<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * Simulatore contabilità
 *
 * @package    mod_mtcontabilita
 * @copyright  2026 Mediatouch 2000 Srl <https://www.mediatouch.it>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 * @author     Marco Cappuccio <m.cappuccio@mediatouch.it>
 */

namespace mod_mtcontabilita\export;

/**
 * Excel class
 */
class xlsx {
    /** @var tp type. */
    private $tp;

    /** @var id . */
    private $id;

    /**
     * Construct
     */
    public function __construct($tp, $id) {
        $this->tp = $tp;
        $this->id = $id;
    }

    /**
     * Init
     */
    public function init() {
        global $CFG, $DB;

        require_once("$CFG->libdir/excellib.class.php");
        require_once($CFG->dirroot.'/mod/mtcontabilita/question2/locallib.php');

        $bdecimali = (int)$DB->get_field('mtcontabilita', 'bdecimali', ['id' => $this->id]);
        $ndec = mtc_get_ndec_for_mtcontabilita($this->id, $bdecimali); // 0 oppure 2

        switch ($this->tp) {
            case 1:
                $tit = get_string('situazionefinale', 'mtcontabilita');
                break;
        }
        $filename = $tit."_".$this->id."_".time().".xlsx";
        $workbook = new \MoodleExcelWorkbook('-', 'Excel2007');
        $workbook->send($filename);

        $plan = $workbook->add_format();
        $plan->set_bold(0);
        $bold = $workbook->add_format();
        $bold->set_bold(1);

        $numfmt = $workbook->add_format();
        $numfmt->set_align('right');
        // Migliaia + 0/2 decimali.
        $numfmt->set_num_format($ndec === 2 ? '#,##0.00' : '#,##0');
        $myxls = $workbook->add_worksheet($tit);
        $this->{'mtpagina'.$this->tp}($myxls, $plan, $bold, $numfmt);
        $workbook->close();
        exit;
    }

    /**
     * Pagina 1
     */
    private function mtpagina1(&$myxls, $plan, $bold, $numfmt) {

        $writenum = function(int $row, int $col, $val) use ($myxls, $plan, $numfmt) {
            if ($val === '' || $val === null) {
                $myxls->write($row, $col, '', $plan);
                return;
            }
            if (!is_numeric($val)) {
                // Fallback: meglio vuoto che eccezione.
                $myxls->write($row, $col, '', $plan);
                return;
            }
            $myxls->write_number($row, $col, (float)$val, $numfmt);
        };

        /*
         * 1 => "costo"
         * 2 => "ricavo",
         * 3 => "attività"
         * 4 => "passività"
         * 5 => "patrimonio netto
        */
        $r = 0;
        $c = 0;
        $bold->set_align('center');
        $myxls->merge_cells($r, $c, $r, $c1 = 3);
        $myxls->write($r, $c, get_string('statopatrimoniale', 'mtcontabilita'), $bold);
        $myxls->merge_cells($r, $c = 4, $r, $c1 = 7);
        $myxls->write($r, $c, get_string('contoeconomico', 'mtcontabilita'), $bold);
        $r++;
        $myxls->merge_cells($r, $c0 = 0, $r, $c1 = 1);
        $myxls->write($r, $c0, get_string('attivo', 'mtcontabilita'), $bold);
        $myxls->merge_cells($r, $c0 = 2, $r, $c1 = 3);
        $myxls->write($r, $c0, get_string('passivor', 'mtcontabilita'), $bold);
        $myxls->merge_cells($r, $c0 = 4, $r, $c1 = 5);
        $myxls->write($r, $c0, get_string('costi', 'mtcontabilita'), $bold);
        $myxls->merge_cells($r, $c0 = 6, $r, $c1 = 7);
        $myxls->write($r, $c0, get_string('ricavi', 'mtcontabilita'), $bold);
        $r++; $c = 0;
        for ($i = 0; $i < 4; $i++) {
            $myxls->write($r, $c++, get_string('denominazioneconto', 'mtcontabilita'), $bold);
            $myxls->write($r, $c++, get_string('saldo', 'mtcontabilita'), $bold);
        }
        list($col1, $col2, $col3, $col4, $maxlen) = $this->get_data($r);
        $totcosti = 0;
        $totricavi = 0;
        $totattivo = 0;
        $totpassivo = 0;
        for ($i = 0; $i < $maxlen; $i++) {
            $r++;
            $myxls->write($r, 0, $col1[$i][0], $plan);
            $writenum($r, 1, $col1[$i][1]);
            $myxls->write($r, 2, $col2[$i][0], $plan);
            $writenum($r, 3, $col2[$i][1]);
            $myxls->write($r, 4, $col3[$i][0], $plan);
            $writenum($r, 5, $col3[$i][1]);
            $myxls->write($r, 6, $col4[$i][0], $plan);
            $writenum($r, 7, $col4[$i][1]);

            $totattivo += (float)$col1[$i][1];
            $totpassivo += (float)$col2[$i][1];
            $totcosti += (float)$col3[$i][1];
            $totricavi += (float)$col4[$i][1];
        }
        // Riga utile/perdita.
        $spdelta = $totattivo - $totpassivo;
        $splabel = $spdelta < 0 ? 'utileperdita2' : 'utileperdita1';
        $myxls->write_string($r + 2, 2, get_string($splabel, 'mtcontabilita'));
        $myxls->write_formula($r + 2, 3, '=SUM(B4:B'.($r + 2).')-SUM(D4:D'.($r + 2).')', $numfmt);

        if ($totricavi >= $totcosti) {
            $myxls->write_string($r + 2, 4, get_string('utileperdita1', 'mtcontabilita'));
            $myxls->write_formula($r + 2, 5, '=SUM(H4:H'.($r + 2).')-SUM(F4:F'.($r + 2).')', $numfmt);
        } else {
            $myxls->write_string($r + 2, 6, get_string('utileperdita2', 'mtcontabilita'));
            $myxls->write_formula($r + 2, 7, '=SUM(F4:F'.($r + 2).')-SUM(H4:H'.($r + 2).')', $numfmt);
        }

        $c = 0;
        $lr = 5 + $maxlen;
        $myxls->write_string ($r + 3, $c, 'TOT '.get_string('attivo', 'mtcontabilita'));
        $myxls->write_formula($r + 3, $c + 1, '=SUM(B4:B'.($lr).')', $numfmt);
        $c = 2;
        $myxls->write_string ($r + 3, $c, 'TOT '.get_string('passivor', 'mtcontabilita'));
        $myxls->write_formula($r + 3, $c + 1, '=SUM(D4:D'.($lr).')', $numfmt);
        $c = 4;
        $myxls->write_string ($r + 3, $c, get_string('total', 'mtcontabilita'));
        $myxls->write_formula($r + 3, $c + 1, '=SUM(F4:F'.($lr).')', $numfmt);
        $c = 6;
        $myxls->write_string ($r + 3, $c, get_string('total', 'mtcontabilita'));
        $myxls->write_formula($r + 3, $c + 1, '=SUM(H4:H'.($lr).')', $numfmt);
    }

    /**
     * Getmax
     */
    private function getmax($array) {
        $max = $array[0];
        foreach ($array as $key => $val) {
            if ($max < $val) {
                $max = $val;
            }
        }
        return $max + 1;
    }

   
private function get_col($scheme, $itemtype) {
    $ctrl = strtolower(trim($scheme)) . "-" . strtolower(trim($itemtype));
    switch ($ctrl) {
        case "sp-d":
        case "pn-d":
            return 1;
        case "sp-a":
        case "pn-a":
            return 2;
        case "ce-d":
            return 3;
        case "ce-a":
            return 4;
    }
    return 0;
}

private function get_data(&$r) {
    global $DB;

    $slist = [];

$sql = "SELECT d.listid,
               l.name,
               LOWER(TRIM(l.scheme)) AS scheme,
               d.amount,
               UPPER(TRIM(d.itemtype)) AS itemtype
        FROM {mtcontabilita_itemdet} d
        JOIN {mtcontabilita_list} l ON l.id = d.listid
        WHERE d.mtcontabilitaid = ?
          AND d.type IN (0,1)
          AND TRIM(d.itemtype) <> ''
        ORDER BY d.listid";
        $rs = $DB->get_recordset_sql($sql, [$this->id]);
        foreach ($rs as $rec) {
            $col = $this->get_col($rec->scheme, $rec->itemtype);
            if (!$col) { continue; }

            if (!isset($slist[$rec->listid][$col])) {
                $slist[$rec->listid][$col] = [$rec->name, 0.0];
            }
            $slist[$rec->listid][$col][1] += (float)$rec->amount;
        }
        $rs->close();

    // IMPORTANTISSIMO: in Excel devi fare entrambe le merge.
    $slist = $this->merge_slist_contraposti($slist, 'sp');
    $slist = $this->merge_slist_contraposti($slist, 'ce');


    $col1 = []; $col2 = []; $col3 = []; $col4 = [];
    foreach ($slist as $columns) {
        foreach ($columns as $col => $val) {
            switch ((int)$col) {
                case 1: $col1[] = $val; break;
                case 2: $col2[] = $val; break;
                case 3: $col3[] = $val; break;
                case 4: $col4[] = $val; break;
            }
        }
    }

    $maxlen = max(count($col1), count($col2), count($col3), count($col4));
    $col1 = array_pad($col1, $maxlen, ["", null]);
    $col2 = array_pad($col2, $maxlen, ["", null]);
    $col3 = array_pad($col3, $maxlen, ["", null]);
    $col4 = array_pad($col4, $maxlen, ["", null]);

    return [$col1, $col2, $col3, $col4, $maxlen];
}

    /**
     * Accorpa le voci presenti su entrambi i lati (es. stesso conto in Attivo e Passivo,
     * oppure in Costi e Ricavi), mantenendo solo il lato con importo maggiore
     * e usando la differenza come nuovo importo.
     *
     * @param array  $slist [listid => [col => [name, amount]]]
     * @param string $sez   'sp' oppure 'ce'
     * @return array
     */
    private function merge_slist_contraposti(array $slist, string $sez): array {
        foreach ($slist as $listid => $columns) {

            if ($sez === 'sp') {
                // Colonne 1 = Attivo, 2 = Passivo
                $attivo  = isset($columns[1]) ? (float)$columns[1][1] : 0.0;
                $passivo = isset($columns[2]) ? (float)$columns[2][1] : 0.0;

                if ($attivo > 0 && $passivo > 0) {
                    if ($attivo > $passivo) {
                        // Rimane solo Attivo con la differenza.
                        $columns[1][1] = $attivo - $passivo;
                        unset($columns[2]);
                    } else if ($passivo > $attivo) {
                        // Rimane solo Passivo con la differenza.
                        $columns[2][1] = $passivo - $attivo;
                        unset($columns[1]);
                    } else {
                        // Importi uguali: il conto si annulla.
                        unset($columns[1], $columns[2]);
                    }
                }

            } else if ($sez === 'ce') {
                // Colonne 3 = Costi, 4 = Ricavi
                $costi  = isset($columns[3]) ? (float)$columns[3][1] : 0.0;
                $ricavi = isset($columns[4]) ? (float)$columns[4][1] : 0.0;

                if ($costi > 0 && $ricavi > 0) {
                    if ($costi > $ricavi) {
                        // Rimangono solo i Costi (col 3) con differenza.
                        $columns[3][1] = $costi - $ricavi;
                        unset($columns[4]);
                    } else if ($ricavi > $costi) {
                        // Rimangono solo i Ricavi (col 4) con differenza.
                        $columns[4][1] = $ricavi - $costi;
                        unset($columns[3]);
                    } else {
                        // Importi uguali: il conto si annulla.
                        unset($columns[3], $columns[4]);
                    }
                }
            }

            // Se dopo le operazioni non è rimasto nulla, rimuovo del tutto il listid.
            if (empty($columns)) {
                unset($slist[$listid]);
            } else {
                $slist[$listid] = $columns;
            }
        }

        return $slist;
    }
    
    /**
     * get_type
     */
    private function get_type($scheme, $itemtype) {
        $ctrl = strtolower($scheme)."-".strtolower($itemtype);
        switch ($ctrl) {
            case "sp-d":
                $rit = 0;
                break;
            case "sp-a":
                $rit = 1;
                break;
            case "ce-d":
                $rit = 2;
                break;
            case "ce-a":
                $rit = 3;
                break;
            case "pn-d":
                $rit = 4;
                break;
            case "pn-a":
                $rit = 5;
                break;
        }
        return $rit;
    }
}
