avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 1 | <?php |
| 2 | if (php_sapi_name() != "cli") |
| 3 | exit(); |
| 4 | |
| 5 | // Font dels nombres d'habitants: https://catsalut.gencat.cat/web/.content/minisite/catsalut/proveidors_professionals/registres_catalegs/documents/poblacio-referencia.pdf |
| 6 | $HABITANTS = [ |
| 7 | "Alt Pirineu i Aran" => 67277, |
| 8 | "Lleida" => 362850, |
| 9 | "Camp de Tarragona" => 607999, |
| 10 | "Terres de l'Ebre" => 176817, |
| 11 | "Girona" => 861753, |
| 12 | "Catalunya Central" => 526959, |
avm99963 | 0b3b407 | 2020-05-17 02:39:07 +0200 | [diff] [blame] | 13 | "Barcelona" => 5050190, |
| 14 | "Barcelona Ciutat" => 1693449, |
| 15 | "Metropolità Sud" => 1370709, |
| 16 | "Metropolità Nord" => 1986032, |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 17 | ]; |
| 18 | |
| 19 | $CODENAME = [ |
| 20 | "Alt Pirineu i Aran" => "AltPirineuAran", |
| 21 | "Lleida" => "Lleida", |
| 22 | "Camp de Tarragona" => "CampDeTarragona", |
| 23 | "Terres de l'Ebre" => "TerresDeLEbre", |
| 24 | "Girona" => "Girona", |
| 25 | "Catalunya Central" => "CatalunyaCentral", |
avm99963 | 0b3b407 | 2020-05-17 02:39:07 +0200 | [diff] [blame] | 26 | "Barcelona" => "Barcelona", |
| 27 | "Barcelona Ciutat" => "BarcelonaCiutat", |
| 28 | "Metropolità Sud" => "MetropolitaSud", |
| 29 | "Metropolità Nord" => "MetropolitaNord", |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 30 | ]; |
| 31 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 32 | // Funció per obtenir el nombre de casos nous al dia originalDay+translation |
| 33 | // a partir de les dades de la regió sanitaria (dataRegio). |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 34 | function getSumDay($originalDay, $translation, &$dataRegio) { |
| 35 | if ($translation >= 0) |
| 36 | $day = (clone $originalDay)->add(new DateInterval("P".abs($translation)."D")); |
| 37 | else |
| 38 | $day = (clone $originalDay)->sub(new DateInterval("P".abs($translation)."D")); |
| 39 | |
| 40 | foreach ($dataRegio as $row) { |
| 41 | $rowDay = new DateTime($row["data"]); |
| 42 | if ($day == $rowDay) return $row["sum_numcasos"]; |
| 43 | } |
| 44 | |
| 45 | return 0; |
| 46 | } |
| 47 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 48 | // Funció per fer una consulta a la taula de dades |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 49 | function query($soql) { |
avm99963 | 4b07f16 | 2020-06-04 13:35:57 +0200 | [diff] [blame] | 50 | $url = "https://analisi.transparenciacatalunya.cat/resource/xuwf-dxjd.json?\$query=".urlencode($soql); |
| 51 | $raw = file_get_contents($url); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 52 | return json_decode($raw, true); |
| 53 | } |
| 54 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 55 | // Demanem una llista del nombre de casos cada dia a cada regió sanitària |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 56 | $data = query("SELECT data, regiosanitariadescripcio AS regio, sum(numcasos) AS sum_numcasos |
| 57 | WHERE |
avm99963 | 4b07f16 | 2020-06-04 13:35:57 +0200 | [diff] [blame] | 58 | resultatcoviddescripcio = 'Positiu PCR' AND |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 59 | regiosanitariadescripcio <> 'No classificat' |
| 60 | GROUP BY regiosanitariadescripcio, data |
avm99963 | 7e562d4 | 2020-07-15 23:27:38 +0200 | [diff] [blame] | 61 | ORDER BY data ASC, regiosanitariadescripcio |
| 62 | LIMIT 50000"); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 63 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 64 | // Fem un array que tindrà com a elements un array per cada regió amb el |
| 65 | // contingut de totes les files d'aquella regió |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 66 | $dataPerRegio = []; |
| 67 | foreach ($data as $row) { |
| 68 | if (!isset($dataPerRegio[$row["regio"]])) $dataPerRegio[$row["regio"]] = []; |
| 69 | $dataPerRegio[$row["regio"]][] = $row; |
| 70 | } |
| 71 | |
| 72 | $summary = []; |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 73 | foreach ($dataPerRegio as $regio => $dataRegio) { // Per a cada regió |
| 74 | if (!in_array($regio, array_keys($CODENAME))) |
| 75 | die("[fatal error] No tenim contemplada la regió '".$regio."'.\n"); |
| 76 | |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 77 | $summary[$regio] = []; |
| 78 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 79 | // Veiem quin és el primer i l'últim dia de la sèrie |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 80 | $oldestDay = new DateTime("today"); |
| 81 | $newestDay = new DateTime(); |
| 82 | $newestDay->setTimestamp(0); |
| 83 | |
| 84 | foreach ($dataRegio as $row) { |
| 85 | $date = new DateTime($row["data"]); |
| 86 | if ($date < $oldestDay) $oldestDay = $date; |
| 87 | if ($date > $newestDay) $newestDay = $date; |
| 88 | } |
| 89 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 90 | // Si l'últim dia és avui, posem que sigui ahir, perquè no volem informació |
| 91 | // incompleta sobre avui. |
| 92 | if ($oldestDay == (new DateTime("today"))) |
| 93 | $oldestDay = new DateTime("yesterday"); |
| 94 | |
| 95 | // Ara calculem les rhos. |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 96 | $rhos = []; |
| 97 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 98 | // Considerem cada dia a partir de 6 dies després del primer dia, i fins al |
| 99 | // dia anterior a l'últim dia (extrems inclosos) |
| 100 | for ($currentDate = (clone $oldestDay)->add(new DateInterval("P6D")); |
| 101 | $currentDate < $newestDay; |
| 102 | $currentDate->add(new DateInterval("P1D"))) { |
| 103 | // Calculem la rho (velocitat reproductiva efectiva) per aquell dia. |
| 104 | // Fórmula: https://biocomsc.upc.edu/en/shared/avaluacio_risc.pdf |
| 105 | $num = getSumDay($currentDate, 1, $dataRegio) + |
| 106 | getSumDay($currentDate, 0, $dataRegio) + |
| 107 | getSumDay($currentDate, -1, $dataRegio); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 108 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 109 | $den = getSumDay($currentDate, -4, $dataRegio) + |
| 110 | getSumDay($currentDate, -5, $dataRegio) + |
| 111 | getSumDay($currentDate, -6, $dataRegio); |
| 112 | |
| 113 | if ($num != 0 && $den == 0) continue; |
| 114 | |
| 115 | $rho = ($num == 0 ? 0 : $num/$den); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 116 | |
| 117 | $rhos[] = [ |
| 118 | "data" => $currentDate->format("c"), |
| 119 | "rho" => $rho |
| 120 | ]; |
| 121 | } |
| 122 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 123 | // Considerem cada dia a partir de 13 dies després del primer dia, i fins el |
| 124 | // dia anterior a l'últim dia (extrems inclosos) |
| 125 | for ($currentDate = (clone $oldestDay)->add(new DateInterval("P13D")); |
| 126 | $currentDate < $newestDay; |
| 127 | $currentDate->add(new DateInterval("P1D"))) { |
| 128 | // Calculem Rho_7 i IA_14 |
| 129 | // Rho_7(t) := \sum_{i=0}^{7} Rho(t - i) |
| 130 | // IA_14(t) := \sum_{i=0}^{14} N(t - i), |
| 131 | // on N(j) és el nombre de casos nous confirmats per PCR el dia j. |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 132 | $sum = 0; |
| 133 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 134 | $p13Date = (clone $currentDate)->sub(new DateInterval("P13D")); |
| 135 | $p6Date = (clone $currentDate)->sub(new DateInterval("P6D")); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 136 | |
| 137 | foreach ($dataRegio as $row) { |
| 138 | $date = new DateTime($row["data"]); |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 139 | if ($date >= $p13Date && $date <= $currentDate) { |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 140 | $sum += $row["sum_numcasos"]; |
| 141 | } |
| 142 | } |
| 143 | |
| 144 | $rhoAverage = 0; |
| 145 | $rhoCount = 0; |
| 146 | |
| 147 | foreach ($rhos as $row) { |
| 148 | $date = new DateTime($row["data"]); |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 149 | if ($date >= $p6Date && $date <= $currentDate) { |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 150 | ++$rhoCount; |
| 151 | $rhoAverage += $row["rho"]; |
| 152 | } |
| 153 | } |
| 154 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 155 | // Si no hem trobat rhos (rhoCount == 0) és perquè el numerador no era 0 |
| 156 | // però el denominador era sempre 0 al calcular les rhos. Aleshores, tot i |
| 157 | // que no poguem calcular la rho_7 a causa de no poder calcular les rho_t |
| 158 | // individuals, aquest fet ens indica que el creixement ha sigut altíssim, |
| 159 | // i per tant posem una rho_7 de 1000000000, que se surt de la gràfica. |
| 160 | $rhoAverage = ($rhoCount == 0 ? 1000000000 : $rhoAverage/$rhoCount); |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 161 | |
| 162 | $summary[$regio][] = [ |
avm99963 | 0b3b407 | 2020-05-17 02:39:07 +0200 | [diff] [blame] | 163 | "data" => $currentDate->format("d/m/y"), |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 164 | "ia14" => $sum*(1e5/$HABITANTS[$regio]), |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 165 | "rho7" => $rhoAverage |
| 166 | ]; |
| 167 | } |
| 168 | } |
| 169 | |
avm99963 | 227cbb0 | 2020-06-09 13:51:25 +0200 | [diff] [blame] | 170 | // Posem les dades a diversos fitxers per tal que les pugui llegir el gnuplot |
avm99963 | 00a4003 | 2020-05-07 21:02:32 +0200 | [diff] [blame] | 171 | foreach ($summary as $regio => $summaryRegio) { |
| 172 | $file = fopen("/tmp/covid19graphgenerator-".$CODENAME[$regio].".dat", "w"); |
| 173 | |
| 174 | foreach ($summaryRegio as $row) |
| 175 | fwrite($file, $row["data"]." ".$row["ia14"]." ".$row["rho7"]."\n"); |
| 176 | |
| 177 | fclose($file); |
| 178 | } |