public ReportInfo GetReportData(BillingExportFilter filter) { IDataReader dataReader = null; try { ReportInfo repInfo = new ReportInfo(); List <string> seriesNameList = new List <string>(); List <string> xValueList = new List <string>(); List <decimal> yValueList = new List <decimal>(); //Get all the xaxis date values for the selected date type DateTime fromDateValue; DateTime.TryParseExact(filter.FromDate.ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out fromDateValue); DateTime toDateValue; DateTime.TryParseExact(filter.ToDate.ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out toDateValue); List <string> dateList = new List <string>(); while (fromDateValue <= toDateValue) { string formattedDate = fromDateValue.ToString("yyyyMMdd", CultureInfo.InvariantCulture); dateList.Add(formattedDate); string formattedDateString = fromDateValue.ToString("yyyy-MMM-dd", CultureInfo.InvariantCulture); xValueList.Add(formattedDateString); fromDateValue = fromDateValue.AddDays(1); } Database db = DatabaseFactory.CreateDatabase("DbConnection"); if (filter.IsDrillDown) { dataReader = BillingDAL.GetBillingItemPrice(db, filter.FromDate, filter.ToDate); } else { dataReader = BillingDAL.GetTotalBillingPrice(db, filter.FromDate, filter.ToDate); } List <ReportData> reportDataList = new List <ReportData>(); ReportData reportData = null; while (dataReader.Read()) { reportData = new ReportData(); reportData.XValue = Common.GetInt32(dataReader, "FXVALUE").ToString(); reportData.YValue = Common.GetDecimal(dataReader, "FYVALUE"); reportData.SeriesName = Common.GetString(dataReader, "FSERIESNAME"); reportDataList.Add(reportData); if (!seriesNameList.Contains(reportData.SeriesName)) { seriesNameList.Add(reportData.SeriesName); } } dataReader.Close(); foreach (string seriesName in seriesNameList) { yValueList = new List <decimal>(); foreach (string dateStr in dateList) { ReportData selReportData = reportDataList.FirstOrDefault(r => r.XValue == dateStr && r.SeriesName == seriesName); if (selReportData != null) { yValueList.Add(selReportData.YValue); } else { yValueList.Add(0); } } repInfo.YValues.Add(yValueList); } repInfo.XValues = xValueList; repInfo.SeriesNames = seriesNameList; return(repInfo); } catch (Exception ex) { Common.LogException(ex); throw new WebFaultException <string>(ex.Message, HttpStatusCode.InternalServerError); } finally { if (dataReader != null && !dataReader.IsClosed) { dataReader.Close(); } } }
public string DownloadBillingInfo(BillingExportFilter filter) { IDataReader dataReader = null; try { ExcelPackage excelPackage = new ExcelPackage(); ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add("Report Data"); int rowIndex = 1; int colIndex = 1; workSheet.Cells[rowIndex, colIndex].Value = "Billing ID"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Item Name"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Item Price"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Quantity"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Amount"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "User"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Created Time"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Comments"; rowIndex = 2; bool recordExists = false; DateTime createdDateTime; Database db = DatabaseFactory.CreateDatabase("DbConnection"); dataReader = BillingDAL.GetBillingInfoForExport(db, filter.FromDate, filter.ToDate); while (dataReader.Read()) { colIndex = 1; recordExists = true; workSheet.Cells[rowIndex, colIndex].Value = Common.GetInt64(dataReader, "FBILLINGID"); colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FITEMNAME"); colIndex++; decimal itemPrice = Common.GetDecimal(dataReader, "FITEMPRICE"); int quantity = Common.GetInt32(dataReader, "FQTY"); workSheet.Cells[rowIndex, colIndex].Value = itemPrice; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = quantity; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = itemPrice * quantity; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FUSERNAME"); colIndex++; DateTime.TryParseExact(Common.GetInt32(dataReader, "FCREATEDDATE").ToString() + Common.GetInt32(dataReader, "FCREATEDTIME").ToString().PadLeft(6, '0'), "yyyyMMddHHmmss", CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateTime); workSheet.Cells[rowIndex, colIndex].Value = createdDateTime.ToString("yyyy-MMM-dd hh:mm:ss tt"); colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FCOMMENT"); rowIndex++; } dataReader.Close(); if (recordExists) { workSheet.Cells.AutoFitColumns(); string fileName = "BillingData_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string excelFolderPath = ConfigurationManager.AppSettings["LogFileLocation"].ToString(); FileInfo excelFile = new FileInfo(excelFolderPath + "/" + fileName); excelPackage.SaveAs(excelFile); return(fileName); } return(string.Empty); } catch (Exception ex) { Common.LogException(ex); throw new WebFaultException <string>(ex.Message, HttpStatusCode.InternalServerError); } finally { if (dataReader != null && !dataReader.IsClosed) { dataReader.Close(); } } }