<?php



$currentdate = date('Y-m-d h:i:s', time());

$FieldHeadings = array( 'TransID',
					   'Client',//  0 'STOCKID',
					   'RefTransId',
					   'Voided',
						'Transtype',	
						'Account',	
					   	'TransDate',
					   	'Amount');



//function the_file() {
    $files = glob("vms/*.csv");
   // return $files[0]; // return the first filename
//}

//$file = the_file();
//get file handle

if (!empty($files)) {
   // die("Error: The directory already has a .csv file");


foreach($files as $file) {
  
	
	

	$FileHandle = fopen($file, 'r');

	//get the header row
	$HeadRow = fgetcsv($FileHandle, 10000, ',');

	//check for correct number of fields
	if ( count($HeadRow) != count($FieldHeadings) ) {
		  mail('sachin@wormaldfj.com', 'Error VMS transaction in ERP', "Hello Authorized CIT Admins ,\r\n\r\nThe column count in csv file is not equal to required parimeters. Error in csv file. Please check and repost " );
		fclose($FileHandle);
		
		exit;
	}

	//test header row field name and sequence
	$HeadingColumnNumber = 0;
	foreach ($HeadRow as $HeadField) {
		if ( trim(mb_strtoupper($HeadField)) != trim(mb_strtoupper($FieldHeadings[$HeadingColumnNumber]))) {
				  mail('sachin@ebusinessfiji.com', 'Error VMS transaction in ERP', "Hello Authorized CIT Admins ,\r\n\r\nThe column header in csv file is not equal to required parimeters. Error in csv file. Please check and repost ");
			fclose($FileHandle);
			
			exit;
		}
		$HeadingColumnNumber++;
	}
	//start database transaction
	//DB_Txn_Begin();

$servername1 = "localhost";
$username1 = "sooggoo";
$password1= "4Compassion*";
$name1 = "evergreenint";
// Create connection
$remote = mysqli_connect($servername1, $username1, $password1, $name1);
// Check connection
if (!$remote) {
    die("Connection failed: " . mysqli_connect_error());
}



	//loop through file rows
	$LineNumber = 1;
	while ( ($myrow = fgetcsv($FileHandle, 10000, ',')) !== FALSE ) {

		
		$id = $myrow[0];
		$client = $myrow[1];
		$ref = $myrow[2];
		$v = $myrow[3];
		
		$type = $myrow[4];
         $account= $myrow[5];
		$transdate= $myrow[6];
		$amount = $myrow[7];
		
		$date1 = str_replace("/", "-", $transdate);

	
		$date = date('Y-m-d',strtotime($date1));
		$longdate = date('Y-m-d H:i:s',strtotime($date1));
		$yeardate = date('Y',strtotime($date1));

		$monthdate =date('m',strtotime($date1));
		
		
		
		
		

		//open vms trans and check if duplicate trans shows
		

		
		$sql55= "SELECT trans_id
			FROM vms
			WHERE trans_id ='" . $id . "'
			";
$result55 = $remote->query($sql55)  or die($remote->error);
$myrow55 = $result55->fetch_assoc();

$transid =  $myrow55['trans_id'];
		    
		if ($id == $transid ) {
			
			mail('sachin@ebusinessfiji.com', 'ERROR in VMS uploads to ERP', "Hello Authorized CIT Admins ,\r\n\r\nThe file seems to have duplicate data in ERP records please check the file sent to FTP folder ");
			exit();
		}
		elseif  ($id !== $transid ) {
		
		$query279 = "SELECT 
						periodno,
		             lastdate_in_period
		                 
					  
		          FROM periods
				    WHERE YEAR(lastdate_in_period) = '" . $yeardate . "'
					AND MONTH(lastdate_in_period)  = '" . $monthdate. "'";
				
        $result279 = $remote->query($query279)  or die($remote->error);
        $myrow279 = $result279->fetch_assoc();
      
				$PeriodNo = $myrow279['periodno'];
		
		//$query271 = "SELECT 
				//		typeno
		            
		                 
					  
		       //   FROM systypes
				//    WHERE typeid = '0'";
				
       // $result271 = $remote->query($query271)  or die($remote->error);
       // $myrow271 = $result271->fetch_assoc();
      
			//	$typeno = $myrow271['typeno'];
			//	$id = $typeno + 1;
		
		
		if ($type == 'Deposit'){
			
			//$PeriodNo = GetPeriod($transdate,$db);

     /*Start a transaction to do the whole lot inside */
	//$result = DB_Txn_Begin();

	//$id= GetNextTransNo( 0, $db);
			
			
		

			$narative = 'Deposit -'.$account;
			//debit coh
			
			$SQL = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,
									posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ157',
					'" . $narative . "',
					'" . $amount. "',
					'0',
					1)";
		
		$result = $remote->query($SQL)  or die($remote->error);
         
			//credit liability
			
			$SQL1 = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ657',
					'" . $narative . "',
					'" . -$amount. "',
					'0',
					1)";
		
		$result = $remote->query($SQL1) or die($remote->error);
       
	
			
		}
		elseif ($type == 'Supply'){
					

     /*Start a transaction to do the whole lot inside */
	//$result = DB_Txn_Begin();

	//$id= GetNextTransNo( 0, $db);

			$narative = 'Supply-'.$account;
			//credit coh
			
			$SQL = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ157',
					'" . $narative . "',
					'" . -$amount. "',
					'0',
					1)";
		
		$result = $remote->query($SQL) or die($remote->error);
         
			//debit liability
			
			$SQL1 = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ657',
					'" . $narative . "',
					'" . $amount. "',
					'0',
					1)";
		
		$result = $remote->query($SQL1) or die($remote->error);
       
	
			
			
		}
		elseif ($type == 'VoidedDeposit'){
						//		$PeriodNo = GetPeriod($transdate,$db);

     /*Start a transaction to do the whole lot inside */
	//$result = DB_Txn_Begin();

	//$id= GetNextTransNo( 0, $db);

			$narative = 'VoidedDeposit Ref-'.$ref;
			//credit coh
			
			$SQL = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ157',
					'" . $narative . "',
					'" . -$amount. "',
					'0',
					1)";
			
		$result = $remote->query($SQL) or die($remote->error);
			//debit liability
			
			$SQL1 = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ657',
					'" . $narative . "',
					'" . $amount. "',
					'0',
					1)";
		$result = $remote->query($SQL1) or die($remote->error);
			
		}
		elseif ($type == 'VoidedSupply'){
			
									//	$PeriodNo = GetPeriod($transdate,$db);

     /*Start a transaction to do the whole lot inside */
	//$result = DB_Txn_Begin();

	//$id= GetNextTransNo( 0, $db);

			$narative = 'VoidedSupply Ref-'.$ref;
			//debit coh
			
			$SQL = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ157',
					'" . $narative . "',
					'" . $amount. "',
					'0',
					1)";
		$result = $remote->query($SQL) or die($remote->error);
         
			//credit liability
			
			$SQL1 = "INSERT INTO gltrans (type,
									typeno,
									trandate,
									periodno,
									account,
									narrative,
									amount,
									tag,posted)
				VALUES ('0',
					'" . $id. "',
					'" . $date . "',
					'" . $PeriodNo . "',
					'FJ657',
					'" . $narative . "',
					'" . -$amount. "',
					'0',
					1)";
		$result = $remote->query($SQL1) or die($remote->error);
       
	
			
		}
		
			
$SQL = "INSERT INTO vms (trans_id,
												client,
												reftransid,
												voided,
												trans_type,
												account,
												trans_date,
												amount)
										VALUES ( $id ,
																					
												'" . $client . "',
												'" . $ref . "',
												'" . $v . "',
												'" . $type . "',
												'" .$account . "',
												'" . $longdate. "',
												'" . $amount. "')";
			$result = $remote->query($SQL) or die($remote->error);
		}
//update systypes 0 journal so it gives tallies
		$LineNumber++;
	}



            
       
	

	

mail('sachin@ebusinessfiji.com', 'VMS transaction in ERP', "Hello Authorized CIT Admins ,\r\n\r\nWe had succesully completed uploaded VMS transations to ERP GL Balancing. Also have move the file to processed folder ","CC: Ian.Anderson@armourguard.co.nz" );

fclose($FileHandle);
	
}
	
}

	
mysqli_close($remote);	

	$Ignore = array(".","..",".ftpquota");
$OriginalFileRoot = "vms";
$OriginalFiles = scandir($OriginalFileRoot);
$DestinationRoot = "vms/processed";

foreach($OriginalFiles as $OriginalFile){
    if(!in_array($OriginalFile,$Ignore)){
        //$FileExt = pathinfo($OriginalFileRoot."\\".$OriginalFile, PATHINFO_EXTENSION); // Get the file extension
        $Filename = basename($OriginalFile, ".csv"); // Get the filename
        $DestinationFile = $DestinationRoot."/".$Filename.".csv"; // Create the destination filename 
        rename($OriginalFileRoot."/".$OriginalFile, $DestinationFile); // rename the file            
    }
}



?>