<?php
if($opt1 && ($opt1 === 'add' OR $opt1 === 'edit')):
	if($opt1 === 'edit'):
		$stmt = $mysqli->prepare("SELECT $firstcol, category, subcategory, name, degree, post, content, email, emailshow, phone, phoneshow, picture, pictureshow, catsortnumber, sortnumber, status, creation FROM $table WHERE $firstcol = ? LIMIT 1") or die($mysqli->error);
		$stmt->bind_param('i', $opt2);
		$stmt->execute();
		$stmt->store_result();
		$count_rows = $stmt->num_rows();
		$stmt->bind_result($id, $category, $subcategory, $name, $degree, $post, $content, $email, $emailshow, $phone, $phoneshow, $picture, $pictureshow, $catsortnumber, $sortnumber, $status, $creation);
		$stmt->fetch();
		$stmt->close();
		if($count_rows !== 1):
			die('Invalid data selection.');
		endif;

		$name	= html_entity_decode($name);
		$degree = html_entity_decode($degree);
		$post	= html_entity_decode($post);
		$content= html_entity_decode($content);
	else:
		$id = '';$category = '';$subcategory = '';$name = '';$degree = '';$post = '';$content = '';$email = '';$emailshow = 'Y';$phone = '';$phoneshow = 'Y';$picture = '';$pictureshow = 'Y';$catsortnumber = '';$sortnumber = '';$status = 'enabled';
	endif;

	function categories()
	{
		global $mysqli, $table;$html = '';

		$stmt = $mysqli->prepare("SELECT TRIM(category) FROM $table GROUP BY TRIM(category) ORDER BY TRIM(category) ASC") or die($mysqli->error);
		$stmt->execute();
		$stmt->store_result();
		$stmt->bind_result($category);
		while($stmt->fetch()):
			$html .= $category ? '<option value="'.$category.'">'.$category.'</option>' : '';
		endwhile;
		$stmt->close();
		return $html;
	}
	$categories = categories();
	function subcategories()
	{
		global $mysqli, $table;$html = '';

		$stmt = $mysqli->prepare("SELECT TRIM(subcategory) FROM $table GROUP BY TRIM(subcategory) ORDER BY TRIM(subcategory) ASC") or die($mysqli->error);
		$stmt->execute();
		$stmt->store_result();
		$stmt->bind_result($subcategory);
		while($stmt->fetch()):
			$html .= $subcategory ? '<option value="'.$subcategory.'">'.$subcategory.'</option>' : '';
		endwhile;
		$stmt->close();
		return $html;
	}
	$subcategories = subcategories();
	$emailchecked  = $emailshow === 'Y' ? 'checked' : '';
	$phonechecked  = $phoneshow === 'Y' ? 'checked' : '';
	$edpicchecked  = $pictureshow === 'Y' ? 'checked' : '';
	$checked	   = $opt1 === 'add' ? 'checked' : '';
	$status		   = $status === 'enabled' ? 'checked' : '';
echo <<<EOJ
	<input type="hidden" name="dataid" value="{$id}" readonly />
	<div class="head white">
		<button type="button" class="waves-effect btn-flat" onclick="history.back()"><i class="material-icons">keyboard_return</i></button>
		<button type="submit" class="waves-effect btn-flat blue accent-3">submit</button>
	</div>
	<div class="body white">
		<div class="row">
			<div class="col s12 m3 l3">
				<label for="category">category<b class="red-text right">*</b></label>
				<input type="text" name="category" value="{$category}" id="category" />
			</div>
			<div class="col s12 m3 l3">
				<label for="categorylist">category list</label>
				<select name="categorylist" id="categorylist" class="browser-default" onchange="this.form.category.value=this.value;">
					<option value="" selected>Select category</potion>
					{$categories}
				</select>
			</div>
			<div class="col s12 m3 l3">
				<label for="subcategory">sub category</label>
				<input type="text" name="subcategory" value="{$subcategory}" id="subcategory" />
			</div>
			<div class="col s12 m3 l3">
				<label for="subcategorylist">sub category list</label>
				<select name="subcategorylist" id="subcategorylist" class="browser-default" onchange="this.form.subcategory.value=this.value;">
					<option value="" selected>Select sub category</potion>
					{$subcategories}
				</select>
			</div>
		</div>
		<div class="row">
			<div class="col s12 m4 l4">
				<label for="name">editor name<b class="red-text right">*</b></label>
				<input type="text" name="name" value="{$name}" id="name" />
			</div>
			<div class="col s12 m4 l4">
				<label for="degree">degree</label>
				<input type="text" name="degree" value="{$degree}" id="degree" />
			</div>
			<div class="col s12 m4 l4">
				<label for="post">post</label>
				<input type="text" name="post" value="{$post}" id="post" />
			</div>
		</div>
		<div class="row">
			<div class="col s12 m12 l12">
				<label for="content">content<b class="red-text right">*</b></label>
				<input type="text" name="content" value="{$content}" id="content" />
			</div>
		</div>
		<div class="row">
			<div class="col s12 m12 l6">
				<label for="email">email</label>
				<div class="flex">
					<input type="text" name="email" value="{$email}" id="email" />
					<div class="chklab right">
						<label for="emailshow"><input type="checkbox" name="emailshow" value="Y" id="emailshow" {$emailchecked} />show</label>
					</div>
				</div>
			</div>
			<div class="col s12 m12 l6">
				<label for="phone">phone</label>
				<div class="flex">
					<input type="text" name="phone" value="{$phone}" class="phone" id="phone" />
					<div class="chklab right">
						<label for="phoneshow"><input type="checkbox" name="phoneshow" value="Y" id="phoneshow" {$phonechecked} />show</label>
					</div>
				</div>
			</div>
		</div>
		<div class="row">
			<div class="col s12 m12 l6">
				<div class="file-field" style="margin-top:16px">
					<div class="btn btn-flat grey lighten-1 white-text">
						<span>picture</span>
						<input type="file" name="picture" />
					</div>
					<div class="file-path-wrapper">
						<input type="text" placeholder="Only JPG, JPEG and PNG allowed." class="file-path" />
					</div>
				</div>
			</div>
			<div class="col s12 m3 l3">
				<label for="catsortnumber">category sort number<b class="red-text right">*</b></label>
				<input type="text" name="catsortnumber" value="{$catsortnumber}" maxlength="6" id="catsortnumber" class="int" onpaste="return false" />
			</div>
			<div class="col s12 m3 l3">
				<label for="sortnumber">sort number</label>
				<input type="text" name="sortnumber" value="{$sortnumber}" maxlength="6" id="sortnumber" class="int" onpaste="return false" />
			</div>
		</div>
		<div class="row" style="margin-top:10px">
			<div class="col s12 m12 l12">
				<div class="chklab left">
					<label for="applytoall"><input type="checkbox" name="applytoall" value="Y" id="applytoall" />apply to all</label>
				</div>
				<div class="chklab left">
					<label for="pictureshow"><input type="checkbox" name="pictureshow" value="Y" id="pictureshow" {$edpicchecked} />picture show</label>
				</div>
				<div class="chklab left">
					<label for="sendemail"><input type="checkbox" name="sendemail" value="Y" id="sendemail" {$checked} />send email</label>
				</div>
				<div class="chklab left">
					<label for="certificate"><input type="checkbox" name="certificate" value="Y" id="certificate" onclick="if(this.checked)this.form.sendemail.checked=true;" {$checked} />send certificate</label>
				</div>
				<div class="chklab left">
					<label for="sendsms"><input type="checkbox" name="sendsms" value="Y" id="sendsms" {$sendsms} />send sms</label>
				</div>
				<div class="chklab left">
					<label for="status"><input type="checkbox" name="status" value="enabled" id="status" {$status} />status</label>
				</div>
			</div>
		</div>
	</div>
EOJ;
else:
	$keyword = htmlentities("%$keyword%");
	if($opt1 === 'search'):
		$stmt = $mysqli->prepare("SELECT * FROM $table WHERE (name LIKE ? OR degree LIKE ? OR post LIKE ? OR content LIKE ? OR email LIKE ? OR phone LIKE ?)") or die($mysqli->error);
		$stmt->bind_param('ssssss', $keyword, $keyword, $keyword, $keyword, $keyword, $keyword);
		$stmt->execute();
		$stmt->store_result();
		$total_rows = $stmt->num_rows();
		$stmt->close();
	else:
		$stmt = $mysqli->prepare("SELECT * FROM $table") or die($mysqli->error);
		$stmt->execute();
		$stmt->store_result();
		$total_rows = $stmt->num_rows();
		$stmt->close();
	endif;
echo <<<EOJ
	<div class="head white">
		<button type="button" class="waves-effect btn-flat green" onclick="cURL('add')">add new</button>
		<button type="submit" class="waves-effect btn-flat" onclick="sOPT('downloademails', '')"><i class="material-icons left">file_download</i>emails</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('certificate', '')"><i class="material-icons left">file_download</i>certificate</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('enabled', '')">enable</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('disabled', '')">disable</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('showpicture', '')"><i class="material-icons left">visibility</i>picture</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('hidepicture', '')"><i class="material-icons left">visibility_off</i>picture</button>
		<button type="submit" class="waves-effect btn-flat h" onclick="sOPT('delete', 'Do you really want to delete?')"><i class="material-icons">delete</i></button>
		<div class="pagination right"><b class="left">Total Rows : {$total_rows}</b></div>
	</div>
	<table class="table">
		<thead>
		<tr>
		<th class="xs center-align"><input type="checkbox" name="checkall" id="checkall" value="" /></th>
		<th class="xs center-align">s. no.</th>
		<th class="xs center-align">picture</th>
		<th>category</th>
		<th>name</th>
		<th>email</th>
		<th>post</th>
		<th class="center-align" nowrap>c. sort</th>
		<th class="xs center-align">sort</th>
		<th class="sm center-align">status</th>
		<th class="lg right-align">creation</th>
		<th class="xs center-align">action</th>
		</tr>
		</thead>
		<tbody>
EOJ;
		if($opt1 === 'search'):
			$stmt = $mysqli->prepare("SELECT $firstcol, category, name, email, degree, post, content, picture, catsortnumber, sortnumber, status, creation FROM $table WHERE (name LIKE ? OR degree LIKE ? OR post LIKE ? OR content LIKE ? OR email LIKE ? OR phone LIKE ?) ORDER BY catsortnumber ASC, sortnumber ASC") or die($mysqli->error);
			$stmt->bind_param('ssssss', $keyword, $keyword, $keyword, $keyword, $keyword, $keyword);
		else:
			$stmt = $mysqli->prepare("SELECT $firstcol, category, name, email, degree, post, content, picture, catsortnumber, sortnumber, status, creation FROM $table ORDER BY catsortnumber ASC, sortnumber ASC") or die($mysqli->error);
		endif;
		$stmt->execute();
		$stmt->store_result();
		if($stmt->num_rows() > 0):
			$stmt->bind_result($id, $category, $name, $email, $degree, $post, $content, $picture, $catsortnumber, $sortnumber, $status, $creation);
			while($stmt->fetch()):
				$snum = $snum + 1;
				$name = html_entity_decode($name);
				$degree = html_entity_decode($degree);
				$post = html_entity_decode($post);
				$content = html_entity_decode($content);
				if($picture && file_exists($uploadpath.$page.'/'.$picture)):
					$picstring = $uploadurl.$page.'/'.$picture;
					$picbutton = '<button type="submit" class="waves-effect btn-flat" onclick="sOPT(\'removepicture\', \'Do you really want to remove picture?\', this)"><i class="material-icons white-text" style="font-size:24px">remove_circle</i></button>';
				else:
					$picstring = $imageurl.'avatar_2x.png';
					$picbutton = '';
				endif;
				$statuscolor = $status === 'enabled' ? 'green-text' : 'red-text';
				$option = $status === 'enabled' ? 'disabled' : 'enabled';

				$mailbutton = $email ? '<button type="button" onclick="compose(\'.emf\', \''.$email.'\')">mail</button>' : '';
echo <<<EOJ
				<tr>
				<td class="center-align"><input type="checkbox" name="dataid[]" value="{$id}" class="checkbox" /></td>
				<td class="center-align">{$snum}</td>
				<td class="center-align"><div class="img-wrap"><img src="{$picstring}" alt="{$name}" class="circle" />{$picbutton}</div></td>
				<td>{$category}</td>
				<td>{$name}</td>
				<td>{$email}</td>
				<td>{$post}</td>
				<td class="center-align">{$catsortnumber}</td>
				<td class="center-align">{$sortnumber}</td>
				<td class="center-align"><button type="submit" class="{$statuscolor}" onclick="sOPT('{$option}', '', this)">{$status}</button></td>
				<td class="right-align">{$fn->nice_date($creation)}</td>
				<td class="center-align">
					{$mailbutton}
					<button type="button" class="blue-text text-accent-3" onclick="cURL('edit/{$id}')">edit</button>
				</td>
				</tr>
EOJ;
			endwhile;
		else:
			echo '<tr><td colspan="12" class="center-align">MySQL returned an empty result set (i.e. zero rows).</td></tr>';
		endif;
		$stmt->close();
	echo '</tbody>';
	echo '</table>';
endif;
?>