def exportXls(rslist):
sname = "商品资料一览表"
titles = [("门店编码", "1000", "shopid"), ("门店名称", "1000", "shopid"),
("商品条码", "1000", "barcodeid"), ("商品名称", "5000", "name"),
("规格", "500", "spec"), ("进价", "500", "cost"),
("执行售价", "1000", "price"), ("销售状态", "1000", "flag"),
("供应商", "1000", "venderid"), ("税率", "500", "costtaxrate"),
("单位", "500", "unitname"), ("小类", "1000", "deptid"),
("商品编码", "1000", "goodsid"), ("促销标志", "1000", "promflag"),
("开始日期", "1000", "startdate"), ("结束日期", "1000", "enddate")]
fmtlist = [
None, None, None, None, None, None, None, None, None, None, None, None,
None, None, "%Y-%m-%d", "%Y-%m-%d"
]
shopDict = findShop()
dictlist = [
None, shopDict, None, None, None, None, None, Constants.SALE_STATUS,
None, None, None, None, None, Constants.PROM_FLAG, None, None
]
book = mtu.exportXls(sname, titles, rslist, None, dictlist, fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "goods.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
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 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):
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):
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):
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):
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 purlog(request):
pageNum = mtu.getReqVal(request,"pageNum","1")
start = mtu.getReqVal(request,"start")
end = mtu.getReqVal(request,"end")
ucode = mtu.getReqVal(request,"ucode","")
pname = mtu.getReqVal(request,"pname","")
try:
karrs = {}
if not start:
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
if not end:
end = datetime.datetime.today().strftime("%Y-%m-%d")
karrs.setdefault("createtime__gte", "{start} 00:00:00".format(start=start))
karrs.setdefault("createtime__lte", "{end} 23:59:59".format(end=end))
karrs.setdefault("ucode__contains", "{ucode}".format(ucode=ucode))
karrs.setdefault("name__contains", "{pname}".format(pname=pname))
rlist = BasPurLog.objects.all().filter(**karrs).order_by("-createtime")
except Exception as e:
rlist = []
print(e)
page = Paginator(rlist, __EACH_PAGE_SHOW_NUMBER, allow_empty_first_page=True).page(int(pageNum))
result = {"page": page, "pageNum": str(pageNum), "start": start,"end":end,"ucode":ucode,"pname":pname,"qtype_dict":{"1":"查询","2":"下载"}}
return render(request, "admin/sysConf_retail_purlog.html", result)
def export(rslist, sum1, sum2, sum3):
sname = "门店－大类销售汇总"
titles = [
("门店名称", "1000", "shopid"),
("销售数量", "1000", "promflag"),
("实际销售", "1000", "startdate"),
("销售成本", "1000", "enddate"),
]
fmtlist = [None, "0.00", "0.000", "0.000"]
shopDict = findShop()
dictlist = [shopDict, None, None, None]
sumList = ["合计", sum1, sum2, sum3]
book = mtu.exportXls(sname, titles, rslist, sumList, dictlist, fmtlist)
outtype = "application/vnd.ms-excel"
fname = "sale_counter.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
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):
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 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 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 exportDetail(rslist, sum1, sum2, sum3):
sname = "门店销售列表"
titles = [
("大类编码", "1000", "shopid"),
("大类名称", "1000", "shopid"),
("日期", "1000", "shopid"),
("销售数量", "1000", "promflag"),
("实际销售", "1000", "startdate"),
("销售成本", "1000", "enddate"),
("占比", "500", "enddate"),
("累计占比", "1000", "enddate"),
]
fmtlist = [None, None, "%Y-%m-%d", "0.00", "0.000", "0.000", "0.00", "0.0"]
sumList = ["合计", None, None, sum1, sum2, sum3, "100.00", "100.00"]
book = mtu.exportXls(sname, titles, rslist, sumList, None, fmtlist)
outtype = "application/vnd.ms-excel"
fname = "sale_counter_detail.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
def writeDataToSheet2(wb, retdetail):
date = DateUtil.get_day_of_day(-1)
year = date.year
month = date.month
lastDay = calendar.monthrange(year, month)[1]
sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True)
titles = [
[("（%s月%s日）门店顾客退货明细" % (month, date.day), 0, 1, 12)],
[("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1),
("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1),
("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1),
("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)],
]
keylist = [
'shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid',
'goodsid', 'goodsname', 'deptid', 'amount', 'sale'
]
widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600]
# 日销售报表
mtu.insertTitle2(sheet2, titles, keylist, widthList)
mtu.insertCell2(sheet2, 3, retdetail, keylist, None)
titlesLen = len(titles)
listTopLen = len(retdetail)
def writeDataToSheet2(wb, 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 inidex(request):
yesterday = datetime.date.today() - datetime.timedelta(days=1)
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()
return render(request, 'report/daily/fruit_lost.html', data)
else:
fname = yesterday.strftime("%m.%d") + "_daily_fruitLost.xls"
return export(fname)
def save(request):
ordercode = MethodUtil.getReqVal(request,"ordercode","")
yyshdate = MethodUtil.getReqVal(request,"yyshdate","")
grpcode = request.session.get("s_grpcode")
response_data = {}
try:
if ordercode:
#1.更新订单明细
# detailList = OrdD.objects \
# .filter(ordercode=ordercode,grpcode=grpcode) \
# .values("ordercode","procode","barcode","grpcode")
# for row in detailList: procode=str(row["procode"])
OrdD.objects.filter(ordercode=ordercode,grpcode=grpcode).update(sjshsum="-1",sjprnum="-1")
#2.保存预约送货日期 更新订单状态
rs = OrdStatus.objects.all().filter(ordercode=ordercode)
if not rs:
OrdStatus.objects.create(ordercode=ordercode, yyshdate=yyshdate, status="Y")
else:
OrdStatus.objects.filter(ordercode=ordercode).update(yyshdate=yyshdate,status="Y")
response_data['result'] = 'success'
else:
response_data['result'] = 'failure'
except Exception as e:
print(e)
response_data['result'] = 'failure'
transaction.rollback()
else:
transaction.commit()
return HttpResponse(json.dumps(response_data), content_type="application/json")
def testmssql():
conn = mdu.getMssqlConn()
cur = conn.cursor()
sql = "select top 10 [ID],[Name] from [Shop]"
cur.execute(sql)
list = cur.fetchall()
for row in list:
print(row["ID"], mdu.getDBVal(row, "Name"))
def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def writeDataToSheet1(wb, rlist):
sheet = wb.add_sheet("单张小票退货超300", cell_overwrite_ok=True)
titles = [[("单张小票退货超300", 0, 1, 10)],
[("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1),
("收银员号", 5, 1, 1), ("收银员名", 6, 1, 1), ("支付原因", 7, 1, 1), ("支付类型", 8, 1, 1), ("支付金额", 9, 1, 1)],
]
keylist = ['shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'payreson', 'paytype', 'payvalue']
widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800]
mtu.insertTitle2(sheet, titles, keylist, widthlist)
mtu.insertCell2(sheet, 2, rlist, keylist, None)
def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def 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,data):
sheet = wb.add_sheet("负毛利大于200",cell_overwrite_ok=True)
titles = [[("负毛利大于200" ,0,1,15)],
[("机构编码",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)],
]
keylist = ['shopid','shopname','sdate','goodsid','goodsname','deptid','deptname','truevalue','qty',
'costvalue','profit','stockqty','solvereason','solvesolution','solvetime']
widthlist = [800,1000,800,800,2000,800,800,800,800,800,800,800,800,800,800]
mtu.insertTitle2(sheet,titles,keylist,widthlist)
mtu.insertCell2(sheet,2,data,keylist,None)
def export(outlist,sum1,sum2,sum3):
sname = "日销售汇总列表"
titles = [("日期","1000"),("门店明细","1000"),("单品明细","1000"),("大类明细","1000"),("销售数量","1000"),("实际销售","1000"),
("销售成本","1000"),("销售成本占比(%)","2000"),("销售成本累计占比(%)","3000")]
sumlist = ["合计","门店合计","单品合计","大类合计",sum1,sum2,sum3,"100.00","100.00"]
fmtlist = [None,None,None,None,"0.00","0.00","0.00","0.00","0.0"]
book = mtu.exportXls(sname,titles,outlist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def writeDataToSheet1(wb,data):
sheet = wb.add_sheet("负毛利大于200",cell_overwrite_ok=True)
titles = [[("负毛利大于200" ,0,1,15)],
[("机构编码",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)],
]
keylist = ['shopid','shopname','sdate','goodsid','goodsname','deptid','deptname','truevalue','qty',
'costvalue','profit','stockqty','solvereason','solvesolution','solvetime']
widthlist = [800,1000,800,800,2000,800,800,800,800,800,800,800,800,800,800]
mtu.insertTitle2(sheet,titles,keylist,widthlist)
mtu.insertCell2(sheet,2,data,keylist,None)
def beforeBackBill(request):
"""
预退货单列表
:param request:
:return:
"""
sperCode = request.session.get('s_suppcode') #用户所属单位
grpCode = request.session.get('s_grpcode') #用户所属单位
grpName = request.session.get('s_grpname')
shopCodeList = []
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
end = datetime.datetime.today().strftime("%Y-%m-%d")
page = mtu.getReqVal(request, 'page', 1)
sheetId = mtu.getReqVal(request, 'sheetId', '')
shopCode = mtu.getReqVal(request, 'shopCode', '')
start = mtu.getReqVal(request, 'start', start)
end = mtu.getReqVal(request, 'end', end)
kwargs = {}
if sheetId:
kwargs.setdefault("sheetid__contains", sheetId)
if len(shopCode):
shopCodeStr = shopCode[0:len(shopCode) - 1]
shopCodeList = shopCodeStr.split(',')
kwargs.setdefault("shopid__in", shopCodeList)
kwargs.setdefault("checkdate__gte", start)
kwargs.setdefault("checkdate__lte", "{end} 23:59:59".format(end=end))
kwargs.setdefault("venderid", sperCode)
kwargs.setdefault("flag", 2)
billList = Ret0.objects.values("sheetid","shopid","venderid","retdate","editdate","paymoney","kxsummoney","acceptflag","badflag","checkdate","notes")\
.filter(**kwargs).order_by("sheetid")
#分页函数
paginator = Paginator(billList, 20)
try:
billList = paginator.page(page)
except Exception as e:
print(e)
ACCEPT_FLAG = {"0": "同意", "1": "不同意"}
BAD_FLAG = {"0": "坏货", "1": "好货"}
return render(request, 'user_bill_beforeback.html', locals())
def beforeBackBill(request):
"""
预退货单列表
:param request:
:return:
"""
sperCode = request.session.get('s_suppcode') #用户所属单位
grpCode = request.session.get('s_grpcode') #用户所属单位
grpName = request.session.get('s_grpname')
shopCodeList = []
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
end = datetime.datetime.today().strftime("%Y-%m-%d")
page = mtu.getReqVal(request,'page',1)
sheetId = mtu.getReqVal(request,'sheetId','')
shopCode = mtu.getReqVal(request,'shopCode','')
start = mtu.getReqVal(request,'start',start)
end = mtu.getReqVal(request,'end',end)
kwargs = {}
if sheetId:
kwargs.setdefault("sheetid__contains",sheetId)
if len(shopCode):
shopCodeStr = shopCode[0:len(shopCode)-1]
shopCodeList = shopCodeStr.split(',')
kwargs.setdefault("shopid__in",shopCodeList)
kwargs.setdefault("checkdate__gte",start)
kwargs.setdefault("checkdate__lte","{end} 23:59:59".format(end=end))
kwargs.setdefault("venderid",sperCode)
kwargs.setdefault("flag",2)
billList = Ret0.objects.values("sheetid","shopid","venderid","retdate","editdate","paymoney","kxsummoney","acceptflag","badflag","checkdate","notes")\
.filter(**kwargs).order_by("sheetid")
#分页函数
paginator=Paginator(billList,20)
try:
billList=paginator.page(page)
except Exception as e:
print(e)
ACCEPT_FLAG = {"0":"同意","1":"不同意"}
BAD_FLAG = {"0":"坏货","1":"好货"}
return render(request,'user_bill_beforeback.html',locals())
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 writeDataToSheet2(wb, rlist):
sheet = wb.add_sheet("商品退货明细", cell_overwrite_ok=True)
titles = [[("商品退货明细", 0, 1, 16)],
[("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 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), ("解释原因", 15, 1, 1)],
]
keylist = ['shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'goodsname', 'deptid', 'xamount', \
'salevalue', 'discvalue', 'truevalue', 'saletype', 'price']
widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800]
mtu.insertTitle2(sheet, titles, keylist, widthlist)
mtu.insertCell2(sheet, 2, rlist, keylist, 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 query(date):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
rlist = Kglistnoret.objects\
.values("shopid", "sdate", "stime", "listno", "posid", "cashierid",
"name", "payreson","paytype", "payvalue")\
.filter(sdate=date,shopid__in=rbacDepartList)
formate_data(rlist)
# 商品退货明细
conn = mtu.getMysqlConn()
sql = "SELECT shopid, sdate, stime, listno, posid, cashierid, name, DeptID, DeptName, goodsid, goodsname, xAmount," \
"salevalue, DiscValue, truevalue, SaleType, Price, DiscType " \
"FROM `kggoodsret` " \
"WHERE (`shopid` IN ({rbacDepart}) AND `sdate` = '{date}' " \
"AND LEFT(`DeptID`,2) IN ({rbacClass}))"\
.format(rbacDepart=rbacDepart,date=date,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sql)
dlist = cur.fetchall()
cur.close()
conn.close()
formate_data(dlist)
data = {"rlist": list(rlist), 'dlist': list(dlist)}
return data
def countSumZb(rlist):
if len(rlist) > 1:
sumdict = {}
yitem = rlist[0]
sitem = rlist[1]
for key in yitem:
est = yitem[key]
if key in sitem:
sale = sitem[key]
if decimal.Decimal(est) > 0:
zb = mtu.convertToStr(decimal.Decimal(sale) * decimal.Decimal("100.0") / decimal.Decimal(est),
"0.00", 1) + "%"
else:
zb = ""
else:
zb = ""
sumdict.setdefault(key, zb)
yitem.setdefault("idname", "合计")
sitem.setdefault("idname", "合计")
sumdict.setdefault("idname", "合计")
yitem.setdefault("codelable", "计划")
sitem.setdefault("codelable", "实际达成")
sumdict.setdefault("codelable", "达成率")
rlist.append(sumdict)
def exportDetail3(rslist, sum1, sum2, sum3, type):
sname = "销售汇总―大类" + type + "销售汇总"
titles = [("大类编号", "1000"), ("大类名称", "1000"), ("销售数量", "1000"),
("实际销售", "1000"), ("销售成本", "1000")]
sumlist = ["合计", "", sum1, sum2, sum3]
fmtlist = [None, None, "0.00", "0.000", "0.000"]
book = mtu.exportXls(sname, titles, rslist, sumlist, None, fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis_gz.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
def countSumZb(rlist):
if len(rlist) > 1:
sumdict = {}
yitem = rlist[0]
sitem = rlist[1]
for key in yitem:
est = yitem[key]
if key in sitem:
sale = sitem[key]
if decimal.Decimal(est) > 0:
zb = mtu.convertToStr(
decimal.Decimal(sale) * decimal.Decimal("100.0") /
decimal.Decimal(est), "0.00", 1) + "%"
else:
zb = ""
else:
zb = ""
sumdict.setdefault(key, zb)
yitem.setdefault("idname", "合计")
sitem.setdefault("idname", "合计")
sumdict.setdefault("idname", "合计")
yitem.setdefault("codelable", "计划")
sitem.setdefault("codelable", "实际达成")
sumdict.setdefault("codelable", "达成率")
rlist.append(sumdict)
def query(sgroupid, date):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
title = ''
if sgroupid == '2':
title = '食品'
if sgroupid == '3':
title = '非食'
conn = mtu.getMysqlConn()
sql= "SELECT `KGnegstock`.`shopid`, `KGnegstock`.`shopname`, `KGnegstock`.`sGroupID`, `KGnegstock`.`sGroupName`, " \
"`KGnegstock`.`GoodsID`, `KGnegstock`.`GoodsName`, `KGnegstock`.`qty`, `KGnegstock`.`CostValue`, `KGnegstock`." \
"`Spec`, `KGnegstock`.`UnitName`, `KGnegstock`.`deptid`, `KGnegstock`.`deptname`, `KGnegstock`.`Venderid`, " \
"`KGnegstock`.`VenderName`, `KGnegstock`.`Promflag`, `KGnegstock`.`OpenQty`, `KGnegstock`.`ReceiptDate`, " \
"`KGnegstock`.`OnReceiptQty`, `KGnegstock`.`SaleDate` " \
"FROM `KGnegstock` " \
"WHERE (`KGnegstock`.`shopid` IN ({rbacDepart}) AND `KGnegstock`.`SaleDate` = '{date}' " \
"AND LEFT(`KGnegstock`.`deptid`,2) IN ({rbacClass}) AND `KGnegstock`.`sGroupID` = {sgroupid}) " \
"ORDER BY `KGnegstock`.`shopid` ASC"\
.format(rbacDepart=rbacDepart,rbacClass=rbacClass,date=date,sgroupid=sgroupid)
cur = conn.cursor()
cur.execute(sql)
resList = cur.fetchall()
cur.close()
conn.close()
formate_data(resList)
return locals()
def exportDetail(rslist,sum1,sum2,sum3):
sname = "类别日销售列表"
titles = [("日期","1000"),("小类编码","1000"),("小类名称","1000"),("销售数量","1000"),("实际销售","1000"),
("销售成本","1000"),("占比(%)","500"),("累计占比(%)","1000")]
sumlist = ["合计","","",sum1,sum2,sum3,"100.00","100.0"]
fmtlist = ["%Y-%m-%d",None,None,"0.00","0.000","0.000","0.00","0.0"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_category.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def export(rslist,sum1,sum2,sum3):
sname = "单品销售汇总"
titles = [("日期","1000"),("商品编号","1000"),("商品条码","1000"),("商品名称","3000"),("规格","500"),
("税率","500"),("销售数量","1000"),("实际销售","1000"),("销售成本","1000")]
sumlist = ["合计","","","","","",sum1,sum2,sum3]
fmtlist = [None,None,None,None,None,None,"0.00","0.000","0.000"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "sale_product.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def exportDetail(rslist,sum1,sum2,sum3,pname):
sname = "单品"+pname+"销售汇总"
titles = [("门店","2000"),("销售数量","1000"),("实际销售","1000"),("销售成本","1000"),
("销售成本占比","2000"),("销售成本累计占比","2000")]
sumlist = ["合计",sum1,sum2,sum3,"100.00","100.00"]
fmtlist = [None,"0.00","0.00","0.000","0.000","0.00"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "sale_sellinfo_pro.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
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 getMenu(purlist):
rlist = []
m1list = []
for row in purlist:
grpname = getCodeByName(row["grpname"].strip())
rlist.append(grpname)
m1list.append((row["parentnm"].strip(), grpname))
#去重
rlist = list(set(rlist))
m1list = list(set(m1list))
#排序
m1list = sorted(m1list, key=lambda menu: menu[0])
dt = {}
pinyin = mtu.PinYin()
for r in rlist:
mls = []
for m in m1list:
if r == m[1]:
dls = []
for d in purlist:
if m[0] == d['parentnm'].strip():
dls.append(d)
mkey = cutStr(m[0], ".")
mls.append([
m[0], dls,
pinyin.hanzi2pinyin_split(string=mkey, split="")
])
dt[r] = mls
# print(">>>>>>菜单：",dt)
return dt
def exportDetail3(rslist,sum1,sum2,sum3,type):
sname = "销售汇总―大类"+type+"销售汇总"
titles = [("大类编号","1000"),("大类名称","1000"),("销售数量","1000"),
("实际销售","1000"),("销售成本","1000")]
sumlist = ["合计","",sum1,sum2,sum3]
fmtlist = [None,None,"0.00","0.000","0.000"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis_gz.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def exportDetail(rslist,sum1,sum2,sum3,pname):
sname = "单品"+pname+"销售汇总"
titles = [("门店","2000"),("销售数量","1000"),("实际销售","1000"),("销售成本","1000"),
("销售成本占比","2000"),("销售成本累计占比","2000")]
sumlist = ["合计",sum1,sum2,sum3,"100.00","100.00"]
fmtlist = [None,"0.00","0.00","0.000","0.000","0.00"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "sale_sellinfo_pro.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def get_colour_checksum(request):
sql = "CHECKSUM TABLE bas_user"
conn = mth.getMysqlConn()
cur = conn.cursor()
cur.execute(sql)
res = cur.fetchone()
print(res)
return str(res['Checksum'])
def export(rslist,sum1,sum2,sum3):
sname = "商品销售明细"
titles = [("商品编号","1000"),("商品条码","1000"),("商品名称","3000"),("小类编码","1000"),("小类名称","1000"),("规格","500"),
("税率","500"),("销售数量","1000"),("实际销售","1000"),("销售成本","1000"),("占比(%)","500"),("累计占比(%)","500")]
sumlist = ["合计","","","","","","",sum1,sum2,sum3,"100.00","100.0"]
fmtlist = [None,None,None,None,None,None,"0.00","0.00","0.000","0.000","0.00","0.0"]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "sale_sellinfo.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def exportDetail(rslist, sum1, sum2, sum3):
sname = "类别日销售列表"
titles = [("日期", "1000"), ("小类编码", "1000"), ("小类名称", "1000"),
("销售数量", "1000"), ("实际销售", "1000"), ("销售成本", "1000"),
("占比(%)", "500"), ("累计占比(%)", "1000")]
sumlist = ["合计", "", "", sum1, sum2, sum3, "100.00", "100.0"]
fmtlist = ["%Y-%m-%d", None, None, "0.00", "0.000", "0.000", "0.00", "0.0"]
book = mtu.exportXls(sname, titles, rslist, sumlist, None, fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_category.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
def exportDetail2(rslist,sum1,sum2,sum3,type):
sname = " 销售汇总―单品"+type+"销售汇总"
titles = [("商品编号","1000"),("商品条码","1000"),("商品名称","3000"),("规格","500"),("单位","500"),
("销售数量","1000"),("实际销售","1000"),("销售成本","1000"),("税率","500")]
sumlist = ["合计","","","","",sum1,sum2,sum3,""]
fmtlist = [None,None,None,None,None,"0.00","0.000","0.000",None]
book = mtu.exportXls(sname,titles,rslist,sumlist,None,fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis_pro.xls"
response = mtu.getResponse(HttpResponse(),outtype,fname)
book.save(response)
return response
def export(outlist, sum1, sum2, sum3):
sname = "日销售汇总列表"
titles = [("日期", "1000"), ("门店明细", "1000"), ("单品明细", "1000"),
("大类明细", "1000"), ("销售数量", "1000"), ("实际销售", "1000"),
("销售成本", "1000"), ("销售成本占比(%)", "2000"), ("销售成本累计占比(%)", "3000")]
sumlist = [
"合计", "门店合计", "单品合计", "大类合计", sum1, sum2, sum3, "100.00", "100.00"
]
fmtlist = [None, None, None, None, "0.00", "0.00", "0.00", "0.00", "0.0"]
book = mtu.exportXls(sname, titles, outlist, sumlist, None, fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
def exportDetail2(rslist, sum1, sum2, sum3, type):
sname = " 销售汇总―单品" + type + "销售汇总"
titles = [("商品编号", "1000"), ("商品条码", "1000"), ("商品名称", "3000"),
("规格", "500"), ("单位", "500"), ("销售数量", "1000"), ("实际销售", "1000"),
("销售成本", "1000"), ("税率", "500")]
sumlist = ["合计", "", "", "", "", sum1, sum2, sum3, ""]
fmtlist = [None, None, None, None, None, "0.00", "0.000", "0.000", None]
book = mtu.exportXls(sname, titles, rslist, sumlist, None, fmtlist)
outtype = 'application/vnd.ms-excel'
fname = "day_sale_analysis_pro.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
book.save(response)
return response
def findKxlist(request):
venderid = request.session.get("s_suppcode")
pend = mtu.getReqVal(request,"pend")
rlist = findKxListData(venderid,pend,1)
sum = decimal.Decimal(0.0)
for row in rlist:
sum += row["kmoney"]
return render(request,"user_settle_kx.html",{"rlist":rlist,"sum":sum})
def exportAdjustArticle(code,tbody,thead,total):
wb = xlwt.Workbook(encoding='utf-8', style_compression=0)
writeDataToSheet1(wb, code,tbody, thead,total)
outtype = 'application/vnd.ms-excel'
fname = "AdjustArticle.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
wb.save(response)
return response
def exportAdjustArticle(code, tbody, thead, total):
wb = xlwt.Workbook(encoding='utf-8', style_compression=0)
writeDataToSheet1(wb, code, tbody, thead, total)
outtype = 'application/vnd.ms-excel'
fname = "AdjustArticle.xls"
response = mtu.getResponse(HttpResponse(), outtype, fname)
wb.save(response)
return response
def writeDataToSheet2(wb, rlist, date):
year = date.year
month = date.month
sheet = wb.add_sheet("类别", cell_overwrite_ok=True)
titles = [[("%s年%s月各店类别每日业绩指标分解达成表（单位：万元）" % (year, month), 0, 1, 15)],
[("类别编码", 0, 3, 1), ("日期", 1, 1, 5)],
[("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1),
("累计计划毛利达标率", 5, 2, 1)],
[]]
keylist = ['idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain']
widthlist = [1200, 800, 800, 800, 800, 800]
initExport(titles, keylist, widthlist, date)
mtu.insertTitle2(sheet, titles, keylist, widthlist)
mtu.insertCell2(sheet, 4, rlist, keylist, None)
def findKxlist(request):
venderid = request.session.get("s_suppcode")
pend = mtu.getReqVal(request,"pend")
rlist = findKxListData(venderid,pend,1)
sum = decimal.Decimal(0.0)
for row in rlist:
sum += row["kmoney"]
return render(request,"user_settle_kx.html",{"rlist":rlist,"sum":sum})
def writeDataToSheet1(wb, rlist):
sheet = wb.add_sheet("单张小票退货超300", cell_overwrite_ok=True)
titles = [
[("单张小票退货超300", 0, 1, 10)],
[("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1),
("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1), ("收银员号", 5, 1, 1),
("收银员名", 6, 1, 1), ("支付原因", 7, 1, 1), ("支付类型", 8, 1, 1),
("支付金额", 9, 1, 1)],
]
keylist = [
'shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name',
'payreson', 'paytype', 'payvalue'
]
widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800]
mtu.insertTitle2(sheet, titles, keylist, widthlist)
mtu.insertCell2(sheet, 2, rlist, keylist, None)
def findRefSheetId(sheetid,venderid):
try:
conn = mtu.getMssqlConn()
cur = conn.cursor()
sql = "select refsheetid,refsheettype from UpdPayableTemp where sheetid='{sheetid}' and venderid='{venderid}'".format(sheetid=sheetid,venderid=venderid)
cur.execute(sql)
item = cur.fetchone()
except Exception as e:
print(e)
return item
def findrole(request):
grpcode = request.session.get("s_grpcode")
utype = request.session.get("s_utype")
ucode = mtu.getReqVal(request,"ucode")
urlist = BasUserRole.objects.filter(ucode=ucode).values("rcode")
rlist = BasRole.objects.filter(grpcode__in=[utype,grpcode]).values()
rs = {}
rs["rlist"]=[dict(row) for row in rlist]
rs["urlist"]=[row["rcode"] for row in urlist]
return HttpResponse(json.dumps(rs))
def writeDataToSheetN(wb, shoplist, rlist, date):
year = date.year
month = date.month
for i in range(len(shoplist)):
shop = shoplist[i]
sheetname = "%s%s" % (shop["shopid"][2:4], shop["shopname"])
sheet = wb.add_sheet(sheetname, cell_overwrite_ok=True)
titles = [[("%s年%s月各店类别每日业绩指标分解达成表（单位：万元）" % (year, month), 0, 1, 15)],
[("类别编码", 0, 3, 1), ("日期", 1, 1, 5)],
[("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1),
("累计计划毛利达标率", 5, 2, 1)],
[]]
keylist = ['idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain']
widthlist = [1200, 800, 800, 800, 800, 800]
initExport(titles, keylist, widthlist, date)
mtu.insertTitle2(sheet, titles, keylist, widthlist)
mtu.insertCell2(sheet, 4, rlist[i], keylist, None)
def uppwd(request):
data = {}
flag = mtu.getReqVal(request,"flag")
#修改
if flag=="1":
user = request.session.get("s_user",None)
ucode = user["ucode"]
elif flag=="2":
ucode = mtu.getReqVal(request,"ucode")
data["result"] = "1"
try:
newPwd = mtu.getReqVal(request,"newPwd","")
pwd = mtu.md5(newPwd)
BasUser.objects.filter(ucode=ucode).update(password=pwd)
data["result"] = "0"
except Exception as e:
print(e)
return HttpResponse(json.dumps(data))