<?php

namespace Teknisa\Libs\Util\Repositories;

use Doctrine\ORM\EntityManager;
use Teknisa\Libs\Util\Utilities;

class UserData {
    /** @var EntityManager $entityManager */
    protected $entityManager;

    /**
     * @param EntityManager $entityManager
     */
    public function __construct(EntityManager $entityManager) {
        $this->entityManager = $entityManager;
    }

    public function setEntityManager($entityManager) {
        $this->entityManager = $entityManager;
    }

    /**
     * @param $operador
     * @return array
     */
    public function getOperatorByNmAccessOrEmail($operador){
        $query =
            <<<SQL
                SELECT CDOPERADOR AS "ID", NMOPERADOR AS "NAME", NRORG AS "ORGANIZATION_ID", DSEMAILOPER AS "EMAIL",
                         CDSENHAOPERWEB AS "PASSWORD_MD5", CDSENHOPER AS "PASSWORD_CRYPTO_PLUS",
                         CASE WHEN IDOPERATIVO = 'A'
                              THEN 'Y'
                              ELSE 'N'
                          END AS "IS_ACTIVE",
                         IDIDIOMA AS "IDIDIOMA", NMACESSO AS "NMACESSO", NMCOMPLOPER AS "NMCOMPLOPER", CDEMPRESA AS "CDEMPRESA", NRPARCNEGOCIO AS "NRPARCNEGOCIO",
                        NRORG AS "NRORG", NRORGTRAB AS "NRORGTRAB", NRESTRUTTRAB AS "NRESTRUTTRAB", CDFILIALSELECIONADA AS "CDFILIALSELECIONADA", NRORGOPER AS "NRORGOPER"
                    FROM OPERADOR
                   WHERE UPPER(NMACESSO) = UPPER(:OPERADOR)
                      OR DSEMAILOPER = :OPERADOR
SQL;


        $params = array( 'OPERADOR' => $operador );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $cdOperator
     * @return array
     */
    public function getOperatorByCdoperador($cdOperator){
        $query =  <<<SQL
                  SELECT CDOPERADOR AS "CDOPERADOR", NMOPERADOR AS "NMOPERADOR", IDOPERATIVO AS "IDOPERATIVO", CDSENHAOPERWEB AS "CDSENHAOPERWEB",
                         CDSENHOPER AS "CDSENHOPER", IDIDIOMA AS "IDIDIOMA", NMACESSO AS "NMACESSO", NMCOMPLOPER AS "NMCOMPLOPER", CDEMPRESA AS "CDEMPRESA",
                         NRPARCNEGOCIO AS "NRPARCNEGOCIO", NRORG AS "NRORG", NRORGTRAB AS "NRORGTRAB", NRESTRUTTRAB AS "NRESTRUTTRAB",
                         DSEMAILOPER AS "DSEMAILOPER", CDFILIALSELECIONADA AS "CDFILIALSELECIONADA", NRORG AS "ORGANIZATION_ID", DSEMAILOPER AS "EMAIL",
                         CDSENHAOPERWEB AS "PASSWORD_MD5", CDSENHOPER AS "PASSWORD_CRYPTO_PLUS"
                    FROM OPERADOR
                   WHERE CDOPERADOR = :CDOPERADOR
SQL;

        $params = array( 'CDOPERADOR' => $cdOperator );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $nrOrg
     * @return array
     */
    public function getParamSenhaByNrorg($nrOrg){
        $query = <<<SQL
             SELECT NRDIASEXPSEN AS "NRDIASEXPSEN", IDUTILIZAAD AS "IDUTILIZAAD", IDUTCTRLSEN AS "IDUTCTRLSEN", CDPICTSENHA AS "CDPICTSENHA", NRERROSENHA AS "NRERROSENHA",
                         QTMINSISINA AS "QTMINSISINA", NRDIASAVISO AS "NRDIASAVISO", NRALTSENHREP AS "NRALTSENHREP", NMDOMINIOAD AS "NMDOMINIOAD",
                         DSURLSERVERAD AS "DSURLSERVERAD", NMUSUARIOADMAD AS "NMUSUARIOADMAD", DSSENHAUSUARIOADMAD AS "DSSENHAUSUARIOADMAD"
                    FROM PARAMSENHA
                   WHERE NRORG = :NRORG
SQL;

        $params = array( 'NRORG' => $nrOrg );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $nrOrg
     * @return array
     */
    public function getParamGeralByNrorg($nrOrg){
        $query =  <<<SQL
                    SELECT CDPICTPROD AS "CDPICTPROD", CDPICTPRAT AS "CDPICTPRAT", IDUTCTRLACESWEB AS "IDUTCTRLACESWEB"
                    FROM PARAMGERAL
                   WHERE NRORG = :NRORG
SQL;

        $params = array( 'NRORG' => $nrOrg );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $nrOrg
     * @param $nrParcNegocio
     * @return array
     */
    public function getPerfAcessParcByNrorgAndNrParcNegocio($nrOrg, $nrParcNegocio){
        $query =  <<<SQL
                    SELECT PP.NRPERFILACESSO AS "ID",  PA.IDPERFILACESSOADM AS "IDPERFILACESSOADM"
                    FROM PERFACESSPARC PP
                   INNER JOIN PERFILACESSO PA ON PA.NRPERFILACESSO = PP.NRPERFILACESSO
                   WHERE PP.NRORG = :NRORG
                     AND PA.NRORG = :NRORG
                     AND PP.NRPARCNEGOCIO = :NRPARCNEGOCIO
                     AND PP.IDATIVO = 'S'
SQL;

        $params = array( 'NRORG' => $nrOrg, 'NRPARCNEGOCIO' => $nrParcNegocio );
        return $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
    }

    /**
     * @param $cdFilial
     * @param $nrOrg
     * @return array
     */
    public function getFilialByCdfilialAndNrorg($cdFilial, $nrOrg){
        $query =  <<<SQL
                    SELECT CDFILIAL AS "CDFILIAL", NMFILIAL AS "NMFILIAL"
                    FROM FILIAL
                   WHERE NRORG = :NRORG
                     AND CDFILIAL = :CDFILIAL
SQL;

        $params = array( 'NRORG' => $nrOrg, 'CDFILIAL' => $cdFilial );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $nrOrg
     * @return array
     */
    public function getOrganizacaoByNrorg($nrOrg){
        $query =  <<<SQL
                    SELECT NRORG AS "NRORG", NMORGANIZACAO AS "TRADING_NAME", NMRAZSOCIALORG AS "COMPANY_NAME"
                    FROM ORGANIZACAO
                   WHERE NRORG = :NRORG
SQL;

        $params = array( 'NRORG' => $nrOrg );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $cdOperador
     * @return array
     */
    public function getLastPasswordUpdateByCdOperador($cdOperador){
        $query =  <<<SQL
                    SELECT TO_CHAR(MAX(DTALTSENHA), 'DD/MM/YYYY') AS "DTALTSENHA", IDATUCTRLSENHA AS "IDATUCTRLSENHA"
                    FROM CONTROLSENHA
                   WHERE CDOPERADOR = :CDOPERADOR
                     AND DTALTSENHA = (SELECT MAX(DTALTSENHA) AS DTALTSENHA
                                         FROM CONTROLSENHA
                                        WHERE CDOPERADOR = :CDOPERADOR)
                   GROUP BY IDATUCTRLSENHA
                   ORDER BY "DTALTSENHA"
SQL;

        $params = array( 'CDOPERADOR' => $cdOperador );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $cdOperator
     * @param $nrOrg
     * @param $webPassword
     * @param $delphiPassword
     * @return array
     */
    public function updateOperatorPassword($cdOperator, $nrOrg, $webPassword, $delphiPassword){
        $dateTimeFormat = 'DD/MM/YYYY HH24:MI:SS';
        $query =  <<<SQL
                    UPDATE OPERADOR
                     SET CDSENHAOPERWEB = :WEB_PASSWORD,
                         CDSENHOPER     = :DELPHI_PASSWORD,
                         DTULTATU       = TO_DATE(:DTULTATU,'$dateTimeFormat'),
                         IDSTATUSENHA   = 'A'
                   WHERE CDOPERADOR = :CDOPERADOR
                     AND NRORG      = :NRORG
SQL;

        $params = array(
            'CDOPERADOR'      => $cdOperator,
            'NRORG'           => $nrOrg,
            'DTULTATU'        => Utilities::getCurrentDateTimeString(),
            'WEB_PASSWORD'    => $webPassword,
            'DELPHI_PASSWORD' => $delphiPassword
        );

        return $this->entityManager->getConnection()->executeQuery($query, $params);
    }

    /**
     * @param $cdOperator
     * @param $nrOrg
     * @param $cdSenhAlt
     * @param $cdSenhAltWeb
     * @param $idAtuCtrlSenha
     * @return array
     */
    public function insertControlSenha($cdOperator, $nrOrg, $cdSenhAlt, $cdSenhAltWeb, $idAtuCtrlSenha){
        $query =  <<<SQL
                    INSERT INTO CONTROLSENHA
                         (NRORGINCLUSAO, DTINCLUSAO, CDOPERINCLUSAO, CDOPERADOR, DTALTSENHA,
                          CDSENHALT, CDSENHALTWEB, IDATUCTRLSENHA, NRORG, DTULTATU, NRORGULTATU, CDOPERULTATU)
                  VALUES (:NRORG, TO_DATE(:NOW,'DD/MM/YYYY HH24:MI:SS'), :CDOPERADOR, :CDOPERADOR, TO_DATE(:NOW,'DD/MM/YYYY HH24:MI:SS'),
                          :CDSENHALT, :CDSENHALTWEB, :IDATUCTRLSENHA, :NRORG, TO_DATE(:NOW,'DD/MM/YYYY HH24:MI:SS'), :NRORG, :CDOPERADOR)
SQL;

        $params = array(
            'CDOPERADOR'     => $cdOperator,
            'NRORG'          => $nrOrg,
            'CDSENHALT'      => $cdSenhAlt,
            'CDSENHALTWEB'   => $cdSenhAltWeb,
            "NOW"            => Utilities::getCurrentDateTimeString(),
            'IDATUCTRLSENHA' => $idAtuCtrlSenha
        );

        return $this->entityManager->getConnection()->executeQuery($query, $params);
    }

    /**
     * @param $nrOrg
     * @param $idAtivo
     * @return array
     */
    public function getUseIpRestrictionByNrorgAndIdativo($nrOrg, $idAtivo){
        $query =  <<<SQL
                    SELECT IDUTIPRESTRICTION AS "IDUTIPRESTRICTION"
                    FROM OM_PARAM_ORG
                   WHERE NRORG = :NRORG
                     AND IDATIVO = :IDATIVO
SQL;

        $params = array( 'NRORG' => $nrOrg, 'IDATIVO' => $idAtivo );
        $result = $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
        return !empty($result) ? $result[0] : $result;
    }

    /**
     * @param $nrOrg
     * @param $idAtivo
     * @return array
     */
    public function getIpRestrictionByNrorgAndIdativo($nrOrg, $idAtivo){
        $query =  <<<SQL
                    SELECT DS_IPV4_RESTRICTED AS "IPV4", DS_IPV6_RESTRICTED AS "IPV6"
                    FROM OM_IP_RESTRICTION
                   WHERE NRORG = :NRORG
                     AND IDATIVO = :IDATIVO
SQL;

        $params = array( 'NRORG' => $nrOrg, 'IDATIVO' => $idAtivo );
        return $this->entityManager->getConnection()->fetchAllAssociative($query, $params);
    }

    /**
     * @param $cdOperator
     * @param $nrOrg
     * @return array
     */
    public function updateUserLastAccess($cdOperator, $nrOrg){
        $query =  <<<SQL
                    UPDATE OPERADOR
                     SET DTULTACESOPER = TO_DATE(:NOW,'DD/MM/YYYY HH24:MI:SS')
                   WHERE CDOPERADOR = :CDOPERADOR
                     AND NRORG      = :NRORG
SQL;

        $params = array(
            'CDOPERADOR'      => $cdOperator,
            "NOW"             => Utilities::getCurrentDateTimeString(),
            'NRORG'           => $nrOrg
        );

        return $this->entityManager->getConnection()->executeQuery($query, $params);
    }

    public function getIdUtlPerfilFilial($nrOrg){
        try {
            $query =  <<<SQL
                    SELECT IDUTLPERFILFILIAL AS "IDUTLPERFILFILIAL"
                        FROM PARAMGERAL
                       WHERE NRORG = :NRORG
SQL;

            $params = array( 'NRORG' => $nrOrg);
            return $this->entityManager->getConnection()->fetchAssociative($query, $params);
        } catch (\Exception $e) {
            return array('IDUTLPERFILFILIAL' => "N");
        }
    }

    public function getPerfilSelecionado($nrOrg, $cdOperator){
        try {
            $query =  <<<SQL
                    SELECT CDPERFILSELECIONADO AS "CDPERFILSELECIONADO" 
                        FROM OPERADOR
                       WHERE NRORG = :NRORG
                         AND CDOPERADOR = :CDOPERADOR
SQL;

            $params = array( 'NRORG' => $nrOrg, 'CDOPERADOR' => $cdOperator );
            return $this->entityManager->getConnection()->fetchAssociative($query, $params);
        } catch (\Exception $e) {
            return array('CDPERFILSELECIONADO' => null);
        }
    }

    public function getOperatorHash($user, $project, $organizationId) {
        $query =  <<<SQL
                    SELECT DSCHAVEULTACESSO AS "DSCHAVEULTACESSO" 
                    FROM OPERADOR_ACESSO
                   WHERE CDOPERADOR   = :USER_ID
                     AND NRPRODUTO_ID = :PROJECT
                     AND NRORG        = :NRORG
SQL;

        $params = array(
            'USER_ID' => $user,
            'PROJECT' => $project,
            'NRORG'   => $organizationId
        );
        return $this->entityManager->getConnection()->fetchAssociative($query, $params);
    }

    public function deleteOperatorHash($user, $project, $hash, $organizationId) {
        $query =  <<<SQL
                    UPDATE OPERADOR_ACESSO
                     SET DSCHAVEULTACESSO = NULL                   
                   WHERE CDOPERADOR       = :USER_ID
                     AND NRPRODUTO_ID     = :PROJECT
                     AND DSCHAVEULTACESSO = :HASH
                     AND NRORG            = :NRORG
SQL;

        $params = array (
            'USER_ID' => $user,
            'PROJECT' => $project,
            'HASH'    => $hash,
            'NRORG'   => $organizationId
        );
        $stmt = $this->entityManager->getConnection()->prepare($query);
        $stmt->execute($params);
    }
}






