Import transaction issues and a query about the meaning of "Transfer'

[MrJamesEllis]

Hello, I recently ran an import of transactions from my bank. There were a couple of issues with the import that may have caused some problems -

1. The code/reference character limit is 16 chrs, my bank does not provide a unique transaction code in a single column, so I had to concatentate two columns in a CSV and select that as a code. The resulting column was over 16 characters. During import this was caught as an error and displayed but I noticed that the table column type for transactions.code and transactions.reference is varchar(255). Changing the validation to 255 chrs max in the import method allowed the transactions to be imported without issue. Additionally, I noticed there are no keys on those two columns which could cause some performance issues when looking up transactions by code.
2. The import kept on timing out due to the FCGI timeout being 30s. I switched this to 600s and the import progressed.
3. Finally, the POSTed transaction data could not be JSON decoded successfully as there were two instances of an extra " character inside a field that threw json_decode(). This wasn't picked up when uploading the transaction CSV - the JSON encoded data that it was tripping on looked like this:

... ,"import-transaction-13-number":" 201410"","import-transaction-13-description":"DIRECT CREDIT","import-transaction-13-date":"2015-04-16","import-transaction-13-amount":"200","import-transaction-13-transfer_account":"","import-transaction-13-transaction-transfer":"ignore", ...

The relevant line in the CSV looks like this:

...,20150416,XXX10,200,DIRECT CREDIT,XXXXX               12345678,"P 201501-3, 201410","XXXXX               12345678 - P 201501-3, 201410"

Note that the "import-transaction-13-number" is "201410" so I think it's tripping up on the "P 201501-3, 201410" cell with a comma in it. That cell is the payment reference field from the bank (who include spaces). The last field is used as the transaction code.

Without digging further into the code, I was wondering why the import doesn't POST the forn data as an array which would be handle in PHP as an array. It seems to be encoding, POSTing a string, then the import method in PHP is running a json_decode() and tripping up.

With that in mind and maybe it affected the 'Transfer' - once I imported all the transactions and had select a 'Transfer' account I noticed that the line item is noted as a 'Decrease' within the account I had imported it into but an 'Increase' on the account I selected as a 'Transfer'.

As an example in my import account I see a "DEBIT CARD FEE" for $3.00 marked as a Decrease. I select 'Transfer' to the 'Bank Service Charge' acocunt and the same transaction when viewing the Bank Service Charge account from Chart Of Accounts is listed as Increase $3.00. The same transaction is now listed under two accounts, as a decrease and an increase.

Maybe I'm misundertanding 'Transfer' but I was thinking I could import transactions into one account then move (Transfer) those transactions to their relevant sub accounts and would appear only once ?

Thanks

James

[MrJamesEllis]

Here's a concrete example of 'Transfer' - Under the 'Taxes' main account I have $10,000 listed under 'Federal'. I then go into that account and then 'Transfer' $9000 of items to another sub-account alongside 'Federal' called 'Sales Taxes'. After the relevant transactions are saved, in the Chart of Accounts it shows '$10,000' in black next to Federal and '$9000' in red next to Sales Taxes. Shouldn't it be showing $1,000 (in red?) next to Federal so that the total remains $10,000 ?

[funnylookinhat]

Hi James - Thanks for the bug reports.

I think I would need to see screen shots of your transfers to understand them completely - but generally speaking, what you're trying to accomplish is best handled with a split.

I'm going to separate out the other three items into separate issues so that we can track them better. In the meantime, could you tell me what server + setup you are running ( re: item 2 ) ? Nginx + FCGI? What does your site configuration file and relevant PHP config file look like? Feel free to paste their entire contents into this issue.

Regarding the extra quote that is in the import data - are you saying the import data is malformed? I would have guessed each item to be surrounded by quotes - but instead it looks like just this should be a single item? "P 201501-3, 201410" Or is that two separate items?

[MrJamesEllis]

Yep sorry, it's gotten a bit woolly.

for 2) It's Apache + FCGI. Bog standard Ubuntu packages for Precise. The relevant timeout setting is in Apache config, current is:

IPCCommTimeout 600

original setting was 30.

I've also set the max_execution_time to 600 for PHP but I believe IPCCommTimeout will kick in before that.

I'll try with splits before taking up more space on the bug tracker. For my reference, what is a 'Transfer'?

Import data: The CSV imports fine, it's just when the importvalidatetransactions is hit up, the error occurs when json_decode is run (the result is NULL). I ran some debug on the code and managed to grab the value of $importdata in this method:

        public function action_importvalidatetransactions()
        {
                $account_id = $this->request->post('account_id');
                $account_table_sign = $this->request->post('account_table_sign');

                $importdata = $this->request->post('importdata');

                if( ! $importdata ) 
                        return $this->_return_error("Missing required import data.");

                $importobject = json_decode($importdata);
                $importarray = array();

When I ran jsonlint (from NodeJS) against the value of $importdata, it was telling me there was an error with the extra quote in the json encoded string, which makes sense:

"import-transaction-13-number":" 201410""

The corresponding line in the CSV has this value:

"P 201501-3, 201410"

Note the space before "201410" - it appears that whatever has encoded the data into JSON format has split on the comma after "P 201501-3" even though that's text and is part of the transaction number. This may be the reason why there is a trailing quote in $importdata as the leading quote has been chomped off - the field is being encode as 201410" without the quote being escaped.

The CSV provided by the bank, which contains fields sometimes enclosed in quotes, may not be helping. I'll see if I can put together a reproduceable CSV file to trigger the JSON decode error.

Thanks for your reply and help.

[funnylookinhat]

OK - I've gone ahead and created two separate issues for the two bugs on importing transactions.

Regarding the FCGI issue - feel free to do a pull request with a fix for the documentation in setup if people would rather use FCGI than the apache module that's documented.