public List <NavInfo> GetNavInfo(string GuoJia, int ParentId, string BuWei)
{
List <NavInfo> list = new List <NavInfo>();
DataTable dt = new DataTable();
NavInfo NavModel = null;
try
{
string sql = "select * from NavInfo where GuoJia=@GuoJia and NavParentID=@NavParentID and BuWei=@BuWei order by PaiXu";
MySqlParameter[] para =
{
new MySqlParameter("@GuoJia", GuoJia),
new MySqlParameter("@NavParentID", ParentId),
new MySqlParameter("@BuWei", BuWei)
};
dt = MySqlDB.GetDataTable(sql, System.Data.CommandType.Text, para);
foreach (DataRow dr in dt.Rows)
{
NavModel = new NavInfo();
NavModel.NavID = Convert.ToInt32(dr["NavID"]);//NavID NavTitleOne NavContentOne NavParentID NavTypeID NavIsLevel NavDate NavCreateBy NavHeat NavTitleTwo NavContentTwo GuoJia BuWei PaiXu depth LinkFor
NavModel.NavTitleOne = dr["NavTitleOne"].ToString();
NavModel.NavContentOne = dr["NavContentOne"].ToString();
NavModel.NavParentID = Convert.ToInt32(dr["NavParentID"]);
NavModel.NavTypeID = dr["NavTypeID"].ToString();
if (!string.IsNullOrEmpty(dr["NavIsLevel"].ToString()))
{
NavModel.NavIsLevel = Convert.ToInt32(dr["NavIsLevel"]);
}
if (!string.IsNullOrEmpty(dr["NavDate"].ToString()))
{
NavModel.NavDate = Convert.ToDateTime(dr["NavDate"]);
}
NavModel.NavCreateBy = dr["NavCreateBy"].ToString();
if (!string.IsNullOrEmpty(dr["NavHeat"].ToString()))
{
NavModel.NavHeat = Convert.ToInt32(dr["NavHeat"]);
}
NavModel.NavTitleTwo = dr["NavTitleTwo"].ToString();
NavModel.NavContentTwo = dr["NavContentTwo"].ToString();
NavModel.GuoJia = dr["GuoJia"].ToString();
NavModel.BuWei = dr["BuWei"].ToString();
if (!string.IsNullOrEmpty(dr["PaiXu"].ToString()))
{
NavModel.PaiXu = Convert.ToInt32(dr["PaiXu"]);
}
if (!string.IsNullOrEmpty(dr["depth"].ToString()))
{
NavModel.depth = Convert.ToInt32(dr["depth"]);
}
NavModel.LinkFor = dr["LinkFor"].ToString();
NavModel.KeyWord = dr["KeyWord"].ToString();
list.Add(NavModel);
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
/// <summary>
/// 删除团队信息
/// </summary>
/// <param name="did"></param>
/// <returns></returns>
public bool DelTeamInfo(string ids)
{
try
{
string sql = "select TeamID from arearelation where AreaRelaID in (" + ids + ") ";
//string sql = "select * from team where TeamID in (" + TeamID + "); ";
string teamids = "";
var dt = MySqlDB.GetDataTable(sql, CommandType.Text, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
teamids += dt.Rows[i][0].ToString() + ",";
}
teamids = teamids.TrimEnd(',');
sql = "delete from term_relation where TeamID in (" + teamids + ");";
sql += "delete from team where TeamID in (" + teamids + ");";
sql += "DELETE from arearelation where AreaRelaID in (" + teamids + ");";
int h = MySqlDB.nonquery(sql, CommandType.Text, null);
return(h > 0);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 删除课程
/// </summary>
/// <param name="did"></param>
/// <returns></returns>
public bool DelCourse(string did)
{
try
{
//首先在关系中查出该选中关系的编号
string sql = "select* from languagerelation where LRelationID in (" + did + ") ";
//获取选中关系编号对应的课程编号
string courseids = "";
var dt = MySqlDB.GetDataTable(sql, CommandType.Text, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
courseids += dt.Rows[i][2].ToString() + ",";
}
courseids = courseids.TrimEnd(',');
//删除选中的关系编号
sql = "delete from languagerelation where LRelationID in (" + did + ");";
//删除课程表中相对应的课程
sql += "delete from course where CourseID in (" + courseids + ")";
int h = MySqlDB.nonquery(sql, CommandType.Text, null);
return(h > 0);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 添加学生信息和学生团队关系表
/// </summary>
/// <returns></returns>
public int stuadd(JiaJiModels.student stu)
{
try
{
string sql = "insert into student(StudentName,Score,Image,JiuDuXueyuan,EducationID) VALUES(?studentname,?score,?image,?jiuduxueyuan,?educationid)";
MySqlParameter[] pars =
{
new MySqlParameter("?studentname", stu.StudentName),
new MySqlParameter("?score", stu.Score),
new MySqlParameter("?image", stu.Image),
new MySqlParameter("?jiuduxueyuan", stu.JiuDuXueyuan),
new MySqlParameter("?educationid", stu.EducationID)
};
MySqlDB.nonquery(sql, CommandType.Text, pars);
string stusql = "select StudentID from student ORDER BY StudentID DESC limit 1";
DataTable dt = MySqlDB.GetDataTable(stusql, CommandType.Text, null);
int stid = Convert.ToInt32(dt.Rows[0]["StudentID"]);
string slq = "insert into teamrelation(TeamID,StudentID) VALUES(?teamid,?studentid)";
MySqlParameter[] parss =
{
new MySqlParameter("?teamid", stu.teamid),
new MySqlParameter("?studentid", stid)
};
MySqlDB.nonquery(slq, CommandType.Text, parss);
return(1);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 修改成功案例
/// </summary>
/// <returns></returns>
public int UpdateAnLi(JiaJiModels.Anli model, int teamid)
{
try
{
//查出关系表中的内容
string sql = "select * from successful_relation where SRelationID=" + model.SRelationID + "";
int successid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][1]);
string teamidsql = "select count(1) from team_anli where SuccessID=" + successid + " ";
int countteam = MySqlDB.scalar(teamidsql, CommandType.Text, null);
sql = "update successful set SuccessTitle='" + model.SuccessTitle + "',SuccessContent='" + model.SuccessContent + "',SuccessDate='" + model.SuccessDate + "' where SuccessID=" + successid + "; ";
if (countteam > 0)
{
sql += "update team_anli set team_anli.TeamID=" + teamid + " where SuccessID=" + successid + ";";
}
else
{
sql += "insert into team_anli(TeamID,SuccessID) VALUES(" + teamid + "," + successid + ");";
}
sql += "update successful_relation set StudentID=" + model.StudentID + " where SuccessID=" + successid + " ";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 修改tuandui信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateTeam(JiaJiModels.Team model)
{
try
{
string sql = "select TeamID from arearelation where AreaRelaID=" + model.AreaRelaID + "";
int teamid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0]["TeamID"]);
sql = "update team set `Name`='" + model.Name + "',`Position`='" + model.Position + "',WorkDate='" + model.WorkDate + "',ShenQing='" + model.ShenQing + "',Image1='" + model.Image1 + "',Image2='" + model.Image2 + "',TeamProduce='" + model.TeamProduce + "',SuccessCount='" + model.SuccessCount + "',SuccessContent='" + model.SuccessContent + "',SuccessKeyWord='" + model.SuccessKeyWord + "',SuccessProfile='" + model.SuccessProfile + "' where TeamID=" + teamid + "; ";
sql += "update arearelation set TeamID=" + teamid + ",AreaID=" + model.AreaID + " where arearelation.AreaRelaID=" + model.AreaRelaID + "";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 修改课程
/// </summary>
/// <returns></returns>
public int UpdateCourse(JiaJiModels.languagerelation model)
{
try
{
//首先查出选中关系表中的主键
string sql = "select * from languagerelation where LRelationID=" + model.LRelationID + "";
int courseid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][2]);
sql = "UPDATE course set CourseName='" + model.CourseName + "',CourseContent='" + model.CourseContent + "',CourseDetail='" + model.CourseDetail + "',CourseDate='" + model.CourseDate + "',CourseKeyWord='" + model.CourseKeyWord + "' where course.CourseID=" + courseid + ";";
sql += "update languagerelation set LanguageID=" + model.LanguageID + ",CourseID=" + courseid + " where LRelationID=" + model.LRelationID + " ";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 添加团队信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int addTeam(JiaJiModels.Team model)
{
try
{
string sql = "INSERT INTO study_abroad.team (`Name`, `Position`,WorkDate,ShenQing,Image1,Image2,TeamProduce,SuccessCount,SuccessContent,SuccessKeyWord,SuccessProfile) VALUE ('" + model.Name + "','" + model.Position + "','" + model.WorkDate + "','" + model.ShenQing + "','" + model.Image1 + "','" + model.Image2 + "','" + model.TeamProduce + "','" + model.SuccessCount + "','" + model.SuccessContent + "','" + model.SuccessKeyWord + "','" + model.SuccessProfile + "');select @@IDENTITY;";
int Teamid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][0]);
sql = "insert into arearelation(TeamID, AreaID) values(" + Teamid + "," + model.AreaID + ")";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 添加课程
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int addCourse(JiaJiModels.CourseModel model)
{
try
{
string sql = "insert into course(CourseName,CourseContent,CourseDetail,CourseReadCount,CourseDate,CourseKeyWord) values ('" + model.CourseName + "','" + model.CourseContent + "','" + model.CourseDetail + "',0,'" + model.CourseDate + "','" + model.CourseKeyWord + "');select @@IDENTITY; ";
int courseid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][0]);
sql = "insert into languagerelation(LanguageID, Courseid) values(" + model.LanguageID + "," + courseid + ")";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
public List <string> GetAllCountry()
{
List <string> list = new List <string>();
try
{
string sql = "select GuoJia from navinfo group by GuoJia order by GuoJia ASC";
DataTable dt = MySqlDB.GetDataTable(sql, CommandType.Text, null);
foreach (DataRow dr in dt.Rows)
{
list.Add(dr["GuoJia"].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
return(list);
}
/// <summary>
/// 成功案例添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Anliadd(JiaJiModels.Anli model, int teamid)
{
try
{
string sql = "insert into successful(SuccessTitle,SuccessContent,SuccessDate) VALUES('" + model.SuccessTitle + "', '" + model.SuccessContent + "', '" + model.SuccessDate + "');select @@IDENTITY";
int successid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][0]);
sql = "insert into successful_relation(SuccessID,StudentID) VALUES(" + successid + "," + model.StudentID + ");";
//sql += "insert into team_anli(TeamID,SuccessID) VALUES("+ teamid + "," + successid + ")";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception)
{
return(0);
}
}
public NavInfo GetModelById(int NavID)
{
NavInfo model = new NavInfo();
try
{
string sql = "select * from NavInfo where NavID=@NavID";
MySqlParameter[] para =
{
new MySqlParameter("@NavID", NavID)
};
DataTable dt = MySqlDB.GetDataTable(sql, CommandType.Text, para);
model.NavID = NavID;
model.NavTitleOne = dt.Rows[0]["NavTitleOne"].ToString();
model.NavContentOne = dt.Rows[0]["NavContentOne"].ToString();
model.NavParentID = Convert.ToInt32(dt.Rows[0]["NavParentID"]);
model.NavTypeID = dt.Rows[0]["NavTypeID"].ToString();
model.NavIsLevel = Convert.ToInt32(dt.Rows[0]["NavIsLevel"]);
if (!string.IsNullOrEmpty(dt.Rows[0]["NavDate"].ToString()))
{
model.NavDate = Convert.ToDateTime(dt.Rows[0]["NavDate"]);
}
model.NavCreateBy = dt.Rows[0]["NavCreateBy"].ToString();
if (!string.IsNullOrEmpty(dt.Rows[0]["NavHeat"].ToString()))
{
model.NavHeat = Convert.ToInt32(dt.Rows[0]["NavHeat"]);
}
model.NavTitleTwo = dt.Rows[0]["NavTitleTwo"].ToString();
model.NavContentTwo = dt.Rows[0]["NavContentTwo"].ToString();
model.GuoJia = dt.Rows[0]["GuoJia"].ToString();
model.BuWei = dt.Rows[0]["BuWei"].ToString();
model.PaiXu = Convert.ToInt32(dt.Rows[0]["PaiXu"].ToString());
model.depth = Convert.ToInt32(dt.Rows[0]["depth"]);
model.LinkFor = dt.Rows[0]["LinkFor"].ToString();
model.KeyWord = dt.Rows[0]["KeyWord"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(model);
}
/// <summary>
/// 修改团队标题
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateTitle(JiaJiModels.Team model)
{
try
{
string sql = "select * from term_relation where TermRealID=" + model.TermRealID + "";
int titleid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][2]);
int contentid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][3]);
sql = "update team_content set Content='" + model.Content + "' where team_content.ContentID=" + contentid + ";";
sql += " update team_title set TitleName='" + model.TitleName + "' where TeamTitleID=" + titleid + "; ";
sql += "update term_relation set term_relation.TeamID=" + model.TeamID + ",term_relation.TeamTitleID=" + titleid + ",term_relation.ContentID=" + contentid + " where term_relation.TermRealID=" + model.TermRealID + "";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 添加标题内容
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public int InsertTitle(Team t)
{
try
{
string sql = "insert into team_content(Content) VALUE ('" + t.Content + "');select @@IDENTITY ;";
int teamcontentid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][0]);
sql = "insert into team_title(TitleName) value ('" + t.TitleName + "');select @@IDENTITY ;";
int titleid = Convert.ToInt32(MySqlDB.GetDataTable(sql, CommandType.Text, null).Rows[0][0]);
sql = "insert into term_relation(TeamID, TeamTitleID,ContentID) values(" + t.TeamID + "," + titleid + "," + teamcontentid + ")";
int he = MySqlDB.nonquery(sql, CommandType.Text, null);
return(he);
}
catch (Exception ex)
{
return(0);
}
}
/// <summary>
/// 删除团队标题信息
/// </summary>
/// <param name="did"></param>
/// <returns></returns>
public bool DelTeamTitle(string ids)
{
try
{
string sql = "select * from term_relation where TermRealID in (" + ids + ")";
string contentids = "";
var dt = MySqlDB.GetDataTable(sql, CommandType.Text, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
contentids += dt.Rows[i][0].ToString() + ",";
}
contentids = contentids.TrimEnd(',');
sql = "delete from team_content where ContentID in (" + contentids + ");";
sql += "delete from term_relation where TermRealID in (" + ids + ");";
int h = MySqlDB.nonquery(sql, CommandType.Text, null);
return(h > 0);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 删除成功案例
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public bool DelAnli(string ids)
{
try
{
string sql = "select * from successful_relation where SRelationID in (" + ids + ")";
string successid = "";
var dt = MySqlDB.GetDataTable(sql, CommandType.Text, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
successid += dt.Rows[i][1].ToString() + ",";
}
successid = successid.TrimEnd(',');
sql = "delete from successful where SuccessID in (" + successid + ") ;";
sql += "delete from team_anli where SuccessID in (" + successid + ") ;";
sql += "delete from successful_relation where SRelationID in (" + ids + ") ";
int h = MySqlDB.nonquery(sql, CommandType.Text, null);
return(h > 0);
}
catch (Exception ex)
{
return(false);
}
}