<?PHP

class User {
	
	private $anaUteRawData;
	private $utApGoogleRawData;
	
	private $utposiz;
	private $iuteid;
	
	private $allIuteIds;		//Array contenente tutti gli iuteid relativi alla coppia utposiz-ututent
	
	private $ututent;
	private $utdatna;
	private $utcodco;
	private $utpiva;
	private $utcodfi;
	private $utsitco;
	private $ut_sito;
	private $utsitci;
	private $utcateg;
	private $utcalfo;
	private $utaldep;
	private $utminim;
	private $utncond;
	private $utmatco;
	private $utdatfi;
	private $tipouten;
	
	private $matricolaMisAggiornata;
	
	private $decodedUtCodCo;
	private $decodedUtSitCo;
	private $decodedUt_Sito;
	private $decodedTariffa;
	private $causalePagamento;
	
	private $utApIdPro;
	private $utDatReg;
	private $utIdGoogle;
	private $utApLogged;
	private $utIdDevice;
	private $utApEmail;
	private $utApEmailLink;
	
	private $subUsers;
	
	private $notices;
	
	private $messaggiDifferiti;
	
	private $bollette;
	
	private $lastLettura;
	private $letture;
	private $codiceOperatoreAutolettura;
	
	private $cn;
	
	function __construct($iuteid,$cn) {
		$this->cn = $cn;
		$this->iuteid = $iuteid;
		
		$this->fetchBasicAnaUteInformation();
	}
	
	private function fetchBasicAnaUteInformation() {
		$query = "SELECT * FROM ANA__UTE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		$this->utposiz = $row["UTPOSIZ"];
		$this->ututent = $row["UTUTENT"];
		
		$query = "SELECT MAX(iuteid) as idute FROM ANA__UTE where UTPOSIZ=".$this->utposiz." AND UTUTENT LIKE '".str_replace('"',"?",str_replace("'","?",$this->ututent))."'";
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		$this->iuteid = $row["idute"];
		
		$query = "SELECT * FROM ANA__UTE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		
		$this->utposiz = $row["UTPOSIZ"];
		$this->ututent = $row["UTUTENT"];
		$this->utdatna = $row["UTDATNA"];
		$this->utcodco = $row["UTCODCO"];
		$this->utpiva = $row["UTPIVA"];
		$this->utcodfi = $row["UTCODFI"];
		$this->utsitco = $row["UTSITCO"];
		$this->ut_sito = $row["UT_SITO"];
		$this->utsitci = $row["utsitci"];
		$this->utcateg = $row["UTCATEG"];
		$this->utcalfo = $row["UTCALFO"];
		$this->utaldep = $row["UTALDEP"];
		$this->utminim = $row["UTMINIM"];
		$this->utncond = $row["UTNCOND"];
		$this->utmatco = $row["UTMATCO"];
		$this->utdatfi = $row["UTDATFI"];
		
		$this->fetchAllIuteIds();
		$this->fetchMatricolaAggiornata();
	}
	
	private function fetchMatricolaAggiornata() {
        $query = "SELECT CNMATCO FROM MOV__CON where CNFLAG='A' AND CNPOSIZ=".$this->utposiz;
        $result = odbc_exec($this->cn, $query);
         
        $row = odbc_fetch_array($result);
        
        $this->matricolaMisAggiornata = $row["CNMATCO"];
	}
	
	private function fetchAllIuteIds() {
		$query = "SELECT iuteid as idute FROM ANA__UTE where UTPOSIZ=".$this->utposiz." AND UTUTENT LIKE '".str_replace('"',"?",str_replace("'","?",$this->ututent))."'";
		$result = odbc_exec($this->cn, $query);
		
		while ($row = odbc_fetch_array($result)) {
			$this->allIuteIds[] = $row["idute"];
		}
	}
	
	public function fetchBasicUtApGoogleInformation() {
		$query = "SELECT * FROM UTAPGOOGLE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		$this->utApIdPro = $row["UTAPIDPRO"];
		$this->utDatReg = $row["UTDATREG"];
		$this->utIdGoogle = $row["UTIDGOOGLE"];
		$this->utApLogged = $row["UTAPLOGGED"];
		$this->utIdDevice = $row["UTIDDEVICE"];
		$this->utApEmail = $row["UTAPEMAIL"];
		$this->utApEmailLink = $row["UTAPEMAILLINK"];
	}
	
	public function fetchLastLettura() {
		$query = "SELECT * FROM MOV__LET WHERE LTPOSIZ=".$this->utposiz." AND (LTFLAG != 'X' OR LTFLAG IS NULL) ORDER BY LTIDPRO DESC";
		$result = odbc_exec($this->cn,$query);
		
		$row = odbc_fetch_array($result);
		
		$this->lastLettura = new Lettura($row,$this->cn);
	}
	
	public function fetchLetture() {
		$query = "SELECT * FROM MOV__LET WHERE LTPOSIZ=".$this->utposiz." ORDER BY LTDATA DESC, LTORARIO DESC";
		$result = odbc_exec($this->cn,$query);
		
		while ($row = odbc_fetch_array($result)) {
			$this->letture[] = new Lettura($row,$this->cn);
		}
	}
	
	public function fetchCodiceOperatoreAutolettura() {
		$query = "SELECT Codi27 FROM ANA__AZI";
		$result = odbc_exec($this->cn,$query);
		
		$row = odbc_fetch_array($result);
		
		$this->codiceOperatoreAutolettura = $row["Codi27"];
	}
	
	public function fetchSubUsers() {
		$query = "SELECT IUTEIDSUB FROM UTAPGOOGLESUB where IUTEID=".$this->iuteid;
		$result = odbc_exec($this->cn,$query);
		
		while ($row = odbc_fetch_array($result)) {
			$subuser = new User($row["IUTEIDSUB"],$this->cn);
			$this->subUsers[] = $subuser;
		}
	}
	
	public function fetchBollette() {
		
		$iutelist = implode(",",$this->allIuteIds);
		
		//FARE UPDATE CAMPI BLTOTBO,BLCREDIT,BLPAGATO A 0 SE VALORE NULL
		
		$query = "UPDATE TESTBOLL SET BLCREDIT=0 WHERE (BLIDUTE IN (".$iutelist.")) AND (BLCREDIT IS NULL)";
		$result = odbc_exec($this->cn,$query);
		$query = "UPDATE TESTBOLL SET BLPAGATO=0 WHERE (BLIDUTE IN (".$iutelist.")) AND (BLPAGATO IS NULL)";
		$result = odbc_exec($this->cn,$query);
		
//		$query = "SELECT * FROM TESTBOLL WHERE (BLIDUTE IN (".$iutelist.")) AND ((BLTOTBO-BLCREDIT-BLPAGATO)>=0.001) AND (BLNUMFA is not null) AND (BLDATEM is not null) ORDER BY BLDATSC ASC";
		$query = "SELECT * FROM TESTBOLL WHERE (BLIDUTE IN (".$iutelist.")) AND (BLNUMFA is not null) AND (BLDATEM is not null) ORDER BY BLDATSC DESC";
		$result = odbc_exec($this->cn,$query);
		
		while ($row = odbc_fetch_array($result)) {
			$this->bollette[] = new Bolletta($row,$this->cn);
		}
	}
	
	public function getBollette() {
		return $this->bollette;
	}
	
	public function getSingolaBolletta($blidpro) {
		$query = "SELECT * FROM TESTBOLL WHERE BLIDPRO=".$blidpro;
		$result = odbc_exec($this->cn,$query);
		
		$row = odbc_fetch_array($result);
		
		return new Bolletta($row,$this->cn);
	}
	
	public function fetchMessaggiDifferiti() {
		$query = "SELECT * FROM MOV__MSG WHERE iuteid=".$this->iuteid." AND SPEDITOMSG=2 ORDER BY MSGIDPRO DESC";
		$result = odbc_exec($this->cn,$query);
		
		while ($row = odbc_fetch_array($result)) {
			$this->messaggiDifferiti[] = new Notice($row,$this->cn);
		}
	}
	
	public function getMessaggiDifferiti() {
		return $this->messaggiDifferiti;
	}
	
	public function getMessaggiDifferitiCount() {
		return count($this->messaggiDifferiti);
	}
	
	public function getSubUsers() {
		return $this->subUsers;
	}
	
	public function getSubUsersCount() {
		return count($this->subUsers);
	}
	
	public function removeSubUser($appcode,$usercode) {
		$query = "DELETE FROM UTAPGOOGLESUB WHERE IUTEIDSUB=".$appcode." AND UTAPSUBPOSIZ=".$usercode." AND IUTEID=".$this->iuteid;
		$result = odbc_exec($this->cn,$query);
		
		return $result;
	}
	
	public function getLastLettura() {
		return $this->lastLettura;
	}
	
	public function getLetture() {
		return $this->letture;
	}
	
	public function getCodiceOperatoreAutolettura() {
		return $this->codiceOperatoreAutolettura;
	}
	
	public function getAllIuteIds() {
		return $this->allIuteIds;
	}
	
	public function registerNewAutoLettura($newlettura,$matricolamisuratore,$correct,$consumo,$media) {
		$query = 'SELECT Codi29 FROM ANA__AZI';
		$result = odbc_exec($this->cn,$query);
		$row = odbc_fetch_array($result);
		
		$ltCautent = $row["Codi29"];
		
		$ltCauFall = null;
		
		if ($correct == 0) {
			$query = 'SELECT Codi30 FROM ANA__AZI';
			$result = odbc_exec($this->cn,$query);
			$row = odbc_fetch_array($result);
		
			$ltCauFall = $row["Codi30"];
		}
		else if ($correct == 1) {
			$query = 'SELECT Cod38 FROM ANA__AZI';
			$result = odbc_exec($this->cn,$query);
			$row = odbc_fetch_array($result);
		
			$ltCauFall = $row["Cod38"];
		}
		
		$correctMessageString = "Autolettura registrata con successo con valore: ".$newlettura;
		$uncorrectMessageString = "L'ultima autolettura rilevata, con valore ".$newlettura.", risulta inferiore alla precedente ed e stata registrata come non valida";
		$fuorimediaMessageString = "Il consumo rilevato (".$consumo.") supera il valore medio previsto (".(int)$media.") e la lettura e stata registrata come Autolettura fallita. Verificare il valore inserito e-o contattare l ufficio";
		
		$query = 'INSERT INTO MOV__LET (LTPOSIZ,LTDATA,LTLETMC,LTMATRI,LTCODOP,LTFLAG,LTORARIO,LTCAUTENT,LTCAUFALL) VALUES ('.$this->utposiz.',$now(),'.$newlettura.',"'.$matricolamisuratore.'","'.$this->codiceOperatoreAutolettura.'","'.($correct == 2?"A":"X").'",$now(),'.$ltCautent.','.$ltCauFall.')';
		$result = odbc_exec($this->cn,$query);
		
		//$query = 'SELECT MAX(LTIDPRO) AS ltid FROM MOV__LET WHERE LTPOSIZ='.$this->utposiz;
		//$result = odbc_exec($this->cn,$query);
		//$row = odbc_fetch_array($result);
		
		//$lastId = $row["ltid"];

		//$query = 'INSERT INTO ACC__LET (LTIDPRO,LTORARIO,LTCAUFALL,LTCAUTENT) VALUES('.$lastId.',$now(),'.$ltCauFall.','.$ltCautent.')';
		//$result = odbc_exec($this->cn,$query);
		
		$mesString;
		
		if ($correct == 0) { $mesString = $uncorrectMessageString; }
		else if ($correct == 1) { $mesString = $fuorimediaMessageString; }
		else if ($correct == 2) { $mesString = $correctMessageString; }
		
		$query = 'INSERT INTO MOV__MSG (IUTEID,DATAMSG,TESTOMSG) VALUES("'.$this->iuteid.'",$now(),"'.$mesString.'")';
		$result = odbc_exec($this->cn,$query);
		
		return $result;
	}

	public function exist() {
		$userExist = false;
		
		$query = "SELECT Count(*) AS counter FROM ANA__UTE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		if ($row['counter'] > 0) { $userExist = true; }
		
		return $userExist;
	}
	
	public function existUtenza($usercode,$appcode) {
		$userExist = false;
		
		$query = "SELECT Count(*) AS counter FROM ANA__UTE where iuteid=".$appcode." AND utposiz=".$usercode;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		if ($row['counter'] > 0) { $userExist = true; }
		
		return $userExist;
	}
	
	public function addUtenza($iuteidsub,$subposiz) {
		$query = "SELECT Count(*) AS counter FROM UTAPGOOGLESUB where IUTEIDSUB=".$iuteidsub." AND UTAPSUBPOSIZ=".$subposiz;
		$result = odbc_exec($this->cn,$query);

		$row = odbc_fetch_array($result);

		if ($row['counter'] < 1) {
			$query = "INSERT INTO UTAPGOOGLESUB (IUTEIDSUB,IUTEID,UTAPSUBPOSIZ) VALUES (".$iuteidsub.",".$this->iuteid.",".$subposiz.")";
			$result = odbc_exec($this->cn,$query);
			
		}

		return $result;
	}
	
	public function isVerified($userUtPosiz) {
		$isverified = false;
		
		if ($userUtPosiz === $this->utposiz) { $isverified = true; }
		
		return $isverified;
	}
	
	public function existInAppTable() {
		$userExist = false;
		
		$query = "SELECT Count(*) AS counter FROM UTAPGOOGLE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		if ($row['counter'] > 0) { $userExist = true; }
		
		return $userExist;
	}
	
	public function existInSubAppTable() {
		$userExist = false;
		
		$query = "SELECT Count(*) AS counter FROM UTAPGOOGLESUB where IUTEIDSUB=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		if ($row['counter'] > 0) { $userExist = true; }
		
		return $userExist;
	}
	
	public function getMainAppCodeFromSubTable() {
		$query = "SELECT IUTEID FROM UTAPGOOGLESUB where IUTEIDSUB=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		$mainAppCode = $row["IUTEID"];
		
		return $mainAppCode;
	}
	
	public function insertIntoUtApGoogleTable($dateTime) {
		$query = 'INSERT INTO UTAPGOOGLE (iuteid, utdatreg) VALUES ("'.$this->iuteid.'",$now())';
		$result = odbc_exec($this->cn, $query);
		
		return $result;
	}
	
	public function registerIncasso($importo,$idpro,$transactionid) {
		
		$query = 'Incasso('.$this->causalePagamento.','.$importo.',$now(),$now(),'.$this->iuteid.','.$idpro.','.$this->codiceOperatoreAutolettura.',"'.$transactionid.'");';
		$result = odbc_exec($this->cn,$query);
		
		return $result;
	}

	public function hasRegistrationDate() {
		$hasRegistrationDate = false;
		
		if (!empty($this->utDatReg) && $this->utDatReg != "null") { $hasRegistrationDate = true; }
		
		return $hasRegistrationDate;
	}
	
	public function updateRegistrationDate($registrationDate) {
		$query = "UPDATE UTAPGOOGLE SET UTDATREG=".$registrationDate." WHERE iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		return $result;
	}
	
	public function updateEmail($email) {
		$emaillink = md5($email);
		
		$query = "UPDATE UTAPGOOGLE SET UTAPEMAIL='".$email."',UTAPEMAILLINK='".$emaillink."' WHERE iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		return $result;
	}
	
	public function setLoginStatus($status) {
		$query = "UPDATE UTAPGOOGLE SET UTAPLOGGED=".$status." WHERE iuteid=".$this->iuteid;
		$cleanQuery = 
		$result = odbc_exec($this->cn, $query);

		return $result;
	}

	public function getLoginStatus() { return ($this->utApLogged > 0 ? "logged" : "notlogged"); }
	
	public function updateUtIdGoogle($googleToken) {
		$query = "UPDATE UTAPGOOGLE SET UTIDGOOGLE='".$googleToken."' WHERE iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);

		return $result;
	}
	
	public function getUtIdGoogle() {
		return $this->utIdGoogle;
	}
	
	public function updateUtIdDevice($utiddevice) {
		$query = "UPDATE UTAPGOOGLE SET UTIDDEVICE='".$utiddevice."' WHERE iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);

		return $result;
	}
	
	public function getUtIdDevice() {
		return $this->utIdDevice;
	}
	
	public function getUtUtent() {
		return $this->ututent;
	}
	
	public function getUtPosiz() {
		return $this->utposiz;
	}
	
	public function getAppCode() {
		return $this->iuteid;
	}

	public function getBirthdate() {
		return $this->utdatna;
	}
	
	public function getUtnCond() {
		return $this->utncond;
	}
	
	public function getBirthCity() {
		return $this->decodedUtCodCo;
	}
	
	public function getPartitaIva() {
		return $this->utpiva;
	}

	public function getFiscalCode() {
		return $this->utcodfi;
	}

	public function getUbicazioneFornitura() {
		return $this->decodedUtSitCo;
	}
	public function getIndirizzoFornitura() {
		return $this->decodedUt_Sito;
	}
	public function getCivicoFornitura() {
		return $this->utsitci;
	}
	public function getTariffa() {
		return $this->decodedTariffa;
	}
	public function getFognaAllontanamento() {
		return $this->utcalfo;
	}
	public function getFognaDepurazione() {
		return $this->utaldep;
	}
	public function getMinimoContrattuale() {
		return $this->utminim;
	}
	public function getUnitaImmobiliari() {
		return $this->utncond;
	}
	public function getMatricolaMisuratore() {
		return $this->matricolaMisAggiornata;
	}
	
	public function getTipoUten() {
		return $this->tipouten;
	}
	
	public function getUtApEmail() {
		return $this->utApEmail;
	}
	
	public function getUtApEmailLink() {
		return $this->utApEmailLink;
	}
	
	public function hasEmail() {
		$hasEmail = false;
		
		if (!empty($this->utApEmail) && $this->utApEmail != "null") { $hasEmail = true; }
		
		return $hasEmail;
	}
	
	public function hasEmailLink() {
		$hasEmailLink = false;
		
		if (!empty($this->utApEmailLink) && $this->utApEmailLink != "null") { $hasEmailLink = true; }
		
		return $hasEmailLink;
	}
	
	public function emailLinkMatches($emaillink) {
		$matches = false;
		
		if ($emaillink == $this->utApEmailLink) { $matches = true; }
		
		return $matches;
	}
	
	public function fetchNotices() {
		$query = "SELECT * FROM MOV__MSG WHERE iuteid=".$this->iuteid." AND (SPEDITOMSG is not null OR SPEDITOMSG=1) ORDER BY DATAMSG DESC";
		$result = odbc_exec($this->cn,$query);
		
		while ($row = odbc_fetch_array($result)) {
			$this->notices[] = new Notice($row,$this->cn);
		}
	}
	
	public function getNotices() {
		return $this->notices;
	}
	
	public function decodeFields() {
		if (!empty($this->utcodco)) {
			$query = "SELECT CODESCR FROM ANA__COM where COCODIC=".$this->utcodco;
			$result = odbc_exec($this->cn, $query);
			$row = odbc_fetch_array($result);
		
			$this->decodedUtCodCo = $row["CODESCR"];
		}
		
		$query = "SELECT CODESCR FROM ANA__COM where COCODIC=".$this->utsitco;
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		
		$this->decodedUtSitCo = $row["CODESCR"];
		
		$query = "SELECT STDESCR FROM ANA__STR where STCODIC=".$this->ut_sito;
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		
		$this->decodedUt_Sito = $row["STDESCR"];
		
		$query = "SELECT CADESCR FROM MOV__CAT where CACODIC=".$this->utcateg." AND CADATFI='31122029'";
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		
		$this->decodedTariffa = $row["CADESCR"];
		
		$query = "SELECT Codi28 FROM ANA__AZI";
		$result = odbc_exec($this->cn, $query);
		$row = odbc_fetch_array($result);
		
		$this->causalePagamento = $row["Codi28"];
		
		$query = "SELECT CACORMIN FROM MOV__CAT WHERE CACODIC=".$this->utcateg." AND CADATFI>='".$this->utdatfi."' AND CADATIN<='".$this->utdatfi."'";
		$result = odbc_exec($this->cn,$query);
		$row = odbc_fetch_array($result);

		if ((int)$row["CACORMIN"] == 1) { $this->utminim = "No"; }
		
		$query = "SELECT TIPOUTEN FROM MOV__CAT WHERE CACODIC=".$this->utcateg." AND CADATFI>='".$this->utdatfi."' AND CADATIN<='".$this->utdatfi."'";
		$result = odbc_exec($this->cn,$query);
		$row = odbc_fetch_array($result);
		
		$this->tipouten = $row["TIPOUTEN"];
	}
	
	public function fetchAnaUteRawData() {
		$query = "SELECT * FROM ANA__UTE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		$this->anaUteRawData = $row;
	}
	
	public function getAnaUteRawData() {
		return $this->anaUteRawData;
	}

	public function fetchUtApGoogleRawData() {
		$query = "SELECT * FROM UTAPGOOGLE where iuteid=".$this->iuteid;
		$result = odbc_exec($this->cn, $query);
		
		$row = odbc_fetch_array($result);
		
		$this->utApGoogleRawData = $row;
	}
	
	public function getUtApGoogleRawData() {
		return $this->utApGoogleRawData;
	}
	
}

?>