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