Database Management

NexaModel menyediakan helper methods yang powerful untuk menangani hari kerja (working days) dan rentang tanggal:

NexaModel · Storage() · NexaDb

Alur

$this->Storage('news')->where()->first();

Show Tables

PHP

// Dari controller: butuh instance NexaModel (nama tabel di Storage hanya untuk instance)
$tables = $this->Storage('users')->showTables();

$this->Storage('users')->showTablesFormatted(true);

Table Information

PHP

$tableInfo = $this->Storage('users')->getTablesInfo();

$columns = $this->Storage('users')->getTableColumns('users');

$exists = $this->Storage('users')->tableExists('users');

Database Information

PHP

$dbInfo = $this->getDatabaseInfo();

// Result:
// [
//     'database_name' => 'your_database',
//     'total_tables' => 15,
//     'total_size' => '50.5 MB',
//     'charset' => 'utf8mb4',
//     'collation' => 'utf8mb4_unicode_ci'
// ]

// Display formatted
$this->showDatabaseInfoFormatted(true);

Database Exploration

PHP

// Explore database structure
$this->exploreDatabase(true, true); // dengan <pre> dan show columns

NexaModel menyediakan helper methods yang powerful untuk menangani hari kerja (working days) dan rentang tanggal:

Basic Working Days Operations

Get Working Days Range

PHP

// Mendapatkan rentang 5 hari kerja dari tanggal tertentu
$range = $this->getWorkingDaysRange('2024-01-15', 5);
// Result: ['start' => '2024-01-15', 'end' => '2024-01-19'] (skip weekends)

// Default 5 hari kerja dari hari ini
$range = $this->getWorkingDaysRange(date('Y-m-d'));

Get Current Work Week

PHP

// Mendapatkan rentang minggu kerja saat ini (Senin-Jumat)
$workWeek = $this->getCurrentWorkWeek();
// Result: ['start' => '2024-01-15', 'end' => '2024-01-19'] (Monday to Friday)

// Berdasarkan tanggal referensi tertentu
$workWeek = $this->getCurrentWorkWeek('2024-01-17');

Check Working Day

PHP

// Cek apakah tanggal adalah hari kerja
$isWorkingDay = $this->isWorkingDay('2024-01-15'); // true (Monday)
$isWeekend = $this->isWorkingDay('2024-01-14'); // false (Sunday)

Query Filtering with Working Days

Filter by Working Days Range

PHP

// Filter query berdasarkan rentang hari kerja
$tasks = $this->Storage('tasks')
    ->whereWorkingDays('due_date', '2024-01-15', 5) // 5 working days from Jan 15
    ->get();

// Filter hanya untuk hari itu saja (single date)
$todayTasks = $this->Storage('tasks')
    ->whereWorkingDays('due_date', '2024-01-15', 0) // Only that specific date
    ->get();

// Default dari hari ini dengan 5 hari kerja
$weekTasks = $this->Storage('tasks')
    ->whereWorkingDays('created_at') // Default 5 days
    ->get();

Filter by Current Work Week

PHP

// Filter berdasarkan minggu kerja saat ini
$weeklyTasks = $this->Storage('tasks')
    ->whereCurrentWorkWeek('created_at')
    ->get();

// Berdasarkan minggu kerja dari tanggal referensi
$specificWeekTasks = $this->Storage('tasks')
    ->whereCurrentWorkWeek('due_date', '2024-01-17')
    ->get();

Working Days List and Count

Get Working Days List

PHP

// Mendapatkan daftar hari kerja antara dua tanggal
$workingDays = $this->getWorkingDaysList('2024-01-15', '2024-01-25');
// Result: ['2024-01-15', '2024-01-16', '2024-01-17', '2024-01-18', '2024-01-19', '2024-01-22', '2024-01-23', '2024-01-24', '2024-01-25']

Count Working Days

PHP

// Menghitung jumlah hari kerja antara dua tanggal
$count = $this->countWorkingDays('2024-01-15', '2024-01-25');
// Result: 9 (excluding weekends)

Practical Examples

Project Tasks Management

PHP

<?php
namespace App\Models;

use App\System\NexaModel;

class ProjectTask extends NexaModel
{
    protected $table = 'project_tasks';

    /**
     * Get tasks for current work week
     */
    public function getCurrentWeekTasks($projectId)
    {
        return $this->Storage($this->table)
            ->where('project_id', $projectId)
            ->whereCurrentWorkWeek('due_date')
            ->orderBy('due_date', 'ASC')
            ->get();
    }

    /**
     * Get tasks for next 5 working days
     */
    public function getUpcomingTasks($projectId)
    {
        return $this->Storage($this->table)
            ->where('project_id', $projectId)
            ->whereWorkingDays('due_date', date('Y-m-d'), 5)
            ->where('status', '!=', 'completed')
            ->get();
    }

    /**
     * Get overdue working day tasks
     */
    public function getOverdueTasks($projectId)
    {
        $today = date('Y-m-d');

        return $this->Storage($this->table)
            ->where('project_id', $projectId)
            ->where('due_date', '<', $today)
            ->where('status', '!=', 'completed')
            ->get();
    }

    /**
     * Schedule task for next working day
     */
    public function scheduleForNextWorkingDay($taskData)
    {
        $tomorrow = date('Y-m-d', strtotime('+1 day'));

        // If tomorrow is weekend, find next Monday
        while (!$this->isWorkingDay($tomorrow)) {
            $tomorrow = date('Y-m-d', strtotime($tomorrow . ' +1 day'));
        }

        $taskData['due_date'] = $tomorrow;

        return $this->Storage($this->table)->insert($taskData);
    }
}

Report Generation Example

$this->Storage() disediakan oleh NexaController. Method seperti activeTasksLogs, avatar, validatorTasksLogs, dan processTaskResults tidak ada di base class — tambahkan sendiri di controller/trait aplikasi Anda.

PHP

<?php
namespace App\Controllers\Admin\Planning;

use App\System\NexaController;

class Report extends NexaController
{
         /**
      * Get tasks for specific date or working days range
      */
     public function Tasks($id, $tanggal = null, $workingDays = 0)
     {
         // Set default date to today if not provided
         if ($tanggal === null) {
             $tanggal = date('Y-m-d');
         }

         // Get tasks from database
         // $workingDays = 0: hanya tanggal tersebut
         // $workingDays = 1+: rentang hari kerja
         $result = $this->Storage('plg_tasks')
             ->select([
                 'id',
                 'assigned_to AS userid',
                 'project_id',
                 'title',
                 'description',
                 'date',
                 'status'
             ])
             ->where('project_id', $id)
             ->whereWorkingDays('date', $tanggal, $workingDays)
             ->orderBy("id", "DESC")
             ->get();

        $result3 = array();

        // Process each task and add user information
        foreach ($result as $key => $value) {
            if ($value['status'] !== 'todo') {
                $logID = self::activeTasksLogs($value['id'], $value['status']);
                $result3[$key]['id'] = $value['id'];
                $result3[$key]['name'] = $this->avatar($value['userid'], 'nama');
                $result3[$key]['date'] = $value['date'];
                $result3[$key]['data'] = $logID;
                $result3[$key]['validator'] = self::validatorTasksLogs($logID);
            }
        }

        return $result3 ?? [];
    }

    /**
     * Get tasks for current work week (Monday to Friday)
     */
    public function TasksCurrentWeek($id, $tanggal = null)
    {
        if ($tanggal === null) {
            $tanggal = date('Y-m-d');
        }

        $result = $this->Storage('plg_tasks')
            ->select([
                'id',
                'assigned_to AS userid',
                'project_id',
                'title',
                'description',
                'date',
                'status'
            ])
            ->where('project_id', $id)
            ->whereCurrentWorkWeek('date', $tanggal)
            ->orderBy("id", "DESC")
            ->get();

        // Process results...
        return $this->processTaskResults($result);
    }
}

Employee Attendance System

PHP

<?php
namespace App\Models;

use App\System\NexaModel;

class Attendance extends NexaModel
{
    protected $table = 'attendance';

    /**
     * Get attendance for current work week
     */
    public function getWeeklyAttendance($employeeId)
    {
        return $this->Storage($this->table)
            ->where('employee_id', $employeeId)
            ->whereCurrentWorkWeek('attendance_date')
            ->orderBy('attendance_date', 'ASC')
            ->get();
    }

    /**
     * Calculate working days attendance percentage
     */
    public function getAttendancePercentage($employeeId, $startDate, $endDate)
    0;

    /**
     * Get missing attendance for working days
     */
    public function getMissingAttendance($employeeId, $startDate, $endDate)
    {
        $workingDays = $this->getWorkingDaysList($startDate, $endDate);

        $recordedDates = $this->Storage($this->table)
            ->where('employee_id', $employeeId)
            ->whereBetween('attendance_date', [$startDate, $endDate])
            ->pluck('attendance_date');

        return array_diff($workingDays, $recordedDates);
    }
}

Advanced Working Days Queries

Complex Reporting

PHP

// Get tasks grouped by working days
$tasksByWorkingDays = $this->Storage('tasks')
    ->whereWorkingDays('created_at', '2024-01-01', 10)
    ->select([
        'DATE(created_at) as task_date',
        'COUNT(*) as task_count',
        'status'
    ])
    ->groupBy(['DATE(created_at)', 'status'])
    ->orderBy('task_date', 'ASC')
    ->get();

// Get productivity metrics for working days
$productivity = $this->Storage('tasks')
    ->whereCurrentWorkWeek('completed_at')
    ->where('status', 'completed')
    ->select([
        'assigned_to',
        'COUNT(*) as completed_tasks',
        'AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)) as avg_completion_hours'
    ])
    ->groupBy('assigned_to')
    ->get();

Date Range Analysis

PHP

// Analyze task distribution across working days
$analysis = $this->Storage('tasks')
    ->select([
        'DAYNAME(due_date) as day_name',
        'COUNT(*) as task_count',
        'AVG(priority_score) as avg_priority'
    ])
    ->whereWorkingDays('due_date', date('Y-m-d'), 15)
    ->groupBy('DAYNAME(due_date)')
    ->orderBy('FIELD(DAYNAME(due_date), "Monday", "Tuesday", "Wednesday", "Thursday", "Friday")')
    ->get();

Migration Usage Examples

Planning System Migration

PHP

// Update existing queries to use working days
// Before:
$tasks = $this->Storage('plg_tasks')
    ->where('project_id', $id)
    ->where('date', $tanggal)
    ->get();

// After:
$tasks = $this->Storage('plg_tasks')
    ->where('project_id', $id)
    ->whereWorkingDays('date', $tanggal, 5)
    ->get();

Bulk Operations for Working Days

PHP

// Mark all tasks for current work week as reviewed
$this->Storage('tasks')
    ->whereCurrentWorkWeek('created_at')
    ->where('status', 'pending')
    ->update([
        'status' => 'reviewed',
        'reviewed_at' => date('Y-m-d H:i:s')
    ]);

// Get statistics for working days
$stats = $this->Storage('tasks')
    ->whereWorkingDays('due_date')
    ->countByConditions('status', ['pending', 'in_progress', 'completed']);

Best Practices for Working Days

1. Consistent Date Handling

PHP

// Always use Y-m-d format for date parameters
$date = date('Y-m-d'); // Good
$date = '2024-01-15';  // Good
$date = '15/01/2024';  // Avoid - may cause issues

2. Performance Optimization

PHP

// Use indexes on date columns for better performance
// CREATE INDEX idx_tasks_date ON tasks(date);

// Prefer working days methods over manual date calculations
$tasks = $this->Storage('tasks')
    ->whereWorkingDays('date', $startDate, 5) // Good

// Instead of:
// ->where('date', '>=', $startDate)
// ->where('date', '<=', $endDate)
// ->whereNotIn('DAYOFWEEK(date)', [1, 7]) // Manual weekend exclusion

3. Error Handling

PHP

public function getWorkingDayTasks($date, $days = 5)
{
    try {
        // Validate date format
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            throw new \InvalidArgumentException('Invalid date format. Use Y-m-d');
        }

        return $this->Storage('tasks')
            ->whereWorkingDays('due_date', $date, $days)
            ->get();

    } catch (\Exception $e) {
        error_log('Working days query failed: ' . $e->getMessage());
        return [];
    }
}

Lanjut

Topik sebelumnya: Performance Monitoring. Topik berikutnya: Advanced Features. Lihat juga Models, Events, Helper & template, daftar dokumentasi.

Nxdom Framework