Card - Model

-- Model --

<?php
    var $column = array(
        'modified_at', 'no_nota_pelunasan', 'nota_spk', 'nama_customer', 'no_hp', 'plat_nomor', 'jenis_mobil', 'nama_produk', 'validUntil', 'maintenance_notes'
    );

private function generate_query_by_nota_pelunasan($count = false)
    {
        $this->db->select("
            pelunasan.id_pelunasan, spk.id_spk, spk_detail.produk_id,
            pelunasan.modified_at, pelunasan.no_nota_pelunasan, spk.nota_spk, spk.nama_customer, spk.no_hp, spk.plat_nomor, spk.jenis_mobil,
            produk.nama_produk,
            spk_detail.spk_valid_until AS validUntil,
            spk_detail.spk_maintenance_notes AS maintenance_notes
        ");

        $this->db->from('spk');

        $this->db->join("spk_detail", "spk.id_spk = spk_detail.spk_id");
        $this->db->join("produk", "spk_detail.produk_id = produk.id_produk");
        $this->db->join("pelunasan", "spk.id_spk = pelunasan.pelunasan_nota_spk", 'left');

        $this->db->where('pelunasan.is_delete', 0);
        $this->db->where('spk.is_delete', 0);

        $i = 0;

        foreach ($this->column as $item) {
            if ($item == 'validUntil') {
                $item = 'spk_valid_until';
            }
            if ($item == 'maintenance_notes') {
                $item = 'spk_maintenance_notes';
            }
            if ($_POST['search']['value']) // if datatable send POST for search
            {
                if (!$count) {
                    if ($i === 0) // first loop
                    {
                        $this->db->group_start();
                        $this->db->like($item, $_POST['search']['value']);
                    } else {
                        $this->db->or_like($item, $_POST['search']['value']);
                    }

                    if (count($this->column) - 1 == $i)
                        $this->db->group_end(); //close bracket
                }
            }
            $column[$i] = $item; // set column array variable to order processing
            $i++;
        }

        $this->db->get();
        $subQuery1 = $this->db->last_query();

        $this->db->select('
            pelunasan.id_pelunasan, spk.id_spk, pelunasan_detail.produk_id,
            pelunasan.modified_at, pelunasan.no_nota_pelunasan, spk.nota_spk, spk.nama_customer, spk.no_hp, spk.plat_nomor, spk.jenis_mobil,
            produk.nama_produk,
            pelunasan_detail.pelunasan_valid_until AS validUntil,
            pelunasan_detail.pelunasan_maintenance_notes AS maintenance_notes
        ');

        $this->db->from('pelunasan');

        $this->db->join("pelunasan_detail", "pelunasan.id_pelunasan = pelunasan_detail.pelunasan_id");
        $this->db->join("produk", "pelunasan_detail.produk_id = produk.id_produk");
        $this->db->join("spk", "pelunasan.pelunasan_nota_spk = spk.id_spk");

        $this->db->where('pelunasan.is_delete', 0);
        $this->db->where('spk.is_delete', 0);

        $i = 0;

        foreach ($this->column as $item) {
            if ($item == 'validUntil') {
                $item = 'pelunasan_valid_until';
            }
            if ($item == 'maintenance_notes') {
                $item = 'pelunasan_maintenance_notes';
            }
            if ($_POST['search']['value']) // if datatable send POST for search
            {
                if (!$count) {
                    if ($i === 0) // first loop
                    {
                        $this->db->group_start();
                        $this->db->like($item, $_POST['search']['value']);
                    } else {
                        $this->db->or_like($item, $_POST['search']['value']);
                    }

                    if (count($this->column) - 1 == $i)
                        $this->db->group_end(); //close bracket
                }
            }
            $column[$i] = $item; // set column array variable to order processing
            $i++;
        }

        $this->db->get();
        $subQuery2 = $this->db->last_query();

        $order = " ";

        if (isset($_POST['order'])) {
            if ($column[$_POST['order']['0']['column']] == 'pelunasan_maintenance_notes') {
                $column[$_POST['order']['0']['column']] = "maintenance_notes";
            }
            if ($column[$_POST['order']['0']['column']] == 'pelunasan_valid_until') {
                $column[$_POST['order']['0']['column']] = 'validUntil';
            }
            if ($column[$_POST['order']['0']['column']] == 'spk_maintenance_notes') {
                $column[$_POST['order']['0']['column']] = "maintenance_notes";
            }
            if ($column[$_POST['order']['0']['column']] == 'spk_valid_until') {
                $column[$_POST['order']['0']['column']] = 'validUntil';
            }
            $order = " order by " . $column[$_POST['order']['0']['column']] . " " . $_POST['order']['0']['dir'];
        }

        return $subQuery1 . " union all " . $subQuery2 . $order;
    }

    public function get_datatables_by_nota_pelunasan()
    {
        $query = $this->generate_query_by_nota_pelunasan();

        $limit = "";
        if (isset($_POST['length']) && $_POST['length'] != -1)
            $limit = " limit " . $_POST['start'] . "," . $_POST['length'];
        return $this->db->query($query . " " . $limit)->result();
    }

    public function count_filtered_by_nota_pelunasan()
    {
        $query_tmp = $this->generate_query_by_nota_pelunasan();
        $query = $this->db->query($query_tmp);
        return $query->num_rows();
    }

?>