Topic: Import of Direct Sales Invoice from csv fails

Just starting out with fresh install of FA 2.4.8.
In Display Setup chose date format YYYY/MM/DD
Updated module import_transactions according to https://frontaccounting.com/punbb/viewtopic.php?pid=30301#p30301 (post by apmuthu at 2017-12-25 12:50:55 am, post ID 117 in that thread)
Defined a customer with branch; shows up in table 1_cust_branch
Did a database backup
Defined an item category for service items; shows up in 1_stock_category
Defined an item in that category; shows up in 1_stock_master
Manually entered a direct sales order for the customer and item defined above; is reflected in tables 1_refs and 1_sales_orders.
Did Import of Journal Entries - success (they show up in reports)
Did a database restore
Redefined same item category and same item.
Imported Direct Sales order for that customer and that item from csv file, turned off trial run check mark; success of processing signaled in green; but there is no change in database tables 1_refs or 1_sales_orders (they stayed empty).

csv file imported:
customer_id,branch_No,reference,      date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,unit,price,discountpercentage,freightcost,delfrom,   deldate,delto,      deladdress,  contactphone,                     email,custref,shipvia,               comments,ex_rate
          1,        1,     2345,2020/04/11,         2,         Retail,          "",           "",   "5551",   "Pattern tow",       1,Each,32.00,                 0,          0,    DEF,2020/04/11, Cust,"Bergseth Field","206 660 0019",info@pugetsoundsoaring.org,  "001", Glider,"Roberto's pattern tow",      1

I expected that the import action is reflected in at least tables 1_refs and 1_sales_orders. What might I be doing wrong?

r4

Re: Import of Direct Sales Invoice from csv fails

Try trial first and then without it.
Also check db permissions are getting percolated into the extension and file and folder permissions. Check the error logs, mysql logs, php logs and fa logs with errors turned on in the config.

Which platform are you on and what versions of PHP/MySQL are you using?
Why don't you try with the latest FA codebase which has post release fixes.

Re: Import of Direct Sales Invoice from csv fails

Try trial first and then without it: Yes, I do that routinely. I get green messages, same either way; no red messages.
Platform: webserver is CloudLinux 7.7, cPanel version 86.0.16. Database server: server type = MariaDB, database client version: libmysql 5.6.43; PHP version 7.3.6.
I applied the post release fixes.
There was no PHP.ini file. I copied one (presumably the standard one provided by the hosting company) into the FrontAccounting root directory. Made these changes: display_errors = On ; track_errors = On ; error_log = /home/pugets12/logs/FA_errors.log .
Made no error-logging related changes in config.PHP.

With the above changes, there was no change in FA import_transactions behavior, nor could I find any errors logged in the routine system logging file (in this case /logs/frontaccounting.pugetsoundsoaring.org-Apr-2020.gz). File /logs/FA_errors.log was not written.

Don't know how to go about check db permissions are getting percolated into the extension and file and folder permissions: what I can report is that, when I descend into modules/import_transactions all folders have permission 0755 and all files have 0644.
I am puzzled about constant VARLOG_PATH in config.PHP . Is it set to root/logs ?

r4

Re: Import of Direct Sales Invoice from csv fails

Hang on, please! Just found definition of VARLOG_PATH, and found error logs in /tmp directory. Will report on what they say ...l

r4

Re: Import of Direct Sales Invoice from csv fails

Here is the error:

[27-Apr-2020 13:35:45 America/Los_Angeles] <b>DATABASE ERROR :</b> order Cannot be Added<br>error code : 1366<br>error message : Incorrect double value: '' for column `pugets12_fron528`.`1_sales_orders`.`prep_amount` at row 1<br>sql that failed was : INSERT INTO 1_sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date,
        order_type, ship_via, deliver_to, delivery_address, contact_phone,
        freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount)
        VALUES ('1','0','          1', '30','        1', '2345','auto','Robertos pattern tow','2020-04-11', '1', '        0','Cust','Bergseth Field', '206 660 0019', '0', 'DEF', '2020-04-11','         2','32','')<br><br><br>

But the template .csv file does not contain a field for prep_amount .

r4

Re: Import of Direct Sales Invoice from csv fails

There are several spaces preceding each ID field. Also the prep_amount is the last field according to your insert statement with an empty value and hence the error.

Post's attachments

Values.png 7.2 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Import of Direct Sales Invoice from csv fails

I removed the extra spaces in the import file SampleSingleLineSalesOrder.csv, and I added a field at the end for prep_amount with a value of 0; it now looks like this:

customer_id,branch_No,reference,date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,unit,price,discountpercentage,freightcost,delfrom,deldate,delto,deladdress,contactphone,email,custref,shipvia,comments,ex_rate,prep_amount
1,1,"2345","2020/04/11",2,"Retail","","","5551","Pattern tow",1,"Each",32.00,0,0,"DEF","2020/04/11","Cust","Bergseth Field","206 660 0019","info@pugetsoundsoaring.org","001",0,"Robertos pattern tow",1,0

The green display of processing shows that the last field in the import .cvs file is ignored (see picture SalesOrderImportProcessing.gif ( https://1drv.ms/u/s!AvoFL8QrGVaTsFnatwmQ9QMfyZfY?e=Rebdze )). The INSERT error in the log file is exactly the same as before.

File import_transactions.php has this statement in line 198:
    display_notification_centered(_("Processing line $line ($customer_id, $branchNo, $reference, $date, $payment_id, $sales_type_name, $dimension_id, $dimension2_id, $item_code, $item_description, $quantity, $unit, $price, $discountpercentage, $freightcost, $delfrom, $deldate, $delto, $deladdress, $contactphone, $email, $custref, $shipvia, $comments, $exrate) in import file '{$_FILES['imp']['name']}')"));

which looks like it produces the output shown in file SalesOrderImportProcessing.gif ( https://1drv.ms/u/s!AvoFL8QrGVaTsFnatwmQ9QMfyZfY?e=Rebdze ), i.e., there is no field for prep_amount. The later calls to copy_to_cart (lines 224 and 235) also do not have an argument for prep_amount. It's not surprising then that a value for this field is missed later on when we try to write to the database.

Might I have uncovered a bug, or am I working with the wrong version of the import module?
As I said earlier, I updated module import_transactions according to https://frontaccounting.com/punbb/viewtopic.php?pid=31790#p31790 (post by apmuthu at 2017-12-25 12:50:55 am, post ID 117 in that thread) which did fix another error I had run into.

Post's attachments

SalesOrderImportProcessing.gif 234.7 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
r4

Re: Import of Direct Sales Invoice from csv fails

It is clear that you are using an old version of the import_transactions module. Get the latest from here.

@joe: Please update the official repo with the one from my GitHub repo (attached herein) for this extension.

Post's attachments

import_transactions.zip 50.5 kb, 6 downloads since 2020-04-30 

You don't have the permssions to download the attachments of this post.

Re: Import of Direct Sales Invoice from csv fails

Hello,

I will ask Janusz to do that.

Joe

Re: Import of Direct Sales Invoice from csv fails

Repo has been updated. Thank you.
J.

11 (edited by ROBERTRACKL 05/03/2020 06:19:56 pm)

Re: Import of Direct Sales Invoice from csv fails

Thank you for the updated import_transactions module!

After using it to replace the import module, I still had the prep_amount error. After some code analysis (I am not a PHP programmer but know other languages [C, VB, C#] I modified line 220 in import_transaction.PHP to read

    $_SESSION['Items'] = new import_sales_cart($type, 0, false); $_SESSION['Items']->prep_amount = 0.0E0;

because a zero prepay amount is fine for me for the time being. This got me past the error.

I then had to change in the csv input file the values for dimension_id and dimension2_id from strings to integers (between "Retail" and "5551"). The file now looks like this (2 lines):

customer_id,branch_No,reference,date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,
unit,price,discountpercentage,freightcost,delfrom,deldate,delto,deladdress,contactphone,email,custref,shipvia,comments,ex_rate,
prep_amount
1,1,"2345","2020/04/11",2,"Retail",1,2,"5551","Pattern tow",1,"Each",32.00,0,0,"DEF","2020/04/11","Cust","Bergseth Field","206 660 0019","info@pugetsoundsoaring.org","001",0,"Robertos pattern tow",1,0

Unfortunately, the import still fails without reporting an error in the PHP error log: In the UI, green messages say that the import was successful. Checking the database - table 1_sales_orders has one record with contents from the csv import file. But table 1_sales_order_details remains empty; a sales order inquiry shows no records.

There is an error reported in the PHP log, but I believe it is unrelated:
[03-May-2020 09:41:35 America/Los_Angeles] 1:admin:import_transactions.php:399: fclose() expects parameter 1 to be resource, null given

There are no errors reported in the system log that logs the GET and POST requests for the FA website.

What should I do next, please?

r4

Re: Import of Direct Sales Invoice from csv fails

The last error means that the query did not yield any records.

If possible, try to revert to using PHP 5.6 instead of your 7.3 and see if that solves the issue.

Re: Import of Direct Sales Invoice from csv fails

We have a resolution, but some kind of software change in the import_transaction module is still required to handle 'prepayment amount'.

I started over the installation of FrontAccounting by doing a manual install instead of using softaculous at InMotion Hosting (see posts 2 and 3 of https://frontaccounting.com/punbb/viewtopic.php?id=8789 ). I installed and activated the import_transactions module; gave several roles access to the import_transactions module. Importing journal entries worked well. The attempt to import a direct sales invoice failed, but in a different way from before: even during the trial import, the progress bar kept going indefinitely without messages on the screen or in the FA error log. As described before, I modified line 220 of import_transactions.php to read:

$_SESSION['Items'] = new import_sales_cart($type, 0, false); $_SESSION['Items']->prep_amount = 0.0E0;

i.e., setting the prepay amount to zero. After that, the direct sales invoice import functioned properly.

Setting the prepay amount to zero is ok for my installation, but most likely not for everyone interested in this module.

Now - - on to solving the next problem!=D

r4

Re: Import of Direct Sales Invoice from csv fails

Thanks guys.
ROBERTRACKL / apmuthu
I’ve been able to make my FA import direct sales invoice to work properly by applying the modification done by ROBERTRACKL.
More power and keep safe.

Re: Import of Direct Sales Invoice from csv fails

I am coming back to this after not using it for several months. I am trying to import a direct sales .csv file with line 1 containing the headers and lines 2 through 5 containing the data, and line 6 being empty, like so:

Line 1:

debtor_no,branch_code,reference,date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,unit,price,discountpercentage,freightcost,delfrom,deldate,delto,deladdress,contactphone,email,custref,shipvia,comments,ex-rate,prep_amount

Line 2:

4,4,"003/2019",2019/05/11,3,"Retail",1,2,"5550","Flight on 2019/05/11 15:32, tow by 3TM, rel @ 3600 ft, Dur 116 min, CC F. Launch $:  32.00 + ( 2500 - 1000 = 1500 ft) x 0.0140 = 53.00, 50 % split -> 26.50",1.0000,"Each",26.5000,0,0,"ID List: INVLINES:155, INVOICES:109",2019/05/11,"Paul `R` Caspell","Bergseth Field","","","10018",0,"",0,0

Line 3:

4,4,"003/2019",2019/05/11,3,"Retail",1,2,"5510","Flight on 2019/05/11 15:32 with First Pilot Robert `G` Rackl, glider PW-6, rel @ 3600 ft, Dur 116 min, CC F. Rental $:  12.00 + (116 - 20 = 96 min) x 0.6000 = 69.60 rounded up to 70.00, 50 % split -> 35.00",1.0000,"Each",35.0000,0,0,"ID List: INVLINES:156, INVOICES:109",2019/05/11,"Paul `R` Caspell","Bergseth Field","","","10018",0,"",0,0

Line 4:

1,1,"002/2019",2019/05/11,3,"Retail",1,2,"5550","Flight on 2019/05/11 15:32, tow by 3TM, rel @ 3600 ft, Dur 116 min, CC F. Launch $:  32.00 + ( 2500 - 1000 = 1500 ft) x 0.0140 = 53.00, 50 % split -> 26.50",1.0000,"Each",26.5000,0,0,"ID List: INVLINES:153, INVOICES:108",2019/05/11,"Robert `G` Rackl","Bergseth Field","","","10001",0,"",0,0

Line 5:

1,1,"002/2019",2019/05/11,3,"Retail",1,2,"5510","Flight on 2019/05/11 15:32 with Second Pilot Paul `R` Caspell, glider PW-6, rel @ 3600 ft, Dur 116 min, CC F. Rental $:  12.00 + (116 - 20 = 96 min) x 0.6000 = 69.60 rounded up to 70.00, 50 % split -> 35.00",1.0000,"Each",35.0000,0,0,"ID List: INVLINES:154, INVOICES:108",2019/05/11,"Robert `G` Rackl","Bergseth Field","","","10001",0,"",0,0

Line 6:

Problem: Doing a 'trial' import of this file. After reading lines 2, 3, and 4 ok (green messages echoing  the data in the lines), line 5 is ignored, i.e., there is no response to it. When I exchange lines 2/3 with lines 4/5 the same thing happens: what was in line 5 is now in line 3 and gets echoed in a green message, but what was in line 3 is now in line 5 and is ignored. When I try to make the input .csv file longer I get the same problem: first 3 lines are echoed in green, remainder is ignored.

r4

Re: Import of Direct Sales Invoice from csv fails

You are using backticks in item_description and delto field values. Remove them and all should be well.

Post's attachments

Import_Invoices_Data.xlsx 10.4 kb, 4 downloads since 2020-11-30 

You don't have the permssions to download the attachments of this post.

Re: Import of Direct Sales Invoice from csv fails

Hmm ... Thanks for the investigation apmuthu but after removing all 12 backticks and importing again, the problem is exactly the same. What should I do?

r4

Re: Import of Direct Sales Invoice from csv fails

Choose the entry that was successful and make 10 entries of the same and import them making sure the Inv Nos are different so as not to violate the primary key requirements.

If you still get the error then it has something to do with the memory setting in PHP / Webserver configs.
Also check if your PHP variables settings are high enough like 3000 or so (default is 1000 only).

Re: Import of Direct Sales Invoice from csv fails

Simplified input test file like so:

debtor_no,branch_code,reference,date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,unit,price,discountpercentage,freightcost,delfrom,deldate,delto,deladdress,contactphone,email,custref,shipvia,comments,ex-rate,prep_amount
1,1,"002/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"003/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"004/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"005/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"006/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"007/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"008/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0
1,1,"009/2019",2019/05/11,3,"Retail",1,2,"5550","Flight",1.0000,"Each",26.5000,0,0,"ID List",2019/05/11,"Robert G Rackl","Bergseth Field","","","10001",0,"",0,0

Strangely, processing now stops after showing first two (instead of three) invoices in green.
Running PHP version 7.2; tried going to 5.6 - no difference.

Here are some settings in php.ini: (no changes made)
max_input_vars=4000
memory_limit=512M
report_memleaks = on
;sysvshm.init_mem  [commented out]
opcache.memory_consumption=64
opcache.interned_string_buffer=4
opcache.max_wasted_percentage=5
opcache.revalidate_freq=2
opcache.preferred_memory_model=    [left empty]
opcache.protect_memory=0
opcache.mmap_base=      [left empty]
opcache.file_cache=     [left empty]
opcache.file_cache_only=0

Here are some settings in .htaccess: (no changes made)
php_value memory_limit 512M
php_value post_max_size 512M
php_value error_log "/home/pugets12/logs/FA_errors.log"    [last written to in May 2020]
php_value max_input_vars 4000
php_value error_reporting E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT
php_flag display_errors On
php_flag display_startup_errors Off
php_flag log_errors On

Any suggestions?

r4