query( "SELECT a.department_id, b.department_desc FROM setting_department a
LEFT JOIN setting_department_translation b ON ( a.department_id = b.department_id )
WHERE a.deleted_at IS NULL AND b.lang = 'en'" ) ;
if ( $select_departments->num_rows > 0 ){
while ( $row_department = $select_departments->fetch_assoc() ){
$department_lists[$row_department['department_id']] = dataFilter( $row_department['department_desc'] ) ;
}
}
// staff department
$staffdeparments = [] ;
$select_staffdeparments = $mysqli->query( "SELECT a.staff_id, a.department_id FROM staff_department
a
LEFT JOIN staff b ON ( a.staff_id = b.staff_id )
WHERE a.deleted_at IS NULL
GROUP BY a.staff_id" ) ;
if ( $select_staffdeparments->num_rows > 0 ){
while ( $row_staffdeparment = $select_staffdeparments->fetch_assoc() ){
$staffdeparments[$row_staffdeparment['staff_id']] = $row_staffdeparment['department_id'] ;
}
}
// redeeem category
$mains = [] ;
$mysqli_main = $mysqli->query("SELECT a.main_id, b.title FROM setting_request a
LEFT JOIN setting_request_translation b ON ( a.main_id = b.main_id )
WHERE a.deleted_at IS NULL AND b.lang = 'en' ORDER BY a.sortable") ;
if ( $mysqli_main->num_rows > 0 ){
while ( $row_main = $mysqli_main->fetch_assoc() ){
$mains[$row_main['main_id']] = $row_main['title'] ;
}
}
// plus minus report
$group_lists = [] ;
$split_departmerns = [] ;
$split_lists = [] ;
$split_grouplists = [] ;
$all_lists = [] ;
$plus_lists = [] ;
$minus_lists = [] ;
$select_request = $mysqli->query( "SELECT
SUM(a.quantity) as totalquantity,
a.staff_id,
a.main_id
FROM request a
WHERE a.deleted_at IS NULL AND a.created_at LIKE '%" . $date_time . "%'
AND a.status = 'confirmed'
" . $search_query . "
GROUP BY a.staff_id, a.main_id" ) ;
if ( $select_request->num_rows > 0 ){
while ( $row_request = $select_request->fetch_assoc() ){
$get_department = $staffdeparments[$row_request['staff_id']] ;
$main_id = $row_request['main_id'] ;
$group_lists[$get_department] = ( $group_lists[$get_department] + $row_request['totalquantity'] ) ;
$split_lists[$main_id][$get_department] = ( $split_lists[$main_id][$get_department] + $row_request['totalquantity'] ) ;
$split_departmerns[$get_department] = $get_department ;
}
}
foreach ( $group_lists as $k => $v ){
$get_departmentname = ( $k == '0' ? 'Cross Department' : $department_lists[$k] ) ;
$all_lists[] = [
'label' => $get_departmentname,
'y' => floatval( $v )
] ;
}
foreach ( $split_lists as $k_lists => $v_lists ){
$temps = [] ;
foreach ( $split_departmerns as $k_department => $v_department ){
$temps[] = [
'label' => ( $v_department == '0' ? 'Cross Department' : $department_lists[$v_department] ),
'y' => floatval( checkExists( $v_lists[$v_department], '0' ) )
] ;
}
$split_grouplists[] = [
'type' => 'bar',
'showInLegend' => true,
'name' => ucwords( $mains[$k_lists] ),
'dataPoints' => $temps
] ;
}
?>