-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathlist.php
More file actions
156 lines (155 loc) · 7.71 KB
/
list.php
File metadata and controls
156 lines (155 loc) · 7.71 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
#!/usr/bin/php -q
<?php
require_once 'database.inc';
// A constant to be used as an error return status
define ('DB_CONNECTION_FAILED',1);
// Try connecting to MySQL
@ $db_connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$db_connection) {
// Write to STDERR
fwrite(STDERR,mysql_error()."\n");
exit(DB_CONNECTION_FAILED);
}
mysql_select_db(DB_NAME);
fwrite(STDOUT,"Connected to database\n");
// DESC - descending order
$query = "SELECT YEAR(date), COUNT(*) FROM rounds GROUP BY date ORDER BY COUNT(*) DESC LIMIT 1;";
if (!($result = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$row = mysql_fetch_array($result);
mysql_free_result($result);
$year = $row['YEAR(date)'];
fwrite(STDOUT,"LYAC: $year\n");
// Delete the records of this year from tables turnout, activities and list.
$query = "DELETE FROM turnout WHERE YEAR(date) = '$year';";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$query = "DELETE FROM activities WHERE YEAR(date) = '$year';";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$query = "DELETE FROM list WHERE YEAR(created) = '$year';";
// if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
// Cycle of bands and rounds
$query = "SELECT rounds.date, bands.bandID FROM bands INNER JOIN bands_groups ON bands.bandID = bands_groups.bandID INNER JOIN rounds ON bands_groups.group_bands = rounds.group_bands ORDER BY rounds.date;";
if (!($result = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
while ($row = mysql_fetch_array($result)) {
$date = $row[0];
$bandID = $row[1];
// Table list
// Cycle of logs
$query = "SELECT logs.callsignID, logs.wwlID, messages.emailID FROM logs
INNER JOIN attachments ON logs.attachmentID = attachments.attachmentID
INNER JOIN messages ON attachments.sourceID = messages.messageID
WHERE logs.date = '$date' and logs.bandID = '$bandID' and attachments.source = 'email';";
if (!($LOGs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
while ($logs = mysql_fetch_array($LOGs)) {
$callsignID = $logs[0];
$wwlID = $logs[1];
$emailID = $logs[2];
//
$query = "SELECT listID FROM list WHERE callsignID = '$callsignID' and wwlID = '$wwlID' and bandID = $bandID";
if (!($HAMs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$num_results = mysql_num_rows($HAMs);
$row = mysql_fetch_array($HAMs);
mysql_free_result($HAMs);
$listID = $row['listID'];
if ($num_results == 0) {
$query = "INSERT INTO list VALUES (NULL, $callsignID, $wwlID, $bandID, '$date', 'Yes');";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$listID = mysql_insert_id();
}
else {
$query = "UPDATE list SET valid = 'Yes' WHERE listID = $listID;";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
$query = "SELECT * FROM activities WHERE listID = $listID and emailID = $emailID and date = '$date';";
if (!($HAMs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$num_results = mysql_num_rows($HAMs);
mysql_free_result($HAMs);
if ($num_results == 0) {
$query = "INSERT INTO activities VALUES ($listID, '$date', $emailID);";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$query = "DELETE FROM turnout WHERE listID = $listID AND date = '$date';";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
}
mysql_free_result($LOGs);
// If absent log
$query = "SELECT qsorecords.callsign FROM qsorecords INNER JOIN logs ON qsorecords.logID = logs.logID WHERE logs.date = '$date' and logs.bandID = '$bandID' GROUP BY callsign HAVING COUNT(*) > 1";
if (!($QSOs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
while ($qsos = mysql_fetch_array($QSOs)) {
$callsign = $qsos[0];
// $query = "SELECT qsorecords.gridsquare FROM qsorecords INNER JOIN logs ON qsorecords.logID = logs.logID WHERE logs.date = '$date' and logs.bandID = '$bandID' and qsorecords.callsign = '$callsign' GROUP BY gridsquare";
// Find two most frequent gridsquare values
$query = "SELECT qsorecords.gridsquare, COUNT(*) FROM qsorecords INNER JOIN logs ON qsorecords.logID = logs.logID WHERE logs.date = '$date' and logs.bandID = $bandID and qsorecords.callsign = '$callsign' GROUP BY gridsquare ORDER BY COUNT(*) DESC LIMIT 2;";
if (!$WWLs = mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
if (mysql_num_rows($WWLs) == 2) {
$wwls = mysql_fetch_array($WWLs);
$gridsquare = $wwls[0];
$first_freq = $wwls[1];
$wwls = mysql_fetch_array($WWLs);
$second_freq = $wwls[1];
}
else if (mysql_num_rows($WWLs) == 1) {
$wwls = mysql_fetch_array($WWLs);
$gridsquare = $wwls[0];
$first_freq = $wwls[1];
$second_freq = 0;
}
else {
$gridsquare = "";
$first_freq = 0;
$second_freq = 0;
}
mysql_free_result($WWLs);
if ($first_freq > $second_freq && $first_freq > 1) {
//
$query = "SELECT * FROM callsigns WHERE callsign = '$callsign';";
if (!($CALLSIGNs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
if (mysql_num_rows($CALLSIGNs) == 0) {
$query = "INSERT INTO callsigns VALUES (NULL, '$callsign');";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
mysql_free_result($CALLSIGNs);
//
$query = "SELECT * FROM wwls WHERE wwl = '$gridsquare';";
if (!($WWLs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
if (mysql_fetch_array($WWLs) == 0) {
$query = "INSERT INTO wwls VALUES (NULL, '$gridsquare');";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$gridsquareID = mysql_insert_id();
}
//
$query = "SELECT * FROM list WHERE callsignID = call2id('$callsign') and wwlID = wwl2id('$gridsquare') and bandID = $bandID";
if (!($HAMs = mysql_query($query))) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$num_results = mysql_num_rows($HAMs);
$row = mysql_fetch_array($HAMs);
mysql_free_result($HAMs);
$listID = $row['listID'];
if ($num_results == 0) {
$query = "INSERT INTO list VALUES (NULL, call2id('$callsign'), wwl2id('$gridsquare'), $bandID, '$date', 'Yes');";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$listID = mysql_insert_id();
}
// Participation history table
$query = "SELECT listID FROM turnout WHERE listID = $listID and date = '$date'
UNION
SELECT listID FROM activities WHERE listID = $listID and date = '$date';";
if (!$history = mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
$num_results = mysql_num_rows($history);
mysql_free_result($history);
if ($num_results == 0) {
$query = "INSERT INTO turnout VALUES ($listID, '$date');";
if (!mysql_query($query)) die("Error " . mysql_errno( ) . " : " . mysql_error( ));
}
}
}
mysql_free_result($QSOs);
}
mysql_free_result($result);
//
$result = mysql_query("SELECT count(*) FROM list;");
$row = mysql_fetch_row($result);
$num = $row[0];
mysql_free_result($result);
fwrite(STDOUT, "Number of rows table 'list' : $num OK\n");
mysql_close($db_connection);
exit(0);
?>