/// <summary>
/// 获得某一个xpath的详细信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Xpath getInfo(Guid id)
{
Xpath path = new Xpath();
DatabaseAccess database = new DatabaseAccess();
String sql = "select * from xpath where id='" + id + "'";
try {
SqlDataReader data = database.executeQuery(sql);
if (data.Read()) {
path.Id = data.GetGuid(0);
path.Websitename = data.GetString(1);
path.Weblanguage = data.GetString(2);
path.Baseurl = data.GetString(3);
path.Cntitle = data.GetString(4);
path.Entitle = data.GetString(5);
path.Unit = data.GetString(6);
path.Types = data.GetString(7);
path.Author = data.GetString(8);
path.Articletype = data.GetString(9);
path.Source = data.GetString(10);
path.Language = data.GetString(11);
path.Journalname = data.GetString(12);
path.Publishtime = data.GetString(13);
path.Levels = data.GetString(14);
path.Recordtype = data.GetString(15);
path.Forbid = (int)data["forbid"];
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return path;
}
/// <summary>
/// 获得一条论文的详细信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Treatise details(Guid id)
{
Treatise treatise = new Treatise();
DatabaseAccess database = new DatabaseAccess();
String sql = "select treatise.*,xpath.websitename,xpath.baseurl from treatise,xpath where treatise.id='" + id + "' and treatise.sourceid=xpath.id";
try {
SqlDataReader result = database.executeQuery(sql);
while (result.Read()) {
treatise.Id = result.GetGuid(0);
treatise.Articleurl = result.GetString(1);
treatise.Sourceid = result.GetGuid(2);
treatise.Cntitle = result.GetString(3);
treatise.Entitle = result.GetString(4);
treatise.Unitname = result.GetString(5);
treatise.Types = result.GetString(6);
treatise.Author = result.GetString(7);
treatise.Articletype = result.GetString(8);
treatise.Source = result.GetString(9);
treatise.Language = result.GetString(10);
treatise.Journalname = result.GetString(11);
treatise.Publishtime = result.GetString(12);
treatise.Levels = result.GetString(13);
treatise.Recordtype = result.GetString(14);
treatise.Websitename = result.GetString(15);
treatise.Baseurl = result.GetString(16);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return treatise;
}
//添加一条课程信息
public bool Add(Bap_Course Bap_Course)
{
DatabaseAccess database = new DatabaseAccess();
String sql = String.Format(@"insert into Bap_Course(Course_Name,Staff_num,Teacher_Name,Department,Hours,Class_Time,Class_Week,Total_Week,Is_Week,Class_Addr,Classes) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')",
Bap_Course.Course_Name, Bap_Course.Staff_num, Bap_Course.Teacher_Name, Bap_Course.Department, Bap_Course.Hours,
Bap_Course.Class_Time, Bap_Course.Class_Week, Bap_Course.Total_Week, Bap_Course.Is_Week, Bap_Course.Class_Addr, Bap_Course.Classes);
return database.executeUpdate(sql);
}
/// <summary>
/// 获得教师的论文总数
/// </summary>
/// <param name="zgxm">职业姓名</param>
/// <returns></returns>
public int count(string zgxm)
{
int total = 0;
DatabaseAccess database = new DatabaseAccess();
String sql = "select count(*) as total from treatise where author like '%" + zgxm + "%'";
try {
SqlDataReader result = database.executeQuery(sql);
if (result.Read()) {
total = result.GetInt32(0);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return total;
}
/// <summary>
/// 获得xpath中的数据总数
/// </summary>
/// <returns></returns>
public long count()
{
long total = 0;
DatabaseAccess database = new DatabaseAccess();
string sql = "select count(*) from xpath";
try {
SqlDataReader result = database.executeQuery(sql);
if (result.Read()) {
total = result.GetInt32(0);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return total;
}
//统计课程表总条数
public int count()
{
int total = 0;
DatabaseAccess database = new DatabaseAccess();
String sql = "select count(*) as total from Bap_Course";
try
{
SqlDataReader result = database.executeQuery(sql);
if (result.Read())
{
total = result.GetInt32(0);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return total;
}
//获得个人课表
public List<Bap_Course> getCourse(String name, String value)
{
List<Bap_Course> list = new List<Bap_Course>();
DatabaseAccess database = new DatabaseAccess();
string sql = "select * from Bap_Course where " + name + " = '" + value + "'";
try
{
SqlDataReader result = database.executeQuery(sql);
while (result.Read())
{
Bap_Course course = new Bap_Course();
course.Id = result["Id"].ToString();
course.Course_Name = result.GetString(1);
course.Staff_num = result.GetString(2);
course.Teacher_Name = result.GetString(3);
course.Department = result.GetString(4);
course.Hours = result["Hours"].ToString();
course.Class_Time = result.GetString(6);
course.Class_Week = result.GetString(7);
course.Total_Week = result.GetString(8);
course.Is_Week = result.GetString(9);
course.Class_Addr = result.GetString(10);
course.Classes = result.GetString(11);
list.Add(course);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return list;
}
/// <summary>
/// 获得抓取规则的所有数据
/// </summary>
/// <param name="page">第几页</param>
/// <param name="size">每页显示的数据</param>
/// <returns></returns>
public List<Xpath> pathList(int page,int size)
{
List<Xpath> pathList = new List<Xpath>();
DatabaseAccess database = new DatabaseAccess();
String sql = "select top(" + size + ") * from xpath where id not in(select top( " + (page - 1) * size + ") id from xpath)";
try {
SqlDataReader result = database.executeQuery(sql);
while (result.Read()) {
Xpath path = new Xpath();
path.Id = (Guid)result["id"];
path.Baseurl = result["baseurl"].ToString();
path.Websitename = result["websitename"].ToString();
path.Weblanguage = result["weblanguage"].ToString();
path.Cntitle = result["cntitle"].ToString();
path.Entitle = result["entitle"].ToString();
path.Unit = result["unit"].ToString();
path.Types = result["types"].ToString();
path.Author = result["author"].ToString();
path.Articletype = result["articletype"].ToString();
path.Source = result["source"].ToString();
path.Language = result["language"].ToString();
path.Journalname = result["journalname"].ToString();
path.Publishtime = result["publishtime"].ToString();
path.Levels = result["levels"].ToString();
path.Recordtype = result["recordtype"].ToString();
path.Forbid = (int)result["forbid"];
pathList.Add(path);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return pathList;
}
/// <summary>
/// 根据文章来源网站在xpath中的ID和文章来源判断文章是否存在，存在返回true，不存在返回false
/// </summary>
/// <param name="id"></param>
/// <param name="url"></param>
/// <returns></returns>
public Boolean exist(Guid id,string url)
{
Boolean exist = false;
DatabaseAccess database = new DatabaseAccess();
String sql = "select id from treatise where articleurl='"+url+"' and sourceid='"+id+"'";
try {
SqlDataReader result = database.executeQuery(sql);
if (result.Read()) {
exist = true;
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return exist;
}
//获得手机号码
public List<Contact> getPhoneNum(String Staff_Num)
{
List<Contact> list = new List<Contact>();
DatabaseAccess database = new DatabaseAccess();
String sql = "select Phone_Num from Bap_SmsContact where Staff_Num=" + Staff_Num;
try
{
SqlDataReader result = database.executeQuery(sql);
while (result.Read())
{
Contact SmsContact = new Contact();
SmsContact.Id = result["Id"].ToString();
SmsContact.Staff_Num = result["Staff_Num"].ToString();
SmsContact.Teacher_Name = result["Teacher_Name"].ToString();
SmsContact.Phone_Num = result["Phone_Num"].ToString();
list.Add(SmsContact);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return list;
}
/// <summary>
/// 获得所有已经添加到数据库的论文网站
/// </summary>
/// <returns></returns>
public List<Xpath> webSiteList()
{
List<Xpath> webList = new List<Xpath>();
DatabaseAccess database = new DatabaseAccess();
String sql = "select id,websitename,baseurl from xpath where forbid=1";
try {
SqlDataReader result = database.executeQuery(sql);
while (result.Read()) {
Xpath path = new Xpath();
path.Id = result.GetGuid(0);
path.Baseurl = result.GetString(2);
path.Websitename = result.GetString(1) + "(" + path.Baseurl + ")";
webList.Add(path);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return webList;
}
//获取查询结果条数
public int SearchCount(String name, String value)
{
int total = 0;
DatabaseAccess database = new DatabaseAccess();
String sql = "select count(*) from Bap_Course where 1=1";
if (name != "")
{
sql += " and Teacher_Name like '%" + name + "%'";
}
if (value != "")
{
sql += " and Staff_num like '%" + value + "%'";
}
// String sql = "select count(*) from Bap_Course where " + name + " like '" + value + "%'";
try
{
SqlDataReader result = database.executeQuery(sql);
if (result.Read())
{
total = result.GetInt32(0);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return total;
}
//获取查询结果
public List<Bap_Course> Search(String name, String value, int page, int size)
{
List<Bap_Course> list = new List<Bap_Course>();
DatabaseAccess database = new DatabaseAccess();
// String sql = "select * from Bap_Course where 1=1";
String sql = "select top(" + size + ") * from Bap_Course where id not in(select top(" + (page - 1) * size + ") id from Bap_Course where 1=1)";
if(name!="")
{
sql +=" and Teacher_Name like '%"+name +"%'";
}
if (value != "")
{
sql += " and Staff_num like '%" + value + "%'";
}
// String sql = "select * from Bap_Course where " + name + " like '" + value + "%'";
try
{
SqlDataReader result = database.executeQuery(sql);
while (result.Read())
{
Bap_Course course = new Bap_Course();
course.Id = result["Id"].ToString();
course.Course_Name = result.GetString(1);
course.Staff_num = result.GetString(2);
course.Teacher_Name = result.GetString(3);
course.Department = result.GetString(4);
course.Hours = result["Hours"].ToString();
course.Class_Time = result.GetString(6);
course.Class_Week = result.GetString(7);
course.Total_Week = result.GetString(8);
course.Is_Week = result.GetString(9);
course.Class_Addr = result.GetString(10);
course.Classes = result.GetString(11);
list.Add(course);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return list;
}
//根据ID保存修改后的课程信息
public bool SaveById(Bap_Course Bap_Course)
{
DatabaseAccess database = new DatabaseAccess();
String sql = String.Format(@"update Bap_Course set
Course_Name='{0}',
Staff_num='{1}',
Teacher_Name='{2}',
Department='{3}',
Hours='{4}',
Class_Time='{5}',
Class_Week='{6}',
Total_Week='{7}',
Is_Week='{8}',
Class_Addr='{9}',
Classes='{10}' where id='" + Convert.ToInt32(Bap_Course.Id) + "'",
Bap_Course.Course_Name,
Bap_Course.Staff_num,
Bap_Course.Teacher_Name,
Bap_Course.Department,
Bap_Course.Hours,
Bap_Course.Class_Time,
Bap_Course.Class_Week,
Bap_Course.Total_Week,
Bap_Course.Is_Week,
Bap_Course.Class_Addr,
Bap_Course.Classes);
return database.executeUpdate(sql);
}
/// <summary>
/// 更新规则是否启用
/// </summary>
/// <param name="ids">id的批量字符串表示形式</param>
/// <param name="forbid">是否启用1表示启用， 0表示禁止</param>
/// <returns></returns>
public Boolean update(string ids,int forbid)
{
Boolean update = false;
DatabaseAccess database = new DatabaseAccess();
string sql = "update xpath set forbid=" + forbid + " where id in (" + ids + ")";
try {
update = database.executeUpdate(sql);
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return update;
}
/// <summary>
/// 更新整个数据的内容
/// </summary>
/// <param name="path">xpath的实例</param>
/// <returns></returns>
public Boolean update(Xpath path)
{
Boolean update = false;
DatabaseAccess database = new DatabaseAccess();
string sql = "update xpath set websitename='" + path.Websitename + "',weblanguage='" + path.Weblanguage + "',baseurl='" + path.Baseurl + "',cntitle='" + path.Cntitle + "',entitle='" + path.Entitle + "',unit='" + path.Unit + "',author='" + path.Author + "',types='" + path.Types + "',articletype='" + path.Articletype + "',source='" + path.Source + "',language='" + path.Language + "',journalname='" + path.Journalname + "',publishtime='" + path.Publishtime + "',levels='" + path.Levels + "',recordtype='" + path.Recordtype + "',forbid=" + path.Forbid + " where id='" + path.Id+"'";
try {
update = database.executeUpdate(sql);
} catch (Exception e) {
throw new Exception(e.Message);
}
return update;
}
/// <summary>
/// 检测添加的论文是否存在
/// </summary>
/// <param name="title"></param>
/// <param name="author"></param>
/// <returns></returns>
public Boolean exist(string title,string author)
{
Boolean exist = false;
DatabaseAccess database = new DatabaseAccess();
String sql = "select id from treatise where cntitle='" + title + "' or entitle='"+title+"' and author='" + author + "'";
try {
SqlDataReader result = database.executeQuery(sql);
if (result.Read()) {
exist = true;
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return exist;
}
/// <summary>
/// 插入一条新的论文记录到数据库
/// </summary>
/// <param name="treatise"></param>
/// <returns></returns>
public Boolean insert(Treatise treatise)
{
Boolean insert = false;
DatabaseAccess database = new DatabaseAccess();
String sql = "insert into treatise (id,articleurl,sourceid,cntitle,entitle,unitname,types,author,articletype,source,language,journalname,publishtime,levels,recordtype) values(NewId(),'"+
treatise.Articleurl+"','"+treatise.Sourceid+"','"+ treatise.Cntitle + "','" + treatise.Entitle + "','" + treatise.Unitname + "','" + treatise.Types + "','" + treatise.Author + "','" + treatise.Articletype + "','" + treatise.Source + "','" + treatise.Language + "','" + treatise.Journalname + "','" + treatise.Publishtime + "','" + treatise.Levels + "','" + treatise.Recordtype + "')";
try{
insert = database.executeUpdate(sql);
}
catch (Exception e){
throw new Exception(e.Message);
} finally {
database.close();
}
return insert;
}
/// <summary>
/// 分页获得用户的所有论文
/// </summary>
/// <param name="page">分页时第几页</param>
/// <param name="size">每页显示的数据量</param>
/// <param name="zgxm">职业的姓名</param>
/// <returns></returns>
public List<Treatise> list(int page,int size,string zgxm)
{
List<Treatise> list = new List<Treatise>();
DatabaseAccess database = new DatabaseAccess();
String sql = "select top(" + size + ") * from treatise where author like '%" + zgxm + "%' and id not in(select top(" + (page - 1) * size + ") id from treatise where author like '%" + zgxm + "%' )";
try {
SqlDataReader result = database.executeQuery(sql);
while (result.Read()) {
Treatise treatise = new Treatise();
treatise.Id = result.GetGuid(0);
treatise.Articleurl = result.GetString(1);
treatise.Sourceid = result.GetGuid(2);
treatise.Cntitle = result.GetString(3);
treatise.Entitle = result.GetString(4);
treatise.Unitname = result.GetString(5);
treatise.Types = result.GetString(6);
treatise.Author = result.GetString(7);
treatise.Articletype = result.GetString(8);
treatise.Source = result.GetString(9);
treatise.Language = result.GetString(10);
treatise.Journalname = result.GetString(11);
treatise.Publishtime = result.GetString(12);
treatise.Levels = result.GetString(13);
treatise.Recordtype = result.GetString(14);
list.Add(treatise);
}
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return list;
}
/// <summary>
/// 插入一条数据到数据库
/// </summary>
/// <param name="path">Xpath的对象</param>
/// <returns></returns>
public Boolean insert(Xpath path)
{
Boolean insert = false;
DatabaseAccess database = new DatabaseAccess();
String sql = "insert into xpath(id,websitename,weblanguage,baseurl,cntitle,entitle,unit,types,author,articletype,source,language,journalname,publishtime,levels,recordtype,forbid) values(NewId(),'"
+ path.Websitename + "','" + path.Weblanguage + "','" + path.Baseurl + "','" + path.Cntitle + "','" + path.Entitle + "','" + path.Unit + "','" + path.Types + "','" + path.Author + "','" + path.Articletype + "','" + path.Source + "','" + path.Language + "','" + path.Journalname + "','" + path.Publishtime + "','" + path.Levels + "','" + path.Recordtype + "',0)";
try {
insert = database.executeUpdate(sql);
} catch (Exception e) {
throw new Exception(e.Message);
} finally {
database.close();
}
return insert;
}
//根据ID获得某一条信息
public Bap_Course getOne(string id)
{
Bap_Course Bap_Course = new Bap_Course();
DatabaseAccess database = new DatabaseAccess();
string sql = "select * from Bap_Course where id = " + Int32.Parse(id);
try
{
SqlDataReader result = database.executeQuery(sql);
while (result.Read())
{
// Bap_Course.Id = result["Id"].ToString();
Bap_Course.Course_Name = result["Course_Name"].ToString();
Bap_Course.Staff_num = result["Staff_num"].ToString();
Bap_Course.Teacher_Name = result["Teacher_Name"].ToString();
Bap_Course.Department = result["Department"].ToString();
Bap_Course.Hours = result["Hours"].ToString();
Bap_Course.Class_Time = result["Class_Time"].ToString();
Bap_Course.Class_Week = result["Class_Week"].ToString();
Bap_Course.Total_Week = result["Total_Week"].ToString();
Bap_Course.Is_Week = result["Is_Week"].ToString();
Bap_Course.Class_Addr = result["Class_Addr"].ToString();
Bap_Course.Classes = result["Classes"].ToString();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
database.close();
}
return Bap_Course;
}