RobinC, Author at ByteMe

CSV to gnucash via QIF with the help of awk

January 11, 2019 Author: RobinC
No comment yet
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!

MySQL/MariaDB to excel

March 26, 2018 Author: RobinC
No comment yet
Sometimes you just need to export some data and have it ready in a spreadsheet to email to sales and marketing. Yes i know a csv is a better storage format but the objective is to send the data in excel format so that people use to *just* using excel can carry on doing their work.

Turns out this is really easy to do on a linux server.

First create some sql query, for example pulling sales data from a sales table, and using the stock_details table to get its name where the data is a specific brand.. It does not matter really its going to return a fixed no of columns based on the query, lets call this query.sql
```
use stock;
select sales.barcode,stock_name,delta from sales,stock_details where sales.barcode=stock_details.barcode AND stock_details.brandid=1;
```
Next we have a little script to do the magic (getsales.sh) :-
```
#!/bin.sh
dir=$(mktemp -d)
mysql -u ROUSER --batch < query.sql > "$dir/sales.txt"
tr '\t' ',' < $dir/sales.txt > $dir/sales.csv
libreoffice --headless --convert-to xls:"MS Excel 97" $dir/sales.csv --outdir $dir
echo "sales report" | mutt -a "$dir/sales.xls" -s "Sales report" -- sales@example.com
```
This has a number of steps. Firstly run the mysql query in “batch” mode this dumps the results as tab separated values. Next libreoffice choked on tab separated so we use tr to translate to comma separated, may be an infilter option would cure this. Then we feed the comma separated into libreoffice in “headless” mode and this generates the Excel file for us. Finally mutt is used to write an email with the attachment.

One thing to note, the msql user in this example ROUSER must have prompt-less READ ONLY access to the required database/table(s) By prompt-less i mean that the password is not required to be entered each time. To achieve this create a file ~/.my.cfg with the following contents
```
[client]
password=thepassword
```
where thepassword is the actual password for ROUSER. Remember to set permissions on this file to be read/write only to the user. Also remember to make the user in the database only have read only access to the things it needs for additional safety.

Now stick that in a cron job and bingo, automatic spreadsheets via email!
Reversing a heat pump protocol

March 9, 2018 Author: RobinC
No comment yet
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
Simple (c#) message broker using Nanomsg

May 1, 2017 Author: RobinC
No comment yet
I was attempting to do some CanOpen development using CanOpenNode in a 100% windows simulated environment where different apps all pretend to be different nodes and talk to each other via a simulated bus. Now message busses in software are not uncommon things but many are quite heavy going and for the magnitude of the product a think like DBUS was just not needed. Enter Nanomsg “nanomsg is a socket library that provides several common communication patterns. It aims to make the networking layer fast, scalable, and easy to use. Implemented in C, it works on a wide range of operating systems with no further dependencies.” It also has support for a BUS type socket so seemed awesome on the surface.

First problem is the exact implementation of the bus socket is not exactly the same as one would expect when creating an electrical bus. Its simply not possible to just wire all the test apps together and have them talking with out some kind of extra glue in the middle. With nanomsg it is necessary to manually connect every node to every other node to form the bus. Some info is given here but the implementation detail is missed and stumped me for a while.

If we consider 3 apps all trying to form a bus via IPC then we have to do the following to make the system work :-

App 1
s.Bind("ipc://can_id1");
App 2
s1.Bind("ipc://can_id2"); s2.Connect("ipc://can_id1");
App 3
s1.Connect("ipc://can_id1"); s2.Connect("ipc://can_id2");

App1 has nothing to connect to so we create a socket with bind, App2 Can connect to app1 but also creates a socket. App3 just binds to app1 and app2’s socket. Nanomsg will take care of the rest and ensure that no matter who broadcasts all nodes receive. Clearly this is only suitable for a fixed implementation where number of nodes and node locations is hard coded. Not really what i had in mind. Enter the message broker. Now i’m not claiming this is efficient or the most elegant way to solve this problem, but its simple and flexible which are the key goals here for the testing i wish to do.

The idea with the simple message broker is to open a number of sockets (in the example 10) then each app will just connect to the appropriate socket. We can decide which one to connect to as i’m simulating a canopen node bus so each node/app has an ID from 1-127 so that makes a perfect sufix to use on the IPC eg can_id1 can_id2 etc.. If we wanted to be more elegant and remove hardcoded things to the next level we could add an interface to the broker that can be contacted by a new node that allocates a new socket on demand and returns its id. But this is good enough for the my requirements… The broker will listen to each of the multiple sockets then rebroadcast anything it gets to all OTHER sockets. As the sockets are all bi-directional it works as expected for a bus topology
```
using System;
using System.Threading.Tasks;
using NNanomsg;
using NNanomsg.Protocols;


namespace MessageBroker
{
    class Program
    {
        const int nosockets = 10;
        static BusSocket[] s = new BusSocket[nosockets];
        static NanomsgListener[] l = new NanomsgListener[nosockets];

        static void Main(string[] args)
        {
            Console.WriteLine("Starting message broker");

            for(int x=0;x< nosockets;x++) { s[x] = new BusSocket(); s[x].Bind(string.Format("ipc://can_id{0}",x)); l[x] = new NanomsgListener(); l[x].AddSocket(s[x]); l[x].ReceivedMessage += Listener_ReceivedMessage; } while (true) { Parallel.ForEach(l, (lx) =>
                 {
                     lx.Listen(new TimeSpan(0));
                 });
            }    
        }

        private static void Listener_ReceivedMessage(int socketID)
        {
            //New data on socket
            byte[] payload = s[socketID].ReceiveImmediate();

            //Send new data to everyone other than the sender
            Parallel.ForEach(s, (sx) =>
            {
                if (sx.SocketID == socketID)
                    return;
                sx.Send(payload);
            });         

        }

    }
}
```
Stuck on Pokemon Go loading screen on android [FIXED]

November 13, 2016 Author: RobinC
No comment yet

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.
Mains frequency monitoring

November 1, 2016 Author: RobinC
No comment yet

Playing around with measuring the UK mains frequency and got the following results

where blue is my measurement and yellow is a reference measurement i obtained from a real time tracker on the internet. Pretty good agreement with in 0.01 of a Hz (mostly)

What i’m really interested in are the variations between blue and yellow, they are not constant and at times look cyclical such as

What am i seeing there? is that the real time frequency regulation of the mains kicking in and out on 10-20 second duty cycles? as generating capacity is added and removed to regulate the frequency? Or am i detecting local distortions that are offsetting my zero crossing detection and causing error in my frequency graphs?
Fake FTDI and esp8266 flashing issues

October 7, 2016 Author: RobinC
No comment yet

So I thought I would try the ESP8266 out at last so got my self a edp8266 module and a USB to 3.3v serial converter.

After many hours of failing to flash the esp with nodemcu and other firmwares I was on the brink of giving up.The nodemcu flash tool kept letting stuck at anywhere between 1% and 6% no settings made any difference and other flash tools failed in similar ways. I then though to change the serial adapter and happen to have a board with a cp2102 on it so a few soldered on wires later and I had an alternative serial converter. This one worked first time.

I remembered the whole fake FTDI issue from a while back and started investigating. Firstly my chip had painted on idents which would easily come off with my finger. Real FTDI chips apparently are laser etched. I then downloaded and ran FT_Prog from ftdi’s website and checked the serial no… Turns out it was the most common fake number there was
Another stunning success

September 13, 2016 Author: RobinC
No comment yet
Another one bites the dust

September 12, 2016 Author: RobinC
No comment yet

Don’t think the puck diode thought a lot of the current I stuck though it. Plus it looks badly clamped as conduction clearly started and burned out at a single point from the edge

First test of the RaspiHats Isolated IO module

September 8, 2016 Author: RobinC

No comment yet

First tests of the Isolated IO boards from http://raspihats.com/ This is the 6 in/out module featuring 6 relay isolated outputs and 6 opto isolated inputs. 3 Boards are currently being produced. A relay output board, an isolated input board and the mixed board shown here.

I’ve connected it here to a system under test to simulate a PLC interface for verification. The Raspberry PI is pretending to do the PLC handshake with our equipment to verify its all working to specification.

Taking to the board from the Raspberry PI is exceeding easy, for a start its a HAT module so supports the auto set up that newer PIs support. The only thing you need to do is enable the PIs I2C from the rasp-config tool (see here https://www.raspberrypi.org/documentation/configuration/raspi-config.md)

After I2C is enabled ensure you have the following packages installed via apt-get

sudo apt-get install python-smbus python-pip

then using pip install the raspihats package

sudo pip install raspihats

If you want a video walk through of the setup you can find one below :-

And here is my python test script simulating some of the PLC. The pupose of this script is to simulate some exteral hardware they we do not have to test the machine against. The particular handshake is defined by our specifications but it shows how easy a test script can be written and the RaspiHats modules used

from raspihats.i2c_hats import Di6Rly6
from time import sleep
import time

board = Di6Rly6(0x60)

#force reset

board.do_set_state(3,1)
sleep(1)
board.do_set_state(3,0)

board.do_set_state(4,1)
board.do_set_state(5,0)
#board.do_set_state(7,1) # only 6 relays this is forced 0


while (1):
 sleep(1)

print("Waiting for ready signal")
 #wait for ready signal
 while(board.di_get_state(0)==0):
 print("Not ready")
 time.sleep(1)

print("system is ready")
 print(" .. Moving head to load position and requesting vac")

board.do_set_state(0,1) # req vac

#wait for vac on signal
 while(board.di_get_state(2)==0):
 #do nothing
 time.sleep(1)

print("VAC on requesting a cal cycle")

board.do_set_state(2,1) # req cal cycle


 #wait for done signal
 while(board.di_get_state(1)==0):
 #do nothing
 time.sleep(1)

#read passfail

if board.di_get_state(5)==1:
 print("** ITS A PASS **")
 else:
 print("** ITS A FAIL **")


 board.do_set_state(2,0) # clear cal cycle request

print("robot head in position, signal vac off")

#move head and turn on robot vac then say our vac is on by deasserting vac req signal
 board.do_set_state(0,0)

print("waiting for vac off comfirm")

#wait for vac off signal
 while(board.di_get_state(2)==1):
 #do nothing
 time.sleep(1)

print("Cycle complete")

#loop around and wait for ready signal

CSV to gnucash via QIF with the help of awk

MySQL/MariaDB to excel

Reversing a heat pump protocol

Simple (c#) message broker using Nanomsg

Stuck on Pokemon Go loading screen on android [FIXED]

Mains frequency monitoring

Fake FTDI and esp8266 flashing issues

Another stunning success

Another one bites the dust

First test of the RaspiHats Isolated IO module

Recent Posts

Sponsored messages

Recent Comments

Archives

Categories

Meta