application/modules/hr/models/Hr_report.php
//use for datatable with live filtering
use for datatable with live filtering
use for datatable with live filtering
private function _get_datatables_query()
    {
        if($this->input->post('filter_empcode'))
            {
                $this->db->where('a.empcode', $this->input->post('filter_empcode'));
            }
        if($this->input->post('filter_dept'))
        {
            $this->db->where('a.departement', $this->input->post('filter_dept'));
        }
        if($this->input->post('filter_position'))
        {
            $this->db->where('a.position', $this->input->post('filter_position'));
        }
        if($this->input->post('filter_emp_status'))
        {
            $this->db->where('a.employeetype', $this->input->post('filter_emp_status'));
        }
        if($this->input->post('filter_start_date') != '' && $this->input->post('filter_end_date') != '' || $this->input->post('filter_start_date') != null)
        {
        $this->db->where('a.hire_date BETWEEN "'.$this->input->post('filter_start_date').'" AND "'.$this->input->post('filter_end_date').'"');
        }
        $this->db->select("a.empcode,
  a.nik_pim,
  a.badgenumber,
  a.empname,
  a.sex,
  a.address,
  a.homephoneno,
  a.mobilephoneno,
  a.email,
  a.religion,
  a.departement,
  c.dept_desc,
  CASE
    WHEN a.employeetype = 'kt' THEN 'Karyawan Tetap'
    ELSE a.employeetype
  END AS status_karyawan,
  a.npwp,
  a.nojamsostek,
  a.lifeinsuranceno,
  a.no_identitas,
  a.hire_date,
  a.position,
  b.position_desc,
  a.bankname,
  a.bankaccountno,
  a.sim_type,
  a.sim_number,
  a.sim_valid");
        $this->db->from('emp_master a');
        $this->db->join('master_position b','a.position = b.id_position','left');
        $this->db->join('master_dept c','a.departement = c.departement','left');
        $this->db->where('a.is_deleted != "1" and a.empcode not in (SELECT empcode FROM hr_termination WHERE dateterminate < CURRENT_DATE() - INTERVAL 26 DAY) and a.site_id = 2001');
        $i = 0;
        foreach ($this->column_search as $item) 
        {
            if($_POST['search']['value']) 
            {
                if($i===0) 
                {
                    $this->db->group_start(); 
                    $this->db->like($item, $_POST['search']['value']);
                }
                else
                {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if(count($this->column_search) - 1 == $i) 
                    $this->db->group_end(); 
            }
            $i++;
        }
        if(isset($_POST['order'])) 
        {
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order))
        {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
private function _get_datatables_query ( ) { if ( $ this -> input -> post ( 'filter_empcode' ) ) { $ this -> db -> where ( 'a.empcode' , $ this -> input -> post ( 'filter_empcode' ) ) ; } if ( $ this -> input -> post ( 'filter_dept' ) ) { $ this -> db -> where ( 'a.departement' , $ this -> input -> post ( 'filter_dept' ) ) ; } if ( $ this -> input -> post ( 'filter_position' ) ) { $ this -> db -> where ( 'a.position' , $ this -> input -> post ( 'filter_position' ) ) ; } if ( $ this -> input -> post ( 'filter_emp_status' ) ) { $ this -> db -> where ( 'a.employeetype' , $ this -> input -> post ( 'filter_emp_status' ) ) ; } if ( $ this -> input -> post ( 'filter_start_date' ) != '' && $ this -> input -> post ( 'filter_end_date' ) != '' || $ this -> input -> post ( 'filter_start_date' ) != null ) { $ this -> db -> where ( 'a.hire_date BETWEEN "' . $ this -> input -> post ( 'filter_start_date' ) . '" AND "' . $ this -> input -> post ( 'filter_end_date' ) . '"' ) ; } $ this -> db -> select ( " a.empcode,
  a.nik_pim,
  a.badgenumber,
  a.empname,
  a.sex,
  a.address,
  a.homephoneno,
  a.mobilephoneno,
  a.email,
  a.religion,
  a.departement,
  c.dept_desc,
  CASE
    WHEN a.employeetype = 'kt' THEN 'Karyawan Tetap'
    ELSE a.employeetype
  END AS status_karyawan,
  a.npwp,
  a.nojamsostek,
  a.lifeinsuranceno,
  a.no_identitas,
  a.hire_date,
  a.position,
  b.position_desc,
  a.bankname,
  a.bankaccountno,
  a.sim_type,
  a.sim_number,
  a.sim_valid " ) ; $ this -> db -> from ( 'emp_master a' ) ; $ this -> db -> join ( 'master_position b' , 'a.position = b.id_position' , 'left' ) ; $ this -> db -> join ( 'master_dept c' , 'a.departement = c.departement' , 'left' ) ; $ this -> db -> where ( 'a.is_deleted != "1" and a.empcode not in (SELECT empcode FROM hr_termination WHERE dateterminate < CURRENT_DATE() - INTERVAL 26 DAY) and a.site_id = 2001' ) ; $ i = 0 ; foreach ( $ this -> column_search as $ item ) { if ( $ _POST [ 'search' ] [ 'value' ] ) { if ( $ i === 0 ) { $ this -> db -> group_start ( ) ; $ this -> db -> like ( $ item , $ _POST [ 'search' ] [ 'value' ] ) ; } else { $ this -> db -> or_like ( $ item , $ _POST [ 'search' ] [ 'value' ] ) ; } if ( count ( $ this -> column_search ) - 1 == $ i ) $ this -> db -> group_end ( ) ; } $ i ++ ; } if ( isset ( $ _POST [ 'order' ] ) ) { $ this -> db -> order_by ( $ this -> column_order [ $ _POST [ 'order' ] [ '0' ] [ 'column' ] ] , $ _POST [ 'order' ] [ '0' ] [ 'dir' ] ) ; } else if ( isset ( $ this -> order ) ) { $ order = $ this -> order ; $ this -> db -> order_by ( key ( $ order ) , $ order [ key ( $ order ) ] ) ; } }
use for datatable with live filtering
use for datatable with live filtering
/*
SELECT 
  a.empcode,
  a.nik_pim,
  a.badgenumber,
  a.empname,
  a.sex,
  a.address,
  a.homephoneno,
  a.mobilephoneno,
  a.email,
  a.religion,
  a.departement,
  c.dept_desc,
  CASE
    WHEN a.employeetype = "kt" THEN "Karyawan Tetap"
    ELSE a.employeetype
  END AS status_karyawan,
  a.npwp,
  a.nojamsostek,
  a.lifeinsuranceno,
  a.no_identitas,
  a.hire_date,
  a.position,
  b.position_desc,
  a.bankname,
  a.bankaccountno,
  a.sim_type,
  a.sim_number,
  a.sim_valid
FROM
  emp_master a
  LEFT JOIN master_position b
  ON a.position = b.id_position
  LEFT JOIN master_dept c
  ON a.departement = c.departement
WHERE a.is_deleted != 1 
  AND a.empcode NOT IN 
  (SELECT 
    empcode 
  FROM
    hr_termination)
    ORDER BY nik_pim

*/ //add custom filter here //$this->db->where('created_at BETWEEN "'. date('Y-m-d', strtotime($from)). '" and "'. date('Y-m-d', strtotime($to)).'"'); /*if($this->input->post('filter_emp_status'))
        {
            $this->db->like('address', $this->input->post('address'));
        }*/ // loop column  // if datatable send POST for search // first loop // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND. //last loop //close bracket // here order processing