<?php
/**
{
REVISION:	0
DATE:		07-Sep-2019
AUTHOR:		Jorge
NOTES:		This file creates a payment run file that needs to be sent to SAGE Pay's portal to process the payments.
			This file is created according to the specifications as set out by SAGE: 
			https://api.sagepay.co.za/integration/Outbound%20Payments/salary_supplier_payments
			To test this file: http://test.smallpay.co.za/lib/testSageDisbursements.php
CRON CMD:	/usr/local/bin/php /home/smallakj/public_html/test.smallpay.co.za/lib/cron/runSageDisbursements.php	
----------------------------------------------------------------------------------------------------------------------------

** To process the batch file **

Successful request

If the method call was successful and the file is being processed, the web service will return a file token which is used to retrieve the load report

Example: 20000000.2550236530.0483.2.2

Unsuccessful request

If the method call fails, the web service will return an error code:

Code	Description
----	--------------------------------------------------------------------------------------
100		Authentication failure. Ensure that the service key in the method call is correct
101		Date format error. If the string contains a date, it should be in the format CCYYMMDD
102		Parameter error. One or more of the parameters in the string is incorrect
200		General code exception. Please contact Sage Pay Technical Support.

$client = new SoapClient("https://ws.sagepay.co.za/NIWS/niws_nif.svc");
$fileToken = $client->BatchFileUpload(array("ServiceKey" => "???", "File" => file_get_contents($file)));
-- or --
$fileToken = $client->BatchFileUpload($this->serviceKey, "file_get_contents($file));

** To get the load report **

$client = new SoapClient("https://ws.sagepay.co.za/NIWS/niws_nif.svc");
$response = $client->RequestFileUploadReport($this->serviceKey, $fileToken);
}
**/
include(__DIR__.'/../includes/sysinit.php');
class SableSagePayment
{
	private $batchName, $filePath, $file, $delim, $serviceKey, $vendorCode, $fileHandle, $cntTxns,
			$actionDate, $sumTxns, $sumRepay, $batchHeaderId, $insSQL, $sableDB;
	var $success, $errorMessage;

	function __construct()
	{
		$this->sableDB = new SableDB();
	}
	
	function __destruct(){}
	
	private function createFile()
	{
		if (!file_exists($this->filePath)) 
		{
			if (!mkdir($this->filePath, 0755, true)) die('Folder '.$this->filePath.' could not be created.');
		} 
	}
	
	private function openFile()
	{
		$this->fileHandle = fopen($this->file, "x");
		if (!$this->fileHandle) die($this->file.' cannot be created.');
	}
	
	private function getDelimitedData($data)
	{
		return implode($this->delim, $data);	
	}
	
	private function addFileHeader()
	{
		/**
		{
		Header record (H)
		
		Every file must contain a header record as the first record in the file.
		
		Record Structure
		
		Field	Name					Type	Value
		1		Record Identifier		AN		H
		2		Service Key				AN		Salary Payments Service Key / Creditor Payments Service Key
		3		Version					AN		1
		4		Instruction				AN		Purpose of the file:
												Update			–	update the master file without loading a batch
												PaySalaries 	–	Sameday salary batch upload (ideal for bonus or once off payments where
																	the time the recipient receives the funds is not important)
												DatedSalaries	–	Dated salary batch upload (ideal for salary / wage payments)
												Realtime		–	Sameday creditor batch upload (ideal for supplier or creditor payments)
												DatedPayments	–	Dated creditor batch upload (ideal for once off payments where the time 
																	the recipient receives the funds is not important)
																	SEE https://api.sagepay.co.za/integration/Outbound%20Payments/salary_supplier_payments#processing-deadlines.	
		
		5		Batch name				AN		Your identifier
		6		Action date				N		CCYYMMDD
		7		Software vendor code	AN		The key issued by Sage Pay to identify the software origin of transactions. 
												(only used by Sage Pay business partners else use default value:
												24ade73c-98cf-47b3-99be-cc7b867b3080)
		}
		**/
	
		$data = [];
		$data[] = "H";																	//Record Identifier
		$data[] = $this->serviceKey;													//Service Key
		$data[] = "1";																	//Version
		$data[] = "Realtime";															//Instruction
		$data[] = $this->batchName;														//Batch Name
		$data[] = $this->actionDate;	 												//Action Date
		$data[] = $this->vendorCode; 													//Software Vendor Code
		
		$headerLine = $this->getDelimitedData($data).PHP_EOL;
		fwrite($this->fileHandle, $headerLine);
	}
	
	private function addKeyRecord()
	{
		/**
		{
		Key record (K)
		
		-	The key record is mandatory and must appear in the file between the header record and the transaction records.
		-	The key defines the content, order and length of the transactions records in the file. 
			It can be likened to the column headings in a spreadsheet.
		-	The key lists all the possible fields contained in the subsequent transaction records, even if not every transaction 
			record contains data in all the fields.
		-	While the file is customizable, it is recommended that keys are listed in ascending numerical order for ease of 
			debugging and support.	
		}
		**/
	
		$data = [];
		$data[] = "K";
		$data[] = "101"; //* Account reference
		$data[] = "102"; //* Account name
	//	$data[] = "103"; //  Beneficiary active
	//	$data[] = "104"; //  Delete beneficiary
		$data[] = "131"; //* Banking detail type
		$data[] = "132"; //* Bank account name
		$data[] = "133"; //* Bank account type
		$data[] = "134"; //* Branch code
		$data[] = "135"; //* Filler
		$data[] = "136"; //* Bank account number
	//	$data[] = "161"; //  Default amount
		$data[] = "162"; //* Amount
	//	$data[] = "201"; //  Email address
	//	$data[] = "202"; //  Mobile number
	//	$data[] = "251"; //  Default Beneficiary statement reference
		$data[] = "252"; //* Beneficiary statement reference
	//	$data[] = "281"; //  Payment beneficiary group	
		$data[] = "301"; //  Extra 1
	//	$data[] = "302"; //  Extra 2
	//	$data[] = "303"; //  Extra 3
		
		$keyLine = $this->getDelimitedData($data).PHP_EOL;
		fwrite($this->fileHandle, $keyLine);	
	}
	
	private function addTransactions()
	{
		/**
		{
		Transaction record (T)
		
		-	There must be at least one transaction record in the file.
		-	The transaction records must conform to the order of the fields as described in the key record.
			(In the example above, the account reference (field id 101) is defined as the first field in the record 
			therefore every transaction record MUST have the account reference as the first field or it will fail validation)
		-	Every transaction record must have the same number of fields as defined in the key record (K).
		-	Empty fields should be replaced by a blank tab or a default value, where one has been defined. 
			Where fields are left empty, the Sage Pay system will assume spaces or zeroes for fields defined in the key record.	
			
	
		Fld	Name						Type	Value
		101	Account reference			AN22	The unique account reference that you will use in the future to address this client
												(Minimum 2; Maximum 22)
		102	Account name				AN25	The name of your client’s account on the Sage Pay system (Typically your client’s name)
												(Minimum 2; Maximum 25)
		103	Account active				N		0 = Inactive 1 = Active
												Note: this field is intended for masterfile update files only. 
												Field 103 will not be processed if it is included in a batch upload file
		104	Delete this account			N		0 = Don't delete 1 = Delete
												Note: this field is intended for masterfile update files only. 
												Field 104 will not be processed if it is included in a batch upload file
		131	Banking detail type			N1		1 = bank account
		132	Bank account name			AN30	The name of the bank account to be debited or credited (may differ to the name in field 102)
												(account holder name)(Maximum 30 characters)
		133	Bank account type			N1		1 = Current / Checking
												2 = Savings
												3 = Transmission
												4 = Bond
		134	Bank account branch			N6		The branch code of the homing account. This field MUST be 6 digits. 
												Pre-fill with zeroes if less than 6.
		135	Filler						N1		0
		136	Bank account number			N11		The homing bank account number. May not exceed 11 digits.
		161	Default payment amount		N		The default amount to be set in the masterfile (in cents)
		162	Payment amount				N		The amount of this payment (in cents)
		201	Email address				AN50	This is the email address used to notify your client of the debit order.
												(The “@” and “.” symbols are allowed in this field)
		202	Mobile number				N11		The cell number to be used for SMS notifications Include dialling code EG: 27827822349
												NOTE: cell numbers which are not correctly formatted will be ignored and not updated 
												to the masterfile
		251	Default Beneficiary ref		AN20	This field is the statement reference that appears on the client’s bank statement. 
												This field can only be used when updating the beneficiary masterfile.
												(Minimum 4; Maximum 20 characters)
												Note: only the first 12 characters will appear on the beneficiary statement
		252	Beneficiary statement ref	AN20	This field is the statement reference that appears on the client’s bank statement. 
												This field can only be used when making payments.
												(Minimum 4; Maximum 20 characters) 
												Note: only the first 12 characters will appear on the beneficiary statement
		281	Payment beneficiary group	AN50	Links this beneficiary to an existing group. Validation will fail if the group 
												indicated does not exist.
		301	Extra 1						AN50	Custom User-defined data (Extra data you want returned for your internal system use)
		302	Extra 2						AN50	Custom User-defined data (Extra data you want returned for your internal system use)
		303	Extra 3						AN50	Custom User-defined data (Extra data you want returned for your internal system use)
		}
		**/

		$sql =  "select user.user_code, COALESCE(user.user_id, user.user_passport) as user_id_passport, user.user_surname, 
				concat(user.user_title, ' ', user.user_name, ' ', user.user_surname) as user_full_name, user.user_name,user.user_surname,
				bat_id, ua_branch_code, ua_account_no, loan_id, company_name, COALESCE(updated_amount, loan_amount) as amount_loaned,
				repay_amount, loan.company_id as loan_company_id 
				from user
				inner join user__account on user__account.user_code = user.user_code 
				inner join loan on loan.user_code = user.user_code 
				inner join company on company.company_id = user.company_id
				where (loan.deleted = 0) and (loan_updated = 0) and (loan_status_id = 5)
				and (loan_processed_date is null) and (account_enabled = 1)"; // and (loan_processed = 0) 
				
		$rows = $this->sableDB->getRows($sql);
		
		$started = false;
		foreach($rows as $row)
		{
			$data = [];
			
			//shorten name - instead of "Jorge Jardim" use "J. Jardim"
			$shortName   = $row['user_name'][0].'. '.$row['user_surname'];
			$txnAmount   = $row['amount_loaned'] * 100;
			$repayAmount = $row['repay_amount'];
			
			$field101 = $row['user_id_passport'].'-'.$row['loan_id'];
			$field102 = substr($shortName, 0, 25);
			
			$field131 = 1;
			$field132 = substr($shortName, 0, 30);
			$field133 = $row['bat_id'];
			$field134 = trim($row['ua_branch_code']);
			$field135 = 0;
			$field136 = trim($row['ua_account_no']);
			
			$field162 = $txnAmount;
			
			$field252 = 'Small Pay Loan '.$row['loan_id'];
			
			$field301 = 'Employer: '.$row['company_name'];
			
			//Follow the same sequence as the Key Record:
			$data[] = "T";
			$data[] = $field101;
			$data[] = $field102;
			$data[] = $field131;
			$data[] = $field132;
			$data[] = $field133;
			$data[] = $field134;
			$data[] = $field135;
			$data[] = $field136;
			$data[] = $field162;
			$data[] = $field252;
			$data[] = $field301;		
			
			$txnLine = $this->getDelimitedData($data).PHP_EOL;
			fwrite($this->fileHandle, $txnLine);
			$this->cntTxns++;
			$this->sumTxns  += $txnAmount;
			$this->sumRepay += $repayAmount;
			
			//(`disb_batch_id`, `loan_id`, `user_code`, `loan_amount`)
			if ($started) $this->insSQL .= ", ";
			$this->insSQL .= "('".$this->batchHeaderId."', ".$row['loan_id'].", ".$row['user_code'].", ".$row['loan_company_id'].", ".$row['amount_loaned'].", ".$row['repay_amount'].")";
			
			$started = true;
		}
	}
	
	private function addFileFooter()
	{
		/**
		{
		Field	Name					Type	Value
		1		Record Identifier		AN		F
		2		No of transactions		N		A count of the transaction records
		3		Sum of amounts			N		The sum of the monetary fields (in cents)
		4		End-of-file indicator	N		9999
		}
		**/
		
		$data = [];
		$data[] = "F";
		$data[] = $this->cntTxns;
		$data[] = $this->sumTxns;
		$data[] = "9999";
		
		$footerLine = $this->getDelimitedData($data).PHP_EOL;
		fwrite($this->fileHandle, $footerLine);	
	}
	
	private function closeFile()
	{
		fclose($this->fileHandle) or die('The file "'.$this->file.'" could not be closed.');
	}
	
	private function saveData()
	{
		if ($this->cntTxns)
		{
			$res = $this->sableDB->updateQuery($this->insSQL);
			if (!$res) 
			{
				die('The batch data could not be saved to the database.');
			}
		}
		else
		{
			$this->errorMessage = 'There are no transactions to process.';
		}
	}
	
	private function processFile()
	{
		$this->createFile();
		$this->openFile();	
		$this->addFileHeader();
		$this->addKeyRecord();
		$this->addTransactions();
		$this->addFileFooter();
		$this->closeFile();
		$this->saveData();
	}
	
	public function runFile()
	{				
		$row = $this->sableDB->getRow("select * from sage_config where cfg_id = 1");
		
		/*
		If the action date falls on a weekend, then we must set the action date for the following Monday:
!!! We should probably also look for Public Holidays? !!!
		*/
		
		$daycount = 1; // add 1 day to action the following day.
		$dw = date("w"); //SUN = 0, MON = 1, TUE = 2, WED = 3, THU = 4, FRI = 5, SAT = 6
		if ($dw === 6) $daycount += 1; //Saturday - so add 2 days to Monday
//		if ($dw === 0) $daycount += 1; //Sunday - so add 1 day to Monday 
		if ($dw === 5) $daycount += 2; //Friday - so add 3 days to Monday 
		$dom = date('d')+$daycount;
		$actionDay = str_pad($dom, 2, '0', STR_PAD_LEFT);
		$this->actionDate = date("Ym".$actionDay);
		
		//we need the batch id for the line items:
		/*
		$batchSQL = "insert into disbursement_batch 
					(disb_create_date, disb_record_count, disb_failed_total, disb_result, disb_run_date, disb_action_date) 
					values
					('".date('y-m-d H:i:s')."', 0, 0, NULL, '".date('y-m-d')."', '".$this->actionDate."')";
		$this->sableDB->updateQuery($batchSQL);
		*/
		$this->batchHeaderId = $this->sableDB->getValue('select MAX(disb_batch_id)+1 from disbursement_batch');	
		if ($this->batchHeaderId === 0) $this->batchHeaderId = 1; //initialisation.
		
		$this->batchName 	= $row["cfg_file_prefix"]."_".date('YmdHis')."_".$this->batchHeaderId;
		$fileName			= $this->batchName.'_'.$row["cfg_file_suffix"].'.txt';
		$this->filePath 	= SABLE_WORKING_PATH.'lib/output/';
		$this->file 		= $this->filePath.$fileName;
		$this->delim 		= "\t";
		$this->serviceKey 	= SABLE_DEMO_SYSTEM ? '4863c5c7-f89b-4409-a946-9939f2793e4c' : $row['cfg_service_key'];
		$this->vendorCode 	= SABLE_DEMO_SYSTEM ? '24ade73c-98cf-47b3-99be-cc7b867b3080' : $row['cfg_vendor_key'];
		
		//process "system" vars:
		$this->fileHandle 	= 0;
		$this->cntTxns 		= 0;
		$this->sumTxns 		= 0;
		$this->sumRepay     = 0;
		$this->success 		= false; //initialisation
		$this->errorMessage = '';
		
		$this->insSQL = "insert into disbursement (`disb_batch_id`, `loan_id`, `user_code`, `company_id`, `loan_amount`, `repay_amount`) values ";
		
		$this->processFile();
		
		if ($this->cntTxns)
		{
			if (is_file($this->file))
			{
				$client 	= new SoapClient("https://ws.sagepay.co.za/NIWS/niws_nif.svc?wsdl");
				$fileToken	= $client->BatchFileUpload(array("ServiceKey" => $this->serviceKey, "File" => file_get_contents($this->file)));
				unset($client);
				switch ($fileToken)
				{
					case "100":	{$this->errorMessage = 'Authentication failure';	break;}
					case "102":	{$this->errorMessage = 'Parameter error'; 			break;}
					case "200":	{$this->errorMessage = 'General code exception.';	break;}
					default: 	{$this->success = true;}
				}
				
				//SableDebug::debug($fileToken);
			
				/*				
				$batchSQL = "update disbursement_batch set
							 disb_record_count = ".$this->cntTxns.", 
							 disb_total_amount = ".floatval($this->sumTxns / 100).",
							 disb_repay_total = ".floatval($this->sumRepay).",
							 disb_run_token = '".$fileToken->BatchFileUploadResult."'  
							 where disb_batch_id = ".$this->batchHeaderId;
				*/		 
				
				$batchSQL = "insert into disbursement_batch 
					(disb_create_date, disb_record_count, disb_failed_total, disb_result, disb_run_date, disb_action_date, 
					disb_total_amount, disb_repay_total, disb_run_token) 
					values
					('".date('y-m-d H:i:s')."', ".$this->cntTxns.", 0, NULL, '".date('y-m-d')."', '".$this->actionDate."', 
					".floatval($this->sumTxns / 100).", ".floatval($this->sumRepay).", '".$fileToken->BatchFileUploadResult."')";
				$this->sableDB->updateQuery($batchSQL);	
			} 
			else 
			{
				$this->errorMessage = 'file failed: '.$this->file;
			}
		}
	}
}