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