/// <summary>
/// 查找并分页
/// </summary>
/// <param name="key"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public ModelByCount <SystemHelp> GetHelps(string key, int pageIndex, int pageSize)
{
ModelByCount <SystemHelp> result = new ModelByCount <SystemHelp>();
SqlCommand cmd = db.GetStoredProcedureCommand("Proc_GetHelpsByKeyPage");
db.AddInputParameter(cmd, "@Key", DbType.String, "%" + key + "%");
db.AddInputParameter(cmd, "@PageIndex", DbType.Int32, pageIndex);
db.AddInputParameter(cmd, "@PageSize", DbType.Int32, pageSize);
db.AddReturnValueParameter(cmd, "@ReturnValue", DbType.Int32, 4);
DataTable dt = db.ExecuteDataTable(cmd);
result.ListAll = dt.CreateDataReader().ReaderToList <SystemHelp>();
result.PageIndex = pageIndex;
result.PageSize = pageSize;
result.AllCount = db.GetParameter(cmd, "@ReturnValue").Value.ToInt();
return(result);
}
/// <summary>
/// 进行中的债权
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public DataTableCollection GetInvestment_Ing(int userID)
{
SqlCommand cmd = db.GetSqlStringCommand
(
@"SELECT
(SELECT COUNT(ClaimsID) FROM SystemClaimsDetails WHERE InvestorsID = @UserId AND PayStatus = 2 AND PrincipalClearState = 1 GROUP BY ClaimsID),
(SELECT SUM(InvestorMoney) FROM SystemClaimsDetails WHERE InvestorsID = @UserId AND PayStatus = 2 AND PrincipalClearState = 1),
(SELECT SUM(CAST(D.InvestorMoney/C.SingleAmount AS INT)) FROM SystemClaimsDetails AS D INNER JOIN SystemClaims AS C ON D.ClaimsID = C.ID WHERE D.InvestorsID = @UserId AND D.PayStatus = 2 AND D.PrincipalClearState = 1)
SELECT CONVERT(VARCHAR(10),CAST(D.InvestorsTime AS DATETIME),120),C.Title,CAST(D.InvestorMoney/C.SingleAmount AS INT),D.InvestorMoney,'还款中'
FROM SystemClaimsDetails AS D INNER JOIN SystemClaims AS C ON D.ClaimsID = C.ID
WHERE D.InvestorsID = @UserId AND D.PayStatus = 2 AND D.PrincipalClearState = 1"
);
db.AddInputParameter(cmd, "@UserId", DbType.Int32, userID);
return(db.ExecuteDataSet(cmd).Tables);
}
/// <summary>
/// 2.7. 转账 （用于投资）
/// </summary>
/// <param name="uid"></param>
/// <param name="PayNo"></param>
/// <returns></returns>
public bool ToCpTransaction(string PayNo, out string errorMsg)
{
SqlCommand cmd = db.GetStoredProcedureCommand("Proc_Transaction_PayCompleted");
db.AddInputParameter(cmd, "@PayNo", System.Data.DbType.String, PayNo);
db.AddOutputParameter(cmd, "@ErrorMsg", System.Data.DbType.String, 4);
db.AddReturnValueParameter(cmd, "@ReturnValue", System.Data.DbType.Int32, 4);
db.ExecuteNonQuery(cmd);
errorMsg = db.GetParameter(cmd, "@ErrorMsg").Value.ToString();
return(db.GetParameter(cmd, "@ReturnValue").Value.ToInt() == 1);
}
/// <summary>
/// 添加债券明细
/// </summary>
/// <param name="userID">用户编号</param>
/// <param name="claimsID">债券ID</param>
/// <param name="buyCount">购买数量</param>
/// <param name="dayEarnings">日结利息</param>
/// <param name="expireEarnings">预期收益</param>
/// <param name="IsUserBounty">是否使用虚拟本金</param>
/// <param name="virtualMoney">虚拟本金数量</param>
/// <param name="virtualMoneyDayEarnings">虚拟本金日结利息</param>
/// <param name="errorMsg">错误信息</param>
/// <param name="targetUserID">扣钱的用户ID</param>
/// <returns></returns>
public int SystemClaimsDetails_Add(int userID, int claimsID, int buyCount, decimal dayEarnings, decimal expireEarnings, int IsUserBounty, decimal virtualMoney, decimal virtualMoneyDayEarnings, out string errorMsg, out int targetUserID)
{
errorMsg = "";
SqlCommand cmd = db.GetStoredProcedureCommand("Proc_SystemClaimsDetails_Add");
db.AddInputParameter(cmd, "@UserID", DbType.Int32, userID);
db.AddInputParameter(cmd, "@ClaimsID", DbType.Int32, claimsID);
db.AddInputParameter(cmd, "@BuyCount", DbType.Int32, buyCount);
db.AddInputParameter(cmd, "@DayEarnings", DbType.Decimal, dayEarnings);
db.AddInputParameter(cmd, "@ExpireEarnings", DbType.Decimal, expireEarnings);
db.AddInputParameter(cmd, "@IsUserBounty", DbType.Int32, IsUserBounty);
db.AddInputParameter(cmd, "@VirtualMoney", DbType.Decimal, virtualMoney);
db.AddInputParameter(cmd, "@VirtualMoneyDayEarnings", DbType.Decimal, virtualMoneyDayEarnings);
db.AddOutputParameter(cmd, "@TargetUserID", DbType.Int32, 40);
db.AddOutputParameter(cmd, "@ErrorMsg", DbType.String, 500);
db.AddReturnValueParameter(cmd, "@ReturnValue", DbType.Int32, 40);
db.ExecuteNonQuery(cmd);
errorMsg = db.GetParameter(cmd, "@ErrorMsg").Value.ToString();
targetUserID = db.GetParameter(cmd, "@TargetUserID").Value.ToInt();
return(db.GetParameter(cmd, "@ReturnValue").Value.ToInt());
}
public int SystemRequestRecord_Add(int userID, int bussnessId, decimal requestMoney, int requestType, out string errorMsg)
{
SqlCommand cmd = db.GetStoredProcedureCommand("Proc_SystemRequestRecord_Add");
db.AddInputParameter(cmd, "@UserID", DbType.Int32, userID);
db.AddInputParameter(cmd, "@BussnessId", DbType.Int32, bussnessId);
db.AddInputParameter(cmd, "@RequestMoney", DbType.Decimal, requestMoney);
db.AddInputParameter(cmd, "@RequestType", DbType.Int32, requestType);
db.AddOutputParameter(cmd, "@ErrorMsg", DbType.String, 500);
db.AddReturnValueParameter(cmd, "@ReturnValue", DbType.Int32, 4);
db.ExecuteNonQuery(cmd);
errorMsg = db.GetParameter(cmd, "@ErrorMsg").Value.ToString();
return(db.GetParameter(cmd, "@ReturnValue").Value.ToInt());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public SystemUsers GetUserInfo(int userID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT TOP 1 ID, UserAddress, UserEmail, UserType, UserState, IsEnterprise, Balance, Freeze, RegTime, UserName, UserPassword, PayPassword, CellPhone, CellPahoneIsAuthenticate, RealName, IDNumber, IDNumberIsAuthenticate ");
strSql.Append("FROM SystemUsers ");
strSql.Append("WHERE ID = @ID");
SqlCommand cmd = db.GetSqlStringCommand(strSql.ToString());
db.AddInputParameter(cmd, "@ID", DbType.Int32, userID);
var dt = db.ExecuteDataTable(cmd);
SystemUsers model = null;
if (dt.Rows.Count > 0)
{
model = Mapper.DynamicMap <IDataReader, List <SystemUsers> >(dt.CreateDataReader()).FirstOrDefault();
}
return(model);
}
