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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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=66b576d099b5579445eddb29f9fb2d24
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2018-07-03 04:57:35
create-version=1
source=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 ((`loan_acc` `l` join `voucher` `v`) join `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 ((`loan_acc` `l` join `voucher` `v`) join `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 ((`loan_acc` `l` join `voucher` `v`) join `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 ((`loan_acc` `l` join `voucher` `v`) join `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 ((`loan_acc` `l` join `voucher` `v`) join `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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`.`loan_acc` `l` join `co-operativebank`.`voucher` `v`) join `co-operativebank`.`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`
