documentation.adoc

SqlBuilder

Table of Contents

• 1. Preface
  • 1.1. Pro
  • 1.2. Contra
• 2. Create a new Type
  • 2.1. Simple way - reuse support ticket
  • 2.2. Hard way - add new context

[ERROR: include::_default_attributes_EN.adoc[] - unresolved directive]

1. Preface

The base idea is that tickets are just extended tasks.
And each TicketType can have additional features as well.

Currently ADITO does not provide inheritance but as it may come in the future, tickets are already built in a way which should be compatible to such a feature.

This means:

• Tickets are using at least two tables (TASK, TICKET)

• TicketTypes may use a third table which provides data only used by this type

1.1. Pro

• You can create a View over all Tickets and Tasks because the same Tables are in the background. For example you could create a Project-entity which provides a list of Tickets and Tasks. As the base-table is always (TICKET) you don’t need any special logic for each type)

• It should be easy to convert to a real inheritance when it’s available

• As each Ticket Type should use the same lib-functions (from Ticket_lib) bugs fixed there will fix bugs in all types.

1.2. Contra

• Currently, as there is no real inheritance, it’s more complex to create a new TicketType if you need a new context for it. (the hard way) But that’s why this guide exists.

2. Create a new Type

2.1. Simple way - reuse support ticket

In versions ⇐ 2019.3.1 there are some things missing to make this approach work.

The SupportTicket in basic is built to already support different ticket types. But because it’s the only type in basic, the respective parameter TicketType_param is preset by the supportTicket keyword.

There are some limitations with this approach.
You cannot easily add different logic for different types. (You could but then the Entity will soon become very complicated. And merges with basic may get complicated, too.

Currently Supported:

• TicketType specific available attributes

• TicketType specific available TaskStatus

As a rule: As soon as you need to hardcode the ticket type keyid (e.g. use $KeywordRegistry.ticketType$…​) in any other process as the TicketType_param → create an extra context (the hard way) or use the Keyword-attributes instead which are described later.

2.1.1. setup

1. add a new TicketType Keyword in liquibase (in your own liquibase folder, not the basic folder!)

<insert tableName="AB_KEYWORD_ENTRY">
    <column name="AB_KEYWORD_ENTRYID" value="54fd061b-3897-49c7-a1fb-92a35b87c846"/>
    <column name="KEYID" value="TESTTICKET"/>
    <column name="TITLE" value="Testticket"/>
    <column name="CONTAINER" value="TicketType"/>
    <column name="SORTING" valueNumeric="1"/>
    <column name="ISACTIVE" valueNumeric="1"/>
    <column name="ISESSENTIAL" valueNumeric="1"/>
</insert>

1. remove the preset of the TicketType_param: SupportTicket_entity → parameters → TicketType_param → valueProcess comment or remove the process. This will enable the TicketType dropdown in the edit view.

2. optionally: change the title and titlePlural of the SupportTicket entity to something more generic (such as "Ticket")

3. deploy

4. relogin (to clear the keyword cache)

5. create a new Supportticket. You can now set the type to Testticket.

You will notice that there are no attributes available and too much possible status.
That’s why you can extend the TicketType:

2.1.2. additional configuration using keyword attributes

The keyword container TicketType provides some keywordattributes to further customize the tickettype.
Currently supported are:

• possible attributes

• available status

• default status

attributes

The keyword attribute named attribute accepts a json string which should be an array of ATTRIBUTEID's.

You can provide any AttributeId, if it is a group, all attributes inside of the group are used.

All groups, attributes and attributes inside of these groups have to be assigned to the SupportTicket as usage (or if you created a custom context, use that) with liquibase-xml like that:

<insert tableName="AB_ATTRIBUTEUSAGE">
    <column name="AB_ATTRIBUTEUSAGEID" value="dfe58279-a0a1-4179-be7d-9fb18ae920ab"/>
    <column name="AB_ATTRIBUTE_ID" value="ff8b1caf-cf30-4edb-b5ca-a9a219ba8399"/>
    <column name="OBJECT_TYPE" value="SupportTicket"/>
</insert>

For example the Keyword SUPPORTTICKET has the following JSON-string:
["ff8b1caf-cf30-4edb-b5ca-a9a219ba8399"]
using the liquibase-xml:

<insert tableName="AB_KEYWORD_ATTRIBUTERELATION">
    <column name="AB_KEYWORD_ATTRIBUTERELATIONID" value="9fd26b1e-1bdd-4ec0-8a4c-d2943232c194"/>
    <column name="AB_KEYWORD_ENTRY_ID" value="32aea6bb-6b54-4341-8bf5-6b5b67c004a1"/>
    <column name="AB_KEYWORD_ATTRIBUTE_ID" value="af6a0af9-0102-426b-a1f7-c8abfea79793"/>
    <column name="LONG_CHAR_VALUE" value="[&quot;ff8b1caf-cf30-4edb-b5ca-a9a219ba8399&quot;]"/>
</insert>

" has to be replaced by " inside of the xml

status

Do not create a new Status-keyword-container. Instead extend the TaskStatus container by own status and use them. This will allow you to search for the status in all tasks/tickets no matter of which type they are. → Each TicketType allows only a small subset of all available TaskStatus

To alter the possible status just add the availableStatus keyword attribute:

<insert tableName="AB_KEYWORD_ATTRIBUTERELATION">
    <column name="AB_KEYWORD_ATTRIBUTERELATIONID" value="3946449e-d3f1-40c4-80c0-d38ad2cd586e"/>
    <column name="AB_KEYWORD_ENTRY_ID" value="32aea6bb-6b54-4341-8bf5-6b5b67c004a1"/>
    <column name="AB_KEYWORD_ATTRIBUTE_ID" value="6f10015d-b7ee-43aa-99f9-87960ec2e4fd"/>
    <column name="LONG_CHAR_VALUE" value="[&quot;UNASSIGNED&quot;,&quot;ASSIGNED&quot;,&quot;IN-PROGRESS&quot;,&quot;ENDED-DONE&quot;,&quot;ENDED-ABANDONED&quot;,&quot;WAITING&quot;,&quot;STILL-INCORRECT&quot;,&quot;CUSTOMER-CHECKS&quot;]"/>
</insert>

It is also just a json array which contains the Keyid’s from the Keyword-Container TaskStatus.

If you need different / additional status which is not available in TaskStatus, just add it to TaskStatus:

<insert tableName="AB_KEYWORD_ENTRY">
    <column name="AB_KEYWORD_ENTRYID" value="dbbe484b-be80-4be5-a804-e4aa877f25a2"/>
    <column name="KEYID" value="SUPERSTATUS"/>
    <column name="TITLE" value="superstatus"/>
    <column name="CONTAINER" value="TaskStatus"/>
    <column name="SORTING" valueNumeric="110"/>
    <column name="ISACTIVE" valueNumeric="1"/>
    <column name="ISESSENTIAL" valueNumeric="0"/>
</insert>

<!-- the Icon used by this new status -->
<insert tableName="AB_KEYWORD_ATTRIBUTERELATION">
    <column name="AB_KEYWORD_ATTRIBUTERELATIONID" value="03581cd1-1372-43b0-b10e-2bae51a4df30"/>
    <column name="AB_KEYWORD_ENTRY_ID" value="dbbe484b-be80-4be5-a804-e4aa877f25a2"/>
    <column name="AB_KEYWORD_ATTRIBUTE_ID" value="472f9bc2-3331-49f9-aba2-72d5eeb89654"/>
    <column name="CHAR_VALUE" value="NEON:STATUS_NOT_ASSIGNED"/>
</insert>

Then you can add it to the availableStatus array described above

default status

To configure one of the availableStatus as default (prefilled) just add a defaultStatus keyword attribute.

<insert tableName="AB_KEYWORD_ATTRIBUTERELATION">
    <column name="AB_KEYWORD_ATTRIBUTERELATIONID" value="6f4e5ec5-6de8-47c1-a70e-d55b84f071e0"/>
    <column name="AB_KEYWORD_ENTRY_ID" value="32aea6bb-6b54-4341-8bf5-6b5b67c004a1"/>
    <column name="AB_KEYWORD_ATTRIBUTE_ID" value="729a41d1-0463-480d-bf7b-0eb6c2e96f37"/>
    <column name="CHAR_VALUE" value="IN-PROGRESS"/>
</insert>

2.1.3. add additional custom configurations

If you just would like to extend the SupportTicket (or another custom context) by a per-TicketType configuration without hardcoded cases in the support ticket entity, you can easily add custom keyword attributes and call them in the jdito code:

Here an example to add a configuration for hiding the progress-field for certain types.

1. first you have to define a new keyword attribute:

   <insert tableName="AB_KEYWORD_ATTRIBUTE">
       <column name="AB_KEYWORD_ATTRIBUTEID" value="c33ce01f-02f2-41ac-b2be-92e5fca67e31"/>
       <column name="NAME" value="hideProgress"/>
       <column name="CONTAINER" value="TicketType"/>
       <column name="KIND" value="BOOL_VALUE"/>
   </insert>

2. Then you need to assign true (1) to all types which you would like to hide the progress:

   <insert tableName="AB_KEYWORD_ATTRIBUTERELATION">
       <column name="AB_KEYWORD_ATTRIBUTERELATIONID" value="79728cdb-28b8-49e8-97bf-d1318981dace"/>
       <column name="AB_KEYWORD_ENTRY_ID" value="98075159-ecc2-4851-a971-6f3123a495d3"/>
       <column name="AB_KEYWORD_ATTRIBUTE_ID" value="c33ce01f-02f2-41ac-b2be-92e5fca67e31"/>
       <column name="BOOL_VALUE" valueNumeric="1"/>
   </insert>

3. Extend the Ticket_lib, add a new method based on e.g. TicketUtils.prototype.getAvailableStatus:

   /**
    * Get if the progress-field should be hidden
    *
    * @return {Boolean} true if the progress-field should be hidden, false if it should be visible
    */
   TicketUtils.prototype.isProgressHidden = function()
   {
       var hidden = JSON.parse(KeywordUtils.getAttributeRelation(this.type, $KeywordRegistry.ticketType(), "hideProgress", false))
   
       return hidden;
   }

4. Now you can call this method where you need it. In this case the stateProcess from the TASK_PROGRESS field:

   import("system.result");
   import("system.vars");
   import("system.neon");
   import("Ticket_lib");
   
   if (vars.get("$field.TICKETTYPE"))
   {
       ticketUtils = new TicketUtils(vars.get("$field.TICKETTYPE"))
       if (ticketUtils.isProgressHidden())
           result.string(neon.COMPONENTSTATE_INVISIBLE);
       else
       {
           // extra if needed, because in edit-mode, a change from COMPONENTSTATE_INVISIBLE to COMPONENTSTATE_AUTO does not make the component visible
           if (vars.get("$sys.recordstate") == neon.OPERATINGSTATE_NEW || vars.get("$sys.recordstate") == neon.OPERATINGSTATE_EDIT)
               result.string(neon.COMPONENTSTATE_EDITABLE);
           else
               result.string(neon.COMPONENTSTATE_AUTO);
       }
   }

   Now the admin can customize the visibility of the progress-field without any further code change for each TicketType. Just by using the keywords.

2.2. Hard way - add new context

Sometimes you may have special demands and need an own context for your type. E.g. if you add many custom fields and special behaviour.

But be carefull. This is not an easy task and you should consider to extend the SupportTicket by using keyword attributes like explaned above.
(There are ideas for inhertince of entities, which would make this task much more easy, but currently it’s only an idea)

As Reference implementation use SupportTicket from the basic.

1. create a new TicketType keyword and it’s keyword attributes as described above (in the easy way)

2. if you need the attributes add your context to the Attribute_lib: AttributeUtil.getPossibleUsageContexts()

3. create a new Context (TestTicket)

4. create a new Entity (TestTicket_entity)

5. Basically you now have to recreate each field, process, parameter, consumer, …​ just like it’s done in Support-ticket.

   • you may join an additional table with custom fields

   • you may ommit some things if they are not needed

     The designer currently cannot copy a whole context/entity. And you shouldn’t copy it yourselve outside of the designer because you will miss something, which could have unknown sideeffects. So it’s better to recreate the entity step by step.

6. If you would like to use only one special TicketType with the entity, just change the valueProcess of the Parameter TicketType_param. If you yould like to use several types, remove the process and optionally add a whitelist to the consumer KeywordTickettype and also filter in the conditionProcess only for the types you need.

7. Add any further customization you want.

8. for support of 360° and creating a ticket from the activity, task or other ticket contexts

   1. add the new context to the Context_lib

9. if you need support for creating a ticket from the activity, task or other ticket contexts

   1. you have to extend the ActivityTask_lib. There is a switch case in _ActivityTaskUtils._addLinkRecords()

   2. add a similar function like TicketUtils.createNewTicket() into the Ticket_lib using your own contextname

   3. add actions to the activity, task supportticket-entities

10. for 360° support, add your context to the ObjectType_params in all Providers of 36Degree entity where you need it (and to the arrays in Context_lib→ContextUtils.getSelectMap of the .setSubContexts(…​) calls where needed)

11. if you would like that your context is selectable in attributes, add it to Attribute_lib→_selectableEntities

12. Search the basic for any further occurence of SupportTicket or TICKET and decide if you have to add something more. (As there may be added some new special cases to the basic, this guide may not contain all places.)