File: /www/exchange0old/exchange/admin/cron/trading_platform_reports_v1.php
<?php
class Platform_reports
{
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', '3000M');
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',$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');
//$addCommissions=$addCommissionsDRP=10;
$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->addAccounts(null, $balanceReport);
$this->addStatutoryAccounts();
$this->buildExelDates();
$this->addTranslationFx();
$this->addJournals();
// * $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 ($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'));
$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 28;
case 2:{
$width='width="13.785125" customWidth="1"';
break;
}
case 31:{
$width='width="17.500000" customWidth="1"';
break;
}
case 4;
case 3:{
$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.transaction_type=7,organisation_details.etr_type,IF(manual_transactions.orpa_id,"b",organisation_details.etr_type))
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,
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Date','Ccy','Amount','Creator','Type','Status','Fx rate','EUR Amount','Bank Ccy', 'Type','ETR Type'),$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.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');
//$this->checkDuplicates('RPCEtr', $this->query->records);
$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
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');
//$this->checkDuplicates('ReturnedPayments', $this->query->records);
$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
);
$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(
'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
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');
//$this->checkDuplicates('ReserveAdjustments', $this->query->records);
$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
);
$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'),$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');
//$this->checkDuplicates('DRP', $this->query->records);
$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;
}
function addSD()
{
$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 sd_date, DATE(DATE(manual_transactions.transaction_date)) as created_date, currencies.currency_code, manual_transactions.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 , DATE(payments.transaction_date) as settled_date, IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency, IF(currencies.currency_code="EUR",1.000,IF(r_trade_rates.currency_rate IS NOT NULL,r_trade_rates.currency_rate, currencies.currency_rate)) as r_trade_rate, 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, "" as EUR_Settled_Amount, "" as EUR_RTrade_Amount,"" as Fx_Amount_Diff_Settled, "" as Fx_Amount_Diff_RTrade, "" as BLDC_to_QI, "" as Ccy_Fx_Amount_Diff_RTrade, "" as Ccy_BLDC_to_QI
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->checkDuplicates('SD', $this->query->records);
$file_name='xl/worksheets/sheet4.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'K'=>6,
'L'=>4,
'O'=>4,
'M'=>3,
'N'=>2,
'P'=>3,
'Q'=>2,
'S'=>4,
'T'=>4,
'U'=>3,
'V'=>3,
'W'=>3,
'X'=>3,
'Y'=>3,
'Z'=>3,
'AA'=>3
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','DRP','Fx rate','EUR Amount','Date','DRP Fx','DRP','Reconciled','Bank Ccy','R-Trade Fx','Settled Fx','EUR Settled','EUR R-Trade','Fx Diff (Settled)','Fx Diff (R-Trade)','BLDC to QI','Ccy Fx Diff (R-Trade)','Ccy BLDC to QI'),$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;
$transaction['EUR_Settled_Amount']='=T'.$index.' * I'.$index;
$transaction['EUR_RTrade_Amount']='=I'.$index.'*S'.$index;
$transaction['Fx_Amount_Diff_Settled']='=U'.$index.'-M'.$index;
$transaction['Fx_Amount_Diff_RTrade']='=V'.$index.'-M'.$index;
$transaction['BLDC_to_QI']='=IF(T'.$index.'<L'.$index.$this->function_sign.'-W'.$index.$this->function_sign.'0)';
$transaction['Ccy_Fx_Amount_Diff_RTrade']='=W'.$index.'/S'.$index;
$transaction['Ccy_BLDC_to_QI']='=IF(T'.$index.'<L'.$index.$this->function_sign.'-Z'.$index.$this->function_sign.'0)';
$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/v1/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(manual_transactions.status=1,"Authorized","Not Authorized") as status, IF(r_trade.manual_transaction_id IS NULL,"","X") as reconcile_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, "" 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, DATE(payments.transaction_date) as setteled_date, IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency, IF(currencies.currency_code="EUR",1.000,IF(r_trade_rates.currency_rate IS NOT NULL,r_trade_rates.currency_rate, currencies.currency_rate)) as r_trade_rate, 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, ""as EUR_Settled_Amount, "" as EUR_RTrade_Amount, "" as Fx_Amount_Diff_Settled, "" as BLDC_to_QI_DETR, "" as Fx_Amount_Diff_RTrade, "" as Ccy_Fx_Amount_Diff, "" as Ccy_BLDC_to_QI_d_ETR
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');
//$this->checkDuplicates('CN', $this->query->records);
$file_name='xl/worksheets/sheet3.xml';
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'F'=>2,
'G'=>2,
'I'=>3,
'K'=>6,
'L'=>6,
'N'=>3,
'M'=>4,
'O'=>2,
'P'=>4,
'Q'=>3,
'R'=>2,
'T'=>4,
'U'=>4,
'V'=>3,
'W'=>3,
'X'=>3,
'Y'=>3,
'Z'=>3,
'AA'=>3,
'AB'=>3
);
$this->writeSheetHeader($file_name,array('Originator ID','Originator Name','Debtor ID','Debtor Name','Transaction ID','Date','Entered','Ccy','Amount','Status','Reconciled','DRP','Fx rate','EUR Amount','Date','DRP Fx','DRP','Reconciled','Bank Ccy','R-Trade Fx','Settled Fx','EUR Settled','EUR R-Trade','Fx Diff (Settled)','BLDC to QI (d-ETR)','Fx Diff (R-Trade)','Ccy Fx Diff','Ccy BLDC to QI (d-ETR)'),$this->query->totrows);
$result=array();
$index=2;
foreach($this->query->records as $transaction)
{
$transaction['EUR_amount']='=M'.$index.' * I'.$index;
$transaction['drp_amount']='=P'.$index.' * I'.$index;
$transaction['EUR_Settled_Amount']='=U'.$index.' * I'.$index;
$transaction['EUR_RTrade_Amount']='=I'.$index.'*T'.$index;
$transaction['Fx_Amount_Diff_Settled']='=V'.$index.'-N'.$index;
$transaction['BLDC_to_QI_DETR']='=IF(U'.$index.'<M'.$index.',-X'.$index.',0)';
$transaction['Fx_Amount_Diff_RTrade']='=W'.$index.'-N'.$index;
$transaction['Ccy_Fx_Amount_Diff']='=X'.$index.'/T'.$index;
$transaction['Ccy_BLDC_to_QI_d_ETR']='=IF(U'.$index.'<M'.$index.',-AA'.$index.',0)';
$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/v1/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
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');
//$this->checkDuplicates('InboundPaymentsDETR', $this->query->records);
// * 2917,1567,654,939,3505,6098,5546,1632,7538,1926,1814,824,2392,1479,1645,2051,5695,2683,2280,5721,2847,10351,15174,1132,2837,2926,2333,3623,1713,3481
// * AND manual_transactions.manual_transaction_id NOT IN(2917,939,3505,6098,5546,1632,7538,1926,1814,1479,1645,2051,5695,2683,2280,5721,2847,10351,15174,1132,2837,2926,2333,3623,1713,3481)
$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
);
$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'),$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/v1/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, debtors_detail.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');
//$this->checkDuplicates('InboundPaymentsCETR', $this->query->records);
$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 adso,0 as rdso, currencies.currency_code, invoice_master.face_value as amount, invoice_master.face_value*(100-invoice_master.payment_discount)/100 as paid_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(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency,1.2/100 as target_split,IFNULL(invoice_closed_attributes.processing_fee,0) as processing_fee,"" as Ccy_Trade_Commission, "" as commission_real , IFNULL(invoice_master.c_etr_commission,0) as c_etr_commission,
IF(main_trans.transaction_type = '.RECONCILE_CREDITOR_ACCOUNT.' ,"R","") as reconcile_status,
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,
"" as purchase_price, "" as EUR_Purchase_Price,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 Ccy_Bank_Exchange,"" as revenue_share,"" as accruals,"" as EUR_Accruals,"" as Exchange_Revenue,"" as EUR_Exchange_Revenue ,"" as Settled_CETR, "" as Deferred_Commission, 45 as ADSO_45, "" as Commission_final, "" as DC_value,"" as Ccy_FV_Due, "" as Ccy_DC_Due, invoice_master.etr_type
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');
//$this->checkDuplicates('CETR', $this->query->records);
$file_name='xl/worksheets/sheet10.xml';
$headers=array('Originator ID','Originator Name','Creditor ID','Creditor Name','M. Credebtor ID','M. Credebtor','Issued','Trade ID','Ref.','Entered','Paid','EDSO','ADSO','RDSO','Ccy','Face Value','Paid','Purchased','Sell Rate','Fx rate','EUR Face Value','EUR Purchased','Bank Ccy','Target Split','Trade Commission','Ccy Trade Commission','Processing Commission Fx','Processing Commission','Status','Settled','Ccy FV Due','Purchase Price','EUR Purchase Price','Commission Fee','EUR Commission Fee',
"4. Traded c-ETR 2104 - Traded c-ETR",
'Bank Exchange','EUR Bank Exchange','Ccy Bank Exchange','Revenue Share','Accruals','EUR Accruals','Exchange Revenue','EUR Exchange Revenue',
"5. Settled c-ETR 1903 - c-ETR",
"5. Settled c-ETR 3150 - Deferred Commission",
'ADSO','Commission','Deferred','Ccy Outstanding','Ccy Deferred','ETR Type');
$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' => 3,
'R' => 7,
'S' => 7,
'T' => 4,
'U' => 3,
'V' => 3,
'X' => 7,
'Y' => 3,
'Z' => 3,
'AA' => 3,
'AB' => 3,
'AC' => 6,
'AD' => 2,
'AE' => 4,
'AF' => 3,
'AG' => 3,
'AH' => 3,
'AI' => 9,
'AJ' => 3,
'AK' => 3,
'AL' => 3,
'AM' => 3,
'AN' => 3,
'AO' => 3,
'AP' => 3,
'AQ' => 3,
'AR' => 9,
'AS' => 3,
'AV' => 9,
'AW' => 3,
'AX' => 3,
'AY' => 3,
'AZ' => 6,
);
$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.'N'.$index.')';
$invoice['rdso']='=IF(MIN(AD'.$index.$this->function_sign.'System!O6)>K'.$index.$this->function_sign.'DATEDIF(K'.$index.$this->function_sign.'IF(AC'.$index.'="R"'.$this->function_sign.'MIN(AD'.$index.$this->function_sign.'System!O6)'.$this->function_sign.'System!O6)'.$this->function_sign.'"D")'.$this->function_sign.'-1*DATEDIF(IF(AC'.$index.'="R"'.$this->function_sign.'MIN(AD'.$index.$this->function_sign.'System!O6)'.$this->function_sign.'System!O6)'.$this->function_sign.'K'.$index.$this->function_sign.'"D"))';
$invoice['EUR_face_value']='=T'.$index.' * P'.$index;
$invoice['EUR_amount_paid']='=T'.$index.' * Q'.$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('.$invoice['commission_fee'].'>0,'.$invoice['commission_fee'].',IF(M'.$index.'<=L'.$index.$this->function_sign.'((P'.$index.'*S'.$index.')/30)*L'.$index.$this->function_sign.'((P'.$index.'*S'.$index.')/30)*M'.$index.'))';
$invoice['EUR_commission_fee']='=AH'.$index.' * T'.$index;
$invoice['Traded_CETR']='=P'.$index.'*T'.$index;
$invoice['Bank_Exchange']='=AF'.$index.'-AA'.$index;
$invoice['EUR_Bank_Exchange']='=AG'.$index.'-AA'.$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['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.' * AE'.$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(AD'.$index.'),U'.$index.',IF(AD'.$index.'<=System!O6,"-",U'.$index.'))';
$invoice['Ccy_DC_Due']='=IF(ISBLANK(AD'.$index.'),AW'.$index.',IF(AD'.$index.'<=System!O6,"-",AD'.$index.'))';
$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;
}
function addCommissions($commision_type)
{
$is_drp=($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','M. Credebtor ID','M. Credebtor','Issued','Trade ID','Ref','Entered','Traded','EDSO','ADSO','RDSO','Ccy','Bank Ccy','Face Value','PP','Sell Rate','Traded Fx','Payment Fx','R-Trade Fx','DRP Fx','EUR Face Value','EUR Settled Face Value','Fx diff. (Face vs Settled)','Ccy Fx diff.','Purchased','EUR Purchased','Investor Purchase','EUR Investor Purchase','EUR Trade Com.','Ccy Trade Com.','Ccy Trade DRP','LDC','EUR LDC','Day1 Reserve','EUR Day 1 Reserve','EUR Day 1 Reserve (Settled Fx)','EUR Day 1 Reserve (R-Trade Fx)','Deferred','EUR Deferred','EUR Deferred (Settled Fx)','EUR Deferred (R-Trade Fx)','ADSO Reserve','EUR ADSO Reserve','Reserve O/s','EUR Reserve O/s','EUR Reserve O/s DRP Fx','LDC Gain','EUR LDC Gain','Status','Reserve Value','DRP Status','EUR Face Value','EUR Reserve Value','Settled','DRP Date','DRP EUR value','Fx BLDC to QI','CCy BLDC to QI','AU without Trade Fee','AL WITHOUT TRADE FEE','Ccy FV Due','Ccy Deferred Due','Ccy Reserve Due','R-Trade Created','o-ETR value','ADSO DC','EUR ADSO DC','Disable 180 days','=-SUMIFS(AV:AV,AZ:AZ,"",AV:AV,"<0")');
$this->styles=array(
'A'=>-1,
'B'=>-2,
'C'=>5,
'D'=>-2,
'E'=>5,
'F'=>-2,
'G' => 2,
'H' => 5,
'J' => 2,
'K' => 2,
'Q' => 3,
'R' => 11,
'S' => 11,
'T' => 4,
'U' => 4,
'V' => 4,
'W' => 4,
'AZ' => 0,
'BB' => 0,
'BE' => 2,
'BF' => 2,
'BA' => 3,
'BC' => 3,
'BD' => 3,
'BG' => 3,
'BH' => 3,
'BI' => 3,
'BJ' => 3,
'BK' => 3,
'BL' => 3,
'BM' => 3,
'BN' => 3,
'BO' => 2,
'BP' => 3,
'BQ' => 3,
'BR' => 3,
'BS' => 2,
);
for($key_index=1;$key_index<=34;$key_index++)
{
$this->styles[$this->indexToColumn($key_index+20)]=3;
}
$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 adso,0 as rdso ,currencies.currency_code,IFNULL(organisation_currencies.currency_code,oc_eur.currency_code) as bank_currency, 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 ,
IF( currencies.currency_code="EUR",1.000, traded_rates.currency_rate) as traded_rate ,
IF( currencies.currency_code="EUR",1.000, IF( invoice_master.reserve IS NULL ,currencies.currency_rate, reserve_rates.currency_rate)) as reserve_rate,
IF( currencies.currency_code="EUR",1.000, IF( invoice_master.reserve IS NULL ,currencies.currency_rate, r_trade_rates.currency_rate)) as r_trade_rate,
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,
0 as EUR_face_value,0 as EUR_Settled_Face_Value, 0 as FX_value_diff_Face_vs_Settled, 0 as Ccy_FX_value_diff,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, 0 as Ccy_Trade_Com, 0 as Ccy_Trade_Com_DRP,0 as LDC_value, 0 as EUR_LDC,0 as Day1_Reserve, 0 as EUR_Day_1_Reserve, 0 as EUR_Day_1_Reserve_Settled_FX,0 as EUR_Day_1_Reserve_RTrade_FX,0 as DC_value,0 as EUR_DC, 0 as EUR_DC_Settled_Fx,0 as EUR_DC_RTrade_Fx,0 as ADSO_Reserve, 0 as EUR_ADSO_Reserve, 0 as Reserve_Outs, 0 as EUR_Reserve_Outs,0 as EUR_Reserve_Outs_DRP_Fx,0 as LDC_Gain,0 as EUR_LDC_Gain,IF(main_trans.transaction_type = ' . RECONCILE_DEBTOR_ACCOUNT . ' ,"R","") as reconcile_status,0 as reserve, IF(main_trans.transaction_type = ' . RECONCILE_DEBTOR_ACCOUNT . ',IF(invoice_master.reserve_on=0,"DUE","PAID"),"") as reserve_status, 0 as EUR_Face_Value2,0 as EUR_Reserve_Value,IF(main_trans.transaction_type = ' . RECONCILE_DEBTOR_ACCOUNT . ', DATE(payment_trans.transaction_date) ,"") as reserve_date,DATE(drp.transaction_date) as drp_date, 0 as EUR_Reserve_DRP_value,0 as FX_BLDC_to_QI,0 as CCy_BLDC_to_QI, 0 as AU_without_Trade_Fee,0 as AL_WITHOUT_TRADE_FEE, 0 as Ccy_FV_Due, 0 as Ccy_DC_Due, 0 as Ccy_Reserve_Due, DATE(DATE(main_trans.created_at)) as rtrade_created_date, 0 as oetr_value, "" as adso_dc, "" as eur_adso_dc,IF(invoice_master.override_180_day_rule, "X", "") as override_180_day_rule
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 adso,0 as rdso ,"EUR" currency_code,"EUR" as bank_currency,0 as amount, 0 as purchase_discount, 0 as sell_rate ,
1.000 as traded_rate ,
1.000 as reserve_rate,
1.000 as r_trade_rate,
1.000 as drp_rate,
0 as EUR_face_value,0 as EUR_Settled_Face_Value, 0 as FX_value_diff_Face_vs_Settled, 0 as Ccy_FX_value_diff,0 as Purchased_amount, 0 as EUR_purchased_amount, 0 as Investor_Purchase, 0 as EUR_Investor_Purchase,0 as processing_fee, 0 as Ccy_Trade_Com, 0 as Ccy_Trade_Com_DRP,0 as LDC_value, 0 as EUR_LDC,0 as Day1_Reserve, 0 as EUR_Day_1_Reserve, 0 as EUR_Day_1_Reserve_Settled_FX,0 as EUR_Day_1_Reserve_RTrade_FX,0 as DC_value,0 as EUR_DC, 0 as EUR_DC_Settled_Fx,0 as EUR_DC_RTrade_Fx,0 as ADSO_Reserve, 0 as EUR_ADSO_Reserve, 0 as Reserve_Outs, 0 as EUR_Reserve_Outs,0 as EUR_Reserve_Outs_DRP_Fx,0 as LDC_Gain,0 as EUR_LDC_Gain,"" as reconcile_status,0 as reserve, "" as reserve_status, 0 as EUR_Face_Value2,0 as EUR_Reserve_Value,"" as reserve_date,"" as drp_date, 0 as EUR_Reserve_DRP_value,0 as FX_BLDC_to_QI,0 as CCy_BLDC_to_QI, 0 as AU_without_Trade_Fee,0 as AL_WITHOUT_TRADE_FEE, 0 as Ccy_FV_Due, 0 as Ccy_DC_Due, 0 as Ccy_Reserve_Due, "" as rtrade_created_date, predicted_amount as oetr_value, "" as adso_dc, "" as eur_adso_dc, "" as override_180_day_rule
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'] = '=Q' . $index . '*T' . $index;
$invoice['EUR_Face_Value2'] = '=Q' . $index . '*T' . $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'] = '=Q' . $index . '-Q' . $index . '*S' . $index . '*12*180/360';
$invoice['EUR_Investor_Purchase'] = '=AD' . $index . '*T' . $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 . 'N' . $index . ')';
$invoice['rdso'] = '=IF(MIN(BE' . $index . $this->function_sign . 'System!O6)>G' . $index . $this->function_sign . 'DATEDIF(G' . $index . $this->function_sign . 'IF(AZ' . $index . '="R"' . $this->function_sign . 'MIN(BE' . $index . $this->function_sign . 'System!O6)' . $this->function_sign . 'System!O6)' . $this->function_sign . '"D")' . $this->function_sign . '-1*DATEDIF(IF(AZ' . $index . '="R"' . $this->function_sign . 'MIN(BE' . $index . $this->function_sign . 'System!O6)' . $this->function_sign . 'System!O6)' . $this->function_sign . 'G' . $index . $this->function_sign . '"D"))';
$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;
}
$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/v1/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( CN!I:I, CN!F:F,">="&System!O5, CN!F:F,"<="&System!O6, CN!J:J,"Authorized",CN!H:H,"'.$item[0].'",CN!S:S,"'.$item[1].'",CN!A:A,A'.$index.') + SUMIFS( SD!I:I, SD!F:F,">="&System!O5, SD!F:F,"<="&System!O6, SD!J:J,"Authorized",SD!H:H,"'.$item[0].'",SD!R:R,"'.$item[1].'",SD!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( CN!I:I, CN!R:R,">="&System!O5, CN!R:R,"<="&System!O6, CN!J:J,"Authorized", CN!K:K,"X", CN!H:H,"'.$item[0].'",CN!S:S,"'.$item[1].'",CN!A:A,A'.$index.') + SUMIFS( SD!I:I, SD!Q:Q,">="&System!O5, SD!Q:Q,"<="&System!O6, SD!J:J,"Authorized",SD!H:H,"'.$item[0].'",SD!R:R,"'.$item[1].'",SD!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( CN!I:I,CN!F:F,"<="&System!O6, CN!J:J,"Authorized",CN!H:H,"'.$item[0].'",CN!S:S,"'.$item[1].'",CN!A:A,A'.$index.') + SUMIFS( SD!I:I, SD!F:F,"<="&System!O6, SD!J:J,"Authorized",SD!H:H,"'.$item[0].'",SD!R:R,"'.$item[1].'",SD!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( CN!I:I, CN!R:R,"<="&System!O6, CN!J:J,"Authorized", CN!K:K,"X", CN!H:H,"'.$item[0].'",CN!S:S,"'.$item[1].'",CN!A:A,A'.$index.') + SUMIFS( SD!I:I, SD!Q:Q,"<="&System!O6, SD!J:J,"Authorized",SD!H:H,"'.$item[0].'",SD!R:R,"'.$item[1].'",SD!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',"='CN'!I".$CNPos),
array('SD SUM',"='SD'!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."-CN!I".$CNPos."-SD!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(CN!I2:I".($CNPos-1).",CN!J2:J".($CNPos-1).",\"Authorized\",CN!L2:L".($CNPos-1).",\"\")-SUMIFS(SD!I2:I".($SDPos-1).",SD!J2:J".($SDPos-1).",\"Authorized\",SD!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."-CN!Q".$CNPos."-SD!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(CN!Q2:Q".($CNPos-1).",CN!J2:J".($CNPos-1).",\"Authorized\",CN!L2:L".($CNPos-1).",\"\")-SUMIFS(SD!P2:P".($SDPos-1).",SD!J2:J".($SDPos-1).",\"Authorized\",SD!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',"='CN'!N".$CNPos),
array('SD SUM',"='SD'!M".$SDPos),
array('d-ETR RP',"='Returned Payments'!L".$ReturnedPos),
array('DRP SUM',"='Settled Reserves'!L".$DPRPos),
array('c-ETR SUM',"='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/v1/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)
{
$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 ($file_name === 'xl/worksheets/sheet19.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);
}
else
{
$row[$td_index]=($td->nodeValue);
}
}
$not_shared = $file_name === '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();
$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/v1/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=>"='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)." + 'c-ETR'!Y".($addCETR+9)." + 'c-ETR'!AB".($addCETR+8)." + 'c-ETR'!AI".($addCETR+9)." + '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)." - CN!X".($addCN+21)." - CN!X".($addCN+23)." - SD!W".($addSD+17)." - SD!W".($addSD+19)." + CD<position>",
3120=>"='d-ETR'!AL".($DETRPos+4)." - CD<position>",
3132=>"='d-ETR'!AJ".($DETRPos+4)." - CD<position>",
3134=>"='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)." + 'c-ETR'!AR".($addCETR+7)." + '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)." - CN!N".($addCN+4)." - SD!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)." - (CN!N".($addCN+27)." + SD!M".($addSD+17).") - 'd-ETR'!AF".($DETRPos+17)." + ('d-ETR'!AV".($DETRPos+20)." + 'd-ETR'!Z".($DETRPos+14)." - (CN!X".($addCN+15)." + SD!W".($addSD+12).") - (CN!N".($addCN+31)." + SD!M".($addSD+21).") - 'd-ETR'!AF".($DETRPos+18).") + ('d-ETR'!AV".($DETRPos+21)." + 'd-ETR'!Z".($DETRPos+15)." - (CN!X".($addCN+16)." + SD!W".($addSD+13).") - (CN!N".($addCN+32)." + SD!M".($addSD+22).") - 'd-ETR'!AF".($DETRPos+19).") + ('d-ETR'!AV".($DETRPos+22)." + 'd-ETR'!Z".($DETRPos+16)." - (CN!X".($addCN+17)." + SD!W".($addSD+14).") - (CN!N".($addCN+33)." + SD!M".($addSD+23).") - 'd-ETR'!AF".($DETRPos+20).") + ('d-ETR'!AV".($DETRPos+23)." + 'd-ETR'!Z".($DETRPos+17)." - (CN!X".($addCN+18)." + SD!W".($addSD+15).") - (CN!N".($addCN+34)." + SD!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=>"='c-ETR'!AF".($addCETR+6)." + '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=>"='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)." - 'c-ETR'!AS".($addCETR+7)." - 'c-ETR'!AI".($addCETR+9)." - 'c-ETR'!Y".($addCETR+9)." - 'c-ETR'!AB".($addCETR+8)." - '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)." - CN!X".($addCN+8)." - SD!W".($addSD+5)." - CD<position>",
1903=>"='c-ETR'!U".($addCETR+7)." - 'Payments (c-ETR)'!N".($addInboundPaymentsCETR+5)." - 'Payments (OCPA)'!J".($addOPATransactions+6)." + 'c-ETR'!AI".($addCETR+9)." + 'c-ETR'!Y".($addCETR+9)." + 'c-ETR'!AB".($addCETR+8)." - CD<position>",
8130=>"=('d-ETR'!AP".($DETRPos+4)." + 'c-ETR'!AR".($addCETR+7)." + 'c-ETR'!AP".($addCETR+7).") - ('d-ETR'!AR".($DETRPos+4)." + 'c-ETR'!AR".($addCETR+9)." + 'c-ETR'!AP".($addCETR+7).") + CD<position>",
8150=>"='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)." - CN!X".($addCN+10)." - CN!X".($addCN+11)." - CN!X".($addCN+12)." - CN!X".($addCN+13)." - SD!W".($addSD+7)." - SD!W".($addSD+8)." - SD!W".($addSD+9)." - SD!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)." - (CN!L".($addCN+4)." + CN!L".($addCN+8)." + CN!L".($addCN+9)." + CN!L".($addCN+10)." + CN!L".($addCN+11)." + SD!K".($addSD+6)." + SD!K".($addSD+10)." + SD!K".($addSD+11)." + SD!K".($addSD+12)." + SD!K".($addSD+13).") + 'd-ETR'!Z".($DETRPos+14)." + 'd-ETR'!Z".($DETRPos+15)." + 'd-ETR'!Z".($DETRPos+16)." + 'd-ETR'!Z".($DETRPos+17)." - CN!X".($addCN+15)." - CN!X".($addCN+16)." - CN!X".($addCN+17)." - CN!X".($addCN+18)." - SD!W".($addSD+12)." - SD!W".($addSD+13)." - SD!W".($addSD+14)." - SD!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)." - 'c-ETR'!AS".($addCETR+7)." - 'c-ETR'!AI".($addCETR+9)." - 'c-ETR'!Y".($addCETR+9)." - 'c-ETR'!AB".($addCETR+8)." - '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)
{
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/v1/management_accounts_xslx.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/v1/statutory_accounts_xslx.html',array(),FALSE);
$this->styles=array(
'H'=>-5,
'I'=>28,
'K'=>-5,
'L'=>28
);
$additional_styles=array();
foreach(array('H2','I3','K2','L3') as $cell)
{
$additional_styles[$cell]=13;
}
foreach(array('I7','I12','I16','I22','I28','L9','L16','L18','L22','L25','L28') as $cell)
{
$additional_styles[$cell]=29;
}
foreach(array('I23','I29','L29') as $cell)
{
$additional_styles[$cell]=30;
}
$this->HTMLtoOOXML($html_content,'xl/worksheets/sheet20.xml',array('','','','','','','','','','','',''),$additional_styles);
}
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/v1/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/v1/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/v1/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/v1/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));
}
}
}
?>