Error creating new invoice

[edsulst]

I can edit all my existing invoices, but as soon as I add a new invoice it’s throwing an error when I save the invoice.

In the php.log file it gives me the following error:

 [3] => Array
        (
            [file] => /home/sulste1q/iwarecms.nl/invoice/index.php
            [line] => 375
            [args] => Array
                (
                    [0] => /home/sulste1q/iwarecms.nl/invoice/modules/invoices/save.php
                )

            [function] => include
        )

)

[04-Oct-2019 15:09:30 Europe/London] Invoice::insert() - Error: PdoDb - query(): Execute error. See error_log.

It seems to me that there is an error in a query, but where can I find the error_log.
My error_log of my domain don’t give any errors.

[RRowley]

What is the version number and date for you installation?

Also, show the complete error from the error log. It will state with a display of the SQL statement that was being executed followed by the debug backtrace of the call stack,

[edsulst]

I’ve upgraded from an older version of Simple Invoices.
I’ve downloaded the “master_2019.2” from Github.

The error below states that there is an duplicate entry.
Off course I made a backup of the old Simple Invoices DB and when I use the older version, it’s still possible to add invoices, but not in the “master_2019.2” version.

This is the complete log:
[04-Oct-2019 15:09:30 Europe/London] PdoDb - debugger(): INSERT INTOsi_invoices(index_id,domain_id,biller_id,customer_id,type_id,preference_id,date,custom_field1,custom_field2,custom_field3,custom_field4,note,owing,last_activity_date,aging_date,age_days,aging,sales_representative`) VALUES (1, 1, 1, 15, 2, 2, ‘2019-10-04 15:09:30’, ”, ”, ”, ”, ‘<div>dd</div>’, 1, ‘2019-10-04 03:09:30’, ‘2019-10-04 03:09:30’, 0, ”, ”)
[04-Oct-2019 15:09:30 Europe/London] PdoDb – query(): Execute error.Array
(
[0] => 23000
[1] => 1062
[2] => Duplicate entry ‘1-2-1-1’ for key ‘UniqDIB’
)

[04-Oct-2019 15:09:30 Europe/London] PdoDb – backtrace: Array
(
[0] => Array
(
[file] => /home/sulste1q/iwarecms.nl/invoice/Inc/Claz/PdoDb.php
[line] => 1174
[function] => query
[class] => Inc\Claz\PdoDb
[object] => Inc\Claz\PdoDb Object
(
[caseStmts:Inc\Claz\PdoDb:private] =>
[debug:Inc\Claz\PdoDb:private] =>
[debug_label:Inc\Claz\PdoDb:private] =>
[debug_microtime:Inc\Claz\PdoDb:private] => 0
[distinct:Inc\Claz\PdoDb:private] =>
[excludedFields:Inc\Claz\PdoDb:private] => Array
(
[id] => 1
)

[fauxPost:Inc\Claz\PdoDb:private] => Array
(
[biller_id] => 1
[customer_id] => 15
[type_id] => 2
[preference_id] => 2
[date] => 2019-10-04 15:09:30
[note] => <div>dd</div>
[custom_field1] =>
[custom_field2] =>
[custom_field3] =>
[custom_field4] =>
[sales_representative] =>
[domain_id] => 1
[index_id] => 1
[last_activity_date] => 2019-10-04 03:09:30
[owing] => 1
[aging_date] => 2019-10-04 03:09:30
[age_days] => 0
[aging] =>
)

[fieldPrefix:Inc\Claz\PdoDb:private] =>
[functions:Inc\Claz\PdoDb:private] =>
[groupBy:Inc\Claz\PdoDb:private] =>
[havings:Inc\Claz\PdoDb:private] =>
[joinStmts:Inc\Claz\PdoDb:private] =>
[keyPairs:Inc\Claz\PdoDb:private] => Array
(
[:index_id_000] => 1
[:domain_id_001] => 1
[:biller_id_002] => 1
[:customer_id_003] => 15
[:type_id_004] => 2
[:preference_id_005] => 2
[:date_006] => 2019-10-04 15:09:30
[:custom_field1_007] =>
[:custom_field2_008] =>
[:custom_field3_009] =>
[:custom_field4_010] =>
[:note_011] => <div>dd</div>
[:owing_012] => 1
[:last_activity_date_013] => 2019-10-04 03:09:30
[:aging_date_014] => 2019-10-04 03:09:30
[:age_days_015] => 0
[:aging_016] =>
[:sales_representative_017] =>
)

[lastCommand:Inc\Claz\PdoDb:private] =>
[limit:Inc\Claz\PdoDb:private] => 0
[noErrorLog:Inc\Claz\PdoDb:private] =>
[onDuplicateKey:Inc\Claz\PdoDb:private] =>
[orderBy:Inc\Claz\PdoDb:private] =>
[pdoDb:Inc\Claz\PdoDb:private] => PDO Object
(
)

[pdoDb2:Inc\Claz\PdoDb:private] => PDO Object
(
)

[saveLastCommand:Inc\Claz\PdoDb:private] =>
[selectAll:Inc\Claz\PdoDb:private] =>
[selectList:Inc\Claz\PdoDb:private] =>
[selectStmts:Inc\Claz\PdoDb:private] =>
[table_columns:Inc\Claz\PdoDb:private] =>
[table_constraints:Inc\Claz\PdoDb:private] =>
[table_engine:Inc\Claz\PdoDb:private] =>
[table_schema:Inc\Claz\PdoDb:private] => sulste1q_invoicev2
[transaction:Inc\Claz\PdoDb:private] =>
[usePost:Inc\Claz\PdoDb:private] =>
[whereClause:Inc\Claz\PdoDb:private] =>
)

[type] => ->
[args] => Array
(
[0] => INSERT INTO si_invoices
(index_id, domain_id, biller_id, customer_id, type_id,
preference_id, date, custom_field1, custom_field2, custom_field3,
custom_field4, note, owing, last_activity_date, aging_date,
age_days, aging, sales_representative)
VALUES (:index_id_000, :domain_id_001, :biller_id_002, :customer_id_003, :type_id_004,
:preference_id_005, :date_006, :custom_field1_007, :custom_field2_008, :custom_field3_009,
:custom_field4_010, :note_011, :owing_012, :last_activity_date_013, :aging_date_014,
:age_days_015, :aging_016, :sales_representative_017)

[1] => Array
(
[:index_id_000] => 1
[:domain_id_001] => 1
[:biller_id_002] => 1
[:customer_id_003] => 15
[:type_id_004] => 2
[:preference_id_005] => 2
[:date_006] => 2019-10-04 15:09:30
[:custom_field1_007] =>
[:custom_field2_008] =>
[:custom_field3_009] =>
[:custom_field4_010] =>
[:note_011] => <div>dd</div>
[:owing_012] => 1
[:last_activity_date_013] => 2019-10-04 03:09:30
[:aging_date_014] => 2019-10-04 03:09:30
[:age_days_015] => 0
[:aging_016] =>
[:sales_representative_017] =>
)

)

)

[1] => Array
(
[file] => /home/sulste1q/iwarecms.nl/invoice/Inc/Claz/Invoice.php
[line] => 563
[function] => request
[class] => Inc\Claz\PdoDb
[object] => Inc\Claz\PdoDb Object
(
[caseStmts:Inc\Claz\PdoDb:private] =>
[debug:Inc\Claz\PdoDb:private] =>
[debug_label:Inc\Claz\PdoDb:private] =>
[debug_microtime:Inc\Claz\PdoDb:private] => 0
[distinct:Inc\Claz\PdoDb:private] =>
[excludedFields:Inc\Claz\PdoDb:private] => Array
(
[id] => 1
)

[fauxPost:Inc\Claz\PdoDb:private] => Array
(
[biller_id] => 1
[customer_id] => 15
[type_id] => 2
[preference_id] => 2
[date] => 2019-10-04 15:09:30
[note] => <div>dd</div>
[custom_field1] =>
[custom_field2] =>
[custom_field3] =>
[custom_field4] =>
[sales_representative] =>
[domain_id] => 1
[index_id] => 1
[last_activity_date] => 2019-10-04 03:09:30
[owing] => 1
[aging_date] => 2019-10-04 03:09:30
[age_days] => 0
[aging] =>
)

[fieldPrefix:Inc\Claz\PdoDb:private] =>
[functions:Inc\Claz\PdoDb:private] =>
[groupBy:Inc\Claz\PdoDb:private] =>
[havings:Inc\Claz\PdoDb:private] =>
[joinStmts:Inc\Claz\PdoDb:private] =>
[keyPairs:Inc\Claz\PdoDb:private] => Array
(
[:index_id_000] => 1
[:domain_id_001] => 1
[:biller_id_002] => 1
[:customer_id_003] => 15
[:type_id_004] => 2
[:preference_id_005] => 2
[:date_006] => 2019-10-04 15:09:30
[:custom_field1_007] =>
[:custom_field2_008] =>
[:custom_field3_009] =>
[:custom_field4_010] =>
[:note_011] => <div>dd</div>
[:owing_012] => 1
[:last_activity_date_013] => 2019-10-04 03:09:30
[:aging_date_014] => 2019-10-04 03:09:30
[:age_days_015] => 0
[:aging_016] =>
[:sales_representative_017] =>
)

[lastCommand:Inc\Claz\PdoDb:private] =>
[limit:Inc\Claz\PdoDb:private] => 0
[noErrorLog:Inc\Claz\PdoDb:private] =>
[onDuplicateKey:Inc\Claz\PdoDb:private] =>
[orderBy:Inc\Claz\PdoDb:private] =>
[pdoDb:Inc\Claz\PdoDb:private] => PDO Object
(
)

[pdoDb2:Inc\Claz\PdoDb:private] => PDO Object
(
)

[saveLastCommand:Inc\Claz\PdoDb:private] =>
[selectAll:Inc\Claz\PdoDb:private] =>
[selectList:Inc\Claz\PdoDb:private] =>
[selectStmts:Inc\Claz\PdoDb:private] =>
[table_columns:Inc\Claz\PdoDb:private] =>
[table_constraints:Inc\Claz\PdoDb:private] =>
[table_engine:Inc\Claz\PdoDb:private] =>
[table_schema:Inc\Claz\PdoDb:private] => sulste1q_invoicev2
[transaction:Inc\Claz\PdoDb:private] =>
[usePost:Inc\Claz\PdoDb:private] =>
[whereClause:Inc\Claz\PdoDb:private] =>
)

[type] => ->
[args] => Array
(
[0] => INSERT
[1] => si_invoices
)

)

[2] => Array
(
[file] => /home/sulste1q/iwarecms.nl/invoice/modules/invoices/save.php
[line] => 51
[function] => insert
[class] => Inc\Claz\Invoice
[type] => ::
[args] => Array
(
[0] => Array
(
[biller_id] => 1
[customer_id] => 15
[type_id] => 2
[preference_id] => 2
[date] => 2019-10-04
[note] => <div>dd</div>
[custom_field1] =>
[custom_field2] =>
[custom_field3] =>
[custom_field4] =>
[sales_representative] =>
)

)

)

[3] => Array
(
[file] => /home/sulste1q/iwarecms.nl/invoice/index.php
[line] => 375
[args] => Array
(
[0] => /home/sulste1q/iwarecms.nl/invoice/modules/invoices/save.php
)

[function] => include
)

)

[04-Oct-2019 15:09:30 Europe/London] Invoice::insert() – Error: PdoDb – query(): Execute error. See error_log.`

[RRowley]

The issue is that the value of the “id” field in the si_index table is not set correctly. It should be set to the value of the “index_id” of the last si_invoice record. The si_index table “id” plus 1 will be the value assigned to the next invoice.

[edsulst]

The index_id field of the si_invoices table is 46
The id field of the si_index table is also 46, so I don’t see any problems here.

[edsulst]

I’ve added the database in this message so you can take a look for yourself.
Maybe you can find something wrong.

• This reply was modified 5 years, 1 month ago by edsulst.

Attachments:

1. invoicev2.rar

[RRowley]

The numbering logic is complex. It keys off of the Invoice Preference setting for the invoice which in the preferences table, the record for that value specifies the Invoice Numbering Group to be used. This then ties back to records in the si_index table keyed by the constant node value of “invoice” and the sub_node value that comes from the Invoice Number Group. If you are confused, I don’t blame you.

To help, I uploaded changes for 2019.2.8 version that adds information to several screens and I updated the “How To …” topic for the “change the invoice number starting point” topic accessed from the menu on the left.

You need to have things set up correctly to get numbering to work. If you want any type of invoice to use that same numbering sequence, in the Inv Prefs records (under the Settings tab) should have the Invoice numbering group set to Invoice. Then they all use the same record.

[edsulst]

Okay, I figured it out.
It had something to do with the “invoice numbering group”.
I can now add new invoices (Yeah !!)

Next problem is that the age of the invoice isn’t shown.
Also the field Owning isn’t shown.

Very strange.
Can you help me out here too ?

[Author]

Posts