public Model.stu updatetoselect(string xxid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from stupasswordtable where id='" + xxid + "' ");
Model.stu model = new Model.stu();
DataTable dt = SQLDBhelper.ExecuteDataTable(strSql.ToString());
if (dt.Rows.Count > 0)
{
model.name = dt.Rows[0]["Name"].ToString();
model.sex = dt.Rows[0]["Sex"].ToString();
model.id = dt.Rows[0]["ID"].ToString();
model.password = dt.Rows[0]["password"].ToString();
model.grade = dt.Rows[0]["grade"].ToString();
model.Class = dt.Rows[0]["class"].ToString();
model.academy = dt.Rows[0]["Academy"].ToString();
model.major = dt.Rows[0]["major"].ToString();
model.phone = dt.Rows[0]["phone"].ToString();
model.email = dt.Rows[0]["Email"].ToString();
model.groupID = dt.Rows[0]["groupID"].ToString();
model.introduce = dt.Rows[0]["introduce"].ToString();
return(model);
}
else
{
return(null);
}
}
//预选结果
public DataTable SelectionList(string teaid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select team.Teamnumber,team.topicname,team.captainname,team.captainid,team.memberonename,team.memberoneid,team.membertwoname,team.membertwoid,team.memberthreename,team.memberthreeid,team.topicintroduce from team,ischoose where team.teamnumber =ischoose.stuid and ischoose.teaid='" + teaid + "'");
return(SQLDBhelper.ExecuteDataTable(strSql.ToString()));
}
//未预选界面的“预选”功能
public void Chooseing(string teaid, string xxid)
{
DataTable member = SQLDBhelper.ExecuteDataTable(string.Format("select captainname,captainid,memberonename,memberoneid,membertwoname,membertwoid,memberthreename,memberthreeid from team where teamnumber='{0}'", xxid));
SqlDataReader readteaname = SQLDBhelper.ExecuteReader(string.Format("select name from teapasswordtable where account='{0}'", teaid));
readteaname.Read();
SqlDataReader t = SQLDBhelper.ExecuteReader(string.Format(" select * from will where stuid='{0}'and (firstwill='{1}' or secondwill='{2}' or thirdwill='{3}')", xxid, teaid, teaid, teaid));
if (t.Read())
{
DataTable will1 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where stuid='{0}'and firstwill='{1}'", xxid, teaid));
DataTable will2 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where stuid='{0}'and secondwill='{1}'", xxid, teaid));
DataTable will3 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where stuid='{0}'and thirdwill='{1}'", xxid, teaid));
if (will1.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess1 = '1' where stuid='{0}'and firstwill='{1}'", xxid, teaid));
//插入到ischoose表
SQLDBhelper.ExecuteNonQuery(string.Format("insert into ischoose(stuid,stuname,teaid,teaname) values('{0}','{1}','{2}','{3}')", member.Rows[0]["captainid"].ToString(), member.Rows[0]["captainname"].ToString(), teaid, readteaname[0].ToString()));
}
else if (will2.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess2 = '1' where stuid='{0}'and secondwill='{1}'", xxid, teaid));
SQLDBhelper.ExecuteNonQuery(string.Format("insert into ischoose(stuid,stuname,teaid,teaname) values('{0}','{1}','{2}','{3}')", member.Rows[0]["captainid"].ToString(), member.Rows[0]["captainname"].ToString(), teaid, readteaname[0].ToString()));
}
else if (will3.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess3 = '1' where stuid='{0}'and thirdwill='{1}'", xxid, teaid));
SQLDBhelper.ExecuteNonQuery(string.Format("insert into ischoose(stuid,stuname,teaid,teaname) values('{0}','{1}','{2}','{3}')", member.Rows[0]["captainid"].ToString(), member.Rows[0]["captainname"].ToString(), teaid, readteaname[0].ToString()));
}
}
}
//导师查看团队信息
public Model.message_and_choose GetGroupModel(string xxid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 Teamnumber,topicname,captainname,memberonename,membertwoname,memberthreename,topicintroduce from team ");
strSql.Append(" where Teamnumber=@xxid");
SqlParameter[] parameters =
{
new SqlParameter("@xxid", SqlDbType.Char, 11)
};
parameters[0].Value = xxid;
Model.message_and_choose model = new Model.message_and_choose();
DataTable dt = SQLDBhelper.ExecuteDataTable(strSql.ToString(), CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
model.Teamnumber = dt.Rows[0]["Teamnumber"].ToString();
model.topicname = dt.Rows[0]["topicname"].ToString();
model.captainname = dt.Rows[0]["captainname"].ToString();
model.memberonename = dt.Rows[0]["memberonename"].ToString();
model.membertwoname = dt.Rows[0]["membertwoname"].ToString();
model.memberthreename = dt.Rows[0]["memberthreename"].ToString();
model.topicintroduce = dt.Rows[0]["topicintroduce"].ToString();
return(model);
}
else
{
return(null);
}
}
//在个人信息界面显示导师信息
public Model.message_and_choose GetModel(string id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,name,sex,position,groupnumber,phone,email,academy,research,grade,major from teapasswordtable ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Char, 9)
};
parameters[0].Value = id;
Model.message_and_choose model = new Model.message_and_choose();
DataTable dt = SQLDBhelper.ExecuteDataTable(strSql.ToString(), CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
model.id = dt.Rows[0]["id"].ToString();
model.name = dt.Rows[0]["name"].ToString();
model.sex = dt.Rows[0]["sex"].ToString();
model.position = dt.Rows[0]["position"].ToString();
model.groupnumber = dt.Rows[0]["groupnumber"].ToString();
model.phone = dt.Rows[0]["phone"].ToString();
model.email = dt.Rows[0]["email"].ToString();
model.academy = dt.Rows[0]["academy"].ToString();
model.research = dt.Rows[0]["research"].ToString();
model.grade = dt.Rows[0]["grade"].ToString();
model.major = dt.Rows[0]["major"].ToString();
return(model);
}
else
{
return(null);
}
}
public Model.tea updatetoselect(string xxid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from teapasswordtable where id='" + xxid + "' ");
Model.tea model = new Model.tea();
DataTable dt = SQLDBhelper.ExecuteDataTable(strSql.ToString());
if (dt.Rows.Count > 0)
{
model.name = dt.Rows[0]["Name"].ToString();
model.sex = dt.Rows[0]["Sex"].ToString();
model.id = dt.Rows[0]["ID"].ToString();
model.password = dt.Rows[0]["password"].ToString();
model.position = dt.Rows[0]["position"].ToString();
model.number = dt.Rows[0]["number"].ToString();
model.groupnumber = dt.Rows[0]["groupnumber"].ToString();
model.phone = dt.Rows[0]["phone"].ToString();
model.email = dt.Rows[0]["Email"].ToString();
model.academy = dt.Rows[0]["Academy"].ToString();
model.research = dt.Rows[0]["research"].ToString();
model.grade = dt.Rows[0]["grade"].ToString();
model.major = dt.Rows[0]["major"].ToString();
return(model);
}
else
{
return(null);
}
}
//未预选
public DataTable GetNoChooseList(string teaid, string term1, string term2)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select stuname,stuid,Subjectname from will where ((isaccess1='0' and firstwill='" + teaid + "') or (isaccess2='0' and secondwill='" + teaid + "') or (isaccess3='0' and thirdwill='" + teaid + "')) and stuid in(select account from stupasswordtable where grade='" + term1 + "' and major='" + term2 + "')");
return(SQLDBhelper.ExecuteDataTable(strSql.ToString()));
}
//修改密码
public bool Update(Model.User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update teapasswordtable set ");
strSql.Append("password=@Password");
strSql.Append(" where account=@UserName ");
SqlParameter[] parameters =
{
new SqlParameter("@Password", SqlDbType.VarChar, 30),
new SqlParameter("@UserName", SqlDbType.VarChar, 11)
};
parameters[0].Value = model.Password;
parameters[1].Value = model.UserName;
int rows = SQLDBhelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
if (rows == 1)
{
return(true);
}
else
{
return(false);
}
}
public DataTable select(string com1, string com2)
{
//DataTable dt = new DataTable();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from stupasswordtable where grade='" + com1 + "' and major='" + com2 + "' ");
return(SQLDBhelper.ExecuteDataTable(strSql.ToString()));
}
//时间安排
public Model.message_and_choose time()
{
DataTable time = SQLDBhelper.ExecuteDataTable(string.Format("select * from time"));
Model.message_and_choose model = new Model.message_and_choose();
model.d1 = Convert.ToDateTime(time.Rows[0]["time1"].ToString());
model.d4 = Convert.ToDateTime(time.Rows[0]["time4"].ToString());
return(model);
}
public Model.User_and_time time1()
{
DataTable time = SQLDBhelper.ExecuteDataTable(string.Format("select * from time"));
Model.User_and_time model = new Model.User_and_time();
model.d1 = Convert.ToDateTime(time.Rows[0]["time1"].ToString());
model.d2 = Convert.ToDateTime(time.Rows[0]["time2"].ToString());
model.d3 = Convert.ToDateTime(time.Rows[0]["time3"].ToString());
model.d4 = Convert.ToDateTime(time.Rows[0]["time4"].ToString());
return(model);
}
//分配结果
public DataTable GetSureList(string teaid)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select team.Teamnumber,team.topicname,team.captainname,team.captainid,stupasswordtable.phone,team.memberonename,team.memberoneid,team.membertwoname,team.membertwoid,team.memberthreename,team.memberthreeid,team.topicintroduce from team,stupasswordtable,finish where team.teamnumber =finish.stuid and finish.ischeck='1' and stupasswordtable.id=team.teamnumber");
strSql.Append(" and " + teaid);
SqlParameter[] parameters =
{
new SqlParameter("@teaid", SqlDbType.Char, 9)
};
parameters[0].Value = teaid;
return(SQLDBhelper.ExecuteDataTable(strSql.ToString()));
}
//预选界面的"退选功能"
public void WithdrawalTeam(string id, string teaid)
{
//先删除ischoose表数据
SQLDBhelper.ExecuteNonQuery(string.Format("delete from ischoose where stuid='{0}' and teaid='{1}'", id, teaid));
//再更新will表中的isaccess
DataTable will1 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where isaccess1='1' and stuid='{0}'and firstwill='{1}'", id, teaid));
DataTable will2 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where isaccess2='1' and stuid='{0}'and secondwill='{1}'", id, teaid));
DataTable will3 = SQLDBhelper.ExecuteDataTable(string.Format("select * from will where isaccess3='1' and stuid='{0}'and thirdwill='{1}'", id, teaid));
if (will1.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess1='0' where stuid='{0}' and firstwill='{1}'", id, teaid));
}
else if (will2.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess2='0' where stuid='{0}' and secondwill='{1}'", id, teaid));
}
else if (will3.Rows.Count != 0)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update will set isaccess3='0' where stuid='{0}' and thirdwill='{1}'", id, teaid));
}
}
public bool Login(string userName, string userPassword)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from manager");
strSql.Append(" where account=@UserName and password=@UserPassword");
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NChar, 10),
new SqlParameter("@UserPassword", SqlDbType.NChar, 10),
};
parameters[0].Value = userName;
parameters[1].Value = userPassword;
int n = Convert.ToInt32(SQLDBhelper.ExecuteScalar(strSql.ToString(), CommandType.Text, parameters));
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public void update(string Name, string Sex, string academy, string Major, string Grade, string Class, string Phone, string Email, string introduce, string ID)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update stupasswordtable set name='{0}', sex='{1}', academy='{2}', major='{3}', grade='{4}', class='{5}', phone='{6}' , email='{7}' , introduce='{8}' where id='{9}'", Name, Sex, academy, Major, Grade, Class, Phone, Email, introduce, ID));
}
public void add(string Account, string Password, string Name, string Sex, string ID, string position, int number, int groupnumber, string Phone, string Email, string academy, string research, string Grade, string Major)
{
SQLDBhelper.ExecuteNonQuery(string.Format("insert into teapasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')", Account, Password, Name, Sex, ID, position, number, groupnumber, Phone, Email, academy, research, Grade, Major));
}
public void update(string Name, string Sex, string Grade, string academy, string position, string research, string Phone, string Email, int number, int groupnumber, string Major, string ID)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update teapasswordtable set name='{0}', sex='{1}', grade='{2}',academy='{3}', position='{4}', research='{5}', phone='{6}' , email='{7}' , number='{8}' , groupnumber='{9}', major='{10}' where id='{11}'", Name, Sex, Grade, academy, position, research, Phone, Email, number, groupnumber, Major, ID));
}
//public DataTable select(string com1, string com2)
//{
// DataTable table = SQLDBhelper.ExecuteQuery("select * from stupasswordtable where grade='" + com1 + "' and major='" + com2 + "' ");
// return table;
//}
public void delect(string xxid)
{
SQLDBhelper.ExecuteNonQuery(string.Format("delete from stupasswordtable where id='{0}'", xxid));
}
public void add(string Account, string Password, string Name, string Sex, string ID, string Grade, string Major, string Class, string Phone, string Email, string introduce, string academy, string groupID)
{
SQLDBhelper.ExecuteNonQuery(string.Format("insert into stupasswordtable values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", Account, Password, Name, Sex, ID, Grade, Major, Class, Phone, Email, introduce, academy, groupID));
}
public void time2(string d1, string d2, string d3, string d4)
{
SQLDBhelper.ExecuteNonQuery(string.Format("update time set time1='{0}',time2='{1}',time3='{2}',time4='{3}'", d1, d2, d3, d4));
}