PVOutput integration v1

As I mentioned back in November, I’ve been working on integrating with the PVOutput website so I don’t have to manually read the Sunnybeam output files and add stats to the website.

In order to be able to look at creating a solution, I need a set of requirements. These requirements are ones I have thought up:

  • Read new files off of the sunny beam into specified directory

    I store the sunny beams output files on my computer as the device only keeps a rolling 90 days of output stats. If I ever wanted to look back at older stats, I would need them on my pc.

  • Separate the daily files from the monthly files

    The sunny beam creates a daily file and at the end of the month, a month file. Monthly files are stored in a different directory to the daily files on my pc.

  • Upload new file data to pvoutput

    I need to be able to upload the new files which have been transferred to my pc to pvoutput.

  • Upload weather data if available from csv file in root of file dir

    For each day, an average picture of the weather can be set on the record created on pvoutput. As such I shall be creating a simple csv on my pc in the root directory where the outputs are stored which will contain a date and a weather value for that day.

There are also requirements from PVOutput:

  • Make use of the API provided
  • Call services providing system Id and API key in the header
  • Limit the number of calls per hour and minute according to the guidelines

From those four requirements I can break the work needed down into the following:
Create a csv sunny beam reader which will gather the statistics from the file.
Create a csv reader for the weather file
Read the latest output files from the sunny beam and copy them to the directory chosen on pc, separating monthly files from daily files.
Ask pvoutput for the last recording.
The getMissing service on PVOutput takes two parameters df and dt which should be dynamic. So as I’m using Mule – the url contains the expressions to pick out the properties from the MuleMessage header. The response from this service is a list of dates. The dates need to be converted to a Calendar object once received.
Upload to pvoutput from the last recording.

The solution I have so far created is only partially written. I still need to add the weather section and decide how the application will be started / run. If the application is run as a service there are benefits, particularly in not breaking the guideline limits on the service calls to pvoutput. Also as new csv files are created on the device they could be uploaded almost straight away.

There is also no error checking on the service calls to pvoutput. This is relatively easy to add in and I’ll get round to it in version 2.

The testing also needs to be beefed up. There are tests in the project, but more are needed. Especially around reading files off the sunny beam and copying them. Also around error handling on service calls.

The next section is about the most interesting aspects of the code so far

I took the rather lazy approach to using Mule :-). At the same time, I decided to try out something I haven’t had a chance to do before in main code (only test code previously) – calling services using the MuleClient.
Creating the MuleContext and MuleClient is potentially expensive, so there is a factory storing the MuleContext and MuleClient for further use.

MuleFactoryImpl

package uk.co.vsf.solar.mule.impl;

import org.mule.DefaultMuleMessage;
import org.mule.api.MuleContext;
import org.mule.api.MuleException;
import org.mule.api.MuleMessage;
import org.mule.api.config.ConfigurationException;
import org.mule.api.lifecycle.InitialisationException;
import org.mule.context.DefaultMuleContextFactory;
import org.mule.module.client.MuleClient;

import uk.co.vsf.solar.mule.GeneralMuleException;
import uk.co.vsf.solar.mule.MuleFactory;

public class MuleFactoryImpl implements MuleFactory {

    private static final String MULE_CONFIGS = "mule/pvoutput.xml,mule/pvoutput-getmissing.xml,mule/pvoutput-addoutput.xml,spring/applicationContext.xml";

    private MuleClient muleClient;
    private MuleContext muleContext;

    /**
     * {@inheritDoc}
     */
    public MuleClient getClient() {
        if (muleClient == null) {
            try {
                DefaultMuleContextFactory mcfactory = new DefaultMuleContextFactory();
                muleContext = mcfactory.createMuleContext(MULE_CONFIGS);

                muleContext.start();
                muleClient = new MuleClient(muleContext);
            } catch (InitialisationException e) {
                throw new GeneralMuleException();
            } catch (ConfigurationException e) {
                throw new GeneralMuleException();
            } catch (MuleException e) {
                throw new GeneralMuleException();
            }
        }

        return muleClient;
    }

    /**
     * {@inheritDoc}
     */
    public MuleMessage createMuleMessage(Object payload) {
        return new DefaultMuleMessage(payload, muleContext);
    }
}

The add output Mule config file calls the addoutput service on pvoutput indirectly through TcpTrace (What is TcpTrace). The service is post and the mime type has been set to represent form data. Without that you may find unusual data sent…
In the post request, I’ve used message property transformers to add in the pvoutput apikey and systemId. These will appear in the header when sent.
pvoutput-addoutput.xml

<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:spring="http://www.springframework.org/schema/beans" xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
	xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.2/mule.xsd
       http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/3.2/mule-http.xsd
       http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.2/mule-vm.xsd">
       
	<flow name="addOutputFlow">
		<vm:inbound-endpoint path="addoutput" 
							 exchange-pattern="request-response"
							 connector-ref="pvoutputConnector" />
		<http:outbound-endpoint address="http://localhost:40000/service/r2/addoutput.jsp"
								method="POST" 
								connector-ref="HttpConnector" 
								exchange-pattern="request-response"
								followRedirects="false"
								mimeType="application/x-www-form-urlencoded">
			<message-properties-transformer>
				<add-message-property key="X-Pvoutput-Apikey" value="${pvoutput.api.key}" />
				<add-message-property key="X-Pvoutput-SystemId" value="${pvoutput.system.id}" />
			</message-properties-transformer>
		</http:outbound-endpoint>
	</flow>
	
</mule>

The same message transformation is applied to the get missing service request. Also for this service, the url has to change to get the dates of outputs not added from the system install date to today. For that I’ve used message property EL selection.
pvoutput-getmissing.xml

<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:spring="http://www.springframework.org/schema/beans" xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
	xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.2/mule.xsd
       http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/3.2/mule-http.xsd
       http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.2/mule-vm.xsd">

	<flow name="getMissingFlow">
		<vm:inbound-endpoint ref="GetMissingEndpoint" />
		<http:outbound-endpoint address="http://localhost:40000/service/r1/getmissing.jsp?df=#[header:INBOUND:df]&amp;dt=#[header:INBOUND:dt]"
								method="GET" 
								connector-ref="HttpConnector" 
								exchange-pattern="request-response"
								followRedirects="false">
			<message-properties-transformer>
				<add-message-property key="X-Pvoutput-Apikey" value="${pvoutput.api.key}" />
				<add-message-property key="X-Pvoutput-SystemId" value="${pvoutput.system.id}" />
			</message-properties-transformer>
		</http:outbound-endpoint>
	</flow>
	
</mule>

Complete Code

Can be found here: http://blog.v-s-f.co.uk/2012/01/pvoutput-integration-v1-code/

GU10 follow up – price checking

This is really a follow up to http://blog.v-s-f.co.uk/2011/12/the-real-cost-of-gu10s/.  I’ve been monitoring the prices of the new 4W LED GU10s on CPC for a few weeks now.  When CPC adds a product to their website, they add the base product code, E.g. LP04169 and then there are up to 99 further product code permutation possible.  E.g. LP04169[01-99].  For the past three weeks I’ve been checking all the codes manually (typing in the product code into the search and visually identifying the cheapest product code.

So today (yes… I know it’s Christmas day, but I couldn’t sleep…) I decided to automate the process.  I started off by writing a program using Mule 3.2, but Mule doesn’t follow the redirects and load the Javascript correctly.  So my next choice was Selenium, but I don’t like the fiddly UI for firefox.  I created the bare basics for a Selenium test and then found I could export the test to Java!  I last used Java Selenium 2 years ago and it wasn’t all that easy, but they’ve vastly improved it since then and added Maven support.  I then proceeded to re-write the code it automatically generated and added in what I really wanted to achieve – price checking.

Below is the code required to check a product on CPC for the lowest price available:

(I’ve used TestNG rather than Junit as it was the test framework already imported for the previous utilities class, but it could be substituted for Junit.)

/**
 * <p>Copyright 2011 Victoria Scales</p>
 * <p>This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.</p>
 * <p>This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.</p>
 * <p>You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>.</p>
 */
package uk.co.vsf.utilities;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
import org.openqa.selenium.NoSuchElementException;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;

/**
 * Simple class for checking the price of a CPC product to find the cheapest code.
 * 
 * @author Victoria
 * @date 2011-12-25
 * @version 0.1
 */
public class CPCPriceCheckerSelenium
{
    // set the driver to the firefox driver by default
    private WebDriver driver = new FirefoxDriver();
    // set the url to the website you wish to check
    private String baseUrl = "http://cpc.farnell.com/";

    @BeforeMethod
    public void setUp() throws Exception
    {
        // this configures a timeout if a selection is not successful after X seconds.
        driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
    }

    @Test
    public void productPriceCheck() throws Exception
    {
        Map<String, Double> pricesFound = new HashMap<String, Double>();
        final int pagesPossible = 100;
        for (int i = 0 ; i < pagesPossible ; i++)
        {
            try
            {
                String product = "LP04169" + pageExtension(i);
                driver.get(baseUrl + product);

                // select the price off the page (if possible)
                // if the text is not found, NoSuchElementException is thrown.
                String price = driver.findElement(By.cssSelector("span.taxedvalue")).getText();
                price = price.replace("(£", "");
                price = price.replace(")", "");

                double priceAsMonetary = Double.parseDouble(price);

                pricesFound.put(product, priceAsMonetary);
            }
            catch (NoSuchElementException e)
            {

            }

            // wait for 10 seconds so as not to spam the website and get blocked...
            Thread.sleep(10000);
        }

        List<String> productCodes = new ArrayList<String>();
        productCodes.addAll(pricesFound.keySet());
        Collections.sort(productCodes);

        for (String instance : productCodes)
        {
            System.out.println(instance + " " + pricesFound.get(instance));
        }

        // cheapest price

        List<Double> pricesOrdered = new ArrayList<Double>();
        pricesOrdered.addAll(pricesFound.values());
        Collections.sort(pricesOrdered);

        System.out.println();
        System.out.println("Cheapest price: £" + pricesOrdered.get(0));
        System.out.println("Most expensive price: £" + pricesOrdered.get(pricesOrdered.size() - 1));
    }

    private String pageExtension(int i)
    {
        if (i == 0)
        {
            return "";
        }
        else if (i > 9)
        {
            return "" + i;
        }

        return "0" + i;
    }

    @AfterMethod
    public void tearDown() throws Exception
    {
        driver.quit();
    }
}

To use Selenium with Maven add the following to your pom:

<dependency>
    <groupId>org.seleniumhq.selenium</groupId>
    <artifactId>selenium-java</artifactId>
    <version>2.15.0</version>
</dependency>

The real cost of GU10s

Before we had the kitchen upgraded, the dining room had the brightest lights (eight, 40 watt standard bulbs) and it was thee room to be in during the winter because of the light. That all changed when we had a new kitchen fitted back in 2001. Back then the modern(ish) thing to have in your redecorated room were GU10 halogen spotlights and we had 9 added in the kitchen and 4 in the bathroom. Each GU10 spotlight uses 50 watts and when all on, the room is really well lit. While 50 watts might not seem a lot, when you work out how much that equates to over an average year, it soon adds up!

I’ve made a simple spreadsheet which roughly represents the cost to our family from having these spotlights on.

Click to download the spreadsheet. MD5 checksum: 8f90bce78a6fc865f1b7564b170121eb

If you’re interested in seeing what your own lighting costs you, simply fill in c7 through c18 with your estimated daily average time the lights are on each month, the number of bulbs, watts per bulb and the cost per unit of electricity (near total). The spreadsheet should add up the other values and present a total estimated yearly cost of using your lights.

Recently the cost of LEDs has dropped quite a bit and the technology has moved on too. We first bought an LED replacement GU10 over a year ago and were not impressed. The light produced was in a very narrow beam and bright blue! It made the entire room seem very cold. The second wasn’t much better. However the third one, which we bought in October 2011, is very impressive! It’s a 4 watt GU10 LED replacement with a warm white light. Having tested it out, the light is very similar to the old bulbs in the ceiling and the colour is no longer bright blue, but a slightly off white heading to yellow colour.

We’ll be upgrading the entire ceiling to use the new bulbs as soon as the price drops. Currently each bulb is £8.28 (including VAT), which would take a year and a half to make back, but as soon as the price drops to £5, I’ll be upgrading J

Interested in buying LED lights? See here: 4 watt GU10 LED warm white

Number of bulbs

9

Watts per bulb

50

Total kWh

0.45

Estimated number of hours a day lights on Total days in month Estimated hours per month Estimated kWh per month
January

3

31

93

41.85

February

3

28

84

37.8

March

2.5

31

77.5

34.875

April

2

30

60

27

May

1.5

31

46.5

20.925

June

1

30

30

13.5

July

1

31

31

13.95

August

1.5

31

46.5

20.925

September

2

30

60

27

October

2.5

31

77.5

34.875

November

3

30

90

40.5

December

3

31

93

41.85

Total

365

789

355.05

Price per unit (pence)

17.5

Total estimated cost per year (pounds)

£62.13