<?php

class _Am_Record_JustForPreload extends Am_Record
{

}

/**
 * Class represents records from table invoice - a subject to bill customer
 * Sample usage:
 * <code>
 * $b = $this->getDi()->invoiceRecord;
 * $b->add(Am_Di::getInstance()->productTable->load(1), 1);
 * $b->add(Am_Di::getInstance()->productTable->load(2), 2);
 * $b->add(Am_Di::getInstance()->productTable->load(3), 3);
 * $b->setUser(Am_Di::getInstance()->userTable->load(1445));
 * $b->setCouponCode('SECOND');
 * $errors = $b->validate();
 * if (!$errors)
 *    $b->calculate();
 * else
 *     echo($errors, 'errors');
 * </code>
 *
 * @method InvoiceTable getTable getTable()
 *
 * {autogenerated}
 * @property int $invoice_id
 * @property int $user_id
 * @property string $paysys_id
 * @property string $currency
 * @property double $first_subtotal
 * @property double $first_discount
 * @property double $first_tax
 * @property double $first_shipping
 * @property double $first_total
 * @property string $first_period
 * @property int $rebill_times
 * @property double $second_subtotal
 * @property double $second_discount
 * @property double $second_tax
 * @property double $second_shipping
 * @property double $second_total
 * @property string $second_period
 * @property double $tax_rate
 * @property int $tax_type
 * @property string $tax_title
 * @property int $status
 * @property int $coupon_id
 * @property int $is_confirmed
 * @property string $public_id
 * @property string $invoice_key
 * @property datetime $tm_added
 * @property datetime $tm_started
 * @property datetime $tm_cancelled
 * @property date $rebill_date
 * @property date $due_date
 * @property string $comment
 * @property double $base_currency_multi
 * @see Am_Table
 * @package Am_Invoice
 */
class Invoice extends Am_Record_WithData
{
    const PENDING = 0; // pending, not processed yet - initial status
    const PAID = 1; // paid and not-recurring
    const RECURRING_ACTIVE=2; // active recurring - there will be rebills, access open
    const RECURRING_CANCELLED=3; // recurring cancelled, access is open until paid
    const RECURRING_FAILED=4; // rebilling failed, access is closed
    const RECURRING_FINISHED=5; // rebilling finished, no access anymore
    const CHARGEBACK=7; // chargeback processed, no access
    const NOT_CONFIRMED = 8;
    const IS_CONFIRMED_CONFIRMED = 1;
    const IS_CONFIRMED_NOT_CONFIRMED = 0;
    const IS_CONFIRMED_WAIT_FOR_USER= -1;
    const UPGRADE_INVOICE_ID = 'upgrade-invoice_id';
    const UPGRADE_INVOICE_ITEM_ID = 'upgrade-invoice_item_id';
    const UPGRADE_CANCEL = 'upgrade-cancel';
    const UPGRADE_REFUND = 'upgrade-refund';
    const ORIG_ID = 'orig_invoice_id';
    const DEFAULT_DUE_PERIOD = 30; //days

    static $statusText = array(
        self::PENDING => 'Pending',
        self::PAID => 'Paid',
        self::RECURRING_ACTIVE => 'Recurring Active',
        self::RECURRING_CANCELLED => 'Recurring Cancelled',
        self::RECURRING_FAILED => 'Recurring Failed',
        self::RECURRING_FINISHED => 'Recurring Finished',
        self::CHARGEBACK => 'Chargeback Received',
        self::NOT_CONFIRMED => 'Not Approved'
    );
    /**
     * Lazy-loaded list of items
     * use ONLY @see $this->getItems() to access
     * @var array of InvoiceItem records
     */
    protected $_items = array();
    /** @var string */
    protected $_couponCode;
    /** @var User lazy-loading (private)  */
    protected $_user;
    /** @var _coupon lazy-loading
     *  @access private */
    protected $_coupon;
    protected $_validateProductRequirements = true;

    const SAVED_TRANSACTION_KEY = '_saved_transaction';

    public function init()
    {
        parent::init();
        if (empty($this->discount_first))
            $this->discount_first = 0.0;
        if (empty($this->discount_second))
            $this->discount_second = 0.0;
        if (empty($this->currency))
            $this->currency = Am_Currency::getDefault();
    }

    function toggleValidateProductRequirements($flag)
    {
        $this->_validateProductRequirements = (bool)$flag;
        return $this;
    }

    /**
     * Approve Invoice. Will reprocess all saved transactions.
     *
     * @return boolean
     */
    public function approve()
    {
        // Make sure that all necessary payment plugins are loaded at this point.
        $this->getDi()->plugins_payment->loadEnabled();
        if ($this->isConfirmed())
            return true;
        $old_status = $this->is_confirmed;
        $this->is_confirmed = self::IS_CONFIRMED_CONFIRMED;

        $this->updateSelectedFields('is_confirmed');
        $saved = array();
        foreach ($this->data()->getAll() as $k => $v) {
            if (strpos($k, self::SAVED_TRANSACTION_KEY) !== false) {
                list(, $time, $payment_id) = explode('-', $k);
                $saved[$time] = array($payment_id, $v);
            }
        }
        ksort($saved);
        foreach ($saved as $time => $v) {
            $this->addAccessPeriod($v[1], $v[0] ? $v[0] : null);
            $this->data()->set(self::SAVED_TRANSACTION_KEY . '-' . $time . '-' . $v[0], null)->update();
        }
        if ($old_status == self::IS_CONFIRMED_NOT_CONFIRMED)
        {
            $this->sendApprovedEmail();
            $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_APPROVE, array('invoice' => $this));
        }
        return true;
    }

    /**
     * Create new empty InvoiceItem, assign invoice_id
     * and return
     * @return InvoiceItem
     */
    function createItem(IProduct $product = null, array $options = array())
    {
        $item = $this->getDi()->invoiceItemRecord;
        $item->invoice_id = empty($this->invoice_id) ? null : $this->invoice_id;
        $item->invoice_public_id = empty($this->public_id) ? null : $this->public_id;
        if ($product) {
            $item->copyProductSettings($product, $options);
        }
        return $item;
    }

    /**
     * Find an item in $this->getItems() by type and ids
     * @return InvoiceItem
     */
    function findItem($type, $id)
    {
        foreach ($this->getItems() as $item)
            if ($item->item_id == $id && $item->item_type == $type)
                return $item;
        return null;
    }

    /**
     * Delete an item
     */
    function deleteItem(InvoiceItem $item)
    {
        foreach ($this->getItems() as $k => $it)
            if ($it === $item) {
                if (!empty($this->_items[$k]->invoice_item_id))
                    $this->_items[$k]->delete();
                unset($this->_items[$k]);
            }
    }

    function addItem(InvoiceItem $item)
    {
        $item->data()->set('orig_first_price', @$item->first_price);
        $item->data()->set('orig_second_price', @$item->second_price);
        $this->_items[] = $item;
        return $this;
    }

    /**
     * @param int $num - number of item in invoice
     * @return InvoiceItem
     */
    function getItem($num)
    {
        $i = 0;
        foreach ($this->getItems() as $item) {
            if ($i++ == $num)
                return $item;
        }
    }

    /**
     * @return InvoiceItem[]
     */
    function getItems()
    {
        if (!empty($this->invoice_id) && !$this->_items)
            $this->_items = $this->getDi()->invoiceItemTable->findByInvoiceId($this->invoice_id, null, null, 'invoice_item_id ASC');
        return (array) $this->_items;
    }

    /**
     * return array of all items Products (it will be loaded if item_type == 'product'
     * @return array Product
     */
    function getProducts()
    {
        $ret = array();
        foreach ($this->getItems() as $item)
            if ($item->item_type == 'product')
                if ($pr = $item->tryLoadProduct())
                    $ret[] = $pr;
        return $ret;
    }

    /**
     * Add a product or calculated charge as a line to invoice
     * @throws Am_Exception_InvalidRequest if items is incompatible
     * @return Invoice provides fluent interface
     */
    function add(IProduct $product, $qty = 1, $options = array())
    {
        $item = $this->findItem($product->getType(), $product->getProductId(), $product->getBillingPlanId());
        if (null == $item) {
            $item = $this->createItem($product, $options);
            $error = $this->isItemCompatible($item);
            if (null != $error)
                throw new Am_Exception_InputError($error);
            $this->addItem($item);
        }
        if (!$item->variable_qty)
            $qty = $product->getQty(); // get default qty
        $item->add($qty);
        return $this;
    }

    /**
     * @return array of instantiated Am_Invoice_Calc_* objects
     */
    function getCalculators()
    {
        class_exists('Am_Invoice_Calc', true);
        $tax_calculators = $this->getDi()->plugins_tax->match($this);
        $ret = array_merge(
                array(
                    new Am_Invoice_Calc_Zero(),
                    new Am_Invoice_Calc_Coupon(),
                    new Am_Invoice_Calc_Discount($this->discount_first, $this->discount_second),
                    new Am_Invoice_Calc_Shipping(),
                ),
                $tax_calculators,
                array(
                    new Am_Invoice_Calc_Total(),
                )
        );
        return $this->getDi()->hook->filter($ret, new Am_Event_InvoiceGetCalculators($this));
    }

    /**
     * Refresh totals according to currently selected
     *   items, _coupon, user and so ons
     * Should be called on a fresh invoice only, because
     * it may break reporting later if called on a paid
     * invoice
     * @return Invoice provides fluent interface
     */
    function calculate()
    {
        $this->first_period = $this->second_period = $this->rebill_times = null;
        foreach ($this->getCalculators() as $calc)
            $calc->calculate($this);
        // now summarize all items to invoice totals
        $priceFields = array(
            'first_subtotal' => null,
            'first_discount' => 'first_discount',
            'first_tax' => 'first_tax',
            'first_shipping' => 'first_shipping',
            'first_total' => 'first_total',
            'second_subtotal' => null,
            'second_discount' => 'second_discount',
            'second_tax' => 'second_tax',
            'second_shipping' => 'second_shipping',
            'second_total' => 'second_total',
        );
        foreach ($priceFields as $k => $kk)
            $this->$k = 0.0;
        foreach ($this->getItems() as $item) {
            $this->first_subtotal += moneyRound(($item->data()->get('first_price_precise') ?: $item->first_price) * $item->qty);
            $this->second_subtotal += moneyRound(($item->data()->get('second_price_precise') ?: $item->second_price) * $item->qty);
            foreach ($priceFields as $k => $kk)
                $this->$k += $kk ? $item->$kk : 0;
        }
        foreach ($priceFields as $k => $kk)
            $this->$k = moneyRound($this->$k);
        /// set periods, it has been checked for compatibility in @see add()
        $mostExpensiveItem = null;
        foreach ($this->getItems() as $item) {
            if (!$mostExpensiveItem || $item->first_price > $mostExpensiveItem->first_price) {
                $mostExpensiveItem = $item;
            }
            $this->currency = $item->currency;
            if (empty($this->first_period) && $item->rebill_times)
                $this->first_period = $item->first_period;
            if (empty($this->second_period))
                $this->second_period = $item->second_period;
            if (empty($this->rebill_times))
                $this->rebill_times = $item->rebill_times;
            $this->rebill_times = max($this->rebill_times, $item->rebill_times);
        }

        // First period is empty, invoice has only one time items,
        // set first period from most expensive item.
        if (empty($this->first_period) && $mostExpensiveItem) {
            $this->first_period = $mostExpensiveItem->first_period;
        }

        if ($this->currency == Am_Currency::getDefault())
            $this->base_currency_multi = 1.0;
        else {
            $this->base_currency_multi = $this->getDi()->currencyExchangeTable->getRate($this->currency,
                    sqlDate(!empty($this->tm_added) ? $this->tm_added : $this->getDi()->sqlDateTime));
            if (!$this->base_currency_multi)
                $this->base_currency_multi = 1;
        }
        $this->getDi()->hook->call(Am_Event::INVOICE_CALCULATE, array('invoice' => $this));

        $this->terms = null;
        if ((count($this->getItems()) == 1) &&
            ($item = $this->getItem(0)) &&
            ($item->item_type == 'product') &&
            ($pr = $item->tryLoadProduct()) &&
            ($bp = $pr->getBillingPlan()) &&
             $bp->terms)  {

            if (((float)$bp->first_price == (float)$this->first_total) &&
                ($bp->first_period == $this->first_period) &&
                ((float)$bp->second_price == (float)$this->second_total) &&
                ($bp->second_period == $this->second_period) &&
                ($bp->rebill_times == $this->rebill_times)
                ) {
                $this->terms = $bp->terms;
            }
        }

        $this->terms = $this->getDi()->hook->filter($this->terms,
            Am_Event::INVOICE_TERMS, array('invoice' => $this));

        return $this;
    }

    /**
     * Validate invoice to make sure it is fully ready for payment processing
     * check for
     * - no items
     * - items are compatible by its terms
     * - user_id set and valid
     * - paysys_id set and valid
     * - @todo currency set and valid
     * - trial1Total, total - calculated
     * - coupon_id is !set || valid
     * - !isPaid
     * @return null|array null if OK, array of translated errors if not
     */
    function validate()
    {
        if (!$this->getItems())
            return array(___('No items selected for purchase'));
        // @todo check compatible items
        if (empty($this->user_id))
            throw new Am_Exception_InternalError("User is not assigned to invoice in " . __METHOD__);
        if (null == $this->getUser())
            throw new Am_Exception_InternalError("Could not load invoice user in " . __METHOD__);
        if ($error = $this->validateCoupon())
            return array($error);
        if ($this->_validateProductRequirements) {
            if ($error = $this->checkProductRequirements())
                return $error;
        }

        $event = new Am_Event(Am_Event::INVOICE_VALIDATE, array('invoice' => $this));
        $this->getDi()->hook->call($event);
        $error = $event->getReturn();

        if(!empty($error))
            return is_array($error) ? $error: array($error);

    }

    /**
     * Check product requirements and return null if OK, or error message
     * @return null|array
     */
    protected function checkProductRequirements()
    {
        $activeProductIds = $expiredProductIds = array();
        if ($this->_user) {
            $activeProductIds = $this->_user->getActiveProductIds();
            $expiredProductIds = $this->_user->getExpiredProductIds();
        }
        $error = $this->getDi()->productTable->checkRequirements($this->getProducts(), $activeProductIds, $expiredProductIds);
        return $error ? $error : null;
    }

    protected function _autoChoosePaysystemIfProductPaysystem()
    {
        if (!$this->getDi()->config->get('product_paysystem')) return null;

        $productPs = array();
        foreach ($this->getProducts() as $pr) {
            $bp = $pr->getBillingPlan();
            $ids[] = $bp->pk();
            if ($bp->paysys_id)
                $productPs[] = explode(',', $bp->paysys_id);
        }
        if (!$productPs) return null;

        if (count($productPs) > 1)
            $intersect_paysys_id = call_user_func_array('array_intersect', $productPs);
        else
            list($intersect_paysys_id) = $productPs;

        foreach ($intersect_paysys_id as $paysys_id) {
            $ps = $this->getDi()->paysystemList->get($paysys_id);
            if (!$ps)
                continue;
            $plugin = $this->getDi()->plugins_payment->get($paysys_id);
            if (!$plugin || $err = $plugin->isNotAcceptableForInvoice($this))
                continue;
            return $paysys_id;
        }
        throw new Am_Exception_InputError("Could not find acceptable payment processor [none selected] for this combination of products: " . join(',', $ids));
    }

    public function setDiscount($first, $second = 0)
    {
        $this->discount_first = $first;
        $this->discount_second = $second;
    }

    /**
     * Validates and sets passed paysysy_id
     * @see $this->paysys_id
     * @param string $paysys_id
     */
    public function setPaysystem($paysys_id, $requirepublic = true)
    {
        $this->paysys_id = null;
        if ($this->isZero() && !empty($this->_items)) {
            $this->paysys_id = 'free';
            return $this->paysys_id;
        }

        if ($id = $this->_autoChoosePaysystemIfProductPaysystem()) {
            $paysys_id = $id;
            $requirepublic = false;
        }

        if (!$paysys_id || (!$this->getDi()->paysystemList->isPublic($paysys_id) && $requirepublic))
            throw new Am_Exception_InputError(___('Please select payment system for payment'));
        if (!$plugin = $this->getDi()->plugins_payment->get($paysys_id))
            throw new Am_Exception_InternalError('Could not load paysystem ' . htmlentities($paysys_id));
        if ($err = (array) $plugin->isNotAcceptableForInvoice($this))
            throw new Am_Exception_InputError(___('Sorry, it is impossible to use this payment method for this order. Please select another payment method') . ' : ' . $err[0]);
        $this->paysys_id = $paysys_id;
        return $this->paysys_id;
    }

    /**
     * Return user record (by user_id) or null
     * caches result in $this->_user
     * @return User|null
     */
    function getUser()
    {
        if (empty($this->user_id))
            return $this->_user = null;
        if (empty($this->_user) || $this->_user->user_id != $this->user_id) {
            $this->_user = $this->getDi()->userTable->load($this->user_id);
        }
        return $this->_user;
    }

    function setUser(User $user)
    {
        $this->_user = $user;
        $this->user_id = $user->user_id;
    }

    /**
     * Return _coupon record (by coupon_id) or a new empty _coupon object
     * caches result in $this->_coupon
     * @return _coupon|null
     */
    function getCoupon()
    {
        if (!empty($this->coupon_id))
            if (!$this->_coupon || ($this->_coupon->coupon_id != $this->coupon_id))
                $this->_coupon = $this->getDi()->couponTable->load($this->coupon_id);
        return $this->_coupon;
    }

    /**
     * Set _coupon and check if that is acceptable
     * @param Coupon $_coupon
     * @return Invoice provides fluent interface
     */
    function setCoupon(Coupon $_coupon)
    {
        $this->_couponCode = null;
        $this->coupon_id = $_coupon->coupon_id;
        $this->coupon_code = $_coupon->code;
        $this->_coupon = $_coupon;
    }

    /**
     * Set _coupon code
     * You also need to call validateCoupon() to get it loaded and checked
     */
    function setCouponCode($code)
    {
        $this->_coupon = $this->coupon_id = $this->coupon_code = null;
        $this->_couponCode = $code;
    }

    /**
     * Validate currently set coupon code, return error message or null of OK
     * @see setCouponCode
     */
    function validateCoupon()
    {
        if ($this->_couponCode != '') {
            $this->_coupon = $this->getDi()->couponTable->findFirstByCode($this->_couponCode);
            if (!$this->_coupon)
                return ___('No coupons found with such coupon code');
            $this->coupon_id = $this->_coupon->coupon_id;
            $this->coupon_code = $this->_coupon->code;
        }
        if (!empty($this->coupon_id)) {
            /* @var $variable Coupon */
            $coupon = $this->getCoupon();
            if ($error = $coupon->validate(@$this->user_id))
                return $error;

            $activeProductIds = $expiredProductIds = array();
            if ($this->_user) {
                $activeProductIds = $this->_user->getActiveProductIds();
                $expiredProductIds = $this->_user->getExpiredProductIds();
            }
            if ($error = $coupon->checkRequirements($this->getProducts(), $activeProductIds, $expiredProductIds))
                return current($error);
        }
    }

    /**
     * @return Am_Currency
     */
    function getCurrency($value = null)
    {
        $c = new Am_Currency($this->currency);
        if ($value !== null)
            $c->setValue($value);
        return $c;
    }

    /**
     * Return flag necessary for _coupon discount calculation
     * @todo actual calcultations in Invoice::isFirstPayment
     * @return boolean
     */
    public function isFirstPayment()
    {
        return true;
    }

    protected function _getItemCompatibleError($reasonSubstring, InvoiceItem $item, InvoiceItem $existingItem)
    {
//        return sprintf('invoice_recurring_terms_incompatible_' . $reasonSubstring, $item->item_title, $existingItem->item_title);
        return sprintf(___('Product %s is incompatible with product %s. Reason: %s'),
            $existingItem->item_title, $item->item_title, $reasonSubstring);
    }

    /**
     * This checks new item for compatibility with already added products
     * in the invoice items. If settings of recurring billing is incompatible,
     * - product is not compared to itself
     * - if product has rebill_times = 0, it is compatible
     * - if product has rebill_times = 1, firstPeriod must be compatible (equal to)
     *     with all other such products in basket
     * - if product has rebill_times > 1, secondPeriod must be compatible (equal to)
     *     with all other such products in basket
     * @return null|string translated error message
     */
    public function isItemCompatible(InvoiceItem $item, $doNotCheckItems = array())
    {
        if (!$this->getItems()) return;

        foreach ($this->getItems() as $existingItem) {
            if (in_array($existingItem, $doNotCheckItems))
                continue;
            if ($item === $existingItem) {
                continue;
            }
            if (
                (floatval($existingItem->first_price) || floatval($existingItem->second_price)) &&
                (floatval($item->first_price) || floatval($item->second_price)) &&
                $existingItem->currency != $item->currency) {
                return $this->_getItemCompatibleError(___('different currency products'), $item, $existingItem);
            }
            if (0 == $existingItem->rebill_times || 0 == $item->rebill_times) {
                continue;
            }
            if ($existingItem->first_period != $item->first_period) {
                return $this->_getItemCompatibleError(___('different the first period subscriptions'), $item, $existingItem);
            }
            if ($existingItem->rebill_times != $item->rebill_times) {
                return $this->_getItemCompatibleError(___('different rebill times subscriptions'), $item, $existingItem);
            }
            if ($existingItem->rebill_times > 1 &&
                $existingItem->second_period != $item->second_period) {
//                    return $this->_getItemCompatibleError('SECONDPERIOD', $item, $existingItem);
                    return $this->_getItemCompatibleError(___('different the second period subscriptions'), $item, $existingItem);
            }
        }
    }

    function isProductCompatible(IProduct $product)
    {
        $newItem = $this->createItem($product);
        return $this->isItemCompatible($newItem);
    }

    public function getLogin()
    {
        return $this->getUser()->login;
    }

    public function getUserId()
    {
        return (int) $this->user_id;
    }

    public function getName()
    {
        return $this->getUser()->getName();
    }

    public function getFirstName()
    {
        return $this->getUser()->name_f;
    }

    public function getLastName()
    {
        return $this->getUser()->name_l;
    }

    public function getEmail()
    {
        return $this->getUser()->email;
    }

    public function getStreet()
    {
        return trim($this->getStreet1() . ' ' . $this->getStreet2());
    }

    public function getStreet1()
    {
        return $this->getUser()->street;
    }

    public function getStreet2()
    {
        return $this->getUser()->street2;
    }

    public function getCity()
    {
        return $this->getUser()->city;
    }

    public function getState()
    {
        return $this->getUser()->state;
    }

    public function getCountry()
    {
        return $this->getUser()->country;
    }

    public function getZip()
    {
        return $this->getUser()->zip;
    }

    public function getPhone()
    {
        return $this->getUser()->phone;
    }

    public function getShippingStreet()
    {
        return $this->getUser()->street;
    }

    public function getShippingCity()
    {
        return $this->getUser()->city;
    }

    public function getShippingState()
    {
        return $this->getUser()->state;
    }

    public function getShippingCountry()
    {
        return $this->getUser()->country;
    }

    public function getShippingZip()
    {
        return $this->getUser()->zip;
    }

    public function getShippingPhone()
    {
        return $this->getUser()->phone;
    }

    /**
     * Return one-line description of products in the basket
     * to be passed to payment system
     * @return string
     */
    function getLineDescription()
    {
        $items = $this->getItems();
        if (1 == count($items))
            return current($items)->item_title;
        else
            return $this->getDi()->config->get('multi_title', 'Invoice Items (by invoice#' . $this->_getPublicId() . ')');
    }

    /**
     * Return invoice id with not-numeric random string added
     * this avoids "duplicate invoice" error in paysystems like
     * Paypal, and can be easily stipped by running
     * it is proved that in same session it will return the same id for same invoice
     * @example $cleanInvoiceId = intval($invoice->getRandomizedId());
     * @param string $param if set to date it will be not be unique within current date
     * @return string $this->invoice_id . 'randomstring';
     */
    function getRandomizedId($param = null)
    {
        if ($param == 'date')
            return $this->_getPublicId() . '-' . date('Ymd');
        if ($param == 'site')
            return $this->_getPublicId() . '-' . substr(md5(ROOT_URL), 0, 6);
        return $this->_getPublicId();
    }

    /**
     * Calculate planned rebill date for $n rebill
     * @return string date
     */
    function calculateRebillDate($n)
    {
        if ($n > $this->rebill_times)
            throw new Am_Exception_InternalError(__METHOD__ . " call error: n[$n] > rebill_times[$this->rebill_times]");
        $date = $this->tm_started;
        if (($date == '0000-00-00 00:00:00') || !$date)
            $date = $this->getDi()->sqlDate;
        else
            $date = preg_replace('/ .+$/', '', $date);
        if ($n == 0)
            return $date;
        $p = new Am_Period($this->first_period);
        $date = $p->addTo($date);
        if ($n == 1)
            return $date;
        $p = new Am_Period($this->second_period);
        for ($i = 1; $i < $n; $i++)
            $date = $p->addTo($date);
        return $date;
    }

    protected function _getInvoiceKey()
    {
        if (empty($this->invoice_key))
            $this->invoice_key = $this->getDi()->security->randomString(16);
        return $this->invoice_key;
    }

    protected function _getPublicId()
    {
        if (empty($this->public_id))
            $this->public_id = $this->getDi()->security->randomString(5, 'QWERTYUASDFGHJKLZXCVBNM1234567890');
        return $this->public_id;
    }

    /**
     * Return unique id for invoice. With the same prefix, returned value
     * is always the same for the same invoice
     */
    function getUniqId($prefix)
    {
        return substr(sha1($prefix . $this->_getInvoiceKey()), 0, 16);
    }

    /**
     * Return string in form 1123-LKj3lrkjg3
     * @link InvoiceTable->findBySecureId
     */
    function getSecureId($prefix)
    {
        return $this->public_id . "-" . $this->getUniqId($prefix);
    }

    function hasShipping()
    {
        foreach ($this->getItems() as $item)
            if ($item->is_tangible)
                return true;
        return false;
    }

    public function isZero()
    {
        return (@$this->first_total == 0) && (@$this->second_total == 0);
    }

    /**
     * @return true if this invoice is acceptable for "fixed price" plugins
     * It means - one product, no taxes, no discounts, no shipping
     */
    public function isFixedPrice()
    {
        return count($this->getItems()) == 1 &&
        $this->first_subtotal == $this->first_total &&
        $this->second_subtotal == $this->second_total;
    }

    function __toString()
    {
        $ret = $this->toArray();
        $ret['_items'] = array();
        foreach ($this->_items as $item)
            $ret['_items'][] = $item->toArray();
        return print_r($ret, true);
    }

    function render($indent = "", InvoicePayment $payment = null)
    {
        $prefix = (!is_null($payment) && !$payment->isFirst()) ? 'second' : 'first';
        $tm_added = is_null($payment) ? $this->tm_added : $payment->dattm;

        $taxes = array();
        foreach ($this->getItems() as $item) {
            if ($item->tax_rate && $item->{$prefix.'_tax'}) {
                if (!isset($taxes[$item->tax_rate])) {
                    $taxes[$item->tax_rate] = 0;
                }
                $taxes[$item->tax_rate] += $item->{$prefix.'_tax'};
            }
        }
        if (!$taxes) {
            $taxes[$this->tax_rate] = $this->{$prefix.'_tax'};
        }

        $newline = "\r\n";

        $price_width = max(mb_strlen(Am_Currency::render($this->{$prefix . '_total'}, $this->currency)), 8);

        $column_padding = 1;
        $column_title_max = 60;
        $column_title_min = 20;
        $column_qty = 4 + $price_width;
        $column_num = 3;
        $column_amount = $price_width;
        $space = str_repeat(' ', $column_padding);

        $max_length = 0;
        foreach ($this->getItems() as $item) {
            $max_length = max(mb_strlen(___($item->item_title)), $max_length);
        }

        $column_title = max(min($max_length, $column_title_max), $column_title_min);
        $row_width = $column_num + $column_padding +
                     $column_title + $column_padding +
                     $column_qty + $column_padding +
                     $column_amount + $column_padding;

        $column_total = $column_title +
                        $column_qty + $column_padding;
        $total_space = str_repeat(' ', $column_padding + $column_num + $column_padding);

        $border = $indent . str_repeat('-', $row_width) . "$newline";

        $out = $indent . ___("Invoice") . ' #' . $this->public_id . " / " . amDate($tm_added) . "$newline";
        $out .= $border;
        $num = 1;
        foreach ($this->getItems() as $item) {
            $item_title = ___($item->item_title);
            $options = array();
            foreach($item->getOptions() as $optKey => $opt) {
                $options[] = sprintf('%s: %s',
                    strip_tags($opt['optionLabel']),
                    implode(', ', array_map('strip_tags', (array)$opt['valueLabel'])));
            }
            if ($options) {
                $item_title .= sprintf(' (%s)', implode(', ', $options));
            }
            $title = explode("\n", $this->wordWrap($item_title, $column_title, "\n", true));
            $out .= $indent . sprintf("{$space}%{$column_num}s{$space}%-{$column_title}s{$space}%{$column_qty}s{$space}%{$price_width}s$newline",
                $num . '.', $title[0], $item->qty . 'x' . Am_Currency::render($item->{$prefix . '_price'}, $this->currency), Am_Currency::render($item->{$prefix . '_total'}, $this->currency));
            for ($i=1; $i<count($title); $i++)
                $out .= $indent . sprintf("{$space}%{$column_num}s{$space}%-{$column_title}s$newline", ' ', $title[$i]);
            $num++;
        }
        $out .= $border;
        if ($this->{$prefix . '_subtotal'} != $this->{$prefix . '_total'})
            $out .= $indent . sprintf("{$total_space}%-{$column_total}s{$space}%{$price_width}s$newline", ___('Subtotal'), Am_Currency::render($this->{$prefix . '_subtotal'}, $this->currency));
        if ($this->{$prefix . '_discount'} > 0)
            $out .= $indent . sprintf("{$total_space}%-{$column_total}s{$space}%{$price_width}s$newline", ___('Discount'), Am_Currency::render($this->{$prefix . '_discount'}, $this->currency));
        if ($this->{$prefix . '_shipping'} > 0)
            $out .= $indent . sprintf("{$total_space}%-{$column_total}s{$space}%{$price_width}s$newline", ___('Shipping'), Am_Currency::render($this->{$prefix . '_shipping'}, $this->currency));
        if ($this->{$prefix . '_tax'} > 0) {
            foreach ($taxes as $rate => $tax) {
                $out .= $indent . sprintf("{$total_space}%-{$column_total}s{$space}%{$price_width}s$newline", ___('Tax') . sprintf(' (%d%s)', $rate, '%'), Am_Currency::render($tax, $this->currency));
            }
        }
        $out .= $indent . sprintf("{$total_space}%-{$column_total}s{$space}%{$price_width}s$newline", ___('Total'), Am_Currency::render($this->{$prefix . '_total'}, $this->currency));
        $out .= $border;
        if ($this->rebill_times) {
            $terms = explode("\n", $this->wordWrap(___($this->getTerms()), $row_width, "\n", true));
            foreach ($terms as $term_part)
                $out .= $indent . $term_part . $newline;
            $out .= $border;
        }
        return $out;
    }

    protected function wordWrap($str, $width = 74, $break = "\n", $cut = false)
    {
       return preg_replace('#([\S\s]{'. $width .'}'. ($cut ? '' : '\s') .')#u', '$1'. $break , $str);
    }

    function renderHtml(InvoicePayment $payment = null)
    {
        $v = $this->getDi()->view;
        $v->prefix = (!is_null($payment) && !$payment->isFirst()) ? 'second_' : 'first_';
        $v->invoice = $this;
        return $v->render('mail/_invoice.phtml');
    }

    function update()
    {
        $ret = parent::update();
        $ids = array();
        foreach ($this->_items as $item)
            $item->set('invoice_id', $this->invoice_id)
                ->set('invoice_public_id', $this->public_id)->save();
        return $ret;
    }

    function isConfirmed()
    {
        return!empty($this->is_confirmed) && ($this->is_confirmed > 0);
    }

    protected function getManuallyApproveInvoiceProducts()
    {
        $ret = array();
        $categoryProduct = $this->getDi()->productCategoryTable->getCategoryProducts();
        foreach($this->getDi()->config->get('manually_approve_invoice_products') as $id) {
            if (preg_match('/c([0-9]*)/i', $id, $m)) {
                $ret = array_merge($ret, $categoryProduct[$m[1]]);
            } else {
                $ret[] = $id;
            }
        }
        return $ret;
    }

    function insert($reload = true)
    {
        // Set is confirmed value if it wasn't set yet;
        if (!isset($this->is_confirmed)) {
            // If user is not approved, invoice shouldn't be approved too.
            if ($this->getUser() && !$this->getUser()->isApproved())
                $this->is_confirmed = self::IS_CONFIRMED_WAIT_FOR_USER;

            if ($this->getDi()->config->get('manually_approve_invoice')) {
                // Now check is manually_approve_invoice_products is set.
                if ($products = $this->getManuallyApproveInvoiceProducts()) {
                    foreach ($this->getProducts() as $p)
                        if (in_array($p->product_id, $products))
                            $this->is_confirmed = self::IS_CONFIRMED_NOT_CONFIRMED;
                }
                else
                    $this->is_confirmed = self::IS_CONFIRMED_NOT_CONFIRMED;
            }

            // If above checks, didn't change is_confirmed status, then invoice is confirmed;
            if (!isset($this->is_confirmed))
                $this->is_confirmed = self::IS_CONFIRMED_CONFIRMED;
        }
        if (empty($this->remote_addr)) {
            $this->remote_addr = htmlentities(@$_SERVER['REMOTE_ADDR']);
        }

        $this->getDi()->hook->call(Am_Event::INVOICE_BEFORE_INSERT, array('invoice' => $this));

        if (empty($this->tm_added))
            $this->tm_added = sqlTime('now');
        $this->_getInvoiceKey();

        $maxAttempts = 20;
        for ($i = 0; $i <= $maxAttempts; $i++)
            try {
                $this->_getPublicId();
                $ret = parent::insert($reload = true);
                break;
            } catch (Am_Exception_Db_NotUnique $e) {
                if ($i >= $maxAttempts)
                    throw $e;
                $this->public_id = null;
            }
        foreach ($this->_items as $item)
            $item->set('invoice_id', $this->invoice_id)
                ->set('invoice_public_id', $this->public_id)->insert();

        $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_INSERT, array('invoice' => $this));

        return $ret;
    }

    /**
     * Dangerous! Deletes all related payments from 'payments' table
     * @see InvoicePayment
     * @see InvoiceItem
     */
    function delete()
    {
        $this->getDi()->hook->call(Am_Event::INVOICE_BEFORE_DELETE, array('invoice' => $this));
        foreach ($this->getItems() as $item) {
            $item->delete();
        }
        // $this->deleteFromRelatedTable('?_invoice_log'); // not good idea to delete
        $this->deleteFromRelatedTable('?_invoice_payment');
        $this->deleteFromRelatedTable('?_invoice_refund');
        $this->deleteFromRelatedTable('?_invoice_item_option');
        foreach ($this->getAccessRecords() as $access) {
            $access->delete();
        }
        parent::delete();
        $this->getUser()->checkSubscriptions(true);
        $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_DELETE, array('invoice' => $this));
        return $this;
    }

    function sendApprovedEmail()
    {
        if ($et = Am_Mail_Template::load('invoice_approved_user', $this->getUser()->lang)) {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send($this->getUser());
        }
    }

    function sendNotApprovedEmail()
    {
        if ($et = Am_Mail_Template::load('invoice_approval_wait_user', $this->getUser()->lang)) {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send($this->getUser());
        }
        if ($et = Am_Mail_Template::load('invoice_approval_wait_admin', $this->getUser()->lang)) {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send(Am_Mail_Template::TO_ADMIN);
        }
    }

    protected function saveTransaction(Am_Paysystem_Transaction_Interface $transaction, $invoicePaymentId = null)
    {
        $saved = new Am_Paysystem_Transaction_Saved($transaction);
        $this->data()->set(self::SAVED_TRANSACTION_KEY . '-' . time() . '-' . intval($invoicePaymentId), $saved)->update();
    }

    public function cancelUpgradedInvoice($invoice_id)
    {
        $parentInvoice = $this->getTable()->load($invoice_id, false);
        if (!$parentInvoice)
            return;

        $parentInvoice->data()->set(self::UPGRADE_CANCEL, 1);
        $parentInvoice->save();
        // stop access records for invoice_item_id
        $item = $this->getDi()->invoiceItemTable->load($this->data()->get(Invoice::UPGRADE_INVOICE_ITEM_ID), false);
        if ($item) {
            $activeAccess = $this->getDi()->accessTable->selectObjects("SELECT *
                FROM ?_access
                WHERE invoice_id=?d AND product_id=?d AND expire_date > ?", $invoice_id, $item->item_id, $this->getDi()->sqlDate);
            foreach ($activeAccess as $access) {
                $access->expire_date = sqlDate($this->getDi()->sqlDate . ' - 1 day');
                $access->update();
            }
        }
        $ps = $this->getPaysystem();
        if (!$ps)
            return;
        // cancel subscription
        if ($parentInvoice->getStatus() == self::RECURRING_ACTIVE) {
            $result = new Am_Paysystem_Result();
            try {
                $ps->cancelAction($parentInvoice, 'cancel', $result);
                if ($result->isSuccess())
                    return;
            } catch (Am_Exception_NotImplemented $e) {
                // nop
            } catch (Exception $e) {
                $this->getDi()->errorLogTable->logException($e);
                // catch all errors
            }
            // email admin to cancel invoice
            if ($et = Am_Mail_Template::load('admin_cancel_upgraded_invoice')) {
                $et->setUser($this->getUser());
                $et->setInvoice($parentInvoice);
                $subscr_id = $this->getDi()->db->selectCell("
                SELECT receipt_id
                FROM ?_invoice_payment
                WHERE invoice_id=? AND receipt_id > ''
                ORDER BY dattm DESC
                ", $invoice_id);
                $et->setArray(array('subscr_id' => $subscr_id));
                $et->sendAdmin();
            }
        }
        // try to refund over-paid amount
        if ($refundAmount = $this->data()->get(self::UPGRADE_REFUND)) {
            $payment = null;
            foreach ($parentInvoice->getPaymentRecords() as $payment);
            if ($payment) {
                $result = new Am_Paysystem_Result();
                try {
                    $ps->processRefund($payment, $result, $refundAmount);
                } catch (Am_Exception $e) {
                    $result->setFailed('Refund error:' . $e->getMessage());
                }
                if (!$result->isSuccess())
                    if ($et = Am_Mail_Template::load('admin_refund_upgraded_invoice')) {
                        $et->setUser($this->getUser());
                        $et->setInvoice($parentInvoice);
                        $et->setArray(array(
                            'subscr_id' => $payment->receipt_id,
                            'refund_amount' => $refundAmount));
                        $et->sendAdmin();
                    }
            }
        }
    }

    /**
     * If given $transaction was not handled yet (@see Am_Paysystem_Transaction_Interface::getUniqId)
     * we will handle it, and add access records to amember_invoice_access table,
     *
     * @param Am_Paysystem_Transaction_Interface $transaction
     */
    public function addAccessPeriod(Am_Paysystem_Transaction_Interface $transaction, $invoicePaymentId = null)
    {
        if (!$this->isConfirmed()) {
            // If invoice is not confirmed, we just need to store transaction data somewhere and leave.
            $this->saveTransaction($transaction, $invoicePaymentId);
            if ($this->is_confirmed == self::IS_CONFIRMED_NOT_CONFIRMED)
                $this->sendNotApprovedEmail();
            $this->updateStatus();
            return;
        }
        $records = $this->getAccessRecords();
        $isFirstPeriod = !$records;
        $transactionDate = $transaction->getTime()->format('Y-m-d');
        $count = array();

        if ($isFirstPeriod) {
            foreach ($this->getItems() as $item) {
                if ($item->item_type != 'product') {
                    continue; // if that is not a product then no access
                }
                if ($item->rebill_times || $this->data()->get('upgrade-invoice_id')) {
                    $start = $transactionDate; // no games with recurring billing dates
                } else { // for not-recurring we can be flexible
                    $ppr = $item->tryLoadProduct();
                    if ($ppr)
                        $start = $ppr->calculateStartDate($transactionDate, $this);
                    else
                        $start = $transactionDate;
                }
                $start = $this->getDi()->hook->filter($start, new Am_Event_CalculateStartDate(null, array(
                        'invoice' => $this,
                        'item' => $item,
                        'isFirst' => $isFirstPeriod,
                    )));
                $item->addAccessPeriod($isFirstPeriod, $this, $transaction, $start, $invoicePaymentId);
            }
        }
        else {
            $today = clone $transaction->getTime();
            $lastBegin = $lastExpire = array();
            foreach ($records as $accessRecord) {
                /* @var $accessRecord Access */
                if ($accessRecord->isLifetime())
                    $accessRecord->updateQuick('expire_date', $today->format('Y-m-d'));
                $pid = $accessRecord->product_id;
                if (empty($lastBegin[$pid]))
                {
                    $lastBegin[$pid] = null;
                    $lastExpire[$pid] = null;
                    $count[$pid] = null;
                }
                $lastBegin[$pid]  = max($lastBegin[$pid], $accessRecord->begin_date);
                $lastExpire[$pid] = max($lastExpire[$pid], $accessRecord->expire_date);
                $count[$pid]      = $count[$pid] + 1;
            }
            foreach ($this->getItems() as $item) {
                if ($item->item_type != 'product') {
                    continue; // if that is not a product then no access
                }
                if (!$item->rebill_times || ($count[$item->item_id] > $item->rebill_times))
                    continue; // this item rebills is over

                $start = max($lastExpire[$item->item_id], $today->format('Y-m-d'));
                $start = $this->getDi()->hook->filter($start, new Am_Event_CalculateStartDate(null, array(
                        'invoice' => $this,
                        'item' => $item,
                        'isFirst' => $isFirstPeriod,
                    )));

                if ($start == Am_Period::MAX_SQL_DATE || $start == Am_Period::RECURRING_SQL_DATE) {
                    $start = $transactionDate;
                    $yesterday = date('Y-m-d', strtotime($start) - 26 * 3600);
                    // set date to yesterday for past access record to this item
                    $this->getDi()->accessTable->setDateForRecurring($item, $yesterday);
                }
                $item->addAccessPeriod($isFirstPeriod, $this, $transaction, $start, $invoicePaymentId);
            }
        }
        if ($isFirstPeriod) {
            $this->updateQuick('tm_started', $transaction->getTime()->format('Y-m-d H:i:s'));
            if ($this->coupon_id) {
                $coupon = $this->getDi()->couponTable->load($this->coupon_id, false);
                if ($coupon && (($this->first_discount > 0) || ($this->second_discount > 0) || ($coupon->getBatch()->discount == 0)))
                    $coupon->setUsed();
            }

            //  By default rebill date will be updated when payment is added.
            //  We need to update it here in case of free trial subscription.
            if (floatval($this->first_total) == 0)
                $this->addToRebillDate(true);

            $this->updateStatus();
            $this->getUser()->checkSubscriptions(true);

            if ($parent_invoice_id = $this->data()->get(self::UPGRADE_INVOICE_ID))
                $this->cancelUpgradedInvoice($parent_invoice_id);

            if($orig_recurring_invoice_id = $this->data()->get(self::ORIG_ID))
            {
                $origInvoice = $this->getDi()->invoiceTable->load($orig_recurring_invoice_id);
                $origInvoice->setStatus(Invoice::RECURRING_FINISHED);
            }


            $this->getDi()->hook->call(new Am_Event_InvoiceStarted(null, array(
                    'user' => $this->getUser(),
                    'invoice' => $this,
                    'transaction' => $transaction,
                    'payment' => $invoicePaymentId ?
                        $this->getDi()->invoicePaymentTable->load($invoicePaymentId, false) :
                        null
                )));
        } else {
            $this->updateStatus();
            $this->getUser()->checkSubscriptions(true);
        }
    }

    /**
     * Add small manual access period for example during cc_rebill failure
     * @param date $start
     * @param date $expire
     */
    public function extendAccessPeriod($newExpire)
    {
        // get last expiration date
        $expire = $this->getAccessExpire();
        // we will be updating only records with this expiration date
        // because all other records are already expired and we will
        // not touch it
        $count = 0;
        foreach ($this->getAccessRecords() as $accessRecord) {
            if ($accessRecord->expire_date != $expire)
                continue;
            $accessRecord->setDisableHooks(true);
            $accessRecord->expire_date = $newExpire;
            $accessRecord->update();
            $accessRecord->setDisableHooks(false);
            $count++;
        }
        if ($count)
            $this->getDi()->userTable->load($this->user_id)->checkSubscriptions(true);
    }

    public function addPayment(Am_Paysystem_Transaction_Interface $transaction)
    {
        $p = $this->addPaymentWithoutAccessPeriod($transaction);
        $this->addAccessPeriod($transaction, $p->invoice_payment_id);
        $this->getDi()->hook->call(new Am_Event_PaymentWithAccessAfterInsert(null,
                array('payment' => $p,
                    'invoice' => $p->getInvoice(),
                    'user' => $p->getInvoice()->getUser())));
        return $p;
    }

    /** @return Invoice_Payment */
    public function addPaymentWithoutAccessPeriod(Am_Paysystem_Transaction_Interface $transaction)
    {
        $c = $this->getPaymentsCount();
        if ($c >= $this->getExpectedPaymentsCount()) {
            $rt = (int) $this->rebill_times;
            if ($this->rebill_times)
                throw new Am_Exception_Paysystem("Existing payments count [$c] exceeds number of allowed rebills [$rt]+1, could not add new payment");
            else { // if that is not a recurring transaction, it is already handled for sure
                $paysys_id = $transaction->getPaysysId();
                $transaction_id = $transaction->getUniqId();
                throw new Am_Exception_Paysystem_TransactionAlreadyHandled("Transaction {$paysys_id}-{$transaction_id} is already handled (1)");
            }
        }
        $p = $this->getDi()->invoicePaymentRecord;
        $p->setFromTransaction($this, $transaction);
        $p->_setInvoice($this); // caching
        try {
            $p->insert();
        } catch (Am_Exception_Db_NotUnique $e) {
            if ($e->getTable() == '?_invoice_payment')
                throw new Am_Exception_Paysystem_TransactionAlreadyHandled("Transaction {$p->paysys_id}-{$p->transaction_id} is already handled (2)");
            else
                throw $e;
        }

        $records = $this->getAccessRecords();
        $isFirstPeriod = !$records;

        $this->addToRebillDate($isFirstPeriod);

        $this->updateStatus();
        return $p;
    }

    /**
     * @access protected
     */
    function updateRebillDate()
    {
        throw new Am_Exception_InternalError('updateRebilldate is deprecated please use addToRebillDate or recalculateRebillDate instead');
    }

    /**
     * Calculate rebill date depends on user's initial payment's date.
     *  If result is in the past, try to calculate it depends on user's last payment;
     *  If result is in the past again,
     */
    function recalculateRebillDate()
    {
        if (is_null($this->tm_started) || in_array($this->status, array(self::RECURRING_FAILED, self::RECURRING_FINISHED, self::RECURRING_CANCELLED))) {
            $this->updateQuick('rebill_date', null);
            return;
        }
        $date = null;
        $c = $this->getPaymentsCount();
        if ($this->first_total <= 0)
            $c++; // first period is "fake" because it was free trial
            //if ($c < $this->getExpectedPaymentsCount()) // not yet done with rebills
        if ($this->rebill_times > ($c - 1)) { // not yet done with rebills
            // we count starting from first payment date, we rely on tm_started field here
            if ($this->first_total <= 0)
                list($date, ) = explode(' ', $this->tm_started);
            else
                $date = $this->getAdapter()
                        ->selectCell("SELECT MIN(dattm) FROM ?_invoice_payment WHERE invoice_id=?d", $this->invoice_id);
            $date = date('Y-m-d', strtotime($date));

            $period1 = new Am_Period($this->first_period);
            $date = $period1->addTo($date);
            $period2 = new Am_Period($this->second_period);
            for ($i = 1; $i < $c; $i++) { // we skip first payment here, already added above
                $date = $period2->addTo($date);
            }
            // If date is in the past, something is wrong here. Now we try to calculate rebill date from user's last payment.
            if ($date < $this->getDi()->dateTime->format('Y-m-d')) {

                $last_payment_date = $this->getAdapter()
                        ->selectCell("SELECT MAX(dattm) FROM ?_invoice_payment WHERE invoice_id=?d", $this->invoice_id);
                if ($last_payment_date) {

                    $period = new Am_Period($this->second_period);
                    $date = date('Y-m-d', strtotime($last_payment_date));
                    $date = $period->addTo($date);
                }
                // date is in the past again;  Use tomorrow's date instead;
                $restore_limit_date = $this->getDi()->dateTime;
                $restore_limit_date->modify('-30 days');
                if (($date < $this->getDi()->sqlDate) && ($date > $restore_limit_date->format('Y-m-d'))) {
                    $tomorrow = $this->getDi()->dateTime;
                    $tomorrow->modify('+1 days');
                    $date = $tomorrow->format('Y-m-d');
                }
            }
        }
        $this->updateQuick('rebill_date', $date);
    }

    /**
     * Add period to rebill_date;
     * If current rebill_date is null or is in the past , script will use today's date(function will be executed only when payment is added to system). So next rebill date should be set to rebill_date + payment_period;
     * When second parameter ($date) will be passed, it will be used instead of rebill_date in order to handle prorated access situations.
     *
     * @param type $isFirst period that will be added;
     * @param type $date  - date that will be used instead of current rebill_date setting;
     *
     */
    function addToRebillDate($isFirst, $date=null)
    {
        // If we are done with rebills just set date to null;
        if ($this->getPaymentsCount() >= $this->getExpectedPaymentsCount()) {
            $this->updateQuick('rebill_date', null);
            $this->rebill_date = null;
            return;
        }

        $today = $this->getDi()->dateTime->format('Y-m-d');

        // Handle situation when customer try to rebill outdated payments;
        // In this situation rebill_date should be set to today.

        if (is_null($this->rebill_date) || ($this->rebill_date < $today))
            $this->rebill_date = $today;

        if (!is_null($date))
            $this->rebill_date = $date;

        $period = new Am_Period($isFirst ? $this->first_period : $this->second_period);
        $this->rebill_date = $period->addTo($this->rebill_date);
        $this->updateSelectedFields('rebill_date');
    }

    public function addVoid(Am_Paysystem_Transaction_Interface $transaction, $origReceiptId)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::VOID);
    }

    /** Add refund for payment with receiptId === $origReceiptId and related access records */
    public function addRefund(Am_Paysystem_Transaction_Interface $transaction, $origReceiptId, $amount = null)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::REFUND, $amount);
    }

    /** Add chargback for payment with given receiptId and disable ALL access records */
    public function addChargeback(Am_Paysystem_Transaction_Interface $transaction, $origReceiptId)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::CHARGEBACK);
    }

    protected function addRefundInternal(Am_Paysystem_Transaction_Interface $transaction, $origReceiptId, $refundType, $refundAmount = null)
    {
        $access = $this->getAccessRecords();
        $dattm = $transaction->getTime();
        $yesterday = clone $dattm;
        $yesterday->modify('-1 days');
        $totalPaid = 0;
        foreach ($this->getDi()->invoicePaymentTable->findBy(
            array('receipt_id' => $origReceiptId, 'invoice_id' => $this->invoice_id)) as $p) {
            $totalPaid += $p->amount;
//          do not disable any access for refunds
//            if ($refundType == InvoiceRefund::REFUND) // disable only related access
//                foreach ($access as $a)
//                    if ($a->invoice_payment_id == $p->invoice_payment_id)
//                        $a->updateQuick('expire_date', $yesterday->format('Y-m-d'));
        }
        if (!$refundAmount)
            $refundAmount = $transaction->getAmount();
        if (!$refundAmount)
            $refundAmount = $totalPaid;
        $refundAmount = abs($refundAmount);

        if (($refundType != InvoiceRefund::REFUND) || ($refundAmount === null) || ($totalPaid <= $refundAmount))
            $this->revokeAccess($transaction, $origReceiptId);

        // Some payment system plugins can pass negative value here.

        $r = $this->getDi()->invoiceRefundRecord;
        $r->setFromTransaction($this, $transaction, $origReceiptId, InvoiceRefund::REFUND);
        $r->amount = $refundAmount;
        $r->refund_type = (int) $refundType;
        if (!empty($p))
            $r->invoice_payment_id = $p->invoice_payment_id;
        $r->insert();
        if (!empty($p)) {
            $p->refund($r);
        }
        $this->updateStatus();
        $this->getUser()->checkSubscriptions(true);

        $this->getDi()->hook->call(Am_Event::INVOICE_PAYMENT_REFUND, array(
            'invoice' => $this,
            'refund' => $r,
            'user' => $this->getUser(),
        ));
        if($this->getDi()->config->get('store_pdf_file'))
        {
            try{
                $pdf = Am_Pdf_Invoice::create($r);
                $pdf->setDi(Am_Di::getInstance());
                $pdf->render();
            }
            catch(Exception $e)
            {
                $this->getDi()->errorLogTable->logException($e);
            }
        }
        return $r;
    }

    function emailCanceled($is_upgrade = false)
    {
        $products = $this->getProducts();
        if ($this->getDi()->config->get('mail_upgraded_cancel_member', 0) && $is_upgrade) {
            $et = Am_Mail_Template::load('mail_upgraded_cancel_member');
            if (!$et)
                throw new Am_Exception_Configuration("No e-mail template found for [mail_cancel_member]");
            $et->setUser($this->getUser());
            $et->setProduct($products[0]);
            $et->setInvoice($this);
            $et->send($this->getUser()->getEmail());
        }
        if ($this->getDi()->config->get('mail_cancel_member', 0) && !$is_upgrade) {
            $et = Am_Mail_Template::load('mail_cancel_member');
            if (!$et)
                throw new Am_Exception_Configuration("No e-mail template found for [mail_cancel_member]");
            $et->setUser($this->getUser());
            $et->setProduct($products[0]);
            $et->setInvoice($this);
            $et->send($this->getUser()->getEmail());
        }
        if ($this->getDi()->config->get('mail_cancel_admin', 0) && !$is_upgrade) {
            $et = Am_Mail_Template::load('mail_cancel_admin');
            if (!$et)
                throw new Am_Exception_Configuration("No e-mail template found for [mail_cancel_admin]");
            $et->setUser($this->getUser());
            $et->setProduct($products[0]);
            $et->setInvoice($this);
            $et->sendAdmin();
        }
    }

    public function calculateStatus()
    {
        $oldStatus = $this->status;
        $newStatus = self::PENDING;

        if (!$this->isConfirmed()) {
            foreach ($this->data()->getAll() as $k => $v) {
                if (strpos($k, self::SAVED_TRANSACTION_KEY) !== false) {
                    $newStatus = self::NOT_CONFIRMED;
                    break;
                }
            }
        } else {
            do {
                $row = $this->getTable()->getAdapter()->selectRow("
                SELECT
                    (SELECT COUNT(*) FROM ?_invoice_payment p WHERE p.invoice_id=?d and p.amount>0) as payments,
                    (SELECT COUNT(*) FROM ?_invoice_refund p WHERE p.invoice_id=?d and p.refund_type=1) as chargebacks,
                    (SELECT COUNT(*) FROM ?_invoice_refund p WHERE p.invoice_id=?d and p.refund_type<>1) as refunds,
                    (SELECT COUNT(*) FROM ?_access p WHERE p.invoice_id=?d ) as access
                ", $this->invoice_id, $this->invoice_id, $this->invoice_id, $this->invoice_id
                );
                if ($row['chargebacks']) {
                    $newStatus = self::CHARGEBACK;
                    break;
                }
                if (!empty($this->tm_started) || $row['access'] || $row['payments']) {
                    if (!$this->rebill_times) {
                        $newStatus = self::PAID;
                    } elseif ($row['payments'] >= $this->getExpectedPaymentsCount()) {
                        $newStatus = self::RECURRING_FINISHED;
                    } elseif ($this->tm_cancelled) {
                        $this->rebill_date = null;
                        $newStatus = self::RECURRING_CANCELLED;
                    } else {
                        $newStatus = self::RECURRING_ACTIVE;
                    }
                    break;
                }
            } while (false);
        }

        // If invocie still marked as recurring active but rebill_date is nul or more then 30 days in the past
        // or paysystem plugin was disabled already consider this invoice as failed.
        // Do not change status if paysystem doesn't support notifications.

            $ps = $this->getPaysystem();
        if(
            ($oldStatus != self::PENDING) && ($newStatus == self::RECURRING_ACTIVE) &&
            (is_null($this->rebill_date) || ($this->rebill_date < $this->getDi()->dateTime->modify('-30 days')->format('Y-m-d'))) &&
            (!$ps || (!in_array($ps->getRecurringType(), array(Am_Paysystem_Abstract::REPORTS_EOT, Am_Paysystem_Abstract::REPORTS_NOTHING))))
            )
        {
            $newStatus = self::RECURRING_FAILED;
        }
        if ($oldStatus != $newStatus) {
            if ($newStatus == self::RECURRING_CANCELLED)
                $this->emailCanceled($this->data()->get(self::UPGRADE_CANCEL));
        }
        return $newStatus;
    }

    public function updateStatus()
    {
        $this->setStatus($this->calculateStatus());
    }

    public function setStatus($newStatus)
    {
        $oldStatus = $this->status;
        if ($oldStatus != $newStatus)
        {
            $this->updateQuick('status', $newStatus);
            $this->getDi()->hook->call(Am_Event::INVOICE_STATUS_CHANGE, array(
                'invoice' => $this,
                'status' => $newStatus,
                'oldStatus' => $oldStatus,
            ));
        }
    }

    /**
     * How many payments must be done here for complete cycle
     */
    public function getExpectedPaymentsCount()
    {
        $ret = 0;
        if ($this->first_total > 0)
            $ret++;
        if ($this->second_total > 0)
            $ret += $this->rebill_times;
        return $ret;
    }

    public function getStatus()
    {
        return $this->status;
    }

    public function getStatusTextColor()
    {
        $color = "";
        switch ($this->status) {
            case self::PAID :
            case self::RECURRING_ACTIVE :
            case self::RECURRING_FINISHED :
                $color = "#488f37";
                break;
            case self::CHARGEBACK :
            case self::RECURRING_CANCELLED :
            case self::RECURRING_FAILED :
            case self::NOT_CONFIRMED :
                $color = "#ba2727";
                break;
            case self::PENDING :
                $color = "#555555";
                break;
        }
        return empty($color) ? ___(self::$statusText[$this->status]) : '<span style="color:' . $color . '">' . ___(self::$statusText[$this->status]) . '</span>';
    }

    public function getStatusText()
    {
        return ___(self::$statusText[$this->status]);
    }

    public function stopAccess(Am_Paysystem_Transaction_Interface $transaction)
    {
        // if second period has been set to lifetime
        // check if we've received all expected payments and invoice is not cancelled
        // if so, do not stop access
        if (($this->second_period == Am_Period::MAX_SQL_DATE)
            && ($this->status != Invoice::RECURRING_CANCELLED)
            && ($this->getPaymentsCount() >= $this->getExpectedPaymentsCount())) {
            return;
        }
        // stop access by setting expiration date to yesterday
        $yesterday = clone $transaction->getTime();
        $yesterday->modify('-1 days');
        $date = $yesterday->format('Y-m-d');
        foreach ($this->getAccessRecords() as $accessRecord) {
            if ($accessRecord->expire_date > $date)
                $accessRecord->updateQuick('expire_date', $date);
            if ($accessRecord->begin_date > $date)
                $accessRecord->updateQuick('begin_date', $date);
        }
        $this->getUser()->checkSubscriptions(true);
        $this->updateStatus();
    }

    public function revokeAccess(Am_Paysystem_Transaction_Interface $transaction, $origReceiptId)
    {
        foreach ($this->getDi()->invoicePaymentTable->findBy(
            array('receipt_id' => $origReceiptId, 'invoice_id' => $this->invoice_id)) as $p) {
                foreach ($this->getDi()->accessTable->findBy(
                    array('invoice_payment_id' => $p->pk(), 'invoice_id' => $this->invoice_id)) as $a) {
                        $a->delete();
                }
        }
        $this->getUser()->checkSubscriptions(true);
        $this->updateStatus();
    }

    /**
     * @return array of related Access objects
     */
    public function getAccessRecords()
    {
        return $this->getDi()->accessTable->findByInvoiceId($this->invoice_id, null, null, "access_id");
    }

    /** @return date max expiration date of current invoice's access records */
    public function getAccessExpire()
    {
        return $this->_db->selectCell("SELECT MAX(expire_date) FROM ?_access
            WHERE invoice_id=?d", $this->invoice_id);
    }

    public function getPaymentRecords()
    {
        return $this->getDi()->invoicePaymentTable->findByInvoiceId($this->invoice_id, null, null, "invoice_payment_id");
    }

    public function getRefundRecords()
    {
        return $this->getDi()->invoiceRefundTable->findByInvoiceId($this->invoice_id, null, null, "invoice_refund_id");
    }

    public function getPaymentsCount()
    {
        return $this->getDi()->invoicePaymentTable->getPaymentsCount($this->invoice_id);
    }

    public function getRefundsCount()
    {
        return $this->getDi()->invoiceRefundTable->getRefundsCount($this->invoice_id);
    }

    public function setCancelled($cancelled = true)
    {
        $this->updateQuick(array(
            'tm_cancelled' => $cancelled ? sqlTime('now') : null,
            'rebill_date' => $cancelled ? null : sqlTime('now'),
        ));
        $this->updateStatus();
        if (!$cancelled)
            $this->recalculateRebillDate();
        if ($cancelled)
            $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_CANCEL, array('invoice' => $this));
        return $this;
    }

    public function isCancelled()
    {
        if ($this->tm_cancelled == '0000-00-00 00:00:00')
            $this->tm_cancelled = null;
        return (bool) $this->tm_cancelled;
    }

    public function isFailed()
    {
        return $this->status == self::RECURRING_FAILED;
    }

    /**
     * @return bool true if there was real payments for this invoice
     */
    public function isPaid()
    {
        return (bool) $this->getPaymentsCount();
    }

    /**
     * @return bool true if this invoice is "completed" as it said in aMember<=3
     */
    public function isCompleted()
    {
        if (empty($this->invoice_id))
            return false;
        return $this->status != self::PENDING;
    }

    /** @return string caclulated billing terms */
    public function getTerms()
    {
        return $this->terms ? $this->terms : $this->getTermsText();
    }

    public function getTermsText()
    {
        $tt = new Am_TermsText($this);
        return (string) $tt;
    }

    public function __sleep()
    {
        return array_merge(parent::__sleep(), array('_items'));
    }

    public function exportXmlLog()
    {
        $xml = new XMLWriter();
        $xml->openMemory();
        $xml->setIndent(true);
        $xml->startDocument();
        $xml->startElement('invoice-log');
        $xml->writeElement('version', '1.0'); // log format version
        $xml->writeComment(sprintf("Dumping invoice#%d, user#%d", $this->invoice_id, $this->user_id));

        $xml->startElement('event');
        $xml->writeAttribute('time', $this->tm_added);
        $this->exportXml($xml,
            array('element' => 'invoice',
                'nested' => array(
                    array('invoiceItem'),
                    array('access', array('element' => 'access')),
                    array('invoicePayment', array('element' => 'invoice-payment')),
                    array('invoiceRefund', array('element' => 'invoice-refund')),
            )));
        $xml->endElement();

        foreach ($this->getDi()->invoiceLogTable->findByInvoiceId($this->pk()) as $log) {
            $xml->startElement('event');
            $xml->writeAttribute('time', $log->tm);
            foreach ($log->getXmlDetails() as $a) {
                list($type, $source) = $a;
                $xml->writeRaw($source);
            }
            $xml->endElement();
        }

        $xml->endElement();
        echo $xml->flush();
    }

    /**
     * Return true if subscription can be changed
     * @param Invoice $invoice
     * @param BillingPlan $from
     * @param BillingPlan $to
     * @return boolean
     */
    public function canUpgrade(InvoiceItem $item, ProductUpgrade $upgrade)
    {
        if($upgrade->hide_if_to && in_array($upgrade->getToProduct()->pk(), $this->getUser()->getActiveProductIds()))
            return false;
        if ($item->billing_plan_id != $upgrade->from_billing_plan_id)
            return false;
        // check for other recurring items
        foreach ($this->getItems() as $it) {
            if ($item->invoice_item_id != $it->invoice_item_id)
                if ((float) $it->second_total)
                    return false; // there is another recurring item, upgrade impossible

        }
        $to = $upgrade->getToPlan();
        if (!$to)
            return false;
        // check if $to is compatible to billing terms
        $newItem = $this->createItem($to->getProduct());
        $error = $this->isItemCompatible($newItem, array($item));
        if (null != $error)
            return false;
        /* check if paysystem can do upgrade */
        $pr = $item->tryLoadProduct();
        if (!$pr instanceof Product)
            return false;
        $ps = $this->getPaysystem();
        if (!$ps)
            return false;

        //check Product Requirements,
        //take into account the fact that
        //from product become expired
        $u = $this->getUser();
        $p = $upgrade->getFromProduct();
        $activeProductIds = array_diff($u->getActiveProductIds(), array($p->pk()));
        $expiredProductIds = array_merge($u->getExpiredProductIds(), array($p->pk()));
        if ($error = $this->getDi()->productTable->checkRequirements(
                array($upgrade->getToProduct()), $activeProductIds, $expiredProductIds)) {
            return false;
        }
        return $ps->canUpgrade($this, $item, $upgrade);
    }

    /**
     * Upgrade billing plan in subscription from one to another
     * @param Invoice $invoice
     * @param BillingPlan $from
     * @param BillingPlan $to
     * @throws Am_Exception if failed
     * @return Invoice $invoice
     */
    public function doUpgrade(InvoiceItem $item, ProductUpgrade $upgrade, $coupon = false)
    {
        $ps = $this->getPaysystem();
        if (!$ps)
            throw new Am_Exception_Paysystem("doUpgrade failed - {$this->paysys_id} not available");
        $newInvoice = $upgrade->createUpgradeInvoice($this, $item, $coupon);
        if ($ps->getId() == 'free') {
            foreach ($this->getDi()->paysystemList->getAllPublic() as $pd) {
                $p = $this->getDi()->plugins_payment->loadGet($pd->getId());
                if (!$p->isNotAcceptableForInvoice($newInvoice)) {
                    $newInvoice->setPaysystem($p->getId());
                    $newInvoice->insert();
                    return $newInvoice;
                }
            }
        }
        $newInvoice->insert();
        try {
            $ps->doUpgrade($this, $item, $newInvoice, $upgrade);
        } catch (Am_Exception_NotImplemented $e) {
            // nope - ignore not implemented error
        }
        return $newInvoice;
    }

    function doRestoreRecurring()
    {
        if(!in_array($this->status, array(self::RECURRING_CANCELLED, self::RECURRING_FAILED)))
            throw new Am_Exception_InputError(sprintf("Can't restore billing for invoice %s, invoice is not cancelled", $this->public_id));

        $newInvoice = $this->getDi()->invoiceRecord;
        $newInvoice->user_id = $this->user_id;

        // Keep these fields in newly created item;
        $keep = array(
                    'item_id', 'item_type', 'item_title', 'item_description',
                    'qty', 'second_price', 'second_discount', 'second_tax',
                    'second_total', 'second_shipping', 'second_period',
                    'currency', 'tax_group', 'is_countable', 'is_tangible',
                    'billing_plan_id', 'billing_plan_data'
                    );

        $discount = 0;

        foreach($this->getItems() as $item)
        {
            // Do not include items which are not recurring;
            if(!$item->rebill_times) continue;

            //calculate number of rebills which where not processed.
            if($item->rebill_times < IProduct::RECURRING_REBILLS) {
                $rebillsCount  = $this->getPaymentsCount() - ($this->first_total>0 ? 1 : 0);
                $rebillTimes = $item->rebill_times - $rebillsCount;

                // This item was finished already; Do not include it in new invoice;
                if($rebillTimes<=0) continue;
            } else {
                $rebillTimes = $item->rebill_times;
            }
            $itemArr = $item->toArray();

            // Now unset all unnecessary fields;
            foreach($itemArr as $k=>$v)
            {
                if(!in_array($k, $keep)) unset($itemArr[$k]);
            }

            $discount += $item->data()->get('orig_second_discount') ?: $itemArr['second_discount'];

            // Now if user already has active period for this invoice we should compensate this;
            if(($free_days = $this->getDi()->db->selectCell("select to_days(max(expire_date)) - to_days(?) from ?_access where invoice_item_id=?", $this->getDi()->sqlDate, $item->pk())) >0)
            {
                $itemArr['first_price'] = 0;
                $itemArr['first_period'] = $free_days.'d';
            } else {
                // Set first_* values to the same values as second_*
                foreach(array('_price', '_discount', '_tax', '_total', '_shipping', '_period') as $k)
                    $itemArr['first'.$k] = $itemArr['second'.$k];

                if ($rebillTimes != IProduct::RECURRING_REBILLS) $rebillTimes--;
            }

            $itemArr['rebill_times'] = $rebillTimes;

            if ($orig_second_price = $item->data()->get('orig_second_price')) {
                $itemArr['first_price'] = $itemArr['first_price'] ? $orig_second_price : $itemArr['first_price'];
                $itemArr['second_price'] = $orig_second_price;
            }

            $newItem = $this->getDi()->invoiceItemRecord;
            $newItem->fromRow($itemArr);

            $newInvoice->addItem($newItem);
        }

        if(!count($newInvoice->getItems()))
            throw new Am_Exception_InputError(sprintf("Can't restore billing for invoice %s, no items to restore", $this->public_id));

        if ($discount) {
            $newInvoice->setDiscount($discount, $discount);
        }

        $newInvoice->calculate();

        return $newInvoice;
    }

    /**
     * Load paysystem or return null if disabled
     * @return Am_Paysystem_Abstract|null
     */
    public function getPaysystem()
    {
        if (!$this->paysys_id)
            return null;
        if (!$this->getDi()->plugins_payment->isEnabled($this->paysys_id))
            return null;
        return $this->getDi()->plugins_payment->loadGet($this->paysys_id);
    }
}

/**
 * @method InvoiceTable getInstance()
 * @method Invoice[] selectObjects()
 * @method Invoice load load($id, $throwException=true)
 */
class InvoiceTable extends Am_Table_WithData
{
    protected $_key = 'invoice_id';
    protected $_table = '?_invoice';
    protected $_recordClass = 'Invoice';

    function findPaidCountByCouponId($coupon_id, $user_id)
    {
        return $this->_db->selectCell("
            SELECT COUNT(*)
            FROM ?_invoice
            WHERE coupon_id=?d
            AND user_id=?d
            AND status<>?d
        ", $coupon_id, $user_id, Invoice::PENDING);
    }

    function findForRebill($date, $paysys_id = null)
    {
        return $this->selectObjects("
            SELECT * FROM ?_invoice
            WHERE rebill_date = ? AND IFNULL(tm_cancelled,0)=0 AND status=? { AND paysys_id = ? }",
                $date, Invoice::RECURRING_ACTIVE,
                $paysys_id ? $paysys_id : DBSIMPLE_SKIP);
    }

    /** @return Invoice|null */
    function findByReceiptIdAndPlugin($receiptId, $paysysId)
    {
        $objs = $this->selectObjects("SELECT i.* FROM ?_invoice i
            LEFT JOIN ?_invoice_payment p
            ON p.invoice_id=i.invoice_id
            WHERE p.receipt_id=?
            AND i.paysys_id=?", $receiptId, $paysysId);

        return count($objs) ? current($objs) : null;
    }

    /** @return Invoice|null */
    function findBySecureId($invoiceId, $prefix)
    {
        if (!preg_match('/(.*)-([a-z0-9]*)$/', filterId($invoiceId), $matches))
            return;

        $id = $matches[1];
        $code = $matches[2];

        $id = filterId($id);
        if (!strlen($id))
            return;
        $invoice = $this->findFirstByPublicId($id);
        if (!$invoice)
            return;
        if ($invoice->getUniqId($prefix) != $code)
            return;
        return $invoice;
    }

    public function clearPending($date)
    {
        $q = $this->_db->queryResultOnly("SELECT i.*
            FROM ?_invoice i
                LEFT JOIN ?_invoice_payment p ON p.invoice_id = i.invoice_id
                LEFT JOIN ?_access a ON a.invoice_id = i.invoice_id
            WHERE i.status = 0 AND p.invoice_payment_id IS NULL AND a.access_id IS NULL
             AND i.tm_added < ?
             AND (due_date IS NULL OR due_date < ?)
            GROUP BY i.invoice_id
            ", sqlTime($date), $this->getDi()->sqlDate);
        while ($r = $this->_db->fetchRow($q)) {
            $i = $this->createRecord($r);
            $i->delete();
        }
    }

    function selectLast($num, $statuses = array())
    {
        return $this->selectObjects("SELECT i.*,
            (SELECT GROUP_CONCAT(IF(qty>1, CONCAT(qty, ' pcs - ', item_title), item_title) SEPARATOR ', ') FROM ?_invoice_item WHERE invoice_id=i.invoice_id) AS items,
            u.login, u.email, CONCAT(u.name_f, ' ', u.name_l) AS name, u.added
            FROM ?_invoice i LEFT JOIN ?_user u USING (user_id)
            { WHERE i.status in (?a) }
            ORDER BY i.invoice_id DESC LIMIT ?d", $statuses ? $statuses : DBSIMPLE_SKIP, $num);
    }
}