1 <?php
  2 
  3 namespace Balance\Model\Persistence\Db;
  4 
  5 use IntlDateFormatter;
  6 use NumberFormatter;
  7 use Zend\Db\Sql\Expression;
  8 use Zend\Db\Sql\Select;
  9 use Zend\ServiceManager\ServiceLocatorAwareInterface;
 10 use Zend\ServiceManager\ServiceLocatorAwareTrait;
 11 use Zend\Stdlib\Parameters;
 12 
 13 /**
 14  */
 15 class Balance implements ServiceLocatorAwareInterface
 16 {
 17     use ServiceLocatorAwareTrait;
 18 
 19     /**
 20      * Consultar o Balanço Completo
 21      *
 22      * @param  Parameters $params Parâmetros de Execução
 23      * @return array      Conjunto de Valores Encontrados
 24      */
 25     public function fetch(Parameters $params)
 26     {
 27         // Inicialização
 28         $db     = $this->getServiceLocator()->get('db');
 29         $result = array(
 30             'ACTIVE'  => array(),
 31             'PASSIVE' => array(),
 32         );
 33 
 34         // Data e Hora Limite
 35         $datetime = false;
 36         // Enviado?
 37         if ($params['datetime']) {
 38             // Formatador
 39             $formatter = new IntlDateFormatter(null, IntlDateFormatter::MEDIUM, IntlDateFormatter::MEDIUM);
 40             // Captura
 41             $datetime = date('c', $formatter->parse($params['datetime']));
 42         }
 43 
 44         // Formatador de Moedas
 45         $formatter = new NumberFormatter(null, NumberFormatter::CURRENCY);
 46         // Expressões
 47         $eValue = new Expression(
 48             'CASE'
 49             . ' WHEN'
 50             . '  "a"."type" = \'ACTIVE\' AND "e"."type" = \'CREDIT\''
 51             . '  OR "a"."type" = \'PASSIVE\' AND "e"."type" = \'DEBIT\''
 52             . ' THEN "e"."value" * -1'
 53             . ' ELSE "e"."value"'
 54             . ' END'
 55         );
 56 
 57         // Seletor de Balanço
 58         $balanceSelect = (new Select())
 59             ->from(array('a' => 'accounts'))
 60             ->columns(array('id', 'value' => $eValue))
 61             ->join(array('e' => 'entries'), 'a.id = e.account_id', array())
 62             ->join(array('p' => 'postings'), 'p.id = e.posting_id', array());
 63         // Captura
 64         $subselect = clone($balanceSelect);
 65         // Filtro de Não Acumulados
 66         $subselect->where(function ($where) {
 67             $where->equalTo('a.accumulate', 0);
 68         });
 69         // Filtro?
 70         if ($datetime) {
 71             // Aplicar Filtro de Data Limite
 72             $subselect->where(function ($where) use ($datetime) {
 73                 $where->lessThanOrEqualTo('p.datetime', $datetime);
 74             });
 75         }
 76         // Seletor
 77         $select = (new Select())
 78             ->from(array('b' => $subselect))
 79             ->columns(array('value' => new Expression('SUM("b"."value")')))
 80             ->join(array('a' => 'accounts'), 'a.id = b.id', array('type', 'id', 'name'))
 81             ->group(array('a.id'))
 82             ->order(array('a.type', 'a.position'))
 83             ->having(function ($where) {
 84                 $where->notEqualTo(new Expression('SUM("b"."value")'), 0);
 85             });
 86         // Consulta
 87         $rowset = $db->query($select->getSqlString($db->getPlatform()))->execute();
 88         // Processamento
 89         foreach ($rowset as $row) {
 90             // Tipagem
 91             $type = $row['type'];
 92             // Adicionar Entrada
 93             $result[$type][] = array(
 94                 'id'       => (int) $row['id'],
 95                 'name'     => $row['name'],
 96                 'value'    => (float) $row['value'],
 97                 'currency' => $formatter->format($row['value']),
 98             );
 99         }
100 
101         // Seletor de Acumuladores
102         $subselect = clone($balanceSelect);
103         // Filtro para Acumulados
104         $subselect->where(function ($where) {
105             $where->equalTo('a.accumulate', 1);
106         });
107         // Seletor
108         $select = (new Select())
109             ->from(array('b' => $subselect))
110             ->columns(array('value' => new Expression('SUM("b"."value")')));
111         // Consulta
112         $value = (float) $db->query($select->getSqlString($db->getPlatform()))->execute()->current()['value'];
113         // Captura
114         return array_merge($result, array(
115             'ACCUMULATE' => array(
116                 'name'     => $value < 0 ? 'Prejuízo' : 'Lucro',
117                 'value'    => (float) $value,
118                 'currency' => $formatter->format($value),
119             ),
120         ));
121     }
122 }
123 

Balance API documentation generated by ApiGen