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

525 lines
18 KiB
PHP
Raw Permalink Blame History

<?php
defined('BASEPATH') or exit('No direct script access allowed');
class Advice extends Admin_Controller
{
const CSV_FIELDS = [
'travel_date', // | 2019-11-19 08:58:00
'duration', // | 26
'cost_raw', // | SGD 13.00
'cost', // | 13.00
'updated', // | 2019-11-19 03:53:48.809183
'distance', // | 10.77
'transport_provider_id', // | 3
'travel_date_end', // | 2019-11-19 09:24:55
'location_start_id', // | 7276
'location_end_id', // | 7277
'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 __construct()
{
parent::__construct();
$this->load->model('advice_model');
$this->load->model('receipt_advice_model');
// Load form validation library
$this->load->library('form_validation');
// Load file helper
$this->load->helper('file');
}
public function index()
{
$data = array();
$this->load->helper('url');
$this->advicelist();
}
protected function renderAdvicePage($page_name, $data)
{
$this->load->view('admin/view_admin_header', $data);
$this->load->view('advice/' . $page_name, $data);
$this->load->view('admin/view_admin_footer', $data);
}
public function advicelist()
{
$data = [];
$data["page_title"] = "Advice List";
$data['advice_list'] = [];
$data['pagination_link'] = [];
$params = [];
$params = $this->input->get();
if (!empty($params)) {
$this->form_validation->set_data($params);
$this->setSearchRules();
if ($this->form_validation->run() == false) {
$this->renderAdvicePage("view_advice", $data);
return;
}
}
$query = $this->advice_model->getAdviceQuery($params);
$tableData = $this->returnAdminTable(
[
'count_query' => $query,
'query' => $query,
],
'advice/advicelist',
[
'per_page' => 100,
'reuse_query_string' => TRUE,
]
);
$data['advice_list'] = $tableData['output_table'];
$data['pagination_link'] = $tableData['links'];
$this->renderAdvicePage("view_advice", $data);
}
public function advicejson()
{
$id = $this->input->get('id');
if ($id == '' || $id < 1) {
echo '{"message":"Invalid ID"}';
return;
}
$q = "SELECT * FROM parsedemail_item WHERE id=" . $id;
$r = $this->read_replica->query($q);
$parsedemail_item = $r->row_array();
$q = "SELECT * FROM parsedemail_item_advice_google WHERE parsedemail_item_id = ${id}";
$r = $this->read_replica->query($q);
$advices = [];
foreach ($r->result() as $f) {
$advices[] = (array) $f;
}
foreach ($advices as $j => $advice) {
$q = "SELECT * FROM google_directions_legs WHERE parsedemail_item_advice_google_id = " . $advice["id"];
$r = $this->read_replica->query($q);
$legs = [];
foreach ($r->result() as $f) {
$legs[] = (array) $f;
}
foreach ($legs as $i => $leg) {
$q = "SELECT a.id AS step_id,a.distance,a.duration,a.travel_mode,a.location_start_lat,a.location_start_lng,a.location_end_lat,a.location_end_lng,a.html_instructions,a.polyline,b.* ";
$q .= ",c.name,c.service,c.board,c.alight,c.distance AS quote_distance,c.fare,c.fare_raw,c.distance_raw";
$q .= " FROM google_directions_leg_steps a LEFT JOIN google_directions_leg_step_details b ON (b.google_directions_leg_step_id=a.id) ";
$q .= " LEFT JOIN leg_step_quote c ON c.google_directions_leg_step_id=a.id ";
$q .= " WHERE a.google_directions_leg_id=" . $leg['id'];
$r = $this->read_replica->query($q);
$steps = [];
foreach ($r->result() as $f) {
$data = (array) $f;
foreach ($data as $key => $val) {
if ($val == NULL || $val == "") {
unset($data[$key]);
}
}
$steps[] = $data;
}
$leg['steps'] = $steps;
$legs[$i] = $leg;
}
$advice["legs"] = $legs;
$advices[$j] = $advice;
}
$parsedemail_item["advices"] = $advices;
echo json_encode($parsedemail_item);
/* echo '{
"id": 224583,
"location_start": "97 Meyer Rd, 93, Singapore 437918",
"location_end": "1 Fusionopolis Way, #01-07 & #02-14, Connexis, Singapore 138632",
"cost_raw": "14.6",
"trackedemail_item_id": 0,
"cost": "14.60",
"transport_provider_id": 4,
"location_start_lat": "1.2970576",
"location_start_lng": "103.8925856",
"location_end_lat": "1.2987049",
"location_end_lng": "103.7875699",
"request_date": "2020-01-09T13:09:00.955Z",
"started": "2020-01-09T13:09:04.203Z",
"complete": "2020-01-09T13:09:04.743Z",
"status": 0,
"message": "android_automation_job_detail",
"attempts": 0,
"automation_id": 206441,
"completed": "2020-01-09T13:09:04.743Z",
"created": "2020-01-09 13:09:01.072305",
"location_start_id": "4426",
"location_end_id": "4386",
"member_id": "13",
"deeplink": "ComfortDelGroTaxi:\/\/\/?action=setBooking&endingLat=1.298705&endingLong=103.787570&startingLat=1.297085&startingLong=103.892571"
}'; // */
}
private function setSearchRules()
{
$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' => 'location_start_id',
'label' => 'Location start',
'rules' => 'integer',
],
[
'field' => 'location_start_name',
'label' => 'Location start name',
'rules' => 'trim',
],
[
'field' => 'location_end_id',
'label' => 'Location end',
'rules' => 'integer',
],
[
'field' => 'location_end_name',
'label' => 'Location end name',
'rules' => 'trim',
],
];
$this->form_validation->set_rules($config);
}
public function exportCSV()
{
$this->load->helper('export_csv');
$data = [];
$data["page_title"] = "Advice List";
$data['advice_list'] = [];
$data['pagination_link'] = [];
$params = [];
$params = $this->input->get();
if (!empty($params)) {
$this->form_validation->set_data($params);
$this->setSearchRules();
if ($this->form_validation->run() == false) {
$this->renderAdvicePage("view_advice", $data);
return;
}
}
$params = array_filter($params, function ($val) {
return $val !== '';
});
$query = $this->advice_model->getAdviceQuery($params);
$tableData = $this->returnAdminTable(
[
'count_query' => $query,
'query' => $query,
],
'advice/advicelist',
[
'per_page' => 100,
'reuse_query_string' => TRUE,
]
);
$data['advice_list'] = $tableData['output_table'];
$data['pagination_link'] = $tableData['links'];
$query = $this->advice_model->get_query_parsed_email_quote_records($params);
$r = $this->read_replica->query($query);
if ($r->result_array()[0]['all_count'] === '0') {
$this->session->set_flashdata('error', 'Data not found !!!');
$this->renderAdvicePage("view_advice", $data);
return;
}
$this->read_replica->save_queries = false;
$query = $this->advice_model->get_query_parsed_email_quote_records(
$params,
self::NONE_COUNT_RECORD
);
$r = $this->read_replica->query($query);
export($r);
}
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) {
$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->get_address($f, 'start');
$location_end_id = $this->get_address($f, 'end');
if ($location_start_id < 1 || $location_end_id < 1) {
echo "Invalid address\n";
return NULL;
}
$q = "INSERT INTO trackedemail_item (member_id) VALUES(${member_id}) RETURNING id";
$trackedemail_item_id = NULL;
$r = $this->db->query($q);
if (isset($r->result_array()[0]['id'])) {
$trackedemail_item_id = $r->result_array()[0]['id'];
} else {
return NULL;
}
$q = "INSERT INTO parsedemail_item (travel_date,duration,cost_raw,trackedemail_item_id,cost,updated,distance,transport_provider_id,travel_date_end,location_start_id,location_end_id) VALUES (";
$q .= "'" . pg_escape_string($f["travel_date"]) . "',"; // | 2019-11-19 08:58:00
$q .= $f['duration'] == '' ? "NULL," : (((int) $f["duration"]) . ","); // | 26
$q .= "'" . pg_escape_string($f["cost_raw"]) . "',"; // | SGD 13.00
$q .= $trackedemail_item_id == NULL ? "NULL," : (((int) $trackedemail_item_id) . ",");
$q .= $f['cost'] == '' ? "NULL," : (floatval($f["cost"]) . ","); // | 13.00
$q .= $f['updated'] == '' ? "NULL," : ("'" . pg_escape_string($f["travel_date"]) . "',"); // | 2019-11-19 03:53:48.809183
$q .= $f['distance'] == '' ? "NULL," : (floatval($f["distance"]) . ","); // | 10.77
$q .= $f['transport_provider_id'] == '' ? "NULL," : (((int) $f["transport_provider_id"]) . ","); // | 3
$q .= $f['travel_date_end'] == '' ? "NULL," : ("'" . pg_escape_string($f["travel_date_end"]) . "',"); // | 2019-11-19 09:24:55
$q .= $location_start_id . "," . $location_end_id;
$q .= ") RETURNING id";
$r = $this->db->query($q)->result_array();
if (count($r) === 0) {
echo $q . "\n";
if ($trackedemail_item_id > 0) {
$q = "DELETE FROM trackedemail_item WHERE member_id = ${member_id} AND id = ${trackedemail_item_id}";
$this->db->query($q);
}
return NULL;
}
}
private function get_address($f, $what)
{
$tzid = $this->get_timezone($f, $what);
$q = "SELECT * FROM address WHERE lower(address)=lower('" . pg_escape_string($f["location_${what}_address"]) . "') and latitude=";
$q .= floatval($f["location_${what}_lat"]) . " AND longitude=" . floatval($f["location_${what}_lng"]);
$r = $this->read_replica->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
} else {
echo $q . "\n";
}
$q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry,description) VALUES(";
$q .= "'" . pg_escape_string($f["location_${what}_address"]) . "',"; // | 65 Jurong West Central 3, Singapore, 648332
$q .= floatval($f["location_${what}_lat"]) . ","; // | 1.339345
$q .= floatval($f["location_${what}_lng"]) . ","; // | 103.705984
$q .= $tzid . ",NOW(),"; // |
$q .= "'" . pg_escape_string($f["location_${what}_postal"]) . "',"; //
$q .= "'" . pg_escape_string($f["location_${what}_country"]) . "',"; // | SG
$q .= "st_setsrid(st_point(" . $f["location_${what}_lng"] . "," . $f["location_${what}_lat"] . "),4326),";
if ($f["location_${what}_description"] == '') {
$q .= "NULL";
} else {
$q .= "'" . pg_escape_string($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;
}
private function get_timezone($f, $what)
{
$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('" . pg_escape_string($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('" . pg_escape_string($f["location_${what}_tz"]) . "') RETURNING id";
$r = $this->db->query($q)->result_array();
if (count($r)) {
return $r[0]['id'];
}
return NULL;
}
// Receipt Advice
public function receiptadvice()
{
$data = [];
$data["page_title"] = "Receipt Advice";
$data['receipt_advice'] = [];
$data['pagination_link'] = [];
$params = [];
$params = $this->input->get();
if (!empty($params)) {
$this->form_validation->set_data($params);
$this->setSearchRules();
if ($this->form_validation->run() == false) {
$this->renderAdvicePage("view_receipt_advice", $data);
return;
}
}
$query = $this->receipt_advice_model->getAdviceQuery($params);
$tableData = $this->returnAdminTable(
[
'count_query' => $query,
'query' => $query,
],
'advice/receiptadvice',
[
'per_page' => 100,
'reuse_query_string' => TRUE,
]
);
$data['receipt_advice'] = $tableData['output_table'];
$data['pagination_link'] = $tableData['links'];
$this->renderAdvicePage("view_receipt_advice", $data);
}
public function exportCSV_receipt_advice()
{
$this->load->helper('export_csv');
$data = [];
$data["page_title"] = "Advice List";
$data['receipt_advice'] = [];
$data['pagination_link'] = [];
$params = [];
$params = $this->input->get();
if (!empty($params)) {
$this->form_validation->set_data($params);
$this->setSearchRules();
if ($this->form_validation->run() == false) {
$this->renderAdvicePage("view_receipt_advice", $data);
return;
}
}
$params = array_filter($params, function ($val) {
return $val !== '';
});
$query = $this->receipt_advice_model->getAdviceQuery($params);
$tableData = $this->returnAdminTable(
[
'count_query' => $query,
'query' => $query,
],
'advice/receiptadvice',
[
'per_page' => 100,
'reuse_query_string' => TRUE,
]
);
$data['receipt_advice'] = $tableData['output_table'];
$data['pagination_link'] = $tableData['links'];
$query = $this->receipt_advice_model->get_query_parsed_email_quote_records($params);
$r = $this->read_replica->query($query);
if ($r->result_array()[0]['all_count'] === '0') {
$this->session->set_flashdata('error', 'Data not found !!!');
$this->renderAdvicePage("view_receipt_advice", $data);
return;
}
$this->read_replica->save_queries = false;
$query = $this->receipt_advice_model->get_query_parsed_email_quote_records(
$params,
self::NONE_COUNT_RECORD
);
$r = $this->read_replica->query($query);
export($r);
}
}
Reference in New Issue View Git Blame Copy Permalink