atlhousingreport/scripts/migrateClientsToUsers.php

<?php

// ==================================
// Migrate RDI Clients To Users Table
// ==================================
// Insure this script is running from the CLI and not a browser.

if (!defined('STDIN')) {
    echo "This script must be run from the CLI, not a browser.";
    exit;
}

echo "Migrating RDI Clients to Users Table..." . PHP_EOL . PHP_EOL;

// Connect to the NBA Database

$settings = simplexml_load_file("../xml/settings.xml");

if (empty($settings)) {
    echo "Unable to load Settings." . PHP_EOL;
    exit();
}

$host = (string) $settings->ATLHousingReportDatabaseServer;
$database = (string) $settings->ATLHousingReportDatabase;
$user = (string) $settings->ATLHousingReportDatabaseUser;
$password = (string) $settings->ATLHousingReportDatabasePassword;

try {

    $atl_connection_one = new PDO("mysql:host={$host};dbname={$database}", $user, $password);
    $atl_connection_one->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException | Exception $e) {

    echo $e->getMessage() . PHP_EOL;
}

try {

    $atl_connection_two = new PDO("mysql:host={$host};dbname={$database}", $user, $password);
    $atl_connection_two->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException | Exception $e) {

    echo $e->getMessage() . PHP_EOL;
}

$users_sql = "insert into users

                   ( user_id,
                     user_name,
                     user_email,
                     user_client_name,
                     user_password,
                     user_roll,
                     user_change_password,
                     user_active,
                     user_creator,
                     user_created )

            values ( :user_id,
                     :user_name,
                     :user_email,
                     :user_client_name,
                     :user_password,
                     :user_roll,
                     :user_change_password,
                     :user_active,
                     :user_creator,
                     :user_created )";

$accountdetails_sql = "insert into usersubscriptions

                   ( usersubscription_user_serial,
                     usersubscription_report,
                     usersubscription_report_description,
                     usersubscription_stop_date_string,
                     usersubscription_stop_date,
                     usersubscription_origin )

            values ( :usersubscription_user_serial,
                     :usersubscription_report,
                     :usersubscription_report_description,
                     :usersubscription_stop_date_string,
                     :usersubscription_stop_date,
                     :usersubscription_origin  )";

$ahr_statement_users = $atl_connection_one->prepare($users_sql);
$ahr_statement_user_account_details = $atl_connection_two->prepare($accountdetails_sql);

// Migrate Unique Users to Users table

$rdi_clients = $atl_connection_one->query("select client_name, client_id, user_name, password from rdi_clients group by user_name order by client_name");

$counter = 0;

while ($rdi_client = $rdi_clients->fetch(PDO::FETCH_ASSOC)) {

    $user_id = $rdi_client["client_id"];
    $user_name = ucwords(strtolower($rdi_client["user_name"]));
    $user_email = "";
    $user_client_name = $rdi_client["client_name"];
    $user_password = password_hash($rdi_client["password"], PASSWORD_DEFAULT);
    $user_roll = "User";
    $user_change_password = 0;
    $user_active = true;

    $user_creator = "Migration";
    $user_created = date("Y-m-d H:i:s");

    $ahr_statement_users->bindValue(":user_id", $user_id);
    $ahr_statement_users->bindValue(":user_name", $user_name);
    $ahr_statement_users->bindValue(":user_email", $user_email);
    $ahr_statement_users->bindValue(":user_client_name", $user_client_name);
    $ahr_statement_users->bindValue(":user_password", $user_password);
    $ahr_statement_users->bindValue(":user_roll", $user_roll);
    $ahr_statement_users->bindValue(":user_change_password", $user_change_password, PDO::PARAM_BOOL);
    $ahr_statement_users->bindValue(":user_active", $user_active, PDO::PARAM_BOOL);
    $ahr_statement_users->bindValue(":user_creator", $user_creator);
    $ahr_statement_users->bindValue(":user_created", $user_created);

    $ahr_statement_users->execute();

    $last_user = $atl_connection_one->lastInsertId();

    // Fetch and migrate user account details

    $rdi_clients_details = $atl_connection_one->query("select rpt_id, rpt_descr, stop_date_str, stop_date from rdi_clients where user_name = '{$user_name}'");

    while ($rdi_client_detail = $rdi_clients_details->fetch(PDO::FETCH_ASSOC)) {

        $subscriptions_user_serial = $last_user;
        $subscriptions_report = $rdi_client_detail['rpt_id'];
        $subscriptions_report_description = $rdi_client_detail['rpt_descr'];
        $subscriptions_stop_date_string = $rdi_client_detail['stop_date_str'];
        $subscriptions_stop_date = $rdi_client_detail['stop_date'];
        $subscriptions_origin = "Migration";

        $ahr_statement_user_account_details->bindValue(":usersubscription_user_serial", $subscriptions_user_serial);
        $ahr_statement_user_account_details->bindValue(":usersubscription_report", $subscriptions_report);
        $ahr_statement_user_account_details->bindValue(":usersubscription_report_description", $subscriptions_report_description);
        $ahr_statement_user_account_details->bindValue(":usersubscription_stop_date_string", $subscriptions_stop_date_string);
        $ahr_statement_user_account_details->bindValue(":usersubscription_stop_date", $subscriptions_stop_date);
        $ahr_statement_user_account_details->bindValue(":usersubscription_origin", $subscriptions_origin);

        $ahr_statement_user_account_details->execute();
    }

    $counter++;

    // Issue an update message every 10 records

    if (($counter % 10) == 0) {
        echo "Processed $counter records..." . PHP_EOL;
    }
}


// --------------
// End of script.
// --------------

echo "Processed $counter records." . PHP_EOL;

echo "Script has completed normally." . PHP_EOL;

exit;
?>