349 lines
13 KiB
VB.net
349 lines
13 KiB
VB.net
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 |