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