File: //home/cafsindia/groups_cafsindia_com/application/controllers/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(){
$this->session->sess_destroy();
redirect('login');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$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_designation.designation from cw_employees join cw_designation on cw_designation.prime_designation_id = cw_employees.designation where cw_employees.trans_status = 1 group by cw_designation.prime_designation_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->designation;
$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 date_format( str_to_date(concat(\"01-\",cw_monthly_input.process_month),\"%d-%m-%Y\"),\"%Y-%m-%d\") BETWEEN '$start_date' AND '$end_date' 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')));
// $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.employee_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')");
// $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_designation.designation from cw_employees join cw_designation on cw_designation.prime_designation_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_designation.designation')");
// $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['designation'];
// $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);
// }
}
?>