Option Explicit  
On Error Resume Next
'On Error GoTo ErrorHandler

Dim strDirectory, nThreshold, counter, extension, msgtext
Dim objConnection, objCommand, sqlQuery, myRecordset, field
Dim objFSO, objFSO1, objFolder, objFile, Logfile, WshNetwork
Dim objShell, strPath, strFolder

Dim dtmEndTime, dtmStartTime

Dim endPadrao, endArmaz, extArquivos, encontrouDados, linhaAtual
Dim nomeArquivo, raioxEquipamento, endAmostra, idRAioxObjeto, idRAioxGrupo, nomeAmostra, dataHoraAmostra, dataHoraRaiox, horaRaioxRealizacao, pesoInicial, pesoFinal, grupoRaiox
Dim idAmostraInserida, dataRaiox, horaRaiox, complNomeAmostra

Dim cont

Dim dadosArquivo()
Dim componentesRaiox()
Dim componentesBD()
Dim objetosRaioxBD()

Const SQLServer = "SQLServer"
Const MySQL = "MySQL"
Const MariaDB = "MariaDB"

Dim bancoUsado : bancoUsado = SQLServer
Dim databaseAtual



'redim dadosArquivo(20, 18)

Const apelidoServidor = "caf"

Const idRaioxEquipamento = 1
CONST DADOS_POR_LINHA = 6
CONST DADOS_COMPONENTES = 5

Const adOpenStatic = 3
Const adLockOptimistic = 3
'Const DB_CONNECT_STRING = "Provider=SQLOLEDB;Data Source=SKYSYSTEM\SQLEXPRESS;Initial Catalog=dbMain;User ID=sa;Password=asd!@#123;"
'Const DB_CONNECT_STRING = "Provider=SQLOLEDB;Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=dbMain;User ID=sa;Password=asd!@#123;"
'Const DB_CONNECT_STRING  = "Driver={ODBC Driver 13 for SQL Server};Server=LOCALHOST\SQLEXPRESS;Database=grd_caf_data_2021_2;Trusted_Connection=yes;"
'Const DB_CONNECT_STRING = "DSN=WEBSERVER\SQLEXPRESS;UID=sa;PWD=asd!@#123;Database=dbMain"
Dim DB_CONNECT_STRING


Set objShell = CreateObject("Wscript.Shell")

strPath = Wscript.ScriptFullName
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO1.GetFile(strPath)
strFolder = objFSO1.GetParentFolderName(objFile) 

Function montaConnectString(apelido, var)
    DB_CONNECT_STRING = "Driver={ODBC Driver 13 for SQL Server};Server=LOCALHOST\SQLEXPRESS;Database=grd_data_" & apelido & "_" & var & ";Trusted_Connection=yes;"
    ' DB_CONNECT_STRING = "Provider=SQLOLEDB;Server=LOCALHOST\SQLEXPRESS;Database=grd_" & apelido & "_data_" & var & ";User ID=ADMGRDCAF;Password=re8!zx3ueud;"
    ' DB_CONNECT_STRING = "Data Source=localhost\SQLEXPRESS;Initial Catalog=grd_" & apelido & "_data_" & var & ";Integrated Security=SSPI;"
    ' wscript.echo DB_CONNECT_STRING
End Function

Function retornaDatabasePelaData(nomeArquivo)
    Dim varTemp
    Dim mesAtual, anoAtual, mesAtualTemp

    anoAtual = Mid(nomeArquivo, 1, 4)
    mesAtualTemp = Mid(nomeArquivo, 6, 2)

    wscript.echo "mes atual " & nomeArquivo

    mesAtual = "4"
    if(CInt(mesAtualTemp) < 10) then
        mesAtual = "3"
    End If
    if(CInt(mesAtualTemp) < 7) then
        mesAtual = "2"
    End If
    if(CInt(mesAtualTemp) < 4) then
        mesAtual = "1"
    End If


    vartemp = anoAtual & "_" & mesAtual

    databaseAtual = varTemp

    montaConnectString apelidoServidor, databaseAtual

End Function

Function criaNovoBancodeDados(nomeNovoBanco)
    On Error Resume Next
    Dim objSQLServer, strDBServerName, objDB, objFG, objDBFile, objLogFile
    const DB_SIZE_IN_MEGABYTES = 5
    strDBServerName = "."

    Set objSQLServer = CreateObject("SQLDMO.SQLServer")
    objSQLServer.LoginSecure = True
    objSQLServer.Connect strDBServerName

    Set objDB = CreateObject("SQLDMO.Database")
    Set objFG = CreateObject("SQLDMO.Filegroup")
    Set objDBFile = CreateObject("SQLDMO.DBFile")
    Set objLogFile = CreateObject("SQLDMO.LogFile")

    objDB.Name = nomeNovoBanco
    objDBFile.Name = nomeNovoBanco & "_Data"
    objDBFile.PhysicalName = "D:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\" & nomeNovoBanco & "_Data.MDF"
    objDBFile.Size = DB_SIZE_IN_MEGABYTES

    objDB.FileGroups("PRIMARY").DBFiles.Add(objDBFile)

    objSQLServer.Databases.Add(objDB)

    If Err.Number <> 0 Then
        WScript.Echo "Error in criaNovoBancodeDados: " & Err.Description
        Err.Clear
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0

End Function
 
Sub AbrirBanco()
    On Error Resume Next
    
    'Criando a conexão com o SQLSERVER'
     Set objConnection = CreateObject("ADODB.Connection")
        
    'Abrindo a conexão com o SQLSERVER'
    objConnection.Open DB_CONNECT_STRING

    If Err.Number <> 0 Then
        WScript.Echo "Error in AbrirBanco: " & Err.Description
        WScript.Echo "Error Number in AbrirBanco: " & Err.Number
        If (Err.Number = -2147217843) Then
        wscript.echo "Criando novo banco"
        criaNovoBancodeDados(databaseAtual)
        End IF
        Err.Clear
    else 
        wscript.echo "banco aberto"
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0

    

End Sub

Sub FecharBanco()
    'Fecha a conexão aberta com o SQLSERVER'
     objConnection.Close
     wscript.echo "banco fechado"
End Sub


Sub procuraArquivos(extension, strDirectory)
	
	'Dim testeEndArmaz : testeEndArmaz = verificaEndArmazExiste (strDirectory)

	'If(testeEndArmaz) Then
	
		wscript.echo "Iniciou Contagem"
		counter = 0  

		Set objFSO = CreateObject("Scripting.FileSystemObject")	
		Set objFolder = objFSO.GetFolder(strDirectory)

		For Each objFile In objFolder.Files  
			if LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) then  
				'setando valores para a tabela 
				'nomeArquivo = objFSO.GetBaseName(objFile) 'pega o nome sem a extensão do arquivo
				nomeArquivo = objFSO.GetFileName(objFile) 'pega o nome junto com a extensão

			   
				'lendo os arquivos
				linhaAtual = 1
				wscript.echo nomeArquivo
				readDadosArquivos objFile
                'getData objFile
				'wscript.echo " "
				counter = counter + 1
			end if  
		Next 
		wscript.echo " "
		wscript.echo "Foram encontrados: " &counter & " arquivos"
		
		
		Set objFSO = Nothing
		Set objFolder = Nothing
		
	
	'End If

    

    'MsgBox ("The number of " & extension & " files in " & strDirectory & " is: " & counter) 
End Sub

Sub readDadosArquivos (arquivo)
    Const ForReading = 1
    Dim fso2, f, dado, i,j,k, inicioLinha, contagem, finalLinha, linhaAtual, nomeArquivoAtual

    Set fso2 = CreateObject("Scripting.FileSystemObject")
    Set f = fso2.OpenTextFile(arquivo, ForReading)
    nomeArquivoAtual = fso2.GetBaseName(objFile)

    Dim strMinuto, strHora, strDia, strDataHora

    dado = ""

    Do Until f.AtEndOfStream
        
        dado = dado & f.ReadLine 'lê o dado da linha e armazenada em dado para o tratamento

    Loop

    f.Close

    Dim aString, aSubString, findpos, findpos1
    Dim posMinuto, posHora, posDia, posFinal

    posMinuto = 0
    posHora = 0
    posDia = 0
    posFinal = 0
    
    Dim tamanhoTotal

    strMinuto = ""  
    strHora = ""  
    strDia = ""  
    strDataHora = ""  

    tamanhoTotal = Len(dado)

    wscript.echo tamanhoTotal

    aString = """dia_json"":{"
    posDia = InStr(dado, aString)

    aString = """hora_json"":{"
    posHora = InStr(dado, aString)

    aString = """minuto_json"":{"
    posMinuto = InStr(dado, aString)

    aString = ",},}}"
    posFinal = InStr(dado, aString)
    wscript.echo posFinal

    strDataHora = Mid(dado, 3, 19) 
    wscript.echo strDataHora

    If posDia <> 0 Then
        strDia = Replace(Mid(dado, posDia, tamanhoTotal),aString,",}")
        wscript.echo posDia
    End If
    
    
    If posHora <> 0 and posDia <> 0 Then
        strHora = Mid(dado, posHora, posDia - posHora - 1)
        wscript.echo posHora
    ElseIf posHora <> 0 Then
        strHora = Replace(Mid(dado, posHora, tamanhoTotal - 3),aString,",}")
    End If

    
    

     
    If posMinuto <> 0 and posHora <> 0 Then
        strMinuto = Mid(dado, posMinuto, posHora - posMinuto - 1) 
        wscript.echo posMinuto
    Else
        strMinuto = Mid(dado, posMinuto, tamanhoTotal - 3) 
    End If
  
    
    retornaDatabasePelaData nomeArquivoAtual 

    AbrirBanco

    armazenaDadosNoBanco strDataHora, strMinuto, strHora, strDia

    FecharBanco

    'Erase dadosArquivo

End Sub

Function armazenaDadosNoBanco(strDataHora, strMinuto, strHora, strDia)

    sqlQuery = "SELECT * FROM processo_valores WHERE datahora='" & strDataHora & "'"

    If (NOT executaQuerySeExiste (sqlQuery) ) Then

        sqlQuery = "INSERT INTO processo_valores VALUES ('" & strDataHora & "'"
        sqlQuery = sqlQuery & ",'" & strMinuto & "'"
        If (strHora<>"") Then
        sqlQuery = sqlQuery & ",'" & strHora & "'"
        Else
        sqlQuery = sqlQuery & ",''"
        End IF
        If (strDia<>"") Then
        sqlQuery = sqlQuery & ",'" & strDia & "'"
        Else
        sqlQuery = sqlQuery & ",''"
        End IF
        sqlQuery = sqlQuery & ")"

    Else

        sqlQuery = "UPDATE processo_valores SET minuto_json='" & strMinuto & "'"
        If (strHora<>"") Then
        sqlQuery = sqlQuery & ",hora_json='" & strHora & "'"
        End IF
        If (strDia<>"") Then
        sqlQuery = sqlQuery & ",dia_json='" & strDia & "'"
        End IF
        sqlQuery = sqlQuery & "Where datahora='" & strDataHora & "'"

    End If

    
    'wscript.echo sqlQuery

    'myRecordset.Open sqlQuery,objConnection, adOpenStatic, adLockOptimistic

    wscript.echo executaQueryAffected(sqlQuery)

End Function

Function executaQueryAffected (queryAExecutar)

    Dim recordsAffected   

    recordsAffected = 0

    On Error Resume Next

    'Usa-se esse metodo de execução poder ter o retorno das linhas afetadas, com o recordset não consegui implementar
    objConnection.Execute queryAExecutar, recordsAffected

    If Err.Number <> 0 Then
        WScript.Echo "Error in DoStep1: " & Err.Description
        Err.Clear
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0

    wscript.echo "teste"

    'first of all determine whether there are any records 
    If recordsAffected > 0 Then
        executaQueryAffected = true
    Else 
        executaQueryAffected = false    
    End If

End Function

Function executaQueryReturnID (queryAExecutar)

    Dim recordsAffected   

    recordsAffected = 0

    'queryAExecutar = queryAExecutar & "; SELECT @@IDENTITY AS NewID;"

    Set myRecordset = CreateObject("ADODB.Recordset")    

    myRecordset.Open queryAExecutar, objConnection, adOpenStatic, adLockOptimistic

    myRecordset.Open "SELECT @@IDENTITY AS NewID", objConnection, adOpenStatic, adLockOptimistic

    'first of all determine whether there are any records 
    If myRecordset.EOF Then
        executaQueryReturnID = false
    Else  
        ' idAmostraInserida = myRecordset.Fields("NewID").value
        'wscript.echo idAmostraInserida
        
        executaQueryReturnID = true    
    End If

    

End Function

Function executaQuerySeExiste (queryAExecutar)

    Set myRecordset = CreateObject("ADODB.Recordset")    

    myRecordset.Open queryAExecutar, objConnection, adOpenStatic, adLockOptimistic

    'first of all determine whether there are any records 
    If myRecordset.EOF Then
        executaQuerySeExiste = false
    Else       
        executaQuerySeExiste = true
    End If

End Function

Function procuraDataMaxima()
    wscript.echo converteDataHora( now )
End Function

Function converteData(dtTemp)
    On Error Resume Next
    Dim datahoraTemp : datahoraTemp = ""
    Dim dt
    dt = CDate(dtTemp)
    datahoraTemp = Year(dt) & "-" 
    if (Month(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Month(dt) & "-"
    if (Day(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    if (Day(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Day(dt)
    
    converteData = datahoraTemp

    If Err.Number <> 0 Then
        WScript.Echo "Error in DoStep1: " & Err.Description
        Err.Clear
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0

End Function

Function converteDataHora(dtTemp)
    On Error Resume Next
    Dim datahoraTemp : datahoraTemp = ""
    Dim dt
    dt = CDate(dtTemp)
    datahoraTemp = Year(dt) & "-" 
    if (Month(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Month(dt) & "-"
    if (Day(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Day(dt) & " "
    if (Hour(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Hour(dt) & ":"
    if (Minute(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Minute(dt) & ":"
    if (Second(dt)<10) then
        datahoraTemp = datahoraTemp & "0"
    End IF
    datahoraTemp = datahoraTemp & Second(dt)

    converteDataHora = datahoraTemp

    If Err.Number <> 0 Then
        WScript.Echo "Error in DoStep1: " & Err.Description
        Err.Clear
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0

End Function

Dim datahoraBuscaValMax, datahoraBuscaAnterior

Function buscaUltimoRegistroProcessos()
    On Error Resume Next
    retornaDatabasePelaData converteDataHora( datahoraBuscaValMax ) 
    AbrirBanco

    Set myRecordset = CreateObject("ADODB.Recordset") 

    if (bancoUsado = SQLServer) Then
        sqlQuery = "SELECT TOP 1 * FROM processo_valores ORDER BY datahora DESC"
    END IF
    if (bancoUsado = MariaDB) Then
        sqlQuery = "SELECT * FROM processo_valores ORDER BY datahora DESC LIMIT 1"
    END IF

    myRecordset.Open sqlQuery,objConnection, adOpenStatic, adLockOptimistic

    'first of all determine whether there are any records 
    If myRecordset.EOF Then
        wscript.echo "Nao existem Dados para esse banco"
        buscaUltimoRegistroProcessos = "Nao Encontrado"
        FecharBanco
    Else 
        'if there are records then loop through the fields 
        ' Do While NOT myRecordset.Eof  
            buscaUltimoRegistroProcessos = myRecordset.Fields.Item("datahora")
        '     myRecordset.MoveNext
        ' Loop      

    End If
    FecharBanco

    

    If Err.Number <> 0 Then
        WScript.Echo "Error in buscaUltimoRegistroProcessos: " & Err.Description
        Err.Clear
    End If

    'If you no longer want to continue following an error after that block's completed,
    'call this.
    On Error Goto 0
End Function


cont = 0

Dim dt, strdatahoraAtual
Dim dataMaxTemp

dt = now

datahoraBuscaValMax = converteDataHora(dt)

Do while dataMaxTemp = "Nao Encontrado"
    datahoraBuscaValMax = DateAdd("m",-1,datahoraBuscaValMax)
    wscript.echo "tentando nova data " & datahoraBuscaValMax
    dataMaxTemp = buscaUltimoRegistroProcessos
Loop

Do
    If Err.Number <> 0 Then
        wscript.echo "Error # " & Err.Number & ": " & Err.Description & " (" & Err.Source & ")"
    Else
        wscript.echo "Executando: " & cont
    End If

    Err.clear
    
    wscript.echo strFolder
    wscript.echo strFolder & "\GRD-JSON\" & CStr(Year(dt)) & "\" & converteData(dt)
    wscript.echo converteDataHora(dt)
    'wscript.echo buscaUltimoRegistroProcessos


    procuraArquivos "json", strFolder & "\GRD-JSON\" & CStr(Year(dt)) & "\" & converteData(dt)

    dt = DateAdd("d",-1,dt)

    cont = cont + 1
    If(cont>10)Then
        cont = 0
    End If

    wscript.Sleep 10000 

Loop