Public Const ForAppending = 8
Public Const ForReading = 1
Public Const ForWriting = 2
Public rutaLog
Public FichLog

Public cvsApp
Public cvsConn
Public cvsSrv
Public Rep
Public cat

Public MySQL

Public fso
Public dirRastreo
Public rutaInformeCMS
Public rutaExportCMS
Public ficheroExport
Public errConnect
public fechaReports
Public fechaMySQL
Public lstAgentes
Public lstSkills
Public lstVDN

Public shell

'Set shell = CreateObject("WScript.Shell")
'shell.Exec "C:\Windows\SysWOW64\Wscript.exe C:\Scripts\regScript.vbs " & WScript.ScriptName & " INICIO"

on Error resume Next
fechaReports = ""
fechaReports = WScript.Arguments.Item(0)
If fechaReports = "" Then fechaReports = Date - 1
on Error GoTo 0

'fechaReports = "07/09/2020"

arrFecha = Split(fechaReports, "/")
fechaMySQL = arrFecha(2) & "-" & arrFecha(1) & "-" & arrFecha(0)

Set fso = CreateObject("Scripting.FileSystemObject")
rutaLog = "C:\Logs"
'If (fso.FileExists(rutaLog & "\CMSmysql.txt")) Then fso.DeleteFile(rutaLog & "\CMSmysql.txt")
'Set FichLog = fso.CreateTextFile(rutaLog & "\CMSmysql.txt")
'FichLog.WriteLine "-- ARRANQUE DEL SCRIPT " & Date & "  " & Time & " --" & vbCrLf


Set MySQL = CreateObject("ADODB.Connection")
MySQL.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
                        "SERVER=172.15.138.14;" & _
                        "Port=3306;" & _
                        "UID=ODBCrun;PWD=PwdODBCrun!1; OPTION=3"
MySQL.Open

If conectarAcms1 Then
'	FichLog.WriteLine "-- Conecta CMS1 " & Date & "  " & Time & " --" & vbCrLf
	ABNskill(1)
	ACDskill(1)
	llamadasSkills(1)
	llamadasSkills2(1)
	llamadasVDN(1)
	tiemposAgente(1)
	reportAgntXskill(1)
	reportSkillXagente(1)
	
	liberarCMS
Else
	liberarCMS
	MsgBox "NO CONECTA CMS1: " & errConnect
End If

If conectarAcms3 Then	
'	FichLog.WriteLine "-- Conecta CMS3 " & Date & "  " & Time & " --" & vbCrLf
	ABNskill(3)
	ACDskill(3)
	llamadasSkills(3)
	llamadasSkills2(3)
	llamadasVDN(3)
	tiemposAgente(3)
	reportAgntXskill(3)
	reportSkillXagente(3)

	liberarCMS
Else
	liberarCMS
	MsgBox "NO CONECTA CMS3: " & errConnect
End If

'shell.Exec "C:\Windows\SysWOW64\Wscript.exe C:\Scripts\regScript.vbs " & WScript.ScriptName & " FIN"

Function conectarAcms1
	Set cvsApp = CreateObject("ACSUP.cvsApplication")
	Set cvsConn = CreateObject("ACSCN.cvsConnection")
	Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set cat = CreateObject("ACSCTLG.cvsCatalog")
	
	serverAddress = "10.177.3.103"
	serverAddress = "10.177.129.103"
	UserName = "Khar1"
	passW = "Khar101" 
	conectarAcms1 = False
	
	on Error resume Next
	if cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ES", cvsSrv, cvsConn) Then
		if cvsConn.Login(UserName, passW, serverAddress, "ES") Then
			cat.ACD = 1
			cvsSrv.Reports.ACD = 1
			conectarAcms1 = True
		End If
	End If
	If err.number <> 0 Then errConnect = err.Description
	on Error GoTo 0
	
End Function

Function conectarAcms3
	Set cvsApp = CreateObject("ACSUP.cvsApplication")
	Set cvsConn = CreateObject("ACSCN.cvsConnection")
	Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set cat = CreateObject("ACSCTLG.cvsCatalog")
	
	serverAddress = "10.177.3.103"
	serverAddress = "10.177.129.103"
	UserName = "Kadlaut1"
	passW = "Kadlaut101" 
	conectarAcms3 = False
	
	on Error resume Next
	if cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ES", cvsSrv, cvsConn) Then
		if cvsConn.Login(UserName, passW, serverAddress, "ES") Then
			cat.ACD = 3
			cvsSrv.Reports.ACD = 3
			conectarAcms3 = True
		End If
	End If
	If err.number <> 0 Then errConnect = err.Description
	on Error GoTo 0
	
End Function

Function ABNskill(acd)
	rutaInformeCMS = "Historical\Designer\RPT_ABNskillPeriodo"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_ABNskill_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
	
'	Set qrySkills = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idSkill " & _
			 "FROM CMS.skills " & _
			 "WHERE ACD = " & acd
'	qrySkills.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qrySkills = MySQL.Execute(txtSQL)
	lstSkills = ""
	While Not qrySkills.EOF
		lstSkills = lstSkills & qrySkills("idSkill") & ";"
		qrySkills.MoveNext
	Wend
'	FichLog.WriteLine "-- informe ABNskill " & acd & "  " & rutaInformeCMS & " -- " & lstSkills & " --" & vbCrLf
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Splits/skills", lstSkills
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarABNskills
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarABNskills
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.abnskill " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "'," & _
        			 "'" & arrFila(12) & "'," & _
        			 "'" & arrFila(13) & "'," & _
        			 "'" & arrFila(14) & "'," & _
        			 "'" & arrFila(15) & "'," & _
        			 "'" & arrFila(16) & "'," & _
        			 "'" & arrFila(17) & "'," & _
        			 "'" & arrFila(18) & "'," & _
        			 "'" & arrFila(19) & "'," & _
        			 "'" & arrFila(21) & "')"
        	MySQL.Execute txtSQL
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.abnskill " & _
        				 "SET abnTOT = '" & arrFila(1) & "', " & _
        				 "P1 = '" & arrFila(2) & "', " & _
        				 "ABN1 = '" & arrFila(3) & "', " & _
        				 "P2 = '" & arrFila(4) & "', " & _
        				 "ABN2 = '" & arrFila(5) & "', " & _
        				 "P3 = '" & arrFila(6) & "', " & _
        				 "ABN3 = '" & arrFila(7) & "', " & _
        				 "P4 = '" & arrFila(8) & "', " & _
        				 "ABN4 = '" & arrFila(9) & "', " & _
        				 "P5 = '" & arrFila(10) & "', " & _
        				 "ABN5 = '" & arrFila(11) & "', " & _
        				 "P6 = '" & arrFila(12) & "', " & _
        				 "ABN6 = '" & arrFila(13) & "', " & _
        				 "P7 = '" & arrFila(14) & "', " & _
        				 "ABN7 = '" & arrFila(15) & "', " & _
        				 "P8 = '" & arrFila(16) & "', " & _
        				 "ABN8 = '" & arrFila(17) & "', " & _
        				 "P9 = '" & arrFila(18) & "', " & _
        				 "ABN9 = '" & arrFila(19) & "', " & _
        				 "ABNsup = '" & arrFila(21) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idSkill = '" & arrFila(0) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
        	
    	End If	
    	fila = fila + 1	
    
    Wend
    
End Function

Function ACDskill(acd)
	rutaInformeCMS = "Historical\Designer\RPT_ACDskillPeriodo"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_ACDskill_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
	
'	Set qrySkills = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idSkill " & _
			 "FROM CMS.skills " & _
			 "WHERE ACD = " & acd
'	qrySkills.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qrySkills = MySQL.Execute(txtSQL)
	lstSkills = ""
	While Not qrySkills.EOF
		lstSkills = lstSkills & qrySkills("idSkill") & ";"
		qrySkills.MoveNext
	Wend
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Splits/skills", lstSkills
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarACDskills
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarACDskills
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.acdskill " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "'," & _
        			 "'" & arrFila(12) & "'," & _
        			 "'" & arrFila(13) & "'," & _
        			 "'" & arrFila(14) & "'," & _
        			 "'" & arrFila(15) & "'," & _
        			 "'" & arrFila(16) & "'," & _
        			 "'" & arrFila(17) & "'," & _
        			 "'" & arrFila(18) & "'," & _
        			 "'" & arrFila(19) & "'," & _
        			 "'" & arrFila(21) & "')"
        	MySQL.Execute txtSQL
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.acdskill " & _
        				 "SET acdTOT = '" & arrFila(1) & "', " & _
        				 "P1 = '" & arrFila(2) & "', " & _
        				 "ACD1 = '" & arrFila(3) & "', " & _
        				 "P2 = '" & arrFila(4) & "', " & _
        				 "ACD2 = '" & arrFila(5) & "', " & _
        				 "P3 = '" & arrFila(6) & "', " & _
        				 "ACD3 = '" & arrFila(7) & "', " & _
        				 "P4 = '" & arrFila(8) & "', " & _
        				 "ACD4 = '" & arrFila(9) & "', " & _
        				 "P5 = '" & arrFila(10) & "', " & _
        				 "ACD5 = '" & arrFila(11) & "', " & _
        				 "P6 = '" & arrFila(12) & "', " & _
        				 "ACD6 = '" & arrFila(13) & "', " & _
        				 "P7 = '" & arrFila(14) & "', " & _
        				 "ACD7 = '" & arrFila(15) & "', " & _
        				 "P8 = '" & arrFila(16) & "', " & _
        				 "ACD8 = '" & arrFila(17) & "', " & _
        				 "P9 = '" & arrFila(18) & "', " & _
        				 "ACD9 = '" & arrFila(19) & "', " & _
        				 "ACDsup = '" & arrFila(21) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idSkill = '" & arrFila(0) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
        	
    	End If	
    	fila = fila + 1	
    
    Wend
    
End Function

Function llamadasSkills(acd)
	rutaInformeCMS = "Historical\Designer\RPT_LlamasSkill"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_LlamadasSkill_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
'	Set qrySkill = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idSkill " & _
			 "FROM CMS.skills " & _
			 "WHERE ACD = " & acd
'	qrySkill.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qrySkill = MySQL.Execute(txtSQL)
	lstSkills = ""
	While Not qrySkill.EOF
		lstSkills = lstSkills & qrySkill("idSkill") & ";"
		qrySkill.MoveNext
	Wend
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Splits/skills", lstSkills
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarLlamadasSkill
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarLlamadasSkill
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.llamadasskill " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "'," & _
        			 "'" & arrFila(12) & "'," & _
        			 "'" & arrFila(13) & "'," & _
        			 "'" & arrFila(14) & "')"
        	MySQL.Execute txtSQL
        	
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.llamadasskill " & _
        				 "SET ACD = '" & arrFila(2) & "', " & _
        				 "Vresp = '" & arrFila(3) & "', " & _
        				 "TMOacd = '" & arrFila(4) & "', " & _
        				 "TMOacw = '" & arrFila(5) & "', " & _
        				 "ABN = '" & arrFila(6) & "', " & _
        				 "TMOabn = '" & arrFila(7) & "', " & _
        				 "EXTNin = '" & arrFila(8) & "', " & _
        				 "TMOextnin = '" & arrFila(9) & "', " & _
        				 "oACD = '" & arrFila(10) & "', " & _
        				 "TMOoacd = '" & arrFila(11) & "', " & _
        				 "TMOoacw = '" & arrFila(12) & "', " & _
        				 "EXTNout = '" & arrFila(13) & "', " & _
        				 "TMOextnout = '" & arrFila(14) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idSkill = '" & arrFila(0) & "' " & _
        				 "AND franja = '" & arrFila(1) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
    	End If	
    	fila = fila + 1	
    
    Wend

End Function

Function llamadasSkills2(acd)
	rutaInformeCMS = "Historical\Designer\RPT_LlamasSkill2"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_LlamadasSkill2_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
'	Set qrySkill = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idSkill " & _
			 "FROM CMS.skills " & _
			 "WHERE ACD = " & acd
'	qrySkill.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qrySkill = MySQL.Execute(txtSQL)
	lstSkills = ""
	While Not qrySkill.EOF
		lstSkills = lstSkills & qrySkill("idSkill") & ";"
		qrySkill.MoveNext
	Wend
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Splits/skills", lstSkills
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarLlamadasSkill2
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarLlamadasSkill2
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.llamadasskill2 " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "'," & _
        			 "'" & arrFila(12) & "'," & _
        			 "'" & arrFila(13) & "'," & _
        			 "'" & arrFila(14) & "'," & _
        			 "'" & arrFila(15) & "'," & _
        			 "'" & arrFila(16) & "'," & _
        			 "'" & arrFila(17) & "'," & _
        			 "'" & arrFila(18) & "'," & _
        			 "'" & arrFila(19) & "'," & _
        			 "'" & arrFila(20) & "'," & _
        			 "'" & arrFila(21) & "'," & _
        			 "'" & arrFila(22) & "'," & _
        			 "'" & arrFila(23) & "'," & _
        			 "'" & arrFila(24) & "'," & _
        			 "'" & arrFila(25) & "'," & _
        			 "'" & arrFila(26) & "')"
        	MySQL.Execute txtSQL
        	
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.llamadasskill2 " & _
        				 "SET ACD = '" & arrFila(2) & "', " & _
        				 "ABN = '" & arrFila(3) & "', " & _
        				 "EXTNin = '" & arrFila(4) & "', " & _
        				 "oACD = '" & arrFila(5) & "', " & _
        				 "EXTNout = '" & arrFila(6) & "', " & _
        				 "ABN1 = '" & arrFila(7) & "', " & _
        				 "ABN2 = '" & arrFila(8) & "', " & _
        				 "ABN3 = '" & arrFila(9) & "', " & _
        				 "ABN4 = '" & arrFila(10) & "', " & _
        				 "ABN5 = '" & arrFila(11) & "', " & _
        				 "ABN6 = '" & arrFila(12) & "', " & _
        				 "ABN7 = '" & arrFila(13) & "', " & _
        				 "ABN8 = '" & arrFila(14) & "', " & _
        				 "ABN9 = '" & arrFila(15) & "', " & _
        				 "ABNsup = '" & arrFila(16) & "', " & _
        				 "ACD1 = '" & arrFila(17) & "', " & _
        				 "ACD2 = '" & arrFila(18) & "', " & _
        				 "ACD3 = '" & arrFila(19) & "', " & _
        				 "ACD4 = '" & arrFila(20) & "', " & _
        				 "ACD5 = '" & arrFila(21) & "', " & _
        				 "ACD6 = '" & arrFila(22) & "', " & _
        				 "ACD7 = '" & arrFila(23) & "', " & _
        				 "ACD8 = '" & arrFila(24) & "', " & _
        				 "ACD9 = '" & arrFila(25) & "', " & _
        				 "ACDsup = '" & arrFila(26) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idSkill = '" & arrFila(0) & "' " & _
        				 "AND franja = '" & arrFila(1) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
    	End If	
    	fila = fila + 1	
    
    Wend

End Function


Function llamadasVDN(acd)
	rutaInformeCMS = "Historical\Designer\RPT_VDNskillIntervalo"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_LlamadasVDN_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
'	Set qryVDN = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idVDN " & _
			 "FROM CMS.vdns " & _
			 "WHERE ACD = " & acd
'	qryVDN.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qryVDN = MySQL.Execute(txtSQL)
	lstVDN = ""
	While Not qryVDN.EOF
		lstVDN = lstVDN & qryVDN("idVDN") & ";"
		qryVDN.MoveNext
	Wend
	
	If lstVDN = "" Then Exit Function
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "VDN", lstVDN
		Rep.SetProperty "Fecha", fechaReports
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarLlamadasVDN
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarLlamadasVDN
    Dim ficheroInforme
    If Not fso.FileExists(ficheroExport) Then Exit Function
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.llamadasvdn " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(11) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(12) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(10) & "')"
        	MySQL.Execute txtSQL
        	
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.llamadasvdn " & _
        				 "SET entrantes = '" & arrFila(6) & "', " & _
        				 "ACD = '" & arrFila(7) & "', " & _
        				 "ABN = '" & arrFila(11) & "', " & _
        				 "tVDN = '" & arrFila(9) & "', " & _
        				 "tABN = '" & arrFila(12) & "', " & _
        				 "FLWin = '" & arrFila(8) & "', " & _
        				 "FLWout = '" & arrFila(10) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idVDN = '" & arrFila(0) & "' " & _
        				 "AND franja = '" & arrFila(1) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
    	End If	
    	fila = fila + 1	
    
    Wend
End Function


Function tiemposAgente(acd)
	rutaInformeCMS = "Historical\Designer\RPT_TiemposTrabajoAgentes"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_TiemposAgente_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
'	Set qryAgentes = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT login " & _
			 "FROM CMS.agentes " & _
			 "WHERE estado " & _
			 "AND ACD = " & acd
'	qryAgentes.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qryAgentes = MySQL.Execute(txtSQL)
	lstAgentes = ""
	While Not qryAgentes.EOF
		lstAgentes = lstAgentes & qryAgentes("login") & ";"
		qryAgentes.MoveNext
	Wend
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Agentes", lstAgentes
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarTiemposAgente
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing	

End Function

Function cargarTiemposAgente
	Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    tagnt = True
    tdesCon = False
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	If arrFila(0) = "Identif. de conexión" Then
        		tagnt = False
    			tdesCon = True
        	Else
        		If tagnt Then
        			on Error resume Next
        			txtSQL = "INSERT INTO CMS.tiemposagentes " & _
        					 "VALUES ('" & fechaMySQL & "','" & arrFila(0) &"','" & arrFila(1) & "','" & arrFila(2) & "','" & arrFila(3) & "','" & arrFila(4) & "')"
        			MySQL.Execute txtSQL
        			If err.number <> 0 Then
        				on Error GoTo 0
        				txtSQL = "UPDATE CMS.tiemposagentes " & _
        						 "SET Tlog = '" & arrFila(1) & "', " & _
        						 "Tdisp = '" & arrFila(2) & "', " & _
        						 "Taux = '" & arrFila(3) & "', " & _
        						 "Tothr = '" & arrFila(4) & "' " & _
        						 "WHERE fecha = '" & fechaMySQL & "' " & _
        						 "AND idCMS = '" & arrFila(0) & "'"
        				MySQL.Execute txtSQL
        			End If
        		Else
        			on Error resume Next
        			txtSQL = "INSERT INTO CMS.conndescagentes " & _
        					 "VALUES ('" & fechaMySQL & "','" & arrFila(0) &"','" & arrFila(1) & "','" & arrFila(2) & "')"
        			MySQL.Execute txtSQL
        			If err.number <> 0 Then
        				txtSQL = "UPDATE CMS.conndescagentes " & _
        						 "SET hIN = '" & arrFila(1) & "', " & _
        						 "hOUT = '" & arrFila(2) & "' " & _
        						 "WHERE fecha = '" & fechaMySQL & "' " & _
        						 "AND idCMS = '" & arrFila(0) & "'"
        				MySQL.Execute txtSQL	
        				on Error GoTo 0
        			End If
        		End If
        	End If
        	on Error GoTo 0
        End If
        fila = fila + 1
    Wend

End Function

Function reportAgntXskill(acd)
	rutaInformeCMS = "Historical\Designer\RPT_llamadasAgntesXskill"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_AgenteXskill_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
	
'	Set qryAgentes = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT login " & _
			 "FROM CMS.agentes " & _
			 "WHERE estado " & _
			 "AND ACD = " & acd
'	qryAgentes.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qryAgentes = MySQL.Execute(txtSQL)
	lstAgentes = ""
	While Not qryAgentes.EOF
		lstAgentes = lstAgentes & qryAgentes("login") & ";"
		qryAgentes.MoveNext
	Wend
	
	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Fecha", fechaReports
		Rep.SetProperty "Agentes", lstAgentes
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarFicheroagntXskill
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarFicheroagntXskill
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.llamadasagentes " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "')"
        	MySQL.Execute txtSQL
        	
        	If err.number <> 0 Then
        		ON Error GoTo 0
        		txtSQL = "UPDATE CMS.llamadasagentes " & _
        				 "SET ACDcalls = '" & arrFila(2) & "', " & _
        				 "TMOacd = '" & arrFila(3) & "', " & _
        				 "TMOacw = '" & arrFila(4) & "', " & _
        				 "oACDcalls = '" & arrFila(5) & "', " & _
        				 "TMOoACD = '" & arrFila(6) & "', " & _
        				 "TMOoACW = '" & arrFila(7) & "', " & _
        				 "EXTNcalls = '" & arrFila(8) & "', " & _
        				 "TMOextn = '" & arrFila(9) & "', " & _
        				 "EXTNoCalls = '" & arrFila(10) & "', " & _
        				 "TMOoExtn = '" & arrFila(11) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idCMS = '" & arrFila(0) & "' " & _
        				 "AND idSkill = '" & arrFila(1) & "'"
        		MySQL.Execute txtSQL
        	End If
        	ON Error GoTo 0
    	End If	
    	fila = fila + 1	
    
    Wend

End Function

Function reportSkillXagente(acd)
	rutaInformeCMS = "Historical\Designer\RPT_LlamadasSkillXagente_AUTR"
	rutaExportCMS = "N:\AUTORENTING\Agentes\OSS\Desarrollo\CMS Nuevo\Extract\MySQL\"
	nombreFichero = fechaMySQL & "_SkillXagente_V" & acd & ".txt"
	ficheroExport = rutaExportCMS & nombreFichero
	
'	Set qryAgentes = CreateObject("ADODB.Recordset")
	txtSQL = "SELECT idSkill " & _
			 "FROM CMS.skills " & _
			 "WHERE ACD = " & acd
'	qryAgentes.Open txtSQL, MySQL, adOpenForwardOnly, adLockReadOnly
	Set qryAgentes = MySQL.Execute(txtSQL)
	lstAgentes = ""
	While Not qryAgentes.EOF
		lstAgentes = lstAgentes & qryAgentes("idSkill") & ";"
		qryAgentes.MoveNext
	Wend

	Set Rep = CreateObject("ACSREP.cvsReport")
	Set Info = cvsSrv.Reports.Reports(rutaInformeCMS)
	informe = cvsSrv.Reports.CreateReport(Info, Rep)
	If informe Then
		Rep.SetProperty "Splits/skills", lstAgentes
		Rep.SetProperty "Fecha", fechaReports
		informe = Rep.ExportData(ficheroExport, 9, 0, False, True, True)
		Set Info = Nothing
		cargarFicheroSkillXagente
	Else
		MsgBox "No se encuentra el informe " & rutaInformeCMS & " en la centralita", vbExclamation
	End If
	Set Info = Nothing
    rep.Quit
    Set Rep = Nothing
	

End Function

Function cargarFicheroSkillXagente
    Dim ficheroInforme
    Set ficheroInforme = fso.OpenTextFile(ficheroExport, ForReading)
    errINFko = 0
    errDesc = ""
    fila = 1
    While Not ficheroInforme.AtEndOfStream
    	txtFila = ficheroInforme.ReadLine
    	If fila > 1 Then
        	arrFila = Split(txtFila, Chr(9))
        	on Error resume Next
        	txtSQL = "INSERT INTO CMS.llamadasagentes " & _
        			 "VALUES ('" & fechaMySQL & "'," & _
        			 "'" & arrFila(0) & "'," & _
        			 "'" & arrFila(1) & "'," & _
        			 "'" & arrFila(2) & "'," & _
        			 "'" & arrFila(3) & "'," & _
        			 "'" & arrFila(4) & "'," & _
        			 "'" & arrFila(5) & "'," & _
        			 "'" & arrFila(6) & "'," & _
        			 "'" & arrFila(7) & "'," & _
        			 "'" & arrFila(8) & "'," & _
        			 "'" & arrFila(9) & "'," & _
        			 "'" & arrFila(10) & "'," & _
        			 "'" & arrFila(11) & "')"
        	MySQL.Execute txtSQL
        	
        	If err.number <> 0 Then
        		'ON Error GoTo 0
        		err.Clear
'        		FichLog.WriteLine "-- CargarFicheroSkill Agente " & ficheroExport & "  " & fila & " -- " & txtFila & " --" & vbCrLf
        		txtSQL = "UPDATE CMS.llamadasagentes " & _
        				 "SET ACDcalls = '" & arrFila(2) & "', " & _
        				 "TMOacd = '" & arrFila(3) & "', " & _
        				 "TMOacw = '" & arrFila(4) & "', " & _
        				 "oACDcalls = '" & arrFila(5) & "', " & _
        				 "TMOoACD = '" & arrFila(6) & "', " & _
        				 "TMOoACW = '" & arrFila(7) & "', " & _
        				 "EXTNcalls = '" & arrFila(8) & "', " & _
        				 "TMOextn = '" & arrFila(9) & "', " & _
        				 "EXTNoCalls = '" & arrFila(10) & "', " & _
        				 "TMOoExtn = '" & arrFila(11) & "' " & _
        				 "WHERE fecha = '" & fechaMySQL & "' " & _
        				 "AND idCMS = '" & arrFila(0) & "' " & _
        				 "AND idSkill = '" & arrFila(1) & "'"
        		MySQL.Execute txtSQL
        		If err.number <> 0 Then
        			errINFko = 1
        			errDesc = err.Description
        		End If
        	End If
        	ON Error GoTo 0
    	End If	
    	fila = fila + 1	
    
    Wend
	If errINFko = 1 Then MsgBox "Comprobar en CMS el informe SkillXagente que esten todas las columnas" & vbCrLf & errDesc
End Function

Function liberarCMS
	On Error Resume Next
	Rep.Quit
	cvsConn.Logout
	cvsConn.Disconnect
	cvsSrv.Connected = False
	Set cvsApp = Nothing
	Set cvsConn = Nothing
	Set cvsSrv = Nothing
	Set Rep = Nothing
	Set cat = Nothing
	On Error Goto 0
End Function