Beispiel #1
0
        public QueryResult QueryOptionalAccessories(OptionalAccessoriesQueryFilter msg)
        {
            int totalCount;
            var dataTable = ObjectFactory <IOptionalAccessoriesQueryDA> .Instance.Query(msg, out totalCount);

            return(new QueryResult()
            {
                Data = dataTable,
                TotalCount = totalCount
            });
        }
        public void Query(OptionalAccessoriesQueryReqVM vm, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
        {
            OptionalAccessoriesQueryFilter filter = vm.ConvertVM <OptionalAccessoriesQueryReqVM, OptionalAccessoriesQueryFilter>();

            filter.CompanyCode = CPApplication.Current.CompanyCode;
            filter.PagingInfo  = new QueryFilter.Common.PagingInfo
            {
                PageSize  = PageSize,
                PageIndex = PageIndex,
                SortBy    = SortField
            };
            string relativeUrl = "/MKTService/OptionalAccessories/Query";

            restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
            {
                if (args.FaultsHandle())
                {
                    return;
                }
                callback(obj, args);
            });
        }
Beispiel #3
0
        public DataTable Query(OptionalAccessoriesQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PagingInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
            if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("DiscountAmt"))
            {
                pagingEntity.SortField = pagingEntity.SortField.Replace("DiscountAmt", "Isnull(Sum(si.[Quantity] * si.[Discount]),0)");
            }
            if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("PriceDiff"))
            {
                pagingEntity.SortField = pagingEntity.SortField.Replace("PriceDiff", "Isnull(Sum(pr.[CurrentPrice]*si.[Quantity] - pr.UnitCost*si.[Quantity]+si.[Quantity]* si.[Discount]),0)");
            }

            var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCombo");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "sm.[CreateTime] DESC"))
            {
                if (filter.SysNo != null && filter.SysNo.Value > 0)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "sm.SysNo", DbType.Int32, "@SystemNumber", QueryConditionOperatorType.Equal, filter.SysNo);
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "sm.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "sm.CreateUserSysNo", DbType.Int32, "@PM", QueryConditionOperatorType.Equal, filter.PM);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.SaleRuleName", DbType.AnsiStringFixedLength,
                                                             "@SaleRuleName", QueryConditionOperatorType.Equal, filter.SaleRuleName);

                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //    "p.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);

                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //    "si.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);

                if (filter.SysNoList != null && filter.SysNoList.Count > 0)
                {
                    List <object> list = new List <object>();
                    filter.SysNoList.ForEach(p =>
                    {
                        list.Add(p);
                    });
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "sm.SysNo", DbType.Int32, "@SysNoList", QueryConditionOperatorType.In, list);
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "sm.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "sm.ReferenceType", DbType.Int32, "@ReferenceType", QueryConditionOperatorType.Equal, 3);

                if (filter.MerchantSysNo == 1)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "V.VendorType",
                        DbType.Int32,
                        "@MerchantSysNo",
                        QueryConditionOperatorType.Equal,
                        0);
                }
                //如果大于 1 则查询sysno=MerchantSysNo的记录
                if (filter.MerchantSysNo > 1)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "V.SysNo",
                        DbType.Int32,
                        "@MerchantSysNo",
                        QueryConditionOperatorType.Equal,
                        filter.MerchantSysNo);
                    sqlBuilder.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "V.VendorType",
                        DbType.Int32,
                        "@VendorType",
                        QueryConditionOperatorType.Equal,
                        1);
                }


                cmd.CommandText = sqlBuilder.BuildQuerySql();

                //构造商品系统编号,商品编号查询参数
                string strWhere = "";
                if (filter.ProductSysNo.HasValue)
                {
                    strWhere += " and p.SysNo=@ProductSysNo ";
                    cmd.AddInputParameter("@ProductSysNo", DbType.Int32, filter.ProductSysNo);
                }
                if (!string.IsNullOrWhiteSpace(filter.ProductID))
                {
                    strWhere += " and p.ProductID=@ProductID";
                    cmd.AddInputParameter("@ProductID", DbType.AnsiString, filter.ProductID);
                }
                if (filter.Category1SysNo > 0)
                {
                    strWhere += " AND C2.C1SysNo = @C1SysNo ";
                    cmd.AddInputParameter("@C1SysNo", DbType.Int32, filter.Category1SysNo);
                }
                if (filter.Category2SysNo > 0)
                {
                    strWhere += " AND C2.SysNo = @C2SysNo ";
                    cmd.AddInputParameter("@C2SysNo", DbType.Int32, filter.Category2SysNo);
                }
                if (filter.Category3SysNo > 0)
                {
                    strWhere += " AND P.Category3SysNo = @C3SysNo ";
                    cmd.AddInputParameter("@C3SysNo", DbType.Int32, filter.Category3SysNo);
                }
                if (filter.Category1SysNo < 1 && filter.Category2SysNo < 1 && filter.Category3SysNo < 1)
                {//如果没有类别条件,则不过滤 主、次 商品
                    filter.OptionalAccessoriesItemType = -1;
                }
                if (filter.OptionalAccessoriesItemType > -1)
                {
                    strWhere += " AND SI.IsMasterItem = @OptionalAccessoriesItemType ";
                    cmd.AddInputParameter("@OptionalAccessoriesItemType", DbType.Int32, filter.OptionalAccessoriesItemType);
                }

                cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);

                DataTable dt = cmd.ExecuteDataTable(new EnumColumnList {
                    { "Status", typeof(ComboStatus) }
                });

                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }