• CSV to gnucash via QIF with the help of awk

    A simple task, i have a database with income information that is logged from a cash register. I have a number of columns that say the daily total, products sold, cash amount, credit card amounts etc. For accounting reasons i want to import this directly into GNU cash and avoid all manual entry. In the past i have use CSV/TXT formats but these cannot handle the split automatically.

    In GNUCash i want to put the daily total in income, and transfer as a split to the cash account and the Accounts Receivable account to handle the money owed by the credit card company. CSV/TXT import can’t handle split but QIF can.

    My example sql :-

    select DATE(DATE), NETSALE_value,PO_1_value,CASH_IN_D_value+CHEQUE_IN_D_value,CHG1_IN_D_value from fin_report_Z1 where DATE>"2017-04-5" AND DATE<"2018-04-06" INTO OUTFILE '/tmp/blah.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

    This will produce a file in /tmp called blah.txt with a number of columns. the ones i care about here are NETSALES, PaidOut, Cash+Cheque total, and Credit card total. I want to send the Cash+Cheque total to the cash account and the credit card total to the A/R account, also i want to handle any paid out by transferring to a liabilities account. Next come AWK

    BEGIN {
    print "!Account"
    print "NIncome:Sales:UK:Shop"
    print "D"
    print "TOth L"
    print "^"
    print "!Type:Oth L"
    print "D"$1
    print "T-"$2
    print "PShop End Of Day"
    print "SAssets:Accounts Receivable:FDMS Incomming"
    print "ECredit Card"
    print "$-"$5
    print "SCash"
    print "ECash"
    print "$-"$4
    if ($3!=0) {
    print "SLiabilities:PaidOut"
    print "EPaid Out"
    print "$-"$3
    print "^"

    And to invoke the entire thing:-

    rm /tmp/blah.txt
    mysql -D stock -u username -p --batch < /root/exportsales.sql awk -f /root/exportsales.awk /tmp/blah.txt > 201718.qif

    it will prompt your your mysql password then dump the finished qif in the current folder. Importing in to GNU cash is just using the qif importer, ensure the date format is correct then ensure account names match your layout and it works!

  • Reversing a heat pump protocol

    I’ve got an air source heat pump that communicates to a HMI (human machine interface) display and updates it with various status on temperatures, and what the compressors/fans etc are doing. I also have a fault with the system that every few days it trips out with an F6 fault, which is annoying. But to top that off the company that installed it have gone bankrupt so i’m pretty much on my own.

    Looking at the controller it looks like one from http://www.gzasl.com/en/page.html?id=28 but there is little info on their site and i’ve never had any success reaching out to these kinds of companies as they don’t want to deal with individuals and usually have some big organised reseller/importer network.

    Anyway back to the protocol, the HMI is fed from 2 wires, there appears to be a 12-14V differential between them, which would make sense for power, but also i can see on my DVM there is a non dc component. The voltages are floating at about 30v wrt earth so need to be a little careful connecting to them as i’ve no idea what the circuit is on the other end and if its safe to ground one side.

    Looking with a differential scope, we see :-

    Well that looks promising, there is data there.  Looks like two distinct sources as well, one pulling the voltage down, one pumping it up. The starts of the downward blocks are almost 250ms apart so this looks like a regular update message. Lets zoom in …

    What we see is 5 pulses, which may be a SOP marker. Then there are various pulses in other positions until we get back 20ms later to the 5 pulses again. This suggests to me that the 5 pulses are indeed a SOP and the data occurs after this. Start of one pulse to start of next is 400us (2.5Khz)

    Into the land of assumptions now, looking at the last pulse i’ve ever found before we get back to the next SOP marker i think there is room for 40 pulses or 5 bytes. There is room for another 5 pulses but these are always 0 (so far) and i’m assuming they are not important, this could prove to be incorrect.

    I exported the picoscope data as a CSV file and ran it through a C# chopper to detect the SOP and extract the bits and get the following results (showing raw bits and my first guess at byte decoding:-)

     SOP 01110000 0x70 00011100 0x1c 00000000 0x00 00111000 0x38 0000111 0x07 
    SOP 00000000 0x00 00011100 0x1c 00000000 0x00 00000000 0x00 0000000 0x00 
    SOP 01110000 0x70 00000000 0x00 11100111 0xe7 00000000 0x00 0000000 0x00 
    SOP 00000000 0x00 00011100 0x1c 00000111 0x07 00000001 0x01 1100000 0x60 
    SOP 00000011 0x03 10011100 0x9c 11100000 0xe0 00000001 0x01 1100000 0x60 
    SOP 00000000 0x00 00000000 0x00 00000000 0x00 00111000 0x38 0000111 0x07 
    SOP 00000011 0x03 10011100 0x9c 11100111 0xe7 00111000 0x38 0000111 0x07 
    SOP 00000000 0x00 00011100 0x1c 11100000 0xe0 00000000 0x00 0000111 0x07

    I spent some time trying to turn these in to bytes, and then see if i could make sense of any numbers. I was expecting a regular temperature update of 40-50 degrees for 2 different sensors. But nothing really seemed to look like it could be this.

    Then i spotted something odd. the 1 pulses are always in groups of 3, and it appears the 0 pulses are in groups of 2, but its not impossible my chopper has got its timing wrong, it was pretty crude. But it is clear from the scope waveforms this is encoded digital data its not a raw bit stream.

    If we assume then that a 1 is 111 and a 0 is 00 can we chop this up? Looking at above, 4th line we have 0111 this does not fit our rules, may be there is always a 0 following the SOP to provide a break, so this byte is always ignored, if we slice this line using our new rule we get 17 bytes ;-( that is not working. Allowing the rest of the bits to be processed does not help either

    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-00-0
    SOP 0111+00-00-00-0111+00-00-00- 0x38 00-00-00-111+00-00-00-0111+ 0xc7 00-00-00-
    SOP 00-00-00-00-00-0111+00-00- 0x1c 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-0
    SOP 0111+00-00-00-00-00-00-111+ 0x07 00-111+00-00-00-00-00-00- 0xc0 00-00-00-00-0
    SOP 00-00-00-00-00-0111+00-00- 0x1c 00-0111+00-00-00-0111+00-00- 0x1c 00-00-00-0
    SOP 00-00-00-111+00-111+00-111+ 0x77 00-00-00-00-00-00-111+00- 0x0e 00-00-00-00-0
    SOP 00-00-00-00-00-00-00-00- 0x00 00-00-00-00-00-111+00-00- 0x1c 00-0111+00-00-00-
    SOP 00-00-00-111+00-111+00-111+ 0x77 00-111+00-111+00-00-00-0111+ 0xc7 00-00-00-

    Above i’ve grouped into 2×0 and 3×1 then put a – for a 0 bit and a + for a 1 bit after 8 of them i decode a byte….. there are some problem sequences i can see some 00-0111+ that has broken my assumption of no of zeros and ones in a true bit… this is not the encoding you are looking for….

    Here are my picoscope captured waveforms, these can be loaded in to the picoscope application (free) from picoscope.com

    Heatpump data 1

    Heatpump data 2



  • Stuck on Pokemon Go loading screen on android [FIXED]

    So since Pokemon Go launched back in the summer there have been many reports of the app getting stuck on the loading screen on android. Many sites have reported the problem to being overloaded login servers and other bugs in the app.

    Many of you will be familiar with the following loading screen.

    Many sites have suggested clearing cache, Restarting, or even reinstalling the app. None of the solutions are working for me.

    I decided to investigate what was going wrong further. I did notice that the web browser was also getting adverts appearing randomly from time to time and clearly this is not correct. Despite some virus checkers giving the tablet the all clear I was very suspicious about a number of apps that I saw running and was not able to stop. One of these apps was called broservice, After managing to force quit and uninstall this App things started to get better for a start the web browser no longer displayed adverts and suddenly Pokémon GO was able to sign in. After some more investigation and removal of some other apps The entire tablet was more responsive and Pokémon GO continue to sign in without issue. 

    My working theory is that the malware was injecting ads directly into the HTTP traffic and this was interfering with Pokémon Go’s login. So as soon as the malware was removed everything returned to normal.

    The worst thing about this problem is that the malware was factory installed on this tablet. The tablet in question was a hipstreet electron and various apps designed to serve adverts and God knows what else were pre-installed and made very difficult to remove.

  • CanOpen

    I’m currently using CanOpen a lot and although there are loads of good documents they are scattered around the internet but no one site I find has everything i need. So this represents my own personal notes on CanOpen that I hope if of benefit to others. I cannot guarantee everything is accurate and if you find a mistake please let me know so i can correct it.

    The start of my documentation can be found here

  • Sage pay and Sage 50 shopping basket Integration

    Well you would think that this is well understood and well documented as sage are a pretty big payment gateway for credit card transactions. But it appears no, the fine details of the integration seem to be spread across multiple versions of documentation and worse than that across versions that look almost identical but have subtle changes in them. Even going through Sage’s support does not seem to shed light on these issues and all you get sent is yet another version of the documentation. Eventually you get all the pieces and manage to put everything together.

    The latest Sagepay protocol is v3.00 at the time of writing and v3.00 supports two methods to send your items to sagepay, one is a text based format with colon delimiters, the other an XML format.

    Note 1 : Sage 50 at the time of writing absolutely does not in any way imaginable support the basketXML format so don’t even bother using it. Sage 50 in fact only supports protocol v2.33 (which apart from basketXML is identical).

    Note 2: If you want to send items over so that they integrate with your stock system in Sage 50 you might be very tempted to try to use basketXML as it has a productSKU field which seems exactly what you were looking for, but as per note 1 it wont work and the correct way to do it (and it depends on where you got the documentation from if you have this little jem) is in the description field place the product SKU/unique identifier in square brackets eg [PRO001]My Product:1:1.99:0:1.99:199 .

    Note 3: To set the delivery charge you need to add a extra item to the basket text field, but ignore the documentation and do not set any unused fields to — set them to 0 instead, so if you have delivery with no tax it is Delivery:1:5.99:0:5.99:5.99 NOT Delivery:—:—:—:—:5.99 as per the instructions.

    Note 4: Delivery charges, the name of the delivery charges you pass in the basket eg “Delivery” “Carriage” needs to match the name in sage 50 in the accounts set up or the import wizard will not know what to do with it. I believe other extras can be passed across in the same way for other non stock type charges.

    Delivery charges don’t integrate correctly with sage 50, you have to set them up as a line item. Not impressed with the entire set up

  • Debian wordpress multisite woocommerse bug?

    For some unknown reason following the instructions I wrote out in the last post woocommerce broke and it looks like somehow it is getting the PLUGIN_URL wrong and using the PLUGIN_DIR instead/appended to so you end up with some horrible url requests from the woocommerce scripts eg :-

    File does not exist: /var/www/wordpress/www.example.com/plugins/var, referer: http://www.example.com/

    The easiest solution to this is to create the symlink :-

    /var/lib/wordpress/wp-content/plugins/var/lib/wordpress/wp-content -> /var/lib/wordpress/wp-content

    To fudge the problem. Its only woocommerce doing this out of all the plugins I have installed so far, so not 100% sure if its a general WordPress bug or a woocommerce bug. PITA but trivial to work around

  • WordPress debian multisite

    Before I forget, or kill some one next time I have to do this, I had better write it down.

    WordPress supports multisite out of the box these days, but Debian supported it long ago and to be honest I would rather use a Debian security team version of wordpress to get my automatic updates and security fixes along with the rest of the server security rather than having to manually mess about unpacking multiple tarballs to update multiple wordpress sites

    to install wordpress on a clean system

    apt-get install wordpress mysql-server

    Yep wordpress only recommends mysql-server so you need to add mysql-server too.

    By default the Debian magic scripts will work with data in /srv/www, I did not want this. I wanted my data under /var/www/wordpress/

    The two files


    are your friends here, the first one are some example apache confs to make the multisite magic work the second is a quick way to setup an empty wordpress site and to create the mysql user, the config and the folder structure needed.

    Before you do that, enable some apache modules to let the magic work, you need vhost_alias and rewrite as a minimum

    a2enmod vhost_alias rewrite

    Next copy the setup-mysql script, unpack it and mod it to the required folder structure :-

    cd ~
    cp /usr/share/doc/wordpress/examples/setup-mysql.gz ~/
    gzip -d setup-mysql.gz
    sed -i 's/\/srv\/www\//\/var\/www\/wordpress\//g' setup-mysql
    chmod +x setup-mysql
    ./setup-mysql -n databaseuser name.of.site.com

    where databaseuser is the name you want to use for mysql access (account will be created and all set up) and name.of.site.com will be the the physical name used in the /var/www/wordpress/ folder to keep the sites separate.

    Now the final thing to do is to configure apache

    What you probably want to do is create a new default virtual host that will grab any requests and handle them BUT do this as a lower priority to any static sites you may have. So create a new file in /etc/apache2/sites-available/50-wordpress with the following content :-

    ## Virtual host VirtualDocumentRoot
            NameVirtualHost *:80
            UseCanonicalName Off
            VirtualDocumentRoot /usr/share/wordpress
            Options All
            # wp-content in /srv/www/wp-content/$0
            RewriteEngine On
            RewriteRule ^/wp-content/(.*)$ /var/www/wordpress/%{HTTP_HOST}/$1

    Which is pretty much the first example in /usr/share/doc/wordpress/examples/apache.conf with my folder locations tweaked as I like them.

    then enable the site

    a2ensite 50-wordpress

    You will with this setup also want to remove the default

     a2dissite default 

    Then if you need to add any static sites in the future add them as regular vhosts with site names starting with numbers less that 50- then they will take priority. If no static matches are found it will try to dynamicly match via the wordpress configuration. If this fails you will get a ungraceful message from the debian wp-config.php file but I’m not really bothered about that at this stage.

    and finally don’t forget to restart the web server

    service apache2 restart
  • Finally have a site again

    It was the 17th Aug 2012 when I realised my new version of this website had vanished from the internet. I guess it serves me right for trying to use a free webhost when I have a perfectly good Virtual server I should have been using.

    I tried to use 000webhost and install a word press site there. And all seemed good, But a week or so later the site was gone, my account blocked and the only thing I could find out was I used too many resources on the server. It was only a fucking wordpress site, what do they expect people to host, one single static html page that says Hello World?. What annoyed me the most though was there were no warnings, no emails nothing. They just yanked the site and then held you ransom to upgrade to premium to get it back.

    Anyway that badness is a long time in the past, but i’ve been so busy I’ve only now found the time to really do anything about it and fix the site. I can’t really believe it was that long ago but thats the date on my twitter posts venting my anger.

    So here back again is another wordpress site. Somewhere to put posts on various little projects that I am working on and hopefully I can start posting a new open source project that I am working on here very soon.

  • September Hours

    Now the “crazyness” is behind i’ve been going back and filling out my time sheets. My september hours looked like this


    So thats only 271.h hours for September worked ;-/