def getStartAndEndDate(contracttype,payTypeName): stime = Constants.ERP_START_TIME #结算日期 #erp系统使用的开始时间 pstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d") # if contracttype == "d": # #上月底 # pend = (datetime.date.today().replace(day=1) - datetime.timedelta(1)).strftime("%Y-%m-%d") # else: #当前日期 pend = datetime.datetime.now().strftime("%Y-%m-%d") if contracttype == "g": #购销 #单据日期 cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d") cend = datetime.datetime.now().strftime("%Y-%m-%d") else: #单据日期 if "月" in payTypeName and "半月结" not in payTypeName: if "月结30天" in payTypeName or "月结45天" in payTypeName: n = -2 #前两个月一整月 elif "月结60天" in payTypeName: n = -3 #前三个月一整月 else: n = -1 #前一个月一整月 cstart = DateUtil.get_firstday_month(n) cend = DateUtil.get_lastday_month(n) else: cstart = datetime.date(stime[0],stime[1],stime[2]).strftime("%Y-%m-%d") cend = datetime.datetime.now().strftime("%Y-%m-%d") return pstart,pend,cstart,cend
def query(yesterday): rbac = caches['redis2'].get('rbac_role') rbacDepart = rbac['depart'] if len(rbacDepart): rbacDepart = json.loads(rbacDepart) rbacDepart = rbacDepart['sub'][0:len(rbacDepart['sub']) - 1] rbacDepartList = rbacDepart.split(',') rbacDepart = '"' + '","'.join(rbacDepartList) + '"' rbacClassList = [] rbacClass = '' rbacCategory = rbac['category'] if len(rbacCategory): rbacCategory = rbacCategory.replace('},', '}$') rbacCategoryList = rbacCategory.split('$') for category in rbacCategoryList: category = json.loads(category) ClassStr = category['sub'][0:len(category['sub']) - 1] ClassList = ClassStr.split(',') ClassStr = '"' + '","'.join(ClassList) + '"' rbacClassList += ClassList rbacClass += ClassStr + ',' rbacClass = rbacClass[0:len(rbacClass) - 1] yearandmon = DateUtil.getyearandmonth() # 当前月份第一天 monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1]) # 当前月份最后一天 monlastday = DateUtil.get_lastday_month() # 今天 today = DateUtil.todaystr() for C in rbacClassList: if C == '10': lis10, unit10 = initDepartData('10', yesterday) elif C == '11': lis11, unit11 = initDepartData('11', yesterday) elif C == '12': lis12, unit12 = initDepartData('12', yesterday) elif C == '13': lis13, unit13 = initDepartData('13', yesterday) elif C == '14': lis14, unit14 = initDepartData('14', yesterday) elif C == '15': lis15, unit15 = initDepartData('15', yesterday) elif C == '16': lis16, unit16 = initDepartData('16', yesterday) elif C == '17': lis17, unit17 = initDepartData('17', yesterday) elif C == '2': lis2, unit2 = initDepartData('2', yesterday) elif C == '3': lis3, unit3 = initDepartData('3', yesterday) elif C == '4': lis4, unit4 = initDepartData('4', yesterday) return locals()
def index(request): ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") timeStart = datetime.date.today() - datetime.timedelta(days=1) timeEnd = datetime.date.today() # 操作日志 qtype = mth.getReqVal(request, "qtype", "1") if not qtype: qtype = "1" key_state = mth.getReqVal(request, "key_state", '') if qtype == '2' and (not key_state or key_state != '2'): qtype = '1' path = request.path today = datetime.datetime.today() BasPurLog.objects.create(name="单品报损超100", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(timeStart, timeEnd) return render(request, 'report/abnormal/loss_rate.html', data) else: yesterday = DateUtil.get_day_of_day(-1) name = '_abnormal_loss_rate100' fname = yesterday.strftime('%m.%d') + name + ".xls" return export(fname, timeStart, timeEnd)
def index(request): exceltype = mtu.getReqVal(request, "exceltype", "2") # 操作日志 if exceltype == '2': qtype = "1" else: qtype = "2" key_state = mtu.getReqVal(request, "key_state", '') if exceltype == '1' and (not key_state or key_state != '2'): exceltype = '2' path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="顾客退货率", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) # 昨天 yesterday = DateUtil.get_day_of_day(-1) if exceltype == '1': fname = yesterday.strftime("%m.%d") + "_daily_customer_returns.xls" return export(fname, str(yesterday)) else: data = query(str(yesterday)) return render(request, "report/daily/customer_returns.html", data)
def writeDataToSheet2(wb, resList, title): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)], [("报表日期", 11, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("管理类别码", 2, 1, 1), ("管理类别名称", 3, 1, 1), ("小类编码", 4, 1, 1), ("小类名称", 5, 1, 1), ("商品编码", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品规格", 8, 1, 1), ("销售单位", 9, 1, 1), ("负库存数量", 10, 1, 1), ("负库存金额", 11, 1, 1), ("解释原因", 12, 1, 1), ("解决方案", 13, 1, 1), ("解决时间", 14, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'sgroupid', 'sgroupname', 'deptid', 'deptname', 'goodsid', 'goodsname', 'spec', 'unitname', 'qty', 'costvalue', 'reason1', 'reason2', 'reason3' ] widthList = [ 600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000, 2000, 2000 ] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
def index(request): yesterday = DateUtil.get_day_of_day(-1) qtype = mtu.getReqVal(request, "qtype", "1") # 操作日志 if not qtype: qtype = "1" path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="单张小票退货超300", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(yesterday) return render(request, "report/abnormal/ret_shopping_rec_300.html", data) else: fname = yesterday.strftime( "%m.%d") + "_abnormal_ret_shopping_rec_300.xls" return export(fname, yesterday)
def index(request): date = DateUtil.get_day_of_day(-1) start = (date.replace(day=1)).strftime("%Y-%m-%d") yesterday = date.strftime("%Y-%m-%d") lastDay = calendar.monthrange(date.year, date.month)[1] end = "{year}-{month}-{day}".format(year=date.year, month=date.month, day=lastDay) qtype = mtu.getReqVal(request, "qtype", "1") # 操作日志 if not qtype: qtype = "1" key_state = mtu.getReqVal(request, "key_state", '') if qtype == '2' and (not key_state or key_state != '2'): qtype = '1' path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="超市运营日分解", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(date,start,end,yesterday,lastDay) return render(request, "report/daily/group_opt_decompt.html",data) else: name = '_daily_group_operate_decompt' fname = date.strftime('%m.%d')+name+".xls" return export(fname,date,start,end,yesterday,lastDay)
def writeDataToSheet2(wb, retdetail): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True) titles = [ [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)], [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1), ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1), ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1), ("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)], ] keylist = [ 'shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'goodsid', 'goodsname', 'deptid', 'amount', 'sale' ] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet2, titles, keylist, widthList) mtu.insertCell2(sheet2, 3, retdetail, keylist, None) titlesLen = len(titles) listTopLen = len(retdetail)
def writeDataToSheet11(wb, lis4): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet4 = wb.add_sheet("4家电", cell_overwrite_ok=True) titles = [ [("(%s月%s日)各店日销售排名日报(家电)" % (month, date.day), 0, 1, 13)], [("门店", 0, 2, 1), ("排名", 1, 2, 1), ("商品编码", 2, 2, 1), ("商品名称", 3, 2, 1), ("销售数量", 4, 2, 1), ("销售金额", 5, 2, 1), ("成本金额", 6, 2, 1), ("毛利", 7, 2, 1), ("毛利率%", 8, 2, 1), ("当前库存数量", 9, 2, 1), ("当前库存金额", 10, 2, 1), ("成本价", 11, 2, 1), ("平均售价", 12, 2, 1)], ] keylist = ['shopid', 'paiming', 'goodsid', 'goodsname', 'SaleQty', 'SaleValue', 'SaleCost', 'gpvalue', 'gprate', \ 'qty', 'costvalue', 'cprice', 'price'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet4, titles, keylist, widthList) mtu.insertCell2(sheet4, 3, lis4, keylist, None) titlesLen = len(titles) listTopLen = len(lis4)
def index(request): yesterday = DateUtil.get_day_of_day(-1) qtype = mtu.getReqVal(request, "qtype", "1") #操作日志 if not qtype: qtype = "1" path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="商品连续3天负毛利", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(yesterday) return render(request, "report/abnormal/negprofit_past3days.html", {"rlist": list(data)}) else: fname = yesterday.strftime( "%m.%d") + "_abnormal_negprofit_past3day.xls" return export(fname, yesterday)
def index(request): exceltype = mtu.getReqVal(request, "exceltype", "2") # 操作日志 if exceltype=='2': qtype = "1" else: qtype = "2" key_state = mtu.getReqVal(request, "key_state", '') if exceltype == '1' and (not key_state or key_state != '2'): exceltype = '2' path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="顾客退货率", url=path, qtype=qtype, ucode=ucode,uname=uname, createtime=today) # 昨天 yesterday = DateUtil.get_day_of_day(-1) if exceltype == '1': fname = yesterday.strftime("%m.%d") + "_daily_customer_returns.xls" return export(fname,str(yesterday)) else: data = query(str(yesterday)) return render(request, "report/daily/customer_returns.html", data)
def writeDataToSheet2(wb, retdetail): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True) titles = [ [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)], [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1), ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1), ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1), ("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)], ] keylist = ['shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'goodsid', 'goodsname', 'deptid', 'amount', 'sale'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet2, titles, keylist, widthList) mtu.insertCell2(sheet2, 3, retdetail, keylist, None) titlesLen = len(titles) listTopLen = len(retdetail)
def writeDataToSheet2(wb,listShops,listShopTotal,listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month titlesSheetShop = [[("(%s月)零库存课组明细日报" % month,0,1,7)], [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)], [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("零库存数",3,1,1),("占比",3,1,1)] ] titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month,0,1,5)], [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)], [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("零库存数",3,1,1),("占比",4,1,1)] ] keylistSheet2 = ['ShopID','shopname','deptid','deptidname','qtyz','qtyl','zhonbi'] keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi'] widthList = [600,1100,600,600,600,600] sheets = [] for i in range(0,len(listShops)): sheets.append(wb.add_sheet(listShops[i][0]['shopname'],cell_overwrite_ok=True)) sheets.append(wb.add_sheet("(月)零库存课组汇总日报",cell_overwrite_ok=True)) for j in range(0,len(sheets)-1): mtu.insertTitle2(sheets[j],titlesSheetShop,keylistSheet2,widthList) mtu.insertCell2(sheets[j],3,listShops[j-2],keylistSheet2,None) titlesLen = len(titlesSheetShop) listShopsLen = len(listShops) totalDict = {'dict':listShopTotal[j-2]} mtu.insertSum2(sheets[j],keylistSheet2,titlesLen+listShopsLen,totalDict,2) mtu.insertTitle2(sheets[len(sheets)-1],titlesSheetLast,keylistSheet3,widthList) mtu.insertCell2(sheets[len(sheets)-1],3,listDept,keylistSheet3,None)
def index(request): date = DateUtil.get_day_of_day(-1) start = (date.replace(day=1)).strftime("%Y-%m-%d") yesterday = date.strftime("%Y-%m-%d") lastDay = calendar.monthrange(date.year, date.month)[1] end = "{year}-{month}-{day}".format(year=date.year, month=date.month, day=lastDay) qtype = mtu.getReqVal(request, "qtype", "1") # 操作日志 if not qtype: qtype = "1" key_state = mtu.getReqVal(request, "key_state", '') if qtype == '2' and (not key_state or key_state != '2'): qtype = '1' path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="超市运营日分解", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(date, start, end, yesterday, lastDay) return render(request, "report/daily/group_opt_decompt.html", data) else: name = '_daily_group_operate_decompt' fname = date.strftime('%m.%d') + name + ".xls" return export(fname, date, start, end, yesterday, lastDay)
def index(request): exceltype = mtu.getReqVal(request, "exceltype", "2") if exceltype == '2': qtype = "1" else: qtype = "2" key_state = mtu.getReqVal(request, "key_state", '') if exceltype == '1' and (not key_state or key_state != '2'): exceltype = '2' # 操作日志 path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="超市课组销售前十", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) yesterday = DateUtil.get_day_of_day(-1) if exceltype == '1': fname = yesterday.strftime("%m.%d") + "_daily_saletop10_operate.xls" return export(fname, yesterday) else: data = query(yesterday) return render(request, "report/daily/saletop10.html", data)
def writeDataToSheet2(wb,listDeptDetail,listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("(月)负库存课组明细日报",cell_overwrite_ok=True) sheet3 = wb.add_sheet("(月)负库存课组汇总日报",cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)负库存课组明细日报" % month,0,1,7)], [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)], [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("负库存数",3,1,1),("占比",3,1,1)] ] titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month,0,1,5)], [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)], [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("负库存数",3,1,1),("占比",4,1,1)] ] keylistSheet2 = ['shopid','shopname','deptid','deptidname','qtyz','qtyl','zhonbi'] keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi'] widthList = [600,1100,600,600,600,600] mtu.insertTitle2(sheet2,titlesSheet2,keylistSheet2,widthList) mtu.insertTitle2(sheet3,titlesSheet3,keylistSheet3,widthList) mtu.insertCell2(sheet2,3,listDeptDetail,keylistSheet2,None) mtu.insertCell2(sheet3,3,listDept,keylistSheet3,None)
def writeDataToSheet2(wb, listDeptDetail, listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("(月)负库存课组明细日报", cell_overwrite_ok=True) sheet3 = wb.add_sheet("(月)负库存课组汇总日报", cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)负库存课组明细日报" % month, 0, 1, 7)], [("报表日期", 3, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("课组编码", 2, 1, 1), ("课组名称", 3, 1, 1), ("课组汇总", 4, 1, 1), ("负库存数", 3, 1, 1), ("占比", 3, 1, 1)]] titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month, 0, 1, 5)], [("报表日期", 2, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)], [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1), ("课组汇总", 2, 1, 1), ("负库存数", 3, 1, 1), ("占比", 4, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi' ] keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'] widthList = [600, 1100, 600, 600, 600, 600] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertTitle2(sheet3, titlesSheet3, keylistSheet3, widthList) mtu.insertCell2(sheet2, 3, listDeptDetail, keylistSheet2, None) mtu.insertCell2(sheet3, 3, listDept, keylistSheet3, None)
def writeDataToSheet1(wb, listTop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet = wb.add_sheet("(月)负库存排名日报", cell_overwrite_ok=True) titles = [[("(%s月)负库存排名日报" % month, 0, 1, 15)], [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计排名(不含联营)", 2, 1, 4)], [('有效商品数', 2, 1, 1), ('合计', 3, 1, 1), ('占比', 4, 1, 1), ('名次', 5, 1, 1)]] keylist = [ 'ShopID', 'shopname', 'qtyzSum', 'qtylSum', 'zhonbiSum', 'mingciSum' ] widthList = [600, 1100, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(d) + '日排名(不含联营)', n, 1, 4)) trow2.append(('有效商品数', n, 1, 1)) trow2.append(('合计', n + 1, 1, 1)) trow2.append(('占比', n + 2, 1, 1)) trow2.append(('名次', n + 3, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 4 if d < 10: keylist.append('qtyz_0' + str(d)) keylist.append('qtyl_0' + str(d)) keylist.append('zhonbi_0' + str(d)) keylist.append('mingci_0' + str(d)) else: keylist.append('qtyz_' + str(d)) keylist.append('qtyl_' + str(d)) keylist.append('zhonbi_' + str(d)) keylist.append('mingci_' + str(d)) #日销售报表 mtu.insertTitle2(sheet, titles, keylist, widthList) mtu.insertCell2(sheet, 3, listTop, keylist, None) titlesLen = len(titles) listTopLen = len(listTop) mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
def writeDataToSheet1(wb,listTop,TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year,month)[1] sheet = wb.add_sheet("(月)零库存排名日报",cell_overwrite_ok=True) titles = [[("(%s月)零库存排名日报" % month,0,1,15)], [("门店编号",0,2,1),("门店名称",1,2,1),("月累计排名(不含联营)",2,1,4)], [('有效商品数',2,1,1),('合计',3,1,1),('占比',4,1,1),('名次',5,1,1)] ] keylist = ['ShopID','shopname','qtyzSum','qtylSum','zhonbiSum','mingciSum'] widthList = [600,1100,600,600,600,600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1,lastDay+1): trow1.append((str(d)+'日排名(不含联营)',n,1,4)) trow2.append(('有效商品数',n,1,1)) trow2.append(('合计',n+1,1,1)) trow2.append(('占比',n+2,1,1)) trow2.append(('名次',n+3,1,1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 4 if d<10: keylist.append('qtyz_0'+str(d)) keylist.append('qtyl_0'+str(d)) keylist.append('zhonbi_0'+str(d)) keylist.append('mingci_0'+str(d)) else: keylist.append('qtyz_'+str(d)) keylist.append('qtyl_'+str(d)) keylist.append('zhonbi_'+str(d)) keylist.append('mingci_'+str(d)) #日销售报表 mtu.insertTitle2(sheet,titles,keylist,widthList) mtu.insertCell2(sheet,3,listTop,keylist,None) titlesLen = len(titles) listTopLen = len(listTop) mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
def writeDataToSheet1(wb, listtop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True) titles = [[("(%s月)门店顾客退货率" % month, 0, 1, 13)], [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1), ('月累计退货', 3, 1, 3)], [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)]] keylist = [ 'sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum', 'retrate' ] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3)) trow2.append(('销售金额', n, 1, 1)) trow2.append(('退货金额', n + 1, 1, 1)) trow2.append(('退货率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) n += 3 if d < 10: keylist.append('retsum_0' + str(d)) keylist.append('shopsalesum_0' + str(d)) keylist.append('retrate_0' + str(d)) else: keylist.append('retsum_' + str(d)) keylist.append('shopsalesum_' + str(d)) keylist.append('retrate_' + str(d)) # 日销售报表 mtu.insertTitle2(sheet1, titles, keylist, widthList) mtu.insertCell2(sheet1, 3, listtop, keylist, None) titlesLen = len(titles) listTopLen = len(listtop) mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
def writeDataToSheet2(wb, resList, title): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True) titlesSheet2 = [ [("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)], [("报表日期", 11, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)], [ ("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("管理类别码", 2, 1, 1), ("管理类别名称", 3, 1, 1), ("小类编码", 4, 1, 1), ("小类名称", 5, 1, 1), ("商品编码", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品规格", 8, 1, 1), ("销售单位", 9, 1, 1), ("负库存数量", 10, 1, 1), ("负库存金额", 11, 1, 1), ("解释原因", 12, 1, 1), ("解决方案", 13, 1, 1), ("解决时间", 14, 1, 1), ], ] keylistSheet2 = [ "shopid", "shopname", "sgroupid", "sgroupname", "deptid", "deptname", "goodsid", "goodsname", "spec", "unitname", "qty", "costvalue", "reason1", "reason2", "reason3", ] widthList = [600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000, 2000, 2000] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
def writeDataToSheet1(wb, shopTop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet = wb.add_sheet("(月)水果报损率日报", cell_overwrite_ok=True) titles = [[("(%s月)水果报损率日报" % month, 0, 1, 15)], [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计报损", 2, 1, 3)], [ ('成本金额', 2, 1, 1), ('销售成本金额', 3, 1, 1), ('报损率', 4, 1, 1), ]] keylist = [ 'shopid', 'shopname', 'lostvalueSum', 'costvalueSum', 'lrateSum' ] widthList = [600, 1100, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 5 #1日单元格开始位置 for d in range(1, lastDay + 1): trow1.append((str(d) + '日', n, 1, 3)) trow2.append(('成本金额', n, 1, 1)) trow2.append(('销售成本金额', n + 1, 1, 1)) trow2.append(('报损率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 3 #每日单元格数量 dStr = '0' + str(d) if d < 10 else str(d) keylist.append('lostvalue_' + dStr) keylist.append('costvalue_' + dStr) keylist.append('lrate_' + dStr) #日销售报表 mtu.insertTitle2(sheet, titles, keylist, widthList) mtu.insertCell2(sheet, 3, shopTop, keylist, None) titlesLen = len(titles) listTopLen = len(shopTop) mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
def writeDataToSheet1(wb, listtop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True) titles = [ [("(%s月)门店顾客退货率" % month, 0, 1, 13)], [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1), ('月累计退货', 3, 1, 3)], [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)] ] keylist = ['sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum', 'retrate'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3)) trow2.append(('销售金额', n, 1, 1)) trow2.append(('退货金额', n + 1, 1, 1)) trow2.append(('退货率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) n += 3 if d < 10: keylist.append('retsum_0' + str(d)) keylist.append('shopsalesum_0' + str(d)) keylist.append('retrate_0' + str(d)) else: keylist.append('retsum_' + str(d)) keylist.append('shopsalesum_' + str(d)) keylist.append('retrate_' + str(d)) # 日销售报表 mtu.insertTitle2(sheet1, titles, keylist, widthList) mtu.insertCell2(sheet1, 3, listtop, keylist, None) titlesLen = len(titles) listTopLen = len(listtop) mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
def index(request): yesterday = DateUtil.get_day_of_day(-1) qtype = mtu.getReqVal(request, "qtype", "1") #操作日志 if not qtype: qtype = "1" path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="负毛利大于200",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today) if qtype == "1": data = query(yesterday) return render(request, "report/abnormal/negprofit_lte200.html", data) else: fname = yesterday.strftime("%m.%d") + "_abnormal_negprofit_lte200.xls" return export(fname,yesterday)
def writeDataToSheet1(wb,shopTop,TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year,month)[1] sheet = wb.add_sheet("(月)水果报损率日报",cell_overwrite_ok=True) titles = [[("(%s月)水果报损率日报" % month,0,1,15)], [("门店编号",0,2,1),("门店名称",1,2,1),("月累计报损",2,1,3)], [('成本金额',2,1,1),('销售成本金额',3,1,1),('报损率',4,1,1),] ] keylist = ['shopid','shopname','lostvalueSum','costvalueSum','lrateSum'] widthList = [600,1100,600,600,600,600] trow1 = titles[1] trow2 = titles[2] n = 5 #1日单元格开始位置 for d in range(1,lastDay+1): trow1.append((str(d)+'日',n,1,3)) trow2.append(('成本金额',n,1,1)) trow2.append(('销售成本金额',n+1,1,1)) trow2.append(('报损率',n+2,1,1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 3 #每日单元格数量 dStr = '0'+str(d) if d<10 else str(d) keylist.append('lostvalue_'+dStr) keylist.append('costvalue_'+dStr) keylist.append('lrate_'+dStr) #日销售报表 mtu.insertTitle2(sheet,titles,keylist,widthList) mtu.insertCell2(sheet,3,shopTop,keylist,None) titlesLen = len(titles) listTopLen = len(shopTop) mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
def index(request): sgroupid = request.REQUEST.get("sgroupid") yesterday = DateUtil.get_day_of_day(-1) qtype = mtu.getReqVal(request, "qtype", "1") # 操作日志 if not qtype: qtype = "1" path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="商品连续3天负毛利", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(sgroupid, yesterday) print(data) return render(request, "report/abnormal/negStock.html", data) else: fname = yesterday.strftime("%m.%d") + "_abnormal_negStock.xls" return export(fname, sgroupid, yesterday)
def writeDataToSheet1(wb,rlist,sumDict): date = DateUtil.get_day_of_day(-1) yesterday = date.strftime("%Y-%m-%d") sheet = wb.add_sheet("宽广集团销售日报表",cell_overwrite_ok=True) titles = [[("宽广集团销售日报表",2,1,13)], [("数据日期:",0,1,2),(yesterday,2,1,1),("单位:元",4,1,1)], [("机构编码",0,2,1),("机构名称",1,2,1),("POS销售数据",3,1,9),("批发销售数据",4,1,4)], [("总客流量",2,1,1),("平均客单价",3,1,1),("销售金额",4,1,1),("折扣金额",5,1,1),("实际销售",6,1,1),("销售成本",7,1,1), ("毛利",8,1,1),("毛利率",9,1,1),("优惠占比",10,1,1),("实际销售",11,1,1),("销售成本",12,1,1),("毛利",13,1,1),("毛利率",14,1,1)], ] keylist = ['shopid','shopnm','tradenumber','tradeprice','salevalue','discvalue','sale','costvalue', 'salegain','gaintx','yhzhanbi','wsalevalue','wcostvalue','wsalegain', 'wgaintx'] widthList = [600,400,1000,800,400,800,800,800,800,800,800,800,800,800,800] mtu.insertTitle2(sheet,titles,keylist,widthList) count = mtu.insertCell2(sheet,4,rlist,keylist,None) mtu.insertSum2(sheet,keylist,count,sumDict,2)
def writeDataToSheet2(wb, listShops, listShopTotal, listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month titlesSheetShop = [[("(%s月)零库存课组明细日报" % month, 0, 1, 7)], [("报表日期", 3, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("课组编码", 2, 1, 1), ("课组名称", 3, 1, 1), ("课组汇总", 4, 1, 1), ("零库存数", 3, 1, 1), ("占比", 3, 1, 1)]] titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month, 0, 1, 5)], [("报表日期", 2, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)], [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1), ("课组汇总", 2, 1, 1), ("零库存数", 3, 1, 1), ("占比", 4, 1, 1)]] keylistSheet2 = [ 'ShopID', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi' ] keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'] widthList = [600, 1100, 600, 600, 600, 600] sheets = [] for i in range(0, len(listShops)): sheets.append( wb.add_sheet(listShops[i][0]['shopname'], cell_overwrite_ok=True)) sheets.append(wb.add_sheet("(月)零库存课组汇总日报", cell_overwrite_ok=True)) for j in range(0, len(sheets) - 1): mtu.insertTitle2(sheets[j], titlesSheetShop, keylistSheet2, widthList) mtu.insertCell2(sheets[j], 3, listShops[j - 2], keylistSheet2, None) titlesLen = len(titlesSheetShop) listShopsLen = len(listShops) totalDict = {'dict': listShopTotal[j - 2]} mtu.insertSum2(sheets[j], keylistSheet2, titlesLen + listShopsLen, totalDict, 2) mtu.insertTitle2(sheets[len(sheets) - 1], titlesSheetLast, keylistSheet3, widthList) mtu.insertCell2(sheets[len(sheets) - 1], 3, listDept, keylistSheet3, None)
def index(request): qtype = mtu.getReqVal(request,"qtype","1") #操作日志 if not qtype: qtype = "1" key_state = mtu.getReqVal(request, "key_state", '') if qtype=='2' and (not key_state or key_state!='2'): qtype = '1' path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="超市销售日报",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today) date = DateUtil.get_day_of_day(-1) if qtype == "1": data = query(date) return render(request,"report/daily/group_sale.html",data) else: fname = date.strftime("%m.%d") + "_daily_group_sale.xls" return export(fname,date)
def writeDataToSheet2(wb, data): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("单品报损超100", cell_overwrite_ok=True) titlesSheet2 = [[("单品报损超100", 0, 1, 12)], [("报表日期", 8, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 9, 1, 3)], [("机构编号", 0, 1, 1), ("机构名称", 1, 1, 1), ("单据编号", 2, 1, 1), ("商品名称", 3, 1, 1), ("商品编码", 4, 1, 1), ("类别编码", 5, 1, 1), ("类别名称", 6, 1, 1), ("申请数量", 7, 1, 1), ("审批数量", 8, 1, 1), ("实际报损数", 9, 1, 1), ("成本金额", 10, 1, 1), ("解释原因", 11, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'sheetid', 'goodsid', 'goodsname', 'deptid', 'deptname', 'askqty', 'checkqty', 'qty', 'costvalue' 'reason' ] widthList = [600, 600, 600, 600, 1000, 600, 600, 600, 600, 600, 600, 1000] mth.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mth.insertCell2(sheet2, 3, data, keylistSheet2, None)
def index(request): sgroupid = request.REQUEST.get('sgroupid') yesterday = DateUtil.get_day_of_day(-1) qtype = mtu.getReqVal(request, "qtype", "1") #操作日志 if not qtype: qtype = "1" path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="商品连续3天负毛利", url=path, qtype=qtype, ucode=ucode, uname=uname, createtime=today) if qtype == "1": data = query(sgroupid, yesterday) return render(request, 'report/abnormal/negStock.html', data) else: fname = yesterday.strftime('%m.%d') + "_abnormal_negStock.xls" return export(fname, sgroupid, yesterday)
def index(request): exceltype = mtu.getReqVal(request, "exceltype", "2") if exceltype=='2': qtype = "1" else: qtype = "2" key_state = mtu.getReqVal(request, "key_state", '') if exceltype == '1' and (not key_state or key_state != '2'): exceltype = '2' # 操作日志 path = request.path today = datetime.datetime.today() ucode = request.session.get("s_ucode") uname = request.session.get("s_uname") BasPurLog.objects.create(name="超市课组销售前十", url=path, qtype=qtype, ucode=ucode,uname=uname, createtime=today) yesterday = DateUtil.get_day_of_day(-1) if exceltype == '1': fname = yesterday.strftime("%m.%d") + "_daily_saletop10_operate.xls" return export(fname,yesterday) else: data = query(yesterday) return render(request, "report/daily/saletop10.html", data)
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): 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()
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()
print(">>>main()") # print(DateUtil.get_firstday_month(-2)) # print(DateUtil.get_lastday_month(-2)) # print("月结30天" in "") # testSort2() # str = "CM01201603020064" # if str.startswith("CM",0,2): # print("包含") # else: # print("不包含") # # print(str[0:2:]) # pr = str[0:2:] # if pr in ["CM","cM","cm","Cm",]: # print("true"); # a=[2,3,4,5] # b=[2,5,8] # print(list(set(a).union(set(b)))) # a.extend(b) # print(a) dayOfWeek = datetime.datetime.today().weekday() print(dayOfWeek) date = DateUtil.strTodate("2016-06-06") print(date.weekday()) date = DateUtil.strTodate("2016-06-05") print(date.weekday())
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): 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()