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