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'] ) ;
}
}
// reject report
$reject_lists = [] ;
$select_task = $mysqli->query( "SELECT COUNT(a.department_id) as total, a.department_id FROM task a
WHERE a.deleted_at IS NULL AND a.status IN ( 'rejected', 'cancelled' ) AND a.updated_at LIKE '%" . $date_time . "%' " . $search_query . "
GROUP BY a.department_id" ) ;
if ( $select_task->num_rows > 0 ){
while ( $row_task = $select_task->fetch_assoc() ){
$reject_lists[] = [
'label' => ( $row_task['department_id'] == '0' ? 'Cross Department' : $department_lists[$row_task['department_id']] ),
'y' => floatval( $row_task['total'] )
] ;
}
}
// complete report by difficulty
$difficulty_departments = [] ;
$difficulty_lists = [] ;
$difficulty_alllists = [] ;
$select_task = $mysqli->query( "SELECT COUNT(a.difficulty) as total, a.department_id, a.difficulty FROM task a
WHERE a.deleted_at IS NULL AND a.status IN ( 'approved' ) AND a.confirmed_at LIKE '%" . $date_time . "%' " . $search_query . "
GROUP BY a.department_id, a.difficulty" ) ;
if ( $select_task->num_rows > 0 ){
while ( $row_task = $select_task->fetch_assoc() ){
$difficulty_lists[$row_task['difficulty']][$row_task['department_id']]= $row_task['total'] ;
$difficulty_departments[$row_task['department_id']] = $row_task['department_id'] ;
}
}
foreach ( $difficulty_lists as $k_lists => $v_lists ){
$temps = [] ;
foreach ( $difficulty_departments as $k_department => $v_department ){
$temps[] = [
'label' => ( $v_department == '0' ? 'Cross Department' : $department_lists[$v_department] ),
'y' => floatval( checkExists( $v_lists[$v_department], '0' ) )
] ;
}
$difficulty_alllists[] = [
'type' => 'bar',
'showInLegend' => true,
'name' => ucwords( $k_lists ),
'dataPoints' => $temps
] ;
}
// complete report
$complete_lists = [] ;
$select_task = $mysqli->query( "SELECT COUNT(a.department_id) as total, a.department_id FROM task a
WHERE a.deleted_at IS NULL AND a.status IN ( 'approved' ) AND a.confirmed_at LIKE '%" . $date_time . "%' " . $search_query . "
GROUP BY a.department_id" ) ;
if ( $select_task->num_rows > 0 ){
while ( $row_task = $select_task->fetch_assoc() ){
$complete_lists[] = [
'label' => ( $row_task['department_id'] == '0' ? 'Cross Department' : $department_lists[$row_task['department_id']] ),
'y' => floatval( $row_task['total'] )
] ;
}
}
?>