load->helper('form');
$this->load->library('form_validation');
$this->load->library('session');
}
public function index() {
$this->load->helper('url');
$data = array();
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT id,username,firstname,lastname,'View ' As action FROM members";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'USERNAME', 'FIRSTNAME', 'LASTNAME', array('data' => 'ACTION', 'style' => 'width:90px'));
$data['member_table'] = $this->table->generate($query);
$this->renderAdminPage('view_dash', $data);
// echo 'Ameye Olu';
}
public function locateMember() {
$this->load->helper('url');
$data = array();
$member_id = $this->input->get('member_id');
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT id,username,firstname,lastname,'View ' As action FROM members";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'USERNAME', 'FIRSTNAME', 'LASTNAME', array('data' => 'ACTION', 'style' => 'width:90px'));
$data['member_table'] = $this->table->generate($query);
$this->renderMemberPage('view_member', $data);
// redirect('dash');
}
public function refreshMemberCard() {
$in['action'] = SAVVYEXT_BKO_REFRESHCARD;
$member_id = $this->input->get('member_id');
if ($member_id > 0) {
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
}
echo "Refresh Category Cards";
}
public function getValueOfFindMember() {
$this->load->model('member_model');
$params_request['search_text'] = trim($this->input->get('search_text'));
$params_request['find_select'] = trim(!empty($this->input->get('card_find_select')) ? $this->input->get('card_find_select') : $this->input->get('find_select'));
$params_request['decision_group'] = trim(!empty($this->input->get('card_decision_group')) ? $this->input->get('card_decision_group') : $this->input->get('decision_group'));
foreach (array_keys(member_model::DATE_FIND_MEMBER) as $val) {
$params_request[$val] = trim($this->input->get($val));
}
foreach (array_keys(member_model::STRING_FIND_MEMBER) as $val) {
$params_request[$val] = trim($this->input->get($val));
}
foreach (array_keys(member_model::NUMERIC_FIND_MEMBER) as $val) {
$params_request[$val] = trim($this->input->get($val));
}
foreach (array_keys(member_model::COMBO_FIND_MEMBER) as $val) {
$params_request[$val] = trim($this->input->get('card_' . $val) ?? $this->input->get($val));
}
return $params_request;
}
public function setComboForFindMember($params) {
$this->load->model('combo_model');
$this->load->model('member_model');
$combo['card_find_select'] = $this->combo_model->getfindMemberType(
'card_find_select', $params['find_select'] ?? '', member_model::FIND_SELECT
);
$combo['card_decision_group'] = $this->combo_model->getDescisionGroupCombo(
'card_decision_group', $params['decision_group'] ?? ''
);
$combo['card_status'] = $this->combo_model->getStatusComboWithAll(
'card_status', $params['status'] ?? -1
);
$combo['card_test'] = $this->combo_model->getYesNoComboWithAll(
'card_test', $params['test'] ?? -1
);
$combo['card_alert_notification'] = $this->combo_model->getYesNoComboWithAll(
'card_alert_notification', $params['alert_notification'] ?? -1
);
$combo['card_alert_email'] = $this->combo_model->getYesNoComboWithAll(
'card_alert_email', $params['alert_email'] ?? -1
);
$combo['card_country'] = $this->combo_model->getCountryComboWithoutFilter(
'card_country', $params['country'] ?? ''
);
return $combo;
}
public function validateValueForFindMember($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForFindMember();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function validateValueForFindMemberBySearchText($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForFindMemberBySearchText();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function setFormRuleForFindMemberBySearchText() {
$this->form_validation->set_rules(
'id', 'ID', 'integer'
);
}
public function setFormRuleForFindMember() {
$this->load->model('member_model');
foreach (array_keys(member_model::NUMERIC_FIND_MEMBER) as $val) {
$this->form_validation->set_rules(
$val, $val, 'integer'
);
}
$status_pattern = 'regex_match[/^(?:[0-9])$/]';
foreach (array_keys(member_model::COMBO_FIND_MEMBER) as $val) {
if (strpos($val, 'country') === false) {
$this->form_validation->set_rules(
$val, $val, $status_pattern
);
}
}
foreach (array_keys(member_model::DATE_FIND_MEMBER) as $val) {
$this->form_validation->set_rules(
$val, $val, 'regex_match[/\d{4}-\d{2}-\d{2}/]'
);
}
$this->form_validation->set_rules(
'select_value', 'Select Value', 'in_list[' . implode(',', array_keys(member_model::FIND_SELECT)) . ']'
);
}
public function load_pagination($total_record, $params, $action) {
// pagination
$this->load->library('pagination');
$config["total_rows"] = $total_record;
$config["base_url"] = base_url() . '/' . get_class($this) . '/' . $action;
$config["per_page"] = 10;
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config["suffix"] = '?'
. http_build_query($params);
$config["first_url"] = '/' . get_class($this) . "/{$action}/0?"
. http_build_query($params);
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$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 getValueCombo($val) {
$status_value = range(0, 1);
return in_array($val, $status_value) ? $val : '';
}
public function findmember() {
// $data = array();
// $this->load->model('combo_model');
// $find_select_value = $decision_group_value = $extra_query = $search_text = "";
// if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// $search_text = trim($this->input->post('search_text'));
// $find_select_value = $this->input->post('find_select');
// $decision_group_value = $this->input->post('decision_group');
// if ($search_text != '' && $find_select_value != '') {
// $extra_query .= " AND lower($find_select_value) LIKE lower('$search_text%')";
// }
// if ($search_text != '' && $decision_group_value != '') {
// $extra_query .= " AND decision_group ='$decision_group_value' ";
// }
// }
// $find_select = $this->combo_model->getfindMemberType("find_select", $find_select_value);
// $decision_group = $this->combo_model->getDescisionGroupCombo("decision_group", $decision_group_value);
// $data["find_select"] = $find_select;
// $data["decision_group"] = $decision_group;
// $data["search_text"] = $search_text;
// $mysql = "SELECT 'Select ' AS select, id,username,firstname,lastname, last_login,loc,"
// . " 'View ' AS ACT "
// . " "
// . " FROM members WHERE id> 0 $extra_query ORDER BY id ASC LIMIT 100 ";
// $this->load->library('pagination');
// $config = array();
// $query = $this->db->query($mysql);
// $config["total_rows"] = $query->num_rows();
// $config["base_url"] = base_url() . "/member/findmember";
// $config["per_page"] = 10;
// $config["uri_segment"] = 3;
// $config["num_links"] = 5;
// $config['full_tag_open'] = "";
// $config['num_tag_open'] = '';
// $config['num_tag_close'] = ' ';
// $config['cur_tag_open'] = "";
// $config['cur_tag_close'] = " ";
// $config['next_tagl_close'] = " ";
// $config['prev_tag_open'] = "";
// $config['prev_tagl_close'] = " ";
// $config['first_tag_open'] = "";
// $config['first_tagl_close'] = " ";
// $config['last_tag_open'] = "";
// $config['last_tagl_close'] = " ";
// $this->pagination->initialize($config);
// $page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
// $page = is_numeric($page) ? $page : 0;
// global $savvyext;
// $this->load->helper('url');
// $this->load->library('table');
// $this->template["table_open"] = "";
// $this->table->set_template($this->template);
// $mysql = "SELECT 'Select ' AS select, id,username,firstname,lastname, last_login,loc,"
// . " 'View ' AS ACT "
// . " "
// . " FROM members WHERE id> 0 $extra_query ORDER BY id ASC LIMIT " . $config["per_page"] . " OFFSET " . $page . " ";
// $query = $this->db->query($mysql);
// $this->table->set_heading(array('data' => 'Select', 'style' => 'width:40px'), array('data' => 'ID', 'style' => 'width:50px'), 'Email', 'Firstname', 'Lastname', 'Last Login', 'Location', array('data' => 'ACT', 'style' => 'width:40px'));
// $data['member_table'] = $this->table->generate($query);
// $data["links"] = $this->pagination->create_links();
// $data['google_api_key'] = $savvyext->cfgReadChar('google.api_key');
// $data["page_title"] = "Find Member";
// $data["page_link"] = $this->pagination->create_links();
// $this->renderMemberPage('view_findmember', $data);
global $savvyext;
$this->load->model('member_model');
$this->load->library('table');
$this->table->set_template($this->template);
$this->table->set_heading([
'Select',
'ID',
'Email',
'FirstName',
'LastName',
'Last Login',
'Register Date',
'IP Address',
'ACT'
]);
$params = $this->getValueOfFindMember();
$errors = $this->validateValueForFindMember($params);
$params = array_filter($params, function($ele) {
return $ele !== "";
});
$params = array_diff_key($params, $errors);
$data = $this->setComboForFindMember($params);
if ( isset($params['search_text']) ) {
// set ['email' => 'test@gmail.com']
$params[$params['find_select']] = $params['search_text'];
}
// validate by search text
$errors = $this->validateValueForFindMemberBySearchText($params);
$params = array_diff_key($params, $errors);
$data = array_merge(
$data, $params, $this->load_pagination(
count( $this->member_model->get_member_records($params) ), $params, 'findmember'
)
);
$data['member_table'] = $this->table->generate(
$this->member_model->get_member_records( $params, $data['limit'], $data['offset'] )
);
$data['google_api_key'] = $savvyext->cfgReadChar('google.api_key');
$data["page_title"] = "Find Member";
$this->renderMemberPage('view_findmember', $data);
}
var $template22 = array(
'table_open' => "",
'thead_open' => '',
'thead_close' => ' ',
'heading_row_start' => '',
'heading_row_end' => ' ',
'heading_cell_start' => '',
'heading_cell_end' => ' ',
'tbody_open' => '',
'tbody_close' => ' ',
'row_start' => '',
'row_end' => ' ',
'cell_start' => '',
'cell_end' => ' ',
'row_alt_start' => '',
'row_alt_end' => ' ',
'cell_alt_start' => '',
'cell_alt_end' => ' ',
'table_close' => '
'
);
public function sendSampleMessage() {
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$member_id = $this->input->post('member_id');
$player_id = $this->input->post('player_id');
$message = $this->input->post('message');
if ($player_id != "" && $message != "") {
$result = $this->postOneSignal($player_id, $message);
var_dump($result);
} else {
echo "Invalid PLAYER_ID and/or MESSAGE!";
}
} else {
echo "Invalid request!";
}
}
private function postOneSignal($player_id, $message) {
global $savvyext;
$url = $savvyext->cfgReadChar('onesignal.url');
$app_id = $savvyext->cfgReadChar('onesignal.app_id');
$url = "https://onesignal.com/api/v1/notifications";
$app_id = "e9a7bb38-0a27-4250-9fb7-9bd7871d7b63";
$data = [
'app_id' => $app_id,
'contents' => ['en' => $message],
'headings' => ['en' => 'Float Msg'],
'subtitle' => ['en' => 'You\'ve got push from Float'],
'content_available' => true,
'mutable_content' => true,
'include_player_ids' => [$player_id],
'data' => ["foo" => "bar"]
];
$opts = array(
'http' => array(
'method' => "POST",
'header' =>
"Content-Type: application/json; charset=utf-8\r\n" .
"Accept: application/json\r\n",
'content' => json_encode($data)
)
);
$context = stream_context_create($opts);
$body = file_get_contents($url, false, $context);
error_log($body);
$result = json_decode($body, true);
return $result;
}
public function viewLocateMember() {
$this->load->helper('url');
$data = array();
$member_items = array(
['GEOLOCATION', 'Location Tracking', 'btn-primary'],
['GPSTRIPS', 'GPS Trips', 'btn-danger'],
['MERGED', 'Merged Receipts', 'btn-secondary'],
['EMAILRECIP', 'Email Receipts', 'btn-success'],
['EMAILFAIL', 'Email Fail', 'btn-danger'],
['EMAILOTHER', 'Email Other', 'btn-secondary'],
['ACCOUNTREC', 'Bank Receipts', 'btn-danger'],
['ACCOUNTOTHER', 'Bank Other', 'btn-primary'],
['MYBANKS', 'Banks', 'btn-info'],
['PARSEDDATA', 'Mobility Profile', 'btn-light'],
['MCARDS', 'Cards', 'btn-info'],
['MCARDATA', 'Cards Data', 'btn-danger'],
['POINTS', 'Points', 'btn-primary'],
['PROFILE', 'Profile/Settings', 'btn-warning'],
['CONTACTS', 'Contacts/Friends', 'btn-dark'],
['SUBSCRIPTION', 'Subscription', 'btn-success'],
['CARPOOL', 'Carpool', 'btn-light'],
['TRIPS', 'Saved Trips', 'btn-danger'],
['SURVEY', 'Survey', 'btn-info'],
['PAYMENTS', 'Payment', 'btn-primary'],
['OAUTH2', 'OAuth2', 'btn-success'],
['NOTIFICATION', 'Msg & Alerts', 'btn-info'],
['ACTIVITIES', 'Activities', 'btn-warning'],
['BOOKING', 'Bookings', 'btn-success']
);
$data['member_items'] = $member_items;
$member_id = $this->input->get('member_id');
if ($member_id != '' && $member_id > 0) {
$this->load->library('table');
// $this->template["table_open"] = "";
$this->table->set_template($this->template);
$point_var = '\"POINTS\"';
$mysql = "SELECT m.id "
. ",m.decision_group||''||m.points||' ' AS points,"
. " m.min_budget*0.01 AS minb, m.max_budget*0.01 AS maxb,m.country,m.city,m.state,"
. " (CASE WHEN m.alert_email=1 THEN 'Active' ELSE 'Inactive' END) emailAlert,"
. " (CASE WHEN m.alert_notification=1 THEN 'Active' ELSE 'Inactive' END) notificationAlert"
. " FROM members m LEFT JOIN decision_group g ON g.dkey=m.decision_group WHERE m.id = $member_id";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Group/Name/Updated', 'USERNAME', 'FIRSTNAME', 'LASTNAME', array('data' => 'PHONE', 'style' => 'width:90px'), 'Created', 'last Login', 'Points', 'Min Budget', 'Max Budget'
, array('data' => 'Country', 'style' => 'width:10px'), array('data' => 'City', 'style' => 'width:10px'), array('data' => 'ST', 'style' => 'width:10px'), 'Emails', 'Notifications'
);
$data['member_table'] = $this->table->generate($query);
$_SESSION["member_id"] = $member_id;
$data["member_id"] = $member_id;
$mysql = "SELECT ma.*,m.min_budget from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE ma.member_id={$member_id}";
$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'Select', 'style' => 'width:40px'), array('data' => 'ID', 'style' => 'width:50px'), 'Email', 'Firstname', 'Lastname', 'Last Login', 'Location', array('data' => 'ACT', 'style' => 'width:40px'));
$data['member_analysis'] = $this->table->generate($query);
$data["page_title"] = "Member ID " . $member_id . " Selected";
$this->renderMemberPage('view_member', $data);
}
}
public function viewmedberdetail() {
global $savvyext;
$data = array();
if ($this->input->get()) {
$member_id = $this->input->get('member_id');
if ($member_id != '' && $member_id > 0) {
$this->load->model('combo_model');
$mysql = "SELECT a.*,a.id AS member_id, b.format AS picture_format ";
$mysql .= " FROM members a LEFT JOIN card_images b ON (b.id=a.profile_picture) WHERE a.id = $member_id";
$query = $this->read_replica->query($mysql);
$data = $query->row_array();
$data["devices"] = [];
$data['storage'] = $savvyext->cfgReadChar('system.storage_url');
$data['start_date'] = $this->input->get('start_date');
$data['end_date'] = $this->input->get('end_date');
$data['status'] = !is_null($this->input->get('card_status')) ? $this->input->get('card_status') : 1;
$data['card_status'] = $this->combo_model->getStatusCombo(
'card_status', $data['status']
);
$q = "SELECT * FROM members_devices WHERE member_id=${member_id}";
$q .= $this->query_condition_member_detail($data);
$r = $this->read_replica->query($q);
foreach ($r->result() as $row) {
$data["devices"][] = $row->player_id;
}
$this->load->view('member/extra/member_detail', $data);
} else {
echo "Not found - illegal call";
}
}
}
public function query_condition_member_detail($data) {
$query_condition = '';
if (isset($data['status'])) {
$query_condition .= " AND status = " . pg_escape_string($data['status']);
} else {
$query_condition .= " AND status = 1";
}
if (!empty($data['start_date'])) {
$query_condition .= " AND DATE(updated) >= '" . pg_escape_string($data['start_date']) . "'";
}
if (!empty($data['end_date'])) {
$query_condition .= " AND DATE(updated) <= '" . pg_escape_string($data['end_date']) . "'";
}
if (empty($data['start_date']) && empty($data['end_date'])) {
$query_condition .= " AND DATE(updated) >= DATE(CURRENT_DATE - INTERVAL '1' month)";
$query_condition .= " AND DATE(updated) <= DATE(CURRENT_DATE)";
}
return $query_condition;
}
public function viewmember() {
$data = array();
if ($this->input->get()) {
$member_id = $this->input->get('member_id');
if ($member_id != '' && $member_id > 0) {
$mysql = "SELECT * FROM members WHERE id = $member_id";
$query = $this->read_replica->query($mysql);
$data['selected_user'] = $query->row_array();
$this->load->library('table');
$this->table->set_template($this->template);
$data['page_title'] = "Recent Tracking Data";
$gps = $this->load->database('gps', TRUE);
$mysql = "SELECT 'View ' AS view ,traked_group, count(id) FROM members_tracking WHERE member_id = $member_id AND traked_group IS NOT NULL GROUP BY traked_group";
$query = $gps->query($mysql);
// $this->table->set_heading(array('data' => 'ID',strtotime 'style' => 'width:10px'), 'USERNAME','FIRSTNAME','LASTNAME', array('data' => 'ACTION', 'style' => 'width:90px'));
$data['member_tracktable'] = $this->table->generate($query);
$mysql = "SELECT id,subject FROM trackedemail_item WHERE member_id=$member_id";
$query = $this->read_replica->query($mysql);
//$ this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Linked Email','Provider');
$data['member_tracked'] = $this->table->generate($query);
$mysql = " SELECT id, link_email , link_provider FROM members_trackemail WHERE active = 1 AND member_id=$member_id";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Linked Email', 'Provider');
$data['member_trackemail'] = $this->table->generate($query);
$this->load->view('member/extra/member_activity', $data);
}
}
/*
$this->load->model('admindash_model');
$out = $this->admindash_model->getAdminDashData($data);
$data['recent_signup'] = $out['recent_signup'];
$this->load->model('service_model');
$outx = $this->service_model->getServiceRequestList(100);
$data['transport_request'] = $outx['service_request_list'];
$this->load->library('googlemaps');
$config['center'] = 'atalnta,GA,USA';
$config['zoom'] = 'auto';
$config['directions'] = TRUE;
$config['directionsStart'] = '4201 defoors farm trail, powder springs, GA 30127, USA';
$config['directionsEnd'] = '2324 stancrest ln, lawrenceville, 30044, GA, USA';
$config['directionsDivID'] = 'directionsDiv';
$this->googlemaps->initialize($config);
$data['map'] = $this->googlemaps->create_map();
*/
}
public function viewMemberAction() {
$this->load->helper('export_csv');
$data = array();
if ($this->input->get() || isset($this->input->post()['export'])) {
$member_id = $this->input->get('member_id') ?? $this->input->post('member_id');
$action_name = $this->input->get('action_name') ?? $this->input->post('action_name');
$start_date = $this->input->get('start_date') ?? $this->input->post('start_date');
$end_date = $this->input->get('end_date') ?? $this->input->post('end_date');
$active_tab = (null !== $this->input->get('active_tab')) ? $this->input->get('active_tab') : '#activity';
$offset = (int) $this->input->get('offset');
$limit = (int) ($this->input->get('limit'));
if ($limit < 1)
$limit = 15;
$pages = array();
if ($member_id != '' && $member_id > 0 && $action_name != '') {
$mysql = "SELECT * FROM members WHERE id = $member_id";
$query = $this->read_replica->query($mysql);
$data['selected_user'] = $query->row_array();
$decision_group = $data['selected_user']['decision_group'];
$this->load->library('table');
$this->table->set_template($this->template);
$data['page_title'] = "Recent Tracking Data";
$mysql = " SELECT id, link_email , link_provider FROM members_trackemail WHERE active = 1 AND member_id=$member_id";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Linked Email', 'Provider');
$data['member_trackemail'] = $this->table->generate($query);
/* $mysql = " SELECT ('View '";
$mysql.= " || 'Parse '";
$mysql.= " || 'Advice '";
$mysql.= ") AS opt, ";
$mysql.= " p.* FROM parsedemail_item p LEFT JOIN trackedemail_item t ON t.id=p.trackedemail_item_id WHERE t.member_id = $member_id";
*/
$mysql = "SELECT * FROM members_bankimport WHERE member_id =" . $member_id . "ORDER BY time DESC LIMIT 200";
$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'),'Subject');
$data['member_banktrx'] = $this->table->generate($query);
$mysql = "SELECT parsedemail_item.*,parsedemail_item_advice_google.id AS gid,parsedemail_item_advice_google.routes,trackedemail_item.id as tid ";
$mysql .= " FROM trackedemail_item, parsedemail_item ";
$mysql .= " LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id ";
$mysql .= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id=${member_id}";
$mysql .= " AND parsedemail_item.dup_id IS NULL";
$mysql .= " ORDER BY parsedemail_item.travel_date DESC, parsedemail_item.travel_date_end DESC";
//e cho $q;
// $query = $this->db->query($mysql);
// $this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'),'Subject');
// $data['member_parsedetail'] = $this->table->generate($query);
//Send
' AS Send "
. " FROM members_notification n LEFT JOIN members m On m.id=n.member_id WHERE n.member_id ={$member_id} ORDER BY n.added DESC";
$query = $this->read_replica->query($mysql);
$data['alerts_recieved'] = $this->table->generate($query);
$data["Notifications"] = "Trips Report";
return $data;
}
public function getMemberTripsReport($member_id, &$data) {
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT * FROM members_trips WHERE member_id = " . $member_id;
$query = $this->read_replica->query($mysql);
$data['trips_table'] = $this->table->generate($query);
$data["page_title"] = "Trips Report";
return $data;
}
public function cancelCarpool() {
$carpool_id = (int) $this->input->get('carpool_id');
$member_id = (int) $this->input->get('member_id');
if ($carpool_id > 0 && $member_id > 0) {
$mysql = "SELECT * from members_carpool WHERE status =1 AND id = " . $carpool_id;
$query = $this->read_replica->query($mysql);
if ($query->num_rows() > 0) {
$cp = $query->row_array();
$card_id = $cp["card_id"];
$mysql_up = "UPDATE members_carpool SET status = 4 WHERE status =1 AND id = " . $carpool_id;
$this->db->query($mysql_up);
$mysql_up = "UPDATE members_carpool_friends SET status = 4 WHERE status NOT IN ( 4 ) AND carpool_id = " . $carpool_id;
$this->db->query($mysql_up);
/*
*
* members_carpool_friends members_carpool_friends_id_seq members_carpool_id_seq
savvy=> SELECT * FROM members_carpool_friends;
id | carpool_id | firstname | lastname | email | status | accept_status | pool_status | level_a | level_b | added | last_msg | updated | link
----+------------+-----------+------------+-----------------------------------------+--------+---------------+-------------+---------+---------+----------------------------+----------+----------------------------+-------------------------------------------------------------------
14 | 18 | Olu | Ameye | ameye@chiefsoft.com | 1 | 0 | 0 | A | A | 2019-11-12 12:41:36.46717 | | 2019-11-12 12:41:36.46717 | 40170ebd8a13f4d4189b49179bcc1da5@aab3238922bcc25a6f606eb525ffdc56
19 | 23 | cornel | float | cornel@float.sg | 1 | 0 | 0 | A | A | 2019-11-14 16:26:22.789631 | | 2019-11-14 16:26:22.789631 | 4d22822d17ee3875d00b1330a791af1d@1f0e3dad99908345f7439f8ffabdffc4
20 | 24 | cornel | float | cornel@float.sg | 1 | 0 | 0 | A | A | 2019-11-14 16:26:40.584426 | | 2019-11-14 16:26:40.584426 | aad64b3918c8f037fe4657ed5f6fbd5d@98f13708210194c475687be6106a3b84
21 | 25 | cornel | flat | cornel@float.sg | 1 | 0 | 0 | A | A | 2019-11-14 16:31:07.37425 | | 2019-11-14 16:31:07.37425 | b1862df558a73d3802cc051811a1dc5b@3c59dc048e8850243be8079a5c74d079
22 | 26 | cornel | float | cornel@float.sg | 1 | 0 | 0 | A | A | 2019-11-14 16:34:17.416433 | | 2019-11-14 16:34:17.416433 | 4d4b3d0cb01b4a0c35794d7f50459cf7@b6d767d2f8ed5d21a44b0e5886680cb9
23 | 27 | cornel | floart | cornel+test@float.sg | 4 | 0 | 0 | A | A | 2019-11-15 04:42:50.224035 | | 2019-11-15 04:42:50.224035 | c89c2f772bc8b069344f0af2469cb268@37693cfc748049e45d87b8c7d8b9aacd
*/
// $mysql_up = "UPDATE members_card_assign SET status = 0 WHERE status =1 AND member_id = $member_id AND card_id = $card_id";
// $this->db->query($mysql_up);
echo "Cancelled";
}
} else {
echo 'Invalid Call';
}
}
public function sendCarpoolMessage() {
$carpool_id = (int) $this->input->get('carpool_id');
if ($carpool_id > 0) {
$out = array();
$in = array();
$in['action'] = SAVVY_BKO_CARPOOL_FRIENDMESSAGE;
$in['carpool_friend_id'] = $carpool_id;
$ret = $this->savvy_api($in, $out);
print_r($out);
if ($ret == PHP_API_OK) {
$message = "Message Sent";
} else {
$message = 'Failed to Send';
}
echo $message;
} else {
echo 'Invalid Call';
}
return 0;
}
public function viewCarpoolData() {
$carpool_id = (int) $this->input->get('carpool_id');
if ($carpool_id > 0) {
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT cp.id AS carpool_id,mc.title,mc.id AS card_id,cp.added,cp.updated,cp.status
FROM members_carpool cp
LEFT JOIN members m ON m.id = cp.member_id
LEFT JOIN main_cards mc ON mc.id=cp.card_id WHERE cp.id = " . $carpool_id;
$query = $this->read_replica->query($mysql);
$carpool_table = $this->table->generate($query);
$mysql = "SELECT
'Send ' AS MSG,
* FROM members_carpool_friends WHERE carpool_id=" . $carpool_id;
$query = $this->read_replica->query($mysql);
$carpool_friend_table = $this->table->generate($query);
echo $carpool_table . "Friends View ' AS view, "
. "cp.pool,mc.title,mc.id AS card_id,cp.added,cp.updated,cp.status,"
. "'Cancel ' AS cancel,"
. "'Process ' AS process "
. "FROM members_carpool cp "
. "LEFT JOIN members m ON m.id = cp.member_id "
. "LEFT JOIN main_cards mc ON mc.id=cp.card_id WHERE cp.member_id=" . $member_id;
$query = $this->read_replica->query($mysql);
$data['carpool_table'] = $this->table->generate($query);
$data["page_title"] = "Carpool Report";
return $data;
}
public function getMemberSurveyReport($member_id, &$data) {
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT ms.id,me.firstname,me.lastname,m.title,ms.answer,ms.status,me.added,m.button1_action "
. "FROM members_survey ms "
. "LEFT JOIN main_cards m ON m.id=ms.card_id "
. "LEFT JOIN members me ON me.id = ms.member_id WHERE ms.member_id=$member_id";
$query = $this->read_replica->query($mysql);
//id firstname lastname title subscribe status button1_action
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:50px'), 'Firstname', 'Lastname', 'Description', 'Answer', 'Date Subscribed', 'Status', 'Action');
$data['survey_table'] = $this->table->generate($query);
$mysql = "SELECT os.answers, ms.answers_key,ms.answers AS user_answer
FROM members_onboarding_survey ms
LEFT JOIN onboarding_survey os ON os.answers_key=ms.answers_key
WHERE ms.member_id = $member_id";
$query = $this->read_replica->query($mysql);
//id firstname lastname title subscribe status button1_action
$this->table->set_heading('Question', array('data' => 'answers_key', 'style' => 'width:1050px'), array('data' => 'user_answer', 'style' => 'width:50px'));
$data['onboarding_survey_table'] = $this->table->generate($query);
$data["page_title"] = "Survey Report";
return $data;
}
public function getMemberGPSData($member_id, &$data) {
$per_page = 20;
$data["member_id"] = $member_id;
$tracking_trips_errors = $data['errors']['tracking_trips'] ?? null;
$tracking_trips_data = $data['tracking_trips'];
$tracking_errors = $data['errors']['tracking'] ?? null;
$tracking_data = $data['tracking'];
$tracking_data['cur_page'] = $tracking_data['cur_page'] ?? 0;
if ($tracking_data['cur_page'] != 0) {
$tracking_data['offset'] = ($tracking_data['cur_page'] - 1) * $per_page;
} else {
$tracking_data['offset'] = 0;
}
$tracking_trips_data['cur_page'] = $tracking_trips_data['cur_page'] ?? 0;
if ($tracking_trips_data['cur_page'] != 0) {
$tracking_trips_data['offset'] = ($tracking_trips_data['cur_page'] - 1) * $per_page;
} else {
$tracking_trips_data['offset'] = 0;
}
$gps = $this->load->database('gps', TRUE);
$this->load->library('pagination');
$this->load->helper('url');
$this->load->library('table');
$this->table->set_template($this->template);
$query_params = [
'proc' => 'PROCESS',
'action_name' => $data['action_name'],
'member_id' => $member_id
];
$config["base_url"] = base_url()
. "/member/viewMemberAction";
$config['first_url'] = '/member/viewMemberAction/0?'
. http_build_query($query_params);
$config['suffix'] = '?' . http_build_query($query_params);
$config["per_page"] = $per_page;
$config['attributes'] = [
'class' => 'tracking__pagination-anchor'
];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$config['cur_page'] = $tracking_data['cur_page'];
$config['use_page_numbers'] = TRUE;
$data["tracking_table"] = "";
$data["tracking_links"] = "";
$data["tracking_trips_table"] = "";
$data["tracking_trips_pagination_link"] = "";
if ($data["member_id"] > 0) {
// id member_id traked_group speed lat lng gps ttime loc created device_id previous_id distance duration
$q = "SELECT id,duration,distance,round(speed,2) as speed,date_trunc('second',ttime) as ttime,device_id,loc,traked_group FROM members_tracking WHERE member_id=" . $data["member_id"];
// date
if (!empty($tracking_data['start_date']) && !isset($tracking_errors['start_date'])) {
$q .= " AND DATE(ttime) >= '" . pg_escape_string($tracking_data['start_date']) . "'";
}
if (!empty($tracking_data['end_date']) && !isset($tracking_errors['end_date'])) {
$q .= " AND DATE(ttime) <= '" . pg_escape_string($tracking_data['end_date']) . "'";
}
if (empty($tracking_data['start_date']) && empty($tracking_data['end_date'])) {
$data['tracking']['start_date'] = date('Y-m-d', strtotime('-1 month'));
$data['tracking']['end_date'] = date('Y-m-d');
$q .= " AND DATE(ttime) >= '" . $data['tracking']['start_date'] . "'"
. " AND DATE(ttime) <= '" . $data['tracking']['end_date'] . "'";
}
// duration
if (isset($tracking_data['start_duration']) && !isset($tracking_errors['start_duration'])) {
$q .= " AND duration >= " . pg_escape_string($tracking_data['start_duration']) . "";
}
if (isset($tracking_data['end_duration']) && !isset($tracking_errors['end_duration'])) {
$q .= " AND duration <= " . pg_escape_string($tracking_data['end_duration']) . "";
}
// distance
if (isset($tracking_data['start_distance']) && !isset($tracking_errors['start_distance'])) {
$q .= " AND distance >= " . pg_escape_string($tracking_data['start_distance']) . "";
}
if (isset($tracking_data['end_distance']) && !isset($tracking_errors['end_distance'])) {
$q .= " AND distance <= " . pg_escape_string($tracking_data['end_distance']) . "";
}
$r = $gps->query($q);
$config["total_rows"] = $r->num_rows();
$this->pagination->initialize($config);
$q .= " ORDER BY ttime DESC LIMIT " . $per_page . " OFFSET " . $tracking_data['offset'];
$query = $gps->query($q);
// id duration distance speed ttime device_id loc traked_group
$this->table->set_heading(
array('data' => 'ID', 'style' => 'width:80px'), array('data' => 'Duration', 'style' => 'width:80px'), array('data' => 'Distance', 'style' => 'width:80px'), array('data' => 'Speed', 'style' => 'width:80px'), array('data' => 'Time', 'style' => 'width:160px'), array('data' => 'Device ID', 'style' => 'width:80px'), array('data' => 'IP Address', 'style' => 'width:80px'), array('data' => 'Tracked Group', 'style' => 'width:80px')
);
$data["tracking_table"] = $this->table->generate($query);
// if ($r->num_rows()) {
$data["tracking_links"] = $this->pagination->create_links();
// }
$q = "SELECT 'View ',";
$q .= "ROUND(a.duration,0) AS duration,ROUND(a.distance,2) AS distance,ROUND(a.speed,2) AS speed, ROUND(a.avg_speed,2) AS avg_speed ";
$q .= "FROM members_tracking_trips a WHERE a.member_id=" . $data["member_id"];
// duration
if (isset($tracking_trips_data['start_duration']) && !isset($tracking_trips_errors['start_duration'])) {
$q .= " AND a.duration >= " . pg_escape_string($tracking_trips_data['start_duration']) . "";
}
if (isset($tracking_trips_data['end_duration']) && !isset($tracking_trips_errors['end_duration'])) {
$q .= " AND a.duration <= " . pg_escape_string($tracking_trips_data['end_duration']) . "";
}
// distance
if (isset($tracking_trips_data['start_distance']) && !isset($tracking_trips_errors['start_distance'])) {
$q .= " AND a.distance >= " . pg_escape_string($tracking_trips_data['start_distance']) . "";
}
if (isset($tracking_trips_data['end_distance']) && !isset($tracking_trips_errors['end_distance'])) {
$q .= " AND a.distance <= " . pg_escape_string($tracking_trips_data['end_distance']) . "";
}
$r = $gps->query($q);
$config = [];
$config["base_url"] = base_url()
. "/member/viewMemberAction";
$config["suffix"] = '?' . http_build_query($query_params);
$config['first_url'] = '/member/viewMemberAction/0?'
. http_build_query($query_params);
$config["per_page"] = $per_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$config["total_rows"] = $r->num_rows();
$config['query_string_segment'] = 'tracking_trips_offset';
$config['attributes'] = [
'class' => 'tracking-trips__pagination-anchor'
];
$config['use_page_numbers'] = TRUE;
$config['cur_page'] = $tracking_trips_data['cur_page'];
$this->pagination->initialize($config);
$q .= " LIMIT " . $per_page . " OFFSET " . $tracking_trips_data['offset'];
$query = $gps->query($q);
$this->table->set_heading(
array('data' => 'ID', 'style' => 'width:30px'), array('data' => 'Duration', 'style' => 'width:40px'), array('data' => 'Distance', 'style' => 'width:40px'), array('data' => 'Speed', 'style' => 'width:40px'), array('data' => 'Avg.Speed', 'style' => 'width:40px')
);
$data["tracking_trips_table"] = $this->table->generate($query);
$data["tracking_trips_pagination_link"] = $this->pagination->create_links();
}
return $data;
}
public function refreshCards() {
$member_id = (int) $this->input->get('member_id');
if ($member_id > 0) {
$in = array();
$in['action'] = SAVVYEXT_BKO_REFRESHCARD;
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
if ($ret == PHP_API_OK) {
$message = $out['status'];
} else {
$message = $out['status'];
}
echo $message;
} // if member id > 0
}
public function refreshPersonalty() {
$member_id = (int) $this->input->get('member_id');
if ($member_id > 0) {
$in = array();
$in['action'] = SAVVYEXT_BKO_REFRESHGROUP;
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
if ($ret == PHP_API_OK) {
$message = $out['status'];
} else {
$message = $out['status'];
}
echo $message;
} // if member id > 0
}
private function memberSubscription($member_id, $data) {
$data['member_subs'] = '';
$mysql = "SELECT m.id AS card_id, m.title,s.subscribe,s.status,s.reciept_count, s.completed FROM members_card_assign s
LEFT JOIN main_cards m ON m.id =s.card_id
WHERE s.subscribe IS NOT NULL AND m.button1_action IN ('GOOFFERS') AND s.member_id = " . $member_id . " ORDER BY subscribe DESC";
$query = $this->read_replica->query($mysql);
$data["member_subs"] = $this->table->generate($query);
return $data;
}
private function memberPoints($member_id, $data) {
$data['points'] = '100';
$data['points_recieved'] = '';
$data['points_redeemed'] = '';
$mysql = "SELECT s.point_key,s.name,p.points,p.added::date "
. " FROM members_points p "
. " LEFT JOIN points_settings s ON s.point_key=p.point_key "
. " WHERE p.member_id = " . $member_id . " ORDER BY p.added DESC";
$query = $this->read_replica->query($mysql);
$data["points_recieved"] = $this->table->generate($query);
$mysql = "SELECT a.description,r.points,r.added,r.status
FROM members_points_redeem r
LEFT JOIN points_redeem_avialable a ON a.key=r.redeem_key
WHERE r.member_id= " . $member_id . " ORDER BY r.added DESC";
$query = $this->read_replica->query($mysql);
$data["points_redeemed"] = $this->table->generate($query);
return $data;
}
private function memberOAuth2Delete($member_id, $data) {
global $savvyext;
if ($member_id < 1) {
$data["message"] = "Invalid member ID!";
return $data;
}
$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
$encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
$encryptionKey = $savvyext->cfgReadChar('encryption.key');
$encryptionIV = $savvyext->cfgReadChar('encryption.iv');
$api_url = $savvyext->cfgReadChar('system.api_url');
$payload = "{
\"member_id\":$member_id
}";
$encrypted_payload = bin2hex(
openssl_encrypt(
$payload, $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
));
$postdata = "{\"encrypted_payload\": \"${encrypted_payload}\"}";
$url = $api_url . "/oauth2/api/remove";
$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 = array_merge($data, json_decode($payload, true));
if ($data && isset($data['error'])) {
$data['message'] = $data['error'];
} else if ($data && !isset($data['message'])) {
$data['message'] = 'Unexpected error: ' . $payload;
}
return $this->memberOAuth2($member_id, $data);
}
private function memberOAuth2($member_id, $data) {
$data["links"] = "";
// $mysql = "SELECT a.email,a.name,b.name as provider, a.created,a.updated,a.expires_in,b.access_type ";
// $mysql .= " FROM oauth2_tokens a, oauth2_providers b WHERE a.member_id=${member_id} AND b.id=a.oauth2_provider_id ORDER BY a.created DESC LIMIT 20";
// $query = $this->db->query($mysql);
// $data["oauth2_tokens"] = $this->table->generate($query);
$data["token_provider"] = "";
// $mysql = "select a.created,a.started,a.completed from oauth2_pull_jobs a where a.member_id=${member_id} and a.started is not null order by a.started DESC LIMIT 10";
// $query = $this->db->query($mysql);
// $data["oauth2_pull_jobs"] = $this->table->generate($query);
// $mysql = "select a.created,a.started,a.completed,a.item_count,a.search_term,a.search_from,a.failed from oauth2_pull_job_threads a, oauth2_pull_jobs b where b.id=a.oauth2_pull_job_id and b.member_id=${member_id} order by a.id desc LIMIT 20";
// $query = $this->db->query($mysql);
// $data["oauth2_pull_job_threads"] = $this->table->generate($query);
return $data;
}
private function viewReceipts($member_id, $data, $limit, $member_parsedemail_offset, $member_tracked_offset, $error) {
$data['member_parsedemail_offset'] = $member_parsedemail_offset;
$data['member_tracked_offset'] = $member_tracked_offset;
$limit = 100;
$mysql = " SELECT ";
$mysql .= " p.id,p.trackedemail_item_id,tp.name,p.travel_date,p.duration,p.cost, p.distance, ";
$mysql .= " CASE WHEN e.id IS NULL THEN 'No' ELSE 'Yes' END AS surge_price, ";
$mysql .= " CASE WHEN (f.id IS NOT NULL AND f.cost>f.average) THEN ROUND(f.average/f.cost,2) ELSE 0 END AS cheaper_alternative, ";
//$mysql.= " 'Insights ' AS Insights ";
$mysql .= " 'Insights ' AS Insights, ";
$mysql .= " p.updated ";
$mysql .= " FROM parsedemail_item p LEFT JOIN trackedemail_item t ON t.id=p.trackedemail_item_id ";
$mysql .= " LEFT JOIN transport_providers tp ON p.transport_provider_id=tp.id";
$mysql .= " LEFT JOIN trip_surge_price e ON (e.data_source_id=p.id AND e.data_source=1) ";
$mysql .= " LEFT JOIN trip_price_comparison f ON (f.data_source_id=p.id AND e.data_source=1) ";
$mysql .= " WHERE t.member_id = ${member_id} AND p.dup_id is NULL ";
// travel date
if (!empty($data['start_travel_date']) && !isset($error['start_travel_date'])) {
$mysql .= " AND DATE(p.travel_date) >= '" . pg_escape_string($data['start_travel_date']) . "'";
}
if (!empty($data['end_travel_date']) && !isset($error['end_travel_date'])) {
$mysql .= " AND DATE(p.travel_date) <= '" . pg_escape_string($data['end_travel_date']) . "'";
}
// duration
if (!empty($data['duration_start']) && !isset($error['duration_start'])) {
$mysql .= " AND p.duration >= " . pg_escape_string($data['duration_start']) . "";
}
if (!empty($data['duration_end']) && !isset($error['duration_end'])) {
$mysql .= " AND p.duration <= " . pg_escape_string($data['duration_end']) . "";
}
// cost
if (!empty($data['cost_start']) && !isset($error['cost_start'])) {
$mysql .= " AND p.cost >= " . pg_escape_string($data['cost_start']) . "";
}
if (!empty($data['cost_end']) && !isset($error['cost_end'])) {
$mysql .= " AND p.cost <= " . pg_escape_string($data['cost_end']) . "";
}
// distance
if (!empty($data['distance_start']) && !isset($error['distance_start'])) {
$mysql .= " AND p.distance >= " . pg_escape_string($data['distance_start']) . "";
}
if (!empty($data['distance_end']) && !isset($error['distance_end'])) {
$mysql .= " AND p.distance <= " . pg_escape_string($data['distance_end']) . "";
}
// surge
if ($data['surge'] == '1') {
$mysql .= " AND e.id IS NOT NULL";
} else {
if ($data['surge'] == '0') {
$mysql .= " AND e.id IS NULL";
}
}
$mysql .= " ORDER BY id DESC";
list($data['pages'], $data['page'], $data['total_pages']) = $this->paginateQuery($mysql, $limit, $member_parsedemail_offset);
$mysql = "SELECT (ROW_NUMBER () OVER (ORDER BY travel_date DESC)) as No,* FROM (${mysql}) AS foo ORDER BY travel_date DESC LIMIT ${limit} OFFSET ${member_parsedemail_offset}";
error_log($mysql);
$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'),'Subject');
$this->table->set_heading(
'No.', array('data' => 'ID', 'style' => 'width:10px'), 'Email ID', 'Merchant', 'Travel Date', 'Duration', 'Cost', 'Distance', 'Surge', 'Alternative', 'Insights', 'Last Data Parse'
);
$data['member_parsedemail'] = $this->table->generate($query);
$config = [];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$data['pagination_config'] = $config;
$select_query = 'SELECT
t.id,
t.created,
t.subject,
t.hash,
t.date_parsed,
t.message_date,
t.message_from,
t.parsed_status,
p.trackedemail_item_id,
p.dup_id,
t.message';
$count_query = "SELECT count(*) AS all_count";
$from_query = " FROM trackedemail_item t left join parsedemail_item p on t.id = p.trackedemail_item_id
WHERE t.member_id=$member_id";
$where_query = ' AND p.dup_id IS NULL';
// if (!empty($data['start_date'])) {
// $where_query .= " AND DATE(created) >= '" . pg_escape_string($data['start_date']) . "'";
// }
// if (!empty($data['end_date'])) {
// $where_query .= " AND DATE(created) <= '" . pg_escape_string($data['end_date']) . "'";
// }
// member_id
// if (!empty($data['member_id_filter']) && !isset($error['member_id_filter'])) {
// $from_query .= " AND member_id = ${data['member_id_filter']}";
// }
// subject
if (!empty($data['subject']) && !isset($error['subject'])) {
$from_query .= " AND lower(t.subject) like '%" . pg_escape_string(strtolower($data['subject'])) . "%'";
}
// sender
if (!empty($data['message_from']) && !isset($error['message_from'])) {
$from_query .= " AND lower(t.message_from) like '%" . pg_escape_string(strtolower($data['message_from'])) . "%'";
}
if (isset($data['export']) && $data['export'] === 'ZIP') {
$total_record = (int) $this->read_replica->query($count_query . $from_query . $where_query)->result_array()[0]['all_count'];
if (!$total_record) {
header('Content-Type: application/json');
http_response_code(404);
return;
}
$start = 0;
$limit_export = 1000;
$query = "${select_query} ${from_query} ${where_query} ORDER BY created LIMIT ${limit_export} OFFSET ${start}";
$filePath = $_SERVER['DOCUMENT_ROOT']
. DIRECTORY_SEPARATOR
. self::TEMP_DIRECTORY_DOWNLOAD;
$filename = 'download-' . date("Y-m-d-H-i-s") . ".zip";
do {
if (ob_get_level()) {
ob_end_clean();
}
$data = $this->read_replica->query($query);
$this->export($data, $filePath, $filename);
$start += $limit;
} while ($start + $limit <= $total_record);
$this->send_file($filePath, $filename);
return;
}
$mysql = $select_query . $from_query . $where_query;
list($data['tpages'], $data['tpage'], $data['ttotal_pages']) = $this->paginateQuery($mysql, $limit, $member_tracked_offset);
$mysql = "SELECT (ROW_NUMBER () OVER (ORDER BY created DESC)) as No, foo.id, foo.created, foo.message_date, foo.subject FROM (${mysql}) AS foo ORDER BY created DESC LIMIT ${limit} OFFSET ${member_tracked_offset}";
error_log($mysql);
$query = $this->read_replica->query($mysql);
$this->table->set_heading('No.', array('data' => 'ID', 'style' => 'width:10px'), 'Created', array('data' => 'Send Date', 'style' => 'width:150px'), 'Subject');
// Email receipt
$data['member_tracked'] = $this->table->generate($query);
// get email data last fetch info
$mysql = "SELECT oauth2.email AS email_account,
CASE WHEN members.status = 1 THEN 'Active' ELSE 'InActive' END AS account_status,
CASE WHEN members.email_connected = 1 THEN 'Connected' ELSE 'Disconnected' END AS email_status,
members.last_email AS last_data_fetch,
oauth2.expires_in
FROM members
LEFT JOIN (SELECT member_id, email, expires_in FROM oauth2_tokens WHERE member_id = ? ORDER BY expires_in DESC LIMIT 1) AS oauth2
ON oauth2.member_id = members.id
WHERE members.id = ?;";
$data['email_data_fetch_info'] = (array) $this->read_replica->query($mysql, [$member_id, $member_id])->row();
return $data;
}
/**
* get all email parse fail
*
* @param int $member_id Member ID
* @return array
*/
private function viewEmailFail( $member_id, $limit, $offset ) {
try {
$sql = sprintf( "
SELECT id, subject, date_parsed, %s
from trackedemail_item
where member_id = %s and parsed_status = 0
order by id desc
", '\'process \' AS Action', $member_id );
// pagination
list( $data['pages'], $data['page'], $data['total_pages'] ) = $this->paginateQuery( $sql, $limit, $offset );
$config = [];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$data['pagination_config'] = $config;
// prepare table
$this->table->set_heading(
'No.', array('data' => 'ID', 'style' => 'width:10px'), 'Subject', 'Parsed', 'Action'
);
// excute query
$excute = sprintf( "
SELECT ( ROW_NUMBER() OVER (order by date_parsed desc) ) as No, *
from ( %s ) as foo
order by date_parsed desc
limit %d
offset %d
", $sql, $limit, $offset );
$results = $this->read_replica->query( $excute );
$data['member_parsedemailfail'] = $this->table->generate( $results );
// catch db errors
$errors = $this->read_replica->error();
if ( isset( $errors['message'] ) and !empty( $errors['message'] ) ) {
throw new Exception( $errors['message'] );
}
return $data;
} catch (Exception $e) {
echo $e->getMessage();
}
}
private function viewMerged($member_id, $data, $limit, $offset) {
$limit = 15;
$mysql = "SELECT * FROM (";
$mysql .= "SELECT 'ACC' AS acc_email,id,time AS travel_date,amount*0.01 AS cost, description FROM members_bankimport WHERE member_id =" . $member_id . " UNION ";
$mysql .= "SELECT 'EMAIL' AS acc_email,p.id,p.travel_date,p.cost,p.transport_provider_id::text AS description FROM parsedemail_item p LEFT JOIN trackedemail_item t ON t.id=p.trackedemail_item_id WHERE t.member_id =" . $member_id . " ";
$mysql .= ") AS FOO WHERE 1 = 1 AND p.dup_id IS NULL ";
$errors = isset($data['errors']) ? $data['errors'] : null;
// travel date
if (!empty($data['start_travel_date']) && !isset($errors['start_travel_date'])) {
$mysql .= " AND DATE(FOO.travel_date) >= '" . pg_escape_string($data['start_travel_date']) . "'";
}
if (!empty($data['end_travel_date']) && !isset($errors['end_travel_date'])) {
$mysql .= " AND DATE(FOO.travel_date) <= '" . pg_escape_string($data['end_travel_date']) . "'";
}
if (empty($data['start_travel_date']) && empty($data['end_travel_date'])) {
$data['start_travel_date'] = date('Y-m-d', strtotime('-1 month'));
$data['end_travel_date'] = date('Y-m-d');
$mysql .= " AND DATE(FOO.travel_date) >= '" . $data['start_travel_date'] . "'"
. " AND DATE(FOO.travel_date) <= '" . $data['end_travel_date'] . "'";
}
// cost
if (!empty($data['start_cost']) && !isset($errors['start_cost'])) {
$mysql .= " AND FOO.cost >= " . pg_escape_string($data['start_cost']) . "";
}
if (!empty($data['end_cost']) && !isset($errors['end_cost'])) {
$mysql .= " AND FOO.cost <= " . pg_escape_string($data['end_cost']) . "";
}
if (!empty($data['acc_email']) && $data['acc_email'] !== 'ALL') {
$mysql .= " AND FOO.acc_email = '" . pg_escape_string($data['acc_email']) . "'";
}
list($data['pages'], $data['page'], $data['total_pages']) = $this->paginateQuery($mysql, $limit, $offset);
$mysql .= " ORDER BY travel_date DESC LIMIT ${limit} OFFSET ${offset}";
error_log($mysql);
$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'),'Subject');
$data['member_merged'] = $this->table->generate($query);
$config = [];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$data['pagination_config'] = $config;
return $data;
}
private function paginateQuery($query, $limit, $offset) {
$delta = 5;
$pages = [];
$q = "SELECT count(*) AS total FROM (${query}) AS foo";
$r = $this->read_replica->query($q);
$p = $r->result_array()[0];
if (is_array($p) && isset($p["total"]) && $p["total"] > $limit) {
// Ok
} else {
return NULL;
}
$num = (int) ($p["total"] / $limit) + ($p["total"] % $limit == 0 ? 0 : 1);
$page = (int) ($offset / $limit);
if ($page > $delta && $page < $num) {
$pages['First'] = 0;
}
$sp = $page - $delta;
$lp = $page + $delta;
if ($sp < 0) {
$lp -= $sp;
$sp = 0;
}
if ($lp > $num) {
$sp -= ($lp - $num);
$lp = $num;
if ($sp < 0)
$sp = 0;
}
for ($i = $sp; $i < $lp; $i++) {
$pages[($i + 1)] = $i * $limit;
}
if ($page < ($num - $delta)) {
$pages['Last'] = $limit * ($num - 1);
}
return array($pages, 1 + $page, $num);
}
public function personalityUpdate() {
///member/personalityUpdate?proc=PROCESS&member_id=" + member_id + "&decision_group=" + decision_group_value
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
$member_id = (int) $this->input->get('member_id');
$decision_group = $this->input->get('decision_group');
if ($member_id > 0 && $decision_group != '') {
$q = "UPDATE members SET decision_group='$decision_group' WHERE id = $member_id AND decision_group<>'$decision_group'";
$this->db->query($q);
$this->refreshPersonalty();
echo " - Updated";
}
return;
} else {
echo 'ERROR!';
}
}
public function updateMemberProfile() {
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$member_id = (int) $this->input->post('member_id');
// echo "Ameye";
$data = $this->memberProfile($member_id);
$this->load->view('member/extra/member_profile', $data);
return;
} else {
echo 'ERROR!';
}
}
private function memberProfile($member_id) {
$this->load->model('combo_model');
$q = "SELECT p.*,m.* FROM members m LEFT JOIN members_profile p ON p.member_id = m.id WHERE m.id='${member_id}'";
$r = $this->read_replica->query($q);
$f = $r->result_array();
$p = (is_array($f) && count($f) > 0) ? $f[0] : [];
//v ar_dump($p);
$decision_group_combo = $this->combo_model->getDescisionGroupCombo("decision_group", $p['decision_group']);
$data = [
'member_id' => $member_id,
'street1' => isset($p['street1']) ? $p['street1'] : '',
'street2' => isset($p['street2']) ? $p['street2'] : '',
'city' => isset($p['city']) ? $p['city'] : '',
'zipcode' => isset($p['zipcode']) ? $p['zipcode'] : '',
'state' => isset($p['state']) ? $p['state'] : '',
'country' => isset($p['country']) ? $p['country'] : '',
'decision_group' => $decision_group_combo,
'dat_array' => $p
];
$data['message'] = "";
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$data['street1'] = $this->input->post('street1');
$data['street2'] = $this->input->post('street2');
$data['city'] = $this->input->post('city');
$data['zipcode'] = $this->input->post('zipcode');
$data['state'] = $this->input->post('state');
$data['country'] = $this->input->post('country');
if ($data['street1'] != '' && $data['city'] != '' && $data['zipcode'] != '' &&
$data['state'] != '' && $data['country'] != '') {
if (isset($p["id"]) && $p["id"] > 0) {
//* $q = "UPDATE members_profile SET ";
$q .= "street1='" . pg_escape_string($data['street1']) . "',";
$q .= "street2='" . pg_escape_string($data['street2']) . "',";
$q .= "city='" . pg_escape_string($data['city']) . "',";
$q .= "zipcode='" . pg_escape_string($data['zipcode']) . "',";
$q .= "state='" . pg_escape_string($data['state']) . "',";
$q .= "country='" . pg_escape_string($data['country']) . "'";
$q .= " WHERE id=" . ((int) $p["id"]) . " RETURNING id";
//* /
} else {
//* $q = "INSERT INTO members_profile (member_id,street1,street2,city,zipcode,state,country) VALUES(";
$q .= "'" . $member_id . "',";
$q .= "'" . pg_escape_string($data['street1']) . "',";
$q .= "'" . pg_escape_string($data['street2']) . "',";
$q .= "'" . pg_escape_string($data['city']) . "',";
$q .= "'" . pg_escape_string($data['zipcode']) . "',";
$q .= "'" . pg_escape_string($data['state']) . "',";
$q .= "'" . pg_escape_string($data['country']) . "'";
$q .= ") RETURNING id";
//* /
}
try {
error_log($q);
$r = $this->db->query($q);
$p = $r->result_array();
$data['message'] = "Profile saved!";
} catch (Exception $e) {
$data['message'] = $e->getMessage();
}
} else {
$data['message'] = "Invalid input!";
}
}
//Adding Personalty Refresh Table
$mysql = "SELECT m.id,m.decision_group,d.description,m.added
FROM members_personalty_log m
LEFT JOIN decision_group d ON d.dkey=m.decision_group
WHERE m.member_id = $member_id ORDER BY m.added DESC LIMIT 10";
$query = $this->read_replica->query($mysql);
$this->table->set_template($this->template);
$data["personalty_table"] = $this->table->generate($query);
return $data;
}
public function moveMemberCard() {
//#define SAVVYEXT_BKO_MOVECARD 100029
$direction = (int) $this->input->get('direction');
$card_id = (int) $this->input->get('card_id');
$member_id = (int) $this->input->get('member_id');
// if ($card_id > 0 && $member_id > 0 && $direction >= 0) {
$in = array();
echo 'aaaaa666';
$in['action'] = SAVVYEXT_BKO_MOVECARD;
$in['card_id'] = $card_id;
$in['direction'] = $direction;
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
if ($ret == PHP_API_OK) {
$message = 'moved';
} else {
$message = $out['status'];
}
// }
}
private function getSlideCardsData($member_id, $decision_group, $data) {
$data["member_id"] = $member_id;
$data["dynamic_cards"] = "";
$mysql = " SELECT ''||c.button1_action||' ]Card Status : '||c.status||'Card Order : '||c.card_order As title,c.description,
c.card_behavior,c.card_country,c.card_time
FROM main_cards c WHERE c.status <> 0 AND c.deleted IS NULL AND c.id IN (SELECT dc.card_id FROM decision_group dg LEFT JOIN decision_cards dc ON dc.decision_id = dg.id WHERE dg.dkey='$decision_group' AND dc.status=1) ORDER by c.card_order ASC , c.id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'PIC', 'style' => 'width:100px')
, 'Info'
, 'Description'
, 'Behavior'
, 'Country'
, 'Time'
);
$data['available_cards'] = $this->table->generate($query);
$mysql = " SELECT ''||c.button1_action||' ]Assign Status : '||ca.status||'Card Order : '||c.card_order As title,c.description,
c.card_behavior,c.card_country,c.card_time
FROM members_card_assign ca LEFT JOIN main_cards c ON c.id=ca.card_id WHERE ca.status <> 0 AND c.status=1 AND c.deleted IS NULL AND ca.member_id =$member_id." . " ORDER by c.card_order ASC , ca.id DESC";
$query = $this->read_replica->query($mysql);
$this->table->set_heading(array('data' => 'PIC', 'style' => 'width:100px')
, 'Info'
, 'Description'
, 'Behavior'
, 'Country'
, 'Time'
);
$data['assigned_cards'] = $this->table->generate($query);
return $data;
}
/*** Todo: call API to get all App cards of member
* @param $member_id
* @return array
*/
private function getAppHomeFeedCards($member_id)
{
$out = [];
$inX["mode"] = '100';
$inX["member_id"] = $member_id;
$inX["action"] = SAVVYEXT_BKO_CARDASSIGNED;
$inX["card_type"] = '22000';
$inX["card_count"] = '100';
$inX["pid"] = 100;
$this->savvy_api_clearly($inX, $out);
$total = $out["total_record"];
$res["result_list"] = [];
for ($i = 0; $i < $total; $i++) {
$key = sprintf("%05d", $i);
$res["result_list"][] = array(
"assign_id" => $out["assign_id_${key}"],
"card_id" => $out["card_id_${key}"],
"can_save" => $out["can_save_${key}"],
"name" => $out["name_${key}"],
"short_title" => $out["short_title_${key}"],
"title" => $out["title_${key}"],
"description" => $out["description_${key}"],
"background_picture" => $out["background_picture_${key}"],
"button1" => $out["button1_${key}"],
"button1_text" => $out["button1_text_${key}"],
"short_title" => $out["short_title_${key}"],
"button1_action" => $out["button1_action_${key}"],
"expires" => $out["expires_${key}"],
"template" => $out["template_${key}"],
"card_canexpire" => $out["card_canexpire_${key}"],
"card_action_type" => $out["card_action_type_${key}"],
"card_action_data" => $out["card_action_data_${key}"],
"titleshow" => $out["titleshow_${key}"],
"multiple_answer" => $out["multiple_answer_${key}"],
"use_short_title" => $out["use_short_title_${key}"],
"target_key" => $out["target_key_${key}"],
"target_text" => $out["target_text_${key}"],
"subscribe" => $out["subscribe_${key}"],
"long_description" => $out["long_description_${key}"],
"card_behavior" => $out["card_behavior_${key}"],
"card_type" => $out["card_type_${key}"],
"card_time" => $out["card_time_${key}"],
"card_country" => $out["card_country_${key}"],
"card_location" => $out["card_location_${key}"],
"latitude" => $out["latitude_${key}"],
"longitude" => $out["longitude_${key}"],
"card_order" => $out["card_order_${key}"],
"background_color" => $out["background_color_${key}"],
"blog_id" => $out["blog_id_${key}"]
);
}
return $res["result_list"];
}
/*** Todo: get member card assign
* @param $member_id
* @param $data
* @param string $mode 'ALL' is default value to all member cards - 'APP': to get home feed app cards
* @return mixed
*/
private function getSlideCards($member_id, $data, $mode = 'ALL') {
$data['mode'] = $mode;
$table_open = $this->template['table_open'];
$this->template['table_open'] = "";
$this->table->set_template($this->template);
$this->template['table_open'] = $table_open;
$where = "ca.status = 1 AND c.status=1 AND c.deleted IS NULL AND ca.member_id =$member_id";
// *** only app filter ***
if($mode == 'APP'){
$data['app_card_result_list'] = $this->getAppHomeFeedCards($member_id);
}
$order_by = "ca.list_order ASC , ca.id DESC";
$mysql = " SELECT ''||CASE WHEN ca.cat IS NOT NULL THEN ca.cat ELSE c.button1_action END||' ]' As title,c.description ,
c.card_order as card_list_order,
ct.country as card_country,
'DEL
' AS Del,
'
' AS Move
FROM members_card_assign ca
LEFT JOIN main_cards c ON c.id=ca.card_id
LEFT JOIN country as ct ON c.card_country=ct.code
WHERE $where ORDER by $order_by";
$query = $this->read_replica->query($mysql);
// $this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'),'Subject');
$data['member_cards'] = $this->table->generate($query);
// check current member card ids to check added
$member_card_ids = $this->read_replica->query("SELECT ca.card_id
FROM members_card_assign ca
LEFT JOIN main_cards c ON c.id=ca.card_id
WHERE ca.status = 1 AND c.status=1 AND c.deleted IS NULL AND ca.member_id = $member_id
ORDER by ca.list_order ASC , ca.id DESC")->result_array();
$data['member_card_ids'] = array_map('intval', array_column($member_card_ids,'card_id'));
$data["card_category_value"] = $this->input->get('card_category');
$this->load->library('pagination');
$config = array();
$q = "SELECT * FROM main_cards WHERE status = 1 AND title IS NOT NULL";
if ($data["card_category_value"] != '' && $data["card_category_value"] != '0') {
$q .= " AND button1_action='" . pg_escape_string($data["card_category_value"]) . "'";
}
$q .= " ORDER BY id DESC ";
$query = $this->read_replica->query($q);
$data["limit"] = 6;
$this->load->model('combo_model');
$data['card_category'] = $this->combo_model->getCardCategoryCombo('card_category', $data['card_category_value']);
$config["total_rows"] = $query->num_rows();
// /member/viewMemberAction?proc=PROCESS&action_name=MCARDS&member_id=3&limit=50&offset=0
$config["base_url"] = '#';
$config["per_page"] = $data["limit"];
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$config['attributes'] = array('onclick' => 'return getSlideCards(this,' . $member_id . ',\'' . $data["card_category_value"] . '\')');
$this->pagination->initialize($config);
//$ page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
$page = $data["offset"];
$page = is_numeric($page) ? $page : 0;
$data["links"] = $this->pagination->create_links();
$q = "SELECT id,'['||button1_action||'] -'||title||''||name||' ','Add
' AS action "
. "FROM main_cards "
. "WHERE status = 1 AND deleted IS NULL AND title IS NOT NULL ";
if ($data["card_category_value"] != '' && $data["card_category_value"] != '0') {
$q .= " AND button1_action='" . pg_escape_string($data["card_category_value"]) . "'";
}
$q .= " ORDER BY id DESC LIMIT " . $data["limit"] . " OFFSET " . $page;
$query = $this->read_replica->query($q);
$this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Description', array('data' => 'Action', 'style' => 'width:30px'));
$tmpl = array(
'table_open' => ''
);
$this->table->set_template($this->template);
$data["available_cards"] = $this->table->generate($query);
return $data;
}
private function plotFacedata($member_id) {
$data = array();
$q = "SELECT parsedemail_item.*,parsedemail_item_advice_google.id AS gid,parsedemail_item_advice_google.routes,trackedemail_item.id as tid ";
$q .= " FROM trackedemail_item, parsedemail_item ";
$q .= " LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id ";
$q .= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id=${member_id}";
$q .= " AND parsedemail_item.dup_id IS NULL";
$q .= " ORDER BY parsedemail_item.travel_date DESC, parsedemail_item.travel_date_end DESC";
//e cho $q;
$query = $this->read_replica->query($q);
$data['member_parsedetail'] = $this->table->generate($query);
// grouping the transactions by trnasport type
$q = "
SELECT transport_providers.name,count(parsedemail_item.id)
FROM trackedemail_item, parsedemail_item
LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id
LEFT JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id
AND trackedemail_item.member_id= ${member_id}
AND parsedemail_item.dup_id IS NULL
GROUP BY transport_providers.name";
$query = $this->read_replica->query($q);
$data['plot_transpotercount'] = $query->result_array();
$q = "
SELECT count(parsedemail_item.id), EXTRACT(DOW FROM parsedemail_item.travel_date) AS dow
FROM trackedemail_item, parsedemail_item
LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id
LEFT JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id
AND trackedemail_item.member_id=${member_id}
AND parsedemail_item.dup_id IS NULL
GROUP BY dow ORDER BY dow";
$query = $this->read_replica->query($q);
$data['plot_dayofweekcount'] = $query->result_array();
$q = "
SELECT sum(parsedemail_item.cost), EXTRACT(DOW FROM parsedemail_item.travel_date) AS dow
FROM trackedemail_item, parsedemail_item
LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id
LEFT JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id
AND trackedemail_item.member_id=${member_id}
AND parsedemail_item.dup_id IS NULL
GROUP BY dow
ORDER BY dow";
$query = $this->read_replica->query($q);
$data['plot_dayofweekamount'] = $query->result_array();
$q = "
SELECT sum(parsedemail_item.distance), EXTRACT(DOW FROM parsedemail_item.travel_date) AS dow
FROM trackedemail_item, parsedemail_item
LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id
LEFT JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id
AND trackedemail_item.member_id=${member_id}
AND parsedemail_item.dup_id IS NULL
GROUP BY dow
ORDER BY dow";
$query = $this->read_replica->query($q);
$data['plot_dayofweekdistance'] = $query->result_array();
$q = "
SELECT count(parsedemail_item.id) AS trav_count, b.address AS location_start, c.address AS location_end
FROM trackedemail_item, parsedemail_item
LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id
LEFT JOIN address b ON b.id=parsedemail_item.location_start_id
LEFT JOIN address c ON c.id=parsedemail_item.location_end_id
WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id=${member_id}
AND parsedemail_item.dup_id IS NULL
GROUP BY b.address , c.address ORDER BY trav_count DESC LIMIT 10";
$query = $this->read_replica->query($q);
$this->table->set_heading(array('data' => 'Count', 'style' => 'width:10px'), 'Start Location', 'End Location');
$data['common_comute'] = $this->table->generate($query);
//p rint_r( $data['plot_transpotercount'] );
return $data;
}
public function deleteMemberCard() {
$id = (int) $this->input->get('card_id');
$member_id = (int) $this->input->get('member_id');
if ($id > 0 && $member_id > 0) {
$in = [];
$in['action'] = SAVVY_BKO_USERCARD_DELETE;
$in['id'] = $id;
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
if ($ret == PHP_API_OK) {
$message = '';
} else {
$message = $out['status'];
}
}
if ($message == '') {
echo "Deleted";
} else {
echo "Fail $id | $member_id";
}
return 0;
}
public function addMemberCard() {
$message = '';
$card_id = (int) $this->input->get('card_id');
$member_id = (int) $this->input->get('member_id');
if ($card_id > 0 && $member_id > 0) {
$in = [];
$in['action'] = SAVVY_BKO_USERCARD_ADD;
$in['card_id'] = $card_id;
$in['member_id'] = $member_id;
$out = [];
$ret = $this->savvy_api($in, $out);
if ($ret == PHP_API_OK) {
$message = '';
} else {
$message = $out['status'];
}
}
if ($message == '') {
echo "Added";
} else {
echo "Card already assigned";//"Fail $card_id | $member_id";
}
return 0;
}
public function viewtrackedgroup() {
$this->load->model('member_model');
$this->load->library('pagination');
$this->setFormRuleForMemberTracking();
$member_tracking_value = $this->getMemberTrackingValue();
$this->form_validation->set_data($member_tracking_value);
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
if ($this->isValidFromTo($member_tracking_value) === FALSE) {
$errors['from_to'] = 'Selected date range is invalid';
}
if ($this->isSearchByRadius($member_tracking_value) === FALSE) {
$errors['searchByRadius'] = 'Please input all required field for search by radius, latitude, longitude';
}
if ($errors !== NULL) {
echo json_encode($this->generate_error_message($errors));
return;
}
$row_no = $member_tracking_value['row_no'];
$filters = array_filter($member_tracking_value['filters'], function($ele) {
return $ele !== '';
});
// Row per page
$rowperpage = 13;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->member_model->get_all_record_member_tracking_by_tracked_group($filters));
// Get records
$records = $this->member_model->get_all_record_member_tracking_by_tracked_group($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/viewtrackedgroup';
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['suffix'] = '?traked_group=' . $filters['traked_group'];
$config['first_url'] = base_url() . '/member/viewtrackedgroup/0?traked_group=' . $filters['traked_group'];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
foreach ($data['result'] as $ele) {
$data['locations'][] = [
'lat' => (float) $ele['lat'],
'lng' => (float) $ele['lng']
];
}
echo json_encode($data);
}
function viewTrackedAdvice() {
$tracked_id = $this->input->get('tracked_id');
$this->load->library('table');
$this->table->set_template($this->template);
$mysql = "SELECT * FROM parsedemail_item_advice WHERE parsedemail_item_id= $tracked_id";
$query = $this->read_replica->query($mysql);
//$ this->table->set_heading(array('data' => 'ID', 'style' => 'width:10px'), 'Linked Email', 'Provider');
echo $data['parsedemail_option'] = $this->table->generate($query);
}
public function parse() {
global $savvyext;
$data = array();
if ($this->input->get()) {
$id = $this->input->get('id');
$url = $savvyext->cfgReadChar('system.oauth2_url') . "/parse/${id}";
$token = $savvyext->cfgReadChar('system.oauth2_token');
$result = $this->get_service_call($url, $token);
if ( $result ) {
$this->output->set_content_type('application/json')->set_output( json_encode( $result ) );
}
} else {
echo "Invalid request";
}
return;
}
public function advice() {
global $savvyext;
$data = array();
if ($this->input->get()) {
$id = $this->input->get('id');
$url = $savvyext->cfgReadChar('system.oauth2_url') . "advice/${id}";
$token = $savvyext->cfgReadChar('system.oauth2_token');
$this->get_service_call($url, $token);
} else {
echo "Invalid request";
}
return;
}
public function emailstatus($member_id) {
global $savvyext;
$url = $savvyext->cfgReadChar('system.oauth2_url') . "/mailstatus/${member_id}";
$token = $savvyext->cfgReadChar('system.oauth2_token');
$result = $this->get_service_call($url, $token);
if ( $result ) {
return $this->get_service_call($url, $token);
}
return null;
}
private function get_service_call($url, $token) {
$opts = array(
'http' => array(
'method' => "GET",
'header' =>
"Accept: application/json\r\n" .
"Authorization: Server-Token ${token}\r\n",
)
);
$context = stream_context_create($opts);
$data = file_get_contents($url, false, $context);
$res = json_decode($data, true);
if ($res != NULL && is_array($res)) {
if (isset($res["code"]) && $res["code"] == 0) {
foreach ($res["data"] as $key => $val)
if ($val == '' || $val == 'N/A')
unset($res["data"][$key]);
}
else if (isset($res["error"])) {
return;
} else {
return;
}
return $res;
} else {
return;
}
}
public function profileImageUpload() {
$data = array("message" => "Unexpected error");
$catid = (int) $this->input->post('catid');
$member_id = (int) $this->input->post('member_id');
if ($member_id > 0 && $catid == 5) { /* 5 is "member_profile_image" */
$this->load->library('uploadhelper');
ob_start();
$data = $this->uploadhelper->cardimagesPost($data, $this); // , $uniqueId);
$str = ob_get_clean();
if (substr($str, 0, 8) == 'Uploaded' && (!isset($data["card_image_id"]) || $data["card_image_id"] < 1)) {
if (preg_match('/^Uploaded ([a-zA-Z0-9]+) to gs:\/\/(.*)\/(.*)\.(.*)$/', $str, $arr) == 1 &&
count($arr) > 4 && $arr[2] == 'adminsavvy_card_image_category_country') {
$uniqueId = pg_escape_string($arr[3]);
$fmt = $arr[4];
$q = "SELECT id FROM card_images WHERE uniqueid='${uniqueId}'";
$r = $this->read_replica->query($q);
$f = $r->row_array();
if ($f != NULL && is_array($f) && $f["id"] > 0) {
$data["card_image_id"] = $f["id"];
}
$data["message"] = "Uploaded to ${uniqueId}.${fmt}";
$data["picture_format"] = $fmt;
} else {
$data["message"] = "Cannot get upload ID: " . $str;
}
}
if (isset($data["card_image_id"]) && $data["card_image_id"] > 0) {
$data["picture_format"] = $data["format"];
$data["message"] = "Uploaded new profile picture ID #" . $data["card_image_id"];
$q = "UPDATE members SET profile_picture='" . $data["card_image_id"] . "' where id=" . $member_id;
$this->db->query($q);
} else {
$data["message"] = "Upload failed: " . $str;
}
/* /Resize and Compress Image
$config['image_library']='gd2';
$config['source_image']='./assets/images/'.$data['file_name'];
$config['create_thumb']= FALSE;
$config['maintain_ratio']= FALSE;
$config['quality']= '60%';
$config['width']= 600;
$config['height']= 400;
$config['new_image']= './assets/images/'.$data['file_name'];
$this->load->library('image_lib', $config);
$this->image_lib->resize();
// */
} else {
$data["message"] = "Invalid ID";
}
error_log(json_encode($data));
echo json_encode($data);
}
// Tracked Group section
public function setFormRuleForTrackedGroup() {
$this->form_validation->set_rules('start_date', 'Start Date', 'callback_isValidDate');
$this->form_validation->set_rules('end_date', 'End Date', 'callback_isValidDate');
$this->form_validation->set_rules('member_id', 'Member ID', 'required|numeric');
$this->form_validation->set_rules('row_no', 'Page', 'numeric');
}
public function getTrackedGroupValue() {
return [
'row_no' => $this->input->post('row_no'),
'start_date' => $this->input->post('filters')['start_date'] ?? '',
'end_date' => $this->input->post('filters')['end_date'] ?? '',
'member_id' => $this->input->post('filters')['member_id'],
'filters' => $this->input->post('filters')
];
}
public function isValidDate($date) {
if (!$date) {
return true;
}
$matches = [];
$result = preg_match_all("/^([0-9]{4})-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $date, $matches, PREG_SET_ORDER);
if ($result == 0 || $result == false) {
$this->form_validation->set_message('isValidDate', 'Date input is invalid');
return false;
}
return true;
}
public function isValidFromTo($date) {
if ($date['start_date'] === '' || $date['end_date'] === '')
return TRUE;
return (strtotime($date['start_date']) > strtotime($date['end_date'])) ? FALSE : TRUE;
}
public function loadTrackedGroupRecord() {
$this->load->model('member_model');
$this->load->library('pagination');
$this->setFormRuleForTrackedGroup();
$tracked_group_value = $this->getTrackedGroupValue();
$this->form_validation->set_data($tracked_group_value);
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
if ($this->isValidFromTo($tracked_group_value) === FALSE) {
$errors['from_to'] = 'Selected date range is invalid';
}
if ($errors !== NULL) {
echo json_encode($this->generate_error_message($errors));
return;
}
$row_no = $tracked_group_value['row_no'];
$filters = array_filter($tracked_group_value['filters'], function($ele) {
return $ele !== '';
});
// Row per page
$rowperpage = 13;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$this->session->set_userdata('tracked_group_filters', $filters);
$allcount = count($this->member_model->get_all_record_member_tracking($filters));
// Get records
$records = $this->member_model->get_all_record_member_tracking($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadTrackedGroupRecord';
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
public function setFormRuleForMemberTracking() {
$this->form_validation->set_rules('start_date', 'Start Date', 'callback_isValidDate');
$this->form_validation->set_rules('end_date', 'End Date', 'callback_isValidDate');
$this->form_validation->set_rules('row_no', 'Page', 'numeric');
$this->form_validation->set_rules('device_id', 'Device ID', 'numeric');
$this->form_validation->set_rules('radius', 'Radius', 'numeric');
$this->form_validation->set_rules('lat', 'Latitude', 'callback_isValidGeometry');
$this->form_validation->set_rules('lng', 'Longitude', 'callback_isValidGeometry');
$this->form_validation->set_rules('traked_group', 'Tracked Group', 'numeric');
}
public function getMemberTrackingValue() {
return [
'row_no' => $this->input->post('row_no'),
'start_date' => $this->input->post('filters')['start_date'] ?? '',
'end_date' => $this->input->post('filters')['end_date'] ?? '',
'device_id' => $this->input->post('filters')['device_id'] ?? '',
'radius' => $this->input->post('filters')['radius'] ?? '',
'lat' => $this->input->post('filters')['lat'] ?? '',
'lng' => $this->input->post('filters')['lng'] ?? '',
'traked_group' => $this->input->post('filters')['traked_group'] ?? '',
'filters' => $this->input->post('filters')
];
}
public function isValidGeometry($geo) {
if ($geo === NULL || $geo === '')
return TRUE;
if (preg_match('/^(\-?\d+(\.\d+)?)$/', $geo)) {
return TRUE;
} else {
$this->form_validation->set_message('isValidGeometry', 'Location is invalid');
return FALSE;
}
}
public function isSearchByRadius($data) {
$count = 0;
if (isset($data['lat']))
$count++;
if (isset($data['lng']))
$count++;
if (isset($data['radius']))
$count++;
return $count === 3 || $count === 0;
}
public function generate_error_message($data) {
$errors['error'] = '';
foreach ($data as $ele) {
$errors['error'] .= "${ele}
\n";
}
return $errors;
}
// Tracked Group section
// Members Account Receipt section
public function getMembersAccountReceiptsValue() {
return [
'row_no' => $this->input->post('row_no'),
'start_date' => $this->input->post('start_date') ?? '',
'end_date' => $this->input->post('end_date') ?? '',
'currency' => $this->input->post('currency') ?? '',
'category' => $this->input->post('category') ?? '',
'provider_category' => $this->input->post('provider_category') ?? '',
'merchant_name' => $this->input->post('merchant_name') ?? '',
'member_id' => $this->input->post('member_id') ?? ''
];
}
public function setFormRuleForMembersAccountReceipts() {
$this->form_validation->set_rules('row_no', 'Page', 'numeric');
$this->form_validation->set_rules(
'start_date', 'Start Date', 'regex_match[/\d{4}-\d{2}-\d{2}/]');
$this->form_validation->set_rules(
'end_date', 'End Date', 'regex_match[/\d{4}-\d{2}-\d{2}/]');
$this->form_validation->set_rules('currency', 'Currency', 'trim|strtolower');
}
public function loadMembersAccountReceipts() {
$this->load->model('member_model');
$this->load->library('pagination');
$this->setFormRuleForMembersAccountReceipts();
$members_account_receipts = $this->getMembersAccountReceiptsValue();
$this->form_validation->set_data($members_account_receipts);
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
if ($errors !== NULL) {
echo json_encode($this->generate_error_message($errors));
return;
}
$row_no = $members_account_receipts['row_no'];
unset($members_account_receipts['row_no']);
$filters = array_filter($members_account_receipts, function($ele) {
return $ele !== '';
});
// Row per page
$rowperpage = 13;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->member_model->get_all_record_members_bankimport($filters));
// Get records
$records = $this->member_model->get_all_record_members_bankimport($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadMembersAccountReceipts';
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadMembersAccountReceipts/0?' . http_build_query($filters);
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
// Members Account Receipt section
// Email receipts Section
public function setFormRuleForEmailReceipts() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules(
'start_travel_date', 'Start Date', $date_pattern
);
$this->form_validation->set_rules(
'end_travel_date', 'End Date', $date_pattern);
$this->form_validation->set_rules(
'duration_start', 'Duration Start', 'is_natural');
$this->form_validation->set_rules(
'duration_end', 'Duration End', 'is_natural'
);
$this->form_validation->set_rules(
'cost_start', 'Cost Start', 'callback_numeric'
);
$this->form_validation->set_rules(
'cost_end', 'Cost End', 'callback_numeric'
);
$this->form_validation->set_rules(
'distance_start', 'Distance Start', 'callback_numeric'
);
$this->form_validation->set_rules(
'distance_end', 'Distance End', 'callback_numeric'
);
}
public function numeric($number) {
if (empty($number))
return TRUE;
return is_numeric($number);
}
public function setDataForEmailReceipts(&$data) {
if ($this->input->post()) {
parse_str($this->input->post('filters'), $filters);
$data['subject'] = trim($filters['subject']);
$data['message_from'] = trim($filters['message_from']);
$data['export'] = trim($this->input->post('export'));
} else {
$data['start_date'] = trim($this->input->get('start_date'));
$data['end_date'] = trim($this->input->get('end_date'));
$data['start_travel_date'] = trim($this->input->get('start_travel_date'));
$data['end_travel_date'] = trim($this->input->get('end_travel_date'));
$data['duration_start'] = trim($this->input->get('duration_start'));
$data['duration_end'] = trim($this->input->get('duration_end'));
$data['cost_start'] = trim($this->input->get('cost_start'));
$data['cost_end'] = trim($this->input->get('cost_end'));
$data['distance_start'] = trim($this->input->get('distance_start'));
$data['distance_end'] = trim($this->input->get('distance_end'));
$data['surge'] = trim($this->input->get('surge'));
$data['subject'] = trim($this->input->get('subject'));
$data['message_from'] = trim($this->input->get('message_from'));
}
}
public function validateForEmailReceipts(&$data) {
$this->form_validation->set_data($data);
$this->setFormRuleForEmailReceipts();
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
$data['errors'] = $errors;
}
// Email receipts Section
// Saved Trips Section
public function setDataForSavedTrip(&$data) {
$this->load->model('combo_model');
$data['start_date'] = $this->input->get('start_date');
$data['end_date'] = $this->input->get('end_date');
$data['start_address'] = $this->input->get('start_address');
$data['end_address'] = $this->input->get('end_address');
$data['country_card'] = $this
->combo_model
->getCountryCombo('country_card', $this->input->get('country'));
}
public function setFormRuleForSavedTrips() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules('start_date', 'Start Date', $date_pattern);
$this->form_validation->set_rules('end_date', 'End Date', $date_pattern);
$this->form_validation->set_rules(
'country', 'Member ID', 'exist[country,code,country_card]'
);
$this->form_validation->set_rules(
'start_address', 'Start Address', 'callback_isExistAddress'
);
$this->form_validation->set_rules(
'end_address', 'End Address', 'callback_isExistAddress'
);
}
public function getSavedTripsValue() {
return [
'row_no' => $this->input->get('row_no'),
'member_id' => $this->input->get('member_id'),
'start_date' => $this->input->get('start_date') ?? '',
'end_date' => $this->input->get('end_date') ?? '',
'country' => $this->input->get('country_card') ?? '',
'trip_from' => $this->input->get('start_address') ?? '',
'trip_to' => $this->input->get('end_address') ?? '',
];
}
public function loadSavedTripsRecord() {
$this->load->model('member_model');
$this->load->library('pagination');
$saved_trips_value = $this->getSavedTripsValue();
$this->form_validation->set_data($saved_trips_value);
$this->setFormRuleForSavedTrips();
if ($this->form_validation->run() === FALSE) {
echo json_encode(validation_errors());
return;
}
$row_no = $saved_trips_value['row_no'];
unset($saved_trips_value['row_no']);
$filters = array_filter($saved_trips_value, function($ele) {
return $ele !== '';
});
// Row per page
$rowperpage = 13;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->member_model->get_all_record_saved_trips($filters));
// Get records
$records = $this->member_model->get_all_record_saved_trips(
$filters, $rowperpage, $row_no
);
// Pagination Configuration
$config['base_url'] = '/member/loadSavedTripsRecord';
$config['use_page_numbers'] = TRUE;
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadSavedTripsRecord?' . http_build_query($filters);
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
public function isExistAddress($id) {
$this->load->model('address_model', 'address');
if (!$this->address->getAddresses(['id' => $id])) {
$this->form_validation->set_message(
'isExistsAddress', 'Please enter an existing address'
);
return FALSE;
} else {
return TRUE;
}
}
public function getLocationByAddress() {
$this->load->model('address_model', 'address');
$location = $this->address->getAddresses([
'address' => trim($this->input->get('name')),
'page' => $this->input->get('page')
]);
echo json_encode([
'data' => $location['list'],
'total' => $location['total']
]);
}
// Saved Trips Section
// Merged Receipts Section
public function setDataForMergedReceipts(&$data) {
$this->load->model('combo_model');
$data['acc_email_card'] = $this
->combo_model
->getAccAndEmailCombo(
'acc_email_card', $this->input->get('acc_email_card')
);
$data['acc_email'] = $this->input->get('acc_email_card');
$data['start_travel_date'] = $this->input->get('start_travel_date');
$data['end_travel_date'] = $this->input->get('end_travel_date');
$data['start_cost'] = $this->input->get('start_cost');
$data['end_cost'] = $this->input->get('end_cost');
}
public function setFormRuleForMergedReceipts() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules(
'start_travel_date', 'Start Travel Date', $date_pattern
);
$this->form_validation->set_rules(
'end_travel_date', 'End Travel Date', $date_pattern
);
$this->form_validation->set_rules(
'start_cost', 'Cost Start', 'callback_numeric'
);
$this->form_validation->set_rules(
'end_cost', 'Cost End', 'callback_numeric'
);
}
public function validateForMergedReceipts(&$data) {
$this->form_validation->set_data($_GET);
$this->setFormRuleForMergedReceipts();
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
$data['errors'] = $errors;
}
// Merged Receipts Section
// GPS Trips section
// Tracking Trips Table
public function setDataForTrackingTrips(&$data) {
$data['tracking_trips'] = json_decode(
$this->input->get('tracking_trips'), true
) ?? [];
$data['tracking_trips'] = array_filter($data['tracking_trips'], function($ele) {
return trim($ele) !== '';
}
);
}
public function setFormRuleForTrackingTrips() {
$this->form_validation->set_rules(
'start_duration', 'Start Duration', 'callback_numeric'
);
$this->form_validation->set_rules(
'end_duration', 'End Duration', 'callback_numeric'
);
$this->form_validation->set_rules(
'start_distance', 'Start Distance', 'callback_numeric'
);
$this->form_validation->set_rules(
'end_distance', 'End Distance', 'callback_numeric'
);
}
public function validateForTrackingTrips(&$data) {
$this->form_validation->set_data($data['tracking_trips']);
$this->setFormRuleForTrackingTrips();
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
$data['errors']['tracking_trips'] = $errors;
}
// Tracking table
public function setDataForTracking(&$data) {
$data['tracking'] = json_decode($this->input->get('tracking'), true) ?? [];
$data['tracking'] = array_filter($data['tracking'], function($ele) {
return trim($ele) !== '';
}
);
}
public function setFormRuleForTracking() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules(
'start_date', 'Start Date', $date_pattern
);
$this->form_validation->set_rules(
'end_date', 'End Date', $date_pattern
);
$this->form_validation->set_rules(
'start_duration', 'Start Duration', 'callback_numeric'
);
$this->form_validation->set_rules(
'end_duration', 'End Duration', 'callback_numeric'
);
$this->form_validation->set_rules(
'start_distance', 'Start Distance', 'callback_numeric'
);
$this->form_validation->set_rules(
'end_distance', 'End Distance', 'callback_numeric'
);
}
public function validateForTracking(&$data) {
$this->form_validation->set_data($data['tracking']);
$this->setFormRuleForTracking();
$errors = null;
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
$data['errors']['tracking'] = $errors;
}
// GPS Trips section
// OAuth2 section
public function getOAuth2TokensRecord() {
return [
'email' => $this->input->get('email') ?? '',
'start_date' => $this->input->get('start_date') ?? '',
'end_date' => $this->input->get('end_date') ?? '',
'member_id' => $this->input->get('member_id')
];
}
public function loadOAuth2TokensRecord() {
$this->load->model('oauth2_model');
$this->load->library('pagination');
$filters = $this->getOAuth2TokensRecord();
$filters = array_filter($filters, function($ele) {
return $ele !== '';
});
// Row per page
$row_no = $this->input->get('row_no');
$rowperpage = 5;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->oauth2_model->get_all_record_oauth2_tokens($filters));
// Get records
$records = $this->oauth2_model->get_all_record_oauth2_tokens($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadOAuth2TokensRecord';
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadOAuth2TokensRecord/0?' . http_build_query($filters);
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
public function getOAuth2PullJobsRecord() {
return [
'start_date' => $this->input->get('start_date') ?? '',
'end_date' => $this->input->get('end_date') ?? '',
'member_id' => $this->input->get('member_id')
];
}
public function loadOAuth2PullJobsRecord() {
$this->load->model('oauth2_model');
$this->load->library('pagination');
$filters = $this->getOAuth2PullJobsRecord();
$filters = array_filter($filters, function($ele) {
return $ele !== '';
});
// Row per page
$row_no = $this->input->get('row_no');
$rowperpage = 5;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->oauth2_model->get_all_record_oauth2_pull_jobs($filters));
// Get records
$records = $this->oauth2_model->get_all_record_oauth2_pull_jobs($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadOAuth2PullJobsRecord';
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadOAuth2PullJobsRecord/0?' . http_build_query($filters);
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
public function getOAuth2PullThreadsRecord() {
return [
'search_term' => $this->input->get('search_term') ?? '',
'start_date' => $this->input->get('start_date') ?? '',
'end_date' => $this->input->get('end_date') ?? '',
'member_id' => $this->input->get('member_id')
];
}
public function loadOAuth2PullThreadsRecord() {
$this->load->model('oauth2_model');
$this->load->library('pagination');
$filters = $this->getOAuth2PullThreadsRecord();
$filters = array_filter($filters, function($ele) {
return $ele !== '';
});
// Row per page
$row_no = $this->input->get('row_no');
$rowperpage = 5;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->oauth2_model->get_all_record_oauth2_pull_threads($filters));
// Get records
$records = $this->oauth2_model->get_all_record_oauth2_pull_threads($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadOAuth2PullThreadsRecord';
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadOAuth2PullThreadsRecord/0?' . http_build_query($filters);
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
// OAuth2 section
// Msg-Alert section
public function getNotificationsValue() {
return [
'trigger_key' => $this->input->get('trigger_key') ?? '',
'msg_status' => $this->input->get('card_msg_status') ?? '',
'start_date' => $this->input->get('start_date') ?? '',
'end_date' => $this->input->get('end_date') ?? '',
'member_id' => $this->input->get('member_id')
];
}
public function loadNotificationsRecord() {
$this->load->model('notification_model');
$this->load->library('pagination');
$filters = $this->getNotificationsValue();
$filters = array_filter($filters, function($ele) {
return $ele !== '';
});
// Row per page
$row_no = $this->input->get('row_no');
$rowperpage = 5;
$cur_page = $row_no;
// Row position
if ($row_no != 0) {
$row_no = ($row_no - 1) * $rowperpage;
}
// All records count
$allcount = count($this->notification_model->get_all_record_notifications($filters));
// Get records
$records = $this->notification_model->get_all_record_notifications($filters, $rowperpage, $row_no);
// Pagination Configuration
$config['base_url'] = '/member/loadNotificationsRecord';
$config['suffix'] = '?' . http_build_query($filters);
$config['first_url'] = '/member/loadNotificationsRecord/0?' . http_build_query($filters);
$config['use_page_numbers'] = TRUE;
$config['total_rows'] = $allcount;
$config['per_page'] = $rowperpage;
$config['cur_page'] = $cur_page;
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
// Initialize
$this->pagination->initialize($config);
// Initialize $data Array
$data['pagination'] = $this->pagination->create_links();
$data['result'] = $records;
$data['row'] = $row_no;
echo json_encode($data);
}
// Msg-Alert section
public function export($r, $filePath, $filename) {
$this->load->helper('upload_file');
$this->load->helper('directory');
set_time_limit(0);
// THE UPLOAD DESITINATION - CHANGE THIS TO YOUR OWN
if (!file_exists($filePath)) {
if (!mkdir($filePath, 0777, true)) {
verbose(0, "Failed to create $filePath");
}
}
// create your zip file
$zipname = $filePath . DIRECTORY_SEPARATOR . $filename;
$zip = new ZipArchive;
if (file_exists($zipname)) {
$zip->open($zipname);
} else {
$zip->open($zipname, ZipArchive::CREATE);
}
while ($row = $r->unbuffered_row()) {
$f = (array) $row;
// create a temporary html file
$fd = fopen('php://temp/maxmemory:1048576', 'w');
if (false === $fd) {
die('Failed to create temporary file');
}
// write the data to csv
fwrite($fd, $f['message']);
// return to the start of the stream
rewind($fd);
// add the in-memory file to the archive, giving a name
$zip->addFromString($f['id'] . '.html', stream_get_contents($fd, -1));
//close the file
fclose($fd);
// create a temporary csv file
$fd = fopen('php://temp/maxmemory:1048576', 'w');
if (false === $fd) {
die('Failed to create temporary file');
}
// write the data to csv
unset($f['message']);
fputcsv($fd, array_keys($f), ",");
fputcsv($fd, array_values($f), ",");
// return to the start of the stream
rewind($fd);
// add the in-memory file to the archive, giving a name
$zip->addFromString($f['id'] . '.csv', stream_get_contents($fd, -1));
//close the file
fclose($fd);
}
// close the archive
$zip->close();
}
// Tracked Email Report
public function trackedemail() {
$this->load->library('table');
$this->table->set_heading([
'ID',
'Member ID',
'Subject',
'Created',
'Message From',
'Hash',
'Date Parsed',
'oauth2_pull_job_id',
'Message Date'
]);
$data['page_title'] = 'Tracked Email';
$this->table->set_template($this->template);
$data['tracked_email_table'] = $this->table->generate([[]]);
$this->renderMemberPage('view_trackedemail', $data);
}
function removeEmptyParamsRequest(&$params) {
$params['member_id_list'] = array_filter(explode(',', $params['member_id_filter']), function($val) {
return $val !== "";
});
if (count($params['member_id_list']) === 0) {
unset($params['member_id_list']);
}
$params = array_filter($params, function($val) {
return $val !== "";
});
}
public function trackedemail_search() {
$data['page_title'] = 'Tracked Email';
$this->load->model('member_model');
$this->load->library('table');
$this->table->set_template($this->template);
$this->table->set_heading([
'ID',
'Member ID',
'Subject',
'Created',
'Message From',
'Hash',
'Date Parsed',
'oauth2_pull_job_id',
'Message Date'
]);
$params = $this->getValueOfTrackedEmail();
$this->removeEmptyParamsRequest($params);
$errors = $this->validateValueForTrackedEmail($params);
$params = array_diff_key($params, $errors);
$data = array_merge(
$data, $params, $this->load_pagination(
$this->member_model
->get_tracked_email_record($params)
->result_array()[0]['all_count'], $params, 'trackedemail_search'
)
);
$records = $this->member_model->get_tracked_email_record(
$params, SELF::NONE_COUNT_RECORD, $data['limit'], $data['offset']
)->result_array();
foreach ($records as &$record) {
unset($record['message']);
}
$data['tracked_email_table'] = $this->table->generate($records);
$this->renderMemberPage('view_trackedemail', $data);
}
public function trackedemail_export_zip() {
$this->load->model('member_model');
$params = $this->getValueOfTrackedEmail();
$this->removeEmptyParamsRequest($params);
$errors = $this->validateValueForTrackedEmail($params);
$params = array_diff_key($params, $errors);
$total_record = (int) $this->member_model
->get_tracked_email_record($params)
->result_array()[0]['all_count'];
if (!$total_record) {
header('Content-Type: application/json');
http_response_code(404);
return;
}
$start = 0;
$limit = 1000;
$filePath = $_SERVER['DOCUMENT_ROOT']
. DIRECTORY_SEPARATOR
. self::TEMP_DIRECTORY_DOWNLOAD;
$filename = 'download-' . date("Y-m-d-H-i-s") . ".zip";
do {
if (ob_get_level()) {
ob_end_clean();
}
$data = $this->member_model->get_tracked_email_record(
$params, SELF::NONE_COUNT_RECORD, $limit, $start
);
$this->export($data, $filePath, $filename);
$start += $limit;
} while ($start + $limit <= $total_record);
$this->send_file($filePath, $filename);
}
public function send_file($filePath, $filename) {
if (file_exists($filePath . DIRECTORY_SEPARATOR . $filename)) {
header('Content-Type: application/zip');
header('Content-Disposition: attachment; filename="' . basename($filename) . '"');
header('Content-Length: ' . filesize($filePath . DIRECTORY_SEPARATOR . $filename));
readfile($filePath . DIRECTORY_SEPARATOR . $filename);
deleteDir($filePath);
}
}
public function validateValueForTrackedEmail($params) {
$this->load->library('form_validation');
$this->form_validation->set_data($params);
$this->setFormRuleForTrackedEmail();
$errors = [];
if ($this->form_validation->run() === FALSE) {
$errors = $this->form_validation->error_array();
}
return $errors;
}
public function setFormRuleForTrackedEmail() {
$date_pattern = 'regex_match[/\d{4}-\d{2}-\d{2}/]';
$this->form_validation->set_rules('start_created', 'Created date', $date_pattern);
$this->form_validation->set_rules('end_created', 'Created date', $date_pattern);
$this->form_validation->set_rules('start_message_date', 'Message date', $date_pattern);
$this->form_validation->set_rules('end_message_date', 'Message date', $date_pattern);
}
public function getValueOfTrackedEmail() {
$params = [];
if ($this->input->post()) {
parse_str($this->input->post('filters'), $filters);
$params['subject'] = trim($filters['subject']);
$params['message_from'] = trim($filters['message_from']);
$params['start_created'] = trim($filters['start_created']);
$params['end_created'] = trim($filters['end_created']);
$params['start_message_date'] = trim($filters['start_message_date']);
$params['end_message_date'] = trim($filters['end_message_date']);
$params['sql_raw'] = trim($filters['sql_raw']);
$params['member_id_filter'] = trim($filters['member_id_filter']);
} else {
$params['subject'] = trim($this->input->get('subject'));
$params['message_from'] = trim($this->input->get('message_from'));
$params['start_created'] = trim($this->input->get('start_created'));
$params['end_created'] = trim($this->input->get('end_created'));
$params['start_message_date'] = trim($this->input->get('start_message_date'));
$params['end_message_date'] = trim($this->input->get('end_message_date'));
$params['sql_raw'] = trim($this->input->get('sql_raw'));
$params['member_id_filter'] = trim($this->input->get('member_id_filter'));
}
return $params;
}
public function export_member_tracking() {
if (empty($this->session->tracked_group_filters)) {
die('something went wrong, try again later');
}
set_time_limit(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=member-tracking-' . date("Y-m-d") . '.csv');
header('Cache-Control: max-age=0');
$this->load->model('member_model');
$rs = $this->member_model->get_all_record_member_tracking($this->session->tracked_group_filters, null, null, true);
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;
}
private function viewOtherReceipts($member_id, $data, $limit, $member_parsedemail_offset, $member_tracked_offset, $error) {
$data['member_parsedemail_offset'] = $member_parsedemail_offset;
$data['member_tracked_offset'] = $member_tracked_offset;
$limit = 100;
//PARSED
$offset = $member_parsedemail_offset;
$mysql = " SELECT ";
$mysql .= " p.id,p.trackedemail_item_id,tp.name, p.receipt_datetime, p.amount,p.created, COALESCE(p.updated,p.created)";
$mysql .= " FROM parsedemail_item_payment p LEFT JOIN trackedemail_item t ON t.id=p.trackedemail_item_id ";
$mysql .= " LEFT JOIN transport_providers tp ON p.transport_provider_id=tp.id";
$mysql .= " LEFT JOIN trip_surge_price e ON (e.data_source_id=p.id AND e.data_source=1) ";
$mysql .= " LEFT JOIN trip_price_comparison f ON (f.data_source_id=p.id AND e.data_source=1) ";
$mysql .= " WHERE t.member_id = ${member_id} ";
// travel date
if (!empty($data['start_travel_date']) && !isset($error['start_travel_date'])) {
$mysql .= " AND DATE(p.receipt_datetime) >= '" . pg_escape_string($data['start_travel_date']) . "'";
}
if (!empty($data['end_travel_date']) && !isset($error['end_travel_date'])) {
$mysql .= " AND DATE(p.receipt_datetime) <= '" . pg_escape_string($data['end_travel_date']) . "'";
}
if (!empty($data['cost_start']) && !isset($error['cost_start'])) {
$mysql .= " AND p.amount >= " . pg_escape_string($data['cost_start']) . "";
}
if (!empty($data['cost_end']) && !isset($error['cost_end'])) {
$mysql .= " AND p.amount <= " . pg_escape_string($data['cost_end']) . "";
}
$mysql .= " ORDER BY id DESC";
list($data['pages'], $data['page'], $data['total_pages']) = $this->paginateQuery($mysql, $limit, $offset);
$mysql = "SELECT (ROW_NUMBER () OVER (ORDER BY receipt_datetime DESC)) as No, * FROM (${mysql}) AS foo ORDER BY receipt_datetime DESC LIMIT ${limit} OFFSET ${offset}";
error_log($mysql);
$query = $this->read_replica->query($mysql);
$this->table->set_heading(
'No.', array('data' => 'ID', 'style' => 'width:10px'), 'Email ID', 'Merchant', 'Receipt Date', 'Amount', 'Created', "Last Data Parse"
);
$data['member_parsedemail'] = $this->table->generate($query);
$config = [];
$config['full_tag_open'] = "";
$config['num_tag_open'] = '';
$config['num_tag_close'] = ' ';
$config['cur_tag_open'] = "";
$config['cur_tag_close'] = " ";
$config['next_tagl_close'] = " ";
$config['prev_tag_open'] = "";
$config['prev_tagl_close'] = " ";
$config['first_tag_open'] = "";
$config['first_tagl_close'] = " ";
$config['last_tag_open'] = "";
$config['last_tagl_close'] = " ";
$data['pagination_config'] = $config;
// TRACKED
$offset = $member_tracked_offset;
$select_query = 'SELECT
t.id,
t.created,
t.subject,
t.hash,
t.date_parsed,
t.message_date,
t.message_from,
t.message,
t.parsed_status,
p.trackedemail_item_id,
p.dup_id';
$count_query = "SELECT count(*) AS all_count";
$from_query = " FROM trackedemail_item t LEFT JOIN parsedemail_item p ON t.id = p.trackedemail_item_id
WHERE t.member_id=$member_id";
$where_query = ' AND p.dup_id IS NULL';
// subject
if (!empty($data['subject']) && !isset($error['subject'])) {
$from_query .= " AND lower(t.subject) like '%" . pg_escape_string(strtolower($data['subject'])) . "%'";
}
// sender
if (!empty($data['message_from']) && !isset($error['message_from'])) {
$from_query .= " AND lower(t.message_from) like '%" . pg_escape_string(strtolower($data['message_from'])) . "%'";
}
if (isset($data['export']) && $data['export'] === 'ZIP') {
$total_record = (int) $this->read_replica->query($count_query . $from_query . $where_query)->result_array()[0]['all_count'];
if (!$total_record) {
header('Content-Type: application/json');
http_response_code(404);
return;
}
$start = 0;
$limit_export = 1000;
$query = "${select_query} ${from_query} ${where_query} ORDER BY created LIMIT ${limit_export} OFFSET ${start}";
$filePath = $_SERVER['DOCUMENT_ROOT']
. DIRECTORY_SEPARATOR
. self::TEMP_DIRECTORY_DOWNLOAD;
$filename = 'download-' . date("Y-m-d-H-i-s") . ".zip";
do {
if (ob_get_level()) {
ob_end_clean();
}
$data = $this->read_replica->query($query);
$this->export($data, $filePath, $filename);
$start += $limit;
} while ($start + $limit <= $total_record);
$this->send_file($filePath, $filename);
return;
}
$mysql = $select_query . $from_query . $where_query;
list($data['tpages'], $data['tpage'], $data['ttotal_pages']) = $this->paginateQuery($mysql, $limit, $offset);
$mysql = "SELECT (ROW_NUMBER () OVER (ORDER BY created DESC)) as No, foo.id, foo.created, foo.message_date, foo.subject FROM (${mysql}) AS foo ORDER BY created DESC LIMIT ${limit} OFFSET ${offset}";
error_log($mysql);
$query = $this->read_replica->query($mysql);
$this->table->set_heading('No.', array('data' => 'ID', 'style' => 'width:10px'), 'Created', array('data' => 'Send Date', 'style' => 'width:150px'), 'Subject');
$data['member_tracked'] = $this->table->generate($query);
return $data;
}
protected function renderMemberPage($page_name, $data) {
$this->load->view('admin/view_admin_header', $data);
$this->load->view('member/' . $page_name, $data);
$this->load->view('admin/view_admin_footer', $data);
}
// Tracked Email Report
private function parse_trip_report_data($raw_data) {
$total_amount = 0;
if (!isset($raw_data['report']) || count($raw_data['report']) <= 0) {
return [];
}
foreach ($raw_data['report'] as $trip_category) {
$total_amount += floatval($trip_category['spent']) ?? 0;
}
$raw_data['total_amount'] = $total_amount;
return $raw_data;
}
}