TYPE=VIEW
query=select `la`.`acc_no` AS `acc_no`,concat(`la`.`fname`,\' \',`la`.`mname`,\' \',`la`.`lname`) 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`,`li`.`imst_date` AS `due_date`,round(if((`li`.`tdate` <= `li`.`fianl_date`),0,`li`.`clear_amt`),0) AS `od_amt`,`li`.`clear_amt` AS `clear_amt`,round(if((`li`.`tdate` <= `li`.`fianl_date`),0,((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`od_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`)),0) AS `od_int_a`,round(if((`li`.`tdate` <= `li`.`fianl_date`),((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`cc_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`),0),0) AS `cc_int_a`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 90),`li`.`clear_amt`,0) AS `standard`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 90) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 1095)),`li`.`clear_amt`,0) AS `sub_standard`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095),if((`ss`.`bal` > `li`.`clear_amt`),`ss`.`bal`,`ss`.`bal`),`ss`.`bal`) AS `r_value`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 1460)),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d1`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1460) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 2190)),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d2`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 2190),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d3`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095),if((`ss`.`bal` > `li`.`clear_amt`),0,(`li`.`clear_amt` - `ss`.`bal`)),0) AS `unsecure`,0 AS `loss`,`li`.`days` AS `days`,`la`.`loan_against_sv_acc` AS `sbacc` from `co-operativebank-dekhaliannapurna`.`loan_inf` `li` join `co-operativebank-dekhaliannapurna`.`loan_account` `la` join `co-operativebank-dekhaliannapurna`.`shg_loan_s` `ss` where ((`ss`.`loan_acc` = `la`.`acc_no`) and (`ss`.`loan_acc` = `li`.`acc_no`) and (`la`.`acc_no` = `li`.`acc_no`)) group by `li`.`acc_no`
md5=9aef2241a7b2980781d9a896c36b541c
updatable=0
algorithm=0
definer_user=admin
definer_host=%
suid=2
with_check_option=0
timestamp=2022-06-21 08:14:33
create-version=1
source=SELECT la.acc_no,concat(la.fname,\' \',la.mname,\' \',la.lname) as name,la.father_name,la.address,la.mobile,li.issue_date,li.loan_amt,concat(li.ins_type,\' [ \',li.duration,li.duration_type,\' ]\') as ist_type,round(loan_amt/loan_inst) as inst_ant,imst_date as due_date,round(if(tdate<=li.fianl_date,0,clear_amt)) as od_amt,clear_amt,round(if(tdate<=li.fianl_date,0,clear_amt*(days/365)*(li.od_int/100)+cc_int_due+od_int_due)) as od_int_a,round(if(tdate<=li.fianl_date,clear_amt*(days/365)*(cc_int/100)+cc_int_due+od_int_due,0)) as cc_int_a,if(timestampdiff(DAY,imst_date,tdate)<90,clear_amt,0) as standard ,if(timestampdiff(DAY,imst_date,tdate)>=90 and timestampdiff(DAY,imst_date,tdate)<1095,clear_amt,0) as sub_standard,if(timestampdiff(DAY,imst_date,tdate)>=1095,if(bal>clear_amt,bal,bal),bal) as r_value,if(timestampdiff(DAY,imst_date,tdate)>=1095 and timestampdiff(DAY,imst_date,tdate)<1460,if(bal>clear_amt,clear_amt,bal),0) as d1,if(timestampdiff(DAY,imst_date,tdate)>=1460 and timestampdiff(DAY,imst_date,tdate)<2190,if(bal>clear_amt,clear_amt,bal),0) as d2,if(timestampdiff(DAY,imst_date,tdate)>=2190 ,if(bal>clear_amt,clear_amt,bal),0) as d3,if(timestampdiff(DAY,imst_date,tdate)>=1095,if(bal>clear_amt,0,(clear_amt- bal)) ,0) as unsecure,0 as loss,li.days as days,la.loan_against_sv_acc as sbacc  FROM loan_inf li,loan_account la,shg_loan_s ss WHERE ss.loan_acc=la.acc_no and ss.loan_acc=li.acc_no and 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`,concat(`la`.`fname`,\' \',`la`.`mname`,\' \',`la`.`lname`) 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`,`li`.`imst_date` AS `due_date`,round(if((`li`.`tdate` <= `li`.`fianl_date`),0,`li`.`clear_amt`),0) AS `od_amt`,`li`.`clear_amt` AS `clear_amt`,round(if((`li`.`tdate` <= `li`.`fianl_date`),0,((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`od_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`)),0) AS `od_int_a`,round(if((`li`.`tdate` <= `li`.`fianl_date`),((((`li`.`clear_amt` * (`li`.`days` / 365)) * (`li`.`cc_int` / 100)) + `li`.`cc_int_due`) + `li`.`od_int_due`),0),0) AS `cc_int_a`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 90),`li`.`clear_amt`,0) AS `standard`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 90) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 1095)),`li`.`clear_amt`,0) AS `sub_standard`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095),if((`ss`.`bal` > `li`.`clear_amt`),`ss`.`bal`,`ss`.`bal`),`ss`.`bal`) AS `r_value`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 1460)),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d1`,if(((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1460) and (timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) < 2190)),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d2`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 2190),if((`ss`.`bal` > `li`.`clear_amt`),`li`.`clear_amt`,`ss`.`bal`),0) AS `d3`,if((timestampdiff(DAY,`li`.`imst_date`,`li`.`tdate`) >= 1095),if((`ss`.`bal` > `li`.`clear_amt`),0,(`li`.`clear_amt` - `ss`.`bal`)),0) AS `unsecure`,0 AS `loss`,`li`.`days` AS `days`,`la`.`loan_against_sv_acc` AS `sbacc` from `co-operativebank-dekhaliannapurna`.`loan_inf` `li` join `co-operativebank-dekhaliannapurna`.`loan_account` `la` join `co-operativebank-dekhaliannapurna`.`shg_loan_s` `ss` where ((`ss`.`loan_acc` = `la`.`acc_no`) and (`ss`.`loan_acc` = `li`.`acc_no`) and (`la`.`acc_no` = `li`.`acc_no`)) group by `li`.`acc_no`
