public static bool deleteDetailTeach(string dchID)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "DELETE FROM DetailTech WHERE DetailTech_ID='" + dchID + "'";
bool del = conn.QueryExecuteNonQuery(sql);
conn.Close();
return del;
}
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 bool deleteCommentPostInclass(string classRoomid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string deleteCommentsql = " delete from CommentPostClass where (ComPostClass_ID in ( select ComPostClass_ID FROM CommentPostClass where ClassRoom_ID='" + classRoomid + "'))";
conn.QueryExecuteNonQuery(deleteCommentsql);
string deletePostSql = "delete from PostClassRoom where ClassRoom_ID = '" + classRoomid + "' ";
conn.QueryExecuteNonQuery(deletePostSql);
conn.Close();
return true;
}
catch (Exception) { return false; }
}
public static bool deleteEnroll(string dchid, string userid)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = " delete FROM [EnrollIn] where Enroll_ID=(select max (Enroll_ID) from EnrollIn where DetailTech_ID='" + dchid + "' and Std_Campus_Code='" + userid + "' )";
bool del = conn.QueryExecuteNonQuery(sql);
conn.Close();
return del;
}
public static bool insertNewSubjectStd(string year, string yearcurri, string userid)
{
string sqlinsert = @" insert into EducationOfStudents (
[EduStdLevel],[EduStdTerm] ,[StructSub_Code],[Create_user],[CreateDate],[EduStdYearEdu],[EduStdYearCurri])
SELECT [ShowPlan_Year],[ShowPlan_Semester],[StructSub_Code] ,'" + userid + "' ,GETDATE() ,'" + year + "','" + yearcurri + "' FROM [WEBCSDB].[dbo].[ShowPlanEducate] where ShowPlan_YearUpdate='" + yearcurri + "'";
ClassConnectDB conn = new ClassConnectDB();
bool insert = conn.QueryExecuteNonQuery(sqlinsert);
conn.Close();
return insert;
}
public static bool deletClassRoom(string classRoomid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string deletePostSql = " DELETE FROM ClassRoom where ClassRoom_ID = '" + classRoomid + "' ";
conn.QueryExecuteNonQuery(deletePostSql);
conn.Close();
return true;
}
catch (Exception) { return false; }
}
public static bool insertEnroll(string userid, string detailTeach)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO EnrollIn (DetailTech_ID, Std_Campus_Code, Enroll_Status)
VALUES('" + detailTeach + "','" + userid + "','N')";
bool inesrt = conn.QueryExecuteNonQuery(sql);
conn.Close();
return inesrt;
}
public static bool updateDetailTeach(string tchid, string classid, string dchid, string subcode, string subThai)
{
ClassConnectDB conn = new ClassConnectDB();
string sqlupdateDetailTeach = "UPDATE DetailTech SET StructSub_Code='" + subcode + "' where DetailTech_ID='" + dchid + "' and Tch_ID='" + tchid + "'";
bool update = conn.QueryExecuteNonQuery(sqlupdateDetailTeach);
string sql = "UPDATE ClassRoom SET ClassRoom_Name='" + subThai + "' where ClassRoom_ID ='" + classid + "'";
bool upd = conn.QueryExecuteNonQuery(sql);
conn.Close();
return upd;
}
public static bool insertDetailTeach(string yeare, string level, string term, string group, string subject, string userid, string teacher, string classid)
{
/*********************************
*
* บันทึกรายละเอียดห้องเรียน
*
* *******************************/
string year = (Convert.ToInt32(yeare) + 543).ToString();
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO DetailTech
(DetailTech_Level, DetailTech_Class, DetailTeach_Year, DetailTech_Term, Tch_ID, ClassRoom_ID, StructSub_Code, Create_user, CreateDate, DetailTeach_Status)
VALUES('" + level + "','" + group + "'," + year + ",'" + term + "','" + teacher + "','" + classid + "','" + subject + "','" + userid + "',getDate(),'A')";
//string addvalue = "@level,@class,@year,@term,@tchid,@classid,@code,@user,";
//string value = level + "," + group + "," + year + "," + term +","+teacher+","+classid+","+subject+ "," + userid;
// bool insert = conn.InsertValue(sql, addvalue, value);
bool insert = conn.QueryExecuteNonQuery(sql);
conn.Close();
if (insert) { return true; }
else { return false; }
}
public static bool apppoveKm(string id)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "UPDATE KmPost SET KmPost_Status ='Y' WHERE KmPost_ID= '"+id+"'";
bool del = conn.QueryExecuteNonQuery(sql);
if (del)
{
return true;
}
else return false;
}
catch (Exception)
{
return false;
}
}
public static void insertEnrollStd(string codeStdRegis, string userid, string year)
{
ClassConnectDB conn = new ClassConnectDB();
/*** SELECT TOP 1000 [DetailTech_ID]
,[DetailTech_Level]
,[DetailTech_Class]
,[DetailTeach_Year]
,[DetailTech_Term]
,[Tch_ID]
,[ClassRoom_ID]
,[StructSub_Code]
,[Create_user]
,[CreateDate]
,[DetailTeach_Status]
FROM [WEBCSDB].[dbo].[DetailTech]
where [StructSub_Code]='0032001' and [DetailTech_Class]='1' and [DetailTeach_Status]='A'
*
* **/
string selectDchId = " SELECT [DetailTech_ID] FROM [DetailTech] where [StructSub_Code]='" + codeStdRegis + "' and [DetailTeach_Status]='A' and [DetailTech_Class] = ( select [Std_Group] from [Student] where [Std_Campus_Code]='" + userid + "')";
SqlDataReader drr = conn.SelectSqlDataReader(selectDchId);
string dchid = "";
while (drr.Read())
{
dchid = drr["DetailTech_ID"].ToString();
}
string sql = @"INSERT INTO EnrollIn (DetailTech_ID, Std_Campus_Code, Enroll_Status)
VALUES('" + dchid + "','" + userid + "','N')";
conn.QueryExecuteNonQuery(sql);
conn.Close();
}
public static bool closeComment(string commentid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = " delete from KmPost where KmComPost_ID='" + commentid + "'";
conn.QueryExecuteNonQuery(sql);
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
public static bool deleteKm(string id)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = " delete FROM [KmPost] where KmPost_ID='"+id+"'";
bool insert = conn.QueryExecuteNonQuery(sql);
conn.Close();
return insert;
}
catch (Exception)
{
return false;
}
}
public static bool cloasePost(string posid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string deleteCommentsql = " delete from CommentPost where Post_ID='" + posid + "'";
conn.QueryExecuteNonQuery(deleteCommentsql);
string deletePostSql = "delete from Post where Post_ID = '" + posid + "' ";
conn.QueryExecuteNonQuery(deletePostSql);
conn.Close();
return true;
}
catch (Exception) { return false; }
}
public static void updateDetailTeach(string detailTeach)
{
ClassConnectDB conn = new ClassConnectDB();
//string sql = " update [DetailTech] set [DetailTeach_Status]='N' where [DetailTech_ID]='" + detailTeach + "'";
string sql = " update [DetailEducationOfStudent] set [EduStdStatus]='N' where [DeEduStd]='" + detailTeach + "'";
bool re = conn.QueryExecuteNonQuery(sql);
conn.Close();
// return re;
}
public static object appoveStudentInclass(string id, string dchID,string status)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "update EnrollIn set Enroll_Status='" + status + "' where Std_Campus_Code='" + id + "' and DeEduStd='" + dchID + "'";
bool a = conn.QueryExecuteNonQuery(sql);
conn.Close();
return a;
}
public static bool insertGradeStart2(string detailTeachID, string grade, string userid, string usertype, string code)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO SchoolRecord
(DeEduStd, SchoolRecord_Grade, Std_Campus_Code, Create_User, Create_Type, Create_Date)
VALUES ('" + detailTeachID + "','" + grade + "','" + code + "','" + userid + "','" + usertype + "',getDate())";
bool insert = conn.QueryExecuteNonQuery(sql);
conn.Close();
if (insert) { return true; }
else { return false; };
}
public static bool deleteRoll(string detailTeach)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string deletePostSql = " DELETE FROM EnrollIn where Enroll_ID in ( SELECT Enroll_ID FROM EnrollIn WHERE DeEduStd='" + detailTeach + "' ) ";
conn.QueryExecuteNonQuery(deletePostSql);
conn.Close();
return true;
}
catch (Exception) { return false; }
}
public static bool deleteDetailTeach(string subcode, string tchID)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "DELETE FROM DetailTech WHERE StructSub_Code ='" + subcode + "' AND Tch_ID='" + tchID + "'";
bool del = conn.QueryExecuteNonQuery(sql);
conn.Close();
return del;
}
public static bool insertDetailTeachMdlSub(string code, string yearcurri, string level, string term)
{
ClassConnectDB conn = new ClassConnectDB();
string insert = @"INSERT INTO DetailTech
(DetailTech_Level, DetailTeach_Year, DetailTech_Term, StructSub_Code, DetailTeach_Status,[DetailTech_Class])
VALUES('" + level + "','" + yearcurri + "','" + term + "','" + code + "','u','1')";
bool ins = conn.QueryExecuteNonQuery(insert);
conn.Close();
return ins;
}
public static void selectDatatableCase3(string yearEdu, string yearCurrent, string level, string termStudy, DataTable dttt)
{
string yearPlan = DAL.PlanEducate.selectCuriYear(yearEdu); //ตรวจสอบว่าเรียนหลักสูตรไหน
DataTable dtDetailTeach = selectShowDetailTechInTerm(yearEdu, yearCurrent, termStudy); // ตรวจสอบว่าช่วงที่เรียนปี 1-ปี N
DataTable dtPlan = selectShowDetailTechInPlanCase3(dtDetailTeach, yearPlan, yearEdu, yearCurrent, termStudy, dttt);
ClassConnectDB conn = new ClassConnectDB();
//DataTable dt = new DataTable();
//dt.Columns.Add("no");
//dt.Columns.Add("subcode");
//dt.Columns.Add("subThai");
//dt.Columns.Add("credite");
//dt.Columns.Add("curi");
//int i = 1;
//if (dtPlan.Rows.Count > 0)
//{
// foreach (DataRow item in dtPlan.Rows)
// {
// dt.Rows.Add(i,item[0],item[1],item[2],item[3]);
// i++;
// }
//}
foreach (DataRow item in dtPlan.Rows)
{
string insert = @"INSERT INTO DetailTech
(DetailTech_Level, DetailTeach_Year, DetailTech_Term, StructSub_Code, DetailTeach_Status,[DetailTech_Class])
VALUES('" + level + "','" + yearCurrent + "','" + termStudy + "','" + item[0].ToString() + "','u','1')";
conn.QueryExecuteNonQuery(insert);
}
conn.Close();
// return dt;
}
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 bool updateDetailTeach(string ecuID, string code, string codeOld, string year)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
//string sql = "UPDATE DetailTech SET StructSub_Code='" + code + "' where DetailTech_ID='" + detailTeach + "'";
string sql = "UPDATE EducationOfStudents set StructSub_Code='" + code + "' where EduStdYearEdu=" + year + " and StructSub_Code='" + codeOld + "'";
string sql2 = "UPDATE EducationOfStudents SET StructSub_Code='" + codeOld + "' where EduStdID='" + ecuID + "'";
bool update = conn.QueryExecuteNonQuery(sql);
bool upd = conn.QueryExecuteNonQuery(sql2);
conn.Close();
return update;
}
catch (Exception ex)
{
return false;
}
}
public static bool deleteDetailTeach(string detailTeach)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string deletePostSql = " DELETE FROM DetailTech where DetailTech_ID = '" + detailTeach + "' ";
conn.QueryExecuteNonQuery(deletePostSql);
conn.Close();
return true;
}
catch (Exception) { return false; }
}