def buyer_orders_order_stats(time_based='hour'): """ 报价成交统计 :return: """ condition = [BuyerOrder.status_order == STATUS_ORDER_OK] # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) condition.extend([ BuyerOrder.create_time >= time_local_to_utc(start_time), BuyerOrder.create_time <= time_local_to_utc(end_time) ]) rows = db_bearing.session \ .query(func.hour(BuyerOrder.create_time).label('hour'), func.count(BuyerOrder.id)) \ .filter(*condition) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) condition.extend([ BuyerOrder.create_time >= time_local_to_utc(start_time), BuyerOrder.create_time <= time_local_to_utc(end_time) ]) rows = db_bearing.session \ .query(func.day(BuyerOrder.create_time).label('date'), func.count(BuyerOrder.id)) \ .filter(*condition) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) condition.extend([ BuyerOrder.create_time >= time_local_to_utc(start_time), BuyerOrder.create_time <= time_local_to_utc(end_time) ]) rows = db_bearing.session \ .query(func.month(BuyerOrder.create_time).label('month'), func.count(BuyerOrder.id)) \ .filter(*condition) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def customer_end_user_stats(time_based='hour'): """ 终端客户统计 :return: """ condition = [Customer.company_type == TYPE_COMPANY_FINAL_USER] # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) condition.extend( [ Customer.create_time >= time_local_to_utc(start_time), Customer.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.hour(Customer.create_time).label('hour'), func.count(Customer.id)) \ .filter(*condition) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) condition.extend( [ Customer.create_time >= time_local_to_utc(start_time), Customer.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.day(Customer.create_time).label('date'), func.count(Customer.id)) \ .filter(*condition) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) condition.extend( [ Customer.create_time >= time_local_to_utc(start_time), Customer.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.month(Customer.create_time).label('month'), func.count(Customer.id)) \ .filter(*condition) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]