private System.Web.Mvc.FileResult ReportPerSalesOrderExtract(ReportInventoryPerItemSearchModel searchModel)
{
DataTable dt = new DataTable();
string userName = Session[SessionVariables.UserDetails].GetUserNameFromSession();
DateTime date = (DateTime)searchModel.DateGenerated;
var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
var monthName = firstDayOfMonth.ToString("MMMM");
dt = _reportCombinationService.GetAllSalesOrder(firstDayOfMonth, lastDayOfMonth, searchModel.CategoryID, searchModel.BranchID);
int rowId = 0;
int colId = 0;
var dir = Server.MapPath(string.Format("~/{0}", Constants.ProductExcelTemplateDir));
var fileNameTemplate = string.Format("{0}{1}", Constants.ReportTemplate, ".xlsx");
var path = System.IO.Path.Combine(dir, fileNameTemplate);
var fileNameGenerated = string.Format("{0}{1}", Constants.ReportPerSalesOrder, ".xlsx");
var contentType = "application/vnd.ms-excel";
var templateFile = new FileInfo(path);
//var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(path);
;
var package = new ExcelPackage(templateFile);
var workSheet = package.Workbook.Worksheets[1];
workSheet.Cells["A1"].Value = string.Format("MONTH OF {0} {1}", monthName, date.Year);
workSheet.Cells["A2"].Value = dt.Rows[0][Constants.BranchName].ToString();
workSheet.Cells["A3"].Value = string.Format("PREPARED BY: {0}", userName);
rowId = 6;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
colId = 4;
workSheet.Cells["B" + rowId.ToString()].Value = string.Format("{0} - {1} - {2}", dt.Rows[i][Constants.ProductCode].ToString(), dt.Rows[i][Constants.ProductName].ToString(), dt.Rows[i][Constants.ProductExtension].ToString());
workSheet.Cells["C" + rowId.ToString()].Value = dt.Rows[i][Constants.OldQuantity].ToString();
for (int c = 13; c <= 43; c++)
{
workSheet.Cells[rowId, colId].Value = dt.Rows[i][c].ToString();
colId++;
}
workSheet.Cells["AK" + rowId.ToString()].Value = dt.Rows[i][Constants.NewQuantity].ToString();
rowId++;
}
var memoryStream = new MemoryStream();
//package.Save();
package.SaveAs(memoryStream);
memoryStream.Position = 0;
return(File(memoryStream, contentType, fileNameGenerated));
}
public virtual ActionResult SalesReport(ReportInventoryPerItemSearchModel model)
{
//if (model.BranchID.IsNull() || model.CategoryID.IsNull() || model.DateGenerated.IsNull())
//{
// model = new ReportInventoryPerItemSearchModel();
//}
if (model.IsNull())
{
model = new ReportInventoryPerItemSearchModel();
}
return(View(model));
}
public virtual ActionResult ExtractReportSalesReportToExcel(ReportInventoryPerItemSearchModel searchModel)
{
int? branchId = Session[SessionVariables.UserDetails].GetBranchIdFromSession();
int? userTypeId = Session[SessionVariables.UserDetails].GetUserTypeIdFromSession();
string userName = Session[SessionVariables.UserDetails].GetUserNameFromSession();
DataTable dt = new DataTable();
if (userTypeId == Constants.UserTypeAdminId)
{
if (ModelState.IsValid)
{
return(ReportPerSalesReportExtract(searchModel));
}
else
{
if (searchModel.CategoryID.IsNull())
{
ModelState.AddModelError("CategoryID", string.Format(Messages.FieldRequired, "Category"));
}
if (userTypeId == Constants.UserTypeAdminId)
{
if (searchModel.BranchID.IsNull())
{
ModelState.AddModelError("BranchID", string.Format(Messages.FieldRequired, "Branch"));
}
}
else
{
searchModel.BranchID = branchId;
}
return(View(IOBALANCEMVC.ReportManagement.InventorySales.Views.SalesReport, searchModel));
//return RedirectToAction(IOBALANCEMVC.ReportManagement.InventorySales.Index(searchModel));
}
}
else
{
if (searchModel.CategoryID.IsNull())
{
ModelState.AddModelError("CategoryID", string.Format(Messages.FieldRequired, "Category"));
return(View(IOBALANCEMVC.ReportManagement.InventorySales.Views.SalesReport, searchModel));
}
else
{
return(ReportPerSalesReportExtract(searchModel));
}
}
}
private System.Web.Mvc.FileResult ReportPerSalesReportExtract(ReportInventoryPerItemSearchModel searchModel)
{
DataTable dt = new DataTable();
string userName = Session[SessionVariables.UserDetails].GetUserNameFromSession();
DateTime date = (DateTime)searchModel.DateGenerated;
var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
var monthName = firstDayOfMonth.ToString("MMMM");
dt = _reportCombinationService.GetAllSalesReport(firstDayOfMonth, lastDayOfMonth, searchModel.CategoryID, searchModel.BranchID);
int rowId = 0;
int colId = 0;
var fileNameGenerated = string.Format("{0}{1}", Constants.ReportSalesReport, ".xlsx");
var contentType = "application/vnd.ms-excel";
//var templateFile = new FileInfo(path);
//var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(path);
var package = new ExcelPackage();
var workSheet = package.Workbook.Worksheets.Add(Constants.ReportSalesReport);
workSheet.Cells["A1"].Value = "Date Value";
workSheet.Cells["B1"].Value = "Total Sales Amount";
rowId = 2;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
workSheet.Cells["A" + rowId].Value = dt.Rows[i][Constants.DateValue].ToString();
workSheet.Cells["B" + rowId].Value = dt.Rows[i][Constants.TotalAmount].ToString();
rowId++;
}
var memoryStream = new MemoryStream();
//package.Save();
package.SaveAs(memoryStream);
memoryStream.Position = 0;
return(File(memoryStream, contentType, fileNameGenerated));
}
