请帮我分析一下下面代码的作用using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class cGlobal
{
private static string _sessionName = "loginUser";
private static string _cookieName = "user";
public cGlobal()
{
// Something to do.
}
public static void setLogout()
{
HttpContext context = HttpContext.Current;
context.Session.Clear();
context.Session.Abandon();
HttpCookie cookie = new HttpCookie(_cookieName);
context.Response.Cookies.Add(cookie);
}
public class loginUserInfo
{
public enum userType
{
sysUser = 0,
deptHeader
}
public string id { get; set; }
public userType type { get; set; }
public bool isADVerify { get; set; }
public bool isDeptHead
{
get
{
return type == userType.deptHeader;
}
}
public bool isSysUser
{
get
{
return type == userType.sysUser;
}
}
public bool isValid { get; set; }
public void setEmpty()
{
id = string.Empty;
type = userType.sysUser;
isValid = false;
isADVerify = false;
}
public loginUserInfo()
{
setEmpty();
}
}
public static loginUserInfo loginUser
{
get
{
HttpContext context = HttpContext.Current;
loginUserInfo userInfo = new loginUserInfo();
if (context.Session[_sessionName] == null)
{
HttpCookie cookie = context.Request.Cookies[_cookieName];
DateTime expires = DateTime.Now.AddDays(-1);
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
object tmpResult = null;
try
{
if (cookie == null
|| cookie.Values["value"] == null
|| cookie.Values["expires"] == null)
{
throw new Exception("cookie not exist.");
}
if (!DateTime.TryParse(cookie.Values["expires"], out expires)
|| expires < DateTime.Now)
{
throw new Exception("expired.");
}
userInfo.id = cookie.Values["value"];
if (userInfo.id == string.Empty)
{
throw new Exception("no value.");
}
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT [IsADVerify],[PasswordIsInit]
FROM [SystemUsers]
WHERE [UserId]=@userId";
sqlCmd.Parameters.AddWithValue("@userId", userInfo.id);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (sqlDR.Read())
{
userInfo.type = cGlobal.loginUserInfo.userType.sysUser;
userInfo.isADVerify = (bool)sqlDR["IsADVerify"];
}
else
{
sqlDR.Close();
sqlCmd.CommandText = @"
SELECT [DeptId]
FROM [DeptHeadMap]
WHERE [HeadMemberId]=@headMemberId";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@headMemberId", userInfo.id);
tmpResult = sqlCmd.ExecuteScalar();
if (tmpResult != null && !tmpResult.Equals(DBNull.Value))
{
userInfo.type = cGlobal.loginUserInfo.userType.deptHeader;
userInfo.isADVerify = true;
}
}
}
userInfo.isValid = true;
cGlobal.loginUser = userInfo;
}
catch (Exception ex)
{
userInfo.setEmpty();
}
}
}
}
else
{
userInfo = context.Session[_sessionName] as cGlobal.loginUserInfo;
}
return userInfo;
}
set
{
HttpContext context = HttpContext.Current;
context.Session.Clear();
context.Session.Add(_sessionName, value);
HttpCookie cookie = new HttpCookie(_cookieName);
cookie.Values.Add("value", value.id);
cookie.Values.Add("type", value.type.ToString());
cookie.Values.Add("expires", string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now.AddDays(1)));
context.Response.Cookies.Add(cookie);
}
}
protected static object _isErrLogWriting = new object();
public static void writeErrLog(string msg)
{
string filePath = string.Format(@"{0}{1}\{2:yyyyMMdd}.err",
HttpContext.Current.Server.MapPath("/"),
WebConfigurationManager.AppSettings["logPath"],
DateTime.Now);
lock (_isErrLogWriting)
{
using (StreamWriter writer = new StreamWriter(filePath, true))
{
writer.WriteLine(string.Format(@"
{0:yyyy-MM-dd HH:mm:ss}, {1}, {2}",
HttpContext.Current.Request.Url.AbsolutePath,
DateTime.Now,
msg));
writer.Close();
}
}
}
public static string mainDatabaseConnectionString
{
get
{
return WebConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
}
}
public static string TestDatabaseConnectionString
{
get
{
return WebConfigurationManager.ConnectionStrings["TestdbConnStr"].ConnectionString;
}
}
public static int cardUIdLength
{
get
{
int len = 8;
if (int.TryParse(WebConfigurationManager.AppSettings["cardUIdLen"], out len))
{
// Todo: output error log
}
return len;
}
}
public static int acsPasswordLength
{
get
{
int len = 4;
if (int.TryParse(WebConfigurationManager.AppSettings["acsPwdLen"], out len))
{
// Todo: output error log
}
return len;
}
}
/// <summary>
/// 系統密碼長度最短限制
/// </summary>
public static int sysPwdMinLength
{
get
{
int len = 8;
if (int.TryParse(WebConfigurationManager.AppSettings["sysPwdMinLen"], out len))
{
// Todo: output error log
}
return len;
}
}
/// <summary>
/// 服務程式呼叫逾時時間
/// </summary>
public static int serviceTimeout
{
get
{
int timeout = 1000;
if (int.TryParse(WebConfigurationManager.AppSettings["serviceTimeout"], out timeout))
{
// Todo: output error log
}
return timeout;
}
}
/// <summary>
/// 修改密碼往前追溯修改紀錄次數
/// </summary>
public static int editSysPwdTraceCount
{
get
{
int count = 13;
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT [ParamValue]
FROM [SystemParam]
WHERE
[ParamId]='Sys_PasswordTraceCount'";
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (sqlDR.Read())
{
int.TryParse(sqlDR["ParamValue"].ToString(), out count);
}
}
}
catch (Exception ex)
{
// Todo: write error log
}
}
}
return count;
}
}
/// <summary>
/// 取得系統密碼有效期限訊息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static sysPwdExpireInto getSysPwdExpiredInfo(string userId)
{
cGlobal.sysPwdExpireInto info = new sysPwdExpireInto()
{
expireValue = 3,
expireUnit = cGlobal.sysPwdExpireUnit.month,
noticeValue = 14,
noticeUnit = cGlobal.sysPwdExpireUnit.day
};
info.lastModifyDate = DateTime.Now;
info.expireDate = info.lastModifyDate.AddMonths(3);
info.startNoticeDate = info.expireDate.AddDays(-14);
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT TOP(1) [LogTime]
FROM [SystemUsersPwdHistory]
WHERE [UserId]=@userId
ORDER BY [LogTime] DESC";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@userId", userId);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (sqlDR.Read())
{
info.lastModifyDate = (DateTime)sqlDR["LogTime"];
}
}
sqlCmd.CommandText = @"
SELECT [ParamId],[ParamValue]
FROM [SystemParam]
WHERE
[ParamId] IN ('Sys_PasswordExpire','Sys_PasswordExpireNotice')";
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
int parseInt;
cGlobal.sysPwdExpireUnit parseUnit;
string[] tmp = null;
while (sqlDR.Read())
{
tmp = sqlDR["ParamValue"].ToString().Split(',');
if (tmp.Length != 2
|| !int.TryParse(tmp[0], out parseInt)
|| !Enum.TryParse(tmp[1], out parseUnit))
{
continue;
}
switch (sqlDR["ParamId"].ToString())
{
case "Sys_PasswordExpire":
info.expireValue = parseInt;
info.expireUnit = parseUnit;
break;
case "Sys_PasswordExpireNotice":
info.noticeValue = parseInt;
info.noticeUnit = parseUnit;
break;
}
}
}
}
catch (Exception ex)
{
// Todo: write error log
}
}
}
switch (info.expireUnit)
{
case cGlobal.sysPwdExpireUnit.year:
info.expireDate = info.lastModifyDate.AddYears(info.expireValue);
break;
case cGlobal.sysPwdExpireUnit.month:
info.expireDate = info.lastModifyDate.AddMonths(info.expireValue);
break;
case cGlobal.sysPwdExpireUnit.day:
info.expireDate = info.lastModifyDate.AddDays(info.expireValue);
break;
case cGlobal.sysPwdExpireUnit.hour:
info.expireDate = info.lastModifyDate.AddHours(info.expireValue);
break;
case cGlobal.sysPwdExpireUnit.minute:
info.expireDate = info.lastModifyDate.AddMinutes(info.expireValue);
break;
case cGlobal.sysPwdExpireUnit.second:
info.expireDate = info.lastModifyDate.AddSeconds(info.expireValue);
break;
}
switch (info.noticeUnit)
{
case cGlobal.sysPwdExpireUnit.year:
info.startNoticeDate = info.expireDate.AddYears(-info.noticeValue);
break;
case cGlobal.sysPwdExpireUnit.month:
info.startNoticeDate = info.expireDate.AddMonths(-info.noticeValue);
break;
case cGlobal.sysPwdExpireUnit.day:
info.startNoticeDate = info.expireDate.AddDays(-info.noticeValue);
break;
case cGlobal.sysPwdExpireUnit.hour:
info.startNoticeDate = info.expireDate.AddHours(-info.noticeValue);
break;
case cGlobal.sysPwdExpireUnit.minute:
info.startNoticeDate = info.expireDate.AddMinutes(-info.noticeValue);
break;
case cGlobal.sysPwdExpireUnit.second:
info.startNoticeDate = info.expireDate.AddSeconds(-info.noticeValue);
break;
}
return info;
}
public enum sysPwdExpireUnit
{
year = 0,
month,
day,
hour,
minute,
second
}
public class sysPwdExpireInto
{
public DateTime lastModifyDate { get; set; }
public int expireValue { get; set; }
public sysPwdExpireUnit expireUnit { get; set; }
public DateTime expireDate { get; set; }
public bool isExpired { get { return DateTime.Now > expireDate; } }
public int noticeValue { get; set; }
public sysPwdExpireUnit noticeUnit { get; set; }
public DateTime startNoticeDate { get; set; }
public bool needNotice { get { return DateTime.Now > startNoticeDate; } }
}
public static string systemVersion
{
get
{
return WebConfigurationManager.AppSettings["version"].Trim();
}
}
public static string systemRelease
{
get
{
return WebConfigurationManager.AppSettings["release"].Trim();
}
}
public static string importFilePath
{
get
{
string path = WebConfigurationManager.AppSettings["importFilePath"];
try
{
path = HttpContext.Current.Server.MapPath(path);
}
catch (Exception ex)
{
// Todo: write error log
path = string.Empty;
}
return path;
}
}
/// <summary>
/// 取得設備時間群組可設定的組數
/// </summary>
/// <param name="modelName"></param>
/// <returns></returns>
public static int getDeviceTimeGroupMaxCount(string modelName)
{
int count = 0;
switch (modelName.ToUpper())
{
case "SC202A":
case "SC202B":
count = 10;
break;
case "SC300":
count = 100;
break;
case "DEFAULT":
case "DEFAULT_EV1":
case "LIFT_3S":
count = 90;
break;
case "DC1001T":
count = 90;
break;
case "SOYAL716E":
count = 10;
break;
}
return count;
}
/// <summary>
/// 取得設備時間群組每組可設定的段數
/// </summary>
/// <param name="modelName"></param>
/// <returns></returns>
public static int getDeviceTimeGroupSectorMaxCount(string modelName)
{
int count = 0;
switch (modelName.ToUpper())
{
case "SC202A":
case "SC202B":
count = 1;
break;
case "SC300":
count = 2;
break;
case "DEFAULT":
case "DEFAULT_EV1":
case "LIFT_3S":
count = 4;
break;
case "DC1001T":
count = 4;
break;
case "SOYAL716E":
count = 1;
break;
}
return count;
}
public static bool getDeviceTimeGroupSectorDatesWithHoliday(string modelName)
{
bool result = false;
if (modelName.ToUpper() == "SC300")
{
result = true;
}
return result;
}
public class sysPageInfo
{
public string pageId { get; set; }
public bool isBasic { get; set; }
}
/// <summary>
/// 取得登入者可使用的頁面
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static sysPageInfo[] getSystemPages(string userId)
{
List<sysPageInfo> pageList = new List<sysPageInfo>();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT DISTINCT
[SFM].[PageId],
[SFM].[IsBasic]
FROM [SystemUserPerms] AS [SUP]
INNER JOIN [SystemFunctionMap] AS [SFM] ON
[SFM].[IsEnable]=1 AND
[SFM].[FunctionId]=[SUP].[FunctionId]
WHERE [SUP].[UserId]=@userId";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@userId", userId);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
while (sqlDR.Read())
{
if (sqlDR["PageId"].Equals(DBNull.Value))
{
continue;
}
pageList.Add(new sysPageInfo()
{
pageId = sqlDR["PageId"].ToString(),
isBasic = (bool)sqlDR["IsBasic"]
});
}
}
}
catch (Exception ex)
{
// Todo: write error log
pageList.Clear();
}
}
}
return pageList.ToArray();
}
/// <summary>
/// 人員資料狀態
/// </summary>
public enum MemberDataState
{
Active = 0,
Disable,
Delete,
Lock,
TempCard
}
/// <summary>
/// 取得登入者在頁面上可使用者功能
/// </summary>
/// <param name="userId"></param>
/// <param name="pageId"></param>
/// <returns></returns>
public static List<string> getPageFunctions(string userId, string pageId)
{
List<string> funcList = new List<string>();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT [SFM].[FunctionId]
FROM [SystemUserPerms] AS [SUP]
INNER JOIN [SystemFunctionMap] AS [SFM] ON
[SFM].[IsEnable]=1 AND
[SFM].[FunctionId]=[SUP].[FunctionId]
WHERE
[SUP].[UserId]=@userId AND
[SFM].[PageId]=@pageId";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@userId", userId);
sqlCmd.Parameters.AddWithValue("@pageId", pageId);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
while (sqlDR.Read())
{
if (sqlDR["FunctionId"].Equals(DBNull.Value))
{
continue;
}
funcList.Add(sqlDR["FunctionId"].ToString());
}
}
}
catch (Exception ex)
{
// Todo: write error log
funcList.Clear();
}
}
}
return funcList;
}
/// <summary>
/// 操作紀錄資訊
/// </summary>
public class opRecordInfo
{
public string pageId { get; set; }
public string funcId { get; set; }
public string actionDescr { get; set; }
public string detailDescr { get; set; }
public opRecordInfo()
{
pageId = string.Empty;
funcId = string.Empty;
actionDescr = string.Empty;
detailDescr = string.Empty;
}
}
/// <summary>
/// 加入一筆操作紀錄
/// </summary>
/// <param name="info"></param>
/// <param name="sqlCmd"></param>
/// <returns></returns>
public static bool writeOperateRecords(cGlobal.opRecordInfo info, SqlCommand sqlCmd = null)
{
bool result = true;
string userId = cGlobal.loginUser.id;
bool sqlCmdExist = true;
string prevCmd = string.Empty;
SqlParameter[] prevParam = null;
if (sqlCmd == null)
{
sqlCmdExist = false;
sqlCmd = new SqlCommand();
sqlCmd.Connection = new SqlConnection();
sqlCmd.Connection.ConnectionString = cGlobal.mainDatabaseConnectionString;
}
else
{
prevCmd = sqlCmd.CommandText;
if (sqlCmd.Parameters.Count > 0)
{
prevParam = new SqlParameter[sqlCmd.Parameters.Count];
sqlCmd.Parameters.CopyTo(prevParam, 0);
}
}
try
{
if (sqlCmd.Connection.State != ConnectionState.Open)
{
sqlCmd.Connection.Open();
}
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@operateTime", DateTime.Now);
sqlCmd.Parameters.AddWithValue("@userId", userId);
sqlCmd.Parameters.AddWithValue("@pageId", info.pageId);
sqlCmd.Parameters.AddWithValue("@functionId", info.funcId);
sqlCmd.Parameters.AddWithValue("@actionDescr", info.actionDescr);
sqlCmd.Parameters.AddWithValue("@detailDescr", info.detailDescr);
if (info.funcId == string.Empty
&& info.pageId != string.Empty)
{
sqlCmd.CommandText = @"
SELECT TOP(1) [FunctionId]
FROM [SystemFunctionMap]
WHERE [PageId]=@pageId";
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (!sqlDR.Read())
{
throw new Exception("Invalid Action.");
}
sqlCmd.Parameters["@functionId"].Value = sqlDR["FunctionId"].ToString();
}
}
sqlCmd.CommandText = @"
INSERT INTO [OperateRecords]
([OperateTime],[UserId],[FunctionId],[ActionDescr],[DetailDescr])
VALUES
(@operateTime,@userId,@functionId,@actionDescr,@detailDescr)";
result = sqlCmd.ExecuteNonQuery() > 0;
}
catch (Exception ex)
{
// Todo: write error log
result = false;
}
finally
{
if (!sqlCmdExist)
{
sqlCmd.Connection.Dispose();
sqlCmd.Dispose();
}
else
{
sqlCmd.CommandText = prevCmd;
if (prevParam != null)
{
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddRange(prevParam);
}
}
}
return result;
}
/// <summary>
/// 權限異動方式
/// </summary>
public enum authEditAction
{
delete = 0,
add
}
/// <summary>
/// 權限異動紀錄資訊
/// </summary>
public class authEditInfo
{
public string memberId { get; set; }
public string cardUId { get; set; }
public string deviceId { get; set; }
public int subDeviceIx { get; set; }
public authEditAction action { get; set; }
public bool isGroup { get; set; }
public string remark { get; set; }
public authEditInfo()
{
memberId = string.Empty;
cardUId = string.Empty;
deviceId = string.Empty;
subDeviceIx = 0;
action = authEditAction.delete;
isGroup = false;
remark = string.Empty;
}
}
/// <summary>
/// 加入一筆權限異動紀錄
/// </summary>
/// <param name="info"></param>
/// <param name="sqlCmd"></param>
/// <returns></returns>
public static bool writeAuthEditRecords(cGlobal.authEditInfo info, SqlCommand sqlCmd = null)
{
bool result = true;
string userId = cGlobal.loginUser.id;
bool sqlCmdExist = true;
string prevCmd = string.Empty;
SqlParameter[] prevParam = null;
if (sqlCmd == null)
{
sqlCmdExist = false;
sqlCmd = new SqlCommand();
sqlCmd.Connection = new SqlConnection();
sqlCmd.Connection.ConnectionString = cGlobal.mainDatabaseConnectionString;
}
else
{
prevCmd = sqlCmd.CommandText;
if (sqlCmd.Parameters.Count > 0)
{
prevParam = new SqlParameter[sqlCmd.Parameters.Count];
sqlCmd.Parameters.CopyTo(prevParam, 0);
}
}
try
{
if (sqlCmd.Connection.State != ConnectionState.Open)
{
sqlCmd.Connection.Open();
}
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@actionTime", DateTime.Now);
sqlCmd.Parameters.AddWithValue("@editor", userId);
sqlCmd.Parameters.AddWithValue("@memberId", info.memberId);
sqlCmd.Parameters.AddWithValue("@cardUId", info.cardUId);
sqlCmd.Parameters.AddWithValue("@deviceId", info.deviceId);
sqlCmd.Parameters.AddWithValue("@subDeviceIx", info.subDeviceIx);
sqlCmd.Parameters.AddWithValue("@action", info.action.ToString());
sqlCmd.Parameters.AddWithValue("@isGroup", info.isGroup);
sqlCmd.Parameters.AddWithValue("@remark", info.remark);
sqlCmd.CommandText = @"
INSERT INTO [AuthEditLog]
([ActionTime],[Editor],
[MemberId],[CardUId],
[DeviceId],[SubDeviceIx],
[Action],[IsGroup],[IsAuto],
[Remark])
VALUES
(@actionTime,@editor,
@memberId,@cardUId,
@deviceId,@subDeviceIx,
@action,@isGroup,0,
@remark)";
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// Todo: write error log
result = false;
}
finally
{
if (!sqlCmdExist)
{
sqlCmd.Connection.Dispose();
sqlCmd.Dispose();
}
else
{
sqlCmd.CommandText = prevCmd;
if (prevParam != null)
{
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddRange(prevParam);
}
}
}
return result;
}
public class mailInfo
{
public string receiver { get; set; }
public string subject { get; set; }
public string mailContent { get; set; }
public mailInfo()
{
receiver = string.Empty;
subject = string.Empty;
mailContent = string.Empty;
}
}
/// <summary>
/// 加入一筆權限異動紀錄
/// </summary>
/// <param name="info"></param>
/// <param name="sqlCmd"></param>
/// <returns></returns>
public static bool writeMailJob(cGlobal.mailInfo info, SqlCommand sqlCmd = null)
{
bool result = true;
bool sqlCmdExist = true;
string prevCmd = string.Empty;
if (sqlCmd == null)
{
sqlCmdExist = false;
sqlCmd = new SqlCommand();
sqlCmd.Connection = new SqlConnection();
sqlCmd.Connection.ConnectionString = cGlobal.mainDatabaseConnectionString;
}
else
{
prevCmd = sqlCmd.CommandText;
}
try
{
if (sqlCmd.Connection.State != ConnectionState.Open)
{
sqlCmd.Connection.Open();
}
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@receiver", info.receiver);
sqlCmd.Parameters.AddWithValue("@subject", info.subject);
sqlCmd.Parameters.AddWithValue("@mailContent", info.mailContent);
sqlCmd.CommandText = @"
INSERT INTO [MailJob]
([Receiver],[Subject],[MailContent])
VALUES
(@receiver,@subject,@mailContent)";
result = sqlCmd.ExecuteNonQuery() > 0;
}
catch (Exception ex)
{
// Todo: write error log
result = false;
}
finally
{
if (!sqlCmdExist)
{
sqlCmd.Connection.Dispose();
sqlCmd.Dispose();
}
else
{
sqlCmd.CommandText = prevCmd;
}
}
return result;
}
/// <summary>
/// 取得登入者可管理的設備
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static DataTable getMgmtDevices(string userId)
{
DataTable deviceTbl = new DataTable();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlCmd.Parameters.AddWithValue("@userId", userId);
sqlCmd.CommandText = @"
SELECT [DeviceId],[SubDeviceIx]
FROM [DeviceMgmtGrpContent]
WHERE
[GroupId] IN (
SELECT [GroupId]
FROM [SystemUserDeviceMgmtGrp]
WHERE [UserId]=@userId)
UNION
SELECT [DeviceId],[SubDeviceIx]
FROM [DeviceMgmtWhiteList]
WHERE [UserId]=@userId
EXCEPT
SELECT [DeviceId],[SubDeviceIx]
FROM [DeviceMgmtBlackList]
WHERE [UserId]=@userId";
using (SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd))
{
sqlDA.Fill(deviceTbl);
}
}
catch (Exception ex)
{
// Todo: write error log
deviceTbl = new DataTable();
}
}
}
return deviceTbl;
}
public class sysParamInfo
{
public string paramId { get; set; }
public string paramValue { get; set; }
public string paramDescr { get; set; }
public sysParamInfo()
{
paramId = string.Empty;
paramValue = string.Empty;
paramDescr = string.Empty;
}
}
public static sysParamInfo getSystemParameter(string paramId)
{
cGlobal.sysParamInfo result = new cGlobal.sysParamInfo()
{
paramId = string.Empty,
paramValue = string.Empty,
paramDescr = string.Empty
};
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT
[ParamId],
[ParamValue],
[ParamDescr]
FROM [SystemParam]
WHERE [ParamId]=@paramId";
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@paramId", paramId);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (sqlDR.Read())
{
result.paramId = sqlDR["ParamId"].ToString();
result.paramValue = sqlDR["ParamValue"].ToString();
result.paramDescr = sqlDR["ParamDescr"].ToString();
}
}
}
catch (Exception ex)
{
// Todo: write error log
}
}
}
return result;
}
public static sysParamInfo getSystemPatameter(string paramId)
{
cGlobal.sysParamInfo result = new sysParamInfo();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT
[ParamId],
[ParamValue],
[ParamDescr]
FROM [SystemParam]
WHERE [ParamId]=@paramId";
sqlCmd.Parameters.AddWithValue("@paramId", paramId);
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
if (sqlDR.Read())
{
result.paramId = sqlDR["ParamId"].ToString();
result.paramValue = sqlDR["ParamValue"].ToString();
result.paramDescr = sqlDR["ParamDescr"].ToString();
}
}
}
catch (Exception ex)
{
// Todo: write error log
}
}
}
return result;
}
public static sysParamInfo[] getSystemPatameter()
{
List<cGlobal.sysParamInfo> result = new List<sysParamInfo>();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT
[ParamId],
[ParamValue],
[ParamDescr]
FROM [SystemParam]";
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
while (sqlDR.Read())
{
result.Add(new cGlobal.sysParamInfo()
{
paramId = sqlDR["ParamId"].ToString(),
paramValue = sqlDR["ParamValue"].ToString(),
paramDescr = sqlDR["ParamDescr"].ToString()
});
}
}
}
catch (Exception ex)
{
// Todo: write error log
}
}
}
return result.ToArray();
}
public class mbrImgFilePathInfo
{
public string path_emp { get; set; }
public string path_ven { get; set; }
public string domain { get; set; }
public string account { get; set; }
public string password { get; set; }
public void Reset()
{
path_emp = string.Empty;
path_ven = string.Empty;
domain = string.Empty;
account = string.Empty;
password = string.Empty;
}
public mbrImgFilePathInfo()
{
Reset();
}
}
/// <summary>
/// 同仁 / 廠商資料照片位置 (從資料庫讀取)
/// </summary>
public static mbrImgFilePathInfo mbrImgFilePath
{
get
{
mbrImgFilePathInfo info = new cGlobal.mbrImgFilePathInfo();
using (SqlConnection sqlConn = new SqlConnection())
{
sqlConn.ConnectionString = cGlobal.mainDatabaseConnectionString;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
try
{
sqlConn.Open();
sqlCmd.CommandText = @"
SELECT [ParamId],[ParamValue]
FROM [SystemParam]
WHERE [ParamId] LIKE 'Sys_ImgFilePath%'";
using (SqlDataReader sqlDR = sqlCmd.ExecuteReader())
{
while (sqlDR.Read())
{
switch (sqlDR["ParamId"].ToString())
{
case "Sys_ImgFilePath_TypeEmp":
info.path_emp = sqlDR["ParamValue"].ToString();
break;
case "Sys_ImgFilePath_TypeVen":
info.path_ven = sqlDR["ParamValue"].ToString();
break;
case "Sys_ImgFilePath_Domain":
info.domain = sqlDR["ParamValue"].ToString();
break;
case "Sys_ImgFilePath_Account":
info.account = sqlDR["ParamValue"].ToString();
break;
case "Sys_ImgFilePath_Password":
info.password = sqlDR["ParamValue"].ToString();
break;
}
}
}
}
catch (Exception ex)
{
// Todo: write error log
info.Reset();
}
}
}
return info;
}
}
/// <summary>
/// 壓縮Base64文字資料顯示圖片至32kb以下(for IE8)
/// </summary>
/// <param name="srcBase64Img"></param>
/// <returns></returns>
public static String compressBase64Img(String srcBase64Img)
{
byte[] org_img_bytes = Convert.FromBase64String(srcBase64Img);
MemoryStream ms = new MemoryStream(org_img_bytes);
byte[] buffer = ms.ToArray();
ms.Flush();
System.Drawing.Image srcImg = null;
System.Drawing.Bitmap tmpBmp = null;
System.Drawing.Graphics tmpGraphics = null;
double new_width = 0;
double new_height = 0;
// 持續壓縮Base64碼大小至32kb以下 (IE8限制)
int length = Convert.ToBase64String(buffer).Length;
int sizeLimit = 32 * 1024;
double scale = 0.75;
int retryLimit = 5;
while (retryLimit > 0 && length >= sizeLimit)
{
srcImg = System.Drawing.Image.FromStream(ms);
if (srcImg.Width >= srcImg.Height)
{
new_width = srcImg.Width * scale;
new_height = new_width * srcImg.Height / (double)srcImg.Width;
}
else if (srcImg.Height >= srcImg.Width)
{
new_height = srcImg.Height * scale;
new_width = new_height * srcImg.Width / (double)srcImg.Height;
}
tmpBmp = new System.Drawing.Bitmap((int)new_width, (int)new_height, srcImg.PixelFormat);
tmpGraphics = System.Drawing.Graphics.FromImage(tmpBmp);
tmpGraphics.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
tmpGraphics.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
tmpGraphics.DrawImage(srcImg, 0, 0, tmpBmp.Width, tmpBmp.Height);
ms = new MemoryStream();
tmpBmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
buffer = ms.ToArray();
length = Convert.ToBase64String(buffer).Length;
retryLimit--;
}
ms.Close();
return Convert.ToBase64String(buffer);
}
}