def viewmyoffer():
id = session["id"]
qry = "select product.*,offer.offer,offer.offr_latdate from offer inner join product on offer.product_id=product.product_id where offer_id in (select ofrid from offer_subscribed where uid='" + str(
id) + "')"
c = conn()
res = c.selectall(qry)
return render_template("user_myoffers.html", res=res)
def addsubscribers(uid, pid, ofid):
c = conn()
q = "insert into offer_subscribed(uid,ofrid,pid) values('" + str(
uid) + "','" + str(ofid) + "','" + str(pid) + "')"
print(q)
c.nonreturn("insert into offer_subscribed(uid,ofrid,pid) values('" +
str(uid) + "','" + str(ofid) + "','" + str(pid) + "')")
def post_adminviewreports():
frm = request.form["from"]
to = request.form["to"]
qry = "select user.name,email,phone,place,date,total_amount from sales_master,user where sales_master.custid=user.user_id and date between '" + frm + "' and '" + to + "'"
c = conn()
res = c.selectall(qry)
return render_template("viewreports.html", res=res)
def inswork():
wid = request.form["wid"]
staffid = request.form["sid"]
c = conn()
qry = "INSERT INTO `order_assign` (`orderid`,`staffid`,`status`,`dates`) VALUES ('" + wid + "','" + staffid + "','pending',CURDATE())"
c.nonreturn(qry)
return "ok"
def reg():
name = request.form['name']
print(name)
phoneno = str(request.form['phonenumber'])
print(phoneno)
place = request.form['place']
print(place)
city = request.form['city']
print(city)
country = request.form['country']
print(country)
email = request.form['email']
print(email)
password = str(request.form['password'])
print(password)
confirmpassword = request.form['confirmpassword']
print(confirmpassword)
addprofilepic = request.files['add']
print(addprofilepic)
c = conn()
s = "select max(user_id)from user"
print(s)
user_id = c.mid(s)
print(user_id)
path = "static/user_img/" + user_id + ".jpg"
addprofilepic.save(path)
query = "insert into user(name,email,password,phone,place,city,country,profilepic)values('" + name + "','" + password + "','" + phoneno + "','" + place + "','" + city + "','" + country + "','" + path + "')"
print(query)
query1 = "insert into login(user_name,password,type) values('" + name + "','" + password + "','user')"
print(query1)
i = c.nonreturn(s)
return i
def usergetcompltedeassignedsearch():
uid = request.form["uid"]
sr = request.form["sr"]
qry = "select user.name,email,phone,place,date,total_amount,sales_master.transactionid,profilepic from sales_master,user where sales_master.custid=user.user_id and sales_master.`transactionid` IN ( SELECT `orderid` FROM `order_assign` where status='completed' and staffid='" + uid + "') and name like '%" + sr + "%'"
c = conn()
res = c.jsonselect(qry)
return jsonify(status='ok', res=res)
def admin_addcategory():
categoryname = request.form['categoryname']
c = conn()
s = "select max(category_id)from category"
print(s)
category_id = c.mid(s)
print(category_id)
query = "insert into category(name)value('" + categoryname + "')"
print(query)
i = c.nonreturn(query)
return view()
def user_myanalytics():
con = conn()
res = con.selectall(
"select brandname,scorepositive,scorenegative from brand_score where uid='"
+ str(session["id"]) + "'")
res1 = con.selectall(
"select name,scorepos,scoreneg from category,cat_score where cat_score.catid=category.category_id and uid='"
+ str(session["id"]) + "'")
print(res)
print(res1)
return render_template("user_myanalytics.html", res=res, res1=res1)
def andlogin():
c = conn()
uname = request.form["username"]
pasword = request.form["password"]
qry = "SELECT `staff`.`staffid` FROM `login`,staff WHERE login.user_name=staff.emailid AND `user_name`='" + uname + "' AND PASSWORD='******' AND TYPE='staff' "
res = c.selectone(qry)
if res is not None:
return jsonify(status='ok', uid=res[0])
else:
return jsonify(status='mo')
def adminviewreports():
qry = "select user.name,email,phone,place,date,total_amount from sales_master,user where sales_master.custid=user.user_id"
c = conn()
res = c.selectall(qry)
return render_template("viewreports.html", res=res)
def viewassignedorder():
qry = "SELECT user.name,email,user.phone,user.place,DATE,total_amount,sales_master.transactionid,`staff`.`name` AS 'Assigned To',`order_assign`.`status` FROM sales_master,USER,`order_assign`,`staff` WHERE sales_master.custid=user.user_id AND `staff`.`staffid`=`order_assign`.`staffid` AND `order_assign`.`orderid`=`sales_master`.`transactionid` "
print(qry)
c = conn()
res = c.selectall(qry)
return render_template("viewassignedorder.html", res=res)
def trans():
d1 = request.args.get("dt1")
d2 = request.args.get("dt2")
print(d1)
print(d2)
transaction1 = []
qty = []
profit = []
tu1 = []
itmid = []
itub = []
ubitm = []
Ubfpe = []
c = conn()
q1 = "SELECT transactionid FROM sales_master" # WHERE DATE BETWEEN '"+d1+"' AND '"+d2+"'"
rs1 = c.selectall(q1)
for i in range(len(rs1)):
q2 = "select itemid,quantity from sales_sub where transactionid='" + str(
rs1[i][0]) + "' order by itemid"
rs2 = c.selectall(q2)
transaction = ""
quantity = ""
profitstring = ""
tu = 0
for k in range(len(rs2)):
q3 = "select profit from product where product_id='" + str(
rs2[k][0]) + "'"
# print('q3',q3)
rs3 = c.selectone(q3)
profitstring = profitstring + str(rs3[0]) + ":"
transaction = transaction + str(rs2[k][0]) + ":"
quantity = quantity + str(rs2[k][1]) + ":"
a = int(rs3[0])
b = int(rs2[k][1])
cb = a * b
tu = tu + cb
transaction1.append(transaction)
qty.append(quantity)
profit.append(profitstring)
tu1.append(tu)
item = []
iprofit = []
count = 0
# item=' '.join(item).split()
# iprofit=' '.join(iprofit).split()
# print("it",item)
# print("pr",iprofit)
q5 = "select distinct itemid from sales_sub,sales_master where sales_master.transactionid=sales_sub.transactionid and sales_master.DATE BETWEEN '" + d1 + "' AND '" + d2 + "' order by itemid"
rs5 = c.selectall(q5)
for i in range(len(rs5)):
itmid.append(str(rs5[i][0]))
for i in range(len(itmid)):
q4 = "select profit from product where product_id='" + str(
itmid[i]) + "'"
rs4 = c.selectone(q4)
for j in range(len(transaction1)):
ar3 = transaction1[j].split(":")
ar4 = qty[j].split(":")
ar4 = ' '.join(ar4).split()
ar3 = ' '.join(ar3).split()
# print("ar3",ar3)
# print(ar4)
for k in range(len(ar3)):
if (str(itmid[i]) == str(ar3[k])):
q = int(ar4[k])
count = count + q
ut = int(rs4[0]) * count
itub.append(ut)
count = 0
# ubitem
for i in range(len(itub)):
item_id = itmid[i]
ubitem = 0
for j in range(len(transaction1)):
trans_action = transaction1[j]
tru = int(tu1[j])
sp = trans_action.split(':')
for m in range(len(sp)):
if (str(sp[m]) == item_id):
ubitem = ubitem + tru
break
ubitm.append(str(ubitem))
# ubfpe
for i in range(len(itub)):
item_id = itmid[i]
ubf_pe = 0
for j in range(len(transaction1)):
trans_action = transaction1[j]
quan_tity = qty[j]
prof_it = profit[j]
ar1 = trans_action.split(':')
ar2 = quan_tity.split(':')
ar3 = prof_it.split(':')
for m in range(len(ar1)):
if (str(ar1[m]) == item_id):
itemidPosition = m
k = itemidPosition
while (k >= 0):
x = int(ar2[k])
y = int(ar3[k])
z = x * y
ubf_pe = ubf_pe + z
k = k - 1
Ubfpe.append(str(ubf_pe))
session["trans"] = transaction1
session["prof"] = profit
session["qty"] = qty
session["tu"] = tu1
session["itmid"] = itmid
session["itu"] = itub
session["ubitm"] = ubitm
session["ubfp"] = Ubfpe
tid = []
for i in range(len(rs1)):
tid.append(str(rs1[i][0]))
return jsonify(data='ok',
tr=transaction1,
qun=qty,
prf=profit,
tuu=tu1,
r1=tid,
itid=itmid,
itu=itub,
ub=ubitm,
ubfp_e=Ubfpe)
def updatestatus():
wid = request.form["workid"]
c = conn()
qry = "UPDATE `order_assign` SET `status`='completed' WHERE `orderid`='" + wid + "'"
c.nonreturn(qry)
return jsonify(status='ok')
def getordererdproducts():
wid = request.form["wid"]
c = conn()
qry = "SELECT `product`.`product_id`,`product_name`,`price`,`profile_picture`,sales_sub.quantity FROM `product` INNER JOIN `sales_sub` ON `product`.`product_id`=`sales_sub`.`itemid` WHERE `transactionid`=" + wid + ""
res = c.jsonselect(qry)
return jsonify(status='ok', res=res)
def assignworks(wid):
c = conn()
qry = "select * from staff"
res = c.selectall(qry)
return render_template('admin_assignorder.html', data=res, r=wid)
def savecomment():
comment = request.form['comment']
pid = request.form["pid"]
qry = "select category_id,brandname from product where product_id=" + str(
pid) + ""
c = conn()
res = c.selectone(qry)
brandname = res[1]
catid = res[0]
uid = session["id"]
qry = "insert into comment (user_id,product_id,comment,date) values ('" + str(
uid) + "','" + str(pid) + "','" + comment + "',CURDATE())"
print(qry)
c = conn()
c.nonreturn(qry)
pstv = 0
ngtv = 0
ntl = 0
sid = SentimentIntensityAnalyzer()
ss = sid.polarity_scores(comment)
print(ss)
a = float(ss['pos'])
b = float(ss['neg'])
c = float(ss['neu'])
sts = ""
if a > b:
if a > c:
sts = "P"
else:
sts = "Ne"
else:
if b > c:
sts = "Ne"
else:
sts = "N"
c = conn()
if sts == "P":
c = conn()
ary = "select * from brand_score where uid='" + str(
uid) + "' and brandname='" + brandname + "'"
print(ary)
ress = c.selectall(ary)
print("----------------------------", ress)
if len(ress) == 0:
qry = "insert into brand_score(brandname,uid,scorepositive,scorenegative) values ('" + brandname + "','" + str(
uid) + "','1','0')"
c.nonreturn(qry)
else:
qry = "update brand_score set scorepositive=scorepositive+1 where uid='" + str(
uid) + "' and brandname='" + brandname + "'"
c.nonreturn(qry)
ary = "select * from cat_score where catid='" + str(
catid) + "' and uid='" + str(uid) + "'"
ress = c.selectall(ary)
print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>", ress)
if len(ress) == 0:
qry = "insert into cat_Score (catid,scorepos,scoreneg,uid) values ('" + str(
catid) + "','1','0','" + str(uid) + "')"
c.nonreturn(qry)
else:
qry = "update cat_score set scorepos=scorepos+1 where catid='" + str(
catid) + "' and uid='" + str(uid) + "'"
c.nonreturn(qry)
elif sts == "Ne":
print("helllllo")
ary = "select * from brand_score where uid='" + str(
uid) + "' and brandname='" + brandname + "'"
print("Abbbbbbbbbbbbbbbbbbbbbbbbbbbb", ary)
ress = c.selectall(ary)
if len(ress) == 0:
qry = "insert into brand_score(brandname,uid,scorepositive,scorenegative) values ('" + brandname + "','" + str(
uid) + "','0','1')"
c.nonreturn(qry)
else:
qry = "update brand_score set scorenegative=scorenegative+1 where uid='" + str(
uid) + "' and brandname='" + brandname + "'"
c.nonreturn(qry)
ary = "select * from cat_score where catid='" + str(
catid) + "' and uid='" + str(uid) + "'"
ress = c.selectall(ary)
if len(ress) == 0:
qry = "insert into cat_Score (catid,scorepos,scoreneg,uid) values ('" + catid + "','0','1','" + str(
uid) + "')"
c.nonreturn(qry)
else:
qry = "update cat_score set scoreneg=scoreneg+1 where catid='" + str(
catid) + "' and uid='" + str(uid) + "'"
c.nonreturn(qry)
return comments(pid)
def usergetcompleted():
uid = request.form["uid"]
qry = "select user.name,email,phone,place,date,total_amount,sales_master.transactionid from sales_master,user where sales_master.custid=user.user_id and sales_master.`transactionid` NOT IN ( SELECT `orderid` FROM `order_assign` where status='completed' and staffid='" + uid + "') "
c = conn()
res = c.jsonselect(qry)
return jsonify(status='ok', res=res)
def adminassignget():
qry = "select user.name,email,phone,place,date,total_amount,sales_master.transactionid from sales_master,user where sales_master.custid=user.user_id and sales_master.`transactionid` NOT IN ( SELECT `orderid` FROM `order_assign`) "
c = conn()
res = c.selectall(qry)
return render_template("assignordertostaff.html", res=res)
from flask import Flask, render_template, request, jsonify
from dbconnection import conn
from flask.globals import session
app = Flask(__name__)
app.secret_key = 'hi'
c = conn()
@app.route('/adminsample')
def adminsample():
return render_template(("admintemplate.html"))
@app.route('/aaa')
def loginpage():
return render_template(("admin_login.html"))
@app.route('/login', methods=["post"])
def login():
username = request.form["username"]
# print(username)
password = request.form["password"]
# print(password)
s = "select * from login where user_name='" + username + "' and password='******'"
res = c.selectone(s)
# print(res)
if res != None: