public JsonResult GetPackgestatus() { ETLCenterReportService.ETLCenterReportService bindpackagestatus = new ETLCenterReportService.ETLCenterReportService(); bindpackagestatus.Url = Constants.EtlReport; try { string JsonString = bindpackagestatus.BindPackageStatus(); if (JsonString != string.Empty) { return(Json(JsonString, JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; } finally { Dispose(); } return(Json(CommonLibrary.Constants.JsonError)); }
public JsonResult GetAggregateReport(int PackageGroup, int packageName, int EmployeeName, string startDate, string EndDate, int pageNo, int RowCount) { DateTime?aggregateDate; DateTime?aggrgateEndDate; if (startDate == "") { aggregateDate = null; } else { aggregateDate = Convert.ToDateTime(startDate); } if (EndDate == "") { aggrgateEndDate = null; } else { aggrgateEndDate = Convert.ToDateTime(EndDate); } Session["groupid"] = PackageGroup; Session["pkgid"] = packageName; Session["empid"] = EmployeeName; Session["startdate"] = aggregateDate; Session["enddate"] = aggrgateEndDate; // DateTime StartDateS; // DateTime EndDateS; try { //StartDateS = Convert.ToDateTime(startDate); // EndDateS = Convert.ToDateTime(EndDate); string AggregateDetails = string.Empty; ETLCenterReportService.ETLCenterReportService selectAggregateDetails = new ETLCenterReportService.ETLCenterReportService(); selectAggregateDetails.Url = Constants.EtlReport; AggregateDetails = selectAggregateDetails.GetAggregateReport(pageNo, RowCount, PackageGroup, packageName, EmployeeName, aggregateDate, aggrgateEndDate); DataTable aggregatedt = JsonConvert.DeserializeObject <DataTable>(AggregateDetails); int Rowcount = Convert.ToInt32(aggregatedt.Rows[0]["TotalRowCount"]); Session["AggregateRowCount"] = Convert.ToInt32(Rowcount); if (AggregateDetails != string.Empty) { return(Json(AggregateDetails, JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; return(Json(new { flag = false }, JsonRequestBehavior.AllowGet)); } finally { Dispose(); } return(Json(CommonLibrary.Constants.JsonError, JsonRequestBehavior.AllowGet)); }
public JsonResult GetLogReport(int PackageGroup, int packageName, int packageStatus, string startDate, string EndDate, int pageNo, int RowCount) { DateTime?logDate; DateTime?logEndDate; if (startDate == "") { logDate = null; } else { logDate = Convert.ToDateTime(startDate); } if (EndDate == "") { logEndDate = null; } else { logEndDate = Convert.ToDateTime(EndDate); } Session["loggroupid"] = PackageGroup; Session["logpkgid"] = packageName; Session["logpkgstatus"] = packageStatus; Session["logstartdate"] = logDate; Session["logenddate"] = logEndDate; try { string logDetails = string.Empty; ETLCenterReportService.ETLCenterReportService selectlogDetails = new ETLCenterReportService.ETLCenterReportService(); selectlogDetails.Url = Constants.EtlReport; logDetails = selectlogDetails.GetLogReport(pageNo, RowCount, PackageGroup, packageName, packageStatus, logDate, logEndDate); DataTable logData = JsonConvert.DeserializeObject <DataTable>(logDetails); int Rowcount = Convert.ToInt32(logData.Rows[0]["TotalRowCount"]); Session["logRowCount"] = Convert.ToInt32(Rowcount); if (logDetails != string.Empty) { return(Json(logDetails, JsonRequestBehavior.AllowGet)); } } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; return(Json(new { flag = false }, JsonRequestBehavior.AllowGet)); } finally { Dispose(); } return(Json(CommonLibrary.Constants.JsonError, JsonRequestBehavior.AllowGet)); }
public JsonResult GetpackageReport(int packageName, string startDate, string EndDate, int pageNo, int RowCount) { DateTime?logDate; DateTime?logEndDate; if (startDate == "") { logDate = null; } else { logDate = Convert.ToDateTime(startDate); } if (EndDate == "") { logEndDate = null; } else { logEndDate = Convert.ToDateTime(EndDate); } //Session["errorogpkgid"] = packageName; //Session["errorlogstartdate"] = logDate; //Session["errorlogenddate"] = logEndDate; try { string jsonString = string.Empty; ETLCenterReportService.ETLCenterReportService selecterrorDetails = new ETLCenterReportService.ETLCenterReportService(); selecterrorDetails.Url = Constants.EtlReport; jsonString = selecterrorDetails.PackagestatisticsGraph(packageName, logDate, logEndDate, pageNo, RowCount); //change value of offset as per pagination case if (jsonString != string.Empty) { return(Json(jsonString, JsonRequestBehavior.AllowGet)); } DataTable logData = JsonConvert.DeserializeObject <DataTable>(jsonString); int rowcount = logData.Rows.Count; } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; return(Json(new { flag = false }, JsonRequestBehavior.AllowGet)); } finally { Dispose(); } return(Json(CommonLibrary.Constants.JsonError, JsonRequestBehavior.AllowGet)); }
public ActionResult AggregateExport() { try { GridView aggregateGrid = new GridView(); DataTable aggregateData = new DataTable(); ETLCenterReportService.ETLCenterReportService selectAggregateDetails = new ETLCenterReportService.ETLCenterReportService(); selectAggregateDetails.Url = Constants.EtlReport; string AggregateList = selectAggregateDetails.ExportAggregateReport(Convert.ToInt32(Session["groupid"]), Convert.ToInt32(Session["pkgid"]), Convert.ToInt32(Session["empid"]), Convert.ToDateTime(Session["startdate"]), Convert.ToDateTime(Session["enddate"]), Convert.ToInt32(Session["AggregateRowCount"])); if (AggregateList != "") { aggregateData = JsonConvert.DeserializeObject <DataTable>(AggregateList); aggregateData.Columns.Remove("TotalRowCount"); if (aggregateData.Rows.Count > 0) { aggregateGrid.DataSource = aggregateData; aggregateGrid.DataBind(); } if (aggregateGrid.Rows.Count > 0) { //Response.Clear(); //Response.AddHeader("content-disposition", "attachment;filename=AggregateReport.xls"); //Response.Charset = ""; //Response.ContentType = "application/ms-excel"; //StringWriter Write = new StringWriter(); //HtmlTextWriter HtmlWrite = new HtmlTextWriter(Write); //aggregateGrid.RenderControl(HtmlWrite); //Response.Write(Write.ToString()); //Response.End(); //Response.Clear(); //Response.Clear(); //Response.AddHeader("content-disposition", "attachment;filename=AggregateReport.xls"); //Response.ContentType = "application/ms-excel"; //Response.ContentEncoding = System.Text.Encoding.Unicode; //Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); //System.IO.StringWriter sw = new System.IO.StringWriter(); //System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw); //aggregateGrid.RenderControl(hw); //Response.Write(sw.ToString()); //Response.End(); ExcelPackage excel = new ExcelPackage(); var workSheet = excel.Workbook.Worksheets.Add("AggregateReportSheet"); //workSheet.Cells["A1"].Style.Numberformat.Format = "dd/MM/yyyy h:mm:ss"; workSheet.Cells["A1"].LoadFromDataTable(aggregateData, true); int i = 1; foreach (DataColumn dc in aggregateData.Columns) { if (dc.DataType == typeof(DateTime)) { workSheet.Column(i).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss"; } workSheet.Column(i).AutoFit(); i++; } using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=AggregateReport.xlsx"); excel.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } else { return(View("Aggregate")); //return Json(new { flag = -1 }, JsonRequestBehavior.AllowGet); } } return(View("Aggregate")); } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; // return null; return(View("Aggregate")); // return Json(JsonRequestBehavior.AllowGet); } finally { Session["groupid"] = null; Session["pkgid"] = null; Session["empid"] = null; Session["startdate"] = null; Session["enddate"] = null; Session["AggregateRowCount"] = null; Dispose(); } }
public ActionResult LogExport() { try { if (Convert.ToInt32(Session["loggroupid"]) != 0) { GridView logGrid = new GridView(); DataTable logData = new DataTable(); ETLCenterReportService.ETLCenterReportService selectAggregateDetails = new ETLCenterReportService.ETLCenterReportService(); selectAggregateDetails.Url = Constants.EtlReport; string LogList = selectAggregateDetails.ExportLogReport(Convert.ToInt32(Session["loggroupid"]), Convert.ToInt32(Session["logpkgid"]), Convert.ToInt32(Session["logpkgstatus"]), Convert.ToDateTime(Session["logstartdate"]), Convert.ToDateTime(Session["logenddate"]), Convert.ToInt32(Session["logRowCount"])); if (LogList != "") { logData = JsonConvert.DeserializeObject <DataTable>(LogList); logData.Columns.Remove("TotalRowCount"); //logData.Columns[3].DataType = typeof(DateTime); string colformat = logData.Columns[3].DataType.ToString(); if (logData.Rows.Count > 0) { logGrid.DataSource = logData; logGrid.DataBind(); } if (logGrid.Rows.Count > 0) { //Response.Clear(); //Response.AddHeader("content-disposition", "attachment;filename=LogReport.xls"); //Response.Charset = ""; //Response.ContentType = "application/ms-excel"; //StringWriter Write = new StringWriter(); //HtmlTextWriter HtmlWrite = new HtmlTextWriter(Write); //logGrid.RenderControl(HtmlWrite); //Response.Write(Write.ToString()); //Response.End(); //Response.Clear(); //Response.Clear(); //Response.AddHeader("content-disposition", "attachment;filename=LogReport.xls"); //Response.ContentType = "application/ms-excel"; //Response.ContentEncoding = System.Text.Encoding.Unicode; //Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); //System.IO.StringWriter sw = new System.IO.StringWriter(); //System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw); //logGrid.RenderControl(hw); //Response.Write(sw.ToString()); //Response.End(); ExcelPackage excel = new ExcelPackage(); var workSheet = excel.Workbook.Worksheets.Add("LogReportSheet"); //workSheet.Cells["A1"].Style.Numberformat.Format = "dd/MM/yyyy h:mm:ss"; workSheet.Cells["A1"].LoadFromDataTable(logData, true); int i = 1; foreach (DataColumn dc in logData.Columns) { if (dc.DataType == typeof(DateTime)) { workSheet.Column(i).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss"; } workSheet.Column(i).AutoFit(); i++; } using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=LogReport.xlsx"); excel.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } //FileInfo excelFile1 = new FileInfo(@"F:\test2.xlsx"); //using (ExcelPackage pck = new ExcelPackage(excelFile1)) //{ // ExcelWorksheet ws = pck.Workbook.Worksheets.Add("LogReport1"); // ws.Cells["A1"].LoadFromDataTable(logData, true); // pck.Save(); //} //using (ExcelPackage excel = new ExcelPackage()) //{ // //excel. // excel.Workbook.Worksheets.Add("LogReport"); // // excel.Workbook.Worksheets.Add("Worksheet2"); // // excel.Workbook.Worksheets.Add("Worksheet3"); // var headerRow = new List<string[]>() // { // new string[] { "ID", "First Name", "Last Name", "DOB" } // }; // // Determine the header range (e.g. A1:D1) // string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1"; // // Target a worksheet // var worksheet = excel.Workbook.Worksheets["LogReport"]; // worksheet.Cells[headerRange].LoadFromArrays(headerRow); // FileInfo excelFile = new FileInfo(@"F:\test.xlsx"); // excel.SaveAs(excelFile); //} //MemoryStream MyMemoryStream = new MemoryStream(); //using (XLWorkbook wb = new XLWorkbook()) //{ // /// wb.Worksheets. //wb.Worksheets.Add(); // wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // wb.Style.Font.Bold = true; // Response.Clear(); // Response.Buffer = true; // Response.Charset = ""; // Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // Response.AddHeader("content-disposition", "attachment;filename= LogReport.xlsx"); // wb.SaveAs(MyMemoryStream); // MyMemoryStream.WriteTo(Response.OutputStream); // Response.Flush(); // Response.End(); // using ( MyMemoryStream = new MemoryStream()) // { // wb.SaveAs(MyMemoryStream); // MyMemoryStream.WriteTo(Response.OutputStream); // Response.Flush(); // Response.End(); // } //} } else { return(View("Log")); //return Json(new { flag = -1 }, JsonRequestBehavior.AllowGet); } } return(View("Log")); } else { return(View("Log")); } } catch (Exception ex) { CommonFunctions commonFun = new CommonFunctions(); commonFun.ExceptionLog(ControllerContext.HttpContext, ex.Message, ex.TargetSite.Name, Convert.ToString(ControllerContext.RouteData.Values["action"]), Convert.ToString(ControllerContext.RouteData.Values["controller"])); commonFun = null; // return null; return(View("Log")); } finally { Session["loggroupid"] = null; Session["logpkgid"] = null; Session["logpkgstatus"] = null; Session["logstartdate"] = null; Session["logenddate"] = null; Session["logRowCount"] = null; Dispose(); } }