232 lines
9.5 KiB
PHP
232 lines
9.5 KiB
PHP
<?php
|
|
include 'connect/cms-config.php' ;
|
|
include 'requires/function.php' ;
|
|
$user_permission_check = 'all-permission' ;
|
|
include 'requires/session.php' ;
|
|
|
|
// keep parameter in value
|
|
$page = escapeString($_GET['page']) ;
|
|
$type = escapeString($_GET['type']) ;
|
|
|
|
// active page
|
|
$active_main_menu = 'dashboard' ;
|
|
$active_menu = 'dashboard' ;
|
|
|
|
// check permission
|
|
if ( !permissionCheck($row_user, 'dashboard-view') ){
|
|
// echo '<script>alert("Sorry You Don\'t Have The Permission.")</script>';
|
|
|
|
header('Location: page-logout.php') ;
|
|
exit ;
|
|
}
|
|
|
|
// check query exsits
|
|
$mysqli_page = $mysqli->query("SELECT post_id, post_title, post_content FROM system_post
|
|
WHERE post_type = 'page-dashboard' AND post_categories = 'page-dashboard' AND post_trash = '0' LIMIT 1") ;
|
|
|
|
//pagination
|
|
if (isset($page) && !empty($page)) { $product_page = $page ; } else { $product_page = 1 ; } // next and prev page (5 thing need to change)
|
|
|
|
$limit_dash = 20;
|
|
|
|
$start_from = ($product_page - 1) * $limit_dash ; //end next and prev page
|
|
|
|
$search_name = escapeString($_GET['search_name']);
|
|
$search_idno = escapeString($_GET['search_idno']);
|
|
$export_excel = escapeString($_GET['export-excel']);
|
|
|
|
$search_query = '' ;
|
|
|
|
if( $search_name != ''){
|
|
|
|
$search_query .= " AND b.staff_name LIKE '%".$search_name."%'" ;
|
|
|
|
}
|
|
|
|
if( $search_idno != ''){
|
|
|
|
$search_query .= " AND b.staff_idno LIKE '%".$search_idno."%'" ;
|
|
|
|
}
|
|
|
|
|
|
$mysqli_achievement = $mysqli->query("SELECT a.*, b.staff_name, b.staff_point, b.staff_idno FROM staff_monthly_achievement a
|
|
LEFT JOIN staff b ON ( a.staff_id = b.staff_id )
|
|
WHERE a.staff_id != '' AND Year(a.created_at) = Year(Now()) AND MONTH(a.created_at) = month(Now()) ".$search_query." ".$user_branch_permission_sql_b." GROUP BY a.staff_id ORDER BY b.staff_point DESC LIMIT $start_from, ".$limit_dash) ;
|
|
|
|
$mysqli_achievement_query = "SELECT a.*, b.staff_name, b.staff_point, b.staff_idno FROM staff_monthly_achievement a LEFT JOIN staff b ON ( a.staff_id = b.staff_id ) WHERE a.staff_id != '' AND Year(a.created_at) = Year(Now()) AND MONTH(a.created_at) = month(Now()) ".$search_query." ".$user_branch_permission_sql_b." GROUP BY a.staff_id ORDER BY b.staff_point DESC";
|
|
|
|
|
|
if ( $export_excel == 'export_eae' ){
|
|
|
|
$page_export_file_name = 'Monthly Achivement-';
|
|
|
|
$t_title_header_excel = 'Monthly Achivement Report ';
|
|
|
|
$array_header_excel = array(
|
|
'ID',
|
|
'Name',
|
|
'Monthly Point Achievement',
|
|
'Current Point',
|
|
'Redeemed Point',
|
|
'Total Accumulated Point',
|
|
'Star',
|
|
'Achievement'
|
|
) ;
|
|
|
|
$mysqli_export = $mysqli->query( $mysqli_achievement_query ) ;
|
|
|
|
if ( $mysqli_export->num_rows > 0 ){
|
|
while ( $mysqli_export_page = $mysqli_export->fetch_assoc() ){
|
|
|
|
$mysqli_accumulated_point = $mysqli->query("SELECT SUM(amount) AS total_accumulated FROM staff_point_movement WHERE staff_id = '".$mysqli_export_page['staff_id']."' AND from_table NOT IN ('redeem','staff_point_movement_cutoff') AND Year(created_at) = Year(Now())");
|
|
|
|
if ( $mysqli_accumulated_point->num_rows > 0 ){
|
|
$row_accumulated = $mysqli_accumulated_point->fetch_array(MYSQLI_ASSOC);
|
|
}
|
|
|
|
|
|
$mysqli_total_redeem = $mysqli->query("SELECT SUM(amount) AS total_redeem FROM staff_point_movement WHERE staff_id = '".$mysqli_export_page['staff_id']."' AND from_table = 'redeem' AND Year(created_at) = Year(Now())");
|
|
|
|
if ( $mysqli_total_redeem->num_rows > 0 ){
|
|
$row_redeem = $mysqli_total_redeem->fetch_array(MYSQLI_ASSOC);
|
|
}
|
|
|
|
$current_point = $row_accumulated['total_accumulated'] + $row_redeem['total_redeem'];
|
|
$current_point = number_format($current_point, 2, '.', '');
|
|
|
|
$array_body_excel[] = array(
|
|
$mysqli_export_page['staff_idno'],
|
|
$mysqli_export_page['staff_name'],
|
|
$mysqli_export_page['staff_point_achievement'],
|
|
$current_point,
|
|
($row_redeem['total_redeem'] != '' ? $row_redeem['total_redeem'] : '0.00' ),
|
|
($row_accumulated['total_accumulated'] != '' ? $row_accumulated['total_accumulated'] : '0.00' ),
|
|
$mysqli_export_page['staff_star'],
|
|
$mysqli_export_page['staff_achievement']
|
|
) ;
|
|
}
|
|
}
|
|
|
|
include 'export_excel_default.php';
|
|
|
|
}
|
|
|
|
// start header here
|
|
include 'requires/page_header.php' ;
|
|
// include 'requires/page_top.php' ;
|
|
|
|
$search_url = 'page_mode=all&search_name='.$search_name.'&search_idno='.$search_idno ;
|
|
|
|
$page_pagination = nextPrevious($product_page, $limit_dash, $search_url, $mysqli_achievement_query) ;
|
|
|
|
?>
|
|
|
|
<!-- preloader -->
|
|
<div class="loading-container"><div class="loading"><div class="l1"><div></div></div><div class="l2"><div></div></div><div class="l3"><div></div></div><div class="l4"><div></div></div></div></div>
|
|
<!-- end preloader -->
|
|
|
|
<div class="panel panel-default" id="basic-table-title">
|
|
<div class="panel-heading" style="text-align:right">
|
|
<a href="page-dashboard-iframe.php?search_name=<?= $search_name ?>&search_idno=<?= $search_idno ?>&export-excel=export_eae">Export As Excel</a>
|
|
</div>
|
|
<div class="panel-body">
|
|
<form method="get" class="form-horizontal" style="max-width:600px;">
|
|
<div class="form-group">
|
|
<label class="col-sm-1 control-label"><?= $lang['Name'] ?></label>
|
|
<div class="col-sm-9">
|
|
<input type="text" name="search_name" value="<?= $search_name ?>" class="form-control" />
|
|
</div>
|
|
</div>
|
|
<div class="form-group">
|
|
<label class="col-sm-1 control-label">ID</label>
|
|
<div class="col-sm-9">
|
|
<input type="text" name="search_idno" value="<?= $search_idno ?>" class="form-control" />
|
|
</div>
|
|
</div>
|
|
<div class="form-group">
|
|
<div class="col-sm-10">
|
|
<button type="submit" class="btn btn-purple" style="float:right"><?= $lang['submit'] ?></button>
|
|
</div>
|
|
</div>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="panel panel-default">
|
|
<div class="panel-heading"></div>
|
|
<div class="panel-body">
|
|
<table cellpadding="0" cellspacing="0" border="0" class="responsive table table-striped table-bordered" id="basic-datatable">
|
|
<thead>
|
|
<tr>
|
|
<th>ID</th>
|
|
<th>Name</th>
|
|
<th>Monthly Point Achievement</th>
|
|
<th>Current Point</th>
|
|
<th>Redeemed Point</th>
|
|
<th>Total Accumulated Point</th>
|
|
<th>Star</th>
|
|
<th>Achievement</th>
|
|
<!-- <th>Created At</th> -->
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php
|
|
if ( $mysqli_achievement->num_rows > 0 ){
|
|
while ( $row_achievement = $mysqli_achievement->fetch_array(MYSQLI_ASSOC) ){
|
|
echo '
|
|
<tr class="odd gradeX">
|
|
<td class="text-center">'.dataFilter($row_achievement['staff_idno']).'</td>
|
|
<td class="text-center">'.dataFilter($row_achievement['staff_name']).'</td>
|
|
<td class="text-center">'.dataFilter($row_achievement['staff_point_achievement']).'</td>';
|
|
|
|
|
|
$mysqli_accumulated_point = $mysqli->query("SELECT SUM(amount) AS total_accumulated FROM staff_point_movement WHERE staff_id = '".$row_achievement['staff_id']."' AND from_table NOT IN ('redeem','staff_point_movement_cutoff') AND Year(created_at) = Year(Now())");
|
|
|
|
if ( $mysqli_accumulated_point->num_rows > 0 ){
|
|
$row_accumulated = $mysqli_accumulated_point->fetch_array(MYSQLI_ASSOC);
|
|
}
|
|
|
|
|
|
$mysqli_total_redeem = $mysqli->query("SELECT SUM(amount) AS total_redeem FROM staff_point_movement WHERE staff_id = '".$row_achievement['staff_id']."' AND from_table = 'redeem' AND Year(created_at) = Year(Now())");
|
|
|
|
if ( $mysqli_total_redeem->num_rows > 0 ){
|
|
$row_redeem = $mysqli_total_redeem->fetch_array(MYSQLI_ASSOC);
|
|
}
|
|
|
|
$current_point = $row_accumulated['total_accumulated'] + $row_redeem['total_redeem'];
|
|
$current_point = number_format($current_point, 2, '.', '');
|
|
|
|
echo '
|
|
<td class="text-center">'.dataFilter($current_point).'</td>
|
|
<td class="text-center">'.($row_redeem['total_redeem'] != '' ?dataFilter($row_redeem['total_redeem']) : '0.00' ).'</td>
|
|
<td class="text-center">'.($row_accumulated['total_accumulated'] != '' ?dataFilter($row_accumulated['total_accumulated']) : '0.00' ).'</td>';
|
|
|
|
// $mysqli_current_point
|
|
|
|
echo '
|
|
<td class="text-center">'.dataFilter($row_achievement['staff_star']).'</td>
|
|
<td class="text-center">'.dataFilter($row_achievement['staff_achievement']).'</td>
|
|
<!---<td class="text-center">'.resetDateFormat($row_achievement['created_at']).'</td>--->
|
|
</tr>';
|
|
}
|
|
}else{
|
|
echo '
|
|
<tr class="odd gradeX">
|
|
<td class="border_none">'.$lang['no_data'].'</td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
<td class="border_none"></td>
|
|
</tr>' ;
|
|
}
|
|
?>
|
|
</tbody>
|
|
</table>
|
|
<?= $page_pagination['page_pagination'] ?>
|
|
</div>
|
|
|