Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class frmRelRendNaoFatur
	Inherits System.Windows.Forms.Form
	Private istrTabela As String
	
	Private Sub cmdEmitir_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdEmitir.Click
		Dim lstrSql As String
		
		Me.Cursor = System.Windows.Forms.Cursors.AppStarting
		Call LigaDesliga(False)
		If MontaTabela = True Then
			lblInfo.Text = " Montando Relatório..."
			lblInfo.Refresh()
			
			' Emite o relatório
			With Me.crRelRendNaoFatur
				.ReportFileName = My.Application.Info.DirectoryPath & "\" & LeConfig("DIR_RPT") & "RelRendaNaoFatur.rpt"
				.DiscardSavedData = True
				.WindowState = Crystal.WindowStateConstants.crptMaximized
				.WindowControlBox = True
				.WindowControls = True
				.WindowShowPrintSetupBtn = True
				.WindowShowCloseBtn = True
				.WindowShowSearchBtn = True
				.set_Formulas(1, "mesano=""" & VB6.Format(Me.dtpMesAno.value, "mm/yyyy") & """")
				.set_Formulas(2, "tipoRelatorio=" & IIf(Me.optSintetico.Checked, 1, 0) & "")
				.WindowTitle = "Relatório de Renda Não Faturada"
				.Destination = Crystal.DestinationConstants.crptToWindow
				.Action = 0
			End With
			
			'    ' Exclui tabela temporária se existe
			'    lstrSql = "DROP TABLE IF EXISTS " & istrTabela
			'    Call executar(lstrSql)
			
			lblInfo.Text = " Pronto!"
			lblInfo.Refresh()
		Else
			lblInfo.Text = ""
			lblInfo.Refresh()
		End If
		Me.Cursor = System.Windows.Forms.Cursors.Arrow
		Call LigaDesliga(True)
	End Sub
	
	Private Function MontaTabela() As Boolean
		Dim ldblQKwhFat As Double
		Dim ldblQKwhNaoFat As Double
		Dim ldblVKwhFat As Double
		Dim ldblVKwhNaoFat As Double
		Dim ldblQKwFat As Double
		Dim ldblQKwNaoFat As Double
		Dim ldblVKwFat As Double
		Dim ldblVKwNaoFat As Double
		
		Dim lstrZLC As String
		Dim lstrClasse As String
		Dim lstrNmClasse As String
		Dim ldblNumNF As Double
		Dim lintQtdDiasNaoFat As Short
		Dim lintQtdDiasLeit As Short
		Dim lstrGrupo As String
		Dim lstrTa As String
		Dim lstrItensserv As String
		Dim ldteLeitura As Date
		Dim lstrNome As String
		
		Dim llngCount As Integer
		Dim lstrSql As String
		Dim lstrSqlIns As String
		Dim lrstConsulta As ADODB.Recordset
		lrstConsulta = New ADODB.Recordset
		
		
		lblInfo.Text = " Buscando as faturas do mês/ano: " & VB6.Format(dtpMesAno.value, "mm/yyyy")
		lblInfo.Refresh()
		
		' Exclui tabela temporária se existe
		lstrSql = "DROP TABLE IF EXISTS `" & istrTabela & "`"
		Call Executar(lstrSql)
		
		' Cria tabela temporária
		lstrSql = "CREATE TABLE `" & istrTabela & "` (" & " `nome`          varchar(255)  NOT NULL default ''," & " `numnf`         double        NOT NULL default '0'," & " `zlc`           varchar(13)   NOT NULL default '000.00.000000'," & " `qKwhFat`       double(14,2)  NOT NULL default '0.00'," & " `qKwhNaoFat`    double(14,2)  NOT NULL default '0.00'," & " `vKwhFat`       double(14,2)  NOT NULL default '0.00'," & " `vKwhNaoFat`    double(14,2)  NOT NULL default '0.00'," & " `qKwFat`        double(14,2)  NOT NULL default '0.00'," & " `qKwNaoFat`     double(14,2)  NOT NULL default '0.00'," & " `vKwFat`        double(14,2)  NOT NULL default '0.00'," & " `vKwNaoFat`     double(14,2)  NOT NULL default '0.00'," & " `classe`        varchar(4)    NOT NULL default '0000'," & " `nmClasse`      varchar(255)  NOT NULL default ''," & " `qtdDiasLeit`   int(3)        NOT NULL default '0'," & " `qtdDiasNaoFat` int(3)        NOT NULL default '0', " & " KEY `idx_nmclasse` (`nmClasse`))"
		Call Executar(lstrSql)
		
		' Busca os dados
		lstrSql = "SELECT nf.itensserv, nf.numnf, nf.ta, nf.codclas, nf.dataleit, nf.dias, nf.zona, nf.livro, nf.conta, " & "IF(MID(nf.ta, 3, 1) = 'A', IFNULL(ua.nome, c.nome), IFNULL(ub.nome, c.nome)) AS nome " & "FROM nf " & "LEFT JOIN unidb ub ON (ub.unid_id = nf.unid_id AND ub.cons_id = nf.cons_id AND MID(ub.ta, 3, 1) = MID(nf.ta, 3, 1)) " & "LEFT JOIN unida ua ON (ua.unid_id = nf.unid_id AND ua.cons_id = nf.cons_id AND MID(ua.ta, 3, 1) = MID(nf.ta, 3, 1)) " & "LEFT JOIN consum c ON (c.consreg = nf.cons_id) " & "WHERE nf.mesano = '" & VB6.Format(dtpMesAno.value, "mm/yyyy") & "'"
		Call Conectar(lstrSql, lrstConsulta)
		
		lstrSqlIns = "INSERT INTO `" & istrTabela & "` (" & "`nome`, `numnf`, `zlc`, `qKwhFat`, `qKwhNaoFat`, " & "`vKwhFat`, `vKwhNaoFat`, `qKwFat`, `qKwNaoFat`, `vKwFat`, " & "`vKwNaoFat`, `classe`, `nmClasse`, `qtdDiasLeit`, `qtdDiasNaoFat`" & ") VALUES "
		lstrSql = ""
		llngCount = 0
		
		With lrstConsulta
			If .RecordCount > 0 Then
				Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, .RecordCount)
				Do While Not .EOF
					lblInfo.Text = " Processando: " & ProgressBar.Value & " de " & ProgressBar.Maximum
					lblInfo.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"'
					lstrNome = Trim(IIf(IsDbNull(.Fields("nome").value), "", .Fields("nome").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"'
					lstrZLC = VB.Right("000" & Trim(IIf(IsDbNull(.Fields("zona").value), "", .Fields("zona").value)), 3) & "." & VB.Right("00" & Trim(IIf(IsDbNull(.Fields("livro").value), "", .Fields("livro").value)), 2) & "." & VB.Right("000000" & Trim(IIf(IsDbNull(.Fields("conta").value), "", .Fields("conta").value)), 6)
					'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"'
					lstrClasse = Trim(IIf(IsDbNull(.Fields("codclas").value), "0000", .Fields("codclas").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"'
					ldteLeitura = CDate(IIf(IsDbNull(.Fields("dataleit").value), "1900-01-01", .Fields("dataleit").value))
					lintQtdDiasNaoFat = VB.Day(System.Date.FromOADate(DateSerial(Year(Me.dtpMesAno.value), Month(Me.dtpMesAno.value) + 1, 1).ToOADate - 1)) - VB.Day(ldteLeitura)
					'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"'
					lintQtdDiasLeit = IIf(IsDbNull(.Fields("dias").value), 0, .Fields("dias").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 = Mid(Trim(IIf(IsDbNull(.Fields("ta").value), "", .Fields("ta").value)), 3, 1)
					'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"'
					lstrTa = Mid(Trim(IIf(IsDbNull(.Fields("ta").value), "", .Fields("ta").value)), 3, 2)
					'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"'
					lstrItensserv = Trim(IIf(IsDbNull(.Fields("itensserv").value), "", .Fields("itensserv").value))
					
					
					'Quantidade e Valor faturado
					ldblQKwhFat = 0
					ldblVKwhFat = 0
					ldblQKwFat = 0
					ldblVKwFat = 0
					
					If lstrTa = "A4" Or lstrTa = "AS" Or lstrGrupo = "B" Then
						If lstrGrupo = "B" Then
							ldblQKwhFat = ldblQKwhFat + BuscaVlrItem(lstrItensserv, "030201009") 'Quantidade faturada
							ldblVKwhFat = ldblVKwhFat + BuscaVlrItem(lstrItensserv, "030202009") 'Valor faturado
						Else
							ldblQKwhFat = ldblQKwhFat + BuscaVlrItem(lstrItensserv, "0302010009") 'Quantidade faturada
							ldblVKwhFat = ldblVKwhFat + BuscaVlrItem(lstrItensserv, "0302020009") 'Valor faturado
						End If
						
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010018") 'Quantidade de Demanda de Ultrapassagem FP
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010017") 'Quantidade de Demanda
						
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020018") 'Valor da Demanda de Ultrapassagem
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020015") 'Valor da Demanda
					Else
						ldblQKwhFat = ldblQKwhFat + BuscaVlrItem(lstrItensserv, "0302010010") 'Quantidade faturada na ponta
						ldblQKwhFat = ldblQKwhFat + BuscaVlrItem(lstrItensserv, "0302010011") 'Quantidade faturada fora de ponta
						
						ldblVKwhFat = ldblVKwhFat + BuscaVlrItem(lstrItensserv, "0302020010") 'Valor faturado na ponta
						ldblVKwhFat = ldblVKwhFat + BuscaVlrItem(lstrItensserv, "0302020011") 'Valor faturado fora de ponta
						
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010022") 'Quantidade de Demanda de Ultrapassagem na ponta
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010026") 'Quantidade de Demanda de Ultrapassagem fora de ponta
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010021") 'Quantidade de Demanda na ponta
						ldblQKwFat = ldblQKwFat + BuscaVlrItem(lstrItensserv, "0302010025") 'Quantidade de Demanda fora de ponta
						
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020022") 'Valor da Demanda de Ultrapassagem na ponta
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020026") 'Valor da Demanda de Ultrapassagem fora de ponta
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020021") 'Valor da Demanda na ponta
						ldblVKwFat = ldblVKwFat + BuscaVlrItem(lstrItensserv, "0302020023") '+ BuscaVlrItem(lstrItensserv, "0302020072") 'Valor da Demanda fora de ponta
					End If
					
					
					'Quantidade e Valor não faturado
					ldblQKwhNaoFat = System.Math.Round((ldblQKwhFat / lintQtdDiasLeit) * lintQtdDiasNaoFat, 0)
					ldblVKwhNaoFat = System.Math.Round((ldblVKwhFat / lintQtdDiasLeit) * lintQtdDiasNaoFat, 2)
					ldblQKwNaoFat = System.Math.Round((ldblQKwFat / lintQtdDiasLeit) * lintQtdDiasNaoFat, 0)
					ldblVKwNaoFat = System.Math.Round((ldblVKwFat / lintQtdDiasLeit) * lintQtdDiasNaoFat, 2)
					
					
					Select Case CShort(VB.Left(lstrClasse, 1))
						Case Is < 4
							If lstrClasse = "0100" Then
								lstrNmClasse = "CONSUMIDOR LIVRE"
							Else
								lstrNmClasse = "INDUSTRIAL"
							End If
						Case Is = 4
							lstrNmClasse = "COMERCIAL"
						Case Is = 5
							lstrNmClasse = "RURAL"
						Case Is = 6
							If CDbl(VB.Left(lstrClasse, 2)) = 61 Then
								lstrNmClasse = "PODER PÚBLICO MUNICIPAL"
							ElseIf CDbl(VB.Left(lstrClasse, 2)) = 62 Then 
								lstrNmClasse = "PODER PÚBLICO ESTADUAL"
							ElseIf CDbl(VB.Left(lstrClasse, 2)) = 63 Then 
								lstrNmClasse = "PODER PÚBLICO FEDERAL"
							ElseIf CDbl(VB.Left(lstrClasse, 2)) = 64 Then 
								lstrNmClasse = "ILUMINAÇÃO PÚBLICA"
							End If
						Case Is = 7
							lstrNmClasse = "SERVIÇO PÚBLICO"
						Case Is = 8
							lstrNmClasse = "CONSUMO PRÓPRIO"
						Case Is = 9
							lstrNmClasse = "RESIDENCIAL"
						Case Else
							lstrNmClasse = "INDEFINIDO"
					End Select
					
					lstrSql = lstrSql & "(" & """" & lstrNome & """," & "'" & ldblNumNF & "'," & "'" & lstrZLC & "'," & "'" & Replace(CStr(Arredondar(ldblQKwhFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblQKwhNaoFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblVKwhFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblVKwhNaoFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblQKwFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblQKwNaoFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblVKwFat, 2)), ",", ".") & "'," & "'" & Replace(CStr(Arredondar(ldblVKwNaoFat, 2)), ",", ".") & "'," & "'" & lstrClasse & "'," & "'" & lstrNmClasse & "'," & lintQtdDiasLeit & "," & lintQtdDiasNaoFat & "),"
					
					'Insere 300 registros de uma vez
					llngCount = llngCount + 1
					If llngCount = 300 Then
						llngCount = 0
						lstrSql = lstrSqlIns & VB.Left(lstrSql, Len(lstrSql) - 1)
						Call Executar(lstrSql)
						lstrSql = ""
					End If
					
					.MoveNext()
					Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
					System.Windows.Forms.Application.DoEvents()
				Loop 
				
				'Insere os registros que sobraram
				If Len(lstrSql) > 0 Then
					lstrSql = lstrSqlIns & VB.Left(lstrSql, Len(lstrSql) - 1)
					Call Executar(lstrSql)
				End If
				
				MontaTabela = True
			Else
				Call MsgBox("Não existem faturas no período selecionado!", MsgBoxStyle.Exclamation, gstrTituloMensagem)
				MontaTabela = False
			End If
			If .State = 1 Then .Close()
		End With
		
		'UPGRADE_NOTE: Object lrstConsulta 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"'
		lrstConsulta = Nothing
		
	End Function
	
	Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
		Call Me.Close()
	End Sub
	
	Private Sub frmRelRendNaoFatur_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
		Me.dtpMesAno.value = "01/" & LeConfig("MESANOFATURAMENTO")
		istrTabela = "temp_relrendnaofatur"
	End Sub
	
	Private Sub LigaDesliga(ByRef aModo As Boolean)
		With Me
			.dtpMesAno.Enabled = aModo
			.optAnalitico.Enabled = aModo
			.optSintetico.Enabled = aModo
			.cmdEmitir.Enabled = aModo
			.cmdFechar.Enabled = aModo
		End With
		System.Windows.Forms.Application.DoEvents()
	End Sub
End Class