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