File: /home/cafsindia/uds.cafsinfotech.in/application/controllers_bk/Home.php
<?php
/**********************************************************
Filename: Home
Description: Chart view and Chart control logic developed, highchart integration based on role.
Author: Jaffer Sathik
Created on: 10-DEC-2018
Reviewed by: Udhayakumar Anandhan (REVIEW PENDING)
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details: HIGHCHARTS
Modification Date: 06/12/2019
Changed by: SVK AND NEHA
Change Info: HIGHCHARTS
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Action_controller.php");
class Home extends Action_controller {
public function __construct(){
parent::__construct();
$this->load->model("Homemodel");
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
//$this->collect_base_info();
}
public function logout(){
$logged_user_id = $_SESSION['logged_user_id'];
//Unset logged user details and update end time
$this->db->where('prime_logged_user_info_id', $logged_user_id);
$upd_info = $this->db->update('logged_user_info', array('end_time'=>date("Y-m-d H:i:s"),
'user_status'=>2));
if($upd_info){
unset($_SESSION['logged_user_id']);
}
$this->session->sess_destroy();
redirect('login');
}
//FOR UNAUTHORIZED LOGIN !
public function unauthorized(){
$logged_id = $this->session->userdata('logged_id');
$logged_user_role = $this->session->userdata('logged_user_role');
$random_number = $this->GenerateRandomNumber();
$update_qry = 'UPDATE cw_employees SET PASSWORD = "'.$random_number.'", trans_status = 0 WHERE prime_employees_id = "'.$logged_id.'"';
$query = $this->db->query("CALL sp_a_run ('UPDATE','$update_qry')");
$this->logout();
}
//CHANGING PASSWORD FOR UNAUTHORIZED LOGIN !
public function GenerateRandomNumber(){
$min = 1000;
$max = 9999;
$gen_random = rand($min, $max);
$prefix = 'UNKNOWN';
$randomNumber = $prefix . $gen_random;
return $randomNumber;
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$logged_id = $this->session->userdata('logged_id');
$logged_user_role = $this->session->userdata('logged_user_role');
$data['quick_header_menu'] = $this->Homemodel->get_header_menu($logged_id);
$data['quick_report_menu'] = $this->Homemodel->get_report_menu($logged_user_role);
//$data = "";
$this->load->view('home',$data);
}
// TOTAL PAY DEPARTMENT WISE
public function get_dept_salary_info(){
$start_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("Y-m-d",strtotime($this->input->post('end_date'))));
$dept_salary_info = $this->db->query("CALL sp_a_run ('SELECT','select IFNULL(SUM(cw_transactions.net_pay),0) as net_pay,cw_department.department from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code join cw_department on cw_department.prime_department_id = cw_employees.department where cw_employees.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' group by cw_department.department')");
$dept_salary_rlst = $dept_salary_info->result();
$dept_salary_info->next_result();
$rows = array();
$rows['name'] = "Department";
foreach($dept_salary_rlst as $rlst){
$net_pay = $rlst->net_pay;
$department = $rlst->department;
$rows['data'][] = array("name"=>$department,"y"=>$net_pay);
}
$department = array();
array_push($department,$rows);
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
// EMPLOYEES COUNT DESIGNATION WISE
public function get_designation_employee_count_info(){
$start_date = date("d-m-Y",strtotime($this->input->post('start_date')));
$end_date = date("d-m-Y",strtotime($this->input->post('end_date')));
$query = 'select count(*) as count,cw_position.position_name from cw_employees join cw_position on cw_position.prime_position_id = cw_employees.designation where cw_employees.trans_status = 1 group by cw_position.prime_position_id';
$dept_emp_count_info = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$dept_emp_count_rlst = $dept_emp_count_info->result();
$dept_emp_count_info->next_result();
$rows = array();
$rows1 = array();
$rows['name'] = 'TOTAL EMPLOYEES';
$rows1['name'] = 'DESIGNATION';
foreach($dept_emp_count_rlst as $rlst){
$count = $rlst->count;
$designation = $rlst->position_name;
$rows['data'][] = $designation;
$rows1['data'][] = $count;
}
$designation_info = array();
array_push($designation_info,$rows);
array_push($designation_info,$rows1);
echo json_encode($designation_info,JSON_NUMERIC_CHECK);
}
//EMPLOYEES AGE DISTRIBUTION
public function get_employee_age_distribution(){
$start_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('end_date'))));
$dept_salary_by_age_info = 'select GROUP_CONCAT(IFNULL(cw_employees.emp_age,0)) as emp_age,cw_gender.gender from cw_employees inner join cw_gender on cw_gender.prime_gender_id = cw_employees.gender where cw_employees.trans_status = 1 group by cw_employees.gender';
$dept_salary_by_age_info = $this->db->query("CALL sp_a_run ('SELECT','$dept_salary_by_age_info')");
$dept_salary_by_age_rlst = $dept_salary_by_age_info->result();
$dept_salary_by_age_info->next_result();
$department = array();
foreach($dept_salary_by_age_rlst as $rlst){
$rows = array();
$gender = $rlst->gender;
$emp_age = $rlst->emp_age;
$rows['name'] = $gender;
$rows['data'] = $emp_age;
//$rows['data'] = array("name"=>$gender,"y"=>$emp_age);
array_push($department,$rows);
}
echo json_encode(array('series' => $department),JSON_NUMERIC_CHECK);
}
//TOP 10 EMPLOYEES BY LOP DAY
public function get_leave_details(){
$start_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date("d-m-Y",strtotime($this->input->post('end_date'))));
$tble_line = '';
$lop_day_info = $this->db->query("CALL sp_a_run ('SELECT','select cw_monthly_input.employee_code,cw_monthly_input.emp_name,IFNULL(SUM(cw_monthly_input.lop_days),0) as lop_days from cw_employees join cw_monthly_input on cw_employees.employee_code = cw_monthly_input.employee_code where cw_employees.trans_status = 1 and (cw_employees.termination_status = 0 or cw_employees.resignation_date between date_format(str_to_date('$start_date',\"%d-%m-%Y\"),\"%Y-%m-%d\") and date_format(str_to_date('$end_date',\"%d-%m-%Y\"),\"%Y-%m-%d\")) and date_format( str_to_date(concat(\"01-\",cw_monthly_input.process_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' and cw_monthly_input.lop_days > 0 group by cw_employees.employee_code order by lop_days DESC limit 10')");
$lop_day_rslt = $lop_day_info->result();
$lop_day_info->next_result();
foreach($lop_day_rslt as $send_rlst){
$employee_code = $send_rlst->employee_code;
$emp_name = $send_rlst->emp_name;
$lop_days = $send_rlst->lop_days;
$tble_line .= "<tr>
<td>$emp_name</td>
<td>$employee_code</td>
<td>$lop_days</td>
</tr>";
}
if($tble_line === ''){
$tble_line = "<tr><td colspan='3'></td></tr>";
}
$table_data = "<table class='table table-striped table-bordered' id='material_info_table'>
<thead>
<tr>
<th scope='col'>Employee Name</th>
<th scope='col'>Employee Code</th>
<th scope='col'>LOP Days</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
echo json_encode(array("success" => TRUE,'message' => $table_data));
}
// EMPLOYEES COUNT DEPARTMENT WISE
public function employees_count_chart(){
$start_date = date("Y-m-d",strtotime($this->input->get('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->get('end_date')));
//echo "BSK $start_date :: $end_date"; die;
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT count(emp_name) as emp_count,cw_department.department as department FROM cw_employees INNER join cw_department on cw_employees.department = cw_department.prime_department_id where cw_employees.trans_status = 1 and date_format(cw_employees.date_of_joining,\"%Y-%m-%d\") BETWEEN \"$start_date\" AND \"$end_date\" GROUP BY cw_department.department')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_emp['name'] = 'Total Employees';
$series1['name'] = 'Department';
foreach($view_result as $key => $value){
$employees = $value['emp_count'];
$department = $value['department'];
$total_emp['data'][] = $department;
$series1['data'][] = $employees;
}
$result = array();
array_push($result,$total_emp);
array_push($result,$series1);
print json_encode($result,JSON_NUMERIC_CHECK);
}
// GENDER DISTRIBUTION CHART
public function gen_distribution_chart(){
$start_date = date("Y-m-d",strtotime($this->input->post('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->post('end_date')));
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT IFNULL(count(emp_name),0) as emp_count,cw_gender.gender as gender FROM cw_employees INNER join cw_gender on cw_employees.gender = cw_gender.prime_gender_id where cw_employees.termination_status = 0 and cw_employees.supplementary_status = 0 and cw_employees.trans_status = 1 and date_format(cw_employees.date_of_joining,\"%Y-%m-%d\") BETWEEN \"$start_date\" AND \"$end_date\" GROUP BY cw_gender.gender')");
$view_result = $view_data->result();
$view_data->next_result();
$rows = array();
$rows['name'] = "Gender";
foreach($view_result as $rlst){
$gender = $rlst->gender;
$emp_count = $rlst->emp_count;
$rows['data'][] = array("name"=>$gender,"y"=>$emp_count);
}
$gender = array();
array_push($gender,$rows);
echo json_encode(array('series' => $gender),JSON_NUMERIC_CHECK);
}
// TOTAL SALARY MONTH WISE
public function total_salary_month_chart(){
$start_date = $this->db->escape(date("d-m-Y",strtotime($this->input->get('start_date'))));
$end_date = $this->db->escape(date("d-m-Y",strtotime($this->input->get('end_date'))));
$view_data = $this->db->query("CALL sp_a_run ('SELECT','SELECT IFNULL(SUM(cw_transactions.net_pay),0) as total_pay,transactions_month from cw_transactions where cw_transactions.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' group by transactions_month order by date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\")')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_salary['name'] = 'Total Salary';
$series1['name'] = 'Month';
foreach($view_result as $key => $value){
$salary = $value['total_pay'];
$transactions_month = date("M-Y",strtotime("01-".$value['transactions_month']));
$total_salary['data'][] = $transactions_month;
$series1['data'][] = $salary;
}
$result = array();
array_push($result,$total_salary);
array_push($result,$series1);
print json_encode($result,JSON_NUMERIC_CHECK);
}
// DESIGNATION WISE NET PAY
public function design_wise_sal(){
$start_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('start_date'))));
$end_date = $this->db->escape(date('Y-m-d',strtotime($this->input->post('end_date'))));
$view_data = $this->db->query("CALL sp_a_run ('SELECT','select IFNULL(SUM(cw_transactions.net_pay),0) as net_pay,cw_position.position_name from cw_employees join cw_position on cw_position.prime_position_id = cw_employees.designation inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code where cw_employees.trans_status = 1 and date_format(str_to_date(concat(\"01-\",cw_transactions.transactions_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' group by cw_position.position_name')");
$view_result = $view_data->result_array();
$view_data->next_result();
$total_pay['name'] = 'Net Pay';
$series1['name'] = 'DESIGNATION';
foreach($view_result as $key => $value){
$net_pay = $value['net_pay'];
$designation = $value['position_name'];
$total_pay['data'][] = $net_pay;
$series1['data'][] = $designation;
}
$result = array();
array_push($result,$series1);
array_push($result,$total_pay);
print json_encode($result,JSON_NUMERIC_CHECK);
}
}
?>