• 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);
            });         

        }

    }
}