/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package nmpt.utils;

import java.sql.Date;

/**
 *
 * @author rumpal
 */
public class DBQueries {

    public static String NMPT01Query01(String stateFIPS, Date date) {
        String query = "SELECT optype.id, optypename.name, optype.userNotes "
                + "FROM setbacks.d_operation_type AS optype "
                + "INNER JOIN setbacks.d_operation_type_name AS optypename "
                + "ON optype.type_name_id = optypename.id "
                + "WHERE optype.stateFIPS = '" + stateFIPS 
                + "' AND optype.start_date <= '" + date 
                + "' AND (optype.end_date IS NULL OR optype.end_date >= '" + date + "');";
        return query;
    }

    public static String NMPT02Query01(String stateFIPS, int operationTypeId, Date date) {
        String query = "SELECT DISTINCT id, name, displayOrder "
                + "FROM setbacks.d_national_category "
                + "WHERE id IN (SELECT std.category_id "
                + "FROM setbacks.d_feature_xreference AS ref "
                + "INNER JOIN setbacks.d_national_feature_standard AS std "
                + "ON ref.federal_feature_id = std.id "
                + "WHERE std.start_date <= '" + date
                + "' AND (std.end_date IS NULL OR std.end_date >= '" + date
                + "') AND ref.state_feature_id IN (SELECT state.id "
                + "FROM setbacks.d_features_state AS state "
                + "INNER JOIN setbacks.d_operation_type AS optype "
                + "ON optype.id = state.op_type_id "
                + "WHERE optype.stateFIPS = '" + stateFIPS 
                + "' AND state.op_type_id = " + operationTypeId
                + " AND state.start_date <= '" + date
                + "' AND (state.end_date IS NULL OR state.end_date >= '" + date + "'))) ORDER BY displayOrder;";
        return query;
    }

    public static String NMPT03Query01(String stateFIPS, int operationTypeId, int categoryId, Date date) {
        String query = "SELECT DISTINCT std.id, name.name, std.feature_num "
                + "FROM setbacks.d_national_feature_standard AS std "
                + "INNER JOIN setbacks.d_feature_xreference AS ref "
                + "ON ref.federal_feature_id = std.id "
                + "INNER JOIN setbacks.d_national_std_feature_name AS name "
                + "ON std.name_id = name.id "
                + "WHERE std.category_id = " + categoryId + " AND std.start_date <= '" + date
                + "' AND (std.end_date IS NULL OR std.end_date >= '" + date
                + "') AND ref.state_feature_id IN (SELECT state.id "
                + "FROM setbacks.d_features_state AS state "
                + "INNER JOIN setbacks.d_operation_type AS optype "
                + "ON optype.id = state.op_type_id "
                + "WHERE optype.stateFIPS = '" + stateFIPS 
                + "' AND state.op_type_id = " + operationTypeId
                + " AND state.start_date <= '" + date
                + "' AND (state.end_date IS NULL OR state.end_date >= '" + date + "')) ORDER BY std.feature_num;";
        return query;
    }

    public static String NMPT04Query01(String stateFIPS, int operationTypeId, int nationalFeatureStdId, Date date) {
        String query = "SELECT state.id AS state_feature_id, "
                + "featName.name AS state_feature_name, "
                + "state.userNotes AS state_feature_notes, "
                + "criteria.id AS setback_description_id, "
                + "application.name AS setback_description, "
                + "criteria.userNotes AS setback_description_notes, "
                + "setback.id AS setback_distance_id, "
                + "setback.distance AS setback_distance, "
                + "setback.userNotes AS setback_distance_notes "
                + "FROM setbacks.d_operation_type AS optype "
                + "INNER JOIN setbacks.d_features_state AS state "
                + "ON optype.id = state.op_type_id "
                + "INNER JOIN setbacks.d_state_feature_name AS featName "
                + "ON state.state_feature_name_id = featName.id "
                + "INNER JOIN setbacks.d_feature_xreference AS ref "
                + "ON state.id = ref.state_feature_id "
                + "INNER JOIN setbacks.d_national_feature_standard AS feastd "
                + "ON ref.federal_feature_id = feastd.id "
                + "INNER JOIN setbacks.d_setback AS setback "
                + "ON state.id = setback.state_feature_id "
                + "INNER JOIN setbacks.d_application_criteria AS criteria "
                + "ON optype.id = criteria.op_type_id AND setback.criteria_id = criteria.id "
                + "INNER JOIN setbacks.d_application_name AS application "
                + "ON criteria.name_id = application.id "
                + "WHERE optype.stateFIPS = '" + stateFIPS 
                + "' AND state.op_type_id = " + operationTypeId
                + " AND feastd.id = " + nationalFeatureStdId
                + " AND criteria.start_date <= '" + date + "' AND (criteria.end_date IS NULL OR criteria.end_date >= '" + date + "') ";
        return query;
    }
}