def createNewSkuByTable(tableName):
"""根据excel给的prodid创建sku，需确定drugstoreid和价格 上下架状态"""
logging.debug('新建商品的pm_prod_sku，huohao,零售价，上下架状态外其他信息用 prod 的原始信息，不用execl中的')
sql = '''
INSERT INTO `medstore`.`pm_prod_sku` (`prod_id`, `sku_status`, `sku_price`, `sku_fee`
, `drugstore_id`, `brand_id`, `sku_update_time`, `sku_create_time`, `sku_remark`, `sku_logistics`
, `prod_name`, `pharmacy_huohao`, `source_id`, `sku_json`, `sku_attr`, `sku_img`, `sku_sum`)
SELECT p.prod_id
,a.sku_status as sku_status
,a.price*100 as sku_price
,a.fee*100 as sku_fee
,a.drugstore_id,'1' as brand_id ,now() as sku_update_time,now() as sku_create_time
,'' as sku_remark,p.prod_logist,p.`prod_name`
,a.huohao as pharmacy_huohao,'1' as source_id
,concat('{{\"prod_spec\":\"',p.prod_spec,'\"}}'),'11100' as sku_attr,'' as sku_img,'' as sku_sum
from {0} a,pm_prod_info p
WHERE a.prod_id = p.prod_id ;
'''.format(tableName)
try:
insertSQL(sql)
db.commit()
except Exception as err:
logging.error("Error %s for execute sql: %s" % (err, sql))
logging.debug('新建商品的pm_prod_sku，insert语句失败！！！')
db.rollback()
def createComb(baseSkuId,combProdId,fee,price,count=1):
"""根据单件的skuid和多盒的prodid创建 ，多盒的sku"""
prodsql = f"select * from pm_prod_info where prod_id = {combProdId}"
prodres = selectOneBy(prodsql)
logging.info(prodres)
prename = f'{count}件装' # prodres.get('pre_prod_name')
prodname = prodres.get('prod_name')
spec = prodres.get('prod_spec')
specjson = f'{{"prod_spec":"{spec}"}}'
sql = f"""
INSERT INTO `medstore`.`pm_prod_sku` ( `prod_id`, `sku_status`, `sku_price`, `sku_fee`
, `drugstore_id`, `brand_id`, `sku_update_time`, `sku_create_time`, `sku_remark`, `sku_logistics`
, `prod_name`, `pharmacy_huohao`, `source_id`, `sku_json`, `sku_attr`, `sku_img`, `sku_sum`
, `sku_activate`, `sales_info`, `sku_sum_flag`, `sku_hot_order`, `sku_sort`, `sku_rank`, `sku_type`
, `is_set`, `set_num`, `dis_before_price`, `dis_after_price`, `discount_price`, `pre_prod_name`)
select '{combProdId}', `sku_status`, {price}, {fee}
, `drugstore_id`, `brand_id`, now(), now(), `sku_remark`, `sku_logistics`
, concat('{prename}',prod_name) ,concat('{prename}',pharmacy_huohao) , `source_id`
,'{specjson}', `sku_attr`, `sku_img`, `sku_sum`
, `sku_activate`, `sales_info`, `sku_sum_flag`, `sku_hot_order`, `sku_sort`, `sku_rank`, 'comb'
,1 `is_set`,{count} `set_num`
,sku_price `dis_before_price`,{price/count} `dis_after_price`,sku_price-{price/count} `discount_price`,'{prename}' `pre_prod_name`
from pm_prod_sku
where sku_id = {baseSkuId} ;
"""
# logging.info(sql)
try:
res = insertSQL(sql)
combSkuId= res['lastId']
skusql = f"select * from pm_prod_sku where sku_id = {combSkuId}"
skures = selectOneBy(skusql)
logging.info(skures)
name = skures['prod_name']
huohao = skures['pharmacy_huohao']
drugstoreId = skures['drugstore_id']
fee = skures['sku_fee']
price = skures['sku_price']
beforeprice = skures['dis_before_price']
disprice = skures['dis_after_price']
packetId= addPmPacketInfo(combSkuId, name,prename,huohao,drugstoreId,fee,price)
addPmPacketSku(packetId,baseSkuId,combSkuId, prodname,huohao,drugstoreId,disprice,beforeprice,count,price)
db.commit()
except:
db.rollback()
def test(self, actId,actName,startTime,endTime,pharmacyId):
'''创建am_act_info'''
sql = f"""INSERT INTO `medstore`.`am_act_info` (`act_id`, `act_name`, `act_type`, `act_status`, `act_content`
, `act_update_time`, `act_create_time`, `act_start_time`, `act_end_time`, `act_level`, `act_remark`
, `act_img`, `act_url`, `pharmacy_id`)
VALUES ('{actId}', '{actName}', 'date', '1', '活动', now(), now(), '{startTime}', '{endTime}', '1', '', '', '', '{pharmacyId}');"""
# print(sql)
# res = insertSQL(sql)
# id = res['lastId']
# logging.debug(f'完成创建am_act_info1----id:{id}')
# logging.info(f'完成创建am_act_info2----id:{id}')
try:
# cursor.execute(sql2)
# # db.commit()
# print(cursor.lastrowid)
# cursor.execute(sql)
# res = insertSQL(sql2)
# res2 = insertSQL(sql3)
res3 = insertSQL(sql)
id = res['lastId']
id2 = res2['lastId']
id3 = res3['lastId']
logging.info(f'完成创建am_act_info2----id:{id}')
logging.info(f'完成创建am_act_info3----id:{id2}')
logging.info(f'完成创建am_act_info4----id:{id3}')
db.commit()
id = res['lastId']
logging.info(f'完成创建am_act_info2----id:{id}')
# except Exception:
except Exception as err:
logging.error("Error %s for execute sql: %s" % (err, sql))
logging.debug('语句失败！！！')
db.rollback()
return {
'code':404,
'count':0,
'lastId':0,
'data':[]
}
return id
def delayMsDateFrom(day, addDays, ydId):
"""延期秒杀活动专用，day为目前已有秒杀的最后一天时间，adddays是要延期的天数，ydid是药店id"""
try:
sql = f'''SELECT * from am_stages_sale s
WHERE sg_start_time >= '{day} 00:00:00' and sg_end_time <= '{day} 23:59:59'
and s.pharmacy_id = {ydId};'''
stagesList = selectBy(sql)
for dic in stagesList:
lineSql = ''
sgId = dic['sg_id']
# dirId = dic['dir_id']
# remark = dic['sg_detail_remark'] if dic['sg_detail_remark']!=None else 'NULL'
# flag = dic['sg_detail_flag']
# type = dic['sg_detail_type']
start = dic['sg_start_time'].strftime('%Y-%m-%d %H:%M:%S')
end = dic['sg_end_time'].strftime('%Y-%m-%d %H:%M:%S')
for i in range(addDays):
# logging.info(datetime.datetime.strptime(day,'%Y-%m-%d')+datetime.timedelta(days=i+1))
today = (datetime.datetime.strptime(day, '%Y-%m-%d') +
datetime.timedelta(days=i + 1)).strftime('%Y-%m-%d')
# logging.info('%s----%s----%s',day,today,type(start))
sd = start.replace(day, today)
nd = end.replace(day, today)
# logging.info('%s----%s----',sd,nd)
lineSql += f"""('{dic['pharmacy_id']}','{dic['sg_title']}','{dic['sg_status']}','{sd}','{nd}','{dic['quota_id']}','{dic['act_id']}','{dic['item_id']}',now(),now()),"""
lineSql = lineSql[:-1]
sql = f"""INSERT INTO `medstore`.`am_stages_sale` ( `pharmacy_id`, `sg_title`, `sg_status`, `sg_start_time`, `sg_end_time`, `quota_id`, `act_id`, `item_id`, `sg_create_time`, `sg_update_time`)
VALUES {lineSql} ; """
res = insertSQL(sql)
# logging.info(sql)
firstId = res['lastId']
count = res['count']
lastId = firstId + count - 1
sql = f'''SELECT * from am_stages_sale s left join am_stages_sale_detail sd on s.sg_id = sd.sg_id
WHERE sg_start_time >= '{day} 00:00:00' and sg_end_time <= '{day} 23:59:59'
and s.pharmacy_id = {ydId}
and s.sg_id = {sgId}
;'''
stagesdetailList = selectBy(sql)
for ddic in stagesdetailList:
dirId = ddic['dir_id']
remark = ddic['sg_detail_remark'] if ddic[
'sg_detail_remark'] != None else 'NULL'
flag = ddic['sg_detail_flag']
type = ddic['sg_detail_type']
detailSql = f"""INSERT INTO `medstore`.`am_stages_sale_detail` ( `sg_id`, `act_id`, `item_id`, `quota_id`, `pharmacy_id`, `dir_id`, `sg_detail_create_time`, `sg_detail_update_time`, `sg_detail_remark`, `sg_detail_flag`, `sg_detail_type`)
SELECT `sg_id`, `act_id`, `item_id`, `quota_id`, `pharmacy_id`,'{dirId}' `dir_id`,NOW() `sg_detail_create_time`,NOW() `sg_detail_update_time`
,'{remark}' `sg_detail_remark`,'{flag}' `sg_detail_flag`,'{type}' `sg_detail_type`
from am_stages_sale
WHERE sg_id between {firstId} and {lastId} ;"""
insertSQL(detailSql)
# saleSql= f"""INSERT INTO `medstore`.`am_stages_sale_detail` ( `sg_id`, `act_id`, `item_id`, `quota_id`, `pharmacy_id`, `dir_id`, `sg_detail_create_time`, `sg_detail_update_time`, `sg_detail_remark`, `sg_detail_flag`, `sg_detail_type`)
# SELECT `sg_id`, `act_id`, `item_id`, `quota_id`, `pharmacy_id`,'{dirId}' `dir_id`,NOW() `sg_detail_create_time`,NOW() `sg_detail_update_time`
# ,{remark} `sg_detail_remark`,'{flag}' `sg_detail_flag`,'{type}' `sg_detail_type`
# from am_stages_sale
# WHERE sg_id between {firstId} and {lastId} ;"""
# insertSQL(saleSql)
copyMsSale(day, addDays, ydId)
copyMsStat(day, addDays, ydId)
logging.info(
'----------------------------------over---------------------------'
)
db.commit()
except Exception as err:
logging.error("Error %s for execute sql: %s" % (err, tableName))
db.rollback()
def 创建秒杀(actId=0,
actName='',
tableName='',
ydList=[],
startTime='',
endTime='',
img='',
color='',
linkimg='',
linkurl='',
linkView='',
windowimg=''):
logging.debug(
'开始创建秒杀，名称为{{actName}}--数据表{{tableName}}-----所属药店{{ydList}} ----起止时间 {{startTime}}-{{endTime}}'
)
if actId == 0:
iAct = queryTableLastOne('am_act_info',
field='act_id',
where='',
order='act_id desc')
iActId = iAct['act_id']
actId = iActId + 1
logging.debug(f'create活动act——id{actId}')
for index in range(len(ydList)):
try:
drugstoreId = ydList[index]
# 创建特价
addPmActSale(tableName, drugstoreId, startTime, endTime)
logging.info('创建价格------------')
addAmActInfo(actId,
actName,
startTime,
endTime,
drugstoreId=drugstoreId)
skuList = querySkuIdByTable(tableName, drugstoreId)
dirList = queryTable(tableName,
where=' dir_code !="" group by dir_code')
itemList = queryTable(tableName,
where=' item_code !="" group by item_code')
if len(dirList) > 0 or len(itemList) > 0:
logging.info(f'开始创建活动目录 {drugstoreId}')
# logging.error('开始创建活动目录error')
parentdirDic = addPmDirInfo(f'act{actId}',
actName,
drugstoreId,
img=img,
color=color,
num='1',
level='2',
parentDirId='')
parentDirId = parentdirDic['dir_id']
logging.info(f'创建zhu活动目录 {parentdirDic}')
for dir in dirList:
# logging.info(f'创建子目录 {dir}')
dirInfo = ActInfo(dir)
sufDirCode = dirInfo.dir_code
# logging.info(f'创建子活动目录 {dirInfo}')
genCode = queryBaseDirCode(drugstoreId)
sdirCode = f'{genCode}act{actId}{sufDirCode}{sufDirCode}'
sdir = queryTableLastOne('pm_dir_info', '*',
f"dir_code = '{sdirCode}'",
'dir_id desc')
remark = ''
if sdir != None:
remark = sdir['dir_id']
dic = addPmDirInfo(f'act{actId}{dirInfo.dir_code}',
dirInfo.dir_name,
drugstoreId,
img=dirInfo.dir_img,
color=remark,
num=dirInfo.dir_num,
level='3',
parentDirId=parentDirId)
dicId = dic['dir_id']
dicCode = dic['dir_code']
sufdicCode = dir['dir_code']
logging.info(f'创建子会场目录 {dic}')
for sku in skuList:
skuDicCodeId = sku['dir_code']
order = sku['xh']
if skuDicCodeId == sufdicCode:
skuId = sku['sku_id']
addPmSkuDir(skuId, dicId, dicCode, order)
for item in itemList:
itemFlag = ''
if 'is_eq' in dic.keys(
) and dic['is_xq'] != None and dic['is_xq'] == 1:
itemFlag = 'false'
# copyAmStatInfoBySkuId( 78,sku_id)
itemInfo = ActInfo(item)
itemName = itemInfo.item_name
itemDesc = itemInfo.item_desc
itemType = itemInfo.item_type
itemCode = itemInfo.item_code
itemImg = itemInfo.item_img
itemImgR = itemInfo.item_img_r
otherImg = f'{{"itemImageR":"{itemImgR}","itemImage":"{itemImg}"}}' if itemImg != '' and itemImgR != '' else ''
# dirDic = addPmDirInfo(f'act{actId}{itemCode}',itemName,drugstoreId,level='3')
# idicInfo = ActInfo(dirDic)
# rangeId =addAmActRange( idicInfo.dir_id,itemName,itemDesc)
itemId = addAmActItem(itemName,
itemDesc,
itemType,
actId,
drugstoreId,
img=itemImg,
imgR=itemImgR,
flag=itemFlag)
addAmItemRange(itemId, rangeId)
logging.info(
f'创建 活动 item range dir {itemId} {itemName}')
quotaId = ''
# logging.info(f'itemType-- -{itemType};details_value--{itemInfo.details_value}')
if itemType == 'quota':
limit = itemInfo.quota_rule
if limit != '':
limit_group = itemInfo.quota_group
quotaId = addAmQuotaInfo(itemId, limit, itemDesc,
limit_group)
logging.info(f'创建 限购规则 quota {itemName}{itemDesc}')
if itemType == 'discount':
details_values = itemInfo.details_value
rule_values = itemInfo.rule_value
logging.info(
f'创建 活动规则 details {itemName}- {itemDesc} -{details_values}- {rule_values}'
)
# logging.info(f'details_value-- -{details_value};rule_value--{rule_value}')
if details_values != '':
details_remark = itemName
details_content = itemDesc
details_value_list = details_values.split(',')
for details_value in details_value_list:
# logging.info(f'detailsId------------------------{itemId,details_value,details_remark,details_content}')
details_level = details_value_list.index(
details_value) + 1
detailsId = addAmItemDetails(
itemId,
details_value,
details_remark,
details_content,
details_type='discount',
details_level=details_level)
# logging.info(f'detailsId------------------------{detailsId}')
if detailsId != '' and rule_values != '':
rule_value_list = rule_values.split(',')
# for details_value in details_value_list:
rule_value = rule_value_list[
details_value_list.index(details_value)]
addAmDetailsRule(detailsId, rule_value)
# quotaId = addAmQuotaInfo( itemId,itemInfo.limit,itemDesc,itemInfo.limit_group)
# dicId = dirDic['dir_id']
# dicCode = dirDic['dir_code']
sufdicCode = item['item_code']
for sku in skuList:
skuDicCodeId = sku['item_code']
if skuDicCodeId == sufdicCode:
skuId = sku['sku_id']
# addPmSkuDir(skuId,dicId,dicCode)
# if hasattr(item,'kc_day') and item.get('kc_day')!='':
# skuTotal = item.get('kc_day',0)
# days = item.get('days',0)
# maxTotal = int(days)*int(skuTotal)
# addAmStockLimit(skuId,itemId,quotaId,skuTotal,maxTotal,remark='')
# addAmStockPday(skuId,actId,itemId,quotaId,drugstoreId,skuTotal,maxTotal,remark='')
addAmStatInfo(skuId,
actId,
itemId,
itemName,
itemDesc,
itemType,
startTime,
endTime,
quotaId=quotaId,
otherStr1=otherImg,
flag=itemFlag)
# copyAmStatInfoByHuohao( '78',huohao,drugstoreId)
# logging.info(f'创建 addAmStatInfo')
# db.commit()
logging.info('创建成功')
db.commit()
except Exception as err:
logging.error(err)
db.rollback()
def buildActInfoByTable(tableName,actId,actName,drugstoreId,startTime,endTime,img='',color=''):
""" 创建基础表的时候，如果有会场，保证有dir_code,dir_name,pharmacy_id 可选 dir_img,dir_num
如果有活动 保证有 item_code(在dirinfo中使用),item_name,item_desc,item_type()
**** 都有code为准，名称重复率太高
1：item_type = quota 需要有limit （格式为 1:1:1） 可选limit group， 如果有库存 需要有 kc_day , （此处需扩展为 每日库存，总库存，天数）
2：item_type = discount 需要有 子级手动配置detail（因为活动中类比较多）
3：item_type = drugtag ,由于文字不会显示在list和detail，一般是配置dir_img用的
3：item_type = cut 满减
5：item_type = nothing 列表页显示，详情页不显示
buy 购买婴幼儿奶粉加9元换购特殊商品，满1000元加19元换购其他商品
gift 购买热销商品满100元送体温计，满200元送电子体温计
excoupon 不可使用优惠券
'dir','actskudir','skudir','item','range','itemrange','quota','stock','stock-pday','ms-sale','ms-packet'"""
try:
addAmActInfo(actId,actName,startTime,endTime,drugstoreId='200')
skuList = querySkuIdByTable(tableName,drugstoreId)
dirList = queryTable(tableName,where=' dir_code !="" group by dir_code')
itemList = queryTable(tableName,where=' item_code !="" group by item_code')
if len(dirList)>0 or len(itemList)>0:
logging.info('开始创建活动目录')
# logging.error('开始创建活动目录error')
parentdirDic = addPmDirInfo(f'act{actId}',actName,drugstoreId,img=img,color=color,num='1',level='2',parentDirId='')
parentDirId = parentdirDic['dir_id']
logging.info(f'创建zhu活动目录 {parentdirDic}')
for dir in dirList:
# logging.info(f'创建子目录 {dir}')
dirInfo = ActInfo(dir)
# logging.info(f'创建子活动目录 {dirInfo}')
dic = addPmDirInfo(f'act{actId}{dirInfo.dir_code}',dirInfo.dir_name,drugstoreId,img=dirInfo.dir_img,num=dirInfo.dir_num,level='3',parentDirId=parentDirId)
dicId = dic['dir_id']
dicCode = dic['dir_code']
sufdicCode = dir['dir_code']
logging.info(f'创建子会场目录 {dic}')
for sku in skuList:
skuDicCodeId=sku['dir_code']
order = sku['xh']
if skuDicCodeId==sufdicCode:
skuId=sku['sku_id']
addPmSkuDir(skuId,dicId,dicCode,order)
for item in itemList:
logging.info(f'创建子活动目录 {item}')
itemInfo = ActInfo(item)
itemName = itemInfo.item_name
itemDesc = itemInfo.item_desc
itemType = itemInfo.item_type
itemCode = itemInfo.item_code
dirDic = addPmDirInfo(f'act{actId}{itemCode}',itemName,drugstoreId,level='3')
idicInfo = ActInfo(dirDic)
rangeId =addAmActRange( idicInfo.dir_id,itemName,itemDesc)
itemId = addAmActItem(itemName,itemDesc,itemType,actId,drugstoreId,img='')
addAmItemRange(itemId,rangeId)
quotaId= ''
if itemInfo.item_type=='quota':
quotaId = addAmQuotaInfo( itemId,itemInfo.limit,itemDesc,itemInfo.limit_group)
dicId = dirDic['dir_id']
dicCode = dirDic['dir_code']
sufdicCode = item['item_code']
for sku in skuList:
skuDicCodeId=sku['item_code']
if skuDicCodeId==sufdicCode:
skuId=sku['sku_id']
addPmSkuDir(skuId,dicId,dicCode)
if hasattr(item,'kc_day'):
skuTotal = item.get('kc_day',0)
days = item.get('days',0)
maxTotal = int(days)*int(skuTotal)
addAmStockLimit(skuId,itemId,quotaId,skuTotal,maxTotal,remark='')
addAmStockPday(skuId,actId,itemId,quotaId,drugstoreId,skuTotal,maxTotal,remark='')
addAmStatInfo(skuId,actId,itemId,itemName,itemDesc,itemType,startTime,endTime,quotaId =quotaId)
db.commit()
logging.info('创建成功')
except Exception as err:
logging.error(err)
db.rollback()