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 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 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 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 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 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 report(request):
pageNum = mtu.getReqVal(request, "pageNum", "1")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
ucode = mtu.getReqVal(request, "ucode", "")
pname = mtu.getReqVal(request, "pname", "")
try:
if not start:
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
if not end:
end = datetime.datetime.today().strftime("%Y-%m-%d")
conn = mtu.getMysqlConn()
cur = conn.cursor()
condition = " where 1=1 "
condition += " and createtime BETWEEN '{start} 00:00:00' AND '{end} 23:59:59'"\
.format(start=start,end=end)
if ucode:
condition += " and ucode like '%{ucode}%'".format(ucode=ucode)
if pname:
condition += " and name like '%{pname}%'".format(pname=pname)
sql = "SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime,`name`,url," \
"SUM(CASE WHEN qtype = 1 THEN 1 ELSE 0 END) views," \
"SUM(CASE WHEN qtype = 2 THEN 1 ELSE 0 END) downs " \
"FROM bas_pur_log {condition} GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d'),`name` "\
"order by DATE_FORMAT(createtime,'%Y-%m-%d') DESC "\
.format(condition=condition)
cur.execute(sql)
rlist = cur.fetchall()
mtu.close(conn, cur)
except Exception as e:
rlist = []
print(e)
page = Paginator(rlist,
__EACH_PAGE_SHOW_NUMBER,
allow_empty_first_page=True).page(int(pageNum))
result = {
"page": page,
"pageNum": str(pageNum),
"start": start,
"end": end,
"ucode": ucode,
"pname": pname,
}
return render(request, "admin/sysConf_retail_purlog_report.html", result)
def 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 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):
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 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 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 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 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))
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 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))
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 report(request):
pageNum = mtu.getReqVal(request, "pageNum", "1")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
ucode = mtu.getReqVal(request, "ucode", "")
pname = mtu.getReqVal(request, "pname", "")
try:
if not start:
start = (datetime.date.today().replace(day=1)).strftime("%Y-%m-%d")
if not end:
end = datetime.datetime.today().strftime("%Y-%m-%d")
conn = mtu.getMysqlConn()
cur = conn.cursor()
condition = " where 1=1 "
condition += " and createtime BETWEEN '{start} 00:00:00' AND '{end} 23:59:59'"\
.format(start=start,end=end)
if ucode:
condition += " and ucode like '%{ucode}%'".format(ucode=ucode)
if pname:
condition += " and name like '%{pname}%'".format(pname=pname)
sql = "SELECT DATE_FORMAT(createtime,'%Y-%m-%d') createtime,`name`,url," \
"SUM(CASE WHEN qtype = 1 THEN 1 ELSE 0 END) views," \
"SUM(CASE WHEN qtype = 2 THEN 1 ELSE 0 END) downs " \
"FROM bas_pur_log {condition} GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d'),`name` "\
"order by DATE_FORMAT(createtime,'%Y-%m-%d') DESC "\
.format(condition=condition)
cur.execute(sql)
rlist = cur.fetchall()
mtu.close(conn,cur)
except Exception as e:
rlist = []
print(e)
page = Paginator(rlist, __EACH_PAGE_SHOW_NUMBER, allow_empty_first_page=True).page(int(pageNum))
result = {"page": page, "pageNum": str(pageNum),"start": start,"end":end,"ucode":ucode,"pname":pname,}
return render(request, "admin/sysConf_retail_purlog_report.html", result)
def 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 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)
yesterday = datetime.date.today() - datetime.timedelta(days=1)
if qtype== "1":
data = query()
return render(request,"report/daily/zero_stock_top.html",data)
else:
fname = yesterday.strftime("%m.%d") + "_daily_zero_stock.xls"
return export(fname)
def index(request):
qtype = mtu.getReqVal(request,"qtype","1")
#操作日志
if not qtype:
qtype = "1"
key_state = mtu.getReqVal(request, "key_state", '')
if qtype=='2' and (not key_state or key_state!='2'):
qtype = '1'
path = request.path
today = datetime.datetime.today()
ucode = request.session.get("s_ucode")
uname = request.session.get("s_uname")
BasPurLog.objects.create(name="超市销售日报",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today)
date = DateUtil.get_day_of_day(-1)
if qtype == "1":
data = query(date)
return render(request,"report/daily/group_sale.html",data)
else:
fname = date.strftime("%m.%d") + "_daily_group_sale.xls"
return export(fname,date)
def index(request):
qtype = mtu.getReqVal(request,"qtype","1")
#操作日志
if not qtype:
qtype = "1"
key_state = mtu.getReqVal(request, "key_state", '')
if qtype=='2' and (not key_state or key_state!='2'):
qtype = '1'
path = request.path
today = datetime.datetime.today()
ucode = request.session.get("s_ucode")
uname = request.session.get("s_uname")
BasPurLog.objects.create(name="超市销售日报",url=path,qtype=qtype,ucode=ucode,uname=uname,createtime=today)
date = DateUtil.get_day_of_day(-1)
if qtype == "1":
data = query(date)
return render(request,"report/daily/group_sale.html",data)
else:
fname = date.strftime("%m.%d") + "_daily_group_sale.xls"
return export(fname,date)
def menu(request):
mtype = mtu.getReqVal(request,"mtype","1")
request.session["menu_type"] = mtype
user = request.session.get("s_user",None)
if user:
pubList = findPubInfoAllByCon(user)
else:
pubList = []
pageNum = int(request.GET.get("pageNum",1))
page = Paginator(pubList,__EACH_PAGE_SHOW_NUMBER,allow_empty_first_page=True).page(pageNum)
return render(request,"admin/index.html",{"page":page,"pageNum":pageNum})
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 saverole(request):
ucode = mtu.getReqVal(request, "ucode")
rlist = request.POST.getlist("rcode")
rs = {}
try:
cursor = connection.cursor()
sql = "delete from bas_user_role where ucode=" + ucode
cursor.execute(sql)
for row in rlist:
sql = "insert into bas_user_role(ucode, rcode, status,brdate) values ('" + ucode + "','" + row + "', 0, curdate())"
cursor.execute(sql)
rs["status"] = '0'
except Exception as e:
print(e)
rs["status"] = '1'
return HttpResponse(json.dumps(rs))
def saverole(request):
ucode = mtu.getReqVal(request,"ucode")
rlist = request.POST.getlist("rcode")
rs = {}
try:
cursor = connection.cursor()
sql = "delete from bas_user_role where ucode=" + ucode
cursor.execute(sql)
for row in rlist:
sql = "insert into bas_user_role(ucode, rcode, status,brdate) values ('" + ucode + "','" + row + "', 0, curdate())"
cursor.execute(sql)
rs["status"] = '0'
except Exception as e:
print(e)
rs["status"] = '1'
return HttpResponse(json.dumps(rs))
def query_supp(request):
posts = []
grpcode = mtu.getReqVal(request,"grpcode","")
if grpcode:
sql = "select u.ucode, u.nm, u.grpcode, g.chnm as gnm from bas_user as u,"
sql += "bas_supplier as g where u.grpcode = g.suppcode and u.utype = '2' "
sql += "and u.grpcode like '%" + grpcode.strip() + "%'"
cursor = connection.cursor()
cursor.execute(sql)
rsobj = cursor.fetchall()
for row in rsobj:
post_dict = {}
post_dict['ucode'] = row[0]
post_dict['nm'] = row[1]
post_dict['grpcode'] = row[2]
post_dict['gnm'] = row[3]
posts.append(post_dict)
form = ChangeGrpPass()
return render(request, 'admin/sysConf_supply_admin.html', {'form': form, 'posts': posts,"grpcode":grpcode})
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 menu(request):
mtype = mtu.getReqVal(request, "mtype", "1")
request.session["menu_type"] = mtype
user = request.session.get("s_user", None)
if user:
pubList = findPubInfoAllByCon(user)
else:
pubList = []
pageNum = int(request.GET.get("pageNum", 1))
page = Paginator(pubList,
__EACH_PAGE_SHOW_NUMBER,
allow_empty_first_page=True).page(pageNum)
return render(request, "admin/index.html", {
"page": page,
"pageNum": pageNum
})
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 createInvioce(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
refSheetId = MethodUtil.getReqVal(request, 'sheetid')
conn2 = MethodUtil.get_MssqlConn()
#判断发票单据是否存在
# sql = "select sheetid from CustReceive0 where venderid={venderid} and ShopID='CM01'".format(venderid=suppCode)
# sheetList = conn2.execute_row(sql)
#计划付款日期
balanceList = Billhead0.objects.filter(sheetid=refSheetId).values(
"planpaydate", "begindate", "enddate")
if balanceList:
balance = balanceList[0]
PlanPayDate = balance['planpaydate']
begindate = balance['begindate']
enddate = balance['enddate']
else:
PlanPayDate = ''
suppList = BasSupplier.objects.filter(suppcode=suppCode).values(
"taxno", "paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
itemList = Billheaditem0.objects.filter(
sheetid=refSheetId).values("inshopid").order_by("inshopid")
if itemList:
shopId = itemList[0]['inshopid']
else:
shopId = ''
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
return render(request, 'user_invoice.html', locals())
def query_supp(request):
posts = []
grpcode = mtu.getReqVal(request, "grpcode", "")
if grpcode:
sql = "select u.ucode, u.nm, u.grpcode, g.chnm as gnm from bas_user as u,"
sql += "bas_supplier as g where u.grpcode = g.suppcode and u.utype = '2' "
sql += "and u.grpcode like '%" + grpcode.strip() + "%'"
cursor = connection.cursor()
cursor.execute(sql)
rsobj = cursor.fetchall()
for row in rsobj:
post_dict = {}
post_dict['ucode'] = row[0]
post_dict['nm'] = row[1]
post_dict['grpcode'] = row[2]
post_dict['gnm'] = row[3]
posts.append(post_dict)
form = ChangeGrpPass()
return render(request, 'admin/sysConf_supply_admin.html', {
'form': form,
'posts': posts,
"grpcode": grpcode
})
def createInvioce(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
refSheetId = MethodUtil.getReqVal(request,'sheetid')
conn2= MethodUtil.get_MssqlConn()
#判断发票单据是否存在
# sql = "select sheetid from CustReceive0 where venderid={venderid} and ShopID='CM01'".format(venderid=suppCode)
# sheetList = conn2.execute_row(sql)
#计划付款日期
balanceList = Billhead0.objects.filter(sheetid=refSheetId).values("planpaydate","begindate","enddate")
if balanceList:
balance=balanceList[0]
PlanPayDate=balance['planpaydate']
begindate = balance['begindate']
enddate = balance['enddate']
else:
PlanPayDate = ''
suppList = BasSupplier.objects.filter(suppcode=suppCode).values("taxno","paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
itemList = Billheaditem0.objects.filter(sheetid=refSheetId).values("inshopid").order_by("inshopid")
if itemList:
shopId = itemList[0]['inshopid']
else:
shopId = ''
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
return render(request,'user_invoice.html',locals())
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 detail(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname", "")
shopCode = mtu.getReqVal(request, "shopCode")
teamcode = mtu.getReqVal(request, "teamcode", "")
teamname = mtu.getReqVal(request, "teamname", "")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
# 当月1号
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")
shopDict = findShop()
if shopCode:
codes = " shopcode='%s' " % shopCode
shops = "%s" % shopDict[str(shopCode)]
sum1 = decimal.Decimal("0.0")
sum2 = decimal.Decimal("0.0")
sum3 = decimal.Decimal("0.0")
sum6 = decimal.Decimal("0.0")
teamsv = decimal.Decimal("0.0")
try:
# 销售成本总和
sql = "select sum(IFNULL(scost,0)) scost "
sql += " from sales_pro where grpcode = '" + grpcode + "' and (" + codes + ") and sstyle<>''"
sql += (
" and DATE_FORMAT(sdate,'%Y-%m-%d') >= '"
+ start
+ "' and DATE_FORMAT(sdate,'%Y-%m-%d') <= '"
+ end
+ "' and teamcode like '%"
+ teamcode.strip()
+ "%'"
)
sql += " and supercode='" + spercode + "' and teamname like '%" + teamname.strip() + "%' "
cursor = connection.cursor()
cursor.execute(sql)
rsobj = cursor.fetchone()
if rsobj:
teamsv = rsobj[0]
# 门店下商品大类销售汇总
# 总折扣、供应商折扣、实际销售、销售数量、销售成本、大类编码、销售日期、大类名称
sql2 = "select IFNULL(tb2.zzk,0),IFNULL(tb2.discount,0),IFNULL(tb2.svalue,0),IFNULL(tb2.num,0),IFNULL(tb2.scost,0),tb2.bccode,tb2.sdate,org.orgname "
sql2 += " from(select sum(tb1.zzk) as zzk,sum(tb1.discount) as discount,sum(tb1.svalue) as svalue,sum(tb1.num) as num,"
sql2 += " sum(tb1.scost) as scost,tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d') as sdate from("
sql2 += " select zzk,discount,svalue,num,scost,bccode,sdate "
sql2 += (
" from sales_pro where grpcode='"
+ grpcode
+ "' and ("
+ codes
+ ") and sstyle<>'' and DATE_FORMAT(sdate,'%Y-%m-%d') >= '"
+ start
+ "' "
)
sql2 += (
" and DATE_FORMAT(sdate,'%Y-%m-%d') <= '"
+ end
+ "' and teamcode like '%"
+ teamcode.strip()
+ "%' and teamname like '%"
+ teamname.strip()
+ "%' "
)
sql2 += " and supercode='" + spercode + "' and bccode is not null"
sql2 += " ) tb1 "
sql2 += " group by tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d')"
sql2 += ") tb2,bas_org org where tb2.bccode = org.orgcode order by tb2.bccode,tb2.sdate"
# print(sql2)
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
outlist = []
type1, type2 = "", ""
if list:
for row in list:
type2 = row[5]
item = ["", "", "", "", "", "", "", "", ""]
# 销售数量、实际销售、销售成本、占比、累计占比
if type1 != type2 and type1 != "":
itemsum = ["", "", "", "", "", "", "", "", ""]
itemsum[0] = "小计："
itemsum[3] = sum1
itemsum[4] = sum2
itemsum[5] = sum3
itemsum[8] = "1"
outitemsum = itemsum[:]
outitemsum.pop()
rslist.append(itemsum)
outlist.append(outitemsum)
item[0] = str(row[5]) # 大类编码
item[1] = str(row[7]) # 大类名称
item[2] = row[6] # 销售日期
item[3] = row[3] # 销售数量
item[4] = row[2] - row[1] # 实际销售
item[5] = row[4] # 销售成本
if teamsv:
radio = row[4] / teamsv * 100
item[6] = str(radio) # 占比
sum6 += radio
item[7] = sum6 # 累计占比
item[8] = "2" # 类型
type1 = type2
sum1 += item[3]
sum2 += item[4]
sum3 += item[5]
outitem = item[:]
outitem.pop()
rslist.append(item)
outlist.append(outitem)
cursor.close()
except Exception as e:
print(e)
if qtype == "1":
result = {"rslist": rslist}
result.setdefault("grpcode", grpcode)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("shopCode", shopCode)
result.setdefault("teamcode", teamcode)
result.setdefault("teamname", teamname)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", sum1.quantize(decimal.Decimal("0.00")))
result.setdefault("sum2", sum2.quantize(decimal.Decimal("0.000")))
result.setdefault("sum3", sum3.quantize(decimal.Decimal("0.000")))
return render(request, "user_sale_counter_detail.html", result)
else:
return exportDetail(outlist, sum1, sum2, sum3)
def find(request):
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
ordercode = MethodUtil.getReqVal(request,"ordercode","")
#查询订单信息
order = Ord.objects.get(ordercode=ordercode)
slist = OrdStatus.objects.filter(ordercode=ordercode).values("ordercode","yyshdate","status")
orderstatus = {}
if not slist:
orderstatus["yyshdate"] = order.sdate
else:
sobj = slist[0]
if not sobj["yyshdate"]:
orderstatus["yyshdate"] = order.sdate
else:
orderstatus = slist[0]
seenum = order.seenum
if not seenum:
seenum = 0
#更新订单查询次数
Ord.objects.filter(ordercode=ordercode).update(seenum=int(seenum)+1)
#查询订单明细
detailList = OrdD.objects \
.filter(ordercode=ordercode,grpcode=grpcode) \
.order_by("rowno","procode","unit","num") \
.values( "drrq","ordercode","rid","procode","salebn","pn","classes","unit","taxrate","num","innums","denums","price_intax","sum_intax","nums_inplan",
"date_inplan","checkdate","prnum","barcode","rowno","grpcode","sjshsum","ssnumzb","sjprnum","promflag","refsheetid")
sum1,sum2,sum3 = 0,0,0
for item in detailList:
sum1 += item["denums"]
sum2 += item["price_intax"]
sum3 += item["denums"] * item["price_intax"]
item["jshj"] = item["denums"] * item["price_intax"]
today = datetime.datetime.today()
#查询门店信息
shop = BasShop.objects.get(grpcode=grpcode,shopcode=order.shopcode)
if shop.tel:
shop.tel = shop.tel.strip()
else:
shop.tel = ""
if shop.shopnm:
shop.shopnm = shop.shopnm.strip()
#查询供应商信息
slist = BasSupplier.objects.filter(suppcode=order.spercode).values("suppcode","chnm","linkmen","phone1","phone2","paytypeid")
if slist:
supp = slist[0]
if not supp["phone1"]:
if supp["phone2"]:
supp["phone1"] = supp["phone2"].strip()
else:
supp["phone1"] = supp["phone1"].strip()
else:
supp = {}
return render(request,"user_order_article.html",locals())
def query(request):
grpname = request.session.get("s_grpname", "")
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
shopcode = mtu.getReqVal(request, "shopCode", "")
teamcode = mtu.getReqVal(request, "teamcode", "") # 条件无效
teamname = mtu.getReqVal(request, "teamname", "") # 条件无效
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
# 当月1号
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")
codes = ""
shops = ""
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0 : len(codes) - 2]
shops = shops[0 : len(shops) - 1]
else:
codes = "1=1"
shops = "全部"
# 门店商品销售汇总
# 门店编号、总折扣、供应商折扣、销售金额、销售成本金额、销售数量
sql = "SELECT tb1.shopcode,IFNULL(tb1.num,0),IFNULL(tb1.svalue,0),IFNULL(tb1.discount,0),IFNULL(tb1.scost,0),IFNULL(tb1.zzk,0) "
sql += "FROM(SELECT a.shopcode,b.svalue,b.scost,b.num,b.discount,b.zzk "
sql += "FROM(SELECT DISTINCT shopcode FROM sales_pro "
sql += (
"WHERE grpcode='"
+ grpcode
+ "' AND ("
+ codes
+ ") AND supercode='"
+ spercode
+ "' and teamcode like '%"
+ teamcode.strip()
+ "%' and teamname like '%"
+ teamname.strip()
+ "%' ) a "
) #
sql += "LEFT JOIN(SELECT tb2.shopcode,SUM(tb2.svalue) svalue,SUM(tb2.scost)scost,SUM(tb2.num)num, "
sql += "SUM(tb2.discount) discount, SUM(tb2.zzk) zzk FROM (SELECT shopcode,svalue,scost,num,discount,zzk FROM sales_pro "
sql += (
"WHERE grpcode='"
+ grpcode
+ "' AND DATE_FORMAT(sdate,'%Y-%m-%d')>='"
+ start
+ "' AND DATE_FORMAT(sdate,'%Y-%m-%d')<='"
+ end
+ "' and teamcode like '%"
+ teamcode.strip()
+ "%' and teamname like '%"
+ teamname.strip()
+ "%' "
)
sql += "AND (" + codes + ") AND (sstyle is not null and sstyle<>'' ) AND supercode='" + spercode + "' " #
sql += ") tb2 GROUP BY tb2.shopcode)b ON a.shopcode=b.shopcode) tb1 "
sum1, sum2, sum3 = decimal.Decimal("0.0"), decimal.Decimal("0.0"), decimal.Decimal("0.0")
try:
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
# 计算合计
rslist = []
if list:
for row in list:
item = ["", "", "", ""]
item[0] = row[0]
item[1] = row[1]
item[2] = row[2] - row[3]
item[3] = row[4]
sum1 += item[1]
sum2 += item[2]
sum3 += item[3]
rslist.append(item)
else:
rslist = []
cursor.close()
# connection.close()
except Exception as e:
print(e)
if qtype == "1":
result = {"rslist": rslist}
result.setdefault("grpcode", grpcode)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("shopCode", shopcode)
result.setdefault("teamcode", teamcode)
result.setdefault("teamname", teamname)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", "%.2f" % sum1)
result.setdefault("sum2", "%.3f" % sum2)
result.setdefault("sum3", "%.3f" % sum3)
return render(request, "user_sale_counter.html", result)
else:
return export(rslist, sum1, sum2, sum3)
def query(request):
grpname = request.session.get("s_grpname", "")
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
shopcode = mtu.getReqVal(request, "shopCode", "")
teamcode = mtu.getReqVal(request, "teamcode", "") #条件无效
teamname = mtu.getReqVal(request, "teamname", "") #条件无效
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes) - 2]
shops = shops[0:len(shops) - 1]
else:
codes = '1=1'
shops = '全部'
#门店商品销售汇总
#门店编号、总折扣、供应商折扣、销售金额、销售成本金额、销售数量
sql = "SELECT tb1.shopcode,IFNULL(tb1.num,0),IFNULL(tb1.svalue,0),IFNULL(tb1.discount,0),IFNULL(tb1.scost,0),IFNULL(tb1.zzk,0) "
sql += "FROM(SELECT a.shopcode,b.svalue,b.scost,b.num,b.discount,b.zzk "
sql += "FROM(SELECT DISTINCT shopcode FROM sales_pro "
sql += "WHERE grpcode='" + grpcode + "' AND (" + codes + ") AND supercode='" + spercode + "' and teamcode like '%" + teamcode.strip(
) + "%' and teamname like '%" + teamname.strip() + "%' ) a " #
sql += "LEFT JOIN(SELECT tb2.shopcode,SUM(tb2.svalue) svalue,SUM(tb2.scost)scost,SUM(tb2.num)num, "
sql += "SUM(tb2.discount) discount, SUM(tb2.zzk) zzk FROM (SELECT shopcode,svalue,scost,num,discount,zzk FROM sales_pro "
sql += "WHERE grpcode='" + grpcode + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')>='" + start + "' AND DATE_FORMAT(sdate,'%Y-%m-%d')<='" + end + "' and teamcode like '%" + teamcode.strip(
) + "%' and teamname like '%" + teamname.strip() + "%' "
sql += "AND (" + codes + ") AND (sstyle is not null and sstyle<>'' ) AND supercode='" + spercode + "' " #
sql += ") tb2 GROUP BY tb2.shopcode)b ON a.shopcode=b.shopcode) tb1 "
sum1, sum2, sum3 = decimal.Decimal('0.0'), decimal.Decimal(
'0.0'), decimal.Decimal('0.0')
try:
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
#计算合计
rslist = []
if list:
for row in list:
item = ["", "", "", ""]
item[0] = row[0]
item[1] = row[1]
item[2] = row[2] - row[3]
item[3] = row[4]
sum1 += item[1]
sum2 += item[2]
sum3 += item[3]
rslist.append(item)
else:
rslist = []
cursor.close()
# connection.close()
except Exception as e:
print(e)
if qtype == "1":
result = {"rslist": rslist}
result.setdefault("grpcode", grpcode)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("shopCode", shopcode)
result.setdefault("teamcode", teamcode)
result.setdefault("teamname", teamname)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", "%.2f" % sum1)
result.setdefault("sum2", "%.3f" % sum2)
result.setdefault("sum3", "%.3f" % sum3)
return render(request, "user_sale_counter.html", result)
else:
return export(rslist, sum1, sum2, sum3)
def login(request):
ucode = mtu.getReqVal(request, "ucode", "")
password = mtu.getReqVal(request, "password", "")
vcode = mtu.getReqVal(request, "vcode", "")
try:
olduser = request.session.get("s_user", default=None)
vcode2 = request.session["s_vcode"]
except:
olduser = []
vcode2 = ""
response_data = {}
try:
#判断是否过期：过期后不显示业务菜单，只显示首页、退出，提醒已经过期。
if not olduser:
if vcode == vcode2:
#查询用户信息
user = BasUser.objects.get(ucode=ucode)
if user:
upwd = user.password
password = mtu.md5(password)
if upwd == password:
request.session["s_user"] = user.toDict()
request.session["s_ucode"] = user.ucode
request.session["s_uname"] = user.nm
request.session["s_utype"] = user.utype
request.session["menu_type"] = user.utype
#根据grpcode查询grpname：
# 如果utype==3，登录用户为报表系统用户
# 如果utype==2，登录用户为供应商则grpname为供应商名称
# 如果utype==1，登录用户为零售商则grpname为零售商名称
if user.utype == "3":
getRbacRole(request, ucode)
response_data[
'homeurl'] = Constants.URL_REPORT_HOME
else:
if user.utype == "2": #供应商
grpcode = findGrpCodeBySuppCode(user.grpcode)
request.session["s_grpcode"] = grpcode
try:
fee = BasFee.objects.get(
suppcode=user.grpcode,
grpcode=grpcode,
ucode=ucode)
request.session["s_fee"] = fee.toDict()
except Exception as e:
print(e)
request.session["s_fee"] = {"status": "N"}
request.session["s_suppcode"] = user.grpcode
supp = findGrpNameByCode(
user.grpcode, user.utype)
request.session["s_suppname"] = supp.chnm
request.session[
"s_contracttype"] = supp.contracttype
request.session["s_paytypeid"] = supp.paytypeid
request.session["s_bank"] = supp.bank
request.session["s_accountno"] = supp.accountno
grp = findGrpNameByCode(grpcode, "1")
request.session["s_grpname"] = grp.grpnm
response_data[
'homeurl'] = Constants.URL_SUPPLIER_HOME
if user.utype == "1": #零售商
request.session["s_grpcode"] = user.grpcode
grp = findGrpNameByCode(
user.grpcode, user.utype)
request.session["s_grpname"] = grp.grpnm
request.session["s_fee"] = {}
response_data[
'homeurl'] = Constants.URL_RETAILER_HOME
request.session["homeurl"] = response_data[
'homeurl']
#查询角色，多个角色用“，”分割
urs = findRoleByUcode(user.ucode)
if urs:
urole = urs[0]
#查询菜单权限
purlist = findPurByRcode(urs[2])
request.session["s_rcodes"] = urs[1]
request.session["isadmin"] = urs[3]
request.session["s_urole"] = urole
request.session["s_umenu"] = getMenu(purlist)
response_data['status'] = "0"
#添加登录日志
if user.utype == "2":
lastlandtime = datetime.date.today(
).strftime("%Y-%m-%d")
slist = BasSuppLand.objects.filter(
suppcode=user.grpcode,
lastlandtime__gte=
"{lastlandtime} 00:00:00".format(
lastlandtime=lastlandtime)).values(
"landcs")
if slist and slist[0]:
sland = slist[0]
landcs = sland["landcs"] + 1
BasSuppLand.objects.filter(
suppcode=user.grpcode,
lastlandtime__gte=
"{lastlandtime} 00:00:00".format(
lastlandtime=lastlandtime
)).update(landcs=landcs,
lastlandtime=datetime.
datetime.today())
else:
fee = request.session.get("s_fee")
if fee:
status = fee["status"]
else:
status = "N"
suppname = request.session.get(
"s_suppname")
lastLand = BasSuppLand.objects.values(
"allcs").latest("allcs")
if lastLand:
allcs = lastLand["allcs"] + 1
else:
allcs = 1
bs = BasSuppLand()
bs.grpcode = "00069"
bs.utype = "2"
bs.suppcode = user.grpcode
bs.landcs = 1
bs.lastlandtime = datetime.date.today()
bs.status = status
bs.supname = suppname
bs.remark = ""
bs.ylzd1 = ""
bs.ylzd2 = ""
bs.allcs = allcs
bs.save()
else:
response_data['status'] = "4"
else:
response_data['status'] = "2"
else:
response_data['status'] = "1"
else:
response_data['status'] = "3"
else:
response_data['status'] = "0"
if olduser["utype"] == "1":
response_data['homeurl'] = Constants.URL_RETAILER_HOME
elif olduser["utype"] == "3":
response_data['homeurl'] = Constants.URL_REPORT_HOME
else:
response_data['homeurl'] = Constants.URL_SUPPLIER_HOME
except Exception as e:
print(e)
return HttpResponse(json.dumps(response_data),
content_type="application/json")
def detail3(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request, "shopcode")
sdate = mtu.getReqVal(request, "sdate")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes) - 2]
shops = shops[0:len(shops) - 1]
else:
codes = '1=1'
shops = '全部'
type = ""
if sdate:
start = sdate
end = sdate
type = '日'
else:
sdate = ""
sv = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
try:
sql = "select tb1.teamcode,tb1.teamname,sum(tb1.num) as num ,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount "
sql += ",sum(tb1.scost) as scost,sum(tb1.zzk) as zzk from "
sql += "(select * from sales_pro where "
sql += "grpcode='" + grpcode + "' and (" + codes + ") and ( "
sql += "sstyle<>'' ) and DATE_FORMAT(sdate,'%Y-%m-%d')>='" + start + "' "
sql += "and DATE_FORMAT(sdate,'%Y-%m-%d')<='" + end + "' "
sql += "and supercode='" + spercode + "' order by teamcode) tb1 "
sql += "group by tb1.teamcode,tb1.teamname "
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
outlist = []
if list:
for row in list:
item = ["", "", "", "", ""]
item[0] = row[0]
item[1] = row[1]
item[2] = row[2]
item[3] = row[3] - row[4]
item[4] = row[5]
sum1 += item[2]
sum2 += item[3]
sum3 += item[4]
outlist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype == '1':
result = {"rslist": list}
result.setdefault("type", type)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sdate", sdate)
result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_analysis_dtlgz.html", result)
else:
return exportDetail3(outlist, sum1, sum2, sum3, type)
def query(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request, "shopCode", "")
start = mtu.getReqVal(request, "start", "")
end = mtu.getReqVal(request, "end", "")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes) - 2]
shops = shops[0:len(shops) - 1]
else:
codes = '1=1'
shops = '全部'
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(ifnull(tb1.svalue,0)) from (select svalue from sales_pro " \
" where grpcode='"+grpcode+"' and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' " \
" and (sstyle <> '') and supercode='"+spercode+"' ) tb1"
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select date_format(tb1.sdate,'%Y-%m-%d') AS sdate,sum(tb1.svalue) AS svalue,sum(tb1.scost) AS scost," \
"sum(tb1.num) AS num,sum(tb1.discount) AS discount " \
"from (select * from sales_pro " \
" where grpcode='"+grpcode+"' and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "\
" and (sstyle <> '') and supercode='"+spercode+"' )tb1 group by tb1.sdate order by tb1.sdate desc"
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["", "", "", "", "", "", "", "", ""]
item[0] = row[0] #sdate
item[1] = ""
item[2] = ""
item[3] = ""
item[4] = row[3] #num
item[5] = (row[1] - row[4]) #svalue - discount
item[6] = row[2] #scost
if sum:
radio = (row[1] / sum * 100)
sum4 += radio
item[7] = radio #占比
item[8] = sum4 #累计占比
sum1 += item[4]
sum2 += item[5]
sum3 += item[6]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
rslist = []
#折线图
#imgfile = analysisImg(rslist,spercode,start,end)
if qtype == '1':
result = {"rslist": rslist}
result.setdefault("shops", shops)
result.setdefault("grpname", grpname)
result.setdefault("shopCode", shopcode)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
# result.setdefault("imgfile",imgfile)
return render(request, "user_sale_analysis.html", result)
else:
return export(rslist, sum1, sum2, sum3)
def query(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname","")
shopcode = mtu.getReqVal(request,"shopCode","")
sccode = mtu.getReqVal(request,"sccode","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
pcode = mtu.getReqVal(request,"pcode","")
pname = mtu.getReqVal(request,"pname","")
tax = mtu.getReqVal(request,"tax","")
barcode = mtu.getReqVal(request,"barcode","")
orderstyle = mtu.getReqVal(request,"orderstyle","")
qtype = mtu.getReqVal(request,"qtype","1")
# fpcode = mtu.getReqVal(request,"fpcode")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
ccodes = ''
if sccode:
list = sccode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
ccodes += " sccode like '"+code+"%' or"
if ccodes:
ccodes = ccodes[0:len(ccodes)-2]
else:
ccodes = '1=1'
if not orderstyle:
orderstyle = "pcode"
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost) scost from sales_pro where "
sql1 += "grpcode='"+grpcode+"' and ("+codes+") "
sql1 += "and ("+ccodes+") "
sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql1 += "and supercode='"+spercode+"' "
if pcode:
sql1 += "and pcode like '%"+pcode.strip()+"%' "
if pname:
sql1 += "and pname like '%"+pname.strip()+"%' "
if tax:
sql1 += "and tax='"+tax+"' "
if barcode:
sql1 += "and barcode like '%"+barcode.strip()+"%' "
sql1 += "and sstyle<>'' "
# sql1 += "and fpcode like '%%' "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select pcode,barcode,pname,sccode,scname,classes,tax,num,svalue,discount,scost,zzk from( "
sql2 += " select pcode,max(barcode) barcode,pname,sccode,scname,classes,tax, "
sql2 += " sum(num) num,sum(svalue) svalue,sum(discount) discount "
sql2 += " ,sum(scost) scost,sum(zzk) zzk, "
sql2 += " sstyle from( "
sql2 += " select * from sales_pro where "
sql2 += " grpcode='"+grpcode+"' and ("+codes+") "
sql2 += " and ("+ccodes+") "
sql2 += " and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql2 += " and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql2 += " and supercode='"+spercode+"' "
if pcode:
sql2 += " and pcode like '%"+pcode.strip()+"%' "
if pname:
sql2 += " and pname like '%"+pname.strip()+"%' "
# sql2 += " and fpcode like '%%' "
if tax:
sql2 += " and tax='"+tax+"' "
if barcode:
sql2 += " and barcode like '%"+barcode.strip()+"%' "
sql2 += " and sstyle<>'' "
sql2 += " ) tb1 group by sstyle,pcode,pname,sccode,scname,classes,tax "
sql2 += ") tb2 order by "+orderstyle+" desc "
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","","","","","","","",""]
if sum:
radio = (row[10] / sum * 100) #占比
# item[12] = str(radio)
sum4 += radio #累计占比
# item[13] = sum4
item[0] = row[0]
item[1] = row[1]
item[2] = row[2]
item[3] = row[3]
item[4] = row[4]
item[5] = row[5]
item[6] = row[6]
item[7] = row[7]
item[8] = (row[8]-row[9])
item[9] = row[10]
item[10] = radio
item[11] = sum4
sum1 += item[7]
sum2 += item[8]
sum3 += item[9]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("shops",shops)
result.setdefault("grpcode",grpcode)
result.setdefault("grpname",grpname)
result.setdefault("shopCode",shopcode)
result.setdefault("sccode",sccode)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("pcode",pcode)
result.setdefault("pname",pname)
result.setdefault("tax",tax)
result.setdefault("barcode",barcode)
result.setdefault("orderstyle",orderstyle)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_sellinfo.html",result)
else:
return export(rslist,sum1,sum2,sum3)
def detail3(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request,"shopcode")
sdate = mtu.getReqVal(request,"sdate")
start = mtu.getReqVal(request,"start")
end = mtu.getReqVal(request,"end")
qtype = mtu.getReqVal(request,"qtype","1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
type = ""
if sdate:
start = sdate;
end = sdate;
type = '日'
else:
sdate = ""
sv = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
try:
sql = "select tb1.teamcode,tb1.teamname,sum(tb1.num) as num ,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount "
sql += ",sum(tb1.scost) as scost,sum(tb1.zzk) as zzk from "
sql += "(select * from sales_pro where "
sql += "grpcode='"+grpcode+"' and ("+codes+") and ( "
sql += "sstyle<>'' ) and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' "
sql += "and DATE_FORMAT(sdate,'%Y-%m-%d')<='"+end+"' "
sql += "and supercode='"+spercode+"' order by teamcode) tb1 "
sql += "group by tb1.teamcode,tb1.teamname "
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
outlist = []
if list:
for row in list:
item = ["","","","",""]
item[0] = row[0]
item[1] = row[1]
item[2] = row[2]
item[3] = row[3]-row[4]
item[4] = row[5]
sum1 += item[2]
sum2 += item[3]
sum3 += item[4]
outlist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":list}
result.setdefault("type",type)
result.setdefault("grpname",grpname)
result.setdefault("shops",shops)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("sdate",sdate)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_analysis_dtlgz.html",result)
else:
return exportDetail3(outlist,sum1,sum2,sum3,type)
def detail1(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request,"shopCode")
sdate = mtu.getReqVal(request,"sdate")
start = mtu.getReqVal(request,"start")
end = mtu.getReqVal(request,"end")
qtype = mtu.getReqVal(request,"qtype","1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
type = ""
if sdate:
start = sdate;
end = sdate;
type = '日'
else:
sdate = ""
sql ="SELECT t1.scost,t1.svalue,t1.num,t1.shopcode,t2.shopnm,t1.discount FROM( "
sql +=" SELECT SUM(scost) scost,SUM(svalue) svalue,SUM(num) num,shopcode,SUM(discount) discount FROM ( "
sql +=" SELECT * FROM sales_pro WHERE grpcode='"+grpcode+"' AND sstyle<>'' "
sql +=" AND ("+codes+") AND "
sql +=" DATE_FORMAT(sdate,'%Y-%m-%d')>= '"+start+"' AND "
sql +=" DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "
sql +=" AND supercode = '"+spercode+"' "
sql +=" ORDER BY shopcode "
sql +=" ) t3 "
sql +=" GROUP BY shopcode "
sql +=") t1 "
sql +="LEFT JOIN bas_shop t2 ON t1.shopcode=t2.shopcode "
sql +="WHERE t2.grpcode='"+grpcode+"' "
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
sum1 = decimal.Decimal("0.0")
sum2 = decimal.Decimal("0.0")
sum3 = decimal.Decimal("0.0")
rslist = []
if list:
for row in list:
item = ["","","","",""]
item[0] = row[3]
item[1] = row[4]
item[2] = row[2]
item[3] = (row[1] - row[5])
item[4] = row[0]
sum1 += row[2]
sum2 += (row[1]- row[5])
sum3 += row[0]
rslist.append(item)
if qtype=='1':
result = {"list":rslist}
result.setdefault("type",type)
result.setdefault("grpname",grpname)
result.setdefault("shops",shops)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("sdate",sdate)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_analysis_dtlshop.html",result)
else:
return exportDetail1(rslist,sum1,sum2,sum3,type)
def query(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request,"shopCode","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
qtype = mtu.getReqVal(request,"qtype","1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(ifnull(tb1.svalue,0)) from (select svalue from sales_pro " \
" where grpcode='"+grpcode+"' and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' " \
" and (sstyle <> '') and supercode='"+spercode+"' ) tb1"
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select date_format(tb1.sdate,'%Y-%m-%d') AS sdate,sum(tb1.svalue) AS svalue,sum(tb1.scost) AS scost," \
"sum(tb1.num) AS num,sum(tb1.discount) AS discount " \
"from (select * from sales_pro " \
" where grpcode='"+grpcode+"' and ("+codes+") and DATE_FORMAT(sdate,'%Y-%m-%d')>='"+start+"' and DATE_FORMAT(sdate,'%Y-%m-%d')<= '"+end+"' "\
" and (sstyle <> '') and supercode='"+spercode+"' )tb1 group by tb1.sdate order by tb1.sdate desc"
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","","","","",""]
item[0]=row[0] #sdate
item[1]=""
item[2]=""
item[3]=""
item[4]=row[3] #num
item[5]=(row[1]-row[4]) #svalue - discount
item[6]=row[2] #scost
if sum:
radio = (row[1] / sum * 100)
sum4 += radio
item[7]=radio #占比
item[8]=sum4 #累计占比
sum1 += item[4]
sum2 += item[5]
sum3 += item[6]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
rslist = []
#折线图
#imgfile = analysisImg(rslist,spercode,start,end)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("shops",shops)
result.setdefault("grpname",grpname)
result.setdefault("shopCode",shopcode)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
# result.setdefault("imgfile",imgfile)
return render(request, "user_sale_analysis.html",result)
else:
return export(rslist,sum1,sum2,sum3)
def query(request):
user = request.session.get("s_user")
suppcode = request.session.get("s_suppcode")
utype = request.session.get("s_utype")
grpname = request.session.get("s_grpname")
dept = user["dept"]
pageNum = MethodUtil.getReqVal(request,"pageNum","1") #页码
shopcode = MethodUtil.getReqVal(request,"shopCode","") #门店编号
status = MethodUtil.getReqVal(request,"status","A") #确认状态
state = MethodUtil.getReqVal(request,"state","") #过期状态
logistics = MethodUtil.getReqVal(request,"logistics","") #订单类型
start = MethodUtil.getReqVal(request,"start","") #审核日期：开始时间
end = MethodUtil.getReqVal(request,"end","") #审核日期：结束时间
orderstyle = MethodUtil.getReqVal(request,"orderstyle","") #排序条件
ordercode = MethodUtil.getReqVal(request,"ordercode","") #订单编号
inflag = MethodUtil.getReqVal(request,"inflag","") #验收状态
#组合查询条件
shopnames = ""
karrs = {}
karrs.setdefault("spercode",suppcode) #供应商ID
shopList = findShop()
if utype=="1":
karrs.setdefault('shopid',dept)
else:
if shopcode:
clist = []
codes = shopcode.split(",")
for i in range(0,len(codes)):
if codes[i]:
clist.append(codes[i])
shopnames+="%s," % shopList[str(codes[i])]
karrs.setdefault('shopcode__in',clist)
if ordercode:
karrs.setdefault('ordercode__icontains',ordercode.strip())
if status and status!="A":
karrs.setdefault("status",status)
if state=='Y':
karrs.setdefault("sdate__lt",datetime.datetime.now().strftime("%Y-%m-%d"))
elif state=='N':
karrs.setdefault("sdate__gte",datetime.datetime.now().strftime("%Y-%m-%d"))
if logistics and logistics!="A":
karrs.setdefault("logistics",logistics)
if start:
karrs.setdefault("checkdate__gte",(start))
else:
karrs.setdefault("checkdate__gte",(datetime.datetime.now() - datetime.timedelta(days = 7)).strftime("%Y-%m-%d"))
if end:
karrs.setdefault("checkdate__lte","{end} 23:59:59".format(end=end))
else:
karrs.setdefault("checkdate__lte","{end} 23:59:59".format(end=datetime.datetime.now().strftime("%Y-%m-%d")))
if inflag and inflag!="":
karrs.setdefault("inflag",inflag)
#设置默认排序方式
orderby = orderstyle
if not orderby:
orderby = "sdate"
#含税进价金额合计
sumList = Ord.objects.filter(**karrs).aggregate(taxSum = Sum("inprice_tax"))
if not sumList["taxSum"]:
sumList["taxSum"] = 0.0
#分页查询数据
pubList = Ord.objects \
.filter(**karrs) \
.order_by("inflag","-"+orderby) \
.values("remark","logistics","inflag","ordercode","checkdate","concode","style","spercode","spername","status","sdate",
"shopcode","inprice_tax","printnum","seenum","purday","spsum","sjshsum","ssspzb")
for item in pubList:
slist = OrdStatus.objects.filter(ordercode=item["ordercode"]).values("status")
if slist:
item["status"] = slist[0]["status"]
page = Paginator(pubList,__EACH_PAGE_SHOW_NUMBER,allow_empty_first_page=True).page(int(pageNum))
result = {"page":page,"pageNum":str(pageNum)}
result.setdefault("shopCode",shopcode)
result.setdefault("status",status)
result.setdefault("grpname",grpname)
result.setdefault("state",state)
result.setdefault("inflag",inflag)
result.setdefault("logistics",logistics)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("ordercode",ordercode)
result.setdefault("orderstyle",orderstyle)
result.setdefault("today",datetime.datetime.today())
result.setdefault("shopnames",shopnames[0:len(shopnames)-1])
result.setdefault("sum",'%.4f' % sumList["taxSum"])
return render(request,"user_order.html",result)
def query(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname", "")
shopcode = mtu.getReqVal(request, "shopCode", "")
sccode = mtu.getReqVal(request, "sccode", "")
start = mtu.getReqVal(request, "start", "")
end = mtu.getReqVal(request, "end", "")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes) - 2]
shops = shops[0:len(shops) - 1]
else:
codes = '1=1'
shops = '全部'
ccodes = ''
if sccode:
list = sccode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
ccodes += " sccode like '" + code + "%' or"
if ccodes:
ccodes = ccodes[0:len(ccodes) - 2]
else:
ccodes = '1=1'
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost) from sales_pro where grpcode='" + grpcode + "' "
sql1 += "and supercode='" + spercode + "' and (" + codes + ") and sstyle<>'' and "
sql1 += "date_format(sdate,'%Y-%m-%d')>='" + start + "' and date_format(sdate,'%Y-%m-%d')<='" + end + "' and (" + ccodes + ") "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select tb1.sccode,tb1.scname,sum(tb1.num) as num,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount,sum(tb1.scost) as scost "
sql2 += " from( "
sql2 += " select sccode,scname,svalue,discount,scost,num from sales_pro "
sql2 += " where grpcode='" + grpcode + "' "
sql2 += " and supercode='" + spercode + "' and (" + codes + ") and sstyle<>'' and "
sql2 += " date_format(sdate,'%Y-%m-%d')>='" + start + "' and date_format(sdate,'%Y-%m-%d')<='" + end + "' and (" + ccodes + ") "
sql2 += " ) tb1 "
sql2 += "group by tb1.sccode,tb1.scname order by tb1.sccode "
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["", "", "", "", "", "", ""]
item[0] = row[0]
item[1] = row[1]
item[2] = row[2]
item[3] = (row[3] - row[4])
item[4] = row[5]
if sum:
radio = (row[5] / sum * 100) #占比
sum4 += radio #累计占比
item[5] = radio
item[6] = sum4
sum1 += item[2]
sum2 += item[3]
sum3 += item[4]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype == '1':
result = {"rslist": rslist}
result.setdefault("shops", shops)
result.setdefault("grpcode", grpcode)
result.setdefault("grpname", grpname)
result.setdefault("shopCode", shopcode)
result.setdefault("sccode", sccode)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_ctg.html", result)
else:
return export(rslist, sum1, sum2, sum3)
def query(request):
#接收前台参数，不区别get/post方法
grpname = request.session.get("s_grpname","")
grpcode = request.session.get("s_grpcode")
suppcode = request.session.get("s_suppcode")
qtype = mtu.getReqVal(request,"qtype","1")
pageNum = mtu.getReqVal(request,"pageNum","1")
shopcode = mtu.getReqVal(request,"shopCode","")
barcode = mtu.getReqVal(request,"barcode","")
goodsName = mtu.getReqVal(request,"goodsName","")
orderstyle = mtu.getReqVal(request,"orderstyle","")
flag = mtu.getReqVal(request,"flag","")
#组合查询条件
q = Q()
karrs = {}
shopnames = ""
shopList = findShop()
#设置默认查询条件
q.add(~Q(grpcode=""),Q.AND)
karrs.setdefault("grpcode",grpcode)
karrs.setdefault("venderid",suppcode)
if shopcode:
clist = []
codes = shopcode.split(",")
for i in range(0,len(codes)):
if codes[i]:
clist.append(codes[i])
shopnames+="%s," % shopList[str(codes[i])]
karrs.setdefault('shopid__in',clist)
if barcode:
karrs.setdefault('barcodeid__icontains',barcode.strip())
if goodsName:
karrs.setdefault("name__icontains",goodsName.strip())
#商品状态
if flag:
q.add(Q(flag=flag),Q.AND)
#设置默认排序方式
orderby = orderstyle
if not orderby:
orderby = "shopid"
#分页查询数据
pubList = BasGoods.objects \
.filter(q,**karrs) \
.order_by("-"+orderby) \
.values("grpcode","name","unitname","spec","brandid","deptid","deptname","goodsid","shopid","venderid","cost",
"costtaxrate","dkrate","promflag","startdate","enddate","flag","price","barcodeid")
page = Paginator(pubList,__EACH_PAGE_SHOW_NUMBER,allow_empty_first_page=True).page(int(pageNum))
if qtype=='1':
result = {"page":page,"pageNum":str(pageNum),"gstatus":Constants.SALE_STATUS}
result.setdefault("promflag",Constants.PROM_FLAG)
result.setdefault("shopCode",shopcode)
result.setdefault("barcode",barcode)
result.setdefault("goodsName",goodsName)
result.setdefault("orderstyle",orderstyle)
result.setdefault("flag",flag)
result.setdefault("grpname",grpname)
result.setdefault("shopnames",shopnames[0:len(shopnames)-1])
return render(request,"user_base.html",result)
else:
return exportXls(pubList)
def index(request):
grpcode = request.session.get('s_grpcode','')
utype = request.session.get("s_utype")
page = mtu.getReqVal(request,"page","1")
action = mtu.getReqVal(request,"action")
nm = mtu.getReqVal(request,"nm")
ucode = mtu.getReqVal(request,"ucode")
pwd = mtu.getReqVal(request,"pwd")
dept = mtu.getReqVal(request,"dept")
status = mtu.getReqVal(request,"status")
remark = mtu.getReqVal(request,"remark")
user = BasUser()
if action!="new":
try:
user = BasUser.objects.get(ucode=ucode)
except:
user = None
if action == "save":
if not user:
user = BasUser()
user.ucode = ucode
user.budate = datetime.date.today()
user.password = mtu.md5(pwd)
user.nm = nm
user.dept = dept
user.utype = utype
user.status = status
user.remark = remark
user.grpcode = grpcode
user.save()
ke = BasKe()
ke.kbcode = user.ucode
ke.kbname = user.nm
ke.save()
elif action == "del":
try:
ke = BasKe.objects.get(kbcode=ucode)
if ke:
ke.delete()
except Exception as e:
print(e)
if user:
user.delete()
user = BasUser()
retUserList = BasUser.objects.values('ucode','nm','password','dept','depttype','utype','status','grpcode').filter(grpcode=grpcode)
paginator = Paginator(retUserList,LIMIT)
try:
retUserList = paginator.page(int(page))
except Exception as e:
print(e)
retUserList = []
pageCount = paginator.num_pages
pageList = []
for index in range(1,pageCount+1):
pageList.append(index)
usertype = (str(utype),Constants.USER_TYPE.get(utype))
rs = {}
rs["page"] = page
rs["usertype"] = usertype
rs["retUserList"] = retUserList
rs["pageList"] = pageList
rs["grpcode"] = grpcode
rs["user"] = user
return render(request,'admin/sysConf_retail_admin.html',rs)
def query(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname","")
shopcode = mtu.getReqVal(request,"shopCode","")
sccode = mtu.getReqVal(request,"sccode","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
pcode = mtu.getReqVal(request,"pcode","")
pname = mtu.getReqVal(request,"pname","")
tax = mtu.getReqVal(request,"tax","")
barcode = mtu.getReqVal(request,"barcode","")
orderstyle = mtu.getReqVal(request,"orderstyle","")
qtype = mtu.getReqVal(request,"qtype","1")
# fpcode = mtu.getReqVal(request,"fpcode")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
ccodes = ''
if sccode:
list = sccode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
ccodes += " sccode like '"+code+"%' or"
if ccodes:
ccodes = ccodes[0:len(ccodes)-2]
else:
ccodes = '1=1'
if not orderstyle:
orderstyle = "pcode"
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost) scost from sales_pro where "
sql1 += "grpcode='"+grpcode+"' and ("+codes+") "
sql1 += "and ("+ccodes+") "
sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql1 += "and supercode='"+spercode+"' "
if pcode:
sql1 += "and pcode like '%"+pcode.strip()+"%' "
if pname:
sql1 += "and pname like '%"+pname.strip()+"%' "
if tax:
sql1 += "and tax='"+tax+"' "
if barcode:
sql1 += "and barcode like '%"+barcode.strip()+"%' "
sql1 += "and sstyle<>'' "
# sql1 += "and fpcode like '%%' "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select pcode,barcode,pname,sccode,scname,classes,tax,num,svalue,discount,scost,zzk from( "
sql2 += " select pcode,max(barcode) barcode,pname,sccode,scname,classes,tax, "
sql2 += " sum(num) num,sum(svalue) svalue,sum(discount) discount "
sql2 += " ,sum(scost) scost,sum(zzk) zzk, "
sql2 += " sstyle from( "
sql2 += " select * from sales_pro where "
sql2 += " grpcode='"+grpcode+"' and ("+codes+") "
sql2 += " and ("+ccodes+") "
sql2 += " and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql2 += " and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql2 += " and supercode='"+spercode+"' "
if pcode:
sql2 += " and pcode like '%"+pcode.strip()+"%' "
if pname:
sql2 += " and pname like '%"+pname.strip()+"%' "
# sql2 += " and fpcode like '%%' "
if tax:
sql2 += " and tax='"+tax+"' "
if barcode:
sql2 += " and barcode like '%"+barcode.strip()+"%' "
sql2 += " and sstyle<>'' "
sql2 += " ) tb1 group by sstyle,pcode,pname,sccode,scname,classes,tax "
sql2 += ") tb2 order by "+orderstyle+" desc "
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","","","","","","","",""]
if sum:
radio = (row[10] / sum * 100) #占比
# item[12] = str(radio)
sum4 += radio #累计占比
# item[13] = sum4
item[0] = row[0]
item[1] = row[1]
item[2] = row[2]
item[3] = row[3]
item[4] = row[4]
item[5] = row[5]
item[6] = row[6]
item[7] = row[7]
item[8] = (row[8]-row[9])
item[9] = row[10]
item[10] = radio
item[11] = sum4
sum1 += item[7]
sum2 += item[8]
sum3 += item[9]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("shops",shops)
result.setdefault("grpcode",grpcode)
result.setdefault("grpname",grpname)
result.setdefault("shopCode",shopcode)
result.setdefault("sccode",sccode)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("pcode",pcode)
result.setdefault("pname",pname)
result.setdefault("tax",tax)
result.setdefault("barcode",barcode)
result.setdefault("orderstyle",orderstyle)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_sellinfo.html",result)
else:
return export(rslist,sum1,sum2,sum3)
def detail1(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname")
shopcode = mtu.getReqVal(request, "shopCode")
sdate = mtu.getReqVal(request, "sdate")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0, len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes) - 2]
shops = shops[0:len(shops) - 1]
else:
codes = '1=1'
shops = '全部'
type = ""
if sdate:
start = sdate
end = sdate
type = '日'
else:
sdate = ""
sql = "SELECT t1.scost,t1.svalue,t1.num,t1.shopcode,t2.shopnm,t1.discount FROM( "
sql += " SELECT SUM(scost) scost,SUM(svalue) svalue,SUM(num) num,shopcode,SUM(discount) discount FROM ( "
sql += " SELECT * FROM sales_pro WHERE grpcode='" + grpcode + "' AND sstyle<>'' "
sql += " AND (" + codes + ") AND "
sql += " DATE_FORMAT(sdate,'%Y-%m-%d')>= '" + start + "' AND "
sql += " DATE_FORMAT(sdate,'%Y-%m-%d')<= '" + end + "' "
sql += " AND supercode = '" + spercode + "' "
sql += " ORDER BY shopcode "
sql += " ) t3 "
sql += " GROUP BY shopcode "
sql += ") t1 "
sql += "LEFT JOIN bas_shop t2 ON t1.shopcode=t2.shopcode "
sql += "WHERE t2.grpcode='" + grpcode + "' "
cursor = connection.cursor()
cursor.execute(sql)
list = cursor.fetchall()
sum1 = decimal.Decimal("0.0")
sum2 = decimal.Decimal("0.0")
sum3 = decimal.Decimal("0.0")
rslist = []
if list:
for row in list:
item = ["", "", "", "", ""]
item[0] = row[3]
item[1] = row[4]
item[2] = row[2]
item[3] = (row[1] - row[5])
item[4] = row[0]
sum1 += row[2]
sum2 += (row[1] - row[5])
sum3 += row[0]
rslist.append(item)
if qtype == '1':
result = {"list": rslist}
result.setdefault("type", type)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sdate", sdate)
result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_analysis_dtlshop.html", result)
else:
return exportDetail1(rslist, sum1, sum2, sum3, type)
def detail(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
pcode = mtu.getReqVal(request,"pcode","")
qtype = mtu.getReqVal(request,"qtype","1")
# fpcode = mtu.getReqVal(request,"fpcode")
#当月1号
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")
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost),pname from(select scost,pname from sales_pro where grpcode='"+grpcode+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql1 += "and supercode='"+spercode+"' and pcode= '"+pcode+"') tb1 "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
pname = ''
if rsobj:
sum = rsobj[0]
pname = str(rsobj[1])
sql2 = "select t1.shopcode,shopnm,num,svalue,discount,scost,zzk from(select shopcode,sum(scost) scost,"
sql2 += "sum(zzk) zzk,sum(discount) discount,sum(svalue) svalue,sum(num) num "
sql2 += "from(select * from sales_pro where grpcode='"+grpcode+"'"
sql2 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql2 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql2 += "and supercode='"+spercode+"' and pcode= '"+pcode +"') t3 "
sql2 += "group by shopcode)t1,bas_shop t2 where t2.grpcode='"+grpcode+"'"
sql2 += "and t1.shopcode=t2.shopcode order by t1.shopcode"
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","",""]
if sum:
radio = (row[5] / sum * 100) #占比
sum4 += radio #累计占比
item[0] = row[1]
item[1] = row[2]
item[2] = (row[3]-row[4])
item[3] = row[5]
item[4] = radio
item[5] = sum4
sum1 += item[1]
sum2 += item[2]
sum3 += item[3]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("grpcode",grpcode)
result.setdefault("grpname",grpname)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("pcode",pcode)
result.setdefault("pname",pname.strip())
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_sellinfo_detail.html",result)
else:
return exportDetail(rslist,sum1,sum2,sum3,pname.strip())
def detail(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname", "")
shopCode = mtu.getReqVal(request, "shopCode")
teamcode = mtu.getReqVal(request, "teamcode", "")
teamname = mtu.getReqVal(request, "teamname", "")
start = mtu.getReqVal(request, "start")
end = mtu.getReqVal(request, "end")
qtype = mtu.getReqVal(request, "qtype", "1")
#当月1号
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")
shopDict = findShop()
if shopCode:
codes = " shopcode='%s' " % shopCode
shops = "%s" % shopDict[str(shopCode)]
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum6 = decimal.Decimal('0.0')
teamsv = decimal.Decimal('0.0')
try:
#销售成本总和
sql = "select sum(IFNULL(scost,0)) scost "
sql += " from sales_pro where grpcode = '" + grpcode + "' and (" + codes + ") and sstyle<>''"
sql += " and DATE_FORMAT(sdate,'%Y-%m-%d') >= '" + start + "' and DATE_FORMAT(sdate,'%Y-%m-%d') <= '" + end + "' and teamcode like '%" + teamcode.strip(
) + "%'"
sql += " and supercode='" + spercode + "' and teamname like '%" + teamname.strip(
) + "%' "
cursor = connection.cursor()
cursor.execute(sql)
rsobj = cursor.fetchone()
if rsobj:
teamsv = rsobj[0]
#门店下商品大类销售汇总
#总折扣、供应商折扣、实际销售、销售数量、销售成本、大类编码、销售日期、大类名称
sql2 = "select IFNULL(tb2.zzk,0),IFNULL(tb2.discount,0),IFNULL(tb2.svalue,0),IFNULL(tb2.num,0),IFNULL(tb2.scost,0),tb2.bccode,tb2.sdate,org.orgname "
sql2 += " from(select sum(tb1.zzk) as zzk,sum(tb1.discount) as discount,sum(tb1.svalue) as svalue,sum(tb1.num) as num,"
sql2 += " sum(tb1.scost) as scost,tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d') as sdate from("
sql2 += " select zzk,discount,svalue,num,scost,bccode,sdate "
sql2 += " from sales_pro where grpcode='" + grpcode + "' and (" + codes + ") and sstyle<>'' and DATE_FORMAT(sdate,'%Y-%m-%d') >= '" + start + "' "
sql2 += " and DATE_FORMAT(sdate,'%Y-%m-%d') <= '" + end + "' and teamcode like '%" + teamcode.strip(
) + "%' and teamname like '%" + teamname.strip() + "%' "
sql2 += " and supercode='" + spercode + "' and bccode is not null"
sql2 += " ) tb1 "
sql2 += " group by tb1.bccode,date_format(tb1.sdate,'%Y-%m-%d')"
sql2 += ") tb2,bas_org org where tb2.bccode = org.orgcode order by tb2.bccode,tb2.sdate"
# print(sql2)
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
outlist = []
type1, type2 = "", ""
if list:
for row in list:
type2 = row[5]
item = ["", "", "", "", "", "", "", "", ""]
#销售数量、实际销售、销售成本、占比、累计占比
if type1 != type2 and type1 != '':
itemsum = ["", "", "", "", "", "", "", "", ""]
itemsum[0] = '小计：'
itemsum[3] = sum1
itemsum[4] = sum2
itemsum[5] = sum3
itemsum[8] = '1'
outitemsum = itemsum[:]
outitemsum.pop()
rslist.append(itemsum)
outlist.append(outitemsum)
item[0] = str(row[5]) #大类编码
item[1] = str(row[7]) #大类名称
item[2] = row[6] #销售日期
item[3] = row[3] #销售数量
item[4] = (row[2] - row[1]) #实际销售
item[5] = row[4] #销售成本
if teamsv:
radio = (row[4] / teamsv * 100)
item[6] = str(radio) #占比
sum6 += radio
item[7] = sum6 #累计占比
item[8] = '2' #类型
type1 = type2
sum1 += item[3]
sum2 += item[4]
sum3 += item[5]
outitem = item[:]
outitem.pop()
rslist.append(item)
outlist.append(outitem)
cursor.close()
except Exception as e:
print(e)
if qtype == "1":
result = {"rslist": rslist}
result.setdefault("grpcode", grpcode)
result.setdefault("grpname", grpname)
result.setdefault("shops", shops)
result.setdefault("shopCode", shopCode)
result.setdefault("teamcode", teamcode)
result.setdefault("teamname", teamname)
result.setdefault("start", start)
result.setdefault("end", end)
result.setdefault("sum1", sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2", sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3", sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_counter_detail.html", result)
else:
return exportDetail(outlist, sum1, sum2, sum3)
def detail(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname","")
shopcode = mtu.getReqVal(request,"shopCode","")
sccode = mtu.getReqVal(request,"sccode","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
qtype = mtu.getReqVal(request,"qtype","1")
#当月1号
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")
codes = ''
shops = ''
shopDict = findShop()
if shopcode:
list = shopcode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
codes += " shopcode='%s' or" % code
shops += "%s," % shopDict[str(code)]
if codes:
codes = codes[0:len(codes)-2]
shops = shops[0:len(shops)-1]
else:
codes = '1=1'
shops = '全部'
ccodes = ''
if sccode:
list = sccode.split(",")
for i in range(0,len(list)):
code = list[i]
if code:
ccodes += " sccode like '"+code+"%' or"
if ccodes:
ccodes = ccodes[0:len(ccodes)-2]
else:
ccodes = '1=1'
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost) from sales_pro where grpcode='"+grpcode+"' "
sql1 += "and supercode='"+spercode+"' and ("+codes+") and sstyle<>'' and "
sql1 += "date_format(sdate,'%Y-%m-%d')>='"+start+"' and date_format(sdate,'%Y-%m-%d')<='"+end+"' and ("+ccodes+") "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
if rsobj:
sum = rsobj[0]
sql2 = "select tb1.sccode,tb1.scname,sum(tb1.num) as num,sum(tb1.svalue) as svalue,sum(tb1.discount) as discount,sum(tb1.scost) as scost "
sql2 += ",tb1.sdate from( "
sql2 += " select sdate,sccode,scname,svalue,discount,scost,num from sales_pro "
sql2 += " where grpcode='"+grpcode+"' "
sql2 += " and supercode='"+spercode+"' and ("+codes+") and sstyle<>'' and "
sql2 += " date_format(sdate,'%Y-%m-%d')>='"+start+"' and date_format(sdate,'%Y-%m-%d')<='"+end+"' and ("+ccodes+") "
sql2 += " ) tb1 "
sql2 += "group by tb1.sccode,tb1.scname,tb1.sdate order by tb1.sccode "
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","","","",""]
if sum:
radio = (row[5] / sum * 100) #占比
sum4 += radio #累计占比
item[0] = row[6]
item[1] = row[0]
item[2] = row[1]
item[3] = row[2]
item[4] = (row[3]-row[4])
item[5] = row[5]
item[6] = radio
item[7] = sum4
sum1 += item[3]
sum2 += item[4]
sum3 += item[5]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("shops",shops)
result.setdefault("grpcode",grpcode)
result.setdefault("grpname",grpname)
result.setdefault("shopCode",shopcode)
result.setdefault("sccode",sccode)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_ctg_detail.html",result)
else:
return exportDetail(rslist,sum1,sum2,sum3)
def detail(request):
spercode = request.session.get("s_suppcode")
grpcode = request.session.get("s_grpcode")
grpname = request.session.get("s_grpname","")
start = mtu.getReqVal(request,"start","")
end = mtu.getReqVal(request,"end","")
pcode = mtu.getReqVal(request,"pcode","")
qtype = mtu.getReqVal(request,"qtype","1")
# fpcode = mtu.getReqVal(request,"fpcode")
#当月1号
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")
sum = decimal.Decimal('0.0')
sum1 = decimal.Decimal('0.0')
sum2 = decimal.Decimal('0.0')
sum3 = decimal.Decimal('0.0')
sum4 = decimal.Decimal('0.0')
try:
sql1 = "select sum(scost),pname from(select scost,pname from sales_pro where grpcode='"+grpcode+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql1 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql1 += "and supercode='"+spercode+"' and pcode= '"+pcode+"') tb1 "
cursor = connection.cursor()
cursor.execute(sql1)
rsobj = cursor.fetchone()
pname = ''
if rsobj:
sum = rsobj[0]
pname = str(rsobj[1])
sql2 = "select t1.shopcode,shopnm,num,svalue,discount,scost,zzk from(select shopcode,sum(scost) scost,"
sql2 += "sum(zzk) zzk,sum(discount) discount,sum(svalue) svalue,sum(num) num "
sql2 += "from(select * from sales_pro where grpcode='"+grpcode+"'"
sql2 += "and date_format(sdate,'%Y-%m-%d')>='"+start+"' "
sql2 += "and date_format(sdate,'%Y-%m-%d')<='"+end+"' "
sql2 += "and supercode='"+spercode+"' and pcode= '"+pcode +"') t3 "
sql2 += "group by shopcode)t1,bas_shop t2 where t2.grpcode='"+grpcode+"'"
sql2 += "and t1.shopcode=t2.shopcode order by t1.shopcode"
cursor.execute(sql2)
list = cursor.fetchall()
rslist = []
if list:
for row in list:
item = ["","","","","",""]
if sum:
radio = (row[5] / sum * 100) #占比
sum4 += radio #累计占比
item[0] = row[1]
item[1] = row[2]
item[2] = (row[3]-row[4])
item[3] = row[5]
item[4] = radio
item[5] = sum4
sum1 += item[1]
sum2 += item[2]
sum3 += item[3]
rslist.append(item)
cursor.close()
except Exception as e:
print(e)
if qtype=='1':
result = {"rslist":rslist}
result.setdefault("grpcode",grpcode)
result.setdefault("grpname",grpname)
result.setdefault("start",start)
result.setdefault("end",end)
result.setdefault("pcode",pcode)
result.setdefault("pname",pname.strip())
result.setdefault("sum1",sum1.quantize(decimal.Decimal('0.00')))
result.setdefault("sum2",sum2.quantize(decimal.Decimal('0.000')))
result.setdefault("sum3",sum3.quantize(decimal.Decimal('0.000')))
return render(request, "user_sale_sellinfo_detail.html",result)
else:
return exportDetail(rslist,sum1,sum2,sum3,pname.strip())
def index(request):
grpcode = request.session.get('s_grpcode', '')
utype = request.session.get("s_utype")
page = mtu.getReqVal(request, "page", "1")
action = mtu.getReqVal(request, "action")
nm = mtu.getReqVal(request, "nm")
ucode = mtu.getReqVal(request, "ucode")
pwd = mtu.getReqVal(request, "pwd")
dept = mtu.getReqVal(request, "dept")
status = mtu.getReqVal(request, "status")
remark = mtu.getReqVal(request, "remark")
user = BasUser()
if action != "new":
try:
user = BasUser.objects.get(ucode=ucode)
except:
user = None
if action == "save":
if not user:
user = BasUser()
user.ucode = ucode
user.budate = datetime.date.today()
user.password = mtu.md5(pwd)
user.nm = nm
user.dept = dept
user.utype = utype
user.status = status
user.remark = remark
user.grpcode = grpcode
user.save()
ke = BasKe()
ke.kbcode = user.ucode
ke.kbname = user.nm
ke.save()
elif action == "del":
try:
ke = BasKe.objects.get(kbcode=ucode)
if ke:
ke.delete()
except Exception as e:
print(e)
if user:
user.delete()
user = BasUser()
retUserList = BasUser.objects.values('ucode', 'nm', 'password', 'dept',
'depttype', 'utype', 'status',
'grpcode').filter(grpcode=grpcode)
paginator = Paginator(retUserList, LIMIT)
try:
retUserList = paginator.page(int(page))
except Exception as e:
print(e)
retUserList = []
pageCount = paginator.num_pages
pageList = []
for index in range(1, pageCount + 1):
pageList.append(index)
usertype = (str(utype), Constants.USER_TYPE.get(utype))
rs = {}
rs["page"] = page
rs["usertype"] = usertype
rs["retUserList"] = retUserList
rs["pageList"] = pageList
rs["grpcode"] = grpcode
rs["user"] = user
return render(request, 'admin/sysConf_retail_admin.html', rs)
