<?
class frmDatabase extends MForm
{
    public $box;

    public function __construct()
    {   
		global $page;
		parent::__construct('Database Sample');
        $this->defaultButton = false;
        $this->eventHandler();
	}

	function CreateFields()
	{   
        $action = $this->manager->getCurrentURL();
        $array_MLinkButton0 = array(
             new MLinkButton('btnSimple','[Simple query]',$action),
             new MLinkButton('btnWhere','[Where]',$action),
             new MLinkButton('btnWhereMult','[Multiple Where]',$action),
             new MLinkButton('btnParam','[Parameter]',$action),
             new MLinkButton('btnParam2','[Parameters]',$action),
             new MLinkButton('btnUpdate','[Update]',$action),
        );
        $array_MLinkButton1 = array(
             new MLinkButton('btnOrder','[Order By clause]',$action),
             new MLinkButton('btnJoin','[Inner Join]',$action),
             new MLinkButton('btnHaving','[Having clause]',$action),
        );
        $array_MLinkButton2 = array(
             new MLinkButton('btnSubquery','[Subquery]',$action),
             new MLinkButton('btnGetRow','[GetRowValues]',$action),
             new MLinkButton('btnNav','[Navigation]',$action),
             new MLinkButton('btnRaw','[RawData]',$action)
        );
        $array_MLinkButton3 = array(
             new MLinkButton('btnCount','[Count]',$action),
             new MLinkButton('btnRange','[ListRange]',$action),
             new MLinkButton('btnSqlRange','[SQLRange]',$action),
             new MLinkButton('btnPag','[Pagination]',$action),
             new MLinkButton('btnSequence','[Sequence]',$action),
             new MLinkButton('btnTrans','[Transactions]',$action)
        );
        $array_MLinkButton4 = array(
             new MLinkButton('btnOrderRs','[Order Result]',$action),
             new MLinkButton('btnFilterRs','[Filter Result]',$action),
             new MLinkButton('btnFilterReRs','[Filter RegEx Result]',$action),
             new MLinkButton('btnBind','[Bind]',$action),
        );
        $array_MLinkButton5 = array(
             new MLinkButton('btnExceptionQuery','[EDatabaseException - Query]',$action),
             new MLinkButton('btnExceptionExec','[EDatabaseException - Exec]',$action),
             new MLinkButton('btnDirectSQL','[DirectSQL]',$action),
             new MLinkButton('btnSQLCommand','[SQLCommand]',$action),
             new MLinkButton('btnOpenClose','[Open/Close DB]',$action),
        );
	   $fields = array(
           new MTextLabel('label1','miolo_transaction','Tabelas'),
           new MTextLabel('label2','seq_miolo_transaction','Sequence'),
           new MLinkButtonGroup('lbGrp0','',$array_MLinkButton0,'horizontal'),
           new MLinkButtonGroup('lbGrp1','',$array_MLinkButton1,'horizontal'),
           new MLinkButtonGroup('lbGrp2','',$array_MLinkButton2,'horizontal'),
           new MLinkButtonGroup('lbGrp3','',$array_MLinkButton3,'horizontal'),
           new MLinkButtonGroup('lbGrp4','',$array_MLinkButton4,'horizontal'),
           new MLinkButtonGroup('lbGrp5','',$array_MLinkButton5,'horizontal')
       );
       $this->setFields($fields);
	}

    public function btnSimple_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $text = '#records: ' . $n . '<br><br>';
       $text .= '#rec - transação' . '<br>';
       $result = $query->result;
       for ($i=0; $i < $n; $i++)
       {
          $text .= "#$i - " . $result[$i][1] . '<br>';
       }
       $this->addField(new MThemeBox('Results - Simple query', $text));
    }

    public function btnWhere_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction','idtransaction=2');
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $text = 'idtransaction = 2   -  #records: ' . $n . '<br><br>';
       $text .= '#rec - transação' . '<br>';
       $result = $query->result;
       for ($i=0; $i < $n; $i++)
       {
          $text .= "#$i - " . $result[$i][1] . '<br>';
       }
       $this->addField(new MThemeBox('Results - Where clause', $text));
    }

    public function btnWhereMult_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $sql->setWhere('idtransaction < 100');
       $sql->setWhereAnd("m_transaction LIKE 'A%'"); 
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $text = '(idtransaction < 100) and (m_transaction LIKE A%)  -  #records: ' . $n . '<br><br>';
       $text .= '#rec - transação' . '<br>';
       $result = $query->result;
       for ($i=0; $i < $n; $i++)
       {
          $text .= "#$i - " . $result[$i][1] . '<br>';
       }
       $this->addField(new MThemeBox('Results - Multiple Where', $text));
    }

    public function btnParam_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction','idtransaction=?');
       $sql->setParameters(3);
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $text = 'idtransaction = 4   -  #records: ' . $n . '<br><br>';
       $row = $query->getRowValues();
       foreach($row as $f)
       {
          $text .= $f . '<br>';
       }
       $this->addField(new MThemeBox('Results - Parameter', $text));
    }

    public function btnParam2_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction',"(idtransaction < ?) AND (m_transaction LIKE ?)");
       $sql->setParameters(array(100,'A%'));
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $text = '(idtransaction < 100) and (m_transaction LIKE A%)  -  #records: ' . $n . '<br><br>';
       for ($i=0; $i < $n; $i++)
       {
          $text .= "#$i - " . $query->result[$i][1] . '<br>';
       }
       $this->addField(new MThemeBox('Results - Parameters', $text));
    }

    public function btnUpdate_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('nickname,confirm_hash','miolo_user',"(iduser=1)");
       $sql->setParameters(array('tron',':NULL'));
       try
       {
           $db->execute($sql->update());
           $text = 'Update ok! : ' . $sql->command; 
       }
       catch (EDatabaseException $e)
       { 
          $text = $e->getMessage();
       }
       $this->addField(new MThemeBox('Results - Update', $text));
    }

    public function btnOrder_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction','','m_transaction');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('select * from miolo_transaction', $query->result);
       $this->addField(new MThemeBox('Results - RawData', $table));
    }

    public function btnJoin_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('t.m_transaction, g.m_group, a.rights','','','t.m_transaction, g.m_group');
       $sql->setJoin('miolo_transaction t', 'miolo_access a','(t.idtransaction=a.idtransaction)');
       $sql->setJoin('miolo_access a','miolo_group g','(g.idgroup=a.idgroup)');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('Join miolo_group * miolo_rights * miolo_transaction', $query->result);
       $this->addField(new MThemeBox('Results - Join', $table));
    }

    public function btnHaving_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('g.m_group, count(*)','','','t.m_transaction, g.m_group','g.m_group', 'count(*) > 2');
       $sql->setJoin('miolo_transaction t', 'miolo_access a','(t.idtransaction=a.idtransaction)');
       $sql->setJoin('miolo_access a','miolo_group g','(g.idgroup=a.idgroup)');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('Clausula having', $query->result);
       $this->addField(new MThemeBox('Results - Having', $table));
    }

    public function btnSubquery_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('r.idgrupo','miolo_access r');
       $sql->setWhere('r.idtransaction IN ?');
       $sqlx = new MSQL('t.idtransaction','miolo_transaction t',"(t.m_transaction LIKE 'A%')");
       $sql->setParameters(':(' . $sqlx->select() . ')');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('Subquery: ' . $query->sql, $query->result);
       $this->addField(new MThemeBox('Results - Subquery', $table));
    }

    public function btnGetRow_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $n = $query->getRowCount();
       $query->moveTo(4);
       $text = 'Row = 4   -  #records: ' . $n . '<br><br>';
       $row = $query->getRowValues();
       foreach($row as $f)
       {
          $text .= $f . '<br>';
       }
       $this->addField(new MThemeBox('Results - GetRowValues', $text));
    }

    public function btnNav_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $text = 'Move to First: <br>';
       $query->moveFirst();
       $row = $query->getRowValues();
       foreach($row as $f)
       {
          $text .= $f . ' - ';
       }
       $text .= '<br><br>';
       $text .= 'Move to Last: <br>';
       $query->moveLast();
       $row = $query->getRowValues();
       foreach($row as $f)
       {
          $text .= $f . ' - ';
       }
       $text .= '<br><br>';
       $text .= 'Transverse: <br>';
       $query->moveFirst();
       while (!$query->eof)
       {
          $text .= $query->fields('m_transaction') . '<br>';
          $query->moveNext();
       }
       $this->addField(new MThemeBox('Results - Navigation', $text));
    }

    public function btnRaw_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('select * from miolo_transaction', $query->result);
       $this->addField(new MThemeBox('Results - RawData', $table));
    }

    public function btnCount_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $n = $db->count($sql);
       $command = $sql->select();
       $text = "$command => #records: " . $n . '<br><br>';
       $sql = new MSQL('*','miolo_transaction','(idtransaction > 100)');
       $n = $db->count($sql);
       $command = $sql->select();
       $text .= "$command => #records: " . $n . '<br><br>';
       $this->addField(new MThemeBox('Results - Count', $text));
    }

    public function btnRange_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $n = $db->count($sql);
       $totalpages = (int) $n / 5;
       for($page=1; $page <= $totalpages; $page++)
       {
         $range = new MQueryRange($page,5);
         $result = $db->MQueryRange($sql->select(), $range)->result;
         $n = $range->total;
         $text .= "Page: $page  [$n records]: <br>";
         $text .= '          #rec - transação' . '<br>';
         for ($i=0; $i < $n; $i++)
         {
            $text .= "         #$i - " . $result[$i][1] . '<br>';
         }
         $text .= "<br>";
       } 
       $this->addField(new MThemeBox('Results - List Range', $text));
    }

    public function btnSqlRange_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $n = $db->count($sql);
       $totalpages = (int) $n / 5;
       for($page=1; $page <= $totalpages; $page++)
       {
         $sql->setRange($page,5);
         $query = $db->getQuery($sql);
         $result = $query->result;
         $n = $query->getRowCount();
         $text .= "Page: $page  [$n records]: <br>";
         $text .= '          #rec - transação' . '<br>';
         for ($i=0; $i < $n; $i++)
         {
            $text .= "         #$i - " . $result[$i][1] . '<br>';
         }
         $text .= "<br>";
       } 
       $this->addField(new MThemeBox('Results - List Range', $text));
    }

    public function btnPag_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $query->setPageLength(10);
       $n = $query->getPageCount();
       $text = "<b>$n pages</b><br><br>";
       for($page=1; $page <= $n; $page++)
       {
           $result = $query->getPage($page); 
           $m = count($result);
           $text .= "Page: $page  [$m records]: <br>";
           $text .= '          #rec - transação' . '<br>';
           for ($i=0; $i < $m; $i++)
           {
              $text .= "         #$i - " . $result[$i][1] . '<br>';
           }
           $text .= "<br>";
       } 
       $this->addField(new MThemeBox('Results - Pagination', $text));
    }

    public function btnSequence_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       for($i=0; $i <= 5; $i++)
       {
           $id = $db->getNewId('seq_miolo_log','miolo_sequence');
           $text .= "id = $id <br>";
       } 
       $this->addField(new MThemeBox('Results - Sequence', $text));
    }

    public function btnTrans_click()
    {  global $MIOLO;
       
       $db = $MIOLO->getDatabase('demo');
// Exemplo com Commit
       $id = $db->getNewId('seq_miolo_transaction','miolo_sequence');
       $sql = new MSQL('idtransaction, m_transaction','miolo_transaction');
       $args = array($id,'Teste');
       $cmd = array();
       $cmd[] = $sql->insert($args);
       $sql->__construct('idtransaction, idgroup, rights','miolo_access');
       $cmd[] = $sql->insert(array($id, 1, 1));
       $cmd[] = $sql->insert(array($id, 2, 1));
       $cmd[] = $sql->insert(array($id, 3, 1));
       $cmd[] = $sql->insert(array($id, 4, 1));
       $text =  "id = $id <br>";
       try
       {
          $db->execute($cmd);
          $text .= "Transaction Ok";
       }
       catch (EDatabaseException $e)
       { 
          $text .=  "Transaction Fail: " . $e->getMessage();
       }
       $text .= '<br>';
// Exemplo com RollBack
       $id = $db->getNewId('seq_miolo_transaction','miolo_sequence');
       $sql = new MSQL('idtransaction, m_transaction','miolo_transaction');
       $args = array($id,'Teste2');
       $cmd = array();
       $cmd[] = $sql->insert($args);
       $sql->__construct('idtransaction, idgroup, rights','miolo_access');
       $cmd[] = $sql->insert(array($id, 1, 1));
       $cmd[] = $sql->insert(array($id, 2, 1));
       $cmd[] = $sql->insert(array(':NULL', 3, 1)); // este comando gera erro!
       $cmd[] = $sql->insert(array($id, 4, 1));
       $text .=  "id = $id <br>";
       try
       {
          $db->execute($cmd);
          $text .= "Transaction Ok";
       }
       catch (EDatabaseException $e)
       { 
          $text .=  "Transaction Fail: " . $e->getMessage();
       }
       $this->addField(new MThemeBox('Results - Transaction', $text));
    }

    public function btnOrderRs_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('ResultSet before order', $query->result);
       $this->addField(new MThemeBox('Results - before order', $table));
       $query->setOrder('m_transaction','SORT_DESC');
       $table = new MTableRaw('ResultSet after order', $query->result);
       $query->setOrder('m_transaction');
       $tableA = new MTableRaw('ResultSet after ReOrder', $query->result);
       $this->addField(new MThemeBox('Results - after order', $table));
       $this->addField(new MThemeBox('Results - after ReOrder', $tableA));
    }

    public function btnFilterRs_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('ResultSet without filter', $query->result);
       $this->addField(new MThemeBox('Results - without filter', $table));
       $query->addFilter('m_transaction','LIKE',"T%");
       $query->applyFilter();
       $table = new MTableRaw('ResultSet with filter', $query->result);
       $this->addField(new MThemeBox('Results - with filter', $table));
    }

    public function btnFilterReRs_click()
    {  global $MIOLO;
       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction');
       $query = $db->getQuery($sql);
       $table = new MTableRaw('ResultSet without filter', $query->result);
       $this->addField(new MThemeBox('Results - without filter', $table));
       $query->addFilter('m_transaction','regex','^(.*?)A(.*)');
       $query->applyFilter();
       $table = new MTableRaw('ResultSet with filter - RegEx: [^(.*?)A(.*)]', $query->result);
       $this->addField(new MThemeBox('Results - with filter', $table));
    }

    public function btnBind_click()
    {  global $MIOLO;

       $db = $MIOLO->getDatabase('demo');
       $sql = new MSQL('*','miolo_transaction','idtransaction=:idtransaction');
       for($i = 1; $i < 6; $i++)
       { 
           $sql->bind(array(':idtransaction'=>$i));
           $query = $db->getQuery($sql);
           $n = $query->getRowCount();
           $row = $query->getRowValues();
           foreach($row as $f)
           {
              $text .= $f . '<br>';
           }
       }
       $query->close();
       $this->addField(new MThemeBox('Results - Parameter', $text));
    }

    public function btnExceptionQuery_click()
    {  global $MIOLO;

       $text = '';
       $db = $MIOLO->getDatabase('demo');
       try
       {
           $sql = new MSQL('*','miolo_transaction',"m_transaction like 'A%'");
           $query = $db->getQuery($sql);
           $text .= "- Comando: " . $sql->command . " : OK <br>";
           $sql = new MSQL('*','cm_transaca',"m_transaction like 'A%'"); // gera erro !
           $query = $db->getQuery($sql);
           $text .= "- Comando: " . $sql->command . " : OK<br>";
       }
       catch (EDatabaseException $e)
       { 
           $text .= "- Comando: " . $sql->command . " : Fail : " . $e->getMessage();
       }
       $this->addField(new MThemeBox('Results - Exception', $text));
    }

    public function btnExceptionExec_click()
    {  global $MIOLO;

       $text = '';
       $db = $MIOLO->getDatabase('demo');
       try
       {
           $id = $db->getNewId('seq_miolo_transaction','miolo_sequence');
           $sql = new MSQL('idtransaction, m_transaction','miolo_transaction');
           $args = array($id,'Teste1 Example');
           $db->execute($sql->insert($args));
           $text .= "- Comando: " . $sql->command . " : OK <br>";
           $id = $db->getNewId('seq_miolo_transaction','miolo_sequence');
           $sql = new MSQL('idtran, m_transaction','miolo_transaction'); // gera erro!
           $args = array($id,'Teste2 Example');
           $db->execute($sql->insert($args));
           $text .= "- Comando: " . $sql->command . " : OK <br>";
       }
       catch (EDatabaseException $e)
       { 
           $text .= "- Comando: " . $sql->command . " : Fail : " . $e->getMessage();
       }
       $this->addField(new MThemeBox('Results - Exception', $text));
    }

    public function btnDirectSQL_click()
    {  global $MIOLO;

       $text = '';
       $db = $MIOLO->getDatabase('demo');
       $query = $db->query('SELECT g.m_group, t.m_transaction, a.rights FROM miolo_transaction t, miolo_group g, miolo_access a WHERE (a.idtransaction=t.idtransaction) AND (a.idgroup = g.idgroup) ORDER BY m_group,m_transaction'); 
       $table = new MTableRaw('ResultSet', $query->result);
       $this->addField(new MThemeBox('Results - DirectSQL', $table));
    }

    public function btnSQLCommand_click()
    {  global $MIOLO;

       $text = '';
       $db = $MIOLO->getDatabase('demo');
       $query = $db->getQueryCommand('SELECT g.m_group, t.m_transaction, a.rights FROM miolo_transaction t, miolo_group g, miolo_access a WHERE (a.idtransaction=t.idtransaction) AND (a.idgroup = g.idgroup) ORDER BY m_group,m_transaction'); 
       $table = new MTableRaw('ResultSet', $query->result);
       $this->addField(new MThemeBox('Results - SQLCommand', $table));
    }

    public function btnOpenClose_click()
    {  global $MIOLO;

       $db = $MIOLO->getDatabase('demo');
       $text = $db->conf . ': ' . $db->status;
       $db->close();
       $text .= ' - ' .$db->conf . ': ' . $db->status;
       $db = $MIOLO->getDatabase('demo'); // the same object is retrieved, and opened
       $text .= ' - ' .$db->conf . ': ' . $db->status;
       $this->addField(new MThemeBox('Results - Open/Close DB', $text));
    }


}
?>
