Workflow Get Instalment - Flow

Backend URL : https://loantap.in/wp-admin/post.php?post=758495&action=edit

Frontend URL : https://loantap.in/banking/workflow-get-instalments

Get Instalment Flow takes 3 Steps to complete.

Step 1 :  Generate Instalment Data

Query Description:

Query :
To see query check Get Instalment

Create Temporary table in marketing

Insert above query result 

[report_data_query raw]
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  DROP TABLE IF EXISTS marketing.[env.search.ticket_id /];
  CREATE TABLE marketing.[env.search.ticket_id /] (
	`ID` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
	`sublan_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
	`lan_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`lapp_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`installment_method` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`nbfc` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`lapp_status_label` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`scheme_id` varchar(50) COLLATE utf8mb4_unicode_ci  NULL,
	`previous_instalment` varchar(50) DEFAULT 0,
	`previous_instalment_posted_date` varchar(10) DEFAULT NULL,
	`total_principal` varchar(50) DEFAULT 0,
	`total_interest` varchar(50) DEFAULT 0,
	`total_instalment` varchar(50) DEFAULT 0,
	`next_instalment_amount` varchar(50) DEFAULT 0,
	`tentative_instalment_amount` varchar(50) DEFAULT 0,
	`no_of_instalments` varchar(50) DEFAULT 0,
	`past_instalments` varchar(50) DEFAULT 0,
	`left_instalments` varchar(50) DEFAULT 0,
	`next_instalment_date` varchar(10) DEFAULT NULL,
	`next_instalment_due_date` varchar(10) DEFAULT NULL,
	`end_date` varchar(10) DEFAULT NULL,		
	`service` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`current_page` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`status` varchar(50) COLLATE utf8mb4_unicode_ci NULL,
	`message` varchar(255) COLLATE utf8mb4_unicode_ci NULL
  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='banking||workflow-get-instalments';
  
	/*store table entry into db*/
	insert into delete_marketing_tables (`table_name`,`expiry_date`)
	select 'marketing.[env.search.ticket_id /]',DATE_ADD(NOW(), INTERVAL 12 HOUR) from dual;
			 
	insert into marketing.[env.search.ticket_id /](`ID`,`sublan_id`,`lan_id`,`lapp_id`,`nbfc`,`service`)
		[env.search.filter_query.with_query /]
	SELECT sublan_id as ID,sublan_id,data_id as lan_id,lapp_id,nbfc,"common_service.transaction.get_instalment" as service FROM bounded_data;
[/report_data_query]         

Download CSV Columns :

[report_first_row] 
"ID", "sublan_id", "lan_id", "lapp_id",
"installment_method", "nbfc", "lapp_status_label",
"scheme_id", "previous_instalment", "previous_instalment_posted_date",
"total_principal","total_interest","total_instalment",
"next_instalment_amount", "tentative_instalment_amount", "no_of_instalments",
"past_instalments", "left_instalments","next_instalment_date",
"next_instalment_due_date","end_date","status"
[/report_first_row]

[report_fetch_query raw]
SELECT ID,sublan_id,lan_id,lapp_id,installment_method,
nbfc,lapp_status_label,scheme_id,previous_instalment,
previous_instalment_posted_date, total_principal,total_interest,total_instalment,
next_instalment_amount,tentative_instalment_amount,
no_of_instalments,past_instalments,left_instalments, next_instalment_date,
next_instalment_due_date,end_date, status
FROM marketing.[env.search.ticket_id /]
[/report_fetch_query]  


	ID
	sublan_id
	lan_id
	lapp_id
	installment_method
	nbfc
	lapp_status_label
	scheme_id
	previous_instalment
	previous_instalment_posted_date
	total_principal
	total_interest
	total_instalment
	next_instalment_amount
	tentative_instalment_amount
	no_of_instalments
	past_instalments
	left_instalments
	next_instalment_date
	next_instalment_due_date
	end_date
	Status

	Below button appear 
	Step1: Generate Instalment Data Mark Done
	- After this button clicked, first step will completed 
	- Second step will start 

Step 2 :  Build Instalment Data :

Activity Queue will get started on already generated data from step 1

Get sublan health :
[sublan.get_health sublan_id=”<sublan_id>” tentative_adb_instalment=<yes> o.set=@sublan_obj /]

Update Query :

UPDATE 
	
SET 
	`installment_method`="[env.@sublan_obj.common.instalment_method/]",
	`lapp_status_label`="[env.@sublan_obj.lapp.lapp_status_label/]",
	`scheme_id`="[env.@sublan_obj.sublan.scheme_id/]",
	`previous_instalment`="[env.@sublan_obj.history.previous_instalment/]",                       
	`previous_instalment_posted_date`= "[env.s@ublan_obj.history.previous_instalment_posted_date date_format='Ymd'/]",
	`next_instalment_amount`="[env.@sublan_obj.current_meta.next_instalment_amount/]",
	`total_principal`="[env.@sublan_obj.current_meta.total_principal/]",
	`total_interest`="[env.@sublan_obj.current_meta.total_interest/]",
	`total_instalment`="[env.@sublan_obj.current_meta.total_instalment/]",
	`next_instalment_amount`="[env.@sublan_obj.current_meta.next_instalment_amount/]",
	`tentative_instalment_amount`="[env.@sublan_obj.common.tentative_adb_instalment.instalment.instalment_amount/]",
	`no_of_instalments`="[env.@sublan_obj.current_meta.no_of_instalments/]",
	`past_instalments`="[env.@sublan_obj.current_meta.past_instalments/]",
	`left_instalments`="[env.@sublan_obj.current_meta.instalments_left/]",
	`next_instalment_date`="[env.@sublan_obj.current_meta.next_instalment_date date_format='Ymd'/]",
	`next_instalment_due_date`="[env.@sublan_obj.current_meta.next_instalment_due_date date_format='Ymd'/]",
	`end_date`="[env.@sublan_obj.current_meta.end_date date_format='Ymd'/]",
	`message`="[module.sublan_obj.message/]"
WHERE 
	ID="[env.@data.row.ID/]"; 

Columns :

Step 3 : Download Instalment Data :