<?php 
    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/reviewer_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" => "New 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/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/view_status"] = array(
            "title" => "ER View Status",
            "page callback" => "tbc_external_review_view_status_all",
            "access arguments" => array("administer tbc_external_review"),
            "weight" => 30,
            "type" => MENU_NORMAL_ITEM
        );
        $items["tbc_external_review/view_status/all"] = array(
            "title" => "All",
            "page callback" => "tbc_external_review_view_status_all",
            "access arguments" => array("administer tbc_external_review"),
            "weight" => 1,
            "type" => MENU_DEFAULT_LOCAL_TASK,
        );
        $items["tbc_external_review/view_status/recent"] = array(
            "title" => "Recently Completed",
            "page callback" => "tbc_external_review_view_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/test"] = array(
            "title" => "ER Add Book",
            "page callback" => "tbc_external_review_test_all",
            "access arguments" => array("download tbc_external_review"),
            "type" => MENU_CALLBACK
        );
        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;
    }

    /*
        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');
    }

    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}";
        $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) {
        global $user;
        $page_content = "";
        if($preference_id) {
            $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
                ORDER BY erc.time DESC
            ";
            $result = db_query($query, $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_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)
            values
            (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '%s')
        ";
        $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"]
        );
        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>Problem statement included in code?</td><td>" . _bool($row->eit) . "</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>";
        }
        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_view_status_all() {
       $page_content = "";
        $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",
        );
        $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}",
            );
            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_view_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) {
        global $base_url;
        $page_content = "";
        if($preference_id) {
            global $user;
            /* displaying comments of a particular book */
            $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
                ORDER BY erc.time DESC
            ";
            $result = db_query($query, $preference_id);
            $headers = array(
                "Chapter", "Example",
                "Time", "Score", "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}",
                    )
                );
                
                /* 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", "", $options),
                    ),
                );
                if($row->sfu == 1 || $row->sum == 0 || $row->amt == 1) {
                    $error_class = array("class" => "error-comment");
                    $item = array_merge($item, $error_class);
                }
                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
                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
                ORDER BY pre.book
            ";
            $result = db_query($query);
            $headers = array(
                "Book", "Author",
                "Contributor", "Reviewer",
                "Action",
            );
            $rows = array();
            while($row = db_fetch_object($result)) {
                $item = array(
                    "{$row->book}",
                    "{$row->author}",
                    "{$row->contributor}",
                    "{$row->reviewer}",
                    l("View", "tbc_external_review/manage_comments/" . $row->preference_id),
                );
                array_push($rows, $item);
            }
            $page_content .= theme("table", $headers, $rows);
        }
        return $page_content;
    }

    function tbc_external_review_add_book_all($preference_id=0, $action = "") {
        if($action == "confirm") {
            $query = "
                INSERT INTO {external_review_details}
                (preference_id)
                VALUES
                (%d)
            ";
            $result = db_query($query, $preference_id);
            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)
            ";
            if(!$result) {
                drupal_set_message("Book status update failed.", "error");
            } else {
                drupal_set_message("Book added successfully.", "status");
            }
            $result = db_query($query, $preference_id);
        } else 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 .= l("Yes", "tbc_external_review/add_book/{$row->id}/confirm") . " | ";
            $page_content .= l("Cancel", "manage_proposal/all");
            return $page_content;
        }
        drupal_goto("manage_proposal/all");
    }

    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);
                
                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 patience. <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 .= l("Yes", "tbc_external_review/status/complete/{$row->id}/yes") . " | ";
                $page_content .= l("Cancel", "tbc_external_review/status/");
            }
        } 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_test_all() {
        global $user;
        $page_content = "";
        /* sending mail */
        $to = "rush2jrp@gmail.com";
        $from = "jayaram@iitb.ac.in";
        $subject = "Test Subject";
        $body = "<b>Test body</b>";
        $message = array(
            "to" => $to,
            "from" => $from,
            "subject" => $subject,
            "body" => $body,
            "headers" => array(
                "Cc" => "jayaram@iitb.ac.in",
                "Bcc" => "jayaram@iitb.ac.in",
                "Content-Type" => "text/html; charset=UTF-8; format=flowed"
            )
        );
        drupal_mail_send($message);
        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.lightbox_me.js");
        drupal_add_js(drupal_get_path("module", "tbc_external_review") . "/js/tbc_external_review.js");
    }
?>