/// <summary>
/// 查询供应商采购单列表
/// </summary>
/// <param name="model">搜索实体对象</param>
/// <returns></returns>
/// <remarks>added by jimmy,2015-7-3</remarks>
public JsonResult List(SearchPurchaseOrderModel model)
{
model.PagedSize = model.PagedSize == 0 ? 10 : model.PagedSize;
var list = _PurchaseOrderSerivce.Select(model);
var data = new { rows = list.Data, total = list.Data.TotalCount };
return(Json(data, JsonRequestBehavior.AllowGet));
}
public ActionResult OrderPurchase(string orderId)
{
SearchPurchaseOrderModel model = new SearchPurchaseOrderModel();
ViewData["orderId"] = orderId;
model.PagedSize = 100;
model.PagedIndex = 0;
model.OrderID = orderId;
var list = _PurchaseOrderSerivce.Select(model);
return(PartialView(list.Data));
}
/// <summary>
/// 采购订单视图
/// zhoub 20150928
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ActionResult Index(SearchPurchaseOrderModel model)
{
int totalCount = 0;
int productQuantity = 0;
model.LanguageID = 1;
model.SupplierId = 5728471707;
model.PagedIndex = 1;
model.PagedSize = 5;
ResultModel result = _IPurchaseOrderSerivce.GetSuppliersPagingPurchaseOrder(model, out totalCount, out productQuantity);
return(View());
}
/// <summary>
/// 采购订单数据获取
/// zhoub 20150928
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public JsonResult List(SearchPurchaseOrderModel model)
{
int totalCount = 0;
int productQuantity = 0;
model.LanguageID = 1;
model.SupplierId = 5728471707;
model.PagedIndex = 1;
model.PagedSize = 5;
ResultModel result = _IPurchaseOrderSerivce.GetSuppliersPagingPurchaseOrder(model, out totalCount, out productQuantity);
var data = new { rows = result.Data, total = totalCount };
return(Json(data, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取导出采购订单
/// </summary>
/// <returns></returns>
public IEnumerable <ExportPurchaseOrder> GetExportPurchasingOrder(SearchPurchaseOrderModel searchPurchaseOrderModel)
{
var state = GetPurchasingOrderStates();
DateTime?dateTime = new DateTime();
var startTime = searchPurchaseOrderModel.StartTime;
var endTime = searchPurchaseOrderModel.EndTime;
var isDetail = searchPurchaseOrderModel.IsDetail;
var startWith = searchPurchaseOrderModel.StartWith;
var warehouse = searchPurchaseOrderModel.WareHouse;
var search = searchPurchaseOrderModel.Search;
var supplierName = searchPurchaseOrderModel.SupplierName;
if (startTime.Equals(dateTime))
{
startTime = null;
}
if (endTime.Equals(dateTime))
{
endTime = null;
}
var data = new List <ExportPurchaseOrder>();
//订单详情
if (isDetail)
{
data = (from p in _omsAccessor.Get <Purchasing>().Where(p => p.Isvalid && p.PurchasingNumber.StartsWith(startWith) && (string.IsNullOrEmpty(search) || p.OrgionSerialNumber.Contains(search) || p.PurchasingNumber.Contains(search) ||
p.PurchasingOrderNumber.Contains(search)) && ((!startTime.HasValue || p.CreatedTime >= startTime.Value) && (!endTime.HasValue || p.CreatedTime <= endTime.Value)))
join w in _omsAccessor.Get <WareHouse>().Where(w => (string.IsNullOrEmpty(warehouse) || w.Name == warehouse)) on p.WareHouseId equals w.Id
join sp in _omsAccessor.Get <Supplier>().Where(sp => (string.IsNullOrEmpty(supplierName) || sp.SupplierName == supplierName)) on p.SupplierId equals sp.Id
join opp in _omsAccessor.Get <PurchasingProducts>().Where(x => x.Isvalid) on p.Id equals opp.PurchasingId into op
from oppu in op.DefaultIfEmpty()
join pr in _omsAccessor.Get <Product>().Where(x => x.Isvalid) on oppu.ProductId equals pr.Id
orderby p.CreatedTime descending
select new ExportPurchaseOrder
{
PurchasingNumber = p.PurchasingNumber,
PurchasingOrderNumber = p.PurchasingOrderNumber,
OrgionSerialNumber = p.OrgionSerialNumber,
Mark = p.Mark,
CreatedTime = string.Format("{0:G}", p.CreatedTime),
CheckTime = string.Format("{0:G}", p.CreatedTime),
SupplierName = sp.SupplierName,
StateStr = state[p.State],
PurchasingProductCode = pr.Code,
PurchasingProductName = pr.Name,
UnitPrice = oppu.Price,
Quantity = oppu.Quantity,
SumPrice = oppu.Price * oppu.Quantity
}).OrderByDescending(r => r.CreatedTime).Distinct().ToList();
}
else //非订单详情
{
data = (from p in _omsAccessor.Get <Purchasing>().Where(p => p.Isvalid && p.PurchasingNumber.StartsWith(startWith) && (string.IsNullOrEmpty(search) || p.OrgionSerialNumber.Contains(search) || p.PurchasingNumber.Contains(search) ||
p.PurchasingOrderNumber.Contains(search)) && ((!startTime.HasValue || p.CreatedTime >= startTime.Value) && (!endTime.HasValue || p.CreatedTime <= endTime.Value)))
join w in _omsAccessor.Get <WareHouse>().Where(w => (string.IsNullOrEmpty(warehouse) || w.Name == warehouse)) on p.WareHouseId equals w.Id
join sp in _omsAccessor.Get <Supplier>().Where(sp => (string.IsNullOrEmpty(supplierName) || sp.SupplierName == supplierName)) on p.SupplierId equals sp.Id
orderby p.CreatedTime descending
select new ExportPurchaseOrder
{
PurchasingNumber = p.PurchasingNumber,
PurchasingOrderNumber = p.PurchasingOrderNumber,
OrgionSerialNumber = p.OrgionSerialNumber,
Mark = p.Mark,
CreatedTime = string.Format("{0:G}", p.CreatedTime),
CheckTime = string.Format("{0:G}", p.CreatedTime),
SupplierName = sp.SupplierName,
StateStr = state[p.State]
}).OrderByDescending(r => r.CreatedTime).Distinct().ToList();
}
return(data);
}
/// <summary>
/// 供应商采购单分布查询
/// </summary>
/// <param name="model">输入查询参数对象</param>
/// <param name="languageID">语言Id</param>
/// <returns></returns>
/// <remarks>added by jimmy,2015-9-8</remarks>
public ResultModel Select(SearchPurchaseOrderModel model)
{
var purchaseOrder = _database.Db.PurchaseOrder;
var suppliers = _database.Db.Suppliers;
#region 查询参数条件
//查询参数条件
var whereParam = new SimpleExpression(1, 1, SimpleExpressionType.Equal);
//PurchaseOrderId
if (!string.IsNullOrEmpty(model.PurchaseOrderId))
{
whereParam = new SimpleExpression(whereParam, purchaseOrder.PurchaseOrderId.Like("%" + model.PurchaseOrderId + "%"), SimpleExpressionType.And);
}
//订单号
if (!string.IsNullOrEmpty(model.OrderID))
{
whereParam = new SimpleExpression(whereParam, purchaseOrder.OrderID.Like("%" + model.OrderID + "%"), SimpleExpressionType.And);
}
//供应商名称
if (!string.IsNullOrEmpty(model.SupplierName))
{
whereParam = new SimpleExpression(whereParam, suppliers.SupplierName.Like("%" + model.SupplierName + "%"), SimpleExpressionType.And);
}
//手机号码
if (!string.IsNullOrEmpty(model.Mobile))
{
whereParam = new SimpleExpression(whereParam, suppliers.Mobile.Like("%" + model.Mobile + "%"), SimpleExpressionType.And);
}
//状态
if (model.status != null)
{
whereParam = new SimpleExpression(whereParam, purchaseOrder.status == model.status.Value, SimpleExpressionType.And);
}
#endregion
var query = purchaseOrder.All().
Join(suppliers).On(suppliers.SupplierId == purchaseOrder.SupplierId).
Select(
purchaseOrder.PurchaseOrderId,
purchaseOrder.OrderID,
purchaseOrder.SupplierId,
purchaseOrder.PurchaseAmount,
purchaseOrder.RealPurchaseAmount,
purchaseOrder.status,
purchaseOrder.CreateTime,
purchaseOrder.CreateBy,
purchaseOrder.Deliveryer,
purchaseOrder.DeliveryDate,
suppliers.SupplierName,
suppliers.Mobile
).Where(whereParam).OrderByCreateTimeDescending();
var result = new ResultModel()
{
Data = new SimpleDataPagedList <PurchaseOrderModel>(query,
model.PagedIndex, model.PagedSize)
};
return(result);
}
/// <summary>
/// 采购订单查询
/// zhoub 20150928
/// </summary>
/// <param name="model"></param>
/// <param name="totalCount">订单条数</param>
/// <param name="productQuantity">产品个数</param>
/// <returns></returns>
public ResultModel GetSuppliersPagingPurchaseOrder(SearchPurchaseOrderModel model, out int totalCount, out int productQuantity)
{
List <PurchaseOrderModel> list = new List <PurchaseOrderModel>();
PurchaseOrderModel pom = null;
var po = _database.Db.PurchaseOrder;
var pod = _database.Db.PurchaseOrderDetails;
var pro = _database.Db.Product;
var prol = _database.Db.Product_Lang;
var pic = _database.Db.ProductPic;
var whereExpr = po.SupplierId == model.SupplierId;
totalCount = 0;
productQuantity = 0;
if (!string.IsNullOrEmpty(model.PurchaseOrderId))
{
whereExpr = new SimpleExpression(whereExpr, pod.PurchaseOrderId == model.PurchaseOrderId.Trim(), SimpleExpressionType.And);
}
if (model.ProductId > 0)
{
whereExpr = new SimpleExpression(whereExpr, pod.ProductId == model.ProductId, SimpleExpressionType.And);
}
if (model.status > 0)
{
whereExpr = new SimpleExpression(whereExpr, po.status == model.status, SimpleExpressionType.And);
}
if (model.StartTime != null)
{
whereExpr = new SimpleExpression(whereExpr, po.CreateTime >= model.StartTime, SimpleExpressionType.And);
}
if (model.EndTime != null)
{
whereExpr = new SimpleExpression(whereExpr, po.CreateTime < Convert.ToDateTime(model.EndTime).AddDays(1), SimpleExpressionType.And);
}
var result = new ResultModel
{
Data = new SimpleDataPagedList <PurchaseOrderModel>(
po.All()
.LeftJoin(pod).On(pod.PurchaseOrderId == po.PurchaseOrderId)
.Select(po.PurchaseOrderId.Distinct(), po.CreateTime, po.status)
.Where(whereExpr).OrderBy(po.CreateTime), model.PagedIndex, model.PagedSize)
};
if (result.Data != null)
{
totalCount = result.Data.TotalCount;
productQuantity = po.All()
.LeftJoin(pod).On(pod.PurchaseOrderId == po.PurchaseOrderId)
.Select(pod.Quantity.Sum())
.Where(whereExpr).ToScalarOrDefault();
foreach (PurchaseOrderModel m in result.Data)
{
pom = new PurchaseOrderModel();
pom.PurchaseOrderId = m.PurchaseOrderId;
pom.CreateTime = m.CreateTime;
pom.status = m.status;
pom.OrderDetailViews = pod.All()
.LeftJoin(pro).On(pro.ProductId == pod.ProductId)
.LeftJoin(prol).On(prol.ProductId == pod.ProductId && prol.LanguageID == model.LanguageID)
.LeftJoin(pic).On(pic.ProductId == pod.ProductId && pic.Flag == 1)
.Select(pod.ProductId, prol.ProductName, pic.PicUrl, pod.SkuName, pod.Quantity)
.Where(pod.PurchaseOrderId == m.PurchaseOrderId).ToList <OrderDetailsModel>();
list.Add(pom);
}
}
result.Data = list;
return(result);
}
/// <summary>
/// 导出采购单信息
/// </summary>
/// <returns></returns>
public IActionResult ExportPurchaseOrder(string searchPurchaseOrderModelStr)
{
try
{
var searchPurchaseOrderModel = new SearchPurchaseOrderModel();
searchPurchaseOrderModel = JsonConvert.DeserializeObject <SearchPurchaseOrderModel>(searchPurchaseOrderModelStr);
//获取数据
var data = _purchasingService.GetExportPurchasingOrder(searchPurchaseOrderModel);
string fileName = "采购订单-" + DateTime.Now.ToString("yyyyMMddHHmmss");
//设置Excel表头
Dictionary <string, string> columnNames = new Dictionary <string, string>
{
{ "采购单号", "PurchasingNumber" },
{ "采购计划单号", "PurchasingOrderNumber" },
{ "原单号", "OrgionSerialNumber" },
{ "下单日期", "CreatedTime" },
{ "审核日期", "CheckTime" },
{ "供应商", "SupplierName" },
{ "订单状态", "StateStr" },
{ "备注", "Mark" },
};
//是否导出采购单详情
if (searchPurchaseOrderModel.IsDetail)
{
columnNames.Add("采购商品名称", "PurchasingProductName");
columnNames.Add("采购商品编码", "PurchasingProductCode");
columnNames.Add("数量", "Quantity");;
columnNames.Add("单价", "UnitPrice");
columnNames.Add("合计金额", "SumPrice");
}
DataTable table = data.ToDataTable(columnNames);
//导出.xls格式的文件
if (searchPurchaseOrderModel.ExportType == ".xls")
{
table.TableName = "Sheet1";
Stream stream = CommonTools.WriteExcel(table);
return(File(stream, "application/vnd.ms-excel;charset=UTF-8", fileName + searchPurchaseOrderModel.ExportType));
}
//导出.csv格式的文件
else if (searchPurchaseOrderModel.ExportType == ".csv")
{
StringBuilder sb = new StringBuilder();
sb.Append("采购单号,采购计划单号,原单号,下单日期,审核日期,供应商,订单状态,备注");
if (searchPurchaseOrderModel.IsDetail)
{
sb.Append(",采购商品名称,采购商品编码,数量,单价,合计金额");
}
sb.Append("\r\n");
for (int i = 0; i < table.Rows.Count; i++)
{
sb.Append("\"" + "\t" + table.Rows[i]["采购单号"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["采购计划单号"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["原单号"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["下单日期"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["审核日期"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["供应商"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["订单状态"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["备注"] + "\"");
if (searchPurchaseOrderModel.IsDetail)
{
sb.Append("," + "\"" + "\t" + table.Rows[i]["采购商品名称"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["采购商品编码"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["数量"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["单价"] + "\"" + ",");
sb.Append("\"" + "\t" + table.Rows[i]["合计金额"] + "\"");
}
sb.Append("\r\n");
}
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(sb.ToString());
writer.Flush();
stream.Position = 0;
return(File(stream, "text/csv;charset=UTF-8", fileName + searchPurchaseOrderModel.ExportType));
}
else
{
return(Json(new { Result = false, Message = "错误信息：导出失败" }));
}
}
catch (Exception ex)
{
return(Json(new { Result = false, Message = "错误信息：" + ex.Message }));
}
}
