public static Entity.Teacher checkForgotPassword(string username, string email)
{
try
{
string sqlforgot = " SELECT * FROM Teacher WHERE Tch_username=@user AND Tch_email=@email";
string Addvalue = "@user,@email";
string value = username + "," + email;
Entity.Teacher tchCheck = new Entity.Teacher();
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
//SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
if (readCheckRole.Read())
{
tchCheck.Tch_FName = readCheckRole["Tch_FName"].ToString();
tchCheck.Tch_LName = readCheckRole["Tch_LName"].ToString();
tchCheck.Tch_username = readCheckRole["Tch_username"].ToString();
tchCheck.Tch_password = readCheckRole["Tch_password"].ToString();
tchCheck.Tch_email = readCheckRole["Tch_email"].ToString();
}
conn.Close();
return tchCheck;
}
catch (Exception)
{
return null;
}
}
public static Entity.Employee checkForgotPassword(string username, string email)
{
try
{
string sqlforgot = " SELECT * FROM Employee WHERE Emp_username=@user AND Emp_Email=@email";
string Addvalue = "@user,@email";
string value = username + "," + email;
Entity.Employee empCheck = new Entity.Employee();
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
//SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
if (readCheckRole.Read())
{
empCheck.Emp_FName = readCheckRole["Emp_FName"].ToString();
empCheck.Emp_LName = readCheckRole["Emp_LName"].ToString();
empCheck.Emp_username = readCheckRole["Emp_username"].ToString();
empCheck.Emp_password = readCheckRole["Emp_password"].ToString();
empCheck.Emp_Email = readCheckRole["Emp_Email"].ToString();
}
conn.Close();
return empCheck;
}
catch (Exception)
{
return null;
}
}
public static Entity.Student checkForgotPassword(string username, string email)
{
try
{
string sqlforgot = " SELECT * FROM Student WHERE Std_Username=@user AND Std_email=@email";
string Addvalue = "@user,@email";
string value = username + "," + email;
Entity.Student stdCheck = new Entity.Student();
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlforgot, Addvalue, value);
//SqlDataReader readCheckRole = conn.SelectSqlDataReader(sqlforgot);
if (readCheckRole.Read())
{
stdCheck.Std_FName = readCheckRole["Std_FName"].ToString();
stdCheck.Std_LName = readCheckRole["Std_LName"].ToString();
stdCheck.Std_Username = readCheckRole["Std_Username"].ToString();
stdCheck.Std_Password = readCheckRole["Std_Password"].ToString();
stdCheck.Std_email = readCheckRole["Std_email"].ToString();
}
conn.Close();
return stdCheck;
}
catch (Exception)
{
return null;
}
}
public static string selectCuriID(string curYear, string curCourse, string curGroup)
{
ClassConnectDB con=new ClassConnectDB();
string reid="";
try
{
string sqlSelect=@"SELECT Curri_Id
FROM Curriculum Where Curri_Year=@year AND Curri_Course=@co AND Curri_Group=@group";
string Addvalue="@year,@co,@group";
string value=curYear+","+curCourse+","+curGroup;
SqlDataReader dr= con.SelectWhereSqlDataReader(sqlSelect,Addvalue,value);
if (dr.Read())
{
reid = dr["Curri_Id"].ToString();
}
return reid;
}
catch (Exception)
{
return "";
}
con.Close();
}
public static bool insertDetailTeachAfter(string year, string level, string term, string group, string subject, string userid)
{
ClassConnectDB conn = new ClassConnectDB();
string sqlcheckvalue = "SELECT DetailTech_ID FROM DetailTech WHERE DetailTech_Level=@level and DetailTech_Class=@class and DetailTeach_Year =@year and DetailTech_Term=@term and StructSub_Code=@code";
string addvaluecheck = "@level,@class,@year,@term,@code";
string valuecheck = level + "," + group + "," + year + "," + term + "," + subject;
SqlDataReader drrcheck = conn.SelectWhereSqlDataReader(sqlcheckvalue, addvaluecheck, valuecheck);
if (!drrcheck.HasRows)
{
string sql = @"INSERT INTO DetailTech
(DetailTech_Level, DetailTech_Class, DetailTeach_Year, DetailTech_Term, StructSub_Code, Create_user, CreateDate)
VALUES(@level,@class,@year,@term,@code,@user,getDate())";
string addvalue = "@level,@class,@year,@term,@code,@user";
string value = level + "," + group + "," + year + "," + term + "," + subject + "," + userid;
bool insert = conn.InsertValue(sql, addvalue, value);
conn.Close();
if (insert) { return true; }
else { return false; }
}
else
{
return false;
}
}
public static Entity.Student checkRoleLogin(string username, string password)
{
try
{
Entity.Student stdlog = new Entity.Student();
string sqlchekRole = " SELECT * FROM Student WHERE (Std_Username = @user) AND (Std_Password = @pass)";
string Addvalue = "@user,@pass";
string value = username + "," + password;
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
if (readCheckRole.Read())
{
stdlog.Std_Type = readCheckRole["Std_Type"].ToString();
stdlog.Std_FName = readCheckRole["Std_FName"].ToString();
stdlog.Std_LName = readCheckRole["Std_LName"].ToString();
stdlog.Std_Campus_Code = readCheckRole["Std_Campus_Code"].ToString();
stdlog.Std_YearEducate = readCheckRole["Std_YearEducate"].ToString();
stdlog.Std_Username = readCheckRole["Std_Username"].ToString();
stdlog.Std_Password = readCheckRole["Std_Password"].ToString();
stdlog.std_picturepath = readCheckRole["Std_picturepath"].ToString();
}
string iplog = Common.network.showIp();
string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
string tid = readCheckRole["Std_Campus_Code"].ToString();
string insertLog = "INSERT INTO LogLogInStd(Log_IP, Log_Date, Log_timeStart, Std_Campus_Code) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
conn.QueryExecuteNonQuery(insertLog);
conn.Close();
return stdlog;
}
catch (Exception)
{
return null;
}
}
public static Entity.Teacher checkRoleLogin(string username, string password)
{
Entity.Teacher tch = new Entity.Teacher();
try
{
string sqlchekRole = " SELECT * FROM Teacher WHERE Tch_username=@user AND Tch_password=@pass";
string Addvalue = "@user,@pass";
string value = username + "," + password;
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
if (readCheckRole.Read())
{
tch.Tch_ID = readCheckRole["Tch_ID"].ToString();
tch.Tch_Type = readCheckRole["Tch_Type"].ToString();
tch.Tch_FName = readCheckRole["Tch_FName"].ToString();
tch.Tch_LName = readCheckRole["Tch_LName"].ToString();
tch.Tch_username = readCheckRole["Tch_username"].ToString();
tch.Tch_password = readCheckRole["Tch_password"].ToString();
tch.Tch_picturepath = readCheckRole["Tch_picturepath"].ToString();
}
string iplog = Common.network.showIp();
string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
string tid = readCheckRole["Tch_ID"].ToString();
string insertLog = "INSERT INTO LogLoginTeach(Log_IP, Log_Date, Log_timeStart, Tch_ID) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
conn.QueryExecuteNonQuery(insertLog);
conn.Close();
return tch;
}
catch (Exception)
{
return null;
}
}
public static Entity.Employee checkRoleLogin(string username, string password)
{
try
{
Entity.Employee emp = new Entity.Employee();
string sqlchekRole = " SELECT * FROM Employee WHERE Emp_username=@user AND Emp_password=@pass";
string Addvalue = "@user,@pass";
string value = username + "," + password;
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader readCheckRole = conn.SelectWhereSqlDataReader(sqlchekRole, Addvalue, value);
if (readCheckRole.Read())
{
emp.Emp_ID = readCheckRole["Emp_ID"].ToString();
emp.Emp_Type = readCheckRole["Emp_Type"].ToString();
emp.Emp_LName = readCheckRole["Emp_LName"].ToString();
emp.Emp_FName = readCheckRole["Emp_FName"].ToString();
emp.Emp_username=readCheckRole["Emp_username"].ToString();
emp.Emp_password = readCheckRole["Emp_password"].ToString();
}
string iplog = Common.network.showIp();
string logdate = "CONVERT(VARCHAR(10), GETDATE(), 104)";
string logtime = "CONVERT(VARCHAR(8), GETDATE(), 108)";
string tid = readCheckRole["Emp_ID"].ToString();
string insertLog = "INSERT INTO LogLoginEmp(Log_IP, Log_Date, Log_timeStart, Emp_id) VALUES('" + iplog + "'," + logdate + "," + logtime + "," + tid + ")";
conn.QueryExecuteNonQuery(insertLog);
conn.Close();
return emp;
}
catch (Exception)
{
return null;
}
}
public static Entity.BranchNewsInfo selectBranchNewsShowDetailNewsPage(string query)
{
ClassConnectDB conn = new ClassConnectDB();
Entity.BranchNewsInfo branchnews = new Entity.BranchNewsInfo();
try
{
string sql = "SELECT * FROM BranchNews WHERE BranchNews_ID=@id";
string Addvalue = "@id";
string value = query;
SqlDataReader drrShow = conn.SelectWhereSqlDataReader(sql, Addvalue, value);
if (drrShow.Read())
{
branchnews.BranchNews_Name = drrShow["BranchNews_Name"].ToString();
branchnews.Branch_Detail = drrShow["BranchNews_Detail"].ToString();
branchnews.Update_date = drrShow["Update_date"].ToString();
branchnews.Branch_Path = drrShow["BranchNews_Path"].ToString();
}
conn.Close();
return branchnews;
}
catch (Exception)
{
return null;
}
}
public static Entity.StudentNewsInfo selectTrainingNewsShowDetailNewsPage(string query)
{
ClassConnectDB conn = new ClassConnectDB();
Entity.StudentNewsInfo student = new Entity.StudentNewsInfo();
try
{
string sql = "SELECT * FROM StudentNews WHERE StudentNews_ID=@id";
string Addvalue = "@id";
string value = query;
SqlDataReader drrShow = conn.SelectWhereSqlDataReader(sql, Addvalue, value);
if (drrShow.Read())
{
student.StudentNews_Name = drrShow["StudentNews_Name"].ToString();
student.StudentNews_Detail = drrShow["StudentNews_Detail"].ToString();
student.Update_date = drrShow["Update_date"].ToString();
student.StudentNews_Path = drrShow["StudentNews_Path"].ToString();
}
conn.Close();
return student;
}
catch (Exception)
{
return null;
}
}
public static DataTable selectShowStdCheckNameSum(string dchID, string stdCode)
{
try
{
DataTable dt = new DataTable();
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT 'STUDENT' ,
( SELECT COUNT(CheckName_Num) as coou FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code INNER JOIN
CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID where Student.Std_Campus_Code=@stdCode and EnrollIn.DeEduStd=@dchid and CheckName_Status='S') as numSTD
,'LATE',
( SELECT COUNT(CheckName_Num) as coou FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code INNER JOIN
CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID where Student.Std_Campus_Code=@stdCode and EnrollIn.DeEduStd=@dchid and CheckName_Status='L') as numLate
,'NO_STD',
(SELECT COUNT(CheckName_Num) as coou FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code INNER JOIN
CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID where Student.Std_Campus_Code=@stdCode and EnrollIn.DeEduStd=@dchid and CheckName_Status='N') as numNosTd
,(SELECT COUNT(CheckName_Num) as coou FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code INNER JOIN
CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID where Student.Std_Campus_Code=@stdCode and EnrollIn.DeEduStd=@dchid ) as numall";
string addvalue = "@stdCode ,@dchid";
string value = stdCode + "," + dchID;
SqlDataReader drr = conn.SelectWhereSqlDataReader(sql, addvalue, value);
dt.Load(drr);
conn.Close();
return dt;
}
catch (Exception)
{
return null;
}
}
public static DataTable selectShowListViewMainHomeWork(string classid, string dchid)
{
try
{
DataTable resultdt = new DataTable();
resultdt.Columns.Add("Homework_Name");
resultdt.Columns.Add("Create_date");
resultdt.Columns.Add("Create_time");
resultdt.Columns.Add("countuser");
resultdt.Columns.Add("Homework_Num");
resultdt.Columns.Add("Tch_Create");
string sqlselectNumcheckname = @" SELECT distinct hw.[Homework_Num] from Homework hw INNER JOIN
EnrollIn en ON hw.Enroll_ID = en.Enroll_ID INNER JOIN
DetailEducationOfStudent deu ON deu.DeEduStd=en.DeEduStd
where deu.EduStdID = '" + dchid + "'";
ClassConnectDB con = new ClassConnectDB();
SqlDataReader drrselectNumcheck = con.SelectSqlDataReader(sqlselectNumcheckname);
DataTable selectdt = new DataTable();
selectdt.Load(drrselectNumcheck);
if (selectdt.Rows.Count > 0)
{
foreach (DataRow roww in selectdt.Rows)
{
string num = roww[0].ToString();
string sqlselecthowork = @" SELECT distinct [Homework_Name]
,( SELECT distinct CONVERT(VARCHAR(12),Create_date, 106) FROM Homework INNER JOIN EnrollIn ON Homework.Enroll_ID = EnrollIn.Enroll_ID INNER JOIN
DetailEducationOfStudent dedu ON EnrollIn.DeEduStd= dedu.DeEduStd where (dedu.ClassRoom_ID = @classid ) AND (Homework.Homework_Num=@num))AS Create_date
,(SELECT distinct( CONVERT(CHAR(5), Create_date, 114)) FROM Homework INNER JOIN EnrollIn ON Homework.Enroll_ID = EnrollIn.Enroll_ID INNER JOIN
DetailEducationOfStudent dedu ON EnrollIn.DeEduStd= dedu.DeEduStd where (dedu.ClassRoom_ID = @classid) AND (Homework.Homework_Num=@num))as Create_time
,( SELECT count(Homework.Enroll_ID) FROM Homework INNER JOIN EnrollIn ON Homework.Enroll_ID = EnrollIn.Enroll_ID INNER JOIN
DetailEducationOfStudent dedu ON EnrollIn.DeEduStd= dedu.DeEduStd where (dedu.ClassRoom_ID = @classid) AND (Homework.Homework_Num=@num) AND (Homework_Status='Y')) AS countuser
,[Homework_Num]
,[Tch_Create]
FROM [Homework] where Homework_Num = @num ";
string addvalue = "@classid,@num";
string value = classid + "," + num;
SqlDataReader drr = con.SelectWhereSqlDataReader(sqlselecthowork, addvalue, value);
DataTable dtt = new DataTable();
dtt.Load(drr);
foreach (DataRow roo in dtt.Rows)
{
resultdt.Rows.Add(roo[0].ToString(), roo[1].ToString(), roo[2].ToString(), roo[3].ToString(), roo[4].ToString(), roo[5].ToString());
}
}
}
return resultdt;
}
catch (Exception)
{
return null;
}
}
public static DataTable searchSubjectRecusiveTest(string p)
{
try
{
string sql = @"SELECT *
FROM [WEBCSDB].[dbo].[StructSubject]
where StructSub_Code='"+p+"'";
string Addvalue = "@p";
string value = p;
ClassConnectDB conn=new ClassConnectDB();
SqlDataReader dr = conn.SelectWhereSqlDataReader(sql,Addvalue,value);
DataTable dt = new DataTable();
dt.Load(dr);
return dt;
}
catch (Exception)
{
return null;
}
}
public static DataTable loadAllShowGridePageSubject(string code)
{
if (!code.Equals(""))
{
SqlDataReader drrsub;
string sqlString = "";
ClassConnectDB conn = new ClassConnectDB();
try
{
string sqlfollowSubject = "SELECT DISTINCT StructSub_CodePass FROM FollowSubject WHERE StructSub_Code=@code";
string Addvalue = "@code";
string value = code;
SqlDataReader drr = conn.SelectWhereSqlDataReader(sqlfollowSubject, Addvalue, value);
sqlString = @"SELECT *
FROM StructSubject
where ";
int i = 1;
while (drr.Read())
{
if (i == 1)
{
sqlString += " StructSub_Code='" + drr["StructSub_CodePass"].ToString() + "'";
}
else
{
sqlString += " or StructSub_Code='" + drr["StructSub_CodePass"].ToString() + "'";
}
i++;
}
drrsub = conn.SelectSqlDataReader(sqlString);
DataTable dt = new DataTable();
dt.Load(drrsub);
return dt;
conn.Close();
}
catch (Exception)
{
return null;
}
}
else
{
return null;
}
}
public static Entity.CurriculumInfo selectLoadShowSubject(string code)
{
try
{
string sql = "SELECT * FROM StructSubject Where StructSub_Code=@code";
string Addvalue = "@code";
string value = code;
Entity.CurriculumInfo sub = new Entity.CurriculumInfo();
ClassConnectDB conn=new ClassConnectDB();
SqlDataReader read = conn.SelectWhereSqlDataReader(sql,Addvalue,value);
if (read.Read()) {
sub.StructSub_Code = read["StructSub_Code"].ToString();
sub.StructSub_NameTha=read["StructSub_NameTha"].ToString();
sub.StructSub_NameEn=read["StructSub_NameEn"].ToString();
sub.StructSub_Detail=read["StructSub_Detail"].ToString();
sub.StructSub_Credit = read["StructSub_Credit"].ToString();
// sub.Curri_Year = read["Curri_Id"].ToString();
}
string sqlCurri = "SELECT * FROM Curriculum Where Curri_Id=@curid";
string addCurri = "@curid";
string valueCurri = read["Curri_Id"].ToString();
SqlDataReader readCurri = conn.SelectWhereSqlDataReader(sqlCurri,addCurri,valueCurri);
if (readCurri.Read()) {
sub.Curri_Year = readCurri["Curri_Year"].ToString();
sub.Curri_Course = readCurri["Curri_Course"].ToString();
sub.Curri_Group = readCurri["Curri_Group"].ToString();
}
return sub;
}
catch (Exception)
{
return null;
}
}
public static string insertClassIDinDetailTeach(string tchid, string subcode, string level, string term, string yearEdu)
{
/*********************************
*
* ตรวจสอบห้องเรียนรหัสห้องเรียนว่ามีอยู่หรือไม่
*
* *******************************/
string year = (Convert.ToInt32(yearEdu) + 543).ToString();
string classid = "";
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT ClassRoom.ClassRoom_ID as classid
FROM ClassRoom INNER JOIN DetailTech ON ClassRoom.ClassRoom_ID = DetailTech.ClassRoom_ID
where DetailTech.Tch_ID=@tch and DetailTech.StructSub_Code=@subcode and
DetailTech.DetailTech_Term=@term and DetailTech.DetailTeach_Year=@yearEdu";
string addvalue = "@tch,@subcode,@level,@term,@yearEdu";
string value = tchid + "," + subcode + "," + level + "," + term + "," + year;
SqlDataReader drr = conn.SelectWhereSqlDataReader(sql, addvalue, value);
if (!drr.Read())
{
string sqlInsert = @"INSERT INTO ClassRoom(ClassRoom_Name, Create_date)
VALUES( (SELECT StructSub_NameTha FROM StructSubject where StructSub_Code='" + subcode + "'),getDate())";
bool insert = conn.QueryExecuteNonQuery(sqlInsert);
if (insert)
{
string sqlSelectMaxClassid = " SELECT MAX([ClassRoom_ID]) as mx FROM [WEBCSDB].[dbo].[ClassRoom] ";
SqlDataReader drrread = conn.SelectSqlDataReader(sqlSelectMaxClassid);
if (drrread.Read())
{
classid = drrread["mx"].ToString();
}
drrread.Close();
}
}
else
{
classid = drr["classid"].ToString();
drr.Close();
}
conn.Close();
return classid;
}
public static DataTable checkNameSumShowDetail(string enrollId, string detailid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"select DISTINCT(Student.Std_Campus_Code) as camCode, Student.Std_FName, Student.Std_LName,
(select count(checkName_Status) from CheckName where CheckName_Status='S' and Enroll_ID=@enroll ) as student ,
(select count(checkName_Status) from CheckName where CheckName_Status='L' and Enroll_ID=@enroll ) as Late ,
(select count(checkName_Status) from CheckName where CheckName_Status='N' and Enroll_ID=@enroll ) as nostd
FROM CheckName INNER JOIN
EnrollIn ON CheckName.Enroll_ID = EnrollIn.Enroll_ID INNER JOIN
Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code
where EnrollIn.DeEduStd=@detailID and EnrollIn.Enroll_ID=@enroll ";
string Addvalue = "@enroll,@detailID";
string value = enrollId + "," + detailid;
SqlDataReader drr = conn.SelectWhereSqlDataReader(sql, Addvalue, value);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
catch (Exception)
{
return null;
}
}