<?PHP
#
#   FILE:  CalendarEvents_EventFactory.php
#
#   Part of the Collection Workflow Integration System (CWIS)
#   Copyright 2016 Edward Almasy and Internet Scout Research Group
#   http://scout.wisc.edu/cwis/
#

class CalendarEvents_EventFactory extends ResourceFactory
{
    /**
    * Class constructor.
    */
    public function __construct()
    {
        $CalPlugin = $GLOBALS["G_PluginManager"]->GetPlugin("CalendarEvents");

        $SchemaId = $CalPlugin->GetSchemaId();
        parent::__construct($SchemaId);

        $this->Schema = new MetadataSchema($SchemaId);
    }

    /**
    * Get the first month that contains an event.
    * @param bool $ReleasedOnly Set to FALSE to consider all events.
    * @return Returns the month and year of the first month that contains an
    *     event.
    */
    public function GetFirstMonth($ReleasedOnly=TRUE)
    {
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $ReleaseFlagName = $this->Schema->GetFieldByName("Release Flag")->DBFieldName();

        # construct the base query for the first month that contains an event
        $Query = "SELECT MIN(".$StartDateName.") AS FirstMonth "
            ."FROM Resources "
            ."WHERE ResourceId >= 0 "
            ."AND SchemaId = ".$this->Schema->Id()." "
            ."AND ".$StartDateName." != 0";

        # add the release flag constraint if necessary
        if ($ReleasedOnly)
        {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # execute the query
        $FirstMonth = $this->DB->Query($Query, "FirstMonth");

        # convert the first month to its timestamp
        $Timestamp = strtotime($FirstMonth);

        # return NULL if there is no first month
        if ($Timestamp === FALSE)
        {
            return NULL;
        }

        # normalize the month and return
        return date("F Y", $Timestamp);
    }

    /**
    * Get the last month that contains an event.
    * @param bool $ReleasedOnly Set to FALSE to consider all events.
    * @return Returns the month and year of the last month that contains an
    *     event.
    */
    public function GetLastMonth($ReleasedOnly=TRUE)
    {
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();
        $ReleaseFlagName = $this->Schema->GetFieldByName("Release Flag")->DBFieldName();

        # constrcut the base query for the last month that contains an event
        $Query = "SELECT MAX(".$EndDateName.") AS LastMonth "
            ."FROM Resources "
            ."WHERE ResourceId >= 0 "
            ."AND SchemaId = ".$this->Schema->Id();

        # add the release flag constraint if necessary
        if ($ReleasedOnly)
        {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # execute the query
        $LastMonth = $this->DB->Query($Query, "LastMonth");

        # convert the last month to its timestamp
        $Timestamp = strtotime($LastMonth);

        # return NULL if there is no last month
        if ($Timestamp === FALSE)
        {
            return NULL;
        }

        # normalize the month and return
        return date("F Y", $Timestamp);
    }

    /**
    * Get the event IDs for all events in the given month.
    * @param string $Month Month and year of the events to get.
    * @param bool $ReleasedOnly Set to TRUE to only get released events. This
    *      parameter is optional and defaults to TRUE.
    * @return Returns an array of event IDs.
    */
    public function GetEventIdsForMonth($Month, $ReleasedOnly=TRUE)
    {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();

        # get the month range
        $Date = strtotime($Month);
        $SafeMonthStart = addslashes(date("Y-m-01 00:00:00", $Date));
        $SafeMonthEnd = addslashes(date("Y-m-t 23:59:59", $Date));

        # leaving this here until query testing with more data can be done
        $Condition = " ((".$StartDateName." >= '".$SafeMonthStart."' ";
        $Condition .= " AND ".$StartDateName." <= '".$SafeMonthEnd."') ";
        $Condition .= " OR (".$EndDateName." >= '".$SafeMonthStart."' ";
        $Condition .= " AND ".$EndDateName." <= '".$SafeMonthEnd."') ";
        $Condition .= " OR (".$StartDateName." < '".$SafeMonthStart."' ";
        $Condition .= " AND ".$EndDateName." >= '".$SafeMonthStart."')) ";

        # retrieve event IDs and return
        return $this->FetchEventIds($Condition, $ReleasedOnly);
    }


    /**
    * Get the event IDs for all upcoming events.
    * @param bool $ReleasedOnly Set to TRUE to only get released events. This
    *      parameter is optional and defaults to TRUE.
    * @param int $Limit Set to a positive integer to limit the amount of event
    *      IDs returned.
    * @return Returns an array of event IDs.
    */
    public function GetEventIdsForUpcomingEvents($ReleasedOnly=TRUE, $Limit=NULL)
    {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();
        $AllDayName = $this->Schema->GetFieldByName("All Day")->DBFieldName();

        # get the month range
        $SafeMonthStart = addslashes(date("Y-m-d 00:00:00"));
        $SafeMonthStartWithTime = addslashes(date("Y-m-d H:i:s"));
        $SafeMonthEnd = addslashes(date("Y-m-t 23:59:59"));

        # all day and is from today until the end of the month or not all day
        # and is from right now (including time) until the end of the month
        $Condition = " ((".$AllDayName." = 1
                         AND ".$StartDateName." >= '".$SafeMonthStart."')
                       OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                         AND ".$StartDateName." >= '".$SafeMonthStartWithTime."'))";

        # retrieve event IDs and return
        return $this->FetchEventIds($Condition, $ReleasedOnly, $Limit);
    }

    /**
    * Get counts for events for the future, occurring, and past events.
    * @param bool $ReleasedOnly Set to FALSE to get the counts for all events.
    * @return Returns the counts for future, occurring, and past events.
    */
    public function GetEventCountsByTense($ReleasedOnly=TRUE)
    {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();
        $AllDayName = $this->Schema->GetFieldByName("All Day")->DBFieldName();
        $ReleaseFlagName = $this->Schema->GetFieldByName("Release Flag")->DBFieldName();

        # get the month range
        $SafeTodayStart = addslashes(date("Y-m-d 00:00:00"));
        $SafeTodayWithTime = addslashes(date("Y-m-d H:i:s"));
        $SafeTodayEnd = addslashes(date("Y-m-d 23:59:59"));

        # construct the first part of the query
        $PastEventsCount = $this->DB->Query("
            SELECT COUNT(*) as EventCount FROM Resources
            WHERE `ResourceId` >= 0
            AND `SchemaId` = ".$this->Schema->Id()
            ." ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : "")."
            AND ((`".$AllDayName."` = 1
                   AND `".$EndDateName."` < '".$SafeTodayStart."')
                 OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                   AND `".$EndDateName."` < '".$SafeTodayWithTime."'))",
            "EventCount");

        # rather than doing complex SQL query logic, just get the count of all
        # of the events and subtract the others below
        $AllEventsCount = $this->DB->Query("
            SELECT COUNT(*) as EventCount FROM Resources
            WHERE `ResourceId` >= 0
            AND `SchemaId` = '".addslashes($this->Schema->Id())."'
            ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : ""),
            "EventCount");

        $FutureEventsCount = $this->DB->Query("
            SELECT COUNT(*) as EventCount FROM Resources
            WHERE `ResourceId` >= 0
            AND `SchemaId` = '".addslashes($this->Schema->Id())."'
            ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : "")."
            AND ((`".$AllDayName."` = 1
                   AND `".$StartDateName."` > '".$SafeTodayEnd."')
                 OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                   AND `".$StartDateName."` > '".$SafeTodayWithTime."'))",
            "EventCount");

        # return the counts
        return array(
            "Past" => $PastEventsCount,
            "Occurring" => $AllEventsCount - $PastEventsCount - $FutureEventsCount,
            "Future" => $FutureEventsCount);
    }

    /**
    * Get counts for events for each month.
    * @param bool $ReleasedOnly Set to FALSE to get the counts for all events.
    * @return Returns the event counts for each month.
    */
    public function GetEventCounts($ReleasedOnly=TRUE)
    {
        # get the bounds of the months
        $FirstMonth = $this->GetFirstMonth();
        $LastMonth = $this->GetLastMonth();

        # convert the month strings to timestamps
        $FirstMonthTimestamp = strtotime($FirstMonth);
        $LastMonthTimestamp = strtotime($LastMonth);

        # print an emprty array if the timestamps aren't valid or there are no
        # events
        if ($FirstMonthTimestamp === FALSE || $LastMonthTimestamp === FALSE)
        {
            return array();
        }

        # get the boundaries as numbers
        $FirstYearNumber = intval(date("Y", $FirstMonthTimestamp));
        $FirstMonthNumber = intval(date("m", $FirstMonthTimestamp));
        $LastYearNumber = intval(date("Y", $LastMonthTimestamp));
        $LastMonthNumber = intval(date("m", $LastMonthTimestamp));

        # start off the query
        $Query = "SELECT ";

        # get the database field names for the date fields
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();
        $ReleaseFlagName = $this->Schema->GetFieldByName("Release Flag")->DBFieldName();

        # loop through the years
        for ($i = $FirstYearNumber; $i <= $LastYearNumber; $i++)
        {
            # loop through the months
            for ($j = ($i == $FirstYearNumber ? $FirstMonthNumber : 1);
                 ($i == $LastYearNumber ? $j <= $LastMonthNumber : $j < 13);
                 $j++)
            {
                $ColumnName = date("MY", mktime(0, 0, 0, $j, 1, $i));
                $LastDay = date("t", mktime(0, 0, 0, $j, 1, $i));

                $Start = $i."-".$j."-01 00:00:00";
                $End = $i."-".$j."-".$LastDay." 23:59:59";

                $Query .= " sum((".$StartDateName." >= '".$Start."' ";
                $Query .= " AND ".$StartDateName." <= '".$End."') ";
                $Query .= " OR (".$EndDateName." >= '".$Start."' ";
                $Query .= " AND ".$EndDateName." <= '".$End."') ";
                $Query .= " OR (".$StartDateName." < '".$Start."' ";
                $Query .= " AND ".$EndDateName." >= '".$Start."')) AS ".$ColumnName.", ";
            }
        }

        # remove the trailing comma
        $Query = rtrim($Query, ", ") . " ";

        # add the table name
        $Query .= "FROM Resources WHERE ResourceId >= 0"
               ." AND SchemaId = ".$this->Schema->Id();

        # add the release flag constraint if necessary
        if ($ReleasedOnly)
        {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # this may be a very long query and could have very long results
        # avoid caching it
        $PreviousSetting = $this->DB->Caching();
        $this->DB->Caching(FALSE);
        $this->DB->Query($Query);
        $Result = $this->DB->FetchRow();
        $this->DB->Caching($PreviousSetting);

        return $Result;
    }

    /**
    * Filter a list of events to either return those with a specified
    * owner or those that have no owner.
    * @param array $EventIds List of event IDs to filter.
    * @param int|NULL $OwnerId Desired OwnerId or NULL to for events
    *   with no owner.
    * @return array of filtered EventIds.
    */
    public function FilterEventsByOwner($EventIds, $OwnerId)
    {
        $OwnerFieldId = $this->Schema->GetField("Owner")->Id();

        if ($OwnerId === NULL)
        {
            # get the list of all events that have an owner
            $this->DB->Query(
                "SELECT ResourceId FROM ResourceUserInts"
                ." WHERE FieldId = ".$OwnerFieldId );
            $OwnedEventIds = $this->DB->FetchColumn("ResourceId");

            # and remove those events from the list we were given
            $Result = array_diff($EventIds, $OwnedEventIds);
        }
        else
        {
            # get the list of events having the specified owner
            $ToMatch = array();
            $ToMatch[$OwnerFieldId] = $OwnerId;

            $OwnedEventIds = $this->GetMatchingResources(
                $ToMatch, TRUE, FALSE);

            # and filter out any events that weren't on that list
            $Result = array_intersect($EventIds, $OwnedEventIds);
        }

        return $Result;
    }

    /**
    * Fetch event IDs.
    * @param string $Condition Additional SQL condition string.
    * @param bool $ReleasedOnly Set to TRUE to only return released events.
    * @param int $Limit Set to a positive integer to limit the amount of event
    *      IDs returned.
    * @return Returns an array of matched event IDs.
    */
    protected function FetchEventIds($Condition=NULL, $ReleasedOnly=TRUE, $Limit=NULL)
    {
        $TitleName = $this->Schema->GetFieldByName("Title")->DBFieldName();
        $StartDateName = $this->Schema->GetFieldByName("Start Date")->DBFieldName();
        $EndDateName = $this->Schema->GetFieldByName("End Date")->DBFieldName();

        # construct the first part of the query
        $Query = "SELECT ResourceId FROM Resources "
                ."WHERE ResourceId >= 0 AND SchemaId = ".$this->Schema->Id();

        # if only released events should be returned
        if ($ReleasedOnly)
        {
            $ReleaseFlagName = $this->Schema->GetFieldByName(
                "Release Flag")->DBFieldName();
            $Query .= " AND ".$ReleaseFlagName." = '1' ";
        }

        # add the condition string if given
        if (!is_null($Condition))
        {
            $Query .= " AND " . $Condition;
        }

        # add sorting parameters
        $Query .= " ORDER BY ".$StartDateName." ASC, ";
        $Query .= " ".$EndDateName." ASC, ";
        $Query .= " ".$TitleName." ASC ";

        # add the limit if given
        if (!is_null($Limit))
        {
            $Query .= " LIMIT " . intval($Limit). " ";
        }

        # execute the query
        $this->DB->Query($Query);

        # return the IDs
        return $this->DB->FetchColumn("ResourceId");
    }

    private $Schema = NULL;
}