def DealGoodsStatus(self):
try:
reload(sys)
sys.setdefaultencoding('utf8')
dicConfig = self.getStatusConfig()
self.connSql(10)
strSql = "select sku,goodsstatus from b_goods(nolock)"
n = self.sqlcursor_py.execute(strSql)
tupResult = self.sqlcursor_py.fetchall()
self.closeSql(10)
with connRedis.pipeline(transaction=False) as p:
j = 0
updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
for row in tupResult:
try:
# 商品状态
if row[1] is None or row[1] == "":
strStatus = u"停售"
else:
strStatus = row[1]
nTmpStatus = dicConfig.get(str(strStatus).replace('\n', ''))
p.hset(row[0], 'GoodsStatus', nTmpStatus)
p.hset(row[0], 'UpdateTime', updateTime)
j += 1
if j == 20000:
p.execute()
j = 0
except Exception as e:
self.Recordlog(e.message + "Syn Error:SKU="+row[0], "error", sys._getframe().f_lineno)
p.execute()
except Exception as e:
self.Recordlog(e.message + "sql exec error", "error", sys._getframe().f_lineno)
def dealShopSevenSales_Syn(self):
reload(sys)
sys.setdefaultencoding('utf8')
self.connSql(111)
strSql = "select concat(ShopName,'@#@',ShopSKU),sum(SalesVolume) from t_report_sales_daily where orderday between DATE_ADD(SYSDATE(),INTERVAL -8 DAY) " \
"and DATE_ADD(SYSDATE(),INTERVAL -1 DAY) GROUP BY ShopSKU,ShopName "
n = self.sqlcursor.execute(strSql)
sArray = self.sqlcursor.fetchall()
with connRedis.pipeline(transaction=False) as p:
j = 0
for sRowArray in sArray:
try:
updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
if sRowArray[0] == "":
continue
# p.hset(str(sRowArray[0].encode('gb2312')), "sevensales", sRowArray[1]) #测试环境可以
p.hset(sRowArray[0], "SevenSales", sRowArray[1]) # 正式环境
p.hset(sRowArray[0], "SevenSales_UpdateTime", updateTime)
if j == 20000:
p.execute()
j = 0
j += 1
# print('{},sRowArray={},i={}'.format(str(sRowArray[0].encode('gb2312')),sRowArray[0],i))
except Exception as e:
self.Recordlog(e.message + ";" + str(sRowArray[0]), "error", sys._getframe().f_lineno)
continue
p.execute()
self.closeSql(111)
def SynShopSevenSales(self):
reload(sys)
sys.setdefaultencoding('utf8')
'''
db_conn = MySQLdb.Connect(DATABASES['default']['HOST'], DATABASES['default']['USER'],
DATABASES['default']['PASSWORD'],
DATABASES['default']['NAME'], charset='utf8')
sqlcursor = db_conn.cursor()
'''
strSql = "select ShopSKU,sum(SalesVolume) from t_report_sales_daily where orderday between DATE_ADD(SYSDATE(),INTERVAL -8 DAY) " \
"and DATE_ADD(SYSDATE(),INTERVAL -1 DAY) GROUP BY ShopSKU "
n = sqlcursor.execute(strSql)
sArray = sqlcursor.fetchall()
with connRedis.pipeline(transaction=False) as p:
j = 0
for sRowArray in sArray:
updateTime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
if sRowArray[0] == "":
continue
#p.hset(str(sRowArray[0].encode('gb2312')), "sevensales", sRowArray[1]) #测试环境可以
p.hset(sRowArray[0], "sevensales", sRowArray[1]) #正式环境
p.hset(sRowArray[0], "sevensales_updateTime",updateTime)
if j == 20000:
p.execute()
j = 0
j += 1
#print('{},sRowArray={},i={}'.format(str(sRowArray[0].encode('gb2312')),sRowArray[0],i))
p.execute()
sqlcursor.close()
db_conn.close()
return 0
def UpdateSkuInfo(self, sListSku):
sSku = ""
preTime = time.time()
for sRow in sListSku:
sSku = sSku + "'" + sRow + "',"
sSku = sSku[:-1]
gResult = []
self.connSql(10)
self.connSql(111)
updateTime = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
#获取采购未入库 SKUName、SKUID、StoreID、采购未入库数量（列表存储）
sUnStock = self.UpdateSkuUnstock(str(sSku))
if len(sUnStock) > 0:
#获取库存数量、商品状态 商品SKUID、库存量、占用量、7天销量、可卖天数、商品状态（字典存储）
sResult = self.UpdateSkuInStoreAndStatus(sUnStock)
with connRedis.pipeline(transaction=False) as p:
for sRow in sUnStock:
sTmp = []
sTmp.append(sRow[0])
sSKUId = sRow[1]
sOtherInfo = sResult.get(sSKUId)
if cmp(str(sRow[3]), 'None') == 0:
sTmp.append(0) # 未入库数量
else:
sTmp.append(int(float(sRow[3]))) # 未入库数量
#p.hset(sRow[0], '19', sTmp[1])
p.hset(sRow[0], 'NotInStore', sTmp[1])
#p.hset(sRow[0], 'unStock_updateTime', updateTime)
p.hset(sRow[0], 'UpdateTime', updateTime)
if len(sOtherInfo) > 0:
p.hset(sRow[0], 'Number', int(sOtherInfo[1])) # 库存量
p.hset(sRow[0], 'ReservationNum',
int(sOtherInfo[2])) # 占用量
p.hset(sRow[0], 'SellCount1',
int(sOtherInfo[3])) # 7天销量
p.hset(sRow[0], 'CanSaleDay',
int(sOtherInfo[4])) # 可卖天数
#p.hset(sRow[0], 'goodsstatus', sOtherInfo[5])
p.hset(sRow[0], 'GoodsStatus', sOtherInfo[5])
#p.hset(sRow[0], 'KC_updateTime', updateTime)
p.hset(sRow[0], 'UpdateTime', updateTime)
sTmp.append(int(sOtherInfo[1])) #库存
sTmp.append(int(sOtherInfo[2])) #占用
sTmp.append(int(sOtherInfo[3])) #7天销量
sTmp.append(int(sOtherInfo[4])) #可卖天数
sTmp.append(int(sOtherInfo[5])) # 商品状态
sTmp.append(updateTime)
gResult.append(copy.deepcopy(sTmp))
del sTmp[:]
p.execute()
self.closeSql(10)
self.closeSql(111)
dealTime = time.time()
strInfo = 'Deal Success and UsedTime:{}'.format(dealTime - preTime)
#print('sResult={},strInfo={}'.format(gResult,strInfo))
return gResult
def BatchReadRedis(self, gSKUNameAndKey):
gSKUValue = {}
gSKUAllValue = []
strInfo = []
with connRedis.pipeline(transaction=False) as p:
for sRow in gSKUNameAndKey:
sSku = sRow.get("SKU")
sSkuKey = sRow.get("SKUKEY", [])
for sRowKey in sSkuKey:
p.hget(sSku, sRowKey)
sShopSku = sRow.get("ShopSKU")
sShopSkuKey = sRow.get("ShopSKUKEY", [])
for sRowShopKey in sShopSkuKey:
p.hget(sShopSku, sRowShopKey)
strInfo = p.execute()
#print(strInfo)
i = 0
for sRow in gSKUNameAndKey:
subSKU = {}
subSKU['SKU'] = sRow.get("SKU")
sSkuKey = sRow.get("SKUKEY", [])
sTmp = []
for sRowKey in sSkuKey:
if len(strInfo) >= i:
sTmp.append(strInfo[i])
else:
sTmp.append('None')
i += 1
subSKU['SKUKEY'] = copy.deepcopy(sTmp)
del sTmp[:]
subSKU['ShopSKU'] = sRow.get("ShopSKU")
sShopSkuKey = sRow.get("ShopSKUKEY", [])
for sRowShopKey in sShopSkuKey:
if len(strInfo) >= i:
sTmp.append(strInfo[i])
else:
sTmp.append('None')
i += 1
subSKU['ShopSKUKEY'] = copy.deepcopy(sTmp)
del sTmp[:]
gSKUAllValue.append(subSKU)
#print(gSKUAllValue)
return gSKUAllValue
def WriteToRedis(self,fileName,strSplit,nKeyIndex,keyName,nValueIndex,gDicCfg={}):
#print('gDicCfg={}'.format(gDicCfg))
nStatus = len(gDicCfg)
with connRedis.pipeline(transaction=False) as p:
with open(fileName) as fileobject:
j = 0
for line in fileobject:
updateTime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
image_list = line.strip(strSplit).split(strSplit)
nLen = len(image_list)
i = 0
for sName in keyName:
if nLen >= nValueIndex[i]:
#print('{},{},{},{}'.format(i, nValueIndex[i],sName,image_list))
if nStatus != 0:
nTmpStatus = gDicCfg.get(str(image_list[nValueIndex[i] - 1].replace('\n','')))
#print('nTmpStatus = {}'.format(nTmpStatus))
#self.synRedis.setToValuesHashRedis(image_list[nKeyIndex - 1], sName,nTmpStatus)
'''
strStatus = ""
if cmp(str(nTmpStatus),"None") == 0:
strStatus = str(nTmpStatus)
elif int(nTmpStatus) == 1:
strStatus = str(nTmpStatus) + "-正常"
elif int(nTmpStatus) == 2:
strStatus = str(nTmpStatus) + "-售完下架"
elif int(nTmpStatus) == 3:
strStatus = str(nTmpStatus) + "-临时下架"
elif int(nTmpStatus) == 4:
strStatus = str(nTmpStatus) + "-停售"
else:
strStatus = str(nTmpStatus)
'''
p.hset(image_list[nKeyIndex - 1], sName, nTmpStatus)
else:
#self.synRedis.setToValuesHashRedis(image_list[nKeyIndex - 1], sName,image_list[nValueIndex[i] - 1])
p.hset(image_list[nKeyIndex - 1], sName,image_list[nValueIndex[i] - 1])
i += 1
p.hset(image_list[nKeyIndex - 1], "KC_updateTime",updateTime)
if j == 5000:
p.execute()
j = 0
j += 1
p.execute()
def DealGoodsWeight(self):
try:
reload(sys)
sys.setdefaultencoding('utf8')
self.connSql(10)
strSql = "select sku,Weight,PackageCount from b_goods(nolock)"
n = self.sqlcursor_py.execute(strSql)
tupResult = self.sqlcursor_py.fetchall()
self.closeSql(10)
strWeight = '0'
with connRedis.pipeline(transaction=False) as p:
j = 0
updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
for row in tupResult:
try:
# 商品状态
if row[1] is None or row[1] == "":
strWeight = '0'
else:
strWeight = row[1]
# 最小包装数量
if row[2] is None or row[2] == "":
PackageCount = '0'
else:
PackageCount = row[2]
p.hset(row[0], 'Weight', strWeight)
p.hset(row[0], 'PackageCount', PackageCount)
p.hset(row[0], 'UpdateTime', updateTime)
j += 1
if j == 20000:
p.execute()
j = 0
except Exception as e:
self.Recordlog(e.message + "Syn Error:SKU="+row[0], "error", sys._getframe().f_lineno)
p.execute()
except Exception as e:
self.Recordlog(e.message + "sql exec error", "error", sys._getframe().f_lineno)
def dealOtherData_Syn(self):
reload(sys)
sys.setdefaultencoding('utf8')
dicConfig = self.getStatusConfig()
#print time.strftime('GetSqlServerData:%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
tuppleResult = self.GetSqlServerData()
#print time.strftime('GetSqlServerData:%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
if len(tuppleResult) != 0:
with connRedis.pipeline(transaction=False) as p:
j = 0
for row in tuppleResult:
try:
updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
strSKU = row[20]
#商品库存量
if row[5] is None:
p.hset(strSKU, 'Number', 0)
else:
p.hset(strSKU, 'Number', row[5])
#商品占有量
if row[6] is None:
p.hset(strSKU, 'ReservationNum', 0)
else:
p.hset(strSKU, 'ReservationNum', row[6])
#商品成本价
p.hset(strSKU, 'CostPrice', row[8])
# 商品7天销量
if row[2] is None:
p.hset(strSKU, 'SellCount1', 0)
else:
p.hset(strSKU, 'SellCount1', row[2])
# 商品采购未入库
if row[10] is None:
p.hset(strSKU, 'NotInStore', 0)
else:
p.hset(strSKU, 'NotInStore', row[10])
# 商品可卖天数
if row[22] is None:
p.hset(strSKU, 'CanSaleDay', 0)
else:
p.hset(strSKU, 'CanSaleDay', row[22])
# 商品预计可用库存
p.hset(strSKU, 'HopeUseNum', row[11])
# 商品缺货及未派单数量
p.hset(strSKU, 'UnPaiDNum', row[12])
#商品库位
p.hset(strSKU, 'Location', row[17])
# 商品 刷新时间
p.hset(strSKU, 'UpdateTime', updateTime)
#print(row[20])
j +=1
if j == 20000:
p.execute()
j = 0
except Exception as e:
strInfo = "%s,SKU=%s,GoodsStatus=%s,Number=%s,ReservationNum=%s,Price=%s,SellCount1=%s,NotInStore=%s," \
"CanSaleDay=%s,HopeUseNum=%s,UnPaiDNum=%s,Location=%s,UpdateTime=%s"\
%(row[21],row[20],str(row[18]),row[5],row[6],row[8],row[2],row[10],row[14],row[11],row[12],row[17],updateTime)
self.Recordlog(e.message + str(strInfo),"error", sys._getframe().f_lineno)
continue
p.execute()