alert("Sorry You Don\'t Have The Permission.")'; header('Location: index.php') ; exit ; } $active_main_menu = 'import' ; $active_sub_menu = 'import-outstanding-employee' ; $active_menu = 'import-outstanding-employee' ; $staff_all = [] ; $mysqli_staff = $mysqli->query("SELECT staff_idno, staff_id, branch_id FROM staff WHERE deleted_at IS NULL" . $user_branch_permission_sql) ; if ( $mysqli_staff->num_rows > 0 ){ while($row_staff = $mysqli_staff->fetch_array(MYSQLI_ASSOC)){ $staff_all[$row_staff['staff_idno']] = $row_staff ; } } if(isset($_FILES['import-excel']['name'])){ $redirect_url = '' ; include 'PhpExcel/PHPExcel.php' ; $file_name = $_FILES['import-excel']['name']; $ext = pathinfo($file_name, PATHINFO_EXTENSION); //Checking the file extension if($ext == "xlsx"){ $file_name = $_FILES['import-excel']['tmp_name']; $inputFileName = $file_name; /**********************PHPExcel Script to Read Excel File**********************/ // Read your Excel workbook try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); //Identify the file $objReader = PHPExcel_IOFactory::createReader($inputFileType); //Creating the reader $objPHPExcel = $objReader->load($inputFileName); //Loading the file } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); header("location: ?result=error&msg=".urlencode($e->getMessage())); exit; } // ################################################################################## // Setting excel // ################################################################################## $sheet1 = $objPHPExcel->getSheet(0); //Selecting sheet 0 $highestRow1 = $sheet1->getHighestRow(); //Getting number of rows $highestColumn1 = $sheet1->getHighestColumn(); //Getting number of columns // Loop through each row of the worksheet in turn -> $row is for starting point for ( $row = 2; $row <= $highestRow1; $row++ ) { // Read a row of data into an array $rowData = $sheet1->rangeToArray('A' . $row . ':' . $highestColumn1 . $row, NULL, TRUE, FALSE); $rowData2[] = $rowData[0] ; } if( isset($rowData2) ){ $error_type = 0 ; $error_staff = 0 ; $update_list = [] ; foreach ( $rowData2 as $kk => $vv ) { if ( $vv[0] != '' ){ $get_staff = $staff_all[$vv[0]] ; if ( $get_staff != null && $get_staff['branch_id'] == $_SESSION['url_get_branch_admin'] ){ if ( $vv[1] == 'merit' || $vv[1] == 'passionate' ){ $update_list[] = [ 'staff_id' => $get_staff['staff_id'], 'type' => $vv[1], 'times' => $vv[2] ] ; }else{ $error_type++ ; } }else{ $error_staff++ ; } } } if ( $error_staff == 0 ){ if ( $error_type == 0 ){ $staff_ids = '' ; $related_staff = $mysqli->query( "SELECT GROUP_CONCAT( a.staff_id ) AS staff_ids FROM staff_attendance_summary a LEFT JOIN staff b ON ( a.staff_id = b.staff_id ) WHERE a.deleted_at IS NULL " . $user_branch_permission_sql_b ) ; if ( $related_staff->num_rows > 0 ){ $row_related = $related_staff->fetch_assoc() ; $staff_ids = $row_related['staff_ids'] ; } $mysqli->query( "UPDATE staff_attendance_summary SET deleted_at = '".TODAYDATE."' WHERE staff_id IN ( ".$staff_ids." ) AND type IN ( 'merit', 'passionate' )" ) ; foreach ( $update_list as $k => $v ){ $mysqli->query( "INSERT INTO staff_attendance_summary ( `staff_id`, `type`, `times`, `created_at`, `updated_at` ) VALUES ( '".$v['staff_id']."', '".$v['type']."', '".$v['times']."', '".TODAYDATE."', '".TODAYDATE."' )" ) ; } pushToBranchUser( [ $_SESSION['url_get_branch_admin'] ], [], 'outstanding-employee', '', 'Outstanding Employee Announced', 'The latest of outstanding employee has been announced' ) ; $redirect_url = '?result=success&msg=Import Successful' ; }else{ $redirect_url = '?result=error&msg=Some of type invalid' ; } }else{ $redirect_url = '?result=error&msg=Some of the Staff not exists or invalid branch' ; } }else{ $redirect_url = '?result=error&msg=Something got ERROR' ; } } header( "Location: " . $redirect_url ) ; exit ; } // form submit if ($_POST['hide'] == '1' && $_POST['hide_status'] == 'action'){ // trash item switch($_POST['page_action']){ case 'trash': $mysqli_query = "UPDATE staff_attendance_summary SET deleted_at = '".TODAYDATE."' WHERE attendance_summary_id = " ; $trash_page = trashPage('staff_attendance_summary', $mysqli, $mysqli_query, $_POST['multiple_trash']) ; break; } } $search_query = ''; // page query $mysqli_query = "SELECT a.attendance_summary_id, a.type, a.times, b.staff_idno FROM staff_attendance_summary a LEFT JOIN staff b ON (a.staff_id = b.staff_id) WHERE a.deleted_at IS NULL AND type IN ( 'merit', 'passionate' ) " . $search_query .$user_branch_permission_sql_b ; // pagination if (isset($page) && !empty($page)) { $product_page = $page ; } else { $product_page = 1 ; } // next and prev page (5 thing need to change) $start_from = ($product_page - 1) * LIMIT ; //end next and prev page // set search url $search_url = 'search='.$search.'&search_staff_idno='.$search_staff_idno.'&search_type='.$search_type.'&page_mode=staff-attendance' ; $mysqli_page = $mysqli->query($mysqli_query." LIMIT $start_from, " . LIMIT) ; // load pagination $page_pagination = nextPrevious($product_page, LIMIT, $search_url, $mysqli_query) ; $result = $_GET['result']; $msg = $_GET['msg']; if ($result == 'error') { $display_error = '

'.$msg.'

'; }elseif ($result == 'success') { $display_error = '

'.$msg.'

'; } include 'requires/page_header.php'; include 'requires/page_top.php'; ?>

Import Excel File Download Sample

num_rows > 0 ){ while( $row_page = $mysqli_page->fetch_array(MYSQLI_ASSOC) ){ // default variable $id = $row_page['staff_id'] ; echo ' '; } }else{ echo ' ' ; } ?>


'.dataFilter($row_page['staff_idno']).'	'.dataFilter($row_page['type']).'	'.dataFilter($row_page['times']).'
'.$lang['no_data'].'