def start_order(dmstore_shop_id,upc):
try:
erp_ins = mysql_util.MysqlUtil(erp)
ms_get_shop = sales_quantity.sql_params['ms_get_shop']
ms_get_shop = ms_get_shop.format(dmstore_shop_id)
print ("查dmstore shop 关系表， 获取ms 的shop_id")
print (ms_get_shop)
result = erp_ins.selectOne(ms_get_shop)
ms_shop_id = result[0]
ms_get_ghs_id = sales_quantity.sql_params['ms_get_ghs_id']
ms_get_ghs_id = ms_get_ghs_id.format(ms_shop_id)
print (ms_get_ghs_id)
ms_ins = mysql_util.MysqlUtil(ms)
result2 = ms_ins.selectOne(ms_get_ghs_id)
ghs_id = result2[0]
ms_get_start_num = sales_quantity.sql_params['ms_get_start_num']
ms_get_start_num = ms_get_start_num.format(upc,ghs_id)
print("查ms 关系表， 获取ms 的步长和起订量")
print (ms_get_start_num)
result1 = ms_ins.selectOne(ms_get_start_num) # 步长 ，起订量
return result1[0],result1[1]
except:
print ("get data from ms upc error! dmstore_shop_id="+str(dmstore_shop_id) + ", upc = "+str(upc))
return None, None
def get_predict_sales(shop_ids):
mysql_ins = mysql_util.MysqlUtil(ai)
sql = sales_quantity.sql_params["sales_ai"]
exe_time = str(time.strftime('%Y-%m-%d', time.localtime()))
exe_time = str("'"+exe_time+"'")
if len(shop_ids) == 1:
shop_ids = str("( "+str(shop_ids[0])+" )")
elif(len(shop_ids) > 1):
shop_ids = str(tuple(shop_ids))
sql = sql.format(shop_ids,exe_time)
print (sql)
results = mysql_ins.selectAll(sql)
shop_ids = []
upcs = []
predict_sales = []
for row in results:
shop_id = row[0]
upc = row[1]
predict_sale = row[2]
shop_ids.append(shop_id)
upcs.append(upc)
predict_sales.append(predict_sale)
shop_upc_sales = {}
for shop_id in list(set(shop_ids)):
upc_sales = {}
for shop_id1,upc,predict_sale in zip(shop_ids,upcs,predict_sales):
if shop_id == shop_id1:
upc_sales[upc] = predict_sale
shop_upc_sales[shop_id] = upc_sales
return shop_upc_sales
def save_oreder_hour(data):
sql = "insert into goods_ai_sales_order (shopid,erp_shop_type,upc,order_sale,status,max_stock,min_stock,stock,create_date,create_time) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
print (sql)
print (data[0])
mysql_ins = mysql_util.MysqlUtil(ai)
mysql_ins.insert_many_sql(data, sql)
print ("insert into goods_ai_sales_order sucess")
mysql_ins.cursor.close()
mysql_ins.conn.close()
def get_data_week(self, results, week_days1):
"""
获取某段时间内的销量数据
:param results:
:return:
"""
salesold_inss = []
sales_old_tmp_dict = {}
for row in list(results):
self.weekdata_row_2_salesoldtmp(sales_old_tmp_dict, row)
mysql_ins = mysql_util.MysqlUtil(ai)
sql3 = "select distinct(weather_type) from goods_ai_weather"
results1 = mysql_ins.selectAll(sql3)
sql4 = "select distinct(winddirect) from goods_ai_weather"
results2 = mysql_ins.selectAll(sql4)
sql5 = "select day,type from goods_ai_holiday"
results3 = mysql_ins.selectAll(sql5)
sql2 = "select * from goods_ai_weather where create_date >= '{0}' and create_date <= '{1}' order by create_date asc "
sql2 = sql2.format(week_days1[0], week_days1[-1])
results4 = mysql_ins.selectAll(sql2)
day_types = {}
for row in results3:
day_types[row[0]] = row[1]
weather_types = []
for row in results1:
weather_types.append(row[0])
winddirects = []
for row in results2:
winddirects.append(row[0])
for key in sales_old_tmp_dict:
try:
sales_old_ins = ai_sales_old_spark.SalesOld()
# 添加基础维度 和 地域维度
self.add_baseinfo(sales_old_ins, sales_old_tmp_dict[key])
# 添加天气维度
self.add_weather(sales_old_ins, week_days1, weather_types,
winddirects, results4)
# 添加时间维度
self.add_time(sales_old_ins, week_days1, day_types)
# 添加销量统计维度
self.add_sales_count(sales_old_ins, sales_old_tmp_dict[key],
week_days1)
salesold_inss.append(sales_old_ins)
except:
print("process_data error!" + str(key))
continue
return salesold_inss
def save_df(data_frame,label,model,mean_encode_ins,sqlsc):
data = []
shop_ids = []
upcs=[]
ai_weekdays=[]
ai_days= []
ai_day_numss = []
ai_nextdays=[]
predict_next_days = []
for feature,label in zip(data_frame.collect(),label.collect()):
shop_id = int(feature[0])
upc = int(feature[1])
ai_weekday = int (feature[2])
ai_day = str(feature[3])
ai_nextday = str(feature[4])
ai_day_nums = float(feature[5])
predict = int(math.floor(label[0]))
shop_ids.append(shop_id)
upcs.append(upc)
ai_weekdays.append(ai_weekday)
ai_days.append(ai_day)
ai_day_numss.append(int(ai_day_nums))
ai_nextdays.append(ai_nextday)
predict_next_days.append(predict)
predicts_info = {}
predict_next_days_copy = predict_next_days.copy()
for i in range(0,predict_ext_days):
ai_nextday = ai_nextdays[0]
ai_nextday = str((datetime.datetime.strptime(ai_nextday, "%Y-%m-%d") + datetime.timedelta(days=i)).strftime("%Y-%m-%d"))
predicts = get_ext_predict_day(shop_ids,upcs,ai_nextday,model,mean_encode_ins,predict_next_days_copy,sqlsc)
predict_next_days_copy = predicts
predicts_info[i] = predict_next_days_copy
predicts_ext = []
for i in range(len(upcs)):
predicts_e = []
for key in predicts_info:
predicts_e.append(predicts_info[key][i])
predicts_ext.append(predicts_e)
exe_time1 = str(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
for shop_id,upc,ai_weekday,ai_day,ai_day_nums,ai_nextday,predict,predicts1 in zip(shop_ids,upcs,ai_weekdays,ai_days,ai_day_numss,ai_nextdays,predict_next_days,predicts_ext):
data.append((shop_id,upc,ai_weekday,ai_day,ai_day_nums,ai_nextday,predict,str(predicts1),str(exe_time1)))
mysql_ins = mysql_util.MysqlUtil(ai)
del_sql = "delete from goods_ai_sales_goods where next_day = {0}"
del_sql = del_sql.format("'"+data[0][5]+"'")
sql = "insert into goods_ai_sales_goods (shopid,upc,day_week,day,day_sales,next_day,nextday_predict_sales,nextdays_predict_sales,create_time) value(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
print(sql)
print (data[0])
mysql_ins.delete_sql(del_sql)
print ("delete predict sales sucess")
print (del_sql)
mysql_ins.insert_many_sql(data,sql)
print ("insert predict sales sucess")
mysql_ins.cursor.close()
mysql_ins.conn.close()
def get_weeks_results(self, sql1, week_days):
mysql_ins = mysql_util.MysqlUtil(erp)
resultss = []
start_time = week_days[0]
end_time = week_days[-1]
sql1 = sql1.format(start_time, end_time, start_time, end_time)
results = mysql_ins.selectAll(sql1)
resultss.extend(list(results))
create_date = week_days[0]
for i in range(1, 12):
week_days1 = self.get_date(i, create_date)
start_time = week_days1[0]
end_time = week_days1[-1]
sql1 = sql1.format(start_time, end_time, start_time, end_time)
results = mysql_ins.selectAll(sql1)
resultss.extend(list(results))
return resultss
def predict(self):
feature, online_df = salves_ins.get_online_features()
result = self.model.transform(feature)
result = result.select('prediction')
sql_dfl = []
for row1, row2 in zip(online_df.collect(), result.collect()):
df1 = {}
df1['shop_id'] = list(row1)[0]
df1['class_three_id'] = 1
df1['predict_sales'] = math.floor(list(row2)[0])
df1['create_date'] = list(row1)[-1]
df1['upc'] = list(row1)[-3]
sql_dfl.append((int(df1['shop_id']), int(df1['class_three_id']),
int(df1['predict_sales']), str(df1['create_date']),
str(df1['upc'])))
print(sql_dfl)
mysql_ins = mysql_util.MysqlUtil()
cur = mysql_ins.cursor
mysql_ins.insert_many(cur, sql_dfl)
def save_oreder(shop_upc_ordersales):
shop_type = config.shellgoods_params['shop_types'][1] # 门店
# order_status = config.shellgoods_params['order_status']
exe_time = str(time.strftime('%Y-%m-%d', time.localtime()))
exe_time1 = str(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
data = []
for shop_id in shop_upc_ordersales:
upc_ordersales = shop_upc_ordersales[shop_id]
for upc in upc_ordersales:
(order_sale, predict_sale, min_stock, max_stock, stock,multiple, start_sum, start_min, start_max) = upc_ordersales[upc]
data.append((shop_id,upc,order_sale, predict_sale, min_stock, max_stock, stock,exe_time,exe_time1,multiple, start_sum, start_min, start_max,shop_type))
mysql_ins = mysql_util.MysqlUtil(ai)
del_sql = "delete from goods_ai_sales_order where create_date = {0} and erp_shop_type = {1}"
del_sql = del_sql.format("'"+exe_time+"'",shop_type)
sql = "insert into goods_ai_sales_order (shopid,upc,order_sale, predict_sale, min_stock, max_stock, stock,create_date,create_time,multiple, start_sum, start_min, start_max,erp_shop_type) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
print(sql)
print(data[0])
mysql_ins.delete_sql(del_sql)
print ("delete goods_ai_sales_order sucess")
print (del_sql)
mysql_ins.insert_many_sql(data, sql)
print ("insert into goods_ai_sales_order sucess")
mysql_ins.cursor.close()
mysql_ins.conn.close()
def save_db(salesold_inss):
mysql_ins = mysql_util.MysqlUtil(ai)
sql = "insert into goods_ai_sales_dim (shop_id," \
"upc," \
"goods_id," \
"first_cate_id," \
"second_cate_id," \
"third_cate_id,"\
"goods_name,"\
"price,"\
"city,"\
"sale_1,"\
"sale_2,"\
"sale_3,"\
"sale_4,"\
"sale_5,"\
"sale_6,"\
"sale_7,"\
"sale_1_2_avg,"\
"sale_2_2_avg,"\
"sale_3_2_avg,"\
"sale_4_2_avg,"\
"sale_5_2_avg,"\
"sale_6_2_avg,"\
"sale_7_2_avg,"\
"sale_1_4_avg,"\
"sale_2_4_avg,"\
"sale_3_4_avg,"\
"sale_4_4_avg,"\
"sale_5_4_avg,"\
"sale_6_4_avg,"\
"sale_7_4_avg,"\
"sale_1_8_avg,"\
"sale_2_8_avg,"\
"sale_3_8_avg,"\
"sale_4_8_avg,"\
"sale_5_8_avg,"\
"sale_6_8_avg,"\
"sale_7_8_avg,"\
"sale_1_12_avg,"\
"sale_2_12_avg,"\
"sale_3_12_avg,"\
"sale_4_12_avg,"\
"sale_5_12_avg,"\
"sale_6_12_avg,"\
"sale_7_12_avg,"\
"sale_1week_avg_in,"\
"sale_1week_avg_out,"\
"sale_2week_avg_in,"\
"sale_2week_avg_out,"\
"sale_4week_avg_in,"\
"sale_4week_avg_out,"\
"sale_8week_avg_in,"\
"sale_8week_avg_out,"\
"sale_12week_avg_in,"\
"sale_12week_avg_out,"\
"templow_1,"\
"temphigh_1,"\
"weather_type_1,"\
"windpower_1,"\
"winddirect_1,"\
"windspeed_1,"\
"templow_2,"\
"temphigh_2,"\
"weather_type_2,"\
"windpower_2,"\
"winddirect_2,"\
"windspeed_2,"\
"templow_3,"\
"temphigh_3,"\
"weather_type_3,"\
"windpower_3,"\
"winddirect_3,"\
"windspeed_3,"\
"templow_4,"\
"temphigh_4,"\
"weather_type_4,"\
"windpower_4,"\
"winddirect_4,"\
"windspeed_4,"\
"templow_5,"\
"temphigh_5,"\
"weather_type_5,"\
"windpower_5,"\
"winddirect_5,"\
"windspeed_5,"\
"templow_6,"\
"temphigh_6,"\
"weather_type_6,"\
"windpower_6,"\
"winddirect_6,"\
"windspeed_6,"\
"templow_7,"\
"temphigh_7,"\
"weather_type_7,"\
"windpower_7,"\
"winddirect_7,"\
"windspeed_7,"\
"week_i_1,"\
"season_1,"\
"week_type_1,"\
"month_1,"\
"holiday_type_1,"\
"week_i_2,"\
"season_2,"\
"week_type_2,"\
"month_2,"\
"holiday_type_2,"\
"week_i_3,"\
"season_3,"\
"week_type_3,"\
"month_3,"\
"holiday_type_3,"\
"week_i_4,"\
"season_4,"\
"week_type_4,"\
"month_4,"\
"holiday_type_4,"\
"week_i_5,"\
"season_5,"\
"week_type_5,"\
"month_5,"\
"holiday_type_5,"\
"week_i_6,"\
"season_6,"\
"week_type_6,"\
"month_6,"\
"holiday_type_6,"\
"week_i_7,"\
"season_7,"\
"week_type_7,"\
"month_7,"\
"holiday_type_7,"\
"city_id,"\
"week_i_1_date) value (%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s,%s,%s," \
"%s,%s,%s)"
data = []
for salesold_ins in salesold_inss:
try:
salesold_ins = data_check(salesold_ins)
data.append((
float(salesold_ins.shop_id),
float(salesold_ins.upc),
float(salesold_ins.goods_id),
float(salesold_ins.first_cate_id),
float(salesold_ins.second_cate_id),
float(salesold_ins.third_cate_id),
str(salesold_ins.goods_name),
float(salesold_ins.price),
str(salesold_ins.city),
# 销量数据维度
float(salesold_ins.sale_1), # 周一
float(salesold_ins.sale_2), # 周二
float(salesold_ins.sale_3),
float(salesold_ins.sale_4),
float(salesold_ins.sale_5),
float(salesold_ins.sale_6),
float(salesold_ins.sale_7),
float(salesold_ins.sale_1_2_avg), # 两个周1 平均销量
float(salesold_ins.sale_2_2_avg), #
float(salesold_ins.sale_3_2_avg), #
float(salesold_ins.sale_4_2_avg), #
float(salesold_ins.sale_5_2_avg), #
float(salesold_ins.sale_6_2_avg), #
float(salesold_ins.sale_7_2_avg), #
float(salesold_ins.sale_1_4_avg), # 4个周1 平均销量
float(salesold_ins.sale_2_4_avg), #
float(salesold_ins.sale_3_4_avg), #
float(salesold_ins.sale_4_4_avg), #
float(salesold_ins.sale_5_4_avg), #
float(salesold_ins.sale_6_4_avg), #
float(salesold_ins.sale_7_4_avg), #
float(salesold_ins.sale_1_8_avg), # 8个周1 平均销量
float(salesold_ins.sale_2_8_avg), #
float(salesold_ins.sale_3_8_avg), #
float(salesold_ins.sale_4_8_avg), #
float(salesold_ins.sale_5_8_avg), #
float(salesold_ins.sale_6_8_avg), #
float(salesold_ins.sale_7_8_avg), #
float(salesold_ins.sale_1_12_avg), # 12个周1 平均销量
float(salesold_ins.sale_2_12_avg), #
float(salesold_ins.sale_3_12_avg), #
float(salesold_ins.sale_4_12_avg), #
float(salesold_ins.sale_5_12_avg), #
float(salesold_ins.sale_6_12_avg), #
float(salesold_ins.sale_7_12_avg), #
float(salesold_ins.sale_1week_avg_in), # 1周 周中平均销量
float(salesold_ins.sale_1week_avg_out), # 1周 周末平均销量
float(salesold_ins.sale_2week_avg_in), # 2周 周中平均销量
float(salesold_ins.sale_2week_avg_out), # 2周 周末平均销量
float(salesold_ins.sale_4week_avg_in), # 4周 周中平均销量
float(salesold_ins.sale_4week_avg_out), # 4周 周末平均销量
float(salesold_ins.sale_8week_avg_in), # 8周 周中平均销量
float(salesold_ins.sale_8week_avg_out), # 8周 周末平均销量
float(salesold_ins.sale_12week_avg_in), # 12周 周中平均销量
float(salesold_ins.sale_12week_avg_out), # 12周 周末平均销量
# 天气维度
float(salesold_ins.templow_1),
float(salesold_ins.temphigh_1),
float(salesold_ins.weather_type_1),
float(salesold_ins.windpower_1),
float(salesold_ins.winddirect_1),
float(salesold_ins.windspeed_1),
float(salesold_ins.templow_2),
float(salesold_ins.temphigh_2),
float(salesold_ins.weather_type_2),
float(salesold_ins.windpower_2),
float(salesold_ins.winddirect_2),
float(salesold_ins.windspeed_2),
float(salesold_ins.templow_3),
float(salesold_ins.temphigh_3),
float(salesold_ins.weather_type_3),
float(salesold_ins.windpower_3),
float(salesold_ins.winddirect_3),
float(salesold_ins.windspeed_3),
float(salesold_ins.templow_4),
float(salesold_ins.temphigh_4),
float(salesold_ins.weather_type_4),
float(salesold_ins.windpower_4),
float(salesold_ins.winddirect_4),
float(salesold_ins.windspeed_4),
float(salesold_ins.templow_5),
float(salesold_ins.temphigh_5),
float(salesold_ins.weather_type_5),
float(salesold_ins.windpower_5),
float(salesold_ins.winddirect_5),
float(salesold_ins.windspeed_5),
float(salesold_ins.templow_6),
float(salesold_ins.temphigh_6),
float(salesold_ins.weather_type_6),
float(salesold_ins.windpower_6),
float(salesold_ins.winddirect_6),
float(salesold_ins.windspeed_6),
float(salesold_ins.templow_7),
float(salesold_ins.temphigh_7),
float(salesold_ins.weather_type_7),
float(salesold_ins.windpower_7),
float(salesold_ins.winddirect_7),
float(salesold_ins.windspeed_7),
# 时间维度
float(salesold_ins.week_i_1),
float(salesold_ins.season_1),
float(salesold_ins.week_type_1),
float(salesold_ins.month_1),
float(salesold_ins.holiday_type_1),
float(salesold_ins.week_i_2),
float(salesold_ins.season_2),
float(salesold_ins.week_type_2),
float(salesold_ins.month_2),
float(salesold_ins.holiday_type_2),
float(salesold_ins.week_i_3),
float(salesold_ins.season_3),
float(salesold_ins.week_type_3),
float(salesold_ins.month_3),
float(salesold_ins.holiday_type_3),
float(salesold_ins.week_i_4),
float(salesold_ins.season_4),
float(salesold_ins.week_type_4),
float(salesold_ins.month_4),
float(salesold_ins.holiday_type_4),
float(salesold_ins.week_i_5),
float(salesold_ins.season_5),
float(salesold_ins.week_type_5),
float(salesold_ins.month_5),
float(salesold_ins.holiday_type_5),
float(salesold_ins.week_i_6),
float(salesold_ins.season_6),
float(salesold_ins.week_type_6),
float(salesold_ins.month_6),
float(salesold_ins.holiday_type_6),
float(salesold_ins.week_i_7),
float(salesold_ins.season_7),
float(salesold_ins.week_type_7),
float(salesold_ins.month_7),
float(salesold_ins.holiday_type_7),
# 地域维度
float(salesold_ins.city_id),
str(salesold_ins.week_i_1_date)))
except:
print("format error")
continue
try:
mysql_ins.insert_many_sql(data, sql)
except:
print("insert db error")