client/Reportes/frmReporteJ14.vb

Imports System.Collections.Generic
Imports System.IO

Public Class frmReporteJ14
    Dim comboKey As String
    Dim year As String
    Dim quarter As String
    Dim canExport As Boolean
    Private queryGuardar As String
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim opeHist As New clsComplejo
        Dim Classcom As New clsComplejo

        Dim SQL As String = String.Empty
        Dim idInherent As Integer
        Dim idConrolEffect As Integer
        Dim incluyeInvalidos As Integer
        comboKey = DirectCast(cmbQuarter.SelectedItem, KeyValuePair(Of String, String)).Key
        year = comboKey.Substring(0, 4)
        quarter = comboKey.Substring(comboKey.Length - 1)
        canExport = False
        btnExport.Enabled = canExport
        btnExport.Visible = canExport
        btnExportXLS.Enabled = canExport
        btnExportXLS.Visible = canExport
        dgvResult.DataSource = Nothing
        incluyeInvalidos = 0



        Try

            SQL = "SP_ConsultaHistRJ14 " + year + "," + quarter + ""
            Dim dsHist As DataSet = opeHist.fdtDataSet(SQL)
            If dsHist.Tables(0).Rows.Count > 0 Then
                canExport = True
                btnGuardar.BackColor = Color.DarkGray
            End If
            btnExport.Enabled = canExport
            btnExport.Visible = canExport
            btnExportXLS.Enabled = canExport
            btnExportXLS.Visible = canExport
            btnGuardar.Visible = canExport
            btnGuardar.Enabled = Not canExport

            dgvResult.DataSource = dsHist.Tables(0)

            If dsHist.Tables(0).Rows.Count > 0 Then
                Dim Aud As New ClassMyUtils
                Aud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Consulta de Reporte Histórico J14")
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("Hist:" & ex.Message)
        End Try



        Dim result As DialogResult = MessageBox.Show("¿Incluir registros sin NIC válido?",
                              "Reporte J14",
                              MessageBoxButtons.YesNo)
        If result = DialogResult.Yes Then
            incluyeInvalidos = 1
        End If

        Try

            SQL = "sp_Consulta_ReporteJ14 " + year + "," + quarter + "," + incluyeInvalidos.ToString & "," & 0
            queryGuardar = "sp_Consulta_ReporteJ14 " + year + "," + quarter + "," + incluyeInvalidos.ToString & "," & 1
            Dim dsGral As DataSet = Classcom.fdtDataSet(SQL)
            If dsGral.Tables.Count = 0 Then
                MessageBox.Show("No existen registros para el trimestre seleccionado")
                Exit Sub
            ElseIf dsGral.Tables(0).Rows.Count = 0 Then
                MessageBox.Show("No existen registros para el trimestre seleccionado")
                Exit Sub
            Else
                canExport = True
            End If
            btnExport.Enabled = canExport
            btnExport.Visible = canExport
            btnExportXLS.Enabled = canExport
            btnExportXLS.Visible = canExport
            btnGuardar.Visible = canExport
            btnGuardar.Enabled = canExport
            btnGuardar.BackColor = Color.Black
            dgvResult.DataSource = dsGral.Tables(0)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Búsqueda Reporte J14")
    End Sub

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Dim fileName As String = year.ToString + "_" + quarter.ToString + "_ R03 J-0314.txt"
        Dim repDirectory As String = ""
        Dim fullPath As String = fileName
        Dim sr As StreamWriter
        Dim strDelimiter As String = ";"
        Dim intColumnCount As Integer = dgvResult.Columns.Count - 1
        Dim strRowData As String = ""
        Dim Classcom As New clsComplejo
        Try
            Classcom.fdtDataSet("SELECT * FROM ParamRep")
            If Classcom.ds.Tables(0).Rows.Count <> 0 Then
                repDirectory = Classcom.ds.Tables(0).Rows(0).Item("rutacarteras").ToString()
                If Directory.Exists(repDirectory) = False Then ' si no existe la carpeta se crea
                    Directory.CreateDirectory(repDirectory)
                End If
                fullPath = repDirectory & "\" & fileName
            End If
            sr = File.CreateText(fullPath)
            For intX As Integer = 0 To dgvResult.Rows.Count - 1
                strRowData = ""
                For intRowData As Integer = 0 To intColumnCount
                    strRowData += Replace(dgvResult.Rows(intX).Cells(intRowData).Value, strDelimiter, "") & IIf(intRowData < intColumnCount, strDelimiter, "") '''''''''highlights this row
                Next intRowData
                sr.WriteLine(strRowData)
            Next intX
            MessageBox.Show("Reporte generado correctamente")
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

        sr.Close()
        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Generando Reporte J14")
    End Sub

    Private Sub frmReporteJ14_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        styleControl(False)

        Dim initialYear As Integer
        Dim currentYear As Integer
        Dim comboSource As New Dictionary(Of String, String)()

        initialYear = 2019
        currentYear = Now.Year
        For year As Integer = initialYear To currentYear
            comboSource.Add(year.ToString + "1", year.ToString + " - 1")
            comboSource.Add(year.ToString + "2", year.ToString + " - 2")
            comboSource.Add(year.ToString + "3", year.ToString + " - 3")
            comboSource.Add(year.ToString + "4", year.ToString + " - 4")
        Next
        cmbQuarter.DataSource = New BindingSource(comboSource, Nothing)
        cmbQuarter.DisplayMember = "Value"
        cmbQuarter.ValueMember = "Key"
        canExport = False
        btnExport.Enabled = canExport
        btnExport.Visible = canExport
        btnExportXLS.Enabled = canExport
        btnExportXLS.Visible = canExport

        '
        cmbPeriodoAnalisis.DataSource = New BindingSource(comboSource, Nothing)
        cmbPeriodoAnalisis.DisplayMember = "Value"
        cmbPeriodoAnalisis.ValueMember = "Key"


        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Entrando Reporte J14")
    End Sub

    Private Sub frmReporteJ14_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Cerrando Reporte J14")
    End Sub

    Private Sub btnExportXLS_Click(sender As Object, e As EventArgs) Handles btnExportXLS.Click

        'Dim fileName As String = cmbPeriodoAnalisis.ToString.Substring(0, 4) + "_" + cmbPeriodoAnalisis.ToString.Substring(3, 1) + "_ R03 J-0314"
        'Dim fileName As String = cmbPeriodoAnalisis.SelectedValue.ToString.Substring(0, 4) + "_" + cmbPeriodoAnalisis.SelectedValue.ToString.Substring(4, 1) + "_ R03 J-0314"
        Dim fileName As String = cmbQuarter.SelectedValue.ToString.Substring(0, 4) + "_" + cmbQuarter.SelectedValue.ToString.Substring(4, 1) + "_ R03 J-0314"

        Dim fullPath As String = fileName
        Dim clsExcel As aExcel
        Dim ds As New DataSet
        Try
            clsExcel = New aExcel
            clsExcel.Titulo = fileName
            ds.Tables.Add("ReporteJ14")
            ' Add Columns
            Dim col As DataColumn
            For Each dgvCol As DataGridViewColumn In dgvResult.Columns
                col = New DataColumn(dgvCol.Name)
                ds.Tables("ReporteJ14").Columns.Add(col)
            Next

            'Add Rows from the datagridview
            Dim row As DataRow
            Dim colcount As Integer = dgvResult.Columns.Count - 1
            For i As Integer = 0 To dgvResult.Rows.Count - 1
                row = ds.Tables("ReporteJ14").Rows.Add
                For Each column As DataGridViewColumn In dgvResult.Columns
                    row.Item(column.Index) = dgvResult.Rows.Item(i).Cells(column.Index).Value
                Next
            Next

            clsExcel.ExportDataset(ds)
            'MessageBox.Show("Reporte generado correctamente")
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Exportando Reporte J14")
    End Sub

    Private Sub btnGuardar_Click(sender As Object, e As EventArgs) Handles btnGuardar.Click

        Dim Classcom As New SenteciasSQL.ClassSQL

        If queryGuardar <> "" Then

            Dim dsGral As DataSet = Classcom.fdtDataSetTabla(queryGuardar)

            '            dgvResult.DataSource = dsGral.Tables(0)
            btnGuardar.BackColor = Color.DarkGray
            btnGuardar.Enabled = False
            MsgBox("Reporte guadado correctamente")

            Dim Aud As New ClassMyUtils
            Aud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Guardar Reporte Histórico J14")

        Else
            MsgBox("Realice previamente una busqueda")
        End If



    End Sub

    Private Sub btnBuscarAnalisis_Click(sender As Object, e As EventArgs) Handles btnBuscarAnalisis.Click
        Dim year As Integer
        Dim noTrimestre As Integer
        Dim Classcom As New SenteciasSQL.ClassSQL
        Dim ClOp2 As New SenteciasSQL.ClassSQL

        If cmbPeriodoAnalisis.SelectedValue IsNot Nothing Then
            year = cmbPeriodoAnalisis.SelectedValue.ToString().Substring(0, 4)
            noTrimestre = cmbPeriodoAnalisis.SelectedValue.ToString().Substring(4, 1)


            Dim opResumen As String = "SP_RESUMENRJ14 " & year & "," & noTrimestre
            Dim opDetalle As String = "SP_DETALLERJ14 " & year & "," & noTrimestre


            Dim dsGral As DataSet = Classcom.fdtDataSetTabla(opResumen)

            dgvResumen.DataSource = dsGral.Tables(0)


            Dim dsDetealle As DataSet
            dsDetealle = ClOp2.fdtDataSetTabla(opDetalle)
            dgvAnalisis.DataSource = dsDetealle.Tables(0)



            If dgvResumen.RowCount > 0 And dgvAnalisis.RowCount > 0 Then
                btnExportarAnalisis.Enabled = True
                styleControl(True)
            Else
                btnExportarAnalisis.Enabled = False
                styleControl(False)

            End If


        Else
            MsgBox("Selecciona un trimestre.")

        End If

    End Sub

    Private Sub styleControl(opc As Boolean)

        If opc Then
            btnExportarAnalisis.BackColor = Color.Black
            btnExportarAnalisis.ForeColor = Color.White
        Else
            btnExportarAnalisis.BackColor = Color.Gray
            btnExportarAnalisis.ForeColor = Color.Black
        End If

    End Sub


    Private Sub btnExportarAnalisis_Click(sender As Object, e As EventArgs) Handles btnExportarAnalisis.Click

        Dim fileName As String = cmbPeriodoAnalisis.SelectedValue.ToString.Substring(0, 4) + "_" + cmbPeriodoAnalisis.SelectedValue.ToString.Substring(4, 1) + "_ R03 J-0314"
        Dim fullPath As String = fileName
        Dim clsExcel As aExcel
        Dim ds As New DataSet
        Try
            clsExcel = New aExcel
            clsExcel.Titulo = fileName
            ds.Tables.Add("ReporteJ14_1")

            ' Add Columns
            Dim col As DataColumn
            For Each dgvCol As DataGridViewColumn In dgvResumen.Columns
                col = New DataColumn(dgvCol.Name)
                ds.Tables("ReporteJ14_1").Columns.Add(col)
            Next

            'Add Rows from the datagridview
            Dim row As DataRow
            Dim colcount As Integer = dgvResumen.Columns.Count - 1
            For i As Integer = 0 To dgvResumen.Rows.Count - 1
                row = ds.Tables("ReporteJ14_1").Rows.Add
                For Each column As DataGridViewColumn In dgvResumen.Columns
                    row.Item(column.Index) = dgvResumen.Rows.Item(i).Cells(column.Index).Value
                Next
            Next


            ds.Tables.Add("ReporteJ14_2")

            ' Add Columns
            Dim col2 As DataColumn
            For Each dgvCol As DataGridViewColumn In dgvAnalisis.Columns
                col2 = New DataColumn(dgvCol.Name)
                ds.Tables("ReporteJ14_2").Columns.Add(col2)
            Next

            'Add Rows from the datagridview
            Dim row2 As DataRow
            Dim colcount2 As Integer = dgvAnalisis.Columns.Count - 1
            For i As Integer = 0 To dgvAnalisis.Rows.Count - 1
                row2 = ds.Tables("ReporteJ14_2").Rows.Add
                For Each column As DataGridViewColumn In dgvAnalisis.Columns
                    row2.Item(column.Index) = dgvAnalisis.Rows.Item(i).Cells(column.Index).Value
                Next
            Next

            clsExcel.ExportTo2Woook(ds)
            'MessageBox.Show("Reporte generado correctamente")
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
        Dim ClassAud As New ClassMyUtils
        ClassAud.RT_Auditoria(clsVaribles.varUsuario, 121, 1, "Exportando Reporte J14 Resumen")

    End Sub
End Class