public static void SerializeObject(SessionInfo sInfo, string token, string username, string sys_code, System.Web.HttpRequest req, string client_ip = "", string client_browser = "", string browser_version = "", string server_url = "")
{
if (sInfo == null) { return; }
try
{
string sql = "select top 1 id ";
sql += " from SSO_SESSION_INFO";
sql += " where token=@_TOKEN";
SqlParameter[] param = new SqlParameter[1];
param[0] = SharedDB.setParameter("@_TOKEN", SqlDbType.VarChar, token);
DataTable dt = SharedDB.GetDatatable(sql, param);
if (dt.Rows.Count == 0)
{
sql = "insert into SSO_SESSION_INFO(created_by,created_date,token, person_id,per_type,per_type_name, ";
sql += " full_name_th, first_name_th,last_name_th,full_name_en,first_name_en,last_name_en, ";
sql += " prefix_id,prefix_name_th,prefix_name_en,position_id,position_name_th,position_name_en, ";
sql += " org_serial, org_name_th, org_name_en, level_id,level_name_th,level_name_en,";
sql += " last_access_time, session_expire_time, sys_code,user_code, per_id_dpis,client_ip,client_browser,server_url,service_url)";
sql += " output inserted.id";
sql += " values (@_USERNAME, getdate(),@_TOKEN, @_PERSON_ID, @_PER_TYPE, @_PER_TYPE_NAME, ";
sql += " @_FULL_NAME_TH, @_FIRST_NAME_TH, @_LAST_NAME_TH, @_FULL_NAME_EN, @_FIRST_NAME_EN, @_LAST_NAME_EN, ";
sql += " @_PREFIX_ID, @_PREFIX_NAME_TH, @_PREFIX_NAME_EN, @_POSITION_ID, @_POSITION_NAME_TH, @_POSITION_NAME_EN, ";
sql += " @_ORG_SERIAL, @_ORG_NAME_TH, @_ORG_NAME_EN, @_LEVEL_ID, @_LEVEL_NAME_TH, @_LEVEL_NAME_EN, ";
sql += " @_LAST_ACCESS_TIME, @_SESSION_EXPIRE_TIME, @_SYS_CODE,@_USER_CODE,@_PER_ID_DPIS,@_CLIENT_IP,@_CLIENT_BROWSER,@_SERVER_URL,@_SERVICE_URL)";
param = new SqlParameter[32];
param[0] = SharedDB.setParameter("@_USERNAME", SqlDbType.VarChar, username);
param[1] = SharedDB.setParameter("@_TOKEN", SqlDbType.VarChar, token);
param[2] = SharedDB.setParameter("@_PERSON_ID", SqlDbType.VarChar, sInfo.UserId);
param[3] = SharedDB.setParameter("@_PER_TYPE", SqlDbType.VarChar, sInfo.PerType);
param[4] = SharedDB.setParameter("@_PER_TYPE_NAME", SqlDbType.VarChar, sInfo.PerTypeName);
param[5] = SharedDB.setParameter("@_FULL_NAME_TH", SqlDbType.VarChar, sInfo.FullNameTH);
param[6] = SharedDB.setParameter("@_FIRST_NAME_TH", SqlDbType.VarChar, sInfo.FirstNameTH);
param[7] = SharedDB.setParameter("@_LAST_NAME_TH", SqlDbType.VarChar, sInfo.LastNameTH);
param[8] = SharedDB.setParameter("@_FULL_NAME_EN", SqlDbType.VarChar, sInfo.FullNameEN);
param[9] = SharedDB.setParameter("@_FIRST_NAME_EN", SqlDbType.VarChar, sInfo.FirstNameEN);
param[10] = SharedDB.setParameter("@_LAST_NAME_EN", SqlDbType.VarChar, sInfo.LastNameEN);
param[11] = SharedDB.setParameter("@_PREFIX_ID", SqlDbType.VarChar, sInfo.PrefixId);
param[12] = SharedDB.setParameter("@_PREFIX_NAME_TH", SqlDbType.VarChar, sInfo.PrefixNameTH);
param[13] = SharedDB.setParameter("@_PREFIX_NAME_EN", SqlDbType.VarChar, sInfo.PrefixNameEN);
param[14] = SharedDB.setParameter("@_POSITION_ID", SqlDbType.VarChar, sInfo.PositionId);
param[15] = SharedDB.setParameter("@_POSITION_NAME_TH", SqlDbType.VarChar, sInfo.PositionNameTH);
param[16] = SharedDB.setParameter("@_POSITION_NAME_EN", SqlDbType.VarChar, sInfo.PositionNameEN);
param[17] = SharedDB.setParameter("@_ORG_SERIAL", SqlDbType.BigInt, Convert.ToInt64(sInfo.OrgId));
param[18] = SharedDB.setParameter("@_ORG_NAME_TH", SqlDbType.VarChar, sInfo.OrgNameTH);
param[19] = SharedDB.setParameter("@_ORG_NAME_EN", SqlDbType.VarChar, sInfo.OrgNameEN);
param[20] = SharedDB.setParameter("@_LEVEL_ID", SqlDbType.VarChar, sInfo.LevelId);
param[21] = SharedDB.setParameter("@_LEVEL_NAME_TH", SqlDbType.VarChar, sInfo.LevelNameTH);
param[22] = SharedDB.setParameter("@_LEVEL_NAME_EN", SqlDbType.VarChar, sInfo.LevelNameEN);
param[23] = SharedDB.setParameter("@_LAST_ACCESS_TIME", SqlDbType.DateTime, sInfo.LastAccessDateTime);
param[24] = SharedDB.setParameter("@_SESSION_EXPIRE_TIME", SqlDbType.DateTime, sInfo.SessionExpireDateTime);
param[25] = SharedDB.setParameter("@_SYS_CODE", SqlDbType.VarChar, sys_code);
param[26] = SharedDB.setParameter("@_USER_CODE", SqlDbType.VarChar, username);
param[27] = SharedDB.setParameter("@_PER_ID_DPIS", SqlDbType.BigInt, Convert.ToInt64(sInfo.PerIDDPIS));
param[28] = SharedDB.setParameter("@_CLIENT_IP", SqlDbType.VarChar, client_ip);
param[29] = SharedDB.setParameter("@_CLIENT_BROWSER", SqlDbType.VarChar, "Browser : " + client_browser + " Version : " + browser_version);
param[30] = SharedDB.setParameter("@_SERVER_URL", SqlDbType.VarChar, server_url);
param[31] = SharedDB.setParameter("@_SERVICE_URL", SqlDbType.VarChar, req.Url.AbsoluteUri);
}
else
{
sql = "update SSO_SESSION_INFO";
sql += " set updated_by=@_USERNAME";
sql += ",updated_date=getdate()";
sql += ",last_access_time = @_LAST_ACCESS_TIME";
sql += ", session_expire_time = @_SESSION_EXPIRE_TIME";
sql += ", sys_code = @_SYS_CODE";
sql += " output inserted.id ";
sql += " where token=@_TOKEN";
param = new SqlParameter[5];
param[0] = SharedDB.setParameter("@_USERNAME", SqlDbType.VarChar, username);
param[1] = SharedDB.setParameter("@_TOKEN", SqlDbType.VarChar, token);
param[2] = SharedDB.setParameter("@_LAST_ACCESS_TIME", SqlDbType.DateTime, sInfo.LastAccessDateTime);
param[3] = SharedDB.setParameter("@_SESSION_EXPIRE_TIME", SqlDbType.DateTime, sInfo.SessionExpireDateTime);
param[4] = SharedDB.setParameter("@_SYS_CODE", SqlDbType.VarChar, sys_code);
}
SharedDB.GetDatatable(sql, param);
}
catch (Exception ex)
{
//Log exception here
LogFileEng.CreateErrorLog("Exception " + ex.Message + Environment.NewLine + ex.StackTrace, "BappAuthenticate_SerializeObject", client_ip);
}
}
private static SessionInfo DeSerializeObject(string token, string client_ip)
{
if (string.IsNullOrEmpty(token)) { return null; }
SessionInfo objectOut = null;
try
{
string sql = "select token,person_id,per_type,per_type_name, user_code , " + Environment.NewLine;
sql += " isnull(full_name_th,'') FullNameTH, isnull(first_name_th,'') FirstNameTH, isnull(last_name_th,'') LastNameTH, " + Environment.NewLine;
sql += " isnull(full_name_en,'') FullNameEN, isnull(first_name_en,'') FirstNameEN, isnull(last_name_en,'') LastNameEN, " + Environment.NewLine;
sql += " isnull(prefix_id,'') PrefixID, isnull(prefix_name_th,'') PrefixNameTH, isnull(prefix_name_en,'') PrefixNameEN, " + Environment.NewLine;
sql += " isnull(position_id,'') PositionID, isnull(position_name_th,'') PositionNameTH, isnull(position_name_en,'') PositionNameEN, " + Environment.NewLine;
sql += " isnull(SSO_SESSION_INFO.org_serial,'') OrgSerial,isnull(org_name_th,'') OrgNameTH, isnull(org_name_en,'') OrgNameEN, " + Environment.NewLine;
sql += " isnull(level_id,'') LevelID, isnull(level_name_th,'') LevelNameTH, isnull(level_name_en,'') LevelNameEN, " + Environment.NewLine;
sql += " created_date, last_access_time,session_expire_time, isnull(per_id_dpis,0) per_id_dpis,ctlt_organize.org_full_abbr " + Environment.NewLine;
sql += " from SSO_SESSION_INFO " + Environment.NewLine;
sql += " inner join ctlt_organize on SSO_SESSION_INFO.org_serial = ctlt_organize.org_serial ";
sql += " where token=@_TOKEN " + Environment.NewLine;
SqlParameter[] param = new SqlParameter[1];
param[0] = SharedDB.setParameter("@_TOKEN", SqlDbType.VarChar, token);
DataTable dt = SharedDB.GetDatatable(sql, param);
if (dt.Rows.Count > 0)
{
objectOut = new SessionInfo()
{
UserId = dt.Rows[0]["person_id"].ToString(),
PerType = dt.Rows[0]["per_type"].ToString(),
PerTypeName = dt.Rows[0]["per_type_name"].ToString(),
Username = dt.Rows[0]["user_code"].ToString(),
OfficerId = dt.Rows[0]["person_id"].ToString(),
FullNameTH = dt.Rows[0]["FullNameTh"].ToString(),
FirstNameTH = dt.Rows[0]["FirstNameTh"].ToString(),
LastNameTH = dt.Rows[0]["LastNameTh"].ToString(),
FullNameEN = dt.Rows[0]["FullNameEn"].ToString(),
FirstNameEN = dt.Rows[0]["FirstNameEN"].ToString(),
LastNameEN = dt.Rows[0]["LastNameEn"].ToString(),
PrefixId = dt.Rows[0]["PrefixID"].ToString(),
PrefixNameTH = dt.Rows[0]["PrefixNameTh"].ToString(),
PrefixNameEN = dt.Rows[0]["PrefixNameEn"].ToString(),
PositionId = dt.Rows[0]["PositionID"].ToString(),
PositionNameTH = dt.Rows[0]["PositionNameTh"].ToString(),
PositionNameEN = dt.Rows[0]["PositionNameEn"].ToString(),
OrgId = dt.Rows[0]["OrgSerial"].ToString(),
OrgNameTH = dt.Rows[0]["OrgNameTh"].ToString(),
OrgAbbr = dt.Rows[0]["org_full_abbr"].ToString(),
OrgNameEN = dt.Rows[0]["OrgNameEn"].ToString(),
LevelId = dt.Rows[0]["LevelID"].ToString(),
LevelNameTH = dt.Rows[0]["LevelNameTh"].ToString(),
LevelNameEN = dt.Rows[0]["LevelNameEn"].ToString(),
ImageUrl = getImageURL(dt.Rows[0]["per_id_dpis"].ToString()),
PerIDDPIS = dt.Rows[0]["per_id_dpis"].ToString(),
StartDateTime = Convert.ToDateTime(dt.Rows[0]["created_date"]),
LastAccessDateTime = Convert.ToDateTime(dt.Rows[0]["last_access_time"]),
SessionExpireDateTime = Convert.ToDateTime(dt.Rows[0]["session_expire_time"])
};
}
dt.Dispose();
}
catch (Exception ex)
{
LogFileEng.CreateErrorLog("Exception " + ex.Message + Environment.NewLine + ex.StackTrace, "BappAuthenticate_DeSerializeObject", client_ip);
}
return objectOut;
}
public static string BappLogin(string user_login, string password, string system, string client_ip, string client_browser, string browser_version, string server_url, System.Web.HttpRequest req)
{
string sessionId = "";
//select user info
string sql = "select id UserID, isnull(user_code,'') username, id OfficerID, " + Environment.NewLine;
sql += " per_type, per_type_name," + Environment.NewLine;
sql += " isnull(prefix_name,'') + isnull(name,'') + ' ' + isnull(surname,'') FullNameTh, " + Environment.NewLine;
sql += " isnull(name,'') FirstNameTh, isnull(surname,'') LastNameTh, " + Environment.NewLine;
sql += " isnull(prefix_eng,'')+ isnull(name_eng,'') + ' ' + isnull(surname_eng,'') FullNameEn, " + Environment.NewLine;
sql += " isnull(name_eng,'') FirstNameEn, isnull(surname_eng,'') LastNameEn, " + Environment.NewLine;
sql += " isnull(prefix_code,'') PrefixID, isnull(prefix_name,'') PrefixNameTh,isnull(prefix_eng,'') PrefixNameEn, " + Environment.NewLine;
sql += " pos_id PositionID, isnull(line_name,'') PositionNameTh, isnull(line_eng,'') PositionNameEn, " + Environment.NewLine;
sql += " org_serial OrgID, isnull(org_name,'') OrgNameTh, isnull(org_eng_name,'') OrgNameEn, " + Environment.NewLine;
sql += " cur_lev LevelID, isnull(level_name,'') LevelNameTh, isnull(level_eng,'') LevelNameEn, user_pwd, per_id_dpis " + Environment.NewLine;
sql += " from vw_CMN_PERSON " + Environment.NewLine;
sql += " where per_status='1' " + Environment.NewLine; // --สถานภาพ(1-ปัจจุบัน 2-พ้นสภาพ)";
sql += " and user_login = @_USER_LOGIN";
SqlParameter[] param = new SqlParameter[1];
param[0] = SharedDB.setParameter("@_USER_LOGIN", SqlDbType.VarChar, user_login);
DataTable dt = SharedDB.GetDatatable(sql, param);
if (dt.Rows.Count > 0)
{
string pwdEncrypt = password;
dt.DefaultView.RowFilter = String.Format("user_pwd = '{0}'", pwdEncrypt.Replace("'", "''"));
if (dt.DefaultView.Count > 0)
{
sessionId = Guid.NewGuid().ToString();
string sessionExpire = SharedDB.GetSysconfig("LoginSessionExpireMin");
if (sessionExpire == "")
sessionExpire = "30";
SessionInfo sessionInfo = new SessionInfo()
{
UserId = dt.Rows[0]["UserID"].ToString(),
PerType = dt.Rows[0]["per_type"].ToString(),
PerTypeName = dt.Rows[0]["per_type_name"].ToString(),
Username = dt.Rows[0]["username"].ToString(),
OfficerId = dt.Rows[0]["OfficerID"].ToString(),
FullNameTH = dt.Rows[0]["FullNameTh"].ToString(),
FirstNameTH = dt.Rows[0]["FirstNameTh"].ToString(),
LastNameTH = dt.Rows[0]["LastNameTh"].ToString(),
FullNameEN = dt.Rows[0]["FullNameEn"].ToString(),
FirstNameEN = dt.Rows[0]["FirstNameEn"].ToString(),
LastNameEN = dt.Rows[0]["LastNameEn"].ToString(),
PrefixId = dt.Rows[0]["PrefixID"].ToString(),
PrefixNameTH = dt.Rows[0]["PrefixNameTh"].ToString(),
PrefixNameEN = dt.Rows[0]["PrefixNameEn"].ToString(),
PositionId = dt.Rows[0]["PositionID"].ToString(),
PositionNameTH = dt.Rows[0]["PositionNameTh"].ToString(),
PositionNameEN = dt.Rows[0]["PositionNameEn"].ToString(),
OrgId = dt.Rows[0]["OrgID"].ToString(),
OrgNameTH = dt.Rows[0]["OrgNameTh"].ToString(),
OrgNameEN = dt.Rows[0]["OrgNameEn"].ToString(),
LevelId = dt.Rows[0]["LevelID"].ToString(),
LevelNameTH = dt.Rows[0]["LevelNameTh"].ToString(),
LevelNameEN = dt.Rows[0]["LevelNameEn"].ToString(),
PerIDDPIS = dt.Rows[0]["per_id_dpis"].ToString(),
ImageUrl = getImageURL(dt.Rows[0]["per_id_dpis"].ToString()),
StartDateTime = DateTime.Now,
LastAccessDateTime = DateTime.Now,
SessionExpireDateTime = DateTime.Now.AddMinutes(Convert.ToInt16(sessionExpire))
};
SerializeObject(sessionInfo, sessionId, sessionInfo.Username, system, req, client_ip, client_browser, browser_version, server_url);
BappLog(sessionId, system, "LOGIN", "LOGIN", "Success", "", "", "", client_ip, client_browser, browser_version, server_url, req);
}
else
{
BappLog(sessionId, system, "LOGIN", "LOGIN", "Fail:Invalid password", "", "", "", client_ip, client_browser, browser_version, server_url, req);
}
}
else
{
BappLog(sessionId, system, "LOGIN", "LOGIN", "Fail:User not found", "", "", "", client_ip, client_browser, browser_version, server_url, req);
}
return sessionId;
}