def showEcharts():
db = MhDatabases()
#从数据库中查询数据---生成Json格式
result = db.executeQuery(
"select sort,sum(cast(total as decimal(18,1))) from pcr group by sort "
)
sorts = []
for i in result:
sorts.append(i[0])
list1 = []
for i in sorts:
r = db.executeQuery(
"select name,sum(number) from pcr where sort=%s group by name ",
[i])
allgoods = []
for j in r:
goods = {}
goods["name"] = j[0]
goods["number"] = int(j[1])
allgoods.append(goods)
list1.append(allgoods)
list2 = []
for i in result:
dict = {}
dict["sort"] = i[0]
dict["total"] = float(i[1])
list2.append(dict)
res = {"result": list2, "goodsresult": list1}
content = json.dumps(res)
return content
def product():
# 连接数据库
db = MhDatabases()
if request.method == 'GET':
# 从数据库中返回最近十条商品信息并返回
result = db.executeQuery("select * from goods")
return render_template("product.html", result=result)
else:
# 获取商品ID
proID = request.form.get("proID")
print("proID", proID)
pid = request.form.get("pid")
print("pid", pid)
#获取操作类型
btntype = request.form.get("btntype")
print("btntype", btntype)
if proID:
# 从数据库获取商品信息并返回
result = db.executeQuery("select * from goods where gid=%s",
[product])
return render_template("product.html", result=result)
if pid and btntype == "pread":
# 从数据库获取商品信息，返回商品查看界面
result = db.executeQuery("select * from goods where gid=%s", [pid])
return render_template("productView.html", result=result)
if pid and btntype == "pupdate":
# 返回商品修改界面
return redirect(url_for('productUpdate', pid=pid))
def shoppingcart():
db = MhDatabases()
# 获取微信小程序端传来的用户id
id = str(json.loads(request.values.get("id")))
print(id)
result = db.executeQuery(
"select image,gid,name,sum(number),uprice from pcr where buyerid=%s and isdelete=0 and optype=5 group by name",
[id])
# 返回查询的结果:
if len(result) != 0:
res = []
for i in result:
dict = {}
result1 = db.executeQuery("select number from goods where gid=%s ",
[i[1]])
dict['imgSrc'] = "http://139.217.130.233/" + i[0]
dict['title'] = i[2]
dict['gid'] = i[1]
dict['price'] = i[4]
dict['quantity'] = int(i[3])
dict['max'] = result1[0][0]
res.append(dict)
else:
res = "空"
return json.dumps(res)
def billList():
# 连接数据库
db = MhDatabases()
if request.method == 'GET':
# 从数据库中返回最近十条交易记录并返回
result = db.executeQuery("select * from pcr")
return render_template("billList.html", result=result)
else:
# 获取商品ID或者名称
option = request.values.get("searchoption")
product = request.form.get("product")
print(option)
print(product)
if option == "请输入商品的ID":
# 根据商品ID从数据库获取交易信息，如果不存在返回“无此交易记录”
result = db.executeQuery("select * from pcr where gid=%s",
[product])
if len(result) == 0:
result = "无此交易记录"
else:
# 根据商品名称从数据库获取交易信息，如果不存在返回“无此交易记录”
result = db.executeQuery("select * from pcr where name=%s",
[product])
if len(result) == 0:
result = "无此交易记录"
return render_template("billList.html", result=result)
def sortchart():
db = MhDatabases()
# 获取微信小程序端传来的id
id = str(json.loads(request.values.get("id")))
#从数据库中查询数据---生成Json格式
result = db.executeQuery(
"select sort,sum(cast(total as decimal(18,1))) from pcr where optype=10 and buyerid=%s and str_to_date(time,'%%Y/%%m/%%d %%H:%%i:%%s')>date_format(curdate(),'%%Y/%%m/01 00:00:00') group by sort",
[id])
result2 = db.executeQuery("select sort from goods group by sort")
r = []
for i in result:
r.append(i[0])
ress = []
totalprice = 0
for i in result:
dict = {}
dict["sort"] = i[0]
dict["total"] = float(i[1])
totalprice += float(i[1])
ress.append(dict)
for i in range(0, len(result2)):
if result2[i][0] not in r:
dict = {}
dict["sort"] = result2[i][0]
dict["total"] = 0
ress.append(dict)
t = round(totalprice, 2)
res = {"result": ress, "totalprice": t}
content = json.dumps(res)
return content
def billList():
db = MhDatabases()
if request.method == 'GET':
result = db.executeQuery(
"select image,gid,name ,sort,uprice,sum(number),sum(cast(total as decimal(18,1))) from pcr group by name"
) # 从数据库中查询账单记录并返回
return render_template("billList.html", result=result)
else:
product = request.form.get("product") # 从web获取管理员输入的商品ID或者名称
print(product)
# 从数据库中查询获取账单记录
result1 = db.executeQuery(
"select image,gid,name ,sort,uprice,sum(number),sum(cast(total as decimal(18,1))) from pcr where gid=%s group by name",
[product])
result2 = db.executeQuery(
"select image,gid,name ,sort,uprice,sum(number),sum(cast(total as decimal(18,1))) from pcr where name=%s group by name",
[product])
if len(result1) != 0:
result = result1
elif len(result2) != 0:
result = result2
else:
result = "暂无记录"
return render_template("billList.html", result=result)
def productAdd():
# 连接数据库
db = MhDatabases()
if request.method == 'GET':
return render_template("productAdd.html")
else:
# 获取要添加的商品信息
product = []
product.append(request.form.get("productId"))
product.append(request.form.get("productName"))
product.append(request.form.get("type"))
product.append(int(request.form.get("number")))
product.append(float(request.form.get("price")))
product.append(request.form.get("dateofproduce"))
product.append(request.form.get("dateofbad"))
print(product)
# 查询数据库中是否已存在该商品，如果不存在则向数据库添加商品信息
result1 = db.executeQuery("select * from goods where gid=%s",
[product[0]])
result2 = db.executeQuery("select * from goods where name=%s",
[product[1]])
if len(result1[0]) == 0 and len(result2[0]) == 0:
result = db.executeUpdate(
"insert into goods values(%s,%s,null,%s,%s,%s,%s,%s,null)",
product)
else:
result = 0
return render_template("productAdd.html", data=result)
def allorders():
# 连接数据库
db = MhDatabases()
# 获取微信小程序端传来的ID
id = str(json.loads(request.values.get("id")))
print("id", id)
# 根据ID在数据库中查询用户数据和表段名称
result = db.executeQuery("select * from pcr where buyerid=%s", [id])
column = db.executeQuery(
"select column_name from information_schema.COLUMNS where table_name=%s order by ordinal_position",
['pcr'])
print(result)
# print (column)
# 返回查询到的结果:
# 如果有订单，按时间整合发送给微信小程序端
if len(result[0]) != 0:
time = result[0][5]
total = []
total2 = []
timess = []
timess.append(time)
for i in range(0, len(result)):
n = i + 1
if n < len(result):
if result[i][5] == time and result[n][5] == time:
a = {}
for j in range(0, len(result[i])):
a[column[j][0]] = result[i][j]
total.append(a)
if result[i][5] == time and result[n][5] != time:
time = result[n][5]
timess.append(time)
a = {}
for j in range(0, len(result[i])):
a[column[j][0]] = result[i][j]
total.append(a)
total2.append(total)
total = []
else:
a = {}
for j in range(0, len(result[i])):
a[column[j][0]] = result[i][j]
total.append(a)
total2.append(total)
res = []
for i in range(0, len(total2)):
order = {"times": total2[i], "timess": timess[i]}
res.append(order)
print(res)
return json.dumps(res)
# 如果没有订单，返回暂无订单
else:
res = "暂无订单"
return json.dumps(res)
def allorders():
db = MhDatabases()
# 获取微信小程序端传来的ID
id = str(json.loads(request.values.get("id")))
print("id", id)
# 根据ID在数据库中查询用户订单数据
result = db.executeQuery(
"select gid,name,sum(number),uprice,image,time from pcr where optype=10 and isdelete=0 and buyerid=%s group by name,time",
[id])
timeresult = db.executeQuery(
"select time from pcr where optype=10 and isdelete=0 and buyerid=%s group by time",
[id])
column = ['gid', 'name', 'number', 'uprice', 'image']
print(result)
timess = []
for i in timeresult:
timess.append(i[0])
# 返回查询到的结果:
# 如果有订单，按时间整合订单信息，转为json格式发送给微信小程序端订单的详细信息
if len(result) != 0:
total = []
total2 = []
totalprice = []
for i in range(0, len(timeresult)):
totalprice.append(0)
for t in range(0, len(timess)):
for i in range(0, len(result)):
if result[i][5] == timess[t]:
a = {}
a[column[0]] = result[i][0]
a[column[1]] = result[i][1]
a[column[2]] = int(result[i][2])
a[column[1]] = result[i][3]
a[column[4]] = "http://139.217.130.233/" + result[i][4]
totalprice[t] += float(result[i][2]) * result[i][3]
total.append(a)
total2.append(total)
total = []
totalprice[t] = round(totalprice[t], 2)
res = []
for i in range(0, len(total2)):
order = {
"orders": total2[i],
"time": timess[i],
"totalprice": totalprice[i]
}
res.append(order)
print(res)
return json.dumps(res)
# 如果没有订单，返回暂无订单
else:
res = "暂无订单"
return json.dumps(res)
def update(pid):
db = MhDatabases()
if request.method == 'GET':
result = db.executeQuery("select * from goods where gid=%s",
[pid]) # 从数据库获取商品信息并返回
return render_template("update.html", result=result)
else:
gid = request.form.get("productId") # 获取web前端发送的要删除的商品ID
print(gid)
if gid:
db.executeUpdate("delete from goods where gid=%s",
[gid]) # 在数据库中删除商品信息
return redirect(url_for('product'))
else:
# 获取web前端发送的商品要修改的信息
product = []
product.append(request.form.get("type"))
product.append(int(request.form.get("number")))
product.append(float(request.form.get("price")))
product.append(request.form.get("location"))
print(product)
# 在数据库中修改商品信息
db.executeUpdate(
"update goods set sort=%s,number=%s,uprice=%s,location=%s where gid=%s",
[product[0], product[1], product[2], product[3], pid])
return redirect(url_for('product'))
def ordersdetail():
db = MhDatabases()
# 获取微信小程序端传来的ID和订单完成时间
id = str(json.loads(request.values.get("id")))
time = str(json.loads(request.values.get("time")))
print("id", id, " time", time)
# 根据ID和订单完成时间查询在数据库中的数据
result = db.executeQuery(
"select gid,name,sum(number),uprice,image from pcr where buyerid=%s and time=%s and isdelete=0 and optype=10 group by name",
[id, time])
# 返回查询的结果:
if len(result) != 0:
list = []
totalprice = 0
for i in result:
dict = {}
dict['gid'] = i[0]
dict['name'] = i[1]
dict['number'] = int(i[2])
dict['uprice'] = i[3]
dict['total'] = float(i[2]) * i[3]
dict['image'] = "http://139.217.130.233/" + i[4]
list.append(dict)
res = {"orders": list}
else:
res = "无订单"
return json.dumps(res)
def recommendmain():
db = MhDatabases()
# 获取微信小程序端传来的用户id
id = str(json.loads(request.values.get("id")))
print(id)
res = []
mh = mhitemCF()
data = mh.getUidScoreBid() # 获得数据
W = mh.similarity(data)
# 算物品相似矩阵
relist = mh.recommandList(data, W, id, 10, 10)
# 推荐
if len(relist) == 0:
res = "暂无推荐"
else:
for i in relist:
result = db.executeQuery(
"select gid,name,image,uprice from goods where name=%s",
[i[0]]) # 获取推荐商品详细信息
dict = {}
dict['gid'] = result[0][0]
dict['name'] = result[0][1]
dict['image'] = "http://139.217.130.233/" + result[0][2]
dict['uprice'] = result[0][3]
res.append(dict)
return json.dumps(res)
def idcodelogin():
db = MhDatabases()
# 获取微信小程序端传来的用户id
id = str(json.loads(request.values.get("id")))
print(id)
result = db.executeQuery("select * from user where phone=%s",
[id]) # 查询id在数据库是否已存在
if len(result) == 0:
db.executeUpdate("insert into user values(%s,null,null,null) ", [id])
res = "成功"
return json.dumps(res)
def mine():
db = MhDatabases()
# 获取微信小程序端传来的用户id
id = str(json.loads(request.values.get("id")))
print(id)
result = db.executeQuery("select name from user where phone=%s",
[id]) # 查询id在数据库中的name
if len(result) != 0:
if result[0][0] == None:
res = "用户昵称为空"
else:
res = result[0][0]
else:
res = "用户昵称为空"
return json.dumps(res)
def showEcharts():
#从数据库中查询数据---生成Json格式
helper = MhDatabases()
result = helper.executeQuery(
"select name,sum(total) from pcr group by name ", [])
print(result)
list = []
for i in result:
dict = {}
dict["name"] = i[0] #上衣
dict["total"] = i[1] #966
list.append(dict)
res = {"result": list}
content = json.dumps(res)
return content
def product():
db = MhDatabases()
if request.method == 'GET':
result = db.executeQuery("select * from goods") # 从数据库中查询商品记录并返回
return render_template("product.html", result=result)
else:
proID = request.form.get("proid") # 获取web端发送的需要查询的商品ID
print("proID", proID)
pid = request.form.get("pid") # 获取web端发送的需要修改/查看的商品ID
print("pid", pid)
outofdate = request.form.get("outofdate") # 获取web端发送的需要修改/查看的商品ID
if outofdate == "search":
todaydate = time.strftime("%Y-%m-%d")
res = db.executeQuery("select * from goods")
result = []
for i in res:
if todaydate > i[7]:
result.append(i)
return render_template("product.html", result=result)
if proID:
result1 = db.executeQuery("select * from goods where gid=%s",
[proID]) # 从数据库获取查询的商品信息并返回
result2 = db.executeQuery("select * from goods where name=%s",
[proID])
if len(result1) != 0:
result = result1
elif len(result2) != 0:
result = result2
else:
result = "暂无记录"
return render_template("product.html", result=result)
if pid:
return redirect(url_for('update', pid=pid)) # 跳转到商品修改界面
def add():
db = MhDatabases()
if request.method == 'GET':
return render_template("add.html")
else:
# 获取web前端发送要添加的商品信息
img = request.files.get("myFile")
fname = img.filename
filepath = "./static/img/" + fname
img.save("C:/macrohardweb/static/img/" + fname)
product = []
product.append(request.form.get("productId"))
product.append(request.form.get("productName"))
product.append(filepath)
product.append(request.form.get("type"))
product.append(int(request.form.get("number")))
product.append(float(request.form.get("price")))
product.append(request.form.get("dateofproduce"))
product.append(request.form.get("dateofbad"))
product.append(request.form.get("location"))
print(product)
# 查询数据库中是否已存在该商品，如果不存在则向数据库添加商品信息
result1 = db.executeQuery("select * from goods where gid=%s",
[product[0]])
result2 = db.executeQuery("select * from goods where name=%s",
[product[1]])
result3 = db.executeQuery("select * from goods where image=%s",
[product[2]])
if len(result1) == 0 and len(result2) == 0 and len(result3) == 0:
result = db.executeUpdate(
"insert into goods values(%s,%s,%s,%s,%s,%s,%s,%s,%s)",
product)
else:
result = 0
return render_template("add.html", data=result)
def searchgoods():
db = MhDatabases()
search = str(json.loads(request.values.get("search")))
result = db.executeQuery(
"select * from goods where name like '%%%%%s%%%%'" % (search))
res = []
if len(result) != 0:
for i in result:
dict = {}
dict['gid'] = i[0]
dict['name'] = i[1]
dict['image'] = "http://139.217.130.233/" + i[2]
dict['uprice'] = i[5]
res.append(dict)
return json.dumps(res)
else:
res = "空"
return json.dumps(res)
def userregister():
db = MhDatabases()
# 获取微信小程序端传来的注册用户的id，password，name，sex
id = str(json.loads(request.values.get("id")))
password = str(json.loads(request.values.get("password")))
name = str(json.loads(request.values.get("name")))
sex = str(json.loads(request.values.get("sex")))
print("name", name, " sex", sex, " id", id, " password", password)
# 判断id是否已注册，将信息存入数据库
result1 = db.executeQuery("select * from user where phone=%s", [id])
if len(result1) == 0:
result = db.executeUpdate("insert into user values(%s,%s,%s,%s)",
[id, password, sex, name])
res = "注册成功"
else:
res = "注册失败，手机号已注册"
return json.dumps(res)
def cartsettle():
db = MhDatabases()
# 获取微信小程序端传来的商品id，订单时间，结算商品数组
id = str(json.loads(request.values.get("id")))
time = str(json.loads(request.values.get("time")))
scart = json.loads(request.values.get("scart"))
print(id, time, scart)
for i in scart:
order = []
result = db.executeQuery(
"select name,image,sort,number from goods where gid=%s",
[i['gid']]) # 获取商品信息
print(result)
if len(result) != 0:
order.append(result[0][1]) # image
order.append(i['gid']) # gid
order.append(result[0][0]) # name
order.append(result[0][2]) # sort
gnumber = result[0][3] - int(i['quantity']) # gnumber
order.append(int(i['quantity'])) # number
order.append(float(i['price'])) # uprice
order.append(int(i['quantity']) * float(i['price'])) # total
order.append(time) # time
order.append(id) # buyerid
order.append(10) # optype
order.append(0) # isdelete
if gnumber < 0:
res = "库存不足"
else:
db.executeUpdate("update goods set number=%s where gid=%s",
[gnumber, order[1]]) # 修改商品库存
db.executeUpdate(
"update pcr set isdelete=1 where gid=%s and buyerid=%s and optype=5",
[order[1], order[8]]) # 修改加入购物车订单isdelete项
db.executeUpdate(
"insert into pcr values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
order) # 添加购买记录
res = "结算成功"
else:
res = "结算失败"
return json.dumps(res)
def goodsdetail():
db = MhDatabases()
# 获取微信小程序端传来的商品id
gid = str(json.loads(request.values.get("gid")))
print(gid)
result = db.executeQuery(
"select image,name,number,uprice,location from goods where gid=%s",
[gid]) # 从数据库中查询商品详细信息
res = []
if len(result) != 0:
for i in result:
dict = {}
dict['imgSrc'] = "http://139.217.130.233/" + i[0]
dict['name'] = i[1]
dict['price'] = i[3]
dict['storage'] = i[2]
dict['location'] = i[4]
res.append(dict)
else:
res = "空"
return json.dumps(res)
def score():
# 连接数据库
db = MhDatabases()
# 获取微信小程序端传来的ID和password
id = str(json.loads(request.values.get("id")))
password = str(json.loads(request.values.get("password")))
name = str(json.loads(request.values.get("name")))
sex = str(json.loads(request.values.get("sex")))
print("name", name, " sex", sex, " id", id, " password", password)
# 根据ID在数据库中查询用户数据
result1 = db.executeQuery("select * from goods where gid=%s", [id])
if len(result1[0]) == 0:
result = db.executeUpdate("insert into goods values(%s,%s,%s,%s,)",
[id, password, sex, name])
else:
result = 0
if result == 1:
res = "注册成功"
else:
res = "注册失败，手机号已注册"
return json.dumps(res)
def score():
# 连接数据库
db = MhDatabases()
# 获取微信小程序端传来的ID和password
id = str(json.loads(request.values.get("id")))
password = str(json.loads(request.values.get("password")))
print("id", id)
print("password", password)
# 根据ID在数据库中查询用户数据
result = db.executeQuery("select * from user where phone=%s", [id])
print(result)
if len(result) != 0:
if result[0][1] == password:
# 如果匹配返回登录成功
res = '登录成功'
return json.dumps(res)
else:
# 如果不匹配返回账号或密码错误
res = '账号或密码错误'
return json.dumps(res)
def cartadd():
db = MhDatabases()
# 获取微信小程序端传来的用户id，商品信息
id = str(json.loads(request.values.get("id")))
gid = str(json.loads(request.values.get("gid")))
quantity = int(json.loads(request.values.get("quantity")))
time = str(json.loads(request.values.get("time")))
result = db.executeQuery(
"select image,name,sort,uprice,number from goods where gid=%s",
[gid]) # 获取商品在数据库中的详细信息
print(result)
if result[0][4] - quantity < 0:
res = "库存不足"
else:
order = []
price = result[0][3]
order.append(result[0][0])
order.append(gid)
order.append(result[0][1])
order.append(result[0][2])
order.append(quantity)
order.append(price)
order.append(quantity * price)
order.append(time)
order.append(id)
order.append(5)
order.append(0)
r = db.executeUpdate(
"insert into pcr values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
order) # 添加加入购物车记录
if r == 1:
res = "添加成功"
else:
res = "添加失败"
return json.dumps(res)
def productUpdate(pid):
# 连接数据库
db = MhDatabases()
if request.method == 'GET':
# 从数据库获取商品信息并返回
result = db.executeQuery("select * from goods where gid=%s", [pid])
return render_template("productUpdate.html", result=result)
else:
# 获取商品可修改的信息
product = []
pid = request.form.get("productId")
product.append(request.form.get("type"))
product.append(int(request.form.get("number")))
product.append(float(request.form.get("price")))
print(product)
# 在数据库中修改商品信息
db.executeUpdate("update goods set sort=%s where gid=%s",
[product[0], pid])
db.executeUpdate("update goods set number=%s where gid=%s",
[product[1], pid])
db.executeUpdate("update goods set uprice=%s where gid=%s",
[product[2], pid])
return redirect(url_for('product'))
def hotmain():
db = MhDatabases()
# 获取微信小程序端传来的用户id
id = str(json.loads(request.values.get("id")))
print(id)
res = []
h = mhHot()
hot = h.hot(h.getPcr(), 10) # 获取当月热销前三的商品名称和销量
if len(hot) == 0:
res = "暂无商品"
else:
for i in hot:
result = db.executeQuery(
"select gid,name,image,uprice from goods where name=%s",
[i[0]]) # 获取热销商品详细信息
dict = {}
dict['gid'] = result[0][0]
dict['name'] = result[0][1]
dict['image'] = "http://139.217.130.233/" + result[0][2]
dict['uprice'] = result[0][3]
dict['sales'] = i[1]
res.append(dict)
return json.dumps(res)
from MhDatabses import MhDatabases
db=MhDatabases()
result=db.executeQuery("select time from pcr where name=%s group by time",["农夫山泉"])
print(result)
def getPcr(self):
db = MhDatabases()
result = db.executeQuery("select * from pcr")
return result