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