/// <summary>
/// 收益统计
/// </summary>
protected void GetProfitStatistics(Guid userId)
{
//DynamicParameters dyParams = new DynamicParameters();
//dyParams.Add("@UserId", userId);
if (profitmodel == null)
{
profitmodel = new ProfitStatistics();
}
//string rsql = @"SELECT ISNULL(SUM(EarnMoney),0) FROM dbo.ExtendEarnRecord WITH(NOLOCK) WHERE UserID =@UserID";
//profitmodel.extenderMoney = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault<decimal>(TdConfig.ApplicationName,
// TdConfig.DBReportWrite, rsql, ref dyParams);
string err = "";
profitmodel.prizeAmount = new TuanDai.UserPrizeNew.Client.UserPrizeQueryClient(TdConfig.ApplicationName).GetRedPacketIncome(userId, out err);;
profitmodel.extenderMoney = GetExtendEarn(userId) ?? 0;
}
/// <summary>
/// 收益统计
/// </summary>
protected void GetProfitStatistics(Guid userId)
{
string sqlText = @"SELECT SUM(A1) AS dueAmount,SUM(A2) AS recAmount,SUM(A3) AS overDueAmount,SUM(A4) AS prizeAmount FROM (
SELECT ISNULL(a.InterestAmout,0)+ISNULL(a.TuandaiRedPacket,0)+ISNULL(a.PublisherRedPacket,0) A1,0 AS A2,0 AS A3,0 AS A4
FROM SubscribeDetail a WITH(NOLOCK)
left join SubscribeExtend b with(nolock) on b.SubscribeId=a.SubscribeId
WHERE a.SubscribeUserId=@UserId
UNION ALL
SELECT 0 AS A1,0 A2 , case when s.isvip = 1 then ISNULL(SUM(ISNULL(B.OverDueInterest,0)),0)+ISNULL(SUM(ISNULL(B.PenaltyAmount,0)),0)/3*2 when s.isvip = 0 then ISNULL(SUM(ISNULL(B.OverDueInterest,0)),0) else 0 end A3,0 AS A4
FROM OverDueRecord B WITH(NOLOCK)
join Subscribe s with(nolock) on b.SubscribeId = s.Id
left join SubscribeExtend ex with(nolock) on ex.SubscribeId=s.Id
WHERE s.SubscribeUserId=@UserId AND B.IsBorrow=1 and isnull(ex.IsFTB,0)!=1
GROUP BY s.IsVip
UNION ALL
SELECT 0 AS A1,ISNULL(RealInterestAmout,0)+ISNULL(TuandaiRedPacket,0)+ISNULL(PublisherRedPacket,0)-ISNULL(InvestCommission,0) A2,0 AS A3,0 AS A4
FROM SubscribeDetailHistory_h1 a WITH(NOLOCK)
left join SubscribeExtend ex with(nolock) on ex.SubscribeId=a.SubscribeId
WHERE isnull(ex.IsFTB,0)!=1 and NOT EXISTS (SELECT b.SubscribeId,b.periods FROM dbo.OverDueRecord b WITH(NOLOCK) WHERE b.SubscribeId = a.SubscribeId AND b.periods = a.Periods
AND b.SubscribeUserId=@UserID AND b.IsBorrow=0 )
AND a.SubscribeUserId=@UserId
UNION ALL
SELECT 0 AS A1,0 AS A2,0 AS A3,ISNULL(SUM(ISNULL(PrizeValue,0)),0) AS A4 FROM UserPrize WITH(NOLOCK)
WHERE (TypeId IN (4,11,13,14) AND IsReceive=1 and ReceiveDate>=@usePrizeDate or TypeId=3 and IsUsed=1 and UseDate>=@usePrizeDate )AND UserId=@UserID
UNION ALL
SELECT isnull(DueComeInterest,0) as A1,isnull(NetEarningsInterest,0) + isnull(TuanDaiRedPacket,0) AS A2,0 AS A3, 0 AS A4
FROM We_FundAccountInfo a WITH(NOLOCK) where UserId=@UserId
) T";
DynamicParameters dyParams = new DynamicParameters();
dyParams.Add("@UserId", userId);
if (IsExistsInDBHistory(DateTime.Parse(DateTime.Today.AddMonths(-2).ToString("yyyy-MM-01")), DateTime.Parse(DateTime.Today.AddMonths(-1).ToString("yyyy-MM-01")).AddSeconds(-1), userId))
{
dyParams.Add("@usePrizeDate", DateTime.Today.AddMonths(-1).ToString("yyyy-MM-01"));
}
else
{
dyParams.Add("@usePrizeDate", DateTime.Today.AddMonths(-2).ToString("yyyy-MM-01"));
}
dyParams.Add("@extenderMoney", 0, DbType.Decimal, ParameterDirection.Output, 18, null, 2);
profitmodel = PublicConn.QuerySingle <ProfitStatistics>(sqlText, ref dyParams);
if (profitmodel == null)
{
profitmodel = new ProfitStatistics();
}
string rsql = @"SELECT ISNULL(SUM(EarnMoney),0) FROM dbo.ExtendEarnRecord WITH(NOLOCK) WHERE UserID =@UserID and HandleStatus = 1";
profitmodel.extenderMoney = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <decimal>(TdConfig.ApplicationName,
TdConfig.DBReportWrite, rsql, ref dyParams);
NetEarningsInterest = decimal.Parse(ToolStatus.ConvertLowerMoney(profitmodel.recAmount));
DueComeInterest = decimal.Parse(ToolStatus.ConvertLowerMoney(profitmodel.dueAmount));
//从历史库中查询红包金额
string sqlText2 = @"SELECT ISNULL(SUM(ISNULL(PrizeValue,0)),0) FROM UserPrize WITH(NOLOCK)
WHERE (TypeId IN (4,11,13,14) AND IsReceive=1 or TypeId=3 and IsUsed=1) AND UserId=@UserID ";
dyParams = new DynamicParameters();
dyParams.Add("@UserId", userId);
decimal dHistoryPrizeValue = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <decimal>(TdConfig.ApplicationName, TdConfig.DBHistoryRead, sqlText2, ref dyParams);
profitmodel.prizeAmount += dHistoryPrizeValue;
}
