<?php

class stats {
    public static function getMembersCountByNode() {
        $return = array();

        $criteria = new Criteria();
        $criteria->addSelectColumn(KataoNodePeer::ID);
        $criteria->addSelectColumn(KataoNodePeer::CITY);
        $criteria->addAsColumn('members_count', sprintf('COUNT(DISTINCT(%s))', KataoMemberPeer::ID));
        $criteria->addJoin(KataoNodePeer::ID, KataoMemberPeer::KATAO_NODE_ID, Criteria::LEFT_JOIN);
        $criteria->addGroupByColumn(KataoNodePeer::CITY);

        $rs = KataoNodePeer::doSelectRS($criteria);
        while ($rs->next()) {
            $return[$rs->getInt(1)] = array('name' => $rs->getString(2), 'members_count' => $rs->getInt(3));
        }

        return $return;
    }

    public static function getInvoicesSumByPeriodAndNode() {
        $return = array();

        $katao_nodes = KataoNodePeer::getAllArray();
        foreach (KataoPeriodPeer::getAllArray() as $katao_period_id => $katao_period) {
            foreach ($katao_nodes as $katao_node_id => $katao_node_name) {
                $return[$katao_period_id]['name'] = $katao_period['name'];
                $return[$katao_period_id]['status'] = $katao_period['status'];
                $return[$katao_period_id]['amount'] = 0;
                $return[$katao_period_id]['nodes_list'][$katao_node_id]['name'] = $katao_node_name;
                $return[$katao_period_id]['nodes_list'][$katao_node_id]['amount'] = 0;
            }
        }

        $criteria = new Criteria();
        $criteria->addSelectColumn(KataoPeriodPeer::ID);
        $criteria->addSelectColumn(KataoNodePeer::ID);
        $criteria->addSelectColumn(KataoNodePeer::CITY);
        $criteria->addAsColumn('amount', sprintf('SUM(%s * %s)', KataoInvoiceProductPeer::QUANTITY, KataoInvoiceProductPeer::PRODUCT_PRICE_EURO));
        $criteria->addJoin(KataoInvoicePeer::KATAO_PERIOD_ID, KataoPeriodPeer::ID);
        $criteria->addJoin(KataoInvoicePeer::KATAO_NODE_ID, KataoNodePeer::ID);
        $criteria->addJoin(KataoInvoicePeer::ID, KataoInvoiceProductPeer::KATAO_INVOICE_ID);
        $criteria->addGroupByColumn(KataoPeriodPeer::NAME);
        $criteria->addGroupByColumn(KataoNodePeer::CITY);

        $rs = KataoInvoiceProductPeer::doSelectRS($criteria);
        while ($rs->next()) {
            $return[$rs->getInt(1)]['amount'] += $rs->getFloat(4);
            $return[$rs->getInt(1)]['nodes_list'][$rs->getInt(2)]['name'] = $rs->getString(3);
            $return[$rs->getInt(1)]['nodes_list'][$rs->getInt(2)]['amount'] = $rs->getFloat(4);
        }

        return $return;
    }
}