/*
 * $Id: VKCUserRatingSerializer.java,v 1.4 2006/03/23 11:39:18 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCUserRatingSerializer.java,v $
 * Revision 1.4  2006/03/23 11:39:18  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.3  2004/12/05 22:57:46  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.2  2004/11/24 09:25:56  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.1  2004/11/19 15:30:38  jonyp
 * first strike
 *
 *
 *
 */

package vkc.db.serializer;

import vkc.db.entities.VKCUserRating;
import vkc.view.bean.VKCQuotaInfo;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;

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

import java.sql.Connection;

public class VKCUserRatingSerializer {
    private VKCUserRatingSerializer() {
	}

	public final static List retrieveAllRatings() throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveAllRatings(conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

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

		try {
			pStmt = conn.prepareStatement("SELECT * FROM userratings");
			rs = pStmt.executeQuery();

			ArrayList allEntries = new ArrayList();

			while (rs.next()) {
				VKCUserRating ur = new VKCUserRating();
				ur.setUserRatingId(new Integer(rs.getInt("userRatingId")));
				ur.setMinPubDocsCount(new Integer(rs.getInt("minPubDocsCount")));
				ur.setDescription(rs.getString("description"));
				ur.setDescr_key(rs.getString("descr_key"));
				ur.setIconPath(rs.getString("icon")); 

				allEntries.add(ur);
			}

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

	public final static VKCUserRating retrieveRating(Integer userId) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveRating(conn, userId);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	public final static VKCUserRating retrieveRating(Connection conn, Integer userId) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		VKCUserRating ur = null;

		try {

		    VKCQuotaInfo qi = WebShare.database.getQuotaInfo(userId);
		    pStmt = conn.prepareStatement("SELECT * FROM userRatings WHERE minPubDocsCount <= ? ORDER BY minPubDocsCount DESC");
		    pStmt.setLong(1, qi.getAmountPubDocs());
			rs = pStmt.executeQuery();
		    
/*			
			rs = stmt.executeQuery( "   SELECT * " +
					                "     FROM userratings " + 
					                "    WHERE minPubDocsCount <=  (SELECT COUNT(*)"  +
					                "                                 FROM documents " +
					                "                                WHERE publisherId = " + userId + ")" + 
					                " ORDER BY minPubDocsCount DESC");
*/
			
			// take only the first one...
			if (rs.next()) {
				ur = new VKCUserRating();
				ur.setUserRatingId(new Integer(rs.getInt("userRatingId")));
				ur.setMinPubDocsCount(new Integer(rs.getInt("minPubDocsCount")));
				ur.setDescription(rs.getString("description"));
				ur.setDescr_key(rs.getString("descr_key"));
				ur.setIconPath(rs.getString("icon")); 
			}

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