TYPE=VIEW
query=select `la`.`acc_no` AS `acc_no`,`la`.`lf_no` AS `lf_no`,`la`.`date_open` AS `date_open`,if((`la`.`name_p` <> \'\'),concat(`j_a`.`name`,\' [ \',`la`.`name_p`,\' ]\'),concat(`j_a`.`fname`,\' \',`j_a`.`mname`,\' \',`j_a`.`lname`)) AS `name`,`j_a`.`father_name` AS `father_name`,`j_a`.`address` AS `address`,`la`.`amt` AS `amt`,sum(`ld`.`Total_amt`) AS `received_amt`,sum(`ld`.`intrst_Amt`) AS `int_received`,(`la`.`amt` - sum(`ld`.`Total_amt`)) AS `final_amt`,round(if(((`la`.`sub_head` = \'23109\') or (`la`.`sub_head` = \'23115\')),(((`la`.`amt` - sum(`ld`.`Total_amt`)) * pow((1 + (`la`.`interest` / 400)),(4 * ((to_days(\'2025-03-31\') - to_days(max(`ld`.`Date`))) / 365)))) - (`la`.`amt` - sum(`ld`.`Total_amt`))),if(((`la`.`sub_head` = \'23101A\') or (`la`.`sub_head` = \'23101B\') or (`la`.`sub_head` = \'23111\') or (`la`.`sub_head` = \'23113\') or (`la`.`sub_head` = \'23107\') or (`la`.`sub_head` = \'23103\') or (`la`.`sub_head` = \'23105\')),`loan_interest`((`la`.`amt` - sum(`ld`.`Total_amt`)),`la`.`interest`,`la`.`od_int`,max(`ld`.`Date`),`la`.`fianl_date`,\'2025-03-31\'),(((`la`.`amt` - sum(`ld`.`Total_amt`)) * (`la`.`interest` / 100)) * ((to_days(\'2025-03-31\') - to_days(max(`ld`.`Date`))) / 365)))),2) AS `interest_receivable`,timestampdiff(DAY,max(`ld`.`Date`),\'2025-03-31\') AS `days`,if((max(`la`.`fianl_date`) >= \'2025-03-31\'),\'CC\',\'OD\') AS `Status`,max(`ld`.`Date`) AS `paid_date`,`j_a`.`mobile` AS `mobile` from ((`co-operativebank-210`.`loan_acc` `la` join `co-operativebank-210`.`loan_deposit` `ld`) join `co-operativebank-210`.`j_a`) where ((`j_a`.`Acc_No` = `la`.`loan_against_sv_acc`) and (`la`.`id` = `ld`.`loan_id`) and (`ld`.`Date` <= \'2025-03-31\') and (`la`.`sub_head` = \'23101B\')) group by `la`.`id`
md5=80153df523b238afc7d4734342aaf305
updatable=0
algorithm=0
definer_user=admin
definer_host=localhost
suid=2
with_check_option=0
timestamp=2025-02-04 04:48:47
create-version=1
source=select la.acc_no AS acc_no,la.lf_no as lf_no,la.date_open AS date_open,if((la.name_p <> \'\'),concat(j_a.name,\' [ \',la.name_p,\' ]\'),concat(fname,\' \',mname,\' \',lname)) AS name,j_a.father_name AS father_name,j_a.address AS address,la.amt AS amt,sum(ld.Total_amt) AS received_amt,sum(ld.intrst_Amt) AS int_received,(la.amt - sum(ld.Total_amt)) AS final_amt,round(if(((la.sub_head = \'23109\') or (la.sub_head = \'23115\')),(((la.amt - sum(ld.Total_amt)) * pow((1 + (la.interest / 400)),(4 * ((to_days(\'2025-03-31\') - to_days(max(ld.Date))) / 365)))) - (la.amt - sum(ld.Total_amt))),if(((la.sub_head = \'23101A\') or (la.sub_head = \'23101B\') or (la.sub_head = \'23111\') or (la.sub_head = \'23113\') or (la.sub_head = \'23107\') or (la.sub_head = \'23103\') or (la.sub_head = \'23105\')),loan_interest((la.amt - sum(ld.Total_amt)),la.interest,la.od_int,max(ld.Date),la.fianl_date,\'2025-03-31\'),(((la.amt - sum(ld.Total_amt)) * (la.interest / 100)) * ((to_days(\'2025-03-31\') - to_days(max(ld.Date))) / 365)))),2) AS interest_receivable,timestampdiff(DAY,max(ld.Date),\'2025-03-31\') AS days,if((max(la.fianl_date) >= \'2025-03-31\'),\'CC\',\'OD\') AS Status,max(ld.Date) AS paid_date,mobile from ((loan_acc la join loan_deposit ld) join j_a) where ( (j_a.Acc_No = la.loan_against_sv_acc)  and (la.id = ld.loan_id) and (ld.Date <= \'2025-03-31\')  and (la.sub_head = \'23101B\')) group by la.id
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `la`.`acc_no` AS `acc_no`,`la`.`lf_no` AS `lf_no`,`la`.`date_open` AS `date_open`,if((`la`.`name_p` <> \'\'),concat(`j_a`.`name`,\' [ \',`la`.`name_p`,\' ]\'),concat(`j_a`.`fname`,\' \',`j_a`.`mname`,\' \',`j_a`.`lname`)) AS `name`,`j_a`.`father_name` AS `father_name`,`j_a`.`address` AS `address`,`la`.`amt` AS `amt`,sum(`ld`.`Total_amt`) AS `received_amt`,sum(`ld`.`intrst_Amt`) AS `int_received`,(`la`.`amt` - sum(`ld`.`Total_amt`)) AS `final_amt`,round(if(((`la`.`sub_head` = \'23109\') or (`la`.`sub_head` = \'23115\')),(((`la`.`amt` - sum(`ld`.`Total_amt`)) * pow((1 + (`la`.`interest` / 400)),(4 * ((to_days(\'2025-03-31\') - to_days(max(`ld`.`Date`))) / 365)))) - (`la`.`amt` - sum(`ld`.`Total_amt`))),if(((`la`.`sub_head` = \'23101A\') or (`la`.`sub_head` = \'23101B\') or (`la`.`sub_head` = \'23111\') or (`la`.`sub_head` = \'23113\') or (`la`.`sub_head` = \'23107\') or (`la`.`sub_head` = \'23103\') or (`la`.`sub_head` = \'23105\')),`loan_interest`((`la`.`amt` - sum(`ld`.`Total_amt`)),`la`.`interest`,`la`.`od_int`,max(`ld`.`Date`),`la`.`fianl_date`,\'2025-03-31\'),(((`la`.`amt` - sum(`ld`.`Total_amt`)) * (`la`.`interest` / 100)) * ((to_days(\'2025-03-31\') - to_days(max(`ld`.`Date`))) / 365)))),2) AS `interest_receivable`,timestampdiff(DAY,max(`ld`.`Date`),\'2025-03-31\') AS `days`,if((max(`la`.`fianl_date`) >= \'2025-03-31\'),\'CC\',\'OD\') AS `Status`,max(`ld`.`Date`) AS `paid_date`,`j_a`.`mobile` AS `mobile` from ((`co-operativebank-210`.`loan_acc` `la` join `co-operativebank-210`.`loan_deposit` `ld`) join `co-operativebank-210`.`j_a`) where ((`j_a`.`Acc_No` = `la`.`loan_against_sv_acc`) and (`la`.`id` = `ld`.`loan_id`) and (`ld`.`Date` <= \'2025-03-31\') and (`la`.`sub_head` = \'23101B\')) group by `la`.`id`
