public DataTable QueryCategoryProperty(CategoryPropertyQueryFilter queryCriteria, out int totalCount) { var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategoryProperty"); var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PagingInfo.SortBy, StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize, MaximumRows = queryCriteria.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "P.SysNo DESC")) { if (queryCriteria.CategorySysNo > 0) { //dataCommand.AddInputParameter("@CategorySysNo", DbType.Int32, queryCriteria.CategorySysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CategorySysNo", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, queryCriteria.CategorySysNo); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList(); enumList.Add("IsItemSearch", typeof(CategoryPropertyStatus)); enumList.Add("IsInAdvSearch", typeof(CategoryPropertyStatus)); enumList.Add("IsMustInput", typeof(CategoryPropertyStatus)); enumList.Add("WebDisplayStyle", typeof(WebDisplayStyle)); enumList.Add("PropertyType", typeof(PropertyType)); DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询商品 /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable QueryProductEx(NeweggProductQueryFilter filter, out int totalCount) { DataCommand command = DataCommandManager.GetDataCommand("QueryProductEx"); filter.CompanyCode = "8601"; filter.CurrentUserSysNo = ServiceContext.Current.UserSysNo; CreateQueryProductCommand(filter, command); var sort = filter.PagingInfo.SortBy; var sortField = ""; var sortType = ""; if (!String.IsNullOrEmpty(sort)) { var input = Regex.Replace(filter.PagingInfo.SortBy.Trim(), "\\s+", " "); var pageArry = input.Split(' '); if (pageArry.Length == 2) { sortField = pageArry[0]; sortType = pageArry[1]; } } command.SetParameterValue("@SortField", sortField); command.SetParameterValue("@SortType", sortType); command.SetParameterValue("@PageSize", filter.PagingInfo.PageSize); command.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex); var columnConfig = new EnumColumnList { { "Status", typeof(ProductStatus) } }; var table = command.ExecuteDataTable(columnConfig); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(table); }
public DataTable QuerySOLog(QueryFilter.Common.LogQueryFilter filter, out int totalCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOLog"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = filter.PagingInfo.SortBy, StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize, MaximumRows = filter.PagingInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, pagingInfo, "[LOG].[SysNo] DESC")) { SetTheSearchCondition(builder, filter); command.CommandText = builder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("Status", typeof(LogSOStatus)); enumList.Add("TicketType", typeof(c.BizLogType)); DataTable dt = command.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable GetRmaPolicyLog(RmaPolicyLogQueryFilter filter, out int totalCount) { DataCommand cmd = DataCommandManager.GetDataCommand("GetRmaPolicyLog"); cmd.SetParameterValue("@RmaPolicySysNo", filter.RmaPolicySysNO); cmd.SetParameterValue("@RmaPolicy", filter.RmaPolicy); cmd.SetParameterValue("@UpdateDateTo", filter.UpdateDateTo); cmd.SetParameterValue("@UpdateDateFrom", filter.UpdateDateFrom); cmd.SetParameterValue("@EditUserName", filter.EidtUserName); cmd.SetParameterValue("@pageIndex", filter.PagingInfo.PageIndex); cmd.SetParameterValue("@pageSize", filter.PagingInfo.PageSize); cmd.SetParameterValue("@sortField", filter.PagingInfo.SortBy); EnumColumnList enumList = new EnumColumnList { { "Status", typeof(RmaPolicyStatus) }, { "Type", typeof(RmaPolicyType) }, { "IsOnlineRequst", typeof(IsOnlineRequst) }, { "OperationType", typeof(RmaLogActionType) }, }; DataTable dt = new DataTable(); dt = cmd.ExecuteDataTable(enumList); totalCount = (int)cmd.GetParameterValue("@totalCount"); return(dt); }
public DataTable Query(HelpCenterQueryFilter 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("HelpCenter_GetHelpTopic"); var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "B.SysNo DESC"); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "B.Priority", DbType.Int32, "@Priority", QueryConditionOperatorType.Equal, filter.Priority); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "B.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "B.Type", DbType.AnsiStringFixedLength, "@Type", QueryConditionOperatorType.Equal, filter.Type); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "B.CategorySysNo", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, filter.CategorySysNo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "B.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); //TODO:添加ChannelID参数 cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumConfig = new EnumColumnList(); enumConfig.Add("Status", typeof(ADStatus)); enumConfig.Add("Type", typeof(FeatureType)); var dt = cmd.ExecuteDataTable(enumConfig); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); }
/// <summary> /// 查询品牌 /// </summary> /// <returns></returns> public virtual DataTable QueryBrand(BrandQueryFilter queryCriteria, out int totalCount) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetBrandListByQuery"); dataCommand.SetParameterValue("@BrandName", string.IsNullOrEmpty(queryCriteria.BrandNameLocal)?null:string.Format("%{0}%", queryCriteria.BrandNameLocal)); dataCommand.SetParameterValue("@ManufacturerName", string.IsNullOrEmpty(queryCriteria.ManufacturerName) ? null : string.Format("%{0}%", queryCriteria.ManufacturerName)); dataCommand.SetParameterValue("@Status", queryCriteria.Status); dataCommand.SetParameterValue("@BrandSysNo", queryCriteria.BrandSysNo); dataCommand.SetParameterValue("@ManufacturerSysNo", queryCriteria.ManufacturerSysNo); dataCommand.SetParameterValue("@Category1SysNo", queryCriteria.Category1SysNo); dataCommand.SetParameterValue("@Category2SysNo", queryCriteria.Category2SysNo); dataCommand.SetParameterValue("@Category3SysNo", queryCriteria.Category3SysNo); dataCommand.SetParameterValue("@IsAuthorized", queryCriteria.IsAuthorized); dataCommand.SetParameterValue("@AuthorizedStatus", queryCriteria.AuthorizedStatus); dataCommand.SetParameterValue("@BrandStory", queryCriteria.IsBrandStory); dataCommand.SetParameterValue("@SortField", queryCriteria.PagingInfo.SortBy); dataCommand.SetParameterValue("@PageSize", queryCriteria.PagingInfo.PageSize); dataCommand.SetParameterValue("@PageCurrent", queryCriteria.PagingInfo.PageIndex); dataCommand.SetParameterValue("@Priority", queryCriteria.Priority == "" ? null : queryCriteria.Priority); EnumColumnList enumList = new EnumColumnList { { "Status", typeof(ValidStatus) }, }; DataTable dt = new DataTable(); dt = dataCommand.ExecuteDataTable(enumList); totalCount = (int)dataCommand.GetParameterValue("@TotalCount"); return(dt); }
/// <summary> /// 查询赠品 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GetGifts(RepeatPromotionQueryFilter queryCriteria, out int totalCount) { var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PageInfo.SortBy, MaximumRows = queryCriteria.PageInfo.PageSize, StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize }; var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetGifts"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC")) { var sysNoList = (new QueryGift()).GetGiftSysNo(queryCriteria); var sysNoStr = sysNoList.Count <= 0 ? "-1" : sysNoList.Join(","); sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "C.SysNo", QueryConditionOperatorType.In, sysNoStr); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList { { "Status", typeof(SaleGiftStatus) }, { "Type", typeof(SaleGiftType) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public System.Data.DataTable QueryPurchaseOrderList(QueryFilter.PO.PurchaseOrderQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); #region CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPOList"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; BuildSearchPOCondition(queryFilter, dataCommand, pagingInfo); if (!string.IsNullOrEmpty(queryFilter.StatusList)) { dataCommand.CommandText = dataCommand.CommandText.Replace("@Status", " @Status OR PO.Status IN (1,2,3,-2,5,6)"); } EnumColumnList enumList = new EnumColumnList(); enumList.Add("status", typeof(PurchaseOrderStatus)); enumList.Add("PaySettleCompany", typeof(PaySettleCompany)); dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); #endregion }
/// <summary> /// 根据query得到CPSUser信息 /// </summary> /// <param name="query"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GetCpsUser(CpsUserQueryFilter query, out int totalCount) { DataCommand cmd = DataCommandManager.GetDataCommand("GetCpsUser"); cmd.SetParameterValue("@Status", query.AuditStatus); cmd.SetParameterValue("@UserType", query.UserType); cmd.SetParameterValue("@WebSiteType", query.WebSiteType); cmd.SetParameterValue("@IsAvailable", query.IsActive); cmd.SetParameterValue("@CustomerID", query.CustomerID); cmd.SetParameterValue("@ReceivablesName", query.ReceivablesName); cmd.SetParameterValue("@Email", query.Email); cmd.SetParameterValue("@Phone", query.Phone); cmd.SetParameterValue("@ImMessenger", query.ImMessenger); cmd.SetParameterValue("@RegisterDateFrom", query.RegisterDateFrom); cmd.SetParameterValue("@RegisterDateTo", query.RegisterDateTo); cmd.SetParameterValue("@PageIndex", query.PageInfo.PageIndex); cmd.SetParameterValue("@PageSize", query.PageInfo.PageSize); cmd.SetParameterValue("@SortFiled", query.PageInfo.SortBy); EnumColumnList enumList = new EnumColumnList { { "Status", typeof(AuditStatus) }, { "UserType", typeof(UserType) }, { "IsAvailable", typeof(IsActive) }, { "BankLock", typeof(IsLock) }, { "BankAccountType", typeof(UserType) } }; DataTable dt = new DataTable(); dt = cmd.ExecuteDataTable(enumList); totalCount = (int)cmd.GetParameterValue("@TotalCount"); return(dt); }
public System.Data.DataSet Query(QueryFilter.MKT.AmbassadorNewsQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAmbassadorNewsList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.NewsType", DbType.String, "@NewsType", QueryConditionOperatorType.Equal, 12); cmd.AddInputParameter("@SysNo", DbType.Int32, filter.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Title", DbType.String, "@Title", QueryConditionOperatorType.LeftLike, filter.Title); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.InDateFromTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate", DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.InDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ReferenceSysNo", DbType.String, "@ReferenceSysNo", QueryConditionOperatorType.Equal, filter.ReferenceSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); cmd.CommandText = sqlBuilder.BuildQuerySql(); var ds = cmd.ExecuteDataSet(); EnumColumnList enumConfigNews = new EnumColumnList(); enumConfigNews.Add("Status", typeof(AmbassadorNewsStatus)); cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews); if (Int32.TryParse(cmd.GetParameterValue("@TotalCount").ToString(), out totalCount)) { } else { totalCount = 0; } return(ds); } }
public virtual DataTable QueryCallsEventLog(CustomerCallsEventLogFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCallsEventLog"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.Customer_CallingSysNo", DbType.Int32, "@CallsEventsSysNo ", QueryConditionOperatorType.Equal, filter.CallsEventsSysNo); cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enunlist = new EnumColumnList(); enunlist.Add(3, typeof(CallsEventsStatus)); enunlist.Add(7, typeof(CustomerCallReason)); CodeNamePairColumnList cpList = new CodeNamePairColumnList(); cpList.Add(2, "Customer", "RecordOrigion"); DataTable dt = cmd.ExecuteDataTable(enunlist, cpList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// SAP确认人查询 /// </summary> /// <param name="query"></param> /// <param name="totalCount"></param> /// <returns></returns> public virtual DataTable QueryIPPUser(SAPIPPUserQueryFilter query, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = query.PagingInfo.SortBy; pagingEntity.MaximumRows = query.PagingInfo.PageSize; pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySAPIPPUser"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingEntity, "I.SysNo desc")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.PayType", DbType.Int32, "@PayType", QueryConditionOperatorType.Equal, query.PayType); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CustID", DbType.String, "@CustID", QueryConditionOperatorType.Equal, query.CustID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.SystemConfirmID", DbType.String, "@SystemConfirmID", QueryConditionOperatorType.Equal, query.SystemConfirmID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumColumnList = new EnumColumnList(); enumColumnList.Add("Status", typeof(SAPStatus)); DataTable dt = dataCommand.ExecuteDataTable(enumColumnList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable GetProductExtByQuery(ProductExtQueryFilter query, out int totalCount) { DataCommand dc = DataCommandManager.GetDataCommand("GetItemExtList"); dc.SetParameterValue("@ProductID", query.ProductID); dc.SetParameterValue("@ProductStatus", query.ProductStatus); if (query.ProductType != null) { dc.SetParameterValue("@ProductType", LegacyEnumMapper.ConvertProductType(query.ProductType.Value)); } else { dc.SetParameterValue("@ProductType", null); } dc.SetParameterValue("@Manufacturer", query.Manufacturer); dc.SetParameterValue("@ProductPrice", query.ProductPrice); dc.SetParameterValue("@IsPermitRefund", query.IsPermitRefund); dc.SetParameterValue("@Category1", query.Category1); dc.SetParameterValue("@Category2", query.Category2); dc.SetParameterValue("@Category3", query.Category3); dc.SetParameterValue("@SortField", query.PagingInfo.SortBy); dc.SetParameterValue("@PageSize", query.PagingInfo.PageSize); dc.SetParameterValue("@PageCurrent", query.PagingInfo.PageIndex); EnumColumnList enumList = new EnumColumnList { { "Status", typeof(ProductStatus) }, { "IsPermitRefund", typeof(IsDefault) } }; DataTable dt = new DataTable(); dt = dc.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dc.GetParameterValue("@TotalCount")); return(dt); }
/// <summary> /// 查询外网搜素 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable QueryKeyword(InternetKeywordQueryFilter queryCriteria, out int totalCount) { var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PageInfo.SortBy, MaximumRows = queryCriteria.PageInfo.PageSize, StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize }; var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryKeyword"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC")) { if (!string.IsNullOrWhiteSpace(queryCriteria.SearchKeyword)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SearchKeyword", DbType.String, "@SearchKeyword", QueryConditionOperatorType.Like, queryCriteria.SearchKeyword); } if (queryCriteria.Status != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status.Value); } if (queryCriteria.BeginDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "InDate", DbType.String, "@BeginDate", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.BeginDate.Value); } if (queryCriteria.EndDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "InDate", DbType.String, "@EndDate", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList { { "Status", typeof(IsDefaultStatus) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询EIMS单据 /// </summary> /// <param name="filter">查询条件集合</param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable EIMSInvoiceQuery(EIMSInvoiceQueryFilter filter, out int totalCount) { CustomDataCommand cmd; if (filter.IsC1Summary == null || filter.IsC1Summary == false) { cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoice"); } else { cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoiceBYC1Name"); } using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "vendor.SysNo DESC")) { AddEIMSInvoiceParameters(filter, sb, cmd); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("EIMSType", "ExternalSYS", "EIMSType"); codeNameColList.Add("ReceiveType", "ExternalSYS", "ReceivedType"); codeNameColList.Add("Status", "ExternalSYS", "InvoiceStatus"); cmd.ConvertColumn(dt, enumColList, codeNameColList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 获取详细的礼品卡操作日志 /// </summary> /// <param name="code"></param> /// <returns></returns> public List <GiftCardRedeemLog> GetGiftCardRedeemLogJoinSOMaster(string code) { DataCommand dc = DataCommandManager.GetDataCommand("GiftCard_GetGiftCardRedeemLogJoinSOMaster"); dc.SetParameterValue("@Code", code); EnumColumnList enumList = new EnumColumnList(); enumList.Add("OrderStatus", typeof(ECCentral.BizEntity.SO.SOStatus)); enumList.Add("RedeemStatus", typeof(ValidStatus)); var dt = dc.ExecuteDataTable(enumList); if (dt != null && dt.Rows.Count > 0) { List <GiftCardRedeemLog> list = new List <GiftCardRedeemLog>(); foreach (DataRow dr in dt.Rows) { list.Add(DataMapper.GetEntity <GiftCardRedeemLog>(dr)); } return(list); } else { return(null); } //using (IDataReader reader = dc.ExecuteDataReader()) //{ // return DataMapper.GetEntityList<GiftCardOperateLog, List<GiftCardOperateLog>>(reader); //} }
public DataTable QueryFloorMaster(FloorMasterQueryFilter 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("Floor_QueryFloorMaster"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "PageCode ASC, Priority ASC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageCode", DbType.String, "@PageCode", QueryConditionOperatorType.Equal, filter.PageCode); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType); cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumConfig = new EnumColumnList(); enumConfig.Add("PageType", typeof(PageCodeType)); enumConfig.Add("Status", typeof(ADStatus)); var dt = cmd.ExecuteDataTable(enumConfig); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable SocietyCommissionQuery(CommissionTypeQueryFilter filter, out int totalCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SocietyCommissionQuery"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = filter.PagingInfo.SortBy, StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize, MaximumRows = filter.PagingInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, pagingInfo, "SysNo DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrganizationID", DbType.String, "@OrganizationID", QueryConditionOperatorType.Like, filter.OrganizationID); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "OrganizationName", DbType.String, "@OrganizationName", QueryConditionOperatorType.Like, filter.OrganizationName); command.CommandText = builder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("IsOnlineShow", typeof(HYNStatus)); DataTable dt = command.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询配送方式-产品 /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable QueryShipTypeProductList(ShipTypeProductQueryFilter filter, out int totalCount) { totalCount = 0; object _itemRange = null;; object _type = null; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ShipTypeProduct_QueryShipTypeProduct"); PagingInfoEntity pagingInfo = ToPagingInfo(filter.PagingInfo); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SPI.SysNo DESC")) { if (filter.ShipTypeProductType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.[Type]", DbType.String, "@Type", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ShipTypeProductType, out _type)); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.Description", DbType.String, "@Description", QueryConditionOperatorType.Like, filter.Description); if (filter.ProductRange.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ItemRange", DbType.String, "@ItemRange", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ProductRange, out _itemRange)); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShippingType); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, filter.WareHouse); cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList EnumList = new EnumColumnList(); EnumList.Add("ItemRange", typeof(ECCentral.BizEntity.Common.ProductRange)); EnumList.Add("Type", typeof(ECCentral.BizEntity.Common.ShipTypeProductType)); DataTable dt = cmd.ExecuteDataTable(EnumList); totalCount = int.Parse(cmd.GetParameterValue("TotalCount").ToString()); return(dt); } }
/// <summary> /// 查询促销计划 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GeSaleCountDownPlan(RepeatPromotionQueryFilter queryCriteria, out int totalCount) { var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PageInfo.SortBy, MaximumRows = queryCriteria.PageInfo.PageSize, StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize }; var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetSaleCountDowns"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.Int32, "@Status", QueryConditionOperatorType.In, new List <object> { CountdownStatus.WaitForVerify, CountdownStatus.Ready , CountdownStatus.Running, CountdownStatus.Init, CountdownStatus.VerifyFaild }); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.IsPromotionSchedule", DbType.Int32, "@IsPromotionSchedule", QueryConditionOperatorType.Equal, 1); if (queryCriteria.StartDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.EndTime", DbType.String, "@StartDate", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.StartDate.Value); } if (queryCriteria.EndDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.StartTime", DbType.String, "@EndDate", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList { { "Status", typeof(CountdownStatus) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public DataTable Query(ConsignAdjustQueryFilter queryFilter, out int totalCount) { if (queryFilter == null) { totalCount = -1; return(null); } CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryConsignAdjustList"); 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, "C.SysNo DESC")) { if (queryFilter.VendorSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value); } if (queryFilter.PMSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.PMSysNo", DbType.Int32, "@PMSysNo", QueryConditionOperatorType.Equal, queryFilter.PMSysNo.Value); } if (queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value); } //if (queryFilter.SettleSysno.HasValue) //{ // sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SettleSysno", // DbType.Int32, "@SettleSysno", QueryConditionOperatorType.Equal, queryFilter.SettleSysno.Value); //} if (queryFilter.SettleRange.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.SettleRange", DbType.String, "@SettleRange", QueryConditionOperatorType.Equal, queryFilter.SettleRange.Value.ToString("yyyy-MM")); } 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("Status", typeof(ConsignAdjustStatus)); DataTable dt = command.ExecuteDataTable(columnEnums); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询配送方式 /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable QueryShipTypeList(ShipTypeQueryFilter filter, out int totalCount) { MapSortField(filter); 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("ShipType_QueryShipType"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "ST.SysNo DESC")) { //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.ShipTypeID", DbType.String, "@ShipTypeID", QueryConditionOperatorType.Equal, filter.ShipTypeID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.ShipTypeName", DbType.String, "@ShipTypeName", QueryConditionOperatorType.Like, filter.ShipTypeName); //if(filter.IsOnlineShow.HasValue&&filter.IsOnlineShow.Value==ECCentral.BizEntity.Common.HYNStatus.Hide) // sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsOnlineShow", DbType.Int32, "@IsOnlineShow", QueryConditionOperatorType.NotEqual, ECCentral.BizEntity.Common.HYNStatus.Hide); //else if (filter.IsOnlineShow != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsOnlineShow", DbType.Int32, "@IsOnlineShow", QueryConditionOperatorType.Equal, filter.IsOnlineShow.GetHashCode()); } if (filter.IsWithPackFee != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsWithPackFee", DbType.Int32, "@IsWithPackFee", QueryConditionOperatorType.Equal, filter.IsWithPackFee.GetHashCode()); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.OnlyForStockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, filter.StockSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("IsOnlineShow", typeof(ECCentral.BizEntity.Common.HYNStatus)); enumList.Add("IsWithPackFee", typeof(ECCentral.BizEntity.MKT.NYNStatus)); enumList.Add("DeliveryPromise", typeof(ECCentral.BizEntity.Common.DeliveryStatusFor24H)); enumList.Add("PackStyle", typeof(ECCentral.BizEntity.Common.ShippingPackStyle)); enumList.Add("ShipTypeEnum", typeof(ECCentral.BizEntity.Common.ShippingTypeEnum)); enumList.Add("DeliveryType", typeof(ECCentral.BizEntity.Common.ShipDeliveryType)); enumList.Add("StoreType", typeof(ECCentral.BizEntity.IM.StoreType)); //CodeNamePairColumnList pairList = new CodeNamePairColumnList(); //pairList.Add("PackStyle", "Common", "ShippingPackStyle"); //pairList.Add("ShipTypeEnum", "Common", "ShipTypeEnum"); //pairList.Add("DeliveryType", "Common", "DeliveryType"); var dt = cmd.ExecuteDataTable(enumList);//<ECCentral.BizEntity.MKT.ADStatus>("Status"); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt);//.Tables[0]; } }
public DataSet QueryAmbassadorBasicInfo(QueryFilter.MKT.NeweggAmbassadorQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetNeweggAmbassadorList"); cmd.SetParameterValue("@NeweggAmbassadorID", filter.AmbassadorID); cmd.SetParameterValue("@NeweggAmbassadorName", string.IsNullOrEmpty(filter.AmbassadorName)?"":string.Format("%{0}%", filter.AmbassadorName.Trim())); cmd.SetParameterValue("@CustomerMark", filter.Status); cmd.SetParameterValue("@AreaSysNo", filter.AreaSysNo); cmd.SetParameterValue("@BigProvinceSysNo", filter.BigAreaSysNo); if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy)) { string[] sortInfo = filter.PagingInfo.SortBy.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (sortInfo.Length > 1) { cmd.SetParameterValue("@SortField", sortInfo[0]); cmd.SetParameterValue("@SortType", sortInfo[1]); } else { cmd.SetParameterValue("@SortField", null); cmd.SetParameterValue("@SortType", null); } } else { cmd.SetParameterValue("@SortField", null); cmd.SetParameterValue("@SortType", null); } cmd.SetParameterValue("@PageSize", filter.PagingInfo.PageSize); cmd.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex); DataSet ds = cmd.ExecuteDataSet(); EnumColumnList enumConfigNews = new EnumColumnList(); enumConfigNews.Add("CustomerMark", typeof(AmbassadorStatus)); if (Int32.TryParse(cmd.GetParameterValue("@TotalCount").ToString(), out totalCount)) { } else { totalCount = 0; } cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews); return(ds); }
public static DataTable UserQuery(UserQueryFilter filter, out int dataCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Query_Vendor_User"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "a.SysNo DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserName", DbType.String, "@UserName", QueryConditionOperatorType.LeftLike, filter.UserName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserID", DbType.String, "@UserID", QueryConditionOperatorType.LeftLike, filter.UserID); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.UserStatus); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VendorSysNo", DbType.AnsiStringFixedLength, "@ManufacturerSysNo", QueryConditionOperatorType.Equal, filter.ManufacturerSysNo); if (!string.IsNullOrEmpty(filter.SerialNum)) { string[] serialNum = filter.SerialNum.Split('-'); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, serialNum[0]); if (serialNum.Length > 1) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.UserNum", DbType.Int32, "@UserNum", QueryConditionOperatorType.Equal, Convert.ToInt32(serialNum[1]).ToString()); } } command.CommandText = builder.BuildQuerySql(); var dt = command.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); enumColList.Add("Status", typeof(UserStatus)); command.ConvertEnumColumn(dt, enumColList); dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); return(dt); } }
private DataTable ExecuteDataTable(CustomDataCommand dataCommand, out int totalCount) { EnumColumnList enumColumns = new EnumColumnList(); enumColumns.Add("OrderType", typeof(PayableOrderType)); var result = dataCommand.ExecuteDataTable(enumColumns); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(result); }
/// <summary> /// 查询团购 /// </summary> /// <param name="queryCriteria"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GetProductGroupBuying(RepeatPromotionQueryFilter queryCriteria, out int totalCount) { var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PageInfo.SortBy, MaximumRows = queryCriteria.PageInfo.PageSize, StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize }; var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductGroupBuying"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.String, "@Status", QueryConditionOperatorType.In, new List <object> { GroupBuyingStatus.Init, GroupBuyingStatus.Pending , GroupBuyingStatus.Active }); if (queryCriteria.StartDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.EndDate", DbType.String, "@StartDate", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.StartDate.Value); } if (queryCriteria.EndDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.BeginDate", DbType.String, "@EndDate", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList { { "Status", typeof(GroupBuyingStatus) }, { "IsByGroup", typeof(IsDefaultShow) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
private DataSet ExecuteDataCommandForTrackingInfo(CustomDataCommand dataCommand, out int totalCount) { DataSet ds = new DataSet(); //注册CodeNamePair类型 CodeNamePairColumnList codeNamePairColumnList = new CodeNamePairColumnList(); codeNamePairColumnList.Add("LossType", "Invoice", "TrackingInfoLoseType", "LossTypeID"); EnumColumnList enumColumnList = new EnumColumnList(); enumColumnList.Add("Status", typeof(TrackingInfoStatus)); enumColumnList.Add("OrderType", typeof(SOIncomeOrderType)); enumColumnList.Add("Source", typeof(NetPaySource)); enumColumnList.Add("OrderStatus", typeof(SOIncomeStatus)); enumColumnList.Add("IncomeStyle", typeof(SOIncomeOrderStyle)); enumColumnList.Add("InType", typeof(TrackingInfoStyle)); DataTable resultDT = dataCommand.ExecuteDataTable(enumColumnList, codeNamePairColumnList); resultDT.TableName = "DataResult"; ds.Tables.Add(resultDT); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); #region 构造统计信息 DataTable statisticDT = new DataTable("StatisticResult"); statisticDT.Columns.AddRange(new DataColumn[] { new DataColumn("OrderAmt", typeof(decimal)), /* 订单金额 */ new DataColumn("PrepayAmt", typeof(decimal)), /* 预收金额 */ new DataColumn("GiftCardPayAmt", typeof(decimal)), /* 礼品卡金额 */ new DataColumn("IncomeAmt", typeof(decimal)), /* 实收金额 */ new DataColumn("UnpayedAmt", typeof(decimal)) /* 未收金额 */ }); DataRow row = statisticDT.NewRow(); statisticDT.Rows.Add(row); row["OrderAmt"] = dataCommand.GetParameterValue("@OrderAmt"); row["PrepayAmt"] = dataCommand.GetParameterValue("@PrepayAmt"); row["GiftCardPayAmt"] = dataCommand.GetParameterValue("@GiftCardPayAmt"); row["IncomeAmt"] = dataCommand.GetParameterValue("@IncomeAmt"); row["UnpayedAmt"] = dataCommand.GetParameterValue("@UnpayedAmt"); ds.Tables.Add(statisticDT); #endregion 构造统计信息 return(ds); }
public DataTable QueryPriceChange(ChangePriceFilter query, out int totalCount) { DataTable result = null; PagingInfoEntity pagingInfo = new PagingInfoEntity(); if (query.PagingInfo != null) { pagingInfo.MaximumRows = query.PagingInfo.PageSize; pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize; pagingInfo.SortField = query.PagingInfo.SortBy; } CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("PriceChange_QueryPriceChange"); using (DynamicQuerySqlBuilder sql = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SysNo DESC")) { sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, query.SysNo); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductsysNo", DbType.Int32, "@ProductsysNo", QueryConditionOperatorType.Equal, query.ProductsysNo); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.Memo", DbType.String, "@Memo", QueryConditionOperatorType.Like, query.Memo); if (query.Status.HasValue) { sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, query.Status); } sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.BeginDate", DbType.DateTime, "@BeginDate", QueryConditionOperatorType.Equal, query.BeginDate); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.EndDate", DbType.DateTime, "@EndDate", QueryConditionOperatorType.Equal, query.EndDate); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, query.C3SysNo); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C3.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, query.C2SysNo); sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C2.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, query.C1SysNo); cmd.CommandText = sql.BuildQuerySql(); EnumColumnList enumColumns = new EnumColumnList(); enumColumns.Add("Status", typeof(RequestPriceStatus)); result = cmd.ExecuteDataTable(enumColumns); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(result); } }
/// <summary> /// 根据SysNo查询Memo /// </summary> /// <param name="sysNo"></param> /// <returns></returns> public DataTable GetMemoBySysNo(int?sysNo) { CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_QueryBySysNo"); cmd.AddInputParameter("@SysNo", DbType.Int32, sysNo); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); enumColList.Add("AuditStatus", typeof(PayableAuditStatus)); cmd.ConvertColumn(dt, enumColList, null); return(dt); }
public virtual DataTable SimpleQuery(CustomerSimpleQueryFilter 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; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_SimpleQuery"); using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC")) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "C.SysNo", DbType.Int32, "@SystemNumber", QueryConditionOperatorType.Equal, queryCriteria.CustomerSysNo); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerID); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerName", DbType.String, "@CustomerName", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName); // sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.MemberShipCard", DbType.String, "@MemberShipCard", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Email", DbType.AnsiString, "@Email", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerEmail); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); if (!string.IsNullOrEmpty(queryCriteria.CustomerPhone)) { //添加CustomerPhone子查询,并添加一个参数 sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "", QueryConditionOperatorType.Exist, @"SELECT 1 FROM OverseaCustomerManagement.[dbo].[CustomerPhone] WITH(NOLOCK) WHERE OverseaCustomerManagement.[dbo].[CustomerPhone].Phone like '%'+@Phone+'%' AND OverseaCustomerManagement.[dbo].CustomerPhone.CustomerSysNo = C.SysNo" ); cmd.AddInputParameter("@Phone", DbType.String, queryCriteria.CustomerPhone); } sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "C.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); cmd.CommandText = sb.BuildQuerySql(); EnumColumnList enumColumnList = new EnumColumnList(); enumColumnList.Add(2, typeof(CustomerStatus)); enumColumnList.Add(30, typeof(CustomerType)); enumColumnList.Add(17, typeof(CustomerRank)); enumColumnList.Add(19, typeof(VIPRank)); DataTable dt = cmd.ExecuteDataTable(); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }