<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
//include wp libriries and set timezone to HK
require_once('wp-load.php'); 
require_once('custom/PHPExcel.php');
date_default_timezone_set("Asia/Hong_Kong"); 
global $wpdb;
$path = "/var/www/html/ossfs/Breadsecret/awb_excel/";

if ($_FILES["excelFile"]["name"] <> ""){
	
	$filename=time()."_".$_FILES["excelFile"]["name"];
	$filepath = $path.$filename;
	if(move_uploaded_file($_FILES["excelFile"]["tmp_name"], $path.$filename)){
		$arr_result['condition'] = 'success';
		$arr_result['message'] = 'upload success';
		
		if(file_exists($filepath)){	
			$arr_data = array(array());
			$xlsObj = PHPExcel_IOFactory::load($filepath);
		//	$wsObj = $xlsObj->getActiveSheet();	
			$sheetindex=1; // use the 2nd sheet
			$wsObj = $xlsObj->setActiveSheetIndex($sheetindex);
				
			$maxColIdx = $wsObj->getHighestColumn();
			$maxColNo = PHPExcel_Cell::columnIndexFromString($maxColIdx);
			$maxCol = $maxColNo-1;
			
 			foreach($wsObj->getRowIterator() as $row) {
				 $xlsRow = $row->getRowIndex();
				 if(trim($wsObj->getCellByColumnAndRow(0, $xlsRow)->getFormattedValue())!=""){
					  for($col=0;$col<=$maxCol;$col++){
						$arr_data[$xlsRow][] = trim($wsObj->getCellByColumnAndRow($col, $xlsRow)->getFormattedValue()); 
					  }
				 } 
			}
					
			 //array_shift($arr_data); // remove first empty row
			 
			 if(count($arr_data)>0){

				for($dataRow=1; $dataRow<count($arr_data); $dataRow++){
					$transactionreferenceno = $arr_data[$dataRow][0];
					$handling_fee = $arr_data[$dataRow][7];
					$liquidation_amount = $arr_data[$dataRow][8];								

							///
                            $servername = "rm-3nsp08e0a25kj2jh3.mysql.rds.aliyuncs.com";
                            $username = "breadsecret.com";
                            $password = "3#!Xj7f&5";
                            $dbname = "breadsecret.com";

                            // 
                            $conn = mysqli_connect($servername, $username, $password, $dbname);
                            
                            // 
                            if (!$conn) {
                                die("Connection failed: " . mysqli_connect_error());
                            }
                          $sql = "SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE meta_key = 'transactionreferenceno' And meta_value = '".$transactionreferenceno."' LIMIT 1";
                         //   $result = $conn->query($sql);
                         $result = mysqli_query($conn,$sql);

                            if ($result->num_rows > 0) {

                                list($post_id) = mysqli_fetch_array($result);

                               // print_r($post_id);
                                update_post_meta($post_id, 'handling_fee', $handling_fee);
                                  update_post_meta($post_id, 'liquidation_amount', $liquidation_amount);
                                //echo $data['post_id'];

                                /*
                            while($row=mysqli_fetch_array($result)){
                                          $post_id = $row['post_id'];
                                        $meta_value = $row['meta_value'];
                       
                                };
                                
                                echo $meta_value;
                                if ($meta_value == $transactionreferenceno){
                                         update_post_meta($post_id, 'handling fee', $handling_fee);
                                         update_post_meta($post_id, 'liquidation amount', $liquidation_amount);
                                } */
                            $conn->close();
                            } 


					
				}
          
		//	print_r($arr_data);
				$arr_result['condition'] = 'success';
				$arr_result['message'] = 'import success';		

			 }
		} else {
			$arr_result['condition'] = 'fail';
			$arr_result['message'] = 'uploaded file not found';			
		}
		
	} else {
		$arr_result['condition'] = 'fail';
		$arr_result['message'] = 'unable to upload file';
	}
}

echo json_encode($arr_result);
?>