Definir Alfa aNomeUsuario;

Definir Alfa  aCmdSql;
Definir Alfa  xCursorPrin;
Definir Alfa  aSql_RetornoJson;
Definir Alfa  aDadosRetornoJSon;
Definir Alfa  aSql_Columns_Name;
Definir Alfa  aSql_Columns_Alias;

Definir Alfa  aSql_Table_NameGetEstrutura;
Definir Alfa  aSql_Table_Name;
Definir Alfa  aSql_Columns_Select;
Definir Alfa  aSql_Condicao_Completa;
Definir Alfa  aSql_Condicao;
Definir Alfa  aSql_Condicao_Value_1;
Definir Alfa  aSql_Condicao_Value_2;
Definir Alfa  aSql_Condicoes_Select;
Definir Alfa  aSql_Ordenacao_Select;
Definir Alfa  aSql_Joins_Select;
Definir Alfa  aSql_Join;
Definir Alfa  aSql_Separador;
Definir Alfa  aSql_Completo;
Definir Alfa  aParamQueryCompleted;
Definir Alfa  aSql_CommandLimitOffset;

Definir Alfa  aEnter;

Definir Funcao InciaProcesso();
Definir Funcao GetParametrosSql();
Definir Funcao GetSqlCompleto();
Definir Funcao GetComandosPaginacao();
Definir Funcao GetColumnsSql();
Definir Funcao GetCondicoesSql();
Definir Funcao GetJoinsSql();
Definir Funcao GetOrderSql();
Definir Funcao ExecutaSql();
Definir Funcao GetDictionaryTable();
Definir Funcao GetColunasTabela();
RetornaAscII(13,aEnter);

ReturnType_AllDAta        =0;
ReturnType_QueryGenerate  =1;
ReturnType_JsonResponse   =2;
ReturnType_DictionaryTable=3;
ReturnType_QueryReceive   =99;



nParam_OffSet         = GenericConsult.OffSet;
nParam_Limit          = GenericConsult.Limit;
nParamReturnType      = genericConsult.ReturnType;
aParamQueryCompleted  = genericConsult.QueryCompleted;

LimpaEspacos(aParamQueryCompleted);

Se(nParam_Limit=0){
  nParam_Limit = 99999999;
}
aSql_CommandLimitOffset =" OFFSET :nParam_OffSet ROWS FETCH NEXT :nParam_Limit ROWS ONLY  ";

Se(aParamQueryCompleted<>""){
  nParamReturnType=ReturnType_QueryReceive;
}

aNomeUsuario           = NomUsu;

Se((aNomeUsuario <> "App Rex") e (aNomeUsuario<>"IntegradorPortalCompras") e (aNomeUsuario<>"AppRexHom")){
  nParamReturnType=ReturnType_QueryReceive;
}

InciaProcesso();

Funcao InciaProcesso();{
  aSql_RetornoJson = "" ;

  Se(nParamReturnType=ReturnType_DictionaryTable){
    GetDictionaryTable();
  }senao {
    Se(nParamReturnType<>ReturnType_QueryReceive){
      GetSqlCompleto();
    }
  }

  Se((nParamReturnType<>ReturnType_QueryGenerate) e  (nParamReturnType<>ReturnType_QueryReceive)){
    ExecutaSql();
    GenericConsult.pmJsonResponse = aDadosRetornoJSon;
  }

  Se(nParamReturnType=ReturnType_QueryReceive){
    aSql_Completo = aParamQueryCompleted;
    GetComandosPaginacao();
    Se((aNomeUsuario <> "App Rex") e (aNomeUsuario<>"IntegradorPortalCompras") e (aNomeUsuario<>"AppRexHom")){
      aCmdSql =  "SELECT JSON_OBJECT('Error' VALUE" +   "'Olá (" + aNomeUsuario + ")" +  " seu usuário não está autorizado a consumir esses serviço.') AS Sql_DadosJson "+ " FROM dual" +  aSql_CommandLimitOffset;
    }
    ExecutaSql();
    GenericConsult.pmJsonResponse = aDadosRetornoJSon;
  }
}
Funcao ExecutaSql();{
  SQL_Criar(xCursorPrin);
  SQL_UsarAbrangencia(xCursorPrin,0);
  SQL_UsarSQLSenior2(xCursorPrin,0);
  SQL_DefinirComando(xCursorPrin,aCmdSql);
  SQL_DefinirInteiro(xCursorPrin, "nParam_OffSet", nParam_OffSet);
  SQL_DefinirInteiro(xCursorPrin, "nParam_Limit" , nParam_Limit);
  GenericConsult.QueryCompleted     = aCmdSql;
  SQL_AbrirCursor(xCursorPrin);
  Enquanto(SQL_EOF(xCursorPrin)=0){
    SQL_RetornarAlfa   (xCursorPrin,"Sql_DadosJson",aSql_RetornoJson);
    Se(aDadosRetornoJSon=""){
      aDadosRetornoJSon = aSql_RetornoJson;
    }senao {
      aDadosRetornoJSon = aDadosRetornoJSon + "," + aSql_RetornoJson;
    }
    SQL_Proximo(xCursorPrin);
  }
  SQL_FecharCursor(xCursorPrin);
  SQL_Destruir(xCursorPrin);
}

Funcao GetSqlCompleto();{
  GetParametrosSql();
  aSql_Completo = "select " + aEnter +
                  aSql_Columns_Select + aEnter +
                  "from " + aSql_Table_Name +
                  aSql_Joins_Select + aEnter +
                  aSql_Condicoes_Select +
                  aSql_Ordenacao_Select  + aEnter;
  GetComandosPaginacao();
}

Funcao GetComandosPaginacao();{
  aCmdSql = aSql_Completo;
  ConverteParaMinusculo(aCmdSql);
  PosicaoAlfa("order ", aCmdSql,nPosOrder);
  Se(nPosOrder=0){
    aSql_Completo = aSql_Completo + " order by 1 ";
  }

  aSql_Completo = aSql_Completo + aSql_CommandLimitOffset;
  /*aCmdSql = "SELECT CAST(( select * from (" + aSql_Completo + " ) ForJson  FOR JSON PATH ) AS TEXT ) AS Sql_DadosJson";*/
  aCmdSql = "select JSON_ARRAYAGG(JSON_OBJECT( *  ) RETURNING CLOB )  AS Sql_DadosJson  from (" + aSql_Completo + " ) ";
  GenericConsult.QueryCompleted     = aCmdSql;
}
Funcao GetParametrosSql();{
  aSql_Table_Name = GenericConsult.Table.TableName;

  GetColumnsSql();

  GetCondicoesSql();

  GetJoinsSql();

  GetOrderSql();
}

Funcao GetColumnsSql();{
  aSql_Columns_Select="";
  iQtdColunas = GenericConsult.columns.QtdLinhas;
  Para(i=0;i<iQtdColunas;i++){
    GenericConsult.columns.LinhaAtual = i ;
    aSql_Columns_Name  = "   " +GenericConsult.columns.columnName;
    aSql_Columns_Alias = GenericConsult.columns.columnAlias;

    LimpaEspacos(aSql_Columns_Alias);
    Se(aSql_Columns_Alias=""){
      aSql_Columns_Alias = aSql_Columns_Name;
      SubstAlfa(".", "_",aSql_Columns_Alias );
    }

    Se(aSql_Columns_Select=""){
      aSql_Columns_Select = aSql_Columns_Name + " as " + aSql_Columns_Alias;
    }senao {
      aSql_Columns_Select = aSql_Columns_Select + "," + aEnter + aSql_Columns_Name + " as " + aSql_Columns_Alias;
    }
  }
}
Funcao GetCondicoesSql();{
  aSql_Condicoes_Select  = "";
  iQtdCondicoes = GenericConsult.Where.QtdLinhas;
  Para(i=0;i<iQtdCondicoes;i++){
    GenericConsult.Where.LinhaAtual = i ;
    aSql_Condicao          = "" ;
    aSql_Condicao_Value_1  = "";
    aSql_Condicao_Value_2  = "";
    aSql_Condicao_Completa = GenericConsult.Where.condicaoCompleta;

    LimpaEspacos(aSql_Condicao_Completa);
    Se(aSql_Condicao_Completa<>""){
      aSql_Condicao = aSql_Condicao_Completa;
    }senao {
      aSql_Condicao_Value_1 = GenericConsult.Where.columnValue;
      aSql_Condicao_Value_2 = GenericConsult.Where.columnValue2;
      LimpaEspacos(aSql_Condicao_Value_2);
      Se(aSql_Condicao_Value_2<>""){
        aSql_Condicao_Value_2 = " and " + aSql_Condicao_Value_2;
      }
      aSql_Condicao = GenericConsult.Where.Condicao + " " +
                      GenericConsult.Where.columnname + " " +
                      GenericConsult.Where.Operator + " " +
                      aSql_Condicao_Value_1 + aSql_Condicao_Value_2;
    }

    aSql_Condicoes_Select = aSql_Condicoes_Select + aEnter +  aSql_Condicao;

  }
}

Funcao GetJoinsSql();{
  aSql_Joins_Select = "";
  iQtdJoins = GenericConsult.Join.QtdLinhas;
  Para(i=0;i<iQtdJoins;i++){
    GenericConsult.Join.LinhaAtual = i ;
    aSql_Join = GenericConsult.Join.TypeJoin  + " " +
                GenericConsult.Join.TableName + " " ;
    /*Vamos percorer as condições do Join*/
    iQtdCondJoins = GenericConsult.Join.Condicoes.QtdLinhas;
    Para(k=0;k<iQtdCondJoins;k++){
      GenericConsult.Join.Condicoes.LinhaAtual = k;
      Se(k=0){
        aSql_Condicao = aEnter + " on ";
      }senao {
        aSql_Condicao = aEnter + " and  ";
      }
      aSql_Join = aSql_Join +
                  aSql_Condicao +
                  GenericConsult.Join.Condicoes.value1 + " " + GenericConsult.Join.Condicoes.Operator + " " + GenericConsult.Join.Condicoes.value2;
    }
    aSql_Joins_Select = aSql_Joins_Select +  aEnter + aSql_Join;
  }
}
Funcao GetOrderSql();{
  aSql_Ordenacao_Select="";
  aSql_Separador = "";
  iQtdOrderBy = GenericConsult.Order.QtdLinhas;
  Para(i=0;i<iQtdOrderBy;i++){
    GenericConsult.Order.LinhaAtual = i ;
    aSql_Ordenacao_Select = aSql_Ordenacao_Select + aSql_Separador +  aEnter +
                            GenericConsult.Order.columnName  ;
    aSql_Separador = ",";

  }
  Se(aSql_Ordenacao_Select<>""){
    aSql_Ordenacao_Select = " order by " +
                            aSql_Ordenacao_Select;
  }senao {
    aSql_Ordenacao_Select = " order by  1 " ;
  }
}
Funcao GetDictionaryTable();{
  aCmdSql = "";
  aSql_Columns_Select="";
  nParamReturnType = ReturnType_QueryGenerate;
  aSql_Table_NameGetEstrutura = GenericConsult.Table.TableName;
  ListaItem(aSql_Table_NameGetEstrutura, " ", 1 , aSql_Table_Name);
  Se(aSql_Table_Name=""){
    ListaItem(aSql_Table_NameGetEstrutura, " as ", 1 , aSql_Table_Name);
  }
  Se(aSql_Table_Name=""){
    aSql_Table_Name = GenericConsult.Table.TableName;
  }
  LimpaEspacos(aSql_Table_Name);
  Se(aSql_Table_Name<>""){
    GetColunasTabela();
    aCmdSql = " select " +   aEnter +
                aSql_Columns_Select +aEnter +
              " from " + aSql_Table_Name;
  }
  GenericConsult.QueryCompleted     = aCmdSql;
}
Funcao GetColunasTabela();{
  SQL_Criar(xCursorPrin);
  SQL_UsarAbrangencia(xCursorPrin,0);
  SQL_UsarSQLSenior2(xCursorPrin,0);
  SQL_DefinirComando(xCursorPrin,"SELECT\
                                   fldnam \
                                 FROM r996fld \
                                 WHERE tblnam = :aSql_Table_Name \
                                 ORDER BY fldord") ;
  SQL_DefinirAlfa(xCursorPrin, "aSql_Table_Name" , aSql_Table_Name);
  SQL_AbrirCursor(xCursorPrin);
  Enquanto(SQL_EOF(xCursorPrin)=0){
    SQL_RetornarAlfa   (xCursorPrin,"fldnam",aSql_Columns_Name);
    Se(aSql_Columns_Select=""){
      aSql_Columns_Select = aSql_Columns_Name;
    }senao {
      aSql_Columns_Select = aSql_Columns_Select + "," + aEnter + aSql_Columns_Name  ;
    }
    SQL_Proximo(xCursorPrin);
  }
  SQL_FecharCursor(xCursorPrin);
  SQL_Destruir(xCursorPrin);

}