/// <summary> /// 成品订单查询 /// </summary> public IEnumerable <WMS_Package> GetWMS_PackageByCondition(WMS_Package SearchCondition, string Customers, int PageIndex, int PageSize, out int RowCount) { string sqlWhere = this.GetWMS_PackageSql(SearchCondition, Customers); int tempRowCount = 0; DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input), new DbParam("@PageIndex", DbType.Int32, PageIndex, ParameterDirection.Input), new DbParam("@PageSize", DbType.Int32, PageSize, ParameterDirection.Input), new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output) }; DataTable dt = this.ExecuteDataTable("Proc_GetWMS_PackageByCondition", dbParams); RowCount = (int)dbParams[3].Value; return(dt.ConvertToEntityCollection <WMS_Package>()); }
/// <summary> /// 查询成品订单拼接sql /// </summary> private string GetWMS_PackageSql(WMS_Package condition, string Customers) { StringBuilder sb = new StringBuilder(); //成品订单号 if (!string.IsNullOrEmpty(condition.PackageNumber)) { IEnumerable <string> numbers = Enumerable.Empty <string>(); if (condition.PackageNumber.IndexOf("\n") > 0) { numbers = condition.PackageNumber.Split('\n').Select(s => { return(s.Trim()); }); } if (condition.PackageNumber.IndexOf(',') > 0) { numbers = condition.PackageNumber.Split(',').Select(s => { return(s.Trim()); }); } if (numbers != null && numbers.Any()) { numbers = numbers.Where(c => !string.IsNullOrEmpty(c)); } if (numbers != null && numbers.Any()) { sb.Append(" and PackageNumber in ( "); foreach (string s in numbers) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append(" and PackageNumber like '%" + condition.PackageNumber.Trim() + "%' "); } } //出库单号 if (!string.IsNullOrEmpty(condition.OrderNumber)) { IEnumerable <string> ordernumber = Enumerable.Empty <string>(); if (condition.OrderNumber.IndexOf("\n") > 0) { ordernumber = condition.OrderNumber.Split('\n').Select(s => { return(s.Trim()); }); } if (condition.OrderNumber.IndexOf(',') > 0) { ordernumber = condition.OrderNumber.Split(',').Select(s => { return(s.Trim()); }); } if (ordernumber != null && ordernumber.Any()) { ordernumber = ordernumber.Where(c => !string.IsNullOrEmpty(c)); } if (ordernumber != null && ordernumber.Any()) { sb.Append(" and OrderNo in ( "); foreach (string s in ordernumber) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append(" and OrderNumber like '%" + condition.OrderNumber.Trim() + "%' "); } } //快递单号 if (!string.IsNullOrEmpty(condition.ExpressNumber)) { IEnumerable <string> ordernumber = Enumerable.Empty <string>(); if (condition.ExpressNumber.IndexOf("\n") > 0) { ordernumber = condition.ExpressNumber.Split('\n').Select(s => { return(s.Trim()); }); } if (condition.ExpressNumber.IndexOf(',') > 0) { ordernumber = condition.ExpressNumber.Split(',').Select(s => { return(s.Trim()); }); } if (ordernumber != null && ordernumber.Any()) { ordernumber = ordernumber.Where(c => !string.IsNullOrEmpty(c)); } if (ordernumber != null && ordernumber.Any()) { sb.Append(" and OrderNo in ( "); foreach (string s in ordernumber) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append("and ExpressNumber like '%" + condition.ExpressNumber.Trim() + "%' "); } } //出库日期 if (condition.PackageTime.HasValue) { sb.Append(" and (Convert(date, PackageTime)>='" + condition.PackageTime.Value.ToString("yyyy-MM-dd") + "')"); } if (condition.HandoverTime.HasValue) { sb.Append(" and (Convert (date, PackageTime)<='" + condition.HandoverTime.Value.ToString("yyyy-MM-dd") + "')"); } //客户名称 //if (condition.CustomerID.HasValue) //{ // sb.Append("and ProjectID=" + (int)condition.CustomerID.Value); //} return(sb.ToString()); }