<?php
$must_login            = true ;
$require_path          = '../../../' ;
$require_sub           = '../../' ;
require( $require_sub.'header.php' ) ;

if ( $boolean_login ){
	$status   		   = '201' ;


    // select all department
    $department_list   = [] ;
    $select            = $mysqli->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 = '".$array['lang']."'" ) ;
    if ( $select->num_rows > 0 ){
        while ( $row   = $select->fetch_assoc() ){
            $department_list[$row['department_id']] = $row['department_desc'] ;
        }
    }


	$search_query      = '' ;
	if ( $array['search'] != '' ){
        $search_query .= " AND title LIKE '%".$array['search']."%'" ;
    }

    $query             = "SELECT suggestion_id, staff_id, title, status, created_at FROM suggestion
                          WHERE deleted_at IS NULL AND branch_id = '".$array['branch_id']."' AND staff_id = '".$staff_info['staff_id']."' " . $search_query ;
    $mysqli_query      = $mysqli->query( $query . " ORDER BY created_at DESC LIMIT " . getLimit( $current ) ) ;

    if ( $mysqli_query->num_rows > 0 ){

        $status        = '200' ;

        $list          = [] ;
        $staff_list    = [] ;
        $suggestion_id = [] ;
        while ( $row   = $mysqli_query->fetch_assoc() ){
            $row['id']          = dataFilter( $row['suggestion_id'] ) ;
            $row['title']       = dataFilter( $row['title'] ) ;
			$row['departments'] = '' ;
            $row['created_at']  = resetDateFormat( $row['created_at'] ) ;
            $list[]             = $row ;

            $staff_list[$row['staff_id']]         = $row['staff_id'] ; 
            $suggestion_id[$row['suggestion_id']] = $row['suggestion_id'] ;
        }

        // select related suggestion media
        $media_list     = [] ;
        $select_media   = $mysqli->query( "SELECT suggestion_id, file FROM suggestion_media a
                                           WHERE a.deleted_at IS NULL AND suggestion_id IN ( ".implode( ',', $suggestion_id )." ) AND filetype IN ( 'jpg', 'png', 'gif' )" ) ;
        if ( $select_media->num_rows > 0 ){
            while ( $row_media   = $select_media->fetch_assoc() ){
                $media_list[$row_media['suggestion_id']][] = ( $row_media['file'] != '' ? PATH.'uploads/Suggestion/b/'.$row_media['file'] : '' ) ;
            }
        }

        // select all staff related deparment
        $related_list   = [] ;
        $select_related = $mysqli->query( "SELECT staff_id, department_id FROM staff_department a
                                           WHERE a.deleted_at IS NULL AND staff_id IN ( ".implode( ',', $staff_list )." )" ) ;
        if ( $select_related->num_rows > 0 ){
            while ( $row_related   = $select_related->fetch_assoc() ){
                if ( checkExists( $department_list[$row_related['department_id']] ) ){
                    $related_list[$row_related['staff_id']][] = $department_list[$row_related['department_id']] ;
                }
            }
        }

        foreach ( $list as $k => $v ){
            $list[$k]['departments'] = ( checkExists( $related_list[$v['staff_id']] ) ? implode( ', ', $related_list[$v['staff_id']] ) : '' ) ;
            $list[$k]['files']       = ( checkExists( $media_list[$v['suggestion_id']] ) ? $media_list[$v['suggestion_id']] : [] ) ;
        }

        $data['list']  = $list ;

    }
}

require( $require_sub.'footer.php' ) ;
?>