FormActionTest.php

<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/15/16
 * Time: 8:24 AM
 */

namespace IMATHUZH\Qfq\Tests\Unit\Core\Form;

 
use IMATHUZH\Qfq\Core\Form\FormAction;
use IMATHUZH\Qfq\Core\Store\Store;
use IMATHUZH\Qfq\Tests\Unit\Core\Database\AbstractDatabaseTest;

require_once(__DIR__ . '/../Database/AbstractDatabaseTest.php');

/**
 * Class FormActionTest
 * @package qfq
 */
class FormActionTest extends AbstractDatabaseTest {

    /**
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoad() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        // Nothing to do: should not throw an exception
        $formAction->elements(0, array(), '');
        $formAction->elements(0, array(), FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);

        // Fire sqlValidate with one record, expect 1 record
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 1}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '1';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);

        // Fire sqlValidate with one record, expect 0-2 records
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1,2';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);

        // Fire sqlValidate with one record, expect 0-2 records
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1,2';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);

        // Check with more classes
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_LOAD;
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1,2';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD);

        # Fake to suppress phpUnit about missing test.
        $this->assertEquals('', '');
    }

    /**
     * Expect 0 recrod, but get 1
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException1() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 1}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '0';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);
    }

    /**
     * Expect 1 recrod, but get 0
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException0() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 0}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '1';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);
    }

    /**
     * Expect '0,1', but get 2 records
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException2() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_BEFORE_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD);
    }

    /**
     * Expect '0,1', but get 2 records
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException3() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_UPDATE;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);
    }

    /**
     * Necessary FE is empty > don't process check
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException4() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_UPDATE;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1';

        // one FE in list
        $this->store->setVar('street', 'Street', STORE_FORM, true);
        $this->store->setVar('city', '', STORE_FORM, true);
        $feSpecAction[FE_REQUIRED_LIST] = 'city';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);

        // three FE in list. one is set, one not, one dont exist
        $this->store->setVar('city', '', STORE_FORM, true);
        $feSpecAction[FE_REQUIRED_LIST] = 'street,city,downtown';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);

        # Fake to suppress phpUnit about missing test.
        $this->assertEquals('', '');

    }

    /**
     * Expect '0,1', but get 2 records
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException5() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $this->store->setVar('city', 'New York', STORE_FORM, true);
        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_UPDATE;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '0,1';
        $formAction->elements(0, [$feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);
    }

    /**
     * Do check for 2 action records
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoad2() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '2';
        $formAction->elements(0, [$feSpecAction, $feSpecAction], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);

        # Fake to suppress phpUnit about missing test.
        $this->assertEquals('', '');
    }

    /**
     * Do check for 2 action records, fail on second.
     * @expectedException \UserFormException
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testBeforeLoadException6() {
        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_LOAD;
        $feSpecAction[FE_MESSAGE_FAIL] = 'error';
        $feSpecAction[FE_SQL_VALIDATE] = '{{!SELECT id FROM Person LIMIT 2}}';
        $feSpecAction[FE_EXPECT_RECORDS] = '2';
        $feSpecAction2 = $feSpecAction;
        $feSpecAction2[FE_EXPECT_RECORDS] = '0';
        $formAction->elements(0, [$feSpecAction, $feSpecAction2], FE_TYPE_BEFORE_LOAD . ',' . FE_TYPE_AFTER_LOAD . ',' . FE_TYPE_AFTER_UPDATE . ',' . FE_TYPE_BEFORE_SAVE);
    }

    /**
     * Process INSERT
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testInsert() {

        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_SAVE;
        $feSpecAction[FE_SQL_INSERT] = '{{ INSERT INTO Address (city, personId) VALUES ("Downtown", {{r}}) }} ';
        $feSpecAction[FE_SQL_UPDATE] = '{{ UPDATE Address SET city="invalid" WHERE id={{r}} }} ';
        $feSpecAction[FE_SQL_DELETE] = '{{ DELETE FROM Address WHERE personId={{r}} AND id=0 }} ';

        $this->store->setVar('r', '2', STORE_SIP, true);

        // slaveId: 0
        $feSpecAction[FE_SLAVE_ID] = '0';
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('TRUNCATE Address');
        $formAction->elements(2, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('SELECT id, city, personId FROM Address', ROW_IMPLODE_ALL);
        $this->assertEquals('1Downtown2', $result);


        // slaveId: SELECT ... >> ''
        $feSpecAction[FE_SLAVE_ID] = '{{SELECT id FROM Address WHERE personId={{r}} ORDER BY id LIMIT 1}}';
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('TRUNCATE Address');
        $formAction->elements(2, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('SELECT id, city, personId FROM Address', ROW_IMPLODE_ALL);
        $this->assertEquals('1Downtown2', $result);


        // slaveId: slaveId through column in master record & update Master record with slaveId
        $this->store->setVar('adrId', '0', STORE_RECORD);
        $feSpecAction[FE_NAME] = 'adrId';
        $feSpecAction[FE_SLAVE_ID] = '';

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('TRUNCATE Address');
        $formAction->elements(2, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        // get the new slave record
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('SELECT id, city, personId FROM Address', ROW_IMPLODE_ALL);
        $this->assertEquals('1Downtown2', $result);

        // get the updated id in the master record
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql('SELECT id, name, adrId FROM Person WHERE id=2', ROW_IMPLODE_ALL);
        $this->assertEquals('2Smith1', $result);
    }

    /**
     * Process UPDATE
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testUpdate() {

        $formSpec[F_TABLE_NAME] = 'Person';
        $formAction = new FormAction($formSpec, $this->dbArray[DB_INDEX_DEFAULT], true);

        $masterId = 2;

        $feSpecAction[FE_NAME] = '';
        $feSpecAction[FE_TYPE] = FE_TYPE_AFTER_SAVE;
        $feSpecAction[FE_SQL_INSERT] = "{{ INSERT INTO Address (city, personId) VALUES ('invalid', {{r}}) }} ";
        $feSpecAction[FE_SQL_UPDATE] = "{{ UPDATE Address SET city='Uptown' WHERE id={{slaveId:V}} }} ";

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("TRUNCATE Address");
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("INSERT INTO Address (city, personId) VALUES ('Downtown1', 1)");
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("INSERT INTO Address (city, personId) VALUES ('Downtown2', 1)");
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("INSERT INTO Address (city, personId) VALUES ('Downtown3', $masterId)");

        $this->store->setVar('r', "$masterId", STORE_SIP, true);

        // slaveId: 1 - hard coded
        $feSpecAction[FE_SLAVE_ID] = '3';
        $formAction->elements($masterId, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, city, personId FROM Address WHERE personId=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('3Uptown' . $masterId, $result);


        // slaveId: SELECT ... >> ''
        $feSpecAction[FE_SLAVE_ID] = "{{SELECT id FROM Address WHERE personId={{r}} ORDER BY id LIMIT 1}}";
        $formAction->elements($masterId, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, city, personId FROM Address WHERE personId=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('3Uptown' . $masterId, $result);


        // slaveId: column in master record
        $this->dbArray[DB_INDEX_DEFAULT]->sql("UPDATE Person SET adrId=3 WHERE id=$masterId", ROW_IMPLODE_ALL);
//        $this->store->setVar('adrId', '3', STORE_RECORD);
        $feSpecAction[FE_NAME] = 'adrId';
        $feSpecAction[FE_SLAVE_ID] = '';

        $formAction->elements($masterId, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, city, personId FROM Address WHERE personId=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('3Uptown' . $masterId, $result);

        // Check updated primary record: person.adrId
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, name, adrId FROM Person WHERE id=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('2Smith3', $result);


        // Same situation as above, with sqlDelete.
        $feSpecAction[FE_SQL_DELETE] = "{{ DELETE FROM Address WHERE id={{slaveId}} }} ";
        $formAction->elements($masterId, [$feSpecAction], FE_TYPE_AFTER_SAVE);

        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, city, personId FROM Address WHERE personId=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('', $result);

        // Check updated primary record: person.adrId
        $result = $this->dbArray[DB_INDEX_DEFAULT]->sql("SELECT id, name, adrId FROM Person WHERE id=$masterId", ROW_IMPLODE_ALL);
        $this->assertEquals('2Smith0', $result);
    }

    /**
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    protected function setUp() {

        $this->store = Store::getInstance('form=TestFormName', true);
        parent::setUp();

        $this->store->setVar('form', 'TestFormName', STORE_TYPO3);
        $this->executeSQLFile(__DIR__ . '/../Database/fixtures/Generic.sql', true);

    }

}