GNUCash sync open reports between workstations

GNUCash is one of my favourite pieces of opensource software, and I have been using it for many years for doing accounts. Just in case it helps anyone else using it across multiple different devices, one thing I really like about the firefox browser is that you can now sync the open tabs across all your devices. So when you get home you have the same set-up on screen as on the computer you left earlier at work. I wanted to do the same thing with GNUCash so the same reports would be open on my workstation at home without having to recreate them and get the same set-up.

It’s very easy to do, you basically just need to copy the gcm file from the most recent device you’ve worked on. On my computers these are stored in:

c:\users\[username]\.gnucash\books\ (windows)
/home/[username]/.gnucash/books (linux)

Overwrite the existing file on the device you want to start working on, and you should have all the reports open, just as you had working on the previous computer.

logo_w120

London Marathon 2016

A bit slower this year, just dipping inside 3 hours, with 2:58:52. It’s my slowest time, but I really can’t complain as I’ve only been running once or twice a week with our new arrival at home, and lots on at work. Hopefully next year will be quicker, and maybe an Autumn marathon in 2017 too.

VBA Macro to find distinct items in an Excel spreadsheet column and count number of times found

Just in case of use to anyone, a quick macro that loops through a column on an excel spreadsheet looking at each different value found, and getting the count of the number of times found…


Sub countValuesInColumn(column As Integer, startRow As Integer)

'Get the frequency of distinct items found in a column
'Assumes fewer than 100 different distinct values
'@author: David Boyce 
'@created: 2015-09-24

Dim activeRow, foundTotal As Variant
Dim activeValue, itemsArray(100, 1), msgTxt As String

activeRow = startRow

Do While NotFound = False
  If ActiveWorkbook.ActiveSheet.Cells(activeRow, column) = "" Then Exit Do
  activeValue = ActiveWorkbook.ActiveSheet.Cells(activeRow, column)
  
    For n = LBound(itemsArray) To UBound(itemsArray)
    
      If itemsArray(n, 0) = "" Then
        itemsArray(n, 0) = activeValue
        itemsArray(n, 1) = 1
        Exit For
      Else
        If itemsArray(n, 0) = activeValue Then
          itemsArray(n, 1) = itemsArray(n, 1) + 1
          Exit For
        End If
      End If
      
    Next
  
    activeRow = activeRow + 1
    
Loop

For n = LBound(itemsArray) To UBound(itemsArray)
  If itemsArray(n, 0) = "" Then Exit For
  msgTxt = msgTxt & itemsArray(n, 0) & " : " & itemsArray(n, 1) & vbCrLf
  foundTotal = foundTotal + itemsArray(n, 1)
Next

msgTxt = msgTxt & foundTotal
MsgBox msgTxt

End Sub

ebizmarts Sage Pay Suite Pro, Slow Checkout Problem

Just in case it helps someone else in the future. I was having problems with the Sage Pay Suite Pro from ebizmarts on a development site, it was hanging after pressing the “place order” button, and then taking over a minute, before moving to the success page with the payment taken.

First assumption was that it must be a conflict with another custom module, or the theme, and so I started disabling other modules and enabling as much logging as possible, without any joy. I then tried setting up another payment method (cash on delivery), and after pressing “place order” the success page loaded almost immediately.

I tried a different integration method (server rather than direct) but it was also very slow. It occurred to me, it might be the domain name (using a test one, rather than the one registered with ebizmarts). So I used the host file, and updated the development magento site to use the registered one. But no, still really slow.

A bit more googling and I came across this page. I disabled the profiler in the Sagepay settings, and the checkout time was back under 10 seconds. A bit strange because the same issue wasn’t present on the live site, but this seems to have resolved the problem.

London Marathon 2015

Last weekend was the 35th London Marathon, it started way back in 1981. I was running for the 5th time, having first raced London in 2001 whilst working an internship in the city. I had a pretty good race all things considered, finishing in 2:46:30 which was close to my best (2:44:31). I was feeling pretty good up to about 18 miles, but started feeling quite sick from around 6 miles to the finish. I put that down to the energy drink and gel I took, and am quite tempted to try running on just water, or maybe 1 energy drink in the future. The ballot for the 2016 race opens on Monday 4th May, and it’s worth entering early (ie soon after midnight) as in recent years it’s closed within a matter of a few hours!

Magento – Multiple Shopping Cart Price Rules using the same Coupon Code

This has just gone into production so I can’t 100% vouch for it, but it looks like you can remove the unique index on a coupon code in magento v1.6 (I think the new versions have improved functionality so a coupon can be used with multiple rules).

This means the same coupon code can have multiple rules (for example offering free post off any item purchased, as well as another benefit such as a discount if a certain item is in the basket).

The table in the mySQL database “salesrule_coupon” needs the index “UNQ_SALESRULE_COUPON_CODE” removed…

database_change

The file /app/code/core/Mage/SalesRule/Model/Resource/Coupon.php needs the function_construct() updated…

FROM:

protected function _construct()
    {
        $this->_init('salesrule/coupon', 'coupon_id');
        $this->addUniqueField(array(
            'field' => 'code',
            'title' => Mage::helper('salesRule')->__('Coupon with the same code')
        ));
    }

TO:

protected function _construct()
    {
        $this->_init('salesrule/coupon', 'coupon_id');
        #$this->addUniqueField(array(
        #    'field' => 'code',
        #    'title' => Mage::helper('salesRule')->__('Coupon with the same code')
        #));
    }

You can then create multiple rules, and specify the same coupon code.

Spotting PO Boxes in different languages

Just in case of help to someone else looking to spot PO Boxes in addresses in various different languages, this array might be useful…


$postBoxArray[] = "post box";
$postBoxArray[] = "postbox";
$postBoxArray[] = "postboks";
$postBoxArray[] = "postfach";
$postBoxArray[] = "po box";
$postBoxArray[] = "p.o. Box";
$postBoxArray[] = "apartado";
$postBoxArray[] = "pl ";
$postBoxArray[] = "pl.";
$postBoxArray[] = "boƮte postale";
$postBoxArray[] = "cp ";
$postBoxArray[] = "cp.";
$postBoxArray[] = "poczta";
$postBoxArray[] = "caixa";
$postBoxArray[] = "box ";

foreach ($postBoxArray as &$value) {
  if ( strpos(strtolower($order->$address1),$value) || strpos(strtolower($order->address2),$value) ) {
    #produce warning here
  }
}

Magento problems with checkout_cart_product_add_after event

I’ve recently been writing some code to fire off emails based on abandoned baskets. The site I’m working on doesn’t send the customer to the basket page when an item is added as it’s better to return the customer to the product page so they can more easily added a second variation of the same product (maybe a different colour or size).

So in order to fire off some code when the item is added I used the checkout_cart_product_add_after event. I found two problems using this event – one is that it fires twice when a configurable product is added to the basket. So when using some code like this to iterate through the items in the basket it was adding an extra entry…

$cart = Mage::getModel('checkout/cart')->getQuote();
$items = $cart->getAllVisibleItems();
foreach ($items as $itemId => $item) {
  $mySku = $item->getSku();
  $myItemId = $item->getId();
  $myItemName = $item->getName();
  $myItemQuantity = $item->getQty();
  $myItemPrice = $item->getBasePriceInclTax() - $item->getBaseDiscountAmount();
}

The price was also not getting set correctly, the quick fix is to switch to using a different event – there’s one called checkout_cart_add_product_complete and this one will only fire once, and set the price correctly.

There’s a handy list of all the Magento events available here.