<?php
class SaftModel extends Database
{

	protected function insertSaft($nome, $nota)
	{
		$stmt = $this->connect();
		$sql = "INSERT INTO Saft (nome, nota) VALUES (?, ?)";
		$query = $stmt->prepare($sql);
		if (!($query->execute(array($nome, $nota)))) {
			$query = null;
			exit();
		}
		$id = $stmt->lastInsertId();
		return $id;
	}

	public function queryEmpresa()
	{
		$data = null;
		$sql = "SELECT * FROM configuracao";
		if ($stmt = $this->connect()->query($sql)) {
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryDefinicao()
	{
		$data = null;
		$sql = "SELECT * FROM definicao";
		if ($stmt = $this->connect()->query($sql)) {
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		$serie = $this->querySerie($data[2]['valor']);
		$pais = $this->queryPais($data[3]['valor']);
		$moeda = $this->queryMoeda($data[4]['valor']);
		return ["pais" => $pais[0]["designacao"], "serie" => $serie[0]["descricao"], "moeda" => $moeda[0]["codigo"], "tipoPrograma" => $data[5]['valor']];
	}

	public function querySerie($idSerie)
	{
		$data = null;
		$sql = "SELECT * FROM serie WHERE idSerie = ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$idSerie]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryPais($idPais)
	{
		$data = null;
		$sql = "SELECT * FROM pais WHERE idPais = ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$idPais]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryMoeda($idMoeda)
	{
		$data = null;
		$sql = "SELECT * FROM moeda WHERE idMoeda = ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$idMoeda]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryEntidade($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT DISTINCT e.idEntidade, e.* FROM entidade e INNER JOIN documento d ON (e.idEntidade = d.idEntidade) INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE e.idTipoEntidade = 2 AND td.saft = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryArtigo($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT DISTINCT a.idArtigo, a.* FROM artigodocumento ad INNER JOIN documento d ON (ad.idDocumento = d.idDocumento) INNER JOIN artigo a ON (ad.idArtigo = a.idArtigo) INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) INNER JOIN entidade e ON (d.idEntidade = e.idEntidade) WHERE e.idTipoEntidade = 2 AND td.saft = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryTipoTaxa()
	{
		$data = null;
		$sql = "SELECT * FROM viewimposto";
		if ($stmt = $this->connect()->query($sql)) {
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function countDocumento($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT COUNT(*) AS contador FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.saft = 1 AND NOT td.idTipoDoc = 3 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function countRecibo($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT COUNT(*) AS contador FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.saft = 1 AND td.idTipoDoc = 3 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function sumDebito($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT SUM(totalLiquido) AS soma FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.idTipoDoc IN (7) AND estadoEmissao = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function sumDebitoRecibo($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT SUM(troco) AS soma FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.idTipoDoc IN (3) AND estadoEmissao = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function sumCredito($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT SUM(totalLiquido) AS soma FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.idTipoDoc IN (1, 2, 8) AND estadoEmissao = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function sumCreditoRecibo($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT SUM(totalLiquido) AS soma FROM documento d INNER JOIN tipoDoc td ON (d.idTipoDoc = td.idTipoDoc) WHERE td.idTipoDoc IN (3) AND estadoEmissao = 1 AND DATE(d.dataDoc) BETWEEN ? AND ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function hash($hash)
	{
		$data = null;
		$sql = "SELECT * FROM documento WHERE idDocumento = ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$hash]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function documento($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT * FROM viewartigomodopagamentodocumento WHERE tdSaft = 1 AND NOT idTipoDoc = 3 AND DATE(dataDoc) BETWEEN ? AND ? ORDER BY idDocumento";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function recibo($dataInicial, $dataFinal)
	{
		$data = null;
		$sql = "SELECT * FROM viewartigomodopagamentodocumento WHERE tdSaft = 1 AND idTipoDoc = 3 AND DATE(dataDoc) BETWEEN ? AND ? ORDER BY idDocumento";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$dataInicial, $dataFinal]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function queryDoc($codigo){
		$data = null;
		$sql = "SELECT * FROM viewartigomodopagamentodocumento WHERE codigoDocumento = ?";
		if($stmt = $this->connect()->prepare($sql)){
			$stmt->execute([$codigo]);
			while($row = $stmt->fetch()){
			$data[] = $row;
			}
		}
		return $data;
	}

	public function queryDocumentoFac($codigo, $idTipoDocumento){
		$data = null;
		$sql = "SELECT * FROM viewartigomodopagamentodocumento vd INNER JOIN docrelac dr ON (vd.idDocumento = dr.idDocRelac) WHERE dr.idDocumento = ? AND idTipoDoc = ?";
		if($stmt = $this->connect()->prepare($sql)){
			$stmt->execute([$codigo, $idTipoDocumento]);
			while($row = $stmt->fetch()){
			$data[] = $row;
			}
		}
		return $data;
	}

	public function linhaDocumento($idDocumento)
	{
		$data = null;
		$sql = "SELECT DISTINCT ad.*, ct.*, tt.*, r.*, i.*, tt.codigo AS tipoTaxatt, ad.descricao AS descricaoAd, a.nome AS nomeA, a.nomeCurto AS nomeCurtoA, a.codigoBarra AS codigoBarraA, um.nome AS nomeUm, um.sigla AS siglaUm,
        um.tipo AS tipoUm, um.casaDecimal AS casaDecimalUm, DATE(ad.dataDocumento) AS dataDocumentoAd FROM artigoDocumento ad INNER JOIN artigo a ON (ad.idArtigo = a.idArtigo) 
        INNER JOIN unidadeMedida um ON (a.idUnidadeMedida = um.idUnidadeMedida) INNER JOIN taxa t ON (ad.idImposto = t.idImposto) INNER JOIN codigoTaxa ct ON
        (t.idCodigoTaxa = ct.idCodigoTaxa) INNER JOIN tipoTaxa tt ON (t.idTipoTaxa = tt.idTipoTaxa) INNER JOIN regiao r ON (t.idRegiao = r.idRegiao) INNER JOIN pais p ON (r.idPais = p.idPais)
        INNER JOIN imposto i ON (t.idImposto = i.idImposto) WHERE ad.idDocumento = ? GROUP BY a.idArtigo";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$idDocumento]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	public function modoPagamento($idDocumento)
	{
		$data = null;
		$sql = "SELECT * FROM viewmodopagamentodocumento WHERE idDocumento = ?";
		if ($stmt = $this->connect()->prepare($sql)) {
			$stmt->execute([$idDocumento]);
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}


	public function listaTipoTaxa()
	{
		$data = null;
		$sql = "SELECT * FROM tipotaxa";
		if ($stmt = $this->connect()->query($sql)) {
			while ($row = $stmt->fetch()) {
				$data[] = $row;
			}
		}
		return $data;
	}

	protected function editSaft($idSaft, $nome, $nota)
	{
		$stmt = $this->connect();

		$sql = "UPDATE Saft SET nome = ?, nota = ? WHERE idSaft = ?";
		$query = $stmt->prepare($sql);

		if (!($query->execute(array($nome, $nota, $idSaft)))) {
			$query = null;
			exit();
		}
		$id = $stmt->lastInsertId();
		return $id;
	}

	public function deleteSaft($idSaft)
	{
		$stmt = $this->connect();
		$sql = "DELETE FROM Saft WHERE idSaft = ?";
		$query = $stmt->prepare($sql);
		if (!($query->execute(array($idSaft)))) {
			$query = null;
			exit();
		}
	}

	public function autoIncrementSaft()
	{
		$sql = "SELECT `AUTO_INCREMENT` AS contador
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'test1234'
			AND TABLE_NAME = 'Saft';";
		$stmt = $this->connect()->query($sql);
		$results = $stmt->fetch();
		return $results['contador'];
	}
}
