File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Data_archive.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Data_archive extends Action_controller{
public function __construct(){
parent::__construct('data_archive');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
// Load employee and shift data for archive filters
$employees_query = 'SELECT employee_code, emp_name FROM cw_employees WHERE trans_status = 1 ORDER BY emp_name ASC';
$employees_result = $this->db->query("CALL sp_a_run ('SELECT','$employees_query')");
$data['employees'] = $employees_result->result();
$employees_result->next_result();
$shifts_query = 'SELECT prime_shift_master_id, shift_name FROM cw_shift_master WHERE trans_status = 1 ORDER BY shift_name ASC';
$shifts_result = $this->db->query("CALL sp_a_run ('SELECT','$shifts_query')");
$data['shifts'] = $shifts_result->result();
$shifts_result->next_result();
$data['encKey'] = $this->generateKey();
$data['module_sts'] = (int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$this->load->view("$this->control_name/manage",$data);
}
// ---------------------------------------------------------------
// UNIVERSAL ARCHIVE FUNCTION - HANDLES ALL ARCHIVE TYPES
// ---------------------------------------------------------------
public function archive_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....'));
exit(0);
}
$draw = intval($this->input->post('draw'));
$start = intval($this->input->post('start'));
$length = intval($this->input->post('length'));
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$archive_type = $this->input->post('archive_type') ?? 'time_log';
// Debug logging
error_log("Archive type: " . $archive_type);
error_log("Received from_date: " . $from_date);
error_log("Received to_date: " . $to_date);
// Mandatory date range check
if (empty($from_date) || empty($to_date)) {
$this->_empty_archive_response($draw, 'Please select From Date and To Date.');
return;
}
// Max 3 month range check
$diff_months = $this->_month_diff($from_date, $to_date);
if ($diff_months > 3) {
$this->_empty_archive_response($draw, 'Maximum allowed date range is 3 months.');
return;
}
$order_dir = strtoupper($this->input->post('order')[0]['dir'] ?? 'DESC');
$order_dir = in_array($order_dir, ['ASC', 'DESC']) ? $order_dir : 'DESC';
// Set filters based on archive type
$filters = [
'from_date' => $from_date,
'to_date' => $to_date,
'employee_code' => $this->input->post('employee_code'),
'search' => $this->input->post('search')['value'] ?? '',
];
// Configure based on archive type
switch ($archive_type) {
case 'time_log':
$filters['record_type'] = $this->input->post('record_type');
$table_prefix = 'cw_time_log_archive';
$date_field = 'log_date';
$response_method = '_json_time_log_response';
break;
case 'shift_import':
$filters['shift_id'] = $this->input->post('shift_id');
$table_prefix = 'cw_shift_import_archive';
$date_field = 'shift_date';
$response_method = '_json_shift_import_response';
break;
default:
$this->_empty_archive_response($draw, 'Invalid archive type.');
return;
}
// Get data using universal methods
$filtered = $this->get_archive_count($filters, $table_prefix);
$records = $this->get_archive_data($filters, $table_prefix, $date_field, $length, $start, $order_dir);
// Send response using appropriate method
try {
$this->$response_method($draw, $filtered, $records);
} catch (Exception $e) {
error_log("Response method error: " . $e->getMessage());
$this->_empty_archive_response($draw, 'Server error occurred.');
}
}
// Universal count method
private function get_archive_count($filters = [], $table_prefix = ''){
$tables = $this->_get_yearly_tables($filters['from_date'], $filters['to_date'], $table_prefix);
if (empty($tables)) return 0;
$total = 0;
foreach ($tables as $table) {
$query = $this->_build_count_query($table, $filters);
$result = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$count = $result->result();
$result->next_result();
$total += $count[0]->count;
}
return $total;
}
// Universal data method
private function get_archive_data($filters = [], $table_prefix = '', $date_field = '', $limit = 25, $offset = 0, $order_dir = 'DESC'){
$tables = $this->_get_yearly_tables($filters['from_date'], $filters['to_date'], $table_prefix);
if (empty($tables)) return [];
$all_results = [];
foreach ($tables as $table) {
$query = $this->_build_data_query($table, $filters, $date_field);
$query .= ' ORDER BY ' . $date_field . ' ' . $order_dir;
$result = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$records = $result->result();
$result->next_result();
$all_results = array_merge($all_results, $records);
}
// Sort merged multi-year results
usort($all_results, function ($a, $b) use ($date_field, $order_dir) {
$diff = strtotime($a->$date_field) - strtotime($b->$date_field);
return $order_dir === 'DESC' ? -$diff : $diff;
});
return array_slice($all_results, $offset, $limit);
}
// Build count query based on archive type
private function _build_count_query($table, $filters = []){
$archive_type = $this->input->post('archive_type') ?? 'time_log';
switch ($archive_type) {
case 'time_log':
$query = 'SELECT COUNT(*) as count FROM ' . $table . ' tla
LEFT JOIN cw_employees e ON e.employee_code = tla.user_id
WHERE tla.trans_status = 1';
// Apply filters
if (!empty($filters['from_date'])) {
$query .= ' AND DATE(tla.log_date) >= "' . $filters['from_date'] . '"';
}
if (!empty($filters['to_date'])) {
$query .= ' AND DATE(tla.log_date) <= "' . $filters['to_date'] . '"';
}
if (!empty($filters['employee_code'])) {
$query .= ' AND tla.user_id = "' . $filters['employee_code'] . '"';
}
if (!empty($filters['record_type'])) {
$query .= ' AND tla.record_type = "' . $filters['record_type'] . '"';
}
if (!empty($filters['search'])) {
$search = $this->db->escape_like_str($filters['search']);
$query .= ' AND (e.emp_name LIKE "%' . $search . '%" OR tla.user_id LIKE "%' . $search . '%" OR tla.device_id LIKE "%' . $search . '%")';
}
break;
case 'shift_import':
$query = 'SELECT COUNT(*) as count FROM ' . $table . ' sia
LEFT JOIN cw_shift_master sm ON sm.prime_shift_master_id = sia.shift_name
WHERE sia.trans_status = 1';
// Apply filters
if (!empty($filters['from_date'])) {
$query .= ' AND sia.shift_date >= "' . $filters['from_date'] . '"';
}
if (!empty($filters['to_date'])) {
$query .= ' AND sia.shift_date <= "' . $filters['to_date'] . '"';
}
if (!empty($filters['employee_code'])) {
$query .= ' AND sia.employee_code = "' . $filters['employee_code'] . '"';
}
if (!empty($filters['shift_id'])) {
$query .= ' AND sia.shift_name = "' . $filters['shift_id'] . '"';
}
if (!empty($filters['search'])) {
$search = $this->db->escape_like_str($filters['search']);
$query .= ' AND (sia.employee_name LIKE "%' . $search . '%" OR sia.employee_code LIKE "%' . $search . '%" OR sm.shift_name LIKE "%' . $search . '%")';
}
break;
default:
$query = 'SELECT COUNT(*) as count FROM ' . $table . ' WHERE 1=0';
}
return $query;
}
// Build data query based on archive type
private function _build_data_query($table, $filters = [], $date_field = ''){
$archive_type = $this->input->post('archive_type') ?? 'time_log';
// Debug table existence
$exists = $this->db->query("SHOW TABLES LIKE '" . $table . "'")->num_rows();
error_log("Table {$table} exists: " . ($exists > 0 ? 'YES' : 'NO'));
switch ($archive_type) {
case 'time_log':
$query = 'SELECT tla.prime_time_log_id, tla.device_id, tla.log_date, tla.record_type,
tla.user_id, tla.location, tla.in_out_img, tla.archived_at,
e.emp_name, e.employee_code
FROM ' . $table . ' tla
LEFT JOIN cw_employees e ON e.employee_code = tla.user_id
WHERE tla.trans_status = 1';
// Apply filters (same as count query)
if (!empty($filters['from_date'])) {
$query .= ' AND DATE(tla.log_date) >= "' . $filters['from_date'] . '"';
}
if (!empty($filters['to_date'])) {
$query .= ' AND DATE(tla.log_date) <= "' . $filters['to_date'] . '"';
}
if (!empty($filters['employee_code'])) {
$query .= ' AND tla.user_id = "' . $filters['employee_code'] . '"';
}
if (!empty($filters['record_type'])) {
$query .= ' AND tla.record_type = "' . $filters['record_type'] . '"';
}
if (!empty($filters['search'])) {
$search = $this->db->escape_like_str($filters['search']);
$query .= ' AND (e.emp_name LIKE "%' . $search . '%" OR tla.user_id LIKE "%' . $search . '%" OR tla.device_id LIKE "%' . $search . '%")';
}
break;
case 'shift_import':
error_log("Building shift import query for table: {$table}");
error_log("Filters: " . json_encode($filters));
$query = 'SELECT sia.prime_shift_import_id, sia.employee_code, sia.employee_name,
sia.from_date, sia.to_date, sia.shift_date, sia.archived_at,
sm.shift_name
FROM ' . $table . ' sia
LEFT JOIN cw_shift_master sm ON sm.prime_shift_master_id = sia.shift_name
WHERE sia.trans_status = 1';
// Apply filters (same as count query)
if (!empty($filters['from_date'])) {
$query .= ' AND sia.shift_date >= "' . $filters['from_date'] . '"';
}
if (!empty($filters['to_date'])) {
$query .= ' AND sia.shift_date <= "' . $filters['to_date'] . '"';
}
if (!empty($filters['employee_code'])) {
$query .= ' AND sia.employee_code = "' . $filters['employee_code'] . '"';
}
if (!empty($filters['shift_id'])) {
$query .= ' AND sia.shift_name = "' . $filters['shift_id'] . '"';
}
if (!empty($filters['search'])) {
$search = $this->db->escape_like_str($filters['search']);
$query .= ' AND (sia.employee_name LIKE "%' . $search . '%" OR sia.employee_code LIKE "%' . $search . '%" OR sm.shift_name LIKE "%' . $search . '%")';
}
break;
default:
$query = 'SELECT * FROM ' . $table . ' WHERE 1=0';
}
error_log("Final query for {$archive_type}: " . $query);
return $query;
}
// ---------------------------------------------------------------
// HELPER METHODS
// ---------------------------------------------------------------
private function _get_yearly_tables($from_date, $to_date, $base_table){
$from_year = (int) date('Y', strtotime($from_date));
$to_year = (int) date('Y', strtotime($to_date));
$tables = [];
for ($y = $from_year; $y <= $to_year; $y++) {
$table = $base_table . '_' . $y;
$exists = $this->db->query("SHOW TABLES LIKE '" . $table . "'")->num_rows();
if ($exists > 0) {
$tables[] = $table;
}
}
return $tables;
}
private function _month_diff($from, $to){
$d1 = new DateTime($from);
$d2 = new DateTime($to);
$interval = $d1->diff($d2);
return ($interval->y * 12) + $interval->m + ($interval->d > 0 ? 1 : 0);
}
private function _empty_archive_response($draw, $message = ''){
echo json_encode([
'draw' => $draw,
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => [],
'error' => $message,
]);
}
private function _empty_time_log_response($draw, $message = ''){
echo json_encode([
'draw' => $draw,
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => [],
'error' => $message,
]);
}
private function _empty_shift_import_response($draw, $message = ''){
echo json_encode([
'draw' => $draw,
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => [],
'error' => $message,
]);
}
private function _json_time_log_response($draw, $filtered, $records){
$rows = [];
foreach ($records as $row) {
$badge = $row->record_type === 'in'
? '<span class="label label-success">IN</span>'
: '<span class="label label-danger">OUT</span>';
$img = !empty($row->in_out_img)
? '<a href="' . base_url($row->in_out_img) . '" target="_blank">
<img src="' . base_url($row->in_out_img) . '" width="35" height="35"
class="img-circle" style="object-fit:cover;">
</a>'
: '<span class="text-muted">-</span>';
$rows[] = [
date('d-m-Y H:i:s', strtotime($row->log_date)),
htmlspecialchars($row->user_id ?? '-'),
htmlspecialchars($row->emp_name ?? '-'),
$badge,
htmlspecialchars($row->device_id ?? '-'),
htmlspecialchars(mb_substr($row->location ?? '-', 0, 60)) . (strlen($row->location) > 60 ? '...' : ''),
$img,
date('d-m-Y H:i', strtotime($row->archived_at)),
];
}
echo json_encode([
'draw' => $draw,
'recordsTotal' => $filtered,
'recordsFiltered' => $filtered,
'data' => $rows,
]);
}
private function _json_shift_import_response($draw, $filtered, $records){
$rows = [];
foreach ($records as $row) {
$rows[] = [
date('d-m-Y', strtotime($row->shift_date)),
htmlspecialchars($row->employee_code ?? '-'),
htmlspecialchars($row->employee_name ?? '-'),
htmlspecialchars($row->shift_name ?? '-'),
date('d-m-Y', strtotime($row->from_date)),
date('d-m-Y', strtotime($row->to_date)),
date('d-m-Y H:i', strtotime($row->archived_at)),
];
}
echo json_encode([
'draw' => $draw,
'recordsTotal' => $filtered,
'recordsFiltered' => $filtered,
'data' => $rows,
]);
}
// ---------------------------------------------------------------
// REFERENCE DATA METHODS
// ---------------------------------------------------------------
public function get_employee_list(){
$query = 'SELECT employee_code, emp_name FROM cw_employees WHERE trans_status = 1 ORDER BY emp_name ASC';
$result = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$employees = $result->result();
$result->next_result();
echo json_encode(['success' => true, 'data' => $employees]);
}
public function get_shift_list(){
$query = 'SELECT prime_shift_master_id, shift_name FROM cw_shift_master WHERE trans_status = 1 ORDER BY shift_name ASC';
$result = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$shifts = $result->result();
$result->next_result();
echo json_encode(['success' => true, 'data' => $shifts]);
}
}
?>