====== Conventions - General Report Writing ======

Author: Michelle McCausland


----

===== Commenting =====

  * All reports must have a comment relating to the report creator/ who modified the report, the date modified and associated ticket number as per the report checklist.

  * Commenting should be used to add further explanations to more difficult aspects of the report to ensure that a new person working on the report can easily determine how the report functions. 
      * For example if there is a tricky piece of groovy code or if there is a complicated mySQL statement.
      
  * Don't over comment

  * Single line commenting within mySQL is achieved by placing ''# THIS IS A COMMENT '' at the start of the line to denote it is a comment

  * Multi line commenting outside of the mysQL is achieved by using: ''/* THIS IS A COMMENT */''

----

===== SQL Fields =====

  * SQL Field retrieval i.e. ''SELECT t.id, t.name, t.summary'' should adhere to the customer's specifications per the report spec doc

  * Use of id fields or extra fields for testing should be removed prior to the customer reviewing the report

  * SQL fields should always be in the following format ''t.id AS ticketId'' 
      * Suitable names should be given to each field being retrieved
      * These names should always be in camel case to ensure the names display correctly in the report output e.g. ''myFieldName, thisIsMyFieldName''

  * Each SQL field should be contained on it's own row and should be indented correctly as per workbench formatting:

{{ :onboarding:reportingmanager:99.png?nolink |}}

----

===== Table Joins =====

  * Table joins should be created and tested thoroughly in mySQL workbench before being added into the report builder and the reporting manager

  * Types of joins should be considered in this process: [[https://www.w3schools.com/sql/sql_join.asp|SQL Joins - W3 Schools]]

  * Use the EXPLAIN feature of mySQL workbench to identify inefficient joins:
{{ :onboarding:reportingmanager:explain.png?nolink |}}


  * Each SQL join should be contained on it's own row and should be indented correctly as per workbench formatting:

{{ :onboarding:reportingmanager:99.png?nolink |}}

  * Fully qualify everything if using more than one database in the report, ie ticketer.ticket and snmp_manager.network_element not ticket and network_element

==== Joining Hubs and their children ====
//For reference only - should not be blindly copy & pasted into report.//

<code>network_element hub 
LEFT JOIN network_element child on child.parent_id = hub.id AND hub.ne_type = "Controller"</code>

==== Joining tickets to ticket statuses ====
//For reference only - should not be blindly copy & pasted into report.//
<code>ticket AS t
JOIN ticket_status st ON st.id = t.current_status_id  </code>

==== Joining tickets to ticket changes to pick specific forms ====
//For reference only - should not be blindly copy & pasted into report.//
<code>JOIN ticket_change tc ON tc.id = (SELECT max(id) FROM ticket_change WHERE ticket_id = t.id AND contents LIKE 'Your Form%')
JOIN f_form_table AS noc ON (tc.id = noc.change_id)</code>

Be aware that a ticket can have more than one of a form attached i.e. Tickets with multiple dispatches will have multiple dispatch forms etc. This should be factored into your query.

----

===== Query Criteria =====

  * Query criteria should be created and tested thoroughly in mySQL workbench before being added into the report builder and the reporting manager

  * Query criteria i.e. '' WHERE t.closed_date IS NULL AND t.id = 3 '' Should be used to refine the SQL query to:
      * Filter out unwanted information such as bad data, orphans, show in monitor = false elements
      * To return tickets only in a particular workflow
      * To only retrieve tickets in a particular state
      * To only retrieve open / closed tickets
      * etc.

  * The query criteria will depend on the report spec but should be examined and considered closely when forming the SQL query

  * Query criteria can incorporate variable selection - where the user generates the report based on the variables selected. For more detail on this process please consult the following wiki entry: [[onboarding:reportingmanager:conventions_-_report_variables|Conventions - Report Variables]]

  * Each SQL Criteria statement should be contained on it's own row and should be indented correctly as per workbench formatting:

{{ :onboarding:reportingmanager:99.png?nolink |}}

----

===== Grouping & Ordering =====

  * Grouping and ordering should be created and tested thoroughly in MySQL workbench before being added into the report builder and the reporting manager

  * The general convention for ordering is to order on the first field returned unless otherwise specified in the report spec doc or by the customer

  * Grouping should be done with care and data should be examined to ensure data is correct

  * Each SQL grouping, ordering statement should be contained on its own row and should be indented correctly as per workbench formatting:

{{ :onboarding:reportingmanager:99.png?nolink |}}

  * Never have more than 2 or 3 “group by” as this can cause significant overhead on large data sets.
  
  * Use the column name instead of column number. Using column numbers makes it harder to read and can lead to errors if the columns change.  E.g: "Group ne.parent_id" instead of "Group by 1" 


----

===== Working with Time =====

When working with time in relation to tickets it is important to specify dates in the following way:

  * When defining start and end date variables use 00:00 and 23:59 to specify the date and time ranges to include all relevant tickets - 

<code>
AND t.create_date >= "${startDate} 00:00"
AND t.create_date <= "${endDate} 23:59"
</code>

  * Ensure the time and date data returned is in an understandable format. For example the output may not need to include time in seconds or minutes but just the date and the hour
  * Example below:

<code>
DATE_FORMAT(t.create_date, '%m/%d/%Y') createdDate,
</code>

  * When converting time in mysql, there are some date functions which have a max limit on their value of output. When doing calculations such as durations, use the UNIX_TIMESTAMP() function in mySQL.

----

===== Report Writing Gotchas =====

Please consult the following page for more info. on report writing guidelines based on previous experience of reports:

[[reporting_manager:report_writing_gotchas|Report Writing Gotchas]]

----

===== Saving your work =====

Important Note: As you work with SQL queries on a frequent basis, it becomes very useful to save common queries that you use into an evernote note or similar to refer back to when you are in a hurry.

----

===== What is a NODE - **Extenet Specific** =====

When referring to a node in an SQL report the following conventions should be followed:

<code>
show_in_monitor = TRUE
network_identifier <> 'n/a' 
carrier.name <> 'ORPHANS'
technology NOT IN( 'SENSAPHONE', 'VoltServer', 'Alpha', 'Small Cell')   
ne_type IN ('Node')    
</code>

**Recommendation:**

Create a new label, to refer to the larger set of equipment, including Extension Units, UPS, Sensaphone, VoltServer, Alpha, etc. This may be something like “Total Devices”  This should be defined as follows:

<code>
show_in_monitor = TRUE
network_identifier <> 'n/a' 
carrier.name <> 'ORPHANS'
ne_type IN ('Node’, 'Extension Unit','mNET','Small Cell','UPS','WIFI')
</code>
----