def query(date):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
rlist = Kglistnoret.objects\
.values("shopid", "sdate", "stime", "listno", "posid", "cashierid",
"name", "payreson","paytype", "payvalue")\
.filter(sdate=date,shopid__in=rbacDepartList)
formate_data(rlist)
# 商品退货明细
conn = mtu.getMysqlConn()
sql = "SELECT shopid, sdate, stime, listno, posid, cashierid, name, DeptID, DeptName, goodsid, goodsname, xAmount," \
"salevalue, DiscValue, truevalue, SaleType, Price, DiscType " \
"FROM `kggoodsret` " \
"WHERE (`shopid` IN ({rbacDepart}) AND `sdate` = '{date}' " \
"AND LEFT(`DeptID`,2) IN ({rbacClass}))"\
.format(rbacDepart=rbacDepart,date=date,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sql)
dlist = cur.fetchall()
cur.close()
conn.close()
formate_data(dlist)
data = {"rlist": list(rlist), 'dlist': list(dlist)}
return data
def query(sgroupid, date):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
title = ''
if sgroupid == '2':
title = '食品'
if sgroupid == '3':
title = '非食'
conn = mtu.getMysqlConn()
sql= "SELECT `KGnegstock`.`shopid`, `KGnegstock`.`shopname`, `KGnegstock`.`sGroupID`, `KGnegstock`.`sGroupName`, " \
"`KGnegstock`.`GoodsID`, `KGnegstock`.`GoodsName`, `KGnegstock`.`qty`, `KGnegstock`.`CostValue`, `KGnegstock`." \
"`Spec`, `KGnegstock`.`UnitName`, `KGnegstock`.`deptid`, `KGnegstock`.`deptname`, `KGnegstock`.`Venderid`, " \
"`KGnegstock`.`VenderName`, `KGnegstock`.`Promflag`, `KGnegstock`.`OpenQty`, `KGnegstock`.`ReceiptDate`, " \
"`KGnegstock`.`OnReceiptQty`, `KGnegstock`.`SaleDate` " \
"FROM `KGnegstock` " \
"WHERE (`KGnegstock`.`shopid` IN ({rbacDepart}) AND `KGnegstock`.`SaleDate` = '{date}' " \
"AND LEFT(`KGnegstock`.`deptid`,2) IN ({rbacClass}) AND `KGnegstock`.`sGroupID` = {sgroupid}) " \
"ORDER BY `KGnegstock`.`shopid` ASC"\
.format(rbacDepart=rbacDepart,rbacClass=rbacClass,date=date,sgroupid=sgroupid)
cur = conn.cursor()
cur.execute(sql)
resList = cur.fetchall()
cur.close()
conn.close()
formate_data(resList)
return locals()
def get_colour_checksum(request):
sql = "CHECKSUM TABLE bas_user"
conn = mth.getMysqlConn()
cur = conn.cursor()
cur.execute(sql)
res = cur.fetchone()
print(res)
return str(res['Checksum'])
def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def getshopid():
'''
获取门店编码
:return list:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select ShopID, ShopName from bas_shop_region"
cur.execute(sql)
res = cur.fetchall()
# 释放
mtu.close(conn, cur)
return res
def 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 findKxListBySheetId(sheetId):
try:
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = """SELECT b.inshopid,b.kno,k.kname,b.kmoney,'扣款(从货款扣)' as kkflag,b.note,k.prtflag
FROM billheadkxitem0 b LEFT JOIN kxd k ON k.kno = b.kno
WHERE b.kkflag=1 and b.sheetid='{sheetId}' order by b.inshopid
""".format(sheetId=sheetId) #
cur.execute(sql)
kxlist = cur.fetchall()
except Exception as e:
print(e)
kxlist = []
finally:
cur.close()
conn.close()
return kxlist
def findKxListBySheetId(sheetId):
try:
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = """SELECT b.inshopid,b.kno,k.kname,b.kmoney,'扣款(从货款扣)' as kkflag,b.note,k.prtflag
FROM billheadkxitem0 b LEFT JOIN kxd k ON k.kno = b.kno
WHERE b.kkflag=1 and b.sheetid='{sheetId}' order by b.inshopid
""".format(sheetId=sheetId) #
cur.execute(sql)
kxlist = cur.fetchall()
except Exception as e:
print(e)
kxlist = []
finally:
cur.close()
conn.close()
return kxlist
def getallcode():
'''
获取所有商品类别编码
:return:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select distinct(deptid) from kwsaletop10"
cur.execute(sql)
res = cur.fetchall()
# 释放
cur.close()
lis = []
for y in res:
lis.append(y['deptid'])
return lis
def getallcode():
'''
获取所有商品类别编码
:return:
'''
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = "select distinct(deptid) from kwsaletop10"
cur.execute(sql)
res = cur.fetchall()
# 释放
cur.close()
lis = []
for y in res:
lis.append(y['deptid'])
return lis
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 query():
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
today = datetime.date.today()
monthFirst = datetime.date.today().replace(day=1)
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year,
datetime.date.today().month, 1)
today = datetime.date(datetime.date.today().year,
datetime.date.today().month,
1) - datetime.timedelta(days=1)
todayStr = today.strftime('%y-%m-%d')
monthFirstStr = str(monthFirst)
shopTop = []
shopTopTotal = {'shopid': '合计', 'shopname': ''}
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 月累计报损
sqlMonthTotal = 'select shopid,shopname,sum(costvalue) costvalueSum,sum(lostvalue) lostvalueSum,(sum(lostvalue)/sum(costvalue)) lrateSum ' \
'from KGshop17lost ' \
'where sdate between "{monthFirstStr}" and "{todayStr}" and shopid in ({rbacDepart})' \
'group by shopid order by shopid '\
.format(monthFirstStr=monthFirstStr,todayStr=todayStr,rbacDepart=rbacDepart)
cur.execute(sqlMonthTotal)
shopTop = cur.fetchall()
# 每日报损
sqlDaily = 'select sdate,shopid,costvalue,lostvalue,lrate ' \
'from KGshop17lost ' \
'where sdate between "{monthFirstStr}" and "{todayStr}" and shopid in ({rbacDepart})' \
'order by sdate '\
.format(monthFirstStr=monthFirstStr,todayStr=todayStr,rbacDepart=rbacDepart)
cur.execute(sqlDaily)
listDaily = cur.fetchall()
# 计算纵向合计、格式化数据、拼接月累计报损和每日报损
for obj1 in shopTop:
obj1['lostvalueSum'] = float(
obj1['lostvalueSum']) if obj1['lostvalueSum'] else 0
if 'lostvalueSum' in shopTopTotal:
shopTopTotal['lostvalueSum'] += obj1['lostvalueSum']
else:
shopTopTotal['lostvalueSum'] = obj1['lostvalueSum']
obj1['costvalueSum'] = float(
obj1['costvalueSum']) if obj1['costvalueSum'] else 0
if 'costvalueSum' in shopTopTotal:
shopTopTotal['costvalueSum'] += obj1['costvalueSum']
else:
shopTopTotal['costvalueSum'] = obj1['costvalueSum']
obj1['lrateSum'] = str(float(
'%0.2f' %
(obj1['lrateSum'] * 100))) + '%' if obj1['lrateSum'] else '0.00%'
shopTopTotal['lrateSum'] = shopTopTotal['lostvalueSum'] / shopTopTotal[
'costvalueSum']
shopTopTotal['lrateSum'] = str(
float('%0.2f' % (shopTopTotal['lrateSum'] * 100))) + '%'
for obj2 in listDaily:
date = str(obj2['sdate'])[8:10]
if obj1['shopid'] == obj2['shopid']:
obj1['costvalue_' + date] = float(
'%0.2f' % obj2['costvalue']) if obj2['costvalue'] else 0
if 'costvalue_' + date in shopTopTotal:
shopTopTotal['costvalue_' + date] += obj1['costvalue_' +
date]
shopTopTotal['costvalue_' + date] = float(
'%0.2f' % shopTopTotal['costvalue_' + date])
else:
shopTopTotal['costvalue_' + date] = obj1['costvalue_' +
date]
obj1['lostvalue_' + date] = float(
'%0.2f' % obj2['lostvalue']) if obj2['lostvalue'] else 0
if 'lostvalue_' + date in shopTopTotal:
shopTopTotal['lostvalue_' + date] += obj1['lostvalue_' +
date]
shopTopTotal['lostvalue_' + date] = float(
'%0.2f' % shopTopTotal['lostvalue_' + date])
else:
shopTopTotal['lostvalue_' + date] = obj1['lostvalue_' +
date]
obj1['lrate_' + date] = str(float(
obj2['lrate'] * 100)) + '%' if obj2['lrate'] else '0.00%'
shopTopTotal['lrate_' +
date] = (shopTopTotal['lostvalue_' + date] /
shopTopTotal['costvalue_' + date])
shopTopTotal['lrate_' + date] = str(
float('%0.2f' %
(shopTopTotal['lrate_' + date] * 100))) + '%'
TotalDict = {'shopTopTotal': shopTopTotal}
return locals()
def query():
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
monthFirst = datetime.date.today().replace(day=1)
today = datetime.datetime.today()
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year,
datetime.date.today().month - 1, 1)
today = datetime.date(datetime.date.today().year,
datetime.date.today().month,
1) - datetime.timedelta(1)
todayStr = today.strftime('%y-%m-%d')
monthFirstStr = str(monthFirst)
conn = mtu.getMysqlConn()
sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
'FROM KNegativestock ' \
'WHERE sdate BETWEEN "{monthFirstStr}" AND "{todayStr}" AND ShopID in ({rbacDepart}) ' \
'GROUP BY ShopID ORDER BY ShopID'\
.format(monthFirstStr=monthFirstStr,todayStr=todayStr,rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sqlTop)
listTop = cur.fetchall()
listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0} # 初始化最后一行
for i in range(0, len(listTop)):
if (not listTop[i]['qtyzSum']):
listTop[i]['qtyzSum'] = 0
else:
listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
listTop[i]['qtyzSum'] = round(
(listTop[i]['qtyzSum']) / int(today.day))
if 'qtyzSum' in listTotal:
listTotal['qtyzSum'] += listTop[i]['qtyzSum']
else:
listTotal['qtyzSum'] = listTop[i]['qtyzSum']
if (not listTop[i]['qtylSum']):
listTop[i]['qtylSum'] = 0
else:
listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
listTop[i]['qtylSum'] = round(
(listTop[i]['qtylSum']) / int(today.day))
if 'qtylSum' in listTotal:
listTotal['qtylSum'] += listTop[i]['qtylSum']
else:
listTotal['qtylSum'] = listTop[i]['qtylSum']
if (not listTop[i]['zhonbiSum']):
listTop[i]['zhonbiSum'] = 0
else:
listTop[i]['zhonbiSum'] = float('%0.2f' %
(listTop[i]['zhonbiSum'] * 100))
listTotal['zhonbiSum'] = listTotal['qtylSum'] / listTotal['qtyzSum']
listTotal['zhonbiSum'] = str(
float('%0.2f' % (listTotal['zhonbiSum'] * 100))) + '%'
listTotal['mingciSum'] = ''
sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM KNegativestock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
"FROM KNegativestock AS b " \
"WHERE ShopID ='{ShopID}' AND sdate BETWEEN '{monthFirstStr}' AND '{todayStr}' " \
"GROUP BY sdate"\
.format(ShopID = listTop[i]['ShopID'],monthFirstStr=monthFirstStr, todayStr=todayStr)
cur.execute(sql)
listDetail = cur.fetchall()
for item in listDetail:
date = str(item['sdate'])[8:10]
if (not item['qtyz']):
listTop[i]['qtyz_' + date] = 0
else:
listTop[i]['qtyz_' + date] = float(item['qtyz'])
if 'qtyz_' + date in listTotal:
listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
else:
listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]
if (not item['qtyl']):
listTop[i]['qtyl_' + date] = 0
else:
listTop[i]['qtyl_' + date] = float(item['qtyl'])
if 'qtyl_' + date in listTotal:
listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
else:
listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]
if (not item['zhonbi']):
listTop[i]['zhonbi_' + date] = 0
else:
listTop[i]['zhonbi_' + date] = float('%0.2f' %
(item['zhonbi'] * 100))
listTotal['zhonbi_' +
date] = listTotal['qtyl_' + date] / listTotal['qtyz_' +
date]
listTotal['zhonbi_' + date] = str(
float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'
listTotal['mingci_' + date] = ''
TotalDict = {'listTotal': listTotal}
listTop = ranking(listTop, 'zhonbiSum', 'mingciSum')
monthFirstReal = int(str(listDetail[0]['sdate'])[8:10])
for date in range(monthFirstReal, today.day + 1):
if (date < 10):
listTop = ranking(listTop, 'zhonbi_0' + str(date),
'mingci_0' + str(date))
else:
listTop = ranking(listTop, 'zhonbi_' + str(date),
'mingci_' + str(date))
listTop.sort(key=lambda x: x['ShopID'])
###课组汇总###
sqlDept = 'select deptid,deptidname,sum(qtyz) qtyz,sum(qtyl) qtyl,(sum(qtyl)/sum(qtyz)) zhonbi ' \
'from KNegativestock ' \
'where sdate="{todayStr}" AND deptid in ({rbacClass}) ' \
'group by deptid,deptidname order by deptid'\
.format(todayStr=todayStr,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sqlDept)
listDept = cur.fetchall()
for obj in listDept:
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
###负库存课组明细###
sqlDeptDetail = 'SELECT shopid,shopname,deptid,deptidname,qtyz,qtyl,zhonbi ' \
'FROM KNegativestock ' \
'WHERE sdate = "{todayStr}" AND shopid IN ({rbacDepart}) AND deptid in ({rbacClass})' \
'GROUP BY deptid,shopid'\
.format(todayStr=todayStr,rbacDepart=rbacDepart,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sqlDeptDetail)
listDeptDetail = cur.fetchall()
for obj in listDeptDetail:
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
cur.close()
conn.close()
return locals()
def query(date):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
yesterday = date.strftime("%Y-%m-%d")
# 查询当月销售
try:
conn = mtu.getMysqlConn()
cur = conn.cursor()
sqlKwholesale = '''
select sdate,shopid,sum(salevalue) wsalevalue,sum(costvalue) wcostvalue,sum(salevalue-costvalue) wsalegain,sum(salevalue-costvalue)/sum(salevalue)*100 wgaintx from kwholesale where
sdate between '{start}' and '{end}' and shopid in ({rbacDepart})
group by sdate,shopid
'''.format(start=yesterday, end=yesterday,rbacDepart=rbacDepart)
cur.execute(sqlKwholesale)
listKwholesale = cur.fetchall()
sqlShopData = '''
select sdate,shopid,b.Shopnm,tradeprice,TradeNumber,SaleValue,DiscValue,(SaleValue-DiscValue) sale,costvalue, (SaleValue-DiscValue-costvalue) salegain,
(SaleValue-DiscValue-costvalue)/(SaleValue-DiscValue)*100 gaintx
from RPT_SaleShop a,bas_shop b where sdate between '{start}' and '{end}' and shopid in ({rbacDepart})
and a.shopid=b.Shopcode and b.khbank='超市店 ';
'''.format(start=yesterday, end=yesterday,rbacDepart=rbacDepart)
cur.execute(sqlShopData)
listShopData = cur.fetchall()
for shop in listShopData :
shopId = shop['shopid']
for Kwholesale in listKwholesale :
if Kwholesale['shopid'] == shopId:
shop['wsalevalue'] = Kwholesale['wsalevalue']
shop['wcostvalue'] = Kwholesale['wcostvalue']
shop['wsalegain'] = Kwholesale['wsalegain']
shop['wgaintx'] = Kwholesale['wgaintx']
rlist = []
sumDict = {}
sum = {"shopid": "合计", "shopnm": "", "tradeprice": 0.0, "tradenumber": 0, "salevalue": 0.0, "discvalue": 0.0,
"sale": 0.0,
"costvalue": 0.0, "salegain": 0.0, "gaintx": "", "yhzhanbi": "", "wsalevalue": 0.0, "wcostvalue": 0.0,
"wsalegain": 0.0, "wgaintx": ""}
sumDict.setdefault("sum1", sum)
unsumkey = ["gaintx", "wgaintx"]
for obj in listShopData:
if obj['shopid'] != 'C009':
row = {}
for key in obj.keys():
item = obj[key]
newkey = key.lower()
if item:
if isinstance(item, int) or isinstance(item, decimal.Decimal):
if newkey not in unsumkey:
row.setdefault(newkey, float(item))
sum[newkey] += float(item)
else:
row.setdefault(newkey, "%0.2f" % item + "%")
elif isinstance(item, datetime.datetime):
row.setdefault(newkey, item.strftime("%Y-%m-%d"))
else:
row.setdefault(newkey, item)
else:
row.setdefault(newkey, "")
if row["sale"] > 0:
yhzhanbi = "%0.2f" % (row["discvalue"] * 100.0 / row["sale"]) + "%"
else:
yhzhanbi = ""
row.setdefault("yhzhanbi", yhzhanbi)
rlist.append(row)
if sum["sale"] > 0:
sum["gaintx"] = "%0.2f" % (sum["salegain"] * 100.0 / sum["sale"]) + "%"
sum["yhzhanbi"] = "%0.2f" % (sum["discvalue"] * 100.0 / sum["sale"]) + "%"
else:
sum["gaintx"] = ""
sum["yhzhanbi"] = ""
if sum["wsalevalue"] > 0:
sum["wgaintx"] = "%0.2f" % (sum["wsalegain"] * 100.0 / sum["wsalevalue"]) + "%"
else:
sum["wgaintx"] = ""
for key in sum.keys():
item = sum[key]
if not isinstance(item, str) and not isinstance(item, int):
sum[key] = "%0.2f" % item
except Exception as e:
print(">>>>>>>>>>>>[异常]", e)
# 计算月累加合计
data = {"gslist":rlist,"sumDict":sumDict}
return data
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(costvalue) as costvaluesum, sum(reth) as rethsum, (sum(reth) / sum(costvalue)) as retrate " \
"from KGretshop " \
"where ShopID!='C009' AND sdate between '" + monfirstday + "' and '" + yesterday + "' " \
"group by shopid " \
"order by shopid"
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {'sequenceNumber': '合计', 'shopid': '', 'shopname': '', 'costvaluesum': '', 'rethsum': '',
'retrate': ''}
# 汇总总计（每月1日到当日）
tempcostvaluesum = 0.00
temprethsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(float("%0.2f" % float(listtop[i][key] * 100))) + '%'
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
# 添加汇总合计
tempcostvaluesum += float(listtop[i]['costvaluesum'])
temprethsum += float(listtop[i]['rethsum'])
sql = "select sdate, shopid, costvalue, reth, reth / costvalue as ret " \
"from `KGretshop` " \
"where shopid='" + listtop[i]['shopid'] + "' " \
"and sdate between '" + monfirstday + "' and '" + yesterday + "'"
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['reth']):
listtop[i]['rethsum_' + date] = 0
else:
listtop[i]['rethsum_' + date] = float(item['reth'])
if 'rethsum_' + date in listtopTotal:
listtopTotal['rethsum_' + date] += listtop[i]['rethsum_' + date]
else:
listtopTotal['rethsum_' + date] = listtop[i]['rethsum_' + date]
if (not item['costvalue']):
listtop[i]['costvaluesum_' + date] = 0
else:
listtop[i]['costvaluesum_' + date] = float(item['costvalue'])
if 'costvaluesum_' + date in listtopTotal:
listtopTotal['costvaluesum_' + date] += listtop[i]['costvaluesum_' + date]
else:
listtopTotal['costvaluesum_' + date] = listtop[i]['costvaluesum_' + date]
if (not item['ret']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(float("%0.2f" % (item['ret'] * 100))) + '%'
# 添加当日汇总
listtopTotal['rethsum_' + date] = float("%0.2f" % (listtopTotal['rethsum_' + date]))
listtopTotal['costvaluesum_' + date] = float("%0.2f" % (listtopTotal['costvaluesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" % (listtopTotal['rethsum_' + date] / listtopTotal['costvaluesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['costvaluesum'] = "%0.2f" % tempcostvaluesum
listtopTotal['rethsum'] = "%0.2f" % temprethsum
tempcandr = "%0.2f" % (temprethsum / tempcostvaluesum)
listtopTotal['retrate'] = str("%0.2f" % float(float(tempcandr) * 100)) + '%'
mtu.close(conn, cur)
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
return locals()
def query(yesterday):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(shopsale) as shopsalesum, sum(ret) as retsum, (sum(ret) / sum(shopsale)) as retrate " \
"from `KGshopretsale` " \
"where sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
"group by shopid order by shopid"\
.format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {
'sequenceNumber': '合计',
'shopid': '',
'shopname': '',
'shopsalesum': '',
'retsum': '',
'retrate': ''
}
# 汇总总计（每月1日到当日）
tempshopsalesum = 0.00
tempretsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(
abs(float(
"%0.2f" % float(listtop[i][key] * 100)))) + '%'
elif isinstance(row, decimal.Decimal) and key == 'retsum':
listtop[i][key] = abs(
float("%0.2f" % float(listtop[i][key])))
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加汇总合计
tempshopsalesum += float(listtop[i]['shopsalesum'])
tempretsum += float(listtop[i]['retsum'])
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
sql = "select sdate, shopid, shopsale, ret, ret / shopsale as retc " \
"from `KGshopretsale` " \
"where shopid='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
.format(shopID=listtop[i]['shopid'],monfirstday=monfirstday,yesterday=yesterday)
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['ret']):
listtop[i]['retsum_' + date] = 0
else:
listtop[i]['retsum_' + date] = abs(float(item['ret']))
if 'retsum_' + date in listtopTotal:
listtopTotal['retsum_' + date] += listtop[i]['retsum_' + date]
else:
listtopTotal['retsum_' + date] = listtop[i]['retsum_' + date]
if (not item['shopsale']):
listtop[i]['shopsalesum_' + date] = 0
else:
listtop[i]['shopsalesum_' + date] = float(item['shopsale'])
if 'shopsalesum_' + date in listtopTotal:
listtopTotal['shopsalesum_' +
date] += listtop[i]['shopsalesum_' + date]
else:
listtopTotal['shopsalesum_' +
date] = listtop[i]['shopsalesum_' + date]
if (not item['retc']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(
abs(float("%0.2f" % (item['retc'] * 100)))) + '%'
# 添加当日汇总
listtopTotal['retsum_' + date] = float(
"%0.2f" % (listtopTotal['retsum_' + date]))
listtopTotal['shopsalesum_' + date] = float(
"%0.2f" % (listtopTotal['shopsalesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" %
(listtopTotal['retsum_' + date] /
listtopTotal['shopsalesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['shopsalesum'] = float("%0.2f" % tempshopsalesum)
listtopTotal['retsum'] = float("%0.2f" % tempretsum)
listtopTotal['retrate'] = str("%0.2f" % float(
float(listtopTotal['retsum'] / listtopTotal['shopsalesum']) *
100)) + '%'
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
# 退货明细
sqldetail = "select shopid, shopname, sdate, stime, listno, posid, cashierid, goodsid, goodsname, deptid, amount, sale " \
"from `KGshopretsaleitem` " \
"where sdate='{yesterday}' and shopid in ({rbacDepart}) and LEFT(deptid,2) in ({rbacClass})" \
"order by shopid"\
.format(yesterday=yesterday,rbacDepart=rbacDepart,rbacClass=rbacClass)
cur = conn.cursor()
cur.execute(sqldetail)
retdetail = cur.fetchall()
# 格式化数据
for i in range(0, len(retdetail)):
for key in retdetail[i].keys():
row = retdetail[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal):
retdetail[i][key] = "%0.2f" % float(retdetail[i][key])
elif isinstance(row, datetime.datetime):
retdetail[i][key] = retdetail[i][key].strftime("%Y-%m-%d")
else:
retdetail[i][key] = retdetail[i][key]
mtu.close(conn, cur)
return locals()
def query():
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
###门店排名###
monthFirst = datetime.date.today().replace(day=1)
today = datetime.datetime.today()
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year,
datetime.date.today().month - 1, 1)
today = datetime.date(datetime.date.today().year,
datetime.date.today().month,
1) - datetime.timedelta(1)
todayStr = today.strftime('%y-%m-%d')
# 获取当月天数
dayNum = todayStr[-2:]
monthFirstStr = str(monthFirst)
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 各门店报表数据
listShops = []
listShopTotal = []
# 月份汇总数据
sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
'FROM Kzerostock ' \
'WHERE sdate BETWEEN "{monthFirstStr}" AND "{todayStr}" AND ShopID IN ({rbacDepart})' \
'GROUP BY ShopID ORDER BY ShopID'\
.format(monthFirstStr=monthFirstStr,todayStr=todayStr,rbacDepart=rbacDepart)
cur.execute(sqlTop)
listTop = cur.fetchall()
# 门店排名合计
listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0} # 初始化最后一行
# 转换数据类型并求纵向合计
for i in range(0, len(listTop)):
if (not listTop[i]['qtyzSum']):
listTop[i]['qtyzSum'] = 0
else:
listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
listTop[i]['qtyzSum'] = round(
(listTop[i]['qtyzSum']) / int(today.day))
if 'qtyzSum' in listTotal:
listTotal['qtyzSum'] += listTop[i]['qtyzSum']
else:
listTotal['qtyzSum'] = listTop[i]['qtyzSum']
if (not listTop[i]['qtylSum']):
listTop[i]['qtylSum'] = 0
else:
listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
listTop[i]['qtylSum'] = round(
(listTop[i]['qtylSum']) / int(today.day))
if 'qtylSum' in listTotal:
listTotal['qtylSum'] += listTop[i]['qtylSum']
else:
listTotal['qtylSum'] = listTop[i]['qtylSum']
if (not listTop[i]['zhonbiSum']):
listTop[i]['zhonbiSum'] = 0
else:
listTop[i]['zhonbiSum'] = float('%0.2f' %
(listTop[i]['zhonbiSum'] * 100))
# 有效商品数，有效商品数合计 除以天数 四舍五入取整
listTotal['qtyzSum'] = round(
float('%0.2f' % (listTotal['qtyzSum'] / int(dayNum))))
listTotal['qtylSum'] = round(
float('%0.2f' % (listTotal['qtyzSum'] / int(dayNum))))
listTotal['zhonbiSum'] = listTotal['qtylSum'] / listTotal['qtyzSum']
listTotal['zhonbiSum'] = str(
float('%0.2f' % (listTotal['zhonbiSum'] * 100))) + '%'
listTotal['mingciSum'] = ''
sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM Kzerostock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
"FROM Kzerostock AS b " \
"WHERE ShopID ='{ShopID}' AND sdate BETWEEN '{monthFirstStr}' AND '{todayStr}' " \
"GROUP BY sdate"\
.format(ShopID=listTop[i]['ShopID'],monthFirstStr=monthFirstStr,todayStr=todayStr)
cur.execute(sql)
listDetail = cur.fetchall()
for item in listDetail:
date = str(item['sdate'])[8:10]
if (not item['qtyz']):
listTop[i]['qtyz_' + date] = 0
else:
listTop[i]['qtyz_' + date] = float(item['qtyz'])
if 'qtyz_' + date in listTotal:
listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
else:
listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]
if (not item['qtyl']):
listTop[i]['qtyl_' + date] = 0
else:
listTop[i]['qtyl_' + date] = float(item['qtyl'])
if 'qtyl_' + date in listTotal:
listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
else:
listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]
if (not item['zhonbi']):
listTop[i]['zhonbi_' + date] = 0
else:
listTop[i]['zhonbi_' + date] = float('%0.2f' %
(item['zhonbi'] * 100))
listTotal['zhonbi_' +
date] = listTotal['qtyl_' + date] / listTotal['qtyz_' +
date]
listTotal['zhonbi_' + date] = str(
float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'
listTotal['mingci_' + date] = ''
# 各个门店的sheet数据
sqlShop = "SELECT ShopID,shopname,deptid,deptidname,qtyz,qtyl,zhonbi " \
"FROM Kzerostock " \
"WHERE ShopID ='{ShopID}' AND sdate = '{todayStr}' AND deptid IN ({rbacClass})" \
.format(ShopID=listTop[i]['ShopID'],todayStr=todayStr, rbacClass=rbacClass)
cur.execute(sqlShop)
listShop = cur.fetchall()
# 门店合计、转换数据格式
shopTotal = {'qtyz': 0, 'qtyl': 0}
for d in listShop:
shopTotal['ShopID'] = '合计'
shopTotal['shopname'] = ''
shopTotal['deptid'] = ''
shopTotal['deptidname'] = ''
if (not d['qtyz']):
d['qtyz'] = 0
else:
d['qtyz'] = float(d['qtyz'])
shopTotal['qtyz'] += float(d['qtyz'])
if (not d['qtyl']):
d['qtyl'] = 0
else:
d['qtyl'] = float(d['qtyl'])
shopTotal['qtyl'] += float(d['qtyl'])
if (not d['zhonbi']):
d['zhonbi'] = '0.0%'
else:
d['zhonbi'] = str(float((d['zhonbi'] * 100))) + '%'
shopTotal['zhonbi'] = (shopTotal['qtyl'] / shopTotal['qtyz']) * 100
shopTotal['zhonbi'] = str(float(
'%0.2f' % shopTotal['zhonbi'])) + '%'
listShopTotal.append(shopTotal)
listShops.append(listShop)
# 门店排名汇总数据
TotalDict = {'listTotal': listTotal}
# 排序生成总排名
listTop = ranking(listTop, 'zhonbiSum', 'mingciSum')
# 排序生成每日排名
monthFirstReal = int(str(listDetail[0]['sdate'])[8:10])
for date in range(monthFirstReal, today.day + 1):
if (date < 10):
listTop = ranking(listTop, 'zhonbi_0' + str(date),
'mingci_0' + str(date))
else:
listTop = ranking(listTop, 'zhonbi_' + str(date),
'mingci_' + str(date))
# 按门店排序
listTop.sort(key=lambda x: x['ShopID'])
###课组汇总###
sqlDept = 'select deptid,deptidname,sum(qtyz) qtyz,sum(qtyl) qtyl,(sum(qtyl)/sum(qtyz)) zhonbi from Kzerostock ' \
'where sdate="{todayStr}" and deptid in ({rbacClass})' \
'group by deptid,deptidname order by deptid'\
.format(todayStr=todayStr,rbacClass=rbacClass)
cur.execute(sqlDept)
listDept = cur.fetchall()
for obj in listDept:
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
cur.close()
conn.close()
return locals()
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 昨天
# 获取部类编码
classcode = getclasscode()
# 获取所有商品的类别编码
allcode = getallcode()
# 获取门店编码
shopsid = getshopid()
# 查询某个部类下的子类编码
subcate = {}
# 将部类编码与子类编码组成dict
for x in classcode:
l = []
for y in allcode:
y = str(y)
if len(x) == 1 and y[:1] == x:
l.append(y)
if len(x) == 2 and y[:2] == x:
l.append(y)
subcate.setdefault(x, l)
subcate10 = subcate.get('10')
subcate11 = subcate.get('11')
subcate12 = subcate.get('12')
subcate13 = subcate.get('13')
subcate14 = subcate.get('14')
subcate15 = subcate.get('15')
subcate16 = subcate.get('16')
subcate17 = subcate.get('17')
subcate2 = subcate.get('2')
subcate3 = subcate.get('3')
subcate4 = subcate.get('4')
sqlsubcate10 = ','.join(subcate10)
sqlsubcate11 = ','.join(subcate11)
sqlsubcate12 = ','.join(subcate12)
sqlsubcate13 = ','.join(subcate13)
sqlsubcate14 = ','.join(subcate14)
sqlsubcate15 = ','.join(subcate15)
sqlsubcate16 = ','.join(subcate16)
sqlsubcate17 = ','.join(subcate17)
sqlsubcate2 = ','.join(subcate2)
sqlsubcate3 = ','.join(subcate3)
sqlsubcate4 = ','.join(subcate4)
sql10 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate10 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql11 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate11 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql12 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate12 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql13 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate13 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql14 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate14 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql15 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate15 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql16 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate16 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql17 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate17 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql2 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate2 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql3 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate3 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
sql4 = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in (" + sqlsubcate4 + ") " \
"and sdate='" + str(yesterday) + "' order by shopid, SaleValue desc"
# 连接数据库
conn = mtu.getMysqlConn()
cur10 = conn.cursor()
cur11 = conn.cursor()
cur12 = conn.cursor()
cur13 = conn.cursor()
cur14 = conn.cursor()
cur15 = conn.cursor()
cur16 = conn.cursor()
cur17 = conn.cursor()
cur2 = conn.cursor()
cur3 = conn.cursor()
cur4 = conn.cursor()
cur10.execute(sql10)
cur11.execute(sql11)
cur12.execute(sql12)
cur13.execute(sql13)
cur14.execute(sql14)
cur15.execute(sql15)
cur16.execute(sql16)
cur17.execute(sql17)
cur2.execute(sql2)
cur3.execute(sql3)
cur4.execute(sql4)
# 获取各部类下的销售数据
rows10 = cur10.fetchall()
rows11 = cur11.fetchall()
rows12 = cur12.fetchall()
rows13 = cur13.fetchall()
rows14 = cur14.fetchall()
rows15 = cur15.fetchall()
rows16 = cur16.fetchall()
rows17 = cur17.fetchall()
rows2 = cur2.fetchall()
rows3 = cur3.fetchall()
rows4 = cur4.fetchall()
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows10)):
for key in rows10[i].keys():
row = rows10[i][key]
if row is None:
rows10[i][key] = ''
else:
if isinstance(row, int):
rows10[i][key] = str(rows10[i][key])
elif isinstance(row, decimal.Decimal):
rows10[i][key] = "%0.2f" % float(rows10[i][key])
# 10 熟食部类
lis10 = []
unit10 = []
for sid in shopsid:
i = 0
for row in rows10:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis10.append(row)
i += 1
else:
continue
unit10.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows11)):
for key in rows11[i].keys():
row = rows11[i][key]
if row is None:
rows11[i][key] = ''
else:
if isinstance(row, int):
rows11[i][key] = str(rows11[i][key])
elif isinstance(row, decimal.Decimal):
rows11[i][key] = "%0.2f" % float(rows11[i][key])
# 11 水产部类
lis11 = []
unit11 = []
for sid in shopsid:
i = 0
for row in rows11:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis11.append(row)
i += 1
else:
continue
unit11.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows12)):
for key in rows12[i].keys():
row = rows12[i][key]
if row is None:
rows12[i][key] = ''
else:
if isinstance(row, int):
rows12[i][key] = str(rows12[i][key])
elif isinstance(row, decimal.Decimal):
rows12[i][key] = "%0.2f" % float(rows12[i][key])
# 12 蔬菜部类
lis12 = []
unit12 = []
for sid in shopsid:
i = 0
for row in rows12:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis12.append(row)
i += 1
else:
continue
unit12.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows13)):
for key in rows13[i].keys():
row = rows13[i][key]
if row is None:
rows13[i][key] = ''
else:
if isinstance(row, int):
rows13[i][key] = str(rows13[i][key])
elif isinstance(row, decimal.Decimal):
rows13[i][key] = "%0.2f" % float(rows13[i][key])
# 13 烘烤部类
lis13 = []
unit13 = []
for sid in shopsid:
i = 0
for row in rows13:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis13.append(row)
i += 1
else:
continue
unit13.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows14)):
for key in rows14[i].keys():
row = rows14[i][key]
if row is None:
rows14[i][key] = ''
else:
if isinstance(row, int):
rows14[i][key] = str(rows14[i][key])
elif isinstance(row, decimal.Decimal):
rows14[i][key] = "%0.2f" % float(rows14[i][key])
# 14 鲜肉部类
lis14 = []
unit14 = []
for sid in shopsid:
i = 0
for row in rows14:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis14.append(row)
i += 1
else:
continue
unit14.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows15)):
for key in rows15[i].keys():
row = rows15[i][key]
if row is None:
rows15[i][key] = ''
else:
if isinstance(row, int):
rows15[i][key] = str(rows15[i][key])
elif isinstance(row, decimal.Decimal):
rows15[i][key] = "%0.2f" % float(rows15[i][key])
# 15 干鲜干果部类
lis15 = []
unit15 = []
for sid in shopsid:
i = 0
for row in rows15:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis15.append(row)
i += 1
else:
continue
unit15.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows16)):
for key in rows16[i].keys():
row = rows16[i][key]
if row is None:
rows16[i][key] = ''
else:
if isinstance(row, int):
rows16[i][key] = str(rows16[i][key])
elif isinstance(row, decimal.Decimal):
rows16[i][key] = "%0.2f" % float(rows16[i][key])
# 16 主食厨房部类
lis16 = []
unit16 = []
for sid in shopsid:
i = 0
for row in rows16:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis16.append(row)
i += 1
else:
continue
unit16.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows17)):
for key in rows17[i].keys():
row = rows17[i][key]
if row is None:
rows17[i][key] = ''
else:
if isinstance(row, int):
rows17[i][key] = str(rows17[i][key])
elif isinstance(row, decimal.Decimal):
rows17[i][key] = "%0.2f" % float(rows17[i][key])
# 17 水果部类
lis17 = []
unit17 = []
for sid in shopsid:
i = 0
for row in rows17:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis17.append(row)
i += 1
else:
continue
unit17.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows2)):
for key in rows2[i].keys():
row = rows2[i][key]
if row is None:
rows2[i][key] = ''
else:
if isinstance(row, int):
rows2[i][key] = str(rows2[i][key])
elif isinstance(row, decimal.Decimal):
rows2[i][key] = "%0.2f" % float(rows2[i][key])
# 2 食品部类
lis2 = []
unit2 = []
for sid in shopsid:
i = 0
for row in rows2:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis2.append(row)
i += 1
else:
continue
unit2.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows3)):
for key in rows3[i].keys():
row = rows3[i][key]
if row is None:
rows3[i][key] = ''
else:
if isinstance(row, int):
rows3[i][key] = str(rows3[i][key])
elif isinstance(row, decimal.Decimal):
rows3[i][key] = "%0.2f" % float(rows3[i][key])
# 3 用品部类
lis3 = []
unit3 = []
for sid in shopsid:
i = 0
for row in rows3:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis3.append(row)
i += 1
else:
continue
unit3.append(i)
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for x in range(0, len(rows4)):
for key in rows4[x].keys():
row = rows4[x][key]
if row is None:
rows4[x][key] = ''
else:
if isinstance(row, int):
rows4[x][key] = str(rows4[x][key])
elif isinstance(row, decimal.Decimal):
rows4[x][key] = "%0.2f" % float(rows4[x][key])
# 将退货数据过滤，值为负
rows4filter = []
for i in range(0, len(rows4)):
if float(rows4[i]['SaleQty']) < 0:
continue
else:
rows4filter.append(rows4[i])
# 4 家电部类
lis4 = []
templist = []
for sid in shopsid:
i = 0
for row in rows4filter:
if sid['ShopID'] == row['shopid'] and i < 20:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis4.append(row)
i += 1
else:
continue
templist.append(i)
# 关闭数据库
mtu.close(conn, cur10)
mtu.close(conn, cur11)
mtu.close(conn, cur12)
mtu.close(conn, cur13)
mtu.close(conn, cur14)
mtu.close(conn, cur15)
mtu.close(conn, cur16)
mtu.close(conn, cur17)
mtu.close(conn, cur2)
mtu.close(conn, cur3)
mtu.close(conn, cur4)
return locals()
def query():
###门店排名###
monthFirst = datetime.date.today().replace(day=1)
today = datetime.datetime.today()
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month - 1, 1)
today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(1)
todayStr = today.strftime('%y-%m-%d')
# 获取当月天数
dayNum = todayStr[-2:]
monthFirstStr = str(monthFirst)
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 各门店报表数据
listShops = []
listShopTotal = []
# 月份汇总数据
sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
'FROM Kzerostock ' \
'WHERE ShopID!="C009" AND sdate BETWEEN "' + monthFirstStr + '" AND "' + todayStr + '" GROUP BY ShopID ORDER BY ShopID'
cur.execute(sqlTop)
listTop = cur.fetchall()
# 门店排名合计
listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0} # 初始化最后一行
# 转换数据类型并求纵向合计
for i in range(0, len(listTop)):
if (not listTop[i]['qtyzSum']):
listTop[i]['qtyzSum'] = 0
else:
listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
listTop[i]['qtyzSum'] = round((listTop[i]['qtyzSum']) / int(today.day))
if 'qtyzSum' in listTotal:
listTotal['qtyzSum'] += listTop[i]['qtyzSum']
else:
listTotal['qtyzSum'] = listTop[i]['qtyzSum']
if (not listTop[i]['qtylSum']):
listTop[i]['qtylSum'] = 0
else:
listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
listTop[i]['qtylSum'] = round((listTop[i]['qtylSum']) / int(today.day))
if 'qtylSum' in listTotal:
listTotal['qtylSum'] += listTop[i]['qtylSum']
else:
listTotal['qtylSum'] = listTop[i]['qtylSum']
if (not listTop[i]['zhonbiSum']):
listTop[i]['zhonbiSum'] = 0
else:
listTop[i]['zhonbiSum'] = float('%0.2f' % (listTop[i]['zhonbiSum'] * 100))
# 有效商品数，有效商品数合计 除以天数 四舍五入取整
listTotal['qtyzSum'] = round(float('%0.2f'%(listTotal['qtyzSum'] / int(dayNum))))
listTotal['qtylSum'] = round(float('%0.2f'%(listTotal['qtyzSum'] / int(dayNum))))
listTotal['zhonbiSum'] = listTotal['qtylSum']/listTotal['qtyzSum']
listTotal['zhonbiSum'] = str(float('%0.2f'%(listTotal['zhonbiSum']*100)))+'%'
listTotal['mingciSum'] = ''
sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM Kzerostock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
"FROM Kzerostock AS b " \
"WHERE ShopID ='" + listTop[i]['ShopID'] +"' AND sdate BETWEEN '"+monthFirstStr+"' AND '"+todayStr+"' GROUP BY sdate"
cur.execute(sql)
listDetail = cur.fetchall()
for item in listDetail:
date = str(item['sdate'])[8:10]
if (not item['qtyz']):
listTop[i]['qtyz_' + date] = 0
else:
listTop[i]['qtyz_' + date] = float(item['qtyz'])
if 'qtyz_' + date in listTotal:
listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
else:
listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]
if (not item['qtyl']):
listTop[i]['qtyl_' + date] = 0
else:
listTop[i]['qtyl_' + date] = float(item['qtyl'])
if 'qtyl_' + date in listTotal:
listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
else:
listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]
if (not item['zhonbi']):
listTop[i]['zhonbi_' + date] = 0
else:
listTop[i]['zhonbi_' + date] = float('%0.2f' % (item['zhonbi'] * 100))
listTotal['zhonbi_' + date] = listTotal['qtyl_' + date] / listTotal['qtyz_' + date]
listTotal['zhonbi_' + date] = str(float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'
listTotal['mingci_' + date] = ''
# 各个门店的sheet数据
sqlShop = "SELECT ShopID,shopname,deptid,deptidname,qtyz,qtyl,zhonbi " \
"FROM Kzerostock " \
"WHERE ShopID ='" + listTop[i]['ShopID'] + "' AND sdate = '" + todayStr + "'"
cur.execute(sqlShop)
listShop = cur.fetchall()
# 门店合计、转换数据格式
shopTotal = {'qtyz': 0, 'qtyl': 0}
for d in listShop:
shopTotal['ShopID'] = '合计'
shopTotal['shopname'] = ''
shopTotal['deptid'] = ''
shopTotal['deptidname'] = ''
if (not d['qtyz']):
d['qtyz'] = 0
else:
d['qtyz'] = float(d['qtyz'])
shopTotal['qtyz'] += float(d['qtyz'])
if (not d['qtyl']):
d['qtyl'] = 0
else:
d['qtyl'] = float(d['qtyl'])
shopTotal['qtyl'] += float(d['qtyl'])
if (not d['zhonbi']):
d['zhonbi'] = '0.0%'
else:
d['zhonbi'] = str(float((d['zhonbi'] * 100))) + '%'
shopTotal['zhonbi'] = (shopTotal['qtyl'] / shopTotal['qtyz']) * 100
shopTotal['zhonbi'] = str(float('%0.2f' % shopTotal['zhonbi'])) + '%'
listShopTotal.append(shopTotal)
listShops.append(listShop)
# 门店排名汇总数据
TotalDict = {'listTotal': listTotal}
# 排序生成总排名
listTop = ranking(listTop, 'zhonbiSum', 'mingciSum')
# 排序生成每日排名
monthFirstReal = int(str(listDetail[0]['sdate'])[8:10])
for date in range(monthFirstReal, today.day+1):
if (date < 10):
listTop = ranking(listTop, 'zhonbi_0' + str(date), 'mingci_0' + str(date))
else:
listTop = ranking(listTop, 'zhonbi_' + str(date), 'mingci_' + str(date))
# 按门店排序
listTop.sort(key=lambda x: x['ShopID'])
###课组汇总###
sqlDept = 'select deptid,deptidname,sum(qtyz) qtyz,sum(qtyl) qtyl,(sum(qtyl)/sum(qtyz)) zhonbi from Kzerostock' \
' where ShopID!="C009" AND sdate="' + todayStr + '" group by deptid,deptidname order by deptid'
cur.execute(sqlDept)
listDept = cur.fetchall()
for obj in listDept:
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
cur.close()
conn.close()
return locals()
def query(yesterday):
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(shopsale) as shopsalesum, sum(ret) as retsum, (sum(ret) / sum(shopsale)) as retrate " \
"from `KGshopretsale` " \
"where ShopID!='C009' AND sdate between '" + monfirstday + "' and '" + yesterday + "' " \
"group by shopid " \
"order by shopid"
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {'sequenceNumber': '合计', 'shopid': '', 'shopname': '', 'shopsalesum': '', 'retsum': '',
'retrate': ''}
# 汇总总计（每月1日到当日）
tempshopsalesum = 0.00
tempretsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(abs(float("%0.2f" % float(listtop[i][key] * 100)))) + '%'
elif isinstance(row, decimal.Decimal) and key == 'retsum':
listtop[i][key] = abs(float("%0.2f" % float(listtop[i][key])))
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加汇总合计
tempshopsalesum += float(listtop[i]['shopsalesum'])
tempretsum += float(listtop[i]['retsum'])
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
sql = "select sdate, shopid, shopsale, ret, ret / shopsale as retc " \
"from `KGshopretsale` " \
"where shopid='" + listtop[i]['shopid'] + "' " \
"and sdate between '" + monfirstday + "' and '" + yesterday + "'"
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['ret']):
listtop[i]['retsum_' + date] = 0
else:
listtop[i]['retsum_' + date] = abs(float(item['ret']))
if 'retsum_' + date in listtopTotal:
listtopTotal['retsum_' + date] += listtop[i]['retsum_' + date]
else:
listtopTotal['retsum_' + date] = listtop[i]['retsum_' + date]
if (not item['shopsale']):
listtop[i]['shopsalesum_' + date] = 0
else:
listtop[i]['shopsalesum_' + date] = float(item['shopsale'])
if 'shopsalesum_' + date in listtopTotal:
listtopTotal['shopsalesum_' + date] += listtop[i]['shopsalesum_' + date]
else:
listtopTotal['shopsalesum_' + date] = listtop[i]['shopsalesum_' + date]
if (not item['retc']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' + date] = str(abs(float("%0.2f" % (item['retc'] * 100)))) + '%'
# 添加当日汇总
listtopTotal['retsum_' + date] = float("%0.2f" % (listtopTotal['retsum_' + date]))
listtopTotal['shopsalesum_' + date] = float("%0.2f" % (listtopTotal['shopsalesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" % (listtopTotal['retsum_' + date] / listtopTotal['shopsalesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['shopsalesum'] = float("%0.2f" % tempshopsalesum)
listtopTotal['retsum'] = float("%0.2f" % tempretsum)
listtopTotal['retrate'] = str(
"%0.2f" % float(float(listtopTotal['retsum'] / listtopTotal['shopsalesum']) * 100)) + '%'
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
# 退货明细
sqldetail = "select shopid, shopname, sdate, stime, listno, posid, cashierid, goodsid, goodsname, deptid, amount, sale " \
"from `KGshopretsaleitem` " \
"where sdate='" + yesterday + "' order by shopid"
cur = conn.cursor()
cur.execute(sqldetail)
retdetail = cur.fetchall()
# 格式化数据
for i in range(0, len(retdetail)):
for key in retdetail[i].keys():
row = retdetail[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal):
retdetail[i][key] = "%0.2f" % float(retdetail[i][key])
elif isinstance(row, datetime.datetime):
retdetail[i][key] = retdetail[i][key].strftime("%Y-%m-%d")
else:
retdetail[i][key] = retdetail[i][key]
mtu.close(conn, cur)
return locals()
def query(date,start,end,yesterday,lastDay):
# 查询所有超市门店
slist = BasShopRegion.objects.values("shopid", "shopname") \
.filter(shoptype=11).exclude(shopid='C009').order_by("shopid")
shopids = "','".join([shop["shopid"] for shop in slist])
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 查询全月预算
ysql = "SELECT dateid saledate,groupid,shopid shopcode,SUM(salevalue)/10000 sale,SUM(salegain)/10000 gain FROM Estimate " \
" WHERE dateid BETWEEN '" + start + " 00:00:00' AND '" + end + " 23:59:59.999' AND deptlevelid=2 AND shopid IN ('" + shopids + "') " \
"and groupid < 50 and groupid <> 42 GROUP BY dateid,shopid,groupid ORDER BY shopid,groupid,dateid "
cur.execute(ysql)
ylist = cur.fetchall()
yshopdict, ygrpdict, ys_grplist = queryData(ylist, lastDay, date.day)
# 查询全月销售实际
sale_sql = "SELECT sdate saledate,shopcode,LEFT(sccode,2) groupid,SUM(svalue-discount)/10000 sale,SUM(svalue-discount-scost)/10000 gain " \
" FROM sales_pro WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59.999' AND shopcode IN ('" + shopids + "') " \
"and LEFT(sccode,2) < 50 and LEFT(sccode,2) <> 42 GROUP BY shopcode,LEFT(sccode,2),DATE_FORMAT(sdate,'%Y-%m-%d') "
cur.execute(sale_sql)
sale_list = cur.fetchall()
sshopdict, sgrpdict, ss_grplist = queryData(sale_list, lastDay, date.day)
# 查询批发销售单
pf_sale_sql = "SELECT sdate saledate,shopid shopcode,LEFT(deptid,2) groupid,SUM(salevalue)/10000 sale,SUM(salevalue-costvalue)/10000 gain " \
" FROM kwholesale WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59.999' " \
"and LEFT(deptid,2) < 50 and LEFT(deptid,2) <> 42 GROUP BY DATE_FORMAT(sdate,'%Y-%m-%d'),shopid,LEFT(deptid,2) " \
"ORDER BY shopid,LEFT(deptid,2),DATE_FORMAT(sdate,'%Y-%m-%d') "
cur.execute(pf_sale_sql)
pf_sale_list = cur.fetchall()
pfshopdict, pfgrpdict, pfs_grplist = queryData(pf_sale_list, lastDay, date.day)
# 计算实际销售
# 门店
shop_saledict, shop_zbdict, shop_sumlist = countSale(yshopdict, sshopdict, pfshopdict, lastDay)
# 课组
group_saledict, group_zbdict, group_sumlist = countSale(ygrpdict, sgrpdict, pfgrpdict, lastDay)
rslist = []
grslist = []
# 计算合计占比
countSumZb(shop_sumlist)
countSumZb(group_sumlist)
# 合并list
rslist.extend(shop_sumlist)
grslist.extend(group_sumlist)
mergeData(rslist, slist, yshopdict, shop_saledict, shop_zbdict, lastDay)
mergeGroupData(grslist, ygrpdict, group_saledict, group_zbdict, lastDay)
formate_data(rslist)
formate_data(grslist)
# 各个门店的课组每日明细
srslist = []
for row in slist:
temp_rslist = []
sid = row["shopid"]
if sid in ys_grplist:
yshopdata = ys_grplist[sid]
else:
yshopdata = {}
if sid in ss_grplist:
sshopdata = ss_grplist[sid]
else:
sshopdata = {}
if sid in pfs_grplist:
pfshopdata = pfs_grplist[sid]
else:
pfshopdata = {}
shop_grp_saledict, shop_grp_zbdict, shop_grp_sumlist = countSale(yshopdata, sshopdata, pfshopdata, lastDay)
countSumZb(shop_grp_sumlist)
temp_rslist.extend(shop_grp_sumlist)
mergeGroupData(temp_rslist, yshopdata, shop_grp_saledict, shop_grp_zbdict, lastDay)
formate_data(temp_rslist)
srslist.append(temp_rslist)
shoplist = []
for row in slist:
item = {}
item.setdefault("shopid", row["shopid"])
item.setdefault("shopname", row["shopname"].strip())
shoplist.append(item)
data = {"rlist": rslist, "shoplist": shoplist, "grslist": grslist, "srslist": srslist}
return data
def initDepartData(id, yesterday):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
# 连接数据库
conn = mtu.getMysqlConn()
# 获取部类编码
classcode = getclasscode()
# 获取所有商品的类别编码
allcode = getallcode()
# 查询某个部类下的子类编码
subcate = {}
# 将部类编码与子类编码组成dict
for x in classcode:
l = []
for y in allcode:
y = str(y)
if len(x) == 1 and y[:1] == x:
l.append(y)
if len(x) == 2 and y[:2] == x:
l.append(y)
subcate.setdefault(x, l)
subcate = subcate.get(id)
sqlsubcate = ','.join(subcate)
sql = "select shopid, goodsid, goodsname, SaleQty, SaleValue, SaleCost, gpvalue, gprate, qty, costvalue, cprice, price " \
"from `kwsaletop10` " \
"where deptid in ({sqlsubcate}) and sdate='{yesterday}' and shopid in ({rbacDepart}) " \
"order by shopid, SaleValue desc" \
.format(sqlsubcate=sqlsubcate, yesterday=str(yesterday), rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
# 判断当天是否有数据，同时转换数据类型 int 转 string, decimal 转 float
for i in range(0, len(rows)):
for key in rows[i].keys():
row = rows[i][key]
if row is None:
rows[i][key] = ''
else:
if isinstance(row, int):
rows[i][key] = str(rows[i][key])
elif isinstance(row, decimal.Decimal):
rows[i][key] = "%0.2f" % float(rows[i][key])
# 将退货数据过滤，值为负
rowsfilter = []
for i in range(0, len(rows)):
if float(rows[i]['SaleQty']) < 0:
continue
else:
rowsfilter.append(rows[i])
lis = []
unit = []
# 获取门店编码
shopsid = getshopid()
for sid in shopsid:
i = 0
for row in rows:
if sid['ShopID'] == row['shopid'] and i < 10:
row['shopid'] = sid['ShopName'].strip() + sid['ShopID']
row['paiming'] = i + 1
lis.append(row)
i += 1
else:
continue
unit.append(i)
return lis, unit
def query(date):
yesterday = date.strftime("%Y-%m-%d")
# 查询当月销售
try:
sql = "CALL k_d_wholesale ('{start}','{end}') ".format(start=yesterday, end=yesterday)
conn = mtu.getMysqlConn()
cur = conn.cursor()
cur.execute(sql)
list = cur.fetchall()
rlist = []
sumDict = {}
sum = {"shopid": "合计", "shopnm": "", "tradeprice": 0.0, "tradenumber": 0, "salevalue": 0.0, "discvalue": 0.0,
"sale": 0.0,
"costvalue": 0.0, "salegain": 0.0, "gaintx": "", "yhzhanbi": "", "wsalevalue": 0.0, "wcostvalue": 0.0,
"wsalegain": 0.0, "wgaintx": ""}
sumDict.setdefault("sum1", sum)
unsumkey = ["gaintx", "wgaintx"]
for obj in list:
if obj['shopid'] != 'C009':
row = {}
for key in obj.keys():
item = obj[key]
newkey = key.lower()
if item:
if isinstance(item, int) or isinstance(item, decimal.Decimal):
if newkey not in unsumkey:
row.setdefault(newkey, float(item))
sum[newkey] += float(item)
else:
row.setdefault(newkey, "%0.2f" % item + "%")
elif isinstance(item, datetime.datetime):
row.setdefault(newkey, item.strftime("%Y-%m-%d"))
else:
row.setdefault(newkey, item)
else:
row.setdefault(newkey, "")
if row["sale"] > 0:
yhzhanbi = "%0.2f" % (row["discvalue"] * 100.0 / row["sale"]) + "%"
else:
yhzhanbi = ""
row.setdefault("yhzhanbi", yhzhanbi)
rlist.append(row)
if sum["sale"] > 0:
sum["gaintx"] = "%0.2f" % (sum["salegain"] * 100.0 / sum["sale"]) + "%"
sum["yhzhanbi"] = "%0.2f" % (sum["discvalue"] * 100.0 / sum["sale"]) + "%"
else:
sum["gaintx"] = ""
sum["yhzhanbi"] = ""
if sum["wsalevalue"] > 0:
sum["wgaintx"] = "%0.2f" % (sum["wsalegain"] * 100.0 / sum["wsalevalue"]) + "%"
else:
sum["wgaintx"] = ""
for key in sum.keys():
item = sum[key]
if not isinstance(item, str) and not isinstance(item, int):
sum[key] = "%0.2f" % item
except Exception as e:
print(">>>>>>>>>>>>[异常]", e)
# 计算月累加合计
data = {"gslist":rlist,"sumDict":sumDict}
return data
def query(date, start, end, yesterday, lastDay):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
rbacClassList, rbacClass = reportMth.getRbacClass()
# 查询所有超市门店
slist = BasShopRegion.objects.values("shopid", "shopname") \
.filter(shopid__in=rbacDepartList,shoptype=11).order_by("shopid")
shopids = "','".join([shop["shopid"] for shop in slist])
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 查询全月预算 groupid
ysql = "SELECT dateid saledate,groupid,shopid shopcode,SUM(salevalue)/10000 sale,SUM(salegain)/10000 gain FROM Estimate " \
" WHERE dateid BETWEEN '" + start + " 00:00:00' AND '" + end + " 23:59:59.999' AND deptlevelid=2 AND shopid IN ('" + shopids + "') " \
"and shopid in ({rbacDepart}) and groupid in ({rbacClass}) " \
"GROUP BY dateid,shopid,groupid ORDER BY shopid,groupid,dateid ".format(rbacDepart=rbacDepart,rbacClass=rbacClass)
cur.execute(ysql)
ylist = cur.fetchall()
yshopdict, ygrpdict, ys_grplist = queryData(ylist, lastDay, date.day)
# 查询全月销售实际 groupid
sale_sql = "SELECT * FROM (SELECT sdate saledate,shopcode,LEFT(sccode,2) groupid,SUM(svalue-discount)/10000 sale,SUM(svalue-discount-scost)/10000 gain " \
" FROM sales_pro WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59' AND shopcode IN ('" + shopids + "') " \
"and shopcode in ({rbacDepart}) " \
"GROUP BY shopcode,LEFT(sccode,2),DATE_FORMAT(sdate,'%Y-%m-%d')) AS a WHERE a.groupid IN ({rbacClass})".format(rbacDepart=rbacDepart,rbacClass=rbacClass)
cur.execute(sale_sql)
sale_list = cur.fetchall()
sshopdict, sgrpdict, ss_grplist = queryData(sale_list, lastDay, date.day)
# 查询批发销售单 groupid
pf_sale_sql = "SELECT * FROM (SELECT sdate saledate,shopid shopcode,LEFT(deptid,2) groupid,SUM(salevalue)/10000 sale,SUM(salevalue-costvalue)/10000 gain " \
" FROM kwholesale WHERE sdate BETWEEN '" + start + " 00:00:00' AND '" + yesterday + " 23:59:59.999' " \
"and shopid in ({rbacDepart}) " \
"GROUP BY DATE_FORMAT(sdate,'%Y-%m-%d'),shopid,LEFT(deptid,2) " \
"ORDER BY shopid,LEFT(deptid,2),DATE_FORMAT(sdate,'%Y-%m-%d')) AS a WHERE a.groupid IN ({rbacClass}) ".format(rbacDepart=rbacDepart,rbacClass=rbacClass)
cur.execute(pf_sale_sql)
pf_sale_list = cur.fetchall()
pfshopdict, pfgrpdict, pfs_grplist = queryData(pf_sale_list, lastDay,
date.day)
# 计算实际销售
# 门店
shop_saledict, shop_zbdict, shop_sumlist = countSale(
yshopdict, sshopdict, pfshopdict, lastDay)
# 课组
group_saledict, group_zbdict, group_sumlist = countSale(
ygrpdict, sgrpdict, pfgrpdict, lastDay)
rslist = []
grslist = []
# 计算合计占比
countSumZb(shop_sumlist)
countSumZb(group_sumlist)
# 合并list
rslist.extend(shop_sumlist)
grslist.extend(group_sumlist)
mergeData(rslist, slist, yshopdict, shop_saledict, shop_zbdict, lastDay)
mergeGroupData(grslist, ygrpdict, group_saledict, group_zbdict, lastDay)
formate_data(rslist)
formate_data(grslist)
# 各个门店的课组每日明细
srslist = []
for row in slist:
temp_rslist = []
sid = row["shopid"]
if sid in ys_grplist:
yshopdata = ys_grplist[sid]
else:
yshopdata = {}
if sid in ss_grplist:
sshopdata = ss_grplist[sid]
else:
sshopdata = {}
if sid in pfs_grplist:
pfshopdata = pfs_grplist[sid]
else:
pfshopdata = {}
shop_grp_saledict, shop_grp_zbdict, shop_grp_sumlist = countSale(
yshopdata, sshopdata, pfshopdata, lastDay)
countSumZb(shop_grp_sumlist)
temp_rslist.extend(shop_grp_sumlist)
mergeGroupData(temp_rslist, yshopdata, shop_grp_saledict,
shop_grp_zbdict, lastDay)
formate_data(temp_rslist)
srslist.append(temp_rslist)
shoplist = []
for row in slist:
item = {}
item.setdefault("shopid", row["shopid"])
item.setdefault("shopname", row["shopname"].strip())
shoplist.append(item)
data = {
"rlist": rslist,
"shoplist": shoplist,
"grslist": grslist,
"srslist": srslist
}
return data
def balanceArticle(request):
grpCode = request.session.get('s_grpcode') #用户所属单位
s_suppname = request.session.get('s_suppname') #用户所属单
grpName = Constants.SCM_UNIT[grpCode]
contracttype = request.session.get("s_contracttype") #经营方式
paytypeid = str(int(request.session.get("s_paytypeid"))) #经营方式
sheetId = request.GET.get('sheetid','')
queryAction = request.POST.get('actionTxt','')
#更新确认状态
if queryAction == 'check':
balanceObj = Billhead0.objects.get(sheetid__contains=sheetId,grpcode=grpCode)
balanceObj.status='Y'
balanceObj.save()
#结算通知单汇总 ,beginsdate,endsdate
balance = Billhead0.objects.values("shopid","venderid","vendername","sheetid","paytype","begindate","enddate"
,"editdate","curdxvalue","payablemoney","kxinvoice","kxmoney","kxcash",
"premoney","editor","checker","paychecker","contracttype","beginsdate","endsdate","advance")\
.get(sheetid__contains=sheetId)
#结算通知明细
balanceItems = Billheaditem0.objects.values("inshopid","refsheettype","refsheetid","managedeptid","payabledate",
"costvalue","costtaxvalue","costtaxrate","salevalue","dkrate","invoicesheetid")\
.filter(sheetid__contains=sheetId).exclude(costvalue=0)\
.order_by("inshopid","refsheettype","refsheetid")
itemList = []
itemShopId = None
for item in balanceItems:
shopid = item["inshopid"]
if itemShopId != shopid:
vlist = []
itemList.append([shopid,vlist])
vlist.append(item)
itemShopId = item["inshopid"]
itemList = sorted(itemList)
#本期帐扣发票金额
if balance.get('kxinvoice'):
cfpkx = balance.get('kxinvoice')
else:
cfpkx = decimal.Decimal(0.0)
#帐扣扣项 帐扣金额
zkkx = balance.get('kxmoney')-balance.get('kxcash')
#本期应付金额
if balance.get('curdxvalue'):
curdxValue = balance.get('curdxvalue')
else:
curdxValue = decimal.Decimal(0.0)
if balance.get('payablemoney'):
payableMoney = balance.get('payablemoney')
else:
payableMoney= decimal.Decimal(0.0)
#预付款余额
if balance.get('premoney'):
premoney = balance.get('premoney')
else:
premoney = decimal.Decimal(0.0)
if curdxValue == 0:
invoicePay =payableMoney-cfpkx#应开票金额
realPay = payableMoney-zkkx-premoney#实付金额
else:
invoicePay = curdxValue-cfpkx
realPay = curdxValue-zkkx-premoney
#实付金额大写
realPayUpper = mtu.rmbupper(float(round(realPay,2)))
#打印日期
printDate = datetime.date.today()
ssum1 = decimal.Decimal(0.0)
ssum2 = decimal.Decimal(0.0)
ssum3 = decimal.Decimal(0.0)
try:
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = """select InShopID,sum(CostValue) AS CostValue,SUM(CostTaxValue) AS CostTaxValue,sum(salevalue) AS SaleValue
from billheaditem0 where SheetID ='{sheetId}' group by InShopID""".format(sheetId=sheetId)
cur.execute(sql)
slist = cur.fetchall()
sdict = {}
for row in slist:
ssum1 += row["CostValue"]
ssum2 += (row["CostValue"]-row["CostTaxValue"])
ssum3 += row["SaleValue"]
sdict[row["InShopID"]] = [ row["CostValue"],(row["CostValue"]-row["CostTaxValue"]),row["SaleValue"]]
except Exception as e:
print(e)
#应结金额总额
totalCostValue = decimal.Decimal(0.0)
#税金总额
totalCostTax = decimal.Decimal(0.0)
mdept = []
for item in balanceItems:
totalCostValue += item.get('costvalue',0)
totalCostTax += item.get('costtaxvalue',0)
item['managedeptid'] = str(item.get('managedeptid',0))
mdept.append(item["managedeptid"])
#查询管理部类：多个用逗号分隔
mdept = list(set(mdept))
orgList = BasOrg.objects.all().values("orgname", "orgcode")
mdeptNames = [x["orgname"] for x in orgList if x["orgcode"] in mdept]
mdeptName = ",".join(mdeptNames)
#查询帐扣明细
kxlist = findKxListBySheetId(sheetId)
kxsum = decimal.Decimal(0.0)
for item in kxlist:
kxsum += item["kmoney"]
jxList = findJxListBySheetId(sheetId)
jxsum = decimal.Decimal(0.0)
for item2 in jxList:
jxsum += item2["kmoney"]
return render(request,'user_settle_article_{ctype}.html'.format(ctype=contracttype),locals())
def balanceArticle(request):
grpCode = request.session.get('s_grpcode') #用户所属单位
s_suppname = request.session.get('s_suppname') #用户所属单
grpName = Constants.SCM_UNIT[grpCode]
contracttype = request.session.get("s_contracttype") #经营方式
paytypeid = str(int(request.session.get("s_paytypeid"))) #经营方式
sheetId = request.GET.get('sheetid','')
queryAction = request.POST.get('actionTxt','')
#更新确认状态
if queryAction == 'check':
balanceObj = Billhead0.objects.get(sheetid__contains=sheetId,grpcode=grpCode)
balanceObj.status='Y'
balanceObj.save()
#结算通知单汇总 ,beginsdate,endsdate
balance = Billhead0.objects.values("shopid","venderid","vendername","sheetid","paytype","begindate","enddate"
,"editdate","curdxvalue","payablemoney","kxinvoice","kxmoney","kxcash",
"premoney","editor","checker","paychecker","contracttype","beginsdate","endsdate","advance")\
.get(sheetid__contains=sheetId)
#结算通知明细
balanceItems = Billheaditem0.objects.values("inshopid","refsheettype","refsheetid","managedeptid","payabledate",
"costvalue","costtaxvalue","costtaxrate","salevalue","dkrate","invoicesheetid")\
.filter(sheetid__contains=sheetId).exclude(costvalue=0)\
.order_by("inshopid","refsheettype","refsheetid")
itemList = []
itemShopId = None
for item in balanceItems:
shopid = item["inshopid"]
if itemShopId != shopid:
vlist = []
itemList.append([shopid,vlist])
vlist.append(item)
itemShopId = item["inshopid"]
itemList = sorted(itemList)
#本期帐扣发票金额
if balance.get('kxinvoice'):
cfpkx = balance.get('kxinvoice')
else:
cfpkx = decimal.Decimal(0.0)
#帐扣扣项 帐扣金额
zkkx = balance.get('kxmoney')-balance.get('kxcash')
#本期应付金额
if balance.get('curdxvalue'):
curdxValue = balance.get('curdxvalue')
else:
curdxValue = decimal.Decimal(0.0)
if balance.get('payablemoney'):
payableMoney = balance.get('payablemoney')
else:
payableMoney= decimal.Decimal(0.0)
#预付款余额
if balance.get('premoney'):
premoney = balance.get('premoney')
else:
premoney = decimal.Decimal(0.0)
if curdxValue == 0:
invoicePay =payableMoney-cfpkx#应开票金额
realPay = payableMoney-zkkx-premoney#实付金额
else:
invoicePay = curdxValue-cfpkx
realPay = curdxValue-zkkx-premoney
#实付金额大写
realPayUpper = mtu.rmbupper(float(round(realPay,2)))
#打印日期
printDate = datetime.date.today()
ssum1 = decimal.Decimal(0.0)
ssum2 = decimal.Decimal(0.0)
ssum3 = decimal.Decimal(0.0)
try:
conn = mtu.getMysqlConn()
cur = conn.cursor()
sql = """select InShopID,sum(CostValue) AS CostValue,SUM(CostTaxValue) AS CostTaxValue,sum(salevalue) AS SaleValue
from billheaditem0 where SheetID ='{sheetId}' group by InShopID""".format(sheetId=sheetId)
cur.execute(sql)
slist = cur.fetchall()
sdict = {}
for row in slist:
ssum1 += row["CostValue"]
ssum2 += (row["CostValue"]-row["CostTaxValue"])
ssum3 += row["SaleValue"]
sdict[row["InShopID"]] = [ row["CostValue"],(row["CostValue"]-row["CostTaxValue"]),row["SaleValue"]]
except Exception as e:
print(e)
#应结金额总额
totalCostValue = decimal.Decimal(0.0)
#税金总额
totalCostTax = decimal.Decimal(0.0)
mdept = []
for item in balanceItems:
totalCostValue += item.get('costvalue',0)
totalCostTax += item.get('costtaxvalue',0)
item['managedeptid'] = str(item.get('managedeptid',0))
mdept.append(item["managedeptid"])
#查询管理部类：多个用逗号分隔
mdept = list(set(mdept))
orgList = BasOrg.objects.all().values("orgname", "orgcode")
mdeptNames = [x["orgname"] for x in orgList if x["orgcode"] in mdept]
mdeptName = ",".join(mdeptNames)
#查询帐扣明细
kxlist = findKxListBySheetId(sheetId)
kxsum = decimal.Decimal(0.0)
for item in kxlist:
kxsum += item["kmoney"]
jxList = findJxListBySheetId(sheetId)
jxsum = decimal.Decimal(0.0)
for item2 in jxList:
jxsum += item2["kmoney"]
return render(request,'user_settle_article_{ctype}.html'.format(ctype=contracttype),locals())
def query():
today = datetime.date.today()
monthFirst = datetime.date.today().replace(day=1)
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)
today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(days=1)
todayStr = today.strftime('%y-%m-%d')
monthFirstStr = str(monthFirst)
shopTop = []
shopTopTotal = {'shopid': '合计', 'shopname': ''}
conn = mtu.getMysqlConn()
cur = conn.cursor()
# 月累计报损
sqlMonthTotal = 'select shopid,shopname,sum(costvalue) costvalueSum,sum(lostvalue) lostvalueSum,(sum(lostvalue)/sum(costvalue)) lrateSum ' \
'from KGshop17lost ' \
'where ShopID!="C009" AND sdate between "' + monthFirstStr + '" and "' + todayStr + '" ' \
'group by shopid order by shopid '
cur.execute(sqlMonthTotal)
shopTop = cur.fetchall()
# 每日报损
sqlDaily = 'select sdate,shopid,costvalue,lostvalue,lrate ' \
'from KGshop17lost ' \
'where ShopID!="C009" AND sdate between "' + monthFirstStr + '" and "' + todayStr + '" ' \
'order by sdate '
cur.execute(sqlDaily)
listDaily = cur.fetchall()
# 计算纵向合计、格式化数据、拼接月累计报损和每日报损
for obj1 in shopTop:
obj1['lostvalueSum'] = float(obj1['lostvalueSum']) if obj1['lostvalueSum'] else 0
if 'lostvalueSum' in shopTopTotal:
shopTopTotal['lostvalueSum'] += obj1['lostvalueSum']
else:
shopTopTotal['lostvalueSum'] = obj1['lostvalueSum']
obj1['costvalueSum'] = float(obj1['costvalueSum']) if obj1['costvalueSum'] else 0
if 'costvalueSum' in shopTopTotal:
shopTopTotal['costvalueSum'] += obj1['costvalueSum']
else:
shopTopTotal['costvalueSum'] = obj1['costvalueSum']
obj1['lrateSum'] = str(float('%0.2f' % (obj1['lrateSum'] * 100))) + '%' if obj1['lrateSum'] else '0.00%'
shopTopTotal['lrateSum'] = shopTopTotal['lostvalueSum'] / shopTopTotal['costvalueSum']
shopTopTotal['lrateSum'] = str(float('%0.2f' % (shopTopTotal['lrateSum'] * 100))) + '%'
for obj2 in listDaily:
date = str(obj2['sdate'])[8:10]
if obj1['shopid'] == obj2['shopid']:
obj1['costvalue_' + date] = float('%0.2f' % obj2['costvalue']) if obj2['costvalue'] else 0
if 'costvalue_' + date in shopTopTotal:
shopTopTotal['costvalue_' + date] += obj1['costvalue_' + date]
shopTopTotal['costvalue_' + date] = float('%0.2f' % shopTopTotal['costvalue_' + date])
else:
shopTopTotal['costvalue_' + date] = obj1['costvalue_' + date]
obj1['lostvalue_' + date] = float('%0.2f' % obj2['lostvalue']) if obj2['lostvalue'] else 0
if 'lostvalue_' + date in shopTopTotal:
shopTopTotal['lostvalue_' + date] += obj1['lostvalue_' + date]
shopTopTotal['lostvalue_' + date] = float('%0.2f' % shopTopTotal['lostvalue_' + date])
else:
shopTopTotal['lostvalue_' + date] = obj1['lostvalue_' + date]
obj1['lrate_' + date] = str(float(obj2['lrate'] * 100)) + '%' if obj2['lrate'] else '0.00%'
shopTopTotal['lrate_' + date] = (shopTopTotal['lostvalue_' + date] / shopTopTotal['costvalue_' + date])
shopTopTotal['lrate_' + date] = str(float('%0.2f' % (shopTopTotal['lrate_' + date] * 100))) + '%'
TotalDict = {'shopTopTotal': shopTopTotal}
return locals()
def query(yesterday):
rbacDepartList, rbacDepart = reportMth.getRbacDepart(11)
yearandmon = DateUtil.getyearandmonth()
# 当前月份第一天
monfirstday = DateUtil.get_firstday_of_month(yearandmon[0], yearandmon[1])
# 当前月份最后一天
monlastday = DateUtil.get_lastday_month()
# 今天
today = DateUtil.todaystr()
# 获取门店信息
getshopname = getshopid()
conn = mtu.getMysqlConn()
sqltop = "select shopid, sum(costvalue) as costvaluesum, sum(reth) as rethsum, (sum(reth) / sum(costvalue)) as retrate " \
"from KGretshop " \
"where sdate between '{monfirstday}' and '{yesterday}' and shopid in ({rbacDepart})" \
"group by shopid order by shopid"\
.format(monfirstday=monfirstday,yesterday=yesterday,rbacDepart=rbacDepart)
cur = conn.cursor()
cur.execute(sqltop)
listtop = cur.fetchall()
# 最后一行的合计
listtopTotal = {
'sequenceNumber': '合计',
'shopid': '',
'shopname': '',
'costvaluesum': '',
'rethsum': '',
'retrate': ''
}
# 汇总总计（每月1日到当日）
tempcostvaluesum = 0.00
temprethsum = 0.00
# 格式化数据
paiming = 1
for i in range(0, len(listtop)):
for key in listtop[i].keys():
row = listtop[i][key]
if row == None:
listtop[i][key] = ''
else:
if isinstance(row, decimal.Decimal) and key == 'retrate':
listtop[i][key] = str(
float("%0.2f" % float(listtop[i][key] * 100))) + '%'
elif isinstance(row, decimal.Decimal):
listtop[i][key] = "%0.2f" % float(listtop[i][key])
else:
listtop[i][key] = listtop[i][key]
# 添加序号
listtop[i]['sequenceNumber'] = paiming
paiming += 1
# 添加汇总合计
tempcostvaluesum += float(listtop[i]['costvaluesum'])
temprethsum += float(listtop[i]['rethsum'])
sql = "select sdate, shopid, costvalue, reth, reth / costvalue as ret " \
"from `KGretshop` " \
"where shopid='{shopID}' and sdate between '{monfirstday}' and '{yesterday}'"\
.format(monfirstday=monfirstday,yesterday=yesterday,shopID = listtop[i]['shopid'])
cur = conn.cursor()
cur.execute(sql)
listdetail = cur.fetchall()
for item in listdetail:
date = str(item['sdate'])[8:10]
if (not item['reth']):
listtop[i]['rethsum_' + date] = 0
else:
listtop[i]['rethsum_' + date] = float(item['reth'])
if 'rethsum_' + date in listtopTotal:
listtopTotal['rethsum_' + date] += listtop[i]['rethsum_' +
date]
else:
listtopTotal['rethsum_' + date] = listtop[i]['rethsum_' + date]
if (not item['costvalue']):
listtop[i]['costvaluesum_' + date] = 0
else:
listtop[i]['costvaluesum_' + date] = float(item['costvalue'])
if 'costvaluesum_' + date in listtopTotal:
listtopTotal['costvaluesum_' +
date] += listtop[i]['costvaluesum_' + date]
else:
listtopTotal['costvaluesum_' +
date] = listtop[i]['costvaluesum_' + date]
if (not item['ret']):
listtop[i]['retrate_' + date] = 0
else:
listtop[i]['retrate_' +
date] = str(float("%0.2f" %
(item['ret'] * 100))) + '%'
# 添加当日汇总
listtopTotal['rethsum_' + date] = float(
"%0.2f" % (listtopTotal['rethsum_' + date]))
listtopTotal['costvaluesum_' + date] = float(
"%0.2f" % (listtopTotal['costvaluesum_' + date]))
listtopTotal['retrate_' + date] = str(
float("%0.2f" %
(listtopTotal['rethsum_' + date] /
listtopTotal['costvaluesum_' + date] * 100))) + '%'
# 添加门店名称
for i in range(0, len(listtop)):
for j in range(0, len(getshopname)):
if listtop[i]['shopid'] == getshopname[j]['ShopID']:
listtop[i]['shopname'] = getshopname[j]['ShopName'].strip()
# 合计转换数据格式
listtopTotal['costvaluesum'] = "%0.2f" % tempcostvaluesum
listtopTotal['rethsum'] = "%0.2f" % temprethsum
tempcandr = "%0.2f" % (temprethsum / tempcostvaluesum)
listtopTotal['retrate'] = str(
"%0.2f" % float(float(tempcandr) * 100)) + '%'
mtu.close(conn, cur)
# 转换为dict，导出excel
TotalDict = {'listtopTotal': listtopTotal}
return locals()
def reconcilType(request):
#复选框列表
conn2 = MethodUtil.getMysqlConn()
conn2.autocommit(True)
cur = conn2.cursor()
sqlPayList = "select id,name from bas_paytype order by name desc"
cur.execute(sqlPayList)
PayList = cur.fetchall()
#对账方式列表（页面左部）
QsqlRec = "select id,rname from reconcil where status='1' order by rname"
cur.execute(QsqlRec)
QrecList = cur.fetchall()
JsqlRec = "select id,rname from reconcil where status='0' order by rname"
cur.execute(JsqlRec)
JrecList = cur.fetchall()
#
rid = request.GET.get('rid', '')
if rid:
sqlShow1 = "select id,rname,status,beginday,endday from reconcil where id={rid}".format(
rid=rid)
curShow1 = conn2.cursor()
curShow1.execute(sqlShow1)
reconList = curShow1.fetchone()
sqlShow2 = "select pid from reconcilitem where rid={rid}".format(
rid=rid)
curShow2 = conn2.cursor()
curShow2.execute(sqlShow2)
payTyleLoad = curShow2.fetchall()
else:
today = datetime.date.today()
currmonth = MethodUtil.getCurrentMonthDay(today)
if today.day <= 15:
reconList = {"beginday": "1", "endday": "15"}
else:
reconList = {
"beginday": "16",
"endday": "{end}".format(end=currmonth[2])
}
payTyleLoad = []
#新建、修改、删除
action = request.GET.get('action', '')
if request.method == 'POST':
reconName = request.POST.get('reconName')
rstatus = request.POST.get('rstatus')
beginday = request.POST.get('beginday')
endday = request.POST.get('endday')
payTyleList = request.POST.getlist('payType')
rid = request.POST.get('reconId')
if action == 'save':
#修改
if rid:
try:
#更新reconcil数据
sqlEdit1 = "update reconcil set rname='{rname}',status='{rstatus}',beginday={beginday},endday={endday} where id={id}"\
.format(rname=reconName,rstatus=rstatus,id=rid,endday=endday,beginday=beginday)
cur.execute(sqlEdit1)
#删除相关信息
sqlEdit2 = "delete from reconcilitem where rid={rid}".format(
rid=rid)
cur.execute(sqlEdit2)
for payTpye in payTyleList:
sqlEdit3 = "insert into reconcilitem (rid,pid) values({rid},'{pid}')".format(
rid=rid, pid=payTpye)
cur.execute(sqlEdit3)
cur.close()
conn2.close()
succ = True
except:
succ = False
#保存
else:
try:
sqlNew1 = "insert into reconcil (rname,status,beginday,endday) values('{name}','{status}',{beginday},{endday})"\
.format(name=reconName,status=rstatus,beginday=beginday,endday=endday)
cur.execute(sqlNew1)
rid = cur.lastrowid
for payTpye in payTyleList:
sqlNew3 = "insert into reconcilitem (rid,pid) values({rid},'{pid}')".format(
rid=int(rid), pid=payTpye)
cur.execute(sqlNew3)
cur.close()
conn2.close()
succ = True
except:
succ = False
#删除
elif action == 'del':
try:
rid = request.POST.get('reconId')
sqlDel1 = "delete from reconcil where id={id}".format(id=rid)
cur.execute(sqlDel1)
sqlDel2 = "delete from reconcilitem where rid={rid}".format(
rid=rid)
cur.execute(sqlDel2)
succ = True
cur.close()
conn2.close()
except:
succ = False
dayList = [n for n in range(1, 32)]
return render(request, 'admin/reconcilType.html', locals())
def query():
monthFirst = datetime.date.today().replace(day=1)
today = datetime.datetime.today()
if (str(today)[8:10] == '01'):
monthFirst = datetime.date(datetime.date.today().year, datetime.date.today().month - 1, 1)
today = datetime.date(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(1)
todayStr = today.strftime('%y-%m-%d')
monthFirstStr = str(monthFirst)
conn = mtu.getMysqlConn()
sqlTop = 'SElECT ShopID,shopname, SUM(qtyz) AS qtyzSum,SUM(qtyl) AS qtylSum,(sum(qtyl) / sum(qtyz)) AS zhonbiSum ' \
'FROM KNegativestock ' \
'WHERE ShopID!="C009" AND sdate BETWEEN "' + monthFirstStr + '" AND "' + todayStr + '" GROUP BY ShopID ORDER BY ShopID'
cur = conn.cursor()
cur.execute(sqlTop)
listTop = cur.fetchall()
listTotal = {'ShopID': '合计', 'shopname': '', 'qtyzSum': 0} # 初始化最后一行
for i in range(0, len(listTop)):
if (not listTop[i]['qtyzSum']):
listTop[i]['qtyzSum'] = 0
else:
listTop[i]['qtyzSum'] = float(listTop[i]['qtyzSum'])
listTop[i]['qtyzSum'] = round((listTop[i]['qtyzSum']) / int(today.day))
if 'qtyzSum' in listTotal:
listTotal['qtyzSum'] += listTop[i]['qtyzSum']
else:
listTotal['qtyzSum'] = listTop[i]['qtyzSum']
if (not listTop[i]['qtylSum']):
listTop[i]['qtylSum'] = 0
else:
listTop[i]['qtylSum'] = float(listTop[i]['qtylSum'])
listTop[i]['qtylSum'] = round((listTop[i]['qtylSum']) / int(today.day))
if 'qtylSum' in listTotal:
listTotal['qtylSum'] += listTop[i]['qtylSum']
else:
listTotal['qtylSum'] = listTop[i]['qtylSum']
if (not listTop[i]['zhonbiSum']):
listTop[i]['zhonbiSum'] = 0
else:
listTop[i]['zhonbiSum'] = float('%0.2f' % (listTop[i]['zhonbiSum'] * 100))
listTotal['zhonbiSum'] = listTotal['qtylSum'] / listTotal['qtyzSum']
listTotal['zhonbiSum'] = str(float('%0.2f' % (listTotal['zhonbiSum'] * 100))) + '%'
listTotal['mingciSum'] = ''
sql = "SELECT b.sdate,SUM(b.qtyz) qtyz , SUM(b.qtyl) qtyl, (SUM(b.qtyl)/SUM(b.qtyz)) zhonbi, (SELECT COUNT(DISTINCT zhonbi) FROM KNegativestock a WHERE a.zhonbi <= b.zhonbi) AS mingci " \
"FROM KNegativestock AS b " \
"WHERE ShopID ='" + listTop[i]['ShopID'] + "' AND sdate BETWEEN '" + monthFirstStr + "' AND '" + todayStr + "' GROUP BY sdate"
cur.execute(sql)
listDetail = cur.fetchall()
for item in listDetail:
date = str(item['sdate'])[8:10]
if (not item['qtyz']):
listTop[i]['qtyz_' + date] = 0
else:
listTop[i]['qtyz_' + date] = float(item['qtyz'])
if 'qtyz_' + date in listTotal:
listTotal['qtyz_' + date] += listTop[i]['qtyz_' + date]
else:
listTotal['qtyz_' + date] = listTop[i]['qtyz_' + date]
if (not item['qtyl']):
listTop[i]['qtyl_' + date] = 0
else:
listTop[i]['qtyl_' + date] = float(item['qtyl'])
if 'qtyl_' + date in listTotal:
listTotal['qtyl_' + date] += listTop[i]['qtyl_' + date]
else:
listTotal['qtyl_' + date] = listTop[i]['qtyl_' + date]
if (not item['zhonbi']):
listTop[i]['zhonbi_' + date] = 0
else:
listTop[i]['zhonbi_' + date] = float('%0.2f' % (item['zhonbi'] * 100))
listTotal['zhonbi_' + date] = listTotal['qtyl_' + date] / listTotal['qtyz_' + date]
listTotal['zhonbi_' + date] = str(float('%0.2f' % (listTotal['zhonbi_' + date] * 100))) + '%'
listTotal['mingci_' + date] = ''
TotalDict = {'listTotal': listTotal}
listTop = ranking(listTop, 'zhonbiSum', 'mingciSum')
monthFirstReal = int(str(listDetail[0]['sdate'])[8:10])
for date in range(monthFirstReal, today.day + 1):
if (date < 10):
listTop = ranking(listTop, 'zhonbi_0' + str(date), 'mingci_0' + str(date))
else:
listTop = ranking(listTop, 'zhonbi_' + str(date), 'mingci_' + str(date))
listTop.sort(key=lambda x: x['ShopID'])
###课组汇总###
sqlDept = 'select deptid,deptidname,sum(qtyz) qtyz,sum(qtyl) qtyl,(sum(qtyl)/sum(qtyz)) zhonbi from KNegativestock' \
' where ShopID!="C009" AND sdate="' + todayStr + '" group by deptid,deptidname order by deptid'
cur = conn.cursor()
cur.execute(sqlDept)
listDept = cur.fetchall()
for obj in listDept:
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
###负库存课组明细###
sqlDeptDetail = 'SELECT shopid,shopname,deptid,deptidname,qtyz,qtyl,zhonbi FROM KNegativestock WHERE ShopID!="C009" AND sdate = "' \
+ todayStr + '" GROUP BY deptid,shopid'
cur = conn.cursor()
cur.execute(sqlDeptDetail)
listDeptDetail = cur.fetchall()
for obj in listDeptDetail:
if (not obj['zhonbi']):
obj['zhonbi'] = 0
obj['zhonbi'] = str(float('%0.4f' % obj['zhonbi']) * 100)[0:4] + '%'
if (not obj['qtyl']):
obj['qtyl'] = 0
obj['qtyl'] = float(obj['qtyl'])
if (not obj['qtyz']):
obj['qtyz'] = 0
obj['qtyz'] = float(obj['qtyz'])
cur.close()
conn.close()
return locals()
