<?php
/*******************************************************************************

    Copyright 2013 Whole Foods Co-op

    This file is part of CORE-POS.

    CORE-POS is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    CORE-POS is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    in the file license.txt along with IT CORE; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

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

class EquityHistoryTask extends FannieTask
{

    public $name = 'Equity History';

    public $description = 'Extracts equity transactions and
adds them to dedicated history tables. Fetches any new 
transactions in the previous 15 days. Can be safely run
repeatedly. Normally run after rotating dtransactions data.
Deprecates nightly.equity.php.';

    public $default_schedule = array(
        'min' => 30,
        'hour' => 1,
        'day' => '*',
        'month' => '*',
        'weekday' => '*',
    );

    private $loggedP = null;

    private function isLogged($dbc, $info)
    {
        if ($this->loggedP === null) {
            $this->loggedP = $this->logCheckStatement($dbc);
        }
        $args = array(
            $info['tdate'],
            $info['card_no'],
            $info['department'],
            $info['trans_num'],
        );
        if ($this->hasTransID($dbc)) {
            $args[] = $info['trans_id'];
        }
        $loggedR = $dbc->execute($this->loggedP, $args);
        while ($logW = $dbc->fetchRow($loggedR)) {
            if ($logW['stockPurchase'] == $info['total']) {
                return true;
            }
        }

        return false;
    }

    private function hasTransID($dbc)
    {
        $table = $dbc->tableDefinition('stockpurchases');
        return isset($table['trans_id']);
    }

    private function logCheckStatement($dbc)
    {
        $query = '
            SELECT stockPurchase
            FROM stockpurchases
            WHERE tdate=?
                AND card_no=?
                AND dept=?
                AND trans_num=?'; 
        if ($this->hasTransID($dbc)) {
            $query .= ' AND (trans_id=? OR trans_id=0)';
        }

        return $dbc->prepare($query);
    }

    public function run()
    {
        $dbc = FannieDB::get($this->config->get('TRANS_DB'));

        // build department list
        $ret = preg_match_all("/[0-9]+/",$this->config->get('EQUITY_DEPARTMENTS'), $depts);
        $depts = array_pop($depts);
        $dlist = "(";
        $where_args = array();
        foreach ($depts as $d) {
            $dlist .= "?,";
            $where_args[] = $d;
        }
        $dlist = substr($dlist,0,strlen($dlist)-1).")";
        if ($dlist == ')') {
            // no configured departments
            return false;
        }
        
        // lookup AR transactions from past 15 days
        $lookupQ = "SELECT card_no,
                department, total,
                tdate, trans_num, trans_id
                FROM dlog_15
                WHERE department IN $dlist"; 
        $lookupP = $dbc->prepare($lookupQ);
        $lookupR = $dbc->execute($lookupP, $where_args);

        $checkP = $dbc->prepare('SELECT stockPurchase FROM stockpurchases 
                    WHERE tdate=? AND trans_num=? AND card_no=? AND dept=?');
        $addP = $dbc->prepare('INSERT INTO stockpurchases (card_no, stockPurchase, tdate, trans_num, dept)
                            VALUES (?, ?, ?, ?, ?)');
        $model = new StockpurchasesModel($dbc);
        while ($lookupW = $dbc->fetch_row($lookupR)) {
            if ($this->isLogged($dbc, $lookupW)) {
                continue;
            }

            $model->card_no($lookupW['card_no']);
            $model->stockPurchase($lookupW['total']);
            $model->tdate($lookupW['tdate']);
            $model->trans_num($lookupW['trans_num']);
            $model->dept($lookupW['department']);
            $model->trans_id($lookupW['trans_id']);
            if ($model->save() === false) {
                $this->cronMsg('Error adding equity entry '.$lookupW['tdate']. ' '.$lookupW['trans_num'], FannieLogger::ERROR);
            }
        }

        // rebuild ar history sum table
        $dbc->query("TRUNCATE TABLE equity_history_sum");
        $query = "INSERT INTO equity_history_sum
            SELECT card_no, SUM(stockPurchase), MIN(tdate)
            FROM stockpurchases GROUP BY card_no";
        $def = $dbc->tableDefinition('equity_history_sum');
        if (isset($def['mostRecent'])) {
            $query = str_replace('MIN(tdate)', 'MIN(tdate), MAX(tdate)', $query);
        }
        $try = $dbc->query($query);
        if ($try === false) {
            $this->cronMsg('Error rebuilding equity_history_sum table', FannieLogger::ERROR);
        }

        if (isset($def['mostRecent'])) {
            /**
              Lookup transactions with net equity purchase
              of zero. These transactions should not impact
              the first/last equity purchase dates
            */
            $voidedR = $dbc->query('
                SELECT card_no,
                    trans_num
                FROM stockpurchases
                GROUP BY card_no,trans_num
                HAVING SUM(stockPurchase)=0');
            $voids = array();
            while ($row = $dbc->fetchRow($voidedR)) {
                if (!isset($voids[$row['card_no']])) {
                    $voids[$row['card_no']] = array();
                }
                $voids[$row['card_no']][] = $row['trans_num'];
            }

            /**
              For applicable members, lookup min and max
              date values again excluding the net-zero
              transactions. Update date fields for these
              members.
            */
            $upP = $dbc->prepare('
                UPDATE equity_history_sum
                SET startdate=?,
                    mostRecent=?
                WHERE card_no=?');
            $dbc->startTransaction();
            foreach ($voids as $card_no => $transactions) {
                $query = '
                    SELECT MIN(tdate) AS startdate,
                        MAX(tdate) AS mostRecent
                    FROM stockpurchases
                    WHERE card_no=?
                        AND trans_num NOT IN (';
                $args = array($card_no);
                foreach ($transactions as $t) {
                    $query .= '?,';
                    $args[] = $t;
                }
                $query = substr($query, 0, strlen($query)-1) . ')';
                $prep = $dbc->prepare($query);
                $res = $dbc->execute($prep, $args);
                if ($res && $dbc->numRows($res)) {
                    $dates = $dbc->fetchRow($res);
                    $dbc->execute($upP, array(
                        $dates['startdate'],
                        $dates['mostRecent'],
                        $card_no,
                    ));
                }
            }
            $dbc->commitTransaction();
        }

        /**
          Update payment plan accounts based on 
          current payment history 
        */
        $dbc->selectDB($this->config->get('OP_DB'));
        $date = new MemDatesModel($dbc);
        $plan = new EquityPaymentPlansModel($dbc);
        $plans = array();
        foreach ($plan->find() as $p) {
            $plans[$p->equityPaymentPlanID()] = $p;
        }
        $accounts = new EquityPaymentPlanAccountsModel($dbc);
        $balP = $dbc->prepare('
            SELECT payments,
                mostRecent
            FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'equity_history_sum
            WHERE card_no=?');
        $historyP = $dbc->prepare('
            SELECT stockPurchase,
                tdate
            FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'stockpurchases
            WHERE card_no=?
            ORDER BY tdate');
        foreach ($accounts->find() as $account) {
            if (!isset($plans[$account->equityPaymentPlanID()])) {
                // undefined plan
                continue;
            }
            $myplan = $plans[$account->equityPaymentPlanID()];
            $bal = $dbc->getRow($balP, array($account->cardNo()));
            if ($bal['payments'] >= $myplan->finalBalance()) {
                // account is now paid in full
                $account->lastPaymentDate($bal['mostRecent']);
                $account->nextPaymentDate(null);
                $account->nextPaymentAmount(0);
                $account->save();
            } else {
                /**
                  Payment plans are really structured into tiers. For a $20 increment, $100 total
                  plan the tiers are at $20, $40, $60, and $80. I'm not assuming any rigid 
                  enforcement of payment amounts (i.e., someone may make a payment that isn't
                  exactly $20). So after the current tier is established, I go through
                  the whole history to figure out when the tier was reached and track
                  any progress toward tier.
                */
                $payment_number = $this->numberOfPayments($myplan, $bal['payments']);
                $last_threshold_reached = $myplan->initialPayment() + (($payment_number-1)*$myplan->recurringPayment());
                $historyR = $dbc->execute($historyP, array($account->cardNo()));
                list($last_payment, $last_date, $next_payment) = $this->analyzePaymentHistory(
                    $dbc,
                    $historyR,
                    $myplan,
                    $last_threshold_reached
                );
                $account->lastPaymentDate($last_date);
                $account->lastPaymentAmount($last_payment);
                $account->nextPaymentAmount($next_payment);

                // finally, figure out the next payment due date
                // if due dates are all based on the original join date,
                // walk forward through due dates from the beginning
                $basis_date = $last_date;
                if ($myplan->dueDateBasis() == 0) {
                    $date->card_no($account->cardNo());
                    $date->load();
                    $basis_date = $date->start_date();
                    for ($i=1; $i<$payment_number-1; $i++) {
                        $basis_date = $this->getNextPaymentDate($myplan, $basis_date);
                    }
                }
                $account->nextPaymentDate($this->getNextPaymentDate($myplan, $basis_date));

                $account->save();
            }
        }
    }

    /**
      Determine how many recurring payments have been made
      @return [int] next payment
      (e.g., if 4 payments have been made this will return 5)
    */
    private function numberOfPayments($myplan, $balance)
    {
        $payment_number = 1;
        for ($i=$myplan->initialPayment(); $i<=$balance; $i+= $myplan->recurringPayment()) {
            $payment_number++;
        }

        return $payment_number-1;
    }

    /**
      Go through member's payment history in order to locate the payment
      that brought their balance to the last tier. Any further payments will
      decrease the next payment owed
      (e.g., if a member on a $20 installment plan makes a $25 payemnt, their
      next payment owed will only be $15)
    */
    private function analyzePaymentHistory($dbc, $historyR, $myplan, $last_threshold_reached)
    {
        $last_payment = 0;
        $last_date = null;
        $next_payment = $myplan->recurringPayment();
        $sum = 0;
        $reached = false;
        while ($historyW = $dbc->fetchRow($historyR)) {
            $sum += $historyW['stockPurchase'];
            if (!$reached && $sum >= $last_threshold_reached) {
                $last_date = $historyW['tdate'];
                $last_payment = $historyW['stockPurchase'];
                $reached = true;
                $next_payment -= ($last_threshold_reached-$sum);
            } elseif ($reached) {
                $next_payment -= $historyW['stockPurchase'];
            }
        }

        return array($last_payment, $last_date, $next_payment);
    }

    /**
      Calculate when the next payment is due 
      Billing cycle is stored as an integer followed by a letter
      Ex: 2M means a payment every two months
    */
    private function getNextPaymentDate($myplan, $basis_date)
    {
        $cycle = trim($myplan->billingCycle());
        $magnitude = substr($cycle, 0, strlen($cycle)-1);
        $frequency = strtoupper(substr($cycle, -1));
        $ts = strtotime($basis_date);
        switch ($frequency) {
            case 'W':
                $magnitude *= 7;
                // intentional fall through
            case 'D':
                return date('Y-m-d', mktime(0,0,0,date('n',$ts),date('j',$ts)+$magnitude,date('Y',$ts)));
                break;
            case 'M':
                return date('Y-m-d', mktime(0,0,0,date('n',$ts)+$magnitude,date('j',$ts),date('Y',$ts)));
                break;
            case 'Y':
                return date('Y-m-d', mktime(0,0,0,date('n',$ts),date('j',$ts),date('Y',$ts)+$magnitude));
                break;
        }

        return null;
    }
}

