CREATE PROCEDURE [dbo].[create_insert_kpi_monthly_package_summary_modified]
AS
BEGIN
    DROP TABLE IF EXISTS kpi_monthly_package_summary_modified;
    CREATE TABLE kpi_monthly_package_summary_modified(
        year int not null,
        month int not null,
        yearmonth varchar(255) null,
        customer_id int null,
        sales_person_id int null,
        shared_with int null,
        temp_shared_with int null,
        mentor_id int null,
        leadgen_id int null,
        collection_user_id int null,
        tl_id int null,
        region_type_id int null,
        relationship_id int default 0,
        package_cnt int null,
        billed_pkg_cnt int null,
        billed_actual_pkg_cnt int null,
        unbilled_pkg_cnt int null,
        unbilled_actual_pkg_cnt int null,
        ignored_timesheets_pkg_cnt int null,
        [0_billing_pkg_cnt] int null,
        total_billed_amt money null,
        total_unbilled_amt money null,
        total_actual_billed_amt money null,
        total_actual_unbilled_amt money null,
        total_invoices_cnt int null,
        total_timesheets_cnt int null,
        total_ignored_timesheets int null,
        total_ignored_timesheets_amt money null,
        total_actual_invoices_cnt int null,
        total_actual_timesheets_cnt int null,
        daily_pkg_cnt int null,
        hourly_pkg_cnt int null,
        monthly_pkg_cnt int null,
        total_daily_rate money null,
        total_hourly_rate money null,
        total_monthly_rate money null,
        started_pkg_cnt int null,
        discontinued_pkg_cnt int null,
        [0-1 Month] int null,
        [1-3 Months] int null,
        [3-6 Months] int null,
        [6-9 Months] int null,
        [9-12 Months] int null,
        [12-18 Months] int null,
        [18-24 Months] int null,
        [24+ Months] int null,
        local_pkg_cnt int null,
        international_pkg_cnt int null,
        onsite_pkg_cnt int null,
        offshore_pkg_cnt int null,
        region_na int null,
        NBNC_cnt int null default 0,
        NBEC_cnt int null default 0,
        Renewal_cnt int null default 0,
        NBNC_amt money null default 0,
        NBNC_amt_cnt int null default 0,
        NBEC_amt money null default 0,
        Renewal_amt money null default 0,
        NBNC_amt_with_shared money null default 0,
        NBEC_amt_with_shared money null default 0,
        Renewal_amt_with_shared money null default 0,
        OTC_cnt int null default 0,
OTC_billed_amt money null default 0,
OTC_unbilled_amt money null default 0,
assigned_amt money null default 0,
shared_amt money null default 0,
total_package_pending money null,
total_package_lost money null,
    billed_days int default 0,
    actual_billed_days int default 0,
    unbilled_days int default 0,
    actual_unbilled_days int default 0,
    client_grade int null,
    collection_amount money null,
    total_lost_amt money null,
--     pkg_tech_bu_id int null
--     res_tech_bu_id int null
                                                     );
  create NONCLUSTERED columnstore index ncci_kpi_monthly_package_summary_modified__index
	    on kpi_monthly_package_summary_modified (yearmonth, customer_id);
      DECLARE @alterTableQuery nvarchar(max) =  '';
    DECLARE @insertQuery nvarchar(max) = '';
    DECLARE @insertRegionQuery nvarchar(max) = '';
    DECLARE @q_region_id nvarchar(10)
    DECLARE @columnName nvarchar(50)
    SELECT @q_region_id = min(region_id) from dim_region
    WHILE @q_region_id IS NOT NULL
        BEGIN
            SET @columnName = 'region_'+@q_region_id;
            SET @alterTableQuery = 'ALTER TABLE kpi_monthly_package_summary_modified ADD '+@columnName+' INT NULL';
            SET @insertRegionQuery = @insertRegionQuery + ',SUM(CASE WHEN fp.region_id = '+@q_region_id+' THEN 1 else 0 end) as '+@columnName
            EXEC sp_executesql @alterTableQuery;
             SELECT @q_region_id = min (region_id) from dim_region where region_id > @q_region_id
        end
     SET @insertQuery = 'INSERT INTO kpi_monthly_package_summary_modified
    select
       mapDate.Year,
       mapDate.Month,
       mapDate.YearMonth,
       fp.customer_id,
       fp.actual_sales_person_id,
       fp.actual_shared_with,
       fp.actual_temp_shared_with,
       fp.actual_mentor_id,
       fp.actual_leadgen_id,
       fp.actual_collection_user_id,
       fp.tl_id,
       fp.region_type_id,
       cus.relationship_id,
       count(distinct fp.parent_id) as package_cnt,
       SUM(CASE WHEN fp.inv_count_in_month > 0 THEN 1 ELSE 0 END) as billed_pkg_cnt,
       SUM(CASE WHEN fp.actual_inv_count > 0 THEN 1 ELSE 0 END) as billed_actual_pkg_cnt,
       SUM(CASE WHEN fp.timesheet_count_in_month > 0 THEN 1 ELSE 0 END) as unbilled_pkg_cnt,
       SUM(CASE WHEN fp.actual_timesheet_count > 0 THEN 1 ELSE 0 END) as unbilled_actual_pkg_cnt,
       SUM(CASE WHEN fp.ignored_timesheets > 0 THEN 1 ELSE 0 END) as ignored_timesheets_pkg_cnt,
       SUM(CASE WHEN fp.ignored_timesheets = 0 and fp.actual_timesheet_count = 0 and fp.actual_inv_count = 0 THEN 1 ELSE 0 END) as ''0_billing_pkg_cnt'',
       SUM(fp.billed_amount) as total_billed_amt,
       SUM(fp.unbilled_amount) as total_unbilled_amt,
           SUM(iif(fp.actual_billed_amount is null,0,fp.actual_billed_amount)) as total_actual_billed_amt,
           SUM(fp.actual_unbilled_amount) as total_actual_unbilled_amt,
       SUM(fp.inv_count_in_month) as total_invoices,
       SUM(fp.timesheet_count_in_month) as total_timesheets,
       SUM(fp.ignored_timesheets) as total_ignored_timesheets,
       SUM(fp.ignored_timesheets_amt) as total_ignored_timesheets_amt,
           SUM(fp.actual_inv_count) as total_actual_invoices_cnt,
           SUM(fp.actual_timesheet_count) as total_actual_timesheets_cnt,
       sum(case when fp.billing_method_id = 1 then 1 else 0 end), --as daily_cnt,
       sum(case when fp.billing_method_id = 2 then 1 else 0 end), --as hourly_cnt,
       sum(case when fp.billing_method_id = 3 then 1 else 0 end), --as monthly_cnt,
       sum(case when fp.billing_method_id = 1 then (fp.package_rate * fp.inr_rate) else 0 end), --as daily_rate,
       sum(case when fp.billing_method_id = 2 then (fp.package_rate * fp.inr_rate) else 0 end), --as hourly_rate,
       sum(case when fp.billing_method_id = 3 then (fp.package_rate * fp.inr_rate) else 0 end), --as monthly_rate,
       count(distinct (case when fp.is_started = 1  then fp.parent_id else null end)) as started_cnt,
       sum(case when fp.is_actual_discontinued = 1 then 1 else 0 end) as stopped_cnt,
       SUM(CASE WHEN fp.duration_since_started <= 1 THEN 1 ELSE 0 END) AS ''0-1 Month'',
       SUM(CASE WHEN duration_since_started > 1 and duration_since_started <=3 THEN 1 ELSE 0 END) AS ''1-3 Months'',
       SUM(CASE WHEN duration_since_started > 3 and duration_since_started<=6 THEN 1 ELSE 0 END) AS ''3-6 Months'',
       SUM(CASE WHEN duration_since_started > 6 and duration_since_started<=9 THEN 1 ELSE 0 END) AS ''6-9 Months'',
       SUM(CASE WHEN duration_since_started > 9 and duration_since_started<=12 THEN 1 ELSE 0 END) AS ''9-12 Months'',
       SUM(CASE WHEN duration_since_started > 12 and duration_since_started<=18 THEN 1 ELSE 0 END) AS ''12-18 Months'',
       SUM(CASE WHEN duration_since_started > 18 and duration_since_started<=24 THEN 1 ELSE 0 END) AS ''18-24 Months'',
       SUM(CASE WHEN duration_since_started > 24 THEN 1 ELSE 0 END) AS ''24+ Months'',
        sum(case when fp.region_type_id = 1 then 1 else 0 end) as local_cnt,
       sum(case when fp.region_type_id = 2 then 1 else 0 end) as international_cnt,
       sum(case when fp.location_id = 1 then 1 else 0 end) as onsite_cnt,
       sum(case when fp.location_id = 2 then 1 else 0 end) as offshore_cnt,
       SUM(CASE WHEN fp.region_id IS NULL THEN 1 else 0 end) as region_na,
       COUNT(distinct (case when sales_type_id = 1 then fp.parent_id else null end)) AS ''NBNC_cnt'',
       COUNT(distinct (case when sales_type_id = 2 then fp.parent_id else null end)) AS ''NBEC_cnt'',
       COUNT(distinct (case when sales_type_id = 6 then fp.parent_id else null end)) AS ''Renewal_cnt'',
       SUM(CASE WHEN actual_sales_type_id = 1 THEN actual_billed_amount ELSE 0 END) as ''NBNC_amt'',
       SUM(CASE WHEN actual_sales_type_id = 1 THEN actual_inv_count ELSE 0 END) as ''NBNC_amt_cnt'',
       SUM(CASE WHEN actual_sales_type_id = 2 THEN actual_billed_amount ELSE 0 END) as ''NBEC_amt'',
       SUM(CASE WHEN actual_sales_type_id = 4 THEN actual_billed_amount ELSE 0 END) as ''Renewal_amt'',
       SUM(CASE WHEN actual_sales_type_id = 1 THEN iif(fp.actual_shared_with is null , actual_billed_amount+actual_unbilled_amount , (actual_billed_amount+actual_unbilled_amount)/2 ) ELSE 0 END) as ''NBNC_amt_with_shared'',
       SUM(CASE WHEN actual_sales_type_id = 2 THEN iif(fp.actual_shared_with is null , actual_billed_amount+actual_unbilled_amount , (actual_billed_amount+actual_unbilled_amount)/2 ) ELSE 0 END) as ''NBEC_amt_with_shared'',
       SUM(CASE WHEN actual_sales_type_id = 4 THEN iif(fp.actual_shared_with is null , actual_billed_amount+actual_unbilled_amount , (actual_billed_amount+actual_unbilled_amount)/2 ) ELSE 0 END) as ''Renewal_amt_with_shared'',
       COUNT(distinct (case when fp.duration_in_months_actual < 1 and (package_status_id = 1 or package_status_id = 2) then fp.parent_id else null end)) AS ''OTC_cnt'',
       SUM(CASE WHEN fp.duration_in_months_actual < 1 and (package_status_id = 1 or package_status_id = 2) THEN actual_billed_amount ELSE 0 END) as ''OTC_billed_amt'',
       SUM(CASE WHEN fp.duration_in_months_actual < 1 and (package_status_id = 1 or package_status_id = 2) THEN actual_unbilled_amount ELSE 0 END) as ''OTC_unbilled_amt'',
       SUM(CASE WHEN fp.actual_shared_with is null THEN actual_billed_amount+actual_unbilled_amount ELSE (actual_billed_amount+actual_unbilled_amount)/2 END) as ''assigned_amt'',
       SUM(CASE WHEN fp.actual_shared_with is not null THEN (actual_billed_amount+actual_unbilled_amount)/2 ELSE 0 END) as ''shared_amt'',
           SUM(billed_pending) as total_package_pending,
           SUM(billed_lost) as total_package_lost,
           SUM(billed_days) as billed_days,
           SUM(actual_billed_days) as actual_billed_days,
           SUM(unbilled_days) as unbilled_days,
           SUM(actual_unbilled_days) as actual_unbilled_days,
           cus.client_grade_id,
           0,
           null'
--            bu_pkg_tech.bu_id as pkg_tech_bu_id
--            bu_res_tech.bu_id as res_tech_bu_id
--        (SELECT SUM(balance_inr) FROM dim_invoices
--        join dim_date as inv_start on inv_start.DateKey = dim_invoices.start_date
--        where package_id > 0 and inv_start.Year <= mapDate.Year and inv_start.Month <= mapDate.Month
--          and fp.customer_id = dim_invoices.customer_id and dim_invoices.ignore_from_outstanding != 1) as total_package_pending,
--             (SELECT SUM(amount_after_adjustment * dim_timesheets.inr_rate) FROM dim_timesheets
--        join dim_date as tim_start on tim_start.DateKey = dim_timesheets.start_date
--        where tim_start.Year <= mapDate.Year and tim_start.Month <= mapDate.Month
--          and fp.parent_id = dim_timesheets.crp_parent_id and timesheet_status_id IN (1,7)) as total_package_unbilled_pending
        SET @insertQuery = @insertQuery + @insertRegionQuery;
SET @insertQuery = @insertQuery+ ' from fact_package as fp
join dim_date as mapDate on fp.DateKey = mapDate.DateKey
    join dim_customer as cus on cus.customer_id = fp.customer_id
--     left join mis_bu_technology as bu_pkg_tech on bu_pkg_tech.technology_id = fp.pkg_tech_id
--     left join mis_bu_technology as bu_res_tech on bu_res_tech.technology_id = fp.res_tech_id
--     where fp.ignore_from_outstanding != 1
group by mapDate.Year, mapDate.Month, mapDate.YearMonth, fp.customer_id,
         fp.actual_sales_person_id,fp.actual_shared_with, fp.actual_temp_shared_with, fp.actual_mentor_id,
         fp.actual_leadgen_id, fp.actual_collection_user_id, fp.tl_id, fp.region_type_id,cus.client_grade_id,cus.relationship_id'

    ;
    EXEC sp_executesql @insertQuery;
     update kpi_monthly_package_summary_modified
    set collection_amount  = (select sum(total_amount * exchange_rate) from mis_billing_transactions_receipt
        join mis_customer_accounts mca on mis_billing_transactions_receipt.client_id = mca.account_id
        where mca.customer_id = kpi_monthly_package_summary_modified.customer_id and transaction_date
        between
        cast(kpi_monthly_package_summary_modified.yearmonth+'-01' as date) and EOMONTH(cast(kpi_monthly_package_summary_modified.yearmonth+'-01' as date))
        );

    update kpi_monthly_package_summary_modified
    set total_lost_amt  = (select sum(pending_amount / exchange_rate) from dim_lost_invoices
        where dim_lost_invoices.package_id > 0 and dim_lost_invoices.customer_id = kpi_monthly_package_summary_modified.customer_id and (case when start_date is null then date_generated else start_date end)
        between
        cast(kpi_monthly_package_summary_modified.yearmonth+'-01' as date) and EOMONTH(cast(kpi_monthly_package_summary_modified.yearmonth+'-01' as date))
        );

     UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_kpi_monthly_package_summary_modified';
end
go

