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