Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmImportaCoordenadas
	Inherits System.Windows.Forms.Form
	Private ifrmDir As frmDir
	Private istrProdistUserSgd As String
	Private istrProdistPassSgd As String
	Private istrProdistUserBdgd As String
	Private istrProdistPassBdgd As String
	Private istrProdistTnsNames As String
	
	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Call Me.Close()
	End Sub
	
	Private Sub cmdPasta_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdPasta.Click
		With Me.cmdPasta
			Me.Enabled = False
			ifrmDir.frmQuemChama = Me
			ifrmDir.intTipo = 1
			Call ifrmDir.SetBounds(VB6.TwipsToPixelsX(VB6.PixelsToTwipsX(Me.Left) + VB6.PixelsToTwipsX(.Left) + 400), VB6.TwipsToPixelsY(VB6.PixelsToTwipsY(Me.Top) + VB6.PixelsToTwipsY(.Top) + 400), 0, 0, Windows.Forms.BoundsSpecified.X Or Windows.Forms.BoundsSpecified.Y)
			Call ifrmDir.ShowDialog()
		End With
	End Sub
	
	Private Sub frmImportaCoordenadas_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Dim lstrSql As String
		Call CenterForm(Me)
		ifrmDir = New frmDir
		'
		istrProdistUserSgd = Trim(LeConfig("PRODIST_USUARIO_SGD"))
		If istrProdistUserSgd = "-1" Then
			istrProdistUserSgd = LCase(gstrEmpresa_Sigla) & "flx_sgd"
			Call CriaParConfig("PRODIST_USUARIO_SGD", istrProdistUserSgd, False, "Usuário do banco de dados do SGD")
		ElseIf istrProdistUserSgd = "" Then 
			istrProdistUserSgd = UCase(gstrEmpresa_Sigla) & "flx_sgd"
			Call GravaConfig("PRODIST_USUARIO_SGD", istrProdistUserSgd)
		End If
		'
		istrProdistPassSgd = Trim(LeConfig("PRODIST_SENHA_SGD"))
		If istrProdistPassSgd = "-1" Then
			istrProdistPassSgd = "brutal"
			Call CriaParConfig("PRODIST_SENHA_SGD", istrProdistPassSgd, False, "Senha do usuário do banco de dados do SGD")
		ElseIf istrProdistPassSgd = "" Then 
			istrProdistPassSgd = "brutal"
			Call GravaConfig("PRODIST_SENHA_SGD", istrProdistPassSgd)
		End If
		'
		istrProdistTnsNames = Trim(LeConfig("COORDENADAS_CAMINHO_TNSNAMES"))
		If istrProdistTnsNames = "-1" Then
			istrProdistTnsNames = ""
			Call CriaParConfig("COORDENADAS_CAMINHO_TNSNAMES", istrProdistTnsNames, False, "Caminho do TNSNAMES.ORA")
		ElseIf istrProdistTnsNames = "" Then 
			istrProdistTnsNames = ""
			Call GravaConfig("COORDENADAS_CAMINHO_TNSNAMES", istrProdistTnsNames)
		End If
		'
		Me.txtUserSgd.Text = istrProdistUserSgd
		Me.txtPassSgd.Text = istrProdistPassSgd
		If istrProdistTnsNames <> "" Then
			Me.txtDir.Text = istrProdistTnsNames
		End If
		'
	End Sub
	
	Private Sub frmImportaCoordenadas_FormClosed(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
		'UPGRADE_NOTE: Object ifrmDir may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
		ifrmDir = Nothing
	End Sub
	
	'UPGRADE_WARNING: Event txtDir.TextChanged may fire when form is initialized. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="88B12AE1-6DE0-48A0-86F1-60C0686C026A"'
	Private Sub txtDir_TextChanged(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtDir.TextChanged
		Dim lstrArq As String
		Dim lstrCam As String
		lstrCam = Me.txtDir.Text & IIf(VB.Right(Trim(Me.txtDir.Text), 1) <> "\", "\", "")
		lstrArq = lstrCam & "tnsnames.ora"
		'UPGRADE_WARNING: Dir has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"'
		If Trim(Dir(lstrArq)) <> "" Then
			Call CarregaArquivo(lstrArq, (Me.cmbBaseDados))
			Call GravaConfig("COORDENADAS_CAMINHO_TNSNAMES", Replace(Me.txtDir.Text & IIf(VB.Right(Trim(Me.txtDir.Text), 1) <> "\", "\", ""), "\", "\\"))
		Else
			Call MsgBox("Caminho do TNSNAME.ORA inválido!", MsgBoxStyle.Exclamation, gstrTituloMensagem)
		End If
	End Sub
	
	Private Sub cmdProcessar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdProcessar.Click
		Call LigDes(False)
		If BuscaDadosdoSgd Then
			Call AtualizaAlimentador()
			Call MsgBox("Processo finalizado.", MsgBoxStyle.Information, gstrTituloMensagem)
		End If
		Call LigDes(True)
	End Sub
	
	Private Sub LigDes(ByVal ablnModo As Boolean)
		Me.cmdPasta.Enabled = ablnModo
		Me.txtUserSgd.Enabled = ablnModo
		Me.txtPassSgd.Enabled = ablnModo
		Me.cmbBaseDados.Enabled = ablnModo
		Me.cmdProcessar.Enabled = ablnModo
		Me.cmdFechar.Enabled = ablnModo
	End Sub
	
	Private Sub CarregaArquivo(ByVal astrArq As String, ByRef acmbAux As System.Windows.Forms.ComboBox)
		Dim lstrLin As String
		Dim lintARQ As Short
		Dim lintAux As Short
		Dim larrIte() As String
		Dim llngTotLin As Integer
		Dim lintLin As Short
		Dim lstrAux As String
		'
		On Error GoTo ErrorCarregaArquivo
		llngTotLin = 0
		lintLin = 0
		'
		'Conta quantidade de linhas do arquivo
		lintARQ = FreeFile
		FileOpen(lintARQ, astrArq, OpenMode.Input)
		While Not EOF(lintARQ)
			lstrLin = LineInput(lintARQ)
			llngTotLin = llngTotLin + 1
			System.Windows.Forms.Application.DoEvents()
		End While
		FileClose(lintARQ)
		With acmbAux
			Call .Items.Clear()
		End With
		lintARQ = FreeFile
		FileOpen(lintARQ, astrArq, OpenMode.Input)
		Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, llngTotLin)
		While Not EOF(lintARQ)
			lstrLin = LineInput(lintARQ)
			If InStr(1, lstrLin, "SERVICE_NAME") > 0 Then
				lstrAux = Replace(Replace(Replace(Replace(Trim(lstrLin), "(", ""), ")", ""), "SERVICE_NAME", ""), "=", "")
				With acmbAux
					Call .Items.Add(Trim(lstrAux))
				End With
			End If
			If InStr(1, lstrLin, "SID") > 0 Then
				lstrAux = Replace(Replace(Replace(Replace(Trim(lstrLin), "(", ""), ")", ""), "SID", ""), "=", "")
				With acmbAux
					Call .Items.Add(Trim(lstrAux))
				End With
			End If
			lintLin = lintLin + 1
			Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
		End While
		With acmbAux
			If .Items.Count > 1 Then
				.SelectedIndex = 0
			Else
				.SelectedIndex = -1
			End If
		End With
ErrorCarregaArquivo: 
		FileClose(lintARQ)
		If Err.Number > 0 Then
			Call MsgBox(Err.Number & " - " & GetErrosVB(Err.Number, Err.Description), MsgBoxStyle.Exclamation, gstrTituloMensagem)
		End If
	End Sub
	
	Private Function BuscaDadosdoSgd() As Boolean
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim lstrContaGD As String
		Dim lstrGrupo As String
		Dim ldblNox As Double
		Dim ldblNoY As Double
		Dim ldblLat As Double
		Dim ldblLon As Double
		Dim lblnRet As Boolean
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		On Error GoTo ErroBuscaDadoSGD
		lblnRet = True
		Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
		Me.lblInfo.Text = " Aguarde, conectando no oracle para buscar os consumidores LT"
		Call Me.lblInfo.Refresh()
		'
		Call CarregaOracleProvider(VB6.GetItemString(Me.cmbBaseDados, Me.cmbBaseDados.SelectedIndex), UCase(Me.txtUserSgd.Text), Me.txtPassSgd.Text)
		lstrSql = ""
		lstrSql = lstrSql & "SELECT NOX,NOY,CONTA,GRUPO FROM " & UCase(Me.txtUserSgd.Text) & ".CONSUMIDOR_LT UNION "
		lstrSql = lstrSql & "SELECT NOX,NOY,CONTA,GRUPO FROM " & UCase(Me.txtUserSgd.Text) & ".CRPENDENTE_LT WHERE NOX > 0 AND NOY > 0 "
		Call ConectarOrcl(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount, " (SGD) ")
					'Call Executar("START TRANSACTION", , , False)
					While Not .EOF
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						ldblNox = IIf(IsDbNull(.Fields("NOX").value), 0, .Fields("NOX").value)
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						ldblNoY = IIf(IsDbNull(.Fields("NOY").value), 0, .Fields("NOY").value)
						Call mdUtmLatLog.MontaCoordenadas(ldblNox, ldblNoY, ldblLat, ldblLon)
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						lstrContaGD = IIf(IsDbNull(.Fields("CONTA").value), "", .Fields("CONTA").value)
						lstrZona = ""
						lstrLivro = ""
						lstrConta = ""
						If Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")) > 0 Then 'Se tiver Z na mascara
							lstrZona = VB.Left(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")))
						End If
						If Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "C", "")) > 0 Then 'Se tiver L na mascara
							If Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")) > 0 Then
								lstrLivro = Mid(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")), Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "C", "")))
							Else
								lstrLivro = VB.Left(lstrContaGD, 2)
							End If
						End If
						If Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "L", "")) > 0 Then
							lstrConta = lstrConta & VB.Right(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "L", "")))
							lstrConta = VB.Right("000000" & lstrConta, 6)
						End If
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						lstrGrupo = IIf(IsDbNull(.Fields("GRUPO").value), "", .Fields("GRUPO").value)
						lstrSql = ""
						If lstrGrupo = "B" Then
							lstrSql = "UPDATE " & gstrDataBase & ".`unidb` "
						Else
							lstrSql = "UPDATE " & gstrDataBase & ".`unida` "
						End If
						lstrSql = lstrSql & "SET `latitude` = '" & Replace(CStr(ldblLat), ",", ".") & "', `longitude` = '" & Replace(CStr(ldblLon), ",", ".") & "' "
						'lstrSql = lstrSql & "SET `latitude` = '" & Strings.Replace(Convert_Degree(ldblLat), "'", "\'") & " " & istrNorteSul & "', `longitude` = '" & Strings.Replace(Convert_Degree(ldblLon), "'", "\'") & " " & IIf(istrOesteLeste = "W", "O", "L") & "' "
						lstrSql = lstrSql & " WHERE `zona` = '" & IIf(lstrZona <> "", lstrZona, "001") & "' "
						lstrSql = lstrSql & "   AND `livro` = '" & lstrLivro & "' "
						lstrSql = lstrSql & "   AND `conta` = '" & lstrConta & "' "
						Call Executar(lstrSql, True,  , False, True)
						If gstrBDCLinkCenter <> "" Then
							lstrSql = "UPDATE " & gstrBDCLinkCenter & ".uc "
							lstrSql = lstrSql & "SET `latitude` = '" & Replace(CStr(ldblLat), ",", ".") & "', `longitude` = '" & Replace(CStr(ldblLon), ",", ".") & "' "
							lstrSql = lstrSql & " WHERE `zlc` = '" & IIf(lstrZona <> "", lstrZona, "001") & lstrLivro & lstrConta & "' "
							lstrSql = lstrSql & "   AND `grupo` = '" & lstrGrupo & "' "
							Call Executar(lstrSql, True,  , False, True)
						End If
						Call .MoveNext()
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1,  , " (SGD) ")
					End While
				End If
				'Call Executar("COMMIT", , , False)
				Call .Close()
			End If
		End With
ErroBuscaDadoSGD: 
		If Err.Number > 0 Then
			Call MsgBox(Err.Description, MsgBoxStyle.Critical, gstrTituloMensagem)
			'Call Executar("ROLLBACK", , , False)
			lblnRet = False
		End If
		If gadoOraclCx.State = ADODB.ObjectStateEnum.adStateOpen Then gadoOraclCx.Close()
		'UPGRADE_NOTE: Object lrstAux may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
		lrstAux = Nothing
		Me.Cursor = System.Windows.Forms.Cursors.Default
		BuscaDadosdoSgd = lblnRet
	End Function
	
	Private Sub AtualizaAlimentador()
		Dim lstrAli As String
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim lstrContaGD As String
		Dim lstrGrupo As String
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		On Error GoTo ErroAtualizaAlimentador
		'lblnRet = True
		Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
		Me.lblInfo.Text = " Aguarde, conectando no oracle para buscar os consumidores LT"
		Call Me.lblInfo.Refresh()
		'
		Call CarregaOracleProvider(VB6.GetItemString(Me.cmbBaseDados, Me.cmbBaseDados.SelectedIndex), UCase(Me.txtUserSgd.Text), Me.txtPassSgd.Text)
		lstrSql = ""
		lstrSql = lstrSql & "SELECT C.CONTA, C.GRUPO, R.ALIMENTADOR FROM " & UCase(Me.txtUserSgd.Text) & ".CONSUMIDOR_LT C, REDEPRIMARIA_LT R WHERE R.UTRANSFORMADORA = C.INSTALACAO UNION "
		lstrSql = lstrSql & "SELECT C.CONTA, C.GRUPO, R.ALIMENTADOR FROM " & UCase(Me.txtUserSgd.Text) & ".CRPENDENTE_LT C, REDEPRIMARIA_LT R WHERE R.UTRANSFORMADORA = C.INSTALACAO AND C.NOX > 0 AND C.NOY > 0 "
		Call ConectarOrcl(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount, " (SGD) ")
					'Call Executar("START TRANSACTION", , , False)
					While Not .EOF
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						lstrContaGD = IIf(IsDbNull(.Fields("CONTA").value), "", .Fields("CONTA").value)
						lstrZona = "001"
						lstrLivro = ""
						lstrConta = ""
						If Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")) > 0 Then 'Se tiver Z na mascara
							lstrZona = VB.Left(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")))
						End If
						If Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "C", "")) > 0 Then 'Se tiver L na mascara
							If Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")) > 0 Then
								lstrLivro = Mid(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "L", ""), "C", "")), Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "C", "")))
							Else
								lstrLivro = VB.Left(lstrContaGD, 2)
							End If
						End If
						If Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "L", "")) > 0 Then
							lstrConta = lstrConta & VB.Right(lstrContaGD, Len(Replace(Replace(UCase(gstrZlcMask), "Z", ""), "L", "")))
							lstrConta = VB.Right("000000" & lstrConta, 6)
						End If
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						lstrGrupo = IIf(IsDbNull(.Fields("GRUPO").value), "", .Fields("GRUPO").value)
						'UPGRADE_WARNING: Use of Null/IsNull() detected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="2EED02CB-5C0E-4DC1-AE94-4FAA3A30F51A"'
						lstrAli = IIf(IsDbNull(.Fields("ALIMENTADOR").value), "", .Fields("ALIMENTADOR").value)
						lstrSql = ""
						If lstrGrupo = "B" Then
							lstrSql = "UPDATE " & gstrDataBase & ".`unidb` "
						Else
							lstrSql = "UPDATE " & gstrDataBase & ".`unida` "
						End If
						lstrSql = lstrSql & "SET `idalimentador` = " & CInt(IIf(IsNumeric(lstrAli), lstrAli, 0)) & " "
						lstrSql = lstrSql & " WHERE `zona` = '" & IIf(lstrZona <> "", lstrZona, "001") & "' "
						lstrSql = lstrSql & "   AND `livro` = '" & lstrLivro & "' "
						lstrSql = lstrSql & "   AND `conta` = '" & lstrConta & "' "
						If IsNumeric(lstrAli) Then Call Executar(lstrSql, True,  , False, True)
						Call .MoveNext()
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1,  , " (SGD) ")
					End While
					
					lstrSql = ""
					lstrSql = lstrSql & "CREATE EVENT job_nfv_alimentador ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND "
					lstrSql = lstrSql & "DO BEGIN "
					lstrSql = lstrSql & "  UPDATE `nfv`, `unidb` SET `nfv`.`id_alimentador` = `unidb`.`idalimentador` WHERE `unidb`.`unid_id` = `nfv`.`unid_id` AND `nfv`.`grupo` = 'B'; "
					lstrSql = lstrSql & "  UPDATE `nfv`, `unida` SET `nfv`.`id_alimentador` = `unida`.`idalimentador` WHERE `unida`.`unid_id` = `nfv`.`unid_id` AND `nfv`.`grupo` = 'A'; "
					lstrSql = lstrSql & "  UPDATE `nfv`, `alimentador` SET `nfv`.`ds_alimentador` = `alimentador`.`descricao` WHERE `alimentador`.`id` = `nfv`.`id_alimentador`; "
					lstrSql = lstrSql & "END;"
					Call Executar(lstrSql, True,  , False, True)
				End If
				'Call Executar("COMMIT", , , False)
				Call .Close()
			End If
		End With
		
ErroAtualizaAlimentador: 
		If Err.Number > 0 Then
			Call MsgBox(Err.Description, MsgBoxStyle.Critical, gstrTituloMensagem)
			'Call Executar("ROLLBACK", , , False)
			'lblnRet = False
		End If
		If gadoOraclCx.State = ADODB.ObjectStateEnum.adStateOpen Then gadoOraclCx.Close()
		'UPGRADE_NOTE: Object lrstAux may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
		lrstAux = Nothing
		Me.Cursor = System.Windows.Forms.Cursors.Default
	End Sub
End Class