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;

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;
    }

    /**
     * Get all revisions filtered for user and timestamp
     * 
     * @param string $user
     * @param DateTime $dateFrom
     * @param DateTime $dateTo
     * @param int $page
     * 
     * @return type
     */
    public function getRevisionsData($user, $dateFrom, $dateTo, $page)
    {
        //Set default page
        $show = 25;

        if (!$page) {
            $page = 1;
        }

        $sqlct = "SELECT Count(id) AS Total FROM revisions WHERE (id <> 0 AND username <> '') ";
        foreach ($user as $key => $usr) {
            if ($key == 0) {
                $sqlct .= "AND username LIKE '%{$usr}%' ";
            } else {
                $sqlct .= "OR username LIKE '%{$usr}%' ";
            }
        }
        if ($dateFrom != '') {
            $sqlct .= "AND (timestamp >= '{$dateFrom} 00:00:00') ";
        }
        if ($dateTo != '') {
            $sqlct .= "AND (timestamp <= '{$dateTo} 23:59:59') ";
        }

        // Sql for get data
        $sqlrev = "SELECT timestamp,id AS rev FROM revisions WHERE (id <> 0 AND username <> '') ";
        foreach ($user as $key => $usr) {
            if ($key == 0) {
                $sqlrev .= "AND username LIKE '%{$usr}%' ";
            } else {
                $sqlrev .= "OR username LIKE '%{$usr}%' ";
            }
        }
        if ($dateFrom != '') {
            $sqlrev .= "AND (timestamp >= '{$dateFrom} 00:00:00') ";
        }
        if ($dateTo != '') {
            $sqlrev .= "AND (timestamp <= '{$dateTo} 23:59:59') ";
        }

        // Count results
        $regs = $this->connection->fetchColumn($sqlct, array(1), 0);

        // Set pagination
        $from = ($page - 1) * $show;
        $To = ($page) * $show;

        if ($To > $regs) {
            $To = $regs;
        }

        $sqlrev .= "ORDER BY timestamp DESC LIMIT " . $from . ", " . $To;

        $query_result = $this->connection->fetchAll($sqlrev);
        $results_processed_changes = $this->results_with_processed_changes($query_result);

        $tdata = array(
            'regs' => $regs,
            'revisions' => $results_processed_changes,
        );
        
        return ($tdata);
    }

    /**
     * @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;
    }

    /**
     * Get all revisions with the changes processed
     * 
     * @param array $query_result
     * 
     * @return array
     */
    public function results_with_processed_changes($query_result)
    {
        $param_query_result = $query_result;
        foreach ($param_query_result as $key => &$data) {
            $entity_results = array();
            $data['rev_data'] = $this->reader->findRevision($data['rev']);
            $data['rev_entities_data'] = $this->reader->findEntitiesChangedAtRevision($data['rev']);
            #Get entity of the revision
            $entity_revs = $data['rev_entities_data'];

            foreach ($entity_revs as $result) {
                $changes_all = array();
                #Get all revisions for each class in table revision
                $history_for_class = $this->reader->findRevisions($result->getClassName(), $result->getId());

                foreach ($history_for_class as $rev_history) {
                    if ($rev_history->getRev() < $data['rev']) {
                        $diff = $this->reader->diff($result->getClassName(), $result->getId(), $rev_history->getRev(), $data['rev']);
                        foreach ($diff as $field => $value) {
                            if ($value['old'] != "") {
                                if (!is_a($value['old'], 'DateTime')) {
                                    $changes = array();
                                    $changes['field'] = $field;
                                    $changes['old'] = $value['old'];
                                    $changes['new'] = $value['new'];
                                    array_push($changes_all, $changes);
                                }
                            }
                        }
                        break;
                    }
                }
            }
            if (!isset($changes_all)) {
                $changes_all = array();
            }
            $data['changed_field'] = $changes_all;
        }

        return $param_query_result;
    }

    /**
     * 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)
    {
        $entity = $this->reader->find($className, $id, $rev);
        
        return array(
            'rev_details' => $this->reader->getEntityValues($className, $entity),
            'rev_revisions' => $this->reader->findRevisions($className, $id),
            'revdata' => $this->reader->findRevision($rev),
        );
    }
    
    /**
     * @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
//        var_dump($page);
//        die;
        $entity = array_search($entity, $entities);
        
        $from = $this->buildFrom($entity);
        $where = $this->buildWhere($entity, $users, $types, $idx, $dateFrom, $dateTo, $searchValue);

        $sql_cnt = $this->buildSelect($entity, true) . $from . $where;
        $count = $this->connection->query($sql_cnt)->fetchAll()[0]['total'];

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

        if ($resxpage != "inf") {
            $pages = ceil(($count / floatval($resxpage)));
            $from = ($page - 1) * $resxpage;
            $to = ($page) * $resxpage;
            $sql .= "LIMIT {$from},{$to}";
        }
        
        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)
    {
        $select = 'SELECT C.rev, C.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' ";
            }
            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;
    }
    
}