1 Topic by ticketman (edited by ticketman 09/13/2012 10:41:55 pm)

  • Registered: 04/23/2011
  • Posts: 8

Topic: Paypal import problem

I have installed the Paypal extension and enabled the roles. I've tried to import a csv file but get an error:-

DATABASE ERROR : Cannot clear paypal items
error code : 1146
error message : Table 'fusion_fact.0_import_paypal' doesn't exist
sql that failed was : DELETE FROM 0_import_paypal WHERE user_id = 'mike'

What do I need to do to clear this error? As far as I can tell the extension installation was successful but obviously there are database tables missing

Thanks

  • From: Canada
  • Registered: 06/12/2010
  • Posts: 69

Re: Paypal import problem

There is a SQL query in /modules/import_paypal/sql that you can run within PhPmyAdmin. This creates two new tables needed by this module.

I have been trying to use this and found a few peculiarities. If you are doing currency conversion, the conversion rates have to be entered prior to importing the PayPal.csv file. And the date for your company has to be in a specific format - Under preferences, select Dateformat = YYYYMMDD and the date separator as -

So far, all this does for me is to import withdrawals. I haven't been able to get it to import any sales yet.

Is anyone using this module?

I am using FrontAccounting 2.3.12

  • Registered: 04/23/2011
  • Posts: 8

Re: Paypal import problem

I am also trying to use this Import Paypal extension. I successfully executed the the SQL script but I ran into a couple of more problems.  The headings in the UK Paypal csv file do not .entirely match up with those in the code, e.g. post and packing heading did not match any of the alternatives given and the 'types' of payment processed do not cover all the types included in the csv file.

However most serious at the moment is ,that although it appears to process payments (I haven't tried generating receipts or Invoices yet), inspection of the Paypal  Bank Account shows only Credits (fees) but never Debits (Nett Amounts).

Bank Transfers however appear in both Credit and Debit columns.

I'm trying to work through the code but my php and sql skills are very limited.

Any ideas?

  • From: Canada
  • Registered: 06/12/2010
  • Posts: 69

Re: Paypal import problem

With debugging turned on I get these error messages. I would appreciate any help to resolve this. FA 2.3.12

Undefined index: Address Line 1 in file: **path**/modules/import_paypal/import_paypal_update.php at line 303
Undefined index: Address Line 2/District/Neighbourhood in file: **path**/modules/import_paypal/import_paypal_update.php at line 304
Undefined index: Town/City in file: **path**/modules/import_paypal/import_paypal_update.php at line 305
Undefined index: State/Province/Region/County/Territory/Prefecture/Republic in file: **path**/modules/import_paypal/import_paypal_update.php at line 306
Undefined index: Zip/Postcode in file: **path**/modules/import_paypal/import_paypal_update.php at line 307
Undefined index: Postcode in file: **path**/modules/import_paypal/import_paypal_update.php at line 309
Undefined index: Country in file: **path**/modules/import_paypal/import_paypal_update.php at line 310
Undefined index: Item Title in file: **path**/modules/import_paypal/import_paypal_update.php at line 345
Undefined index: Item ID in file: **path**/modules/import_paypal/import_paypal_update.php at line 346
Undefined index: Item Title in file: **path**/modules/import_paypal/import_paypal_update.php at line 347
Undefined index: Insurance Amount in file: **path**/modules/import_paypal/import_paypal_update.php at line 352
Undefined variable: shipping in file: **path**/modules/import_paypal/import_paypal_update.php at line 373
Undefined variable: tax in file: **path**/modules/import_paypal/import_paypal_update.php at line 373

The lines 303 -310 in the import_paypal_update.php are as follows:

          $address1 = $data["Address Line 1"];
          $address2 = $data["Address Line 2/District/Neighbourhood"];
          $address3 = $data["Town/City"];
          $address4 = $data["State/Province/Region/County/Territory/Prefecture/Republic"];
          $postcode = $data["Zip/Postcode"];
          if (empty($postcode))
              $postcode = $data["Postcode"];
          $country = $data["Country"];

Lines 345 - 373 are as follows: 

          $memo = $data["Item Title"];
          $item_code = $data["Item ID"];
          $item_title = $data["Item Title"];
          $currency = $data["Currency"];
          $gross = str_replace(",","",$data["Gross"]);
          $fee = str_replace(",","",$data["Fee"]);
          $net = str_replace(",","",$data["Net"]);
          $insurance = str_replace(",","",$data["Insurance Amount"]);

          // shipping and tax have different headings in different paypal countries
          if (array_key_exists("Shipping and Handling Amount", $data))
              $shipping = str_replace(",","",$data["Shipping and Handling Amount"]);
          elseif (array_key_exists("Postage and Packing Amount", $data))
              $shipping = str_replace(",","",$data["Postage and Packing Amount"]);
          elseif (array_key_exists("Postage and Packing", $data))
              $shipping = str_replace(",","",$data["Postage and Packing"]);

          if (array_key_exists("GST", $data))
              $tax = str_replace(",","",$data["GST"]);
          elseif (array_key_exists("VAT", $data))
              $tax = str_replace(",","",$data["VAT"]);
          elseif (array_key_exists("Sales Tax", $data))
              $tax = str_replace(",","",$data["Sales Tax"]);

          if ($use_paypal_trn_id) {
              $ref = $data["Transaction ID"];
          }
          $type = strtoupper($data["Type"]);
          log_message($type.", gross:".$gross.", fee:".$fee.", net:".$net.", shipping:".$shipping.", tax: ".$tax);
  • From: Canada
  • Registered: 06/12/2010
  • Posts: 69

Re: Paypal import problem

After posting the above I realized that my PayPal csv file did not have these entries (Address Line 1 etc.) so I commented out the lines in the import_paypal_update.php file and the error messages are gone. Still no data being imported though.

  • Registered: 04/23/2011
  • Posts: 8

Re: Paypal import problem

I have solved my immediate problem of not being able to import the 'net' figure in a transaction.  This was traced to the 'taxline' calculation which needed a 'Tax Group' to be defined.  We are a non VAT registered Company and although I had set up a 0% tax rate I had not defined a 'Tax Group'. Setting up a Tax Group after enabling the appropriate role, resulted in correct import of Web transactions.

Now need to find out why I  get database errors when trying to create Customers and Invoices. I'll post what I find