Option Strict Off
Option Explicit On
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Imports CrystalDecisions.CrystalReports.Engine
Friend Class frmRelConsumVencto
	Inherits System.Windows.Forms.Form
	Dim intBanID As Short
	Private objAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

	Private objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
	Private repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados
	Private dt, dt1, dt2, dt3, dt4 As DataTable
	Private LImagem() As Byte
	Private sNomeEmpresa As String
	Private sEnderecoEmpresa As String
	Private sCepEmpresa As String
	Private sCidadeEmpresa As String
	Private sUFempresa As String
	Private sBairroEmpresa As String
	Private sFoneEmpresa As String
	Private sWebEmpresa As String

	Private Sub frmRelConsumVencto_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Try
			txtMesAno.Text = GetConfig("mes.ano.faturamento")
			Call pegaDataVencimento()
			Call CarregaComboBancos()
			Call sCriaTabelaAuxiliar()
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: frmRelConsumVencto_Load" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub frmRelConsumVencto_KeyPress(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
		Try
			Dim KeyAscii As Short = Asc(eventArgs.KeyChar)
			If KeyAscii = System.Windows.Forms.Keys.Return Or KeyAscii = System.Windows.Forms.Keys.Tab Then 'Identifica o Enter
				TabEnter(KeyAscii, eventArgs)
				KeyAscii = 0 'Zera KeyAscii para eliminar o Enter
			End If
			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: frmRelConsumVencto_KeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub txtMesAno_Enter(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtMesAno.Enter
		Try
			With txtMesAno
				.SelectionStart = 0
				.SelectionLength = Len(.Text)
			End With
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: txtMesAno_Enter" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub txtMesAno_Leave(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtMesAno.Leave
		Try
			With txtMesAno
				.Text = FormataData(txtMesAno, 1)
				If Len(Trim(.Text)) = 0 Then
					.Text = GetConfig("mes.ano.faturamento")
				End If
			End With
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: txtMesAno_Leave" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub cmdEmitir_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdEmitir.Click
		Dim strCriterio As String
		Dim strSql As String

		Try
			Call LigaDesliga(False)
			'Limpa a tabela so para garantir
			strSql = "DELETE FROM AUX_LANCTO_VENCTO WHERE nm_usuario=""" & My.Computer.Name & """"
			objExecAtualizaBancoDados.QUERY_NAME = strSql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)
			'Cria a tabela temporária
			Call TempTable()

			Call sDadosEmpresa(LImagem, sNomeEmpresa, sEnderecoEmpresa, sCepEmpresa, sCidadeEmpresa, sUFempresa, sBairroEmpresa, sFoneEmpresa, sWebEmpresa)
			Call lPrint("rptConsumidorVencto")

			Call LigaDesliga(True)
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdEmitir_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	Private Sub cboBanco_SelectedIndexChanged(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cboBanco.SelectedIndexChanged
		intBanID = VB6.GetItemData(cboBanco, cboBanco.SelectedIndex)
	End Sub
	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Me.Close()
	End Sub
	Private Sub pegaDataVencimento()
		Dim strDatas As String
		Dim strAux As String
		Dim intPos As Short
		Try
			strDatas = GetConfig("data.vencimento")
			strAux = ""
			intPos = 1
			cboDia.Items.Clear()

			cboDia.Items.Add(strAux)
			While intPos <= Len(strDatas)
				If Mid(strDatas, intPos, 1) <> ";" Then
					strAux = strAux & Mid(strDatas, intPos, 1)
				ElseIf Mid(strDatas, intPos, 1) = ";" Then
					cboDia.Items.Add(strAux)
					strAux = ""
				End If
				intPos = intPos + 1
			End While
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: pegaDataVencimento" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub CarregaComboBancos()
		Dim strSql As String
		Try
			strSql = "SELECT ban_id, nrbco, nmbco, tpmovto FROM bancos"
			objAtualizaBancoDados.QUERY_NAME = strSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			cboBanco.Items.Add("000 - TODOS")
			VB6.SetItemData(cboBanco, 0, 999)

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					cboBanco.Items.Add(IIf(IsDBNull(row.Item("Nrbco")), "000", row.Item("Nrbco")) & " - " & Trim(IIf(IsDBNull(row.Item("nmbco")), "Indefinido", row.Item("nmbco"))) & IIf(row.Item("tpmovto") = "D", " (DEBITO)", ""))
					VB6.SetItemData(cboBanco, cboBanco.Items.Count - 1, CShort(IIf(IsDBNull(row.Item("ban_id")), 0, row.Item("ban_id"))))
				Next
			Else
				cboBanco.Items.Add("(nenhum banco)")
				VB6.SetItemData(cboBanco, cboBanco.Items.Count - 1, 0)
			End If

			cboBanco.SelectedIndex = 0
			intBanID = VB6.GetItemData(cboBanco, cboBanco.SelectedIndex)
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: CarregaComboBancos" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub frmRelConsumVencto_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed

		Dim lstrsql As String

		Try

			lstrsql = ""
			lstrsql = lstrsql & "DROP TABLE IF EXISTS AUX_LANCTO_VENCTO"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

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

	End Sub

	Private Sub TempTable()
		Dim strSql As String
		Dim strfiltro As String
		Dim intNrBco As Short
		Dim strNmBco As String
		Dim dblValor As Double
		Dim strBcoUnidAB As String

		Try
			'Monta filtro
			strfiltro = ""
			If Len(Trim(cboDia.Text)) > 0 Then
				strfiltro = " u.diavenc = " & cboDia.Text & ""
			End If
			If intBanID <> 999 Then '999 = todos
				If Len(Trim(cboDia.Text)) > 0 Then
					strfiltro = strfiltro & " AND u.banco = " & intBanID & ""
				Else
					strfiltro = strfiltro & " u.banco = " & intBanID & ""
				End If
			End If

			strSql = ""


			strSql = strSql & "SELECT u.zona, u.livro, u.conta, u.nome, "
			strSql = strSql & "       u.agenc, u.cc, u.rota, u.banco, "
			strSql = strSql & "       u.diavenc, u.situacao, CONCAT(bancos.nrbco, ' - ', bancos.nmbco) AS bancoRet, nf.valortot "
			'strSql = strSql & "  FROM bancos, unida u "
			strSql = strSql & "  FROM bancos, uc_faturamento u "
			strSql = strSql & "  LEFT JOIN nf "
			strSql = strSql & "    ON nf.mesano = '" & txtMesAno.Text & "' "
			strSql = strSql & "   AND nf.zona = u.zona "
			strSql = strSql & "   AND nf.livro = u.livro "
			strSql = strSql & "   AND nf.conta = u.conta "
			strSql = strSql & " WHERE IFNULL(nf.flgst,'') <> 'C' "
			strSql = strSql & "   AND u.banco = bancos.ban_id " & IIf(Len(Trim(strfiltro)) > 0, " AND " & strfiltro, "")
			objAtualizaBancoDados.QUERY_NAME = strSql
			dt1 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)


			lblAcao.Text = "Buscando os dados..."
			lblAcao.Refresh()

			If dt1.Rows.Count > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt1.Rows.Count)
				For Each row1 As DataRow In dt1.Rows
					lblAcao.Text = "Processando " & ProgressBar.Value & " de " & ProgressBar.Maximum
					lblAcao.Refresh()

					dblValor = IIf(IsDBNull(row1.Item("ValorTot")), 0, row1.Item("ValorTot"))
					intNrBco = Val(Mid(row1.Item("bancoRet"), 1, InStr(1, row1.Item("bancoRet"), " -") - 1))
					strNmBco = row1.Item("bancoRet")

					'strSql = strSql & "'Insere os dados na tabela temporária
					strSql = "INSERT INTO AUX_LANCTO_VENCTO (`nm_usuario`, "
					strSql = strSql & "`id_banco`, "
					strSql = strSql & "`nm_banco`, "
					strSql = strSql & "`zona`, "
					strSql = strSql & "`livro`, "
					strSql = strSql & "`conta`, "
					strSql = strSql & "`nome`, "
					strSql = strSql & "`situacao`, "
					strSql = strSql & "`agencia`, "
					strSql = strSql & "`cc`, "
					strSql = strSql & "`rota`,"
					strSql = strSql & "`mesano`,"
					strSql = strSql & "`valor`, "
					strSql = strSql & "`diavenc`) VALUES (" & " '" & My.Computer.Name & "'" & ","
					strSql = strSql & "" & intNrBco & "" & ","
					strSql = strSql & "'" & strNmBco & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("Zona")), "000", row1.Item("Zona")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("Livro")), "00", row1.Item("Livro")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("Conta")), "000000", row1.Item("Conta")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("nome")), "", row1.Item("nome")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("situacao")), "", row1.Item("situacao")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("agenc")), "", row1.Item("agenc")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("cc")), "", row1.Item("cc")) & "'" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("ROTA")), "", row1.Item("ROTA")) & "'" & ","
					strSql = strSql & "'" & txtMesAno.Text & "'" & ","
					strSql = strSql & "" & Replace(CStr(dblValor), ",", ".") & "" & ","
					strSql = strSql & "'" & IIf(IsDBNull(row1.Item("DiaVenc")), "00", row1.Item("DiaVenc")) & "')"
					objExecAtualizaBancoDados.QUERY_NAME = strSql
					repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
					System.Windows.Forms.Application.DoEvents()
				Next
			End If

			dt1 = Nothing

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

	End Sub

	Private Sub LigaDesliga(ByRef aModo As Boolean)
		Try
			lblMesAno.Enabled = aModo
			lblDia.Enabled = aModo
			lblBanco.Enabled = aModo
			txtMesAno.Enabled = aModo
			cboDia.Enabled = aModo
			cboBanco.Enabled = aModo
			cmdEmitir.Enabled = aModo
			cmdFechar.Enabled = aModo
			System.Windows.Forms.Application.DoEvents()
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: LigaDesliga" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	Private Sub lPrint(ByVal vNomeRPT As String)

		Dim dt As DataTable
		Dim dt_1 As DataTable
		Dim dt_2 As DataTable
		Dim dt_3 As DataTable
		Dim dc As DataColumn
		Dim ds As DataSet
		Dim lstrsql As String

		Dim bytBLOBData() As Byte

		Dim objEmpresa As New entidadeEmpresa
		Dim repositorioEmpresa As New repositorioEmpresa

		Dim vlist


		'Dim vlist

		'Dim objEmpresa As New entidadeEmpresa
		'Dim repositorioEmpresa As New repositorioEmpresa


		'vlist = repositorioEmpresa.Select(objEmpresa)

		'If vlist.Count <> 0 Then

		'	For Each objEmpresaFor As entidadeEmpresa In vlist

		'		bytBLOBData = objEmpresaFor.LOGOTIPO

		'	Next

		'End If

		Try
			Dim rpt As New ReportDocument()

			'rptRelSampNovoBaixaRenda
			If vNomeRPT = "rptConsumidorVencto" Then

				dt_2 = New DataTable()
				dt_2.Columns.Add("NM_USUARIO ", GetType(String))
				dt_2.Columns.Add("ID_BANCO ", GetType(String))
				dt_2.Columns.Add("NM_BANCO ", GetType(String))
				dt_2.Columns.Add("ZONA ", GetType(String))
				dt_2.Columns.Add("LIVRO ", GetType(String))
				dt_2.Columns.Add("CONTA ", GetType(String))
				dt_2.Columns.Add("NOME ", GetType(String))
				dt_2.Columns.Add("SITUACAO ", GetType(String))
				dt_2.Columns.Add("AGENCIA ", GetType(String))
				dt_2.Columns.Add("CC ", GetType(String))
				dt_2.Columns.Add("ROTA ", GetType(String))
				dt_2.Columns.Add("MESANO  ", GetType(String))
				dt_2.Columns.Add("VALOR ", GetType(String))
				dt_2.Columns.Add("DIAVENC  ", GetType(String))
				dt_2.Columns.Add("IMAGEM", GetType(Byte()))
				ds = New DataSet

				lstrsql = ""
				lstrsql = "SELECT * FROM AUX_LANCTO_VENCTO WHERE NM_USUARIO = '" & My.Computer.Name & "'"
				objAtualizaBancoDados.QUERY_NAME = lstrsql
				dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

				If dt.Rows.Count > 0 Then
					For Each dr_1 As DataRow In dt.Rows

						dt_2.Rows.Add(dr_1("NM_USUARIO"),
										  dr_1("ID_BANCO"),
										  dr_1("NM_BANCO"),
										  dr_1("ZONA"),
										  dr_1("LIVRO"),
										  dr_1("CONTA"),
										  dr_1("NOME"),
										  dr_1("SITUACAO"),
										  dr_1("AGENCIA"),
										  dr_1("CC"),
										  dr_1("ROTA"),
										  dr_1("MESANO"),
										  dr_1("VALOR"),
										  dr_1("DIAVENC"),
										  bytBLOBData)
					Next dr_1
				End If

				ds.Tables.Add(dt)
				ds.Tables(0).TableName = "REL_LANCTO_VENCTO"

				If ds.Tables(0).Rows.Count = 0 Then
					MessageBox.Show("Não há registros para o relatório escolhido! ", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information)
					Exit Sub
				Else


					rpt = New rptConsumidorVencto

					rpt.SetDataSource(ds)
					rpt.SetParameterValue("nomeempresa", sNomeEmpresa)
					rpt.SetParameterValue("enderecoempresa", sEnderecoEmpresa)
					rpt.SetParameterValue("cepempresa", sCepEmpresa)
					rpt.SetParameterValue("ufempresa", sUFempresa)
					rpt.SetParameterValue("cidadeempresa", sCidadeEmpresa)
					rpt.SetParameterValue("bairroempresa", sBairroEmpresa)
					rpt.SetParameterValue("foneempresa", sFoneEmpresa)
					rpt.SetParameterValue("websiteempresa", sWebEmpresa)
					'rpt.SetParameterValue("MesAnoRef", Month(dtpMesAno.Value) & "/" & Year(dtpMesAno.Value))
					'rpt.SetParameterValue("NomeRel", "Grupo A - Período de: " & dtpIni.Value & " Até: " & dtpFim.Value)

					frmViewRelatorios.rpt = rpt

					frmViewRelatorios.CRViewer.Zoom((100))
					frmViewRelatorios.Text = "Relatório Consumidor por Vencimento"
					frmViewRelatorios.ShowDialog()

				End If

			End If

			Me.Cursor = System.Windows.Forms.Cursors.Default

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

	End Sub

	Private Sub sCriaTabelaAuxiliar()

		Dim lstrsql As String

		Try

			lstrsql = ""
			lstrsql = lstrsql & "DROP TABLE IF EXISTS AUX_LANCTO_VENCTO"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

			lstrsql = ""
			lstrsql = lstrsql & "create table IF NOT EXISTS AUX_LANCTO_VENCTO("
			lstrsql = lstrsql & "             NM_USUARIO varchar(50), "
			lstrsql = lstrsql & "             ID_BANCO varchar(20), "
			lstrsql = lstrsql & "             NM_BANCO varchar(50), "
			lstrsql = lstrsql & "             ZONA varchar(10),"
			lstrsql = lstrsql & "             LIVRO varchar(10), "
			lstrsql = lstrsql & "             CONTA varchar(15), "
			lstrsql = lstrsql & "             NOME varchar(100), "
			lstrsql = lstrsql & "             SITUACAO varchar(20), "
			lstrsql = lstrsql & "             AGENCIA varchar(20), "
			lstrsql = lstrsql & "             CC varchar(20), "
			lstrsql = lstrsql & "             ROTA varchar(20), "
			lstrsql = lstrsql & "             MESANO  varchar(10),"
			lstrsql = lstrsql & "             VALOR varchar(20),"
			lstrsql = lstrsql & "             DIAVENC  varchar(10))"
			objExecAtualizaBancoDados.QUERY_NAME = lstrsql
			repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)



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