/// <summary> /// 导出库存变更数据 /// </summary> /// <param name="SearchCondition"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public GetAdjustmentDetailByConditionResponse ExportadjustByCondition(AdjustmentSearchCondition SearchCondition) { GetAdjustmentDetailByConditionResponse response = new GetAdjustmentDetailByConditionResponse(); string sqlWhere = this.GenGetAdjustWhere(SearchCondition); DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input), }; DataSet ds = this.ExecuteDataSet("Proc_WMS_ExportAdjustmentByCondition", dbParams); response.AdjustmentCollection = ds.Tables[0].ConvertToEntityCollection <Adjustment>(); response.AdjustmentDetailCollection = ds.Tables[1].ConvertToEntityCollection <AdjustmentDetail>(); return(response); }
/// <summary> /// 库存变更数据查询 /// </summary> /// <param name="SearchCondition"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public GetAdjustmentDetailByConditionResponse GetadjustByCondition(AdjustmentSearchCondition SearchCondition, int pageIndex, int pageSize, out int rowCount) { GetAdjustmentDetailByConditionResponse response = new GetAdjustmentDetailByConditionResponse(); string sqlWhere = this.GenGetAdjustWhere(SearchCondition); 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) }; DataSet ds = this.ExecuteDataSet("Proc_WMS_GetAdjustmentByCondition", dbParams); rowCount = (int)dbParams[3].Value; response.AdjustmentCollection = ds.Tables[0].ConvertToEntityCollection <Adjustment>(); //response.AdjustmentDetailCollection = ds.Tables[1].ConvertToEntityCollection<AdjustmentDetail>(); return(response); }
private string GenGetAdjustWhere(AdjustmentSearchCondition SearchCondition) { StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(SearchCondition.AdjustmentNumber)) { IEnumerable <string> numbers = Enumerable.Empty <string>(); if (SearchCondition.AdjustmentNumber.IndexOf("\n") > 0) { numbers = SearchCondition.AdjustmentNumber.Split('\n').Select(s => { return(s.Trim()); }); } if (SearchCondition.AdjustmentNumber.IndexOf(',') > 0) { numbers = SearchCondition.AdjustmentNumber.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 a.AdjustmentNumber in ( "); foreach (string s in numbers) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } else { sb.Append(" and a.AdjustmentNumber like '%" + SearchCondition.AdjustmentNumber.Trim() + "%' "); } } if (SearchCondition.CustomerID != 0) { sb.Append(" AND a.CustomerID = ").Append(SearchCondition.CustomerID); } if (SearchCondition.CustomerID == 0 && SearchCondition.CustomerIDs != null) { IEnumerable <string> numbers = Enumerable.Empty <string>(); numbers = SearchCondition.CustomerIDs.Split(',').Select(s => { return(s.Trim()); }); if (numbers != null && numbers.Any()) { numbers = numbers.Where(c => !string.IsNullOrEmpty(c)); } sb.Append(" and a.CustomerID in ( "); foreach (string s in numbers) { sb.Append("'").Append(s).Append("',"); } sb.Remove(sb.Length - 1, 1); sb.Append(" ) "); } if (!string.IsNullOrEmpty(SearchCondition.Warehouse)) { sb.Append(" AND a.Warehouse in (select WarehouseName from wms_warehouse where id in ( ").Append(SearchCondition.Warehouse.Trim()).Append(")) "); } if (!string.IsNullOrEmpty(SearchCondition.AdjustmentType)) { sb.Append(" AND a.AdjustmentType='").Append(SearchCondition.AdjustmentType).Append("' "); } if (SearchCondition.Status != 0) { sb.Append(" AND a.Status='").Append(SearchCondition.Status).Append("' "); } if (SearchCondition.StartAdjustmentDate != null) { sb.Append(" AND a.AdjustmentTime >='").Append(SearchCondition.StartAdjustmentDate.DateTimeToString("yyyy-MM-dd 00:00:00.00")).Append("' "); } if (SearchCondition.EndAdjustmentDate != null) { sb.Append(" AND a.AdjustmentTime <='").Append(SearchCondition.EndAdjustmentDate.DateTimeToString("yyyy-MM-dd 23:59:59.99")).Append("' "); } if (SearchCondition.IsHold != 0) { sb.Append(" AND a.IsHold ='").Append(SearchCondition.IsHold).Append("' "); } //if (SearchCondition.AdjustmentReason != null) //{ // sb.Append(" and a.AdjustmentReason like '%" + SearchCondition.AdjustmentReason.Trim() + "%' "); //} if (!string.IsNullOrEmpty(SearchCondition.AdjustmentReason)) { sb.Append(" AND a.AdjustmentReason='").Append(SearchCondition.AdjustmentReason).Append("' "); } //备注 if (!string.IsNullOrEmpty(SearchCondition.Remark)) { sb.Append(" and a.Remark like '%" + SearchCondition.Remark.Trim() + "%' "); } if (!string.IsNullOrEmpty(SearchCondition.str1)) { sb.Append(" AND a.str1='").Append(SearchCondition.str1).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str2)) { sb.Append(" AND a.str2='").Append(SearchCondition.str2).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str3)) { sb.Append(" AND a.str3='").Append(SearchCondition.str3).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str4)) { sb.Append(" AND a.str4='").Append(SearchCondition.str4).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str5)) { sb.Append(" AND a.str5='").Append(SearchCondition.str5).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str6)) { sb.Append(" AND a.str6='").Append(SearchCondition.str6).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str7)) { sb.Append(" AND a.str7='").Append(SearchCondition.str7).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str8)) { sb.Append(" AND a.str8=").Append(SearchCondition.str8).Append(" "); } if (!string.IsNullOrEmpty(SearchCondition.str9)) { sb.Append(" AND a.str9='").Append(SearchCondition.str9).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str10)) { sb.Append(" AND a.str10='").Append(SearchCondition.str10).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str11)) { sb.Append(" AND a.str11='").Append(SearchCondition.str11).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str12)) { sb.Append(" AND a.str12='").Append(SearchCondition.str12).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str13)) { sb.Append(" AND a.str13='").Append(SearchCondition.str13).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str14)) { sb.Append(" AND a.str14='").Append(SearchCondition.str14).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str15)) { sb.Append(" AND a.str15='").Append(SearchCondition.str15).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str16)) { sb.Append(" AND a.str16='").Append(SearchCondition.str16).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str17)) { sb.Append(" AND a.str17='").Append(SearchCondition.str17).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str18)) { sb.Append(" AND b.SKU='").Append(SearchCondition.str18).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.str19)) { sb.Append(" AND b.FromArea=(SELECT TOP 1 AreaName FROM dbo.WMS_Warehouse_Area WHERE ID=" + SearchCondition.str19 + ")").Append(" "); } if (!string.IsNullOrEmpty(SearchCondition.str20)) { sb.Append(" AND b.ToLocation=(SELECT TOP 1 Location FROM dbo.WMS_Warehouse_Location WHERE Location='" + SearchCondition.str20 + "')").Append(" "); } if (SearchCondition.Int1 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int1.ToString()))) { sb.Append(" AND a.Int1=").Append(SearchCondition.Int1).Append(" "); } if (SearchCondition.Int2 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int2.ToString()))) { sb.Append(" AND a.Int2=").Append(SearchCondition.Int2).Append(" "); } if (SearchCondition.Int3 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int3.ToString()))) { sb.Append(" AND a.Int3=").Append(SearchCondition.Int3).Append(" "); } if (SearchCondition.Int4 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int4.ToString()))) { sb.Append(" AND a.Int4=").Append(SearchCondition.Int4).Append(" "); } if (SearchCondition.Int5 != 0 && !(string.IsNullOrEmpty(SearchCondition.Int5.ToString()))) { sb.Append(" AND a.Int5=").Append(SearchCondition.Int5).Append(" "); } return(sb.ToString()); }