/// <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);
}