application/modules/ticket/models/rpt_gang_model.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('b.departement', $this->input->post('filter_dept'));
        }
        if($this->input->post('filter_position'))
        {
            $this->db->where('b.position', $this->input->post('filter_position'));
        }
        if($this->input->post('filter_emp_status'))
        {
            $this->db->where('b.employeetype', $this->input->post('filter_emp_status'));
        }
        if($this->input->post('filter_start') || $this->input->post("filter_end"))
        {
            $this->db->where('a.attd_date BETWEEN "'.$this->input->post("filter_start").'" AND "'.$this->input->post("filter_end").'"');
        }
        $this->db->select('COALESCE(a.empcode,"Grand Total") as empcode, COALESCE(b.empname,"Sub Total") as empname, COALESCE(a.attd_date,"Sub Total") as attd_date,  MONTH(a.attd_date) as month, YEAR(a.attd_date) as year, c.upah, a.overtime, a.attd_code,
round((a.overtime * 1 / 173 * c.upah)) as lembur
, coalesce(ROUND((1/173*c.upah) * SUM(a.overtime)),0) as total,a.remarks, b.departement,b.position, b.employeetype');
        $this->db->from('gang_activity a, emp_master b, hr_hist_status c');
        $this->db->where('a.empcode = b.empcode
and a.empcode = c.empcode');
        $this->db->group_by("a.empcode, a.attd_date");
		$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 ( 'b.departement' , $ this -> input -> post ( 'filter_dept' ) ) ; } if ( $ this -> input -> post ( 'filter_position' ) ) { $ this -> db -> where ( 'b.position' , $ this -> input -> post ( 'filter_position' ) ) ; } if ( $ this -> input -> post ( 'filter_emp_status' ) ) { $ this -> db -> where ( 'b.employeetype' , $ this -> input -> post ( 'filter_emp_status' ) ) ; } if ( $ this -> input -> post ( 'filter_start' ) || $ this -> input -> post ( " filter_end " ) ) { $ this -> db -> where ( 'a.attd_date BETWEEN "' . $ this -> input -> post ( " filter_start " ) . '" AND "' . $ this -> input -> post ( " filter_end " ) . '"' ) ; } $ this -> db -> select ( 'COALESCE(a.empcode,"Grand Total") as empcode, COALESCE(b.empname,"Sub Total") as empname, COALESCE(a.attd_date,"Sub Total") as attd_date,  MONTH(a.attd_date) as month, YEAR(a.attd_date) as year, c.upah, a.overtime, a.attd_code,
round((a.overtime * 1 / 173 * c.upah)) as lembur
, coalesce(ROUND((1/173*c.upah) * SUM(a.overtime)),0) as total,a.remarks, b.departement,b.position, b.employeetype' ) ; $ this -> db -> from ( 'gang_activity a, emp_master b, hr_hist_status c' ) ; $ this -> db -> where ( 'a.empcode = b.empcode
and a.empcode = c.empcode' ) ; $ this -> db -> group_by ( " a.empcode, a.attd_date " ) ; $ 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
//add custom filter here //$this->db->where('a.attd_date', $this->input->post('filter_year')); /*if($this->input->post('filter_month'))
        {
            $this->db->where('MONTH(a.attd_date)', $this->input->post('filter_month'));
        }*/ /*$this->db->select('gang_activity.empcode, emp_master.empname, master_dept.dept_desc, gang_activity.attd_date, master_position.position_desc, gang_activity.overtime, gang_activity.remarks, gang_activity.id_emp_act_sk,gang_activity.attd_code');
        $this->db->from($this->table);
        $this->db->join('emp_master','gang_activity.empcode = emp_master.empcode');
        $this->db->join('master_dept','emp_master.departement = master_dept.departement');
        $this->db->join('master_position','gang_activity.attd_code = master_position.id_position','left');*/ //$this->db->where_in('site_id','2102','2202'); /* $query = $this->db->query('
select COALESCE(a.empcode,"Grand Total") as empcode, COALESCE(b.empname,"Sub Total") as empname, COALESCE(a.attd_date,"Sub Total") as attd_date,c.upah, a.overtime, a.attd_code,
round((a.overtime * 1 / 173 * c.upah)) as lembur
, coalesce(ROUND((1/173*c.upah) * SUM(a.overtime)),0) as total,a.remarks
from gang_activity a, emp_master b, hr_hist_status c
where a.empcode = b.empcode
and a.empcode = c.empcode
and a.attd_date BETWEEN "2018-01-01" AND "2018-01-30" 
GROUP BY  a.attd_date
WITH ROLLUP');
        $this->db->select('empcode');
        $this->df->from($query);*/ // 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