<?php

define('cn_EventReportReport', 'Event_Report');

require_once 'class/DB/Grid/Grid.php';

class Event_Report extends DB_Grid
{
    function Event_Report(&$Doc, &$DBObj)
    {
        $this->DB_Grid($Doc, $DBObj);
        $this->actions['brokeragereport']              = 'doBrokerageReport';
        $this->actions['bytypereport']                 = 'doByTypeReport';
        $this->actions['noethicsreport']               = 'doNoEthicsReport';
        $this->actions['allattendeereport']            = 'doAllAttendeeReport';
        $this->actions['docCourseRenewalReportExport'] = '_doDocCourseRenewalReportExport';
    }

    function _getBaseForm($method = 'post')
    {
        require_once 'class/Form/Form.class.php';
        $form = new Form(
            $this->Doc,
            'form_' . strtolower(get_class($this->DBObj)),
            $method,
            'admin/event/eventreportshow',
            '_blank',
            array('class' => 'form form_' . strtolower(get_class($this->DBObj)))
        );
        $form->setSubmitTitle('Get Report');
        $form->setCancelType('none');
        $form->setTpl('tpl/Event/Report/form.tpl');
        return $form;
    }

    function doList()
    {
        $fBrokerage = $this->_getBaseForm();
        $fBrokerage->setTitle('Brokerage Year Report');
        $fBrokerage->addElement('select', 'year', 'Year', $this->DBObj->getEventsYears(3));
        $fBrokerage->addElement('hidden', 'action', 'brokeragereport');
        $fBrokerage->exec();

        $fByType = $this->_getBaseForm();
        $fByType->setTitle('List of all members who did and didn\'t attend programs within the selected 2 years.');
        $fByType->addElement('select', 'id_type', 'Select Event Type', $this->DBObj->getTypes());
        $fByType->addElement('select', 'year', 'Years', $this->DBObj->getEventsYears(null, true));
        $fByType->addElement('hidden', 'action', 'bytypereport');
        $fByType->exec();

        $fNoEthicsYear = 2004 + floor((date('Y') - 2006) / 3);
        $fNoEthics = $this->_getBaseForm();
        $fNoEthics->setTitle('List of members who have not completed ethics training since December 31, ' . $fNoEthicsYear);
        $fNoEthics->addElement('static',
                '',
                '',
                '<b>People who have completed the training include:</b><ul class="listul" style="text-align:left;"><li>People who participated in a New Member Orientation since Jan. 1, 2005</li>
        <li>People who have Online Ethics Requirement 2008 as an event in their member record (\'Ethics Training Complete 12.31.08\')</li>');
        $fNoEthics->addElement('hidden', 'action', 'noethicsreport');
        $fNoEthics->exec();

        $fAllAttendee = $this->_getBaseForm();
        $fAllAttendee->setTitle('Report that includes every event in ' . date('Y') . ' along with the first name, '
                                . 'last name, company, and years in industry for every attendee.');
        $fAllAttendee->addElement('hidden', 'action', 'allattendeereport');
        $fAllAttendee->exec();

        $this->_addDocCourseRenewalReportItem();
    }

    protected function _addDocCourseRenewalReportItem()
    {
        $form = $this->_getBaseForm();
        $form->setTitle('Doc Course Renewal Report');
        $form->addElement('hidden', 'action', 'docCourseRenewalReportExport');
        $form->exec();
    }

    function doBrokerageReport()
    {
        $this->Doc->setTitle('Brokerage '.(int) $_REQUEST['year'] .'Year Report');
        $aEvents = $this->DBObj->db->queryAll(
            'SELECT id, name, eventdate
            FROM mn_Event
            WHERE id_type=3 AND YEAR(eventdate)=' . (int) $_REQUEST['year']
        );
        $aAttendee = $this->DBObj->db->queryAll(
            'SELECT m.id, first_name, last_name, c.name company_name, email
            FROM mn_Member m LEFT JOIN mn_Location l ON m.id_location=l.id
                LEFT JOIN mn_Company c ON l.id_company=c.id
            WHERE m.id IN (
                SELECT DISTINCT id_member
                    FROM mn_Event e INNER JOIN mn_EventFormReq efr ON e.id=efr.id_event
                    WHERE id_type=3 AND YEAR(eventdate)=' . (int) $_REQUEST['year'] . ')'
        );
        $aEventAttendeeRaw = $this->DBObj->db->queryAll(
            'SELECT id_member, id_event
            FROM mn_Event e INNER JOIN mn_EventFormReq efr ON e.id=efr.id_event
            WHERE id_type=3 AND YEAR(eventdate)=' . (int) $_REQUEST['year']
        );
        $aEventAttendee = array();
        foreach ($aEventAttendeeRaw as $aEventAttendeeRow) {
            $aEventAttendee[$aEventAttendeeRow['id_member']][$aEventAttendeeRow['id_event']] = 1;
        }

        if ($_REQUEST['subaction'] == 'csv') {
            header('Content-Type: text/csv;');
            header('Content-Disposition: attachment; filename=no-ethics.csv;');
            echo ("Events Report {$_REQUEST['year']}\r\nAgent First Name,Agent Last Name,Company,Email,");
            foreach ($aEvents as $aEvent) echo $aEvent['name'].'-'.$aEvent['eventdate'].',';
            echo "Total classes attended, Total classes missed\r\n";
            foreach ($aAttendee as $aRow) {
                echo ('"' . $aRow['first_name'] . '","' .
                    $aRow['last_name'] . '","' .
                    $aRow['name'] . '","' .
                    $aRow['email'] . '","' );
                foreach ($aEvents as $aEvent) {
                    echo ($aEventAttendee[$aRow['id']][$aEvent['id']]?'X':'') . '","';
                }
                echo (int) count($aEventAttendee[$aRow['id']]) . '","'
                    . (int) (count($aEvents)-count($aEventAttendee[$aRow['id']]));
                echo "\"\r\n";
            }
            die();
        } else {
            $this->Doc->addContent(
                array(
                    'tpl'          => 'Event/Report/brokerage.tpl',
                    'year'         => $_REQUEST['year'],
                    'events'       => $aEvents,
                    'attendee'     => $aAttendee,
                    'attendeevent' => $aEventAttendee
                )
            );
            $this->addcsvbutton();
        }
    }

    function doByTypeReport()
    {
        $this->Doc->setTitle(
            'List of all members who did and didn\'t attend programs within the '
            . (int) $_REQUEST['year'].'-'.((int) $_REQUEST['year']+1).' years'
        );
        $aEvents = $this->DBObj->db->queryAll(
            'SELECT id, name, eventdate
            FROM mn_Event
            WHERE id_type=' . (int) $_REQUEST['id_type']
            . ' AND ' . (int) $_REQUEST['year'] . ' IN (YEAR(eventdate),YEAR(eventdate)+1)'
        );
        $aAttendee = $this->DBObj->db->queryAll(
            'SELECT m.id, first_name, last_name, c.name company_name, email, m.primary_phone, industry_experience
            FROM mn_Member m LEFT JOIN mn_Location l ON m.id_location=l.id
                LEFT JOIN mn_Company c ON l.id_company=c.id
        ');

        $aEventAttendeeRaw = $this->DBObj->db->queryAll(
            'SELECT id_member, id_event
            FROM mn_Event e INNER JOIN mn_EventFormReq efr ON e.id=efr.id_event
            WHERE id_type=' . (int) $_REQUEST['id_type']
            . ' AND ' . (int) $_REQUEST['year'] . ' IN (YEAR(eventdate),YEAR(eventdate)+1)'
        );
        $aEventAttendee = array();
        foreach ($aEventAttendeeRaw as $aEventAttendeeRow) {
            $aEventAttendee[$aEventAttendeeRow['id_member']][$aEventAttendeeRow['id_event']] = 1;
        }
        if ($_REQUEST['subaction']=='csv') {
            header('Content-Type: text/csv;');
            header('Content-Disposition: attachment; filename=no-ethics.csv;');
            echo ("Events Report {$_REQUEST['year']}\r\nCounter,Agent First Name,Agent Last Name,Company,Email,Phone,Years In Bussiness");

            foreach ($aEvents as $aEvent) {
                echo $aEvent['name'].'-'.$aEvent['eventdate'].',';
            }
            echo "Total classes attended, Total classes missed\r\n";
            foreach ($aAttendee as $aRow) {
                echo ('"' . ++$i . '","' .
                    $aRow['first_name'] . '","' .
                    $aRow['last_name'] . '","' .
                    $aRow['name'] . '","' .
                    $aRow['email'] . '","' .
                    $aRow['phone'] . '","' .
                    $aRow['industry_experience'] . '","');
                foreach ($aEvents as $aEvent) {
                    echo ($aEventAttendee[$aRow['id']][$aEvent['id']]?'X':'').'","';
                }
                echo (int)count($aEventAttendee[$aRow['id']]) . '","'
                    . (int)(count($aEvents)-count($aEventAttendee[$aRow['id']]));
                echo "\"\r\n";
            }
            die();
        } else {
            $this->Doc->addContent(
                array(
                    'tpl'          => 'Event/Report/bytype.tpl',
                    'year'         => $_REQUEST['year'],
                    'events'       => $aEvents,
                    'attendee'     => $aAttendee,
                    'attendeevent' => $aEventAttendee
                )
            );
            $this->addcsvbutton();
        }
    }

    function doNoEthicsReport()
    {
        $year = 2004 + floor((date('Y') - 2006) / 3);
        $this->Doc->setTitle('List of members who have not completed ethics training since December 31, ' . $year);
        $resAttendee = $this->DBObj->getRes4ExportNoEthicsReportAttendee();
        $listAttendeeOrientationEvent = $this->DBObj->getlistAttendeeOrientationEvent($year);
        if ($_REQUEST['subaction'] == 'csv') {
            header('Content-Type: text/csv;');
            header('Content-Disposition: attachment; filename=no-ethics.csv;');
            //Members who have not completed ethics training since December 31 $year\r\n
            echo ("Agent First Name,Agent Last Name,Company,Phone,Email,MNCAR Member,MNCAR Status, MNCAR-L Member,"
                 . "MNCAR-L Status, Misc. Member,Misc. Status,Orienation,Online Ethics,Orienations Attended\r\n");
            //Counter,
            while($row = $resAttendee->fetchRow()) {
                echo ('"' .
                    $row['first_name'] . '","' .
                    $row['last_name'] . '","' .
                    $row['company_name'] . '","' .
                    $row['primary_phone'] . '","' .
                    $row['email'] . '","' .
                    $row['mncar_type'] . '","' .
                    $row['mncar_status'] . '","' .
                    $row['mncar_l_type'] . '","' .
                    $row['mncar_l_status'] . '","' .
                    $row['misc_type'] . '","' .
                    $row['misc_status'] . '","' .
                    (count($listAttendeeOrientationEvent[$row['id']])?'Yes':'No') . '","' .
                    ($row['id_member']?'Yes':'No') . '","'
                );
                foreach ($listAttendeeOrientationEvent[$row['id']] as $event) {
                    echo $event['name'] . "-" . $event['eventdate'] . ' ';
                }
                echo "\"\r\n";
            }
            die();
        } else {
            $this->Doc->addContent(array(
                'tpl'          => 'Event/Report/noethics.tpl',
                'year'         => $year,
                'attendee'     => $resAttendee->fetchAll(),
                'attendeevent' => $listAttendeeOrientationEvent
            ));
            $this->addcsvbutton();
        }
    }

    function doAllAttendeeReport()
    {
        $this->Doc->setTitle('Report that includes every event in ' . date('Y') . ' along with the first name, '
                             .'last name, company, and years in industry for every attendee.');
        $aEventAttendee = $this->DBObj->db->queryAll(
            'SELECT e.name event_name, id_event, first_name, last_name, c.name company_name, email, m.primary_phone, industry_experience
            FROM mn_Event e INNER JOIN mn_EventFormReq efr ON e.id=efr.id_event
                INNER JOIN mn_Member m ON efr.id_member=m.id
                LEFT JOIN mn_Location l ON m.id_location=l.id
                LEFT JOIN mn_Company c ON l.id_company=c.id
            WHERE YEAR(eventdate)=' . date('Y')
        );
        $aEvents=$this->DBObj->db->queryAll(
            'SELECT id_event, ROUND(AVG(industry_experience),2)
            FROM mn_Member m LEFT JOIN mn_EventFormReq efr ON m.id=efr.id_member
            WHERE industry_experience>0
            GROUP BY efr.id_event', 0, null, true
        );
        if ($_REQUEST['subaction']=='csv') {
            header('Content-Type: text/csv;');
            header('Content-Disposition: attachment; filename=all-attendee.csv;');
            echo ("All events and attendees in ".date('Y')."\r\n\"Counter\",\"Event Name\",\"Agent First Name\",\"Agent Last Name\",\"Company\",\"Years In Bussiness\"\r\n");
            $id_event=0;
            foreach ($aEventAttendee as $aRow) {
                if ($aRow['id_event']!= $id_event) {
                    if ($id_event) {
                         echo ',,,,"Average Industry Exprience","'.$aEvents[$id_event]."\"\r\n";
                    }
                    $id_event=$aRow['id_event'];
                }
                echo ('"' . ++$i . '","' . $aRow['event_name'] . '","' . $aRow['first_name'] . '","'
                    . $aRow['last_name'] . '","' . $aRow['name'] . '",'
                    . ($aRow['industry_experience']?$aRow['industry_experience']:'') . "\r\n"
                );
            }
            echo ',,,,"Average Industry Exprience","'.$aEvents[$id_event]."\"\r\n";
            die();
        } else {
            $this->Doc->addContent(
                array(
                    'tpl'          => 'Event/Report/allattendee.tpl',
                    'attendeevent' => $aEventAttendee,
                    'event'        => $aEvents
                )
            );
            $this->addcsvbutton();
        }
    }

    function addcsvbutton()
    {
        $form=$this->_getBaseForm();
        $form->setSubmitTitle('Donwload Report');
        $form->addElement('hidden', 'action', '');
        $form->addElement('hidden', 'subaction', 'csv');
        $form->addElement('hidden', 'year');
        $form->addElement('hidden', 'id_type');
        $form->setTpl('class/Form/tpl/smarty-dynamic.tpl');
        $form->exec();
    }

    protected function _doDocCourseRenewalReportExport()
    {
        require_once('app/Event/Doc/Doc.php');
        $eventDocCourseObj = new EventDoc();
        $statement = $eventDocCourseObj->getReportStatement();
        $columns = array(
            'doc_number'   => 'Course ID',
            'doc_name'     => 'Course Name',
            'ce_hours'     => 'CE Credits',
            'status_title' => 'Course Status',
            'renewal_date' => 'Renewal Date',
        );
        $this->_exportCsv($statement, $columns, 'DocCourseRenewalReport_' . date('Y-m-d_g-i-A') . '.csv');
    }
}