File: /home/cafsindia/login_cafsindia_com/application/controllers/Business_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Business_report extends Action_controller{
public function __construct(){
parent::__construct('business_report');
$this->collect_base_info();
}
public function index(){
$channel_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_channel` where trans_status = 1')");
$channel_result = $channel_info->result();
$channel_info->next_result();
$channel_list["0"] = "---- Channel ----";
foreach($channel_result as $for){
$channel_id = $for->prime_channel_id;
$channel_name = $for->channel_name;
$channel_list[$channel_id] = $channel_name;
}
$data['channel_list'] = $channel_list;
$designation_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_designation` where trans_status = 1')");
$designation_result = $designation_info->result();
$designation_info->next_result();
$designation_list["0"] = "---- Designation ----";
foreach($designation_result as $des_for){
$designation_id = $des_for->prime_designation_id;
$designation_name = $des_for->designation_name;
$designation_list[$designation_id] = $designation_name;
}
$data['designation_list'] = $designation_list;
$this->load->view("$this->control_name/manage",$data);
}
public function get_business(){
$channel = implode(",", $this->input->post('channel'));
$designation = implode(",", $this->input->post('designation'));
$login_month = date("Y-m",strtotime("01-".$this->input->post('login_month')));
$type = (int)$this->input->post('type');
$today_date = date("Y-m-d");
$login_code_qry = 'select GROUP_CONCAT(login_code) as login_code,GROUP_CONCAT(employee_code) as employee_code from cw_employees where channel in ('.$channel.') and designation in ('.$designation.') and cw_employees.login_code != "" and employee_status =1';
$login_code_info = $this->db->query("CALL sp_a_run ('SELECT','$login_code_qry')");
$login_code_result = $login_code_info->result_array();
$login_code_info->next_result();
$login_code = str_replace(',', '","', $login_code_result[0]['login_code']);
$employee_code = str_replace(',', '","', $login_code_result[0]['employee_code']);
if(!$login_code){
echo json_encode(array('success' => FALSE,'message' => "Login Code not Available"));
exit(0);
}
// FOR SELF TYPE
if($type === 1){
$emp_qry = 'select cw_employees.login_code,cw_employees.employee_name,cw_employees.date_of_joining,d.designation_name as revised_designation,channel_name,cd.designation_name, CONCAT(TIMESTAMPDIFF( YEAR, cw_employees.date_of_joining, now() ),".", TIMESTAMPDIFF( MONTH, cw_employees.date_of_joining, now() ) % 12) as tenure,"0" as life,"0" as pdc,"0" as login,"0" as credit_amt,"0" as health,"0" as product_credit,a.employee_name as level1,b.employee_name as level2 from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation cd on cd.prime_designation_id = cw_employees.designation inner join cw_designation d on d.prime_designation_id = cw_employees.revised_designation inner join cw_employees a on cw_employees.level_1_reporting_person = a.employee_code inner join cw_employees b on cw_employees.level_2_reporting_person = b.employee_code where cw_employees.login_code != "" and cw_employees.employee_status =1 and cw_employees.channel in ('.$channel.') and cw_employees.designation in ('.$designation.')';
//echo $emp_qry; die;
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result_array();
$emp_info->next_result();
$emp_array = array_reduce($emp_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$business_qry = 'SELECT pickup_date,login_code, SUM(CASE WHEN cw_portfolio.login_status = 1 and (cw_portfolio.pickup_date like "%'.$login_month.'%" or cw_portfolio.expected_login_date like "%'.$login_month.'%") THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.total_net_premium ELSE 0 END) login,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.credit_amount ELSE 0 END) credit_amt from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and login_code in ("'.$login_code.'") and login_code != "" GROUP BY cw_portfolio.login_code';
//echo $business_qry; die;
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result_array();
$business_info->next_result();
$health_qry = 'SELECT login_date,login_code,IFNULL(SUM(cw_health.net_premium),0) as health,IFNULL(SUM(cw_health.product_credit),0) as product_credit from cw_health where cw_health.login_date like "%'.$login_month.'%" and cw_health.trans_status = 1 and cw_health.login_status != 6 and login_code in ("'.$login_code.'") and login_code != "" GROUP BY cw_health.login_code';
$health_info = $this->db->query("CALL sp_a_run ('SELECT','$health_qry')");
$health_result = $health_info->result_array();
$health_info->next_result();
$life_array = array_reduce($business_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$health_array = array_reduce($health_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
$final_array = array_replace_recursive($emp_array,$life_array,$health_array);
$final_array = array_values($final_array);
if($final_array){
echo json_encode(array('success' => TRUE,'business_result' => $final_array, 'type' => $type));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}else{// FOR TEAM TYPE
$emp_qry = 'select cw_employees.login_code,cw_employees.employee_code,d.designation_name as revised_designation,cw_employees.employee_name,cw_employees.date_of_joining,channel_name,cd.designation_name, CONCAT(TIMESTAMPDIFF( YEAR, cw_employees.date_of_joining, now() ),".", TIMESTAMPDIFF( MONTH, cw_employees.date_of_joining, now() ) % 12) as tenure,a.employee_name as level1,b.employee_name as level2 from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation cd on cd.prime_designation_id = cw_employees.designation inner join cw_designation d on d.prime_designation_id = cw_employees.revised_designation inner join cw_employees a on cw_employees.level_1_reporting_person = a.employee_code inner join cw_employees b on cw_employees.level_2_reporting_person = b.employee_code where cw_employees.login_code != "" and cw_employees.employee_status =1 and cw_employees.channel in ('.$channel.') and cw_employees.designation in ('.$designation.')';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_result = $emp_info->result();
$emp_info->next_result();
// FOR SELF(LEVEL 1 OR LEVEL2 REPORTING PERSON QUERY)
$business_qry = 'SELECT pickup_date,login_code, SUM(CASE WHEN cw_portfolio.login_status = 1 and (cw_portfolio.pickup_date like "%'.$login_month.'%" or cw_portfolio.expected_login_date like "%'.$login_month.'%") THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.total_net_premium ELSE 0 END) login,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.credit_amount ELSE 0 END) credit_amt from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and login_code in ("'.$login_code.'") and login_code != "" GROUP BY cw_portfolio.login_code';
//echo $business_qry; die;
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result_array();
$business_info->next_result();
$life_array = array_reduce($business_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
// FOR SELF(LEVEL 1 OR LEVEL2 REPORTING PERSON QUERY)
$health_qry = 'SELECT login_date,login_code,IFNULL(SUM(cw_health.net_premium),0) as health,IFNULL(SUM(cw_health.product_credit),0) as product_credit from cw_health where cw_health.login_date like "%'.$login_month.'%" and cw_health.trans_status = 1 and cw_health.login_status != 6 and login_code in ("'.$login_code.'") and login_code != "" GROUP BY cw_health.login_code';
$health_info = $this->db->query("CALL sp_a_run ('SELECT','$health_qry')");
$health_result = $health_info->result_array();
$health_info->next_result();
$health_array = array_reduce($health_result, function ($result, $arr) {
$result[$arr['login_code']] = $arr;
return $result;
}, array());
// FOR TEAM(LEVEL 1 REPORTING TEAM QUERY)
$bus_level_1_qry = 'SELECT pickup_date,login_code,level_1_reporting_person, SUM(CASE WHEN cw_portfolio.login_status = 1 and (cw_portfolio.pickup_date like "%'.$login_month.'%" or cw_portfolio.expected_login_date like "%'.$login_month.'%") THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.total_net_premium ELSE 0 END) login,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.credit_amount ELSE 0 END) credit_amt,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.non_credit_amount ELSE 0 END) non_credit_amt from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and level_1_reporting_person in ("'.$employee_code.'") and login_code not in ("'.$login_code.'") and login_code != "" GROUP BY cw_portfolio.level_1_reporting_person';
$bus_level_1_info = $this->db->query("CALL sp_a_run ('SELECT','$bus_level_1_qry')");
$bus_level_1_result = $bus_level_1_info->result_array();
$bus_level_1_info->next_result();
$bus_level_1_arr = array_reduce($bus_level_1_result, function ($result, $arr) {
$result[$arr['level_1_reporting_person']] = $arr;
return $result;
}, array());
//print_r($bus_level_1_arr);
// FOR TEAM(LEVEL 2 REPORTING TEAM QUERY)
$bus_level_2_qry = 'SELECT pickup_date,login_code,level_2_reporting_person, SUM(CASE WHEN cw_portfolio.login_status = 1 and (cw_portfolio.pickup_date like "%'.$login_month.'%" or cw_portfolio.expected_login_date like "%'.$login_month.'%") THEN cw_portfolio.total_net_premium ELSE 0 END) pdc,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.total_net_premium ELSE 0 END) login,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.credit_amount ELSE 0 END) credit_amt,SUM(CASE WHEN cw_portfolio.login_status = 5 and cw_portfolio.login_date like "%'.$login_month.'%" THEN cw_portfolio.non_credit_amount ELSE 0 END) non_credit_amt from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and level_2_reporting_person in ("'.$employee_code.'") and login_code not in ("'.$login_code.'") and login_code != "" GROUP BY cw_portfolio.level_2_reporting_person';
//echo $business_qry; die;
$bus_level_2_info = $this->db->query("CALL sp_a_run ('SELECT','$bus_level_2_qry')");
$bus_level_2_result = $bus_level_2_info->result_array();
$bus_level_2_info->next_result();
$bus_level_2_arr = array_reduce($bus_level_2_result, function ($result, $arr) {
$result[$arr['level_2_reporting_person']] = $arr;
return $result;
}, array());
//HEALTH ARRAY
$health_level_1_qry = 'SELECT login_date,login_code,level_1_reporting_person,IFNULL(SUM(cw_health.net_premium),0) as health,IFNULL(SUM(cw_health.product_credit),0) as product_credit from cw_health where cw_health.login_date like "%'.$login_month.'%" and cw_health.trans_status = 1 and cw_health.login_status != 6 and level_1_reporting_person in ("'.$employee_code.'") and login_code not in ("'.$login_code.'") and login_code != "" GROUP BY cw_health.level_1_reporting_person';
$health_level_1_info = $this->db->query("CALL sp_a_run ('SELECT','$health_level_1_qry')");
$health_level_1_result = $health_level_1_info->result_array();
$health_level_1_info->next_result();
// FOR TEAM(LEVEL 1 REPORTING TEAM QUERY)
$health_level_1_arr = array_reduce($health_level_1_result, function ($result, $arr) {
$result[$arr['level_1_reporting_person']] = $arr;
return $result;
}, array());
$health_level_2_qry = 'SELECT login_date,login_code,level_2_reporting_person,IFNULL(SUM(cw_health.net_premium),0) as health,IFNULL(SUM(cw_health.product_credit),0) as product_credit from cw_health where cw_health.login_date like "%'.$login_month.'%" and cw_health.trans_status = 1 and cw_health.login_status != 6 and level_2_reporting_person in ("'.$employee_code.'") and login_code not in ("'.$login_code.'") and login_code != "" GROUP BY cw_health.level_2_reporting_person';
$health_level_2_info = $this->db->query("CALL sp_a_run ('SELECT','$health_level_2_qry')");
$health_level_2_result = $health_level_2_info->result_array();
$health_level_2_info->next_result();
// FOR TEAM(LEVEL 2 REPORTING TEAM QUERY)
$health_level_2_arr = array_reduce($health_level_2_result, function ($result, $arr) {
$result[$arr['level_2_reporting_person']] = $arr;
return $result;
}, array());
/*echo "<pre>";
print_r($bus_level_1_arr); die;*/
$tr_line = "";
foreach ($emp_result as $key => $value) {
$emp_code = $value->employee_code;
$emp_name = $value->employee_name;
$login_code = $value->login_code;
$doj = date('d-m-Y', strtotime($value->date_of_joining));
$channel = $value->channel_name;
$designation = $value->designation_name;
$revised_designation = $value->revised_designation;
$tenure = $value->tenure;
$level_1 = $value->level1;
$level_2 = $value->level2;
$pdc = $life_array[$login_code]['pdc'] + $bus_level_1_arr[$emp_code]['pdc'] + $bus_level_2_arr[$emp_code]['pdc'];
$login = $life_array[$login_code]['login'] + $bus_level_1_arr[$emp_code]['login'] + $bus_level_2_arr[$emp_code]['login'];
$login_credit = $life_array[$login_code]['credit_amt'] + $bus_level_1_arr[$emp_code]['non_credit_amt'] + $bus_level_2_arr[$emp_code]['non_credit_amt'];
//$bus_level_1_arr[$emp_code]['credit_amt'] +
//+ $bus_level_2_arr[$emp_code]['credit_amt']
$login_credit = number_format((float)$login_credit, 2, '.', '');
$health = $health_array[$login_code]['health'] + $health_level_1_arr[$emp_code]['health'] + $health_level_2_arr[$emp_code]['health'];
$health_credit = $health_array[$login_code]['product_credit'] + $health_level_1_arr[$emp_code]['product_credit'] + $health_level_2_arr[$emp_code]['product_credit'];
$health_credit = number_format((float)$health_credit, 2, '.', '');
$total = $pdc + $login + $health;
if(!$pdc){
$pdc = "0";
}
if(!$login){
$login = "0";
}
if(!$health){
$health = "0";
}
if(!$total){
$total = "0";
}
//TABLE DATA'S CREATED
$tr_line .= "<tr><td>$login_code</td><td>$emp_name</td><td>$doj</td>
<td>$tenure</td><td>$channel</td><td>$designation</td><td>$revised_designation</td>
<td>$level_1</td><td>$level_2</td><td>$login</td><td>$pdc</td>
<td>$health</td><td>$total</td><td>$login_credit</td><td>$health_credit</td></tr>";
}
if(!$tr_line){
$tr_line = "<tr><td></td><td></td><td></td>
<td></td><td>No Results Found</td><td></td>
<td></td><td></td></tr>";
}
$table_info = "<table id='team_table' width='100%' class='table table-hover'>
<thead>
<tr>
<th>Login Code</th>
<th>Name</th>
<th>DOJ</th>
<th>Tenure</th>
<th>Channel</th>
<th>Designation</th>
<th>Revised Designation</th>
<th>Level 1</th>
<th>Level 2</th>
<th>Life</th>
<th>PDC</th>
<th>Health</th>
<th>Total</th>
<th>Life Revenue</th>
<th>Health Revenue</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
if($table_info){
echo json_encode(array('success' => TRUE,'table_info' => $table_info, 'type' => $type));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
}
?>