read_replica = $this->load->database('savvy_replica', TRUE);
}
public function get_decision_status_records(
$filters = [],
$limit = null,
$offset = null
) {
$combo_array = [
'status' => 'd.status'
];
$numeric_array = [
'from_order' => 'd.lorder >=',
'to_order' => 'd.lorder <='
];
$string_array = [
'description' => 'd.description',
'personalty' => 'd.personality',
'key' => 'd.dkey'
];
$this->read_replica->select([
'd.id',
'd.lorder',
'd.description||\'
Personalty:\'||d.personality AS pp',
'\'V\'',
'd.dkey',
'\'\'||count(a.id)||\'\' AS nxt',
'(CASE WHEN d.status = 1 THEN \'Active\' ELSE \'Not Active\' END) AS status',
'\'\' AS Cards',
]);
$this->read_replica->from('decision_group d');
$this->read_replica->join('decision_group_action a', 'a.dkey = d.dkey', 'LEFT');
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 {
$this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
}
}
$this->read_replica->group_by(
'd.id,
d.description,
d.personality,
d.dkey,
d.lorder,
d.status'
);
if ($limit) {
$this->read_replica->limit($limit, $offset);
$this->read_replica->order_by('d.lorder ASC');
}
return $this->read_replica->get()->result_array();
}
public function get_quote_records(
$filters = [],
$limit = null,
$offset = null
) {
$combo_array = [
];
$numeric_array = [
'transport_provider' => 'transport_provider_id'
];
$string_array = [
];
$boolean_array = [
'prefill' => 'a.prefill'
];
$this->read_replica->select([
'\'\' AS "Check Quote"',
'date_trunc(\'second\',a.created) AS Created',
'date_trunc(\'second\',a.completed) AS Completed',
'date_trunc(\'second\',a.travel_date) AS "Travel Date"',
'a.cost_raw AS "Cost Raw"',
'ROUND(a.cost,2) AS Cost',
'CASE WHEN a.prefill=\'t\' THEN \'Automatic\' ELSE \'Application\' END AS "Data Source"',
'd.name AS "Transport Provider"',
'b.address AS Origin',
'c.address AS Destination',
'b.country AS "Origin Country"',
'b.postal AS "Origin Postal"'
]);
$this->read_replica->from('quotes 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('transport_providers d', 'd.id=a.transport_provider_id', 'LEFT');
$this->read_replica->where('a.id > 0');
if (isset($filters['sql_raw'])) {
$this->read_replica->where($filters['sql_raw']);
} else {
foreach($filters as $key => $val) {
if ($key === 'country') {
$this->read_replica->group_start();
$this->read_replica->where('b.country', $val);
$this->read_replica->or_where('c.country', $val);
$this->read_replica->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 ($limit) {
$this->read_replica->limit($limit, $offset);
}
$this->read_replica->order_by('a.created DESC');
return $this->read_replica->get()->result_array();
}
public function get_query_export_quote_records(
$filters = [],
$count_record = true
) {
$combo_array = [
];
$numeric_array = [
'transport_provider' => 'transport_provider_id'
];
$string_array = [
];
$boolean_array = [
'prefill' => 'a.prefill'
];
if ($count_record) {
$this->read_replica->select([
'count(*) AS all_count'
]);
} else {
$this->read_replica->select([
'a.transport_provider_id',
'a.automation_id',
'a.cost_raw',
'ROUND(a.cost,2) AS cost',
'date_trunc(\'second\',a.created) AS created',
'date_trunc(\'second\',a.completed) AS completed',
'date_trunc(\'second\',a.travel_date) AS travel_date',
'a.member_id',
'a.location_start_id',
'a.location_end_id',
'a.quote_group_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('quotes 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->join('transport_providers f', 'f.id=a.transport_provider_id', 'LEFT');
$this->read_replica->where('a.id > 0');
if (isset($filters['sql_raw'])) {
$this->read_replica->where($filters['sql_raw']);
} else {
foreach($filters as $key => $val) {
if ($key === 'country') {
$this->read_replica->group_start();
$this->read_replica->where('b.country', $val);
$this->read_replica->or_where('c.country', $val);
$this->read_replica->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.created DESC');
}
return $this->read_replica->get_compiled_select();
}
public function count_quote_records($filters) {
$combo_array = [
];
$numeric_array = [
'transport_provider' => 'transport_provider_id',
];
$string_array = [
];
$boolean_array = [
'prefill' => 'a.prefill'
];
$this->read_replica->select([
'count(a.id) AS all_count'
]);
$this->read_replica->from('quotes 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->where('a.id > 0');
if (isset($filters['sql_raw'])) {
$this->read_replica->where($filters['sql_raw']);
} else {
foreach($filters as $key => $val) {
if ($key === 'country') {
$this->read_replica->group_start();
$this->read_replica->where('b.country', $val);
$this->read_replica->or_where('c.country', $val);
$this->read_replica->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);
}
}
}
return $this->read_replica->get()->result_array()[0]['all_count'];
}
}