public DataTable GetAllServiceItemsSummary(FilterReport filter)
{
DataTable dt = new DataTable();
try
{
SqlConnection cnn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand("sp_report_dinamic_summary", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
filter.strNumberDocument = string.IsNullOrEmpty(filter.strNumberDocument) ? null : filter.strNumberDocument;
filter.Id = string.IsNullOrEmpty(filter.Id) ? null : filter.Id;
cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = filter.startDate;
cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = filter.endDate;
cnn.Open();
dt.Load(cmd.ExecuteReader());
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public JsonResult ActualReport(FilterReport f)
{
var memoryKey = Guid.NewGuid().ToString();
_memoryCache.Add(memoryKey, f, DateTime.Now.AddMinutes(30));
//ReportManager rm = new ReportManager();
//List<ActualReportView> list = rm.GetActualReport();
//ViewBag.Export = true;
//Exporter exporter = new Exporter();
//Exporter.ToExcel(Response, "ActualReport" + DateTime.Now.ToString("dd.MM.yyyy"));
return(Json(new { Key = memoryKey }));
}
public void ProcessRequest(HttpContext context)
{
DataTable dt = null;
ExcelBuilder exbuilder;
string rutaPlantilla = "";
try
{
string tipoReporte = context.GetString("rpt") == "null" ? null : context.GetString("rpt");
byte[] buffer = null;
FilterReport filter = new FilterReport();
DateTime start;
DateTime end;
string subtitulo = string.Empty;
var days = new List <int>();
switch (tipoReporte)
{
case "reporte_por_area":
filter.Id = context.GetString("Id") == "-1" ? null : context.GetString("Id");
filter.strNumberDocument = context.GetString("strNumberDocument") == "null" ? null : context.GetString("strNumberDocument");
filter.startDate = context.GetString("startDate") == "null" ? null : context.GetString("startDate");
filter.endDate = context.GetString("endDate") == "null" ? null : context.GetString("endDate");
dt = new ReportDAO().GetAllServiceItems(filter);
start = Convert.ToDateTime(filter.startDate);
end = Convert.ToDateTime(filter.endDate);
start = Convert.ToDateTime(filter.startDate);
end = Convert.ToDateTime(filter.endDate);
for (var d = start; d <= end; d = d.AddDays(1))
{
days.Add(d.Day);
}
;
rutaPlantilla = context.Server.MapPath(
@"~/TemplateReport/plantilla_reporte_dinamico.xlsx");
exbuilder = new ExcelBuilder();
exbuilder.Plantilla = rutaPlantilla;
exbuilder.SetHojaContenido("Reporte", 7, 2);
subtitulo = string.Format("Del {0} de {1} al {2} de {3} del {4}", start.Day, start.ToString("MMMM"), end.Day, end.ToString("MMMM"), end.Year);
exbuilder.SetSubTitulo(subtitulo, 2, 35);
var datosAdicionales1 = Days(days);
exbuilder.SetDatosAdicionales(datosAdicionales1, 5, 4);
if (filter.Id != null)
{
var title = new ReportDAO().GetNameAreaById(Convert.ToInt32(filter.Id));
exbuilder.SetTitulo(title, 2, 20);
}
else
{
exbuilder.SetTitulo("Todas las Áreas", 2, 20);
}
buffer = exbuilder.Crear(dt);
break;
case "reporte_resumen":
filter.Id = null;
filter.strNumberDocument = null;
filter.startDate = context.GetString("startDate") == "null" ? null : context.GetString("startDate");
filter.endDate = context.GetString("endDate") == "null" ? null : context.GetString("endDate");
dt = new ReportDAO().GetAllServiceItemsSummary(filter);
start = Convert.ToDateTime(filter.startDate);
end = Convert.ToDateTime(filter.endDate);
for (var d = start; d <= end; d = d.AddDays(1))
{
days.Add(d.Day);
}
;
rutaPlantilla = context.Server.MapPath(
@"~/TemplateReport/plantilla_reporte_resumen.xlsx");
exbuilder = new ExcelBuilder();
exbuilder.Plantilla = rutaPlantilla;
exbuilder.SetHojaContenido("Resumen", 7, 2);
subtitulo = string.Format("Del {0} de {1} al {2} de {3} del {4}", start.Day, start.ToString("MMMM"), end.Day, end.ToString("MMMM"), end.Year);
exbuilder.SetSubTitulo(subtitulo, 2, 34);
var datosAdicionales2 = Days(days);
exbuilder.SetDatosAdicionales(datosAdicionales2, 5, 3);
buffer = exbuilder.Crear(dt);
break;
}
if (buffer == null)
{
context.Response.ContentType = "text/plain";
context.Response.Write("No se ha implementado el reporte solicitado");
}
else
{
string filename = ObtenerNombreArchivo(tipoReporte);
context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("content-disposition", "attachment; filename=\"" + filename + "\"");
context.Response.BinaryWrite(buffer);
context.Response.Flush();
}
}
catch (Exception ex)
{
//Logging.LogException(ex);
context.Response.ContentType = "text/plain";
context.Response.Write("No fue posible generar el archivo");
}
}
private void btnGenerar_Click(object sender, EventArgs e)
{
int id = cmdArea.SelectedIndex + 1;
string dni = txtDNI.Text;
DateTime start = Convert.ToDateTime(txtDesde.Text);
DateTime end = Convert.ToDateTime(txtHasta.Text);
DataTable dt = null;
ExcelBuilder exbuilder;
string rutaPlantilla = "";
try
{
string tipoReporte = "reporte_por_area";
byte[] buffer = null;
switch (tipoReporte)
{
case "reporte_por_area":
FilterReport filter = new FilterReport();
filter.Id = id;
filter.strNumberDocument = dni;
filter.startDate = start;
filter.endDate = end;
dt = new ReportDAO().GetAllServiceItems(filter);
string RutaApp = Application.StartupPath;
string RutaPlantilla = @"\PlantillaReporte\plantilla_reporte_dinamico.xls";
rutaPlantilla = Path.Combine(RutaApp, RutaPlantilla);
exbuilder = new ExcelBuilder();
exbuilder.Plantilla = rutaPlantilla;
exbuilder.SetHojaContenido("ENDOSO", 9, 1);
exbuilder.SetDatosAdicionales(datosAdicionalesEndoso, 5, 4);
exbuilder.SetTitulo("REPORTE DE ENDODOS DE POLIZA", 2, 3);
buffer = exbuilder.Crear(dt);
break;
}
if (buffer == null)
{
Response.ContentType = "text/plain";
Response.Write("No se ha implementado el reporte solicitado");
}
else
{
string filename = ObtenerNombreArchivo(tipoReporte);
Response.ContentType = "application/octet-stream";
Response.AddHeader("content-disposition", "attachment; filename=\"" + filename + "\"");
Response.BinaryWrite(buffer);
Response.Flush();
}
}
catch (Exception ex)
{
//Logging.LogException(ex);
context.Response.ContentType = "text/plain";
context.Response.Write("No fue posible generar el archivo");
}
}
public async Task <HttpResponseMessage> GetAllReport(HttpRequestMessage request, string userID, [FromBody] FilterReport filter, int page, int pageSize)
{
Func <HttpResponseMessage> func = () =>
{
if (string.IsNullOrEmpty(userID))
{
return(request.CreateErrorResponse(HttpStatusCode.BadRequest, nameof(userID) + MessageSystem.NoValues));
}
var totalRow = 0;
var data = _fingerTimeSheetService.GetAllReportPaging(userID, filter, page, pageSize, ref totalRow);
//var data = model.Skip((page - 1) * pageSize).Take(pageSize);
var paginationSet = new PaginationSet <ReportModel>()
{
Items = data,
PageIndex = page,
TotalRows = totalRow,
PageSize = pageSize
};
return(request.CreateResponse(HttpStatusCode.OK, paginationSet));
};
return(await CreateHttpResponse(request, func));
}
public async Task <HttpResponseMessage> ReportExcelEx(HttpRequestMessage request, string userID, [FromBody] FilterReport filter)
{
if (!string.IsNullOrEmpty(filter.StartDate) && !string.IsNullOrEmpty(filter.EndDate))
{
DateTime startDate = DateTime.ParseExact(filter.StartDate, CommonConstants.FormatDate_MMDDYYY, CultureInfo.InvariantCulture).Date;
DateTime endDate = DateTime.ParseExact(filter.EndDate, CommonConstants.FormatDate_MMDDYYY, CultureInfo.InvariantCulture).Date;
//string fileName = string.Concat(CommonConstants.FunctionReport + DateTime.Now.ToString(CommonConstants.dateExport) + CommonConstants.fileExport);
string fileName = @"ReportEx.xlsx";
var folderReport = ConfigHelper.GetByKey(CommonConstants.reportFolder);
string fileTemplate = folderReport + CommonConstants.Link + fileName;
string filePath = HttpContext.Current.Server.MapPath(folderReport);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
if (File.Exists(HttpContext.Current.Server.MapPath(fileTemplate)))
{
File.Delete(HttpContext.Current.Server.MapPath(fileTemplate));
}
string fullPath = Path.Combine(filePath, fileName);
try
{
await _fingerTimeSheetService.GetAllReportEx(userID, startDate, endDate);
return(request.CreateResponse(HttpStatusCode.OK, fileTemplate));
}
catch (Exception ex)
{
return(request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message));
}
}
else
{
return(request.CreateResponse(HttpStatusCode.BadRequest, "Please Choose Start Date and End Date"));
}
}
public List <ActualReportView> GetActualReport(FilterReport fr)
{
StringBuilder sb = new StringBuilder();
sb.Append($@"WHERE AD.[Month]={fr.month}");
if (fr.spend != 0)
{
sb.Append($"AND Spend_1={fr.spend}");
}
List <ActualReportView> ListReport = null;
var sql = $@"SELECT
AD.Id,
AD.[Year],
AD.[Month],
AD.Client_Id,
AD.Brand_Id,
AD.Spend_Id,
GroupClients.Name As GroupClient,
Clients.Name As Client,
Product1.Name As Product_1,
Product2.Name As Product_2,
Product3.Name As Product_3,
Spend1.Name As Spend_1,
Spend2.Name As Spend_2,
Spend3.Name As Spend_3,
AD.EUser,
AD.EDo,
AD.EDocumentHeaderText,
AD.EPurchaseOrderText,
AD.EOperationName,
AD.EMaterial,
AD.EMaterialDescription,
AD.EPostingDate,
AD.CreatedOn,
AD.ETotalQuantity,
AD.EDocumentDate,
AD.EVendorName,
AD.ETransactionCurrency,
AD.ETransactionCurrencySum,
AD.EIOName,
AD.EIOCode,
AD.EGLName,
AD.EGLCode
FROM
dbo.ActualData AS AD
LEFT JOIN dbo.Classifier AS Clients
ON Clients.Id = AD.Client_Id
LEFT JOIN dbo.Classifier AS GroupClients
ON Clients.Parent_Id = GroupClients.Id
LEFT JOIN dbo.Classifier AS Product3
ON Product3.Id = AD.Brand_Id
LEFT JOIN dbo.Classifier AS Product2
ON Product2.Id = Product3.Parent_Id
LEFT JOIN dbo.Classifier AS Product1
ON Product1.Id = Product2.Parent_Id
LEFT JOIN dbo.Classifier AS Spend3
ON Spend3.Id = AD.Spend_Id
LEFT JOIN dbo.Classifier AS Spend2
ON Spend3.Parent_Id = Spend2.Id
LEFT JOIN dbo.Classifier As Spend1
ON Spend1.Id = Spend2.Parent_Id";
var ddm = new DirectDataManager(sql);
ListReport = ddm.ToList <ActualReportView>().ToList();
for (int i = 0; i < ListReport.Count; i++)
{
ListReport[i].Period = GetDatePerion(ListReport[i].Year, ListReport[i].Month);
}
return(ListReport);
}
