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