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 ?

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 .

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.

Hello,

I will ask Janusz to do that.

Joe

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?

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

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.

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?