64 lines
1.9 KiB
Python
64 lines
1.9 KiB
Python
# -*- coding: utf-8 -*-
|
|
import os
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
project_root = Path(__file__).parent.parent / "etl_billiards"
|
|
sys.path.insert(0, str(project_root))
|
|
|
|
from dotenv import load_dotenv
|
|
load_dotenv(project_root / ".env")
|
|
|
|
from database.connection import DatabaseConnection
|
|
|
|
dsn = os.getenv("PG_DSN")
|
|
db = DatabaseConnection(dsn)
|
|
|
|
print("=== Fixing remaining issues ===")
|
|
|
|
# 1. Fix principal_change_amount type mismatch
|
|
db.execute("""
|
|
UPDATE billiards_dwd.dwd_member_balance_change d
|
|
SET principal_change_amount = o.principal_data::numeric
|
|
FROM billiards_ods.member_balance_changes o
|
|
WHERE d.balance_change_id = o.id
|
|
AND d.principal_change_amount IS NULL
|
|
AND o.principal_data IS NOT NULL
|
|
""")
|
|
db.commit()
|
|
print("principal_change_amount: fixed")
|
|
|
|
# 2. Add missing DWD columns for dwd_recharge_order
|
|
missing_cols = [
|
|
("pl_coupon_sale_amount", "NUMERIC(18,2)"),
|
|
("mervou_sales_amount", "NUMERIC(18,2)"),
|
|
("electricity_money", "NUMERIC(18,2)"),
|
|
("real_electricity_money", "NUMERIC(18,2)"),
|
|
("electricity_adjust_money", "NUMERIC(18,2)"),
|
|
]
|
|
for col, dtype in missing_cols:
|
|
try:
|
|
db.execute(f'ALTER TABLE billiards_dwd.dwd_recharge_order ADD COLUMN IF NOT EXISTS "{col}" {dtype}')
|
|
db.commit()
|
|
print(f"dwd_recharge_order.{col}: column added")
|
|
except Exception as e:
|
|
db.rollback()
|
|
print(f"dwd_recharge_order.{col}: {str(e)[:50]}")
|
|
|
|
# 3. Backfill dwd_recharge_order from ODS
|
|
db.execute("""
|
|
UPDATE billiards_dwd.dwd_recharge_order d
|
|
SET pl_coupon_sale_amount = o.plcouponsaleamount,
|
|
mervou_sales_amount = o.mervousalesamount,
|
|
electricity_money = o.electricitymoney,
|
|
real_electricity_money = o.realelectricitymoney,
|
|
electricity_adjust_money = o.electricityadjustmoney
|
|
FROM billiards_ods.recharge_settlements o
|
|
WHERE d.recharge_order_id = o.id
|
|
""")
|
|
db.commit()
|
|
print("dwd_recharge_order: backfilled")
|
|
|
|
db.close()
|
|
print("Done")
|