Improving Date Filtering

Grocery CRUD Community Edition
1

Hello,

I have generated a datagrid with a few date columns. To filter a single date works fine with a pop-up calendar to assist with formatting.

I am looking to improve this filtering by adding DATE RANGE filtering. I have generated procedure and call in MySQL 5.x, that gives me a start and end date based on a select option:

DELIMITER //

CREATE PROCEDURE get_date_range(
    IN range_type VARCHAR(20),
    OUT start_date DATE,
    OUT end_date DATE
)
BEGIN
    DECLARE today DATE;
    SET today = CURDATE();

    CASE range_type
        WHEN 'today' THEN
            SET start_date = today;
            SET end_date = today;

        WHEN 'yesterday' THEN
            SET start_date = DATE_SUB(today, INTERVAL 1 DAY);
            SET end_date = start_date;

        WHEN 'tomorrow' THEN
            SET start_date = DATE_ADD(today, INTERVAL 1 DAY);
            SET end_date = start_date;

        WHEN 'this week' THEN
            SET start_date = DATE_SUB(today, INTERVAL WEEKDAY(today) DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'this week to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL WEEKDAY(today) DAY);
            SET end_date = today;

        WHEN 'last week' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL WEEKDAY(today) DAY), INTERVAL 7 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'next week' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL WEEKDAY(today) DAY), INTERVAL 7 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'this month' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY);
            SET end_date = LAST_DAY(today);

        WHEN 'this month to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY);
            SET end_date = today;

        WHEN 'last month' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY), INTERVAL 1 MONTH);
            SET end_date = LAST_DAY(start_date);

        WHEN 'next month' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY), INTERVAL 1 MONTH);
            SET end_date = LAST_DAY(start_date);

        WHEN 'last 30 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 30 DAY);
            SET end_date = today;

        WHEN 'last 60 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 60 DAY);
            SET end_date = today;

        WHEN 'last 90 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 90 DAY);
            SET end_date = today;

        WHEN 'this year' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        WHEN 'this year to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY);
            SET end_date = today;

        WHEN 'last year' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY), INTERVAL 1 YEAR);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        WHEN 'next year' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY), INTERVAL 1 YEAR);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        ELSE
            SET start_date = NULL;
            SET end_date = NULL;
    END CASE;
END //

DELIMITER ;


CALL get_date_range('this year to date', @start_date, @end_date);
SELECT @start_date AS start_date, @end_date AS end_date;

The goal is to show a select menu of the options (with CUSTOM RANGE - to manually enter start and dates), then it produces the proper date range filtered set.

Anyone ever done this? Feedback would be appreciated.

2

I’d appreciate some feedback if this can be done in GroceryCrud or possible this can be added as a new feature. Here is an example:

https://dwdataconcepts.com/date_options.html

In the same way, I would link to improved the Quick Search on a TEXT Column by adding a Multi-Select menu which the options the column values GROUPED. Here is an example:

https://dwdataconcepts.com/text_options.html

In both cases, it would be nice to add a new line to code in this fashion:

$crud->setTable(‘customers’);
$crud->setSubject(‘Customer’, ‘Customers’);
$crud->columns([‘customerName’,‘phone’,‘addressLine1’,‘creditLimit’, ‘birthdate’,‘anniversaryDate’,‘cutomerType’]);
$crud->columnsQuickSearch([‘’,‘’,‘’,‘’, ‘rangeOptions’,‘rangeOptions’,‘groupOptions’]);

$output = $crud->render();

If column IS NULL, it uses the DEFAULT (existing) date pop-up and keyword methed ELSE the improved filtering options.

Appreciate any thought on this.

3

I guess what are you looking for is something like this:

$crud->fieldTypeSearchColumn('my_date', 'dropdown_search', [
    date('Y-m-d') => 'Today',
    date('Y-m-d', strtotime('+1 day')) => 'Tomorrow',
    date('Y-m-d', strtotime('-1 day')) => 'Yesterday',
    date('Y-m-d', strtotime('monday this week')) => 'Monday this week',
    date('Y-m-d', strtotime('sunday this week')) => 'Sunday this week',
    date('Y-m-d', strtotime('monday last week')) => 'Monday last week',
    date('Y-m-d', strtotime('sunday last week')) => 'Sunday last week',
    date('Y-m-d', strtotime('monday next week')) => 'Monday next week',
    date('Y-m-d', strtotime('sunday next week')) => 'Sunday next week',
    date('Y-m-01') => 'First day of this month',
    date('Y-m-t') => 'Last day of this month',
    date('Y-m-01', strtotime('first day of last month')) => 'First day of last month',
    date('Y-m-t', strtotime('last day of last month')) => 'Last day of last month',
]);

If you are looking about having if statements to the quick search, currently there isn’t such functionality but I may revisit that in the future to see what I can do. Is that work-around ok for you for now?

Regards
Johnny

1 Like
4

Yeah - my request was more DATE RANGES options (ala QuickBooks reporting). That would be helpful.

5

Hello @dwdc ,

Currently date range on quick search is a bit tricky. Quick search is for a quick search value and filter search is the more advance one. On the other hand, I can suggest you a work-around that at least for now may fix the issue of date range (hopefully). You can create custom buttons above grocery crud and you can actually then refresh the page by using where condition. For example if you are using Codeigniter framework you can store it on the session. Something like this may work:

...
    public function customers()
    {
        ...

        // Just an example code, it may not work if you just copy-paste it
        $session = session();

        // Check for POST data from the buttons
        $dateFilter = $this->request->getPost('date_filter');
        if ($dateFilter) {
            // Get start and end dates based on the filter
            $dateRange = $this->getDateRange($dateFilter);

            // Store the dates in the session
            $session->set('startDate', $dateRange['start_date']);
            $session->set('endDate', $dateRange['end_date']);
        }

        // Retrieve session data
        $startDate = $session->get('startDate');
        $endDate = $session->get('endDate');

        // Apply the where condition if dates are set
        if ($startDate && $endDate) {
            $crud->where([
                'customers.my_date >=' => $startDate,
                'customers.my_date <=' => $endDate
            ]);
        }

        $crud->setTable('customers');
        $crud->setSubject('Customers');
        $crud->columns(['name', 'email', 'my_date']);

        $output = $crud->render();
       ...
    }

    /**
     * Get start and end dates based on the selected filter
     */
    private function getDateRange($dateFilter)
    {
        $today = date('Y-m-d');
        $start_date = null;
        $end_date = null;

        switch ($dateFilter) {
            case 'today':
                $start_date = $today;
                $end_date = $today;
                break;

            case 'yesterday':
                $start_date = date('Y-m-d', strtotime('-1 day', strtotime($today)));
                $end_date = $start_date;
                break;

            case 'this_week':
                $start_date = date('Y-m-d', strtotime('monday this week', strtotime($today)));
                $end_date = date('Y-m-d', strtotime('sunday this week', strtotime($start_date)));
                break;

            case 'last_week':
                $start_date = date('Y-m-d', strtotime('monday last week', strtotime($today)));
                $end_date = date('Y-m-d', strtotime('sunday last week', strtotime($start_date)));
                break;

            case 'this_month':
                $start_date = date('Y-m-01', strtotime($today));
                $end_date = date('Y-m-t', strtotime($today));
                break;

            case 'last_month':
                $start_date = date('Y-m-01', strtotime('first day of last month', strtotime($today)));
                $end_date = date('Y-m-t', strtotime('last day of last month', strtotime($start_date)));
                break;

            default:
                break;
        }

        return [
            'start_date' => $start_date,
            'end_date' => $end_date
        ];
    }

And in your HTML something like this:

<form method="post" action="<?= base_url('customers') ?>">
    <button type="submit" name="date_filter" value="today">Today</button>
    <button type="submit" name="date_filter" value="yesterday">Yesterday</button>
    <button type="submit" name="date_filter" value="this_week">This Week</button>
    <button type="submit" name="date_filter" value="last_week">Last Week</button>
    <button type="submit" name="date_filter" value="this_month">This Month</button>
    <button type="submit" name="date_filter" value="last_month">Last Month</button>
</form>

The only “ugly” thing about this is that the buttons will be outside of the datagrid and that it will refresh the page. But if you make it work then maybe we can find a way to add them somewhere within datagrid.

Let me know if that worked for you.

Regards
Johnny

1 Like
6

I will see if I can figure something out.

1 Like