read_replica = $this->load->database('savvy_replica', TRUE);
}
public function getAdviceQuery($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(a.travel_date, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "'
AND
to_char(a.travel_date, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "'
)
OR
(to_char(a.updated, 'YYYY-MM-DD') >= '" . pg_escape_string($fromDate) . "'
AND
to_char(a.updated, 'YYYY-MM-DD') <= '" . pg_escape_string($toDate) . "'
)
) ";
}
if (!empty($params['duration_from'])) {
$whereQuery .= " AND a.duration >= " . pg_escape_string($params['duration_from']) . " ";
}
if (!empty($params['duration_to'])) {
$whereQuery .= " AND a.duration <= " . pg_escape_string($params['duration_to']) . " ";
}
if (!empty($params['distance_from'])) {
$whereQuery .= " AND a.distance >= " . pg_escape_string($params['distance_from']) . " ";
}
if (!empty($params['distance_to'])) {
$whereQuery .= " AND a.distance <= " . pg_escape_string($params['distance_to']) . " ";
}
if (!empty($params['location_start_id'])) {
$whereQuery .= " AND a.location_start_id = " . pg_escape_string($params['location_start_id']) . " ";
}
if (!empty($params['location_end_id'])) {
$whereQuery .= " AND a.location_end_id = " . pg_escape_string($params['location_end_id']) . " ";
}
$query = "
SELECT a.id,a.travel_date||'
'||a.updated As \"Date/Updated\",a.duration,a.distance,
b.address||'
'||c.address as \"From and To\",
'' AS \"View\"
FROM parsedemail_item a
LEFT JOIN address b ON b.id = a.location_start_id
LEFT JOIN address c ON c.id = a.location_end_id
WHERE a.private='t' AND a.trackedemail_item_id IS NULL " . $whereQuery . " ORDER BY a.id DESC
";
return $query;
}
public function get_query_parsed_email_quote_records($filters, $count_record = true) {
$combo_array = [
];
$numeric_array = [
'duration_from' => 'a.duration >=',
'duration_to' => 'a.duration <=',
'distance_from' => 'a.distance >=',
'distance_to' => 'a.distance <=',
'location_start_id' => 'a.location_start_id',
'location_end_id' => 'a.location_end_id',
];
$string_array = [
];
$boolean_array = [
];
if ($count_record) {
$this->read_replica->select([
'count(*) AS all_count'
]);
} else {
$this->read_replica->select([
'a.travel_date',
'a.duration',
'a.cost_raw',
'a.cost',
'a.updated',
'a.distance',
'a.transport_provider_id',
'a.travel_date_end',
'a.location_start_id',
'a.location_end_id',
'b.address AS location_start_address',
'b.latitude AS location_start_lat',
'b.longitude AS location_start_lng',
'b.postal AS location_start_postal',
'b.country AS location_start_country',
'b.description AS location_start_description',
'd.timezone AS location_start_tz',
'c.address AS location_end_address',
'c.latitude AS location_end_lat',
'c.longitude AS location_end_lng',
'c.postal AS location_end_postal',
'c.country AS location_end_country',
'c.description AS location_end_description',
'e.timezone AS location_end_tz'
]);
}
$this->read_replica->from('parsedemail_item a');
$this->read_replica->join('address b', 'b.id=a.location_start_id', 'LEFT');
$this->read_replica->join('address c', 'c.id=a.location_end_id', 'LEFT');
$this->read_replica->join('address_timezone d', 'b.timezone=d.id', 'LEFT');
$this->read_replica->join('address_timezone e', 'c.timezone=e.id', 'LEFT');
$this->read_replica->where('a.trackedemail_item_id IS NULL');
$this->read_replica->where('a.private', 't');
foreach($filters as $key => $val) {
if ($key === 'travel_date') {
$fromToArray = explode(' - ', $val);
$this->read_replica->group_start()
->group_start()
->where('DATE(a.travel_date) >=', $fromToArray[0]) // start date
->where('DATE(a.travel_date) <=', $fromToArray[1]) // end date
->group_end()
->or_group_start()
->where('DATE(a.updated) >=', $fromToArray[0]) // start date
->where('DATE(a.updated) <=', $fromToArray[1]) // end date
->group_end()
->group_end();
} else if ($key === 'location_start_ids') {
$this->read_replica->where_in('a.location_start_id', $val);
} else if ($key === 'location_end_ids') {
$this->read_replica->where_in('a.location_end_id', $val);
} else 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 (array_key_exists($key, $string_array)) {
$this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
} else if (array_key_exists($key, $boolean_array)) {
$this->read_replica->where($boolean_array[$key], $val);
}
}
if ($count_record === false) {
$this->read_replica->order_by('a.id DESC');
}
return $this->read_replica->get_compiled_select();
}
public function count_parsedemail_item_record() {
return $this->read_replica->select(['count(id) AS all_count'])
->from('parsedemail_item')
->get()
->result_array()[0]['all_count'];
}
}