def getGoodsList(shop_id,user_id):
sql='''SELECT
a.GoodsID,
a.Quantity,
b.SalePrice,
b.SetNum,
b.SetPrice,
IF(
a.Quantity >= b.SetNum,
b.SetPrice,
round(b.SalePrice * b.Discount, 2)
)AS DiscountPrice
FROM
tb_shoppingcart a
INNER JOIN tb_goodsinfo_s b ON a.GoodsID = b.GoodsID
INNER JOIN tb_shopinfo_s c ON c.ShopID = b.ShopID
WHERE
c.ShopID = %s
AND a.BuyerID = %s
AND a.IsSelected = 1 '''
result_set=db.engine.execute(sql,(shop_id,user_id))
arr=[]
for row in result_set:
row_map=row_map_converter(row)
arr.append(row_map)
return arr
def get_order_listss(token_type,user_info):
result={'code':1,'msg':'ok'}
try:
sql='''
select a.OrderNo,a.ShopID,a.BuyerID,a.SaleMoney,a.SubmitTime,a.SendTime,a.ConfirmTime,a.Freight,a.AddressID,a.SendAddress,a.Receiver,a.Phone,a.Remark,a.Status,a.PayStatus,a.UpdateTime
,b.GoodsID,b.BatchNo,b.SalePrice,b.Quantity,b.DiscountPrice,c.ShopName,
d.GoodsName,e.PhotoID,e.PhotoName,e.PhotoPath,e.ThumbnailPath,e.SortNo
from tb_order_s a
left join tb_orderdetail_s b on a.OrderNo=b.OrderNo
left join tb_shopinfo_s c on a.ShopID=c.ShopID
left join tb_goodsinfo_s d on d.GoodsID=b.GoodsID
left join tb_photo e on e.LinkID=d.GoodsID
where buyerid=%s
'''
result_set=db.engine.execute(sql,(user_info.buyer_id))
orders=[]
for row in result_set:
temp=row_map_converter(row)
order=sub_map(temp,['order_no','shop_id','shop_name','buyer_id','sale_money','submit_time','send_time','confirm_time',\
'freight','send_address','receiver','phone','remark','status','update_time'])
goods=sub_map(temp,['goods_id','batch_no','sale_price','quantity','discount_price','goods_name',\
'photo_id','photo_path','thumbnail_path','sort_no'])
order['goods']=goods
orders.append(order)
result['orders']=orders
except Exception,e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_order_list(token_type,user_info):
result={'code':1,'msg':'ok'}
try:
sql='''
select a.OrderNo,d.communityName,a.ShopID,a.BuyerID,a.SaleMoney,a.SubmitTime,a.SendTime,a.ConfirmTime,a.Freight,a.AddressID,a.SendAddress,a.Receiver,a.Phone,a.Remark,a.Status,a.PayStatus,a.UpdateTime,a.PayType,
c.ShopName
from tb_order_s a
left join tb_shopinfo_s c on a.ShopID=c.ShopID
left join tb_buyeraddress b on b.AddressID=a.AddressID
left join tb_community_m d on d.communityId=b.communityId
where a.BuyerID=%s order by a.SubmitTime desc
'''
sql_detail='''
select a.* ,b.GoodsName,
c.PhotoID ,c.PhotoName,c.PhotoPath,c.ThumbnailPath,c.SortNo
from tb_orderdetail_s a
left join tb_goodsinfo_s b on b.GoodsID=a.GoodsID
left join tb_photo c on c.LinkID=a.GoodsID and c.IsChecked=1 and c.IsVisable=1
where OrderNo=%s GROUP BY OrderNo
'''
result_set=db.engine.execute(sql,(user_info.buyer_id))
orders=[]
for row in result_set:
order_map=row_map_converter(row)
order_detail_result_set=db.engine.execute(sql_detail,(order_map['order_no']))
order_detail_arr=[]
for item in order_detail_result_set:
order_detail_arr.append(row_map_converter(item))
order_map['goods']=order_detail_arr
sqlc='''select count(*) as count from tb_comment where CommentType=1 and OrderNo=%s'''
#temp= db.engine.execute(sqlc,(order_map['order_no'])).fetchone()
c=Comment.query.filter_by(comment_type='1',order_no=order_map['order_no']).first()
if c:
order_map['isAppraise']=1
else:
order_map['isAppraise']=0
orders.append(order_map)
result['orders']=orders
except Exception, e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_latest_shop_goods():
result={'code':1,'msg':'ok'}
try:
data=request.get_json()
sql='''
SELECT g.GoodsID,g.GoodsName,g.SalePrice,g.SetPrice,g.SetNum,
round(g.SalePrice * g.Discount, 2) AS DisPrice,
IFNULL(p.ThumbnailPath,'./Content/images/web/nowprinting2.jpg') AS ThumbnailPath,
IFNULL(o.SaleQuantity,0) AS TotalSale
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
WHERE
g.ShopID = %s
and g.Status = 0 group by g.GoodsID
'''
order_by=data.get('order_by',None)
if order_by=='saleasc':
sql+=' order by TotalSale asc'
elif order_by=='saledesc':
sql+=' order by TotalSale desc'
elif order_by=='priceasc':
sql+=' order by SalePrice asc'
elif order_by=='pricedesc':
sql+=' order by SalePrice desc'
else:
sql+=' order by SalePrice,TotalSale desc'
sql+=' limit %s'
shop_id=str(data['shop_id'])
count=data.get('count',4)
result_set=db.engine.execute(sql,(shop_id,count))
arr=[]
for row in result_set:
temp=row_map_converter(row)
arr.append(temp)
result['goods']=arr
except Exception,e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_goods_by_id():
result = {'code': 1, 'msg': 'ok'}
try:
data = request.get_json()
sql = '''
'''
row = db.engine.execute(sql, (data['goods_id'])).fetchone()
if row:
result['goods'] = row_map_converter(row)
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_attention_shops(token_type, user_info):
result = {'code': 1, 'msg': 'ok'}
try:
sql = '''
select a.* ,b.*from tb_attention a,tb_shopinfo_s b where a.AttentionID=b.ShopID and a.AttentionType=0 and a.BuyerID=%s
'''
result_set = db.engine.execute(sql, (user_info.buyer_id))
shops = []
for row in result_set:
shops.append(row_map_converter(row))
result['shops'] = shops
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_goods_by_id():
result={'code':1,'msg':'ok'}
try:
data=request.get_json()
sql='''
SELECT g.GoodsID,g.GoodsName,g.SalePrice,g.SetPrice,g.SetNum,
round(g.SalePrice * g.Discount, 2) AS DisPrice,
IFNULL(p.ThumbnailPath,'./Content/images/web/nowprinting2.jpg') AS ThumbnailPath,
IFNULL(o.SaleQuantity,0) AS TotalSale
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
WHERE
g.GoodsID = %s
and g.Status = 0 '''
row=db.engine.execute(sql,(data['goods_id'])).fetchone()
if row:
result['goods_info']=row_map_converter(row)
p_sql='''select quantity as remains from tb_purchase_s where GoodsID=%s order by BatchNo Desc'''
_row=db.engine.execute(p_sql,(data['goods_id'])).fetchone()
if _row:
result['goods_info']['remains']=int(_row['remains'])
else :
result['goods_info']['remains']=0
except Exception, e:
current_app.logger.exception(e)
result['code']=0
result['msg']=e.message
def get_recommend_shop_for_home_page():
result = {'code': 1, 'msg': 'ok'}
try:
sql = '''
select * from tb_shopinfo_s where ShopName like %s and ShopID <>1
'''
result_set = db.engine.execute(sql, ('%远邦%'))
arr = []
for row in result_set:
temp = row_map_converter(row)
arr.append(temp)
result['recommend_shops'] = arr
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_shop_by_id():
result = {'code': 1, 'msg': 'ok'}
try:
data = request.get_json()
mktxzb = data.get('mktxzb', None)
mktyzb = data.get('mktyzb', None)
sql = '''
SELECT s.ShopName,s.ShopPhoto,s.ShopID,s.Email,s.ShopPhone,s.LinkMan,s.Mobile,
s.ShopProperty,s.OperatingStatus,s.IsSupportOnLinePay,
s.ShopAddress,s.SEOTitle,s.SEOKeyWord,s.SEOContent,s.mktxzb,s.mktyzb,s.xzb,s.yzb,
'''
if mktxzb and mktyzb:
sql = sql + 'ROUND(SQRT(POW(%s - s.mktxzb, 2) + POW(%s- s.mktyzb, 2))/1000,2) AS Distance,'
else:
sql = sql + " '' AS Distance,"
sql = sql + '''
IFNULL(v.VisitCount,0) AS VisitCount,
IFNULL(o.Quantity,0) AS SaleCount
FROM
tb_shopinfo_s s
LEFT JOIN (SELECT ShopID,sum(VisitCount) AS VisitCount FROM tb_visitcount_s GROUP BY ShopID) v ON s.ShopID = v.ShopID
LEFT JOIN (SELECT ShopID,COUNT(OrderNo) AS Quantity FROM tb_order_s GROUP BY ShopID) o ON s.ShopID = o.ShopID
WHERE
s.IsChecked = '2' and s.ShopID=%s
'''
if mktxzb and mktyzb:
row = db.engine.execute(
sql, (mktxzb, mktyzb, data['shop_id'])).fetchone()
else:
row = db.engine.execute(sql, (data['shop_id'])).fetchone()
if row:
result['shop_info'] = row_map_converter(row)
activities = Activity.query.filter_by(shop_id=data['shop_id'])
result['shop_info']['activities'] = result_set_converter(
activities)
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_attention_goods(token_type, user_info):
result = {'code': 1, 'msg': 'ok'}
try:
sql = '''
select a.*,b.* ,
c.PhotoID,c.PhotoName,c.PhotoPath,c.ThumbnailPath
from tb_attention a
inner join tb_goodsinfo_s b on a.AttentionID=b.GoodsID and a.AttentionType=3
left join tb_photo c on c.LinkID=b.GoodsID and c.IsChecked=1 and c.IsVisable=1
where BuyerID=%s GROUP BY b.GoodsID
'''
result_set = db.engine.execute(sql, (user_info.buyer_id))
goods = []
for row in result_set:
goods.append(row_map_converter(row))
result['goods'] = goods
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_most_discount_goods():
result = {'code': 1, 'msg': 'ok'}
try:
sql = '''
SELECT g.GoodsID,g.GoodsName,g.SalePrice,g.Discount,
round(g.SalePrice * g.Discount, 2) AS DisPrice,
IFNULL(p.ThumbnailPath,'./Content/images/web/nowprinting2.jpg') AS ThumbnailPath,
IFNULL(o.SaleQuantity,0) AS TotalSale
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
order by Discount asc limit 10
'''
result_set = db.engine.execute(sql)
arr = []
for row in result_set:
temp = row_map_converter(row)
arr.append(temp)
result['goods_infos'] = arr
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def GetGoodsListFromCart(shop_id,buyer_id,is_selected):
arr=[]
try:
sql='''
SELECT
a.GoodsID,
c.PhotoPath,
c.ThumbnailPath,
b.GoodsName,
b.SalePrice,
b.Discount,
a.Quantity,
a.IsSelected,
b.SetNum,
b.SetPrice,
IF (
a.Quantity >= b.SetNum,
b.SetPrice,
round(b.SalePrice * b.Discount, 2)
) AS DisPrice,
IF (
a.Quantity >= b.SetNum,
round(b.SetPrice * a.Quantity, 2),
round(
round(b.SalePrice * b.Discount, 2) * a.Quantity,
2
)
) AS Money,
IFNULL(d.Quantity, 0) AS SumQuantity
FROM
tb_shoppingcart a
INNER JOIN tb_goodsinfo_s b ON a.GoodsID = b.GoodsID
AND b.ShopID = %s
INNER JOIN tb_photo c ON b.GoodsID = c.LinkID
AND c.IsChecked = '1'
AND c.IsVisable = '1'
LEFT JOIN (
SELECT
GoodsID,
SUM(Quantity) AS Quantity
FROM
tb_purchase_s
GROUP BY
GoodsID
) d ON a.GoodsID = d.GoodsID
WHERE
a.BuyerID = %s
'''
if is_selected==1 or is_selected=='1':
sql+='and a.IsSelected=1 '
sql+='group by b.GoodsID ORDER BY a.CreateTime desc'
result_set=db.engine.execute(sql,(shop_id,buyer_id))
for row in result_set:
arr.append(row_map_converter(row))
except Exception,e:
current_app.logger.exception(e)
def getOrderCartList(user_id,address_id):
sql='''
SELECT tmp.BuyerID,tmp.ShopID,tmp.ShopName,tmp.HasAlipay,tmp.HasOnlineBank,tmp.sumTheShop,
IF (
tmp.Distance > tmp.FreeDistance,
CEIL(
tmp.Distance - tmp.FreeDistance
) * tmp.Freight +
IF (
tmp.sumTheShop < tmp.ExtraOrderAmount,
tmp.ExtraFreight,
0
),
0 + IF ( tmp.sumTheShop < tmp.ExtraOrderAmount, tmp.ExtraFreight, 0 )
) AS Freight
FROM (SELECT a.BuyerID,c.ShopID,
c.ShopName,
c.HasAlipay,
c.HasOnlineBank,
c.OrderAmount,
c.FreeDistance / 1000 AS FreeDistance,
c.Freight,
c.FarthestDistance / 1000 AS FarthestDistance,
ROUND(
SQRT(
POW(s.mktxzb - c.mktxzb, 2) + POW(s.mktyzb - 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 sumTheShop
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 tb_buyeraddress s ON s.BuyerID = a.BuyerID
AND s.AddressID = %s
WHERE
a.BuyerID = %s
AND a.IsSelected = '1'
GROUP BY
a.BuyerID,
c.ShopID,
c.ShopName,
c.HasAlipay,
c.HasOnlineBank,
c.OrderAmount,
FreeDistance,
c.Freight,
FarthestDistance,
Distance,
c.ExtraOrderAmount,
c.ExtraFreight ) tmp
'''
result_set=db.engine.execute(sql,(address_id,user_id))
arr=[]
for row in result_set:
row_map=row_map_converter(row)
arr.append(row_map)
return arr
def search_goods_by_page():
result = {'code': 1, 'msg': 'ok'}
try:
data = request.get_json()
page = data.get('page', 1)
page_size = data.get('page_size', 20)
order_by = data.get('order_by')
sql = '''
SELECT g.GoodsID,g.GoodsName,g.SalePrice,g.Discount,
round(g.SalePrice * g.Discount, 2) AS DisPrice,
IFNULL(p.ThumbnailPath,'./Content/images/web/nowprinting2.jpg') AS ThumbnailPath,
IFNULL(o.SaleQuantity,0) AS TotalSale
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
and GoodsName like %s limit %s,%s
'''
result_set = db.engine.execute(sql,
('%' + data['key_words'] + '%',
(page - 1) * page_size, page_size))
arr = []
for row in result_set:
temp = row_map_converter(row)
arr.append(temp)
result['goods_infos'] = arr
result['page'] = page
result['page_size'] = page_size
count_sql = '''
SELECT count(*) as total_count
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
and GoodsName like %s
'''
row = db.engine.execute(count_sql,
('%' + data['key_words'] + '%')).fetchone()
if row:
result['total_count'] = row['total_count']
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_home_page_shop_goods():
result = {'code': 1, 'msg': 'ok'}
try:
data = request.get_json()
buyer_id = data.get('buyer_id')
page_size = int(data.get('page_size', 10))
page = int(data.get('page', 1))
shop_goods_num = int(data.get('shop_goods_num', 10))
mktxzb = None
mktyzb = None
if data.get('mktxzb') and data.get('mktyzb'):
mktxzb = data['mktxzb']
mktyzb = data['mktyzb']
elif buyer_id:
buyer_address = BuyerAddress.query.filter_by(
buyer_id=buyer_id, is_default='1').first()
if buyer_address:
mktxzb = buyer_address.mktxzb
mktyzb = buyer_address.mktyzb
if mktxzb and mktyzb:
sql = '''
select shop.*,ROUND(SQRT(POW(%s - shop.mktxzb, 2) + POW(%s- shop.mktyzb, 2))/1000,2) AS Distance from tb_shopinfo_s shop where ShopID <>1 and IsChecked = '2' and Status='0' order by Distance limit %s,%s
'''
shops = db.engine.execute(sql, (mktxzb, mktyzb,
(page - 1) * page_size, page_size))
else:
sql = '''
select shop.* from tb_shopinfo_s shop where ShopID<>1 and IsChecked = '2' and Status='0' order by ShopID asc limit %s,%s
'''
shops = db.engine.execute(sql, ((page - 1) * page_size, page_size))
shop_arr = []
for shop in shops:
shop_temp = row_map_converter(shop)
temp_sql = '''
SELECT g.GoodsID,g.GoodsName,g.SalePrice,g.Discount,g.SetPrice,g.SetNum,
round(g.SalePrice * g.Discount, 2) AS DisPrice,
IFNULL(p.ThumbnailPath,'./Content/images/web/nowprinting2.jpg') AS ThumbnailPath,
IFNULL(o.SaleQuantity,0) AS TotalSale
FROM
tb_goodsinfo_s g
LEFT JOIN (
SELECT
sum(t.Quantity) AS SaleQuantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN tb_photo p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
where ShopID=%s and g.Status=0
order by Discount asc limit %s
'''
goods = db.engine.execute(temp_sql,
(shop_temp['shop_id'], shop_goods_num))
goods_arr = []
for good in goods:
good_temp = row_map_converter(good)
goods_arr.append(good_temp)
shop_temp['most_discount_goods'] = goods_arr
shop_arr.append(shop_temp)
result['shopinfos'] = shop_arr
result['page_size'] = page_size
result['page'] = page
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message
def get_shop_lists_by_page():
result = {'code': 1, 'msg': 'ok'}
try:
query = request.get_json()
page = query.get('page', 1)
page_size = query.get('count', 20)
shop_type = query.get('shop_type')
order_by = query.get('order_by')
mktxzb = query.get('mktxzb')
mktyzb = query.get('mktyzb')
sql = '''
SELECT s.ShopName,s.ShopPhoto,s.ShopID,s.Email,s.ShopPhone,s.LinkMan,s.Mobile,
s.ShopProperty,s.OperatingStatus,s.IsSupportOnLinePay,
s.ShopAddress,s.SEOTitle,s.SEOKeyWord,s.SEOContent,s.mktxzb,s.mktyzb,s.xzb,s.yzb,
'''
if mktxzb and mktxzb:
sql = sql + 'ROUND(SQRT(POW(%s - s.mktxzb, 2) + POW(%s- s.mktyzb, 2))/1000,2) AS Distance,'
else:
sql = sql + "'' AS Distance,"
sql = sql + '''
IFNULL(v.VisitCount,0) AS VisitCount,
IFNULL(o.Quantity,0) AS SaleCount
FROM
tb_shopinfo_s s
LEFT JOIN (SELECT ShopID,sum(VisitCount) AS VisitCount FROM tb_visitcount_s GROUP BY ShopID) v ON s.ShopID = v.ShopID
LEFT JOIN (SELECT ShopID,COUNT(OrderNo) AS Quantity FROM tb_order_s GROUP BY ShopID) o ON s.ShopID = o.ShopID
WHERE
s.IsChecked = '2' and s.Status='0'
AND (s.xzb is not null or s.xzb <> '')
AND (s.yzb is not null or s.yzb <> '')
AND s.ShopType LIKE %s
'''
if "saleasc" == order_by:
sql = sql + 'order by SaleCount asc'
elif "saledesc" == order_by:
sql = sql + 'order by SaleCount desc'
elif 'visitasc' == order_by:
sql = sql + 'order by VisitCount asc'
elif 'visitdesc' == order_by:
sql = sql + 'order by VisitCount desc'
elif 'distanceasc' == order_by:
sql = sql + 'order by Distance asc'
elif 'distancedesc' == order_by:
sql = sql + 'order by Distance desc'
else:
sql = sql + 'order by SaleCount desc'
sql = sql + ' limit %s,%s'
if mktxzb and mktyzb:
result_set = db.engine.execute(
sql, (mktxzb, mktyzb, '%' + shop_type + '%',
(page - 1) * page_size, page_size))
else:
result_set = db.engine.execute(sql,
('%' + shop_type + '%',
(page - 1) * page_size, page_size))
arr = []
for row in result_set:
temp = row_map_converter(row)
arr.append(temp)
count_sql = '''
select count(*) as total from tb_shopinfo_s s
LEFT JOIN (SELECT ShopID,sum(VisitCount) AS VisitCount FROM tb_visitcount_s GROUP BY ShopID) v ON s.ShopID = v.ShopID
LEFT JOIN (SELECT ShopID,COUNT(OrderNo) AS Quantity FROM tb_order_s GROUP BY ShopID) o ON s.ShopID = o.ShopID
WHERE
s.IsChecked = '2'
AND (s.xzb is not null or s.xzb <> '')
AND (s.yzb is not null or s.yzb <> '')
AND s.ShopType LIKE %s
'''
row = db.engine.execute(count_sql, ('%' + shop_type + '%')).fetchone()
if row:
result['total_count'] = row['total']
result['shops'] = arr
result['page'] = page
result['count'] = page_size
result['order_by'] = order_by
except Exception, e:
current_app.logger.exception(e)
result['msg'] = e.message
result['code'] = 0
def send_email_2_shop(shop_id,order_no):
try:
order_sql='''
SELECT
o.OrderNo,
o.SubmitTime,
o.SendTime,
o.ConfirmTime,
o.Freight,
o.Receiver,
o.SendAddress,
o.Phone,
o.Remark,
(o.SaleMoney + o.Freight) AS SaleMoney,
o.ShopID,
b.Account,
b.NickName,
s.ShopName,
s.ShopPhone,
s.LinkMan,
s.Mobile,
s.ShopAddress,
s.Email,
c.ItemName AS Status
FROM
tb_order_s o
LEFT JOIN tb_shopinfo_s s on o.ShopID = s.ShopID
LEFT JOIN TB_BUYER b on b.BuyerID = o.BuyerID
LEFT JOIN TB_CONSTENT_M c on c.TypeID = '009' and o.Status = c.ItemID
WHERE
o.OrderNo = %s
'''
order_row=db.engine.execute(order_sql,order_no).fetchone()
order=row_map_converter(order_row)
detail_sql='''
SELECT
o.OrderNo,
o.SalePrice,
o.DiscountPrice,
SUM(o.Quantity) AS Quantity,
o.GoodsID,
g.GoodsName,
g.ShopID,
p.ThumbnailPath AS PhotoPath
FROM
TB_GOODSINFO_S g,
TB_ORDERDETAIL_S o
INNER JOIN TB_PHOTO p ON p.LinkID = o.GoodsID
AND p.IsVisable = 1 AND p.IsChecked = 1
WHERE
o.OrderNo = %s
AND o.GoodsID = g.GoodsID
GROUP BY
o.OrderNo,
o.SalePrice,
o.DiscountPrice,
o.GoodsID,
g.GoodsName,
PhotoPath '''
order_details= db.engine.execute(detail_sql,order_no)
arr=[]
goodIds=[]
for row in order_details:
temp_order=row_map_converter(row)
if goodIds.index(temp_order['goods_id'])>0:
continue
else:
goodIds.append(temp_order['goods_id'])
arr.append(temp_order)
order['order_detail']=arr
temp_template=string.Template(''' 尊敬的${shop_name}店主：<br /> 您的店铺有新的订单，订单编号为:${order_no}
<table width='100%' border='1' cellspacing='0' cellpadding='5' style='margin-bottom:5px;' bgcolor='#FFFFFF'>
<tbody>
<tr>
<td width='80' class='biaoti' >收货人：
${reciever}
</td>
<td width='80' class='biaoti' >联系电话：
${phone}
</td>
<td width='80' class='biaoti' >配送地址：
${send_address}
</td>
</tr>
</tbody>
</table>
<table width='100%' border='1' cellspacing='0' cellpadding='5' class='ordergl ordersearch'>
<tbody>
<tr>
<td width='280' class='biaoti' align='center' colspan='2'>商品</td>
<td width='70' class='biaoti'>单价(元)</td>
<td width='50' height='26' class='biaoti'>数量</td>
<td width='120' class='biaoti'>合计(元)</td>
</tr>
''')
mail_body=temp_template.substitute(shop_name=order['shop_name'],order_no=order['order_no'],reciever=order['receiver'],phone=order['phone'],send_address=order['send_address'])
temp_template=''
i=0
for order_detail in order['order_detail']:
temp_template=string.Template('''<tr>
<td width='80' class='orderpic hang'><a href='${base_url}/Display/ShopGoodsInfoPage?ShopID=${shop_id}&GoodsID=${goods_id}'><img src='${base_url}/${photo_path}' width='80px' height='80px' /></a></td>
<td width='200' class='hang' style='text-align: left'><a href='${base_url}/Display/ShopGoodsInfoPage?ShopID=${shop_id}&GoodsID=${goods_id}'>${goods_name}</a></td>
<td width='70' class='hang'><s>${sale_price}</s><br/>${discount_price}</td>
<td width='50' class='hang'>${quantity}</td>" ''')
mail_body+=temp_template.substitute(base_url='http://www.yuanbangshop.com',shop_id=order['shop_id'],goods_id=order_detail['goods_id'],photo_path=order_detail['photo_path'],goods_name=order_detail['goods_name'],sale_price=order_detail['sale_price'],discount_price=order_detail['discount_price'],quantity=order_detail['quantity'])
if i==0:
temp_template=string.Template(''' <td width='120' class='rowspan' rowspan='${count}'>${sale_money}<br/>(含运费：${freight})</td>''')
mail_body+=temp_template.substitute(count=len(order['order_detail']),sale_money=order['sale_money'],freight=order['freight'])
i=i+1
mail_body+='</tr>'
mail_body+='''</tbody>
</table><table width='100%' border='1' cellspacing='0' cellpadding='5' style='margin-bottom:5px;' bgcolor='#FFFFFF'><tbody>
<tr><td width='80' class='biaoti' >备注:'''
if order['remark']:
mail_body+=order['remark']
else:
mail_body+="无"
param=base64.encodestring(order['email']+order['shop_name'])
temp_template=string.Template('''</td></tr></tbody></table>
请点击下列链接进行操作。<a href='${base_url}/ShopCenterManage/OrderListPage?p=${param}'>${base_url}/ShopCenterManage/OrderListPage?p=${param}</a>
<br>(如果上面不是链接形式，请将地址手工粘贴到浏览器地址栏再访问)<br><br>此邮件为系统邮件，请勿直接回复''')
mail_body+=temp_template.substitute(base_url='http://www.yuanbangshop.com',param=param)
send_mail([order['email']], '[远邦邻里网] 订单提醒邮件', mail_body)
except Exception,e:
current_app.logger.exception(e)
def search_goods_by_page_ex_(data):
result = {'code': 1, 'msg': 'ok'}
try:
mktxzb = data.get('mktxzb', None)
mktyzb = data.get('mktyzb', None)
page = data.get('page', 1)
page_size = data.get('page_size', 10)
order_by = data.get('order_by', '')
sql = '''
SELECT g.ShopID,g.GoodsID,g.GoodsName,g.SalePrice,round(g.SalePrice * g.Discount, 2) AS DisPrice,
p.ThumbnailPath,
IFNULL(o.Quantity, 0) AS Quantity,
s.ShopName,s.ShopPhoto,s.mktxzb,s.mktyzb,s.xzb,s.yzb,
'''
if mktxzb and mktyzb:
sql += 'ROUND(SQRT(POW(%s - s.mktxzb, 2) + POW(%s- s.mktyzb, 2))/1000,2) AS Distance '
else:
sql += ''' 0 as Distance '''
sql += '''
FROM TB_GOODSINFO_S g
INNER JOIN tb_shopinfo_s s ON s.ShopID = g.ShopID
AND s.IsChecked = '2' and s.Status='0'
AND (
s.ShopType IS NOT NULL
OR s.ShopType <> ''
)
AND (s.xzb IS NOT NULL OR s.xzb <> '')
AND (s.yzb IS NOT NULL OR s.yzb <> '')
LEFT JOIN (
SELECT
sum(t.Quantity) AS Quantity,
t.GoodsID
FROM
tb_order_s d,
tb_orderdetail_s t
WHERE
d.OrderNo = t.OrderNo
AND d.`Status` <> '3'
GROUP BY
t.GoodsID
) o ON g.GoodsID = o.GoodsID
INNER JOIN TB_PHOTO p ON g.GoodsID = p.LinkID
AND p.IsVisable = '1'
AND p.IsChecked = '1'
LEFT JOIN TB_GOODSTYPE_M m on m.GoodsTypeID = g.GoodsTypeID
WHERE g.Status = 0
AND (g.GoodsName LIKE %s
OR g.GoodsLocality LIKE %s
OR g.GoodsBrand LIKE %s
OR g.GoodsSpec LIKE %s
OR m.GoodsTypeName LIKE %s
OR g.Remark LIKE %s)
'''
if order_by == 'saleasc':
sql += 'ORDER BY IFNULL(o.Quantity,0) asc'
elif order_by == 'saledesc':
sql += 'ORDER BY IFNULL(o.Quantity,0) desc '
elif order_by == 'distancedesc':
sql += 'ORDER BY Distance desc '
elif order_by == 'distanceasc':
sql += 'ORDER BY Distance asc'
elif order_by == 'pricedesc':
sql += 'ORDER BY round(g.SalePrice * g.Discount, 2) desc '
elif order_by == 'priceasc':
sql += 'ORDER BY round(g.SalePrice * g.Discount, 2) asc'
else:
sql += 'ORDER BY Distance asc'
sql += ' limit %s,%s'
search_words = '%' + data['key_words'] + '%'
if mktxzb and mktyzb:
result_set = db.engine.execute(
sql, (mktxzb, mktyzb, search_words, search_words, search_words,
search_words, search_words, search_words,
(page - 1) * page_size, page_size))
else:
result_set = db.engine.execute(
sql, (search_words, search_words, search_words, search_words,
search_words, search_words,
(page - 1) * page_size, page_size))
goods = []
for row in result_set:
temp = row_map_converter(row)
goods.append(temp)
result['goods'] = goods
result['page'] = page
result['page_size'] = page_size
except Exception, e:
current_app.logger.exception(e)
result['code'] = 0
result['msg'] = e.message