def add_child_area_in_one_category3(self, level_id, end_width, display_goods_list):
"""
创建子区域
:param level_id:
:param end_width:
:param display_goods_list:
:return:
"""
self.end_level_id = level_id
self.end_width = end_width
category2 = display_goods_list[0].goods_data.category2
category3 = display_goods_list[0].goods_data.category3
if self.category2 is None:
self.category2 = category2
else:
if self.category2 != category2:
msg = '同三级分类的商品二级分类不一样：{}'.format(category3)
print(msg)
dingtalk.send_message(msg, 2)
raise ValueError(msg)
if category3 not in self.category3_list:
self.category3_list.append(category3)
start_width = end_width
for display_goods in display_goods_list:
start_width -= display_goods.goods_data.width*display_goods.face_num
self.child_area_list.append(ChildArea(level_id, start_width, display_goods_list))
def calculate_result(self):
"""
:param threshold: 最大排列数的阈值
:return:
"""
if self.children is not None:
for child in self.children:
if child.children is not None:
child.calculate_result()
self.result_list = []
# temp_result = arrange_all(self.children)
iter = itertools.permutations(self.children, len(self.children)) # 所有排列的生成器
list_len = len(self.children)
if list_len > 8:
msg = '候选解太多：{}'.format(str(self))
dingtalk.send_message(msg, 2)
raise ValueError(msg)
max_length = reduce(lambda x, y: x * y, range(1, list_len + 1)) # 阶乘
if max_length > self.one_category_combination_threshhold: # 如果大于阈值，则根据步长设置进行下采样
step_size = math.ceil(max_length / self.one_category_combination_threshhold)
else:
step_size = 1
j = -1
for i, one_result in enumerate(iter):
# if random.random() > 1 // step_size: # 进行下采样
# continue
# else:
last_category_tree = None
is_valid = True
for category_tree in one_result:
if last_category_tree is not None:
if last_category_tree.level_value is None and category_tree.level_value == 0:
is_valid = False
break
if category_tree.level_value is None and last_category_tree.level_value == 10:
is_valid = False
break
if last_category_tree.level_value is not None and category_tree.level_value is not None and last_category_tree.level_value > category_tree.level_value:
is_valid = False
break
last_category_tree = category_tree
if is_valid:
j += 1
if j % step_size == 0: # 进行下采样
self.result_list.append(one_result)
if len(self.result_list) == 0:
msg = '货架层级规则导致没有有效解: {}'.format(str(self))
dingtalk.send_message(msg, 2)
raise ValueError(msg)
def _level_add_goods(candidate_shelf, input_level, goods, last_goods):
"""
处理层添加和层的width和height变化
:param candidate_shelf:
:param input_level:
:param goods:
:param last_goods: 上一个goods
:return: 商品添加的层
"""
cur_level = input_level
display_goods = display_taizhang.DisplayGoods(goods)
if cur_level is None:
# 初始陈列
cur_level = display_taizhang.Level(candidate_shelf, 0,
candidate_shelf.shelf.bottom_height,
True)
# 陈列商品 FIXME 需要处理陈列同商品跨层拆分
success = cur_level.display_goods(display_goods)
if not success:
# FIXME 需要考虑整层无法摆下的拆分
if cur_level != input_level:
msg = '无法成列商品，商品在一层无法摆下！'
dingtalk.send_message(msg, 2)
print(msg)
raise ValueError(msg)
# 无法陈列商品
cur_level = display_taizhang.Level(
candidate_shelf, cur_level.level_id + 1, cur_level.start_height +
cur_level.goods_height + candidate_shelf.shelf.level_buff_height +
candidate_shelf.shelf.level_board_height,
bool(1 - cur_level.is_left_right_direction))
cur_level.display_goods(display_goods)
if goods.is_spu(last_goods):
candidate_shelf.badcase_value += 0.3 # 计算spu badcase
else:
candidate_shelf.badcase_value += abs(
goods.height_diff(last_goods)) * 0.02 # 计算同层板相邻品高度差 badcase
if last_goods is not None and goods.category3 == last_goods.category3:
candidate_shelf.badcase_value += abs(
goods.height_diff(last_goods)) * 0.2 # 计算同三级分类相邻品高度差 badcase
return cur_level
def generate_workflow_displays(uc_shopid, batch_id):
"""
自动陈列一个批次流程的所有台账
:param uc_shopid: ucentor的shopid
:param batch_id: 流程的批次号
:return:
"""
cursor = connections['ucenter'].cursor()
# 获取台账
try:
# FIXME 只获取有指定三级分类的台账
cursor.execute(
"select t.id, t.shelf_id from sf_shop_taizhang st, sf_taizhang t where st.taizhang_id=t.id and st.shop_id = {}"
.format(uc_shopid))
taizhangs = cursor.fetchall()
except:
msg = '获取台账失败：{}！'.format(uc_shopid)
print(msg)
dingtalk.send_message(msg)
raise ValueError(msg)
finally:
cursor.close()
# 计算陈列
taizhang_display_list = []
for one_tz_id in taizhangs:
# 获取上次陈列
old_display_id = None
close_old_connections()
cursor = connections['ucenter'].cursor()
try:
cursor.execute(
"select id from sf_taizhang_display where taizhang_id={} and status in (1,2,3) and approval_status=1 order by start_datetime desc limit 1"
.format(one_tz_id[0]))
(old_display_id, ) = cursor.fetchone()
except Exception as e:
# traceback.print_exc()
print("没有找到已有陈列:{}".format(one_tz_id[0]))
finally:
cursor.close()
generate_displays(uc_shopid, one_tz_id[0], batch_id, old_display_id)
def arrange_goods(self):
candidate_shelf = self.shelf.copy_raw()
cur_level = None
for area in self.area_list:
cur_level_id = area.start_level_id
start_width = area.start_width
if len(area.up_choose_goods_list) == 0 and len(
area.down_display_goods_list) == 0:
# 如果没有上下架情况就完全按原来区域样子摆
# FIXME 上一个区域如果出现强行摆并超出需要处理
for child_area in area.child_area_list:
if child_area.level_id != cur_level_id:
cur_level_id = child_area.level_id
cur_level = candidate_shelf.levels[cur_level_id]
for display_goods in child_area.display_goods_list:
cur_level.add_display_goods(display_goods)
else:
one_area_display_goods_list = area.best_display_goods_list
for display_goods in one_area_display_goods_list:
goods_width = display_goods.goods_data.width * display_goods.face_num
cur_level = candidate_shelf.levels[cur_level_id]
if start_width + goods_width > candidate_shelf.width + self.level_max_width_tolerance: # 加固定的容差
cur_level_id += 1
if cur_level_id >= len(candidate_shelf.levels):
msg = "陈列商品超出货架，陈列到此结束:{}".format(cur_level_id)
dingtalk.send_message(msg, 2)
print(msg)
return candidate_shelf
cur_level = candidate_shelf.levels[cur_level_id]
cur_level.add_display_goods(display_goods)
start_width = goods_width
else:
cur_level.add_display_goods(display_goods)
start_width += goods_width
if start_width < area.end_width:
cur_level.add_display_goods(
DisplayGoods(
NullGoodsData(area.end_width - start_width)))
return candidate_shelf
def order_process():
conn = connections['default']
cursor_ai = conn.cursor()
# 获取日常订单
cursor_ai.execute(sql_workflow)
first_flow_data = cursor_ai.fetchall()
if first_flow_data is not None and len(first_flow_data) > 0:
for data in first_flow_data:
id = data[0]
batch_id = data[1]
erp_warehouse_id = data[3]
uc_shopid = data[3]
type = data[4]
status = data[5]
create_time = str(data[6])
msg = "订货计算异常，batch_id={},erp_warehouse_id={},uc_shopid={},type={},status={},create_time={}".format(
batch_id, erp_warehouse_id, uc_shopid, type, status,
create_time)
print(msg)
send_message(msg, 3)
cursor_ai.close()
conn.close()
def generate_displays(uc_shopid, tz_id, batch_id, old_display_id=None):
"""
自动陈列一个批次流程的指定台账
:param uc_shopid: ucentor的shopid
:param tz_id: 台账id
:return: taizhang_display对象，如果为None则说明生成失败
"""
if old_display_id is None:
print("begin generate_first_displays:{},{},{}".format(
uc_shopid, tz_id, batch_id))
else:
print("begin generate_replace_displays:{},{},{},{}".format(
uc_shopid, tz_id, batch_id, old_display_id))
close_old_connections()
# 初始化基础数据
base_data = db_data.init_base_data(uc_shopid, batch_id)
# 创建陈列在ai系统的数据记录
shelf_display_debug_model = ShelfDisplayDebug.objects.create(
uc_shopid=uc_shopid, batch_id=batch_id, tz_id=tz_id)
try:
# 初始化台账数据
if old_display_id is None:
taizhang_display = first_db_display_data.init_display_data(
uc_shopid, tz_id, base_data)
else:
taizhang_display = replace_db_display_data.init_display_data(
uc_shopid, tz_id, old_display_id, base_data)
taizhang_display.display()
# 打印陈列图
try:
image_name = taizhang_display.to_image(taizhang_display.image_dir)
shelf_display_debug_model.display_source = os.path.join(
taizhang_display.image_relative_dir, image_name)
image_old_name = taizhang_display.to_old_image(
taizhang_display.image_dir)
if image_old_name is not None:
shelf_display_debug_model.old_display_source = os.path.join(
taizhang_display.image_relative_dir, image_old_name)
except Exception as e:
print('陈列图生成错误：{}'.format(e))
traceback.print_exc()
# 更新陈列在ai系统的数据记录
shelf_display_debug_model.json_ret = json.dumps(
taizhang_display.to_json())
shelf_display_debug_model.calculate_time = taizhang_display.display_calculate_time
shelf_display_debug_model.save()
print("Success:{},{}".format(uc_shopid, tz_id))
return taizhang_display
except Exception as e:
# 更新陈列在ai系统的数据记录
msg = "自动陈列错误:{},{},{}。错误原因：{}".format(uc_shopid, tz_id, batch_id, e)
shelf_display_debug_model.json_ret = str(e)
shelf_display_debug_model.save()
traceback.print_exc()
print(msg)
dingtalk.send_message(msg, 2)
return None
def get_can_order_dict(self):
"""
获取可订货的7位得mch_goods_code的字典，value为配送类型，k为店内码,从saas查询
筛掉商圈的标签：trade_tag_str
:return:
"""
self.cursor.execute(
"SELECT erp_shop_id from erp_shop_related WHERE shop_id ={} AND erp_shop_type=1;"
.format(self.shop_id))
try:
erp_shop_id = self.cursor.fetchone()[0]
print("erp_shop_id", erp_shop_id)
except Exception as e:
send_message('pos店号是{},erp_shop_id获取失败:{}'.format(self.shop_id, e))
raise Exception('pos店号是{},erp_shop_id获取失败:{}'.format(
self.shop_id, e))
# return []
# 获取商品的 可定 配送类型
can_order_list = [] # 可订货列表
can_order_new_list = [] # 可订货列表(必上和可选上用这个)
delivery_type_dict = {} # 店内码是key，配送类型是value
delivery_type_new_dict = {} # 店内码是key，配送类型是value(必上和可选上用这个)
try:
self.cursor_ucenter.execute(
"SELECT supplier_id from uc_warehouse_supplier_shop WHERE warehouse_id={}"
.format(erp_shop_id))
all_supplier_id_data = self.cursor_ucenter.fetchall()
for supplier_data in all_supplier_id_data:
self.supplier_id_list.append(str(supplier_data[0]))
print("supplier_id_list", self.supplier_id_list)
if not self.supplier_id_list:
send_message(
"pos店号是{},查询supplier_id为空，sql为'SELECT supplier_id from uc_warehouse_supplier_shop WHERE warehouse_id={}'"
.format(self.shop_id, erp_shop_id))
raise Exception(
"pos店号是{},查询supplier_id为空，sql为'SELECT supplier_id from uc_warehouse_supplier_shop WHERE warehouse_id={}'"
.format(self.shop_id, erp_shop_id))
self.cursor_ucenter.execute(
# 有尺寸数据
"select DISTINCT a.supplier_goods_code,b.delivery_attr,c.display_second_cat_id from uc_supplier_goods a LEFT JOIN uc_supplier_delivery b on a.delivery_type=b.delivery_code LEFT JOIN uc_merchant_goods c on (a.supplier_goods_code=c.supplier_goods_code and a.supplier_id=c.supplier_id) where a.supplier_id in ({}) and order_status = 1 and c.width > 0 and c.height > 0 and c.depth > 0 and c.display_third_cat_id > 0"
.format(','.join(self.supplier_id_list)))
all_data = self.cursor_ucenter.fetchall()
# print("可订货数据：",all_data)
for data in all_data:
can_order_list.append(data[0])
if data[2] == "104": # 巧克力分类 ,按照非日配逻辑来处理
delivery_type_dict[data[0]] = 2
continue
delivery_type_dict[data[0]] = data[1]
# 以下是把商圈不选的筛掉，仅为必上和可选上等新品服务
self.cursor_ucenter.execute(
# 有尺寸数据
"select DISTINCT a.supplier_goods_code,b.delivery_attr,c.display_second_cat_id from uc_supplier_goods a LEFT JOIN uc_supplier_delivery b on a.delivery_type=b.delivery_code LEFT JOIN uc_merchant_goods c on (a.supplier_goods_code=c.supplier_goods_code and a.supplier_id=c.supplier_id) where a.supplier_id in ({}) and order_status = 1 and c.width > 0 and c.height > 0 and c.depth > 0 {} and c.display_third_cat_id > 0"
.format(','.join(self.supplier_id_list), self.trade_tag_str))
all_data = self.cursor_ucenter.fetchall()
# print("可订货数据：",all_data)
for data in all_data:
can_order_new_list.append(data[0])
if data[2] == "104": # 巧克力分类 ,按照非日配逻辑来处理
delivery_type_new_dict[data[0]] = 2
continue
delivery_type_new_dict[data[0]] = data[1]
except Exception as e:
send_message('pos店号是{},查询是否可订货和配送类型失败,{}'.format(self.shop_id, e))
raise Exception('pos店号是{},查询是否可订货和配送类型失败,{}'.format(
self.shop_id, e))
if not can_order_list:
send_message('pos店号是{},查询是否可订货数据为空'.format(self.shop_id))
raise Exception('pos店号是{},查询是否可订货数据为空'.format(self.shop_id))
return can_order_list, delivery_type_dict, can_order_new_list, delivery_type_new_dict
def init_display_data(uc_shopid, tz_id, old_display_id, base_data):
"""
初始化陈列相关的台账、货架、指定分类数据，最终生成初始化好的taizhang_display对象
:param uc_shopid: ucentor系统shopid
:param tz_id:
:param old_display_id:
:param base_data:
:return: taizhang_display对象
"""
taizhang_display = TaizhangDisplay(tz_id)
cursor = connections['ucenter'].cursor()
# 获取fx系统的shopid,台账系统的商家mch_id
cursor.execute("select mch_shop_code,mch_id from uc_shop where id = {}".format(uc_shopid))
(shopid, mch_id) = cursor.fetchone()
# 获取台账信息
try:
cursor.execute(
"select t.id, t.shelf_id, t.shelf_count from sf_taizhang t where t.id = {}".format(tz_id))
(taizhang_id, shelf_id, count) = cursor.fetchone()
except:
msg = '获取台账失败:{},{}'.format(uc_shopid, tz_id)
print(msg)
cursor.close()
dingtalk.send_message(msg, 2)
raise ValueError(msg)
# 获取陈列信息
cursor.execute("select display_goods_info, display_shelf_info from sf_taizhang_display where id = {} and taizhang_id = {}".format(old_display_id, tz_id))
(display_goods_info, display_shelf_info) = cursor.fetchone()
cursor.execute(
"select t.shelf_no,s.length,s.height,s.depth from sf_shelf s, sf_shelf_type t where s.shelf_type_id=t.id and s.id={}".format(
shelf_id))
(shelf_no, length, height, depth) = cursor.fetchone()
display_shelf_info = json.loads(display_shelf_info)
display_goods_info = json.loads(display_goods_info)
cursor.close()
# TODO 需要支持多个货架
shelfs = display_shelf_info['shelf']
shelf_dict = {}
goods_array_dict = {}
for shelf in shelfs:
shelf_dict[shelf['shelfId']] = shelf['layer']
for goods_info in display_goods_info:
goods_array_dict[goods_info['shelfId']] = goods_info['layerArray']
category3_list = []
for shelfId in shelf_dict.keys():
shelf_array = shelf_dict[shelfId]
goods_array = goods_array_dict[shelfId]
shelf = Shelf(shelf_id, height, length, depth)
for i in range(len(shelf_array)):
level = shelf_array[i]
goods_level_array = goods_array[i]
# floor_type 1：普通陈列 2：挂层
level = Level(shelf, i, round(float(level['height'])), round(float(level['depth'])))
shelf.add_level(level)
goods_data_to_left_top = {}
goods_level_array.sort(key=lambda x:x['left'])
for goods_info in goods_level_array:
goods_data = find_goods(goods_info['mch_goods_code'], base_data.goods_data_list)
if goods_data is None:
msg = '选品表中有陈列商品不存在：{}'.format(goods_info['mch_goods_code'])
print(msg)
dingtalk.send_message(msg, 2)
raise ValueError('选品表中有陈列商品不存在')
# FIXME 修改陈列方式和长宽深
if goods_info['layout'] != 1:
goods_data.width = goods_info['p_width']
goods_data.height = goods_info['p_height']
goods_data.depth = goods_info['p_depth']
goods_data.layout = goods_info['layout']
if goods_data in goods_data_to_left_top:
goods_data_to_left_top[goods_data].append((goods_info['left'], goods_info['top']))
else:
goods_data_to_left_top[goods_data] = [(goods_info['left'], goods_info['top'])]
for goods_data in goods_data_to_left_top.keys():
left_list = []
top_list = []
for left_top in goods_data_to_left_top[goods_data]:
left = int(left_top[0])
top = int(left_top[1])
if left not in left_list:
left_list.append(left)
if top not in top_list:
# 考虑摆放误差
min_diff = goods_data.height
for one_top in top_list:
if abs(one_top-top) < min_diff:
min_diff = abs(one_top-top)
if min_diff > int(goods_data.height/2):
top_list.append(top)
display_goods = DisplayGoods(goods_data,face_num=len(left_list),superimpose_num=len(top_list))
level.add_display_goods(display_goods)
if goods_data.category3 not in category3_list:
category3_list.append(goods_data.category3)
# FIXME 目前只支持一个货架
choose_goods_list = filter_goods_data(category3_list, base_data.goods_data_list)
taizhang_display.init_data(shelf, category3_list, choose_goods_list)
return taizhang_display
def data_exception_alarm(shopid):
"""
获取商店的所有货架及货架上的商品信息，进行数据异常报警
:param shopid: fx系统的商店id
"""
ret = []
cursor = connections['ucenter'].cursor()
cursor_dmstore = connections['dmstore'].cursor()
cursor_erp = connections['erp'].cursor()
cursor_ai = connections['default'].cursor()
try:
cursor_bi = connections['bi'].cursor()
except:
cursor_bi = None
# 获取台账系统的uc_shopid
cursor.execute(
'select id, shop_name , mch_id from uc_shop where mch_shop_code = {}'.
format(shopid))
(uc_shopid, shop_name, mch_id) = cursor.fetchone()
if not uc_shopid:
send_message('{}在台账系统找不到对应的shopid！'.format(shopid))
# 获取erp系统的erp_shopid
erp_resupply_id = None
erp_supply_id = None
try:
cursor_dmstore.execute(
"select erp_shop_id from erp_shop_related where shop_id = {} and erp_shop_type = 0"
.format(shopid))
(erp_shop_id, ) = cursor_dmstore.fetchone() # 门店id
print("erp 门店id" + str(erp_shop_id))
cursor_erp.execute(
"SELECT authorized_shop_id from ms_relation WHERE is_authorized_shop_id = {} and status=1"
.format(erp_shop_id))
(erp_supply_id, ) = cursor_erp.fetchone() # 仓库id
print("erp 仓库id" + str(erp_supply_id))
cursor_erp.execute(
"SELECT authorized_shop_id from ms_relation WHERE is_authorized_shop_id = {} and status=1"
.format(erp_supply_id))
(erp_resupply_id, ) = cursor_erp.fetchone() # 供货商id
print("erp 供货商id" + str(erp_resupply_id))
if erp_shop_id is None or erp_supply_id is None or erp_resupply_id is None:
send_message('pos店号为{}的店，获取的erp_shop_id异常：{}'.format(shopid, None),
3)
except:
send_message('pos店号为{}的店，获取的erp_shop_id异常：{}'.format(shopid, None), 3)
# 获取台账 TODO 只能获取店相关的台账，不能获取商家相关的台账
cursor.execute(
"select t.id, t.shelf_id, td.display_shelf_info, td.display_goods_info from sf_shop_taizhang st, sf_taizhang t, sf_taizhang_display td where st.taizhang_id=t.id and td.taizhang_id=t.id and td.status in (1,2) and td.approval_status=1 and st.shop_id = {}"
.format(uc_shopid))
taizhangs = cursor.fetchall()
for taizhang in taizhangs:
taizhang_id = taizhang[0]
shelf_id = taizhang[1]
shelf_type = ''
shelf_type_id = None
try:
cursor.execute(
"select id,shelf_type_id from sf_shelf where id = {}".format(
shelf_id))
(id, shelf_type_id) = cursor.fetchone()
except:
print("台账找不到货架 ， shelf_id=" + str(shelf_id))
traceback.print_exc()
try:
cursor.execute(
"select id,type_name from sf_shelf_type where id = {} ".format(
shelf_type_id))
(id, type_name) = cursor.fetchone()
shelf_type = type_name
except:
print("台账找不到货架类型名称 ， shelf_type_id=" + str(shelf_type_id), 3)
traceback.print_exc()
display_shelf_info = taizhang[2]
display_goods_info = taizhang[3]
display_shelf_info = json.loads(display_shelf_info)
display_goods_info = json.loads(display_goods_info)
shelfs = display_shelf_info['shelf']
shelf_dict = {}
goods_array_dict = {}
for shelf in shelfs:
shelf_dict[shelf['shelfId']] = shelf['layer']
for goods_info in display_goods_info:
goods_array_dict[goods_info['shelfId']] = goods_info['layerArray']
for shelfId in shelf_dict.keys():
level_array = shelf_dict[shelfId]
goods_array = goods_array_dict[shelfId]
for i in range(len(level_array)):
level = level_array[i]
goods_level_array = goods_array[i]
level_depth = round(float(level['depth']))
if level_depth is None or level_depth <= 0:
send_message(
'uc店号为{}、id为{}的货架的第{}层深度异常为{}'.format(
uc_shopid, shelfId, i + 1, level_depth), 3)
for goods in goods_level_array:
mch_code = goods['mch_goods_code']
# time.sleep(5)
if mch_code in ret:
print("该商品已查询过")
# elif mch_code != "2027047":
# print('haha')
# continue
else:
ret.append(mch_code)
# 从台账获取商品属性
try:
print(shelf_id)
p_depth = goods["p_depth"]
if p_depth is None or p_depth <= 0:
send_message(
"台账id为{}，货架id为{}，mch_goods_code为{}的商品p_depth属性异常：{}"
.format(taizhang_id, shelf_id, mch_code,
p_depth), 3)
except:
send_message(
"台账id为{}，货架id为{}，mch_goods_code为{}的商品没有p_depth属性"
.format(taizhang_id, shelf_id, mch_code), 3)
# continue
# 从库里获取商品属性
try:
cursor.execute(
"select id, goods_name,upc, tz_display_img, spec, volume, width,height,depth,is_superimpose,is_suspension,display_first_cat_id,display_second_cat_id,display_third_cat_id,storage_day,package_type,supplier_id,supplier_goods_code from uc_merchant_goods where mch_id = {} and mch_goods_code = {}"
.format(mch_id, mch_code))
(goods_id, goods_name, upc, tz_display_img, spec,
volume, height, width, depth, is_superimpose,
is_suspension, display_first_cat_id,
display_second_cat_id, display_third_cat_id,
storage_day, package_type, supplier_id,
supplier_goods_code) = cursor.fetchone()
if upc is None or display_third_cat_id is None or display_third_cat_id == 0 or display_third_cat_id == "0" or storage_day is None or storage_day <= 0 or depth <= 0:
send_message(
'mch_code为{}的商品存在空字段或异常值，upc:{}，陈列三级分类(display_third_cat_id):{}，保质期(storage_day):{}，陈列时所占深度(p_depth):{}'
.format(mch_code, upc,
display_third_cat_id, storage_day,
depth), 3)
print(
'mch_code为{}的商品存在空字段，upc:{}，陈列三级分类(display_third_cat_id):{}，保质期(storage_day):{}，陈列时所占深度(p_depth):{}'
.format(mch_code, upc,
display_third_cat_id, storage_day,
depth))
except:
send_message(
'mch_code为{}的商品在库里找不到'.format(mch_code), 3)
continue
# 获取最大陈列系数
# 单价和库存
try:
cursor_dmstore.execute(
"select id,price,purchase_price,stock FROM shop_goods where upc = '{}' and shop_id = {} order by modify_time desc "
.format(upc, shopid))
(id, upc_price, purchase_price,
stock) = cursor_dmstore.fetchone()
if upc_price is None or upc_price <= 0:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品单价异常:{}'
.format(goods_name, uc_shopid, mch_code,
upc, upc_price), 3)
if stock is None or stock < 0:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品库存异常:{}'
.format(goods_name, uc_shopid, mch_code,
upc, stock), 3)
except:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>库存和单价异常为：{}、{}'
.format(goods_name, uc_shopid, mch_code, upc,
None, None), 3)
# 获取起订量
# if erp_resupply_id is not None:
# try:
# # 获取起订量
# # "select start_sum,multiple from ms_sku_relation where ms_sku_relation.status=1 and sku_id in (select sku_id from ls_sku where model_id = '{0}' and ls_sku.prod_id in (select ls_prod.prod_id from ls_prod where ls_prod.shop_id = {1} ))"
# cursor_erp.execute("select s.sku_id prod_id from ls_prod as p, ls_sku as s where p.prod_id = s.prod_id and p.shop_id = {} and s.model_id = '{}' AND s.party_code='{}'".format(erp_resupply_id, upc,mch_code))
# (sku_id,) = cursor_erp.fetchone()
# cursor_erp.execute("select start_sum,multiple from ms_sku_relation where ms_sku_relation.status=1 and sku_id = {}".format(sku_id))
# (start_sum, multiple) = cursor_erp.fetchone()
#
# if start_sum is None or start_sum <= 0:
# send_message('{}(uc店号:{},mch_code:{},upc:{})—>>商品起订量异常:{}'.format(goods_name, uc_shopid,mch_code, upc, start_sum),3)
# except:
# try:
# # 看是什么原因导致查询不到，一可能是好邻居码和upc不对应，二可能是不可订货
# cursor_erp.execute(
# "select s.sku_id prod_id from ls_prod as p, ls_sku as s where p.prod_id = s.prod_id and p.shop_id = {} AND s.party_code='{}'".format(
# erp_resupply_id, mch_code))
# (sku_id,) = cursor_erp.fetchone()
# if sku_id:
# cursor_erp.execute(
# "select model_id ,party_code from ls_prod where shop_id='{}' and party_code='{}';".format(
# erp_resupply_id, mch_code))
# (upc_2,) = cursor_erp.fetchone()
#
# send_message(
# '{}(uc店号:{},mch_code:{},upc:{})—>>商品mch_code和upc不对应，该mch_code查出的upc为{}'.format(goods_name, uc_shopid,
# mch_code, upc,upc_2), 3)
# except:
# send_message(
# '{}(uc店号:{},mch_code:{},upc:{})—>>商品在好邻居不可订货'.format(goods_name, uc_shopid,
# mch_code, upc),3)
# 获取商品的 可定 起订量 配送类型
if erp_resupply_id is not None:
try:
cursor.execute(
"select min_order_num,order_status,delivery_type from uc_supplier_goods where supplier_id = {} and supplier_goods_code = {} and order_status = 1 "
.format(supplier_id, supplier_goods_code))
(start_sum, order_status,
delivery_type_str) = cursor.fetchone()
cursor.execute(
"select delivery_attr from uc_supplier_delivery where delivery_code = '{}' "
.format(delivery_type_str))
(delivery_type, ) = cursor.fetchone()
if start_sum is None or start_sum <= 0:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品起订量异常:{}'
.format(goods_name, uc_shopid,
mch_code, upc, start_sum), 3)
if order_status is None or order_status not in (
1, 2, '1', '2'):
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品是否可订货数据异常:{}'
.format(goods_name, uc_shopid,
mch_code, upc, order_status),
3)
if delivery_type is None or delivery_type not in (
1, 2, '1', '2'):
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品配送类型数据异常:{}'
.format(goods_name, uc_shopid,
mch_code, upc, delivery_type),
3)
except:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>获取商品的是否可定、起订量、配送类型数据失败'
.format(goods_name, uc_shopid, mch_code,
upc), 3)
# 获取小仓库库存
if erp_supply_id is not None:
try:
cursor_erp.execute(
"select s.sku_id prod_id from ls_prod as p, ls_sku as s where p.prod_id = s.prod_id and p.shop_id = {} and s.model_id = '{}'"
.format(erp_supply_id, upc))
(sku_id, ) = cursor_erp.fetchone()
cursor_erp.execute(
"select stock from ms_sku_relation where ms_sku_relation.status=1 and sku_id = {}"
.format(sku_id))
(supply_stock, ) = cursor_erp.fetchone()
if supply_stock is None or supply_stock < 0:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品小仓库库存异常:{}'
.format(goods_name, uc_shopid,
mch_code, upc, supply_stock),
3)
except:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品小仓库库存异常:{}'
.format(goods_name, uc_shopid, mch_code,
upc, None), 3)
# 获取商品的上架时间、是否新品
try:
cursor_ai.execute(
"select up_shelf_date,is_new_goods from goods_up_shelf_datetime where shopid={} and upc='{}'"
.format(uc_shopid, upc))
(up_shelf_date, up_status) = cursor_ai.fetchone()
if not up_status in (0, 1):
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品的"是否新品"字段异常,is_new_goods:{}'
.format(goods_name, uc_shopid, mch_code,
upc, up_status), 3)
except:
send_message(
'{}(uc店号:{},mch_code:{},upc:{})—>>商品的"是否新品"和"上架时间"字段异常:{},{}'
.format(goods_name, uc_shopid, mch_code, upc,
None, None), 3)
# 获取商品的最小陈列量
try:
cursor_ai.execute(
"select single_face_min_disnums from goods_config_disnums where shop_id={} and shelf_id={} and upc='{}'"
.format(shopid, shelf_id, upc))
(single_face_min_disnums, ) = cursor_ai.fetchone()
if single_face_min_disnums is None or single_face_min_disnums <= 0:
send_message(
'{}(uc店号:{},货架id:{},mch_code:{},upc:{})—>>商品的最小陈列量字段异常:{}'
.format(goods_name, shelf_id, uc_shopid,
mch_code, upc,
single_face_min_disnums), 3)
except:
send_message(
'{}(uc店号:{},货架id:{},mch_code:{},upc:{})—>>商品的最小陈列量字段异常:{}'
.format(goods_name, uc_shopid, shelf_id,
mch_code, upc, None), 3)
cursor.close()
cursor_dmstore.close()
cursor_erp.close()
cursor_ai.close()
if cursor_bi is not None:
cursor_bi.close()
def init_display_data(uc_shopid, tz_id, base_data):
"""
初始化陈列相关的台账、货架、指定分类数据，最终生成初始化好的taizhang_display对象
:param uc_shopid: ucentor系统shopid
:param tz_id:
:param base_data:
:return: taizhang_display对象
"""
taizhang_display = TaizhangDisplay(tz_id)
cursor = connections['ucenter'].cursor()
# 获取fx系统的shopid,台账系统的商家mch_id
cursor.execute(
"select mch_shop_code,mch_id from uc_shop where id = {}".format(
uc_shopid))
(shopid, mch_id) = cursor.fetchone()
# 获取台账信息
try:
# cursor.execute(
# "select t.id, t.shelf_id, t.shelf_count, t.third_cate_ids from sf_shop_taizhang st, sf_taizhang t where st.taizhang_id=t.id and st.shop_id = {} and t.id = {}".format(
# uc_shopid, tz_id))
# FIXME 没有指定商店
cursor.execute(
"select t.id, t.shelf_id, t.shelf_count, t.third_cate_ids from sf_taizhang t where t.id = {}"
.format(tz_id))
(taizhang_id, shelf_id, count, third_cate_ids) = cursor.fetchone()
if third_cate_ids is None or third_cate_ids == '':
msg = '台账没有定义陈列分类:{},{},{}'.format(taizhang_id, shelf_id, count)
dingtalk.send_message(msg, 2)
raise ValueError(msg)
except:
msg = '获取台账失败：{},{}！'.format(uc_shopid, tz_id)
print(msg)
dingtalk.send_message(msg, 2)
cursor.close()
raise ValueError(msg)
# 获取货架信息
cursor.execute(
"select t.shelf_no,s.length,s.height,s.depth,s.hole_height,s.hole_distance,s.option from sf_shelf s, sf_shelf_type t where s.shelf_type_id=t.id and s.id={}"
.format(shelf_id))
(shelf_no, length, height, depth, hole_height, hole_distance,
option) = cursor.fetchone()
level_depth_list = []
try:
shelf_levels_option = json.loads(option)
for one_level_option in shelf_levels_option:
if 'floor_depth' in one_level_option:
level_depth_list.append(int(one_level_option['floor_depth']))
except:
print('货架层信息不合法：{}！'.format(option))
level_depth_list = []
# 准备陈列需要的数据
display_category3_list = third_cate_ids.split(',')
display_category3_list = list(set(display_category3_list))
category3_to_category3_obj = {}
shelf_category3_to_goods_cnt = {}
shelf_goods_data_list = []
# 检查所有三级分类
for category3 in display_category3_list:
cat_id = None
try:
cursor.execute(
"select cat_id, name, pid from ao_display_category where mch_id={} and cat_id='{}' and level=3"
.format(mch_id, category3))
(cat_id, name, pid) = cursor.fetchone()
except:
print('台账陈列类别无法找到：{}！'.format(category3))
# FIXME 目前只支持cat_id是字符串
cat_id = str(cat_id)
if cat_id is not None:
total_height = 0
# 筛选商品
for goods in base_data.goods_data_list:
if goods.category3 == cat_id and goods.goods_role != 2: # 删除品需要去掉
total_height += goods.height
shelf_goods_data_list.append(goods)
if goods.category3 in shelf_category3_to_goods_cnt:
shelf_category3_to_goods_cnt[cat_id] += 1
else:
shelf_category3_to_goods_cnt[cat_id] = 1
# 生成Category3对象数据
if cat_id in shelf_category3_to_goods_cnt:
average_height = total_height / shelf_category3_to_goods_cnt[
cat_id]
category3_to_category3_obj[cat_id] = Category3(
cat_id, name, pid, average_height)
# 根据商品筛选三级分类 FIXME 三级分类目前一定是超量的
print('总共获取的候选陈列商品: {}'.format(len(shelf_goods_data_list)))
print(shelf_category3_to_goods_cnt)
if len(shelf_goods_data_list) == 0:
msg = '首次陈列：选品表没有适合这个台账{}的商品'.format(tz_id)
raise ValueError(msg)
shelf_goods_data_list.sort(key=lambda x: x.mch_code)
for goods_data in shelf_goods_data_list:
print(goods_data)
shelf_category3_list = list(shelf_category3_to_goods_cnt.keys())
shelf_category3_list.sort()
print('总共需要陈列的分类: {}'.format(len(shelf_category3_list)))
print(shelf_category3_list)
shelf_category3_intimate_weight, shelf_category3_level_value, shelf_category3_to_category3_obj = filter_to_shelf_data(
base_data, shelf_category3_list, category3_to_category3_obj)
# 重新计算货架的三级分类比例
shelf_category3_area_ratio = calculate_shelf_category3_area_ratio(
shelf_category3_list, base_data.category_area_ratio)
# TODO 需要支持多个货架
for i in range(count):
shelf = Shelf(shelf_id, shelf_no, length, height, depth,
level_depth_list, shelf_category3_list,
shelf_category3_intimate_weight,
shelf_category3_level_value,
shelf_category3_to_category3_obj,
shelf_category3_area_ratio, shelf_goods_data_list)
taizhang_display.shelfs.append(shelf)
cursor.close()
return taizhang_display
def goods_arrange(shelf):
"""
第四步，商品布局主体函数
:param shelf:货架
:return:
"""
# 一、准备工作
# 每一个三级分类获得排序商品
extra_add_num = 2 # FIXME 冗余数量怎么定，如果没有了呢？
categoryid_to_sorted_goods_list = {}
for categoryid in shelf.shelf_category3_list:
sorted_goods_list = single_algorithm.choose_goods_for_category3(
shelf, categoryid, extra_add=extra_add_num)
categoryid_to_sorted_goods_list[categoryid] = sorted_goods_list
# print('排序商品列表：{},{}'.format(categoryid,len(sorted_goods_list)))
# input("输任意键继续")
# 设定shelf的全局计算参数
shelf.categoryid_to_sorted_goods_list = categoryid_to_sorted_goods_list
shelf.extra_add_num = extra_add_num
# 生成所有的候选解
categoryid_to_arrange_goods_list_list = {}
candidate_result_shelf_list = []
max_goods_combination = 1
for categoryid in shelf.shelf_category3_list:
arrange_goods_list_list = goods.shelfdisplay.firstdisplay.goods_arrange_category3.goods_arrange(
shelf.categoryid_to_sorted_goods_list[categoryid])
categoryid_to_arrange_goods_list_list[
categoryid] = arrange_goods_list_list
max_goods_combination *= len(arrange_goods_list_list)
print("共{}个分类解：".format(len(shelf.candidate_category_list)))
# input("按任何键继续：")
i = 0
for categoryid_list in shelf.candidate_category_list:
i += 1
candidate_shelf_list = create_candidate_shelf_list(
shelf, categoryid_list, categoryid_to_arrange_goods_list_list,
max_goods_combination)
if i % 10 == 1:
print("开始第{}个分类解（共{}个商品解）：".format(i, len(candidate_shelf_list)))
j = 0
for candidate_shelf in candidate_shelf_list:
j += 1
# print("开始第{}.{}个商品组合解：".format(i, j))
if _display_shelf(candidate_shelf):
candidate_result_shelf_list.append(candidate_shelf)
# 计算候选解的badcase得分
print('共找到{}个候选解'.format(len(candidate_result_shelf_list)))
if len(candidate_result_shelf_list) == 0:
msg = '没有找到一个候选解'
dingtalk.send_message(msg, 2)
raise ValueError(msg)
best_candidate_shelf = single_algorithm.goods_badcase_score(
candidate_result_shelf_list)
shelf.best_candidate_shelf = best_candidate_shelf
