public static DataTable getCommentForPost(int thePost)
{
try
{
string sql = @"SELECT TOP 1 [KmComPost_ID]
,[KmPost_ID]
,[ComPost_Detail]
,REPLACE(CONVERT(VARCHAR(11), Create_date, 106), ' ', '-') AS datepostkm
,CONVERT(VARCHAR(12),Create_time ,114) as timepostkm
,[Create_user_id]
,[Create_user_type]
FROM KmCommentPost where KmPost_ID='" + thePost + "'order by [Create_date] asc ";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
catch (Exception)
{
return null;
}
}
public static int checkCredit(string year, string level, string term, string group)
{
try
{
/****************************
* function ตรวจสอบหน่วยกิตทั้งหมด
*
* **************************/
int creditAll = 0;
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT sum (CONVERT(int,(SUBSTRING([StructSub_Credit], 1 , 1 )) ))as credit
FROM [WEBCSDB].[dbo].[StructSubject]
where StructSub_Code in (
SELECT TOP 1000 [StructSub_Code]
FROM [DetailTech]
where [DetailTech_Level]='" + level + "' and [DetailTech_Class]='" + group + "' and [DetailTeach_Year]='" + year + "' and [DetailTech_Term]='" + term + "' )";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.Read())
{
creditAll = Convert.ToInt32(drr["credit"].ToString());
}
return creditAll;
}
catch (Exception)
{
return 0;
}
}
public static DataTable selectTestCode()
{
ClassConnectDB conn = new ClassConnectDB();
string sql = " SELECT * FROM [WEBCSDB].[dbo].[Teacher] ";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
public static System.Data.DataTable manateYear()
{
string sql = "select YEAR(getdate())+542 as passYear,YEAR(getdate())+543 as curYear,YEAR(getdate())+544 as fuYear";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
public static DataTable selectCommentPost(int postID)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT Top 1 [ComPost_ID]
,[Post_ID]
,[ComPost_Detail]
,[Create_date]
,[Create_time]
,[Create_user_id]
,[Create_user_type]
FROM [CommentPost]
where Post_ID = " + postID + " ORDER BY Create_date desc , Create_time desc";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
public static System.Data.DataTable selectShowAllPlanTree(string yearCurriEdu)
{
DataTable dt = new DataTable();
try
{
string sql = @"SELECT ShowPlanEducate.ShowPlan_Year, ShowPlanEducate.ShowPlan_Semester, ShowPlanEducate.StructSub_Code, StructSubject.StructSub_NameTha
FROM ShowPlanEducate INNER JOIN
StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
where ShowPlanEducate.showplan_yearupdate='" + yearCurriEdu + "'";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
dt.Load(drr);
return dt;
}
catch (Exception)
{
return null;
}
}
public static string selectCuriYear(string yearedu)
{
string year = "";
try
{
string sql = @"SELECT max( [Curri_Year]) as yearEducate
FROM [Curriculum]
where curri_year <= '" + yearedu + "' ";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.Read())
{
year = drr["yearEducate"].ToString();
}
conn.Close();
return year;
}
catch (Exception)
{
return null;
}
}
public static System.Data.DataTable selectUserPost(string userid)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT [Post_ID]
,[Post_Detail]
,[Create_date]
,[Create_time]
,[Create_user_id]
,[Create_user_type]
FROM [Post]
where Create_user_id = '" + userid + "' ORDER BY Create_date desc , Create_time desc ";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
catch (Exception ex) {
return null;
}
}
public static DataTable selectShowStudentSuject(string code, string user)
{
try
{
string sql = @"SELECT SchoolRecord.SchoolRecord_Level, SchoolRecord.SchoolRecord_Term, SchoolRecord.StructSub_Code, StructSubject.StructSub_NameTha,
SchoolRecord.SchoolRecord_Grade FROM SchoolRecord INNER JOIN
StructSubject ON SchoolRecord.StructSub_Code = StructSubject.StructSub_Code
where SchoolRecord.Std_Campus_Code='" + user + "' AND SchoolRecord.StructSub_Code='" + code + "' ";
ClassConnectDB conn = new ClassConnectDB();
DataTable dt = new DataTable();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
dt.Load(drr);
conn.Close();
return dt;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
//return null;
throw;
}
}
public static DataTable swapSubject(string yearEdu, int year, int level, int term, string code, string namethai)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "select distinct StructSub_Code ,EduStdYearCurri from EducationOfStudents where EduStdYearEdu='" + yearEdu + "' ";
for (int i = 1; i <= level; i++)
{
for (int j = 1; j <= term; j++)
{
sql += " and ( EduStdLevel=" + i + " and EduStdTerm = " + j + ") ";
}
}
DataTable dt = new DataTable();
SqlDataReader dr = conn.SelectSqlDataReader(sql);
dt.Load(dr);
string sql2 = @" select ROW_NUMBER() over (order by StructSub_Code ) as no,
StructSub_Code as subcode, StructSub_NameTha as subThai , StructSubject.Curri_Id as curi , StructSub_Credit as credite
FROM StructSubject INNER JOIN Curriculum ON StructSubject.Curri_Id = Curriculum.Curri_Id where 1=1 ";
int m = 1;
foreach (DataRow item in dt.Rows)
{
if (m == 1)
{
sql2 += " and Curriculum.Curri_Year='" + item[1] + "' ";
}
sql2 += " and StructSubject.StructSub_Code <> '" + item[0] + "' ";
}
if (code != "")
{
sql2 += " AND StructSubject.StructSub_Code = '" + code + "'";
}
if (namethai != "")
{
sql2 += " AND StructSubject.StructSub_NameTha LIKE '%" + namethai + "%'";
}
DataTable dtt = new DataTable();
SqlDataReader drr = conn.SelectSqlDataReader(sql2);
dtt.Load(drr);
return dtt;
}
public static bool checkSubjectCurri(string code, string cur)
{
string selec = " SELECT [Curri_Id] FROM [WEBCSDB].[dbo].[StructSubject] where [StructSub_Code]='" + code + "' and [Curri_Id]='" + cur + "' ";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(selec);
string curri = "";
//if (drr.Read())
//{
// curri = drr["Curri_Id"].ToString();
//}
if (drr.HasRows)
{
return true;
}
else return false;
}
public static DataTable selectDtShowGrideViewCase3(string year, string level, string term)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT StructSubject.StructSub_Code, StructSubject.StructSub_NameTha, StructSubject.StructSub_Credit, StructSubject.Curri_Id,DetailTech_ID
FROM DetailTech INNER JOIN
StructSubject ON DetailTech.StructSub_Code = StructSubject.StructSub_Code
where DetailTech.DetailTeach_Year='" + year + "' AND DetailTech.DetailTech_Level='" + level + "' AND DetailTech.DetailTech_Term='" + term + "' AND DetailTech.DetailTeach_Status='u' ";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dtt = new DataTable();
dtt.Load(drr);
DataTable dt = new DataTable();
dt.Columns.Add("no");
dt.Columns.Add("subcode");
dt.Columns.Add("subThai");
dt.Columns.Add("credite");
dt.Columns.Add("curi");
dt.Columns.Add("dchid");
int i = 1;
if (dtt.Rows.Count > 0)
{
foreach (DataRow item in dtt.Rows)
{
dt.Rows.Add(i, item[0], item[1], item[2], item[3], item[4]);
i++;
}
}
return dt;
}
public static DataTable selectSubjectWithPlan(string yearEdu, int level, int term)
{
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");
string sql = @"
SELECT StructSubject.StructSub_Code, StructSubject.StructSub_NameTha, StructSubject.StructSub_Credit, StructSubject.Curri_Id
FROM ShowPlanEducate INNER JOIN
StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
where ShowPlanEducate.ShowPlan_YearUpdate='" + yearEdu + "' and ShowPlanEducate.ShowPlan_Year='" + level + "' and ShowPlanEducate.ShowPlan_Semester='" + term + "'";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dtt = new DataTable();
dtt.Load(drr);
int i = 1;
foreach (DataRow item in dtt.Rows)
{
dt.Rows.Add(i, item[0], item[1], item[2], item[3]);
i++;
}
return dt;
}
public static DataTable selectShowSubjectShowpopup(string year, string code, string nameThai)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "Select * FROM [StructSubject] where 1=1 ";
if (!string.IsNullOrEmpty(code))
{
sql += " and [StructSub_Code]='" + code + "'";
}
if (!string.IsNullOrEmpty(nameThai))
{
sql += " and [StructSub_NameTha] LIKE '%" + nameThai + "%'";
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
public static DataTable searchShowDetailTeach(string year, string level, string term)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT (DetailTech.DetailTeach_Year) as DetailTeach_Year, (DetailTech.DetailTech_Level) as DetailTech_Level , (DetailTech.DetailTech_Term) as DetailTech_Term, ( StructSubject.StructSub_Code) as StructSub_Code,( StructSubject.StructSub_NameTha) as StructSub_NameTha,
(StructSubject.StructSub_NameEn ) as StructSub_NameEn , (StructSubject.StructSub_Credit ) as StructSub_Credit FROM DetailTech INNER JOIN
StructSubject ON DetailTech.StructSub_Code = StructSubject.StructSub_Code WHERE 1=1 ";
if (!year.Equals("N"))
{
sql += " AND [DetailTeach_Year]='" + year + "' ";
}
if (!level.Equals("0"))
{
sql += " AND [DetailTech_Level]= '" + level + "'";
}
if (!term.Equals("0"))
{
sql += " AND [DetailTech_Term] ='" + term + "'";
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
private static DataTable selectDetailSubject(string subject, DataTable dttsub, DataTable curriDt)
{
try
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "SELECT StructSub_Code, StructSub_NameTha, StructSub_Credit, Curri_Id FROM StructSubject WHERE StructSub_Code='" + subject + "'";
//foreach (DataRow rrr in dttsub.Rows)
//{
// sql += " AND StructSub_Code = '" + rrr[0].ToString() + "' ";
//}
//foreach (DataRow curri in curriDt.Rows)
//{
// int curriuser = Convert.ToInt32(curri[5].ToString());
// int curriAll = Convert.ToInt32(curri[4].ToString());
// if (curriuser >= curriAll)
// {
// sql += " AND Curri_Id <> " + curri[0].ToString();
// }
//}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
catch (Exception)
{
return null;
}
}
public static bool checkBtnOpenNewEducationPage(string year)
{
string sql = @" select * from EducationOfStudents where EduStdYearEdu='" + year + "'";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
bool check;
if (drr.HasRows)
{
check = true;
}
else { check = false; }
drr.Close();
conn.Close();
return check;
}
public static DataTable selectDtShowGrideViewOpenSubjec(string year, string level, string term)
{
string sql = @" SELECT ROW_NUMBER()
OVER (ORDER BY edu.EduStdID) AS no,stu.StructSub_Code as subcode, stu.StructSub_NameTha as subThai, stu.StructSub_Credit as credite, stu.Curri_Id as curi, edu.EduStdID as dchid
FROM EducationOfStudents edu INNER JOIN
StructSubject stu ON edu.StructSub_Code = stu.StructSub_Code
where edu.EduStdYearEdu='" + year + "' AND edu.EduStdLevel=" + level + " AND edu.EduStdTerm=" + term + "";
ClassConnectDB conn = new ClassConnectDB();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
conn.Close();
return dt;
}
public static DataTable selectShowGrideAddGradeStart(string year)
{
try
{
DateTime datetime = DateTime.Now;
int yearCurrent = (datetime.Year + 543);
int yearEdu = Convert.ToInt32(year);
ClassConnectDB conn = new ClassConnectDB();
string sql = @"SELECT DetailTech_ID, DetailTech.DetailTeach_Year, DetailTech.DetailTech_Level, DetailTech.DetailTech_Term, StructSubject.StructSub_Code, StructSubject.StructSub_NameTha
FROM StructSubject INNER JOIN DetailTech ON StructSubject.StructSub_Code = DetailTech.StructSub_Code WHERE DetailTeach_Status='N' ";
int count = 1;
for (int i = yearEdu; i < yearCurrent; i++)
{
if (count == 1)
{
sql += " AND (DetailTech.DetailTeach_Year='" + i + "' and DetailTech_Class='1' and [DetailTech_Level]='" + count + "' ) ";
}
else
{
sql += " OR (DetailTech.DetailTeach_Year='" + i + "' and DetailTech_Class='1' and [DetailTech_Level]='" + count + "') ";
}
count++;
}
sql += " order by DetailTech.DetailTeach_Year ";
DataTable dtt = new DataTable();
SqlDataReader drrread = conn.SelectSqlDataReader(sql);
dtt.Load(drrread);
conn.Close();
return dtt;
}
catch (Exception)
{
return null;
}
}
public static string checkTerm(string yearEdu)
{
string term = "";
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT max ([DetailTech_Term]) as term
FROM [DetailTech]
where DetailTeach_Year = " + yearEdu + "";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.Read())
{
term = drr["term"].ToString();
}
conn.Close();
return term;
}
public static bool checkDetailTeach()
{
ClassConnectDB conn = new ClassConnectDB();
string sql = "SELECT DetailTech_ID FROM DetailTech where DetailTeach_Status='A' ";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.HasRows)
{
return false;
}
else
return true;
}
public static DataTable selectShowDetailTechInTerm(string yearEdu, string yearcurri, string termStudy)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT [StructSub_Code]
FROM [DetailTech]
where 1=1 ";
int yearStart = Convert.ToInt32(yearEdu);
int yearEnd = Convert.ToInt32(yearcurri);
int level = 1;
for (int i = yearStart; i <= yearEnd; i++)
{
if (level == 1)
{
sql += " AND (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + "and DetailTech_Class='1' )";
}
else
{
sql += " OR (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + " and DetailTech_Class='1' )";
}
level++;
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
public static bool checkvalueDoubleShowgride(string ye, string level, string term, string group, string subject, string userid, string teacher)
{
/****
*
* ตรวจสอบข้อมูลก่อนจะเพิ่มเข้ารายละเอียดการสอนว่ามี ข้อมูลหรือไม่
*
* ***************/
string year = (Convert.ToInt32(ye) + 543).ToString();
ClassConnectDB conn = new ClassConnectDB();
string sql = @" select * FROM [DetailTech]
where DetailTeach_Year='" + year + "' and DetailTech_Level='" + level + "' and DetailTech_Term='" + term + "' and DetailTech_Class='" + group + "' and StructSub_Code ='" + subject + "' and DetailTeach_Status='A' ";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.HasRows)
{
conn.Close();
return false;
}
else
{
conn.Close();
return true;
}
}
public static DataTable selectShowDetailTechInTerm1111(string yearEdu)
{
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT [StructSub_Code]
FROM [DetailTech]
where Year_code = '" + yearEdu + "'";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
public static DataTable selectShowSwap1(string yearEdu, string year, string level, string term, string curri)
{
ClassConnectDB conn = new ClassConnectDB();
string yearPlan = DAL.PlanEducate.selectCuriYear(yearEdu); //ตรวจสอบว่าเรียนหลักสูตรไหน
DataTable dttt = new DataTable();
dttt.Columns.Add("code");
int lev = Convert.ToInt32(level);
for (int i = 1; i <= lev; i++)
{
DataTable dtDetailTeach = selectShowDetailTechInTerm(yearEdu, year, i.ToString()); // ตรวจสอบว่าช่วงที่เรียนปี 1-ปี N
foreach (DataRow rr in dtDetailTeach.Rows)
{
dttt.Rows.Add(rr[0].ToString());
}
}
string sql = @"SELECT StructSubject.StructSub_Code as code, StructSubject.StructSub_NameTha as nameTha, StructSubject.StructSub_Credit as credit
FROM Curriculum INNER JOIN StructSubject ON Curriculum.Curri_Id = StructSubject.Curri_Id
where Curriculum.Curri_Id='" + curri + "' ";
foreach (DataRow item in dttt.Rows)
{
sql += " and StructSubject.StructSub_Code <> '" + item[0].ToString() + "' ";
}
DataTable dtt = new DataTable();
SqlDataReader drr = conn.SelectSqlDataReader(sql);
dtt.Load(drr);
return dtt;
}
public static DataTable selectShowDetailTechInPlan(DataTable dtDetailTeach, string yearPlan, string yearEdu, string yearCurrent, string term, DataTable dtcurri)
{
ClassConnectDB conn = new ClassConnectDB();
int yearStart = Convert.ToInt32(yearEdu);
int yearEnd = Convert.ToInt32(yearCurrent);
int year = 0;
int range = yearEnd - yearStart;
if (range <= 0)
{
year = 1;
}
else
{
year = range;
}
string sql = @" SELECT distinct ShowPlanEducate.StructSub_Code
FROM ShowPlanEducate INNER JOIN
StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
where ( ShowPlan_YearUpdate='" + yearPlan + "' and ( ShowPlan_Year BETWEEN 1 AND " + year + " )";// and [ShowPlan_Semester]="+term+"";
if (year > 0)
{
// ตรวจสอบเทอมในเทเบิลแผนการศึกษา
int y = year + 1;
if (term.Equals("1"))
{
sql += " or ( ShowPlan_YearUpdate='" + yearPlan + "' and ShowPlan_Year = " + y + " and ShowPlan_Semester='1' ) )";
}
else
{
sql += " or ( ShowPlan_YearUpdate='" + yearPlan + "' and ShowPlan_Year = " + y + " and ( ShowPlan_Semester BETWEEN 1 AND " + term + " )))";
}
}
if (dtDetailTeach.Rows.Count > 0)
{
foreach (DataRow rows in dtDetailTeach.Rows)
{
sql += " AND ShowPlanEducate.StructSub_Code <> '" + rows[0] + "'";
}
}
foreach (DataRow curri in dtcurri.Rows)
{
int curriuser = Convert.ToInt32(curri[5].ToString());
int curriAll = Convert.ToInt32(curri[4].ToString());
if (curriuser >= curriAll)
{
sql += " AND StructSubject.Curri_Id <> " + curri[0].ToString();
}
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
public static DataTable selectShowDetailTechInPlanCase3(DataTable dtDetailTeach, string yearPlan, string yearEdu, string yearCurrent, string term, DataTable dtcurri)
{
ClassConnectDB conn = new ClassConnectDB();
int yearStart = Convert.ToInt32(yearEdu);
int yearEnd = Convert.ToInt32(yearCurrent);
int year = 0;
int range = yearEnd - yearStart;
if (range <= 0)
{
year = 1;
}
else
{
year = range + 1;
}
string sql = @" Select distinct ShowPlanEducate.StructSub_Code ,StructSub_NameTha,StructSub_Credit,Curri_Id
FROM ShowPlanEducate INNER JOIN
StructSubject ON ShowPlanEducate.StructSub_Code = StructSubject.StructSub_Code
where ( ShowPlan_YearUpdate='" + yearPlan + "' and ShowPlan_Year = " + year + " and ShowPlan_Semester='" + term + "' )";
if (dtDetailTeach.Rows.Count > 0)
{
foreach (DataRow rows in dtDetailTeach.Rows)
{
sql += " AND ShowPlanEducate.StructSub_Code <> '" + rows[0] + "'";
}
}
foreach (DataRow curri in dtcurri.Rows)
{
int curriuser = Convert.ToInt32(curri[5].ToString());
int curriAll = Convert.ToInt32(curri[4].ToString());
if (curriuser >= curriAll)
{
sql += " AND StructSubject.Curri_Id <> " + curri[0].ToString();
}
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dt = new DataTable();
dt.Load(drr);
return dt;
}
private static DataTable selectSubjectXY(string yearEdu, string yearcuri, string code, DataTable dtDetailTeach, DataTable checkCurri)
{
/*****************ตรวจสอบตัวเป็นรายวิชา xxxx **************************/
DataTable dtt = new DataTable();
ClassConnectDB conn = new ClassConnectDB();
string sql = "SELECT Curri_Id FROM StructSubject where StructSub_Code='" + code + "'";
SqlDataReader drr = conn.SelectSqlDataReader(sql);
if (drr.Read())
{
string curID = drr["Curri_Id"].ToString();
bool check = countDtch(yearEdu, yearcuri, curID);
if (check)
{
/*******************ถ้ารายวิชานั้นครบ **********************/
return null;
}
else
{
string sqlselect = @" SELECT StructSubject.StructSub_Code, StructSubject.StructSub_NameTha, StructSubject.StructSub_Credit, StructSubject.Curri_Id
FROM Curriculum INNER JOIN StructSubject ON Curriculum.Curri_Id = StructSubject.Curri_Id INNER JOIN
DetailTech ON StructSubject.StructSub_Code = DetailTech.StructSub_Code
where StructSubject.Curri_Id='" + curID + "' ";
foreach (DataRow rrr in dtDetailTeach.Rows)
{
sqlselect += " AND StructSubject.StructSub_Code <> '" + rrr[0].ToString() + "' ";
}
foreach (DataRow curri in checkCurri.Rows)
{
int curriuser = Convert.ToInt32(curri[5].ToString());
int curriAll = Convert.ToInt32(curri[4].ToString());
if (curriuser >= curriAll)
{
sqlselect += " AND StructSubject.Curri_Id <> " + curri[0].ToString();
}
}
////int yearStart = Convert.ToInt32(yearEdu);
////int yearEnd = Convert.ToInt32(yearcuri);
////int level = 1;
////for (int i = yearStart; i <= yearEnd; i++)
////{
//// if (level == 1)
//// {
//// sqlselect += " AND (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + " and DetailTech_Class='1' )";
//// }
//// else
//// {
//// sqlselect += " OR (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + " and DetailTech_Class='1' )";
//// }
//// level++;
////}
SqlDataReader drrselect = conn.SelectSqlDataReader(sqlselect);
dtt.Load(drrselect);
}
}
return dtt;
}
public static DataTable selectshowSwap(string yearEdu, int year, int level, int term, DataTable dtt, string curr)
{
string yearPlan = DAL.PlanEducate.selectCuriYear(yearEdu); //ตรวจสอบว่าเรียนหลักสูตรไหน
DataTable dtDetailTeach = selectShowDetailTechInTerm(yearEdu, year.ToString(), term.ToString()); // ตรวจสอบว่าช่วงที่เรียนปี 1-ปี N
ClassConnectDB conn = new ClassConnectDB();
string sql = "SELECT StructSub_Code, StructSub_NameTha, StructSub_Credit FROM StructSubject where Curri_Id='" + curr + "'";
foreach (DataRow rrsubject in dtDetailTeach.Rows)
{
sql += " AND StructSub_Code <> '" + rrsubject[0].ToString() + "' ";
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
DataTable dtselect = new DataTable();
dtselect.Load(drr);
DataTable dt = new DataTable();
dt.Columns.Add("no");
dt.Columns.Add("subcode");
dt.Columns.Add("subThai");
dt.Columns.Add("credite");
int i = 1;
if (dtselect.Rows.Count > 0)
{
foreach (DataRow item in dtselect.Rows)
{
string code = item[0].ToString();
if (!code.Substring(0, 1).Equals("x"))
{
dt.Rows.Add(i, item[0], item[1], item[2]);
}
i++;
}
}
conn.Close();
return dt;
}
public static bool countDtch(string yearEdu, string yearcurri, string curID)
{
/*************** ตรวจสอบจำนวนหน่วยกิตในระบบ ***********************/
ClassConnectDB conn = new ClassConnectDB();
string sql = @" SELECT (SUM( CONVERT(int, substring(StructSubject.StructSub_Credit,0,2)) )) as ccount
FROM DetailTech INNER JOIN
StructSubject ON DetailTech.StructSub_Code = StructSubject.StructSub_Code
where (Curri_Id='" + curID + "') ";
int yearStart = Convert.ToInt32(yearEdu);
int yearEnd = Convert.ToInt32(yearcurri);
int level = 1;
for (int i = yearStart; i <= yearEnd; i++)
{
if (level == 1)
{
sql += " AND (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + " and DetailTech_Class='1' )";
}
else
{
sql += " OR (DetailTeach_Year = " + i + " and DetailTech_Level=" + level + " and DetailTech_Class='1' )";
}
level++;
}
SqlDataReader drr = conn.SelectSqlDataReader(sql);
int countA = 0;
if (drr.Read())
{
string value = drr["ccount"].ToString();
if (value.Length > 0)
{
countA = Convert.ToInt32(value);
}
}
int countB = 0;
string sqlAllcredit = "SELECT [Curri_Credit] FROM [WEBCSDB].[dbo].[Curriculum] where [Curri_Id]='" + curID + "'";
SqlDataReader drrr = conn.SelectSqlDataReader(sqlAllcredit);
if (drrr.Read())
{
countB = Convert.ToInt32(drrr["Curri_Credit"].ToString());
}
if (countA >= countB) { return true; }
else return false;
}