CREATE PROCEDURE [dbo].[create_insert_kpi_monthly_customer_project_summary]
    AS
BEGIN
   DROP TABLE IF EXISTS kpi_monthly_customer_project_summary;
   CREATE TABLE kpi_monthly_customer_project_summary(
       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,
        leadgen_id int null,
        mentor_id int null,
        collection_user_id int null,
        tl_id int null,
        region_type_id int null,
        relationship_id int default 0,
        projects_cnt int null,
        project_amount money null,
        invoice_amount money null,
        invoice_pending_amount money null,
        pending_amount_till_date money null,
        unrealized_amt money null,
        closed_projects int null,
        closed_project_amt money null,
        closed_project_inv_amt money null,
        delivered_projects int null,
        client_grade int null,
        total_lost_amount money null
   );
        create index kpi_monthly_customer_project_summary_yearmonth_index on kpi_monthly_customer_project_summary(yearmonth);
           create NONCLUSTERED columnstore index ncci_kpi_monthly_customer_project_summary__index
	on kpi_monthly_customer_project_summary (yearmonth, customer_id);
       INSERT INTO kpi_monthly_customer_project_summary
       select ddate.Year,
              ddate.Month,
              ddate.YearMonth,
              t.customer_id,
              t.sales_person_id,
              t.shared_with,
              t.temp_shared_with,
              t.leadgen_id,
              t.mentor_id,
              t.collection_user_id,
              t.tl_id,
              cus.region_type_id,
              cus.relationship_id,
               count(distinct t.project_id) as projects,
               sum(project_amount * inr_rate),
               sum(isnull(invoice_amount,0) * isnull(inr_rate,1)),
               sum(isnull(invoice_pending_amount,0)),
               sum(isnull(pending_amt_till_date,0) * isnull(inr_rate,1)),
               sum((isnull(project_amount,0) - isnull(project_billed,0)) * isnull(inr_rate,1)),
              count(distinct (iif(is_closed = 1,t.project_id,null))),
              sum(case when is_closed = 1 then project_amount*inr_rate else 0 end),
              sum(case when is_closed = 1 then invoice_amount*inr_rate else 0 end),
              count(distinct (case when is_delivered = 1 then t.project_id else null end)),
              cus.client_grade_id,0
       from fact_projects as t
       join dim_customer_project as p on p.project_id = t.project_id
       join dim_customer as cus on cus.customer_id = p.customer_id
       join dim_date as ddate on ddate.DateKey = t.Datekey
--        where p.ignore_from_outstanding != 1 and p.project_type_id = 1
       group by ddate.Year, ddate.Month, ddate.YearMonth,
       t.customer_id, t.sales_person_id,
       t.shared_with, t.temp_shared_with,
       t.leadgen_id,t.mentor_id,t.collection_user_id,
       t.tl_id,cus.region_type_id,cus.client_grade_id,cus.relationship_id;

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

     UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_kpi_monthly_customer_project_summary';

END
go

