/// <summary> /// 装箱单页面查询post /// </summary> /// <param name="SearchCondition"></param> /// <param name="Customers"></param> /// <returns></returns> public IEnumerable <AMSUpload> GetBoxNumberAttachments(AMSSearchCondition SearchCondition, string Customers) { string sqlWhere = this.GenQueryAttachmentSql(SearchCondition, Customers); DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input), }; DataTable dt = this.ExecuteDataTable("Proc_GetBoxNumberAttachmentSql", dbParams); return(dt.ConvertToEntityCollection <AMSUpload>()); }
/// <summary> /// 对于装箱单方面的查询 /// </summary> /// <param name="SearchCondition"></param> /// <param name="StateID">查询是否有装箱单</param> /// <param name="Customers">控制查询权限</param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="RowCount"></param> /// <returns></returns> public IEnumerable <AMSUpload> GetQueryAttachments(AMSSearchCondition SearchCondition, string Customers, int PageIndex, int PageSize, out int RowCount) { string sqlWhere = this.GenQueryAttachmentSql(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_QueryAMSupload", dbParams); RowCount = (int)dbParams[3].Value; return(dt.ConvertToEntityCollection <AMSUpload>()); }
/// <summary> /// 查询装箱单号拼接sql /// </summary> /// <param name="condition"></param> /// <param name="Customers"></param> /// <returns></returns> private string GenQueryAttachmentSql(AMSSearchCondition condition, string Customers) { StringBuilder sb = new StringBuilder(); if (condition.CustomerID == null) { sb.Append("and ProjectName in(" + Customers + ")"); } if (!string.IsNullOrEmpty(condition.Numbers)) { IEnumerable <string> numbers = Enumerable.Empty <string>(); if (condition.Numbers.IndexOf("\n") > 0) { numbers = condition.Numbers.Split('\n').Select(s => { return(s.Trim()); }); } if (condition.Numbers.IndexOf(',') > 0) { numbers = condition.Numbers.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 FileName in ( "); foreach (string s in numbers) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append(" and FileName like '%" + condition.Numbers.Trim() + "%' "); } } if (condition.StateNumID.HasValue) { if (condition.StateNumID == 1) { sb.Append("and status =1"); } else { sb.Append("and status =0"); } } if (condition.StatUpLoadTime.HasValue) { sb.Append("and (Convert(date, CreateTime)>='" + condition.StatUpLoadTime.Value.ToString("yyyy-MM-dd") + "')"); } if (condition.EndUpLoadTime.HasValue) { sb.Append("and (Convert (date, CreateTime)<='" + condition.EndUpLoadTime.Value.ToString("yyyy-MM-dd") + "')"); } if (condition.CustomerID.HasValue) { sb.Append("and ProjectID=" + (int)condition.CustomerID.Value); } if (!string.IsNullOrEmpty(condition.BoxNumber)) { IEnumerable <string> boxnumber = Enumerable.Empty <string>(); if (condition.BoxNumber.IndexOf("\n") > 0) { boxnumber = condition.BoxNumber.Split('\n').Select(s => { return(s.Trim()); }); } if (condition.BoxNumber.IndexOf(',') > 0) { boxnumber = condition.BoxNumber.Split(',').Select(s => { return(s.Trim()); }); } if (boxnumber != null && boxnumber.Any()) { boxnumber = boxnumber.Where(c => !string.IsNullOrEmpty(c)); } if (boxnumber != null && boxnumber.Any()) { sb.Append(" and OrderNo in ( "); foreach (string s in boxnumber) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append("and OrderNo like '%" + condition.BoxNumber.Trim() + "%' "); } } else { if (condition.StateID.HasValue) { if (condition.StateID == 0) { sb.Append("and OrderNo=''"); } else if (condition.StateID == 1) { sb.Append("and OrderNo !=''"); } } } return(sb.ToString()); }