def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def report(request):
pageNum = mtu.getReqVal(request, "pageNum", "1")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
ucode = mtu.getReqVal(request, "ucode", "")
pname = mtu.getReqVal(request, "pname", "")
try:
if not start:
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
if not end:
end = datetime.datetime.today().strftime("%Y-%m-%d")
conn = mtu.getMysqlConn()
cur = conn.cursor()
condition = " where 1=1 "
condition += " and createtime BETWEEN '{start} 00:00:00' AND '{end} 23:59:59'"\
.format(start=start,end=end)
if ucode:
condition += " and ucode like '%{ucode}%'".format(ucode=ucode)
if pname:
condition += " and name like '%{pname}%'".format(pname=pname)
sql = "SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime,`name`,url," \
"SUM(CASE WHEN qtype = 1 THEN 1 ELSE 0 END) views," \
"SUM(CASE WHEN qtype = 2 THEN 1 ELSE 0 END) downs " \
"FROM bas_pur_log {condition} GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d'),`name` "\
"order by DATE_FORMAT(createtime,'%Y-%m-%d') DESC "\
.format(condition=condition)
cur.execute(sql)
rlist = cur.fetchall()
mtu.close(conn, cur)
except Exception as e:
rlist = []
print(e)
page = Paginator(rlist,
__EACH_PAGE_SHOW_NUMBER,
allow_empty_first_page=True).page(int(pageNum))
result = {
"page": page,
"pageNum": str(pageNum),
"start": start,
"end": end,
"ucode": ucode,
"pname": pname,
}
return render(request, "admin/sysConf_retail_purlog_report.html", result)
def report(request):
pageNum = mtu.getReqVal(request, "pageNum", "1")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
ucode = mtu.getReqVal(request, "ucode", "")
pname = mtu.getReqVal(request, "pname", "")
try:
if not start:
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
if not end:
end = datetime.datetime.today().strftime("%Y-%m-%d")
conn = mtu.getMysqlConn()
cur = conn.cursor()
condition = " where 1=1 "
condition += " and createtime BETWEEN '{start} 00:00:00' AND '{end} 23:59:59'"\
.format(start=start,end=end)
if ucode:
condition += " and ucode like '%{ucode}%'".format(ucode=ucode)
if pname:
condition += " and name like '%{pname}%'".format(pname=pname)
sql = "SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime,`name`,url," \
"SUM(CASE WHEN qtype = 1 THEN 1 ELSE 0 END) views," \
"SUM(CASE WHEN qtype = 2 THEN 1 ELSE 0 END) downs " \
"FROM bas_pur_log {condition} GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d'),`name` "\
"order by DATE_FORMAT(createtime,'%Y-%m-%d') DESC "\
.format(condition=condition)
cur.execute(sql)
rlist = cur.fetchall()
mtu.close(conn,cur)
except Exception as e:
rlist = []
print(e)
page = Paginator(rlist, __EACH_PAGE_SHOW_NUMBER, allow_empty_first_page=True).page(int(pageNum))
result = {"page": page, "pageNum": str(pageNum),"start": start,"end":end,"ucode":ucode,"pname":pname,}
return render(request, "admin/sysConf_retail_purlog_report.html", result)
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(shopsale) as shopsalesum, sum(ret) as retsum, (sum(ret) / sum(shopsale)) as retrate " \
"from `KGshopretsale` " \
"where ShopID!='C009' AND sdate between '" + monfirstday + "' and '" + yesterday + "' " \
"group by shopid " \
"order by shopid"
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {'sequenceNumber': '合计', 'shopid': '', 'shopname': '', 'shopsalesum': '', 'retsum': '',
'retrate': ''}
# 汇总总计（每月1日到当日）
tempshopsalesum = 0.00
tempretsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(abs(float("%0.2f" % float(listtop[i][key] * 100)))) + '%'
elif isinstance(row, decimal.Decimal) and key == 'retsum':
listtop[i][key] = abs(float("%0.2f" % float(listtop[i][key])))
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加汇总合计
tempshopsalesum += float(listtop[i]['shopsalesum'])
tempretsum += float(listtop[i]['retsum'])
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
sql = "select sdate, shopid, shopsale, ret, ret / shopsale as retc " \
"from `KGshopretsale` " \
"where shopid='" + listtop[i]['shopid'] + "' " \
"and sdate between '" + monfirstday + "' and '" + yesterday + "'"
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['ret']):
listtop[i]['retsum_' + date] = 0
else:
listtop[i]['retsum_' + date] = abs(float(item['ret']))
if 'retsum_' + date in listtopTotal:
listtopTotal['retsum_' + date] += listtop[i]['retsum_' + date]
else:
listtopTotal['retsum_' + date] = listtop[i]['retsum_' + date]
if (not item['shopsale']):
listtop[i]['shopsalesum_' + date] = 0
else:
listtop[i]['shopsalesum_' + date] = float(item['shopsale'])
if 'shopsalesum_' + date in listtopTotal:
listtopTotal['shopsalesum_' + date] += listtop[i]['shopsalesum_' + date]
else:
listtopTotal['shopsalesum_' + date] = listtop[i]['shopsalesum_' + date]
if (not item['retc']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(abs(float("%0.2f" % (item['retc'] * 100)))) + '%'
# 添加当日汇总
listtopTotal['retsum_' + date] = float("%0.2f" % (listtopTotal['retsum_' + date]))
listtopTotal['shopsalesum_' + date] = float("%0.2f" % (listtopTotal['shopsalesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" % (listtopTotal['retsum_' + date] / listtopTotal['shopsalesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['shopsalesum'] = float("%0.2f" % tempshopsalesum)
listtopTotal['retsum'] = float("%0.2f" % tempretsum)
listtopTotal['retrate'] = str(
"%0.2f" % float(float(listtopTotal['retsum'] / listtopTotal['shopsalesum']) * 100)) + '%'
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
# 退货明细
sqldetail = "select shopid, shopname, sdate, stime, listno, posid, cashierid, goodsid, goodsname, deptid, amount, sale " \
"from `KGshopretsaleitem` " \
"where sdate='" + yesterday + "' order by shopid"
cur = conn.cursor()
cur.execute(sqldetail)
retdetail = cur.fetchall()
# 格式化数据
for i in range(0, len(retdetail)):
for key in retdetail[i].keys():
row = retdetail[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal):
retdetail[i][key] = "%0.2f" % float(retdetail[i][key])
elif isinstance(row, datetime.datetime):
retdetail[i][key] = retdetail[i][key].strftime("%Y-%m-%d")
else:
retdetail[i][key] = retdetail[i][key]
mtu.close(conn, cur)
return locals()
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(costvalue) as costvaluesum, sum(reth) as rethsum, (sum(reth) / sum(costvalue)) as retrate " \
"from KGretshop " \
"where ShopID!='C009' AND sdate between '" + monfirstday + "' and '" + yesterday + "' " \
"group by shopid " \
"order by shopid"
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {'sequenceNumber': '合计', 'shopid': '', 'shopname': '', 'costvaluesum': '', 'rethsum': '',
'retrate': ''}
# 汇总总计（每月1日到当日）
tempcostvaluesum = 0.00
temprethsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(float("%0.2f" % float(listtop[i][key] * 100))) + '%'
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
# 添加汇总合计
tempcostvaluesum += float(listtop[i]['costvaluesum'])
temprethsum += float(listtop[i]['rethsum'])
sql = "select sdate, shopid, costvalue, reth, reth / costvalue as ret " \
"from `KGretshop` " \
"where shopid='" + listtop[i]['shopid'] + "' " \
"and sdate between '" + monfirstday + "' and '" + yesterday + "'"
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['reth']):
listtop[i]['rethsum_' + date] = 0
else:
listtop[i]['rethsum_' + date] = float(item['reth'])
if 'rethsum_' + date in listtopTotal:
listtopTotal['rethsum_' + date] += listtop[i]['rethsum_' + date]
else:
listtopTotal['rethsum_' + date] = listtop[i]['rethsum_' + date]
if (not item['costvalue']):
listtop[i]['costvaluesum_' + date] = 0
else:
listtop[i]['costvaluesum_' + date] = float(item['costvalue'])
if 'costvaluesum_' + date in listtopTotal:
listtopTotal['costvaluesum_' + date] += listtop[i]['costvaluesum_' + date]
else:
listtopTotal['costvaluesum_' + date] = listtop[i]['costvaluesum_' + date]
if (not item['ret']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(float("%0.2f" % (item['ret'] * 100))) + '%'
# 添加当日汇总
listtopTotal['rethsum_' + date] = float("%0.2f" % (listtopTotal['rethsum_' + date]))
listtopTotal['costvaluesum_' + date] = float("%0.2f" % (listtopTotal['costvaluesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" % (listtopTotal['rethsum_' + date] / listtopTotal['costvaluesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['costvaluesum'] = "%0.2f" % tempcostvaluesum
listtopTotal['rethsum'] = "%0.2f" % temprethsum
tempcandr = "%0.2f" % (temprethsum / tempcostvaluesum)
listtopTotal['retrate'] = str("%0.2f" % float(float(tempcandr) * 100)) + '%'
mtu.close(conn, cur)
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
return locals()
def query(yesterday):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(shopsale) as shopsalesum, sum(ret) as retsum, (sum(ret) / sum(shopsale)) as retrate " \
"from `KGshopretsale` " \
"where sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
"group by shopid order by shopid"\
.format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {
'sequenceNumber': '合计',
'shopid': '',
'shopname': '',
'shopsalesum': '',
'retsum': '',
'retrate': ''
}
# 汇总总计（每月1日到当日）
tempshopsalesum = 0.00
tempretsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(
abs(float(
"%0.2f" % float(listtop[i][key] * 100)))) + '%'
elif isinstance(row, decimal.Decimal) and key == 'retsum':
listtop[i][key] = abs(
float("%0.2f" % float(listtop[i][key])))
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加汇总合计
tempshopsalesum += float(listtop[i]['shopsalesum'])
tempretsum += float(listtop[i]['retsum'])
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
sql = "select sdate, shopid, shopsale, ret, ret / shopsale as retc " \
"from `KGshopretsale` " \
"where shopid='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
.format(shopID=listtop[i]['shopid'],monfirstday=monfirstday,yesterday=yesterday)
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['ret']):
listtop[i]['retsum_' + date] = 0
else:
listtop[i]['retsum_' + date] = abs(float(item['ret']))
if 'retsum_' + date in listtopTotal:
listtopTotal['retsum_' + date] += listtop[i]['retsum_' + date]
else:
listtopTotal['retsum_' + date] = listtop[i]['retsum_' + date]
if (not item['shopsale']):
listtop[i]['shopsalesum_' + date] = 0
else:
listtop[i]['shopsalesum_' + date] = float(item['shopsale'])
if 'shopsalesum_' + date in listtopTotal:
listtopTotal['shopsalesum_' +
date] += listtop[i]['shopsalesum_' + date]
else:
listtopTotal['shopsalesum_' +
date] = listtop[i]['shopsalesum_' + date]
if (not item['retc']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(
abs(float("%0.2f" % (item['retc'] * 100)))) + '%'
# 添加当日汇总
listtopTotal['retsum_' + date] = float(
"%0.2f" % (listtopTotal['retsum_' + date]))
listtopTotal['shopsalesum_' + date] = float(
"%0.2f" % (listtopTotal['shopsalesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" %
(listtopTotal['retsum_' + date] /
listtopTotal['shopsalesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['shopsalesum'] = float("%0.2f" % tempshopsalesum)
listtopTotal['retsum'] = float("%0.2f" % tempretsum)
listtopTotal['retrate'] = str("%0.2f" % float(
float(listtopTotal['retsum'] / listtopTotal['shopsalesum']) *
100)) + '%'
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
# 退货明细
sqldetail = "select shopid, shopname, sdate, stime, listno, posid, cashierid, goodsid, goodsname, deptid, amount, sale " \
"from `KGshopretsaleitem` " \
"where sdate='{yesterday}' and shopid in ({rbacDepart}) and LEFT(deptid,2) in ({rbacClass})" \
"order by shopid"\
.format(yesterday=yesterday,rbacDepart=rbacDepart,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sqldetail)
retdetail = cur.fetchall()
# 格式化数据
for i in range(0, len(retdetail)):
for key in retdetail[i].keys():
row = retdetail[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal):
retdetail[i][key] = "%0.2f" % float(retdetail[i][key])
elif isinstance(row, datetime.datetime):
retdetail[i][key] = retdetail[i][key].strftime("%Y-%m-%d")
else:
retdetail[i][key] = retdetail[i][key]
mtu.close(conn, cur)
return locals()
def query(yesterday):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(costvalue) as costvaluesum, sum(reth) as rethsum, (sum(reth) / sum(costvalue)) as retrate " \
"from KGretshop " \
"where sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
"group by shopid order by shopid"\
.format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {
'sequenceNumber': '合计',
'shopid': '',
'shopname': '',
'costvaluesum': '',
'rethsum': '',
'retrate': ''
}
# 汇总总计（每月1日到当日）
tempcostvaluesum = 0.00
temprethsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(
float("%0.2f" % float(listtop[i][key] * 100))) + '%'
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
# 添加汇总合计
tempcostvaluesum += float(listtop[i]['costvaluesum'])
temprethsum += float(listtop[i]['rethsum'])
sql = "select sdate, shopid, costvalue, reth, reth / costvalue as ret " \
"from `KGretshop` " \
"where shopid='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
.format(monfirstday=monfirstday,yesterday=yesterday,shopID = listtop[i]['shopid'])
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['reth']):
listtop[i]['rethsum_' + date] = 0
else:
listtop[i]['rethsum_' + date] = float(item['reth'])
if 'rethsum_' + date in listtopTotal:
listtopTotal['rethsum_' + date] += listtop[i]['rethsum_' +
date]
else:
listtopTotal['rethsum_' + date] = listtop[i]['rethsum_' + date]
if (not item['costvalue']):
listtop[i]['costvaluesum_' + date] = 0
else:
listtop[i]['costvaluesum_' + date] = float(item['costvalue'])
if 'costvaluesum_' + date in listtopTotal:
listtopTotal['costvaluesum_' +
date] += listtop[i]['costvaluesum_' + date]
else:
listtopTotal['costvaluesum_' +
date] = listtop[i]['costvaluesum_' + date]
if (not item['ret']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' +
date] = str(float("%0.2f" %
(item['ret'] * 100))) + '%'
# 添加当日汇总
listtopTotal['rethsum_' + date] = float(
"%0.2f" % (listtopTotal['rethsum_' + date]))
listtopTotal['costvaluesum_' + date] = float(
"%0.2f" % (listtopTotal['costvaluesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" %
(listtopTotal['rethsum_' + date] /
listtopTotal['costvaluesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['costvaluesum'] = "%0.2f" % tempcostvaluesum
listtopTotal['rethsum'] = "%0.2f" % temprethsum
tempcandr = "%0.2f" % (temprethsum / tempcostvaluesum)
listtopTotal['retrate'] = str(
"%0.2f" % float(float(tempcandr) * 100)) + '%'
mtu.close(conn, cur)
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
return locals()
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 昨天
# 获取部类编码
classcode = getclasscode()
# 获取所有商品的类别编码
allcode = getallcode()
# 获取门店编码
shopsid = getshopid()
# 查询某个部类下的子类编码
subcate = {}
# 将部类编码与子类编码组成dict
for x in classcode:
l = []
for y in allcode:
y = str(y)
if len(x) == 1 and y[:1] == x:
l.append(y)
if len(x) == 2 and y[:2] == x:
l.append(y)
subcate.setdefault(x, l)
subcate10 = subcate.get('10')
subcate11 = subcate.get('11')
subcate12 = subcate.get('12')
subcate13 = subcate.get('13')
subcate14 = subcate.get('14')
subcate15 = subcate.get('15')
subcate16 = subcate.get('16')
subcate17 = subcate.get('17')
subcate2 = subcate.get('2')
subcate3 = subcate.get('3')
subcate4 = subcate.get('4')
sqlsubcate10 = ','.join(subcate10)
sqlsubcate11 = ','.join(subcate11)
sqlsubcate12 = ','.join(subcate12)
sqlsubcate13 = ','.join(subcate13)
sqlsubcate14 = ','.join(subcate14)
sqlsubcate15 = ','.join(subcate15)
sqlsubcate16 = ','.join(subcate16)
sqlsubcate17 = ','.join(subcate17)
sqlsubcate2 = ','.join(subcate2)
sqlsubcate3 = ','.join(subcate3)
sqlsubcate4 = ','.join(subcate4)
sql10 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate10 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql11 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate11 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql12 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate12 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql13 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate13 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql14 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate14 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql15 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate15 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql16 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate16 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql17 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate17 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql2 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate2 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql3 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate3 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql4 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate4 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
# 连接数据库
conn = mtu.getMysqlConn()
cur10 = conn.cursor()
cur11 = conn.cursor()
cur12 = conn.cursor()
cur13 = conn.cursor()
cur14 = conn.cursor()
cur15 = conn.cursor()
cur16 = conn.cursor()
cur17 = conn.cursor()
cur2 = conn.cursor()
cur3 = conn.cursor()
cur4 = conn.cursor()
cur10.execute(sql10)
cur11.execute(sql11)
cur12.execute(sql12)
cur13.execute(sql13)
cur14.execute(sql14)
cur15.execute(sql15)
cur16.execute(sql16)
cur17.execute(sql17)
cur2.execute(sql2)
cur3.execute(sql3)
cur4.execute(sql4)
# 获取各部类下的销售数据
rows10 = cur10.fetchall()
rows11 = cur11.fetchall()
rows12 = cur12.fetchall()
rows13 = cur13.fetchall()
rows14 = cur14.fetchall()
rows15 = cur15.fetchall()
rows16 = cur16.fetchall()
rows17 = cur17.fetchall()
rows2 = cur2.fetchall()
rows3 = cur3.fetchall()
rows4 = cur4.fetchall()
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows10)):
for key in rows10[i].keys():
row = rows10[i][key]
if row is None:
rows10[i][key] = ''
else:
if isinstance(row, int):
rows10[i][key] = str(rows10[i][key])
elif isinstance(row, decimal.Decimal):
rows10[i][key] = "%0.2f" % float(rows10[i][key])
# 10 熟食部类
lis10 = []
unit10 = []
for sid in shopsid:
i = 0
for row in rows10:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis10.append(row)
i += 1
else:
continue
unit10.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows11)):
for key in rows11[i].keys():
row = rows11[i][key]
if row is None:
rows11[i][key] = ''
else:
if isinstance(row, int):
rows11[i][key] = str(rows11[i][key])
elif isinstance(row, decimal.Decimal):
rows11[i][key] = "%0.2f" % float(rows11[i][key])
# 11 水产部类
lis11 = []
unit11 = []
for sid in shopsid:
i = 0
for row in rows11:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis11.append(row)
i += 1
else:
continue
unit11.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows12)):
for key in rows12[i].keys():
row = rows12[i][key]
if row is None:
rows12[i][key] = ''
else:
if isinstance(row, int):
rows12[i][key] = str(rows12[i][key])
elif isinstance(row, decimal.Decimal):
rows12[i][key] = "%0.2f" % float(rows12[i][key])
# 12 蔬菜部类
lis12 = []
unit12 = []
for sid in shopsid:
i = 0
for row in rows12:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis12.append(row)
i += 1
else:
continue
unit12.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows13)):
for key in rows13[i].keys():
row = rows13[i][key]
if row is None:
rows13[i][key] = ''
else:
if isinstance(row, int):
rows13[i][key] = str(rows13[i][key])
elif isinstance(row, decimal.Decimal):
rows13[i][key] = "%0.2f" % float(rows13[i][key])
# 13 烘烤部类
lis13 = []
unit13 = []
for sid in shopsid:
i = 0
for row in rows13:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis13.append(row)
i += 1
else:
continue
unit13.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows14)):
for key in rows14[i].keys():
row = rows14[i][key]
if row is None:
rows14[i][key] = ''
else:
if isinstance(row, int):
rows14[i][key] = str(rows14[i][key])
elif isinstance(row, decimal.Decimal):
rows14[i][key] = "%0.2f" % float(rows14[i][key])
# 14 鲜肉部类
lis14 = []
unit14 = []
for sid in shopsid:
i = 0
for row in rows14:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis14.append(row)
i += 1
else:
continue
unit14.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows15)):
for key in rows15[i].keys():
row = rows15[i][key]
if row is None:
rows15[i][key] = ''
else:
if isinstance(row, int):
rows15[i][key] = str(rows15[i][key])
elif isinstance(row, decimal.Decimal):
rows15[i][key] = "%0.2f" % float(rows15[i][key])
# 15 干鲜干果部类
lis15 = []
unit15 = []
for sid in shopsid:
i = 0
for row in rows15:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis15.append(row)
i += 1
else:
continue
unit15.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows16)):
for key in rows16[i].keys():
row = rows16[i][key]
if row is None:
rows16[i][key] = ''
else:
if isinstance(row, int):
rows16[i][key] = str(rows16[i][key])
elif isinstance(row, decimal.Decimal):
rows16[i][key] = "%0.2f" % float(rows16[i][key])
# 16 主食厨房部类
lis16 = []
unit16 = []
for sid in shopsid:
i = 0
for row in rows16:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis16.append(row)
i += 1
else:
continue
unit16.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows17)):
for key in rows17[i].keys():
row = rows17[i][key]
if row is None:
rows17[i][key] = ''
else:
if isinstance(row, int):
rows17[i][key] = str(rows17[i][key])
elif isinstance(row, decimal.Decimal):
rows17[i][key] = "%0.2f" % float(rows17[i][key])
# 17 水果部类
lis17 = []
unit17 = []
for sid in shopsid:
i = 0
for row in rows17:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis17.append(row)
i += 1
else:
continue
unit17.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows2)):
for key in rows2[i].keys():
row = rows2[i][key]
if row is None:
rows2[i][key] = ''
else:
if isinstance(row, int):
rows2[i][key] = str(rows2[i][key])
elif isinstance(row, decimal.Decimal):
rows2[i][key] = "%0.2f" % float(rows2[i][key])
# 2 食品部类
lis2 = []
unit2 = []
for sid in shopsid:
i = 0
for row in rows2:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis2.append(row)
i += 1
else:
continue
unit2.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows3)):
for key in rows3[i].keys():
row = rows3[i][key]
if row is None:
rows3[i][key] = ''
else:
if isinstance(row, int):
rows3[i][key] = str(rows3[i][key])
elif isinstance(row, decimal.Decimal):
rows3[i][key] = "%0.2f" % float(rows3[i][key])
# 3 用品部类
lis3 = []
unit3 = []
for sid in shopsid:
i = 0
for row in rows3:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis3.append(row)
i += 1
else:
continue
unit3.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for x in range(0, len(rows4)):
for key in rows4[x].keys():
row = rows4[x][key]
if row is None:
rows4[x][key] = ''
else:
if isinstance(row, int):
rows4[x][key] = str(rows4[x][key])
elif isinstance(row, decimal.Decimal):
rows4[x][key] = "%0.2f" % float(rows4[x][key])
# 将退货数据过滤，值为负
rows4filter = []
for i in range(0, len(rows4)):
if float(rows4[i]['SaleQty']) < 0:
continue
else:
rows4filter.append(rows4[i])
# 4 家电部类
lis4 = []
templist = []
for sid in shopsid:
i = 0
for row in rows4filter:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis4.append(row)
i += 1
else:
continue
templist.append(i)
# 关闭数据库
mtu.close(conn, cur10)
mtu.close(conn, cur11)
mtu.close(conn, cur12)
mtu.close(conn, cur13)
mtu.close(conn, cur14)
mtu.close(conn, cur15)
mtu.close(conn, cur16)
mtu.close(conn, cur17)
mtu.close(conn, cur2)
mtu.close(conn, cur3)
mtu.close(conn, cur4)
return locals()