Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Public Class frmLocalizaValorFat

	Private Sub cmdfechar_Click(sender As Object, e As EventArgs) Handles cmdfechar.Click
		vfrmOld.enabled = True
		Call Me.Close()
	End Sub


	Private Sub MontaGrade()
		Try

			With Me.mshDados
				Call .Clear()
				.Redraw = False
				.Rows = 1
				.Cols = 13
				.set_ColAlignment(1, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(2, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(3, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(4, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(5, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(6, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(7, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(8, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(9, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(10, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(11, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				.set_ColAlignment(12, MSFlexGridLib.AlignmentSettings.flexAlignLeftCenter)
				'.set_ColAlignment(13, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColWidth(1, 1500)
				.set_ColWidth(2, 1500)
				.set_ColWidth(3, 1500)
				.set_ColWidth(4, 1500)
				.set_ColWidth(5, 1500)
				.set_ColWidth(6, 1500)
				.set_ColWidth(7, 1500)
				.set_ColWidth(8, 2500)
				.set_ColWidth(9, 1500)
				.set_ColWidth(10, 1500)
				.set_ColWidth(11, 3500)
				.set_ColWidth(12, 3000)
				'.set_ColWidth(.get_Cols() - 2, 3000)
				'.set_ColWidth(.get_Cols() - 1, 2000)
				.set_TextMatrix(0, 0, "")
				.set_TextMatrix(0, 1, "Número")
				.set_TextMatrix(0, 2, "Mês/Ano")
				.set_TextMatrix(0, 3, "Zona")
				.set_TextMatrix(0, 4, "Livro")
				.set_TextMatrix(0, 5, "Conta")
				.set_TextMatrix(0, 6, "Valor Total")
				.set_TextMatrix(0, 7, "Valor Pagar")
				.set_TextMatrix(0, 8, "Vencimento")
				.set_TextMatrix(0, 9, "Pagamento")
				.set_TextMatrix(0, 10, "Situação")
				.set_TextMatrix(0, 11, "Nome")
				.set_TextMatrix(0, 12, "Classe")
				.AllowUserResizing = MSFlexGridLib.AllowUserResizeSettings.flexResizeColumns
				.AllowUserResizing = MSFlexGridLib.AllowUserResizeSettings.flexResizeBoth
				.AllowBigSelection = True
				.Redraw = True
			End With
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: MontaGrade" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub
	Private Sub mshDados_DblClick(sender As Object, e As EventArgs) Handles mshDados.DblClick
		Try

			If Me.mshDados.RowSel > 0 Then
				'Me.Close()
				Call vfrmOld.SetaValor(Me.mshDados.get_TextMatrix(Me.mshDados.RowSel, 1), Me.mshDados.get_TextMatrix(Me.mshDados.RowSel, 2))
			End If
			vfrmOld.enabled = True
			Me.Close()

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

	Private Sub cmdlocaliza_Click(sender As Object, e As EventArgs) Handles cmdlocaliza.Click
		Dim lstrSql As String
		Dim dt As DataTable
		Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
		Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados
		Dim sNumero As String
		Dim sMesAno As String
		Dim sZona As String
		Dim sLivro As String
		Dim sConta As String
		Dim sValorTotal As String
		Dim sValorPagar As String
		'Dim sVencto As String
		'Dim sPagto As String
		Dim sVencto As Date
		Dim sPagto As Date
		Dim sSituacao As String
		Dim sNome As String
		Dim sClasse As String
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String

		Try


			lstrZona = IIf(IsNumeric(Me.txtZona.Text), Me.txtZona.Text, "")
			lstrLivro = IIf(IsNumeric(Me.txtLivro.Text), Me.txtLivro.Text, "")
			lstrConta = IIf(IsNumeric(Me.txtConta.Text), Me.txtConta.Text, "")

			lstrSql = ""
			lstrSql = lstrSql & "SELECT "
			lstrSql = lstrSql & " `nf`.`numnf` as 'Numero', "
			lstrSql = lstrSql & " `nf`.`mesano` as 'MesAno', "
			lstrSql = lstrSql & " `nf`.`zona` as 'Zona', "
			lstrSql = lstrSql & " `nf`.`livro` as 'Livro', "
			lstrSql = lstrSql & " `nf`.`conta` as 'Conta', "
			lstrSql = lstrSql & " FORMAT(CONVERT(ROUND(`nf`.`valortot`,2) USING latin1),2,'de_DE') as 'Vlr.Total', "
			lstrSql = lstrSql & " FORMAT(CONVERT(ROUND(`nf`.`valorliq`,2) USING latin1),2,'de_DE') as 'Vlr.Pagar', "
			lstrSql = lstrSql & " `nf`.`datavenc` as 'Vecto', "
			lstrSql = lstrSql & " `nf`.`datapag` as 'Pgto', "
			lstrSql = lstrSql & " (CASE `nf`.`flgst` WHEN 'P' THEN 'Pago' WHEN 'C' THEN 'Cancelado' WHEN 'R' THEN 'Reserva' WHEN 'J' THEN 'Júdice' ELSE 'Aberta' END) as 'Situacao', "
			lstrSql = lstrSql & " IFNULL(`uc_faturamento`.`nome`,`uc_faturamento`.`nome`) as 'Nome', "
			lstrSql = lstrSql & " IFNULL(`classe`.`descricao`,'') as 'Classe' "
			lstrSql = lstrSql & "FROM `nf` "
			lstrSql = lstrSql & " LEFT JOIN `uc_faturamento` ON(`uc_faturamento`.`id` = `nf`.`unid_id`) "
			'lstrSql = lstrSql & " LEFT JOIN `unidb` ON(`unidb`.`unid_id` = `nf`.`unid_id` AND `nf`.`grupo` = 'B') "
			lstrSql = lstrSql & " LEFT JOIN `classe` ON(`classe`.`codigo` = `nf`.`codclas`) "
			lstrSql = lstrSql & "WHERE "
			If Me.ChkAbertos.Checked Then
				lstrSql = lstrSql & "`flgst` ='' "
			Else
				lstrSql = lstrSql & "1=1 "
			End If

			If optzlc.Checked = True Then
				If Len(Trim(lstrZona)) > 0 Then
					lstrSql = lstrSql & "    AND nf.ZONA = """ & lstrZona & """ " & vbCrLf
				End If

				If Len(Trim(lstrLivro)) > 0 Then
					lstrSql = lstrSql & "    AND nf.LIVRO = """ & lstrLivro & """ " & vbCrLf
				End If

				If Len(Trim(lstrConta)) > 0 Then
					lstrSql = lstrSql & "    AND nf.CONTA = """ & lstrConta & """ " & vbCrLf
				End If

			Else
				If txtValor.Text <> "" Then
					lstrSql = lstrSql & "  AND (`nf`.`valortot` = " & Replace(Replace(Me.txtValor.Text, ".", ""), ",", ".") & " OR "
					lstrSql = lstrSql & "       `nf`.`valorliq` = " & Replace(Replace(Me.txtValor.Text, ".", ""), ",", ".") & ") "
				End If
			End If

				lstrSql = lstrSql & " ORDER BY `mesano_ref` DESC, `numnf` ASC "

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

			Call MontaGrade()

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					sNumero = Trim(IIf(IsDBNull(row.Item("Numero")), "", row.Item("Numero")))
					sMesAno = Trim(IIf(IsDBNull(row.Item("MesAno")), "", row.Item("MesAno")))
					sZona = Trim(IIf(IsDBNull(row.Item("Zona")), "", row.Item("Zona")))
					sLivro = Trim(IIf(IsDBNull(row.Item("Livro")), "", row.Item("Livro")))
					sConta = Trim(IIf(IsDBNull(row.Item("Conta")), "", row.Item("Conta")))
					sValorTotal = Trim(IIf(IsDBNull(row.Item("Vlr.Total")), "", row.Item("Vlr.Total")))
					sValorPagar = Trim(IIf(IsDBNull(row.Item("Vlr.Pagar")), "", row.Item("Vlr.Pagar")))
					sVencto = Trim(IIf(IsDBNull(row.Item("Vecto")), "", row.Item("Vecto")))
					sPagto = Trim(IIf(IsDBNull(row.Item("Pgto")), "", row.Item("Pgto")))
					sSituacao = Trim(IIf(IsDBNull(row.Item("Situacao")), "", row.Item("Situacao")))
					sNome = Trim(IIf(IsDBNull(row.Item("Nome")), "", row.Item("Nome")))
					sClasse = Trim(IIf(IsDBNull(row.Item("Classe")), "", row.Item("Classe")))

					With Me.mshDados
						.Rows = Me.mshDados.Rows + 1
						.set_TextMatrix(.Rows - 1, 0, "")
						.set_TextMatrix(.Rows - 1, 1, sNumero)
						.set_TextMatrix(.Rows - 1, 2, sMesAno)
						.set_TextMatrix(.Rows - 1, 3, sZona)
						.set_TextMatrix(.Rows - 1, 4, sLivro)
						.set_TextMatrix(.Rows - 1, 5, sConta)
						.set_TextMatrix(.Rows - 1, 6, VB6.Format(CDbl(sValorTotal), "Standard"))
						.set_TextMatrix(.Rows - 1, 7, VB6.Format(CDbl(sValorPagar), "Standard"))
						.set_TextMatrix(.Rows - 1, 8, VB6.Format(sVencto, "dd/MM/yyyy"))
						If sSituacao <> "ABERTA" Then
							.set_TextMatrix(.Rows - 1, 9, VB6.Format(sPagto, "dd/MM/yyyy"))
						End If
						.set_TextMatrix(.Rows - 1, 10, sSituacao)
						.set_TextMatrix(.Rows - 1, 11, sNome)
						.set_TextMatrix(.Rows - 1, 12, sClasse)
					End With

				Next
			Else
				MessageBox.Show("Nenhum Registro encontrado", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information)
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdLocalizar_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	Private Sub frmLocalizaValorFat_Load(sender As Object, e As EventArgs) Handles MyBase.Load
		txtConta.Visible = False
		txtLivro.Visible = False
		txtZona.Visible = False
	End Sub
	Public Sub sSetaForm(ByVal afrmQuemChama As System.Windows.Forms.Form)
		vfrmOld = afrmQuemChama
		vfrmOld.Enabled = False
	End Sub
	Private Sub txtValor_Enter(sender As Object, e As EventArgs) Handles txtValor.Enter
		Try
			With Me.txtValor
				.SelectionStart = 0
				.SelectionLength = CInt(Len(.Text))
			End With
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: txtValor_Enter" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub txtValor_Leave_1(sender As Object, e As EventArgs) Handles txtValor.Leave
		Try
			With Me.txtValor
				.Text = VB6.Format(CDbl(IIf(IsNumeric(.Text), .Text, 0)), "0.00")
			End With
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: txtValor_Leave" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub txtValor_KeyPress(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.KeyPressEventArgs) Handles txtValor.KeyPress
		Try
			Dim KeyAscii As Short = Asc(eventArgs.KeyChar)
			If KeyAscii = System.Windows.Forms.Keys.Return Then
				TabEnter(KeyAscii, eventArgs)
			ElseIf KeyAscii <> System.Windows.Forms.Keys.Back And (KeyAscii < System.Windows.Forms.Keys.D0 Or KeyAscii > System.Windows.Forms.Keys.D9) Then
				If KeyAscii = System.Windows.Forms.Keys.Delete Then
					KeyAscii = System.Windows.Forms.Keys.Snapshot
				End If
				If KeyAscii <> System.Windows.Forms.Keys.Snapshot Then
					KeyAscii = 0
				Else
					If InStr(1, Me.txtValor.Text, ",") > 0 Then
						KeyAscii = 0
					End If
				End If
			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: txtValor_KeyPress" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub optzlc_CheckedChanged(sender As Object, e As EventArgs) Handles optzlc.CheckedChanged

		txtValor.Text = ""
		txtValor.Visible = False

		txtZona.Visible = True
		txtLivro.Visible = True
		txtConta.Visible = True

		Label1.Visible = True
		Label1.Text = optzlc.Text

		txtZona.Focus()

	End Sub

	Private Sub optValor_CheckedChanged(sender As Object, e As EventArgs) Handles optValor.CheckedChanged

		txtZona.Text = ""
		txtLivro.Text = ""
		txtConta.Text = ""

		txtZona.Visible = False
		txtLivro.Visible = False
		txtConta.Visible = False

		Label1.Visible = True
		Label1.Text = optValor.Text

		txtValor.Visible = True
		txtValor.Focus()
	End Sub

	Private Sub frmLocalizaValorFat_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
		vfrmOld.enabled = True
	End Sub

	Private Sub frmLocalizaValorFat_KeyPress(sender As Object, e As KeyPressEventArgs) Handles MyBase.KeyPress
		Dim KeyAscii As Short = Asc(e.KeyChar)

		Try

			If KeyAscii = 13 Then
				TabEnter(KeyAscii, e)
			End If

			e.KeyChar = Chr(KeyAscii)

			If KeyAscii = 0 Then
				e.Handled = True
			End If

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub txtZona_Enter(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtZona.Enter

		Try

			With Me.txtZona
				.SelectionStart = 0
				.SelectionLength = Len(.Text)
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub txtZona_Leave(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtZona.Leave


		Try

			With Me.txtZona
				.Text = Format(Convert.ToInt64(IIf(String.IsNullOrEmpty(.Text), "0", .Text)), "000")
				.Refresh()
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub txtLivro_Enter(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtLivro.Enter


		Try

			With Me.txtLivro
				.SelectionStart = 0
				.SelectionLength = Len(.Text)
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub txtLivro_Leave(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtLivro.Leave


		Try

			With Me.txtLivro
				.Text = Format(Convert.ToInt64(IIf(String.IsNullOrEmpty(.Text), "0", .Text)), "00")
				.Refresh()
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub txtConta_Enter(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtConta.Enter

		Try

			With Me.txtConta
				.SelectionStart = 0
				.SelectionLength = Len(.Text)
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Private Sub txtConta_Leave(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles txtConta.Leave
		Try

			With Me.txtConta
				.Text = Format(Convert.ToInt64(IIf(String.IsNullOrEmpty(.Text), "0", .Text)), "000000")
				.Refresh()
			End With

		Catch ex As Exception
			MessageBox.Show("Ocorreu um Erro Inesperado. Favor, Contate o Administrador do Sistema ou a Equipe de Suporte de sua localidade! ", "Fatal - Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try

	End Sub

	Public Sub TabEnter(ByRef Key As Short, ByRef eventArgs As System.Windows.Forms.KeyPressEventArgs)
		If Key = 13 Then
			eventArgs.Handled = True
			SendKeys.Send("{TAB}")
		End If
	End Sub

	Private Sub frmLocalizaValorFat_KeyUp(sender As Object, e As KeyEventArgs) Handles Me.KeyUp

	End Sub

	Private Sub grpFiltrar_Enter(sender As Object, e As EventArgs) Handles grpFiltrar.Enter

	End Sub
End Class