/*
 * $Id: VKCNewsSerializer.java,v 1.18 2007/08/08 12:14:42 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCNewsSerializer.java,v $
 * Revision 1.18  2007/08/08 12:14:42  marbobo
 * + License-Funktionalität komplett umgeschrieben
 * + Bugfix bzgl. Beschreibungsfelder von Entitäten
 * + Bugfix mailing
 *
 * Revision 1.17  2007/03/01 15:02:56  korowa
 * Beschreibungen zur Kategorien und Dokumenten und News werden jetzt in eigener Tabelle NOTES über VKCNoteSerializer gespeichert und sind nicht begrenzt an Zeichenanzahl
 */
package vkc.db.serializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

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

public class VKCNewsSerializer {
    private VKCNewsSerializer() {
    }

    public final static void saveNews(VKCNews news) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveNews(news, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static void saveNews(VKCNews news, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            int newsCount = VKCSysParameterSerializer.retrieveIntValue("NEWS", "MaxCountNews", 20);

            // delete the oldest entry, if count greater than "preDefinedCount - 1"
            if (VKCNewsSerializer.getCountNews(conn) > (newsCount - 1)) {
                pStmt = conn.prepareStatement("SELECT newsId FROM News WHERE created <= ( SELECT min(created) From news)");
                rs = pStmt.executeQuery();
                Integer newsId = null;
                while(rs.next()){
                    newsId = new Integer(rs.getInt("newsid"));
                }
                if (newsId!=null) {
                    VKCNoteSerializer.deleteNote(newsId, "NEWS", conn);
                }

                DBHelper.close(rs);
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("DELETE FROM news WHERE created <= ( SELECT min(created) FROM news )");
                pStmt.execute();
            }

            if (news.getNewsId() == null || news.getNewsId().intValue() <= 0) {
                Integer newsId = DBHelper.getNewId(conn, "news", "newsId");
                news.setNewsId(newsId);

                //NoteId in abh. von der Beschreibung setzen
                if (!StringUtils.isEmpty(news.getNewsText())) {
                    news.setNoteId(VKCNoteSerializer.saveNote(news.getNewsId(), "NEWS", news.getNewsText(), conn));
                } else {
                    news.setNoteId(null);
                }
                
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("INSERT INTO news ( newsId, headline, noteId, infoUrl, validFrom, validUntil, created, creatorId ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )");
                pStmt.setInt(1, newsId.intValue());
                pStmt.setString(2, news.getHeadline());
                
                if (news.getNoteId() != null) {
                    pStmt.setInt(3, news.getNoteId().intValue());
                } else {
                    pStmt.setNull(3, Types.INTEGER);
                }

                if (news.getInfoUrl() != null) {
                    pStmt.setString(4, news.getInfoUrl());
                } else {
                    pStmt.setNull(4, Types.VARCHAR);
                }

                if (news.getValidFrom() != null) {
                    pStmt.setTimestamp(5, news.getValidFrom());
                } else {
                    pStmt.setNull(5, Types.TIMESTAMP);
                }

                if (news.getValidUntil() != null) {
                    pStmt.setTimestamp(6, news.getValidUntil());
                } else {
                    pStmt.setNull(6, Types.TIMESTAMP);
                }

                pStmt.setTimestamp(7, new Timestamp(System.currentTimeMillis()));
                pStmt.setInt(8, news.getCreatorId().intValue());
            } else {
                //NoteId in abh. von der Beschreibung setzen
                if (!StringUtils.isEmpty(news.getNewsText())) {
                    news.setNoteId(VKCNoteSerializer.saveNote(news.getNewsId(), "NEWS", news.getNewsText(), conn));
                } else {
                    news.setNoteId(null);
                }
                
                DBHelper.close(pStmt);
                pStmt = conn.prepareStatement("UPDATE news SET headline = ?, noteId = ?, infoUrl = ?, validFrom  = ?, validUntil = ? WHERE newsId = ?");
                pStmt.setString(1, news.getHeadline());

                if (news.getNoteId() != null) {
                    pStmt.setInt(2, news.getNoteId().intValue());
                } else {
                    pStmt.setNull(2, Types.INTEGER);
                }


                if (news.getInfoUrl() != null) {
                    pStmt.setString(3, news.getInfoUrl());
                } else {
                    pStmt.setNull(3, Types.VARCHAR);
                }

                if (news.getValidFrom() != null) {
                    pStmt.setTimestamp(4, news.getValidFrom());
                } else {
                    pStmt.setNull(4, Types.TIMESTAMP);
                }

                if (news.getValidUntil() != null) {
                    pStmt.setTimestamp(5, news.getValidUntil());
                } else {
                    pStmt.setNull(5, Types.TIMESTAMP);
                }

                pStmt.setInt(6, news.getNewsId().intValue());
            }

            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public final static void deleteNews(int newsId) throws DBException {
        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            deleteNews(newsId, conn);
            conn.commit();
        } catch (Exception ex) {
            DBHelper.rollBack(conn);
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static void deleteNews(int newsId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        try {
            VKCNoteSerializer.deleteNote(new Integer(newsId), "NEWS", conn);
            pStmt = conn.prepareStatement("DELETE FROM news WHERE newsid = ?");
            pStmt.setInt(1, newsId);
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    public final static ArrayList retrieveNews() throws DBException {
        return retrieveNews(VKCSysParameterSerializer.retrieveIntValue("NEWS", "MaxCountNews", 20), null, null);
    }

    public final static ArrayList retrieveNews(int maxCount) throws DBException {
        return retrieveNews(maxCount, null, null);
    }

    public final static ArrayList retrieveNews(int maxCount, Timestamp from, Timestamp until) throws DBException {
        Connection conn = null;

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

            return retrieveNews(maxCount, from, until, conn);
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static ArrayList retrieveNews(int maxCount, Timestamp from, Timestamp until, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ArrayList newsList = new ArrayList();
        List params = new ArrayList();
        int currentCountSets = 0;

        try {
            String sqlStmt = "SELECT news.newsId, news.headline, news.infoUrl, news.validFrom, news.validUntil, news.created, news.creatorId, news.noteId, users.username, users.firstname, users.lastname "
                           + "  FROM news, users "
                           + " WHERE news.creatorId = users.userId";

            if (from != null) {
                sqlStmt += " AND (news.validFrom >= ? OR news.validFrom IS NULL)";
                params.add(from);
            }

            if (until != null) {
                sqlStmt += " AND (news.validUntil <= ? OR news.validUntil IS NULL)";
                params.add(until);
            }

            sqlStmt += " ORDER BY newsId DESC";

            pStmt = conn.prepareStatement(sqlStmt);
            DBHelper.setElements(pStmt, params.toArray());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                VKCNews news = new VKCNews();

                news.setNewsId(new Integer(rs.getInt("newsId")));
                news.setHeadline(rs.getString("headline"));
                news.setInfoUrl(rs.getString("infoUrl"));
                news.setValidFrom(rs.getTimestamp("validFrom"));
                news.setValidUntil(rs.getTimestamp("validUntil"));
                news.setCreated(rs.getTimestamp("created"));
                news.setCreatorId(new Integer(rs.getInt("creatorId")));
                news.setNewsText(VKCNoteSerializer.retrieveNote(new Integer(rs.getInt("noteId")), conn));

                if (rs.wasNull() || news.getNewsText() == null) {
                    news.setNewsText("");
                }

                news.setCreatorName(rs.getString("username"));
                news.setCreatorFirstname(rs.getString("firstname"));
                news.setCreatorLastname(rs.getString("lastname"));

                newsList.add(news);

                currentCountSets++;

                if (currentCountSets >= maxCount) {
                    break;
                }
            }

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

    public final static VKCNews retrieveNews(Integer newsId) throws DBException {
        Connection conn = null;

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

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

    public final static VKCNews retrieveNews(Integer newsId, Connection conn) throws DBException {
        if (newsId == null) {
            return null;
        }

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        VKCNews news = null;

        try {
            String sqlStmt = "SELECT news.newsId, news.headline, news.noteId, news.infoUrl, news.validFrom, news.validUntil, news.created, news.creatorId, users.username, users.firstname, users.lastname "
                           + "  FROM news, users "
                           + " WHERE news.creatorId = users.userId AND news.newsId = ?";
            pStmt = conn.prepareStatement(sqlStmt);
            pStmt.setInt(1, newsId.intValue());
            rs = pStmt.executeQuery();

            if (rs.next()) {
                news = new VKCNews();

                news.setNewsId(new Integer(rs.getInt("newsId")));
                news.setHeadline(rs.getString("headline"));
                news.setNewsText(VKCNoteSerializer.retrieveNote(new Integer(rs.getInt("noteId")), conn));

                if (rs.wasNull() || news.getNewsText() == null) {
                    news.setNewsText("");
                }

                news.setInfoUrl(rs.getString("infoUrl"));
                news.setValidFrom(rs.getTimestamp("validFrom"));
                news.setValidUntil(rs.getTimestamp("validUntil"));
                news.setCreated(rs.getTimestamp("created"));
                news.setCreatorId(new Integer(rs.getInt("creatorId")));
                news.setCreatorName(rs.getString("username"));
                news.setCreatorFirstname(rs.getString("firstname"));
                news.setCreatorLastname(rs.getString("lastname"));
            }

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

    public final static int getCountNews() throws DBException {
        Connection conn = null;

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

            return getCountNews(conn);
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static int getCountNews(Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = WebShare.database.getConnection();
            pStmt = conn.prepareStatement("SELECT count(*) AS countNews FROM news");
            rs = pStmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("countNews");
            }

            // should never happen
            return 0;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
}