#INCLUDE "TOTVS.CH"
#INCLUDE "RESTFUL.CH"
#include "rwmake.ch"
#include "protheus.ch"
#include "topconn.ch"
#include "COLORS.ch"
#include "tbiconn.ch"
  
WSRESTFUL Exemplo3 DESCRIPTION "Exemplo de API - Grafico Barra e Linha"
    WSDATA JsonFilter       AS STRING     OPTIONAL
    WSDATA drillDownFilter  AS STRING     OPTIONAL
    WSDATA Fields           AS STRING     OPTIONAL
    WSDATA Order            AS STRING     OPTIONAL
    WSDATA Page             AS INTEGER  OPTIONAL
    WSDATA PageSize         AS INTEGER  OPTIONAL
  
    WSMETHOD GET form ;
        DESCRIPTION "Formulario de Cadastro do Gráfico" ;
        WSSYNTAX "/charts/form/" ;
        PATH "/charts/form";
        PRODUCES APPLICATION_JSON
  
    WSMETHOD POST retDados ;
        DESCRIPTION "Deverá retornar as informações apresentadas no gráfico." ;
        WSSYNTAX "/charts/retDados/{JsonFilter}" ;
        PATH "/charts/retDados";
        PRODUCES APPLICATION_JSON
  
    WSMETHOD POST itemsDetails ;
        DESCRIPTION "Carrega o detalhamento do gráfico" ;
        WSSYNTAX "/charts/itemsDetails/{JsonFilter}" ;
        PATH "/charts/itemsDetails";
        PRODUCES APPLICATION_JSON
  
ENDWSRESTFUL
  
WSMETHOD GET form WSSERVICE Exemplo3
  
    Local oResponse   := JsonObject():New()
    Local oCoreDash   := CoreDash():New()
  
  oCoreDash:SetPOForm("Tipo de Gráfico" , "charttype"     , 6 , "Tipo de Gráfico" ;
  , .T., "string" , oCoreDash:SetPOCombo({{"bar","Barra"}}))
   
  oCoreDash:SetPOForm("Período" , "datainicio"    , 6 , "Data de" ;
  , .F., "date"   )
   
  oCoreDash:SetPOForm(""      , "datafim"       , 6 , "Data até" ;
  , .F., "date"   )
 
  oResponse  := oCoreDash:GetPOForm()
 
  Self:SetResponse( EncodeUtf8(oResponse:ToJson()))
  
Return .T.
  

WSMETHOD POST retDados WSRECEIVE JsonFilter WSSERVICE Exemplo3
  
    Local oResponse   := JsonObject():New()
    Local oCoreDash   := CoreDash():New()
    Local oJson       := JsonObject():New()
  
    oJson:FromJson(DecodeUtf8(Self:GetContent()))
  
    retDados(@oResponse, oCoreDash, oJson)
  
    Self:SetResponse( EncodeUtf8(oResponse:ToJson()))
  
    oResponse := Nil
    FreeObj( oResponse )
  
    oCoreDash:Destroy()
    FreeObj( oCoreDash )
  
Return .T.
  
Static Function retDados(oResponse, oCoreDash, oJson)
    Local aDataFim := {}
    Local aCab     := {}
    Local aCores    := oCoreDash:GetColorChart()
    Local cFiltros := ""
     local cSql   := ""
  local cTmp      := GETNEXTALIAS() + "TOK"

    //Filtro Data Inicial
If oJson:HasProperty("dateIni") //.And. ValType(oJson["dateIni"]) == "C"
  cFiltros += " AND DATA_CALENDARIO >= '" + Replace(oJson["dateIni"],"-","") + "' "
EndIf

//Filtro Data Final
If oJson:HasProperty("dateFim") //.And. ValType(oJson["dateIni"]) == "C"
  cFiltros += " AND DATA_CALENDARIO <= '" + Replace(oJson["dateFim"],"-","") + "' "
EndIf
  
    If oJson:GetJsonText("level") == "null" .Or. Len(oJson["level"]) == 0

       aCab := {'%' }
 
        cSql := "SELECT NOME,ROUND((SUM(SALDO_DIA)/SUM(MINUTOS_DIA))*100,2) 'VALOR' "
  cSql += "FROM BI_ASSIDUIDADE "
  cSql += "WHERE DATA_CALENDARIO BETWEEN '2024-08-01' AND '2024-08-31' "
  If ALLTRIM(cUsername) == "RICARDOD"
    cSql += "AND CENTRO_CUSTO LIKE '314%' "
  Else
    cSql += "AND CENTRO_CUSTO LIKE '321%' "
  Endif 
  cSql += "GROUP BY NOME ORDER BY 2 DESC "

  TCQuery cSql new alias &(cTmp)

  WHILE !&(cTmp)->(eof())
    oCoreDash:SetChartInfo( {&(cTmp)->(VALOR)},&(cTmp)->(NOME), , aCores[1][3] ) //Cor utilizada: GreenDk
    &(cTmp)->(dbSkip())
  END
  &(cTmp)->(dbCloseArea())

  
        aDataFim := {}
        aAdd(aDataFim, oCoreDash:SetChart(aCab,,.F., ,"Assiduidade por Colaborador"))
    
EndIf

    oResponse["items"] := aDataFim
  
Return
  
WSMETHOD POST itemsDetails WSRECEIVE JsonFilter, drillDownFilter WSRESTFUL Exemplo3
  
  Local aHeader     := {}
  Local aItems      := {}
  Local aRet        := {}
  Local cBody       := DecodeUtf8(Self:GetContent())
  Local cError        := "Erro na Requisição"
  Local lRet              := .T.
  Local oCoreDash   := CoreDash():New()
  Local oBody       := JsonObject():New()
  Local oJsonFilter := JsonObject():New()
  Local oJsonDD     := JsonObject():New()
  
  If !Empty(cBody)
    oBody:FromJson(cBody)
    If ValType(oBody["chartFilter"]) == "J"
        oJsonFilter := oBody["chartFilter"]
    EndIf
    If ValType(oBody["detailFilter"]) == "A"
        oJsonDD := oBody["detailFilter"]
    EndIf
  EndIf
  
  Self:SetContentType("application/json")
  //Verifico o Nivel do grafico
  If oJsonFilter:GetJsonText("level") == "null" .Or. Len(oJsonFilter["level"]) == 0
    //Verifico o nivel do Drilldpwn
    If Len(oJsonDD) == 0
      aHeader := {;
          {"codigo"     , "Código"        ,"link"               },;
          {"nome"         , "Nome Vendedor"                       },;
          {"totalItens" , "Total de Itens","number",'1.2-5',.F. },;
          {"totalValor" , "Valor Total"   , "currency","BRL",.F.};
          }
  
      aItems := {;
          {"codigo"     , "SA3.A3_COD"    },;
          {"nome"         , "SA3.A3_NOME"   },;
          {"totalItens" , "QTDITEM"       },;
          {"totalValor" , "TOTAL","N"     };
          }
  
      aRet := MntQuery1()
    ElseIf Len(oJsonDD) == 1
      //Caso eu queira pegar o nome do nível selecionado : oJsonFilter["level"][1]["labelDataSet"]
      // Se fosse gráfico do tipo pizza: oJsonFilter["level"][1]["label"]
      aHeader := {;
          {"codigoPed"  , "Código do Pedido"    },;
          {"codigoCli"  , "Código do Cliente"   },;
          {"nome"         , "Nome"                },;
          {"totalValor" , "TOTAL","currency","BRL",.F.};
          }
  
      aItems := {;
          {"codigoPed"  , "SC5.C5_NUM"   },;
          {"codigoCli"  , "SC5.C5_CLIENTE"  },;
          {"nome"       , "SA1.A1_NOME"      },;
          {"totalValor" , "TOTAL","N"        };
          }
      aRet := MntQuery2("SC5.C5_VEND1 = '" + oJsonDD[1]["codigo"] + "'")
    EndIf
  ElseIf Len(oJsonFilter["level"]) == 1
    aHeader := {;
          {"codigoPed"  , "Código do Pedido"    },;
          {"codigoCli"  , "Código do Cliente"   },;
          {"nome"         , "Nome"                },;
          {"totalValor" , "TOTAL","currency","BRL",.F.};
          }
  
      aItems := {;
          {"codigoPed"  , "SC5.C5_NUM"   },;
          {"codigoCli"  , "SC5.C5_CLIENTE"  },;
          {"nome"       , "SA1.A1_NOME"      },;
          {"totalValor" , "TOTAL","N"        };
          }
      aRet := MntQuery2("SA1.A1_RISCO = '" + Right(oJsonFilter["level"][1]["labelDataSet"],1) + "' AND SA1.A1_EST = '" + oJsonFilter["level"][1]["label"] + "' ")
  ElseIf Len(oJsonFilter["level"]) == 2
    aHeader := {;
        {"codigo"     , "Código"        },;
        {"nome"       , "Nome Vendedor" },;
        {"totalItens" , "Total de Itens","number",'1.2-5',.F.},;
        {"totalValor" , "Valor Total", "currency","BRL",.F.};
        }
  
    aItems := {;
        {"codigo"     , "SA3.A3_COD"   },;
        {"nome"       , "SA3.A3_NOME"  },;
        {"totalItens" , "QTDITEM"      },;
        {"totalValor" , "TOTAL","N" };
        }
  
    aRet := MntQuery1()
  EndIf
  
  oCoreDash:SetQuery(aRet[1])
  oCoreDash:SetWhere(aRet[2])
  oCoreDash:SetGroupBy(aRet[3])
  oCoreDash:SetFields(aItems)
  oCoreDash:SetApiQstring(Self:aQueryString)
  oCoreDash:BuildJson()
  
  If lRet
    oCoreDash:SetPOHeader(aHeader)
    Self:SetResponse( oCoreDash:ToObjectJson() )
  Else
    cError := oCoreDash:GetJsonError()
    SetRestFault( 500,  EncodeUtf8(cError) )
  EndIf
  
  oCoreDash:Destroy()
  
  FreeObj(oBody)
  FreeObj(oJsonFilter)
  FreeObj(oJsonDD)
  FreeObj(oCoreDash)
  
  aSize(aRet, 0)
  aSize(aItems, 0)
  aSize(aHeader, 0)
  
Return( lRet )
  
Static Function MntQuery1()
  Local cQuery  := ""
  Local cWhere  := ""
  Local cGroup  := ""
    
  cQuery := " SELECT SA3.A3_COD, SA3.A3_NOME, COUNT(C6_ITEM) QTDITEM, SUM(C6_VALOR) TOTAL "
  cQuery += " FROM " + RetSqlName("SC5") + " SC5 "
  
  cQuery += " INNER JOIN " + RetSqlName("SC6") + " SC6 ON SC6.C6_NUM = SC5.C5_NUM "
  cQuery += " INNER JOIN " + RetSqlName("SA3") + " SA3 ON SA3.A3_COD = SC5.C5_VEND1 "
  
  cWhere := " SC5.D_E_L_E_T_ = ' ' "
  cWhere += " AND SC6.D_E_L_E_T_ = ' ' "
  cWhere += " AND SA3.D_E_L_E_T_ = ' ' "
  cWhere += " AND SC5.C5_FILIAL = '" + xFilial("SC5") + "'   "
  cWhere += " AND SC6.C6_FILIAL = '" + xFilial("SC6") + "'   "
  cWhere += " AND SA3.A3_FILIAL = '" + xFilial("SA3") + "'   "
  cWhere += " AND SC5.C5_EMISSAO >= '20220401'   "
  
  cGroup := " SA3.A3_COD, SA3.A3_NOME "
  
Return {cQuery, cWhere, cGroup}
  

Static Function MntQuery2(cFilter)
  Local cQuery  := ""
  Local cGroup  := ""
  Local cWhere  := ""
  
  Default cFilter := ""
  
  cQuery := " SELECT SC5.C5_NUM, SC5.C5_CLIENTE, SA1.A1_NOME, SUM(C6_VALOR) TOTAL "
  cQuery += " FROM " + RetSqlName("SC5") + " SC5 "
  cQuery += " INNER JOIN " + RetSqlName("SC6") + " SC6 ON SC6.C6_NUM = SC5.C5_NUM "
  cQuery += " INNER JOIN " + RetSqlName("SA1") + " SA1 ON SA1.A1_COD = SC5.C5_CLIENTE
  
  if !Empty(cFilter)
      cWhere += cFilter + " AND "
  EndIf
  
  cWhere += " SC5.D_E_L_E_T_ = ' ' "
  cWhere += " AND SC6.D_E_L_E_T_ = ' ' "
  cWhere += " AND SA1.D_E_L_E_T_ = ' ' "
  cWhere += " AND SC5.C5_FILIAL = '" + xFilial("SC5") + "'   "
  cWhere += " AND SC6.C6_FILIAL = '" + xFilial("SC6") + "'   "
  cWhere += " AND SA1.A1_FILIAL = '" + xFilial("SA1") + "'   "
  cWhere += " AND SC5.C5_EMISSAO >= '20220401'   "  // Limite adicionado para evitar lentidao no retorno da query em bases com uma quantidade muito grande de pedidos de vendas
  
  cGroup := " C5_NUM, C5_CLIENTE, SA1.A1_NOME "
  
Return {cQuery, cWhere, cGroup}
  
Static Function RetRisco(cFiltro)
    Local aQuery  := MntQuery3("COUNT(SA1.A1_COD) TOTAL_REGISTROS", cFiltro)
    Local cQuery  := ""
    Local cTemp   := GetNextAlias()
    Local xRet
  
    Default cWhere  := ""
    Default cInfo   := ""
  
    cQuery := aQuery[1] + " WHERE " + aQuery[2]
  
    DBUseArea( .T., "TOPCONN", TCGenQry( ,, cQuery ), cTemp, .T., .T. )
  
    xRet := (cTemp)->TOTAL_REGISTROS
  
    (cTemp)->( DBCloseArea() )
Return xRet
  

Static Function MntQuery3(cCampos, cFiltro, cGroupBy)
    Local cQuery
    Local cWhere
    Local cGroup
  
    Default cTable := "SA1"
    Default cCampos := "SA1.A1_COD, SA1.A1_LOJA, SA1.A1_NOME, SA1.A1_NREDUZ, SA1.A1_RISCO"
    
    cQuery := " SELECT " + cCampos + " FROM " + RetSqlName("SA1") + " SA1 "
    cWhere := " SA1.A1_FILIAL = '" + xFilial("SA1") + "'" + cFiltro
    cWhere += " AND SA1.D_E_L_E_T_ = ' ' "
  
    If !Empty(cGroupBy)
        cGroup := cGroupBy
    EndIf
Return {cQuery, cWhere, cGroup}