<?php
/**
*
* Classe Back-Side do Aplicativo Android
* @author Célio Rodrigues
* @version 0.1 alpha
*
*/

class App{

	private $mysql;
	private $gcm_key;
	private $gcm_url;

	function __construct(){
		$this->mysql = mysqli_connect('localhost', 'goulart_adm', '!L!oLdq2p%(x', 'goulart_crm_2014') or die(mysqli_error());
		$this->gcm_key = 'AIzaSyAFAl1qT3QPnwG9xbw9QQHnPInCdNJv4Bk';
		$this->gcm_url = 'https://gcm-http.googleapis.com/gcm/send';
	}

	private function remove_id( $id ){
		$select = mysqli_query($this->mysql, "DELETE FROM phones WHERE registration_id = '".$id."' ");
		if($select){
			return true;
		}else{
			return false;
		}
	}
	private function update_id( $before, $after ){
		$select = mysqli_query($this->mysql, "UPDATE phones SET registration_id = '".$after."' WHERE registration_id = '".$before."' ");
		if($select){
			return true;
		}else{
			return false;
		}
	}
	private function getAll(){
		$select = mysqli_query($this->mysql, "SELECT * FROM phones");
		$arr = array();
		while($linha = mysqli_fetch_assoc($select)){
			$arr[] = $linha['registration_id'];
		}
		return $arr;
	}

	private function dataFormatada($data) {
		$dataBR = date("d/m/Y", strtotime($data));
		return $dataBR;
	}

	public function register_phone($reg_id){
		$select = mysqli_query($this->mysql, "SELECT * FROM phones WHERE registration_id = '".mysqli_escape_string($this->mysql, $reg_id)."'");
		if(mysqli_num_rows($select) > 0){
			return true;
		}else{
			$insert = mysqli_query($this->mysql, "INSERT INTO phones (registration_id) VALUES ('".mysqli_escape_string($this->mysql, $reg_id)."')");
			if($insert){
				return true;
			}else{
				return false;
			}
		}
	}

	public function send($id_user){

		$phones = $this->getAll();
		$ciclos = ceil(count($phones) / 1000);
		$count_errors = 0;
		$desc_errors = array();
		for($i = $c = 0; $i < $ciclos; $i++){
			$gcmIds = array();
			for($j = 0; $j < 1000 && isset($phones[$j + $c]); $j++){
				$gcmIds[] = $phones[$j + $c];
			}
			$c += 1000;
			$mensagem = array('title' => 'Novo Arquivo', 'user' => $id_user, 'time' => date('Y-m-d H:i:s'), 'message' => 'Existe um novo arquivo');
			$fields = array('registration_ids' => $gcmIds, 'collapse_key' => 'my_type', 'delay_while_idle' => false, 'time_to_live' => (60*60*24), 'dry_run' => false, 'data' => $mensagem);
			$header = array('Authorization: key='.$this->gcm_key, 'Content-Type: application/json');
			$ch = curl_init();
			curl_setopt($ch, CURLOPT_URL, $this->gcm_url);
			curl_setopt($ch, CURLOPT_IPRESOLVE, CURL_IPRESOLVE_V4);
			curl_setopt($ch, CURLOPT_POST, true);
			curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
			curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($fields));
			curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
			curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
			$result = curl_exec($ch);
			$result = json_decode($result, true);
			if($result["failure"] > 0){
				$count_errors++;
				$i = 0;
				foreach ($result["results"] as $chave => $valor) {
					foreach ($valor as $key => $value) {
						if(strcasecmp($key, 'registration_id') == 0 && strlen( trim($value) ) > 0){
							$return = $this->update_id($gcmIds[$i], $value);
						}else if(strcasecmp($key, 'error') == 0 && strcasecmp($value, 'NotRegistered') == 0){
							$return = $this->remove_id($gcmIds[$i]);
						}else if(strcasecmp($key, 'error') == 0 && strcasecmp($value, 'MismatchSenderId') == 0){
							$return = $this->remove_id($gcmIds[$i]);
						}else if(strcasecmp($key, 'error') == 0 && strcasecmp($value, 'InvalidRegistration') == 0){
							$return = $this->remove_id($gcmIds[$i]);
						}
					}
					$i++;
				}
			}
			curl_close($ch);
		}
		return true;

	}

	public function get_old_files($id_user, $type){
		switch ($type) {
			case 1:
				$certidoes = mysqli_query($this->mysql, "SELECT * FROM crm_certidoes WHERE codcli = '".mysqli_escape_string($this->mysql, $id_user)."' AND status = 1");
				if(mysqli_num_rows($certidoes) > 0){
					$ArrCertidoes = array();
					while($linha = mysqli_fetch_assoc($certidoes)){
						$cert = array();
						foreach ($linha as $key => $value) {
							$cert[$key] = $value;
						}
						$ArrCertidoes[] = $cert;
					}
					$JsonFinal = $ArrCertidoes;
				}
				break;

			case 2:
				$documentos = mysqli_query($this->mysql, "SELECT * FROM crm_documentos WHERE codcli = '".mysqli_escape_string($this->mysql, $id_user)."' AND status = 1");
				if(mysqli_num_rows($documentos) > 0){
					$ArrDocumentos = array();
					while($linha = mysqli_fetch_assoc($documentos)){
						$docs = array();
						foreach ($linha as $key => $value) {
							$docs[$key] = $value;
						}
						$ArrDocumentos[] = $docs;
					}
					$JsonFinal = $ArrDocumentos;
				}
				break;
		}
		return json_encode($JsonFinal);
	}

	public function set_old_file($id_user, $id_file){

		// EM DESENVOLVIMENTO

		return true;
	}

	public function get_new_files($id_user, $type){
		switch ($type) {
			case 1:
				$certidoes = mysqli_query($this->mysql, "SELECT * FROM crm_certidoes WHERE codcli = '".mysqli_escape_string($this->mysql, $id_user)."' AND status <> 1");
				if(mysqli_num_rows($certidoes) > 0){
					$ArrCertidoes = array();
					while($linha = mysqli_fetch_assoc($certidoes)){
						$cert = array();
						foreach ($linha as $key => $value) {
							$cert[$key] = $value;
						}
						$ArrCertidoes[] = $cert;
					}
					$JsonFinal = $ArrCertidoes;
				}
				break;
			
			case 2:
				$documentos = mysqli_query($this->mysql, "SELECT * FROM crm_documentos WHERE codcli = '".mysqli_escape_string($this->mysql, $id_user)."' AND status <> 1");
				if(mysqli_num_rows($documentos) > 0){
					$ArrDocumentos = array();
					while($linha = mysqli_fetch_assoc($documentos)){
						$docs = array();
						foreach ($linha as $key => $value) {
							$docs[$key] = $value;
						}
						$ArrDocumentos[] = $docs;
					}
					$JsonFinal = $ArrDocumentos;
				}
				break;
		}

		return json_encode($JsonFinal);

	}

	public function get_new_relatorios($id_user){
		$relatorios = mysqli_query($this->mysql, "SELECT * FROM crm_resposta WHERE id_cliente = '".mysqli_escape_string($this->mysql, $id_user)."' AND status = 1 AND down = 0 ORDER BY data_hora DESC");
		if(mysqli_num_rows($relatorios) > 0){
			while($linha = mysqli_fetch_assoc($relatorios)){
				$relat = array();
				foreach ($linha as $key => $value) {
					$relat[$key] = $value;
				}
				$ArrRelatorios[] = $relat;
			}
		}
		return json_encode($ArrRelatorios);
	}
	public function get_old_relatorios($id_user){
		$relatorios = mysqli_query($this->mysql, "SELECT * FROM crm_resposta WHERE id_cliente = '".mysqli_escape_string($this->mysql, $id_user)."' AND status = 1 AND down <> 0 ORDER BY data_hora DESC");
		if(mysqli_num_rows($relatorios) > 0){
			while($linha = mysqli_fetch_assoc($relatorios)){
				$relat = array();
				foreach ($linha as $key => $value) {
					$relat[$key] = $value;
				}
				$ArrRelatorios[] = $relat;
			}
		}
		return json_encode($ArrRelatorios);
	}

	public function set_old($id_user, $id_relatorio){

		$query = mysqli_query($this->mysql, "SELECT resposta FROM crm_resposta WHERE id_resposta = '".$id_relatorio."' AND id_cliente = '".$id_user."' AND status = '1'");
		if(mysqli_num_rows($query) > 0){
			$query = mysqli_fetch_assoc($query);
			$arquivo = $query['resposta'];
			$updateState = mysqli_query($this->mysql, "UPDATE crm_resposta SET down = '1', data_down = CURRENT_TIMESTAMP WHERE id_resposta = '".$id_relatorio."'");
			if($updateState){
				return $arquivo;
			}else{
				exit;	
			}
		}else{
			exit;
		}
	}

	public function send_new_client($nome, $email, $telefone, $newsl){



		$retorno = 0;
		include("../controladores/emails/phpmailer/class.phpmailer.php");
		$mail = new PHPMailer();
		//$mail->SMTPDebug = true;
		$mail->IsHTML(true);
		$mail->WordWrap = 50;
		$mail->IsSMTP();
		//$mail->SMTPAuth = true;
		$mail->Mailer = "smtp";
		$mail->Host = "localhost";
		$mail->Port = 587;
		$mail->Username = "no-reply@controlecontabilidadejf.com.br";
		$mail->Password = "C51bRg&}H9KO";
		$mail->From = "no-reply@controlecontabilidadejf.com.br";
		$mail->FromName = utf8_decode("Controle Contabilidade");
		$mail->AddReplyTo("marcos@controlecontabilidadejf.com.br", utf8_decode("Controle Contabilidade"));
		$mail->ConfirmReadingTo = 'marcos@controlecontabilidadejf.com.br';
		$mail->AddBcc("suporte@ambientecomunicacao.com","Max");
		$mail->AddBcc("criacao@ambientecomunicacao.com", utf8_decode("Célio Rodrigues"));
		$mail->Subject = utf8_decode("Recebemos seu contato.");
		$msg = utf8_decode("<br>Olá ".utf8_encode($nome).",<br>Recebemos seu contato e logo entraremos em contato pelo email $email.<br><br>Atenciosamente.<br>Controle Contabilidade");
		$mail->Body = $msg;
		$mail->AddAddress($email, $nome);
		$retornoEnvio = $mail->Send();
		if(!$retornoEnvio) {
			$retorno++;
		}

		$mail2 = new PHPMailer();
		//$mail->SMTPDebug = true;
		$mail2->IsHTML(true);
		$mail2->WordWrap = 50;
		$mail2->IsSMTP();
		$mail2->Mailer = "smtp";
		$mail2->Host = "localhost";
		$mail2->Port = 587;
		$mail2->Username = "no-reply@controlecontabilidadejf.com.br";
		$mail2->Password = "C51bRg&}H9KO";
		$mail2->From = "no-reply@controlecontabilidadejf.com.br";
		$mail2->FromName = utf8_decode("Contato via Aplicativo - Sistema ContábilAPP");
		$mail2->AddBcc("suporte@ambientecomunicacao.com","Max");
		$mail2->AddBcc("criacao@ambientecomunicacao.com", utf8_decode("Célio Rodrigues"));
		$mail2->Subject = utf8_decode("Novo contato via Aplicativo.");
		$msg = utf8_decode("<br>Novo contato pelo aplicativo.<br><br><b>Nome:</b>".utf8_encode($nome)."<br><b>Email:</b>".$email."<br><b>Telefone:</b>".$telefone."<br><b>Informações para seu negócio: </b>".( $newsl == '1' ? 'Sim' : 'Não' )." <br><br>Atenciosamente.<br>Sistema ContábilAPP");
		$mail2->Body = $msg;
		$mail2->AddAddress("marcos@controlecontabilidadejf.com.br", "Controle Contabilidade");
		$retornoEnvio = $mail2->Send();
		if(!$retornoEnvio) {
			$retorno++;
		}

		if($newsl == "1"){
			$sql = mysqli_query($this->mysql, "INSERT INTO emk_contatos (idLista, nome, email) VALUES (1, '".$nome."', '".$email."')");
			if(!$sql){
				$retorno++;
			}
		}
		
		if($retorno > 0){
			$retorno = array("status" => "ERROR");
		}else{
			$retorno = array("status" => "OK");
		}

		return json_encode($retorno, JSON_PRETTY_PRINT);

	}

	public function get_infos($id_user){
		$login = mysqli_query($this->mysql, "SELECT * FROM crm_cliente WHERE id = '".mysqli_escape_string($this->mysql, $id_user)."' ");
		$row = mysqli_num_rows($login);
		if($row > 0){
			$infos = array();
			while($linha = mysqli_fetch_assoc($login)){
				foreach ($linha as $key => $value) {
					$infos[$key] = utf8_encode($value);
				}
			}
		}
		return json_encode($infos);
	}

	public function get_informativos(){

		$db_host1='localhost';
		$db_name1='goulart_crm_2014';
		$db_username1='goulart_adm';
		$db_pass1='!L!oLdq2p%(x';

		$db_host2='186.202.127.60';
		$db_name2='ambcom_contabil';
		$db_username2='ambcom_contabil';
		$db_pass2='r%yHsTmgIL@t';

		$db1 = new PDO('mysql:host='.$db_host1.';dbname='.$db_name1,$db_username1,$db_pass1);
		$db2 = new PDO('mysql:host='.$db_host2.';dbname='.$db_name2,$db_username2,$db_pass2);

		$db1->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
		$db2->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);

		$querydb1 = "SELECT id, titulo, data, resumo, capa, slugArtigo FROM adm_artigos WHERE status = 1 AND categoria = 17 AND YEAR(data) = '".date("Y")."' ORDER BY data DESC LIMIT 10";
		$querydb2 = "SELECT id, titulo, data, resumo, capa, slugArtigo FROM adm_artigos WHERE status = 1 AND categoria = 17 AND YEAR(data) = '".date("Y")."' ORDER BY data DESC LIMIT 10";

		$stmt1 = $db1->query($querydb1);
		$stmt2 = $db2->query($querydb2);

		$qtd1 = $stmt1->rowCount();
		$qtd2 = $stmt2->rowCount();


		if($qtd1 > 0){
			if($qtd1 > 5){
				$qtd1 = 5;
			}
			$qtd2 -= $qtd1;
		}

		$all = $qtd1 + $qtd2;

		$results = array_merge($stmt1->fetchAll(PDO::FETCH_ASSOC), $stmt2->fetchAll(PDO::FETCH_ASSOC));

		$informativos = array();

		for ($i=0; $i < $all; $i++) {
			$informativos[] = array("titulo" => utf8_encode($results[$i]['titulo']),
									"data" => $this->dataFormatada($results[$i]['data']),
									"id" => $results[$i]['id']);
		}
		
		return json_encode($informativos, JSON_PRETTY_PRINT);

	}

	public function get_informativo($id){
		$db_host1='localhost';
		$db_name1='goulart_crm_2014';
		$db_username1='goulart_adm';
		$db_pass1='!L!oLdq2p%(x';
		$db_host2='186.202.127.60';
		$db_name2='ambcom_contabil';
		$db_username2='ambcom_contabil';
		$db_pass2='r%yHsTmgIL@t';
		$db1 = new PDO('mysql:host='.$db_host1.';dbname='.$db_name1,$db_username1,$db_pass1);
		$db2 = new PDO('mysql:host='.$db_host2.';dbname='.$db_name2,$db_username2,$db_pass2);
		$db1->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
		$db2->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
		$querydb1 = "SELECT id, titulo, data, texto FROM adm_artigos WHERE id = '".$id."' AND status = 1 AND categoria = 17 ORDER BY data DESC";
		$querydb2 = "SELECT id, titulo, data, texto FROM adm_artigos WHERE id = '".$id."' AND status = 1 AND categoria = 17 ORDER BY data DESC";
		$stmt1 = $db1->query($querydb1);
		$stmt2 = $db2->query($querydb2);
		$qtd1 = $stmt1->rowCount();
		$qtd2 = $stmt2->rowCount();
		$all = $qtd1 + $qtd2;
		$results = array_merge($stmt1->fetchAll(PDO::FETCH_ASSOC), $stmt2->fetchAll(PDO::FETCH_ASSOC));

		for ($i=0; $i < $all; $i++) {
			$informativo = array("id" => $results[$i]['id'],
								 "data" => $this->dataFormatada($results[$i]['data']),
								 "titulo" => utf8_encode($results[$i]['titulo']),
								 "texto" => utf8_encode($results[$i]['texto']));
		}
		
		return json_encode($informativo, JSON_PRETTY_PRINT);

	}

	public function login($user, $pass){
		$login = mysqli_query($this->mysql, "SELECT * FROM crm_cliente WHERE login = '".mysqli_escape_string($this->mysql, $user)."' ");
		$row = mysqli_num_rows($login);
		if($row > 0){
			$infos = array();
			while($linha = mysqli_fetch_assoc($login)){
				if($linha['senha'] == mysqli_escape_string($this->mysql, $pass)){
					$infos['status'] = '0x1234333';
					foreach ($linha as $key => $value) {
						$infos[$key] = utf8_encode($value);
					}
				}else{
					$infos['status'] = '0x1234222';
				}
			}
		}else{
			$infos['status'] = '0x1234111';
		}

		return json_encode($infos);
	}
}