EShopSession.cpp

Go to the documentation of this file.

#include <dcl/Config.h>
 
#if __DCL_WINDOWS
#include <windows.h>
#endif
 
#include <dcl/String.h>
#include <dcl/Files.h>
#include <dcl/MD5.h>
#include <dcl/HttpHeader.h>
 
#include "EShopServlet.h"
#include "EShopSession.h"
 
__DCL_BEGIN_NAMESPACE
 
#ifdef __DCL_DEBUG
#undef __THIS_FILE__
static const wchar_t __THIS_FILE__[] = L"eshop/Session.cpp";
#endif
 
EShopSession::EShopSession(
            HttpServletContextEx& ctx,
            SQLConnection* pSQLConn
            ) __DCL_THROWS1(SQLException*)
{
    m_pSQLConn = pSQLConn;
 
    m_nCommand = CM_ABOUT;
    ListedStringToStringArrayMap::Iterator it = ctx.__queryMap.find(COMMAND_STR);
    if ((it != ctx.__queryMap.end()) && (!(*it).value.isEmpty())) {
        try {
            m_nCommand = Int32::parse((*it).value[0]);
        }
        catch(Exception* e) {
            e->destroy();
        }
    }
 
    m_nUserID = 2;
    m_nGroupID = 2;   // 
    m_strSessionID = ctx.__cookieMap[L"sid"];
    if (m_strSessionID.isEmpty()) {
        StringBuilder str = ctx.remoteAddr();
        str += String::valueOf(ctx.remotePort());
        str += String::valueOf(time(NULL));
        m_strSessionID = MD5::final(str);
 
        __DCL_TRACE2(L"sessionId [%zd][%ls]\n",
            m_strSessionID.length(), m_strSessionID.data());
 
        HttpSetCookie cookie(
                    L"sid",
                    m_strSessionID,
                    0,
                    Files::dirname(ctx.path())
                    );
        ctx.addHeader(cookie);
 
        SQLQuery q(pSQLConn);
        q.prepare(L""
            "INSERT INTO ES_SESSION(SESSION_ID, LAST_USE) "
            "   VALUES(:SESSION_ID, CURRENT_TIMESTAMP)"
            );
        q.params()[0].setValue(m_strSessionID);
        q.execute();
//      __DCL_TRACE1(L"%d\n", q.affectedRows());
    }
    else {
        // user info
        SQLQuery q(pSQLConn);
        q.execute(L""
            "SELECT USER_ID "
            "   FROM ES_SESSION "
            "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
            );
        q.fetch();
        if (!q.eof()) {
            q.execute(L""
                "UPDATE ES_SESSION "
                "   SET LAST_USE = CURRENT_TIMESTAMP "
                "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
                );
        }
        else {
//          if (q.affectedRows() == 0)
//          {
            // mysql에서 1초 미만에 update 했더니 affectedRows() 가 0인 경우가 있다.
            // 세션 시간이 초과되어 삭제 되었다.
            // guest 세션을 생성한다.
            q.prepare(L""
                "INSERT INTO ES_SESSION(SESSION_ID, USER_ID, LAST_USE) "
                "   VALUES(:SESSION_ID, :USER_ID, CURRENT_TIMESTAMP)"
                );
 
            int nUserID = 2;
            q.params()[0].setValue(m_strSessionID);
            q.params()[1].setValue(nUserID);  // guest id
            q.execute();
            __DCL_TRACE1(L"%d\n", q.affectedRows());
        }
 
        q.execute(L""
            "SELECT S.USER_ID, U.GROUP_ID, U.USER_NAME "
            "   FROM ES_SESSION S "
            "       INNER JOIN ES_USER U ON (S.USER_ID = U.USER_ID) "
            "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
            );
        q.fetch();
        __DCL_ASSERT(!q.eof());
 
        m_nUserID = q.fields().byName(L"USER_ID").asInteger();
        m_nGroupID = q.fields().byName(L"GROUP_ID").asInteger();
        m_strUserName = q.fields().byName(L"USER_NAME").asString();
    }
}
 
bool EShopSession::login(ListedStringToStringArrayMap& params)
{
    __DCL_ASSERT(m_pSQLConn != NULL);
 
    String strLogin;
    String strPassword = L"";
 
    ListedStringToStringArrayMap::Iterator it = params.find(L"login");
    if ((it != params.end()) && (!(*it).value.isEmpty()))
        strLogin = (*it).value[0];
 
    it = params.find(L"password");
    if ((it != params.end()) && (!(*it).value.isEmpty()))
        strPassword = (*it).value[0];
 
    if (strLogin.isEmpty())
        return false;
 
    SQLQuery q(m_pSQLConn);
    q.prepare(L""
        "SELECT USER_ID FROM ES_USER "
        "   WHERE LOGIN_ID LIKE :LOGIN_ID AND PASSWORD LIKE :PASSWORD"
        );
    q.params()[0].setValue(strLogin);
    q.params()[1].setValue(strPassword);
 
    q.execute();
    q.fetch();
    if (q.eof()) {
        __DCL_TRACE0(L"false\n");
        return false;
    }
 
    int nUserID = 0;
    nUserID = q.fields()[0].asInteger();
 
    __DCL_TRACE1(L"UserID : %d\n", nUserID);
    q.prepare(L""
        "UPDATE ES_SESSION SET USER_ID = :USER_ID "
        "   WHERE SESSION_ID LIKE :SESSION_ID"
        );
 
    q.params()[0].setValue(nUserID);
    q.params()[1].setValue(m_strSessionID);
    q.execute();
 
    q.execute(L""
        "SELECT S.USER_ID, U.GROUP_ID, U.USER_NAME "
        "   FROM ES_SESSION S "
        "       INNER JOIN ES_USER U ON (S.USER_ID = U.USER_ID) "
        "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
        );
    q.fetch();
    __DCL_ASSERT(!q.eof());
 
    m_nUserID = q.fields().byName(L"USER_ID").asInteger();
    m_nGroupID = q.fields().byName(L"GROUP_ID").asInteger();
    m_strUserName = q.fields().byName(L"USER_NAME").asString();
 
    return true;
}
 
void EShopSession::logout()
{
    SQLQuery q(m_pSQLConn);
    q.execute(L""
        "DELETE FROM ES_SHOPPING_BASKET "
        "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
        );
 
    q.execute(L""
        "UPDATE ES_SESSION SET USER_ID = 2 "
        "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
        );
 
    q.execute(L""
        "SELECT S.USER_ID, U.GROUP_ID, U.USER_NAME "
        "   FROM ES_SESSION S "
        "       INNER JOIN ES_USER U ON (S.USER_ID = U.USER_ID) "
        "   WHERE SESSION_ID = \'" + m_strSessionID + L'\''
        );
    q.fetch();
    __DCL_ASSERT(!q.eof());
 
    m_nUserID = q.fields().byName(L"USER_ID").asInteger();
    m_nGroupID = q.fields().byName(L"GROUP_ID").asInteger();
    m_strUserName = q.fields().byName(L"USER_NAME").asString();
}
 
 
void EShopSession::onViewSessions(
            ListedStringToStringArrayMap& params,
            Writer& out,
            const String& strMainCommand,
            int nMainCommandID,
            const String& strCommandID,
            const EShopServlet& _servlet
            )
{
    SQLQuery q(m_pSQLConn);
 
    ListedStringToStringArrayMap::Iterator it = params.find(L"idle");
    if (it != params.end())
    {
        int nMin = 0;
        try
        {
            nMin = Int32::parse(((*it).value)[0]);
        }
        catch(Exception* e)
        {
            e->destroy();
        }
 
        q.prepare(L""
            "DELETE FROM ES_SESSION "
            "WHERE SESSION_ID != :SESSION_ID "
            "   AND (CURRENT_TIMESTAMP - LAST_USE) >= (:MIN * 60) "
            );
        q.params()[0].setValue(sessionID());
        q.params()[1].setValue(nMin);
        q.execute();
    }
 
    TextTemplate tpl(_servlet.readTemplate(L"view_session.html"));
    String strHREF = String::format(L"?%ls=%d",
                    strMainCommand.data(), nMainCommandID
                    );
 
    tpl.assign(L"ACTION_HREF", strHREF);
 
    TextTemplate& row = tpl[L"ROW"];
    q.execute(L""
        "SELECT S.SESSION_ID, U.LOGIN_ID, U.USER_NAME,"
        "   S.LAST_USE, (CURRENT_TIMESTAMP - S.LAST_USE) / 60 AS IDLE_TIME\n"
        "FROM ES_SESSION S\n"
        "   INNER JOIN ES_USER U ON (S.USER_ID = U.USER_ID)\n"
        "ORDER BY S.LAST_USE"
        );
    q.fetch();
    while(!q.eof())
    {
        row.assign(q.fields(), L"&nbsp;");
        tpl.append(L"ROW", row);
        q.fetch();
    }
 
    out << tpl;
}
 
__DCL_END_NAMESPACE