File: //home/cafsindia/login_cafsindia_com/app/api_controller_bk14DEC2021.php
<?php
$frm = "";
/* Module Initilization */
$json = file_get_contents('php://input');
$json = json_decode($json);
if($json){
require("./api_model.php");
$api_model = new api_model;
}
$frm = $json->frm;
$mysql_health_rslt = "";
$mysql_portfolio_rslt = "";
$mysql_portfolio_insert_rslt = "";
if($frm === "check_exist"){
$parameter = $json->parameter;
$login_code = $json->login_code;
$mobile_no = $json->contact_no;
$dial_for = $json->dial_for;
if((int)$parameter === 1 || (int)$parameter === 2){
//mobile is not available create selfdata.
$mysql_rslt = $api_model->get_login_code_number("SELECT count(*) as count FROM (SELECT contact_no FROM cw_portfolio WHERE contact_no = '".$mobile_no."' and trans_status = 1 UNION SELECT contact_number FROM cw_health WHERE contact_number = '".$mobile_no."' and trans_status = 1 UNION SELECT contact_no FROM cw_dump_data WHERE contact_no = '".$mobile_no."' and trans_status = 1) a");
$mysql_rslt_count = $mysql_rslt[0]->count;
if((int)$mysql_rslt_count === 0){
echo json_encode(array('sts' => TRUE, "msg" => "New Mobile Number. You can create as Self or Reference","rslt"=>"","error"=>""));
}else{
$mysql_rslt = $api_model->get_login_code_number("SELECT count(*) as count FROM (SELECT login_code,contact_no FROM cw_portfolio WHERE contact_no = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1 UNION SELECT login_code,contact_number FROM cw_health WHERE contact_number = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1 UNION SELECT login_code,contact_no FROM cw_dump_data WHERE contact_no = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1) a order by login_code");
$mysql_rslt_count = $mysql_rslt[0]->count;
if((int)$mysql_rslt_count === 0){
echo json_encode(array('sts' => FALSE, "msg" => "Already Exist with Someone, So You cannot Add this Lead..","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Already Exist. You can create as Upsell","rslt"=>"","error"=>""));
}
}
}else
if((int)$parameter === 3){
//logincode and mobile is me. create upsell allow.
$mysql_rslt = $api_model->get_login_code_number("SELECT count(*) as count FROM (SELECT login_code,contact_no FROM cw_portfolio WHERE contact_no = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1 UNION SELECT login_code,contact_number FROM cw_health WHERE contact_number = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1 UNION SELECT login_code,contact_no FROM cw_dump_data WHERE contact_no = '".$mobile_no."' and login_code = '".$login_code."' and trans_status = 1) a order by login_code");
$mysql_rslt_count = $mysql_rslt[0]->count;
if((int)$mysql_rslt_count === 0){
$mysql_rslt = $api_model->get_login_code_number("SELECT count(*) as count FROM (SELECT contact_no FROM cw_portfolio WHERE contact_no = '".$mobile_no."' and trans_status = 1 UNION SELECT contact_number FROM cw_health WHERE contact_number = '".$mobile_no."' and trans_status = 1 UNION SELECT contact_no FROM cw_dump_data WHERE contact_no = '".$mobile_no."' and trans_status = 1) a");
$mysql_rslt_count = $mysql_rslt[0]->count;
if((int)$mysql_rslt_count === 0){
echo json_encode(array('sts' => FALSE, "msg" => "New Mobile Number. You can create as Self or Reference","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Already Exist with Someone, So You cannot Add this Lead..","rslt"=>"","error"=>""));
}
}else{
echo json_encode(array('sts' => TRUE, "msg" => "You can Proceed..","rslt"=>"","error"=>""));
}
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Bad Request","rslt"=>"","error"=>""));
}
}else
if($frm === "insert_data"){
$calling_id = $json->calling_id;
$contact_no = $json->contact_no;
$dial_for = $json->dial_for;
$client_name = $json->client_name;
$login_code = $json->login_code;
$entry_date = date("Y-m-d",strtotime($json->entry_date));
$mysql_select_rslt = "";
if($dial_for === "LIFE") {
$mysql_select_rslt = $api_model->get_select_qry("Select count(*) as count from cw_portfolio where calling_id = '".$calling_id."' and trans_status = 1");
if($mysql_select_rslt[0]->count){
$mysql_portfolio_upd_rslt = $api_model->get_upd_qry("UPDATE cw_portfolio SET calling_id ='".$calling_id."', contact_no ='".$contact_no."', client_name ='".$client_name."', login_id ='".$login_code."', entry_date ='".$entry_date."' WHERE calling_id ='".$calling_id."'");
if($mysql_portfolio_upd_rslt) {
echo json_encode(array('sts' => TRUE, "msg" => "Record Successfully Updated","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Records Update Error","rslt"=>"","error"=>""));
}
}else {
$mysql_portfolio_insert_rslt = $api_model->get_insert_qry("INSERT INTO cw_portfolio (`calling_id`, `contact_no`, `client_name`, `login_id`, `entry_date`) VALUES ('".$calling_id."','".$contact_no."', '".$client_name."','".$login_code."', '".$entry_date."')");
if($mysql_portfolio_insert_rslt) {
echo json_encode(array('sts' => TRUE, "msg" => "Record Successfully Inserted","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Records Insert Error","rslt"=>"","error"=>""));
}
}
}else {
$mysql_select_rslt = $api_model->get_select_qry("Select count(*) as count from cw_health where calling_id = '".$calling_id."' and trans_status = 1");
if($mysql_select_rslt[0]->count) {
$mysql_health_upd_rslt = $api_model->get_upd_qry("UPDATE cw_health SET calling_id ='".$calling_id."', contact_number ='".$contact_no."', proposer_name ='".$client_name."', login_code ='".$login_code."', entry_date ='".$entry_date."' WHERE calling_id ='".$calling_id."'");
if($mysql_health_upd_rslt) {
echo json_encode(array('sts' => TRUE, "msg" => "Record Successfully Updated","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Records Update Error","rslt"=>"","error"=>""));
}
}else {
$mysql_health_insert_rslt = $api_model->get_insert_qry("INSERT INTO cw_health (`calling_id`, `contact_number`, `proposer_name`, `login_code`, `entry_date`) VALUES ('".$calling_id."','".$contact_no."', '".$client_name."','".$login_code."', '".$entry_date."')");
if($mysql_health_insert_rslt) {
echo json_encode(array('sts' => TRUE, "msg" => "Record Successfully Inserted","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Records Insert Error","rslt"=>"","error"=>""));
}
}
}
}else
if($frm === "get_business"){
$login_qry = "";
$login_code = implode("','",$json->userId);
$pro_mode = $json->pro_mode;
if((int)$pro_mode === 2){
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
}else
if((int)$pro_mode === 3){
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
}else{
$from_date = date('Y-m-01', strtotime($json->from_date));
$to_date = date('Y-m-t', strtotime($json->to_date));
}
$life_rslt = $api_model->get_life_business("SELECT login_date,login_code,IFNULL(SUM(cw_portfolio.total_net_premium),0) as life_business, SUM(CASE WHEN cw_portfolio.login_status = 1 THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 THEN cw_portfolio.total_net_premium ELSE 0 END) login from cw_portfolio where cw_portfolio.login_date >= '".$from_date."' and cw_portfolio.login_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and login_code in ('".$login_code."') and cw_portfolio.login_status != 6 GROUP BY cw_portfolio.login_code");
$life_rslt = json_decode(json_encode($life_rslt),true);
$health_rslt = $api_model->get_health_business("SELECT login_date,login_code,IFNULL(SUM(cw_health.net_premium),0) as health_business from cw_health where cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and login_code in ('".$login_code."') and cw_health.login_status != 6 GROUP BY cw_health.login_code");
$health_rslt = json_decode(json_encode($health_rslt),true);
if((int)$pro_mode === 3){
$life_array = array_reduce($life_rslt, function ($result, $arr) {
$result[$arr['login_date']][$arr['login_code']] = $arr;
return $result;
}, array());
$health_array = array_reduce($health_rslt, function ($result, $arr) {
$result[$arr['login_date']][$arr['login_code']] = $arr;
return $result;
}, array());
$replace_array = array_replace_recursive($life_array,$health_array);
$final_array = array();
foreach ($replace_array as $key => $values){
$login_date = $values->login_date;
foreach ($values as $code_key => $value) {
$final_array[$key][$code_key]['buiz']['life'] = $value['life_business'];
$final_array[$key][$code_key]['buiz']['health'] = $value['health_business'];
$final_array[$key][$code_key]['life']['login'] = $value['login'];
$final_array[$key][$code_key]['life']['pdc'] = $value['pdc'];
}
}
}else{
$life_array = array_reduce($life_rslt, function ($result, $arr){
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$health_array = array_reduce($health_rslt, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$replace_array = array_replace_recursive($life_array,$health_array);
$final_array = array();
foreach ($replace_array as $key => $value){
$final_array[$key]['buiz']['life'] = $value['life_business'];
$final_array[$key]['buiz']['health'] = $value['health_business'];
$final_array[$key]['life']['login'] = $value['login'];
$final_array[$key]['life']['pdc'] = $value['pdc'];
}
}
if($final_array){
echo json_encode(array('sts' => TRUE, "rslt"=>$final_array,"error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else
if($frm === "get_pickup_business"){
$login_qry = "";
$login_code = implode("','",$json->userId);
$pro_mode = $json->pro_mode;
if((int)$pro_mode === 2){
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
}else
if((int)$pro_mode === 3){
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
}else{
$from_date = date('Y-m-01', strtotime($json->from_date));
$to_date = date('Y-m-t', strtotime($json->to_date));
}
$life_rslt = $api_model->get_life_business("SELECT pickup_date,login_code,IFNULL(SUM(cw_portfolio.total_net_premium),0) as life_business, SUM(CASE WHEN cw_portfolio.login_status = 1 THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 THEN cw_portfolio.total_net_premium ELSE 0 END) login from cw_portfolio where cw_portfolio.pickup_date >= '".$from_date."' and cw_portfolio.pickup_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and login_code in ('".$login_code."') and cw_portfolio.login_status != 6 GROUP BY cw_portfolio.login_code");
$life_rslt = json_decode(json_encode($life_rslt),true);
$health_rslt = $api_model->get_health_business("SELECT login_date,login_code,IFNULL(SUM(cw_health.net_premium),0) as health_business from cw_health where cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and login_code in ('".$login_code."') and cw_health.login_status != 6 GROUP BY cw_health.login_code");
$health_rslt = json_decode(json_encode($health_rslt),true);
if((int)$pro_mode === 3){
$life_array = array_reduce($life_rslt, function ($result, $arr) {
$result[$arr['pickup_date']][$arr['login_code']] = $arr;
return $result;
}, array());
$health_array = array_reduce($health_rslt, function ($result, $arr) {
$result[$arr['login_date']][$arr['login_code']] = $arr;
return $result;
}, array());
$replace_array = array_replace_recursive($life_array,$health_array);
$final_array = array();
foreach ($replace_array as $key => $values){
//$pickup_date = $values->pickup_date;
foreach ($values as $code_key => $value) {
$final_array[$key][$code_key]['buiz']['life'] = $value['life_business'];
$final_array[$key][$code_key]['buiz']['health'] = $value['health_business'];
$final_array[$key][$code_key]['life']['login'] = $value['login'];
$final_array[$key][$code_key]['life']['pdc'] = $value['pdc'];
}
}
}else{
$life_array = array_reduce($life_rslt, function ($result, $arr){
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$health_array = array_reduce($health_rslt, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$replace_array = array_replace_recursive($life_array,$health_array);
$final_array = array();
foreach ($replace_array as $key => $value){
$final_array[$key]['buiz']['life'] = $value['life_business'];
$final_array[$key]['buiz']['health'] = $value['health_business'];
$final_array[$key]['life']['login'] = $value['login'];
$final_array[$key]['life']['pdc'] = $value['pdc'];
}
}
if($final_array){
echo json_encode(array('sts' => TRUE, "rslt"=>$final_array,"error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else
if($frm === "get_team_business"){
$login_qry = "";
$pro_mode = $json->pro_mode;
$details = $json->details;
$arr = array();
foreach ($details as $key => $value){
$login_code = $value->userId;
$level_1 = $value->level_1;
$level_2 = $value->level_2;
if((int)$pro_mode === 2){
$from_date = date('Y-m-d', strtotime($value->from_date));
$to_date = date('Y-m-d', strtotime($value->to_date));
}else{
$from_date = date('Y-m-01', strtotime($value->from_date));
$to_date = date('Y-m-t', strtotime($value->to_date));
}
$life_rslt = $api_model->get_life_business("SELECT login_date,cw_portfolio.login_code,IFNULL(SUM(cw_portfolio.total_net_premium),0) as life_business, SUM(CASE WHEN cw_portfolio.login_status = 1 THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 THEN cw_portfolio.total_net_premium ELSE 0 END) login from cw_portfolio inner join cw_employees on cw_employees.employee_code = cw_portfolio.actual_level_1 inner join cw_employees level2 on cw_portfolio.actual_level_2 = level2.employee_code where cw_portfolio.login_date >= '".$from_date."' and cw_portfolio.login_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and cw_portfolio.login_code in ('".$login_code."') and cw_employees.login_code in ('".$level_1."') and level2.login_code in ('".$level_2."') and cw_portfolio.login_status != 6");
$life_rslt = json_decode(json_encode($life_rslt),true);
$life_business = $life_rslt[0]['life_business'];
$login = $life_rslt[0]['login'];
$pdc = $life_rslt[0]['pdc'];
//print_r($life_rslt); die;
$health_rslt = $api_model->get_health_business("SELECT login_date,cw_health.login_code,IFNULL(SUM(cw_health.net_premium),0) as health_business from cw_health inner join cw_employees on cw_employees.employee_code = cw_health.actual_level_1 inner join cw_employees level2 on cw_health.actual_level_2 = level2.employee_code where cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and cw_health.login_code in ('".$login_code."') and cw_employees.login_code in ('".$level_1."') and level2.login_code in ('".$level_2."') and cw_health.login_status != 6 GROUP BY cw_health.login_code");
$health_rslt = json_decode(json_encode($health_rslt),true);
$health = $health_rslt[0]['health_business'];
if(!$life_business){ $life_business = '0.00'; }
if(!$login){ $login = '0.00'; }
if(!$pdc){ $pdc = '0.00'; }
if(!$health){ $health = '0.00'; }
$arr[] = array("user_id"=>$login_code,"level_1"=>$level_1,"level_2"=>$level_2,"from_date"=>$from_date,"to_date"=>$to_date,"life"=>$life_business,"health"=>$health,"login"=>$login,"pdc"=>$pdc);
}
if($arr){
echo json_encode(array('sts' => TRUE, "rslt"=>$arr,"error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else
if($frm === "get_team_pickup_business"){
$login_qry = "";
$pro_mode = $json->pro_mode;
$details = $json->details;
$arr = array();
foreach ($details as $key => $value){
$login_code = $value->userId;
$level_1 = $value->level_1;
$level_2 = $value->level_2;
if((int)$pro_mode === 2){
$from_date = date('Y-m-d', strtotime($value->from_date));
$to_date = date('Y-m-d', strtotime($value->to_date));
}else{
$from_date = date('Y-m-01', strtotime($value->from_date));
$to_date = date('Y-m-t', strtotime($value->to_date));
}
$life_rslt = $api_model->get_life_business("SELECT pickup_date,cw_portfolio.login_code,IFNULL(SUM(cw_portfolio.total_net_premium),0) as life_business, SUM(CASE WHEN cw_portfolio.login_status = 1 THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 THEN cw_portfolio.total_net_premium ELSE 0 END) login from cw_portfolio inner join cw_employees on cw_employees.employee_code = cw_portfolio.actual_level_1 inner join cw_employees level2 on cw_portfolio.actual_level_2 = level2.employee_code where cw_portfolio.pickup_date >= '".$from_date."' and cw_portfolio.pickup_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and cw_portfolio.login_code in ('".$login_code."') and cw_employees.login_code in ('".$level_1."') and level2.login_code in ('".$level_2."') and cw_portfolio.login_status != 6");
$life_rslt = json_decode(json_encode($life_rslt),true);
$life_business = $life_rslt[0]['life_business'];
$login = $life_rslt[0]['login'];
$pdc = $life_rslt[0]['pdc'];
//print_r($life_rslt); die;
$health_rslt = $api_model->get_health_business("SELECT login_date,cw_health.login_code,IFNULL(SUM(cw_health.net_premium),0) as health_business from cw_health inner join cw_employees on cw_employees.employee_code = cw_health.actual_level_1 inner join cw_employees level2 on cw_health.actual_level_2 = level2.employee_code where cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and cw_health.login_code in ('".$login_code."') and cw_employees.login_code in ('".$level_1."') and level2.login_code in ('".$level_2."') and cw_health.login_status != 6 GROUP BY cw_health.login_code");
$health_rslt = json_decode(json_encode($health_rslt),true);
$health = $health_rslt[0]['health_business'];
if(!$life_business){ $life_business = '0.00'; }
if(!$login){ $login = '0.00'; }
if(!$pdc){ $pdc = '0.00'; }
if(!$health){ $health = '0.00'; }
$arr[] = array("user_id"=>$login_code,"level_1"=>$level_1,"level_2"=>$level_2,"from_date"=>$from_date,"to_date"=>$to_date,"life"=>$life_business,"health"=>$health,"login"=>$login,"pdc"=>$pdc);
}
if($arr){
echo json_encode(array('sts' => TRUE, "rslt"=>$arr,"error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else
if($frm === "get_business_sts"){
$login_qry = "";
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
$details = $json->details;
$arr = array();
$arr1 = array();
$header_arr = array();
$total_arr = array();
$tot_life_login = 0;
$tot_life_issued = 0;
$tot_life_pending = 0;
$tot_life_source_amt = 0;
$tot_life_nonsource_amt = 0;
$tot_health_issued = 0;
$tot_health_pending = 0;
$tot_health_source_amt = 0;
$tot_health_nonsource_amt = 0;
foreach ($details as $key => $value){
$login_code = $value->userId;
$level_1 = $value->level_1;
$level_2 = $value->level_2;
$life_rslt = $api_model->get_life_business_sts("SELECT cw_employees.employee_name,level1.login_code as level1_code,level1.employee_name as level1_name,level2.login_code as level2_code,level2.employee_name as level2_name,IFNULL(SUM(CASE WHEN cw_portfolio.login_status in ('1','5') THEN 1 ELSE 0 END),0) nop, IFNULL(SUM(CASE WHEN cw_portfolio.issuance_status = 2 THEN 1 ELSE 0 END),0) issued, IFNULL(SUM(CASE WHEN cw_portfolio.issuance_status not in ('2','8') and cw_portfolio.login_status = 5 THEN 1 ELSE 0 END),0) pending, IFNULL(SUM(CASE WHEN cw_portfolio.login_status = 1 THEN ROUND(cw_portfolio.total_net_premium,2) ELSE 0 END),0) pdc_amt, IFNULL(SUM(CASE WHEN cw_portfolio.login_status = 5 THEN ROUND(cw_portfolio.total_net_premium,2) ELSE 0 END),0) login_amt from cw_portfolio inner join cw_employees on cw_employees.login_code = cw_portfolio.login_code inner join cw_employees level1 on level1.employee_code = cw_portfolio.actual_level_1 inner join cw_employees level2 on cw_portfolio.actual_level_2 = level2.employee_code where cw_portfolio.login_date >= '".$from_date."' and cw_portfolio.login_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and cw_portfolio.login_code = '".$login_code."' and level1.login_code = '".$level_1."' and level2.login_code = '".$level_2."' ORDER BY nop DESC");
$life_rslt = json_decode(json_encode($life_rslt),true);
$life_nop = $life_rslt[0]['nop'];
$life_issued = $life_rslt[0]['issued'];
$life_pending = $life_rslt[0]['pending'];
// $life_source_amt = $life_rslt[0]['sourcing_amt'];
$life_pdc_amt = $life_rslt[0]['pdc_amt'];
$life_login_amt = $life_rslt[0]['login_amt'];
$life_pdc_login_amt = $life_pdc_amt + $life_login_amt;
$tot_life_nop += $life_nop;
$tot_life_issued += $life_issued;
$tot_life_pending += $life_pending;
// $tot_life_source_amt += $life_source_amt;
$tot_life_pdc_amt += $life_pdc_amt;
$tot_life_login_amt += $life_login_amt;
$tot_life_pdc_login_amt += $life_pdc_login_amt;
$health_rslt = $api_model->get_health_business_sts("SELECT cw_employees.employee_name,level1.login_code as level1_code,level1.employee_name as level1_name,level2.login_code as level2_code,level2.employee_name as level2_name,IFNULL(SUM(CASE WHEN cw_health.login_status in ('1','5') THEN 1 ELSE 0 END),0) nop, IFNULL(SUM(CASE WHEN cw_health.issuance_status = 1 THEN 1 ELSE 0 END),0) issued, IFNULL(SUM(CASE WHEN cw_health.issuance_status not in ('1','7') and cw_health.login_status = 5 THEN 1 ELSE 0 END),0) pending, IFNULL(SUM(CASE WHEN cw_health.login_status = 1 THEN ROUND(cw_health.net_premium,2) ELSE 0 END),0) pdc_amt, IFNULL(SUM(CASE WHEN cw_health.login_status = 5 THEN ROUND(cw_health.net_premium,2) ELSE 0 END),0) login_amt from cw_health inner join cw_employees on cw_employees.login_code = cw_health.login_code inner join cw_employees level1 on level1.employee_code = cw_health.actual_level_1 inner join cw_employees level2 on cw_health.actual_level_2 = level2.employee_code where cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and cw_health.login_code = '".$login_code."' and level1.login_code = '".$level_1."' and level2.login_code = '".$level_2."' ORDER BY nop DESC");
$health_rslt = json_decode(json_encode($health_rslt),true);
$health_nop = $health_rslt[0]['nop'];
$health_issued = $health_rslt[0]['issued'];
$health_pending = $health_rslt[0]['pending'];
// $health_source_amt = $health_rslt[0]['sourcing_amt'];
$health_pdc_amt = $health_rslt[0]['pdc_amt'];
$health_login_amt = $health_rslt[0]['login_amt'];
$health_pdc_login_amt = $health_pdc_amt + $health_login_amt;
$tot_health_nop += $health_nop;
$tot_health_issued += $health_issued;
$tot_health_pending += $health_pending;
// $tot_health_source_amt += $health_source_amt;
$tot_health_pdc_amt += $health_pdc_amt;
$tot_health_login_amt += $health_login_amt;
$tot_health_pdc_login_amt += $health_pdc_login_amt;
$emp_rslt = $api_model->get_life_business_sts("SELECT cw_employees.employee_name,level1.login_code as level1_code,level1.employee_name as level1_name,level2.login_code as level2_code,level2.employee_name as level2_name from cw_employees inner join cw_employees level1 on level1.employee_code = cw_employees.actual_level_1 inner join cw_employees level2 on cw_employees.actual_level_2 = level2.employee_code where cw_employees.trans_status = 1 and cw_employees.login_code = '".$login_code."'");
$emp_rslt = json_decode(json_encode($emp_rslt),true);
if(!empty($life_rslt[0]['employee_name'])){
$emp_rslt = $life_rslt;
}else
if(!empty($health_rslt[0]['employee_name'])){
$emp_rslt = $health_rslt;
}else{
$emp_rslt = $emp_rslt;
}
$employee_name = $emp_rslt[0]['employee_name'];
$level1_code = $emp_rslt[0]['level1_code'];
$level1_name = $emp_rslt[0]['level1_name'];
$level2_code = $emp_rslt[0]['level2_code'];
$level2_name = $emp_rslt[0]['level2_name'];
$arr["user_name"] = $employee_name;
$arr["level1_name"] = $level1_name;
$arr["level2_name"] = $level2_name;
$arr["user_id"] = $login_code;
$arr["level_1"] = $level1_code;
$arr["level_2"] = $level2_code;
$arr["from_date"] = $from_date;
$arr["to_date"] = $to_date;
$arr["life"]['nop']["value"] = $life_nop;
$arr["life"]['nop']["id_info"]["activity"] = 1;
$arr["life"]['pdc']["value"] = $life_pdc_amt;
$arr["life"]['pdc']["id_info"]["activity"] = 4;
$arr["life"]['login']["value"] = $life_login_amt;
$arr["life"]['login']["id_info"]["activity"] = 5;
$arr["life"]['pdc_login']["value"] = $life_pdc_login_amt;
$arr["life"]['pdc_login']["id_info"]["activity"] = 11;
$arr["life"]['issued']["value"] = $life_issued;
$arr["life"]['issued']["id_info"]["activity"] = 2;
$arr["life"]['pending']["value"] = $life_pending;
$arr["life"]['pending']["id_info"]["activity"] = 3;
$arr["health"]['nop']["value"] = $health_nop;
$arr["health"]['nop']["id_info"]["activity"] = 6;
$arr["health"]['pdc']["value"] = $health_pdc_amt;
$arr["health"]['pdc']["id_info"]["activity"] = 9;
$arr["health"]['login']["value"] = $health_login_amt;
$arr["health"]['login']["id_info"]["activity"] = 10;
$arr["health"]['pdc_login']["value"] = $health_pdc_login_amt;
$arr["health"]['pdc_login']["id_info"]["activity"] = 12;
$arr["health"]['issued']["value"] = $health_issued;
$arr["health"]['issued']["id_info"]["activity"] = 7;
$arr["health"]['pending']["value"] = $health_pending;
$arr["health"]['pending']["id_info"]["activity"] = 8;
$arr1[] = $arr;
}
$total_arr["user_name"] = "Over All Total";
$total_arr["level1_name"] = "";
$total_arr["level2_name"] = "";
$total_arr["user_id"] = "";
$total_arr["level_1"] = "";
$total_arr["level_2"] = "";
$total_arr["from_date"] = "";
$total_arr["to_date"] = "";
$total_arr["life"]['nop']["value"] = $tot_life_nop;
$total_arr["life"]['nop']["id_info"] = [];
$total_arr["life"]['pdc']["value"] = $tot_life_pdc_amt;
$total_arr["life"]['pdc']["id_info"] = [];
$total_arr["life"]['login']["value"] = $tot_life_login_amt;
$total_arr["life"]['login']["id_info"] = [];
$total_arr["life"]['pdc_login']["value"] = $tot_life_pdc_login_amt;
$total_arr["life"]['pdc_login']["id_info"] = [];
$total_arr["life"]['issued']["value"] = $tot_life_issued;
$total_arr["life"]['issued']["id_info"] = [];
$total_arr["life"]['pending']["value"] = $tot_life_pending;
$total_arr["life"]['pending']["id_info"] = [];
$total_arr["health"]['nop']["value"] = $tot_health_nop;
$total_arr["health"]['nop']["id_info"] = [];
$total_arr["health"]['pdc']["value"] = $tot_health_pdc_amt;
$total_arr["health"]['pdc']["id_info"] = [];
$total_arr["health"]['login']["value"] = $tot_health_login_amt;
$total_arr["health"]['login']["id_info"] = [];
$total_arr["health"]['pdc_login']["value"] = $tot_health_pdc_login_amt;
$total_arr["health"]['pdc_login']["id_info"] = [];
$total_arr["health"]['issued']["value"] = $tot_health_issued;
$total_arr["health"]['issued']["id_info"] = [];
$total_arr["health"]['pending']["value"] = $tot_health_pending;
$total_arr["health"]['pending']["id_info"] = [];
$arr1[] = $total_arr;
$header_arr[] = array('name' => 'Name','col' => "user_name",'sub_header' => false);
$header_arr[] = array('name' => 'Login Code','col' => "user_id",'sub_header' => false);
$header_arr[] = array('name' => 'Level 1 Reporter','col' => "level1_name",'sub_header' => false);
$header_arr[] = array('name' => 'Level 2 Reporter','col' => "level2_name",'sub_header' => false);
$header_arr[] = array('name' => 'Life','col' => "life",'sub_header' =>array('nop' => 'NOP','pdc' => "PDC",'login' => 'Login','pdc_login' => 'Total','issued' => 'Issued','pending' => 'Pending'));
$header_arr[] = array('name' => 'Health','col' => "health",'sub_header' =>array('nop' => 'NOP','pdc' => "PDC",'login' => 'Login','pdc_login' => 'Total','issued' => 'Issued','pending' => 'Pending'));
if($arr1){
echo json_encode(array('sts' => TRUE, "rslt"=>$arr1,'header'=> $header_arr,"error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else
if($frm === "get_business_data"){
$from_date = date('Y-m-d', strtotime($json->from_date));
$to_date = date('Y-m-d', strtotime($json->to_date));
$login_code = $json->user_id;
$level_1 = $json->level_1;
$level_2 = $json->level_2;
$activity = (int)$json->activity;
$table_name = "";
$select_column = "";
$join_table = "";
$where_cond = "";
$header_arr = array();
//ARRAY FOR CREATE A HEADER FORMAT TO BE BASED ON ACTIVITY WISE
// if($activity === 5 || $activity === 10){
// $header_arr[] = array('name' => 'Login Code','col' => "login_code",'sub_header' => false);
// }
$header_arr[] = array('name' => 'Client Name','col' => "client_name",'sub_header' => false);
$header_arr[] = array('name' => 'Contact Number','col' => "contact_number",'sub_header' => false);
$header_arr[] = array('name' => 'Company Name','col' => "company_name",'sub_header' => false);
$header_arr[] = array('name' => 'Product Name','col' => "product_name",'sub_header' => false);
$header_arr[] = array('name' => 'Login Status','col' => "login_status",'sub_header' => false);
$header_arr[] = array('name' => 'login Date','col' => "login_date",'sub_header' => false);
if($activity === 2|| $activity === 7){
$header_arr[] = array('name' => 'Issuance Status','col' => "issuance_status",'sub_header' => false);
$header_arr[] = array('name' => 'Issuance Date','col' => "issuance_date",'sub_header' => false);
$header_arr[] = array('name' => 'Source Amout','col' => "sourcing_amt",'sub_header' => false);
}else
if($activity === 3 || $activity === 8){
$header_arr[] = array('name' => 'Issuance Status','col' => "issuance_status",'sub_header' => false);
}else
if($activity === 4 || $activity === 5 || $activity === 9 || $activity === 10 || $activity === 11 || $activity === 12){
$header_arr[] = array('name' => 'Source Amout','col' => "sourcing_amt",'sub_header' => false);
}
$header_arr[] = array('name' => 'Issuance Remarks','col' => "issuance_remarks",'sub_header' => false);
if($activity === 1 || $activity === 2 || $activity === 3 || $activity === 4 || $activity === 5){
$header_arr[] = array('name' => 'RM Remarks','col' => "rm_remarks",'sub_header' => false);
$header_arr[] = array('name' => 'MIS Remarks','col' => "mis_remarks",'sub_header' => false);
}
//CREATE A QUERY FORMAT BASED ON PORTFOLIO AND HEALTH ACTIVITY WISE
if($activity === 1 || $activity === 2 || $activity === 3 || $activity === 4 || $activity === 5 || $activity === 11){
$table_name = "cw_portfolio";
$select_column = "cw_portfolio.client_name,cw_portfolio.contact_no as contact_number,cw_company_name.company_name,cw_product_name.product_name,cw_login_status.login_status,DATE_FORMAT(login_date,'%d-%m-%Y') as login_date,cw_portfolio.issuance_remarks,cw_portfolio.rm_remarks,cw_portfolio.mis_remarks";
$join_table = "inner join cw_company_name on cw_company_name.prime_company_name_id = cw_portfolio.product_company_name inner join cw_product_name on cw_product_name.prime_product_name_id = cw_portfolio.product_name inner join cw_employees level1 on level1.employee_code = cw_portfolio.actual_level_1 inner join cw_employees level2 on cw_portfolio.actual_level_2 = level2.employee_code inner join cw_login_status on cw_login_status.prime_login_status_id = cw_portfolio.login_status";
$where_cond = " cw_portfolio.login_date >= '".$from_date."' and cw_portfolio.login_date <= '".$to_date."' and cw_portfolio.trans_status = 1 and cw_portfolio.login_code = '".$login_code."' and level1.login_code = '".$level_1."' and level2.login_code = '".$level_2."'";
}
else
if($activity === 6 || $activity === 7 || $activity === 8 || $activity === 9 || $activity === 10 || $activity === 12){
$table_name = "cw_health";
$select_column = "cw_health.proposer_name as client_name,cw_health.contact_number,cw_health_company.company_name, cw_health_product.product_name,cw_login_status.login_status,DATE_FORMAT(login_date,'%d-%m-%Y') as login_date,cw_health.issued_remark as issuance_remarks";
$join_table = "inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health.company inner join cw_health_product on cw_health_product.prime_health_product_id = cw_health.product inner join cw_employees level1 on level1.employee_code = cw_health.actual_level_1 inner join cw_employees level2 on cw_health.actual_level_2 = level2.employee_code inner join cw_login_status on cw_login_status.prime_login_status_id = cw_health.login_status";
$where_cond = "cw_health.login_date >= '".$from_date."' and cw_health.login_date <= '".$to_date."' and cw_health.trans_status = 1 and cw_health.login_code = '".$login_code."' and level1.login_code = '".$level_1."' and level2.login_code = '".$level_2."'";
}
if($activity === 1){//FOR PORTFOLIO RESULTS
$select_column .= "";
$join_table .= "";
$where_cond .= " and cw_portfolio.login_status in ('1','5')";
}else
if($activity === 2){
$select_column .= ",cw_issuance_details.issuance_status, DATE_FORMAT(issuance_date,'%d-%m-%Y') as issuance_date, IFNULL(ROUND(cw_portfolio.total_net_premium,2),0) sourcing_amt";
$join_table .= " inner join cw_issuance_details on cw_issuance_details.prime_issuance_details_id = cw_portfolio.issuance_status";
$where_cond .= " and cw_portfolio.login_status = 5 and cw_portfolio.issuance_status = 2";
}else
if($activity === 3){
$select_column .= ",cw_issuance_details.issuance_status";
$join_table .= " inner join cw_issuance_details on cw_issuance_details.prime_issuance_details_id = cw_portfolio.issuance_status";
$where_cond .= " and cw_portfolio.login_status = 5 and cw_portfolio.issuance_status not in ('2','8')";
}else
if($activity === 4){
$select_column .= ", IFNULL(ROUND(cw_portfolio.total_net_premium,2),0) as sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_portfolio.login_status = 1";
}else
if($activity === 5){
$select_column .= ", IFNULL(ROUND(cw_portfolio.total_net_premium,2),0) as sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_portfolio.login_status = 5";
}else
if($activity === 11){//FOR PORTFOLIO RESULTS
$select_column .= ", IFNULL(ROUND(cw_portfolio.total_net_premium,2),0) as sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_portfolio.login_status in ('1','5')";
}else
if($activity === 6){//FOR HEALTH RESULTS
$select_column .= "";
$join_table .= "";
$where_cond .= " and cw_health.login_status in ('1','5')";
}else
if($activity === 7){
$select_column .= ",cw_issuance_status.issuance_status, DATE_FORMAT(issued_date,'%d-%m-%Y') as issuance_date, IFNULL(ROUND(cw_health.net_premium,2),0) as sourcing_amt";
$join_table .= " inner join cw_issuance_status on cw_issuance_status.prime_issuance_status_id = cw_health.issuance_status";
$where_cond .= " and cw_health.login_status = 5 and cw_health.issuance_status = 1";
}else
if($activity === 8){
$select_column .= ",cw_issuance_status.issuance_status";
$join_table .= " inner join cw_issuance_status on cw_issuance_status.prime_issuance_status_id = cw_health.issuance_status";
$where_cond .= " and cw_health.login_status = 5 and cw_health.issuance_status not in ('1','7')";
}else
if($activity === 9){
$select_column .= ", IFNULL(ROUND(cw_health.net_premium,2),0) as sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_health.login_status = 1";
}else
if($activity === 10){
$select_column .= ", IFNULL(ROUND(cw_health.net_premium,2),0) sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_health.login_status = 5";
}else
if($activity === 12){//FOR HEALTH RESULTS
$select_column .= ", IFNULL(ROUND(cw_health.net_premium,2),0) sourcing_amt";
$join_table .= "";
$where_cond .= " and cw_health.login_status in ('1','5')";
}
$business_rslt = $api_model->get_life_business_sts("SELECT $select_column from $table_name $join_table where $where_cond");
if($business_rslt){
echo json_encode(array('sts' => TRUE, "rslt"=>$business_rslt, "header"=>$header_arr, "error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}else{
echo json_encode(array('sts' => FALSE, "msg" => "Bad Request","rslt"=>"","error"=>""));
}
function return_rslt($mysql_portfolio_rslt,$mysql_health_rslt){
if((int)$mysql_portfolio_rslt[0]->count || (int)$mysql_health_rslt[0]->count){
echo json_encode(array('sts' => TRUE, "msg" => "Record Found","rslt"=>"","error"=>""));
}else{
echo json_encode(array('sts' => FALSE, "msg" => "No Record Found","rslt"=>"","error"=>""));
}
}
?>