public static bool insertSubject(Entity.CurriculumInfo subject)
{
subcode=subject.StructSub_Code.ToString();
subNameTha=subject.StructSub_NameTha.ToString();
subNameEn=subject.StructSub_NameEn.ToString();
detail=subject.StructSub_Detail.ToString();
subCre=subject.StructSub_Credit.ToString();
curID=subject.Curri_Id.ToString();
ClassConnectDB conn = new ClassConnectDB();
//conn.TransStart();
try
{
string insertSubject = "INSERT INTO StructSubject(StructSub_Code, StructSub_NameTha, StructSub_NameEn, StructSub_Detail, StructSub_Credit, Curri_Id)VALUES(@code,@nameTha,@nameEn,@detail,@struCre,@curiID)";
string AddvalueSubject = "@code,@nameTha,@nameEn,@detail,@struCre,@curiID";
string value = subcode + "," + subNameTha + "," + subNameEn + "," + detail + "," + subCre + "," + curID;
conn.InsertValue(insertSubject,AddvalueSubject,value);
return true;
}
catch (Exception)
{
return true;
}
conn.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 bool insertInstructionMedia(string classID, string namefile, string type, string path, string des, string filesize)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO InstructionMedia
(InstMedia_Name, InstMedia_Type, InstMedia_Path, InstMedia_Description, Create_date, ClassRoom_ID,InstMedia_Size)
VALUES(@name,@type,@path,@des,getdate(),@classID,@filetype)";
string addvalue = "@name,@type,@path,@des,@classID,@filetype";
string value = namefile + "," + type + "," + path + "," + des + "," + classID + "," + filesize;
bool insert = conn.InsertValue(sql, addvalue, value);
if (insert)
{
return true;
}
else return false;
}
catch (Exception)
{
return false;
}
}
public static bool insertGrade(string codesubject, string codestd, string grade, string detailID)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string selectCheck = @"SELECT DetailTech.DetailTech_Term FROM EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
where DetailTech.DetailTech_ID='" + detailID + "'";
SqlDataReader drr = conn.SelectSqlDataReader(selectCheck);
if (!drr.HasRows)
{
string sqlSchoolRecord = @" INSERT INTO SchoolRecord
(SchoolRecord_Level, SchoolRecord_Ter, SchoolRecord_Year, Std_Campus_Code)
SELECT DetailTech.DetailTech_Level, DemtailTech.DetailTech_Term, DetailTech.DetailTeach_Year, EnrollIn.Std_Campus_Code
FROM EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
where DetailTech.DetailTech_ID=@deid ";
string Addvalue = "@deid";
string value = detailID;
conn.InsertValue(sqlSchoolRecord, Addvalue, value);
}
/*join enrool ; if not Enrolln your can't check*/
string sqlInserDetailTeach = @"INSERT INTO DetailResultStudent (StructSub_Code, DeStudent_Grade, SchoolRecord_ID)
VALUES (@code,@grade,(
SELECT SchoolRecord_ID FROM SchoolRecord where SchoolRecord_Year=
(SELECT DetailTech.DetailTeach_Year FROM EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID where DetailTech.DetailTech_ID=@detailID )
AND SchoolRecord_Term =
(SELECT DetailTech.DetailTech_Term FROM EnrollIn INNER JOIN DetailTech ON EnrollIn.DetailTech_ID = DetailTech.DetailTech_ID
where DetailTech.DetailTech_ID=@detailID ))) ";
string AddvalueDE = "@code,@grade,@detailID";
string valueDe = codesubject + "," + grade + "," + detailID;
conn.InsertValue(sqlInserDetailTeach, AddvalueDE, valueDe);
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
public static bool deleteStudentInclass(string id, string dchID)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sqlcheckEnrollInCheckname = @"SELECT CheckName.CheckName_ID FROM CheckName INNER JOIN
EnrollIn ON CheckName.Enroll_ID = EnrollIn.Enroll_ID WHERE(EnrollIn.DetailTech_ID = '" + dchID + "') AND (EnrollIn.Std_Campus_Code = '" + id + "')";
SqlDataReader drrcheckNameinroll = conn.SelectSqlDataReader(sqlcheckEnrollInCheckname);
if (drrcheckNameinroll.HasRows)
{
string deleteCheckName = @"DELETE FROM CheckName WHERE CheckName_ID in ( SELECT CheckName.CheckName_ID FROM CheckName INNER JOIN
EnrollIn ON CheckName.Enroll_ID = EnrollIn.Enroll_ID WHERE(EnrollIn.DetailTech_ID = @deid) AND (EnrollIn.Std_Campus_Code = @id))";
string addvalueDelete = "@deid,@id";
string valuedelete = dchID + "," + id;
conn.DeleteValue(deleteCheckName, addvalueDelete, valuedelete);
string sqlinsert = "DELETE FROM EnrollIn WHERE Std_Campus_Code=@id AND DetailTech_ID=@dchID";
string addvalue = "@id,@dchID";
string value = id + "," + dchID;
conn.InsertValue(sqlinsert, addvalue, value);
}
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
public static bool insertStudentInclass(string id, string dchID)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "SELECT DetailTech_ID FROM EnrollIn WHERE Std_Campus_Code='" + id + "' AND DetailTech_ID ='" + dchID + "'";
SqlDataReader ddr = conn.SelectSqlDataReader(sql);//chechk detailTeach has Roww
if (!ddr.HasRows)
{
string sqlinsert = "INSERT INTO EnrollIn ( DetailTech_ID, Std_Campus_Code) VALUES (@dchId,@stdCode)";
string addvalue = "@dchId,@stdCode";
string value = dchID + "," + id;
conn.InsertValue(sqlinsert, addvalue, value);
string sqlSelectCheckname = @"SELECT max(CheckName.CheckName_Num)As numCheck FROM EnrollIn INNER JOIN
CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID WHERE ([DetailTech_ID] = '" + dchID + "')";
string colum = "numCheck";
int countid = Convert.ToInt32(selectMaxIDValue(sqlSelectCheckname, colum));
for (int i = 1; i <= countid; i++)
{
string sqlinsertChechname = @"INSERT INTO CheckName (CheckName_Num,Create_date,Enroll_ID)VALUES(@num,(SELECT distinct CheckName.Create_date
FROM EnrollIn INNER JOIN CheckName ON EnrollIn.Enroll_ID = CheckName.Enroll_ID
WHERE EnrollIn.DetailTech_ID=@deid AND CheckName.CheckName_Num=@num),( select max(Enroll_ID) FROM EnrollIN))";
string addvalueinsert = "@num,@deid";
string valueinsert = i.ToString() + "," + dchID;
conn.InsertValue(sqlinsertChechname, addvalueinsert, valueinsert);
}
}
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
public static bool createDetailTeach(string year, string level, string group, string term, string showPlanId, string teacher, string checkCreateRoom, string createUser)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO DetailTech
(DetailTeach_Year,DetailTech_Level, DetailTech_Class, DetailTech_Term, ShowPlan_Id, Tch_ID, ClassRoom_ID, Create_user, CreateDate)
VALUES (@year,@level,@group,@term,@planId,@tchID,@classID,@user,getdate())";
string Addvalue = "@year,@level,@group,@term,@planId,@tchID,@classID,@user";
string value = year + "," + level + "," + group + "," + term + "," + showPlanId + "," + teacher + "," + checkCreateRoom + "," + createUser;
bool insert = conn.InsertValue(sql, Addvalue, value);
if (insert)
{
return true;
}
else return false;
}
catch (Exception)
{
return false;
}
}
public static string checkCreateRoom(string showPlanId, string teacherid)
{
try
{
string classid = "";
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT ClassRoom.ClassRoom_ID AS classid
FROM DetailTech INNER JOIN ClassRoom ON DetailTech.ClassRoom_ID = ClassRoom.ClassRoom_ID
WHERE DetailTech.ShowPlan_Id = '" + showPlanId + "' AND DetailTech.Tch_ID = '" + teacherid + "'";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.Read())
{
classid = drr["classid"].ToString();
}
else
{
string createRoom = @"INSERT INTO ClassRoom (ClassRoom_Name, Create_date) VALUES( (SELECT StructSubject.StructSub_NameTha
FROM ShowPlanEducate INNER JOIN StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code WHERE ShowPlanEducate.ShowPlan_Id=@planID),getdate())";
string Addvalue = "@planID";
string value = showPlanId;
bool insertRoom = conn.InsertValue(createRoom, Addvalue, value);
if (insertRoom)
{
string sqlselectClassMax = "SELECT MAX(ClassRoom_ID) AS idclass FROM ClassRoom";
SqlDataReader read = conn.SelectSqlDataReader(sqlselectClassMax);
if (read.Read())
{
classid = read["idclass"].ToString();
}
}
}
conn.Close();
return classid;
}
catch (Exception)
{
return "";
}
}
public static bool InsertStudentInclass(string code, string name, string group, string classID)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @" INSERT INTO EnrollIn ( DetailTech_ID, Std_Campus_Code) VALUES (
( SELECT DetailTech_ID FROM DetailTech WHERE (ClassRoom_ID = @classID) AND (DetailTech_Class = @group) ),
(SELECT Std_Campus_Code FROM Student WHERE (Std_Campus_Code = @codestd)))";
string Addvalue = "@classID,@group,@codestd";
string value = classID + "," + group + "," + code;
bool insert = conn.InsertValue(sql, Addvalue, value);
return insert;
}
catch (Exception)
{
return false;
}
}
public static bool insertSchoolRecord(string year, string level, string term, string codesubject, string resultGrade, string code, string userid, string userType)
{
try
{
string sql = @"INSERT INTO SchoolRecord
(SchoolRecord_Year, SchoolRecord_Level, SchoolRecord_Term, StructSub_Code, SchoolRecord_Grade, Std_Campus_Code, Create_User, Create_Type, Create_Date,
Update_User, Update_Type, Update_Date)
VALUES (@year,@level,@term,@subcode,@grade,@stdcode,@createuser,@createType,getDate(),@updUser,@updType,getDate())";
string Addvalue = "@year,@level,@term,@subcode,@grade,@stdcode,@createuser,@createType,@updUser,@updType";
string value = year + "," + level + "," + term + "," + codesubject + "," + resultGrade + "," + code + "," + userid + "," + userType + "," + userid + "," + userType;
ClassConnectDB conn = new ClassConnectDB();
bool inert = conn.InsertValue(sql, Addvalue, value);
if (inert)
{
return true;
}
else
{
return false;
}
}
catch (Exception)
{
return false;
}
}
public static bool updateInManageCurriculumPage(string updyear, string year)
{
try
{
string sql = "UPDATE Curriculum SET Curri_Year =@updyear Where Curri_Year=@year";
string Addvalue = "@year,@updyear";
string value = year+","+updyear;
ClassConnectDB conn = new ClassConnectDB();
conn.InsertValue(sql, Addvalue, value);
return true;
}
catch (Exception)
{
return false;
}
}
/// Manage Data in ManageCuluculum Page
///
public static bool insertYearInManageCurriculumPage(string year)
{
try
{
string sql = "INSERT INTO Curriculum (Curri_Year)VALUES(@year)";
string Addvalue = "@year";
string value =year;
ClassConnectDB conn = new ClassConnectDB();
conn.InsertValue(sql, Addvalue, value);
return true;
}
catch (Exception)
{
return false;
}
}
public static bool getInsertGridAddPlanEducate(string yearMod, string year, string semes, string code)
{
try
{
string sql = @"INSERT INTO ShowPlanEducate (ShowPlan_YearUpdate,ShowPlan_Year, ShowPlan_Semester, StructSub_Code)
VALUES(@yearMod,@year,@sem,@code)";
string Addvalue = "@yearMod,@year,@sem,@code";
string value = yearMod+","+ year + "," + semes+","+code;
ClassConnectDB conn = new ClassConnectDB();
conn.InsertValue(sql, Addvalue, value);
return true;
}
catch (Exception)
{
return false;
}
}
public static bool getInsertGridAddSubject(string code,string codepass)
{
try
{
string sql = "INSERT INTO FollowSubject(StructSub_Code, StructSub_CodePass)VALUES(@code,@codepass)";
string Addvalue = "@code,@codepass";
string value = code + "," + codepass;
ClassConnectDB conn = new ClassConnectDB();
conn.InsertValue(sql, Addvalue, value);
return true;
}
catch (Exception)
{
return false;
}
}
public static bool insertUserTeacherPageAdmin(Entity.Teacher teacher)
{
ClassConnectDB conn = new ClassConnectDB();
// string code = teacher.Tch_Campus_code.ToString();
string fname = teacher.Tch_FName.ToString();
string lname = teacher.Tch_LName.ToString();
string nickname = teacher.Tch_Nickname.ToString();
string sex = teacher.Tch_Sex.ToString();
//string birth = teacher.Tch_Birth.ToString();
string address = teacher.Tch_Address.ToString();
string tel = teacher.Tch_Tel.ToString();
string type = teacher.Tch_Type.ToString();
string email = teacher.Tch_email.ToString();
string fnameEn = teacher.Tch_FNameEn.ToString();
string lnameEn = teacher.Tch_LNameEn.ToString();
string username = teacher.Tch_username.ToString();
string password = teacher.Tch_password.ToString();
string typeTea = teacher.Tch_TypeInSch.ToString();
try
{
string sqlInsert = @"INSERT INTO Teacher
(Tch_FName, Tch_LName,Tch_FNameEn,Tch_LNameEn, Tch_Nickname, Tch_Sex, Tch_Address, Tch_Tel, Tch_Type, Tch_email,Tch_username, Tch_password,Tch_TypeInSch)
VALUES (@fname,@lname,@fnameEn,@lnameEn,@nickname,@sex,@address,@tel,@type,@email,@user,@pass,@typeSch)";
string Addvalue = "@fname,@lname,@fnameEn,@lnameEn,@nickname,@sex,@address,@tel,@type,@email,@user,@pass,@typeSch";
string value = fname + "," + lname + "," + fnameEn + "," + lnameEn + "," + nickname + "," + sex + "," + address + "," + tel + "," + type + "," + email + "," + username + "," + password+","+typeTea;
conn.InsertValue(sqlInsert, Addvalue, value);
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
public static bool checkNameStudent(string checkType, string code, string deid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sqlinsert = @"insert into CheckName(CheckName_Num,CheckName_Status,Create_date,Enroll_ID)
values( (select count(CheckName_Num)+1 from CheckName where Enroll_ID in (SELECT DISTINCT EnrollIn.Enroll_ID
FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code
WHERE (Student.Std_Campus_Code = @code)AND(DeEduStd=@deid) ) )
, @type ,getDate() ,
(SELECT DISTINCT EnrollIn.Enroll_ID
FROM EnrollIn INNER JOIN Student ON EnrollIn.Std_Campus_Code = Student.Std_Campus_Code
WHERE (Student.Std_Campus_Code = @code)AND(DeEduStd=@deid)))";
string Addvalue = "@code,@type,@deid";
string value = code + "," + checkType + "," + deid;
conn.InsertValue(sqlinsert, Addvalue, value);
return true;
}
catch (Exception)
{
return false;
}
}
public static bool insertGradeStart(string year, string level, string term, string code, string resultGrade, string userid, string usertype)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"INSERT INTO SchoolRecord
(SchoolRecord_Year, SchoolRecord_Level, SchoolRecord_Term, StructSub_Code, SchoolRecord_Grade, Std_Campus_Code, Create_User, Create_Type, Create_Date)
VALUES (@year,@level,@term,@code,@grade,@stdcode,@stdcode,@createtpe,getDate())";
string addvalue = "@year,@level,@term,@code,@grade,@stdcode,@createtpe";
string value = year + "," + level + "," + term + "," + code + "," + resultGrade + "," + userid + "," + usertype;
bool insert = conn.InsertValue(sql, addvalue, value);
conn.Close();
if (insert) { return true; }
else { return false; };
}
public static bool insertUserStudentPageAdmin(Entity.Student student)
{
ClassConnectDB conn = new ClassConnectDB();
string code = student.Std_Campus_Code.ToString();
string fname = student.Std_FName.ToString();
string lname = student.Std_LName.ToString();
string nickname = student.Std_Nickname.ToString();
string sex = student.Std_Sex.ToString();
string birth = student.Std_Birth.ToString();
string address = student.Std_Address.ToString();
string tel = student.Std_Tel.ToString();
string type = student.Std_Type.ToString();
string email = student.Std_email.ToString();
string username = student.Std_Campus_Code.ToString();
string password = student.Std_Campus_Code.ToString();
string group = student.Std_Group.ToString();
string year = student.Std_YearEducate.ToString();
try
{
string sqlInsert = @"INSERT INTO Student
(Std_FName, Std_LName, Std_Nickname, Std_Campus_Code, Std_Sex, Std_Birth, Std_Address, Std_Tel, Std_Type, Std_email,Std_Username, Std_Password,Std_Group, Std_YearEducate)
VALUES (@fname,@lname,@nickname,@code,@sex,@birth,@address,@tel,@type,@email,@user,@pass,@group,@year)";
string Addvalue = "@fname,@lname,@nickname,@code,@sex,@birth,@address,@tel,@type,@email,@user,@pass,@group,@year";
string value = fname + "," + lname + "," + nickname + "," + code + "," + sex + "," + birth + "," + address + "," + tel + "," + type + "," + email + "," + username + "," + password + "," + group + "," + year;
conn.InsertValue(sqlInsert, Addvalue, value);
conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}