public DataTable upData()
{
string sql = "select distinct Class as 班级, ClassId as 班级代号, Garde as 年级,Department as 院系 from Basic_Information order by Garde ,Department ,ClassId";
dt = db.FillDt(sql);
return(dt);
}
public string UserPwd(string user)//根据名字找 pwd
{
string sql = "select PassWord from Users where UsersName='" + user + "'";
dt = sqlDB.FillDt(sql);
return(dt.Rows[0]["PassWord"].ToString());
}
/// <summary>
/// 判断实验室门标是否重复
/// </summary>
/// <param name="no"></param>
/// <returns></returns>
public bool ReLabNo(string no)
{
str = "select * from LabInfo where name='" + no + "'";
if (sql.FillDt(str).Rows.Count > 0)
{
return(true);
}
return(false);
}
public DataTable recordDemo(string courseName, string classs, string tea, string lab)
{
try
{
int flag = 0;
string ss = "";
str = "select * from LabCourse ";
if (!string.IsNullOrWhiteSpace(courseName))
{
flag = 1;
ss += " where courseName like '%" + courseName + "%'";
}
if (!string.IsNullOrWhiteSpace(classs))
{
if (flag == 0)
{
flag = 1;
ss += " where classs like '%" + classs + "%'";
}
else
{
ss += " and classs like '%" + classs + "%'";
}
}
if (!string.IsNullOrWhiteSpace(tea))
{
if (flag == 0)
{
flag = 1;
ss += " where teacher like '" + tea + "%'";
}
else
{
ss += " and teacher like '" + tea + "%'";
}
}
if (!string.IsNullOrWhiteSpace(lab))
{
if (flag == 0)
{
flag = 1;
ss += " where lab = '" + lab + "'";
}
else
{
ss += " and lab = '" + lab + "'";
}
}
string kk = str + ss + " order by id desc";
DataTable dd = sql.FillDt(kk);
int a = 0;
a++;
return(dd);
}
catch (Exception)
{
return(null);
}
}
public DataTable SearchTeacher(string id, string name)
{
SqlDB db = new SqlDB();
string strsql = "select id as 工号,TeacherName as 姓名, Department as 学院,Major as 专业 from Teacher ";
string strwhere = "";
if (!string.IsNullOrWhiteSpace(id))
{
if (string.IsNullOrWhiteSpace(strwhere))
{
strwhere += "where";
}
strwhere += " id like '%" + id + "%' ";
}
if (!string.IsNullOrWhiteSpace(name))
{
if (string.IsNullOrWhiteSpace(strwhere))
{
strwhere += " where ";
}
else
{
strwhere += " and ";
}
strwhere += " TeacherName like '%" + name + "%' ";
}
strsql += strwhere + "order by id";
DataTable dt = db.FillDt(strsql);
return(dt);
}
public static DataTable update()//static。
{
string sql = "select ABS(building_id) as 教学楼,name as 教室名称,ABS(classroom_id) as 教室门牌号,people as 教室可容纳人数,manager as 教室管理员,descride as 教室信息 from classroom order by ABS(building_id) ,ABS(classroom_id) asc";
SqlDB db = new SqlDB();
DataTable dt = db.FillDt(sql);
return(dt);
}
public DataTable GetAdmin()
{
SqlDB db = new SqlDB();
string strsql = "select id as ID,UsersName as 用户名, PassWord as 密码 from Users";
DataTable dt = db.FillDt(strsql);
return(dt);
}
public DataTable GetTeacher()
{
SqlDB db = new SqlDB();
string strsql = "select id as 工号,TeacherName as 姓名, Department as 学院,Major as 专业 from Teacher order by id";
DataTable dt = db.FillDt(strsql);
return(dt);
}
/// <summary>
/// 得到treeview的第2个节点
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public DataTable GetNode2(string name)
{
DataTable ds = new DataTable();
string strql = "select TeacherName from Teacher where Department='" + name + "'";
SqlDB db = new SqlDB();
ds = db.FillDt(strql);
return(ds);
}
public DataTable GetCourse1(string name)
{
DataTable dt = new DataTable();
string sql = "select Course,Course_Id from AddCourse where Department='" + name + "'";
SqlDB db = new SqlDB();
dt = db.FillDt(sql);
return(dt);
}
/// <summary>
/// 根据年级查找院系并将班级list实体化返回
/// </summary>
/// <param name="Grade"></param>
/// <returns></returns>
public DataTable LoadCmbCs(string garde, string de)
{
SqlDB db = new SqlDB();
string sql = "select ClassId,Class from Basic_Information where Department='" + de + "' and Garde='" + garde + "'";
DataTable dt = new DataTable();
dt = db.FillDt(sql);
return(dt);
}
/// <summary>
/// 得到treeview的第一个节点
/// </summary>
/// <returns></returns>
public DataTable GetNode1()
{
DataTable ds = new DataTable();
string strsql = "select Department from Teacher GROUP BY Department";
SqlDB db = new SqlDB();
ds = db.FillDt(strsql);
return(ds);
}
/// <summary>
/// 根据教学楼的id返回对应教室的集合
/// </summary>
/// <param name="xx"></param>
/// <returns></returns>
public DataTable Class(string xx)
{
DataTable dt = new DataTable();
SqlDB db = new SqlDB();
string sql = "select Classroom_Id,Name from Classroom where Building_Id='" + xx + "' order by Classroom_Id";
dt = db.FillDt(sql);
return(dt);
}
/// <summary>
/// 返回教学楼列表
/// </summary>
/// <returns></returns>
public DataTable LoadBuilding()
{
List <Classroom> list = new List <Classroom>();
SqlDB db = new SqlDB();
DataTable dt = new DataTable();
string sql = "select distinct ABS(Building_Id) as Building_Id from Classroom order by ABS(Building_Id) ";
dt = db.FillDt(sql);
return(dt);
}
public string GetMajor(string name)
{
DataTable ds = new DataTable();
string strql = "select Major from Teacher where TeacherName='" + name + "'";
SqlDB db = new SqlDB();
ds = db.FillDt(strql);
string major = ds.Rows[0][0].ToString();
return(major);
}
/// <summary>
/// 查找所有
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="a"></param>
/// <returns></returns>
public DataTable findAll <T>(T a)
{
try
{
str = DBOperate.sqlLoadAll(a);
return(sql.FillDt(str));
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 返回list实体类的年级信息
/// sqldb是基层辅助类 用来对数据库的基本操作
/// </summary>
/// <returns></returns>
public List <Basic_Information> LoadCobGrd()
{
List <Basic_Information> list = new List <Basic_Information>();
string sql = "select Garde from Basic_Information GROUP BY Garde";
SqlDB db = new SqlDB();
DataTable dt = db.FillDt(sql);
foreach (DataRow item in dt.Rows)
{
Basic_Information information = new Basic_Information();
information.Garde = item["Garde"].ToString();
list.Add(information);
}
return(list);
}
/// <summary>
/// 根据 账号 查找 通知集合 2017年6月27日21:12:04
/// </summary>
/// <param name="find"></param>
/// <returns></returns>
public List <HS_Inform> FindHS_Inform_ByAccount(E_User find)
{
string ss = "";
ss = @"select * from HS_Inform where ( inform_Message_Time < inform_Message_Total_Time )";
if (!string.IsNullOrWhiteSpace(find.user_Account))
{
ss += " and inform_User_Account = '" + find.user_Account + "'";
}
str = ss + " order by inform_Id desc ";
DataTable table = sql.FillDt(str);
List <HS_Inform> list = HS_Inform.DataTable2HS_Inform(table);
return(list);
}
//窗体加载的时候加载 系别 年级 老师
public List <DataTable> FormLoad()
{
sql = "select distinct Department from CourseBuild";
list.Add(sqldb.FillDt(sql));
sql = "select distinct Garde from Basic_Information ";
list.Add(sqldb.FillDt(sql));
sql = "select distinct TeacherName from Teacher where TeacherName in (select teacher from CourseBuild)";
list.Add(sqldb.FillDt(sql));
return(list);
}
/// <summary>
/// 根据院系查找班级并将班级list实体化返回
/// </summary>
/// <param name="Grade"></param>
/// <returns></returns>
public List <Basic_Information> LoadCmbdt(Basic_Information Grade)
{
SqlDB db = new SqlDB();
string sql = "select distinct Department from Basic_Information where Garde='" + Grade.Garde + "'";
List <Basic_Information> list = new List <Basic_Information>();
DataTable dt = new DataTable();
dt = db.FillDt(sql);
foreach (DataRow item in dt.Rows)
{
Basic_Information information = new Basic_Information();
information.Department = item["Department"].ToString();
list.Add(information);
}
return(list);
}
public DataTable findCourseByLab(string labNo)
{
try
{
str = "select * from LabCourse where remark1='" + labNo + "'";
return(sql.FillDt(str));
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 通过 账号主键 精确查找 会员 2017年6月27日20:56:29
/// </summary>
/// <param name="user_id">用户的主键</param>
/// <returns></returns>
public E_User FindE_User_ByAccount(string user_Account)
{
str = @"select user_Id,user_Account,user_Name,
user_Address,user_Balance,user_MS_Count,
user_Rank,user_Tel
from E_User where user_Account='" + user_Account + "' ";
DataTable table = sql.FillDt(str);
E_User dd = new E_User();
dd.user_Id = (int)table.Rows[0]["user_Id"];
dd.user_Account = table.Rows[0]["user_Account"].ToString();
dd.user_Name = table.Rows[0]["user_Name"].ToString();
dd.user_Address = table.Rows[0]["user_Address"].ToString();
dd.user_Balance = table.Rows[0]["user_Balance"].ToString();
dd.user_MS_Count = (int)table.Rows[0]["user_MS_Count"];
dd.user_Rank = (int)table.Rows[0]["user_Rank"];
dd.user_Tel = table.Rows[0]["user_Tel"].ToString();
return(dd);
}
/// <summary>
/// 返回表的操作
/// </summary>
/// <param name="KEY">key值决定调用那条sql语句</param>
/// <returns></returns>
public DataTable dtShow(int KEY)
{
string sqlst = null;
int key = KEY;
if (KEY == 1) //通过教学楼，教室，第几周，周几查出详细课程
{
sqlst = "SELECT DISTINCT Building_Id AS 教学楼,Name AS 教室,'" + WK + "' AS 第几周,Weekday AS 周几,Section AS 节次 FROM CourseBuild WHERE Building_Id='" + B_ID + "' AND Name='" + CM + "' AND Week LIKE '%" + WK + "%' AND Weekday='" + WY + "' order by Section";
}
else if (KEY == 2) //查出节次
{
sqlst = "SELECT Section FROM CourseBuild WHERE Building_Id='" + B_ID + "' AND Name='" + CM + "' AND Week LIKE '%" + WK + "%' AND Weekday='" + WY + "'";
}
else if (KEY == 3)
{
sqlst = "SELECT Building_Id,Name,Week,Weekday,Section,Course,Garde,Department,Class,Teacher,Year,Term,reason From CourseBuild WHERE Building_Id='" + B_ID + "'AND Name='" + CM + "' AND Week LIKE '%" + WK + "%' AND Weekday='" + WY + "' AND Section='" + Section + "' AND Course='" + Course + "' AND Garde='" + Garde + "' AND Department='" + Department + "' AND Class='" + Class + "' AND Teacher ='" + Teacher + "'";
}
dt = db.FillDt(sqlst);
return(dt);
}
/// <summary>
/// 通过 地区或行业 模糊查找 消息
/// </summary>
/// <param name="message_title"></param>
/// <returns></returns>
//public List<MessageEN> FindMessage_ByRegionOrIndustry(string region, string industry)
//{
// string ss = "";
// ss = "select * from MessageEN where (message_State is null or message_State='') ";
// if (!string.IsNullOrWhiteSpace(region) && !region.Equals("undefined"))
// {
// ss += " and message_Region like '%" + region + "%'";
// }
// if (!string.IsNullOrWhiteSpace(industry) && !industry.Equals("undefined"))
// {
// ss += " and message_Industry like '%" + industry + "%'";
// }
// str = ss + " order by message_Id desc ";
// DataTable table = sql.FillDt(str);
// List<MessageEN> list = MessageEN.DataTable2Message(table);
// return list;
//}
#endregion
/// <summary>
/// 通过 地区或行业 模糊查找 消息
/// </summary>
/// <param name="message_title"></param>
/// <returns></returns>
public List <MessageLessList> FindMessage_ByRegionOrIndustry(string region, string industry)
{
string ss = "";
ss = @"select message_Id,message_Region,message_Industry,
message_Title,message_Content,message_State,
message_Creator,message_Checker,remark1,remark2
from MessageEN where (message_State is null or message_State='') ";
if (!string.IsNullOrWhiteSpace(region) && !region.Equals("undefined"))
{
ss += " and message_Region like '%" + region + "%'";
}
if (!string.IsNullOrWhiteSpace(industry) && !industry.Equals("undefined"))
{
ss += " and message_Industry like '%" + industry + "%'";
}
str = ss + " order by message_Id desc ";
DataTable table = sql.FillDt(str);
List <MessageLessList> list = MessageLessList.DataTable2Message(table);
return(list);
}
//判断用户名重复
public bool judgeRepeat(string name)
{
try
{
bool b = false;
str = "select name from Users where name='" + name + "'";
DataTable dt = sql.FillDt(str);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
b = true;
}
}
return(b);
}
catch (Exception)
{
return(false);
}
}