tlkm_db_ht.php

<?php

const DB_HOST = "1.1.1.1";
const DB_USER = "1";
const DB_PASS = "1";
const DB_PORT = 3306;
const DB_NAME = "00002_indihome";

$g_user_hashtable = [];

function pdo(): PDO
{
	return new PDO("mysql:host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
}

function load_user_hashtable(PDO $pdo, array &$uht)
{
	printf("Loading user hashtable...\n");
	$st = $pdo->prepare("SELECT `id`, `nik` FROM `users`;");
	$st->execute();
	while ($r = $st->fetch(PDO::FETCH_ASSOC))
		$uht[$r["nik"]] = $r["id"];

	printf("Loaded %d users\n", count($uht));
}

function tlkm_insert()
{
	global $g_user_hashtable;

	$h = fopen("metranet_log.csv", "rb");
	if (!$h) {
		printf("Failed to open file\n");
		return;
	}

	$pdo = pdo();

	load_user_hashtable($pdo, $g_user_hashtable);
	$st_users = $pdo->prepare("INSERT INTO `users` (`tlkm_id`, `nik`, `name`, `gender`) VALUES (:tlkm_id, :nik, :name, :gender);");
	$st_br_hist = $pdo->prepare("INSERT INTO `browsing_history` (`lg_id`, `datetime`, `realm`, `meta_keyword`, `top_level_domain`, `platform`, `browser`, `url_access`, `google_text_search`, `ip_addr`, `screen_res`, `geolocation`, `user_id`) VALUES (:lg_id, :datetime, :realm, :meta_keyword, :top_level_domain, :platform, :browser, :url_access, :google_text_search, :ip_addr, :screen_res, :geolocation, :user_id);");
	$st_user_ips = $pdo->prepare("INSERT IGNORE INTO `user_ips` (`ip_addr`) VALUES (:ip_addr);");
	$st_sel_user = $pdo->prepare("SELECT `id` FROM `users` WHERE `nik` = :nik;");

	$i = 0;
	while (true) {
		$r = fgetcsv($h);
		if (!$r)
			break;

		if ($i++ === 0)
			continue;

		$user = json_decode($r[12], true);
		if ($user) {
			if ($user["sex"] === "LAKI-LAKI")
				$user["sex"] = "m";
			else
				$user["sex"] = "f";

			$em = explode("@telkom.net", $user["email"], 2);
			if (count($em) === 2)
				$user["tlkm_id"] = $em[0];
			else
				$user["tlkm_id"] = $user["email"];
		} else {
			$user = NULL;
		}

		if ($user) {
			if (isset($g_user_hashtable[$user["nik"]])) {
				$user_id = $g_user_hashtable[$user["nik"]];
			} else {
				$st_users->execute([
					":tlkm_id"	=> $user["tlkm_id"],
					":nik"		=> $user["nik"],
					":name"		=> $user["name"],
					":gender"	=> $user["sex"]
				]);
				$user_id = $pdo->lastInsertId();
				if ($user_id === "0") {
					$st_sel_user->execute([":nik" => $user["nik"]]);
					$user_id = $st_sel_user->fetchColumn();
				}

				$g_user_hashtable[$user["nik"]] = $user_id;
			}
		} else {
			$user_id = NULL;
		}

		$ip = $r[9];
		if ($ip) {
			$st_user_ips->execute([":ip_addr" => inet_pton($ip)]);
			$ip_id = $pdo->lastInsertId();
		} else {
			$ip_id = NULL;
		}

		$date = date("Y-m-d H:i:s", strtotime($r[1]));
		printf("%016d Inserting: %s user_id=%010d; nik=%s", $i, $date, $user_id, ($user ? $user["nik"] : "NULL"));
		$st_br_hist->execute([
			":lg_id"			=> hex2bin($r[0]),
			":datetime"			=> $date,
			":realm"			=> $r[2] ? $r[2] : NULL,
			":meta_keyword"			=> $r[3] ? $r[3] : NULL,
			":top_level_domain"		=> $r[4] ? $r[4] : NULL,
			":platform"			=> $r[5] ? $r[5] : NULL,
			":browser"			=> $r[6] ? $r[6] : NULL,
			":url_access"			=> $r[7] ? $r[7] : NULL,
			":google_text_search"		=> $r[8] ? $r[8] : NULL,
			":ip_addr"			=> $ip_id,
			":screen_res"			=> $r[10] ? $r[10] : NULL,
			":geolocation"			=> $r[11] ? $r[11] : NULL,
			":user_id"			=> $user_id
		]);
		printf(".. OK!\n");
	}
}

tlkm_insert();