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 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,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 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 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 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):
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):
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()
