def send_product_notification():
from functions.sqlquery import sql_query2
from pyfcm import FCMNotification
user_id = request.form['user_id']
prod_id = sql_query2(
''' SELECT * from user_side_notifications where user_id = ?''',
(user_id, ))
prod_id = prod_id[-1][1]
#time = prod_id[0][1]
# return jsonify(time)
best_product = sql_query2(
''' SELECT name from product_table where product_id =?''', (prod_id, ))
best_product = best_product[-1][0]
time_id = get_time()
# return "hora hai"
push_service = FCMNotification(
api_key=
"AAAAYNJto2I:APA91bEHdYbm2mhUfGfBIWseFzintaOJgJDifgSyzJdfcZrCyUdLD9XPwSKlX6IMaOeXNMkdTSs52mGKVequsJ_JtdZcq-6e15L9UzjHhs2JaMhcHmpDPgBu0D0bmPyXO2pqHa2Q0q0d"
)
registration_id = "f8bLCD9kaoI:APA91bGN7uLHIZyefhLFBLa_HvHedRH7sq-o-LFKelIEkV9NS_ZGCrFVUePsySHjaeZZoWREfRkwF6LEcF-Z1vYpzfybfGJb7hMIwfdknVQzPcneWMsBpFsLDIlw4A7QAzFSS9vO9lVL"
message_title = "We think you will love"
message_body = best_product # + time_id
result = push_service.notify_single_device(registration_id=registration_id,
message_title=message_title,
message_body=message_body)
return jsonify(result)
def getUserTime():
user_id = request.form['user_id']
notif_category = request.form['notif_category']
#ab time_id ek hi aana chaiye nahi to agar multiple aaya to algo waise aayega
#abhi lets take only one for each category and overwrite agar jyaada liya to
#ek hi aayega number mere pass
time_id = request.form['time_id']
#time_ids are derived from time_slot.csv
from functions.sqlquery import sql_query2
results = sql_query2(
''' INSERT INTO user_time (user_id, notif_category, time_id) VALUES (?,?,?)''',
(user_id, notif_category, time_id))
results2 = sql_query2(''' SELECT * from user_time WHERE user_id = ? ''',
(user_id, ))
return jsonify(results2)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
edname = request.args.get('edname')
eresults = sql_query2(''' SELECT * FROM data_table where drink_name = ?''', (edname))
results = sql_query(''' SELECT * FROM data_table''')
return render_template('sqldatabase.html', eresults=eresults, results=results)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
word = request.args.get('word')
# efname = request.args.get('efname')
eresults1 = sql_query2(
''' SELECT DISTINCT * FROM words where word = ?''', (word, ))
# Decode base 64
eresults = []
for result in eresults1:
resultx = dict()
payload = base64.decodestring(str(result['payload']))
resultx['word'] = word
resultx['payload'] = payload
resultx['created_time'] = result['created_time']
eresults.append(resultx)
results1 = sql_query(''' SELECT * FROM words''')
# Decode base 64
results = []
for result in results1:
resultx = dict()
payload = json.loads(
base64.decodestring(str(result['payload']).encode()))
mean = json.loads(base64.decodestring(str(payload['means']).encode()))
resultx['payload'] = mean
resultx['word'] = result['word']
resultx['created_time'] = result['created_time']
results.append(resultx)
return render_template('sqldatabase.html',
eresults=eresults,
results=results)
def sql_editlink():
if request.method == 'GET':
elname = request.args.get('elname')
efname = request.args.get('efname')
eresults = sql_query2(''' SELECT * FROM data_table where first_name = ? and last_name = ?''', (efname,elname))
results = sql_query(''' SELECT * FROM data_table''')
return render_template('sqldatabase.html', eresults=eresults, results=results)
def register():
from functions.sqlquery import sql_query2, sql_edit_insert
msg = ''
if request.method == 'POST' and 'username' in request.form and 'password' in request.form and 'email' in request.form:
email = request.form['email']
username = request.form['username']
password = request.form['password']
cursor = sql_query2(''' SELECT * FROM data_table where username = ?''',
(username, ))
if len(cursor) > 0:
msg = 'Account already exists!'
elif not re.match(r'[A-Za-z0-9]+', username):
msg = 'Username must contain only characters and numbers!'
elif not re.search(r'^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$',
email):
msg = 'Email must be a valid one!'
elif not username or not password or not email:
msg = 'Please fill out the form !'
else:
sql_edit_insert(
'INSERT INTO data_table(email,username,password) VALUES (?, ?, ?)',
(email, username, password))
msg = 'You have successfully registered !'
elif request.method == 'POST':
msg = 'Please fill out the form !'
return render_template('register.html', msg=msg)
def getRecom(id):
store = id
#results = []
from functions.sqlquery import sql_query2
final_answer = []
results = sql_query2(
''' SELECT product_2_id FROM data_table WHERE product_1_id = ? and factor > 0.5''',
(store, ))
#sirf dikhaneke liye
for i in range(len(results)):
var = results[i][0]
answer = sql_query2(
''' SELECT * FROM product_table WHERE product_id = ?''', (var, ))
final_answer.append(answer)
return jsonify(final_answer)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
e_artist = request.args.get('e_artist')
e_title = request.args.get('e_artist')
e_results = sql_query2(''' SELECT * FROM record WHERE artist = ? and
title = ?''', (e_artist,e_title))
results = sql_query(''' SELECT * FROM record''')
return render_template('sqldatabase.html', eresults=eresults,
results=results)
def query_Selected_Row():
from functions.sqlquery import getAllDevices, sql_query2, getSortedDeviceID
if request.method == 'GET':
Selected_Device_ID = request.args.get('Selected_Device_ID')
eresults = sql_query2('''select * from Device where device_ID = ? ''',
(Selected_Device_ID, ))
dresults = getSortedDeviceID()
return render_template('admin.html',
eresults=eresults,
dresults=dresults)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
elreply = request.args.get('elreply')
efquery = request.args.get('efquery')
eresults = sql_query2(
''' SELECT * FROM dataset where query = ? and reply = ?''',
(efquery, elreply))
results = sql_query(''' SELECT * FROM dataset''')
return render_template('t_board.html', eresults=eresults, results=results)
def profile(uname):
#get account info
if request.method == 'POST':
old_last_name = request.form['old_last_name']
old_first_name = request.form['old_first_name']
last_name = request.form['last_name']
first_name = request.form['first_name']
school = request.form['school']
degree = request.form['degree']
field = request.form['field']
year = request.form['year']
current_employer = request.form['current_employer']
job_title = request.form['job_title']
user_name = request.form['user_name']
contact = request.form['contact']
location = request.form['location']
sql_edit_insert(''' UPDATE data_table set first_name=?,last_name=?,school=?,degree=?,field=?,year=?,current_employer=?,job_title=?,user_name=?,contact=?,location=? WHERE user_name=? ''', (first_name,last_name,school,degree,field,year,current_employer,job_title,user_name,contact,location,user_name))
msg = 'Updated, return to Home page!'
aresults = sql_query2(''' SELECT * FROM data_table where user_name = ?''', (uname,))
return render_template('profile.html', uname=user_name, aresults=aresults, msg=msg)
else:
msg=''
aresults = sql_query2(''' SELECT * FROM data_table where user_name = ?''', (uname,))
return render_template('profile.html', uname=uname, aresults=aresults, msg=msg)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
#elname = request.args.get('elname')
edit_title = request.args.get('edit_title')
#efname = request.args.get('efname')
edit_date = request.args.get('edit_date')
eresults = sql_query2(
''' SELECT * FROM data_table_2 where date = ? and title = ?''',
(edit_date, edit_title))
results = sql_query(''' SELECT * FROM data_table_2''')
return render_template('update_page.html',
eresults=eresults,
results=results)
def login():
from functions.sqlquery import sql_query2
msg = ''
if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
username = request.form['username']
password = request.form['password']
room = request.form['room']
cursor = sql_query2(
'SELECT * FROM data_table WHERE username = ? AND password = ?',
(username, password))
if len(cursor) > 0:
account = cursor[0]
session['loggedin'] = True
session['room'] = room
session['username'] = account['username']
msg = 'Logged in successfully !'
return render_template('chat.html',
name=account['username'],
room=session['room'])
else:
msg = 'Incorrect username / password !'
return render_template('login.html', msg=msg)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2, sql_query3
if request.method == 'GET':
egenus = request.args['egenus']
especies = request.args['especies']
ecomname = request.args['ecomname']
eresults = sql_query2(
''' SELECT * FROM Flowers WHERE genus = ? AND species = ? ''',
(egenus, especies))
sightings_results = sql_query3(
'''SELECT * FROM SIGHTINGS WHERE NAME=? ORDER BY SIGHTED DESC''',
(ecomname, ), 10)
for each in sightings_results:
print(each['person'])
flowers_table = sql_query('''SELECT * FROM Flowers''')
sightings_table = sql_query('''SELECT * FROM Sightings''')
return render_template('sqldatabase.html',
eresults=eresults,
sightings_results=sightings_results,
sightings_table=sightings_table,
flowers_table=flowers_table)
def sql_database():
global customer_cache
global start_time
count = 0
while (True):
# print(count)
result_list = []
extra_cust = []
flag = False
# if((time.time() - start_time)/(1000*60*60) >3 ):
print((time.time() - start_time))
if ((time.time() - start_time) > 5555):
# print("*********************customer cache")
customer_cache = []
start_time = time.time()
# print("ok")
fname = ["akshata", "shreya"]
#for testing purposes
if (time.time() - start_time > 5):
fname.append("POPO")
# print(customer_cache)
for name in fname:
if (name not in customer_cache):
flag = True
# print("name*****************",name)
extra_cust.append(name)
# print("extra cust******",extra_cust)
if (flag == True):
# print("***************DB CALL********************")
results = sql_query2(
''' SELECT person_id, first_name,last_name,firebase_id,IsSignificant,mobile FROM Customers_master where first_name in (%s)'''
% ','.join('?' * len(extra_cust)), extra_cust)
count += 1
# print("Result*****",results)
if (len(results) == 0):
for name in extra_cust:
customer_cache.append(name)
for result in results:
result_dict = {
'ID': result['person_id'],
'firstname': result['first_name'],
'lastname': result['last_name'],
'firebaseid': result['firebase_id'],
'IsSignificant': result['IsSignificant'],
'mobile number': result['mobile']
}
if (len(customer_cache) == 0):
start_time = time.time()
customer_cache.append(result['first_name'])
result_list.append(result_dict)
name = result_dict['firstname'] + result_dict['lastname']
sql_edit_insert(
''' INSERT INTO Customers (id,Name,IsSignificant,firebase_id, mobile) VALUES (?,?,?,?,?)''',
(result_dict['ID'], name, result_dict['IsSignificant'],
result_dict['firebaseid'], result_dict['mobile number']))
extra_cust = []
def sql_edit():
from functions.sqlquery import sql_query2
name = request.args.get('episode_name')
episode_to_edit = sql_query2(''' SELECT * FROM got WHERE `episode name` = ?''', (name, ))
return render_template('sqlform2.html', results=episode_to_edit)
def getBest():
#should implement after set period
from pyfcm import FCMNotification
user_id = int(request.form['user_id'])
from functions.sqlquery import sql_query2, sql_query
results = sql_query2(
''' SELECT search1_id, time_spent from searches where user_id = ?''',
(user_id, ))
results = results[-10:]
df = pd.read_csv('products.csv',
usecols=[
'product_id', 'number_of_purchases', 'name',
'actual_price', 'original_price',
'discount_percentage', 'number_of_offers',
'seller_name'
])[1:162]
df["number_of_purchases"].fillna("0", inplace=True)
df2 = pd.read_csv('products.csv',
usecols=['product_id', 'number_of_purchases'])[1:162]
df2["number_of_purchases"].fillna("1", inplace=True)
main_data = df.values.tolist()
data2 = df2.values.tolist()
for i in range(len(data2)):
data2[i][0] = int(data2[i][0])
data2[i][1] = int(data2[i][1])
df3 = pd.read_csv('recommendations.csv')
data3 = df3.values.tolist()
for i in range(len(data3)):
data3[i][0] = int(data3[i][0])
data3[i][1] = int(data3[i][1])
total = 0
for i in range(len(data2)):
total += data2[i][1]
max_value = 0
final_id = 1
result_list = []
for i in range(len(data2)):
pro_id = data2[i][0]
final_prob = 0
every_result = []
for j in range(len(results)):
second_id = results[j][0]
time_value = results[j][1]
#ab inka intersection nikaalna padta
intersect_value = sql_query2(
''' SELECT factor from
data_table where product_1_id = ?
and product_2_id = ?''', (pro_id, second_id))
if not intersect_value:
value = 0.1
else:
value = intersect_value[0][0]
#time aayega abhi bas
quantity_value = sql_query2(
''' SELECT number_of_purchases from
product_table where product_id = ? ''', (second_id, ))
quantity = quantity_value[0][0]
# bayes_prob = probability of A intersection B * probability of purchase b
ratio = quantity / total
computed_value = math.log10(quantity)
time_log = math.log10(time_value)
#log of denominator jaruri nahi hai because nonetheless it manages
#itself by minusing itself from
bayes_prob = (computed_value) * (value) * (time_log)
final_prob += bayes_prob
shevat = sql_query2(
''' SELECT name from
product_table where product_id = ? ''', (pro_id, ))
every_result = [shevat[0][0], final_prob]
result_list.append(every_result)
if final_prob > max_value:
max_value = final_prob
final_id = pro_id
#prod_id = sql_query2(''' SELECT name from product_table where product_id = ?''', (final_id,))
#prod_id = prod_id[0][0]
#return jsonify(prod_id)
#ab we have that product with us. Lets see kya aaya
from operator import itemgetter
result_list = sorted(result_list, key=itemgetter(1))
time_id = str(get_time())
time = time_id[0:2] + time_id[3:5]
from functions.sqlquery import sql_edit_insert
return_value = sql_edit_insert(
''' INSERT INTO user_side_notifications (user_id, product_id, time_id) VALUES (?,?,?) ''',
(user_id, final_id, time))
#ye idhar hi end hoga
return jsonify(result_list)
def getDetails(id):
store = id
from functions.sqlquery import sql_query2
results = sql_query2(
''' SELECT * FROM product_table WHERE product_id = ?''', (store, ))
return jsonify(results[0])