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 queryTableLastOne(tableName,field='*',where =''):
'''基础查询表内容
返回数组结构 []'''
if where !='':
where = ' where '+where
sql = f'''select {field} from {tableName} {where} order by {field} desc limit 1'''
res =selectOneBy(sql)
return res[field]
def querySkuId(huohao,drugstoreId):
sql = '''
SELECT sku_id FROM `pm_prod_sku`
WHERE `pharmacy_huohao` = '{0}'
and `drugstore_id` ={1};
'''.format(huohao,drugstoreId)
dic = selectOneBy(sql)
return dic.get('sku_id')
def queryBaseDirCode(drugstoreId):
'''查询药店的基础dircode， 相应药店下的子级目录需要 以此code开头
'''
# for drugstoreId in self.drugstoreIdList:
sql = '''
SELECT * from pm_dir_info WHERE
dir_name = '功能主目录'
and pharmacy_id ={};
'''.format(drugstoreId)
dic = selectOneBy(sql)
return dic.get('dir_code')
def addAmStatInfo(skuId,actId,itemId,itemName,itemDesc,itemType,startTime,endTime,quotaId='',itemPriority='100',itemAttr='multi',otherStr1=''):
configSql = '''SELECT config_value from sm_config WHERE config_key = 'act_batch';'''
configDic = selectOneBy(configSql)
configValue =configDic.get('config_value')
sql = f"""INSERT INTO am_stat_info ( `batch_num`, `sku_id`, `act_id`, `item_id`, `item_remark` , `item_name`
, `item_attr`, `stat_update_time`, `stat_create_time`, `item_effect_time`, `item_expire_time`
, `item_type`, `other_str1`, `quota_id`, `item_flag`, `item_priority`)
VALUES ( '{configValue}', '{skuId}', '{actId}', '{itemId}', '{itemDesc}', '{itemName}'
, '{itemAttr}', now(), now(), '{startTime}', '{endTime}'
, '{itemType}', '{otherStr1}', '{quotaId}', NULL, '{itemPriority}');
"""
res = insertSQL(sql)
return res['lastId']
def copyMsStat(day, addDays, ydId):
sql = f'''SELECT * from am_stat_info stat,pm_prod_sku s
WHERE stat.sku_id = s.sku_id and s.drugstore_id={ydId}
and item_effect_time >= '{day} 00:00:00' and item_expire_time <= '{day} 23:59:59'
;'''
statList = selectBy(sql)
configSql = '''SELECT config_value from sm_config WHERE config_key = 'act_batch';'''
configDic = selectOneBy(configSql)
configValue = configDic.get('config_value')
for dic in statList:
lineSql = ''
skuId = dic.get('sku_id')
actId = dic.get('act_id')
itemId = dic.get('item_id')
itemName = dic.get('item_name')
itemRemark = dic.get('item_remark')
itemType = dic.get('item_type')
quotaId = dic.get('quota_id') if dic.get('quota_id') != None else ''
# logging.error('quotaId-------%s---%s--',dic.get('quota_id'),dic.get('quota_id') if dic.get('quota_id')!=None else '')
# dirId = dic.get('dir_id')
# dirCode = dic.get('dir_code')
itemAttr = dic.get('item_attr')
otherStr1 = dic.get('other_str1',
'') if dic.get('other_str1') != None else ''
itemPriority = dic.get('item_priority')
start = dic['item_effect_time'].strftime('%Y-%m-%d %H:%M:%S')
end = dic['item_expire_time'].strftime('%Y-%m-%d %H:%M:%S')
for i in range(addDays):
today = (datetime.datetime.strptime(day, '%Y-%m-%d') +
datetime.timedelta(days=i + 1)).strftime('%Y-%m-%d')
sd = start.replace(day, today)
nd = end.replace(day, today)
lineSql += f"""( '{configValue}', '{skuId}', '{actId}', '{itemId}', '{itemRemark}', '{itemName}'
, '{itemAttr}', now(), now(), '{sd}', '{nd}'
, '{itemType}', '{otherStr1}', '{quotaId}', NULL, '{itemPriority}'),"""
# lineSql += f"""('{sku_id}','{sale_fee}','{sale_price}','{sd}','{nd}','{sale_status}',now(),now(),'{sale_remark}'),"""
lineSql = lineSql[:-1]
sql = f"""INSERT INTO am_stat_info ( `batch_num`, `sku_id`, `act_id`, `item_id`, `item_remark` , `item_name`
, `item_attr`, `stat_update_time`, `stat_create_time`, `item_effect_time`, `item_expire_time`
, `item_type`, `other_str1`, `quota_id`, `item_flag`, `item_priority`)
VALUES {lineSql} ; """
res = insertSQL(sql)
def selectActInfo(itemId):
"""根据itemid查询活动信息"""
sql = f"""SELECT a.act_start_time, a.act_end_time,a.act_id,
b.item_id,b.item_name,b.item_desc,b.item_type,b.item_attr ,b.item_remark,b.item_img,b.item_num,b.item_priority
,dd.dir_id,dd.dir_code,dd.dir_name,dd.pharmacy_id
,e.details_id,e.details_type,e.details_val_type,e.details_value,e.details_remark,e.details_content
,g.rule_id,g.rule_value
,h.quota_remark,h.quota_content,h.quota_id,h.quota_rule,h.qutoa_group
from am_act_item b
LEFT JOIN am_item_range c on b.item_id = c.item_id
LEFT JOIN am_act_range d on c.range_id = d.range_id
LEFT JOIN pm_dir_info dd on d.range_value = dd.dir_id
LEFT JOIN am_item_details e on b.item_id = e.item_id
LEFT JOIN am_details_rule f on e.details_id = f.details_id
LEFT JOIN am_act_rule g on f.rule_id = g.rule_id
LEFT JOIN am_quota_info h on b.item_id = h.item_id
LEFT JOIN am_act_info a on a.act_id = b.act_id
WHERE b.item_id = {itemId}"""
list = selectOneBy(sql)
return list
def createCombByHuohao(huohao,drugstoreId,combProdId,fee,price,count=1):
skusql = f"select * from pm_prod_sku where pharmacy_huohao = {huohao} and drugstore_id= {drugstoreId}"
skures = selectOneBy(skusql)
logging.info(skures)
baseSkuId = skures['sku_id']
createComb(baseSkuId,combProdId,fee,price,count=count)
