Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
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
		Try
			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)
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdExporta_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub cmdPasta_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdPasta.Click
		Try

			fbdAna.ShowDialog()
			txtDir.Text = fbdAna.SelectedPath() & "\"

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdPasta_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	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
		Try

			Dim KeyAscii As Short = Asc(eventArgs.KeyChar)
			Select Case KeyAscii
				Case System.Windows.Forms.Keys.Return
					TabEnter(KeyAscii, eventArgs)
				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

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: frmExportaANA_KeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub frmExportaANA_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Try
			Me.txtDir.Text = My.Application.Info.DirectoryPath
			Me.dtpMesAno.Value = Date.Now
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: frmExportaANA_Load" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	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)
		Try
			Me.cmdExporta.Enabled = ablnModo
			Me.cmdFechar.Enabled = ablnModo
			Me.fmeAux.Enabled = ablnModo
			Me.dtpMesAno.Enabled = ablnModo
			Me.cmdPasta.Enabled = ablnModo
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: LigDes" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	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 i As Integer
		Dim sNomeArquivo As String
		Dim dt, dt2 As DataTable
		Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
		Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

		Try

			lintAge = CShort(GetConfig("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 "
			lstrSql = lstrSql & "  FROM `uc_faturamento` `uc` LEFT JOIN consum ON(consum.consreg = `uc`.`cons_id`) "

			'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 = ""
			lstrSql = lstrSql & "WHERE `uc`.`irrigacao_aquicultura` = 1 AND EXISTS(SELECT * "
			lstrSql = lstrSql & "               FROM `nf` "
			lstrSql = lstrSql & "              WHERE `nf`.`unid_id` = `uc`.`id` "
			'lstrSql = lstrSql & "              WHERE `nf`.`grupo`= 'B' "
			'lstrSql = lstrSql & "                AND `nf`.`unid_id` = `uc`.`unid_id` "
			lstrSql = lstrSql & "                AND `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 CD_UNIDADE_CONSUMIDORA ASC"

			'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) "

			objAtualizaBancoDados.QUERY_NAME = lstrSql '& lstrSql1 & lstrCon1 & " UNION ALL " & lstrSql & lstrSql2 & lstrCon2 & " ORDER BY CD_UNIDADE_CONSUMIDORA ASC
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt.Rows.Count)
				'adoStream.Open()
				'adoStream.Charset = "UTF-8"
				lintARQ = FreeFile()
				sNomeArquivo = ""
				sNomeArquivo = txtDir.Text & IIf(VB.Right(Me.txtDir.Text, 1) = "\", "", "\") & "Unidades_Consumidoras_" & gstrEmpresa_Sigla & ".csv" 'For Output As lintARQ
				FileOpen(lintARQ, sNomeArquivo, OpenMode.Output)
				lstrTab = ""

				For Each columns As DataColumn In dt.Columns
					lstrTab = lstrTab & IIf(IsDBNull(columns.ColumnName), "", columns.ColumnName) & ";"
				Next
				PrintLine(lintARQ, lstrTab)

				For Each row As DataRow In dt.Rows
					lstrTab = ""
					i = 0
					For Each columns2 As DataColumn In dt.Columns
						lstrTab = lstrTab & IIf(IsDBNull(row.Item(i)), "", row.Item(i)) & ";"
						i = i + 1
					Next
					PrintLine(lintARQ, lstrTab)
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
				Next
				FileClose(lintARQ)
			End If


			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 "

			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt2 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)


			If dt2.Rows.Count > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt2.Rows.Count)
				'			adoStream.Open()
				'			adoStream.Charset = "UTF-8"
				lintARQ = FreeFile()
				sNomeArquivo = ""
				sNomeArquivo = Me.txtDir.Text & IIf(VB.Right(Me.txtDir.Text, 1) = "\", "", "\") & "Consumos_Mensais_" & gstrEmpresa_Sigla & "_" & Year(Me.dtpMesAno.Value) & ".csv" 'For Output As lintARQ
				FileOpen(lintARQ, sNomeArquivo, OpenMode.Output)
				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;"
				PrintLine(lintARQ, lstrTab)

				For Each row2 As DataRow In dt2.Rows
					lstrTab = ""
					lstrTab = lstrTab & ";"
					lstrTab = lstrTab & lintAge & ";"

					lstrTab = lstrTab & IIf(IsDBNull(row2.Item("zona")), "0", row2.Item("zona")) & IIf(IsDBNull(row2.Item("livro")), "", row2.Item("livro")) & IIf(IsDBNull(row2.Item("conta")), "", row2.Item("conta")) & ";"
					lstrTab = lstrTab & VB.Right(IIf(IsDBNull(row2.Item("mesano")), "00/0000", row2.Item("mesano")), 4) & ";"
					lstrTab = lstrTab & VB.Left(IIf(IsDBNull(row2.Item("mesano")), "00/0000", row2.Item("mesano")), 2) & ";"
					lstrTab = lstrTab & BuscaValorItem(IIf(IsDBNull(row2.Item("itensserv")), "", row2.Item("itensserv")), "0302990054") & ";"
					lstrTab = lstrTab & BuscaValorItem(IIf(IsDBNull(row2.Item("itensserv")), "", row2.Item("itensserv")), "030201009") - BuscaValorItem(IIf(IsDBNull(row2.Item("itensserv")), "", row2.Item("itensserv")), "0302990054") & ";"
					PrintLine(lintARQ, lstrTab)

					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
				Next

				FileClose(lintARQ)
			End If

			Me.lblInfo.Text = Me.lblInfo.Text & " - Finalizado..."
			Call Me.lblInfo.Refresh()

		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: MontaDados" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub
End Class