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']