roomresourcemanager.java

openfire whiteboard plugin

/*

 *
 * Copyright (C) GNU/GPL AVOIR 2008
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation; either version 2 of the License, or (at your option) any later
 * version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc., 51
 * Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

 */
package org.avoir.realtime.plugins;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.QName;
import org.jivesoftware.database.DbConnectionManager;
import org.xmpp.packet.IQ;

/**
 *
 * @author developer
 */
public class RoomResourceManager {

    private Connection con = null;
    private PreparedStatement ps = null;

    private String getRoomResourcesContent(String roomName) {

        StringBuilder sb = new StringBuilder();
        sb.append("<fileview>");
        try {
            con = DbConnectionManager.getConnection();
            String resultSQL =
                    "select * from ofAvoirRealtime_Classroom_SlideShows where" +
                    " room_name = '" + roomName + "'";
            ps = con.prepareStatement(resultSQL);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sb.append("<file>");
                String fPath = rs.getString("file_path");
                int ver = rs.getInt("version");
                sb.append("<file-name>").append(new File(fPath).getName()).append("</file-name>");
                sb.append("<file-path>").append(fPath).append("</file-path>");
                sb.append("<is-directory>").append("false").append("</is-directory>");
                sb.append("<access>").append("private").append("</access>");
                sb.append("<version>").append(ver).append("</version>");
                sb.append("</file>");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        sb.append("</fileview>");
        return sb.toString();
    }

    public int getRoomResourceRoomVersion(String filePath) {

        int version = 0;
        try {
            con = DbConnectionManager.getConnection();
            String resultSQL =
                    "select version from ofAvoirRealtime_Classroom_SlideShows where" +
                    " file_path = '" + filePath + "'";
            ps = con.prepareStatement(resultSQL);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                version = rs.getInt("version");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return version;
    }

    private String getRoomResourceRoomName(String filePath) {
        String roomName = null;
        try {
            con = DbConnectionManager.getConnection();
            String resultSQL =
                    "select room_name from ofAvoirRealtime_Classroom_SlideShows where" +
                    " file_path = '" + filePath + "'";
            ps = con.prepareStatement(resultSQL);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                roomName = rs.getString("room_name");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return roomName;
    }

    public IQ getRoomResources(IQ packet, String roomName) {
        IQ replyPacket = IQ.createResultIQ(packet);
        Element queryResult = DocumentHelper.createElement(QName.get("query", Constants.NAME_SPACE));
        queryResult.addElement("mode").addText(Mode.SLIDE_SHOW_ROOM_RESOURCES);
        queryResult.addElement("content").addText(getRoomResourcesContent(roomName));
        replyPacket.setChildElement(queryResult);
        return replyPacket;
    }

    public IQ addSlideShowAsRoomResource(IQ packet, String roomName, String filePath) {
        try {
            con = DbConnectionManager.getConnection();
            String sql =
                    "insert into ofAvoirRealtime_Classroom_SlideShows values " +
                    "('" + filePath + "','" + roomName + "',0)";

            ps = con.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

        return getRoomResources(packet, roomName);
    }

    public void updateSlideShowRoomResourcePath(String oldPath, String newFilePath) {
        try {
            con = DbConnectionManager.getConnection();

            String sql =
                    "update ofAvoirRealtime_Classroom_SlideShows set file_path ='" + newFilePath + "'" +
                    " where file_path = '" + oldPath + "'";

           
            ps = con.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }


    }

    public IQ increaseSlideShowRoomResourceVersion(IQ packet, String filePath) {
        try {
            con = DbConnectionManager.getConnection();

            String sql =
                    "select * from ofAvoirRealtime_Classroom_SlideShows where" +
                    " file_path = '" + filePath + "'";
            System.out.println(sql);
            ps = con.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();
            int existingVer = 0;
            while (rs.next()) {
                existingVer = rs.getInt("version");
            }
           
            sql =
                    "update ofAvoirRealtime_Classroom_SlideShows set version =" + (existingVer + 1) + "" +
                    " where file_path = '" + filePath + "'";

           
            ps = con.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

        return getRoomResources(packet, getRoomResourceRoomName(filePath));
    }
}