def collect_feature_data(self,
warehouse_id=1,
id_type='sku',
start_date='2018-01-01',
end_date='2018-05-02'):
'''
将warehouse_id中所有store_id下的sku_id在日期范围内容的特征数据查找出来并写入数据库
'''
# (step 1) 查找warehouse_id下的所有store_id
sql = """
SELECT *
FROM ios_base_store
WHERE warehouse_id={}
""".format(warehouse_id)
rows = BaseModel.raw(sql)
store_id_list = [row.store_id for row in rows]
print("[INFO]:找到warehouse_id({0})下的{1}个store_id:{2}".format(
warehouse_id, len(store_id_list), store_id_list))
# (step 2) 查找store_id下的所有sku_id及上新日期
for store_rank, store_id in enumerate(store_id_list):
sql = """
SELECT
DISTINCT
{0}_id,
book_date
FROM ios_sale_up_to_new
WHERE warehouse_id={1}
AND store_id={2}
-- AND sku_id=13689
limit 5
""".format(id_type, warehouse_id, store_id)
rows = BaseModel.raw(sql)
# (step 3) 查找store_id下的所有sku_id在日期范围内的特征数据并写入数据库
# 循环所有sku_id
for sku_rank, row in enumerate(rows):
current_time = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
sku_id = row.sku_id
print(
'current_time:%s Processing %d-th sku of %d-th store:%s ... There are %d skus in total ...'
% (current_time, sku_rank + 1, store_rank + 1, sku_id,
len(rows)))
book_date = str(row.book_date) # datetime类型转为字符串类型
start_date = max(start_date, book_date)
self.feature_data_aggregation(warehouse_id=1,
store_id=1,
id=sku_id,
id_type='sku',
start_date=start_date,
end_date=end_date)
def get_demand_proportion(self):
"""
从 ios_sale_order 和 ios_sale_order_line 表中获取产品在某一短时间的销量
"""
date_end_train = datetime.datetime.strptime(self.date_end, '%Y-%m-%d')
# 上一期的结束时间
date_end_last_period = date_end_train - datetime.timedelta(1)
date_end_last_period = str(date_end_last_period)[:10]
# 上一期的开始时间
date_start_last_period = date_end_train - datetime.timedelta(
self.length_merge)
date_start_last_period = str(date_start_last_period)[:10]
sql = """
SELECT
id,
store_id,
quantity/SUM(quantity)over(partition by id) AS demand_proportion
FROM
(SELECT
ol.{1}_id AS id,
o.store_id AS store_id,
SUM(ol.quantity) AS quantity
FROM ios_sale_order AS o
INNER JOIN ios_sale_order_line AS ol
ON o.order_id=ol.order_id
INNER JOIN ios_base_store AS s
ON s.store_id=o.store_id
WHERE s.warehouse_id={0}
AND o.sale_date>='{2}'
AND o.sale_date<='{3}'
AND o.status=1
AND ol.quantity>0
GROUP BY o.store_id,ol.{1}_id
) AS t
""".format(self.warehouse_id, self.id_type, date_start_last_period,
date_end_last_period)
# print(sql)
rows = BaseModel.raw(sql)
demand_proportion_dict = dict()
if len(rows) >= 1:
for row in rows:
id = int(row.id)
store_id = int(row.store_id)
demand_proportion = round(float(row.demand_proportion), 9)
if id in demand_proportion_dict:
demand_proportion_dict[id][store_id] = demand_proportion
else:
demand_proportion_dict[id] = {}
demand_proportion_dict[id][store_id] = demand_proportion
else:
print('[INFO]:找不到warehouse_id({})的销量分摊比例信息!!!'.format(
self.warehouse_id))
# print(demand_proportion_dict)
return demand_proportion_dict
def get_on_hand_quantity(self,
warehouse_id=1,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_base_supplier_product 表中获取产品在某一天的库存:0-无库存,1-有库存
"""
sql = """
SELECT
stock_on_hand.{2}_id AS id,
stock_on_hand.on_hand_quantity AS on_hand_quantity,
Date(stock_on_hand.book_date) AS book_date,
row_number() over(partition by stock_on_hand.{2}_id ORDER BY stock_on_hand.book_date DESC) AS rank
FROM ios_stock_on_hand AS stock_on_hand
INNER JOIN ios_base_warehouse_location AS warehouse_location
ON warehouse_location.location_id = stock_on_hand.location_id
INNER JOIN ios_base_warehouse AS warehouse
ON warehouse.warehouse_id = warehouse_location.warehouse_id
INNER JOIN ios_base_store AS store
ON store.warehouse_id = warehouse.warehouse_id
WHERE
store.store_id = {0}
AND warehouse_location.location_type=1
AND stock_on_hand.{2}_id={3}
AND stock_on_hand.book_date<='{1}'
limit 1
""".format(store_id, book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) == 1:
on_hand_quantity = 1 if int(rows[0].on_hand_quantity) > 0 else 0
res_dict = [{
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'on_hand_quantity',
'feature_type': 0,
'feature_value': on_hand_quantity
}]
else:
res_dict = None
print('[ERROR]:找不到store_id({3})的{0}_id({1})在{2}的产品库存信息!!!'.format(
id_type, id, book_date, store_id))
return res_dict
def get_store_id_list_by_warehouse_ids(self,warehouse_id_list=None):
# 若为总仓, 则获取所有门店的数据
# 若为地区仓, 则获取改该地区仓下的所有门店数据
# ios_base_store
if len(warehouse_id_list)==1:
warehouse_id_list_2sql='({})'.format(warehouse_id_list[0])
else:
warehouse_id_list_2sql='{}'.format(tuple(warehouse_id_list))
sql='''
SELECT *
FROM ios_base_store
WHERE warehouse_id in {}
'''.format(warehouse_id_list_2sql)
stores = BaseModel.raw(sql)
if not stores:
return None
store_id_list = [store.store_id for store in stores]
return store_id_list # 返回门店列表,比如:[2,1]
def get_sale_price(self,
warehouse_id=None,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_base_price_change 表中获取产品在某一天的销售价
"""
sql = """
SELECT
new_sale_price
FROM ios_base_price_change
WHERE store_id={0}
AND start_date<='{1}' AND end_date>='{1}'
AND {2}_id={3}
""".format(store_id, book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) == 1:
sale_price = round(float(rows[0].new_sale_price), 2)
res_dict = [{
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'sale_price',
'feature_type': 0,
'feature_value': sale_price
}]
else:
res_dict = None
if len(rows) == 0:
print('[ERROR]:找不到store_id({3})的{0}_id({1})在{2}的销售价!!!'.format(
id_type, id, book_date, store_id))
if len(rows) > 1:
print(
'[ERROR]:找到store_id({3})的{0}_id({1})在{2}的多个销售价!!!'.format(
id_type, id, book_date, store_id))
return res_dict
def get_sale_quantity(self,
warehouse_id=1,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_sale_order 和 ios_sale_order_line 表中获取产品在某一天的销量
"""
sql = """
SELECT
ol.{2}_id AS id,
SUM(ol.quantity) AS quantity
FROM ios_sale_order AS o
INNER JOIN ios_sale_order_line AS ol
ON o.order_id=ol.order_id
INNER JOIN ios_base_store AS s
ON s.store_id=o.store_id
WHERE o.store_id={0} AND o.sale_date='{1}' AND ol.{2}_id={3}
AND o.status=1
AND ol.status=1
AND ol.quantity>0
GROUP BY ol.{2}_id
""".format(store_id, book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) == 1:
quantity = round(float(rows[0].quantity), 2)
else:
# print('[INFO]:找不到store_id({3})的{0}_id({1})在{2}的销量信息!!!'.format(id_type,id,book_date,store_id))
quantity = 0
res_dict = [{
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'quantity',
'feature_type': 0,
'feature_value': quantity
}]
return res_dict
def get_demand_class_data(self):
'''
获取需求分类数据
'''
sql = """
SELECT
DISTINCT
{1}_id AS id,
demand_class
FROM ios_optimization_demand_classification
WHERE warehouse_id={0}
""".format(self.warehouse_id, self.id_type)
rows = BaseModel.raw(sql)
demand_class_dict = dict()
for row in rows:
id = str(row.id)
demand_class = int(row.demand_class)
demand_class_dict[id] = demand_class
if not demand_class_dict: # 字典为空
raise Exception('获取产品需求分类数据为空!!!')
return demand_class_dict
def get_purchase_price(self,
warehouse_id=1,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_base_supplier_product 表中获取产品在某一天的采购价
"""
sql = """
SELECT
purchase_price
FROM ios_base_supplier_product_relation
WHERE start_date<='{0}' AND end_date>='{0}'
AND {1}_id={2}
""".format(book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) == 1:
purchase_price = round(float(rows[0].purchase_price), 2)
res_dict = [{
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'purchase_price',
'feature_type': 0,
'feature_value': purchase_price
}]
else:
res_dict = None
if len(rows) == 0:
print('[ERROR]:找不到store_id({3})的{0}_id({1})在{2}的采购价!!!'.format(
id_type, id, book_date, store_id))
if len(rows) > 1:
print(
'[ERROR]:找到store_id({3})的{0}_id({1})在{2}的多个采购价!!!'.format(
id_type, id, book_date, store_id))
return res_dict
def get_product_status(self,
warehouse_id=1,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_base_supplier_product 表中获取产品在某一天的采购价
"""
sql = """
SELECT
product_status
FROM
(SELECT
stage AS product_status,
row_number() over(partition by {1}_id ORDER BY book_date DESC) AS rank
FROM ios_base_product_classification
WHERE booK_date<='{0}' AND {1}_id={2}
) AS t
WHERE rank=1
""".format(book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) == 1:
product_status = int(rows[0].product_status)
res_dict = [{
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'product_status',
'feature_type': product_status,
'feature_value': 1
}]
else:
res_dict = None
print('[ERROR]:找不到store_id({3})的{0}_id({1})在{2}的产品状态信息!!!'.format(
id_type, id, book_date, store_id))
return res_dict
def get_promotion(self,
warehouse_id=1,
store_id=None,
id=None,
id_type='sku',
book_date=None):
"""
从 ios_base_supplier_product 表中获取产品在某一天的促销情况
"""
sql = """
SELECT
spp.type
FROM ios_sale_promotion AS sp
INNER JOIN ios_sale_promotion_policy AS spp
ON sp.policy_id=spp.policy_id
WHERE sp.store_id={0} AND sp.{2}_id={3}
AND spp.start_time<='{1}' AND spp.end_time>='{1}'
""".format(store_id, book_date, id_type, id)
# print(sql)
rows = BaseModel.raw(sql)
if len(rows) >= 1:
types = [int(row.type) for row in rows]
res_dict = []
for promotion_type in types:
tmp_dict = {
'warehouse_id': warehouse_id,
'store_id': store_id,
'{}_id'.format(id_type): id,
'book_date': book_date,
'feature_name': 'promotion',
'feature_type': promotion_type,
'feature_value': 1
}
res_dict.append(tmp_dict)
else:
res_dict = None
print('[INFO]:找不到store_id({3})的{0}_id({1})在{2}的促销信息!!!'.format(
id_type, id, book_date, store_id))
return res_dict
def get_time_data_from_work_calendar(self):
sql='''
SELECT
work_day AS date,
holiday_type AS holiday,
month AS season,
is_weekend AS weekend
FROM
ios_base_work_calendar
'''
sql_results = BaseModel.raw(sql)
time_data = []
for result in sql_results:
data_dict = dict()
data_dict['date'] = str(result.date)
data_dict['holiday'] = str(result.holiday)
data_dict['season'] = str(result.season)
data_dict['weekend'] = int(result.weekend)
time_data.append(data_dict)
# 转成json字符串
time_data_str=str(time_data)
return time_data_str
def run(self):
print('[INFO]:{:-^30}'.format('开始计算仓库(%d)的需求分类'%self.warehouse_id))
# (1) 获取产品的上市时间
sql='''
SELECT
DISTINCT sku_id AS id,
DATE(book_date) AS book_date
FROM ios_sale_up_to_new
WHERE warehouse_id={}
'''.format(self.warehouse_id)
sql_results = BaseModel.raw(sql)
time2market= dict()
# for result in sql_results:
# time2market[str(result.id)] = str(result.book_date)
print('[INFO]:{:-^30}'.format('完成获取产品上市时间'))
# (2) 获取销量数据
sql="""
SELECT
ol.sku_id AS id,
DATE(o.sale_date) AS book_date,
SUM(ol.quantity) AS quantity
FROM ios_sale_order AS o
INNER JOIN ios_sale_order_line AS ol
ON o.order_id=ol.order_id
INNER JOIN ios_base_store AS s
ON o.store_id=s.store_id
WHERE s.warehouse_id={0}
AND o.sale_date>='{1}'
AND o.sale_date<='{2}'
-- AND ol.sku_id=11516
GROUP BY ol.sku_id,book_date
ORDER BY book_date DESC
""".format(self.warehouse_id,self.start_date,self.end_date)
input_df=pd.read_sql(sql, db)
# 将book_date列转成日期类型
input_df['book_date']=input_df['book_date'].map(lambda x: datetime.datetime.strptime(str(x),'%Y-%m-%d'))
print('[INFO]:{:-^30}'.format('完成获取产品销量数据'))
# (3) 计算销量类别
from_date=datetime.datetime.strptime(self.start_date,'%Y-%m-%d')
to_date=datetime.datetime.strptime(self.end_date,'%Y-%m-%d')
insert_values=[]
df=input_df.drop_duplicates()
# 所有的产品id
ids=df['id'].unique()
if len(ids)==0:
print("[INFO]:未获取到任何产品的销量数据,请检查输入参数及SQL语句！！！")
return None
else:
print('[INFO]:{:-^30}'.format('获取到%d个产品的销量数据'%len(ids)))
print('[INFO]:{:-^30}'.format('使用最近%d天的销量数据做需求分类'%self.use_recent_days))
# 循坏每个id
for index_no,id in enumerate(ids):
# print('Processing %d-th sku %s ... There are %d skus in total ...'%(index_no+1,id,len(ids)))
df_tmp=df[df['id']==id] # 将id对应的所有数据取出来
if time2market.get(str(id)):
# 获取产品上市日期
date_market=time2market.get(str(id))
date_market=datetime.datetime.strptime(date_market,'%Y-%m-%d')
date_min=max(date_market,from_date)
else: # 未获取到产品的上市日期
date_min=from_date
date_max=to_date
# date_min=datetime.datetime.strptime('2017-01-01','%Y-%m-%d')
# date_max=datetime.datetime.strptime('2017-12-31','%Y-%m-%d')
# date_min=datetime.datetime.strptime('2018-01-28','%Y-%m-%d')
# date_max=datetime.datetime.strptime('2018-05-02','%Y-%m-%d')
df_date=df_tmp.set_index(['book_date']) # 将date列设置为索引,仍然为DataFrame
ts_quantity=df_date['quantity'] # 返回Series ******
dates=pd.date_range(date_min,date_max) # 有效的历史日期
ts_quantity=ts_quantity.reindex(dates,fill_value=0) # 重新索引
ts_quantity=ts_quantity.astype(np.float64) # 将其转化为float64 ******
# 取最近N天的数据:N=use_recent_days
if self.use_recent_days:
ts_quantity=ts_quantity[-self.use_recent_days:]
dc=CalculateDemandClassification(tmp_list=ts_quantity,sku_id=id,length_merge=self.length_merge)
dc.fit()
tmp_dict={
'warehouse_id':self.warehouse_id,
'sku_id':id,
'adi_value':dc.adi_value,
'cv2_value':dc.cv2_value,
'demand_class':dc.demand_class,
'sale_days':dc.sale_days
}
insert_values.append(tmp_dict)
print('[INFO]:{:-^30}'.format('完成计算产品销量类别'))
# (4) 销量分类结果写入数据库
# 测试时不操作数据库
# # 删除warehouse_id下面对应的销量分类数据
# DemandClassification.delete().where(DemandClassification.warehouse_id==self.warehouse_id).execute()
# # 多条记录插入
# DemandClassification.insert_many(insert_values).execute()
print('[INFO]:{:-^30}'.format('完成将产品销量类别数据写入数据库'))
print('[INFO]:{:=^50}'.format(' end '))
def get_time2market_data_for_demand_forecast(self,calculate_dimension_id=None,forecast_by='store_id',id=None):
'''
按门店或仓库进行预测
forecast_by='store_id' or forecast_by='warehouse_id'
output:
time2market--dict
'''
time2market = None
sql=''
# 按门店进行预测
if forecast_by=='store_id':
if calculate_dimension_id==1: # 按照sku的方式合并
sql='''
SELECT
DISTINCT sku_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE store_id={}
'''.format(id)
elif calculate_dimension_id==2: # 按照skc的方式合并
sql='''
SELECT
DISTINCT skc_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE store_id={}
'''.format(id)
elif calculate_dimension_id==3: # 按照product的方式合并
sql='''
SELECT
DISTINCT product_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE store_id={}
'''.format(id)
# 按仓库进行预测
elif forecast_by=='warehouse_id':
if calculate_dimension_id==1: # 按照sku的方式合并
sql='''
SELECT
DISTINCT sku_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE warehouse_id={}
'''.format(id)
elif calculate_dimension_id==2: # 按照skc的方式合并
sql='''
SELECT
DISTINCT skc_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE warehouse_id={}
'''.format(id)
elif calculate_dimension_id==3: # 按照product的方式合并
sql='''
SELECT
DISTINCT product_id AS id,
book_date
FROM ios_sale_up_to_new
WHERE warehouse_id={}
'''.format(id)
else:
pass
if sql!='':
sql_results = BaseModel.raw(sql)
time2market= dict()
for result in sql_results:
time2market[str(result.id)] = str(result.book_date)
return time2market
def get_forecast_data_for_demand_forecast(self,date_start, date_end, store_id_list, calculate_dimension_id, calculate_range_id):
"""
获取所有id对应的商品的预测数据
date_start, date_end: 时间, 限定ios_sale_order的sale_date # date_end限定为昨天
store_id_list: 指定门店, 限定ios_sale_order的store
calculate_dimension_id: 指定计算维度, 影响ios_sale_order_line的group by方式 # 1.sku合并 2.skc合并 3.product合并
calculate_range_id: 指定计算范围, 限定sku数量 # 1:全部, 2:快流件 3: 慢流件
"""
# date_end = str(datetime.datetime.now()-datetime.timedelta(days=1))[0:10] # 默认为昨天
results = None
if len(store_id_list)==1:
store_id_list_2sql='({})'.format(store_id_list[0])
else:
store_id_list_2sql='{}'.format(tuple(store_id_list))
if calculate_dimension_id==1: # 按照sku的方式合并
results = BaseModel.raw("""
SELECT
id,
fact_price,
tag_price,
discount
FROM
(SELECT
ol.sku_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level,
row_number() over(partition by ol.sku_id ORDER BY o.sale_date DESC) AS rank
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
GROUP BY ol.sku_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
) t
WHERE rank=1;
""".format(date_start,date_end,store_id_list_2sql))
elif calculate_dimension_id==2: # 按照skc的方式合并
results = BaseModel.raw("""
SELECT
id,
fact_price,
tag_price,
discount
FROM
(SELECT
ol.skc_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level,
row_number() over(partition by ol.skc_id ORDER BY o.sale_date DESC) AS rank
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
GROUP BY ol.skc_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
) t
WHERE rank=1;
""".format(date_start,date_end,store_id_list_2sql))
else: # calculate_dimension_id==3 按照product的方式合并
results = BaseModel.raw("""
SELECT
id,
fact_price,
tag_price,
discount
FROM
(SELECT
ol.product_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level,
row_number() over(partition by ol.product_id ORDER BY o.sale_date DESC) AS rank
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
AND oc.classfication_level>=1
AND oc.classfication_level<=2
GROUP BY ol.product_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
) t
WHERE rank=1;
""".format(date_start,date_end,store_id_list_2sql))
return results
def get_history_data_for_demand_forecast(self,date_start=None, date_end=None, store_id_list=None, calculate_dimension_id=None, calculate_range_id=None):
"""
获取所有指定时间的历史数据
date_start, date_end: 时间, 限定ios_sale_order的sale_date
store_id_list: 指定门店, 限定ios_sale_order的store
calculate_dimension_id: 指定计算维度, 影响ios_sale_order_line的group by方式 # 1.sku合并 2.skc合并 3.product合并
calculate_range_id: 指定计算范围, 限定sku数量 # 1:全部, 2:快流件 3: 慢流件
"""
results = None
if len(store_id_list)==1:
store_id_list_2sql='({})'.format(store_id_list[0])
else:
store_id_list_2sql='{}'.format(tuple(store_id_list))
if calculate_dimension_id==1: # 按照sku的方式合并
results = BaseModel.raw("""
SELECT
ol.sku_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
GROUP BY ol.sku_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
ORDER BY oc.classfication_level, ol.sku_id;
""".format(date_start,date_end,store_id_list_2sql))
elif calculate_dimension_id==2: # 按照skc的方式合并
results = BaseModel.raw("""
SELECT
ol.skc_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
GROUP BY ol.skc_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
ORDER BY oc.classfication_level, ol.skc_id;
""".format(date_start,date_end,store_id_list_2sql))
else: # calculate_dimension_id==3 按照product的方式合并
results = BaseModel.raw("""
SELECT
ol.product_id AS id,
AVG(ol.amount/ol.quantity) AS fact_price,
AVG(ol.price) AS tag_price,
AVG(ol.discount/ol.quantity) AS discount,
SUM(ol.quantity) AS quantity,
product.name AS product_name,
sku.bar_code AS bar_code,
o.sale_date AS sale_date,
oc.classfication_level AS classification_level
FROM ios_sale_order_line AS ol
INNER JOIN ios_optimization_classificaition AS oc
ON ol.sku_id = oc.sku_id
INNER JOIN ios_sale_order AS o
ON ol.order_id = o.order_id
INNER JOIN ios_base_sku AS sku
ON ol.product_id = sku.product_id
INNER JOIN ios_base_product AS product
ON sku.product_id = product.product_id
WHERE oc.classfication_level IS NOT NULL AND
o.sale_date>='{0}' AND
o.sale_date<='{1}' AND
o.store_id in {2}
AND oc.classfication_level>=1
AND oc.classfication_level<=2
GROUP BY ol.product_id,o.sale_date, oc.classfication_level, sku.bar_code, product.name
ORDER BY oc.classfication_level, ol.product_id;
""".format(date_start,date_end,store_id_list_2sql))
return results
def get_last_predicted_error(self):
'''
获取产品上一期的预测误差
'''
date_end_train = datetime.datetime.strptime(self.date_end, '%Y-%m-%d')
# 上一期的结束时间
date_end_last_period = date_end_train - datetime.timedelta(1)
date_end_last_period = str(date_end_last_period)[:10]
# 上一期的开始时间
date_start_last_period = date_end_train - datetime.timedelta(
self.length_merge)
date_start_last_period = str(date_start_last_period)[:10]
# 获取上期的历史销量
sql = """
SELECT
ol.{1}_id AS id,
SUM(ol.quantity) AS quantity
FROM ios_sale_order AS o
INNER JOIN ios_sale_order_line AS ol
ON o.order_id=ol.order_id
INNER JOIN ios_base_store AS s
ON s.store_id=o.store_id
WHERE s.warehouse_id={0}
AND o.sale_date>='{2}'
AND o.sale_date<='{3}'
AND o.status=1
AND ol.status=1
AND ol.quantity>0
GROUP BY ol.{1}_id
""".format(self.warehouse_id, self.id_type, date_start_last_period,
date_end_last_period)
# print(sql)
rows = BaseModel.raw(sql)
history_quantity_dict = dict()
for row in rows:
id = int(row.id)
quantity = float(row.quantity)
history_quantity_dict[id] = quantity
# 获取上期的预测销量
sql = """
SELECT
df.{1}_id AS id,
df.quantity AS quantity
FROM ios_optimization_demand_forecast AS df
INNER JOIN
(select *
FROM ios_base_calculate_record
WHERE type=4
AND position('True' in params)>0
ORDER BY gen_time DESC
limit 1
) AS cr
ON df.calculate_record_id=cr.record_id
WHERE df.warehouse_id={0}
AND df.store_id IS NULL
AND df.code IS NULL
AND df.start_date='{2}'
AND df.end_date='{3}'
""".format(self.warehouse_id, self.id_type, date_start_last_period,
date_end_last_period)
# print(sql)
rows = BaseModel.raw(sql)
predict_quantity_dict = dict()
for row in rows:
id = int(row.id)
quantity = float(row.quantity)
predict_quantity_dict[id] = quantity
# print(predict_quantity_dict)
last_predicted_error = dict()
if not predict_quantity_dict:
print('[INFO]:获取上期的预测销量为空!!!')
elif not history_quantity_dict:
print('[INFO]:获取上期的历史销量为空!!!')
else:
for id in predict_quantity_dict:
predicted_value = predict_quantity_dict.get(id, None)
real_value = history_quantity_dict.get(id, None)
if predicted_value is not None and real_value is not None:
if real_value == 0:
error = 0.0 if predicted_value == 0 else 1.0
else:
error = abs(real_value - predicted_value) / real_value
last_predicted_error[id] = error
else:
continue
# print(last_predicted_error)
return last_predicted_error