# LifecycleReportsController Query Reference (PostgreSQL + Elasticsearch) This document captures the query shapes used by `app/Http/Controllers/Reports/LifecycleReportsController.php` with dummy values. ## Dummy Input Used In Examples - `app_id`: `101` - `start_date`: `2026-05-01` - `end_date`: `2026-05-31` - `start_ts`: `2026-05-01 00:00:00` - `end_ts`: `2026-05-31 23:59:59` - `package_ids`: `['1001', '1002']` - `limit`: `200` ## Important Note On Dynamic Columns `LifecycleReportsController` uses `resolveColumn()` and may map to different physical columns depending on schema. Examples below assume common mappings: - `tbl_customer_subscriptions.order_id` - `tbl_customer_subscriptions.sms_cust_id` - `tbl_customer_subscriptions.sms_package_id` - `tbl_customer_subscriptions.package_name` - `tbl_customer_subscriptions.created_at` - `tbl_customer_subscriptions.trans_id` - `tbl_customer_subscriptions.pg_name` - `tbl_order.id` - `tbl_order.transaction_id` - `tbl_order.gateway_name` - `customers.sms_cust_id` - `customers.email_id` - `customers.phone_number` If your schema differs, SQL columns are substituted at runtime. --- ## 1) Filters Query (`getReportFilters`) ### PostgreSQL Query ```sql SELECT sms_package_id AS id, title AS title, client_package_id AS client_package_id FROM tbl_packages WHERE app_id = 101 ORDER BY title; ``` Label/value mapping in PHP: - value: always `sms_package_id` - label: `title (client_package_id)` - fallback label id: `title (sms_package_id)` if client id is missing ### Elasticsearch - Not used for filter dropdown in this controller. --- ## 2) Common ES Query Template: Order Events (`collectOrderEvents`) Used by: - `complete_payments_report` - `expired_subscriptions_report` - `active_subscriptions_report` ```json { "from": 0, "size": 200, "_source": { "includes": [ "sms_cust_id", "email_id", "phone_number", "first_name", "last_name" ] }, "query": { "bool": { "must": [ { "term": { "app_id": 101 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [""], "must_not": [""] } }, "inner_hits": { "name": "matched_orders", "size": 100 } } } ] } }, "sort": [ { "_doc": { "order": "asc" } } ] } ``` --- ## 3) Common ES Query Template: Customer Documents (`collectCustomerDocuments`) Used by: - `login_activity_report` - `logout_activity_report` - `new_signups_report` ```json { "from": 0, "size": 200, "_source": { "includes": [ "sms_cust_id", "email_id", "phone_number", "first_name", "last_name", "created_at", "last_login_time" ] }, "query": { "bool": { "must": [ { "term": { "app_id": 101 } }, "" ] } }, "sort": [ { "_doc": { "order": "asc" } } ] } ``` --- ## 4) `complete_payments_report` ### PostgreSQL Query Shape ```sql SELECT s.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, s.order_id AS order_id, s.sms_package_id AS package_id, s.package_name AS package_title, s.price AS amount, s.currency AS currency, COALESCE(s.pg_name, o.gateway_name) AS gateway, COALESCE(s.trans_id, o.transaction_id) AS transaction_id, s.start_date, s.end_date, s.created_at AS payment_date FROM tbl_customer_subscriptions s JOIN customers c ON c.sms_cust_id = s.sms_cust_id LEFT JOIN tbl_order o ON o.id = s.order_id WHERE s.status IN (2, 7) AND s.created_at BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' AND s.sms_package_id IN ('1001', '1002') AND s.app_id = 101 ORDER BY s.created_at DESC LIMIT 200; ``` ### Elasticsearch Must Clauses - subscription status in `[2, 7]` - `orders.subscription.subscription_created_at` range - optional package filter (`orders.sms_package_id` OR nested `orders.subscription.sms_package_id`) ```json [ { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [2, 7] } } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-05-01T00:00:00", "lte": "2026-05-31T23:59:59" } } } } }, { "bool": { "should": [ { "terms": { "orders.sms_package_id": ["1001", "1002"] } }, { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.sms_package_id": ["1001", "1002"] } } } } ], "minimum_should_match": 1 } } ] ``` --- ## 5) `expired_subscriptions_report` ### PostgreSQL Query Shape ```sql SELECT s.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, s.sms_package_id AS package_id, s.package_name AS package_title, s.start_date, s.end_date, s.status AS subscription_status, CASE WHEN s.status = 7 THEN 'Expired' WHEN s.status = 2 THEN 'Active' ELSE 'Other' END AS subscription_status_label, s.order_id AS order_id FROM tbl_customer_subscriptions s JOIN customers c ON c.sms_cust_id = s.sms_cust_id WHERE s.status = 7 AND s.end_date BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' AND s.sms_package_id IN ('1001', '1002') AND s.app_id = 101 ORDER BY s.end_date DESC LIMIT 200; ``` ### Elasticsearch Must Clauses - nested subscription status `7` - nested range on `orders.subscription.end_date` - optional package filter --- ## 6) `active_subscriptions_report` ### PostgreSQL Query Shape ```sql SELECT s.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, s.sms_package_id AS package_id, s.package_name AS package_title, s.start_date, s.end_date, s.status AS subscription_status, CASE WHEN s.status = 7 THEN 'Expired' WHEN s.status = 2 THEN 'Active' ELSE 'Other' END AS subscription_status_label, s.order_id AS order_id FROM tbl_customer_subscriptions s JOIN customers c ON c.sms_cust_id = s.sms_cust_id WHERE s.status = 2 AND s.start_date <= '2026-05-31 23:59:59' AND (s.end_date IS NULL OR s.end_date >= '2026-05-01 00:00:00') AND s.sms_package_id IN ('1001', '1002') AND s.app_id = 101 ORDER BY s.start_date DESC LIMIT 200; ``` ### Elasticsearch Clauses - must: - nested subscription status `2` - nested range: `orders.subscription.start_date <= end` - optional package filter - must_not: - nested range: `orders.subscription.end_date < start` --- ## 7) `login_activity_report` ### PostgreSQL Query Shape ```sql SELECT c.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, c.last_login AS last_login_at, c.created_at AS signup_at FROM customers c WHERE c.last_login IS NOT NULL AND c.last_login BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' AND c.app_id = 101 ORDER BY c.last_login DESC LIMIT 200; ``` ### Elasticsearch Must Clause ```json { "range": { "last_login_time": { "gte": "2026-05-01T00:00:00", "lte": "2026-05-31T23:59:59" } } } ``` --- ## 8) `logout_activity_report` ### PostgreSQL Query Shape (when `customer_devices` exists) ```sql SELECT c.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, c.last_login AS last_login_at, MAX(d.updated_at) AS logout_at FROM customer_devices d JOIN customers c ON c.sms_cust_id = d.sms_cust_id WHERE c.last_login IS NOT NULL AND d.status = 0 AND d.app_id = 101 GROUP BY c.sms_cust_id, c.first_name, c.last_name, c.email_id, c.phone_number, c.last_login HAVING MAX(d.updated_at) BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' ORDER BY MAX(d.updated_at) DESC LIMIT 200; ``` ### PostgreSQL Fallback (when `customer_devices` table/columns missing) Returns no rows via `WHERE 1 = 0`. ### Elasticsearch Must Clauses - exists: `last_login_time` - should range on one of: - `last_activity_time` - `login_activity_time` --- ## 9) `daily_active_non_subscribed_report` ### PostgreSQL Query Shape `daysInRange = diff_days(start_date, end_date) + 1` (integer) ```sql SELECT c.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, COUNT(DISTINCT DATE(d.updated_at)) AS active_days, MAX(d.updated_at) AS last_activity_at FROM customer_devices d JOIN customers c ON c.sms_cust_id = d.sms_cust_id WHERE d.updated_at BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' AND d.status IN (0, 1) AND NOT EXISTS ( SELECT 1 FROM tbl_customer_subscriptions s WHERE s.sms_cust_id = c.sms_cust_id AND s.status IN (2, 7) ) AND d.app_id = 101 GROUP BY c.sms_cust_id, c.first_name, c.last_name, c.email_id, c.phone_number HAVING COUNT(DISTINCT DATE(d.updated_at)) >= 31 ORDER BY MAX(d.updated_at) DESC LIMIT 200; ``` ### Elasticsearch - Intentionally returns `[]` in `LifecycleReportsElasticsearchService` for this report. - Controller then falls back to PostgreSQL query above. --- ## 10) `new_signups_report` ### PostgreSQL Query Shape ```sql SELECT c.sms_cust_id AS user_id, TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS full_name, c.email_id AS email, c.phone_number AS phone, c.created_at AS signup_at, c.last_login AS last_login_at FROM customers c WHERE c.created_at BETWEEN '2026-05-01 00:00:00' AND '2026-05-31 23:59:59' AND c.app_id = 101 ORDER BY c.created_at DESC LIMIT 200; ``` ### Elasticsearch Must Clause ```json { "range": { "created_at": { "gte": "2026-05-01T00:00:00", "lte": "2026-05-31T23:59:59" } } } ``` --- ## 11) Export Behavior `exportReport()` reuses the same query logic as `getReportData()`: - Try Elasticsearch first (if available and returns rows) - Else fallback to PostgreSQL query builder - Uses `export_limit` from report config (default 10000) - Streams CSV with configured report columns --- ## 12) Request Examples (Dummy) ### Data API ```http GET /admin/api/lifecycle-reports/complete_payments_report?app_id=&start_date=2026-05-01&end_date=2026-05-31&package_ids=1001,1002&limit=200 ``` ### Export API ```http GET /admin/api/lifecycle-reports/complete_payments_report/export?app_id=&start_date=2026-05-01&end_date=2026-05-31&package_ids=1001,1002&limit=all ``` ### Filters API ```http GET /admin/api/lifecycle-reports/filters?app_id= ```