def demand_statistic(cls, session, statistic_session): """统计所有的要货请求,用于后续数据统计使用 """ #首先按天统计要货情况 StatisticsDemand = models_statistic.StatisticsDemand Demand = models.Demand old_statistic_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_type=1)\ .order_by(StatisticsDemand.statistic_datetime.desc())\ .first() start_datetime = None if not old_statistic_demand: # 如果一条记录都没有,说明此前还没有更新数据,则统计demand的开始时间就是所有demand的最小时间 early_demand=session.query(Demand)\ .order_by(Demand.create_date)\ .first() start_datetime = early_demand.create_date else: # 这里注意要增加1秒 免得会重复统计已经统计过的,那么为啥不在query语句中使用大于呢,这是为了满足在old_statistic_demand=None的时候 start_datetime = old_statistic_demand.statistic_datetime + datetime.timedelta( seconds=1) cls.update_by_day(session, statistic_session, start_datetime) statistic_session.flush() #其次统计按周的要货情况 old_statistic_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_type=2)\ .order_by(StatisticsDemand.statistic_datetime.desc())\ .first() start_datetime = None if not old_statistic_demand: # 如果一条记录都没有,说明此前还没有按周的更新数据,则统计的开始时间就是所有按天统计的最小时间 early_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_type=1)\ .order_by(StatisticsDemand.statistic_datetime)\ .first() # 还没有任何按天统计的要货记录 if not early_demand: return True start_datetime = early_demand.statistic_datetime else: start_datetime = old_statistic_demand.statistic_datetime week_start_datetime, week_end_datetime = TimeFunc.get_week_start_end( start_datetime) cls.update_by_week_and_month(statistic_session, week_start_datetime, 2) statistic_session.flush() #最后统计按月的要货情况 old_statistic_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_type=3)\ .order_by(StatisticsDemand.statistic_date.desc())\ .first() start_datetime = None if not old_statistic_demand: # 如果一条记录都没有,说明此前还没有按月的更新数据,则统计的开始时间就是所有按周统计的最小时间 early_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_type=2)\ .order_by(StatisticsDemand.statistic_date)\ .first() # 还没有任何按周统计的要货记录 if not early_demand: return True start_datetime = early_demand.statistic_date else: start_datetime = old_statistic_demand.statistic_date month_start_datetime, month_end_datetime = TimeFunc.get_month_start_end( start_datetime) cls.update_by_week_and_month(statistic_session, month_start_datetime, 3) statistic_session.commit() return True
def update_by_week_and_month(cls, statistic_session, start_datetime, statistic_type): """按周或者月更新统计数据 statistic_type 2: 按月 3:按周 """ StatisticsDemand = models_statistic.StatisticsDemand Demand = models.Demand start_date = datetime.date(start_datetime.year, start_datetime.month, start_datetime.day) group_type = None if statistic_type == 2: group_type = StatisticsDemand.statistic_week elif statistic_type == 3: group_type = StatisticsDemand.statistic_month day_statistic_demands=statistic_session.query(StatisticsDemand.shop_id, StatisticsDemand.goods_id,\ func.sum(StatisticsDemand.total_order_amount),\ func.sum(StatisticsDemand.total_wasted),\ func.sum(StatisticsDemand.total_sale),func.sum(StatisticsDemand.total_arrival),\ func.sum(StatisticsDemand.average_purchase_price),func.sum(StatisticsDemand.average_price),\ func.sum(StatisticsDemand.total_current_stock),func.count(StatisticsDemand.id),\ func.max(StatisticsDemand.statistic_datetime),StatisticsDemand.statistic_year,\ StatisticsDemand.statistic_month,StatisticsDemand.statistic_week)\ .order_by(StatisticsDemand.statistic_year,group_type)\ .filter(StatisticsDemand.statistic_date>=start_date)\ .filter_by(statistic_type=statistic_type-1)\ .group_by(StatisticsDemand.shop_id,\ StatisticsDemand.goods_id,\ StatisticsDemand.statistic_year,\ group_type)\ .all() if not day_statistic_demands: return True for each_demand in day_statistic_demands: average_purchase_price = 0 average_price = 0 demand_count = each_demand[9] if demand_count: average_purchase_price = each_demand[6] / demand_count average_price = each_demand[7] / demand_count new_statistic_demand = None if_new = False # 统计按周的数据 if statistic_type == 2: if each_demand[11] == int( start_date.strftime("%Y")) and each_demand[13] == int( start_date.strftime("%W")): exist_statistic_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_year=each_demand[11],\ statistic_week=each_demand[13],\ statistic_type=statistic_type,\ shop_id=each_demand.shop_id,\ goods_id=each_demand.goods_id)\ .first() if exist_statistic_demand: new_statistic_demand = exist_statistic_demand else: if_new = True else: if_new = True # 统计按月的数据 elif statistic_type == 3: if each_demand[11] == int( start_date.strftime("%Y")) and each_demand[12] == int( start_date.strftime("%m")): exist_statistic_demand=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_year=each_demand[11],\ statistic_month=each_demand[12],\ statistic_type=statistic_type,\ shop_id=each_demand.shop_id,\ goods_id=each_demand.goods_id)\ .first() if exist_statistic_demand: new_statistic_demand = exist_statistic_demand else: if_new = True else: if_new = True if if_new: new_statistic_demand=StatisticsDemand(statistic_type=statistic_type,\ shop_id=each_demand[0],\ goods_id=each_demand[1],\ statistic_year=each_demand[11],\ statistic_month=each_demand[12],\ statistic_week=each_demand[13]) statistic_session.add(new_statistic_demand) statistic_session.flush() new_statistic_demand.total_order_amount = each_demand[2] # 重要点,因为数据录入的时候,昨日损耗和昨日销量是保存在今天这条录入记录的, # 所以,在进行按周和按月统计的时候,统计的时间需要错开,也就是说损耗统计和 # 销量统计需要减去统计周期(按周/按月)的起始天的,并加上下个周期起始天的数据 # 首先重新计算统计日期 if statistic_type == 2: new_start_datetime, new_end_datetime = TimeFunc.get_week_start_end( each_demand[10]) elif statistic_type == 3: new_start_datetime, new_end_datetime = TimeFunc.get_month_start_end( each_demand[10]) # 统计起始天昨日消耗和昨日销量 day_statistic_demand_base=statistic_session.query(StatisticsDemand.total_wasted,\ StatisticsDemand.total_sale)\ .filter_by(shop_id=each_demand[0],\ goods_id=each_demand[1]) start_date_statistic_demand =day_statistic_demand_base.filter_by(statistic_date=new_start_datetime.strftime("%Y-%m-%d"))\ .first() next_start_date_statistic_demand=day_statistic_demand_base.filter_by(statistic_date=new_end_datetime.strftime("%Y-%m-%d"))\ .first() decrease_wasted = 0 decrease_sale = 0 increase_wasted = 0 increase_sale = 0 if start_date_statistic_demand: decrease_wasted = start_date_statistic_demand[0] decrease_sale = start_date_statistic_demand[1] if next_start_date_statistic_demand: increase_wasted = next_start_date_statistic_demand[0] increase_sale = next_start_date_statistic_demand[1] # 累计损耗和销量数据修正 new_statistic_demand.total_wasted = each_demand[ 3] - decrease_wasted + increase_wasted new_statistic_demand.total_sale = each_demand[ 4] - decrease_sale + increase_sale new_statistic_demand.total_arrival = each_demand[5] new_statistic_demand.average_purchase_price = average_purchase_price new_statistic_demand.average_price = average_price new_statistic_demand.total_current_stock = each_demand[8] new_statistic_demand.demand_count = demand_count new_statistic_demand.statistic_datetime = each_demand[10] new_statistic_demand.statistic_date = each_demand[10].strftime( "%Y-%m-%d") statistic_session.flush() return True
def update_by_week_and_month(cls, statistic_session, start_datetime, statistic_type): """按周或者月更新统计数据 statistic_type 2: 按月 3:按周 """ StatisticsDemand = models_statistic.StatisticsDemand Demand = models.Demand start_date = datetime.date(start_datetime.year, start_datetime.month, start_datetime.day) group_type = None if statistic_type == 2: group_type = StatisticsDemand.statistic_week elif statistic_type == 3: group_type = StatisticsDemand.statistic_month day_statistic_demands=statistic_session.query(StatisticsDemand.shop_id, StatisticsDemand.goods_id,\ func.sum(StatisticsDemand.total_order_amount),\ func.sum(StatisticsDemand.total_wasted),\ func.sum(StatisticsDemand.total_sale),func.sum(StatisticsDemand.total_arrival),\ func.sum(StatisticsDemand.average_purchase_price),func.sum(StatisticsDemand.average_price),\ func.sum(StatisticsDemand.total_current_stock),func.count(StatisticsDemand.id),\ func.max(StatisticsDemand.statistic_datetime),StatisticsDemand.statistic_year,\ StatisticsDemand.statistic_month,StatisticsDemand.statistic_week)\ .order_by(StatisticsDemand.statistic_year,group_type)\ .filter(StatisticsDemand.statistic_date>=start_date)\ .filter_by(statistic_type=1)\ .group_by(StatisticsDemand.shop_id,\ StatisticsDemand.goods_id,\ StatisticsDemand.statistic_year,\ group_type)\ .all() if not day_statistic_demands: return True all_statistic_demand_week=statistic_session.query(StatisticsDemand)\ .filter(StatisticsDemand.statistic_date>=start_date)\ .filter_by(statistic_type=2)\ .all() shop_goods_week_obj_dict = {} for item in all_statistic_demand_week: s_id = item.shop_id g_id = item.goods_id year = item.statistic_year week = item.statistic_week if s_id not in shop_goods_week_obj_dict: shop_goods_week_obj_dict[s_id] = {} if g_id not in shop_goods_week_obj_dict[s_id]: shop_goods_week_obj_dict[s_id][g_id] = {} if year not in shop_goods_week_obj_dict[s_id][g_id]: shop_goods_week_obj_dict[s_id][g_id][year] = {} if week not in shop_goods_week_obj_dict[s_id][g_id][year]: shop_goods_week_obj_dict[s_id][g_id][year][week] = {} shop_goods_week_obj_dict[s_id][g_id][year][week] = item all_statistic_demand_month=statistic_session.query(StatisticsDemand)\ .filter(StatisticsDemand.statistic_date>=start_date)\ .filter_by(statistic_type=3)\ .all() shop_goods_month_obj_dict = {} for item in all_statistic_demand_month: s_id = item.shop_id g_id = item.goods_id year = item.statistic_year month = item.statistic_month if s_id not in shop_goods_month_obj_dict: shop_goods_month_obj_dict[s_id] = {} if g_id not in shop_goods_month_obj_dict[s_id]: shop_goods_month_obj_dict[s_id][g_id] = {} if year not in shop_goods_month_obj_dict[s_id][g_id]: shop_goods_month_obj_dict[s_id][g_id][year] = {} if month not in shop_goods_month_obj_dict[s_id][g_id][year]: shop_goods_month_obj_dict[s_id][g_id][year][month] = {} shop_goods_month_obj_dict[s_id][g_id][year][month] = item all_statistic_demands=statistic_session.query(StatisticsDemand)\ .filter_by(statistic_date=start_date,\ statistic_type=1)\ .all() # 存储店铺商品统计数据对象 shop_goods_obj_dict = {} for item in all_statistic_demands: s_id = item.shop_id g_id = item.goods_id if s_id not in shop_goods_obj_dict: shop_goods_obj_dict[s_id] = {} if g_id not in shop_goods_obj_dict[s_id]: shop_goods_obj_dict[s_id][g_id] = {} shop_goods_obj_dict[s_id][g_id] = item for each_demand in day_statistic_demands: average_purchase_price = 0 average_price = 0 demand_count = each_demand[9] if demand_count: average_purchase_price = each_demand[6] / demand_count average_price = each_demand[7] / demand_count new_statistic_demand = None if_new = True s_id = each_demand[0] g_id = each_demand[1] year = each_demand[11] month = each_demand[12] week = each_demand[13] # 统计按周的数据 if statistic_type == 2: if each_demand[11] == int( start_date.strftime("%Y")) and each_demand[13] == int( start_date.strftime("%W")): if s_id in shop_goods_week_obj_dict: if g_id in shop_goods_week_obj_dict[s_id]: if year in shop_goods_week_obj_dict[s_id][g_id]: if week in shop_goods_week_obj_dict[s_id][ g_id][year]: if_new = False new_statistic_demand = shop_goods_week_obj_dict[ s_id][g_id][year][week] # 统计按月的数据 elif statistic_type == 3: if each_demand[11] == int( start_date.strftime("%Y")) and each_demand[12] == int( start_date.strftime("%m")): if s_id in shop_goods_month_obj_dict: if g_id in shop_goods_month_obj_dict[s_id]: if year in shop_goods_month_obj_dict[s_id][g_id]: if month in shop_goods_week_obj_dict[s_id][ g_id][year]: if_new = False new_statistic_demand = shop_goods_week_obj_dict[ s_id][g_id][year][month] if if_new: new_statistic_demand=StatisticsDemand(statistic_type=statistic_type,\ shop_id=s_id,\ goods_id=g_id,\ statistic_year=year,\ statistic_month=month,\ statistic_week=week) statistic_session.add(new_statistic_demand) statistic_session.flush() new_statistic_demand.total_order_amount = each_demand[2] # 重要点,因为数据录入的时候,昨日损耗和昨日销量是保存在今天这条录入记录的, # 所以,在进行按周和按月统计的时候,统计的时间需要错开,也就是说损耗统计和 # 销量统计需要减去统计周期(按周/按月)的起始天的,并加上下个周期起始天的数据 # 首先重新计算统计日期 if statistic_type == 2: new_start_datetime, new_end_datetime = TimeFunc.get_week_start_end( each_demand[10]) elif statistic_type == 3: new_start_datetime, new_end_datetime = TimeFunc.get_month_start_end( each_demand[10]) start_date_statistic_demand = None next_start_date_statistic_demand = None if s_id in shop_goods_obj_dict: if g_id in shop_goods_obj_dict[s_id]: if new_start_datetime.strftime( "%Y-%m-%d" ) == shop_goods_obj_dict[s_id][g_id].statistic_date: start_date_statistic_demand = shop_goods_obj_dict[ s_id][g_id] if new_end_datetime.strftime( "%Y-%m-%d" ) == shop_goods_obj_dict[s_id][g_id].statistic_date: next_start_date_statistic_demand = shop_goods_obj_dict[ s_id][g_id] decrease_wasted = 0 decrease_sale = 0 increase_wasted = 0 increase_sale = 0 if start_date_statistic_demand: decrease_wasted = start_date_statistic_demand.total_wasted decrease_sale = start_date_statistic_demand.total_sale if next_start_date_statistic_demand: increase_wasted = next_start_date_statistic_demand.total_wasted increase_sale = next_start_date_statistic_demand.total_sale # 累计损耗和销量数据修正 new_statistic_demand.total_wasted = each_demand[ 3] - decrease_wasted + increase_wasted new_statistic_demand.total_sale = each_demand[ 4] - decrease_sale + increase_sale new_statistic_demand.total_arrival = each_demand[5] new_statistic_demand.average_purchase_price = average_purchase_price new_statistic_demand.average_price = average_price new_statistic_demand.total_current_stock = each_demand[8] new_statistic_demand.demand_count = demand_count new_statistic_demand.statistic_datetime = each_demand[10] new_statistic_demand.statistic_date = each_demand[10].strftime( "%Y-%m-%d") statistic_session.flush() return True