File: /www/exchange0old/exchange/admin/cron/trading_platform_reports_v2.php
<?php
class Platform_reports_v2 {
private $files_location;
private $accounts_journal;
private $function_sign = ',';
private $query;
private $start_time;
private $shared_strings = array();
private $styles = array();
private $track_progress = false;
private $report_type = '';
private $excel_dates = array();
private $backdated_merges = array(
INBOUND_DEBTOR_PAYMENT => array(),
TRANSFER_OF_PAYMENT_BY_ORIGINATOR => array(),
PAID_DIRECTLY_TO_ORIGINATOR => array(),
ORIGINATOR_PAYMENT_ON_ACCOUNT => array(),
INBOUND_CREDITOR_PAYMENT => array()
);
function __construct($track_progress = false, $report_type = 'manual') {
include_once(ROOT_FOLDER . 'classes/Lib/PHPExcel.php');
$this->query = new Bin_Query();
/*
$this->query->updateQuery('DROP FUNCTION IF EXISTS `getXLSXDate`;');
$this->query->updateQuery('CREATE FUNCTION `getXLSXDate`(`mysql_date` DATE) RETURNS MEDIUMINT
BEGIN
RETURN DATEDIFF(mysql_date, DATE("1900-01-01")) + 2;
END'); */
$this->track_progress = $track_progress;
$this->report_type = $report_type;
}
function __destruct() {
// $this->query->updateQuery('DROP FUNCTION IF EXISTS getXLSXDate;');
}
function copyFolder($src,$dst)
{
$dir = opendir($src);
@mkdir($dst);
while(false !== ( $file = readdir($dir)) )
{
if (( $file != '.' ) && ( $file != '..' ) && ($file!='.svn'))
{
if ( is_dir($src . '/' . $file) )
{
$this->copyFolder($src . '/' . $file,$dst . '/' . $file);
}
else
{
copy($src . '/' . $file,$dst . '/' . $file);
}
}
}
closedir($dir);
}
function deleteDir($dirPath)
{
if (substr($dirPath, strlen($dirPath) - 1, 1) != '/') {
$dirPath .= '/';
}
$files = glob($dirPath . '*', GLOB_MARK);
foreach ($files as $file) {
if (is_dir($file)) {
self::deleteDir($file);
} else {
unlink($file);
}
}
rmdir($dirPath);
}
function init()
{
ini_set('memory_limit', '2000M');
include_once(ROOT_FOLDER.'admin/classes/Model/MStatements.php');
include_once(ROOT_FOLDER.'admin/classes/Model/reports/aj2_reports.php');
chdir(ROOT_FOLDER);
$this->files_location=sys_get_temp_dir().DIRECTORY_SEPARATOR.uniqid('tpr_').DIRECTORY_SEPARATOR;
if (!file_exists($this->files_location))
{
mkdir($this->files_location,0777);
}
$this->copyFolder(pathinfo(__FILE__,PATHINFO_DIRNAME).DIRECTORY_SEPARATOR.'xlsx_templates_v2',$this->files_location);
$this->accounts_journal = new AJ2_reports();
}
function saveReports()
{
$time_start = time();
$params = array();
$this->init();
$this->checkRates();
$this->initTemplates();
$addCommissions=$this->addCommissions('logapprove_date');
$addCommissionsDRP=$this->addCommissions('drp_date');
$params['addCommissions'] = $addCommissions;
$params['addCommissionsDRP'] = $addCommissionsDRP;
if ($this->track_progress)
{
$this->query->updateQuery('UPDATE trp_requests SET completed=completed+10 WHERE request_id='.$this->track_progress);
}
foreach(array('addCN','addSD','addDRP','addReserveAdjustments','addInboundPaymentsDETR','addReturnedPayments','addInboundPaymentsCETR','addCETR','addDETR','addRates','addOPATransactions','addDailySummary','addRPCEtr') as $function)
{
$$function=$this->$function();
//$$function=10;
$params[$function] = $$function;
if ($this->track_progress)
{
$this->query->updateQuery('UPDATE trp_requests SET completed=completed+5 WHERE request_id='.$this->track_progress);
}
}
$addOriginatorReserveReport=$this->addOriginatorReserveReport($addDETR,$addInboundPaymentsDETR,$addCN,$addSD,$addReturnedPayments,$addDRP,$addCETR,$addInboundPaymentsCETR,$addReserveAdjustments,$addOPATransactions);
$params['addOriginatorReserveReport'] = $addOriginatorReserveReport;
$this->addSystemChecks($addDETR,$addCETR,$addCommissions,$addCommissionsDRP,$addCN,$addSD,$addInboundPaymentsDETR,$addInboundPaymentsCETR,$addDRP,$addReserveAdjustments,$addReturnedPayments,$addOPATransactions,$addOriginatorReserveReport);
$this->addSummary($addRates,$addDETR,$addCETR,$addCommissions,$addCommissionsDRP,$addCN,$addSD,$addInboundPaymentsDETR,$addInboundPaymentsCETR,$addDRP,$addReserveAdjustments,$addReturnedPayments,$addOPATransactions,$addOriginatorReserveReport);
$balanceReport = $this->addOutstandingBalances();
// $this->addAccountsOld(null, $balanceReport);
$this->addAccounts(null, $balanceReport);
$this->addStatutoryAccounts();
$this->buildExelDates();
// $this->addTranslationFx();
$this->addJournals();
$this->addUnallocatedPayments();
// $this->addMarginCalls();
$this->addOriginatorProfiles();
$this->addCredebtorConcentration();
// * $this->addKMI();
// * $this->addRevenueWeekly();
$this->writeSharedStrings();
$xlsx_name='TPR-'.date('Y-m-d-H-i').'.xlsx';
if ($this->report_type=='manual')
{
$this->createXlsx($xlsx_name);
}
if ($this->track_progress)
{
if ($this->report_type=='manual')
{
$this->moveToStorage($xlsx_name);
$this->query->updateQuery('UPDATE trp_requests SET
completed=100,
status=1,
completed_at=NOW(),
file_name="'.$xlsx_name.'"
WHERE request_id='.$this->track_progress);
}
elseif($this->report_type=='weekly_tpr')
{
// $params['shared_strings'] = $this->shared_strings;
// $params = serialize($params);
$this->query->updateQuery('UPDATE trp_requests SET
completed=99,
status=3,
source_data="' . addslashes($this->files_location) . '"
WHERE request_id='.$this->track_progress);
}
}
}
function createXlsx($xlsx_name)
{
$zip = new ZipArchive();
$zip->open(sys_get_temp_dir().DIRECTORY_SEPARATOR.$xlsx_name, ZipArchive::CREATE | ZipArchive::OVERWRITE);
$files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($this->files_location),RecursiveIteratorIterator::LEAVES_ONLY);
foreach ($files as $name => $file)
{
if (!$file->isDir())
{
$zip->addFile(realpath($name), str_replace($this->files_location,'',$file));
}
else
{
$zip->addEmptyDir(str_replace($name . '/', '', $file . '/'));
}
}
$zip->close();
}
function moveToStorage($xlsx_name)
{
require_once(ROOT_FOLDER."classes/Storage.php");
Storage_Repository::get(Storage_Repository::OTHER)->setFromUpload($xlsx_name,array('tmp_name'=>sys_get_temp_dir().DIRECTORY_SEPARATOR.$xlsx_name));
$this->deleteDir($this->files_location);
}
function completeReport($source_data)
{
$this->files_location=$source_data;
$this->query->executeQuery('SELECT DATE(date) as date
FROM daily_summary
GROUP BY date
ORDER BY date DESC
LIMIT 2');
$last_dates=$this->query->records;
$result=$this->getDailySummary(array($last_dates[0]['date'],$last_dates[1]['date']));
$result=implode("",$result['final_result']);
$data=file_get_contents($source_data.'xl/worksheets/sheet14.xml');
$data=str_replace("<friday_bank_closing_positions>",$result,$data);
file_put_contents($source_data.'xl/worksheets/sheet14.xml',$data);
$xlsx_name='TPR-'.date('Y-m-d-H-i').'.xlsx';
$this->createXlsx($xlsx_name);
$this->moveToStorage($xlsx_name);
$this->query->updateQuery('UPDATE trp_requests SET
completed=100,
status=1,
completed_at=NOW(),
file_name="' . $xlsx_name . '"
WHERE request_id=' . $this->track_progress);
}
function addBackdatedPayments()
{
ini_set('memory_limit', '2000M');
include_once(ROOT_FOLDER.'admin/classes/Model/MStatements.php');
include_once(ROOT_FOLDER.'admin/classes/Model/reports/aj2_reports.php');
$db = Bin_Db::connect();
chdir(ROOT_FOLDER);
$this->initTemplates();
$this->query->updateQuery('UPDATE trp_requests SET completed = 99
WHERE request_id=' . $this->track_progress);
$selectRequest = "SELECT * FROM trp_requests WHERE request_id = :request_id";
$request = $db->query($selectRequest, array(':request_id' => $this->track_progress))->getFirstRow();
$requestParams = unserialize(array_get($request, 'params'));
$requestDate = array_get($request, 'is_weekly');
$requestDateObject = $requestDate ? new DateTime($requestDate) : new DateTime();
$_REQUEST['start_date'] = FIRST_TRADE_DATE;
$_REQUEST['end_date'] = $requestDate;
$this->accounts_journal = new AJ2_reports();
if ($request and is_array($requestParams) and !empty($requestParams) and $requestDate) {
$this->files_location = array_get($request, 'source_data');
/**
* @var $addCommissions
* @var $addCommissionsDRP
* @var $addCN
* @var $addSD
* @var $addDRP
* @var $addReserveAdjustments
* @var $addInboundPaymentsDETR
* @var $addReturnedPayments
* @var $addInboundPaymentsCETR
* @var $addCETR
* @var $addDETR
* @var $addRates
* @var $addOPATransactions
* @var $addDailySummary
* @var $addRPCEtr
* @var $addOriginatorReserveReport
* @var $shared_strings
*/
extract($requestParams);
$this->shared_strings = $shared_strings;
$backDatedResult = $this->checkBackDatedPayments($requestDate);
$this->checkBackdatedMerges($requestDate);
foreach ($backDatedResult as $function => $exec) {
if ($function === 'addOriginatorReserveReport') {
continue;
}
if ($exec) {
$$function = $this->$function($requestDate);
}
}
// $addOriginatorReserveReport = $this->addOriginatorReserveReport($addDETR, $addInboundPaymentsDETR, $addCN, $addSD, $addReturnedPayments, $addDRP, $addCETR, $addInboundPaymentsCETR, $addReserveAdjustments);
$reserveReportSummary = $this->getReserveReportSummary($addOriginatorReserveReport, $addDETR, $addInboundPaymentsDETR, $addCN, $addSD, $addReturnedPayments, $addDRP, $addCETR, $addInboundPaymentsCETR, $addReserveAdjustments);
$reserveReportSummary = implode("", $reserveReportSummary);
$data = file_get_contents($this->files_location . 'xl/worksheets/sheet15.xml');
$data = str_replace("<reserve_report_summary_backdated_payments>", $reserveReportSummary, $data);
file_put_contents($this->files_location . 'xl/worksheets/sheet15.xml', $data);
// $this->addSystemChecks($addDETR, $addCETR, $addCommissions, $addCommissionsDRP, $addCN, $addSD, $addInboundPaymentsDETR, $addInboundPaymentsCETR, $addDRP, $addReserveAdjustments, $addReturnedPayments, $addOPATransactions, $addOriginatorReserveReport);
$this->addSummary($addRates, $addDETR, $addCETR, $addCommissions, $addCommissionsDRP, $addCN, $addSD, $addInboundPaymentsDETR, $addInboundPaymentsCETR, $addDRP, $addReserveAdjustments, $addReturnedPayments, $addOPATransactions, $addOriginatorReserveReport);
$balanceReport = $this->addOutstandingBalances($requestDateObject);
$this->addAccounts($requestDate, $balanceReport);
$this->writeSharedStrings();
$xlsx_name = 'TPR-' . date('Y-m-d-H-i') . '.xlsx';
$this->createXlsx($xlsx_name);
$this->moveToStorage($xlsx_name);
$this->query->updateQuery('UPDATE trp_requests SET
completed=100,
status=1,
completed_at=NOW(),
file_name="' . $xlsx_name . '"
WHERE request_id=' . $this->track_progress);
}
}
function checkBackDatedPayments($requestDate)
{
$db = Bin_Db::connect();
$result = array(
'addInboundPaymentsDETR' => false,
'addReturnedPayments' => false,
'addInboundPaymentsCETR' => false,
'addOPATransactions' => false,
'addRPCEtr' => false,
'addOriginatorReserveReport' => false,
);
$selectAdditionalPayments = "SELECT mt.manual_transaction_id,
mt.amount,
mt.debtor_id,
IF(dd.currency_id IS NULL, c2.currency_code, c.currency_code) as ccy,
mt.transaction_type,
mt.transaction_date,
mt.created_at,
mt.authorised_at,
mt.notes,
mt2.manual_transaction_id as parent_transaction,
mt2.transaction_type as parent_type,
mt2.transaction_date as parent_transaction_date,
mt2.created_at as parent_created,
mt2.notes as parent_notes
FROM manual_transactions mt
LEFT JOIN transaction_relations tr ON tr.transaction_id = mt.manual_transaction_id
LEFT JOIN manual_transactions_rejected mt2 ON mt2.manual_transaction_id = tr.parent_transaction_id
LEFT JOIN debtors_detail dd ON dd.debtor_id = mt.debtor_id
LEFT JOIN currencies c ON c.currency_id = dd.currency_id
LEFT JOIN currencies c2 ON c2.currency_id = mt.currency_id
WHERE mt.transaction_type IN (1, 2, 9, 7, 13, 19, 107, 108, 110)
AND DATE(mt.created_at) >= ':request_date'
AND mt.transaction_date < ':request_date'
AND mt.status = 1";
$additionalPayments = $db->query($selectAdditionalPayments, array(':request_date' => $requestDate))->getResultArray();
foreach ($additionalPayments as $ap) {
switch ($ap['transaction_type']) {
case 1:
case 2:
case 9:
$result['addInboundPaymentsDETR'] = true;
$result['addOriginatorReserveReport'] = true;
break;
case 13:
$result['addReturnedPayments'] = true;
$result['addOriginatorReserveReport'] = true;
$result['addInboundPaymentsDETR'] = true;
break;
case 19:
$result['addInboundPaymentsCETR'] = true;
$result['addOriginatorReserveReport'] = true;
$result['addOPATransactions'] = true;
break;
case 7:
case 110:
$result['addOPATransactions'] = true;
// $result['addOriginatorReserveReport'] = true;
break;
case 107:
case 108:
$result['addRPCEtr'] = true;
$result['addInboundPaymentsCETR'] = true;
$result['addOPATransactions'] = true;
break;
default:
break;
}
}
return $result;
}
function checkBackdatedMerges($requestDate)
{
$db = Bin_Db::connect();
$result = array();
$selectAdditionalPayments = "SELECT mt.manual_transaction_id,
mt.amount,
mt.debtor_id,
IF(dd.currency_id IS NULL, c2.currency_code, c.currency_code) as ccy,
mt.transaction_type,
mt.transaction_date,
mt.created_at,
mt.authorised_at,
mt.notes,
mt2.manual_transaction_id as parent_transaction,
mt2.transaction_type as parent_type,
mt2.transaction_date as parent_transaction_date,
DATE(mt2.created_at) as parent_created,
mt2.notes as parent_notes
FROM manual_transactions mt
LEFT JOIN transaction_relations tr ON tr.transaction_id = mt.manual_transaction_id
LEFT JOIN manual_transactions_rejected mt2 ON mt2.manual_transaction_id = tr.parent_transaction_id
LEFT JOIN debtors_detail dd ON dd.debtor_id = mt.debtor_id
LEFT JOIN currencies c ON c.currency_id = dd.currency_id
LEFT JOIN currencies c2 ON c2.currency_id = mt.currency_id
WHERE mt.transaction_type IN (1, 2, 9, 7, 19)
AND DATE(mt.created_at) > ':request_date'
AND mt.transaction_date >= ':request_date'
AND mt.status = 1
AND tr.parent_transaction_id IS NOT NULL";
$additionalPayments = $db->query($selectAdditionalPayments, array(':request_date' => $requestDate))->getResultArray();
$groupedResult = array();
foreach ($additionalPayments as $ap) {
$groupedResult[$ap['manual_transaction_id']][] = $ap;
}
foreach ($groupedResult as $key => $group) {
if (count($group) > 1) {
foreach ($this->getBackdatedMerges($key, $requestDate) as $backdatedMerge) {
$result[$backdatedMerge['transaction_type']][] = $backdatedMerge['manual_transaction_id'];
}
}
}
$this->backdated_merges = $result;
}
function getBackdatedMerges($transactionId, $requestDate)
{
$db = Bin_Db::connect();
$result = array();
$selectParents = "SELECT mtr.manual_transaction_id, mtr.transaction_type,
mtr.transaction_date, DATE(mtr.created_at) as created_at
FROM manual_transactions_rejected mtr
JOIN transaction_relations tr ON tr.parent_transaction_id = mtr.manual_transaction_id
WHERE tr.transaction_id = :transaction_id";
$parents = $db->query($selectParents, array(':transaction_id' => $transactionId,))->getResultArray();
foreach ($parents as $parent) {
if ($parent['transaction_date'] <= $requestDate) {
$result[] = $parent;
} else {
$result = array_merge($result, $this->getBackdatedMerges($parent['manual_transaction_id'], $requestDate));
}
}
return $result;
}
function indexToColumn($index)
{
return PHPExcel_Cell::stringFromColumnIndex($index);
}
function getXMLRow($data, $row_id, $additional_styles = array(), $not_shared = array(), $formula_in_header = false, $template = null)
{
$result='<row r="'.$row_id.'" spans="1:'.count($data).'">';
$data_keys=array_flip(array_keys($data));
$end_date=$this->getExcelEndDate();
$magicDate = new DateTime(date('Y-m-d', strtotime('Dec 31')));
$magic_date=$this->getExcelEndDate($magicDate);
$non_empty_value=FALSE;
foreach($data as $index=>$field_value)
{
$field_name='';
if (is_string($index) AND strlen($index)>3)
{
$field_name=$index;
$index=$data_keys[$index];
}
$is_shared=(!in_array($field_name,array('invocie_no','trade_reference_id','manual_transaction_id','amount','purchase_discount','sell_rate','processing_fee','c_etr_commission','edso','target_split','eur_eur','gbp_eur','usd_eur','gbp_gbp','usd_usd','date','1001_','1002_','1003_','1001_adj','1002_adj','1003_adj','1101_','1102_','1103_','1201_','1201_adj','1401_','1402_','5000_','ADSO_45','oetr_value','commission_fee')) AND !preg_match('/(_date|_rate|_amount|_nshared)/si',$field_name));
foreach ($not_shared as $ns) {
if (isset($ns['row']) and isset($ns['column'])) {
if ($row_id == $ns['row'] and $index == $ns['column']) {
$is_shared = false;
}
}
}
$column_sign=(is_string($index) AND strlen($index)<=2)?$index:$this->indexToColumn($index);
if($template === 'xl/worksheets/sheet20.xml' and $row_id == 1){
$style_id = 41;
}
elseif($template === 'xl/worksheets/sheet33.xml' and $row_id == 5){
$style_id = 1;
}
elseif($template === 'xl/worksheets/sheet33.xml' and $row_id == 4){
$style_id = 6;
}
elseif ($row_id==1)
{
$style_id=1;
}
elseif(isset($additional_styles[$column_sign.$row_id]))
{
$style_id=$additional_styles[$column_sign.$row_id];
}
elseif(isset($this->styles[$column_sign]))
{
$style_id=$this->styles[$column_sign];
}
else
{
$style_id=FALSE;
}
$is_formula=(strlen($field_value)>0 AND $field_value[0]==='=');
if ($row_id == 1 and $is_formula and $formula_in_header) {
$style_id = 3;
}
if ($style_id == 2
and !$is_formula
and !empty($field_value)
and !in_array($field_value, array('40909', $end_date, $magic_date))
and $template != 'xl/worksheets/sheet19.xml') {
$field_value = $this->getXLSXDate(strtotime($is_shared ? $this->getSharedStringId($field_value) : $field_value));
}
$result.='<c r="'.$column_sign.$row_id.'" '.(($is_shared AND !$is_formula)?('t="s"'):'').' '.(($style_id)?(' s="'.$style_id.'"'):'');
if (empty($field_value) and ($template === null OR ($template === 'xl/worksheets/sheet29.xml' and !is_float($field_value))))
{
$result.='/>';
continue;
}
$non_empty_value=TRUE;
$result.='>';
if ($is_formula)
{
$result.='<f>'.htmlentities(substr($field_value,1,strlen($field_value)-1),ENT_COMPAT,'UTF-8').'</f>';
}
else
{
$result.='<v>'.($is_shared?$this->getSharedStringId($field_value):$field_value).'</v>';
}
$result.='</c>';
}
return preg_replace('/<c r="[A-Z0-9]+"\s+\/>/si','',$result.'</row>');
if ($non_empty_value)
{
return preg_replace('/<c r="[A-Z0-9]+"\s+\/>/si','',$result.'</row>');
}
return $result.'</row>';
}
function writeSheetHeader($file_name,$columns,$last_row, $formula_in_header = false)
{
$skip_header=in_array($file_name,array('xl/worksheets/sheet16.xml','xl/worksheets/sheet18.xml','xl/worksheets/sheet19.xml','xl/worksheets/sheet20.xml','xl/worksheets/sheet32.xml'));
$file_name=$this->files_location.$file_name;
@unlink($file_name);
$last_column=(empty($columns) OR $skip_header)?'K':$this->indexToColumn(count($columns)-1);
file_put_contents($file_name,'<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1:'.$last_column.($last_row+1).'" />
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0" '.((empty($columns) OR $skip_header)?'/':'').'>');
chmod($file_name,0777);
if (!empty($columns) OR $skip_header)
{
file_put_contents($file_name,'<pane xSplit="16710" ySplit="600" topLeftCell="R2" activePane="bottomRight" />
<selection pane="topRight" activeCell="R1" sqref="R1" />
<selection pane="bottomLeft" activeCell="A2" sqref="A2" />
<selection pane="bottomRight" activeCell="R2" sqref="R2" />',FILE_APPEND);
}
file_put_contents($file_name,((empty($columns) OR $skip_header)?'':'</sheetView>').'
</sheetViews>
<sheetFormatPr defaultRowHeight="10.5" />',FILE_APPEND);
if (empty($columns))
{
file_put_contents($file_name,'<sheetData>',FILE_APPEND);
return ;
}
file_put_contents($file_name,'<cols>',FILE_APPEND);
$index=0;
foreach($columns as $column)
{
$width='width="10" customWidth="1"';
if (isset($this->styles[PHPExcel_Cell::stringFromColumnIndex($index)]))
{
switch($this->styles[PHPExcel_Cell::stringFromColumnIndex($index)])
{
//Originator ID
case -1:{
$width='width="15.85515625" customWidth="1"';
break;
}
//Originator Name
case -2:{
$width='width="30.14515625" customWidth="1"';
break;
}
case -4:{
$width='width="31.50000" customWidth="1"';
break;
}
case -5:{
$width='width="45.00000" customWidth="1"';
break;
}
case -6:{
$width='width="35.00000" customWidth="1"';
break;
}
case 28;
case 2:{
$width='width="13.785125" customWidth="1"';
break;
}
case 31:{
$width='width="17.500000" customWidth="1"';
break;
}
case 4;
case 3:
case 42:{
$width='width="15.000000" customWidth="1"';
break;
}
case 5:{
$width='width="18.85515625" customWidth="1"';
break;
}
}
}
file_put_contents($file_name,'<col min="'.($index+1).'" max="'.($index+1).'" '.$width.' />',FILE_APPEND);
$index++;
}
if($skip_header)
{
file_put_contents($file_name,'</cols>
<sheetData>',FILE_APPEND);
return ;
}
file_put_contents($file_name,'</cols>
<sheetData>'.$this->getXMLRow($columns, 1, array(), array(), $formula_in_header),FILE_APPEND);
}
function writeSheetFooter($file_name)
{
file_put_contents($this->files_location.$file_name,' </sheetData>
<sheetProtection formatCells="0" formatColumns="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0" deleteRows="0" sort="0" autoFilter="0" pivotTables="0" />
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />',FILE_APPEND);
/*if ($file_name === 'xl/worksheets/sheet18.xml') {
file_put_contents($this->files_location . $file_name, '<mergeCells>
<mergeCell ref="H98:I98"/>
</mergeCells>', FILE_APPEND);
}*/
file_put_contents($this->files_location . $file_name, '</worksheet>', FILE_APPEND);
}
function getSharedStringId($string)
{
if (isset($this->shared_strings[$string]))
{
return $this->shared_strings[$string];
}
$last_index=count($this->shared_strings);
$this->shared_strings[$string]=$last_index;
return $last_index;
}
function writeSharedStrings()
{
$file_name=$this->files_location.'xl/sharedStrings.xml';
@unlink($file_name);
if (count($this->shared_strings)==0)
{
return ;
}
file_put_contents($file_name,'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="'.count($this->shared_strings).'" uniqueCount="'.count($this->shared_strings).'">');
$result=array();
foreach($this->shared_strings as $shared_string=>$index)
{
$result[]='<si><t>'.htmlspecialchars($shared_string,ENT_COMPAT).'</t></si>';
if (count($result)>100)
{
file_put_contents($file_name,implode("",$result),FILE_APPEND);
$result=array();
}
}
file_put_contents($file_name,implode("",$result).'</sst>',FILE_APPEND);
}
function checkRates()
{
$query = new Bin_Query();
$query->executeQuery('SELECT DATEDIFF(MAX(currency_date),MIN(currency_date)) as existing_dates, DATEDIFF(CURDATE(),MIN(currency_date)) as needed_dates, ROUND(COUNT(*)/2-1,0) as real_dates
FROM currency_rates');
if ($query->records[0]['existing_dates']==$query->records[0]['needed_dates'] AND $query->records[0]['needed_dates']==$query->records[0]['real_dates'])
{
return TRUE;
}
list($start_time, $end_time) = array(new DateTime('2013-07-04'), new DateTime(date('Y-m-d')));
$period = new DateInterval('P1D');
for($date=$start_time;$date<=$end_time;$date=$date->add($period))
{
$query->executeQuery('SELECT *
FROM currency_rates
WHERE currency_date="'.$date->format('Y-m-d').'"');
if (count($query->records)==2)
{
continue;
}
$query->executeQuery('SELECT *
FROM currency_rates
WHERE currency_date<DATE("'.$date->format('Y-m-d').'")
ORDER BY currency_date DESC
LIMIT 2');
$previous_dates = $query->records;
$query->updateQuery("DELETE FROM currency_rates WHERE currency_date = '" . $date->format('Y-m-d') . "'");
foreach($previous_dates as $previous_date)
{
$query->updateQuery('INSERT INTO currency_rates (currency_id,currency_rate,currency_date) VALUES('.$previous_date['currency_id'].','.$previous_date['currency_rate'].',"'.$date->format('Y-m-d').'")');
}
}
$query->updateQuery('DELETE FROM currency_rates WHERE currency_date>CURDATE()');
}
function saveXLSX($file_name)
{
$writer = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$writer->save($this->files_location.$file_name.'.xlsx');
}
function getDailySummary($specific_dates=FALSE,$last_date='')
{
$db = Bin_Db::connect();
$this->query->executeQuery('SELECT d_s.daily_summary as daily_summary, DATE(d_s.date) as date,b_a.bank_account_no as bank_account_no,IFNULL(b_a.names, d_s.type) as name,accounts_journal.aj_account_id, DATE(date) as real_date
FROM daily_summary as d_s
LEFT JOIN bank_account as b_a ON d_s.account_id = b_a.account_id
LEFT JOIN currencies as cur ON b_a.currency_id = cur.currency_id
LEFT JOIN accounts_journal ON accounts_journal.bank_account_id = b_a.bank_account_no
'.($specific_dates?(' WHERE DATE(d_s.date) IN ("'.implode('","',$specific_dates).'") '):'').'
ORDER BY d_s.date DESC, d_s.account_id, d_s.type');
$investorSummary = $db->query("SELECT * FROM investor_accounts_summary")->getResultArray();
$investorSummaryValues = array();
foreach ($investorSummary as $investorSum) {
$investorSummaryValues[$investorSum['date']] = $investorSum;
}
$result=array();
$initial_item=array(
'summary_date'=>'',
'1001_'=>0,
'1001_adj'=>0,
'1002_'=>0,
'1002_adj'=>0,
'1003_'=>0,
'1003_adj'=>0,
'1101_'=>0,
'1102_'=>0,
'1103_'=>0,
'1201_'=>0,
'1201_adj'=>0,
'1401_'=>0,
'1402_'=>0,
'5000_'=>0,
'Current_Bank_Balances_GBP'=>'',
'Current_Bank_Balances_USD'=>'',
'full_dte'=>'',
'real_date'=>''
);
$account_headers=array(
'summary_dte'=>'Date',
'1001#'=>0,
'1001_adj#'=>'1001 - Adjustment',
'1002#'=>0,
'1002_adj#'=>'1002 - Adjustment',
'1003#'=>0,
'1003_adj#'=>'1003 - Adjustment',
'1101#'=>0,
'1102#'=>0,
'1103#'=>0,
'1201#'=>0,
'1201_adj#'=>'1201 - Adjustment',
'1401#'=>0,
'1402#'=>0,
'5000#'=>0,
'Current_Bank_Balances_GBP'=>'Current Bank Balances GBP',
'Current_Bank_Balances_USD'=>'Current Bank Balances USD',
'full_dte' => 'Dates',
'5000IB' => '5000 - Investabill ETR Funds',
'5000LB' => '5001 - Leasabill ETR Funds',
'8110#' => '8110 - Deferred Yields',
'8111#' => '8111 - Crystallised Yields',
);
foreach($this->query->records as $item)
{
if (empty($item['aj_account_id']))
{
$item['aj_account_id']='5000';
}
if (empty($result[$item['date']]))
{
$result[$item['date']]=$initial_item;
$result[$item['date']]['summary_date']=$item['date'];
$result[$item['date']]['real_date']=$item['real_date'];
}
$result[$item['date']][$item['aj_account_id'].'_']=$item['daily_summary'];
if (empty($account_headers[$item['aj_account_id'].'#']))
{
$account_headers[$item['aj_account_id'].'#']=$item['aj_account_id'].' - '.$item['name'].' ('.$item['bank_account_no'].')';
}
}
$final_result=array();
$index=2;
if($this->report_type=='weekly_tpr')
{
$final_result[]='<friday_bank_closing_positions>';
$index+=2;
array_shift($result);
}
$this->styles=array(
'A'=>2,
'B'=>3,
'C'=>3,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'H'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M' => 3,
'O' => 3,
'P' => 3,
'Q' => 3,
'R' => 3,
'S' => 3,
'T' => 3,
'U' => 3,
'V' => 3,
);
foreach($result as $item)
{
$item['Current_Bank_Balances_GBP']='=C'.$index.'+F'.$index.'+J'.$index;
$item['Current_Bank_Balances_USD']='=D'.$index.'+G'.$index;
$full_dates=('=("'.$item['real_date'].'")');
if ($last_date=='')
{
$last_date=$item['real_date'];
}
if ($last_date<>$item['real_date'])
{
if ((strtotime($last_date)-86400)!=strtotime($item['real_date']))
{
$full_dates=array();
for($date_id=strtotime($item['real_date']);$date_id<strtotime($last_date);$date_id+=86400)
{
$full_dates[]=date('Y-m-d',$date_id);
}
$full_dates='=("'.implode(',',$full_dates).'")';
}
$last_date=$item['real_date'];
}
$item['full_dte']=$full_dates;
if (isset($investorSummaryValues[$item['real_date']])) {
$item['5000IB_amount'] = $investorSummaryValues[$item['real_date']]['investabill_etr_funds'];
$item['5000LB_amount'] = $investorSummaryValues[$item['real_date']]['leasabill_etr_funds'];
$item['8110_amount'] = $investorSummaryValues[$item['real_date']]['deferred_yields'];
$item['8111_amount'] = $investorSummaryValues[$item['real_date']]['crystallised_yields'];
$item['1001_adj'] = $investorSummaryValues[$item['real_date']]['1001_adj'];
$item['1002_adj'] = $investorSummaryValues[$item['real_date']]['1002_adj'];
$item['1003_adj'] = $investorSummaryValues[$item['real_date']]['1003_adj'];
$item['1201_adj'] = $investorSummaryValues[$item['real_date']]['1201_adj'];
}
if (empty($final_result)) {
$bankAdjustments = $this->getBankAdjustments(new DateTime($item['real_date']));
foreach ($bankAdjustments as $key => $value) {
$item[$key] = $value;
}
}
unset($item['real_date']);
$final_result[]=$this->getXMLRow($item,$index);
$index++;
}
return array(
'final_result'=>$final_result,
'account_headers'=>$account_headers,
);
}
function getBankAdjustments(DateTime $dateTime)
{
Bin_Config::requireAdminModel('MReportStatements');
$db = Bin_Db::connect();
$selectAdjustments = "SELECT `1001_adj`, `1002_adj`, `1003_adj`, `1201_adj`
FROM investor_accounts_summary
WHERE date = ':date' AND `1001_adj` IS NOT NULL AND `1002_adj` IS NOT NULL
AND `1003_adj` IS NOT NULL AND `1201_adj` IS NOT NULL";
$adjustments = $db->query($selectAdjustments,array(
':date' => $dateTime->format('Y-m-d'),
))->getFirstRow();
if ($adjustments) {
return $adjustments;
}
$statements = new Model_MReportStatements();
$rates = $statements->getCurrencyRates($dateTime->format('Y-m-d'));
$result = array(
'1001_adj' => 0,
'1002_adj' => 0,
'1003_adj' => 0,
'1201_adj' => 0,
);
$batches = array();
$selectTransactions = "SELECT st.*
FROM spool_batches sb
LEFT JOIN spool_transactions st ON st.batch_id = sb.id
WHERE date = :date";
$transactions = $db->query($selectTransactions, array(':date' => $db->escapeValue($dateTime)))->getResultArray();
foreach ($transactions as $transaction) {
$batches[$transaction['batch_id']][] = $transaction;
}
foreach ($batches as $batch) {
$accountKey = null;
$adjustmentAmount = 0;
foreach ($batch as $spoolTransaction) {
if (in_array($spoolTransaction['type'], array(
'etr_purchase_from_investor_to_clearing',
'etr_reserve_from_default_control_to_clearing',
'payment_return_from_investor_to_clearing',
))) {
// select Account Key
if ($spoolTransaction['type'] === 'etr_reserve_from_default_control_to_clearing') {
$accountKey = '1201_adj';
} else {
switch ($spoolTransaction['currency']) {
case 'EUR':
$accountKey = '1001_adj';
break;
case 'GBP':
$accountKey = '1002_adj';
break;
case 'USD':
$accountKey = '1003_adj';
break;
}
}
}
if (in_array($spoolTransaction['type'], array(
'etr_purchase_from_exchange_to_originator',
'etr_purchase_creditor_from_exchange_to_creditor',
'etr_reserve_from_clearing_to_investor',
'payment_return_from_clearing_to_investor',
))) {
$reroutedAccounts = array(
'44513401',
'73119157',
'44513405',
'IE86BARC99021244513401',
);
if (!in_array($spoolTransaction['account_to'], $reroutedAccounts)) {
// select Amount
if ($spoolTransaction['type'] === 'etr_reserve_from_clearing_to_investor') {
// convert amount
$rate = ($spoolTransaction['currency'] === 'EUR') ? 1 : $rates[$spoolTransaction['currency']];
$adjustmentAmount = $spoolTransaction['amount'] * $rate;
} else {
$adjustmentAmount = $spoolTransaction['amount'];
}
}
}
}
if ($accountKey and $adjustmentAmount) {
// add adjustment
$result[$accountKey] += $adjustmentAmount;
}
}
$selectRecord = "SELECT * FROM investor_accounts_summary WHERE date = :date LIMIT 1";
$checkRecord = $db->query($selectRecord, array(':date' => $db->escapeValue($dateTime)))->getFirstRow();
if ($checkRecord) {
$params = array(
':1001_adj' => $result['1001_adj'],
':1002_adj' => $result['1002_adj'],
':1003_adj' => $result['1003_adj'],
':1201_adj' => $result['1201_adj'],
':date' => $dateTime->format('Y-m-d'),
);
$updateAdjustments = "UPDATE investor_accounts_summary
SET `1001_adj` = :1001_adj,
`1002_adj` = :1002_adj,
`1003_adj` = :1003_adj,
`1201_adj` = :1201_adj,
`modified_at` = NOW()
WHERE date = ':date'";
$db->query($updateAdjustments, $params);
} else {
$db->builder()
->insert('investor_accounts_summary')
->values(array_merge($result, array('date' => $dateTime)))
->execute();
}
return $result;
}
function addDailySummary()
{
$file_name='xl/worksheets/sheet14.xml';
$result=$this->getDailySummary();
$this->writeSheetHeader($file_name,$result['account_headers'],count($result['final_result']));
file_put_contents($this->files_location.$file_name,implode("",$result['final_result']),FILE_APPEND);
$this->writeSheetFooter($file_name);
}
function addRates()
{
$this->query->executeQuery('SELECT DATE(gbp_rates.currency_date) as currency_date, gbp_rates.currency_rate as gbp_rate, usd_rates.currency_rate as usd_rate
FROM currency_rates as gbp_rates
LEFT JOIN currency_rates as usd_rates ON usd_rates.currency_date = gbp_rates.currency_date AND usd_rates.currency_id=3
WHERE gbp_rates.currency_id=2');
$file_name='xl/worksheets/sheet12.xml';
$this->styles=array(
'A'=>2,
'B'=>4,
'C'=>4
);
$this->writeSheetHeader($file_name,array('Date','GBP Rate','USD Rate'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addOPATransactions($requestDate = null)
{
$backdatedMerges = false;
if ($requestDate and count($this->backdated_merges[ORIGINATOR_PAYMENT_ON_ACCOUNT]) > 0) {
$backdatedMerges = implode(',', $this->backdated_merges[ORIGINATOR_PAYMENT_ON_ACCOUNT]);
}
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name, organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
DATE(manual_transactions.transaction_date) as transaction_date,
currencies.currency_code, manual_transactions.amount, admin_users.admin_email, "Creditor" as type,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate,
"" as EUR_amount,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(manual_transactions.transaction_type=7,"OCPA","DEPOSIT"),
IF(manual_transactions.is_aetr = 1, "a",IF(manual_transactions.transaction_type=7,organisation_details.etr_type,IF(manual_transactions.orpa_id,"b",organisation_details.etr_type))),
IF(manual_transactions.false_payment<>0, "X", "") as returned
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN currencies ON currencies.currency_id = manual_transactions.currency_id
LEFT JOIN admin_users ON admin_users.admin_user_id = manual_transactions.created_by
LEFT JOIN currency_rates ON currency_rates.currency_id = manual_transactions.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
WHERE transaction_type IN('.ORIGINATOR_PAYMENT_ON_ACCOUNT.','.ORIGINATOR_DEPOSIT.')
' . ($requestDate === null ? '' : ' AND manual_transactions.transaction_date < "' . $requestDate . '"') . '
' . ($backdatedMerges === false ? '' : '
UNION ALL
SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
DATE(manual_transactions.transaction_date) as transaction_date, currencies.currency_code,
manual_transactions.amount, admin_users.admin_email, "Creditor" as type,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate, "" as EUR_amount,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(manual_transactions.transaction_type=7,"OCPA","DEPOSIT"),
IF(manual_transactions.transaction_type=7,organisation_details.etr_type,IF(manual_transactions.orpa_id,"b",organisation_details.etr_type))
FROM manual_transactions_rejected as manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN currencies ON currencies.currency_id = manual_transactions.currency_id
LEFT JOIN admin_users ON admin_users.admin_user_id = manual_transactions.created_by
LEFT JOIN currency_rates ON currency_rates.currency_id = manual_transactions.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
WHERE manual_transaction_id IN(' . $backdatedMerges . ') ') . '
#LIMIT 10');
$file_name='xl/worksheets/sheet13.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>2,
'E'=>3,
'I'=>4,
'J'=>3,
'M'=>6,
'N'=>6,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Date','Ccy','Amount','Creator','Type','Status','Fx rate','EUR Amount','Bank Ccy', 'Type','ETR Type','Returned'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=I'.$index.' * E'.$index;
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
$result[]=$this->getXMLRow(array(
'J'=>'=SUMIFS(J2:J'.($index-1).',H2:H'.($index-1).',"Authorized")'
),$index+6);
$result[]=$this->getXMLRow(array(
'I'=>'EUR',
'J'=>'=SUMIFS(J2:J'.($index-1).',H2:H'.($index-1).',"Authorized",D2:D'.($index-1).',"EUR")'
),$index+7);
$result[]=$this->getXMLRow(array(
'I'=>'GBP',
'J'=>'=SUMIFS(J2:J'.($index-1).',H2:H'.($index-1).',"Authorized",D2:D'.($index-1).',"GBP")'
),$index+8);
$result[]=$this->getXMLRow(array(
'I'=>'USD',
'J'=>'=SUMIFS(J2:J'.($index-1).',H2:H'.($index-1).',"Authorized",D2:D'.($index-1).',"USD")'
),$index+9);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addRPCEtr($requestDate = null)
{
$this->query->executeQuery('SELECT
od.organisation_reference_id,
od.organisation_name,
dd.debtor_reference_id,
dd.debtor_name,
IF(mt.transaction_type = 107, \'OCPA\', \'ICP\') AS transaction_type,
mt.manual_transaction_id,
mtr.manual_transaction_id AS returned_id_nshared,
DATE(mt.transaction_date) as transaction_date,
DATE(DATE(mt.created_at)) as entered_date,
cur.currency_code,
mt.amount,
IF(mt.`status`=1,\'Authorized\',\'Not Authorized\') AS status,
IFNULL(cur_r.currency_rate,1) AS currency_rate,
(mt.amount*IFNULL(cur_r.currency_rate,1)) AS eur_amount,
IF(oc.org_currency_id IS NOT NULL, cur.currency_code, \'EUR\') as bank_cur,
IF(mt.is_aetr = 1, "a", IF(mt.transaction_type = 107, od.etr_type, dd.etr_type))
FROM
manual_transactions mt
LEFT JOIN organisation_details od ON od.user_id = mt.originator_id
LEFT JOIN debtors_detail dd ON dd.debtor_id = mt.debtor_id
LEFT JOIN manual_transactions mtr ON mtr.false_payment = mt.manual_transaction_id
LEFT JOIN currencies cur ON cur.currency_id = mt.currency_id
LEFT JOIN currency_rates cur_r ON cur_r.currency_id = mt.currency_id AND cur_r.currency_date = mt.transaction_date
LEFT JOIN organisation_currency oc ON oc.organisation_id=od.organisation_id AND oc.currency_id = mt.currency_id
WHERE mt.transaction_type IN (107,108)
' . ($requestDate === null ? '' : ' AND mt.transaction_date < "' . $requestDate . '" AND mtr.transaction_date < "' . $requestDate . '"') . '
ORDER BY od.organisation_name, dd.debtor_name, mt.manual_transaction_id
#LIMIT 10');
$file_name='xl/worksheets/sheet28.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>-1,
'D'=>-2,
'H'=>2,
'I'=>2,
'K'=>3,
'N'=>3,
'M'=>4,
'P'=>6,
);
$this->writeSheetHeader($file_name, array(
'Originator ID',
'Originator Name',
'Creditor ID',
'Creditor Name',
'Type',
'Transaction ID',
'Returned ID',
'Date',
'Entered',
'Ccy',
'Amount',
'Status',
'Fx Rate',
'EUR Amount',
'Bank Ccy',
'ETR Type'
), $this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addReturnedPayments($requestedDate = null)
{
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as transaction_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate, "" as EUR_amount,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(manual_transactions.is_aetr = 1, "a", "d") as etr_type
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
LEFT JOIN manual_transactions mtr ON mtr.false_payment = manual_transactions.manual_transaction_id
WHERE manual_transactions.transaction_type='.FALSE_PAYMENT_RETURNED.
($requestedDate === null ? '' : ' AND manual_transactions.transaction_date < "' . $requestedDate . '" AND mtr.transaction_date < "' . $requestedDate . '"') . '
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet8.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'L'=>3,
'I'=>3,
'F'=>2,
'G'=>2,
'K'=>4,
'N'=>6,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','Fx Rate','EUR Amount','Bank Ccy','ETR Type'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=K'.$index.' * I'.$index;
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
$result[]=$this->getXMLRow(array(
'K'=>'=SUMIF(J2:J'.($index-1).$this->function_sign.'"Authorized"'.$this->function_sign.'I2:I'.($index-1).')',
'L'=>'=SUMIF(J2:J'.($index-1).$this->function_sign.'"Authorized"'.$this->function_sign.'L2:L'.($index-1).')'
),$index);
$result[]=$this->getXMLRow(array(
'K'=>'EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"EUR",M2:M'.($index-1).',"EUR")'
),$index+3);
$result[]=$this->getXMLRow(array(
'L'=>'Returned (used in Fx Commission)'
),$index+5);
$result[]=$this->getXMLRow(array(
'K'=>'GBP-EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"GBP",M2:M'.($index-1).',"EUR")'
),$index+6);
$result[]=$this->getXMLRow(array(
'K'=>'USD-EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"USD",M2:M'.($index-1).',"EUR")'
),$index+7);
$result[]=$this->getXMLRow(array(
'K'=>'GBP-GBP',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"GBP",M2:M'.($index-1).',"GBP")'
),$index+8);
$result[]=$this->getXMLRow(array(
'K'=>'USD-USD',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"USD",M2:M'.($index-1).',"USD")'
),$index+9);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addReserveAdjustments()
{
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date),
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code,
IF(manual_transactions.transaction_type='.POSITIVE_RESERVE_ADJUSTMENT.',manual_transactions.amount,-1*manual_transactions.amount) as amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(drp.manual_transaction_id IS NULL,"","X") as reserve_on,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate, "" as EUR_amount,
DATE(drp.transaction_date) as drp_date,
IF(currencies.currency_code="EUR",1.000,IF(drp_rates.currency_rate IS NOT NULL,drp_rates.currency_rate, currencies.currency_rate)) as drp_rate,
"" as drp_amount,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(manual_transactions.notes LIKE "Margin Call", "X", "") as margin_call
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN manual_transactions as drp ON drp.transaction_type=11 AND drp.originator_id = manual_transactions.originator_id AND drp.debtor_id = manual_transactions.debtor_id AND drp.transref_list<>"" AND FIND_IN_SET(manual_transactions.manual_transaction_id,drp.transref_list)>0
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN currency_rates as drp_rates ON drp_rates.currency_id = debtors_detail.currency_id AND drp_rates.currency_date = drp.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
WHERE manual_transactions.transaction_type IN ('.POSITIVE_RESERVE_ADJUSTMENT.','.NEGATIVE_RESERVE_ADJUSTMENT.')
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet6.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'L'=>4,
'K'=>6,
'M'=>3,
'N'=>2,
'O'=>4,
'P'=>3,
'R'=>6,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Adjusted','Entered','Ccy','Adjustment','Status','DRP','Fx rate','EUR Amount','Date','DRP Fx','DRP','Bank Ccy','Margin Call'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=L'.$index.' * I'.$index;
$transaction['drp_amount']='=O'.$index.' * I'.$index;
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
$result[]=$this->getXMLRow(array(
'I'=>'=SUMIF(J2:J'.($index-1).$this->function_sign.'"Authorized"'.$this->function_sign.'I2:I'.($index-1).')',
'M'=>'=SUMIF(J2:J'.($index-1).$this->function_sign.'"Authorized"'.$this->function_sign.'M2:M'.($index-1).')',
'P'=>'=SUMIF(J2:J'.($index-1).$this->function_sign.'"Authorized"'.$this->function_sign.'P2:P'.($index-1).')'
),$index+3);
$result[]=$this->getXMLRow(array(
'P'=>'=SUMIFS(P2:P'.($index-1).$this->function_sign.'K2:K'.($index-1).$this->function_sign.'"X")'
),$index+4);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addDRP()
{
$this->query->executeQuery('SELECT organisation_details.organisation_reference_id, IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name, debtors_detail.debtor_reference_id, IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name, manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date), DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount, IF(manual_transactions.status=1,"Authorized","Not Authorized") as status, IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate, "" as EUR_amount, IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
WHERE transaction_type ='.DATED_RESERVE_PAYMENT.'
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet5.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'K'=>4,
'L'=>3
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','Fx Rate','EUR Amount','Bank Ccy'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=K'.$index.' * I'.$index;
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
$result[]=$this->getXMLRow(array(
'I'=>'=SUMIF(J2:J'.($index-1).',"Authorized",I2:I'.($index-1).')',
'L'=>'=SUMIF(J2:J'.($index-1).',"Authorized",L2:L'.($index-1).')'
),$index);
$result[]=$this->getXMLRow(array(
'K'=>'EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"EUR",M2:M'.($index-1).',"EUR")'
),$index+7);
$result[]=$this->getXMLRow(array(
'K'=>'GBP-EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"GBP",M2:M'.($index-1).',"EUR")'
),$index+8);
$result[]=$this->getXMLRow(array(
'K'=>'USD-EUR',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"USD",M2:M'.($index-1).',"EUR")'
),$index+9);
$result[]=$this->getXMLRow(array(
'K'=>'GBP-GBP',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"GBP",M2:M'.($index-1).',"GBP")'
),$index+10);
$result[]=$this->getXMLRow(array(
'K'=>'USD-USD',
'L'=>'=SUMIFS(L2:L'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"USD",M2:M'.($index-1).',"USD")'
),$index+11);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
public function addSD() {
$sql = /** @lang MySQL */ "SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,
organisation_details.organisation_trade_name,
organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,
debtors_detail.trade_name,
debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id,
DATE(manual_transactions.transaction_date) as sd_date,
DATE(manual_transactions.transaction_date) as created_date,
currencies.currency_code,
manual_transactions.amount,
IF(currencies.currency_code = 'EUR',
1.000,
currency_rates.currency_rate) as currency_rate,
'' as EUR_amount,
DATE(payments.transaction_date) as settled_date,
IF(currencies.currency_code = 'EUR',
1.000,
IF(payment_rates.currency_rate IS NOT NULL,
payment_rates.currency_rate,
currencies.currency_rate)) as payment_rate,
DATE(drp.transaction_date) as drp_date,
IF(currencies.currency_code = 'EUR',
1.000,
IF(drp_rates.currency_rate IS NOT NULL,
drp_rates.currency_rate,
currencies.currency_rate)) as drp_rate,
'' as EUR_Settled_Amount
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN manual_transactions as drp ON drp.transaction_type = " . DATED_RESERVE_PAYMENT . "
AND drp.originator_id = manual_transactions.originator_id
AND drp.debtor_id = manual_transactions.debtor_id
AND drp.transref_list <> ''
AND FIND_IN_SET(manual_transactions.manual_transaction_id, drp.transref_list) > 0
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id
AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN currency_rates as drp_rates ON drp_rates.currency_id = debtors_detail.currency_id
AND drp_rates.currency_date = drp.transaction_date
LEFT JOIN manual_transactions as payments ON payments.reconcile_ref = manual_transactions.reconcile_ref
AND payments.transaction_type IN (" . INBOUND_DEBTOR_PAYMENT . ",
" . TRANSFER_OF_PAYMENT_BY_ORIGINATOR . "," . PAID_DIRECTLY_TO_ORIGINATOR . ")
LEFT JOIN manual_transactions as r_trade ON r_trade.manual_transaction_id = manual_transactions.reconcile_ref
LEFT JOIN currency_rates as r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id
AND r_trade_rates.currency_date = r_trade.transaction_date
LEFT JOIN currency_rates as payment_rates ON payment_rates.currency_id = debtors_detail.currency_id
AND payment_rates.currency_date = payments.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id
AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id
AND oc_eur.currency_id = 1
WHERE manual_transactions.transaction_type = " . SPECIFIC_DEDUCTIBLE . "
ORDER BY organisation_name, debtor_name
#LIMIT 10";
$this->query->executeQuery($sql);
$file_name='xl/worksheets/sheet4.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'J'=>4,
'K'=>3,
'L'=>2,
'O'=>4,
'M'=>4,
'N'=>2,
'P'=>3,
'Q'=>3,
'R'=>3,
'S'=>3,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Issued','Traded','Ccy','Face Value','Traded Fx','EUR Face Value','Settled','Settled Fx','Closed','Closed Fx','EUR Settled','Margin Call','Fx Gain - Settled','Fx Gain - Reserve'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=J'.$index.' * I'.$index;
$transaction['EUR_Settled_Amount']='=M'.$index.' * I'.$index;
$transaction['Margin Call'] = '=IF(ISBLANK(L' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>M' . $index . $this->function_sign . 'I' . $index . '*M' . $index . '-I' . $index . '*J' . $index . $this->function_sign . '0))';
$transaction['Fx Gain - Settled'] = '=IF(ISBLANK(L' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>M' . $index . $this->function_sign . '0' . $this->function_sign . 'I' . $index . '*M' . $index . '-I' . $index . '*J' . $index . '))';
$transaction['Fx Gain - Reserve'] = '=IF(ISBLANK(N' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>O' . $index . $this->function_sign . '0' . $this->function_sign . 'I' . $index . '*O' . $index . '-I' . $index . '*J' . $index . '))';
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
/*$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/sd_tpr.html',array('addSD'=>$index-1),FALSE);
$result=$this->HTMLtoOOXML($html_content,FALSE,array(),FALSE,$index+3,7);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);*/
$this->writeSheetFooter($file_name);
return $index;
}
function addCN()
{
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as cn_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate, "" EUR_amount,
DATE(payments.transaction_date) as settled_date,
IF(currencies.currency_code="EUR",1.000,IF(payment_rates.currency_rate IS NOT NULL,payment_rates.currency_rate, currencies.currency_rate)) as payment_rate,
DATE(drp.transaction_date) as drp_date, IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(currencies.currency_code="EUR",1.000,IF(drp_rates.currency_rate IS NOT NULL,drp_rates.currency_rate, currencies.currency_rate)) as drp_rate
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN manual_transactions as drp ON drp.transaction_type='.DATED_RESERVE_PAYMENT.'
AND drp.originator_id = manual_transactions.originator_id
AND drp.debtor_id = manual_transactions.debtor_id
AND drp.transref_list<>""
AND FIND_IN_SET(manual_transactions.manual_transaction_id,drp.transref_list)>0
LEFT JOIN manual_transactions as r_trade ON r_trade.transaction_type='.RECONCILE_DEBTOR_ACCOUNT.'
AND r_trade.originator_id = manual_transactions.originator_id
AND r_trade.debtor_id = manual_transactions.debtor_id
AND r_trade.transref_list<>""
AND FIND_IN_SET(manual_transactions.manual_transaction_id,r_trade.transref_list)>0
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id
AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN currency_rates as drp_rates ON drp_rates.currency_id = debtors_detail.currency_id
AND drp_rates.currency_date = drp.transaction_date
LEFT JOIN currency_rates as r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id
AND r_trade_rates.currency_date = r_trade.transaction_date
LEFT JOIN manual_transactions as payments ON payments.reconcile_ref = r_trade.manual_transaction_id
AND payments.transaction_type IN ('.INBOUND_DEBTOR_PAYMENT.','.TRANSFER_OF_PAYMENT_BY_ORIGINATOR.','.PAID_DIRECTLY_TO_ORIGINATOR.')
LEFT JOIN currency_rates as payment_rates ON payment_rates.currency_id = debtors_detail.currency_id
AND payment_rates.currency_date = payments.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id
AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id
AND oc_eur.currency_id=1
WHERE manual_transactions.transaction_type = '.CREDIT_NOTE.'
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet3.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'J'=>4,
'K'=>3,
'L'=>2,
'N'=>2,
'M'=>4,
'P'=>4,
'Q'=>3,
'R'=>3,
'S'=>3,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Issued','Traded','Ccy','Face Value','Traded Fx','EUR Face Value','Settled','Settled Fx','Closed','Bank Ccy','Closed Fx','Margin Call','Fx Gain - Settled','Fx Gain - Reserve'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=J'.$index.' * I'.$index;
$transaction['Margin Call'] = '=IF(ISBLANK(L' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>M' . $index . $this->function_sign . 'I' . $index . '*M' . $index . '-I' . $index . '*J' . $index . $this->function_sign . '0))';
$transaction['Fx Gain - Settled'] = '=IF(ISBLANK(L' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>M' . $index . $this->function_sign . '0' . $this->function_sign . 'I' . $index . '*M' . $index . '-I' . $index . '*J' . $index . '))';
$transaction['Fx Gain - Reserve'] = '=IF(ISBLANK(N' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(J' . $index . '>P' . $index . $this->function_sign . '0' . $this->function_sign . 'I' . $index . '*P' . $index . '-I' . $index . '*J' . $index . '))';
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
/* $html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/cn_tpr.html',array('addCN'=>$index-1),FALSE);
$result=$this->HTMLtoOOXML($html_content,FALSE,array(),FALSE,$index+3,7);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);*/
$this->writeSheetFooter($file_name);
return $index;
}
function addInboundPaymentsDETR($requestedDate = null)
{
$backdatedMerges = false;
if ($requestedDate) {
$backdatedMergesResult = array_merge(
$this->backdated_merges[INBOUND_DEBTOR_PAYMENT],
$this->backdated_merges[TRANSFER_OF_PAYMENT_BY_ORIGINATOR],
$this->backdated_merges[PAID_DIRECTLY_TO_ORIGINATOR]
);
$backdatedMerges = count($backdatedMergesResult) > 0 ? implode(',', $backdatedMergesResult) : false;
}
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as transaction_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(manual_transactions.reconcile_ref<>"","R","") as reconcile_status,
IF(manual_transactions.false_payment<>0'.($requestedDate === null ? '':' AND returned_transaction.transaction_date < "'.$requestedDate.'"').',"X","") as returned,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate,
"" as EUR_amount, manual_transactions.transaction_type,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
'.(($requestedDate === null) ? 'DATE(returned_transaction.transaction_date)' : 'IF(returned_transaction.transaction_date < "'.$requestedDate.'",DATE(returned_transaction.transaction_date),"")').' as returned_date,
IF(manual_transactions.is_aetr = 1, "a", "d") as etr_type
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
LEFT JOIN manual_transactions as returned_transaction ON returned_transaction.manual_transaction_id = manual_transactions.false_payment
WHERE manual_transactions.transaction_type IN ('.INBOUND_DEBTOR_PAYMENT.','.PAID_DIRECTLY_TO_ORIGINATOR.','.TRANSFER_OF_PAYMENT_BY_ORIGINATOR.')
' . ($requestedDate === null ? '' : ' AND manual_transactions.transaction_date < "' . $requestedDate . '"') . '
' . ($backdatedMerges === false ? '' : '
UNION ALL
SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as transaction_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(manual_transactions.reconcile_ref<>"","R","") as reconcile_status,
IF(manual_transactions.false_payment<>0'.($requestedDate === null ? '':' AND returned_transaction.transaction_date < "'.$requestedDate.'"').',"X","") as returned,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate,
"" as EUR_amount, manual_transactions.transaction_type,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency, '.(($requestedDate === null) ? 'DATE(returned_transaction.transaction_date)' : 'IF(returned_transaction.transaction_date < "'.$requestedDate.'",DATE(returned_transaction.transaction_date),"")').' as returned_date
FROM manual_transactions_rejected as manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
LEFT JOIN manual_transactions as returned_transaction ON returned_transaction.manual_transaction_id = manual_transactions.false_payment
WHERE manual_transactions.manual_transaction_id IN (' . $backdatedMerges . ') ') . '
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet7.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'K'=>6,
'L'=>6,
'M'=>4,
'N'=>3,
'Q'=>2,
'R'=>6,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','Status','Returned','Fx rate','EUR Amount','Type','Bank Ccy','Returned','ETR Type'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=M'.$index.' * I'.$index;
switch($transaction['transaction_type'])
{
case INBOUND_DEBTOR_PAYMENT:{
$transaction['transaction_type']='IDP';
break;
}
case TRANSFER_OF_PAYMENT_BY_ORIGINATOR:{
$transaction['transaction_type']='TPO';
break;
}
case PAID_DIRECTLY_TO_ORIGINATOR:{
$transaction['transaction_type']='PDO';
break;
}
}
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/dpayments_tpr.html',array('addInboundPaymentsDETR'=>$index-1),FALSE);
$result=$this->HTMLtoOOXML($html_content,FALSE,array(),FALSE,$index+6,8);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addInboundPaymentsCETR($requestDate = null)
{
$backdatedMerges = false;
if ($requestDate and count($this->backdated_merges[INBOUND_CREDITOR_PAYMENT]) > 0) {
$backdatedMerges = implode(',', $this->backdated_merges[INBOUND_CREDITOR_PAYMENT]);
}
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as transaction_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(manual_transactions.reconcile_ref<>"","R","") as reconcile_status,
IF(manual_transactions.false_payment<>0'.($requestDate === null ? '':' AND returned_transaction.transaction_date < "'.$requestDate.'"').',"X","") as returned,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate,
"" as EUR_amount, manual_transactions.transaction_type,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,
IF(manual_transactions.is_aetr = 1, "a", debtors_detail.etr_type) as etr_type
FROM manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
LEFT JOIN manual_transactions as returned_transaction ON returned_transaction.manual_transaction_id = manual_transactions.false_payment
WHERE manual_transactions.transaction_type IN ('.INBOUND_CREDITOR_PAYMENT.')
' . ($requestDate === null ? '' : ' AND manual_transactions.transaction_date < "' . $requestDate . '"') . '
' . ($backdatedMerges === false ? '' : '
UNION ALL
SELECT
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
manual_transactions.manual_transaction_id, DATE(manual_transactions.transaction_date) as transaction_date,
DATE(DATE(manual_transactions.created_at)) as created_date, currencies.currency_code, manual_transactions.amount,
IF(manual_transactions.status=1,"Authorized","Not Authorized") as status,
IF(manual_transactions.reconcile_ref<>"","R","") as reconcile_status,
IF(manual_transactions.false_payment<>0'.($requestDate === null ? '':' AND returned_transaction.transaction_date < "'.$requestDate.'"').',"X","") as returned,
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as currency_rate,
"" as EUR_amount, manual_transactions.transaction_type,
IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency, debtors_detail.etr_type
FROM manual_transactions_rejected as manual_transactions
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.debtor_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1
LEFT JOIN manual_transactions as returned_transaction ON returned_transaction.manual_transaction_id = manual_transactions.false_payment
WHERE manual_transactions.manual_transaction_id IN (' . $backdatedMerges . ') ') . '
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet9.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'K'=>6,
'L'=>6,
'M'=>4,
'N'=>3,
'Q'=>6
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','Status','Returned','Fx rate','EUR Amount','Type','Bank Ccy','ETR Type'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=M'.$index.' * I'.$index;
switch($transaction['transaction_type'])
{
case INBOUND_CREDITOR_PAYMENT:{
$transaction['transaction_type']='ICP';
break;
}
}
$result[]=$this->getXMLRow($transaction,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
$result[]=$this->getXMLRow(array(
'N'=>'=SUMIFS(N2:N'.($index-1).',J2:J'.($index-1).',"Authorized")'
),$index+5);
$result[]=$this->getXMLRow(array(
'M'=>'EUR',
'N'=>'=SUMIFS(N2:N'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"EUR")'
),$index+6);
$result[]=$this->getXMLRow(array(
'M'=>'GBP',
'N'=>'=SUMIFS(N2:N'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"GBP")'
),$index+7);
$result[]=$this->getXMLRow(array(
'M'=>'USD',
'N'=>'=SUMIFS(N2:N'.($index-1).',J2:J'.($index-1).',"Authorized",H2:H'.($index-1).',"USD")'
),$index+8);
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addCETR()
{
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id, IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id, IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
debtors_master.master_reference_id, IF(debtors_master.debtor_trade_name, debtors_master.debtor_trade_name, debtors_master.debtor_name) as master_debtor_name,
DATE(invoice_master.actual_date), invoice_master.trade_reference_id, convert(binary convert(invoice_master.invoice_no using latin1) using utf8) as invoice_no, DATE(DATE(invoice_master.created_at)) as created_date, DATE(DATE(invoice_master.logapprove_date)) as authorized_date, IFNULL(DATEDIFF(DATE(expected_date), DATE(logapprove_date)),0) as edso,0 as rdso,0 as adso, currencies.currency_code, invoice_master.face_value as amount, (invoice_master.payment_discount/100) as purchase_discount,IFNULL(invoice_closed.max_thirty_reserve,0)/100 as sell_rate , IF( currencies.currency_code="EUR",1.000, currency_rates.currency_rate) as currency_rate, "" as EUR_face_value, "" as EUR_amount_paid, IFNULL(invoice_closed_attributes.processing_fee,0) as processing_fee,
IF(main_trans.transaction_type = '.RECONCILE_CREDITOR_ACCOUNT.', DATE(payment_trans.transaction_date) ,"") as reserve_date,
IF( currencies.currency_code="EUR",1.000, IF( payment_trans.transaction_date IS NULL ,currencies.currency_rate, reserve_rates.currency_rate)) as reserve_rate,
IF(invoice_master.reserve IS NULL,0.00,invoice_master.reserve) as commission_fee, "" as EUR_commission_fee, "" as Traded_CETR, "" as Bank_Exchange, "" as EUR_Bank_Exchange,"" as revenue_share,"" as Settled_CETR, "" as Ccy_FV_Due, "" as Ccy_DC_Due, IF(invoice_master.is_aetr = 1, "a", invoice_master.etr_type) as etr_type,
IF(invoice_master.face_value_charge_applied=0,0,IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = "debtor", organisation_details.face_value_charge, organisation_details.creditor_face_value_charge)))/100) as real_face_value_charge
FROM invoice_master
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id
LEFT JOIN debtors_master ON debtors_master.debtor_master_id = debtors_detail.parent_id
LEFT JOIN invoice_closed ON invoice_closed.invoice_id = invoice_master.invoice_id
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions as main_trans ON main_trans.manual_transaction_id = invoice_master.manual_transaction_id AND main_trans.status=1
LEFT JOIN manual_transactions as payment_trans ON main_trans.reconcile_payment = payment_trans.manual_transaction_id AND payment_trans.status=1
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = DATE(invoice_master.logapprove_date)
LEFT JOIN currency_rates as reserve_rates ON reserve_rates.currency_id = debtors_detail.currency_id AND reserve_rates.currency_date = payment_trans.transaction_date
LEFT JOIN currency_rates as r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id AND r_trade_rates.currency_date = main_trans.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = invoice_master.user_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = invoice_master.user_id AND oc_eur.currency_code="EUR"
LEFT JOIN site_settings ON site_settings.site_setting_id=1
WHERE invoice_master.root_invoice_id>0 AND invoice_master.log_status=1 AND debtors_detail.type="creditor"
ORDER BY organisation_name, debtor_name
#LIMIT 10');
$file_name='xl/worksheets/sheet10.xml';
$headers=array('Originator ID','Originator Name','Creditor ID','Creditor Name','Master ID','Master Creditor','Issued','Trade ID','Ref.','Posted','Traded','EDSO','RDSO','ADSO','Ccy','Face Value','Purchased','Sell Rate','Traded Fx','EUR Face Value','EUR Purchased','Trade Commission','Closed','Settled Fx','Commission Fee','EUR Commission Fee',
"4. Traded c-ETR 2104 - Traded c-ETR",
'Bank Exchange','EUR Bank Exchange','Revenue Share',
"5. Settled c-ETR 1903 - c-ETR", 'Ccy Outstanding','Ccy Deferred','ETR Type',
'Margin Calls', 'Settled Fx', 'Trade Profit', 'All Trade Profit', 'Deferred Income',
'EUR Trade Commission', 'Settled Commission','Fixed Charge');
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'E'=>5,
'F'=>-2,
'G' => 2,
'H' => 5,
'J' => 2,
'K' => 2,
'P' => 3,
'Q' => 7,
'R' => 7,
'S' => 4,
'T' => 3,
'U' => 3,
'V' => 3,
'W' => 2,
'X' => 4,
'Y' => 3,
'Z' => 3,
'AA' => 3,
'AB' => 3,
'AC' => 3,
'AD' => 3,
'AE' => 3,
'AF' => 3,
'AG' => 3,
'AI' => 3,
'AJ' => 3,
'AK' => 3,
'AL' => 3,
'AM' => 3,
'AN' => 3,
'AO' => 3,
'AP' => 7,
);
$this->writeSheetHeader($file_name,$headers,$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $invoice)
{
$invoice['adso']='=MAX(L'.$index.$this->function_sign.'M'.$index.')';
$invoice['rdso'] = '=IF(ISBLANK(W' . $index . ')' . $this->function_sign . 'System!$O$6-G' . $index . $this->function_sign . 'W' . $index . '-G' . $index . ')';
$invoice['EUR_face_value']='=S'.$index.' * P'.$index;
$invoice['EUR_amount_paid']='=P'.$index.' * (1 - Q'.$index.') * S'.$index;
// $invoice['Ccy_Trade_Commission']='=Y'.$index.'/T'.$index;
// $invoice['commission_real']='=AB'.$index.' / T'.$index;
// $invoice['purchase_price']='=P'.$index.'*(1-R'.$index.')';
// $invoice['EUR_Purchase_Price']='=AF'.$index.'*T'.$index;
$invoice['commission_fee']='=IF(AP' . $index . '>0%' . $this->function_sign . 'P' . $index . '*AP' . $index . '' . $this->function_sign . 'IF('.$invoice['commission_fee'].'>0,'.$invoice['commission_fee'].',IF(N'.$index.'<=L'.$index.$this->function_sign.'((P'.$index.'*R'.$index.')/30)*L'.$index.$this->function_sign.'((P'.$index.'*R'.$index.')/30)*N'.$index.')))';
$invoice['EUR_commission_fee'] = '=Y' . $index . '*S' . $index;
$invoice['Traded_CETR']='=P'.$index.'*S'.$index;
$invoice['Bank_Exchange'] = '=P' . $index . '*(1-Q' . $index . ')-V' . $index . '/S' . $index;
$invoice['EUR_Bank_Exchange'] = '=P' . $index . '*(1-Q' . $index . ')*S' . $index . '-V' . $index;
// $invoice['Ccy_Bank_Exchange']='=AF'.$index.'-AA'.$index.'/T'.$index;
$invoice['revenue_share']='=IF(R'.$index.'=0'.$this->function_sign.'0'.$this->function_sign.'IF(R'.$index.'<=X'.$index.$this->function_sign.'0'.$this->function_sign.'IF(R'.$index.'<=X'.$index.'*2'.$this->function_sign.'(R'.$index.'-X'.$index.')/R'.$index.$this->function_sign.'50%)))';
$invoice['revenue_share']='check formula';
// $invoice['accruals']='=P'.$index.'*R'.$index.'*AN'.$index;
// $invoice['EUR_Accruals']='=AO'.$index.'*T'.$index;
// $invoice['Exchange_Revenue']='=P'.$index.'*R'.$index.'*(100%-AN'.$index.')';
// $invoice['EUR_Exchange_Revenue']='=AQ'.$index.' * T'.$index;
$invoice['Settled_CETR']='=P'.$index.' * X'.$index;
// $invoice['Deferred_Commission']='=P'.$index.'*R'.$index;
// $invoice['Commission_final']='=0.00875*V'.$index.'*AU'.$index.'/30';
// $invoice['DC_value']='=Y'.$index.'+AB'.$index.'+AH'.$index.'+AQ'.$index;
$invoice['Ccy_FV_Due']='=IF(ISBLANK(W'.$index.'),T'.$index.',IF(W'.$index.'<=System!O6,"-",T'.$index.'))';
$invoice['Ccy_DC_Due']='check formula';
$invoice['margin_calls']='=IF(ISBLANK(W'.$index.')'.$this->function_sign.'0'.$this->function_sign.'IF(X'.$index.'<S'.$index.$this->function_sign.'P'.$index.'*X'.$index.'-P'.$index.'*S'.$index.$this->function_sign.'0))';
$invoice['settled_fx']='=IF(ISBLANK(W'.$index.')'.$this->function_sign.'0'.$this->function_sign.'IF(X'.$index.'>S'.$index.$this->function_sign.'P'.$index.'*X'.$index.'-P'.$index.'*S'.$index.$this->function_sign.'0))';
$invoice['trade_profit'] = '=IF(W'.$index.'>0'.$this->function_sign.'Z'.$index.'+V'.$index.'*S'.$index.$this->function_sign.'0)';
$invoice['all_trade_profit'] = '=Z'.$index.'+V'.$index.'*S'.$index;
$invoice['Deferred Income'] = '=IF(AP' . $index . '>0%' . $this->function_sign . 'P' . $index . '*AP' . $index . '*S' . $index . ',P' . $index . '*S' . $index . '*R' . $index . '/30*L' . $index.')';
$invoice['EUR Trade Commission'] = '=V' . $index . '*S' . $index;
$invoice['Settled Commission'] = '=IF(AP' . $index . '>0%' . $this->function_sign . '0' . $this->function_sign . 'IF(Z' . $index . '-AM' . $index . '>0' . $this->function_sign . 'Z' . $index . '-AM' . $index . $this->function_sign . '0))';
$invoice['Fixed Charge_nshared'] = (empty($invoice['real_face_value_charge']) || $invoice['real_face_value_charge'] == 0) ? '' : $invoice['real_face_value_charge'];
unset($invoice['real_face_value_charge']);
$result[]=$this->getXMLRow($invoice,$index);
if (count($result)>50)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
/*$result[]=$this->getXMLRow(array(
"AB"=>"=SUM(AB2:AB".($index-1).")",
"AF"=>"=SUMIFS(AF2:AF".($index-1).",O2:O".($index-1).",\"EUR\")",
"AG"=>"=SUM(AG2:AG".($index-1).")"
),$index+6);
$result[]=$this->getXMLRow(array(
"P"=>"=SUM(P2:P".($index-1).")",
"Q"=>"=SUM(Q2:Q".($index-1).")",
"U"=>"=SUM(U2:U".($index-1).")",
"Y"=>"=SUM(Y2:Y".($index-1).")",
"AB"=>"Settled Trades / Reconciled",
"AF"=>"EUR",
"AG"=>"=SUMIFS(AG2:AG".($index-1).",O2:O".($index-1).",\"EUR\")",
"AI"=>"=SUM(AI2:AI".($index-1).")",
"AJ"=>"=SUM(AJ2:AJ".($index-1).")",
"AK"=>"=SUM(AK2:AK".($index-1).")",
"AP"=>"=SUM(AP2:AP".($index-1).")",
"AR"=>"=SUM(AR2:AR".($index-1).")",
"AS"=>"=SUMIFS(AS2:AS".($index-1).",AC2:AC".($index-1).",\"R\")"
),$index+7);
$result[]=$this->getXMLRow(array(
"U"=>"Settled Trades",
"Y"=>"Settled Trades / Reconciled",
"AB"=>"=SUMIFS(AB2:AB".($index-1).",AC2:AC".($index-1).",\"R\")",
"AF"=>"GBP",
"AG"=>"=SUMIFS(AG2:AG".($index-1).",O2:O".($index-1).",\"GBP\")",
"AI"=>"Settled Trades / Reconciled",
"AR"=>"Settled Trades / Reconciled"
),$index+8);
$result[]=$this->getXMLRow(array(
"U"=>"=SUMIFS(U2:U".($index-1).",AC2:AC".($index-1).",\"R\")",
"Y"=>"=SUMIFS(Y2:Y".($index-1).",AC2:AC".($index-1).",\"R\")",
"AF"=>"USD",
"AG"=>"=SUMIFS(AG2:AG".($index-1).",O2:O".($index-1).",\"USD\")",
"AI"=>"=SUMIFS(AI2:AI".($index-1).",AC2:AC".($index-1).",\"R\")",
"AR"=>"=SUMIFS(AR2:AR".($index-1).",AC2:AC".($index-1).",\"R\")"
),$index+9);*/
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
public function addCommissions($commision_type) {
$is_drp = ((string)$commision_type === 'logapprove_date') ? 0 : 1;
$this->query->executeQuery("SELECT
eur_eur,
gbp_eur,
usd_eur,
gbp_gbp,
usd_usd,
DATE(posted_date) as posted_date,
organisation_details.organisation_reference_id,
IF(organisation_details.organisation_trade_name, organisation_details.organisation_trade_name,
organisation_details.organisation_name) as organisation_name,
gbp_rates.currency_rate as gbp_rate,
usd_rates.currency_rate as usd_rate,
'' as eur_gbp_ccy,
'' as eur_usd_ccy,
'' as gbp_value,
'' as usd_value,
'' total_eur
FROM processing_commissions
LEFT JOIN organisation_details ON organisation_details.user_id = processing_commissions.user_id
LEFT JOIN currency_rates as gbp_rates ON gbp_rates.currency_id = 2
AND gbp_rates.currency_date = processing_commissions.posted_date
LEFT JOIN currency_rates as usd_rates ON usd_rates.currency_id = 3
AND usd_rates.currency_date = processing_commissions.posted_date
WHERE is_drp = " . $is_drp . "
ORDER BY organisation_name, processing_commissions.posted_date
#LIMIT 10");
$this->styles = array(
'A' => 3,
'B' => 3,
'C' => 3,
'D' => 3,
'E' => 3,
'F' => 2,
'G' => -1,
'H' => -2,
'I' => 4,
'J' => 4,
'K' => 3,
'L' => 3,
'M' => 3,
'N' => 3,
'O' => 3
);
if ($commision_type == 'logapprove_date') {
$file_name = 'xl/worksheets/sheet1.xml';
$this->writeSheetHeader($file_name, array('EUR-EUR', 'EUR-GBP', 'EUR-USD', 'GBP-GBP', 'USD-USD',
'Receipt', 'Originator ID', 'Originator Name', 'GBP Fx rate', 'USD Fx rate', 'EUR-GBP Ccy Value',
'EUR-USD Ccy Value', 'GBP-GBP Value', 'USD Value', 'Total'), $this->query->totrows);
} else {
$file_name = 'xl/worksheets/sheet2.xml';
$this->writeSheetHeader($file_name, array('EUR-EUR', 'EUR-GBP', 'EUR-USD', 'GBP-GBP', 'USD-USD',
'Receipt', 'Originator ID', 'Originator Name', 'GBP Fx rate', 'USD Fx rate', 'EUR-GBP Value',
'EUR-USD Value', 'GBP-GBP Value', 'USD-USD Value', 'Total'), $this->query->totrows);
}
$result = array();
$index = 2;
foreach ($this->query->records as $item) {
if ($commision_type == 'logapprove_date') {
$item['gbp_value'] = '=IF(I' . $index . '>0,D' . $index . '/I' . $index . ',0)';
$item['usd_value'] = '=IF(J' . $index . '>0,E' . $index . '/J' . $index . ',0)';
$item['eur_gbp_ccy'] = '=IF(I' . $index . '>0,B' . $index . '/I' . $index . ',0)';
$item['eur_usd_ccy'] = '=IF(C' . $index . '>0,C' . $index . '/J' . $index . ',0)';
} else {
$item['gbp_value'] = '=D' . $index . '/I' . $index;
$item['usd_value'] = '=E' . $index . '/J' . $index;
$item['eur_gbp_ccy'] = '=B' . $index . '/I' . $index;
$item['eur_usd_ccy'] = '=C' . $index . '/J' . $index;
}
$item['total_eur'] = '=A' . $index . '+B' . $index . '+C' . $index . '+D' . $index;
$result[] = $this->getXMLRow($item, $index);
if (count($result) > 50) {
file_put_contents($this->files_location . $file_name,
implode("", $result), FILE_APPEND);
$result = array();
}
$index++;
}
$result[] = $this->getXMLRow(array(
'A' => '=SUM(A2:A' . ($index - 1) . ')',
'B' => '=SUM(B2:B' . ($index - 1) . ')',
'C' => '=SUM(C2:C' . ($index - 1) . ')',
'D' => '=SUM(D2:D' . ($index - 1) . ')',
'E' => '=SUM(E2:E' . ($index - 1) . ')'), $index + 5);
$result[] = $this->getXMLRow(array(
'A' => '=SUM(A' . ($index + 5) . ':E' . ($index + 5) . ')'), $index + 6);
file_put_contents($this->files_location . $file_name,
implode("", $result), FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addDETR()
{
$file_name='xl/worksheets/sheet11.xml';
$headers=array('Originator ID','Originator Name','Debtor ID','Debtor Name','Master ID','Master Debtor','Issued','Trade ID','Ref','Posted','Traded','EDSO','RDSO','ADSO','Ccy','Face Value','Purchased','Sell Rate','LDC Premium','Agent Premium','Traded Fx','EUR Face Value','Purchased','EUR Purchased','Investor','EUR Investor','Trade Commission','Settled','Settled Fx','EUR Settled','Closed','Closed Fx','Disable 180 days','Traded - Settled','Trade Commission','Purchased','Deferred Income','LDC','Loss Premium','3120 - Reserves','Check Model v7.4 - Traded','Settled Commission','Reserves Due/Outstanding','Check Model v7.4 - Settled','Margin Call Settled','Trade Profit','180-Day Rule','Fx Gain - Settled','Fx Gain - Reserve','All Trade Profit','Write-Off','Cost','ETR Type','Margin Call Closed','Fixed Charge');
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'E'=>5,
'F'=>-2,
'G' => 2,
'H' => 5,
'J' => 2,
'K' => 2,
'P' => 3,
'Q' => 11,
'R' => 11,
'S' => 11,
'T' => 11,
'U' => 4,
'V' => 3,
'W' => 3,
'X' => 3,
'Y' => 3,
'Z' => 3,
'AA' => 3,
'AB' => 2,
'AC' => 4,
'AD' => 3,
'AE' => 2,
'AF' => 4,
'AG' => 6,
);
for($key_index=11;$key_index<=34;$key_index++)
{
$this->styles[$this->indexToColumn($key_index+20)]=3;
}
$this->styles['BA'] = 6;
$this->styles['BC'] = 7;
$this->writeSheetHeader($file_name,$headers,$this->query->totrows, true);
$index = 2;
$count = $limit = 10000;
$offset = 0;
while ($count === $limit) {
$result = array();
$this->query->executeQuery('SELECT
organisation_details.organisation_reference_id, IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
debtors_detail.debtor_reference_id, IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as debtor_name,
debtors_master.master_reference_id, IF(debtors_master.debtor_trade_name, debtors_master.debtor_trade_name, debtors_master.debtor_name) as master_debtor_name,
DATE(invoice_master.actual_date) as actual_date,invoice_master.trade_reference_id, convert(binary convert(invoice_master.invoice_no using latin1) using utf8) as invoice_no , DATE(DATE(invoice_master.created_at)) as created_date, DATE(DATE(invoice_master.logapprove_date)) as authorized_date,IFNULL(DATEDIFF(DATE(expected_date), DATE(actual_date)),0) as edso, 0 as rdso,0 as adso ,currencies.currency_code, invoice_master.face_value as amount, (IFNULL(invoice_closed_attributes.purchase_discount,0)/100) as purchase_discount, (invoice_master.max_thirty_day_reserve/100) as sell_rate ,
(invoice_master.ldc_premium/100) as ldc_premium_nshared,
(invoice_master.agent_commission/100) as agent_commission_nshared,
IF( currencies.currency_code="EUR",1.000, traded_rates.currency_rate) as traded_rate ,
0 as EUR_face_value,0 as Purchased_amount, 0 as EUR_purchased_amount, 0 as Investor_Purchase, 0 as EUR_Investor_Purchase,IFNULL(invoice_closed_attributes.processing_fee,0) as processing_fee, IF(main_trans.transaction_type = ' . RECONCILE_DEBTOR_ACCOUNT . ', DATE(payment_trans.transaction_date) ,"") as reserve_date,IF( currencies.currency_code="EUR",1.000, IF( invoice_master.reserve IS NULL ,currencies.currency_rate, reserve_rates.currency_rate)) as reserve_rate,"" as "EUR_Settled_Face_Value",DATE(drp.transaction_date) as drp_date,IF( currencies.currency_code="EUR",1.000, IF( invoice_master.reserve IS NOT NULL AND invoice_master.reserve_on=1,drp_rates.currency_rate,currencies.currency_rate)) as drp_rate,IF(invoice_master.override_180_day_rule, "X", "") as override_180_day_rule, 0 as oetr_value, invoice_master.is_aetr,
IF(invoice_master.face_value_charge_applied = 1,IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = "debtor", organisation_details.face_value_charge, organisation_details.creditor_face_value_charge)))/100,"") as real_face_value_charge
FROM invoice_master
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id
LEFT JOIN debtors_master ON debtors_master.debtor_master_id = debtors_detail.parent_id
LEFT JOIN invoice_closed ON invoice_closed.invoice_id = invoice_master.invoice_id
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions as main_trans ON main_trans.manual_transaction_id = invoice_master.manual_transaction_id AND main_trans.status=1
LEFT JOIN manual_transactions as payment_trans ON main_trans.reconcile_payment = payment_trans.manual_transaction_id AND payment_trans.status=1
LEFT JOIN manual_transactions as drp ON drp.manual_transaction_id = invoice_master.drp_id AND drp.status=1
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates as traded_rates ON traded_rates.currency_id = debtors_detail.currency_id AND traded_rates.currency_date = DATE(invoice_master.logapprove_date)
LEFT JOIN currency_rates as drp_rates ON drp_rates.currency_id = debtors_detail.currency_id AND drp_rates.currency_date = drp.transaction_date
LEFT JOIN currency_rates as reserve_rates ON reserve_rates.currency_id = debtors_detail.currency_id AND reserve_rates.currency_date = payment_trans.transaction_date
LEFT JOIN currency_rates as r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id AND r_trade_rates.currency_date = main_trans.transaction_date
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = invoice_master.user_id AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = invoice_master.user_id AND oc_eur.currency_code="EUR"
WHERE invoice_master.root_invoice_id>0 AND invoice_master.log_status=1 AND debtors_detail.type="debtor"
/*UNION ALL
SELECT organisation_details.organisation_reference_id, IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as organisation_name,
"" as debtor_reference_id, "" as debtor_name,
"" master_reference_id, "" as master_debtor_name,
DATE(orpa_date) as actual_date,reference, "o-ETR" as invoice_no , DATE(orpa_date) as created_date, DATE(orpa_date) as authorized_date,0 as edso, 0 as rdso,0 as adso ,"EUR" currency_code,0 as amount, 0 as purchase_discount, 0 as sell_rate ,
1.000 as traded_rate ,
0 as EUR_face_value,0 as Purchased_amount, 0 as EUR_purchased_amount, 0 as Investor_Purchase, 0 as EUR_Investor_Purchase,0 as processing_fee, "" as reserve_date,1.000 as reserve_rate,"" as "EUR_Settled_Face_Value", "" as drp_date,1.000 as drp_rate,"" as override_180_day_rule,predicted_amount as oetr_value, "" as is_aetr
FROM orpa
LEFT JOIN organisation_details ON organisation_details.user_id = orpa.originator_id
WHERE orpa.`status`=3*/
ORDER BY organisation_name, debtor_name
LIMIT ' . $limit . ' OFFSET ' . $offset);
$count = $this->query->totrows;
$offset += $count;
foreach ($this->query->records as $invoice) {
if ($invoice['oetr_value'] == '0') {
$invoice['EUR_face_value'] = '=P' . $index . '*U' . $index;
// $invoice['EUR_Face_Value2'] = '=Q' . $index . '*T' . $index;
$invoice['Purchased_amount'] = '=P' . $index . '*Q' . $index;
$invoice['EUR_purchased_amount'] = '=U' . $index . ' * W' . $index;
// $invoice['EUR_Settled_Face_Value'] = '=Q' . $index . '*U' . $index;
// $invoice['FX_value_diff_Face_vs_Settled'] = '=Y' . $index . '-X' . $index;
// $invoice['Ccy_FX_value_diff'] = '=Z' . $index . '/V' . $index;
// $invoice['Purchased_amount'] = '=Q' . $index . '*R' . $index;
// $invoice['EUR_purchased_amount'] = '=T' . $index . ' * AB' . $index;
$invoice['Investor_Purchase'] = '=IF(BC' . $index . '>0%' . $this->function_sign . 'P' . $index . '-P' . $index . '*BC' . $index . ',P' . $index . '-P' . $index . '*R' . $index . '*12*180/360)';
$invoice['EUR_Investor_Purchase'] = '=Y' . $index . '*U' . $index;
$invoice['EUR_Settled_Face_Value'] = '=P' . $index . '*AC' . $index;
// $invoice['Ccy_Trade_Com'] = '=AF' . $index . '/T' . $index;
// $invoice['Ccy_Trade_Com_DRP'] = '=AF' . $index . '/W' . $index;
// $invoice['LDC_value'] = '=Q' . $index . '-Q' . $index . '*S' . $index . '*12*180/360-Q' . $index . '*R' . $index;
// $invoice['EUR_LDC'] = '=AI' . $index . '*T' . $index;
// $invoice['Day1_Reserve'] = '=Q' . $index . '*S' . $index . '*12*(180-L' . $index . ')/360';
// $invoice['EUR_Day_1_Reserve'] = '=AK' . $index . '*T' . $index;
// $invoice['EUR_Day_1_Reserve_Settled_FX'] = '=AK' . $index . '*U' . $index;
// $invoice['EUR_Day_1_Reserve_RTrade_FX'] = '=AK' . $index . '*V' . $index;
// $invoice['DC_value'] = '=Q' . $index . '-AB' . $index . '-AI' . $index . '-AK' . $index;
// $invoice['EUR_DC'] = '=AO' . $index . '*T' . $index;
// $invoice['EUR_DC_Settled_Fx'] = '=AO' . $index . '*U' . $index;
// $invoice['EUR_DC_RTrade_Fx'] = '=AO' . $index . '*V' . $index;
// $invoice['ADSO_Reserve'] = '=Q' . $index . '*S' . $index . '*12*(180-M' . $index . ')/360';
// $invoice['EUR_ADSO_Reserve'] = '=AS' . $index . '*T' . $index;
// $invoice['Reserve_Outs'] = '=IF(M' . $index . '>=180' . $this->function_sign . 'IF(BS' . $index . '="X"' . $this->function_sign . 'AI' . $index . '+AK' . $index . '+(AO' . $index . '-BQ' . $index . ')' . $this->function_sign . 'AS' . $index . ')' . $this->function_sign . 'IF(M' . $index . '<=L' . $index . $this->function_sign . 'AI' . $index . '+AK' . $index . $this->function_sign . 'AI' . $index . '+AS' . $index . '))';
// $invoice['EUR_Reserve_Outs'] = '=IF(M' . $index . '>=180' . $this->function_sign . 'IF(BS' . $index . '="X"' . $this->function_sign . 'AJ' . $index . '+AL' . $index . '+(AP' . $index . '-BR' . $index . ')' . $this->function_sign . 'AT' . $index . ')' . $this->function_sign . 'IF(M' . $index . '<=L' . $index . $this->function_sign . 'AJ' . $index . '+AL' . $index . $this->function_sign . 'AJ' . $index . '+AT' . $index . '))';
// $invoice['EUR_Reserve_Outs_DRP_Fx'] = '=AU' . $index . '*W' . $index;
// $invoice['LDC_Gain'] = '=AI' . $index . '+AK' . $index . '-AU' . $index;
// $invoice['EUR_LDC_Gain'] = '=AJ' . $index . '+AL' . $index . '-AV' . $index;
// $invoice['EUR_Reserve_Value'] = '=BA' . $index . ' * U' . $index;
// $invoice['EUR_Reserve_DRP_value'] = '=W' . $index . ' * BA' . $index;
// $invoice['reserve'] = '=IF(AU' . $index . '>0' . $this->function_sign . 'AU' . $index . '+AF' . $index . $this->function_sign . 'AU' . $index . '-AF' . $index . ')';
$invoice['adso'] = '=MAX(L' . $index . $this->function_sign . 'M' . $index . ')';
$invoice['rdso'] = '=IF(ISBLANK(AB' . $index . '),System!$O$6-G' . $index . ',AB' . $index . '-G' . $index . ')';
// $invoice['FX_BLDC_to_QI'] = '=IF(U' . $index . '<T' . $index . $this->function_sign . '-Z' . $index . $this->function_sign . '0)';
// $invoice['CCy_BLDC_to_QI'] = '=IF(U' . $index . '<T' . $index . $this->function_sign . '-AA' . $index . $this->function_sign . '0)';
// $invoice['AU_without_Trade_Fee'] = '=IF(M' . $index . '>=180,Q' . $index . '*S' . $index . '*12*(180-M' . $index . ')/360' . $this->function_sign . 'IF(M' . $index . '<=L' . $index . ',Q' . $index . '-Q' . $index . '*S' . $index . '*12*180/360-Q' . $index . '*R' . $index . '+Q' . $index . '*S' . $index . '*12*(180-L' . $index . ')/360' . $this->function_sign . 'Q' . $index . '-Q' . $index . '*S' . $index . '*12*180/360-Q' . $index . '*R2+Q' . $index . '*S' . $index . '*12*(180-M' . $index . ')/360))';
// $invoice['AL_WITHOUT_TRADE_FEE'] = '=IF(Q' . $index . '*(S' . $index . ')*12*(180-L' . $index . ')/360<0' . $this->function_sign . '0' . $this->function_sign . 'Q' . $index . '*(S' . $index . ')*12*(180-L' . $index . ')/360)';
// $invoice['Ccy_FV_Due'] = '=IF(ISBLANK(BE' . $index . '),Q' . $index . ',IF(BE' . $index . '<=System!O6,"-",Q' . $index . '))';
// $invoice['Ccy_DC_Due'] = '=IF(ISBLANK(BE' . $index . '),AO' . $index . ',IF(BE' . $index . '<=System!O6,"-",AO' . $index . '))';
// $invoice['Ccy_Reserve_Due'] = '=IF(ISBLANK(BE' . $index . '),"-",IF(BE' . $index . '<=System!O6,IF(ISBLANK(BF' . $index . '),AU' . $index . ',IF(BF' . $index . '<=System!O6,"-",AU' . $index . ')),"-"))';
// $invoice['adso_dc'] = '=(Q' . $index . '*S' . $index . ')/30*M' . $index;
// $invoice['eur_adso_dc'] = '=BQ' . $index . '*T' . $index;
$invoice['Traded - Settled'] = '=IF(ISBLANK(AB' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'P' . $index . '*AC' . $index . '-P' . $index . '*U' . $index . ')';
$invoice['Trade Commission'] = '=AA' . $index . '*U' . $index;
$invoice['Purchased'] = '=P' . $index . '*U' . $index . '*Q' . $index . '+N("Always working from first principles")';
$invoice['Deferred Income'] = '=IF(BC' . $index . '>0%' . $this->function_sign . 'P' . $index . '*BC' . $index . '*U' . $index . '' . $this->function_sign . 'IF(AND(AG' . $index . '="X"' . $this->function_sign . 'MAX(L' . $index . ':N' . $index . ')>180)' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30>P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30)+AA' . $index . '*U' . $index . '+N("180-Day Rule not applied")' . $this->function_sign . 'IF(L' . $index . '>=180' . $this->function_sign . 'P' . $index . '*U' . $index . '-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360)+(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Deals with 180-Day Rule")' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . '<0' . $this->function_sign . '(P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*L' . $index . '/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . '))+(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . ')+N("Reduces Commission because there is not enough Trade Commission capacity")' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*L' . $index . '/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Normal Commission w/ X.XX Trade Commission capacity")))))';
$invoice['LDC_formula'] = '=IF(BC' . $index . '>0%' . $this->function_sign . 'P' . $index . '*U' . $index . '-P' . $index . '*Q' . $index . '*U' . $index . '-P' . $index . '*BC' . $index . '*U' . $index . '' . $this->function_sign . 'IF(AND(AG' . $index . '="X"' . $this->function_sign . 'MAX(L' . $index . ':N' . $index . ')>180)' . $this->function_sign . '0+N("180-Day Rule not applied")' . $this->function_sign . 'IF(L' . $index . '>=180' . $this->function_sign . '0' . $this->function_sign . 'P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Always working from first principles")))';
$invoice['Loss Premium'] = '=P' . $index . '*S' . $index . '*U' . $index;
$invoice['3120 - Reserves'] = '=IF(BC' . $index . '>0%' . $this->function_sign . '0' . $this->function_sign . 'IF(AND(AG' . $index . '="X"' . $this->function_sign . 'MAX(L' . $index . ':N' . $index . ')>180)' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')-IF(P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30>P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30)-AA' . $index . '*U' . $index . '+N("180-Day Rule not applied")' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . '<=0' . $this->function_sign . '0+N("Reduces to zero 0 because there is not enough Trade Commission capacity")' . $this->function_sign . 'P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . ')))';
$invoice['Check Model v7.3 - Traded'] = '=P' . $index . '*U' . $index . '-AJ' . $index . '-AK' . $index . '-AL' . $index . '-AN' . $index;
$invoice['Settled Commission'] = '=IF(OR(BC' . $index . '>0%' . $this->function_sign . 'R' . $index . '<=0.01%)' . $this->function_sign . '0' . $this->function_sign . 'IF(AND(AG' . $index . '="X"' . $this->function_sign . 'MAX(L' . $index . ':N' . $index . ')>180)' . $this->function_sign . '0+N("180-Day Rule not applied")' . $this->function_sign . '+N("Deferred Commission on Traded")+IF(MAX(L' . $index . ':N' . $index . ')>=180' . $this->function_sign . 'P' . $index . '*U' . $index . '-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360)+(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Deals with 180-Day Rule")' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '*U' . $index . '<0' . $this->function_sign . '(P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . '))+(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '*U' . $index . ')+N("Reduces Commission because there is not enough Trade Commission capacity")+P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360-P' . $index . '*U' . $index . '*Q' . $index . '+N("And moves the full LDC into Exchange Commission")' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Normal Commission w/ X.XX Trade Commission capacity")))-N("Deferred Commission on Traded")-IF(L' . $index . '>=180' . $this->function_sign . 'P' . $index . '*U' . $index . '-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360)+(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*180/360-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Deals with 180-Day Rule")' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . '<0' . $this->function_sign . '(P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*L' . $index . '/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . '))+(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-L' . $index . ')/360-AA' . $index . '*U' . $index . ')+N("Reduces Commission because there is not enough Trade Commission capacity")' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')-(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*L' . $index . '/360-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . ')+N("Normal Commission w/ X.XX Trade Commission capacity")))))';
$invoice['Reserves Due/Outstanding'] = '=IF(OR(BC' . $index . '>0%' . $this->function_sign . 'R' . $index . '<=0.01%)' . $this->function_sign . 'P' . $index . '*U' . $index . '-P' . $index . '*Q' . $index . '*U' . $index . '-P' . $index . '*BC' . $index . '*U' . $index . '' . $this->function_sign . 'IF(AND(AG' . $index . '="X"' . $this->function_sign . 'MAX(L' . $index . ':N' . $index . ')>180)' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')-IF(P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30>P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*(1-Q' . $index . ')' . $this->function_sign . 'P' . $index . '*U' . $index . '*R' . $index . '*MAX(L' . $index . ':N' . $index . ')/30)-AA' . $index . '*U' . $index . '+N("180-Day Rule not applied")' . $this->function_sign . 'IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '*U' . $index . '<=0' . $this->function_sign . '0+N("Reduces to zero 0 because there is not enough Trade Commission capacity")' . $this->function_sign . '(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360)-AA' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . ')))';
$invoice['Check Model v7.3 - Settled'] = '=P' . $index . '*U' . $index . '-AJ' . $index . '-AK' . $index . '-AP' . $index . '-AQ' . $index;
$invoice['Margin Call'] = '=IF(ISBLANK(AB' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(U' . $index . '>AC' . $index . $this->function_sign . 'P' . $index . '*AC' . $index . '-P' . $index . '*U' . $index . $this->function_sign . '0))';
$invoice['Trade Profit'] = '=IF(AB' . $index . '>0' . $this->function_sign . 'AI' . $index . '+AK' . $index . '+AP' . $index . $this->function_sign . '0)';
$invoice['180-Day Rule'] = '=IF(AY' . $index . '="X"' . $this->function_sign . '0' . $this->function_sign . 'IF(ISBLANK(AG' . $index . ')' . $this->function_sign . 'IF(MAX(L' . $index . ':N' . $index . ')<180' . $this->function_sign . '0' . $this->function_sign . '-IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '<=0' . $this->function_sign . 'P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . $this->function_sign . '(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360)-AA' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . '))' . $this->function_sign . '0))';
$invoice['Fx Gain - Settled'] = '=IF(ISBLANK(AB' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(U' . $index . '>AC' . $index . $this->function_sign . '0' . $this->function_sign . 'P' . $index . '*AC' . $index . '-P' . $index . '*U' . $index . '))';
$invoice['Fx Gain - Reserve'] = '=IF(ISBLANK(AE' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'N("Reserve on Traded")+IF(P' . $index . '*U' . $index . '*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '<=0' . $this->function_sign . '0+N("Reduces to zero 0 because there is not enough Trade Commission capacity")' . $this->function_sign . '(P' . $index . '*U' . $index . '-P' . $index . '*U' . $index . '*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360)-AA' . $index . '-P' . $index . '*U' . $index . '*Q' . $index . ')-N("Settled Reserved")-IF(P' . $index . '*MIN(U' . $index . $this->function_sign . 'AC' . $index . $this->function_sign . 'AF' . $index . ')*R' . $index . '*12*(180-MAX(L' . $index . ':N' . $index . '))/360-AA' . $index . '<=0' . $this->function_sign . '0+N("Reduces to zero 0 because there is not enough Trade Commission capacity")' . $this->function_sign . '(P' . $index . '*MIN(U' . $index . $this->function_sign . 'AC' . $index . $this->function_sign . 'AF' . $index . ')-P' . $index . '*MIN(U' . $index . $this->function_sign . 'AC' . $index . $this->function_sign . 'AF' . $index . ')*R' . $index . '*12*MAX(L' . $index . ':N' . $index . ')/360)-AA' . $index . '-P' . $index . '*MIN(U' . $index . $this->function_sign . 'AC' . $index . $this->function_sign . 'AF' . $index . ')*Q' . $index . '))';
$invoice['All Trade Profit'] = '=IF(AY' . $index . '="x"' . $this->function_sign . '0' . $this->function_sign . 'AI' . $index . '+AK' . $index . '+AP' . $index . ')';
$invoice['Write-Off'] = '';
$invoice['Cost'] = '=IF(AY' . $index . '="X"' . $this->function_sign . '-W' . $index . '*U' . $index . $this->function_sign . '0)';
$invoice['etr_type'] = $invoice['is_aetr'] ? 'a' : 'd';
$invoice['Margin Call Closed'] = '=IF(ISBLANK(AE' . $index . ')' . $this->function_sign . '0' . $this->function_sign . 'IF(U' . $index . '>AF' . $index . $this->function_sign . 'AQ' . $index . '*AF' . $index . '-AQ' . $index . '*U' . $index . $this->function_sign . '0))';
$invoice['Fixed Charge_nshared'] = empty($invoice['real_face_value_charge']) ? '' : $invoice['real_face_value_charge'];
}
unset($invoice['oetr_value'],$invoice['is_aetr'],$invoice['real_face_value_charge']);
$result[] = $this->getXMLRow($invoice, $index);
if (count($result) > 50) {
file_put_contents($this->files_location . $file_name, implode("", $result), FILE_APPEND);
$result = array();
}
$index++;
}
file_put_contents($this->files_location . $file_name, implode("", $result), FILE_APPEND);
}
// $html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/detr_tpr.html',array('addDETR'=>$index-1,'startCheck'=>$index+16,'endCheck'=>$index+18),FALSE);
// $result=$this->HTMLtoOOXML($html_content,FALSE,array(),FALSE,$index+4,15);
// file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $index;
}
function addOriginatorReserveReport($DETRPos,$inboundDETRPos,$CNPos,$SDPos,$ReturnedPos,$DPRPos,$CETRPos,$inboundCETRPos,$ReserveAdjustmentsPos)
{
$originators=Model_MStatements::getOriginatorReserveReport();
$file_name='xl/worksheets/sheet15.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'H'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M'=>3,
'N'=>3,
'O'=>3,
'P'=>3,
'Q'=>3,
'S'=>3,
'T'=>3,
'U'=>3,
'V'=>3,
'W'=>3,
'X'=>3,
'Y'=>3,
'Z'=>3,
'AA'=>3,
'AB'=>3,
'AC'=>3,
'AD'=>3,
'AE'=>3,
'AF'=>3,
'AG'=>3,
'AH'=>3,
'AI'=>3,
'AJ'=>3,
'AK'=>3,
'AL'=>3
);
$this->writeSheetHeader($file_name,array('Originator','Originator Name','Ccy','Due','Due EUR','Reserves O/s','Reserves O/s EUR','Total','Total EUR','ETR Oustanding','ETR Oustanding EUR','Trailing','Trailing EUR','Discounts','Discounts EUR','Closing','Closing EUR','Check',
'Traded Discounts (EUR-EUR)','Setteled Discounts (EUR-EUR)','Traded Discounts Fx (EUR-GBP)','Setteled Discounts Fx (EUR-GBP)','Traded Discounts Fx (EUR-USD)','Setteled Discounts Fx (EUR-USD)','Traded Discounts Fx (GBP-GBP)','Setteled Discounts Fx (GBP-GBP)','Traded Discounts Fx (USD-USD)','Setteled Discounts Fx (USD-USD)',
'All Time Traded Discounts (EUR-EUR)','All Time Setteled Discounts (EUR-EUR)','All Time Traded Discounts Fx (EUR-GBP)','All Time Setteled Discounts Fx (EUR-GBP)','All Time Traded Discounts Fx (EUR-USD)','All Time Setteled Discounts Fx (EUR-USD)','All Time Traded Discounts Fx (GBP-GBP)','All Time Setteled Discounts Fx (GBP-GBP)','All Time Traded Discounts Fx (USD-USD)','All Time Setteled Discounts Fx (USD-USD)'),count($originators));
$final_result=array();
$index=2;
$current_originator=0;
foreach($originators as $originator)
{
$result=array(
$originator['organisation_reference_id'],
$originator['organisation_name'],
$originator['currency_code']);
foreach(array('reserveDue','reservesOut','reserveTotal','etr_outstanding','trailing','discounts','closing_position') as $value)
{
if ($originator['currency_code']!='EUR')
{
$result[$value.'_nshared']=$originator[$value];
$result['fx_'.$value.'_nshared']=$originator['fx_'.$value];
}
else
{
$result[$value.'_nshared']=empty($originator[$value])?'0.00':$originator[$value];
$result['fx_'.$value.'_nshared']=empty($originator[$value])?'0.00':$originator[$value];
}
}
$result['check_nshared']='=IF(ROUND(J'.$index.'-H'.$index.',2)=ROUND(P'.$index.',2),"PASS","ERROR")';
if ($current_originator!=$originator['organisation_reference_id'])
{
$current_originator=$originator['organisation_reference_id'];
foreach(array(array('EUR','EUR'),array('GBP','EUR'),array('USD','EUR'),array('GBP','GBP'),array('USD','USD')) as $item)
{
$result[$item[0] . '-' . $item[1] . '-traded'] = '=SUMIFS(\'d-ETR\'!X:X, \'d-ETR\'!K:K,">="&System!O5, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ' )*((SUMIFS( \'Credit Notes\'!I:I, \'Credit Notes\'!F:F,">="&System!O5, \'Credit Notes\'!F:F,"<="&System!O6, \'Credit Notes\'!J:J,"Authorized",\'Credit Notes\'!H:H,"' . $item[0] . '",\'Credit Notes\'!S:S,"' . $item[1] . '",\'Credit Notes\'!A:A,A' . $index . ') + SUMIFS( \'Specific Deductions\'!I:I, \'Specific Deductions\'!F:F,">="&System!O5, \'Specific Deductions\'!F:F,"<="&System!O6, \'Specific Deductions\'!J:J,"Authorized",\'Specific Deductions\'!H:H,"' . $item[0] . '",\'Specific Deductions\'!R:R,"' . $item[1] . '",\'Specific Deductions\'!A:A,A' . $index . '))/IF(SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!K:K,">="&System!O5, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')=0,1,SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!K:K,">="&System!O5, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ' )))';
$result[$item[0] . '-' . $item[1] . '-setteled'] = '=SUMIFS(\'d-ETR\'!Y:Y,\'d-ETR\'!AZ:AZ,"R",\'d-ETR\'!BE:BE,">="&System!O5, \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ') * ((SUMIFS( \'Credit Notes\'!I:I, \'Credit Notes\'!R:R,">="&System!O5, \'Credit Notes\'!R:R,"<="&System!O6, \'Credit Notes\'!J:J,"Authorized", \'Credit Notes\'!K:K,"X", \'Credit Notes\'!H:H,"' . $item[0] . '",\'Credit Notes\'!S:S,"' . $item[1] . '",\'Credit Notes\'!A:A,A' . $index . ') + SUMIFS( \'Specific Deductions\'!I:I, \'Specific Deductions\'!Q:Q,">="&System!O5, \'Specific Deductions\'!Q:Q,"<="&System!O6, \'Specific Deductions\'!J:J,"Authorized",\'Specific Deductions\'!H:H,"' . $item[0] . '",\'Specific Deductions\'!R:R,"' . $item[1] . '",\'Specific Deductions\'!A:A,A' . $index . '))/IF(SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!AZ:AZ,"R", \'d-ETR\'!BE:BE,">="&System!O5, \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')=0,1,SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!AZ:AZ,"R", \'d-ETR\'!BE:BE,">="&System!O5, \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')))';
}
foreach(array(array('EUR','EUR'),array('GBP','EUR'),array('USD','EUR'),array('GBP','GBP'),array('USD','USD')) as $item)
{
$result[$item[0] . '-' . $item[1] . '-traded-all'] = '=SUMIFS(\'d-ETR\'!X:X, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ' )*((SUMIFS( \'Credit Notes\'!I:I,\'Credit Notes\'!F:F,"<="&System!O6, \'Credit Notes\'!J:J,"Authorized",\'Credit Notes\'!H:H,"' . $item[0] . '",\'Credit Notes\'!S:S,"' . $item[1] . '",\'Credit Notes\'!A:A,A' . $index . ') + SUMIFS( \'Specific Deductions\'!I:I, \'Specific Deductions\'!F:F,"<="&System!O6, \'Specific Deductions\'!J:J,"Authorized",\'Specific Deductions\'!H:H,"' . $item[0] . '",\'Specific Deductions\'!R:R,"' . $item[1] . '",\'Specific Deductions\'!A:A,A' . $index . '))/IF(SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')=0,1,SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!K:K,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ' )))';
$result[$item[0] . '-' . $item[1] . '-setteled-all'] = '=SUMIFS(\'d-ETR\'!Y:Y,\'d-ETR\'!AZ:AZ,"R", \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ') * ((SUMIFS( \'Credit Notes\'!I:I, \'Credit Notes\'!R:R,"<="&System!O6, \'Credit Notes\'!J:J,"Authorized", \'Credit Notes\'!K:K,"X", \'Credit Notes\'!H:H,"' . $item[0] . '",\'Credit Notes\'!S:S,"' . $item[1] . '",\'Credit Notes\'!A:A,A' . $index . ') + SUMIFS( \'Specific Deductions\'!I:I, \'Specific Deductions\'!Q:Q,"<="&System!O6, \'Specific Deductions\'!J:J,"Authorized",\'Specific Deductions\'!H:H,"' . $item[0] . '",\'Specific Deductions\'!R:R,"' . $item[1] . '",\'Specific Deductions\'!A:A,A' . $index . '))/IF(SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!AZ:AZ,"R", \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')=0,1,SUMIFS( \'d-ETR\'!Q:Q, \'d-ETR\'!AZ:AZ,"R", \'d-ETR\'!BE:BE,"<="&System!O6, \'d-ETR\'!O:O,"' . $item[0] . '", \'d-ETR\'!P:P,"' . $item[1] . '", \'d-ETR\'!A:A,A' . $index . ')))';
}
}
$final_result[]=$this->getXMLRow($result,$index);
$index++;
}
$result=array();
foreach(array('D','E','F','G','H','I','J','L','M','N','O','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL') as $item)
{
$result[$item]='=SUM('.$item.'2:'.$item.($index-1).')';
}
$final_result[]=$this->getXMLRow($result,$index);
$reserveReportPos=$index;
$final_result = array_merge($final_result, $this->getReserveReportSummary($reserveReportPos, $DETRPos, $inboundDETRPos, $CNPos, $SDPos, $ReturnedPos, $DPRPos, $CETRPos, $inboundCETRPos, $ReserveAdjustmentsPos));
file_put_contents($this->files_location.$file_name,implode("",$final_result),FILE_APPEND);
$this->writeSheetFooter($file_name);
return $reserveReportPos;
}
function getReserveReportSummary($reserveReportPos, $DETRPos, $inboundDETRPos, $CNPos, $SDPos, $ReturnedPos, $DPRPos, $CETRPos, $inboundCETRPos, $ReserveAdjustmentsPos)
{
$final_result = array();
/*if ($this->report_type == 'weekly_tpr') {
$final_result[] = '<reserve_report_summary_backdated_payments>';
return $final_result;
}*/
$this->styles=array(
'A'=>-1,
'B'=>-2,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'H'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M'=>3,
'N'=>3,
'O'=>3,
'P'=>3,
'Q'=>3,
'S'=>3,
'T'=>3,
'U'=>3,
'V'=>3,
'W'=>3,
'X'=>3,
'Y'=>3,
'Z'=>3,
'AA'=>3,
'AB'=>3,
'AC'=>3,
'AD'=>3,
'AE'=>3,
'AF'=>3,
'AG'=>3,
'AH'=>3,
'AI'=>3,
'AJ'=>3,
'AK'=>3,
'AL'=>3
);
$initial_checks=array(
array('d-ETR SUM',"='d-ETR'!O".$DETRPos),
array('Payment SUM',"='Payments (d-ETR)'!I".$inboundDETRPos),
array('CN SUM',"='Credit Notes'!I".$CNPos),
array('SD SUM',"='Specific Deductions'!I".$SDPos),
array('RP SUM',"='Returned Payments'!I".$ReturnedPos),
array('Comparison','=H'.$reserveReportPos.' - '.'(E'.($reserveReportPos+5).'-E'.($reserveReportPos+6).'-E'.($reserveReportPos+7).'-E'.($reserveReportPos+8).'+E'.($reserveReportPos+9).')')
);
$reservesChecks=array(
array('Reserve Total All',
'=F'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"='d-ETR'!AY" . $DETRPos . "-'Credit Notes'!I" . $CNPos . "-'Specific Deductions'!I" . $SDPos . "-'Settled Reserves'!I" . $DPRPos . "+'Reserve Adjustments'!I" . $ReserveAdjustmentsPos . "-SUMIFS('Payments (d-ETR)'!I2:I" . $inboundDETRPos . ",'Payments (d-ETR)'!J2:J" . $inboundDETRPos . ",\"Authorized\",'Payments (d-ETR)'!O2:O" . $inboundDETRPos . ",\"PDO\")"),
array('Reserve DUE All',
'=D'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"=SUMIFS('d-ETR'!AY2:AY" . ($DETRPos - 1) . ",'d-ETR'!AZ2:AZ" . ($DETRPos - 1) . ",\"DUE\")-SUMIFS('Credit Notes'!I2:I" . ($CNPos - 1) . ",'Credit Notes'!J2:J" . ($CNPos - 1) . ",\"Authorized\",'Credit Notes'!L2:L" . ($CNPos - 1) . ",\"\")-SUMIFS('Specific Deductions'!I2:I" . ($SDPos - 1) . ",'Specific Deductions'!J2:J" . ($SDPos - 1) . ",\"Authorized\",'Specific Deductions'!K2:K" . ($SDPos - 1) . ",\"\")+SUMIFS('Reserve Adjustments'!I2:I" . ($ReserveAdjustmentsPos - 1) . ",'Reserve Adjustments'!J2:J" . ($ReserveAdjustmentsPos - 1) . ",\"Authorized\",'Reserve Adjustments'!K2:K" . ($ReserveAdjustmentsPos - 1) . ",\"\")"),
array('Reserve Total Fx',
'=G'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"='d-ETR'!BU" . $DETRPos . "-'Credit Notes'!Q" . $CNPos . "-'Specific Deductions'!P" . $SDPos . "-'Settled Reserves'!L" . $DPRPos . "+'Reserve Adjustments'!P" . $ReserveAdjustmentsPos . "-SUMIFS('Payments (d-ETR)'!N2:N" . ($inboundDETRPos - 1) . ",'Payments (d-ETR)'!J2:J" . ($inboundDETRPos - 1) . ",\"Authorized\",'Payments (d-ETR)'!O2:O" . ($inboundDETRPos - 1) . ",\"PDO\")"),
array('Reserve DUE Fx',
'=E'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"=SUMIFS('d-ETR'!BU2:BU" . ($DETRPos) . ",'d-ETR'!AZ2:AZ" . ($DETRPos) . ",\"DUE\")-SUMIFS('Credit Notes'!Q2:Q" . ($CNPos - 1) . ",'Credit Notes'!J2:J" . ($CNPos - 1) . ",\"Authorized\",'Credit Notes'!L2:L" . ($CNPos - 1) . ",\"\")-SUMIFS('Specific Deductions'!P2:P" . ($SDPos - 1) . ",'Specific Deductions'!J2:J" . ($SDPos - 1) . ",\"Authorized\",'Specific Deductions'!K2:K" . ($SDPos - 1) . ",\"\")+SUMIFS('Reserve Adjustments'!P2:P" . ($ReserveAdjustmentsPos - 1) . ",'Reserve Adjustments'!J2:J" . ($ReserveAdjustmentsPos - 1) . ",\"Authorized\",'Reserve Adjustments'!K2:K" . ($ReserveAdjustmentsPos - 1) . ",\"\")"
),
array('Discounts All',
'=L'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"=E".($reserveReportPos+5+2)."+E".($reserveReportPos+5+3)
),
array('Discounts Fx',
'=M'.$reserveReportPos,
'=ROUND(I{row_index},3)=ROUND(K{row_index},3)',
"=E".($reserveReportPos+15+2)."+E".($reserveReportPos+15+3)
)
);
foreach($initial_checks as $index=>$checks)
{
$reserveCheck=$reservesChecks[$index];
$reserveCheck[2]=str_replace('{row_index}',$reserveReportPos+5+$index,$reserveCheck[2]);
$final_result[]=$this->getXMLRow(array(
'D'=>$checks[0],
'E'=>$checks[1],
'H'=>$reserveCheck[0],
'I'=>$reserveCheck[1],
'J'=>$reserveCheck[2],
'K'=>$reserveCheck[3]
),$reserveReportPos+5+$index);
}
$summary_checks=array(
array('d-ETR SUM',"='d-ETR'!V".$DETRPos),
array('d-ETR Payment SUM',"='Payments (d-ETR)'!N".$inboundDETRPos),
array('CN SUM',"='Credit Notes'!N".$CNPos),
array('SD SUM',"='Specific Deductions'!M".$SDPos),
array('d-ETR RP',"='Returned Payments'!L".$ReturnedPos),
array('DRP SUM',"='Settled Reserves'!L".$DPRPos),
array('c-ETR SUM',"='b-ETR & c-ETR'!O".$CETRPos),
array('c-ETR Payment SUM',"='Payments (c-ETR)'!J".$inboundCETRPos)
);
foreach($summary_checks as $index=>$checks)
{
$final_result[]=$this->getXMLRow(array(
'D'=>$checks[0],
'E'=>$checks[1]
),$reserveReportPos+15+$index);
}
return $final_result;
}
function initTemplates()
{
include_once(ROOT_FOLDER.'Bin/Smarty.php');
include_once(ROOT_FOLDER.'Bin/Template.php');
$_SESSION=array();
}
function getExcelEndDate(Datetime $date = null)
{
$date = $date ? $date->format('Y-m-d') : date('Y-m-d');
return $this->getXLSXDate(strtotime($date));
}
function addSystemChecks($DETRPos,$addCETR,$addCommissions,$addCommissionsDRP,$addCN,$addSD,$addInboundPaymentsDETR,$addInboundPaymentsCETR,$addDRP,$addReserveAdjustments,$addReturnedPayments,$addOPATransactions,$addOriginatorReserveReport)
{
$magicDate = new DateTime(date('Y-m-d', strtotime('Dec 31')));
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/back_office_xslx.html',array(
'DETRPos'=>$DETRPos,
'addCETR'=>$addCETR,
'addCommissions'=>$addCommissions,
'addCommissionsDRP'=>$addCommissionsDRP,
'addCN'=>$addCN,
'addSD'=>$addSD,
'addInboundPaymentsDETR'=>$addInboundPaymentsDETR,
'addInboundPaymentsCETR'=>$addInboundPaymentsCETR,
'addDRP'=>$addDRP,
'addReserveAdjustments'=>$addReserveAdjustments,
'addReturnedPayments'=>$addReturnedPayments,
'addOPATransactions'=>$addOPATransactions,
'addOriginatorReserveReport'=>$addOriginatorReserveReport,
'end_date' => $this->getExcelEndDate(),
'magic_date'=>$this->getExcelEndDate($magicDate),
),FALSE);
$this->styles=array(
'E'=>-4,
'F'=>3,
'G'=>3,
'H'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M'=>3
);
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet16.xml',array('','','','','','','','','','','','','','',''),array('O5'=>2,'O6'=>2,'O7'=>2,'O8'=>2,'O12'=>2));
}
function HTMLtoOOXML($html_content,$file_name,$headers,$additional_styles=array(),$index=2,$starting_position=0, $additional_file_name = null)
{
$doc = new DOMDocument();
$doc->loadHTML('<html><body><table>'.$html_content.'</table></body></html>');
$table = $doc->getElementsByTagName('table')->item(0);
$trs=$table->getElementsByTagName('tr');
$rowsCount = $trs->length;
if ($file_name)
{
$this->writeSheetHeader($file_name,$headers,$trs->length-1);
}
$tempTemplate = null;
if ($file_name === 'xl/worksheets/sheet29.xml') {
$tempTemplate = $file_name;
$header_rows = array($rowsCount-2, $rowsCount-6, $rowsCount-16);
foreach (range('C','R') as $aCol){
foreach ($header_rows as $aRow){
$additional_styles[$aCol.$aRow]=1;
}
}
$additional_headers = array_merge($headers, array(
'Totals',
'Type',
'Ccy',
'Total Outstanding',
'Face Value Total',
'Disallowed Total',
'Commission Total',
'Payments Total',
'Fees and Charges',
'Total Overdue',
'Total Reserve',
'Reserve Due',
'OCPA',
'Unallocated IDP',
'Unallocated ICP',
'Trailing Balances',
'O/s Less Disallowed',
));
} elseif (in_array($file_name, array('xl/worksheets/sheet19.xml','xl/worksheets/sheet20.xml','xl/worksheets/sheet33.xml',))) {
$tempTemplate = $file_name;
}
$result=array();
$end_date=$this->getExcelEndDate();
$magicDate = new DateTime(date('Y-m-d', strtotime('Dec 31')));
$magic_date=$this->getExcelEndDate($magicDate);
foreach($trs as $tr)
{
$tds = $tr->getElementsByTagName('td');
$row=array();
if ($starting_position>0)
{
$row=array_fill(0,$starting_position,'');
}
foreach($tds as $td_index=>$td)
{
$td_index+=$starting_position;
if (isset($td->nodeValue[0]) AND $td->nodeValue[0]=='=')
{
$row[$td_index.'_nshared']=($td->nodeValue);
}
elseif(in_array($td->nodeValue,array('40909',$end_date,$magic_date,'43465')) OR preg_match('/_date/si',$td->nodeValue))
{
$td->nodeValue=str_replace('_date','',$td->nodeValue);
$row[$td_index.'_nshared']=htmlentities(floatval($td->nodeValue));
} elseif ($file_name === 'xl/worksheets/sheet29.xml' and $td_index > 4
and !in_array(trim($td->nodeValue), $additional_headers) and $index < $rowsCount - 3)
{
$row[$td_index . '_nshared'] = floatval($td->nodeValue);
} elseif ($additional_file_name === 'xl/worksheets/sheet30.xml'
and (in_array($td_index, array(4, 5, 7, 10, 11, 13)))) {
$row[$td_index . '_nshared'] = ($td->nodeValue);
} elseif ($additional_file_name === 'xl/worksheets/sheet31.xml'
and (in_array($td_index, array(1, 8, 10, 12, 13, 14, 15, 16)))) {
$row[$td_index . '_nshared'] = $td->nodeValue;
}
else
{
$row[$td_index]=($td->nodeValue);
}
}
$not_shared = in_array($file_name, array('xl/worksheets/sheet18.xml',)) ? array(array('row' => 24, 'column' => 8), array('row' => 25, 'column' => 8), array('row' => 26, 'column' => 9), array('row' => 27, 'column' => 9), array('row' => 36, 'column' => 11),) : array();
$not_shared = in_array($file_name, array('xl/worksheets/sheet32.xml',)) ? array(array('row' => 24, 'column' => 7), array('row' => 25, 'column' => 7), array('row' => 26, 'column' => 8), array('row' => 27, 'column' => 8), array('row' => 36, 'column' => 10),) : $not_shared;
$result[]=$this->getXMLRow($row,$index,$additional_styles, $not_shared, false, $tempTemplate);
if (count($result)>50 AND $file_name)
{
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$result=array();
}
$index++;
}
if (!$file_name)
{
return $result;
}
file_put_contents($this->files_location.$file_name,implode("",$result),FILE_APPEND);
$this->writeSheetFooter($file_name);
}
function addSummary($addRates,$DETRPos,$addCETR,$addCommissions,$addCommissionsDRP,$addCN,$addSD,$addInboundPaymentsDETR,$addInboundPaymentsCETR,$addDRP,$addReserveAdjustments,$addReturnedPayments,$addOPATransactions,$addOriginatorReserveReport)
{
$codes=array();
foreach($this->accounts_journal->ledger_codes as $ledger_data)
{
preg_match('/([0-9.]+)([\w\W]+)/si',$ledger_data['details']['title'],$temp_ledger);
$codes[]=array(
'code_id'=>trim($temp_ledger[1]),
'code_text'=>$temp_ledger[2]
);
}
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/summary_xslx.html',array(
'summary'=>array(
'PL'=>array(
array(2001,'2001 - Arrangement Fees'),
array(2002,'2002 - Access Fees'),
array(2003,'2003 - Administration Fees'),
array(2005,'2005 - Rating Fees'),
array(2103,'2103 - Traded c-ETR'),
array(2102,'2102 - Traded b-ETR'),
array(2104,'2104 - Traded d-ETR'),
array(2121,'2121 - Trade Commission'),
array(2122,'2122 - Processing Commission'),
array(2123,'2123 - Exchange Commission'),
array(2124,'2124 - Discount Commission'),
array(2125,'2125 - Trade Fx Commission'),
array(2127,'2127 - Settled Commission'),
array(3120,'3120 - Reserves'),
array(3132,'3132 - LDC'),
array(3133,'3133 - b-ETR Purchase Price'),
array(3134,'3134 - c-ETR Purchase Price'),
array(3135,'3135 - d-ETR Purchase Price'),
array(3136,'3136 - d-ETR Discount'),
array(3150,'3150 - Deferred Commission'),
array(3501,'3501 - Foreign Exchange'),
array(2601,'2601 - Translation Fx')
),
'BS'=>array(
array(1001,'1001 - Quistclose Investor EUR'),
array(1002,'1002 - Quistclose Investor GBP'),
array(1003,'1003 - Quistclose Investor USD'),
array(1101,'1101 - Bank Clearing EUR'),
array(1102,'1102 - Bank Clearing GBP'),
array(1103,'1103 - Bank Clearing USD'),
array(1201,'1201 - Bank LDC EUR'),
array(1301,'1301 - Investor GBP Control'),
array(1302,'1302 - Investor USD Control'),
array(1321,'1321 - LDC GBP Control'),
array(1322,'1322 - LDC USD Control'),
array(1343,'1343 - Exchange USD Control'),
array(1401,'1401 - Bank Exchange EUR'),
array(1402,'1402 - Bank Exchange GBP'),
array(1600,'1600 - Settled Trades'),
array(1904,'1904 - d-ETR'),
array(1903,'1903 - c-ETR'),
array(1902,'1902 - b-ETR'),
array(8130,'8130 - Deferred Commissions'),
array(8150,'8150 - Originator Accruals'),
array(8310,'8310 - Reserves Due'),
array(8320,'8320 - Reserves Outstanding'),
array(8330,'8330 - LDC Reserves'),
array(8404,'8404 - d-ETR Creditors')
),
'CT'=>array(
array(1002,'1002 - Quistclose Investor GBP'),
array(1102,'1102 - Bank Clearing GBP'),
array(1402,'1402 - Bank Exchange GBP'),
array(1003,'1003 - Quistclose Investor USD'),
array(1103,'1103 - Bank Clearing USD')
)
),
'currencies'=>array(
1002=>'GBP',
1102=>'GBP',
1402=>'GBP',
1003=>'USD',
1103=>'USD'
),
'codes'=>$codes,
'all_time_checks'=>array(
2101=>"='b-ETR & c-ETR'!U".($addCETR+7)." + CD<position>",
2104=>"='d-ETR'!X".($DETRPos+4)." + SUM( 'd-ETR'!BP:BP) + CD<position>",
2121=>"='d-ETR'!AF".($DETRPos+4)." + 'd-ETR'!AF".($DETRPos+6)." + CD<position>",
2122=>"=Commissions!A".($addCommissions+6)." + 'DRP Commissions'!A".($addCommissionsDRP+6)." + CD<position>",
2123=>"='d-ETR'!AR".($DETRPos+4)." + 'b-ETR & c-ETR'!Y".($addCETR+9)." + 'b-ETR & c-ETR'!AB".($addCETR+8)." + 'b-ETR & c-ETR'!AI".($addCETR+9)." + 'b-ETR & c-ETR'!AR".($addCETR+9)." + CD<position>",
2124=>"='Reserve Report'!T".$addOriginatorReserveReport." + 'Reserve Report'!V".$addOriginatorReserveReport." + 'Reserve Report'!X".$addOriginatorReserveReport." + 'Reserve Report'!Z".$addOriginatorReserveReport." + 'Reserve Report'!AB".$addOriginatorReserveReport." + CD<position>",
2125 => "='d-ETR'!Z" . ($DETRPos + 28) . " + 'd-ETR'!Z" . ($DETRPos + 30) . " - 'Credit Notes'!X" . ($addCN + 21) . " - 'Credit Notes'!X" . ($addCN + 23) . " - 'Specific Deductions'!W" . ($addSD + 17) . " - 'Specific Deductions'!W" . ($addSD + 19) . " + CD<position>",
3120=>"='d-ETR'!AL".($DETRPos+4)." - CD<position>",
3132=>"='d-ETR'!AJ".($DETRPos+4)." - CD<position>",
3134=>"='b-ETR & c-ETR'!AG".($addCETR+6)." - CD<position>",
3135=>"='d-ETR'!AC".($DETRPos+4)." + SUM('d-ETR'!BP:BP) - CD<position>",
3136=>"='Reserve Report'!S".$addOriginatorReserveReport." + 'Reserve Report'!U".$addOriginatorReserveReport." + 'Reserve Report'!W".$addOriginatorReserveReport." + 'Reserve Report'!Y".$addOriginatorReserveReport." + 'Reserve Report'!AA".$addOriginatorReserveReport." - CD<position>",
3150=>"='d-ETR'!AP".($DETRPos+4)." + 'b-ETR & c-ETR'!AR".($addCETR+7)." + 'b-ETR & c-ETR'!AP".($addCETR+7)." - CD<position>",
3501=>"=CD<position>",
1001 => "='d-ETR'!AD" . ($DETRPos + 5) . " + 'd-ETR'!AR" . ($DETRPos + 5) . " + 'd-ETR'!AK" . ($DETRPos + 5) . " + 'Returned Payments'!L" . ($addReturnedPayments + 3) . " + 'd-ETR'!AE" . ($DETRPos + 6) . " - 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 27) . " - 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 33) . " - 'Credit Notes'!N" . ($addCN + 4) . " - 'Specific Deductions'!M" . ($addSD + 6) . " + CD<position>",
1002=>"='d-ETR'!AE".($DETRPos+7)." + 'd-ETR'!AR".($DETRPos+9)." + 'd-ETR'!AN".($DETRPos+7)." + 'd-ETR'!AR".($DETRPos+11)." + 'd-ETR'!AN".($DETRPos+8)." + 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+21)." +'Payments (d-ETR)'!N".($addInboundPaymentsDETR+23)." - 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+28)." - 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+30)." - 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+19)." - 'Reserve Report'!V".$addOriginatorReserveReport." - 'Reserve Report'!Z".$addOriginatorReserveReport." - 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+7)." - 'Payments (OCPA)'!J".($addOPATransactions+8)." + CD<position>",
1003=>"='d-ETR'!AE".($DETRPos+8)." + 'd-ETR'!AR".($DETRPos+10)." + 'd-ETR'!AN".($DETRPos+9)." + 'd-ETR'!AR".($DETRPos+12)." + 'd-ETR'!AN".($DETRPos+10)." + 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+22)."+ 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+24)." - ('Payments (d-ETR)'!N".($addInboundPaymentsDETR+29)." + 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+31)." + 'Payments (d-ETR)'!N".($addInboundPaymentsDETR+37)." + 'Reserve Report'!AB".$addOriginatorReserveReport." + 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+8)." + 'Payments (OCPA)'!J".($addOPATransactions+9).") + CD<position>",
1201 => "='Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 33) . " + 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 34) . " + 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 35) . " + 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 36) . " + 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 37) . " + 'Reserve Report'!T" . $addOriginatorReserveReport . " + 'Reserve Report'!V" . $addOriginatorReserveReport . " + 'Reserve Report'!X" . $addOriginatorReserveReport . " + 'Reserve Report'!Z" . $addOriginatorReserveReport . " + 'Reserve Report'!AB" . $addOriginatorReserveReport . " + 'd-ETR'!AV" . ($DETRPos + 16) . " - ('Credit Notes'!N" . ($addCN + 27) . " + 'Specific Deductions'!M" . ($addSD + 17) . ") - 'd-ETR'!AF" . ($DETRPos + 17) . " + ('d-ETR'!AV" . ($DETRPos + 20) . " + 'd-ETR'!Z" . ($DETRPos + 14) . " - ('Credit Notes'!X" . ($addCN + 15) . " + 'Specific Deductions'!W" . ($addSD + 12) . ") - ('Credit Notes'!N" . ($addCN + 31) . " + 'Specific Deductions'!M" . ($addSD + 21) . ") - 'd-ETR'!AF" . ($DETRPos + 18) . ") + ('d-ETR'!AV" . ($DETRPos + 21) . " + 'd-ETR'!Z" . ($DETRPos + 15) . " - ('Credit Notes'!X" . ($addCN + 16) . " + 'Specific Deductions'!W" . ($addSD + 13) . ") - ('Credit Notes'!N" . ($addCN + 32) . " + 'Specific Deductions'!M" . ($addSD + 22) . ") - 'd-ETR'!AF" . ($DETRPos + 19) . ") + ('d-ETR'!AV" . ($DETRPos + 22) . " + 'd-ETR'!Z" . ($DETRPos + 16) . " - ('Credit Notes'!X" . ($addCN + 17) . " + 'Specific Deductions'!W" . ($addSD + 14) . ") - ('Credit Notes'!N" . ($addCN + 33) . " + 'Specific Deductions'!M" . ($addSD + 23) . ") - 'd-ETR'!AF" . ($DETRPos + 20) . ") + ('d-ETR'!AV" . ($DETRPos + 23) . " + 'd-ETR'!Z" . ($DETRPos + 17) . " - ('Credit Notes'!X" . ($addCN + 18) . " + 'Specific Deductions'!W" . ($addSD + 15) . ") - ('Credit Notes'!N" . ($addCN + 34) . " + 'Specific Deductions'!M" . ($addSD + 24) . ") - 'd-ETR'!AF" . ($DETRPos + 21) . ") - 'd-ETR'!AJ" . ($DETRPos + 4) . " - 'd-ETR'!AN" . ($DETRPos + 6) . " - 'Reserve Adjustments'!M" . ($addReserveAdjustments + 3) . " + 'Reserve Adjustments'!P" . ($addReserveAdjustments + 4) . " + CD<position>",
1401=>"='b-ETR & c-ETR'!AF".($addCETR+6)." + 'b-ETR & c-ETR'!AG".($addCETR+9)." - ('d-ETR'!AF".($DETRPos+10)." + Commissions!A".($addCommissions+5).") - 'd-ETR'!AR".($DETRPos+5)." - 'DRP Commissions'!A".($addCommissionsDRP+5)." - 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+6)." - 'Payments (OCPA)'!J".($addOPATransactions+7)." - ('d-ETR'!AF".($DETRPos+11)." + Commissions!B".($addCommissions+5).") - ('d-ETR'!AF".($DETRPos+12)." + Commissions!C".($addCommissions+5).") - ('d-ETR'!AF".($DETRPos+14)." + Commissions!E".($addCommissions+5).") - 'd-ETR'!AR".($DETRPos+10)." - 'd-ETR'!AR".($DETRPos+12)." - 'DRP Commissions'!B".($addCommissionsDRP+5)." - 'DRP Commissions'!C".($addCommissionsDRP+5)." - 'DRP Commissions'!E".($addCommissionsDRP+5)." + CD<position>",
1402=>"='b-ETR & c-ETR'!AG".($addCETR+8)." - ('d-ETR'!AF".($DETRPos+13)." + Commissions!D".($addCommissions+5).") - 'd-ETR'!AR".($DETRPos+9)." - 'd-ETR'!AR".($DETRPos+11)." - 'DRP Commissions'!D".($addCommissionsDRP+5)." + CD<position>",
1600=>"='Payments (d-ETR)'!N".($addInboundPaymentsDETR+6)." - 'Payments (d-ETR)'!L".($addInboundPaymentsDETR+6)." - 'd-ETR'!Y".($DETRPos+16)." - 'd-ETR'!Y".($DETRPos+17)." - 'd-ETR'!Y".($DETRPos+18)." + ('Reserve Report'!T".$addOriginatorReserveReport." + 'Reserve Report'!V".$addOriginatorReserveReport." + 'Reserve Report'!X".$addOriginatorReserveReport." + 'Reserve Report'!Z".$addOriginatorReserveReport." + 'Reserve Report'!AB".$addOriginatorReserveReport.") + 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+5)." + 'Payments (OCPA)'!J".($addOPATransactions+6)." - 'b-ETR & c-ETR'!AS".($addCETR+7)." - 'b-ETR & c-ETR'!AI".($addCETR+9)." - 'b-ETR & c-ETR'!Y".($addCETR+9)." - 'b-ETR & c-ETR'!AB".($addCETR+8)." - 'b-ETR & c-ETR'!AR".($addCETR+9)." - CD<position>",
1904 => "='d-ETR'!X" . ($DETRPos + 4) . " - 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 6) . " + 'Payments (d-ETR)'!L" . ($addInboundPaymentsDETR + 6) . " - ('Reserve Report'!S" . $addOriginatorReserveReport . " + 'Reserve Report'!U" . $addOriginatorReserveReport . " + 'Reserve Report'!W" . $addOriginatorReserveReport . " + 'Reserve Report'!Y" . $addOriginatorReserveReport . " + 'Reserve Report'!AA" . $addOriginatorReserveReport . ") + 'd-ETR'!Z" . ($DETRPos + 6) . " - 'Credit Notes'!X" . ($addCN + 8) . " - 'Specific Deductions'!W" . ($addSD + 5) . " - CD<position>",
1903=>"='b-ETR & c-ETR'!U".($addCETR+7)." - 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+5)." - 'Payments (OCPA)'!J".($addOPATransactions+6)." + 'b-ETR & c-ETR'!AI".($addCETR+9)." + 'b-ETR & c-ETR'!Y".($addCETR+9)." + 'b-ETR & c-ETR'!AB".($addCETR+8)." - CD<position>",
8130=>"=('d-ETR'!AP".($DETRPos+4)." + 'b-ETR & c-ETR'!AR".($addCETR+7)." + 'b-ETR & c-ETR'!AP".($addCETR+7).") - ('d-ETR'!AR".($DETRPos+4)." + 'b-ETR & c-ETR'!AR".($addCETR+9)." + 'b-ETR & c-ETR'!AP".($addCETR+7).") + CD<position>",
8150=>"='b-ETR & c-ETR'!AP".($addCETR+7)." + CD<position>",
8310 => "=('d-ETR'!AV" . ($DETRPos + 4) . " + 'd-ETR'!Z" . ($DETRPos + 9) . " + 'd-ETR'!Z" . ($DETRPos + 10) . " + 'd-ETR'!Z" . ($DETRPos + 11) . " + 'd-ETR'!Z" . ($DETRPos + 12) . " - 'Credit Notes'!X" . ($addCN + 10) . " - 'Credit Notes'!X" . ($addCN + 11) . " - 'Credit Notes'!X" . ($addCN + 12) . " - 'Credit Notes'!X" . ($addCN + 13) . " - 'Specific Deductions'!W" . ($addSD + 7) . " - 'Specific Deductions'!W" . ($addSD + 8) . " - 'Specific Deductions'!W" . ($addSD + 9) . " - 'Specific Deductions'!W" . ($addSD + 10) . " - ('Reserve Report'!T" . $addOriginatorReserveReport . " + 'Reserve Report'!V" . $addOriginatorReserveReport . " + 'Reserve Report'!X" . $addOriginatorReserveReport . " + 'Reserve Report'!Z" . $addOriginatorReserveReport . " + 'Reserve Report'!AB" . $addOriginatorReserveReport . ") - 'Payments (d-ETR)'!N" . ($addInboundPaymentsDETR + 18) . ") - ('d-ETR'!AV" . ($DETRPos + 15) . " - ('Credit Notes'!L" . ($addCN + 4) . " + 'Credit Notes'!L" . ($addCN + 8) . " + 'Credit Notes'!L" . ($addCN + 9) . " + 'Credit Notes'!L" . ($addCN + 10) . " + 'Credit Notes'!L" . ($addCN + 11) . " + 'Specific Deductions'!K" . ($addSD + 6) . " + 'Specific Deductions'!K" . ($addSD + 10) . " + 'Specific Deductions'!K" . ($addSD + 11) . " + 'Specific Deductions'!K" . ($addSD + 12) . " + 'Specific Deductions'!K" . ($addSD + 13) . ") + 'd-ETR'!Z" . ($DETRPos + 14) . " + 'd-ETR'!Z" . ($DETRPos + 15) . " + 'd-ETR'!Z" . ($DETRPos + 16) . " + 'd-ETR'!Z" . ($DETRPos + 17) . " - 'Credit Notes'!X" . ($addCN + 15) . " - 'Credit Notes'!X" . ($addCN + 16) . " - 'Credit Notes'!X" . ($addCN + 17) . " - 'Credit Notes'!X" . ($addCN + 18) . " - 'Specific Deductions'!W" . ($addSD + 12) . " - 'Specific Deductions'!W" . ($addSD + 13) . " - 'Specific Deductions'!W" . ($addSD + 14) . " - 'Specific Deductions'!W" . ($addSD + 15) . ") + 'Reserve Adjustments'!M" . ($addReserveAdjustments + 3) . " - 'Reserve Adjustments'!P" . ($addReserveAdjustments + 4) . " + CD<position>",
8320=>"='d-ETR'!AJ".($DETRPos+4)." + 'd-ETR'!AL".($DETRPos+4)." - 'd-ETR'!AY".($DETRPos+4)." - 'd-ETR'!AV".($DETRPos+4)." + CD<position>",
8330=>"='d-ETR'!AY".($DETRPos+4)." + CD<position>",
8404=>"='Payments (d-ETR)'!N".($addInboundPaymentsDETR+6)." - 'Payments (d-ETR)'!L".($addInboundPaymentsDETR+6)." - 'd-ETR'!Y".($DETRPos+16)." - 'd-ETR'!Y".($DETRPos+17)." - 'd-ETR'!Y".($DETRPos+18)." + ('Reserve Report'!T".$addOriginatorReserveReport." + 'Reserve Report'!V".$addOriginatorReserveReport." + 'Reserve Report'!X".$addOriginatorReserveReport." + 'Reserve Report'!Z".$addOriginatorReserveReport." + 'Reserve Report'!AB".$addOriginatorReserveReport.") + 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+5)." + 'Payments (OCPA)'!J".($addOPATransactions+6)." - 'b-ETR & c-ETR'!AS".($addCETR+7)." - 'b-ETR & c-ETR'!AI".($addCETR+9)." - 'b-ETR & c-ETR'!Y".($addCETR+9)." - 'b-ETR & c-ETR'!AB".($addCETR+8)." - 'b-ETR & c-ETR'!AR".($addCETR+9)." + CD<position>"
)
),FALSE);
$this->styles=array(
'E'=>3,
'BI'=>3
);
for($key_index=4;$key_index<=92;$key_index++)
{
$this->styles[$this->indexToColumn($key_index)]=3;
}
$columns=array('Ledger','Ccy','Code','Description');
foreach($codes as $code)
{
$columns[]=$code['code_id'].$code['code_text'];
}
$columns = array_merge($columns, array(
'EUR',
'TFx EUR',
'',
'EUR',
'GBP',
'USD',
'ALL TIME FOR ALL DATA'
));
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet17.xml',$columns);
}
function addAccounts($requestedDate = null, $balanceReport = null)
{
Bin_Config::requireAdminModel('MAEtr');
require_once ROOT_FOLDER . 'admin/classes/Model/investor_actions.php';
$investorActions = new Investor_actions();
$balanceReportCounter = null;
if (isset($balanceReport['data'])) {
$balanceReportCounter = 17; //start value
foreach ($balanceReport['data'] as $originatorsData) {
if (isset($originatorsData['stats'])) {
foreach ($originatorsData['stats'] as $currenciesData) {
foreach ($currenciesData as $debtorsData) {
if (isset($debtorsData['outstanding'])) {
$balanceReportCounter++;
}
}
}
}
}
}
$html_content = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/management_accounts_xslx.html', array(
'yield' => $investorActions->getDeferredYieldByYear(date('Y'), $requestedDate),
'balance_report_counter' => $balanceReportCounter,
'a_etr_values' => Model_MAEtr::getAEtrValuesForTpr(),
), FALSE);
$this->styles=array(
'G'=>-4,
'H'=>31,
'E'=>31,
'I'=>31,
'J'=>-4,
'K'=>31
);
$additional_styles=array();
$additional_styles['G2']=36;
$additional_styles['J2']=36;
$additional_styles['H3']=36;
$additional_styles['K3']=36;
$additional_styles['G102']=36;
$additional_styles['J102']=36;
$additional_styles['H103']=36;
$additional_styles['K103']=36;
foreach(array('G3','G4','G38','G50','G51','G87','J3','J31','J64','G103','D103','G119','J92','J103','J111','J129','E24') as $cell)
{
$additional_styles[$cell]=13;
}
$thin_lines=array('G36','H36','G46','H46','G76','H76','G81','H81','J29','K29','J59','K59','J89','K89','G111','H111','G115','H115','G133','H133','G130','H130','J109','K109','J123','K123','J139','K139','E25');
foreach($thin_lines as $cell)
{
$additional_styles[$cell]=21;
}
$double_lines=array('G48','H48','G83','H83','G85','H85','G91','H91','J61','K61','J91','K91','G117','H117','G135','H135','G137','H137','G141','H141','J126','K126','J141','K141');
foreach($double_lines as $cell)
{
$additional_styles[$cell]=22;
}
//$additional_styles['H98']=37;
$additional_styles['H99']=40;
$additional_styles['I26']=31;
$additional_styles['I27']=31;
$additional_styles['C102']=13;
$additional_styles['E19']=13;
$additional_styles['E25']=13;
$additional_styles['E23']=40;
$additional_styles['E31']=40;
$additional_styles['E103']=25;
$additional_styles['E104']=25;
$additional_styles['E105']=25;
$additional_styles['E106']=24;
$additional_styles['E107']=26;
$additional_styles['G94']=39;
$additional_styles['H94']=38;
for($cell_index=105;$cell_index<=143;$cell_index++)
{
foreach(array('H','K') as $column_id)
{
if (in_array($column_id.$cell_index,$thin_lines))
{
$additional_styles[$column_id.$cell_index]=23;
}
elseif(in_array($column_id.$cell_index,$double_lines))
{
$additional_styles[$column_id.$cell_index]=24;
}
else
{
$additional_styles[$column_id.$cell_index]=25;
}
}
}
foreach(array('K62','K92','H142','K142','K127') as $cell)
{
$additional_styles[$cell]=26;
}
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet32.xml',array('','','','','','','','','','','','','','',''),$additional_styles);
}
function addAccountsOld($requestedDate = null, $balanceReport = null)
{
require_once ROOT_FOLDER . 'admin/classes/Model/investor_actions.php';
$investorActions = new Investor_actions();
$balanceReportCounter = null;
if (isset($balanceReport['data'])) {
$balanceReportCounter = 17; //start value
foreach ($balanceReport['data'] as $originatorsData) {
if (isset($originatorsData['stats'])) {
foreach ($originatorsData['stats'] as $currenciesData) {
foreach ($currenciesData as $debtorsData) {
if (isset($debtorsData['outstanding'])) {
$balanceReportCounter++;
}
}
}
}
}
}
$html_content = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/management_accounts_xslx_old.html', array(
'yield' => $investorActions->getDeferredYieldByYear(date('Y'), $requestedDate),
'balance_report_counter' => $balanceReportCounter,
), FALSE);
$this->styles=array(
'H'=>-4,
'I'=>31,
'J'=>31,
'K'=>-4,
'L'=>31
);
$additional_styles=array();
$additional_styles['H2']=36;
$additional_styles['K2']=36;
$additional_styles['I3']=36;
$additional_styles['L3']=36;
$additional_styles['H103']=36;
$additional_styles['K103']=36;
$additional_styles['I104']=36;
$additional_styles['L104']=36;
foreach(array('H3','H4','H36','H44','H45','H48','H49','H86','K3','K29','K62','H104','H119','K91','K92','L92','K104','K112','K130') as $cell)
{
$additional_styles[$cell]=13;
}
$thin_lines=array('H34','I34','H44','I44','H75','I75','H80','I80','K27','L27','K57','L57','K88','L88','H111','I111','H115','I115','H134','I134','H131','I131','K110','L110','K124','L124','K140','L140');
foreach($thin_lines as $cell)
{
$additional_styles[$cell]=21;
}
$double_lines=array('H46','I46','H82','I82','H84','I84','H90','I90','K59','L59','K90','L90','H117','I117','H136','I136','H138','I138','H142','I142','K127','L127','K142','L142');
foreach($double_lines as $cell)
{
$additional_styles[$cell]=22;
}
$additional_styles['I99']=37;
$additional_styles['J26']=31;
$additional_styles['J27']=31;
for($cell_index=106;$cell_index<=143;$cell_index++)
{
foreach(array('I','L') as $column_id)
{
if (in_array($column_id.$cell_index,$thin_lines))
{
$additional_styles[$column_id.$cell_index]=23;
}
elseif(in_array($column_id.$cell_index,$double_lines))
{
$additional_styles[$column_id.$cell_index]=24;
}
else
{
$additional_styles[$column_id.$cell_index]=25;
}
}
}
foreach(array('L60','L91','I94','I143','L143','L128') as $cell)
{
$additional_styles[$cell]=26;
}
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet18.xml',array('','','','','','','','','','','','','','',''),$additional_styles);
}
function addOutstandingBalances(DateTime $requestedDate = null)
{
Bin_Config::requireAdminModel('MReports', 'MStatements', 'MHelpers');
$statementModel = new Model_MStatements();
$reportModel = new Model_MReports();
$originators = $statementModel->statementOriginators();
$currencyRateDate = date('Y-m-d');
$toDate = $requestedDate ? $requestedDate->format('Y-m-d') : date('Y-m-d');
if ($requestedDate instanceof DateTime and in_array($requestedDate->format('N'), array(6, 7))) {
// if we generate TPR in Saturday or Sunday
$currencyRateDate = date('Y-m-d', strtotime($requestedDate->format('Y-m-d') . ' previous friday'));
}
$filter = array(
'from_date' => FIRST_TRADE_DATE,
'to_date' => $toDate,
);
foreach ($originators as $originator) {
$filter['user_id'][] = $originator['user_id'];
}
$balanceReport = $reportModel->getOriginatorReport($filter, false, $currencyRateDate);
$htmlContent = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/originator_report_tpr.html', $balanceReport, false);
$this->styles = array(
'A' => -1,
'B' => -2,
'C' => -1,
'D' => -1,
'E' => -1,
'F' => 3,
'G' => 3,
'H' => 3,
'I' => 3,
'J' => 3,
'K' => 3,
'L' => 3,
'M' => 3,
'N' => 3,
'O' => 3,
'P' => 3,
'Q' => 3,
'R' => 3,
);
$this->HTMLtoOOXML($htmlContent, 'xl/worksheets/sheet29.xml', array('Originator ID', 'Originator Name', 'Status on TaR', 'Type', 'Ccy', 'Total Outstanding', 'Face Value Total', 'Disallowed', 'Commission Total', 'Payments Total', 'Fees and Charges', 'Total Overdue', 'Total Reserve', 'Reserve Due', 'OCPA', 'Unallocated IDP', 'Unallocated ICP', 'Trailing Balances'));
return $balanceReport;
}
function addStatutoryAccounts()
{
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/statutory_accounts_xslx.html',array(),FALSE);
$this->styles=array(
'G'=>41,
'H'=>-6,
'I'=>42,
'J'=>41,
'K'=>-6,
'L'=>42,
'M'=>41,
);
$additional_styles=array();
for($i=1; $i<=30; $i++){
$additional_styles['H' . $i]=41;
$additional_styles['K' . $i]=41;
}
foreach(array('H2','K2') as $cell)
{
$additional_styles[$cell]=46;
}
foreach(array('I3','L3') as $cell)
{
$additional_styles[$cell]=43;
}
foreach(array('I7','I12','I16','I22','I28','L9','L16','L18','L22','L25','L28') as $cell)
{
$additional_styles[$cell]=44;
}
foreach(array('I23','I29','L29') as $cell)
{
$additional_styles[$cell]=45;
}
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet20.xml',array('','','','','','','','','','','',''),$additional_styles, 1);
}
function addUnallocatedPayments()
{
$types = array(
INBOUND_DEBTOR_PAYMENT,
TRANSFER_OF_PAYMENT_BY_ORIGINATOR,
PAID_DIRECTLY_TO_ORIGINATOR,
INBOUND_CREDITOR_PAYMENT,
ORIGINATOR_PAYMENT_ON_ACCOUNT,
ORIGINATOR_DEPOSIT,
);
$sql = "SELECT
od.user_id,
dd.debtor_id,
od.organisation_reference_id,
IF(od.organisation_trade_name != '', od.organisation_trade_name, od.organisation_name) as organisation_name,
dd.debtor_reference_id,
IF(dd.trade_name='', dd.debtor_name, dd.trade_name) as debtor_name,
main_trans.transaction_date as payment_date,
main_trans.created_at as date_entered,
IFNULL(cm.currency_code, cm2.currency_code) as currency,
main_trans.amount as amount,
main_trans.transaction_type,
IF(main_trans.is_aetr = 1, 'a', IF(main_trans.transaction_type = 110 AND main_trans.orpa_id, 'b', IFNULL(dd.etr_type, od.etr_type))) as etr_type,
IF(IFNULL(cm.currency_id, cm2.currency_id) = 1, 1, cr.currency_rate) as fx_rate
FROM manual_transactions as main_trans
LEFT JOIN debtors_detail dd ON dd.debtor_id = main_trans.debtor_id
LEFT JOIN debtor_relation dr ON dr.debtor_id = dd.debtor_id
LEFT JOIN organisation_details od ON main_trans.originator_id = od.user_id
LEFT JOIN currencies cm ON cm.currency_id = dd.currency_id
LEFT JOIN currencies cm2 ON cm2.currency_id = main_trans.currency_id
LEFT JOIN currency_rates cr on IFNULL(cm.currency_id, cm2.currency_id) = cr.currency_id AND main_trans.transaction_date = cr.currency_date
WHERE main_trans.status = 1 AND main_trans.reconcile_ref = 0 AND main_trans.false_payment = 0
AND main_trans.fees_commissions = 0
AND transaction_type IN (:types)
ORDER BY user_id, IFNULL(cm.currency_code, cm2.currency_code), debtor_id, date_entered DESC";
$sql = strtr($sql, array(
':types' => implode(',', $types),
));
$this->query->executeQuery($sql);
$records = $this->query->records;
$count = $this->query->totrows;
$totals = array();
foreach ($records as $rec) {
$totals[$rec['transaction_type']][$rec['currency']]['type'] = array('transaction_type' => $rec['transaction_type']);
$totals[$rec['transaction_type']][$rec['currency']]['amount'] += $rec['amount'];
}
ksort($totals);
$this->styles = array(
'A' => -1,
'B' => -2,
'C' => -1,
'D' => -2,
'E' => 2,
'F' => 2,
// 'G' => 4,
'H' => -1,
'I' => -1,
'J' => -1,
'K' => -1,
'L' => -2,
'M' => -1,
'N' => -1,
);
$file_name = 'xl/worksheets/sheet30.xml';
$headers = array('Originator ID', 'Originator Name', 'Debtor ID', 'Debtor Name', 'Payment Date', 'Date Entered', 'Currency', 'Amount Received', 'Type', 'ETR Type', 'Fx Rate', 'EUR Amount Received', '', '');
$this->writeSheetHeader($file_name, $headers, $count);
$htmlContent = Bin_Template::createTemplate(
ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/unallocatedpaymentsreport.html',
array(
'report' => $records,
'totals' => $totals,
),
false
);
$addStyle = $count + 3;
$additionalStyles = array();
for ($i = 1; $i <= 20; $i++) {
$additionalStyles['B' . $addStyle] = 3;
$addStyle++;
}
$this->styles = array(
'A' => -1,
'B' => -2,
'C' => -1,
'D' => -2,
'E' => 2,
'F' => 2,
// 'G' => 4,
'H' => 3,
'J' => 6,
'K' => 4,
'L' => 3,
'N' => 3,
);
$result = $this->HTMLtoOOXML($htmlContent, false, array(), $additionalStyles, 2, 0, $file_name);
file_put_contents($this->files_location . $file_name, implode("", $result), FILE_APPEND);
$this->writeSheetFooter($file_name);
}
function addMarginCalls()
{
require_once(ROOT_FOLDER . 'admin/classes/Model/margin_calls.php');
$reports = new Margin_calls();
$data = $reports->getReport(true, true);
$file_name = 'xl/worksheets/sheet31.xml';
$this->styles = array(
'B' => 2,
'C' => -1,
'D' => -2,
'E' => -1,
'F' => -2,
'H' => -1,
'I' => 3,
'K' => 3,
'M' => 3,
'N' => 3,
'O' => 2,
'P' => 2,
'Q' => 3,
);
$headers = array('Type', 'Date', 'Originator ID', 'Originator Name', 'Credebtor ID', 'Credebtor Name', 'Ccy', 'Trade ID', 'Settled Loss', 'Status', 'Closed Loss', 'Status', 'Settled Gain', 'Closed Gain', 'Settled', 'Closed', 'Margin call amount paid');
$this->writeSheetHeader($file_name, $headers, count($data));
$htmlContent = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/margin_calls.html', array('data' => $data,), false);
$result = $this->HTMLtoOOXML($htmlContent, false, array(), array(), 2, 0, $file_name);
$tempResult = array();
foreach ($result as $res) {
$tempResult[] = $res;
if (count($tempResult) == 50) {
file_put_contents($this->files_location . $file_name, implode("", $tempResult), FILE_APPEND);
$tempResult = array();
$this->query->executeQuery('UPDATE trp_requests SET completed = IF(completed < 98, completed + 1, completed) WHERE request_id = '.(int)$this->track_progress);
}
}
file_put_contents($this->files_location . $file_name, implode("", $tempResult), FILE_APPEND);
$this->writeSheetFooter($file_name);
}
function addOriginatorProfiles()
{
Bin_Config::requireAdminModel('MAEtr');
$selectAEtrData = "SELECT od.organisation_reference_id,
IF(od.organisation_trade_name = '', od.organisation_name, od.organisation_trade_name) as organisation_name,
'a' as etr_type
FROM organisation_details od
JOIN a_etr ae ON ae.organisation_id = od.organisation_id
JOIN invoice_master_assets ima ON ima.a_etr_id = ae.a_etr_id
WHERE ae.status = " . Model_MAEtr::STATUS_APPROVED . "
GROUP BY od.organisation_id
ORDER BY od.organisation_reference_id";
$this->query->executeQuery($selectAEtrData);
$aEtrData = $this->query->records;
$aEtrValues = Model_MAEtr::getAEtrTradedValuesByYear();
$selectData = "SELECT od.organisation_reference_id,
IF(od.organisation_trade_name = '', od.organisation_name, od.organisation_trade_name) as organisation_name,
IF(im.is_aetr = 1, 'a', im.etr_type) as etr_type
FROM invoice_master im
JOIN organisation_details od on im.user_id = od.user_id
WHERE im.root_invoice_id > 0 AND im.log_status = 1
GROUP BY od.organisation_reference_id, etr_type
ORDER BY etr_type, od.organisation_reference_id";
$this->query->executeQuery($selectData);
$data = $total = array();
foreach ($this->query->records as $rec) {
$data[$rec['etr_type']][] = $rec;
}
//$data['a'] = $aEtrData;
foreach ($data as $etrData) {
foreach ($etrData as $datum) {
if (!isset($total[$datum['organisation_reference_id']])) {
$total[$datum['organisation_reference_id']] = $datum;
}
}
}
ksort($total);
foreach ($total as $tot) {
$data['total'][] = $tot;
}
$years = range(2013, date('Y'));
$count = range(1, max(count($data['a']), count($data['b']), count($data['c']), count($data['d']), count($data['total'])));
// we want know originator column and current year column for each section
$columns = array();
$startColumn = 2;
$columns['a']['originator_column'] = $this->indexToColumn($startColumn);
$columns['a']['id_column'] = $this->indexToColumn($startColumn-1);
$yearIterator = 2;
foreach ($years as $year) {
$columns['a'][$year] = $this->indexToColumn($startColumn + $yearIterator);
$yearIterator++;
}
$columns['a']['year_column'] = $this->indexToColumn($startColumn + count($years) + 1);
$columns['a']['previous_year_column'] = $this->indexToColumn($startColumn + count($years));
$columns['a']['total_column'] = $this->indexToColumn($startColumn + count($years) + 2);
$columns['a']['n_column'] = $this->indexToColumn($startColumn + count($years) + 3);
$columns['a']['count_records'] = count($data['a']);
$startColumn = $startColumn + count($years) + 5;
$columns['b']['originator_column'] = $this->indexToColumn($startColumn);
$columns['b']['id_column'] = $this->indexToColumn($startColumn-1);
$yearIterator = 2;
foreach ($years as $year) {
$columns['b'][$year] = $this->indexToColumn($startColumn + $yearIterator);
$yearIterator++;
}
$columns['b']['year_column'] = $this->indexToColumn($startColumn + count($years) + 1);
$columns['b']['previous_year_column'] = $this->indexToColumn($startColumn + count($years));
$columns['b']['n_column'] = $this->indexToColumn($startColumn + count($years) + 2);
$columns['b']['count_records'] = count($data['b']);
$startColumn = $startColumn + count($years) + 4;
$columns['c']['originator_column'] = $this->indexToColumn($startColumn);
$columns['c']['id_column'] = $this->indexToColumn($startColumn-1);
$yearIterator = 2;
foreach ($years as $year) {
$columns['c'][$year] = $this->indexToColumn($startColumn + $yearIterator);
$yearIterator++;
}
$columns['c']['year_column'] = $this->indexToColumn($startColumn + count($years) + 1);
$columns['c']['previous_year_column'] = $this->indexToColumn($startColumn + count($years));
$columns['c']['n_column'] = $this->indexToColumn($startColumn + count($years) + 2);
$columns['c']['count_records'] = count($data['c']);
$startColumn = $startColumn + count($years) + 4;
$columns['d']['originator_column'] = $this->indexToColumn($startColumn);
$columns['d']['id_column'] = $this->indexToColumn($startColumn-1);
$yearIterator = 2;
foreach ($years as $year) {
$columns['d'][$year] = $this->indexToColumn($startColumn + $yearIterator);
$yearIterator++;
}
$columns['d']['year_column'] = $this->indexToColumn($startColumn + count($years) + 1);
$columns['d']['previous_year_column'] = $this->indexToColumn($startColumn + count($years));
$columns['d']['total_column'] = $this->indexToColumn($startColumn + count($years) + 2);
$columns['d']['n_column'] = $this->indexToColumn($startColumn + count($years) + 3);
$columns['d']['count_records'] = count($data['d']);
$startColumn = $startColumn + count($years) + 5;
$columns['total']['originator_column'] = $this->indexToColumn($startColumn);
$yearIterator = 2;
foreach ($years as $year) {
$columns['total'][$year] = $this->indexToColumn($startColumn + $yearIterator);
$yearIterator++;
}
$columns['total']['year_column'] = $this->indexToColumn($startColumn + count($years) + 1);
$columns['total']['previous_year_column'] = $this->indexToColumn($startColumn + count($years));
$columns['total']['total_column'] = $this->indexToColumn($startColumn + count($years) + 2);
$this->styles = array(
'B' => 6,
'C' => -2,
'D' => -2,
);
// generate headers
$headers = array('','','','','',);
foreach($years as $year){
array_push($headers, 'Value ' . $year);
}
$startHeader = 4;
$endHeader = 5 * count($years) + 25;
while($startHeader < $endHeader){
$this->styles[$this->indexToColumn($startHeader)] = 31;
array_push($headers, '');
$startHeader++;
}
$this->styles[$this->indexToColumn(4 + 1 * (count($years) + 1))] = 48;
$this->styles[$this->indexToColumn(4 + 1 * (count($years) + 2))] = 6;
$this->styles[$this->indexToColumn(4 + 1 * (count($years) + 2) + 1)] = -2;
$this->styles[$this->indexToColumn(4 + 1 * (count($years) + 2) + 2)] = -2;
$this->styles[$this->indexToColumn(4 + 2 * (count($years) + 2) + 1)] = 48;
$this->styles[$this->indexToColumn(4 + 2 * (count($years) + 2) + 2)] = 6;
$this->styles[$this->indexToColumn(4 + 2 * (count($years) + 2) + 3)] = -2;
$this->styles[$this->indexToColumn(4 + 2 * (count($years) + 2) + 4)] = -2;
$this->styles[$this->indexToColumn(4 + 3 * (count($years) + 2) + 3)] = 48;
$this->styles[$this->indexToColumn(4 + 3 * (count($years) + 2) + 4)] = 6;
$this->styles[$this->indexToColumn(4 + 3 * (count($years) + 2) + 5)] = -2;
$this->styles[$this->indexToColumn(4 + 3 * (count($years) + 2) + 6)] = -2;
$this->styles[$this->indexToColumn(4 + 4 * (count($years) + 2) + 6)] = 48;
$this->styles[$this->indexToColumn(4 + 4 * (count($years) + 2) + 7)] = 6;
$this->styles[$this->indexToColumn(4 + 4 * (count($years) + 2) + 8)] = -2;
$this->styles[$this->indexToColumn(4 + 4 * (count($years) + 2) + 9)] = -2;
$additional_styles = array();
$emptyString = count($count) + 6;
foreach($years as $year){
$additional_styles[$columns['a'][$year] . ($emptyString + 2)] = 47;
$additional_styles[$columns['b'][$year] . ($emptyString + 2)] = 47;
$additional_styles[$columns['c'][$year] . ($emptyString + 2)] = 47;
$additional_styles[$columns['d'][$year] . ($emptyString + 2)] = 47;
$additional_styles[$columns['total'][$year] . ($emptyString + 3)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 4)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 4)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 4)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 4)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 4)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 5)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 5)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 5)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 5)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 5)] = 47;
$additional_styles[$columns['a'][$year] . ($emptyString + 6)] = 47;
$additional_styles[$columns['b'][$year] . ($emptyString + 6)] = 47;
$additional_styles[$columns['c'][$year] . ($emptyString + 6)] = 47;
$additional_styles[$columns['d'][$year] . ($emptyString + 6)] = 47;
$additional_styles[$columns['total'][$year] . ($emptyString + 6)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 7)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 7)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 7)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 7)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 7)] = 47;
$additional_styles[$columns['a'][$year] . ($emptyString + 8)] = 47;
$additional_styles[$columns['b'][$year] . ($emptyString + 8)] = 47;
$additional_styles[$columns['c'][$year] . ($emptyString + 8)] = 47;
$additional_styles[$columns['d'][$year] . ($emptyString + 8)] = 47;
$additional_styles[$columns['total'][$year] . ($emptyString + 8)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 9)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 9)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 9)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 9)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 9)] = 47;
$additional_styles[$columns['a'][$year] . ($emptyString + 10)] = 47;
$additional_styles[$columns['b'][$year] . ($emptyString + 10)] = 47;
$additional_styles[$columns['c'][$year] . ($emptyString + 10)] = 47;
$additional_styles[$columns['d'][$year] . ($emptyString + 10)] = 47;
$additional_styles[$columns['total'][$year] . ($emptyString + 10)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 11)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 11)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 11)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 11)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 11)] = 47;
$additional_styles[$columns['a'][$year] . ($emptyString + 12)] = 47;
$additional_styles[$columns['b'][$year] . ($emptyString + 12)] = 47;
$additional_styles[$columns['c'][$year] . ($emptyString + 12)] = 47;
$additional_styles[$columns['d'][$year] . ($emptyString + 12)] = 47;
$additional_styles[$columns['total'][$year] . ($emptyString + 12)] = 6;
$additional_styles[$columns['a'][$year] . ($emptyString + 14)] = 6;
$additional_styles[$columns['b'][$year] . ($emptyString + 14)] = 6;
$additional_styles[$columns['c'][$year] . ($emptyString + 14)] = 6;
$additional_styles[$columns['d'][$year] . ($emptyString + 14)] = 6;
$additional_styles[$columns['total'][$year] . ($emptyString + 14)] = 6;
}
$file_name = 'xl/worksheets/sheet33.xml';
$htmlContent = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/originator_profiles.html', array(
'data' => $data,
'years' => $years,
'count_rows' => $count,
'count_rows_count' => count($count),
'columns' => $columns,
'a_etr_values' => $aEtrValues,
), false);
$this->HTMLtoOOXML($htmlContent, $file_name, $headers, $additional_styles, 2, 0, $file_name);
}
function addCredebtorConcentration()
{
$selectCredebtors = "SELECT dd.debtor_reference_id,
IF(dd.trade_name = '', dd.debtor_name, dd.trade_name) as debtor_name,
dd.type
FROM debtors_detail dd
JOIN invoice_master im ON im.debtor_id = dd.debtor_id AND im.invoice_status = 3 AND im.root_invoice_id > 0
GROUP BY dd.debtor_id
ORDER BY debtor_reference_id";
$data = array();
$this->query->executeQuery($selectCredebtors);
foreach($this->query->records as $rec){
$data[$rec['type']][] = $rec;
}
$count = range(1, max(count($data[TYPE_DEBTOR]), count($data[TYPE_CREDITOR])));
$this->styles=array(
'B'=>-2,
'C'=>-2,
'D'=>31,
'E'=>11,
'G'=>-2,
'H'=>-2,
'I'=>31,
'J'=>11,
);
$file_name = 'xl/worksheets/sheet34.xml';
$htmlContent = Bin_Template::createTemplate(ROOT_FOLDER . 'admin/templates/reports/accounts_journal/v2/credebtor_concentration.html', array(
'data' => $data,
'count_rows' => $count,
'count_rows_count' => count($count),
), false);
$this->HTMLtoOOXML($htmlContent, $file_name, array('', 'Creditor ID', 'Creditor Name', 'Value', 'Dist','', 'Debtor ID', 'Debtor Name', 'Value', 'Dist'), array(), 2, 0, $file_name);
}
function getXLSXDate($date)
{
$interval = date_diff(date_create(date('Y-m-d',$date)), date_create("1900-01-01"));
return (int)$interval->format('%a')+3;
}
function buildExelDates()
{
$this->excel_dates=array(
'dates'=>array(),
'end_of_years'=>array(),
'last_friday'=>'',
'revenue_weekly'=>array(),
);
$current_date=$start_date=strtotime('2013-07-03');
$end_date=mktime(NULL,NULL,NULL);
while($current_date<$end_date)
{
$current_date+=86400;
if (in_array(date('l',$current_date),array('Sunday','Saturday')))
{
continue;
}
$this->excel_dates['dates'][$this->getXLSXDate($current_date)]=date('l',$current_date);
if (date('md',$current_date)=='1231')
{
$this->excel_dates['end_of_years'][$this->getXLSXDate($current_date)]=date('Y-m-d',$current_date);
}
if (date('l',$current_date)=='Friday')
{
if (date('Y',$current_date)==date('Y'))
{
if (count($this->excel_dates['revenue_weekly'])==0)
{
$this->excel_dates['revenue_weekly'][]=$this->getXLSXDate(strtotime($this->excel_dates['last_friday']));
}
$this->excel_dates['revenue_weekly'][]=$this->getXLSXDate($current_date);
}
$this->excel_dates['last_friday']=date('Y-m-d',$current_date);
}
}
$this->excel_dates['end_of_years'][$this->getXLSXDate(strtotime($this->excel_dates['last_friday']))]=$this->excel_dates['last_friday'];
$this->excel_dates['revenue_weekly'][]=$this->getXLSXDate(strtotime($this->excel_dates['last_friday'])+86400*7);
}
function addTranslationFx()
{
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/translation_fx_xlsx.html',array(
'dates'=>$this->excel_dates['dates'],
'end_date'=>$this->getXLSXDate(mktime(NULL,NULL,NULL)),
'end_of_years'=>$this->excel_dates['end_of_years'],
'last_friday'=>$this->excel_dates['last_friday']
),FALSE);
$this->styles=array(
'B'=>2,
'C'=>3,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M'=>3,
'O'=>3,
'P'=>3,
'R'=>3,
'S'=>3,
'T'=>3,
'V'=>4,
'W'=>4,
'X'=>3
);
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet19.xml',array('','','','','','','','','','','','','','','','','','','','','','','',''));
}
function addJournals()
{
$this->accounts_journal->initXeroLedgers();
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/xero_journals_xlsx.html',array(
'xero_ledgers'=>$this->accounts_journal->xero_ledgers,
'ledger_codes'=>$this->accounts_journal->ledger_codes
),FALSE);
$this->styles=array(
'A'=>-5,
'B'=>2,
'C'=>-4,
'F'=>3,
'G'=>-4
);
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet21.xml',array('Narration','Date','Description','AccountCode','TaxRate','Amount','TrackingName1'));
}
function addKMI()
{
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/kmi_originators_xlsx.html',array(
'originators'=>Model_MStatements::statementOriginators()
),FALSE);
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>3,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'H'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3
);
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet22.xml',array('Originaator ID','Originator Name','Total','Fees','Commissions','Reserves','2121 - Trade Commission','2122 - Processing Commission','2123 - Exchange Commission','2125 - Fx Trade Commission','Reserve Adjustments','8330 - LDC Reserves'));
}
function addRevenueWeekly()
{
$html_content=Bin_Template::createTemplate(ROOT_FOLDER.'admin/templates/reports/accounts_journal/v2/revenue_weekly_xlsx.html',array(
'dates'=>$this->excel_dates['revenue_weekly']
),FALSE);
$this->styles=array(
'A'=>2,
'B'=>3,
'C'=>3,
'D'=>3,
'E'=>3,
'F'=>3,
'G'=>3,
'H'=>3,
'I'=>3,
'J'=>3,
'K'=>3,
'L'=>3,
'M'=>3,
'N'=>3,
'O'=>3,
'P'=>3,
'Q'=>3,
'R'=>3,
'S'=>3,
'T'=>3,
'U'=>3,
'V'=>3,
'W'=>3,
'X'=>3
);
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet23.xml',array('Date','- Fees','2121 - Trade Commission','2122 - Processing Commission','2123 - Exchange Commission','2125 - Fx Trade Commission','Sub-Total','8360 - LDC Reserves','- Reserve Adjustments','Sub-Total','Total','Average','2124 - Discount Commission','3136 - d-ETR Discount','1201 - LDC','8310 - Reserves Due','8330 - LDC Reserves','0.5863',"=COUNTIF('d-ETR'!AX:AX,\"R\")",'8320 - Reserves Outstanding',"=COUNTIF('d-ETR'!AX:AX,\"R\")",'1201 - LDC','Equity','1201 - LDC & Equity'),array('R1'=>35,'S1'=>27,'U1'=>27));
}
function checkDuplicates($key, $data)
{
$field = in_array($key, array('DETR', 'CETR')) ? 'trade_reference_id' : 'manual_transaction_id';
$keys = array();
foreach ($data as $item) {
$keys[$item[$field]][] = $item[$field];
}
$duplicateKeys = array();
foreach ($keys as $k => $v) {
if (count($v) > 1) {
$duplicateKeys[] = $k;
}
}
sort($duplicateKeys);
if ($duplicateKeys) {
mail('dev@credebt.com', 'TPR error', $key . ' - ' . implode(',', $duplicateKeys));
}
}
}