· 7 years ago · Dec 29, 2018, 01:44 PM
1<?php
2
3# Configs
4$config = [
5 'host' => '127.0.0.1',
6 'port' => 3306,
7 'database' => 'db_name',
8 'user' => 'user',
9 'pass' => 'pass',
10 'table' => 'table_name',
11];
12
13try {
14 $startTime = microtime(true);
15
16 $filename = getFilename($argv);
17 $spentLimit = getSpentLimit($argv);
18 $connection = getDbConnection($config);
19
20 createTable($connection, $config['table']);
21 loadFile($filename, $connection, $config['table']);
22
23 $result = getResult($connection, $config['table'], $spentLimit);
24
25 print_r($result);
26}
27catch (PDOException $e) {
28 echo 'DB Error: '. $e->getMessage() . PHP_EOL;
29 echo 'DB Error: '. $e->getFile() .':'. $e->getLine() . PHP_EOL;
30}
31catch (Exception $e) {
32 echo 'Error: '. $e->getMessage() . PHP_EOL;
33}
34finally {
35 dropTable($connection, $config['table']);
36
37 echo 'Memory peak: '. (memory_get_peak_usage(true)/1024/1024) .' MiB' . PHP_EOL;
38 echo 'Execution time : '. (microtime(true) - $startTime) .' seconds' . PHP_EOL;
39}
40
41/**
42 * Get filename
43 *
44 * @param array $argv
45 * @return string
46 * @throws Exception
47 */
48function getFilename(array $argv): string
49{
50 $filename = $argv[1] ?: null;
51 if(!$filename) {
52 throw new Exception('Argument "filename" is missing');
53 }
54
55 if(!is_string($filename)) {
56 throw new Exception('Argument "filename" must be string');
57 }
58
59 if(!file_exists($filename)) {
60 throw new Exception('File "'. $filename .'" is missing');
61 }
62
63 return $filename;
64}
65
66/**
67 * Get spent limit
68 *
69 * @param array $argv
70 * @return int
71 * @throws Exception
72 */
73function getSpentLimit(array $argv): int
74{
75 $limit = $argv[2] ?: null;
76 if(!$limit) {
77 throw new Exception('Argument "spent" is missing');
78 }
79
80 if(!is_numeric($limit)) {
81 throw new Exception('Argument "spent" must be numeric');
82 }
83
84 return $limit;
85}
86
87/**
88 * Get result
89 *
90 * @param PDO $connection
91 * @param string $table
92 * @param int $spentLimit
93 * @return array
94 */
95function getResult(PDO $connection, string $table, int $spentLimit): array
96{
97 $data = getData($connection, $table, $spentLimit);
98
99 $result = [];
100 foreach ($data as $uid => $user) {
101 ksort($user);
102
103 if (array_sum($user) >= $spentLimit) {
104 $userSum = 0;
105 foreach ($user as $unixtime => $sum) {
106 $userSum += $sum;
107
108 if ($userSum >= $spentLimit) {
109 $result[$uid] = date('Y-m-d', $unixtime);
110 break;
111 }
112 }
113 }
114 }
115
116 ksort($result);
117
118 return $result;
119}
120
121/**
122 * Get data from DB
123 *
124 * @param PDO $connection
125 * @param string $table
126 * @param int $spentLimit
127 * @return array
128 */
129function getData(PDO $connection, string $table, int $spentLimit): array
130{
131 # Get UIDs
132 $sql = 'SELECT `uid`, SUM(`sum`) AS summary
133 FROM '. $table .'
134 GROUP BY `uid`
135 HAVING summary >= '. $spentLimit;
136 $uids = $connection->query($sql)->fetchAll(PDO::FETCH_COLUMN, 0);
137
138 # Get Data
139 $sql = 'SELECT *
140 FROM '. $table .'
141 WHERE `uid` IN ('. implode(',', $uids) .')';
142 $result = $connection->query($sql)->fetchAll(PDO::FETCH_ASSOC);
143
144 $data = [];
145 foreach ($result as $row) {
146 $data[$row['uid']][strtotime($row['date'])] += $row['sum'];
147 }
148
149 return $data;
150}
151
152/**
153 * Get DB connection
154 *
155 * @param array $config
156 * @return PDO
157 */
158function getDbConnection(array $config): PDO
159{
160 $dsn = 'mysql:host='. $config['host'] .';port='. $config['port'] .';dbname='. $config['database'];
161 $connection = new PDO($dsn, $config['user'], $config['pass'], [
162 PDO::ATTR_PERSISTENT => true,
163 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
164 PDO::MYSQL_ATTR_LOCAL_INFILE => true,
165 ]);
166
167 return $connection;
168}
169
170/**
171 * Create and truncate table in DB
172 *
173 * @param PDO $connection
174 * @param string $table
175 */
176function createTable(PDO $connection, string $table): void
177{
178 $sql = 'CREATE TABLE IF NOT EXISTS '. $table .' (
179 `uid` int(11) UNSIGNED NOT NULL,
180 `date` date NOT NULL,
181 `sum` double(7, 2) UNSIGNED NOT NULL
182 );';
183 $sql .= 'TRUNCATE TABLE `'. $table .'`;';
184
185 $connection->query($sql);
186}
187
188/**
189 * Drop table from DB
190 *
191 * @param PDO $connection
192 * @param string $table
193 */
194function dropTable(PDO $connection, string $table): void
195{
196 $sql = 'DROP TABLE IF EXISTS `'. $table .'`';
197
198 $connection->query($sql);
199}
200
201/**
202 * Load data from file to DB
203 *
204 * @param string $filename
205 * @param PDO $connection
206 * @param string $table
207 */
208function loadFile(string $filename, PDO $connection, string $table): void
209{
210 try {
211 $connection->beginTransaction();
212
213 $sql = 'LOAD DATA LOCAL INFILE \''. $filename .'\''. PHP_EOL;
214 $sql .= 'INTO TABLE `'. $table .'`'. PHP_EOL;
215 $sql .= 'FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\''. PHP_EOL;
216 $sql .= 'IGNORE 1 ROWS'. PHP_EOL;
217 $connection->query($sql);
218
219 $connection->commit();
220 }
221 catch (PDOException $e) {
222 $connection->rollBack();
223
224 throw $e;
225 }
226}