USE [JSDataMiddlePlatform];
GO

/*
  Template: 安装服务质量分析
  Window: 最近90天 [date_from, date_to)
*/

DECLARE @date_from datetime2 = CAST(DATEADD(DAY, -90, CAST(GETDATE() AS date)) AS datetime2);
DECLARE @date_to datetime2 = CAST(DATEADD(DAY, 1, CAST(GETDATE() AS date)) AS datetime2);

IF OBJECT_ID('tempdb..#ticket') IS NOT NULL
    DROP TABLE #ticket;

SELECT
    i.Id,
    i.SubmitTime,
    i.OrderNum,
    ISNULL(NULLIF(i.ShopCode, ''), 'unknown') AS shop_code,
    ISNULL(NULLIF(i.Province, ''), 'unknown') AS province,
    ISNULL(NULLIF(i.City, ''), 'unknown') AS city,
    ISNULL(NULLIF(i.Operation, ''), 'unknown') AS operation_user,
    ISNULL(NULLIF(i.sourceFrom, ''), 'unknown') AS source_from,
    TRY_CONVERT(int, i.StatusId) AS status_id,
    TRY_CONVERT(int, i.installComplete) AS install_complete,
    TRY_CONVERT(decimal(18, 2), i.PayMent) AS payment_amount,
    TRY_CONVERT(decimal(18, 2), i.FinalPayMent) AS final_payment_amount,
    TRY_CONVERT(decimal(18, 2), i.installcost) AS install_cost,
    TRY_CONVERT(decimal(18, 2), i.settleAmount) AS settle_amount,
    TRY_CONVERT(decimal(18, 2), i.acceptAmount) AS accept_amount,
    TRY_CONVERT(int, i.kfsh) AS kf_audit,
    TRY_CONVERT(int, i.cwsh) AS cw_audit,
    i.SourceGuid
INTO #ticket
FROM dbo.fys_Ticketing_install AS i WITH (NOLOCK)
WHERE i.SubmitTime >= @date_from
  AND i.SubmitTime < @date_to
  AND (i.[Delete] = 0 OR i.[Delete] IS NULL)
  AND ISNULL(NULLIF(i.OrderNum, ''), '') <> '';

IF OBJECT_ID('tempdb..#item_agg') IS NOT NULL
    DROP TABLE #item_agg;

SELECT
    ii.guid AS source_guid,
    COUNT(*) AS item_count,
    SUM(ISNULL(ii.installcount, 0)) AS install_count,
    SUM(ISNULL(ii.Payfee, 0)) AS pay_fee_sum,
    SUM(ISNULL(ii.applyItemAmount, 0)) AS apply_item_amount_sum,
    SUM(ISNULL(ii.settleItemAmount, 0)) AS settle_item_amount_sum,
    SUM(ISNULL(ii.acceptItemAmount, 0)) AS accept_item_amount_sum,
    MAX(ISNULL(NULLIF(ii.scmname, ''), 'unknown')) AS sample_scm_name
INTO #item_agg
FROM dbo.fys_Ticketing_install_item AS ii WITH (NOLOCK)
GROUP BY ii.guid;

-- 1) 总览
SELECT
    @date_from AS date_from,
    @date_to AS date_to,
    COUNT(*) AS ticket_count,
    COUNT(DISTINCT ordernum) AS order_count,
    SUM(ISNULL(payment_amount, 0)) AS payment_amount,
    SUM(ISNULL(final_payment_amount, 0)) AS final_payment_amount,
    SUM(ISNULL(install_cost, 0)) AS install_cost,
    SUM(ISNULL(settle_amount, 0)) AS settle_amount,
    SUM(ISNULL(accept_amount, 0)) AS accept_amount
FROM #ticket;

-- 2) 工单状态分布
SELECT
    status_id,
    install_complete,
    COUNT(*) AS ticket_count,
    SUM(ISNULL(payment_amount, 0)) AS payment_amount,
    SUM(ISNULL(install_cost, 0)) AS install_cost
FROM #ticket
GROUP BY status_id, install_complete
ORDER BY ticket_count DESC;

-- 3) 服务商表现（取明细表scm）
SELECT TOP (30)
    ISNULL(NULLIF(ii.scmname, ''), 'unknown') AS scm_name,
    COUNT(*) AS item_count,
    SUM(ISNULL(ii.installcount, 0)) AS install_count,
    SUM(ISNULL(ii.Payfee, 0)) AS pay_fee_sum,
    SUM(ISNULL(ii.settleItemAmount, 0)) AS settle_item_amount_sum,
    SUM(ISNULL(ii.acceptItemAmount, 0)) AS accept_item_amount_sum
FROM dbo.fys_Ticketing_install_item AS ii WITH (NOLOCK)
WHERE ii.createTime >= @date_from
  AND ii.createTime < @date_to
GROUP BY ISNULL(NULLIF(ii.scmname, ''), 'unknown')
ORDER BY item_count DESC;

-- 4) 区域问题清单（客服/财务未审核）
SELECT TOP (50)
    t.province,
    t.city,
    COUNT(*) AS pending_ticket_count,
    SUM(ISNULL(t.payment_amount, 0)) AS payment_amount,
    SUM(ISNULL(t.install_cost, 0)) AS install_cost
FROM #ticket AS t
WHERE ISNULL(t.kf_audit, 0) <> 1
   OR ISNULL(t.cw_audit, 0) <> 1
GROUP BY t.province, t.city
ORDER BY pending_ticket_count DESC, payment_amount DESC;