public QueryResult QueryShiftRequest(ShiftRequestQueryFilter queryFilter) { int totalCount; SetShiftRequestPMRequestRightFilter(queryFilter); var dataTable = ObjectFactory <IShiftRequestQueryDA> .Instance.QueryShiftRequest(queryFilter, out totalCount); return(new QueryResult() { Data = dataTable, TotalCount = totalCount }); }
/// <summary> /// 统计 /// </summary> /// <param name="queryCriteria"></param> /// <returns></returns> public DataSet QueryCountData(ShiftRequestQueryFilter queryCriteria) { PagingInfoEntity pagingEntity = PageInfoToEntity(queryCriteria.PagingInfo); pagingEntity.SortField = ShiftRequestSortFieldMapping(pagingEntity.SortField); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCountData"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC")) { BuildeCondition(queryCriteria, pagingEntity, cmd, sqlBuilder); return(cmd.ExecuteDataSet()); } }
private void btnSearch_Click(object sender, RoutedEventArgs e) { ValidationManager.Validate(expanderCondition); if (PageView.QueryInfo.HasValidationErrors) { return; } //查询: if (PageView.QueryInfo.ProductSysNo.HasValue) { ShiftRequestQueryFilter queryFilter = new ShiftRequestQueryFilter(); queryFilter.ProductSysNo = PageView.QueryInfo.ProductSysNo; queryFilter.UserName = Newegg.Oversea.Silverlight.ControlPanel.Core.CPApplication.Current.LoginUser.LoginName; queryFilter.CompanyCode = Newegg.Oversea.Silverlight.ControlPanel.Core.CPApplication.Current.CompanyCode; queryFilter.UserSysNo = Newegg.Oversea.Silverlight.ControlPanel.Core.CPApplication.Current.LoginUser.UserSysNo; } dgShiftRequestQueryResult.Bind(); }
private static void SetShiftRequestPMRequestRightFilter(ShiftRequestQueryFilter queryFilter) { if (null != queryFilter.PMQueryRightType) { List <int> pms = new List <int>(); pms = new InentoryAppService().QueryPMListByRight(queryFilter.PMQueryRightType.Value, queryFilter.UserName, queryFilter.CompanyCode); if (pms != null && pms.Count > 0) { foreach (var item in pms) { queryFilter.AuthorizedPMsSysNumber += "," + item; } } if (queryFilter.AuthorizedPMsSysNumber.Contains(",")) { queryFilter.AuthorizedPMsSysNumber = queryFilter.AuthorizedPMsSysNumber.Remove(0, 1); } } }
public QueryResultList QueryShiftCountData(ShiftRequestQueryFilter queryFilter) { SetShiftRequestPMRequestRightFilter(queryFilter); var data = ObjectFactory <IShiftRequestQueryDA> .Instance.QueryCountData(queryFilter); var result = new QueryResultList { new QueryResult() { Data = data.Tables[0] } }; if (data.Tables.Count > 1) { result.Add(new QueryResult { Data = data.Tables[1] }); } return(result); }
/// <summary> /// 查询移仓单 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public virtual DataTable QueryShiftRequest(ShiftRequestQueryFilter queryCriteria, out int totalCount) { PagingInfoEntity pagingEntity = PageInfoToEntity(queryCriteria.PagingInfo); pagingEntity.SortField = ShiftRequestSortFieldMapping(pagingEntity.SortField); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryShiftRequest"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC")) { //Build Query Condition BuildeCondition(queryCriteria, pagingEntity, cmd, sqlBuilder); EnumColumnList enumColumnList = new EnumColumnList(); enumColumnList.Add("RequestStatus", typeof(ShiftRequestStatus)); enumColumnList.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus)); enumColumnList.Add("SpecialShiftType", typeof(SpecialShiftRequestType)); var resultData = cmd.ExecuteDataTable(enumColumnList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(resultData); } }
private static void BuildeCondition(ShiftRequestQueryFilter queryCriteria, PagingInfoEntity pagingEntity, CustomDataCommand cmd, DynamicQuerySqlBuilder sqlBuilder) { if (!string.IsNullOrEmpty(queryCriteria.RequestID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ShiftID", DbType.String, "@RequestID", QueryConditionOperatorType.Like, queryCriteria.RequestID); } if (queryCriteria.ShiftRequestSysNoList != null && queryCriteria.ShiftRequestSysNoList.Count > 0) { sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "a.SysNo", DbType.Int32, queryCriteria.ShiftRequestSysNoList); } if (!string.IsNullOrEmpty(queryCriteria.CompanyCode)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); } if (queryCriteria.CreateDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThan, queryCriteria.CreateDateFrom); } if (queryCriteria.CreateDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThan, queryCriteria.CreateDateTo.Value.AddDays(1)); } if (queryCriteria.CreateUserSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.CreateUserSysNo.Value); } if (queryCriteria.InStockDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.InTime", DbType.DateTime, "@InStockDateFrom", QueryConditionOperatorType.MoreThan, queryCriteria.InStockDateFrom); } if (queryCriteria.InStockDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.InTime", DbType.DateTime, "@InStockDateTo", QueryConditionOperatorType.LessThan, queryCriteria.InStockDateTo.Value.AddDays(1)); } //是否为特殊移仓单 if (queryCriteria.IsSpecialShift.HasValue && queryCriteria.IsSpecialShift.Value) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SpecialShiftType", DbType.Int32, "@SpecialShiftType", QueryConditionOperatorType.MoreThan, 0); } if (queryCriteria.SpecialShiftRequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SpecialShiftType", DbType.Int32, "@SpecialShiftRequestStatus", QueryConditionOperatorType.Equal, queryCriteria.SpecialShiftRequestStatus); } if (queryCriteria.ProductSysNo.HasValue && queryCriteria.ProductSysNo > 0) { //添加子查询,并添加参数 sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "", QueryConditionOperatorType.Exist, @" SELECT TOP 1 SysNo FROM St_Shift_Item si WITH(NOLOCK) WHERE a.SysNo = si.ShiftSysNo AND si.ProductSysNo = @ProductSysNo"); cmd.AddInputParameter("@ProductSysNo", DbType.Int32, queryCriteria.ProductSysNo); } if (queryCriteria.OutStockDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.OutTime", DbType.DateTime, "@OutStockDateFrom", QueryConditionOperatorType.MoreThan, queryCriteria.OutStockDateFrom); } if (queryCriteria.OutStockDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.OutTime", DbType.DateTime, "@OutStockDateTo", QueryConditionOperatorType.LessThan, queryCriteria.OutStockDateTo.Value.AddDays(1)); } if (queryCriteria.ConsignFlag.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IsConsign", DbType.Int32, "@ConsignFlag", QueryConditionOperatorType.Equal, queryCriteria.ConsignFlag); } if (queryCriteria.SOStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AutoST.SOstatus", DbType.Int32, "@SOstatus", QueryConditionOperatorType.Equal, queryCriteria.SOStatus); } if (queryCriteria.RequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@RequestStatus", QueryConditionOperatorType.Equal, queryCriteria.RequestStatus); } //当前数据存放的是配送方式描述,而不是枚举值 sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ShipViaTerm", DbType.String, "@ShipViaTerm", QueryConditionOperatorType.Equal, queryCriteria.ShiftShippingType); if (queryCriteria.ShiftRquestType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ShiftType", DbType.Int32, "@ShiftType", QueryConditionOperatorType.Equal, queryCriteria.ShiftRquestType); } if (queryCriteria.SourceStockSysNo.HasValue && queryCriteria.SourceStockSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNoA", DbType.Int32, "@SourceStockSysNo", QueryConditionOperatorType.Equal, queryCriteria.SourceStockSysNo); } if (queryCriteria.TargetStockSysNo.HasValue && queryCriteria.TargetStockSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNoB", DbType.Int32, "@TargetStockSysNo", QueryConditionOperatorType.Equal, queryCriteria.TargetStockSysNo); } if (queryCriteria.CreateUserSysNo.HasValue && queryCriteria.CreateUserSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.CreateUserSysNo); } if (queryCriteria.IsVirtualTransfer.HasValue) { switch (queryCriteria.IsVirtualTransfer.Value) { case VirtualTransferType.Yes: sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.Status", QueryConditionOperatorType.NotIn, "1,2"); sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.IsScanned", QueryConditionOperatorType.In, "0"); sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo", QueryConditionOperatorType.In, "493"); break; case VirtualTransferType.No: sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.Status", QueryConditionOperatorType.NotIn, "1,2"); sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.IsScanned", QueryConditionOperatorType.NotIn, "0"); break; case VirtualTransferType.Empty: sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.Status", QueryConditionOperatorType.In, "1,2"); break; default: break; } } //不是高级权限 if (queryCriteria.PMQueryRightType != PMQueryType.AllValid) { string subSQLString_ProductSysNoList = @" SELECT ProductLineSysNo FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p WHERE PMUserSysNo=" + queryCriteria.UserSysNo + " OR CHARINDEX(';'+CAST(" + queryCriteria.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0"; sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.ProductLineSysno", QueryConditionOperatorType.In, subSQLString_ProductSysNoList); } cmd.CommandText = sqlBuilder.BuildQuerySql(); string tmpSql = string.Empty; string soCondition = null; bool isSOCondition = false; if (queryCriteria.SOStatus.HasValue) { soCondition = String.Format(" AND so.[Status]={0} ", (int)queryCriteria.SOStatus.Value); isSOCondition = true; } if (queryCriteria.SOSysNoList != null && queryCriteria.SOSysNoList.Count > 0) { soCondition = String.Format(" AND so.SysNo IN({0}) ", queryCriteria.SOSysNoList.Join(",")); isSOCondition = true; } if (isSOCondition) { tmpSql = String.Format(@"INNER JOIN ( SELECT DISTINCT sost.ShiftSysNo ShiftSysNo ,so.SysNo SOSysNo ,so.OutTime SOOutStockDate ,so.Status SOStatus FROM IPP3.dbo.SO_AutoShift sost WITH (NOLOCK) INNER JOIN IPP3.dbo.V_SO_Master so WITH (NOLOCK) ON so.SysNo = sost.SOSysNo AND sost.Status= 1 {0}) AutoST ON AutoST.ShiftSysNo = a.SysNo", soCondition); } else if (pagingEntity.SortField != null && (pagingEntity.SortField.ToUpper().IndexOf("AUTOST") > -1 || pagingEntity.SortField.ToUpper().IndexOf("SOOUTSTOCKDATE") > -1)) { tmpSql = @"INNER JOIN ( SELECT DISTINCT sost.ShiftSysNo ShiftSysNo ,so.SysNo SOSysNo ,so.OutTime SOOutStockDate ,so.Status SOStatus FROM IPP3.dbo.SO_AutoShift sost WITH (NOLOCK) INNER JOIN IPP3.dbo.V_SO_Master so WITH (NOLOCK) ON so.SysNo = sost.SOSysNo AND sost.Status= 1 ) AutoST ON AutoST.ShiftSysNo = a.SysNo"; } cmd.CommandText = cmd.CommandText.Replace("#JoinType_Count#", tmpSql); cmd.CommandText = cmd.CommandText.Replace("#SortByWeightCondition#", (pagingEntity.SortField != null && pagingEntity.SortField.ToUpper().IndexOf("TOTALWEIGHT") > -1) ? @" LEFT JOIN ( SELECT si.ShiftSysNo ,SUM(si.ShiftQty * product.weight) AS TotalWeight FROM IPP3.dbo.St_Shift_Item si WITH (NOLOCK) LEFT JOIN OverseaContentManagement.dbo.V_CM_ItemBasicInfo product WITH (NOLOCK) ON si.ProductSysNo=product.SysNo GROUP BY si.ShiftSysNo ) AS b ON a.SysNo = b.ShiftSysNo " : string.Empty); cmd.CommandText = cmd.CommandText.Replace("#JoinType#", (queryCriteria.SOStatus.HasValue ? "INNER JOIN" : "LEFT JOIN")); }