<?php

require_once "IDataClass.php";
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of UTENTI
 *
 * @author Stefano
 */
class Domanda extends DataClass {

    const TABLE_NAME = "DOMANDA";
    const TABLE_NAME_CONTRIBUTO = "CONTRIBUTO_PROVINCIALE";
    const SEQ_NAME = "DOMANDA_ID_SEQ";
    const SP_ = "SPDOMANDASAVE";
    const SP_CLICKDAY = "SP_PRESENTA_DOMANDA";

    public $ID = 0; // Primary key
    public $CODICE_FISCALE_LEGALE = null;
    public $CODICE_FISCALE_IMPRESA = null;
    public $DOMANDA_JSON = null;
    public $STATO = 0;
    public $FILEPATH = null;
    public $HASH_FILE = null;
    public $DATA_INSERIMENTO = null;
    public $DATA_MODIFICA = null;
    public $DATA_COMPLETAMENTO = null;
    //public $PROTOCOLLO = null;
    public $ID_SEDEOPERATIVA = null;
    //NUOVI
    public $SPID_MOBILE_PHONE = "";
    public $SPID_TIPO_DOCUMENTO = "";
    public $SPID_NUMERO_DOCUMENTO = "";
    public $SPID_ENTE_RILASCIO = "";
    public $SPID_DATA_RILASCIO = "";
    public $SPID_DATA_SCADENZA = "";
    public $INFO_PEC = "";
    public $RECAPITO_TELEFONICO = ""; //IMPRESA
    public $DATA_APERTURA_SEDE_LEGALE = "";
    public $IBAN = null;
    public $ISTITUTO_DI_CREDITO = "";
    public $FILIALE_DI = "";
    //public $CLUSTER_TOT_VOL_IVA_2018 = ""; // VOLUME D'AFFARI 2018
    //public $CLUSTER_CONTRIBUTO_PREVISTO = ""; // CONTRIBUTO IN BASE AL VOLUME 2018
    //public $TOT_VOL_IVA_2019 = ""; // VOLUME D'AFFARI 2019
    public $CONTRIBUTO_DICHIARATO = ""; // CONTRIBUTO CALCOLATO 
    public $INFO_ADDETTI = "";
    public $ADDETTI_DICHIARATI = 0; // Ultimo bilancio disponibile (2019)
    public $DENOMINAZIONE_IMPRESA = "";
    public $DATA_SPORTELLO = "";
    public $SEDEOPERATIVA_COMUNE = "";
    public $SEDEOPERATIVA_PROVINCIA = "";
    public $DECRETO = "";
    public $FILEPATH_DECRETO = "";
    public $DATA_DECRETO = "";
    public $DATA_INIZIO_ATTIVITA = ""; // Sede per la quale è richiesto il contributo
    public $VOLUME_AFFARI_DICHIARATO_2018 = ""; // BIlancio 2018
    public $VOLUME_AFFARI_DICHIARATO = ""; // Ultimo bilancio disponibile (2019)
    public $REGIME_FISCALE = 0;
    public $COLLEGATA = 0;
    public $VERSIONE = 0; // SECONDA VERSIONE DEL EBANDO
    public $Protocolli = array();

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            if (isset($src['ID']) && $src['ID'] > 0) {
                $this->_loadAndFillObject($src['ID'], self::TABLE_NAME, $src);
            } else {
                $this->_loadByRow($src, $stripSlashes);
            }
        } elseif (intval($src)) {

            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID = :id";
            $query = $con->prepare($sql);
            $query->bindParam(":id", $src);
            try {
                $query->execute();
                $it = $query->fetchAll(PDO::FETCH_ASSOC);
                $it[0]['DOMANDA_JSON'] = stream_get_contents($it[0]['DOMANDA_JSON']);
                Utils::FillObjectFromRow($this, $it[0]);
            } catch (Exception $exc) {
                $exc->getMessage();
            }
        }
        if ($this->ID > 0) {
            $this->Protocolli = DomandeProtocollo::loadFromId($this->ID);
        }
    }
    /**
     * Verifico se il file che sto scaricando appartiene ad una istanza presentata dal CF loggato
     * @global type $con
     * @global type $LoggedAccount
     * @param type $path
     * @return boolean
     */
    public static function verifyFileStreamer($path = "") {
        global $con, $LoggedAccount;
        
        $return=false;
        $verify = $con->prepare("SELECT CODICE_FISCALE_IMPRESA FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:cod AND STATO=1");
        $verify->bindParam(":cod", $LoggedAccount->CODICE_FISCALE);
        try {
            $verify->execute();
            $i = 0;
            while ($itVerify = $verify->fetch(PDO::FETCH_ASSOC)) {                
                $generoAlVoloNomeFile = md5($itVerify['CODICE_FISCALE_IMPRESA']) . ".pdf";
                $pos = strpos($path, $generoAlVoloNomeFile);
                if ($pos === false) {
                    /*echo "non esiste<br>";*/
                } else {
                    $return = true;
                }
                $i++;
            }
        } catch (Exception $exc) {
            
        }
        return $return;
    }

    public static function verifyFile($path = "") {
        global $con, $LoggedAccount;
        $return = array();

        $verify = $con->prepare("SELECT ID FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:cod AND FILEPATH=:path ");
        $verify->bindParam(":cod", $LoggedAccount->CODICE_FISCALE);
        $verify->bindParam(":path", $path);
        try {
            $verify->execute();
            $itVerify = $verify->fetch(PDO::FETCH_ASSOC);
            if (intval($itVerify['ID']) > 0) {
                $return['ID'] = $itVerify['ID'];
                $return['esito'] = 1;
            } else {
                $return['esito'] = -999;
            }
        } catch (Exception $exc) {
            $return['esito'] = -999;
            $return['ID'] = -999;
//            $return['descrizioneErrore'] = $exc->getMessage();
        }
        return $return;
    }

    /*
     * Select id by codice Fiscale
     */

    public static function selectListaDomande($codice_fiscale = "") {
        global $con;
        $return = array();
        $verify = $con->prepare("SELECT DOMANDA.VERSIONE, DOMANDA.CODICE_FISCALE_IMPRESA, 
                                DOMANDA.ID, DOMANDA.STATO, DOMANDA.DENOMINAZIONE_IMPRESA,
                                DOMANDA_ADESIONE.DATA_INSERIMENTO, DOMANDA_ADESIONE.DATA_COMPLETAMENTO 
                                FROM " . self::TABLE_NAME . " LEFT JOIN DOMANDA_ADESIONE 
                                ON DOMANDA.ID=DOMANDA_ADESIONE.ID
                                WHERE DOMANDA.CODICE_FISCALE_LEGALE=:cod
                                ORDER BY DOMANDA.STATO ASC");
        $verify->bindParam(":cod", $codice_fiscale);
        try {
            $verify->execute();
            $itVerify = $verify->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
            $return = $itVerify;
        } catch (Exception $exc) {
            
        }
        return $return;
    }

    /*
     * Select id by codice Fiscale
     */

    public static function selectIdFromCodiceF($codice_fiscale = "", $cod_fis_impresa = "") {
        global $con;

        $return = new Domanda();
        $verify = $con->prepare("SELECT * FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:cod AND CODICE_FISCALE_IMPRESA=:cod_fis_impresa ");
        $verify->bindParam(":cod", $codice_fiscale);
        $verify->bindParam(":cod_fis_impresa", $cod_fis_impresa);
        try {
            $verify->execute();
            $itVerify = $verify->fetch(PDO::FETCH_ASSOC);
            //$itVerify = $verify->fetch(PDO::FETCH_ASSOC);            
            if (intval($itVerify['ID']) > 0) {
                Utils::FillObjectFromRow($return, $itVerify);
            }
        } catch (Exception $exc) {
            
        }
        return $return;
    }

    /**
     * 
     * @global type $con
     * @param type $codice_fiscale
     * @param type $cod_fis_impresa
     * @return \Domanda
     */
    public static function verificaPresenzaDomanda($codice_fiscale = "", $cod_fis_impresa = "") {
        global $con;

        $return = new Domanda();
        $verify = $con->prepare("SELECT D.ID, D.VERSIONE, D.STATO, D.CODICE_FISCALE_LEGALE, D.CODICE_FISCALE_IMPRESA, A.DATA_COMPLETAMENTO FROM " . self::TABLE_NAME . " D "
                . "LEFT JOIN DOMANDA_ADESIONE A ON D.ID = A.ID "
                . " WHERE D.CODICE_FISCALE_LEGALE=:cod AND D.CODICE_FISCALE_IMPRESA=:cod_fis_impresa");
        $verify->bindParam(":cod", $codice_fiscale);
        $verify->bindParam(":cod_fis_impresa", $cod_fis_impresa);
        try {
            $verify->execute();
            $itVerify = $verify->fetch(PDO::FETCH_ASSOC);
            //$itVerify = $verify->fetch(PDO::FETCH_ASSOC);            
            if (intval($itVerify['ID']) > 0) {
                Utils::FillObjectFromRow($return, $itVerify);
            }
        } catch (Exception $exc) {
            
        }
        return $return;
    }

    /**
     * 
     * @global type $con
     * @param type $id
     * @return \DomandaAdesione
     */
    public static function getDomandaForAdesione($id = "") {
        global $con;

        $return = new Domanda();
        $verify = $con->prepare("SELECT D.ID, D.CODICE_FISCALE_LEGALE, D.CODICE_FISCALE_IMPRESA, D.DOMANDA_JSON, D.VERSIONE FROM " . self::TABLE_NAME . " D "
                . " WHERE D.ID=:id");
        $verify->bindParam(":id", $id);
        try {
            $verify->execute();
            $itVerify = $verify->fetch(PDO::FETCH_ASSOC);
            if (intval($itVerify['ID']) > 0) {
                $itVerify['DOMANDA_JSON'] = stream_get_contents($itVerify['DOMANDA_JSON']);
            }
        } catch (Exception $exc) {
            
        }
        return $itVerify;
    }

    public function domandeClickDay() {
        global $con, $LoggedAccount;
        $return = array();
        if ($LoggedAccount->CODICE_FISCALE == "") {
            $return['esito'] = -999;
            $return['erroreDescrizione'] = "Codice fiscale non presente!";
            return $return;
        }

        $sql = "SELECT i.DATA_PRESENTAZIONE, d.ID, d.CODICE_FISCALE_LEGALE , d.CODICE_FISCALE_IMPRESA, d.SEDEOPERATIVA_PROVINCIA, "
                . " d.DATA_COMPLETAMENTO, d.STATO "
                . " FROM " . self::TABLE_NAME . " d "
                . "LEFT JOIN " . DomandeInviate::TABLE_NAME . " i ON i.ID_DOMANDA = d.ID "
                . "WHERE d.CODICE_FISCALE_LEGALE=:cod AND (d.STATO = 1 OR d.STATO = 2)";

        //$verify = $con->prepare("SELECT * FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:cod AND (STATO = 1 OR STATO = 2) ");
        $verify = $con->prepare($sql);
        $verify->bindParam(":cod", $LoggedAccount->CODICE_FISCALE);
        try {
            $verify->execute();
            $itVerify = $verify->fetchAll(PDO::FETCH_ASSOC);
            $return = $itVerify;
        } catch (Exception $exc) {
            $return['esito'] = -999;
            $return['erroreDescrizione'] = $exc->getMessage();
        }
        return $return;
    }

    public function domandeStatoCompletato($stato = 1) {
        global $con, $LoggedAccount;
        $return = array();
        $verify = $con->prepare("SELECT * FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:cod AND STATO=:stato");
//        $verify = $con->prepare("SELECT * FROM DOMANDA A INNER JOIN CONTRIBUTO_PROVINCIALE B  ON  B.PROVINCIA=A.SEDEOPERATIVA_PROVINCIA WHERE A.CODICE_FISCALE_LEGALE=:cod  AND A.STATO=1 AND B.ABILITATO=1");
        $verify->bindParam(":cod", $LoggedAccount->CODICE_FISCALE);
        $verify->bindParam(":stato", $stato);
        try {
            $verify->execute();
            $itVerify = $verify->fetchAll(PDO::FETCH_ASSOC);
            $return = $itVerify;
        } catch (Exception $exc) {
            $return['esito'] = -999;
            $return['erroreDescrizione'] = $exc->getMessage();
        }
        return $return;
    }

    public static function controlloContributo($provincia = "") {
        global $con;
        $return = array();
        $verify = $con->prepare("SELECT ABILITATO FROM " . self::TABLE_NAME_CONTRIBUTO . " WHERE PROVINCIA=:provincia  ");
        $verify->bindParam(":provincia", $provincia);
        try {
            $verify->execute();
            $itVerify = $verify->fetch(PDO::FETCH_ASSOC);
            $return = $itVerify['ABILITATO'];
        } catch (Exception $exc) {
            $return['esito'] = -999;
            $return['descrizioneErrore'] = $exc->getMessage();
        }
        return $return;
    }

    public static function LoadDataTable($searchQuery = "", $searchArray = array(), $columnName = array(), $columnSortOrder = array(), $start = 0, $offset = 0) {

        return parent::_loadDataTable(self::TABLE_NAME, $searchQuery, $searchArray, $columnName, $columnSortOrder, $start, $offset);
    }

    public static function LoadDataTableLeft($searchQuery = "", $searchArray = array(), $columnName = array(), $columnSortOrder = array(), $start = 0, $offset = 0) {

        $sql = "SELECT "
                . "(SELECT NUMERO_PROTOCOLLO FROM DOMANDE_PROTOCOLLO WHERE ID_DOMANDA  = d.ID AND FASE = 1) AS PROTOCOLLO_SOTTOMISSIONE,
                    (SELECT DATA_PROTOCOLLO FROM DOMANDE_PROTOCOLLO WHERE ID_DOMANDA  = d.ID AND FASE = 1) AS DATA_SOTTOMISSIONE,
                    (SELECT NUMERO_PROTOCOLLO FROM DOMANDE_PROTOCOLLO WHERE ID_DOMANDA  = d.ID AND FASE = 2) AS PROTOCOLLO_EROGAZIONE,
                    (SELECT DATA_PROTOCOLLO FROM DOMANDE_PROTOCOLLO WHERE ID_DOMANDA  = d.ID AND FASE = 2) AS DATA_EROGAZIONE,"
                . "a.FILEPATH_CERTIFICATO, i.DATA_PRESENTAZIONE, d.ID, d.CODICE_FISCALE_LEGALE , d.CODICE_FISCALE_IMPRESA, d.SEDEOPERATIVA_PROVINCIA, "
                . " d.DATA_COMPLETAMENTO,"
                . "CASE WHEN i.ID_DOMANDA is null THEN d.STATO "
                . "ELSE 2 END AS STATO "
                . " FROM " . self::TABLE_NAME . " d "
                . "LEFT JOIN " . DomandeInviate::TABLE_NAME . " i ON i.ID_DOMANDA = d.ID "
                //. "LEFT JOIN ". DomandeProtocollo::TABLE_NAME." p ON p.ID_DOMANDA = d.ID "
                . "LEFT JOIN " . DomandeAmmesse::TABLE_NAME . " a ON a.ID_DOMANDA = d.ID "
                . "WHERE 1 = 1 " . $searchQuery;


        //echo $sql;
        return parent::_loadDataTable($sql, $searchQuery, $searchArray, $columnName, $columnSortOrder, $start, $offset);
    }

    public function updateStateDomanda() {
        global $con;
        $return = array();
        $sql = "UPDATE " . self::TABLE_NAME . " SET  FILEPATH_DECRETO=:filepath,STATO=:stato,DATA_DECRETO=SYSTIMESTAMP  WHERE ID=:id";
        $query = $con->prepare($sql);
        $query->bindParam(":filepath", $this->FILEPATH_DECRETO);
        $query->bindParam(":stato", $this->STATO);
        $query->bindParam(":id", $this->ID);
        try {
            $query->execute();
            $return['esito'] = 1;
        } catch (Exception $exc) {
            $return['erroreDescrizione'] = $exc->getMessage();
            $return['esito'] = -999;
        }
        return $return;
    }

    /*
     * Modificare ogni qual volta si fa un update o insert
     */

    public function Save() {
        global $con;
        $vars = get_object_vars($this);
        ////       
        //  1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2
        $sql = "CALL " . self::SP_ . " (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //$this->CLUSTER_TOT_VOL_IVA_2018 = str_replace(",", ".", $this->CLUSTER_TOT_VOL_IVA_2018);
        //$this->CLUSTER_CONTRIBUTO_PREVISTO = str_replace(",", ".", $this->CLUSTER_CONTRIBUTO_PREVISTO);
        //$this->TOT_VOL_IVA_2019 = str_replace(",", ".", $this->TOT_VOL_IVA_2019);
        $this->CONTRIBUTO_DICHIARATO = str_replace(",", ".", $this->CONTRIBUTO_DICHIARATO);
        $this->VOLUME_AFFARI_DICHIARATO_2018 = str_replace(",", ".", $this->VOLUME_AFFARI_DICHIARATO_2018);
        $this->VOLUME_AFFARI_DICHIARATO = str_replace(",", ".", $this->VOLUME_AFFARI_DICHIARATO);
//echo $sql.'<br/>';     
//echo '1 =>'.$this->ID.'<br/>';
//echo '2 =>'.$this->CODICE_FISCALE_LEGALE.'<br/>';
//echo '3 =>'.$this->CODICE_FISCALE_IMPRESA.'<br/>';
//echo '4 =>'.$this->IBAN.'<br/>';
//echo '5 =>'.$this->DOMANDA_JSON.'<br/>';
//echo '6 =>'.$this->STATO.'<br/>';
//echo '7 =>'.$this->FILEPATH.'<br/>';
//echo '8 =>'.$this->HASH_FILE.'<br/>';
//echo '9 =>'.$this->PROTOCOLLO.'<br/>';
//echo '10 =>'.$this->ID_SEDEOPERATIVA.'<br/>';
//echo '11 =>'.$this->SPID_MOBILE_PHONE.'<br/>';
//echo '12 =>'.$this->SPID_TIPO_DOCUMENTO.'<br/>';
//echo '13 =>'.$this->SPID_NUMERO_DOCUMENTO.'<br/>';
//echo '14 =>'.$this->SPID_ENTE_RILASCIO.'<br/>';
//echo '15 =>'.$this->SPID_DATA_RILASCIO.'<br/>';
//echo '16 =>'.$this->SPID_DATA_SCADENZA.'<br/>';
//echo '17 =>'.$this->INFO_PEC.'<br/>';
//echo '18 =>'.$this->RECAPITO_TELEFONICO.'<br/>';
//echo '19 =>'.$this->DATA_APERTURA_SEDE_LEGALE.'<br/>';
//echo '20 =>'.$this->ISTITUTO_DI_CREDITO.'<br/>';
//echo '21 =>'.$this->FILIALE_DI.'<br/>';
//echo '22 =>'.$this->CLUSTER_TOT_VOL_IVA_2018.'<br/>';
//echo '23 =>'.$this->CLUSTER_CONTRIBUTO_PREVISTO.'<br/>';
//echo '24 =>'.$this->TOT_VOL_IVA_2019.'<br/>';
//echo '25 =>'.$this->CONTRIBUTO_DICHIARATO.'<br/>';
//echo '26 =>'.$this->INFO_ADDETTI.'<br/>';
//echo '27 =>'.$this->ADDETTI_DICHIARATI.'<br/>';
//echo '28 =>'.$this->DENOMINAZIONE_IMPRESA.'<br/>';
//echo '29 =>'.$this->SEDEOPERATICA_COMUNE.'<br/>';
//echo '30 =>'.$this->SEDEOPERATICA_PROVINCIA.'<br/>';
//exit();
        $query = $con->prepare($sql);
        $query->bindParam(1, $this->ID);
        $query->bindParam(2, $this->CODICE_FISCALE_LEGALE);
        $query->bindParam(3, $this->CODICE_FISCALE_IMPRESA);
        $query->bindParam(4, $this->IBAN);
        $query->bindParam(5, $this->DOMANDA_JSON, PDO::PARAM_STR, strlen($this->DOMANDA_JSON));
        $query->bindParam(6, $this->STATO);
        $query->bindParam(7, $this->FILEPATH);
        $query->bindParam(8, $this->HASH_FILE);
        //$query->bindParam(9, $this->PROTOCOLLO);
        $query->bindParam(9, $this->ID_SEDEOPERATIVA);
        $query->bindParam(10, $this->SPID_MOBILE_PHONE);
        $query->bindParam(11, $this->SPID_TIPO_DOCUMENTO);
        $query->bindParam(12, $this->SPID_NUMERO_DOCUMENTO);
        $query->bindParam(13, $this->SPID_ENTE_RILASCIO);
        $query->bindParam(14, $this->SPID_DATA_RILASCIO);
        $query->bindParam(15, $this->SPID_DATA_SCADENZA);
        $query->bindParam(16, $this->INFO_PEC);
        $query->bindParam(17, $this->RECAPITO_TELEFONICO);
        $query->bindParam(18, $this->DATA_APERTURA_SEDE_LEGALE);
        $query->bindParam(19, $this->ISTITUTO_DI_CREDITO);
        $query->bindParam(20, $this->FILIALE_DI);
        //$query->bindParam(22, $this->CLUSTER_TOT_VOL_IVA_2018);
        //$query->bindParam(23, $this->CLUSTER_CONTRIBUTO_PREVISTO);
        //$query->bindParam(24, $this->TOT_VOL_IVA_2019);
        $query->bindParam(21, $this->CONTRIBUTO_DICHIARATO);
        $query->bindParam(22, $this->INFO_ADDETTI);
        $query->bindParam(23, $this->ADDETTI_DICHIARATI);
        $query->bindParam(24, $this->DENOMINAZIONE_IMPRESA);
        $query->bindParam(25, $this->SEDEOPERATIVA_COMUNE);
        $query->bindParam(26, $this->SEDEOPERATIVA_PROVINCIA);
        $query->bindParam(27, $this->DECRETO);
        $query->bindParam(28, $this->FILEPATH_DECRETO);
        $query->bindParam(29, $this->DATA_INIZIO_ATTIVITA);
        $query->bindParam(30, $this->VOLUME_AFFARI_DICHIARATO_2018);
        $query->bindParam(31, $this->VOLUME_AFFARI_DICHIARATO);
        $query->bindParam(32, $this->REGIME_FISCALE);
        try {
            $it = parent::_Save($query);
        } catch (Exception $exc) {

            $it['descrizioneErrore'] = $exc->getMessage();
        }
        return $it;
    }

    public function statoDomandaCompletata() {
        global $con, $LoggedAccount;
        $risultato = array();
        $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE CODICE_FISCALE_LEGALE=:codice_fiscale AND STATO=1";
        $query = $con->prepare($sql);
        $query->bindParam(":codice_fiscale", $LoggedAccount->CODICE_FISCALE);
        try {
            $query->execute();
            $it = $query->fetchAll(PDO::FETCH_ASSOC);
            $risultato['esito'] = 1;
            $risultato['dati'] = $it;
        } catch (Exception $exc) {
            $risultato['esito'] = -999;
            $risultato['erroreDescrizione'] = $exc->getMessage();
        }
        return $risultato;
    }

    /**
     * Setta lo stato della domanda a chiusa
     * @global type $con
     * @return type
     */
    public function setDomandaChiusa() {
        global $con;
        $risultato = array();
        $sql = "UPDATE " . self::TABLE_NAME . " SET STATO=5  WHERE ID=:id";
        $query = $con->prepare($sql);
        $query->bindParam(":id", $this->ID);
        try {
            $query->execute();
            $risultato['esito'] = 1;
        } catch (Exception $exc) {
            $risultato['esito'] = -999;
            $risultato['erroreDescrizione'] = $exc->getMessage();
        }
        return $risultato;
    }

    public function PresentaDomanda($id = 0) {
        global $con, $LoggedAccount;
        $res = array();
        if (intval($id) > 0) {
            $sql = "CALL " . self::SP_CLICKDAY . " (?,?,?)";


            // echo $sql."<---sql";
            // echo "--->id".($id)."<---";
            // echo "--->cf".$LoggedAccount->CODICE_FISCALE."<---";
            $result = "";
            $query = $con->prepare($sql);
            $query->bindParam(1, $id);
            $query->bindParam(2, $LoggedAccount->CODICE_FISCALE);
            $query->bindParam(3, $result, PDO::PARAM_INPUT_OUTPUT, 40);
            try {
                $query->execute();
                $res['esito'] = 1;
            } catch (Exception $exc) {
                $res['esito'] = -999;
                echo $exc->getMessage();
                $res['erroreDescrizione'] = $exc->getMessage();
            }
        }
        //Utils::print_array($res);
        return $res;
    }

    /**
     * Restituisce la sede operativa scelta per la presentazione della domanda
     * @param type $arraySediOperative
     * @return type
     */
    public function GetImpresaOperativa($arraySediOperative = array()) {
        $return = array();
        if (count($arraySediOperative) > 0) {
            $return = $arraySediOperative[$this->ID_SEDEOPERATIVA];
        }
        return $return;
    }

    /**
     * Restituisce lo stato della domanda
     * @global type $STATI_DOMANDA
     * @return type
     */
    public function GetStatoDomanda($row) {
        global $STATI_DOMANDA;
        $return = $STATI_DOMANDA[$row['STATO']];
        //$protocolli = DomandeProtocollo::loadFromId($this->ID);

        if ($row['STATO'] == 2) {
            $return .= ($row['PROTOCOLLO_SOTTOMISSIONE'] == "" ? " IN ATTESA DI PROTOCOLLO" : " E PROTOCOLLATA");
        } elseif ($row['STATO'] == 3) {


            if ($row['FILEPATH_CERTIFICATO'] == '') {
                $RICHISTA_AGEVOLAZIONE = " IN ATTESA DELLA DOMANDA DI CONTRIBUTO";
            }

            $return .= $RICHISTA_AGEVOLAZIONE;
        } elseif ($row['STATO'] == 5) {

            $return .= ($row['PROTOCOLLO_EROGAZIONE'] == "" ? " IN ATTESA DI PROTOCOLLO" : " E PROTOCOLLATA");
        }


        return $return;
    }

    public function ModificaDomanda() {
        global $con;
        $res = array();
        if ($this->STATO == 1) {
            $sql = "UPDATE " . self::TABLE_NAME . " SET STATO = 0, FILEPATH = '', HASH_FILE = '' WHERE ID = " . $this->ID;
            $query = $con->prepare($sql);

            try {
                $query->execute();
                $res['esito'] = 1;
            } catch (Exception $exc) {
                $res['esito'] = -999;
                echo $exc->getMessage();
                $res['erroreDescrizione'] = $exc->getMessage();
            }
        } else {
            $res['esito'] = -999;
            $res['erroreDescrizione'] = "Lo stato della domanda non permette le modifica";
        }

        return $res;
    }

    /**
     * Controlla se l'azienda per cui si sta preparando la domanda risulta collegata ad altre domande inserite
     * @global type $con
     * @return type boolean
     */
    public function IsCollegata() {
        global $con;
        $risultato = false;

        $sql = "select 
            count(*) as COLLEGATA             
            from " . self::TABLE_NAME . "  d
            inner join " . Domanda_Imprese::TABLE_NAME . " i on i.CODICE_FISCALE_IMPRESA = d.codice_fiscale_impresa and i.ID_DOMANDA <> :id_domanda
            inner join " . self::TABLE_NAME . "  s on s.ID = i.ID_DOMANDA
            where d.ID = :id  ";
        $query = $con->prepare($sql);
        $query->bindParam(":id_domanda", $this->ID);
        $query->bindParam(":id", $this->ID);

        try {
            $query->execute();
            $it = $query->fetch(PDO::FETCH_ASSOC);
            $risultato = (intval($it['COLLEGATA']) > 0 ? true : false );
        } catch (Exception $exc) {
            //$risultato['erroreDescrizione'] = $exc->getMessage();
        }
        return $risultato;
    }

    /**
     * Controlla se l'azienda per cui si sta preparando la domanda risulta collegata ad altre domande inserite e restituisce tutte le aziende collegate
     * @global type $con
     * @return type array
     */
    public function getCollegate() {
        global $con;
        $it = array();

        $sql = "select 
                d.codice_fiscale_impresa as CFCONTROLLATO , i.id_domanda as DOMANDA_COLLEGATA, 
                s.codice_fiscale_impresa, s.denominazione_impresa 
            from " . self::TABLE_NAME . "  d
            inner join " . Domanda_Imprese::TABLE_NAME . " i on i.CODICE_FISCALE_IMPRESA = d.codice_fiscale_impresa and i.ID_DOMANDA <> :id_domanda
            inner join " . self::TABLE_NAME . "  s on s.ID = i.ID_DOMANDA
            where d.ID = :id  ";
        $query = $con->prepare($sql);
        $query->bindParam(":id_domanda", $this->ID);
        $query->bindParam(":id", $this->ID);

        try {
            $query->execute();
            $it = $query->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $exc) {
            //$risultato['erroreDescrizione'] = $exc->getMessage();
        }
        return $it;
    }

    public function Delete() {
        return parent::_Delete(self::TABLE_NAME, "ID = " . $this->ID);
    }

    /**
     * Elimina l'oggetto corrente dal database e restituisce TRUE se ha successo
     *
     * @return bool Risultato booleano dell'operazione
     */
    public function LogicalDelete() {
        return parent::_LogicalDelete(self::TABLE_NAME, "id = " . $this->id);
    }

    /**
     * Funzione per la visualizzazione dei logs
     * @param type $record
     * @param type $campo
     */
    public function Parse(&$record, $campo = "") {
        $props = get_class_vars(get_class($this));
        switch ($campo) {

            default:
                array_push($record, array("Campo" => $campo, "Valore" => $this->$campo));
                break;
        }
    }

}

/**
 * Description of Domande Inviate
 * Tabella che gestisce il click day
 * @author Anselmo
 */
class DomandeInviate extends DataClass {

    const TABLE_NAME = "DOMANDE_INVIATE";
    const SEQ_NAME = "DOMANDE_INVIATE_ID_SEQ";

    public $ID = 0; // Primary key
    public $ID_DOMANDA = 0;
    public $DATA_PRESENTAZIONE = null;

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            if (isset($src['ID']) && $src['ID'] > 0) {
                $this->_loadAndFillObject($src['ID'], self::TABLE_NAME, $src);
            } else {
                $this->_loadByRow($src, $stripSlashes);
            }
        } elseif (intval($src)) {
            $this->_loadById($src, self::TABLE_NAME, true);
        }

        $this->SchedaPersona = array();
    }

    public function loadFromId($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetchAll(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public static function loadFromIdStato($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetch(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public function statoDomandaCompletata($id_domanda = 0) {
        global $con, $LoggedAccount;
        $risultato = array();
        $risultato['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT COUNT(ID) AS CONTEGGIO FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $it = $query->fetch(PDO::FETCH_ASSOC);
                if ($it['CONTEGGIO'] > 0) {
                    $risultato['esito'] = 1;
                }
            } catch (Exception $exc) {
                $risultato['esito'] = -999;
                $risultato['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $risultato;
    }

    public function Parse(&$record, $campo = "") {
        $props = get_class_vars(get_class($this));
        switch ($campo) {

            default:
                array_push($record, array("Campo" => $campo, "Valore" => $this->$campo));
                break;
        }
    }

}

class DomandeProtocollo extends DataClass {

    const TABLE_NAME = "DOMANDE_PROTOCOLLO";

    public $ID_DOMANDA = 0;
    public $ID_DOCUMENTO = 0;
    public $ANNO_PROTOCOLLO = 0;
    public $NUMERO_PROTOCOLLO = 0;
    public $DATA_PROTOCOLLO = null;
    public $FASE = '';
    public $PROTOCOLLO_JSON = '';

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            $this->_loadByRow($src, $stripSlashes);
        }
    }

    public static function loadFromIdFase($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda AND FASE=1";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetch(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public function loadFromId($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetchAll(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public function Parse(&$record, $campo = "") {
        $props = get_class_vars(get_class($this));
        switch ($campo) {

            default:
                array_push($record, array("Campo" => $campo, "Valore" => $this->$campo));
                break;
        }
    }

}

class DomandeAmmesse extends DataClass {

    const TABLE_NAME = "DOMANDE_AMMESSE";

    public $ID_DOMANDA = 0;
    public $CUP = '';
    public $NUMERO_DECRETO = '';
    public $DATA_DECRETO = '';
    public $IBAN = '';
    public $ISTITUTO_DI_CREDITO = '';
    public $FILIALE_DI = '';
    public $MOTIVAZIONE_CAMBIO = '';
    public $FILEPATH_CERTIFICATO = '';
    public $IMPORTO_RICHIESTO = 0;
    public $CAMBIO_ISTITUTO = 0;
    public $FILEPATH_EROGAZIONE = 0;
    public $HASHFILE_EROGAZIONE = 0;
    public $FILE_REVISORE_TMP = 0;

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            $this->_loadByRow($src, $stripSlashes);
        } elseif (intval($src)) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID = :id";
            $query = $con->prepare($sql);
            $query->bindParam(":id", $src);
            try {
                $query->execute();
                $it = $query->fetchAll(PDO::FETCH_ASSOC);
                Utils::FillObjectFromRow($this, $it[0]);
            } catch (Exception $exc) {
                $exc->getMessage();
            }
        }
    }

    public function loadFromId($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetch(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public function Parse(&$record, $campo = "") {
        $props = get_class_vars(get_class($this));
        switch ($campo) {

            default:
                array_push($record, array("Campo" => $campo, "Valore" => $this->$campo));
                break;
        }
    }

    public function Save() {
        global $con;
        $vars = get_object_vars($this);
//        Utils::print_array($vars);
        $sql = Utils::prepareQuery(self::TABLE_NAME, $vars, 'ID_DOMANDA');
        $query = $con->prepare($sql);
        foreach ($vars as $k => $v) {
            if ($k == "ID_DOMANDA" && $v == 0) {
                continue;
            }
            $query->bindValue(":" . $k, $v);
        }

        try {
            $it = parent::_Save($query);
        } catch (Exception $exc) {
            $it['descrizioneErrore'] = $exc->getMessage();
        }
        return $it;
    }

}

class DomandaAteco extends DataClass {

    const TABLE_NAME = "DOMANDA_ATECO";
    const SEQ_NAME = "DOMANDA_ATECO_ID_SEQ";

    public $ID = 0;
    public $ID_DOMANDA = 0;
    public $ATECO = '';
    public $CF_IMPRESA = '';

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            $this->_loadByRow($src, $stripSlashes);
        } elseif (intval($src)) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID = :id";
            $query = $con->prepare($sql);
            $query->bindParam(":id", $src);
            try {
                $query->execute();
                $it = $query->fetchAll(PDO::FETCH_ASSOC);
                Utils::FillObjectFromRow($this, $it[0]);
            } catch (Exception $exc) {
                $exc->getMessage();
            }
        }
    }

    public function loadFromId($id_domanda = 0) {
        global $con, $LoggedAccount;
        $return = array();
        $return['esito'] = -999;
        if (intval($id_domanda) > 0) {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA=:id_domanda";
            $query = $con->prepare($sql);
            $query->bindParam(":id_domanda", $id_domanda);
            try {
                $query->execute();
                $return = $query->fetch(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $return['esito'] = -999;
                $return['erroreDescrizione'] = $exc->getMessage();
            }
        }
        return $return;
    }

    public function Parse(&$record, $campo = "") {
        $props = get_class_vars(get_class($this));
        switch ($campo) {

            default:
                array_push($record, array("Campo" => $campo, "Valore" => $this->$campo));
                break;
        }
    }

    public function Save() {
        global $con;
        $vars = get_object_vars($this);
//        Utils::print_array($vars);
        $sql = Utils::prepareQuery(self::TABLE_NAME, $vars);

        $query = $con->prepare($sql);
        foreach ($vars as $k => $v) {
            if ($k == "ID" && $v == 0) {
                continue;
            }
            $query->bindValue(":" . $k, $v);
        }

        try {
            $it = parent::_Save($query);
        } catch (Exception $exc) {
            $it['descrizioneErrore'] = $exc->getMessage();
        }
        return $it;
    }

}

class DomandaAttivita extends DataClass {

    const TABLE_NAME = 'DOMANDA_ATTIVITA';
    const SP_ = 'SPDOMANDATTIVITASAVE';

    public $ID_DOMANDA = 0;
    public $CF_IMPRESA = '';
    public $C_STATO_ATTIVITA = '';

    public function __construct($src = null) {
        global $con;
        if ($src == null)
            return;
        if (is_array($src)) {
            if (isset($src['ID_DOMANDA']) && $src['ID_DOMANDA'] != "") {
                $this->_loadAndFillObject($src['ID_DOMANDA'], self::TABLE_NAME, $src);
            } else {
                $this->_loadByRow($src, $stripSlashes);
            }
        } else {
            $sql = "SELECT * FROM " . self::TABLE_NAME . " WHERE ID_DOMANDA = :id";
            $query = $con->prepare($sql);
            $query->bindParam(":id", $src);
            try {
                $query->execute();
                $it = $query->fetchAll(PDO::FETCH_ASSOC);
            } catch (Exception $exc) {
                $exc->getMessage();
            }
        }
    }

    public function Save() {
        global $con;
        $vars = get_object_vars($this);
        $return = array();
        $sql = "CALL " . self::SP_ . " (?,?,?)";
        $query = $con->prepare($sql);
        $query->bindParam(1, $this->ID_DOMANDA);
        $query->bindParam(2, $this->CF_IMPRESA);
        $query->bindParam(3, $this->C_STATO_ATTIVITA);

        try {
            if ($it = $query->execute()) {
                $return['esito'] = 1;
            } else {
                $return['esito'] = -999;
            }
        } catch (Exception $exc) {
            $return['esito'] = -999;
            $return['descrizioneErrore'] = $exc->getMessage();
        }

        return $return;
    }

}