FloatBackOfffice/application/models/ParsedEmailItem_model.php

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

use Carbon\Carbon;

class ParsedEmailItem_model extends CI_Model
{

    public $id;
    public $travel_date;
    public $duration;
    public $cost_raw;
    public $trackedemail_item_id;
    public $cost;
    public $updated;
    public $distance;
    public $transport_provider_id;
    public $scheduled;
    public $travel_date_end;
    public $dup_id;
    public $location_start_id;
    public $location_end_id;
    public $private;

    public function __contstruct()
    {
        parrent::__contstruct();
    }

    public function ParsedEmailItem($item)
    {
        return [
            'id' => $item->id,
            'travel_date' => Carbon::parse($item->travel_date)->format('Y-m-d'),
            'travel_date_end' => Carbon::parse($item->travel_date_end)->format('Y-m-d'),
            'location_start_id' => $item->location_start_id,
            'location_end_id' => $item->location_end_id,
            'distance' => $item->distance,
            'cost' => $item->cost,
            'duration' => $item->duration,
            'cost_raw' => $item->cost_raw,
            'trackedemail_item_id' => $item->trackedemail_item_id,
            'transport_provider_id' => $item->transport_provider_id,
        ];
    }

    public function getParsedEmailItemList($params = [])
    {
        $res = SQEAPI::get('trip/api/trip/all', $params);

        if (isset($res['error'])) {
            return ['success' => false, 'error' => $res['error']];
        }

        $data = array();
        foreach ($res['data'] as $trip) {
            $data[] = $this->ParsedEmailItem($trip);
        }

        $itemTotal = isset($res['total']) ? $res['total'] : 0;
        $page = isset($res['page']) ? $res['page'] : 0;

        return [
            'list' => $data,
            'itemTotal' => $itemTotal,
            'page' => $page,
        ];
    }

    public function getParsedEmailItem($id, $params = [])
    {
        $res = SQEAPI::get('trip/api/trip/' . $id, $params);

        $data = $this->ParsedEmailItem($res['data']);
        return $data;
    }

    public function createParsedEmailItem($inputData)
    {
        return SQEAPI::post('trip/api/trip/new', $inputData);
    }

    public function updateParsedEmailItem($id, $inputData)
    {
        // convert input array to query string format
        $data = http_build_query($inputData);
        
        return SQEAPI::put('trip/api/trip/' . $id, $data);
    }

    public function deleteParsedEmailItem($id)
    {
        return SQEAPI::delete("trip/api/trip/$id");
    }

    public function getSureReportQuery($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(p.travel_date, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "' 
                     AND to_char(p.travel_date, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "'";
            }
        
        if (!empty($params['duration_from'])) {
            $whereQuery .= " AND p.duration >= " . pg_escape_string($params['duration_from']) . " ";
        }
        
        if (!empty($params['duration_to'])) {
            $whereQuery .= " AND p.duration <= " . pg_escape_string($params['duration_to']) . " ";
        }
        
        if (!empty($params['distance_from'])) {
            $whereQuery .= " AND p.distance >= " . pg_escape_string($params['distance_from']) . " ";
        }
        
        if (!empty($params['distance_to'])) {
            $whereQuery .= " AND p.distance <= " . pg_escape_string($params['distance_to']) . " ";
        }

        if (!empty($params['cost_from'])) {
            $whereQuery .= " AND p.cost >= " . pg_escape_string($params['cost_from']) . " ";
        }
        
        if (!empty($params['cost_to'])) {
            $whereQuery .= " AND p.cost <= " . pg_escape_string($params['cost_to']) . " ";
        }

        if (!empty($params['email'])) {
            // email format (comma separated): tet1@gmail.com, test2@gmail
            $emails = $params['email'];
            
            // remove space
            $emails = preg_replace("/\s+/", '', $emails);

            // remove empty value
            $emailArray = explode(',', $emails);
            $emailArray = array_filter($emailArray, function($v, $k) {
                return !empty($v);
            }, ARRAY_FILTER_USE_BOTH);

            //convert to format: ('tet1@gmail.com', 'test2@gmail')
            $emails = implode("','", $emailArray);
            $emails = "('" . $emails . "')";

            $whereQuery .= " AND m.email IN " . $emails . " ";
        }

        $query = "
            SELECT
                p.id,
                m.id AS member_id,
                m.email,
                p.travel_date,
                p.duration,
                p.cost_raw,
                p.trackedemail_item_id,
                p.cost,
                p.updated,
                p.distance,
                CASE WHEN e.id IS NULL THEN
                    'No'
                ELSE
                    'Yes'
                END AS surge_price,
                CASE WHEN (f.id IS NOT NULL
                    AND f.cost > f.average) THEN
                    ROUND(f.average / f.cost, 2)
                ELSE
                    0
                END AS cheaper_alternative,
                '<a class=\"showTripInsights\" onclick=\"showTripInsights('||t.member_id||','||p.trackedemail_item_id||', this);\" href=\"javascript:void(0);\">Insights</a>' AS Insights
            FROM
                parsedemail_item p
                LEFT JOIN trackedemail_item t ON t.id = p.trackedemail_item_id
                LEFT JOIN trip_surge_price e ON (e.data_source_id = p.id
                        AND e.data_source = 1)
                LEFT JOIN trip_price_comparison f ON (f.data_source_id = p.id
                        AND e.data_source = 1)
                LEFT JOIN members m ON m.id = t.member_id
            WHERE
                e.id IS NOT NULL " . $whereQuery . "
            ORDER BY
                t.id DESC
        ";

        return $query;

    }
}