AuditBundle/Services/AuditDataService.php

<?php

namespace AuditBundle\Services;

use Doctrine\DBAL\Connection;
use Doctrine\ORM\EntityManagerInterface;
use SimpleThings\EntityAudit\AuditManager;
use SimpleThings\EntityAudit\AuditReader;
use SimpleThings\EntityAudit\Revision;
use SimpleThings\EntityAudit\Exception\NoRevisionFoundException;

class AuditDataService
{

    /**
     * @var Connection
     */
    private $connection;

    /**
     * @var AuditReader
     */
    private $reader;
    
    /**
     * @var AuditManager 
     */
    private $auditManager;
    
    /**
     * @var EntityManagerInterface 
     */
    private $entityManager;
    
    
    /**
     * @param Connection $connection
     * @param AuditReader $reader
     * @param AuditManager $auditManager
     * @param EntityManagerInterface $entityManager
     */
    public function __construct(Connection $connection, AuditReader $reader, AuditManager $auditManager, EntityManagerInterface $entityManager)
    {
        $this->connection = $connection;
        $this->reader = $reader;
        $this->auditManager = $auditManager;
        $this->entityManager = $entityManager;
    }

    /**
     * @return array
     */
    public function getRevisionUsers()
    {
        $query = "SELECT DISTINCT(SUBSTRING_INDEX(username, '(', 1)) as username FROM revisions ORDER BY username ASC;";

        $usernames = array();
        foreach ($this->connection->fetchAll($query) as $username) {
            $usernames[$username['username']] = $username['username'];
        }

        return $usernames;
    }

    /**
     * Applied diff between revisions
     * 
     * @param string $className
     * @param int $id
     * @param Revision $oldRev
     * @param Revision $newRev
     * 
     * @return array
     */
    public function diff($className, $id, $oldRev, $newRev)
    {
        return $this->reader->diff($className, $id, $oldRev, $newRev);
    }
    
    /**
     * Get data of a revision
     * 
     * @param string $className
     * @param int $id
     * @param Revision $rev
     * 
     * @return array
     */
    public function viewRevision($className, $id, $rev)
    {
        $rev_details = [];
        $rev_revisions = [];
        $revdata = [];
        try {
            $entity = $this->reader->find($className, $id, $rev);
        } catch(NoRevisionFoundException $e) {
            $entity = $this->entityManager->getRepository($className)->find($id);
        }
        
        if ($entity) {
            $rev_details = $this->reader->getEntityValues($className, $entity);
            $rev_revisions = $this->reader->findRevisions($className, $id);
            $revdata = $this->reader->findRevision($rev);
        }
        
        return [
            'rev_details' => $rev_details,
            'rev_revisions' => $rev_revisions,
            'revdata' => $revdata,
        ];
    }
    
    /**
     * @return array
     */
    public function getEntities()
    {
        $mdFactory = $this->auditManager->getMetadataFactory();
        $meta = $this->entityManager->getMetadataFactory()->getAllMetadata();
        $entities = array();
        foreach ($meta as $m) {
            $entities[$m->getName()] = $m->table['name'];
        }
        ksort($entities);
        foreach ($entities as $key => $entityName) {
            if (!$mdFactory->isAudited($key)) {
                unset($entities[$key]);
            }
        }
        
        return $entities;
    }
    
    /**
     * @return array
     */
    public function getColumns()
    {
        $columns = array();
        $meta = $this->entityManager->getMetadataFactory()->getAllMetadata();
        foreach ($meta as $m) {
            $columns[$m->getName()] = $m->getColumnNames();
        }
        
        return $columns;
    }
    
    /**
     * @param array $data
     * 
     * @return array
     */
    public function getResults($data)
    {

        $entities = $this->getEntities();
        $pages = 1;
        $users = array();
        $types = array();
        
        extract($data); // retorna la data del filter form

        $entity = array_search($entity, $entities);
        
        $from = $this->buildFrom($entity);
        $where = $this->buildWhere($entity, $users, $types, $idx, $dateFrom, $dateTo, $searchValue);
        
        $groupBy = $this->buildGroupBy($entity);
        
        $sql_cnt = $this->buildSelect($entity, true) . $from . $where . $groupBy;
        $fetchAll = $this->connection->query($sql_cnt)->fetchAll();
        
        $count = 0;
        foreach ($fetchAll as $row) {
            $count += isset($row['total']) ? $row['total'] : 0;
        }

        $sql = $this->buildSelect($entity) . $from . $where . $groupBy;
        $sql .= " ORDER BY R.timestamp DESC ";

        if(is_null($resxpage)) $resxpage = 10;
        
        if ($resxpage != "inf") {
            $pages = ceil(($count / floatval($resxpage)));
            $from = ($page - 1) * $resxpage;
            //$to = ($page) * $resxpage;
            $sql .= " LIMIT {$from},{$resxpage} ";
        }
        
        return array(
            'result' => $this->connection->query($sql)->fetchAll(),
            'count' => $count,
            'page' => $page,
            'pages' => $pages,
        );
    }
    
    /**
     * @param string $entity
     * @param bool $count
     * 
     * @return string
     */
    private function buildSelect($entity, $count = false)
    {
        $entities = $this->getEntities();
        $primaryKeyQuery = "SHOW KEYS FROM `{$entities[$entity]}` WHERE Key_name = 'PRIMARY';";
        $primaryKey = $this->connection->fetchAll($primaryKeyQuery);
        $id = 'id';
        if (isset($primaryKey[0])) {
            $id = $primaryKey[0]['Column_name'];
        }

        $select = "SELECT C.rev, C.{$id} as id, C.revtype, R.username, R.timestamp ";
        if ($count) {
            $select = 'SELECT COUNT(*) as total ';
        }
        $columns = $this->getColumns();
        foreach ($columns[$entity] as $column) {
            $select .= ", C." . $column . " ";
        }
        
        return $select;
    }
    
    /**
     * @param string $entity
     * 
     * @return string
     */
    private function buildFrom($entity)
    {
        $entities = $this->getEntities();
        
        return "FROM `{$entities[$entity]}_audit` AS C INNER JOIN `revisions` AS R ON C.rev = R.id ";
    }
    
    /**
     * @param string $entity
     * @param array $users
     * @param array $types
     * @param int $idx
     * @param string $dateFrom
     * @param string $dateTo
     * @param string $searchValue
     * 
     * @return string
     */
    private function buildWhere($entity, $users = null, $types = null, $idx = null, $dateFrom = null, $dateTo = null, $searchValue = null)
    {
        $where = '';
        $columns = $this->getColumns();
        if ($users || $types || $idx || $dateFrom || $dateTo || $searchValue) {
            $where .= 'WHERE C.id > 0 ';
            if ($users) {
                foreach ($users as $key => $user) {
                    if ($key == 0) {
                        $where .= "AND (R.username = '{$user}' ";
                    } else {
                        $where .= "OR R.username = '{$user}' ";
                    }
                }
                $where .= ') ';
            }
            if ($types) {
                foreach ($types as $key => $type) {
                    if ($key == 0) {
                        $where .= "AND (C.revtype = '{$type}' ";
                    } else {
                        $where .= "OR C.revtype = '{$type}' ";
                    }
                }
                $where .= ') ';
            }
            if ($idx) {
                $where .= "AND (C.id = '{$idx}') ";
            }
            if ($dateFrom && $dateTo) {
                $where .= " AND R.timestamp BETWEEN '{$dateFrom->format('Y-m-d')} 00:00:00' AND '{$dateTo->format('Y-m-d')} 23:59:59' ";
            }elseif($dateFrom) {
                $where .= " AND R.timestamp > '{$dateFrom->format('Y-m-d')} 00:00:00' ";
            }elseif($dateTo) {
                $where .= " AND R.timestamp < '{$dateTo->format('Y-m-d')} 23:59:59' ";
            }
            if ($searchValue) {
                foreach ($columns[$entity] as $key => $col) {
                    if ($key == 0) {
                        $where .= " AND (C.{$col} LIKE '{$searchValue}' ";
                    } else {
                        $where .= " OR C.{$col} LIKE '{$searchValue}' ";
                    }
                }
                $where .= ') ';
            }
        }
                
        return $where;
    }
    
    /**
     * @return string
     */
    private function buildGroupBy($entity)
    {
        $columns = $this->getColumns();
        $groupBy[] = 'C.rev';
        foreach ($columns[$entity] as $column) {
            $groupBy[] = "C.{$column}";
        }
        $groupBy = implode(', ', $groupBy);
        $groupBy = " GROUP BY {$groupBy} ";
    
        return $groupBy;
    }
    
}