protected string GetNextReturnMoneyDay(Guid userId)
{
string sql = @"SELECT TOP 1 Convert (VARCHAR(10),CycDate,120) AS CycDate,
SUM(ISNULL(Amount,0)+ISNULL(InterestAmout,0)+ISNULL(TuandaiRedPacket,0) +ISNULL(PublisherRedPacket,0) ) AS TotalAmount FROM (
SELECT sd.CycDate,
CASE WHEN sd.InvestType IN(3,2) THEN 0 ELSE sd.Amount END AS Amount,
CASE WHEN sd.InvestType = 3 THEN 0 ELSE sd.InterestAmout END AS InterestAmout,
CASE WHEN sd.InvestType = 3 THEN 0 ELSE ISNULL(sd.TuandaiRedPacket,0) END AS TuandaiRedPacket,
CASE WHEN sd.InvestType = 3 THEN 0 ELSE ISNULL(sd.PublisherRedPacket,0) END AS PublisherRedPacket
FROM dbo.SubscribeDetail sd with(nolock)
WHERE isnull(sd.InvestType,0)!=1 and sd.SubscribeUserId=@UserId and sd.CycDate>getdate()
union all
select we.CycDate, we.Amount, we.InterestAmount,we.TuanDaiRedPacket, we.PublisherRedPacket from dbo.We_OrderDetail we
where we.IsHandle=0 and we.UserId=@UserId and we.CycDate>getdate() )a
GROUP BY Convert (VARCHAR(10),CycDate,120)
having sum(isnull(Amount, 0) + isnull(InterestAmout, 0)+ isnull(TuandaiRedPacket, 0) + isnull(PublisherRedPacket, 0))>0
ORDER BY CycDate ASC";
var para = new Dapper.DynamicParameters();
para.Add("@UserId", userId);
var item = PublicConn.QuerySingle <NextReturnMoneyDay>(sql, ref para);
if (item != null)
{
return("<span>" + item.CycDate.ToString("yyyy-MM-dd") + "<b>" + ToolStatus.ConvertLowerMoney(item.TotalAmount) + "</b>元</span>");
}
else
{
return("<span>无回款日</span>");
}
}
protected bool GetData()
{
model = bll.GetSubScriberansfer(projectId);
//获取项目信息
DynamicParameters dyParams = new DynamicParameters();
dyParams.Add("@id", projectId);
string strSQL = "select top 1 SubscribeUserId from Subscribe with(nolock) where Id=(select m_FromSubscribeId from t_SubScribeTransfer with(nolock) where m_id=@id)";
var SubscribeUserId = PublicConn.QuerySingle <Guid?>(strSQL, ref dyParams);
dyParams = new DynamicParameters();
dyParams.Add("@userid", SubscribeUserId);
strSQL = "select * from UserBasicInfo with(nolock) where Id=@userid";
borrowUserInfo = PublicConn.QuerySingle <UserBasicInfoInfo>(strSQL, ref dyParams);
//申购笔数
strSQL = @"select COUNT(0)
from dbo.Subscribe S with(nolock)
inner join UserBasicInfo C with(nolock) on S.SubscribeUserId=C.Id
inner join t_SubScribeTransfer as D with(nolock)
on D.m_id=S.TranId
where s.tranid=@ProjectId" ;
dyParams = new DynamicParameters();
dyParams.Add("@ProjectId", projectId);
SubscribeUserCount = PublicConn.QuerySingle <int>(strSQL, ref dyParams);
return(true);
}
/// <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;
}
protected void Page_Load(object sender, EventArgs e)
{
userId = WebUserAuth.UserId.Value;
if (!IsPostBack)
{
UserBLL userBll = new UserBLL();
userModel = userBll.GetUserBasicInfoModelById(userId);
DrawType = WEBRequest.GetQueryInt("drawtype", 2);
if (DrawType != 1 && DrawType != 2)
{
DrawType = 2;
}
string strSQL = "select Id, ActualWithdrawDeposit, merchanType from AppWithdrewFund where UserId=@userId order by AppDate desc";
DynamicParameters dyParams = new DynamicParameters();
dyParams.Add("@userId", userId);
WXAppWithdrewFund appWith = PublicConn.QuerySingleWrite <WXAppWithdrewFund>(strSQL, ref dyParams);
if (appWith != null)
{
Amout = appWith.ActualWithdrawDeposit ?? 0;
if (DrawType == 1)
{
strSQL = " update AppWithdrewFund set merchanType=9 where Id=@id";
dyParams = new DynamicParameters();
dyParams.Add("@id", appWith.Id);
PublicConn.ExecuteTD(PublicConn.DBWriteType.FundWrite, strSQL, ref dyParams);
}
}
}
}
/// <summary>
/// 获取显示Image
/// </summary>
private void GetShowImage()
{
string sql = @"SELECT TOP 1 ImageUrl,Link,Title FROM AdImage(nolock) where Type=42 AND IsEnable=1 ORDER BY AddDate DESC ";
var para = new Dapper.DynamicParameters();
showImage = PublicConn.QuerySingle <ShowImage>(sql, ref para);
}
//获取资产统计
private void GetAccountInfo(Guid userid)
{
//获取We待投
weWaitInvestment = GetWePlanWaitInvestment(userid);
Tuple <decimal, decimal> ftbDueIn = GetFTBDueInAmountInterest(userid);
var commandText = @"SELECT * from dbo.FundAccountInfo with(nolock) WHERE UserId =@UserId";;
DynamicParameters para = new DynamicParameters();
para.Add("@UserId", userid);
fundAccountInfo = PublicConn.QuerySingle <FundAccountInfoInfo>(commandText, ref para);
//待收利息加上复投宝的利息
fundAccountInfo.DueInPAndI = (fundAccountInfo.DueInPAndI ?? 0) + ftbDueIn.Item1 + ftbDueIn.Item2;
fundAccountInfo.DueComeInterest = (fundAccountInfo.DueComeInterest ?? 0) + ftbDueIn.Item2;
fundAccountInfo.FreezeAcount = (fundAccountInfo.FreezeAcount ?? 0) + weWaitInvestment; //冻结资金加上We待投
fundAccountInfo.TotalInvest += GetWeFtbTotalInvest(userid);
TotalRecharge = fundAccountInfo.TotalRecharge ?? 0;
totalamount = (fundAccountInfo.AviMoney ?? 0) + (fundAccountInfo.DueInPAndI ?? 0)
+ (fundAccountInfo.BorrowerOut ?? 0) + (fundAccountInfo.DueConfirmWithdrawDeposit ?? 0) + (fundAccountInfo.RewardMoney ?? 0) + (fundAccountInfo.FreezeAcount ?? 0);
}
private SuperProjectDetailInfo QueryDataBySql(string querySql, DynamicParameters dynamicParameters)
{
SuperProjectDetailInfo list = null;
list = PublicConn.QuerySingle <SuperProjectDetailInfo>(querySql, ref dynamicParameters);
return(list);
}
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);
}
this.SubscribeUserCount = TuanDai.WXApiWeb.Common.WXInvest.WXGetSubscribeUserCount(this.projectId.Value);
#region 获取担保公司
UserEnterpriseInfo userEnterprise = GetBorrowerGuaranteeEnterprise(this.model.UserId.Value, model.AddDate.Value);
if (userEnterprise != null)
{
string sql = "select NickName from UserBasicInfo with(nolock) where id=@enterpriseuserid";
DynamicParameters args = new DynamicParameters();
args.Add("@enterpriseuserid", userEnterprise.UserId);
this.EnterpriseName = PublicConn.QuerySingle <string>(sql, ref args);
}
else
{
if (model.Guarantors != null && model.Guarantors != "")
{
this.EnterpriseName = BusinessDll.business.GetAssureNameById(int.Parse(model.Guarantors.ToString()));
}
}
#endregion
return(true);
}
//查询赎回费用
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);
}
protected void Page_Load(object sender, EventArgs e)
{
string cookieUrl = Tool.CookieHelper.GetCookie("InvestUrl").ToText();
if (cookieUrl.Trim().IsNotEmpty())
{
GoReturnUrl = cookieUrl;
Tool.CookieHelper.ClearCookie("InvestUrl");
}
if (TuanDai.WXApiWeb.WebUserAuth.IsAuthenticated)
{
Guid userid = TuanDai.WXApiWeb.WebUserAuth.UserId.Value;
List <int> typeList = new List <int>()
{
11
};
string strSQL = "select count(1) from AccountRechare where UserId=@userId and Status=2 and type=11";
DynamicParameters dyParams = new DynamicParameters();
dyParams.Add("@userId", userid);
int iCount = PublicConn.QuerySingle <int>(strSQL, ref dyParams);
if (iCount > 0)
{
TuanDai.PortalSystem.BLL.VipGetWorthBLL.AddGetWorth(userid, (int)ConstString.UserGrowthType.FirstReCharge, null, 0);
}
}
}
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;
}
}
private void GetContractInfo(string key)
{
string contactno = this.key;
string strSQL = @"SELECT 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,c.TypeWord
FROM dbo.We_Product a WITH(NOLOCK)
left JOIN dbo.We_Order b WITH(NOLOCK) on b.ProductId=a.Id
INNER JOIN dbo.We_ProductType c WITH(NOLOCK) ON a.ProductTypeId = c.Id
WHERE b.Id=@WeOrderId";
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@WeOrderId", contactno);
model = PublicConn.QuerySingle <MyWeOrderInfo>(strSQL, ref dyParams);
if (model == null)
{
model = new MyWeOrderInfo();
}
this.subBasicModel = userbll.GetUserBasicInfoModelById(model.UserId);
if (model.OrderDate >= DateTime.Parse("2016-03-11"))
{
strCompanyName = "东莞团贷网互联网科技服务有限公司";
Sealt_TuanDai = "images/tuandai_ss.png";
}
//this.subBasicModel.RealName = StringHandler.MaskStartPre(subBasicModel.RealName, 1);//出借人
//this.subBasicModel.IdentityCard = StringHandler.MaskCardNo(subBasicModel.IdentityCard);//出借人身份证号
this.GetRedemptionRate();
}
/// <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();
}
//获取资产统计
private void GetAccountInfo(Guid userid)
{
//获取We待投
weWaitInvestment = GetWePlanWaitInvestment(userid);
GetWe_FundAccountInfo(userid);
var commandText = @"SELECT * from dbo.FundAccountInfo with(nolock) WHERE UserId =@UserId";;
DynamicParameters para = new DynamicParameters();
para.Add("@UserId", userid);
fundAccountInfo = PublicConn.QuerySingle <FundAccountInfoInfo>(commandText, ref para);
//待收利息加上复投宝的利息
fundAccountInfo.DueInPAndI = (fundAccountInfo.DueInPAndI ?? 0) + (we_FundAccountInfo.DueComeInterest ?? 0) + (we_FundAccountInfo.RecoverBorrowOut ?? 0);
fundAccountInfo.DueComeInterest = (fundAccountInfo.DueComeInterest ?? 0) + (we_FundAccountInfo.DueComeInterest ?? 0);
fundAccountInfo.FreezeAcount = (fundAccountInfo.FreezeAcount ?? 0) + weWaitInvestment; //冻结资金加上We待投
fundAccountInfo.TotalInvest += (we_FundAccountInfo.TotalInvest ?? 0);
fundAccountInfo.NetEarningsInterest += (we_FundAccountInfo.NetEarningsInterest ?? 0);
if (fundAccountInfo.TotalPenaltyOfInvest < 0)
{
fundAccountInfo.TotalPenaltyOfInvest = 0;
}
TotalRecharge = fundAccountInfo.TotalRecharge ?? 0;
totalamount = (fundAccountInfo.AviMoney ?? 0) + (fundAccountInfo.DueInPAndI ?? 0)
+ (fundAccountInfo.BorrowerOut ?? 0) + (fundAccountInfo.DueConfirmWithdrawDeposit ?? 0) + (fundAccountInfo.RewardMoney ?? 0) + (fundAccountInfo.FreezeAcount ?? 0);
}
private void GetMaxStatusString()
{
string sql = @"SELECT ISNULL(a.Status,0) AS Status from (SELECT Status FROM dbo.UserAutoTenderSetting WITH(NOLOCK) WHERE UserId=@UserId
UNION
SELECT Status FROM dbo.UserAutoWePlanSetting WITH(NOLOCK) WHERE UserId=@UserId)a
ORDER BY a.Status DESC";
var para = new Dapper.DynamicParameters();
para.Add("@UserId", WebUserAuth.UserId.Value);
var maxStatus = PublicConn.QuerySingle <int?>(sql, ref para);
if (maxStatus == null)
{
MaxStatusString = "未设置";
}
else if (maxStatus == 0)
{
MaxStatusString = "暂停";
}
else
{
MaxStatusString = "开启";
}
}
//获取质量保障服务风险金
protected decimal GetBabyPlanTotalAmount()
{
try
{
if (IsRedis == "1")
{
var redis = mRedis.GetBabyPlanMoney();
BabyPlanReportDate = redis.ReportDate;
return(redis.TotalAmount);
}
else
{
decimal totalAmount = 0;
string sql = @"--DECLARE @ReportDate DATETIME
SELECT TOP 1 @ReportDate=ReportDate FROM dbo.BaoBeiJiHua(NOLOCK) ORDER BY ReportDate DESC
SELECT ISNULL(HostedTotaBalance,0) AS TotalAmount FROM dbo.BaoBeiJiHua(NOLOCK) WHERE ReportDate=@ReportDate and StatusId=1";
var param = new DynamicParameters();
param.Add("@ReportDate", null, DbType.DateTime, ParameterDirection.Output);
totalAmount = PublicConn.QuerySingle <decimal>(sql, ref param);
BabyPlanReportDate = param.Get <DateTime>("ReportDate");
string amount = string.Format("{0:n}", Math.Floor(totalAmount));
amount = amount.Substring(0, amount.IndexOf("."));
return(decimal.Parse(amount));
}
}
catch
{
return(46395598);
}
}
private void GetContractInfo(string key)
{
string contactno = key;
this.submodel = new SubscribeBLL().GetSubscribeInfoContractNo(contactno);
if (submodel.AddDate < Convert.ToDateTime("2016-3-11"))
{
companyName = "广东俊特团贷网络信息服务股份有限公司";
}
else
{
companyName = "东莞团贷网互联网科技服务有限公司";
}
Dapper.DynamicParameters param = new Dapper.DynamicParameters();
param = new Dapper.DynamicParameters();
param.Add("@projectId", submodel.ProjectId);
param.Add("@curUserId", WebUserAuth.UserId);
string strSQL = "select Count(1) from project where Id=@projectId and UserId=@curUserId";
bool isExist = PublicConn.QuerySingle <int>(strSQL, ref param) > 0;
if (this.submodel.SubscribeUserId != WebUserAuth.UserId && !isExist)
{
HttpContext.Current.Response.Write("对不起，您没有权限查看此合同。");
HttpContext.Current.Response.End();
return;
}
}
/// <summary>
/// 获取we计划可用金额
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public decimal GetWePlanWaitInvestment(Guid userId)
{
decimal weWaitInvestment;
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@userId", userId);
string strSQL = @"SELECT SUM(a.OrderAviAmount)
FROM We_Order a with(nolock)
where a.UserId=@userId and a.StatusId in (0,1,2,4) and a.IsRefund=0";
weWaitInvestment = PublicConn.QuerySingle <decimal?>(strSQL, ref dyParams) ?? 0;
//We计划分期宝已回款待投冻结金额
string sql = @"SELECT isnull(SUM(OrderAviAmount),0) FROM(
SELECT ISNULL(B.OrderAviAmount,0) AS OrderAviAmount
FROM dbo.We_Order a WITH(NOLOCK)
left JOIN We_OrderExtFQB b WITH(NOLOCK) on b.OrderId=a.Id AND b.UserId=a.UserId and b.StatusId=0
WHERE a.StatusId in (0,1,2,4) and a.UserId=@userId and isnull(a.IsWeFQB,0)=1
) AS main";
weWaitInvestment += TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <decimal>(TdConfig.ApplicationName, TdConfig.DBRead, sql,
ref dyParams);
return(weWaitInvestment);
}
/// <summary>
/// 我的奖品
/// </summary>
public void GetRecordByUserId()
{
Guid?userId = WebUserAuth.UserId;
if (userId == Guid.Empty)
{
this.PrintJson("2", "对不起，你未登录，请先登录。");
}
var param = new DynamicParameters();
param.Add("@UserId", userId);
RecordInfo recordInfo;
string sqlText = "SELECT UserName,PrizeName FROM dbo.Activity_Guangzhou_Record WITH(NOLOCK) WHERE UserId=@UserId";
recordInfo = PublicConn.QuerySingle <RecordInfo>(sqlText, ref param);
if (recordInfo != null)
{
this.PrintJson("1", recordInfo.PrizeName);
}
else
{
this.PrintJson("0", recordInfo.PrizeName);
}
}
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);
}
}
private void GetContractInfo(string key)
{
string contactno = this.key;
this.submodel = new SubscribeBLL().GetSubscribeInfoContractNo(contactno);
if (submodel.AddDate < Convert.ToDateTime("2016-3-11"))
{
companyName = "广东俊特团贷网络信息服务股份有限公司";
}
else
{
companyName = "东莞团贷网互联网科技服务有限公司";
}
this.subBasicModel = userbll.GetUserBasicInfoModelById(submodel.SubscribeUserId.Value); //基本信息
this.proModel = projectbll.GetProjectDetailInfo(submodel.ProjectId.Value); //标信息
this.publisherModel = userbll.GetUserBasicInfoModelById(proModel.UserId.Value);
var Params = new Dapper.DynamicParameters();
Params.Add("@ProjectId", proModel.Id);
string selSQL = @"select * from fq_ItemSetsProject with(nolock) where ProjectId=@ProjectId";
itemProjectInfo = PublicConn.QuerySingle <Fq_ItemSetsProjectInfo>(selSQL, ref Params);
RepaymentTypeDesc = ToolStatus.ConvertRepaymentType(proModel.RepaymentType.Value);
if (WebUserAuth.UserId == publisherModel.Id)
{
this.subBasicModel.RealName = StringHandler.MaskStartPre(subBasicModel.RealName, 1); //出借人
this.subBasicModel.IdentityCard = StringHandler.MaskCardNo(subBasicModel.IdentityCard); //出借人身份证号
}
else
{
this.itemProjectInfo.RealName = StringHandler.MaskStartPre(itemProjectInfo.RealName, 1); //借款人
this.itemProjectInfo.IdentityCard = StringHandler.MaskCardNo(itemProjectInfo.IdentityCard); //出借人身份证号
}
UserEnterpriseInfo userEnterprise = new Common.Contract().GetBorrowerGuaranteeEnterprise(proModel.UserId.Value, proModel.AddDate.Value, proModel.Type.Value);
if (userEnterprise != null)
{
assureModel = new Common.Contract().GetAssureOrganizationByUserId(userEnterprise.UserId);
}
else
{
int gid = 0;
int.TryParse(proModel.Guarantors, out gid);
assureModel = new Common.Contract().GetAssureOrganizationById(gid);
if (gid == 1 && DateTime.Now < DateTime.Parse("2013-11-27"))
{
assureModel.FullName = "东莞市俊特团贷网络信息服务有限公司";
}
}
//印章小图片
if (assureModel != null)
{
this.assureModel.image = this.GetSmallImage(this.assureModel.image);
}
}
/// <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>
/// 获取用户VIP的等级及管理费优惠利率
/// </summary>
protected void GetUserVipMsg(Guid userid)
{
DynamicParameters param = new DynamicParameters();
param.Add("@userId", userid);
param.Add("@Rate", null, DbType.Double, ParameterDirection.Output);
param.Add("@Level", null, DbType.Int32, ParameterDirection.Output);
PublicConn.ExecuteVip("P_VipProjectZCRate", ref param, CommandType.StoredProcedure);
UserVipLevel = param.Get <int?>("@Level");
if (UserVipLevel == null)
{
UserVipLevel = 1;
}
ManagerRate = param.Get <double?>("@Rate");
if (ManagerRate == null)
{
ManagerRate = 1;
}
if (DateTime.Now < DateTime.Parse("2016/03/12 00:00:00"))
{
ManagerRate = 1;
}
}
protected void GetUserBankInfo()
{
//using (SqlConnection connection = TuanDai.PortalSystem.DAL.PubConstant.CrateReadConnection())
//{
string strSQL = " select BankNo as BankAccountNo, BankType from dbo.UserBankInfo where userid=@userId ";
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@userId", UserId);
bankModel = PublicConn.QuerySingle <BankNoInfo>(strSQL, ref dyParams);
if (bankModel == null)
{
bankModel = new BankNoInfo()
{
};
}
switch (bankModel.BankType)
{
case 3:
BankHotLine = "95588";
BankName = "工商银行";
break;
case 4:
BankHotLine = "95533";
BankName = "建设银行";
break;
case 2:
BankHotLine = "95566";
BankName = "中国银行";
break;
case 5:
BankHotLine = "95599";
BankName = "农业银行";
break;
case 1:
BankHotLine = "95555";
BankName = "招商银行";
break;
case 10:
BankHotLine = "95595";
BankName = "光大银行";
break;
case 7:
BankHotLine = "95528";
BankName = "浦发银行";
break;
case 15:
BankHotLine = "95511";
BankName = "平安银行";
break;
}
//}
}
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);
}
//获取头部广告图
private void GetBannerList()
{
string sql = "SELECT top 8 ImageUrl,Url FROM dbo.MVipBanner with(nolock) WHERE LocationType=1 and isshow = 1 order by DisplaySequence ASC,AddDate DESC";
var para = new Dapper.DynamicParameters();
BannerList = PublicConn.QueryVipBySql <MVipBanner>(sql, ref para);
}
private void GetAccountInfo(Guid userid)
{
var commandText = @"SELECT * from dbo.FundAccountInfo with(nolock) WHERE UserId =@UserId";;
DynamicParameters para = new DynamicParameters();
para.Add("@UserId", userid);
accountInfo = PublicConn.QuerySingle <WXFundAccountInfo_Info>(commandText, ref para);
}
