/// <summary>
/// 获取当前获得最多进去的6条数据
/// </summary>
/// <returns></returns>
public List <Project> GetModelByAll()
{
string sql = "select top 6 * from Projects p left join [Users] u on p.OwnerId=u.Id order by p.CurrentMoney desc";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
List <Project> list = new List <Project>();
while (dr.Read())
{
project = new Project()
{
Id = int.Parse(dr["Id"].ToString()),
ClassifyId = int.Parse(dr["ClassifyId"].ToString()),
ProjectName = dr["ProjectName"].ToString(),
State = (ProjectState)int.Parse(dr["State"].ToString()),
CurrentMoney = Convert.ToDecimal(dr["CurrentMoney"].ToString()),
Goal = Convert.ToDecimal(dr["Goal"].ToString()),
Deadline = Convert.ToDateTime(dr["Deadline"].ToString()),
LikeCount = int.Parse(dr["LikeCount"].ToString()),
Content = dr["Content"].ToString(),
CoverImg = dr["CoverImg"].ToString(),
PublishState = (PublishState)int.Parse(dr["PublishState"].ToString()),
OwnerId = int.Parse(dr["OwnerId"].ToString()),
Nickname = dr["Nickname"].ToString(),
Address = dr["Address"].ToString()
};
list.Add(project);
}
dr.Close();
return(list);
}
/// <summary>
/// 获取待审批的项目信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Project> GetModelByIdTest(int id)
{
string sql = $"select * from Projects where OwnerId='{id}'and PublishState=0 ";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
List <Project> list = new List <Project>();
while (dr.Read())
{
project = new Project()
{
Id = int.Parse(dr["Id"].ToString()),
ClassifyId = int.Parse(dr["ClassifyId"].ToString()),
ProjectName = dr["ProjectName"].ToString(),
State = (ProjectState)int.Parse(dr["State"].ToString()),
CurrentMoney = Convert.ToDecimal(dr["CurrentMoney"].ToString()),
Goal = Convert.ToDecimal(dr["Goal"].ToString()),
Deadline = Convert.ToDateTime(dr["Deadline"].ToString()),
LikeCount = int.Parse(dr["LikeCount"].ToString()),
Content = dr["Content"].ToString(),
CoverImg = dr["CoverImg"].ToString(),
PublishState = (PublishState)int.Parse(dr["PublishState"].ToString()),
OwnerId = int.Parse(dr["OwnerId"].ToString()),
Address = dr["Address"].ToString()
};
list.Add(project);
}
dr.Close();
return(list);
}
public int SumOfSupportProjects()
{
string sql = "select count(id) as SumOfSupportProjects from SupportProjects";
SqlDataReader dr = DbHelper.GetReader(sql);
int sum = 0;
while (dr.Read())
{
sum = (int)Convert.ToDecimal(dr["SumOfSupportProjects"].ToString());
}
dr.Close();
return(sum);
}
/// <summary>
/// 收到的众筹资总和
/// </summary>
/// <returns></returns>
public int SumOfCurrentMoney()
{
string sql = "select sum(CurrentMoney) as SumOfCurrentMoney from Projects ";
SqlDataReader dr = DbHelper.GetReader(sql);
int sum = 0;
while (dr.Read())
{
sum = (int)Convert.ToDecimal(dr["SumOfCurrentMoney"] == DBNull.Value?"0": dr["SumOfCurrentMoney"].ToString());
}
dr.Close();
return(sum);
}
/// <summary>
/// 项目总数总和
/// </summary>
/// <returns></returns>
public int SumOfProject()
{
string sql = "select COUNT(id) as SumOfPeoject from Projects ";
SqlDataReader dr = DbHelper.GetReader(sql);
int sum = 0;
while (dr.Read())
{
sum = int.Parse(dr["SumOfPeoject"].ToString());
}
dr.Close();
return(sum);
}
/// <summary>
/// 获取项目的支持人数
/// </summary>
/// <param name="PeojectId"></param>
/// <returns></returns>
public int CountSupportWhereProjectId(int PeojectId)
{
string sql = $"select COUNT(*) as [Count] from SupportProjects where ProjectId='{PeojectId}'";
SqlDataReader dr = DbHelper.GetReader(sql);
int num = 0;
while (dr.Read())
{
num = int.Parse(dr["Count"].ToString());
}
dr.Close();
return(num);
}
public int CountSupportPeopleSmall(int ProjectId)
{
string sql = $"select COUNT(*) as SupportPeople from SupportProjects where ProjectId='{ProjectId}' and Money between 300 and 1000";
SqlDataReader dr = DbHelper.GetReader(sql);
int num = 0;
while (dr.Read())
{
num = int.Parse(dr["SupportPeople"].ToString());
}
;
dr.Close();
return(num);
}
public Model.Likes GetModelByUserIdAndProjectId(int UserId,int ProjectId)
{
string sql = $"select * from Likes where UserId='{UserId}' and ProjectId='{ProjectId}'";
SqlDataReader dr = DbHelper.GetReader(sql);
Model.Likes likes = null;
while (dr.Read())
{
likes = new Model.Likes()
{
UserId = int.Parse(dr["UserId"].ToString()),
ProjectId=int.Parse(dr["ProjectId"].ToString())
};
}
dr.Close();
return likes;
}
/// <summary>
/// 当用户修改密码时判读手机是否输入正确
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public User PhoneNumberTest(User user)
{
string sql = $"select * from users where Phone='{user.Phone}' and id='{user.Id}'";
SqlDataReader dr = DbHelper.GetReader(sql);
User UserPhone = null;
while (dr.Read())
{
UserPhone = new User()
{
Id = int.Parse(dr["id"].ToString()),
Phone = dr["Phone"].ToString()
};
}
dr.Close();
return(UserPhone);
}
/// <summary>
/// 当用户发表项目成功后，获得该用户最新发表的项目id用于下一步操作
/// </summary>
/// <param name="UserId"></param>
/// <returns></returns>
public Project GetProjectLaunchId(int UserId)
{
string sql = $"select top 1 id,OwnerId from Projects where OwnerId='{UserId}' order by Id desc";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
while (dr.Read())
{
project = new Project()
{
Id = int.Parse(dr["id"].ToString())
};
}
;
dr.Close();
return(project);
}
public Project GetModelByPublishState(int UserId)
{
string sql = $"select * from Projects where OwnerId='{UserId}' and PublishState=0";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
while (dr.Read())
{
project = new Project()
{
ProjectName = dr["ProjectName"].ToString(),
PublishState = (PublishState)int.Parse(dr["PublishState"].ToString()),
};
}
;
dr.Close();
return(project);
}
public List <Project> RetuenMoney(int Userid)
{
string sql = $"select ProjectName,SUM(Money)as Money from Projects p left join SupportProjects s on p.Id=s.ProjectId where state=3 and Userid='{Userid}' group by ProjectName";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
List <Project> list = new List <Project>();
while (dr.Read())
{
project = new Project()
{
ProjectName = dr["ProjectName"].ToString(),
Money = Convert.ToDecimal(dr["Money"])
};
list.Add(project);
}
dr.Close();
return(list);
}
public void OverDate()
{
string sql = $"select * from Projects";
SqlDataReader dr = DbHelper.GetReader(sql);
Project project = null;
while (dr.Read())
{
project = new Project()
{
Id = int.Parse(dr["id"].ToString()),
Deadline = Convert.ToDateTime(dr["Deadline"]),
};
if (project.Deadline < DateTime.Now)
{
string sql1 = $"UPDATE [dbo].[Projects] set state = 3 where id='{project.Id}'";
DbHelper.Update(sql1);
}
}
;
}
public List <Project> GetProjectUserLaunch(int UserId)
{
string sql = $"select * from LaunchInfo l left join Projects p on l.ProjectId=p.Id where UserId='{UserId}'";
SqlDataReader dr = DbHelper.GetReader(sql);
List <Project> list = new List <Project>();
Project project = null;
while (dr.Read())
{
project = new Project()
{
ProjectName = dr["ProjectName"].ToString(),
Deadline = Convert.ToDateTime(dr["Deadline"]),
Goal = Convert.ToDecimal(dr["Goal"]),
CurrentMoney = Convert.ToDecimal(dr["CurrentMoney"]),
LikeCount = int.Parse(dr["LikeCount"].ToString())
};
list.Add(project);
}
;
dr.Close();
return(list);
}
public List <Comment> GetModelLinkUsers(int UserId, int ProjectId)
{
string sql = $"select * from Comments c left join Users u on c.UserId=u.Id where UserId='{UserId}'and ProjectId='{ProjectId}'";
SqlDataReader dr = DbHelper.GetReader(sql);
List <Comment> list = new List <Comment>();
Comment comment = null;
while (dr.Read())
{
comment = new Comment()
{
Id = int.Parse(dr["Id"].ToString()),
UserId = int.Parse(dr["UserId"].ToString()),
ProjectId = int.Parse(dr["ProjectId"].ToString()),
UserPic = dr["UserPic"].ToString(),
SendTime = dr["SendTime"].ToString(),
Nickname = dr["Nickname"].ToString(),
Content = dr["Content"].ToString()
};
list.Add(comment);
}
dr.Close();
return(list);
}
