160 lines
6.8 KiB
Python
160 lines
6.8 KiB
Python
from typing import Any, Optional, Tuple, List
|
|
from datetime import datetime
|
|
|
|
from sqlalchemy import select, func
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
|
|
from app.models.user import User
|
|
from app.models.material_cost import MaterialCost
|
|
from app.models.user_oauth import UserOAuth
|
|
|
|
|
|
async def get_consumption_list(
|
|
db: AsyncSession,
|
|
page: int = 1,
|
|
page_size: int = 20,
|
|
advertiser_id: Optional[str] = None,
|
|
user_id: Optional[str] = None,
|
|
consume_date: Optional[List[str]] = None,
|
|
current_user: Optional[User] = None,
|
|
) -> Tuple[List[MaterialCost], int]:
|
|
offset = (page - 1) * page_size
|
|
|
|
query = select(MaterialCost).where(MaterialCost.deleted_at.is_(None))
|
|
|
|
if current_user:
|
|
query = query.join(
|
|
UserOAuth,
|
|
MaterialCost.oauth_id == UserOAuth.id,
|
|
).where(
|
|
UserOAuth.user_id == current_user.id,
|
|
UserOAuth.deleted_at.is_(None),
|
|
)
|
|
|
|
if user_id:
|
|
query = query.join(
|
|
UserOAuth,
|
|
MaterialCost.oauth_id == UserOAuth.id,
|
|
).where(UserOAuth.user_id == user_id)
|
|
|
|
if advertiser_id:
|
|
query = query.where(MaterialCost.advertiser_id == advertiser_id)
|
|
|
|
if consume_date and len(consume_date) >= 2:
|
|
start_date_obj = datetime.strptime(consume_date[0], "%Y-%m-%d").date()
|
|
end_date_obj = datetime.strptime(consume_date[1], "%Y-%m-%d").date()
|
|
query = query.where(MaterialCost.consume_date >= start_date_obj)
|
|
query = query.where(MaterialCost.consume_date <= end_date_obj)
|
|
|
|
result = await db.execute(
|
|
query.order_by(MaterialCost.consume_date.desc())
|
|
.offset(offset)
|
|
.limit(page_size)
|
|
)
|
|
consumptions = result.scalars().all()
|
|
|
|
count_query = select(func.count(MaterialCost.id)).where(MaterialCost.deleted_at.is_(None))
|
|
|
|
if current_user:
|
|
count_query = count_query.join(
|
|
UserOAuth,
|
|
MaterialCost.oauth_id == UserOAuth.id,
|
|
).where(
|
|
UserOAuth.user_id == current_user.id,
|
|
UserOAuth.deleted_at.is_(None),
|
|
)
|
|
|
|
if user_id:
|
|
count_query = count_query.join(
|
|
UserOAuth,
|
|
MaterialCost.oauth_id == UserOAuth.id,
|
|
).where(UserOAuth.user_id == user_id)
|
|
|
|
if advertiser_id:
|
|
count_query = count_query.where(MaterialCost.advertiser_id == advertiser_id)
|
|
|
|
if consume_date and len(consume_date) >= 2:
|
|
start_date_obj = datetime.strptime(consume_date[0], "%Y-%m-%d").date()
|
|
end_date_obj = datetime.strptime(consume_date[1], "%Y-%m-%d").date()
|
|
count_query = count_query.where(MaterialCost.consume_date >= start_date_obj)
|
|
count_query = count_query.where(MaterialCost.consume_date <= end_date_obj)
|
|
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar_one()
|
|
|
|
return consumptions, total
|
|
|
|
|
|
def format_consumption_response(consumptions: List[MaterialCost], include_oauth_id: bool = False) -> List[dict]:
|
|
result = []
|
|
for consumption in consumptions:
|
|
item = {
|
|
"id": consumption.id,
|
|
"advertiser_id": consumption.advertiser_id,
|
|
"material_id": consumption.material_id,
|
|
"consume_date": consumption.consume_date.isoformat() if consumption.consume_date else None,
|
|
"stat_cost": consumption.stat_cost,
|
|
"show_cnt": consumption.show_cnt,
|
|
"cpm_platform": consumption.cpm_platform,
|
|
"click_cnt": consumption.click_cnt,
|
|
"ctr": consumption.ctr,
|
|
"cpc_platform": consumption.cpc_platform,
|
|
"convert_cnt": consumption.convert_cnt,
|
|
"conversion_cost": consumption.conversion_cost,
|
|
"conversion_rate": consumption.conversion_rate,
|
|
"deep_convert_cnt": consumption.deep_convert_cnt,
|
|
"deep_convert_cost": consumption.deep_convert_cost,
|
|
"deep_convert_rate": consumption.deep_convert_rate,
|
|
"active": consumption.active,
|
|
"active_cost": consumption.active_cost,
|
|
"active_rate": consumption.active_rate,
|
|
"active_register": consumption.active_register,
|
|
"active_register_cost": consumption.active_register_cost,
|
|
"active_register_rate": consumption.active_register_rate,
|
|
"attribution_next_day_open_cnt": consumption.attribution_next_day_open_cnt,
|
|
"attribution_next_day_open_cost": consumption.attribution_next_day_open_cost,
|
|
"attribution_next_day_open_rate": consumption.attribution_next_day_open_rate,
|
|
"active_pay": consumption.active_pay,
|
|
"active_pay_cost": consumption.active_pay_cost,
|
|
"active_pay_rate": consumption.active_pay_rate,
|
|
"phone": consumption.phone,
|
|
"form": consumption.form,
|
|
"download_start": consumption.download_start,
|
|
"form_submit": consumption.form_submit,
|
|
"button": consumption.button,
|
|
"view": consumption.view,
|
|
"message": consumption.message,
|
|
"consult": consumption.consult,
|
|
"consult_effective": consumption.consult_effective,
|
|
"shopping": consumption.shopping,
|
|
"customer_effective": consumption.customer_effective,
|
|
"attribution_game_in_app_ltv_1day": consumption.attribution_game_in_app_ltv_1day,
|
|
"attribution_game_in_app_roi_1day": consumption.attribution_game_in_app_roi_1day,
|
|
"loan_completion": consumption.loan_completion,
|
|
"loan_completion_cost": consumption.loan_completion_cost,
|
|
"loan_completion_rate": consumption.loan_completion_rate,
|
|
"loan_credit": consumption.loan_credit,
|
|
"loan_credit_cost": consumption.loan_credit_cost,
|
|
"loan_credit_rate": consumption.loan_credit_rate,
|
|
"in_app_order_gmv": consumption.in_app_order_gmv,
|
|
"in_app_order_roi": consumption.in_app_order_roi,
|
|
"in_app_pay_gmv": consumption.in_app_pay_gmv,
|
|
"in_app_pay_roi": consumption.in_app_pay_roi,
|
|
"total_play": consumption.total_play,
|
|
"valid_play": consumption.valid_play,
|
|
"valid_play_cost": consumption.valid_play_cost,
|
|
"valid_play_rate": consumption.valid_play_rate,
|
|
"valid_play_of_mille": consumption.valid_play_of_mille,
|
|
"valid_play_cost_of_mille": consumption.valid_play_cost_of_mille,
|
|
"average_play_time_per_play": consumption.average_play_time_per_play,
|
|
"play_over_rate": consumption.play_over_rate,
|
|
"dy_like": consumption.dy_like,
|
|
"dy_comment": consumption.dy_comment,
|
|
"dy_share": consumption.dy_share,
|
|
"report_cnt": consumption.report_cnt,
|
|
"created_at": consumption.created_at,
|
|
}
|
|
if include_oauth_id:
|
|
item["oauth_id"] = consumption.oauth_id
|
|
result.append(item)
|
|
return result |