Creating SMCP Queries

Post date: Sep 29, 2014 8:56:19 PM

The SMCP Query Selector tool allows you to create standard SQL queries to mine data from the SMCP database.  These queries can then be saved and re-used  - any query can be 'private' (meaning that it only appears later to the person who created it) or not (meaning that it will appear in the list of queries to any user with the permission to view the list).

The function accepts SQL queries formatted in accordance with the current MySQL SQL command specifications.  Table definitions, including table names, field names, and field sizes (where applicable) appear when you click the link labeled 'Display Data Definitions' on the Query Selector screen.

Queries can be displayed on the screen, downloaded as comma-delimited lists to be imported into spreadsheet programs, or downloaded as 'HTML' (web formatted) documents.  You can also choose to total the numeric fields in the query.

The Query Selector provides 'aliases' to allow you to automatically reference the user's name ('*USER*'), the 'session tag' ('*SESSIONTAG*' - useful for creating links in your queries), and the 'link base' ('*LINKBASE*' - also used to create links in queries).

Additionally, you can also create prompts in your queries which will appear to query users - these appear on a second screen after the query is submitted.  For example, you might want the user to select a starting date, or a customer number to include in the query.  You can add three types of prompts:

1) A simple text box for string or numeric values

2) A 'date picker' field

3) A 'drop down list' - with the ability to read drop down values from the data, using the 'SQLDROPDOWNLIST' keyword (see below).

The 'date picker' field can display default dates for useful dates, like the current date, etc., using these keywords:

TODAY

FIRSTDAYOFYEAR

FIRSTDAYOFMONTH

LASTDAYOFYEAR

LASTDAYOFMONTH

Each of the three types is indicated by adding a special tag to the query, which will be replaced in the final query by whatever value the user chooses.  The tag definitions are listed at the bottom of the Query Selector screen.  For example, this query will prompt for an order type from a drop down list, a customer number from a text box, and a starting order date from a date picker.  In the query results, the order number will be a link which takes the user to the 'Edit' screen for each order:

select

 datOrderDate AS 'ORDER DATE'

, CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMEditOrderEdit?OrderNumber=',strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>', strimmedordernumber, '</A>') as 'ORDER #'

, sCustomerCode AS 'CUSTOMER #'

, if (iOrderType = 1, 'Active', if (iOrderType = 3, 'Standing', 'Quote')) AS 'ORDER TYPE'

 from orderheaders where (

(iOrderType = [[*DROPDOWNLIST*{Select an order type:}{1,3,4}{Active,Standing,Quote}]])

 AND (sCustomerCode ='[[Enter the customer code]]')

 AND (datOrderDate >= STR_TO_DATE('[[*DATEPICKER*{With order dates STARTING on:}{2/3/2015}]] 00:00:00', '%m/%d/%Y %H:%i:%S'))

) ORDER BY datOrderDate

The 'parameters' screen created by this query appears like this:

Another example - use this when you need a 'starting' and 'ending' date range:  this query will prompt for a service type from a drop down list, a customer number from a text box, a starting and ending order date from a date picker - it also includes a link to edit the order.  The starting date defaults to the first day of the month, and the ending date defaults to today:

select

 datOrderDate AS 'ORDER DATE'

, CONCAT('<A HREF=\*LINKBASE*smcontrolpanel.SMEditOrderEdit?OrderNumber=',strimmedordernumber, '&SESSIONTAG=*SESSIONTAG*>', strimmedordernumber, '</A>') as 'ORDER #'

, sCustomerCode AS 'CUSTOMER #'

, sServiceTypeCode AS 'SERVICE TYPE CODE'

, sServiceTypeCodeDescription AS 'SERVICE TYPE DESCRIPTION'

 from orderheaders where (

(sServiceTypeCode = '[[*DROPDOWNLIST*{Select a status:}{SH0001,SH0002,SH0003,SH0004}{Residential Service,Residential Installation,Commercial Service,Commercial Installation}]]')

 AND (sCustomerCode = '[[Enter the customer code]]')

AND (datOrderDate >= STR_TO_DATE('[[*DATEPICKER*{With order dates STARTING on:}{FIRSTDAYOFMONTH}]] 00:00:00', '%m/%d/%Y %H:%i:%S'))

AND (datOrderDate <= STR_TO_DATE('[[*DATEPICKER*{With order dates ENDING on:}{TODAY}]] 23:59:59', '%m/%d/%Y %H:%i:%S'))

) ORDER BY datOrderDate

Note that when using the datepicker, the date still has to be converted to MySQL date format - see the examples above.  Also note that if the 'values' in the drop down list are numeric, they don't require string delimiters - but if the values ARE strings, then each value needs to be surrounded with single quotes (').  The drop down list descriptions don't require any kind of quotes.  Neither the values NOR the descriptions should include commas, since commas are used to separate the values and descriptions in the lists.

USING THE 'SQLDROPDOWN' FEATURE:

You can also create 'dynamic' drop down lists, reading the values and labels from your live data.  Using the 'SQLDROPDOWNLIST' keyword, you'll replace the list of values and labels with a SQL 'SELECT' statement.  The system will run the SQL command, and use the FIRST TWO FIELDS of the SELECT statement for the DROPDOWNLIST 'values', and 'labels', respectively.  (If you only 'SELECT' one field, that field will be used for both the 'values' and the 'labels'.)

The syntax for the SQL drop down list portion looks like this:

[[*SQLDROPDOWNLIST*{Prompt}{SQL Command}]]

Here's a simple example of how to get a record from the 'users' table, creating a drop down list from the users table itself:

SELECT * from users WHERE (

(lid = [[*SQLDROPDOWNLIST*{Prompt}{SELECT lid, sUserName from users ORDER BY sUserName}]])

)

You can use as many SQLDROPDOWNLIST key words in your queries as needed.

SETTING QUERY VARIABLES:

MySQL allows you to create and set values for variables that make can make more complex queries possible.  For example, you might want to add a 'row counter' to your query, which you would do IN MySQL with a variable, like this:

SET @rownum := 0;

SELECT sitemnumber, @rownum := @rownum + 1 AS rowcounter FROM icitems;

In order to use a 'set variable' string like the one above in the SMCP Query tool, you have to use the 'SETVARIABLES' flag to indicate that the line is setting variables.  So the query above would be entered like this in the SMCP Query Selector:

[[*SETVARIABLES*SET @rownum := 0]]

SELECT sitemnumber, @rownum := @rownum + 1 AS rowcounter FROM icitems

The query selector will process the 'SET' line separately first, then run the query.  You can enter as many 'SET' lines as needed.