<?php
/*
    search.php
    OpenDRS Online Discrepancy Reporting System
    Copyright (C) 2020  Rod Wright

    SPDX-License-Identifier: GPL-2.0
*/

include("common.php");

// import session variables
if (isset($_SESSION['userid'])) $userid=$_SESSION['userid'];

// import incoming arrays
$print=$_REQUEST['print'];
if ($print) {
    // if printer friendly was clicked, values will be passed in serialized
    // form as "all_parameters" so we need to load those into $incoming[]
    $incoming=unserialize($_REQUEST['all_parameters']);
    
} else {
    if (!$_REQUEST['cancel']) $incoming=arrayCopy($_REQUEST);
}

// load variables from incoming array
$search=$incoming['search'];
$viewtype=$incoming['viewtype'];
$s_device=$incoming['s_device'];
$s_period=$incoming['s_period'];
$s_period_effective=$incoming['s_period_effective'];
$s_reported_by=$incoming['s_reported_by'];
$s_report_date_start=$incoming['s_report_date_start'];
$s_report_date_end=$incoming['s_report_date_end'];
$s_report_time_start=$incoming['s_report_time_start'];
$s_report_time_end=$incoming['s_report_time_end'];
$s_action_date_start=$incoming['s_action_date_start'];
$s_action_date_end=$incoming['s_action_date_end'];
$s_action_time_start=$incoming['s_action_time_start'];
$s_action_time_end=$incoming['s_action_time_end'];
$s_action_by=$incoming['s_action_by'];
$s_action_reason=$incoming['s_action_reason'];
$s_action_text=$incoming['s_action_text'];
$s_subsystem=$incoming['s_subsystem'];
$s_id=$incoming['s_id'];
$s_discrepancy_text=$incoming['s_discrepancy_text'];
$s_status=$incoming['s_status'];
$s_group=$incoming['s_group'];
$sort=$incoming['sort'];
$order=$incoming['order'];

// assign variables from constants
$appname=APP_NAME;

if ($print) {
    $mbgcolor=P_MENUBG_COLOR;
} else {
    $mbgcolor=MENUBG_COLOR;
}

$role=dblookup($drs_db,"users","id","role",$userid);
$pgtitle="Search Writeups";

framework("begin","$appname",$pgtitle,$print);

//var_dump($_REQUEST);
//var_dump($incoming);


// ******** begin database manipulation ********

// get min, max report and action_dates from writeups table, validate input dates/times and set defaults
$minmaxqry=mysqli_query($drs_db,"select min(report_date),max(report_date),min(action_date),max(action_date) from writeups");
$minmaxdates=mysqli_fetch_row($minmaxqry);
if (!$s_report_date_start || !validateDate($s_report_date_start)) $s_report_date_start=$minmaxdates[0];
if (!$s_report_date_end || !validateDate($s_report_date_end)) $s_report_date_end=$minmaxdates[1];
if (!$s_report_time_start || !validateTime($s_report_time_start)) $s_report_time_start="00:00:00";
if (!$s_report_time_end || !validateTime($s_report_time_end)) $s_report_time_end="23:59:59";

if ($search) {
    // strip whitespace from beginning and end of text fields
    $s_reported_by=trim($s_reported_by);
    $s_action_text=trim($s_action_text);
    $s_discrepancy_text=trim($s_discrepancy_text);
    $s_id=trim($s_id);
    
    // build the query string
    $query_string="";
    // device
    if ($s_device) {
        $query_string="{$query_string}(";
        $device_param="Devices:";
        foreach ($s_device as $dev_val) {
            $query_string="{$query_string}device=\"{$dev_val}\" or ";
            $device_dsp=dblookup($drs_db,"devices","id","name",$dev_val);
            $device_param="{$device_param} {$device_dsp}, ";
        }
        $device_param=rtrim($device_param,", ");
        $num_devices=mysqli_num_rows(mysqli_query($drs_db,"select id from devices"));
        if (count($s_device)==$num_devices) $device_param="Devices: any";
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // subsystem
    if ($s_subsystem) {
        $query_string="{$query_string}(";
        $subsystem_param="Subsystems:";
        foreach ($s_subsystem as $sub_val) {
            $query_string="{$query_string}subsystem=\"{$sub_val}\" or ";
            $subsystem_dsp=dblookup($drs_db,"subsystems","id","name",$sub_val);
            $subsystem_param="{$subsystem_param} {$subsystem_dsp}, ";
        }
        $subsystem_param=rtrim($subsystem_param,", ");
        $num_subsystems=mysqli_num_rows(mysqli_query($drs_db,"select id from subsystems"));
        if (count($s_subsystem)==$num_subsystems) $subsystem_param="Subsystems: any";
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // id
    if ($s_id) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}id={$s_id}";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $id_param="ID: {$s_id}";
    } else {
        $id_param="ID: any";
    }
    // period
    if ($s_period) {
        $query_string="{$query_string}(";
        $period_param="Periods:";
        foreach ($s_period as $per_val) {
            $query_string="{$query_string}period=\"{$per_val}\" or ";
            $period_dsp=$per_val;
            $period_param="{$period_param} {$period_dsp}, ";
        }
        $period_param=rtrim($period_param,", ");
        $num_periods=mysqli_num_rows(mysqli_query($drs_db,"select id from periods"));
        if (count($s_period)==$num_periods) $period_param="Periods: any";
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // period_effective
    if ($s_period_effective) {
        $query_string="{$query_string}(";
        $eff_param="Period effective:";
        foreach ($s_period_effective as $eff_val) {
            $query_string="{$query_string}period_effective=\"{$eff_val}\" or ";
            if ($eff_val=="0") $eff_dsp="<font color=\"#FF0000\">NEF</font>";
            if ($eff_val=="1") $eff_dsp="<font color=\"#00FF00\">EFF</font>";
            $eff_param="{$eff_param} {$eff_dsp}, ";
        }
        $eff_param=rtrim($eff_param,", ");
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // status
    if ($s_status) {
        $query_string="{$query_string}(";
        $status_param="Status:";
        foreach ($s_status as $stat_val) {
            $query_string="{$query_string}status=\"{$stat_val}\" or ";
            if ($stat_val=="1") $stat_dsp="<font color=\"#FF0000\">OPEN</font>";
            if ($stat_val=="2") $stat_dsp="<font color=\"#00FF00\">CLSD</font>";
            if ($stat_val=="3") $stat_dsp="<font color=\"#FFFF00\">DFRD</font>";
            $status_param="{$status_param} {$stat_dsp}, ";
        }
        $status_param=rtrim($status_param,", ");
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // action by
    if ($s_action_by) {
        $query_string="{$query_string}(";
        $actionby_param="Status changed by:";
        foreach ($s_action_by as $actby_val) {
            $query_string="{$query_string}action_by=\"{$actby_val}\" or ";
            if ($actby_val==0) {
                $actionby_dsp=" (none set)";
            } else {
                $actionby_dsp=dblookup($drs_db,"users","id","firstname",$actby_val)." ".dblookup($drs_db,"users","id","lastname",$actby_val);
            }
            $actionby_param="{$actionby_param} {$actionby_dsp}, ";
        }
        $actionby_param=rtrim($actionby_param,", ");
        $num_users=mysqli_num_rows(mysqli_query($drs_db,"select id from users"));
        if (count($s_action_by)==$num_users+1) $actionby_param="Status changed by: any";
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // action reason
    if ($s_action_reason) {
        $query_string="{$query_string}(";
        $reason_param="Status change reason:";
        foreach ($s_action_reason as $actrn_val) {
            $query_string="{$query_string}action_reason=\"{$actrn_val}\" or ";
            if ($actrn_val==0) {
                $reason_dsp=" (none set)";
            } else {
                $reason_dsp=dblookup($drs_db,"reasons","id","text",$actrn_val);
            }
            $reason_param="{$reason_param} {$reason_dsp}, ";
        }
        $reason_param=rtrim($reason_param,", ");
        $num_reasons=mysqli_num_rows(mysqli_query($drs_db,"select id from reasons"));
        if (count($s_action_reason)==$num_reasons+1) $reason_param="Status change reason: any";
        $query_string=rtrim($query_string," or ");
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
    }
    // report date
    $query_string="{$query_string}(";
    $query_string="{$query_string}report_date between \"{$s_report_date_start}\" and \"{$s_report_date_end}\"";
    $query_string="{$query_string})";
    $query_string="{$query_string} and ";
    $reportdate_param="Report date between {$s_report_date_start} and {$s_report_date_end}";
    // report time
    $query_string="{$query_string}(";
    $query_string="{$query_string}report_time between \"{$s_report_time_start}\" and \"{$s_report_time_end}\"";
    $query_string="{$query_string})";
    $query_string="{$query_string} and ";
    $reporttime_param="Report time between {$s_report_time_start} and {$s_report_time_end}";
    // action date
    if ($s_action_date_start && $s_action_date_end) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}action_date between \"{$s_action_date_start}\" and \"{$s_action_date_end}\"";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $actiondate_param="Action taken date between {$s_action_date_start} and {$s_action_date_end}";
    } else {
        $actiondate_param="Action taken date: any";
    }
    // action time
    if ($s_action_time_start && $s_action_time_end) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}action_time between \"{$s_action_time_start}\" and \"{$s_action_time_end}\"";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $actiontime_param="Action taken time between {$s_action_time_start} and {$s_action_time_end}";
    } else {
        $actiontime_param="Action taken time: any";
    }
    // discrepancy text
    if ($s_discrepancy_text) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}discrepancy_text like \"%{$s_discrepancy_text}%\"";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $discrepancytext_param="Text in discrepancy: {$s_discrepancy_text}";
    } else {
        $discrepancytext_param="Text in discrepancy: any";
    }
    // action text
    if ($s_action_text) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}action_text like \"%{$s_action_text}%\"";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $actiontext_param="Text in Action taken: {$s_action_text}";
    } else {
        $actiontext_param="Text in Action taken: any";
    }
    // reported by
    if ($s_reported_by) {
        $query_string="{$query_string}(";
        $query_string="{$query_string}reported_by like \"%{$s_reported_by}%\"";
        $query_string="{$query_string})";
        $query_string="{$query_string} and ";
        $repby_param="Text in Reported by: {$s_reported_by}";
    } else {
        $repby_param="Text in Reported by: any";
    }
    
    $query_string=rtrim($query_string," and ");
    $query_string="select * from writeups where {$query_string} order by $sort $order";
    
    // group membership
    if ($s_group) {
        $group_param="Group member:";
        foreach ($s_group as $group_val) {
            if ($group_val=="0") $group_dsp="No";
            if ($group_val=="1") $group_dsp="Yes";
            $group_param="{$group_param} {$group_dsp}, ";
        }
        $group_param=rtrim($group_param,", ");
    }
}


// ******** end database manipulation ********

pagetable("begin");
if(!$print) {
    pageblock("left","begin");
    sidemenu();
    echo "<br><br>";
    pageblock("left","end");
}
pageblock("right","begin");
banner($print); 
echo "<br>";

// show search form
if (!$print) {
    echo "
    <form method=\"post\" action=\"search.php#results\">
    <table border=\"0\" cellpadding=\"5\">
    <tr>
    <td align=\"left\">
    ";
    // ID cell *****************
    echo "
    ID<br>
    <input type=\"text\" name=\"s_id\" size=\"10\" value=\"$s_id\">
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Device cell *********************
    echo "
    Device<br>
    <select name=\"s_device[]\" id=\"ms-device\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    ";
    $drow=mysqli_query($drs_db,"select * from devices order by id asc");
    while ($ditem=mysqli_fetch_assoc($drow)) {
        if (!$search || in_array($ditem["id"],$s_device,true)) {
            printf("<option value=\"%s\" selected>%s</option>",$ditem["id"],$ditem["name"]);
        } else {
            printf("<option value=\"%s\">%s</option>",$ditem["id"],$ditem["name"]);
        }
    }
    echo "
    </select>
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Subsystem cell *******************
    echo "
    Subsystem<br>
    <select name=\"s_subsystem[]\" id=\"ms-subsystem\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    ";
    $ssrow=mysqli_query($drs_db,"select * from subsystems order by id asc");
    while ($ssitem=mysqli_fetch_assoc($ssrow)) {
        if (!$search || in_array($ssitem["id"],$s_subsystem,true)) {
            printf("<option value=\"%s\" title=\"%s\" selected>%s</option>",$ssitem["id"],$ssitem["description"],$ssitem["name"]);
        } else {
            printf("<option value=\"%s\" title=\"%s\">%s</option>",$ssitem["id"],$ssitem["description"],$ssitem["name"]);
        }
    }
    echo "
    </select>
    ";
    // **********************************
    echo "
    </td>
    ";
    echo "
    </tr>
    <tr>
    <td align=\"left\">
    ";
    // Period cell **********************
    echo "
    Period<br>
    <select name=\"s_period[]\" id=\"ms-period\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    ";
    $prow=mysqli_query($drs_db,"select * from periods order by times asc");
    while ($pitem=mysqli_fetch_assoc($prow)) {
        if (!$search || in_array($pitem["id"],$s_period,true)) {
            printf("<option value=\"%s\" title=\"Period %s\" selected>%s</option>",$pitem["id"],$pitem["id"],$pitem["times"]);
        } else {
            printf("<option value=\"%s\" title=\"Period %s\">%s</option>",$pitem["id"],$pitem["id"],$pitem["times"]);
        }
    }
    echo "
    </select>
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Report Date cell *****************
    echo "
    Report Date Between<br>
    <input type=\"text\" name=\"s_report_date_start\" size=\"10\" maxlength=\"10\" value=\"$s_report_date_start\" id=\"sdp-report_date\" title=\"Format: YYYY-MM-DD\">
    &nbsp;and&nbsp;
    <input type=\"text\" name=\"s_report_date_end\" size=\"10\" maxlength=\"10\" value=\"$s_report_date_end\" id=\"edp-report_date\" title=\"Format: YYYY-MM-DD\">
    ";
    // **********************************
    echo "
    </td>
    ";
    echo "
    <td align=\"left\">
    ";
    // Report Time cell *****************
    echo "
    Report Time Between<br>
    <input type=\"text\" name=\"s_report_time_start\" size=\"8\" maxlength=\"8\" value=\"$s_report_time_start\" title=\"Format: HH:MM:SS\">
    &nbsp;and&nbsp;
    <input type=\"text\" name=\"s_report_time_end\" size=\"8\" maxlength=\"8\" value=\"$s_report_time_end\" title=\"Format: HH:MM:SS\">
    ";
    // **********************************
    echo "
    </td>
    </tr>
    <tr><td></td><td></td><td></td></tr>
    <tr>
    <td colspan=\"3\">
    ";
    // Discrepancy text cell ************
    echo "
    Words in Discrepancy<br>
    <textarea rows=\"5\" cols=\"80\" name=\"s_discrepancy_text\" wrap=\"soft\">$s_discrepancy_text</textarea><br>
    ";
    // **********************************
    echo "
    </td>
    </tr>
    <tr>
    <td align=\"left\">
    ";
    // Reported by cell *****************
    echo "
    Reported by<br>
    <input type=\"text\" name=\"s_reported_by\" size=\"30\" value=\"$s_reported_by\">
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Period effective cell ************
    echo "
    Sim period effective? &nbsp; <img src=\"icons/question.png\" title=\"" . PERIOD_EFF_HELP_TXT . "\"><br>
    ";
    if ($search) {
        $effstate=$nefstate="";
        if (in_array("1",$s_period_effective,true)) $effstate="selected";
        if (in_array("0",$s_period_effective,true)) $nefstate="selected";
    } else {
        $effstate=$nefstate="selected";
    }
    echo "
    <select name=\"s_period_effective[]\" id=\"ms-period_effective\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    <option value=\"1\" $effstate>Effective</option>
    <option value=\"0\" $nefstate>Non-effective</option>
    </select>
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Group status cell **************
    echo "
    Group Member?<br>
    ";
    if ($search) {
        $yesgroupstate=$nogroupstate="";
        if (in_array("1",$s_group,true)) $yesgroupstate="selected";
        if (in_array("0",$s_group,true)) $nogroupstate="selected";
    } else {
        $yesgroupstate=$nogroupstate="selected";
    }echo "
    <select name=\"s_group[]\" id=\"ms-group\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    <option value=\"1\" $yesgroupstate title=\"Writeup is a member of a group\">Show group members</option>
    <option value=\"0\" $nogroupstate title=\"Writeup is not a member of a group\">Show non group members</option>
    </select>
    ";
    // ************************************
    echo "
    </td></tr>
    <tr><td colspan=\"3\" bgcolor=\"black\"></td></tr>
    <tr><td colspan=\"3\" bgcolor=\"black\"></td></tr>
    <tr>
    <td align=\"left\">
    ";
    // Status cell **********************
    echo "
    Status<br>
    ";
    if ($search) {
        $openstate=$clsdstate=$dfrdstate="";
        if (in_array("1",$s_status,true)) $openstate="selected";
        if (in_array("2",$s_status,true)) $clsdstate="selected";
        if (in_array("3",$s_status,true)) $dfrdstate="selected";
    } else {
        $openstate=$clsdstate=$dfrdstate="selected";
    }
    echo "
    <select name=\"s_status[]\" id=\"ms-status\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    <option title=\"Open\" value=\"1\" $openstate>OPEN</option>
    <option title=\"Closed\" value=\"2\" $clsdstate>CLSD</option>
    <option title=\"Deferred\" value=\"3\" $dfrdstate>DFRD</option>
    </select>
    ";
    // **********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // action by cell ************
    echo "
    Status changed by<br>
    <select name=\"s_action_by[]\" id=\"ms-user\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    <option value=\"0\" selected>(none set)</option>
    ";
    $persrow=mysqli_query($drs_db,"select id,firstname,lastname from users order by lastname asc");
    while ($persitem=mysqli_fetch_assoc($persrow)) {
        printf("<option value=\"%s\" selected>%s %s</option>",$persitem["id"],$persitem["firstname"],$persitem["lastname"]);
    }
    echo "
    </select>
    ";
    // ***********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // action reason cell *********
    echo "
    Status change reason<br>
    <select name=\"s_action_reason[]\" id=\"ms-reason\" multiple title=\"If you uncheck all, then this field will not be included in the search.\">
    <option value=\"0\" selected>(none set)</option>
    ";
    $reasrow=mysqli_query($drs_db,"select id,text from reasons order by id asc");
    while ($reasitem=mysqli_fetch_assoc($reasrow)) {
        printf("<option value=\"%s\" selected>%s</option>",$reasitem["id"],$reasitem["text"]);
    }
    echo "
    </select>
    ";
    // ***********************************
    echo "
    </td>
    </tr>
    <tr><td></td><td></td><td></td></tr>
    <tr>
    <td colspan=\"3\">
    ";
    // Action taken text cell ************
    echo "
    Words in Action Taken<br>
    <textarea rows=\"5\" cols=\"80\" name=\"s_action_text\" wrap=\"soft\">$s_action_text</textarea><br>
    ";
    // ***********************************
    echo "
    </td>
    </tr>
    <tr>
    <td align=\"left\" >
    ";
    // Action date cell ******************
    echo "
    Action Taken Date Between<br>
    <input type=\"text\" name=\"s_action_date_start\" size=\"10\" maxlength=\"10\" value=\"$s_action_date_start\" id=\"sdp-action_date\" title=\"Format: YYYY-MM-DD\">
    &nbsp;and&nbsp;
    <input type=\"text\" name=\"s_action_date_end\" size=\"10\" maxlength=\"10\" value=\"$s_action_date_end\" id=\"edp-action_date\" title=\"Format: YYYY-MM-DD\">
    ";
    // ***********************************
    echo "
    </td>
    <td align=\"left\">
    ";
    // Action time cell ******************
    echo "
    Action Taken Time Between<br>
    <input type=\"text\" name=\"s_action_time_start\" size=\"8\" maxlength=\"8\" value=\"$s_action_time_start\" title=\"Format: HH:MM:SS\">
    &nbsp;and&nbsp;
    <input type=\"text\" name=\"s_action_time_end\" size=\"8\" maxlength=\"8\" value=\"$s_action_time_end\" title=\"Format: HH:MM:SS\">
    ";
    // ***********************************
    $summarystat=$detailstat="";
    if ($viewtype=="detail") {
        $detailstat="checked";
    } else {
        $summarystat="checked";
    }
    $idsort=$statussort=$datesort=$periodsort=$devicesort=$subsyssort=$repbysort=$discrepsort=$effsort="";
    switch ($sort) {
        case "id":
            $idsort="checked";
            break;
        case "status":
            $statussort="checked";
            break;
        case "report_date":
            $datesort="checked";
            break;
        case "period":
            $periodsort="checked";
            break;
        case "device":
            $devicesort="checked";
            break;
        case "subsystem":
            $subsyssort="checked";
            break;
        case "reported_by":
            $repbysort="checked";
            break;
        case "discrepancy_text":
            $discrepsort="checked";
            break;
        case "period_effective":
            $effsort="checked";
            break;
        default:
            $datesort="checked";
    }
    $ascsort=$descsort="";
    if ($order=="desc") {
        $descsort="checked";
    } else {
        $ascsort="checked";
    }
    echo "</td>
    </tr>
    </table>
    <br>
    <input type=\"radio\" $summarystat name=\"viewtype\" value=\"summary\" id=\"summaryview\" ><label for=\"summaryview\" title=\"Show one writeup per line with minimal detail.\">Results in summary view</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $detailstat name=\"viewtype\" value=\"detail\" id=\"detailview\"><label for=\"detailview\" title=\"Show each writeup's full details.\">Results in detail view</label>
    <br><br>
    Sort Field:<br>
    <input type=\"radio\" $idsort name=\"sort\" value=\"id\" id=\"idview\" ><label for=\"idview\" title=\"Sort by ID.\">ID</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $statussort name=\"sort\" value=\"status\" id=\"statusview\" ><label for=\"statusview\" title=\"Sort by Status.\">Status</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $datesort name=\"sort\" value=\"report_date\" id=\"dateview\" ><label for=\"dateview\" title=\"Sort by Report Date.\">Date</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $periodsort name=\"sort\" value=\"period\" id=\"periodview\" ><label for=\"periodview\" title=\"Sort by Period.\">Period</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $devicesort name=\"sort\" value=\"device\" id=\"deviceview\" ><label for=\"deviceview\" title=\"Sort by Device.\">Device</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $subsyssort name=\"sort\" value=\"subsystem\" id=\"subsysview\" ><label for=\"subsysview\" title=\"Sort by Subsystem.\">Subsystem</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $repbysort name=\"sort\" value=\"reported_by\" id=\"repbyview\" ><label for=\"repbyview\" title=\"Sort by Reported by.\">Reported by</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $discrepsort name=\"sort\" value=\"discrepancy_text\" id=\"discrepview\" ><label for=\"discrepview\" title=\"Sort by Discrepancy.\">Discrepancy</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $effsort name=\"sort\" value=\"period_effective\" id=\"effview\" ><label for=\"effview\" title=\"Sort by Mission Effective.\">Msn Eff</label>&nbsp;&nbsp;&nbsp;
    <br>Sort Order:<br>
    <input type=\"radio\" $ascsort name=\"order\" value=\"asc\" id=\"ascview\" ><label for=\"ascview\" title=\"Sort results in ascending order.\">Ascending</label>&nbsp;&nbsp;&nbsp;
    <input type=\"radio\" $descsort name=\"order\" value=\"desc\" id=\"descview\" ><label for=\"descview\" title=\"Sort results in descending order.\">Descending</label>&nbsp;&nbsp;&nbsp;
    <br><br>
    <input type=\"submit\" name=\"search\" value=\"Search\">
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <input type=\"submit\" name=\"cancel\" value=\"Start New Search\">
    
    </form>
    <br>
    ";
}

// display the results
if ($search) {
    //display the search parameters used
    if (!$print) echo "<a id=\"results\"></a>";
    echo "
    <table border=\"2\" width=\"100%\">
    <tr><td align=\"left\"><b>Parameters used in this search</b>
    ";
    if (!$print) {
        echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font size=\"-1\">Scroll up to modify parameters</font></td>";
    } else {
        echo "</td>";
    }
    echo "
    </tr>
    <tr><td>
    <table border=\"0\">
    <td><b>$status_param</b></td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$device_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$subsystem_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$id_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$period_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$reportdate_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$reporttime_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$discrepancytext_param</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$repby_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$eff_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$group_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$actionby_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$reason_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$actiontext_param</td>
    </tr>
    </table>
    <table border=\"0\">
    <tr>
    <td>$actiondate_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    <td>$actiontime_param</td><td>&nbsp;&nbsp;&nbsp;</td>
    </tr>
    </table>
    </td></tr></table>
    <br>
    ";
    $writeup=mysqli_query($drs_db,$query_string);
    // create an array of ids returned by the query string
    $result_ids=array();
    while ($eachid=mysqli_fetch_assoc($writeup)) {
        $result_ids[]=$eachid['id'];
    }
    mysqli_data_seek($writeup,0);
    // create an array of all ids in groups
    $groupmems=array();
    $groupmem_qry=mysqli_query($drs_db,"select writeupid from links");
    while ($groupmemrow=mysqli_fetch_row($groupmem_qry)) {
        $groupmems[]=$groupmemrow[0];
    }
    $groupmems=array_unique($groupmems);
    // determine how many group members are in results
    $member_count=0;
    foreach($result_ids as $result_id) {
        if (in_array($result_id,$groupmems)) $member_count += 1;
    }
    if (in_array("1",$s_group)) $show_groupmems=true;
    if (in_array("0",$s_group)) $show_nongroupmems=true;
    
    if ($show_groupmems == $show_nongroupmems) $num_results=mysqli_num_rows($writeup);    //show members and nonmembers
    if ($show_groupmems && !$show_nongroupmems) $num_results=$member_count;    //only show members
    if (!$show_groupmems && $show_nongroupmems) $num_results=mysqli_num_rows($writeup)-$member_count;    //only show nonmembers
    if ($num_results > 0) {
        if ($viewtype=="summary") {
            echo "
            <table border=\"1\" cellpadding=\"5\" style=\"width:100%;\">
                <tr>
                    <th style=\"width:1%;\">ID</th>
                    <th style=\"width:1%;\">Status</th>
                    <th style=\"width:100px;\">Date</th>
                    <th style=\"width:1%;\">Period</th>
                    <th style=\"width:1%;\">Device</th>
                    <th style=\"width:1%;\">Subsystem</th>
                    <th style=\"width:1%;\">Reported by</th>
                    <th>Discrepancy</th>
                    <th style=\"width:1%;\">Msn Eff</th>
                </tr>
            ";
            while ($tablerow = mysqli_fetch_assoc($writeup)) {
                // determine if this is a member of a group
                $member_qry=mysqli_query($drs_db,"select linkgroup from links where writeupid=\"{$tablerow['id']}\"");
                $is_member=mysqli_num_rows($member_qry);
                
                // determine effective icon
                if($tablerow["period_effective"]==1) {
                    $eff_icon="<img src=\"icons/tick.png\" border=\"0\" title=\"Period Effective\" alt=\"Period Effective\">";
                } else {
                    $eff_icon="<img src=\"icons/cross.png\" border=\"0\" title=\"Period Non-effective\" alt=\"Period Non-effective\">";
                }
                
                // determine status indicator
                if ($tablerow['status']==1) $statusind="<font color=\"#FF0000\"><div title=\"Open\">OPEN</div></font>";
                if ($tablerow['status']==2) $statusind="<font color=\"#00FF00\"><div title=\"Closed\">CLSD</div></font>";
                if ($tablerow['status']==3) $statusind="<font color=\"#FFFF00\"><div title=\"Deferred\">DFRD</div></font>";
                
                // convert device number to name
                $dname=dblookup($drs_db,"devices","id","name",$tablerow["device"]);
                // convert period number to times
                $ptimes=dblookup($drs_db,"periods","id","times",$tablerow["period"]);
                // convert subsystem number to name
                $ssname=dblookup($drs_db,"subsystems","id","name",$tablerow["subsystem"]);
                $disc_txt="{$tablerow["discrepancy_text"]}";
                
                // set group indicator
                if ($is_member) {
                    $group_count=$is_member;
                    if ($group_count == 1) {
                        $group_num=mysqli_fetch_row(mysqli_query($drs_db,"select linkgroup from links where writeupid=\"{$tablerow['id']}\""))[0];
                        $member_group_name=dblookup($drs_db,"groups","id","name",$group_num);
                        if ($member_group_name) {
                            $imgtitle="Member of group: $member_group_name";
                        } else {
                            $imgtitle="Member of group ID: $group_num";
                        }
                        $disc_txt=$disc_txt."<div align=\"right\"><a href=\"groups.php?group=$group_num\"><img src=\"icons/block.png\" alt=\"GRP\" title=\"$imgtitle\"></a></div>";
                    } else {
                        $disc_txt=$disc_txt."<div align=\"right\"><a href=\"groups.php\"><img src=\"icons/block.png\" alt=\"GRP\" title=\"View writeup groups\"></a></div>";
                    }
                }
                
                if (($show_groupmems == $show_nongroupmems) || ($is_member && $show_groupmems) || (!$is_member && $show_nongroupmems)) {
                    // print table row
                    printf(
                    "<tr>
                    <td align=\"center\" valign=\"top\"><a href=\"writeupdetail.php?id=%s\" title=\"View or change details of this writeup\">%s</a></td>
                    <td valign=\"top\">%s</td>
                    <td valign=\"top\">%s</td>
                    <td valign=\"top\"><font title=\"%s\">%s</font></td>
                    <td valign=\"top\">%s</td>
                    <td valign=\"top\">%s</td>
                    <td valign=\"top\">%s</td>
                    <td valign=\"top\">%s</td>
                    <td align=\"center\" valign=\"top\">%s</td>
                    </tr>\n",
                    $tablerow["id"],$tablerow["id"],
                    $statusind,
                    $tablerow["report_date"],
                    $ptimes,$tablerow["period"],
                    $dname,
                    $ssname,
                    $tablerow["reported_by"],
                    $disc_txt,
                    $eff_icon
                    );
                }
            }
            echo "</table>\n";
        } elseif ($viewtype=="detail") {
            echo "
            <table border=\"0\"><tr><td>
            ";
            while ($tablerow = mysqli_fetch_assoc($writeup)) {
                // determine if this is a member of a group
                $member_qry=mysqli_query($drs_db,"select linkgroup from links where writeupid=\"{$tablerow['id']}\"");
                $is_member=mysqli_num_rows($member_qry);
                if (($show_groupmems == $show_nongroupmems) || ($is_member && $show_groupmems) || (!$is_member && $show_nongroupmems)) {
                    displaywriteup($tablerow['id']);
                    echo "<br>";
                }
            }
            echo "
            </td></tr></table>
            ";
        }
    } else {
        echo "<b>No writeups match your search parameters.</b><br>";
    }
}

echo "<br>";
banner($print);
pageblock("right","end");
pagetable("end");

framework("end","","",$print);

?>