Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmExportaANA
	Inherits System.Windows.Forms.Form
	Private ifrmDir As frmDir
	Private istrNmArq As String
	
	Private Sub cmdExporta_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdExporta.Click
		Call LigDes(False)
		Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
		System.Windows.Forms.Application.DoEvents()
		Call MontaDados()
		Me.Cursor = System.Windows.Forms.Cursors.Default
		Call LigDes(True)
	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 cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Call Me.Close()
	End Sub
	
	Private Sub frmExportaANA_KeyPress(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
		Dim KeyAscii As Short = Asc(eventArgs.KeyChar)
		Select Case KeyAscii
			Case System.Windows.Forms.Keys.Return
				Call SendKeysA(System.Windows.Forms.Keys.Tab, True)
			Case System.Windows.Forms.Keys.Escape
				Call cmdFechar_Click(cmdFechar, New System.EventArgs())
		End Select
		eventArgs.KeyChar = Chr(KeyAscii)
		If KeyAscii = 0 Then
			eventArgs.Handled = True
		End If
	End Sub
	
	Private Sub frmExportaANA_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Call CenterForm(Me)
		ifrmDir = New frmDir
		Me.txtDir.Text = My.Application.Info.DirectoryPath
		Me.dtpMesAno.value = BuscaDataBD
	End Sub
	
	Private Sub frmExportaANA_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
	
	Private Sub LigDes(ByVal ablnModo As Boolean)
		Me.cmdExporta.Enabled = ablnModo
		Me.cmdFechar.Enabled = ablnModo
		Me.fmeAux.Enabled = ablnModo
		Me.dtpMesAno.Enabled = ablnModo
		Me.cmdPasta.Enabled = ablnModo
	End Sub
	
	Private Sub MontaDados()
		Dim adoStream As New ADODB.Stream
		Dim lintAge As Short
		Dim lintAux As Short
		Dim lintARQ As Short
		Dim lstrTab As String
		Dim lstrSql As String
		Dim lstrSql1 As String
		Dim lstrSql2 As String
		Dim lstrCon1 As String
		Dim lstrCon2 As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		lintAge = CShort(LeConfig("CODIGO_EMPRESA"))
		Me.lblInfo.Text = " Carregando as unidades, aguarde..."
		Call Me.lblInfo.Refresh()
		lstrSql = "SELECT '' PLN_CD, "
		lstrSql = lstrSql & lintAge & " ID_AGENTE, "
		lstrSql = lstrSql & " CONCAT(`uc`.`zona`,`uc`.`livro`,`uc`.`conta`) CD_UNIDADE_CONSUMIDORA, "
		lstrSql = lstrSql & " `uc`.`nome` NOME, "
		lstrSql = lstrSql & " IF(LENGTH(`uc`.`cpf_cnpj`)<=11,'1','5') TIPO_DOC,"
		lstrSql = lstrSql & " `uc`.`cpf_cnpj` NUMERO_DOC, "
		lstrSql = lstrSql & " IFNULL(`consum`.`telefone`,'') TELEFONE, "
		lstrSql = lstrSql & " IFNULL(`consum`.`dddtel`,'') DDD_TELEFONE, "
		lstrSql = lstrSql & " IFNULL(`consum`.`email`,'') EMAIL, "
		
		lstrSql = lstrSql & " IFNULL(`uc`.`cep_entrega`,`uc`.`cep`) CEP_COR, "
		lstrSql = lstrSql & " IFNULL(`uc`.`end_entrega`,`uc`.`end`) ENDERECO_RUA_COR, "
		lstrSql = lstrSql & " IFNULL(IFNULL(`uc`.`num_entrega`,`uc`.`numero`),'SN') ENDERECO_NUM_COR, "
		lstrSql = lstrSql & " IFNULL(`uc`.`bai_entrega`,`uc`.`bairro`) ENDERECO_BAIRRO_COR, "
		lstrSql = lstrSql & " IFNULL(`uc`.`uf_entrega`,`uc`.`uf`) ENDERECO_UF_COR, "
		lstrSql = lstrSql & " IFNULL(`uc`.`cid_entrega`,`uc`.`cidade`) ENDERECO_MUNICIPIO_COR, "
		lstrSql = lstrSql & " (SELECT `ibge` FROM munic WHERE munic.cod = LEFT(IFNULL(`uc`.`codend_entrega`,`uc`.`codend`),6)) COD_IBGE_MUNICIPIO_COR, "
		
		lstrSql = lstrSql & " IFNULL(`uc`.`cep`,'') CEP_UC, "
		lstrSql = lstrSql & " IFNULL(`uc`.`end`,'') ENDERECO_RUA_UC, "
		lstrSql = lstrSql & " IFNULL(`uc`.`numero`,'SN') ENDERECO_NUM_UC, "
		lstrSql = lstrSql & " IFNULL(`uc`.`bairro`,'') ENDERECO_BAIRRO_UC, "
		lstrSql = lstrSql & " IFNULL(`uc`.`uf`,'') ENDERECO_UF_UC, "
		lstrSql = lstrSql & " IFNULL(`uc`.`cidade`,'') ENDERECO_MUNICIPIO_UC, "
		lstrSql = lstrSql & " (SELECT `ibge` FROM munic WHERE munic.cod = LEFT(IFNULL(`uc`.`codend`,'      '),6)) COD_IBGE_MUNICIPIO_UC, "
		
		lstrSql = lstrSql & " REPLACE(TRUNCATE(`uc`.`latitude`,7),'.',',') LATITUDE, "
		lstrSql = lstrSql & " REPLACE(TRUNCATE(`uc`.`longitude`,7),'.',',') LONGITUDE, "
		
		lstrSql = lstrSql & " IF(MID(IF(`uc`.`grupo`= 'A','01A400',`uc`.`ta`),3,2)='A4',5,"
		lstrSql = lstrSql & " IF(MID(IF(`uc`.`grupo`= 'A','01A400',`uc`.`ta`),3,2)='B1',7, "
		lstrSql = lstrSql & " IF(MID(IF(`uc`.`grupo`= 'A','01A400',`uc`.`ta`),3,2)='B2',8, "
		lstrSql = lstrSql & " IF(MID(IF(`uc`.`grupo`= 'A','01A400',`uc`.`ta`),3,2)='B3',9, "
		lstrSql = lstrSql & " IF(MID(IF(`uc`.`grupo`= 'A','01A400',`uc`.`ta`),3,2)='B4',10,7))))) GRUPO_TENSAO, "
		lstrSql = lstrSql & " IF(MID(`uc`.`ta`,4,1) = 'V',2,IF(MID(`uc`.`ta`,4,1) = 'A',3,4)) MODALIDADE_TARIFA, "
		lstrSql = lstrSql & " IFNULL(`uc`.`dem_contr_fp`,0) DEMANDA_CONTRATADA, "
		lstrSql = lstrSql & " `uc`.`irr_hr_ini` INICIO_HORA_DESCONTO, "
		lstrSql = lstrSql & " `uc`.`irr_hr_fim` FINAL_HORA_DESCONTO "
		lstrSql1 = "  FROM `unidb` `uc` LEFT JOIN consum ON(consum.consreg = `uc`.`cons_id`) "
		lstrSql2 = "  FROM `unida` `uc` LEFT JOIN consum ON(consum.consreg = `uc`.`cons_id`) "
		lstrCon1 = ""
		lstrCon1 = lstrCon1 & "WHERE `uc`.`irrigacao_aquicultura` = 1 AND EXISTS(SELECT * "
		lstrCon1 = lstrCon1 & "               FROM `nf` "
		lstrCon1 = lstrCon1 & "              WHERE `nf`.`grupo`= 'B' "
		lstrCon1 = lstrCon1 & "                AND `nf`.`unid_id` = `uc`.`unid_id` "
		lstrCon2 = lstrCon2 & "                AND `nf`.`irrigacao_aquicultura` = 1 "
		lstrCon2 = lstrCon2 & "                AND `nf`.`mesano_ref` >= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "01 "
		lstrCon1 = lstrCon1 & "                AND `nf`.`mesano_ref` <= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "12) "
		lstrCon2 = ""
		lstrCon2 = lstrCon2 & "WHERE `uc`.`irrigacao_aquicultura` = 1 AND EXISTS(SELECT * "
		lstrCon2 = lstrCon2 & "               FROM `nf` "
		lstrCon2 = lstrCon2 & "              WHERE `nf`.`grupo`= 'A' "
		lstrCon2 = lstrCon2 & "                AND `nf`.`unid_id` = `uc`.`unid_id` "
		lstrCon2 = lstrCon2 & "                AND `nf`.`irrigacao_aquicultura` = 1 "
		lstrCon2 = lstrCon2 & "                AND `nf`.`mesano_ref` >= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "01 "
		lstrCon2 = lstrCon2 & "                AND `nf`.`mesano_ref` <= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "12) "
		
		Call Conectar(lstrSql & lstrSql1 & lstrCon1 & " UNION ALL " & lstrSql & lstrSql2 & lstrCon2 & " ORDER BY CD_UNIDADE_CONSUMIDORA ASC", lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount)
					adoStream.Open()
					adoStream.Charset = "UTF-8"
					'lintARQ = FreeFile
					'Open Me.txtDir.Text & IIf(Right(Me.txtDir.Text, 1) = "\", "", "\") & "Unidades_Consumidoras_" & gstrEmpresa_Sigla & ".csv" For Output As lintARQ
					lstrTab = ""
					For lintAux = 0 To .Fields.Count - 1
						lstrTab = lstrTab & .Fields(lintAux).Name & ";"
					Next lintAux
					adoStream.WriteText(lstrTab, ADODB.StreamWriteEnum.adWriteLine)
					'Print #lintARQ, lstrTab
					While Not .EOF
						lstrTab = ""
						For lintAux = 0 To .Fields.Count - 1
							'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"'
							lstrTab = lstrTab & IIf(IsDbNull(.Fields(lintAux).value), "", .Fields(lintAux).value) & ";"
						Next lintAux
						adoStream.WriteText(lstrTab, ADODB.StreamWriteEnum.adWriteLine)
						'Print #lintARQ, lstrTab
						Call .MoveNext()
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
					End While
					adoStream.SaveToFile(Me.txtDir.Text & IIf(VB.Right(Me.txtDir.Text, 1) = "\", "", "\") & "Unidades_Consumidoras_" & gstrEmpresa_Sigla & ".csv", ADODB.SaveOptionsEnum.adSaveCreateOverWrite)
					adoStream.Close()
					'Close #lintARQ
				End If
				Call .Close()
			End If
		End With
		Me.lblInfo.Text = " Carregando os consumos, aguarde..."
		Call Me.lblInfo.Refresh()
		lstrSql = "SELECT `nf`.`zona`,`nf`.`livro`,`nf`.`conta`, `nf`.`mesano`, `nf`.`itensserv`, `nf`.`mesano_ref`, `nf`.`numnf`, `nf`.`irrigacao_aquicultura` "
		lstrSql = lstrSql & "  FROM `nf` "
		lstrSql = lstrSql & " WHERE `nf`.`irrigacao_aquicultura` = 1 "
		lstrSql = lstrSql & "   AND `nf`.`mesano_ref` >= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "01 "
		lstrSql = lstrSql & "   AND `nf`.`mesano_ref` <= " & VB6.Format(Me.dtpMesAno.value, "yyyy") & "12 "
		lstrSql = lstrSql & " ORDER BY `nf`.`zona` ASC,`nf`.`livro` ASC,`nf`.`conta` ASC, `nf`.`mesano_ref` ASC "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount)
					adoStream.Open()
					adoStream.Charset = "UTF-8"
					'lintARQ = FreeFile
					'Open Me.txtDir.Text & IIf(Right(Me.txtDir.Text, 1) = "\", "", "\") & "Consumos_Mensais_" & gstrEmpresa_Sigla & "_" & Strings.Format(Me.dtpMesAno.value, "YYYY") & ".csv" For Output As lintARQ
					lstrTab = ""
					lstrTab = lstrTab & "PLN_CD;PCI_ID_AGENTE;PCI_CD_UNID_CONSUM;PCI_ANO_COMP;PCI_MES_COMP;PCI_CONSUMO_M_IN;PCI_CONSUMO_M_OUT;"
					adoStream.WriteText(lstrTab, ADODB.StreamWriteEnum.adWriteLine)
					'Print #lintARQ, lstrTab
					While Not .EOF
						lstrTab = ""
						lstrTab = lstrTab & ";"
						lstrTab = lstrTab & lintAge & ";"
						'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"'
						lstrTab = lstrTab & IIf(IsDbNull(.Fields("zona").value), "0", .Fields("zona").value) & IIf(IsDbNull(.Fields("livro").value), "", .Fields("livro").value) & IIf(IsDbNull(.Fields("conta").value), "", .Fields("conta").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"'
						lstrTab = lstrTab & VB.Right(IIf(IsDbNull(.Fields("mesano").value), "00/0000", .Fields("mesano").value), 4) & ";"
						'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"'
						lstrTab = lstrTab & VB.Left(IIf(IsDbNull(.Fields("mesano").value), "00/0000", .Fields("mesano").value), 2) & ";"
						'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"'
						lstrTab = lstrTab & BuscaValorItem(IIf(IsDbNull(.Fields("itensserv").value), "", .Fields("itensserv").value), "0302990054") & ";"
						'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"'
						lstrTab = lstrTab & BuscaValorItem(IIf(IsDbNull(.Fields("itensserv").value), "", .Fields("itensserv").value), "030201009") - BuscaValorItem(IIf(IsDbNull(.Fields("itensserv").value), "", .Fields("itensserv").value), "0302990054") & ";"
						adoStream.WriteText(lstrTab, ADODB.StreamWriteEnum.adWriteLine)
						'Print #lintARQ, lstrTab
						Call .MoveNext()
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
					End While
					adoStream.SaveToFile(Me.txtDir.Text & IIf(VB.Right(Me.txtDir.Text, 1) = "\", "", "\") & "Consumos_Mensais_" & gstrEmpresa_Sigla & "_" & VB6.Format(Me.dtpMesAno.value, "YYYY") & ".csv", ADODB.SaveOptionsEnum.adSaveCreateOverWrite)
					adoStream.Close()
					'Close #lintARQ
				End If
				Call .Close()
			End If
		End With
		Me.lblInfo.Text = Me.lblInfo.Text & " - Finalizado..."
		Call Me.lblInfo.Refresh()
	End Sub
End Class