public ActionResult ViaticosNuevo([Bind(
Include = "GrupoJerarquicoId, TipoViaje, ZonaDestino, TarifaDiariaViaticosMXP, TarifaDiariaViaticosUSD"
)] Viatico viatico)
{
if (ModelState.IsValid)
{
_db.Viaticos.Add(viatico);
_db.SaveChanges();
return(RedirectToAction("ViaticosListar"));
}
return(View(viatico));
}
/// <summary>
/// Inserta o actualiza un viático
/// </summary>
/// <param name="viatico">Viatico a editar</param>
/// <author>Fernando Ricardo Morán</author>
public void EditViatico(Viatico viatico)
{
try
{
if (viatico.ViaticoId <= 0)
{
CatalogosDA.CreateViatico(viatico);
}
else
{
CatalogosDA.UpdateViatico(viatico);
}
}
catch (Exception exception)
{
EventLogManager.LogErrorEntry(exception);
throw;
}
}
/// <summary> /// Edita un viatico /// </summary> /// <param name="viatico">Información del viático</param> public void EditViatico(Viatico viatico) => new CatalogoLogic().EditViatico(viatico);
public async Task <IActionResult> PostExcelViaticos([FromBody] Viatico requestViaticos)
{
///Stream result;
string empresa = string.Empty;
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = "FORMATO_CAUSACION.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, "Reports", sFileName));
var memory = new MemoryStream();
if (!file.Exists)
{
var response = new ResponseApi();
response.IsSuccess = false;
response.Message = "File no exist: " + file.FullName;
//response.Result=file;
Console.Write("File no exist");
return(Ok(response));
}
else
{
using (
FileStream rstrEmpty = new FileStream(Path.Combine(sWebRootFolder, "Reports", sFileName), FileMode.Open, FileAccess.Read))
{
IWorkbook workbookEmpty;
// rstr.CopyTo(memory);
// memory.Position = 0;
workbookEmpty = new XSSFWorkbook(rstrEmpty);
ISheet excelSheetEmpty = workbookEmpty.GetSheet("Relacion_Causion_Viaticos");
var style1 = workbookEmpty.CreateCellStyle();
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
using (FileStream wstrEmpty = new FileStream(Path.Combine(sWebRootFolder, "Reports", sFileName), FileMode.Create, FileAccess.Write))
{
//Realizar consulta y recorrer por cada elemento
var addNewRoows = requestViaticos.Data.Count + 4;
var rowsExist = excelSheetEmpty.PhysicalNumberOfRows;
if (addNewRoows < excelSheetEmpty.PhysicalNumberOfRows)
{
var f = 0;
for (int i = 0; i <= (rowsExist - 4); i++)
{
//IRow row = excelSheetEmpty.GetRow(11+l);
///row.Height = 20;
var cell = excelSheetEmpty.CreateRow(8 + f);
cell.Height = 30 * 10;
cell.CreateCell(0).SetCellValue("");
cell.GetCell(0).CellStyle = style1;
//cell.GetCell(0).CellType=CellType.Unknown;
//HSSFDateUtil.isCellDateFormatted();
cell.CreateCell(1).SetCellValue("");
cell.GetCell(1).CellStyle = style1;
cell.CreateCell(2).SetCellValue("");
cell.GetCell(2).CellStyle = style1;
cell.CreateCell(3).SetCellValue("");
cell.GetCell(3).CellStyle = style1;
cell.CreateCell(4).SetCellValue("");
cell.GetCell(4).CellStyle = style1;
cell.CreateCell(5).SetCellValue("");
cell.GetCell(5).CellStyle = style1;
cell.CreateCell(6).SetCellValue("");
cell.GetCell(6).CellStyle = style1;
cell.CreateCell(7).SetCellValue("");
cell.GetCell(7).CellStyle = style1;
cell.CreateCell(8).SetCellValue("");
cell.GetCell(8).CellStyle = style1;
cell.CreateCell(9).SetCellValue("");
cell.GetCell(9).CellStyle = style1;
cell.CreateCell(10).SetCellValue("");
cell.GetCell(10).CellStyle = style1;
cell.CreateCell(11).SetCellValue("");
cell.GetCell(11).CellStyle = style1;
cell.CreateCell(12).SetCellValue("");
cell.GetCell(12).CellStyle = style1;
cell.CreateCell(13).SetCellValue("");
cell.GetCell(13).CellStyle = style1;
cell.CreateCell(14).SetCellValue("");
cell.GetCell(14).CellStyle = style1;
f++;
}
}
else
{
var f = 0;
for (int i = 0; i <= addNewRoows; i++)
{
//IRow row = excelSheetEmpty.GetRow(11+l);
///row.Height = 20;
var cell = excelSheetEmpty.CreateRow(8 + f);
cell.Height = 30 * 10;
cell.CreateCell(0).SetCellValue("");
cell.GetCell(0).CellStyle = style1;
//cell.GetCell(0).CellType=CellType.Unknown;
//HSSFDateUtil.isCellDateFormatted();
cell.CreateCell(1).SetCellValue("");
cell.GetCell(1).CellStyle = style1;
cell.CreateCell(2).SetCellValue("");
cell.GetCell(2).CellStyle = style1;
cell.CreateCell(3).SetCellValue("");
cell.GetCell(3).CellStyle = style1;
cell.CreateCell(4).SetCellValue("");
cell.GetCell(4).CellStyle = style1;
cell.CreateCell(5).SetCellValue("");
cell.GetCell(5).CellStyle = style1;
cell.CreateCell(6).SetCellValue("");
cell.GetCell(6).CellStyle = style1;
cell.CreateCell(7).SetCellValue("");
cell.GetCell(7).CellStyle = style1;
cell.CreateCell(8).SetCellValue("");
cell.GetCell(8).CellStyle = style1;
cell.CreateCell(9).SetCellValue("");
cell.GetCell(9).CellStyle = style1;
cell.CreateCell(10).SetCellValue("");
cell.GetCell(10).CellStyle = style1;
cell.CreateCell(11).SetCellValue("");
cell.GetCell(11).CellStyle = style1;
cell.CreateCell(12).SetCellValue("");
cell.GetCell(12).CellStyle = style1;
cell.CreateCell(13).SetCellValue("");
cell.GetCell(13).CellStyle = style1;
cell.CreateCell(14).SetCellValue("");
cell.GetCell(14).CellStyle = style1;
f++;
}
}
workbookEmpty.Write(wstrEmpty);
wstrEmpty.Close();
rstrEmpty.Close();
}
}
using (
FileStream rstr = new FileStream(Path.Combine(sWebRootFolder, "Reports", sFileName), FileMode.Open, FileAccess.Read))
{
IWorkbook workbook;
// rstr.CopyTo(memory);
// memory.Position = 0;
workbook = new XSSFWorkbook(rstr);
ISheet excelSheet = workbook.GetSheet("Relacion_Causion_Viaticos");
using (FileStream wstr = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
//Realizar consulta y recorrer por cada elemento
//llenar el archivo de excel
IRow FechaInicio = excelSheet.GetRow(3);
FechaInicio.GetCell(11).SetCellValue(requestViaticos.FechaInicial);
IRow FechaFin = excelSheet.GetRow(4);
FechaFin.GetCell(11).SetCellValue(requestViaticos.FechaFinal);
IRow Contrato = excelSheet.GetRow(3);
Contrato.CreateCell(2).SetCellValue(requestViaticos.Contrato);
IRow Base = excelSheet.GetRow(4);
Base.CreateCell(2).SetCellValue(requestViaticos.Base);
IRow Relacion = excelSheet.GetRow(3);
Relacion.GetCell(13).SetCellValue(requestViaticos.Relacion);
/*
* var style2 = workbook.CreateCellStyle();
* style2.FillForegroundColor = HSSFColor.Yellow.Index2;
* style2.FillPattern = FillPattern.SolidForeground;*/
//var numero_apoyo=0;
double totalViaticoPermanente = 0;
double totalViaticoOcasional = 0;
double totalSaldoAnticipo = 0;
double totalDescAlim = 0;
double totalDescTran = 0;
//var count=0;
var j = 0;
///for(int i=0; i<=100;i++){
foreach (var item in requestViaticos.Data)
{
totalViaticoPermanente = totalViaticoPermanente + double.Parse(item.ViaticoPermanente, System.Globalization.CultureInfo.InvariantCulture);
totalViaticoOcasional = totalViaticoOcasional + double.Parse(item.ViaticoOcasional, System.Globalization.CultureInfo.InvariantCulture);
totalSaldoAnticipo = totalSaldoAnticipo + double.Parse(item.SaldoAnticipo, System.Globalization.CultureInfo.InvariantCulture);
totalDescAlim = totalDescAlim + double.Parse(item.DescAlim, System.Globalization.CultureInfo.InvariantCulture);
totalDescTran = totalDescTran + double.Parse(item.DescTran, System.Globalization.CultureInfo.InvariantCulture);
// count++;
// numero_apoyo= numero_apoyo+1;
//for(int i=0; i<=dataList.Count;i++){
IRow row = excelSheet.GetRow(8 + j);
row.GetCell(0).SetCellValue(item.Consecutivo);
row.GetCell(1).SetCellValue(item.Cedula);
row.GetCell(2).SetCellValue(string.Format("{0}", item.Nombre));
row.GetCell(3).SetCellValue(item.Cargo);
row.GetCell(4).SetCellValue(item.Sucursal);
row.GetCell(5).SetCellValue(item.FechaInicial);
row.GetCell(6).SetCellValue(item.FechaFinal);
row.GetCell(7).SetCellValue(item.Orden);
row.GetCell(8).SetCellValue(item.TotalDias);
row.GetCell(9).SetCellValue(item.Incidencia);
row.GetCell(10).SetCellValue(string.Format("$ {0:N2}", double.Parse(item.ViaticoPermanente, System.Globalization.CultureInfo.InvariantCulture)));
row.GetCell(11).SetCellValue(string.Format("$ {0:N2}", double.Parse(item.ViaticoOcasional, System.Globalization.CultureInfo.InvariantCulture)));
row.GetCell(12).SetCellValue(string.Format("$ {0:N2}", double.Parse(item.SaldoAnticipo, System.Globalization.CultureInfo.InvariantCulture)));
row.GetCell(13).SetCellValue(string.Format("$ {0:N2}", double.Parse(item.DescAlim, System.Globalization.CultureInfo.InvariantCulture)));
row.GetCell(14).SetCellValue(string.Format("$ {0:N2}", double.Parse(item.DescTran, System.Globalization.CultureInfo.InvariantCulture)));
j++;
// }
}
IRow rowTotal = excelSheet.GetRow(8 + j + 1);
rowTotal.GetCell(10).SetCellValue(string.Format("$ {0:N2}", totalViaticoPermanente));
rowTotal.GetCell(11).SetCellValue(string.Format("$ {0:N2}", totalViaticoOcasional));
rowTotal.GetCell(12).SetCellValue(string.Format("$ {0:N2}", totalSaldoAnticipo));
rowTotal.GetCell(13).SetCellValue(string.Format("$ {0:N2}", totalDescAlim));
rowTotal.GetCell(14).SetCellValue(string.Format("$ {0:N2}", totalDescTran));
// rowTotal.GetCell(14).SetCellValue(totalDescTran);
workbook.Write(wstr);
wstr.Close();
rstr.Close();
}
}
/// archivo=file.OpenRead();
}
//var archivo=file.OpenRead();
using (var stream = new FileStream(Path.Combine(sWebRootFolder, "Reports", sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
