'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
'' 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 '' 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([
'\'Select\' AS select',
'id',
'username',
'firstname',
'lastname',
'last_login',
'added',
'loc',
'\'\' 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);
}
}