| | | 1 | | using ClosedXML.Excel; |
| | | 2 | | using FAU.DataAccess; |
| | | 3 | | using FAU.Entidades; |
| | | 4 | | using FAU.Logica.DTOs; |
| | | 5 | | using Microsoft.EntityFrameworkCore; |
| | | 6 | | |
| | | 7 | | namespace FAU.Logica.Services; |
| | | 8 | | |
| | | 9 | | public class ExporteMdnExcelService : IExporteMdnExcelService |
| | | 10 | | { |
| | | 11 | | private readonly ApplicationDbContext _context; |
| | | 12 | | private readonly IReporteMDNService _reporteMdnService; |
| | | 13 | | |
| | 0 | 14 | | public ExporteMdnExcelService(ApplicationDbContext context, IReporteMDNService reporteMdnService) |
| | | 15 | | { |
| | 0 | 16 | | _context = context; |
| | 0 | 17 | | _reporteMdnService = reporteMdnService; |
| | 0 | 18 | | } |
| | | 19 | | |
| | | 20 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 21 | | // DTOs internos |
| | | 22 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | 0 | 23 | | private sealed record ItemRaw(string Cedula, string CodigoConcepto, decimal Importe, string RubroContable); |
| | | 24 | | |
| | 0 | 25 | | private sealed record PersonaMdn( |
| | 0 | 26 | | string Cedula, |
| | 0 | 27 | | string Apellido, |
| | 0 | 28 | | string Nombre, |
| | 0 | 29 | | string Escalafon, |
| | 0 | 30 | | string Grado, |
| | 0 | 31 | | string Situacion, |
| | 0 | 32 | | string Programa, |
| | 0 | 33 | | long PersonaId, |
| | 0 | 34 | | short? AniosServicioAnterior, |
| | 0 | 35 | | DateTime? FechaInicio, |
| | 0 | 36 | | DateTime? FechaFin, |
| | 0 | 37 | | DateTime? FechaAscensoOficial); |
| | | 38 | | |
| | 0 | 39 | | private sealed record DedDep( |
| | 0 | 40 | | long PersonaId, |
| | 0 | 41 | | int CantDisca, |
| | 0 | 42 | | int DedHij100, |
| | 0 | 43 | | int DedHij50); |
| | | 44 | | |
| | | 45 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 46 | | // Mapeo hardcode código de concepto → campo MDN legacy |
| | | 47 | | // Solo para los códigos que NO tienen OG asociado o tienen destino especial. |
| | | 48 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 49 | | /// <summary> |
| | | 50 | | /// Diccionario de excepciones: código de concepto → campo MDN legacy. |
| | | 51 | | /// Usado cuando el catálogo no tiene OG o el OG no mapea a un campo MDN legacy. |
| | | 52 | | /// </summary> |
| | 0 | 53 | | private static readonly Dictionary<string, string> OverrideCodigoACampo = new() |
| | 0 | 54 | | { |
| | 0 | 55 | | // Fictos IRPF (no tienen OG en el legacy) |
| | 0 | 56 | | ["370"] = "Boletos", |
| | 0 | 57 | | ["372"] = "Comedor", |
| | 0 | 58 | | ["374"] = "Alquiler", |
| | 0 | 59 | | ["375"] = "Guarderia", |
| | 0 | 60 | | ["376"] = "Ticket", |
| | 0 | 61 | | |
| | 0 | 62 | | // Descuentos fictos IRPF |
| | 0 | 63 | | ["607"] = "Boletos_2", |
| | 0 | 64 | | ["608"] = "Comedor_2", |
| | 0 | 65 | | ["609"] = "Alquiler_2", |
| | 0 | 66 | | ["613"] = "Boletos_2", |
| | 0 | 67 | | ["614"] = "Guarderia2", |
| | 0 | 68 | | ["618"] = "Ticket_2", |
| | 0 | 69 | | |
| | 0 | 70 | | // Códigos especiales del legacy → R_99_99 |
| | 0 | 71 | | ["9"] = "R_99_99", |
| | 0 | 72 | | ["690"] = "R_99_99", |
| | 0 | 73 | | ["691"] = "R_99_99", |
| | 0 | 74 | | ["692"] = "R_99_99", |
| | 0 | 75 | | ["693"] = "R_99_99", |
| | 0 | 76 | | ["694"] = "R_99_99", |
| | 0 | 77 | | ["695"] = "R_99_99", |
| | 0 | 78 | | ["696"] = "R_99_99", |
| | 0 | 79 | | |
| | 0 | 80 | | // Códigos de catálogo nuevo (5 dígitos) → campo MDN legacy |
| | 0 | 81 | | // Cuando el OG del catálogo no coincide con el nombre de campo legacy. |
| | 0 | 82 | | ["280"] = "R_234_002", // Rancho Metálico |
| | 0 | 83 | | ["281"] = "R_234_000", // Viático de Traslado (legacy mapea aquí) |
| | 0 | 84 | | ["300"] = "R_234_000", // Viático de Traslado (variante) |
| | 0 | 85 | | |
| | 0 | 86 | | // IRPF — en el legacy el código 517 (IRPF Sueldo) → R_1_089 |
| | 0 | 87 | | ["517"] = "R_1_089", |
| | 0 | 88 | | ["518"] = "R_1_104", |
| | 0 | 89 | | ["519"] = "R_1_089", // IRPF Civil |
| | 0 | 90 | | |
| | 0 | 91 | | // Sanidad militar por tramos — código legacy 5010/5011/5012 |
| | 0 | 92 | | ["5010"] = "R_5_010", |
| | 0 | 93 | | ["5011"] = "R_5_011", |
| | 0 | 94 | | ["5012"] = "R_5_012", |
| | 0 | 95 | | |
| | 0 | 96 | | // FRL — no tiene OG claro en legacy |
| | 0 | 97 | | ["FRL"] = "R_5_012", |
| | 0 | 98 | | }; |
| | | 99 | | |
| | | 100 | | // Columnas exactas del legacy LIQ_MDN, en orden |
| | 0 | 101 | | private static readonly string[] ColumnasLiquidacionMdn = |
| | 0 | 102 | | { |
| | 0 | 103 | | "cedula_7", "apellido", "nombre", "esc", "grado", "situacion", "programa", |
| | 0 | 104 | | "R_011_000", "R_011_300", "R_015_000", "R_031_000", "R_041_003", "R_041_008", |
| | 0 | 105 | | "R_042_000", "R_042_012", "R_042_014", "R_042_019", "R_042_022", "R_042_025", |
| | 0 | 106 | | "R_042_067", "R_042_093", "R_042_103", "R_042_400", "R042_520_1", "R042_520_2", |
| | 0 | 107 | | "R042_520_3", "R_042_536", "R_042_610", "R_042_611", "R_043_003", "R_043_004", |
| | 0 | 108 | | "R_044_001", "R_047_500", "R_048_004", "R_048_009", "R_048_012", "R_048_015", |
| | 0 | 109 | | "R_048_017", "R_048_018", "R_048_023", "R_048_026", "R_048_032", "R_048_038", |
| | 0 | 110 | | "R_048_040", "R_048_042", "R_059_000", "R_066_000", "R_071_000", "R_072_000", |
| | 0 | 111 | | "R_073_000", "R_074_000", "R_079_001", "R_1_01", "R_1_03", "R_1_04", |
| | 0 | 112 | | "R_1_089", "R_1_104", "R_122_001", "R_234_000", "R_234_002", "R_4_01", |
| | 0 | 113 | | "R_5_01", "R_5_010", "R_5_011", "R_5_012", "R_5_03", "R_5_04", |
| | 0 | 114 | | "R_5_05", "R_5_08", "R_513_008", "R_9_064", "R_9_070", "R_9_071", |
| | 0 | 115 | | "R_9_074", "Boletos", "Boletos_2", "Comedor", "Comedor_2", "Alquiler", |
| | 0 | 116 | | "Alquiler_2", "Ticket", "Ticket_2", "Guarderia", "Guarderia2", "R_99_99", |
| | 0 | 117 | | "SuelPaIRPF", "ded_Sanid", "dedu_mont", "dedu_pasiv", "dedu_Hijos", |
| | 0 | 118 | | "dedu_Profe", "Cant_disca", "ded_hij100", "ded_hij_50" |
| | 0 | 119 | | }; |
| | | 120 | | |
| | | 121 | | /// <summary> |
| | | 122 | | /// Convierte un OG SIIF (ej. "042.012", "011.000") al nombre de campo MDN legacy (ej. "R_042_012"). |
| | | 123 | | /// Null/empty → cadena vacía. |
| | | 124 | | /// </summary> |
| | | 125 | | private static string OgACampoMdn(string? og) |
| | | 126 | | { |
| | 0 | 127 | | if (string.IsNullOrWhiteSpace(og)) return ""; |
| | 0 | 128 | | var parts = og.Split('.'); |
| | 0 | 129 | | if (parts.Length != 2) return ""; |
| | 0 | 130 | | return $"R_{parts[0]}_{parts[1]}"; |
| | | 131 | | } |
| | | 132 | | |
| | | 133 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 134 | | // API pública |
| | | 135 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 136 | | |
| | | 137 | | public async Task<(byte[] Bytes, string FileName)> GenerarExcelAsync(long periodoId, long? regimenId = null) |
| | | 138 | | { |
| | 0 | 139 | | var periodo = await _context.PeriodosLiquidacion.AsNoTracking().FirstAsync(p => p.Id == periodoId); |
| | 0 | 140 | | var version = await ObtenerVersionMaximaAsync(periodoId); |
| | | 141 | | |
| | | 142 | | // ── Items de liquidación ─────────────────────────────────────────────── |
| | 0 | 143 | | var items = await _context.LiquidacionItems |
| | 0 | 144 | | .AsNoTracking() |
| | 0 | 145 | | .Where(i => i.PeriodoId == periodoId && i.Version == version) |
| | 0 | 146 | | .Select(i => new ItemRaw(i.Cedula, i.CodigoConcepto, i.Importe, i.RubroContable)) |
| | 0 | 147 | | .ToListAsync(); |
| | | 148 | | |
| | | 149 | | // ── Catálogo para clasificación y resolución de OG ──────────────────── |
| | 0 | 150 | | var catalogoItems = await _context.CatalogoItems |
| | 0 | 151 | | .AsNoTracking() |
| | 0 | 152 | | .Where(ci => items.Select(i => i.CodigoConcepto).Contains(ci.Codigo.ToString())) |
| | 0 | 153 | | .ToListAsync(); |
| | | 154 | | |
| | 0 | 155 | | var catalogoDict = catalogoItems.ToDictionary(ci => ci.Codigo.ToString(), ci => ci.ClasificacionSiif ?? ""); |
| | 0 | 156 | | var catalogoTipoDict = catalogoItems.ToDictionary(ci => ci.Codigo.ToString(), ci => ci.Tipo); |
| | | 157 | | |
| | 0 | 158 | | items = items.Where(i => catalogoTipoDict.GetValueOrDefault(i.CodigoConcepto) != "descuento_personal").ToList(); |
| | 0 | 159 | | var cedulas = items.Select(i => i.Cedula).Distinct().ToList(); |
| | | 160 | | |
| | | 161 | | // ── Montepio por cédula (para columna dedu_mont) ─────────────────────── |
| | 0 | 162 | | var montepioPorCedula = items |
| | 0 | 163 | | .Where(i => i.CodigoConcepto == "550") |
| | 0 | 164 | | .GroupBy(i => i.Cedula) |
| | 0 | 165 | | .ToDictionary(g => g.Key, g => Math.Abs(g.Sum(i => i.Importe))); |
| | | 166 | | |
| | | 167 | | // ── Datos personales ────────────────────────────────────────────────── |
| | 0 | 168 | | var personasDict = await _context.RelacionesLaborales |
| | 0 | 169 | | .AsNoTracking() |
| | 0 | 170 | | .Where(r => cedulas.Contains(r.Persona.Cedula) && r.Estado == EstadoRelacion.Activo) |
| | 0 | 171 | | .Select(r => new PersonaMdn( |
| | 0 | 172 | | r.Persona.Cedula, |
| | 0 | 173 | | $"{r.Persona.PrimerApellido} {r.Persona.SegundoApellido}".Trim(), |
| | 0 | 174 | | $"{r.Persona.PrimerNombre} {r.Persona.SegundoNombre}".Trim(), |
| | 0 | 175 | | r.Escalafon.Denominacion, |
| | 0 | 176 | | r.Grado.Denominacion, |
| | 0 | 177 | | r.Situacion.Denominacion, |
| | 0 | 178 | | r.Programa.Denominacion, |
| | 0 | 179 | | r.PersonaId, |
| | 0 | 180 | | r.AniosServicioAnterior, |
| | 0 | 181 | | r.FechaInicio, |
| | 0 | 182 | | r.FechaFin, |
| | 0 | 183 | | r.FechaAscensoOficial)) |
| | 0 | 184 | | .ToDictionaryAsync(x => x.Cedula); |
| | | 185 | | |
| | 0 | 186 | | var personasCrudas = await _context.Personas |
| | 0 | 187 | | .AsNoTracking() |
| | 0 | 188 | | .Where(p => cedulas.Contains(p.Cedula)) |
| | 0 | 189 | | .ToDictionaryAsync(p => p.Cedula); |
| | | 190 | | |
| | | 191 | | // ── IRPF ─────────────────────────────────────────────────────────────── |
| | 0 | 192 | | var personaIds = personasDict.Values.Select(p => p.PersonaId).Distinct().ToList(); |
| | 0 | 193 | | var irpfDict = await _context.IrpfsMensual |
| | 0 | 194 | | .AsNoTracking() |
| | 0 | 195 | | .Where(i => i.LiquidacionId == periodoId && personaIds.Contains(i.PersonaId)) |
| | 0 | 196 | | .ToDictionaryAsync(i => i.PersonaId); |
| | | 197 | | |
| | | 198 | | // ── Dependientes ─────────────────────────────────────────────────────── |
| | 0 | 199 | | var dedDict = await _context.Dependientes |
| | 0 | 200 | | .AsNoTracking() |
| | 0 | 201 | | .Where(d => personaIds.Contains(d.PersonaId) && d.Activo) |
| | 0 | 202 | | .GroupBy(d => d.PersonaId) |
| | 0 | 203 | | .Select(g => new DedDep( |
| | 0 | 204 | | g.Key, |
| | 0 | 205 | | g.Count(d => d.Tipo == "hijo_discapacidad"), |
| | 0 | 206 | | g.Count(d => d.Tipo.StartsWith("hijo") && d.PorcentajeDeduccion == 100), |
| | 0 | 207 | | g.Count(d => d.Tipo.StartsWith("hijo") && d.PorcentajeDeduccion == 50))) |
| | 0 | 208 | | .ToDictionaryAsync(x => x.PersonaId); |
| | | 209 | | |
| | 0 | 210 | | using var wb = new XLWorkbook(); |
| | | 211 | | |
| | 0 | 212 | | GenerarHojaLiquidacion(wb, "Liquidacion_MDN", items, catalogoDict, personasDict, irpfDict, dedDict, montepioPorC |
| | 0 | 213 | | GenerarHojaPersonal(wb, "Personal_MDN", items, personasDict, personasCrudas); |
| | | 214 | | |
| | 0 | 215 | | var control = await _reporteMdnService.ObtenerControlPorcentualAsync(periodoId, regimenId); |
| | 0 | 216 | | GenerarHojaControlPorcentual(wb, "Control_Porcentual", control); |
| | | 217 | | |
| | 0 | 218 | | using var ms = new MemoryStream(); |
| | 0 | 219 | | wb.SaveAs(ms); |
| | 0 | 220 | | var fileName = $"MDN_{periodo.Anio}_{periodo.Mes:D2}_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"; |
| | 0 | 221 | | return (ms.ToArray(), fileName); |
| | 0 | 222 | | } |
| | | 223 | | |
| | | 224 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 225 | | // Mapeo principal: resuelve OG del catálogo como fallback, luego override |
| | | 226 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 227 | | |
| | | 228 | | /// <summary> |
| | | 229 | | /// Determina el campo MDN legacy para un item de liquidación. |
| | | 230 | | /// 1. Si el item tiene RubroContable poblado → mapea por OG. |
| | | 231 | | /// 2. Si está vacío → busca el OG en el catálogo por CodigoConcepto. |
| | | 232 | | /// 3. Si aún sin OG → aplica override por código de concepto. |
| | | 233 | | /// </summary> |
| | | 234 | | private static string ObtenerCampoMdn(ItemRaw item, Dictionary<string, string> catalogoDict) |
| | | 235 | | { |
| | | 236 | | // Paso 1: intentar OG del item mismo |
| | 0 | 237 | | var og = item.RubroContable; |
| | 0 | 238 | | if (string.IsNullOrWhiteSpace(og)) |
| | | 239 | | { |
| | | 240 | | // Paso 2: fallback al catálogo |
| | 0 | 241 | | catalogoDict.TryGetValue(item.CodigoConcepto, out og); |
| | | 242 | | } |
| | 0 | 243 | | if (!string.IsNullOrWhiteSpace(og)) |
| | | 244 | | { |
| | 0 | 245 | | var campo = OgACampoMdn(og); |
| | 0 | 246 | | if (!string.IsNullOrEmpty(campo)) return campo; |
| | | 247 | | } |
| | | 248 | | // Paso 3: override por código |
| | 0 | 249 | | OverrideCodigoACampo.TryGetValue(item.CodigoConcepto, out var overrideCampo); |
| | 0 | 250 | | return overrideCampo ?? ""; |
| | | 251 | | } |
| | | 252 | | |
| | | 253 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 254 | | // Hoja 1 — Liquidacion_MDN |
| | | 255 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 256 | | |
| | | 257 | | private static void GenerarHojaLiquidacion( |
| | | 258 | | XLWorkbook wb, |
| | | 259 | | string sheetName, |
| | | 260 | | List<ItemRaw> items, |
| | | 261 | | Dictionary<string, string> catalogoDict, |
| | | 262 | | Dictionary<string, PersonaMdn> personasDict, |
| | | 263 | | Dictionary<long, IrpfMensual> irpfDict, |
| | | 264 | | Dictionary<long, DedDep> dedDict, |
| | | 265 | | Dictionary<string, decimal> montepioPorCedula) |
| | | 266 | | { |
| | 0 | 267 | | var ws = wb.Worksheets.Add(sheetName); |
| | | 268 | | |
| | 0 | 269 | | for (int c = 0; c < ColumnasLiquidacionMdn.Length; c++) |
| | 0 | 270 | | ws.Cell(1, c + 1).Value = ColumnasLiquidacionMdn[c]; |
| | | 271 | | |
| | 0 | 272 | | var acumuladoPorCedula = new Dictionary<string, Dictionary<string, decimal>>(); |
| | 0 | 273 | | foreach (var item in items) |
| | | 274 | | { |
| | 0 | 275 | | var campo = ObtenerCampoMdn(item, catalogoDict); |
| | 0 | 276 | | if (string.IsNullOrEmpty(campo)) continue; |
| | 0 | 277 | | if (!acumuladoPorCedula.TryGetValue(item.Cedula, out var campos)) |
| | | 278 | | { |
| | 0 | 279 | | campos = new Dictionary<string, decimal>(); |
| | 0 | 280 | | acumuladoPorCedula[item.Cedula] = campos; |
| | | 281 | | } |
| | 0 | 282 | | campos[campo] = campos.GetValueOrDefault(campo) + item.Importe; |
| | | 283 | | } |
| | | 284 | | |
| | 0 | 285 | | int row = 2; |
| | 0 | 286 | | foreach (var (cedula, campos) in acumuladoPorCedula.OrderBy(kv => kv.Key)) |
| | | 287 | | { |
| | 0 | 288 | | if (!personasDict.TryGetValue(cedula, out var p)) continue; |
| | | 289 | | |
| | 0 | 290 | | ws.Cell(row, 1).Value = cedula; |
| | 0 | 291 | | ws.Cell(row, 2).Value = p.Apellido; |
| | 0 | 292 | | ws.Cell(row, 3).Value = p.Nombre; |
| | 0 | 293 | | ws.Cell(row, 4).Value = p.Escalafon; |
| | 0 | 294 | | ws.Cell(row, 5).Value = p.Grado; |
| | 0 | 295 | | ws.Cell(row, 6).Value = p.Situacion; |
| | 0 | 296 | | ws.Cell(row, 7).Value = p.Programa; |
| | | 297 | | |
| | 0 | 298 | | for (int c = 7; c < ColumnasLiquidacionMdn.Length; c++) |
| | | 299 | | { |
| | 0 | 300 | | var campo = ColumnasLiquidacionMdn[c]; |
| | 0 | 301 | | if (campos.TryGetValue(campo, out var val)) |
| | 0 | 302 | | ws.Cell(row, c + 1).Value = val; |
| | | 303 | | } |
| | | 304 | | |
| | 0 | 305 | | if (irpfDict.TryGetValue(p.PersonaId, out var irpf)) |
| | | 306 | | { |
| | 0 | 307 | | var baseCol = ColumnasLiquidacionMdn.Length - 9; |
| | 0 | 308 | | var dedMontepio = montepioPorCedula.GetValueOrDefault(cedula); |
| | 0 | 309 | | ws.Cell(row, baseCol + 1).Value = irpf.IngresosGravados; // SuelPaIRPF |
| | 0 | 310 | | ws.Cell(row, baseCol + 2).Value = irpf.DeduccionAportes; // ded_Sanid |
| | 0 | 311 | | ws.Cell(row, baseCol + 3).Value = dedMontepio; // dedu_mont |
| | 0 | 312 | | ws.Cell(row, baseCol + 4).Value = Math.Max(0m, irpf.DeduccionAportes - dedMontepio); // dedu_pasiv |
| | 0 | 313 | | ws.Cell(row, baseCol + 5).Value = irpf.DeduccionHijos; // dedu_Hijos |
| | 0 | 314 | | ws.Cell(row, baseCol + 6).Value = irpf.DeduccionProfesional; // dedu_Profe |
| | | 315 | | } |
| | | 316 | | |
| | 0 | 317 | | if (dedDict.TryGetValue(p.PersonaId, out var dep)) |
| | | 318 | | { |
| | 0 | 319 | | var baseCol = ColumnasLiquidacionMdn.Length - 3; |
| | 0 | 320 | | ws.Cell(row, baseCol + 1).Value = dep.CantDisca; // Cant_disca |
| | 0 | 321 | | ws.Cell(row, baseCol + 2).Value = dep.DedHij100; // ded_hij100 |
| | 0 | 322 | | ws.Cell(row, baseCol + 3).Value = dep.DedHij50; // ded_hij_50 |
| | | 323 | | } |
| | | 324 | | |
| | 0 | 325 | | row++; |
| | | 326 | | } |
| | | 327 | | |
| | 0 | 328 | | ws.Columns().AdjustToContents(); |
| | 0 | 329 | | } |
| | | 330 | | |
| | | 331 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 332 | | // Hoja 2 — Personal_MDN |
| | | 333 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 334 | | |
| | | 335 | | private static void GenerarHojaPersonal( |
| | | 336 | | XLWorkbook wb, |
| | | 337 | | string sheetName, |
| | | 338 | | List<ItemRaw> items, |
| | | 339 | | Dictionary<string, PersonaMdn> personasDict, |
| | | 340 | | Dictionary<string, Persona> personasCrudas) |
| | | 341 | | { |
| | 0 | 342 | | var ws = wb.Worksheets.Add(sheetName); |
| | | 343 | | |
| | 0 | 344 | | string[] cols = |
| | 0 | 345 | | { |
| | 0 | 346 | | "cedula", "ape1_per", "ape2_per", "nom1_per", "nom2_per", |
| | 0 | 347 | | "situa_per", "unidad", "compania", "esc_per", "grado", |
| | 0 | 348 | | "anios_trop", "anios_adm", "fec_ing", "fec_retiro", "fec_ofi", |
| | 0 | 349 | | "fec_sub", "fec_ult_as", "programa", "nom_sit", "nom_grado", "antiguedad" |
| | 0 | 350 | | }; |
| | | 351 | | |
| | 0 | 352 | | for (int c = 0; c < cols.Length; c++) |
| | 0 | 353 | | ws.Cell(1, c + 1).Value = cols[c]; |
| | | 354 | | |
| | 0 | 355 | | var cedulasConLiq = items.Select(i => i.Cedula).Distinct().ToHashSet(); |
| | | 356 | | |
| | 0 | 357 | | int row = 2; |
| | 0 | 358 | | foreach (var (cedula, p) in personasDict.OrderBy(kv => kv.Key).Where(kv => cedulasConLiq.Contains(kv.Key))) |
| | | 359 | | { |
| | 0 | 360 | | personasCrudas.TryGetValue(cedula, out var pc); |
| | | 361 | | |
| | 0 | 362 | | ws.Cell(row, 1).Value = cedula; |
| | 0 | 363 | | ws.Cell(row, 2).Value = pc?.PrimerApellido ?? p.Apellido; |
| | 0 | 364 | | ws.Cell(row, 3).Value = pc?.SegundoApellido ?? ""; |
| | 0 | 365 | | ws.Cell(row, 4).Value = pc?.PrimerNombre ?? p.Nombre; |
| | 0 | 366 | | ws.Cell(row, 5).Value = pc?.SegundoNombre ?? ""; |
| | 0 | 367 | | ws.Cell(row, 6).Value = p.Situacion; |
| | 0 | 368 | | ws.Cell(row, 7).Value = ""; |
| | 0 | 369 | | ws.Cell(row, 8).Value = ""; |
| | 0 | 370 | | ws.Cell(row, 9).Value = p.Escalafon; |
| | 0 | 371 | | ws.Cell(row, 10).Value = p.Grado; |
| | 0 | 372 | | ws.Cell(row, 11).Value = p.AniosServicioAnterior; |
| | 0 | 373 | | ws.Cell(row, 12).Value = 0; |
| | 0 | 374 | | ws.Cell(row, 13).Value = p.FechaInicio; |
| | 0 | 375 | | ws.Cell(row, 14).Value = p.FechaFin; |
| | 0 | 376 | | ws.Cell(row, 15).Value = p.FechaAscensoOficial; |
| | 0 | 377 | | ws.Cell(row, 16).Value = (DateTime?)null; |
| | 0 | 378 | | ws.Cell(row, 17).Value = (DateTime?)null; |
| | 0 | 379 | | ws.Cell(row, 18).Value = p.Programa; |
| | 0 | 380 | | ws.Cell(row, 19).Value = p.Situacion; |
| | 0 | 381 | | ws.Cell(row, 20).Value = p.Grado; |
| | 0 | 382 | | var ant = p.FechaInicio.HasValue |
| | 0 | 383 | | ? (int)((DateTime.Now - p.FechaInicio.Value).TotalDays / 365) + (p.AniosServicioAnterior ?? 0) |
| | 0 | 384 | | : p.AniosServicioAnterior ?? 0; |
| | 0 | 385 | | ws.Cell(row, 21).Value = ant; |
| | | 386 | | |
| | 0 | 387 | | row++; |
| | | 388 | | } |
| | | 389 | | |
| | 0 | 390 | | ws.Columns().AdjustToContents(); |
| | 0 | 391 | | } |
| | | 392 | | |
| | | 393 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 394 | | // Hoja 3 — Control_Porcentual |
| | | 395 | | // ═════════════════════════════════════════════════════════════════════════════ |
| | | 396 | | |
| | | 397 | | private static void GenerarHojaControlPorcentual(XLWorkbook wb, string sheetName, ControlPorcentualMdnDto dto) |
| | | 398 | | { |
| | 0 | 399 | | var ws = wb.Worksheets.Add(sheetName); |
| | 0 | 400 | | int row = 1; |
| | | 401 | | |
| | 0 | 402 | | ws.Cell(row, 1).Value = "RESUMEN RETRIBUCIONES PERCIBIDAS — Porcentajes a cobrar sobre Líquido"; |
| | 0 | 403 | | ws.Range(row, 1, row, 5).Merge(); |
| | 0 | 404 | | row += 2; |
| | | 405 | | |
| | 0 | 406 | | string[] headers = { "Cédula", "Nombre", "Grado", "Unidad", "Haberes Gravados", |
| | 0 | 407 | | "Haberes No Gravados", "Beneficios Sociales", "Descuentos Legales", "Líquido MDN", |
| | 0 | 408 | | "Cálculo 30%", "Porcentaje Real" }; |
| | 0 | 409 | | for (int c = 0; c < headers.Length; c++) |
| | 0 | 410 | | ws.Cell(row, c + 1).Value = headers[c]; |
| | 0 | 411 | | row++; |
| | | 412 | | |
| | 0 | 413 | | foreach (var f in dto.Filas) |
| | | 414 | | { |
| | 0 | 415 | | ws.Cell(row, 1).Value = f.Cedula; |
| | 0 | 416 | | ws.Cell(row, 2).Value = f.Nombre; |
| | 0 | 417 | | ws.Cell(row, 3).Value = f.Grado; |
| | 0 | 418 | | ws.Cell(row, 4).Value = f.Unidad; |
| | 0 | 419 | | ws.Cell(row, 5).Value = f.HaberesGravados; |
| | 0 | 420 | | ws.Cell(row, 6).Value = f.HaberesNoGravados; |
| | 0 | 421 | | ws.Cell(row, 7).Value = f.BeneficiosSociales; |
| | 0 | 422 | | ws.Cell(row, 8).Value = f.DescuentosLegales; |
| | 0 | 423 | | ws.Cell(row, 9).Value = f.LiquidoMDN; |
| | 0 | 424 | | ws.Cell(row, 10).Value = f.Calculo30Porciento; |
| | 0 | 425 | | ws.Cell(row, 11).Value = f.PorcentajeReal; |
| | 0 | 426 | | row++; |
| | | 427 | | } |
| | | 428 | | |
| | 0 | 429 | | row += 2; |
| | 0 | 430 | | ws.Cell(row, 1).Value = "RANGO"; |
| | 0 | 431 | | ws.Cell(row, 2).Value = "CANTIDAD"; |
| | 0 | 432 | | ws.Cell(row, 3).Value = "IMPORTE NOMINAL"; |
| | 0 | 433 | | row++; |
| | | 434 | | |
| | 0 | 435 | | var r = dto.Resumen; |
| | | 436 | | void Escribir(string rango, int cant, decimal imp) |
| | | 437 | | { |
| | 0 | 438 | | ws.Cell(row, 1).Value = rango; |
| | 0 | 439 | | ws.Cell(row, 2).Value = cant; |
| | 0 | 440 | | ws.Cell(row, 3).Value = imp; |
| | 0 | 441 | | row++; |
| | 0 | 442 | | } |
| | | 443 | | |
| | 0 | 444 | | Escribir("> 50%", r.CantidadSuperior50, r.ImporteSuperior50); |
| | 0 | 445 | | Escribir("40% – 50%", r.CantidadEntre40Y50, r.ImporteEntre40Y50); |
| | 0 | 446 | | Escribir("30% – 40%", r.CantidadEntre30Y40, r.ImporteEntre30Y40); |
| | 0 | 447 | | Escribir("< 30% (insuficiente)", r.CantidadInferior30, r.ImporteInferior30); |
| | 0 | 448 | | Escribir("Total deudas no atendidas", 0, r.TotalDeudasNoAtendidas); |
| | | 449 | | |
| | 0 | 450 | | ws.Columns().AdjustToContents(); |
| | 0 | 451 | | } |
| | | 452 | | |
| | | 453 | | private async Task<int> ObtenerVersionMaximaAsync(long periodoId) |
| | | 454 | | { |
| | 0 | 455 | | return await _context.LiquidacionItems |
| | 0 | 456 | | .AsNoTracking() |
| | 0 | 457 | | .Where(i => i.PeriodoId == periodoId) |
| | 0 | 458 | | .MaxAsync(i => (int?)i.Version) ?? 0; |
| | 0 | 459 | | } |
| | | 460 | | } |