Let's get a proper domain for this!

I created this blog to document some of the stuff I do (mostly as a reminder to myself but if it helps someone else🤷). For some work I may be doing in the future, I thought it would be helpful to actually go through the process rather than just be aware of it on a high level.

Step one, register an account at the domain registrar of your choice. I've heard too many stories of people not completing the purchase of their domain only finding out later that it's been purchased in the mean time and parked. (With the added bonus of costing significantly more.) I chose to use https://www.namecheap.com and as part of the registration process, I chose to pre-load my account with some funds.

Step two, register the domain. Now I am wishing I had taken screen shots of the process. It was pretty straight forward to go through the process. I did elect to get the "free" email service using https://privateemail.com which is also a namecheap service.

It is a free trial for two months but only costs $10 a year. For years, I was paying more to Yahoo! for the privilege of being able to POP email. When you registered for the Private Email service, you would have been sent an email with the username and password for the email account. So the first thing to do is change the password to your new email address. Log in with your provided credentials.

Click the hamburger:

 Then settings:

Scroll down until you find change password:

And finally change your password. You'll have to log back in at this point if you want to explore any of the other settings.

I also chose to use the free WhoisGuard which is a whois anonymizer and also a namecheap service.

If you are not familiar with what a whois anonymizer is and why one would need to use the service, read on, otherwise skip below. In the early days of the internet, whois was a service that allowed one to retrieve contact information about a domain for such things as who the real owner of the domain is(in the past, a way to tell if a site is legitimate), report misbehaving services or other abuse taking place at the site. The whois command will typically come installed on any Linux system as part of setting up the network services. It is typically available as a webservice by many domain registrars and some third parties as well. For most domains, I typically use the webservice provided by ICANN at https://whois.icann.org. For some top level domains, the webservice is only available from a resellers of those domains. Some bad actors realized they could harvest email addresses by abusing the domain registry service and sending spam. Anonymizers help to protect your information and will filter spam but forward legitimate inquires to the correct parties. whois entries for protected domains will typically look like this:

Next step, setup gmail to send and receive email from privateemail.com:
In gmail, hit that gear in the top right corner

Click the gear

Then settings

Then select Accounts and Import

Scroll down until you find Check email from other accounts, then click on Add an email account:

  Enter your username and password for privatemail.com as well as change the port number and to always use an SSL connection. namespace provides details for what ports to use for Private Email here.

Next scroll down to add an email address to send email from:

There will be two dialog boxes this time. The first you enter your name and the new email address.

Google auto-guessed the STMP server to be mx2.privateemail.com and the Username as ash. These both had to be changed to mail.privateemail.com and the full email address. Either tls or ssl should work but they both use different ports. Again, refer to the reference provided by namespace here.

 With the email sorted out, it's now time to point the domain to the blog. We'll be switching back and forth between blogger.com and namecheap.com while we do this. namecheap will park your domain for you and this will have to change the cname records. We'll start with blogger.com since they give you instructions on what to change the cname records to. Since I've already gone through this with this blog, I have an older, unused blog through blogger that I'll walk through here.
In the blogger dashboard, go to settings.

Next, find the section for publish and click +Set up a third-party URL for your blog.

Next, add what address the blog should appear at ( something like blog.yourdomain.tld)

Next, google will tell you to create some cname records.

Back to namecheap... Log in and from the dashboard, click manage for your domain.

If you just leave the default settings when you register the domain, domain parking will be turned on. Scroll down to the bottom of the page and make sure domain parking is turned off.

Next, go back to the top of the page and click on Advanced DNS

Edit those cname records. If you had to turn off domain parking, there will a cname record pointing to the parking website. That can be deleted or edited to point to the google domain as above.

Now we need to add the entry per google's instructions. Click on add record

Make it a cname record in the type dropdown that appears next,

You should be left with three records.

 There is one more record that should be added. This allows you to turn on https on blogger. It's not really required since the information published here is meant to be public anyways, however being proactive now prevents an oversight later when it might become more meaningful.
This time, when adding a new record, select a CAA record. Enter the domain, and use letsencrypt.org as the certificate issuer.

We can now go back to google and turn on https

Finally done. What was supposed to be a relatively short post about my first experience buying and setting up a domain some how morphed into a step by step process with images where I could reproduce them.

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'

Some tcl stuff

At work, there was an expect script that, on a daily basis, ran a job that created some files for handheld computers used by our merchandisers. The script used a static date to create those files. As a result, any changes such as new customers made after that static date would not be included in the files.

In 2014, I modified the script to calculate the date instead. The nature of the job required using Sunday's date to ensure as much information as possible was included for the handheld computers.

I stumbled across a file containing some of the middle steps I took to modify the script calculate the correct date to use.

x=091509
y=$(printf 'puts [clock format [clock add [clock scan "%s" -format "%%m%%d%%y"] -1 day] -format "%%Y%%m%%d"]' "091509" | tclsh)
puts [clock format [clock add [clock seconds] -1 day] -format %Y%m%d]

set current_date [clock seconds]
set day_of_week [clock format $current_date -format %w]
set tmp "${day_of_week} days"
set relative_time  [clock scan "-${day_of_week} days" ]
set sunday [clock format [expr $current_date - $relative_time] -format %m%d%y]

puts [clock format [clock scan "-[clock format [clock seconds] -format %w] days" ] -format %m%d%y]

and the final code that went into the file.

set sunday [clock format [clock scan "-[clock format [clock seconds] -format %w] days" ] -format %m%d%y]
send -- "${sunday}\r"

Time being what it is, I really don't remember how this works. I did a lot of googling about tcl at the time. This script was effectively retired two years ago.

When Crystal Reports isn't quite good enough.

Queries were written for Sage AccPac 5.5A for a third party audit of HST recovery.
The first query, APVNDLT1, extracted a list of vendors. The second query, apchkreg, extracted a listing of cheque made to those vendors along with which invoice(s) applied to each cheque. The remainder of the queries were various iterations of extracting data from the posting journal. Starting off with just the posting journal, then adding accounts from the g/l.

These were written/modified 12/2018 with the last query modified 01/2019 to distinguish between credits and invoices. There were reports available from within accpac to get most of the below data, but the reports "Excel" export of the reports end up as a heavily formatted spreadsheet that is supposed to look like the printed report making it very difficult to do any practical analysis of the actual data. In addition, the reports took quite a long time to run and would fail if the dataset was large. The end report required data spanning four years.

-- APVNDLT1

SELECT "APVEN"."VENDORID", "APVEN"."VENDNAME", "APVEN"."TEXTSTRE1", "APVEN"."TEXTSTRE2", "APVEN"."TEXTSTRE3", "APVEN"."TEXTSTRE4", "APVEN"."NAMECITY", "APVEN"."CODESTTE", "APVEN"."DATELASTMN", "APVEN"."DATEINAC", "APVEN"."SWHOLD", "APVEN"."SHORTNAME", "APVEN"."NAMECTAC", "APVEN"."TEXTPHON1", "APVEN"."TEXTPHON2", "APVEN"."IDGRP", "APVEN"."DATESTART", "APVEN"."CURNCODE", "APVEN"."AMTCRLIMT", "APVEN"."DUPAMTCODE", "APVEN"."CODECHECK", "APVEN"."TAXRPTSW", "APVEN"."TAXNBR", "APVEN"."TAXIDTYPE", "APVEN"."CLASID", "APVEN"."IDACCTSET", "APVEN"."SWDISTBY", "APVEN"."TERMSCODE", "APVEN"."PRIMRMIT", "APVEN"."BANKID", "APVEN"."CODETAXGRP", "APVEN"."CODEPSTL", "APVEN"."CODECTRY", "APVEN"."TAXCLASS1", "APVEN"."TAXCLASS2", "APVEN"."TAXCLASS3", "APVEN"."TAXCLASS4", "APVEN"."TAXCLASS5", "TXGRP"."AUTHORITY2", "TXGRP"."AUTHORITY3", "TXGRP"."AUTHORITY4", "TXGRP"."AUTHORITY5", "TXGRP"."AUTHORITY1", "APVEN"."IDTAXREGI1", "APVEN"."IDTAXREGI2", "APVEN"."IDTAXREGI3", "APVEN"."IDTAXREGI4", "APVEN"."IDTAXREGI5", "APVEN"."SWTXINC1", "APVEN"."SWTXINC2", "APVEN"."SWTXINC3", "APVEN"."SWTXINC4", "APVEN"."SWTXINC5", "APVEN"."SWACTV", "APVEN"."DUPDATECD", "APVEN"."RATETYPE", "APVEN"."PRTSEPCHKS", "APVCM"."VENDORID", "APVEN"."EMAIL2", "APVEN"."EMAIL1", "APVEN"."WEBSITE", "CSCCD"."DECIMALS", "APVEN"."CTACPHONE", "APVEN"."CTACFAX", "APVEN"."DELMETHOD", "CSCOM"."HOMECUR", "APVEN"."DISTSETID", "APVEN"."DISTCODE", "APVEN"."GLACCNT", "APVEN"."RTGPERCENT", "APVEN"."RTGDAYS", "APVEN"."RTGTERMS", "APP03"."SWRTG", "APVEN"."VALUES", "APSLVEN"."SELSEQ", "TXGRP"."TTYPE", "APVEN"."PAYMCODE", "APSLVEN"."RECORDNO"
FROM  ((((("APSLVEN" "APSLVEN" INNER JOIN "APVEN" "APVEN" ON "APSLVEN"."IDVEND"="APVEN"."VENDORID") INNER JOIN "TXGRP" "TXGRP" ON "APVEN"."CODETAXGRP"="TXGRP"."GROUPID") INNER JOIN "CSCOM" "CSCOM" ON "APVEN"."AUDTORG"="CSCOM"."ORGID") INNER JOIN "APP03" "APP03" ON "APVEN"."AUDTORG"="APP03"."AUDTORG") LEFT OUTER JOIN "APVCM" "APVCM" ON "APVEN"."VENDORID"="APVCM"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APVEN"."CURNCODE"="CSCCD"."CURID"
WHERE "TXGRP"."TTYPE"=2

The second query, apchkreg, extracted a listing of cheque made to those vendors along with which invoice(s) applied to each cheque.

-- apchkreg

SELECT "APVEN"."VENDNAME", "CSCCD"."DECIMALS", "APPYM"."IDBANK", "APPYM"."CODECURN", "APPYM"."AMTPAYM", "CSCCD_APPYM"."DECIMALS", "APPYM"."IDRMIT", "APPJH"."POSTSEQNCE", "APPJH"."SWNONRCVBL", "APPJH"."MISCRMITTO", "APPJD"."IDINVC", "APPJD"."CNTPAYM", "APPJD"."AMTDSCTCUR", "APPJD"."AMTADJTCUR", "APPJD"."AMTEXTNDTC", "APPJD"."AMTDSCHCUR", "APPJD"."AMTADJHCUR", "APPJD"."AMTEXTNDHC", "APPJH"."SWSTATUS", "APPJD"."CODECURN", "APPJH"."TYPEBTCH", "APPJD"."ACCTTYPE", "APPJD"."IDTRANS", "APPJH"."IDVEND", "APPJD"."TRANSTYPE", "APPJD"."DATEDISC", "APPJD"."DATEDUE", "APPJH"."IDRMIT", "APPJH"."LONGSERIAL", "APPJH"."DATEINVC", "APPJH"."PAYMTYPE"
FROM  (((("APPJH" "APPJH" INNER JOIN "APPJD" "APPJD" ON ((("APPJH"."TYPEBTCH"="APPJD"."TYPEBTCH") AND ("APPJH"."POSTSEQNCE"="APPJD"."POSTSEQNCE")) AND ("APPJH"."CNTBTCH"="APPJD"."CNTBTCH")) AND ("APPJH"."CNTITEM"="APPJD"."CNTITEM")) INNER JOIN "APPYM" "APPYM" ON ((("APPJH"."IDBANK"="APPYM"."IDBANK") AND ("APPJH"."IDVEND"="APPYM"."IDVEND")) AND ("APPJH"."IDRMIT"="APPYM"."IDRMIT")) AND ("APPJH"."LONGSERIAL"="APPYM"."LONGSERIAL")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "CSCCD" "CSCCD_APPYM" ON "APPYM"."CODECURN"="CSCCD_APPYM"."CURID"
WHERE  "APPJH"."TYPEBTCH"='PY' AND "APPJH"."PAYMTYPE"=2 AND ("APPJH"."POSTSEQNCE">=10435 AND "APPJH"."POSTSEQNCE"<=10520) AND "APPJD"."ACCTTYPE"=1 AND ("APPJD"."IDTRANS"=51 OR "APPJD"."IDTRANS"=57)
ORDER BY IDVEND ASC

This is where things begin to get interesting. First is the posting journal

-- APIPJ01
SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH", "APPJH"."POSTSEQNCE", "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS"
FROM (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350)
ORDER BY "APPJS"."POSTSEQNCE"

Then we had to add information from the GL. Start with the posting journal query and the GL query.

-- gl detail and apipj01

 ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC"
 FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD"
 WHERE  "APPJD"."TYPEBTCH"='IN' AND "APPJD"."POSTSEQNCE"=6317 AND "APPJD"."CNTBTCH"=6337 AND "APPJD"."CNTITEM"=1 ) as GLDTL

 ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH", "APPJH"."POSTSEQNCE", "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS"
 FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
 WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01

Remove unnessasary fields and combine the two queries.

-- Fields removed from below SELECT - , "GLDTL"."DECIMALS", "GLDTL"."TRANSTYPE", "GLDTL"."TYPEBTCH", "GLDTL"."CNTITEM", "GLDTL"."CNTSEQENCE", "GLDTL"."OPTFIELD", "GLDTL"."VALUES", "GLDTL"."VALUE", "GLDTL"."TYPE", "GLDTL"."GLREF", "GLDTL"."GLDESC"
SELECT "APIPJ01"."DATEBUS", "APIPJ01"."IDVEND", "APIPJ01"."POSTSEQNCE", "APIPJ01"."VENDNAME", "APIPJ01"."CNTBTCH", "APIPJ01"."CNTITEM", "APIPJ01"."DATEINVC", "APIPJ01"."FISCYR", "APIPJ01"."FISCPER", "APIPJ01"."IDINVC", "APIPJ01"."TRANSTYPE", "APIPJ01"."PGMVER", "APIPJ01"."CODETAXGRP", "APIPJ01"."CODECURNTC", "APIPJ01"."CODETERM", "APIPJ01"."RATEEXCHTC", "APIPJ01"."DATEDUE", "APIPJ01"."RATEDATETC", "APIPJ01"."RTGTERMS", "APIPJ01"."SWRTG", "APIPJ01"."SWJOB", "APIPJ01"."SWRTGRATE", "APIPJ01"."AMTINVCTC", "APIPJ01"."RTGAMTTC", "APIPJ01"."RTGAPPLYTO", "APIPJ01"."IDINVCAPPL", "APIPJ01"."IDBANK", "APIPJ01"."IDRMIT", "APIPJ01"."TYPEBTCH", "APIPJ01"."DECIMALS", "APIPJ01"."RTGDATEDUE", "APIPJ01"."VALUES", "APIPJ01"."SWPRINTED", "APIPJ01"."DESC", "APIPJ01"."RATETYPETC", "APIPJ01"."IDACCTSET", "APIPJ01"."APPJH_DATEBUS", "GLDTL"."IDDIST", "GLDTL"."IDACCT", "GLDTL"."ACCTDESC", "GLDTL"."AMTEXTNDTC", "GLDTL"."AMTTAXTC", "GLDTL"."AMTEXTNDHC", "GLDTL"."AMTTAXHC"
FROM
  ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH" AS APPJH_TYPEBTCH, "APPJH"."POSTSEQNCE" AS APPJH_POSTSEQNCE, "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS" AS APPJH_DATEBUS
    FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
    WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01
  LEFT JOIN
  ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC"
    FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD") as GLDTL
  ON "APIPJ01"."TYPEBTCH" = "GLDTL"."TYPEBTCH" AND "APIPJ01"."POSTSEQNCE" = "GLDTL"."POSTSEQNCE" AND "APIPJ01"."CNTBTCH" = "GLDTL"."CNTBTCH" AND "APIPJ01"."CNTITEM" = "GLDTL"."CNTITEM"
ORDER BY "APIPJ01"."POSTSEQNCE", "APIPJ01"."IDVEND"

Now we have all of the records. However, there is no distinction between invoices and credits. Added one field to tie everything together.

-- Fields removed from below SELECT - , "GLDTL"."DECIMALS", "GLDTL"."TRANSTYPE", "GLDTL"."TYPEBTCH", "GLDTL"."CNTITEM", "GLDTL"."CNTSEQENCE", "GLDTL"."OPTFIELD", "GLDTL"."VALUES", "GLDTL"."VALUE", "GLDTL"."TYPE", "GLDTL"."GLREF", "GLDTL"."GLDESC"
-- Added srcetype appjd.srcetype - gldtl.srcetype
SELECT "APIPJ01"."DATEBUS", "APIPJ01"."IDVEND", "APIPJ01"."POSTSEQNCE", "APIPJ01"."VENDNAME", "APIPJ01"."CNTBTCH", "APIPJ01"."CNTITEM", "APIPJ01"."DATEINVC", "APIPJ01"."FISCYR", "APIPJ01"."FISCPER", "APIPJ01"."IDINVC", "APIPJ01"."TRANSTYPE", "APIPJ01"."PGMVER", "APIPJ01"."CODETAXGRP", "APIPJ01"."CODECURNTC", "APIPJ01"."CODETERM", "APIPJ01"."RATEEXCHTC", "APIPJ01"."DATEDUE", "APIPJ01"."RATEDATETC", "APIPJ01"."RTGTERMS", "APIPJ01"."SWRTG", "APIPJ01"."SWJOB", "APIPJ01"."SWRTGRATE", "APIPJ01"."AMTINVCTC", "APIPJ01"."RTGAMTTC", "APIPJ01"."RTGAPPLYTO", "APIPJ01"."IDINVCAPPL", "APIPJ01"."IDBANK", "APIPJ01"."IDRMIT", "APIPJ01"."TYPEBTCH", "APIPJ01"."DECIMALS", "APIPJ01"."RTGDATEDUE", "APIPJ01"."VALUES", "APIPJ01"."SWPRINTED", "APIPJ01"."DESC", "APIPJ01"."RATETYPETC", "APIPJ01"."IDACCTSET", "APIPJ01"."APPJH_DATEBUS", "GLDTL"."IDDIST", "GLDTL"."IDACCT", "GLDTL"."ACCTDESC", "GLDTL"."AMTEXTNDTC", "GLDTL"."AMTTAXTC", "GLDTL"."AMTEXTNDHC", "GLDTL"."AMTTAXHC", "GLDTL"."SRCETYPE"
FROM
  ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH" AS APPJH_TYPEBTCH, "APPJH"."POSTSEQNCE" AS APPJH_POSTSEQNCE, "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS" AS APPJH_DATEBUS
   FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
   WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01
 LEFT JOIN
  ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC", "APPJD"."SRCETYPE"
   FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD") as GLDTL
 ON "APIPJ01"."TYPEBTCH" = "GLDTL"."TYPEBTCH" AND "APIPJ01"."POSTSEQNCE" = "GLDTL"."POSTSEQNCE" AND "APIPJ01"."CNTBTCH" = "GLDTL"."CNTBTCH" AND "APIPJ01"."CNTITEM" = "GLDTL"."CNTITEM"
ORDER BY "APIPJ01"."POSTSEQNCE", "APIPJ01"."IDVEND"

I made a couple of attempts extracting and exporting the data using Sql Server Management Studio Express on the server hosting they MSSQL backend for AccPac. There were several issues with getting the data in a usable form. I ended up connecting to the server in Excel 2016 and using the queries as part of the setup to extract the data directly into an xlsx spreadsheet.

PDOException “could not find driver”

ashley@shop1:/etc$ sudo a2enmod php7.0
Considering dependency mpm_prefork for php7.0:
Considering conflict mpm_event for mpm_prefork:
Considering conflict mpm_worker for mpm_prefork:
Module mpm_prefork already enabled
Considering conflict php5 for php7.0:
Enabling module php7.0.
To activate the new configuration, you need to run:
  systemctl restart apache2
ashley@shop1:/etc$ systemctl restart apache2
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to restart 'apache2.service'.
Authenticating as: ashley,,, (ashley)
Password:
==== AUTHENTICATION COMPLETE ===
ashley@shop1:/etc$


Serveral months ago, I performed a distribution upgrade to my debian server at home.
It hosted an RSS aggregator (Tiny Tiny RSS) that I started using after Google dropped Reader.
After the upgrade, I got a PDO Exception error using the web interface for the aggregator. I did some searching at the time but had intended to move the aggregator to a Raspberry-Pi anyways. I left the MySQL (now MariaDB after the distribution upgrade) on the original host and everything worked fine.

Monday, I had need to create an invoice for some work I did for a client. I don't do a lot of side-gig at the moment so I hadn't used the invoicing (Simple Invoices) software for a couple of years. Low and behold, I was getting the same error. This time warranted a little extra effort.
Visited php - PDOException “could not find driver” - Stack Overflow

and invoicing system


Search
Visited How to Switch between Multiple PHP Version on Ubuntu – TecAdmin
7:59 am
•
Details


Search
Visited apt - How to remove php 5.6 - Ask Ubuntu
7:51 am
•
Details

Search
Visited 14.04 - How do I remove PHP 7 Completely? - Ask Ubuntu
7:51 am
•
Details

Search
Visited How to migrate PHP Code between versions 5 - 7 without rewriting ...
7:50 am
•
Details

Search
Searched for how to migrate from php 5 to php 7
7:49 am
•
Details
•
Location info

Search
Visited How to Check PHP Version: 3 Steps (with Pictures) - wikiHow
7:44 am
•
Details


Search
Searched for which version of php do i have
7:44 am
•
Details
•
Location info

Search
Visited PHP: Migrating from PHP 5.6.x to PHP 7.0.x - Manual
7:44 am
•
Details

Search
Visited php - PDOException “could not find driver” - Stack Overflow
7:42 am
•
Details

Search
Searched for php could not find driver
7:42 am
•
Details
•
Location info

Search
Visited The Simple Invoices Open Source Project on Open Hub
7:34 am
•
Details

ATTiny Low Power Prank

I read this last year near Christmas and knew I would have to build one!

It was several months before I found the project details from the original creator on hackaday.io

Note to self: When it takes a while to find a link to something, copy the link and don't just assume you can find it readily again for the 2nd (or 3rd or 4th time.... though I'm pretty sure I'm up to five at the time of this writing...)

I bought similar material opting for a coin cell holder for the battery, an SMT variant of the ATTiny, and a different form factor for the piezo speaker. Going with the SMT variant necessitated purchasing a socket in order to program the device.


RickRoll Troll
Inspiration (Hackaday)
Implementation (Hackaday.io)

"The Song that Never Ends"
but it's really "The Song that Doesn't End"
https://musescore.com/user/1447551/scores/617656
https://www.youtube.com/watch?v=9ffL573XI50

Multiple devices that communicate with each other and play off of each other (possibly esp8266?)