FloatBackOfffice/application/models/Member_model.php

<?php

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

class Member_model extends CI_Model
{

    const FIND_SELECT = [
        'email'          => 'Email',
        'firstname'      => 'Firstname',
        'lastname'       => 'Lastname',
        'id'             => 'ID',
        // 'location'       => 'Location',
        'phone'          => 'Phone',
        'username'       => 'Username',
        'loc'            => 'Loc',
        'decision_group' => 'Decistion Group'
    ];

    const COMBO_FIND_MEMBER = [
        'status'                => 'status',
        'test'                  => 'test',
        'alert_notification'    => 'alert_notification',
        'alert_email'           => 'alert_email',
        'country'               => 'country'
    ];

    const NUMERIC_FIND_MEMBER = [
        'from_min_budget'       => 'min_budget >=',
        'to_min_budget'         => 'min_budget <=',
        'from_max_budget'       => 'max_budget >=',
        'to_max_budget'         => 'max_budget <=',
        'from_points'           => 'points >=',
        'to_points'             => 'points <=',
        'from_count_acct'       => 'count_acct >=',
        'to_count_acct'         => 'count_acct <=',
        'from_count_email'      => 'count_email >=',
        'to_count_email'        => 'count_email <=',
        'from_login_failures'   => 'login_failures >=',
        'to_login_failures'     => 'login_failures <='
    ];

    const DATE_FIND_MEMBER = [
        'from_added'            => 'DATE(added) >=',
        'to_added'              => 'DATE(added) <=',
        'from_last_login'       => 'DATE(last_login) >=',
        'to_last_login'         => 'DATE(last_login) <=',
        'from_last_acct'        => 'DATE(last_acct) >=',
        'to_last_acct'          => 'DATE(last_acct) <=',
        'from_last_email'       => 'DATE(last_email) >=',
        'to_last_email'         => 'DATE(last_email) <=',
        'from_last_audit'       => 'DATE(last_audit) >=',
        'to_last_audit'         => 'DATE(last_audit) <=',
        'from_points_updated'   => 'DATE(points_updated) >=',
        'to_points_updated'     => 'DATE(points_updated) <=',
        'from_updated'          => 'DATE(updated) >=',
        'to_updated'            => 'DATE(updated) <=',
        'from_decision_updated' => 'DATE(decision_updated) >=',
        'to_decision_updated'   => 'DATE(decision_updated) <=',
        'from_gps_enabled'      => 'DATE(gps_enabled) >=',
        'to_gps_enabled'        => 'DATE(gps_enabled) <=',
    ];

    const STRING_FIND_MEMBER = [
        'city'                  => 'city'
    ];

    private $read_replica;

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

    public function getmemberLocationTracking($member_id, $start_date, $end_date, $limit = null, $page = null)
    {

        $gps = $this->load->database('gps', TRUE);
        $mysql = "SELECT
                  '<button type=\"button\" class=\"btn btn-info btn-xs\" onclick=\"viewTracked('||traked_group||');\" >View</button>' AS view ,traked_group, count(id)
                FROM members_tracking 
                WHERE member_id = $member_id
                  AND traked_group IS NOT NULL";

        if ($start_date) {
            $mysql .= " AND ttime::timestamp::date >= '" . pg_escape_string($start_date) . "'";
        }

        if ($end_date) {
            $mysql .= " AND ttime::timestamp::date <= '" . pg_escape_string($end_date) . "'";
        }

        $mysql .= " GROUP BY traked_group";

        if ($limit) {
            $mysql .= " LIMIT " . $limit . " OFFSET " . $page;
        }

        return $gps->query($mysql)->result_array();
    }
    public function getMemberMessagelReport($member_id, &$data)
    {

        $this->load->library('table');
        $this->table->set_template($this->template);

        $mysql = "SELECT '<button id=\"trip'||cp.id||'\" type=\"button\" class=\"btn btn-primary btn-xs\" block onclick=\"viewCarpool('||cp.id||');\">View</button>' AS view, "
            . "cp.pool,mc.title,mc.id AS card_id,cp.added,cp.updated,cp.status "
            . "FROM members_carpool cp "
            . "LEFT JOIN members m ON m.id = cp.member_id  "
            . "LEFT JOIN main_cards mc ON mc.id=cp.card_id WHERE cp.member_id=" . $member_id;

        $query = $this->read_replica->query($mysql);
        $data['carpool_table'] = $this->table->generate($query);
        $data["page_title"] = "Carpool Report";

        return $data;
    }

    public function  get_all_record_member_tracking($filters, $limit = null, $offset = null, $is_export_mode = false)
    {
        $gps = $this->load->database('gps', TRUE);
        $gps->select('traked_group, count(id) AS count');
        $gps->from('members_tracking');

        foreach ($filters as $key => $value) {
            if ($key === 'start_date') {
                $gps->where('DATE(ttime) >=', $value);
            } else if ($key === 'end_date') {
                $gps->where('DATE(ttime) <=', $value);
            } else {
                $gps->where($key, $value);
            }
        }

        $gps->group_by('traked_group');

        if ($limit && !$is_export_mode) {
            $gps->limit($limit, $offset);
        }
        if($is_export_mode){
            return $gps->get();
        }
        return $gps->get()->result_array();
    }

    public function get_all_record_member_tracking_by_tracked_group($filters, $limit = null, $offset = null)
    {
        $gps = $this->load->database('gps', TRUE);

        $gps->select('*');

        if (isset($filters['radius'])) {
            $gps->from("( {$this->withinDistanceFromLatLng($filters['lat'],$filters['lng'],$filters['radius'])} ) AS members_tracking");

            unset($filters['radius']);
            unset($filters['lat']);
            unset($filters['lng']);
        } else {
            $gps->from('members_tracking');
        }

        foreach ($filters as $key => $value) {
            if ($key === 'start_date') {
                $gps->where('DATE(ttime) >=', $value);
            } else if ($key === 'end_date') {
                $gps->where('DATE(ttime) <=', $value);
            } else {
                $gps->where($key, $value);
            }
        }

        if ($limit) {
            $gps->limit($limit, $offset);
            $gps->order_by('id DESC');
        }

        return array_map(function ($ele) {
            return array_map(function ($val) {
                return is_null($val) ? "" : $val;
            }, $ele);
        }, $gps->get()->result_array());
    }

    function withinDistanceFromLatLng($latitude, $longitude, $distance_in_km)
    {
        $earth_radius = 6371;
        $km_per_degree_lat = 111.2;
        $pi = 3.14 / 180;

        $query = "SELECT * FROM (
                  SELECT (
                    $earth_radius * acos(
                      cos( radians( {$latitude}) )
                    * cos( radians( lat  ) )
                    * cos( radians( lng ) - radians( {$longitude} ) )
                    + sin( radians( {$latitude}) )
                    * sin( radians( lat  ) )
                  )
                ) AS distance_filter
                , *
                FROM members_tracking
                WHERE lat BETWEEN " . ($latitude - ($distance_in_km / $km_per_degree_lat)) .
            " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

        $delta = round($distance_in_km  / ($km_per_degree_lat  * COS(deg2rad($longitude))), 10);

        // Bounding box for speed - latitude within range (longtitude to km not linear)
        if (cos($longitude * $pi) > 0) {

            $from =  $longitude - $delta;
            $to = $longitude + $delta;

            $query .=
                " AND lng" .
                " BETWEEN " . $from .
                " AND " . $to;
        } else {

            $from =  $longitude + $delta;
            $to = $longitude - $delta;

            $query .=
                " AND lng" .
                " BETWEEN " . $from .
                " AND " . $to;
        }

        // distance limit for circle
        $query .= " ) members_tracking
                  WHERE distance_filter <= " . $distance_in_km;

        return $query;
    }

    public function  get_all_record_members_bankimport($filters, $limit = null, $offset = null)
    {
        $this->read_replica->select('*');
        $this->read_replica->from('members_bankimport');

        $array_date = [
          'start_date' => 'DATE(time) >=',
          'end_date' => 'DATE(time) <=',
        ];

        $array_string = [
          'provider_category' => 'provider_category',
          'merchant_name' => 'merchant_name',
          'category' => 'category',
        ];

        $array_numeric = [
          'currency' => 'currency',
          'member_id' => 'member_id'
        ];


        foreach ($filters as $key => $value) {
            if (in_array($key, $array_date)) {

              $this->read_replica->where($array_date[$key], $value);
            } else if (in_array($key, $array_string)) {

              $this->read_replica->like('lower(' . $key . ')', strtolower($value));
            } else if (in_array($key, $array_numeric)) {

              $this->read_replica->where($array_numeric[$key], $value);
            }

        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('time DESC');
        }
        return $this->read_replica->get()->result_array();
    }

    public function  get_all_record_saved_trips($filters, $limit = null, $offset = null)
    {
        $this->read_replica->select('*');
        $this->read_replica->from('members_trips');

        foreach ($filters as $key => $value) {
            if ($key === 'start_date') {
                $this->read_replica->where('DATE(trip_date) >=', $value);
            } else if ($key === 'end_date') {
                $this->read_replica->where('DATE(trip_date) <=', $value);
            } else if (
                strpos($key, 'trip') !== FALSE ||
                strpos($key, 'country') !== FALSE
            ) {
                $this->read_replica->like('lower(' . $key . ')', strtolower($value));
            } else {
                $this->read_replica->where($key, $value);
            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('trip_name DESC');
        }
        return $this->read_replica->get()->result_array();
    }

    public function get_member_records(
        $filters = [],
        $limit = null,
        $offset = null
    ) {

        $this->read_replica->select([
            '\'<a class="btn btn-warning btn-sm"
            href="/member/viewLocateMember?member_id=\'||id||\'" >Select</a>\' AS select',
            'id',
            'username',
            'firstname',
            'lastname',
            'last_login',
            'added',
            'loc',
            '\'<button style="padding:3px;" type="button" class="btn btn-info btn-xs" onclick="viewMember(\'||id||\');" >View</button>\' AS ACT'
        ]);

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

        $this->read_replica->where('id > 0');
        foreach ($filters as $key => $value) {

            if (array_key_exists($key, self::NUMERIC_FIND_MEMBER)) {

                $this->read_replica->where(self::NUMERIC_FIND_MEMBER[$key], $value);

            } else if (array_key_exists($key, self::STRING_FIND_MEMBER)) {

                $this->read_replica->where(self::STRING_FIND_MEMBER[$key], $value);

            } else if (array_key_exists($key, self::DATE_FIND_MEMBER)) {

                $this->read_replica->where(self::DATE_FIND_MEMBER[$key], $value);

            } else if (array_key_exists($key, self::FIND_SELECT)) {

                switch($key) {
                    case 'id':

                        $this->read_replica->where($key, $value);
                        break;

                    default:

                        $key = ($key === 'loc' ? "TEXT($key)" : $key);
                        $key = 'lower(' . $key . ')';
                        $this->read_replica->like($key, strtolower($value));

                    }

            } else if (array_key_exists($key, self::COMBO_FIND_MEMBER)) {

                $this->read_replica->where(self::COMBO_FIND_MEMBER[$key], $value);

            }
        }

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

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

    public function get_tracked_email_record(
        $filters = [],
        $count_record = true,
        $limit = null,
        $offset = null) {

        $in_array = [
            'member_id_list' => 'member_id',
        ];

        $string_array = [
            'subject' => 'lower(subject)',
            'message_from' => 'lower(message_from)'
        ];

        $date_array = [
            'start_created' => 'DATE(created) >=',
            'end_created' => 'DATE(created) <=',
            'start_message_date' => 'DATE(message_date) >=',
            'end_message_date' => 'DATE(message_date) <=',
        ];

        if ($count_record) {

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

        } else {

            $this->read_replica->select([
                'id',
                'member_id',
                'subject',
                'created',
                'message_from',
                'hash',
                'date_parsed',
                'oauth2_pull_job_id',
                'message_date',
                'message'
            ]);

        }

        $this->read_replica->from('trackedemail_item');
        $this->read_replica->where('1 = 1');

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

            if (array_key_exists($key, $in_array)) {

                $this->read_replica->where_in($in_array[$key], $val);

            } else if (array_key_exists($key, $date_array)) {

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

            } else if (array_key_exists($key, $string_array)) {

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

            }
        }

        if ($limit) {
            $limit = " LIMIT ${limit} OFFSET ${offset}";
        }

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

        if (isset($filters['sql_raw'])) {
            $query .= ' AND ' . pg_escape_string($filters['sql_raw']);
        }

        $query .= $limit;

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