File: //home/cafsindia/groups_cafsindia_com/application/controllers/Budget_capacity.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Budget_capacity extends Action_controller{
public function __construct(){
parent::__construct('budget_capacity');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$branch_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_branch` where trans_status = 1')");
$branch_result = $branch_info->result();
$branch_info->next_result();
$branch_list["0"] = "---- Branch ----";
foreach($branch_result as $branch_for){
$branch_id = $branch_for->prime_branch_id;
$branch_name = $branch_for->branch_name;
$branch_list[$branch_id] = $branch_name;
}
$data['branch_list'] = $branch_list;
$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;
$this->load->view("$this->control_name/manage",$data);
}
public function get_budget_capacity(){
$channel = implode(",", $this->input->post('channel'));
$branch = implode(",", $this->input->post('branch'));
$bud_bran_chan_where_con = '';
$emp_bran_chan_where_con = '';
if($channel && $branch){
$bud_bran_chan_where_con = 'and cw_budget.channel_name in ('.$channel.') and cw_budget.branch in ('.$branch.')';
$emp_bran_chan_where_con = 'and cw_employees.channel_name in ('.$channel.') and cw_employees.branch in ('.$branch.')';
}else
if($channel && !$branch){
$bud_bran_chan_where_con = 'and cw_budget.channel_name in ('.$channel.')';
$emp_bran_chan_where_con = 'and cw_employees.channel_name in ('.$channel.')';
}
if(!$channel && $branch){
$bud_bran_chan_where_con = 'and cw_budget.branch in ('.$branch.')';
$emp_bran_chan_where_con = 'and cw_employees.branch in ('.$branch.')';
}
// $login_code = implode(",", $this->input->post('login_code'));
$budget_capacity_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,
CASE WHEN cw_budget.band = 1 THEN actual_count ELSE 0 END as S,
CASE WHEN cw_budget.band = 2 THEN actual_count ELSE 0 END as M,
CASE WHEN cw_budget.band = 3 THEN actual_count ELSE 0 END as L,
CASE WHEN cw_budget.band = 4 THEN actual_count ELSE 0 END as T
from cw_budget inner join cw_channel on cw_channel.prime_channel_id = cw_budget.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_budget.branch inner join cw_band on cw_band.prime_band_id = cw_budget.band and cw_budget.trans_status = 1 '.$bud_bran_chan_where_con.' GROUP BY cw_budget.branch,cw_budget.channel_name,cw_budget.band';
$budget_capacity_info = $this->db->query("CALL sp_a_run ('SELECT','$budget_capacity_qry')");
$budget_capacity_rslt = $budget_capacity_info->result_array();
$budget_capacity_info->next_result();
$budget_arr = array_reduce($budget_capacity_rslt, function ($result, $arr) {
$branch_name = $arr['branch_name'];
$channel_name = $arr['channel_name'];
$band_value = $arr['band_value'];
$count = $arr[$band_value];
$result[$branch_name][$channel_name][$band_value] = $count;
return $result;
}, array());
$active_s_band_qry = 'SELECT cw_branch.prime_branch_id as branch,cw_channel.prime_channel_id as channel,cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,CONCAT(cw_employees.level_1_reporting," - ",a.emp_name) as level1,CONCAT(cw_employees.level_2_reporting," - ",b.emp_name) as level2,count(cw_employees.login_code) as emp_count from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_employees a on cw_employees.level_1_reporting = a.login_code inner join cw_employees b on cw_employees.level_2_reporting = b.login_code where cw_employees.employee_status != 1 and cw_employees.band = 1 and cw_employees.trans_status = 1 '.$emp_bran_chan_where_con.' GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.level_1_reporting';
$active_s_band_info = $this->db->query("CALL sp_a_run ('SELECT','$active_s_band_qry')");
$active_s_band_rslt = $active_s_band_info->result_array();
$active_s_band_info->next_result();
$emp_s_band_arr = array_reduce($active_s_band_rslt, function ($result, $arr) {
$emp_count = $arr['emp_count'];
$level1 = $arr['level1'];
$level2 = $arr['level2'];
$branch_name = $arr['branch_name'];
$channel_name = $arr['channel_name'];
$branch = $arr['branch'];
$channel = $arr['channel'];
$l_key = $level2."::".$branch_name."::".$channel_name."::".$branch."::".$channel;
$result['CAFSUSR00024 - Suresh Kumar'][$l_key][$level1] = $emp_count;
return $result;
}, array());
// die;
// $emp_s_band_pend_arr = array_reduce($active_s_band_rslt, function ($result, $arr) {
// $emp_count = $arr['emp_count'];
// $level1 = $arr['level1'];
// $level2 = $arr['level2'];
// $branch_name = $arr['branch_name'];
// $channel_name = $arr['channel_name'];
// $branch = $arr['branch'];
// $channel = $arr['channel'];
// // $l_key = $level2."::".$branch_name."::".$channel_name."::".$branch."::".$channel;
// $result['CAFSUSR00024 - Suresh Kumar'][$branch_name][$channel_name][$level2][$level1] = $emp_count;
// return $result;
// }, array());
$branch_channel_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,
SUM(CASE WHEN cw_employees.band = 1 THEN 1 ELSE 0 END) as S,
SUM(CASE WHEN cw_employees.band = 2 THEN 1 ELSE 0 END) as M,
SUM(CASE WHEN cw_employees.band = 3 THEN 1 ELSE 0 END) as L from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band where cw_employees.employee_status = 0 and cw_employees.trans_status = 1 '.$emp_bran_chan_where_con.' GROUP BY cw_employees.branch,cw_employees.channel_name,cw_employees.band';
$branch_channel_info = $this->db->query("CALL sp_a_run ('SELECT','$branch_channel_qry')");
$branch_channel_rslt = $branch_channel_info->result_array();
$branch_channel_info->next_result();
$branch_channel_arr = array_reduce($branch_channel_rslt, function ($result, $arr) {
$branch_name = $arr['branch_name'];
$channel_name = $arr['channel_name'];
$band_value = $arr['band_value'];
$count = $arr[$band_value];
$result[$branch_name][$channel_name][$band_value] = $count;
return $result;
}, array());
$tr_line = "";
foreach($emp_s_band_arr as $t_key => $t_info) {
$t_count = count($t_info);
$rowspan_tot = 1;
// $t_band_tot = $budget_arr[$t_branch][$t_channel]["T"];
$tr_list = "";
$total_t += $t_count;
// print_r($t_info);
foreach($t_info as $l_key => $l_info) {
$l_count = count($l_info)+1;
$rowspan_tot += $l_count;
$l_band_arr = explode("::",$l_key);
$l_code = $l_band_arr[0];
$l_branch = $l_band_arr[1];
$l_channel = $l_band_arr[2];
$l_branch_id = $l_band_arr[3];
$l_channel_id = $l_band_arr[4];
$l_band_tot = $budget_arr[$l_branch][$l_channel]["L"];
$total_l += $l_count;
if($l_band_tot > 0){
$tr_list .= "<tr>
<td rowspan='$l_count' style = 'border: 0.3px solid #000000;'>$l_branch</td>
<td rowspan='$l_count' style = 'border: 0.3px solid #000000;'>$l_channel</td>
<td rowspan='$l_count' style = 'border: 0.3px solid #000000;'>$l_code</td>
</tr >";
}else{
$tr_list .= "<tr>
<td rowspan='$l_count' style = 'border: 0.3px solid #000000;'>$l_branch</td>
<td rowspan='$l_count' style = 'border: 0.3px solid #000000;'>$l_channel</td>
<td rowspan='$l_count' style = 'color:red; border: 0.3px solid #000000;'>Please Map Budget Capacity</td>
</tr>";
}
foreach ($l_info as $m_key => $m_info) {
$m_band_tot = $budget_arr[$l_branch][$l_channel]["M"];
$s_band_tot = $budget_arr[$l_branch][$l_channel]["S"];
$total_m += count($m_key);
$total_s += $m_info;
$s_actual = $s_band_tot / $m_band_tot;
if(is_int($s_actual)){
$total_act_s += $s_actual;
}
$s_pending = $s_actual - $m_info;
if(is_int($s_pending)){
$total_pend_s += $s_pending;
}
if($s_pending !== 0){
$color = "color: red;";
}else{
$color = "";
}
if($m_band_tot > 0){
$l_emp_code_arr = explode(" - ",$l_code);
$l_emp_code = $l_emp_code_arr[0];
$m_emp_code_arr = explode(" - ",$m_key);
$m_emp_code = $m_emp_code_arr[0];
$tr_list .= "<tr>
<td style = 'border: 0.3px solid #000000;'>$m_key</td>
<td style = 'border: 0.3px solid #000000;'>$s_actual</td>
<td style = 'border: 0.3px solid #000000;' id = 'active_emp' onclick = show_active_emp('$l_branch_id','$l_channel_id','$m_emp_code','$l_emp_code');>$m_info</td>
<td style = 'border: 0.3px solid #000000; $color'>$s_pending</td>
</tr>";
}else{
$tr_list .= "<tr>
<td style = 'color:red; border: 0.3px solid #000000;'>Please Map Budget Capacity</td>
<td colspan = '3' style = 'color:red; border: 0.3px solid #000000;'>Please Map Budget Capacity</td>
</tr>";
}
}
}
$tr_line .= "<tr>
<td rowspan='$rowspan_tot' style = 'border: 0.3px solid #000000;'> $t_key</td>
</tr>
$tr_list";
}
foreach ($budget_arr as $branch_key => $channel_arr) {
foreach ($channel_arr as $channel_key => $band_arr) {
$capacity_l_count = $band_arr['L'];
$capacity_m_count = $band_arr['M'];
$capacity_s_count = $band_arr['S'];
$emp_l_count = $branch_channel_arr[$branch_key][$channel_key]['L'];
$emp_m_count = $branch_channel_arr[$branch_key][$channel_key]['M'];
$emp_s_count = $branch_channel_arr[$branch_key][$channel_key]['S'];
// echo "$emp_l_count";die;
$pen_emp_l_count = $capacity_l_count - $emp_l_count;
$pen_emp_m_count = $capacity_m_count - $emp_m_count;
$pen_emp_s_count = $capacity_s_count - $emp_s_count;
if($pen_emp_l_count > 0){
$tr_line .= "<tr>
<td style = 'border: 0.3px solid #000000;'> $t_key</td>
<td style = 'border: 0.3px solid #000000;'> $branch_key</td>
<td style = 'border: 0.3px solid #000000;'> $channel_key</td>
<td colspan='6' style = 'border: 0.3px solid #000000;'> Pending Count(<span style ='color:red;'> $branch_key - $channel_key - L</span>) => <span style ='color:red;'> $pen_emp_l_count</span></td>
</tr>
";
}else
if($pen_emp_m_count > 0){
$tr_line .= "<tr>
<td style = 'border: 0.3px solid #000000;'> $t_key</td>
<td style = 'border: 0.3px solid #000000;'> $branch_key</td>
<td style = 'border: 0.3px solid #000000;'> $channel_key</td>
<td colspan='6' style = 'border: 0.3px solid #000000;'> Pending Count(<span style ='color:red;'> $branch_key - $channel_key - M</span>) => <span style ='color:red;'> $pen_emp_m_count</span></td>
</tr>
";
}else
if($pen_emp_s_count > 0){
$tr_line .= "<tr>
<td style = 'border: 0.3px solid #000000;'> $t_key</td>
<td style = 'border: 0.3px solid #000000;'> $branch_key</td>
<td style = 'border: 0.3px solid #000000;'> $channel_key</td>
<td colspan='6' style = 'border: 0.3px solid #000000;'> Pending Count(<span style ='color:red;'> $branch_key - $channel_key - S</span>) => <span style ='color:red;'> $pen_emp_s_count</span></td>
</tr>";
}
}
}
$table_info = "<table id = 'budget_capacity_table' class = 'table table-bordered table-stripted'>
<thead>
<tr>
<th rowspan = '2'>T</th>
<th rowspan = '2'>BRANCH</th>
<th rowspan = '2'>CHANNEL</th>
<th rowspan = '2'>L</th>
<th rowspan = '2'>M</th>
<th colspan = '3'>S</th>
</tr>
<tr>
<th>ACTUAL</th>
<th>FILLED</th>
<th>PENDING</th>
</tr>
</thead>
<tboady>
$tr_line
<td colspan = '5' style = 'border: 0.3px solid #000000;text-align: right;'><h5 style = 'color:red;'>Total</h5></td>
<td style = 'border: 0.3px solid #000000; color: blue;'>$total_act_s</td>
<td style = 'border: 0.3px solid #000000; color: blue;'>$total_s</td>
<td style = 'border: 0.3px solid #000000; color: blue;'>$total_pend_s</td>
</tbody>
</table>";
if($tr_line){
echo json_encode(array('success' => TRUE,'table_info' => $table_info));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
public function get_active_emp_details(){
$channel = $this->input->post('channel');
$branch = $this->input->post('branch');
$level1 = $this->input->post('level1');
$level2 = $this->input->post('level2');
$active_emp_qry = 'SELECT cw_branch.branch_name,cw_channel.channel_name,cw_band.band_value,cw_designation.designation,CONCAT(cw_employees.login_code," - ",cw_employees.emp_name) as emp_code,cw_employees.date_of_joining,active_status_value from cw_employees inner join cw_channel on cw_channel.prime_channel_id = cw_employees.channel_name inner join cw_branch on cw_branch.prime_branch_id = cw_employees.branch inner join cw_band on cw_band.prime_band_id = cw_employees.band inner join cw_designation on cw_designation.prime_designation_id = cw_employees.designation inner join cw_employees a on cw_employees.level_1_reporting = a.login_code inner join cw_employees b on cw_employees.level_2_reporting = b.login_code inner join cw_active_status on cw_active_status.prime_active_status_id = cw_employees.employee_status where cw_employees.employee_status != 1 and cw_employees.band = 1 and cw_employees.trans_status = 1 and cw_employees.branch = "'.$branch.'" and cw_employees.channel_name = "'.$channel.'" and a.login_code = "'.$level1.'" and b.login_code = "'.$level2.'"';
$active_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$active_emp_qry')");
$active_emp_rslt = $active_emp_info->result_array();
$active_emp_info->next_result();
if($active_emp_rslt[0]){
echo json_encode(array('success' => TRUE,'active_emp_rslt' => $active_emp_rslt));
}else{
echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
}
}
}
?>