public List <PurchaseOrderModel> GetPurchaseSearchData(PurchaseOrderSearchModel search, int?page)
{
var body = JsonConvert.SerializeObject(search);
var result = ServerResponse.Invoke <List <PurchaseOrderModel> >("api/PurchaseOrder/getPurchaseSearchData", body, "POST");
return(result);
}
public ActionResult _Index(PurchaseOrderSearchModel search, int?page)
{
int TotalCount = 0;
var pageSize = 10;
var pageNumber = page ?? 1;
int CurrentPage = pageNumber;
var endPage = CurrentPage + 4;
int PagesToShow = 10;
ViewBag.PageSize = pageSize;
var PurchaseOrderList = Services.PurchaseOrderService.GetPurchaseOrderSearchData1(search, page, out TotalCount);
foreach (var item in PurchaseOrderList)
{
item.OrderDate = item.OrderDate.Replace("T00:00:00", "");
}
ViewBag.TotalCount = TotalCount;
var result = Helper.CommonFunction.GetPages(TotalCount, pageSize, CurrentPage, PagesToShow);
int totalPages = (TotalCount / pageSize) + (TotalCount % pageSize == 0 ? 0 : 1);
ViewBag.result = result;
ViewBag.totalPages = totalPages;
ViewBag.CurrentPage = CurrentPage;
var pageCount = result.Count();
ViewBag.pageCount = pageCount;
ViewBag.endPage = endPage;
return(View(PurchaseOrderList));
}
public ActionResult _Index1(PurchaseOrderSearchModel search, int?page)
{
int TotalCount = 0;
var pageSize = 10;
var pageNumber = page ?? 1;
int CurrentPage = pageNumber;
var endPage = CurrentPage + 4;
int PagesToShow = 10;
ViewBag.PageSize = pageSize;
var AreaModelList = Services.PurchaseOrderService.GetPurchaseOrderSearchData1(search, page, out TotalCount);
foreach (var date in AreaModelList)
{
var str = date.OrderDate;
if (!string.IsNullOrEmpty(str))
{
str = str.Substring(0, str.Length - 9);
date.OrderDate = str;
}
}
ViewBag.TotalCount = TotalCount;
var result = Helper.CommonFunction.GetPages(TotalCount, pageSize, CurrentPage, PagesToShow);
int totalPages = (TotalCount / pageSize) + (TotalCount % pageSize == 0 ? 0 : 1);
ViewBag.result = result;
ViewBag.totalPages = totalPages;
ViewBag.CurrentPage = CurrentPage;
var pageCount = result.Count();
ViewBag.pageCount = pageCount;
ViewBag.endPage = endPage;
return(View(AreaModelList));
}
public ServiceResult <List <PurchaseOrder> > GetSearchPurchaseOrder(PurchaseOrderSearchModel search)
{
ServiceResult <List <PurchaseOrder> > model = new ServiceResult <List <PurchaseOrder> >();
var list = Entities.PurchaseOrders.Where(x => x.IsActive == true).OrderByDescending(x => x.ID).Include(x => x.Supplier).Include(x => x.Buyer).Include(x => x.PurchaseOrderStatu);
if (search != null)
{
if (search.IsActive == false)
{
list = Entities.PurchaseOrders.Where(x => x.IsActive == false).OrderByDescending(x => x.ID);
}
if (!string.IsNullOrEmpty(search.OrderNumber))
{
list = list.Where(x => x.OrderNumber.ToLower().Contains(search.OrderNumber.ToLower()));
}
if (!string.IsNullOrEmpty(search.SupplierName))
{
list = list.Where(x => x.Supplier.Name.ToLower().Contains(search.SupplierName.ToLower()));
}
if (!string.IsNullOrEmpty(search.BuyerName))
{
list = list.Where(x => x.Buyer.Name.ToLower().Contains(search.BuyerName.ToLower()));
}
}
var pageSize = 10;
var source = list.ToList();
int count = source.Count();
var items = source.OrderByDescending(m => m.ID).Skip(((search.Page ?? 1) - 1) * pageSize)
.Take(pageSize).ToList();
model.data = items.Select(x => new PurchaseOrder
{
ID = x.ID,
OrderNumber = x.OrderNumber,
ClientInvoiceNumber = x.ClientInvoiceNumber,
OrderDate = x.OrderDate,
ExpectedDeliveryDate = x.ExpectedDeliveryDate,
FirstDeliveryDate = x.FirstDeliveryDate,
OrderCompletionDate = x.OrderCompletionDate,
Quantity = x.Quantity,
Amount = x.Amount,
VatAmount = x.VatAmount,
PurchaseOrderStatusId = x.PurchaseOrderStatusId,
BuyerId = x.BuyerId,
SupplierId = x.SupplierId,
Buyer = Entities.Buyers.Where(m => m.IsActive == true && m.Id == x.BuyerId).FirstOrDefault(),
Supplier = Entities.Suppliers.Where(m => m.IsActive == true && m.Id == x.SupplierId).FirstOrDefault()
// BuyerName=Entities.Buyers.Where(m=>m.Id==BuyerId)
}).ToList();
model.TotalCount = count;
return(model);
}
public int BuildPaganationPurchaseOrderList(PurchaseOrderSearchModel model)
{
int NOP = 0;
ShomaRMEntities db = new ShomaRMEntities();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetPurchaseOrderPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
if (dtTable.Rows.Count > 0)
{
NOP = int.Parse(dtTable.Rows[0]["NumberOfPages"].ToString());
}
db.Dispose();
return(NOP);
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public List <PurchaseOrderModel> GetSearchData1(PurchaseOrderSearchModel search, int?page, out int TotalCount)
{
int pageNumber = (page ?? 1);
var body = JsonConvert.SerializeObject(search);
var result = ServerResponse.Invoke <ServiceResult <List <PurchaseOrderModel> > >("api/PurchaseOrder/getPurchaseSearchDataForReport", body, "Post");
TotalCount = result.TotalCount;
if (result.data != null)
{
var model = result.data.ToList();
return(model);
}
else
{
}
return(result.data.ToList());
}
private System.Web.Mvc.FileResult ReportPurchaseOrderExtract(PurchaseOrderSearchModel searchModel)
{
int rowId = 0;
int colId = 0;
var list = GetPurchaseOrderReport(searchModel).ToList();
var dir = Server.MapPath(string.Format("~/{0}", Constants.ProductExcelTemplateDir));
var fileNameTemplate = string.Format("{0}{1}", Constants.ReportPOTemplate, ".xlsx");
var path = System.IO.Path.Combine(dir, fileNameTemplate);
var fileNameGenerated = string.Format("{0}{1}", Constants.ReportPerPurchaseOrder, ".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];
rowId = 2;
foreach (var detail in list)
{
workSheet.Cells["A" + rowId.ToString()].Value = detail.product.ProductInfoDisplay;
workSheet.Cells["B" + rowId.ToString()].Value = detail.SupplierInfoDisplay;
workSheet.Cells["C" + rowId.ToString()].Value = detail.Quantity;
workSheet.Cells["D" + rowId.ToString()].Value = detail.DateCreatedWithTimeFormat;
rowId++;
}
var memoryStream = new MemoryStream();
//package.Save();
package.SaveAs(memoryStream);
memoryStream.Position = 0;
return(File(memoryStream, contentType, fileNameGenerated));
}
private IQueryable <ReportPurchaseOrderDto> GetPurchaseOrderReport(PurchaseOrderSearchModel searchModel)
{
IQueryable <ReportPurchaseOrderDto> result = null;
if ((searchModel.DateFrom.IsNull() && searchModel.DateTo.IsNull() && searchModel.ProductId == 0 && searchModel.SupplierId == 0))
{
result = _orderService.GetAllPurchaseOrderReport().Where(s => DbFunctions.TruncateTime(s.DateCreated) == DbFunctions.TruncateTime(DateTime.Now));
}
else
{
var predicate = PredicateBuilder.True <ReportPurchaseOrderDto>();
if (searchModel.ProductId != 0)
{
predicate = predicate.And(p => p.ProductId == searchModel.ProductId);
}
if (searchModel.SupplierId != 0)
{
predicate = predicate.And(p => p.SupplierId == searchModel.SupplierId);
}
if (!searchModel.DateFrom.IsNull() && searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) >= DbFunctions.TruncateTime(searchModel.DateFrom));
}
else if (searchModel.DateFrom.IsNull() && !searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) <= DbFunctions.TruncateTime(searchModel.DateTo));
}
else if (!searchModel.DateFrom.IsNull() && !searchModel.DateTo.IsNull())
{
predicate = predicate.And(a => DbFunctions.TruncateTime(a.DateCreated) >= DbFunctions.TruncateTime(searchModel.DateFrom) && DbFunctions.TruncateTime(a.DateCreated) <= DbFunctions.TruncateTime(searchModel.DateTo));
}
result = _orderService.GetAllPurchaseOrderReport().AsExpandable().Where(predicate);
}
return(result);
}
public ServiceResult <List <PurchaseOrder> > GetSearchData(PurchaseOrderSearchModel search)
{
var pageSize = 10;
var CurrentPage = 1;
ServiceResult <List <PurchaseOrder> > model = new ServiceResult <List <PurchaseOrder> >();
var source = Entities.PurchaseOrders.Where(x => x.IsActive == true).Include(x => x.Buyer).Include(x => x.PurchaseOrderStatu).Include(x => x.Supplier);
int count = source.Count();
var items = source.OrderByDescending(m => m.ID).Skip(((search.Page ?? 1) - 1) * pageSize)
.Take(pageSize).ToList();
model.data = items.Select(x => new PurchaseOrder
{
ID = x.ID,
OrderNumber = x.OrderNumber,
SupplierId = x.SupplierId,
BuyerId = x.BuyerId
}).ToList().RemoveReferences();
model.TotalCount = count;
return(model);;
}
public ServiceResult <List <PurchaseOrder> > GetSearchPurchaseOrder1(PurchaseOrderSearchModel search)
{
ServiceResult <List <PurchaseOrder> > model = new ServiceResult <List <PurchaseOrder> >();
var list = Entities.PurchaseOrders.Where(x => x.IsActive == true).OrderByDescending(x => x.ID).Include(x => x.Supplier).Include(x => x.Buyer).Include(x => x.PurchaseOrderStatu);
if (search != null)
{
if (search.IsActive == false)
{
list = Entities.PurchaseOrders.Where(x => x.IsActive == false).OrderByDescending(x => x.ID);
}
if (search.OrderNumber1 != null)
{
list = list.Where(x => x.OrderNumber == search.OrderNumber1);
}
if (search.OrderNumber != null)
{
list = list.Where(x => x.OrderNumber == search.OrderNumber);
}
if (search.PurchaseOrderStatusId > 0)
{
list = list.Where(x => x.PurchaseOrderStatusId == search.PurchaseOrderStatusId);
}
if (search.FromDate != null && search.ToDateS != null)
{
list = Entities.PurchaseOrders.Where(entry => entry.OrderDate >= search.FromDate &&
entry.OrderDate <= search.ToDateS);
}
}
var pageSize = 10;
var source = list.ToList();
int count = source.Count();
var items = source.OrderByDescending(m => m.ID).Skip(((search.Page ?? 1) - 1) * pageSize)
.Take(pageSize).ToList().RemoveReferences();
//model.data = items.Select(x => new PurchaseOrder
//{
// ID = x.ID,
// OrderNumber = x.OrderNumber,
// ClientInvoiceNumber = x.ClientInvoiceNumber,
// OrderDate = x.OrderDate,
// ExpectedDeliveryDate = x.ExpectedDeliveryDate,
// FirstDeliveryDate = x.FirstDeliveryDate,
// OrderCompletionDate = x.OrderCompletionDate,
// Quantity = x.Quantity,
// Amount = x.Amount,
// VatAmount = x.VatAmount,
// PurchaseOrderStatusId = x.PurchaseOrderStatusId,
// BuyerId = x.BuyerId,
// SupplierId = x.SupplierId,
// Buyer = Entities.Buyers.Where(m => m.IsActive == true && m.Id == x.BuyerId).FirstOrDefault(),
// Supplier = Entities.Suppliers.Where(m => m.IsActive == true && m.Id == x.SupplierId).FirstOrDefault(),
// // BuyerName=Entities.Buyers.Where(m=>m.Id==BuyerId)
// PurchaseOrderStatu=x.PurchaseOrderStatu,
//}).ToList();
model.data = items;
model.TotalCount = count;
return(model);
}
public List <PurchaseOrderSearchModel> FillPurchaseOrderSearchGrid(PurchaseOrderSearchModel model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <PurchaseOrderSearchModel> lstPurchaseOrder = new List <PurchaseOrderSearchModel>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetPurchaseOrderPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
PurchaseOrderSearchModel searchmodel = new PurchaseOrderSearchModel();
searchmodel.POID = Convert.ToInt32(dr["POID"].ToString());
searchmodel.PropertyID = dr["PropertyID"].ToString();
searchmodel.OrderNumber = dr["OrderNumber"].ToString();
searchmodel.Vendor = dr["Vendor"].ToString();
searchmodel.PODesc = dr["PODesc"].ToString();
searchmodel.PODate = dr["PODate"].ToString();
searchmodel.TotalAmount = Convert.ToDecimal(dr["TotalAmount"].ToString()).ToString("N2");
searchmodel.Route = dr["Route"].ToString();
searchmodel.ApprovedDate = dr["ApprovedDate"].ToString();
searchmodel.ApprovedBy = dr["ApprovedBy"].ToString();
searchmodel.CanceledDate = dr["CanceledDate"].ToString();
searchmodel.CanceledBy = dr["CanceledBy"].ToString();
searchmodel.CreatedBy = dr["CreatedBy"].ToString();
searchmodel.CreatedDate = dr["CreatedDate"].ToString();
lstPurchaseOrder.Add(searchmodel);
}
db.Dispose();
return(lstPurchaseOrder.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public virtual ActionResult ExportPOExcel(PurchaseOrderSearchModel searchModel)
{
return(ReportPurchaseOrderExtract(searchModel));
}
public virtual ActionResult GetPOReport([DataSourceRequest] DataSourceRequest request, PurchaseOrderSearchModel searchModel)
{
var list = GetPurchaseOrderReport(searchModel);
return(Json(list.ToDataSourceResult(request), JsonRequestBehavior.AllowGet));
}
