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()