<?php
session_start();
class MyDB extends SQLite3
{
function __construct()
{
$this->open('resources.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
}
//Session Information
if (!empty($_POST)){
if(isset($_POST['section']))
$_SESSION['section'] = $_POST['section'];
if(isset($_POST['category']))
$_SESSION['category'] = $_POST['category'];
if(isset($_POST['topic']))
$_SESSION['topic'] = $_POST['topic'];
if(isset($_POST['items_to_show']))
$_SESSION['items_to_show'] = $_POST['items_to_show'];
}
$current_section = ((isset($_SESSION['section']) && $_SESSION['section'] != 'all')? $_SESSION['section'] : NULL);
$current_category = ((isset($_SESSION['category']) && $_SESSION['category'] != 'all') ? $_SESSION['category'] : NULL);
$current_topic = ((isset($_SESSION['topic']) && $_SESSION['topic'] != 'all') ? $_SESSION['topic'] : NULL);
$current_items_to_show = (isset($_SESSION['items_to_show']) ? $_SESSION['items_to_show'] : 10);
$sql_where = '';
if(isset($current_section) || isset($current_category) || isset($current_topic)){
if(isset($current_section)){
if(isset($current_category)){
$sql_where = 'WHERE section == "'.$current_section.'" AND category == "'.$current_category.'"';
if(isset($current_topic)){
$sql_where = 'WHERE section == "'.$current_section.'" AND category == "'.$current_category.'" AND topic == "'.$current_topic.'"';
}
}elseif (isset($current_topic)) {
$sql_where = 'WHERE section == "'.$current_section.'" AND topic == "'.$current_topic.'"';
}else{
$sql_where = 'WHERE section == "'.$current_section.'"';
}
}elseif (isset($current_category)) {
if(isset($current_topic)){
$sql_where = 'WHERE category == "'.$current_category.'" AND topic == "'.$current_topic.'"';
}else{
$sql_where = 'WHERE category == "'.$current_category.'"';
}
}elseif (isset($current_topic)){
$sql_where = 'WHERE topic == "'.$current_topic.'"';
}
}
//pageation
try {
// Find out how many items are in the table
$rows = $db->query('SELECT COUNT(*) as count FROM resources '.$sql_where);
$row = $rows->fetchArray();
$total = $row['count'];
// How many items to list per page
$items_to_show = $current_items_to_show;
$maxlinks = 10;
// How many pages will there be
$pages = ceil($total / $items_to_show);
// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array('default' => 1, 'min_range' => 1,),)));
// Calculate the offset for the query
$offset = ($page - 1) * $items_to_show;
// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $items_to_show), $total);
// The "back" link
$prevlink = ($page > 1) ? '<li><a href="?page=1" title="First page">«</a></li> <li><a href="?page=' . ($page - 1) . '" title="Previous page">‹</a></li>' : '<li class="disabled"><a href="#">«</a></li> <li class="disabled"><a href="#">‹</a></li>';
// The "forward" link
$nextlink = ($page < $pages) ? '<li><a href="?page=' . ($page + 1) . '" title="Next page">›</a></li> <li><a href="?page=' . $pages . '" title="Last page">»</a></li>' : '<li class="disabled"><a href="#">›</a></li> <li class="disabled"><a href="#">»</a></li>';
//Filers
//Sections
$sql_section = "SELECT DISTINCT section FROM resources ORDER BY category ASC";
$sections = $db->query($sql_section);
//add each category to an array
$section_array = array();
while($section = $sections->fetchArray(SQLITE3_ASSOC) ){
$section_array[] = $section['section'];
}
// Prepare the paged query
$sql = "SELECT * FROM resources ".
$sql_where.
" ORDER BY section, category, topic, title ASC
LIMIT ".$items_to_show." OFFSET ".$offset;
$result = $db->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Links | Admin | The Family & Community Resource Centre</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<?php require_once($_SERVER['DOCUMENT_ROOT'].'/require/head.php');?>
<link href="//maxcdn.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css" rel="stylesheet">
<script src="//code.jquery.com/jquery-1.11.0.min.js"></script>
<style>
th{
text-align: left;
}
h5 {
margin: 0;
padding: 0;
}
span.buttons{
float: right;
}
.pagination li{
margin: 0;
padding: 0;
}
form .btn {
margin-top:24px;
}
</style>
<script type="text/javascript">
var setcategorieshtml = function(){
$('#category').prop('selectedIndex',0);
if($('#section').val() == "all"){
$("#category").empty().html(
<?php
echo '"';
$sql_categories = "SELECT DISTINCT category FROM resources WHERE category IS NOT NULL AND category != '' ORDER BY section, category ASC";
$categories = $db->query($sql_categories);
echo "<option value='all'>All</option>";
while($category = $categories->fetchArray(SQLITE3_ASSOC)){
echo "<option value='".$category['category']."'".($category['category'] == $current_category ? ' selected' :'').'>'.$category['category'].'</option>';
}
echo '"';
?>
);
}
<?php
$sql_section = "SELECT DISTINCT section FROM resources ORDER BY section ASC";
$sections = $db->query($sql_section);
while($section = $sections->fetchArray(SQLITE3_ASSOC)){
$sql_categories = "SELECT DISTINCT category FROM resources WHERE category IS NOT NULL AND category != '' AND section =='".$section['section']."' ORDER BY section, category ASC";
$categories = $db->query($sql_categories);
echo "else if ($('#section').val() == '".$section['section']."'){"."\n";
echo '$("#category").empty().html("';
echo "<option value='all'>All</option>";
while($category = $categories->fetchArray(SQLITE3_ASSOC) ){
echo "<option value='".$category['category']."'".($category['category'] == $current_category ? ' selected' :'').'>'.$category['category'].'</option>';
}
echo '");';
echo "}";
}
?>
};
var settopicshtml = function(){
$('#topic').prop('selectedIndex',0);
if($('#category').val() == "all"){
$("#topic").empty().html(
<?php
echo '"';
$sql_topics = "SELECT DISTINCT topic FROM resources WHERE topic IS NOT NULL AND topic != '' ORDER BY section, category, topic ASC";
$topics = $db->query($sql_topics);
echo "<option value='all'>All</option>";
while($topic = $topics->fetchArray(SQLITE3_ASSOC)){
echo "<option value='".$topic['topic']."'".($topic['topic'] == $current_topic ? ' selected' :'').'>'.$topic['topic'].'</option>';
}
echo '"';
?>
);
}
<?php
$sql_categories = "SELECT DISTINCT category FROM resources ORDER BY category ASC";
$categories = $db->query($sql_categories);
while($category = $categories->fetchArray(SQLITE3_ASSOC)){
$sql_topics = "SELECT DISTINCT topic FROM resources WHERE topic IS NOT NULL AND topic != '' AND category =='".$category['category']."' ORDER BY section, category, topic ASC";
$topics = $db->query($sql_topics);
echo "else if ($('#category').val() == '".$category['category']."'){"."\n";
echo '$("#topic").empty().html("';
echo "<option value='all'>All</option>";
while($topic = $topics->fetchArray(SQLITE3_ASSOC) ){
echo "<option value='".$topic['topic']."'".($topic['topic'] == $current_topic ? ' selected' :'').'>'.$topic['topic'].'</option>';
}
echo '");';
echo "}";
}
?>
};
var settopicshtml2 = function(){
$('#topic').prop('selectedIndex',0);
if($('#section').val() == "all"){
$("#topic").empty().html(
<?php
echo '"';
$sql_topics = "SELECT DISTINCT topic FROM resources WHERE topic IS NOT NULL AND topic != '' ORDER BY section, category, topic ASC";
$topics = $db->query($sql_topics);
echo "<option value='all'>All</option>";
while($topic = $topics->fetchArray(SQLITE3_ASSOC)){
echo "<option value='".$topic['topic']."'".($topic['topic'] == $current_topic ? ' selected' :'').'>'.$topic['topic'].'</option>';
}
echo '"';
?>
);
}
<?php
$sql_section = "SELECT DISTINCT section FROM resources ORDER BY section ASC";
$sections = $db->query($sql_section);
while($section = $sections->fetchArray(SQLITE3_ASSOC)){
$sql_topics = "SELECT DISTINCT topic FROM resources WHERE topic IS NOT NULL AND topic != '' AND section =='".$section['section']."' ORDER BY section, category, topic ASC";
$topics = $db->query($sql_topics);
echo "else if ($('#section').val() == '".$section['section']."'){"."\n";
echo '$("#topic").empty().html("';
echo "<option value='all'>All</option>";
while($topic = $topics->fetchArray(SQLITE3_ASSOC) ){
echo "<option value='".$topic['topic']."'".($topic['topic'] == $current_topic ? ' selected' :'').'>'.$topic['topic'].'</option>';
}
echo '");';
echo "}";
}
?>
};
$(document).ready(settopicshtml);
$(document).ready(setcategorieshtml);
$(document).ready(function(){
<?php
if($current_topic != NULL){
echo '$("select#topic").val("'.$current_topic.'");'."\n";
}
if($current_category != NULL){
echo '$("select#category").val("'.$current_category.'");'."\n";
}
?>
$("#category").change(function(){
settopicshtml();
});
$("#section").change(function(){
setcategorieshtml();
settopicshtml2();
});
});
</script>
</head>
<body>
<div class="wrapper">
<?php require_once($_SERVER['DOCUMENT_ROOT'].'/require/header.php');?>
<section class="middle">
<article style="width:100%;">
<h2>FCRC Links</h2>
<hr>
<form role="form" method="post" action="./" class="form row" name="filter">
<div class="form-group col-xs-3">
<label for="section">Section</label>
<select class="form-control" id="section" name="section">
<option value="all">All</option>
<?php
$sql_section = "SELECT DISTINCT section FROM resources ORDER BY section ASC";
$sections = $db->query($sql_section);
while($section = $sections->fetchArray(SQLITE3_ASSOC)){
echo '<option value="'.$section['section'].'"'.($section['section'] == $current_section ? ' selected' :'').'>'.$section['section'].'</option>'."\n";
}
?>
</select>
</div>
<div class="form-group col-xs-3">
<label for="category">Category</label>
<select class="form-control" id="category" name="category">
<option value="all">All</option>
<?php
$sql_categories = "SELECT DISTINCT category FROM resources WHERE category IS NOT NULL AND category != '' ORDER BY section ASC";
$categories = $db->query($sql_categories);
while($category = $categories->fetchArray(SQLITE3_ASSOC)){
echo '<option value="'.$category['category'].'"'.($category['category'] == $current_category ? ' selected' :'').'>'.$category['category'].'</option>'."\n";
}
?>
</select>
</div>
<div class="form-group col-xs-3">
<label for="topic">Topic</label>
<select class="form-control" id="topic" name="topic">
<option value="all">All</option>
<?php
$sql_topics = "SELECT DISTINCT topic FROM resources WHERE topic IS NOT NULL AND topic != '' ORDER BY category ASC";
$topics = $db->query($sql_topics);
while($topic = $topics->fetchArray(SQLITE3_ASSOC)){
echo '<option value="'.$topic['topic'].'" '.($topic['topic'] == $current_topic ? ' selected' :'').'>'.$topic['topic'].'</option>'."\n";
}
?>
</select>
</div>
<div class="form-group col-xs-2">
<label for="items_to_show">Links/Page</label>
<!-- <input type"number" class="form-control" id="items_to_show" name="items_to_show" placeholder="Links per page"> -->
<select class="form-control" id="items_to_show" name="items_to_show">
<option value="10" <?php echo ($items_to_show == 10 ? 'selected="selected"' : '')?> >10</option>
<option value="25" <?php echo ($items_to_show == 25 ? 'selected="selected"' : '')?> >25</option>
<option value="50" <?php echo ($items_to_show == 50 ? 'selected="selected"' : '')?> >50</option>
<option value="75" <?php echo ($items_to_show == 75 ? 'selected="selected"' : '')?> >75</option>
<option value="100" <?php echo ($items_to_show == 100 ? 'selected="selected"' : '')?> >100</option>
</select>
</div>
<button type="submit" class="btn btn-default">Submit</button>
</form>
<a href="add.php" class="btn btn-primary" role="button">Add New Link</a>
<hr>
<?php
while($row = $result->fetchArray(SQLITE3_ASSOC) ){
echo '<table class="table table-bordered table-condensed" id="'.$row['ID'].'">'."\n";
echo '<tr>'."\n";
echo ' <th colspan="2" >'.$row['section'].($row['category'] !=NULL? ' / '.$row['category'] : '').($row['topic'] !=NULL? ' / '.$row['topic'] : '').'<span class="buttons"><a href="edit.php?id='.$row['ID'].'" class="btn btn-warning btn-xs" role="button">Edit</a> <a href="delete.php?id='.$row['ID'].'" class="btn btn-danger btn-xs" role="button">Delete</a></span></th>'."\n";
echo '<tr>'."\n";
echo ' <td style="width: 60%;"><h5><small>Title</small></h5>'.($row['title'] !=NULL? $row['title'] : 'None').'</td>'."\n";
echo ' <td style="width: 40%;"><h5><small>Organization</small></h5>'.($row['organization'] !=NULL? $row['organization'] : 'None').'</td>'."\n";
echo '</tr>'."\n";
echo '<tr>'."\n";
echo ' <td><h5><small>URL</small></h5>'.($row['link'] !=NULL? htmlentities($row['link'], ENT_QUOTES) : 'None').'</td>'."\n";
echo ' <td><h5><small>Phone</small></h5>'.($row['phone'] !=NULL? $row['phone'] : 'None').'</td>'."\n";
echo '</tr>'."\n";
echo '<tr>'."\n";
echo ' <td colspan="2"><h5><small>Description</small></h5>'.($row['description'] !=NULL? $row['description'] : 'None').'</td>'."\n";
echo '</tr>'."\n";
echo '<tr>'."\n";
echo ' <td><h5><small>Documents</small></h5>'."\n";
if($row['document one title'] != NULL || $row['document one link'] != NULL){
echo '<div>'.$row['document one title'].' - '.$row['document one link'].'</div>'."\n";
}
if($row['document two title'] != NULL || $row['document two link'] != NULL){
echo '<div>'.$row['document two title'].' - '.$row['document two link'].'</div>'."\n";
}
if($row['document three title'] != NULL || $row['document three link'] != NULL){
echo '<div>'.$row['document three title'].' - '.$row['document three link'].'</div>'."\n";
}
if($row['document one title'] == NULL && $row['document one link'] == NULL && $row['document two title'] == NULL && $row['document two link'] == NULL && $row['document three title'] == NULL && $row['document three link'] == NULL){
echo '<div>None</div>'."\n";
}
echo '</td>'."\n";
echo ' <td><h5><small>Dates</small></h5>Created:'.date("y-m-d h:i:s A", $row['created']).'<br>Modified:'.date("y-m-d h:i:s A", $row['modified']).'</td>'."\n";
echo '</tr>'."\n";
echo '</table>'."\n";
}
echo '<ul class="pagination">'."\n";
echo $prevlink;
$range_min = ($maxlinks % 2 == 0) ? ($maxlinks /2)-1 : ($maxlinks-1)/2;
$range_max = ($maxlinks % 2 == 0) ? $range_min + 1 : $range_min;
$page_min = $page - $range_min;
$page_max = $page + $range_max;
$page_min = ($page_min < 1) ? 1 : $page_min;
$page_max = ($page_max < ($page_min + $maxlinks - 1)) ? $page_min + $maxlinks - 1 : $page_max;
if ($page_max > $pages) {
$page_min = ($page_min > 1) ? $pages - $maxlinks + 1 : 1;
$page_max = $pages;
}
$page_min = ($page_min < 1) ? 1 : $page_min;
for($i=$page_min; $i <= $page_max; $i++ ){
if ($i == $page){
echo '<li class="active" ><span>'.$page.' of '.$pages.'</span></li>'."\n";
}else{
echo '<li><a href="?page='.$i.(isset($_POST['section']) ? '§ion='.$current_section :'').'" title="Page #'.$i.'">'.$i.'</a></li>'."\n";
}
}
echo $nextlink.' </ul>'."\n";
$db->close();
} catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
}
?>
</article>
</section><!-- #middle-->
</div><!-- #wrapper -->
<?php require_once $_SERVER['DOCUMENT_ROOT'].'/require/footer.php';?>