223 lines
8.2 KiB
Python
223 lines
8.2 KiB
Python
"""
|
||
会员消费报表：2025-11-01 至今，按手机号归总，消费金额从高到低
|
||
口径：items_sum = table_charge_money + goods_money + assistant_pd_money + assistant_cx_money + electricity_money
|
||
台费拆分：通过 dws.cfg_area_category 配置，category_code='KTV' 为 K包，其余为一般包厢台费
|
||
会员信息：通过 member_id LEFT JOIN dim_member 获取 mobile/nickname
|
||
（12月起 settlement_head.member_phone 为空，必须走 dim_member）
|
||
输出：CSV → export/SYSTEM/REPORTS/member_reports/
|
||
"""
|
||
import os
|
||
import csv
|
||
from dotenv import load_dotenv
|
||
|
||
load_dotenv()
|
||
|
||
PG_DSN = os.environ.get("TEST_DB_DSN") or os.environ.get("PG_DSN")
|
||
if not PG_DSN:
|
||
raise RuntimeError("TEST_DB_DSN / PG_DSN 未配置")
|
||
|
||
SYSTEM_ANALYZE_ROOT = os.environ.get("SYSTEM_ANALYZE_ROOT")
|
||
if not SYSTEM_ANALYZE_ROOT:
|
||
raise RuntimeError("SYSTEM_ANALYZE_ROOT 未配置")
|
||
|
||
import psycopg2
|
||
|
||
SQL = r"""
|
||
WITH date_range AS (
|
||
SELECT '2025-11-01'::date AS start_date, CURRENT_DATE AS end_date
|
||
),
|
||
|
||
area_mapping AS (
|
||
SELECT source_area_name, source_table_name, category_code
|
||
FROM dws.cfg_area_category
|
||
WHERE is_active = true AND match_type = 'EXACT'
|
||
),
|
||
|
||
-- 先筛出有会员的结算单（member_id 有值，或 member_phone 有值）
|
||
settle_ids AS (
|
||
SELECT sh.order_settle_id
|
||
FROM dwd.dwd_settlement_head sh
|
||
CROSS JOIN date_range dr
|
||
WHERE sh.settle_type IN (1, 3)
|
||
AND sh.pay_time >= dr.start_date
|
||
AND sh.pay_time < dr.end_date + 1
|
||
AND (
|
||
(sh.member_id IS NOT NULL AND sh.member_id != 0)
|
||
OR (sh.member_phone IS NOT NULL AND sh.member_phone != '')
|
||
)
|
||
),
|
||
|
||
table_fee_split AS (
|
||
SELECT
|
||
tfl.order_settle_id,
|
||
SUM(CASE WHEN COALESCE(am.category_code, 'OTHER') = 'KTV'
|
||
THEN tfl.ledger_amount ELSE 0 END) AS k_room_fee,
|
||
SUM(CASE WHEN COALESCE(am.category_code, 'OTHER') != 'KTV'
|
||
THEN tfl.ledger_amount ELSE 0 END) AS normal_table_fee
|
||
FROM dwd.dwd_table_fee_log tfl
|
||
INNER JOIN settle_ids si ON tfl.order_settle_id = si.order_settle_id
|
||
LEFT JOIN dwd.dim_table dt
|
||
ON dt.table_id = tfl.site_table_id AND dt.scd2_is_current = 1
|
||
LEFT JOIN area_mapping am
|
||
ON tfl.site_table_area_name = am.source_area_name
|
||
AND dt.table_name = am.source_table_name
|
||
WHERE tfl.is_delete = 0
|
||
GROUP BY tfl.order_settle_id
|
||
),
|
||
|
||
-- 关联 dim_member 获取手机号，优先 dim_member.mobile，回退 settlement_head.member_phone
|
||
consumption AS (
|
||
SELECT
|
||
sh.order_settle_id,
|
||
COALESCE(NULLIF(dm.mobile, ''), NULLIF(sh.member_phone, '')) AS phone,
|
||
sh.pay_time,
|
||
COALESCE(sh.table_charge_money, 0)
|
||
+ COALESCE(sh.goods_money, 0)
|
||
+ COALESCE(sh.assistant_pd_money, 0)
|
||
+ COALESCE(sh.assistant_cx_money, 0)
|
||
+ COALESCE(sh.electricity_money, 0) AS items_sum,
|
||
COALESCE(sh.goods_money, 0) AS goods_money,
|
||
COALESCE(sh.assistant_pd_money, 0) + COALESCE(sh.assistant_cx_money, 0) AS assistant_money
|
||
FROM dwd.dwd_settlement_head sh
|
||
CROSS JOIN date_range dr
|
||
LEFT JOIN dwd.dim_member dm
|
||
ON dm.member_id = sh.member_id AND dm.scd2_is_current = 1
|
||
WHERE sh.settle_type IN (1, 3)
|
||
AND sh.pay_time >= dr.start_date
|
||
AND sh.pay_time < dr.end_date + 1
|
||
AND (
|
||
(sh.member_id IS NOT NULL AND sh.member_id != 0)
|
||
OR (sh.member_phone IS NOT NULL AND sh.member_phone != '')
|
||
)
|
||
),
|
||
|
||
-- 过滤掉最终仍无手机号的记录
|
||
consumption_with_phone AS (
|
||
SELECT * FROM consumption WHERE phone IS NOT NULL
|
||
),
|
||
|
||
monthly AS (
|
||
SELECT
|
||
c.phone,
|
||
EXTRACT(YEAR FROM c.pay_time)::int AS yr,
|
||
EXTRACT(MONTH FROM c.pay_time)::int AS mo,
|
||
SUM(c.items_sum) AS month_total,
|
||
SUM(c.goods_money) AS month_goods,
|
||
SUM(c.assistant_money) AS month_assistant,
|
||
SUM(COALESCE(tf.k_room_fee, 0)) AS month_k_room,
|
||
SUM(COALESCE(tf.normal_table_fee, 0)) AS month_normal_table
|
||
FROM consumption_with_phone c
|
||
LEFT JOIN table_fee_split tf ON c.order_settle_id = tf.order_settle_id
|
||
GROUP BY c.phone, yr, mo
|
||
),
|
||
|
||
member_agg AS (
|
||
SELECT
|
||
phone,
|
||
SUM(month_total) AS total_consumption,
|
||
SUM(CASE WHEN yr = 2025 AND mo = 11 THEN month_total ELSE 0 END) AS m11,
|
||
SUM(CASE WHEN yr = 2025 AND mo = 12 THEN month_total ELSE 0 END) AS m12,
|
||
SUM(CASE WHEN yr = 2026 AND mo = 1 THEN month_total ELSE 0 END) AS m01,
|
||
SUM(CASE WHEN yr = 2026 AND mo = 2 THEN month_total ELSE 0 END) AS m02,
|
||
SUM(CASE WHEN yr = 2026 AND mo = 3 THEN month_total ELSE 0 END) AS m03,
|
||
SUM(month_k_room) AS k_room_total,
|
||
SUM(month_normal_table) AS normal_table_total,
|
||
SUM(month_assistant) AS assistant_total,
|
||
SUM(month_goods) AS goods_total
|
||
FROM monthly
|
||
GROUP BY phone
|
||
),
|
||
|
||
-- 昵称：合并 dim_member.nickname 和 settlement_head.member_name
|
||
member_names AS (
|
||
SELECT
|
||
COALESCE(NULLIF(dm.mobile, ''), NULLIF(sh.member_phone, '')) AS phone,
|
||
STRING_AGG(
|
||
DISTINCT COALESCE(NULLIF(dm.nickname, ''), NULLIF(sh.member_name, '')),
|
||
' | '
|
||
ORDER BY COALESCE(NULLIF(dm.nickname, ''), NULLIF(sh.member_name, ''))
|
||
) FILTER (
|
||
WHERE COALESCE(NULLIF(dm.nickname, ''), NULLIF(sh.member_name, '')) IS NOT NULL
|
||
) AS names
|
||
FROM dwd.dwd_settlement_head sh
|
||
CROSS JOIN date_range dr
|
||
LEFT JOIN dwd.dim_member dm
|
||
ON dm.member_id = sh.member_id AND dm.scd2_is_current = 1
|
||
WHERE sh.settle_type IN (1, 3)
|
||
AND sh.pay_time >= dr.start_date
|
||
AND sh.pay_time < dr.end_date + 1
|
||
AND (
|
||
(sh.member_id IS NOT NULL AND sh.member_id != 0)
|
||
OR (sh.member_phone IS NOT NULL AND sh.member_phone != '')
|
||
)
|
||
AND COALESCE(NULLIF(dm.mobile, ''), NULLIF(sh.member_phone, '')) IS NOT NULL
|
||
GROUP BY phone
|
||
)
|
||
|
||
SELECT
|
||
COALESCE(mn.names, '') AS "会员昵称",
|
||
ma.phone AS "手机号",
|
||
ROUND(ma.total_consumption, 2) AS "11月至今共消费",
|
||
ROUND(ma.m11, 2) AS "11月消费共计",
|
||
ROUND(ma.m12, 2) AS "12月消费共计",
|
||
ROUND(ma.m01, 2) AS "1月消费共计",
|
||
ROUND(ma.m02, 2) AS "2月消费共计",
|
||
ROUND(ma.m03, 2) AS "3月消费共计",
|
||
ROUND(ma.k_room_total, 2) AS "K包",
|
||
CASE WHEN ma.total_consumption > 0
|
||
THEN ROUND(ma.k_room_total / ma.total_consumption * 100, 1)
|
||
ELSE 0 END AS "K包占比%",
|
||
ROUND(ma.normal_table_total, 2) AS "一般包厢台费",
|
||
CASE WHEN ma.total_consumption > 0
|
||
THEN ROUND(ma.normal_table_total / ma.total_consumption * 100, 1)
|
||
ELSE 0 END AS "一般包厢台费占比%",
|
||
ROUND(ma.assistant_total, 2) AS "助教费",
|
||
CASE WHEN ma.total_consumption > 0
|
||
THEN ROUND(ma.assistant_total / ma.total_consumption * 100, 1)
|
||
ELSE 0 END AS "助教费占比%",
|
||
ROUND(ma.goods_total, 2) AS "商品费",
|
||
CASE WHEN ma.total_consumption > 0
|
||
THEN ROUND(ma.goods_total / ma.total_consumption * 100, 1)
|
||
ELSE 0 END AS "商品费占比%"
|
||
FROM member_agg ma
|
||
LEFT JOIN member_names mn ON ma.phone = mn.phone
|
||
ORDER BY ma.total_consumption DESC
|
||
"""
|
||
|
||
|
||
def run_report():
|
||
with psycopg2.connect(PG_DSN, connect_timeout=15, options="-c statement_timeout=300000") as conn:
|
||
with conn.cursor() as cur:
|
||
cur.execute(SQL)
|
||
columns = [desc[0] for desc in cur.description]
|
||
rows = cur.fetchall()
|
||
|
||
report_dir = os.path.join(os.path.dirname(SYSTEM_ANALYZE_ROOT), "member_reports")
|
||
os.makedirs(report_dir, exist_ok=True)
|
||
|
||
csv_path = os.path.join(report_dir, "member_consumption_202511_to_now.csv")
|
||
with open(csv_path, "w", newline="", encoding="utf-8-sig") as f:
|
||
writer = csv.writer(f)
|
||
writer.writerow(columns)
|
||
writer.writerows(rows)
|
||
|
||
print(f"✅ 报表已生成: {csv_path}")
|
||
print(f" 共 {len(rows)} 位会员")
|
||
|
||
print(f"\n{'='*200}")
|
||
header = " | ".join(f"{c:>14}" if i > 1 else f"{c:<20}" for i, c in enumerate(columns))
|
||
print(header)
|
||
print(f"{'='*200}")
|
||
for row in rows[:20]:
|
||
line = " | ".join(
|
||
f"{str(v):<20}" if i <= 1 else f"{str(v):>14}"
|
||
for i, v in enumerate(row)
|
||
)
|
||
print(line)
|
||
if len(rows) > 20:
|
||
print(f"... 还有 {len(rows) - 20} 行（见 CSV 文件）")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
run_report()
|