FloatBackOfffice/application/models/Bkoadmin_model.php

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

class Bkoadmin_model extends CI_Model {

    private $read_replica;

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

    public function get_point_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {
        $combo_array = [
            'activated' => 'activated'
        ];

        $numeric_array = [
            'from_value' => 'value >=',
            'to_value' => 'value <=',
        ];

        $string_array = [
            'key' => 'point_key',
            'name' => 'name'
        ];

        $this->read_replica->select([
            'id',
            'lorder',
            'point_key',
            'name',
            '\'<input type="text" id="points\'||id||\'" class="form-control" value="\'||value||\'">\' AS pvalue',
            '\'<button ion-button class="btn-info btn-sx" onclick="return UpdatePoints(this,\'||id||\')">Update</button>\' AS updt',
            'status',
            'added',
            '(CASE 
                WHEN activated = 1 THEN \'Activated\'
                ELSE \'Not Activated\' END
            ) AS activated_text',
            'activated',
        ]);

        $this->read_replica->from('points_settings');

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

            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 (strpos($key, 'from') !== FALSE) {

                $this->read_replica->where('DATE(added) >=', $val);

            } else if (strpos($key, 'to') !== FALSE) {

                $this->read_replica->where('DATE(added) <=', $val);

            } else {

                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));

            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('id');
        }

        return $this->read_replica->get()->result_array();
    }

    public function getAppSettingsQuery($params = [])
    {
        $whereQuery = ' WHERE 1 = 1 ';

        if (!empty($params['key'])) {
            $whereQuery .= " AND setting_key  ILIKE '%" . pg_escape_string(trim($params['key'])) . "%' ";
        }

        if (!empty($params['name'])) {
            $whereQuery .= " AND description  ILIKE '%" . pg_escape_string(trim($params['name'])) . "%' ";
        }

        $query = "
        SELECT
            lorder,
            setting_key,
            description,
            " . " '<input type=\"text\" class=\"form-control\" name=\"T' || setting_key || '\" id=\"T' || setting_key || '\" value=\"' || value || '\">' AS pvalue,
            " . " '<button ion-button class=\"btn-info btn-sx\" onclick=\"updateSettings(' || id || ',''' || setting_key || ''');\">Update</button><div id=\"set_form' || setting_key || '\"><div>' AS updt
        FROM app_settings   
        " . $whereQuery . "   
        ORDER BY id
        ";

        return $query;
    }

    public function getGlobalSettingsQuery($params = [])
    {
        $whereQuery = ' WHERE 1 = 1 ';

        if (!empty($params['key'])) {
            $whereQuery .= " AND key  ILIKE '%" . pg_escape_string(trim($params['key'])) . "%' ";
        }

        if (!empty($params['description'])) {
            $whereQuery .= " AND description  ILIKE '%" . pg_escape_string(trim($params['description'])) . "%' ";
        }
        if (isset($params['status']) && $params['status'] != -1) {
            $whereQuery .= " AND status  = " . pg_escape_string($params['status']) . " ";
        }

        $query = "
            SELECT * 
            FROM 
                global_settings 
            " . $whereQuery . "   
            ORDER BY id
        ";

        return $query;
    }

    public function getTransportProviderQuery($params = [])
    {
        $whereQuery = ' WHERE 1 = 1 ';

        if (!empty($params['name'])) {
            $whereQuery .= " AND name  ILIKE '%" . pg_escape_string(trim($params['name'])) . "%' ";
        }

        $query = "
        SELECT
            id,
            concat('<a href=\"/bkoadmin/transportApps?id=', id, '\">', name, '</a>') AS Name,
            concat('<b>Client:</b> ', client, '<br/><b>Token:</b> ', token, '<br/><b>Code:</b> ', code, '<br/><b>Access Token:</b> ', access_token, '<br/><b>Name Alias:</b> ', name_alias) AS Credentials,
            (
                CASE active
                WHEN 1 THEN
                    'Yes'
                ELSE
                    'No'
                END) AS Active,
            '<input type=\"button\" class=\"btn btn-info btn-xs\" onclick=\"return EditTransportProvider(' || id || ');\" value=\"Edit\" />' AS action
        FROM
            transport_providers
        " . $whereQuery . " 
        ORDER BY
            name
        ";

        return $query;
    }

    public function getTransportProviderAppQuery($params = [])
    {
        $query = "
        SELECT
            b.name,
            a.country,
            a.ios_app_id,
            a.android_app_id,
            concat('<button type=\"button\" class=\"btn btn-info btn-xs\" onclick=\"document.location=''/bkoadmin/transportApps?id=', b.id, '&app_id=', a.id, ''';return false;\">Edit</button>') AS Action
        FROM
            transport_provider_apps a,
            transport_providers b
        WHERE
            b.id = a.transport_provider_id
        ORDER BY
            b.name,
            a.country
        ";

        return $query;
    }

    public function insertOrUpdateAdminQuery($data)
    {
        $query = "INSERT INTO bko_users
                  (
                    pid,
                    firstname,
                    lastname,
                    email,
                    username,
                    password,
                    plevel,
                    status,
                    last_login,
                    loc
                  )
                  VALUES
                  (
                    " . $data['pid'] . ",
                    '" . pg_escape_string($data['firstname']) . "',
                    '" . pg_escape_string($data['lastname']) . "',
                    '" . pg_escape_string($data['email']) . "',
                    '" . pg_escape_string($data['username']) . "',
                    '" . pg_escape_string(md5($data['password'])) . "',
                    " . $data['plevel'] . ",
                    " . $data['status'] . ",
                    " . ($data['last_login'] ? "'" . pg_escape_string($data['last_login']) . "'" : 'NULL') . ",
                    " . ($data['loc'] ? "'" . pg_escape_string($data['loc']) . "'" : 'NULL') . "
                  ) ON CONFLICT (username) DO UPDATE SET
                    pid        = " . $data['pid'] . ",
                    firstname  = '" . pg_escape_string($data['firstname']) . "',
                    lastname   = '" . pg_escape_string($data['lastname']) . "',
                    email      = '" . pg_escape_string($data['email']) . "',
                    " . (empty($data['password']) ? "" : "password   = '" . pg_escape_string(md5($data['password'])) . "',") . "
                    plevel     = " . $data['plevel'] . ",
                    status     = " . $data['status'] . ",
                    last_login = " . ($data['last_login'] ? "'" . pg_escape_string($data['last_login']) . "'" : 'NULL') . ",
                    loc        = " . ($data['loc'] ? "'" . pg_escape_string($data['loc']) . "'" : 'NULL') . "
                    RETURNING *,
                    CASE WHEN xmax::text::int > 0
                      THEN 'updated' else 'inserted' END AS active";

        return $query;
    }

    public function getAdminListQuery($params = [])
    {

        $whereQuery = ' WHERE 1 = 1 ';

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

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

        if (!empty($params['first_name'])) {
            $whereQuery .= " AND firstname  ILIKE '%" . pg_escape_string(trim($params['first_name'])) . "%' ";
        }

        if (!empty($params['last_name'])) {
            $whereQuery .= " AND lastname  ILIKE '%" . pg_escape_string(trim($params['last_name'])) . "%' ";
        }

        if (!empty($params['email'])) {
            $whereQuery .= " AND email  ILIKE '%" . pg_escape_string(trim($params['email'])) . "%' ";
        }

        if (!empty($params['username'])) {
            $whereQuery .= " AND username  ILIKE '%" . pg_escape_string(trim($params['username'])) . "%' ";
        }

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

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

        $query = "
        SELECT *,
            to_char(added, 'YYYY-MM-DD') AS added,
            to_char(last_login, 'YYYY-MM-DD') AS last_login
        FROM
            bko_users
        " . $whereQuery . " 
        ORDER BY
            username
        ";

        return $query;
    }
}