<?php namespace App\Models;


/*
    
    CREATE TABLE IF NOT EXISTS `Stime` (
      `stIDcommessa` int NOT NULL AUTO_INCREMENT COMMENT 'Identificativo univoco della commessa a cui si riferisce la stima',
      `stIDTipoWork` varchar(5) NOT NULL COMMENT 'Identificativo univoco della tipologia di lavoro a cui si riferisce la stima',
      `stStima` int NOT NULL DEFAULT '0' COMMENT 'Stima (in ore) del tempo necessario allo svolgimento del lavoro per la commessa, fatta in fase di offerta.',
      `stNota` text NOT NULL COMMENT 'Eventuale annotazione sulla stima',
      PRIMARY KEY (`stIDcommessa`,`stIDTipoWork`),
      KEY `FK_Stime-Commessa` (`stIDcommessa`),
      KEY `FK_Stime-TipiWork` (`stIDTipoWork`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Elenco delle stime fatte per la commessa';

    Fields:
  Tab:  Stime  =  ['stIDcommessa', 'stIDTipoWork', 'stStima', 'stNota'];

*/


use CodeIgniter\Model;


class Stime_Model extends Model{


	//--------------------------------------------------------------------
    // Property


    protected $table = 'Stime';
    //protected $primaryKey = 'stIDcommessa';

    protected $returnType     = 'App\Entities\Stima';
    //protected $returnType     = 'array';

    protected $allowedFields = ['stIDcommessa', 'stIDTipoWork', 'stStima', 'stNota'];

    protected $useSoftDeletes = false;
    protected $useTimestamps = false;
    //protected $createdField  = 'spCreatedAt';
    //protected $updatedField  = 'spUpdatedAt';
    //protected $deletedField  = 'spDeletedAt';


	//--------------------------------------------------------------------
    // Events


    //protected $beforeInsert = ['beforeInsert'];
    //protected $afterInsert = ['afterInsert'];
    //protected $beforeUpdate = ['beforeUpdate'];
    //protected $afterUpdate = ['afterUpdate'];
    //protected $beforeDelete = ['beforeDelete'];
    //protected $afterDelete = ['afterDelete'];
    //protected $afterFind = ['afterFind'];


	//--------------------------------------------------------------------
    // Methods


	//--------------------------------------------------------------------


    // Ritorna i dati per il report della commessa indicata
    public function get_ReportByCommessa($ID){
      $query = $this->query("
                              SELECT
                                twIDTipoWork AS IDTipoWork,
                                twDescrizione AS Descrizione,
                                (SELECT sum(stStima) FROM `Stime` WHERE stIDcommessa='$ID' AND stIDTipoWork=twIDTipoWork) AS Stima,
                                (SELECT sum(orOre) FROM `Orari` WHERE orIDcommessa='$ID' AND orIDTipoWork=twIDTipoWork) AS TotOre,
                                (SELECT stNota FROM `Stime` WHERE stIDcommessa='$ID' AND stIDTipoWork=twIDTipoWork) AS Nota
                              FROM `TipiWork`
                              ORDER BY twOrdinamento
                          ");
      return $query->getResult();
    }


  //--------------------------------------------------------------------


	//--------------------------------------------------------------------


}//class Stime_Model