FloatBackOfffice/application/models/Receipt_advice_model.php

<?php if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Receipt_advice_model extends CI_Model
{

    private $read_replica;

    public function __construct()
    {
        parent::__construct();
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
    }

    public function getAdviceQuery($params = [])
    {
        $whereQuery = '';

        if (!empty($params['travel_date'])) {
            // travel_date format: 2020-02-03 - 2020-03-01
            $fromToArray = explode(' - ', $params['travel_date']);
            $fromDate = $fromToArray[0];
            $toDate = $fromToArray[1];

            $whereQuery .= " AND (
                (to_char(a.travel_date, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "' 
                    AND 
                to_char(a.travel_date, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "'
                )
                OR
                (to_char(a.updated, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "' 
                    AND 
                to_char(a.updated, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "'
                )
            ) ";
        }

        if (!empty($params['duration_from'])) {
            $whereQuery .= " AND a.duration >= " . pg_escape_string($params['duration_from']) . " ";
        }

        if (!empty($params['duration_to'])) {
            $whereQuery .= " AND a.duration <= " . pg_escape_string($params['duration_to']) . " ";
        }

        if (!empty($params['distance_from'])) {
            $whereQuery .= " AND a.distance >= " . pg_escape_string($params['distance_from']) . " ";
        }

        if (!empty($params['distance_to'])) {
            $whereQuery .= " AND a.distance <= " . pg_escape_string($params['distance_to']) . " ";
        }

        if (!empty($params['location_start_id'])) {
            $whereQuery .= " AND a.location_start_id = " . pg_escape_string($params['location_start_id']) . " ";
        }

        if (!empty($params['location_end_id'])) {
            $whereQuery .= " AND a.location_end_id = " . pg_escape_string($params['location_end_id']) . " ";
        }

        $query = "
                SELECT a.id,a.travel_date||'<br/>'||a.updated As \"Date/Updated\",a.duration,a.distance,
                b.address||'<br/>'||c.address as \"From and To\", 
                '<input type=\"button\" class=\"btn btn-info btn-xs\" onclick=\"return viewAdvice('||a.id||');\" value=\"View\" />' AS \"View\"
            FROM parsedemail_item a 
            LEFT JOIN address b ON b.id = a.location_start_id 
            LEFT JOIN address c ON c.id = a.location_end_id
            WHERE a.transport_provider_id>0 AND a.dup_id IS NULL AND a.private='f' " . $whereQuery . " ORDER BY a.travel_date DESC
        ";

        return $query;
    }

    public function get_query_parsed_email_quote_records($filters, $count_record = true)
    {
        $combo_array = [];

        $numeric_array = [
            'duration_from' => 'a.duration >=',
            'duration_to' => 'a.duration <=',
            'distance_from' => 'a.distance >=',
            'distance_to' => 'a.distance <=',
            'location_start_id' => 'a.location_start_id',
            'location_end_id' => 'a.location_end_id',
        ];

        $string_array = [];

        $boolean_array = [];

        if ($count_record) {

            $this->read_replica->select([
                'count(*) AS all_count'
            ]);
        } else {

            $this->read_replica->select([
                'a.travel_date',
                'a.duration',
                'a.cost_raw',
                'a.cost',
                'a.updated',
                'a.distance',
                'a.transport_provider_id',
                'a.travel_date_end',
                'a.location_start_id',
                'a.location_end_id',
                'b.address AS location_start_address',
                'b.latitude AS location_start_lat',
                'b.longitude AS location_start_lng',
                'b.postal AS location_start_postal',
                'b.country AS location_start_country',
                'b.description AS location_start_description',
                'd.timezone AS location_start_tz',
                'c.address AS location_end_address',
                'c.latitude AS location_end_lat',
                'c.longitude AS location_end_lng',
                'c.postal AS location_end_postal',
                'c.country AS location_end_country',
                'c.description AS location_end_description',
                'e.timezone AS location_end_tz'
            ]);
        }

        $this->read_replica->from('parsedemail_item a');
        $this->read_replica->join('address b', 'b.id=a.location_start_id', 'LEFT');
        $this->read_replica->join('address c', 'c.id=a.location_end_id', 'LEFT');
        $this->read_replica->join('address_timezone d', 'b.timezone=d.id', 'LEFT');
        $this->read_replica->join('address_timezone e', 'c.timezone=e.id', 'LEFT');

        $this->read_replica->where('a.transport_provider_id>0');
        $this->read_replica->where('a.dup_id IS NULL');
        $this->read_replica->where('a.private', 'f');

        foreach ($filters as $key => $val) {

            if ($key === 'travel_date') {

                $fromToArray = explode(' - ', $val);
                $this->read_replica->group_start()
                                ->group_start()
                                    ->where('DATE(a.travel_date) >=', $fromToArray[0]) // start date
                                    ->where('DATE(a.travel_date) <=', $fromToArray[1]) // end date
                                ->group_end()
                                ->or_group_start()
                                    ->where('DATE(a.updated) >=', $fromToArray[0]) // start date
                                    ->where('DATE(a.updated) <=', $fromToArray[1]) // end date
                                ->group_end()
                            ->group_end();
            } else if ($key === 'location_start_ids') {

                $this->read_replica->where_in('a.location_start_id', $val);
            } else if ($key === 'location_end_ids') {

                $this->read_replica->where_in('a.location_end_id', $val);
            } else if (array_key_exists($key, $combo_array)) {

                $this->read_replica->where($combo_array[$key], $val);
            } else if (array_key_exists($key, $numeric_array)) {

                $this->read_replica->where($numeric_array[$key], $val);
            } else if (array_key_exists($key, $string_array)) {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
            } else if (array_key_exists($key, $boolean_array)) {

                $this->read_replica->where($boolean_array[$key], $val);
            }
        }

        if ($count_record === false) {

            $this->read_replica->order_by('a.travel_date DESC');
        }

        return $this->read_replica->get_compiled_select();
    }

    public function count_parsedemail_item_record()
    {
        return $this->read_replica->select(['count(id) AS all_count'])
            ->from('parsedemail_item')
            ->get()
            ->result_array()[0]['all_count'];
    }
}