Search in support tickets

#953 – 1525 Incorrect DATETIME value: '2021'

Posted in ‘Support’
This is a public ticket. Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.
Friday, 06 August 2021 08:46 CEST
riofrio
Hello!

I have a problem with the invoice component. It does not appear, it shows an error.
An error has occurred.

1525 Incorrect DATETIME value: '2021'

PHP Built On Linux server20.megacp.com 3.10.0-1160.36.2.el7.x86_64 # 1 SMP Wed Jul 21 11:57:15 UTC 2021 x86_64
Database Type mysql
Database Version 8.0.23
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_0900_ai_ci
PHP Version 7.4.21
Web Server Apache
WebServer to PHP Interface fpm-fcgi
Joomla! Version of Joomla! 3.9.28 Stable [Amani] 6-July-2021 15:00 GMT
Joomla! Platform Version Joomla Platform 13.1.0 Stable [Curiosity] 24-Apr-2013 00:00 GMT

What is the solution?
Custom Fields
Joomla Version
3.9.28
PHP Version
7.4.21
Product
Invoicing
Product Version
3.2
Attachment
Friday, 06 August 2021 08:51 CEST
loic
Hello,

Did you just install the component before the crash ? Or were you using it when the crash happened ?

Best regards.
 
Friday, 06 August 2021 08:56 CEST
riofrio
I first created the page on a demo page, it worked properly there. The database was older: 5.7.29-log

I moved to another storage location, there everything works only the invoice does not.

I made a subdomain, put a virgin joomla there, and installed invoic, but it still doesn't work.
Friday, 06 August 2021 08:58 CEST
riofrio
This is the picture of the old page, everything is okay here
Attachment
Friday, 06 August 2021 09:24 CEST
loic
Can you give me the version of the database you are using ?

I think it is MySQL 8 or MariaDb 10, but I wish to be sure.

Best regards.
 
Friday, 06 August 2021 09:25 CEST
riofrio
MySQL verzió 8.0.26
Friday, 06 August 2021 09:46 CEST
loic
I will run some test today and come back to you.
 
Friday, 06 August 2021 14:05 CEST
loic
Hello,

So, that error is indeed caused by MySQL8, but it is because of a issue in the code that was tolerated by MySQL5.7.

To fix that, open the file administrator/components/com_invoicing/models/invoices.php, and find the function 'getNumberAndSumBySpecifiedTime', replace the line 463 :
$condition = "`created_on` > '".date("Y",strtotime('today - 2 months'))."' AND `created_on` < '".date("Y-m-01")."'";

By that line
$condition = "`created_on` > '".date("Y-m-01",strtotime('today - 2 months'))."' AND `created_on` < '".date("Y-m-01")."'";


Follow that fix, you will have another issue -> 1525 Incorrect DATETIME value: '--01'

In administrator/components/com_invoicing/views/cpanel/view.html.php, and find the block of lines from line 100 to 111, replace :
$caperdaylast31days = array();
$caperdaylast31days = $invoiceModel->getCAHTbetweenDaysFilters($filters->dateFilterFrom,$filters->dateFilterTo);
$dates = InvoicingHelperDates::getDatesBetween($filters->dateFilterFrom, $filters->dateFilterTo); 
$dailypoints = InvoicingHelperDates::fillDatesWithZero($dates,$caperdaylast31days);
$this->assignRef('dailypoints',$dailypoints);
		
$capermonth = array(); 
$capermonth = $invoiceModel->getCAHTbetweenMonthsFilters($filters->monthto,$filters->yearto,$filters->monthfrom,$filters->yearfrom);
		
$months = InvoicingHelperDates::getMonthsBetween($filters->monthfrom, $filters->monthto,$filters->yearfrom,$filters->yearto);   
$monthlypoints = InvoicingHelperDates::fillMonthsWithZero($months,$capermonth);
$this->assignRef('monthlypoints',$monthlypoints);


By
try {
  $caperdaylast31days = array();
  $caperdaylast31days = $invoiceModel->getCAHTbetweenDaysFilters($filters->dateFilterFrom,$filters->dateFilterTo);
  $dates = InvoicingHelperDates::getDatesBetween($filters->dateFilterFrom, $filters->dateFilterTo); 
  $dailypoints = InvoicingHelperDates::fillDatesWithZero($dates,$caperdaylast31days);
  $this->assignRef('dailypoints',$dailypoints);
		
  $capermonth = array(); 
  $capermonth = $invoiceModel->getCAHTbetweenMonthsFilters($filters->monthto,$filters->yearto,$filters->monthfrom,$filters->yearfrom);
		
  $months = InvoicingHelperDates::getMonthsBetween($filters->monthfrom, $filters->monthto,$filters->yearfrom,$filters->yearto);   
  $monthlypoints = InvoicingHelperDates::fillMonthsWithZero($months,$capermonth);
  $this->assignRef('monthlypoints',$monthlypoints);
} catch (exception $e) {
  $this->assign('dailypoints',0);
  $this->assign('monthlypoints',0);
}


Please note that the dashboard will not work anymore, I will look for a solution for next week.

Best regards.
 
Friday, 06 August 2021 15:15 CEST
riofrio
I solved it, I just had to type "Y-m-d" instead of "Y"

case "lastmonth" :
$condition = "`created_on` > '".date("Y",strtotime('today - 2 months'))."' AND `created_on` < '".date("Y-m-01")."'";

"Y" = "Y-m-d"

case "lastmonth" :
$condition = "`created_on` > '".date("Y-m-d",strtotime('today - 2 months'))."' AND `created_on` < '".date("Y-m-01")."'";

nothing else I fixed and it works!
Friday, 06 August 2021 15:32 CEST
loic
That a great news !

The other issue happen if you don't have any invoice on my installation, so I will still check that.

Best regards.
 
Friday, 06 August 2021 15:48 CEST
riofrio
I have another problem.
The coupon.
We do not have the euro, but the forint. 1000 HUF = about 3 Euro.

If I give, e.g. 10% discount for 1000 forints, you deduct the 10% well, but if the amount is 2000 or 5000 or more, you calculate incorrectly. The invoice contains 1, 2.

So, it counts well up to 1000 forints, incorrectly over 1000 forints.

How can this be improved?
Attachment
Monday, 09 August 2021 14:01 CEST
riofrio
It counts well up to 1999 forints but incorrectly from 2000 forints

Attachment
Tuesday, 10 August 2021 13:26 CEST
loic
Hello,

I never tried a coupon with a value that high.

I will test that and come back to you.

Best regards.
 

Please rate this ticket

Help us improve our support services by rating this ticket from one to five stars, according to how much you are satisfied from the handling of this ticket, one being not satisfied and five being very satisfied.