7ZFHupxf

1<?php
2
3$db_host = '127.0.0.1';
4$db_user = 'root';
5$db_pass = '';
6$db_name = 'UTM5';
7$db_arc_name = 'UTM5arc';
8
9$tables = [
10    [
11        'name' => 'discount_transactions_all',
12        'backup' => 'arc_discount_transactions_all',
13        'type' => 1,
14        'datefield' => 'discount_date'
15    ],
16    [
17        'name' => 'discount_transactions_iptraffic_all',
18        'backup' => 'arc_discount_transactions_iptraffic_all',
19        'type' => 2,
20        'datefield' => 'discount_date'
21    ],
22    [
23        'name' => 'dhs_sessions_log',
24        'backup' => 'arc_dhs_sessions_log',
25        'type' => 5,
26        'datefield' => 'last_update_date'
27    ],
28    [
29        'name' => 'payment_transactions',
30        'backup' => 'arc_payment_transactions',
31        'type' => 7,
32        'datefield' => 'payment_enter_date'
33    ]
34];
35
36echoDate("Script start");
37
38$link = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
39if (!$link) {
40    echoDate('Error: Can not connect to MySQL.');
41    echoDate('Errno: ' . mysqli_connect_errno());
42    echoDate('Error message: ' . mysqli_connect_error());
43    exit;
44}
45
46$start_date = strtotime('first day of previous month 00:00:00');
47$end_date = strtotime('first day of this month 00:00:00');
48
49echoDate("Archive since " . $start_date . " to " . $end_date);
50
51// RENAME TABLES
52foreach ($tables as $t) {
53    moveTable($link, $t['name'], $t['backup']);
54
55    mysqli_query($link, "INSERT INTO {$t['name']} SELECT * FROM {$t['backup']} WHERE {$t['datefield']} >= {$end_date}");
56    $affected_rows = mysqli_affected_rows($link);
57    echoDate("INSERT INTO {$t['name']} SELECT * FROM {$t['backup']} WHERE {$t['datefield']} >= {$end_date}");
58    echoDate("Affected rows " . $affected_rows);
59
60    mysqli_query($link, "DELETE FROM {$t['backup']} WHERE {$t['datefield']} >= {$end_date}");
61    $affected_rows = mysqli_affected_rows($link);
62    echoDate("DELETE FROM {$t['backup']} WHERE {$t['datefield']} >= {$end_date}");
63    echoDate("Affected rows " . $affected_rows);
64}
65
66// CREATE TRIGGERS
67mysqli_query($link, 'DROP TRIGGER IF EXISTS discount_transactions_all_AINS');
68mysqli_query($link, "CREATE TRIGGER discount_transactions_all_AINS AFTER INSERT ON discount_transactions_all FOR EACH ROW
69    BEGIN
70        UPDATE accounts SET last_discount = NEW.discount_date WHERE id = NEW.account_id AND NEW.discount > 0;
71
72        INSERT INTO ots_discount_credits (id, account_id, discount, service_id, discount_period_id, slink_id, discount_date)
73        SELECT NEW.id, c.account_id, NEW.discount, c.service_id, NEW.discount_period_id, c.slink_id, NEW.discount_date
74        FROM ots_services_credits as c
75        WHERE c.account_id = NEW.account_id AND c.service_id = NEW.service_id AND c.slink_id = NEW.slink_id;
76    END");
77mysqli_query($link, "DROP TRIGGER IF EXISTS payment_transactions_AINS");
78mysqli_query($link, "CREATE TRIGGER payment_transactions_AINS AFTER INSERT ON payment_transactions FOR EACH ROW UPDATE accounts SET last_payment = NEW.payment_enter_date WHERE id = NEW.account_id;");
79mysqli_query($link, "DROP TRIGGER IF EXISTS dhs_sessions_log_BINS");
80mysqli_query($link, "CREATE TRIGGER dhs_sessions_log_BINS BEFORE INSERT ON dhs_sessions_log FOR EACH ROW SET NEW.recv_date=UNIX_TIMESTAMP(now());");
81
82// NEW ARCHIVE ID
83$archive_id_query = mysqli_query($link, 'SELECT COALESCE(MAX(archive_id), 0) + 1 FROM archives');
84$archive_id = mysqli_fetch_array($archive_id_query, MYSQLI_NUM)[0];
85mysqli_free_result($archive_id_query);
86echoDate("New archive id " . $archive_id);
87
88// MOVE DATA TO ARCHIVE DATABASE
89while (true) {
90    $suffix = date('m_Y', $start_date);
91    $rows_count = 0;
92    foreach ($tables as $t) {
93        $rows_count_query = mysqli_query($link, "SELECT COUNT(*) FROM {$t['backup']} WHERE {$t['datefield']} >= {$start_date} AND {$t['datefield']} < {$end_date}");
94        $rows_count += mysqli_fetch_array($rows_count_query, MYSQLI_NUM)[0];
95    }
96
97    if (!$rows_count)
98        break;
99
100    echoDate('Processing from ' . date('m Y', $start_date) . ' to ' . date('m Y', $end_date));
101
102    foreach ($tables as $t) {
103        $archive = "{$db_arc_name}.{$t['name']}_{$suffix}";
104        mysqli_query($link, "CREATE TABLE {$archive} LIKE {$t['backup']}");
105        mysqli_query($link, "INSERT INTO {$archive} SELECT * FROM {$t['backup']} WHERE {$t['datefield']} >= {$start_date} AND {$t['datefield']} < {$end_date}");
106        mysqli_query($link, "INSERT INTO archives (archive_id, table_type, table_name, start_date, end_date) VALUES ({$archive_id}, {$t['type']}, '{$archive}', {$start_date}, {$end_date})");
107    }
108
109    $archive_id++;
110    $start_date = strtotime('-1 month', $start_date);
111    $end_date = strtotime('-1 month', $end_date);
112}
113
114// DROP BACKUP TABLES
115foreach ($tables as $t) {
116    mysqli_query($link, "DROP TABLE {$t['backup']}");
117}
118
119echoDate('Processing end');
120mysqli_close($link);
121
122/**
123 * Move SQL table
124 * @param type $link
125 * @param string $from
126 * @param string $to
127 * @return boolean
128 */
129function moveTable($link, $from, $to) {
130    echoDate("Move {$from} to {$to}");
131
132    if ($show_query = mysqli_query($link, "SHOW CREATE TABLE {$from}")) {
133        $sql = mysqli_fetch_array($show_query, MYSQLI_ASSOC)['Create Table'];
134        //echoDate('SQL: ' . $sql);
135        mysqli_free_result($show_query);
136
137        mysqli_query($link, "RENAME TABLE {$from} TO {$to};");
138        mysqli_query($link, $sql);
139        return true;
140    } else {
141        echoDate('Error: can not move table.');
142        echoDate('Errno: ' . mysqli_errno($link));
143        echoDate('Error message: ' . mysqli_error($link));
144        return false;
145    }
146}
147
148function echoDate($in) {
149    $curdate = date("Y.m.d H:i:s");
150    echo '[' . $curdate . '] ' . $in . PHP_EOL;
151    return;
152}
153
154?>