Syntax Highlighting for Blogger

For the previous posts that included code, I wanted to have proper syntax highlighting for them. I tried two different libraries and ended up settling on highlight.js.

The first one I tried was SyntaxHighlighter for which I had found instructions here. So I started with the following:

      <link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>
      <link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeEclipse.css' rel='stylesheet' type='text/css'/>
      <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>
      <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
      <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
      <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>
      <script language='javascript' type='text/javascript'>
      SyntaxHighlighter.config.bloggerMode = true;
      SyntaxHighlighter.all();
      </script>

But there was a problem. Notice all of the sources are referencing http? Blogger blogs support https and browsers will start flagging for mixed content. I thought I could resolve this by switching the references to https like so:

      <link href='https://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>
      <link href='https://alexgorbatchev.com/pub/sh/current/styles/shThemeEclipse.css' rel='stylesheet' type='text/css'/>
      <script src='https://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>
      <script src='https://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
      <script src='https://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
      <script src='https://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>
      <script language='javascript' type='text/javascript'>
      SyntaxHighlighter.config.bloggerMode = true;
      SyntaxHighlighter.all();
      </script>

Which still brings up a mixed content warning. Digging deeper revealed that the library makes calls to un-encrypted links. Time to try another library! A little searching for other libraries yielded this post. Next to try was highlight.js, following these instructions. Inserted the following lines:

    <link href='//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/default.min.css' rel='stylesheet'/>
    <script src='//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js'/>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/languages/sql.min.js'/>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/languages/tcl.min.js'/>
    <script>hljs.initHighlightingOnLoad();</script>

Success!!
I've since added a line for html to format this post.

One final catch. Since I am showing HTML in this post, all of the > and < tags need to be replaced with their html entites &gt; and &lt; respectively. You could do this manually with your favourite editor, or run your snippets through this page.

Building queries

For a year end report, some data needs to gathered from some tables and calculations made.

First run, get the data that needs to be summarised (This query returned 929 rows):

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` )
FROM `truckdumps`
WHERE `truckdumps`.`Date` >= '2017-10-29'
AND `truckdumps`.`Date` <= '2018-11-03'
GROUP BY `Route` , `item`

Because of the way costing is done for this internally, we have to group our data by week with the week ending on Saturdays (For this period, this query returned 5284 rows)

SELECT `truckdumps`.`Route`, `truckdumps`.`item`, sum(`truckdumps`.`qty`), DATE_ADD(`truckdumps`.`Date`, INTERVAL (7 - DAYOFWEEK(`truckdumps`.`Date`)) day) as WeekEndDay 
FROM `truckdumps` 
WHERE`truckdumps`.`Date` >= '2017-10-29' and `truckdumps`.`Date` <= '2018-11-03'
GROUP by `Route`, `item`, WeekEndDay

Now, to attach the pricing. (Which also returns 5284 rows)

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` ) , DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) AS WeekEndDay, `pricing`.`price`
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route` , `item` , WeekEndDay, `price`

Next show some totals(again, 5284 rows) :

SELECT `truckdumps`.`Route` , `truckdumps`.`item` , sum( `truckdumps`.`qty` ) , `pricing`.`price` , DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) AS WeekEndDay, sum( `truckdumps`.`qty` * `pricing`.`price` ) as value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route` , `item` , WeekEndDay, `price`

Totals by Route (Returned 18 rows):

SELECT `truckdumps`.`Route` , sum( `truckdumps`.`qty` * `pricing`.`price` ) AS value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'
GROUP BY `Route`

And one final Grand Total:

SELECT sum( `truckdumps`.`qty` * `pricing`.`price` ) AS value
FROM `truckdumps`
LEFT JOIN `pricing` ON ( DATE_ADD( `truckdumps`.`Date` , INTERVAL( 7 - DAYOFWEEK( `truckdumps`.`Date` ) )
DAY ) = `pricing`.`END`
AND `truckdumps`.`item` = `pricing`.`PRODNO` )
WHERE `Date` >= '2017-10-29'
AND `Date` <= '2018-11-03'