/// <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> /// 查询大于等于30天没登录过的用户 /// </summary> /// <param name="filter">查询条件</param> /// <returns></returns> public static StatementResult<ArrayList> QueryThirtyDayNotLoginCustomers(QueryCategoryCustomersFilter filter) { StatementResult<ArrayList> result = new StatementResult<ArrayList>(); result.ServicePageIndex = filter.ServicePageIndex; result.PageSize = filter.PageSize; PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryThirtyDayNotLoginCustomers"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "CustomerID DESC")) { cmd.CommandText = sqlBuilder.BuildQuerySql(); result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable()); result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return result; } }
/// <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); } }
/// <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); } }
private void AddReceiveByVendorParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); if (filter.VendorSysNo.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.VendorNumber", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); } if (filter.PMUserSysNo.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ruleinfo.PM", DbType.Int32, "@PMUserSysNo", QueryConditionOperatorType.Equal, filter.PMUserSysNo); } if (!string.IsNullOrEmpty(filter.EIMSType)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.EIMSType", DbType.String, "@EIMSType", QueryConditionOperatorType.Equal, filter.EIMSType); } if (filter.ExpiredDays.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "DATEDIFF(DAY,invoice.DueDate,GETDATE())", DbType.Int32, "@ExpriedDays", QueryConditionOperatorType.MoreThan, filter.ExpiredDays); } cmd.AddOutParameter("@SumNoReceivedAmount", DbType.Double, sizeof(double)); cmd.AddOutParameter("@SumOverDueAmount", DbType.Double, sizeof(double)); cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize); cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex); cmd.CommandText = sb.BuildQuerySql(); }
private void AddReceiveByYearParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "accbyrule.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); if (filter.VendorSysNo.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "accbyrule.VendorNumber", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); } if (!string.IsNullOrEmpty(filter.EIMSType)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "accbyrule.EIMSType", DbType.String, "@EIMSType", QueryConditionOperatorType.Equal, filter.EIMSType); } if (filter.Year.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "substring(accbyrule.AccruePeriod,0,5)", DbType.String, "@Year", QueryConditionOperatorType.Equal, filter.Year); } cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize); cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex); cmd.CommandText = sb.BuildQuerySql(); }
public DataTable Query(PostIncomeQueryFilter query, out int totalCount) { DataTable result = null; PagingInfoEntity pagingInfo = new PagingInfoEntity(); if (query.PagingInfo != null) { MapSortField(query.PagingInfo); pagingInfo.MaximumRows = query.PagingInfo.PageSize; pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize; pagingInfo.SortField = query.PagingInfo.SortBy; } CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPostIncomeList"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingInfo, "a.SysNo desc")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, query.SOSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeAmt", DbType.Decimal, "@IncomeAmt", QueryConditionOperatorType.Equal, query.IncomeAmt); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate", DbType.Date, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.CreateDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate", DbType.Date, "@CreateDateTo", QueryConditionOperatorType.LessThan, query.CreateDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate", DbType.Date, "@IncomeDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.IncomeDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate", DbType.Date, "@IncomeDateTo", QueryConditionOperatorType.LessThan, query.IncomeDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayBank", DbType.String, "@PayBank", QueryConditionOperatorType.Like, query.PayBank); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeBank", DbType.String, "@IncomeBank", QueryConditionOperatorType.Like, query.IncomeBank); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.DisplayName", DbType.String, "@CreateUser", QueryConditionOperatorType.Like, query.CreateUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "e.DisplayName", DbType.String, "@AuditUser", QueryConditionOperatorType.Like, query.AuditUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayUser", DbType.String, "@PayUser", QueryConditionOperatorType.Like, query.PayUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode); if (!string.IsNullOrEmpty(query.ConfirmedSOSysNoList)) { sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.SysNo", QueryConditionOperatorType.In, string.Format(@"SELECT a.PostIncomeSysNo FROM [OverseaInvoiceReceiptManagement].[dbo].[PostIncomeConfirm] a WITH(NOLOCK) WHERE a.ConfirmedSoSysNo IN ({0}) AND a.Status <> 'C'" , query.ConfirmedSOSysNoList.Replace('.', ','))); } if (query.HandleStatus != null) { switch (query.HandleStatus.Value) { case PostIncomeHandleStatusUI.UnConfirmed: sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus", DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.NotEqual, PostIncomeStatus.Confirmed); break; case PostIncomeHandleStatusUI.UnHandled: sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus", DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.WaitingHandle); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus", DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.Equal, PostIncomeStatus.Confirmed); break; case PostIncomeHandleStatusUI.Handled: sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus", DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.Handled); break; default: break; } } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumColumns = new EnumColumnList(); enumColumns.Add("HandleStatus", typeof(PostIncomeHandleStatus)); enumColumns.Add("ConfirmStatus", typeof(PostIncomeStatus)); result = dataCommand.ExecuteDataTable(enumColumns); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } return(result); }
public static List <ProductCategoryQueryBasicInfo> QueryProductCategory(ProductCategoryQueryFilter queryCriteria, out int totalCount) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductCategory"); var pagingInfo = new PagingInfoEntity { StartRowIndex = queryCriteria.PageIndex * queryCriteria.PageSize, MaximumRows = queryCriteria.PageSize, SortField = queryCriteria.SortFields, }; using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, string.IsNullOrEmpty(pagingInfo.SortField) ? pagingInfo.SortField : "P.ProductSysNo DESC")) { if (!String.IsNullOrEmpty(queryCriteria.ProductSysNo)) { dataCommand.AddInputParameter("@ProductID", DbType.String, queryCriteria.ProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductSysNo", DbType.String, "@ProductID", QueryConditionOperatorType.Like, queryCriteria.ProductSysNo); } if (!String.IsNullOrEmpty(queryCriteria.ProductName)) { dataCommand.AddInputParameter("@ProductName", DbType.String, queryCriteria.ProductName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductName", DbType.String, "@ProductName", QueryConditionOperatorType.Like, queryCriteria.ProductName); } if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo)) { dataCommand.AddInputParameter("@AttachmentID", DbType.String, queryCriteria.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.AttachmentSysNo", DbType.String, "@ProductAttachmentSysNo", QueryConditionOperatorType.Like, queryCriteria.ProductAttachmentSysNo); } //if (!String.IsNullOrEmpty(queryCriteria.ProductAttachmentSysNo)) //{ // dataCommand.AddInputParameter("@AttachmentName", DbType.String, queryCriteria.AttachmentName); // sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "P.AttachmentName", // DbType.String, "@AttachmentName", // QueryConditionOperatorType.Like, // queryCriteria.AttachmentName); //} if (!String.IsNullOrEmpty(queryCriteria.EditUser)) { dataCommand.AddInputParameter("@EditUser", DbType.String, queryCriteria.EditUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.InUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, queryCriteria.EditUser); } //sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateEnd, queryCriteria.InDateStart); if (queryCriteria.InDateStart != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateStart", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateStart ); } if (queryCriteria.InDateEnd != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateEnd", QueryConditionOperatorType.LessThanOrEqual, queryCriteria.InDateEnd ); } if (queryCriteria.SellerSysNo.HasValue) { dataCommand.AddInputParameter("@SellerSysNo", DbType.String, queryCriteria.SellerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.SellerID", DbType.String, "@SellerSysNo", QueryConditionOperatorType.Equal, queryCriteria.SellerSysNo); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); List <ProductCategoryQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductCategoryQueryBasicInfo>(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(list); } }
/// <summary> /// 查询仓库 /// </summary> /// <returns></returns> public virtual DataTable QueryWarehouse(WarehouseQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = SortFieldMapping(filter.PageInfo.SortBy); pagingEntity.MaximumRows = filter.PageInfo.PageSize; pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryWarehouseListByCondition");//说明:EC_Central产品化时 调用的 SQL为:Inventory_QueryWarehouse 现在是中蛋定制化 不存在渠道仓库所以保持同IPP一致 using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo ASC")) { bool isSysNo = false; //编号格式验证 if (filter.SysNo != null && Regex.IsMatch(filter.SysNo, @"^[,\. ]*\d+[\d,\. ]*$")) { filter.SysNo = String.Join(",", filter.SysNo.Split(new char[] { '.', ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)); isSysNo = true; } else { filter.SysNo = null; } if (filter.WarehouseID != null && Regex.IsMatch(filter.WarehouseID, @"^[, ]*\w+[\w-#, ]*$")) { filter.WarehouseID = "'" + String.Join("','", filter.WarehouseID.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)) + "'"; isSysNo = true; } else { filter.WarehouseID = null; } if (isSysNo) { sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.SysNo", QueryConditionOperatorType.In, filter.SysNo); sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.StockID", QueryConditionOperatorType.In, filter.WarehouseID); sb.ConditionConstructor.EndGroupCondition(); } sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockName", DbType.String, "@WarehouseName", QueryConditionOperatorType.LeftLike, filter.WarehouseName); //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "a.WarehouseOwnerSysNo", DbType.Int32, "@WarehouseOwnerSysNo", // QueryConditionOperatorType.Equal, filter.OwnerSysNo); //if (!filter.OwnerSysNo.HasValue && filter.OwnerSysNo!=0) //{ // sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND // , " ((a.WarehouseOwnerSysNo IN ( SELECT a.SysNo FROM OverseaInventoryManagement.dbo.WarehouseOwner a WITH(NOLOCK)WHERE a.OwnerType=1)) OR a.WarehouseOwnerSysNo IS NULL)"); //} sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.[Status]", DbType.Int32, "@WarehouseStatus", QueryConditionOperatorType.Equal, filter.WarehouseStatus); //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "a.[WarehouseType]", DbType.Int32, "@WarehouseType", // QueryConditionOperatorType.Equal, filter.WarehouseType); //sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "a.[WarehouseArea]", DbType.Int32, "@WarehouseArea", // QueryConditionOperatorType.Equal, filter.WarehouseArea); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); EnumColumnList enumColumnList = new EnumColumnList(); enumColumnList.Add("WarehouseStatus", typeof(ValidStatus)); cmd.CommandText = sb.BuildQuerySql(); var resultData = cmd.ExecuteDataTable(enumColumnList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(resultData); } }
private void AddParameter4GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "a.OutTime" , DbType.DateTime , "@OutDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , filter.OutDateFrom); if (filter.OutDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "a.OutTime" , DbType.DateTime , "@OutDateTo" , QueryConditionOperatorType.LessThanOrEqual , filter.OutDateTo.Value.AddDays(1).AddMinutes(-1)); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "sl.InvoiceCreateDate" , DbType.DateTime , "@InvoiceCreateTimeFrom" , QueryConditionOperatorType.MoreThanOrEqual , filter.InvoiceDateFrom); if (filter.InvoiceDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "sl.InvoiceCreateDate" , DbType.DateTime , "@InvoiceCreateTimeTo" , QueryConditionOperatorType.LessThanOrEqual , filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1)); } if (!string.IsNullOrEmpty(filter.OrderID)) { List <string> orderIDList = new List <string>(); foreach (string item in filter.OrderID.Split('.')) { orderIDList.Add(item.Trim()); } sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "a.SOID", DbType.String, orderIDList); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "sl.InvoiceNo" , DbType.String , "@InvoiceNumber" , QueryConditionOperatorType.Like , filter.InvoiceNumber); int intTry; decimal decimalTry; if (!string.IsNullOrEmpty(filter.CustomerName)) { if (int.TryParse(filter.CustomerName, out intTry) || decimal.TryParse(filter.CustomerName, out decimalTry)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "ct.CustomerName" , DbType.String , "@CustomerName" , QueryConditionOperatorType.Equal , filter.CustomerName); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "ct.CustomerName" , DbType.String , "@CustomerName" , QueryConditionOperatorType.LeftLike , filter.CustomerName); } } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "scs.SOType" , DbType.Int32 , "@SOType" , QueryConditionOperatorType.Equal , filter.SOType); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "a.CompanyCode" , DbType.AnsiStringFixedLength , "@CompanyCode" , QueryConditionOperatorType.Equal , filter.CompanyCode); }
/// <summary> /// 分页查询商品 /// </summary> /// <param name="queryFilter"></param> /// <returns></returns> public static QueryResult <ProductCommonInfo> QueryCommonProduct(ProducCommonQueryFilter queryFilter) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCommonProduct"); using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "P.[SysNo] ASC" : queryFilter.SortFields)) { if (!string.IsNullOrEmpty(queryFilter.VendorSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.[SysNo]", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo); } if (!string.IsNullOrEmpty(queryFilter.BrandSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[BrandSysNo]", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo); } if (!string.IsNullOrEmpty(queryFilter.ProductID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Like, queryFilter.ProductID); } if (!string.IsNullOrEmpty(queryFilter.ProductTitle)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductTitle]", DbType.String, "@ProductTitle", QueryConditionOperatorType.Like, queryFilter.ProductTitle); } if (!string.IsNullOrEmpty(queryFilter.FrontCategorySysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[FrontCategorySysNo]", DbType.Int32 , "@FrontCategorySysNo", QueryConditionOperatorType.Like, Convert.ToInt32(queryFilter.FrontCategorySysNo)); } if (!string.IsNullOrEmpty(queryFilter.Status)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status); } if (queryFilter.CreateTimeBegin != null && queryFilter.CreateTimeBegin != new DateTime()) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.ToString("yyyy-MM-dd")); } if (queryFilter.CreateTimeEnd != null && queryFilter.CreateTimeEnd != new DateTime()) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeEnd", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeEnd.AddDays(1).ToString("yyyy-MM-dd")); } if (!string.IsNullOrEmpty(queryFilter.GroupName)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PG.[ProductName]", DbType.String, "@GroupName", QueryConditionOperatorType.Like, queryFilter.GroupName); } if (!string.IsNullOrEmpty(queryFilter.ProductTradeType)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[ProductTradeType]", DbType.String, "@ProductTradeType", QueryConditionOperatorType.Equal, queryFilter.ProductTradeType); } command.CommandText = sqlBuilder.BuildQuerySql(); List <ProductCommonInfo> resultList = command.ExecuteEntityList <ProductCommonInfo>(); int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(new QueryResult <ProductCommonInfo>() { PageInfo = new PageInfo() { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList }); } }
public static QueryResponseDTO <bool> DeleteProductInfo(ProductDTO dto) { QueryResponseDTO <bool> response = new QueryResponseDTO <bool>(); response.ResultEntity = false; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveProductInfo"); foreach (var model in dto.ProductModel) { using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC")) { if (null != dto) { if (!string.IsNullOrEmpty(model.ProductID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ProductID", DbType.AnsiString, "@ProductID", QueryConditionOperatorType.Equal, model.ProductID); } if (model.Stock > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Stock", DbType.Int32, "@Stock", QueryConditionOperatorType.MoreThanOrEqual, model.Stock); } if (!string.IsNullOrEmpty(model.CategoryID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "CategoryID", DbType.AnsiString, "@CategoryID", QueryConditionOperatorType.Equal, model.CategoryID); } sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "IsPublish", DbType.Boolean, "@IsPublish", QueryConditionOperatorType.Equal, model.IsPublish); if (model.OriginalPrice > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "OriginalPrice", DbType.AnsiString, "@OriginalPrice", QueryConditionOperatorType.MoreThanOrEqual, model.OriginalPrice); } if (dto.LastEditDate != null) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "LastEditDate", DbType.DateTime, "@LastEditDate", QueryConditionOperatorType.MoreThanOrEqual, model.LastEditDate); } if (model.Discount > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Discount", DbType.Int32, "@Discount", QueryConditionOperatorType.MoreThanOrEqual, model.Discount); } } //QueryData dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var updateRows = dataCommand.ExecuteNonQuery(); if (updateRows > 0) { response.ResultEntity = true; response.Code = ResponseStaticModel.Code.OK; response.Message = ResponseStaticModel.Message.OK; } else { response.ResultEntity = false; response.Code = ResponseStaticModel.Code.FAILED; response.Message = ResponseStaticModel.Message.FAILED; } } } return(response); }
public static QueryResponseDTO <bool> DeleteTransactionInfo(TransactionDTO dto) { QueryResponseDTO <bool> response = new QueryResponseDTO <bool>(); response.ResultEntity = false; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveTransactionInfo"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC")) { if (null != dto) { if (!string.IsNullOrEmpty(dto.TransactionNumber)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "TransactionNumber", DbType.AnsiString, "@TransactionNumber", QueryConditionOperatorType.Equal, dto.TransactionNumber); } if (!string.IsNullOrEmpty(dto.FromUser)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "FromUser", DbType.AnsiString, "@FromUser", QueryConditionOperatorType.Equal, dto.FromUser); } if (!string.IsNullOrEmpty(dto.ToUser)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ToUser", DbType.AnsiString, "@ToUser", QueryConditionOperatorType.Equal, dto.ToUser); } if (dto.State > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "State", DbType.Int32, "@State", QueryConditionOperatorType.Equal, dto.State); } if (dto.Status > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, dto.Status); } if (dto.Number > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Number", DbType.Int32, "@Number", QueryConditionOperatorType.Equal, dto.Number); } } //QueryData dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var updateRows = dataCommand.ExecuteNonQuery(); if (updateRows > 0) { response.ResultEntity = true; response.Code = ResponseStaticModel.Code.OK; response.Message = ResponseStaticModel.Message.OK; } else { response.ResultEntity = false; response.Code = ResponseStaticModel.Code.FAILED; response.Message = ResponseStaticModel.Message.FAILED; } } return(response); }
/// <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 }); } }
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(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 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 }); } }
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); } } }
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); } }
private static void BuilCondition <Q>(Q filter, string dataType, List <FilterField> filterFields, DynamicQuerySqlBuilder sqlBuilder) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "r.BusinessDataType", DbType.String, "@BusinessDataType", QueryConditionOperatorType.Equal, dataType); List <string> changeTypes = new List <string>() { "A", "U" }; sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "r.ChangeType", DbType.String, changeTypes); int index = 0; filterFields.ForEach(p => { object parameterValue = Invoker.PropertyGet(filter, p.Name); string[] pNames = p.ValuePath.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); string path = string.Format("(//{0}/text())[1]", pNames.Join("/")); DbType dbType = GetDbType(p.DBType); string field = string.Format("r.BusinessData.value('{0}','{1}')", path, p.DBType); QueryConditionOperatorType operatorType = GetEnum <QueryConditionOperatorType>(p.OperatorType); QueryConditionRelationType relationType = GetEnum <QueryConditionRelationType>(p.RelationType); sqlBuilder.ConditionConstructor.AddCondition(relationType, field, dbType, "@Parameter" + index.ToString(), operatorType, parameterValue); index++; }); }
/// <summary> /// 扣款项维护列表 /// </summary> /// <param name="queryFilter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable LoadDeductInfo(DeductQueryFilter queryFilter, out int totalCount) { if (queryFilter == null) { totalCount = -1; return(null); } CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryDeductList"); if (null != queryFilter.PageInfo.SortBy) { if (queryFilter.PageInfo.SortBy.IndexOf("[Status]") > -1) { queryFilter.PageInfo.SortBy = "D." + queryFilter.PageInfo.SortBy; } } PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Indate DESC")) { if (!string.IsNullOrEmpty(queryFilter.Name)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Name", DbType.String, "@Name", QueryConditionOperatorType.Like, queryFilter.Name); } if (queryFilter.DeductType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DeductType", DbType.Int32, "@DeductType", QueryConditionOperatorType.Equal, queryFilter.DeductType.Value); } if (queryFilter.AccountType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AccountType", DbType.Int32, "@AccountType", QueryConditionOperatorType.Equal, queryFilter.AccountType.Value); } if (queryFilter.DeductMethod.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DeductMethod", DbType.Int32, "@DeductMethod", QueryConditionOperatorType.Equal, queryFilter.DeductMethod.Value); } if (queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value); } command.CommandText = sqlBuilder.BuildQuerySql(); command.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex); command.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize); EnumColumnList columnEnums = new EnumColumnList(); columnEnums.Add("DeductType", typeof(DeductType)); columnEnums.Add("AccountType", typeof(AccountType)); columnEnums.Add("DeductMethod", typeof(DeductMethod)); columnEnums.Add("Status", typeof(Status)); DataTable dt = command.ExecuteDataTable(columnEnums); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
private void AddParameter4InvoiceDetailReportQuery(InvoiceDetailReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "OutTime" , DbType.DateTime , "@OutDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , filter.OutDateFrom); if (filter.OutDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "OutTime" , DbType.DateTime , "@OutDateTo" , QueryConditionOperatorType.LessThanOrEqual , filter.OutDateTo.Value.AddDays(1).AddMinutes(-1)); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "InvoiceDate" , DbType.DateTime , "@InvoiceDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , filter.InvoiceDateFrom); if (filter.InvoiceDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "InvoiceDate" , DbType.DateTime , "@InvoiceDateTo" , QueryConditionOperatorType.LessThanOrEqual , filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1)); } if (!string.IsNullOrEmpty(filter.OrderID)) { List <string> orderIDList = new List <string>(); foreach (string item in filter.OrderID.Split('.')) { orderIDList.Add(item.Trim()); } sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "OrderID", DbType.String, orderIDList); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "InvoiceNumber" , DbType.String , "@InvoiceNumber" , QueryConditionOperatorType.Like , filter.InvoiceNumber); var customerNameString = filter.CustomerName; int intTry; decimal decimalTry; if (!string.IsNullOrEmpty(customerNameString)) { if (int.TryParse(customerNameString, out intTry) || decimal.TryParse(customerNameString, out decimalTry)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "CustomerName" , DbType.String , "@CustomerName" , QueryConditionOperatorType.Equal , filter.CustomerName); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "CustomerName" , DbType.String , "@CustomerName" , QueryConditionOperatorType.Like , filter.CustomerName); } } }
public DataTable Query(FreeShippingChargeRuleQueryFilter filter, out int totalCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFreeShippingChargeRule"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, null, "[SysNo] DESC")) { if (filter.StartDate.HasValue || filter.EndDate.HasValue) { builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); #region 构造时间条件 builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR); // s1 ≤ start && t1 ≥ start builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date, "@StartDate1", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date, "@EndDate1", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate); builder.ConditionConstructor.EndGroupCondition(); builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR); // s1 ≥ start && t1 ≤ end builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "StartDate", DbType.Date, "@StartDate2", QueryConditionOperatorType.LessThanOrEqual, filter.StartDate); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date, "@EndDate2", QueryConditionOperatorType.MoreThanOrEqual, filter.EndDate); builder.ConditionConstructor.EndGroupCondition(); builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR); // s1 ≤ end && t1 ≥ end builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date, "@StartDate3", QueryConditionOperatorType.MoreThanOrEqual, filter.StartDate); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndDate", DbType.Date, "@EndDate3", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate); builder.ConditionConstructor.EndGroupCondition(); #endregion builder.ConditionConstructor.EndGroupCondition(); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal, "@AmtFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AmtFrom); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingValue", DbType.Decimal, "@AmtTo", QueryConditionOperatorType.LessThanOrEqual, filter.AmtTo); if (filter.PayTypeSysNo.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayTypeSettingValue", DbType.AnsiString, "@PayTypeSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.PayTypeSysNo)); } if (filter.ShipAreaID.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ShipAreaSettingValue", DbType.AnsiString, "@ShipAreaSettingValueLike", QueryConditionOperatorType.Like, string.Format(",{0},", filter.ShipAreaID)); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "AmountSettingType", DbType.Int32, "@AmountSettingType", QueryConditionOperatorType.Equal, filter.AmountSettingType); command.CommandText = builder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("AmountSettingType", typeof(FreeShippingAmountSettingType)); enumList.Add("Status", typeof(FreeShippingAmountSettingStatus)); DataTable dt = command.ExecuteDataTable(enumList); if (dt != null && dt.Rows.Count > 0) { string areaSettingName = String.Empty; string[] areaNameParts; foreach (DataRow row in dt.Rows) { if (!(row["AreaSettingName"] is DBNull) && row["AreaSettingName"] != null) { areaSettingName = Convert.ToString(row["AreaSettingName"]); areaNameParts = areaSettingName.Split(new char[] { ',' }, StringSplitOptions.None); if (areaNameParts.Length > 4) { row["AreaSettingName"] = string.Format("{0}等{1}个地区", string.Join(",", areaNameParts.ToList().GetRange(0, 4)), areaNameParts.Length); } } } } totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
private void AddParameter4InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder) { //SOSysNo var sosysNoString = filter.SOSysNo; if (!string.IsNullOrEmpty(sosysNoString)) { List <int> sosysNOList = new List <int>(); foreach (string item in sosysNoString.Split('.')) { sosysNOList.Add(int.Parse(item.Trim())); } sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "so.SysNo", DbType.Int32, sosysNOList); } //SOStatusCode(已出库) sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.Status" , DbType.Int32 , "@SOStatusCode" , QueryConditionOperatorType.Equal , ECCentral.BizEntity.SO.SOStatus.OutStock); //IsVAT是否增票 sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.IsVAT" , DbType.Int32 , "@IsVAT" , QueryConditionOperatorType.Equal , filter.IsVAT); //CreateDateFrom var createdateFrom = filter.CreateDateFrom; if (createdateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.OrderDate" , DbType.DateTime , "@CreateDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , createdateFrom.Value); } //CreateDateTo var createdateTo = filter.CreateDateTo; if (createdateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.OrderDate" , DbType.DateTime , "@CreateDateTo" , QueryConditionOperatorType.LessThanOrEqual , createdateTo.Value.AddDays(1).AddSeconds(-1)); } //OutDateFrom var outdateFrom = filter.OutDateFrom; if (outdateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.OutTime" , DbType.DateTime , "@OutDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , outdateFrom.Value); } //OutDateTo var outdateTo = filter.OutDateTo; if (outdateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.OutTime" , DbType.DateTime , "@OutDateTo" , QueryConditionOperatorType.LessThanOrEqual , outdateTo.Value.AddDays(1).AddSeconds(-1)); } //AuditDateFrom var auditDateFrom = filter.AuditDateFrom; if (auditDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.AuditTime" , DbType.DateTime , "@AuditDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , auditDateFrom.Value); } //AuditDateTo var auditdateTo = filter.AuditDateTo; if (auditdateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "so.AuditTime" , DbType.DateTime , "@AuditDateTo" , QueryConditionOperatorType.LessThanOrEqual , auditdateTo.Value.AddDays(1).AddSeconds(-1)); } //StockSysNo var stockSysNOString = filter.StockSysNo; if (string.IsNullOrEmpty(stockSysNOString)) { //1:全部查询:只查询“发票打印”支持的分仓,从配置文件中取值,即StockSysNO="51,59,90,99"; string invoicePrintstocks = this.GetInvoicePrintStocks(filter.CompanyCode); List <string> stockNOList = new List <string>(); foreach (string item in invoicePrintstocks.Split(',')) { stockNOList.Add(item.Trim()); } sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND , "sb.WareHouseNumber" , DbType.AnsiStringFixedLength , stockNOList); } else { //非全部查询 if (stockSysNOString.IndexOf(',') > 0) { //2:上海仓:实际查询为“嘉定仓”+“宝山仓”,即StockSysNO="51,59" List <string> stockNOList = new List <string>(); foreach (string item in stockSysNOString.Split(',')) { stockNOList.Add(item.Trim()); } sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND , "sb.WareHouseNumber" , DbType.AnsiStringFixedLength , stockNOList); } else { //3: 其他仓:即StockSysNO="90" 或者 StockSysNo="99" sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "sb.WareHouseNumber" , DbType.AnsiStringFixedLength , "@StockSysNo" , QueryConditionOperatorType.Equal , stockSysNOString); } } //MerchantSysNo sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "scs.MerchantSysNo" , DbType.Int32 , "@MerchantSysNo" , QueryConditionOperatorType.Equal , filter.VendorSysNo); //InvoiceType sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "scs.InvoiceType" , DbType.AnsiStringFixedLength , "@InvoiceType" , QueryConditionOperatorType.Equal , filter.InvoiceType); //StockType sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "scs.StockType" , DbType.AnsiStringFixedLength , "@StockType" , QueryConditionOperatorType.Equal , filter.StockType); //ShippingType sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "scs.ShippingType" , DbType.AnsiStringFixedLength , "@ShippingType" , QueryConditionOperatorType.Equal , filter.ShippingType); //CompanyCode sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "sb.CompanyCode" , DbType.AnsiStringFixedLength , "@CompanyCode" , QueryConditionOperatorType.Equal , filter.CompanyCode); }
private void AddARReceiveDetialsParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ruleinfo.Status", DbType.String, "@RuleStatus", QueryConditionOperatorType.Equal, "O"); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.Status", DbType.String, "@InvoiceStatus", QueryConditionOperatorType.Equal, "O"); if (filter.VendorSysNo.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.VendorNumber", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); } if (!string.IsNullOrEmpty(filter.EIMSType)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "invoice.EIMSType", DbType.String, "@EIMSType", QueryConditionOperatorType.Equal, filter.EIMSType); } if (filter.ProductCategory1.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "item.ItemCategory1", DbType.Int32, "@ProductCategory1", QueryConditionOperatorType.Equal, filter.ProductCategory1); } if (filter.ProductCategory2.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "item.ItemCategory2", DbType.Int32, "@ProductCategory2", QueryConditionOperatorType.Equal, filter.ProductCategory2); } cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize); cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex); cmd.CommandText = sb.BuildQuerySql(); }
public static StorePage QueryStorePage(StorePageFilter filter) { CustomDataCommand cmd; if (filter.IsPreview) { //preview cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPreview"); using (var sqlBuild = new DynamicQuerySqlBuilder(cmd)) { sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "page.[SellerSysNo]", DbType.Int32, "@SellerSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "page.[SysNo]", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.PublishPageSysNo); sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "page.[PageTypeKey]", DbType.String, "@PageType", QueryConditionOperatorType.Equal, filter.PageType); cmd.CommandText = sqlBuild.BuildQuerySql(); } } else { //not preview cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageWithPublish"); #region build condition using (var sqlBuild = new DynamicQuerySqlBuilder(cmd)) { sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.[SellerSysNo]", DbType.Int32, "@SellerSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.[SysNo]", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.PublishPageSysNo); sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[PageTypeKey]", DbType.String, "@PageType", QueryConditionOperatorType.Equal, filter.PageType); sqlBuild.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 1); cmd.CommandText = sqlBuild.BuildQuerySql(); } #endregion } #if DEBUG Console.WriteLine("Query Store Page Command is : {0}", cmd.CommandText); #endif var result = cmd.ExecuteEntity <StorePage>(); if (result == null) { return(null); } var page = SerializationUtility.JsonDeserialize2 <StorePage>(result.DataValue); page.SysNo = result.SysNo.Value; page.SellerSysNo = filter.SellerSysNo.Value; var pageTheme = QueryStorePageTheme(page.StorePageTemplate.StorePageThemeSysNo); if (pageTheme != null) { page.StorePageTemplate.StorePageThemeCssUrl = pageTheme.CssResUrl; } page.StorePageType = QueryStorePageType(page.StorePageType.Key); return(page); }
public static QueryResponseDTO <List <ReviewModel> > GetReviewInfo(ReviewDTO dto) { QueryResponseDTO <List <ReviewModel> > responseDTO = new QueryResponseDTO <List <ReviewModel> >(); CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetReviewInfo"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC")) { if (null != dto) { if (!string.IsNullOrEmpty(dto.UserID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "UserID", DbType.AnsiString, "@UserID", QueryConditionOperatorType.Equal, dto.UserID); } if (!string.IsNullOrEmpty(dto.ReviewID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID); } if (!string.IsNullOrEmpty(dto.ProductOROrderID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID); } if (null != dto.ReviewDate) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate); } if (dto.Score > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score); } if (dto.FavourCount > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount); } if (dto.OpposeCount > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount); } sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed); } //QueryData dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var result = dataCommand.ExecuteEntityList <ReviewModel>(); responseDTO.ResultEntity = result; } responseDTO.TotalCount = dto.PagingInfo.TotalCount; responseDTO.Code = ResponseStaticModel.Code.OK; responseDTO.Message = ResponseStaticModel.Message.OK; return(responseDTO); }
/// <summary> /// 查询奖品发放记录 /// </summary> /// <param name="filter">查询条件</param> /// <returns></returns> public static QueryResult<GiftsGrantRecord> QueryGiftsGrantRecord(GiftsGrantRecordQueryFilter filter) { QueryResult<GiftsGrantRecord> result = new QueryResult<GiftsGrantRecord>(); result.ServicePageIndex = filter.ServicePageIndex; result.PageSize = filter.PageSize; PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftsGrantRecord"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "M.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GiftSysNo", DbType.Int32, "@GiftSysNo", QueryConditionOperatorType.Equal, filter.GiftSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantCounts", DbType.Int64, "@GrantCounts", QueryConditionOperatorType.Like, filter.GrantCounts); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantDate", DbType.DateTime, "@GrantDate", QueryConditionOperatorType.Like, filter.GrantDate); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPerson", DbType.String, "@GrantPerson", QueryConditionOperatorType.Like, filter.GrantPerson); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPlace", DbType.String, "@GrantPlace", QueryConditionOperatorType.Like, filter.GrantPlace); cmd.CommandText = sqlBuilder.BuildQuerySql(); result.ResultList = cmd.ExecuteEntityList<GiftsGrantRecord>(); result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return result; } }
public static QueryResponseDTO <bool> DeleteReviewInfo(ReviewDTO dto) { QueryResponseDTO <bool> response = new QueryResponseDTO <bool>(); response.ResultEntity = false; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveReviewInfo"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, dto.PagingInfo, "ID ASC")) { if (null != dto) { if (!string.IsNullOrEmpty(dto.UserID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "UserID", DbType.AnsiString, "@UserID", QueryConditionOperatorType.Equal, dto.UserID); } if (!string.IsNullOrEmpty(dto.ReviewID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ReviewID", DbType.AnsiString, "@ReviewID", QueryConditionOperatorType.Equal, dto.ReviewID); } if (!string.IsNullOrEmpty(dto.ProductOROrderID)) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ProductOROrderID", DbType.AnsiString, "@ProductOROrderID", QueryConditionOperatorType.Equal, dto.ProductOROrderID); } if (null != dto.ReviewDate) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "ReviewDate", DbType.DateTime, "@ReviewDate", QueryConditionOperatorType.Equal, dto.ReviewDate); } if (dto.Score > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "Score", DbType.Int32, "@Score", QueryConditionOperatorType.Equal, dto.Score); } if (dto.FavourCount > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "FavourCount", DbType.Int32, "@FavourCount", QueryConditionOperatorType.Equal, dto.FavourCount); } if (dto.OpposeCount > 0) { sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "OpposeCount", DbType.Int32, "@OpposeCount", QueryConditionOperatorType.Equal, dto.OpposeCount); } sqlBuilder.ConditionConstructor .AddCondition(QueryConditionRelationType.AND, "CheckPassed", DbType.Boolean, "@CheckPassed", QueryConditionOperatorType.Equal, dto.CheckPassed); } //QueryData dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var updateRows = dataCommand.ExecuteNonQuery(); if (updateRows > 0) { response.ResultEntity = true; response.Code = ResponseStaticModel.Code.OK; response.Message = ResponseStaticModel.Message.OK; } else { response.ResultEntity = false; response.Code = ResponseStaticModel.Code.FAILED; response.Message = ResponseStaticModel.Message.FAILED; } } return(response); }
/// <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); } }
private void AddSAPDOCHeaderParameters(HeaderDataQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { if (filter.UploadDateFrom.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "[PSTNG_DATE]", DbType.DateTime, "@UploadDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.UploadDateFrom); } if (filter.UploadDateTo.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "[PSTNG_DATE]", DbType.DateTime, "@UploadDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.UploadDateTo); } if (!string.IsNullOrEmpty(filter.DocType)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "[DOC_TYPE]", DbType.String, "@DOC_TYPE", QueryConditionOperatorType.Equal, filter.DocType); } if (filter.SapCoCode != ResCommonEnum.Enum_All) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "[COMP_CODE]", DbType.String, "@COMP_CODE", QueryConditionOperatorType.Equal, filter.SapCoCode); } cmd.CommandText = sb.BuildQuerySql(); }
private void AddEIMSEventMemoQueryParameters(EIMSEventMemoQueryFilter filter, DynamicQuerySqlBuilder sb, CustomDataCommand cmd) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventType", DbType.Int32, "@EventType", QueryConditionOperatorType.Equal, 195); string tempMemo = string.Empty; if (!string.IsNullOrEmpty(filter.Memo)) { tempMemo = filter.Memo.Replace(" ", "%"); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventMemo", DbType.String, "@EventMemo", QueryConditionOperatorType.Like, "%" + tempMemo + "%"); } if (filter.BeginDate.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@BDate", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDate); } if (filter.EndDate.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.EventDate", DbType.String, "@EDate", QueryConditionOperatorType.LessThanOrEqual, filter.EndDate.Value.ToShortDateString() + " 23:59:59"); } cmd.AddInputParameter("@PageSize", DbType.Int32, filter.PagingInfo.PageSize); cmd.AddInputParameter("@PageIndex", DbType.Int32, filter.PagingInfo.PageIndex); cmd.CommandText = sb.BuildQuerySql(); }
public System.Data.DataTable QueryBasketList(QueryFilter.PO.PurchaseOrderBasketQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryBasket"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, pagingInfo, "Basket.SysNo DESC")) { if (queryFilter != null) { //by Jack.W.Wang 2012-12-6 CRL21776--------------------------BEGIN if (!queryFilter.IsManagerPM.HasValue || !queryFilter.IsManagerPM.Value) { string tSubSqlTxt = @"Select ProductLineSysNo FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " + "WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0"; builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "line.ProductLineSysNo", QueryConditionOperatorType.In, tSubSqlTxt); } //by Jack.W.Wang 2012-12-6 CRL21776--------------------------END //TODO:获取PM List; #region 权限筛选 //var pmHasRight = AuthorizedPMs(); //var createUserSysNo = query.Condition.CreateUserSysNo; //if (!string.IsNullOrEmpty(createUserSysNo)) //{ // var createIsValid = pmHasRight.Exists( // x => x == Convert.ToInt32(createUserSysNo)); // if (createIsValid) // { // builder.ConditionConstructor.AddCustomCondition( // QueryConditionRelationType.AND, // "Basket.CreateUserSysNo in(" + createUserSysNo + ")"); // } // else // { // builder.ConditionConstructor.AddInCondition( // QueryConditionRelationType.AND, // "Basket.CreateUserSysNo", // DbType.Int32, // new List<int> { -999 }); // } //} //else //{ // builder.ConditionConstructor.AddInCondition( // QueryConditionRelationType.AND, // "Basket.CreateUserSysNo", // DbType.Int32, // pmHasRight); //} #endregion if (queryFilter.StockSysNo.HasValue) { string stockStr = queryFilter.StockSysNo.ToString(); //有中转仓 if (stockStr.Length > 2) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, Int32.Parse(stockStr.Substring(2))); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.IsTransfer", DbType.Int32, "@IsTransfer", QueryConditionOperatorType.Equal, 1); } else { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); } } if (!string.IsNullOrEmpty(queryFilter.CreateUserSysNo)) { builder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Basket.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, Int32.Parse(queryFilter.CreateUserSysNo)); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Basket.CompanyCode", System.Data.DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); } command.CommandText = builder.BuildQuerySql(); } EnumColumnList enumList = new EnumColumnList(); enumList.Add("PaySettleCompany", typeof(PaySettleCompany)); dt = command.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); }
private void AddInvoiceInfoListParameters(EIMSInvoiceEntryQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { List <Object> receiveTypeList = new List <Object>(); receiveTypeList.Add(1); receiveTypeList.Add(2); List <Object> status = new List <Object>(); status.Add("O"); status.Add("F"); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.ReceiveType", DbType.Int32, "@MyReceiveType", QueryConditionOperatorType.In, receiveTypeList); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.[STATUS]", DbType.String, "@MySTATUS", QueryConditionOperatorType.In, status); if (!string.IsNullOrEmpty(filter.AssignedCode)) { filter.AssignedCode = filter.AssignedCode.Replace(" ", string.Empty); string[] spiltResults = filter.AssignedCode.Split('.'); string invoiceNumbers = string.Empty; string assignedCodes = string.Empty; int invoiceNumber = 0; if (spiltResults.Length > 1) { for (int i = 0; i < spiltResults.Length; i++) { if (int.TryParse(spiltResults[i], out invoiceNumber)) { if (invoiceNumbers.Length > 0) { invoiceNumbers += ","; } invoiceNumbers += spiltResults[i]; } else { if (assignedCodes.Length > 0) { assignedCodes += ","; } assignedCodes += "'" + spiltResults[i] + "'"; } } if (invoiceNumbers.Length > 0 && assignedCodes.Length == 0) { sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.AND, "ei.InvoiceNumber", QueryConditionOperatorType.In, invoiceNumbers); } if (assignedCodes.Length > 0 && invoiceNumbers.Length == 0) { sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.AND, "ei.AssignedCode", QueryConditionOperatorType.In, assignedCodes); } if (invoiceNumbers.Length > 0 && assignedCodes.Length > 0) { sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.AND, "ei.InvoiceNumber", QueryConditionOperatorType.In, invoiceNumbers); sb.ConditionConstructor.AddSubQueryCondition( QueryConditionRelationType.OR, "ei.AssignedCode", QueryConditionOperatorType.In, assignedCodes); sb.ConditionConstructor.EndGroupCondition(); } } else { if (int.TryParse(filter.AssignedCode, out invoiceNumber)) { sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.AssignedCode", DbType.String, "@AssignedCode", QueryConditionOperatorType.Equal, filter.AssignedCode); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.OR, "ei.InvoiceNumber", DbType.String, "@InvoiceNumber", QueryConditionOperatorType.Equal, invoiceNumber); sb.ConditionConstructor.EndGroupCondition(); } else { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.AssignedCode", DbType.String, "@AssignedCode", QueryConditionOperatorType.Equal, filter.AssignedCode); } } } if (filter.EIMSType.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.EIMSType", DbType.String, "@EIMSType", QueryConditionOperatorType.Equal, filter.EIMSType); } if (!string.IsNullOrEmpty(filter.InvoiceInputStatus)) { string subSql = "SELECT COUNT(1) " + "FROM eims.dbo.EIMSInvoiceInput iei WITH(NOLOCK) " + "INNER JOIN eims.dbo.EIMSInvoiceInput_Ex iex WITH(NOLOCK) " + "ON iei.SysNo = iex.InvoiceInputSysNO AND iei.[status]=0 AND iex.[status]=0 " + "WHERE iex.InvoiceNumber = ei.InvoiceNumber "; if (filter.InvoiceInputStatus == "已录入") { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "(" + subSql + ")", DbType.Int32, "@InvoiceInputStatusSql", QueryConditionOperatorType.MoreThan, 0); } else if (filter.InvoiceInputStatus == "未录入") { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "(" + subSql + ")", DbType.Int32, "@InvoiceInputStatusSql", QueryConditionOperatorType.Equal, 0); } } if (filter.ReceiveType.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.ReceiveType", DbType.String, "@ReceiveType", QueryConditionOperatorType.Equal, filter.ReceiveType); } if (!string.IsNullOrEmpty(filter.RuleAssignedCode)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "er.AssignedCode", DbType.String, "@RuleAssignedCode", QueryConditionOperatorType.Equal, filter.RuleAssignedCode); } if (!string.IsNullOrEmpty(filter.Status)) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.[Status]", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status); } if (filter.VendorNumber.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.VendorNumber", DbType.String, "@VendorNumber", QueryConditionOperatorType.Equal, filter.VendorNumber); } if (!string.IsNullOrEmpty(filter.IsSAPImported)) { if (filter.IsSAPImported == "Y") { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "fp.IsSAPImported", DbType.String, "@IsSAPImported", QueryConditionOperatorType.Equal, filter.IsSAPImported); } else if (filter.IsSAPImported == "N") { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "fp.IsSAPImported", DbType.String, "@IsSAPImported", QueryConditionOperatorType.Equal, filter.IsSAPImported); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.OR, "fp.IsSAPImported", DbType.String, "@InvoiceNumber", QueryConditionOperatorType.IsNull, null); } } if (!string.IsNullOrEmpty(filter.InvoiceNumber)) { string[] arr = filter.InvoiceNumber.Split('.'); List <Object> invoiceNumberList = new List <Object>(); string invoiceNumber = string.Empty; for (int i = 0; i < arr.Length; i++) { invoiceNumberList.Add(arr[i]); } sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ei.InvoiceNumber", DbType.String, "@InvoiceNumber", QueryConditionOperatorType.In, invoiceNumberList); } if (!string.IsNullOrEmpty(filter.InvoiceInputNo)) { string subSql = "EXISTS(SELECT 1 " + "FROM EIMS.dbo.EIMSInvoiceInput_Ex eiiex WITH(NOLOCK) " + "INNER JOIN eims.dbo.EIMSInvoiceInput eii WITH(NOLOCK) " + " ON eiiex.InvoiceInputSysNO = eii.SysNO "+ "WHERE eii.Status=0 " + " AND eiiex.Status=0 " + " AND eii.InvoiceInputNo="+ filter.InvoiceInputNo + " " + " AND eiiex.InvoiceNumber=ei.InvoiceNumber)"; cmd.CommandText += subSql; sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, subSql); } cmd.CommandText = sb.BuildQuerySql(); }
private void AddARReceiveParameters(ReceivedReportQueryFilter filter, CustomDataCommand cmd, DynamicQuerySqlBuilder sb) { string strSql = " WHERE ruleinfo.Status = 'O' AND invoice.Status = 'O' AND invoice.CompanyCode = @CompanyCode"; cmd.AddInputParameter("@CompanyCode", DbType.AnsiStringFixedLength, filter.CompanyCode); if (filter.VendorSysNo.HasValue) { strSql += " AND invoice.VendorNumber = @VendorSysNo "; cmd.AddInputParameter("@VendorSysNo", DbType.Int32, filter.VendorSysNo); } if (!string.IsNullOrEmpty(filter.EIMSType)) { strSql += " AND invoice.EIMSType = @EIMSType "; cmd.AddInputParameter("@EIMSType", DbType.String, filter.EIMSType); } if (filter.ProductCategory1.HasValue) { strSql += " AND item.ItemCategory1 = @ItemCategory1 "; cmd.AddInputParameter("@ItemCategory1", DbType.Int32, filter.ProductCategory1); } if (filter.ProductCategory2.HasValue) { strSql += " AND item.ItemCategory2 = @ItemCategory2 "; cmd.AddInputParameter("@ItemCategory2", DbType.Int32, (filter.ProductCategory2)); } cmd.CommandText = cmd.CommandText.Replace("#WhereStr1#", strSql); strSql += " AND item.AccruePeriod = @AccruePeriod"; cmd.AddInputParameter("@AccruePeriod", DbType.String, GetEffectiveAccruePeriod()); cmd.CommandText = cmd.CommandText.Replace("#WhereStr2#", strSql); cmd.AddOutParameter("@TotalCount", DbType.Int32, 4); }