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'] ;
}
}
// report
$group_lists = [] ;
$split_departmerns = [] ;
$split_lists = [] ;
$split_grouplists = [] ;
$all_lists = [] ;
$select_attendance = $mysqli->query( "SELECT
COUNT(a.staff_id) as total,
a.staff_id
FROM staff_attendance_list a
LEFT JOIN staff b ON ( a.staff_id = b.staff_id )
WHERE a.deleted_at IS NULL AND a.list_work_day > 0 AND a.list_date LIKE '%" . $date_time . "%'
" . str_replace( 'a.branch_id', 'b.branch_id', $search_query ) . "
GROUP BY a.staff_id" ) ;
if ( $select_attendance ->num_rows > 0 ){
while ( $row_attendance = $select_attendance->fetch_assoc() ){
$get_department = $staffdeparments[$row_attendance['staff_id']] ;
$type = $row_attendance['type'] ;
$group_lists[$get_department] = ( $group_lists[$get_department] + $row_attendance['total'] ) ;
$split_lists[$type][$get_department] = ( $split_lists[$type][$get_department] + $row_attendance['total'] ) ;
$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( $k_lists ),
'dataPoints' => $temps
] ;
}
?>