Model Union implementation in Agile Toolkit (draft version)

How to use on a page.php

<?php


        $tt->addTab('Debtor Report Level 1')->add('Grid')->setModel('Model_Report_Debtors',['contractor_id','amount','amount_sum'])->groupBy('contractor_id')
            ->owner->removeColumn('amount');//->addCondition('contractor_id','217716');
        $tt->addTab('Debtor Report Drilldown on 217717 ')->add('Grid')->setModel('Model_Report_Debtors',['contractor_id','amount'])->addCondition('contractor_id','217717');

ModelUnion.php

<?php
namespace romaninsh\ModelUnion;

class ModelUnion extends \SQL_Model {

    /**
     * Contain hash of arrayrs, where key is short name of the nested model
     * and the value are field mappings for specific models.
     *
     * client => [ 'name' => 'client_name'  ]
     * @var [type]
     */
    public $field_mapping = [];

    function init(){
        parent::init();
        $this->addField('id')->system(true);
    }

    /**
     * Associates field of this model to map into all models
     */
    function addField($name, $mappings = [])
    {
        $field = parent::addField($name);

        foreach($this->elements as $m)if($m instanceof \Model){

            $real_field = $name;

            // Decide what do we do with this model
            if(isset($mappings[$m->short_name])){

                if($mappings[$m->short_name]){
                    // mapped to a real field. Store it
                    $real_field = $mappings[$m->short_name];
                    $this->field_mapping[$m->short_name][$name] = $real_field;

                }elseif($mappings[$name]===false){
                    // ignore this field
                    $m->addExpression($name)->set(null);
                }
            }

            if($f=$m->hasElement($real_field)){
                if($name != $real_field){
                    unset($m[$real_field]);
                    $f->rename($name);
                    $m[$name]=$f;
                }
            }else{
                throw $this->exception('Field is not defined in model')
                ->addMoreInfo('field',$real_field)
                ->addMoreInfo('model',$m);
            }
        }

        return $field;
    }

    function addNestedModel($class, $options = null){
        $class = $this->app->normalizeClassName($class, 'Model');

        if(is_string($options))$options = ['name'=>$options];
        $options['auto_track_element']=true;


        $m=$this->add($class, $options);

        if(!$this->field_mapping[$m->short_name]){
            $this->field_mapping[$m->short_name] = [];
        }

        return $m;

    }

    function setActualFields($group = UNDEFINED){
        parent::setActualFields($group);
        $f = $this->getActualFields();

        $f = array_merge(['id'],$f);

        // calculate and map into nested models
        foreach($this->field_mapping as $m_name => $mappings){

            $m = $this->getElement($m_name);

            $ff=[];
            foreach($f as $field){
                $ff[] = $field; //$mappings[$field]?:$field;
            }
            $m->setActualFields($ff);
        }
    }

    public $_group = null;
    function groupBy($field)
    {
        $this->_group = $field;
        return $this;
    }

    function addCondition($field, $operator = UNDEFINED, $value = UNDEFINED)
    {
        foreach($this->field_mapping as $m_name => $mappings){
            $m = $this->getElement($m_name);
            $m->addCondition($mappings[$field]?:$field, $operator, $value);
        }
    }

    function initQuery(){
        if($this->table)throw $this->exception('UnionTable does not need $table property');
        $this->dsql=$this->db->dsql();
        $this->dsql->debug($this->debug);
        //$this->dsql->table($this->table,$this->table_alias);
        $this->dsql->id_field = $this->id_field;
        return $this;
    }


    function selectQuery($fields=null){

        $actual_fields=$fields?:$this->getActualFields();

        $actual_fields = array_merge(['id'],$actual_fields);

        if($this->fast && $this->_selectQuery) {
            return $this->_selectQuery();
        }
        $this->_selectQuery=$select=$this->_dsql()->del('fields');

        foreach($actual_fields as $field){
            $field=$this->hasElement($field);
            if(!$field)continue;

            $field->updateSelectQuery($select);
        }

        // next prepare sub-selects
        $expr= [];
        $cnt = 0;
        $args= [];

        foreach($this->field_mapping as $m_name => $mappings){

            // translate actual fields
            $f = [];
            foreach($actual_fields as $field){
                // TODO: simplify
                $f[] = $field; //$mappings[$field]?:$field;
            }

            $m = $this->getElement($m_name);

            foreach($m->elements as $el)if($el instanceof \Field){
                $el->system(false);
            }




            $q = $m->selectQuery($f);

            $expr[]='['.$cnt.']';
            $args[$cnt] = $q;
            $cnt++;
        }
        $args[$cnt] = 'derivedTable';
        $expr = $this->dsql()->expr('('.join(' UNION ALL ',$expr).') ['.$cnt.']', $args);
        //echo $expr->getDebugQuery();
        $select->sql_templates['select']="select [options] [field] from [my_table] [join] [where] [group] [having] [order] [limit]";

        $select->setCustom('my_table',$expr);

        if($this->_group){
            $select->group($this->_group);
        }
        return $select;

    }

}

Report Model.php

<?php
class Model_Report_Debtors extends romaninsh\ModelUnion\ModelUnion {

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


        $i = $this->addNestedModel('Invoice','invoice');
        $i->addCondition('contractor_from',$this->app->system['contractor_id']);
        $i->addExpression('contractor_id')->set('contractor_to');
        $i->addExpression('amount')->set('total_gross');
        $i->addExpression('type')->set('"invoice"');


        $p = $this->addNestedModel('Payment','payment');
        $p->addCondition('contractor_to',$this->app->system['contractor_id']);
        $p->addCondition('doc_type','payment');
        $p->addExpression('contractor_id')->set('contractor_from');//$i->getElement('contractor_from'));
        $p->addExpression('amount')->set('-total_gross');
        $p->addExpression('type')->set('"payment"');

        //$this->addField('contractor_id',['client'=>'last_sale', 'supplier'=>'last_purchase']);
        $this->addField('type');
        $this->addField('contractor_id');
        $this->addField('amount')->type('money');
        $this->addExpression('amount_sum')->set('sum(amount)');

        $this->debug();

    }

}