<?php
/*
* Survey.inc.php
*
* This is the domain logic managing the fundamental behaviors and data of Surveys.
*
* @author   Quinn Comendant <quinn@strangecode.com>
* @version  1.0
* @since    20 Aug 2014 17:20:09
*/

require_once 'models/Model.inc.php';

class Survey extends Model
{
    // The database fields used by this model.
    public static $fields = array(
        'survey_id' => '',
        'account_id' => '',
        'survey_name' => '',
        'begin_datetime' => '',
        'end_datetime' => '',
        'virtual_number' => '',
        'status' => '',
        'added_datetime' => '',
        'added_by_user_id' => '',
        'modified_datetime' => '',
        'modified_by_user_id' => '',
    );

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function insert($frm)
    {
        global $auth, $cache;
    	$db =& DB::getInstance();
    	$app =& App::getInstance();

        // Remove any stale cached list data.
        $cache->delete('survey list');

        // Create the record under the user's account, unless an account_id was provided.
        $account_id = isset($frm['account_id']) && is_numeric($frm['account_id']) ? $frm['account_id'] : $auth->get('account_id');

        // Insert record data.
        $app->logMsg(sprintf('%s with data %s', __METHOD__, getDump($frm)), LOG_DEBUG, __FILE__, __LINE__);
        $db->query("
            INSERT INTO survey_tbl (
                account_id,
                survey_name,
                begin_datetime,
                end_datetime,
                status,
                added_datetime,
                added_by_user_id,
                modified_datetime,
                modified_by_user_id
            ) VALUES (
                '" . $db->escapeString($account_id) . "',
                '" . $db->escapeString($frm['survey_name']) . "',
                '" . $db->escapeString(strToSQLDate($frm['begin_datetime'])) . "',
                '" . $db->escapeString(strToSQLDate($frm['end_datetime'])) . "',
                'queued',
                NOW(),
                '" . $db->escapeString($auth->get('user_id')) . "',
                NOW(),
                '" . $db->escapeString($auth->get('user_id')) . "'
            )
        ");
        $last_insert_id = mysql_insert_id($db->getDBH());

        // Create version.
        $version = Version::getInstance($auth);
        $version->create('survey_tbl', 'survey_id', $last_insert_id, $frm['survey_name']);

        return $last_insert_id;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function update($frm)
    {
    	global $auth, $lock, $cache;
    	$db =& DB::getInstance();
    	$app =& App::getInstance();

        $lock->select('survey_tbl', 'survey_id', $frm['survey_id']);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('survey list');

        // Create the record under the user's account, unless an account_id was provided.
        $account_id = isset($frm['account_id']) && is_numeric($frm['account_id']) ? $frm['account_id'] : $auth->get('account_id');

        // Update record data.
        $app->logMsg(sprintf('%s with data %s', __METHOD__, getDump($frm)), LOG_DEBUG, __FILE__, __LINE__);
        $db->query("
            UPDATE survey_tbl SET
                account_id = '" . $db->escapeString($account_id) . "',
                survey_name = '" . $db->escapeString($frm['survey_name']) . "',
                begin_datetime = '" . $db->escapeString(strToSQLDate($frm['begin_datetime'])) . "',
                end_datetime = '" . $db->escapeString(strToSQLDate($frm['end_datetime'])) . "',
                status = '" . $db->escapeString($frm['status']) . "',
                modified_datetime = NOW(),
                modified_by_user_id = '" . $db->escapeString($auth->get('user_id')) . "'
            WHERE survey_id = '" . $db->escapeString($frm['survey_id']) . "'
        ");

        // Create version.
        $version = Version::getInstance($auth);
        $version->create('survey_tbl', 'survey_id', $frm['survey_id'], $frm['survey_name']);

        // Unlock record.
        $lock->remove();
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function delete($id)
    {
        global $lock, $cache, $locally_carried_queries;
    	$db =& DB::getInstance();
    	$app =& App::getInstance();

        $lock->select('survey_tbl', 'survey_id', $id);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('survey list');

        // Delete the record.
        $db->query("DELETE FROM survey_tbl WHERE survey_id = '" . $db->escapeString($id) . "'");

        // Unlock record.
        $lock->remove();
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function getPaginatedList($where_clause='')
    {
        global $page, $so, $cache;
    	$db =& DB::getInstance();
    	$app =& App::getInstance();

        // Build search query if available.
        if (getFormData('q', false)) {
            $qry_words = preg_split('/[^\w]/', getFormData('q'));
            for ($i=0; $i<sizeof($qry_words); $i++) {
                $where_clause .= (empty($where_clause) ? 'WHERE' : 'AND') . "
                    (
                        survey_tbl.survey_name LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR survey_tbl.begin_datetime LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR survey_tbl.end_datetime LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                    )
                ";
            }
        }

        if (getFormData('filter_account_id', false)) {
            // Limit by filter.
            $where_clause .= (empty($where_clause) ? 'WHERE' : ' AND') . " survey_tbl.account_id = '" . $db->escapeString(getFormData('filter_account_id')) . "'";
        }

        // Count the total number of records so we can do something about the page numbers.
        $qid = $db->query("
            SELECT COUNT(*)
            FROM survey_tbl
            $where_clause
        ");
        list($num_results) = mysql_fetch_row($qid);

        // Set page numbers now we know (needed for next step).
        $page->setTotalItems($num_results);
        $page->calculate();

        // Final SQL, with sort and page limiters.
        $sql = "
            SELECT
                survey_tbl.*,
                (SELECT COUNT(*) FROM question_tbl WHERE question_tbl.survey_id = survey_tbl.survey_id) AS num_questions,
                (SELECT COUNT(*) FROM participant_survey_tbl WHERE participant_survey_tbl.survey_id = survey_tbl.survey_id) AS num_participants,
                (SELECT COUNT(*) FROM response_tbl LEFT JOIN question_tbl ON (response_tbl.question_id = question_tbl.question_id) WHERE question_tbl.survey_id = survey_tbl.survey_id) AS num_responses,
                a1.username AS added_by_username,
                a2.username AS modified_by_username
            FROM survey_tbl
            LEFT JOIN user_tbl a1 ON (survey_tbl.added_by_user_id = a1.user_id)
            LEFT JOIN user_tbl a2 ON (survey_tbl.modified_by_user_id = a2.user_id)
            $where_clause
            " . $so->getSortOrderSQL() . "
            " . $page->getLimitSQL() . "
        ";

        // Use a cache hash to determine if the result-set has changed.
        // A unique key for this query, with the total_items in case db records
        // were added since the last cache. This identifies a unique set of
        // cached data, but we must refer to the list that is cached by a more
        // generic name. so that we can flush the cache (if records updated)
        // without knowing the hash.
        $cache_hash = md5($sql . '|' . $page->total_items);
        $tmp_prefs = new Prefs('session', array('storagetype' => 'session'));
        if ($tmp_prefs->get('cache_hash') != $cache_hash) {
            $cache->delete('survey list');
            $tmp_prefs->set('cache_hash', $cache_hash);
        }

        // First try to return from the cache.
        if ($cache->exists('survey list')) {
            return $cache->get('survey list');
        }

        // The list was not cached, so issue the real query.
        $qid = $db->query($sql);
        $list = array();
        while ($row = mysql_fetch_assoc($qid)) {
            $list[] = $row;
        }

        // Save this list into the cache.
        if (isset($list) && !empty($list)) {
            $cache->set('survey list', $list);
        }

        return $list;
    }

    /*
    * Manually set a survey as ready to run.
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    09 Nov 2013 20:37:55
    */
    static public function run($survey_id)
    {
        global $lock, $cache, $locally_carried_queries;
        $db =& DB::getInstance();
        $app =& App::getInstance();

        $lock->select('survey_tbl', 'survey_id', $survey_id);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('survey list');

        // Get this survey underway.
        $db->query("
            UPDATE survey_tbl SET
                begin_datetime = NOW(),
                status = 'underway'
            WHERE survey_id = '" . $db->escapeString($survey_id) . "'
        ");

        // Unlock record.
        $lock->remove();
    }

    /*
    * Close a survey so no further questions will be sent and no responses can be added.
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    03 Nov 2013 11:52:41
    */
    static public function close($survey_id)
    {
        global $lock, $cache, $locally_carried_queries;
    	$db =& DB::getInstance();
    	$app =& App::getInstance();

        $lock->select('survey_tbl', 'survey_id', $survey_id);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('survey list');

        // Close the record.
        $db->query("
            UPDATE survey_tbl
            SET status = 'closed'
            WHERE survey_id = '" . $db->escapeString($survey_id) . "'
        ");

        // Unlock record.
        $lock->remove();
    }


    /*
    * Returns the values from the survey_tbl for the current survey (survey is queued, and begin_datetime is in the past).
    *
    * @access   public
    * @return   mixed   Return value from mysql_fetch_assoc() for the query.
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    09 Nov 2013 15:25:01
    */
    static public function getCurrent()
    {
        $db =& DB::getInstance();

        // Get survey to run: only one survey, with a begin_datetime that has passed, which isn't closed yet.
        $qid = $db->query("
            SELECT *
            FROM survey_tbl
            WHERE status IN ('underway')
            -- WHERE begin_datetime <= NOW()
            -- AND end_datetime > NOW()
            -- AND status IN ('underway')
            -- AND status IN ('queued', 'underway')
            ORDER BY begin_datetime ASC
            LIMIT 1
        ");
        return mysql_fetch_assoc($qid);
    }

    /*
    * Returns an array of survey statistics.
    *
    * @access   public
    * @param    int     $survey_id
    * @return   mixed       The result from mysql_fetch_assoc() for the query.
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    10 Nov 2013 14:33:17
    */
    static public function getStats($survey_id)
    {
        $db =& DB::getInstance();
        $qid = $db->query("
            SELECT (
                SELECT COUNT(*)
                FROM response_tbl
                LEFT JOIN question_tbl ON (response_tbl.question_id = question_tbl.question_id)
                WHERE survey_id = '" . $db->escapeString($survey_id) . "'
            ) AS num_responses, (
                SELECT COUNT(*)
                FROM question_tbl
                WHERE survey_id = '" . $db->escapeString($survey_id) . "'
            ) AS num_questions, (
                SELECT COUNT(*)
                FROM participant_tbl
                LEFT JOIN participant_survey_tbl ON (participant_tbl.participant_id = participant_survey_tbl.participant_id)
                WHERE participant_survey_tbl.survey_id = '" . $db->escapeString($survey_id) . "'
            ) AS num_participants
        ");
        return mysql_fetch_assoc($qid);
    }

    /*
    * Sets a participant_id as being a member of the specified survey_id, and defines
    * their progress through that survey, and the current question to send or receive an answer for.
    * This function should only be run once per-participant per-survey.
    *
    * @access   public
    * @param    int     $participant_id
    * @param    int     $survey_id
    * @return   int     The question_id of the first question for the survey.
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    09 Nov 2013 15:27:03
    */
    static public function addParticipant($participant_id, $survey_id, $status='pending')
    {
        $db =& DB::getInstance();
        $app =& App::getInstance();

        // Get first question of the specified survey.
        if ($q = Question::getFirstInSurvey($survey_id)) {
            $db->query("
                INSERT INTO participant_survey_tbl (
                    participant_id,
                    survey_id,
                    status,
                    current_question_id
                ) VALUES (
                    '" . $db->escapeString($participant_id) . "',
                    '" . $db->escapeString($survey_id) . "',
                    '" . $db->escapeString($status) . "',
                '" . $db->escapeString($q['question_id']) . "'
                )
            ");
            $app->logMsg(sprintf('Added participant_id %s to survey_id %s with first question_id %s', $participant_id, $survey_id, $q['question_id']), LOG_DEBUG, __FILE__, __LINE__);
        }
        return $q['question_id'];
    }

    /*
    * Defines survey_id as 'completed' for participant_id, also sending a 'thank you' SMS to the participant.
    *
    * @access   public
    * @param    int     $participant_id
    * @param    int     $survey_id
    * @return   null    No value returned.
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    09 Nov 2013 18:35:20
    */
    static public function endParticipant($participant_id, $survey_id)
    {
        $db =& DB::getInstance();
    	$app =& App::getInstance();

        $db->query("
            UPDATE participant_survey_tbl SET
                status = 'completed',
                current_question_id = NULL
            WHERE participant_id = '" . $db->escapeString($participant_id) . "'
            AND survey_id = '" . $db->escapeString($survey_id) . "'
        ");
        // SMS::send($sms['sender_number'], 'Gracias por participar!'); // TODO: we need to make this multilingual. Migrate this into the Survey settings.
        $app->logMsg(sprintf('Participant_id %s has completed survey_id %s', $participant_id, $survey_id), LOG_INFO, __FILE__, __LINE__);
    }

}