// Decompiled by Jad v1.5.7g. Copyright 2000 Pavel Kouznetsov.
// Jad home page: http://www.geocities.com/SiliconValley/Bridge/8617/jad.html
// Decompiler options: packimports(3) fieldsfirst ansi 
// Source File Name:   UserManager.java

package mediastore.user;

import java.io.PrintStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import mediastore.common.DBConnection;
import mediastore.common.SessionContext;
import mediastore.util.StrUtility;
import mediastore.web.form.UserInfo;

public class UserManager
{

    private String strError;

    public UserManager()
    {
        strError = "";
    }

    public SessionContext login(String strUserName, String strPassword)
    {
        SessionContext sc;
        int nRet;
        sc = null;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        String strSQL = null;
        String strUserID = "";
        String strResID = "";
        try
        {
            dbc = new DBConnection();
            System.out.println("---new DBConnection---");
            conn = dbc.getDBConnection();
            System.out.println("---conn = dbc.getDBConnection();---");
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            strSQL = "SELECT * FROM TabUserInfo WHERE UserName = '" + StrUtility.replaceString(strUserName, "'", "''") + "' and Password = '" + StrUtility.replaceString(strPassword, "'", "''") + "';";
            System.out.println("strSQL:" + strSQL);
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                System.out.println("用戶名或密碼錯誤");
                throw new Exception("用戶名或密碼錯誤");
            }
            System.out.println("用戶名和密碼正確");
            sc = new SessionContext();
            strUserID = rs.getString(1);
            int nRole = rs.getInt(4);
            rs.close();
            rs = null;
            sc.setUser(strUserID, strUserName, nRole);
            strSQL = "UPDATE TabUserInfo SET Status = 1, StatusChangeTime = getdate(), LastLoginTime = getdate() WHERE UserID = " + strUserID + ";";
            System.out.println("strSQL:" + strSQL);
            nRet = stmt.executeUpdate(strSQL);
            if(nRet != 1)
            {
                nRet = -1;
                throw new Exception("無法更新用戶狀態信息");
            }
            conn.commit();
        }
        catch(Exception e)
        {
            strError = e.getMessage();
            nRet = -1;
        }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        if(nRet != -1)
            return sc;
        else
            return null;
    }

    public void logout(String strUserId)
    {
        int nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT * FROM TabUserInfo WHERE UserID = " + strUserId + ";";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("用戶不存在");
            }
            rs.close();
            rs = null;
            strSQL = "UPDATE TabUserInfo SET Status = 0, StatusChangeTime = getdate() WHERE UserID = " + strUserId + ";";
            stmt.executeUpdate(strSQL);
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return;
    }

    public int getTotalUserNum()
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT count(*) FROM TabUserInfo;";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("獲得系統用戶總數失敗");
            }
            nRet = rs.getInt(1);
            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public int getOnlineUserNum()
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT count(*) FROM TabUserInfo WHERE Status = 1;";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("獲得系統用戶總數失敗");
            }
            nRet = rs.getInt(1);
            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public int getOfflineUserNum()
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT count(*) FROM TabUserInfo WHERE Status = 0;";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("獲得系統用戶總數失敗");
            }
            nRet = rs.getInt(1);
            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public List getUserList(String orderField)
    {
        List userList;
        userList = new ArrayList();
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            stmt = conn.createStatement();
            strSQL = "SELECT * FROM TabUserInfo Order by " + orderField + ";";
            UserInfo ui;
            for(rs = stmt.executeQuery(strSQL); rs.next(); userList.add(ui))
            {
                ui = new UserInfo();
                ui.setUserId((new StringBuffer(String.valueOf(rs.getInt(1)))).toString());
                ui.setUserName(rs.getString(2));
                ui.setRole((new StringBuffer(String.valueOf(rs.getInt(4)))).toString());
                ui.setUserStatus((new StringBuffer(String.valueOf(rs.getInt(5)))).toString());
                ui.setCreateTime(rs.getString(6));
                ui.setLastStatusChangeTime(rs.getString(7));
                ui.setLastLoginTime(rs.getString(8));
            }

            rs.close();
            rs = null;
        }
        catch(Exception exception) { }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return userList;
    }

    public int deleteUserRec(String userId)
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            String strSQL = "DELETE FROM TabUserInfo WHERE UserID = " + userId;
            stmt = conn.createStatement();
            nRet = stmt.executeUpdate(strSQL);
            if(nRet != 1)
            {
                strError = "記錄不存在";
                nRet = -1;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
            strError = e.getMessage();
            nRet = -1;
        }
        finally
        {
            try
            {
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException e) { }
        }
        return nRet;
    }

    public int insertUserRec(String userName, String password, String role)
    {
        int nRet;
        nRet = 0;
        DBConnection dbc = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String strSQL = null;
        try
        {
            dbc = new DBConnection();
            conn = dbc.getDBConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            strSQL = "SELECT MAX(UserID) FROM TabUserInfo";
            rs = stmt.executeQuery(strSQL);
            if(!rs.next())
            {
                nRet = -1;
                throw new Exception("沒有找到相應記錄");
            }
            int userId = rs.getInt(1) + 1;
            rs.close();
            rs = null;
            strSQL = "insert into TabUserInfo( UserID, UserName, Password, Role, Status, CreateTime, StatusChangeTime) values (" + userId + ", '" + StrUtility.replaceString(userName, "'", "''") + "', '" + StrUtility.replaceString(password, "'", "''") + "', " + role + ", 0, getdate(), getdate())";
            nRet = stmt.executeUpdate(strSQL);
            if(nRet != 1)
            {
                nRet = -1;
                throw new Exception("插入新記錄錯誤");
            }
            conn.commit();
        }
        catch(Exception e)
        {
            strError = e.getMessage();
            nRet = -1;
        }
        finally
        {
            try
            {
                if(stmt != null)
                    stmt.close();
                if(rs != null)
                    rs.close();
                if(conn != null)
                    dbc.closeDBConnection(conn);
            }
            catch(SQLException ex) { }
        }
        return nRet;
    }

    public String getLastError()
    {
        return strError;
    }
}