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 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):
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_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 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 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="商品连续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 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):
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 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 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 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, 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 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 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 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,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 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 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):
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 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 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):
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 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)