Agile Data - UNION Model usage.php

<?php
namespace smbo;
class Model_Report_Ledger2 extends UnionModel {
    use \atk4\core\AppScopeTrait;

    function init() {
        parent::init();

        $this->addInvoices();
        $this->addPayments();
        $this->addJournals();
        // union fields below 

        $this->addField('transaction_date', ['type'=>'date']);
        $this->addField('ref_no');
        $this->addField('doc_type');
        $this->addField('amount', ['type'=>'money']);
        $this->addField('account_id');
        $this->addField('account');
        $this->addField('job_id');
        $this->addField('job');
        $this->addField('description');
        $this->addField('group_type');
        $this->addField('dochead_id');
        $this->addField('replink');
        $this->addField('contractor_id');
        $this->addField('contractor');
        $this->addField('journal_spec_id');

        $this->addField('nominal_id');

        // Adding stuff that applies to all records
        $n = $this->leftJoin('nominal');
        $n->addField('nominal_name', ['actual'=>'name']);
        $n->addField('nominal_code', ['actual'=>'code']);
        $n->addField('root_code');
        $n->addField('nominal_parent_id', ['actual'=>'parent_id']);
        $this->addExpression('path', 'get_nominal_path([nominal_id])'); // e.g. Sales:Limerick:Main St
        $nt = $n->leftJoin('nominal_type');
        $nt->addField('type_ord', ['actual'=>'ord']);
        $nt->addField('is_neg', ['actual'=>'is_reverse', 'type'=>'boolean', 'enum'=>['N','Y']]);

        $this->addExpression('is_root', ['[nominal_parent_id] is NULL',
            'type'=>'boolean',
        ]);

        $this->setOrder('type_ord,root_code,nominal_id,transaction_date');
    }

    // Enables aggregation
    function getSummaryByNominal($top_level = true) {
        if($top_level) {
            //$q = $this->action('field',['root_code']);
            $this->groupBy('root_code', ['amount'=>'sum']);
        } else {
            //$q = $this->action('field',['nominal_id']);
            $this->groupBy('nominal_id', ['amount'=>'sum']);
        }
        return $q;
    }

    function addJournals(){
        $m = $this->addNestedModel($this->app->add('Journal')->addCondition('active', true)->ref('Lines') ,[
            'transaction_date'=>'[date]',
            'amount'=>'-[amount_x]',
            'replink'=>'concat("",[journal_id])',
            'group_type'=>'"journal"',
            'nominal_id'=>'[nominal_from]',
            'dochead_id'=>'[journal_id]',
            'journal_spec_id'=>'[id]',
        ]);
        $m->getElement('amount')->destroy();
        $m->addField('amount_x', ['actual'=>'amount']); // to avoid duplicate
        $m->addCondition('nominal_from','not',null);


        $m = $this->addNestedModel($this->app->add('Journal')->addCondition('active', true)->ref('Lines') ,[
            'transaction_date'=>'[date]',
            'amount'=>'[amount_x]',
            'replink'=>'concat("",[journal_id])',
            'group_type'=>'"journal"',
            'nominal_id'=>'[nominal_to]',
            'dochead_id'=>'[journal_id]',
            'journal_spec_id'=>'[id]',
        ]);
        $m->getElement('amount')->destroy();
        $m->addField('amount_x', ['actual'=>'amount']);
        $m->addCondition('nominal_to','not',null);


        $m = $this->addNestedModel($this->app->add('Journal')->addCondition('active', true)->addCondition('reverse', true)->ref('Lines') ,[
            'transaction_date'=>'[reverse_date]',
            'amount'=>'[amount_x]',
            'replink'=>'concat("",[journal_id])',
            'group_type'=>'"journal"',
            'nominal_id'=>'[nominal_from]',
            'dochead_id'=>'[journal_id]',
            'journal_spec_id'=>'[id]',
        ]);
        $m->join('journal')->addField('reverse_date');
        $m->getElement('amount')->destroy();
        $m->addField('amount_x', ['actual'=>'amount']);
        $m->addCondition('nominal_from','not',null);


        $m = $this->addNestedModel($this->app->add('Journal')->addCondition('active', true)->addCondition('reverse', true)->ref('Lines') ,[
            'transaction_date'=>'[reverse_date]',
            'amount'=>'-[amount_x]',
            'replink'=>'concat("",[journal_id])',
            'group_type'=>'"journal"',
            'nominal_id'=>'[nominal_to]',
            'dochead_id'=>'[journal_id]',
            'journal_spec_id'=>'[id]',
        ]);
        $m->join('journal')->addField('reverse_date');
        $m->getElement('amount')->destroy();
        $m->addField('amount_x', ['actual'=>'amount']);
        $m->addCondition('nominal_to','not',null);
    }

    function addPayments(){

        // Payment In - debtors

        $n_14 = $this->app->add('Nominal')->loadBy('code','14000')->id;
        $m = $this->addNestedModel($this->app->add('Payment_In'),[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->join('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->join('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_14);

        // Payment In - bank

        $m = $this->addNestedModel($this->app->add('Payment_In', ['doc_type'=>['payment', 'tillroll']]),[
            'transaction_date'=>'[date]',
            'amount'=>'[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'nominal_id'=>'[account_nominal_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->join('account')->addField('account_nominal_id');
        $m->getElement('nominal_id')->destroy();

        // Payment Out - creditors

        $n_21 = $this->app->add('Nominal')->loadBy('code','21000')->id;
        $m = $this->addNestedModel($this->app->add('Payment_Out'),[
            'transaction_date'=>'[date]',
            'amount'=>'[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->join('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->join('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_21);

        // Payment Out - bank

        $m = $this->addNestedModel($this->app->add('Payment_Out'),[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'nominal_id'=>'[account_nominal_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->join('account')->addField('account_nominal_id');
        $m->getElement('nominal_id')->destroy();

        // Misc Payment Out - nom
        $m = $this->addNestedModel($this->app->add('Payment_Misc', ['direction'=>'out']),[
            'transaction_date'=>'[date]',
            'amount'=>'[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'"misc_payment"',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'description'=>'[note]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);

        // Misc Payment Out - bink

        $m = $this->addNestedModel($this->app->add('Payment_Misc', ['direction'=>'out']),[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'"misc_payment"',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'nominal_id'=>'[account_nominal_id]',
            'description'=>'[note]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->join('account')->addField('account_nominal_id');
        $m->getElement('nominal_id')->destroy();

        // will display query for PART of the whole union query!
        //$m->debug();



        // Misc Payment In - nom
        $m = $this->addNestedModel($this->app->add('Payment_Misc', ['direction'=>'in']),[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'"misc_payment"',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'description'=>'[note]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);

        // Misc Payment in - bank

        $m = $this->addNestedModel($this->app->add('Payment_Misc', ['direction'=>'in']),[
            'transaction_date'=>'[date]',
            'amount'=>'[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'"misc_payment"',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
            'nominal_id'=>'[account_nominal_id]',
            'description'=>'[note]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->join('account')->addField('account_nominal_id');
        $m->getElement('nominal_id')->destroy();
    }

    function addInvoices(){

        // Sales invoices NET

        $m = $this->addNestedModel($this->app->add('Invoice_Sale', ['doc_type'=>['invoice','taking']])->ref('Lines') ,[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_net]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
        ]);
        $m->join('dochead')->addFields([['date','actual'=>'doc_date'],'doc_type','ref_no',['contractor_id','actual'=>'contractor_to']]);
        $m->leftJoin('contractor')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);

        // Sale invoice GROSS
        $n_14 = $this->app->add('Nominal')->loadBy('code','14000')->id;
        $m = $this->addNestedModel($this->app->add('Invoice_Sale') ,[
            'transaction_date'=>'[date]',
            'amount'=>'[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_14);

        // Sale invocie VAT
        $n_23 = $this->app->add('Nominal')->loadBy('code','23000')->id;
        $m = $this->addNestedModel($this->app->add('Invoice_Sale', ['doc_type'=>['invoice','taking']]) ,[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_vat]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->leftJoin('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_23);
        $m->debug();

        // Purchase invocies
        $m = $this->addNestedModel($this->app->add('Invoice_Purchase')->ref('Lines') ,[
            'transaction_date'=>'[date]',
            'amount'=>'[total_net]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
        ]);
        $m->join('dochead')->addFields([['date','actual'=>'doc_date'],'doc_type','ref_no',['contractor_id','actual'=>'contractor_from']]);
        $m->leftJoin('contractor')->addField('contractor', ['actual'=>'legal_name']);
        $m->join('job')->addField('job', ['actual'=>'name']);

        $n_21 = $this->app->add('Nominal')->loadBy('code','21000')->id; // TODO: wrapp nominal queries into one
        $m = $this->addNestedModel($this->app->add('Invoice_Purchase') ,[
            'transaction_date'=>'[date]',
            'amount'=>'-[total_gross]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->join('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_21);

        $n_23 = $this->app->add('Nominal')->loadBy('code','23000')->id;
        $m = $this->addNestedModel($this->app->add('Invoice_Purchase') ,[
            'transaction_date'=>'[date]',
            'amount'=>'[total_vat]',
            'replink'=>'[dochead_id]',
            'group_type'=>'[doc_type]',
            'dochead_id'=>'[id]',
            'contractor_id'=>'[contact_id]',
        ]);
        $m->leftJoin('contractor','contact_id')->addField('contractor', ['actual'=>'legal_name']);
        $m->join('job')->addField('job', ['actual'=>'name']);
        $m->getElement('nominal_id')->destroy();
        $m->addExpression('nominal_id', (string)$n_23);
    }
}

full-debug-query.sql

select 
  `_n`.`root_code`, 
  `_n`.`code` `nominal_code`, 
  `derivedTable`.`nominal_id`, 
  (
    get_nominal_path(`derivedTable`.`nominal_id`)
  ) `path`, 
  `_n_2`.`is_reverse` `is_neg`, 
  `_n_2`.`ord` `type_ord`, 
  (`_n`.`parent_id` is NULL) `is_root`, 
  `_n`.`name` `nominal_name`, 
  `derivedTable`.`transaction_date`, 
  `derivedTable`.`account_id`, 
  `derivedTable`.`account`, 
  `derivedTable`.`job_id`, 
  `derivedTable`.`job`, 
  `derivedTable`.`description`, 
  `derivedTable`.`group_type`, 
  `derivedTable`.`dochead_id`, 
  `derivedTable`.`ref_no`, 
  `derivedTable`.`replink`, 
  `derivedTable`.`contractor_id`, 
  `derivedTable`.`contractor`, 
  `derivedTable`.`journal_spec_id`, 
  `derivedTable`.`amount`, 
  `_n`.`nominal_type_id` 
from 
  (
    (
      select 
        `L`.`nominal_id`, 
        (`L_d`.`doc_date`) `transaction_date`, 
        (null) `account_id`, 
        (null) `account`, 
        `L`.`job_id`, 
        `L_j`.`name` `job`, 
        `L`.`description`, 
        (`L_d`.`doc_type`) `group_type`, 
        `L`.`dochead_id`, 
        `L_d`.`ref_no`, 
        (`L`.`dochead_id`) `replink`, 
        `L_d`.`contractor_to` `contractor_id`, 
        `L_c`.`legal_name` `contractor`, 
        (null) `journal_spec_id`, 
        (- `L`.`total_net`) `amount` 
      from 
        `docspec` `L` 
        inner join `dochead` as `L_d` on `L_d`.`id` = `L`.`dochead_id` 
        left join `contractor` as `L_c` on `L_c`.`id` = `L_d`.`contractor_to` 
        left join `job` as `L_j` on `L_j`.`id` = `L`.`job_id` 
      where 
        `L`.`deleted` = 'N' 
        and `L`.`dochead_id` in (
          select 
            `dochead`.`id` 
          from 
            `dochead` 
            inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
          where 
            `dochead`.`doc_type` in ('invoice', 'taking') 
            and `dochead`.`contractor_from` = 402732 
            and `dochead`.`deleted` = 'N' 
            and `dochead`.`system_id` = 5897
        )
    ) 
    UNION ALL 
      (
        select 
          (442474) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'invoice' 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (442479) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_vat`) `amount` 
        from 
          `dochead` 
          inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` in ('invoice', 'taking') 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          `L`.`nominal_id`, 
          (`L_d`.`doc_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `L`.`job_id`, 
          `L_j`.`name` `job`, 
          `L`.`description`, 
          (`L_d`.`doc_type`) `group_type`, 
          `L`.`dochead_id`, 
          `L_d`.`ref_no`, 
          (`L`.`dochead_id`) `replink`, 
          `L_d`.`contractor_from` `contractor_id`, 
          `L_c`.`legal_name` `contractor`, 
          (null) `journal_spec_id`, 
          (`L`.`total_net`) `amount` 
        from 
          `docspec` `L` 
          inner join `dochead` as `L_d` on `L_d`.`id` = `L`.`dochead_id` 
          left join `contractor` as `L_c` on `L_c`.`id` = `L_d`.`contractor_from` 
          inner join `job` as `L_j` on `L_j`.`id` = `L`.`job_id` 
        where 
          `L`.`deleted` = 'N' 
          and `L`.`dochead_id` in (
            select 
              `dochead`.`id` 
            from 
              `dochead` 
              inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
            where 
              `dochead`.`doc_type` = 'invoice' 
              and `dochead`.`contractor_to` = 402732 
              and `dochead`.`deleted` = 'N' 
              and `dochead`.`contractor_to` = 402732 
              and `dochead`.`system_id` = 5897
          )
      ) 
    UNION ALL 
      (
        select 
          (442477) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          inner join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'invoice' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (442479) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_vat`) `amount` 
        from 
          `dochead` 
          inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          inner join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'invoice' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (442474) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          inner join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          inner join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `_p`.`misc_payment` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (`_a`.`account_nominal_id`) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
          inner join `account` as `_a` on `_a`.`id` = `_p`.`account_id` 
        where 
          `dochead`.`doc_type` in ('payment', 'tillroll') 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `_p`.`misc_payment` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (442477) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          inner join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          inner join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `_p`.`misc_payment` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (`_a`.`account_nominal_id`) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (null) `description`, 
          (`dochead`.`doc_type`) `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
          inner join `account` as `_a` on `_a`.`id` = `_p`.`account_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `_p`.`misc_payment` = 'N' 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          `dochead`.`nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (`dochead`.`note`) `description`, 
          ("misc_payment") `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`doc_type` = 'payment' 
          and `_p`.`misc_payment` = 'Y' 
          and `dochead`.`nominal_id` is not NULL 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (`_a`.`account_nominal_id`) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (`dochead`.`note`) `description`, 
          ("misc_payment") `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_to`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
          inner join `account` as `_a` on `_a`.`id` = `_p`.`account_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_from` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`doc_type` = 'payment' 
          and `_p`.`misc_payment` = 'Y' 
          and (`_a`.`account_nominal_id`) is not NULL 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          `dochead`.`nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (`dochead`.`note`) `description`, 
          ("misc_payment") `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (- `dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`doc_type` = 'payment' 
          and `_p`.`misc_payment` = 'Y' 
          and `dochead`.`nominal_id` is not NULL 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (`_a`.`account_nominal_id`) `nominal_id`, 
          (`dochead`.`doc_date`) `transaction_date`, 
          `_p`.`account_id`, 
          (
            select 
              `name` 
            from 
              `account` `a` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `_p`.`account_id`
          ) `account`, 
          `dochead`.`job_id`, 
          `_j`.`name` `job`, 
          (`dochead`.`note`) `description`, 
          ("misc_payment") `group_type`, 
          (`dochead`.`id`) `dochead_id`, 
          `dochead`.`ref_no`, 
          (
            (`dochead`.`id`)
          ) `replink`, 
          (`dochead`.`contractor_from`) `contractor_id`, 
          `_c`.`legal_name` `contractor`, 
          `dochead`.`journal_spec_id`, 
          (`dochead`.`total_gross`) `amount` 
        from 
          `dochead` 
          inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
          left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_from` 
          left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
          inner join `account` as `_a` on `_a`.`id` = `_p`.`account_id` 
        where 
          `dochead`.`doc_type` = 'payment' 
          and `dochead`.`contractor_to` = 402732 
          and `dochead`.`deleted` = 'N' 
          and `dochead`.`doc_type` = 'payment' 
          and `_p`.`misc_payment` = 'Y' 
          and (`_a`.`account_nominal_id`) is not NULL 
          and `dochead`.`system_id` = 5897
      ) 
    UNION ALL 
      (
        select 
          (`nominal_from`) `nominal_id`, 
          (
            (
              select 
                `doc_date` 
              from 
                `journal` `L_j` 
              where 
                `deleted` = 'N' 
                and `system_id` = 5897 
                and `id` = `L`.`journal_id`
            )
          ) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `L`.`job_id`, 
          (
            select 
              `L_j`.`name` 
            from 
              `job` `L_j` 
            where 
              `L_j`.`deleted` = 'N' 
              and `L_j`.`system_id` = 5897 
              and `L_j`.`id` = `L`.`job_id`
          ) `job`, 
          `L`.`description`, 
          ("journal") `group_type`, 
          (`L`.`journal_id`) `dochead_id`, 
          (
            select 
              `ref_no` 
            from 
              `journal` `L_j` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `L`.`journal_id`
          ) `ref_no`, 
          (
            concat("", `L`.`journal_id`)
          ) `replink`, 
          (null) `contractor_id`, 
          (null) `contractor`, 
          (`L`.`id`) `journal_spec_id`, 
          (- `L`.`amount`) `amount` 
        from 
          `journal_spec` `L` 
        where 
          `L`.`deleted` = 'N' 
          and `L`.`journal_id` in (
            select 
              `id` 
            from 
              `journal` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `active` = 'Y'
          ) 
          and `L`.`nominal_from` is not NULL 
        order by 
          `L`.`id`
      ) 
    UNION ALL 
      (
        select 
          (`nominal_to`) `nominal_id`, 
          (
            (
              select 
                `doc_date` 
              from 
                `journal` `L_j` 
              where 
                `deleted` = 'N' 
                and `system_id` = 5897 
                and `id` = `L`.`journal_id`
            )
          ) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `L`.`job_id`, 
          (
            select 
              `L_j`.`name` 
            from 
              `job` `L_j` 
            where 
              `L_j`.`deleted` = 'N' 
              and `L_j`.`system_id` = 5897 
              and `L_j`.`id` = `L`.`job_id`
          ) `job`, 
          `L`.`description`, 
          ("journal") `group_type`, 
          (`L`.`journal_id`) `dochead_id`, 
          (
            select 
              `ref_no` 
            from 
              `journal` `L_j` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `L`.`journal_id`
          ) `ref_no`, 
          (
            concat("", `L`.`journal_id`)
          ) `replink`, 
          (null) `contractor_id`, 
          (null) `contractor`, 
          (`L`.`id`) `journal_spec_id`, 
          (`L`.`amount`) `amount` 
        from 
          `journal_spec` `L` 
        where 
          `L`.`deleted` = 'N' 
          and `L`.`journal_id` in (
            select 
              `id` 
            from 
              `journal` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `active` = 'Y'
          ) 
          and `L`.`nominal_to` is not NULL 
        order by 
          `L`.`id`
      ) 
    UNION ALL 
      (
        select 
          (`L`.`nominal_from`) `nominal_id`, 
          (`L_j`.`reverse_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `L`.`job_id`, 
          (
            select 
              `L_j`.`name` 
            from 
              `job` `L_j` 
            where 
              `L_j`.`deleted` = 'N' 
              and `L_j`.`system_id` = 5897 
              and `L_j`.`id` = `L`.`job_id`
          ) `job`, 
          `L`.`description`, 
          ("journal") `group_type`, 
          (`L`.`journal_id`) `dochead_id`, 
          (
            select 
              `ref_no` 
            from 
              `journal` `L_j` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `L`.`journal_id`
          ) `ref_no`, 
          (
            concat("", `L`.`journal_id`)
          ) `replink`, 
          (null) `contractor_id`, 
          (null) `contractor`, 
          (`L`.`id`) `journal_spec_id`, 
          (`L`.`amount`) `amount` 
        from 
          `journal_spec` `L` 
          inner join `journal` as `L_j` on `L_j`.`id` = `L`.`journal_id` 
        where 
          `L`.`deleted` = 'N' 
          and `L`.`journal_id` in (
            select 
              `id` 
            from 
              `journal` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `active` = 'Y' 
              and `reverse` = 'Y'
          ) 
          and `L`.`nominal_from` is not NULL 
        order by 
          `L`.`id`
      ) 
    UNION ALL 
      (
        select 
          (`L`.`nominal_to`) `nominal_id`, 
          (`L_j`.`reverse_date`) `transaction_date`, 
          (null) `account_id`, 
          (null) `account`, 
          `L`.`job_id`, 
          (
            select 
              `L_j`.`name` 
            from 
              `job` `L_j` 
            where 
              `L_j`.`deleted` = 'N' 
              and `L_j`.`system_id` = 5897 
              and `L_j`.`id` = `L`.`job_id`
          ) `job`, 
          `L`.`description`, 
          ("journal") `group_type`, 
          (`L`.`journal_id`) `dochead_id`, 
          (
            select 
              `ref_no` 
            from 
              `journal` `L_j` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `id` = `L`.`journal_id`
          ) `ref_no`, 
          (
            concat("", `L`.`journal_id`)
          ) `replink`, 
          (null) `contractor_id`, 
          (null) `contractor`, 
          (`L`.`id`) `journal_spec_id`, 
          (- `L`.`amount`) `amount` 
        from 
          `journal_spec` `L` 
          inner join `journal` as `L_j` on `L_j`.`id` = `L`.`journal_id` 
        where 
          `L`.`deleted` = 'N' 
          and `L`.`journal_id` in (
            select 
              `id` 
            from 
              `journal` 
            where 
              `deleted` = 'N' 
              and `system_id` = 5897 
              and `active` = 'Y' 
              and `reverse` = 'Y'
          ) 
          and `L`.`nominal_to` is not NULL 
        order by 
          `L`.`id`
      )
  ) `derivedTable` 
  left join `nominal` as `_n` on `_n`.`id` = `derivedTable`.`nominal_id` 
  left join `nominal_type` as `_n_2` on `_n_2`.`id` = `_n`.`nominal_type_id` 
order by 
  `_n_2`.`ord`, 
  `_n`.`root_code`, 
  `derivedTable`.`nominal_id`, 
  `derivedTable`.`transaction_date`, 
  `derivedTable`.`dochead_id`, 
  `derivedTable`.`journal_spec_id`

single-leg-debug-output.sql

select 
  (`_a`.`account_nominal_id`) `nominal_id`, 
  (`dochead`.`doc_date`) `transaction_date`, 
  `_p`.`account_id`, 
  (
    select 
      `name` 
    from 
      `account` `a` 
    where 
      `deleted` = 'N' 
      and `system_id` = 5897 
      and `id` = `_p`.`account_id`
  ) `account`, 
  `dochead`.`job_id`, 
  `_j`.`name` `job`, 
  (`dochead`.`note`) `description`, 
  ("misc_payment") `group_type`, 
  (`dochead`.`id`) `dochead_id`, 
  `dochead`.`ref_no`, 
  (
    (`dochead`.`id`)
  ) `replink`, 
  (`dochead`.`contractor_to`) `contractor_id`, 
  `_c`.`legal_name` `contractor`, 
  `dochead`.`journal_spec_id`, 
  (- `dochead`.`total_gross`) `amount` 
from 
  `dochead` 
  inner join `payment` as `_p` on `_p`.`dochead_id` = `dochead`.`id` 
  left join `contractor` as `_c` on `_c`.`id` = `dochead`.`contractor_to` 
  left join `job` as `_j` on `_j`.`id` = `dochead`.`job_id` 
  inner join `account` as `_a` on `_a`.`id` = `_p`.`account_id` 
where 
  `dochead`.`doc_type` = 'payment' 
  and `dochead`.`contractor_from` = 402732 
  and `dochead`.`deleted` = 'N' 
  and `dochead`.`doc_type` = 'payment' 
  and `_p`.`misc_payment` = 'Y' 
  and (`_a`.`account_nominal_id`) is not NULL 
  and `dochead`.`system_id` = 5897

top-level-query.sql

select 
  `_n`.`root_code`, 
  `_n`.`code` `nominal_code`, 
  `derivedTable`.`nominal_id`, 
  (
    get_nominal_path(`derivedTable`.`nominal_id`)
  ) `path`, 
  `_n_2`.`is_reverse` `is_neg`, 
  `_n_2`.`ord` `type_ord`, 
  (`_n`.`parent_id` is NULL) `is_root`, 
  `_n`.`name` `nominal_name`, 
  `derivedTable`.`transaction_date`, 
  `derivedTable`.`account_id`, 
  `derivedTable`.`account`, 
  `derivedTable`.`job_id`, 
  `derivedTable`.`job`, 
  `derivedTable`.`description`, 
  `derivedTable`.`group_type`, 
  `derivedTable`.`dochead_id`, 
  `derivedTable`.`ref_no`, 
  `derivedTable`.`replink`, 
  `derivedTable`.`contractor_id`, 
  `derivedTable`.`contractor`, 
  `derivedTable`.`journal_spec_id`, 
  `derivedTable`.`amount`, 
  `_n`.`nominal_type_id` 
from 
  `derivedTable` 
  left join `nominal` as `_n` on `_n`.`id` = `derivedTable`.`nominal_id` 
  left join `nominal_type` as `_n_2` on `_n_2`.`id` = `_n`.`nominal_type_id` 
order by 
  `_n_2`.`ord`, 
  `_n`.`root_code`, 
  `derivedTable`.`nominal_id`, 
  `derivedTable`.`transaction_date`, 
  `derivedTable`.`dochead_id`, 
  `derivedTable`.`journal_spec_id`