<?php
/**
{
REVISION:	0
DATE:		5-Feb-2019
AUTHOR:		Jorge
NOTES:		This file reads the return report provided after a debit order run created by "createSAGEDebitOrderFile.php".
			This file is read 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/testSagePaymentPostback.php
CRON:		/usr/local/bin/php /home/smallakj/public_html/test.smallpay.co.z/lib/cron/runSagePaymentPostback.php			
----------------------------------------------------------------------------------------------------------------------------
== EXAMPLE OUTPUT ==

###BEGIN LIH_20190131231103 SUCCESSFUL WITH ERRORS 04:22 PM R678.00 20190301
Acc Ref :12300320190131993 Line :3 Branch code is invalid MEMBER PACKAGE: PLATINUM 
Acc Ref :12300320190130994 Line :4 Branch code is invalid MEMBER PACKAGE: GOLD 

###END 04:22 PM

==================================================

###BEGIN	LIH_20190218212517	UNSUCCESSFUL	11:25 PM	R0.00	20190301
###ERROR:	 Unsupported service type      R0.00     20190301

###END	11:25 PM
}
**/
include(__DIR__.'/../includes/sysinit.php');
class SableSagePaymentPostback
{
	private $sableDB, $sumRejectedTxns, $cntRejectedTxns, $amtRejected, $reportTotal, $currBatchId, $sumRejectedRepay;
	var $success, $errorMessage, $successMessage;
	
	function __construct()
	{
		$this->sableDB         	= new SableDB();
		$this->success         	= false;
		$this->errorMessage    	= "";
		$this->successMessage  	= "";
		$this->sumRejectedTxns 	= 0;
		$this->sumRejectedRepay = 0;
		$this->cntRejectedTxns 	= 0;
		$this->amtRejected 		= 0;
	}
	
	function __destruct(){}	
	
	private function getAccountRef($field)
	{
		$tmp = trim($field);
		$p = strpos($field, ':')+1;
		return substr($tmp, $p);
	}
	
	private function getLineNo($field)
	{
		$tmp = trim($field);
		$p = strpos($field, ':')+1;
		return substr($tmp, $p);
	}
	
	private function processData($data)
	{
		$delim = "\t";
		
		//We have all the lines from the file, so now we process the report
		$lines = explode("\n", $data);
		$cnt = 0;
		$lineCount = sizeof($lines);
		$lastLine = intval($lineCount) - 1;
		
		$okCount 	= 0;
		$failCount 	= 0;
		$count		= 0;		
		
		$batchId = 0;
		$doArray = [];
	
		$sql = "insert into disbursement_report_detail 
				(user_code, loan_id, rpt_header_id, rpt_detail_message, rpt_detail_info, rpt_detail_line_no) values ";
		$incd = false;
		$failed = false;
	
		foreach($lines as $line)
		{
			if (empty($line)) continue;
			$fields = explode($delim, $line);
			//SableDebug::debug($fields);
			if ((!$cnt)) 
			{
				if ($fields[0] != '###BEGIN') 
				{
					$this->errorMessage .= 'Header record is missing. File may be corrupt.<br>';
					$failed = true;
					break;
				}
				else
				{
					$dt = date('Y-m-d', strtotime($fields[5]));
					$tm = date('H:i:s', strtotime($fields[3]));
					$ts = date('Y-m-d H:i:s', strtotime($dt.$tm));
		
					$this->reportTotal	= substr($fields[4], 1);
					$batchNameData = explode("_", $fields[1]);
					$batchId 	= $batchNameData[2]; 
					$sqli 		= "insert into disbursement_report_header (disb_batch_id, rpt_result, rpt_value, rpt_time) 
									values 
									(".$batchId.", '".$fields[2]."', ".$this->reportTotal.", '".$ts."')";
					$res = $this->sableDB->updateQuery($sqli);
					if ($res)
						$rptHdrId = $this->sableDB->getValue("select rpt_header_id from disbursement_report_header where disb_batch_id = '".$batchId."'");
					else
					{
						$this->errorMessage .= 'Unable to save the header record to the database. Please contact support.<br>';
						$failed = true;
						break;
					}
				}
			}
			else
			{
				if (($cnt < $lastLine) && (!empty($fields[0])))
				{
					//we process the content of any errors here.
					if ($fields[0] === '###ERROR:')
					{
						$this->errorMessage .= 'File could not be processed: "'.$fields[1].'"<br>';
						//SableDebug::debug($this);
						$failed = true;
					}
					else
					{
						if (!empty($fields[0])) 
						{
							$accRef = $this->getAccountRef($fields[0]);
							$refData = explode('-', $accRef);
							if (($fields[0] !== '###END') && !empty($accRef))
							{	
								$tmp = [];
								//$tmp['rpt_header_id'] 	= $batchId;
								$tmp['user_code'] 		= SableDBFunctions::getUserCodeFromId($refData[0]);
								$tmp['loan_id']			= $refData[1];
								$tmp['user_id'] 		= $refData[0];
								$tmp['rpt_header_id'] 	= $rptHdrId;
								$doArray[] 				= $tmp;
								
								$loanAmount = $this->sableDB->getRow('select COALESCE(updated_amount, loan_amount) as amount_loaned,
																	updated_amount, repay_amount, company_id 
																	from loan 
																	where loan_id = '.$tmp['loan_id']);
								$this->sumRejectedTxns += floatval($loanAmount['amount_loaned']);
								$this->sumRejectedRepay += floatval($loanAmount['repay_amount']);
								$this->cntRejectedTxns++;
								
								$this->sableDB->updateQuery('update disbursement set rejected = true where loan_id = '.$tmp['loan_id']);
								
								//(user_code, loan_id, rpt_header_id, rpt_detail_message, rpt_detail_info, rpt_detail_line_no)
								$sql .= $incd?',':'';
								$sql .= "(".$tmp['user_code'].", ".$tmp['loan_id'].", ".$rptHdrId.", '".$fields[2]."', '".$fields[3]."', ".
										$this->getLineNo($fields[1]).")";
								$incd = true;
							}
						}
					}
				}
			}
			if (($cnt === $lastLine) && ($fields[0] !== '###END')) 
			{
				$this->errorMessage .= 'Footer record is missing. File may be corrupt.';
				$failed = true;
			}
			$cnt++;
		} //for loop
		
		//SableDebug::debug($failed);
		//SableDebug::debugx($this->errorMessage);
		$result = (!$failed && $cnt);
		if ($result)
		{
			$rr = $this->sableDB->updateQuery($sql);
			$this->success = !$failed;
			$this->successMessage = $rr.' records processed.<br>';

			$loanids = $this->sableDB->getValue('select GROUP_CONCAT(loan_id) from disbursement where disb_batch_id = '.$this->currBatchId);
			$res = $this->sableDB->updateQuery('update loan 
												set loan_processed_date = "'.$ts.'", 
												loan_disbursed = 1, 
												loan_processed = 1 ,
												disb_batch_id = '.$this->currBatchId.'
												where loan_id in ('.$loanids.')'); //all set to successful until we've processed the failures: 
	
			foreach($doArray as $do) //failed records
			{
				$sql = "update loan set loan_disbursed = 0, loan_processed_date = null where loan_id = ".$do['loan_id'];
				$res = $this->sableDB->updateQuery($sql);
				if ($res) $okCount++; else $failCount++;	
				$count++;
			}
		}
		
		$this->successMessage .= $count.' loan records rejected. <br>'.$okCount.' processed successfully.<br>'.$failCount.' failed.';
		return $result;
	}
	
	private function getCompanyCommission($companyid)
	{
		$date = $this->sableDB->getValue('select disb_action_date from disbursement_batch where disb_batch_id = '.$this->currBatchId);
		$query="select comm_percent from company__commission where company_id = ".$companyid." and 
		(
			(
				(comm_start_date <= '".$date."') or (comm_start_date is null)
			) 
			and
			(
				(comm_end_date >= '".$date."') or (comm_end_date is null)
			)
		)";	
		return $this->sableDB->getValue($query) / 100;	
	}
	
	private function processCommissions()
	{
		//for each disbursed transaction, we need to process the commission for the transaction:
		//$this->currBatchId
		$commSQL = "select disbursement.company_id, SUM(loan_amount) as loan_sum
					from disbursement 
					inner join disbursement_batch on disbursement_batch.disb_batch_id = disbursement.disb_batch_id 
					inner join company on disbursement.company_id = company.company_id 
					where rejected = 0 and disbursement.disb_batch_id = ".$this->currBatchId." 
					group by disbursement.company_id";
		$commRows = $this->sableDB->getRows($commSQL);
		foreach($commRows as $key=>$comm)
		{
			//get commission percentage for the company for this date:
			$commission = (floatval($comm['loan_sum']) * $this->getCompanyCommission($comm['company_id']));
			$qry = "insert into company_commission (disbursement_batch_id, company_id, capital_disbursed, commission_amount) 
			values (".$this->currBatchId.", ".$comm['company_id'].", ".$comm['loan_sum'].", ".$commission.")";
			$this->sableDB->updateQuery($qry);
		}

	}

	public function processPostBack()
	{
		$row		= $this->sableDB->getRow("select * from sage_config where cfg_id = 1");
		$serviceKey = (SABLE_DEMO_SYSTEM) ? '4863c5c7-f89b-4409-a946-9939f2793e4c' : $row['cfg_service_key'];
		$batch		= $this->sableDB->getRows("select * from disbursement_batch where (disb_result is null) 
												and (disb_run_token is not null)");
		//SableDebug::debugx($batch);
		
		$client = new SoapClient("https://ws.sagepay.co.za/NIWS/niws_nif.svc?wsdl");
		$result = false;
		foreach($batch as $dob)
		{
			$this->currBatchId = $dob['disb_batch_id'];
			$filetoken = $dob['disb_run_token'];
			$response = $client->RequestFileUploadReport(array("ServiceKey" => trim($serviceKey), "FileToken" => trim($filetoken)));
			//SableDebug::debug($response);
			
			$processResult = $this->processData($response->RequestFileUploadReportResult);

			if ($processResult)
			{
//				$rptResult = floatval($this->sumRejectedTxns) === floatval($this->reportTotal) ? 0 : 1;
				$upd = 'update disbursement_report_header set 
						rpt_result = "'.$response->RequestFileUploadReportResult.'", 
						rpt_result_time = "'.date('Y-m-d H:i:s').'" 
						where disb_batch_id = '.$dob['disb_batch_id'];
				$this->sableDB->updateQuery($upd);
				
				$rptResult = (floatval($this->sumRejectedTxns) === 0) && ($this->cntRejectedTxns > 0) ? 1 : 0;
				
				$sql = "update disbursement_batch set 
						disb_result = ".$rptResult.",
						disb_failed_count = ".$this->cntRejectedTxns.",
						disb_failed_total = ".$this->sumRejectedTxns.",
						disb_repay_failed = ".$this->sumRejectedRepay."
						where disb_batch_id = ".$dob['disb_batch_id'];
				$this->sableDB->updateQuery($sql);
				$this->processCommissions();
			}
		} //for loop
		
		unset($client);
	}
}