def create_folder():
"""Append new folder to folder table
Returns:
String: Success if operation successful else Error
"""
if request.method == 'POST' and 'parentID' in request.form and 'name' in request.form \
and 'MailUser_ID' in request.form:
parent_id, name, user_id = get_folder_form_data(request.form)
if not parent_id or not name or not user_id:
return "Missing data, could not create folder"
connection = mysql.connect()
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO `folder` (`parentID`, `name`, `MailUser_ID`) VALUES (%s, %s, %s)",
(parent_id, name, user_id))
connection.commit()
cursor.close()
connection.close()
return "Success"
except MySQLError:
cursor.close()
connection.close()
return "Error connecting to mysql database"
return "Error"
def comments(id, page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from Comments where phoneId = %s" % (id))
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
phon = ""
phones = [
Samsung, Apple, Microsoft, Nokia, Sony, LG, HTC, Motorola, Huawei,
Lenovo, Xiaomi, Acer, Asus, Oppo, Blackberry, Alcatel, ZTE, Toshiba,
Gigabyte, Pantech, XOLO, Lava, Micromax, BLU, Spice, Verykool, Maxwest,
Celkon, Gionee, Vivo, NIU, Yezz, Parla, Plum
]
for phone in phones:
if not phon:
phon = db.session.query(phone).filter_by(phoneId=id).first()
sql = "select * from Comments where phoneId=%s order by id limit {}, {}".format(
offset, per_page) % (id)
cur.execute(sql)
comments = cur.fetchall()
count = Comments.query.filter_by(phoneId=id).count()
pagination = get_pagination(page=page,
per_page=per_page,
total=total,
record_name='comments',
format_total=True,
format_number=True)
return render_template('comments.html',
phone=phon,
comments=comments,
id=id,
count=count,
page=page,
per_page=per_page,
pagination=pagination)
def deleteOrder(order_id):
#TODO support multiple items
conn = mysql.connect()
delete_cursor = conn.cursor()
all_orders = Order(order_id).getOrderInfo(fetch_all=True)
if 'order' in all_orders:
all_order_ids = Order.fetchAllOrderIds(all_orders)
order_info = all_orders['order']
else:
order_info = all_orders
all_order_ids = str(order_info['order_id'])
if order_info is None:
return {'status': 'false'}
delete_cursor.execute("""SELECT inventory_id FROM order_history WHERE
order_id = %d""" % (order_id))
inventory_id = delete_cursor.fetchone()[0]
q_cond = """ AND fetched = 1"""
delete_cursor.execute("""DELETE FROM inventory WHERE inventory_id =
""" + str(inventory_id) + q_cond)
conn.commit()
delete_cursor.execute(
"DELETE orders, order_history FROM orders INNER JOIN \
order_history WHERE orders.order_id = order_history.order_id AND orders.order_id IN ("
+ all_order_ids + ")")
conn.commit()
delete_cursor.close()
return {'status': 'true'}
def update_folder(folder_id):
"""Update folder from folder table
Returns:
String: Success if operation successful else Error
"""
if request.method == 'PUT':
parent_id, name, user_id = get_folder_form_data(request.form)
connection = mysql.connect()
cursor = connection.cursor()
try:
cursor.execute(
"UPDATE `folder` SET `parentID` = COALESCE(NULLIF(%s, ''), `parentID`),"
"`name` = COALESCE(NULLIF(%s, ''), `name`), "
"`MailUser_ID` = COALESCE(NULLIF(%s, ''), `MailUser_ID`)"
"WHERE id = %s",
(parent_id, name, user_id, folder_id))
connection.commit()
cursor.close()
connection.close()
return "Success"
except MySQLError:
cursor.close()
connection.close()
return "Error connecting to mysql database"
except ValueError:
cursor.close()
connection.close()
return "Wrong password"
return "Could not update folder"
def add_symptom(user_id, symptom_name, rating, time): conn = mysql.connect() cursor = conn.cursor() query = "SELECT Symptom.symptom_id FROM Symptom WHERE Symptom.description = %s" # statement = "INSERT INTO Symptom VALUES (0, %s)" cursor.execute(query, [symptom_name]) data = cursor.fetchone() # if data is None: # cursor.execute(statement, [symptom_name]) # cursor.execute(query, [symptom_name]) # data = cursor.fetchone() symptom_id = int(data[0]) # Insert into Eats, if necessary query = "SELECT Has.symptom_id FROM Has WHERE Has.symptom_id = %s AND Has.user_id = %s AND Has.rating = %s AND Has.time = %s" statement = "INSERT INTO Has VALUES (%s, %s, %s, %s)" cursor.execute(query, [symptom_id, user_id, rating, time]) data = cursor.fetchone() if data is None: cursor.execute(statement, [symptom_id, user_id, rating, time]) conn.commit() cursor.close() conn.close()
def logMetadata(self, app_version):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"""UPDATE users SET last_app_version = %s, last_used_timestamp =
CURRENT_TIMESTAMP WHERE user_id = %s""",
(app_version, self.user_id))
conn.commit()
client = MongoClient(webapp.config['MONGO_DB'])
db = client.ostrich
user_id = int(self.user_id)
user_set = db.user_access_frequency.find({'_id': user_id}).count()
if user_set:
db.user_access_frequency.update(
{'_id': user_id},
{'$addToSet': {
'dates': datetime.now().strftime('%Y-%m-%d')
}})
else:
db.user_access_frequency.insert({
'_id':
user_id,
'dates': [datetime.now().strftime('%Y-%m-%d')]
})
return True
def delete_user(user_id):
"""Delete user from user table
Returns:
String: Success if operation successful else Error
"""
if request.method == 'DELETE' and 'password' in request.form:
connection = mysql.connect()
cursor = connection.cursor()
try:
cursor.execute(
"SELECT `hashedPassword` FROM `mailuser` WHERE id = %s",
user_id)
pw_hash = cursor.fetchone()
#: if provided password is correct, delete user
if pw_hash and bcrypt.check_password_hash(
pw_hash[0], request.form['password']):
cursor.execute("DELETE FROM `mailuser` WHERE id = %s", user_id)
connection.commit()
cursor.close()
connection.close()
return "Success"
except MySQLError:
cursor.close()
connection.close()
return "Error connecting to mysql database"
except ValueError:
cursor.close()
connection.close()
return "Wrong password"
return "Could not delete user"
def service():
cur = mysql.connect().cursor()
cur.execute("SELECT * FROM post ORDER BY id ASC LIMIT 12")
r = [dict((cur.description[i][0], value)
for i, value in enumerate(row)) for row in cur.fetchall()]
print(r)
return jsonify(r)
def indexCollections(self, query_condition):
#TODO fetch collection object from Collection class
cursor = mysql.connect().cursor()
condition = 'WHERE c.partial_order = 0'
if query_condition:
condition += ' AND ' + query_condition
cursor.execute("""SELECT c.*,
(select group_concat(ci.item_id SEPARATOR ',') FROM collections_items ci
WHERE ci.collection_id = c.collection_id) AS item_ids
FROM collections c """ + condition)
num_items = cursor.rowcount
for i in range(num_items):
collection = Utils.fetchOneAssoc(cursor)
collection['item_ids'] = [
int(_) for _ in collection['item_ids'].split(',')
]
collection['metadata'] = self.fetchCollectionsMetadata(
collection['collection_id'])
try:
self.es.index(index=self.es_index,
doc_type='collections',
id=collection['collection_id'],
body=collection,
refresh=True)
self.updateItems(collection['item_ids'], collection['name'])
except Exception, e:
print str(e), collection['collection_id']
def indexItems(self, query_condition='', limit='', custom_keys={}):
search_query = """SELECT i.item_id, i.item_name, i.author, i.price,i.ratings,
i.num_ratings, i.num_reviews, i.img_small, i.asin, i.goodreads_id, i.summary, i.slug_url,
(select group_concat(c.category_name SEPARATOR '|') FROM categories c
INNER JOIN items_categories ic ON ic.category_id = c.category_id WHERE
ic.item_id = i.item_id) AS categories
FROM items i WHERE i.active=1"""
if query_condition:
search_query += query_condition
if limit:
search_query += ' LIMIT ' + limit
cursor = mysql.connect().cursor()
cursor.execute(search_query)
num_items = cursor.rowcount
for num in range(num_items):
record = Utils.fetchOneAssoc(cursor)
print record['item_id']
if record['categories'] is not None:
record['categories'] = record['categories'].split("|")
else:
record['categories'] = []
record = self.fetchItemProperties(record, custom_keys)
record = self.extendItemProperties(record)
record = self.handleUnicode(record)
self.indexItemObject(record)
def getArborBooks(client):
cursor = mysql.connect().cursor()
cursor.execute(
"""SELECT * FROM arbor_inventory WHERE
client=%s GROUP BY item_id""", (client.lower(), ))
stock, taken = [], []
for _ in range(cursor.rowcount):
item = Utils.fetchOneAssoc(cursor)
item['arbor_id'] = '_'.join([
item['client'],
str(item['item_id']),
str(item['inventory_id'])
])
item['item'] = WebUtils.extendItemWebProperties(
[Item(item['item_id']).getObj()])[0]
categories = []
for category in item['item']['categories'][:3]:
categories.append(Item.fetchCategory(name=category))
item['item']['categories'] = categories
if item['in_stock']:
stock.append(item)
else:
taken.append(item)
return [stock, taken]
def getUserOrders(user_id, for_mobile=False):
cursor = mysql.connect().cursor()
cursor.execute(
"""SELECT * FROM arbor_orders ao
INNER JOIN arbor_inventory ai ON ai.inventory_id = ao.inventory_id
WHERE ao.user_id = %s ORDER BY order_placed DESC""", (user_id, ))
orders = {'reading': [], 'history': []}
for _ in range(cursor.rowcount):
item = Utils.fetchOneAssoc(cursor)
item['arbor_id'] = '_'.join([
item['owner'],
str(item['item_id']),
str(item['inventory_id'])
])
if for_mobile:
item['items'] = [
WebUtils.extendItemWebProperties(
[Item(item['item_id']).getObj()])[0]
]
else:
item['item'] = WebUtils.extendItemWebProperties(
[Item(item['item_id']).getObj()])[0]
if not item['order_returned']:
orders['reading'].append(item)
else:
orders['history'].append(item)
return orders
def get(self):
mydata = parser.parse_args()
conn = mysql.connect()
cursor = conn.cursor()
result = []
cursor.execute("select * from orders ")
data = cursor.fetchall()
for i in data:
result.append({
'orderId': i[0],
'itemId': i[1],
"userId": i[2],
"quantity": i[3],
"price": i[4]
})
if result != []:
return {"data": result, "statusCode": 1}
else:
return {"message": "no data found", "statusCode": 0}
def news_comments(id, page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from Comments_news where newsid = %s" % (id))
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
sql = "select * from Comments_news where newsid=%s order by id limit {}, {}".format(
offset, per_page) % (id)
cur.execute(sql)
comments = cur.fetchall()
news = News.query.filter_by(newsid=id).first()
p = re.compile(r'<.*?>')
new = p.sub('', news.Content)
pagination = get_pagination(page=page,
per_page=per_page,
total=total,
record_name='news_comments',
format_total=True,
format_number=True)
count = 0
comm = Comments_news.query.filter_by(newsid=id).all()
if comm:
for comment in comm:
count = Comments_news.query.filter_by(
newsid=id).count() + Replies_news.query.filter_by(
ids=comment.id).count()
return render_template('news_comments.html',
news=news,
new=new,
comments=comments,
count=count,
page=page,
per_page=per_page,
pagination=pagination)
def create_user():
"""Append new user to user table
Returns:
String: Success if operation successful else Error
"""
if request.method == 'POST' and 'username' in request.form and 'password' in request.form \
and 'mail' in request.form:
name = request.form['username']
mail = request.form['mail']
password = request.form['password']
if not password or not name or not mail:
return "password, username and email fields cannot be empty"
connection = mysql.connect()
cursor = connection.cursor()
pw_hash = bcrypt.generate_password_hash(password)
try:
cursor.execute(
"INSERT INTO `mailuser` (`name`, `hashedPassword`, `mailAddress`) VALUES (%s, %s, %s)",
(name, pw_hash, mail))
connection.commit()
except MySQLError:
return "Error connecting to mysql database"
return "Success"
return "Error"
def predict_android():
# get data
json_data = request.get_json()
_time = json_data.get('time')
_mac = json_data.get('mac')
result = json_data.get('result')
# push notifications & send messages
push_url = base_url + "/admin-api/trigger-push-notifications"
requests.post(url=push_url,
data=json.dumps({
"title": "응급상황이 발생했습니다.",
"body": result
}))
# send_url = base_url + "/admin-api/send-messages"
# requests.post(url=send_url, data=json.dumps(
# {"title": "응급상황이 발생했습니다.", "body": result}))
# mysql insert emergency log
conn = mysql.connect()
curs = conn.cursor()
curs.callproc('p_insert_emergency', (result, _time, _mac))
conn.commit()
conn.close()
return jsonify({"status": "success"})
def login():
form = LoginForm()
# s.bind((HOST,PORT))
# s.listen(1) #Listen for a connection
# incoming_socket, address = s.accept()
# sendData1 = "Login detected at" + time.strftime("%a, %d %b %Y %H:%M:%S %Z")
# print(sendData1)
# stringAddress= str(address)
if form.validate_on_submit():
username = form.username.data
password = form.password.data
cursor = mysql.connect().cursor()
cursor.execute("SELECT * FROM Employees WHERE username='******' AND password_hash='" + password + "'")
data = cursor.fetchone()
if data is not None:
session['logged_in'] = True
session['username'] = username
# return """<h1>Login Successful</h1>"""
return redirect(url_for('home'))
else:
sendData1 = "This is the Organization's SMTP MAIL Service, <br> Login detected at " + time.strftime(
"%a, %d %b %Y %H:%M:%S %Z")
sendData2 = request.environ.get('HTTP_X_REAL_IP',
request.remote_addr)
return """<h1>Username or Password is incorrect!</h1><br>
<a href= "/login" >Login</a><br><br>
""" + sendData1 + """
<br>
Intruder IP = """ + sendData2 + """
<br>
"""
return render_template('login.html', form=form)
def update_user():
conn = None
cursor = None
try:
_first_name = request.form['inputFirstName']
_last_name = request.form['inputLastName']
_email = request.form['inputEmail']
_password = request.form['inputPassword']
_id = request.form['id']
# validate the received values
if _first_name and _last_name and _email and _password and _id and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
print(_hashed_password)
# save edits
sql = "UPDATE users SET first_name=%s, last_name=%s, email=%s, password=%s WHERE id=%s"
data = (_first_name, _last_name, _email, _hashed_password, _id,)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
flash('User updated successfully!')
return redirect('/')
else:
return 'Error while updating user'
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def getData(self, collection_id):
from app import cache
cache_key = 'collection_' + str(collection_id)
collection_data = cache.get(cache_key)
if collection_data:
return collection_data
cursor = mysql.connect().cursor()
cursor.execute(
"""SELECT c.*,
(select group_concat(ci.item_id order by ci.sort_order asc separator ',') from collections_items ci
where ci.collection_id = c.collection_id) as item_ids,
(select group_concat(concat(cm.meta_key,":",cm.meta_value) separator '&') from collections_metadata cm
where cm.collection_id = c.collection_id) as metadata
FROM collections c WHERE c.collection_id = %s""",
(collection_id, ))
data = Utils.fetchOneAssoc(cursor)
if data['metadata']:
collections_metadata_raw = data['metadata']
data['metadata'] = {}
for props in collections_metadata_raw.split('&'):
props_formatted = props.split(':')
data['metadata'][props_formatted[0]] = props_formatted[1]
if data['item_ids']:
data['item_ids'] = [int(_) for _ in data['item_ids'].split(',')]
data['items'] = Search().getById(data['item_ids'])
else:
data['items'] = []
if not data:
data = {}
cache.set(cache_key, data)
return data
def applyReferralCode(self, code):
code_details = self.isCodeValid(code)
if not code_details:
return False
else:
[code_id, referrer_id] = code_details
conn = mysql.connect()
apply_code_cursor = conn.cursor()
apply_code_cursor.execute(
"UPDATE user_invite_codes SET counter = counter +1 \
WHERE code_id = %d" % (code_id))
conn.commit()
log_referral_cursor = conn.cursor()
log_referral_cursor.execute(
"INSERT INTO referrals (referrer_id, referent_id, \
activated, activation_date, source, source_id) VALUES (%d, %d, %d, '%s', '%s', \
%d)" %
(referrer_id, self.user_id, 1, Utils.getCurrentTimestamp(),
'invite_code', code_id))
conn.commit()
referral_id = log_referral_cursor.lastrowid
Wallet.creditTransaction(self.wallet_id, self.user_id, 'referral',
referral_id)
return True
def index():
if request.method == 'GET':
if session.get('auth'):
return render_template('index.html')
else:
return redirect(url_for('login'))
else:
conn = mysql.connect()
cur = conn.cursor()
cur.execute('desc pokemon_mv;')
data = cur.fetchmany(3)
attrs = []
for d in data:
attrs.append(d[0])
cur.execute('select * from pokemons;')
data1 = cur.fetchmany(5)
cur.execute('select * from pokemons;')
data2 = cur.fetchmany(5)
cur.close()
print(len(data1), len(data2))
return render_template('relation.html',
attrs=attrs,
data1=data1,
data2=data2,
data=data1)
def add_user():
conn = None
cursor = None
try:
_first_name = request.form['inputFirstName']
_last_name = request.form['inputLastName']
_email = request.form['inputEmail']
_password = request.form['inputPassword']
# validate the received values
if _first_name and _last_name and _email and _password and request.method == 'POST':
#do not save password as a plain text
_hashed_password = generate_password_hash(_password)
# save edits
sql = "INSERT INTO users(first_name, last_name, email, password) VALUES(%s, %s, %s, %s)"
data = (_first_name, _last_name, _email, _hashed_password)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
flash('User added successfully!')
return redirect('/')
else:
return 'Error while adding user'
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def post(self):
try:
"""
Synchronous Key Auth
"""
# Parse the arguments
parser = reqparse.RequestParser()
parser.add_argument('username', type=str, help='username for authentication')
parser.add_argument('password', type=str, help='Password for authentication')
parser.add_argument('encoded', type=str, help='encoded payload')
args = parser.parse_args()
_username = args['username']
_password = args['password']
__encoded = args['encoded']
_hashed_password = generate_password_hash(_password)
conn = mysql.connect()
cursor = conn.cursor()
data = cursor.callproc('sp_AuthenticateUser', (_username, _hashed_password))
secret = data[0]
result = jwt.decode(_encoded, secret, algorithm='HS512')
if(_username == result.username):
return {'status' : 200, 'message' : 'authenticated'}
else:
return {'status' : 100, 'message' : 'rejected'}
except Exception as e:
return {'error': str(e)}
def login():
if request.method == 'POST':
try:
username = request.form['username']
password = request.form['password']
db = mysql.connect()
cursor = db.cursor()
cursor.callproc('validateLogin',(username))
data = cursor.fetchall()
if len(data) > 0:
if check_password_hash(str(data[0][3]),password):
session['user'] = data[0][0]
return redirect('/')
else:
return render_template('error.html',error = 'Wrong username or Password.')
else:
return render_template('error.html',error = 'Wrong username or Password.')
except Exception as e:
return render_template('error.html',error = str(e))
finally:
cursor.close()
db.close()
if session.get('user'):
return render_template('userHome.html')
else:
return render_template('signin.html')
def addrecipe():
if request.method == 'POST':
try:
if session.get('user'):
title = request.form['title']
description = request.form['description']
user = session.get('user')
db = mysql.connect()
cursor = db.cursor()
cursor.callproc('addRecipe',(title,description,user))
data = cursor.fetchall()
if len(data) is 0:
db.commit()
return redirect('/userHome')
else:
return render_template('error.html',error = 'An error occurred!')
else:
return render_template('error.html',error = 'Unauthorized Access')
except Exception as e:
return render_template('error.html',error = str(e))
finally:
cursor.close()
db.close()
return render_template('addRecipe.html')
def getData(self, user_id, login_type):
get_data_query = """SELECT u.user_id, u.username, u.name, u.email, u.phone, u.google_id,
u.gcm_id, u.picture_url, u.date_created, ui.invite_code, uw.wallet_id, uw.amount as wallet_balance FROM users u
LEFT JOIN user_invite_codes ui ON ui.user_id = u.user_id
LEFT JOIN user_wallet uw ON uw.user_id = u.user_id
WHERE u.%s = %d"""
if login_type != 'user_id':
get_data_query = get_data_query.replace("%d", "'%s'")
else:
user_id = int(user_id)
obj_cursor = mysql.connect().cursor()
obj_cursor.execute(get_data_query % (login_type, user_id))
self.data = Utils.fetchOneAssoc(obj_cursor)
if not self.data:
self.data = {}
else:
# TODO shift this to getAddressInfo
self.data['address'] = []
obj_cursor.execute("SELECT * FROM user_addresses WHERE \
user_id = %d" % (self.user_id))
num_address = obj_cursor.rowcount
for i in range(num_address):
address_data = Utils.fetchOneAssoc(obj_cursor)
address_data.update(
Utils.getDeliveryCharge(address_data['distance']))
self.data['address'].append(address_data)
def signup():
if request.method == 'POST':
try:
email = request.form['email'].strip()
username = request.form['username'].strip()
password = request.form['password'].strip()
firstname = request.form['firstname'].strip()
lastname = request.form['lastname'].strip()
gender = request.form['gender'].strip()
birthday = request.form['birthday'].strip()
if email and username and password and firstname and lastname and birthday and gender:
db = mysql.connect()
cursor = db.cursor()
password_hash = generate_password_hash(password)
cursor.callproc('createUser',(email, username, password_hash, firstname, lastname, gender, birthday))
data = cursor.fetchall()
if len(data) is 0:
db.commit()
return json.dumps({'message':'Account successfully created!'})
else:
return json.dumps({'error':str(data[0])})
else:
return json.dumps({'html':'<span>All fields are required</span>'})
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
db.close()
return render_template('signup.html')
def create_mail():
"""Append new email to mail table
Returns:
String: Success if operation successful else Error
"""
if request.method == 'POST' and 'fromMail' in request.form and 'fromName' in request.form \
and 'receivedData' in request.form and 'subject' in request.form and 'Folder_ID' in request.form \
and 'MailUser_ID' in request.form and 'body' in request.form:
from_mail, from_name, received_data, subject, body, folder_id, mail_user_id = get_mail_form_data(
request.form)
if not from_name or not from_mail or not received_data or not subject or not body \
or not folder_id or not mail_user_id:
return "Missing data, could not append email to database"
connection = mysql.connect()
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO `mail` (fromMail, fromName, receivedData, subject, body, Folder_ID, MailUser_ID)"
"VALUES (%s, %s, %s, %s, %s, %s, %s)",
(from_mail, from_name, received_data, subject, body, folder_id,
mail_user_id))
connection.commit()
cursor.close()
connection.close()
return "Success"
except MySQLError:
cursor.close()
connection.close()
return "Error connecting to mysql database"
return "Error"
def pickupSchedule():
current_ts = Utils.getCurrentTimestamp()
date = current_ts.split(' ')[0]
# Order Pickup
order_list = []
cursor = mysql.connect().cursor()
user_id_format_char = ",".join(["%s"] * len(Utils.getAdmins()))
cursor.execute(
"""SELECT COUNT(*)
FROM orders
WHERE DATE(order_return) = %s AND order_id NOT IN
(SELECT DISTINCT parent_id FROM orders) AND user_id NOT IN (""" +
user_id_format_char + """)
AND order_status >= 4""",
tuple([date]) + tuple(Utils.getAdmins()))
order_ids = cursor.fetchone()
if order_ids[0] > 0:
Utils.notifyAdmin(-1, "PICKUPS DUE TODAY!!")
cursor.execute(
"""SELECT COUNT(*)
FROM b2b_users WHERE DATE(timestamp) = %s""",
(Utils.getDefaultReturnTimestamp(current_ts, -21).split(' ')[0], ))
order_ids = cursor.fetchone()
if order_ids[0] > 0:
Utils.notifyAdmin(-1, "B2B PICKUPS DUE!!")
return None
def getAddressInfo(address_id):
address_cusor = mysql.connect().cursor()
address_cusor.execute("""SELECT * FROM user_addresses
WHERE address_id = %d""" % (address_id))
address_obj = Utils.fetchOneAssoc(address_cusor)
address_obj.update(Utils.getDeliveryCharge(address_obj['distance']))
return address_obj if address_obj else {}
def removeFromUnregistered(self):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("""DELETE FROM users_unregistered WHERE gcm_id = %s""",
(self.gcm_id, ))
conn.commit()
return True
def submitReview(review_data):
user_id = Utils.getParam(review_data, 'user_id')
item_id = Utils.getParam(review_data, 'item_id')
order_id = Utils.getParam(review_data, 'order_id')
if not (user_id and item_id and order_id):
return False
title = Utils.getParam(review_data, 'title')
description = Utils.getParam(review_data, 'description')
rating = Utils.getParam(review_data, 'rating')
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"""SELECT review_id FROM reviews WHERE user_id = %s AND item_id = %s""",
(user_id, item_id))
present_review_id = cursor.fetchone()
if present_review_id:
review_data['review_id'] = present_review_id[0]
review = Review(review_id=review_data['review_id'])
review.editReview(review_data)
return review_data['review_id']
cursor.execute(
"""INSERT INTO reviews (user_id, item_id, order_id, title, description, rating) VALUES (%s,%s,%s,%s,%s,%s)""",
(user_id, item_id, order_id, title, description, rating))
conn.commit()
review_id = cursor.lastrowid
#TODO Index item again with review
return review_id
def updateOrderComment(data):
data['delivered_by'] = Utils.getParam(data, 'delivered_by', default='')
data['delivery_amount'] = Utils.getParam(data,
'delivery_amount',
default=0)
data['picked_by'] = Utils.getParam(data, 'picked_by', default='')
data['pickup_amount'] = Utils.getParam(data,
'pickup_amount',
default=0)
data['edited'] = Utils.getParam(data, 'edited', default=0)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(
"""UPDATE orders_admin_notes SET
comment = %s,
edited = %s,
delivered_by = %s,
delivery_amount = %s,
picked_by = %s,
pickup_amount = %s
WHERE order_id = %s AND order_type = %s""",
(data['comment'], data['edited'], data['delivered_by'],
data['delivery_amount'], data['picked_by'], data['pickup_amount'],
data['order_id'], data['order_type']))
conn.commit()
affected = cursor.rowcount
if not affected:
cursor.execute(
"""INSERT INTO orders_admin_notes (order_id, order_type, comment, edited)
VALUES (%s, %s, %s, %s)""",
(data['order_id'], data['order_type'], data['comment'],
data['edited']))
conn.commit()
return True
def getAllRentals(self):
from app.models import Order
inv_cursor = mysql.connect().cursor()
inv_cursor.execute("""SELECT i.*, l.*
FROM inventory i
INNER JOIN lenders l ON l.inventory_id = i.inventory_id
WHERE l. user_id = %d""" % (self.user_id))
num_items = inv_cursor.rowcount
inv_items = []
for slot in range(num_items):
inv_info = Utils.fetchOneAssoc(inv_cursor)
inv_info['items'] = [Item(int(inv_info['item_id'])).getObj()]
all_timeslots = Order.getTimeSlot()
ts = [
_ for _ in all_timeslots
if _['slot_id'] == inv_info['pickup_slot']
][0]
inv_info['pickup_time'] = Utils.cleanTimeSlot(ts)
inv_items.append(inv_info)
rental_statses = {"rentals": [], "rental_history": []}
current_timestamp = datetime.now()
for item in inv_items:
date_removed = datetime.strptime(item['date_removed'],
"%Y-%m-%d %H:%M:%S")
diff = current_timestamp - date_removed
if diff.days > 0:
rental_statses["rental_history"].append(item)
else:
rental_statses["rentals"].append(item)
return rental_statses
def reviewLiked():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * from liked")
data = cursor.fetchall()
cursor.close()
print "view"
return [dict(small=row[1], big=row[2]) for row in data]
def deleteSubscribers(id):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM SUBSCRIBERS WHERE ID=%s", [id])
cursor.close()
conn.commit()
conn.close()
return True
def getQuestionAll(self):
cursor = mysql.connect().cursor()
cursor.execute("SELECT * from question_value")
data = cursor.fetchall()
if data is None:
return False
else:
return data
def loadSubscribers():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT id, name, email, phone_num FROM SUBSCRIBERS")
data = cursor.fetchall()
cursor.close ()
conn.close ()
print data
return data
def mobiles(brand, page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from %s" % (brand))
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
sql = "select phoneId, Name, Picture, OS, Announced, Type, `Primary`, WLAN, GPS,Bluetooth from %s order by name limit {}, {}".format(offset, per_page) % (brand)
cur.execute(sql)
phones = cur.fetchall()
pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='phones', format_total=True, format_number=True)
return render_template('phones.html', phones=phones,page=page, per_page=per_page, pagination=pagination,brand=brand)
def delete_food_item(user_id, item_id, time): conn = mysql.connect() cursor = conn.cursor() statement = "DELETE FROM Eats WHERE Eats.user_id = %s AND Eats.item_id = %s AND Eats.time = %s" cursor.execute(statement, [user_id, item_id, time]) conn.commit() cursor.close() conn.close()
def delete_symptom(user_id, symptom_id, time): conn = mysql.connect() cursor = conn.cursor() statement = "DELETE FROM Has WHERE Has.user_id = %s AND Has.symptom_id = %s AND Has.time = %s" cursor.execute(statement, [user_id, symptom_id, time]) conn.commit() cursor.close() conn.close()
def processSubscriber(name, email, phone_num):
if phone_num.isdigit==False:
return False
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("INSERT INTO SUBSCRIBERS VALUES (%s, %s, %s, %s);", (0,name, email, int(phone_num)))
cursor.close ()
conn.commit ()
conn.close ()
print name, email, phone_num
return True
def new(page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from news")
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
sql = "select * from news order by id limit {}, {}".format(offset, per_page)
cur.execute(sql)
news = cur.fetchall()
pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='news', format_total=True, format_number=True)
for new in news:
p = re.compile(r'<.*?>')
return render_template('all_news.html',news=news,p=p,page=page, per_page=per_page, pagination=pagination)
def news_comment(id):
form = CommentForm()
cur = mysql.connect().cursor()
sql = "select newsid, Picture, Content, Name from news WHERE newsid=%s" %(id)
cur.execute(sql)
news = cur.fetchone()
p = re.compile(r'<.*?>')
new = p.sub('', news[2])
if form.validate():
comment = Comments_news(newsid=id,created_at=datetime.datetime.now(),body=form.body.data,author=form.author.data)
db.session.add(comment)
db.session.commit()
return render_template('news_comment.html',news=news,new=new,form=form)
def sign_up_user(name, email, password): conn = mysql.connect() cursor = conn.cursor() # query = "SELECT User.user_id FROM User WHERE User.email = %s" statement = "INSERT INTO User VALUES (0, %s, %s, %s)" cursor.execute(statement, [name, email, password]) # cursor.execute(query, [email]) # data = cursor.fetchone() conn.commit() cursor.close() conn.close()
def add_food_item(user_id, item_name, ingr_list, time):
conn = mysql.connect()
cursor = conn.cursor()
# Insert into Food_Item, if necessary
# query = "SELECT MAX (Food_Item.item_id) FROM Food_Item WHERE Food_Item.name = %s"
query = "SELECT LAST_INSERT_ID()"
statement = "INSERT INTO Food_Item VALUES (0, %s)"
# cursor.execute(query, [item_name])
#data = cursor.fetchone()
# if data is None:
cursor.execute(statement, [item_name])
cursor.execute(query)
data = cursor.fetchone()
item_id = int(data[0])
# Insert into Eats, if necessary
query = "SELECT Eats.item_id FROM Eats WHERE Eats.user_id = %s AND Eats.item_id = %s and Eats.time = %s"
statement = "INSERT INTO Eats VALUES (%s, %s, %s)"
cursor.execute(query, [user_id, item_id, time])
data = cursor.fetchone()
#if data is None:
cursor.execute(statement, [user_id, item_id, time])
# Insert into Ingredients, if necessary
# ingr_list = map(str.strip, str(ingr_list).split(','))
for ingr in ingr_list:
query = "SELECT Ingredient.ingredient_id FROM Ingredient WHERE Ingredient.name = %s"
statement = "INSERT INTO Ingredient VALUES (0, %s)"
cursor.execute(query, [ingr])
data = cursor.fetchone()
if data is None:
cursor.execute(statement, [ingr])
cursor.execute(query, [ingr])
data = cursor.fetchone()
ingr_id = int(data[0])
# Insert into Contains, if necessary
query = "SELECT Contains.item_id FROM Contains WHERE Contains.ingredient_id = %s AND Contains.item_id = %s"
statement = "INSERT INTO Contains VALUES (%s, %s)"
cursor.execute(query, [ingr_id, item_id])
data = cursor.fetchone()
if data is None:
cursor.execute(statement, [ingr_id, item_id])
conn.commit()
cursor.close()
conn.close()
def get_result(query, values): conn = mysql.connect() cursor = conn.cursor() cursor.execute(query, values) data = [] row = row_to_dict(cursor) while row is not None: data.append(row) row = row_to_dict(cursor) cursor.close() conn.close() return data
def LikeImage(smallURL, bigURL):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * from liked where url_s = '"+smallURL+"'")
data = cursor.fetchone()
if data is None:
cursor.execute("INSERT into liked values ('', '"+smallURL+"','"+bigURL+"')")
conn.commit()
cursor.close()
print "INSERT"
return "This image is liked!"
else:
cursor.close()
print "already liked"
return "This image has already been liked!"
def comments(id, page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from Comments where phoneId = %s" % (id))
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
phon = ""
phones = [Samsung,Apple,Microsoft,Nokia,Sony,LG,HTC,Motorola,Huawei,Lenovo,Xiaomi,Acer,Asus,Oppo,Blackberry,Alcatel,
ZTE,Toshiba,Gigabyte,Pantech,XOLO,Lava,Micromax,BLU,Spice,Verykool,Maxwest,Celkon,Gionee,Vivo,NIU,Yezz,Parla,Plum]
for phone in phones:
if not phon:
phon = db.session.query(phone).filter_by(phoneId=id).first()
sql = "select * from Comments where phoneId=%s order by id limit {}, {}".format(offset, per_page) % (id)
cur.execute(sql)
comments = cur.fetchall()
count = Comments.query.filter_by(phoneId=id).count()
pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='comments', format_total=True, format_number=True)
return render_template('comments.html',phone=phon,comments=comments, id=id, count=count,page=page, per_page=per_page, pagination=pagination)
def get(self):
cursor = mysql.connect().cursor()
cursor.execute("SELECT * from next_question where soft_delete = 0")
data = cursor.fetchall()
my_question = []
for i in data:
self.nid = i[0]
self.option_one = i[2]
self.option_two = i[3]
self.option_three = i[4]
self.option_four = i[5]
my_question.append({'nid':self.nid,
'option_one':self.option_one,
'option_two':self.option_two,
'option_three':self.option_three,
'option_four':self.option_four})
return my_question
def news_comments(id,page=1):
cur = mysql.connect().cursor()
cur.execute("select count(*) from Comments_news where newsid = %s" % (id))
total = cur.fetchone()[0]
page, per_page, offset = get_page_items()
sql = "select * from Comments_news where newsid=%s order by id limit {}, {}".format(offset, per_page) % (id)
cur.execute(sql)
comments = cur.fetchall()
news = News.query.filter_by(newsid=id).first()
p = re.compile(r'<.*?>')
new = p.sub('', news.Content)
pagination = get_pagination(page=page, per_page=per_page, total=total, record_name='news_comments', format_total=True, format_number=True)
count = 0
comm = Comments_news.query.filter_by(newsid=id).all()
if comm:
for comment in comm:
count = Comments_news.query.filter_by(newsid=id).count()+Replies_news.query.filter_by(ids=comment.id).count()
return render_template('news_comments.html',news=news,new=new,comments=comments,count=count,page=page, per_page=per_page, pagination=pagination)
def getQuestionbyId(self):
cursor = mysql.connect().cursor()
cursor.execute("SELECT * from question_value where soft_delete = 0 and aid = "+str(self.aid))
data = cursor.fetchall()
my_question = []
for i in data:
self.aid = i[0]
self.question = i[1]
self.option_one = i[2]
self.option_two = i[3]
self.option_three = i[4]
self.option_four = i[5]
my_question.append({'aid':self.aid,
'question':self.question,
'option_one':self.option_one,
'option_two':self.option_two,
'option_three':self.option_three,
'option_four':self.option_four})
return my_question
def getRecipes():
try:
if session.get('user'):
_user = session.get('user')
con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,))
recipes = cursor.fetchall()
recipes_dict = []
for recipe in recipes:
recip_dict = {
'Id': recipe[0],
'Title': recipe[1],
'Description': recipe[2],
'Date': recipe[4]}
recipes_dict.append(recip_dict)
return json.dumps(recipes_dict)
else:
return render_template('error.html', error = 'Unauthorized Access')
except Exception as e:
return render_template('error.html', error = str(e))
def dislikeImage(smallURL, bigURL):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE from liked where url_s = '"+smallURL+"'")
conn.commit()
print "delete"
def get_cursor():
""" Get a MySQL cursor """
cursor = mysql.connect().cursor()
return cursor
def get_connection():
""" Tests MySQL connection """
return mysql.connect()
def check_connection():
""" Tests MySQL connection """
conn = mysql.connect()
return conn.close()
def insert(self):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('insert into question_value(question,option_one,option_two,option_three,option_four) values("'+self.question+'","'+self.option_one+'","'+self.option_two+'","'+self.option_three+'","'+self.option_four+'")')
conn.commit()
return 'success'
from flaskext.mysql import MySQL
from app import mysql
connection = mysql.connect()
def get_top_questions(q_ref_id=None):
cursor = connection.cursor()
query = "SELECT * from Questions order by q_votes desc"
if q_ref_id:
query = "SELECT * from Questions where q_ref_id = '"+q_ref_id+"' order by q_votes desc"
cursor.execute(query)
data = cursor.fetchall()
results = []
for _d in data:
print _d
_data = {
'q_id': _d[0],
'q_question': _d[1],
'q_ref_id': _d[2],
'q_user_id': _d[3],
'q_votes': _d[4],
'q_state': _d[5]
}
results.append(_data)
return results
