Datanory Production » Datanory - SNEKKU

--select * Into #upload_temp_di_mobile_txn from di_mobile_txn where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--select * Into #upload_temp_di_mobile_txn_salesman_route from di_mobile_txn_salesman_route where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--select * Into #upload_temp_di_mobile_txn_collection_debit_notes from di_mobile_txn_collection_debit_notes where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--	select * Into #upload_temp_di_mobile_txn_collection_invoices from di_mobile_txn_collection_invoices where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--	select * Into #upload_temp_di_mobile_txn_collection_credit_notes from di_mobile_txn_collection_credit_notes where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--	select * Into #upload_temp_di_mobile_txn_collection_header from di_mobile_txn_collection_header where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

--	select * Into #upload_temp_di_mobile_txn_collection_photo from di_mobile_txn_collection_photo where process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

	--Insert into txn_type_running_no (type, member_id, txn_id, org_id, created_date)

			select 'collection','46350ac9c70345d4a8aafe6b9a74b38b', collection_id, 'SNEKKU', GETDATE() from #upload_temp_di_mobile_txn_collection_header d where process_id = '136058d4-8902-432c-8ed5-f57d185cb974' and not exists (select 1 from txn_type_running_no a where a.txn_id = d.collection_id and type = 'collection'and a.org_id = 'SNEKKU')

			group by collection_id

			--Insert Into txn_mobile_collection_header(col_id,col_no, txn_id, total_cheque_amt, total_cn_amt, total_col_amt, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, reason_id)

			Select * from (

			Select	Max(ch.collection_id) as collection_header_id, 

			CASE WHEN ISNULL(Max(ch.doc_no), '') = '' THEN Trim(Concat('CC', Max(m.member_cd), Right('00000'+ rTrim(

			dbo.fn_getRunningNumberByMobileTransaction('46350ac9c70345d4a8aafe6b9a74b38b', Max(ch.collection_id), 'SNEKKU', 'collection')), 6), '-', convert(char, getdate(), 12))) ELSE Max(ch.doc_no) END as col_no,

			txn.transaction_id, 

			Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) as total_cheque,

			IsNull(Max(cn.cnTotal), 0) as total_cn,

			Sum(Cast(convert(float,IsNull(ch.payment_amount, '0.00')) as decimal(18,2))) + IsNull(Max(cn.cnTotal), 0) as total_collection,

			--Sum(Cast(IsNull(cn.paid,0) as decimal(18,2))),

			--Sum(Cast(ch.payment_amount as decimal(18,2))) +Sum(Cast(IsNull(cn.paid, 0) as decimal(18,2))), 

			Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

			Max(ch.created_date) as created_date, '46350ac9c70345d4a8aafe6b9a74b38b' as created_by, GetDate() as updated_date, '46350ac9c70345d4a8aafe6b9a74b38b' as updated_by, 'SNEKKU' as org_id, 'SNEKKU' as tenant_id, Max(ch.collection_reason) as reason_id

			From #upload_temp_di_mobile_txn txn

			Inner Join #upload_temp_di_mobile_txn_salesman_route sr On sr.transaction_id = txn.transaction_id 

			Left Join (Select customer_id, tenant_id, customer_code from customer union Select new_customer_id, tenant_id, customer_code from Txn_new_customer where approval_status is null) c On c.customer_id = sr.customer_id and c.tenant_id = sr.tenant_id

			Left Join member m on m.member_id = '46350ac9c70345d4a8aafe6b9a74b38b'

			Left Join #upload_temp_di_mobile_txn_collection_header ch On txn.transaction_id = ch.transaction_id 

			Left Join (Select transaction_id, process_id, Sum(Cast(IsNull(paid,0) as decimal(18,2))) as cnTotal from #upload_temp_di_mobile_txn_collection_credit_notes group by transaction_id, process_id) cn On cn.transaction_id = ch.transaction_id

			--Version 2

			Left Join (Select mh.salesman_id, Max(col_no) as col_no 

				From txn_mobile_collection_header mch Inner Join txn_mobile_header mh On mch.txn_id = mh.txn_id

				where mh.salesman_id = '46350ac9c70345d4a8aafe6b9a74b38b'

				group by salesman_id

			) tcmh On tcmh.salesman_id = '46350ac9c70345d4a8aafe6b9a74b38b'

			--Version 2

			--Left Join (Select mh.customer_id, Case When Try_Convert(int, Right(mch.col_no, 6)) Is Null Then 0 Else Right(mch.col_no,6) End as col_no from txn_mobile_collection_header mch Inner Join txn_mobile_header mh On mch.txn_id = mh.txn_id) tcmh On tcmh.customer_id = sr.customer_id

			Where cast(txn.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974' and IsNull(ch.collection_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'

			Group By txn.transaction_id, sr.customer_id, ch.collection_id, ch.collection_status, m.member_cd

			) x Where Not Exists (Select 1 from txn_mobile_collection_header mch where mch.col_id = collection_header_id) ;

			--end txn_mobile_collection_header

			--txn_mobile_collection_detail

			--Select * from txn_mobile_collection_detail

			--18/6/2021: added bank, temporary receipt, payment_narration, journal_notation

			-- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales

			--Insert Into txn_mobile_collection_detail(col_dtl_id, col_id, doc_type, doc_no, doc_amt, doc_date, doc_unpaid, remark, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, mobile_paid, web_paid, bank, journal_notation, payment_narration, temporary_receipt )

			--Collection Header (Cheque)

			Select	ch.collection_header_id, Max(tch.colId), Max(ch.collection_type), Max(ch.collection_reference_id), Max(convert(float,IsNull(ch.payment_amount, 0))), Max(ch.cheque_date), 0, '',

			Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

			Max(ch.created_date), '46350ac9c70345d4a8aafe6b9a74b38b', GetDate(), '46350ac9c70345d4a8aafe6b9a74b38b', 'SNEKKU', 'SNEKKU', Max(convert(float,IsNull(ch.payment_amount, 0))), Max(convert(float,IsNull(ch.payment_amount, 0))), '', '', '', Max(ch.temporary_receipt)

			From #upload_temp_di_mobile_txn_collection_header ch 

			Inner Join (Select Max(case when isnull(collection_id, '') = '' then collection_header_id else collection_id end) as colId, process_id, transaction_id from #upload_temp_di_mobile_txn_collection_header Group By process_id, transaction_id, case when isnull(collection_id, '') = '' then collection_header_id else collection_id end) tch On tch.transaction_id = ch.transaction_id and ch.collection_id = tch.colId

			Where cast(ch.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974'

			and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = ch.collection_header_id) 

			and IsNull(ch.collection_header_id, '') <> '' --and ch.payment_amount NOT LIKE '%e%'

			Group By ch.transaction_id, ch.collection_header_id, ch.collection_status 

			Union All

			--CreditNote

			Select	cn.collection_credit_notes_id, Max(cn.collection_id), 'CN', Max(cnhh.credit_note_no), Max(cnhh.currency_value), Max(cnhh.credit_note_date), 

			Max(cnhh.credit_note_balance), 

			'', 

			Case when Max(cnhh.status) = 'Cancelled' then Max(cnhh.status) else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

			Max(cn.created_date), '46350ac9c70345d4a8aafe6b9a74b38b', GetDate(), '46350ac9c70345d4a8aafe6b9a74b38b', 'SNEKKU', 'SNEKKU', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''

			From #upload_temp_di_mobile_txn_collection_credit_notes cn

			Inner Join txn_credit_note_history_header cnhh On cnhh.credit_note_header_id = cn.credit_note_id

			Where cast(cn.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974' 

			and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id) 

			and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0

			Group By cn.transaction_id, cn.collection_credit_notes_id

			Union All

			--CreditNote (Non Product)

			Select	cn.collection_credit_notes_id, Max(cn.collection_id), 'CN (Non-Product)', Max(cnhh.credit_note_no), Max(cnhh.net_total), Max(cnhh.credit_note_date), 

			Max(cnhh.credit_note_balance), 

			'', 

			Case when Max(cnhh.status) = 'Cancelled' then Max(cnhh.status) else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

			Max(cn.created_date), '46350ac9c70345d4a8aafe6b9a74b38b', GetDate(), '46350ac9c70345d4a8aafe6b9a74b38b', 'SNEKKU', 'SNEKKU', Cast(Max(cn.paid) as decimal(18,2)), Cast(Max(cn.paid) as decimal(18,2)), '','','', ''

			From #upload_temp_di_mobile_txn_collection_credit_notes cn

			Inner Join txn_credit_note_non_product_header cnhh On cnhh.credit_note_id = cn.credit_note_id

			Where cast(cn.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974' 

			and not exists (Select 1 from txn_mobile_collection_detail mcd where mcd.col_dtl_id = cn.collection_credit_notes_id) 

			and IsNull(cn.collection_credit_notes_id, '') <> '' and cast(isnull(cn.paid, 0) as decimal(18,2)) <> 0

			Group By cn.transaction_id, cn.collection_credit_notes_id;

			--end txn_mobile_collection_detail

			--txn_mobile_collection_invoice

			--Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales

					 --  2022-10-20, RL: Added Update for invoice unpaid balance for Van Sales

			--Select * from txn_mobile_collection_invoice

				--Insert Into txn_mobile_collection_invoice (col_inv_id, col_id, inv_id, receipt_no, previous_balance, col_amt, web_paid, new_balance, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)

					Select	ci.collection_invoices_id, Max(ci.collection_id), ci.invoice_id, '', Max(ihh.invoice_balance), Max(isnull(ci.paid, 0)), Max(isnull(ci.paid, 0)),

						Max(ihh.invoice_balance-isnull(ci.paid, 0)),

						Case when Max(ci.collection_status) = 'Cancelled' then Max(ci.collection_status) else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

						Max(ci.created_date), '46350ac9c70345d4a8aafe6b9a74b38b', GetDate(), '46350ac9c70345d4a8aafe6b9a74b38b', 'SNEKKU', 'SNEKKU'

					From #upload_temp_di_mobile_txn_collection_invoices ci 

						Left Join (Select  invoice_id, a.currency_value - sum(isnull(b.web_paid, 0)) as invoice_balance from txn_invoice_history_header a 

						left join txn_mobile_collection_invoice b on a.invoice_id = b.inv_id

						--update by arid 20240325 start

						where b.status in ('Approved','Auto-Approved','Pending')

						--update by arid 20240325 start

						group by invoice_id, a.currency_value) ihh On ihh.invoice_id = ci.invoice_id

					Where not exists (Select 1 from txn_mobile_collection_invoice mci where mci.col_inv_id = ci.collection_invoices_id) 

						and IsNull(ci.invoice_id, '') <> '' and cast(ci.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974' and cast(isnull(ci.paid, 0) as decimal(18,2)) <> 0

					Group By ci.transaction_id,ci.invoice_id, ci.collection_invoices_id; 

					--Insert Into txn_mobile_collection_debit_note(col_dn_id, col_id, debit_note_id, previous_balance, col_amt, web_paid, new_balance, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id, debit_note_type)

					Select	ci.collection_debit_notes_id, Max(ci.collection_id), ci.debit_note_id, Max(isnull(ihh.dn_balance, isnull(dh.debit_note_balance, dnh.debit_note_balance))), Max(isnull(ci.paid, 0)), Max(isnull(ci.paid, 0)),

						Max(isnull(ihh.dn_balance, isnull(dh.debit_note_balance, dnh.debit_note_balance))-isnull(ci.paid, 0)),

						Case when Max(ci.status) = 'Cancelled' then Max(ci.status) else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) end as status,

						Max(ci.created_date), '46350ac9c70345d4a8aafe6b9a74b38b', GetDate(), '46350ac9c70345d4a8aafe6b9a74b38b', 'SNEKKU', 'SNEKKU', case when Max(dh.debit_note_id) is null then 'DN (Non-Product)' else 'DN' end 

					From #upload_temp_di_mobile_txn_collection_debit_notes ci 

						Left Join (Select  isnull(dh.debit_note_id, dnh.debit_note_id) as debit_note_id, isnull(dh.debit_note_amount, isnull(dnh.net_total, 0)) - sum(isnull(b.col_amt, 0)) as dn_balance from txn_mobile_collection_debit_note b 

						left join txn_debit_note_header dh on dh.debit_note_id = b.debit_note_id

						left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = b.debit_note_id

						--update by arid 20240325 start

						where b.status in ('Approved','Auto-Approved','Pending')

						--update by arid 20240325 start

						group by dh.debit_note_id, dnh.debit_note_id, dh.debit_note_amount, dnh.net_total) ihh On ihh.debit_note_id = ci.debit_note_id

						left join txn_debit_note_header dh on dh.debit_note_id = ci.debit_note_id

						left join txn_debit_note_non_product_header dnh on dnh.debit_note_id = ci.debit_note_id

					Where not exists (Select 1 from txn_mobile_collection_debit_note mci where mci.col_dn_id = ci.collection_debit_notes_id) 

						and IsNull(ci.debit_note_id, '') <> '' and cast(ci.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974' and cast(isnull(ci.paid, 0) as decimal(18,2)) <> 0

					Group By ci.transaction_id,ci.debit_note_id, ci.collection_debit_notes_id; 

			--IF ('Credit Sales' = 'Van Sales') -- Update invoice balance for Van Sales

			--	BEGIN

			--		Update txn_invoice_history_header Set

			--			invoice_balance = (invoice_balance - x.payment_amount),

			--			updated_date = GetDate(),

			--			updated_by = '46350ac9c70345d4a8aafe6b9a74b38b'

			--		From (

			--			Select di.invoice_id, SUM(IsNull(Try_Cast(di.paid as decimal(18,2)), 0)) as [payment_amount]

			--			From di_mobile_txn_collection_invoices di

			--			Where di.process_id = '136058d4-8902-432c-8ed5-f57d185cb974'

			--			Group By di.transaction_id, di.invoice_id

			--		) x

			--		Where txn_invoice_history_header.invoice_id = x.invoice_id;

			--	END

			--end txn_mobile_collection_invoice

			--Version 2 -- start txn_mobile_collection_photo

			--Insert into txn_mobile_collection_photo (col_id, col_photo_id, filename, photo_url, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)

				Select Max(ch.collection_id) as [col_id], di.collection_photo_id as [col_photo_id], Max(di.medias_uri) as [filename],

				Concat('/', 'SNEKKU', '/MobileUpload/CollectionPhotos/', Convert(varchar(10),Cast(Max(di.created_date) as datetime), 120) ,'/', '46350ac9c70345d4a8aafe6b9a74b38b','/', di.transaction_id, '/', Max(di.medias_uri)) as [photo_url],

				'Active' as [status], Max(di.created_date) as [created_date], '46350ac9c70345d4a8aafe6b9a74b38b' as [created_by], GETDATE() as [updated_date], '46350ac9c70345d4a8aafe6b9a74b38b' as [updated_by], 'SNEKKU' as [org_id], 'SNEKKU' as [tenant_id]

				From #upload_temp_di_mobile_txn_collection_photo di

				Left Join #upload_temp_di_mobile_txn_collection_invoices ch on ch.transaction_id = di.transaction_id 

				Where cast(di.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974'

					and not exists (Select 1 from txn_mobile_collection_photo mcp where mcp.col_photo_id = di.collection_photo_id) 

					and IsNull(di.collection_photo_id, '') <> ''

				Group By di.transaction_id, di.collection_photo_id

			--Version 2 -- end txn_mobile_collection_photo

			--txn_collection_approval_history

			-- Version 2, 2022-10-14, RL: Added Auto Approval for Van Sales

			--Select * from txn_collection_approval_history

			--Insert Into txn_collection_approval_history([id], [collection_id], [member_id], [status], [status_date], [remark], [approval_flow_id], [approval_type],[approval_sequence], [created_date], [created_by], [updated_date], [updated_by], [org_id], [tenant_id], approval_mode)

			Select newid() as [id], 

			ch.collection_header_id as [collection_id],

			afa.member_id as [member_id],

			Case when ch.collection_status = 'Cancelled' then ch.collection_status else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else 'Pending' End) End as status,

			GetDate() as [status_date], 

			null [remark],

			afa.approval_flow_id as [approval_flow_id],

			'Y' as [approval_type],

			afa.level as [approval_sequence],

			GetDate() as [created_date],

			'46350ac9c70345d4a8aafe6b9a74b38b' as [created_by],

			GetDate() as [updated_date],

			'46350ac9c70345d4a8aafe6b9a74b38b' as [updated_by],	

			ch.org_id as [org_id],

			ch.tenant_id as [tenant_id], 

			approval_mode

			from (select Max(collection_id) as collection_header_id,transaction_id, org_id, tenant_id, process_id, collection_status from #upload_temp_di_mobile_txn_collection_invoices Group By transaction_id, org_id, process_id, tenant_id, collection_status

				UNION

				select Max(collection_id) as collection_header_id,transaction_id, org_id, tenant_id, process_id, status as collection_status from #upload_temp_di_mobile_txn_collection_debit_notes Group By transaction_id, org_id, process_id, tenant_id, status) ch 

			Inner Join #upload_temp_di_mobile_txn_salesman_route sr On sr.transaction_id = ch.transaction_id 

			Left Join (Select customer_id, branch_id from customer c union Select new_customer_id, branch_id from Txn_new_customer where approval_status is null) c On c.customer_id = sr.customer_id

			inner join (

				Select afa.* from approval_flow_approver afa 

				where exists (Select 1 from approval_flow af Where af.activity = 'Collection'

				and convert(date,getdate()) between af.start_date and af.end_date and afa.approval_flow_id=af.approval_flow_id and af.status = 'Active') and afa.status = 'Active'

			) afa On exists (select 1 from approval_flow_branch afb where afb.branch_id=c.branch_id and afa.approval_flow_id=afb.approval_flow_id and afb.status = 'Active') 

			Where cast(sr.process_id as varchar(50)) = '136058d4-8902-432c-8ed5-f57d185cb974'

			and not exists (Select 1 from txn_collection_approval_history cah Where cah.collection_id = ch.collection_header_id)

			order by ch.collection_header_id, afa.level

			--end txn_collection_approval_history

			--IF('Credit Sales' <> 'Van Sales')

			--BEGIN

			--	Update h set h.approver_id=ap.member_id from txn_mobile_collection_header h

			--	left join txn_collection_approval_history ap on h.col_id=ap.collection_id 

			--	where ap.approval_sequence='1' and exists (

			--		select 1 From #upload_temp_di_mobile_txn_collection_invoices txn

			--		where txn.collection_id = h.col_id

			--	)

			--END