/*
 * 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.data;

/**
 * Collection of premade SQL query statements.
 * 
 * @author Ryan Gilera
 */
public enum PreparedQueryStatement {
    LOGIN_USER_STUDENT("SELECT User.id, Student.student_id, User.first_name, "
                        + "User.last_name "
                        + "FROM Student\n"
                        + "INNER JOIN User \n"
                        + "ON Student.user_id = User.id\n"
                        + "WHERE Student.student_id = ? and "
                        + " ? = Student.password"),
    LOGIN_USER_LECTURER("SELECT User.id, Lecturer.email, "
                        + "User.first_name, User.last_name "
                        + "FROM Lecturer\n"
                        + "INNER JOIN User \n"
                        + "ON Lecturer.user_id = User.id\n"
                        + "WHERE Lecturer.email = ? and "
                        + "? = Lecturer.password;"),
    LOGIN_USER_ADMIN("SELECT User.id, Admin.email, "
                        + "User.first_name, User.last_name "
                        + "FROM Admin\n"
                        + "INNER JOIN User \n"
                        + "ON Admin.user_id = User.id\n"
                        + "WHERE Admin.email = ? and "
                        + "? = Admin.password;"),
    
    ANNOUNCEMENT_STUDENT_QUERY("SELECT Announcement.id AS id, "
            + "Announcement.title AS title, Announcement.message as message, " 
            + "Announcement.date_submitted AS dateSubmitted, "
            + "Announcement.announcement_type_id AS announcementTypeId " 
            + "FROM Announcement " 
            + "LEFT JOIN ClassWideAnnouncement "
            + "ON Announcement.id = ClassWideAnnouncement.id " 
            + "LEFT JOIN Class "
            + "ON Class.id = ClassWideAnnouncement.class_id " 
            + "LEFT JOIN StudentClass "
            + "ON StudentClass.class_id = Class.id " 
            + "WHERE StudentClass.user_id = ? "
            + "OR Announcement.announcement_type_id = 1 " 
            + "ORDER BY date_submitted DESC;"),
    
    ANNOUNCEMENT_LECTURER_QUERY("SELECT Announcement.id AS id, "
            + "Announcement.title AS title, Announcement.message as message, "
            + "Announcement.date_submitted AS dateSubmitted, "
            + "Announcement.announcement_type_id AS announcementTypeId " 
            + "FROM Announcement " 
            + "LEFT JOIN ClassWideAnnouncement "
            + "ON Announcement.id = ClassWideAnnouncement.id " 
            + "LEFT JOIN Class ON Class.id = ClassWideAnnouncement.class_id " 
            + "WHERE Class.lecturer_id = ? "
            + "OR Announcement.announcement_type_id = 1 " 
            + "ORDER BY date_submitted DESC;");
    
    private final String query;

    private PreparedQueryStatement(String script) {
        this.query = script;
    }

    public String getQuery() {
        return query;
    }
    
    
}

@@ -2010,34 +2010,34 @@
 ncement.message 
-as
+AS
  message, %22 %0A   
@@ -2158,35 +2158,187 @@
 nouncementTypeId
+, %22%0A            + %22Module.id AS moduleId, Module.name AS moduleName, %22 %0A            + %22User.first_name AS lecturer_first, User.last_name AS lecturer_last
  %22
- 
 %0A            + %22
@@ -2559,32 +2559,204 @@
 ent.class_id %22 %0A
+            + %22LEFT JOIN Lecturer %22%0A            + %22ON Lecturer.user_id = Class.lecturer_id %22%0A            + %22LEFT JOIN User %22%0A            + %22ON User.id = Lecturer.user_id %22%0A
             + %22L
@@ -2827,24 +2827,89 @@
 = Class.id %22
+%0A            + %22LEFT JOIN Module ON Module.id = Class.module_id %22
  %0A          
@@ -2915,24 +2915,25 @@
    + %22WHERE 
+(
 StudentClass
@@ -2994,32 +2994,119 @@
 ment_type_id = 1
+) AND %22%0A            + %22(DATE(Announcement.date_submitted)  %3E= (NOW() - INTERVAL 7 DAY))
  %22 %0A            
@@ -3276,18 +3276,18 @@
 message 
-as
+AS
  message
@@ -3423,16 +3423,83 @@
 ntTypeId
+, %22%0A            + %22Module.id AS moduleId, Module.name AS moduleName
  %22 %0A    
@@ -3712,32 +3712,97 @@
 ement.class_id %22
+%0A            + %22LEFT JOIN Module ON Module.id = Class.module_id %22
  %0A            + 
@@ -3808,16 +3808,17 @@
  %22WHERE 
+(
 Class.le
@@ -3888,16 +3888,103 @@
 e_id = 1
+) AND %22%0A            + %22(DATE(Announcement.date_submitted)  %3E= (NOW() - INTERVAL 7 DAY))
  %22 %0A    

Add extra information to retrieve using query