getdata.php 3.98 KB
Newer Older
1 2 3 4 5 6 7
<?php
require_once(dirname(__DIR__) . '/config.php');

// Initialize database
$dbh = new PDO('mysql:dbname='.DBNAME.';host='.DBHOST, DBUSER, DBPASS);

// Start from the last summarized pingback
cividesk's avatar
cividesk committed
8
$result = $dbh->query("SELECT MAX(last_ping_id) FROM pingback_site")->fetch();
9 10 11 12 13 14
if (empty($result[0])) $result[0] = 0;
echo "Starting from stat_id: $result[0]" . PHP_EOL;

// Feed raw pings into the summary database
$query = "
INSERT INTO pingback_site
15
  (`hash`, `version`, `lang`, `uf`, `ufv`, `civi_country`,
16
   `geoip_isoCode`, `MySQL`, `PHP`,
17 18 19
   `first_ping_id`, `first_timestamp`, `last_ping_id`, `last_timestamp`, `num_pings`,
   `Contact`, `Contribution`, `Participant`)
   SELECT
cividesk's avatar
cividesk committed
20
     hash, version, lang, uf, ufv, c.name,
21
     geoip_isoCode, MySQL, PHP,
cividesk's avatar
cividesk committed
22
     s.id, s.time, s.id, s.time, 1,
23 24
     e1.size AS Contact, e2.size AS Contribution, e3.size as Participant
   FROM " . DBPING . ".stats s
25
   LEFT JOIN civicrm_country c ON c.id = s.co
26 27 28
   LEFT JOIN " . DBPING . ".entities e1 ON e1.stat_id = s.id AND e1.name = 'Contact'
   LEFT JOIN " . DBPING . ".entities e2 ON e2.stat_id = s.id AND e2.name = 'Contribution'
   LEFT JOIN " . DBPING . ".entities e3 ON e3.stat_id = s.id AND e3.name = 'Participant'
cividesk's avatar
cividesk committed
29 30
   WHERE s.id > $result[0]
   ORDER BY s.id ASC
31 32
   LIMIT 30000
ON DUPLICATE KEY UPDATE
33
   version = s.version, lang = s.lang, uf = s.uf, ufv = s.ufv, civi_country = c.name,
34
   geoip_isoCode = s.geoip_isoCode, MySQL = s.MySQL, PHP = s.PHP,
35 36 37 38 39 40 41
   last_ping_id = s.id, last_timestamp = s.time, num_pings = num_pings + 1,
   Contact = e1.size, Contribution = e2.size, Participant = e3.size
   ";
$dbh->query($query);
$result = $dbh->query("SELECT COUNT(*) FROM pingback_site;")->fetch();
echo "Total sites: $result[0]" . PHP_EOL;

cividesk's avatar
cividesk committed
42 43 44 45 46 47 48 49 50 51
// Set the DB flavor - MySQL, MariaDB, Percona
$query = "
UPDATE pingback_site
   SET DB = CASE
         WHEN MySQL LIKE '%percona%' THEN 'Pr'
	     WHEN MySQL LIKE '%MariaDB%' THEN 'Ma'
	     ELSE 'My' END
 WHERE DB IS NULL";
$dbh->query($query);

52 53 54
// Calculate the active sites flag
$dbh->query("
UPDATE pingback_site
55 56 57 58
   SET is_active = (
         last_timestamp > (NOW() - INTERVAL 100 DAY)
         AND Contact > 10 AND Contact NOT IN (201, 202, 203, 204)
       )
59 60 61 62
");
$result = $dbh->query("SELECT COUNT(*) FROM pingback_site WHERE is_active = 1;")->fetch();
echo "Total active sites: $result[0]" . PHP_EOL;

cividesk's avatar
cividesk committed
63 64
// Create the cohort data on the first of each month
if (date('j') == 1) {
65 66 67 68 69 70 71
  echo "Recalculating cohort data:";
  // We need to calculate cohort data all over again since the
  // values might have changed with sites that have lingered
  // ex: someone from cohort X reconnects this month after been absent for a few months
  // Clear the cohort table
  $dbh->query("TRUNCATE pingback_cohort");
  // Loop over all previous cohorts and recalculate previous month's data
cividesk's avatar
cividesk committed
72
  $cohort = '2014-09';
73 74 75 76 77 78 79 80
  $thismonth = date('Y-m');
  while ($cohort < $thismonth) {
    echo " $cohort";
    $month = $cohort;
    while ($month < $thismonth) {
      $dbh->query("
INSERT INTO pingback_cohort (`cohort`, `month`, `num_sites`)
VALUES ('$cohort', '$month', (
cividesk's avatar
cividesk committed
81
            SELECT COUNT(*) FROM pingback_site
82 83 84
             WHERE LEFT(first_timestamp,7) = '$cohort' AND last_timestamp >= CONCAT('$month', '-01')))");
      $month = date('Y-m', strtotime($month.'-01 +1 month'));
    }
cividesk's avatar
cividesk committed
85 86
    $cohort = date('Y-m', strtotime($cohort.'-01 +1 month'));
  }
87
  echo PHP_EOL;
cividesk's avatar
cividesk committed
88 89
}

90 91 92 93
// Now calculate the extension stats
$dbh->query("TRUNCATE pingback_extension");
$query = "
INSERT INTO pingback_extension
94 95
  (`name`, `site_id`)
  SELECT e.name, s.id
96
    FROM pingback_site s
97
         LEFT JOIN " . DBPING . ".extensions e ON e.stat_id = s.last_ping_id AND e.enabled = 1
98 99 100 101 102 103 104 105
   WHERE s.is_active = 1
         AND LENGTH(e.name) > 2 AND e.name NOT LIKE 'org.civicrm.component%'
   ";
$dbh->query($query);
$result = $dbh->query("SELECT COUNT(*) FROM pingback_extension")->fetch();
echo "Unique active extensions: $result[0]" . PHP_EOL;
$result = $dbh->query("SELECT SUM(num_sites) FROM pingback_extension")->fetch();
echo "Total enabled extensions: $result[0]" . PHP_EOL;