def select(self): # 商品id try: cursor.execute( "select name,initial_account from goods where id = '" + self.goodsid + "'") name = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商品名称", "errno": "4001"}), content_type="application/json") #单商品卡券剩余量 try: cursor.execute( "SELECT sum(amount) from userbalance_"+self.goodsid[-2::]+" where goodsAddr = '"+self.goodsid+"' and userAddress = '"+name[0][1]+"'" ) cardvouchers = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单商品卡券剩余量", "errno": "4001"}),content_type="application/json") #商铺单商品提货总用户数 try: cursor.execute( "select count(DISTINCT user_addr) from take_order as o,take_order_goods as g where g.goods_id = '"+self.goodsid+"' and o.order_state = '0' and g.order_number = o.order_number " ) whotake = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商铺单商品提货总用户数", "errno": "4001"}),content_type="application/json") #单商品转赠次数 try: cursor.execute( "select count(amount) from transfer_record where goodsAddr = '"+self.goodsid+"'" ) transfers = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单商品转赠次数", "errno": "4001"}),content_type="application/json") #单商品卡券领取总量 try: cursor.execute( "SELECT sum(amount) from userbalance_" + self.goodsid[-2::] + " where goodsAddr = '" + self.goodsid + "' and userAddress != '"+name[0][1]+"'" ) cardcollection = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单商品卡券领取总量", "errno": "4001"}),content_type="application/json") #单商品卡券发行总量 try: cursor.execute( "select sum(circulation) from goods where id = '" + self.goodsid + "' and is_delete = '0' and is_represent = '0' and putaway = '0'" ) cardissue = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单商品卡券发行总量", "errno": "4001"}),content_type="application/json") dict = { 'name':name[0][0],#商品名字 'cardvouchers':to_string(cardvouchers[0][0]),#单商品卡券剩余量 'whotake':whotake[0][0],#商铺单商品提货总用户数 'transfers':transfers[0][0],#单商品转赠次数 'cardcollection':to_string(cardcollection[0][0]),#单商品卡券领取总量 'cardissue':to_string(cardissue[0][0]),#单商品卡券发行总量 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def select(self): # 用户id try: cursor.execute( "select sum(g.goods_amount),(select name from jld_user where id = '"+self.useraddr+"') from take_order as o,take_order_goods as g where o.user_addr = '"+self.useraddr+"' and o.order_state = '0' and o.order_number = g.order_number" ) userdata = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "用户申请提货量", "errno": "4001"}), content_type="application/json") data = {'num': to_string(userdata[0][0]), 'username': userdata[0][1]} data = {'data': data, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def buytake(self, takeorder, buyorder, splilist, pages, numberbars): self.takeorder = takeorder self.buyorder = buyorder self.splilist = splilist self.pages = pages self.numberbars = numberbars for takekey, take in enumerate(self.takeorder): for key, id in enumerate(self.splilist): if take['manager_id'] == id['id']: self.splilist[key]['tabkegoods'] = self.takeorder[takekey].get('count') for buykey, take in enumerate(self.buyorder): for key, id in enumerate(self.splilist): if take['manager_id'] == id['id']: self.splilist[key]['purchase'] = self.buyorder[buykey].get('count') self.splilist[key]['total'] = to_string(self.buyorder[buykey].get('money')) dict = {'data': self.splilist[(self.pages - 1) * self.numberbars:self.pages * self.numberbars]} totalnum = len(self.splilist) dict['totalnum'] = totalnum return {'data': dict, "errmsg": "成功", "errno": "0"}
def buytake(self, takeorder, buyorder, splilist, pages, numberbars): self.takeorder = takeorder self.buyorder = buyorder self.splilist = splilist self.pages = pages self.numberbars = numberbars for takekey, take in enumerate(self.takeorder): for key, id in enumerate(self.splilist): if take[0] == id['id']: self.splilist[key]['tabkegoods'] = self.takeorder[takekey][1] break for buykey, take in enumerate(self.buyorder): for key, id in enumerate(self.splilist): if take[0] == id['id']: self.splilist[key]['purchase'] = self.buyorder[buykey][1] self.splilist[key]['total'] = to_string(self.buyorder[buykey][2]) break dict = {'data': self.splilist[(self.pages - 1) * self.numberbars:self.pages * self.numberbars]} totalnum = len(self.splilist) dict['totalnum'] = totalnum data = {'data': dict, "errmsg": "成功", "errno": "0"} return data
def select(self): #foreign_key try: cursor.execute( "select name from manager where foreign_key = '"+self.foreign_key+"'") orgname = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商家名字", "errno": "4001"}), content_type="application/json") # 返回所有商家地址和名字 try: cursor.execute( "select id,name,initial_account from goods where foreign_key = '" + self.foreign_key + "' and name is not null") goodsaddr = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商品id", "errno": "4001"}),content_type="application/json") if not goodsaddr: pass goodsidset = set(info[0][-2::] for info in goodsaddr) totalnum = set() for info in goodsidset: try: cursor.execute( "SELECT toAddr from reviewrecord_" + info + " where goodsAddr in (select id from goods where foreign_key = '"+self.foreign_key+"' and name is not null) and type in ('1','2','3')" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "领抢买卡券量", "errno": "4001"}),content_type="application/json") if result: for id in result: totalnum.add(id[0]) #抢券用户数 cardtickets = set() for info in goodsaddr: try: cursor.execute( "select userAddr from activity_user where goodsAddr = '" + info[0] + "'" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "抢券用户数", "errno": "4001"}),content_type="application/json") if result: for id in result: cardtickets.add(id[0]) #单店铺领卡券用户总量 usertotal = set() for info in goodsidset: try: cursor.execute( "SELECT userAddress from userbalance_" + info + " where userAddress != '" + goodsaddr[0][2] + "'" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单店铺领卡券用户总量", "errno": "4001"}), content_type="application/json") if result: for id in result: usertotal.add(id[0]) addrelist = set() for info in goodsidset: try: cursor.execute( "select toAddr from reviewrecord_"+info+" where type = '1' and goodsAddr in (select goodsAddr from goods where foreign_key = '" + self.foreign_key + "' and name is not null)" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商家持有卡券用户总量", "errno": "4001"}),content_type="application/json") if result: for id in result: addrelist.add(id[0]) total = 0 for info in goodsaddr: try: cursor.execute( "SELECT sum(amount) from userbalance_" + info[0][-2::] + " where goodsAddr = '" + info[0] + "' and userAddress != '" + goodsaddr[0][2] + "'" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "卡券领取总量", "errno": "4001"}),content_type="application/json") if result[0][0] is not None: total += int(result[0][0]) try: cursor.execute( "select sum(g.circulation) from goods as g,manager as m where m.foreign_key = '"+self.foreign_key+"' and g.foreign_key = m.foreign_key and g.is_represent = '0' and putaway = '0'") totalmerchandise = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "单商家所有卡券总量", "errno": "4001"}),content_type="application/json") zztotal = 0 for info in goodsaddr: try: cursor.execute( "select count(amount) from transfer_record where goodsAddr = '" + info[0] + "'" ) result = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "全部商品总转增次数", "errno": "4001"}),content_type="application/json") if result[0][0] is not None: zztotal += int(result[0][0]) dict = { 'num': len(addrelist), #商家持有卡券用户总量 'orgname': orgname[0][0],#商家名称 'totalmerchandise':to_string(totalmerchandise[0][0]),# 单商家所有卡券总量 'total':total,# 卡券领取总量 'zztotal':zztotal,#全部商品总转增次数 'usertotal':len(usertotal),#单店铺领卡券用户总量 'cardtickets':len(cardtickets),#抢券用户数 'totalnum':len(totalnum),#领抢买卡券量 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def funcdata(self, request): manager_id = request.data.get('manager_id', 'zhaomemgqiqizai') startime = request.data.get('start', '2018-01-01') endtime = request.data.get('end', '2018-01-01') page = int(request.data.get('page', 1)) num = int(request.data.get('num', 10)) cursor = connections['default'].cursor() goodsidlist = set() goodsdatalist = [] start, end = ormfunc.startend(startime, endtime) # 全部订单量 团购价 try: cursor.execute( "select g.goods_id,count(g.order_number),g.goods_group_price from take_order_goods as g, take_order as o where o.manager_id = '"+manager_id+"' and o.create_date BETWEEN '"+start+"' and '"+end+"' and g.order_number = o.order_number and o.order_state = '0' GROUP BY g.goods_id") totalorder = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "全部订单量", "errno": "4001"}), content_type="application/json") for goodsid in totalorder: goodsidlist.add(goodsid[0]) # 团购订单量 try: cursor.execute( "select g.goods_id,count(g.order_number) from take_order_goods as g, take_order as o where o.manager_id = '"+manager_id+"' and o.create_date BETWEEN '"+start+"' and '"+end+"' and g.order_number = o.order_number and o.order_state = '0' and o.order_type = '1' GROUP BY g.goods_id") groupbuy = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "团购订单量", "errno": "4001"}), content_type="application/json") for goodsid in groupbuy: goodsidlist.add(goodsid[0]) # 零售免费订单量 try: cursor.execute( "select g.goods_id,count(g.order_number) from take_order_goods as g, take_order as o where o.manager_id = '"+manager_id+"' and o.create_date BETWEEN '"+start+"' and '"+start+"' and g.order_number = o.order_number and o.order_state = '0' and o.order_type = '0' GROUP BY g.goods_id") retailorder = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "零售免费订单量", "errno": "4001"}), content_type="application/json") for goodsid in retailorder: goodsidlist.add(goodsid[0]) # 零售订单金额 try: cursor.execute( "select g.goods_id,sum(actual_price) from purchase_order as o, purchase_order_goods as g where o.manager_id = '"+manager_id+"' and o.create_time BETWEEN '"+start+"' and '"+end+"' and o.order_number = g.order_number and o.order_state = '1' and o.payment = '1' and o.pay_state ='1' GROUP BY g.goods_id") retailprice = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "零售订单金额", "errno": "4001"}), content_type="application/json") for goodsid in retailprice: goodsidlist.add(goodsid[0]) # 活动订单金额 try: cursor.execute( "select g.goods_id,sum(actual_price) from purchase_order as o, purchase_order_goods as g where o.manager_id = '"+manager_id+"' and o.create_time BETWEEN '"+start+"' and '"+end+"' and o.order_number = g.order_number and o.order_state = '1' and o.payment = '2' and o.pay_state ='1' GROUP BY g.goods_id") activiprice = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "活动订单金额", "errno": "4001"}), content_type="application/json") for goodsid in activiprice: goodsidlist.add(goodsid[0]) # 销售总金额 try: cursor.execute( "select g.goods_id,sum(actual_price) from purchase_order as o, purchase_order_goods as g where o.manager_id = '"+manager_id+"' and o.create_time BETWEEN '"+start+"' and '"+end+"' and o.order_number = g.order_number and o.order_state = '1' and o.pay_state ='1' GROUP BY g.goods_id") totalprice = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "销售总金额", "errno": "4001"}), content_type="application/json") for goodsid in totalprice: goodsidlist.add(goodsid[0]) # 使用代言券数量 代言券总抵扣金额 代言佣金总额 最终收入金额 try: cursor.execute( "SELECT g.goods_id,count(g.coupons_addr),(count(g.coupons_addr) * (SELECT represent_price FROM goods WHERE id = (SELECT DISTINCT goods_id FROM purchase_order_goods WHERE coupons_addr = g.coupons_addr))),(count(g.coupons_addr) * (SELECT represent_commission FROM goods WHERE id = (SELECT DISTINCT goods_id FROM purchase_order_goods WHERE coupons_addr = g.coupons_addr))),sum(actual_price) - (count(g.coupons_addr) * (SELECT represent_commission FROM goods WHERE id = (SELECT DISTINCT goods_id FROM purchase_order_goods WHERE coupons_addr = g.coupons_addr)))FROM purchase_order AS o,purchase_order_goods AS g WHERE o.manager_id = '"+manager_id+"' AND o.create_time BETWEEN '"+start+"'AND '"+end+"' AND o.order_number = g.order_number AND o.order_state = '1' AND o.pay_state ='1' AND g.is_coupons = '1' GROUP BY g.goods_id") couponsnumprice = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "最终收入金额", "errno": "4001"}), content_type="application/json") for goodsid in couponsnumprice: goodsidlist.add(goodsid[0]) #拼接商品字典 for id in goodsidlist: # 规格型号 try: cursor.execute( "select specification_id1 from goods_property where goods_id ='"+id+"'") specification = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "规格型号", "errno": "4001"}), content_type="application/json") try: data = GoodsOrm.objects.filter(id=id) except GoodsOrm.DoesNotExist: return HttpResponse(status=404) for info in data: goodsdatalist.append({ 'name': info.name, #商品名称 'id': info.id, #商品ID 'specification': specification[0][0], #规格型号 'market_price': to_string(info.market_price), #零售单价 'activity_pricr': 0, #活动单价 'total_delivery': 0, #总提货订单量 'retail_order': 0, #零售订单量 'activity_order': 0, #活动订单量 # 'free_order': 0, #免费提货量 'retail_amount': 0, #零售订单金额 'activity_amount': 0, #活动订单金额 'total_sales': 0, #销售总额 'voucher_deduction': to_string(info.represent_price), #代言券抵扣金额 'vocher_num': 0,#使用代言券数量 'totalvocher_price': 0,#代言券总抵扣金额 'commission': to_string(info.represent_commission), # 代言人佣金 'totalcommission': 0 ,# 佣金扣除总额 'final_revenue': 0, #最终收入金额 }) for key, info in enumerate(goodsdatalist): for data in totalorder: if info['id'] == data[0]: goodsdatalist[key]['total_delivery'] = to_string(data[2]) goodsdatalist[key]['activity_pricr'] = data[1] break for key, info in enumerate(goodsdatalist): for data in groupbuy: if info['id'] == data[0]: goodsdatalist[key]['activity_order'] = data[1] break for key, info in enumerate(goodsdatalist): for data in retailorder: if info['id'] == data[0]: goodsdatalist[key]['retail_order'] = to_string(data[1]) break for key, info in enumerate(goodsdatalist): for data in retailprice: if info['id'] == data[0]: goodsdatalist[key]['retail_amount'] = to_string(data[1]) break for key, info in enumerate(goodsdatalist): for data in activiprice: if info['id'] == data[0]: goodsdatalist[key]['activity_amount'] = to_string(data[1]) break for key, info in enumerate(goodsdatalist): for data in totalprice: if info['id'] == data[0]: goodsdatalist[key]['total_sales'] = to_string(data[1]) break for key, info in enumerate(goodsdatalist): for data in couponsnumprice: if info['id'] == data[0]: goodsdatalist[key]['vocher_num'] = to_string(data[1]) goodsdatalist[key]['totalvocher_price'] = to_string(data[2]) goodsdatalist[key]['totalcommission'] = to_string(data[3]) goodsdatalist[key]['final_revenue'] = to_string(data[4]) break data = {'data': {'tdgoods': goodsdatalist[(page - 1) * num:page * num], 'total': len(goodsdatalist)}, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def funcdata(self): # 财务需要 start = '2019-07-21 16:00:00' end = '2019-07-28 16:00:00' # 商户商品信息 try: cursor.execute( "select m.id,m.name,s.id,s.name,s.circulation, s.initial_account from goods as s, manager as m where s.state in ('2','4') and s.foreign_key = m.foreign_key and s.name is not null GROUP BY s.id ORDER BY m.id, s.id desc" ) shopgoods = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商户商品信息", "errno": "4001"}), content_type="application/json") # 购买详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount),sum(o.actual_price) from purchase_order as o, purchase_order_goods as g where o.pay_time BETWEEN '" + start + "' and '" + end + "' and o.pay_state = '1' and g.order_number = o.order_number GROUP BY g.goods_id " ) buygodos = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "购买详情", "errno": "4001"}), content_type="application/json") # 提货详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount) from take_order_goods as g, take_order as o where o.order_state = '0' and o.take_state in('2','3') and o.order_number = g.order_number GROUP BY g.goods_id " ) takegoods = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "提货详情", "errno": "4001"}), content_type="application/json") # 提货详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount) from take_order_goods as g, take_order as o where o.create_date BETWEEN '" + start + "' and '" + end + "' and o.order_state = '0' and o.take_state in('2','3') and o.order_number = g.order_number GROUP BY g.goods_id " ) takegoodss = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "提货详情", "errno": "4001"}), content_type="application/json") resultdict = [] surplus = [] for info in shopgoods: datadict = OrderedDict() datadict['manageid'] = info[0] datadict['managename'] = info[1] datadict['goodsid'] = info[2] datadict['goodsname'] = info[3] datadict['totalnum'] = info[4] datadict['surplus'] = info[4] datadict['buynum'] = 0 datadict['buyprice'] = 0 datadict['takenum'] = 0 resultdict.append(datadict) # 获取 商品地址和剩余数量 # try: cursor.execute( "select goodsAddr,sum(amount) from userbalance_" + info[2][-2::] + " where userAddress != '" + info[5] + "' and goodsAddr = '" + info[2] + "'" ) fromuser = cursor.fetchall() # except: # return HttpResponse(json.dumps({"errmsg": "商品地址和数量", "errno": "4001"}), content_type="application/json") if fromuser[0][0] is not None: surplus.append(fromuser) for info in buygodos: for data in resultdict: if data['goodsid'] == info[0]: data['buynum'] = to_string(info[1]) data['buyprice'] = to_string(info[2]) for info in takegoods: for data in resultdict: if data['goodsid'] == info[0]: data['surplus'] = data['totalnum'] - to_string(info[1]) for info in takegoodss: for data in resultdict: if data['goodsid'] == info[0]: data['takenum'] = to_string(info[1]) for info in surplus: for key, data in enumerate(resultdict): if data['goodsid'] == info[0][0]: data['surplus'] = data['surplus'] - to_string(info[0][1]) book = xlwt.Workbook() # 新建一个excel sheet = book.add_sheet('case1_sheet', cell_overwrite_ok=True) # 添加一个sheet页 row0 = ["商户ID", "商户名称", "商品ID", "商品名称", "发行总数", "库存数量", "购买数量", "购买金额", "提货数量"] row = 0 # 控制行 # 生成第一行 for i in range(0, len(row0)): sheet.write(0, i, row0[i]) row += 1 row = 1 # 控制行 for stu in resultdict: col = 0 # 控制列 for s in stu: # 再循环里面list的值,每一列 sheet.write(row, col, stu.get(s)) col += 1 row += 1 book.save('finance.xls') # 保存到当前目录下 # with open("Finance.csv", "w") as csvfile: # writer = csv.writer(csvfile) # # # 先写入columns_name # writer.writerow(["商户ID", "商户名称", "商品ID", "商品名称", "发行总数", "库存数量", "购买数量", "购买金额", "提货数量"]) # # 写入多行用writerows # for write in resultdict: # writer.writerow([write["manageid"], write["managename"], write["goodsid"], write["goodsname"], str(write["totalnum"]), str(write["surplus"]), str(write["buynum"]), str(write["buyprice"]), str(write["takenum"])]) return HttpResponse(json.dumps({'data': 222, "errmsg": "555", "errno": "4001"}), content_type="application/json")
def funcdata(self): time_stamp = int(time.time()) # 可提现 try: cursor.execute( "select g.affiliateID from purchase_order as o, purchase_order_goods as g where g.order_number = o.order_number and g.affiliateID is not null GROUP BY g.affiliateID" ) affid = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "可提现", "errno": "400"}), content_type="application/json") cashlist = [] forzelist = [] for usid in affid: try: cursor.execute( "select COALESCE(sum(g.commission * g.goods_amount), 0) - (select COALESCE(sum(amount),0) from withdrawals_record where user_id = '" + usid[ 0] + "' and state in ('1','0')),(select name from jld_user where id = g.affiliateID),g.affiliateID from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + usid[0] + "' and o.pay_state = '1' and o.pay_time < '" + commission_price( time_stamp) + "' and o.order_number = g.order_number" ) withdrawal = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "可提现", "errno": "400"}), content_type="application/json") if withdrawal[0][0] > 0: cashlist.append(withdrawal) # 冻结 try: cursor.execute( "select COALESCE(sum(g.commission * g.goods_amount ), 0),(select name from jld_user where id = g.affiliateID),g.affiliateID from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + usid[0] + "' and o.pay_state = '1' and o.pay_time > '" + commission_price( time_stamp) + "' and o.order_number = g.order_number " ) thaw = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "冻结", "errno": "400"}), content_type="application/json") if thaw[0][0] > 0: forzelist.append(thaw) resultdict = [] for info in affid: datadict = OrderedDict() datadict['id'] = info[0] datadict['name'] = '' datadict['cash'] = 0 datadict['frozen'] = 0 resultdict.append(datadict) for info in forzelist: for data in resultdict: if data['id'] == info[0][2]: data['frozen'] = to_string(info[0][0]) data['name'] = to_string(info[0][1]) for info in cashlist: for data in resultdict: if data['id'] == info[0][2]: data['cash'] = to_string(info[0][0]) data['name'] = to_string(info[0][1]) book = xlwt.Workbook() # 新建一个excel sheet = book.add_sheet('case1_sheet', cell_overwrite_ok=True) # 添加一个sheet页 row0 = ["用户ID", "用户名称", "入账", "解冻中"] row = 0 # 控制行 # 生成第一行 for i in range(0, len(row0)): sheet.write(0, i, row0[i]) row += 1 row = 1 # 控制行 for stu in resultdict: col = 0 # 控制列 for s in stu: # 再循环里面list的值,每一列 sheet.write(row, col, stu.get(s)) col += 1 row += 1 book.save('Commission.xls') # 保存到当前目录下 return HttpResponse( json.dumps({'data': {'thaw': 'llll', 'withdrawal': 'kkkk'}, "errmsg": "成功", "errno": "200"}), content_type="application/json")
def get(self, request): global null null = '' global false false = False cursor = connections['default'].cursor() redis_conn = get_redis_connection('default') # 获取搜索类型 type = request.GET.get('type') token = request.GET.get('token') try: tokendata = eval(redis_conn.get(token)) except: return HttpResponse(json.dumps({ "errmsg": "token已过期", "errno": "4001" }), content_type="application/json") foreign_key = tokendata['foreignKey'] manager_id = tokendata['managerId'] if not all([type, token]): return HttpResponse(json.dumps({ "errmsg": "type token参数未传", "errno": "4001" }), content_type="application/json") elif type == '0': # 商品总数 try: cursor.execute( "select count(id) from goods where foreign_key = '" + foreign_key + "' and name is not null") numshopuser = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "商品总数", "errno": "4001" }), content_type="application/json") # 今日订单总量 try: cursor.execute( "select count(order_number) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time bETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59' and pay_state ='1'") todaybuyorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日订单总量", "errno": "4001" }), content_type="application/json") # 购买卡券用户 try: cursor.execute( "select count(distinct user_addr) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time bETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59' and pay_state ='1'") buycarduser = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "购买卡券用户", "errno": "4001" }), content_type="application/json") # 完成提货总量 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and create_date bETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59' and take_state = '3'") ordertake = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本周提货订单量", "errno": "4001" }), content_type="application/json") goodsidset = set() # 卡券转赠总数 try: cursor.execute( "select id,name ,initial_account from goods where foreign_key = '" + foreign_key + "' and name is not null") goodsid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "店铺地址名字", "errno": "4001" }), content_type="application/json") total = 0 for info in goodsid: goodsidset.add(info[0][-2::]) try: cursor.execute( "select sum(amount) from transfer_record where goodsAddr = '" + info[0] + "'") result = cursor.fetchall() if result[0][0] is not None: total += int(result[0][0]) except: return HttpResponse(json.dumps({ "errmsg": "转赠总数", "errno": "4001" }), content_type="application/json") #领取卡券 receive = 0 for info in goodsidset: try: cursor.execute( "select sum(amount) from reviewrecord_" + info + " where type = '1' and goodsAddr in (select goodsAddr from goods where foreign_key = '" + foreign_key + "' and name is not null)") result = cursor.fetchall() if result[0][0] is not None: receive += int(result[0][0]) except: return HttpResponse(json.dumps({ "errmsg": "领取卡券", "errno": "4001" }), content_type="application/json") # 卡券剩余 surplus = 0 for info in goodsidset: try: cursor.execute("SELECT sum(amount) from userbalance_" + info + " where userAddress = '" + goodsid[0][2] + "'") result = cursor.fetchall() if result[0][0] is not None: surplus += int(result[0][0]) except: return HttpResponse(json.dumps({ "errmsg": "卡券剩余", "errno": "4001" }), content_type="application/json") dict = { 'numshopuser': numshopuser[0][0], #商品总数 'todaybuyorder': todaybuyorder[0][0], #今日订单总量 'buycarduser': buycarduser[0][0], #购买卡券用户 'ordertake': ordertake[0][0], #完成提货总量 'totalnumcardcoupon': total, #卡券转赠总数 'todaytotalviews': 0, #今日总浏览量 'totacardreceipt': receive, #卡券领取总量 'totalcardsurplus': surplus, #卡券剩余总量 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") elif type == '1': goodsidset = set() # 卡券转赠总数 try: cursor.execute( "select id,name ,initial_account from goods where foreign_key = '" + foreign_key + "' and name is not null") goodsid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "店铺地址名字", "errno": "4001" }), content_type="application/json") for info in goodsid: goodsidset.add(info[0][-2::]) #购买订单 try: cursor.execute( "select count(order_number) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time BETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59' and pay_state = '1'") buyorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "购买订单", "errno": "4001" }), content_type="application/json") # 提货订单 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and create_date BETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59'") takeorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "提货订单", "errno": "4001" }), content_type="application/json") # 成交额 try: cursor.execute( "select COALESCE(SUM(actual_price),0) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time BETWEEN '" + datedict['today'][0] + " 00:00:00' and '" + datedict['today'][0] + " 23:59:59' and pay_state = '1'") turnover = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日订单总量今日销售总额", "errno": "4001" }), content_type="application/json") # 领取卡券 receive = 0 for info in goodsidset: try: cursor.execute( "select sum(amount) from reviewrecord_" + info + " where type = '1' and goodsAddr in (select goodsAddr from goods where foreign_key = '" + foreign_key + "' and name is not null)") result = cursor.fetchall() if result[0][0] is not None: receive += int(result[0][0]) except: return HttpResponse(json.dumps({ "errmsg": "领取卡券", "errno": "4001" }), content_type="application/json") dict = { 'buyorder': to_string(buyorder[0][0]), #购买订单 'takeorder': takeorder[0][0], #提货订单 'turnover': to_string(turnover[0][0]), #成交额 'browsevolume': 0, #浏览量 'godsbowsed': 0, #被浏览商品 'crdreceipt': receive, #卡券领取 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def funcdata(self): cursor = connections['default'].cursor() # 商户商品信息 try: cursor.execute( "select m.id,m.name,s.id,s.name,s.circulation, s.initial_account from goods as s, manager as m where s.state in ('2','4') and s.foreign_key = m.foreign_key and s.name is not null GROUP BY s.id ORDER BY m.id, s.id desc " ) shopgoods = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商户商品信息", "errno": "4001"}), content_type="application/json") # 购买详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount),sum(o.actual_price) from purchase_order as o, purchase_order_goods as g where o.pay_time BETWEEN '" + self.manage['startTime'] + "' and '" + self.manage['endTime'] + "' and o.pay_state = '1' and g.order_number = o.order_number GROUP BY g.goods_id " ) buygodos = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "购买详情", "errno": "4001"}), content_type="application/json") # 提货详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount) from take_order_goods as g, take_order as o where o.order_state = '0' and o.take_state in('2','3') and o.order_number = g.order_number GROUP BY g.goods_id " ) takegoods = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "提货详情", "errno": "4001"}), content_type="application/json") # 提货详情 try: cursor.execute( "select g.goods_id,sum(g.goods_amount) from take_order_goods as g, take_order as o where o.create_date BETWEEN '" + self.manage['startTime'] + "' and '" + self.manage['endTime'] + "' and o.order_state = '0' and o.take_state in('2','3') and o.order_number = g.order_number GROUP BY g.goods_id " ) takegoodss = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "提货详情", "errno": "4001"}), content_type="application/json") resultdict = [] surplus = [] for info in shopgoods: datadict = OrderedDict() datadict['manageid'] = info[0] datadict['managename'] = info[1] datadict['goodsid'] = info[2] datadict['goodsname'] = info[3] datadict['totalnum'] = info[4] datadict['surplus'] = info[4] datadict['buynum'] = 0 datadict['buyprice'] = 0 datadict['takenum'] = 0 resultdict.append(datadict) # 获取 商品地址和剩余数量 try: cursor.execute( "select goodsAddr,sum(amount) from userbalance_" + info[2][-2::] + " where userAddress != '" + info[5] + "' and goodsAddr = '" + info[2] + "'" ) fromuser = cursor.fetchall() except: return HttpResponse(json.dumps({"errmsg": "商品地址和数量", "errno": "4001"}), content_type="application/json") if fromuser[0][0] is not None: surplus.append(fromuser) for info in buygodos: for data in resultdict: if data['goodsid'] == info[0]: data['buynum'] = to_string(info[1]) data['buyprice'] = to_string(info[2]) for info in takegoods: for data in resultdict: if data['goodsid'] == info[0]: data['surplus'] = data['totalnum'] - to_string(info[1]) for info in takegoodss: for data in resultdict: if data['goodsid'] == info[0]: data['takenum'] = to_string(info[1]) for info in surplus: for key, data in enumerate(resultdict): if data['goodsid'] == info[0][0]: data['surplus'] = data['surplus'] - to_string(info[0][1]) dict = {'goodsdata': resultdict[(self.manage['pages'] - 1) * self.manage['numberbars']:self.manage['pages'] * self.manage['numberbars']]} dict['totalnum'] = len(shopgoods) data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def funcdata(self): list = [] goodslist = [] logisticslist = [] goodsnamelist = [] try: total = PurchaseOrder.objects.filter(manager_id=self.manage['manager_id'], pay_time__range=(self.manage['startTime'], self.manage['endTime']), pay_state='1', order_state='1') buyordernum = PurchaseOrder.objects.filter(manager_id=self.manage['manager_id'], pay_time__range=(self.manage['startTime'], self.manage['endTime']), pay_state='1', order_state='1').order_by('-create_time')[(self.manage['pages'] - 1) * self.manage['numberbars']:self.manage['pages'] * self.manage['numberbars']] except PurchaseOrder.DoesNotExist: return HttpResponse(status=404) for data in buyordernum: list.append( {'orderNum': data.order_number, 'userName': '', 'phone': '', 'amount': '', 'province': '', 'city': '', 'area': '', 'detailAddr': '', 'goodsName': '', 'brief': '', 'value': '', 'amountmoney': to_string(data.actual_price), 'isUseCoupons': '', 'goodsid': '', 'date': str_time_stamp(data.create_time.strftime('%Y-%m-%d %H:%M:%S'))} ) try: goodsdernum = PurchaseOorderGoods.objects.filter(order_number=data.order_number) logisticsdernum = PurchaseOrderLogistics.objects.filter(order_num=data.order_number) goodslist.append(goodsdernum) logisticslist.append(logisticsdernum) except PurchaseOorderGoods.DoesNotExist: return HttpResponse(status=404) for id in goodslist: for data in id: try: goodsname = GoodsOrm.objects.filter(id=data.goods_id) goodsnamelist.append(goodsname) except UserData.DoesNotExist: return HttpResponse(status=404) for key, info in enumerate(list): for goodsid in goodslist: for data in goodsid: if info['orderNum'] == data.order_number: list[key]['amount'] = data.goods_amount list[key]['goodsid'] = data.goods_id list[key]['isUseCoupons'] = data.is_coupons break for key, info in enumerate(list): for goodsid in goodsnamelist: for data in goodsid: if info['goodsid'] == data.id: list[key]['brief'] = data.brief list[key]['goodsName'] = data.name list[key]['value'] = to_string(data.market_price) break for key, info in enumerate(list): for goodsid in logisticslist: for data in goodsid: if info['orderNum'] == data.order_num: list[key]['userName'] = data.consignee_user list[key]['phone'] = data.consignee_phone list[key]['province'] = data.province list[key]['city'] = data.city list[key]['area'] = data.area list[key]['detailAddr'] = data.detail_addr list[key]['logisticsCompany'] = data.logistics_company list[key]['LogisticsNum'] = data.logistics_num break dict = {'tdgoods': list} dict['totalnum'] = len(total) data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def funcdata(self): list = [] goodslist = [] logisticslist = [] goodsnamelist = [] propertylist = [] try: total = TakeOrder.objects.exclude(order_state='1').filter(manager_id=self.manage['manager_id'], create_date__range=(self.manage['startTime'], self.manage['endTime']), take_state__in=[0, 1, 2, 3]) takeordernum = TakeOrder.objects.exclude(order_state='1').filter(manager_id=self.manage['manager_id'],create_date__range=(self.manage['startTime'], self.manage['endTime']), take_state__in=[0, 1, 2, 3]).order_by('-create_date')[(self.manage['pages'] - 1) * self.manage['numberbars']:self.manage['pages'] * self.manage['numberbars']] except TakeOrder.DoesNotExist: return HttpResponse(status=404) for order in takeordernum: list.append( {'goodsName': '', 'brief': '', 'orderNum': order.order_number, 'userName': order.user_name, 'phone': '', 'amount': '', 'msg': order.user_msg, 'province': '', 'city': '', 'area': '', 'detailAddr': '', 'state': order.take_state, 'logisticsCompany': '', 'LogisticsNum': '', 'goodsid': '', 'price': '', 'group_price': '', 'date': str_time_stamp(order.create_date.strftime('%Y-%m-%d %H:%M:%S'))}) try: goodsdernum = TakeOrderGoods.objects.filter(order_number=order.order_number) logisticsdernum = TakeOrderLogistics.objects.filter(order_num=order.order_number) goodslist.append(goodsdernum) logisticslist.append(logisticsdernum) except UserData.DoesNotExist: return HttpResponse(status=404) for id in goodslist: for data in id: try: goodsname = GoodsOrm.objects.filter(id=data.goods_id) goodsnamelist.append(goodsname) property = GoodsProperty.objects.filter(goods_id=data.goods_id) propertylist.append(property) except UserData.DoesNotExist: return HttpResponse(status=404) for key, info in enumerate(list): for goodsid in goodslist: for data in goodsid: if info['orderNum'] == data.order_number: list[key]['amount'] = data.goods_amount list[key]['goodsid'] = data.goods_id break for key, info in enumerate(list): for goodsid in goodsnamelist: for data in goodsid: if info['goodsid'] == data.id: list[key]['brief'] = data.brief list[key]['goodsName'] = data.name break for key, info in enumerate(list): for goodsid in propertylist: for data in goodsid: if info['goodsid'] == data.goods_id: list[key]['price'] = to_string(data.price) list[key]['group_price'] = to_string(data.group_price) break for key, info in enumerate(list): for goodsid in logisticslist: for data in goodsid: if info['orderNum'] == data.order_num: list[key]['phone'] = data.consignee_phone list[key]['province'] = data.province list[key]['city'] = data.city list[key]['area'] = data.area list[key]['detailAddr'] = data.detail_addr list[key]['logisticsCompany'] = data.logistics_company list[key]['LogisticsNum'] = data.logistics_num break dict = {'tdgoods': list} dict['totalnum'] = len(total) data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json")
def get(self, request): global true true = True global null null = '' global false false = False type = request.GET.get('type') token = request.GET.get('token') start_day = request.GET.get('start') end_day = request.GET.get('end') year = request.GET.get('year') month = request.GET.get('month') page = request.GET.get('page') num = request.GET.get('numberbars') redis_conn = get_redis_connection('default') try: tokendata = eval(redis_conn.get(token)) # tokendata = eval(get_redis_connection('default').get(request.GET.get('token'))) except: return HttpResponse(json.dumps({ "errmsg": "token已过期", "errno": "4001" }), content_type="application/json") foreign_key = tokendata['foreignKey'] manager_id = tokendata['managerId'] if not start_day and not end_day: pass elif start_day in ['month', 'week', 'total', 'yesday', 'months']: datestart = datedict[start_day][0] dateend = datedict[start_day][1] else: datestart = localutc(int(start_day)) dateend = localutc(int(end_day)) cursor = connections['default'].cursor() if not all([type, token]): return HttpResponse(json.dumps({ "errmsg": "type token参数未传", "errno": "4001" }), content_type="application/json") elif type == '0': #今日订单总量今日销售总额 try: cursor.execute( "select count(order_number),COALESCE(SUM(order_price),0) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state='1' and create_time BETWEEN '" + datedict['today'][0] + "' and '" + datedict['today'][1] + "' and pay_state ='1'") ordervolumesalestoday = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日订单总量今日销售总额", "errno": "4001" }), content_type="application/json") # 昨日销售总额 try: cursor.execute( "select COALESCE(SUM(order_price),0) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state='1' and create_time BETWEEN '" + datedict['yesday'][0] + "' and '" + datedict['yesday'][1] + "' and pay_state ='1'") totalsalesyesdate = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "昨日销售总额", "errno": "4001" }), content_type="application/json") # 七日销售总额 try: cursor.execute( "select COALESCE(SUM(order_price),0) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state='1' and create_time BETWEEN '" + datedict['week'][0] + "' and '" + datedict['today'][1] + "' and pay_state ='1'") totalsalesweek = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "七日销售总额", "errno": "4001" }), content_type="application/json") # 商品总览 #已下架商品 try: cursor.execute( "select count(id) from goods where foreign_key = '" + foreign_key + "' and putaway = '1' and name is not null") offshelfmerchandise = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已下架商品", "errno": "4001" }), content_type="application/json") # 已上架商品 try: cursor.execute( "select count(id) from goods where foreign_key = '" + foreign_key + "' and putaway = '0' and name is not null") goodsonshelves = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已下架商品", "errno": "4001" }), content_type="application/json") # 全部商品 try: cursor.execute( "select count(id) from goods where foreign_key = '" + foreign_key + "' and name is not null") tightstock = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "全部商品", "errno": "4001" }), content_type="application/json") # 用户今日新增 try: cursor.execute( "select count(DISTINCT user_addr) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state = '1' and pay_state = '1' and user_addr in (select jld_user.id from jld_user where create_time BETWEEN '" + datedict['today'][0] + "' and '" + datedict['today'][1] + "')") useraddtoday = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "用户今日新增", "errno": "4001" }), content_type="application/json") # 用户昨日新增 try: cursor.execute( "select count(DISTINCT user_addr) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state = '1' and pay_state = '1' and user_addr in (select jld_user.id from jld_user where create_time BETWEEN '" + datedict['yesday'][0] + "' and '" + datedict['yesday'][1] + "')") useraddyesday = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "用户昨日新增", "errno": "4001" }), content_type="application/json") # 用户本月新增 try: cursor.execute( "select count(DISTINCT user_addr) from purchase_order where manager_id = '" + str(manager_id) + "' and order_state = '1' and pay_state = '1' and user_addr in (select jld_user.id from jld_user where create_time BETWEEN '" + datedict['day_begin'][0] + " 00:00:00' and '" + datedict['day_end'][0] + " 23:59:59')") useraddmonth = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "用户昨日新增", "errno": "4001" }), content_type="application/json") membershipnum = set() # 会员总数 try: cursor.execute("select id from goods where foreign_key = '" + foreign_key + "' and name is not null") goodsid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "goodsid", "errno": "4001" }), content_type="application/json") for id in goodsid: # 会员总数 try: cursor.execute( "select userAddr from relation where goodsAddr = '" + id[0] + "'") usercount = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "会员总数", "errno": "4001" }), content_type="application/json") if not usercount: pass for data in usercount: membershipnum.add(data[0]) dict = { 'membershipnum': len(membershipnum), #会员总数 'useraddmonth': useraddmonth[0][0], #用户本月新增 'useraddyesday': useraddyesday[0][0], #用户昨日新增 'useraddtoday': useraddtoday[0][0], #用户今日新增 'tightstock': tightstock[0][0], #全部商品 'goodsonshelves': goodsonshelves[0][0], #已上架商品 'offshelfmerchandise': offshelfmerchandise[0][0], #已下架商品 'totalsalesyesdate': to_string(totalsalesyesdate[0][0]), #昨日销售总额 'totalsalesweek': to_string(totalsalesweek[0][0]), #七日销售总额 'ordervolumetoday': ordervolumesalestoday[0][0], #今日订单总量 'salestoday': to_string(ordervolumesalestoday[0][1]), #今日销售总额 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") #待处理事物 elif type == '1': # 待付款订单 try: cursor.execute( "select count(order_number) from purchase_order where manager_id = '" + manager_id + "' and order_state='1' and pay_state = '0'") ordertobepaid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "待付款订单", "errno": "4001" }), content_type="application/json") # 待发货订单 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and take_state in ('0','1')") standbyorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "待发货订单", "errno": "4001" }), content_type="application/json") # 已发货订单 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and take_state in ('2','3')") outgoingorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已发货订单", "errno": "4001" }), content_type="application/json") # 已完成订单 try: cursor.execute( "select count(purchase_number) from take_order where manager_id = '" + manager_id + "' and take_state = '3'") completedorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已完成订单", "errno": "4001" }), content_type="application/json") # 本周提货订单量 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and create_date bETWEEN '" + datedict['week'][0] + " ' and '" + datedict['today'][1] + "' ") weekordertake = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本周提货订单量", "errno": "4001" }), content_type="application/json") # 本月提货量 try: cursor.execute( "select count(order_number) from take_order where manager_id = '" + manager_id + "' and create_date bETWEEN '" + datedict['month'][0] + "' and '" + datedict['today'][1] + "'") monthordertake = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本月提货量", "errno": "4001" }), content_type="application/json") # 本周购买量 try: cursor.execute( "select COALESCE(sum(actual_price),0) from purchase_order where manager_id = '" + manager_id + "' and order_state='1' and create_time bETWEEN '" + datedict['week'][0] + "' and '" + datedict['today'][1] + "' and pay_state ='1'") weekorderbuy = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本月购买量", "errno": "4001" }), content_type="application/json") # 本月购买量 try: cursor.execute( "select COALESCE(sum(actual_price),0) from purchase_order where manager_id = '" + manager_id + "' and order_state='1' and create_time bETWEEN '" + datedict['day_begin'][0] + " 00:00:00' and '" + datedict['day_end'][0] + " 23:59:59' and pay_state ='1'") monthorderbuy = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本月购买量", "errno": "4001" }), content_type="application/json") # 处理退货 try: cursor.execute( "select count(outTradeNo) from apply_sale where orgAddr = '" + manager_id + "' and type = '1' and state in ('0','1')") returngoods = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "处理退货", "errno": "4001" }), content_type="application/json") # 处理退款 try: cursor.execute( "select count(outTradeNo) from apply_sale where orgAddr = '" + manager_id + "' and type = '2' and state in ('0','1')") returnmony = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "处理退款", "errno": "4001" }), content_type="application/json") dict = { 'ordertobepaid': ordertobepaid[0][0], # 待付款订单 'standbyorder': standbyorder[0][0], # 待发货订单 'outgoingorder': outgoingorder[0][0], # 已发货订单 'completedorder': completedorder[0][0], # 已完成订单 'weekordertake': weekordertake[0][0], # 本周提货订单量 'monthordertake': monthordertake[0][0], # 本月提货量 'weekorderbuy': to_string(weekorderbuy[0][0]), # 本周购买总额 'monthorderbuy': to_string(monthorderbuy[0][0]), # 本月购买总额 'returngoods': to_string(returngoods[0][0]), # 处理退货 'returnmony': to_string(returnmony[0][0]), # 处理退款 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # 综合统计本周,本月,选择时间 提货 elif type == '2': if not all([start_day, end_day]): return HttpResponse(json.dumps({ "errmsg": "时间未传", "errno": "4001" }), content_type="application/json") # 选择提货量 try: cursor.execute( "select count(order_number),date_format(create_date,'%Y-%m-%d') from take_order where manager_id = '" + manager_id + "' and order_state = '0' and create_date bETWEEN '" + datestart + "' and '" + dateend + "' GROUP BY date_format(create_date,'%Y-%m-%d')") selecttakenum = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本月提货量", "errno": "4001" }), content_type="application/json") import pandas as pd date = pd.date_range(datestart, dateend, freq='D') week = [int(i.strftime("%w")) for i in date] # 0表示星期日 dataframe = pd.DataFrame({'date': date, 'week': week, 'num': 0.00}) for key1, sqldate in enumerate(selecttakenum): for key, date in enumerate(dataframe['date']): if sqldate[1] == date.strftime('%Y-%m-%d'): dataframe['num'][key] = selecttakenum[key1][0] break result = json.loads(dataframe.to_json()) data = {'data': result, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # ccc elif type == '3': if not all([start_day, end_day]): return HttpResponse(json.dumps({ "errmsg": "时间未传", "errno": "4001" }), content_type="application/json") # 选择购买量 try: cursor.execute( "select sum(actual_price),date_format(create_time,'%Y-%m-%d') from purchase_order where manager_id = '" + manager_id + "' and order_state='1' and create_time bETWEEN '" + datestart + "' and '" + dateend + "' and pay_state = '1' GROUP BY date_format(create_time,'%Y-%m-%d')" ) selectbuynum = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "本月购买量", "errno": "4001" }), content_type="application/json") import pandas as pd date = pd.date_range(datestart, dateend, freq='D') week = [int(i.strftime("%w")) for i in date] # 0表示星期日 dataframe = pd.DataFrame({'date': date, 'week': week, 'num': 0.00}) for key1, sqldate in enumerate(selectbuynum): for key, date in enumerate(dataframe['date']): if sqldate[1] == date.strftime('%Y-%m-%d'): dataframe['num'][key] = selectbuynum[key1][0] break result = json.loads(dataframe.to_json()) return JsonResponse({'data': result, "errmsg": "成功", "errno": "0"}) # 交易数据 elif type == '4': if not all([start_day, end_day]): return HttpResponse(json.dumps({ "errmsg": "时间未传", "errno": "4001" }), content_type="application/json") # 下单人数,订单数,下单件数,下单金额 try: cursor.execute( "select COALESCE(count(o.user_addr),0),COALESCE(count(o.order_number),0),COALESCE(sum(g.goods_amount),0),COALESCE(sum(o.actual_price),0) from purchase_order as o,purchase_order_goods as g where manager_id = '" + manager_id + "' and o.order_state='1' and o.create_time bETWEEN '" + datestart + "' and '" + dateend + "' and o.order_number = g.order_number and pay_state != '2'" ) unpaidorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日订单总量今日销售总额", "errno": "4001" }), content_type="application/json") # 付款人数,付款订单数,付款件数,付款金额 try: cursor.execute( "select distinct count(o.user_addr),count(o.order_number),COALESCE(sum(g.goods_amount),0),COALESCE(sum(o.actual_price),0) from purchase_order as o,purchase_order_goods as g where manager_id = '" + manager_id + "' and o.order_state='1' and o.create_time bETWEEN '" + datestart + "' and '" + dateend + "' and o.order_number = g.order_number and o.pay_state ='1'" ) orderpaid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "昨日销售总额", "errno": "4001" }), content_type="application/json") # 有效订单数 try: cursor.execute( "select count(order_number) from purchase_order where manager_id = '" + manager_id + "' and order_state='1' and create_time bETWEEN '" + datestart + "' and '" + dateend + "' and pay_state ='1'") limitorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "七日销售总额", "errno": "4001" }), content_type="application/json") # 退款金额 try: cursor.execute( "select COALESCE(sum(refundFee),0) from apply_sale where orgAddr = '" + manager_id + "' and createdAt bETWEEN '" + datestart + "' and '" + dateend + "'") offshelfmerchandise = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已下架商品", "errno": "4001" }), content_type="application/json") # 客单价 try: cursor.execute( "select COALESCE(Round(sum(actual_price) / count(user_addr),2),0) from purchase_order where manager_id = '" + manager_id + "'and order_state='1'and pay_state = '1' and create_time bETWEEN '" + datestart + "' and '" + dateend + "'") customerunitprice = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "已下架商品", "errno": "4001" }), content_type="application/json") dict = { 'ordernum': to_string(unpaidorder[0][0]), # 下单人数 'ordernumber': unpaidorder[0][1], # 订单数 'lowerunitnum': to_string(unpaidorder[0][2]), # 下单件数 'orderamount': to_string(unpaidorder[0][3]), # 下单金额 'numpayments': orderpaid[0][0], # 付款人数 'numpaymentsorder': orderpaid[0][1], # 付款订单数 'numberpayments': to_string(orderpaid[0][2]), # 付款件数 'paymentsamount': to_string(orderpaid[0][3]), # 付款金额 'limitorder': limitorder[0][0], # 有效订单数 'offshelfmerchandise': to_string(offshelfmerchandise[0][0]), # 退款金额 'customerunitprice': to_string(customerunitprice[0][0]), # 客单价 'numbervisitors': 0, # 浏览人数 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # 新老客户交易构成 elif type == '5': if not all([year, month]): return HttpResponse(json.dumps({ "errmsg": 'year,month$参数缺失', "errno": 4001 }), content_type="application/json") import calendar try: monthRange = calendar.monthrange(eval(month), eval(month)) except: return HttpResponse(json.dumps({ "errmsg": "月份必须是1月-12月之间", "errno": 4001 }), content_type="application/json") import datetime try: startTime = datetime.datetime(eval(year), eval(month), 1).strftime("%Y-%m-%d") end = datetime.datetime( eval(year), eval(month), 1) + datetime.timedelta(monthRange[1] - 1) endTime = end.strftime("%Y-%m-%d") except: return HttpResponse(json.dumps({ "errmsg": "年份或者月份错误", "errno": 4001 }), content_type="application/json") # 新客户付款金额 新客户付款人数 try: cursor.execute( "select count(distinct user_addr),COALESCE(sum(actual_price),0) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time BETWEEN '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59' and pay_state ='1' and user_addr in (select id from jld_user where create_time BETWEEN '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59')") newclient = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "新客户", "errno": "4001" }), content_type="application/json") # 旧客户付款金额 旧客户付款人数 try: cursor.execute( "select count(distinct user_addr),COALESCE(sum(actual_price),0) from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and create_time BETWEEN '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59' and pay_state ='1' and user_addr not in (select id from jld_user where create_time BETWEEN '" + startTime + " 00:00:00' and '" + endTime + " 23:59:59')") oldclient = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "老客户", "errno": "4001" }), content_type="application/json") dict = { 'newclientmoney': to_string(newclient[0][1]), # 新客户付款人数 'newclientnum': newclient[0][0], # 新客户付款金额 'oldclientmonry': to_string(oldclient[0][1]), # 旧客户付款人数 'oldclientnum': oldclient[0][0] } # 旧客户付款金额 data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # 交易数据 elif type == '6': if not all([start_day, end_day]): return HttpResponse(json.dumps({ "errmsg": "时间未传", "errno": "4001" }), content_type="application/json") # 交易数据 try: cursor.execute( "select actual_price from purchase_order where manager_id = '" + manager_id + "' and order_state='1'and pay_state = '1'and create_time bETWEEN '" + datestart + "' and '" + dateend + "' GROUP BY order_number ORDER BY actual_price") transactiondata = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "交易数据", "errno": "4001" }), content_type="application/json") list1 = [i for i in transactiondata if 0 < i[0] < 51] list2 = [i for i in transactiondata if 51 <= i[0] < 101] list3 = [i for i in transactiondata if 101 <= i[0] < 201] list4 = [i for i in transactiondata if 201 <= i[0] < 501] list5 = [i for i in transactiondata if 501 <= i[0] < 1000] list6 = [i for i in transactiondata if 1001 <= i[0] < 5001] list7 = [i for i in transactiondata if 5001 <= i[0] < 10001] list8 = [i for i in transactiondata if i[0] > 10001] dict = { '0-50元': len(list1), '51-100元': len(list2), '101-200元': len(list3), '201-500元': len(list4), '501-1000元': len(list5), '1001-5000元': len(list6), '5001-10000元': len(list7), '10001元以上': len(list8), } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # 商品统计 elif type == '7': if not all([start_day, end_day, page, num]): return HttpResponse(json.dumps({ "errmsg": "商家地址未传", "errno": "4001" }), content_type="application/json") numberbars = int(num) pages = int(page) # 商品销售情况 提货数量 try: cursor.execute( "select g.goods_id ,sum(g.goods_amount) from take_order as o,take_order_goods as g where o.manager_id = '" + manager_id + "' and o.create_date bETWEEN '" + datestart + "' and '" + dateend + "' and o.order_number = g.order_number GROUP BY g.goods_id" ) takegoodsnum = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "提货数量", "errno": "4001" }), content_type="application/json") # 商品销售情况 销售数量 销售金额 try: cursor.execute( "select g.goods_id,sum(g.goods_amount),sum(o.actual_price) from purchase_order as o, purchase_order_goods as g where o.manager_id = '" + manager_id + "' and o.order_state = '1' and o.create_time bETWEEN '" + datestart + "' and '" + dateend + "' and g.order_number = o.order_number and o.pay_state = '1' GROUP BY g.goods_id" ) amountsales = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "销售数量 销售金额", "errno": "4001" }), content_type="application/json") # 商品销售情况 付款人数 订单号 try: cursor.execute( "select g.goods_id, count(DISTINCT o.user_addr) from purchase_order as o, purchase_order_goods as g where o.manager_id = '" + manager_id + "' and o.order_state = '1' and o.create_time bETWEEN '" + datestart + "' and '" + dateend + "' and g.order_number = o.order_number and o.pay_state = '1' GROUP BY g.goods_id" ) peopel = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "付款人数 订单号", "errno": "4001" }), content_type="application/json") setlist = set() for i in takegoodsnum: setlist.add(i[0]) for j in amountsales: setlist.add(j[0]) for x in peopel: setlist.add(x[0]) totalgoods = [] for naem in setlist: # 商品销售情况 商品id name try: cursor.execute("select id,name from goods where id = '" + naem + "'") idname = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "商品id_name", "errno": "4001" }), content_type="application/json") totalgoods.append(idname) splilist = [] for name in totalgoods: merchamt = { 'id': name[0][0], 'numpayments': 0, # 付款人数 'orgName': name[0][1], 'quantity': 0, # 提货数量 'salesvolumes': 0, # 销售数量 'saleamount': 0 } # 销售金额 splilist.append(merchamt) for takekey, take in enumerate(takegoodsnum): for key, id in enumerate(splilist): if take[0] == id['id']: splilist[key]['quantity'] = to_string( takegoodsnum[takekey][1]) break for buykey, take in enumerate(amountsales): for key, id in enumerate(splilist): if take[0] == id['id']: splilist[key]['salesvolumes'] = to_string( amountsales[buykey][1]) splilist[key]['saleamount'] = to_string( amountsales[buykey][2]) break for takekey, take in enumerate(peopel): for key, id in enumerate(splilist): if take[0] == id['id']: splilist[key]['numpayments'] = to_string( peopel[takekey][1]) break dict = { 'data': splilist[(pages - 1) * numberbars:pages * numberbars] } totalnum = len(splilist) dict['totalnum'] = totalnum data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") # 综合统计 elif type == '8': if not all([start_day, end_day]): return HttpResponse(json.dumps({ "errmsg": "时间未传", "errno": "4001" }), content_type="application/json") # 订单统计 # 销售总额 订单总量 try: cursor.execute( "select sum(actual_price),count(order_number) from purchase_order where manager_id = '" + manager_id + "' and order_state = '1' and create_time BETWEEN '" + datestart + "' and '" + dateend + "' and pay_state ='1'") totalsalveorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "销售总额 订单总量", "errno": "4001" }), content_type="application/json") # 退货总数 try: cursor.execute( "select sum(amount) from apply_sale where orgAddr = '" + manager_id + "' and createdAt BETWEEN '" + datestart + "' and '" + dateend + "'") totalreturn = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "退货总数", "errno": "4001" }), content_type="application/json") # 用户提货总量完成 try: cursor.execute( "select sum(g.goods_amount) from take_order as t, take_order_goods as g where t.manager_id = '" + manager_id + "' and t.order_number = g.order_number and t.take_state = '3' and t.create_date BETWEEN '" + datestart + "' and '" + dateend + "'") completedelivery = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "退货总数", "errno": "4001" }), content_type="application/json") # 退货总量 try: cursor.execute( "select sum(g.goods_amount) from take_order as t, take_order_goods as g where t.manager_id = '" + manager_id + "' and t.order_number = g.order_number and t.create_date BETWEEN '" + datestart + "' and '" + dateend + "'") take = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "退货总数", "errno": "4001" }), content_type="application/json") # 商品总量 try: cursor.execute( "select sum(circulation) from goods where foreign_key = '" + foreign_key + "' and is_delete = '0' and is_represent = '0' and putaway = '0' and create_time BETWEEN '" + datestart + "' and '" + dateend + "' and name is not null") totalshop = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "商品总量", "errno": "4001" }), content_type="application/json") totaluser = set() # 会员总数 try: cursor.execute( "select id,name ,initial_account from goods where foreign_key = '" + foreign_key + "' and name is not null") goodsid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "goodsid", "errno": "4001" }), content_type="application/json") for id in goodsid: # 会员总数 try: cursor.execute( "select userAddr from relation where goodsAddr = '" + id[0] + "' and createdAt BETWEEN '" + datestart + "' and '" + dateend + "'") usercount = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "会员总数", "errno": "4001" }), content_type="application/json") if usercount: for data in usercount: totaluser.add(data[0]) # 购买卡券用户 try: cursor.execute( "select count(DISTINCT user_addr) from purchase_order where manager_id = '" + manager_id + "' and order_state = '1' and create_time BETWEEN '" + datestart + "' and '" + dateend + "' and pay_state ='1' ") totaluserbuy = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "用户总数", "errno": "4001" }), content_type="application/json") goodsidset = set() total = 0 for info in goodsid: goodsidset.add(info[0][-2::]) try: cursor.execute( "select sum(amount) from transfer_record where goodsAddr = '" + info[0] + "' and createdAt BETWEEN '" + datestart + "' and '" + dateend + " '") result = cursor.fetchall() if result[0][0] is not None: total += result[0][0] except: return HttpResponse(json.dumps({ "errmsg": "转赠总数", "errno": "4001" }), content_type="application/json") # 领取卡券 receive = 0 for info in goodsidset: try: cursor.execute( "select sum(amount) from reviewrecord_" + info + " where type = '1' and createdAt BETWEEN '" + datestart + "' and '" + dateend + "' and goodsAddr in (select goodsAddr from goods where foreign_key = '" + foreign_key + "' and name is not null)") result = cursor.fetchall() if result[0][0] is not None: receive += result[0][0] except: return HttpResponse(json.dumps({ "errmsg": "领取卡券", "errno": "4001" }), content_type="application/json") # 卡券剩余 surplus = 0 for info in goodsidset: try: cursor.execute("SELECT sum(amount) from userbalance_" + info + " where userAddress = '" + goodsid[0][2] + "' and createdAt BETWEEN '" + datestart + "' and '" + dateend + "'") result = cursor.fetchall() if result[0][0] is not None: surplus += result[0][0] except: return HttpResponse(json.dumps({ "errmsg": "卡券剩余", "errno": "4001" }), content_type="application/json") # 购买卡券用户 buycard = set() for info in goodsidset: try: cursor.execute( "select toAddr from reviewrecord_" + info + " where type = '3' and createdAt BETWEEN '" + datestart + "' and '" + dateend + "' and goodsAddr in (select goodsAddr from goods where foreign_key = '" + foreign_key + "' and name is not null)") result = cursor.fetchall() if result: buycard.add(result[0][0]) except: return HttpResponse(json.dumps({ "errmsg": "购买卡券用户", "err no": "4001" }), content_type="application/json") dict = { 'totalsales': to_string(totalsalveorder[0][0]), # 销售总额 'totalorders': to_string(totalsalveorder[0][1]), # 订单总量 'totalreturn': to_string(totalreturn[0][0]), # 退货总量 'completedelivery': to_string(completedelivery[0][0]), # 提货总量完成 'take': to_string(take[0][0]), # 用户提货 'totalshop': to_string(totalshop[0][0]), # 商品总量 'totaluser': len(totaluser), # 用户总数 'totaluserbuy': totaluserbuy[0][0], # 用户购买总数 'totalnumcardcoupon': to_string(total), # 卡券转赠总数 'todaytotalviews': 0, # 今日总浏览量 'totacardreceipt': to_string(receive), # 卡券领取总量 'totalcardsurplus': to_string(surplus), # 卡券剩余总量 'buycard': len(buycard), # 购买卡券用户 } data = {'data': dict, "errmsg": "成功", "errno": "0"} return HttpResponse(json.dumps(data), content_type="application/json") else: return HttpResponse(json.dumps({ "errmsg": "type参数错误", "errno": "4001" }), content_type="application/json")
def funcdata(self): cursor = connections['default'].cursor() timedate = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S') time_stamp = int(time.time()) # 可提现 resultwithdrawal = 0 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' AND sale = '3' UNION all SELECT (COALESCE (sum(g.commission * g.goods_amount),0) - (SELECT COALESCE(sum(amount), 0)FROM withdrawals_record WHERE user_id = '" + self.data['userid'] + "' AND state IN ('1', '0'))) FROM purchase_order_goods AS g, purchase_order AS o WHERE g.affiliateID = '" + self.data['userid'] + "' AND o.pay_state = '1' AND o.pay_time IS NOT NULL and o.pay_time < '" + commission_price(time_stamp) + "' AND o.order_number = g.order_number) a") withdrawal = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "可提现4", "errno": "400" }), content_type="application/json") if withdrawal and withdrawal[0][0] > 0: resultwithdrawal = withdrawal[0][0] try: sumcommin = Endorsementuser.objects.filter( id=self.data['userid']) except Endorsementuser.DoesNotExist: return HttpResponse(status=404) # 如果已有信息 if sumcommin: userinfo = Endorsementuser.objects.get(id=self.data['userid']) userinfo.cash_withdrawal = withdrawal[0][0] userinfo.save() #如果没有 else: userinfo = UserData.objects.get(id=self.data['userid']) enduser = Endorsementuser() enduser.id = userinfo.id enduser.unionid = userinfo.unionid enduser.phone = userinfo.phone enduser.name = userinfo.name enduser.wx_nick_name = userinfo.wx_nick_name enduser.sex = userinfo.sex enduser.cash_withdrawal = withdrawal[0][0] enduser.create_time = timedate enduser.save() else: try: sumcommin = Endorsementuser.objects.filter( id=self.data['userid']) except Endorsementuser.DoesNotExist: return HttpResponse(status=404) # 如果已有信息 if sumcommin: userinfo = Endorsementuser.objects.get(id=self.data['userid']) userinfo.cash_withdrawal = 0 userinfo.save() # 如果没有 else: userinfo = UserData.objects.get(id=self.data['userid']) enduser = Endorsementuser() enduser.id = userinfo.id enduser.unionid = userinfo.unionid enduser.phone = userinfo.phone enduser.name = userinfo.name enduser.wx_nick_name = userinfo.wx_nick_name enduser.sex = userinfo.sex enduser.create_time = timedate enduser.save() # 冻结 resulthaw = 0 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' AND sale = '1' UNION all select COALESCE(sum(g.commission * g.goods_amount ), 0) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time > '" + commission_price(time_stamp) + "' and o.order_number = g.order_number) a") thaw = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "冻结", "errno": "400" }), content_type="application/json") if thaw and thaw[0][0] > 0: resulthaw = thaw[0][0] try: sumcommin = Endorsementuser.objects.filter( id=self.data['userid']) except Endorsementuser.DoesNotExist: return HttpResponse(status=404) # 如果已有信息 if sumcommin: userinfo = Endorsementuser.objects.get(id=self.data['userid']) userinfo.frozen = thaw[0][0] userinfo.save() # 如果没有 else: userinfo = UserData.objects.get(id=self.data['userid']) enduser = Endorsementuser() enduser.id = userinfo.id enduser.unionid = userinfo.unionid enduser.phone = userinfo.phone enduser.name = userinfo.name enduser.wx_nick_name = userinfo.wx_nick_name enduser.sex = userinfo.sex enduser.frozen = thaw[0][0] enduser.create_time = timedate enduser.save() else: try: sumcommin = Endorsementuser.objects.filter( id=self.data['userid']) except Endorsementuser.DoesNotExist: return HttpResponse(status=404) # 如果已有信息 if sumcommin: userinfo = Endorsementuser.objects.get(id=self.data['userid']) userinfo.frozen = 0 userinfo.save() # 如果没有 else: userinfo = UserData.objects.get(id=self.data['userid']) enduser = Endorsementuser() enduser.id = userinfo.id enduser.unionid = userinfo.unionid enduser.phone = userinfo.phone enduser.name = userinfo.name enduser.wx_nick_name = userinfo.wx_nick_name enduser.sex = userinfo.sex enduser.frozen = 0 enduser.create_time = timedate enduser.save() return HttpResponse(json.dumps({ 'data': { 'thaw': to_string(resulthaw), 'withdrawal': to_string(resultwithdrawal) }, "errmsg": "成功", "errno": "200" }), content_type="application/json")
def funcdata(self): cursor = connections['default'].cursor() time_stamp = int(time.time()) #带解冻佣金 try: cursor.execute( "select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(create_time)),@@SESSION .time_zone,'+08:00'),'%Y-%m-%d'),sum(price),sum(number) from (select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(create_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d') as create_time,COALESCE(sum(commission), 0) as price ,count(order_number) as number from goods_presell_order where sharer_id = '" + self.data['userid'] + "' and sale = '1' GROUP BY DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(create_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d') UNION select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(o.pay_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d')as create_time,COALESCE(sum(g.commission * g.goods_amount ), 0),count(g.order_number) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time > '" + commission_price(time_stamp) + "' and o.order_number = g.order_number GROUP BY DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(o.pay_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d')) a GROUP BY create_time ORDER BY create_time desc" ) data = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "带解冻佣金1", "errno": "400" }), content_type="application/json") dateresult = [] if data: for info in data: dateresult.append({ 'date': info[0], # 日期 'totalprice': float(to_string(info[1])), # 共计佣金 'totalnum': float(info[2]), # 共计订单数 'data': [], 'datetime': round(int(str_time_stamp_date(info[0])) * 1000), # 时间戳日期 }) try: cursor.execute( "select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(create_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d') ,unix_timestamp(create_time) as create_time,goods_amount,COALESCE((commission ), 0), (select name from goods where id = goods_id),(select main_photo from goods where id = goods_id) from goods_presell_order where sharer_id = '" + self.data['userid'] + "' and sale = '1' GROUP BY order_number UNION select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp(o.pay_time)), @@session.time_zone,'+08:00'),'%Y-%m-%d')as create_time,unix_timestamp(o.pay_time),g.goods_amount,COALESCE((g.commission * g.goods_amount ), 0), (select name from goods where id = g.goods_id),(select main_photo from goods where id = g.goods_id) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time > '" + commission_price(time_stamp) + "' and o.order_number = g.order_number GROUP BY g.order_number ORDER BY create_time desc" ) withthawing = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "带解冻佣金2", "errno": "400" }), content_type="application/json") result = [] if withthawing: for info in withthawing: result.append({ 'date': info[0], # 日期 'time': round(int(info[1]) * 1000), # 时间 'latestime': round(int(info[1] + 604800) * 1000), # 到期时间 'num': float(info[2]), # 件数 'commission': float(to_string(info[3])), # 佣金 'name': info[4], # 名字 'photo': info[5], # 图片 }) for date in dateresult: for timeinfo in result: if date['date'] == timeinfo['date']: date['data'].append(timeinfo) if self.data['token_active_data']: for date in result: if date['name'] == eval( self.data['token_active_data'])['goodsName']: date['latestime'] = int( eval(self.data['token_active_data'])['endTime']) return HttpResponse(json.dumps({ 'data': { "list": dateresult[ (int(self.data['pageno']) - 1) * int(self.data['pagesize']):int(self.data['pageno']) * int(self.data['pagesize'])] }, "errmsg": "成功", "errno": "200" }), content_type="application/json") else: return HttpResponse(json.dumps({ 'data': dateresult, "errmsg": "成功", "errno": "200" }), content_type="application/json")
def funcdata(self): cursor = connections['default'].cursor() time_stamp_index = int(time.time()) #今日购买订单量 cursor.execute( "SELECT sum(price) FROM (SELECT count(order_number) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' and create_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' AND sale in ('1','3') UNION all select count(g.order_number) from purchase_order as o, purchase_order_goods as g where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and o.order_number = g.order_number) a") todaybuyorder = cursor.fetchall() # 带解冻金额 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' AND sale = '1' UNION all select COALESCE(sum(g.commission * g.goods_amount ), 0) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time > '" + commission_price(time_stamp_index) + "' and o.order_number = g.order_number) a") daijiedong = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日购买订单量", "errno": "400" }), content_type="application/json") # 累计总收入 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' AND sale in ('1','3') UNION all select COALESCE(sum(g.commission * g.goods_amount), 0) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.order_number = g.order_number) a" ) numpayments = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "累计总收入1", "errno": "400" }), content_type="application/json") #今日佣金收入 todaycommission = 0 # 代言人的所有券地址 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' and create_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' AND sale in ('1','3') UNION all select COALESCE(sum(g.commission * g.goods_amount), 0) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and o.order_number = g.order_number) a") conponid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日佣金收入", "errno": "400" }), content_type="application/json") if conponid: todaycommission = conponid[0][0] # 最近一笔收入 lastincome = -1 lastdate = '1559530800' # 代言人的所有券地址 try: cursor.execute( "select COALESCE(commission, 0) ,unix_timestamp(create_time),create_time as create_time from goods_presell_order where sharer_id = '" + self.data['userid'] + "' and create_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and sale in ('1','3') UNION select COALESCE(g.commission * g.goods_amount, 0) ,unix_timestamp(o.pay_time), o.pay_time as create_time from purchase_order as o, purchase_order_goods as g where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '1' and o.pay_time is not null and o.pay_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and o.order_number = g.order_number ORDER BY create_time desc LIMIT 1" ) conponid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "最近一笔收入", "errno": "400" }), content_type="application/json") if conponid and conponid[0][1] is not None: lastdate = conponid[0][1] lastincome = conponid[0][0] # 今日退佣 retreatincome = 0 # 代言人的所有券地址 try: cursor.execute( "SELECT sum(price) FROM (SELECT COALESCE (sum(commission),0) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' and refund_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' AND sale ='2' UNION all select COALESCE(sum(g.commission * g.goods_amount), 0) from purchase_order_goods as g, purchase_order as o where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '2' and o.pay_time is not null and o.pay_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and o.order_number = g.order_number) a") conponid = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日退佣", "errno": "400" }), content_type="application/json") if conponid: retreatincome = conponid[0][0] # 今日退订单 try: cursor.execute( "SELECT sum(price) FROM (SELECT count(order_number) as price FROM goods_presell_order WHERE sharer_id = '" + self.data['userid'] + "' and refund_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' AND sale ='2' UNION all select count(g.order_number) from purchase_order as o, purchase_order_goods as g where g.affiliateID = '" + self.data['userid'] + "' and o.pay_state = '2' and o.pay_time is not null and o.pay_time between '" + time_stamp()['today'][0] + "' and '" + time_stamp()['today'][1] + "' and o.order_number = g.order_number) a") retreatorder = cursor.fetchall() except: return HttpResponse(json.dumps({ "errmsg": "今日退订单", "errno": "400" }), content_type="application/json") dict = { 'todaybuyorder': to_string(todaybuyorder[0][0]), # 今日订单量 'todaycommission': to_string(todaycommission), # 今日预计收入 'lastincome': to_string(lastincome), #最近一笔收入 'lastdate': int(round(int(lastdate) * 1000)), #最后收入时间 'retreatincome': to_string(retreatincome), #今日退佣 'retreatorder': to_string(retreatorder[0][0]), #今日退订单 'numpayments': to_string(numpayments[0][0]), #累计总收入 'jiedong': to_string(daijiedong[0][0]) #待解冻 } return HttpResponse(json.dumps({ 'data': dict, "errmsg": "成功", "errno": "200" }), content_type="application/json")