aMemberPurge.prg

<?php
/**
 *  @package MemberHub
 *  @author Erin
 *
 *  @uses admin member delete/purge
 *    * delete orphaned members
 *    * delete users from member by going to user hub
 *
 *  @var string $sOperation: requested search action
 */

require_once("$admLibrary/aMemberSupport.i");

try {
  $admVars = array();
  $admOk = array(
    "operation" => array("filter" => FILTER_SANITIZE_STRING),
    "payload" => array("filter" => FILTER_SANITIZE_STRING)
  );
  HCU_ImportVars($admVars, "MEMBER_PURGE", $admOk);

  $dOperation = isset($admVars["MEMBER_PURGE"]["operation"]) ? $admVars["MEMBER_PURGE"]["operation"] : null;
  $dPayload = isset($admVars["MEMBER_PURGE"]["payload"]) ? $admVars["MEMBER_PURGE"]["payload"] : null;

  // decrypt payload only if there is one
  // payload won't exist if operation is empty
  // operation empty means just load card content
  $dMember = $dPayload ? MemberDecrypt($SYSENV, $Cu, $dPayload) : null;

  // load context depending on payload existence.
  $dContext = $dPayload ? MemberContext($SYSENV, $Cu, $dMember['member']) : MemberContext($SYSENV, $Cu);

  $aryResult = array();
  $aryReply = array();

  switch ($dOperation) {
    case "":
      PrintMemberPurge();
      break;
    case "memberPurge":
      header('Content-type: application/json');

      $mPurge = MemberPurge($SYSENV, $dbh, $dContext);
      $aryResult['data']['member'] = $mPurge['member'];
      $aryResult['info'] = $mPurge['message'];
      MemberReply($aryResult, $aryReply, $dOperation);

      break;
    default:
      throw new Exception("Unknown server request: " . $dOperation);
      break;
  }


} catch (Exception $e) {
  $aryReply['errors'][] = $e->getMessage();
  $aryResult['data'] = array();
  $aryResult['info'] = array();
  MemberReply($aryResult, $aryReply, $dOperation);
}


/**
   * Removing records from the following tables:
   * {cu}accountbalance
   * {cu}accounthistory
   * {cu}loanbalance
   * {cu}loanhistory
   * {cu}crossaccounts
   * {cu}memberacct
   * {cu}memberacctrights
   * {cu}transhdr
   * {cu}transdtl
   * {cu}holds
   */
function MemberPurge($pEnv, $pDbh, $pContext) {

  $dHistory = MemberReadHistory($pEnv, $pDbh, $pContext);
  $dValidateHistory = MemberValidateHistory($pEnv, $pContext, $dHistory);
  $dDelete = MemberDeleteHistory($pEnv, $pDbh, $pContext, $dValidateHistory);

  $sqlReturn = array();
  if ($dDelete['message'] == "Success") {
    $sqlReturn['member'] = $pContext['m_account'];
    $sqlReturn['message'] = "The following member account has been deleted successfully: " . $pContext['m_account'];
  } else {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to delete member account.");
  }

  return $sqlReturn;
}

function MemberReadHistory($pEnv, $pDbh, $pContext) {
  $cuTable = $pContext['cu_table'];
  $cuMember = $pContext['m_account'];

  $sqlReturn = array();
  $sqlData = array();
  //loan tables
  $sqlColumns = "
    DISTINCT ON (lb.loannumber) lb.loannumber AS a_type,
    lb.accountnumber AS a_number,
    lb.description AS a_desc,
    'Loan' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}loanhistory lh
    WHERE lh.accountnumber = '$cuMember'
      AND lh.loannumber = lb.loannumber";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}loanbalance lb
    WHERE lb.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read transaction history for loan accounts.");
  }
  $sqlData['loan'] = db_fetch_all($sqlSelectRs);

  //account tables
  $sqlColumns = "
    DISTINCT ON (ab.accounttype) ab.accounttype AS a_type,
    ab.accountnumber AS a_number,
    ab.description AS a_desc,
    'Deposit' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}accounthistory ah
    WHERE ah.accountnumber = '$cuMember'
      AND ah.accounttype = ab.accounttype";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}accountbalance ab
    WHERE ab.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read transaction history for deposit accounts.");
  }
  $sqlData['deposit'] = db_fetch_all($sqlSelectRs);

  //transaction tables
  $sqlColumns = "
    DISTINCT ON (tr.accounttype) tr.accounttype AS a_type,
    tr.accountnumber AS a_number,
    tr.memo AS a_desc,
    'Transaction' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}transdtl td
    WHERE td.transhdr_id = tr.id";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}transhdr tr
    WHERE tr.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read transaction history for accounts.");
  }
  $sqlData['transactions'] = db_fetch_all($sqlSelectRs);

  //crossaccount table
  $sqlColumns = "
    DISTINCT ON (ca.accounttype) ca.accounttype AS a_type,
    ca.accountnumber AS a_number,
    ca.description AS a_desc,
    'CrossAccount' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}crossaccounts
    WHERE accountnumber = '$cuMember'";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}crossaccounts ca
    WHERE ca.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read crossaccounts.");
  }
  $sqlData['crossaccounts'] = db_fetch_all($sqlSelectRs);

  //memberacct and memberacctrights tables
  $sqlColumns = "
    'NA' AS a_type,
    ma.accountnumber AS a_number,
    'NA' AS a_desc,
    'MemberAccount' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}memberacctrights mr
    WHERE mr.accountnumber = '$cuMember'";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}memberacct ma
    WHERE ma.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read transaction history for deposit accounts.");
  }
  $sqlData['memberacct'] = db_fetch_all($sqlSelectRs);

  //holds table
  $sqlColumns = "
    DISTINCT ON (h.accounttype) h.accounttype AS a_type,
    h.accountnumber AS a_number,
    h.description AS a_desc,
    'Holds' AS a_record";
  $sqlSub = "
    SELECT COUNT(*)
    FROM {$cuTable}holds
    WHERE accountnumber = '$cuMember'";
  $sqlSelect = "
    SELECT $sqlColumns, ($sqlSub) AS a_count
    FROM {$cuTable}holds h
    WHERE h.accountnumber = '$cuMember'";
  $sqlSelectRs = db_query($sqlSelect, $pDbh);
  if (!$sqlSelectRs) {
    $pEnv['logger']->error(db_last_error());
    throw new Exception("Failed to read holds.");
  }
  $sqlData['holds'] = db_fetch_all($sqlSelectRs);

  $sqlReturn = $sqlData;

  return $sqlReturn;
}

function MemberValidateHistory($pEnv, $pContext, $pHistory) {
  $dValidate = array();
  foreach ($pHistory as $action => $set) {
    if (is_array($set)) {
      $dValidate[$action] = array();
      foreach ($set as $key => $value) {
        $dValidate[$action][] = array(
          "_action" => "delete",
          "accounttype" => prep_save($value['a_type'], 25),
          "record" => prep_save($value['a_record'], 25)
        );
      }
    }
  }

  return $dValidate;
}

function MemberDeleteHistory($pEnv, $pDbh, $pContext, $pHistory) {

  $cuTable = $pContext['cu_table'];
  $cuMember = $pContext['m_account'];

  $cuHistoryLoan = HCU_array_key_exists('loan', $pHistory) ? $pHistory['loan'] : [];
  $cuHistoryDeposit = HCU_array_key_exists('deposit', $pHistory) ? $pHistory['deposit'] : [];
  $cuHistoryTransaction = HCU_array_key_exists('transactions', $pHistory) ? $pHistory['transactions'] : [];
  $cuHistoryCrossAccounts = HCU_array_key_exists('crossaccounts', $pHistory) ? $pHistory['crossaccounts'] : [];
  $cuHistoryMemberAcct = HCU_array_key_exists('memberacct', $pHistory) ? $pHistory['memberacct'] : [];
  $cuHistoryHolds = HCU_array_key_exists('holds', $pHistory) ? $pHistory['holds'] : [];

  $sqlReturn = array();
  $sqlDelete = "";
  $sqlDeleteRs = null;
  $sqlTrans = "";
  $sqlTransRs = null;

  try {
    // begin transaction
    $sqlTrans = "BEGIN TRANSACTION";
    $sqlTransRs = db_query($sqlTrans, $pDbh);

    // add sql for deposit deletions
    if (count($cuHistoryDeposit) > 0) {
      foreach ($cuHistoryDeposit as $key => $value) {
        $sqlDelete .= "
          DELETE FROM {$cuTable}accounthistory
          WHERE accountnumber = '$cuMember'
          AND accounttype = '{$value['accounttype']}';";

        $sqlDelete .= "
          DELETE FROM {$cuTable}accountbalance
          WHERE accountnumber = '$cuMember'
          AND accounttype = '{$value['accounttype']}';";
      }
    }

    // add sql for loan deletions
    if (count($cuHistoryLoan) > 0) {
      foreach ($cuHistoryLoan as $key => $value) {
        $sqlDelete .= "
          DELETE FROM {$cuTable}loanhistory
          WHERE accountnumber = '$cuMember'
          AND loannumber = '{$value['accounttype']}';";

        $sqlDelete .= "
          DELETE FROM {$cuTable}loanbalance
          WHERE accountnumber = '$cuMember'
          AND loannumber = '{$value['accounttype']}';";
      }
    }

    // add sql for transaction deletions
    if (count($cuHistoryTransaction) > 0) {
      foreach ($cuHistoryTransaction as $key => $value) {
        $sqlSelect = "
          SELECT id
          FROM {$cuTable}transhdr
          WHERE accountnumber = '$cuMember';";

        $sqlSelectRs = db_query($sqlSelect, $pDbh);
        if (!$sqlSelectRs) {
          $pEnv['logger']->error(db_last_error());
          throw new Exception("Failed to read transaction header records.");
        }

        $sqlData['transdtlids'] = db_fetch_all($sqlSelectRs);
        foreach($sqlData['transdtlids'] as $hdr => $dtl) {
          $hdrid = $dtl['id'];
          $sqlDelete .= "
          DELETE FROM {$cuTable}transdtl
          WHERE transhdr_id = $hdrid;";

        }

        $sqlDelete .= "
          DELETE FROM {$cuTable}transhdr
          WHERE accountnumber = '$cuMember';";

      }
    }

    // add sql for crossaccount deletions
    if (count($cuHistoryCrossAccounts) > 0) {
      foreach ($cuHistoryCrossAccounts as $key => $value) {
        $sqlDelete .= "
          DELETE FROM {$cuTable}crossaccounts
          WHERE accountnumber = '$cuMember'
          AND accounttype = '{$value['accounttype']}';";

      }
    }

    // add sql for memberaccts deletions
    if (count($cuHistoryMemberAcct) > 0) {
      foreach ($cuHistoryMemberAcct as $key => $value) {
        $sqlDelete .= "
          DELETE FROM {$cuTable}memberacctrights
          WHERE accountnumber = '$cuMember';";

        $sqlDelete .= "
          DELETE FROM {$cuTable}memberacct
          WHERE accountnumber = '$cuMember';";

      }
    }

    // add sql for holds deletions
    if (count($cuHistoryHolds) > 0) {
      foreach ($cuHistoryHolds as $key => $value) {
        $sqlDelete .= "
          DELETE FROM {$cuTable}holds
          WHERE accountnumber = '$cuMember'
          AND accounttype = '{$value['accounttype']}';";

      }
    }

    // delete query for all records
    $sqlDeleteRs = db_query($sqlDelete, $pDbh);
    if (!$sqlDeleteRs) {
      $pEnv['logger']->error(db_last_error());
      throw new Exception("Failed to delete account records.");
    }

    // commit transaction
    $sqlTrans = "COMMIT TRANSACTION";
    $sqlTransRs = db_query($sqlTrans, $pDbh);

  } catch (Exception $e) {
    // transaction failed
    $pEnv['logger']->error(db_last_error());

    // revert transaction
    $sqlTrans = "ROLLBACK TRANSACTION";
    $sqlTransRs = db_query($sqlTrans, $pDbh);
    throw new Exception($e->getMessage());
  }

  // send success message
  $sqlReturn['message'] = "Success";

  return $sqlReturn;
}

?>

<?php function PrintMemberPurge() { ?>

  <div id="gdPurge">
    <div class="hcu-template">
      <div class="hcu-edit-buttons k-state-default">
        <span class="hcu-icon-delete">
        </span>
        <a href="##" id="lnkCancel">Cancel</a>
        &ensp;
        <a href="##" id="btnUpdate" class="k-button k-primary">
          <i class="fa fa-trash fa-lg"></i>
          Delete
        </a>
      </div>
    </div>
  </div>

  <div id="gdInfo"></div>

  <div id="confirmationWindow"></div>

  <script type="text/x-kendo-template" id="gdOrphaned">
    <div class="col-sm-12" id="deleteDiv">
      <p>You are about to remove this member account and all its history records from the system.</p>
      <p>Do you wish to continue?</p>
    </div>
  </script>

  <script type="text/x-kendo-template" id="gdFull">
    <div class="col-sm-12">
      <p>There are existing users with this account.</p>
      <p>Member accounts cannot be deleted if there are users.
      Use the Related Users card to view which users
      are still using this member account.</p>
    </div>
  </script>

  <script id="confirmationTemplateDelete" type="text/x-kendo-template">
      <div id="confirmationStatus"></div>
        <div id="responseDiv">
          <div class="local-transfer-msg">
            #= message #
          </div>
      </div>
  </script>

  <script type="text/javascript">

    var MemberPurge = function() {
      var gdCardContainer = null;
      var gdCardWindows = null;

      var gdCall = null;
      var gdMember = null;
      var gdPayload = null;
      var gdAction = null;

      var gdDataSource = null;
      var gdDelete = null;
      var gdInfo = null;

      var EventOpenWindow = function(e) {
        var windowElement = this.element[0];
        var windowId = windowElement.id;

        switch (windowId) {
        }

        gdCardWindows.push(this);
      }

      var EventCloseWindow = function(e) {
        var windowElement = this.element[0];
        var windowId = windowElement.id;

        switch (gdAction) {
          case "deleteConfirm":
            EventPopWindow(windowId);
            var memberRequest = {
              operation: "memberPurge",
              payload: gdPayload
            };

            gdDataSource.transport.options.read.type = "POST";
            gdDataSource.read(memberRequest);
            break;
          default:
            EventPopWindow(windowId);
            break;
        }

        gdAction = null;

      }

      var EventPopWindow = function(windowId) {
        var popIndex = -1;
        for (var i = 0; i < gdCardWindows.length; i++) {
          var openWindow = gdCardWindows[i].element[0];

          var openId = openWindow.id;

          if (openId == windowId) {
            popIndex = i;
            break;
          }
        }

        if (popIndex > -1) {
          gdCardWindows.splice(popIndex, 1);
        }
      }

      var InitDataSources = function() {
        gdDataSource = new kendo.data.DataSource({
          transport: {
            read: {
              url: "main.prg",
              dataType: "json",
              contentType: "application/x-www-form-urlencoded",
              type: "GET",
              data: {
                ft: "103110"
              },
              cache: false
            }
          },
          schema: {
            type: "json",
            data: "response",
            model: {
              fields: {
                Results: { field: "Results" }
              }
            }
          },
          requestStart: function(e) {
            showWaitWindow();
          },
          requestEnd: function(e) {
            var error = null;
            var results = null;
            hideWaitWindow();
            if (e.response.hasOwnProperty("Results")) {
              results = e.response.Results;
              resultData = results.info;
              ShowSuccess(resultData);
            } else {
              gdInfo.content("Error Parsing Server");
              gdInfo.open();
              return [];
            }
          }
        });
      }

      var InitDataViews = function() {
        gdPurge = $("#gdPurge").kendoWindow({
          title: "Delete Member",
          modal: true,
          visible: false,
          resizable: false,
          minWidth: 300,
          maxWidth: 500,
          activate: EventOpenWindow,
          close: EventCloseWindow
        }).data("kendoWindow");

        gdInfo = $("#gdInfo").kendoDialog({
          title: "Delete Member",
          modal: true,
          visible: false,
          resizable: false,
          minWidth: 300,
          maxWidth: 300,
          show: EventOpenWindow,
          close: EventCloseWindow,
          actions: [
            { text: "Ok", primary: true }
          ]
        }).data("kendoDialog");

        // Confirmation dialog
        $("#confirmationWindow").kendoDialog({
          title: "Success",
          minWidth: 300,
          maxWidth: 300,
          modal: true,
          visible: false,
          resizable: false,
          actions: [
            {
              text: "Close",
              primary: true
            }
          ],
          close: function() {
            //redirect back to main account hub page
            window.location = 'main.prg?ft=103101';
          }
        }).data("kendoDialog");

      }

      var InitDataActions = function() {
        $("#btnUpdate").off();
        $("#btnUpdate").on("click", function(e) {
          e.preventDefault();
          if (gdMember.m_count == 0) {
            gdAction = "deleteConfirm";
            gdPurge.close();
          }
        });

        $("#lnkCancel").off();
        $("#lnkCancel").on("click", function(e) {
          e.preventDefault();
          gdAction = "deleteDeny";
          gdPurge.close();
        });
      }

      var ShowSuccess = function(message) {
        var confirmationData = { message: message };
        var confirmationTemplate = kendo.template($("#confirmationTemplateDelete").html());
        $("#confirmationWindow").data("kendoDialog").content(confirmationTemplate(confirmationData));
        $("#confirmationWindow").data("kendoDialog").open().toFront().center();
      }

      this.Open = function(windowStack) {
        gdCardWindows = windowStack;

        if (gdMember.m_count == 0) {
          $("#gdPurge").prepend(
            $("#gdOrphaned").html()
          );
          $("#btnUpdate").show();
        } else {
          $("#gdPurge").prepend(
            $("#gdFull").html()
          );
          $("#btnUpdate").hide();
        }

        var template= kendo.template($("#titleTemplate").html());
        gdMember.cardTitle= "Delete Member";
        gdPurge.title(template(gdMember));
        gdPurge.center();
        gdPurge.open();
      }

      this.Close = function() {
        gdPurge.destroy();

      }

      this.Data = function(payload, member) {
        gdPayload = payload;
        gdMember = member;
      }

      this.Init = function(hubCall, carContainer) {
        gdCall = hubCall;
        gdCardContainer = carContainer;

        InitDataSources();
        InitDataViews();
        InitDataActions();

        gdCall("MemberPurge", this);
      }

    }

    <?php // Library javascript functions
    getShowWaitFunctions(); ?>

  </script>

<?php }