/// <summary>
/// 取出所有ORCS_GroupClassification資料
/// </summary>
/// <returns></returns>
public static DataTable ORCS_GroupClassification_SELECT_All()
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_GroupClassification;
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 根據iGroupNodeClassificationID取出ORCS_GroupClassification資料
/// </summary>
/// <param name="iGroupNodeClassificationName">節點類別名稱</param>
/// <returns></returns>
public static DataTable ORCS_GroupClassification_SELECT_By_GroupNodeClassificationName(string iGroupNodeClassificationName)
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_GroupClassification + " WHERE " + TF_GroupClassification_cGroupNodeClassificationName + " ='" + iGroupNodeClassificationName + "'";
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 根據ParentNodeID取出ORCS_Group資料表中的ChildNode
/// </summary>
/// <param name="iParentNodeID">父節點ID</param>
/// <returns></returns>
public static DataTable ORCS_Group_SELECT_All_By_ParentNodeID(string iParentNodeID)
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_Group + " WHERE " + TF_Group_ParentNodeID + " ='" + iParentNodeID + "'";
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 根據課程ID取出小組資料
/// </summary>
/// <param name="strClassGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_TempGroup_SELECT_By_cClassGroupID(string strClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_TempGroup + " WHERE " + TF_ORCS_TempGroup_ClassGroupID + " ='" + strClassGroupID + "'";
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 取出所有課程資料
/// </summary>
/// <returns></returns>
public static DataTable ORCS_TempGroup_SELECT_All()
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_TempGroup;
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 增加上課初始時間記錄
/// </summary>
/// <param name="iClassGroupID"></param>
/// <param name="cStartTime"></param>
/// <returns></returns>
public static int addInintialClassTimeRecord(object iClassGroupID, object cStartTime)
{
clsORCSDB ORCSDB = new clsORCSDB();
//取得該堂課最新一筆記錄
DataTable dtClassTimeRecord = getLastClassTimeRecord(iClassGroupID);
//如果沒有上課記錄代表第一次上課
if (dtClassTimeRecord.Rows.Count == 0)
{
string strSQL = "INSERT INTO ORCS_ClassTimeRecord(iClassGroupID,cStartTime,iClassTime,bIsReRollCall) VALUES('" + iClassGroupID + "','" + cStartTime + "','" + "1" + "','" + "0" + "')";
try
{
ORCSDB.ExecuteNonQuery(strSQL);
}
catch
{
return(-1);
}
}
else //以最後一堂課的堂次再加1做為新的堂次
{
string strSQL = "INSERT INTO ORCS_ClassTimeRecord(iClassGroupID,cStartTime,iClassTime,bIsReRollCall) VALUES('" + iClassGroupID + "','" + cStartTime + "','" + (int.Parse(dtClassTimeRecord.Rows[0]["iClassTime"].ToString()) + 1) + "','" + "0" + "')";
try
{
ORCSDB.ExecuteNonQuery(strSQL);
}
catch
{
return(-1);
}
}
return(0);
}
//檢查ORCS是否為上課狀態並回傳課程ID
protected string CheckClassState()
{
clsORCSDB myDb = new clsORCSDB(); //呼叫ORCS資料庫
string strClassID = ""; //定義正在上課的課程ID
//抓取使用者的課程ID
string strSQL = "SELECT * FROM " + clsGroup.TB_ORCS_MemberCourseTeacher + " WHERE cUserID = '" + usi.UserID + "'";
DataTable dtGroupMember = new DataTable();
dtGroupMember = myDb.GetDataSet(strSQL).Tables[0];
if (dtGroupMember.Rows.Count > 0)
{
//抓取正在上課的課程ID
foreach (DataRow drGroupMember in dtGroupMember.Rows)
{
strSQL = "SELECT * FROM ORCS_SystemControl WHERE iClassGroupID = '" + drGroupMember["iGroupID"].ToString() + "' AND cSysControlName = 'SystemControl'";
DataTable dtSystemControl = new DataTable();
dtSystemControl = myDb.GetDataSet(strSQL).Tables[0];
if (dtSystemControl.Rows.Count > 0)
{
if (dtSystemControl.Rows[0]["iSysControlParam"].ToString() != "0") //判斷該課程是否上課("0":非上課,"1":上課,"2":上課遲到)
{
strClassID = dtSystemControl.Rows[0]["iClassGroupID"].ToString();
}
}
}
}
return(strClassID);//回傳課程ID
}
public static DataTable ORCS_SchoolGroup_SELECT_By_cSchoolGroupName(string strGroupName)
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_SchoolGroup + " WHERE " + TF_ORCS_SchoolGroup_SchoolGroupName + " ='" + strGroupName + "'";
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable ORCS_ClassGroup_SELECT_By_cClassGroupName_cSchoolGroupID(string strCourseGroupName, string strSchoolGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
string strSQL = "SELECT * FROM " + TB_ORCS_ClassGroup + " WHERE " + TF_ORCS_ClassGroup_ClassGroupName + " ='" + strCourseGroupName +
"' AND " + TF_ORCS_ClassGroup_SchoolGroupID + " ='" + strSchoolGroupID + "'";
return(ORCSDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 根據UserID和Authority取出使用者資料
/// </summary>
/// <param name="cUserID"></param>
/// <param name="cAuthority"></param>
/// <returns></returns>
public static DataTable ORCS_User_SELECT_ID_Name_by_UserID_Authority(object cUserID, object cAuthority)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtUserInfo = new DataTable();
string strSQL = "SELECT cUserID, cUserName FROM ORCS_User WHERE cUserID LIKE '" + cUserID + "' AND cAuthority LIKE '" + cAuthority + "'";
dtUserInfo = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtUserInfo);
}
/// <summary>
/// 根據ClassGroupID和UserID取得學生參與課程最後一次上課記錄
/// </summary>
/// <param name="iClassGroupID"></param>
/// <returns></returns>
public static DataTable getLastAttendanceRecord(object iClassGroupID, object cUserID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtStudentAttRecord = new DataTable();
string strSQL = "SELECT * FROM ORCS_StudentAttendanceRecord WHERE iClassGroupID = '" + iClassGroupID + "' AND cUserID ='" + cUserID + "' ORDER BY cAttendanceTime DESC";
dtStudentAttRecord = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtStudentAttRecord);
}
/// <summary>
/// 根據ClassGroupID取出單一歷史出席狀態資料
/// </summary>
/// <param name="iClassGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_StudentAttendanceHistory_SELECT_by_DISTINCT_GroupID(object iClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtStudentAttHis = new DataTable();
string strSQL = "SELECT DISTINCT cAttendanceID FROM ORCS_StudentAttendanceHistory WHERE iClassGroupID LIKE '" + iClassGroupID + "' ORDER BY cAttendanceID ASC ";
dtStudentAttHis = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtStudentAttHis);
}
/// <summary>
/// 根據UserID和GroupID取出使用者出席狀態資料
/// </summary>
/// <param name="cUserID"></param>
/// <param name="iClassGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_StudentAttendance_SELECT_by_UserID_GroupID_GroupMode(object cUserID, object iClassGroupID, object iGroupMode)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtStudentAtt = new DataTable();
string strSQL = "SELECT * FROM ORCS_StudentAttendance WHERE cUserID LIKE '" + cUserID + "' AND iClassGroupID LIKE '" + iClassGroupID + "' AND iGroupMode = '" + iGroupMode + "'";
dtStudentAtt = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtStudentAtt);
}
/// <summary>
/// 根據IP Address取出使用者出席狀態資料
/// </summary>
/// <param name="cIPAddress"></param>
/// <returns></returns>
public static DataTable ORCS_StudentAttendance_SELECT_by_IPAddress(object cIPAddress)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtStudentAtt = new DataTable();
string strSQL = "SELECT * FROM ORCS_StudentAttendance WHERE cIPAddress LIKE '" + cIPAddress + "'";
dtStudentAtt = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtStudentAtt);
}
/// <summary>
/// 從ClassTimeTable取出資料
/// </summary>
/// <param name="iClassGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_ClassTimeTable_SELECT_by_ClassGroupID(object iClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtClassTimeTable = new DataTable();
string strSQL = "SELECT * FROM ORCS_ClassTimeTable WHERE iClassGroupID LIKE '" + iClassGroupID + "'";
dtClassTimeTable = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtClassTimeTable);
}
/// <summary>
/// 根據iClassGroupID和iAnswerMode取出使用者資料
/// </summary>
/// <param name="cExerciseID"></param>
/// <returns></returns>
public static DataTable ORCS_ExerciseCondition_SELECT_by_iClassGroupID_iAnswerMode(object iClassGroupID, object iAnswerMode)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtExerciseCond = new DataTable();
string strSQL = "SELECT * FROM ORCS_ExerciseCondition WHERE iClassGroupID LIKE '" + iClassGroupID + "' AND iAnswerMode = '" + iAnswerMode + "'";
dtExerciseCond = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtExerciseCond);
}
/// <summary>
/// 根據iClassGroupID取該堂課程所有題目ID
/// </summary>
/// <param name="cExerciseID"></param>
/// <returns></returns>
public static DataTable ORCS_ExerciseCondition_SELECT_by_iClassGroupID_For_QuestionNumber(object iClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtExerciseCond = new DataTable();
string strSQL = "SELECT DISTINCT cExerciseID FROM ORCS_ExerciseCondition WHERE iClassGroupID = '" + iClassGroupID + "'";
dtExerciseCond = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtExerciseCond);
}
/// <summary>
/// 取出所有小組
/// </summary>
/// <returns></returns>
public static DataTable ORCS_TempGroup_SELECT_All()
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtSchoolGroup = new DataTable();
string strSQL = "SELECT * FROM ORCS_TempGroup";
dtSchoolGroup = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtSchoolGroup);
}
/// <summary>
/// 根據ORCS_SchoolGroup的iParentNodeID(iSchoolGroupID)取出群組
/// </summary>
/// <param name="iParentNodeID"></param>
/// <returns></returns>
public static DataTable ORCS_SchoolGroup_SELECT__ChildeNode_by_ParentGroupID(object iParentNodeID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtTempGroup = new DataTable();
string strSQL = "SELECT * FROM ORCS_SchoolGroup WHERE iParentNodeID LIKE '" + iParentNodeID + "'";
dtTempGroup = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtTempGroup);
}
/// <summary>
/// 根據iClassGroupID取出群組
/// </summary>
/// <param name="iTempGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_TempGroup_SELECT_by_iClassGroupID(object iClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtTempGroup = new DataTable();
string strSQL = "SELECT * FROM ORCS_TempGroup WHERE iClassGroupID LIKE '" + iClassGroupID + "'ORDER BY iTempGroupID ASC";
dtTempGroup = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtTempGroup);
}
/// <summary>
/// 根據UserID取出使用者資料
/// </summary>
/// <param name="cUserID"></param>
/// <returns></returns>
public static DataTable ORCS_User_SELECT_by_UserID(object cUserID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtUserInfo = new DataTable();
string strSQL = "SELECT * FROM ORCS_User WHERE cUserID LIKE '" + cUserID + "'";
dtUserInfo = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtUserInfo);
}
/// <summary>
/// 根據iGroupID和cIdentityType取出會議主席/紀錄者資料
/// </summary>
/// <param name="cUserID"></param>
/// <param name="iGroupID"></param>
/// <param name="cGroupClassify"></param>
/// <returns></returns>
public static DataTable ORCS_MeetingIdentity_SELECT_by_GroupID_IdentityType(object iGroupID, object cIdentityType)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtGroupMember = new DataTable();
string strSQL = "SELECT * FROM ORCS_MeetingIdentity WHERE iGroupID LIKE '" + iGroupID + "' AND cIdentityType LIKE '" + cIdentityType + "'";
dtGroupMember = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtGroupMember);
}
/// <summary>
/// 根據cUserID、iGroupID取出第幾小組
/// </summary>
/// <param name="cUserID"></param>
/// <param name="iGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_TempGroup_SELECT_by_UserID_GroupID(object cUserID, object iGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtGroupMember = new DataTable();
string strSQL = "SELECT * FROM ORCS_TempGroup WHERE iTempGroupID LIKE '" + cUserID + "' AND iClassGroupID LIKE '" + iGroupID + "'";
dtGroupMember = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtGroupMember);
}
/// <summary>
/// 根據cUserID取出使用者資料
/// </summary>
/// <param name="cUserID"></param>
/// <param name="cGroupClassify"></param>
/// <returns></returns>
public static DataTable ORCS_GroupMember_SELECT_by_UserID(object cUserID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtGroupMember = new DataTable();
string strSQL = "SELECT * FROM " + clsGroup.TB_ORCS_MemberGroup + " WHERE cUserID LIKE '" + cUserID + "'";
dtGroupMember = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtGroupMember);
}
/// <summary>
/// 根據ParentGroupID(iSchoolGroupID)和cClassGroupName取出課程ID
/// </summary>
/// <param name="iSchoolGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_ClassGroup_SELECT_by_ParentGroupID_ClassName(object iSchoolGroupID, object cClassGroupName)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtClassGroup = new DataTable();
string strSQL = "SELECT * FROM ORCS_ClassGroup WHERE iSchoolGroupID LIKE '" + iSchoolGroupID + "'AND cClassGroupName LIKE '" + cClassGroupName + "'";
dtClassGroup = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtClassGroup);
}
/// <summary>
/// 根據UserID取出上傳資料
/// </summary>
/// <param name="cUserID"></param>
/// <returns></returns>
public static DataTable ORCS_FileUploadData_SELECT_by_ClassGroupID_UserID(object iClassGroupID, object cUserID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtFileUploadData = new DataTable();
string strSQL = "SELECT * FROM ORCS_FileUploadData WHERE iClassGroupID LIKE '" + iClassGroupID + "' AND cUserID LIKE '" + cUserID + "'";
dtFileUploadData = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtFileUploadData);
}
/// <summary>
/// 根據iSchoolGroupID取出群組
/// </summary>
/// <param name="iSchoolGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_SchoolGroup_SELECT_by_SchoolGroupID(object iSchoolGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtSchoolGroup = new DataTable();
string strSQL = "SELECT * FROM ORCS_SchoolGroup WHERE iSchoolGroupID LIKE '" + iSchoolGroupID + "'";
dtSchoolGroup = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtSchoolGroup);
}
/// <summary>
/// 根據ExerciseCondID和UserID取出上傳資料
/// </summary>
/// <param name="cExerciseCondID"></param>
/// <param name="cUserID"></param>
/// <returns></returns>
public static DataTable ORCS_TextUploadData_SELECT_by_ExerciseCondID_UserID(object cExerciseCondID, object cUserID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtFileUploadData = new DataTable();
string strSQL = "SELECT * FROM ORCS_TextUploadData WHERE cExerciseCondID LIKE '" + cExerciseCondID + "' AND cUserID LIKE '" + cUserID + "'";
dtFileUploadData = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtFileUploadData);
}
/// <summary>
/// 根據ExerciseCondID和ClassGroupID取出唯一習題資料
/// </summary>
/// <param name="cExerciseCondID"></param>
/// <param name="iClassGroupID"></param>
/// <returns></returns>
public static DataTable ORCS_ExerciseConditionHistory_SELECT_by_DISTINCT_ExerciseCondID_GroupID(object cExerciseCondID, object iClassGroupID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtExerciseCondHis = new DataTable();
string strSQL = "SELECT DISTINCT cExerciseID FROM ORCS_ExerciseConditionHistory WHERE cExerciseCondID LIKE '" + cExerciseCondID + "' AND iClassGroupID LIKE '" + iClassGroupID + "' ORDER BY cExerciseID ASC ";
dtExerciseCondHis = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtExerciseCondHis);
}
/// <summary>
/// 根據題目ID取出使用者資料
/// </summary>
/// <param name="cExerciseID"></param>
/// <returns></returns>
public static DataTable ORCS_ExerciseCondition_SELECT_by_cExerciseID(object cExerciseID)
{
clsORCSDB ORCSDB = new clsORCSDB();
DataTable dtExerciseCond = new DataTable();
string strSQL = "SELECT * FROM ORCS_ExerciseCondition WHERE cExerciseID LIKE '" + cExerciseID + "'";
dtExerciseCond = ORCSDB.GetDataSet(strSQL).Tables[0];
return(dtExerciseCond);
}
