TYPE=VIEW
query=select `la`.`acc_no` AS `acc_no`,`la`.`name` AS `name`,`la`.`father_name` AS `father_name`,`la`.`address` AS `address`,`la`.`mobile` AS `mobile`,`li`.`issue_date` AS `issue_date`,`li`.`loan_amt` AS `loan_amt`,concat(`li`.`ins_type`,\' [ \',`li`.`duration`,`li`.`duration_type`,\' ]\') AS `ist_type`,round((`li`.`loan_amt` / `li`.`loan_inst`),0) AS `inst_ant`,if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))) AS `due_date`,truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) AS `C_INST`,round(if(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) > 0),(((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`),0),0) AS `od_amt`,`li`.`clear_amt` AS `clear_amt`,round(if(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) > 0),(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) * ((to_days(\'2020-03-31\') - to_days(if((if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))) >= `li`.`pay_date`),if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))),`li`.`pay_date`))) / 365)) * (2 / 100)),0),0) AS `od_int_a`,round(((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`cc_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`),0) AS `cc_int_a`,0 AS `loss`,`li`.`days` AS `days`,(to_days(\'2020-03-31\') - to_days(if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))))) AS `oddays`,`la`.`s_amt` AS `s_amt` from (`co-operativebank-milani`.`loan_inf` `li` join `co-operativebank-milani`.`loan_account` `la`) where (`la`.`acc_no` = `li`.`acc_no`) group by `li`.`acc_no`
md5=5944fd7e3c465dfb0dfbd566019d9cd5
updatable=0
algorithm=0
definer_user=admin
definer_host=localhost
suid=1
with_check_option=0
timestamp=2021-10-21 04:25:05
create-version=1
source=select  `la`.`acc_no`  AS  `acc_no` ,\n `la`.`name`  AS  `name` ,\n `la`.`father_name`  AS  `father_name` ,\n `la`.`address`  AS  `address` ,\n `la`.`mobile`  AS  `mobile` ,\n `li`.`issue_date`  AS  `issue_date` ,\n `li`.`loan_amt`  AS  `loan_amt` ,\nconcat(  `li`.`ins_type` ,  \' [ \',  `li`.`duration` ,  `li`.`duration_type` ,  \' ]\'  )  AS  `ist_type` ,\nround( (  `li`.`loan_amt`  /  `li`.`loan_inst`  ), 0  )  AS  `inst_ant` ,\nif( (  `li`.`ins_type`  =  \'Monthly (12)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) month  ),\nif( (  `li`.`ins_type`  =  \'Quaterly (4)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )quarter ),\nif( (  `li`.`ins_type`  =  \'Halfyearly (2)\' ), (  `li`.`issue_date`  + interval( truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  *2 )quarter ),\nif( (  `li`.`ins_type`  =  \'Yearly (1)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )year ),\nif( (  `li`.`ins_type`  =  \'Days (365)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) day  ),\n `li`.`fianl_date`  ) ) ) ) ) AS  `due_date` ,\ntruncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1 ),\n0 ) AS  `C_INST` ,\nround( if( ( ( ( (  `li`.`loan_amt`  /  `li`.`loan_inst`  ) *  `li`.`ins_no`  )  -  `li`.`total_pay`  )  >0 ),\n( ( (  `li`.`loan_amt`  /  `li`.`loan_inst`  ) *  `li`.`ins_no`  ) -  `li`.`total_pay`  ),\n0 ),\n0 ) AS  `od_amt` ,\n `li`.`clear_amt`  AS  `clear_amt` ,\nround( if( ( ( ( (  `li`.`loan_amt`  /  `li`.`loan_inst`  ) *  `li`.`ins_no`  )  -  `li`.`total_pay`  )  >0 ),\n( ( ( ( (  `li`.`loan_amt`  /  `li`.`loan_inst`  ) *  `li`.`ins_no`  ) -  `li`.`total_pay`  ) * ( ( to_days(  \'2020-03-31\'  )  - to_days( if( ( if( (  `li`.`ins_type`  =  \'Monthly (12)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  month  ) , if( (  `li`.`ins_type`  =  \'Quaterly (4)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  ) quarter ),\nif( (  `li`.`ins_type`  =  \'Halfyearly (2)\' ), (  `li`.`issue_date`  + interval( truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  *2 )quarter ),\nif( (  `li`.`ins_type`  =  \'Yearly (1)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )year ),\nif( (  `li`.`ins_type`  =  \'Days (365)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) day  ),\n `li`.`fianl_date`  ) ) ) ) ) >=  `li`.`pay_date`  ),\nif( (  `li`.`ins_type`  =  \'Monthly (12)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) month  ),\nif( (  `li`.`ins_type`  =  \'Quaterly (4)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )quarter ),\nif( (  `li`.`ins_type`  =  \'Halfyearly (2)\' ), (  `li`.`issue_date`  + interval( truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  *2 )quarter ),\nif( (  `li`.`ins_type`  =  \'Yearly (1)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )year ),\nif( (  `li`.`ins_type`  =  \'Days (365)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) day  ),\n `li`.`fianl_date`  ) ) ) ) ),\n `li`.`pay_date`  ) ) ) /365 ) ) * ( 2 /100  )  ),\n0 ),\n0 ) AS  `od_int_a` ,\nround( ( ( ( (  `li`.`clear_amt`  * (  `li`.`days`  /365  )  )  * (  `li`.`cc_int`  /100  )  ) +  `li`.`cc_int_due`  ) +  `li`.`od_int_due`  ),\n0 ) AS  `cc_int_a` ,\n0 AS  `loss` ,\n `li`.`days`  AS  `days` ,\n( to_days(  \'2020-03-31\'  )  - to_days( if( (  `li`.`ins_type`  =  \'Monthly (12)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  month  ),\nif( (  `li`.`ins_type`  =  \'Quaterly (4)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )quarter ),\nif( (  `li`.`ins_type`  =  \'Halfyearly (2)\' ), (  `li`.`issue_date`  + interval( truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) , 0  )  *2 )quarter ),\nif( (  `li`.`ins_type`  =  \'Yearly (1)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 )year ),\nif( (  `li`.`ins_type`  =  \'Days (365)\' ), (  `li`.`issue_date`  +  interval truncate( ( (  `li`.`total_pay`  / (  `li`.`loan_amt`  /  `li`.`loan_inst`  )  )  +1  ) ,\n0 ) day  ),\n `li`.`fianl_date`  ) ) ) ) ) ) ) AS  `oddays` ,\n `la`.`s_amt`  AS  `s_amt`  from (  `co-operativebank-milani`.`loan_inf`  `li`  join  `co-operativebank-milani`.`loan_account`  `la`  ) where (  `la`.`acc_no`  =  `li`.`acc_no`  ) group  by  `li`.`acc_no`
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `la`.`acc_no` AS `acc_no`,`la`.`name` AS `name`,`la`.`father_name` AS `father_name`,`la`.`address` AS `address`,`la`.`mobile` AS `mobile`,`li`.`issue_date` AS `issue_date`,`li`.`loan_amt` AS `loan_amt`,concat(`li`.`ins_type`,\' [ \',`li`.`duration`,`li`.`duration_type`,\' ]\') AS `ist_type`,round((`li`.`loan_amt` / `li`.`loan_inst`),0) AS `inst_ant`,if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))) AS `due_date`,truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) AS `C_INST`,round(if(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) > 0),(((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`),0),0) AS `od_amt`,`li`.`clear_amt` AS `clear_amt`,round(if(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) > 0),(((((`li`.`loan_amt` / `li`.`loan_inst`) * `li`.`ins_no`) - `li`.`total_pay`) * ((to_days(\'2020-03-31\') - to_days(if((if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))) >= `li`.`pay_date`),if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))),`li`.`pay_date`))) / 365)) * (2 / 100)),0),0) AS `od_int_a`,round(((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`cc_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`),0) AS `cc_int_a`,0 AS `loss`,`li`.`days` AS `days`,(to_days(\'2020-03-31\') - to_days(if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / (`li`.`loan_amt` / `li`.`loan_inst`)) + 1),0) day),`li`.`fianl_date`))))))) AS `oddays`,`la`.`s_amt` AS `s_amt` from (`co-operativebank-milani`.`loan_inf` `li` join `co-operativebank-milani`.`loan_account` `la`) where (`la`.`acc_no` = `li`.`acc_no`) group by `li`.`acc_no`
