<?php

/*
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/Scripting/PHPClass.php to edit this template
 */

/**
 * Description of BackofficeManager
 *
 * @author Americo
 */
include_once("server/class/User.php");
include_once("server/lib/mod_dbm/package.dbm.php");

class BackofficeManager {

    //put your code here
    private $user;
    private $dao;
    private $lapse;
    private $toTimestamp;
    private $idOrganizacion;

    function __construct(User $user) {
        $this->user = $user;
        $this->dao = new dbObject();
        $this->idOrganizacion = $user->getIdOrganizacion();
    }

    public function setRange($toTs, $days) {
        $this->lapse = $days * 86400;
        $this->toTimestamp = $toTs;
    }

    public function getKmData($to) {

        $kmData = null;
        $q = sprintf("SELECT i.machine_id, min(i.mileage) as inicial,max(i.mileage) as final,max(mileage) - min(i.mileage) as recorrido, m.machine_name, m.matricula 
                        FROM f22.log_ignition i
                        LEFT JOIN machines m ON m.machine_id = i.machine_id
                        WHERE 
                        i.gps_timestamp >= %d AND i.gps_timestamp <= %d
                        AND event_id IN (\"GTIGN\",\"GTIGF\")
                        AND i.machine_id IN (SELECT machine_id FROM machines WHERE id_grupo IN (SELECT id_grupo FROM grupos WHERE id_organizacion = %d))
                        GROUP BY i.machine_id
                        ORDER BY max(mileage) - min(i.mileage) DESC", $to - $this->lapse, $to, $this->user->getIdOrganizacion());

        $res = $this->dao->fetchQuery($q);
        if (is_array($res) && count($res)) {

            $kmData['totals']['sumKm'] = 0;
            $kmData['totals']['maxKm'] = 0;
            $kmData['totals']['minKm'] = PHP_INT_MAX;
            $kmData['totals']['maxKmUnit'] = null;
            $kmData['totals']['minKmUnit'] = null;
            $kmData['totals']['maxKmUnitMatricula'] = null;
            $kmData['totals']['minKmUnitMatricula'] = null;

            foreach ($res as $unit) {
                $kmData['details'][$unit['machine_id']]['machineName'] = $unit['machine_name'];
                $kmData['details'][$unit['machine_id']]['machineMatricula'] = $unit['matricula'];
                $kmData['details'][$unit['machine_id']]['recorrido'] = round($unit['recorrido']);
                $kmData['totals']['sumKm'] += $unit['recorrido'];
                if ($unit['recorrido'] > $kmData['totals']['maxKm']) {
                    $kmData['totals']['maxKm'] = $unit['recorrido'];
                    $kmData['totals']['maxKmUnit'] = $unit['machine_name'];
                    $kmData['totals']['maxKmUnitMatricula'] = $unit['matricula'];
                }
                if ($unit['recorrido'] < $kmData['totals']['minKm']) {
                    $kmData['totals']['minKm'] = $unit['recorrido'];
                    $kmData['totals']['minKmUnit'] = $unit['machine_name'];
                    $kmData['totals']['minKmUnitMatricula'] = $unit['matricula'];
                }
            }
            foreach ($kmData['details'] as $unitId => $unit) {
                $kmData['details'][$unitId]['kmPct'] = round(100 * ($unit['recorrido'] / $kmData['totals']['maxKm']));
            }
        }
        $kmData['totals']['sumKm'] = round($kmData['totals']['sumKm']);
        return $kmData;
    }

    public function getOverspeedCount($to) {
        $speedingRep = null;
        $q = sprintf("SELECT s.machine_id, m.machine_name, m.matricula, m.max_allowed_speed, count(*) as excesos, round(max(speed),0) as max_speed
                        FROM f22.log_speeding s
                        LEFT JOIN machines m ON m.machine_id = s.machine_id
                        WHERE s.in_range = 0 
                        AND s.gps_timestamp >= %d AND s.gps_timestamp <= %d
                        AND s.machine_id IN (SELECT machine_id FROM machines WHERE id_grupo IN (SELECT id_grupo FROM grupos WHERE id_organizacion = %d))
                        GROUP BY s.machine_id
                        ORDER BY count(*) DESC", $to - $this->lapse, $to, $this->user->getIdOrganizacion());
        $res = $this->dao->fetchQuery($q);

        if (is_array($res) && count($res)) {

            $speedingRep['totals']['count'] = 0;
            $speedingRep['totals']['maxSpeed'] = 0;
            $speedingRep['totals']['maxSpeedUnit'] = null;
            $speedingRep['totals']['maxSpeedUnitMastricula'] = null;

            foreach ($res as $unit) {
                $speedingRep['totals']['count'] += $unit['excesos'];
                if (($unit['max_speed'] > $speedingRep['totals']['maxSpeed'])) {
                    $speedingRep['totals']['maxSpeed'] = $unit['max_speed'];
                    $speedingRep['totals']['maxSpeedUnit'] = $unit['machine_name'];
                    $speedingRep['totals']['maxSpeedUnitMatricula'] = $unit['matricula'];
                }
                $speedingRep['details'][$unit['machine_id']]['machineName'] = $unit['machine_name'];
                $speedingRep['details'][$unit['machine_id']]['machineMatricula'] = $unit['matricula'];
                //$speedingRep['details'][$unit['machine_id']]['maxAllowed'] = $unit['max_allowed_speed'];
                $speedingRep['details'][$unit['machine_id']]['count'] = $unit['excesos'];
                $speedingRep['details'][$unit['machine_id']]['max'] = $unit['max_speed'];
            }
        }
        return $speedingRep;
    }

    public function getKmFlota() {

        $report = null;
        $repActual = $this->getKmData($this->toTimestamp);
        $repAnterior = $this->getKmData($this->toTimestamp - $this->lapse - 1);

        if (is_array($repActual) && count($repActual)) {

            // dKm: La diferencia porcentual de kilometraje. es Positiva si se recorrieron en el
            // período corriente más km que en el anterior, negativa si se recorrieron menos y cero
            // si no hubo variación. Los valores están expresados en porcentajes de variación.
            // En caso de que algún denominador sea cero, adopta el valor NULL.
            if (isset($repAnterior['totals']['sumKm']) && isset($repActual['totals']['sumKm'])) {
                if ($repAnterior['totals']['sumKm'] > $repActual['totals']['sumKm']) {
                    if ($repActual['totals']['sumKm'] > 0) {
                        $dSumKm = round(-100 * (($repAnterior['totals']['sumKm'] / $repActual['totals']['sumKm']) - 1));
                    } else {
                        $dSumKm = null;
                    }
                } elseif ($repActual['totals']['sumKm'] > $repAnterior['totals']['sumKm']) {
                    if ($repAnterior['totals']['sumKm'] > 0) {
                        $dSumKm = round(100 * (($repActual['totals']['sumKm'] / $repAnterior['totals']['sumKm']) - 1));
                    } else {
                        $dSumKm = null;
                    }
                } else {
                    $dSumKm = 0;
                }
                $kmReport['km']['dKm'] = $dSumKm;
            }

            // totKm: El total de km recorridos por la flota.
            $kmReport['km']['totKm'] = $repActual['totals']['sumKm'];
            //$report['km']['totKm'] = $repActual['km']['sumKm'];

            $kmReport['overspeedCount'] = $this->getOverspeedCount($this->toTimestamp);

            $kmReport['km']['unitCount'] = count($repActual['details']);
            $kmReport['km']['avgKm'] = round($kmReport['km']['totKm'] / ($this->lapse / 86400)); //$kmReport['km']['unitCount']);
            $kmReport['km']['minKm'] = round($repActual['totals']['minKm']);
            $kmReport['km']['maxKm'] = round($repActual['totals']['maxKm']);
            $kmReport['km']['minKmUnit'] = $repActual['totals']['minKmUnit'];
            $kmReport['km']['maxKmUnit'] = $repActual['totals']['maxKmUnit'];
            $kmReport['km']['minKmUnitMatricula'] = $repActual['totals']['minKmUnitMatricula'];
            $kmReport['km']['maxKmUnitMatricula'] = $repActual['totals']['maxKmUnitMatricula'];
            $kmReport['details'] = $repActual['details'];
        }
        return $kmReport;
    }

    public function getLagList($unitType) {

        switch ($unitType) {
            case REPORTE_PROTRACKING:
                $unitTypeCondition = "m.machine_key IS NOT NULL";
                break;

            case REPORTE_FORESTRACKING:
                $unitTypeCondition = "m.machine_key IS NOT NULL";
                break;
        }
        

        $q = sprintf("SELECT o.org_id, o.org_name, m.machine_id, machine_name, matricula, imei, current_lat, current_lng, current_gps_timestamp, last_message_timestamp, 
                        last_source_file_id, s.creation_date,
                        UNIX_TIMESTAMP() - last_message_timestamp as device_lag, UNIX_TIMESTAMP() - s.creation_date as production_lag,
                        admin_report_snooze, report_snooze
                        FROM machines m
                        LEFT JOIN grupos g ON g.id_grupo = m.id_grupo 
                        LEFT JOIN organizaciones o ON o.org_id = g.id_organizacion
                        LEFT JOIN source_files s ON s.source_file_id = m.last_source_file_id
                        WHERE admin_report_snooze = 0
                        AND %s", $unitTypeCondition);
    }

    public function registerSending($reportType, $body, $sent = null) {

        if (is_null($sent)) {
            $sent = time();
        }

        if (is_null($body)) {
            $body = "";
        }
        $dao = new dbObject();
        $sql = "INSERT INTO log_borr(user_id, report_body, report_sent, report_to_timestamp, report_lapse, report_type) VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $dao->connection->prepare($sql);
        $uid = $this->user->getUserId();
        $stmt->bind_param("isiiis", $uid, $body, $sent, $this->toTimestamp, $this->lapse, $reportType);
        $stmt->execute();

        //$affectedRows = $dao->execute($q);
        return $dao->connection->getAutoIncValue();
    }

    public function registerSendingResult($reportId, $result) {

        $q = sprintf(
                "UPDATE log_borr set sending_status = \"%s\" WHERE borr_id = %d;",
                ($result) ? "OK" : "FAIL",
                $reportId
        );
        $affectedRows = $this->dao->execute($q);
        return ($affectedRows === 1);
    }

    public function registerReading($reportId, $timestamp = null) {
        if (is_null($timestamp)) {
            $timestamp = time();
        }
        $q = sprintf("INSERT INTO log_borr_reads (borr_id, red) VALUES (%d,%d);", $reportId, $timestamp);
        $this->dao->execute($q);
    }

}
