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 addAmStockPday(skuId,actId,itemId,quotaId,drugstoreId,skuTotal,maxTotal,remark=''):
sql = f'''
INSERT INTO `medstore`.`am_stock_pday` ( `act_id`, `item_id`, `quota_id`, `sku_id`, `pharmacy_id`, `sku_num`, `sku_total`, `remark`)
VALUES ( '{actId}','{itemId}', '{quotaId}', '{skuId}','{drugstoreId}', '{skuTotal}', '{maxTotal}', '{remark}');
'''
res = insertSQL(sql)
return res['lastId']
def copyMsSale(day, addDays, ydId):
sql = f'''SELECT * from pm_sku_sale sale,pm_prod_sku s
WHERE sale.sku_id = s.sku_id and s.drugstore_id={ydId}
and sale_start_time >= '{day} 00:00:00' and sale_end_time <= '{day} 23:59:59';'''
saleList = selectBy(sql)
for dic in saleList:
lineSql = ''
sku_id = dic['sku_id']
sale_fee = dic['sale_fee']
sale_price = dic['sale_price']
sale_status = dic['sale_status']
sale_remark = dic['sale_remark']
start = dic['sale_start_time'].strftime('%Y-%m-%d %H:%M:%S')
end = dic['sale_end_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"""('{sku_id}','{sale_fee}','{sale_price}','{sd}','{nd}','{sale_status}',now(),now(),'{sale_remark}'),"""
lineSql = lineSql[:-1]
sql = f"""INSERT INTO `medstore`.`pm_sku_sale` ( `sku_id`, `sale_fee`, `sale_price`, `sale_start_time`, `sale_end_time`, `sale_status`, `sale_create_time`, `sale_update_time`, `sale_remark`)
VALUES {lineSql} ; """
res = insertSQL(sql)
def addNewSku4JHSMS(tableName,preHuohao,drugstoreId):
'''创建聚划算和秒杀的商品sku，根据base——huohao来确认商品，然后复制一个富裕新huohao
'''
sql = '''
UPDATE {} set huohao = CONCAT('{}',base_huohao);
'''.format(tableName,preHuohao)
res = insertSQL(sql)
if res['code']==200:
sql1 = '''
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 s.`prod_id`, `sku_status`, `sku_price`, `sku_fee`, `drugstore_id`, `brand_id`,NOW() `sku_update_time`,NOW() `sku_create_time`, `sku_remark`, `sku_logistics`, `prod_name`
,a.huohao `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`
from {} a,pm_prod_sku s
WHERE a.base_huohao = s.pharmacy_huohao
and s.drugstore_id = {};
'''.format(tableName,drugstoreId)
res = insertSQL(sql1)
def addAmItemRange(itemId,rangeId):
'''创建 range item 的关连
'''
sql = '''
INSERT INTO `medstore`.`am_item_range` ( `range_id`, `item_id`) VALUES ( '{}', '{}');
'''.format(rangeId,itemId)
res = insertSQL(sql)
return res['code']
def addPmPacketSku(packetId,skuId,combSkuId, name,packethuohao,drugstoreId,disprice,price,count,combPrice):
"""关联多盒Packetsku 和单盒sku"""
sql = f"""INSERT INTO `medstore`.`pm_packet_sku` ( `packet_id`, `sku_id`, `prod_name`, `sku_huohao`
, `packet_sku_id`, `packet_sku_price`, `sku_fee`, `sku_seq`, `sku_amount`, `total_price`, `link_remark`)
VALUES ( '{packetId}', '{skuId}', '{name}', '{packethuohao}', '{combSkuId}', '{disprice}', '{price}', '1', '{count}', '{combPrice}'
, '');
"""
res = insertSQL(sql)
return res['lastId']
def copyAmStatInfoBySkuId( item_id,sku_id):
'''根据查询到的活动信息，复制amstatinfo数据，修改skuid即可'''
statSql = '''
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`)
SELECT `batch_num`,'{}' `sku_id`, `act_id`, `item_id`, `item_remark`, `item_name`, `item_attr`,NOW() `stat_update_time`, `stat_create_time`, `item_effect_time`, `item_expire_time`, `item_type`, `other_str1`, `quota_id`, `item_flag`, `item_priority`
from am_stat_info
WHERE `item_id` = {}
limit 1;
'''.format(sku_id,item_id)
res = insertSQL(statSql)
def addAmActRange( dirId,dirName,dirRemark=''):
'''创建 range item 通过目录id来创建
'''
sql = F'''
INSERT INTO `medstore`.`am_act_range` ( `range_type`, `range_value`, `range_status`
, `range_name`, `range_remark`, `range_update_time`, `range_create_time`)
values('category', {dirId}, '1', '{dirName}', '{dirRemark}', NOW(), NOW())
'''
res = insertSQL(sql)
return res['lastId']
def addAmskuImage(huohao,drugstoreId,img='http://image.ykd365.cn/icon/home/drsd.png',radio='342',title='当日送达',startTime='2020-03-01',endTime='2028-03-01'):
"""创建列表页的药品名左侧的标志，当日送达"""
# logging.info('创建列表页的药品名左侧的标志，当日送达')
sql = '''
INSERT INTO am_sku_img ( `sku_id`, `act_type`, `act_start_time`, `act_end_time`, `act_status`, `create_time`, `update_time`, `act_img_url`, `aspect_ratio`, `remark`)
SELECT s.sku_id,'logo','{}', '{}', '1', NOW(), NOW(), '{}', '{}', '{}'
from pm_prod_sku s
WHERE pharmacy_huohao = '{}' and s.drugstore_id = {};
'''.format(startTime,endTime,img,radio,title,huohao,drugstoreId)
res = insertSQL(sql)
return res['lastId']
def addPmPacketInfo(skuId, name,prename,huohao,drugstoreId,fee,price):
"""根据多盒sku创建多盒装packet"""
sql = f"""INSERT INTO `medstore`.`pm_packet_info` ( `packet_huohao`, `packet_name`, `packet_status`, `packet_type`
, `packet_price`, `packet_fee`, `packet_category`, `packet_content`, `packet_stock`, `packet_img`, `drugstore_id`
, `packet_update_time`, `packet_create_time`, `packet_remark`, `sku_id`, `pre_prod_name`, `packet_coures`, `packet_title_formula`)
VALUES ( '{huohao}', '{name}', '1', 'normal', '{price}', '{fee}', '1001000296', '{huohao}', '1', NULL
, '{drugstoreId}', now(), now(), '', '{skuId}', '{prename}', 'single'
, '{name}');
"""
res = insertSQL(sql)
return res['lastId']
def addAmQuotaInfo( itemId,limit,desc,group='0'):
'''创建 range item 通过目录id来创建
'''
sql = f'''
INSERT INTO `medstore`.`am_quota_info` ( `item_id`, `quota_rule`, `quota_kind`
, `quota_type`, `quota_status`, `quota_total`, `quota_update_time`, `quota_create_time`
, `quota_remark`, `quota_content`, `qutoa_group`)
VALUES ( '{itemId}', '{limit}', 'main', 'force', '1', '0', now(), now(), '{desc}', '活动商品{desc}', '{group}');
'''
res = insertSQL(sql)
return res['lastId']
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 addNewSkuWithPre(huohao,preHuohao,drugstoreId):
'''创建聚划算和秒杀的商品sku，根据base——huohao来确认商品，然后复制一个富裕新huohao
'''
sql1 = 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 s.`prod_id`, `sku_status`, `sku_price`, `sku_fee`, `drugstore_id`, `brand_id`,NOW() `sku_update_time`,NOW() `sku_create_time`, `sku_remark`, `sku_logistics`, `prod_name`
,'{preHuohao}{huohao}' `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`
from pm_prod_sku s
WHERE s.pharmacy_huohao = '{huohao}'
and s.drugstore_id = {drugstoreId};
'''
res = insertSQL(sql1)
def addPmProdSku(prodId,prodName,huohao,skuPrice,skuFee,drugstoreId,spec,skuStatus='0',skuActivate='0',skuRemark='',skuType='normal',sku_sum_flag='0'):
"""创建商品sku 普通商品（如需创建组合商品需扩展后面的字段）"""
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`
)
VALUES ( '{prodId}', '{skuStatus}', '{skuPrice}', '{skuFee}', '{drugstoreId}', '1', NOW(),NOW()
, '{skuRemark}', '', '{prodName}', '{huohao}', '1', '{{"prod_spec":"{spec}"}}', NULL, NULL, '0'
, '{skuActivate}', NULL, '{sku_sum_flag}', '0', '0', NULL, '{skuType}');
"""
res = insertSQL(sql)
return res['lastId']
def addPmActSale(tableName,drugstoreId,startTime,endTime):
'''创建活动价格 fee price
'''
# for drugstoreId in self.drugstoreIdList:
sql1 = '''
INSERT INTO `medstore`.`pm_sku_sale` ( `sku_id`, `sale_fee`, `sale_price`, `sale_start_time`, `sale_end_time`, `sale_status`, `sale_create_time`, `sale_update_time`, `sale_remark`)
SELECT s.`sku_id`,a.fee*100 `sale_fee`,a.price*100 `sale_price`
,'{}' `sale_start_time`,'{}' `sale_end_time`
,0 `sale_status`,NOW() `sale_create_time`,NOW() `sale_update_time`,'' `sale_remark`
from {} a,pm_prod_sku s
WHERE a.huohao = s.pharmacy_huohao
and s.drugstore_id = {}
'''.format(startTime,endTime,tableName,drugstoreId)
res = insertSQL(sql1)
return res['code']
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 addAmActInfo(actId,actName,startTime,endTime,drugstoreId='200'):
logging.info('创建活动主体，包括活动名，开始结束时间')
list = queryTable('am_act_info',result='*',where =f' act_id="{actId}"')
if len(list)>0:
return ''
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', '', '', '', '{drugstoreId}');
'''
res =insertSQL(sql)
if res['code']==200:
# res = insertSQL(sql)
logging.info('创建活动成功')
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 addAmActItem(itemName,itemDesc,itemType,actId,drugstoreId,img='',flag=''):
'''创建 range item 通过目录id来创建
'''
# itemName = dic['item_name']
# itemDesc = dic['item_desc']
# itemType = dic['item_type']
sql = f'''
INSERT INTO `medstore`.`am_act_item` ( `item_attr`, `item_name`, `act_id`
, `item_status`, `item_priority`, `item_type`, `item_desc`, `item_update_time`
, `item_create_time`, `item_remark`, `item_img`, `pharmacy_id`, `item_flag`
, `sales_goto_type`, `sales_goto_title`, `sales_goto_url`, `item_num`, `item_title`
, `activity_img`, `activity_img_ratio`)
VALUES ('single', '{itemName}', '{actId}', '1', '90', '{itemType}','{itemDesc}', NOW(), NOW()
,'', '{img}', '{drugstoreId}', '{flag}', '', '', '', '0', '', '', '')
;
'''
res = insertSQL(sql)
return res['lastId']
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 addPmDirInfo(sufDirCode,dirName,drugstoreId,img='',color='',num='1',level='1',parentDirId=''):
logging.debug('根据提供的盘货信息，创建展示目录 dir ')
genCode = queryBaseDirCode(drugstoreId)
dirCode = genCode+sufDirCode
sql = f'''
INSERT INTO `medstore`.`pm_dir_info` ( `dir_code`, `dir_name`, `dir_type`, `dir_status`
, `dir_revalue`, `prod_sum`, `dir_num`, `dir_level`, `dir_img`, `parent_dir_id`, `dir_update_time`
, `dir_create_time`, `dir_remark`, `dir_all_name`, `category_id`, `pharmacy_id`, `dir_gotocata`, `dir_main_show`)
value ('{dirCode}','{dirName}','dir','1',NULL,100,'{num}',{level},'{img}','{parentDirId}',NOW(),NOW(),'{color}','',NULL,'{drugstoreId}',NULL,NULL) ;
'''
res = insertSQL(sql)
if res["code"]==200:
return {
'dir_id':res['lastId'],
'dir_code':dirCode,
'dir_name':dirName,
'dir_level':level,
'pharmacy_id':drugstoreId
}
return None
def addAmStockLimit(skuId,itemId,quotaId,skuTotal,maxTotal,remark=''):
"""添加到活动中去"""
sql = f"""INSERT INTO `medstore`.`am_stock_limit` ( `item_id`, `quota_id`, `sku_id`, `sku_total`, `as_remark`, `max_total`)
VALUES ( '{itemId}', '{quotaId}', '{skuId}', '{skuTotal}', '{remark}', '{maxTotal}');"""
res = insertSQL(sql)
return res['lastId']
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 addPmSkuDir(skuId,dirId,dirCode,order='1'):
sql = f"""INSERT INTO `medstore`.`pm_sku_dir` ( `dir_id`, `sku_id`, `dir_code`, `sku_order`, `update_time`)
VALUES ( '{dirId}', '{skuId}', '{dirCode}', '{order}', now());"""
res = insertSQL(sql)
return res['lastId']