File: /home/cafsindia/lifemaze_in/lp_lib/lp_snap.php
<?php
/**********************************************************
Filename: lp_snap.php
Description: Object for all snap reports related operation
Author: uday
Created on: SEP, 26 2018
Approved on:
Reviewed on:
------------------------------------------------------------
Modification Details
Changed by:
------------------------------------------------------------
**********************************************************/
class lp_snap{
private $libObject = "";
private $dbObj = null;
private $cust_id;
function __construct() {
$this->libObject = 0;
$this->cust_id = 0;
}
/**** USED TO SET VALID CUSTOMER LIB OBJECT WITH THIS CLASS ****/
function setLibObject($snap_libObject) {
if(!$snap_libObject)
return false;
$this->libObject = $snap_libObject;
$this->dbObj = $this->libObject->getDBObject();
if(!$this->dbObj){
$this->libObject = 0;
return false;
}
}
/**** USED TO SET CUSTOMER ID WITH THIS CLASS ****/
function set_cust_id($cust_id){
if($cust_id){
$this->cust_id = $cust_id;
}
}
/**** FAMILY INCOME - START ****/
function get_sum_income($income_type=false){
$where = "";
if($income_type){
$where = " and lp_cust_income.income_type = '$income_type'";
}
$income_qry = $this->dbObj->runQuery("SELECT family_id,IFNULL(sum(income_amt),0) as income_amt,family_name,IFNULL(sum(income_yearly),0) as income_yearly FROM lp_cust_income left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_income.income_pay_type left join lp_cust_family on lp_cust_family.family_id = lp_cust_income.income_family where lp_cust_income.status = '1' and lp_cust_income.cust_id = '$this->cust_id' $where");
return $this->dbObj->result($income_qry);
}
function all_income_chart($income_type=false){
$where = "";
if($income_type){
$where = " and lp_cust_income.income_type = '$income_type'";
}
$income_qry = $this->dbObj->runQuery("SELECT family_id,IFNULL(sum(income_amt),0) as income_amt,family_name,IFNULL(sum(income_yearly),0) as income_yearly FROM lp_cust_income left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_income.income_pay_type left join lp_cust_family on lp_cust_family.family_id = lp_cust_income.income_family where lp_cust_income.status = '1' and lp_cust_income.cust_id = '$this->cust_id' $where GROUP by family_name");
return $this->dbObj->result($income_qry);
}
function get_income_family_id($family_id){
$income_qry = $this->dbObj->runQuery("SELECT family_id,IFNULL(income_amt,0) as income_amt,family_name,income_type,IFNULL(sum(income_yearly),0) as income_yearly FROM lp_cust_income left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_income.income_pay_type left join lp_cust_family on lp_cust_family.family_id = lp_cust_income.income_family where lp_cust_income.status = '1' and lp_cust_income.cust_id = '$this->cust_id' and lp_cust_family.family_id = '$family_id' GROUP by income_type");
return $this->dbObj->result($income_qry);
}
/**** FAMILY INCOME - END ****/
/**** EXPENSE - START ****/
function get_sum_expense(){
$expense_qry = $this->dbObj->runQuery("SELECT IFNULL(sum(exp_amt),0) as exp_amt,IFNULL(sum(exp_yearly),0) as exp_yearly,expense_name,expense_sub_name,paying_type_name,expense_related,exp_family FROM lp_cust_expense left join lp_expense on lp_expense.expense_id = lp_cust_expense.exp_cat LEFT join lp_expense_sub on lp_expense_sub.expense_sub_id = lp_cust_expense.exp_sub_cat left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_expense.exp_frq where cust_id = '$this->cust_id' and lp_cust_expense.status= '1'");
return $this->dbObj->result($expense_qry);
}
function get_expense(){
$expense_qry = $this->dbObj->runQuery("SELECT IFNULL(sum(exp_amt),0) as exp_amt,IFNULL(sum(exp_yearly),0) as exp_yearly,expense_name,expense_sub_name,paying_type_name,expense_related,exp_family FROM lp_cust_expense left join lp_expense on lp_expense.expense_id = lp_cust_expense.exp_cat LEFT join lp_expense_sub on lp_expense_sub.expense_sub_id = lp_cust_expense.exp_sub_cat left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_expense.exp_frq where cust_id = '$this->cust_id' and lp_cust_expense.status= '1' GROUP by expense_name");
return $this->dbObj->result($expense_qry);
}
function get_expense_drill($expense_name,$expense_related){
$group = "";
if((int)$expense_related === 1){
$group = "GROUP by exp_sub_cat";
}
if((int)$expense_related === 2){
$group = "GROUP by family_name";
}
$expense_qry = $this->dbObj->runQuery("SELECT IFNULL(exp_amt,0) as exp_amt,IFNULL(sum(exp_yearly),0) as exp_yearly,expense_name,expense_sub_name,paying_type_name,family_name FROM lp_cust_expense left join lp_expense on lp_expense.expense_id = lp_cust_expense.exp_cat LEFT join lp_expense_sub on lp_expense_sub.expense_sub_id = lp_cust_expense.exp_sub_cat left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_expense.exp_frq left join lp_cust_family on lp_cust_family.family_id = lp_cust_expense.exp_family where lp_cust_expense.cust_id = '$this->cust_id' and lp_cust_expense.status= '1' and lp_expense.expense_name = '$expense_name' $group");
return $this->dbObj->result($expense_qry);
}
/**** EXPENSE - END ****/
/**** LOAN - START****/
function get_sum_loan(){
$loan_qry = $this->dbObj->runQuery("SELECT loan_type_name,IFNULL(sum(loan_outstanding),0) as loan_outstanding,IFNULL(sum(emi_yearly),0) as emi_yearly FROM `lp_cust_loan` left join lp_loan_type on lp_loan_type.loan_type_id = lp_cust_loan.loan_type where cust_id = '$this->cust_id' and lp_cust_loan.status= '1'");
return $this->dbObj->result($loan_qry);
}
function get_loan(){
$loan_qry = $this->dbObj->runQuery("SELECT loan_type_name,IFNULL(sum(loan_outstanding),0) as loan_outstanding,IFNULL(sum(emi_yearly),0) as emi_yearly FROM `lp_cust_loan` left join lp_loan_type on lp_loan_type.loan_type_id = lp_cust_loan.loan_type where cust_id = '$this->cust_id' and lp_cust_loan.status= '1' GROUP by loan_type_name");
return $this->dbObj->result($loan_qry);
}
/**** LOAN - END****/
/**** INVESTMENTS - START ****/
function get_bank_sum(){
$rd_bank_qry = $this->dbObj->runQuery("SELECT bank_scheme,IFNULL(sum(bank_amt),0) as bank_amt,IFNULL(sum(bank_maturity_amt),0) as bank_maturity_amt,IFNULL(sum(bank_yearly),0) as bank_yearly FROM lp_cust_bank left join lp_cust_family on lp_cust_family.family_id = lp_cust_bank.bank_family left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_bank.bank_type where lp_cust_bank.status = '1' and lp_cust_bank.cust_id = '$this->cust_id' and bank_scheme = '3'");
$rd_info = $this->dbObj->result($rd_bank_qry);
$rd_bank_yearly = $rd_info[0]->bank_yearly;
$other_bank_qry = $this->dbObj->runQuery("SELECT bank_scheme,IFNULL(sum(bank_amt),0) as bank_amt,IFNULL(sum(bank_maturity_amt),0) as bank_maturity_amt,IFNULL(sum(bank_yearly),0) as bank_yearly FROM lp_cust_bank left join lp_cust_family on lp_cust_family.family_id = lp_cust_bank.bank_family left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_bank.bank_type where lp_cust_bank.status = '1' and lp_cust_bank.cust_id = '$this->cust_id' and bank_scheme != '3' and YEAR(bank_startdate) > (YEAR(NOW())-1)");
$other_info = $this->dbObj->result($other_bank_qry);
$other_bank_yearly = $other_info[0]->bank_yearly;
$bank_sum = $rd_bank_yearly + $other_bank_yearly;
return $bank_sum;
}
function get_bank_list(){
$bank_list = array();
$rd_bank_list_qry = $this->dbObj->runQuery("SELECT bank_scheme,IFNULL(sum(bank_amt),0) as bank_amt,IFNULL(sum(bank_maturity_amt),0) as bank_maturity_amt FROM lp_cust_bank left join lp_cust_family on lp_cust_family.family_id = lp_cust_bank.bank_family left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_bank.bank_type where lp_cust_bank.status = '1' and lp_cust_bank.cust_id = '$this->cust_id' and bank_scheme = '3' GROUP by bank_scheme");
$rd_bank_info = $this->dbObj->result($rd_bank_list_qry);
foreach ($rd_bank_info as $bank) {
$bank_list[] = array("bank_scheme"=>$bank->bank_scheme,"bank_amt"=>(int)$bank->bank_amt,"bank_maturity_amt"=>(int)$bank->bank_maturity_amt);
}
$or_bank_list_qry = $this->dbObj->runQuery("SELECT bank_scheme,IFNULL(sum(bank_amt),0) as bank_amt,IFNULL(sum(bank_maturity_amt),0) as bank_maturity_amt FROM lp_cust_bank left join lp_cust_family on lp_cust_family.family_id = lp_cust_bank.bank_family left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_bank.bank_type where lp_cust_bank.status = '1' and lp_cust_bank.cust_id = '$this->cust_id' and bank_scheme != '3' and YEAR(bank_startdate) > (YEAR(NOW())-1) GROUP by bank_scheme");
$or_bank_info = $this->dbObj->result($or_bank_list_qry);
foreach ($or_bank_info as $bank) {
$bank_list[] = array("bank_scheme"=>$bank->bank_scheme,"bank_amt"=>(int)$bank->bank_amt,"bank_maturity_amt"=>(int)$bank->bank_maturity_amt);
}
return json_encode($bank_list);
}
function get_insurance_sum(){
$insurance_qry = $this->dbObj->runQuery("SELECT IFNULL(sum(ins_premium),0) as ins_premium,IFNULL(sum(premium_yearly),0) as premium_yearly,insurance_type_name FROM lp_cust_insurance left join lp_insurance_type on lp_insurance_type.insurance_type_id = lp_cust_insurance.ins_type left join lp_insurance_company on lp_insurance_company.insurance_company_id = lp_cust_insurance.ins_company left join lp_insurance_plan on lp_insurance_plan.insurance_plan_id = lp_cust_insurance.ins_plan where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id'");
return $this->dbObj->result($insurance_qry);
}
function get_insurance_list(){
$insurance_list_qry = $this->dbObj->runQuery("SELECT IFNULL(sum(ins_premium),0) as ins_premium,IFNULL(sum(premium_yearly),0) as premium_yearly,insurance_type_name FROM lp_cust_insurance left join lp_insurance_type on lp_insurance_type.insurance_type_id = lp_cust_insurance.ins_type left join lp_insurance_company on lp_insurance_company.insurance_company_id = lp_cust_insurance.ins_company left join lp_insurance_plan on lp_insurance_plan.insurance_plan_id = lp_cust_insurance.ins_plan where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' GROUP by insurance_type_name");
return $this->dbObj->result($insurance_list_qry);
}
function get_mf_sum(){
$mf_qry = $this->dbObj->runQuery("SELECT amc_name,IFNULL(sum(mf_present_val),0) as mf_present_val,IFNULL(sum(mf_live_sip),0) as mf_live_sip,IFNULL(sum(mf_yearly),0) as mf_yearly FROM lp_cust_mf left join lp_amc on lp_amc.amc_id = lp_cust_mf.mf_amc left join lp_fund_name on lp_fund_name.fund_name_id = lp_cust_mf.mf_scheme left join lp_fund_class on lp_fund_class.fund_class_id = lp_cust_mf.mf_fund_class left join lp_cust_family on lp_cust_family.family_id = lp_cust_mf.mf_investor where lp_cust_mf.status = '1' and lp_cust_mf.cust_id = '$this->cust_id'");
return $this->dbObj->result($mf_qry);
}
function get_mf_list(){
$mf_list_qry = $this->dbObj->runQuery("SELECT amc_name,IFNULL(sum(mf_present_val),0) as mf_present_val,IFNULL(sum(mf_live_sip),0) as mf_live_sip,IFNULL(sum(mf_yearly),0) as mf_yearly FROM lp_cust_mf left join lp_amc on lp_amc.amc_id = lp_cust_mf.mf_amc left join lp_fund_name on lp_fund_name.fund_name_id = lp_cust_mf.mf_scheme left join lp_fund_class on lp_fund_class.fund_class_id = lp_cust_mf.mf_fund_class left join lp_cust_family on lp_cust_family.family_id = lp_cust_mf.mf_investor where lp_cust_mf.status = '1' and lp_cust_mf.cust_id = '$this->cust_id' GROUP by lp_cust_mf.mf_amc");
return $this->dbObj->result($mf_list_qry);
}
function get_gov_sum(){
$gov_qry = $this->dbObj->runQuery("SELECT gov_scheme_name,IFNULL(sum(gov_amount),0) as gov_amount,IFNULL(sum(gov_yearly),0) as gov_yearly FROM lp_cust_gov left join lp_cust_family on lp_cust_family.family_id = lp_cust_gov.gov_holder left join lp_gov_scheme on lp_gov_scheme.gov_scheme_id = lp_cust_gov.gov_scheme left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_gov.gov_type where lp_cust_gov.status = '1' and lp_cust_gov.cust_id = '$this->cust_id'");
return $this->dbObj->result($gov_qry);
}
function get_gov_list(){
$gov_list_qry = $this->dbObj->runQuery("SELECT gov_scheme_name,IFNULL(sum(gov_amount),0) as gov_amount,IFNULL(sum(gov_yearly),0) as gov_yearly FROM lp_cust_gov left join lp_cust_family on lp_cust_family.family_id = lp_cust_gov.gov_holder left join lp_gov_scheme on lp_gov_scheme.gov_scheme_id = lp_cust_gov.gov_scheme left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_gov.gov_type where lp_cust_gov.status = '1' and lp_cust_gov.cust_id = '$this->cust_id' GROUP by gov_scheme_name");
return $this->dbObj->result($gov_list_qry);
}
function get_stock_sum(){
$stock_qry = $this->dbObj->runQuery("SELECT stock_type,bullion_type,IFNULL(sum(stock_current_value),0) as stock_current_value,IFNULL(sum(stock_inflow_value),0) as stock_inflow_value,IFNULL(sum(stock_yearly),0) as stock_yearly FROM lp_cust_stock left join lp_cust_family on lp_cust_family.family_id = lp_cust_stock.stock_investor where lp_cust_stock.status = '1' and lp_cust_stock.cust_id = '$this->cust_id'");
return $this->dbObj->result($stock_qry);
}
function get_stock_list(){
$stock_list_qry = $this->dbObj->runQuery("SELECT stock_type,bullion_type,IFNULL(sum(stock_current_value),0) as stock_current_value,IFNULL(sum(stock_inflow_value),0) as stock_inflow_value,IFNULL(sum(stock_yearly),0) as stock_yearly FROM lp_cust_stock left join lp_cust_family on lp_cust_family.family_id = lp_cust_stock.stock_investor where lp_cust_stock.status = '1' and lp_cust_stock.cust_id = '$this->cust_id' GROUP by stock_type");
return $this->dbObj->result($stock_list_qry);
}
function get_other_sum(){
$other_qry = $this->dbObj->runQuery("SELECT IFNULL(sum(others_amt),0) as others_amt,IFNULL(sum(other_yearly),0) as other_yearly FROM lp_cust_others left join lp_cust_family on lp_cust_family.family_id = lp_cust_others.others_investor left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_others.others_type where lp_cust_others.status = '1' and lp_cust_others.cust_id = '$this->cust_id'");
return $this->dbObj->result($other_qry);
}
function get_other_list(){
$other_list_qry = $this->dbObj->runQuery("SELECT family_name,IFNULL(sum(others_amt),0) as others_amt,IFNULL(sum(other_yearly),0) as other_yearly FROM lp_cust_others left join lp_cust_family on lp_cust_family.family_id = lp_cust_others.others_investor left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_others.others_type where lp_cust_others.status = '1' and lp_cust_others.cust_id = '$this->cust_id' group by others_investor");
return $this->dbObj->result($other_list_qry);
}
/**** INVESTMENTS - START ****/
function protection_suggested(){
$family_qry = $this->dbObj->runQuery("SELECT * FROM lp_cust_family left join lp_relation on lp_relation.relation_id = lp_cust_family.family_relation where lp_cust_family.cust_id = '$this->cust_id' and lp_cust_family.status = '1' order by lp_cust_family.family_id asc");
$cust_family = $this->dbObj->result($family_qry);
$profolio = "";
$health = "";
$count_query = $this->dbObj->runQuery("SELECT count(*) as family_count FROM `lp_cust_family` where lp_cust_family.cust_id = '$this->cust_id' and lp_cust_family.status = '1' and family_earning in(1,2)");
$count_info = $this->dbObj->result($count_query);
$family_count = $count_info[0]->family_count;
$exp_query = $this->dbObj->runQuery("SELECT IFNULL(sum(exp_yearly),0) as exp_yearly FROM `lp_cust_expense` where lp_cust_expense.status = '1' and cust_id = '$this->cust_id'");
$exp_info = $this->dbObj->result($exp_query);
$exp_yearly = $exp_info[0]->exp_yearly;
$income_query = $this->dbObj->runQuery("SELECT IFNULL(sum(income_yearly),0) as income_yearly FROM `lp_cust_income` where lp_cust_income.status = '1' and cust_id = '$this->cust_id'");
$income_info = $this->dbObj->result($income_query);
$income_yearly = $income_info[0]->income_yearly;
$loan_query = $this->dbObj->runQuery("SELECT IFNULL(sum(loan_outstanding),0) as loan_outstanding FROM `lp_cust_loan` where lp_cust_loan.status = '1' and cust_id = '$this->cust_id'");
$loan_info = $this->dbObj->result($loan_query);
$loan_outstanding = $loan_info[0]->loan_outstanding;
$goal_query = $this->dbObj->runQuery("SELECT IFNULL(sum(goal_amount),0) as goal_amount FROM `lp_cust_goal` left join lp_cust_family on family_id = goal_family where family_relation != '0' and lp_cust_goal.status = '1' and lp_cust_goal.cust_id = '$this->cust_id'");
$goal_info = $this->dbObj->result($goal_query);
$goal_amount = $goal_info[0]->goal_amount;
$income_sug = round($income_yearly *12 / $family_count);
//$income_sug = round($income_yearly *15);
$inflation_amt = $exp_yearly;
$exp_sug = 0;
$inf_10year = 0;
for($i=1; $i<=10; $i++){
$inflation_info = lp_calculation::compound_interest($inflation_amt,7,1);
$inflation_info = json_decode($inflation_info);
$inflation_amt = $inflation_info->future_value;
$exp_sug += $inflation_amt;
}
$inf_10year = round($exp_sug);
$exp_sug = round($exp_sug/10);
$exp_sug = $exp_sug * 100 / 6;
$inf_6per = round($exp_sug);
$exp_sug = round($exp_sug + $loan_outstanding + $goal_amount);
$sug_array = array($exp_sug,$income_sug);
$suggested = max($sug_array);
$sugg_derive = "<tr>
<td>
<table>
<tr>
<td style='border:1px solid #CCCCCC;'>Yearly Income</td>
<td style='border:1px solid #CCCCCC;'>$income_yearly</td>
</tr>
<tr>
<td style='border:1px solid #CCCCCC;'>$income_yearly *10 Years/ Family count ($family_count)</td>
<td style='border:1px solid #CCCCCC;'>$income_sug</td>
</tr>
</table>
</td>
<td> = $income_sug</td>
</tr>
<tr><td colspan='2' class='center-align' style='font-weight:bold;'>OR</td></tr>
<tr>
<td>
<table>
<tr>
<td style='border:1px solid #CCCCCC;'>Yearly Expense</td>
<td style='border:1px solid #CCCCCC;'>$exp_yearly</td>
</tr>
<tr>
<td style='border:1px solid #CCCCCC;'>7% Inflation for 10 years</td>
<td style='border:1px solid #CCCCCC;'>$inf_10year</td>
</tr>
<tr>
<td style='border:1px solid #CCCCCC;'>Final amount = Inflation Amount / 10 years with 6%</td>
<td style='border:1px solid #CCCCCC;'>$inf_6per</td>
</tr>
<tr>
<td style='border:1px solid #CCCCCC;'>Final amount + Loan Out standing ($loan_outstanding) + Goal amount($goal_amount)</td>
<td style='border:1px solid #CCCCCC;'>$exp_sug</td>
</tr>
</table>
</td>
<td> = $exp_sug</td>
</tr>";
$pro_list = array();
$pro_cat = array();
$hel_list = array();
$hel_cat = array();
foreach($cust_family as $family){
$family_id = $family->family_id;
$name = $family->family_name;
$dob = new DateTime($family->family_dob);
$dob_date = $dob->format("d-M-Y");
$age = $family->family_age;
$gender = $family->family_gender;
$earning = $family->family_earning;
$relation = $family->family_relation;
$relation_name = $family->relation_name;
$pro_sum_assured = 0;
$pro_ins_query = $this->dbObj->runQuery("SELECT IFNULL(sum(ins_sum_assured),0) as ins_sum_assured FROM `lp_cust_insurance` where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' and ins_type in(1,2,3) and ins_life_assured = '$family_id ' ");
$pro_ins_info = $this->dbObj->result($pro_ins_query);
$pro_sum_assured = $pro_ins_info[0]->ins_sum_assured;
$hel_sum_assured = 0;
$hel_ins_query = $this->dbObj->runQuery("SELECT IFNULL(sum(ins_sum_assured),0) as ins_sum_assured FROM `lp_cust_insurance` where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' and ins_type in(4) and find_in_set('$family_id',ins_insured)");
$hel_ins_info = $this->dbObj->result($hel_ins_query);
$hel_sum_assured = $hel_ins_info[0]->ins_sum_assured;
$color = "";
$ch_color = "";
$text = "";
if((int)$hel_sum_assured <= 500000){
$ch_color = "red";
$color = "style='color:red;'";
$text = "High";
}else
if(((int)$hel_sum_assured > 500000)&&((int)$hel_sum_assured < 1000000)){
$ch_color = "yellow";
$color = "style='color:yellow;'";
$text = "Medium";
}else
if((int)$hel_sum_assured >= 1000000){
$ch_color = "#43d854";
$color = "style='color:#43d854;'";
$text = "Good";
}
if(($earning === "1") || ($earning === "2")){
$profolio .= "<tr><td>$name</td><td>$pro_sum_assured</td><td>$suggested</td></tr>";
}
$health .= "<tr><td>$name</td><td>$hel_sum_assured</td><td $color > $text </td></tr>";
$hel_list_data = array();
$hel_list_data['name'][] = $name;
$hel_list_data['data'][] = array("y"=>(int)$hel_sum_assured,"color"=>$ch_color);
array_push($hel_list,$hel_list_data);
$ins_query = $this->dbObj->runQuery("SELECT insurance_id,ins_type,ins_sum_assured,ins_start_date,ins_issued_date,ins_term,ins_ppt,IFNULL(ins_premium,0) as ins_premium,IFNULL(premium_yearly,0) as premium_yearly,insurance_type_name,ins_frq,pensioner,vesting_date,pen_frq,pen_amt,pen_ror,vesting_corpus,one3rd_corpus,pen_payout,pen_ropen,pension_amount,family_name FROM lp_cust_insurance left join lp_insurance_type on lp_insurance_type.insurance_type_id = lp_cust_insurance.ins_type left join lp_insurance_company on lp_insurance_company.insurance_company_id = lp_cust_insurance.ins_company left join lp_insurance_plan on lp_insurance_plan.insurance_plan_id = lp_cust_insurance.ins_plan left join lp_cust_family on ins_life_assured = family_id where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' and ins_type in(1,2,3) and ins_life_assured = '$family_id' ORDER by ins_term asc");
$ins_count = $this->dbObj->num_rows($ins_query);
if((int)$ins_count > 0){
$ins_info = $this->dbObj->result($ins_query);
$list_data = array();
$list_data['name'][] = $name;
$list_data['data'][] = (int)$pro_sum_assured;
array_push($pro_cat,date("Y"));
$balance_sum_assured = 0;
foreach($ins_info as $ins){
$ins_type = $ins->ins_type;
$ins_issued_date = $ins->ins_issued_date;
$ins_sum_assured = $ins->ins_sum_assured;
$ins_term = $ins->ins_term;
if((int)$ins_type === 1){
$balance_sum_assured = $ins_sum_assured;
$balance_sum_assured = (int)$balance_sum_assured;
$maturity_dates = "+ 100 years";
$family_dob = new DateTime($family->family_dob);
$maturity_date = $family_dob->modify("$maturity_dates");
$maturity_date = $maturity_date->format("Y");
}else{
$balance_sum_assured += $ins_sum_assured;
$balance_sum_assured = (int)$pro_sum_assured - (int)$balance_sum_assured;
$maturity_dates = "+ $ins_term years";
$ins_issued_date = new DateTime($ins_issued_date);
$maturity_date = $ins_issued_date->modify("$maturity_dates");
$maturity_date = $maturity_date->format("Y");
}
//$balance_sum_assured = (int)$pro_sum_assured - (int)$ins_sum_assured;
$list_data['data'][] = $balance_sum_assured;
array_push($pro_cat,$maturity_date);
}
array_push($pro_list,$list_data);
}
}
$general_info = array('1'=>"Car",'2'=>"Bike",'3'=>"Van",'4'=>"Others");
$gen_ins_query = $this->dbObj->runQuery("SELECT ins_general_type,IFNULL(sum(premium_yearly),0) as premium_yearly FROM `lp_cust_insurance` where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' and ins_type in(5) GROUP by ins_general_type");
$gen_ins_info = $this->dbObj->result($gen_ins_query);
$general = "";
foreach($gen_ins_info as $gen_ins){
$ins_general_type = $gen_ins->ins_general_type;
$premium_yearly = $gen_ins->premium_yearly;
$ins_general_name = $general_info[$ins_general_type];
$general .= "<tr><td>$ins_general_name</td><td>$premium_yearly</td></tr>";
}
return json_encode(array('sts' => TRUE,'profolio' =>"$profolio",'sugg_derive' =>"$sugg_derive",'health' =>"$health",'pro_list' =>$pro_list,'pro_cat' =>$pro_cat,'hel_cat' =>$hel_cat,'hel_list' =>$hel_list));
}
function get_goal_info(){
$goal_query = $this->dbObj->runQuery("SELECT goal_id,lp_cust_goal.cust_id,lp_cust_goal.goal_family,goal_name,goal_date,goal_amount,inflation,inflation_amt,family_name,goal_for,goal_map FROM lp_cust_goal left join lp_cust_family on lp_cust_family.family_id = lp_cust_goal.goal_family where lp_cust_goal.status = '1' and lp_cust_goal.cust_id = '$this->cust_id' order by lp_cust_goal.goal_id");
$goal_info = $this->dbObj->result($goal_query);
$goal_for_array = array("1"=>"Education","2"=>"Marriage","3"=>"Retirement","4"=>"Dream Home","5"=>"Dream Car");
$icon_array = array("1"=>"fa-graduation-cap","2"=>"fa-heartbeat","3"=>"fa-bed","4"=>"fa-home","5"=>"fa-car");
$content = "<h1 class='inner_title'>YOUR RISK PROFILE</h1>";
$mf_amount = 0;
$list = array();
$cat = array();
$list_crrent = array();
$list_crrent['name'][] = "Current Expense";
$list_excepted = array();
$list_excepted['name'][] = "Excepted Amount";
$list_difficite = array();
$list_difficite['name'][] = "Deficit";
$list_inhand = array();
$list_inhand['name'][] = "In Hand";
$tr_line = "";
$tot_deficit = 0;
foreach($goal_info as $goal){
$goal_id = $goal->goal_id;
$goal_name = $goal->goal_name;
$goal_date = new DateTime($goal->goal_date);
$goal_date = $goal_date->format("d M Y");
$goal_amount = $goal->goal_amount;
$inflation_amt = $goal->inflation_amt;
$family_name = $goal->family_name;
$goal_for = $goal->goal_for;
$goal_map = $goal->goal_map;
$goal_for_name = $goal_for_array[$goal_for];
$icon_name = $icon_array[$goal_for];
$now = new DateTime();
$end = new DateTime($goal->goal_date);
$interval = $now->diff($end);
$years = $interval->format('%y years %m months and %d days');
$goal_year = new DateTime($goal->goal_date);
$goal_year = $goal_year->format("Y");
$tot_goal_amount = 0;
if($goal_map){
$goal_map = explode(",",$goal_map);
foreach ($goal_map as $key => $value){
$goal_list = explode("_",$value);
$inv_mode = $goal_list[0];
$inv_rec_id = $goal_list[1];
if($inv_mode === "BK"){
$bank_list_info = $this->dbObj->runQuery("SELECT bank_id,lp_cust_bank.cust_id,bank_family,bank_scheme,bank_descript,bank_amt,bank_type,bank_startdate,bank_tenure,bank_tenure_type,bank_ror,bank_maturity,bank_maturity_amt,paying_type_name,family_name,bank_yearly FROM lp_cust_bank left join lp_cust_family on lp_cust_family.family_id = lp_cust_bank.bank_family left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_bank.bank_type where lp_cust_bank.status = '1' and lp_cust_bank.cust_id = '$this->cust_id' and bank_id = '$inv_rec_id'");
$bank_info = $this->dbObj->result($bank_list_info);
$bank_maturity = $bank_info[0]->bank_maturity;
$bank_maturity_amt = $bank_info[0]->bank_maturity_amt;
$maturity_year = new DateTime($bank_info[0]->bank_maturity);
$maturity_year = $maturity_year->format("Y");
if((int)$goal_year > (int)$maturity_year){
$inflation = $bank_maturity_amt;
for($i=$maturity_year; $i<=$goal_year; $i++){
$inflation_info = lp_calculation::compound_interest($inflation,6,1);
$inflation_info = json_decode($inflation_info);
$inflation = $inflation_info->future_value;
}
$tot_goal_amount += round($inflation);
}else{
$tot_goal_amount += $bank_maturity_amt;
}
}else
if($inv_mode === "INS"){
$ins_list_info = $this->dbObj->runQuery("SELECT insurance_id,lp_cust_insurance.cust_id,ins_type,ins_general_type,ins_proposer,ins_insured,ins_life_assured,ins_company,ins_plan,ins_sum_assured,ins_start_date,ins_issued_date,ins_term,ins_ppt,ins_premium,ins_due_date,insurance_type_name,insurance_company_name,insurance_plan_name,premium_yearly ,pensioner,vesting_date,pen_frq,pen_amt,pen_ror,vesting_corpus,one3rd_corpus,pen_payout,pen_ropen,pension_amount FROM lp_cust_insurance left join lp_insurance_type on lp_insurance_type.insurance_type_id = lp_cust_insurance.ins_type left join lp_insurance_company on lp_insurance_company.insurance_company_id = lp_cust_insurance.ins_company left join lp_insurance_plan on lp_insurance_plan.insurance_plan_id = lp_cust_insurance.ins_plan where lp_cust_insurance.status = '1' and lp_cust_insurance.cust_id = '$this->cust_id' and insurance_id = '$inv_rec_id'");
$ins_info = $this->dbObj->result($ins_list_info);
$ins_issued_date = $ins_info[0]->ins_issued_date;
$ins_term = $ins_info[0]->ins_term;
$ins_ppt = $ins_info[0]->ins_ppt;
$ins_premium = $ins_info[0]->ins_premium;
$premium_yearly = $ins_info[0]->premium_yearly;
$ins_sum_assured = $ins_info[0]->ins_sum_assured;
$maturity_info = lp_calculation::maturity_date($ins_issued_date,$ins_term,3);
$maturity_info = json_decode($maturity_info);
$maturity_date = $maturity_info->maturity_date;
$maturity_year = new DateTime($maturity_date);
$maturity_year = $maturity_year->format("Y");
if((int)$goal_year > (int)$maturity_year){
$inflation = $premium_yearly;
for($i=1; $i<(int)$ins_ppt; $i++){
$interst= $inflation * 0.06;
$inflation=$inflation+$interst+$premium_yearly;
}
for($i=(int)$ins_ppt; $i<=(int)$ins_term; $i++){
$inflation_info = lp_calculation::compound_interest($inflation,6,1);
$inflation_info = json_decode($inflation_info);
$inflation = $inflation_info->future_value;
}
for($i=$maturity_year; $i<$goal_year; $i++){
$inflation_info = lp_calculation::compound_interest($inflation,6,1);
$inflation_info = json_decode($inflation_info);
$inflation = $inflation_info->future_value;
}
$tot_goal_amount += round($inflation);
}else{
//$tot_goal_amount += $ins_sum_assured;
}
}else
if($inv_mode === "MF"){
$mf_list_info = $this->dbObj->runQuery("SELECT mf_id,lp_cust_mf.cust_id,mf_investor,mf_amc,mf_scheme,mf_fund_class,mf_fund_type,mf_inv_type,mf_present_val,mf_live_sip,amc_name,fund_name,fund_class_name,family_name,mf_yearly,mf_end_date FROM lp_cust_mf left join lp_amc on lp_amc.amc_id = lp_cust_mf.mf_amc left join lp_fund_name on lp_fund_name.fund_name_id = lp_cust_mf.mf_scheme left join lp_fund_class on lp_fund_class.fund_class_id = lp_cust_mf.mf_fund_class left join lp_cust_family on lp_cust_family.family_id = lp_cust_mf.mf_investor where lp_cust_mf.status = '1' and lp_cust_mf.cust_id = '$this->cust_id' and mf_id = '$inv_rec_id'");
$mf_info = $this->dbObj->result($mf_list_info);
$mf_present_val = $mf_info[0]->mf_present_val;
$mf_live_sip = $mf_info[0]->mf_live_sip;
$mf_yearly = $mf_info[0]->mf_yearly;
$mf_end_date = $mf_info[0]->mf_end_date;
$amc_name = $mf_info[0]->amc_name;
$maturity_date = new DateTime($mf_info[0]->mf_end_date);
$maturity_date = $maturity_date->format("d M Y");
$maturity_year = new DateTime($mf_info[0]->mf_end_date);
$maturity_year = $maturity_year->format("Y");
$inflation = $mf_yearly;
for($i=date("Y"); $i<$maturity_year; $i++){
$interst = $inflation * 0.12;
$inflation = $inflation+$interst+$mf_yearly;
}
for($i=$maturity_year; $i<=$goal_year; $i++){
$interst = $inflation * 0.12;
$inflation = $inflation+$interst;
}
$mf_amount += $mf_present_val + round($inflation);
$tot_goal_amount += $mf_amount;
}else
if($inv_mode === "GOV"){
$gov_list_info = $this->dbObj->runQuery("SELECT gov_id,lp_cust_gov.cust_id,gov_holder,gov_scheme,gov_type,gov_amount,gov_start_date,gov_maturity_date,gov_ror,gov_maturity_value,family_name,gov_scheme_name,paying_type_name,gov_yearly FROM lp_cust_gov left join lp_cust_family on lp_cust_family.family_id = lp_cust_gov.gov_holder left join lp_gov_scheme on lp_gov_scheme.gov_scheme_id = lp_cust_gov.gov_scheme left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_gov.gov_type where lp_cust_gov.status = '1' and lp_cust_gov.cust_id = '$this->cust_id' and gov_id = '$inv_rec_id'");
$gov_info = $this->dbObj->result($gov_list_info);
$gov_id = $gov_info[0]->gov_id;
$gov_maturity_date = $gov_info[0]->gov_maturity_date;
$gov_maturity_value = $gov_info[0]->gov_maturity_value;
$maturity_year = new DateTime($gov_info[0]->gov_maturity_date);
$maturity_year = $maturity_year->format("Y");
if((int)$goal_year > (int)$maturity_year){
$inflation = $bank_maturity_amt;
for($i=$maturity_year; $i<=$goal_year; $i++){
$inflation_info = lp_calculation::compound_interest($inflation,6,1);
$inflation_info = json_decode($inflation_info);
$inflation = $inflation_info->future_value;
}
$tot_goal_amount += round($inflation);
}else{
$tot_goal_amount += $gov_maturity_value;
}
}else
if($inv_mode === "OTH"){
$others_list_info = $this->dbObj->runQuery("SELECT others_id,lp_cust_others.cust_id,others_investor,others_descript,others_amt,others_type,others_startdate,others_tenure,others_tenure_type,others_expected_val,others_maturity_date,others_maturity_val,family_name,paying_type_name,other_yearly FROM lp_cust_others left join lp_cust_family on lp_cust_family.family_id = lp_cust_others.others_investor left join lp_paying_type on lp_paying_type.paying_type_id = lp_cust_others.others_type where lp_cust_others.status = '1' and lp_cust_others.cust_id = '$this->cust_id' and others_id = '$inv_rec_id'");
$others_info = $this->dbObj->result($others_list_info);
$others_maturity_date = $others_info[0]->others_maturity_date;
$others_maturity_val = $others_info[0]->others_maturity_val;
$maturity_year = new DateTime($others_info[0]->others_maturity_date);
$maturity_year = $maturity_year->format("Y");
if((int)$goal_year > (int)$maturity_year){
$inflation = $others_maturity_val;
for($i=$maturity_year; $i<=$goal_year; $i++){
$inflation_info = lp_calculation::compound_interest($inflation,6,1);
$inflation_info = json_decode($inflation_info);
$inflation = $inflation_info->future_value;
}
$tot_goal_amount += round($inflation);
}else{
$tot_goal_amount += $others_maturity_val;
}
}
}
}
if((int)$goal_for === 3){
$req_corp = ($inflation_amt * 12) / 6 * 100;
$req_amount = $req_corp - $tot_goal_amount;
$percentage = round(($tot_goal_amount/$req_corp)*100,2);
}else{
$req_amount = $inflation_amt - $tot_goal_amount;
$percentage = round(($tot_goal_amount/$inflation_amt)*100,2);
}
$in_hand = 0;
$deficit = 0;
if($percentage >= 100){
$req_amount = abs($req_amount);
$in_hand = $req_amount;
$req_info = "<label>Deficit</label> 0<br/><label>In Hand</label> $req_amount";
$percent_icon = "<div style='color:#43d854;padding-right:6%;'>100% <i class='fa fa-thumbs-o-up fa-2x' aria-hidden='true'></i></div>";
}else{
$deficit = $req_amount;
$req_info = "<label>Deficit</label><br/>$req_amount";
$percent_icon = "<div style='color:#e31e2b;padding-right:6%;'>$percentage% <i class='fa fa-thumbs-o-down fa-2x' aria-hidden='true'></i></div>";
}
$cat_name = $family_name."-".$goal_name;
$list_crrent['data'][] = $goal_amount;
$list_excepted['data'][] = $inflation_amt;
$list_difficite['data'][] = $deficit;
$list_inhand['data'][] = $in_hand;
array_push($cat,$cat_name);
$tr_line .= "<tr><td style='white-space: nowrap;'>$goal_date</td><td>$family_name - $goal_name</td><td>$goal_amount</td><td>$inflation_amt</td><td>$in_hand </td><td>$deficit</td></tr>";
$tot_deficit += $deficit;
}
$tr_line .= "<tr style='background-color: #CC3366; font-weight: bold; color: #FFFFFF;'><td colspan='5'>Total Deficit</td><td>$tot_deficit</td></tr>";
array_push($list,$list_crrent);
array_push($list,$list_excepted);
array_push($list,$list_difficite);
array_push($list,$list_inhand);
$table_info = "<table class='striped highlight centered' style='box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2); border-radius: 4px;'>
<thead>
<tr style='background-color:#ed780e;font-weight:bold;color:#FFFFFF;'>
<th>Date</th>
<th>Name</th>
<th>Expense</th>
<th>Excepted</th>
<th>In Hand</th>
<th>Deficit</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return json_encode(array('series' => $list, 'categories' => $cat, 'table_info' => $table_info),JSON_NUMERIC_CHECK);
}
}
?>