DbLoanCreator.php

<?php
/**
 * @copyright HomeCu 05/2019
 *
 * A class specifically for adding sample data to loans. Not used directly;
 * inject this object into your consumer class then call public
 * addSampleData() with an array of prepared/mapped data.
 *
 * $obj->addSampleData([array data]);
 *
 * Expects
 *
 * array[
 *  [table identifier] = [
 *    [
 *      [field identifier] => [value],
 *      // for each field to insert
 *    ]
 *  ],
 *  // for each table to insert data
 * ]
 *
 * Returns array [
 *  'success'  => [
 *    [table name] => [int n count of successes],
 *    // for each table
 *  ],
 *  'errors' => [
 *    [table name] => array[
 *      [string specific error],
 *      // for each error in this table
 *    ],
 *    // for each table
 *  ]
 * ]
 *
 */
class DbLoanCreator
{
  /** @var object the DB connection/link object injected from client */
  protected $DbObj;

  /** @var array of data, should already be mapped to DB fields.  */
  protected $data = [];

  /** @var array with keys success empty/array records added to which tables */
  protected $response = [];

  /**
   * Constructor, initialize the database link object (your "$dbh")
   * @param object $DbObject
   * @return void
   */
  public function __construct($DbObject) {

    $this->Set('DbObj', $DbObject);
  }

  /**
   * Entry point, add data to the DB and respond with errors or success log. Note
   * we flush out response array with each call or it will exponentially add
   * counts and errors to earlier values.
   * @param array $data already mapped to DB fields
   * @return array
   */
  public function AddSampleData($data = []) {

    return $this
      ->Set('data', $data)
      ->InitResponse()
      ->AddRecords()
      ->ReturnResponse();
  }

  /**
   * The following three don't really belong in a "creator" but since there's no DB layer,
   * here they are. GetRecord() gets a single record by a single field value (usually id.)
   * @param string $table
   * @param string $keyfield
   * @param int $value
   * @return array
   */
  public function GetRecord($table, $keyfield, $value) {

    $sql      = "select * from $table where $keyfield = $1";
    $msg_part = "table $table, key field $keyfield, value $value SQL $sql";
    $result   = pg_query_params($this->DbObj, $sql, [$value]);

    if (! $result) {
      return [
        'error' => "No DB result in getRecord $msg_part " . pg_last_error(),
        'data' =>  []
      ];
    }

    $res = pg_fetch_assoc($result);

    return [
      'error' => ($res)? null : "No result found for $msg_part in getRecord",
      'data' => ($res)? $result : []
    ];
  }

  /**
   * We **should** be using the native OID method OR "returning [keyfield]" on inserts
   * but for various reasons this is problematic. Get the last inserted ID.
   * @param string $table
   * @param string $keyfield
   * @return array
   */
  public function GetLastRecord($table, $keyfield) {

    $sql      = "select $keyfield from $table order by $keyfield desc limit 1";
    $msg_part = "table $table, key field $keyfield SQL $sql";
    $result   = pg_query($this->DbObj, $sql);

    if (! $result) {
      return [
        'error' => "No DB result in GetLastRecord $msg_part " . pg_last_error(),
        'data' =>  []
      ];
    }

    $res = pg_fetch_assoc($result);

    return [
      'error' => ($res)? null : "No result found for $msg_part in GetLastRecord",
      'data' => ($res)? $res : []
    ];
  }


  /**
   * Generally used to get the entire schema template record, get multiple rows by a single keyfield.
   * @param string $table
   * @param string $keyfield
   * @param int $value
   * @return array
   */
  public function GetList($table, $keyfield, $value) {

    $sql      = "select * from $table where $keyfield = $1 order by $keyfield asc";
    $msg_part = "table $table, key field $keyfield value $value SQL $sql";
    $result   = pg_query_params($this->DbObj, $sql, [$value]);

    if (! $result) {
      return [
        'error' => "No DB result in GetList $msg_part " . pg_last_error(),
        'data' =>  []
      ];
    }

    $res = pg_fetch_all($result);

    return [
      'error' => ($res)? null : "No result found for $msg_part in GetList",
      'data' => ($res)? $res : []
    ];
  }

  /**
   * A typical setter, sets internal properties (DbObj, cu)
   * Usually these are public, don't think we'll need it
   * @param string prop property identifier
   * @param mixed property to set
   * @return $this
   */
  protected function Set($prop, $object) {

    $this->{$prop} = $object;
    return $this;
  }

  /**
   * Set the response array. Response will be two arrays, successes and errors. Both
   * are returned giving a running log of the process, see doc block at head.
   * @return $this
   */
  protected function InitResponse() {

    $this->response = [
      'success' => [],
      'errors'  => []
    ];

    return $this;
  }

  /**
   * Add records using $this->data.
   * @return $this
   */
  protected function AddRecords() {

    foreach ($this->data as $table => $data) {

      if ($this->IsErrors()) {

        return $this;
      }

      if ($this->PopulateTable($table, $data)) {
        if (isset($this->response['success'][$table])) {
          $this->response['success'][$table]++;
          continue;
        }
        // else ...
        $this->response['success'][$table] = 1;
      }
    }

    return $this;
  }

  /**
   * Populate the table with records from the data rows, one by one, using pg_query_params
   * to avoid futzing about with quoting and escaping. Could probably compile as one
   * statement, but it's cmd line and light.
   * @param string $table
   * @param array $data
   * @return bool
   */
  protected function PopulateTable($table = '', $data = []) {

    if (! $this->IsAtLeastTableAndData($table, $data)) {
      return false;
    }

    foreach ($data as $arr) {

      $sql    = $this->BuildInsertStatement($table);
      $result = pg_query_params($this->DbObj, $sql, $arr);

      if (! $result) {
        $this->response['errors'][$table][] = "Failed on insert: $sql last error: " . pg_last_error();
        return false;
      }
    }

    return true;
  }

  /**
   * Build the insert statements. Generally I would prefer to dynamically create fields and values
   * but you have two choices in PHP for PostgreSql: fiddle about with data types and figure out
   * which to escape and which not, or use pg_query_params which solves that and other issues
   * (such as SQL injection.) The problem then becomes that the placeholders can't be
   * dynamic, you need placeholders like $1, $2, etc. We only have a few tables in
   * a test script so letting it slide. Just be sure the fields correspond . . .
   * @param string $table
   * @return string
   */
  protected function BuildInsertStatement($table) {

    switch($table) {

      case 'lnappuser':
        return "insert into $table (email, pwd, allow_e_comm, cu, confidenceword,
                failedloginattempts, challenge_quest_id, userlogintype, banking_user_id,
                session_account) values($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";

      case 'lnappschemadetail':
        return "insert into $table (loanid, pageid, groupid, lineid, fieldtype,
                fieldvalue, fieldattr) values($1, $2, $3, $4, $5, $6, $7)";

      case 'lnappuserresponse':
        return "insert into $table (userid, loanid, respstatus, respcomments,
              respcoreloanappid, respstarton, respsubmiton, respmodifiedon, respfname,
              resplname, respmname, respssn, respdob, respmember, respphone, respapplication,
              respstatusdesc, respamt, resplastinquire) values($1, $2, $3, $4, $5, $6, $7, $8,
              $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)";

      case 'lnappschemamaster':
        return "insert into $table (cu, loantitle, loandisclosure_fragment, lastmodified) values($1, $2, $3, $4)";

      case 'lnappuser_questselect':
        return "insert into $table(userid, questid, user_answer) values($1, $2, $3)";

      default:
        return '';
    }
  }

  /**
   * Not much validation, being a command line script you should only be sending The Good Stuff.
   * Setting a var so we can collect multiple errors (and improve validation if needed)
   * @param string $table
   * @param array $data
   * @return bool
   */
  protected function IsAtLeastTableAndData($table, $data) {

    $valid = true;

    if (empty($table)) {
      $this->response['errors'][$table][] = "Empty table value found, fix it.";
      $valid = false;
    }

    if (count($data) == 0) {
      $this->response['errors'][$table][] = "Empty data array found, fix it.";
      $valid = false;
    }

    return $valid;
  }

  /**
   * Return a response, this approach allows chaining of methods above it.
   * @return array
   */
  protected function ReturnResponse() {

    return $this->response;
  }

  /**
   * Got errors?
   * @return bool
   */
  protected function IsErrors() {

    return count($this->response['errors']) > 0;
  }

}