Loading Retrieving data as well as the search in using datatable

39 Views Asked by At

Model:

public function get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir)
    {
        $this->db->select('uni.inv_id, batch.batch_id, uni.item_code, des.design_name, cat.cat_type, supply.item_size, des.gender_status, uni.quantity, batch.b_remain_qty, batch.batch_code');
        $this->db->from('ebs.uni_inventory uni');
        $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
        $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
        $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
        $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
        $this->db->where('uni.status', 'active');
        $this->db->where('uni.group_id', $group_id);
        $this->db->order_by($order_by, $order_dir);
        $this->db->limit($length, $start);

        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('uni.item_code', $search);
            $this->db->or_like('des.design_name', $search);
            $this->db->or_like('cat.cat_type', $search);
            $this->db->or_like('supply.item_size', $search);
            $this->db->or_like('des.gender_status', $search);
            $this->db->or_like('uni.quantity', $search);
            $this->db->or_like('batch.b_remain_qty', $search);
            $this->db->or_like('batch.batch_code', $search);
            $this->db->group_end();
        }
        $result = $this->db->get();
        return $result->result_array();
    }

    public function getTotalRecords($group_id, $search)
    {
        $this->db->select('*');
        $this->db->from('ebs.uni_inventory uni');
        $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
        $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
        $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
        $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
        $this->db->where('uni.status', 'active');
        $this->db->where('uni.group_id', $group_id);

        // $this->db->where('uni.quantity != batch.b_remain_qty');

        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('uni.item_code', $search);
            $this->db->or_like('des.design_name', $search);
            $this->db->or_like('cat.cat_type', $search);
            $this->db->or_like('supply.item_size', $search);
            $this->db->or_like('des.gender_status', $search);
            $this->db->or_like('uni.quantity', $search);
            $this->db->or_like('batch.b_remain_qty', $search);
            $this->db->or_like('batch.batch_code', $search);
            $this->db->group_end();
        }
        // return $this->db->get()->row()->total_records;
        $query = $this->db->get();
        return count($query->result_array());

    }...............
}

Controller:

public function get_inventory_data()
{
    $group_id   = $this->input->post('group_id');
    $start      = $this->input->post('start');
    $length     = $this->input->post('length');
    $search     = $this->input->post('search')['value'];
    // $column     = $this->input->post('order')[0]['column'];
    // $order_dir  = $this->input->post('order')[0]['dir'];

    // $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];
    // $order_by = $columns[$column];
    $order_by   = $this->input->post('columns')[$this->input->post('order')[0]['column']]['data'];
    $order_dir  = $this->input->post('order')[0]['dir'];

    $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];

    if (empty($order_by) || !in_array($order_by, $columns)) {
        $order_by = $columns[0];
    }

    $data = $this->AdjustmentModel->get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir);

    $totalRecords = $this->AdjustmentModel->getTotalRecords($group_id, $search);

    $data_output = array(
        'draw' => intval($this->input->post('draw')),
        'recordsTotal' => intval($totalRecords),
        'recordsFiltered' => intval($totalRecords),
        'data' => $data,
    );

    echo json_encode($data_output);
}

I Want to get the data fast and load data when search fast.......helpp

0

There are 0 best solutions below