CREATE PROCEDURE [dbo].[create_insert_dim_invoices]
    AS
BEGIN
    DROP TABLE IF EXISTS dim_invoices;
    create table dim_invoices
    (
        id int not null,
        invoice_number varchar(250) not null,
        description text null,
        start_date int null,
        end_date int null,
        date_generated int null,
        amount money null,
        amount_inr money null,
        tax money null,
        tax_inr money null,
        balance money null,
        balance_inr money null,
        total money null,
        total_inr money null,
        paidamt money null,
        paidamt_inr money null,
        account_id int null,
        customer_id int null,
        currency_id int null,
        package_id int null,
        project_id int null,
        child_package_id int null,
        inv_request_id int null,
        duration_in_days int null,
        per_day_amount money null,
        per_day_amount_without_tax money null,
        inr_rate float null,
        service_type_id int null,
        invoice_status_id int,
        ignore_from_outstanding bit default 0,
        is_proforma_invoice bit default 0,
        grp_inv_num varchar(255) null,
        grp_inv_date int null,
        is_lost_invoice bit default 0,
        lost_amount money default 0,
        is_during_package_active bit default 0,
        billed_days int default 0,
        due_date varchar(255) null
    );
    create index dim_invoices_start_date_index
        on dim_invoices (start_date);

create index dim_invoices_date_generated_index
        on dim_invoices (date_generated);

create index dim_invoices_customer_id_index
        on dim_invoices (customer_id);

create index dim_invoices_package_id_index
        on dim_invoices (package_id);

INSERT INTO dim_invoices
select
id,
new_invoice_id,
description,
CONVERT(varchar,start_date,112),
CONVERT(varchar,end_date,112),
CONVERT(varchar,date_generated,112),
ammount,
ammount_inr,
tax,
tax_inr,
total as balance,
total_inr as balance_inr,
(ammount+tax) as total,
(ammount_inr+tax_inr) as total_inr,
paidamt,
paidamt_inr,
i.mis_client_id,
fa.customer_id,
curr.currency_id,
mis_resource_id,
mis_project_id,
child_package_id,
inv_request_id,
DATEDIFF(DD,start_date,end_date)+1,
       (ammount_inr+tax_inr)/(DATEDIFF(DD,start_date,end_date)+1) as per_day_amount,
       (ammount_inr)/(DATEDIFF(DD,start_date,end_date)+1) as per_day_amount_without_tax,
inr_rate,
dist.service_type_id,
dis.status_id,
fa.ignore_from_outstanding,
       CASE WHEN proforma_invoice = new_invoice_id THEN 1 ELSE 0 END as is_proforma_invoice,
       CASE WHEN grp_inv_num = '' or grp_inv_num IS NULL THEN NULL ELSE grp_inv_num END,
       CONVERT(varchar,grp_inv_date,112),
       CASE WHEN lost_id is not null then 1 else 0 end,
       CASE WHEN lost_id is not null then mli.pending_amount*i.exchange_rate else 0 end,
       case when (select count(crp_id) from dim_customer_package as dcp where  CONVERT(varchar,i.start_date,112) between ((dcp.start_date/100)*100+01) and iif(dcp.actual_end_date is null,CONVERT(varchar,EOMONTH(getdate()),112),((dcp.actual_end_date/100)*100+31)) and dcp.parent_id = i.mis_resource_id) > 0 then 1 else 0 end,
       case when start_date is not null then dbo.returnNumOfWorkingDays(i.start_date, i.end_date) else 0 end,
       dbo.convertDateToInt(CASE WHEN cm.payment_terms IS NULL AND mis_resource_id > 0 THEN i.start_date
            WHEN cm.payment_terms IS NOT NULL AND pymt.has_days = 1 AND pymt.based_on = 'start_date' AND mis_resource_id > 0 THEN DATEADD(DD, cm.due_days, i.start_date)
            WHEN cm.payment_terms IS NOT NULL AND pymt.has_days != 1 AND pymt.based_on = 'start_date' AND mis_resource_id > 0 THEN i.start_date
            WHEN cm.payment_terms IS NOT NULL AND pymt.has_days = 1 AND pymt.based_on = 'end_date' AND mis_resource_id > 0 THEN DATEADD(DD, cm.due_days, i.end_date)
            WHEN cm.payment_terms IS NOT NULL AND pymt.has_days != 1 AND pymt.based_on = 'end_date' AND mis_resource_id > 0 THEN i.end_date
            WHEN cm.payment_terms IS NULL AND mis_resource_id = 0 THEN date_generated
            WHEN mis_resource_id = 0 AND pymt.has_days = 1 THEN DATEADD(DD,cm.due_days,date_generated)
            WHEN mis_resource_id = 0 AND pymt.has_days != 1 THEN date_generated END) as due_date



from mis_invoices_master as i
left join dim_invoice_service_type as dist on dist.service_type_value = i.service_type
left join mis_currency_master as curr on curr.currency_symbol = i.currency
left join dim_invoice_statuses as dis ON dis.status_label = i.status
left join mis_customer_accounts fa on fa.account_id = i.mis_client_id
left join mis_customer_master as cm on cm.customer_id = fa.customer_id
left join mis_payment_terms as pymt on pymt.terms_id = cm.payment_terms
left join mis_lost_invoices mli on mli.invoice_id = i.new_invoice_id;

UPDATE dim_invoices set invoice_status_id = 2 where invoice_status_id = 1 and paidamt > 0;
UPDATE dim_customer set invoices_count = (select count(id) from dim_invoices where customer_id = dim_customer.customer_id);
UPDATE dim_customer set customer_start_date = (select min(case when start_date is null then date_generated else start_date end) from dim_invoices where customer_id = dim_customer.customer_id)
    where invoices_count > 0 and customer_start_date is null;
         UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_dim_invoices';


END;
go

