thetool/application/VoiceCallHistoryJob/VoiceCallHistoryJobModel.php

<?php

class VoiceCallHistoryJobModel {
    public $id;
    public $date;
    public $status;
    public $create;
    public $edit;


    public function __construct($data = []) {
        foreach ($data as $field => $value) {
            if (property_exists(get_called_class(), $field)) {
                $this->$field = $value;
            }
        }
    }

    /**
     * Generate SQL Filter condition (space separated) for a given column.
     *
     * @param string|null $filterValue The filter value to match against.
     * @param string $columnName The name of the column in the database table.
     * @return string The SQL condition generated based on the filter value and column name.
     */
    public static function generateFilterCondition(?string $filterValue, string $columnName): string {
        $sql = "";
        if (!empty($filterValue)) {
            $filterItems = explode(" ", $filterValue);
            foreach ($filterItems as $item) {
                $sql .= " AND `$columnName` LIKE '%" . $item . "%'";
            }
        }

        return $sql;
    }

    public static function createJobsUntilToday(): array {
        $db = FronkDB::singleton();

        // $i = first day of the month; $i <= today; $i += 1 day
        $values = [];
        for ($i = strtotime(date("Y-m-01")); $i <= strtotime(date("Y-m-d")); $i += 86400) {
            $values[] = "('" . date("Y-m-d", $i) . "')";
        }

        $valueStr = implode(", ", $values);

        $db->query("INSERT INTO `VoiceCallHistoryJob` (`date`) VALUES $valueStr ON DUPLICATE KEY UPDATE date=VALUES(date)");
        $db->query("UPDATE `VoiceCallHistoryJob` SET `status` = 'created', `finished` = NULL WHERE `date` = '" . date("Y-m-d") . "'");

        return [
            "message" => "Created " . count($values) . " jobs."
        ];
    }

    public static function updateJobStatus($id, $status): array {
        $db = FronkDB::singleton();

        $escapedStatus = $db->escape($status);
        $escapedId = $db->escape($id);

        $finished = $status == "success" ? ", `finished` = NOW()" : "";

        $db->query("UPDATE `VoiceCallHistoryJob` SET `status` = '$escapedStatus' $finished WHERE `id` = $escapedId");

        return [
            "message" => "Updated job $id status to $status."
        ];
    }

    public static function getJobsNotDone(): array {
        $db = FronkDB::singleton();

        $query = $db->query("SELECT * FROM `VoiceCallHistoryJob` WHERE `status` = 'created' OR `status` = 'failed' OR `status` = 'pending' ORDER BY `date`");

        $items = [];
        if($db->num_rows($query)) {
            while($data = $db->fetch_object($query)) {
                $items[] = new VoiceCallHistoryJobModel($data);
            }
        }
        return $items;
    }

}