dev-chiefworks
372 lines
12 KiB
PHP
372 lines
12 KiB
PHP
|
|
<?php if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Bko_report_model extends CI_Model {
|
|
|
|
private $read_replica;
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->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||\'<br><b>Personalty:</b>\'||d.personality AS pp',
|
|
'\'<a class="btn btn-warning btn-sm btn-block"
|
|
onclick="configureDescision(\'||d.id||\',0);" >V</a>\'',
|
|
'd.dkey',
|
|
'\'<a id="nacc\'||d.id||\'" style="backgound-color:yellow;"
|
|
class="btn btn-sm btn-block"
|
|
onclick="configureNext(\'||d.id||\',0);" >\'||count(a.id)||\'</a>\' AS nxt',
|
|
'(CASE WHEN d.status = 1 THEN \'Active\' ELSE \'Not Active\' END) AS status',
|
|
'\'<button type="button" id="\'||d.id||\'" class="btn btn-info btn-sm"
|
|
onclick="configureCard(\'||d.id||\',0);">Cards</button>\' 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([
|
|
'\'<div id="check"\'||a.id||\'"><button type="button" class="btn btn-danger btn-xs"
|
|
onclick="return checkQuote(\'||a.id||\',this)">Check Quote</button></div>\' 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'];
|
|
}
|
|
} |