<?php
App::uses('AppController', 'Controller');
App::uses('CakeTime', 'Utility');

/**
 * Reports Controller
 *
 * @property Report $Report
 * @property PaginatorComponent $Paginator
 */
class ReportsController extends AppController {

    // all actions require log in
    public function beforeFilter() {
        $this->Auth->allow('period', 'growth');
    }

    public function growth($yearType = 'normal') {

        if ($yearType == 'normal') {
            $normalClass = 'btn btn-success';
            $financialClass = 'btn btn-default';
        } else {
            $normalClass = 'btn btn-default';
            $financialClass = 'btn btn-success';
        }
        $this->set('year_type', $yearType);
        $this->set('normal_classes', $normalClass);
        $this->set('financial_classes', $financialClass);

        $this->set('topicTitle', __('ตารางจำนวนเครือข่ายองค์กรที่เพิ่มขึ้น รายเดือน/รายปี'));

        $maxYear = date('Y') + 543;
        $minYear = 2545; // Min year should be 2545
        // $minYear = $maxYear - 15; // ideally should be one from the oldest records

        $this->set('min_year', $minYear);
        $this->set('max_year', $maxYear);

        $from = isset($this->params->query['from']) ? $this->params->query['from'] : $maxYear - 1;
        $to = isset($this->params->query['to']) ? $this->params->query['to'] : $maxYear;

        // convert to international year for querying
        $from -= 543;
        $to -= 543;

        if (isset($this->params->query['growth_type'])
            && $this->params->query['growth_type'] == 'type') {

            $this->set('first_column_title', __('ประเภทองค์กร'));
            $growthType = 'type';
            // get all types
            // for each type: get all organizations
            // with start in $year
            $this->loadModel('Organization');
            $this->loadModel('Type');
            $types = $this->Type->find('list');
            $members = $types;
        } else {
            $this->set('first_column_title', __('ความร่วมมือพิเศษ'));
            $growthType = 'member';
            // growth type is member
            $this->loadModel('Member');
            $members = $this->Member->find('list');

            $this->loadModel('OrgMembership');
            $increasedMembers = array();
        }
        $this->set('growth_type', $growthType);

        // year mode
        if ($from < $to) {

            // columns to display data
            $range = array();

            // need org created in 2012 to calculate growth of 2013
            for ($i = $from; $i <= $to; $i++) {
                array_push($range, $i);
            }
            if ($yearType == 'normal') {
                // [2012, 2013, 2014]
                foreach ($members as $memberId => $member) {
                    // keep # of org created in current iterating year in a temp array
                    $tmp = array();
                    $increasedFromLastYear = array();
                    if ($growthType == 'member') {
                        for ($i = 0; $i < count($range); $i++) {
                       // count number of OrgMembership `start` current year
                        // with current member_id

                            $conditions = CakeTime::daysAsSql('1 January ' . $range[$i], '31 December ' . $range[$i], 'start') . ' AND (OrgMembership.member_id = ' . $memberId . ')';
                            $startInYear = $this->OrgMembership->find('count', array(
                                'conditions' => $conditions
                                ));
                            $increasedFromLastYear[$range[$i]] = $startInYear;
                        } // endfor


                        $totalInMember = $this->OrgMembership->find('count',
                            array('conditions' =>
                                array('OrgMembership.member_id' => $memberId)
                            ));
                        $increasedMembers[$memberId] = array(
                            'name' => $member,
                            'total' => $totalInMember,
                            'growth' => $increasedFromLastYear
                        );

                    } else {

                        // organization type
                        for ($i = 0; $i < count($range); $i++) {
                            $joins = array();
                            $joins[] = array(
                                'alias'         => 'OrganizationsType',
                                'table'         => 'organizations_types',
                                'type'          => 'INNER',
                                'conditions'    => array(
                                    '`Organization`.`id` = `OrganizationsType`.`organization_id`',
                                    '`OrganizationsType`.`type_id` = ' . $memberId,
                                    CakeTime::daysAsSql('1 January ' . $range[$i], '31 December ' . $range[$i], 'joined')
                                ),
                            );

                            // joined in $range[$i]
                            $startInYear = $this->Organization->find('count',
                                array('joins' => $joins));
                            $increasedFromLastYear[$range[$i]] = $startInYear;

                            $increasedMembers[$memberId] = array(
                                'name' => $member,
                                'total' => $this->__countOrgByType($memberId),
                                'growth' => $increasedFromLastYear
                            );
                        }

                    } // endif

                }

            } else {
                // year type == financial year
                // construct months in each financial year
                $yearlyGrowth = array();
                foreach ($range as $r) {
                    $months = $this->__monthsInFinancialYear($r);
                    foreach ($members as $memberId => $member) {
                        $monthMembers = $this->__getInMonths($months, $memberId, $growthType);

                        // summary of increased in the year
                        $increasedInYear = array_sum($monthMembers);
                        $yearlyGrowth[$memberId][$r] = $increasedInYear;
                    }
                }

                $increasedMembers = array();
                if ($growthType == 'member') {
                    foreach ($yearlyGrowth as $memberId => $y) {
                        $totalInMember = $this->OrgMembership->find('count',
                            array('conditions' =>
                                array('OrgMembership.member_id' => $memberId)
                            ));
                        $increasedMembers[$memberId] = array(
                            'name' => $members[$memberId],
                            'total' => $totalInMember,
                            'growth' => $y
                        );
                    }
                } else {
                    foreach ($yearlyGrowth as $memberId => $y) {
                        $increasedMembers[$memberId] = array(
                            'name' => $members[$memberId],
                            'total' => $this->__countOrgByType($memberId),
                            'growth' => $y
                        );
                    }
                }
            }

            $range = $this->__toThaiYears($range);

        } else if ($from == $to) {
            // month mode
            // $range should be months
            $months = array();
            if ($yearType == 'normal') {
                // $months[] = ($from - 1) . '-12';
                for ($i = 1; $i <= 12; $i++) {
                    $months[] = $from . '-' . $i;
                }
            } else {
                // financial year
                $months = $this->__monthsInFinancialYear($from);
            }

            if ($growthType == 'member') {
                foreach ($members as $memberId => $member) {
                    $monthMembers = $this->__getInMonths($months, $memberId, $growthType);
                    $totalInMember = $this->OrgMembership->find('count',
                        array('conditions' =>
                            array('OrgMembership.member_id' => $memberId)
                        ));
                    $increasedMembers[$memberId] = array(
                        'name' => $member,
                        'total' => $totalInMember,
                        'growth' => $monthMembers
                    );
                }
            } else {
                foreach ($members as $memberId => $member) {
                    $monthMembers = $this->__getInMonths($months, $memberId, $growthType);
                    $increasedMembers[$memberId] = array(
                        'name' => $member,
                        'total' => $this->__countOrgByType($memberId),
                        'growth' => $monthMembers
                    );
                }
            }

            $range = $months;
            $range = $this->__toMonthNames($range);

        } else {
            // $from > to : display error message?
            $this->Session->setFlash(__('กรุณาเลือกช่วงปีให้ถูกต้อง'), 'flash-warning');
            $this->redirect($this->referer());
        }

        $this->set('members', $increasedMembers);

        // convert back to thai year
        $from += 543;
        $to += 543;

        $this->set('from', $from);
        $this->set('to', $to);
        $this->set('range', $range);

        if (isset($this->params->ext)) {
            if ($this->params->ext == 'pdf') {
                $this->_renderPdf(array(), '/Reports/pdf/growth');
            } else if ($this->params->ext == 'csv') {
                $this->render('/Elements/csv/growth', false);
            }
        }
    }

    // take array of months -> count members started
    // get number of members started in all given months
    private function __getInMonths($months, $memberId, $growthType) {
        $firstDays = array();
        foreach ($months as $month) {
            array_push($firstDays, $month . '-01');
        }

        $results = array();

        if ($growthType == 'member') {
            $this->loadModel('OrgMembership');
            foreach ($firstDays as $f) {
                // we want orgmemberships created in $month
                $startInMonth = $this->OrgMembership->find('count', array(
                    'conditions' => array(
                        'OrgMembership.start BETWEEN ? AND LAST_DAY(?)' => array($f, $f),
                        'OrgMembership.member_id' => $memberId
                        )
                    ));
                $results[$f] = $startInMonth;
            }
        } else {
            $this->loadModel('Organization');
            foreach ($firstDays as $f) {
                $joins = array();
                $joins[] = array(
                    'alias'         => 'OrganizationsType',
                    'table'         => 'organizations_types',
                    'type'          => 'INNER',
                    'conditions'    => array(
                        '`Organization`.`id` = `OrganizationsType`.`organization_id`',
                        '`OrganizationsType`.`type_id` = ' . $memberId,
                        '`Organization`.`joined` BETWEEN ? AND LAST_DAY(?)' => array($f, $f)
                    ),
                );
                // we want orgmemberships created in $month
                $startInMonth = $this->Organization->find('count',
                    array('joins' => $joins));
                $results[$f] = $startInMonth;
            }
        }

        return $results;
    }

    // take an array of new members in months
    // get number of increased members in each month in the array
    // private function __getIncreasedNumber($arr) {
    //     $increased = array();

    //     $counter = 0;
    //     $tmp = array();
    //     foreach ($arr as $month => $num) {
    //         $tmp[] = $num;
    //         if ($counter > 0) { // skip first month
    //             $increased[$month] = max($tmp[$counter] - $tmp[$counter - 1], 0);
    //         }
    //         $counter += 1;
    //     }

    //     return $increased;
    // }

    // generate an array of months for a given financial year
    private function __monthsInFinancialYear($year) {

        // october to september of next year
        for ($i = 9; $i <= 12; $i++) {
            $months[] = $year . '-' . $i;
        }

        for ($i = 1; $i <= 9; $i++) {
            $months[] = ($year + 1) . '-' . $i;
        }

        return $months;
    }

    // conver `2013-10` to `ตุลาคม 2013`
    private function __toMonthNames($months) {
        $thaiMonths = Configure::read('MONTHS');
        $monthNames = array();
        foreach ($months as $month) {
            list($year, $month) = explode('-', $month);
            $monthNames[] = $thaiMonths[$month] . ' ' . ($year + 543);
        }
        return $monthNames;
    }

    private function __countOrgByType($typeId) {
        $this->loadModel('Organization');
        return $this->Organization->find('count', array(
            'joins' => array(
                array(
                'alias'         => 'IJ_OrganizationType',
                'table'         => 'organizations_types',
                'type'          => 'INNER',
                'conditions'    => array(
                    '`Organization`.`id` = `IJ_OrganizationType`.`organization_id`',
                    '`IJ_OrganizationType`.`type_id` = ' . $typeId
                    )
                )
            ))
        );
    }

    private function __toThaiYears($months) {
        foreach ($months as &$month) {
            $month += 543;
        }
        return $months;
    }

    // report of org, ppl, activities in a period of time
    public function period() {
        $this->set('topicTitle', __('ตารางจำนวนภาพรวมจำนวนเครือข่ายและกิจกรรมทั้งหมด'));
        $this->set('months', Configure::read('MONTHS'));

        $maxYear = date('Y') + 543;
        $minYear = 2545;
        // $minYear = $maxYear - 15;
        $this->set('min_year', $minYear);
        $this->set('max_year', $maxYear);

        $month = isset($this->params->query['month']) ? $this->params->query['month'] : '';
        $year = isset($this->params->query['year']) ? $this->params->query['year'] : '';
        $this->set('month', $month);
        $this->set('year', $year);

        $entryTypes = array(
            'year' => __('ปี'),
            'region' => __('ภาค'),
            'province' => __('จังหวัด'),
            'topic' => __('ประเภทสิทธ์หลัก')
        );
        $this->set('entry_types', $entryTypes);

        $entryType = isset($this->params->query['entry_type']) ? $this->params->query['entry_type'] : 'year';

        $this->set('entry_type', $entryType);
        $this->set('first_column', $entryTypes[$entryType]);

        $data = array();
        if ($entryType == 'year') {
            // construct year range (oldest to newest)
            // $oldest = $this->__getYear('oldest');
            $oldest = 2002;
            $newest = $this->__getYear('newest');
            $yearRange = array_fill($oldest, ($newest - $oldest + 1), 0);
            krsort($yearRange); // Sort data from last year first.
            // for each year, get total org, ppl, and act
            $dataInYears = array();
            foreach ($yearRange as $year => $nay) { // we don't really use $nay
                $dataInYears[] = array_merge(array($year + 543), $this->__getPeriodDataInYear($year));
            }

            $data = $dataInYears;
        } else {
            // for other types, $month and $year are required
            if (empty($month) || empty($year)) {
                $this->Session->setFlash(__('กรุณาเลือกเดือนและปีให้ถูกต้อง'), 'flash-warning');
                return;
            }

            $year -= 543;
            $data = $this->__getPeriodData($entryType, $month, $year);
        }

        $this->set('data', $data);

        if (isset($this->params->ext)) {
            if ($this->params->ext == 'pdf') {
                $this->_renderPdf($data, '/Reports/pdf/period');
            } else if ($this->params->ext == 'csv') {
                $this->render('/Elements/csv/period', false);
            }
        }
    }

    // get 1 of oldest/newest year in org, people, and activities
    private function __getYear($type) {
        $models = array('Organization', 'Person', 'Activity');

        if ($type == 'oldest') {
            $baseYear = 9999;
            $orderDirection = 'ASC';
        } else {
            $baseYear = 0;
            $orderDirection = 'DESC';
        }
        foreach ($models as $model) {
            $this->loadModel($model);
            $orderField = ($model == 'Activity') ? 'started' : 'joined';
            $max = $this->{$model}->find('first', array(
                'order' => array($orderField => $orderDirection),
                'recursive' => -1,
                'fields' => array('id', $orderField)
            ));

            if ( !empty($max) ) {
                $max = CakeTime::format($max[$model][$orderField], '%Y');
                if ($type == 'oldest' && $max < $baseYear) {
                    $baseYear = $max;
                } else if ($type == 'newest' && $max > $baseYear) {
                    $baseYear = $max;
                }
            }
        }

        return $baseYear;
    }

    // count number of organizations, people, and activities
    // in a given $year
    private function __getPeriodDataInYear($year) {

        $models = array('Organization', 'Person', 'Activity');
        $data = array();
        foreach ($models as $model) {
            $this->loadModel($model);
            $countField = ($model == 'Activity') ? 'started' : 'joined';
            $conditions = CakeTime::daysAsSql('1 January ' . $year,
                '31 December ' . $year, $countField);
            $hoo = $this->{$model}->find('count', array(
                'recursive' => -1,
                'conditions' => array( $conditions )
            )); // `started` or `joined` in year

            $data[] = $hoo;
        }

        return $data;
    }

    // count number of organizations, people, and activities
    // in a given $month & $year
    private function __getPeriodData($type, $month, $year) {

        $firstDay = $year . '-' . $month . '-01';
        $models = array('Organization', 'Person', 'Activity');


        // get all $types
        $type = Inflector::camelize($type);
        $this->loadModel($type);
        $typeRecords = $this->{$type}->find('list');
        $data = array();
        foreach ($typeRecords as $id => $r) {
            // count org in ภาคกลาง
            $eachType = array();
            foreach ($models as $model) {
                $this->loadModel($model);
                $field = ($model == 'Activity') ? 'started' : 'joined';
                $conditions = array(
                    $field . ' BETWEEN ? AND LAST_DAY(?)' => array($firstDay, $firstDay),
                );

                // create joins conditions based on current objects
                $joins = $this->__getJoins($type, $model, $id);

                $hoo = $this->{$model}->find('count', array(
                    'recursive' => -1,
                    'conditions' => $conditions,
                    'joins' => $joins
                )); // `started` or `joined` in selected month

                $eachType[] = $hoo;
            }

            $data[] = array_merge(array($r), $eachType);
        }

        return $data;
    }

    // create $joins conditions for periodic report
    private function __getJoins($type, $model, $id) {

        $joins = array();

        // Topic has diffent joins conditions than Region and Province
        if ($type == 'Topic') {
            // all subtopics in selected topic_id
            // HABTM table name
            $tableName = Inflector::tableize($model) . '_subtopics';
            $fk = strtolower($model) . '_id';
            $joins[] = array(
                'alias'         => 'IJ_' . $model . 'Subtopic',
                'table'         => $tableName,
                'type'          => 'INNER',
                'conditions'    => array(
                    '`IJ_' . $model . 'Subtopic`.`'. $fk .'` = `'. $model .'`.`id`'
                )
            );

            $joins[] = array(
                'alias'         => 'IJ_SubtopicTopic',
                'table'         => 'subtopics',
                'type'          => 'INNER',
                'conditions'    => array(
                    '`IJ_'. $model .'Subtopic`.`subtopic_id` = `IJ_SubtopicTopic`.`id`',
                    '`IJ_SubtopicTopic`.`topic_id` = ' . $id
                )
            );
            return $joins;
        }


        // type = Region or Province
        // these 2 types have the same join conditions
        // but have different field to check in `provinces` table
        // Region => use `region_id` field in `provinces` table to check
        // Province => use `id` field in `provinces` table to check
        if ($type == 'Region') {
            $fieldToCheck = 'region_id';
        } else if ($type == 'Province') {
            $fieldToCheck = 'id';
        }

        $joins[] = array(
            'alias'         => 'IJ_' . $model . 'District',
            'table'         => 'districts',
            'type'          => 'INNER',
            'conditions'    => array(
                '`IJ_'.$model.'District`.`id` = `'.$model.'`.`district_id`'
            )
        );

        $joins[] = array(
            'alias'         => 'IJ_DistrictProvince',
            'table'         => 'provinces',
            'type'          => 'INNER',
            'conditions'    => array(
                '`IJ_'.$model.'District`.`province_id` = `IJ_DistrictProvince`.`id`',
                '`IJ_DistrictProvince`.`'. $fieldToCheck .'` = ' . $id
            )
        );

        return $joins;
    }
}