Option Strict Off
Option Explicit On
Imports fatd_faturamento.BLL.Repositorio
Imports fatd_faturamento.ENTIDADE
Imports CrystalDecisions.CrystalReports.Engine
Imports VB = Microsoft.VisualBasic
Friend Class FrmRelPDD
	Inherits System.Windows.Forms.Form
	
	Private Structure totClas
		Dim intClas As Short
		Dim dblValor As Double
	End Structure

	'Private fr As New FrmRelatorio
	Private dblTotal As Double
	Private lngContador As Integer
	Private lngClasse As Integer
	Private strClasseCabec As String
    Private vtClasse(10) As totClas
    Private dt, dt1 As DataTable
    Private LImagem() As Byte
    Private sNomeEmpresa As String
    Private sEnderecoEmpresa As String
    Private sCepEmpresa As String
    Private sCidadeEmpresa As String
    Private sUFempresa As String
    Private sBairroEmpresa As String
    Private sFoneEmpresa As String
    Private sWebEmpresa As String

    Private objAtualizaBancoDados As New entidadeAtualizaBancoDados
    Private repositorioAtualizaBancoDados As New repositorioAtualizaBancoDados

    Private objExecAtualizaBancoDados As New entidadeAtualizaBancoDados
    Private repositorioExecAtualizaBancoDados As New repositorioAtualizaBancoDados

    Private Sub cmdFechar_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdFechar.Click
        Call Me.Close()
    End Sub

    Private Sub cmdOK_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdOk.Click
        Try
            Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
            cmdOk.Enabled = False
            cmdFechar.Enabled = False
            Call RelPDD()
            cmdOk.Enabled = True
            cmdFechar.Enabled = True
            Me.Cursor = System.Windows.Forms.Cursors.Default
        Catch ex As Exception
            MessageBox.Show("Ocorreu o Erro: " & Err.Description & " - Rotina: cmdOK_Click" & "", "Aviso de Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

    Private Sub FrmRelPDD_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
        DTPBase.Value = Date.Now
        Call sCriaTabelaAuxiliar()
    End Sub

    Private Sub RelPDD()
        Dim strSql As String
        Dim strSql1 As String
        Dim strSql2 As String
        Dim strSql3 As String
        Dim strClasse As String
        Dim strZona As String
        Dim strLivro As String
        Dim strConta As String
        Dim strNome As String
        Dim dtVencto As Date
        Dim dblValor As Double
        Dim lngDias As Integer
        Dim Ilum As Boolean
        Dim intAux As Short
        Dim lstrsql As String

        Try

            lstrsql = ""
            lstrsql = "DELETE FROM AUX_RESERVA"
            objExecAtualizaBancoDados.QUERY_NAME = lstrsql
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

            lstrsql = ""
            lstrsql = "DELETE FROM AUX_RESERVA_TOT"
            objExecAtualizaBancoDados.QUERY_NAME = lstrsql
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

            strSql1 = "SELECT nf.codclas classe, nf.numnf, nf.mesano, nf.zona, nf.livro,  nf.conta, " & "       u.nome nome, nf.datavenc, DATEDIFF('" & VB6.Format(DTPBase.Value, "yyyy-MM-dd") & "',nf.datavenc) dias, nf.valortot " & "  FROM nf nf, uc_faturamento u"
            strSql2 = " WHERE nf.zona=u.zona " & "   AND nf.livro = u.livro " & "   AND nf.conta = u.conta " & "   AND (nf.datapag = '0000-00-00' OR nf.datapag >= '" & VB6.Format(DTPBase.Value, "yyyy-MM-dd") & "') " & "   AND NOT EXISTS(SELECT * FROM reserva " & "                   WHERE reserva.numnf = nf.numnf " & "                     AND reserva.mesano = nf.mesano " & "                     AND reserva.zona = nf.zona " & "                     AND reserva.livro = nf.livro " & "                     AND reserva.conta = nf.conta " & "                     AND reserva.datares <= '" & VB6.Format(DTPBase.Value, "yyyy-MM-dd") & "') " & "   AND to_days(nf.datavenc) <= to_days('" & VB6.Format(DTPBase.Value, "yyyy-MM-dd") & "') "

            If chkJudice.CheckState Then
                strSql2 = strSql2 & " AND trim(nf.flgst) in('R','J') "
            Else
                strSql2 = strSql2 & " AND trim(nf.flgst) in('R') "
            End If

            strSql = strSql1 & strSql2 & " ORDER BY MID(classe,1,1) ASC, datavenc ASC" '& " UNION " & strSql3 & strSql2 & " ORDER BY MID(classe,1,1) ASC, datavenc ASC"
            objAtualizaBancoDados.QUERY_NAME = strSql
            dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

            If dt.Rows.Count > 0 Then
                Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 0, dt.Rows.Count)

                For Each row As DataRow In dt.Rows

                    strClasse = IIf(IsDBNull(row.Item("classe")), "", row.Item("classe"))
                    strZona = IIf(IsDBNull(row.Item("Zona")), "", row.Item("Zona"))
                    strLivro = IIf(IsDBNull(row.Item("Livro")), "", row.Item("Livro"))
                    strConta = IIf(IsDBNull(row.Item("Conta")), "", row.Item("Conta"))
                    strNome = IIf(IsDBNull(row.Item("nome")), "", row.Item("nome"))
                    dtVencto = IIf(IsDBNull(row.Item("datavenc")), "", row.Item("datavenc"))
                    dblValor = IIf(IsDBNull(row.Item("ValorTot")), "", row.Item("ValorTot"))
                    lngDias = IIf(IsDBNull(row.Item("dias")), "", row.Item("dias"))

                    Dim sIdentificacao As String
                    Dim lstrNomClas As String
                    sIdentificacao = strZona & "." & strLivro & "." & strConta


                    Select Case VB.Left(strClasse, 1)
                        Case Is = "9"
                            If VB.Right(strClasse, 2) = "02" Or VB.Right(strClasse, 2) = "04" Or VB.Right(strClasse, 2) = "05" Or VB.Right(strClasse, 2) = "06" Then
                                lstrNomClas = "Resid. Bx.Renda"
                            Else
                                lstrNomClas = "Residencial"
                            End If
                        Case Is = "0"
                            lstrNomClas = "Industrial"
                        Case Is = "1"
                            lstrNomClas = "Industrial"
                        Case Is = "2"
                            lstrNomClas = "Industrial"
                        Case Is = "3"
                            lstrNomClas = "Industrial"
                        Case Is = "4"
                            lstrNomClas = "Comercial"
                        Case Is = "5"
                            lstrNomClas = "Rural"
                        Case Is = "6"
                            If VB.Left(strClasse, 2) = "61" Then
                                lstrNomClas = "P. Púb. Municipal"
                            ElseIf VB.Left(strClasse, 2) = "62" Then
                                lstrNomClas = "P. Púb. Estadual"
                            ElseIf VB.Left(strClasse, 2) = "63" Then
                                lstrNomClas = "P. Púb. Federal"
                            ElseIf VB.Left(strClasse, 2) = "64" Then
                                lstrNomClas = "Iluminação Púb."
                            End If
                        Case Is = "7"
                            lstrNomClas = "Serviço Público"
                        Case Is = "8"
                            lstrNomClas = "Consumo Próprio"
                        Case Else
                            lstrNomClas = "Indefinido"
                    End Select

                    lstrsql = "INSERT INTO AUX_RESERVA SET "
                    lstrsql = lstrsql & "CLASSE = '" & lstrNomClas & "' ,"
                    lstrsql = lstrsql & "IDENTIFICACAO = '" & sIdentificacao & "',"
                    'lstrsql = lstrsql & "LIVRO = '" & strLivro & "',"
                    'lstrsql = lstrsql & "CONTA = '" & strConta & "',"
                    lstrsql = lstrsql & "NOME = '" & strNome & "',"
                    lstrsql = lstrsql & "VENCIMENTO = '" & dtVencto & "',"
                    lstrsql = lstrsql & "DIAS = '" & lngDias & "',"
                    'lstrsql = lstrsql & "TOLERANCIA = '" & iTolerancia & "',"
                    'lstrsql = lstrsql & "VALOR = '" & ldblValor & "'"
                    lstrsql = lstrsql & "VALOR = " & Replace(Replace(dblValor, ".", ""), ",", ".") & ""
                    objExecAtualizaBancoDados.QUERY_NAME = lstrsql
                    repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

                    Call BarraProgresso((Me.ProgressBar), (Me.lblPerc), (Me.lblInfo), 1)
                    System.Windows.Forms.Application.DoEvents()
                Next

                lstrsql = "SELECT classe, SUM(valor) AS valor , COUNT(*) AS qtde FROM AUX_RESERVA group by classe"
                objAtualizaBancoDados.QUERY_NAME = lstrsql
                dt = repositorioExecAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                If dt.Rows.Count > 0 Then
                    For Each row As DataRow In dt.Rows

                        lstrsql = "INSERT INTO AUX_RESERVA_TOT SET "
                        lstrsql = lstrsql & "CLASSE = '" & row.Item("classe") & "' ,"
                        lstrsql = lstrsql & "QTDE = '" & row.Item("qtde") & "',"
                        lstrsql = lstrsql & "VALOR = '" & Replace(Replace(row.Item("valor"), ".", ""), ",", ".") & "'"
                        objExecAtualizaBancoDados.QUERY_NAME = lstrsql
                        repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

                    Next
                End If

                Call sDadosEmpresa(LImagem, sNomeEmpresa, sEnderecoEmpresa, sCepEmpresa, sCidadeEmpresa, sUFempresa, sBairroEmpresa, sFoneEmpresa, sWebEmpresa)
                lPrint("rptRelReserva")

            Else
                Call MsgBox("Não há consumidores para esta data base!", MsgBoxStyle.Exclamation, gstrTituloMensagem)
            End If

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

    End Sub
    Private Sub lPrint(ByVal vNomeRPT As String)

        Dim dt As DataTable
        Dim dt_1 As DataTable
        Dim dt_2 As DataTable
        Dim dt_3 As DataTable
        Dim dt_4 As DataTable
        Dim dt_5 As DataTable
        Dim dc As DataColumn
        Dim ds As DataSet
        Dim lstrsql As String

        Dim bytBLOBData() As Byte

        Dim objEmpresa As New entidadeEmpresa
        Dim repositorioEmpresa As New repositorioEmpresa

        Dim vlist

        Try
            Dim rpt As New ReportDocument()

            'rptConsumidorAVencer
            If vNomeRPT = "rptRelReserva" Then

                dt_3 = New DataTable()
                dt_3.Columns.Add("CLASSE", GetType(String))
                dt_3.Columns.Add("ZONA", GetType(String))
                dt_3.Columns.Add("LIVRO", GetType(String))
                dt_3.Columns.Add("CONTA", GetType(String))
                dt_3.Columns.Add("NOME", GetType(String))
                dt_3.Columns.Add("VENCIMENTO", GetType(String))
                dt_3.Columns.Add("DIAS", GetType(String))
                dt_3.Columns.Add("TOLERANCIA", GetType(String))
                dt_3.Columns.Add("VALOR", GetType(String))

                dt_2 = New DataTable()
                dt_2.Columns.Add("CLASSE", GetType(String))
                dt_2.Columns.Add("QTDE", GetType(String))
                dt_2.Columns.Add("VLR", GetType(String))
                dt_2.Columns.Add("teste", GetType(String))

                ds = New DataSet

                lstrsql = ""
                lstrsql = "SELECT * FROM AUX_RESERVA"
                objAtualizaBancoDados.QUERY_NAME = lstrsql
                dt = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)


                If dt.Rows.Count > 0 Then
                    For Each dr_1 As DataRow In dt.Rows

                        dt_3.Rows.Add(dr_1("CLASSE"),
                                          dr_1("IDENTIFICACAO"),
                                          dr_1("NOME"),
                                          dr_1("VENCIMENTO"),
                                          dr_1("DIAS"),
                                          dr_1("TOLERANCIA"),
                                          dr_1("VALOR"))

                    Next dr_1
                End If


                lstrsql = ""
                lstrsql = "SELECT * FROM AUX_RESERVA_TOT"
                objAtualizaBancoDados.QUERY_NAME = lstrsql
                dt1 = repositorioAtualizaBancoDados.Consultar(objAtualizaBancoDados)

                If dt1.Rows.Count > 0 Then
                    For Each dr_1 As DataRow In dt1.Rows

                        dt_2.Rows.Add(dr_1("CLASSE"),
                                      dr_1("QTDE"),
                                      dr_1("VALOR"),
                                      dr_1("VALOR"))

                    Next dr_1
                End If



                ds.Tables.Add(dt)
                ds.Tables(0).TableName = "REL_RESERVA"

                ds.Tables.Add(dt1)
                ds.Tables(1).TableName = "REL_RESERVA_TOTAL"



                If ds.Tables(0).Rows.Count = 0 Then
                    MessageBox.Show("Não há registros para o relatório escolhido! ", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit Sub
                Else

                    rpt = New rptRelReserva


                    rpt.SetDataSource(ds)
                    rpt.SetParameterValue("nomeempresa", sNomeEmpresa)
                    rpt.SetParameterValue("enderecoempresa", sEnderecoEmpresa)
                    rpt.SetParameterValue("cepempresa", sCepEmpresa)
                    rpt.SetParameterValue("ufempresa", sUFempresa)
                    rpt.SetParameterValue("cidadeempresa", sCidadeEmpresa)
                    rpt.SetParameterValue("bairroempresa", sBairroEmpresa)
                    rpt.SetParameterValue("foneempresa", sFoneEmpresa)
                    rpt.SetParameterValue("websiteempresa", sWebEmpresa)
                    rpt.SetParameterValue("database", "Data Base: " & DTPBase.Value)


                    frmViewRelatorios.rpt = rpt

                    frmViewRelatorios.CRViewer.Zoom((100))
                    frmViewRelatorios.Text = "Relatório"
                    frmViewRelatorios.ShowDialog()

                End If


            End If
            Me.Cursor = System.Windows.Forms.Cursors.Default

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

    End Sub

    Private Sub FrmRelPDD_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
        Try
            Dim lstrsql As String

            objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_RESERVA"
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

            objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_RESERVA_TOT"
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

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

    Private Sub sCriaTabelaAuxiliar()
        Try
            Dim lstrsql As String

            objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_RESERVA"
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

            lstrsql = ""
            lstrsql = "create table AUX_RESERVA("
            lstrsql = lstrsql & "CLASSE VARCHAR(50),"
            lstrsql = lstrsql & "IDENTIFICACAO VARCHAR(20),"
            'lstrsql = lstrsql & "LIVRO  VARCHAR(10),"
            'lstrsql = lstrsql & "CONTA VARCHAR(15),"
            lstrsql = lstrsql & "NOME VARCHAR(100),"
            lstrsql = lstrsql & "VENCIMENTO VARCHAR(10),"
            lstrsql = lstrsql & "DIAS VARCHAR(10),"
            lstrsql = lstrsql & "TOLERANCIA VARCHAR(10),"
            lstrsql = lstrsql & "VALOR decimal(14,2))"
            objExecAtualizaBancoDados.QUERY_NAME = lstrsql
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)


            objExecAtualizaBancoDados.QUERY_NAME = "drop table if exists AUX_RESERVA_TOT"
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

            lstrsql = ""
            lstrsql = "create table AUX_RESERVA_TOT("
            lstrsql = lstrsql & "CLASSE VARCHAR(50),"
            lstrsql = lstrsql & "VALOR varchar(20),"
            lstrsql = lstrsql & "QTDE VARCHAR(20))"
            objExecAtualizaBancoDados.QUERY_NAME = lstrsql
            repositorioExecAtualizaBancoDados.Execute(objExecAtualizaBancoDados)

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

    End Sub

End Class