//查询赎回费用 protected void GetRedemptionRate() { string strSQL = @" SELECT MonthStart, MonthEnd, CostRate FROM We_Redemption with(NoLock) ORDER BY MonthStart "; var para = new Dapper.DynamicParameters(); RateList = PublicConn.QueryBySql <WeRedemptionInfo>(strSQL, ref para); }
protected void GetProvinceList() { string strSQL = "SELECT AreaCode, AreaName FROM dbo.fq_AreaRegion WHERE ParentCode=0 ORDER BY AreaCode"; Dapper.DynamicParameters dyParams = new Dapper.DynamicParameters(); provinceList = PublicConn.QueryBySql <AddressInfo>(strSQL, ref dyParams); }
/// <summary> /// 获取回款记录明细 /// </summary> /// <param name="subscribeId">申购Id</param> /// <returns></returns> protected List <SubscribeInfo> GetSubscribeDetailList(Guid subscribeId) { List <SubscribeInfo> list = null; string sqlText = @"SELECT ISNULL(Amount,0) Amount,ISNULL(InterestAmout,0) InterestAmout,ISNULL(InvestCommission,0) Commssion, ISNULL(TuandaiRedPacket,0) TuandaiRedPacket,Periods,CycDate,[Desc],PenaltyAmount,IsBorrow,AddDate as OverDueDate,isnull(PublisherRedPacket,0) as PublisherRedPacket,InvestDate FROM ( SELECT a.Amount,a.InterestAmout,a.InvestCommission,a.BorrorCommission,a.TuandaiRedPacket,a.Periods,a.CycDate,'待回款' as [Desc], CASE c.IsVip WHEN 0 THEN 0 WHEN 1 THEN (b.PrePenalty/2+(b.PenaltyAmount-b.PrePenalty)*2/3) ELSE 0 END AS PenaltyAmount,b.IsBorrow,b.AddDate, a.PublisherRedPacket, c.AddDate as InvestDate FROM SubscribeDetail a LEFT JOIN OverDueRecord b ON a.SubscribeId=b.SubscribeId AND a.Periods=b.periods INNER JOIN Subscribe c ON a.SubscribeId=c.Id WHERE a.SubscribeId=@SubscribeId UNION SELECT a.RealAmount Amount,a.RealInterestAmout InterestAmout,a.InvestCommission,a.BorrorCommission, a.TuandaiRedPacket,a.Periods,ISNULL(a.RepayAdvanceDate,a.CycDate) CycDate,'已回款' as [Desc], CASE c.IsVip WHEN 0 THEN 0 WHEN 1 THEN (b.PrePenalty/2+(b.PenaltyAmount-b.PrePenalty)*2/3) ELSE 0 END AS PenaltyAmount,b.IsBorrow,b.AddDate, a.PublisherRedPacket, c.AddDate as InvestDate FROM td_SubDetailHis.dbo.SubscribeDetailHistory_h1 a LEFT JOIN OverDueRecord b ON a.SubscribeId=b.SubscribeId AND a.Periods=b.periods INNER JOIN Subscribe c ON a.SubscribeId=c.Id WHERE a.SubscribeId=@SubscribeId )t ORDER BY Periods ASC"; var para = new Dapper.DynamicParameters(); para.Add("@SubscribeId", subscribeId); list = PublicConn.QueryBySql <SubscribeInfo>(sqlText, ref para); return(list); }
/// <summary> /// 获取用户信息 /// </summary> /// <param name="appName"></param> /// <param name="telNo"></param> /// <returns></returns> private TdUserInfo GetUserInfo(string telNo) { if (string.IsNullOrEmpty(Context.Request["app"].Trim()) || string.IsNullOrEmpty(telNo.Trim())) { return(null); } string sql = @"SELECT a.*,b.RegisterFrom from UserBasicInfo as a with(nolock) left join UserRegisterFrom as b with(nolock) on a.Id=b.UserId where TelNo=@TelNo"; //DynamicParameters pars = new DynamicParameters(); //pars.Add("TelNo", telNo); //List<TdUserInfo> users = TuanDaiDB.Query<TdUserInfo>(ConnectionType.PCProjectRead, sql, ref pars); //if (users == null || users.Count == 0) return null; //return users[0]; DynamicParameters pars = new DynamicParameters(); pars.Add("TelNo", telNo); List <TdUserInfo> users; users = PublicConn.QueryBySql <TdUserInfo>(sql, ref pars); if (users == null || users.Count == 0) { return(null); } return(users[0]); }
/// <summary> /// 绑定项目进度 /// </summary> private void BindProjectProgress() { List <ProjectProgress_Info> listProjectProgress_Info = new List <ProjectProgress_Info>(); List <ProjectProgressImage_Info> listProjectProgressImage_Info = new List <ProjectProgressImage_Info>(); DynamicParameters param = new DynamicParameters(); param.Add("@ProjectId", ProjectId); string strProgress = @"SELECT Id,ISNULL([Desc],'') AS [DESC],ProgressDate FROM dbo.ProjectProgress WHERE ProjectId=@ProjectId ORDER BY ProgressDate DESC"; string strstrProgressImage = "SELECT ProgressId,ISNULL(MinImageUrl,'') AS MinImageUrl,ISNULL(MaxImageUrl,'') AS MaxImageUrl,SortOrder FROM dbo.ProjectProgressImage WHERE ProjectId=@ProjectId ORDER BY SortOrder ASC,AddDate ASC"; listProjectProgress_Info = PublicConn.QueryBySql <ProjectProgress_Info>(strProgress, ref param); listProjectProgressImage_Info = PublicConn.QueryBySql <ProjectProgressImage_Info>(strstrProgressImage, ref param); List <ProjectProgress> listProjectProgress = new List <ProjectProgress>(); foreach (var itemA in listProjectProgress_Info) { ProjectProgress mProjectProgress = new ProjectProgress(); mProjectProgress.ProgressDate = itemA.ProgressDate; mProjectProgress.Desc = itemA.Desc; mProjectProgress.listProjectProgressImage = listProjectProgressImage_Info.Where(p => p.ProgressId == itemA.Id).ToList(); listProjectProgress.Add(mProjectProgress); } mPageModel.listProjectProgress = listProjectProgress; }
private void GetContractInfo() { string strSQL = @"SELECT a.Id as ProductId,a.ProductName, a.PlanAmount,a.ProjectTypes,a.Deadline,b.RepeatInvestType, b.YearRate, b.MinYearRate, a.StartDate,a.UnitAmount, b.Amount as JoinAmount, b.OrderQty, b.UserId, b.OrderDate FROM dbo.We_Product a WITH(NOLOCK) left JOIN dbo.We_Order b WITH(NOLOCK) on b.ProductId=a.Id WHERE b.Id=@WeOrderId"; var dyParams = new Dapper.DynamicParameters(); dyParams.Add("@WeOrderId", Key); List <MyWeOrderInfo> list = new List <MyWeOrderInfo>(); list = PublicConn.QueryBySql <MyWeOrderInfo>(strSQL, ref dyParams); WeOrder = list.Count > 0 ? list[0] : new MyWeOrderInfo(); if (WeOrder.OrderDate >= DateTime.Parse("2016-03-11")) { companyName = "东莞团贷网互联网科技服务有限公司"; Sealt_TuanDai = "images/tuandai_ss.png"; } this.subBasicModel = userbll.GetUserBasicInfoModelById(WeOrder.UserId);//投资人信息 switch (GetTypeWord(WeOrder.ProductId).ToLower()) { case "b": case "c": RepaymentTypeDesc = "每月付息"; break; case "a": RepaymentTypeDesc = "每月付息或到期本息"; break; } }
/// <summary> /// 绑定数据 /// </summary> /// <param name="type">0:省 1:市 2:县</param> /// <returns></returns> private List <AddressInfo> GetList(string type, int id) { List <AddressInfo> list = null; string sqlText = string.Empty; DynamicParameters dyParams = new DynamicParameters(); if (type == "0") { sqlText = @"SELECT m_ProId AS ProId,m_ProName AS ProName FROM dbo.t_Mall_Province"; list = PublicConn.QueryBySql <AddressInfo>(sqlText, ref dyParams); } else if (type == "1") { sqlText = @"SELECT m_CityID AS ProId,m_CityName AS ProName FROM dbo.t_Mall_City WHERE m_ProId=@m_ProId"; dyParams = new DynamicParameters(); dyParams.Add("@m_ProId", id); list = PublicConn.QueryBySql <AddressInfo>(sqlText, ref dyParams); } else if (type == "2") { sqlText = @"SELECT m_Id AS ProId,m_DisName AS ProName FROM dbo.t_Mall_District WHERE m_CityID=@m_CityID"; dyParams = new DynamicParameters(); dyParams.Add("@m_CityID", id); list = PublicConn.QueryBySql <AddressInfo>(sqlText, ref dyParams); } return(list); }
/// <summary> /// 中奖纪录 /// </summary> public void GetRecord() { IList <RecordInfo> list; DynamicParameters dyParams = new DynamicParameters(); string sqlText = "SELECT TOP 60 UserName,PrizeName FROM dbo.Activity_Guangzhou_Record WITH(NOLOCK) ORDER BY AddDate DESC"; list = PublicConn.QueryBySql <RecordInfo>(sqlText, ref dyParams); StringBuilder sb = new StringBuilder(); if (list != null && list.Count() > 0) { int index = 1; sb.Append("{\"result\":\"1\",\"list\":["); foreach (RecordInfo temp in list) { if (index == list.Count()) { sb.Append("{\"PrizeName\":\"" + temp.PrizeName + "\",\"UserName\":\"" + BusinessDll.StringHandler.MaskTelNo(temp.UserName) + "\"}]}"); } else { sb.Append("{\"PrizeName\":\"" + temp.PrizeName + "\",\"UserName\":\"" + BusinessDll.StringHandler.MaskTelNo(temp.UserName) + "\"},"); } index++; } } else { sb.Append("{\"result\":\"0\"}"); } this.Context.Response.Write(sb.ToString()); this.Context.Response.End(); }
public static UserEnterpriseInfo GetBorrowerGuaranteeEnterprise(Guid borrowerUserId, DateTime AddDate) { string sql = "select * from UserInEnterprise with(nolock) where BorrowerUserId=@BorrowerUserId"; var dyParams = new Dapper.DynamicParameters(); dyParams.Add("@BorrowerUserId", borrowerUserId); List <UserInEnterpriseInfo> UserInEnterpriseList = PublicConn.QueryBySql <UserInEnterpriseInfo>(sql, ref dyParams); UserInEnterpriseInfo userInEnterprise; if (UserInEnterpriseList.Count() > 1) { userInEnterprise = UserInEnterpriseList.Where(p => p.CreateDate < AddDate).OrderByDescending(p => p.CreateDate).FirstOrDefault(); } else { userInEnterprise = UserInEnterpriseList.FirstOrDefault(); } if (userInEnterprise != null) { Guid EnterpriseUserId = userInEnterprise.EnterpriseUserId; // UserEnterprise userenter = db.UserEnterprise.FirstOrDefault(p => p.UserId == EnterpriseUserId); sql = "select * from UserEnterprise with(nolock) where UserId=@UserId"; dyParams = new Dapper.DynamicParameters(); dyParams.Add("@UserId", EnterpriseUserId); UserEnterpriseInfo userenter = PublicConn.QuerySingle <UserEnterpriseInfo>(sql, ref dyParams); return(userenter); } else { return(null); } }
protected void GetYearRate(string projectId) { string sqlText = @"SELECT * FROM ProjectRateContrast WITH(NOLOCK) WHERE ProjectId=@ProjectId "; var para = new Dapper.DynamicParameters(); para.Add("@ProjectId", projectId); listRateInfo = PublicConn.QueryBySql <ProjectRateContrastInfo>(sqlText, ref para); }
/// <summary> /// 获取某类复投宝的利率设置 /// </summary> /// <param name="productTypeId">产品类型Id</param> /// <returns></returns> public List <WeProductFTBRateInfo> GetWeFTBRateList(int productTypeId, DateTime publisherDate) { string strSQL = "select * from We_ProductFTBRate where ProductTypeId=@typeId and ( (@publisherDate between StartDate and EndDate) or StartDate is null) order by MonthType"; Dapper.DynamicParameters dyParams = new Dapper.DynamicParameters(); dyParams.Add("@typeId", productTypeId); dyParams.Add("@publisherDate", publisherDate); return(PublicConn.QueryBySql <WeProductFTBRateInfo>(strSQL, ref dyParams)); }
/// <summary> /// 资料图片 /// </summary> private void BindProjectImg() { DynamicParameters param = new DynamicParameters(); param.Add("@ProjectId", ProjectId); string strProject = @"SELECT ImageSource AS LargeImg, REPLACE(ImageSource,(SUBSTRING (ImageSource, 1, LEN(ImageSource)-CHARINDEX('.', REVERSE(ImageSource)))),SUBSTRING (ImageSource, 1, LEN(ImageSource)-CHARINDEX('.', REVERSE(ImageSource)))+'_s') AS SmallImg,Name FROM dbo.ProjectImage WHERE ProjectId=@ProjectId AND Type=1 ORDER BY AddDate DESC"; mPageModel.listProjectImage = PublicConn.QueryBySql <ProjectImage_Info>(strProject, ref param); }
private string GetTypeWord(Guid productId) { string sql = @"select b.TypeWord from dbo.We_Product a left join We_ProductType b on b.Id =a.ProductTypeId where a.Id=@id"; var whereParams = new Dapper.DynamicParameters(); whereParams.Add("@id", productId); List <string> list = new List <string>(); list = PublicConn.QueryBySql <string>(sql, ref whereParams); return(list.Count > 0 ? list[0] : string.Empty); }
//债权转让 protected void GetZQZRSubscribelist(Guid projectId) { var commandText = @"select UserName,NickName,Amount, TenderMode as OrderType,TenderMode, SubscribeShares,TotalShares,LowerUnit,InterestRate,AddDate as OrderDate,TelNo from ( select ROW_NUMBER() over(order by S.AddDate desc) as num_,C.UserName,C.TelNo as NickName,isnull(S.ReceiveAmount,0)+isnull(s.ReceiveInterest,0) as Amount,S.TenderMode,S.TranDate as AddDate ,S.SubscribeShares,S.TotalShares,(isnull(D.m_TotalAmount,0)+dbo.f_TransferPriceCal(S.Id,isnull(D.m_TotalShares,0)))/isnull(D.m_TotalShares,0) as LowerUnit, isnull(dbo.f_TransferProjectRate(D.m_Id),0) as InterestRate,isnull(S.IsAuto,0) as IsAuto,C.TelNo from dbo.Subscribe S with(nolock) inner join UserBasicInfo C on S.SubscribeUserId=C.Id inner join t_SubScribeTransfer as D on D.m_id=S.TranId where s.tranid=@ProjectId ) T where T.num_>@PageSize*(@PageIndex-1) and T.num_<=@PageSize*@PageIndex" ; DynamicParameters dyParams = new DynamicParameters(); dyParams.Add("@ProjectId", projectId); dyParams.Add("@PageIndex", 1); dyParams.Add("@PageSize", 15); dataList = PublicConn.QueryBySql <SubscribeUserInfo>(commandText, ref dyParams); if (dataList.Any()) { dataList.ForEach(o => o.OrderType = GetProjectTenderName(o.OrderType.ToInt(1))); } var commandTotalText = @"select COUNT(0) from dbo.Subscribe S with(nolock) --inner join dbo.Project P on S.ProjectId=P.Id inner join UserBasicInfo C on S.SubscribeUserId=C.Id inner join t_SubScribeTransfer as D on D.m_id=S.TranId where s.tranid=@ProjectId" ; dyParams = new DynamicParameters(); dyParams.Add("@ProjectId", projectId); int count = PublicConn.QuerySingle <int>(commandTotalText, ref dyParams); pageCount = GetPageCount(count); }
/// <summary> /// 获取奖品详情 /// </summary> /// <returns></returns> private bool GetPrizeDetail() { var dyParams = new DynamicParameters(); string strSQL = string.Format(@"SELECT Id,SKU,ProductName,ActivityName,ISNULL(ActivityUrl,'') ActivityUrl, MarketPrice,ISNULL(Remark,'') Remark,ImageUrl,Description, CreateDate,SortOrder, CASE Upselling WHEN 1 THEN '已上架' ELSE '已下架' END as Upselling FROM Mall_Product with(nolock) WHERE 1=1 AND Id=@Id SELECT TOP 15 ProductName,ImageUrl FROM Mall_Product WHERE Upselling=1 AND Id NOT IN (@Id) ORDER BY SortOrder,CreateDate DESC "); dyParams.Add("@Id", PrizeId); var _PrizeModels = PublicConn.QuerySingle <Mall_ProductInfo>(strSQL, ref dyParams); var _OtherPrizeModels = PublicConn.QueryBySql <Mall_ProductImage>(strSQL, ref dyParams); if (_PrizeModels == null) { return(false); } else { Model.ImageUrl = _PrizeModels.ImageUrl; Model.ProductName = _PrizeModels.ProductName; Model.MarketPrice = _PrizeModels.MarketPrice == null ? "0.00" : Convert.ToString(_PrizeModels.MarketPrice); Model.Remark = _PrizeModels.Remark; Model.ActivityName = _PrizeModels.ActivityName; Model.ActivityUrl = _PrizeModels.ActivityUrl; Model.Description = _PrizeModels.Description; if (_OtherPrizeModels != null && _OtherPrizeModels.Count() > 0) { Model.OtherPrizes = _OtherPrizeModels; } else { Model.OtherPrizes = null; } return(true); } }
/// <summary> /// 获取回款记录明细 /// </summary> /// <param name="subscribeId">申购Id</param> /// <returns></returns> protected List <SubscribeInfo> GetSubscribeDetailList(Guid subscribeId) { List <SubscribeInfo> list = null; string sqlText = @"select CycDate,Amount,InterestAmout,ISNULL(InvestCommission,0) AS Commission,ISNULL(TuandaiRedPacket,0) AS TuandaiRedPacket,ISNULL(PublisherRedPacket,0)AS PublisherRedPacket,Periods,0 as OverDueAmount,0 RealAmount,0 AdvanceAmount,NULL IsRefundAdvance,1 Status,'待回款' AS [Desc] from SubscribeDetail with(nolock) where SubscribeId=@SubscribeID order by Periods"; var para = new Dapper.DynamicParameters(); para.Add("@SubscribeID", subscribeId); list = PublicConn.QueryBySql <SubscribeInfo>(sqlText, ref para); sqlText = @"select Adddate CycDate,RealAmount Amount,RealInterestAmout InterestAmout,isnull(InvestCommission, 0) as Commission,isnull(TuandaiRedPacket, 0) TuandaiRedPacket,isnull(PublisherRedPacket, 0) PublisherRedPacket,Periods ,0 OverDueAmount,0 RealAmount,0 AdvanceAmount,NULL IsRefundAdvance,2 as Status ,'已回款' as [Desc] from SubscribeDetailHistory_h1 with(nolock) where SubscribeId=@SubscribeID order by Periods"; var hlist = TuanDai.DB.TuanDaiDB.Query <SubscribeInfo>(TdConfig.ApplicationName, TdConfig.DBSubDetailHisRead, sqlText, ref para); if (hlist != null && hlist.Count > 0) { foreach (var info in hlist) { info.IsBorrow = true; sqlText = string.Empty; sqlText = @"select b.Adddate OverDueDate,b.IsBorrow from OverDueRecord B with(nolock) where B.SubscribeId=@SubscribeID and B.periods=@periods and isnull(b.IsHide,0)=0"; SubscribeInfo s = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <SubscribeInfo>(TdConfig.ApplicationName, TdConfig.DBRead, sqlText, ref para); if (s != null) { info.IsBorrow = s.IsBorrow; info.OverDueDate = s.OverDueDate; } } list.AddRange(hlist); } if (list != null && list.Count > 0) { list = list.OrderBy(o => o.Periods).ToList(); } return(list); }
public void GetInterestPayable() { Guid subscribeId = Guid.Empty; Guid.TryParse(Context.Request["sid"], out subscribeId); int shares = 0; int.TryParse(Context.Request["transfershares"], out shares); //using (System.Data.SqlClient.SqlConnection connection = TuanDai.PortalSystem.DAL.PubConstant.CrateReadConnection()) //{ string sql = "SELECT ISNULL(dbo.f_TransferPriceCal(@SubscribeId,@shares),0)"; var dyParams = new Dapper.DynamicParameters(); dyParams.Add("@SubscribeId", subscribeId); dyParams.Add("@shares", shares); decimal interestPaya = PublicConn.QueryBySql <decimal>(sql, ref dyParams).Sum(); // decimal interestPaya = connection.Query<decimal>(sql, dyParams).Sum(); PrintJson("1", interestPaya.ToString("0.00")); //} }
protected void GetWeAutoString(string projectTypes) { WeDeadlines = string.Empty; string sql = @"SELECT YearRate,Deadline FROM dbo.We_ProductType with(nolock) WHERE Id IN(" + projectTypes + ") order by YearRate"; var para = new Dapper.DynamicParameters(); var weAuto = PublicConn.QueryBySql <SimpleWeAuto>(sql, ref para); if (weAuto != null) { WeMinRate = ToolStatus.DeleteZero(weAuto[0].YearRate); foreach (var item in weAuto) { if (string.IsNullOrEmpty(WeDeadlines)) { WeDeadlines = item.Deadline.ToString(); } else { WeDeadlines += "," + item.Deadline.ToString(); } } } }
protected void Page_Load(object sender, EventArgs e) { string type = WEBRequest.GetQueryString("type"); string code = ""; switch (type.Trim().ToLower()) { case "": string ReturnUrl = WEBRequest.GetQueryString("ReturnUrl"); code = WEBRequest.GetQueryString("code"); string goBackUrl = ReturnUrl; if (ReturnUrl.IndexOf("?") != -1) { goBackUrl += "&code=" + code; } else { goBackUrl += "?code=" + code; } Response.Redirect(goBackUrl); break; //获取微细OpenId case "getopenid": #region Response.ContentType = "application/json"; try { code = WEBRequest.GetQueryString("code"); ThirdLoginSDK sdkApi = new ThirdLoginSDK(); sdkApi.InitSDK(ThirdLoginSDK.ThirdLoginType.WeiXin); string wxOpenId = sdkApi.GetCookieOpenId(code); TuanDai.LogSystem.LogClient.LogClients.TraceLog(TdConfig.ApplicationName, "getopenid", "", wxOpenId); this.PrintJson("1", wxOpenId); } catch (Exception ex) { this.PrintJson("-100", "程序异常:" + ex.Message); } finally { Response.End(); } break; #endregion //获取微信用户信息 case "getuserinfor": #region Response.ContentType = "application/json"; try { DateTime beginTime = DateTime.Now; code = WEBRequest.GetQueryString("code"); //写入openid 因为code被使用过一次后,就会失效,缓存的openid就取不到。 string openId = WEBRequest.GetQueryString("openid"); if (openId.IsNotEmpty()) { GlobalUtils.WriteOpenIdToCookie(openId); } ThirdLoginSDK sdk = new ThirdLoginSDK(); sdk.InitSDK(ThirdLoginSDK.ThirdLoginType.WeiXin); //获取微信上用户信息 TuanDai.WXApiWeb.Common.ThirdLoginSDK.WXOAuthUser wxUserInfo = sdk.GetWXUserSubscribeInfor(code, openId.ToText()); var jsonStr = JsonHelper.ToJson(wxUserInfo); this.PrintJson("1", jsonStr); DateTime endTime = DateTime.Now; LogClients.TraceLog("WXTouch", "微信授权时间差", openId, (endTime - beginTime).TotalMilliseconds.ToString()); } catch (Exception ex) { this.PrintJson("-100", "程序异常:" + ex.Message); LogClients.ErrorLog("WXTouch", "微信授权错误", "", ex.Message + ex.StackTrace); } finally { Response.End(); } break; #endregion //获取Token case "getaccesstoken": #region string token = WEBRequest.GetQueryString("Token"); string configToken = ConfigHelper.getConfigString("APIKey"); Response.ContentType = "application/json"; try { if (configToken != token) { this.PrintJson("99", "验证不通过!"); return; } //从同一个缓存中读取全局的Token ThirdLoginSDK sdkApi = new ThirdLoginSDK(); sdkApi.InitSDK(ThirdLoginSDK.ThirdLoginType.WeiXin); TuanDai.WXApiWeb.Common.ThirdLoginSDK.WXOAuth_Token accessToken = sdkApi.GetWXUserOpenIdByCgi(code); var jsonStr = JsonHelper.ToJson(accessToken); this.PrintJson("1", jsonStr); } catch (Exception ex) { this.PrintJson("-100", "程序异常:" + ex.Message); } finally { Response.End(); } break; #endregion //App中活动登录 case "appajaxlogin": #region Response.ContentType = "application/json"; try { string appActivityToken = WEBRequest.GetFormString("appActivityToken"); if (!string.IsNullOrEmpty(appActivityToken)) { TuanDai.PortalSystem.Model.UserBasicInfoInfo model = new TuanDai.PortalSystem.BLL.AppUserTokenRecBLL().GetAppActivityUser(appActivityToken); if (model != null) { string DOMAINNAME = ConfigurationManager.AppSettings["CookieDomain"]; Tool.CookieHelper.WriteCookie("TDW_WapUserName", model.UserName); string strLastLoginDate = DateTime.Now.ToString("yyyy-MM-dd") + model.UserName; if (string.IsNullOrEmpty(CookieHelper.GetCookie("TDLastLoginDate")) || CookieHelper.GetCookie("TDLastLoginDate") != strLastLoginDate) { TuanDai.PortalSystem.BLL.VipGetWorthBLL.AddGetWorth(model.Id, (int)ConstString.UserGrowthType.EveryDayFirstLogin, null, 0); } Tool.CookieHelper.WriteCookie(DOMAINNAME, "TDLastLoginDate", strLastLoginDate); WebUserAuth.SignIn(model.Id.ToString()); } else { WebUserAuth.SignOut(); } } this.PrintJson("1", "登录成功!"); } catch (Exception ex) { this.PrintJson("-1", ex.Message); } finally { Response.End(); } #endregion break; //获取用户推送开关设置 case "getwxpushswitch": #region token = WEBRequest.GetQueryString("Token"); configToken = ConfigHelper.getConfigString("APIKey"); string pushtype = WEBRequest.GetQueryString("PushType"); Response.ContentType = "application/json"; try { if (configToken != token) { this.PrintJson("99", "验证不通过!"); return; } string strSQL = "SELECT DISTINCT OpenId FROM dbo.UserWXNotice WHERE ISNULL(OpenId,'')!=''"; if (pushtype.ToLower() == "wexnotice") { strSQL += " and IsWeXNotice=1"; } else if (pushtype.ToLower() == "actnotice") { strSQL += " and IsActivityNotice=1"; } else { strSQL += " and 1=0"; } Dapper.DynamicParameters dyParams = new Dapper.DynamicParameters(); List <string> OpenIDList = PublicConn.QueryBySql <string>(strSQL, ref dyParams); var jsonStr = JsonHelper.ToJson(OpenIDList); this.PrintJson("1", jsonStr); } catch (Exception ex) { this.PrintJson("-100", "程序异常:" + ex.Message); } finally { Response.End(); } #endregion break; } }
private void GetBind() { interestBillInfo = new InterestBillInfo(); #region string sqlText = @" DECLARE @ExtendKey NVARCHAR(50); SELECT @UserAddDate=AddDate,@UserAddDay=DATEDIFF(Day,AddDate,GETDATE()),@ExtendKey=ExtendKey,@Sex=Sex,@HeadImage=HeadImage FROM dbo.UserBasicInfo WITH(NOLOCK) WHERE Id=@UserId; SELECT @ExtendCount=COUNT(0) FROM dbo.UserBasicInfo WITH(NOLOCK) WHERE ExtenderKey=@ExtendKey; SELECT @ReturnInMoney=SUM(ISNULL(InterestAmout,0)+ISNULL(Amount,0)) FROM dbo.SubscribeDetail WITH(NOLOCK) WHERE SubscribeUserId=@UserId; SELECT @TotalInMoney= SUM(A1)+SUM(A2)+SUM(A3)+SUM(A4) FROM --收益 ( SELECT SUM(ISNULL(InterestAmout,0)) AS A1,0 AS A2,0 AS A3,0 AS A4 FROM dbo.SubscribeDetail WITH(NOLOCK) WHERE SubscribeUserId=@UserId UNION ALL SELECT 0 AS A1,SUM(ISNULL(A.RealInterestAmout,0)) AS A2 ,ISNULL(SUM(B.Interest),0)+ISNULL(SUM(B.PenaltyAmount),0)/2 AS A3,0 AS A4 FROM dbo.SubscribeDetailHistory A WITH(NOLOCK) LEFT JOIN dbo.OverDueRecord B WITH(NOLOCK) ON A.SubscribeId=B.SubscribeId AND B.IsBorrow=0 WHERE A.SubscribeUserId=@UserId ) T; DECLARE @temp1 NUMERIC(18,2)=0 DECLARE @temp2 NUMERIC(18,2)=0 SELECT @temp2= COUNT(0) FROM FundAccountInfo WITH(NOLOCK) SELECT @temp1= A.TotalInvestRank FROM ( SELECT ROW_NUMBER() OVER(ORDER BY TotalIncomeInvest DESC) AS TotalInvestRank,UserId FROM dbo.FundAccountInfo WITH(NOLOCK)) AS A WHERE A.UserId=@UserId SELECT @TotalInMoneyPercent= CAST((@temp2-@temp1)/@temp2*100 AS NUMERIC(18,2)); "; DynamicParameters paramDatas = new DynamicParameters(); paramDatas.Add("@UserId", WebUserAuth.UserId.Value); paramDatas.Add("@UserAddDate", null, DbType.DateTime, ParameterDirection.Output); paramDatas.Add("@UserAddDay", 0, DbType.Int32, ParameterDirection.Output); paramDatas.Add("@ExtendCount", 0, DbType.Int32, ParameterDirection.Output); paramDatas.Add("@Sex", 0, DbType.Int32, ParameterDirection.Output); paramDatas.Add("@ReturnInMoney", 0, DbType.Double, ParameterDirection.Output); paramDatas.Add("@TotalInMoney", 0, DbType.Double, ParameterDirection.Output); paramDatas.Add("@TotalInMoneyPercent", 0, DbType.Double, ParameterDirection.Output); paramDatas.Add("@HeadImage", "", DbType.String, ParameterDirection.Output); PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, sqlText, ref paramDatas); interestBillInfo.UserAddDate = paramDatas.Get <DateTime>("@UserAddDate"); interestBillInfo.UserAddDay = paramDatas.Get <Int32>("@UserAddDay"); interestBillInfo.ExtendCount = paramDatas.Get <Int32>("@ExtendCount"); interestBillInfo.Sex = paramDatas.Get <Int32?>("@Sex"); interestBillInfo.ReturnInMoney = paramDatas.Get <Double?>("@ReturnInMoney"); interestBillInfo.TotalInMoney = paramDatas.Get <Double?>("@TotalInMoney"); interestBillInfo.TotalInMoneyPercent = paramDatas.Get <Double?>("@TotalInMoneyPercent"); interestBillInfo.HeadImage = paramDatas.Get <String>("@HeadImage"); if (string.IsNullOrWhiteSpace(interestBillInfo.HeadImage)) { interestBillInfo.HeadImage = "/imgs/users/avatar/bav_head.gif"; } int temp = (int)(interestBillInfo.ReturnInMoney == null ? 0 : interestBillInfo.ReturnInMoney); interestBillInfo.ReturnIntegral = temp / 1000 > 100 ? 100 : temp / 1000; // var r = 1.08 / 100; var d = Math.Pow(1 + r, 10 * 12); var P = temp * d; var P2 = temp * ((d - 1) / r); var pSum = P + P2; tenMoney = Math.Round(pSum); //价值 tenMoney = tenMoney < 100000 ? 100000 : tenMoney; double money = interestBillInfo.TotalInMoney == null ? 0 : Convert.ToDouble(interestBillInfo.TotalInMoney); int sex = interestBillInfo.Sex == null ? 1 : Convert.ToInt32(interestBillInfo.Sex); if (money <= 10000) { name = sex == 1 ? "普通男青年" : "普通女青年"; image = name == "普通男青年" ? "images/role/role1.png" : "images/role/普通女青年-.png"; } else if (money > 10000 && money <= 20000) { name = sex == 1 ? "文艺男青年" : "文艺女青年"; image = name == "文艺男青年" ? "images/role/文艺男青年-.png" : "images/role/文艺女青年-.png"; } else if (money > 20000 && money <= 30000) { name = sex == 1 ? "经济适用男" : "小家碧玉"; image = name == "经济适用男" ? "images/role/经济适用男.png" : "images/role/小家碧玉.png"; } else if (money > 30000 && money <= 50000) { name = sex == 1 ? "小资男" : "陀枪师姐"; image = name == "小资男" ? "images/role/小姿男.png" : "images/role/陀枪师姐.png"; } else if (money > 50000 && money <= 70000) { name = sex == 1 ? "城市精英" : "百变御姐"; image = name == "城市精英" ? "images/role/城市精英.png" : "images/role/百变御姐.png"; } else if (money > 70000 && money <= 100000) { name = sex == 1 ? "多金男" : "白富美"; image = name == "多金男" ? "images/role/多金男.png" : "images/role/白富美.png"; } else if (money > 100000 && money <= 150000) { name = sex == 1 ? "土豪" : "选美皇后"; image = name == "土豪" ? "images/role/土豪.png" : "images/role/选美皇后.png"; } else if (money > 150000 && money <= 300000) { name = sex == 1 ? "国民老公" : "国民美女"; image = name == "国民老公" ? "images/role/国民老公.png" : "images/role/国民美女.png"; } else if (money > 300000) { name = sex == 1 ? "霸道总裁" : "富婆"; image = name == "霸道总裁" ? "images/role/霸道总裁-.png" : "images/role/富婆.png"; } #endregion #region InvestList = new List <BillInfo>(); sqlText = @"SELECT ISNULL(SUM(t.ParaValue3),0) AS ParaValue3,t.ParaValue1 FROM ( SELECT ISNULL(Amount,0) AS ParaValue3,ParaValue1= CASE WHEN AddDate>='2012-07-01' AND AddDate<'2013-01-01' THEN 1 WHEN AddDate>='2013-01-01' AND AddDate<'2013-07-01' THEN 2 WHEN AddDate>='2013-07-01' AND AddDate<'2014-01-01' THEN 3 WHEN AddDate>='2014-01-01' AND AddDate<'2014-07-01' THEN 4 WHEN AddDate>='2014-07-01' AND AddDate<'2015-01-01' THEN 5 WHEN AddDate>='2015-01-01' AND AddDate<'2015-07-01' THEN 6 ELSE 0 END FROM dbo.Subscribe WITH(NOLOCK) WHERE SubscribeUserId=@UserId ) t GROUP BY t.ParaValue1"; DynamicParameters param = new DynamicParameters(); param.Add("@UserId", WebUserAuth.UserId.Value); InvestList = PublicConn.QueryBySql <BillInfo>(sqlText, ref param); InvestMoney = InvestList.Where(p => p.ParaValue1 != 0).Sum(p => p.ParaValue3); #endregion #region ReturnList = new List <BillInfo>(); sqlText = @"SELECT ISNULL(SUM(t.ParaValue3),0) AS ParaValue3,t.ParaValue1 FROM ( SELECT ISNULL(RealInterestAmout,0) AS ParaValue3,ParaValue1= CASE WHEN CycDate>='2012-07-01' AND CycDate<'2013-01-01' THEN 1 WHEN CycDate>='2013-01-01' AND CycDate<'2013-07-01' THEN 2 WHEN CycDate>='2013-07-01' AND CycDate<'2014-01-01' THEN 3 WHEN CycDate>='2014-01-01' AND CycDate<'2014-07-01' THEN 4 WHEN CycDate>='2014-07-01' AND CycDate<'2015-01-01' THEN 5 WHEN CycDate>='2015-01-01' AND CycDate<'2015-07-01' THEN 6 ELSE 0 END FROM dbo.SubscribeDetailHistory WITH(NOLOCK) WHERE SubscribeUserId=@UserId ) t GROUP BY t.ParaValue1"; DynamicParameters para = new DynamicParameters(); para.Add("@UserId", WebUserAuth.UserId.Value); ReturnList = PublicConn.QueryBySql <BillInfo>(sqlText, ref para); ReturnMoney = ReturnList.Where(p => p.ParaValue1 != 0).Sum(p => p.ParaValue3); #endregion #region ProjectList = new List <BillInfo>(); sqlText = @"SELECT ParaValue2,COUNT(0) AS ParaValue3 FROM ( SELECT ParaValue2 = CASE WHEN dbo.f_ProjectPartition(p.Type)=1 THEN '小微企业' WHEN dbo.f_ProjectPartition(p.Type)=2 THEN '微团贷' WHEN dbo.f_ProjectPartition(p.Type)=3 THEN '证券宝' WHEN dbo.f_ProjectPartition(p.Type)=4 THEN '分期宝' ELSE '净股专区' END FROM dbo.Subscribe s WITH(NOLOCK) LEFT JOIN Project p WITH(NOLOCK) ON s.ProjectId=p.Id WHERE s.SubscribeUserId=@UserId AND dbo.f_ProjectPartition(p.Type) IS NOT NULL ) t GROUP BY t.ParaValue2"; DynamicParameters paramm = new DynamicParameters(); paramm.Add("@UserId", WebUserAuth.UserId.Value); ProjectList = PublicConn.QueryBySql <BillInfo>(sqlText, ref paramm); double projectCount = ProjectList.Sum(p => p.ParaValue3); int percent = 0; for (int i = 0; i < ProjectList.Count; i++) { if (i == ProjectList.Count - 1) { ProjectList[i].ParaValue1 = 100 - percent; } else { ProjectList[i].ParaValue1 = (int)(ProjectList[i].ParaValue3 / projectCount * 100); percent += ProjectList[i].ParaValue1; } } #endregion #region ExtendtList = new List <BillInfo>(); sqlText = @"DECLARE @ExtendKey NVARCHAR(50); SELECT @ExtendKey=ExtendKey FROM dbo.UserBasicInfo WITH(NOLOCK) WHERE Id=@UserId; SELECT TOP 7 NickName AS ParaValue2,HeadImage AS ParaValue4 FROM dbo.UserBasicInfo WITH(NOLOCK) WHERE ExtenderKey=@ExtendKey ORDER BY AddDate DESC; "; DynamicParameters parammm = new DynamicParameters(); parammm.Add("@UserId", WebUserAuth.UserId.Value); ExtendtList = PublicConn.QueryBySql <BillInfo>(sqlText, ref parammm); foreach (var item in ExtendtList) { if (string.IsNullOrWhiteSpace(item.ParaValue4)) { item.ParaValue4 = "/imgs/users/avatar/bav_head.gif"; } } #endregion }
protected void GetEChartData1(Guid userId) { Response.ContentType = "application/json"; decimal dTotalInAmount = WEBRequest.GetQueryString("TotalInAmount").ToDecimal(0); decimal dTotalPayAmount = WEBRequest.GetQueryString("TotalPayAmount").ToDecimal(0); try { var param = new Dapper.DynamicParameters(); param.Add("@userId", userId); param.Add("@startDate", DateTime.Today.AddDays(-7)); param.Add("@endDate", DateTime.Today.AddDays(1).AddSeconds(-1)); //这里要加上复投宝的数据 string sql = @" select a.id,a.value as CalcDay, isnull(b.InterestAmout_2,0)+isnull(O.InterestAmout_3,0)+isnull(ftb.InterestAmout_4,0) as TotalIn, isnull(m.InterestAmout_2,0)+isnull(pb.todayCommission,0) as TotalOut from dbo.SplitToTable('{0}',',') a left join ( SELECT convert(varchar(10),ISNULL(a.RepayAdvanceDate,A.CycDate),23) CalcDay, ISNULL(SUM(ISNULL(a.RealInterestAmout,0)),0)+ISNULL(SUM(ISNULL(a.TuandaiRedPacket,0)),0)+ISNULL(SUM(ISNULL(a.PublisherRedPacket,0)),0) AS InterestAmout_2 --已赚利息 FROM SubscribeDetailHistory_h1 A WITH(NOLOCK) INNER JOIN dbo.Subscribe s WITH(NOLOCK) ON a.SubscribeId = s.Id LEFT JOIN dbo.We_Order wo WITH(NOLOCK) ON s.WeOrderId = wo.Id WHERE ( s.WeOrderId IS NULL OR (ISNULL(wo.IsWeFQB,0)=1 AND ISNULL(wo.RepeatInvestType,0) =2) OR (ISNULL(wo.IsWeFQB,0)=0 and ISNULL(wo.IsFTB,0)=0)) 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 AND ISNULL(a.RepayAdvanceDate,A.CycDate)>=@startDate AND ISNULL(a.RepayAdvanceDate,A.CycDate)<=@endDate group by convert(varchar(10),ISNULL(a.RepayAdvanceDate,A.CycDate),23) ) b on b.CalcDay=cast(a.value as datetime) LEFT JOIN (select convert(varchar(10),B.AddDate,23) CalcDay, isnull(sum(B.Interest),0) InterestAmout_3 --逾期利息 from OverDueRecord B WITH(NOLOCK) INNER JOIN dbo.Subscribe s ON b.SubscribeId = s.Id LEFT JOIN we_order wo ON s.WeOrderId = wo.Id WHERE ( s.WeOrderId IS NULL OR (ISNULL(wo.IsWeFQB,0)=1 AND ISNULL(wo.RepeatInvestType,0) =2) OR (ISNULL(wo.IsWeFQB,0)=0 and ISNULL(wo.IsFTB,0)=0)) and B.SubscribeUserId=@userId and B.IsBorrow=0 AND B.AddDate>=@startDate and B.AddDate<=@endDate GROUP by convert(varchar(10),B.AddDate,23) ) O ON O.CalcDay=cast(a.value as datetime) left join ( select isnull(sum(BorrowPayoutCommission),0) as todayCommission,convert(varchar(10),AddDate,23)as CalcDay from dbo.Project with(nolock) where UserId=@UserId and AddDate >=@startDate and Status <>6 group by convert(varchar(10),AddDate,23) )pb on pb.CalcDay=cast(a.value as datetime) left join ( select convert(varchar(10),ISNULL(A.RepayAdvanceDate,A.CycDate),23) CalcDay ,sum(A.RealInterestAmout-isnull(B.Interest,0)) InterestAmout_2 --已赚利息 from dbo.SubscribeDetailHistory_h1 A left join OverDueRecord B on A.SubscribeId=B.SubscribeId and B.IsBorrow=0 and A.Periods =B.periods inner join Project C on A.ProjectId=C.Id and C.UserId=@userId left join dbo.SubscribeExtend ex on ex.SubscribeID=A.SubscribeId where isnull(ex.IsFTB,0)!=1 and ISNULL(A.RepayAdvanceDate,A.CycDate)>=@startDate and ISNULL(A.RepayAdvanceDate,A.CycDate)<=@endDate group by convert(varchar(10),ISNULL(A.RepayAdvanceDate,A.CycDate),23) ) m on m.CalcDay=cast(a.value as datetime) left join( --复投宝已收 select convert(varchar(10),CycDate,23) CalcDay, ISNULL(SUM(ISNULL(InterestAmount,0)),0)+ISNULL(SUM(ISNULL(TuandaiRedPacket,0)),0)+ISNULL(SUM(ISNULL(PublisherRedPacket,0)),0) InterestAmout_4 --申购利息汇总 FROM We_OrderDetail WHERE CycDate>=@startDate and CycDate<=@endDate and UserId=@UserID and IsHandle=1 GROUP by convert(varchar(10),CycDate,23) ) FTB on FTB.CalcDay=cast(a.value as datetime) order by a.value"; string dayStr = ""; for (int i = 7; i >= 0; i--) { dayStr += DateTime.Today.AddDays(-1 * i).ToString("yyyy.MM.dd") + ","; } dayStr = dayStr.Substring(0, dayStr.Length - 1); sql = string.Format(sql, dayStr); chartDatas1 = PublicConn.QueryBySql <EChartData1Info>(sql, ref param); //从后往前倒推数据 if (chartDatas1 != null && chartDatas1.Any()) { decimal calcInAmount = chartDatas1.Sum(p => p.TotalIn); decimal calcOutAmount = chartDatas1.Sum(p => p.TotalOut); if (calcInAmount < dTotalInAmount) { decimal firstInCalc = dTotalInAmount - calcInAmount + chartDatas1[0].TotalIn;//减去总和再加第一天的收益 for (int i = 0; i < chartDatas1.Count; i++) { if (i == 0) { chartDatas1[i].TotalIn = firstInCalc; } else if (i == 7) { chartDatas1[i].TotalIn = dTotalInAmount; } else { chartDatas1[i].TotalIn += chartDatas1[i - 1].TotalIn; } } } if (calcOutAmount < dTotalPayAmount) { decimal firstOutCalc = dTotalPayAmount - calcOutAmount + chartDatas1[0].TotalOut; //减去总和再加第一天的支出 for (int i = 0; i < chartDatas1.Count; i++) { if (i == 0) { chartDatas1[i].TotalOut = firstOutCalc; } else if (i == 7) { chartDatas1[i].TotalOut = dTotalPayAmount; } else { chartDatas1[i].TotalOut += chartDatas1[i - 1].TotalOut; } } } } string jsonStr = ToJson(chartDatas1); PrintJson("1", jsonStr); } catch (Exception ex) { PrintJson("0", "获取数据失败"); } finally { Response.End(); } }
protected bool GetData() { UserBLL userbll = new UserBLL(); //获取项目信息 model = bll.GetProjectDetailInfo(projectId.Value); if (model == null) { Response.Redirect(GlobalUtils.WebURL + "/Member/my_account.aspx"); return(false); } userApply = bll.WXGetFQUserApplyInfo(projectId.Value.ToText()); if (userApply != null) { Organization = bll.WXGetOrganizationInfo(userApply.OrgId.HasValue ? userApply.OrgId.Value.ToText() : ""); } else { userApply = new WXFQUserApplyInfo(); } string strFile = string.Format(" oa.OrgTypeId ={0} and (f.UserId IS NULL OR f.UserId = '{1}') ", Organization.OrgTypeId, userApply.ApplyUserId); Dapper.DynamicParameters dyParams = new Dapper.DynamicParameters(); string strSQL = string.Format(@" SELECT DISTINCT AuthName = a.Name, oa.AuthId, a.UploadedStyle,oa.SortOrder FROM fq_OrgAuthSetting oa with(nolock) INNER JOIN fq_AuthSetting a with(nolock) ON oa.AuthId = a.Id INNER JOIN (SELECT AuthId,UserId from dbo.fq_UserAuthFile with(nolock) WHERE (IsDelete IS NULL OR IsDelete = 0) AND IsHiddenInfo =0) f ON a.Id = f.AuthId Where {0} Order by oa.SortOrder asc", strFile); var AuthList = PublicConn.QueryBySql <AuthSet>(strSQL, ref dyParams); for (int i = 0; i < AuthList.Count; i++) { if (i == 0) { AuthHtml = AuthList[i].AuthName; } else { AuthHtml += "、" + AuthList[i].AuthName; } } //是否学生分期 dyParams = new Dapper.DynamicParameters(); dyParams.Add("@ProjectId", model.Id); strSQL = "select isnull(IdentityTypeId,0) as IdentityTypeId from fq_UserApply with(nolock) where ProjectId=@ProjectId"; IdentityTypeId = PublicConn.QuerySingle <int>(strSQL, ref dyParams); if (IdentityTypeId == 1) { //获取学生基本信息 dyParams = new Dapper.DynamicParameters(); dyParams.Add("@UserId", model.UserId); strSQL = "select * from fq_ApplyEducation with(nolock) where UserId=@UserId"; applyEducation = PublicConn.QuerySingle <ApplyEducation>(strSQL, ref dyParams); } else { borrowUserInfo = userbll.GetUserBasicInfoModelById(model.UserId.Value); strSQL = "select * from userDetailInfo with(nolock) where UserId=@UserId"; dyParams = new Dapper.DynamicParameters(); dyParams.Add("@UserId", borrowUserInfo.Id); userDetai = PublicConn.QuerySingle <userDetailInfoInfo>(strSQL, ref dyParams); } //信用档案 creditInfo = CommUtils.GetBorrowerCreditData(model.UserId.Value); //项目展示图 imageList = CommUtils.GetProjectImages(this.projectId.Value); finishProcess = CommUtils.GetProjectProcess(model); SubscribeUserCount = CommUtils.GetSubscribeUserCount(this.projectId.Value); //计算预期收益 PreInterestRate = CommUtils.CalcInvestInterest(model, 10000); EbaoMultiple = int.Parse(Math.Ceiling(model.InterestRate.Value / decimal.Parse("2.5")).ToString()); EbaoInterest = CommUtils.GetEbaoMultipleInterest(model, 10000); preSubscribeList = CommUtils.GetPreSubscribeDetail(model, 10000); return(true); }
/// <summary> /// 获取回款记录明细 /// </summary> /// <param name="subscribeId">申购Id</param> /// <returns></returns> protected List <SubscribeInfo> GetSubscribeDetailList(Guid subscribeId, Guid ProjectId) { List <SubscribeInfo> list = new List <SubscribeInfo>(); if (GlobalUtils.IsOpenSubscribeApi) { string postUrl = GlobalUtils.SubApiUrl; string err = ""; string response = HttpClient.HttpUtil.HttpGet(TdConfig.ApplicationName, postUrl + "/wap/getSubscribeDetailList?subscribeId=" + subscribeId + "&pid=" + ProjectId, "", out err, null, 3); if (!string.IsNullOrEmpty(response)) { ResponsePublicModel <List <SubscribeInfo> > responsePublicModel = JsonConvert.DeserializeObject <ResponsePublicModel <List <SubscribeInfo> > >(response); if (responsePublicModel != null && responsePublicModel.data != null) { list = responsePublicModel.data; } } else { list = new List <SubscribeInfo>(); TuanDai.LogSystem.LogClient.LogClients.ErrorLog(TdConfig.ApplicationName, "/wap/getSubscribeDetailList", "", err); } } else { string sqlText = @"SELECT ISNULL(Amount,0) Amount,ISNULL(InterestAmout,0) InterestAmout,ISNULL(InvestCommission,0) Commssion, ISNULL(TuandaiRedPacket,0) TuandaiRedPacket,Periods,CycDate,[Desc],PenaltyAmount,IsBorrow,AddDate as OverDueDate,isnull(PublisherRedPacket,0) as PublisherRedPacket,InvestDate,OverDueInterest FROM ( SELECT a.Amount,a.InterestAmout,a.InvestCommission,a.BorrorCommission,a.TuandaiRedPacket,a.Periods,a.CycDate,'待回款' as [Desc], CASE c.IsVip WHEN 0 THEN 0 WHEN 1 THEN (b.PrePenalty/2+(b.PenaltyAmount-b.PrePenalty)*2/3) ELSE 0 END AS PenaltyAmount,isnull(b.IsBorrow,1) as IsBorrow,b.AddDate, a.PublisherRedPacket, c.AddDate as InvestDate,0 as OverDueInterest FROM SubscribeDetail a with(nolock) LEFT JOIN OverDueRecord b with(nolock) ON a.SubscribeId=b.SubscribeId AND a.Periods=b.periods and isnull(b.IsHide,0)=0 INNER JOIN Subscribe c with(nolock) ON a.SubscribeId=c.Id WHERE a.SubscribeId=@SubscribeId )t ORDER BY Periods ASC"; var para = new Dapper.DynamicParameters(); para.Add("@SubscribeId", subscribeId); list = PublicConn.QueryBySql <SubscribeInfo>(sqlText, ref para); //回款历史表 sqlText = @"select a.RealAmount Amount,a.RealInterestAmout InterestAmout,a.InvestCommission Commssion,a.BorrorCommission, a.TuandaiRedPacket,a.Periods,ISNULL(a.RepayAdvanceDate,a.CycDate) CycDate,'已回款' as [Desc], 0 AS PenaltyAmount,1 as IsBorrow,NULL as OverDueDate, a.PublisherRedPacket, NULL as InvestDate,0 OverDueInterest from SubscribeDetailHistory_h1 a with(nolock) where SubscribeId=@SubscribeID order by Periods"; var hlist = TuanDai.DB.TuanDaiDB.Query <SubscribeInfo>(TdConfig.ApplicationName, TdConfig.DBSubDetailHisRead, sqlText, ref para); if (hlist != null && hlist.Count > 0) { foreach (var item in hlist) { //历史表有逾期的数据(已回款) sqlText = string.Empty; sqlText = @"select isnull(b.IsBorrow,1) IsBorrow,isnull(b.PrePenalty,0) PrePenalty,isnull(b.PenaltyAmount,0) PenaltyAmount,b.AddDate OverDueDate,c.IsVip,c.AddDate InvestDate ,0 as OverDueInterest from OverDueRecord b with(nolock) inner join Subscribe c with(nolock) on c.id=b.SubscribeId where b.SubscribeId=@SubscribeID and b.periods=@periods and ISNULL(b.IsBorrow,1) =1 and isnull(b.IsHide,0)=0"; para.Add("@periods", item.Periods); OverInfo info = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <OverInfo>(TdConfig.ApplicationName, TdConfig.DBRead, sqlText, ref para); if (info != null) { item.Desc = "已回款"; item.OverDueDate = info.OverDueDate; item.InvestDate = info.InvestDate; item.IsBorrow = true; item.OverDueInterest = info.OverDueInterest; if (info.IsVip) { item.PenaltyAmount = info.PrePenalty / 2 + (info.PenaltyAmount - info.PrePenalty) * 2 / 3; } else { item.PenaltyAmount = 0; } } //历史表有逾期的数据(已逾期) sqlText = string.Empty; sqlText = @"select isnull(b.IsBorrow,1) IsBorrow,isnull(b.PrePenalty,0) PrePenalty,isnull(b.PenaltyAmount,0) PenaltyAmount,b.AddDate OverDueDate,c.IsVip,c.AddDate InvestDate,isnull(b.OverDueInterest,0) OverDueInterest from OverDueRecord b with(nolock) inner join Subscribe c with(nolock) on c.id=b.SubscribeId where b.SubscribeId=@SubscribeID and b.periods=@periods and ISNULL(b.IsBorrow,1) =0 and isnull(b.IsHide,0)=0"; para.Add("@periods", item.Periods); OverInfo info1 = TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <OverInfo>(TdConfig.ApplicationName, TdConfig.DBRead, sqlText, ref para); if (info1 != null) { item.Desc = "已逾期"; item.OverDueDate = info1.OverDueDate; item.InvestDate = info1.InvestDate; item.IsBorrow = false; item.OverDueInterest = info1.OverDueInterest; if (info1.IsVip) { item.PenaltyAmount = info1.PrePenalty / 2 + (info1.PenaltyAmount - info1.PrePenalty) * 2 / 3; } else { item.PenaltyAmount = 0; } } } } if (list == null) { list = new List <SubscribeInfo>(); } if (hlist != null) { list.AddRange(hlist); } } return(list); }