FloatBackOfffice/application/models/Report_model.php

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

class Report_model extends CI_Model
{
    private $read_replica;

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

    public function getPriceComparisonTrend(array $params)
    {
        $errors = [];
        $whereQuery = ' ';

        if (!empty($params['start_date']) && !empty($params['end_date'])) {
            $whereQuery .= " AND to_char(a.travel_date, 'YYYY-MM-DD') >= '" . pg_escape_string($params['start_date']) . "' 
                AND to_char(a.travel_date, 'YYYY-MM-DD') <= '" . pg_escape_string($params['end_date']) . "' ";
        } else {
            $errors[] = 'Start date, end date are required.';
        }

        if (!empty($params['transport_providers'])) {
            $transport_providers = '(' . implode(',', $params['transport_providers']) . ')';
            $whereQuery .= " AND CASE WHEN a.data_source = 1 THEN
                                    parsedemail_item.transport_provider_id IN $transport_providers
                                WHEN a.data_source = 2 THEN
                                    quotes.transport_provider_id IN $transport_providers
                                END ";
        } else {
            $errors[] = 'Transport provider is required.';
        }

        if (!empty($errors)) {
            return [
                'success' => false,
                'message' => $errors[0]
            ];
        }

        $query = "
            SELECT
                *, 
                count(*) as count
            FROM (
                SELECT
                    a.cost,
                    to_char(a.travel_date, 'YYYY-MM-DD') AS travel_date,
                    CASE WHEN a.data_source = 1 THEN
                        parsedemail_item.transport_provider_id
                    WHEN a.data_source = 2 THEN
                        quotes.transport_provider_id
                    END AS transport_provider_id
                FROM
                    union_trip_and_quote_view_table AS a
                LEFT JOIN parsedemail_item ON parsedemail_item.id = a.data_source_id
                    AND a.data_source = 1
                LEFT JOIN quotes ON quotes.id = a.data_source_id
                    AND a.data_source = 2
            WHERE
                a.travel_date IS NOT NULL  
                " . $whereQuery . "  
                ) AS sub
            GROUP BY
                transport_provider_id,
                travel_date,
                cost
            ORDER BY
                travel_date ASC
        ";

        $query = $this->read_replica->query($query);

        return [
            'success' => true,
            'data' => $query->result_array()
        ];
    }

    public function area_to_area($area1, $area2, $date1, $date2, $t, $c = '#FF0000')
    {
        global $pgconn;
        $data = [];
        $q = "SELECT * FROM geofence_area WHERE id=${area1}";
        $query = $this->read_replica->query($q);
        $area1i = $query->result_array();
        $boundaries = json_decode($area1i[0]["boundaries"], true);
        $polygonCoords = [];

        foreach ($boundaries["polygon"] as $coord) {
            $polygonCoords[] = "$coord[0] $coord[1]";
        }
        $polygonCoordsString1 = implode(",", $polygonCoords);

        $q = "SELECT * FROM geofence_area WHERE id=${area2}";
        $query = $this->read_replica->query($q);
        $area2i = $query->result_array();

        $boundaries = json_decode($area2i[0]["boundaries"], true);
        $polygonCoords = [];
        foreach ($boundaries["polygon"] as $coord) {
            $polygonCoords[] = "$coord[0] $coord[1]";
        }
        $polygonCoordsString2 = implode(",", $polygonCoords);

        $q = "SELECT a.travel_date,a.cost";
        $q .= ",b.latitude AS location_start_lat,b.longitude AS location_start_lng";
        $q .= ",c.latitude AS location_end_lat,c.longitude AS location_end_lng";
        $q .= " FROM quotes a, address b, address c";
        $q .= " WHERE a.cost>0 AND a.transport_provider_id=${t}";
        $q .= " AND b.id=a.location_start_id AND c.id=a.location_end_id";
        $q .= " AND a.travel_date>'${date1} 00:00' AND a.travel_date<'${date2} 23:59'";
        $q .= " AND ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString1}))', 4326), b.geometry::geometry)";
        $q .= " AND ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString2}))', 4326), c.geometry::geometry)";
        $q .= " ORDER BY a.travel_date";


        $query = $this->read_replica->query($q);
        foreach ($query->result_array() as $f) {
            $f['origin'] = $area1i[0]['name'];
            $f['destination'] = $area2i[0]['name'];
            $f['c'] = $c;
            $f['time'] = strtotime($f['travel_date']);
            $data[] = $f;
        }
        return [$data, $area1i[0]["name"] . " to " . $area2i[0]["name"], $boundaries["polygon"]];
    }

    public function getEmailAndBankConnectionReportSummary()
    {
        $sql = "SELECT COUNT(*) AS total_account,
        CASE WHEN members.country IS NULL OR members.country='' THEN 'unknown' ELSE members.country END AS location
        FROM members
        WHERE members.id>0 AND members.email != '' AND members.email IS NOT NULL
        GROUP BY CASE WHEN members.country IS NULL OR members.country='' THEN 'unknown' ELSE members.country END
        ORDER BY total_account DESC";
        $countries = $this->read_replica->query($sql)->result_array();
        foreach ($countries as &$country) {
            // get Bank Connection Report
            if ($country['location'] !== 'unknown') {
                $bank_where = "members.country='" . pg_escape_string($country['location']) . "'";
                $email_where = "mb.country='" . pg_escape_string($country['location']) . "'";
            } else {
                $bank_where = "(members.country IS NULL OR members.country='')";
                $email_where = "(mb.country IS NULL OR mb.country='')";
            }
            // active bank connection
            $sql = "SELECT count(*) as active_bank_connection FROM members
            WHERE id>0 AND email!='' AND email IS NOT NULL 
            AND EXISTS(SELECT * from members_bank_accounts where bank_login_status = 'active' and member_id = members.id) and $bank_where";
            $country['active_bank_connection'] = $this->read_replica->query($sql)->row()->active_bank_connection;

            // inactive bank connection
            $sql = "SELECT count(*) as inactive_bank_connection FROM members
            WHERE id>0 AND email!='' AND email IS NOT NULL 
            AND NOT EXISTS (SELECT * from members_bank_accounts where bank_login_status = 'active' and member_id = members.id) 
            AND EXISTS(SELECT * from members_bank_accounts where bank_login_status = 'inactive' and member_id = members.id) 
            and $bank_where";

            $country['inactive_bank_connection'] = $this->read_replica->query($sql)->row()->inactive_bank_connection;

            // active email connection
            $sql = "SELECT COUNT (*) as total FROM members AS mb 
            WHERE EXISTS (SELECT*FROM oauth2_tokens WHERE member_id=mb.ID AND expires_in>=CURRENT_TIMESTAMP AND email !='' AND email IS NOT NULL) 
            AND mb.email !='' AND mb.email IS NOT NULL AND mb.ID> 0 AND $email_where;";
            $country['active_email_connection'] = $this->read_replica->query($sql)->row()->total;

            //inactive email connection
            $country['inactive_email_connection'] = (int)$country['total_account'] - (int)$country['active_email_connection'];
            $country['no_bank_connected'] = (int)$country['total_account'] - (int)$country['active_bank_connection'] - (int)$country['inactive_bank_connection'];
        }
        return $countries;
    }

    public function getEmailBankConnectionReportDatatables($postData, $mode = 'search')
    {
        ## Save param for CSV
        $this->load->library('session');
        $this->session->set_userdata("EBCRR_PARAM", $postData);
        ## Read value
        $from = DateTime::createFromFormat('m/d/Y', '1/1/1970');
        $to = (new DateTime());
        $date_range = $postData['date_range'] ?? '';
        if (!empty($date_range)) {
            $date_range_part = array_map('trim', explode('-', $date_range));
            $from = DateTime::createFromFormat('m/d/Y', $date_range_part[0]);
            $to = DateTime::createFromFormat('m/d/Y', $date_range_part[1]);
        }
        $draw = $postData['draw'] ?? 0;
        $start = $postData['start'];
        $rowperpage = $postData['length']; // Rows display per page
        $columnIndex = $postData['order'][0]['column'] ?? 0; // Column index
        $columnName = $postData['columns'][$columnIndex]['data'] ?? ''; // Column name
        $columnSortOrder = $postData['order'][0]['dir']; // asc or desc

        $searchValue = $postData['email_search']??'';//['value'] ?? ''; // Search value
        $where = " members.id>0 AND members.email!='' AND members.email IS NOT NULL ";
        $where .= " AND date(members.added) >='" . $from->format('Y-m-d') . "' and date(members.added)<='" . $to->format('Y-m-d') . "' ";
        if (!empty($postData['country_filter'])) {
            if ($postData['country_filter'] == 'unknown') {
                $where .= " AND (members.country = '' or members.country IS NULL) ";
            } else {
                $where .= " AND members.country = '" . pg_escape_string($postData['country_filter']) . "' ";
            }
        }
        // bank connection filter
        if (!empty($postData['bank_connection_status'])) {
            if ($postData['bank_connection_status'] == 'active') {
                $where .= " AND EXISTS (SELECT * FROM members_bank_accounts WHERE member_id = members.id AND bank_login_status = 'active')";
            } else if ($postData['bank_connection_status'] == 'inactive') {
                $where .= " AND NOT EXISTS (SELECT * FROM members_bank_accounts WHERE member_id = members.id AND bank_login_status = 'active') ";
                $where .= " AND EXISTS (SELECT * FROM members_bank_accounts WHERE member_id = members.id AND bank_login_status = 'inactive') ";
            }
        }
        // email connection filter
        if (!empty($postData['email_connection_status'])) {
            if ($postData['email_connection_status'] == 'active') {
                $where .= " AND EXISTS (SELECT ot.email FROM oauth2_tokens AS ot WHERE ot.member_id = members.id AND ot.expires_in IS NOT NULL AND ot.expires_in>=CURRENT_TIMESTAMP 
                            ORDER BY ot.expires_in DESC LIMIT 1 )";
            } else if($postData['email_connection_status'] == 'inactive'){
                $where .= " AND NOT EXISTS (SELECT ot.email FROM oauth2_tokens AS ot WHERE ot.member_id = members.id AND ot.expires_in IS NOT NULL AND ot.expires_in>=CURRENT_TIMESTAMP 
                            ORDER BY ot.expires_in DESC LIMIT 1 )";
            }
        }
        if (!empty($searchValue)) {
            $searchStr = pg_escape_string($searchValue);
            $where .= " AND members.email ILIKE '%" . $searchStr . "%' ";
        }

        $map_order_by = [
            'sign_up_date' => 'members.added',
            'location' => 'members.country',
            'user_id' => 'members.id',
        ];
        if (isset($map_order_by[$columnName])) {
            $order_by = ' ORDER BY ' . $map_order_by[$columnName] . ' ' . $columnSortOrder;
        } else {
            $order_by = ' ORDER BY ' . $map_order_by['sign_up_date'] . ' DESC';
        }

        $limit = $mode === 'search' ? " LIMIT $rowperpage OFFSET $start" : '';

        $mysql = "SELECT
                    to_char(members.added, 'MM/DD/YYYY HH24:MI:SS') AS sign_up_date,
                    (CASE WHEN members.country IS NULL THEN 'unknown' ELSE members.country END) AS location,
                    members.id AS user_id,
                    members.email AS user_email,
                    CASE WHEN (SELECT COUNT ( * ) FROM members_bank_accounts WHERE member_id = members.id AND bank_login_status = 'active')>0 THEN 'active' ELSE 'inactive' END AS bank_connection,
                   	CASE WHEN (
                            SELECT ot.email FROM oauth2_tokens AS ot WHERE ot.member_id = members.id AND ot.expires_in IS NOT NULL AND ot.expires_in>=CURRENT_TIMESTAMP 
                            ORDER BY ot.expires_in DESC LIMIT 1 
                    ) IS NOT NULL THEN 'active' ELSE 'inactive' END  AS email_connection
                    FROM members
                    WHERE $where
                    $order_by $limit";

        # Response Search or CSV
        if ($mode === 'export_csv') {
            $report = $this->read_replica->query($mysql);
            $this->exportToCSV($report, 'email-bank-connection-report-' . date("Y-m-d") . '.csv');
            exit;
        }
        $report = $this->read_replica->query($mysql)->result_array();
        $mysql_count = "SELECT COUNT(*) FROM members WHERE $where;";
        $totalRecords = $this->read_replica->query($mysql_count)->row_array()['count'];
        $totalRecordwithFilter = $totalRecords;
        $response = array(
            "draw" => intval($draw),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalRecordwithFilter,
            "aaData" => $report
        );
        return $response;
    }

    public function exportToCSV($data, $file_name)
    {
        set_time_limit(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$file_name);
        header('Cache-Control: max-age=0');
        if (ob_get_contents())
            ob_end_clean();
        $fp = fopen('php://output', 'w');
        $i = 0;
        while ($row = $data->unbuffered_row()) {
            // header
            if ($i == 0) {
                fputcsv($fp, array_keys((array)$row));
            }
            // records
            fputcsv($fp, (array)$row);
            $i++;
        }
        fclose($fp);
        exit;
    }

    public function getBankAccountDetail($member_id = 0)
    {
        $sql = "SELECT * FROM members_bank_accounts WHERE member_id= " . $member_id;
        $result = $this->read_replica->query($sql)->result_array();
        return $result;
    }
}