asylum-to-sqlite

#!/usr/bin/env php
<?php

$members_url = "https://lataamo.helsinki.hacklab.fi/admin/members/member/";
$slackid_url = "https://lataamo.helsinki.hacklab.fi/admin/access/token/?all=&ttype=6";
$cookies = __DIR__.'/cookies.txt';
require(__DIR__.'/common.php');

// Prepare cURL
$curl = curl_init();
if ($curl === false) {
    die("Curl initialization failed\n");
}
if (curl_setopt_array($curl, [
    CURLOPT_COOKIEFILE => $cookies,
    CURLOPT_COOKIEJAR => $cookies,
    CURLOPT_FAILONERROR => true,
    CURLOPT_RETURNTRANSFER => true,
    //CURLOPT_VERBOSE => true,
]) === false) {
    die("Setting curl options failed\n");
}

// Prepare SQL
$db = init_db();
$db->exec('BEGIN; DROP TABLE IF EXISTS member; CREATE TABLE member (name,email,nick,city,credit,mtypes,grants,email_norm,slack_id);');
$stmt_member = $db->prepare('INSERT INTO member (name,email,nick,city,credit,mtypes,grants,email_norm) VALUES (:rname,:email,:nick,:city,:credit_formatted,:mtypes_formatted,:grants_formatted,:email_norm)');
$stmt_slack = $db->prepare('UPDATE member SET slack_id=:id WHERE email=:email');

function asylum_load_members($page)
{
    global $members_url, $curl;

    $url = $members_url . '?' . http_build_query(['p' => $page]);
    curl_setopt($curl, CURLOPT_URL, $url);

    $html = curl_exec($curl);
    if ($html === false) {
        die("Unable to load document from Asylum\n");
    }

    // To DOM
    $doc = new DOMDocument();
    $doc->loadHTML('<?xml encoding="UTF-8">'.$html);
    return $doc;
}

function asylum_load_slack_userids()
{
    global $slackid_url, $curl;

    curl_setopt($curl, CURLOPT_URL, $slackid_url);

    $html = curl_exec($curl);
    if ($html === false) {
        die("Unable to load document from Asylum\n");
    }

    // To DOM
    $doc = new DOMDocument();
    $doc->loadHTML('<?xml encoding="UTF-8">'.$html);
    return $doc;
}

function store_page($page)
{
    global $stmt_member;

    $doc = asylum_load_members($page);
    $xpath = new DOMXpath($doc);
    $rows = $xpath->query("id('result_list')/tbody/tr");
    $count = 0;

    foreach ($rows as $row) {
        $stmt_member->reset();
    
        // Just extract cells
        $cells = $xpath->query("th|td", $row);
        foreach ($cells as $cell) {
            $cls = $cell->getAttribute('class');
            if (str_starts_with($cls, 'field-')) {
                // Only cells with field prefix contain data we need
                $param = ':'.substr($cls, 6);
                switch ($param) {
                case ':credit_formatted':
                    $val = floatval($cell->textContent);
                    break;
                default:
                    $val = $cell->textContent;
                    if ($val === '') {
                        $val = null;
                    }
                }

                if ($param === ':email' && $val !== null) {
                    $stmt_member->bindValue(':email_norm', normalize_email($val));
                }

                $stmt_member->bindValue($param, $val);
            }
        }

        $stmt_member->execute();
        $count++;
    }

    return $count;
}

function store_slack_ids()
{
    global $db, $stmt_slack;

    $doc = asylum_load_slack_userids();
    $xpath = new DOMXpath($doc);
    $rows = $xpath->query("id('result_list')/tbody/tr");

    foreach ($rows as $row) {
        $stmt_slack->reset();
    
        // Just extract cells
        $user_p = $xpath->query("*[@class='field-owner_f']", $row);
        $slack_p = $xpath->query("*[@class='field-value_formatted']", $row);

        if (preg_match('/.*<(.*)>/', $user_p[0]->textContent, $matches) !== 1) {
            print("Skipping invalid user string\n");
            continue;
        }
        $email = $matches[1];
        $stmt_slack->bindValue('email', $email);
        $stmt_slack->bindValue('id', $slack_p[0]->textContent);
        $stmt_slack->execute();
        if ($db->changes() != 1) {
            die("Something weird going on, email $email not found\n");
        }
    }
}

for ($i = 1; ; $i++) {
    $got = store_page($i);
    print("Page $i: parsed $got members\n");
    if ($got < 100) break;
}

store_slack_ids();

// Housekeeping
$db->exec('UPDATE member AS m SET slack_id=s.userid FROM (SELECT userid, email_norm FROM slack) AS s WHERE m.slack_id IS NULL AND s.email_norm = m.email_norm');
$changes = $db->changes();
print("Matched $changes email pairs with Slack\n");

// Commit the changes
$db->exec('END');