<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of dao
 *
 * @author dghai
 */

require_once 'Config.php';
require_once 'DbTables.php';

class Dao 
{
    private $_dbConnection;
    private $_config;
    //stores request made to server. Used for logging in case error occurs.
    private $_request; 
    private $_queries = array
    (
        'isAuthorized'
            =>
           'SELECT `users_loginId`, `users_ageid`, `users_gender`, 
                   `users_height`, `users_shoesize`, `users_weightid`, `users_zip`, 
                   `users_shirtsize`, `users_email`, `user_sittingheight`, 
                   `user_upperleglength` 
            FROM `users` 
            WHERE `users_loginId` = ? AND `users_password` = PASSWORD(?)',
        
        'isAdminAuthorized'
            =>
           'SELECT `admins_id`
            FROM `admins` 
            WHERE `admins_id` = ? AND `admins_password` = PASSWORD(?) 
                  AND `admins_active` = true
            LIMIT 1',
        
        'isUsernameAvailable'
            => 
            'SELECT `users_loginId` FROM `users` WHERE `users_loginId` = ? LIMIT 1
             UNION
             SELECT `admins_id` FROM `admins` WHERE `admins_id` = ? LIMIT 1',
        
        'getSurveyQuestions'
            =>
            'SELECT `q`.`questions_id`,`q`.`questions_text`,
                    `q`.`questions_ans_value_type`,`q`.`questions_ans_direction`,
                    `q`.`questions_location_x`,`q`.`questions_location_y`,
                    `q`.`questions_location_z` 
            FROM `questions_in_set` `qis`
                    INNER JOIN  `questions` `q` 
                    ON (`qis`.`questionsinset_question_id` = `q`.`questions_id` 
                        AND `q`.`questions_active` = true 
                        AND `qis`.`questionsinset_set_id` = ?)',       
        
        'getAges'
            =>
            'SELECT `ages_id`, `ages_from`, `ages_to` FROM `ages`',
        
        'getWeights'
            =>
            'SELECT `weights_id`, `weights_from`, `weights_to` FROM `weights`',
        
        'setNewUserInfo'
            =>
            'INSERT INTO `users`(`users_loginId`, `users_password`, `users_email`) 
             VALUES (?, PASSWORD(?), ?)',
        
        'setAnswer'
            =>
            'INSERT INTO `answers`(`answers_ques_id`, 
                `answers_value`, `answers_attachment_path`, `answers_survey_id`, 
                `answers_date`, `answers_sequence`, `answers_comment`) 
             VALUES (?, ?, ?, ?, NOW(), ? , ?)',
        
        'updateAnswer'
            => 
            'UPDATE `answers` 
             SET `answers_value` = ?, `answers_attachment_path` = ?, 
                 `answers_date` = NOW(), `answers_comment` = ?
             WHERE `answers_id` = ?',
        
        'updateUserInfo'
            =>
            'UPDATE `users` SET `users_ageid` = ?, `users_gender` = ?, 
                `users_height` = ?, `users_shoesize` = ?, `users_weightid` = ?,
                `users_zip` = ?, `users_shirtsize` = ?, `user_sittingheight` = ?, 
                `user_upperleglength` = ?, `users_email` = ?
             WHERE `users_loginId` = ?',
        
        'getCarModel'
            =>
            'SELECT `vehicle_id`, `vehicle_model` FROM `vehicle` 
             WHERE `vehicle_make` = ? AND `vehicle_year` = ?',
        
        'getQuesCountForActiveSurveys'
            =>
            'SELECT `qs`.`questionset_id`, 
                     COUNT(`qis`.`questionsinset_question_id`) AS `question_count`
             FROM `questions_in_set` `qis`
                    INNER JOIN `question_set` `qs` ON 
                    (`qs`.`questionset_id` = `qis`.`questionsinset_set_id` 
                        AND `qs`.`questionset_active` = true)
             GROUP BY `qis`.`questionsinset_set_id`',
        
        'getAnswerCount'
            =>
            'SELECT `qs`.`questionset_id`, `qs`.`questionset_setname`, 
                 `s`.`survey_id` , COUNT(`a`.`answers_id`) AS `answer_count`, `v`.`vehicle_make`, `v`.`vehicle_model`, `v`.`vehicle_year`
             FROM `question_set` `qs`
             LEFT OUTER JOIN `survey` `s` ON 
                         (`qs`.`questionset_id` = `s`.`survey_question_set` 
                          AND `qs`.`questionset_active` = true 
                          AND `s`.`survey_user_id` = ?)
             LEFT OUTER JOIN `answers` `a` ON `s`.`survey_id` = `a`.`answers_survey_id`
             LEFT OUTER JOIN `vehicle` `v` ON `s`.`survey_vehicleid` = `v`.`vehicle_id`
             GROUP BY `s`.`survey_id`, `qs`.`questionset_id`
             ORDER BY `s`.`survey_id` DESC',
        
        'getPreviousAnswers'
            =>
            'SELECT `a`.`answers_id`, `q`.`questions_text`, `a`.`answers_value`, 
                   `a`.`answers_comment`, `a`.`answers_attachment_path`
             FROM `answers` `a`
             INNER JOIN `questions` `q` ON `q`.`questions_id` = `a`.`answers_ques_id`
             WHERE `a`.`answers_survey_id` = ?',
        
        'setSurveyEnded'
            =>
            'UPDATE `survey` SET `survey_end_date` = NOW()
             WHERE `survey_id` = ?',
        
        'setNewSurvey'
            => 
            'INSERT INTO `survey` (`survey_start_date`, `survey_vehicleid`, `survey_user_id`,
            `survey_question_set`, `survey_end_date`) VALUES (NOW(), ?, ?, ?, ?)',
        
        'getLatestSurveyId'
            =>
            'SELECT `survey_id`
             FROM `survey`
             WHERE `survey_vehicleid` = ? AND `survey_user_id` = ? 
                 AND `survey_question_set` = ?
             ORDER BY `survey_id` DESC
             LIMIT 1',
        
        'resetPassword'
            =>
            'UPDATE `users`
             SET `users_password` = PASSWORD(?)
             WHERE `users_loginId` = ?',
        
        'getEmailId'
            =>
            'SELECT `users_email` FROM `users` WHERE `users_loginId` = ?',
        
        'getUserBadges'
            =>
            'SELECT `userbadges_badgeid` FROM `user_badges` WHERE `userbadges_userid` = ?',
        
        'setBadges'
            =>
            'INSERT INTO `user_badges`(`userbadges_badgeid`, `userbadges_userid`) VALUES(?,?)',
    );
    
    function __construct($request) 
    {
        $this->_request = $request;
        $this->_config = new Config();
        //connect to DB;
        $this->_dbConnection = new mysqli(
                                    $this->_config->GetDbServer(),
                                    $this->_config->GetDbUser(),
                                    $this->_config->GetDbUserPassword(),
                                    $this->_config->GetDbSchema(),
                                    $this->_config->GetDbPort()
                                   );
        if($this->_dbConnection->connect_error)
        {
            return new ErrorObject($this->_request, E_VROOM_DB_CONNECT_FAILED, $this->GetMysqlError('DB Connection'));
        }
        //select the schema we need to use.
        //if data needs to support UTF-8, mysql_set_charset should be called here.
    }
    
    public function IsUserAuthorized($userid, $password, $isAdmin)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['isAuthorized']))
        {
            //bind parameters
            $stmt->bind_param('ss', $userid, $password);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['isAuthorized'])
                                      );
            }
            $stmt->store_result();
            
            if($stmt->num_rows > 1)
            {
                //something's wrong with the DB. report to admin
                 return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['isAuthorized'])
                                      );
            }
            else if ($stmt->num_rows === 0)
            {
                return false;
            }
            
            //bind the column values to variables
            $user = new Users();
            /*
             * "SELECT `users_id`, `users_loginId`, `users_ageid`, `users_gender`, `users_height`, `users_shoesize`, `users_weightid`, 
             * `users_zip`, `users_shirtsize`, `users_email`, `user_sittngheight`, `user_upperleglength` 
             */
            $stmt->bind_result(
                                $user->loginId, 
                                $user->ageId, 
                                $user->gender, 
                                $user->height, 
                                $user->shoesize, 
                                $user->weightId, 
                                $user->zip, 
                                $user->shirtsize,
                                $user->email,
                                $user->sittingHeight,
                                $user->upperLegLength
                            );
            //copy result from DB into bound variables
            $stmt->fetch();
            //return the data
            return $user;
            
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED, 
                                    $this->GetMysqlError($this->_queries['isAuthorized'])
                                   );
        }
    }
    
    public function IsUserAdmin($userid, $password)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['isAdminAuthorized']))
        {
            //bind parameters
            $stmt->bind_param('ss', $userid, $password);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['isAdminAuthorized'])
                                      );
            }
            $stmt->store_result();
            $admin = new Admin();
            if($stmt->num_rows > 1)
            {
                //something's wrong with the DB. report to admin
                 return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['isAdminAuthorized'])
                                      );
            }
            else if ($stmt->num_rows === 0)
            {
                $admin->isAdmin = false;
                return $admin;
            }
            
            //return the data
            $admin->isAdmin = true;
            
            return $admin;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED, 
                                    $this->GetMysqlError($this->_queries['isAdminAuthorized'])
                                   );
        }
    }
    
    public function IsUsernameAvailable($userid)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['isUsernameAvailable']))
        {
            //bind parameters
            $stmt->bind_param('ss', $userid, $userid);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['isUsernameAvailable'])
                                      );
            }
            $stmt->store_result();
            
            return $stmt->num_rows == 0;
            
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED, 
                                    $this->GetMysqlError($this->_queries['isUsernameAvailable'])
                                   );
        }
    }
    
    public function GetAgeRanges()
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getAges']))
        {
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getAges'])
                                      );
            }
            $stmt->store_result();
            
            //bind the column values to variables
            $ages = array();
           
            $stmt->bind_result(
                                $age_id,
                                $age_from,
                                $age_to
                            );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $age = new Ages();
                $age->age_id = $age_id;
                $age->age_from = $age_from;
                $age->age_to = $age_to;
                
                $ages[$age_id] = $age;
            }
            //return the data
            return $ages;
            
        }
        else
        {
            return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_PREPARE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getAges'])
                                      );
        }
    }
    
    public function GetWeightRanges()
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getWeights']))
        {            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request, 
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getWeights'])
                                      );
            }
            $stmt->store_result();
                        
            //bind the column values to variables
            $weights = array();
            $stmt->bind_result(
                                $weight_id,
                                $weight_from,
                                $weight_to
                            );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $weight = new Weights();
                $weight->weight_id = $weight_id;
                $weight->weight_from = $weight_from;
                $weight->weight_to = $weight_to;
                
                $weights[$weight_id] = $weight;
            }
            //return the data
            return $weights;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request, 
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getWeights'])
                                  );
        }
    }

    public function GetSurveysForUser($loginId)
    {
        //Get number of questions for each survey available.
        $questionCount = $this->GetQuestionCountForAvailableSurveys();
        
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getAnswerCount']))
        {            
            $stmt->bind_param('s', $loginId);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request, 
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getAnswerCount'])
                                      );
            }
            $stmt->store_result();
                        
            //bind the column values to variables
            $surveys = array();
            $stmt->bind_result(
                                $survey_question_set_id,
                                $survey_name,
                                $survey_id,
                                $survey_answerCount,
                                $survey_vehicle_make,
                                $survey_vehicle_model,
                                $survey_vehicle_year
                              );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $survey = new Survey();
                $survey->survey_id = $survey_id == null ? -1 : $survey_id;
                $survey->survey_question_set_id = $survey_question_set_id;
                $survey->survey_name = $survey_name;
                $survey->survey_quesAnswered = $survey_answerCount;
                $survey->survey_quesCount = array_key_exists($survey_question_set_id, $questionCount) ? 
                                            $questionCount[$survey_question_set_id] : 0;
                $survey->survey_vehicle_make = $survey_vehicle_make;
                $survey->survey_vehicle_model = $survey_vehicle_model;
                $survey->survey_vehicle_year = $survey_vehicle_year;
                
                $surveys []= $survey;
            }
            //return the data
            return $surveys;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request, 
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getAnswerCount'])
                                  );
        }
    }
    
    private function GetQuestionCountForAvailableSurveys()
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getQuesCountForActiveSurveys']))
        {
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request, 
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getQuesCountForActiveSurveys'])
                                      );
            }
            $stmt->store_result();
                        
            //bind the column values to variables
            $questionCounts = array();
            $stmt->bind_result(
                                $survey_id,
                                $maxQuestions
                              );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $questionCounts[$survey_id] = $maxQuestions;
            }
            //return the data
            return $questionCounts;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request, 
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getQuesCountForActiveSurveys'])
                                  );
        }
    }
    
    public function GetPreviousAnswers($surveyId)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getPreviousAnswers']))
        {
            $stmt->bind_param('i', $surveyId);
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request, 
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getPreviousAnswers'])
                                      );
            }
            $stmt->store_result();
            //bind the column values to variables
            $answers = array();
            $stmt->bind_result(
                                $answer_id,
                                $answer_ques_text,
                                $answer_value,
                                $answer_comment,
                                $answer_image
                              );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $answer = new Answer();
                $answer->answer_comment = $answer_comment;
                $answer->answer_id = $answer_id;
                $answer->answer_ques_text = $answer_ques_text;
                $answer->answer_value = $answer_value;
                $answer->answer_image = $answer_image;
                if($answer_image != null)
                {
                    $imageFile = $this->_config->GetFilestore().$answer_image;
                    $imgFileHandle = fopen($imageFile, 'rb');
                    $answer->answer_image = base64_encode(fread($imgFileHandle, filesize($imageFile)) );
                    fclose($imgFileHandle);
                }
                $answers []= $answer;
            }
            //return the data
            return $answers;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request, 
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getPreviousAnswers'])
                                  );
        }
    }
    
    public function GetSurveyQuestions($question_set)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getSurveyQuestions']))
        {
            //bind parameters
            $stmt->bind_param('i', $question_set);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject($this->GetMysqlError($this->_queries['getSurveyQuestions']), E_VROOM_EXECUTE_QUERY_FAILED);
            }
            $stmt->store_result();
                      
            //bind the column values to variables
            $stmt->bind_result(
                                $ques_id, 
                                $ques_text, 
                                $ques_valueType, 
                                $ques_answerDirection, 
                                $ques_x, 
                                $ques_y, 
                                $ques_z
                            );
            $questions = array();
            $questionNumber = -1;
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $ques = new Questions();
                $ques->id = $ques_id; 
                $ques->text = $ques_text; 
                $ques->valueType = $ques_valueType; 
                $ques->answerDirection = $ques_answerDirection; 
                $ques->x = $ques_x; 
                $ques->y = $ques_y; 
                $ques->z = $ques_z; 
                            
                $questions[++$questionNumber] = $ques;
            }
            //return the data
            return $questions;
            
        }
        else
        {
            return new ErrorObject($this->GetMysqlError($this->_queries['getSurveyQuestions']), E_VROOM_PREPARE_QUERY_FAILED);
        }
    }
    
    public function GetBadges($userid)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getUserBadges']))
        {
            //bind parameters
            $stmt->bind_param('s', $userid);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['getUserBadges'])
                                      );
            }
            $stmt->store_result();
            
            $stmt->bind_result($badge);
            //copy result from DB into bound variables
            $badges = array();
            while($stmt->fetch())
            {
                $badges []= $badge;
            }
            //return the data
            return $badges;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED, 
                                    $this->GetMysqlError($this->_queries['getUserBadges'])
                                   );
        }
    }
    
    public function GetCarModel($make, $year)
    {
         //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getCarModel']))
        {            
            $stmt->bind_param('si', $make, $year);
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request, 
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getCarModel'])
                                      );
            }
            $stmt->store_result();
                        
            //bind the column values to variables
            $vehicles = array();
            $stmt->bind_result(
                                $vehicle_id,
                                $vehicle_model
                            );
            //copy result from DB into bound variables
            while($stmt->fetch())
            {
                $vehicle = new Vehicle();
                $vehicle->vehicle_id = $vehicle_id;
                $vehicle->vehicle_model = $vehicle_model;
                $vehicles []= $vehicle;
            }
            //return the data
            return $vehicles;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request, 
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getCarModel'])
                                  );
        }
    }
    
    public function SetAnswers(array $answers)
    {
        //disable autocommit to work with transactions
        $this->_dbConnection->autocommit(false);
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['setAnswer']))
        {        
            foreach($answers as $key=>$value)
            {
                //answers that have answer_is attached to them will need to be
                //updated and not inserted.
                if(array_key_exists('answer_id', $value))
                    if($value['answer_id'] != null)
                        continue;
                
                $attachment_path = null;
                //now see if the image exists. if it does, dump it to current working directory.
                if(array_key_exists('image', $value))
                    if(sizeof($value['image']) > 0)
                        $attachment_path = $this->SavePicture($value['image']);
                    
                
                //insert all answers that do not have an answer id.
                //bind parameters
                $stmt->bind_param('idsiis', 
                                    $value['ques_id'], 
                                    $value['value'], 
                                    $attachment_path, 
                                    $value['survey_id'],
                                    $value['sequence'], 
                                    $value['comment']
                                 );

                //execute query
                if(!$stmt->execute())
                {
                    
                    $e = new ErrorObject(
                                            $this->_request,
                                            E_VROOM_EXECUTE_QUERY_FAILED,
                                            $this->GetMysqlError($this->_queries['setAnswer'])
                                          );
                    $this->_dbConnection->rollback();
                    return $e;
                }
                $stmt->store_result();

                if($stmt->affected_rows != 1)
                {
                    //something's wrong with the DB. report to admin
                    $e = new ErrorObject(
                                            $this->_request,
                                            E_VROOM_INSERT_FAILED,
                                            $this->GetMysqlError($this->_queries['setAnswer'])
                                          );
                    $this->_dbConnection->rollback();
                    return $e;
                }
                
                //remove once the values have been serviced.
                unset($answers[$key]);
            }
            
            //If there are still some answers left, then it means we need to 
            //update some answers
            if(count($answers) > 0)
            {
                //if an error occured while updating the answers, do a rollabck
                //and send the error message back.
                $updateAnswers = $this->UpdateAnswers ($answers);
                if($updateAnswers instanceof ErrorObject)
                {
                    return $updateAnswers;
                }
            }
            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['setAnswer'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    private function UpdateAnswers(array $answers)
    {
        $attachment_path = '';
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['updateAnswer']))
        {        
            foreach($answers as $key=>$value)
            {
                if(array_key_exists('image', $value))
                    if(sizeof($value['image']) > 0)
                        $attachment_path = $this->SavePicture($value['image']);
                    
                //update all answers that have an answer id.
                //bind parameters
                $stmt->bind_param('dssi',  
                                    $value['value'], 
                                    $attachment_path, 
                                    $value['comment'],
                                    $value['answer_id']
                                 );

                //execute query
                if(!$stmt->execute())
                {
                    $e = new ErrorObject(
                                            $this->_request,
                                            E_VROOM_EXECUTE_QUERY_FAILED,
                                            $this->GetMysqlError($this->_queries['updateAnswer'])
                                          );
                    $this->_dbConnection->rollback();
                    return $e;
                }
                
                if($stmt->affected_rows != 1)
                {
                    $this->_dbConnection->rollback();
                    return false;
                }
                //remove once the values have been serviced.
                unset($answers[$key]);
            }
            
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['updateAnswer'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
        
    private function SavePicture($encodedImage)
    {
        //check if the directory exists
        $directory = $this->_config->GetFilestore();
                     //$value['survey_id'].$this->_config->GetPathSeparator();
        if(!is_dir($directory))
           mkdir ($directory, 0644, true);

        $uniqueId = substr($value['image'], strlen($value['image'])/2 - 12, 24);
        $filename = sha1($uniqueId.  microtime()).'.jpeg';
        $attachment_path = $filename;
        $data = trim(str_replace('\\r\\n', '', $encodedImage));
        $data = base64_decode($data);

        $im = imagecreatefromstring($data);
        if ($im !== false) 
        {
            imagejpeg($im, $directory.$filename);
            imagedestroy($im);
            return $filename;
        }
        return null;
    }


    public function SetNewUserInfo($userid, $password, $email)
    {
        //first call IsUsernameAvailable. Return only if username is available and there is a successful insert.
        $this->_dbConnection->autocommit(false);
        if(!$this->IsUsernameAvailable($userid))
        {
            $this->_dbConnection->rollback();
            return false;
        }
        
        if($stmt = $this->_dbConnection->prepare($this->_queries['setNewUserInfo']))
        {
            //bind parameters
            $stmt->bind_param('sss', $userid, $password, $email);

            //execute query
            if(!$stmt->execute())
            {
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['setNewUserInfo'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }
            $stmt->store_result();

            if($stmt->affected_rows != 1)
            {
                //something's wrong with the DB. report to admin
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_INSERT_FAILED,
                                        $this->GetMysqlError($this->_queries['setNewUserInfo'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }

            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['setNewUserInfo'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    public function UpdateUserInfo($userid, $email, $zip, $height, $sittingHeight, 
                                    $upperLegLength, $weightRange, $ageRange, 
                                    $gender, $shirtSize, $shoeSize)
    {
        $this->_dbConnection->autocommit(false);
        
        if($stmt = $this->_dbConnection->prepare($this->_queries['updateUserInfo']))
        {    
            //bind parameters
            $stmt->bind_param('isidiisddss', 
                                $ageRange,
                                $gender,
                                $height,
                                $shoeSize,
                                $weightRange,
                                $zip,
                                $shirtSize,
                                $sittingHeight,
                                $upperLegLength,
                                $email,
                                $userid
                             );

            //execute query
            if(!$stmt->execute())
            {
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['updateUserInfo'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }
            
            if($stmt->affected_rows != 1)
            {
                $this->_dbConnection->rollback();
                return false;
            }
            
            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['updateUserInfo'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    public function SetBadges($userid, array $badges)
    {
        $this->_dbConnection->autocommit(false);
        if($stmt = $this->_dbConnection->prepare($this->_queries['setBadges']))
        {
            foreach($badges as $key=>$value)
            {
                //bind parameters
                $stmt->bind_param('ss', $value, $userid);

                //execute query
                if(!$stmt->execute())
                {
                    $e = new ErrorObject(
                                            $this->_request,
                                            E_VROOM_EXECUTE_QUERY_FAILED,
                                            $this->GetMysqlError($this->_queries['setBadges'])
                                          );
                    $this->_dbConnection->rollback();
                    return $e;
                }
                if($stmt->affected_rows != 1)
                {
                    $this->_dbConnection->rollback();
                    return false;
                }
            }
            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['setBadges'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    //inserts a row in the survey table to indicate that user has started a survey.
    //should return the assigned survey id.
    public function SetNewSurvey($vehicleId, $questionSet_id, $userId)
    {
//               echo("<p>".__CLASS__."::".__FUNCTION__.": Received values: vehicleId = $vehicleId, questionSet_id = $questionSet_id, userId = $userId</p>");
//        $this->_dbConnection->autocommit(false);
        if($stmt = $this->_dbConnection->prepare($this->_queries['setNewSurvey']))
        {
            //bind parameters
            $stmt->bind_param('isis', $vehicleId, $userId, $questionSet_id, date('Y-m-d G:i:s', 0));

            //execute query
            if(!$stmt->execute())
            {
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['setNewSurvey'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }
            $stmt->store_result();

            if($stmt->affected_rows != 1)
            {
                //something's wrong with the DB. report to admin
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_INSERT_FAILED,
                                        $this->GetMysqlError($this->_queries['setNewSurvey'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }

            $insertedId = $this->_dbConnection->insert_id;
            $this->_dbConnection->commit();
            //return the data
            return $insertedId;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['setNewSurvey'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    private function GetLastestSurveyId($vehicleId, $questionSet_id, $userId)
    {
        if($stmt = $this->_dbConnection->prepare($this->_queries['getLatestSurveyId']))
        {
            //bind parameters
            $stmt->bind_param('isi', $vehicleId, $userid, $questionSet_id);

            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['getLatestSurveyId'])
                                      );
            }
            $stmt->store_result();

            $stmt->bind_result($surveyId);
            
            $stmt->fetch();
            //return the data
            return $surveyId;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['getLatestSurveyId'])
                                  );
        }
    }
    
    public function SetEndSurvey($surveyId)
    {
        //ending the survey like this saves queries required to get number of
        //questions associated to a set and then finding how many have been
        //answered.
        $this->_dbConnection->autocommit(false);
        if($stmt = $this->_dbConnection->prepare($this->_queries['setSurveyEnded']))
        {
            //bind parameters
            $stmt->bind_param('i', $surveyId);

            //execute query
            if(!$stmt->execute())
            {
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['setSurveyEnded'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }
            
            if($stmt->affected_rows != 1)
            {
                $this->_dbConnection->rollback();
                return false;
            }
            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['setSurveyEnded'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    public function SetNewPassword($loginId, $newPassword)
    {
        $this->_dbConnection->autocommit(false);
        if($stmt = $this->_dbConnection->prepare($this->_queries['resetPassword']))
        {
            //bind parameters
            $stmt->bind_param('ss', $newPassword, $loginId);

            //execute query
            if(!$stmt->execute())
            {
                $e = new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED,
                                        $this->GetMysqlError($this->_queries['resetPassword'])
                                      );
                $this->_dbConnection->rollback();
                return $e;
            }
            if($stmt->affected_rows != 1)
            {
                $this->_dbConnection->rollback();
                return false;
            }
            $this->_dbConnection->commit();
            //return the data
            return true;
        }
        else
        {
            $e = new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED,
                                    $this->GetMysqlError($this->_queries['resetPassword'])
                                  );
            $this->_dbConnection->rollback();
            return $e;
        }
    }
    
    public function GetEmailForLoginId($loginId)
    {
        //take statement from central repository and prepare statement
        if($stmt = $this->_dbConnection->prepare($this->_queries['getEmailId']))
        {
            //bind parameters
            $stmt->bind_param('s', $loginId);
            
            //execute query
            if(!$stmt->execute())
            {
                return new ErrorObject(
                                        $this->_request,
                                        E_VROOM_EXECUTE_QUERY_FAILED, 
                                        $this->GetMysqlError($this->_queries['getEmailId'])
                                      );
            }
            $stmt->store_result();
            
            if($stmt->num_rows === 0)
            {
                 return false;
            }
            
            $stmt->bind_result($email);
            //copy result from DB into bound variables
            $stmt->fetch();
            //return the data
            return $email;
        }
        else
        {
            return new ErrorObject(
                                    $this->_request,
                                    E_VROOM_PREPARE_QUERY_FAILED, 
                                    $this->GetMysqlError($this->_queries['getEmailId'])
                                   );
        }
    }
    public function LogError($requestHash, $request)
    {
        
    }
    
    public function CloseConnection()
    {
        $this->_dbConnection->commit();
        $this->_dbConnection->close();
    }
    
    private function GetMysqlError($statement)
    {
        $mysqlErrorCode = $this->_dbConnection->errno;
        $mysqlErrorDescription = $this->_dbConnection->error;
        
        //echo("<p>".__CLASS__."::".__FUNCTION__.": Received values: mysqlErrorCode = $mysqlErrorCode, mysqlErrorDescription = $mysqlErrorDescription</p>");
        
        return "Statement: $statement, Mysql error code: $mysqlErrorCode, Mysql error description: $mysqlErrorDescription";
    }
}

?>