ArchiveCommand::buildValues() - Code Metrics - Maelstromeous/ps2alerts-api - Measure and Improve Code Quality continuously with Scrutinizer

ArchiveCommand::buildValues() A
last analyzed 2018-01-02 23:34 UTC

↳ Parent: ArchiveCommand

Complexity

Conditions	2
Paths	2

Size

Total Lines	10
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	6
nc	2
nop	1
dl	0
loc	10
rs	9.4285
c	0
b	0
f	0

<?php

namespace Ps2alerts\Api\Command;

use Ps2alerts\Api\Command\BaseCommand;
use Ps2alerts\Api\Repository\AlertRepository;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class ArchiveCommand extends BaseCommand
{
    protected $dbArchive;
    protected $alertRepo;
    protected $guzzle;
    protected $recordsArchived = 0;
    protected $alertsArchived = 0;

    protected function configure()
    {
        parent::configure(); // See BaseCommand.php
        $this
            ->setName('Archive')
            ->setDescription('Archives old alerts')
            ->addArgument('start', InputArgument::OPTIONAL, 'ResultID to start from')
            ->addArgument('process', InputArgument::OPTIONAL, 'Number of results to process');
        $this->dbArchive = $this->container->get('Database\Archive');
        $this->alertRepo = $this->container->get('Ps2alerts\Api\Repository\AlertRepository');
        $this->guzzle = $this->container->get('GuzzleHttp\Client');
    }

    /**
     * Execution
     *
     * @param  InputInterface   $input
     * @param  OutputInterface $output
     *
     * @return void
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $output->writeln('Executing archive operations...');

        $this->check($input, $output);
    }

    /**
     * Checks for alerts to be archived then runs routing against said alerts
     *
     * @param  OutputInterface $output
     *
     * @return void
     */
    public function check(InputInterface $input, OutputInterface $output)
    {
        $obj = new \DateTime();
        $obj->sub(new \DateInterval('P3D'));

        $query = $this->alertRepo->newQuery();
        $query->cols(['*']);
        $query->where('ResultStartTime < ?', $obj->format('U'));
        $query->where('Archived = 0');

        if (!empty($input->getArgument('start'))) {
            $query->where('ResultID >= ?', $input->getArgument('start'));
        }

        if (!empty($input->getArgument('process'))) {
            $query->where('ResultID <= ?', $input->getArgument('process'));
        }

        $alerts = $this->alertRepo->fireStatementAndReturn($query);
        $count = count($alerts);

        $output->writeln("Detected {$count} alerts to be archived");

        if ($count > 0) {
            $tables = [
                'ws_classes',
                'ws_classes_totals',
                'ws_combat_history',
                'ws_factions',
                'ws_map',
                'ws_map_initial',
                'ws_outfits',
                'ws_players',
                'ws_pops',
                'ws_vehicles',
                'ws_weapons',
                'ws_xp'
            ];

            for ($i = 0; $i < $count; $i++) {
                $this->archive($alerts[$i], $tables, $output);

                $per = ($i / $count) * 100;
                $per = round($per, 2);
                $output->writeln("{$i} / {$count} ({$per}%) processed");
            }
        }

        $records = number_format($this->recordsArchived, 0);

        $payload = [
            'channel' => '#logs',
            'username' => 'ps2alerts-archive',
            'text' => "Alerts archived: {$this->alertsArchived} - Records archived: {$records}",
            'icon_emoji' => ':open_file_folder:'
        ];

        $this->guzzle->request(
            'POST',
            'https://hooks.slack.com/services/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',
            ['json' => $payload]
        );

        $output->writeln("Archived {$records} records!");
    }

    /**
     * Execution of routine
     *
     * @param  array                                            $alert
     * @param  array                                            $tables
     * @param  Symfony\Component\Console\Output\OutputInterface $output
     *
     * @return void
     */
    public function archive($alert, $tables, OutputInterface $output)
    {
        $output->writeln("Processing Alert #{$alert['ResultID']}");

        $this->dbArchive->beginTransaction();

        // Get all data and insert it into the archive DB
        foreach ($tables as $table) {
            $output->writeln("Alert #{$alert['ResultID']} - Table: {$table}");

            $sql = "SELECT * FROM {$table} WHERE resultID = :result";

            $stm = $this->db->prepare($sql);
            $stm->bindParam(':result', $alert['ResultID']);
            $stm->execute();

            if ($stm->rowCount() > 0) {
                $values = '';
                $cols = '';

                // Build the values so we can do all of this in one huge query,
                // which helps with transmission over the internet greatly.
                while ($row = $stm->fetch(\PDO::FETCH_ASSOC)) {
                    $cols = $this->buildCols($row);
                    $data = $this->buildValues($row);
                    $values .= "('{$data}'),";
                }

                $values = rtrim($values, ',');
                $sql = "INSERT INTO {$table} ({$cols}) VALUES {$values}";

                $this->dbArchive->exec($sql);
            }
        }

        try {
            $output->writeln('Committing...');
            $this->dbArchive->commit();
        } catch (\Exception $e) {
            $this->dbArchive->rollBack();
            throw new \Exception($e->getMessage());
        }

        $records = 0;

        $this->db->beginTransaction();

        // Loop through all tables and delete the alert's data from the DB
        foreach ($tables as $table) {
            $sql = "DELETE FROM {$table} WHERE resultID = :result";
            $stm = $this->db->prepare($sql);
            $stm->execute(['result' => $alert['ResultID']]);

            $this->recordsArchived += $stm->rowCount();
            $records += $stm->rowCount();

            $output->writeln("Archived {$stm->rowCount()} from Alert #{$alert['ResultID']} - Table {$table}");
        }

        $this->db->commit();

        $output->writeln("{$records} records archived for Alert #{$alert['ResultID']}");
        $this->alertsArchived++;

        // Set the alert as archived in the resultset
        $sql = "UPDATE ws_results SET Archived = '1' WHERE ResultID = :result";
        $stm = $this->db->prepare($sql);
        $stm->execute(['result' => $alert['ResultID']]);
    }

    /**
     * Builds the columns of the insert query
     *
     * @param  array $row
     *
     * @return string
     */
    public function buildCols($row)
    {
        $keys = [];
        foreach ($row as $key => $val) {
            $keys[] = (string) $key;
        }

        return implode(",", $keys);
    }

    /**
     * Builds the values of the insert query
     *
     * @param  array $row
     *
     * @return string
     */
    public function buildValues($row)
    {
        $values = [];
        foreach ($row as $key => $val) {
            $val = str_replace("'", '', $val); // Remove any apostophies from char names
            $values[] = $val;
        }

        return implode("','", $values);
    }
}


1			<?php
2
3			namespace Ps2alerts\Api\Command;
4
5			use Ps2alerts\Api\Command\BaseCommand;
6			use Ps2alerts\Api\Repository\AlertRepository;
7			use Symfony\Component\Console\Command\Command;
8			use Symfony\Component\Console\Input\InputArgument;
9			use Symfony\Component\Console\Input\InputInterface;
10			use Symfony\Component\Console\Output\OutputInterface;
11
12			class ArchiveCommand extends BaseCommand
13			{
14			protected $dbArchive;
15			protected $alertRepo;
16			protected $guzzle;
17			protected $recordsArchived = 0;
18			protected $alertsArchived = 0;
19
20			protected function configure()
21			{
22			parent::configure(); // See BaseCommand.php
23			$this
24			->setName('Archive')
25			->setDescription('Archives old alerts')
26			->addArgument('start', InputArgument::OPTIONAL, 'ResultID to start from')
27			->addArgument('process', InputArgument::OPTIONAL, 'Number of results to process');
28			$this->dbArchive = $this->container->get('Database\Archive');
29			$this->alertRepo = $this->container->get('Ps2alerts\Api\Repository\AlertRepository');
30			$this->guzzle = $this->container->get('GuzzleHttp\Client');
31			}
32
33			/**
34			* Execution
35			*
36			* @param InputInterface $input
37			* @param OutputInterface $output
38			*
39			* @return void
40			*/
41			protected function execute(InputInterface $input, OutputInterface $output)
42			{
43			$output->writeln('Executing archive operations...');
44
45			$this->check($input, $output);
46			}
47
48			/**
49			* Checks for alerts to be archived then runs routing against said alerts
50			*
51			* @param OutputInterface $output
52			*
53			* @return void
54			*/
55			public function check(InputInterface $input, OutputInterface $output)
56			{
57			$obj = new \DateTime();
58			$obj->sub(new \DateInterval('P3D'));
59
60			$query = $this->alertRepo->newQuery();
61			$query->cols(['*']);
62			$query->where('ResultStartTime < ?', $obj->format('U'));
63			$query->where('Archived = 0');
64
65			if (!empty($input->getArgument('start'))) {
66			$query->where('ResultID >= ?', $input->getArgument('start'));
67			}
68
69			if (!empty($input->getArgument('process'))) {
70			$query->where('ResultID <= ?', $input->getArgument('process'));
71			}
72
73			$alerts = $this->alertRepo->fireStatementAndReturn($query);
74			$count = count($alerts);
75
76			$output->writeln("Detected {$count} alerts to be archived");
77
78			if ($count > 0) {
79			$tables = [
80			'ws_classes',
81			'ws_classes_totals',
82			'ws_combat_history',
83			'ws_factions',
84			'ws_map',
85			'ws_map_initial',
86			'ws_outfits',
87			'ws_players',
88			'ws_pops',
89			'ws_vehicles',
90			'ws_weapons',
91			'ws_xp'
92			];
93
94			for ($i = 0; $i < $count; $i++) {
95			$this->archive($alerts[$i], $tables, $output);
96
97			$per = ($i / $count) * 100;
98			$per = round($per, 2);
99			$output->writeln("{$i} / {$count} ({$per}%) processed");
100			}
101			}
102
103			$records = number_format($this->recordsArchived, 0);
104
105			$payload = [
106			'channel' => '#logs',
107			'username' => 'ps2alerts-archive',
108			'text' => "Alerts archived: {$this->alertsArchived} - Records archived: {$records}",
109			'icon_emoji' => ':open_file_folder:'
110			];
111
112			$this->guzzle->request(
113			'POST',
114			'https://hooks.slack.com/services/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',
115			['json' => $payload]
116			);
117
118			$output->writeln("Archived {$records} records!");
119			}
120
121			/**
122			* Execution of routine
123			*
124			* @param array $alert
125			* @param array $tables
126			* @param Symfony\Component\Console\Output\OutputInterface $output
127			*
128			* @return void
129			*/
130			public function archive($alert, $tables, OutputInterface $output)
131			{
132			$output->writeln("Processing Alert #{$alert['ResultID']}");
133
134			$this->dbArchive->beginTransaction();
135
136			// Get all data and insert it into the archive DB
137			foreach ($tables as $table) {
138			$output->writeln("Alert #{$alert['ResultID']} - Table: {$table}");
139
140			$sql = "SELECT * FROM {$table} WHERE resultID = :result";
141
142			$stm = $this->db->prepare($sql);
143			$stm->bindParam(':result', $alert['ResultID']);
144			$stm->execute();
145
146			if ($stm->rowCount() > 0) {
147			$values = '';
148			$cols = '';
149
150			// Build the values so we can do all of this in one huge query,
151			// which helps with transmission over the internet greatly.
152			while ($row = $stm->fetch(\PDO::FETCH_ASSOC)) {
153			$cols = $this->buildCols($row);
154			$data = $this->buildValues($row);
155			$values .= "('{$data}'),";
156			}
157
158			$values = rtrim($values, ',');
159			$sql = "INSERT INTO {$table} ({$cols}) VALUES {$values}";
160
161			$this->dbArchive->exec($sql);
162			}
163			}
164
165			try {
166			$output->writeln('Committing...');
167			$this->dbArchive->commit();
168			} catch (\Exception $e) {
169			$this->dbArchive->rollBack();
170			throw new \Exception($e->getMessage());
171			}
172
173			$records = 0;
174
175			$this->db->beginTransaction();
176
177			// Loop through all tables and delete the alert's data from the DB
178			foreach ($tables as $table) {
179			$sql = "DELETE FROM {$table} WHERE resultID = :result";
180			$stm = $this->db->prepare($sql);
181			$stm->execute(['result' => $alert['ResultID']]);
182
183			$this->recordsArchived += $stm->rowCount();
184			$records += $stm->rowCount();
185
186			$output->writeln("Archived {$stm->rowCount()} from Alert #{$alert['ResultID']} - Table {$table}");
187			}
188
189			$this->db->commit();
190
191			$output->writeln("{$records} records archived for Alert #{$alert['ResultID']}");
192			$this->alertsArchived++;
193
194			// Set the alert as archived in the resultset
195			$sql = "UPDATE ws_results SET Archived = '1' WHERE ResultID = :result";
196			$stm = $this->db->prepare($sql);
197			$stm->execute(['result' => $alert['ResultID']]);
198			}
199
200			/**
201			* Builds the columns of the insert query
202			*
203			* @param array $row
204			*
205			* @return string
206			*/
207			public function buildCols($row)
208			{
209			$keys = [];
210			foreach ($row as $key => $val) {
211			$keys[] = (string) $key;
212			}
213
214			return implode(",", $keys);
215			}
216
217			/**
218			* Builds the values of the insert query
219			*
220			* @param array $row
221			*
222			* @return string
223			*/
224			public function buildValues($row)
225			{
226			$values = [];
227			foreach ($row as $key => $val) {
228			$val = str_replace("'", '', $val); // Remove any apostophies from char names
229			$values[] = $val;
230			}
231
232			return implode("','", $values);
233			}
234			}
235

Maelstromeous / ps2alerts-api

ArchiveCommand::buildValues() A last analyzed 2018-01-02 23:34 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

ArchiveCommand::buildValues() A
last analyzed 2018-01-02 23:34 UTC