<?php
/**
 * Class represents records from table aff_commission
 * {autogenerated}
 * @property int $commission_id 
 * @property int $aff_id 
 * @property date $date
 * @property double $amount 
 * @property string $record_type commission, void
 * @property int $invoice_id
 * @property int $invoice_payment_id 
 * @property string $receipt_id 
 * @property int $product_id 
 * @property bool $is_first 
 * @property string $payout_detail_id 
 * @property int $tier default: 0
 * @see Am_Table
 */

class AffCommission extends Am_Record 
{
    const COMMISSION = 'commission';
    const VOID = 'void';
    
    /** @var Invoice */
    protected $_invoice;
    /** @var User */
    protected $_aff;
    /** @var InvoicePayment */
    protected $_payment;
    
    public function init()
    {
        parent::init();
        $this->record_type = self::COMMISSION;
    }
    
    public function _setPayment(InvoicePayment $payment=null){ $this->_payment = $payment; return $this; }
    public function _setInvoice(Invoice $invoice){ $this->_invoice = $invoice; return $this; }
    public function _setAff(User $aff){ $this->_aff = $aff; return $this; }
    public function getPayment()
    { 
        if (empty($this->invoice_payment_id)) return null;
        return $this->_payment ? $this->_payment : $this->_payment=$this->getDi()->invoicePaymentTable->load($this->invoice_payment_id); 
    }
    public function getAff()  { return $this->_aff ? $this->_aff : $this->_aff=$this->getDi()->userTable->load($this->aff_id); }
    public function getInvoice()
    {
        if (empty($this->invoice_id)) return null;
        return $this->_invoice ? $this->_invoice : $this->_invoice=$this->getDi()->invoiceTable->load($this->invoice_id); 
    }
    
    function insert($reload = true)
    {
        $ret = parent::insert($reload);
        $this->getDi()->hook->call(Bootstrap_Aff::AFF_COMMISSION_AFTER_INSERT, array(
            'commission' => $this,
            'user'       => $this->getInvoice() ? $this->getInvoice()->getUser() : null,
            'aff'        => $this->getAff(),
            'invoice'    => $this->getInvoice(),
            'payment'    => $this->getPayment(),
        ));
        return $ret;
    }
}

class AffCommissionTable extends Am_Table {
    protected $_key = 'commission_id';
    protected $_table = '?_aff_commission';
    protected $_recordClass = 'AffCommission';

    /**
     * Return query with affiliates linked to sum of their unpaid commissions
     * made up to $toDate
     * @return Am_Query
     */
    function fetchByDate($date, $aff_id=null)
    {
        return $this->selectObjects(
                "SELECT * FROM ?_aff_commission WHERE `date` = ? { AND aff_id=?d}",
                date('Y-m-d', amstrtotime($date)), $aff_id === null ? DBSIMPLE_SKIP : $aff_id); 
    }
    
    /**
     * Return commission stats
     * @param type $startTm
     * @param type $endTm 
     * @return array with keys: count and amount
     */
    function getAffStats($aff_id, $startTm, $endTm)
    {
        return $this->_db->selectRow("
            SELECT COUNT(DISTINCT(invoice_id)) AS `count`, SUM(IF(record_type='commission',amount,-amount)) as `amount`
            FROM ?_aff_commission
            WHERE aff_id=?d AND `date` BETWEEN ? AND ? AND tier = 0
        ", $aff_id, sqlDate($startTm), sqlDate($endTm));
    }
    
    /*
     * Find last records by invoice_id with the same date (for refunds)
     * @return array AffCommission
     */
    function findLastRecordsByInvoiceId($invoice_id)
    {
        return $this->selectObjects("SELECT * FROM $this->_table 
            WHERE 
            invoice_id=?d AND record_type='commission' AND
                `date` = (SELECT MAX(`date`) FROM $this->_table WHERE invoice_id=?d)"
            , $invoice_id, $invoice_id);
    }
    
    /**
     * Generate payout records for records on $date
     * @param date $date today's date
     */
    function runPayout($date)
    {
        $threseholdDate = sqlDate(amstrtotime($date) - 24 * 3600 * $this->getDi()->config->get('aff.payout_delay_days', 30));
        // now select all affiliates to pay with one query
        $q = $this->_db->queryResultOnly("
            SELECT SUM(IF(c.record_type=?,-c.amount,c.amount)) AS _total,
                a.*
            FROM ?_aff_commission c RIGHT JOIN ?_user a ON a.user_id=c.aff_id
            WHERE (c.record_type=? OR c.date<?) AND (c.payout_detail_id IS NULL)
            GROUP BY c.aff_id
            HAVING _total > ? AND a.aff_payout_type > ''
        ", AffCommission::VOID, AffCommission::VOID,
            $threseholdDate,
            (double)$this->getDi()->config->get('aff.payout_min', 0));
        // then do job
        $payouts = array();
        while ($row = $this->_db->fetchRow($q))
        {
            $aff = $this->getDi()->userTable->createRecord($row);
            if (empty($payouts[$aff->aff_payout_type]))
            {
                $payout = $this->getDi()->affPayoutRecord;
                $payout->type = $aff->aff_payout_type;
                $payout->date = sqlDate($date);
                $payout->thresehold_date = $threseholdDate;
                $payout->insert();
                $payouts[$aff->aff_payout_type] = $payout;
            }
            $detail = $payouts[$aff->aff_payout_type]->addDetail($aff->pk(), $row['_total']);
            $this->_db->query("UPDATE ?_aff_commission c
                SET c.payout_detail_id=?d
                WHERE aff_id=?d AND (c.record_type=? OR c.date<?) AND (c.payout_detail_id IS NULL)
                ", $detail->pk(),
                $aff->pk(), AffCommission::VOID, $threseholdDate);
        }
        foreach ($payouts as $payout) $payout->update(); // store totals
        if ($payouts)
        {
            if ($et = Am_Mail_Template::load('aff.new_payouts'))
                $et->setUrl(ROOT_SURL . '/aff/admin-payout')->sendAdmin ();
        }
    }
    
    function getStats($start, $stop)
    {
        return (double)$this->_db->selectCell("SELECT SUM(amount) FROM ?_aff_commission
            WHERE record_type=? AND date BETWEEN ? AND ?", 
            AffCommission::COMMISSION, sqlDate($start), sqlDate($stop));
    }
}