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(\'2025-09-25\') - 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)) * ((`li`.`od_int` - `li`.`cc_int`) / 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(\'2025-09-25\') - to_days(if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) day),`li`.`fianl_date`))))))) AS `oddays`,`la`.`s_amt` AS `s_amt` from `co-operativebank`.`loan_inf` `li` join `co-operativebank`.`loan_account` `la` where (`la`.`acc_no` = `li`.`acc_no`) group by `li`.`acc_no`
md5=f533d7aff03d36279687b4c3e0609eca
updatable=0
algorithm=0
definer_user=admin
definer_host=localhost
suid=2
with_check_option=0
timestamp=2025-09-24 14:34:57
create-version=1
source=SELECT la.acc_no,la.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,     if(li.ins_type=\'Monthly (12)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) MONTH),   if(li.ins_type=\'Quaterly (4)\',DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) QUARTER),  if(li.ins_type=\'Halfyearly (2)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0)*2 QUARTER)  ,  if(li.ins_type=\'Yearly (1)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) YEAR) , if(li.ins_type=\'Days (365)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) DAY) , li.fianl_date  ))))) AS due_date ,  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) AS C_INST  ,round(if (((loan_amt/loan_inst)*(ins_no)-total_pay)>0,((loan_amt/loan_inst)*(ins_no)-total_pay),0)) as od_amt,clear_amt,round(if (((loan_amt/loan_inst)*(ins_no)-total_pay)>0,((loan_amt/loan_inst)*(ins_no)-total_pay)*(((datediff(\'2025-09-25\',if(if(li.ins_type=\'Monthly (12)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) MONTH),   if(li.ins_type=\'Quaterly (4)\',DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) QUARTER),  if(li.ins_type=\'Halfyearly (2)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0)*2 QUARTER)  ,  if(li.ins_type=\'Yearly (1)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) YEAR) , if(li.ins_type=\'Days (365)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) DAY) , li.fianl_date  )))))>=pay_date,if(li.ins_type=\'Monthly (12)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) MONTH),   if(li.ins_type=\'Quaterly (4)\',DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) QUARTER),  if(li.ins_type=\'Halfyearly (2)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0)*2 QUARTER)  ,  if(li.ins_type=\'Yearly (1)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) YEAR) , if(li.ins_type=\'Days (365)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/(loan_amt/loan_inst)+1,0) DAY) , li.fianl_date  ))))),pay_date ))))/365)* (( li.od_int - cc_int)/100),0)) as od_int_a,round(clear_amt*(days/365)*(cc_int/100)+cc_int_due+od_int_due) as cc_int_a,0 as loss,li.days as days, datediff(\'2025-09-25\',if(li.ins_type=\'Monthly (12)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/round(loan_amt/loan_inst)+1,0) MONTH),   if(li.ins_type=\'Quaterly (4)\',DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/round(loan_amt/loan_inst)+1,0) QUARTER),  if(li.ins_type=\'Halfyearly (2)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/round(loan_amt/loan_inst)+1,0)*2 QUARTER)  ,  if(li.ins_type=\'Yearly (1)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/round(loan_amt/loan_inst)+1,0) YEAR) , if(li.ins_type=\'Days (365)\', DATE_ADD(issue_date, INTERVAL  TRUNCATE(total_pay/round(loan_amt/loan_inst)+1,0) DAY) , li.fianl_date  )))))) AS oddays,s_amt  FROM loan_inf li,loan_account la WHERE la.acc_no=li.acc_no group by li.acc_no
client_cs_name=utf8mb4
connection_cl_name=utf8mb4_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(\'2025-09-25\') - 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)) * ((`li`.`od_int` - `li`.`cc_int`) / 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(\'2025-09-25\') - to_days(if((`li`.`ins_type` = \'Monthly (12)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) month),if((`li`.`ins_type` = \'Quaterly (4)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) quarter),if((`li`.`ins_type` = \'Halfyearly (2)\'),(`li`.`issue_date` + interval (truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) * 2) quarter),if((`li`.`ins_type` = \'Yearly (1)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) year),if((`li`.`ins_type` = \'Days (365)\'),(`li`.`issue_date` + interval truncate(((`li`.`total_pay` / round((`li`.`loan_amt` / `li`.`loan_inst`),0)) + 1),0) day),`li`.`fianl_date`))))))) AS `oddays`,`la`.`s_amt` AS `s_amt` from `co-operativebank`.`loan_inf` `li` join `co-operativebank`.`loan_account` `la` where (`la`.`acc_no` = `li`.`acc_no`) group by `li`.`acc_no`
