20190108163001_insert_friendbuy_to_referral_conversion.php

<?php
use Phinx\Migration\AbstractMigration;
class InsertFriendbuyToReferralConversion extends AbstractMigration
{
    public function change()
    {
        // we only care about rows that are not duplicate
        $friendbuyRows = $this->fetchAll("SELECT *  FROM friendbuy_conversions WHERE is_duplicated = 0 
            AND new_order_id IS NOT NULL
        ");
        $referralTable = $this->table('referral_conversions');
        foreach ($friendbuyRows as $friendbuyRow) {
            $data = [[
                'referrer_email' => $friendbuyRow['email'],
                'referred_email' => $friendbuyRow['new_order_customer_email'],
                'order_id' => $friendbuyRow['new_order_id'],
                'order_date' => $friendbuyRow['new_order_date'],
                'reward_amount' => $friendbuyRow['reward_amount'],
                'reward_type' => $friendbuyRow['reward_type'],
                'added_credit' => $friendbuyRow['added_credit'],
                'raw_data' => $friendbuyRow['raw_data'],
                'vendor_id' => 1 //set vendor_id to 1 for friendbuy
            ]];
            $referralTable->insert($data);
        }
        $referralTable->saveData();
        // edge case: found instances for duplicate conversion rows where the duplicate row has added_credited = 1
        // but the original row (i.e is_duplicate == false) has added_credit = 0, in this case
        // we want the new row to have added_credit = 1
        $fbuyOrdersWithAddedCreditInDuplidateRowOnly = $this->fetchAll("SELECT new_order_id FROM friendbuy_conversions WHERE new_order_id IN
            (SELECT new_order_id FROM friendbuy_conversions WHERE added_credit = 1 and is_duplicated != 0)
            AND added_credit = 0
            AND is_duplicated = 0
        ");
        foreach($fbuyOrdersWithAddedCreditInDuplidateRowOnly as $orderWithAddedCredit) {
            $orderId = $orderWithAddedCredit['new_order_id'];
            $this->execute("UPDATE referral_conversions SET added_credit = true WHERE order_id = '$orderId'");
        }
    }
}