Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmRelNis
	Inherits System.Windows.Forms.Form
	Private istrCab As String
	Private ifrAuxiliar As FrmRelatorio
	Private istrClasseCabec As String
	Private istrCol As String
	
	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Call Me.Close()
	End Sub
	
	Private Sub cmdImprimir_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdImprimir.Click
		Dim lstrCpfNis As String 'cpf que amarra o nis a unidade
		Dim lstrNis As String
		Dim ldteCad As Date
		Dim ldteAlt As Date
		Dim ldteNcto As Date
		Dim lstrUf As String
		Dim lstrEvento As String
		Dim lstrNomNis As String
		Dim lstrNisCpf As String 'cpf do titular do número do nis
		Dim lstrZona As String
		Dim lstrLivro As String
		Dim lstrConta As String
		Dim lstrNomUc As String
		
		Dim lstrRotUc As String
		Dim lstrMedUc As String
		Dim lstrFasUc As String
		Dim lstrSitUc As String
		Dim lstrCpfUc As String
		Dim lstrLogEndUc As String
		Dim lstrLogNumUc As String
		Dim lstrLogComUc As String
		Dim lstrLogBaiUc As String
		Dim lstrCol As String
		Dim lintCtrl As Short
		Dim lstrTab As String
		Dim llngTot As Integer
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		llngTot = 0
		istrCab = "Filtros: "
		lstrSql = ""
		lstrSql = lstrSql & "SELECT nis.cpf, " & vbCrLf
		lstrSql = lstrSql & "       nis.nis, " & vbCrLf
		lstrSql = lstrSql & "       nis.data_cadastro, " & vbCrLf
		lstrSql = lstrSql & "       nis.data_alteracao, " & vbCrLf
		lstrSql = lstrSql & "       nis.data_nascimento, " & vbCrLf
		lstrSql = lstrSql & "       nis.uf_nascimento, " & vbCrLf
		lstrSql = lstrSql & "       nis.evento, " & vbCrLf
		lstrSql = lstrSql & "       nis.nome_nis, " & vbCrLf
		lstrSql = lstrSql & "       nis.cpf_nis, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.zona,nis.zona)   AS zonanew, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.livro,nis.livro) AS livronew, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.conta,nis.conta) AS contanew, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.rota,'')         AS rotauc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.nome,'')         AS nomeuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.end,'')          AS logenduc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.numero,'')       AS lognumuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.complement,'')   AS logcomuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.bairro,'')       AS logbaiuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.num,'')          AS medidor, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.fase,'')         AS faseuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.situacao,'')     AS situuc, " & vbCrLf
		lstrSql = lstrSql & "       IFNULL(unidb.cpf_cnpj,'')     AS cpfuc " & vbCrLf
		lstrSql = lstrSql & "  FROM nis " & vbCrLf
		If Me.opbUcTodos.Checked Then
			istrCab = istrCab & " - Todas as unidades consumidoras"
			lstrSql = lstrSql & " INNER JOIN unidb ON(unidb.unid_id = nis.unid_id) "
		ElseIf Me.opbUcCpfIgual.Checked Then 
			istrCab = istrCab & " - unidades consumidoras com cpf igual do nis"
			lstrSql = lstrSql & " INNER JOIN unidb ON(unidb.unid_id = nis.unid_id AND unidb.cpf_cnpj = nis.cpf) "
		ElseIf Me.opbUcCpfDiferente.Checked Then 
			istrCab = istrCab & " - unidades consumidoras com cpf diferente do nis"
			lstrSql = lstrSql & " INNER JOIN unidb ON(unidb.unid_id = nis.unid_id AND unidb.cpf_cnpj <> nis.cpf) "
		End If
		If Me.opbNisAtivos.Checked Then
			istrCab = istrCab & " - somente os ativos"
			lstrSql = lstrSql & " WHERE nis.evento <> 'D' "
		ElseIf Me.opbNisDesativados.Checked Then 
			istrCab = istrCab & " - somente os desativados"
			lstrSql = lstrSql & " WHERE nis.evento = 'D' "
		Else
			istrCab = istrCab & " - todos nis"
		End If
		If Me.opbOrdemCpf.Checked Then
			istrCab = istrCab & " - ordem por cpf"
			lstrSql = lstrSql & " ORDER BY nis.cpf ASC, nis.id ASC"
		ElseIf Me.opbOrdemNis.Checked Then 
			istrCab = istrCab & " - ordem por nis"
			lstrSql = lstrSql & " ORDER BY nis.nis ASC, nis.id ASC"
		ElseIf Me.opbOrdemNomeNis.Checked Then 
			istrCab = istrCab & " - ordem por nome"
			lstrSql = lstrSql & " ORDER BY nis.nome_nis ASC, nis.id ASC"
		ElseIf Me.opbOrdemZLC.Checked Then 
			istrCab = istrCab & " - ordem por zona,livro e conta"
			lstrSql = lstrSql & " ORDER BY nis.zona ASC, nis.livro ASC, nis.conta ASC, nis.id ASC"
		End If
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				If .RecordCount > 0 Then
					'          zlc   rota  nome  fas   sit uc medid cpfuc cpfni nis   even  sit
					istrCol = "<19mm|<14mm|<108mm|<10mm|<10mm|<15mm|<20mm|<20mm|<21mm|<15mm|<30mm;"
					With ifrAuxiliar
						.quem_chama = Me
						.VS.Font = VB6.FontChangeName(.VS.Font, "Arial")
						.VS.Font = VB6.FontChangeSize(.VS.Font, 7)
						.VS.TableBorder = VSVIEW6.TableBorderSettings.tbNone
						.VS.PageBorder = VSVIEW6.PageBorderSettings.pbNone
						.VS.ShowGuides = VSVIEW6.ShowGuideSettings.gdShow
						.VS.Orientation = VSVIEW6.OrientationSettings.orLandscape
						.VS.PhysicalPage = True
						.VS.PaperSize = VSVIEW6.PaperSizeSettings.pprA4
						.VS.MarginTop = "7mm"
						.VS.MarginRight = "7mm"
						.VS.MarginLeft = "7mm"
						.VS.MarginBottom = "5mm"
						.VS.MarginFooter = "0mm"
						.VS.MarginHeader = "0mm"
						.VS.TextColor = System.Drawing.Color.Black
						.VS.BrushStyle = VSVIEW6.BrushStyleSettings.bsSolid
						.VS.Font = VB6.FontChangeBold(.VS.Font, False)
						.VS.StartDoc()
					End With
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount)
					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"'
						lstrCpfNis = Trim(IIf(IsDbNull(.Fields("cpf").value), "", .Fields("cpf").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"'
						lstrNis = Trim(IIf(IsDbNull(.Fields("nis").value), "", .Fields("nis").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"'
						ldteCad = CDate(IIf(IsDbNull(.Fields("data_cadastro").value), "1900-01-01", .Fields("data_cadastro").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"'
						ldteAlt = CDate(IIf(IsDbNull(.Fields("data_alteracao").value), "1900-01-01", .Fields("data_alteracao").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"'
						ldteNcto = CDate(IIf(IsDbNull(.Fields("data_nascimento").value), "1900-01-01", .Fields("data_nascimento").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"'
						lstrUf = Trim(IIf(IsDbNull(.Fields("uf_nascimento").value), "", .Fields("uf_nascimento").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"'
						lstrEvento = Trim(IIf(IsDbNull(.Fields("evento").value), "", .Fields("evento").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"'
						lstrZona = Trim(IIf(IsDbNull(.Fields("zonanew").value), "", .Fields("zonanew").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 = Trim(IIf(IsDbNull(.Fields("livronew").value), "", .Fields("livronew").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 = Trim(IIf(IsDbNull(.Fields("contanew").value), "", .Fields("contanew").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"'
						lstrNomNis = Trim(IIf(IsDbNull(.Fields("nome_nis").value), "", .Fields("nome_nis").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"'
						lstrNomUc = Trim(IIf(IsDbNull(.Fields("nomeuc").value), "", .Fields("nomeuc").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"'
						lstrRotUc = Trim(IIf(IsDbNull(.Fields("rotauc").value), "", .Fields("rotauc").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"'
						lstrMedUc = Trim(IIf(IsDbNull(.Fields("medidor").value), "", .Fields("medidor").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"'
						lstrFasUc = Trim(IIf(IsDbNull(.Fields("faseuc").value), "M", .Fields("faseuc").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"'
						lstrSitUc = Trim(IIf(IsDbNull(.Fields("situuc").value), "L", .Fields("situuc").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"'
						lstrCpfUc = Trim(IIf(IsDbNull(.Fields("cpfuc").value), "", .Fields("cpfuc").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"'
						lstrLogEndUc = Trim(IIf(IsDbNull(.Fields("logenduc").value), "", .Fields("logenduc").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"'
						lstrLogNumUc = Trim(IIf(IsDbNull(.Fields("lognumuc").value), "", .Fields("lognumuc").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"'
						lstrLogComUc = Trim(IIf(IsDbNull(.Fields("logcomuc").value), "", .Fields("logcomuc").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"'
						lstrLogBaiUc = Trim(IIf(IsDbNull(.Fields("logbaiuc").value), "", .Fields("logbaiuc").value))
						If lstrCpfUc <> lstrCpfNis Then
							lstrNomUc = BuscaNomeConsumidor(lstrCpfNis)
						End If
						lstrTab = ""
						lstrTab = lstrTab & lstrZona & "." & lstrLivro & "." & lstrConta
						lstrTab = lstrTab & "|" & VB.Left(lstrRotUc, 4) & "." & VB.Right(lstrRotUc, 4)
						lstrTab = lstrTab & "|" & lstrNomNis & IIf(lstrNomUc <> lstrNomNis, "/" & lstrNomUc, "")
						lstrTab = lstrTab & "|" & IIf(lstrFasUc = "M", "MON.", IIf(lstrFasUc = "B", "BIF.", IIf(lstrFasUc = "T", "TRI.", "")))
						lstrTab = lstrTab & "|" & IIf(lstrSitUc = "L", "LIG.", IIf(lstrSitUc = "D", "DES.", IIf(lstrSitUc = "I", "INA.", IIf(lstrSitUc = "C", "COR.", IIf(lstrSitUc = "N", "N.L.", "")))))
						lstrTab = lstrTab & "|" & lstrMedUc
						lstrTab = lstrTab & "|" & FormataCPF(lstrCpfUc)
						lstrTab = lstrTab & "|" & FormataCPF(lstrCpfNis)
						lstrTab = lstrTab & "|" & lstrNis
						lstrTab = lstrTab & "|" & IIf(lstrEvento = "D", "Desativado", "Ativo")
						lstrTab = lstrTab & "|" & IIf(lstrCpfUc <> lstrCpfNis, "CPF DIFERENTE", "CPF OK")
						ifrAuxiliar.VS.Table = istrCol & lstrTab
						'ifrAuxiliar.VS.TableBorder = tbBottom
						'          If lstrNomUc <> lstrNomNis Then
						'            lstrTab = ""
						'            lstrTab = lstrTab & "<33mm|<249mm;|" & lstrNomUc
						'            ifrAuxiliar.VS.Table = lstrTab
						'          End If
						lstrTab = ""
						lstrTab = lstrTab & "<33mm|<219mm|<30mm;|" & lstrLogEndUc
						lstrTab = lstrTab & IIf(Len(lstrLogNumUc) > 0, ", " & lstrLogNumUc, "")
						lstrTab = lstrTab & IIf(Len(lstrLogComUc) > 0, " - " & lstrLogComUc, "")
						lstrTab = lstrTab & IIf(Len(lstrLogBaiUc) > 0, " " & lstrLogBaiUc, "")
						lstrTab = lstrTab & "|" & IIf(lstrCpfUc <> lstrCpfNis, "SEM DIREITO", "")
						ifrAuxiliar.VS.Table = lstrTab
						'ifrAuxiliar.VS.TableBorder = tbNone
						Call .MoveNext()
						llngTot = llngTot + 1
						If Not .EOF Then
							With ifrAuxiliar
								'UPGRADE_ISSUE: VBControlExtender method ifrAuxiliar.VS.CurrentY was not upgraded. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="CC4C7EC0-C903-48FC-ACCC-81861D12DA4A"'
								If lintCtrl = 0 And .VS.CurrentY < 11250 Then
									.VS.PenStyle = VSVIEW6.PenStyleSettings.psTransparent
									.VS.BrushColor = System.Drawing.ColorTranslator.FromOle(&HE0E0E0)
									'UPGRADE_ISSUE: VBControlExtender method ifrAuxiliar.VS.CurrentY was not upgraded. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="CC4C7EC0-C903-48FC-ACCC-81861D12DA4A"'
									Call .VS.DrawRectangle("8mm", (.VS.CurrentY), "288mm", (.VS.CurrentY) + 328)
									.VS.BrushColor = System.Drawing.Color.White
									.VS.PenStyle = VSVIEW6.PenStyleSettings.psSolid
									lintCtrl = 1
								Else
									lintCtrl = 0
								End If
							End With
						End If
						Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
					End While
					lstrTab = ""
					lstrTab = lstrTab & "<249mm;Total: " & FormatNumber(llngTot, 0)
					ifrAuxiliar.VS.Font = VB6.FontChangeBold(ifrAuxiliar.VS.Font, True)
					ifrAuxiliar.VS.Table = lstrTab
					ifrAuxiliar.VS.Font = VB6.FontChangeBold(ifrAuxiliar.VS.Font, False)
					Call ifrAuxiliar.VS.EndDoc()
					Call ifrAuxiliar.Show()
				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
	
	Public Function CabecaRel() As Object
		With ifrAuxiliar
			Call Cabecalho_Relatorio(.VS, "Listagem dos N.I.S. das unidades consumidoras em " & VB6.Format(BuscaDataBD, "dd/mm/yyyy"), istrCab, "3", "", "")
			Call LineFeed(.VS, 1)
			.VS.Table = istrCol & "Z.L.C.|Rota|Nome NIS/Nome U.C./Endereço|Fas|Sit|Medidor|CPF U.C.|CPF N.I.S.|Número N.I.S.|Evento|Mensagem"
			'UPGRADE_ISSUE: VBControlExtender method ifrAuxiliar.VS.CurrentY was not upgraded. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="CC4C7EC0-C903-48FC-ACCC-81861D12DA4A"'
			.VS.CurrentY = .VS.CurrentY + 200
			.VS.TableBorder = VSVIEW6.TableBorderSettings.tbNone
		End With
	End Function
	
	
	Private Sub frmRelNis_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		ifrAuxiliar = New FrmRelatorio
	End Sub
	
	Private Function BuscaNomeConsumidor(ByVal astrCpfCnpj As String) As String
		Dim lstrRet As String
		Dim lstrSql As String
		Dim lrstAux As ADODB.Recordset
		lrstAux = New ADODB.Recordset
		lstrRet = ""
		lstrSql = ""
		lstrSql = lstrSql & "SELECT consreg, nome, cgc"
		lstrSql = lstrSql & "  FROM consum "
		lstrSql = lstrSql & " WHERE consum.cgc = '" & astrCpfCnpj & "' "
		Call Conectar(lstrSql, lrstAux)
		With lrstAux
			If .State = ADODB.ObjectStateEnum.adStateOpen Then
				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"'
					lstrRet = Trim(IIf(IsDbNull(.Fields("nome").value), "", .Fields("nome").value))
				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
		BuscaNomeConsumidor = lstrRet
	End Function
End Class