Option Strict Off
Option Explicit On
Friend Class frmRelConsumVencto
	Inherits System.Windows.Forms.Form
	Dim intBanID As Short
	
	Private Sub frmRelConsumVencto_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		txtMesAno.Text = LeConfig("MESANOFATURAMENTO")
		Call pegaDataVencimento()
		Call CarregaComboBancos()
	End Sub
	
	Private Sub frmRelConsumVencto_KeyPress(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.KeyPressEventArgs) Handles MyBase.KeyPress
		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
			Call SendKeysA(System.Windows.Forms.Keys.Tab, True) 'Grava no buffer do teclado um Tab
			KeyAscii = 0 'Zera KeyAscii para eliminar o Enter
		End If
		eventArgs.KeyChar = Chr(KeyAscii)
		If KeyAscii = 0 Then
			eventArgs.Handled = True
		End If
	End Sub
	
	Private Sub txtMesAno_Enter(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtMesAno.Enter
		With txtMesAno
			.SelectionStart = 0
			.SelectionLength = Len(.Text)
		End With
	End Sub
	
	Private Sub txtMesAno_Leave(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtMesAno.Leave
		With txtMesAno
			.Text = FormataData(txtMesAno, 1)
			If Len(Trim(.Text)) = 0 Then
				.Text = LeConfig("MESANOFATURAMENTO")
			End If
		End With
	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
		
		Call LigaDesliga(False)
		'Limpa a tabela so para garantir
		strSql = "DELETE FROM temprellanctosvencto WHERE nm_usuario=""" & gstrNmEstacao & """"
		Call Executar(strSql)
		'Cria a tabela temporária
		Call TempTable()
		
		'-- Chama o relatório em Crystal Reports
		strCriterio = "{temprellanctosvencto.nm_usuario} = """ & gstrNmEstacao & """" 'parametros
		With Me.crpAux
			.ReportFileName = My.Application.Info.DirectoryPath & "\" & LeConfig("DIR_RPT") & "\ConsumidorVencto.rpt"
			.DiscardSavedData = True
			.WindowState = Crystal.WindowStateConstants.crptMaximized
			.WindowControlBox = True
			.WindowControls = True
			.WindowShowPrintSetupBtn = True
			.WindowShowCloseBtn = True
			.WindowShowSearchBtn = True
			.SelectionFormula = strCriterio
			.WindowTitle = "Consumidor Vencimento"
			.Destination = Crystal.DestinationConstants.crptToWindow
			.Action = 0
		End With
		
		'Depois que emite o relatório, deleta os dados da tabela temporaria do usuario
		'strSql = "DELETE FROM temprellanctosvencto WHERE nm_usuario=""" & gstrNmEstacao & """"
		'Call executar(strSql)
		Call LigaDesliga(True)
	End Sub
	
	'UPGRADE_WARNING: Event cboBanco.SelectedIndexChanged 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 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
	
	' Enche a combo box com os dias de vencto
	Private Sub pegaDataVencimento()
		Dim strDatas As String
		Dim strAux As String
		Dim intPos As Short
		
		strDatas = LeConfig("DATAS_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
	End Sub
	
	Private Sub CarregaComboBancos()
		Dim rsBanco As New ADODB.Recordset
		Dim strSql As String
		
		strSql = "SELECT ban_id, nrbco, nmbco, tpmovto FROM bancos"
		Call Conectar(strSql, rsBanco)
		
		cboBanco.Items.Add("000 - TODOS")
		VB6.SetItemData(cboBanco, 0, 999)
		If rsBanco.RecordCount > 0 Then
			rsBanco.MoveFirst()
			While Not rsBanco.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"'
				cboBanco.Items.Add(IIf(IsDbNull(rsBanco.Fields("Nrbco").Value), "000", rsBanco.Fields("Nrbco").Value) & " - " & Trim(IIf(IsDbNull(rsBanco.Fields("nmbco").Value), "Indefinido", rsBanco.Fields("nmbco").Value)) & IIf(rsBanco.Fields("tpmovto").Value = "D", " (DEBITO)", ""))
				'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"'
				VB6.SetItemData(cboBanco, cboBanco.Items.Count - 1, CShort(IIf(IsDbNull(rsBanco.Fields("ban_id").Value), 0, rsBanco.Fields("ban_id").Value)))
				rsBanco.MoveNext()
			End While
		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)
	End Sub
	
	Private Sub TempTable()
		Dim rsConsulta As New ADODB.Recordset
		Dim rsBancos As New ADODB.Recordset
		Dim rsNF As New ADODB.Recordset
		Dim strSql As String
		Dim strfiltro As String
		Dim intNrBco As Short
		Dim strNmBco As String
		Dim dblValor As Double
		Dim strBcoUnidAB As String
		
		'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, unidb 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, "")
		
		strSql = strSql & " UNION "
		
		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 & "  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, "")
		
		
		'  strSql = "SELECT zona, livro, conta, nome, agenc, cc, rota, banco, diavenc,situacao, " _
		''         & "      (SELECT valortot " _
		''         & "         FROM nf USE INDEX(keyzona,keylivro,keyconta) " _
		''         & "        WHERE nf.zona = unida.zona " _
		''         & "          AND nf.livro = unida.livro " _
		''         & "          AND nf.conta = unida.conta " _
		''         & "          AND mesano = '" & txtMesAno.Text & "') valortot,  " _
		''         & "      (SELECT DISTINCT CONCAT(nrbco,' - ',nmbco) " _
		''         & "         FROM bancos " _
		''         & "        WHERE bancos.ban_id = unida.banco) bancoRet " _
		''         & "  FROM unida USE INDEX(keyzona,keylivro,keyconta) " & IIf(Len(Trim(strfiltro)) > 0, " WHERE " & strfiltro, "") _
		''         & " UNION " _
		''         & "SELECT zona, livro, conta, nome, agenc, cc, rota, banco, diavenc,situacao, " _
		''         & "      (SELECT valortot " _
		''         & "         FROM nf USE INDEX(keyzona,keylivro,keyconta) " _
		''         & "        WHERE nf.zona = unidb.zona " _
		''         & "          AND nf.livro = unidb.livro " _
		''         & "          AND nf.conta = unidb.conta " _
		''         & "          AND mesano = '" & txtMesAno.Text & "') valortot,  " _
		''         & "      (SELECT DISTINCT CONCAT(nrbco,' - ',nmbco) " _
		''         & "         FROM bancos " _
		''         & "        WHERE bancos.ban_id = unidb.banco) bancoRet " _
		''         & "  FROM unidb USE INDEX(keyzona,keylivro,keyconta) " & IIf(Len(Trim(strfiltro)) > 0, " WHERE " & strfiltro, "")
		Call Conectar(strSql, rsConsulta)
		lblAcao.Text = "Buscando os dados..."
		lblAcao.Refresh()
		If rsConsulta.RecordCount > 0 Then
			Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, rsConsulta.RecordCount)
			While Not rsConsulta.EOF
				lblAcao.Text = "Processando " & ProgressBar.Value & " de " & ProgressBar.Maximum
				lblAcao.Refresh()
				'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"'
				dblValor = IIf(IsDbNull(rsConsulta.Fields("ValorTot").Value), 0, rsConsulta.Fields("ValorTot").Value)
				intNrBco = Val(Mid(rsConsulta.Fields("bancoRet").Value, 1, InStr(1, rsConsulta.Fields("bancoRet").Value, " -") - 1))
				strNmBco = rsConsulta.Fields("bancoRet").Value
				
				'Insere os dados na tabela temporária
				'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"'
				strSql = "INSERT INTO TempRelLanctosVencto (`nm_usuario`, `id_banco`, `nm_banco`, `zona`, `livro`, `conta`, `nome`, `situacao`,`agencia`, `cc`, `rota`, `mesano`, `valor`, `diavenc`) VALUES (" & " """ & gstrNmEstacao & """" & ", """ & intNrBco & """" & ", """ & strNmBco & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("Zona").Value), "000", rsConsulta.Fields("Zona").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("Livro").Value), "00", rsConsulta.Fields("Livro").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("Conta").Value), "000000", rsConsulta.Fields("Conta").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("nome").Value), "", rsConsulta.Fields("nome").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("situacao").Value), "", rsConsulta.Fields("situacao").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("agenc").Value), "", rsConsulta.Fields("agenc").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("cc").Value), "", rsConsulta.Fields("cc").Value) & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("ROTA").Value), "", rsConsulta.Fields("ROTA").Value) & """" & ", """ & txtMesAno.Text & """" & ", """ & Replace(CStr(dblValor), ",", ".") & """" & ", """ & IIf(IsDbNull(rsConsulta.Fields("DiaVenc").Value), "00", rsConsulta.Fields("DiaVenc").Value) & """)"
				Call Executar(strSql)
				rsConsulta.MoveNext()
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
				System.Windows.Forms.Application.DoEvents()
			End While
		End If
		If rsConsulta.State = 1 Then rsConsulta.Close()
		If rsBancos.State = 1 Then rsBancos.Close()
		If rsNF.State = 1 Then rsNF.Close()
	End Sub
	
	Private Sub LigaDesliga(ByRef aModo As Boolean)
		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()
	End Sub
End Class