Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Friend Class frmEstornaBaixas
	Inherits System.Windows.Forms.Form
	Private ifrmQuemChama As System.Windows.Forms.Form
	Private idtePagto As Date
	Dim dt, dt2 As DataTable
	Dim objAtualizaBancoDados As New entidadeAtualizaBancoDados
	Dim repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

	Dim objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
	Dim repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados

	Private Sub EstornaBaixas()
		Dim lstrSql As String
		Dim llngUnidId As Integer
		Dim llngConsId As Integer
		Dim lstrGrupo As String
		Dim ldblNumNf As Double
		Dim lstrMesAno As String
		Dim ldteBase As Date
		Dim llngRow As Integer
		Try
			ldteBase = Date.Now
			llngRow = 1
			If Len(Trim(Me.txtMotivo.Text)) > 0 Then
				With Me.mfgDados
					If .Rows > 1 Then
						While llngRow <= .Rows - 1
							lstrMesAno = .get_TextMatrix(llngRow, 0)
							ldblNumNf = CDbl(.get_TextMatrix(llngRow, 1))
							llngUnidId = CInt(.get_TextMatrix(llngRow, 7))
							llngConsId = CInt(.get_TextMatrix(llngRow, 8))
							lstrGrupo = .get_TextMatrix(llngRow, 9)
							lstrSql = ""
							lstrSql = lstrSql & "UPDATE `nf` SET "
							lstrSql = lstrSql & "       `flgst` = ' ', "
							lstrSql = lstrSql & "       `datapag` = '0000-00-00', "
							lstrSql = lstrSql & "       `motivocanc`    = 'ESTORNO " & VB6.Format(ldteBase, "DD/MM/YYYY") & " - " & Replace(VB.Left(Me.txtMotivo.Text, 79), "'", "\'") & "', "
							lstrSql = lstrSql & "       `usuariosegvia` = '" & vUsuario.NOME & "' "
							lstrSql = lstrSql & " WHERE `numnf`  = " & ldblNumNf
							lstrSql = lstrSql & "   AND `mesano` = '" & lstrMesAno & "' "
							objExecAtualizaBancoDados.QUERY_NAME = lstrSql
							repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

							Call AtualizaHistoricoUnidade(llngUnidId, lstrGrupo, "[" & VB6.Format(ldteBase, "DD/MM/YYYY") & "]: FATURA ESTORNADA: " & ldblNumNf & " - " & lstrMesAno & " " & VB.Left(Me.txtMotivo.Text, 79))
							llngRow = llngRow + 1
						End While
						Call MsgBox("Faturas estornadas com sucesso.", MsgBoxStyle.Information, gstrTituloMensagem)
					Else
						Call MsgBox("Não existe faturas a serem estornadas!", MsgBoxStyle.Exclamation, gstrTituloMensagem)
					End If
				End With
			Else
				Call MsgBox("Informe o motivo do estorno!", MsgBoxStyle.Exclamation, gstrTituloMensagem)
			End If

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

	Public Sub SetaParametros(ByRef afrmQuemChama As System.Windows.Forms.Form, ByVal adtePagto As Date, ByVal aintBanco As Short, ByVal astrBanco As String)
		Try
			ifrmQuemChama = afrmQuemChama
			Me.lblIdBanco.Text = CStr(aintBanco)
			Me.lblDsBanco.Text = " " & astrBanco
			Me.lblDataPgto.Text = VB6.Format(adtePagto, "dd/MM/yyyy")
			Call BuscaFaturas()
			If Not ifrmQuemChama Is Nothing Then
				ifrmQuemChama.Enabled = False
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: SetaParametros" & "", "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 cmdSalvar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdSalvar.Click
		Try
			Call EstornaBaixas()
			Call BuscaFaturas()
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdSalvar_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub frmEstornaBaixas_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		'Call CenterForm(Me)
	End Sub

	Private Sub frmEstornaBaixas_FormClosed(ByVal eventSender As System.Object, ByVal eventArgs As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
		Try
			If Not ifrmQuemChama Is Nothing Then
				ifrmQuemChama.Enabled = True
				ifrmQuemChama = Nothing
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: frmEstornaBaixas_FormClosed" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub

	Private Sub BuscaFaturas()
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim lstrGrupo As String
		Dim lstrFlgst As String
		Dim ldtePagamento As Date
		Dim ldteSituacao As Date
		Dim ldteVencimento As Date
		Dim llngConsId As Integer
		Dim llngUnidId As Integer
		Dim ldblNumNf As Double
		Dim lstrMesAno As String
		Dim llngBanco As Integer
		Dim ldblLiquido As Double
		Dim lstrNome As String
		Dim lstrSql As String

		Try
			Call MontaGrade()
			Me.txtTotReg.Text = CStr(0)
			Me.txtValTot.Text = "0,00"
			lstrSql = ""
			lstrSql = lstrSql & "SELECT `nf`.`zona`,     " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`livro`,    " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`conta`,    " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`grupo`,       " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`flgst`,    " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`datapag`,  " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`datast`,   " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`datavenc`, " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`cons_id`,  " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`unid_id`,  " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`numnf`,    " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`mesano`,   " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`banarrec`, " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`valorliq`, " & vbCrLf
			lstrSql = lstrSql & "       `nf`.`valortot`  " & vbCrLf
			lstrSql = lstrSql & "  FROM `nf` " & vbCrLf
			lstrSql = lstrSql & " WHERE `nf`.`datapag` = '" & VB6.Format(Me.lblDataPgto.Text, "yyyy-MM-dd") & "' " & vbCrLf
			lstrSql = lstrSql & "   AND `nf`.`banarrec` = " & Me.lblIdBanco.Text & " "
			lstrSql = lstrSql & "   AND `nf`.`flgst` = 'P' "
			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)


			If dt.Rows.Count > 0 Then
				Me.txtTotReg.Text = VB6.Format(dt.Rows.Count, "###,###0")
				Me.mfgDados.Redraw = False
				For Each row As DataRow In dt.Rows
					lstrZona = IIf(IsDBNull(row.Item("zona")), "", row.Item("zona"))
					lstrLivro = IIf(IsDBNull(row.Item("livro")), "", row.Item("livro"))
					lstrConta = IIf(IsDBNull(row.Item("conta")), "", row.Item("conta"))
					lstrGrupo = IIf(IsDBNull(row.Item("grupo")), "B", row.Item("grupo"))
					lstrFlgst = UCase(IIf(IsDBNull(row.Item("flgst")), "", row.Item("flgst")))
					ldtePagamento = IIf(IsDBNull(row.Item("datapag")), "1900-01-01", row.Item("datapag"))
					ldteSituacao = IIf(IsDBNull(row.Item("datast")), "1900-01-01", row.Item("datast"))
					ldteVencimento = IIf(IsDBNull(row.Item("datavenc")), "1900-01-01", row.Item("datavenc"))
					llngConsId = IIf(IsDBNull(row.Item("cons_id")), 0, row.Item("cons_id"))
					llngUnidId = IIf(IsDBNull(row.Item("unid_id")), 0, row.Item("unid_id"))
					ldblNumNf = IIf(IsDBNull(row.Item("numnf")), 0, row.Item("numnf"))
					lstrMesAno = IIf(IsDBNull(row.Item("mesano")), "", row.Item("mesano"))
					llngBanco = IIf(IsDBNull(row.Item("banarrec")), 0, row.Item("banarrec"))
					ldblLiquido = IIf(IsDBNull(row.Item("valorliq")), 0, row.Item("valorliq"))
					If ldblLiquido <= 0 Then
						ldblLiquido = IIf(IsDBNull(row.Item("valortot")), 0, row.Item("valortot"))
					End If
					lstrNome = BuscaNomeUnidade(llngUnidId, llngConsId, lstrGrupo)
					If Len(lstrNome) = 0 Then
						lstrNome = BuscaNomeConsumidor(llngConsId)
					End If
					With Me.mfgDados
						.Rows = .Rows + 1
						.set_TextMatrix(.Rows - 1, 0, lstrMesAno)
						.set_TextMatrix(.Rows - 1, 1, ldblNumNf)
						.set_TextMatrix(.Rows - 1, 2, VB6.Format(ldblLiquido, "#,##0.00"))
						.set_TextMatrix(.Rows - 1, 3, ldteVencimento)
						.set_TextMatrix(.Rows - 1, 4, ldtePagamento)
						.set_TextMatrix(.Rows - 1, 5, lstrZona & "." & lstrLivro & "." & lstrConta)
						.set_TextMatrix(.Rows - 1, 6, lstrFlgst)
						.set_TextMatrix(.Rows - 1, 7, llngUnidId)
						.set_TextMatrix(.Rows - 1, 8, llngConsId)
						.set_TextMatrix(.Rows - 1, 9, lstrGrupo)
						.set_TextMatrix(.Rows - 1, 10, lstrNome)
					End With
					Me.txtValTot.Text = VB6.Format(IIf(IsNumeric(Me.txtValTot.Text), Me.txtValTot.Text, 0) + ldblLiquido, "#,##0.00")
				Next
				Me.mfgDados.Redraw = True
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaFaturas" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Sub
		End Try
	End Sub
	
	Private Function BuscaNomeConsumidor(ByVal alngConsId As Integer) As String
		Dim lstrSql As String

		Try

			lstrSql = ""
			lstrSql = lstrSql & "SELECT `consum`.`consreg`, " & vbCrLf
			lstrSql = lstrSql & "       `consum`.`nome` " & vbCrLf
			lstrSql = lstrSql & "  FROM `consum` " & vbCrLf
			lstrSql = lstrSql & " WHERE `consum`.`consreg` = " & alngConsId & " " & vbCrLf
			lstrSql = lstrSql & " LIMIT 0, 1 "
			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					BuscaNomeConsumidor = Trim(IIf(IsDBNull(row.Item("nome")), "", row.Item("nome")))
				Next
			Else
				BuscaNomeConsumidor = ""
			End If

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

	End Function
	
	Private Function BuscaNomeUnidade(ByVal alngUnidId As Integer, ByVal alngConsId As Integer, ByVal astrGrupo As String) As String
		Dim lstrSql As String

		Try

			'lstrSql = ""
			'lstrSql = lstrSql & "SELECT `unid" & LCase(astrGrupo) & "`.`unid_id`, " & vbCrLf
			'lstrSql = lstrSql & "       `unid" & LCase(astrGrupo) & "`.`cons_id`, " & vbCrLf
			'lstrSql = lstrSql & "       `unid" & LCase(astrGrupo) & "`.`nome` " & vbCrLf
			'lstrSql = lstrSql & "  FROM `unid" & LCase(astrGrupo) & "` " & vbCrLf
			'lstrSql = lstrSql & " WHERE `unid" & LCase(astrGrupo) & "`.`cons_id` = " & alngConsId & " " & vbCrLf
			'lstrSql = lstrSql & "   AND `unid" & LCase(astrGrupo) & "`.`unid_id` = " & alngUnidId & " " & vbCrLf
			'lstrSql = lstrSql & " LIMIT 0,1 "

			lstrSql = ""
			lstrSql = lstrSql & "SELECT `uc_faturamento`.`id`, " & vbCrLf
			lstrSql = lstrSql & "       `uc_faturamento`.`cons_id`, " & vbCrLf
			lstrSql = lstrSql & "       `uc_faturamento`.`nome` " & vbCrLf
			lstrSql = lstrSql & "  FROM `uc_faturamento` " & vbCrLf
			lstrSql = lstrSql & " WHERE `uc_faturamento`.`cons_id` = " & alngConsId & " " & vbCrLf
			lstrSql = lstrSql & "   AND `uc_faturamento`.`id` = " & alngUnidId & " " & vbCrLf
			lstrSql = lstrSql & " LIMIT 0,1 "
			objAtualizaBancoDados.QUERY_NAME = lstrSql
			dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

			If dt.Rows.Count > 0 Then
				For Each row As DataRow In dt.Rows
					BuscaNomeUnidade = Trim(IIf(IsDBNull(row.Item("nome")), "", row.Item("nome")))
				Next
			Else
				BuscaNomeUnidade = ""
			End If
		Catch ex As Exception
			MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: BuscaNomeUnidade" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
			Exit Function
		End Try

	End Function

	Private Sub MontaGrade()
		Try
			With Me.mfgDados
				.Redraw = False
				.Cols = 11
				.Rows = 1
				Call .Clear()
				.set_ColWidth(0, 940)
				.set_ColWidth(1, 900)
				.set_ColWidth(2, 1240)
				.set_ColWidth(3, 1240)
				.set_ColWidth(4, 1240)
				.set_ColWidth(5, 1500)
				.set_ColWidth(6, 840)
				.set_ColWidth(7, 0)
				.set_ColWidth(8, 0)
				.set_ColWidth(9, 600)
				.set_ColWidth(10, 4000)

				.set_TextMatrix(0, 0, "Mês/Ano")
				.set_TextMatrix(0, 1, "NF")
				.set_TextMatrix(0, 2, "Valor")
				.set_TextMatrix(0, 3, "Data Venc.")
				.set_TextMatrix(0, 4, "Data Pag. ")
				.set_TextMatrix(0, 5, "ZLC")
				.set_TextMatrix(0, 6, "Estado")
				.set_TextMatrix(0, 7, "unid_id")
				.set_TextMatrix(0, 8, "cons_id")
				.set_TextMatrix(0, 9, "Grupo")
				.set_TextMatrix(0, 10, "nome")
				.set_ColAlignment(0, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColAlignment(1, MSFlexGridLib.AlignmentSettings.flexAlignRightCenter)
				.set_ColAlignment(2, MSFlexGridLib.AlignmentSettings.flexAlignRightCenter)
				.set_ColAlignment(3, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColAlignment(4, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColAlignment(5, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColAlignment(6, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.set_ColAlignment(9, MSFlexGridLib.AlignmentSettings.flexAlignCenterCenter)
				.Redraw = True
				'.ScrollBars = flexScrollBarBoth
			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
End Class