Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmEstornaBaixas
	Inherits System.Windows.Forms.Form
	Private ifrmQuemChama As System.Windows.Forms.Form
	Private idtePagto As Date
	
	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
		ldteBase = BuscaDataBD
		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` = '" & gstrUsuario_Nome & "' "
						lstrSql = lstrSql & " WHERE `numnf`  = " & ldblNumNf
						lstrSql = lstrSql & "   AND `mesano` = '" & lstrMesAno & "' "
						Call Executar(lstrSql)
						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
	End Sub
	
	Public Sub SetaParametros(ByRef afrmQuemChama As System.Windows.Forms.Form, ByVal adtePagto As Date, ByVal aintBanco As Short, ByVal astrBanco As String)
		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
		
	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
		Call EstornaBaixas()
		Call BuscaFaturas()
	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
		If Not ifrmQuemChama Is Nothing Then
			ifrmQuemChama.Enabled = True
			'UPGRADE_NOTE: Object ifrmQuemChama 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"'
			ifrmQuemChama = Nothing
		End If
	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
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		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' "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					Me.txtTotReg.Text = VB6.Format(.RecordCount, "###,###0")
					Me.mfgDados.Redraw = False
					While Not .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"'
						lstrZona = IIf(IsDbNull(.Fields("zona").value), "", .Fields("zona").value)
						'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"'
						lstrLivro = IIf(IsDbNull(.Fields("livro").value), "", .Fields("livro").value)
						'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"'
						lstrConta = IIf(IsDbNull(.Fields("conta").value), "", .Fields("conta").value)
						'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"'
						lstrGrupo = IIf(IsDbNull(.Fields("grupo").value), "B", .Fields("grupo").value)
						'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"'
						lstrFlgst = UCase(IIf(IsDbNull(.Fields("flgst").value), "", .Fields("flgst").value))
						'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"'
						ldtePagamento = IIf(IsDbNull(.Fields("datapag").value), "1900-01-01", .Fields("datapag").value)
						'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"'
						ldteSituacao = IIf(IsDbNull(.Fields("datast").value), "1900-01-01", .Fields("datast").value)
						'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"'
						ldteVencimento = IIf(IsDbNull(.Fields("datavenc").value), "1900-01-01", .Fields("datavenc").value)
						'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"'
						llngConsId = IIf(IsDbNull(.Fields("cons_id").value), 0, .Fields("cons_id").value)
						'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"'
						llngUnidId = IIf(IsDbNull(.Fields("unid_id").value), 0, .Fields("unid_id").value)
						'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"'
						ldblNumNf = IIf(IsDbNull(.Fields("numnf").value), 0, .Fields("numnf").value)
						'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"'
						lstrMesAno = IIf(IsDbNull(.Fields("mesano").value), "", .Fields("mesano").value)
						'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"'
						llngBanco = IIf(IsDbNull(.Fields("banarrec").value), 0, .Fields("banarrec").value)
						'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"'
						ldblLiquido = IIf(IsDbNull(.Fields("valorliq").value), 0, .Fields("valorliq").value)
						If ldblLiquido <= 0 Then
							'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"'
							ldblLiquido = IIf(IsDbNull(.Fields("valortot").value), 0, .Fields("valortot").value)
						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
						Call .MoveNext()
						Me.txtValTot.Text = VB6.Format(IIf(IsNumeric(Me.txtValTot.Text), Me.txtValTot.Text, 0) + ldblLiquido, "#,##0.00")
					End While
					Me.mfgDados.Redraw = True
				End If
				Call .Close()
			End If
		End With
		'UPGRADE_NOTE: Object lrstAux 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"'
		lrstAux = Nothing
	End Sub
	
	Private Function BuscaNomeConsumidor(ByVal alngConsId As Integer) As String
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		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 "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .RecordCount > 0 Then
				'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"'
				BuscaNomeConsumidor = Trim(IIf(IsDbNull(.Fields("nome").value), "", .Fields("nome").value))
			Else
				BuscaNomeConsumidor = ""
			End If
			If .State = 1 Then Call .Close()
		End With
		'UPGRADE_NOTE: Object lrstAux 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"'
		lrstAux = Nothing
	End Function
	
	Private Function BuscaNomeUnidade(ByVal alngUnidId As Integer, ByVal alngConsId As Integer, ByVal astrGrupo As String) As String
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		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 "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .RecordCount > 0 Then
				'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"'
				BuscaNomeUnidade = Trim(IIf(IsDbNull(.Fields("nome").value), "", .Fields("nome").value))
			Else
				BuscaNomeUnidade = ""
			End If
			If .State = 1 Then Call .Close()
		End With
		'UPGRADE_NOTE: Object lrstAux 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"'
		lrstAux = Nothing
	End Function
	
	Private Sub MontaGrade()
		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
	End Sub
End Class