def syn_supplier_info(self, supplier_info): """同步供应商信息""" try: from brick.pydata.py_syn.py_conn import py_conn pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] select_sql = 'select count(1) from B_Supplier WHERE SupplierName=%s' sqlserver_cursor.execute(select_sql, (supplier_info['SupplierName'].strip(), )) count = sqlserver_cursor.fetchone() if count[0] > 0: result = { 'error_code': -1, 'error_info': u'普源已有此供应商: "%s"' % supplier_info['SupplierName'] } else: insert_sql = 'insert into B_Supplier(CategoryID, SupplierCode, SupplierName, FitCode, LinkMan, Address, ' \ 'OfficePhone, Mobile, Used, Recorder, InputDate, Modifier, ModifyDate, Email, QQ, MSN, ' \ 'ArrivalDays, URL, Memo, Account, CreateDate, SupPurchaser, supplierLoginId, paytype, SalerNameNew) ' \ 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' \ 'SELECT SCOPE_IDENTITY()' param = ( supplier_info['CategoryID'], supplier_info['SupplierCode'], supplier_info['SupplierName'], supplier_info['FitCode'], supplier_info['LinkMan'], supplier_info['Address'], supplier_info['OfficePhone'], supplier_info['Mobile'], supplier_info['Used'], supplier_info['Recorder'], supplier_info['InputDate'], supplier_info['Modifier'], supplier_info['ModifyDate'], supplier_info['Email'], supplier_info['QQ'], supplier_info['MSN'], supplier_info['ArrivalDays'], supplier_info['URL'], supplier_info['Memo'], supplier_info['Account'], supplier_info['CreateDate'], supplier_info['SupPurchaser'], supplier_info['supplierLoginId'], supplier_info['paytype'], supplier_info['SalerNameNew']) sqlserver_cursor.execute(insert_sql, param) exe_result = sqlserver_cursor.fetchone() return_id = int(exe_result[0]) conn_result['py_conn'].commit() result = {'error_code': 0, 'return_id': return_id} pyconn_obj.py_close_conn_database() except Exception, e: error_info = u'ex=%s; 供应商=%s; __LINE__=%s;' % ( e, sys._getframe().f_lineno, supplier_info['SupplierName']) result = {'error_code': -1, 'error_info': error_info}
def modify_py_purchaser(self, supplier_id, new_purchaser_id, category, modify_name, new_purchaser=None): """修改采购员""" from brick.pydata.py_syn.py_conn import py_conn pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] clothing = ('0|1|', '0|2|', '0|170|', '0|174|', '0|174|175|', '0|176|', '0|176|177|', '0|176|178|', '0|176|180|', '0|176|183|') result = {'error_code': 0} if not new_purchaser: sql1 = 'select PersonName from B_Person WHERE NID=%s' sqlserver_cursor.execute(sql1, (new_purchaser_id, )) info1 = sqlserver_cursor.fetchone() if info1: new_purchaser = info1[0] else: result = { 'error_code': -1, 'error_code': u'采购员ID: "%s"在普源不存在' % new_purchaser_id } if new_purchaser: if category == 0: sql2 = "update b_goods set Purchaser = \'%s\' WHERE SupplierID = %s AND GoodsStatus != '停售' AND CategoryCode in %s;" % ( new_purchaser, supplier_id, str(clothing)) else: sql2 = "update b_goods set Purchaser = \'%s\' WHERE SupplierID = %s AND GoodsStatus != '停售' AND CategoryCode not in %s;" % ( new_purchaser, supplier_id, str(clothing)) sqlserver_cursor.execute(sql2) sql3 = "update b_supplier set Suppurchaser=(select stuff((select '/'+ b.Purchaser from (select supplierid,purchaser from b_goods WHERE supplierid=%s and GoodsStatus != '停售' AND Purchaser != '' AND Purchaser is not null group by supplierid, purchaser) b for xml path('') ),1,1,'')), Modifier=%s, ModifyDate=GETDATE() WHERE nid=%s;" sqlserver_cursor.execute(sql3, (supplier_id, modify_name, supplier_id)) conn_result['py_conn'].commit() pyconn_obj.py_close_conn_database() return result
def online_sku_binding_add_puyuan(sku_link_data_list, opnum): """新增普源SKU绑定信息""" operation_log_obj = t_operation_log_online_syn_py(DBConn=connection) start_time = end_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') aNum = len(sku_link_data_list) rNum = 0 eNum = 0 operation_log_obj.update_banding_schedue(opnum, start_time, end_time, aNum, rNum, eNum) public_obj = public() pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] success_add_id_list = [] i = 0 for sku_link_data in sku_link_data_list: if sqlserver_cursor: b_goodsskulinkshop_data_list = get_param( sku_link_data=sku_link_data, opflag='add') result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list=b_goodsskulinkshop_data_list, sqlserverInfo=conn_result) if result['errorcode'] == 0: success_add_id_list.append(sku_link_data['id']) rNum += 1 else: eNum += 1 else: eNum += 1 operation_log_obj.update_banding_schedue(opnum, start_time, end_time, aNum, rNum, eNum) i += 1 if i == 100: b_goodsskulinkshop.objects.filter( NID__in=success_add_id_list).update(Falg=1) i = 0 success_add_id_list = [] b_goodsskulinkshop.objects.filter(NID__in=success_add_id_list).update( Falg=1) pyconn_obj.py_close_conn_database() end_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') endFlag = 1 operation_log_obj.update_banding_schedue(opnum, start_time, end_time, aNum, rNum, eNum, endFlag)
def modify_py_possessman2(self, possessman2_info): try: from brick.pydata.py_syn.py_conn import py_conn pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] sql = "update b_goods set PossessMan2 = %s WHERE SupplierID = %s; " sqlserver_cursor.execute( sql, (possessman2_info['PossessMan2'], possessman2_info['nid'])) conn_result['py_conn'].commit() pyconn_obj.py_close_conn_database() result = {'error_code': 0} except Exception, e: error_info = u'ex=%s; __LINE__=%s;' % (e, sys._getframe().f_lineno) result = {'error_code': -1, 'error_info': error_info}
def modify_py_supplier(self, supplier_info): """修改普源供应商信息""" try: from brick.pydata.py_syn.py_conn import py_conn pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] sql = 'update b_supplier set ' + supplier_info[ 'column_name'] + '=%s WHERE NID=%s; ' sqlserver_cursor.execute( sql, (supplier_info['column_value'], supplier_info['nid'])) conn_result['py_conn'].commit() pyconn_obj.py_close_conn_database() result = {'error_code': 0} except Exception, e: error_info = u'ex=%s; __LINE__=%s;' % (e, sys._getframe().f_lineno) result = {'error_code': -1, 'error_info': error_info}
def online_sku_binding_delete_puyuan(sku_link_data_list, opnum): """删除普源SKU绑定信息""" public_obj = public() operation_log_obj = t_operation_log_online_syn_py(DBConn=connection) pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] success_delete_id_list = [] success_delete_nid_list = [] for sku_link_data in sku_link_data_list: shop_sku = sku_link_data['shop_sku'] if sqlserver_cursor: b_goodsskulinkshop_data_list = get_param( sku_link_data=sku_link_data, opflag='delete') result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list=b_goodsskulinkshop_data_list, sqlserverInfo=conn_result) if result['errorcode'] == 0: status = 'over' error_info = str(b_goodsskulinkshop_data_list) success_delete_id_list.append(sku_link_data['id']) success_delete_nid_list.append(sku_link_data['link_id']) else: status = 'error' error_info = result['errortext'] else: status = 'error' error_info = u'普源数据库连接失败' ooResult = operation_log_obj.updateStatusP(opnum=opnum, opkey=shop_sku, status=status, elogs=error_info) assert ooResult['errorcode'] == 0, ooResult['errortext'] t_log_sku_shopsku_change.objects.filter( id__in=success_delete_id_list).update(Status='DELETED') b_goodsskulinkshop.objects.filter(NID__in=success_delete_nid_list).delete() pyconn_obj.py_close_conn_database()
def to_export_py(self, request, queryset): pyconn = py_conn() sqlserverinfo = pyconn.py_conn_database() try: result = {'errorcode': 0, 'errortext': ''} strAllSKU = "" data_list = [] for qs in queryset: #获取大类、小类名称 if qs.LRTime is None: qs.LRTime = fbaDateTime.now() b_goods_data = { 'GoodsCategoryID': '', 'CategoryCode': '', 'GoodsCode': qs.SKU, 'GoodsName': qs.Name2, 'ShopTitle': '', 'SKU': qs.SKU, 'BarCode': qs.BarCode, 'FitCode': '', 'MultiStyle': qs.MultiStyle, 'Material': qs.Material, 'Class': qs.Class, 'Model': qs.Model, 'Unit': qs.Unit, 'Style': qs.Style, 'Brand': qs.Brand, 'LocationID': '', 'Quantity': qs.Quantity, 'SalePrice': qs.SalePrice, 'CostPrice': qs.CostPrice, 'AliasCnName': qs.ReportName2, 'AliasEnName': qs.ReportName, 'Weight': qs.Weight, 'DeclaredValue': qs.DeclaredValue, 'OriginCountry': qs.OriginCountry, 'OriginCountryCode': qs.OriginCountryCode, 'ExpressID': '', 'Used': '', 'BmpFileName': '', 'BmpUrl': qs.BmpUrl, 'MaxNum': qs.MaxNum, 'MinNum': qs.MinNum, 'GoodsCount': '', 'SupplierID': '', 'Notes': qs.Remark, 'SampleFlag': qs.SampleFlag, 'SampleCount': qs.SampleCount, 'SampleMemo': '', 'CreateDate': str(qs.LRTime)[:10], 'GroupFlag': '', 'SalerName': qs.SalerName, 'SellCount': '', 'SellDays': qs.SellDays, 'PackFee': qs.InnerPrice, 'PackName': qs.PackingID, 'GoodsStatus': qs.GoodsStatus, 'DevDate': str(qs.LRTime)[:10], 'SalerName2': qs.SalerName2, 'BatchPrice': qs.BatchPrice, 'MaxSalePrice': qs.MaxSalePrice, 'RetailPrice': qs.RetailPrice, 'MarketPrice': qs.MarketPrice, 'PackageCount': qs.MinPackNum, 'ChangeStatusTime': '', 'StockDays': qs.StockDays, 'StoreID': '', 'Purchaser': qs.Purchaser, 'LinkUrl': qs.LinkUrl, 'LinkUrl2': qs.LinkUrl2, 'LinkUrl3': qs.LinkUrl3, 'StockMinAmount': qs.StockMinAmount, 'MinPrice': qs.MinPrice, 'HSCODE': qs.HSCODE, 'ViewUser': '', 'InLong': qs.InLong, 'InWide': qs.InWide, 'InHigh': qs.InHigh, 'InGrossweight': qs.InGrossweight, 'InNetweight': qs.InNetweight, 'OutLong': qs.OutLong, 'OutWide': qs.OutWide, 'OutHigh': qs.OutHigh, 'OutGrossweight': qs.OutGrossweight, 'OutNetweight': qs.OutNetweight, 'ShopCarryCost': qs.ShopFreight, 'ExchangeRate': qs.ExchangeRate, 'WebCost': '', 'PackWeight': qs.PackWeight, 'LogisticsCost': '', 'GrossRate': '', 'CalSalePrice': '', 'CalYunFei': '', 'CalSaleAllPrice': '', 'PackMsg': qs.PackMsg, 'ItemUrl': qs.ItemUrl, 'IsCharged': qs.Electrification, 'DelInFile': '', 'Season': qs.Season, 'IsPowder': qs.Powder, 'IsLiquid': qs.Liquid, 'possessMan1': qs.possessMan1, 'possessMan2': qs.possessMan2, 'LinkUrl4': qs.LinkUrl4, 'LinkUrl5': qs.LinkUrl5, 'LinkUrl6': qs.LinkUrl6, 'isMagnetism': '', 'NoSalesDate': '', 'NotUsedReason': '', 'PackingRatio': qs.DegreeOfDifficulty, 'shippingType': '', 'FreightRate': qs.LogisticsPrice, 'USEDueDate': '', 'SupplierName': qs.SupplierName, 'LargeCategoryName': qs.LargeCategory, 'SmallCategoryName': qs.SmallCategory, 'Storehouse': qs.Storehouse, 'ProductAttr': qs.ContrabandAttribute } strAllSKU = strAllSKU + qs.SKU + "," data_list.append(b_goods_data) from brick.table.t_operation_log_online_syn_py import t_operation_log_online_syn_py from django.db import connection operation_log_obj = t_operation_log_online_syn_py( DBConn=connection) param = {} # 操作日志的参数 param['OpNum'] = 'add_fbasku_%s_%s' % (fbaDateTime.now().strftime( '%Y%m%d%H%M%S'), request.user.username) param['OpKey'] = [ 'FBA_Main', ] param['OpType'] = 'FBA' param['Status'] = 'runing' param['ErrorInfo'] = "sucess syn_SKU:" + str( queryset.values_list("SKU", flat=True)) param['OpPerson'] = request.user.first_name param['OpTime'] = fbaDateTime.now() param['OpStartTime'] = fbaDateTime.now() param['OpEndTime'] = fbaDateTime.now() param['aNum'] = len(queryset) param['rNum'] = 0 param['eNum'] = 0 param['SKU'] = strAllSKU iResult = operation_log_obj.createLog(param) # 写入普源 public_obj = public() result = public_obj.sku_info_to_pydb(b_goods_data_list=data_list, sqlserverInfo=sqlserverinfo, mainsku=qs.MainSKU, pydb_connect=hqdbconn) if result['errorcode'] != 0 and result['errortext'].find( 'FBA') == -1: error_info = result['errortext'] messages.error(request, "同步普源错误,请联系开发人员;错误信息:%s" % (error_info)) operation_log_obj.update_error( param['OpNum'], "errorcode:" + str(result['errorcode']) + ";errortext:" + str(result['errortext'])) else: self.t_product_mainsku_sku_deal(request, queryset) if len(result['errortext']) > 0: messages.info( request, "已存在普源SKU:%s,不需要重复做同步,已更新为已录入。" % (result['errortext'])) else: # 将状态设置为已录入 messages.info(request, "选中商品已成功同步普源,并将当前数据更新为已录入。") operation_log_obj.update_success(param['OpNum'], param['ErrorInfo'], param['aNum'], 0) pyconn.py_close_conn_database() except Exception, ex: messages.error(request, "同步普源错误,请联系开发人员:%s,%s" % (Exception, ex)) operation_log_obj.update_error( param['OpNum'], str(Exception) + str(ex) + ";errorcode:" + str(result['errorcode']) + ";errortext:" + str(result['errortext'])) pyconn.py_close_conn_database()
# coding=utf-8 from brick.pydata.py_syn.public import public import pymssql from django.db import connection from skuapp.table.t_product_information_modify import t_product_information_modify from datetime import datetime from brick.table.t_operation_log_online_syn_py import t_operation_log_online_syn_py as operation_log from brick.pydata.py_syn.py_conn import py_conn pyconn_obj = py_conn() public_obj = public() operation_log_obj = operation_log(DBConn=connection) GOODSSTATUS_DICT = { u'清仓下架(需审核)': u'清仓', u'售完下架(需审核)': u'售完下架', u'处理库尾(需审核)': u'处理库尾', u'清仓(合并)': u'清仓(合并)', u'停售': u'停售', u'清仓(合并)(需审核)': u'清仓(合并)', u'停售(需审核)': u'停售', u'临时下架': u'临时下架', u'重新上架': u'正常', u'清仓下架(无需审核)': u'清仓', u'停售(无需审核)': u'停售', u'清仓': u'清仓', u'售完下架': u'售完下架' }
def online_sku_binding_modify_puyuan(sku_link_data_list, opnum): """修改普源SKU绑定信息""" public_obj = public() operation_log_obj = t_operation_log_online_syn_py(DBConn=connection) pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] for sku_link_data in sku_link_data_list: old_sku = sku_link_data['old_sku'] new_sku = sku_link_data['new_sku'] shop_sku = sku_link_data['shop_sku'] person_code = sku_link_data['person_code'] memo = sku_link_data['memo'] shop_name = sku_link_data['shop_name'] if sqlserver_cursor: delete_data_dict = { 'sku': old_sku, 'shop_sku': shop_sku, 'person_code': person_code, 'memo': memo, 'shop_name': shop_name } add_data_dict = { 'sku': new_sku, 'shop_sku': shop_sku, 'person_code': person_code, 'memo': memo, 'shop_name': shop_name } b_goodsskulinkshop_data_delete_list = get_param( sku_link_data=delete_data_dict, opflag='delete') b_goodsskulinkshop_data_add_list = get_param( sku_link_data=add_data_dict, opflag='add') delete_result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list= b_goodsskulinkshop_data_delete_list, sqlserverInfo=conn_result) if delete_result['errorcode'] == 0: add_result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list= b_goodsskulinkshop_data_add_list, sqlserverInfo=conn_result) if add_result['errorcode'] == 0: status = 'over' error_info = str(delete_data_dict) + str(add_data_dict) link_id = sku_link_data['id'] b_goodsskulinkshop.objects.filter(NID=link_id).update( Falg=3, SKU=new_sku) shop_code_list = (u'{}'.format( delete_data_dict['memo'])).split('-') if len(shop_code_list) >= 2: shopname = u'{}-{}'.format(shop_code_list[0], shop_code_list[1]) else: shopname = delete_data_dict['memo'] classshopsku_obj = classshopsku(db_conn=None, redis_conn=redis_coon, shopname=shopname) classshopsku_obj.setSKU(shopsku=shop_sku, sku=new_sku) else: status = 'error' error_info = add_result['errortext'] else: status = 'error' error_info = delete_result['errortext'] else: status = 'error' error_info = u'普源数据库连接失败' ooResult = operation_log_obj.updateStatusP(opnum=opnum, opkey=shop_sku, status=status, elogs=error_info) assert ooResult['errorcode'] == 0, ooResult['errortext'] pyconn_obj.py_close_conn_database()
def getHopeNum(self, productSKU): try: strSql = ''' with Goods as ( select gs.nid as GoodsSKUID,gs.SKU,g.Purchaser,gs.maxnum,gs.minnum,g.sellDays, gs.CostPrice,g.CostPrice as CostPrice1,gs.GoodsSKUStatus,g.LinkUrl5 from B_GoodsSKU(nolock) gs, B_Goods(nolock) g where g.NID=gs.GoodsID and g.used=0 ), /*采购未入库数*/--已完全入库订单商品 InStoreD as ( select om.NID as StockOrderID, m.StoreID, d.GoodsSKUID, sum(d.Amount) as Number from CG_StockInD(nolock) d inner join CG_StockInM(nolock) m on d.StockInNID = m.NID and m.StoreID=49 inner join Goods g on d.GoodsSKUID = g.GoodsSKUID left join CG_StockOrderM(nolock) om on m.StockOrder = om.BillNumber where m.CheckFlag = 1 and m.MakeDate > (GETDATE()-365) group by om.NID, m.StoreID, d.GoodsSKUID ), --未入库商品 UnInStore AS ( select d.GoodsSKUID, m.StoreID, SUM(case when (d.Amount - isnull(id.Number,0)) <= 0 then null else (d.Amount - isnull(id.Number,0)) end ) as UnInNum from CG_StockOrderD(nolock) d inner join Goods g on d.GoodsSKUID = g.GoodsSKUID left join CG_StockOrderM(nolock) m on d.StockOrderNID = m.NID left join InStoreD id on d.StockOrderNID = id.StockOrderID and d.GoodsSKUID = id.GoodsSKUID and id.StoreID=m.StoreID where m.MakeDate > (GETDATE()-365) and (m.CheckFlag = 1)--审核通过的订单 and (m.Archive = 0)--不统计归档订单 group by d.GoodsSKUID,m.StoreID ), UnPaiDNum as ( SELECT GoodsSKUID, SUM(SaleNum) AS UnPaiDNum,SUM(SalereNum) AS SalereNum, StoreID FROM ( SELECT gs.GoodsSKUID, SUM(ptd.L_QTY) AS SaleNum, SUM(case when pt.RestoreStock=-1 then ptd.L_QTY else 0 end) AS SalereNum,ISNULL(ptd.StoreID,0)AS StoreID FROM P_TradeDt(nolock) ptd inner join P_trade(nolock) pt on pt.NID=ptd.TradeNID inner join Goods gs on gs.SKU=ptd.SKU WHERE pt.FilterFlag <= 5 GROUP BY gs.GoodsSKUID,ISNULL(ptd.StoreID,0) UNION all SELECT gs.GoodsSKUID, SUM(ptdu.L_QTY) AS SaleNum, SUM(case when pt.RestoreStock=-1 then ptdu.L_QTY else 0 end) AS SalereNum, ISNULL(ptdu.StoreID,0) AS StoreID FROM P_TradeDtUn(nolock) ptdu inner join P_TradeUn(nolock) pt on pt.NID=ptdu.TradeNID inner join Goods gs on gs.SKU=ptdu.SKU WHERE pt.FilterFlag = 1 GROUP BY gs.GoodsSKUID,ISNULL(ptdu.StoreID,0) ) AS C GROUP BY GoodsSKUID,StoreID ), --调拨单保存之后的预期入库 StockChange as ( select cd.GoodsSKUID,sum(cd.amount) as hopenum,cm.StoreInID from KC_StockChangeD(nolock) cd inner join KC_StockChangeM(nolock) cm on cm.nid=cd.StockChangenid and cm.checkflag=0 and cm.StoreInID=49 inner join Goods g on cd.GoodsSKUID = g.GoodsSKUID where cm.MakeDate > (GETDATE()-45) group by cd.GoodsSKUID,cm.StoreInID ), --今天实际采购数量 PurchaseNum as ( select d.GoodsSKUID, sum(d.Amount) as Amount,max(m.makedate) as Makedate,max(AudieDate) as AudieDate, case min(m.checkflag) when 1 then '审核通过' when 3 then '作废' else '未审核' end as Checkflag from CG_StockOrderD(nolock) d inner join CG_StockOrderM(nolock) m on m.NID=d.StockOrderNID and m.StoreID=49 and m.makedate>=CONVERT(VARCHAR(20),GETDATE()-1,23)+' 16:40:00' group by d.GoodsSKUID ), Result as ( SELECT case when d.KcMaxNum=0 then g.maxnum else d.KcMaxNum end as 'KcMaxNum', case when d.KcMinNum=0 then g.minnum else d.KcMinNum end as 'KcMinNum', d.SellCount1, d.SellCount2, d.SellCount3, round((d.SellCount1/7.0+d.SellCount2/15.0+d.SellCount3/30.0)/3.00,2) as 'AvgDayNum', d.Number , d.ReservationNum, (d.Number-d.ReservationNum) as 'UseNumber', case when g.CostPrice<>0 then g.CostPrice else g.CostPrice1 end as 'CostPrice', d.Price, d.Money, isnull(u.UnInNum,0) as 'NotInStore', d.Number-d.ReservationNum+isnull(u.UnInNum,0)-ISNULL(up.UnPaiDNum,0) + isnull(sc.hopenum,0)+isnull(up.SaleReNum,0) as 'hopeUseNum', isnull(up.UnPaiDNum,0) as 'UnPaiDNum', case when (case when d.KcMaxNum>0 then d.KcMaxNum else g.MaxNum end) -(d.Number-d.ReservationNum+isnull(up.SaleReNum,0))+ (case when d.KcMinNum>0 then d.KcMinNum else g.MinNum end) -isnull(u.UnInNum,0)+ISNULL(up.UnPaiDNum,0)<0 then 0 else (case when d.KcMaxNum>0 then d.KcMaxNum else g.MaxNum end) -(d.Number-d.ReservationNum+isnull(up.SaleReNum,0))+ (case when d.KcMinNum>0 then d.KcMinNum else g.MinNum end) -isnull(u.UnInNum,0)+ISNULL(up.UnPaiDNum,0) end as 'SuggestNum', isnull((select max(DATEDIFF(DAY,Dateadd(hour,8,bb.ORDERTIME), GETDATE())) from P_TradeDtUn(nolock) aa left join P_TradeUn(nolock) bb on aa.TradeNID = bb.NID where bb.FilterFlag = 1 and aa.SKU=g.SKU),0) as 'MaxDelayDays', isnull(up.SaleReNum,0) as 'SaleReNum', (SELECT TOP 1 bsl.LocationName FROM B_StoreLocation bsl WHERE bsl.nid = isNull(bgs.LocationID,0) ) AS 'LocationName', IsNull(g.GoodsSKUStatus,'') as GoodsStatus,g.Purchaser, g.SKU,g.LinkUrl5,pnum.Amount,pnum.Checkflag,pnum.Makedate,pnum.AudieDate, round(d.Number * case when IsNull(g.CostPrice,0) <> 0 then g.CostPrice else IsNull(g.CostPrice1,0) end ,2) as 'AllCostPrice', round(cast(d.SellCount1 as float)/nullif(cast(d.SellCount2 as float)-cast(d.SellCount1 as float),0),2) as 'Ratio' From kc_Currentstock_ExcludeBlacklist d with(nolock) inner join Goods g on d.GoodsSKUID = g.GoodsSKUID and d.StoreID =49 left join UnInStore u on d.GoodsSKUID = u.GoodsSKUID and d.StoreID = u.StoreID left join UnPaiDNum up on up.GoodsSKUID = d.GoodsSKUID and d.StoreID = up.StoreID left join StockChange sc on sc.goodsskuid = d.goodsskuid and sc.storeinid = d.storeid left join B_GoodsSKULocation(nolock) bgs ON g.GoodsSKUID = bgs.GoodsSKUID AND bgs.StoreID =49 left join PurchaseNum pnum on d.GoodsSKUID = pnum.GoodsSKUID where g.SKU='%s' ) select KcMaxNum,KcMinNum,SellCount1,SellCount2,SellCount3, Number,ReservationNum,UseNumber,NotInStore,hopeUseNum,UnPaiDNum,SuggestNum, case when AvgDayNum=0.0 then case when hopeUseNum=0.0 then 0.0 when hopeUseNum <0.0 then -99999.0 else 99999.0 end else round(hopeUseNum/(AvgDayNum*case when Ratio is null then 1 when Ratio>2 then 2 when Ratio=0 then 0.1 else Ratio end),2) end as SaleDay, MaxDelayDays,SaleReNum,LocationName,hopeUseNum-SellCount2 as 'StockDiff15', Ratio,GoodsStatus,Purchaser,SKU,AllCostPrice, round(AvgDayNum*case when Ratio is null then 1 when Ratio>2 then 2 when Ratio=0 then 0.1 else Ratio end,2) as AvgDayNum, isnull(LinkUrl5,'无') as LinkUrl5,isnull(Amount,0) as Amount,isnull(Checkflag,' ') as Checkflag, Makedate, AudieDate from Result ; ''' % (productSKU) reslut = {'errorcode': -1, 'errortext': '', 'returnConent': ()} from brick.pydata.py_syn.py_conn import py_conn py_connObj = py_conn() sqlServerInfo = py_connObj.py_conn_database() if sqlServerInfo['errorcode'] == 0: sqlServerInfo['py_cursor'].execute(strSql) returnContent = sqlServerInfo['py_cursor'].fetchone() if returnContent: reslut['errorcode'] = 0 # 库存量、占有量、 未入库量、预计可用数量、未派单量、建议采购数量、可卖天数、日平均销量 reslut['returnConent'] = (returnContent[5], returnContent[6], returnContent[8], returnContent[9], returnContent[10], returnContent[11], returnContent[12], returnContent[22]) else: pass py_connObj.py_close_conn_database() except Exception, ex: messages.info( self.request, "获取库存量、占有量、 未入库量、预计可用数量、未派单量、建议采购数量、可卖天数失败,请联系IT解决:%s" % (str(ex))) py_connObj.py_close_conn_database()