Events Module - getEntriesByMonth

I was looking at the calendar code of the events and noticed the following awfull function

    function getEntriesByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth)
    {
        $aEvents = array ();
        $iDays = cal_days_in_month(CAL_GREGORIAN, $iMonth, $iYear);
        for ($iDay=1 ; $iDay <= $iDays ; ++$iDay) {
            $a = $this->getAll ("SELECT *, $iDay AS `Day`
                FROM `" . $this->_sPrefix . "main`
                WHERE ((`EventEnd` >= UNIX_TIMESTAMP('$iYear-$iMonth-$iDay 00:00:00')) AND (`EventStart` <= UNIX_TIMESTAMP('$iYear-$iMonth-$iDay 23:59:59')))
                    AND `Status` = 'approved'");
            if ($a)
                $aEvents = array_merge($aEvents, $a);
        }
        return $aEvents;
    }

Is there any reason why the DB has to be queried more or less 30 times to display a calendar? For what I've seen this is only used in the Events module and nowhere else.

Anyone?

Still wondering what is wrong with the following function and why it's not used for the Events module, that onyl does 1 DB lookup, rather than 30!

    function getEntriesByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth)
    {
        return $this->getAll ("SELECT *, DAYOFMONTH(FROM_UNIXTIME(`{$this->_sFieldCreated}`)) AS `Day`
            FROM `" . $this->_sPrefix . $this->_sTableMain . "`
            WHERE `{$this->_sFieldCreated}` >= UNIX_TIMESTAMP('$iYear-$iMonth-1') AND `{$this->_sFieldCreated}` < UNIX_TIMESTAMP('$iNextYear-$iNextMonth-1') AND `{$this->_sFieldStatus}` = 'approved'");
    }

I suspect that this code was more complicated in the past and it was some PHP routine in the loop, it maybe that later additional code was removed, but the loop was not optimised for one SQL query.

Thank you for pointing this out, we will try to implement it in the upcoming version:

https://github.com/boonex/dolphin.pro/issues/60

From what I can see, removing the function from the BxEventsDb.php is sufficient. This way it will use the function from its parent (BxDolTwigModuleDb.php).

 This is incorrect and will display the creation date, rather than the event date. I corrected the function below to use the EventStart date.

    function getEntriesByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth)
    {
        return $this->getAll ("SELECT *, DAYOFMONTH(FROM_UNIXTIME(`EventStart`)) AS `Day`
            FROM `" . $this->_sPrefix . $this->_sTableMain . "`
            WHERE `{$this->_sFieldCreated}` >= UNIX_TIMESTAMP('$iYear-$iMonth-1') AND `{$this->_sFieldCreated}` < UNIX_TIMESTAMP('$iNextYear-$iNextMonth-1') AND `{$this->_sFieldStatus}` = 'approved'");
    }

Sorry, your function doesn't work, so we'll stick to the original one.

There is custom complicated function for Events because there is EventStart and EventEnd fields and event must be displayed for all dates during all these dates. Try to create event for two or more days long and look at the calendar - it will be displayed for all days from EventStart to EventEnd.

You're right, didn't think of that.

Wrote a new function that does 1 DB call and the rest in PHP.

    function getEntriesByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth)
    {
        $aEvents = $this->getAll ("SELECT *, DAYOFMONTH(FROM_UNIXTIME(`EventStart`)) AS `Day`,
            DAYOFMONTH(LAST_DAY('2015-10-1')) AS current_month,
            (
            case
                when MONTH(FROM_UNIXTIME(`EventStart`)) = MONTH(FROM_UNIXTIME(`EventEnd`)) then
                    DAYOFMONTH(FROM_UNIXTIME(`EventEnd`)) - DAYOFMONTH(FROM_UNIXTIME(`EventStart`)) + 1
                when MONTH(FROM_UNIXTIME(`EventStart`)) < MONTH(FROM_UNIXTIME(`EventEnd`)) then
                    DAYOFMONTH(LAST_DAY(FROM_UNIXTIME(`EventStart`))) - DAYOFMONTH(FROM_UNIXTIME(`EventStart`)) + 1
                else
                    1
            end
            ) AS `Days`
            FROM `" . $this->_sPrefix . $this->_sTableMain . "`
            WHERE `EventEnd` >= UNIX_TIMESTAMP('$iYear-$iMonth-1')
            AND `EventStart` <= UNIX_TIMESTAMP('$iNextYear-$iNextMonth-1')
            AND `{$this->_sFieldStatus}` = 'approved'");

        foreach($aEvents as $aEvent)
        {
            for ($iTime=$iDay=0; $iDay < $aEvent['Days'] && $aEvent['Day'] <= $aEvent['current_month']; ++$iDay)
            {
                $aEvent['Day'] = $aEvent['Day'] + $iTime;
                $aAllEvents[] = $aEvent;

                if($iTime == 0)
                    $iTime = 1;
            }
        }

        return $aAllEvents;
    }

There was something wrong with the logic and events spanning multiple months. The following code fixes that.

    function getEntriesByMonth ($iYear, $iMonth, $iNextYear, $iNextMonth)
    {
        $aEvents = $this->getAll ("SELECT  *
            ,(
                case
                    when $iMonth = MONTH(FROM_UNIXTIME(`EventStart`)) then
                        DAYOFMONTH(FROM_UNIXTIME(`EventStart`))
                    else
                        1
                end
            ) AS `Day`
           ,(
                case
                    when MONTH(FROM_UNIXTIME(`EventStart`)) = MONTH(FROM_UNIXTIME(`EventEnd`)) then
                        DAYOFMONTH(FROM_UNIXTIME(`EventEnd`)) - (DAYOFMONTH(FROM_UNIXTIME(`EventStart`)) -1)
                    when MONTH(FROM_UNIXTIME(`EventStart`)) < MONTH(FROM_UNIXTIME(`EventEnd`)) then
                        case
                            when $iMonth = MONTH(FROM_UNIXTIME(`EventStart`)) then
                                (DAYOFMONTH(LAST_DAY(FROM_UNIXTIME(`EventStart`))) - DAYOFMONTH(FROM_UNIXTIME(`EventStart`))) +1
                            when $iMonth = MONTH(FROM_UNIXTIME(`EventEnd`)) then
                                DAYOFMONTH(FROM_UNIXTIME(`EventEnd`))
                            else
                                DAYOFMONTH(LAST_DAY('$iYear-$iMonth-1'))
                        end
                    else
                        1
                end
            ) AS `Days`
            FROM `bx_events_main`
            WHERE `EventEnd` >= UNIX_TIMESTAMP('$iYear-$iMonth-1')
                AND `EventStart` <= UNIX_TIMESTAMP('$iNextYear-$iNextMonth-1')
                AND `Status` = 'approved'");

        foreach($aEvents as $aEvent)
        {
            for ($iTime=$iDay=0; $iDay < $aEvent['Days'] && $aEvent['Day'] <= 31; ++$iDay)
            {
                $aEvent['Day'] = $aEvent['Day'] + $iTime;
                $aAllEvents[] = $aEvent;

                if($iTime == 0)
                    $iTime = 1;
            }
        }

        return $aAllEvents;
    }