File: //home/cafsindia/login_cafsindia_com/application/controllers/Business_credit_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Business_credit_report extends Action_controller{
public function __construct(){
parent::__construct('business_credit_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_start_date = date("Y-m-d",strtotime("-2 month",strtotime("01-".$this->input->post('login_month'))));
$login_end_date = date("Y-m-t",strtotime("-2 month",strtotime("01-".$this->input->post('login_month'))));
//$login_end_date = date("Y-m-t",strtotime("01-".$this->input->post('login_month')));
$issued_next_month = date("Y-m-d",strtotime("-1 month",strtotime("20-".$this->input->post('login_month'))));
//echo "BSK $login_start_date :: $issued_next_month"; die;
//strtotime("-1 month",strtotime("01-".$this->input->post('login_month')))
// echo "$login_satrt_date::$login_end_date::$issued_next_month";die;
$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);
}else{
// FOR SELF TYPE
$emp_qry = 'select cw_employees.login_code,cw_employees.employee_code,cw_employees.employee_name,cw_employees.date_of_joining,channel_name,designation_name from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation 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 CASE WHEN "'.$login_start_date.'" < login_date then 1 else 0 end as status,pickup_date,login_code, SUM(cw_portfolio.credit_amount) as credit_amt from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and cw_portfolio.login_status = 5 and cw_portfolio.issuance_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and login_code in ("'.$login_code.'") GROUP BY cw_portfolio.login_code';
// CASE WHEN "'.$login_start_date.'" < login_date then 1 else 0 end as status,
$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_code,IFNULL(SUM(cw_health.product_credit),0) as product_credit from cw_health where cw_health.issued_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and cw_health.trans_status = 1 and cw_health.login_status != 6 and login_code in ("'.$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());
// cw_health.login_date between "'.$login_satrt_date.'" and "'.$login_end_date.'" and
// FOR TEAM(LEVEL 1 REPORTING TEAM QUERY)
$bus_report1_qry = 'SELECT pickup_date,cw_portfolio.level_1_reporting_person, SUM(non_credit_amount) as non_product_credit from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and level_1_reporting_person in ("'.$employee_code.'") and cw_portfolio.login_status = 5 and cw_portfolio.issuance_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and cw_portfolio.login_code != "" GROUP BY cw_portfolio.level_1_reporting_person';
$bus_report1_info = $this->db->query("CALL sp_a_run ('SELECT','$bus_report1_qry')");
$bus_report1_result = $bus_report1_info->result_array();
$bus_report1_info->next_result();
$bus_report1_arr = array_reduce($bus_report1_result, function ($result, $arr) {
$result[$arr['level_1_reporting_person']] = $arr;
return $result;
}, array());
$bus_report2_qry = 'SELECT level_2_reporting_person, SUM(non_credit_amount) as non_product_credit from cw_portfolio where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and level_2_reporting_person in ("'.$employee_code.'") and cw_portfolio.login_status = 5 and cw_portfolio.issuance_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and login_code != "" GROUP BY cw_portfolio.level_2_reporting_person';
$bus_report2_info = $this->db->query("CALL sp_a_run ('SELECT','$bus_report2_qry')");
$bus_report2_result = $bus_report1_info->result_array();
$bus_report2_info->next_result();
$bus_report2_arr = array_reduce($bus_report2_result, function ($result, $arr) {
$result[$arr['level_2_reporting_person']] = $arr;
return $result;
}, array());
//HEALTH ARRAY
$health_report1_qry = 'SELECT level_1_reporting_person,IFNULL(SUM(cw_health.non_product_credit),0) as non_product_credit from cw_health where cw_health.issued_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and cw_health.trans_status = 1 and cw_health.login_status != 6 and level_1_reporting_person in ("'.$employee_code.'") and login_code != "" GROUP BY cw_health.level_1_reporting_person';
$health_report1_info = $this->db->query("CALL sp_a_run ('SELECT','$health_report1_qry')");
$health_report1_result = $health_report1_info->result_array();
$health_report1_info->next_result();
// cw_health.login_date between "'.$login_satrt_date.'" and "'.$login_end_date.'" and
// FOR TEAM(LEVEL 1 REPORTING TEAM QUERY)
$health_report1_arr = array_reduce($health_report1_result, function ($result, $arr) {
$result[$arr['level_1_reporting_person']] = $arr;
return $result;
}, array());
$health_report2_qry = 'SELECT level_2_reporting_person,IFNULL(SUM(cw_health.non_product_credit),0) as non_product_credit from cw_health where cw_health.issued_date between "'.$login_start_date.'" and "'.$issued_next_month.'" and cw_health.trans_status = 1 and cw_health.login_status != 6 and level_2_reporting_person in ("'.$employee_code.'") and login_code != "" GROUP BY cw_health.level_2_reporting_person';
$health_report2_info = $this->db->query("CALL sp_a_run ('SELECT','$health_report2_qry')");
$health_report2_result = $health_report2_info->result_array();
$health_report2_info->next_result();
$health_report2_arr = array_reduce($health_report2_result, function ($result, $arr) {
$result[$arr['level_2_reporting_person']] = $arr;
return $result;
}, array());
$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;
$band = $value->band_value;
$port_source_credit = $life_array[$login_code]['credit_amt'];
$port_non_source_credit = $bus_report1_arr[$emp_code]['non_product_credit'] + $bus_report2_arr[$emp_code]['non_product_credit'];
$status = $life_array[$login_code]['status'];
//echo "BSK $status <br/>";
if((int)$status === 1){
$style = "style = 'color : red;'";
}else{
$style = "";
}
if(!$port_source_credit){
$port_source_credit = 0;
}
if(!$port_non_source_credit){
$port_non_source_credit = 0;
}
$port_source_credit = number_format((float)$port_source_credit, 2, '.', '');
$port_non_source_credit = number_format((float)$port_non_source_credit, 2, '.', '');
$health_source_credit = $health_array[$login_code]['product_credit'];
$health_non_source_credit = $health_report1_arr[$emp_code]['non_product_credit'] + $health_report2_arr[$emp_code]['non_product_credit'];
if(!$health_source_credit){
$health_source_credit = 0;
}
if(!$health_non_source_credit){
$health_non_source_credit = 0;
}
$health_source_credit = number_format((float)$health_source_credit, 2, '.', '');
$health_non_source_credit = number_format((float)$health_non_source_credit, 2, '.', '');
//TABLE DATA'S CREATED
$tr_line .= "<tr $style><td>$login_code</td><td>$emp_name</td><td>$doj</td>
<td>$channel</td><td>$designation</td><td>$band</td><td onclick=business_credit_log('port_sourcing','$login_code','$emp_code','$login_start_date','$issued_next_month');>$port_source_credit</td>
<td onclick=business_credit_log('port_non_sourcing','$login_code','$emp_code','$login_start_date','$issued_next_month');>$port_non_source_credit</td><td onclick=business_credit_log('health_sourcing','$login_code','$emp_code','$login_start_date','$issued_next_month');>$health_source_credit</td><td onclick=business_credit_log('health_non_sourcing','$login_code','$emp_code','$login_start_date','$issued_next_month');>$health_non_source_credit</td></tr>";
}
if(!$tr_line){
$tr_line = "<tr><td></td><td></td><td></td><td></td><td></td>
<td>No Results Found</td><td></td><td></td><td></td><td></td></tr>";
}
$table_info = "<table id='business_credit_table' width='100%' class='table table-hover'>
<thead>
<tr>
<th rowspan='2'>Login Code</th>
<th rowspan='2'>Name</th>
<th rowspan='2'>DOJ</th>
<th rowspan='2'>Channel</th>
<th rowspan='2'>Designation</th>
<th rowspan='2'>Band</th>
<th colspan='2'>Life Credit</th>
<th colspan='2'>Health Credit</th>
</tr>
<tr>
<th>Sourcing</th>
<th>Non Sourcing</th>
<th>Sourcing</th>
<th>Non Sourcing</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
if($table_info){
echo json_encode(array('success' => TRUE,'table_info' => $table_info));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
public function business_credit_data(){
$type = $this->input->post('type');
$login_code = $this->input->post('login_code');
$employee_code = $this->input->post('employee_code');
$issuance_start_date = $this->input->post('issuance_start_date');
$issuance_end_date = $this->input->post('issuance_end_date');
if($type === "port_sourcing"){
$business_qry = 'SELECT cw_portfolio.client_name,cw_portfolio.contact_no,net_premium ,login_date,cw_portfolio.issuance_date, cw_employees.login_code, cw_employees.employee_name,channel_name,designation_name,credit_amount,non_credit_amount from cw_portfolio inner join cw_employees on cw_employees.login_code = cw_portfolio.login_code inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and cw_portfolio.login_code = "'.$login_code.'" and cw_portfolio.login_status = 5 and cw_portfolio.issuance_date between "'.$issuance_start_date.'" and "'.$issuance_end_date.'"';
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result();
$business_info->next_result();
}else
if($type === "port_non_sourcing"){
$business_qry = 'SELECT cw_portfolio.client_name,cw_portfolio.contact_no,net_premium ,login_date,cw_portfolio.issuance_date, cw_employees.login_code, cw_employees.employee_name,channel_name,designation_name,credit_amount,non_credit_amount from cw_portfolio inner join cw_employees on cw_employees.login_code = cw_portfolio.login_code inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where cw_portfolio.trans_status = 1 and cw_portfolio.login_status != 6 and (cw_portfolio.level_1_reporting_person in ("'.$employee_code.'") or cw_portfolio.level_2_reporting_person in ("'.$employee_code.'")) and cw_portfolio.login_status = 5 and cw_portfolio.issuance_date between "'.$issuance_start_date.'" and "'.$issuance_end_date.'"';
//echo $business_qry; die;
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result();
$business_info->next_result();
}else
if($type === "health_sourcing"){
$business_qry = 'SELECT proposer_name,contact_number,net_premium ,login_date,issued_date, cw_employees.login_code, cw_employees.employee_name,channel_name,designation_name,product_credit,non_product_credit from cw_health inner join cw_employees on cw_employees.login_code = cw_health.login_code inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where cw_health.issued_date between "'.$issuance_start_date.'" and "'.$issuance_end_date.'" and cw_health.trans_status = 1 and cw_health.login_status != 6 and cw_health.login_code in ("'.$login_code.'")';
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result();
$business_info->next_result();
}else
if($type === "health_non_sourcing"){
$business_qry = 'SELECT proposer_name,contact_number,net_premium ,login_date,issued_date, cw_employees.login_code, cw_employees.employee_name,channel_name,designation_name,product_credit,non_product_credit from cw_health inner join cw_employees on cw_employees.login_code = cw_health.login_code inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where cw_health.issued_date between "'.$issuance_start_date.'" and "'.$issuance_end_date.'" and cw_health.trans_status = 1 and cw_health.login_status != 6 and (cw_health.level_1_reporting_person in ("'.$employee_code.'") or cw_health.level_2_reporting_person in ("'.$employee_code.'"))';
$business_info = $this->db->query("CALL sp_a_run ('SELECT','$business_qry')");
$business_result = $business_info->result();
$business_info->next_result();
}
$tr_line = "";
foreach ($business_result as $key => $value) {
$net_premium = $value->net_premium;
$employee_name = $value->employee_name;
$channel = $value->channel_name;
$designation = $value->designation_name;
$login_date = date('d-m-Y', strtotime($value->login_date));
if($value->login_date > $issued_start_date){
$style = "style = 'background-colour : red;'";
}else{
$style = "";
}
if($type === "port_sourcing" || $type === "port_non_sourcing"){
$client_name = $value->client_name;
$contact_no = $value->contact_no;
$sourcing = $value->credit_amount;
$non_sourcing = $value->non_credit_amount;
$issuance_date = date('d-m-Y', strtotime($value->issuance_date));
}else{
$client_name = $value->proposer_name;
$contact_no = $value->contact_number;
$sourcing = $value->product_credit;
$non_sourcing = $value->non_product_credit;
$issuance_date = date('d-m-Y', strtotime($value->issued_date));
}
//TABLE DATA'S CREATED
$tr_line .= "<tr $style><td>$client_name</td><td>$login_date</td><td>$issuance_date</td><td>$net_premium</td><td>$login_code</td><td>$employee_name</td>
<td>$channel</td><td>$designation</td><td>$sourcing</td><td>$non_sourcing</td></tr>";
}
if(!$tr_line){
$tr_line = "<tr><td></td><td></td><td></td><td></td><td></td>
<td>No Results Found</td><td></td><td></td><td></td><td></td></tr>";
}
$table_info = "<table id='credit_report_table' width='100%' class='table table-hover'>
<thead>
<tr>
<th>Client Name</th>
<th>Login Date</th>
<th>Issuance Date</th>
<th>Total Premium</th>
<th>Login Code</th>
<th>Employee Name</th>
<th>Channel</th>
<th>Designation</th>
<th>Sourcing</th>
<th>Non Sourcing</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
if($table_info){
echo json_encode(array('success' => TRUE,'table_info' => $table_info));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
?>