Bug with Groupby Expected Close Date (month) for report charts

There is an error with the dates within charts when using Group by Expected Close Date (month) when creating a report, as you can see from the image I have attached the dates are not in order. Running vTiger 6.3 Open Source

Let me know if you require more info.

I would recommend the following:

1. How does the date-format change influence the display?
2. Is this reproducible on 6.4.0 as well?

Added bug label

1. I assume by date-format you mean changing through the user profile? This didn't change the ordering of the data.

2. Yes I have reproduced in 6.4.0 and have provided screenshots below.

dd-mm-yyyy

yyyy-mm-dd

mm-dd-yyyy

I tried with excepted close-dates shown in graph it worked on our demo instance. Suspect the combination of amount and forecast selection to be influencing the behaviour.

Can you please alert the data-fields (limit to Record count) is there any difference?

There is unfortunately no difference, here is the result, and settings I am applying. I also removed all conditions, and again the data are still not ordered correctly.

I've same problem with invoice module, grouped for data.

Thanks for the screenshot - Expected Close Date (month) seem to be troubling. I tried Expected Close Date earlier (sorry for the oversight)

Analysis

modules/Reports/ReportRun.php - getColumnSQL - is transforming (MY) month-year type to date_format('%M %Y') which results with value friendly for php date transformation but creates ambiguous sorting (since %M - yield Monthname)!

Workaround

Since the impact of changing (modules/Reports/ReportRun.php - getColumnSQL) for MY type-handling would be deeper. Let us aim for a workaround fix now.

In modules/Reports/models/Chart.php

function getReportColumnSQL() {
	// ...
	$columnSQL = $reportRunObject->getColumnSQL($selectedfields);
	
	// ADD THIS
	switch ($selectedfields[count($selectedfields)-1]) {
	case 'MY':
	        $columnSQL = str_replace('%M', '%m', $columnSQL); // %M (yields Jan), %m - 01
	        break;
	}
	// END
	
	$reportRunObject->append_currency_symbol_to_value = $append_currency_symbol_to_value;
	// ...
}

Make Date UIType aware for new incoming value format.

In modules/Vtiger/uitypes/Date.php

public static function getDisplayDateValue($date) {
	// ADD THIS
	// Handle (MonthNumber Year) format value conversion.
	if (preg_match("/([0-9]{1,2}) ([0-9]{1,4})/", $date, $m)) {
		return date("M Y", strtotime($m[2].'-'.$m[1].'-'.'1'));
	}
	// END
	$date = new DateTimeField($date);
	return $date->getDisplayDate();
}


public static function getDisplayDateTimeValue($dateTime) {
	// ADD THIS
	// Handle (MonthNumber Year) format value conversion.
	if (preg_match("/([0-9]{1,2}) ([0-9]{1,4})/", $date, $m)) {
        return date("M Y", strtotime($m[2].'-'.$m[1].'-'.'1'));
	}
	// END
	$date = new DateTimeField($dateTime);
	return $date->getDisplayDateTimeValue();
}

I have applied your fix, it seems to work fine if you have only the same year in the chart.

Also when you click the bar to go to the opportunity you get the following.. I clicked one of the 2016 records.

Thank you for the update. It is a quick fix - please list areas where it breaks, it can help in testing it further.

It seems at the moment it sorts only by month and not month + year. Aside from that and what I mentioned above I can't see any other bugs :)

Is there any plans to include the fix for this bug in the next release of vTiger?

Milestone changed to 6.5.0

yes.

Fix integrated 7978ef23

Status changed to closed

@prasad I have tried this fix and I am still getting the same problem as before.

Status changed to reopened

mentioned in issue #555 (closed)

mentioned in issue #597 (closed)

mentioned in issue #841 (closed)

As you noticed, the workaround submited by @prasad doesn't order result by year/month but by month/year. The code that works for me is the following:

In modules/Reports/models/Chart.php

function getReportColumnSQL() {
    // ...
    $columnSQL = $reportRunObject->getColumnSQL($selectedfields);

    // ADD THIS
    switch ($selectedfields[count($selectedfields)-1]) {
    case 'MY':
            // Change from '%M %Y' to '%Y %m - 'Oct 2018' -> '2018 10'
            $columnSQL = str_replace('%Y', '%m', $columnSQL);
            $columnSQL = str_replace('%M', '%Y', $columnSQL);
            break;
    }
    // END

    $reportRunObject->append_currency_symbol_to_value = $append_currency_symbol_to_value;
    // ...
}

Make Date UIType aware for new incoming value format for date and dateTime :

In modules/Vtiger/uitypes/Date.php

public static function getDisplayDateValue($date) {
    // ADD THIS
    // Handle (Year MonthNumber) format value conversion.
    if (preg_match("/([0-9]{1,4}) ([0-9]{1,2})/", $date, $m)) {
        return date("M Y", strtotime($m[1].'-'.$m[2].'-'.'1'));
    }
    // END
    $date = new DateTimeField($date);
    return $date->getDisplayDate();
}


public static function getDisplayDateTimeValue($dateTime) {
    // ADD THIS
    // Handle (MonthNumber Year) format value conversion.
    if (preg_match("/([0-9]{1,4}) ([0-9]{1,2})/", $dateTime, $m)) {
        return date("M Y", strtotime($m[1].'-'.$m[2].'-'.'1'));
    }
    // END
    $date = new DateTimeField($dateTime);
    return $date->getDisplayDateTimeValue();
}

mentioned in issue #1731 (closed)

mentioned in issue #1731 (closed)

mentioned in issue #1731 (closed)

mentioned in issue #1731 (closed)

mentioned in issue #1951 (closed)