CREATE PROCEDURE [dbo].[create_fact_customers]
    AS
BEGIN
    DROP TABLE IF EXISTS fact_customers;
    CREATE TABLE fact_customers (
         DateKey int null,
         customer_id int not 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,
          invoice_total money null,
          invoice_balance money null,
          invoice_basic money null,
          unbilled_amount money null,
          collection_amount money null,
          due_amount money null,
          due_amount_unbilled money null,
          client_grade_id int null,
          relationship_id int default 0,
          region_type_id int null,
          packages_cnt int null,
          projects_cnt int null
    )
    create index fact_customers_DateKey on fact_customers (DateKey);
create index fact_customers_customer_id on fact_customers(customer_id);
    create CLUSTERED columnstore index cci_fact_customers__index
	on fact_customers;
    DECLARE @CurrentMonth DATE = CAST('2009-07-01' AS DATE)
   DECLARE @EndMonth DATE = EOMONTH(CAST(CONVERT(numeric,YEAR(getdate()))+1 AS VARCHAR)+'-12-31')
   WHILE @CurrentMonth < @EndMonth
    BEGIN
        INSERT INTO fact_customers (DateKey, customer_id, sales_person_id, shared_with, temp_shared_with, leadgen_id, mentor_id, collection_user_id, tl_id, invoice_total,invoice_balance,invoice_basic, unbilled_amount, collection_amount,due_amount,due_amount_unbilled, client_grade_id, relationship_id, region_type_id, packages_cnt, projects_cnt)
        SELECT (YEAR(@CurrentMonth) * 10000 + MONTH(@CurrentMonth) * 100 + DAY(@CurrentMonth)) AS datekey,
               cus.customer_id,
               fa.sales_person_id,
               fa.shared_with, fa.temp_shared_with, fa.leadgen_id, fa.mentor_id, fa.collection_user_id, fa.tl_id,
               invoices.total_inr, invoices.balance_inr, invoices.amount_inr, timesheets.amount_inr, receipts.total_inr,
               due_invoices.balance_inr,
               timesheet_due.amount_inr,
               cus.client_grade_id,cus.relationship_id, cus.region_type_id,
               (select count(distinct parent_id) from fact_package where DateKey = dbo.convertDateToInt(@CurrentMonth) and fact_package.customer_id = cus.customer_id),
               (select count(distinct project_id) from fact_projects where DateKey = dbo.convertDateToInt(@CurrentMonth) and fact_projects.customer_id = cus.customer_id)
               from dim_customer as cus
               left join fact_customer_sales_history fa on fa.history_id = (
                select top 1 fcsh1.history_id from fact_customer_sales_history as fcsh1 where fcsh1.customer_id = cus.customer_id and fcsh1.DateKey <= dbo.convertDateToInt (EOMONTH(@CurrentMonth))
                order by fcsh1.DateKey desc
                )
                left join (select customer_id,date_generated/100 as yearmonth, sum(amount_inr) as amount_inr, sum(balance_inr) as balance_inr, sum(total_inr) as total_inr from dim_invoices
--                 where ignore_from_outstanding = 0
group by customer_id,(date_generated/100)) as invoices on invoices.customer_id = cus.customer_id and invoices.yearmonth = (dbo.convertDateToInt(@CurrentMonth)/100)
                left join (select customer_id,dbo.convertDateToInt(transaction_date)/100 as yearmonth, sum(total_amount * exchange_rate) as total_inr
from mis_billing_transactions_receipt
join mis_customer_accounts mca on mis_billing_transactions_receipt.client_id = mca.account_id
--                 where mca.ignore_from_outstanding = 0
group by customer_id,dbo.convertDateToInt(transaction_date)/100) as receipts on receipts.customer_id = cus.customer_id and receipts.yearmonth = (dbo.convertDateToInt(@CurrentMonth)/100)
               left join (select customer_id,start_date/100 as yearmonth, round(sum(amount_after_adjustment * inr_rate),2) as amount_inr
from dim_timesheets where timesheet_status_id IN (1,4,7)
group by customer_id,start_date/100) as timesheets on timesheets.customer_id = cus.customer_id and timesheets.yearmonth = (dbo.convertDateToInt(@CurrentMonth)/100)
                left join (select customer_id,due_date/100 as yearmonth, sum(balance_inr) as balance_inr from dim_invoices where balance_inr > 0
--                 and ignore_from_outstanding = 0
group by customer_id,(due_date/100)) as due_invoices on due_invoices.customer_id = cus.customer_id and due_invoices.yearmonth = (dbo.convertDateToInt(@CurrentMonth)/100)
                 left join (select customer_id,due_date/100 as yearmonth, round(sum(amount_after_adjustment * inr_rate),2) as amount_inr
from dim_timesheets where timesheet_status_id IN (1,4,7)
group by customer_id,due_date/100) as timesheet_due on timesheet_due.customer_id = cus.customer_id and timesheet_due.yearmonth = (dbo.convertDateToInt(@CurrentMonth)/100)
where customer_start_date <= dbo.convertDateToInt (EOMONTH(@CurrentMonth))
--                 where (first_start_pkg_on <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) or first_project_started_on <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) or (first_start_pkg_on is null and first_project_started_on is null) )
--                 and ((last_package_end_date is null or last_package_end_date >= dbo.convertDateToInt(@CurrentMonth)) or (last_project_delivered_on is null or last_project_delivered_on >= dbo.convertDateToInt(@CurrentMonth)))
        SET @CurrentMonth = DATEADD(MONTH , 1, @CurrentMonth)
    end
END
go

