• You are here : 
  • ByteMe
  • / Monthly archives for 2018/03

  • 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