/// <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; } }
/// <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; } }
/// <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; } }
/// <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> /// 查询近7天未下过订单的用户 /// </summary> /// <param name="filter">查询条件</param> /// <returns></returns> public static StatementResult<ArrayList> QuerySevenDayNotHaveOrder(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("QuerySevenDayNotHaveOrder"); 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; } }
/// <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; } }
/// <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); } }
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 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 }); } }
/// <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); } }
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); }
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); } }
public DataTable UserQuery(VendorUserQueryFilter filter, out int dataCount) { if (filter.PagingInfo.SortBy != null) { string sortCondition = filter.PagingInfo.SortBy.Trim(); Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase); if (match.Groups["SortColumn"].Success) { string sortColumn = match.Groups["SortColumn"].Value; string sortType = match.Groups["SortType"].Success ? match.Groups["SortType"].Value : "DESC"; #region switch switch (sortColumn) { case "SysNo": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.SysNo", sortType); break; case "RoleName": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.RoleName", sortType); break; case "Status": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.Status", sortType); break; case "InUser": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.InUser", sortType); break; case "InDate": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.InDate", sortType); break; case "EditUser": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.EditUser", sortType); break; case "EditDate": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.EditDate", sortType); break; case "VendorSysNo": filter.PagingInfo.SortBy = String.Format("{0} {1}", "a.VendorSysNo", sortType); break; case "VendorName": filter.PagingInfo.SortBy = String.Format("{0} {1}", "b.VendorName", sortType); break; case "VendorStatus": filter.PagingInfo.SortBy = String.Format("{0} {1}", "b.Status", sortType); break; case "Rank": filter.PagingInfo.SortBy = String.Format("{0} {1}", "b.Rank", sortType); break; case "IsConsign": filter.PagingInfo.SortBy = String.Format("{0} {1}", "b.IsConsign", sortType); break; } #endregion } } CustomDataCommand command = DataCommandManager. CreateCustomDataCommandFromConfig("External_Query_Vendor"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo); if (!string.IsNullOrEmpty(filter.SerialNum)) { string[] serialNum = filter.SerialNum.Split('-'); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, serialNum[0]); if (serialNum.Length > 1) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserNum", DbType.Int32, "@UserNum", QueryConditionOperatorType.Equal, Convert.ToInt32(serialNum[1]).ToString()); } } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserName", DbType.String, "@UserName", QueryConditionOperatorType.LeftLike, filter.UserName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserID", DbType.String, "@UserID", QueryConditionOperatorType.LeftLike, filter.UserID); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, LegacyEnumMapper.ConvertValidStatus(filter.UserStatus)); if (filter.RoleSysNo.HasValue) { builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, @"SELECT TOP 1 1 FROM (SELECT UserSysNo FROM [IPP3].[dbo].[VendorUser_User_Role] WITH(NOLOCK) WHERE RoleSysNo=@RoleSysNo ) AS RESULT WHERE RESULT.[UserSysNo]=a.[SysNo]"); builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.OR, null, QueryConditionOperatorType.Exist, @"SELECT TOP 1 1 FROM (SELECT UserSysNo FROM [IPP3].[dbo].[VendorUser_RoleMapping] WITH(NOLOCK) WHERE RoleSysNo=@RoleSysNo ) AS RESULT WHERE RESULT.[UserSysNo]=a.[SysNo]"); command.AddInputParameter("@RoleSysNo", DbType.Int32, filter.RoleSysNo); builder.ConditionConstructor.EndGroupCondition(); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.VendorName", DbType.String, "@VendorName", QueryConditionOperatorType.Like, filter.VendorName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ExpiredDate", DbType.DateTime, "@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.ExpiredDateFrom); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ExpiredDate", DbType.DateTime, "@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.ExpiredDateTo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.Contact", DbType.String, "@Contact", QueryConditionOperatorType.LeftLike, filter.Contact); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.Phone", DbType.String, "@Phone", QueryConditionOperatorType.LeftLike, filter.Phone); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.Address", DbType.String, "@Address", QueryConditionOperatorType.LeftLike, filter.Address); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.Status", DbType.Int32, "@VendorStatus", QueryConditionOperatorType.Equal, filter.VendorStatus); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.Rank", DbType.String, "@Rank", QueryConditionOperatorType.Equal, filter.Rank); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.IsConsign", DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, filter.ConsignType); if (filter.ManufacturerSysNo.HasValue || (!string.IsNullOrEmpty(filter.AgentLevel) && filter.AgentLevel.Trim() != "") || filter.C1SysNo.HasValue) { string subCond = @"SELECT distinct [VendorSysNo] FROM dbo.Vendor_Manufacturer WITH(NOLOCK) "; string strWhere = string.Empty; //代理品牌 二级类 三级类 代理级别 if (filter.ManufacturerSysNo.HasValue) { strWhere += "ManufacturerSysNo=@ManufacturerSysNo"; command.AddInputParameter("@ManufacturerSysNo", DbType.Int32, filter.ManufacturerSysNo); } if (!string.IsNullOrEmpty(filter.AgentLevel) && filter.AgentLevel.Trim() != "") { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "AgentLevel=@AgentLevel"; command.AddInputParameter("@AgentLevel", DbType.String, filter.AgentLevel); } if (filter.C3SysNo.HasValue) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "C3SysNo=@C3SysNo"; command.AddInputParameter("@C3SysNo", DbType.Int32, filter.C3SysNo); } else { if (filter.C2SysNo.HasValue) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "C2SysNo=@C2SysNo"; command.AddInputParameter("@C2SysNo", DbType.Int32, filter.C2SysNo); } else { if (filter.C1SysNo.HasValue) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += @"C2SysNo in (SELECT [Category2Sysno] FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo] WITH(NOLOCK) WHERE [Category1Sysno]=@C1SysNo)"; command.AddInputParameter("@C1SysNo", DbType.Int32, filter.C1SysNo); } } } if (!string.IsNullOrEmpty(strWhere)) { subCond += "WHERE status=0 and " + strWhere; } builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.SysNo", QueryConditionOperatorType.In, subCond); } command.CommandText = builder.BuildQuerySql(); var dt = command.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); enumColList.Add("IsConsign", typeof(ECCentral.BizEntity.PO.VendorConsignFlag)); enumColList.Add("Status", typeof(ECCentral.BizEntity.ExternalSYS.ValidStatus)); enumColList.Add("VendorStatus", typeof(VendorStatus)); command.ConvertEnumColumn(dt, enumColList); dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable QueryAllocatedItemOrdersRelated(QueryFilter.Inventory.InventoryAllocatedCardQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); if (queryFilter == null || queryFilter.ProductSysNo == null || !queryFilter.ProductSysNo.HasValue) { totalCount = 0; return(null); } PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; CustomDataCommand dataCommand; if (queryFilter.StockSysNo.HasValue) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryItemAllocatedCardReLatedOrder"); dataCommand.SetParameterValue("@WarehouseSysNumber", queryFilter.StockSysNo.Value); } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryItemAllocatedCardReLatedOrderWithOutWarehouseNumber"); } dataCommand.SetParameterValue("@ItemSysNumber", queryFilter.ProductSysNo.Value); dataCommand.SetParameterValue("@CompanyCode", queryFilter.CompanyCode); using (var sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingInfo, "OrderTime DESC")) { dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } #region 计算结存数量 if (null != dt) { dt.Columns.Add("OrderThenQty", typeof(int)); } var orderThenQty = 0; for (var i = dt.Rows.Count - 1; i >= 0; i--) { //OrderCode means po-调价(instock)(PO调价单) if (dt.Rows[i]["OrderCode"] != null && dt.Rows[i]["OrderCode"].ToString() != "3") { orderThenQty += (dt.Rows[i]["OrderQty"] != null ? int.Parse(dt.Rows[i]["OrderQty"].ToString()) : 0); } dt.Rows[i]["OrderThenQty"] = orderThenQty; } #endregion if (null != dt && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dr["OrderNameString"] = CodeNamePairManager.GetName("Inventory", "InventoryCardOrderType", dr["OrderName"].ToString()); } } return(dt); }
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); } } }
/// <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); } }
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); }
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); }
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(); }
/// <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); } }
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); } }
public static QueryResult AOQuery(AOQueryFilter queryFilter) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("AOQuery"); using ( var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "bank.SysNo ASC" : queryFilter.SortFields)) { if (queryFilter.SOSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format("(so.SysNo = {0} or sc.SoSplitMaster = {0})", queryFilter.SOSysNo)); } if (queryFilter.OrderDateBegin.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate", DbType.DateTime, "@OrderDateBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.OrderDateBegin); } if (queryFilter.OrderDateEnd.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate", DbType.DateTime, "@OrderDateEnd", QueryConditionOperatorType.LessThan, queryFilter.OrderDateEnd.Value.Date.AddDays(1)); } if (!string.IsNullOrWhiteSpace(queryFilter.CustomerID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cust.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, queryFilter.CustomerID); } if (queryFilter.PayTypeSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo); } if (queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "bank.Status", DbType.Int32, "@SOStatus", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value); } if (queryFilter.RefundPayType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RefundPayType", DbType.Int32, "@RefundPayType", QueryConditionOperatorType.Equal, (int)queryFilter.RefundPayType.Value); } if (queryFilter.MerchantSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sc.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo); } if (string.IsNullOrWhiteSpace(queryFilter.SortFields)) { queryFilter.SortFields = "bank.SysNo ASC"; } command.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumColumns = new EnumColumnList(); enumColumns.Add("Status", typeof(RefundStatus)); enumColumns.Add("RefundPayType", typeof(RefundPayType)); DataTable result = command.ExecuteDataTable(enumColumns); int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(new QueryResult(result, queryFilter, totalCount)); } }
/// <summary> /// 查询商品价格变动申请单据 /// </summary> /// <param name="queryFilter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable QueryProductPriceRequesList(ProductPriceRequestQueryFilter queryFilter, out int totalCount) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductPriceRequesList"); var pagingInfo = new PagingInfoEntity { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; if (ComparisonMapping.Count == 0) { SetComparisonMapping(); } using ( var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "P.ProductSysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Status", DbType.String, "@Status", QueryConditionOperatorType.In, new List <object> { ProductPriceRequestStatus.Origin, ProductPriceRequestStatus.NeedSeniorApprove }); if (!String.IsNullOrEmpty(queryFilter.ProductID)) { dataCommand.AddInputParameter("@ProductID", DbType.String, queryFilter.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PP.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, queryFilter.ProductID); } if (queryFilter.ProductStatus != null) { dataCommand.AddInputParameter("@ProductStatus", DbType.Int32, queryFilter.ProductStatus); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PP.Status", DbType.Int32, "@ProductStatus", QueryConditionOperatorType.Equal, queryFilter.ProductStatus); } if (queryFilter.ManufacturerSysNo != null && queryFilter.ManufacturerSysNo > 0) { dataCommand.AddInputParameter("@ManufacturerSysNo", DbType.Int32, queryFilter.ManufacturerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PP.ManufacturerSysNo", DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal, queryFilter.ManufacturerSysNo); } if (queryFilter.Category3 != null && queryFilter.Category3.Value > 0) { dataCommand.AddInputParameter("@C3SysNo", DbType.Int32, queryFilter.Category3.Value); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PP.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryFilter.Category3.Value); } if (queryFilter.Category2 != null && queryFilter.Category2.Value > 0) { dataCommand.AddInputParameter("@C2SysNo", DbType.Int32, queryFilter.Category2.Value); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryFilter.Category2.Value); } if (queryFilter.Category1 != null && queryFilter.Category1.Value > 0) { dataCommand.AddInputParameter("@C1SysNo", DbType.Int32, queryFilter.Category1.Value); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryFilter.Category1.Value); } dataCommand.AddInputParameter("@PMRole", DbType.Int32, queryFilter.PMRole); if (queryFilter.PMRole == 1)//初级权限 { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " PP.PMUserSysNo IN (SELECT PMSysNo FROM #temp1 t1) "); } else if (queryFilter.PMRole == 3)//高级权限 { } else//无权限 { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " 1<>1 "); } if (queryFilter.AuditType != null) { switch (queryFilter.AuditType) { case QueryProductPriceRequestAuditType.Audit: sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Type", DbType.String, "@AuditType", ComparisonMapping[queryFilter.ComparisonOperators], ProductPriceRequestAuditType.Audit); break; case QueryProductPriceRequestAuditType.SeniorAudit: if (queryFilter.ComparisonOperators == Comparison.Equal) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Type", DbType.String, "@AuditType", ComparisonMapping[queryFilter.ComparisonOperators], ProductPriceRequestAuditType.SeniorAudit); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Status", DbType.String, "@OtherStatus", QueryConditionOperatorType.Equal, ProductPriceRequestStatus.NeedSeniorApprove ); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Status", DbType.String, "@OtherStatus", QueryConditionOperatorType.Equal, ProductPriceRequestStatus.Origin ); } break; case QueryProductPriceRequestAuditType.Submit: if (queryFilter.ComparisonOperators == Comparison.Equal) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Type", DbType.String, "@AuditType", ComparisonMapping[queryFilter.ComparisonOperators], ProductPriceRequestAuditType.SeniorAudit); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.Status", DbType.String, "@OtherStatus", QueryConditionOperatorType.Equal, ProductPriceRequestStatus.Origin ); } else { sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "P.Type", DbType.String, "@AuditType", ComparisonMapping[queryFilter.ComparisonOperators], ProductPriceRequestAuditType.SeniorAudit); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "P.Status", DbType.String, "@OtherStatus", ComparisonMapping[queryFilter.ComparisonOperators], ProductPriceRequestStatus.Origin ); sqlBuilder.ConditionConstructor.EndGroupCondition(); } break; } } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList { { "AuditType", typeof(ProductPriceRequestAuditType) }, { "Status", typeof(ProductPriceRequestStatus) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable QueryRMARequest(RMARequestQueryFilter filter, out int totalCount) { if (filter == null) { totalCount = -1; return(null); } CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRequests"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = filter.PagingInfo.SortBy, StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize, MaximumRows = filter.PagingInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "request.SysNo DESC")) { #region build query conditions if (filter.CreateDateFrom != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateDateFrom); } if (filter.CreateDateTo != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, filter.CreateDateTo); } if (filter.ReceivedDateFrom != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.RecvTime", DbType.DateTime, "@RecvTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.ReceivedDateFrom); } if (filter.ReceivedDateTo != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.RecvTime", DbType.DateTime, "@RecvTimeTo", QueryConditionOperatorType.LessThan, filter.ReceivedDateTo); } if (filter.ETakeDateFrom != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.ETakeDate", DbType.DateTime, "@ETakeDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.ETakeDateFrom); } if (filter.ETakeDateTo != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.ETakeDate", DbType.DateTime, "@ETakeDateTo", QueryConditionOperatorType.LessThan, filter.ETakeDateTo); } if (!string.IsNullOrEmpty(filter.SOID)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "so.SOID", DbType.String, "@SOID", QueryConditionOperatorType.Equal, filter.SOID); } if (!string.IsNullOrEmpty(filter.RequestID)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.RequestID", DbType.String, "@RequestID", QueryConditionOperatorType.Equal, filter.RequestID); } if (filter.IsVIP.HasValue) { using (GroupCondition group = new GroupCondition(builder, QueryConditionRelationType.AND)) { if (filter.IsVIP.Value) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "customer.VIPRank", DbType.Int32, "@AutoVIP", QueryConditionOperatorType.Equal, 2 // VIPRank.AutoVIP ); builder.ConditionConstructor.AddCondition( QueryConditionRelationType.OR, "customer.VIPRank", DbType.Int32, "@ManualVIP", QueryConditionOperatorType.Equal, 4 // VIPRank.ManualVIP ); } else { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "customer.VIPRank", DbType.Int32, "@AutoNonVIP", QueryConditionOperatorType.Equal, 1 // VIPRank.AutoNonVIP ); builder.ConditionConstructor.AddCondition( QueryConditionRelationType.OR, "customer.VIPRank", DbType.Int32, "@ManualNonVIP", QueryConditionOperatorType.Equal, 3 // VIPRank.ManualNonVIP ); } } } if (filter.CustomerSysNo.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "customer.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo); } if (!string.IsNullOrEmpty(filter.CustomerID)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "customer.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID); } if (filter.ReceiveUserSysNo.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.RecvUserSysNo", DbType.Int32, "@RecvUserSysNo", QueryConditionOperatorType.Equal, filter.ReceiveUserSysNo); } if (filter.IsSubmit.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.IsSubmit", DbType.Int32, "@IsSubmit", QueryConditionOperatorType.Equal, filter.IsSubmit); } if (filter.Status.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status ); } if (filter.SysNo.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo ); } if (filter.SOSysNo.HasValue) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo ); } if (!string.IsNullOrEmpty(filter.CompanyCode)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode ); } if (filter.IsLabelPrinted != null) { string strIsLabelPrinted = "N"; if (filter.IsLabelPrinted.Value) { strIsLabelPrinted = "Y"; } builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.IsLabelPrinted", DbType.AnsiStringFixedLength, "@IsLabelPrinted", QueryConditionOperatorType.Equal, strIsLabelPrinted ); } if (filter.SellersType != null) { if (filter.SellersType == SellersType.Self) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.InvoiceType", DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.Equal, 0 ); } else { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.InvoiceType", DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.Equal, 1 ); } } //服务编码 add by norton 2012.11.21 if (!string.IsNullOrEmpty(filter.ServiceCode)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.ServiceCode", DbType.String, "@ServiceCode", QueryConditionOperatorType.Equal, filter.ServiceCode); } if (filter.AuditUserSysNo.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.AuditUserSysNo", DbType.Int32, "@AuditUserSysNo", QueryConditionOperatorType.Equal, filter.AuditUserSysNo); } if (filter.AuditDateFrom != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.AuditTime", DbType.DateTime, "@AuditDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AuditDateFrom); } if (filter.AuditDateTo != null) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "request.AuditTime", DbType.DateTime, "@AuditDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.AuditDateTo); } #endregion dataCommand.CommandText = builder.BuildQuerySql(); #region 扩展字段读取 string columnExtendColumnRMAItemSysNos = string.Empty; //需要读取额外的单件连接数据 if (filter.IsReadRMAItemSysNos) { columnExtendColumnRMAItemSysNos = string.Format(",{0}", "(SELECT [OverseaOrderManagement].[dbo].[UF_GetRMAItemSysNos] (request.SysNo)) as RMAItemSysNos"); } dataCommand.CommandText = dataCommand.CommandText.Replace("#ExtendColRMAItemSysNos#", columnExtendColumnRMAItemSysNos); #endregion dataCommand.SetParameterValue("@StartNumber", filter.PagingInfo.PageSize * filter.PagingInfo.PageIndex); dataCommand.SetParameterValue("@EndNumber", filter.PagingInfo.PageSize * filter.PagingInfo.PageIndex + filter.PagingInfo.PageSize); DataTable dt = dataCommand.ExecuteDataTable("Status", typeof(RMARequestStatus)); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public DataSet Query(BalanceAccountQueryFilter filter, out int totalCount) { DataSet result = null; PagingInfoEntity pagingInfo = new PagingInfoEntity(); if (filter.PagingInfo != null) { MapSortField(filter); pagingInfo.MaximumRows = filter.PagingInfo.PageSize; pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; pagingInfo.SortField = filter.PagingInfo.SortBy; } CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBalanceAccount"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingInfo, "CP.SysNo desc")) { StringBuilder t1 = new StringBuilder(); StringBuilder t2 = new StringBuilder(); StringBuilder t3 = new StringBuilder(); t1.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode "); t2.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode "); t3.Append("WHERE PL.Status = @Status AND PL.CompanyCode = @CompanyCode "); if (filter.CustomerSysNo.HasValue) { t1.Append(" AND PL.CustomerSysNo = @CustomerSysNo "); t2.Append(" AND PL.CustomerSysNo = @CustomerSysNo "); t3.Append(" AND PL.CustomerSysNo = @CustomerSysNo "); } if (!string.IsNullOrEmpty(filter.CustomerID)) { t1.Append(" AND C.CustomerID = @CustomerID "); t2.Append(" AND C.CustomerID = @CustomerID "); t3.Append(" AND C.CustomerID = @CustomerID "); } if (filter.CreateTimeFrom.HasValue) { t1.Append(" AND PL.CreateTime < @CreateDateFrom "); t3.Append(" AND PL.CreateTime > @CreateDateFrom "); } if (filter.CreateTimeTo.HasValue) { t2.Append(" AND PL.CreateTime <= @CreateDateTo "); t3.Append(" AND PL.CreateTime <= @CreateDateTo "); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CustomerSysNo", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime", DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CreateTime", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CP.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, 'A'); if (filter.DetailType.HasValue) { if (filter.DetailType.Value > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.MoreThanOrEqual, 0); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cp.PrepayAmt", DbType.Decimal, "@PrepayAmt", QueryConditionOperatorType.LessThan, 0); } } dataCommand.AddOutParameter("@StartBalance", DbType.Double, 50); dataCommand.AddOutParameter("@EndBalance", DbType.Double, 50); dataCommand.AddOutParameter("@PayedIn", DbType.Double, 50); dataCommand.AddOutParameter("@PayedOut", DbType.Double, 50); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE01#", t1.ToString()); dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE02#", t2.ToString()); dataCommand.CommandText = dataCommand.CommandText.Replace("#ITWHERE03#", t3.ToString()); result = ExecuteDataTable(dataCommand, filter, out totalCount); } return(result); }
/// <summary> /// 查询VendorProdcut /// </summary> /// <returns></returns> public DataTable VendorProductQuery(VendorProductQueryFilter filter, out int TotalCount) { CustomDataCommand command = DataCommandManager. CreateCustomDataCommandFromConfig("External_Query_VendorProduct"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC")) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductType", DbType.Int32, "@ProductType", QueryConditionOperatorType.Equal, 0); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@Status", QueryConditionOperatorType.MoreThanOrEqual, 0); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ManufacturerSysNo", DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal, filter.VendorManufacturerSysNo); command.AddInputParameter("@UserSysNo", DbType.Int32, filter.UserSysNo); if (filter.C3SysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo); } else { if (filter.C2SysNo.HasValue) { ConditionConstructor subQueryBuilder = sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.AND, "a.C3SysNo", QueryConditionOperatorType.In, @"SELECT [Category3Sysno] FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo] WHERE [Category2Sysno]=@C2SysNo"); command.AddInputParameter("@C2SysNo", DbType.Int32, filter.C2SysNo); } } if (!filter.IsAuto) { QueryConditionOperatorType ot = QueryConditionOperatorType.Exist; if (!filter.IsMapping) { ot = QueryConditionOperatorType.NotExist; } sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.AND, null, ot, @"SELECT TOP 1 1 FROM [IPP3].[dbo].VendorUser_ProductMapping WHERE ProductSysNo=a.sysno AND UserSysNo=@UserSysNo AND VendorExSysNo=@VendorExSysNo"); } sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); command.AddInputParameter( "@VendorManufacturerSysNo", DbType.Int32, filter.ManufacturerSysNo); command.CommandText = sb.BuildQuerySql(); DataTable dt = command.ExecuteDataTable(); TotalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
/// <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 }); } }
/// <summary> /// 按PM统计 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public virtual DataTable ExportAllByPM(LendRequestQueryFilter queryCriteria, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize; pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryLendItemforExport"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " userInfo.DisplayName ASC")) { if (queryCriteria.SysNo.HasValue && queryCriteria.SysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lend.SysNo", DbType.Int32, "@RequestSysNo", QueryConditionOperatorType.Equal, queryCriteria.SysNo); } if (!string.IsNullOrEmpty(queryCriteria.RequestID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lend.LendID", DbType.String, "@RequestID", QueryConditionOperatorType.Like, queryCriteria.RequestID); } if (queryCriteria.LendUserSysNo.HasValue && queryCriteria.LendUserSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lendUser.IPPUserSysNo", DbType.String, "@LendUserSysNo", QueryConditionOperatorType.LeftLike, queryCriteria.LendUserSysNo); } if (queryCriteria.CreateDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lend.CreateTime", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.CreateDateFrom); } if (queryCriteria.CreateDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lend.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThanOrEqual, queryCriteria.CreateDateTo); } if (queryCriteria.RequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lend.Status", DbType.Int32, "@RequestStatus", QueryConditionOperatorType.Equal, queryCriteria.RequestStatus); } if (queryCriteria.StockSysNo.HasValue && queryCriteria.StockSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "stock.SysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryCriteria.StockSysNo); } if (queryCriteria.ProductSysNo.HasValue && queryCriteria.ProductSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lendItem.productsysno", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo); } if (queryCriteria.PMUserSysNo.HasValue && queryCriteria.PMUserSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "itemBasic.PMUserSysNo", DbType.Int32, "@PMUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo); } if (!string.IsNullOrEmpty(queryCriteria.CompanyCode)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "lendItem.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); } cmd.CommandText = sqlBuilder.BuildQuerySql(); DataTable resultData = cmd.ExecuteDataTable(); totalCount = 10000; return(resultData); } }
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); }
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); } }
public virtual DataTable Query(QueryFilter.Customer.RefundRequestQueryFilter 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("QueryRefundRequest"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " R.SysNo DESC ")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.RequestType", DbType.String, "@RequestType", QueryConditionOperatorType.Equal, queryCriteria.RequestType); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryCriteria.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, queryCriteria.SOSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.RefundType", DbType.String, "@RefundType", QueryConditionOperatorType.Equal, queryCriteria.RefundType); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, queryCriteria.CustomerId); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Equal, queryCriteria.EditUserName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.InDate", DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.CreateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.InDate", DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryCriteria.CreateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.EditDate", DbType.DateTime, "@EditDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.EditFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "R.EditDate", DbType.DateTime, "@EditDateTo", QueryConditionOperatorType.LessThan, queryCriteria.EditTo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "R.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add(4, typeof(RefundRequestType)); enumList.Add(12, typeof(RefundRequestStatus)); CodeNamePairColumnList cpList = new CodeNamePairColumnList(); cpList.Add(5, "Customer", "RefundType"); DataTable dt = cmd.ExecuteDataTable(enumList, cpList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <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 DataTable RoleQuery(VendorRoleQueryFilter filter, out int dataCount) { if (filter.PagingInfo != null && filter.PagingInfo.SortBy != null) { string sortCondition = filter.PagingInfo.SortBy.Trim(); Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase); if (match.Groups["SortColumn"].Success) { string sortColumn = match.Groups["SortColumn"].Value; string sortType = match.Groups["SortType"].Success ? match.Groups["SortType"].Value : "DESC"; #region switch switch (sortColumn) { case "SysNo": filter.PagingInfo.SortBy = String.Format("{0} {1}", "SysNo", sortType); break; case "RoleName": filter.PagingInfo.SortBy = String.Format("{0} {1}", "RoleName", sortType); break; case "Status": filter.PagingInfo.SortBy = String.Format("{0} {1}", "Status", sortType); break; case "InUser": filter.PagingInfo.SortBy = String.Format("{0} {1}", "InUser", sortType); break; case "InDate": filter.PagingInfo.SortBy = String.Format("{0} {1}", "InDate", sortType); break; case "EditUser": filter.PagingInfo.SortBy = String.Format("{0} {1}", "EditUser", sortType); break; case "EditDate": filter.PagingInfo.SortBy = String.Format("{0} {1}", "EditDate", sortType); break; } #endregion } } CustomDataCommand command = DataCommandManager. CreateCustomDataCommandFromConfig("ExternalSYS_Query_VendorRole"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "SysNo DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.RoleSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RoleName", DbType.String, "@RoleName", QueryConditionOperatorType.LeftLike, filter.RoleName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); if (!string.IsNullOrEmpty(filter.PrivilegeName)) { ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, @"SELECT 1 FROM ( SELECT [RoleSysNo] FROM [IPP3].[dbo].[VendorUser_Role_Privilege] WITH(NOLOCK) WHERE [PrivilegeSysNo] IN ( SELECT [SysNo] FROM [IPP3].[dbo].[VendorUser_Privilege] WITH(NOLOCK) WHERE [Memo] LIKE @PrivilegeName ) ) AS RESULT WHERE RESULT.[RoleSysNo]=[VendorUser_Role].[SysNo]"); command.AddInputParameter("@PrivilegeName", DbType.String, filter.PrivilegeName + "%"); } command.CommandText = builder.BuildQuerySql(); var dt = command.ExecuteDataTable("Status", typeof(ValidStatus)); dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
/// <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); } }
public static QueryResult <SOInfo> SOQuery(SOQueryFilter queryFilter) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SOQuery"); using ( var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "s.SysNo ASC" : queryFilter.SortFields)) { if (queryFilter.SOSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format("(so.SysNo = {0} or sc.SoSplitMaster = {0})", queryFilter.SOSysNo)); } if (queryFilter.OrderDateBegin.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate", DbType.DateTime, "@OrderDateBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.OrderDateBegin); } if (queryFilter.OrderDateEnd.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.OrderDate", DbType.DateTime, "@OrderDateEnd", QueryConditionOperatorType.LessThan, queryFilter.OrderDateEnd.Value.Date.AddDays(1)); } if (!string.IsNullOrWhiteSpace(queryFilter.CustomerID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "cust.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, queryFilter.CustomerID); } if (queryFilter.ProductSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(@"(so.SysNo in (SELECT si.sosysno from ipp3..so_item si(nolock) WHERE si.ProductSysNo = {0}))" , queryFilter.ProductSysNo)); } if (!string.IsNullOrWhiteSpace(queryFilter.ReceivePhone)) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(so.ReceivePhone = @ReceivePhone OR so.ReceiveCellPhone = @ReceivePhone)"); command.AddInputParameter("@ReceivePhone", DbType.String, queryFilter.ReceivePhone); } if (queryFilter.SOStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.Status", DbType.Int32, "@SOStatus", QueryConditionOperatorType.Equal, (int)queryFilter.SOStatus.Value); } if (queryFilter.PayTypeSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo); } if (queryFilter.ShipTypeSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "so.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, queryFilter.ShipTypeSysNo); } if (queryFilter.ConsolidatedPaymentStatus.HasValue) { //已支付未审核 if (queryFilter.ConsolidatedPaymentStatus == ConsolidatedPaymentStatus.PaymentWaitApprove) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(netpay.Status = 0)"); } //未支付 else if (queryFilter.ConsolidatedPaymentStatus == ConsolidatedPaymentStatus.PaymentNone) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(netpay.SysNo IS NULL OR netpay.Status = -1)"); } // 匹配SOIncome状态 else { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "fs.Status = " + (int)queryFilter.ConsolidatedPaymentStatus.Value); } } if (queryFilter.MerchantSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sc.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo); } if (string.IsNullOrWhiteSpace(queryFilter.SortFields)) { queryFilter.SortFields = "so.[OrderDate] DESC"; } command.CommandText = sqlBuilder.BuildQuerySql(); List <SOInfo> resultList = command.ExecuteEntityList <SOInfo>(); int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(new QueryResult <SOInfo> { PageInfo = new PageInfo { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList }); } }
public static QueryResult <RMARequestQueryResultInfo> QueryList(RMARequestQueryFilter filter) { QueryResult <RMARequestQueryResultInfo> result = new QueryResult <RMARequestQueryResultInfo>(); CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRMARequestList"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "SysNo DESC")) { //订单编号 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOSysNo", DbType.Int32 , "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo); //订单日期 DateTime orderDateFrom, orderDateTo; if (!string.IsNullOrWhiteSpace(filter.OrderDateFrom) && DateTime.TryParse(filter.OrderDateFrom, out orderDateFrom)) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderDate", DbType.DateTime , "@OrderDateFrom", QueryConditionOperatorType.MoreThanOrEqual, orderDateFrom); } if (!string.IsNullOrWhiteSpace(filter.OrderDateTo) && DateTime.TryParse(filter.OrderDateTo, out orderDateTo)) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrderDate", DbType.DateTime , "@OrderDateTo", QueryConditionOperatorType.LessThan, orderDateTo.Date.AddDays(1)); } //顾客帐号 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CustomerID", DbType.String , "@CustomerID", QueryConditionOperatorType.Like, filter.CustomerID); //退货单编号 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RequestID", DbType.String , "@RequestID", QueryConditionOperatorType.Equal, filter.RequestID); //申请日期 DateTime requestDateFrom, requestDateTo; if (!string.IsNullOrWhiteSpace(filter.RequestDateFrom) && DateTime.TryParse(filter.RequestDateFrom, out requestDateFrom)) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime , "@RequestDateFrom", QueryConditionOperatorType.MoreThanOrEqual, requestDateFrom); } if (!string.IsNullOrWhiteSpace(filter.RequestDateTo) && DateTime.TryParse(filter.RequestDateTo, out requestDateTo)) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.CreateTime", DbType.DateTime , "@RequestDateTo", QueryConditionOperatorType.LessThan, requestDateTo.Date.AddDays(1)); } //退换货状态 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.Status", DbType.Int32 , "@Status", QueryConditionOperatorType.Equal, filter.Status); //商品编号 if (!string.IsNullOrWhiteSpace(filter.ProductSysNo)) { builder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, String.Format(@" EXISTS (SELECT TOP 1 1 FROM dbo.RMA_Request_Item WITH(NOLOCK) INNER JOIN dbo.RMA_Register WITH(NOLOCK) ON RMA_Request_Item.RegisterSysno = RMA_Register.Sysno INNER JOIN dbo.Product WITH(NOLOCK) ON Product.Sysno = RMA_Register.ProductSysNo WHERE RMA_Request_Item.RequestSysno =request.Sysno AND RMA_Register.ProductSysNo= {0})", filter.ProductSysNo)); } //电话号码 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.Phone", DbType.String , "@Phone", QueryConditionOperatorType.Equal, filter.ReceiverPhone); //商家编号 builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "request.MerchantSysno", DbType.Int32 , "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); command.CommandText = builder.BuildQuerySql(); StringBuilder sb = new StringBuilder(); List <RMARequestQueryResultInfo> resultList = command.ExecuteEntityList <RMARequestQueryResultInfo>((s, t) => { sb.Clear(); if (!String.IsNullOrWhiteSpace((string)s["RequestType"])) { string[] types = s["RequestType"].ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < types.Length; i++) { sb.AppendFormat("{0}/", EnumHelper.GetDescription((RMARequestType)Enum.Parse(typeof(RMARequestType), types[i]))); } t.RequestType = sb.ToString().TrimEnd('/'); } t.Status = EnumHelper.GetDescription((RMARequestStatus)Enum.Parse(typeof(RMARequestStatus), s["Status"].ToString())); }); int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); result.PageInfo = new PageInfo() { PageIndex = filter.PageIndex, PageSize = filter.PageSize, TotalCount = totalCount, SortBy = filter.SortFields }; result.ResultList = resultList; } return(result); }
/// <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 }); } }
/// <summary> /// 产品咨询查询 /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <ProductMatchedTradingQueryBasicInfo> QueryProductMatchedTradingBasicInfoList(ProductMatchedTradingQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.SortFields; pagingEntity.MaximumRows = filter.PageSize; pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductMatchedTrading_GetProductMatchedTradingDetailList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, string.IsNullOrEmpty(pagingEntity.SortField) ? pagingEntity.SortField : "A.SysNo DESC")) { if (filter.SellerSysNo.HasValue) { dataCommand.AddInputParameter("@SellerSysNo", DbType.String, filter.SellerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.MerchantSysNo", DbType.String, "@SellerSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); } //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID); //中蛋故有 if (filter.VendorType == 1) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "VD.VendorType=0"); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VD.SysNo", DbType.Int32, "@VendorType", QueryConditionOperatorType.Equal, filter.VendorType); } if (filter.ProductGroupNo != 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.ProductGroupSysno", DbType.Int32, "@GroupID", QueryConditionOperatorType.Equal, filter.ProductGroupNo); } if (filter.IsByGroup) { if (filter.ProductSysNo > 0) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "B.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))"); } } else { if (!string.IsNullOrEmpty(filter.ProductID)) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE ProductID = '" + filter.ProductID + "')"); } } sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo); sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.EditDateFrom, filter.EditDateTo); //商品类别 if (filter.Category1SysNo != null && filter.Category2SysNo != null && filter.Category3SysNo != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE C3SysNo = " + filter.Category3SysNo + " AND CompanyCode=" + filter.CompanyCode + ")"); } else if (filter.Category1SysNo != null && filter.Category2SysNo != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c WITH (NOLOCK) WHERE p.C3SysNo = c.SysNo AND c.C2SysNo =" + filter.Category2SysNo + " AND P.CompanyCode=" + filter.CompanyCode + " AND C.CompanyCode=" + filter.CompanyCode + ")"); } else if (filter.Category1SysNo != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c3 WITH (NOLOCK),ipp3.dbo.Category2 c2 WITH (NOLOCK) WHERE p.C3SysNo = c3.SysNo AND c3.C2SysNo = c2.SysNo AND c2.C1SysNo = " + filter.Category1SysNo + " AND P.companycode=" + filter.CompanyCode + " AND c2.companycode=" + filter.CompanyCode + " AND c3.companycode=" + filter.CompanyCode + ")"); } if (filter.PMUserSysNo != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE PMUserSysNo = " + filter.PMUserSysNo + ")"); } if (filter.ProductStatus != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE Status = " + filter.ProductStatus + ")"); } if (!string.IsNullOrEmpty(filter.Status)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status.Substring(0, 1)); if (filter.Status.Length > 1) { if (filter.Status.Substring(2, 1) == "1") { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser='******'"); } else if (filter.Status.Substring(2, 1) == "2") { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser<>'System'"); } } } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, filter.Type); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Content", DbType.String, "@Content", QueryConditionOperatorType.Like, filter.Content); if (filter.CustomerSysNo != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo=" + filter.CustomerSysNo + ""); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, filter.EditUser); if (filter.CustomerCategory != null) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo IN ( SELECT SysNo FROM ipp3.dbo.Customer WITH (NOLOCK) WHERE CompanyCustomer = " + filter.CustomerCategory + ")"); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); //CodeNamePairColumnList pairList = new CodeNamePairColumnList(); //pairList.Add("Status", "MKT", "ReplyStatus");//咨询状态 ////pairList.Add("ReferenceType", "MKT", "CommentsCategory"); //var dt = cmd.ExecuteDataTable(pairList); //totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); List <ProductMatchedTradingQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductMatchedTradingQueryBasicInfo>(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(list); } }
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); } }