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