TYPE=VIEW
query=select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'MONTHLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Monthly (12)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'QUARTER\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Quaterly (4)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'YEARLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Yearly (1)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),round((timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`) / 6),0),round((timestampdiff(MONTH,`l`.`date_open`,now()) / 6),0)) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),round((timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`) / 6),0),round((timestampdiff(MONTH,`l`.`date_open`,now()) / 6),0))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'HALFYEARLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Halfyearly (2)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,`l`.`amt` AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`)) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,if((max(`l`.`date_open`) > max(`l`.`fianl_date`)),(sum(`v`.`dr`) - sum(`v`.`cr`)),0) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'ONE-TIME\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'One Time (1)\')) group by `l`.`acc_no`
md5=6777da13c25c536a42889d5df65807f0
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=1
with_check_option=0
timestamp=2021-07-15 17:07:06
create-version=1
source=select  `l`.`acc_no`  AS  `acc_no` ,\n `a`.`name`  AS  `name` ,\n `l`.`lf_no`  AS  `lf_no` ,\nround( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  AS  `inst_amt` ,\nif( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff(  MONTH ,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff(  MONTH ,  `l`.`date_open` , now(  )  )  )  AS  `Pre_inst` ,\nsum(  `v`.`cr`  )  AS  `bal` ,\n( ( round( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  * if( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff(  MONTH ,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff(  MONTH ,  `l`.`date_open` , now(  )  )  )  ) - sum(  `v`.`cr`  )  ) AS  `Due_Amt` ,\n \'MT-LOAN\' AS  `Loan_type` ,\n \'MONTHLY\' AS  `PREMIUM`  from ( (  `co-operativebank-henria`.`loan_acc`  `l`  join  `co-operativebank-henria`.`voucher`  `v`  ) join  `co-operativebank-henria`.`quick_name`  `a`  ) where ( (  `a`.`acc_no`  =  `v`.`Acc_no`  ) and (  `a`.`acc_no`  =  `l`.`acc_no`  ) and (  `v`.`Acc_no`  =  `l`.`acc_no`  ) and (  `v`.`head`  =  `l`.`head`  ) and (  `l`.`head`  =  \'23200\' ) and (  `l`.`ins_type`  =  \'Monthly (12)\' ) ) group  by  `l`.`acc_no`  union  all  select  `l`.`acc_no`  AS  `acc_no` ,\n `a`.`name`  AS  `name` ,\n `l`.`lf_no`  AS  `lf_no` ,\nround( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  AS  `inst_amt` ,\nif( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff( QUARTER,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff( QUARTER,  `l`.`date_open` , now(  )  )  )  AS  `Pre_inst` ,\nsum(  `v`.`cr`  )  AS  `bal` ,\n( ( round( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  * if( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff( QUARTER,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff( QUARTER,  `l`.`date_open` , now(  )  )  )  ) - sum(  `v`.`cr`  )  ) AS  `Due_Amt` ,\n \'MT-LOAN\' AS  `Loan_type` ,\n \'QUARTER\' AS  `PREMIUM`  from ( (  `co-operativebank-henria`.`loan_acc`  `l`  join  `co-operativebank-henria`.`voucher`  `v`  ) join  `co-operativebank-henria`.`quick_name`  `a`  ) where ( (  `a`.`acc_no`  =  `v`.`Acc_no`  ) and (  `a`.`acc_no`  =  `l`.`acc_no`  ) and (  `v`.`Acc_no`  =  `l`.`acc_no`  ) and (  `v`.`head`  =  `l`.`head`  ) and (  `l`.`head`  =  \'23200\' ) and (  `l`.`ins_type`  =  \'Quaterly (4)\' ) ) group  by  `l`.`acc_no`  union  all  select  `l`.`acc_no`  AS  `acc_no` ,\n `a`.`name`  AS  `name` ,\n `l`.`lf_no`  AS  `lf_no` ,\nround( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  AS  `inst_amt` ,\nif( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff( YEAR,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff( YEAR,  `l`.`date_open` , now(  )  )  )  AS  `Pre_inst` ,\nsum(  `v`.`cr`  )  AS  `bal` ,\n( ( round( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  * if( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff( YEAR,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff( YEAR,  `l`.`date_open` , now(  )  )  )  ) - sum(  `v`.`cr`  )  ) AS  `Due_Amt` ,\n \'MT-LOAN\' AS  `Loan_type` ,\n \'YEARLY\' AS  `PREMIUM`  from ( (  `co-operativebank-henria`.`loan_acc`  `l`  join  `co-operativebank-henria`.`voucher`  `v`  ) join  `co-operativebank-henria`.`quick_name`  `a`  ) where ( (  `a`.`acc_no`  =  `v`.`Acc_no`  ) and (  `a`.`acc_no`  =  `l`.`acc_no`  ) and (  `v`.`Acc_no`  =  `l`.`acc_no`  ) and (  `v`.`head`  =  `l`.`head`  ) and (  `l`.`head`  =  \'23200\' ) and (  `l`.`ins_type`  =  \'Yearly (1)\' ) ) group  by  `l`.`acc_no`  union  all  select  `l`.`acc_no`  AS  `acc_no` ,\n `a`.`name`  AS  `name` ,\n `l`.`lf_no`  AS  `lf_no` ,\nround( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  AS  `inst_amt` ,\nif( (  `l`.`date_open`  >  `l`.`fianl_date`  ), round( ( timestampdiff(  MONTH ,  `l`.`date_open` ,  `l`.`fianl_date`  )  /6  ) , 0  ) ,\nround( ( timestampdiff(  MONTH ,  `l`.`date_open` , now(  )  )  /6  ) ,\n0 ) ) AS  `Pre_inst` ,\nsum(  `v`.`cr`  )  AS  `bal` ,\n( ( round( (  `l`.`amt`  /  `l`.`no_inst`  ), 0  )  * if( (  `l`.`date_open`  >  `l`.`fianl_date`  ), round( ( timestampdiff(  MONTH ,  `l`.`date_open` ,  `l`.`fianl_date`  )  /6  ) , 0  ) ,\nround( ( timestampdiff(  MONTH ,  `l`.`date_open` , now(  )  )  /6  ) ,\n0 ) ) ) - sum(  `v`.`cr`  )  ) AS  `Due_Amt` ,\n \'MT-LOAN\' AS  `Loan_type` ,\n \'HALFYEARLY\' AS  `PREMIUM`  from ( (  `co-operativebank-henria`.`loan_acc`  `l`  join  `co-operativebank-henria`.`voucher`  `v`  ) join  `co-operativebank-henria`.`quick_name`  `a`  ) where ( (  `a`.`acc_no`  =  `v`.`Acc_no`  ) and (  `a`.`acc_no`  =  `l`.`acc_no`  ) and (  `v`.`Acc_no`  =  `l`.`acc_no`  ) and (  `v`.`head`  =  `l`.`head`  ) and (  `l`.`head`  =  \'23200\' ) and (  `l`.`ins_type`  =  \'Halfyearly (2)\' ) ) group  by  `l`.`acc_no`  union  all  select  `l`.`acc_no`  AS  `acc_no` ,\n `a`.`name`  AS  `name` ,\n `l`.`lf_no`  AS  `lf_no` ,\n `l`.`amt`  AS  `inst_amt` ,\nif( (  `l`.`date_open`  >  `l`.`fianl_date`  ), timestampdiff( YEAR,  `l`.`date_open` ,  `l`.`fianl_date`  ) , timestampdiff( YEAR,  `l`.`date_open` ,  `l`.`fianl_date`  )  )  AS  `Pre_inst` ,\nsum(  `v`.`cr`  )  AS  `bal` ,\nif( ( max(  `l`.`date_open`  )  > max(  `l`.`fianl_date`  )  ) ,\n( sum(  `v`.`dr`  )  - sum(  `v`.`cr`  )  ),\n0 ) AS  `Due_Amt` ,\n \'MT-LOAN\' AS  `Loan_type` ,\n \'ONE-TIME\' AS  `PREMIUM`  from ( (  `co-operativebank-henria`.`loan_acc`  `l`  join  `co-operativebank-henria`.`voucher`  `v`  ) join  `co-operativebank-henria`.`quick_name`  `a`  ) where ( (  `a`.`acc_no`  =  `v`.`Acc_no`  ) and (  `a`.`acc_no`  =  `l`.`acc_no`  ) and (  `v`.`Acc_no`  =  `l`.`acc_no`  ) and (  `v`.`head`  =  `l`.`head`  ) and (  `l`.`head`  =  \'23200\' ) and (  `l`.`ins_type`  =  \'One Time (1)\' ) ) group  by  `l`.`acc_no`
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(MONTH,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'MONTHLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Monthly (12)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(QUARTER,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'QUARTER\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Quaterly (4)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,now())) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,now()))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'YEARLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Yearly (1)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,round((`l`.`amt` / `l`.`no_inst`),0) AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),round((timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`) / 6),0),round((timestampdiff(MONTH,`l`.`date_open`,now()) / 6),0)) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,((round((`l`.`amt` / `l`.`no_inst`),0) * if((`l`.`date_open` > `l`.`fianl_date`),round((timestampdiff(MONTH,`l`.`date_open`,`l`.`fianl_date`) / 6),0),round((timestampdiff(MONTH,`l`.`date_open`,now()) / 6),0))) - sum(`v`.`cr`)) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'HALFYEARLY\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'Halfyearly (2)\')) group by `l`.`acc_no` union all select `l`.`acc_no` AS `acc_no`,`a`.`name` AS `name`,`l`.`lf_no` AS `lf_no`,`l`.`amt` AS `inst_amt`,if((`l`.`date_open` > `l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`),timestampdiff(YEAR,`l`.`date_open`,`l`.`fianl_date`)) AS `Pre_inst`,sum(`v`.`cr`) AS `bal`,if((max(`l`.`date_open`) > max(`l`.`fianl_date`)),(sum(`v`.`dr`) - sum(`v`.`cr`)),0) AS `Due_Amt`,\'MT-LOAN\' AS `Loan_type`,\'ONE-TIME\' AS `PREMIUM` from ((`co-operativebank-henria`.`loan_acc` `l` join `co-operativebank-henria`.`voucher` `v`) join `co-operativebank-henria`.`quick_name` `a`) where ((`a`.`acc_no` = `v`.`Acc_no`) and (`a`.`acc_no` = `l`.`acc_no`) and (`v`.`Acc_no` = `l`.`acc_no`) and (`v`.`head` = `l`.`head`) and (`l`.`head` = \'23200\') and (`l`.`ins_type` = \'One Time (1)\')) group by `l`.`acc_no`
