/*
     * Get all applicants with expired or soon-to-be expired cards
     * Assumptions:
     *      * 5 year expiry date cannot be relyed upon for
     *        citizens/applicants younger than 16 -- will need to be
     *        checked separately
     *
     *      * citizens older than 16 may not have renewed their cards
     *        when they expired at 15
     */
    $sql2 = <<<QUER
    SELECT c_id, c_first_name, c_last_name, c_citizenship_id,
        c_process_action, region_name, ci_card_type, ci_datetime,
        expiry_date, age
    FROM
        (SELECT c_id, c_first_name, c_last_name, c_citizenship_id,
        c_process_action, c_region_id, ci_card_type, ci_received_type,
        c_renew_notic_0_6, c_renew_notic_6_10, c_renew_notic_11_15,
        c_renew_notic_older_0_6, DATE(ci_datetime) ci_datetime,
        IF(YEAR(ci_datetime) <=2004,
            DATE_ADD(DATE(ci_datetime), INTERVAL $renewal_interval YEAR),
            IF(DATE_ADD(DATE(ci_datetime), INTERVAL $renewal_interval YEAR) >
                DATE_ADD(
                    DATE_SUB(c_date_of_birth,
                        INTERVAL YEAR(c_date_of_birth) YEAR),
                    INTERVAL YEAR(DATE_ADD(ci_datetime, INTERVAL $renewal_interval YEAR))
                    YEAR),

                DATE_ADD(
                    DATE_SUB(c_date_of_birth,
                        INTERVAL YEAR(c_date_of_birth) YEAR),
                    INTERVAL YEAR(DATE_ADD(ci_datetime, INTERVAL $intervalPlus YEAR))
                    YEAR),

                DATE_ADD(
                    DATE_SUB(c_date_of_birth,
                        INTERVAL YEAR(c_date_of_birth) YEAR),
                        INTERVAL YEAR(
                            DATE_ADD(ci_datetime, INTERVAL $renewal_interval YEAR))YEAR))
        ) as expiry_date,
        (DATE_FORMAT(Now(), '%Y') -
            DATE_FORMAT(c_date_of_birth, '%Y') -
            (DATE_FORMAT(Now(), '00-%m-%d') <
                DATE_FORMAT(c_date_of_birth, '00-%m-%d')) ) as age
        FROM citizenship
            JOIN card_issued ON card_issued.ci_c_id=citizenship.c_id
        WHERE (c_process_action='Citizen'
            OR c_process_action='Approved')
            AND c_deceased='No' AND c_moved_out_of_province='No') as citizenship
        JOIN region ON region.region_id=citizenship.c_region_id
        WHERE ($renewal_condition OR age < 16
            OR (age >=16 AND NOT EXISTS (SELECT c2.ci_id
                FROM card_issued c2
                WHERE c2.ci_card_type="Adult 16 up")))
            AND c_renew_notic_0_6='No' AND c_renew_notic_6_10='No'
            AND c_renew_notic_11_15='No' AND c_renew_notic_older_0_6='No'
            AND ci_received_type='Correct'
        ORDER BY age, c_last_name, c_first_name, ci_datetime DESC
QUER
;