<?php 


    date_default_timezone_set('Asia/Calcutta');
    function tbc_external_review_menu() {
        $items = array();
        
        $items["tbc_external_review/books_available"] = array(
            "title" => "ER Books Available",
            "page callback" => "tbc_external_review_all",
            "access arguments" => array("access tbc_external_review"),
            "type" => MENU_CALLBACK
        );
        $items["tbc_external_review/profile"] = array(
            "title" => "TBC External Reviewer Profile",
            "page callback" => "tbc_external_review_profile_all",
            "access arguments" => array("access tbc_external_review"),
            "type" => MENU_CALLBACK
        );
        $items["tbc_external_review/view_reviewers"] = array(
            "title" => "ER List Reviewers",
            "page callback" => "tbc_external_review_view_reviewers_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" =>  MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/reviewer_selections"] = array(
            "title" => "TBC External Reviewer Selections",
            "page callback" => "tbc_external_review_reviewer_selections_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" => MENU_CALLBACK
        );
        $items["tbc_external_review/selected_books"] = array(
            "title" => "External Review Books",
            "page callback" => "tbc_external_review_selected_books_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/status"] = array(
            "title" => "External Review Status",
            "page callback" => "tbc_external_review_status_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/comments"] = array(
            "title" => "External Review Comments",
            "page callback" => "tbc_external_review_comments_all",
            "access arguments" => array("download tbc_external_review"),
                "weight" => 30,
            "type" => MENU_NORMAL_ITEM,
        );
        $items["tbc_external_review/comments/new"] = array(
            "title" => "Review Comment",
            "page callback" => "tbc_external_review_comments_all",
            "access arguments" => array("download tbc_external_review"),
            "weight" => 1,
            "type" => MENU_DEFAULT_LOCAL_TASK,
        );
        $items["tbc_external_review/comments/view"] = array(
            "title" => "View Comments",
            "page callback" => "tbc_external_review_comments_view_all",
            "access arguments" => array("download tbc_external_review"),
            "weight" => 2,
            "type" => MENU_LOCAL_TASK,
        );
        $items["tbc_external_review/comments/captions"] = array(
            "title" => "Review Captions",
            "page callback" => "tbc_external_review_comments_captions_all",
            "access arguments" => array("download tbc_external_review"),
            "weight" => 2,
            "type" => MENU_LOCAL_TASK,
        );
        $items["tbc_external_review/manage_comments"] = array(
            "title" => "ER Manage Comments",
            "page callback" => "tbc_external_review_manage_comments_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );
        
        $items["tbc_external_review/mail_comments"] = array(
            "title" => "ER Mail Comments",
            "page callback" => "tbc_external_review_mail_comments_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" => MENU_CALLBACK
        );
        $items["tbc_external_review/manage_status"] = array(
            "title" => "ER View Status",
            "page callback" => "tbc_external_review_manage_status_all",
            "access arguments" => array("administer tbc_external_review"),
            "weight" => 30,
            "type" => MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/manage_status/all"] = array(
            "title" => "All",
            "page callback" => "tbc_external_review_manage_status_all",
            "access arguments" => array("administer tbc_external_review"),
            "weight" => 1,
            "type" => MENU_DEFAULT_LOCAL_TASK,
        );
        $items["tbc_external_review/manage_status/recent"] = array(
            "title" => "Recently Completed",
            "page callback" => "tbc_external_review_manage_status_recent_all",
            "access arguments" => array("administer tbc_external_review"),
            "weight" => 2,
            "type" => MENU_LOCAL_TASK,
        );
        $items["tbc_external_review/add_book"] = array(
            "title" => "ER Add Book",
            "page callback" => "tbc_external_review_add_book_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" =>  MENU_CALLBACK
        );
        $items["tbc_external_review/ajax"] = array(
            "page callback" => "tbc_external_review_ajax",
            "access callback" => TRUE,
            "type" => MENU_CALLBACK
        );
        
        $items["tbc_external_review/eligibility_test"] = array(
            "title" => "Eligibility Test",
            "page callback" => "tbc_external_review_eligibility_test_all",
            "access arguments" => array("access tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/manage_tests"] = array(
            "title" => "Manage Eligibility Test",
            "page callback" => "tbc_external_review_manage_tests_all",
            "access arguments" => array("administer tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        ); 
        $items["tbc_external_review/er"] = array(
            "title" => "ER Assign/ Remove Book",
            "page callback" => "tbc_external_review_er_assign_book_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_NORMAL_ITEM            
        );          
        $items["tbc_external_review/er/assign_book"] = array(
            "title" => "ER Assign Book",
            "page callback" => "tbc_external_review_er_assign_book_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_DEFAULT_LOCAL_TASK            
        );     
        $items["tbc_external_review/er/remove_book"] = array(
            "title" => "ER Remove Book",
            "page callback" => "tbc_external_review_er_remove_book_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_LOCAL_TASK            
        ); 
        $items["tbc_external_review/mail"] = array(
            "title" => "Mail Email To Reviewer",
            "page callback" => "tbc_external_review_mail_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_DEFAULT_LOCAL_TASK 

        ); 
        $items["tbc_external_review/reminder"] = array(
            "title" => "Reviewer Reminder",
            "description" => "Failed to complete review",
            "page callback" => "_er_reminder_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );  
        $items["tbc_external_review/reminder/reviewer_reminder"] = array(
            "title" => "Reviewer reminder",
            "description" => "Failed to complete review",
            "page callback" => "_er_reminder_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_DEFAULT_LOCAL_TASK             
        );   
        $items["tbc_external_review/reminder/tbc_reviewer_reminder"] = array(
            "title" => "TBC Reviewer Reminder",
            "description" => "Failed to review",
            "page callback" => "_er_tbc_reviewer_reminder_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_LOCAL_TASK         
        );
        $items["tbc_external_review/reminder_all"] = array(
            "title" => "TBC/ ER Reviewer Reminders",
            "description" => "Failed to complete review",
            "page callback" => "_er_reminder_page_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_NORMAL_ITEM
        );  
        $items["tbc_external_review/reminder_all/reviewer_reminder_all"] = array(
            "title" => "Reviewer reminder",
            "description" => "Failed to complete review",
            "page callback" => "_er_reminder_page_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_DEFAULT_LOCAL_TASK             
        );   
        $items["tbc_external_review/reminder_all/tbc_reviewer_all_reminder"] = array(
            "title" => "TBC Reviewer Reminder",
            "description" => "Failed to review",
            "page callback" => "_tbc_reviewer_reminder_page_all",   
            "access arguments" =>array("download tbc_external_review"),
            "type" => MENU_LOCAL_TASK
        );                 
        return $items;
    }

    function tbc_external_review_perm() {
        return array(
            "access tbc_external_review", "administer tbc_external_review",
            "download tbc_external_review"
        );
    }

    /*
        book selection form
        {external_review_details} database required.
        fields: preference_id, uid, status
        status = 1 for selected.
    */
    function tbc_external_review_form($form_state) {
        global $user;
        $form = array();
        
        /* create multiple checkboxes from database */
        $result = remaining_books();
        $num_rows = mysql_num_rows($result);
        if($num_rows > 0) {
            while($row = db_fetch_object($result)) {
                $form[$row->id] = array(
                    "#type" => "checkbox",
                    "#title" => t("{$row->book} by {$row->author} ( ed: {$row->edition}, pub: {$row->year})"),
                );
            }
            $form["submit"] = array(
                "#type" => "submit",
                "#value" => "Submit",
            );
        } else {
            drupal_set_message("<strong>All the available books have been selected. Please wait for more updates.</strong>", "status");
        }
        return $form;
    }

    function tbc_external_review_form_validate($form, &$form_state) {
        $result = remaining_books();
        $count = 0;
        $selections = array();
        while($row = db_fetch_object($result)) {
            if($form_state["values"][$row->id] == 1) {
                $count++;
                array_push($selections, $row->id);
            }
        }
        $remaining = remaining_books_count();
        if($count > $remaining) {
            form_set_error("", "You can select a maximum of <strong>{$remaining}</strong> books.");
        } else {
            $form_state["values"]["selections"] = $selections;
        }
    }
    
    function tbc_external_review_form_submit($form, &$form_state) {
        global $user;
        
        $selections = $form_state["values"]["selections"];
        foreach($selections as $selection) {
            $query = "update {external_review_details} set uid = {$user->uid}, status = 1, review = 1  where preference_id = {$selection}";
            db_query($query);
        }
        drupal_set_message("<strong>Your selections have been updated sucessfully.</strong>", "status");
    }

    function tbc_external_review_all() {
        global $user;
        $page_content = "";
        
        $remaining = remaining_books_count();
        if($remaining > 0) {
            $page_content = "You have <strong>{$remaining}</strong> selections remaining:";
            $page_content .= drupal_get_form("tbc_external_review_form");
        } else {
            $page_content = "Thanks for your selections.";
        }
        return $page_content;
    }

    /* helper functions */
    function remaining_books_count() {
        global $user;
        /*finding how many books the user can select */
        $query = "select count(*) from {external_review_details} where uid = {$user->uid}";
        $result = db_query($query);
        $row = db_fetch_array($result);
        $remaining = 6 - $row["count(*)"];
        return $remaining;
    }

    function remaining_books() {
        $query = "select * from {textbook_companion_preference} where id in (select preference_id from {external_review_details} where status = 0)";
        $result = db_query($query);
        return $result;
    }

    function suffix($number) {
        /* 
            considering number will be not more than 10 
            eg: 11th, 21st - Not required
        */
        $output = "";                                                               
        switch ($number) {                                                             
            case "1":                                                               
                $output = $number. "st";                                              
                break;                                                              
            case "2":                                                               
                $output = $number. "nd";                                              
                break;                                                              
            case "3":                                                               
                $output = $number. "rd";                                              
                break;                                                              
            default:                                                                
                $output = $number. "th";                                              
                break;                                                              
        }                                                                           
        return $output;
    }

    function send_mail_attachment($from, $to, $cc, $bcc, $subject, $message, $file) {
        // $file should include path and filename
        $filename = basename($file);
        $file_size = filesize($file);
        $content = chunk_split(base64_encode(file_get_contents($file))); 
        $uid = md5(uniqid(time()));
        $from = str_replace(array("\r", "\n"), '', $from); // to prevent email injection
        $header = "From: ".$from."\r\n"
            ."Cc: ".$cc."\r\n"
            ."Bcc: ".$bcc."\r\n"
            ."MIME-Version: 1.0\r\n"
            ."Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n"
            ."This is a multi-part message in MIME format.\r\n" 
            ."--".$uid."\r\n"
            ."Content-type: text/html; charset=UTF-8; format=flowed\r\n"
            ."Content-Transfer-Encoding: 7bit\r\n\r\n"
            .$message."\r\n\r\n"
            ."--".$uid."\r\n"
            ."Content-Type: application/octet-stream; name=\"".$filename."\"\r\n"
            ."Content-Transfer-Encoding: base64\r\n"
            ."Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n"
            .$content."\r\n\r\n"
            ."--".$uid."--"; 
        return mail($to, $subject, "", $header);
    }

    /*
        external reviewer profile form,
        save the data in {external_review_profile}
    */
    function tbc_external_review_profile_form() {
        global $user;
        $form = array();
        /* populating the form with previous entry details if exists */
        $query = "
            SELECT * FROM {external_review_profile} 
            WHERE uid = %d
        ";
        $result = db_query($query, $user->uid);
        $row = db_fetch_array($result);
        
        $form["first_name"] = array(
            "#type" => "textfield",
            "#title" => t("First Name"),
            "#required" => True,
            "#default_value" => $row["first_name"],
        );
        $form["last_name"] = array(
            "#type" => "textfield",
            "#title" => t("Last Name"),
            "#required" => True,
            "#default_value" => $row["last_name"],
        );
        $form["designation"] = array(
            "#type" => "textfield",
            "#title" => t("Designation"),
            "#required" => True,
            "#default_value" => $row["designation"],
        );
        $form["university"] = array(
            "#type" => "textfield",
            "#title" => t("University/Institute"),
            "#required" => True,
            "#default_value" => $row["university"],
        );
        $form["phone"] = array(
            "#type" => "textfield",
            "#title" => t("Phone Number"),
            "#required" => True,
            "#default_value" => $row["phone"],
        );
        $form["email"] = array(
            "#type" => "textfield",
            "#title" => t("Email"),
            "#required" => True,
            "#default_value" => $row["email"],
        );
        $form["alt_email"] = array(
            "#type" => "textfield",
            "#title" => t("Alternate Email"),
            "#default_value" => $row["alt_email"],
        );
        $form["address"] = array(
            "#type" => "textarea",
            "#title" => t("Postal Address"),
            "#required" => True,
            "#default_value" => $row["address"],
        );
        $form["description"] = array(
            "#type" => "textarea",
            "#title" => t("Description: 
                (Research interest / Level of expertise in Scilab / Teaching Expertise )
            "),
            "#required" => True,
            "#default_value" => $row["description"],
        );
        $form["submit"] =array(
            "#type" => "submit",
            "#value" => "Update Profile",
        );
        return $form;
    }

    function tbc_external_review_profile_form_validate($form, &$form_state) {
        /* validating phone */
        if(!preg_match("/^[0-9]+$/", $form_state["values"]["phone"])) {
            form_set_error("phone", "Please enter a valid Phone Number, only (0-9) allowed.");
        }
        /*validating email */
        if(!valid_email_address($form_state["values"]["email"])) {
            form_set_error("email", "Please enter a valid Email address");
        }
        if($form_state["values"]["alt_email"] && !valid_email_address($form_state["values"]["alt_email"])) {
            form_set_error("alt_email", "Please enter a valid Alternate Email address" );
        }
        /* validating address */
        if(strlen($form_state["values"]["address"]) < 20) {
            form_set_error("address", "Please enter a proper Postal Address.");
        }
        /* validating description */
        if(strlen($form_state["values"]["description"]) < 25) {
            form_set_error("description", "Please enter a longer description.");
        }
    }

    function tbc_external_review_profile_form_submit($form, &$form_state) {
        global $user;
        /* setting $val as the new values array */
        $val = array(); 
        $val = $form_state["values"];
        
        /* checking whether the user already has an entry */
        $query = "select uid from {external_review_profile} where uid = %d";
        $result = db_query($query, $user->uid);
        $has_entry = mysql_num_rows($result);
        
        if($has_entry) {
            /* update the existing entry */
            $query = "update {external_review_profile} 
                    set 
                    first_name = '%s', last_name = '%s', designation = '%s', 
                    university = '%s', phone = '%s', email = '%s', alt_email = '%s',
                    address = '%s', description = '%s' 
                    where uid = %d
            ";
            $result = db_query(
                $query, $val["first_name"], $val["last_name"], $val["designation"], $val["university"],
                $val["phone"], $val["email"], $val["alt_email"], $val["address"], $val["description"],
                $user->uid
            );
        } else {
            /* create a new entry */
            $query = "insert into {external_review_profile} 
                    (uid, first_name, last_name, designation, university, phone, email, alt_email, address, description) 
                    values
                    (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
            ";
            $result = db_query(
                $query, $user->uid, $val["first_name"], $val["last_name"], $val["designation"], $val["university"],
                $val["phone"], $val["email"], $val["alt_email"], $val["address"], $val["description"]
            );
        }
        if($result) {
            drupal_set_message("Profile updated successfully. Thank you.", "status");
        } else {
            drupal_set_message("Database updation failed. Please contact support.", "error");
        }
        // drupal_goto('tbc_external_review');
        drupal_goto('tbc_external_review/eligibility_test');
    }

    function tbc_external_review_profile_all() {
        $page_content = "";
        $page_content .= drupal_get_form("tbc_external_review_profile_form");
        return $page_content;
    }

    function tbc_external_review_view_reviewers_all() {
        $page_content = "";
        $query = "
            SELECT * FROM {external_review_profile}
            WHERE active = 1
        ";
        $result = db_query($query);
        
        $page_content .= "<ul>";
        while($row = db_fetch_object($result)) {
            $page_content .= "<li>";
            $page_content .= l("{$row->first_name} {$row->last_name}", "tbc_external_review/reviewer_selections/{$row->uid}");
            $page_content .= "</li>";
        }
        $page_content .= "</ul>";
        return $page_content;
    }

    function tbc_external_review_reviewer_selections_all($uid=0) {
        $page_content = "";
        
        $query = "SELECT * FROM {external_review_profile} WHERE uid = %d";
        $result = db_query($query, $uid);
        $row = db_fetch_object($result);
        $page_content .= "Books selected by <strong>{$row->first_name} {$row->last_name}</strong>";
        $query = "
            SELECT * FROM {textbook_companion_preference} WHERE id IN 
            (SELECT preference_id FROM {external_review_details} WHERE uid = %d)
        ";
        $result = db_query($query, $uid);
        
        $page_content .= "<ol>";
        while($row = db_fetch_object($result)) {
            $page_content .= "<li>";
            $page_content .= t("{$row->book} by {$row->author} ( ed: {$row->edition}, pub: {$row->year})");
            $page_content .= "</li>";
        }
        $page_content .= "</ol>";
        return $page_content;
    }

    function tbc_external_review_selected_books_all() {
        global $user;
        $page_content = "";
        $query = "
            SELECT * FROM {textbook_companion_preference} WHERE id IN 
            (SELECT preference_id FROM {external_review_details} WHERE uid = %d)
        ";
        $result = db_query($query, $user->uid);
        
        $headers = array(
            "Book", "Author",
            "Edition", "Action",
        );
        $rows = array();
        while($row = db_fetch_object($result)) {
            $item = array(
                "{$row->book}",
                "{$row->author}",
                "{$row->edition}",
                l(t("Download [.zip]"), "full_download_external/book/{$row->id}")
            );
            array_push($rows, $item);
        }
        $page_content .= theme("table", $headers, $rows);
        return $page_content;
    }
    
    function tbc_external_review_comments_all() {
        global $user;
        $page_content = "";
        $page_content = "* Use this form to notify only examples that have errors.";
        $page_content .= drupal_get_form("tbc_external_review_comment_form");
        return $page_content;
    }

    function tbc_external_review_comments_view_all($preference_id = 0, $review = 0) {
        global $user;
        $page_content = "";
        $page_content .= "<br>";
        
        /* creating review links */
        $query = "
            SELECT DISTINCT(review) FROM external_review_comments
            WHERE preference_id = %d
            ORDER BY review DESC "; $result = db_query($query, $preference_id); $tmp = $review; // for active class in case of 0
        while($row = db_fetch_object($result)) {
            $option["attributes"]["class"] = "review-links";
            if($review == $row->review) {
                $option["attributes"]["class"] .= " active";
            } else if($tmp == 0) {
                $option["attributes"]["class"] .= " active";
                $tmp = -1;
            }
            $page_content .= l(
                suffix($row->review) . "review", 
                "tbc_external_review/comments/view/{$preference_id}/{$row->review}",
                $option
            );
        }
        $page_content .= "<br><br><hr>";
        if($preference_id) {
            if($review) {
                $query = "
                    SELECT erc.*, cha.number AS chapter, exa.number AS example FROM external_review_comments erc
                    LEFT JOIN textbook_companion_chapter cha ON cha.id = erc.chapter_id 
                    LEFT JOIN textbook_companion_example exa ON exa.id = erc.example_id
                    WHERE erc.preference_id = %d AND erc.review = %d
                    ORDER BY erc.time DESC
                ";
                $result = db_query($query,$preference_id, $review);
            } else {
                $query = "
                    SELECT erc.*, cha.number AS chapter, exa.number AS example FROM external_review_comments erc
                    LEFT JOIN textbook_companion_chapter cha ON cha.id = erc.chapter_id 
                    LEFT JOIN textbook_companion_example exa ON exa.id = erc.example_id
                    WHERE erc.preference_id = %d AND review = (
                        SELECT MAX(review) FROM external_review_comments WHERE preference_id = %d
                    )
                    ORDER BY erc.time DESC
                ";
                $result = db_query($query, $preference_id, $preference_id);
            }
            $headers = array(
                "Chapter", "Example",
                "Time", "Action"
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $options = array(
                    /* # linking in drupal l() */
                    "fragment" => " ",
                    "external" => TRUE,
                    "attributes" => array(
                        "class" => "view-comment",
                        "data-comment" => "{$row->id}",
                    )
                );
                $item = array(
                    "{$row->chapter}",
                    "{$row->example}",
                    "{$row->time}",
                    l("View", "", $options),
                );
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
            $page_content .= "<div id='lightbox-wrapper'>";
            $page_content .= "<div id='lightbox-inner'></div></div> ";
        } else {
            $query = "
                SELECT * FROM textbook_companion_preference
                WHERE id IN (
                    SELECT preference_id FROM external_review_details 
                    WHERE uid = %d
                )
            ";
            $result = db_query($query, $user->uid);
            $headers = array(
                "Book", "Author",
                "Action"
            );
            $rows = array();
            
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}",
                    "{$row->author}",
                    l("View", "tbc_external_review/comments/view/" . $row->id),
                );
                array_push($rows, $item);
            }
            $page_content = theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_comments_captions_form($form_state, $preference_id) {
        $form = array();
        /*fetching pre-existing caption entry if any */
        $query = "
            SELECT examples FROM external_review_captions
            WHERE preference_id = {$preference_id}
            ";
        $result = db_query($query);
        $row = db_fetch_object($result);
        $examples = $row->examples;
        $examples = explode(",", $examples);
        
        /* fetching chapter and examples */
        $c_query = "
            SELECT * FROM textbook_companion_chapter
            WHERE preference_id = {$preference_id}
            ORDER BY number
        ";
        $c_result = db_query($c_query);
        while($c_row = db_fetch_object($c_result)) {
            $form[$c_row->id] = array(
                "#type" => "fieldset",
                "#title" => "Chapter {$c_row->number} ({$c_row->name})"
            );
            $e_query = "
                SELECT * FROM textbook_companion_example
                WHERE chapter_id = {$c_row->id}
                ORDER BY number
            ";
            $e_result = db_query($e_query);
            while($e_row = db_fetch_object($e_result)) {
                $form[$c_row->id][$e_row->id] = array(
                    "#type" => "checkbox",
                    "#title" => $e_row->caption,
                );
                if(in_array($e_row->id, $examples)) {
                    $form[$c_row->id][$e_row->id]["#default_value"] = 1;
                }
            }
        }
        $form["preference_id"] = array(
            "#type" => "hidden",
            "#value" => $preference_id
        );
        $form["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit"
        );
        return $form;
    }

    function tbc_external_review_comments_captions_form_submit($form, &$form_state) {
        $values = $form_state["values"];
        $preference_id = $values["preference_id"];
        $examples = array();
        foreach($values as $key => $value) {
            if(is_numeric($key) && $value) {
                array_push($examples, $key);
            }
        }
        $examples = implode(",", $examples);
        /* inserting or updating based on row existence */
        $query = "
            SELECT id FROM external_review_captions
            WHERE preference_id = {$preference_id} 
            AND review = (
                SELECT review FROM external_review_details
                WHERE preference_id = {$preference_id}
            )
        ";
        $result = db_query($query);
        $num_rows = mysql_num_rows($result);
        if($num_rows) {
            /* updating the existing */
            $query = "
                UPDATE external_review_captions
                SET examples = '{$examples}'
                WHERE preference_id = {$preference_id}
                AND review = (
                    SELECT review FROM external_review_details
                    WHERE preference_id = {$preference_id}
                )
            ";
            $result = db_query($query);
            if($result) {
                drupal_set_message("Caption error list updated successfully", "status");
            } else {
                drupal_set_message("Caption error list updation failed.", "error");
            }
        } else {
            /* creating a new entry */
            $query = "
                INSERT INTO external_review_captions
                (preference_id, examples, review)
                VALUES
                (
                    {$preference_id}, '{$examples}', (
                        SELECT review FROM external_review_details
                        WHERE preference_id = {$preference_id}
                    )
                )
            ";
            $result = db_query($query);
            if($result) {
                drupal_set_message("Caption error list updated successfully.", "status");
            } else {
                drupal_set_message("Caption error list updation failed.", "error");
            }
        }
    }

    function tbc_external_review_comments_captions_all($preference_id=0) {
        global $user;
        $page_content = "";
        if($preference_id) {
            $page_content .= "<p>* Check mark all the <strong>captions that do not make sense</strong>.</p>";
            $page_content .= drupal_get_form("tbc_external_review_comments_captions_form", $preference_id);
        }
        else {
            $query = "
                SELECT * FROM {textbook_companion_preference} WHERE id IN 
                (SELECT preference_id FROM {external_review_details} WHERE uid = %d)
                ";
            $result = db_query($query, $user->uid);
            
            $headers = array(
                "Book", "Edition",
                "Action",
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}<br>{$row->author}",
                    "{$row->edition}",
                    l(t("Review Captions"), "tbc_external_review/comments/captions/{$row->id}"),
                );
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_comment_form($form_state) {
        $form = array();
        global $user;
        global $base_url;
        $form["wrapper"] = array(
            "#type" => "fieldset",
            "#title" => t("
                Reviewer comments
                <img id='ajax-loader' src='" . "{$base_url}/" . drupal_get_path("module", "tbc_external_review") ."/img/throbber.gif'>
            "),
            "#prefix" => "<div id='comment-form-wrapper'>",
            "#suffix" => "</div>",
        );
        $form["wrapper"]["book"] = array(
            "#type" => "select",
            "#title" => t("Please select the book."),
            "#options" => _get_books($user->uid),
        );
        $form["wrapper"]["chapter"] = array(
            "#type" => "select",
            "#title" => t("Please select the chapter"),
        );
        $form["wrapper"]["example"] = array(
            "#type" => "select",
            "#title" => t("Please select the example"),
        );
        $form["wrapper"]["error"] = array(
            "#type" => "fieldset",
            "#title" => "Types of errors found",
            "#prefix" => "<div id='comment-error-wrapper'>",
            "#suffix" => "</div>",
        );
        $form["wrapper"]["error"]["ncf"] = array(
            "#type" => "radios",
            "#title" => t("Naming convention followed? (Checklist point 1, 2 and 4)"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["eit"] = array(
            "#type" => "radios",
            "#title" => t("Error in textbook mentioned as comment?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["axl"] = array(
            "#type" => "radios",
            "#title" => t("Axes labeled?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["sua"] = array(
            "#type" => "radios",
            "#title" => t("Symbols used are appropiate?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["sfu"] = array(
            "#type" => "radios",
            "#title" => t("Scilab functions used?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["sum"] = array(
            "#type" => "radios",
            "#title" => t("Solved using Matlab in textbook?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["aci"] = array(
            "#type" => "radios",
            "#title" => t("Appropriate comments included as mentioned in checklist point 7?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["auu"] = array(
            "#type" => "radios",
            "#title" => t("Appropriate units used as given in the textbook?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["teo"] = array(
            "#type" => "radios",
            "#title" => t("Typographical errors in output and/or comments?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["amt"] = array(
            "#type" => "radios",
            "#title" => t("Answers matching with the textbook?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
        );
        $form["wrapper"]["error"]["comment"] = array(
            "#type" => "textarea",
            "#title" => t("Other commment? (if any)"),
        );
        $form["wrapper"]["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit Comment"
        );
        return $form;
    }
    
    function tbc_external_review_comment_form_validate($form, &$form_state) {
        // for future use
    }

    function tbc_external_review_comment_form_submit($form, &$form_state) {
        global $user;
        $v = $form_state["values"];
        $query = "
            INSERT INTO {external_review_comments}
            (uid, preference_id, chapter_id, example_id, ncf, eit, axl, sua, sfu, sum, aci, auu, teo, amt, comment, review)
            VALUES
            (
                %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '%s', 
                (
                    SELECT review FROM external_review_details WHERE preference_id = %d
                )
            )
        ";
        $result = db_query(
            $query, $user->uid, $v["book"], $v["chapter"], $v["example"], 
            $v["ncf"], $v["eit"], $v["axl"], $v["sua"], $v["sfu"], $v["sum"], 
            $v["aci"], $v["auu"], $v["teo"], $v["amt"], $v["comment"], $v["book"]
        );
        drupal_set_message(t("Comment submited successfully"), "status");
    }

    /* Ajax Calls */
    function tbc_external_review_ajax($item, $key) {
        function _bool($var) {
            if($var == 1) {
                return "No";
            }
            return "Yes";
        }
        
        $data = "";
        if($item == "book" && $key) {
            $query = "select * from {textbook_companion_chapter} where preference_id = %d order by number";
            $result = db_query($query, $key);
            
            $data .= "<option value='0'>Please select the chapter.</option>";
            while($row = db_fetch_object($result)) {
                $data .= "<option value='{$row->id}'>{$row->number} {$row->name}</option>";
            }
        } else if($item == "chapter" && $key) {
            $query = "select * from {textbook_companion_example} where chapter_id = %d order by number";
            $result = db_query($query, $key);
            
            $data .= "<option value='0'>Please select the example.</option>";
            while($row = db_fetch_object($result)) {
                $data .= "<option value='{$row->id}'>{$row->number} {$row->caption}</option>";
            }
        } else if($item == "comment" && $key) {
            $query = "
                SELECT * FROM external_review_comments erc 
                LEFT JOIN textbook_companion_example_files tcef 
                ON (erc.example_id = tcef.example_id AND tcef.filetype = 'S')
                WHERE erc.id = %d
            ";
            $result = db_query($query, $key);
            $row = db_fetch_object($result);
            
            /* fetching example file data */
            $uploads_dir = $_SERVER['DOCUMENT_ROOT'] . base_path() . "uploads/";
            $example_path = $uploads_dir . $row->filepath;
            $example = file_get_contents($example_path);
            
            $data .= "<table><th>Type of Error</th><th>Status</th>";
            $data .= "<tr><td>Naming convention followed?</td><td>" . _bool($row->ncf) . "</td></tr>";
            $data .= "<tr><td>Error in textbook mentioned as comment?</td><td>" . _bool($row->eit) . "</td></tr>";
            $data .= "<tr><td>Problem statement included in code?</td><td>" . _bool($row->psi) . "</td></tr>";
            $data .= "<tr><td>Axes labeled?</td><td>" . _bool($row->axl) . "</td></tr>";
            $data .= "<tr><td>Symbols used are appropiate?</td><td>" . _bool($row->sua) . "</td></tr>";
            $data .= "<tr><td>Scilab functions used?</td><td>" . _bool($row->sfu) . "</td></tr>";
            $data .= "<tr><td>Solved using Matlab in textbook?</td><td>" . _bool($row->sum) . "</td></tr>";
            $data .= "<tr><td>Appropriate comments included as mentioned in checklist point 7?</td><td>" . _bool($row->aci) . "</td></tr>";
            $data .= "<tr><td>Appropriate units used as given in the textbook?</td><td>" . _bool($row->auu) . "</td></tr>";
            $data .= "<tr><td>Typographical errors in output and/or comments?</td><td>" . _bool($row->teo) . "</td></tr>";
            $data .= "<tr><td>Answers matching with the textbook?</td><td>" . _bool($row->amt) . "</td></tr>";
            $data .= "<tr><td>Any Other Comment?</td><td>" . $row->comment . "</td></tr>";
            $data .= "</table>";
            $data .= "<textarea style='width:100%; height:200px; background:#fafafa' readonly>";
            $data .= "{$example}</textarea>";
        } else if($item == "hide-show" && $key) {
            $query = "
                UPDATE external_review_comments
                SET hidden = !hidden
                WHERE id = %d
            ";
            $result = db_query($query, $key);
            $data .= $key;

        } 

         else if($item == "hide-show1") {
             
            $query = "
                UPDATE external_review_details
                SET hidden = !hidden
                WHERE preference_id = %d
            ";
            $result = db_query($query,$key);
            $data .= $key;

        }else if($item == "toggle" && $key) {
            $query = "
                UPDATE external_review_comments
                SET hidden = !hidden
                WHERE preference_id = %d AND (
                    sfu != 1 AND
                    sum != 0 AND
                    amt != 1
                )
            ";
            $result = db_query($query, $key);
            $data .= $key;
        }
        
        echo $data;
        exit();
    }

    /* Comment form helper functions */
    function _get_books($uid) {
        $query = "select * from {textbook_companion_preference} where id in 
            (select preference_id from {external_review_details} where uid = %d)
        ";
        $result = db_query($query, $uid);
        
        $books = array();
        $books[0] = "Please select a book";
        while($row = db_fetch_object($result)) {
            $books[$row->id] = $row->book;
        }
        return $books;
    }

    function tbc_external_review_status_edit_form($form_state, $preference_id=0) {
        $query = "
            SELECT * FROM external_review_details
            WHERE preference_id = %d
        ";
        $result = db_query($query, $preference_id);
        $row = db_fetch_object($result);
        
        $form = array();
        $form["wrapper"] = array(
            "#prefix" => "<div id='status-edit-form'>",
            "#suffix" => "</div>"
        );
        $form["wrapper"]["review"] = array(
            "#type" => "select",
            "#title" => "Review Number",
            "#options" => range(0,10),
            "#default_value" => $row->review
        );
        $form["wrapper"]["status"] = array(
            "#type" => "radios",
            "#title" => "Status",
            "#options" => array(
                t("Ongoing"),
                t("Completed")
            ),
            "#default_value" => $row->completed
        );
        $form["wrapper"]["preference_id"] = array(
            "#type" => "hidden",
            "#value" => $preference_id
        );
        $form["wrapper"]["submit"] = array(
            "#type" => "submit",
            "#value" => "Update"
        );
        $form["wrapper"]["cancel"] = array(
            "#type" => "button",
            "#value" => "Cancel",
        );
        return $form;
    }

    function tbc_external_review_status_edit_form_submit($form, &$form_state) {
        $query = "
            UPDATE external_review_details
            SET review = {$form_state['values']['review']},
            completed = {$form_state['values']['status']}
            WHERE preference_id = {$form_state['values']['preference_id']}
        ";
        $result = db_query($query);
        if(!$result) {
            drupal_set_message("An error occured during updation.", "error");
        } else {
            drupal_set_message("Updated successfully.", "status");
        }
    }

    function tbc_external_review_manage_status_all($action="", $preference_id=0, $confirm="") {
        global $base_url;
        $page_content = "";
        if($action == "next" && $preference_id && $confirm == "yes") {
            $query = "
                UPDATE external_review_details 
                SET review = review + 1, completed = 0, sent = 0
                WHERE preference_id = %d
            ";
            $result = db_query($query, $preference_id);
            if (!$result) {
                drupal_set_message("Database updation failed", "error");
            } else {
                /* preparing to send mail to reviewer */
                $query = "
                    SELECT * FROM textbook_companion_preference pre
                    LEFT JOIN textbook_companion_proposal pro ON pre.proposal_id = pro.id
                    LEFT JOIN external_review_details erd ON pre.id = erd.preference_id
                    LEFT JOIN users usr ON erd.uid = usr.uid
                    WHERE pre.id = %d
                ";
                $result = db_query($query, $preference_id);
                $row = db_fetch_object($result);
                $review_no = suffix($row->review);
                $download_link = "<a href='{$base_url}/full_download_external/book/{$preference_id}'>Download [.zip]</a>";
                
                /* sending mail */
                $to = "{$row->mail}";
                $subject = "Please Start {$review_no} Review - Textbook Companion Project";
                $body = "
                    Dear {$row->name} <br><br>
                    The student has submitted the corrected codes.<br>
                    Please start the {$review_no} review for the following book.<br><br>
                    Book: <b>{$row->book}</b> <br>
                    Author: <b>{$row->author}</b><br>
                    Review: <b>{$review_no}</b> <br><br>
                    The updated code for the book can be downloaded here: <b>{$download_link}</b><br><br>
                    You can start giving comments on error codes for this review.<br>
                    Please make sure to mark the review as completed again after you finish this review.<br><br>
                    Thank you for your contribution. <br><br>
                    Best Wishes, <br>
                    Scilab.in
                ";
                $message = array(
                    "to" => $to,
                    "subject" => $subject,
                    "body" => $body,
                    "headers" => array(
                        "From" => "textbook@scilab.in",
                        "Bcc" => "mukulrkulkarni@gmail.com, lavitha89@gmail.com, rush2jrp@gmail.com",
                        "Content-Type" => "text/html; charset=UTF-8; format=flowed"
                    )
                );
                drupal_mail_send($message);
                drupal_set_message("Next review started.", "status");
                drupal_goto("tbc_external_review/manage_status");
            }
        } else if($action == "next" && $preference_id) {
            $query = "
                SELECT pre.book, pre.author, usr.name, erd.review, ext.name AS reviewer FROM textbook_companion_preference pre
                LEFT JOIN textbook_companion_proposal pro ON pre.proposal_id = pro.id
                LEFT JOIN users usr ON pro.uid = usr.uid
                LEFT JOIN external_review_details erd ON  pre.id = erd.preference_id
                LEFT JOIN users ext ON erd.uid = ext.uid
                WHERE pre.id = %d
            ";
            $result = db_query($query, $preference_id);
            $row = db_fetch_object($result);
            $page_content .= "Are you sure you want start the <b>" . suffix($row->review+1) . "</b> review? <br><br>";
            $page_content .= "Book: <b>{$row->book}</b> <br>";
            $page_content .= "Author: <b>{$row->author}</b> <br>";
            $page_content .= "Contributor: <b>{$row->name}</b> <br>";
            $page_content .= "External Reviewer: <b>{$row->reviewer}</b> <br><br>";
            $page_content .= l("Yes", "tbc_external_review/manage_status/next/{$preference_id}/yes");
            $page_content .= " | ";
            $page_content .= l("Cancel", "tbc_external_review/manage_status");
        } else if($action = "edit" && $preference_id) {
            $query = "
                SELECT * FROM external_review_details erd
                LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
                WHERE preference_id = {$preference_id}
            ";
            $result = db_query($query);
            $row = db_fetch_object($result);
            $page_content .= "<h1>Status Edit Form</h1>";
            $page_content .= "Book: <b>{$row->book}</b> <br>";
            $page_content .= "Author: <b>{$row->author}</b> <br>";
            $page_content .= drupal_get_form("tbc_external_review_status_edit_form", $preference_id);
            return $page_content;
        } else {
            $query = "
                SELECT * from textbook_companion_preference tcp INNER JOIN external_review_details erd 
                ON (tcp.id = erd.preference_id AND erd.status=1) INNER JOIN users ON (erd.uid = users.uid)
                ORDER BY users.name
            ";
            $result = db_query($query);
            $headers = array(
                "Book", "Review No.", 
                "Status", "Reviewer",
                "Action",
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}<br><em>by {$row->author}</em>",
                    suffix($row->review),
                    ($row->completed?"Completed":"Ongoing"),
                    "{$row->name}",
                    ($row->completed?l("Next", "tbc_external_review/manage_status/next/" . $row->preference_id):"---") . " | " . l("Edit", "tbc_external_review/manage_status/edit/{$row->preference_id}")
                );
                array_push($rows, $item);
            }
            $page_content .= "<h2><u>Selected Books</u></h2>";
            $page_content .= theme("table", $headers, $rows);
            
            $query = "
                SELECT * from textbook_companion_preference tcp INNER JOIN external_review_details erd 
                ON (tcp.id = erd.preference_id AND erd.status=0) INNER JOIN users ON (erd.uid = users.uid)
                ";
            $result = db_query($query);
            $headers = array(
                "Book", 
                "Author",
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}",
                    "{$row->author}",
                );
                array_push($rows, $item);
            }
            $page_content .= "<h2><u>Books Not Selected</u></h2>";
            $page_content .= theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_manage_status_recent_all() {
        $page_content = "";
        $query = "
            SELECT * FROM external_review_details erd
            LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
            LEFT JOIN users usr ON erd.uid = usr.uid
            WHERE erd.completed = 1
            ORDER BY time DESC
        ";
        $result = db_query($query);
        $headers = array(
            "Book", "Review No.",
            "Status", "Time",
            "Reviewer", 
        );
        $rows = array($query);
        while($row = db_fetch_object($result)) {
            $item = array(
                "{$row->book}<br><em>by {$row->author}</em>",
                suffix($row->review),
                ($row->status?"Completed":"Ongoing"),
                "{$row->time}",
                "{$row->name}"
            );
            array_push($rows, $item);
        }
        $page_content .= theme("table", $headers, $rows);
        return $page_content;
    }

    function tbc_external_review_manage_comments_all($preference_id=0, $review=0) {
        global $base_url;
        $page_content = "";
        if($preference_id) {
        $page_content .= "<span class='toggle-hide-show' data-preference='{$preference_id}'>Toggle Hide-Show</span>";
        $page_content .= "<div class='clearfix'></div>";
        $page_content .= "<br>";
        /* creating review links */
        $query = "
            SELECT DISTINCT(review) FROM external_review_comments
            WHERE preference_id = %d
            ORDER BY review DESC
        ";
        $result = db_query($query, $preference_id);
        $tmp = $review; // for active class in case of 0
        while($row = db_fetch_object($result)) {
            $option["attributes"]["class"] = "review-links";
            if($review == $row->review) {
                $option["attributes"]["class"] .= " active";
            } else if($tmp == 0) {
                $option["attributes"]["class"] .= " active";
                $tmp = -1;
            }
            $page_content .= l(
                suffix($row->review) . "review", 
                "tbc_external_review/manage_comments/{$preference_id}/{$row->review}",
                $option
            );
        }
        $page_content .= "<br><br><hr>";
            global $user;
            /* displaying comments of a particular book */
            if($review) {
                $query = "
                    SELECT erc.*, cha.number AS chapter, exa.number AS example FROM external_review_comments erc
                    LEFT JOIN textbook_companion_chapter cha ON cha.id = erc.chapter_id 
                    LEFT JOIN textbook_companion_example exa ON exa.id = erc.example_id
                    WHERE erc.preference_id = %d AND review = %d
                    ORDER BY erc.chapter_id, erc.example_id
                    ";
                $result = db_query($query, $preference_id, $review);
            } else {
                $query = "
                    SELECT erc.*, cha.number AS chapter, exa.number AS example FROM external_review_comments erc
                    LEFT JOIN textbook_companion_chapter cha ON cha.id = erc.chapter_id 
                    LEFT JOIN textbook_companion_example exa ON exa.id = erc.example_id
                    WHERE erc.preference_id = %d AND review = (
                        SELECT MAX(review) FROM external_review_comments WHERE preference_id = %d
                    )
                    ORDER BY erc.chapter_id, erc.example_id
                ";
                $result = db_query($query, $preference_id, $preference_id);
            }
            $headers = array(
                "Chapter", "Example",
                "Time", "Score", "Action"
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $view_options = array(
                    /* # linking in drupal l() */
                    "fragment" => " ",
                    "external" => TRUE,
                    "attributes" => array(
                        "class" => "view-comment",
                        "data-comment" => "{$row->id}",
                    )
                );
                $hide_options= array(
                    /* # linking in drupal l() */
                    "fragment" => " ",
                    "external" => TRUE,
                    "attributes" => array(
                        "class" => "hide-show",
                        "data-comment" => "{$row->id}",
                    )
                );
                
                /* scoring comments */
                $score = 0;
                $row->ncf?666:$score++;
                $row->eit?$score++:666;
                $row->axl?666:$score++;
                $row->sua?666:$score++;
                $row->sfu?666:$score++;
                $row->sum?$score++:666;
                $row->aci?666:$score++;
                $row->auu?666:$score++;
                $row->teo?$score++:666;
                $row->amt?666:$score++;
                
                $item = array(
                    "data" => array(
                        "{$row->chapter}",
                        "{$row->example}",
                        "{$row->time}",
                        "{$score}",
                        l("View", "", $view_options),
                    ),
                );
                if($row->sfu == 1 || $row->sum == 0 || $row->amt == 1) {
                    $error_class = array("class" => "error-comment");
                    $item = array_merge($item, $error_class);
                }
                if($row->hidden) {
                    $item["class"] .= " dull";
                }
                /* hide/unhide link */
                end($item["data"]);
                $key= key($item["data"]);
                if($row->hidden) {
                    $item["data"][$key] .= " | " . l("Show", "", $hide_options);
                } else {
                    $item["data"][$key] .= " | " . l("Hide", "", $hide_options);
                }
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
            $page_content .= "<div id='lightbox-wrapper'>";
            $page_content .= "<div id='lightbox-inner'></div></div> ";
        } else {
            /* displaying the list of books */
            $query = "
                SELECT DISTINCT erc.preference_id, pre.book, pre.author, usr_con.name AS contributor, usr_ext.name AS reviewer, erd.*
                FROM external_review_comments erc
                LEFT JOIN textbook_companion_preference pre ON erc.preference_id = pre.id
                LEFT JOIN textbook_companion_proposal pro ON pre.proposal_id = pro.id
                LEFT JOIN users usr_con ON usr_con.uid = pro.uid
                LEFT JOIN users usr_ext ON usr_ext.uid = erc.uid
                LEFT JOIN external_review_details erd ON erd.preference_id = erc.preference_id 
                ORDER BY erd.hidden
            ";
             $result = db_query($query);
                     
            $headers = array(
                "Book", "Review No.",
                "Status", "Contributor",
                "Reviewer", "Action"
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                  $hide_optionss= array(
                    /* # linking in drupal l() */
                    "fragment" => " ",
                    "external" => TRUE,
                    "attributes" => array(
                        "class" => "hide-show1",
                        "manages-comment" => "{$row->preference_id}",
                    )
                );
                $item = array(
                    "data" => array(
                        "{$row->book}<br><em>by {$row->author}</em>",
                        suffix($row->review),
                        ($row->completed?"Completed":"Ongoing"),
                        "{$row->contributor}",
                        "{$row->reviewer}",
                        l("View", "tbc_external_review/manage_comments/" . $row->preference_id)
                    ),
                );

                if($row->hidden) {
                 $item["class"] .= " dulls";
                } 

                end($item["data"]);
                $key = key($item["data"]);
                if(!$row->sent) {
                    $item["data"][$key] .= " | " . l("Mail", "tbc_external_review/mail_comments/" . $row->preference_id);
                } else {
                    $item["data"][$key] .= " | " . "Sent";
                }
                 
                /* hide/unhide link */
                end($item);
                $key= key($item["data"]);
                if($row->hidden) {
                    $item["data"][$key] .= " | " . l("Show", "", $hide_optionss);
                } else {
                    $item["data"][$key] .= " | " . l("Hide", "", $hide_optionss);
                }

                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
           }
        return $page_content;
    }

    function tbc_external_review_mail_comments_all($preference_id=0, $confirm="") {
        function _bool($var) {
            if($var == 1) {
                return "No";
            }
            return "Yes";
        }
        $page_content = "";
        if($confirm == "confirm") {
            $query = "
                SELECT * FROM external_review_details
                WHERE preference_id = %d
                ";
            $result = db_query($query, $preference_id);
            $row = db_fetch_object($result);
            $current_review = $row->review;
            $review_dir = $_SERVER['DOCUMENT_ROOT'] . base_path() . "reviews/{$row->preference_id}/";
            if(!file_exists($review_dir)) {
                mkdir($review_dir, 0755, TRUE);
            }
            $review_file = $review_dir . "{$row->preference_id}-review-{$row->review}" . ".csv";
            $fp = fopen($review_file, "w");
            /* making the first row */
            $item = array(
                "Chapter No.", "Example No.",
                "Naming convention followed?", "Error in textbook mentioned as comment?",
                "Problem statement included in code?", "Axes labeled?", "Symbols used are appropiate?",
                "Scilab functions used?", "Solved using Matlab in textbook?",
                "Appropriate comments included?", "Appropriate units used as given in the textbook?",
                "Typographical errors in output and/or comments?", "Answers matching with the textbook?", "Other Comments"
            );
            fputcsv($fp, $item);
            $query = "
                SELECT erc.*, cha.number AS chapter, exa.number AS example FROM external_review_comments erc
                LEFT JOIN textbook_companion_chapter cha ON erc.chapter_id = cha.id
                LEFT JOIN textbook_companion_example exa ON erc.example_id = exa.id
                WHERE (erc.hidden = 0 AND erc.preference_id = %d AND erc.review = %d)
                ORDER BY chapter, example
                ";
            $result = db_query($query, $preference_id, $current_review);
            while($row = db_fetch_object($result)) {
                $item = array(
                    $row->chapter, $row->example,
                    _bool($row->ncf), _bool($row->eit),
                    _bool($row->psi), _bool($row->axl),
                    _bool($row->sua), _bool($row->sfu),
                    _bool($row->sum), _bool($row->aci),
                    _bool($row->auu), _bool($row->teo),
                    _bool($row->amt), "{$row->comment}"
                );
                fputcsv($fp, $item);
            }
            fclose($fp);
            
            /* preparing and sending mail */
            $query = "
                SELECT * FROM external_review_details erd
                LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
                LEFT JOIN textbook_companion_proposal pro ON pre.proposal_id = pro.id
                LEFT JOIN users usr ON pro.uid = usr.uid
                WHERE erd.preference_id = %d
            ";
            $result = db_query($query, $preference_id);
            $row = db_fetch_object($result);
            $review_no = suffix($row->review);
            $from = "textbook@scilab.in";
            $to = "{$row->mail}";
            $cc = "textbook@scilab.in";
            $bcc = "mukulrkulkarni@gmail.com, lavitha89@gmail.com, rush2jrp@gmail.com";
            $subject = "Scilab Textbook Companion - {$review_no} Review";
            $message = "
                Dear {$row->name}<br><br>
                Please find the attached file containing the list of errors found in your uploaded book: <br>
                Book: <b>{$row->book}</b> <br>
                Author: <b>{$row->author}</b> <br>
                Review Number: <b>{$review_no}</b> <br><br>
                Kindly rectify the errors and re-upload the codes on the website.<br>
                <b>Reply to this mail once the corrections are done.</b> <br><br>
                <i>Note: The attachment is a comma separated file, which can be viewed using any office software.</i> <br><br>
                Regards,<br>
                Scilab.in
            ";
            $file = $review_file;
            $mail_status = send_mail_attachment($from, $to, $cc, $bcc, $subject, $message, $file);
            if(!mail_status) {
                drupal_set_message("An error occurred while sending mail.", "error");
                drupal_goto("tbc_external_review/manage_comments");
            } else {
                /* toggling  sent column in database */
                $query = "
                    UPDATE external_review_details
                    SET sent = 1
                    WHERE preference_id = %d
                ";
                $result = db_query($query, $preference_id);
                drupal_set_message("Mail sent successfully.", "status");
                drupal_goto("tbc_external_review/manage_comments");
            }
        } else if($preference_id) {
            $query = "
                SELECT * FROM external_review_details erd
                LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
                LEFT JOIN textbook_companion_proposal pro ON pre.proposal_id = pro.id
                LEFT JOIN users usr ON pro.uid = usr.uid
                WHERE erd.preference_id = %d
            ";
            $result = db_query($query, $preference_id);
            $row = db_fetch_object($result);
            $page_content .= "<h4>Are you sure you want to send mail?</h4><br>";
            $page_content .= "Book: <b>{$row->book}</b><br>";
            $page_content .= "Author: <b>{$row->author}</b><br>";
            $page_content .= "Contributor: <b>{$row->name}</b><br>";
            $page_content .= "Email: <b>{$row->mail}</b><br><br>";
            $page_content .= l("Yes", "tbc_external_review/mail_comments/{$preference_id}/confirm") . " | ";
            $page_content .= l("Cancel", "tbc_external_review/manage_comments");
        }
        return $page_content;
        }

    function tbc_external_review_add_book_form($form_state, $preference_id) {
        $form = array();
        $form["wrapper"] = array(
            "#prefix" => "<div id='add-book-form'>",
            "#suffix" => "</div>"
        );
        $form["wrapper"]["review"] = array(
            "#type" => "select",
            "#title" => "Review Number",
            "#options" => range(0,10),
            "#default_value" => 1
        );
        $form["wrapper"]["preference_id"] = array(
            "#type" => "hidden",
            "#value" => $preference_id
        );
        $form["wrapper"]["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit"
        );
        $form["wrapper"]["cancel"] = array(
            "#type" => "button",
            "#value" => "Cancel",
        );
        return $form;
    }

    function tbc_external_review_add_book_form_submit($form, &$form_state) {
        $preference_id = $form_state["values"]["preference_id"];
        $review = $form_state["values"]["review"];
        
        $query = "
            INSERT INTO {external_review_details}
            (preference_id, review)
            VALUES
            (%d, %d)
        ";
        $result = db_query($query, $preference_id, $review);
        
        if(!$result) {
            drupal_set_message("An error occured while adding the book for review.", "error");
        } 
        $query = "
            UPDATE textbook_companion_proposal
            SET proposal_status = 4
            WHERE id IN
            (SELECT proposal_id from textbook_companion_preference WHERE id = %d)
        ";
        $result = db_query($query, $preference_id);
        if(!$result) {
            drupal_set_message("Book status update failed.", "error");
        } else {
            drupal_set_message("Book added successfully.", "status");
        }
        drupal_goto("manage_proposal/all");
    }

    function tbc_external_review_add_book_all($preference_id=0) {
        if ($preference_id) {
            $page_content = "";
            $query = "
                SELECT * FROM {textbook_companion_preference} WHERE id = %d
            ";
            $result = db_query($query, $preference_id);
            $row = db_fetch_object($result);
            $page_content .= "<strong>Do you want to continue adding the book?</strong><br>";
            $page_content .= "Book: {$row->book} <br>";
            $page_content .= "Author: {$row->author} <br>";
            $page_content .= drupal_get_form("tbc_external_review_add_book_form", $preference_id);
            return $page_content;
        }
        drupal_goto("manage_proposal/all");
    }

    function tbc_external_review_completion_form($form, $preference_id=10) {
        function _missed_chapter() {
            $chapter = array();
            $chapter[0] = "Select Chapter";
            $chapter = array_merge($chapter, range(1,50));
            return $chapter;
        }
        function _missed_example() {
            $example = array();
            $example[0] = "Select Example";
            $example = array_merge($example, range(1,80));
            return $example;
        }
        $form = array();
        $form["#action"] = url("tbc_external_review/status/complete/{$preference_id}/yes");
        $form["wrapper"] = array(
            "#prefix" => "<div  id='review-completion-form'>",
            "#suffix" => "</div>"
        );
        $form["wrapper"]["missing"] = array(
            "#title" => "Are all examples from the book attempted by the contributor?",
            "#type" => "radios",
            "#options" => array(
                t("Yes"),
                t("No")
            ),
        );
        $form["wrapper"]["list"] = array(
            "#prefix" => "<div id='missed-list'>",
            "#suffix" => "</div>",
        );
        $form["wrapper"]["list"]["missed_chapter"] = array(
            "#title" => "Chapter",
            "#type" => "select",
            "#options" => _missed_chapter()
        );
        $form["wrapper"]["list"]["missed_example"] = array(
            "#title" => "Example",
            "#type" => "select",
            "#options" => _missed_example(),
        );
        $form["wrapper"]["more"] = array(
            "#type" => "markup",
            "#value" => "<a id='more' href='#'>Add More</a><br><br><hr>",
        );
        $form["wrapper"]["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit"
        );
        $form["wrapper"]["cancel"] = array(
            "#type" => "button",
            "#value" => "Cancel",
        );
        return $form;
    }

    function tbc_external_review_status_all($action="", $preference_id=0, $confirm="") {
        global $user;
        $page_content = "";
        if($action == "complete" && $preference_id) {
            if($confirm == "yes"){
                $query = "
                    UPDATE external_review_details
                    SET completed = 1
                    WHERE preference_id = %d
                ";
                $result = db_query($query, $preference_id);
                
                $query = "
                    INSERT INTO external_review_missed
                    (preference_id, uid, chapter, example)
                    VALUES
                    ({$preference_id}, {$user->uid}, {$_REQUEST['missed_chapter']}, {$_REQUEST['missed_example']})
                ";
                
                if ($_REQUEST["missing"]) {
                    $i = 0;
                    foreach($_REQUEST["missed_chapters"] as $chapter){
                        $query .= ", ({$preference_id}, {$user->uid}, {$chapter}, {$_REQUEST['missed_examples'][$i]})";
                        $i++;
                    }
                    $result = db_query($query, $preference_id);
                }
                
                if(!$result) {
                    drupal_set_message("Updation failed.", "error");
                } else {
                    /* fetching the book and review details */
                    $query = "
                        SELECT * FROM external_review_details erd
                        LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
                        WHERE erd.preference_id = %d
                    ";
                    $result = db_query($query, $preference_id);
                    $row = db_fetch_object($result);
                    
                    $review_no = suffix($row->review);
                    /* sending mail */
                    $to = "{$user->mail}";
                    $subject = "External Review - Textbook Companion Project";
                    $body = "
                        Dear {$user->name} <br><br>
                        You have completed a review. <br><br>
                        Book: <b>{$row->book}</b> <br>
                        Author: <b>{$row->author}</b><br>
                        Review: <b>{$review_no}</b> <br><br>
                        We will review your comments soon.<br>
                        Thank you for your contribution. <br><br>
                        Best Wishes, <br>
                        Scilab.in
                    ";
                    $message = array(
                        "to" => $to,
                        "subject" => $subject,
                        "body" => $body,
                        "headers" => array(
                            "From" => "textbook@scilab.in",
                            "Bcc" => "mukulrkulkarni@gmail.com, lavitha89@gmail.com, rush2jrp@gmail.com",
                            "Content-Type" => "text/html; charset=UTF-8; format=flowed"
                        )
                    );
                    drupal_mail_send($message);
                    drupal_set_message("Book marked as completed successfully.");
                }
                drupal_goto("tbc_external_review/status");
            } else {
                $query = "
                    SELECT erd.review, pre.id, pre.book, pre.author FROM external_review_details erd
                    LEFT JOIN textbook_companion_preference pre ON erd.preference_id = pre.id
                    WHERE erd.preference_id = %d
                ";
                $result = db_query($query, $preference_id);
                $row = db_fetch_object($result);
                
                $page_content .= "<strong>Are you sure you want to mark the book completed?</strong><br>";
                $page_content .= "Book: {$row->book} <br>";
                $page_content .= "Author: {$row->author} <br>";
                $page_content .= drupal_get_form("tbc_external_review_completion_form", $preference_id);
            }
        } else {
            $query = "
                SELECT erd.*, pre.* FROM external_review_details erd
                LEFT JOIN textbook_companion_preference pre ON pre.id = erd.preference_id
                WHERE erd.uid = %d
                ORDER BY pre.book
            ";
            $result = db_query($query, $user->uid);
            
            $headers = array(
                "Book", "Review No.",
                "Action"
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}<br>by <em>{$row->author}</em>",
                    suffix($row->review),
                );
                if($row->completed){
                    array_push($item, "Completed");
                } else {
                    array_push(
                        $item, l("Mark Completed", "tbc_external_review/status/complete/".$row->preference_id)
                    );
                }
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_eligibility_test_form($form, $question_id=0) {
        $form = array();
        global $user;
        
        $query = "
            SELECT * FROM external_review_eligibility_answers
            WHERE uid = {$user->uid} AND question_id = {$question_id}
        ";
        $result = db_query($query);
        $row = db_fetch_object($result);
        
        $form["wrapper"] = array(
            "#type" => "fieldset",
            "#title" => t("Eligibility Test"),
            "#prefix" => "<div id='eligibility-form-wrapper'>",
            "#suffix" => "</div>",
        );
        $form["wrapper"]["ncf"] = array(
            "#type" => "radios",
            "#title" => t("Naming convention followed? (Checklist point 1, 2 and 4)"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->ncf
        );
        $form["wrapper"]["eit"] = array(
            "#type" => "radios",
            "#title" => t("Error in textbook mentioned as comment?"),
            "#options" => array(
                t("Yes"),
                t("No"),
                t("Not required and answer matches with the textbook"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->eit
        );
        $form["wrapper"]["sua"] = array(
            "#type" => "radios",
            "#title" => t("Symbols used are appropiate?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->sua
        );
        $form["wrapper"]["sfu"] = array(
            "#type" => "radios",
            "#title" => t("Scilab functions used?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->sfu
        );
        $form["wrapper"]["aci"] = array(
            "#type" => "radios",
            "#title" => t("Appropriate comments included as mentioned in checklist point 7?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->aci
        );
        $form["wrapper"]["auu"] = array(
            "#type" => "radios",
            "#title" => t("Appropriate units used as given in the textbook?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->auu
        );
        $form["wrapper"]["teo"] = array(
            "#type" => "radios",
            "#title" => t("Typographical errors in output and/or comments?"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->teo
        );
        $form["wrapper"]["mit"] = array(
            "#type" => "radios",
            "#title" => t("The code can be approved if the same example is solved using Matlab in the textbook."),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->mit
        );
        $form["wrapper"]["agl"] = array(
            "#type" => "radios",
            "#title" => t("Axes of graph need not be labeled"),
            "#options" => array(
                t("Yes"),
                t("No"),
            ),
            "#required" => TRUE,
            "#default_value" => $row->agl
        );
        $form["wrapper"]["question_id"] = array(
            "#type" => "hidden",
            "#value" => $question_id
        );
        $form["wrapper"]["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit and Continue."
        );
        return $form;
    }

    function tbc_external_review_eligibility_test_form_submit($form, &$form_state) {
        global $user;
        
        $v = $form_state["values"];
        
        /* checking the timer and status of current user */
        $query = "
            SELECT * FROM external_review_eligibility_timings
            WHERE uid = {$user->uid}
        ";
        $result = db_query($query);
        $num_rows = mysql_num_rows($result); //either 1 or 0
        if($num_rows){
            /* now checking whether the test is still open */
            $row = db_fetch_object($result);
            $start_date = new DateTime($row->start_date);
            $current_date = new DateTime();
            $since_start = $start_date->diff($current_date);
            $minutes_remaining = 24 - $since_start->i;
            $seconds_remaining = 60 - $since_start->s;
            if($minutes_remaining >= 0) {
            } else {
                drupal_goto("tbc_external_review/eligibility_test/end");
            }
        }
        
        $query = "
            SELECT id FROM external_review_eligibility_answers
            WHERE 
            uid = {$user->uid} AND question_id = {$v['question_id']}
        ";
        $result = db_query($query);
        $num_rows = mysql_num_rows($result);
        if($num_rows) {
            $query = "
                UPDATE external_review_eligibility_answers
                SET
                ncf = {$v['ncf']}, eit = {$v['eit']}, sua = {$v['sua']},
                sfu = {$v['sfu']}, aci = {$v['aci']}, auu = {$v['auu']},
                teo = {$v['teo']}, mit = {$v['mit']}, agl = {$v['agl']}
                WHERE
                uid = {$user->uid} AND question_id = {$v['question_id']}
                ";
            $result = db_query($query);
        } else {
            $query = "
                INSERT INTO external_review_eligibility_answers
                (
                    uid, question_id, 
                    ncf, eit, sua,
                    sfu, aci, auu,
                    teo, mit, agl
                )
                VALUES
                (
                    {$user->uid}, {$v['question_id']},
                    {$v['ncf']}, {$v['eit']}, {$v['sua']},
                    {$v['sfu']}, {$v['aci']}, {$v['auu']},
                    {$v['teo']}, {$v['mit']}, {$v['agl']}
                )
            ";
            $result = db_query($query);
        }
        $question_id = $v["question_id"];
        if($question_id == 5) {
            // end the test
            drupal_goto("tbc_external_review/eligibility_test/end");
        } else {
            drupal_goto("tbc_external_review/eligibility_test/question/".($question_id+1));
        }
    }

    function tbc_external_review_eligibility_test_all($action="", $question_id=0) {
        global $user;
        $page_content = "";
        $minutes_remaining = 25;
        $seconds_remaining = 0;
        
        /* checking if a valid question */
        if ($question_id > 5 || $question_id < 0) {
            drupal_set_message("Wrong question number.", "error");
            drupal_goto("tbc_external_review/eligibility_test/question/1");
        }
        
        if($action == "question" && $question_id) {
            /* checking the timer and status of current user */
            $query = "
                SELECT * FROM external_review_eligibility_timings
                WHERE uid = {$user->uid}
            ";
            $result = db_query($query);
            $num_rows = mysql_num_rows($result); //either 1 or 0
            if($num_rows){
                /* now checking whether the test is still open */
                $row = db_fetch_object($result);
                $start_date = new DateTime($row->start_date);
                $current_date = new DateTime();
                $since_start = $start_date->diff($current_date);
                $minutes_remaining = 24 - $since_start->i;
                $seconds_remaining = 60 - $since_start->s;
                if($minutes_remaining >= 0) {
                } else {
                    drupal_goto("tbc_external_review/eligibility_test/end");
                }
            } else {
                /* inserting a new entry into timings table */
                $current_date = new DateTime();
                $current_date = $current_date->format('Y-m-d H:i:s');
                $query = "
                    INSERT INTO external_review_eligibility_timings
                    (uid, status, start_date)
                    VALUES
                    ({$user->uid}, 1, '{$current_date}')
                ";
                $result = db_query($query);
            }
            
            /* getting the questions */
            $query = "
                SELECT * FROM external_review_eligibility_questions
                WHERE id = {$question_id}
            ";
            $result = db_query($query);
            $row = db_fetch_object($result);
            $page_content .= "<div id='timer'>";
                $page_content .= "<span id='minutes'></span>m: ";
                $page_content .= "<span id='seconds'></span>s";
            $page_content .= "</div>";
            $page_content .= "<h4>";
                if ($question_id != 1) {
                    $page_content .= l("<Prev - ", "tbc_external_review/eligibility_test/question/" . ($question_id-1));
                }
                $page_content .= "Question {$question_id} of 5";
                if ($question_id != 5) {
                    $page_content .= l(" - Next>", "tbc_external_review/eligibility_test/question/" . ($question_id+1));
                }
            $page_content .= "</h4><br>";
            $page_content .= "<div id='example-name'>Example file name: <strong>{$row->example_name}</strong></div><br>";
            $page_content .= "<div class='well'><pre>{$row->code}</pre></div>";
            $page_content .= drupal_get_form("tbc_external_review_eligibility_test_form", $question_id);
            
            /* hidden fields with minutes_remaining and seconds_remaining */
            $page_content .= "<input id='minutes_remaining' value='{$minutes_remaining}' type='hidden'>";
            $page_content .= "<input id='seconds_remaining' value='{$seconds_remaining}' type='hidden'>";
        } else if($action == "end") {
            $query = "
                UPDATE external_review_eligibility_timings
                SET status = 2
                WHERE uid = {$user->uid}
            ";
            $result = db_query($query);
            drupal_set_message("Eligibility Test completed successfully. We will get back to you soon.", "status");
        } else {
            /* checking if the user has filled the profile form */
            $query = "
                SELECT uid FROM external_review_profile
                WHERE uid = %d
            ";
            $result = db_query($query, $user->uid);
            $num_rows = mysql_num_rows($result);
            if(!$num_rows) {
                drupal_set_message("Please fill  the profile form before you can attend the test.", "error");
                drupal_goto("tbc_external_review/profile");
            }
            
            /* showing the test details before starting the test */
            $page_content .= "<p>You are about to give a test based on which you will be selected as a reviewer for the Scilab Textbook Companions.</p>";
            $page_content .= "<ul>";
                $page_content .= "<li>The test consists of 5 code review questions that are mandatory.</li>";
                $page_content .= "<li>Test duration is 25 minutes, after which the test will automatically close.</li>";
            $page_content .= "</ul>";
            $page_content .= "<u><b>" . l("Click here", "tbc_external_review/eligibility_test/question/1") . "</b></u>";
            $page_content .= " to start the test.";
            // $page_content .= "<strong>The test has been suspended for today due to technical reasons.</strong><br>";
            // $page_content .= "<strong>Please visit this page tomorrow.</strong>";
        }
        return $page_content;
    }

    function tbc_external_review_manage_tests_all($action="", $user_id=0){
        /* 
            end the tests that did not close properly 
            i.e set status=2 before loading this page in the future\
            if the time has expired.
        */
        $page_content = "";
        if($action == "view" && $user_id) {
            /* fetching the correct answers*/
            $query = "
                SELECT * FROM external_review_eligibility_timings
                WHERE uid = %d
            ";
            $result = db_query($query, $user_id);
            if(mysql_num_rows($result)) {
                /* fetching correct answers */
                $query = "
                    SELECT id, ncf, eit, sua, sfu, aci, auu, teo, mit, agl
                    FROM external_review_eligibility_questions
                ";
                $result = db_query($query);
                $correct_answers = array();
                while($row = db_fetch_object($result)) {
                    $correct_answers[$row->id] = $row;
                }
                
                /* fetching user answers */
                $query = "
                    SELECT * FROM external_review_eligibility_answers
                    WHERE uid = %d
                ";
                $result = db_query($query, $user_id);
                $user_answers = array();
                while($row = db_fetch_object($result)) {
                    $user_answers[$row->question_id] = $row;
                }
                
                /* comparing the results */
                $scores = array();
                foreach($user_answers as $user_answer) {
                    $scores [$user_answer->question_id] = 0;
                    ($user_answer->ncf == $correct_answers[$user_answer->question_id]->ncf)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->eit == $correct_answers[$user_answer->question_id]->eit)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->sua == $correct_answers[$user_answer->question_id]->sua)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->sfu == $correct_answers[$user_answer->question_id]->sfu)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->aci == $correct_answers[$user_answer->question_id]->aci)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->auu == $correct_answers[$user_answer->question_id]->auu)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->teo == $correct_answers[$user_answer->question_id]->teo)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->mit == $correct_answers[$user_answer->question_id]->mit)?$scores[$user_answer->question_id]++:Null;
                    ($user_answer->agl == $correct_answers[$user_answer->question_id]->agl)?$scores[$user_answer->question_id]++:Null;
                }
                $page_content .= "<hr><h1><u>User Score</u></h1>";
                $total_score = 0;
                foreach($scores as $key => $value) {
                    $page_content .= "Question {$key} score = <strong>{$value}</strong> out of 9<br>";
                    $total_score += $value;
                }
                $page_content .= "<h1>Total Score = {$total_score} out of 45";
                /* fetching user data for display */
                $query = "
                    SELECT * FROM external_review_profile erd
                    LEFT JOIN users usr ON usr.uid = erd.uid
                    WHERE erd.uid = %d
                ";
                $result = db_query($query, $user_id);
                $row = db_fetch_array($result);
                $fields = array(
                    "first_name", "last_name", "designation", "university",
                    "phone", "email", "alt_email", "address",
                    "description"
                );
                $page_content .= "<hr><h1><u>User Profile</u></h1>";
                $page_content .= "<table border=1 cellspacing=1 cellpadding=1>";
                foreach($fields as $field) {
                    $page_content .= "<tr>";
                        $page_content .= "<td>{$field}</td>";
                        $page_content .= "<td>{$row[$field]}</td>";
                    $page_content .= "</tr>";
                }
                $page_content .= "</table>";
            }
        } else {
            $query = "
                SELECT * FROM external_review_eligibility_timings tim
                LEFT JOIN users usr ON usr.uid = tim.uid
                WHERE tim.status = 2
                ";
            $result = db_query($query);
            
            $headers = array(
                "User", "Date Attended",
                "Action"
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    $row->name,
                    $row->start_date,
                    l("View", "tbc_external_review/manage_tests/view/{$row->uid}")
                );
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_er_assign_book_form(){
            $query = "  
                 SELECT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid, erd.completed FROM textbook_companion_preference pre
                 LEFT JOIN external_review_details erd ON erd.preference_id = pre.id
                 LEFT JOIN users usr ON usr.uid = erd.uid
                 WHERE erd.status = 0
                 ORDER BY pre.book ASC
            ";
         $result = db_query($query);
         $books = array();
         $books[0] = "Please select a book";
         while($row1 = db_fetch_object($result)) {
            $books[$row1->id] = "{$row1->book} (Author: {$row1->author}, Edition: {$row1->edition})";           
         }
                    
            $querys = "  
                 SELECT * FROM users_roles rol
                 LEFT JOIN users usr ON usr.uid = rol.uid
                 LEFT JOIN external_review_profile erp ON erp.uid = usr.uid
                 WHERE rol.rid = 9
                 ORDER BY erp.first_name ASC
            ";
         $results = db_query($querys);         
         $reviewers = array();
         $reviewers[0] = "Please select a reviewer";
         while($row = db_fetch_object($results)) {                
             $reviewers[$row->uid] = "{$row->first_name} {$row->last_name}";
         }
        $form = array();
        $form["reviewers"] = array(
            "#type" => "select",
            "#title" => "",
            "#description" => t("Please select a reviewer."),
            "#options" => $reviewers
        );  
        $form["book"] = array(
            "#type" => "select",
            "#title" => "",
            "#description" => t("Please select a book."),
            "#options" => $books
        );        
        $form["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit",
        );
        return $form;
    }

    function tbc_external_review_er_assign_book_form_validate($form, &$form_state) {
        if(!$form_state["values"]["reviewers"]) {
            form_set_error("reviewers", "Please select a reviewer.");
        }
        if(!$form_state["values"]["book"]) {
            form_set_error("book", "Please select a book.");
        }
    }

    function tbc_external_review_er_assign_book_form_submit($form, &$form_state){
    
        $v = $form_state["values"];
            $query = "
                UPDATE external_review_details
                SET status = 1 , uid = %d
                WHERE preference_id = %d
            ";
            $result = db_query($query, 
               $v["reviewers"], $v["book"]
            );        
            drupal_set_message("Book alloted successfully", "status");
            
    }

    function tbc_external_review_er_assign_book_all() {
        $page_content .= "";
        $page_content .= "";
        $page_content .= "";
        $page_content .= "";
        $page_content .= drupal_get_form("tbc_external_review_er_assign_book_form");
        return $page_content;
    }

    function tbc_external_review_er_remove_book_form(){
            $query = "  
                SELECT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid FROM textbook_companion_preference pre
                LEFT JOIN external_review_details erd ON erd.preference_id = pre.id
                LEFT JOIN users usr ON usr.uid = erd.uid
                WHERE erd.status = 1  
                ORDER BY pre.book ASC 
            ";
        $result = db_query($query);
        $books = array();
        $books[0] = "Please select a book";    
        while($row = db_fetch_object($result)) {
           $books[$row->id] = "{$row->book} (Author: {$row->author}, Edition: {$row->edition})";        
        }

        $form["book"] = array(
            "#type" => "select",
            "#title" => "",
            "#description" => t("Please select a book."),
            "#options" => $books
        );        
        $form["submit"] = array(
            "#type" => "submit",
            "#value" => "Submit",
        );
        return $form;
    }

    function tbc_external_review_er_remove_book_form_validate($form, &$form_state) {
        if(!$form_state["values"]["book"]) {
            form_set_error("book", "Please select a book.");
        }
    }

    function tbc_external_review_er_remove_book_form_submit($form, &$form_state){    
        $v = $form_state["values"];
            $query = "
                UPDATE external_review_details
                SET status = 0 , uid = 0 
                WHERE preference_id = %d
            ";
            $result = db_query($query, 
                $v["book"]
            );        
            drupal_set_message("Book removed successfully", "status");
            
    }

    function tbc_external_review_er_remove_book_all() {
        $page_content .= "";        
        $page_content .= drupal_get_form("tbc_external_review_er_remove_book_form");
        return $page_content;
    }
    
    function _er_reminder_all($id=0, $confirm = "") {

        function _er_reminder_mail($id){
            $query = "
                SELECT pre.id AS id, pre.book, pre.author, pre.edition, erd.failed_reminder, 
                    erd.time, usr.uid, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                    LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                    LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                    LEFT JOIN users usr ON usr.uid = erd.uid
                    LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                    WHERE erd.status = 1 AND completed = 0 AND rol.rid = 9 AND id = {$id}
                ";            
                    $result = db_query($query);
                    $row = db_fetch_object($result);
                    $start_date = new DateTime($row->time);
                    $current_date = new DateTime();
                    $since_start = $start_date->diff($current_date);
                    $diff = ($since_start->format('%a'));                   
                    if($diff % 15 == 0){

                        $query = "
                            UPDATE external_review_details
                            SET failed_reminder = failed_reminder + 1
                            WHERE preference_id = {$id}
                        ";

                            /* sending mail */
                            $to = $row->mail;
                            $subject = "Reminder to complete review";
                            $body = "
                            <p>
                              Dear Reviewer,<br><br>
                              This is to remind you that it has been 15 days since the book was allotted to you. You still have 15 days left from the provided 30 days to submit your review comments. 
                              Please submit your review comments in the Scilab interface. If you fail to submit the comments on time, the textbook will be removed from your account.<br>
                              Please note that extension of deadline is not possible on any account.<br><br>
                              Regards,<br>
                              Scilab Team
                            </p>
                            ";
                        $message = array(
                            "to" => $to,
                            "subject" => $subject,
                            "body" => $body,
                            "headers" => array(
                            "From" => "textbook@scilab.in",
                            "Bcc" => "textbook@scilab.in, lavitha89@gmail.com, bhavani.jalkrish@gmail.com, rush2jrp@gmail.com, prashantsinalkar@gmail.com",
                            "Content-Type" => "text/html; charset=UTF-8; format=flowed"
                            )
                        );
                        drupal_mail_send($message);
                        db_query($query);
                    } else {
                            }
        }

        $page_content = ""; 
            $query = "
                SELECT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid, erd.time, erd.failed_reminder, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                    LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                    LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                    LEFT JOIN users usr ON usr.uid = erd.uid
                    LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                    WHERE erd.status = 1 AND completed = 0 AND rol.rid = 9
                    ";
            $result = db_query($query);            
            $headers = array(
             "Book", "Author", "Reviewer Name",
             "Remainders"
            );
            $rows = array();  
            while ($row = db_fetch_object($result)) {
              $item  =array(        
                $row->book,
                $row->author,
                $row->first_name. " ". $row->last_name,  
                $row->failed_reminder,
                _er_reminder_mail($row->id),               
            );
            array_push($rows, $item);
            }
        $page_content .= theme("table", $headers, $rows);  
        
        return $page_content;
    }

    function _er_tbc_reviewer_reminder_all($id=0, $confirm="") {

        function _tbc_er_reminder_mail($id){

            $query = "
                SELECT DISTINCT pre.id, erc.review AS id, pre.book, pre.author, pre.edition, usr.uid, erd.time, erd.failed_reminder,
                erd.failed_review, erc.review, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                LEFT JOIN users usr ON usr.uid = erd.uid
                LEFT JOIN external_review_comments erc ON erc.uid = erd.uid
                LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                WHERE erd.status = 1 AND rol.rid = 9 AND erc.review = 0 AND  pre.id = {$id}
                ";
                $result = db_query($query);
                $row = db_fetch_object($result);
                $start_date = new DateTime($row->time);
                $current_date = new DateTime();
                $since_start = $start_date->diff($current_date);
                $diff = ($since_start->format('%a'));
                if($diff % 15 == 0){
                  /* increment failed to review */
                    $query = "
                      UPDATE external_review_details
                      SET failed_review = failed_review + 1
                      WHERE preference_id = {$id}
                    ";
                    /* sending mail */
                    $to = $row->mail;
                    $subject = "External reviewers book delay";
                    $body = "
                    <p>
                      Dear TBC Reviewers,<br><br>
                      This is to inform you that <b>{$row->first_name} {$row->last_name}</b> has failed to review the Book: <b>{$row->book}</b>, Author: <b>{$row->author}</b>, 
                      Edition: <b>{$row->edition}</b> on time.<br><br><br>
                      Regards,<br>
                      Scilab Team
                    </p>
                    ";
                    $message = array(
                      "to" => "lavitha89@gmail.com, bhavani.jalkrish@gmail.com ",
                      "subject" => $subject,
                      "body" => $body,
                      "headers" => array(
                      "From" => "textbook@scilab.in",
                      "Bcc" => "rush2jrp@gmail.com, prashantsinalkar@gmail.com",
                      "Content-Type" => "text/html; charset=UTF-8; format=flowed"
                      )
                    );
                    drupal_mail_send($message);
                    db_query($query);
                } else{
                    }
        }

        $page_content = "";
            $query = "
                SELECT DISTINCT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid, erd.failed_reminder,
                        erd.failed_review, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                        LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                        LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                        LEFT JOIN users usr ON usr.uid = erd.uid
                        LEFT JOIN external_review_comments erc ON erc.uid = erd.uid
                        LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                        WHERE erd.status = 1 AND rol.rid = 9 AND erc.review = 0
                        ";
                $result = db_query($query);                
                $headers = array(
                 "Book", "Author", "Reviewer Name", "Remainders"
                );
                $rows = array();  
                while ($row = db_fetch_object($result)) {
                $item  = array(        
                    $row->book,
                    $row->author,
                    $row->first_name ." ". $row->last_name,      
                    $row->failed_review,
                    _tbc_er_reminder_mail($row->id),                    
                );
            array_push($rows, $item);
                }
        $page_content .= theme("table", $headers, $rows);      

        return $page_content;
    }

    function _er_reminder_page_all($id=0) {

        $page_content = "";
                $query = "
                    SELECT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid, erd.time, erd.failed_reminder, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                        LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                        LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                        LEFT JOIN users usr ON usr.uid = erd.uid
                        LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                        WHERE erd.status = 1 AND completed = 0 AND rol.rid = 9
                        ";
               
                $result = db_query($query);
                $headers = array(
                "Book", "Author", "Reviewer Name",
                "Remainders"
                );
                $rows = array();  
            while ($row = db_fetch_object($result)) {
                $item  =array(        
                    $row->book,
                    $row->author,
                    $row->first_name. " ". $row->last_name,  
                    $row->failed_reminder 
                );
            array_push($rows, $item);
            }
        $page_content .= theme("table", $headers, $rows);
        return $page_content;
    }
    

    function _tbc_reviewer_reminder_page_all($id=0, $confirm="") {

        $page_content = "";
                $query = "
                    SELECT DISTINCT pre.id AS id, pre.book, pre.author, pre.edition, usr.uid, erd.failed_reminder,
                        erd.failed_review, erp.first_name, erp.last_name, usr.mail FROM textbook_companion_preference pre
                        LEFT JOIN external_review_details erd ON erd.preference_id = pre.id          
                        LEFT JOIN users_roles rol ON erd.uid = rol.uid            
                        LEFT JOIN users usr ON usr.uid = erd.uid
                        LEFT JOIN external_review_comments erc ON erc.uid = erd.uid
                        LEFT JOIN external_review_profile erp ON erp.uid = erd.uid
                        WHERE erd.status = 1 AND rol.rid = 9 AND erc.review = 0
                        ";                    
                $result = db_query($query);                
                $headers = array(
                 "Book", "Author", "Reviewer Name", "Remainders"
                );
                $rows = array();  
            while ($row = db_fetch_object($result)) {
                $item  = array(        
                    $row->book,
                    $row->author,
                    $row->first_name ." ". $row->last_name,
                    $row->failed_review,
                );
            array_push($rows, $item);
            }
        $page_content .= theme("table", $headers, $rows);      
      
        return $page_content;
    }

    function tbc_external_review_init() {
        drupal_add_css(drupal_get_path("module", "tbc_external_review") . "/css/tbc_external_review.css");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/jquery-1.11.0.min.js");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/jquery-noconfilct.js");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/jquery.lightbox_me.js");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/tbc_external_review.js");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/timer.js");
    }