cleanup-test-records.php

<?php
/**
 * Run this script after running generate-loans.php to create random test loans.
 * This script will clean up all the records you created. Basically it will
 * DELETE all loans with loanid  >= 1000 (local DB only affected.)
 *
 */

// Needed from the existing system
require_once(__DIR__ . '/../../../../../app/web/shared/library/hcuCommon.i');
require_once(__DIR__ . '/../../../../../app/web/shared/library/db.postgres.i');
require_once(__DIR__ . '/../../../../../app/web/shared/library/hcuEnv.i');

$sysenv = LoadSystemEnv("eforms");
$dbh    = GetDBH($sysenv['db']);

/**********************************************************/
if (! UserInputOkay()) {
  echo "Exiting program." . PHP_EOL . PHP_EOL;
  exit;
}

try {
  $ids = GetTestLoans($dbh);
} catch (Exception $e) {
  echo "Exception thrown: {$e->getMessage()}" . PHP_EOL;
}

foreach ($ids as $loanid) {

  try {
    $userid = GetUserId($dbh, $loanid);
    echo "deleting loan id $loanid user $userid" . PHP_EOL;
    DeleteUserRecords($dbh, $userid);
    DeleteLoanRecords($dbh, $loanid);

  } catch (Exception $e) {
    echo "Exception thrown: {$e->getMessage()}" . PHP_EOL;
    break;
  }
}

echo PHP_EOL . "All done. Type 'php generate-loans.php' to generate test loan records." . PHP_EOL . PHP_EOL;

/**********************************************************/

/**
 * Wait for user input.
 * @return bool
 */
function UserInputOkay()
{
  $line = null;

  while ($line !== 'y') {

    if ($line == 'q') {
      return false;
    }

    echo "
    Local test loan records cleanup script.
    This will DELETE any local loan records and associated user records
    with a loan ID >= 1000. Enter 'y' to continue (not 'Y' or yes, 'y'.)
    Enter 'q' to exit without changes.
    ";

    $handle = fopen ("php://stdin","r");
    $line   = trim(fgets($handle));
    fclose($handle);

  }

  return true;
}

/**
 * Get an array of all test loans with ID >= 1000.
 * @param object $db
 * @throws Exception
 * @return array
 */
function GetTestLoans($db)
{
  $test_loans = 1000;
  $loan_ids   = [];
  $sql        = "select loanid from lnappuserresponse where loanid >= $1";
  $result     = pg_query_params($db, $sql, [$test_loans]);

  if (! $result) {
    throw new Exception("Exception getting test loans: " . pg_last_error());
  }

  $res = pg_fetch_all($result);

  for ($i = 0; $i < count($res); $i++) {
    $loan_ids[] = $res[$i]['loanid'];
  }

  return $loan_ids;
}

/**
 * Get the user id for the current loan in the loop.
 * @param object $db
 * @param int $loanid
 * @throws Exception
 * @return int
 */
function GetUserId($db, $loanid)
{
  $sql    = "select userid from lnappuserresponse where loanid = $1";
  $result = pg_query_params($db, $sql, [$loanid]);

  if (! $result) {
    throw new Exception("Exception getting iser id for loan id $loanid: " . pg_last_error());
  }

  $res = pg_fetch_row($result);

  return (isset($res[0]))? $res[0] : 0;
}

/**
 * Delete test user records.
 * @param object $db
 * @param int $userid
 * @throws Exception
 * @return void
 */
function DeleteUserRecords($db, $userid)
{
  $sql = "delete from lnappuser_questselect where userid = $1";
  if (! pg_query_params($db, $sql, [$userid])) {
    throw new Exception("Could not delete loan app question select: " . pg_last_error());
  }

  $sql = "delete from lnappuser where userid = $1";
  if (! pg_query_params($db, $sql, [$userid])) {
    throw new Exception("Could not delete loan app user record: " . pg_last_error());
  }
}

/**
 * Delete all the loan data for the current test record.
 * @param object $db
 * @param int $loanid
 * @throws Exception
 * @return void
 */
function DeleteLoanRecords($db, $loanid)
{
  foreach (['lnappschemadetail', 'lnappschemamaster', 'lnappuserresponse'] as $table) {

    $sql = "delete from $table where loanid = $1";

    if (! pg_query_params($db, $sql, [$loanid])) {
      throw new Exception("Could not delete loan app record for table $table: " . pg_last_error());
    }
  }
}