===== Vortex Example: Customer database with REST interface =====

This tutorial will go through all steps required to make a working customer database with a REST webservice interface.

=== Requirements ===
A working server environment, LAMP or WAMP, which is a server with a working Apache/PHP/MySQL setup.

=== Step 1: Installing the vortex framework ===

Install the latest Vortex framework and create a DBI. Instructions on how to do this are found [[vortex:installation|here]]. 
For this example we will call the DBI //mani//, you can however call your whatever you like.

=== Step 2: Verify the installation ===

After creating the dbi surfing to the folder on the webserver using a browser should look something like this:

{{:vortex:examples:vortex_1.png?200|}}

=== Step 3 : Creating the tables needed ===

If you created the DBI giving valid information about the database and using the standard Vortex config you should now be able to log in to the Vortex using the //admin// account and the standard password //changeMe//. Logging in the first time creates the empty Vortex database structure in your mysQL database.

Now it's time to create the tables we need. For this example we will create the following five tables, with at least the given fields:
  - customer
    * name, textfield
    * pid, textfield
    * noagreements, integer
    * nocontacts, integer
  - contactperson
    * name, textfield
    * position, list (a couple of options by your choosing)
    * customer, relation
  - contactinformation
    * type, list (a couple of options by your choosing, ex: phone, email, address)
    * value, textfield
    * contactperson, relation
  - product
    * name, textfield
    * price, integer
  - agreement
    * number, textfield
    * product, relation
    * customer, relation
 
For information on how to create the tables and fields, check out [[vortex:examples:create_table|the table creating guide]].
Give the tables and fields suitable descriptions. None of the tables will user WorkFlow or history for now.

=== Step 4: Defining the relations ===

An instruction on how to define relations are found [[vortex:examples:defining_relations|in the Relations example]]

Define the following relations for this DBI:
  * 1-x relation from contactperson.customer to customer
  * 1-x relation from contactinformation.contactperson to contactperson
  * 1-x relation from agreement.customer to customer
  * 1-x relation from agreement.product to product

=== Step 5: Creating test data ===

Now create a few customers, having contact persons with contact informations. Then create a few products and connect the products to the customers using the agreement table.

For information on how to enter data see [[vortex:examples:enter_data|the guide on entering data]].

=== Step 6: Summation scripts ===

The fields we created in the //customer// table called //nocontacts// and //noagreements// are supposed to show the total number of agreements and contact persons for the customer. To achieve this we will create two script methods for the //customer// table calculating theese sums. 
For in-depth information on table scripting see [[vortex:scripting:tablemethods|Table Scripting]].

To create the script class, create a new file called //customer.php// in the //scripts/// path in the DBI and open it in your favourite editor. As described in the //Table Scripting// section, the class for theese methods should be called //Methodcustomer//, so create this class.

<code php>
  <?php
    class Methodcustomer {
	
    }
  ?>
</code>

We'll call the first method //getNoAgreements//, which will count all the agreements connected to this customer and return the resulting value. According to the documentation, this would look something like:

<code php>
  public static function getNoAgreements( &$customer ) {
    $agreements = $customer->getRelated( "agreements" );
    return count( $agreements );
  }
</code>

In the same way we create our //getNoContacts// method, but only for the sake of it we'll do that in a different way. The resulting script file will look like this:

<file php customer.php>
<?php
  class Methodcustomer {
    public static function getNoAgreements( &$customer ) {
      $agreements = $customer->getRelated( "agreements" );
      return count( $agreements );
    }

    public static function getNoContacts( &$customer ) {
      $fd = new FilterData( "contactperson" );
      $fd->add( 'customer', FilterData::EQUALS, $customer->get( '_uuid' ) );
      $rows = $fd->getFilter()->getTableRecords();
      return count($rows);
    }
  }
?>
</file>

Now all we need to do is make sure the //nocontacts// and //noagreements// fields fetch their values from theese methods, according to the [[vortex:scripting:vxstring|VXString documentation]] this can be done by adding the name of the method to the //value// element of the //Extra info// field in the definition of theese fields. {{ :vortex:examples:vortex_15.png?373|}}

This will make sure theese two fields always has the calculated value from the connected method.

=== Step 7: REST Interface ===

Now the table structure is set up and we have the calculated fields in order, the next step is creating a REST webservice interface allowing us to get and set data to the database with some sort of crude authentication.

According to [[vortex:scripting:restservices|Vortex Integration via REST Webservices]] we need to create a class called //RESTService//, supply a //RESTUser// and a //RESTPassword// and implement the constructor for the service before implementing REST-methods. So let's start by creating a new file in the //scripts/// directory in the DBI called //rest.php// and make write the class there.

<file php rest.php>
<?php
  class RESTService {
    const RESTUser="restuser";
    const RESTPassword="restpass";
    private $reqMethod;

    public function __construct( $reqMethod ) {
      $this->reqMethod = $reqMethod;
    }
}
?>
</file>

We will of course have to create the user //restuser// with password //restpass//, make it an //External user// to make sure noone can log in to the vortex with that user, and give it permission to all the tables. We'll do this by adding it to the //users// group, and set the permissions so that the //users// group can read, write and create in all tables.

In this example we'll just create a method to handle the //customer// and //agreement// tables but you can add methods in a similar way for the other tables.

We'll start by adding a method called //customer// and let it take an argument called //customer// which is a json encoded string containing customer information, and another argument called //auth// which is a json encoded string containing autentication information.
We'll also add a very simple authentication method.

<code php>
  private function authenticate( $auth ) {
    if( $auth->username=="user" && $auth->password=="pass" ) return true;
    return false;
  }
	
  public function customer( $args ) {
    if( !isset($args['auth']) || !isset( $args['customer'] ) ) return "Invalid call";

    if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
    switch( $this->reqMethod ) {
      case 'POST':
        return $this->setCustomer( json_decode( $args['customer'] ) );
      case 'GET':
        return $this->getCustomer( json_decode( $args['customer'] ) );
    }
    return "Invalid call";
  }
</code>

Since only public functions are exported over the REST Interface we can use private helper functions within the class without them posing a security threat. We'll add the methods for getting and setting customer information:

<code php>
  private function getCustomer( $c ) {
    if( isset( $c->uuid ) ) {
      $iCustomer = TableRecordFactory::getTableRecord( $c->uuid );
      return $iCustomer->toJSON();
    }
    if( !isset( $c->name ) ) return "No customer found";
    $fd = new FilterData("customer");
    $fd->add( "name", FilterData::EQUALS, $c->name );
    $customers = $fd->getFilter()->getTableRecords();
    if( count( $customers ) > 0 ) return $customers[0]->toJSON();
    return "No customer found";
  }

  private function setCustomer( $c ) {
    $iCustomer = (isset( $c->uuid) ? TableRecordFactory::getTableRecord( $c->uuid ) : TableRecordFactory::getTableRecord( "customer", 0 ) );
    if( isset( $c->name ) ) $iCustomer->set( 'name', $c->name );
    if( isset( $c->pid ) ) $iCustomer->set( 'pid', $c->pid );
    $iCustomer->save();
    return $iCustomer->toJSON();
  }
</code>

What we now need to do is to add the //toJSON// method to the //Methodcustomer// class we created earlier:

<code php>
  public static function toJSON( &$customer ) {
    return json_encode( array( "customer"=>array(
      "pid"=>utf8_encode( $customer->get( 'pid' ) ),
      "name"=>utf8_encode( $customer->get( 'name' ) ),
      "noagreements"=>$customer->get( 'noagreements' ),
      "nocontacts"=>$customer->get( 'nocontacts' )
    )));
  }
</code>

Surfing to the URL of the REST service for this DBI should now return information from the database:
{{ :vortex:examples:vortex_16.png |}}

In the same way posting to the URL will create or change a customer from the supplied information. Adding the //_uuid// field to the output would be wise to let REST users be able to change the information of created customers.

Since the //customer// table does not contain any relation fields we'll post an example of how to write the REST handler for the //agreement// table. Below is the complete source code for the RESTService class with the handler for the agreement class as well.

<file php rest.php>
<?php
  class RESTService {
    const RESTUser="restuser";
    const RESTPassword="restpass";
    private $reqMethod;

    public function __construct( $reqMethod ) {
      $this->reqMethod = $reqMethod;
    }

    private function authenticate( $auth ) {
      if( $auth->username=="user" && $auth->password=="pass" ) return true;
      return false;
    }
	
    public function agreement( $args ) {
      if( !isset($args['auth']) || !isset( $args['agreement'] ) ) return "Invalid call";
      if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
      switch( $this->reqMethod ) {
        case "POST":
          return $this->setAgreement( json_decode( $args['agreement'] ) );
        case "GET":
          return $this->getAgreement( json_decode( $args['agreement'] ) );
      }
      return "Invalid call";
    }
	
    private function getAgreement( $a ) {
      if( !isset( $a->customer ) || !isset( $a->number ) ) return "No agreement found";
      $fd = new FilterData( "agreement" );
      $fd->add( "customer.name", FilterData::EQUALS, $a->customer );
      $fd->add( "number", FilterData::EQUALS, $a->number );
      $agreements = $fd->getFilter()->getTableRecords();
      if( count( $agreements ) < 1) return "No agreement found";
      return $agreements[0]->toJSON();
    }

    private function setAgreement( $a ) {
      if( !isset( $a->customer ) || !isset( $a->number ) || !isset( $a->product ) ) return "Invalid call";
      $cfd = new FilterData( "customer" );
      $cfd->add( "name", FilterData::EQUALS, $a->customer );
      $customers = $cfd->getFilter()->getTableRecords();
      if( count( $customers ) == 0 ) return "No customer found";
      $iCustomer = $customers[0];
	
      $pfd = new FilterData( "product" );
      $pfd->add( 'name', FilterData::EQUALS, $a->product );
      $products = $pfd->getFilter()->getTableRecords();
      if( count( $products ) == 0 ) return "No product found";
      $iProduct = $products[0];
	
      $fd = new FilterData( "agreement" );
      $fd->add( "customer.name", FilterData::EQUALS, $iCustomer->get( 'name' ) );
      $fd->add( "number", FilterData::EQUALS, $a->get( 'number' ) );
      $agreements = $fd->getFilter()->getTableRecords();
      if( count( $agreements ) == 0 ) {
        $iAgreement = TableRecordFactory::getTableRecord( "agreement", 0 );
      } else {
        $iAgreement = $agreements[0];
      }

      $iAgreement->set( "customer", $iCustomer->get( '_uuid' ) );
      $iAgreement->set( "product", $iProduct->get( '_uuid' ) );
      $iAgreement->set( "number", $a->number );
      $iAgreement->save();
      return $iAgreement->toJSON();
    }
	
    public function customer( $args ) {
      if( !isset($args['auth']) || !isset( $args['customer'] ) ) return "Invalid call";
      if( !$this->authenticate( json_decode( $args['auth'] ) ) ) return "Authentication error";
      switch( $this->reqMethod ) {
        case 'POST':
          return $this->setCustomer( json_decode( $args['customer'] ) );
        case 'GET':
          return $this->getCustomer( json_decode( $args['customer'] ) );
      }
      return "Invalid call";
    }
	
    private function getCustomer( $c ) {
      if( isset( $c->uuid ) ) {
        $iCustomer = TableRecordFactory::getTableRecord( $c->uuid );
        return $iCustomer->toJSON();
      }
      if( !isset( $c->name ) ) return "No customer found";
      $fd = new FilterData("customer");
      $fd->add( "name", FilterData::EQUALS, $c->name );
      $customers = $fd->getFilter()->getTableRecords();
      if( count( $customers ) > 0 ) return $customers[0]->toJSON();
      return "No customer found";
    }
	
    private function setCustomer( $c ) {
      $iCustomer = (isset( $c->uuid) ? TableRecordFactory::getTableRecord( $c->uuid ) : TableRecordFactory::getTableRecord( "customer", 0 ) );
      if( isset( $c->name ) ) $iCustomer->set( 'name', $c->name );
      if( isset( $c->pid ) ) $iCustomer->set( 'pid', $c->pid );
      $iCustomer->save();
      return $iCustomer->toJSON();
    }
}
?>
</file>

For this to work we need to add a //toJSON// method to the //Methodagreement// class in the same way as for the //customer// table.

<file php agreement.php>
<?php
  class Methodagreement {
    public static function toJSON( &$agreement ) {
      return json_encode( array( "customer"=>array(
        "number"=>utf8_encode( $agreement->get( 'number' ) ),
        "product"=>array( 
          "name"=>utf8_encode( $agreement->get( 'product.name') ),
          "price"=>utf8_encode( $agreement->get( 'product.price' ) )
        ),
        "customer"=>array( 
          "name"=>utf8_encode( $agreement->get( 'customer.name' ) ),
          "pid"=>utf8_encode( $agreement->get( 'customer.pid' ) )
        )
      )));
    }
  }
?>
</file>

In the same way as before, requesting the URL for the REST Service in your browser will return an agreement:
{{ :vortex:examples:vortex_17.png |}}

Posting data to the same URL will set data to an agreement.

=== Step 8: HTML Overview for Contact Person Table ===

For this example we'll create a simple //HTML Overview// for one of the tables, we'll choose the //contactperson// table. To do this, according to [[vortex:scripting:tablescripts#overview| the Overview documentation]] we'll add an //_overview// method to the //Scriptcontactperson//class that we'll create in a file called //contactperson.php// which resides in the //scripts/// directory.

The return of this method will be shown as an overview in the //Details view// of every record of this table. We'll just output a few comments and try it.

<file php contactperson.php>
<?php
class Scriptcontactperson {
  public static function _overview( &$cp ) {
    $txt = "<b>Overview of Contact Person</b><br />";
    $txt .= " <b>Name</b>: " . utf8_encode( $cp->get( 'name' ) ) . "<br />";
    $txt .= " <b>Position</b>:" . utf8_encode( $cp->getPlaintext( position ) ) . "<br />";
    $txt .= "<hr />Informations<br />";
	
    $cis = $cp->getRelated( 'contactinformations' );
    foreach( $cis AS $ci ) {
      $txt .= "  - " . $ci->getPlaintext( "type" ) . " : " . $ci->get( 'value' ) . "<br />";
    }
    return $txt;
  }
}
?>
</file>


=== Conclusion ===

We've now created a customer database with a few related tables, some calculated fields, an overview and a REST Webservice interface from scratch with minimal coding. This is just a small display of what the Vortex could be used for. 

If you find any errors or have any questions regarding the Vortex that are not answered in this Wiki, please contact us at info@stilit.se and we'll update the wiki as soon as possible.