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 {
 FS=","
 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:-

!/bin/sh
 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!

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.

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

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

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

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

/usr/share/doc/wordpress/examples/apache.conf
/usr/share/doc/wordpress/examples/setup-mysql.gz

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

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.

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 ;-/