/// <summary>접속내역 조회</summary>
public List<BmConnectLog> GetConnectLogList(BmConnectLog pDataRq)
{
try
{
#region SetQuery
StringBuilder sbQuery = new StringBuilder(@"SELECT *
FROM tbConnectLog
WHERE 1=1");
if (pDataRq.Seq > 0)
sbQuery.AppendLine(" AND Seq = @Seq");
if (!string.IsNullOrEmpty(pDataRq.Ip))
sbQuery.AppendLine(" AND Ip = @Ip");
if (!string.IsNullOrEmpty(pDataRq.LinkUrl))
sbQuery.AppendLine(" AND LinkUrl LIKE '%' + @LinkUrl + '%'");
sbQuery.AppendLine(" ORDER BY RegDt");
#endregion SetQuery
List<BmConnectLog> result = new List<BmConnectLog>();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sbQuery.ToString();
#region Set Parameters
if (pDataRq.Seq > 0)
cmd.Parameters.Add("@Seq", SqlDbType.Int, 0).Value = pDataRq.Seq;
if (string.IsNullOrEmpty(pDataRq.Ip) == false)
cmd.Parameters.Add("@Ip", SqlDbType.VarChar, 15).Value = pDataRq.Ip;
if (string.IsNullOrEmpty(pDataRq.LinkUrl) == false)
cmd.Parameters.Add("@LinkUrl", SqlDbType.VarChar).Value = pDataRq.LinkUrl;
#endregion Set Parameters
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
DataTable dt = new DataTable();
dt.Load(reader);
result = ConvertToBmConnectLog(dt);
}
reader.Dispose();
cmd.Dispose();
return result;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>접속내역 조회</summary>
public List<BmConnectLog> GetConnectLogList(BmConnectLog pDataRq)
{
try
{
using (SqlConn = new SqlConnection(ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
SqlConn.Open();
var result = dac.GetConnectLogList(pDataRq);
scope.Complete();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlConn.Dispose();
}
}
}
}
catch (Exception ex)
{
WriteLog("Exception", ex.Message);
throw;
}
}
public BmConnectLogPagingRq()
{
ConnectLog = new BmConnectLog();
Paging = new BmPaging();
}
/// <summary>세션종료 정보 등록</summary>
public bool UpdateDisConnectLog(BmConnectLog pDataRq)
{
try
{
using (SqlConn = new SqlConnection(ConnectionString))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
SqlConn.Open();
//Session_End에서 호출되기 때문에 세션 정보값이 없음. Session_End에서 값 셋팅
//pDataRq.Seq = (int)System.Web.HttpContext.Current.Session["ConnectSeq"];
var result = dac.UpdateDisConnectLog(pDataRq);
scope.Complete();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlConn.Dispose();
}
}
}
}
catch (Exception ex)
{
WriteLog("Exception", ex.Message);
throw ex;
}
}
/// <summary>접속내역 Convert DataTable -> BmConnectLog</summary>
private List<BmConnectLog> ConvertToBmConnectLog(DataTable dt)
{
List<BmConnectLog> list = new List<BmConnectLog>();
foreach (DataRow dr in dt.Rows)
{
BmConnectLog data = new BmConnectLog();
data.Seq = (int)dr["Seq"];
data.SessionId = dr["SessionId"].ToString();
data.Ip = dr["Ip"].ToString();
data.LinkUrl = dr["LinkUrl"].ToString();
data.Id = dr["Id"].ToString();
data.LoginDate = string.IsNullOrEmpty(dr["LoginDate"].ToString()) ? null : (DateTime?)dr["LoginDate"];
data.ConnectDate = (DateTime)dr["ConnectDate"];
data.DisconnectDate = string.IsNullOrEmpty(dr["DisconnectDate"].ToString()) ? null : (DateTime?)dr["DisconnectDate"];
list.Add(data);
}
return list;
}
/// <summary>로그인 정보 등록</summary>
public bool UpdateLoginLog(BmConnectLog pDataRq)
{
try
{
#region SetQuery
string strQuery = @"UPDATE tbConnectLog SET
Id = @Id,
LoginDate = dbo.getkordate()
WHERE
Seq = @Seq";
#endregion SetQuery
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strQuery.ToString();
#region Set Parameters
cmd.Parameters.Add("@Seq", SqlDbType.Int, 0).Value = pDataRq.Seq;
cmd.Parameters.Add("@Id", SqlDbType.VarChar, 20).Value = pDataRq.Id;
#endregion Set Parameters
result = cmd.ExecuteNonQuery();
cmd.Dispose();
if (result == 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>접속내역 등록 (세션, IP, 경로 등록)</summary>
public int InsertConnectLogScalar(BmConnectLog pDataRq)
{
try
{
#region SetQuery
string strQuery = @"INSERT INTO tbConnectLog
(SessionId
, Ip
, ReconnectYn
, LinkUrl)
Values
(@SessionId
, @Ip
, @ReconnectYn
, @LinkUrl)
SELECT @@Identity";
#endregion SetQuery
int result = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = SqlConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strQuery.ToString();
#region Set Parameters
cmd.Parameters.Add("@ReconnectYn", SqlDbType.Char).Value = pDataRq.ReconnectYn;
cmd.Parameters.Add("@SessionId", SqlDbType.VarChar).Value = pDataRq.SessionId;
cmd.Parameters.Add("@Ip", SqlDbType.VarChar, 15).Value = pDataRq.Ip;
cmd.Parameters.Add("@LinkUrl", SqlDbType.VarChar).Value = pDataRq.LinkUrl;
#endregion Set Parameters
result = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
if (result > 0)
{
return result;
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>로그인</summary>
/// <returns>
/// 0 = 성공
/// , -1 = 비밀번호 오류
/// , -2 = 아이디 오류
/// , -3 = 세션 갱신 실패
/// , -4 = Exception
/// , -5 = 접속로그 오류
/// </returns>
public static int Login(string Id, string Pw)
{
try
{
HttpContext context = HttpContext.Current;
BizMember bizMember = new BizMember();
BmAdminMember bmAdminMember = new BmAdminMember();
bmAdminMember.Id = Id;
bmAdminMember.State = 1;
var AccountList = bizMember.GetAdminMemberList(bmAdminMember);
if (AccountList.Count == 1)
{
BmAdminMember Account = AccountList[0];
if (Pw == Account.Pw)
{
Account.SessionId = context.Session.SessionID;
SessionId = context.Session.SessionID;
if (bizMember.UpdateAdminMemberSession(Account))
{
BizLog bizLog = new BizLog();
BmConnectLog bmConnectLog = new BmConnectLog();
//접속세션이 없을시 접속세션 등록
if (context.Session["ConnectSeq"] == null)
{
bmConnectLog.ReconnectYn = "Y";
bmConnectLog.SessionId = context.Session.SessionID;
bmConnectLog.Ip = context.Request.ServerVariables["REMOTE_ADDR"] != null ? context.Request.ServerVariables["REMOTE_ADDR"] : "";
bmConnectLog.LinkUrl = context.Request.ServerVariables["HTTP_REFERER"] != null ? context.Request.ServerVariables["HTTP_REFERER"] : "";
context.Session["ConnectSeq"] = bizLog.InsertConnectLogScalar(bmConnectLog);
}
bmConnectLog.Seq = (int)context.Session["ConnectSeq"];
bmConnectLog.Id = bmAdminMember.Id;
//접속로그 로그인Id, 시간 업데이트
if (bizLog.UpdateLoginLog(bmConnectLog))
{
//로그인 성공 세션 등록
foreach (string colName in strLoginColumnArray)
{
Type t = Account.GetType();
context.Session.Add("Login" + colName, t.GetProperties().FirstOrDefault(o => o.Name.Equals(colName)).GetValue(Account).ToString());
}
return 0;
}
//접속 로그 오류
else
return -5;
}
else
{
//세션 갱신 실패
return -3;
}
}
else
{
//비밀번호가 다를 경우
return -1;
}
}
else
{
//아이디가 1개가 아닐경우
return -2;
}
}
catch (Exception ex)
{
_cLib.WriteLog("Exception", ex.Message);
return -4;
}
}