<?
class frmMOQL extends MForm
{
    public $group;
    public $user; 
    public $sector;
    public $person;
    public $student;
    public $test;
    public $options;
 
    public function __construct()
    {   
		parent::__construct('Persistence Sample - MOQL Samples');
        $this->setClose($this->manager->getActionURL('persistence','main'));
        $this->group   = $this->manager->getBusiness('persistence','group');
        $this->user    = $this->manager->getBusiness('persistence','user');
        $this->sector  = $this->manager->getBusiness('persistence','sector');
        $this->person  = $this->manager->getBusiness('persistence','person');
        $this->student = $this->manager->getBusiness('persistence','student');
        $this->test    = $this->manager->getBusiness('persistence','test');
        $this->defaultButton = false;
        $this->eventHandler();
	}

	function createFields()
    {
        $this->options = array(
             '1'=>'[List All Groups]',
             '2'=>'[List Person By Name]',
             '3'=>'[List Person By Name - Using Parameters]',
             '4'=>'[List Person - distinct]',
             '5'=>'[List Person - order]',
             '6'=>'[List Person - multiple filters]',
             '7'=>'[List User/Sector - join oneToOne]',
             '8'=>'[List User/Sector - join oneToOne - columns - filter]',
             '9'=>'[List User/Group - join manyToMany]',
             '10'=>'[List Group - join + groupby]',
             '11'=>'[List Group - join + groupby + having]',
             '12'=>'[List Group - in]',
             '13'=>'[List Group - alias]',
             '14'=>'[List Group - association join alias]',
             '15'=>'[List Sector - forced join alias]',
             '16'=>'[List Sector - auto-relation]',
             '17'=>'[List Group - subquery]',
             '18'=>'[List Group - subquery - reference]',
             '19'=>'[List Group - triple association join]',
             '20'=>'[List Group - outer join]',
             '21'=>'[List Student - inheritance]',
             '22'=>'[List Group - compound filter]',
             '23'=>'[New Group - manyToMany]',
             '24'=>'[New User - oneToOne]',
             '25'=>'[New Student - inheritance]'
        );

        $action = $this->manager->getCurrentURL();
        foreach($this->options as $i=>$option)
        {
            $array[] = new MLinkButton("btnPost$i",$option,$action);
        }
        $group1 = new MBaseGroup('lbGrp1','Using MOQL - Criterias',$array,'vertical');
        $group1->setShowLabel(false);

        $groups = array($group1);

        $divLeft  = new MDiv('divLeft',$groups);
            $divLeft->width = '35%';

        $divRight = new MContainer('divRight',array());
            $divRight->width = '60%';
            $divRight->float = 'left';

        $fields[] = array(
            $divLeft,
            $divRight
        );

        $this->setFields($fields);
	}

    public function showResult($option, $sql, $result)
    {
        $container = new MVContainer('mv',array(
            new MLabel($this->options[$option],'black',true),
            new MThemeBox('SQL', $sql),
            new MTableRaw('', $result)
        ));

        $this->divRight->setControls(array($container));
    }

    public function btnPost1_click()
    {
        // list all groups
        $criteria = $this->group->getCriteria();
        $query = $criteria->retrieveAsQuery();

        $this->showResult(1,$query->sql, $query->result);
    }

    public function btnPost2_click()
    {
        // list person by name 
        $criteria = $this->person->getCriteria();
        $criteria->addCriteria('name','LIKE',"P%");
        $query = $criteria->retrieveAsQuery();

        $this->showResult(2,$query->sql, $query->result);
    }

    public function btnPost3_click()
    {
        // using parameters
        $name = 'P';
        $criteria = $this->person->getCriteria();
        $criteria->addCriteria('name','LIKE',"?");
        $query = $criteria->retrieveAsQuery("{$name}%");

        $this->showResult(3,$query->sql, $query->result);
    }

    public function btnPost4_click()
    {
        // list person by name - distinct 
        $criteria = $this->person->getCriteria();
        $criteria->setDistinct(true);
        $criteria->addColumnAttribute('name');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(4,$query->sql, $query->result);
    }

    public function btnPost5_click()
    {
        // list person by name - order
        $criteria = $this->person->getCriteria();
        $criteria->addColumnAttribute('name');
        $criteria->addOrderAttribute('name');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(5,$query->sql, $query->result);
    }

    public function btnPost6_click()
    {
        // list person by name - using multiple filters and parameters
        $criteria = $this->person->getCriteria();
        $condition = array(
            array('','name','LIKE',"N%"),
            array('OR', array(
                array('','name','LIKE',"C%"),
                array('OR','name','LIKE',"?")
              )
            )
        );
        $criteria->addMultiCriteria($condition);
        $query = $criteria->retrieveAsQuery("A%");

        $this->showResult(6,$query->sql, $query->result);
    }

    public function btnPost7_click()
    {
        // List User/Sector - join oneToOne
        $criteria = $this->user->getCriteria();
        // 'sector' is the association
        $criteria->addCriteria('sector.nick','LIKE',"S%");
        $query = $criteria->retrieveAsQuery();

        $this->showResult(7,$query->sql, $query->result);
    }

    public function btnPost8_click()
    {
        // List User/Sector - join oneToOne - columns - filter
        $criteria = $this->user->getCriteria();
        // 'sector' is the association
        $criteria->addCriteria('sector.nick','LIKE',"S%");
        $criteria->addColumnAttribute('login');
        $criteria->addColumnAttribute('sector.nick','Sector');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(8,$query->sql, $query->result);
    }

    public function btnPost9_click()
    {
        // List User/Group - join manyToMany
        $criteria = $this->user->getCriteria();
        // 'groups' is the association
        $criteria->addCriteria('groups.group','LIKE',"G%");
        //getting all columns of user + group name
        $criteria->addColumnAttribute('*');
        $criteria->addColumnAttribute('groups.group');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(9,$query->sql, $query->result);
    }

    public function btnPost10_click()
    {
        // List Group - join + groupby
        $criteria = $this->group->getCriteria();
        $criteria->addColumnAttribute('group');
        $criteria->addColumnAttribute('count(users.idUser)');
        $criteria->addGroupAttribute('group');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(10,$query->sql, $query->result);
    }

    public function btnPost11_click()
    {
        //  List Group - join + groupby + having
        $criteria = $this->group->getCriteria();
        $criteria->addColumnAttribute('group');
        $criteria->addColumnAttribute('count(users.idUser)');
        $criteria->addGroupAttribute('group');
        $criteria->addHavingCriteria('count(users.idUser)','>','2');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(11,$query->sql, $query->result);
    }

    public function btnPost12_click()
    {
        //  List Group - in
        $criteria = $this->group->getCriteria();
        $values = array('Grupo Admin','PHP');
        $criteria->addCriteria('group','IN', $values);
        $query = $criteria->retrieveAsQuery();

        $this->showResult(12,$query->sql, $query->result);
    }

    public function btnPost13_click()
    {
        //  List Group - alias
        $criteria = $this->group->getCriteria();
        $criteria->setAlias('G');
        $criteria->addColumnAttribute('G.idGroup');
        $criteria->addColumnAttribute('G.group');
        $criteria->addCriteria('group','LIKE', "G%");
        $query = $criteria->retrieveAsQuery();

        $this->showResult(13,$query->sql, $query->result);
    }

    public function btnPost14_click()
    {
        //  List Group - association join alias
        $criteria = $this->group->getCriteria();
        $criteria->setAlias('G');
        $criteria->setAssociationAlias('users','U');
        $criteria->addColumnAttribute('G.idGroup');
        $criteria->addColumnAttribute('G.group');
        $criteria->addColumnAttribute('U.login');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(14,$query->sql, $query->result);
    }

    public function btnPost15_click()
    {
        // List Sector - forced join alias
        $criteria = $this->sector->getCriteria();
        $criteria->setAlias('S');
        $criteria->addCriteria('S.nick','=', "'ADM'");
        $criteria2 = $this->person->getCriteria();
        $criteria2->setAlias('P');
        $criteria2->addColumnAttribute('S.nick');
        $criteria2->addColumnAttribute('P.name');
        $criteria2->addJoinCriteria($criteria);
        $criteria2->addCriteria('S.id', '=', 'P.idPerson');
        $query = $criteria2->retrieveAsQuery();

        $this->showResult(15,$query->sql, $query->result);
    }

    public function btnPost16_click()
    {
        // List Sector - auto-relation
        $criteria = $this->sector->getCriteria();
        $criteria->setAutoAssociationAlias('S1','S2');
        $criteria->addColumnAttribute('S1.nick','parent');
        $criteria->addColumnAttribute('S2.nick','child');
        $criteria->addCriteria('S1.id', '=', 'S2.idParent');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(16,$query->sql, $query->result);
    }

    public function btnPost17_click()
    {
        // List Group - subquery
        $subCriteria = $this->user->getCriteria();
        $subCriteria->addCriteria('sector.nick','LIKE',"'A%'");
        $subCriteria->addColumnAttribute('idUser');
        $criteria = $this->user->getCriteria();
        $criteria->addCriteria('idUser','IN', $subCriteria);
        $query = $criteria->retrieveAsQuery();

        $this->showResult(17,$query->sql, $query->result);
    }

    public function btnPost18_click()
    {
        // List Group - subquery - reference
        $subCriteria = $this->user->getCriteria();
        $subCriteria->setReferenceAlias('S');
        $subCriteria->addColumnAttribute('count(idUser)');
        $subCriteria->addCriteria('idSector','=',"S.idSector");
        $criteria = $this->sector->getCriteria();
        $criteria->setAlias('S');
        $criteria->addCriteria($subCriteria, '>', '150');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(18,$query->sql, $query->result);
    }

    public function btnPost19_click()
    {
        // List Group - triple association join
        $criteria = $this->group->getCriteria();
        $criteria->addColumnAttribute('group');
        $criteria->addColumnAttribute('users.login');
        $criteria->addColumnAttribute('users.sector.nick');
        $criteria->addCriteria('users.sector.nick','=',"'ADM'");
        $query = $criteria->retrieveAsQuery();

        $this->showResult(19,$query->sql, $query->result);
    }

    public function btnPost20_click()
    {
        // List Group - outer join
        $criteria = $this->sector->getCriteria();
        $criteria->addColumnAttribute('nick');
        $criteria->addColumnAttribute('count(users.idUser)');
        $criteria->addCriteria('nick','LIKE',"'PRO%'");
        $criteria->addGroupAttribute('nick');
        $criteria->addHavingCriteria('count(users.idUser)','=','0');
        $criteria->setAssociationType('users','right');
        $query = $criteria->retrieveAsQuery();

        $this->showResult(20,$query->sql, $query->result);
    }

    public function btnPost21_click()
    {
        // List Student - inheritance
        $criteria = $this->student->getCriteria();
        $criteria->addColumnAttribute('number');
        $criteria->addColumnAttribute('name');
        $criteria->addCriteria('name','LIKE',"'ZEEV%'");
        $query = $criteria->retrieveAsQuery();

        $this->showResult(21,$query->sql, $query->result);
    }

    public function btnPost22_click()
    {
        // List Group - compound filter
        $criteria = $this->group->getCriteria();
        $cc = new CriteriaCondition;
        $cc->addCriteria($criteria->getCriteria('group','LIKE',"'%A%'"));
        $cc->addOrCriteria($criteria->getCriteria('group','LIKE',"'%E%'"));
        $criteria->addCriteria('group','LIKE',"'C%'");
        $criteria->addCriteria($cc);
        $query = $criteria->retrieveAsQuery();

        $this->showResult(22,$query->sql, $query->result);
    }

    public function btnPost23_click()
    {   
        // New Group - manyToMany
        $this->group->group = 'PERSISTENCE';
        $user1 = $this->manager->getBusiness('persistence','user');
        $user1->getByLogin('login1');
        $user2 = $this->manager->getBusiness('persistence','user');
        $user2->getByLogin('login2');
        $user3 = $this->manager->getBusiness('persistence','user');
        $user3->getByLogin('login3');
        $this->group->addUser($user1);
        $this->group->addUser($user2);
        $this->group->addUser($user3);
        $this->group->save();
        $text = "IdGroup: {$this->group->idGroup}<br>";
        $text .= "Group: {$this->group->group}<br>";
        $text .= "Usuarios:<br>";
        $this->group->retrieveAssociation('users');
        for($i=0;$i<count($this->group->users);$i++)
        {
            $text .= "&nbsp;&nbsp;&nbsp;User login: ". $this->group->users[$i]->login . "<br>";
        }
        $this->showResult(23, $text);
    }

    public function btnPost24_click()
    {   
        // New User - oneToOne
        $this->sector->getByNick('root');
        $this->user->person = $this->person->GetById(1);
        $this->user->idSector = $this->sector->id;
        $this->user->login = 'PERSISTENCE';
        $this->user->nick = 'PTC';
        $this->user->password = 'PERSISTENCE';
        $this->user->save();
        $text = "IdUser: {$this->user->idUser}<br>";
        $text .= "Login: {$this->user->login}<br>";
        $text .= "Password: {$this->user->password}<br>";
        $text .= "Nick: {$this->user->nick}<br>";
        $text .= "Name: " . $this->user->person->name ."<br>";
        $text .= "IdSetor: ".$this->user->idSector."<br>"; 
        $this->showResult(24, $text);
    }

    public function btnPost25_click()
    {   
        // New Student - inheritance
        $this->student->number = '199924005';
        $this->student->situation = 'MATR';
        $this->student->name = 'Persistence Silva';
        $this->student->save(); 
        $this->student->GetById('199924005');
        $text = "Number: {$this->student->number}<br>";
        $text .= "Situation: {$this->student->situation}<br>";
        $text .= "Idperson: {$this->student->idPerson}<br>";
        $text .= "Name: " . $this->student->name ."<br>";
        $this->showResult(25, $text);
    }
}
?>