File: /var/www/nclive/technc/nc_exam_entity_tracker_list.php
<?php
include 'config.php';
$getUsers="Select id, username from sp_admin_login where status='1'";
$allUsers = $db->prepare($getUsers);
$allUsers->execute();
$getExams="SELECT exam_id, exam_name from sp_exam where status='1' order by exam_name asc";
$allExams=$db->prepare($getExams);
$allExams->execute();
if(isset($_REQUEST['submit']))
{
if(isset($_REQUEST['search_type']))
{
$search_type = $_REQUEST['search_type'];
$search_base = $_REQUEST['search_base'];
$user = $_REQUEST['user'];
$date = date('Y-m-d',strtotime($_REQUEST['ondate']));
if($search_type=='1')
{
if($_REQUEST['search_base']=='1' && $user!='U')
{
$subquery = "where date(el.add_date)='$date' and el.add_by='$user'";
}
else if($_REQUEST['search_base']=='1' && $user=='U')
{
$subquery = "where date(el.add_date)='$date'";
}
else if($_REQUEST['search_base']=='2' && $user!='U')
{
$subquery = "where date(el.update_date)='$date' and el.update_by='$user'";
}
else if($_REQUEST['search_base']=='2' && $user=='U')
{
$subquery = "where date(el.update_date)='$date'";
}
else
{
$subquery = "";
}
}
else if($search_type=='2')
{
$user = $_REQUEST['user'];
$fromdate = date('Y-m-d',strtotime($_REQUEST['fromdate']));
$todate = date('Y-m-d',strtotime($_REQUEST['todate']));
if($_REQUEST['search_base']=='1' && $user!='U')
{
$subquery = "where date(el.add_date) >='$fromdate' AND date(el.add_date) <= '$todate' and el.add_by='$user'";
}
else if($_REQUEST['search_base']=='1' && $user=='U')
{
$subquery = "where date(el.add_date) >='$fromdate' AND date(el.add_date) <= '$todate'";
}
else if($_REQUEST['search_base']=='2' && $user!='U')
{
$subquery = "where date(el.update_date) >='$fromdate' AND date(el.update_date) <= '$todate' and el.update_by='$user'";
}
else if($_REQUEST['search_base']=='2' && $user=='U')
{
$subquery = "where date(el.update_date)='$date'";
}
else
{
$subquery = "";
}
}
else if($search_type=='3')
{
$user = $_REQUEST['user'];
if($_REQUEST['search_base']=='1' && $user!='U')
{
$subquery = "where el.add_by='$user'";
}
else if($_REQUEST['search_base']=='2' && $user!='U')
{
$subquery = "where el.update_by='$user'";
}
else
{
$subquery = "";
}
}
else if($search_type=='4')
{
$exam = $_REQUEST['exam'];
$subquery = "where el.exam_id='$exam'";
}
}
}
$getLinkQuery = "Select e.exam_id,e.exam_name,ee.name,el.display_text,el.links,el.date_type, el.date, el.add_date, el.update_date,el.status,al.username as addBy,alu.username as updateBy from sp_exam_links as el
left join sp_exam as e on e.exam_id=el.exam_id
left join sp_exam_entity as ee on ee.id=el.entity_id
left join sp_admin_login as al on al.id=el.add_by
left join sp_admin_login as alu on alu.id=el.update_by $subquery
order by el.add_date desc";
$getData=$db->prepare($getLinkQuery);
$getData->execute();
?>
<link rel="stylesheet" type="text/css" href="assets/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="assets/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/dataTables.bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/responsive.dataTables.min.css">
<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" charset="utf8" src="assets/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="utf8" src="assets/js/dataTables.responsive.min.js"></script>
<script>
jQuery.noConflict();
jQuery(function($) {
jQuery('#examData').dataTable({
"bPaginate": true,
bFilter:true,
"responsive": true,
"lengthMenu": [[50, 100, 150, 200, 1000], [50, 100, 150, 200, 1000]],
});
});
</script>
<table>
<form method="post">
<tr>
<td>Search Base :
<select name="search_base">
<option value="0">--Search on Basis--</option>
<option value="1">Add Date</option>
<option value="2">Update Date</option>
</select>
</td>
<td><input type="radio" name="search_type" value="4">Select Exam :
<select name="exam">
<option value="0">--Select Exam--</option>
<?php while($data=$allExams->fetch()){ ?>
<option value="<?php echo $data['exam_id']; ?>"><?php echo $data['exam_name']; ?></option>
<?php } ?>
</select>
</td>
<td><input type="radio" name="search_type" value="3"> Select Editor :
<select name="user">
<option value="U">--Select User--</option>
<?php while($data1=$allUsers->fetch()){ ?>
<option value="<?php echo $data1['id']; ?>"><?php echo $data1['username']; ?></option>
<?php } ?>
</select>
</td>
</tr>
<tr>
<td><input type="radio" name="search_type" value="1"> On Date :
<input class="form-control" type="date" name="ondate" value="" placeholder="On Date">
</td>
<td><input type="radio" name="search_type" value="2"> Between Date :
<input class="form-control" type="date" name="fromdate" value="">
<input class="form-control" type="date" name="todate" value="">
</td>
</tr>
<tr>
<td></td>
<td>
<button class="btn btn-sm btn-primary" type="submit" name="submit">Submit</button>
</td>
</tr>
</form>
</table>
<br>
<table id="examData" style="font-family: verdana;">
<thead style="background: #663399c7;">
<tr>
<th>#</th>
<th>Exam</th>
<th>Entity</th>
<th>Link date</th>
<th>Display Text</th>
<th>Link</th>
<th>AddBy</th>
<th>AddDate</th>
<th>UpdateBy</th>
<th>UpdateDate</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php $i=1; while($row=$getData->fetch())
{
$link = wordwrap($row['links'],40,"<br>\n");
$status = $row['status'];
$dateType = $row['date_type'];
$curDate = date('Y-m-d');
$date = $row['date'];
if($dateType=='N')
{
$Ndate = $date;
if($curDate<$Ndate)
{
$color = "#ff000047";
}
else
{
$color = "";
}
}
else if($dateType=='FT')
{
$dateArray = explode(",",$date);
$date1 = date('Y-m-d',strtotime($dateArray[0]));
$date2 = date('Y-m-d',strtotime($dateArray[1]));
if($curDate<$date1)
{
$color = "#ff000047";
}
else
{
$color = "";
}
}
if($status=='1')
{
$text = '<font style="color:green;">Active</font>';
}
else
{
$text = '<font style="color:red">Deactive</font>';
}
?>
<tr style="background: <?php echo $color; ?>;">
<td><?php echo $i; ?></td>
<td><?php echo $row['exam_name']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['date']; ?></td>
<td><?php echo $row['display_text']; ?></td>
<td><a href="<?php echo $link; ?>"><?php echo $link; ?></a></td>
<td><?php echo $row['addBy']; ?></td>
<td><?php echo $row['add_date']; ?></td>
<td><?php echo $row['updateBy']; ?></td>
<td><?php echo $row['update_date']; ?></td>
<td><?php echo $text; ?></td>
</tr>
<?php $i++; } ?>
</tbody>
</table>