/*
 * $Id: VKCNoteSerializer.java,v 1.2 2007/08/08 12:14:41 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 * 
 * $Log: VKCNoteSerializer.java,v $
 * Revision 1.2  2007/08/08 12:14:41  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 */
package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import java.sql.Connection;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.util.StringUtils;
import vkc.view.WebShare;

public class VKCNoteSerializer {
    private final static int MAX_TEXTCOL_SIZE = 4000;

    public VKCNoteSerializer() {
    }

    /**
     * Gibt anhand der mitgelieferten Id einen Beschreibungsstring zurück
     * 
     * @param noteId
     *            AnfangsId des Beschreibungs
     * @return Beschreibungsstring
     * @throws DBException
     */
    public final static String retrieveNote(Integer noteId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();

            return retrieveNote(noteId, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Gibt einen Beschreibungsstring zurück anhand der mitgelieferten Id
     * 
     * @param noteId
     *            AnfangsId des Beschreibungs
     * @param conn
     *            Verbindung zum Datenbank
     * @return Beschreibungsstring
     * @throws DBException
     */
    public final static String retrieveNote(Integer noteId, Connection conn) throws DBException {
        if (noteId == null) {
            return null;
        }
        
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Integer successorId = null;
        String note = "";

        try {
            String sqlStmt = "SELECT notes.noteId, notes.successorId, notes.text FROM notes WHERE notes.noteId = ?";
            pStmt = conn.prepareStatement(sqlStmt);
            pStmt.setInt(1, noteId.intValue());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                note += rs.getString("text");
                successorId = new Integer(rs.getInt("successorId"));
                
                if (!rs.wasNull()) {
                    DBHelper.close(rs);
                    pStmt.setInt(1, successorId.intValue());
                    rs = pStmt.executeQuery();
                }
            }

            return note;
        } catch (SQLException e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    /**
     * 
     * Speichert eine Beschreibung
     * 
     * @param objectId
     *            Id des beschriebenen Objektes
     * @param type
     *            Type des beschriebenen Objektes(Documents, Categories usw. )
     * @param text
     *            Beschreibung
     * @return AnfangsId der Beschreibung
     * @throws DBException
     * @throws SQLException
     */
    public static Integer saveNote(Integer objectId, String type, String text) throws DBException, SQLException {
        Connection conn = null;
        Integer noteId = null;

        try {
            conn = WebShare.database.getConnection();
            noteId = saveNote(objectId, type, text, conn);
            conn.commit();

            return noteId;
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * 
     * Speichert eine Beschreibung
     * 
     * @param objectId
     *            Id des beschriebenen Objektes
     * @param type
     *            Type des beschriebenen Objektes(Documents, Categories usw. )
     * @param text
     *            Beschreibung
     * @return AnfangsId der Beschreibung
     * @throws DBException
     * @throws SQLException
     */
    public static Integer saveNote(Integer objectId, String type, String text, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        Integer startId = null;
        
        if (objectId == null || StringUtils.isEmpty(type) || StringUtils.isEmpty(text)) {
            return null;
        }
        
        try {
            deleteNote(objectId, type, conn);
            pStmt = conn.prepareStatement("INSERT INTO notes ( noteId, objectId, sourceTable, successorId, text) VALUES ( ?, ?, ?, ?, ? )");

            int length = (int)Math.ceil((double)text.length() / MAX_TEXTCOL_SIZE);
            String[] fullNote = new String[length];
            
            // Beschreibung auf 4000 Zeichen große Blöcke aufteilen und in ein Array packen
            for (int i = 0; i < length; i++) {
                int start = i * MAX_TEXTCOL_SIZE;
                int end = (i + 1) * MAX_TEXTCOL_SIZE;
                
                if (text.length() < end) {
                    end = text.length();
                }
                
                fullNote[i] = text.substring(start, end);
            }
            
            startId = DBHelper.getNewId(conn, "notes", "noteId");
            Integer id = startId;
            
            for (int i = 0; i < fullNote.length; i++) {
                pStmt.setInt(1, id.intValue());
                pStmt.setInt(2, objectId.intValue());
                pStmt.setString(3, type);

                if (i == fullNote.length - 1) {
                    pStmt.setNull(4, Types.INTEGER);
                } else {
                    //Es gibt nachfolger
                    id = DBHelper.getNewId(conn, "notes", "noteId");
                    pStmt.setInt(4, id.intValue());
                }
                
                pStmt.setString(5, fullNote[i]);
                pStmt.execute();
            }
            
            return startId;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Löscht Beschreibung zum angegebenen Objekt
     * 
     * @param objectId
     *            Id des Objektes
     * @param sourceTabele
     *            Type des Objektes(zz NEWS, DOCUMENTS, CATEGORIES)
     * @throws DBException
     */
    public static void deleteNote(Integer objectId, String sourceTabele) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteNote(objectId, sourceTabele, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    /**
     * Löscht Beschreibung zum angegebenen Objekt
     * 
     * @param objectId
     *            Id des Objektes
     * @param sourceTabele
     *            Type des Objektes(zz NEWS, DOCUMENTS, CATEGORIES)
     * @throws DBException
     */
    public static void deleteNote(Integer objectId, String sourceTabele, Connection conn) throws DBException {
        if (objectId == null || StringUtils.isEmpty(sourceTabele)) {
            return;
        }
        
        PreparedStatement pStmt = null;
        
        try {
            pStmt = conn.prepareStatement("DELETE FROM notes WHERE notes.sourceTable = ? AND notes.objectId = ?");
            pStmt.setString(1, sourceTabele);
            pStmt.setInt(2, objectId.intValue());
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }
}