query("SELECT * FROM master_country WHERE deleted_at IS NULL") ; if ( $get_country->num_rows > 0 ){ while ( $row_country = $get_country->fetch_assoc() ){ $country[$row_country['country_id']] = $row_country['country_desc'] ; } } // mode type | all list | new | edit switch($page_mode){ // edit advance case 'new' : case 'edit' : // check query exsits $submit_type = 'new' ; $mysqli_page = $mysqli->query("SELECT * FROM staff_advance WHERE advance_id = '".$page."' AND deleted_at IS NULL LIMIT 1"); if ($mysqli_page->num_rows > 0){ // keep query value in array $row_page = $mysqli_page->fetch_array(MYSQLI_ASSOC) ; $submit_type = 'edit' ; } // update database if ( !($row_page['advance_id']>0) && isset($type) && ( $type == 'new' || $type == 'edit' ) && $_POST['hide'] == 1 ){ $error_message = '

Please enter all required field.

' ; $staff_id = escapeString($_POST['staff_id']) ; $advance_paidby = escapeString($_POST['advance_paidby']) ; $advance_amount = escapeString($_POST['advance_amount']) ; $advance_reason = escapeString($_POST['advance_reason']) ; if ( ( ( $submit_type == 'new' && $staff_id != '' ) || $submit_type == 'edit' ) && $advance_paidby != '' && $advance_amount != '' ){ $advance_reason = ( $advance_reason != '' ? $advance_reason : 'ADVANCE' ) ; $error_message = '

No setting found.

' ; $day = date('j', strtotime(TODAYDATE)) ; // check query exsits $setting_query = $mysqli->query("SELECT post_id, post_title as advance_from, post_link as advance_to, post_content as advance_remark FROM system_post WHERE post_type = 'page-advance' AND post_categories = 'page-advance' AND post_trash = '0' LIMIT 1") ; $setting = [] ; if ( $setting_query->num_rows > 0 ){ $setting = $setting_query->fetch_assoc() ; } $boolean_advance = false ; if ( $setting['advance_from'] != '' ){ if ( $setting['advance_from'] <= $day && $setting['advance_to'] >= $day ){ $boolean_advance = true ; } }else{ $boolean_advance = true ; } if ( $setting['advance_remark'] != '' ){ $error_message = '

'.$setting['advance_remark'].'

' ; } if ( $boolean_advance ){ $error = 0 ; $mysqli->autocommit( false ) ; try { $amount = is_numeric($advance_amount) ? number_format($advance_amount, 2, '.', '') : 0 ; // insert into advance $mysqli->query("INSERT INTO staff_advance (staff_id, advance_paidby, advance_amount, advance_reason, advance_status, created_at, updated_at) VALUES ('".$staff_id."', '".$advance_paidby."', '".$advance_amount."', '".$advance_reason."', 'pending', '".TODAYDATE."', '".TODAYDATE."')") ; $page = $mysqli->insert_id ; pushToUserCron( 'staff_advance', $page, $staff_id, 'Apply Advance', 'Your advance was submitted.' ) ; }catch( Exception $e ){ $error_message = '

'.$e.'

' ; $error++; } if( $error == 0 ) { // commit query $mysqli->commit() ; $error_message = '

'.$lang['Advance was submitted'].'

' ; }else{ $mysqli->rollback() ; } } } // refresh page header("Location:hr-advance.php?page_mode=".$page_mode."&page=".$page) ; $_SESSION['system_result'] = $error_message ; exit ; }elseif($row_page['advance_id']>0 && $_POST['hide'] == 1 && $type == 'edit'){ $advance_paidby = escapeString($_POST['advance_paidby']); $advance_amount = escapeString($_POST['advance_amount']); $advance_reason = escapeString($_POST['advance_reason']); $error = 0 ; $mysqli->autocommit( false ) ; try { $q = "UPDATE `staff_advance` SET advance_paidby = '".$advance_paidby."', advance_amount = '".$advance_amount."', advance_reason = '".$advance_reason."' WHERE advance_id = '".$page."' "; $mysqli->query($q); if($mysqli->error == ''){ }else{ $error_message = '

Failed Update

' ; $error++; } }catch( Exception $e ){ $error_message = '

'.$e.'

' ; $error++; } if( $error == 0 ) { // commit query $mysqli->commit() ; $error_message = '

Update success

' ; }else{ $mysqli->rollback() ; } header("Location:hr-advance.php?page_mode=".$page_mode."&page=".$page) ; $_SESSION['system_result'] = $error_message ; exit; } // active menu bar $active_main_menu = 'hr' ; $active_sub_menu = 'hr-advance' ; if ( ( $page_mode == 'new' && !permissionCheck($row_user, 'advance-new') ) || ( $page_mode == 'edit' && !permissionCheck($row_user, 'advance-update') ) ){ header('Location: hr-advance.php') ; exit ; } // start header here include 'requires/page_header.php'; include 'requires/page_top.php'; // get all requires // get all staff $staff_list = [] ; $mysqli_staff = $mysqli->query("SELECT staff_id, staff_name, staff_idno FROM staff WHERE deleted_at IS NULL ".$user_branch_permission_sql) ; if ( $mysqli_staff->num_rows > 0 ){ while ( $row_staff = $mysqli_staff->fetch_assoc() ){ $staff_list[$row_staff['staff_id']] = dataFilter($row_staff['staff_name']) . ' ( ' . dataFilter($row_staff['staff_idno']) . ' )' ; } } ?>

0 ){ $new_print_id = [] ; foreach ( $_POST['multiple_print'] as $key => $value ){ $new_print_id[] = $key ; } $print_id = implode(',', $new_print_id) ; $_SESSION['open_in_new'] = $print_id ; }else{ // trash item if ( $page_action != '' ){ switch ( $page_action ){ case 'confirmed' : case 'rejected' : case 'trash' : $result = 'failed-check' ; $multiple = $_POST['multiple_trash'] ; $staff_list = [] ; $update_list = [] ; if ( arrayCheck($multiple) ){ foreach ( $multiple as $key => $value ){ $update_list[] = $key ; $staff_list[] = $value ; } if ( $page_action == 'trash' ){ if ( $mysqli->query("UPDATE staff_advance SET deleted_at = '".TODAYDATE."', advance_updated_author = '".$_SESSION['system_id']."' WHERE advance_id IN (".implode(',', $update_list).") AND advance_payment_status = 'no'") ){ $result = 'success-update' ; } }else{ if ( $mysqli->query("UPDATE staff_advance SET advance_status = '".$page_action."', advance_updated_author = '".$_SESSION['system_id']."' WHERE advance_id IN (".implode(',', $update_list).") AND advance_status = 'pending' AND advance_payment_status = 'no'") ){ $result = 'success-update' ; } } } break ; } } // update payment status $update_payment_status = $_POST['update_payment_status']; if( $update_payment_status != '' ){ $multiple = $_POST['multiple_trash'] ; if ( arrayCheck($multiple) ){ foreach ( $multiple as $key => $value ){ $result = 'success-update' ; $mysqli->query("UPDATE staff_advance SET advance_payment_status = '".$update_payment_status."', advance_payment_date = '".TODAYDATE."', advance_updated_author = '".$_SESSION['system_id']."' WHERE advance_id IN (".$key.") ") ; pushToUserCron( 'staff_advance', $key, $value, 'Advance '.ucwords($page_action), 'Your advance has been '.$page_action.'.' ) ; } } } $_SESSION['system_result'] = $result ; } } // 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_paidby='.$search_paidby.'&search_status_type='.$search_status_type.'&search_payment_status='.$search_payment_status.'&search_date_from='.$search_date_from.'&search_date_to='.$search_date_to.'&sort_by='.$sort_by.'&sort_by_type='.$sort_by_type ; // page query $mysqli_query = "SELECT a.advance_id, a.advance_paidby, a.advance_amount, a.advance_reason, a.advance_status, a.advance_payment_status, a.advance_payment_date, a.created_at, a.updated_at, b.staff_id, b.staff_name, b.staff_idno, b.staff_accountno, b.staff_icno, b.staff_passportno, b.country_id FROM staff_advance a LEFT JOIN staff b ON ( a.staff_id = b.staff_id ) WHERE a.deleted_at IS NULL " . $search_query.$user_branch_permission_sql_b ; // export excel if ( $export == 'yes' ){ include 'PhpExcel/PHPExcel.php' ; $page_filename = 'Advance-'.date( 'Ymd', time() ) ; $objPHPExcel = new PHPExcel() ; $objPHPExcel->getProperties() ->setCreator(COMPANY) ->setTitle(COMPANY) ->setSubject(COMPANY) ->setDescription(COMPANY) ->setKeywords(COMPANY) ->setCategory(COMPANY) ; $objPHPExcel->getActiveSheet()->setTitle( $page_filename ) ; $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel5' ) ; $border_Style = array( 'borders' => array( 'allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ) ); // default parameter $count = 1 ; $from_count = $count; $char = 'A' ; $count_staff = 1 ; $total_amt = 0 ; $array_title = array( 'Emp. No', 'Name', 'Country', 'IC', 'Passport', 'Bank Account No.', 'Paid By', 'Amount RM', 'Reason', 'Status', 'Payment Status', 'Created Date', 'Updated Date' ) ; $newChar = $char ; foreach( $array_title as $k => $v ){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue( $newChar.$count, $v ) ; $newChar++ ; } $count++ ; $staff_advance = $mysqli->query( $mysqli_query." ORDER BY a.created_at DESC" ) ; if ( $staff_advance->num_rows > 0 ){ while ( $staff_adv = $staff_advance->fetch_assoc() ){ $staff_idno = ucwords($staff_adv['staff_idno']) ; switch ( $staff_adv['advance_paidby'] ){ case 'cash' : $paidby = 'Cash' ; break ; case 'debit' : $paidby = 'Debit Bank' ; break ; default : $paidby = '' ; } switch ( $staff_adv['advance_payment_status'] ){ case 'no' : $payment_status = 'Pending' ; break ; case 'yes' : $payment_status = 'Done ('.$staff_adv['advance_payment_date'].')' ; break ; default : $payment_status = '' ; } $newChar = $char ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_idno ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['staff_name'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $country[$staff_adv['country_id']] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['staff_icno'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['staff_passportno'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, "'".$staff_adv['staff_accountno'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $paidby ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['advance_amount'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['advance_reason'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, ucwords( $staff_adv['advance_status'] ) ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $payment_status ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['created_at'] ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $staff_adv['updated_at'] ) ; $count++ ; $count_staff++ ; $total_amt += $staff_adv['advance_amount'] ; } $newChar = $char ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, 'Total :' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, $total_amt ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $objPHPExcel->setActiveSheetIndex(0)->setCellValue( ($newChar++).$count, '' ) ; $count++ ; } $to_count = $count-1; $objPHPExcel->getActiveSheet()->getStyle('A') ->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('A'.$count.':A'.$to_count)->getAlignment()->applyFromArray( array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER) ); $objPHPExcel->getActiveSheet()->getStyle('Q'.$count.':Q'.$to_count)->getAlignment()->applyFromArray( array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER) ); $objPHPExcel->getActiveSheet()->getStyle('A1:M'.$to_count)->applyFromArray($border_Style) ; $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth("5"); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth("28"); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth("7"); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth("5"); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth("5"); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth("28"); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth("7"); header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' ) ; header( 'Content-Disposition: attachment;filename="'.$page_filename.'.xls"' ) ; header( 'Cache-Control: max-age=0' ) ; // save to pc ob_clean(); $objWriter->save('php://output') ; header( "Refresh: 0" ) ; exit ; } $mysqli_page = $mysqli->query($mysqli_query." ORDER BY a.created_at DESC LIMIT $start_from, " . LIMIT) ; // load pagination $page_pagination = nextPrevious($product_page, LIMIT, $search_url, $mysqli_query) ; // start header here include 'requires/page_header.php' ; include 'requires/page_top.php' ; if ( $_SESSION['open_in_new'] != '' ){ echo '' ; unset( $_SESSION['open_in_new'] ) ; } ?>

' ; break ; case 'failed-check' : echo '

'.$lang['Sorry please select at least one'].'

' ; break ; case 'success-update' : echo '

'.$lang['Thank you status updated sucessfully'].'

' ; break ; } unset($_SESSION['system_result']) ; } ?>

search

num_rows > 0){ while ($row_page = $mysqli_page->fetch_array(MYSQLI_ASSOC)){ $staff_idno = ucwords($row_page['staff_idno']) ; // advance status if ( $row_page['advance_paidby'] == 'cash' ){ $button_paidby = ''.$lang['Cash'].'' ; }elseif ( $row_page['advance_paidby'] == 'debit' ){ $button_paidby = ''.$lang['Debit Bank'].'' ; } // advance status if ( $row_page['advance_status'] == 'pending' ){ $button = '' ; }elseif ( $row_page['advance_status'] == 'confirmed' ){ $button = '' ; }else{ $button = '' ; } // payment status if ( $row_page['advance_payment_status'] == 'yes' ){ $button_p = '' ; }else{ $button_p = '' ; } echo ' '; } }else{ echo ' ' ; } ?>

Update Status
' ; if ( $row_page['advance_payment_status'] == 'no' ){ echo ' ' ; } echo '	\|	'.dataFilter($row_page['staff_name']).' ( '.$staff_idno.' )	'.dataFilter($row_page['advance_reason']).'	'.$button_paidby.'	'.dataFilter($row_page['advance_amount']).'	'.$button.'	'.$button_p.'	'.resetDateFormat($row_page['created_at']).'	'.resetDateFormat($row_page['updated_at']).'
'.$lang['no_data'].'