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