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); }