public static DataTable VIEWCourseUser_SELECT(object cCourseID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM VIEW_MLAS_CourseUser WHERE cCourseID LIKE '" + cCourseID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_ActivityMessage_Select(object iMessageID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_ActivityMessage WHERE iMessageID LIKE '" + iMessageID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable VIEWCourseToActivity_SELECT_SeriesActivity(object cSeriesActivityID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM VIEW_MLAS_CourseToActivity WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable VIEWCourseToActivity_SELECT_SpecialActivity(object cActivityID, object cActivityName)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM VIEW_MLAS_CourseToActivity WHERE cActivityID LIKE '" + cActivityID + "' AND cActivityName LIKE '%" + cActivityName + "%'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_ActivityUserRole_SELECT_cActivityID_UserID(object cActivityID, object cUserID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_ActivityUserRole WHERE cActivityID='" + cActivityID + "'AND cUserID='" + cUserID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLASConfig_SELECT(object cName)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_Config WHERE cName LIKE '" + cName + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_SeriesActivityUser_Select_by_cSeriesActivityID_cSeriesActivityUserGroupID(object cSeriesActivityID, object cSeriesActivityUserGroupID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_SeriesActivityUser WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "' AND cSeriesActivityUserGroupID LIKE '" + cSeriesActivityUserGroupID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static string MLAS_SeriesActivityUser_Select_SeriesActivityUserGroupID(object cSeriesActivityID, object cUserID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT cSeriesActivityUserGroupID FROM MLAS_SeriesActivityUser WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "' AND cUserID LIKE '" + cUserID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0].Rows[0]["cSeriesActivityUserGroupID"].ToString());
}
/// <summary>
/// Get data from MLAS_CourseInfo table
/// </summary>
/// <param name="cCourseID"></param>
/// <returns></returns>
public static DataTable MLAS_CourseType_SELECT_All()
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_CourseType";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_SeriesActivityMetadata_Select_cSeriesActivityID(object cSeriesActivityID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_SeriesActivityMetadata WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLASCourse_SELECT_byViewMode(object cCourseViewMode)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_CourseInfo WHERE cCourseViewMode LIKE '" + cCourseViewMode + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_ActivityBasicInfo_Select_Activity_HaveDueDate()
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_ActivityBasicInfo WHERE cActivityEndTime NOT LIKE ''";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_ActivityBasicInfo_Select_Activity_ByEndTime(object cActivityEndTime)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_ActivityBasicInfo WHERE cActivityEndTime LIKE '" + cActivityEndTime + "%'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_SeriesActivityUser_Select_group_DISTINCT_cSeriesActivityUserGroup(object cSeriesActivityID, object cSeriesActivityUserGroupID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT DISTINCT cSeriesActivityUserGroupID FROM MLAS_SeriesActivityUser WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "'AND cSeriesActivityUserGroupID NOT LIKE '" + cSeriesActivityUserGroupID + "' ORDER BY cSeriesActivityUserGroupID ASC ";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLAS_ActivityMessage_Select(object cActivityID, object cActivityStep, object cRoleID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_ActivityMessage WHERE cActivityID LIKE '" + cActivityID + "'AND cActivityStep LIKE'" + cActivityStep + "'AND cRoleID LIKE'" + cRoleID + "'";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 刪除活動通知手機簡訊
/// </summary>
/// <param name="Key"></param>
public static void DeleteActivityNotifyPhone(string Key)
{
clsMLASDB MLASDB = new clsMLASDB(System.Configuration.ConfigurationManager.ConnectionStrings["connHintsDB"].ToString());
string strSQL_PhoneMessage = "DELETE From PhoneMessage Where pkey ='" + Key + "'";
MLASDB.ExecuteNonQuery(strSQL_PhoneMessage);
}
/// <summary>
/// 根據使用者ID取得組別的ID
/// </summary>
/// <param name="cSeriesActivityID"></param>
/// <param name="cActivityID"></param>
/// <param name="cUserID"></param>
/// <returns></returns>
public static DataTable MLAS_SeriesActivityUserJOINMLAS_SeriesActivityUser_SELECT_GroupID(object cSeriesActivityID, object cActivityID, object cUserID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT DISTINCT dbo.MLAS_SeriesActivityUser.cSeriesActivityUserGroupID " +
" FROM dbo.MLAS_ActivityUserRole INNER JOIN dbo.MLAS_SeriesActivityUser ON dbo.MLAS_ActivityUserRole.cUserID = dbo.MLAS_SeriesActivityUser.cUserID " +
" WHERE (dbo.MLAS_SeriesActivityUser.cSeriesActivityID = '" + cSeriesActivityID + "') AND (dbo.MLAS_ActivityUserRole.cActivityID = '" + cActivityID + "') AND (dbo.MLAS_SeriesActivityUser.cUserID = '" + cUserID + "')";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
/// <summary>
/// 取得使用者是否具有角色ID
/// </summary>
/// <param name="strActivityID"></param>
/// <param name="strUserID"></param>
/// <returns></returns>
public static DataTable getUserRoleID(String strActivityID, String strUserID)
{
clsMLASDB MLASDB = new clsMLASDB();
String strSQL_MLAS_ActivityUserRoleJoinMLAS_Role = "SELECT dbo.MLAS_ActivityUserRole.cRoleID, dbo.MLAS_ActivityUserRole.cActivityID, dbo.MLAS_ActivityUserRole.cUserID, dbo.MLAS_Role.cRoleName " +
" FROM dbo.MLAS_ActivityUserRole INNER JOIN dbo.MLAS_Role ON dbo.MLAS_ActivityUserRole.cRoleID = dbo.MLAS_Role.cRoleID " +
" WHERE dbo.MLAS_ActivityUserRole.cActivityID LIKE '" + strActivityID + "' AND dbo.MLAS_ActivityUserRole.cUserID LIKE '" + strUserID + "'";
DataTable dtMLAS_ActivityUserRoleJoinMLAS_Role = MLASDB.GetDataSet(strSQL_MLAS_ActivityUserRoleJoinMLAS_Role).Tables[0];
return(dtMLAS_ActivityUserRoleJoinMLAS_Role);
}
/// <summary>
/// 更新活動通知手機簡訊
/// </summary>
/// <param name="Employee_no"></param>
/// <param name="Msg"></param>
/// <param name="Key"></param>
public static void UpdateActivityNotifyPhone(string Employee_no, string Msg, string Key)
{
clsMLASDB MLASDB = new clsMLASDB(System.Configuration.ConfigurationManager.ConnectionStrings["connHintsDB"].ToString());
string strCreateTime = DateTime.Now.ToString("yyyy/MM/dd");
string strSQL_PhoneMessage = "UPDATE PhoneMessage SET ";
strSQL_PhoneMessage += "employee_no = '" + Employee_no + "', msg = '" + Msg + "', bbcall = '8888', create_time = '" + strCreateTime + "', proc_time = ''";
strSQL_PhoneMessage += "where (pkey = '" + Key + "')";
MLASDB.ExecuteNonQuery(strSQL_PhoneMessage);
}
/// <summary>
/// 新增活動通知手機簡訊 PhoneMessage為備份與測試的資料表 PhoneMessageX為醫學院資訊室取的資料的資料表
/// </summary>
/// <param name="Employee_no"></param>
/// <param name="Msg"></param>
public static void InsertActivityNotifyPhone(string Msg, string BBCall, string Sender)
{
string strCreateTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm");
//存到資訊室(140.116.60.111)做簡訊傳送 若下載到本機端請註解下面程式碼 避免測試時傳送簡訊
clsMLASDB NCKMDB = new clsMLASDB(System.Configuration.ConfigurationManager.ConnectionStrings["connNCKMDB"].ToString());
string strSQL_PhoneMessageX = "INSERT INTO PhoneMessageX(employee_no,sender,msg,source,bbcall,create_time) VALUES(";
strSQL_PhoneMessageX += "'MR0950','" + Sender + "','" + Msg + "','R','" + BBCall + "','" + strCreateTime + "')";
NCKMDB.ExecuteNonQuery(strSQL_PhoneMessageX);
}
public static DataTable MLAS_User_SELECT_ID_Name_by_UserID_Authority(object cUserAccount, object cAuthority)
{
clsMLASDB MLASDB = new clsMLASDB();
DataTable dtUserInfo = new DataTable();
string strSQL = "SELECT cUserAccount, cUserName FROM MLAS_User WHERE cUserAccount LIKE '" + cUserAccount + "' AND cAuthority LIKE '" + cAuthority + "'";
dtUserInfo = MLASDB.GetDataSet(strSQL).Tables[0];
return(dtUserInfo);
}
/// <summary>
/// 取得分組的組別ID與名稱
/// </summary>
/// <param name="cSeriesActivityID"></param>
/// <param name="cActivityID"></param>
/// <returns></returns>
public static DataTable MLAS_SeriesActivityUserJOINMLAS_SeriesActivityUser_SELECT_DISTINCT_Group(object cSeriesActivityID, object cActivityID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT DISTINCT MLAS_SeriesActivityUser.cSeriesActivityUserGroupID, MLAS_SeriesActivityUser.cSeriesActivityUserGroupName " +
" FROM MLAS_ActivityUserRole INNER JOIN MLAS_SeriesActivityUser ON MLAS_ActivityUserRole.cUserID = MLAS_SeriesActivityUser.cUserID " +
" WHERE (MLAS_SeriesActivityUser.cSeriesActivityID = '" + cSeriesActivityID + "') AND " +
" (MLAS_ActivityUserRole.cActivityID = '" + cActivityID + "') " +
" ORDER BY MLAS_SeriesActivityUser.cSeriesActivityUserGroupID ASC";
return(MLASDB.GetDataSet(strSQL).Tables[0]);
}
public static DataTable MLASUser_SELECT_Group(object cGroup, object cUserID)
{
DataTable dtResult = new DataTable();
string strSQL = "SELECT * FROM MLAS_User WHERE cUserID LIKE '" + cUserID + "' AND cGroup LIKE '" + cGroup + "'";
try
{
clsMLASDB MLASDB = new clsMLASDB();
dtResult = MLASDB.GetDataSet(strSQL).Tables[0];
}
catch { }
return(dtResult);
}
public static DataTable MLASUser_SELECT_DISTINCT_Group()
{
DataTable dtResult = new DataTable();
string strSQL = "SELECT DISTINCT cGroup FROM MLAS_User ";
try
{
clsMLASDB MLASDB = new clsMLASDB();
dtResult = MLASDB.GetDataSet(strSQL).Tables[0];
}
catch { }
return(dtResult);
}
public static DataTable MLASUser_SELECT_DISTINCT_Group_byAuthority(object cAuthority)
{
DataTable dtResult = new DataTable();
string strSQL = "SELECT DISTINCT cGroup FROM MLAS_User WHERE cAuthority LIKE '%" + cAuthority + "%'";
try
{
clsMLASDB MLASDB = new clsMLASDB();
dtResult = MLASDB.GetDataSet(strSQL).Tables[0];
}
catch { }
return(dtResult);
}
public static string MLAS_ActivityBasicInfo_Select_ActivityTitle_ByActivityID(object cActivityID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT cActivityTitle FROM MLAS_ActivityBasicInfo WHERE cActivityID LIKE'" + cActivityID + "'";
try
{
return(MLASDB.ExecuteScalar(strSQL).ToString());
}
catch
{
return("");
}
}
public static DataTable MLASUser_SELECT(object cUserID)
/*cUserID 已經改成編號，不是過去存放使用者ID的欄位了*/
{
DataTable dtResult = new DataTable();
string strSQL = "SELECT * FROM MLAS_User WHERE cUserID LIKE '" + cUserID + "'";
try
{
clsMLASDB MLASDB = new clsMLASDB();
dtResult = MLASDB.GetDataSet(strSQL).Tables[0];
}
catch { }
return(dtResult);
}
public static string MLAS_SeriesActivityUser_Select_GroupName(object cSeriesActivityID, object cSeriesActivityUserGroupID)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT cSeriesActivityUserGroupName FROM MLAS_SeriesActivityUser WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "' AND cSeriesActivityUserGroupID LIKE '" + cSeriesActivityUserGroupID + "'";
DataTable dt = MLASDB.GetDataSet(strSQL).Tables[0];
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]["cSeriesActivityUserGroupName"].ToString());
}
else
{
return("");
}
}
public static string MLAS_SeriesActivityMetadata_Select_Parameter(object cSeriesActivityID, object cParameterName)
{
clsMLASDB MLASDB = new clsMLASDB();
string strSQL = "SELECT * FROM MLAS_SeriesActivityMetadata WHERE cSeriesActivityID LIKE '" + cSeriesActivityID + "' AND cParameterName LIKE '" + cParameterName + "'";
DataTable dtMetadata = MLASDB.GetDataSet(strSQL).Tables[0];
if (dtMetadata.Rows.Count > 0)
{
return(dtMetadata.Rows[0]["cParameterValue"].ToString());
}
else
{
return("");
}
}
public static int MLAS_CourseType_DELETE(object cCourseTypeName)
{
string strSQL = "DELETE MLAS_CourseType WHERE cCourseTypeName LIKE '" + cCourseTypeName + "'";
try
{
clsMLASDB MLASDB = new clsMLASDB();
MLASDB.ExecuteNonQuery(strSQL);
}
catch
{
return(-1);
}
return(0);
}
