Exemple #1
0
        /// <summary>
        /// 查询邮件短信模板
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <returns></returns>
        public static QueryResult<EmailAndSMSTemplates> QueryEmailAndSMSTemplates(EmailAndSMSTemplatesQueryFilter filter)
        {
            QueryResult<EmailAndSMSTemplates> result = new QueryResult<EmailAndSMSTemplates>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryEmailAndSMSTemplates");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
                    "@Status1", QueryConditionOperatorType.NotEqual, CommonStatus.Deleted);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32,
                    "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Category", DbType.String,
                    "@Category", QueryConditionOperatorType.Like, filter.Category);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Templates", DbType.String,
                    "@Templates", QueryConditionOperatorType.Like, filter.Templates);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
                    "@Status", QueryConditionOperatorType.Equal, filter.Status);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.ResultList = cmd.ExecuteEntityList<EmailAndSMSTemplates>();
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return result;
            }
        }
        /// <summary>
        /// 查询日志
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <returns></returns>
        public static QueryResult<Logs> QueryLogs(LogsQueryFilter filter)
        {
            QueryResult<Logs> result = new QueryResult<Logs>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryLogs");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Classes", DbType.Int32,
                    "@Classes", QueryConditionOperatorType.Like, filter.Classes);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Section", DbType.Int32,
                    "@Section", QueryConditionOperatorType.Like, filter.Section);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Family", DbType.Int32,
                    "@Family", QueryConditionOperatorType.Like, filter.Family);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RefenceSysNo", DbType.Int32,
                    "@RefenceSysNo", QueryConditionOperatorType.Like, filter.RefenceSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String,
                    "@Contents", QueryConditionOperatorType.Like, filter.Contents);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.ResultList = cmd.ExecuteEntityList<Logs>();
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return result;
            }
        }
Exemple #3
0
        /// <summary>
        /// 查询奖品
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <returns></returns>
        public static QueryResult<Gift> QueryGifts(GiftQueryFilter filter)
        {
            QueryResult<Gift> result = new QueryResult<Gift>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGifts");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
                    "@Status1", QueryConditionOperatorType.NotEqual, CommonStatus.Deleted);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32,
                    "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GiftName", DbType.String,
                    "@GiftName", QueryConditionOperatorType.Like, filter.GiftName);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GiftID", DbType.String,
                    "@GiftID", QueryConditionOperatorType.Like, filter.GiftID);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Descriptions", DbType.String,
                    "@Descriptions", QueryConditionOperatorType.Like, filter.Descriptions);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "MarketPrice", DbType.Decimal,
                    "@MarketPrice", QueryConditionOperatorType.Like, filter.MarketPrice);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
                    "@Status", QueryConditionOperatorType.Equal, filter.Status);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.ResultList = cmd.ExecuteEntityList<Gift>();
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return result;
            }
        }
Exemple #4
0
        /// <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;
            }
        }
Exemple #5
0
        /// <summary>
        /// 日销售情况报表
        /// </summary>
        /// <param name="filter">条件</param>
        /// <returns></returns>
        public static StatementResult<ArrayList> DaySalesData(DaySalesFilter filter)
        {
            StatementResult<ArrayList> result = new StatementResult<ArrayList>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("DaySalesData");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate DESC"))
            {
                DateTime beginDate = DateTime.Parse(filter.Date);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
                    "@BeginLogDate", QueryConditionOperatorType.MoreThanOrEqual, beginDate);
                DateTime endDate = beginDate.AddDays(1);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
                    "@EndLogDate", QueryConditionOperatorType.LessThan, endDate);

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

                return result;
            }
        }
        /// <summary>
        /// 查询大于等于30天没登录过的用户
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <returns></returns>
        public static StatementResult<ArrayList> QueryThirtyDayNotLoginCustomers(QueryCategoryCustomersFilter filter)
        {
            StatementResult<ArrayList> result = new StatementResult<ArrayList>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryThirtyDayNotLoginCustomers");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "CustomerID DESC"))
            {
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return result;
            }
        }
Exemple #7
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;
            }
        }
Exemple #8
0
        /// <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);
            }
        }
Exemple #9
0
        /// <summary>
        /// 查询投票列表
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable QueryPollList(PollQueryFilter 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("Poll_QueryPollList");

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

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

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
                if (filter.PageType.HasValue && filter.PageID.HasValue)
                {
                    if (filter.PageType == 2 && filter.PageID.Value > 0)
                    {
                        sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.PageID IN ( SELECT DISTINCT C2SysNo  FROM OverseaECommerceManagement.dbo.V_EM_EC_Category WHERE (C2SysNo IS NOT NULL OR C3SysNo IS NOT NULL) AND (C1SysNo =" + filter.PageID + " OR C2SysNo = " + filter.PageID + ") AND CompanyCode=" + filter.CompanyCode + "  )");
                    }

                    else if (filter.PageType == 3 && filter.PageID.Value > 0)
                    {
                        sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.PageID IN ( SELECT DISTINCT C3SysNo  FROM OverseaECommerceManagement.dbo.V_EM_EC_Category WHERE (C2SysNo IS NOT NULL OR C3SysNo IS NOT NULL) AND (C1SysNo =" + filter.PageID + " OR C2SysNo = " + filter.PageID + " OR C3SysNo = " + filter.PageID + ") AND CompanyCode=" + filter.CompanyCode + "  )");
                    }
                    else if (filter.PageID > 0)
                    {
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.PageID", DbType.String, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
                    }
                }
                if (filter.UserDefined == YNStatus.Yes)
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.SysNo in (SELECT distinct PollSysno FROM OverseaECommerceManagement.dbo.Poll_ItemGroup with (nolock) WHERE CompanyCode=" + filter.CompanyCode + " AND Type = 'A')");
                }

                else if (filter.UserDefined == YNStatus.No)
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "P.SysNo not in (SELECT distinct PollSysno FROM OverseaECommerceManagement.dbo.Poll_ItemGroup with (nolock) WHERE CompanyCode=" + filter.CompanyCode + " AND Type = 'A')");
                }

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

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


                cmd.CommandText = sqlBuilder.BuildQuerySql();
                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("Status", typeof(ADStatus));
                enumList.Add("UserDefined", typeof(YNStatus));

                var dt = cmd.ExecuteDataTable(enumList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
Exemple #10
0
        private void AddReceiveByVendorParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
        {
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "invoice.CompanyCode",
                DbType.AnsiStringFixedLength,
                "@CompanyCode",
                QueryConditionOperatorType.Equal,
                filter.CompanyCode);
            if (filter.VendorSysNo.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "invoice.VendorNumber",
                    DbType.Int32,
                    "@VendorSysNo",
                    QueryConditionOperatorType.Equal,
                    filter.VendorSysNo);
            }
            if (filter.PMUserSysNo.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ruleinfo.PM",
                    DbType.Int32,
                    "@PMUserSysNo",
                    QueryConditionOperatorType.Equal,
                    filter.PMUserSysNo);
            }
            if (!string.IsNullOrEmpty(filter.EIMSType))
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "invoice.EIMSType",
                    DbType.String,
                    "@EIMSType",
                    QueryConditionOperatorType.Equal,
                    filter.EIMSType);
            }
            if (filter.ExpiredDays.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "DATEDIFF(DAY,invoice.DueDate,GETDATE())",
                    DbType.Int32,
                    "@ExpriedDays",
                    QueryConditionOperatorType.MoreThan,
                    filter.ExpiredDays);
            }

            cmd.AddOutParameter("@SumNoReceivedAmount", DbType.Double, sizeof(double));
            cmd.AddOutParameter("@SumOverDueAmount", DbType.Double, sizeof(double));
            cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
            cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
            cmd.CommandText = sb.BuildQuerySql();
        }
Exemple #11
0
 private void AddReceiveByYearParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
 {
     sb.ConditionConstructor.AddCondition(
         QueryConditionRelationType.AND,
         "accbyrule.CompanyCode",
         DbType.AnsiStringFixedLength,
         "@CompanyCode",
         QueryConditionOperatorType.Equal,
         filter.CompanyCode);
     if (filter.VendorSysNo.HasValue)
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "accbyrule.VendorNumber",
             DbType.Int32,
             "@VendorSysNo",
             QueryConditionOperatorType.Equal,
             filter.VendorSysNo);
     }
     if (!string.IsNullOrEmpty(filter.EIMSType))
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "accbyrule.EIMSType",
             DbType.String,
             "@EIMSType",
             QueryConditionOperatorType.Equal,
             filter.EIMSType);
     }
     if (filter.Year.HasValue)
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "substring(accbyrule.AccruePeriod,0,5)",
             DbType.String,
             "@Year",
             QueryConditionOperatorType.Equal,
             filter.Year);
     }
     cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
     cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
     cmd.CommandText = sb.BuildQuerySql();
 }
Exemple #12
0
        public DataTable Query(PostIncomeQueryFilter query, out int totalCount)
        {
            DataTable        result     = null;
            PagingInfoEntity pagingInfo = new PagingInfoEntity();

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

                pagingInfo.MaximumRows   = query.PagingInfo.PageSize;
                pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
                pagingInfo.SortField     = query.PagingInfo.SortBy;
            }
            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPostIncomeList");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, pagingInfo, "a.SysNo desc"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SOSysNo",
                                                             DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, query.SOSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeAmt",
                                                             DbType.Decimal, "@IncomeAmt", QueryConditionOperatorType.Equal, query.IncomeAmt);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
                                                             DbType.Date, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.CreateDateFrom);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
                                                             DbType.Date, "@CreateDateTo", QueryConditionOperatorType.LessThan, query.CreateDateTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
                                                             DbType.Date, "@IncomeDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.IncomeDateFrom);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
                                                             DbType.Date, "@IncomeDateTo", QueryConditionOperatorType.LessThan, query.IncomeDateTo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayBank",
                                                             DbType.String, "@PayBank", QueryConditionOperatorType.Like, query.PayBank);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeBank",
                                                             DbType.String, "@IncomeBank", QueryConditionOperatorType.Like, query.IncomeBank);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.DisplayName",
                                                             DbType.String, "@CreateUser", QueryConditionOperatorType.Like, query.CreateUser);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "e.DisplayName",
                                                             DbType.String, "@AuditUser", QueryConditionOperatorType.Like, query.AuditUser);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayUser",
                                                             DbType.String, "@PayUser", QueryConditionOperatorType.Like, query.PayUser);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
                                                             DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);

                if (!string.IsNullOrEmpty(query.ConfirmedSOSysNoList))
                {
                    sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.SysNo",
                                                                         QueryConditionOperatorType.In, string.Format(@"SELECT a.PostIncomeSysNo FROM
                    [OverseaInvoiceReceiptManagement].[dbo].[PostIncomeConfirm] a WITH(NOLOCK)
                    WHERE a.ConfirmedSoSysNo IN ({0}) AND a.Status <> 'C'"    , query.ConfirmedSOSysNoList.Replace('.', ',')));
                }
                if (query.HandleStatus != null)
                {
                    switch (query.HandleStatus.Value)
                    {
                    case PostIncomeHandleStatusUI.UnConfirmed:
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
                                                                     DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.NotEqual, PostIncomeStatus.Confirmed);
                        break;

                    case PostIncomeHandleStatusUI.UnHandled:
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
                                                                     DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.WaitingHandle);
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
                                                                     DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.Equal, PostIncomeStatus.Confirmed);
                        break;

                    case PostIncomeHandleStatusUI.Handled:
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
                                                                     DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.Handled);
                        break;

                    default:
                        break;
                    }
                }
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList enumColumns = new EnumColumnList();
                enumColumns.Add("HandleStatus", typeof(PostIncomeHandleStatus));
                enumColumns.Add("ConfirmStatus", typeof(PostIncomeStatus));

                result     = dataCommand.ExecuteDataTable(enumColumns);
                totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
            }
            return(result);
        }
Exemple #13
0
        public static List <ProductCategoryQueryBasicInfo> QueryProductCategory(ProductCategoryQueryFilter queryCriteria, out int totalCount)
        {
            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductCategory");
            var pagingInfo = new PagingInfoEntity
            {
                StartRowIndex = queryCriteria.PageIndex * queryCriteria.PageSize,
                MaximumRows   = queryCriteria.PageSize,
                SortField     = queryCriteria.SortFields,
            };

            using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, string.IsNullOrEmpty(pagingInfo.SortField) ? pagingInfo.SortField : "P.ProductSysNo DESC"))
            {
                if (!String.IsNullOrEmpty(queryCriteria.ProductSysNo))
                {
                    dataCommand.AddInputParameter("@ProductID", DbType.String, queryCriteria.ProductSysNo);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.ProductSysNo",
                                                                 DbType.String, "@ProductID",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.ProductSysNo);
                }
                if (!String.IsNullOrEmpty(queryCriteria.ProductName))
                {
                    dataCommand.AddInputParameter("@ProductName", DbType.String, queryCriteria.ProductName);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.ProductName",
                                                                 DbType.String, "@ProductName",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.ProductName);
                }
                if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo))
                {
                    dataCommand.AddInputParameter("@AttachmentID", DbType.String, queryCriteria.SysNo);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.AttachmentSysNo",
                                                                 DbType.String, "@ProductAttachmentSysNo",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.ProductAttachmentSysNo);
                }
                //if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo))
                //{
                //    dataCommand.AddInputParameter("@AttachmentName", DbType.String, queryCriteria.AttachmentName);
                //    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //        "P.AttachmentName",
                //        DbType.String, "@AttachmentName",
                //        QueryConditionOperatorType.Like,
                //        queryCriteria.AttachmentName);
                //}
                if (!String.IsNullOrEmpty(queryCriteria.EditUser))
                {
                    dataCommand.AddInputParameter("@EditUser", DbType.String, queryCriteria.EditUser);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.InUser",
                                                                 DbType.String, "@EditUser",
                                                                 QueryConditionOperatorType.Like,
                                                                 queryCriteria.EditUser);
                }
                //sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateEnd, queryCriteria.InDateStart);
                if (queryCriteria.InDateStart != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "P.InDate",
                        DbType.DateTime,
                        "@InDateStart",
                        QueryConditionOperatorType.MoreThanOrEqual,
                        queryCriteria.InDateStart
                        );
                }
                if (queryCriteria.InDateEnd != null)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "P.InDate",
                        DbType.DateTime,
                        "@InDateEnd",
                        QueryConditionOperatorType.LessThanOrEqual,
                        queryCriteria.InDateEnd
                        );
                }
                if (queryCriteria.SellerSysNo.HasValue)
                {
                    dataCommand.AddInputParameter("@SellerSysNo", DbType.String, queryCriteria.SellerSysNo);
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "P.SellerID",
                                                                 DbType.String, "@SellerSysNo",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.SellerSysNo);
                }

                dataCommand.CommandText = sqlBuilder.BuildQuerySql();

                List <ProductCategoryQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductCategoryQueryBasicInfo>();

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

                return(list);
            }
        }
Exemple #14
0
        /// <summary>
        /// 查询仓库
        /// </summary>
        /// <returns></returns>
        public virtual DataTable QueryWarehouse(WarehouseQueryFilter filter, out int totalCount)
        {
            PagingInfoEntity pagingEntity = new PagingInfoEntity();

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

            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryWarehouseListByCondition");//说明:EC_Central产品化时 调用的 SQL为:Inventory_QueryWarehouse 现在是中蛋定制化 不存在渠道仓库所以保持同IPP一致

            using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo ASC"))
            {
                bool isSysNo = false;
                //编号格式验证
                if (filter.SysNo != null && Regex.IsMatch(filter.SysNo, @"^[,\. ]*\d+[\d,\. ]*$"))
                {
                    filter.SysNo = String.Join(",", filter.SysNo.Split(new char[] { '.', ',', ' ' }, StringSplitOptions.RemoveEmptyEntries));
                    isSysNo      = true;
                }
                else
                {
                    filter.SysNo = null;
                }

                if (filter.WarehouseID != null && Regex.IsMatch(filter.WarehouseID, @"^[, ]*\w+[\w-#, ]*$"))
                {
                    filter.WarehouseID = "'" + String.Join("','", filter.WarehouseID.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)) + "'";
                    isSysNo            = true;
                }
                else
                {
                    filter.WarehouseID = null;
                }

                if (isSysNo)
                {
                    sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                    sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
                                                                 "a.SysNo", QueryConditionOperatorType.In, filter.SysNo);

                    sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
                                                                 "a.StockID", QueryConditionOperatorType.In, filter.WarehouseID);
                    sb.ConditionConstructor.EndGroupCondition();
                }

                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                     "a.StockName", DbType.String, "@WarehouseName",
                                                     QueryConditionOperatorType.LeftLike, filter.WarehouseName);


                //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //        "a.WarehouseOwnerSysNo", DbType.Int32, "@WarehouseOwnerSysNo",
                //        QueryConditionOperatorType.Equal, filter.OwnerSysNo);


                //if (!filter.OwnerSysNo.HasValue && filter.OwnerSysNo!=0)
                //{
                //    sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND
                //        , " ((a.WarehouseOwnerSysNo IN ( SELECT a.SysNo FROM   OverseaInventoryManagement.dbo.WarehouseOwner a WITH(NOLOCK)WHERE  a.OwnerType=1)) OR a.WarehouseOwnerSysNo IS NULL)");
                //}

                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                     "a.[Status]", DbType.Int32, "@WarehouseStatus",
                                                     QueryConditionOperatorType.Equal, filter.WarehouseStatus);
                //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //        "a.[WarehouseType]", DbType.Int32, "@WarehouseType",
                //        QueryConditionOperatorType.Equal, filter.WarehouseType);
                //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                //        "a.[WarehouseArea]", DbType.Int32, "@WarehouseArea",
                //        QueryConditionOperatorType.Equal, filter.WarehouseArea);

                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                     "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
                                                     QueryConditionOperatorType.Equal, filter.CompanyCode);
                EnumColumnList enumColumnList = new EnumColumnList();
                enumColumnList.Add("WarehouseStatus", typeof(ValidStatus));

                cmd.CommandText = sb.BuildQuerySql();
                var resultData = cmd.ExecuteDataTable(enumColumnList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(resultData);
            }
        }
        private void AddParameter4GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
        {
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "a.OutTime"
                                                         , DbType.DateTime
                                                         , "@OutDateFrom"
                                                         , QueryConditionOperatorType.MoreThanOrEqual
                                                         , filter.OutDateFrom);

            if (filter.OutDateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "a.OutTime"
                                                             , DbType.DateTime
                                                             , "@OutDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , filter.OutDateTo.Value.AddDays(1).AddMinutes(-1));
            }

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "sl.InvoiceCreateDate"
                                                         , DbType.DateTime
                                                         , "@InvoiceCreateTimeFrom"
                                                         , QueryConditionOperatorType.MoreThanOrEqual
                                                         , filter.InvoiceDateFrom);

            if (filter.InvoiceDateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "sl.InvoiceCreateDate"
                                                             , DbType.DateTime
                                                             , "@InvoiceCreateTimeTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1));
            }

            if (!string.IsNullOrEmpty(filter.OrderID))
            {
                List <string> orderIDList = new List <string>();
                foreach (string item in filter.OrderID.Split('.'))
                {
                    orderIDList.Add(item.Trim());
                }

                sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "a.SOID", DbType.String, orderIDList);
            }

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

            int     intTry;
            decimal decimalTry;

            if (!string.IsNullOrEmpty(filter.CustomerName))
            {
                if (int.TryParse(filter.CustomerName, out intTry) ||
                    decimal.TryParse(filter.CustomerName, out decimalTry))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "ct.CustomerName"
                                                                 , DbType.String
                                                                 , "@CustomerName"
                                                                 , QueryConditionOperatorType.Equal
                                                                 , filter.CustomerName);
                }
                else
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "ct.CustomerName"
                                                                 , DbType.String
                                                                 , "@CustomerName"
                                                                 , QueryConditionOperatorType.LeftLike
                                                                 , filter.CustomerName);
                }
            }

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

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "a.CompanyCode"
                                                         , DbType.AnsiStringFixedLength
                                                         , "@CompanyCode"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.CompanyCode);
        }
Exemple #16
0
        /// <summary>
        /// 分页查询商品
        /// </summary>
        /// <param name="queryFilter"></param>
        /// <returns></returns>
        public static QueryResult <ProductCommonInfo> QueryCommonProduct(ProducCommonQueryFilter queryFilter)
        {
            CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCommonProduct");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "P.[SysNo] ASC" : queryFilter.SortFields))
            {
                if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.[SysNo]", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
                }
                if (!string.IsNullOrEmpty(queryFilter.BrandSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[BrandSysNo]", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo);
                }
                if (!string.IsNullOrEmpty(queryFilter.ProductID))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Like, queryFilter.ProductID);
                }
                if (!string.IsNullOrEmpty(queryFilter.ProductTitle))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductTitle]", DbType.String, "@ProductTitle", QueryConditionOperatorType.Like, queryFilter.ProductTitle);
                }

                if (!string.IsNullOrEmpty(queryFilter.FrontCategorySysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[FrontCategorySysNo]", DbType.Int32
                                                                 , "@FrontCategorySysNo", QueryConditionOperatorType.Like, Convert.ToInt32(queryFilter.FrontCategorySysNo));
                }

                if (!string.IsNullOrEmpty(queryFilter.Status))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
                }
                if (queryFilter.CreateTimeBegin != null && queryFilter.CreateTimeBegin != new DateTime())
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.ToString("yyyy-MM-dd"));
                }
                if (queryFilter.CreateTimeEnd != null && queryFilter.CreateTimeEnd != new DateTime())
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeEnd", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeEnd.AddDays(1).ToString("yyyy-MM-dd"));
                }
                if (!string.IsNullOrEmpty(queryFilter.GroupName))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PG.[ProductName]", DbType.String, "@GroupName", QueryConditionOperatorType.Like, queryFilter.GroupName);
                }
                if (!string.IsNullOrEmpty(queryFilter.ProductTradeType))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[ProductTradeType]", DbType.String, "@ProductTradeType", QueryConditionOperatorType.Equal, queryFilter.ProductTradeType);
                }

                command.CommandText = sqlBuilder.BuildQuerySql();
                List <ProductCommonInfo> resultList = command.ExecuteEntityList <ProductCommonInfo>();
                int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));

                return(new QueryResult <ProductCommonInfo>()
                {
                    PageInfo = new PageInfo()
                    {
                        PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
                    }, ResultList = resultList
                });
            }
        }
        public static QueryResponseDTO <bool> DeleteProductInfo(ProductDTO dto)
        {
            QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();

            response.ResultEntity = false;


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

            foreach (var model in dto.ProductModel)
            {
                using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                           dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
                {
                    if (null != dto)
                    {
                        if (!string.IsNullOrEmpty(model.ProductID))
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "ProductID", DbType.AnsiString, "@ProductID", QueryConditionOperatorType.Equal, model.ProductID);
                        }

                        if (model.Stock > 0)
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "Stock", DbType.Int32, "@Stock", QueryConditionOperatorType.MoreThanOrEqual, model.Stock);
                        }

                        if (!string.IsNullOrEmpty(model.CategoryID))
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "CategoryID", DbType.AnsiString, "@CategoryID", QueryConditionOperatorType.Equal, model.CategoryID);
                        }

                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "IsPublish", DbType.Boolean, "@IsPublish", QueryConditionOperatorType.Equal, model.IsPublish);

                        if (model.OriginalPrice > 0)
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "OriginalPrice", DbType.AnsiString, "@OriginalPrice", QueryConditionOperatorType.MoreThanOrEqual, model.OriginalPrice);
                        }

                        if (dto.LastEditDate != null)
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "LastEditDate", DbType.DateTime, "@LastEditDate", QueryConditionOperatorType.MoreThanOrEqual, model.LastEditDate);
                        }

                        if (model.Discount > 0)
                        {
                            sqlBuilder.ConditionConstructor
                            .AddCondition(QueryConditionRelationType.AND, "Discount", DbType.Int32, "@Discount", QueryConditionOperatorType.MoreThanOrEqual, model.Discount);
                        }
                    }
                    //QueryData
                    dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                    var updateRows = dataCommand.ExecuteNonQuery();
                    if (updateRows > 0)
                    {
                        response.ResultEntity = true;
                        response.Code         = ResponseStaticModel.Code.OK;
                        response.Message      = ResponseStaticModel.Message.OK;
                    }
                    else
                    {
                        response.ResultEntity = false;
                        response.Code         = ResponseStaticModel.Code.FAILED;
                        response.Message      = ResponseStaticModel.Message.FAILED;
                    }
                }
            }
            return(response);
        }
        public static QueryResponseDTO <bool> DeleteTransactionInfo(TransactionDTO dto)
        {
            QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();

            response.ResultEntity = false;


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

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
            {
                if (null != dto)
                {
                    if (!string.IsNullOrEmpty(dto.TransactionNumber))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "TransactionNumber", DbType.AnsiString, "@TransactionNumber", QueryConditionOperatorType.Equal, dto.TransactionNumber);
                    }

                    if (!string.IsNullOrEmpty(dto.FromUser))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "FromUser", DbType.AnsiString, "@FromUser", QueryConditionOperatorType.Equal, dto.FromUser);
                    }

                    if (!string.IsNullOrEmpty(dto.ToUser))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ToUser", DbType.AnsiString, "@ToUser", QueryConditionOperatorType.Equal, dto.ToUser);
                    }

                    if (dto.State > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "State", DbType.Int32, "@State", QueryConditionOperatorType.Equal, dto.State);
                    }

                    if (dto.Status > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, dto.Status);
                    }

                    if (dto.Number > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "Number", DbType.Int32, "@Number", QueryConditionOperatorType.Equal, dto.Number);
                    }
                }
                //QueryData
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                var updateRows = dataCommand.ExecuteNonQuery();
                if (updateRows > 0)
                {
                    response.ResultEntity = true;
                    response.Code         = ResponseStaticModel.Code.OK;
                    response.Message      = ResponseStaticModel.Message.OK;
                }
                else
                {
                    response.ResultEntity = false;
                    response.Code         = ResponseStaticModel.Code.FAILED;
                    response.Message      = ResponseStaticModel.Message.FAILED;
                }
            }
            return(response);
        }
Exemple #19
0
        /// <summary>
        /// 查询商品库存调整单列表
        /// </summary>
        /// <param name="queryCriteria"></param>
        /// <returns></returns>
        public static QueryResult <ProductStockAdjustViewInfo> QueryProductStockAdjustmentList(ProductStockAdjustListQueryFilter queryFilter)
        {
            CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductStockAdjustmentList");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "ps.SysNo DESC" : queryFilter.SortFields))
            {
                //Set SQL WHERE Condition:
                #region Build Condition:

                if (queryFilter.VendorSysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value);
                }

                var sysno = 0;
                if (!string.IsNullOrEmpty(queryFilter.SysNo) && int.TryParse(queryFilter.SysNo, out sysno))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, sysno);
                }
                if (queryFilter.Status.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.Status", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
                }

                if (queryFilter.StockSysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.StockSysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value);
                }
                int productSysNo = 0;
                if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) && int.TryParse(queryFilter.ProductSysNo, out productSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" Exists(SELECT TOP 1 1 FROM ECStore.dbo.Product_StockAdjustDetail (NOLOCK) WHERE ProductSysNo = {0} AND AdjustSysNo = ps.SysNo)", productSysNo));
                }

                if (!string.IsNullOrEmpty(queryFilter.CreateDateFrom))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.InDate", DbType.DateTime, "@DateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, Convert.ToDateTime(queryFilter.CreateDateFrom));
                }
                if (!string.IsNullOrEmpty(queryFilter.CreateDateTo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.InDate", DbType.DateTime, "@DateTimeTo", QueryConditionOperatorType.LessThan, Convert.ToDateTime(queryFilter.CreateDateTo).AddDays(1));
                }
                if (!string.IsNullOrEmpty(queryFilter.AuditDateFrom))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.AuditDate", DbType.DateTime, "@AuditTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, Convert.ToDateTime(queryFilter.AuditDateFrom));
                }
                if (!string.IsNullOrEmpty(queryFilter.AuditDateTo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ps.AuditDate", DbType.DateTime, "@AuditTimeTo", QueryConditionOperatorType.LessThan, Convert.ToDateTime(queryFilter.AuditDateTo).AddDays(1));
                }
                #endregion

                command.CommandText = sqlBuilder.BuildQuerySql();
                List <ProductStockAdjustViewInfo> resultList = command.ExecuteEntityList <ProductStockAdjustViewInfo>();
                int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));

                return(new QueryResult <ProductStockAdjustViewInfo>()
                {
                    PageInfo = new PageInfo()
                    {
                        PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
                    }, ResultList = resultList
                });
            }
        }
Exemple #20
0
        public DataTable Query(AdvertisingQueryFilter 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("AdvertisingQuery");

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

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

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

                string sql = string.Empty;
                if (filter.OperateDateFrom.HasValue)
                {
                    sql = string.Format(@" Link.InDate >= '{0}'", filter.OperateDateFrom);
                }
                if (filter.OperateDateTo.HasValue)
                {
                    sql = string.Format(@" Link.InDate <= '{0}' or Link.EditDate <='{0}'",
                                        filter.OperateDateTo.Value.AddDays(1).AddSeconds(-1));
                }
                if (filter.OperateDateFrom.HasValue && filter.OperateDateTo.HasValue)
                {
                    sql = string.Format(@" ((Link.InDate >= '{0}' AND Link.InDate <='{1}') OR (Link.EditDate >= '{0}' AND Link.EditDate <='{1}'))",
                                        filter.OperateDateFrom, filter.OperateDateTo.Value.AddDays(1).AddSeconds(-1));
                }

                if (!string.IsNullOrEmpty(sql))
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, sql);
                }

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

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


                object _type;
                if (filter.Type != null && EnumCodeMapper.TryGetCode(filter.Type, out _type))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Link.Type",
                                                                 DbType.String,
                                                                 "@Type",
                                                                 QueryConditionOperatorType.Like,
                                                                 _type);
                }

                cmd.CommandText = sqlBuilder.BuildQuerySql();

                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("Type", typeof(AdvertisingType));
                enumList.Add("Status", typeof(ValidStatus));

                var dt = cmd.ExecuteDataTable(enumList);
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
Exemple #21
0
        public virtual DataTable Query(OrderCheckItemQueryFilter 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("GetOrderCheckItem");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
            {
                if (string.Compare(queryCriteria.ReferenceType, "PC3") == 0)
                {
                    if (queryCriteria.C1SysNo.HasValue)
                    {
                        string subQuerySQLC1 = @"SELECT [Category3Sysno]
                        FROM  OverseaContentManagement.dbo.V_CM_CategoryInfo with(nolock)
                        WHERE [Category1Sysno] = " + queryCriteria.C1SysNo;

                        sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
                                                                             "a.Description",
                                                                             QueryConditionOperatorType.In,
                                                                             subQuerySQLC1
                                                                             );
                    }
                    else if (queryCriteria.C2SysNo.HasValue)
                    {
                        string subQuerySQLC1 = @"SELECT [Category3Sysno]
                        FROM  OverseaContentManagement.dbo.V_CM_CategoryInfo with(nolock)
                        WHERE [Category2Sysno] = " + queryCriteria.C2SysNo;

                        sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
                                                                             "a.Description",
                                                                             QueryConditionOperatorType.In,
                                                                             subQuerySQLC1
                                                                             );
                    }
                }
                if (!string.IsNullOrEmpty(queryCriteria.ReferenceTypeIn) &&
                    string.Compare(queryCriteria.ReferenceTypeIn, "'DT11','DT12'") == 0)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                                 "a.sysno",
                                                                 DbType.Int32, "@sysno",
                                                                 QueryConditionOperatorType.Equal,
                                                                 queryCriteria.SysNo);
                }
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "a.ReferenceType",
                                                             DbType.String, "@ReferenceType",
                                                             QueryConditionOperatorType.Equal,
                                                             queryCriteria.ReferenceType);

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "a.ReferenceContent",
                                                             DbType.String, "@ReferenceContent",
                                                             QueryConditionOperatorType.Equal,
                                                             queryCriteria.ReferenceContent);

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

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                             "a.Description",
                                                             DbType.String, "@Description",
                                                             QueryConditionOperatorType.Equal,
                                                             queryCriteria.Description);

                sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
                                                                     "a.ReferenceType",
                                                                     QueryConditionOperatorType.In,
                                                                     queryCriteria.ReferenceTypeIn);
                sqlBuilder.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
                    QueryConditionOperatorType.Equal,
                    queryCriteria.CompanyCode);
                cmd.CommandText = sqlBuilder.BuildQuerySql();
                DataTable dt = cmd.ExecuteDataTable(4, typeof(OrderCheckStatus));
                totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
Exemple #22
0
        public static QueryResult <CouponCode> QueryCouponCodeList(CouponCodeQueryFilter queryFilter)
        {
            CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponCode");

            using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "c.SysNo ASC" : queryFilter.SortFields))
            {
                //Set SQL WHERE Condition:

                if (queryFilter.CouponSysNo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponSysNo", DbType.Int32, "@CouponSysNo", QueryConditionOperatorType.Equal, queryFilter.CouponSysNo);
                }

                if (!string.IsNullOrWhiteSpace(queryFilter.CouponCode))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponCode", DbType.String, "@CouponCode", QueryConditionOperatorType.Like, queryFilter.CouponCode);
                }

                if (queryFilter.InDateFrom.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.InDate", DbType.DateTime, "@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InDateFrom.Value.Date);
                }

                if (queryFilter.InDateTo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.InDate", DbType.DateTime, "@InDateTo", QueryConditionOperatorType.LessThan, queryFilter.InDateTo.Value.Date.AddDays(1));
                }

                if (queryFilter.BeginDateFrom.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.BeginDateFrom.Value.Date);
                }

                if (queryFilter.BeginDateTo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, queryFilter.BeginDateTo.Value.Date.AddDays(1));
                }

                if (queryFilter.EndDateFrom.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.EndDateFrom.Value.Date);
                }

                if (queryFilter.EndDateTo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDateTo.Value.Date.AddDays(1));
                }

                command.CommandText = sqlBuilder.BuildQuerySql();
                List <CouponCode> resultList = command.ExecuteEntityList <CouponCode>();
                int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));

                return(new QueryResult <CouponCode>()
                {
                    PageInfo = new PageInfo()
                    {
                        PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
                    }, ResultList = resultList
                });
            }
        }
Exemple #23
0
        private static void BuildSearchPOCondition(ProductPurchaseQueryFilter queryFilter, CustomDataCommand dataCommand, PagingInfoEntity pagingInfo)
        {
            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "po.sysno desc"))
            {
                #region build search condition
                string replaceSQL1 = "";
                string replaceSQL2 = "where 1=1";
                string replaceSQL3 = "where 1=1";

                if (queryFilter.CreateTimeBegin.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
                                                                 DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.Value);
                }

                if (queryFilter.CreateTimeTo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime",
                                                                 DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
                }
                if (!string.IsNullOrEmpty(queryFilter.AuditUser))
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%') ");
                }
                if (queryFilter.Status.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
                                                                 DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status);
                }

                if (!string.IsNullOrEmpty(queryFilter.StatusList))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status",
                                                                 DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 999);
                }

                if (queryFilter.IsConsign.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign",
                                                                 DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, (int)queryFilter.IsConsign);
                }

                if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.VendorSysNo",
                                                                 DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
                }

                #region 权限筛选

                //#region 添加供应商(创建PO单的对象)
                //int vendorCreateUserSysNo = int.Parse(AppSettingManager.GetSetting("PO", "VendorCreateUserSysNo"));
                //queryFilter.PMAuthorizedList.Add(vendorCreateUserSysNo);
                //#endregion 添加供应商(创建PO单的对象)

                //by Jack.W.Wang  2012-11-8 CRL21776--------------------------BEGIN
                //                if (!(queryFilter.IsManagerPM ?? false))
                //                {
                //                    string sqlStr = @"Select
                //									ProductLineSysNo
                //                            FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
                //                "WHERE  PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
                //                    sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po.ProductLineSysNo", QueryConditionOperatorType.In, sqlStr);
                //                }

                var createUserSysNo = queryFilter.PMSysNo;

                if (!string.IsNullOrEmpty(createUserSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
                                                                       "po.PMSysNo in(" + createUserSysNo + ")");
                }

                #endregion

                if (string.IsNullOrEmpty(queryFilter.POSysNoExtention))
                {
                    if (queryFilter.POSysNo != "" && queryFilter.POSysNo != null && Regex.IsMatch(queryFilter.POSysNo, "^[0-9]+$"))
                    {
                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.poid",
                                                                     DbType.String, "@poid", QueryConditionOperatorType.Like, queryFilter.POSysNo.Trim());
                    }
                }
                else
                {
                    string[]      poSysNoStr  = queryFilter.POSysNoExtention.Split('.');
                    List <string> poSysNoList = new List <string>();
                    foreach (string s in poSysNoStr)
                    {
                        poSysNoList.Add(s);
                    }

                    sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "po.poid",
                                                                   DbType.String, poSysNoList);
                }

                if (!string.IsNullOrEmpty(queryFilter.IsApportion))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsApportion",
                                                                 DbType.Int32, "@IsApportion", QueryConditionOperatorType.Equal, queryFilter.IsApportion);
                }

                if (!string.IsNullOrEmpty(queryFilter.CreatePOSysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateUserSysNo",
                                                                 DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreatePOSysNo);
                }

                if (queryFilter.PaySettleCompany.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PaySettleCompany",
                                                                 DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, (int)queryFilter.PaySettleCompany);
                }

                if (!string.IsNullOrEmpty(queryFilter.CurrencySysNo))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CurrencySysNo",
                                                                 DbType.Int32, "@CurrencySysNo", QueryConditionOperatorType.Equal, queryFilter.CurrencySysNo);
                }

                if (!string.IsNullOrEmpty(queryFilter.IsStockStatus))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PartlyReceiveStatus",
                                                                 DbType.Int32, "@PartlyReceiveStatus", QueryConditionOperatorType.Equal, queryFilter.IsStockStatus);
                }

                if (queryFilter.InStockFrom.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
                                                                 DbType.DateTime, "@InTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InStockFrom.Value);
                }

                if (queryFilter.InStockTo.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime",
                                                                 DbType.DateTime, "@InStockTo", QueryConditionOperatorType.LessThan, queryFilter.InStockTo.Value.AddDays(1));
                }

                if (queryFilter.POType.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POType",
                                                                 DbType.Int32, "@POType", QueryConditionOperatorType.Equal, (int)queryFilter.POType);
                }

                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
                                                             "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);

                if (queryFilter.LeaseFlag.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.LeaseFlag",
                                                                 DbType.Int32, "@LeaseFlag", QueryConditionOperatorType.Equal, queryFilter.LeaseFlag.Value == true ? 1 : 0);
                }

                if (!string.IsNullOrEmpty(queryFilter.VerifyStatus))
                {
                    if (queryFilter.VerifyStatus == "1")
                    {
                        replaceSQL1 += " and po.TPStatus ='1'  ";
                        replaceSQL3 += " and po.TPStatus ='1'  ";
                    }
                    if (queryFilter.VerifyStatus == "2")
                    {
                        replaceSQL1 += " and po.TPStatus = '2' ";
                        replaceSQL3 += " and po.TPStatus = '2' ";
                    }
                }

                if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) &&
                    Regex.IsMatch(queryFilter.ProductSysNo, "^[0-9]+$"))
                {
                    replaceSQL1 += string.Format(" and  exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
                    replaceSQL3 += string.Format(" and  exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo);
                }

                if (!string.IsNullOrEmpty(queryFilter.StockSysNo))
                {
                    //添加在途商品查询
                    if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value)
                    {
                        //获取仓库数据
                        string[]   stockArray = queryFilter.QueryStock.Split(',');
                        List <int> stockList  = new List <int>(stockArray.Length);
                        for (int i = 0; i < stockArray.Length; i++)
                        {
                            stockList.Add(int.Parse(stockArray[i]));
                        }

                        sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);

                        sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                        sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.ITStockSysNo",
                                                                             DbType.Int32, stockList);

                        sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
                                                                     DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
                        sqlBuilder.ConditionConstructor.EndGroupCondition();

                        sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.OR, "po.StockSysNo",
                                                                             DbType.Int32, stockList);
                        sqlBuilder.ConditionConstructor.EndGroupCondition();
                    }
                    else
                    {
                        if (queryFilter.StockSysNo == "50")
                        {
                            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
                                                                         DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50);
                            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ITStockSysNo",
                                                                         DbType.Int32, "@TransferMiddleStock", QueryConditionOperatorType.Equal, queryFilter.TranferStock);
                        }
                        else
                        {
                            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo",
                                                                         DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
                        }
                    }
                }

                if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value && !string.IsNullOrEmpty(queryFilter.QueryStatus))
                {
                    string[]   statusArray = queryFilter.QueryStatus.Split(',');
                    List <int> statusList  = new List <int>(statusArray.Length);

                    for (int i = 0; i < statusArray.Length; i++)
                    {
                        statusList.Add(int.Parse(statusArray[i]));
                    }

                    sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.Status",
                                                                         DbType.Int32, statusList);
                }
                #endregion

                if (queryFilter.PrintTime.HasValue)
                {
                    replaceSQL2 += string.Format(" and exists (select top 1 1 from scm.dbo.poLog with(nolock) GROUP BY Purno having scm.dbo.poLog.purno= po.sysno and convert(varchar(10),MAX(recdate),120) = convert(varchar(10),'{0}',120))", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
                    replaceSQL3 += string.Format(" and convert(varchar(10),a.MaxTastDate,120) = convert(varchar(10),'{0}',120)", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd"));
                }

                if (!string.IsNullOrEmpty(queryFilter.AuditUser))
                {
                    replaceSQL2 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
                    replaceSQL3 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'";
                }

                if (queryFilter.BrandSysNo.HasValue)
                {
                    replaceSQL1 += string.Format(@" AND EXISTS(SELECT TOP 1 1 
                                                                 FROM IPP3.dbo.PO_Item POI
                                                                      INNER JOIN IPP3.dbo.Product PRO
                                                                      ON POI.ProductSysNo = PRO.SysNo
                                                                      INNER JOIN [OverseaContentManagement].dbo.Brand BRA
                                                                      ON BRA.SysNo = PRO.BrandSysNo
                                                                WHERE POI.POSysNo = PO.SysNO
                                                                  AND BRA.SysNo = {0}) ", queryFilter.BrandSysNo.Value);
                }

                dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("replaceSQL1", replaceSQL1).Replace("replaceSQL2", replaceSQL2).Replace("replaceSQL3", replaceSQL3);
                if (pagingInfo != null)
                {
                    dataCommand.SetParameterValue("@StartNumber", queryFilter.PageSize * queryFilter.PageIndex);
                    dataCommand.SetParameterValue("@EndNumber", queryFilter.PageSize * queryFilter.PageIndex + queryFilter.PageSize);
                }
            }
        }
Exemple #24
0
        public static DataTable SettleQuery(SettleQueryFilter filter, out int dataCount)
        {
            CustomDataCommand command = DataCommandManager.
                                        CreateCustomDataCommandFromConfig("Invocie_SearchCommissionSettle");

            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
                       command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "SysNo DESC"))
            {
                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "SysNo", DbType.String,
                                                          "@SysNo", QueryConditionOperatorType.LeftLike,
                                                          filter.SysNo);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "MerchantSysNo", DbType.String,
                                                          "@MerchantSysNo", QueryConditionOperatorType.LeftLike,
                                                          filter.MerchantSysNo);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "Type", DbType.AnsiStringFixedLength,
                                                          "@Type", QueryConditionOperatorType.Equal,
                                                          filter.Type);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "SettledDateTime", DbType.AnsiStringFixedLength,
                                                          "@SettledBeginDateTime", QueryConditionOperatorType.MoreThanOrEqual,
                                                          filter.SettledBeginDateTime);


                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "SettledDateTime", DbType.AnsiStringFixedLength,
                                                          "@SettledEndDateTime", QueryConditionOperatorType.LessThanOrEqual,
                                                          filter.SettledEndDateTime);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "IssuingDateTime", DbType.AnsiStringFixedLength,
                                                          "@IssuingBeginDateTime", QueryConditionOperatorType.MoreThanOrEqual,
                                                          filter.IssuingBeginDateTime);


                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                          "IssuingDateTime", DbType.AnsiStringFixedLength,
                                                          "@IssuingEndDateTime", QueryConditionOperatorType.LessThanOrEqual,
                                                          filter.IssuingEndDateTime);

                command.CommandText = builder.BuildQuerySql();

                string typeStr = string.Empty;
                if (filter.Type != null)
                {
                    typeStr = string.Format("Where Type={0}", ((int)filter.Type.Value).ToString());
                }

                command.CommandText = command.CommandText.Replace("#StrWhere2#", typeStr);
                var            dt          = command.ExecuteDataTable();
                EnumColumnList enumColList = new EnumColumnList();
                enumColList.Add("Status", typeof(SettleOrderStatus));
                enumColList.Add("Type", typeof(SettleOrderType));
                command.ConvertEnumColumn(dt, enumColList);
                dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
Exemple #25
0
        private static void BuilCondition <Q>(Q filter, string dataType, List <FilterField> filterFields, DynamicQuerySqlBuilder sqlBuilder)
        {
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "r.BusinessDataType", DbType.String, "@BusinessDataType", QueryConditionOperatorType.Equal, dataType);
            List <string> changeTypes = new List <string>()
            {
                "A", "U"
            };

            sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "r.ChangeType", DbType.String, changeTypes);

            int index = 0;

            filterFields.ForEach(p =>
            {
                object parameterValue = Invoker.PropertyGet(filter, p.Name);
                string[] pNames       = p.ValuePath.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
                string path           = string.Format("(//{0}/text())[1]", pNames.Join("/"));
                DbType dbType         = GetDbType(p.DBType);
                string field          = string.Format("r.BusinessData.value('{0}','{1}')", path, p.DBType);
                QueryConditionOperatorType operatorType = GetEnum <QueryConditionOperatorType>(p.OperatorType);
                QueryConditionRelationType relationType = GetEnum <QueryConditionRelationType>(p.RelationType);

                sqlBuilder.ConditionConstructor.AddCondition(relationType,
                                                             field, dbType, "@Parameter" + index.ToString(), operatorType, parameterValue);
                index++;
            });
        }
Exemple #26
0
        /// <summary>
        /// 扣款项维护列表
        /// </summary>
        /// <param name="queryFilter"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable LoadDeductInfo(DeductQueryFilter queryFilter, out int totalCount)
        {
            if (queryFilter == null)
            {
                totalCount = -1;
                return(null);
            }

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

            if (null != queryFilter.PageInfo.SortBy)
            {
                if (queryFilter.PageInfo.SortBy.IndexOf("[Status]") > -1)
                {
                    queryFilter.PageInfo.SortBy = "D." + queryFilter.PageInfo.SortBy;
                }
            }

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

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Indate DESC"))
            {
                if (!string.IsNullOrEmpty(queryFilter.Name))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Name",
                                                                 DbType.String, "@Name", QueryConditionOperatorType.Like, queryFilter.Name);
                }

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

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

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

                if (queryFilter.Status.HasValue)
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.Status",
                                                                 DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value);
                }

                command.CommandText = sqlBuilder.BuildQuerySql();
                command.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
                command.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
                EnumColumnList columnEnums = new EnumColumnList();
                columnEnums.Add("DeductType", typeof(DeductType));
                columnEnums.Add("AccountType", typeof(AccountType));
                columnEnums.Add("DeductMethod", typeof(DeductMethod));
                columnEnums.Add("Status", typeof(Status));
                DataTable dt = command.ExecuteDataTable(columnEnums);
                totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
        private void AddParameter4InvoiceDetailReportQuery(InvoiceDetailReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
        {
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "OutTime"
                                                         , DbType.DateTime
                                                         , "@OutDateFrom"
                                                         , QueryConditionOperatorType.MoreThanOrEqual
                                                         , filter.OutDateFrom);

            if (filter.OutDateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "OutTime"
                                                             , DbType.DateTime
                                                             , "@OutDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , filter.OutDateTo.Value.AddDays(1).AddMinutes(-1));
            }

            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "InvoiceDate"
                                                         , DbType.DateTime
                                                         , "@InvoiceDateFrom"
                                                         , QueryConditionOperatorType.MoreThanOrEqual
                                                         , filter.InvoiceDateFrom);

            if (filter.InvoiceDateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "InvoiceDate"
                                                             , DbType.DateTime
                                                             , "@InvoiceDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1));
            }

            if (!string.IsNullOrEmpty(filter.OrderID))
            {
                List <string> orderIDList = new List <string>();
                foreach (string item in filter.OrderID.Split('.'))
                {
                    orderIDList.Add(item.Trim());
                }

                sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "OrderID", DbType.String, orderIDList);
            }

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

            var     customerNameString = filter.CustomerName;
            int     intTry;
            decimal decimalTry;

            if (!string.IsNullOrEmpty(customerNameString))
            {
                if (int.TryParse(customerNameString, out intTry) ||
                    decimal.TryParse(customerNameString, out decimalTry))
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "CustomerName"
                                                                 , DbType.String
                                                                 , "@CustomerName"
                                                                 , QueryConditionOperatorType.Equal
                                                                 , filter.CustomerName);
                }
                else
                {
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "CustomerName"
                                                                 , DbType.String
                                                                 , "@CustomerName"
                                                                 , QueryConditionOperatorType.Like
                                                                 , filter.CustomerName);
                }
            }
        }
Exemple #28
0
        public DataTable Query(FreeShippingChargeRuleQueryFilter filter, out int totalCount)
        {
            CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFreeShippingChargeRule");

            using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
                       command.CommandText, command, null, "[SysNo] DESC"))
            {
                if (filter.StartDate.HasValue || filter.EndDate.HasValue)
                {
                    builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                    #region 构造时间条件
                    builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
                    // s1 ≤ start && t1 ≥ start
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
                                                              "@StartDate1", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
                                                              "@EndDate1", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate);
                    builder.ConditionConstructor.EndGroupCondition();

                    builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
                    // s1 ≥ start && t1 ≤ end
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date,
                                                              "@StartDate2", QueryConditionOperatorType.LessThanOrEqual, filter.StartDate);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
                                                              "@EndDate2", QueryConditionOperatorType.MoreThanOrEqual, filter.EndDate);
                    builder.ConditionConstructor.EndGroupCondition();

                    builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR);
                    // s1 ≤ end && t1 ≥ end
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
                                                              "@StartDate3", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate);
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date,
                                                              "@EndDate3", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate);
                    builder.ConditionConstructor.EndGroupCondition();
                    #endregion
                    builder.ConditionConstructor.EndGroupCondition();
                }

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal,
                                                          "@AmtFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AmtFrom);

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal,
                                                          "@AmtTo", QueryConditionOperatorType.LessThanOrEqual, filter.AmtTo);

                if (filter.PayTypeSysNo.HasValue)
                {
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayTypeSettingValue", DbType.AnsiString,
                                                              "@PayTypeSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.PayTypeSysNo));
                }

                if (filter.ShipAreaID.HasValue)
                {
                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ShipAreaSettingValue", DbType.AnsiString,
                                                              "@ShipAreaSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.ShipAreaID));
                }

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

                builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingType", DbType.Int32,
                                                          "@AmountSettingType", QueryConditionOperatorType.Equal, filter.AmountSettingType);

                command.CommandText = builder.BuildQuerySql();

                EnumColumnList enumList = new EnumColumnList();
                enumList.Add("AmountSettingType", typeof(FreeShippingAmountSettingType));
                enumList.Add("Status", typeof(FreeShippingAmountSettingStatus));
                DataTable dt = command.ExecuteDataTable(enumList);

                if (dt != null && dt.Rows.Count > 0)
                {
                    string   areaSettingName = String.Empty;
                    string[] areaNameParts;
                    foreach (DataRow row in dt.Rows)
                    {
                        if (!(row["AreaSettingName"] is DBNull) && row["AreaSettingName"] != null)
                        {
                            areaSettingName = Convert.ToString(row["AreaSettingName"]);
                            areaNameParts   = areaSettingName.Split(new char[] { ',' }, StringSplitOptions.None);
                            if (areaNameParts.Length > 4)
                            {
                                row["AreaSettingName"] = string.Format("{0}等{1}个地区",
                                                                       string.Join(",", areaNameParts.ToList().GetRange(0, 4)), areaNameParts.Length);
                            }
                        }
                    }
                }

                totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
                return(dt);
            }
        }
        private void AddParameter4InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
        {
            //SOSysNo
            var sosysNoString = filter.SOSysNo;

            if (!string.IsNullOrEmpty(sosysNoString))
            {
                List <int> sosysNOList = new List <int>();
                foreach (string item in sosysNoString.Split('.'))
                {
                    sosysNOList.Add(int.Parse(item.Trim()));
                }
                sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "so.SysNo", DbType.Int32, sosysNOList);
            }

            //SOStatusCode(已出库)
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "so.Status"
                                                         , DbType.Int32
                                                         , "@SOStatusCode"
                                                         , QueryConditionOperatorType.Equal
                                                         , ECCentral.BizEntity.SO.SOStatus.OutStock);

            //IsVAT是否增票
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "so.IsVAT"
                                                         , DbType.Int32
                                                         , "@IsVAT"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.IsVAT);

            //CreateDateFrom
            var createdateFrom = filter.CreateDateFrom;

            if (createdateFrom.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.OrderDate"
                                                             , DbType.DateTime
                                                             , "@CreateDateFrom"
                                                             , QueryConditionOperatorType.MoreThanOrEqual
                                                             , createdateFrom.Value);
            }
            //CreateDateTo
            var createdateTo = filter.CreateDateTo;

            if (createdateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.OrderDate"
                                                             , DbType.DateTime
                                                             , "@CreateDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , createdateTo.Value.AddDays(1).AddSeconds(-1));
            }

            //OutDateFrom
            var outdateFrom = filter.OutDateFrom;

            if (outdateFrom.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.OutTime"
                                                             , DbType.DateTime
                                                             , "@OutDateFrom"
                                                             , QueryConditionOperatorType.MoreThanOrEqual
                                                             , outdateFrom.Value);
            }
            //OutDateTo
            var outdateTo = filter.OutDateTo;

            if (outdateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.OutTime"
                                                             , DbType.DateTime
                                                             , "@OutDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , outdateTo.Value.AddDays(1).AddSeconds(-1));
            }

            //AuditDateFrom
            var auditDateFrom = filter.AuditDateFrom;

            if (auditDateFrom.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.AuditTime"
                                                             , DbType.DateTime
                                                             , "@AuditDateFrom"
                                                             , QueryConditionOperatorType.MoreThanOrEqual
                                                             , auditDateFrom.Value);
            }
            //AuditDateTo
            var auditdateTo = filter.AuditDateTo;

            if (auditdateTo.HasValue)
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                             , "so.AuditTime"
                                                             , DbType.DateTime
                                                             , "@AuditDateTo"
                                                             , QueryConditionOperatorType.LessThanOrEqual
                                                             , auditdateTo.Value.AddDays(1).AddSeconds(-1));
            }

            //StockSysNo
            var stockSysNOString = filter.StockSysNo;

            if (string.IsNullOrEmpty(stockSysNOString))
            {
                //1:全部查询:只查询“发票打印”支持的分仓,从配置文件中取值,即StockSysNO="51,59,90,99";
                string        invoicePrintstocks = this.GetInvoicePrintStocks(filter.CompanyCode);
                List <string> stockNOList        = new List <string>();
                foreach (string item in invoicePrintstocks.Split(','))
                {
                    stockNOList.Add(item.Trim());
                }
                sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
                                                                        , "sb.WareHouseNumber"
                                                                        , DbType.AnsiStringFixedLength
                                                                        , stockNOList);
            }
            else
            {
                //非全部查询
                if (stockSysNOString.IndexOf(',') > 0)
                {
                    //2:上海仓:实际查询为“嘉定仓”+“宝山仓”,即StockSysNO="51,59"
                    List <string> stockNOList = new List <string>();
                    foreach (string item in stockSysNOString.Split(','))
                    {
                        stockNOList.Add(item.Trim());
                    }
                    sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
                                                                            , "sb.WareHouseNumber"
                                                                            , DbType.AnsiStringFixedLength
                                                                            , stockNOList);
                }
                else
                {
                    //3: 其他仓:即StockSysNO="90" 或者 StockSysNo="99"
                    sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                                 , "sb.WareHouseNumber"
                                                                 , DbType.AnsiStringFixedLength
                                                                 , "@StockSysNo"
                                                                 , QueryConditionOperatorType.Equal
                                                                 , stockSysNOString);
                }
            }

            //MerchantSysNo
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "scs.MerchantSysNo"
                                                         , DbType.Int32
                                                         , "@MerchantSysNo"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.VendorSysNo);

            //InvoiceType
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "scs.InvoiceType"
                                                         , DbType.AnsiStringFixedLength
                                                         , "@InvoiceType"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.InvoiceType);

            //StockType
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "scs.StockType"
                                                         , DbType.AnsiStringFixedLength
                                                         , "@StockType"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.StockType);

            //ShippingType
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "scs.ShippingType"
                                                         , DbType.AnsiStringFixedLength
                                                         , "@ShippingType"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.ShippingType);

            //CompanyCode
            sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
                                                         , "sb.CompanyCode"
                                                         , DbType.AnsiStringFixedLength
                                                         , "@CompanyCode"
                                                         , QueryConditionOperatorType.Equal
                                                         , filter.CompanyCode);
        }
Exemple #30
0
        private void AddARReceiveDetialsParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
        {
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "invoice.CompanyCode",
                DbType.AnsiStringFixedLength,
                "@CompanyCode",
                QueryConditionOperatorType.Equal,
                filter.CompanyCode);
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "ruleinfo.Status",
                DbType.String,
                "@RuleStatus",
                QueryConditionOperatorType.Equal,
                "O");
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "invoice.Status",
                DbType.String,
                "@InvoiceStatus",
                QueryConditionOperatorType.Equal,
                "O");
            if (filter.VendorSysNo.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "invoice.VendorNumber",
                    DbType.Int32,
                    "@VendorSysNo",
                    QueryConditionOperatorType.Equal,
                    filter.VendorSysNo);
            }
            if (!string.IsNullOrEmpty(filter.EIMSType))
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "invoice.EIMSType",
                    DbType.String,
                    "@EIMSType",
                    QueryConditionOperatorType.Equal,
                    filter.EIMSType);
            }
            if (filter.ProductCategory1.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "item.ItemCategory1",
                    DbType.Int32,
                    "@ProductCategory1",
                    QueryConditionOperatorType.Equal,
                    filter.ProductCategory1);
            }
            if (filter.ProductCategory2.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "item.ItemCategory2",
                    DbType.Int32,
                    "@ProductCategory2",
                    QueryConditionOperatorType.Equal,
                    filter.ProductCategory2);
            }
            cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
            cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);

            cmd.CommandText = sb.BuildQuerySql();
        }
Exemple #31
0
        public static StorePage QueryStorePage(StorePageFilter filter)
        {
            CustomDataCommand cmd;

            if (filter.IsPreview)
            {
                //preview
                cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPreview");
                using (var sqlBuild = new DynamicQuerySqlBuilder(cmd))
                {
                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "page.[SellerSysNo]", DbType.Int32, "@SellerSysNo",
                                                               QueryConditionOperatorType.Equal, filter.SellerSysNo);

                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "page.[SysNo]", DbType.Int32, "@SysNo",
                                                               QueryConditionOperatorType.Equal, filter.PublishPageSysNo);

                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "page.[PageTypeKey]", DbType.String, "@PageType",
                                                               QueryConditionOperatorType.Equal, filter.PageType);

                    cmd.CommandText = sqlBuild.BuildQuerySql();
                }
            }
            else
            {
                //not preview
                cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPublish");
                #region build condition
                using (var sqlBuild = new DynamicQuerySqlBuilder(cmd))
                {
                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "p.[SellerSysNo]", DbType.Int32, "@SellerSysNo",
                                                               QueryConditionOperatorType.Equal, filter.SellerSysNo);

                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "p.[SysNo]", DbType.Int32, "@SysNo",
                                                               QueryConditionOperatorType.Equal, filter.PublishPageSysNo);

                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "P.[PageTypeKey]", DbType.String, "@PageType",
                                                               QueryConditionOperatorType.Equal, filter.PageType);

                    sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
                                                               "p.[Status]", DbType.Int32, "@Status",
                                                               QueryConditionOperatorType.Equal, 1);

                    cmd.CommandText = sqlBuild.BuildQuerySql();
                }
                #endregion
            }


#if DEBUG
            Console.WriteLine("Query Store Page Command is : {0}", cmd.CommandText);
#endif
            var result = cmd.ExecuteEntity <StorePage>();
            if (result == null)
            {
                return(null);
            }

            var page = SerializationUtility.JsonDeserialize2 <StorePage>(result.DataValue);
            page.SysNo       = result.SysNo.Value;
            page.SellerSysNo = filter.SellerSysNo.Value;


            var pageTheme = QueryStorePageTheme(page.StorePageTemplate.StorePageThemeSysNo);
            if (pageTheme != null)
            {
                page.StorePageTemplate.StorePageThemeCssUrl = pageTheme.CssResUrl;
            }

            page.StorePageType = QueryStorePageType(page.StorePageType.Key);

            return(page);
        }
        public static QueryResponseDTO <List <ReviewModel> > GetReviewInfo(ReviewDTO dto)
        {
            QueryResponseDTO <List <ReviewModel> > responseDTO = new QueryResponseDTO <List <ReviewModel> >();
            CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetReviewInfo");

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
            {
                if (null != dto)
                {
                    if (!string.IsNullOrEmpty(dto.UserID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "UserID", DbType.AnsiString, "@UserID", QueryConditionOperatorType.Equal, dto.UserID);
                    }

                    if (!string.IsNullOrEmpty(dto.ReviewID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID);
                    }

                    if (!string.IsNullOrEmpty(dto.ProductOROrderID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID);
                    }

                    if (null != dto.ReviewDate)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate);
                    }

                    if (dto.Score > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score);
                    }

                    if (dto.FavourCount > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount);
                    }

                    if (dto.OpposeCount > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount);
                    }

                    sqlBuilder.ConditionConstructor
                    .AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed);
                }
                //QueryData
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                var result = dataCommand.ExecuteEntityList <ReviewModel>();

                responseDTO.ResultEntity = result;
            }
            responseDTO.TotalCount = dto.PagingInfo.TotalCount;
            responseDTO.Code       = ResponseStaticModel.Code.OK;
            responseDTO.Message    = ResponseStaticModel.Message.OK;
            return(responseDTO);
        }
Exemple #33
0
        /// <summary>
        /// 查询奖品发放记录
        /// </summary>
        /// <param name="filter">查询条件</param>
        /// <returns></returns>
        public static QueryResult<GiftsGrantRecord> QueryGiftsGrantRecord(GiftsGrantRecordQueryFilter filter)
        {
            QueryResult<GiftsGrantRecord> result = new QueryResult<GiftsGrantRecord>();
            result.ServicePageIndex = filter.ServicePageIndex;
            result.PageSize = filter.PageSize;

            PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
            CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftsGrantRecord");
            using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "M.SysNo DESC"))
            {
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32,
                    "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GiftSysNo", DbType.Int32,
                    "@GiftSysNo", QueryConditionOperatorType.Equal, filter.GiftSysNo);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantCounts", DbType.Int64,
                    "@GrantCounts", QueryConditionOperatorType.Like, filter.GrantCounts);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantDate", DbType.DateTime,
                    "@GrantDate", QueryConditionOperatorType.Like, filter.GrantDate);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPerson", DbType.String,
                    "@GrantPerson", QueryConditionOperatorType.Like, filter.GrantPerson);
                sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPlace", DbType.String,
                    "@GrantPlace", QueryConditionOperatorType.Like, filter.GrantPlace);

                cmd.CommandText = sqlBuilder.BuildQuerySql();
                result.ResultList = cmd.ExecuteEntityList<GiftsGrantRecord>();
                result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));

                return result;
            }
        }
        public static QueryResponseDTO <bool> DeleteReviewInfo(ReviewDTO dto)
        {
            QueryResponseDTO <bool> response = new QueryResponseDTO <bool>();

            response.ResultEntity = false;


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

            using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
                       dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC"))
            {
                if (null != dto)
                {
                    if (!string.IsNullOrEmpty(dto.UserID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "UserID", DbType.AnsiString, "@UserID", QueryConditionOperatorType.Equal, dto.UserID);
                    }

                    if (!string.IsNullOrEmpty(dto.ReviewID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID);
                    }

                    if (!string.IsNullOrEmpty(dto.ProductOROrderID))
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID);
                    }

                    if (null != dto.ReviewDate)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate);
                    }

                    if (dto.Score > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score);
                    }

                    if (dto.FavourCount > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount);
                    }

                    if (dto.OpposeCount > 0)
                    {
                        sqlBuilder.ConditionConstructor
                        .AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount);
                    }

                    sqlBuilder.ConditionConstructor
                    .AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed);
                }
                //QueryData
                dataCommand.CommandText = sqlBuilder.BuildQuerySql();
                var updateRows = dataCommand.ExecuteNonQuery();
                if (updateRows > 0)
                {
                    response.ResultEntity = true;
                    response.Code         = ResponseStaticModel.Code.OK;
                    response.Message      = ResponseStaticModel.Message.OK;
                }
                else
                {
                    response.ResultEntity = false;
                    response.Code         = ResponseStaticModel.Code.FAILED;
                    response.Message      = ResponseStaticModel.Message.FAILED;
                }
            }
            return(response);
        }
Exemple #35
0
        /// <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);
            }
        }
Exemple #36
0
 private void AddSAPDOCHeaderParameters(HeaderDataQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
 {
     if (filter.UploadDateFrom.HasValue)
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "[PSTNG_DATE]",
             DbType.DateTime,
             "@UploadDateFrom",
             QueryConditionOperatorType.MoreThanOrEqual,
             filter.UploadDateFrom);
     }
     if (filter.UploadDateTo.HasValue)
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "[PSTNG_DATE]",
             DbType.DateTime,
             "@UploadDateTo",
             QueryConditionOperatorType.LessThanOrEqual,
             filter.UploadDateTo);
     }
     if (!string.IsNullOrEmpty(filter.DocType))
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "[DOC_TYPE]",
             DbType.String,
             "@DOC_TYPE",
             QueryConditionOperatorType.Equal,
             filter.DocType);
     }
     if (filter.SapCoCode != ResCommonEnum.Enum_All)
     {
         sb.ConditionConstructor.AddCondition(
             QueryConditionRelationType.AND,
             "[COMP_CODE]",
             DbType.String,
             "@COMP_CODE",
             QueryConditionOperatorType.Equal,
             filter.SapCoCode);
     }
     cmd.CommandText = sb.BuildQuerySql();
 }
        private void AddEIMSEventMemoQueryParameters(EIMSEventMemoQueryFilter filter, DynamicQuerySqlBuilder sb, CustomDataCommand cmd)
        {
            sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
            sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventType", DbType.Int32, "@EventType", QueryConditionOperatorType.Equal, 195);
            string tempMemo = string.Empty;

            if (!string.IsNullOrEmpty(filter.Memo))
            {
                tempMemo = filter.Memo.Replace(" ", "%");
                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventMemo", DbType.String, "@EventMemo", QueryConditionOperatorType.Like, "%" + tempMemo + "%");
            }
            if (filter.BeginDate.HasValue)
            {
                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@BDate", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDate);
            }
            if (filter.EndDate.HasValue)
            {
                sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@EDate", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate.Value.ToShortDateString() + " 23:59:59");
            }
            cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize);
            cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex);
            cmd.CommandText = sb.BuildQuerySql();
        }
        public System.Data.DataTable QueryBasketList(QueryFilter.PO.PurchaseOrderBasketQueryFilter queryFilter, out int totalCount)
        {
            DataTable         dt         = new DataTable();
            CustomDataCommand command    = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBasket");
            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, "Basket.SysNo DESC"))
            {
                if (queryFilter != null)
                {
                    //by Jack.W.Wang  2012-12-6 CRL21776--------------------------BEGIN
                    if (!queryFilter.IsManagerPM.HasValue || !queryFilter.IsManagerPM.Value)
                    {
                        string tSubSqlTxt = @"Select 
									ProductLineSysNo 
                    FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
                                            "WHERE  PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
                        builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "line.ProductLineSysNo", QueryConditionOperatorType.In, tSubSqlTxt);
                    }
                    //by Jack.W.Wang  2012-12-6 CRL21776--------------------------END

                    //TODO:获取PM List;
                    #region 权限筛选
                    //var pmHasRight = AuthorizedPMs();
                    //var createUserSysNo = query.Condition.CreateUserSysNo;

                    //if (!string.IsNullOrEmpty(createUserSysNo))
                    //{
                    //    var createIsValid = pmHasRight.Exists(
                    //        x => x == Convert.ToInt32(createUserSysNo));
                    //    if (createIsValid)
                    //    {
                    //        builder.ConditionConstructor.AddCustomCondition(
                    //            QueryConditionRelationType.AND,
                    //            "Basket.CreateUserSysNo in(" + createUserSysNo + ")");
                    //    }
                    //    else
                    //    {
                    //        builder.ConditionConstructor.AddInCondition(
                    //            QueryConditionRelationType.AND,
                    //            "Basket.CreateUserSysNo",
                    //            DbType.Int32,
                    //            new List<int> { -999 });
                    //    }
                    //}
                    //else
                    //{
                    //    builder.ConditionConstructor.AddInCondition(
                    //        QueryConditionRelationType.AND,
                    //        "Basket.CreateUserSysNo",
                    //        DbType.Int32,
                    //        pmHasRight);
                    //}
                    #endregion

                    if (queryFilter.StockSysNo.HasValue)
                    {
                        string stockStr = queryFilter.StockSysNo.ToString();
                        //有中转仓
                        if (stockStr.Length > 2)
                        {
                            builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32,
                                                                      "@StockSysNo", QueryConditionOperatorType.Equal, Int32.Parse(stockStr.Substring(2)));
                            builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.IsTransfer", DbType.Int32,
                                                                      "@IsTransfer", QueryConditionOperatorType.Equal, 1);
                        }
                        else
                        {
                            builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
                        }
                    }
                    if (!string.IsNullOrEmpty(queryFilter.CreateUserSysNo))
                    {
                        builder.ConditionConstructor.AddCondition(
                            QueryConditionRelationType.AND,
                            "Basket.CreateUserSysNo",
                            DbType.Int32,
                            "@CreateUserSysNo",
                            QueryConditionOperatorType.Equal,
                            Int32.Parse(queryFilter.CreateUserSysNo));
                    }

                    builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.CompanyCode", System.Data.DbType.AnsiStringFixedLength,
                                                              "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
                }

                command.CommandText = builder.BuildQuerySql();
            }
            EnumColumnList enumList = new EnumColumnList();
            enumList.Add("PaySettleCompany", typeof(PaySettleCompany));
            dt         = command.ExecuteDataTable(enumList);
            totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
            return(dt);
        }
        private void AddInvoiceInfoListParameters(EIMSInvoiceEntryQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
        {
            List <Object> receiveTypeList = new List <Object>();

            receiveTypeList.Add(1);
            receiveTypeList.Add(2);
            List <Object> status = new List <Object>();

            status.Add("O");
            status.Add("F");
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "ei.ReceiveType",
                DbType.Int32,
                "@MyReceiveType",
                QueryConditionOperatorType.In,
                receiveTypeList);
            sb.ConditionConstructor.AddCondition(
                QueryConditionRelationType.AND,
                "ei.[STATUS]",
                DbType.String,
                "@MySTATUS",
                QueryConditionOperatorType.In,
                status);
            if (!string.IsNullOrEmpty(filter.AssignedCode))
            {
                filter.AssignedCode = filter.AssignedCode.Replace(" ", string.Empty);
                string[] spiltResults   = filter.AssignedCode.Split('.');
                string   invoiceNumbers = string.Empty;
                string   assignedCodes  = string.Empty;
                int      invoiceNumber  = 0;
                if (spiltResults.Length > 1)
                {
                    for (int i = 0; i < spiltResults.Length; i++)
                    {
                        if (int.TryParse(spiltResults[i], out invoiceNumber))
                        {
                            if (invoiceNumbers.Length > 0)
                            {
                                invoiceNumbers += ",";
                            }
                            invoiceNumbers += spiltResults[i];
                        }
                        else
                        {
                            if (assignedCodes.Length > 0)
                            {
                                assignedCodes += ",";
                            }
                            assignedCodes += "'" + spiltResults[i] + "'";
                        }
                    }
                    if (invoiceNumbers.Length > 0 && assignedCodes.Length == 0)
                    {
                        sb.ConditionConstructor.AddSubQueryCondition(
                            QueryConditionRelationType.AND,
                            "ei.InvoiceNumber",
                            QueryConditionOperatorType.In,
                            invoiceNumbers);
                    }
                    if (assignedCodes.Length > 0 && invoiceNumbers.Length == 0)
                    {
                        sb.ConditionConstructor.AddSubQueryCondition(
                            QueryConditionRelationType.AND,
                            "ei.AssignedCode",
                            QueryConditionOperatorType.In,
                            assignedCodes);
                    }
                    if (invoiceNumbers.Length > 0 && assignedCodes.Length > 0)
                    {
                        sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                        sb.ConditionConstructor.AddSubQueryCondition(
                            QueryConditionRelationType.AND,
                            "ei.InvoiceNumber",
                            QueryConditionOperatorType.In,
                            invoiceNumbers);
                        sb.ConditionConstructor.AddSubQueryCondition(
                            QueryConditionRelationType.OR,
                            "ei.AssignedCode",
                            QueryConditionOperatorType.In,
                            assignedCodes);
                        sb.ConditionConstructor.EndGroupCondition();
                    }
                }
                else
                {
                    if (int.TryParse(filter.AssignedCode, out invoiceNumber))
                    {
                        sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
                        sb.ConditionConstructor.AddCondition(
                            QueryConditionRelationType.AND,
                            "ei.AssignedCode",
                            DbType.String,
                            "@AssignedCode",
                            QueryConditionOperatorType.Equal,
                            filter.AssignedCode);
                        sb.ConditionConstructor.AddCondition(
                            QueryConditionRelationType.OR,
                            "ei.InvoiceNumber",
                            DbType.String,
                            "@InvoiceNumber",
                            QueryConditionOperatorType.Equal,
                            invoiceNumber);
                        sb.ConditionConstructor.EndGroupCondition();
                    }
                    else
                    {
                        sb.ConditionConstructor.AddCondition(
                            QueryConditionRelationType.AND,
                            "ei.AssignedCode",
                            DbType.String,
                            "@AssignedCode",
                            QueryConditionOperatorType.Equal,
                            filter.AssignedCode);
                    }
                }
            }
            if (filter.EIMSType.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ei.EIMSType",
                    DbType.String,
                    "@EIMSType",
                    QueryConditionOperatorType.Equal,
                    filter.EIMSType);
            }
            if (!string.IsNullOrEmpty(filter.InvoiceInputStatus))
            {
                string subSql = "SELECT COUNT(1) " +
                                "FROM  eims.dbo.EIMSInvoiceInput iei WITH(NOLOCK) " +
                                "INNER JOIN eims.dbo.EIMSInvoiceInput_Ex iex WITH(NOLOCK) " +
                                "ON iei.SysNo = iex.InvoiceInputSysNO AND iei.[status]=0 AND iex.[status]=0 " +
                                "WHERE iex.InvoiceNumber = ei.InvoiceNumber ";
                if (filter.InvoiceInputStatus == "已录入")
                {
                    sb.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "(" + subSql + ")",
                        DbType.Int32,
                        "@InvoiceInputStatusSql",
                        QueryConditionOperatorType.MoreThan,
                        0);
                }
                else if (filter.InvoiceInputStatus == "未录入")
                {
                    sb.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "(" + subSql + ")",
                        DbType.Int32,
                        "@InvoiceInputStatusSql",
                        QueryConditionOperatorType.Equal,
                        0);
                }
            }
            if (filter.ReceiveType.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ei.ReceiveType",
                    DbType.String,
                    "@ReceiveType",
                    QueryConditionOperatorType.Equal,
                    filter.ReceiveType);
            }
            if (!string.IsNullOrEmpty(filter.RuleAssignedCode))
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "er.AssignedCode",
                    DbType.String,
                    "@RuleAssignedCode",
                    QueryConditionOperatorType.Equal,
                    filter.RuleAssignedCode);
            }
            if (!string.IsNullOrEmpty(filter.Status))
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ei.[Status]",
                    DbType.String,
                    "@Status",
                    QueryConditionOperatorType.Equal,
                    filter.Status);
            }
            if (filter.VendorNumber.HasValue)
            {
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ei.VendorNumber",
                    DbType.String,
                    "@VendorNumber",
                    QueryConditionOperatorType.Equal,
                    filter.VendorNumber);
            }
            if (!string.IsNullOrEmpty(filter.IsSAPImported))
            {
                if (filter.IsSAPImported == "Y")
                {
                    sb.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "fp.IsSAPImported",
                        DbType.String,
                        "@IsSAPImported",
                        QueryConditionOperatorType.Equal,
                        filter.IsSAPImported);
                }
                else if (filter.IsSAPImported == "N")
                {
                    sb.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.AND,
                        "fp.IsSAPImported",
                        DbType.String,
                        "@IsSAPImported",
                        QueryConditionOperatorType.Equal,
                        filter.IsSAPImported);
                    sb.ConditionConstructor.AddCondition(
                        QueryConditionRelationType.OR,
                        "fp.IsSAPImported",
                        DbType.String,
                        "@InvoiceNumber",
                        QueryConditionOperatorType.IsNull,
                        null);
                }
            }

            if (!string.IsNullOrEmpty(filter.InvoiceNumber))
            {
                string[]      arr = filter.InvoiceNumber.Split('.');
                List <Object> invoiceNumberList = new List <Object>();
                string        invoiceNumber     = string.Empty;
                for (int i = 0; i < arr.Length; i++)
                {
                    invoiceNumberList.Add(arr[i]);
                }
                sb.ConditionConstructor.AddCondition(
                    QueryConditionRelationType.AND,
                    "ei.InvoiceNumber",
                    DbType.String,
                    "@InvoiceNumber",
                    QueryConditionOperatorType.In,
                    invoiceNumberList);
            }

            if (!string.IsNullOrEmpty(filter.InvoiceInputNo))
            {
                string subSql = "EXISTS(SELECT 1 " +
                                "FROM EIMS.dbo.EIMSInvoiceInput_Ex eiiex WITH(NOLOCK) " +
                                "INNER JOIN eims.dbo.EIMSInvoiceInput eii WITH(NOLOCK) " +
                                "	ON eiiex.InvoiceInputSysNO = eii.SysNO  "+
                                "WHERE eii.Status=0  " +
                                "   AND eiiex.Status=0 " +
                                "	AND eii.InvoiceInputNo="+ filter.InvoiceInputNo + "  " +
                                "   AND eiiex.InvoiceNumber=ei.InvoiceNumber)";
                cmd.CommandText += subSql;
                sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, subSql);
            }

            cmd.CommandText = sb.BuildQuerySql();
        }
Exemple #40
0
        private void AddARReceiveParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb)
        {
            string strSql = " WHERE ruleinfo.Status = 'O' AND invoice.Status = 'O' AND invoice.CompanyCode = @CompanyCode";

            cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, filter.CompanyCode);
            if (filter.VendorSysNo.HasValue)
            {
                strSql += " AND invoice.VendorNumber = @VendorSysNo ";
                cmd.AddInputParameter("@VendorSysNo", DbType.Int32, filter.VendorSysNo);
            }
            if (!string.IsNullOrEmpty(filter.EIMSType))
            {
                strSql += " AND invoice.EIMSType = @EIMSType ";
                cmd.AddInputParameter("@EIMSType", DbType.String, filter.EIMSType);
            }
            if (filter.ProductCategory1.HasValue)
            {
                strSql += " AND item.ItemCategory1 = @ItemCategory1 ";
                cmd.AddInputParameter("@ItemCategory1", DbType.Int32, filter.ProductCategory1);
            }

            if (filter.ProductCategory2.HasValue)
            {
                strSql += " AND item.ItemCategory2 = @ItemCategory2 ";
                cmd.AddInputParameter("@ItemCategory2", DbType.Int32, (filter.ProductCategory2));
            }

            cmd.CommandText = cmd.CommandText.Replace("#WhereStr1#", strSql);
            strSql         += " AND item.AccruePeriod = @AccruePeriod";
            cmd.AddInputParameter("@AccruePeriod", DbType.String, GetEffectiveAccruePeriod());
            cmd.CommandText = cmd.CommandText.Replace("#WhereStr2#", strSql);
            cmd.AddOutParameter("@TotalCount", DbType.Int32, 4);
        }