/// <summary>
/// 获取学历类别
/// </summary>
/// <returns></returns>
public List <EducationType> GetEducationType()
{
string sql = "select * from educationtype";
List <EducationType> list = MySqlDB.GetList <EducationType>(sql, System.Data.CommandType.Text, null);
return(list);
}
/// <summary>
/// 通过国家和学历查找规划
/// </summary>
/// <param name="countryid">国家ID</param>
/// <param name="educationid">学历ID</param>
/// <returns></returns>
public List <StudentProgram> GetProgramBy(int countryid, int educationid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT a.TypeID,a.StudentProgramID,a.StudentProgramTitle,a.StudentProgramContent,a.Imageurl,");
sql.Append(" a.ReadCount,StudentProfile,StudentKeyWord from (select studentprogramtype.TypeID, studentprogram.ReadCount, studentprogram.StudentProgramID,");
sql.Append(" studentprogram.StudentProgramTitle, studentprogram.StudentProgramContent, studentprogram.Imageurl,StudentProfile,StudentKeyWord FROM");
sql.Append(" (select studentprogram.TypeID, studentprogram.ReadCount, studentprogram.StudentProgramID,");
sql.Append(" studentprogram.StudentProgramTitle, studentprogram.StudentProgramContent, studentprogram.Imageurl,StudentProfile,StudentKeyWord");
sql.Append(" from studentprogram LEFT JOIN country on studentprogram.CountryID = Country.CountryID");
sql.Append(" left join educationtype on studentprogram.EducationID = educationtype.EducationID");
sql.Append(" where studentprogram.CountryID = " + countryid + " AND studentprogram.EducationID = " + educationid + ") studentprogram");
sql.Append(" RIGHT JOIN studentprogramtype on studentprogram.TypeID = studentprogramtype.TypeID ) a");
sql.Append(" WHERE 2 >= (");
sql.Append(" SELECT COUNT(*) from");
sql.Append(" (select studentprogramtype.TypeID, studentprogram.ReadCount, studentprogram.StudentProgramID,");
sql.Append(" studentprogram.StudentProgramTitle, studentprogram.StudentProgramContent, studentprogram.Imageurl,StudentProfile,StudentKeyWord FROM");
sql.Append(" (select studentprogram.TypeID, studentprogram.ReadCount, studentprogram.StudentProgramID,");
sql.Append(" studentprogram.StudentProgramTitle, studentprogram.StudentProgramContent, studentprogram.Imageurl,StudentProfile,StudentKeyWord");
sql.Append(" from studentprogram LEFT JOIN country on studentprogram.CountryID = Country.CountryID");
sql.Append(" left join educationtype on studentprogram.EducationID = educationtype.EducationID");
sql.Append(" where studentprogram.CountryID = " + countryid + " AND studentprogram.EducationID = " + educationid + ") studentprogram");
sql.Append(" RIGHT JOIN studentprogramtype on studentprogram.TypeID = studentprogramtype.TypeID) b");
sql.Append(" WHERE a.TypeID = b.TypeID and a.ReadCount <= b.ReadCount) ORDER BY a.StudentProgramID desc");
List <StudentProgram> list = MySqlDB.GetList <StudentProgram>(sql.ToString(), System.Data.CommandType.Text, null) ?? new List <StudentProgram>();
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 团队首页介绍信息
/// </summary>
/// <returns></returns>
public List <JiaJiNewWebModel.TeamIndexModel> TeamindexList()
{
try
{
//string sql = @" select a.Image1,a.TeamID,a.Name,a.Position,a.WorkDate,a.ShenQing,c.Content
// from Team a INNER JOIN Term_Relation b on a.TeamID = b.TeamID
// inner join team_content c ON c.ContentID = b.ContentID LIMIT 4";
string sql = "select TeamID,`Name`,Position,WorkDate,ShenQing,Image1,Image2,TeamProduce from Team LIMIT 4";
List <JiaJiNewWebModel.TeamIndexModel> list = MySqlDB.GetList <JiaJiNewWebModel.TeamIndexModel>(sql, System.Data.CommandType.Text, null);
foreach (JiaJiNewWebModel.TeamIndexModel item in list)
{
item.Content = Help.Chinese(item.TeamProduce);
}
Log4netHelper.WriteLog("系统日志，请求了ActiveDal类下的ActiveLsitIndex方法");
return(list);
}
catch (System.Exception ex)
{
Log4netHelper.WriteLog("错误信息：请求了ActiveDal类下的ActiveLsitIndex方法", ex);
return(null);
}
}
public List <Team> SuccessCountList(int pageindex, int teamid)
{
try
{
int pagesize = 6;
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT SQL_CALC_FOUND_ROWS successful_relation.SRelationID,StudentName, Score, student.CollegeID,JiuDuXueyuan,student.EducationID,student.CountryID,successful_relation.SuccessID ");
sql.Append(" ,CollegeName,EducationName,CountryName,SuccessTitle,CollegeImg FROM successful_relation ");
sql.Append(" LEFT JOIN student on successful_relation.StudentID = student.StudentID ");
sql.Append(" left join college on student.CollegeID = college.CollegeID ");
sql.Append(" left join educationtype on student.EducationID = educationtype.EducationID ");
sql.Append(" left join country on student.CountryID = country.CountryID ");
sql.Append(" left join successful on successful.SuccessID = successful_relation.SuccessID ");
sql.Append(" WHERE successful_relation.StudentID in ");
sql.Append(" (select StudentID from teamrelation where TeamID = " + teamid + ") ORDER BY successful_relation.SRelationID DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS();");
List <Team> list = MySqlDB.GetList <Team>(sql.ToString(), System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取地区
/// </summary>
/// <returns></returns>
public List <Area> GetArea()
{
string sql = "SELECT * from areases limit 2";
List <Area> list = MySqlDB.GetList <Area>(sql, System.Data.CommandType.Text, null);
return(list);
}
/// <summary>
/// 获取每个国家的学生
/// </summary>
/// <param name="Index">开始条数</param>
/// <param name="GoIndex">结束条数</param>
/// <returns>学生信息的List集合</returns>
public List <JiaJiNewWebModel.StudentIndexModel> CountryStuList(int countryid, int Index, int GoIndex)
{
try
{
//string sql = @"select a.StudentName,a.JiuDuXueyuan,a.Score,a.CollegeID,d.CollegeImg,d.CollegeName,b.SuccessID,a.CountryID,CountryName,EducationName from student a
// INNER JOIN Successful_Relation b on a.StudentID=b.StudentID
// INNER JOIN College d ON b.CollegeID=d.CollegeID left join country on a.CountryID=country.CountryID left join educationtype on a.EducationID=educationtype.EducationID where a.CountryID=" + countryid + " LIMIT " + Index + "," + GoIndex + "; SELECT FOUND_ROWS();";
string sql = @"select SRelationID,StudentName,JiuDuXueyuan,Score,student.CollegeID,CollegeName,college.CollegeImg,educationtype.EducationName from Successful_Relation" +
" left join student on Successful_Relation.StudentID=student.StudentID" +
" left join educationtype on educationtype.EducationID=student.EducationID" +
" left join College on student.CollegeID=College.CollegeID where CountryID=" + countryid + " LIMIT " + Index + "," + GoIndex + "; SELECT FOUND_ROWS();"
;
List <JiaJiNewWebModel.StudentIndexModel> list = MySqlDB.GetList <JiaJiNewWebModel.StudentIndexModel>(sql, CommandType.Text, null);
Log4netHelper.WriteLog("系统日志，请求了StudentDAL类下的StudentIndexList方法");
return(list);
}
catch (System.Exception ex)
{
Log4netHelper.WriteLog("错误信息：请求了ActiveDal类下的ActiveLsitIndex方法", ex);
return(null);
}
}
/// <summary>
/// 查找国家
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Country> GetCountry(int id)
{
string sql = "select CountryName,CountryYouShi,CountryImg2 from Country where CountryID=" + id + " and IsCountry=1";
List <Country> list = MySqlDB.GetList <Country>(sql, System.Data.CommandType.Text, null);
return(list);
}
/// <summary>
/// 获取项目对应学员分享的内容列表信息
/// </summary>
/// <returns></returns>
public List <JiaJiNewWebModel.Share> GetShareList(int id)
{
try
{
string sql = "select ShareID,ShareTitle,ShareContent,ShareImg,ShareDate,Pro_Img,ProactiveImg1,ProactiveImg2,ShareKeyword,ShareProfile from Shares LEFT JOIN projectitem on shares.Pro_ID=projectitem.Pro_ID where Shares.pro_ID=" + id + " limit 3 ";
List <JiaJiNewWebModel.Share> list = MySqlDB.GetList <JiaJiNewWebModel.Share>(sql, System.Data.CommandType.Text, null);
//foreach (var item in list)
//{
// if (item.ShareTitle.Length > 14)
// {
// item.ShareTitle = item.ShareTitle.Substring(0, 14);
// }
// if(item.ShareContent.Length>36)
// {
// item.ShareContent = item.ShareContent.Substring(0, 34);
// }
//}
Log4netHelper.WriteLog("系统日志，请求了ProjectItemDAL类下的GetShareList方法");
return(list);
}
catch (System.Exception ex)
{
Log4netHelper.WriteLog("错误信息：请求了ProjectItemDAL类下的GetShareList方法", ex);
return(null);
}
}
/// 获取具体活动列表
/// </summary>
/// <returns></returns>
public List <Active> ActiveList(int pageindex)
{
try
{
int pagesize = 5;
string sql = "SELECT SQL_CALC_FOUND_ROWS * FROM active left join areases on active.Site=areases.AreaID ORDER BY HeatID DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS(); ";
List <Active> list = MySqlDB.GetList <Active>(sql, System.Data.CommandType.Text, null);
if (list.Count != 0)
{
foreach (var v in list)
{
v.Datails = Help.Chinese(v.Datails);
}
foreach (var item in list)
{
if (item.Datails.Length > 65)
{
item.Datails = item.Datails.Substring(0, 65) + "......";
}
}
}
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取首加载时
/// </summary>
/// <returns></returns>
public List <JiaJiNewWebModel.Share> GetFirstList()
{
try
{
string sql = "select * from Shares where pro_ID=1";
List <JiaJiNewWebModel.Share> list = MySqlDB.GetList <JiaJiNewWebModel.Share>(sql, System.Data.CommandType.Text, null);
foreach (var item in list)
{
if (item.ShareTitle.Length > 14)
{
item.ShareTitle = item.ShareTitle.Substring(0, 14);
}
if (item.ShareContent.Length > 36)
{
item.ShareContent = item.ShareContent.Substring(0, 34);
}
}
Log4netHelper.WriteLog("系统日志，请求了ProjectItemDAL类下的GetShareList方法");
return(list);
}
catch (System.Exception ex)
{
Log4netHelper.WriteLog("错误信息：请求了ProjectItemDAL类下的GetShareList方法", ex);
return(null);
}
}
/// <summary>
/// 获取全部的移民项目
/// </summary>
/// <returns></returns>
public List <Project> ImmigrantList(int pageindex)
{
try
{
int pagesize = 5;
string sql = "SELECT SQL_CALC_FOUND_ROWS * FROM project ORDER BY ProjectID DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS(); ";
List <Project> list = MySqlDB.GetList <Project>(sql, System.Data.CommandType.Text, null);
foreach (var v in list)
{
v.ProjectContent = Help.Chinese(v.ProjectContent);
}
foreach (var item in list)
{
if (item.ProjectContent.Length > 50)
{
item.ProjectContent = item.ProjectContent.Substring(0, 50) + "......";
}
}
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
return(null);
}
}
///<summary>
///获取全部观点
/// </summary>
public List <JiaJiNewWebModel.Option> OptionList(int pageindex)
{
try
{
int pagesize = 5;
string sql = "SELECT SQL_CALC_FOUND_ROWS * from `optioninfo` ORDER BY OptionHot DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS();";
List <JiaJiNewWebModel.Option> list = MySqlDB.GetList <JiaJiNewWebModel.Option>(sql, System.Data.CommandType.Text, null);
foreach (var v in list)
{
v.OptionContent = Help.Chinese(v.OptionContent);
}
foreach (var item in list)
{
if (item.OptionContent.Length > 50)
{
item.OptionContent = item.OptionContent.Substring(0, 50) + "......";
}
}
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
/// <summary>
/// 获取国家列表
/// </summary>
/// <returns></returns>
public List <Country> GetCountry()
{
string sql = "select * from country where IsCountry=1";
List <Country> list = MySqlDB.GetList <Country>(sql, System.Data.CommandType.Text, null);
return(list);
}
/// <summary>
/// 获取具体国家规划列表
/// </summary>
/// <returns></returns>
public List <StudentProgram> StudentProgramList(int pageindex)
{
try
{
int pagesize = 8;
string sql = "SELECT SQL_CALC_FOUND_ROWS * FROM studentprogram left join country on studentprogram.CountryID=country.CountryID left join educationtype on studentprogram.EducationID=educationtype.EducationID ORDER BY ReadCount DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS(); ";
List <StudentProgram> list = MySqlDB.GetList <StudentProgram>(sql, System.Data.CommandType.Text, null);
//foreach (var v in list)
//{
// v.StudentProgramContent = Help.Chinese(v.StudentProgramContent);
//}
//foreach (var item in list)
//{
// if (item.StudentProgramContent.Length > 50)
// {
// item.StudentProgramContent = item.StudentProgramContent.Substring(0, 50) + "...";
// }
//}
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误日志", ex);
return(null);
}
}
/// <summary>
/// 获取活动列表
/// </summary>
/// <returns></returns>
public List <Active> ActiveLsit()
{
string sql = "select * from active ORDER BY ActiveDate DESC";
//string sql = "select * from active left join areases on active.Site=areases.AreaID ORDER BY HeatID DESC ";
List <Active> list = MySqlDB.GetList <Active>(sql, System.Data.CommandType.Text, null);
return(list);
}
/// <summary>
/// 获取所有的学员分享
/// </summary>
/// <returns></returns>
public List <JiaJiNewWebModel.Share> GetAllShareList(int pageindex)
{
try
{
int pagesize = 8;
string sql = "SELECT SQL_CALC_FOUND_ROWS * FROM shares ORDER BY ShareID DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS(); ";
List <JiaJiNewWebModel.Share> list = MySqlDB.GetList <JiaJiNewWebModel.Share>(sql, System.Data.CommandType.Text, null);
//Regex reg = new Regex("[\u4e00-\u9fa5]+");
//foreach (var item in list)
//{
// foreach (Math v in reg.Matches(item.Content))
// {
// item.Content = v.ToString();
// }
//}
//提取汉字
string pattern = @"^[\u300a\u300b]|[\u4e00-\u9fa5]|[\uFF00-\uFFEF]";
foreach (var item in list)
{
if (System.Text.RegularExpressions.Regex.IsMatch(item.ShareContent, pattern))
{
//提示的代码在这里写
Match m = Regex.Match(item.ShareContent, pattern);
item.ShareContent = "";
while (m.Success)
{
if (m.Value == ",")
{
item.ShareContent += m.Value;
continue;
}
item.ShareContent += m.Value;
m = m.NextMatch();
}
}
}
foreach (var item in list)
{
if (item.ShareContent.Length > 50)
{
item.ShareContent = item.ShareContent.ToString().Substring(0, 25);
}
}
return(list);
}
catch (System.Exception ex)
{
Log4netHelper.WriteLog("错误信息：请求了ProjectItemDAL类下的GetShareList方法", ex);
return(null);
}
}
/// <summary>
/// 国家页面资讯图片
/// </summary>
/// <param name="countryid"></param>
/// <param name="educatonid"></param>
/// <returns></returns>
public List <Information> CountryZiXunImage(int countryid)
{
try
{
string sql = "select InformationID,Title,InformationImgUrl from information where ReadCount=(select MAX(ReadCount) from information where CountryID=" + countryid + ") limit 1";
List <Information> list = MySqlDB.GetList <Information>(sql.ToString(), System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 热门资讯显示前6条
/// </summary>
/// <returns></returns>
public List <Information> GetInformationTopList()
{
try
{
string sql = "select InformationID,Title,InfoDate from information order by InfoDate DESC LIMIT 6 ";
List <Information> list = MySqlDB.GetList <Information>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取语言背景移民
/// </summary>
/// <returns></returns>
public List <Country> GetLBY(int countryid)
{
try
{
string sql = "select * from country where IsCountry=0 AND CountryID=" + countryid + "";
List <Country> list = MySqlDB.GetList <Country>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取合作的媒体
/// </summary>
/// <returns></returns>
public List <Medium> GetMedium()
{
try
{
string sql = "select MediumID,MediumName,MediumTitle,MediumImg,MediumUrl,`UpDate` from medium order by `UPDATE` desc LIMIT 3";
List <Medium> list = MySqlDB.GetList <Medium>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
///<summary>
///语言显示
/// </summary>
public List <Language> Language()
{
try
{
string sql = "select * from Languages";
List <Language> list = MySqlDB.GetList <Language>(sql, CommandType.Text, null);
return(list);
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// 根据热度显示资讯列表（分页）
/// </summary>
/// <param name="pageindex"></param>
/// <returns></returns>
public List <Information> GetInformationList(int pageindex)
{
try
{
int pagesize = 8;
string sql = "SELECT SQL_CALC_FOUND_ROWS * FROM information ORDER BY InfoDate DESC LIMIT " + (pageindex - 1) * pagesize + ", " + pagesize + ";SELECT FOUND_ROWS(); ";
List <Information> list = MySqlDB.GetList <Information>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
///// <summary>
///// 首页留学图片
///// </summary>
///// <param name="countryid"></param>
///// <param name="educatonid"></param>
///// <returns></returns>
//public List<LunBoImageModel> IndexLXueList()
//{
// try
// {
// StringBuilder sql = new StringBuilder();
// sql.Append(" select lunboimage.ImageUrl,lunboimage.`UpDate` from lunboimage where IsLunBo=3 order by `UpDate` desc limit 2 ");
// List<LunBoImageModel> list = MySqlDB.GetList<LunBoImageModel>(sql.ToString(), System.Data.CommandType.Text, null);
// return list;
// }
// catch (Exception ex)
// {
// return null;
// }
//}
/// <summary>
/// 首页资讯图片
/// </summary>
/// <param name="countryid"></param>
/// <param name="educatonid"></param>
/// <returns></returns>
public List <Information> IndexInforImage()
{
try
{
string sql = "select InformationID,Title,InformationImgUrl from information where ReadCount=(select MAX(ReadCount) from information) limit 1";
List <Information> list = MySqlDB.GetList <Information>(sql.ToString(), System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
///<summary>
///获取热度前六的观点
/// </summary>
public List <JiaJiNewWebModel.Option> HotOption()
{
try
{
string sql = "select * from `optioninfo` ORDER BY Date DESC LIMIT 6";
List <JiaJiNewWebModel.Option> list = MySqlDB.GetList <JiaJiNewWebModel.Option>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
///<summary>
///观点图片详情
/// </summary>
public List <JiaJiNewWebModel.Option> HotOptionImage()
{
try
{
string sql = "select OptionUrl from optioninfo where OptionHot=(select max(OptionHot) from optioninfo) limit 1";
List <JiaJiNewWebModel.Option> list = MySqlDB.GetList <JiaJiNewWebModel.Option>(sql, System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
///<summary>
///项目显示
/// </summary>
public List <projectItem> ProjectShow()
{
try
{
string sql = "select * from projectitem";
List <projectItem> list = MySqlDB.GetList <projectItem>(sql, CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
///<summary>
///移民项目
/// </summary>
public List <Project> ImmigrantShow()
{
try
{
string sql = "select * from project order by Date desc limit 2";
List <Project> list = MySqlDB.GetList <Project>(sql, CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
Log4netHelper.WriteLog("错误报告", ex);
throw ex;
}
}
/// <summary>
/// 获取首页轮播图
/// </summary>
/// <returns></returns>
public List <JiaJiNewWebModel.IndexImage> IndexImageLsit()
{
try
{
string sql = "select * from indeximage ORDER BY ImageUpDate DESC Limit 0,3";
List <JiaJiNewWebModel.IndexImage> indeximglist = MySqlDB.GetList <JiaJiNewWebModel.IndexImage>(sql, System.Data.CommandType.Text, null);
return(indeximglist);
}
catch (Exception ex)
{
return(null);
}
}
///<summary>
///申请时间规划表
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public List <ArrangeTime> ArrangeShow(int countryid, int educationid)
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" select ArrangeID,ArrangeMonth,ArrangeContent from arrangetime where CountryID=" + countryid + " and EducationID=" + educationid + " GROUP BY ShowID ORDER BY ShowID limit 7 ");
List <ArrangeTime> list = MySqlDB.GetList <ArrangeTime>(sql.ToString(), CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 二维码
/// </summary>
/// <param name="countryid"></param>
/// <param name="educatonid"></param>
/// <returns></returns>
public List <erweima> ErWeiMaList()
{
try
{
StringBuilder sql = new StringBuilder();
sql.Append(" select EWMTitle,EWMUrl from erweimainfo order by EWMUpdate desc limit 4 ");
List <erweima> list = MySqlDB.GetList <erweima>(sql.ToString(), System.Data.CommandType.Text, null);
return(list);
}
catch (Exception ex)
{
return(null);
}
}