def get_preview_orders_by_shopcart(token_type,user_info):
result={'code':1,'msg':'ok'}
try:
data=request.get_json()
buyer_address=BuyerAddress.query.filter_by(buyer_id=user_info.buyer_id,is_default='1').first()
xzb=None
yzb=None
mktxzb=None
mktyzb=None
if buyer_address:
xzb=buyer_address.xzb
yzb=buyer_address.yzb
mktxzb=buyer_address.mktxzb
mktyzb=buyer_address.mktyzb
is_selected=data.get('is_selected',None)
shop_list=GetShopListFromCart(mktxzb, mktyzb, user_info, is_selected)
temp=[]
for shop in shop_list:
if not str(shop['shop_id']).isdigit():
continue
temp.append(shop)
##判断用户是否是第一次下单
orders= Order.query.filter_by(buyer_id=user_info.buyer_id,shop_id=shop['shop_id']).first()
if orders:
shop['first_order']=False
else:
shop['first_order']=True
#获取已经有的商铺优惠券
sql='''
SELECT
IFNULL(
sum(
CASE
WHEN CouponType = '0' THEN
CouponMoney
ELSE
-CouponMoney
END
),
0
) AS CouponMoney
FROM
tb_coupon
WHERE
ShopID = %s
AND BuyerID =%s'''
shopCoupons = db.engine.execute(sql,(shop['shop_id'], user_info.buyer_id))
shop['shopCoupons']=rows_array_converter(shopCoupons)
goods=GetGoodsListFromCart(shop['shop_id'], user_info.buyer_id, is_selected)
shop['goods']=goods
result['orders']=temp
except Exception,e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_orders_count(token_type,user_info):
result={'code':1,'msg':'ok'}
try:
sql="select count(OrderNo) as count,status from tb_order_s where BuyerID='%s' group by status";
rows= db.engine.execute(sql,(user_info.buyer_id))
result['orders_counts']=rows_array_converter(rows)
except Exception,e:
result['msg']=e.message
def get_all_my_coupons(token_type,buyer):
result={'code':1,'msg':'ok'}
try:
sql='''
SELECT
c.ShopID,
s.ShopName,
IFNULL(
sum(
CASE
WHEN CouponType = '0' THEN
CouponMoney
ELSE
- CouponMoney
END
),
0
) AS CanUseCouponSum,
IFNULL(
sum(
CASE
WHEN CouponType = '0' THEN
CouponMoney
ELSE
0
END
),
0
) AS GetCouponSum,
IFNULL(
sum(
CASE
WHEN CouponType = '1' THEN
CouponMoney
ELSE
0
END
),
0
) AS UsedCouponSum
FROM
tb_coupon c
INNER JOIN tb_shopinfo_s s on s.ShopID = c.ShopID
WHERE
BuyerID = %s
GROUP BY c.ShopID,s.ShopName
'''
rows=db.engine.execute(sql,(buyer.buyer_id))
r=rows_array_converter(rows)
r.reverse()
result['coupons']=r
except Exception,e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_delivery_list_counts(token_type,user_info):
result = {'code':1,'msg':'ok'}
try:
sql='select count(DeliveryStatus) as counts,DeliveryStatus from tb_deliverylist where BuyerID=%s group by DeliveryStatus '
rows=db.engine.execute(sql,(user_info.buyer_id))
result['delivery_list_counts']=rows_array_converter(rows)
except Exception as e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
return Response(json.dumps(result), content_type='application/json')
def get_communities_by_xyzb(token_type, user_info):
result = {'code': 1, 'msg': 'ok'}
data = request.json
mktxzb = data.get('mktxzb', None)
mktyzb = data.get('mktyzb', None)
try:
sql = '''select c.communityId,c.communityName,ROUND(SQRT(POW(%s - c.mktxzb, 2) + POW(%s- c.mktyzb, 2)),2) AS Distance from tb_community_m c where ROUND(SQRT(POW(%s - c.mktxzb, 2) + POW(%s- c.mktyzb, 2)),2)<=500 order by Distance'''
if mktyzb and mktxzb:
rows = db.engine.execute(sql, (mktxzb, mktyzb, mktxzb, mktyzb))
result['communities'] = rows_array_converter(rows)
else:
result['communities'] = []
except Exception, e:
current_app.logger.exception(e)
result['msg'] = e.message
result['code'] = 0
def query_coupons_by_shop_id(token_type,buyer):
result={'code':1,'msg':'ok'}
try:
data=request.get_json()
sql='''SELECT
s.ShopName,
CASE c.CouponType
WHEN '0' THEN
'收入'
WHEN '1' THEN
'支出'
END AS CouponTypeName,
c.OperateTime,
c.OrderNO,
c.ShopID,
CASE c.CouponType
WHEN '0' THEN
c.CouponMoney
WHEN '1' THEN
-c.CouponMoney
END AS CouponMoney,
c.Remark
FROM
tb_coupon c
LEFT JOIN tb_shopinfo_s s ON c.ShopID = s.ShopID
WHERE c.BuyerID = %s
AND c.ShopID = %s '''
rows=db.engine.execute(sql,(buyer.buyer_id,data['shop_id']))
r=rows_array_converter(rows)
r.reverse()
rr=[]
for temp in r:
if temp["coupon_money"]!=0:
rr.append(temp)
result['coupons']=rr
except Exception,e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def GetShopListFromCart(mktxzb,mktyzb,user_info,is_selected):
arr=[]
try:
if mktxzb and mktyzb:
sql='''
SELECT
tmp.ShopID,
tmp.ShopName,
tmp.OrderAmount,
tmp.FreeDistance,
tmp.FarthestDistance,
tmp.Distance,
tmp.Freight as BaseFreight,
tmp.ExtraOrderAmount,
tmp.ExtraFreight,
tmp.Money,
tmp.CouponMoney,
IFNULL(tmp.OrderCount,0) as OrderCount,
tmp.FirstGiveCoupon,
tmp.GiveLimit,
tmp.GiveCoupon,
tmp.UseLimit,
tmp.CanUseCoupon,
IF (
tmp.Distance > tmp.FreeDistance,
CEIL(
tmp.Distance - tmp.FreeDistance
) * tmp.Freight + IF(tmp.Money < tmp.ExtraOrderAmount,tmp.ExtraFreight,0),
0 + IF(tmp.Money < tmp.ExtraOrderAmount,tmp.ExtraFreight,0)
) AS Freight
FROM (
SELECT
c.ShopID,
c.ShopName,
c.OrderAmount,
c.FreeDistance / 1000 AS FreeDistance,
c.Freight,
c.FarthestDistance / 1000 AS FarthestDistance,
ROUND(
SQRT(
POW(%s - c.mktxzb, 2) + POW(%s - c.mktyzb, 2)
) / 1000,
2
) AS Distance,
c.ExtraOrderAmount,
c.ExtraFreight,
sum(
IF (
a.Quantity >= b.SetNum,
round(b.SetPrice * a.Quantity, 2),
round(
round(b.SalePrice * b.Discount, 2) * a.Quantity,
2
)
)
) AS Money,
sum(
case when b.CanGetCoupon = 1 then
IF (
a.Quantity >= b.SetNum,
round(b.SetPrice * a.Quantity, 2),
round(
round(b.SalePrice * b.Discount, 2) * a.Quantity,
2
)
)
else 0 end
) AS CouponMoney,
d.OrderCount,
c.FirstGiveCoupon,
c.GiveLimit,
c.GiveCoupon,
c.UseLimit,
c.CanUseCoupon
FROM
tb_shoppingcart a
LEFT JOIN tb_goodsinfo_s b ON a.GoodsID = b.GoodsID
LEFT JOIN tb_shopinfo_s c ON b.ShopID = c.ShopID
LEFT JOIN (select count(1) as OrderCount,ShopID,BuyerID from tb_order_s group by ShopID,BuyerID) d
on d.ShopID = c.ShopID and d.BuyerID = a.BuyerID
WHERE
a.BuyerID = %s
'''
if int(is_selected)==1:
sql=sql+" and a.IsSelected=1 "
sql=sql+'''GROUP BY
c.ShopID,
c.ShopName,
c.OrderAmount,
FreeDistance,
c.Freight,
FarthestDistance,
Distance,
c.ExtraOrderAmount,
c.ExtraFreight) tmp'''
result_set=db.engine.execute(sql,(mktxzb,mktyzb,user_info.buyer_id))
arr=rows_array_converter(result_set)
else:
sql='''
SELECT
c.ShopID,
c.ShopName,
c.OrderAmount,
c.FreeDistance / 1000 AS FreeDistance,
c.Freight,
c.FarthestDistance / 1000 AS FarthestDistance,
c.ExtraOrderAmount,
c.ExtraFreight,
sum(
IF (
a.Quantity >= b.SetNum,
round(b.SetPrice * a.Quantity, 2),
round(
round(b.SalePrice * b.Discount, 2) * a.Quantity,
2
)
)
) AS Money,
c.FirstGiveCoupon,
c.GiveLimit,
c.GiveCoupon,
c.UseLimit,
c.CanUseCoupon,
0 as CouponMoney
FROM
tb_shoppingcart a
LEFT JOIN tb_goodsinfo_s b ON a.GoodsID = b.GoodsID
LEFT JOIN tb_shopinfo_s c ON b.ShopID = c.ShopID
WHERE
a.BuyerID = %s'''
if int(is_selected)==1:
sql=sql+" and a.IsSelected=1 "
else:
sql=sql+''' GROUP BY
c.ShopID,
c.ShopName,
c.OrderAmount,
FreeDistance,
c.Freight,
FarthestDistance,
c.ExtraOrderAmount,
c.ExtraFreight'''
result_set=db.engine.execute(sql,(user_info.buyer_id))
arr=rows_array_converter(result_set)
print "==============================="
current_app.logger.info("==================")
for row in arr:
if int(row['use_limit'])==0:
row['coupon_money']='0.00'
else:
row['coupon_money']=int(float(row['money'])/float(row['use_limit']))*float(row['can_use_coupon'])
row['coupon_money']=str(row['coupon_money'])
except Exception,e:
current_app.logger.exception(e)
def get_delivery_list_by_page(token_type, buyer):
result = {'code': 1, 'msg': 'ok'}
data = request.get_json()
try:
page = data.get('page', 1)
page_size = data.get('page_size', 20)
order_no = data.get('order_no', None)
sql = '''SELECT d.id,
d.SubmitTime,
d.OrderNo,
s.ShopName,
s.ShopPhone,
b.Phone as BuyerPhone,
IFNULL(
IF (
b.NickName = '',
NULL,
b.NickName
),
b.Account
) AS BuyerName,
b.Phone,
b.Avatar,
(o.SaleMoney + o.Freight) AS orderMoney,
o.Freight,
o.SendAddress,
o.Receiver,
o.phone,
o.Remark as OrderRemark,
o.PayStatus,
d.DeliveryMoney,
c.ItemName,
o.PayType,
d.DeliveryStatus,
CONCAT('+',(o.SaleMoney + o.Freight)-d.DeliveryMoney) as giveMoney
FROM
tb_deliverylist d
LEFT JOIN tb_buyer b ON b.BuyerID = d.BuyerID
LEFT JOIN tb_constent_m c ON c.ItemID = d.DeliveryStatus
INNER JOIN tb_order_s o ON o.OrderNo = d.OrderNo
AND c.TypeID = '021'
Left join tb_shopinfo_s s on s.ShopID=o.ShopID
WHERE
d.BuyerID = %s'''
values = []
values.append(buyer.buyer_id)
if order_no:
sql += ' and d.OrderNo=%s'
values.append(order_no)
sql += ' ORDER BY d.ReceiveTime DESC limit %s , %s'
values.append((page - 1) * page_size)
values.append(page_size)
rows = db.engine.execute(sql, tuple(values))
result['delivery_list'] = rows_array_converter(rows)
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
