tinyqc/app/Models/Qc/ResultsModel.php

<?php
namespace App\Models\Qc;

use App\Models\BaseModel;

class ResultsModel extends BaseModel {
    protected $table = 'results';
    protected $primaryKey = 'result_id';
    protected $allowedFields = [
        'control_id',
        'test_id',
        'res_date',
        'res_value',
        'created_at',
        'updated_at',
        'deleted_at'
    ];
    protected $useTimestamps = true;
    protected $useSoftDeletes = true;

    public function search($keyword = null) {
        if ($keyword) {
            return $this->groupStart()
                ->like('res_value', $keyword)
                ->groupEnd()
                ->findAll();
        }
        return $this->findAll();
    }

    /**
     * Get results by date and control
     */
    public function getByDateAndControl(string $date, int $controlId): array {
        $builder = $this->db->table('results r');
        $builder->select('
            r.result_id as id,
            r.control_id as controlId,
            r.test_id as testId,
            r.res_date as resDate,
            r.res_value as resValue,
            tc.comment_text as resComment
        ');
        $builder->join('test_comments tc', 'tc.test_id = r.test_id AND tc.comment_date = r.res_date AND tc.deleted_at IS NULL', 'left');
        $builder->where('r.res_date', $date);
        $builder->where('r.control_id', $controlId);
        $builder->where('r.deleted_at', null);

        return $builder->get()->getResultArray();
    }

    /**
     * Get results by month for a specific test (for monthly entry)
     */
    public function getByMonth(int $testId, string $month): array {
        $builder = $this->db->table('results r');
        $builder->select('
            r.result_id as id,
            r.control_id as controlId,
            r.test_id as testId,
            r.res_date as resDate,
            r.res_value as resValue,
            tc.comment_text as resComment
        ');
        $builder->join('test_comments tc', 'tc.test_id = r.test_id AND tc.comment_date = r.res_date AND tc.deleted_at IS NULL', 'left');
        $builder->where('r.test_id', $testId);
        $builder->where('r.res_date >=', $month . '-01');
        $builder->where('r.res_date <=', $month . '-31');
        $builder->where('r.deleted_at', null);
        $builder->orderBy('r.res_date', 'ASC');

        return $builder->get()->getResultArray();
    }

    /**
     * Get results by control and month (for monthly entry calendar grid)
     */
    public function getByControlAndMonth(int $controlId, int $testId, string $month): array {
        $builder = $this->db->table('results r');
        $builder->select('
            r.result_id as id,
            r.res_date as resDate,
            r.res_value as resValue,
            tc.comment_text as resComment
        ');
        $builder->join('test_comments tc', 'tc.test_id = r.test_id AND tc.comment_date = r.res_date AND tc.deleted_at IS NULL', 'left');
        $builder->where('r.control_id', $controlId);
        $builder->where('r.test_id', $testId);
        $builder->where('r.res_date >=', $month . '-01');
        $builder->where('r.res_date <=', $month . '-31');
        $builder->where('r.deleted_at', null);
        $builder->orderBy('r.res_date', 'ASC');

        return $builder->get()->getResultArray();
    }

    /**
     * Upsert results (insert or update based on date/control/test)
     */
    public function upsertResult(array $data): int {
        // Check if record exists
        $existing = $this->where('control_id', $data['control_id'])
            ->where('test_id', $data['test_id'])
            ->where('res_date', $data['res_date'])
            ->where('deleted_at', null)
            ->first();

        if ($existing) {
            $this->update($existing['resultId'], $data);
            return $existing['resultId'];
        } else {
            return $this->insert($data, true);
        }
    }

    /**
     * Batch upsert results
     */
    public function batchUpsertResults(array $results): array {
        $ids = [];
        foreach ($results as $result) {
            $ids[] = $this->upsertResult($result);
        }
        return $ids;
    }
}