Float/FloatBackOfffice
1
0
Fork 0
Code Issues Pull Requests Releases Activity
Files
master
FloatBackOfffice/application/controllers/Bkoreport.php
T
dev-chiefworks f76abffdcd first commit
2022-05-31 16:21:53 -04:00

2141 lines
88 KiB
PHP
Raw Permalink Blame History

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Bkoreport extends Admin_Controller {
var $template = array(
'table_open' => "<table class='table table-striped table-hover table-bordered table-condensed' style='font-size:10px;'>",
'thead_open' => '<thead class=\'bg-indigo\'>',
'thead_close' => '</thead>',
'heading_row_start' => '<tr>',
'heading_row_end' => '</tr>',
'heading_cell_start' => '<th>',
'heading_cell_end' => '</th>',
'tbody_open' => '<tbody>',
'tbody_close' => '</tbody>',
'row_start' => '<tr>',
'row_end' => '</tr>',
'cell_start' => '<td>',
'cell_end' => '</td>',
'row_alt_start' => '<tr>',
'row_alt_end' => '</tr>',
'cell_alt_start' => '<td>',
'cell_alt_end' => '</td>',
'table_close' => '</table>'
);
const CSV_FIELDS = [
'transport_provider_id', // | 2019-11-19 08:58:00
'automation_id', // | 26
'cost_raw', // | SGD 13.00
'cost', // | 13.00
'created', // | 2019-05-07 10:52:52
'completed', // | 2019-05-07 10:52:54
'travel_date', // | 2019-05-07 04:52:54
'member_id', // | 22
'location_start_id', // | 7276
'location_end_id', // | 7277
'quote_group_id', // | 851
'location_start_address', // | 65 Jurong West Central 3, Singapore, 648332
'location_start_lat', // | 1.339345
'location_start_lng', // | 103.705984
'location_start_postal', // |
'location_start_country', // | SG
'location_start_description', // |
'location_start_tz', // | Asia/Singapore
'location_end_address', // | 3 Engineering Drive 3, Singapore, 117582
'location_end_lat', // | 1.299255
'location_end_lng', // | 103.772206
'location_end_postal', // |
'location_end_country', // | SG
'location_end_description', // |
'location_end_tz' // | Asia/Singapore
];
public function index() {
$this->load->helper('url');
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$data['backoffice_users'] = "";
$mysql = "SELECT '<button type=\"button\" class=\"btn\">Edit</button>' AS edit,firstname,lastname,email,username,status,last_login,loc FROM bko_users ORDER by id ASC";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['backoffice_users'] = $this->table->generate($query);
$this->renderAdminPage('view_admin_users', $data);
// echo 'Ameye Olu';
}
protected function renderAdminPage($page_name, $data) {
$this->load->view('admin/view_admin_header', $data);
$this->load->view('admin/' . $page_name, $data);
$this->load->view('admin/view_admin_footer', $data);
}
public function cardimages() {
global $savvyext;
$data = array();
$data['storage'] = $savvyext->cfgReadChar('system.storage_url');
//$data['js'] = array('https://maps.googleapis.com/maps/api/js?key=AIzaSyDvjiRTxngOQyBP4zpqFlZuiquc0ROvo9c&callback=initMap');
$this->renderAdminPage('cardimages', $data);
}
public function getValueCombo($val) {
$status_value = range(0, 1);
return in_array($val, $status_value)
? $val
: '';
}
public function setComboForDecisionStatus($params) {
$this->load->model('combo_model');
$combo['card_status'] = $this->combo_model->getStatusComboWithAll(
'card_status',
$params['status']
);
return $combo;
}
public function setFormRuleForDecisionStatus() {
$this->form_validation->set_rules('from_order', 'Lorder', 'integer');
$this->form_validation->set_rules('to_order', 'Lorder', 'integer');
$this->form_validation->set_rules('status', 'Status', 'integer');
}
public function getValueOfDecisionStatus() {
return [
'from_order' => trim($this->input->get('from_order')),
'to_order' => trim($this->input->get('to_order')),
'description' => trim($this->input->get('description')),
'personalty' => trim($this->input->get('personalty')),
'key' => trim($this->input->get('key')),
'status' => trim($this->input->get('card_status')
?? ($this->input->get('status') ?? -1)),
];
}
public function validateValueForDecisionStatus($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForDecisionStatus();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function grabpricing(){
$where = 'true';
$form_data = $this->input->get();
$data = [];
// travel date filter
if (!empty($form_data['TravelDate'])) {
$TravelDate = $form_data['TravelDate'];
$data['final_date_range'] = $this->grabpricing_calc_date_range($TravelDate, $form_data['TravelDateVariance']);
$travelDateFrom = $TravelDate . ' 00:00:00';
$travelDateTo = $TravelDate . ' 23:59:59';
$IntervalDays = ($form_data['TravelDateVariance']) . ' days';
$where .= sprintf(" AND (a.travel_date BETWEEN TIMESTAMP '%s' - INTERVAL '%s' and TIMESTAMP '%s' + INTERVAL '%s')", $travelDateFrom, $IntervalDays, $travelDateTo, $IntervalDays);
}
$TravelTime = $form_data['TravelTime'] ?? '00:00';
$TravelTimeVariance = $form_data['TravelTimeVariance'] ?? 0;
// travel time filter
if ($TravelTime !== '00:00' || ($TravelTime === '00:00' && $TravelTimeVariance > 0)) {
$time_calc_field = "(date_part('hour',a.travel_date)::TEXT||':'||date_part('minute',a.travel_date)::TEXT)::TIME";
$IntervalTravelTimeVariance = $TravelTimeVariance . ' MINUTE';
$where .= sprintf(" AND ($time_calc_field BETWEEN '%s'::TIME - INTERVAL '%s' AND '%s'::TIME + INTERVAL '%s')", $TravelTime, $IntervalTravelTimeVariance, $TravelTime, $IntervalTravelTimeVariance);
$data['final_time_range'] = $this->grabpricing_calc_time_range($TravelTime, $TravelTimeVariance);
}
//distance filter
$distance_calc_field = 'ST_Distance(ST_SetSRID(ST_MakePoint(ls.longitude,ls.latitude), 4326)::geometry,ST_SetSRID(ST_MakePoint(le.longitude,le.latitude), 4326)::geography)';
if (!empty($form_data['TravelDistance'])) {
$TravelDistance = $form_data['TravelDistance'] ?? 0;
$TravelDistanceVariance = $form_data['TravelDistanceVariance'] ?? 0;
$where .= sprintf(" AND ($distance_calc_field BETWEEN %f and %f)", $TravelDistance - $TravelDistanceVariance, $TravelDistance + $TravelDistanceVariance);
}else{
$where .= ' AND '.$distance_calc_field.'>0';
}
//save view model data
$data['TravelDate'] = $form_data['TravelDate'] ?? '';
$data['TravelDateVariance'] = $form_data['TravelDateVariance'] ?? '';
$data['TravelTime'] = $TravelTime;
$data['TravelTimeVariance'] = $form_data['TravelTimeVariance'] ?? '';
$data['TravelDistance'] = $form_data['TravelDistance'] ?? '';
$data['TravelDistanceVariance'] = $form_data['TravelDistanceVariance'] ?? '';
$mysql = 'SELECT a."id", round(a."cost", 2) as "cost",
'.$distance_calc_field.' AS distance,
a.travel_date,
ls.address AS location_start,
le.address AS location_end
FROM quotes a, address ls, address le
WHERE ls.id=a.location_start_id AND le.id=a.location_end_id
AND transport_provider_id=3 and a."cost">0 AND '.$where.' ORDER BY a.id DESC'; // OLD LIMIT 500
//$mysql = " SELECT id,cost,distance,travel_date,location_start,location_end FROM sample_grab WHERE cost > 0 AND distance > 0 AND $where ORDER BY id DESC";
$this->load->library('table');
$this->table->set_template($this->template_small);
//$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'RM', 'style' => 'width:50px'), 'Description', array('data' => 'WT', 'style' => 'width:50px'), array('data' => 'ACT', 'style' => 'width:50px'));
//$data['grap_history'] = $this->table->generate($query);
$tableData = $this->returnAdminTable(
[
'count_query' => $mysql,
'query' => $mysql,
],
'bkoreport/grabpricing/',
[
'per_page' => 20,
'reuse_query_string' => TRUE,
'uri_segment' => 3,
]
);
$data['grap_history'] = $tableData['output_table'];
$data['links'] = $tableData['links'];
$this->renderAdminPage('view_grabpricing', $data);
}
private function grabpricing_calc_date_range($travel_date, $date_variance)
{
if($date_variance==0){
return '';
}
$calc_date = \DateTime::createFromFormat('Y-m-d H:i:s', $travel_date . ' 00:00:00');
$hoursToAdd = $date_variance * 24;
$intervalSub = new \DateInterval("PT{$hoursToAdd}H");
$intervalSub->invert = 1;
$calc_date_from = clone $calc_date;
$calc_date_to = clone $calc_date;
$calc_date_from->add($intervalSub);
$calc_date_to->add(new \DateInterval("PT{$hoursToAdd}H"));
$rs = $calc_date_from->format('Y-m-d') . ' -> ' . $calc_date_to->format('Y-m-d');
return $rs;
}
private function grabpricing_calc_time_range($travel_time, $time_variance)
{
if($time_variance==0){
return '';
}
$calc_date = \DateTime::createFromFormat('Y-m-d H:i:s', (new DateTime())->format('Y-m-d') . ' ' . $travel_time . ':00');
$intervalSub = new \DateInterval("PT{$time_variance}M");
$intervalSub->invert = 1;
$calc_date_from = clone $calc_date;
$calc_date_to = clone $calc_date;
$calc_date_from->add($intervalSub);
$calc_date_to->add(new \DateInterval("PT{$time_variance}M"));
$final_time_range = $calc_date_from->format('H:i') . ' -> ' . $calc_date_to->format('H:i');
return $final_time_range;
}
/*
savvy=> \d sample_grab
Table "public.sample_grab"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
id | integer | | |
transport_provider_id | integer | | |
automation_id | integer | | |
cost_raw | character varying(20) | | |
cost | numeric | | |
created | timestamp without time zone | | |
completed | timestamp without time zone | | |
member_id | integer | | |
location_start_id | bigint | | |
location_end_id | bigint | | |
quote_group_id | integer | | |
prefill | boolean | | |
pool | smallint | | |
travel_date | timestamp with time zone | | |
location_start | character varying(200) | | |
location_start_lat | numeric | | |
location_start_lng | numeric | | |
location_start_tz | integer | | |
location_geocoding_date | date | | |
location_end | character varying(200) | | |
location_end_lat | numeric | | |
location_end_lng | numeric | | |
*/
public function decisionstatus() {
$this->load->model('bko_report_model');
$this->load->library('table');
$this->table->set_template($this->template_small);
$this->table->set_heading(
array('data' => 'ID', 'style' => 'width:50px'),
array('data' => 'Order', 'style' => 'width:40px'),
'Description',
array('data' => 'V', 'style' => 'width:10px'),
array('data' => 'Key', 'style' => 'width:40px'),
array('data' => 'Next', 'style' => 'width:40px'),
array('data' => 'Status', 'style' => 'width:40px'),
array('data' => 'Action', 'style' => 'width:40px')
);
$this->table->set_heading([
'ID',
'Order',
'Description',
'V',
'Key',
'Next',
'Status',
'Action'
]);
$params = $this->getValueOfDecisionStatus();
$data = $this->setComboForDecisionStatus($params);
$params['status'] = $this->getValueCombo($params['status']);
$params = array_filter($params, function($ele) {
return $ele !== "";
});
$errors = $this->validateValueForDecisionStatus($params);
$params = array_diff_key($params, $errors);
$data = array_merge(
$data,
$params,
$this->load_pagination(
count($this->bko_report_model->get_decision_status_records($params)),
$params,
'decisionstatus'
)
);
$data['decision_group'] = $this->table->generate(
$this->bko_report_model->get_decision_status_records(
$params,
$data['limit'],
$data['offset']
)
);
$this->renderAdminPage('r-engine/view_recomendation', $data);
}
public function configuredecision() {
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$decision_id = (int) $this->input->get('decision_id');
$id = (int) $this->input->get('id');
$proc = $this->input->get('proc');
$mysql = "SELECT * FROM decision_group WHERE id = $decision_id";
$query = $this->read_replica->query($mysql);
$row = $query->row();
if (isset($row)) {
$data["decision_group_title"] = $row->dkey;
$data["decision_group_desc"] = $row->description;
}
// savvy=> ;
// id | lorder | description | dkey | status
// ----+--------+---------------------+---------+--------
// 5 | 99 | Bike/Scooters Lover | A300006 | 0
// (1 row)
$mysql = "SELECT id,description,
'<button type=\"button\" id=\"logic'||id||'\" class=\"btn btn-danger btn-sm\" onclick=\"addLogic('||id||',this,$decision_id);\">+></button>' AS Cards
FROM decision_logic";
$mysql .= " WHERE id NOT IN (SELECT decision_logic FROM group_decision_logic WHERE group_id=$decision_id AND status=1) ";
$mysql .= " ORDER BY id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Description', array('data' => 'ADD', 'style' => 'width:50px'));
$data['logic_list'] = $this->table->generate($query);
// $this->load->library('table');
// $this->table->set_template($this->template);
$mysql = "SELECT
'<button type=\"button\" id=\"logic'||g.id||'\" class=\"btn btn-info btn-sm\" onclick=\"removeLogic('||g.id||',this,'||g.group_id||');\"><-</button>' AS Cards,
l.description AS desc,'<input type=\"text\" style=\"width:60px;\" id=\"T'||g.id||'\" name=\"T'||g.id||'\" class=\"form-control\" value=\"'||g.weight||'\">' AS weight,
'<button type=\"button\" id=\"logic'||g.id||'\" class=\"btn btn-primary btn-sm\" onclick=\"updateLogic('||g.id||','||l.id||');\">UP</button>' as updt
FROM group_decision_logic g LEFT JOIN decision_logic l ON l.id =g.decision_logic WHERE g.group_id=" . $decision_id . " AND g.status=1";
$mysql .= " ORDER BY l.id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'RM', 'style' => 'width:50px'), 'Description', array('data' => 'WT', 'style' => 'width:50px'), array('data' => 'ACT', 'style' => 'width:50px'));
$data['group_logic_list'] = $this->table->generate($query);
$data["card_category"] = "";
$data["decision_id"] = $decision_id;
/*
* we have to send survey cards
*/
$this->load->model('combo_model');
$data["survey_list"] = $this->combo_model->getSurvelListGroupCombo("survey_list", "");
$data["location_list"] = $this->combo_model->getGPSTriggerLocationCombo("gps_location_list", "");
$this->load->view('admin/r-engine/view_recomendation_logic', $data);
return 0;
}
public function decisioncard() {
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$decision_id = (int) $this->input->get('decision_id');
$id = (int) $this->input->get('id');
$proc = $this->input->get('proc');
if ($proc == 'ADD') {
if ($decision_id > 0 && $id > 0) {
$q = "INSERT INTO decision_cards (decision_id,card_id) VALUES($decision_id,$id) RETURNING id";
$r = $this->db->query($q);
$f = $r->result_array()[0];
if ($f["id"] > 0) {
echo $f["id"];
} else {
echo "Failed to add card!";
}
} else {
echo "Invalid decision and/or card ID";
}
} else if ($proc == 'DEL') {
if ($decision_id > 0 && $id > 0) {
$q = "UPDATE decision_cards SET status=0 WHERE id=${id} AND decision_id=${decision_id} RETURNING status,card_id";
$r = $this->db->query($q);
$f = $r->result_array()[0];
if ($f['card_id'] > 0 && $f['status'] == 0) {
echo $f['card_id'];
} else {
echo "Failed to remove card!";
var_dump($f);
}
} else {
echo "Invalid decision and/or card ID";
}
} else {
//echo '<h1>'.$decision_id.'</h1>';
$q = "SELECT dkey,description FROM decision_group WHERE id=${decision_id}";
$r = $this->read_replica->query($q);
$f = $r->result_array()[0];
$data["decision_group_title"] = $f["dkey"] . " " . $f["description"];
$this->load->model('combo_model');
$data['card_category_vaue'] = trim($this->input->get('card_category'));
$data['card_category'] = $this->combo_model->getCardCategoryCombo('card_category', $data['card_category_vaue'],'','CARDDYNAMIC');
$data['decision_id'] = $decision_id;
$mysql = "SELECT '<span style=\"coor:red;\">'||name||'</span><br>'||id||' - '|| title ,'<button id=\"acc'||id||'\" type=\"button\" class=\"btn btn-primary btn-xs\" onclick=\"addCard('||id||',this,$decision_id, '||card_order||');\"> + </button>' AS Addcard FROM main_cards ";
$mysql .= " WHERE status=1 AND deleted IS NULL AND id NOT IN (SELECT card_id FROM decision_cards WHERE decision_id=$decision_id AND status =1) AND button1_action NOT IN ('CARDDYNAMIC')";
if ($data['card_category_vaue'] != '' && $data['card_category_vaue'] != '0') {
$mysql .= " AND button1_action='" . pg_escape_string($data['card_category_vaue']) . "'";
}
$mysql .= " ORDER BY id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading("Card Title", array('data' => 'Action', 'style' => 'width:90px'));
$data["card_list"] = $this->table->generate($query);
$mysql = "SELECT '<button id=\"acc'||c.id||'\" type=\"button\" class=\"btn btn-danger btn-xs\" onclick=\"removeCard('||d.id||',this,'||d.decision_id||','||c.card_order||');\"> - </button>' AS SelCard, c.name as CardId, c.card_order FROM decision_cards d LEFT JOIN main_cards c ON c.id=d.card_id WHERE d.decision_id=$decision_id AND d.status=1 ";
if ($data['card_category_vaue'] != '' && $data['card_category_vaue'] != '0') {
$mysql .= " AND c.button1_action='" . pg_escape_string($data['card_category_vaue']) . "'";
}
$mysql .= " ORDER BY c.card_order ASC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'Remove', 'style' => 'width:90px'), "Card ID", "Card Order");
$data["sel_list"] = $this->table->generate($query);
$this->load->view('admin/r-engine/view_recomendation_card', $data);
}
}
public function validateValue($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForLoginReportForm();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function setFormRuleForLoginReportForm() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules(
'member_id',
'Member ID',
'numeric'
);
$this->form_validation->set_rules(
'from_updated',
'Updated date',
$date_pattern
);
$this->form_validation->set_rules(
'to_updated',
'Updated date',
$date_pattern
);
$this->form_validation->set_rules(
'from_created',
'Created date',
$date_pattern
);
$this->form_validation->set_rules(
'to_created',
'Created date',
$date_pattern
);
}
public function getValueOfLoginReportForm() {
return [
'username' => trim($this->input->get('username') ?? ''),
'member_id' => trim($this->input->get('member_id') ?? ''),
'from_updated' => trim($this->input->get('from_updated') ?? ''),
'to_updated' => trim($this->input->get('to_updated') ?? ''),
'from_created' => trim($this->input->get('from_created') ?? ''),
'to_created' => trim($this->input->get('to_created') ?? ''),
'loc' => trim($this->input->get('loc') ?? ''),
];
}
public function setOnePastMonthForLoginReport($params) {
if (empty($params['from_updated']) && empty($params['to_updated'])) {
$params['from_updated'] = date("Y-m-d", strtotime("-1 months"));
$params['to_updated'] = date("Y-m-d");
}
return $params;
}
public function loginreport() {
$this->load->model('login_report_model');
$this->load->library('table');
$this->table->set_template($this->template);
$data["page_title"] = "Pass Reset Report";
$this->table->set_heading([
'ID',
'Username',
'Member ID',
'Created',
'Updated',
'Loc',
'Shop'
]);
$params = $this->getValueOfLoginReportForm();
$params = $this->setOnePastMonthForLoginReport($params);
$params = array_filter($params, function($ele) {
return $ele !== "";
});
$errors = $this->validateValue($params);
$params = array_diff_key($params, $errors);
$data = array_merge(
$data,
$params,
$this->load_pagination(
count($this->login_report_model->get_login_report_records($params)),
$params,
'loginreport'
)
);
$data['login_table'] = $this->table->generate(
$this->login_report_model->get_login_report_records(
$params,
$data['limit'],
$data['offset']
)
);
$this->renderAdminPage('view_loginreport', $data);
}
public function load_pagination( $count_record, $params, $action, $limit = 10 ) {
// pagination
$this->load->library('pagination');
$config["total_rows"] = $count_record;
$config["base_url"] = base_url() . "/Bkoreport/" . $action;
$config["per_page"] = $limit;
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config["suffix"] = '?' . http_build_query($params);
$config["first_url"] =
"/bkoreport/{$action}/0?" . http_build_query($params);
$config['full_tag_open'] = "<ul class='pagination'>";
$config['full_tag_close'] = "</ul>";
$config['num_tag_open'] = '<li>';
$config['num_tag_close'] = '</li>';
$config['cur_tag_open'] = "<li class='disabled'><li class='active'><a href='#'>";
$config['cur_tag_close'] = "<span class='sr-only'></span></a></li>";
$config['next_tag_open'] = "<li>";
$config['next_tagl_close'] = "</li>";
$config['prev_tag_open'] = "<li>";
$config['prev_tagl_close'] = "</li>";
$config['first_tag_open'] = "<li>";
$config['first_tagl_close'] = "</li>";
$config['last_tag_open'] = "<li>";
$config['last_tagl_close'] = "</li>";
$this->pagination->initialize($config);
$page = ( $this->uri->segment(3) ) ? $this->uri->segment(3) : 0;
$offset = is_numeric($page) ? $page : 0;
return [
'link' => $this->pagination->create_links(),
'offset' => $offset,
'limit' => $config["per_page"]
];
}
public function setValueCombo($val) {
$status_value = range(0, 9);
return in_array($val, $status_value)
? $val
: '';
}
public function setComboForResetReport($params) {
$this->load->model('combo_model');
$combo['card_status'] = $this->combo_model->getStatusComboFromZeroToNine(
'card_status',
$params['status']
);
return $combo;
}
public function setFormRuleForResetReportForm() {
$status_pattern = 'regex_match[/^(?:[0-9])$/]';
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules('member_id', 'Member ID', 'numeric');
$this->form_validation->set_rules('from_date', 'Created date', $date_pattern);
$this->form_validation->set_rules('to_date', 'Created date', $date_pattern);
$this->form_validation->set_rules('status', 'Status', $status_pattern);
}
public function getValueOfResetReportForm() {
return [
'username' => trim($this->input->get('username') ?? ''),
'member_id' => trim($this->input->get('member_id') ?? ''),
'from_date' => trim($this->input->get('from_date') ?? ''),
'to_date' => trim($this->input->get('to_date') ?? ''),
'loc' => trim($this->input->get('loc') ?? ''),
'status' => trim($this->input->get('card_status')
?? ($this->input->get('status') ?? -1)),
];
}
public function setOnePastMonthForResetReport($params) {
if (empty($params['from_date']) && empty($params['to_date'])) {
$params['from_date'] = date("Y-m-d", strtotime("-1 months"));
$params['to_date'] = date("Y-m-d");
}
return $params;
}
public function resetreport() {
$this->load->model('resetpassword_model');
$this->load->library('table');
$this->table->set_template($this->template);
$data["page_title"] = "Pass Reset Report";
$this->table->set_heading([
'ID',
'Username',
'Member ID',
'Reset Bin',
'Reset Key',
'Created',
'Expired',
'Status',
'Loc',
'Trail Count',
]);
$params = $this->getValueOfResetReportForm();
$params = $this->setOnePastMonthForResetReport($params);
$data = $this->setComboForResetReport($params);
$params['status'] = $this->setValueCombo($params['status']);
$params = array_filter($params, function($ele) {
return $ele !== "";
});
$errors = $this->validateValue($params);
$params = array_diff_key($params, $errors);
$data = array_merge(
$data,
$params,
$this->load_pagination(
count($this->resetpassword_model->get_resetpassword_records($params)),
$params,
'resetreport'
)
);
$data['reset_table'] = $this->table->generate(
$this->resetpassword_model->get_resetpassword_records(
$params,
$data['limit'],
$data['offset']
)
);
$this->renderAdminPage('view_resetpass', $data);
}
public function decisionlogic() {
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$decision_id = (int) $this->input->get('decision_id');
$id = (int) $this->input->get('id');
$proc = $this->input->get('proc');
if ($proc == 'ADD') {
if ($decision_id > 0 && $id > 0) {
$q = "INSERT INTO group_decision_logic (group_id,decision_logic) VALUES($decision_id,$id) RETURNING id";
$r = $this->db->query($q);
$f = $r->result_array()[0];
if ($f["id"] > 0) {
echo $f["id"];
} else {
echo "Failed to add logic!";
}
} else {
echo "Invalid decision and/or logic ID";
}
} else if ($proc == 'DEL') {
if ($decision_id > 0 && $id > 0) {
$q = "UPDATE group_decision_logic SET status=0 WHERE id=${id} AND group_id=${decision_id} RETURNING status,decision_logic";
$r = $this->db->query($q);
$f = $r->result_array()[0];
if ($f['decision_logic'] > 0 && $f['status'] == 0) {
echo $f['decision_logic'];
} else {
echo "Failed to remove logic!";
var_dump($f);
}
} else {
echo "Invalid decision and/or logic ID";
}
} else {
//echo '<h1>'.$decision_id.'</h1>';
$q = "SELECT dkey,description FROM decision_group WHERE id=${decision_id}";
$r = $this->read_replica->query($q);
$f = $r->result_array()[0];
$data["decision_group_title"] = $f["dkey"] . " " . $f["description"];
$data['decision_id'] = $decision_id;
$mysql = "SELECT id,description,
'<button type=\"button\" id=\"logic'||id||'\" class=\"btn btn-danger btn-sm\" onclick=\"addLogic('||id||',this,$decision_id);\">+></button>' AS Cards
FROM decision_logic";
$mysql .= " WHERE id NOT IN (SELECT decision_logic FROM group_decision_logic WHERE group_id=$decision_id AND status=1) ";
$mysql .= " ORDER BY id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Description', array('data' => 'ADD', 'style' => 'width:50px'));
$data['logic_list'] = $this->table->generate($query);
// $this->load->library('table');
// $this->table->set_template($this->template);
$mysql = "SELECT
'<button type=\"button\" id=\"logic'||g.id||'\" class=\"btn btn-info btn-sm\" onclick=\"removeLogic('||g.id||',this,'||g.group_id||');\"><-</button>' AS Cards, g.id, l.description AS desc
FROM group_decision_logic g LEFT JOIN decision_logic l ON l.id =g.decision_logic WHERE g.group_id=" . $decision_id . " AND g.status=1";
$mysql .= " ORDER BY l.id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'RM', 'style' => 'width:50px'), array('data' => 'ID', 'style' => 'width:10px'), 'Description');
$data['group_logic_list'] = $this->table->generate($query);
$this->load->view('admin/r-engine/view_recomendation_logic', $data);
}
}
public function devices() {
$this->load->helper('url');
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "select a.*,case when platform='' then 'Unknown' else b.platform end as platform from (select platform_id,count(*) as count from members_devices group by platform_id) as a left join members_device_platforms b on (b.id=a.platform_id) order by a.count desc";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['device_platforms'] = $this->table->generate($query);
list($data['device_platforms_labels'], $data['device_platforms_values']) = $this->getDevicePie($mysql);
$mysql = "select a.*,case when model='' then 'Unknown' else b.model end as model from (select model_id,count(*) as count from members_devices group by model_id) as a left join members_device_models b on (b.id=a.model_id) order by a.count desc";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['device_models'] = $this->table->generate($query);
list($data['device_models_labels'], $data['device_models_values']) = $this->getDevicePie($mysql);
$mysql = "select a.*,case when manufacturer='' then 'Unknown' else b.manufacturer end as manufacturer from (select manufacturer_id,count(*) as count from members_devices group by manufacturer_id) as a left join members_device_manufacturers b on (b.id=a.manufacturer_id) order by a.count desc";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['device_manufacturers'] = $this->table->generate($query);
list($data['device_manufacturers_labels'], $data['device_manufacturers_values']) = $this->getDevicePie($mysql);
$this->renderAdminPage('report/view_report_devices', $data);
// echo 'Ameye Olu';
}
private function getDevicePie($mysql) {
$labels = [];
$values = [];
$query = $this->read_replica->query($mysql);
foreach ($query->result() as $row) {
$record = array_values((array) $row);
$labels[] = $record[2];
$values[] = $record[1];
}
return [$labels, $values];
}
public function trips() {
$this->load->helper('url');
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT b.id,a.count,CASE WHEN name IS NULL THEN 'Unknown' ELSE b.name END AS name FROM (SELECT transport_provider_id,count(*) AS count,sum(cost) AS cost FROM parsedemail_item GROUP BY transport_provider_id) AS a LEFT JOIN transport_providers b ON (b.id=a.transport_provider_id) ORDER by a.count DESC";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['vendor_count'] = $this->table->generate($query);
list($data['vendor_count_labels'], $data['vendor_count_values']) = $this->getDevicePie($mysql);
$mysql = "SELECT b.id,a.cost,CASE WHEN name IS NULL THEN 'Unknown' ELSE b.name END AS name FROM (SELECT transport_provider_id,count(*) AS count,sum(cost) AS cost FROM parsedemail_item GROUP BY transport_provider_id) AS a LEFT JOIN transport_providers b ON (b.id=a.transport_provider_id) ORDER by a.count DESC";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['vendor_cost'] = $this->table->generate($query);
list($data['vendor_cost_labels'], $data['vendor_cost_values']) = $this->getDevicePie($mysql);
$mysql = "SELECT c.transport_provider_id AS id,count(*) AS count,d.name FROM (SELECT a.transport_provider_id,b.member_id,count(*) AS count FROM parsedemail_item a, trackedemail_item b WHERE b.id=a.trackedemail_item_id GROUP BY a.transport_provider_id,b.member_id) AS c LEFT JOIN transport_providers d ON (d.id=c.transport_provider_id) GROUP BY c.transport_provider_id,d.name ORDER BY count(*) DESC";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['vendor_members'] = $this->table->generate($query);
list($data['vendor_members_labels'], $data['vendor_members_values']) = $this->getDevicePie($mysql);
$this->renderAdminPage('report/view_report_trips', $data);
// echo 'Ameye Olu';
}
public function oauth2pulls() {
$this->load->library('table');
$this->table->set_template($this->template);
$member_id = (int) $this->input->get('member_id');
$oauth2_token_id = (int) $this->input->get('oauth2_token_id');
$params = $this->getOauth2PullsValue();
$params['oauth2_pulls_params'] = array_filter($params['oauth2_pulls_params']);
$errors = $this->validateForOauth2Pulls($params['oauth2_pulls_params']);
$data = $params;
$condition_query = $this->generateWhereConditionForOauth2Pulls($params['oauth2_pulls_params'], $errors);
$oauth2_provider_id = 1; // gmail
$mysql =
" SELECT count(tmp.email) AS all_count
FROM (
SELECT
e.email,
d.member_id,
d.last_run,
round(((extract(epoch from now())-extract(epoch from d.last_run))/3600)::numeric,2) AS elapsed,
d.oauth2_token_id
FROM
(
SELECT a.id AS member_id,
(
SELECT max(b.created)
FROM oauth2_pull_jobs b
WHERE b.member_id=a.id
AND b.created IS NOT NULL
) AS last_run,
(
SELECT c.id
FROM oauth2_tokens c
WHERE c.member_id=a.id
AND c.oauth2_provider_id=${oauth2_provider_id}
ORDER BY c.updated DESC LIMIT 1
) AS oauth2_token_id
FROM members a
WHERE a.id>0
) AS d,
members e
WHERE d.oauth2_token_id>0 AND e.id=d.member_id
) tmp
";
$mysql .= $condition_query;
$query = $this->read_replica->query($mysql);
// pagination
$this->load->library('pagination');
$config["total_rows"] = $query->result_array()[0]['all_count'];
$config["base_url"] = base_url() . "/bkoreport/oauth2pulls";
$config["suffix"] = '?' . http_build_query($params['oauth2_pulls_params']);
$config["first_url"] = '/bkoreport/oauth2pulls/0?' . http_build_query($params['oauth2_pulls_params']);
$config["per_page"] = 10;
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config['full_tag_open'] = "<ul class='pagination'>";
$config['full_tag_close'] = "</ul>";
$config['num_tag_open'] = '<li>';
$config['num_tag_close'] = '</li>';
$config['cur_tag_open'] = "<li class='disabled'><li class='active'><a href='#'>";
$config['cur_tag_close'] = "<span class='sr-only'></span></a></li>";
$config['next_tag_open'] = "<li>";
$config['next_tagl_close'] = "</li>";
$config['prev_tag_open'] = "<li>";
$config['prev_tagl_close'] = "</li>";
$config['first_tag_open'] = "<li>";
$config['first_tagl_close'] = "</li>";
$config['last_tag_open'] = "<li>";
$config['last_tagl_close'] = "</li>";
$this->pagination->initialize($config);
$page = ( $this->uri->segment(3) ) ? $this->uri->segment(3) : 0;
$page = is_numeric($page) ? $page : 0;
// pagination
$mysql =
"SELECT '<button type=\"button\" class=\"btn btn-info btn-xs\" onclick=\"return viewStats('||member_id||')\">Member</button>' AS Member,
'<button type=\"button\" class=\"btn btn-danger btn-xs\" onclick=\"return viewPulls('||member_id||','||oauth2_token_id||')\">View</button>' AS View,
CONCAT(firstname,' ',lastname,' (',CASE WHEN email='' THEN username ELSE email END,')') AS name,
member_id,
last_run,
round(((extract(epoch from now())-extract(epoch from last_run))/3600)::numeric,2) AS elapsed,
oauth2_token_id
FROM
(
SELECT
e.email,
e.firstname,
e.lastname,
e.username,
d.member_id,
d.last_run,
round(((extract(epoch from now())-extract(epoch from d.last_run))/3600)::numeric,2) AS elapsed,
d.oauth2_token_id
FROM
(
SELECT a.id AS member_id,
(
SELECT max(b.created)
FROM oauth2_pull_jobs b
WHERE b.member_id=a.id
AND b.created IS NOT NULL
) AS last_run,
(
SELECT c.id
FROM oauth2_tokens c
WHERE c.member_id=a.id
AND c.oauth2_provider_id=${oauth2_provider_id}
ORDER BY c.updated DESC LIMIT 1
) AS oauth2_token_id
FROM members a
WHERE a.id>0
) AS d,
members e
WHERE d.oauth2_token_id>0 AND e.id=d.member_id
) tmp " . $condition_query .
"ORDER BY last_run
LIMIT " . $config["per_page"] . " OFFSET " . $page . " ";
$query = $this->read_replica->query($mysql);
//$this->table->set_heading( array('data' => 'ID', 'style' => 'width:50px'),'Email', 'Firstname', 'Lastname','Last Login','Location', array('data' => 'ACTION', 'style' => 'width:40px'), array('data' => 'Select', 'style' => 'width:40px'));
$data['oauth2_pulls'] = $this->table->generate($query);
$data["oauth2_pulls_links"] = $this->pagination->create_links();
if ($member_id < 1) {
$query = $this->read_replica->query($mysql);
$rows = $query->result();
if ($rows && count($rows) > 0) {
$record = array_values((array) $rows[0]);
$member_id = $record[0];
$oauth2_token_id = $record[3];
}
}
if ($member_id > 0 && $oauth2_token_id > 0) {
$q = "SELECT CONCAT(firstname,' ',lastname,' (',CASE WHEN email='' THEN username ELSE email END,')') AS member FROM members WHERE id=${member_id}";
$r = $this->read_replica->query($q);
$data["member"] = $r->row_array();
$q = "SELECT round(extract(epoch from completed)::numeric-extract(epoch from started)::numeric,2) as duration,item_count,CONCAT(search_term,' ',search_from) AS vendor FROM oauth2_pull_job_threads WHERE oauth2_pull_job_id=(SELECT id FROM oauth2_pull_jobs WHERE member_id=${member_id} AND oauth2_token_id=${oauth2_token_id} AND completed IS NOT NULL ORDER BY completed DESC limit 1)";
$r = $this->read_replica->query($q);
$data['oauth2_threads'] = $this->table->generate($r);
list($data['oauth2_threads_duration_labels'], $data['oauth2_threads_duration_values']) = $this->getPullPie($q, 2, 0);
list($data['oauth2_threads_count_labels'], $data['oauth2_threads_count_values']) = $this->getPullPie($q, 2, 1);
}
$data['member_id'] = $member_id;
$this->renderAdminPage('report/view_report_pulls', $data);
}
private function getPullPie($mysql, $i, $j) {
$labels = [];
$values = [];
$query = $this->read_replica->query($mysql);
foreach ($query->result() as $row) {
$record = array_values((array) $row);
$labels[] = $record[$i];
$values[] = $record[$j];
}
return [$labels, $values];
}
public function addresssearch() {
$response = [];
$search = $this->input->post('search');
if (strlen($search) > 3) {
$q = "SELECT * FROM address WHERE lower(address) LIKE '%" . pg_escape_string(strtolower($search)) . "%'";
$r = $this->read_replica->query($q);
foreach ($r->result() as $row) {
$response[] = array("value" => $row->id, "label" => $row->address);
}
}
echo json_encode($response);
}
public function cards_clicked_report(){
$this->load->model('combo_model');
// POST data
$data['report'] = [];
$data['card_category'] = $this->combo_model->getCardCategoryCombo('card_category', '');
$this->renderAdminPage('report/view_report_cards_clicked_report', $data);
}
public function cards_clicked_report_datatables(){
$data = $this->get_biz_report_data($this->input->post(),'search');
header('Content-Type: application/json');
echo json_encode( $data );
}
public function cards_clicked_report_export_csv(){
$this->load->library('session');
$postData = $this->session->userdata("BIZ_PARAM");
if($postData){
$this->get_biz_report_data($postData,'export_csv');
}else{
echo 'not found!';
}
}
/**
* Report list signup email
* @return mixed
*/
public function signup_email_report( $data = array() ) {
$this->load->helper('url');
$params = $this->input->get();
$subject = 'Float - How it Works';
$sql = sprintf("
SELECT count(e), e.created_at::date as Day
from emails e
where subject like '%s'
", $subject );
if ( isset( $params['startdate'] ) and isset( $params['enddate'] ) ) {
$sql .= sprintf("and e.created_at between '%s' and '%s'", $params['startdate'], $params['enddate']);
} else {
$sql .= sprintf("and e.created_at >= date_trunc('month', CURRENT_DATE - interval '1' month)");
}
// group and order
$sql .= sprintf("group by Day order by Day desc");
$results = $this->read_replica->query( $sql )->result_array();
// $reports = array();
// if ( $results ) {
// foreach ( $results as $value => $result ) {
// $email = explode( ',', preg_replace( '/.*?(\{)|(\})/m', '', $result['email'] ) );
// $reports[ $result['day'] ]['email'][] = $email;
// }
// }
$data['signup_emails'] = $results;
$this->renderAdminPage('report/view_signup_emails', $data);
}
/**
* Signup Email - view details by date
* @return json
*/
public function signup_email_report_details() {
$payload = $this->input->get();
$page = $payload['page'] ?? $this->uri->segment(3);
$limit = 20;
$sql = sprintf("
SELECT row_number() OVER () as no, to_emails as email
from emails
where created_at::date = '%s'
", $payload['date']);
$results = $this->read_replica->query( $sql )->result_array();
// load table
$this->load->library( 'table' );
$this->table->set_template( $this->template );
$this->table->set_heading([
'No.', 'Email'
]);
$link = $this->load_pagination( count( $results ), array( 'date' => $payload['date'] ), 'signup_email_report_details', $limit );
$sql .= sprintf("limit %d offset %d", $limit, (int) $page);
$results = $this->read_replica->query( $sql )->result_array();
// remove {} character
$content = array();
array_map( function($var) use (&$content) {
$var['email'] = preg_replace( '/.*?(\{)|(\})/m', '', $var['email'] );
array_push( $content, array( 'no' => $var['no'], 'email' => $var['email'] ) );
}, $results );
$content = $this->table->generate( $content );
header( 'Content-Type: application/json' );
echo json_encode( array( 'pagination' => $link, 'results' => $content ) );
}
public function get_biz_report_data($postData, $mode = 'search'){
$this->load->library('session');
$this->session->set_userdata("BIZ_PARAM", $postData);
## Read value
$from = DateTime::createFromFormat('m/d/Y', '1/1/1970');
$to = (new DateTime());
$date_range = $postData['date_range']??'';
if(!empty($date_range)){
$date_range_part = array_map('trim',explode('-',$date_range));
$from = DateTime::createFromFormat('m/d/Y', $date_range_part[0]);
$to = DateTime::createFromFormat('m/d/Y', $date_range_part[1]);
}
$draw = $postData['draw']??0;
$start = $postData['start'];
$rowperpage = $postData['length']; // Rows display per page
$columnIndex = $postData['order'][0]['column']??0; // Column index
$columnName = $postData['columns'][$columnIndex]['data']??''; // Column name
$columnSortOrder = $postData['order'][0]['dir']; // asc or desc
$order_by = " ORDER BY $columnName $columnSortOrder";
$searchValue = $postData['search']['value']??''; // Search value
# where
$where = "date(m.added) >='".$from->format('Y-m-d')."' and date(m.added)<='".$to->format('Y-m-d')."'";
if(!empty($postData['card_category'])){
$where.= " AND c.button1_action = '".pg_escape_string($postData['card_category'])."'";
}
if(!empty($searchValue)){
$where.= " AND c.title like '%".pg_escape_string($searchValue)."%'";
}
$limit = '';
if($mode == 'search'){
$limit = " LIMIT $rowperpage OFFSET $start";
}
$mysql = "SELECT c.title, m.card_id, COUNT(m.card_id) AS cards_total
FROM members_cardclicktrack m
LEFT JOIN main_cards c ON c.id=m.card_id
WHERE $where
GROUP BY c.title,m.card_id
$order_by $limit";
$data = [];
## Response
if($mode == 'export_csv'){
$report = $this->read_replica->query($mysql);
$this->biz_export_csv($report);
exit;
}else{
$report = $this->read_replica->query($mysql)->result_array();
}
$mysql_count = "SELECT COUNT(grouped) as count from (SELECT COUNT(m.card_id)
FROM members_cardclicktrack m
LEFT JOIN main_cards c ON c.id=m.card_id
WHERE $where
GROUP BY c.title,m.card_id) as grouped";
$totalRecords = $this->read_replica->query($mysql_count)->row_array()['count'];
$totalRecordwithFilter = $totalRecords;
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $report
);
return $response;
}
public function biz_export_csv($rs){
set_time_limit(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=cards-clicked-report-' . date("Y-m-d") . '.csv');
header('Cache-Control: max-age=0');
if (ob_get_contents())
ob_end_clean();
$fp = fopen('php://output', 'w');
$i = 0;
while ($row = $rs->unbuffered_row()) {
// header
if ($i == 0) {
fputcsv($fp, array_keys((array) $row));
}
// records
fputcsv($fp, (array) $row);
$i++;
}
fclose($fp);
exit;
}
public function totalquotes() {
global $savvyext;
$data = [];
$data["days"] = 1;
$data["result"] = [["ID", "Name", "Total", "Success", "Failure"]];
if ($this->input->post()) {
$data["days"] = $this->input->post('days');
$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
$api_url = "http://automation.float.sg";
$url = $api_url . "/api/statistics/" . ((int) $data["days"]);
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_VERBOSE, false);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/json',
'Authorization: Server-Token ' . $httpAuthToken)
);
$body = curl_exec($ch);
$data["result"] = array_merge($data["result"], json_decode($body, true));
}
$this->load->library('table');
$this->table->set_template($this->template);
$data["total_quotes_table"] = $this->table->generate($data["result"]);
$this->renderAdminPage('report/view_report_totalquotes', $data);
}
public function quotes() {
$data = [];
$this->load->model('combo_model');
$data["autofrom"] = "";
$data["from"] = 0;
$data["autoto"] = "";
$data["to"] = 0;
$data["from_country"] = "";
$data["to_country"] = "";
$data["from_postal"] = "";
$data["to_postal"] = "";
if ($this->input->post()) {
$data["autofrom"] = $this->input->post('autofrom');
$data["from"] = (int) $this->input->post('from');
$data["autoto"] = $this->input->post('autoto');
$data["to"] = (int) $this->input->post('to');
$data["from_country"] = $this->input->post('card_from_country');
$data["to_country"] = $this->input->post('card_to_country');
$data["from_postal"] = $this->input->post('from_postal');
$data["to_postal"] = $this->input->post('to_postal');
}
$data["card_from_country"] = $this->combo_model->getCountryCombo("card_from_country", $data["from_country"]);
$data["card_to_country"] = $this->combo_model->getCountryCombo("card_to_country", $data["to_country"]);
$this->load->library('pagination');
$this->load->library('table');
$this->table->set_template($this->template);
$q = " SELECT count(*) AS count
FROM (
SELECT count(*), q.location_start_id, q.location_end_id
FROM quotes q
JOIN address location_start ON location_start.id = q.location_start_id
JOIN address location_end ON location_end.id = q.location_end_id
WHERE cost > 0 ";
if ($data["from"] > 0) {
$q .= " AND q.location_start_id = " . $data["from"];
}
if ($data["to"] > 0) {
$q .= " AND q.location_end_id = " . $data["to"];
}
if ( ! empty($data["from_country"])) {
$q .= " AND lower(location_start.country) = '" . strtolower(pg_escape_string($data["from_country"])) . "'";
}
if ( ! empty($data["to_country"])) {
$q .= " AND lower(location_end.country) = '" . strtolower(pg_escape_string($data["to_country"])) . "'";
}
if ( ! empty($data["from_postal"])) {
$q .= " AND location_start.postal LIKE '%" . pg_escape_string($data["from_postal"]) . "%'";
}
if ( ! empty($data["to_postal"])) {
$q .= " AND location_end.postal LIKE '%" . pg_escape_string($data["to_postal"]) . "%'";
}
$q .= " GROUP BY q.location_start_id, q.location_end_id) AS a";
$r = $this->read_replica->query($q);
$f = $r->result_array()[0];
$config = array();
$config["total_rows"] = $f["count"];
$config["base_url"] = base_url() . "/bkoreport/quotes";
$config["per_page"] = 50;
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config['full_tag_open'] = "<ul class='pagination'>";
$config['full_tag_close'] = "</ul>";
$config['num_tag_open'] = '<li>';
$config['num_tag_close'] = '</li>';
$config['cur_tag_open'] = "<li class='disabled'><li class='active'><a href='#'>";
$config['cur_tag_close'] = "<span class='sr-only'></span></a></li>";
$config['next_tag_open'] = "<li>";
$config['next_tagl_close'] = "</li>";
$config['prev_tag_open'] = "<li>";
$config['prev_tagl_close'] = "</li>";
$config['first_tag_open'] = "<li>";
$config['first_tagl_close'] = "</li>";
$config['last_tag_open'] = "<li>";
$config['last_tagl_close'] = "</li>";
$this->pagination->initialize($config);
$page = ( $this->uri->segment(3) ) ? $this->uri->segment(3) : 0;
$page = is_numeric($page) ? $page : 0;
//"SELECT count(*),location_start_id,location_end_id FROM quotes GROUP BY location_start_id,location_end_id;"
$q = "SELECT count(*) AS \"Quotes\",'<button type=\"button\" class=\"btn btn-danger btn-xs\" onclick=\"return viewQuotes('||a.location_start_id||','||a.location_end_id||')\">View</button>' AS \"View\",";
$q .= " b.address AS \"Origin\", c.address AS \"Destination\", b.country AS \"Origin Country\", b.postal AS \"Origin Postal\",";
$q .= " c.country AS \"Dest.Country\", c.postal AS \"Dest.Postal\"";
$q .= " FROM quotes a ";
$q .= " LEFT JOIN address b ON (b.id=a.location_start_id)";
$q .= " LEFT JOIN address c ON (c.id=a.location_end_id)";
$q .= " WHERE a.cost>0 ";
if ($data["from"] > 0) {
$q .= " AND a.location_start_id=" . $data["from"];
}
if ($data["to"] > 0) {
$q .= " AND a.location_end_id=" . $data["to"];
}
if ( ! empty($data["from_country"])) {
$q .= " AND lower(b.country) = '" . strtolower(pg_escape_string($data["from_country"])) . "'";
}
if ( ! empty($data["to_country"])) {
$q .= " AND lower(c.country) = '" . strtolower(pg_escape_string($data["to_country"])) . "'";
}
if ( ! empty($data["from_postal"])) {
$q .= " AND b.postal LIKE '%" . pg_escape_string($data["from_postal"]) . "%'";
}
if ( ! empty($data["to_postal"])) {
$q .= " AND c.postal LIKE '%" . pg_escape_string($data["to_postal"]) . "%'";
}
$q .= " GROUP BY a.location_start_id,a.location_end_id,";
$q .= " b.address,b.country,b.postal,";
$q .= " c.address,c.country,c.postal ";
$q .= " LIMIT " . $config["per_page"] . " OFFSET " . $page . " ";
//echo '<textarea>'.$q.'</textarea>';
$r = $this->read_replica->query($q);
$data["quotes_table"] = $this->table->generate($r);
$data["links"] = $this->pagination->create_links();
$q = "SELECT id,address FROM address ORDER BY address";
$query = $this->read_replica->query($q);
$address = [];
$address[0] = '&lt;&lt;All&gt;&gt;';
foreach ($query->result() as $row) {
$address[$row->{"id"}] = $row->{"address"} == '' ? 'Empty address' : $row->{"address"};
}
$data["address"] = $address;
$this->renderAdminPage('report/view_report_quotes', $data);
}
public function setFormRuleForQuote() {
$this->form_validation->set_rules('transport_provider', 'Transport Provider', 'integer');
$this->form_validation->set_rules('from', 'from', 'integer');
$this->form_validation->set_rules('to', 'to', 'integer');
$this->form_validation->set_rules('prefill', 'Prefill', 'regex_match[/^(?:[0-1])$/]');
}
public function getValueOfQuote() {
return [
'country' => trim($this->input->get('card_country')
?? $this->input->get('country')),
'transport_provider' => trim($this->input->get('card_transport_provider')
?? $this->input->get('transport_provider')),
'from' => trim($this->input->get('from')),
'address_from' => trim($this->input->get('address_from')),
'to' => trim($this->input->get('to')),
'address_to' => trim($this->input->get('address_to')),
'prefill' => trim($this->input->get('card_prefill')
?? $this->input->get('prefill')),
'quote' => trim($this->input->get('quote')),
'address_to' => trim($this->input->get('address_to')),
'export' => trim($this->input->get('export')),
'sql_raw' => trim($this->input->get('sql_raw')),
'message' => trim($this->input->get('message')),
];
}
public function validateValueForQuote($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForQuote();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function setComboForQuote($params) {
$this->load->model('combo_model');
$combo['card_country'] =
$this
->combo_model
->getCountryCombo('card_country', $params['country']);
$combo['card_transport_provider'] =
$this
->combo_model
->getTransportProvidersCombo(
'card_transport_provider',
$params['transport_provider']
);
$combo['card_prefill'] =
$this
->combo_model
->getQuoteSourceCombo(
'card_prefill',
$params['prefill']
);
return $combo;
}
public function quote() {
$this->load->model('bko_report_model');
$this->load->library('table');
$this->table->set_template($this->template_small);
$this->table->set_heading(
'Check Quote',
'Created',
'Completed',
'Travel Date',
'Cost Raw',
'Cost',
'Data Source',
'Transport Provider',
'Origin',
'Destination',
'Origin Country',
'Origin Postal'
);
$params = $this->getValueOfQuote();
$data = $this->setComboForQuote($params);
if (($params['quote']) !== '') {
$params['message'] = $this->runQuote($params);
}
if (isset($params['address_from'])) {
$params['location_start_id'] = $this->get_address($params['address_from']);
}
if (isset($params['address_to'])) {
$params['location_end_id'] = $this->get_address($params['address_to']);
}
$params = array_filter($params, function($val) {
return $val !== "";
});
$errors = $this->validateValueForQuote($params);
$params = array_diff_key($params, $errors);
$quote_records = $this->bko_report_model->count_quote_records($params);
// remove from param url
if (isset($params['location_start_id'])) {
unset($params['location_start_id']);
}
if (isset($params['location_end_id'])) {
unset($params['location_end_id']);
}
$data = array_merge(
$data,
$params,
$this->load_pagination(
$quote_records,
$params,
'quote'
)
);
$data['quotes_table'] = $this->table->generate(
$this->bko_report_model->get_quote_records(
$params,
$data['limit'],
$data['offset']
)
);
$this->renderAdminPage('report/view_report_quote', $data);
}
public function exportCSV() {
$this->load->model('bko_report_model');
$this->load->helper('export_csv');
$params = $this->getValueOfQuote();
if ( ! empty($params['address_from'])) {
$params['location_start_ids'] = $this->get_address($params['address_from']);
}
if ( ! empty($params['address_to'])) {
$params['location_end_ids'] = $this->get_address($params['address_to']);
}
$params = array_filter($params, function($val, $key) {
return $val !== "" || $key === 'message';
}, ARRAY_FILTER_USE_BOTH);
$errors = $this->validateValueForQuote($params);
$params = array_diff_key($params, $errors);
$query = $this->bko_report_model->get_query_export_quote_records(
$params
);
$r = $this->read_replica->query($query);
if ($r->result_array()[0]['all_count'] === '0') {
$params['message'] = 'Data Not Found !!!';
redirect('/bkoreport/quote/?' . http_build_query($params) );
return;
}
$r = $this->read_replica->query(
$this->bko_report_model->get_query_export_quote_records(
$params,
self::NONE_COUNT_RECORD
)
);
export($r);
}
public function checkQuote() {
$id = (int) $this->input->get('id');
if ($id < 1) {
echo "Invalid ID";
return;
}
$q = "SELECT * FROM quotes WHERE id=" . $id;
$r = $this->read_replica->query($q);
if ($r->num_rows()) {
// OK
} else {
echo "Quote not found";
return;
}
list($payload, $decrypted, $result, $body) = $this->main_api_get('/trips/api/quote/', $id);
if (is_array($payload) && array_key_exists('id', $payload) && $payload['id'] > 0) {
if (array_key_exists('cost', $payload) && $payload['cost'] > 0) {
echo 'Quote is ' . sprintf("%0.02f", $payload['cost']);
return;
}
if (array_key_exists('completed', $payload) && $payload['completed'] != '') {
echo "<pre>";
var_dump($payload);
echo "</pre>";
echo "Quote service check failed";
return;
}
} else {
echo "Quote check failed";
return;
}
echo "N/A";
}
protected function runQuote($data) {
$message = 'Unhandled exception';
if ($data['country'] == '' || $data['transport_provider'] < 1 || $data['address_from'] == '' || $data['address_to'] == '') {
return 'Invalid input';
}
if ($data['country'] == 'SG') {
if ($data['transport_provider'] < 4 || $data['transport_provider'] > 5) {
return 'Unsupported transport provider';
}
} else if ($data['country'] == 'US') {
if ($data['transport_provider'] < 1 || $data['transport_provider'] > 2) {
return 'Unsupported transport provider';
}
} else {
return 'Unsupported country';
}
// Step 1: Geocode addresses
$params = [
"address" => $data['address_from'],
"member_id" => 0,
"country" => $data['country']
];
$input = http_build_query($params);
list($payload, $decrypted, $result, $body) = $this->main_api_get('/trips/api/geocode/?', $input);
if (!is_array($payload) || !array_key_exists('geocode', $payload) || !is_array($payload['geocode']) || !array_key_exists('id', $payload['geocode']) || $payload['geocode']['id'] < 1) {
return 'Failed to geocode address from';
}
$location_start = $payload['geocode']['address'];
$location_start_id = $payload['geocode']['id'];
$params["address"] = $data['address_to'];
$input = http_build_query($params);
list($payload, $decrypted, $result, $body) = $this->main_api_get('/trips/api/geocode/?', $input);
if (!is_array($payload) || !array_key_exists('geocode', $payload) || !is_array($payload['geocode']) || !array_key_exists('id', $payload['geocode']) || $payload['geocode']['id'] < 1) {
return 'Failed to geocode address to';
}
$location_end = $payload['geocode']['address'];
$location_end_id = $payload['geocode']['id'];
// Step 2: Run quote
$endpoint = '/trips/api/quote';
$payload = "{
\"origin\":\"" . $location_start . "\",
\"destination\":\"" . $location_end . "\",
\"member_id\":13,
\"transport_provider_id\":" . $data['transport_provider'] . ",
\"trackedemail_item_id\":0,
\"country\":\"" . $data['country'] . "\",
\"group_quote_id\":0,
\"prefill\":\"t\"
}";
list($payload, $decrypted, $result, $body) = $this->main_api_post($endpoint, $payload);
if (is_array($payload) && array_key_exists('id', $payload) && $payload['id'] > 0) {
if (array_key_exists('cost', $payload) && $payload['cost'] > 0) {
return 'Quote is ' . $payload['cost'];
}
if (array_key_exists('completed', $payload) && $payload['completed'] != '') {
return "Quote service failed";
}
$startTime = time();
$sleeps = [1, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4]; // 43 + 11 ~ 54
foreach ($sleeps as $sec) {
sleep($sec);
list($payload, $decrypted, $result, $body) = $this->main_api_get('/trips/api/quote/', $payload['id']);
if (is_array($payload) && array_key_exists('id', $payload) && $payload['id'] > 0) {
if (array_key_exists('cost', $payload) && $payload['cost'] > 0) {
return 'Quote is ' . $payload['cost'];
}
if (array_key_exists('completed', $payload) && $payload['completed'] != '') {
return "Quote service check failed";
}
} else {
return "Quote check failed";
}
}
$message = "Quote check timed out";
} else {
$message = 'Failed to schedule quote';
}
return $message;
}
protected function get_address($address) {
$result = [];
$q = "SELECT id FROM address WHERE address ILIKE '%" . pg_escape_string($address) . "%'";
$r = $this->read_replica->query($q);
foreach ($r->result() as $row) {
$result[] = $row->id;
}
return $result;
}
public function heatmap() {
$q = "SELECT SUM(num) AS total, lat, lng FROM ( ";
$q .= "SELECT COUNT(*) AS num, ROUND(b.latitude,2) AS lat, ROUND(b.longitude,2) AS lng FROM parsedemail_item a LEFT JOIN address b ON b.id=a.location_start_id WHERE b.timezone=1 AND a.private='f' AND a.updated>'2019-11-13 00:00' GROUP BY b.latitude, b.longitude ";
$q .= " UNION ";
$q .= "SELECT COUNT(*) AS num, ROUND(c.latitude,2) AS lat, ROUND(c.longitude,2) AS lng FROM parsedemail_item a LEFT JOIN address c ON c.id=a.location_end_id WHERE c.timezone=1 AND a.private='f' AND a.updated>'2019-11-13 00:00' GROUP BY c.latitude, c.longitude ";
$q .= " ) AS foo GROUP BY foo.lat,foo.lng ORDER BY total DESC";
$r = $this->read_replica->query($q);
foreach ($r->result() as $row) {
$item = (array) $row;
if ($item["lat"]==='' || $item["lng"]==='') continue;
if ($item["lat"]==0 && $item["lng"]==0) continue;
$top_locations[] = $item;
}
$data["top_locations"] = $top_locations;
$data["center_lat"] = 1.351616;
$data["center_lng"] = 103.808053;
$data["googleKey"] = "AIzaSyDvjiRTxngOQyBP4zpqFlZuiquc0ROvo9c";
$this->renderAdminPage('report/view_report_heatmap', $data);
}
public function surgereport() {
$data['page_title'] = "Surge Report";
$data['report_parsedemail'] = '';
$data['pagination_link'] = '';
$params = $this->input->get();
if (!empty($params)) {
$this->form_validation->set_data($params);
$this->setSurgereportSearchRules();
if ($this->form_validation->run() == false) {
$this->renderAdminPage('report/view_report_parsedemail', $data);
return;
}
}
$this->load->model('parsedEmailItem_model');
$query = $this->parsedEmailItem_model->getSureReportQuery($params);
$this->load->library('table');
$this->table->set_heading(
['data' => 'ID', 'style' => 'width:10px'],
'ID',
'Email',
'Travel Date',
'Duration',
'Cost Raw',
'Email ID',
'Cost',
'Update',
'Distance',
'Surge',
'Alternative',
'Insights'
);
$tableData = $this->returnAdminTable(
[
'count_query' => $query,
'query' => $query,
],
'bkoreport/surgereport',
[
'per_page' => 100,
'reuse_query_string' => TRUE,
]
);
$data['report_parsedemail'] = $tableData['output_table'];
$data['pagination_link'] = $tableData['links'];
$this->renderAdminPage('report/view_report_parsedemail', $data);
}
public function tripinsights() {
global $savvyext;
$data = [];
$id = $this->input->get('id');
$member_id = $this->input->get('member_id');
$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
$encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
$encryptionKey = $savvyext->cfgReadChar('encryption.key');
$encryptionIV = $savvyext->cfgReadChar('encryption.iv');
$baseURL = $savvyext->cfgReadChar('system.api_url');
$payload = "{\"trackedemail_item_id\":9518790,\"member_id\":13}";
$payload = "{\"trackedemail_item_id\":${id},\"member_id\":${member_id}}";
$encrypted_payload = bin2hex(
openssl_encrypt(
$payload, $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
));
$postdata = "{\"encrypted_payload\": \"${encrypted_payload}\"}";
$url = $baseURL . "/trips/api/tripinsights";
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_VERBOSE, false);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/json',
'Content-Length: ' . strlen($postdata),
'Authorization: Server-Token ' . $httpAuthToken,
"client_id: BackOffice"
)
);
$body = curl_exec($ch);
$result = json_decode($body, true);
$payload = openssl_decrypt(
hex2bin(
$result['payload']
), $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
);
$data["payload"] = json_decode($payload, true);
if ($this->input->is_ajax_request()) {
$this->load->view('admin/report/view_report_trip_insights', $data);
}else{
$this->renderAdminPage('report/view_report_trip_insights', $data);
}
}
// oauth2 pulls
public function validateForOauth2Pulls($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForOauth2Pulls();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function setFormRuleForOauth2Pulls() {
// $date_pattern = 'regex_match[/([0-9]{1,2}\-\[0-9]{1,2}\-\[0-9]{4})?/]';
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules('from_last_run', 'Start Date', $date_pattern);
$this->form_validation->set_rules('to_last_run', 'End Date', $date_pattern);
$this->form_validation->set_rules('from_elapsed', 'Elapsed', 'numeric');
$this->form_validation->set_rules('to_elapsed', 'Elapsed', 'numeric');
$this->form_validation->set_rules('oauth2_token_id', 'Oauth2 Token ID', 'numeric');
$this->form_validation->set_rules('member_id', 'Member ID', 'numeric');
}
public function getOauth2PullsValue() {
return
[
'oauth2_pulls_params' =>
[
'from_last_run' => $this->getValueOfParam('from_last_run'),
'to_last_run' => $this->getValueOfParam('to_last_run'),
'from_elapsed' => $this->getValueOfParam('from_elapsed'),
'to_elapsed' => $this->getValueOfParam('to_elapsed'),
'oauth2_token_id' => $this->getValueOfParam('oauth2_token_id'),
'member_id' => $this->getValueOfParam('member_id'),
'email' => $this->getValueOfParam('email'),
]
];
}
public function getValueOfParam($param) {
return trim(empty($this->input->post($param))
? $this->input->get($param)
: $this->input->post($param));
}
public function generateWhereConditionForOauth2Pulls($params, $errors) {
foreach($params as $key => $value) {
if (array_key_exists($key, $errors)) {
continue;
}
$key = strpos($key, 'last_run') !== FALSE ? "DATE(${key})" : $key;
if (strpos($key, 'from_') !== FALSE) {
$key = str_replace('from_', '', $key);
$this->read_replica->where($key . '>=', $value);
} else if (strpos($key, 'to_')!== FALSE) {
$key = str_replace('to_', '', $key);
$this->read_replica->where($key . '<=', $value);
} else if (strpos($key, 'email')!== FALSE) {
$this->read_replica->like('lower('. $key . ')', strtolower($value));
} else {
$this->read_replica->where($key, $value);
}
}
return str_replace('SELECT *', '', $this->read_replica->get_compiled_select());
}
private function setSurgereportSearchRules()
{
$config = [
[
'field' => 'travel_date',
'label' => 'Travel date',
'rules' => 'trim',
],
[
'field' => 'duration_from',
'label' => 'Duration from',
'rules' => 'trim|numeric',
],
[
'field' => 'duration_to',
'label' => 'Duration to',
'rules' => 'trim|numeric',
],
[
'field' => 'distance_from',
'label' => 'Distance from',
'rules' => 'trim|numeric',
],
[
'field' => 'distance_to',
'label' => 'Distance to',
'rules' => 'trim|numeric',
],
[
'field' => 'cost_from',
'label' => 'Cost from',
'rules' => 'trim|numeric',
],
[
'field' => 'cost_to',
'label' => 'Cost to',
'rules' => 'trim|numeric',
],
[
'field' => 'email',
'label' => 'Email',
'rules' => 'trim|valid_emails',
],
];
$this->form_validation->set_rules($config);
}
public function upload()
{
$this->load->helper('upload_file');
upload_file();
}
public function importCSV()
{
$this->load->helper('directory');
$row = 0;
$file_path = $_SERVER['DOCUMENT_ROOT']
. DIRECTORY_SEPARATOR
. self::TEMP_DIRECTORY_UPLOAD;
$file_name = scandir($file_path);
if ( ! $file_name) {
echo json_encode([
'code' => '404',
'message' => 'File Not Found !!!'
]);
}
if (($handle = fopen($file_path . '/' . $file_name[2], "r")) !== FALSE) {
deleteDir($file_path);
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
//if ($row>2) break;
$num = count($data);
$f = [];
for ($c=0; $c < $num; $c++) {
$f[self::CSV_FIELDS[$c]] = $data[$c];
}
if ($row>0) {
$this->save_record($f);
}
$row++;
}
fclose($handle);
}
echo json_encode([
'code' => '201',
'message' => 'Imported Successfully !!!'
]);
}
function save_record($f) {
$member_id = $this->input->post()['member_id'];
$location_start_id = $this->insert_and_get_address($f,'start');
$location_end_id = $this->insert_and_get_address($f,'end');
if ($location_start_id<1 || $location_end_id<1) {
echo "Invalid address\n";
return NULL;
}
$q =
"INSERT INTO quotes (
transport_provider_id,
automation_id,
cost_raw,
cost,
created,
completed,
travel_date,
member_id,
quote_group_id,
location_start_id,
location_end_id,
prefill
) VALUES (";
$q.= $this->formatInteger($f["transport_provider_id"]) . ",";
$q.= $this->formatInteger($f["automation_id"]) . ",";
$q.= $this->formatString($f["cost_raw"]) . ",";
$q.= $this->formatFloat($f["cost"]) . ",";
$q.= $this->formatDate($f["created"]) . ",";
$q.= $this->formatDate($f["completed"]) . ",";
$q.= $this->formatDate($f["travel_date"]) . ",";
$q.= $member_id . ","; /* formatInteger($f["member_id"]) . ","; */
$q.= "NULL,"; /* formatInteger($f["quote_group_id"]) . ","; */
$q.= $location_start_id . "," . $location_end_id;
$q.= ",'t'
) RETURNING id";
$r = $this->db->query($q)->result_array();
if (count($r) === 0) {
echo $q."\n";
return;
}
}
function insert_and_get_address($f,$what) {
$tzid = $this->get_timezone($f,$what);
$q = " SELECT *
FROM address
WHERE
lower(address)=lower(".$this->formatString($f["location_${what}_address"]).") ";
$q.= " AND latitude = " . $this->formatFloat($f["location_${what}_lat"]) ;
$q.= " AND longitude = " . $this->formatFloat($f["location_${what}_lng"]);
$r = $this->read_replica->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
}
/*
id | bigint | | not null | nextval('address_id_seq'::regclass)
address | character varying(200) | | |
latitude | numeric | | |
longitude | numeric | | |
timezone | integer | | |
geocoding_date | date | | |
postal | character varying(40) | | |
country | character varying(2) | | | 'SG'::character varying
geometry | geography(Point,4326) | | |
description | character varying(100) | | |
*/
$q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry,description) VALUES(";
$q.= $this->formatString($f["location_${what}_address"]) . ",";
$q.= $this->formatFloat($f["location_${what}_lat"]) . ",";
$q.= $this->formatFloat($f["location_${what}_lng"]) . ",";
$q.= $tzid . ",";
$q.= "NOW()" . ",";
$q.= $this->formatString($f["location_${what}_postal"]) . ",";
$q.= $this->formatString($f["location_${what}_country"]) . ",";
$q.= "st_setsrid(st_point(".$f["location_${what}_lng"] . ",";
$q.= $f["location_${what}_lat"]."), 4326)" . ",";
$q.= $this->formatString($f["location_${what}_description"]);
$q.= ") RETURNING id";
$r = $this->db->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
} else {
echo $q . "\n";
}
return NULL;
}
function get_timezone($f,$what) {
$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower(".$this->formatString($f["location_${what}_tz"]).")";
$r = $this->read_replica->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
}
$q = "INSERT INTO address_timezone (timezone) VALUES(".$this->formatString($f["location_${what}_tz"]).") RETURNING id";
$r = $this->db->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
}
return NULL;
}
function formatDate($value) {
return empty($value) ? "NULL" : "'".pg_escape_string($value)."'";
}
function formatInteger($value) {
return empty($value) ? "NULL" : (int)$value;
}
function formatFloat($value) {
return empty($value) ? "NULL" : floatval($value);
}
function formatString($value) {
return trim($value)===""?"NULL":("'" . pg_escape_string($value) . "'");
}
}
Reference in New Issue View Git Blame Copy Permalink