/// <summary> /// Gets the user all account. /// </summary> /// <param name="userId">The user identifier.</param> /// <param name="moneyTypeGuid">The money type unique identifier.</param> /// <returns>IList<Account>.</returns> public IList <Account> GetUserAllAccount(long userId, string moneyTypeGuid) { IList <Account> result = new List <Account>(); if (userId <= 0) { return(result); } if (moneyTypeGuid.IsNullOrEmpty()) { return(result); } var sql = $"select * from Asset_Account where UserId={userId} and MoneyTypeId In ('{moneyTypeGuid}')"; using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadAccount(dr)); } } return(result); }
/// <summary> /// 根据下面的会员,更新团队信息 /// </summary> /// <param name="childuUserId"></param> public void UpdateTeamInfo(long childuUserId = 0) { var userConfig = Ioc.Resolve <IAutoConfigService>().GetValue <TeamConfig>(); var userIds = new List <long> { childuUserId }; if (childuUserId == 0) { var pageCount = 30; // 每次处理30个 // 总数 var totalCount = RepositoryContext.ExecuteScalar("select count(id) from User_UserMap").ConvertToLong(); var totalPage = totalCount / 30 + 1; for (var i = 1; i < totalPage + 1; i++) { userIds = new List <long>(); var sql = $"SELECT TOP 30 userId FROM (SELECT ROW_NUMBER() OVER (ORDER BY id ) AS RowNumber,userId FROM User_UserMap ) as A WHERE RowNumber > {pageCount}*({i}-1) "; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { userIds.Add(reader["UserId"].ConvertToLong()); } } UpdateTeamInfo(userIds, userConfig); } } else { UpdateTeamInfo(userIds, userConfig); } }
/// <summary> /// 获取所有的Sql表实体 /// </summary> public List <string> GetSqlTable() { var entityNames = Ioc.Resolve <ITypeService>().GetAllEntityType().Select(r => r.Name).ToList(); var list = new List <string>(); var sql = "select name from sys.tables"; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var tableName = reader["name"].ToStr(); var arrarys = tableName.SplitString("_").ToList(); if (arrarys.Count > 1) { var entity = arrarys[1]; if (entityNames.Contains(entity)) { list.Add(tableName); } } } } return(list); }
/// <summary> /// Gets the sku price. /// 获取所有商品的价格 /// 在线状态的 /// </summary> public IEnumerable <SkuPrice> GetSkuPrice(long productId = 0) { var sqlWhere = string.Empty; if (productId > 0) { sqlWhere = $" and Shop_Product.Id={productId} "; } var sql = $@"SELECT dbo.Shop_ProductSku.Id, dbo.Shop_ProductSku.ProductId, dbo.Shop_ProductSku.Price, dbo.Shop_Product.PriceStyleId, dbo.Shop_Product.MinCashRate FROM dbo.Shop_ProductSku INNER JOIN dbo.Shop_Product ON dbo.Shop_ProductSku.ProductId = dbo.Shop_Product.Id Where Shop_Product.ProductStatus=2 { sqlWhere } order by id desc "; var result = new List <SkuPrice>(); using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var productSkuItem = new SkuPrice { ProductSkuId = reader["Id"].ConvertToLong(0), ProductId = reader["ProductId"].ConvertToLong(0), Price = reader["Price"].ToDecimal(), PriceStyleId = reader["PriceStyleId"].ToGuid(), MinCashRate = reader["MinCashRate"].ToDecimal() }; result.Add(productSkuItem); } } return(result); }
public IList <long> GetUnHandledIdList() { var sql = $"select Id from Basic_MessageQueue where Status={(byte)MessageStatus.Pending} order by id "; IList <long> list = new List <long>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { list.Add(dr.Read <long>("Id")); } } return(list); }
public MessageQueue GetSingle(long id) { var sql = $"select * from Basic_MessageQueue where Id={id}"; MessageQueue result = null; using (var dr = RepositoryContext.ExecuteDataReader(sql)) { if (dr.Read()) { result = ReadQueue(dr); } } return(result); }
public IList <User> GetList(IList <long> userIds) { var sql = $@"SELECT * FROM User_User WHERE Id in ({userIds.ToSqlString()})"; var result = new List <User>(); using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { result.Add(ReadUser(reader)); } return(result); } }
/// <summary> /// 获取单条原生Sql记录 /// </summary> /// <param name="shareOrderId">The share order identifier.</param> public Trade GetSingleNative(long shareOrderId) { var sql = $"select * from Things_Trade where id={shareOrderId} "; //每次处理10条 var shareOrder = new Trade(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { if (dr.Read()) { shareOrder = ReadShareOrder(dr); } } return(shareOrder); }
public User GetSingle(string userName) { var sql = @"SELECT * FROM User_User WHERE UserName = @UserName"; using (var reader = RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@UserName", userName))) { User user = null; if (reader.Read()) { user = ReadUser(reader); } return(user); } }
public User GetSingleByMail(string mail) { var sql = @"SELECT * FROM User_User WHERE Email = @Email"; using (var reader = RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@Email", mail))) { User user = null; if (reader.Read()) { user = ReadUser(reader); } return(user); } }
public IList <long> GetAllServiceCenterUserIds(long userId) { var sql = $"select UserId from User_UserDetail where ServiceCenterUserId={userId}"; IList <long> result = new List <long>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadUserId(dr)); } } return(result); }
/// <summary> /// 支付时使用 /// </summary> /// <param name="transaction">The transaction.</param> /// <param name="userId">The user identifier.</param> /// <param name="moneyTypeId">The money type identifier.</param> /// <returns>Account.</returns> public Account GetAccount(DbTransaction transaction, long userId, Guid moneyTypeId) { var sql = $"select * from Asset_Account where MoneyTypeId='{moneyTypeId}' and UserId={userId}"; Account result = null; using (var dr = RepositoryContext.ExecuteDataReader(transaction, sql)) { if (dr.Read()) { result = ReadAccount(dr); } } return(result); }
/// <summary> /// Gets the account. /// </summary> /// <param name="id">Id标识</param> /// <returns>Account.</returns> public Account GetAccount(long id) { var sql = $"select * from Asset_Account where Id='{id}'"; Account result = null; using (var dr = RepositoryContext.ExecuteDataReader(sql)) { if (dr.Read()) { result = ReadAccount(dr); } } return(result); }
public List <Trade> GetList(List <long> EntityIds) { var shareOrders = new List <Trade>(); var strSql = $"SELECT T.Id,T.EntityId,T.UserId,T.CreateTime FROM Things_Trade T WHERE EntityId IN ({EntityIds.ToSqlString()}) "; using (var dr = RepositoryContext.ExecuteDataReader(strSql)) { while (dr.Read()) { shareOrders.Add(ShareOrder(dr)); } } return(shareOrders); }
public UserMap GetSingle(long userId) { var sql = @"SELECT * FROM User_UserMap WHERE UserId = @UserId"; using (var reader = RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@UserId", userId))) { UserMap userMap = null; if (reader.Read()) { userMap = ReadUser(reader); } return(userMap); } }
/// <summary> /// 获取多个用户的账户 /// </summary> /// <param name="userIds">The user ids.</param> /// <returns>IList<Account>.</returns> public IList <Account> GetAccountByUserIds(IList <long> userIds) { var sql = $"select * from Asset_Account where UserId in ({userIds.ToSqlString()})"; IList <Account> result = new List <Account>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadAccount(dr)); } } return(result); }
public UserMap GetParentMap(long userId) { var sql = @"SELECT ParentMap FROM User_UserMap WHERE UserId = @UserId"; using (var reader = RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@userId", userId))) { var userMap = new UserMap(); if (reader.Read()) { userMap.ParentMap = reader["ParentMap"].ToString(); } return(userMap); } }
/// <summary> /// 获取s the un handled identifier list. /// </summary> public IList <long> GetUnHandledIdList() { var sql = $"select top 10 Id from Things_Trade where Status={(byte)ShareOrderStatus.Pending} order by id "; //每次处理10条 IList <long> list = new List <long>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { list.Add(dr.Read <long>("Id")); } } return(list); }
public User GetUserDetail(string userName) { var sql = $"{UserDetailSql} where User_User.UserName='******'"; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { User user = null; if (reader.Read()) { user = ReadUser(reader); user.Detail = ReadUserDetail(reader); user.Map = ReadUserMap(reader); } return(user); } }
public List <Order> GetOrders(int?day = 2) { var result = new List <Order>(); var sql = $"select OrderStatus,Id,CreateTime from Shop_order where OrderStatus= {Convert.ToInt16(OrderStatus.WaitingBuyerPay)} and DATEDIFF(DAY, Createtime,GETDATE())={day}"; using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadOrder(dr)); } } return(result); }
public IList <long> GetAllUserIdsWidthOutAccount() { var sql = "select Id from User_User where Id not in (select DISTINCT UserId from Asset_Account ) order by id "; IList <long> result = new List <long>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(dr["Id"].ConvertToLong(0)); } } return(result); }
public User UserTeam(long userId) { var sql = @"SELECT u.* ,vi.ServiceCenterUserId from User_User u ,(SELECT ud.userId ,ud.ServiceCenterUserId FROM user_userDetail ud ) vi WHERE vi.userId=u.id and u.Id=@Id"; using (var reader = RepositoryContext.ExecuteDataReader(sql, RepositoryContext.CreateParameter("@Id", userId))) { User user = null; if (reader.Read()) { user = ReadUser(reader); } return(user); } }
public IEnumerable <TaskQueue> GetUnhandledList() { var sql = @"SELECT Id, CreateTime ,ExecutionTime ,ExecutionTimes ,HandleTime ,IsHandled ,MaxExecutionTimes ,ModuleId , Parameter ,Type ,UserId FROM dbo.Task_TaskQueue WHERE IsHandled=0" ; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { var list = new List <TaskQueue>(); while (reader.Read()) { list.Add(ReaderSingle(reader)); } return(list); } }
public IList <ShareOrderReportItem> GetShareOrderReportItems(long shareOrderId) { var sql = $"select UserId,MoneyTypeId,Amount from Share_Reward where OrderId={shareOrderId} and MoneyTypeId!='E97CCD1E-1478-49BD-BFC7-E73A5D699006' "; IList <ShareOrderReportItem> list = new List <ShareOrderReportItem>(); using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var shareOrder = new ShareOrderReportItem { UserId = reader["UserId"].ConvertToLong(0), MoneyTypeId = reader["MoneyTypeId"].ToGuid(), Amount = reader["Amount"].ConvertToDecimal(0) }; list.Add(shareOrder); } } return(list); }
/// <summary> /// GetProductCount /// </summary> /// <param name="productIds"></param> public List <ProductCount> GetProductCount(List <long> productIds) { var sql = $@"select Count,userId,productId from Shop_OrderProduct inner join Shop_Order on Shop_Order.Id=Shop_OrderProduct.OrderId where ProductId in ({productIds.ToSqlString()}) and (OrderStatus!=200 and OrderStatus!=1)"; var result = new List <ProductCount>(); using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var productCount = new ProductCount { Count = reader["Count"].ConvertToLong(), ProductId = reader["ProductId"].ConvertToLong() }; result.Add(productCount); } } return(result); }
/// <summary> /// 获取最后一条记录 /// </summary> /// <param name="kpi"></param> public Kpi GetLastSingle(Kpi kpi) { var sqlWhere = $" UserId={kpi.UserId} and ModuleId='{kpi.ModuleId}' and Type={Convert.ToInt16(kpi.Type)}"; var timeTypeCondition = DataRecordExtension.GetTimeTypeCondition(kpi.Type, kpi.CreateTime); if (!timeTypeCondition.IsNullOrEmpty()) { sqlWhere += $" And {timeTypeCondition}"; } var sql = $"SELECT top 1 * FROM kpi_kpi WHERE {sqlWhere} order by id desc "; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { Kpi readKpi = null; if (reader.Read()) { readKpi = ReadKpi(reader); } return(readKpi); } }
public List <UserDetail> GetList(UserDetailInpt userDetail, out long count) { if (userDetail.PageIndex < 0) { throw new ArgumentNullException("pageIndex", "pageindex has to be greater than 1"); } if (userDetail.PageSize > 100) { userDetail.PageSize = 100; } var sqlWhere = string.Empty; var sqlCount = $"SELECT COUNT(Id) [Count] FROM User_User where 1=1 {sqlWhere}"; count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0; var sql = $@"SELECT TOP {userDetail.PageSize} * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id desc) AS RowNumber,* FROM User_UserDetail where 1=1 { sqlWhere } ) as A WHERE RowNumber > {userDetail.PageSize}*({userDetail.PageIndex}-1)"; var result = new List <UserDetail>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadUser(dr)); } } return(result); }
/// <summary> /// Gets the store width product sku. /// 根据商品SkuId获取商品Sku对象列表 /// 获取上架商品 /// </summary> /// <param name="productSkuIds">The product sku ids.</param> public IEnumerable <ProductSkuItem> GetProductSkuItemList(IEnumerable <long> productSkuIds) { var result = new List <ProductSkuItem>(); var sql = $"{ProdutSkuSql} where Shop_Product.ProductStatus=2 and Shop_ProductSku.Stock>0 and Shop_ProductSku.Id in ({productSkuIds.ToList().ToSqlString()})"; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var productSkuItem = new ProductSkuItem { ProductSkuId = reader["ProductSkuId"].ConvertToLong(0), ProductId = reader["ProductId"].ConvertToLong(0), StoreId = reader["StoreId"].ToObjectId(), Name = reader["name"].ToString(), Stock = reader["Stock"].ConvertToLong(), Bn = reader["Bn"].ToString(), DisplayPrice = reader["DisplayPrice"].ToString(), FenRunPrice = reader["FenRunPrice"].ToDecimal(), MarketPrice = reader["MarketPrice"].ToDecimal(), MaxPayPrice = reader["MaxPayPrice"].ToDecimal(), MinPayCash = reader["MinPayCash"].ToDecimal(), PropertyValueDesc = reader["PropertyValueDesc"].ToString(), ThumbnailUrl = reader["ThumbnailUrl"].ToString(), Price = reader["Price"].ToDecimal(), PriceStyleId = reader["PriceStyleId"].ToGuid(), Weight = reader["Weight"].ToDecimal(), DeliveryTemplateId = reader["DeliveryTemplateId"].ToString() }; result.Add(productSkuItem); } } return(result); }
/// <summary> /// 根据实体Id列表获取订单金额 /// </summary> /// <param name="entityIdList">The entity identifier list.</param> public IEnumerable <PayShopOrderInfo> GetOrderPayAccount(List <object> entityIdList) { var result = new List <PayShopOrderInfo>(); // 读取未付支付订单的金额 var sql = $"select paymentAmount,AccountPay from Shop_Order where OrderStatus=1 and id in ({entityIdList.ToSqlString()})"; using (var reader = RepositoryContext.ExecuteDataReader(sql)) { while (reader.Read()) { var payShopInfoItem = new PayShopOrderInfo { PaymentAmount = reader["PaymentAmount"].ToDecimal(), AccountPay = reader["AccountPay"].ToStr() }; payShopInfoItem.AccountPayPair = payShopInfoItem.AccountPay.DeserializeJson <List <KeyValuePair <Guid, decimal> > >(); result.Add(payShopInfoItem); } } return(result); }
public List <ProductItem> GetProductItems(ProductApiInput input, out long count) { if (input.PageIndex < 0) { input.PageIndex = 1; } if (input.PageSize > 100) { input.PageSize = 100; } #region 标签 分类查询 var TcSql = string.Empty; if (input.ClassIds != null && input.ClassIds.Count > 1) { TcSql = $"{TcSql} AND Type='{typeof(ProductClassRelation).FullName}' AND Id in ({input.ClassIds.ToSqlString()}) or FatherId in ({input.ClassIds.ToSqlString()}) "; } if (input.TagIds != null && input.TagIds.Count > 1) { TcSql = $"{TcSql} AND Type='{typeof(ProductTagRelation).FullName}' AND Id in ({input.TagIds.ToSqlString()}) or FatherId in ({input.TagIds.ToSqlString()}) "; } if (!string.IsNullOrEmpty(TcSql)) { TcSql = $@"SELECT DISTINCT EntityId FROM Basic_RelationIndex WHERE RelationId IN( SELECT Id FROM Basic_Relation WHERE 1=1 {TcSql} )"; } #endregion 标签 分类查询 var sqlWhere = string.Empty; if (!string.IsNullOrEmpty(input.Keyword)) { sqlWhere = $"{sqlWhere} AND Name Like '%{input.Keyword}%'"; } if (input.MinPrice.HasValue) { sqlWhere = $"{sqlWhere} AND Price>{input.MinPrice}"; } if (input.MaxPrice.HasValue) { sqlWhere = $"{sqlWhere} AND Price<{input.MaxPrice}"; } if (input.PriceStyleId.HasValue) { sqlWhere = $"{sqlWhere} AND PriceStyleId ='{input.PriceStyleId}'"; } if (input.BrandId.HasValue) { sqlWhere = $"{sqlWhere} AND BrandId ='{input.BrandId}'"; } if (!TcSql.IsNullOrEmpty()) { sqlWhere = $"{sqlWhere} AND Id in ({TcSql})"; } if (!input.ProductIds.IsNullOrEmpty()) { sqlWhere = $"{sqlWhere} AND Id in ({input.ProductIds})"; } //商品状态过滤 商品店铺过滤 sqlWhere = $"{sqlWhere} AND ProductStatus= {(int)GoodsStatus.Online}"; // sqlWhere = $"{sqlWhere} AND StoreId>0"; // 库存 sqlWhere = $"{sqlWhere} AND Stock >= 1 "; var priceStyleId = Guid.Empty; // 商城模式状态不正常,获取不存在的,商品不显示 if (input.PriceStyles != null && input.PriceStyles.Count > 0 && !input.PriceStyleId.IsGuidNullOrEmpty()) { input.PriceStyles = input.PriceStyles .Where(r => input.PriceStyleId != null && r.Id == (Guid)input.PriceStyleId).ToList(); var priceStyleIdList = input.PriceStyles.Select(r => r.Id).ToList(); sqlWhere = $"{sqlWhere} AND PriceStyleId ='{input.PriceStyleId}' "; } var sqlCount = $"SELECT COUNT(Id) [Count] FROM Shop_Product where 1=1 {sqlWhere}"; count = RepositoryContext.ExecuteScalar(sqlCount)?.ConvertToLong() ?? 0; //排序处理 desc 降序 var sort = string.Empty; if (input.SortOrder != ProductSortOrder.Defualt) { sort = input.SortOrder.ToString(); } else { sort = "Id"; } if (input.OrderType == 0) { sort = $"{sort} desc"; } // 如果有传入指定数量,不分页,输出具体的数量 if (input.Count > 0) { input.PageSize = input.Count; input.PageIndex = 1; } var sql = $@"SELECT TOP {input.PageSize} * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {sort}) AS RowNumber,* FROM Shop_Product where 1=1 { sqlWhere } ) as A WHERE RowNumber > {input.PageSize}*({input.PageIndex}-1) "; var result = new List <ProductItem>(); using (var dr = RepositoryContext.ExecuteDataReader(sql)) { while (dr.Read()) { result.Add(ReadProduct(dr)); } } return(result); }