def user_active_stats(time_based='hour'): """ 用户激活统计 :return: """ # 按小时统计 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))) rows = db.session \ .query(func.hour(User.create_time).label('hour'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .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))) rows = db.session \ .query(func.day(User.create_time).label('date'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .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))) rows = db.session \ .query(func.month(User.create_time).label('month'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def sales_orders_order_stats(time_based='hour'): """ 报价成交统计 :return: """ condition = [SalesOrder.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( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.hour(SalesOrder.create_time).label('hour'), func.count(SalesOrder.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( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.day(SalesOrder.create_time).label('date'), func.count(SalesOrder.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( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.month(SalesOrder.create_time).label('month'), func.count(SalesOrder.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 supplier_end_user_stats(time_based='hour'): """ 终端客户统计 :return: """ condition = [Supplier.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( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.hour(Supplier.create_time).label('hour'), func.count(Supplier.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( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.day(Supplier.create_time).label('date'), func.count(Supplier.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( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.month(Supplier.create_time).label('month'), func.count(Supplier.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)]