<?php
$folders = explode(DIRECTORY_SEPARATOR, pathinfo(__FILE__, PATHINFO_DIRNAME));
array_pop($folders);
array_pop($folders);

include(implode(DIRECTORY_SEPARATOR, $folders) . '/Bin/init.php');
require_once(ROOT_FOLDER . 'Bin/Security.php');
include(implode(DIRECTORY_SEPARATOR, $folders) . '/Bin/constants.php');
include('../../classes/Lib/PHPExcel.php');
include(ROOT_FOLDER . '../sfws/classes/Model/MRegister.php');
require_once ROOT_FOLDER . 'admin/classes/Model/MOrpa.php';
require_once(ROOT_FOLDER . "classes/Storage.php");
require_once(ROOT_FOLDER . "admin/classes/Model/trade_actions.php");

$db = Bin_Db::connect();
$trade = new Trade_actions();
$storage = Storage_Repository::etrByType(TYPE_CREDITOR);
$storagePo = Storage_Repository::poByType(TYPE_CREDITOR);
$inputFileName = 'import/lucas_import/2019-01-04_TK22463.xlsx';

$selectOrganisation = "SELECT * FROM organisation_details WHERE organisation_id = 618";
$organisation = $db->query($selectOrganisation)->getFirstRow();
$query = new Bin_Query();

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}

/**
 * @var $objPHPExcel PHPExcel
 */
//  Get worksheet dimensions
$sheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

$records = array();
//  Loop through each row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++) {
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row);
    $rowData = $rowData[0];
    if ($rowData[0]) {
        $key = $rowData[0];
        $records[$key]['value'] += str_replace(',', '', $rowData[3]) * ($rowData[8] - $rowData[6]) / $rowData[8];
        $records[$key]['ccy'] = $rowData[4];
    }
}

$invoices = array();
foreach ($records as $key => $record) {
    $faceValue = abs(round($record['value'], 2));
    $invoiceNo = 'Margin call ' . number_format($faceValue, 2);
    $invoices[] = array(
        'debtor_reference_id' => $key,
        'face_value' => $faceValue,
        'invoice_date' => date('Y-m-d'),
        'expected_date' => date('Y-m-d', strtotime('+1 day')),
        'po_no' => $invoiceNo,
        'invoice_no' => $invoiceNo,
        'type' => TYPE_CREDITOR,
    );
}

foreach ($invoices as $invoice) {
    if (!$invoice['debtor_reference_id']) {
        continue;
    }
    $apiModel = new Model_MRegister($invoice);
    // skip validation
    $apiModel->_status = true;
    $apiModel->openCEtrTrade();

    if (!$apiModel->_status) {
        echo 'Error for ' . $invoice['debtor_reference_id'] . ' : ' . current($apiModel->_errors) . PHP_EOL;
        continue;
    }

    if ($apiModel->_invoice) {
        $pdfName = $invoice['debtor_reference_id'];
        $pdfFiles = glob(ROOT_FOLDER . 'admin/cron/import/lucas_import' . DIRECTORY_SEPARATOR . $pdfName . '.pdf');
        if ($pdfName and $pdfFiles) {
            $handle = fopen('php://memory', 'w');
            $source = fopen($pdfFiles[0], 'r');
            stream_copy_to_stream($source, $handle);
            rewind($handle);
            $content = stream_get_contents($handle);
            fclose($handle);

            $fileName = $trade->generateAttachmentLocationCreditor(
                array_get($organisation, 'organisation_refrence_id'),
                'file_invoice',
                'pdf',
                array_get($organisation, 'user_id')
            );
            $fileNamePo = $trade->generateAttachmentLocationCreditor(
                array_get($organisation, 'organisation_refrence_id'),
                'file_po',
                'pdf',
                array_get($organisation, 'user_id')
            );
            $storage->set($fileName, $content);
            $storagePo->set($fileNamePo, $content);
            $updateInvoice = "UPDATE invoice_documents id
            JOIN invoice_master im ON im.invoice_id = id.invoice_id
            SET invoice_document_path = ':file_name', purchase_order_document_path = ':file_name_po'
            WHERE im.trade_reference_id = ':trade_reference_id'";
            $db->query($updateInvoice, array(
                ':file_name' => $fileName,
                ':file_name_po' => $fileNamePo,
                ':trade_reference_id' => $apiModel->_invoice,
            ));
        } else {
            echo 'No file ' . $pdfName . PHP_EOL;
        }
        // authorise invoice
        $authoriseInvoiceQuery = "UPDATE invoice_master SET max_thirty_day_reserve = :max_thirty_day_reserve,
                                  buy_max_thirty_day_reserve = :max_thirty_day_reserve,
                                  log_status = 1,
                                  logapprove_date = NOW(),
                                  authorised_at = NOW(),
                                  spooled = 1,
                                  posting_interface = 1,
                                  etr_type = 'c'
                                  WHERE trade_reference_id = ':trade_reference_id' LIMIT 1";
        $db->query($authoriseInvoiceQuery, array(
            ':trade_reference_id' => $apiModel->_invoice,
            ':max_thirty_day_reserve' => 0,
        ));
    } else {
        exit('Error');
    }

    echo 'Added invoice for creditor ' . $invoice['debtor_reference_id'] . PHP_EOL;
}