dev-chiefworks
197 lines
8.0 KiB
PHP
197 lines
8.0 KiB
PHP
<?php
|
|
|
|
if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Point_model extends CI_Model {
|
|
|
|
public function __constructor() {
|
|
$this->read_replica = $this->load->database('savvy_replica', TRUE);
|
|
parrent::__constructor();
|
|
}
|
|
|
|
public function getSystemicPointsSummary()
|
|
{
|
|
$query_arr = [
|
|
'last_24_hrs' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()",
|
|
'last_7_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-6 days')) . "' AND '" . date('Y-m-d') . "'",
|
|
'last_14_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-13 days')) . "' AND '" . date('Y-m-d') . "'",
|
|
'last_30_days' => "AND m.id > 0 AND m.email != '' AND mp.added BETWEEN '" . date('Y-m-d', strtotime('-29 days')) . "' AND '" . date('Y-m-d') . "'",
|
|
'all_time' => "",
|
|
];
|
|
|
|
$countries = [];
|
|
|
|
foreach ($query_arr as $key => $value) {
|
|
$sql = "SELECT COUNT(*) AS total_assigned_times,
|
|
CASE WHEN m.country IS NULL THEN 'Unknown' ELSE m.country END AS location
|
|
FROM members_points AS mp
|
|
LEFT JOIN members AS m ON m.id = mp.member_id
|
|
WHERE mp.points > 0 $value
|
|
GROUP BY CASE WHEN m.country IS NULL THEN 'Unknown' ELSE m.country END
|
|
ORDER BY total_assigned_times DESC";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
$countries[$key] = $query->result_array();
|
|
}
|
|
|
|
foreach ($countries['all_time'] as &$country) {
|
|
foreach ($countries['last_30_days'] as $tempCountry) {
|
|
if ($tempCountry['location'] === $country['location']) {
|
|
$country['last_30_days'] = $tempCountry['total_assigned_times'];
|
|
break;
|
|
}
|
|
}
|
|
foreach ($countries['last_14_days'] as $tempCountry) {
|
|
if ($tempCountry['location'] === $country['location']) {
|
|
$country['last_14_days'] = $tempCountry['total_assigned_times'];
|
|
break;
|
|
}
|
|
}
|
|
foreach ($countries['last_7_days'] as $tempCountry) {
|
|
if ($tempCountry['location'] === $country['location']) {
|
|
$country['last_7_days'] = $tempCountry['total_assigned_times'];
|
|
break;
|
|
}
|
|
}
|
|
foreach ($countries['last_24_hrs'] as $tempCountry) {
|
|
if ($tempCountry['location'] === $country['location']) {
|
|
$country['last_24_hrs'] = $tempCountry['total_assigned_times'];
|
|
break;
|
|
}
|
|
}
|
|
|
|
$country['last_30_days'] = $country['last_30_days'] ?? 0;
|
|
$country['last_14_days'] = $country['last_14_days'] ?? 0;
|
|
$country['last_7_days'] = $country['last_7_days'] ?? 0;
|
|
$country['last_24_hrs'] = $country['last_24_hrs'] ?? 0;
|
|
}
|
|
|
|
return $countries['all_time'];
|
|
}
|
|
|
|
public function getSystemicPointsDatatables($postData, $mode = 'search')
|
|
{
|
|
## Save param for CSV
|
|
$this->load->library('session');
|
|
$this->session->set_userdata("SPR_PARAM", $postData);
|
|
## Read value
|
|
; $from = DateTime::createFromFormat('m/d/Y', '1/1/1970');
|
|
$to = (new DateTime());
|
|
$date_range = $postData['date_range'] ?? '';
|
|
if (!empty($date_range)) {
|
|
$date_range_part = array_map('trim', explode('-', $date_range));
|
|
$from = DateTime::createFromFormat('m/d/Y', $date_range_part[0]);
|
|
$to = DateTime::createFromFormat('m/d/Y', $date_range_part[1]);
|
|
}
|
|
$draw = $postData['draw'] ?? 0;
|
|
$start = $postData['start'];
|
|
$rowperpage = $postData['length']; // Rows display per page
|
|
$columnIndex = $postData['order'][0]['column'] ?? 0; // Column index
|
|
$columnName = $postData['columns'][$columnIndex]['data'] ?? ''; // Column name
|
|
$columnSortOrder = $postData['order'][0]['dir']; // asc or desc
|
|
|
|
$searchValue = $postData['email'] ?? ''; // Search value
|
|
$pointKey = $postData['point_key'] ?? ''; // Point key
|
|
$pointValue = $postData['point_value'] ?? ''; // Point key
|
|
|
|
$where = " m.id>0 AND m.email!='' AND mp.points > 0";
|
|
$where .= " AND date(m.added) >='" . $from->format('Y-m-d') . "' and date(m.added)<='" . $to->format('Y-m-d') . "' ";
|
|
|
|
if (!empty($postData['country_filter'])) {
|
|
if (strtolower($postData['country_filter']) === 'unknown') {
|
|
$where .= " AND (m.country = '' or m.country IS NULL) ";
|
|
} else {
|
|
$where .= " AND m.country = '" . pg_escape_string($postData['country_filter']) . "' ";
|
|
}
|
|
}
|
|
|
|
if (!empty($searchValue)) {
|
|
$searchStr = pg_escape_string($searchValue);
|
|
$where .= " AND m.email ILIKE '%" . $searchStr . "%' ";
|
|
}
|
|
|
|
if (!empty($pointKey)) {
|
|
$searchStr = pg_escape_string($pointKey);
|
|
$where .= " AND mp.point_key = '" . $searchStr . "' ";
|
|
}
|
|
|
|
if (!empty($pointValue)) {
|
|
$searchStr = pg_escape_string($pointValue);
|
|
$where .= " AND mp.points = '" . $searchStr . "' ";
|
|
}
|
|
|
|
$map_order_by = [
|
|
'allocation_date' => 'mp.added',
|
|
'sign_up_date' => 'm.added',
|
|
'country' => 'm.country',
|
|
'member_id' => 'mp.member_id',
|
|
];
|
|
|
|
if (isset($map_order_by[$columnName])) {
|
|
$order_by = ' ORDER BY ' . $map_order_by[$columnName] . ' ' . $columnSortOrder;
|
|
} else {
|
|
$order_by = ' ORDER BY ' . $map_order_by['sign_up_date'] . ' DESC';
|
|
}
|
|
|
|
$limit = $mode === 'search' ? " LIMIT $rowperpage OFFSET $start" : '';
|
|
|
|
$mysql = "SELECT
|
|
to_char(mp.added, 'MM/DD/YYYY HH24:MI:SS') as allocation_date,
|
|
to_char(m.added, 'MM/DD/YYYY HH24:MI:SS') as sign_up_date,
|
|
CASE WHEN m.country IS NOT NULL THEN m.country ELSE 'Unknown' END as country,
|
|
mp.member_id, m.email, mp.point_key, ps.name, mp.points
|
|
FROM members_points mp
|
|
INNER JOIN members m on m.id = mp.member_id INNER JOIN points_settings ps on ps.point_key = mp.point_key
|
|
WHERE $where
|
|
$order_by $limit";
|
|
# Response Search or CSV
|
|
if ($mode === 'export_csv') {
|
|
$report = $this->db->query($mysql);
|
|
$this->exportToCSV($report, 'systemic-points-report-' . date("Y-m-d") . '.csv');
|
|
exit;
|
|
}
|
|
$report = $this->db->query($mysql)->result_array();
|
|
$mysql_count = "SELECT COUNT(*)
|
|
FROM members_points mp
|
|
INNER JOIN members m on m.id = mp.member_id INNER JOIN points_settings ps on ps.point_key = mp.point_key
|
|
WHERE $where;";
|
|
$totalRecords = $this->db->query($mysql_count)->row_array()['count'];
|
|
$totalRecordwithFilter = $totalRecords;
|
|
$response = array(
|
|
"draw" => intval($draw),
|
|
"iTotalRecords" => $totalRecords,
|
|
"iTotalDisplayRecords" => $totalRecordwithFilter,
|
|
"aaData" => $report
|
|
);
|
|
return $response;
|
|
}
|
|
|
|
public function exportToCSV($data, $file_name)
|
|
{
|
|
set_time_limit(0);
|
|
header('Content-Type: application/vnd.ms-excel');
|
|
header('Content-Disposition: attachment;filename='.$file_name);
|
|
header('Cache-Control: max-age=0');
|
|
if (ob_get_contents())
|
|
ob_end_clean();
|
|
$fp = fopen('php://output', 'w');
|
|
$i = 0;
|
|
while ($row = $data->unbuffered_row()) {
|
|
// header
|
|
if ($i == 0) {
|
|
fputcsv($fp, array_keys((array)$row));
|
|
}
|
|
// records
|
|
fputcsv($fp, (array)$row);
|
|
$i++;
|
|
}
|
|
fclose($fp);
|
|
exit;
|
|
}
|
|
|
|
}
|
|
|
|
?>
|