SQL queries

In this form, SQL queries are administered. These queries are the foundation the user defined content are built around. For example, the user defined overviews in the Client, and in several reports, and in some of the sections of the administration.

The SQL queries is created in a window that looks like the window in the image above.
 
Name
Here the name of the query is shown/should be entered.
 
Query
Here the SQL query is entered. An example of an SQL query can be found below.

Note
Here a description of the SQL query can be entered to make it easier to find out what it does.
  
Type
Which database the query should be entered into. Balthzar represent Client in the selection, and ERP represent a connection to the ERP system.

Enable text color
If this tick box is activated it will show some commands with colors. For example, if tags are used correctly they will be green.  

Add tag
To add tags to the SQL Query, use the drop-down menu to the right. Once the tag is selected, use the button to confirm. If inserted correctly, and if the tick box enable text color is activated the tag will be seen in green. See the picture above. A list of all the tags, and information about each one, can be found below.      

If any of the tags that ends with "User" is chosen it means that the end user is going to input the correspoding data. To make it easy for the end user you have to enter a text that will help the end user. The end user is the person who tries to use the SQL query.

Below is a list of the different queries, and tags that exists.

When a SQL-querie is writen in Baltzhar there are a few shortcuts that are good to know.
There exist command to aquire data that is commonly used from the client. 

Company ID
To get the company ID you write:
<%company_id%>.

Start of the day, Today
To get the start of the current day you write: 
<%today_start%>.

End of the day, Today
To get the end of the current day you write: 
<%today_end%>.
     
Start of the day, Yesterday
To get the start of yesterday you write: 
<%yesterday_start%>.

End of the day, Yesterday
To get the end of yesterday you write: 
<%yesterday_end%>.
     
Start of week
To get the start of the current week you write:
<%this_week_start%>.
End of week
To get the end of the current week you write: 
<%this_week_end%>. 

Start of previous week
To get the start of the previous week you write:
<%last_week_start%>.

End of previous week
To get the end of the previous week you write:
<%last_week_end%>.

New user in system
In order to replace the username with username from current logged in user you write:
<%username%>.  

If you want the user to enter data when the query is asked that is possible. 
The different data types you can aske the user to enter is:
Date
'<?d? "info for the user" ?d?>'
Time
'<?t? "info for the user" ?t?>'
Text
'<?s? "info for the user" ?s?>'
Integer
<?i? "info for the user" ?i?>
Float
<?f? "info for the user" ?f?>
Boolean (true or false expression)
<?b? "info for the user" ?b?>
Machine list sorted on MachineID in ascending order
'<?m? "info for the user" ?m?>'
Machine list sroted on MachineID in decending order
'<?n? "info for the user" ?n?>'

In all examples above the "info for the user" be exchanged for the info that the user might need. If, for example, the user is to enter start date the string could look as follows. 
'<?d? insert start date ?d?>'
It is important to remember to write a ' in the begining and end of the instances that starts/ends with it in the examples above. 

SQL querie example

Below follows an example on how a script that fetches all user-made SQL queries that has been made by a certain user, where the "username" is entered by the user (left image). How this looks for the user can be seen in the right image.