/*
 * Copyright 2015 Ryan Gilera.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.github.daytron.revworks.service;

import com.github.daytron.revworks.exception.SQLNoResultFoundException;
import com.github.daytron.revworks.exception.SQLErrorRetrievingConnectionAndPoolException;
import com.github.daytron.revworks.exception.SQLErrorQueryException;
import com.github.daytron.revworks.exception.NoCurrentUserException;
import com.github.daytron.revworks.MainUI;
import com.github.daytron.revworks.data.AnnouncementType;
import com.github.daytron.revworks.data.ExceptionMsg;
import com.github.daytron.revworks.data.PreparedQueryStatement;
import com.github.daytron.revworks.data.UserType;
import com.github.daytron.revworks.model.Announcement;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ryan Gilera
 */
public abstract class DataProviderAbstract implements DataProvider {

    private static final String GSM_LONDON = "GSM London";

    private List<Announcement> listOfAnnouncements;
    private JDBCConnectionPool connectionPool;
    private Connection connection;
    private PreparedStatement preparedStatement;

    public DataProviderAbstract() {
    }

    @Override
    public List<Announcement> populateHomeViewWithData() throws
            SQLErrorQueryException, SQLNoResultFoundException,
            SQLErrorRetrievingConnectionAndPoolException {

        if (getConnectionPool()) {
            try {

                if (MainUI.get().getAccessControl().isUserAStudent()) {
                    preparedStatement = connection.prepareStatement(
                            PreparedQueryStatement.ANNOUNCEMENT_STUDENT_QUERY
                            .getQuery());

                } else {
                    // Otherwise it's a lecturer
                    preparedStatement = connection.prepareStatement(
                            PreparedQueryStatement.ANNOUNCEMENT_LECTURER_QUERY
                            .getQuery());
                }

                preparedStatement.setInt(1, 
                       MainUI.get().getAccessControl().getUserId());

                ResultSet resultSet = preparedStatement.executeQuery();

                if (!resultSet.next()) {
                    Throwable throwable
                            = new NoCurrentUserException(
                                    ExceptionMsg.EMPTY_SQL_RESULT.getMsg());
                    Logger.getLogger(DataProviderAbstract.class.getName())
                            .log(Level.SEVERE, null, throwable);
                    throw new SQLNoResultFoundException(
                            ExceptionMsg.SQL_NO_RESULT_FOUND.getMsg());
                }
                
                resultSet.beforeFirst();
                
                this.listOfAnnouncements = new ArrayList<>();

                
                while (resultSet.next()) {
                    Timestamp timestamp = resultSet.getTimestamp(4);
                    LocalDateTime dateSubmitted = timestamp.toLocalDateTime();

                    AnnouncementType announcementType;
                    String announcementSource;
                    Announcement announcement;

                    if (resultSet.getInt(5) == 2) {
                        String moduleId = resultSet.getString(6);
                        String moduleName = resultSet.getString(7);

                        announcementType = AnnouncementType.CLASS_WIDE;
                        
                        if (MainUI.get().getAccessControl().getUserType() == 
                                UserType.STUDENT) {
                            announcementSource = resultSet.getString(8) + 
                                    " " + resultSet.getString(9);
                        } else {
                            announcementSource
                                = MainUI.get().getAccessControl().getFullName();
                        }
                        

                        announcement = new Announcement(
                                resultSet.getInt(1),
                                resultSet.getString(2),
                                resultSet.getString(3),
                                dateSubmitted,
                                announcementType,
                                announcementSource,
                                moduleId,
                                moduleName);
                    } else {
                        announcementType = AnnouncementType.SYSTEM_WIDE;
                        announcementSource = GSM_LONDON;

                        announcement = new Announcement(
                                resultSet.getInt(1),
                                resultSet.getString(2),
                                resultSet.getString(3),
                                dateSubmitted,
                                announcementType,
                                announcementSource);
                    }

                    this.listOfAnnouncements.add(announcement);
                }

                preparedStatement.close();
                connectionPool.releaseConnection(connection);

                return listOfAnnouncements;

            } catch (SQLException ex) {
                Logger.getLogger(DataProviderAbstract.class.getName())
                        .log(Level.SEVERE, null, ex);
                throw new SQLErrorQueryException(
                        ExceptionMsg.SQL_ERROR_QUERY.getMsg());
            }
        } else {
            throw new SQLErrorRetrievingConnectionAndPoolException(
                    ExceptionMsg.SQL_ERROR_CONNECTION.getMsg());
        }

    }

    @Override
    public boolean getConnectionPool() {
        try {
            this.connectionPool = SQLConnectionManager.get().connect();
            this.connection = connectionPool.reserveConnection();

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DataProviderAbstract.class.getName())
                    .log(Level.SEVERE, null, ex);
            return false;
        }
    }

}

@@ -872,77 +872,8 @@
 on;%0A
-import com.github.daytron.revworks.exception.NoCurrentUserException;%0A
 impo
@@ -1861,57 +1861,8 @@
 ion;
-%0A    private PreparedStatement preparedStatement;
 %0A%0A  
@@ -2115,19 +2115,23 @@
     if (
-get
+reserve
 Connecti
@@ -2160,16 +2160,68 @@
   try %7B%0A
+                PreparedStatement preparedStatement;
 %0A       
@@ -2397,32 +2397,40 @@
 dQueryStatement.
+STUDENT_
 ANNOUNCEMENT_STU
@@ -2427,21 +2427,20 @@
 CEMENT_S
-TUDEN
+ELEC
 T_QUERY%0A
@@ -2676,16 +2676,25 @@
 atement.
+LECTURER_
 ANNOUNCE
@@ -2698,24 +2698,22 @@
 NCEMENT_
+SE
 LECT
-URER
 _QUERY%0A 
@@ -2882,24 +2882,154 @@
 tUserId());%0A
+                preparedStatement.setInt(2, %0A                       MainUI.get().getAccessControl().getUserId());%0A                
 %0A           
@@ -3209,21 +3209,24 @@
 new 
-NoCurrentUser
+SQLNoResultFound
 Exce
@@ -6602,19 +6602,23 @@
 boolean 
-get
+reserve
 Connecti
@@ -7002,10 +7002,184 @@
 %0A    %7D%0A%0A
+    public Connection getConnection() %7B%0A        return connection;%0A    %7D%0A%0A    public JDBCConnectionPool getConnectionPool() %7B%0A        return connectionPool;%0A    %7D%0A    %0A    %0A%0A
 %7D%0A

Refactor PreparedStatement variable as local, match the new query with 2 variables, switch to more meaningful custom exception and refactor method name