
<!DOCTYPE HTML>
<?PHP 
require 'functions.php';
checkLogin();
$db_link = connect();

//Select from GROUP and GROUP_MEMBER
if (isset($_POST['group_search'])){
    $group_search_name = sanitize($db_link, $_POST['group_search_name']);
    $group_search_member = sanitize($db_link, $_POST['group_search_member']);
    $group_search_contact = sanitize($db_link, $_POST['group_search_contact']);
    $group_search_residence = sanitize($db_link, $_POST['group_search_residence']);

    //Defining WHERE condition
    $where = "1=1";
    $title = "Groups";
    if ($group_search_name != ""){
        $where .= " AND group_name LIKE '%$group_search_name%'";
        $title .= ' with name "'.$group_search_name.'"';
    }
    if ($group_search_member != ""){
        $where .= " AND member_name LIKE '%$group_search_member%'";
        $title .= ' with member "'.$group_search_member.'"';
    }
    if ($group_search_contact != ""){
        $where .= " AND member_contact LIKE '%$group_search_contact%'";
        $title .= ' with contact "'.$group_search_contact.'"';
    }
    if ($group_search_residence != ""){
        $where .= " AND member_residence LIKE '%$group_search_residence%'";
        $title .= ' from "'.$group_search_residence.'"';
    }

    $sql_groupsearch = "SELECT * FROM `group` LEFT JOIN group_member ON `group`.group_id = group_member.group_id WHERE $where GROUP BY `group`.group_id ORDER BY `group`.group_id";
    $query_groupsearch = mysqli_query($db_link, $sql_groupsearch);
    checkSQL($db_link, $query_groupsearch);

    //Make array for exporting data
    $group_exp_date = date("Y-m-d",time());
    $_SESSION['group_export'] = array();
    $_SESSION['group_exp_title'] = 'groups-search_'.$group_exp_date;
} else header('Location: start.php');
?>
<html>
<?PHP includeHead('Group Search Result',1) ?>
<body>
<!-- MENU -->
<?PHP includeMenu(2); ?>
<div id="menu_main">
    <a href="group_search.php" id="item_selected">Search</a>
    <a href="group_new.php">New Group</a>
    <a href="group_act.php">Active Groups</a>
    <a href="group_inact.php">Inactive Groups</a>
</div>
<!-- CONTENT: Search Results -->
<div id="content_center">
    <table id="tb_table">
        <colgroup>
            <col width="10%">
            <col width="20%">
            <col width="20%">
            <col width="20%">
            <col width="20%">
            <col width="10%">
        </colgroup>
        <tr>
            <form class="export" action="group_export.php" method="post">
                <th class="title" colspan="6"><?PHP echo $title; ?>
                    <!-- Export Button -->
                    <input type="submit" name="export_group" value="Export" />
                </th>
            </form>
        </tr>
        <tr>
            <th>Group ID</th>
            <th>Group Name</th>
            <th>Member Name</th>
            <th>Contact</th>
            <th>Residence</th>
            <th>Occupation</th>
        </tr>
        <?PHP while ($row_groupsearch = mysqli_fetch_assoc($query_groupsearch)){
            echo '<tr>
                <td><a href="group.php?group_id='.$row_groupsearch['group_id'].'">'.$row_groupsearch['group_id'].'</a></td>
                <td><a href="group.php?group_id='.$row_groupsearch['group_id'].'">'.$row_groupsearch['group_name'].'</a></td>
                <td>'.$row_groupsearch['member_name'].'</td>
                <td>'.$row_groupsearch['member_contact'].'</td>
                <td>'.$row_groupsearch['member_residence'].'</td>
                <td>'.$row_groupsearch['member_occupation'].'</td>
            </tr>';
            //Prepare data for export to Excel file
            array_push($_SESSION['group_export'], array("Group ID" => $row_groupsearch['group_id'], "Group Name" => $row_groupsearch['group_name'], "Member Name" => $row_groupsearch['member_name'], "Contact" => $row_groupsearch['member_contact'], "Residence" => $row_groupsearch['member_residence'], "Occupation" => $row_groupsearch['member_occupation']));
        } ?>
    </table>
</div>
</body>
</html>