Page 1 of 1

[DEV] KashFlow Accounting Integration

Posted: Fri Dec 03, 2010 11:26 pm
by Martin
I'm going to be using this as my slate to record notes and vocalise ideas, nuggets of intel, etc... so don't expect much of it to make sense :)

Re: [DEV] KashFlow Accounting Integration

Posted: Sat Dec 04, 2010 12:34 am
by Martin
Ok... Initial thoughts:

1. It would be easy enough to just create a PHP/MySQL specific code set to handle the export but that'd be lazy and more likely to result in mistake later so opting to use the existing modules as guidance (although Quickbooks module is supposedly broken half the time) to get at least the variables for transfer from the admin system.

2. There seems to be a useful form type called "datechooser" that doesn't appear to be used a great deal elsewhere in the ISC code but should provide the necessary date fields for start/end settings to create the range for processing records.

3. The class.accounting.php code has a lot of functionality tied around a "spool" system which I'm guessing is like a print spooler, where the information to be synchronised or processed is pre-processed, checked and then made ready for actual handling. To be honest this stuff is a complete mystery to me with very little documentation and it's pretty tricky to reverse engineer the quickbooks example because I've not used Quickbooks since around 1998 (might have changed a teensy bit since ;)).

So, looking at the StoneEdge code it's obvious that this simply doesn't use the spool code at all so I'm discounting it for now and looking at my own approach.


4. Already got a nice class that should handle the actual data transfer to Kashflow without needing me to spend forever with the XML so that makes life easier too.




So, plan of action...
1. Create necessary JOIN tables to store synch data (eg: last synched) on customer records, order records and product..
2. Figure way of "batching" information to avoid timeouts and/or overstressing the system (possibly a "kfsession" table to record last record, success, etc... information. Couple with a CRON to transfer/update data in Kashflow over time rather than in one major push)
3. Work out how to make this whole "module" lark work...

Re: [DEV] KashFlow Accounting Integration

Posted: Sat Feb 26, 2011 1:26 am
by Martin
Done a ton of work on this over the last few days and I'm reasonably close to having a basic import script ready soon.

Still incredibly rough and it won't be ready for any kind of public release for a while but definitely taking shape...

All the rubbish above was a waste of time too... Ended up coding from scratch and ignoring most of my notes apart from point 4. That has been a $deity send.

Re: [DEV] KashFlow Accounting Integration

Posted: Fri Jun 03, 2011 1:03 am
by Martin
This module has been something I've had to leave, come back to, leave... get hacked off with... leave... etc...

Well, today was the day to finally dust everything off and after a lot of testing I thought I was ready to do the final import to my live accounts. Well, I was... I just hadn't thought it all through.


The import went really well with the code pulling across only those customers with an order that had been accepted (whether shipped, completed, cancelled, refunded) and then their orders.

What I forgot was that there were multiple payment methods involved with some (cheques, Bank to Bank transfer, etc..) going direct into my Business bank account and others (like the Paypal importing system) where you essentially have another "bank account" that acts as an intermediary between the Payment Gateway and your Business bank account. Short version is that my initial import, put every single transaction into the Business bank account. This is not a great idea because the Business account statements normally only contain the Merchant Account consolidation transfers, and not individual gateway payments.

A further complication is that I have multiple merchant accounts for the various foreign currencies I accept on Interspire Cart (thanks to another mod of the SagePay/PayPoint modules they are actually charged in the target currency, not the default).


Chances are I've probably lost you and tbh, I'm not surprised. I nearly lost my own sanity trying to suss it all out but here's the run down of what this module will now do...

Module Features/Functions
  • Reads ISC store invoices for customer information then checks for customer (using the email address) in Kashflow account
    .
  • If customer (email) doesn't exist, module transfers the basic customer information to Kashflow
    Note: It doesn't currently transfer the customer address information
    .
  • Adds invoice information and links to the customer
    .
  • Adds payment information for invoice and links the payment information to payment method and the appropriate account
    .
  • Offers the option to import or ignore Paypal orders
    (Essential if you are already using the Kashflow import system and don't want to duplicate)
    .
  • Creates "unpaid/overdue" invoices for any refunds or cancellations allowing you to manually update any partial or full refunds and make further notes on customers within Kashflow. This is necessary because Interspire Cart doesn't record the amount of a refund. It's a hash but it works.
Kashflow Settings/Requirements
In terms of setting things up you do have to do some manual setting of your Kashflow account to ensure it's ready to accept the data properly
  1. Add any relevant currencies
  2. Add Invoice payment methods to Kashflow
  3. Setup "Bank Accounts" in KashFlow for each of your merchant accounts (to help make consolidation easier or you'll regret it later!)
Module Settings
Most of the following settings for the module itself will be handled by an Admin CP panel but for now here's some of the things the rough cmd line versions requires:
  • Define the $payMethods array in the module to map checkout modules to those payment methods
    eg: checkout_protx and checkout_protxvspdirect => 'SagePay'
  • Define the relevant $kf_bank_accounts in the module (so it can link payments to the appropriate ones)
  • Define the $payMethodMap to link specific modules/currencies to specific bank accounts
All in all, I'm really pleased with the way the code has shaped up... It's still VERY rough around the edges but I've successfully imported all my invoices for 2008 to date (approximately 4,000 orders filtered down to 1,000 non-paypal ones) in less than 2 minutes.


Plan
In case you're wondering, it's not currently ready for distribution or sale yet but here's an idea of what's required before I release it
  1. Move all settings to an Admin CP panel to simplify the settings process
  2. Parse out a lot of the rubbish debug code
  3. Test the finished code again

Future Features?
There's a few things the module won't do either but those will be handled by a later update if I feel there's any point to continuing to support Interspire. In truth it's going to depend a great deal on whether the product is just dead or indeed if Interspire are willing to even consider purchasing a module from me for BigCommerce. We'll see... but things I'd like to add if time allows:
  • Ability to synch customer address data with Kashflow from last ISC order
  • Email notification if an ISC order is deleted or significantly changed that the Kashflow system needs to be updated
    (This is always going to be manual to avoid potential automated fubar messes with consolidated accounts, etc..)
  • Product line import to Kashflow instead of single line summary/total?
    (Probably not going to happen as it makes it very cumbersome and just duplicates the ISC data unnecessarily)