USE [DMS_EcoSystem]
GO
/****** Object:  StoredProcedure [dbo].[sp_sync_download_invoice]    Script Date: 18/6/2024 4:23:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Declare @batch_no int = 3,
--@last_sync_date varchar(50) = '',
--@batch_size varchar(50) = 100,
--@token_id varchar(50) = 'BFDEF787-3787-42ED-B3EC-A912986FEAA9',
--@checkTotal varchar(50) = 'N' 
--Select * from sync_mobile_token where member_id = 'julies-J-J15'

--version arif 20240618 to exclude cancelled invoice
alter   PROCEDURE [dbo].[sp_sync_download_invoice]

@batch_no int,
@last_sync_date varchar(50),
@batch_size varchar(50),
@token_id varchar(50),
@checkTotal varchar(50)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

CREATE TABLE #integration_error_log(
	[id] [varchar](50) NULL,
	[token_id] [varchar](50) NULL,
	[member_id] [varchar](50) NULL,
	[org_id] [varchar](50) NULL,
	[integration_type] [varchar](100) NULL,
	[module_id] [varchar](100) NULL,
	[module_name] [varchar](100) NULL,
	[sp_name] [varchar](100) NULL,
	[error_type] [varchar](100) NULL,
	[exception_msg] [varchar](max) NULL,
	[exception_value] [varchar](100) NULL,
	[process_id] [varchar](50) NULL,
	[created_date] [datetime] NULL
)

CREATE TABLE #temp_table(
    action [varchar](50) NULL, -- For the conditional 'action'
    id [varchar](50) NULL, -- For 'invoice_id' as 'id'
    customer_id [varchar](50) NULL, -- For 'customer_id'
    docNo [varchar](50) NULL, -- For 'invoice_no' as 'docNo'
    docDate [datetime] NULL, -- For 'invoice_date' as 'docDate'
    docAmount [decimal](18,2) NULL, -- For 'currency_value' as 'docAmount'
    unpaid [decimal](18,2) NULL, -- For 'unpaid'
    discount1 [decimal](18,2) NULL, -- For 'discount1'
	totalOrderLevelPromoDiscount [decimal](18,2) NULL,
	perpetualDiscountAmount [decimal](18,2) NULL

)


declare @intBatchsize int = 100, @tenant_id varchar(50), @member_id varchar(50), @org_id varchar(50) 

select top 1 @tenant_id = tenant_id, @member_id = member_id,@org_id = org_id from sync_mobile_token where token_id = @token_id

--Temp table for on_behalf
Select customer_id, oba.member_id as user_id, ob.member_id as salesman_id Into #on_behalf from on_behalf_access_customer obc
Inner Join on_behalf_access oba On oba.access_id = obc.access_id
Inner Join on_behalf ob On ob.on_behalf_id = obc.on_behalf_id Where ob.status = 'Active' and oba.member_id = @member_id and (GetDate() between ob.start_date and ob.end_date)
		
if(@batch_size <> '' and @batch_size is not null)
BEGIN
     set @intBatchsize  = cast(@batch_size as int)
END
ELSE
BEGIN
	set @intBatchsize  = 100
END

If(@checkTotal ='Y')
	begin
		select @intBatchsize = '99999999', @batch_no=1
	END


	IF(IsNull(@last_sync_date,'') = '')
	BEGIN
	print('3');
	--select 'Insert/Update' as action, invoice as id ,customer as customer_id,  invoice as docNo, 
	--InvoiceDate as docDate,
	--CurrencyValue as docAmount, unpaid as unpaid
	--from 
	--(SELECT  ROW_NUMBER() OVER (ORDER BY customer, invoice ) AS TotalRowNumber, customer, Invoice, convert(varchar(10),cast(InvoiceDate as date),120) as InvoiceDate, 
	--CurrencyValue, cast(InvoiceBalance as decimal(18,2)) * -1 as unpaid from di_syspro_invoice where DocumentType ='I'
	--group by customer, Invoice, InvoiceDate, CurrencyValue, InvoiceBalance 	
	--) a
	-- where TotalRowNumber between
	-- (@batch_no*@intBatchsize)+1-@intBatchsize and @batch_no * @intBatchsize;

	
		insert into #temp_table
		SELECT 
		CASE status WHEN 'Active' THEN 'Insert/Update' END as action, 
		invoice_id as id, customer_id as customer_id, invoice_no as docNo, invoice_date as docDate,
		currency_value as docAmount, unpaid as unpaid,
		Case When IsNull(discount1, '') = '' Then '0' Else discount1 End as discount1,  ISNULL(order_web_total_discount_amount, 0) as totalOrderLevelPromoDiscount, ISNULL(perpetual_discount_amount, 0) as perpetualDiscountAmount
		FROM(
		SELECT  ROW_NUMBER() OVER (ORDER BY customer_id, invoice_id ) AS TotalRowNumber, currency_value,
		cast(invoice_balance as decimal(18,2)) as unpaid, discount1, status, customer_code, invoice_date, invoice_no, invoice_id, customer_id, order_web_total_discount_amount, perpetual_discount_amount, updated_date
		from(
			select i.invoice_id
			, i.customer_id
			, i.currency_value, i.invoice_balance, 'Active' as status
			, c.customer_code
			, i.invoice_date, i.invoice_no, i.discount1, i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE i.tenant_id = @tenant_id 
			and exists (Select 1 from customer_to_user_relation cr where cr.customer_id= c.customer_id and cr.user_id= @member_id
				and convert(date,getdate()) between start_date and end_date)
				--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			Union
		
			-- Modify on 20230926 (Download Sub Account data)
			select i.invoice_id, c.master_account as customer_id, i.currency_value, i.invoice_balance, 'Active' as status, c.customer_code, i.invoice_date, i.invoice_no, i.discount1, i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			inner join org o on o.org_id = i.org_id
			WHERE i.tenant_id = @tenant_id and o.master_acc_billing = 'Yes'
			and exists (
				select 1 from customer_to_user_relation cr where cr.user_id = @member_id and cr.customer_id = c.master_account 
				and convert(date,getdate()) between start_date and end_date
			)

			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end

			Union
			--Details for on behalf salesman
			select i.invoice_id
			,i.customer_id
			, i.currency_value, i.invoice_balance, 'Active' as status
			, c.customer_code
			, i.invoice_date, i.invoice_no, i.discount1, i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE i.tenant_id = @tenant_id 
			and exists (Select 1 from #on_behalf obc Where obc.customer_id = c.customer_id)
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			
			-- Modify on 20230926 (Download Sub Account data)
			Union
			--Details for on behalf salesman
			select i.invoice_id, c.master_account as customer_id, i.currency_value, i.invoice_balance, 'Active' as status, c.customer_code, i.invoice_date, i.invoice_no, i.discount1, i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			inner join org o on o.org_id = i.org_id
			WHERE i.tenant_id = @tenant_id and o.master_acc_billing = 'Yes'
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			and  exists (Select obc.customer_id from #on_behalf obc where obc.customer_id = c.master_account)

			)x where Convert(varchar(10), updated_date, 120) >= Convert(varchar(10), DATEADD(MONTH, -2, GetDate()), 120) OR invoice_balance > 0

			
		
		)v
		where TotalRowNumber between
		(@batch_no*@intBatchsize)+1-@intBatchsize and @batch_no * @intBatchsize;

	END
	ELSE
	BEGIN

	print('4');
		insert into #temp_table
		SELECT 
		CASE status WHEN 'Active' THEN 'Insert/Update'
		WHEN 'Inactive' THEN 'Delete' END as action, invoice_id as id, customer_id as customer_id, invoice_no as docNo, invoice_date as docDate,
		currency_value as docAmount, unpaid as unpaid,
		Case When ISNULL(discount1, '') = '' Then '0' Else discount1 End as discount1, order_web_total_discount_amount as totalOrderLevelPromoDiscount, perpetual_discount_amount as perpetualDiscountAmount
		FROM(
		SELECT  ROW_NUMBER() OVER (ORDER BY customer_id, invoice_id ) AS TotalRowNumber, currency_value,
		cast(invoice_balance as decimal(18,2))  as unpaid, status, customer_code, invoice_date, invoice_no, invoice_id, customer_id, discount1,  order_web_total_discount_amount, perpetual_discount_amount, updated_date 
		from(
			select i.invoice_id
			, i.customer_id
			, i.currency_value, i.invoice_balance, 'Active' as status
			, c.customer_code
			, i.invoice_date, i.invoice_no, i.discount1,  i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE i.tenant_id = @tenant_id and i.updated_date > @last_sync_date
			and exists (Select 1 from customer_to_user_relation cr where cr.customer_id= c.customer_id and cr.user_id= @member_id
				and convert(date,getdate()) between start_date and end_date)	
				--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			Union
			-- Modify on 20230926 (Download Sub Account data)
			select i.invoice_id, c.master_account as customer_id, i.currency_value, i.invoice_balance, 'Active' as status, c.customer_code, i.invoice_date, i.invoice_no, i.discount1,  i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			inner join org o on o.org_id = i.org_id
			WHERE i.tenant_id = @tenant_id and o.master_acc_billing = 'Yes' and i.updated_date > @last_sync_date
			and exists (
				select 1 from customer_to_user_relation cr
				where cr.user_id = @member_id and cr.customer_id = c.master_account
				and convert(date,getdate()) between start_date and end_date
			)
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			UNION
			select ii.invoice_id
			,ii.customer_id
			, ii.currency_value, ii.invoice_balance, 'Inactive' as status
			, c.customer_code
			, ii.invoice_date, ii.invoice_no, ii.discount1, ii.order_web_total_discount_amount, ii.perpetual_discount_amount, ii.updated_date
			from txn_invoice_history_header_at ii
			INNER JOIN customer c on c.customer_id= ii.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE ii.tenant_id = @tenant_id and ii.at_date > @last_sync_date and ii.at_type='D'
			and exists (Select 1 from customer_to_user_relation cr where cr.customer_id= c.customer_id and cr.user_id= @member_id
				and convert(date,getdate()) between start_date and end_date)
				--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			Union
			--Details for on behalf salesmans
			select i.invoice_id
			, i.customer_id
			, i.currency_value, i.invoice_balance, 'Active' as status
			, c.customer_code
			, i.invoice_date, i.invoice_no, i.discount1,  i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE i.tenant_id = @tenant_id and i.updated_date > @last_sync_date
			and exists (Select 1 from #on_behalf obc Where obc.customer_id = c.customer_id)	
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			UNION
			select ii.invoice_id
			, ii.customer_id
			, ii.currency_value, ii.invoice_balance, 'Inactive' as status
			, c.customer_code
			, ii.invoice_date, ii.invoice_no, ii.discount1, ii.order_web_total_discount_amount, ii.perpetual_discount_amount, ii.updated_date
			from txn_invoice_history_header_at ii
			INNER JOIN customer c on c.customer_id= ii.customer_id
			left join customer mc on mc.customer_id = c.master_account
			WHERE ii.tenant_id = @tenant_id and ii.at_date > @last_sync_date and ii.at_type='D'
			and exists (Select 1 from #on_behalf obc Where obc.customer_id = c.customer_id)
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			UNION
			
			-- Modify on 20230926 (Download Sub Account data)
			select i.invoice_id, c.master_account as customer_id, i.currency_value, i.invoice_balance, 'Active' as status, c.customer_code, i.invoice_date, i.invoice_no, i.discount1,  i.order_web_total_discount_amount, i.perpetual_discount_amount, i.updated_date
			from txn_invoice_history_header i
			inner join customer c on c.customer_id = i.customer_id
			inner join org o on o.org_id = i.org_id
			WHERE i.tenant_id = @tenant_id and o.master_acc_billing = 'Yes'  and i.updated_date > @last_sync_date
			--version arif 20240618 start
				and invoice_status <> 'Cancelled'
				--version arif 20240618 end
			and exists (Select 1 from #on_behalf obc where obc.customer_id = c.master_account)

			)x where Convert(varchar(10), updated_date, 120) >= Convert(varchar(10), DATEADD(MONTH, -2, GetDate()), 120) OR invoice_balance > 0
		)v
		where TotalRowNumber between
		(@batch_no*@intBatchsize)+1-@intBatchsize and @batch_no * @intBatchsize;

end


insert into #integration_error_log
		([id],[token_id],[member_id],[org_id],[integration_type],[module_id],[module_name],[sp_name],[error_type],[exception_msg],[exception_value],[process_id],[created_date])
		select newid() [id],@token_id [token_id],@member_id [member_id],@tenant_id [org_id],'Mobile Download' [integration_type],
		'E6D58D21-3172-4FB8-9357-C0117361DEAD' as module_id,'DownloadInvoice' [module_name],'sp_sync_download_invoice - batch ' + convert(varchar,@batch_no) [sp_name],
		'Duplication'[error_type],'Duplicated Invoice -' + x.id [exception_msg], x.id,@token_id [process_id],getdate() [created_date]
		from (
			select a.id, a.customer_id from #temp_table a
			group by a.id, a.customer_id
			having count(1) > 1
		) x
		
		
set @intBatchsize  = case when @batch_size <> '' and @batch_size is not null then cast(@batch_size as int) else 100 end
	
	If(@checkTotal ='Y')
	begin
		SELECT  CEILING( count(1)  / cast(@intBatchsize as decimal(18,6))) 
		FROM #temp_table a  
		where not exists (select 1 from #integration_error_log ex where a.id=ex.[exception_value])
	END
	ELSE
	BEGIN
		insert into [integration_data_log]
		([id],[token_id],[member_id],[org_id],[integration_type],[module_id],[module_name],[sp_name],[pk1_type],[pk1_value],[pk2_type],[pk2_value],[pk3_type],[pk3_value],[process_id],[created_date])
		select newid() [id],@token_id [token_id],@member_id [member_id],@tenant_id [org_id],'Mobile Download' [integration_type],
		'E6D58D21-3172-4FB8-9357-C0117361DEAD' as module_id,'DownloadInvoice' [module_name],'sp_sync_download_invoice - batch ' + convert(varchar,@batch_no) [sp_name],
		'invoice_id' [pk1_type],a.id [pk1_value],'' [pk2_type],'' [pk2_value],'' [pk3_type],'' [pk3_value],
		@token_id [process_id],getdate() [created_date] from #temp_table a
		where not exists (select 1 from #integration_error_log ex where a.id=ex.[exception_value])

		select * from #temp_table a
		where not exists (select 1 from #integration_error_log ex where a.id=ex.[exception_value])
	END
	
	insert into integration_error_log
	([id],[token_id],[member_id],[org_id],[integration_type],[module_id],[module_name],[sp_name],[error_type],[exception_msg],[exception_value],[process_id],[created_date])
	select [id],[token_id],[member_id],[org_id],[integration_type],[module_id],[module_name],[sp_name],[error_type],[exception_msg],[exception_value],[process_id],[created_date] from #integration_error_log

	drop table #temp_table
	drop table #integration_error_log

drop table #on_behalf