search

fearless359 / simpleinvoices

Fearless359 SimpleInvoices beyond great beginnings
https://simpleinvoices.group
GNU General Public License v3.0
28 stars 7 forks source link

Tried to post this on Simple Invoices Group but it would not let me post #62

Closed johnanastasio17 closed 5 years ago

johnanastasio17 commented 5 years ago

Hi, New PC so I wanted to install and import my existing fearless/simple invoices fromn the old PC. Installed newest xampp, made tmp writeable, and I believe moved everything over that needs moved. Got hung up on the install process when it wants to patch SQL, this is where it stopped: (log file below SQL patch 305)

SQL patch 301, Add last_activity_date, aging_date and aging_value to invoices. has not been applied to the database SQL patch 302, Added owing to invoices table has not been applied to the database SQL patch 303, Add invoice custom field report extension to standard application and add sales_representative field. has not been applied to the database SQL patch 304, Add default_invoice field to the customers table. has not been applied to the database SQL patch 305, Add expense tables to the database. has not been applied to the database

[01-Jul-2019 19:07:20 America/Los_Angeles] PdoDb - debugger(): ALTER TABLE si_invoices ADD last_activity_date DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL COMMENT 'Date last activity update to the invoice', ADD aging_date DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL COMMENT 'Date aging was last calculated', ADD age_days SMALLINT(5) UNSIGNED DEFAULT 0 NOT NULL COMMENT 'Age of invoice balance', ADD aging VARCHAR(5) DEFAULT '' NOT NULL COMMENT 'Aging string (1-14, 15-30, etc.';DELETE IGNORE FROM si_system_defaults WHERE name = 'large_dataset'; [01-Jul-2019 19:07:20 America/Los_Angeles] PdoDb - query(): Execute error.Array ( [0] => 42000 [1] => 1067 [2] => Invalid default value for 'date' )

[01-Jul-2019 19:07:20 America/Los_Angeles] PdoDb - backtrace: Array ( [0] => Array ( [file] => C:\xampp\htdocs\simple_invoices\Inc\Claz\SqlPatchManager.php [line] => 232 [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 ( )

                [fauxPost:Inc\Claz\PdoDb:private] => 
                [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] => 
                [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] => simple_invoices
                [transaction:Inc\Claz\PdoDb:private] => 1
                [usePost:Inc\Claz\PdoDb:private] => 1
                [whereClause:Inc\Claz\PdoDb:private] => 
            )

        [type] => ->
        [args] => Array
            (
                [0] => ALTER TABLE `si_invoices` ADD `last_activity_date` DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL COMMENT 'Date last activity update to the invoice', ADD `aging_date` DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL COMMENT 'Date aging was last calculated', ADD `age_days` SMALLINT(5) UNSIGNED DEFAULT 0 NOT NULL COMMENT 'Age of invoice balance', ADD `aging` VARCHAR(5) DEFAULT '' NOT NULL COMMENT 'Aging string (1-14, 15-30, etc.';DELETE IGNORE FROM `si_system_defaults` WHERE `name` = 'large_dataset';
            )

    )

[1] => Array
    (
        [file] => C:\xampp\htdocs\simple_invoices\Inc\Claz\SqlPatchManager.php
        [line] => 305
        [function] => runSqlPatch
        [class] => Inc\Claz\SqlPatchManager
        [type] => ::
        [args] => Array
            (
                [0] => 301
                [1] => Array
                    (
                        [name] => Add last_activity_date, aging_date and aging_value to invoices.
                        [patch] => ALTER TABLE `si_invoices` ADD `last_activity_date` DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL COMMENT 'Date last activity update to the invoice', ADD `aging_date` DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL COMMENT 'Date aging was last calculated', ADD `age_days` SMALLINT(5) UNSIGNED DEFAULT 0 NOT NULL COMMENT 'Age of invoice balance', ADD `aging` VARCHAR(5) DEFAULT '' NOT NULL COMMENT 'Aging string (1-14, 15-30, etc.';DELETE IGNORE FROM `si_system_defaults` WHERE `name` = 'large_dataset';
                        [date] => 20181012
                        [source] => fearless359
                    )

            )

    )

[2] => Array
    (
        [file] => C:\xampp\htdocs\simple_invoices\index.php
        [line] => 182
        [function] => runPatches
        [class] => Inc\Claz\SqlPatchManager
        [type] => ::
        [args] => Array
            (
            )

    )

)

[01-Jul-2019 19:07:20 America/Los_Angeles] SqlPatchManager::runSqlPatch() - PdoDb - query(): Execute error. See error_log.

What did I miss? Thank You!

fearless359 commented 5 years ago

First, you have to register and/or log in to the SimpleInvoices.group forum in order to post to it.

For the error that you are getting, I'm at a loss. There are to statements that create datetime fields possibly throwing the error. You can try entering each of these independently to see which or if both are throwing the error. Then work to debug it. Since I can't replicate this error, I can't do this for you. Here are the two commands to try:

ALTER TABLE si_invoices ADD last_activity_date DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL COMMENT 'Date last activity update to the invoice';

ALTER TABLE si_invoices ADD aging_date DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL COMMENT 'Date aging was last calculated';

The values in these dates is important (at least the aging_date is earlier than last_activity_date. And that both dates are in the past. This will cause the aging fields to be recalculated for every invoice the first time you access SI after changes are in place.

Once you figure out what the deal is, let me know what you find.

johnanastasio17 commented 5 years ago

Thanks I’ll give it a shot this weekend . I did register and login to the forum before posting, guess it wasn’t my day LOL

On Jul 3, 2019, at 10:08, Rich Rowley notifications@github.com wrote:

First, you have to register and/or log in to the SimpleInvoices.group forum in order to post to it.

For the error that you are getting, I'm at a loss. There are to statements that create datetime fields possibly throwing the error. You can try entering each of these independently to see which or if both are throwing the error. Then work to debug it. Since I can't replicate this error, I can't do this for you. Here are the two commands to try:

ALTER TABLE si_invoices ADD last_activity_date DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL COMMENT 'Date last activity update to the invoice';

ALTER TABLE si_invoices ADD aging_date DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL COMMENT 'Date aging was last calculated';

The values in these dates is important (at least the aging_date is earlier than last_activity_date. And that both dates are in the past. This will cause the aging fields to be recalculated for every invoice the first time you access SI after changes are in place.

Once you figure out what the deal is, let me know what you find.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

johnanastasio17 commented 5 years ago

This is what I get when running the SQL commands from above: MySQL said: #1067 - Invalid default value for 'date'#1067 - Invalid default value for 'date' I tried running them separate and together

fearless359 commented 5 years ago

Try this:

ALTER TABLE si_invoices ADD last_activity_date DATETIME DEFAULT '2000-12-31 00:00:00' NOT NULL; ALTER TABLE si_invoices ADD aging_date DATETIME DEFAULT '2000-12-30 00:00:00' NOT NULL ;

If that doesn't work, try this:

ALTER TABLE si_invoices ADD last_activity_date DATETIME NOT NULL DEFAULT '2000-12-31 00:00:00'; ALTER TABLE si_invoices ADD aging_date DATETIME NOT NULL DEFAULT '2000-12-30 00:00:00';

If this doesn't work, show me the structure of the si_invoices table.

johnanastasio17 commented 5 years ago

Hope this comes out right, I wasn't sure how best to relay this to you, and I truly appreciate you helping me with this!

#   Name    Type    Collation   Attributes  Null    Default     Comments    Extra   Action
1   idPrimary   int(10)             No  None        AUTO_INCREMENT  Change Change   Drop Drop   

More More

2   index_id    int(11)             No  None            Change Change   Drop Drop   

More More

3   domain_idPrimaryIndex   int(11)             No  1           Change Change   Drop Drop   

More More

4   biller_idIndex  int(10)             No  0           Change Change   Drop Drop   

More More

5   customer_idIndex    int(10)             No  0           Change Change   Drop Drop   

More More

6   type_id     int(10)             No  0           Change Change   Drop Drop   

More More

7   preference_id   int(10)             No  0           Change Change   Drop Drop   

More More

8   date    datetime            No  0000-00-00 00:00:00             Change Change   Drop Drop   

More More

9   custom_field1   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

10  custom_field2   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

11  custom_field3   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

12  custom_field4   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

13  note    text    utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More
fearless359 commented 5 years ago

Well, I am at a loss. Let's see if you can add one of the fields in incremental steps:

1) Enter this SQL command: ALTER TABLE si_invoices ADD aging_date DATETIME; 2) If fails, let me know the error. Else, go to the Structure tab and select the Change link for this new field. 3) In the Default column, select As defined from the drop down. Enter 0000-00-00 00:00:00 in the text box that appears below it and Save this change. 4) If this fails, let me know the error. Else verify in the Structure tab, that the field now has a Null setting of Yes, and a Default setting of 0000-00-00 00:00:00. If not, something is really squirrelly. 5) Select the Change option for the field again, change the default value to: 2000-12-31 00:00:00 and Save this change. 6) If this fails, there is something (I don't know what) that objects to this date value. You can try different date values to see if one works. If it turns out that you find a value in the past that works, then try setting up the two fields setting the value of the last_activity_date one day greater than the aging_date. The aging_date is when the aging values were last calculated for the invoice. The last_activity_date is when the last activity occurred on the invoice. If the last_activity_date is more current than the aging_date, the aging values need to be reset. The system will be forced to recalculate the aging fields for the invoice the next time it is accessed. This is how the other new fields (owing, age_days and aging) get set correctly following the installation of this version.

In any case, let me know what you find out.

johnanastasio17 commented 5 years ago

Like Ground Hog Day LOL:

MySQL said: #1067 - Invalid default value for 'date'#1067 - Invalid default value for 'date'

johnanastasio17 commented 5 years ago

The working copy I've been using for years on my old windows 10 pc is running XAMPP with php version 7.1.1 and phpadmin is 4.6.5.2. I wonder if maybe I need to do some kind of incremental upgrade before getting to php 7.3.6?

johnanastasio17 commented 5 years ago

Here's the latest, on a whim I changed "DATETIME" to None and the database installed and ran all of its patches with no errors. Progress hahaha With the following structure I can see invoices paid/open and aging, however I can't run any reports or add a new invoice. Can't access products either:

Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp\htdocs\simple_invoices\Inc\Claz\PdoDb.php on line 1218

Structure

#   Name    Type    Collation   Attributes  Null    Default     Comments    Extra   Action
1   idPrimary   int(10)             No  None        AUTO_INCREMENT  Change Change   Drop Drop   

More More

2   index_id    int(11)             No  None            Change Change   Drop Drop   

More More

3   domain_idPrimaryIndex   int(11)             No  1           Change Change   Drop Drop   

More More

4   biller_idIndex  int(10)             No  0           Change Change   Drop Drop   

More More

5   customer_idIndex    int(10)             No  0           Change Change   Drop Drop   

More More

6   type_id     int(10)             No  0           Change Change   Drop Drop   

More More

7   preference_id   int(10)             No  0           Change Change   Drop Drop   

More More

8   date    datetime            No  2001-01-01 00:00:00             Change Change   Drop Drop   

More More

9   custom_field1   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

10  custom_field2   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

11  custom_field3   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

12  custom_field4   varchar(50)     utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

13  note    text    utf8_unicode_ci         Yes     NULL            Change Change   Drop Drop   

More More

14  owing   decimal(25,6)           No  0.000000    Amount owing as of aging-date       Change Change   Drop Drop   

More More

15  last_activity_date  datetime            No  2001-01-01 00:00:00     Date last activity update to the invoice        Change Change   Drop Drop   

More More

16  aging_date  datetime            No  2001-01-01 00:00:00     Date aging was last calculated      Change Change   Drop Drop   

More More

17  age_days    smallint(5)         UNSIGNED    No  0   Age of invoice balance      Change Change   Drop Drop   

More More

18  aging   varchar(5)  utf8_unicode_ci         No      Aging string (1-14, 15-30, etc.         Change Change   Drop Drop   

More More

19  sales_representative    varchar(50)     utf8_unicode_ci         No              Change Change   Drop Drop   

More More
fearless359 commented 5 years ago

First, which step did the invalid default date occur? Setting 0000-00-00 00:00:00 or another value?

Second, the error is not PHP but is mySQL (or MariaDb). However, if you don't have a reason not to update to the latest XAMPP, then do so. There are previous update versions here: www.apachefriends.org/download.html

fearless359 commented 5 years ago

For the last message, no sure what you mean by changed DateTime to None? This is a field type and they is no such value as None. So what did you change?

As to the PdoDb error, if you look in the error log, you will get a lengthy error that contains the SQL statement that was performed. It would helpful if you give me that.

johnanastasio17 commented 5 years ago

Sorry, From Structure, I clicked "change" and changed DEFAULT from 2001-01-01 00:00:00 to NONE After doing that, all of the patches applied and I was presented with the "normal" login screen with no errors

I probably know barely enough of this to break stuff, but figured since the date was throwing us off, I'd try letting it go with out setting the default date. I was surprised that it actually ran, but it may still be broken I guess.

All of my past invoices loaded, the login worked without an error. I did change the structure back to what I posted above 3 or so hours ago. That is the current structure of the invoices table.

As to the version, my existing usage of Simple Invoices is the fearless version on a different win 10 pc with php version 7.1.1 and phpadmin is 4.6.5.2

All of this that we've been working on is a new PC Win 10 with XAMPP running php 7.3.6 using the simpleinvoices-master_2019 zip file

johnanastasio17 commented 5 years ago

This is today's entries to c:\xampp\apache\logs\error.txt: [Fri Jul 12 06:52:39.079910 2019] [ssl:warn] [pid 3452:tid 608] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Fri Jul 12 06:52:39.126747 2019] [core:warn] [pid 3452:tid 608] AH00098: pid file C:/xampp/apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run? [Fri Jul 12 06:52:39.126747 2019] [ssl:warn] [pid 3452:tid 608] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Fri Jul 12 06:52:39.392308 2019] [mpm_winnt:notice] [pid 3452:tid 608] AH00455: Apache/2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.6 configured -- resuming normal operations [Fri Jul 12 06:52:39.392308 2019] [mpm_winnt:notice] [pid 3452:tid 608] AH00456: Apache Lounge VC15 Server built: May 29 2019 11:22:50 [Fri Jul 12 06:52:39.392308 2019] [core:notice] [pid 3452:tid 608] AH00094: Command line: 'c:\xampp\apache\bin\httpd.exe -d C:/xampp/apache' [Fri Jul 12 06:52:39.392308 2019] [mpm_winnt:notice] [pid 3452:tid 608] AH00418: Parent: Created child process 10156 [Fri Jul 12 06:52:39.860975 2019] [ssl:warn] [pid 10156:tid 552] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Fri Jul 12 06:52:39.907811 2019] [ssl:warn] [pid 10156:tid 552] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Fri Jul 12 06:52:39.939056 2019] [mpm_winnt:notice] [pid 10156:tid 552] AH00354: Child: Starting 150 worker threads. [Fri Jul 12 08:48:50.164581 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51898] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10943 [Fri Jul 12 08:48:50.165582 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51898] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10949 [Fri Jul 12 08:50:48.160631 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51921] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10943 [Fri Jul 12 08:50:48.161629 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51921] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10949 [Fri Jul 12 08:50:52.096357 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51921] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10943, referer: http://127.0.0.1/simple_invoices/ [Fri Jul 12 08:50:52.096357 2019] [php7:warn] [pid 10156:tid 1892] [client 127.0.0.1:51921] PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\xampp\htdocs\simple_invoices\library\HTMLPurifier\HTMLPurifier.standalone.php on line 10949, referer: http://127.0.0.1/simple_invoices/

fearless359 commented 5 years ago

it is tmp/logs/php.log

johnanastasio17 commented 5 years ago

php.log

fearless359 commented 5 years ago

In SI, on the top right, click on the About link, and then again on the About link that is in the list of the page that comes up. Tell me the Version line value (both number and date).

johnanastasio17 commented 5 years ago

Version: 2019.1.1

Last change on change log: 2019-03-21 - 2019.1.1 Remove last use of ZendDb ($zendDb), replacing it with PdoDb. Use ajax for customer and product tables. Update Symphony and related files.

fearless359 commented 5 years ago

Please test as follows:

1) Copy attached file, Havings.zip. 2) Unzip content (Havings.php) file into the SI Inc/Claz directory. 3) Delete the tmp/logs/php.log file. 4) Run your test of report that isn't working. Should still fail but will write debug info into a new tmp/logs/php.log file. Verify this and if so, send me that tmp/logs/php.log file. 5) If nothing in the php.log file (or file doesn't exist), tell me which report you are testing assuming it still fails.

Havings.zip

johnanastasio17 commented 5 years ago

New php log, I tried running statement of invoices and it comes up with nothing listed. Tried creating a new invoice and it just hangs. No error shown on screen, status bar just says waiting for 127.0.0.1 php.log

fearless359 commented 5 years ago

I can't replicate the issue with an extra AND being appended to the HAVING clause on the SQL statement. But this is the result of the logic thinking there are multiple HAVING tests when there is actually only one. I added a is_countable() test on the off chance that the version of PHP you are one is trying to count and object that isn't countable. Not sure this is the case but it is worth a try.

On the other hand, I did find an issue with logic that sets up the HAVING clause for logic from the Statement of Invoices and fixed this. So the report not properly filters by date. This issue probably exists in other reports or parts of the code, so they will be fixed also. I have attached the Fixes.zip file with three files that need to be stored in the proper directories:

report_summary.php - stores in modules/reports directory. Invoice.php and Havings.php - store in Inc/Claz directory.

Fixes.zip

Delete the php.log file and then test this. Let me know how it works along with attaching the new php.log file.

johnanastasio17 commented 5 years ago

I started from scratch, (new install into a new directory NO errors on setup). Since the dates seem to be an issue I exported just the products & customer tables from my working install on my old computer into this new clean install. Was able to create a new invoice using the imported products to an imported customer. Tried to run "Statement of Invoices" report and got no report back or error messages. Added the 3 files from the "Fixes.zip" posted above and i got php errors, Log attached below.

php.log

fearless359 commented 5 years ago

I didn't realize that the is_countable() function is PHP 7.3.0 or greater. That is why you get an error. The attached Fixes2.zip file has two files to load in the Inc/Claz directory. Let me know if this helps.

Fixes2.zip

johnanastasio17 commented 5 years ago

Report comes up and shows invoice, but only if I don't tick the show open invoices only. If I tick the box, no invoices are shown even though the 1 invoice I have in there is not paid. So I tried to create a new invoice but that gives a php error (log attached) I really feel like a huge p.i.t.a. hopefully this all helps you along the way :-) John php.log

fearless359 commented 5 years ago

Sorry for delay in responding. Been taking care of the homefront. Anyway, I suspect the reason that you don't see any unpaid invoices is that the "owing" field in the si_invoices table is 0 on all invoices. The report performs a selection for unpaid invoices based on the field <> 0. When selected, it recalculates the amount owed so the correct value displays. Verify if this is indeed the case. If so, I'll see if I can't create a SQL query for your to run that will set these fields correctly.