コード例 #1
0
ファイル: NoticesDA.cs プロジェクト: jeanmahai/28helpmate
        /// <summary>
        /// 查询公告
        /// </summary>
        /// <param name="filter">查询条件</param>
        public static QueryResult<Notices> QueryNotices(NoticesQueryFilter filter)
        {
            QueryResult<Notices> result = new QueryResult<Notices>();
            PagingInfoEntity page = new PagingInfoEntity();
            page.SortField = (filter.SortList == null || filter.SortList.Count == 0) ? null : filter.SortListToString();
            page.MaximumRows = filter.PageSize;
            page.StartRowIndex = (filter.PageIndex - 1) * filter.PageSize;
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryNotices");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo",
                    QueryConditionOperatorType.Equal, filter.SysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String, "@Contents",
                    QueryConditionOperatorType.Like, filter.Contents);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status",
                    QueryConditionOperatorType.Equal, filter.Status);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.ResultList = cmd.ExecuteEntityList<Notices>();

                int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                result.PagingInfo = new PagingInfo() { PageIndex = filter.PageIndex, PageSize = filter.PageSize, TotalCount = totalCount };
                return result;
            }
        }
コード例 #2
0
 public static PagingInfoEntity ToPagingInfo(FilterBase filter)
 {
     PagingInfoEntity page = new PagingInfoEntity();
     page.MaximumRows = filter.PageSize;
     page.StartRowIndex = filter.ServicePageIndex * filter.PageSize;
     return page;
 }
コード例 #3
0
        public DataTable Query(HotSaleCategoryQueryFilter query, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = query.PageInfo.SortBy;
            pagingEntity.MaximumRows   = query.PageInfo.PageSize;
            pagingEntity.StartRowIndex = query.PageInfo.PageIndex * query.PageInfo.PageSize;
            var cmd        = DataCommandManager.CreateCustomDataCommandFromConfig("WebSite_QueryHotSaleCategory");
            var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "h.TransactionNumber desc");

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Status",
                                                         DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal,
                                                         query.Status);

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Position",
                                                         DbType.Int32, "@Position", QueryConditionOperatorType.Equal,
                                                         query.Position);

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.GroupName",
                                                         DbType.String, "@GroupName", QueryConditionOperatorType.Like,
                                                         query.GroupName);

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.PageType",
                                                         DbType.Int32, "@PageType", QueryConditionOperatorType.Equal,
                                                         query.PageType);

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.CompanyCode",
                                                         DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal,
                                                         query.CompanyCode);
            //TODO:添加ChannelID参数

            cmd.CommandText = sqlBuilder.BuildQuerySql();

            EnumColumnList enumConfig = new EnumColumnList();

            enumConfig.Add("Status", typeof(ADStatus));
            var dt = cmd.ExecuteDataTable(enumConfig);

            totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
            return(dt);
        }
コード例 #4
0
ファイル: UserDA.cs プロジェクト: jeanmahai/28helpmate
        /// <summary>
        /// 查询用户
        /// </summary>
        /// <param name="pageIndex">取第几页</param>
        /// <param name="pageSize">每页显示几条</param>
        /// <param name="userID">用户登录名</param>
        /// <param name="status">状态</param>
        /// <param name="payUserBeginTime">充值使用时间起</param>
        /// <param name="payUseEndTime">充值使用时间止</param>
        /// <returns></returns>
        public static PageList<List<User>> QueryUser(int pageIndex, int pageSize, string userID, int status, DateTime payUserBeginTime, DateTime payUseEndTime)
        {
            PagingInfoEntity page = new PagingInfoEntity();
            page.MaximumRows = pageSize;
            page.StartRowIndex = pageIndex * pageSize;
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Users_Query");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserID", DbType.String, "@UserID", QueryConditionOperatorType.Like, userID);
                if (status >= 0)
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseBeginTime", DbType.DateTime, "@PayUseBeginTime", QueryConditionOperatorType.MoreThanOrEqual, payUserBeginTime);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseEndTime", DbType.DateTime, "@PayUseEndTime", QueryConditionOperatorType.LessThanOrEqual, payUseEndTime);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                List<User> result = cmd.ExecuteEntityList<User>();

                int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return new PageList<List<User>>(result, pageIndex, pageSize, totalCount);
            }
        }
コード例 #5
0
        /// <summary>
        /// 查询商品批次信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public virtual DataTable QueryProductBatch(ProductBatchQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = "batch.BatchNumber";
            pagingEntity.MaximumRows   = filter.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;

            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductBatchInfoByProductAndStock");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "batch.BatchNumber"))
            {
                if (filter.ProductSysNo.HasValue && filter.ProductSysNo > 0)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "p.SysNo"
                                                                 , DbType.Int32
                                                                 , "@ProductSysNo"
                                                                 , QueryConditionOperatorType.Equal
                                                                 , filter.ProductSysNo);
                }

                if (filter.StockSysNo.HasValue && filter.StockSysNo > 0)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "bStock.StockSysNo"
                                                                 , DbType.Int32
                                                                 , "@StockSysNo"
                                                                 , QueryConditionOperatorType.Equal
                                                                 , filter.StockSysNo);
                }

                cmd.CommandText = sqlBuilder.BuildQuerySql();

                var result = cmd.ExecuteDataTable();
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return(result);
            }
        }
コード例 #6
0
        /// <summary>
        /// 查询礼品券关联请求
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryGiftVoucherProductRelationReq(GiftCardProductFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "GP.SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GP.AuditStatus", DbType.AnsiStringFixedLength,
                                                             "@AuditStatus", QueryConditionOperatorType.Equal, "W");

                if (filter.SysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GV.GiftVoucherSysNo"
                                                                 , DbType.Int32, "@GiftVoucherSysNo", QueryConditionOperatorType.Equal, filter.SysNo);
                }

                cmd.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("AuditStatus", typeof(ECCentral.BizEntity.IM.GVRReqAuditStatus));

                var dt = cmd.ExecuteDataTable(enumList);

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

                return(dt);
            }
        }
コード例 #7
0
ファイル: HotKeywordsQueryDA.cs プロジェクト: sanlonezhang/ql
        /// <summary>
        /// 查询热门关键字
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryHotKeywords(HotKeywordsQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC"))
            {
                //TODO:添加渠道查询条件
                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.PageID", DbType.Int32, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.Keyword", DbType.String, "@Keyword", QueryConditionOperatorType.Like, filter.Keywords);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.Priority", DbType.Int32, "@Priority", QueryConditionOperatorType.Equal, filter.Priority);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.EditUserSysNo", DbType.Int32, "@EditUserSysNo", QueryConditionOperatorType.Equal, filter.EditUserSysNo);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.IsOnlineShow", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.IsOnlineShow);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "sk.EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.EditDateFrom, filter.EditDateTo);
                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "sk.HiddenDate", DbType.DateTime, "@HiddenDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.InvalidDateFrom, filter.InvalidDateTo);


                cmd.CommandText = sqlBuilder.BuildQuerySql();
                var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.NYNStatus>("IsOnlineShow");
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #8
0
ファイル: CallingLogQueryDA.cs プロジェクト: sanlonezhang/ql
        public virtual DataTable GetRMARegisterList(RMARegisterQueryFilter 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;


            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetRMARegisterList");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "B.RequestSysNo",
                    DbType.Int32,
                    "@RequestSysNo ",
                    QueryConditionOperatorType.Equal,
                    filter.RequestSysNo);

                sqlBuilder.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "A.ProductSysNo",
                    DbType.Int32,
                    "@ProductSysNo",
                    QueryConditionOperatorType.Equal,
                    filter.ProductSysNo
                    );
                sqlBuilder.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "A.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
                    QueryConditionOperatorType.Equal,
                    filter.CompanyCode);
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                DataTable dt = cmd.ExecuteDataTable();
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #9
0
        /// <summary>
        /// 查询默认关键字
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryDefaultKeywords(DefaultKeywordsQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "TransactionNumber DESC"))
            {
                //TODO:添加渠道查询条件
                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DefaultKeyword", DbType.String, "@DefaultKeyword", QueryConditionOperatorType.Like, filter.Keywords);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageID", DbType.Int32, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "BeginDate", DbType.DateTime, "@BeginDate",
                                                                    QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.BeginDateFrom, filter.BeginDateTo);

                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "EndDate", DbType.DateTime, "@EndDate",
                                                                    QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.EndDateFrom, filter.EndDateTo);


                cmd.CommandText = sqlBuilder.BuildQuerySql();
                var dt = cmd.ExecuteDataTable <ADStatus>("Status");
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #10
0
ファイル: FPCheckQueryDA.cs プロジェクト: sanlonezhang/ql
        public virtual DataTable Query(FPCheckQueryFilter queryCriteria, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = queryCriteria.PagingInfo.SortBy;
            pagingEntity.MaximumRows   = queryCriteria.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
            var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFPCheckMaster");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " a.sysno ASC "))
            {
                sqlBuilder.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
                    QueryConditionOperatorType.Equal, "8601");
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                cmd.SetParameterValue("@ChannelID", queryCriteria.ChannelID);
                DataTable dt = cmd.ExecuteDataTable();
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #11
0
ファイル: VendorStoreQueryDA.cs プロジェクト: sanlonezhang/ql
        public DataTable QuerySecondDomain(SecondDomainQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingInfo = new PagingInfoEntity()
            {
                SortField     = filter.PageInfo.SortBy,
                StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize,
                MaximumRows   = filter.PageInfo.PageSize
            };

            CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySecondDomain");

            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "v.[SysNo] DESC"))
            {
                builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "v.SysNo", DbType.Int32, filter.VendorSysNoList);

                command.CommandText = builder.BuildQuerySql();
                DataTable dt = command.ExecuteDataTable();

                totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #12
0
        /// <summary>
        /// 用户流失分析报表
        /// </summary>
        /// <param name="filter">条件</param>
        /// <returns></returns>
        public static StatementResult <ArrayList> CustomersLoseAnalyze(CustomersLoseAnalyzeFilter filter)
        {
            StatementResult <ArrayList> result = new StatementResult <ArrayList>();

            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize         = filter.PageSize;

            PagingInfoEntity  page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd  = DataCommandManager.CreateCustomDataCommandFromConfig("CustomersLoseAnalyze");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate ASC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
                                                             "@LogDate", QueryConditionOperatorType.LessThan, DateTime.Parse(DateTime.Now.AddDays(-1 * filter.Day).ToShortDateString()));

                cmd.CommandText   = sqlBuilder.BuildQuerySql();
                result.Result     = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return(result);
            }
        }
コード例 #13
0
ファイル: KeywordQueryDA.cs プロジェクト: sanlonezhang/ql
        /// <summary>
        /// 查询自动匹配关键字
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QuerySearchedKeywords(SearchedKeywordsFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
            {
                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);

                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Keywords", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.Keywords);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CreateUserType", DbType.Int32, "@CreateUserType", QueryConditionOperatorType.Like, filter.CreateUserType);

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

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


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

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.ADStatus>("Status");
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #14
0
        private DataTable GetVendorSettleCanbePayList(CanBePayOrderQueryFilter filter, out int totalCount)
        {
            DataTable         result      = null;
            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetVendorSettleCanBePayList");
            PagingInfoEntity  pagingInfo  = CreatePagingInfo(filter);

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, pagingInfo, "vendorSettle.SysNo desc"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.SettleID",
                                                             DbType.String, "@SettleID", QueryConditionOperatorType.Like, filter.OrderID);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.Status",
                                                             DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.VendorSettleStatus);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.CompanyCode",
                                                             DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);

                dataCommand.CommandText = sqlBuilder.BuildQuerySql();

                result = ExecuteDataTable(dataCommand, out totalCount);
            }
            return(result);
        }
コード例 #15
0
        public DataTable QueryPurchaseOrderHistory(PurchaseOrderQueryFilter queryFilter, out int totalCount)
        {
            DataTable dt = new DataTable();

            PagingInfoEntity pagingInfo = new PagingInfoEntity()
            {
                SortField     = queryFilter.PageInfo.SortBy,
                StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
                MaximumRows   = queryFilter.PageInfo.PageSize
            };

            CustomDataCommand command = command = DataCommandManager.CreateCustomDataCommandFromConfig("POhistory");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "intime desc"))
            {
                if (!string.IsNullOrEmpty(queryFilter.ProductSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_item.ProductSysNo",
                                                                 DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
                }

                sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po_master.Status", QueryConditionOperatorType.In,
                                                                     "4,6,7,8");

                if (!string.IsNullOrEmpty(queryFilter.StockSysNo) && queryFilter.StockSysNo != "-999999")
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.StockSysNo",
                                                                 DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.CompanyCode",
                                                             DbType.Int32, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
                command.CommandText = sqlBuilder.BuildQuerySql();
                dt         = command.ExecuteDataTable();
                totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
            }
            return(dt);
        }
コード例 #16
0
        /// <summary>
        /// 优惠券发放记录查询
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryCouponCodeCustomerLog(CouponCodeCustomerLogFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PageInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "Coupon.InDate desc"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Coupon.CouponName", DbType.String,
                                                             "@CouponName", QueryConditionOperatorType.Like, filter.CouponName);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Customer.SysNo", DbType.String,
                                                             "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);

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

                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "Coupon.SysNo", DbType.Int32,
                                                                    "@SysNo", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual,
                                                                    filter.CouponBeginNo, filter.CouponEndNo);

                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "CustomerLog.GetCouponCodeDate", DbType.DateTime,
                                                                    "@GetCouponCodeDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan,
                                                                    filter.BeginUseDate, filter.EndUseDate);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                var dt = cmd.ExecuteDataTable(new EnumColumnList {
                    { "Status", typeof(CouponsStatus) }
                });

                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #17
0
        /// <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);
            }
        }
コード例 #18
0
        public DataTable RefundAdjustQuery(RefundAdjustQueryFilter filter, out int totalCount)
        {
            MapSortField(filter);
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PagingInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;

            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_Query_RefundAdjust");

            using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "R.[SysNo] DESC"))
            {
                AddRefundAdjustParameters(filter, cmd, sb);
                //EnumColumnList colList = new EnumColumnList();
                //colList.Add("AdjustType", typeof(RefundAdjustType));
                //colList.Add("RefundPayType", typeof(RefundPayType));
                DataTable dt = cmd.ExecuteDataTable();

                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #19
0
        public DataTable Query(RefundBalanceQueryFilter queryFilter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = queryFilter.PagingInfo.SortBy;
            pagingEntity.MaximumRows   = queryFilter.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize;
            var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRefundBalance");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, null, "RB.[SysNo] DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RB.[OrgRefundSysNo]", DbType.Int32, "@RefundSysNo", QueryConditionOperatorType.Equal, queryFilter.RefundSysNo);
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                cmd.ReplaceParameterValue("#OrderType#", SOIncomeOrderType.RO_Balance);

                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("Status", typeof(RefundBalanceStatus));
                enumList.Add("AuditStatus", typeof(RefundStatus));
                DataTable dt = cmd.ExecuteDataTable(enumList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #20
0
        /// <summary>
        /// SAP供应商查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public virtual DataTable QueryVendor(SAPVendorQueryFilter query, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = query.PagingInfo.SortBy;
            pagingEntity.MaximumRows   = query.PagingInfo.PageSize;
            pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;

            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySAPVendor");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, pagingEntity, "SV.VendorSysNo desc"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SV.VendorSysNo",
                                                             DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, query.VendorSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SV.CompanyCode",
                                                             DbType.StringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                DataTable dt = dataCommand.ExecuteDataTable();
                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #21
0
ファイル: CategoryQueryDA.cs プロジェクト: sanlonezhang/ql
        public virtual DataTable QueryCategory(CategoryQueryFilter queryCriteria, out int totalCount)
        {
            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategory");
            var pagingInfo = new PagingInfoEntity
            {
                SortField     = queryCriteria.PagingInfo.SortBy,
                StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
                MaximumRows   = queryCriteria.PagingInfo.PageSize
            };

            using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "SysNo DESC"))
            {
                if (!String.IsNullOrEmpty(queryCriteria.CategoryName))
                {
                    dataCommand.AddInputParameter("@CategoryName", DbType.String, queryCriteria.CategoryName);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "C3Name",
                                                                 DbType.String, "@CategoryName",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.CategoryName);
                }
                if (queryCriteria.Status != null)
                {
                    dataCommand.AddInputParameter("@Status", DbType.String, queryCriteria.Status);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "Status",
                                                                 DbType.String, "@Status",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.Status);
                }

                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                DataTable dt = dataCommand.ExecuteDataTable("CategoryStatus", typeof(CategoryStatus));
                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #22
0
        public DataTable QueryVirtualRequestCloseLog(VirtualRequestQueryFilter queryFilter, out int totalCount)
        {
            if (queryFilter == null)
            {
                totalCount = 0;
                return(null);
            }
            DataTable        dt         = new DataTable();
            PagingInfoEntity pagingInfo = new PagingInfoEntity()
            {
                SortField     = queryFilter.PagingInfo.SortBy,
                StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
                MaximumRows   = queryFilter.PagingInfo.PageSize
            };

            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualInventoryCloseLog");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, " SysNo DESC"))
            {
                if (queryFilter.SysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VirtualRequestSysNo",
                                                                 DbType.AnsiStringFixedLength, "@VirtualRequestSysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo.Value.ToString());
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode",
                                                             DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList list = new EnumColumnList();
                list.Add("ActionType", typeof(VirtualRequestActionType));
                list.Add("VirtualRequestStatus", typeof(VirtualRequestStatus));
                dt         = dataCommand.ExecuteDataTable(list);
                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
            }
            return(dt);
        }
コード例 #23
0
ファイル: KeywordQueryDA.cs プロジェクト: sanlonezhang/ql
        /// <summary>
        /// 查询同义词
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryThesaurusKeywords(ThesaurusKeywordsQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }

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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "TransactionNumber DESC"))
            {
                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "WordContent", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.ThesaurusWords);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, filter.Type);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);

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

                cmd.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("Status", typeof(ECCentral.BizEntity.MKT.ADTStatus));//前台展示状态
                enumList.Add("Type", typeof(ECCentral.BizEntity.MKT.ThesaurusWordsType));
                var dt = cmd.ExecuteDataTable(enumList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #24
0
ファイル: RMATrackingQueryDA.cs プロジェクト: sanlonezhang/ql
        public virtual DataTable QueryRMATracking(RMATrackingQueryFilter 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;
            var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryTracking");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " A.SysNo DESC "))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.RegisterSysNo", DbType.Int32, "@RegisterSysNo", QueryConditionOperatorType.Equal, filter.RegisterSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo);
                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.CreateTime", DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.CreateTimeFrom, filter.CreateTimeTo);
                sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.UpDateTime", DbType.DateTime, "@CloseTime", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.CloseTimeFrom, filter.CloseTimeTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, filter.CreateUserSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.UpdateUserSysNo", DbType.Int32, "@UpdateUserSysNo", QueryConditionOperatorType.Equal, filter.UpdateUserSysNo);

                if (filter.NextHandler != null)
                {
                    ConditionConstructor subQueryBuilder = sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "Select SysNo From dbo.RMA_Register RMA with(nolock)");
                    subQueryBuilder.AddCustomCondition(QueryConditionRelationType.AND, "RMA.SysNo=A.RegisterSysNo");
                    subQueryBuilder.AddCondition(QueryConditionRelationType.AND, "RMA.NextHandler", DbType.Int32, "@NextHandler", QueryConditionOperatorType.Equal, filter.NextHandler);
                }
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("Status", typeof(InternalMemoStatus));
                CodeNamePairColumnList codeNameList = new CodeNamePairColumnList();
                codeNameList.Add("Source", "RMA", "RMAInternalMemoSourceType");
                DataTable dt = cmd.ExecuteDataTable(enumList, codeNameList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #25
0
ファイル: KeywordQueryDA.cs プロジェクト: sanlonezhang/ql
        /// <summary>
        /// 查询中文词库
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QuerySegment(SegmentQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            if (filter.PageInfo == null)
            {
                pagingEntity = null;
            }
            else
            {
                pagingEntity.SortField     = filter.PageInfo.SortBy;
                pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
                pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            }


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

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
            {
                //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Keywords", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.Keywords);

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

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

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                //CodeNamePairColumnList pairList = new CodeNamePairColumnList();
                //pairList.Add("Status", "MKT", "KeywordsStatus");
                //var dt = cmd.ExecuteDataTable(pairList);
                var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.KeywordsStatus>("Status");
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #26
0
        public DataTable Query(PageTypeQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PageInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            var cmd        = DataCommandManager.CreateCustomDataCommandFromConfig("PageType_QueryPageType");
            var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC");

            sqlBuilder.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "A.PageTypeName",
                DbType.String,
                "@PageTypeName",
                QueryConditionOperatorType.Like,
                filter.PageTypeName);

            sqlBuilder.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "A.CompanyCode",
                DbType.AnsiStringFixedLength,
                "@CompanyCode",
                QueryConditionOperatorType.Equal,
                filter.CompanyCode);
            //TODO:添加ChannelID参数

            cmd.CommandText = sqlBuilder.BuildQuerySql();
            EnumColumnList enumConfig = new EnumColumnList();

            enumConfig.Add("Status", typeof(ADStatus));
            var dt = cmd.ExecuteDataTable(enumConfig);

            totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
            return(dt);
        }
コード例 #27
0
        /// <summary>
        /// 发票打印查询
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, out int totalCount)
        {
            DataTable result = null;

            MapSortField4InvoicePrintAllQuery(filter);

            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoicePrintAll");

            dataCommand.AddInputParameter("@NEG", DbType.AnsiStringFixedLength, InvoiceType.SELF);
            dataCommand.AddInputParameter("@MET", DbType.AnsiStringFixedLength, InvoiceType.MET);

            PagingInfoEntity pagingInfo = new PagingInfoEntity();

            if (filter.PagingInfo != null)
            {
                pagingInfo.MaximumRows   = filter.PagingInfo.PageSize;
                pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
                pagingInfo.SortField     = filter.PagingInfo.SortBy;
            }
            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, pagingInfo, "so.SysNo DESC"))
            {
                AddParameter4InvoicePrintAllQuery(filter, sqlBuilder);
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList enumColumns = new EnumColumnList();
                enumColumns.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus));

                CodeNamePairColumnList codeNamePairColunms = new CodeNamePairColumnList();
                codeNamePairColunms.Add("InvoiceType", "Invoice", "SOIsVAT");

                result     = dataCommand.ExecuteDataTable(enumColumns, codeNamePairColunms);
                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
            }
            return(result);
        }
コード例 #28
0
ファイル: TopItemQueryDA.cs プロジェクト: sanlonezhang/ql
        public System.Data.DataTable QueryTopItem(QueryFilter.MKT.TopItemFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PageInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;

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

            cmd.SetParameterValue("@FrontPageSize", filter.FrontPageSize);
            cmd.SetParameterValue("@CategoryType", filter.PageType);
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "OrderPriority ASC,CreateTime DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[Status]", DbType.String, "@Status", QueryConditionOperatorType.Equal, 1);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[ProductType]", DbType.String, "@ProductType", QueryConditionOperatorType.Equal, 0);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C1SysNo", DbType.String, "@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo);
                if (filter.PageType.HasValue)
                {
                    PageTypePresentationType pType = PageTypeUtil.ResolvePresentationType(ModuleType.TopItem, filter.PageType.Value.ToString());
                    if (pType == PageTypePresentationType.Category2)
                    {
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C2SysNo", DbType.String, "@C2SysNo", QueryConditionOperatorType.Equal, filter.RefSysNo);
                    }
                    else if (pType == PageTypePresentationType.Category3)
                    {
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C3SysNo", DbType.String, "@C3SysNo", QueryConditionOperatorType.Equal, filter.RefSysNo);
                    }
                }
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                DataTable dt = cmd.ExecuteDataTable();
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #29
0
        public System.Data.DataTable QuerySaleGiftLog(SaleGiftLogQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

            pagingEntity.SortField     = filter.PageInfo.SortBy;
            pagingEntity.MaximumRows   = filter.PageInfo.PageSize;
            pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
            var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySaleGiftLog");


            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ProductSysNo",
                                                             DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);

                cmd.CommandText = sqlBuilder.BuildQuerySql();


                DataTable dt = cmd.ExecuteDataTable("IsOnlineShow", typeof(GiftIsOnlineShow));

                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #30
0
        public DataTable QueryProductChannelInfo(ProductChannelInfoQueryFilter queryCriteria, out int totalCount)
        {
            var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductChannelInfo");
            var pagingInfo  = new PagingInfoEntity
            {
                SortField     = queryCriteria.PagingInfo.SortBy,
                StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
                MaximumRows   = queryCriteria.PagingInfo.PageSize
            };

            using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "CP.SysNo DESC"))
            {
                if (queryCriteria.ChannelSysNo > 0)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "CM.Sysno",
                                                                 DbType.Int32, "@ChannelSysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.ChannelSysNo);
                }

                if (queryCriteria.C1SysNo != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "C1.Sysno",
                                                                 DbType.Int32, "@C1SysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.C1SysNo.Value);
                }

                if (queryCriteria.C2SysNo != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "C2.Sysno",
                                                                 DbType.Int32, "@C2SysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.C2SysNo.Value);
                }

                if (queryCriteria.C3SysNo != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "C3.Sysno",
                                                                 DbType.Int32, "@C3SysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.C3SysNo.Value);
                }

                if (queryCriteria.ProductSysNo != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.SysNo",
                                                                 DbType.Int32, "@ProductSysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.ProductSysNo.Value);
                }

                if (!string.IsNullOrEmpty(queryCriteria.ProductID))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.ProductID",
                                                                 DbType.String, "@ProductID",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.ProductID);
                }

                if (!string.IsNullOrEmpty(queryCriteria.Status))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "CP.Status",
                                                                 DbType.String, "@Status",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.Status);
                }

                //渠道商品ID
                if (!string.IsNullOrEmpty(queryCriteria.ChannelProductID))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "CP.SynProductID",
                                                                 DbType.String, "@ChannelProductID",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.ChannelProductID);
                }
                //taobaoSKU
                if (!string.IsNullOrEmpty(queryCriteria.TaobaoSKU))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "CP.TaoBaoSku",
                                                                 DbType.String, "@TaobaoSKU",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.TaobaoSKU);
                }


                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                var enumList = new EnumColumnList {
                    { "Status", typeof(ProductChannelInfoStatus) }
                    , { "IsAppointInventory", typeof(BooleanEnum) }
                    , { "IsUsePromotionPrice", typeof(BooleanEnum) }
                };

                DataTable dt = dataCommand.ExecuteDataTable(enumList);

                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
コード例 #31
0
ファイル: AuditRefundQueryDA.cs プロジェクト: sanlonezhang/ql
        public DataTable Query(AuditRefundQueryFilter filter, out int totalCount)
        {
            DataTable        result     = null;
            PagingInfoEntity pagingInfo = new PagingInfoEntity();

            if (filter.PagingInfo != null)
            {
                MapSortField(filter.PagingInfo);

                pagingInfo.MaximumRows   = filter.PagingInfo.PageSize;
                pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
                pagingInfo.SortField     = filter.PagingInfo.SortBy;
            }

            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAuditRefundList");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SOIncomeBank.SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[SysNo]", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.Id);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.AuditStatus);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[HaveAutoRMA]", DbType.Int32, "@HaveAutoRMA", QueryConditionOperatorType.Equal, filter.ShipRejected);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderType]", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundPayType]", DbType.Int32, "@RefundType", QueryConditionOperatorType.Equal, filter.RefundPayType);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundReason]", DbType.Int32, "@RMAReason", QueryConditionOperatorType.Equal, filter.RMAReasonCode);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderSysNo]", DbType.Int32, "@RMANumber", QueryConditionOperatorType.Equal, filter.RMANumber);

                if (!string.IsNullOrEmpty(filter.OrderNumber))
                {
                    List <int> OrderNumberList  = new List <int>();
                    int[]      OrderNumberArray = Array.ConvertAll <string, int>(filter.OrderNumber.Split('.'),
                                                                                 new Converter <string, int>((source) =>
                    {
                        return(Convert.ToInt32(string.IsNullOrEmpty(source) ? "0" : source));
                    }));
                    OrderNumberList.AddRange(OrderNumberArray);
                    sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "SOIncomeBank.SOSysNo", DbType.Int32, OrderNumberList);
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AuditTimeFrom);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.AuditTimeTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.Status", DbType.Int32, "@RMAStatus", QueryConditionOperatorType.Equal, filter.RMAStatus);
                if (filter.CashRelated)
                {
                    using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
                    {
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.[CashFlag]", DbType.Int32, "@CashFlag", QueryConditionOperatorType.Equal, 0);
                        sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.OR, "RMARefund.[CashFlag]", QueryConditionOperatorType.IsNull);
                    }
                }
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncome.[Status]", DbType.Int32, "@RefundStatus", QueryConditionOperatorType.Equal, filter.RefundStatus);

                //为提高性能改为左匹配
                if (!string.IsNullOrEmpty(filter.CustomerID))
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" Customer.CustomerID like '{0}%'", filter.CustomerID));
                }

                switch (filter.OperationType)
                {
                case OperationSignType.LessThanOrEqual:
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.LessThanOrEqual, filter.RefundAmount);
                    break;

                case OperationSignType.Equal:
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.Equal, filter.RefundAmount);
                    break;

                case OperationSignType.MoreThanOrEqual:
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.MoreThanOrEqual, filter.RefundAmount);
                    break;
                }
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPayExt.[Status]", DbType.Int32, "@WLTRefundStatus", QueryConditionOperatorType.Equal, filter.WLTRefundStatus);

                if (!string.IsNullOrEmpty(filter.PayTypeSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.payTypeSysNo",
                                                                 DbType.Int32, "@payTypeSysNo", QueryConditionOperatorType.Equal, Int32.Parse(filter.PayTypeSysNo));
                }

                if (filter.IsVAT.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.IsVAT",
                                                                 DbType.Int32, "@IsVAT", QueryConditionOperatorType.Equal, filter.IsVAT.Value);
                }

                if (filter.StockSysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
                                                                       @"exists (SELECT TOP 1 1 FROM OverseaOrderManagement.dbo.V_OM_SO_Item ITEM WITH(NOLOCK)
	                      WHERE item.SOSysNo = SOMaster.SysNo
                          AND item.WarehouseNumber = @WareHouseNumber)");

                    cmd.AddInputParameter("@WareHouseNumber", DbType.Int32, filter.StockSysNo.Value);
                }

                cmd.CommandText    = sqlBuilder.BuildQuerySql();
                cmd.CommandTimeout = TimeOut;

                EnumColumnList enumColumns = new EnumColumnList();
                enumColumns.Add("AuditStatus", typeof(RefundStatus));
                enumColumns.Add("OrderType", typeof(RefundOrderType));
                enumColumns.Add("RefundStatus", typeof(SOIncomeStatus));
                enumColumns.Add("RefundPayType", typeof(RefundPayType));
                enumColumns.Add("Source", typeof(NetPaySource));

                result     = cmd.ExecuteDataTable(enumColumns);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
            }
            return(result);
        }
コード例 #32
0
        public DataTable QueryConsignToAccountLogTotalAmt(QueryFilter.PO.ConsignToAccountLogQueryFilter queryFilter)
        {
            DataTable         dt         = new DataTable();
            CustomDataCommand command    = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAccountLogTotalAmt");
            PagingInfoEntity  pagingInfo = new PagingInfoEntity()
            {
                SortField     = queryFilter.PageInfo.SortBy,
                StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
                MaximumRows   = queryFilter.PageInfo.PageSize
            };

            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Consign.SysNo DESC"))
            {
                if (queryFilter != null && (queryFilter.SysNoList == null || queryFilter.SysNoList.Count <= 0))
                {
                    if (queryFilter.CreateTimeFrom.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
                                                                  "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeFrom.Value);
                    }
                    if (queryFilter.CreateTimeTo.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
                                                                  "@CreateTimTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
                    }
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.Product", DbType.String,
                                                              "@ProductName", QueryConditionOperatorType.Like, queryFilter.ProductName);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.SysNo", DbType.Int32,
                                                              "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.StockSysNo", DbType.Int32,
                                                              "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.SettleType", DbType.String,
                                                              "@SettleType", QueryConditionOperatorType.Equal, queryFilter.SettleType == null ? null : queryFilter.SettleType.ToString());
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String,
                                                              "@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.VendorSysNo", DbType.Int32,
                                                              "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);

                    if (queryFilter.ConsignToAccType.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.ConsignToAccType", DbType.String,
                                                                  "@ConsignToAccType", QueryConditionOperatorType.Equal, (int)queryFilter.ConsignToAccType.Value);
                    }

                    if (queryFilter.Status.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.Status", DbType.String,
                                                                  "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
                    }
                }
                else if (queryFilter != null && queryFilter.SysNoList != null && queryFilter.SysNoList.Count > 0)
                {
                    builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND
                                                                      , "Consign.sysno", DbType.Int32, queryFilter.SysNoList);
                }

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CompanyCode", DbType.String,
                                                          "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
                command.CommandText = builder.BuildQuerySql();
                dt = command.ExecuteDataTable();

                return(dt);
            }
        }
コード例 #33
0
        public System.Data.DataTable QueryConsignToAccountLog(QueryFilter.PO.ConsignToAccountLogQueryFilter queryFilter, out int totalCount)
        {
            DataTable         dt         = new DataTable();
            CustomDataCommand command    = DataCommandManager.CreateCustomDataCommandFromConfig("QueryConsinToAccountLog");
            PagingInfoEntity  pagingInfo = new PagingInfoEntity()
            {
                SortField     = queryFilter.PageInfo.SortBy,
                StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
                MaximumRows   = queryFilter.PageInfo.PageSize
            };

            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Consign.SysNo DESC"))
            {
                if (pagingInfo != null && (queryFilter.SysNoList == null || queryFilter.SysNoList.Count <= 0))
                {
                    if (queryFilter.CreateTimeFrom.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
                                                                  "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeFrom.Value);
                    }
                    if (queryFilter.CreateTimeTo.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
                                                                  "@CreateTimTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
                    }
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.Product", DbType.String,
                                                              "@ProductName", QueryConditionOperatorType.Like, queryFilter.ProductName);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.SysNo", DbType.Int32,
                                                              "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.StockSysNo", DbType.Int32,
                                                              "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.SettleType", DbType.String,
                                                              "@SettleType", QueryConditionOperatorType.Equal, queryFilter.SettleType == null ? null : queryFilter.SettleType.ToString());
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String,
                                                              "@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.VendorSysNo", DbType.Int32,
                                                              "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);

                    if (queryFilter.ConsignToAccType.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.ConsignToAccType", DbType.String,
                                                                  "@ConsignToAccType", QueryConditionOperatorType.Equal, (int)queryFilter.ConsignToAccType.Value);
                    }

                    if (queryFilter.Status.HasValue)
                    {
                        builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.Status", DbType.String,
                                                                  "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
                    }
                }
                else if (queryFilter != null && queryFilter.SysNoList != null && queryFilter.SysNoList.Count > 0)
                {
                    builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND
                                                                      , "Consign.sysno", DbType.Int32, queryFilter.SysNoList);
                }

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CompanyCode", DbType.String,
                                                          "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
                command.CommandText = builder.BuildQuerySql();

                EnumColumnList enumList = new EnumColumnList
                {
                    { "Status", typeof(ConsignToAccountLogStatus) },
                    { "ReferenceType", typeof(ConsignToAccountType) },
                    { "SettleType", typeof(SettleType) }
                };
                dt         = command.ExecuteDataTable(enumList);
                totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                if (null != dt && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows[i]["InDate"] != null && dt.Rows[i]["InDate"] != DBNull.Value)
                        {
                            dt.Rows[i]["InDateString"] = string.Format("{0}[{1}]", EnumHelper.GetDisplayText((ConsignToAccountType)Enum.Parse(typeof(ConsignToAccountType), dt.Rows[i]["ReferenceType"].ToString())), dt.Rows[i]["InDate"]);
                        }
                    }
                }
            }
            return(dt);
        }
コード例 #34
0
ファイル: PayCardDA.cs プロジェクト: jeanmahai/28helpmate
        /// <summary>
        /// 查询充值卡
        /// </summary>
        /// <param name="pageIndex">取第几页</param>
        /// <param name="pageSize">每页显示几条</param>
        /// <param name="category">卡类型</param>
        /// <param name="status">状态</param>
        /// <param name="beginTime">有效期起</param>
        /// <param name="endTime">有效期止</param>
        /// <returns></returns>
        public static PageList<List<PayCard>> QueryPayCard(int pageIndex, int pageSize, int category, int status, DateTime beginTime, DateTime endTime)
        {
            PagingInfoEntity page = new PagingInfoEntity();
            page.MaximumRows = pageSize;
            page.StartRowIndex = pageIndex * pageSize;
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("PayCard_Query");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                if (category > 0)
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CategorySysNo", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, category);
                if (status >= 0)
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "BeginTime", DbType.DateTime, "@BeginTime", QueryConditionOperatorType.MoreThanOrEqual, beginTime);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndTime", DbType.DateTime, "@EndTime", QueryConditionOperatorType.LessThanOrEqual, endTime);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                List<PayCard> result = cmd.ExecuteEntityList<PayCard>();

                int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return new PageList<List<PayCard>>(result, pageIndex, pageSize, totalCount);
            }
        }