2018121400_cucmsresponseAccount.php

<?php
/**
 * Notes:
 * 1. Anything complicated needs to be done with SQL.
 * 2. Always have a check to know if the migration needs to occur (up or down).
 * 3. Use up() and down(), not change(), because of the SQL
 */
use Phinx\Migration\AbstractMigration;

class cucmsresponseAccount extends AbstractMigration {
    public function up() {
        $table = "cucmsresponse";

        if ($this->hasTable($table)) {

            $pkey = "cucmsresponse_pkey";
            $sql = "alter table $table drop constraint $pkey";
            $this->query($sql);

            $sql = "alter table $table add constraint $pkey primary key (docsid, cu, user_id, accountnumber)";
            $this->query($sql);

            // Now need to update $table table.  For users with one account (most), this will be just updating the accountnumber.
            // For other users, I will delete and insert n records for all accounts.  I have to do this by credit union.

            // Get the docsids for estatement start and estatement stop.
            $sql = "select a.docsid as startid, b.docsid as stopid
                    from cucmsdocs a cross join cucmsdocs b
                    where a.docsname = 'esTermsStart' and b.docsname = 'esTermsStop'";
            $row = $this->fetchRow($sql);
            $startId = intval($row["startid"]);
            $stopId = intval($row["stopid"]);

            // First, update the accountnumber to empty string for all the response that AREN'T estatement start or stop AND aren't already empty string.
            // There are no existing cases of the accountnumber being null.
            $sql = "update $table set accountnumber = '' where docsid not in ($startId, $stopId) and accountnumber <> ''";
            $this->query($sql);

            // Then find all the CUs where <cucode>user, <cucode>useraccounts, and <cucode>memberacct tables exist.
            $sql = "select upper(a.cu) as cu
                    from (select substring(table_name from 0 for (char_length(table_name) - 9)) as cu from information_schema.tables
                    where table_type = 'BASE TABLE' and table_schema = 'public' and table_name like '%memberacct') a
                    inner join information_schema.tables b on a.cu || 'user' = b.table_name
                    inner join information_schema.tables c on a.cu || 'useraccounts' = c.table_name
                    order by a.cu";
            $rows = $this->fetchAll($sql);

            $regex = "select cr.docsid, '{{CU}}'::text as cu, u.user_id, ma1.accountnumber as account1, ma2.accountnumber as account2,
                      row_number() over (partition by cr.docsid, u.user_id, ma1.accountnumber) as rown
                      from {{CU}}useraccounts ua1
                      inner join $table cr on ua1.user_id = cr.user_id and cr.cu = '{{CU}}'::text and cr.docsid in ($startId, $stopId)
                      left join {{CU}}useraccounts ua2 on ua1.user_id = ua2.user_id and ua1.accountnumber <> ua2.accountnumber
                      inner join {{CU}}memberacct ma1 on ua1.accountnumber = ma1.accountnumber
                      left join {{CU}}memberacct ma2 on ua2.accountnumber = ma2.accountnumber
                      inner join {{CU}}user u on ua1.user_id = u.user_id
                      order by cr.docsid, u.user_id, ma1.accountnumber";

            foreach($rows as $cuRow) {
                // replace {{CU}} with the CU.
                $cu = trim($cuRow["cu"]);
                $cuRegex = str_replace("{{CU}}", $cu, $regex);

                // Simple case: one accountnumber per user.  Just update the accountnumber.
                // The accountnumber will be updated to one accountnumber for a user with multiple accounts.  (Corrected in next step.)
                $sql = "update $table a set accountnumber = b.accountnumber
                        from (
                            select t.docsid, t.cu, t.user_id, t.account1 as accountnumber from ($cuRegex) t where t.rown = 1
                        ) b where a.docsid = b.docsid and a.cu = b.cu and a.user_id = b.user_id";
                $this->query($sql);

                // Complex case: multiple accountnumbers per user.  Insert into new records for accounts that the account isn't updated to in the previous step.
                // Need to get from the table again because I need the same responseon time.
                $sql = "insert into $table (docsid, cu, accountnumber, responseon, user_id)
                        select t.docsid, t.cu, t.account1, r.responseon, t.user_id from ($cuRegex) t
                        inner join $table r on t.docsid = r.docsid and t.cu = r.cu and t.user_id = r.user_id
                        and t.account1 <> r.accountnumber and t.rown = 1 and t.account2 is not null";
                $this->query($sql);
            }
        }
    } // End "up" function.

    public function down() {

    } // End "down" function
}