Skip to content

Instantly share code, notes, and snippets.

@romaninsh

romaninsh/Agile Data - UNION Model usage.php

Created December 2, 2016 13:19
Show Gist options
  • Save romaninsh/8cfbd6b18669489643eefbdfdbd9f9b3 to your computer and use it in GitHub Desktop.
Save romaninsh/8cfbd6b18669489643eefbdfdbd9f9b3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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);
}
}
Raw
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`
Raw
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
Raw
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`
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment