stats.class.php 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
  1. <?php
  2. class stats {
  3. public static function getMembersCountByNode() {
  4. $return = array();
  5. $criteria = new Criteria();
  6. $criteria->addSelectColumn(KataoNodePeer::ID);
  7. $criteria->addSelectColumn(KataoNodePeer::CITY);
  8. $criteria->addAsColumn('members_count', sprintf('COUNT(DISTINCT(%s))', KataoMemberPeer::ID));
  9. $criteria->addJoin(KataoNodePeer::ID, KataoMemberPeer::KATAO_NODE_ID, Criteria::LEFT_JOIN);
  10. $criteria->addGroupByColumn(KataoNodePeer::CITY);
  11. $rs = KataoNodePeer::doSelectRS($criteria);
  12. while ($rs->next()) {
  13. $return[$rs->getInt(1)] = array('name' => $rs->getString(2), 'members_count' => $rs->getInt(3));
  14. }
  15. return $return;
  16. }
  17. public static function getInvoicesSumByPeriodAndNode() {
  18. $return = array();
  19. $katao_nodes = KataoNodePeer::getAllArray();
  20. foreach (KataoPeriodPeer::getAllArray() as $katao_period_id => $katao_period) {
  21. foreach ($katao_nodes as $katao_node_id => $katao_node_name) {
  22. $return[$katao_period_id]['name'] = $katao_period['name'];
  23. $return[$katao_period_id]['status'] = $katao_period['status'];
  24. $return[$katao_period_id]['amount'] = 0;
  25. $return[$katao_period_id]['nodes_list'][$katao_node_id]['name'] = $katao_node_name;
  26. $return[$katao_period_id]['nodes_list'][$katao_node_id]['amount'] = 0;
  27. }
  28. }
  29. $criteria = new Criteria();
  30. $criteria->addSelectColumn(KataoPeriodPeer::ID);
  31. $criteria->addSelectColumn(KataoNodePeer::ID);
  32. $criteria->addSelectColumn(KataoNodePeer::CITY);
  33. $criteria->addAsColumn('amount', sprintf('SUM(%s * %s)', KataoInvoiceProductPeer::QUANTITY, KataoInvoiceProductPeer::PRODUCT_PRICE_EURO));
  34. $criteria->addJoin(KataoInvoicePeer::KATAO_PERIOD_ID, KataoPeriodPeer::ID);
  35. $criteria->addJoin(KataoInvoicePeer::KATAO_NODE_ID, KataoNodePeer::ID);
  36. $criteria->addJoin(KataoInvoicePeer::ID, KataoInvoiceProductPeer::KATAO_INVOICE_ID);
  37. $criteria->addGroupByColumn(KataoPeriodPeer::NAME);
  38. $criteria->addGroupByColumn(KataoNodePeer::CITY);
  39. $rs = KataoInvoiceProductPeer::doSelectRS($criteria);
  40. while ($rs->next()) {
  41. $return[$rs->getInt(1)]['amount'] += $rs->getFloat(4);
  42. $return[$rs->getInt(1)]['nodes_list'][$rs->getInt(2)]['name'] = $rs->getString(3);
  43. $return[$rs->getInt(1)]['nodes_list'][$rs->getInt(2)]['amount'] = $rs->getFloat(4);
  44. }
  45. return $return;
  46. }
  47. }