<?php
/**
 * Invoicing - class CInvoices
 *
 * PHP version 5
 *
 * @category PHP
 * @package  Invoicing
 * @author   Dimiter Tsonev <dtsonev@zlatestranky.cz>
 * @author   Martin Staník <mstanik@zlatestranky.cz>
 * @license  Mediatel's GPL http://wiki.zs.loc/index.php/Mediatel_license
 * @link     invoicing
 *
 **/


/**
 * Invoicing - class CInvoices - class body
 *  Use the overloaded $this->oDB instead of the global $oDB !
 *
 * PHP version 5
 *
 * @category PHP
 * @package  Invoicing
 * @author   Dimiter Tsonev <dtsonev@zlatestranky.cz>
 * @author   Martin Staník <mstanik@zlatestranky.cz>
 * @license  Mediatel's GPL http://wiki.zs.loc/index.php/Mediatel_license
 * @link     invoicing
 *
 **/
class CInvoices
{
    /**
     * Use the overloaded $this->oDB instead of the global $oDB !
     **/


    /**
     * function __construct - constructor
     */
    public function __construct()
    {

    }//end __construct()


    /**
     * function __destruct - destructor
     */
    public function __destruct()
    {
        $oDB = &$GLOBALS['oDB'];

        if (isset($oDB) === true) {
            $oDB->__destruct();
            unset($oDB);
        }

    }//end __destruct()


    /**
     * function __sleep - closes the database link on sleep
     * 
     * @return nothing
     */
    public function __sleep()
    {
        $oDB = &$GLOBALS['oDB'];

        if (isset($oDB) === true) {
            $oDB->closeConnection();
            CDebugLog::Writeln('EN> CInvoices sleep. End of script.');
        }

    }//end __sleep()


    /**
     * function __get - the __get magic function
     *
     * @param string $aAttrName name of the attribute
     *
     * @return string the value of the attribute
     * 
     */
    public function __get($aAttrName)
    {
        $oDB     = &$GLOBALS['oDB'];
        $dbSetup = &$GLOBALS['dbSetup'];

        switch ($aAttrName) {
        case 'oDB' :
            if (isset($oDB) === false) {
                $oDB = new CDBTools($dbSetup);
            }

            return $oDB;
            break;
        }

    }//end __get()


    /**
     * function getActiveBooks - selects the active books
     *
     * @param string $sBookTypeId BookType Id
     *   
     * @return array of all the active books
     * 
     */
    public function getActiveBooks($sBookTypeId)
    {
        $sql = sprintf(SELECT_ACTIVE_BOOK_SECTIONS, $sBookTypeId);
        return $this->oDB->MyDbQueryRev($sql);

    }//end getActiveBooks()


    /**
     * function getActiveCampaigns - selects the active campaigns
     *   
     * @return array of all the active campaigns
     * 
     */
    public function getActiveCampaigns()
    {
        $sql = SELECT_ACTIVE_CAMPAIGNS;
        return $this->oDB->MyDbQueryRev($sql);

    }//end getActiveCampaigns()


    /**
     * function getActiveProducts - selects the active products
     *
     * @param string $aBookId id of the book
     *   
     * @return array of all the active products
     * 
     */
    public function getActiveProducts($aBookId)
    {
        $sql = sprintf(SELECT_ACTIVE_PRODUCTS, $aBookId);
        return $this->oDB->MyDbQueryRev($sql);

    }//end getActiveProducts()


    /**
     * function getAllBooks - selects all the books
     *   
     * @return array of all the books
     * 
     */
    public function getAllBooks()
    {
        $sql = SELECT_ALL_BOOK_SECTIONS;
        return $this->oDB->MyDbQueryRev($sql);

    }//end getAllBooks()


    /**
     * function getAllPayPlans - selects all the payplans from nxDSMP
     *   
     * @param string $aBookTypeId the book type id
     *   
     * @return array of all the payplans
     * 
     */
    public function getAllPayPlans($aBookTypeId='')
    {
        if ($aBookTypeId !== '') {
            $sql = sprintf(SELECT_ALL_PAYPLANS_PER_BOOKTYPE, $aBookTypeId);
        } else {
            $sql = SELECT_ALL_PAYPLANS;
        }

        return $this->oDB->MyDbQueryRev($sql);

    }//end getAllPayPlans()


    /**
     * function getAllCampaigns - selects all the campaigns
     *   
     * @return array of all the campaigns
     * 
     */
    public function getAllCampaigns()
    {
        $sql = SELECT_ALL_CAMPAIGNS;
        return $this->oDB->MyDbQueryRev($sql);

    }//end getAllCampaigns()


    /**
     * function getAllProductTypes - selects all the products
     *   
     * @return array of all the product types
     * 
     */
    public function getAllProductTypes()
    {
        $sql = GET_ALL_ADTYPE_INFO;
        return $this->oDB->MyDbQueryRev($sql);

    }//end getAllProductTypes()


    /**
     * function getAllProducts - selects all the products
     *
     * @param string $aBookId id of the book
     *   
     * @return array of all the products
     * 
     */
    public function getAllProducts($aBookId)
    {
        $sql = sprintf(SELECT_ALL_PRODUCTS, $aBookId);
        return $this->oDB->MyDbQueryRev($sql);

    }//end getAllProducts()


    /**
     * function getBookInfo - selects a book record
     *
     * @param string $aBookId id of the book
     *   
     * @return array with one book only or empty
     * 
     */
    public function getBookInfo($aBookId)
    {
        $sql = sprintf(SELECT_A_BOOK_SECTION, $aBookId);
        return $this->oDB->MyDbQueryRev($sql);

    }//end getBookInfo()


    /**
     * function _prapareIdParam - prapares an id param for a proc call
     *
     * @param string $aValue the id
     *
     * @return string the prepared param
     * 
     */
    private static function _prapareIdParam($aValue)
    {
        $res = 'NULL';

        if ($aValue !== '') {
            $res = "HexToRaw('".CDBTools::ToDB($aValue)."')";
        }

        return $res;

    }//end _prapareIdParam()


    /**
     * function _prapareNullableParam - prapares a param for a proc call
     *  if the param is empty sets it null
     *
     * @param string $aValue the value
     *
     * @return string the prepared param
     * 
     */
    private static function _prapareNullableParam($aValue)
    {
        $res = 'NULL';

        if ($aValue !== '') {
            $res = "'".CDBTools::ToDB($aValue)."'";
        }

        return $res;

    }//end _prapareNullableParam()


    /**
     * function _prapareDateParam - prapares a date param for a proc call
     *  if the param is empty returns the default value without processing it
     *
     * @param string $aValue   the value
     * @param string $aDefault the default value
     *
     * @return string the prepared param
     * 
     */
    private static function _prapareDateParam($aValue, $aDefault)
    {
        $res = 'NULL';

        if ($aValue !== '') {
            $res  = "TO_DATE('";
            $res .= CDBTools::ToDB($aValue)."', '".DATE_FORMAT_SQL."')";
        } else {
            $res  = $aDefault;
        }

        return $res;

    }//end _prapareDateParam()


    /**
     * function addInvoice - inserts a new record into table invoice using
     *   the stored procedure addInvoice
     *
     * @param string $aBookSectionId    id of the book
     * @param string $aCampaignId       id of the campaign
     * @param date   $aContractDate     Contract Date
     * @param string $aNXClientNumber   id of the client
     * @param string $aNXAgencyNumber   id of the agency
     * @param string $aSellerID         id of the seller
     * @param string $aIDSentStatus     id of the sent status
     * @param string $aIsHandMade       if is hand made - 1/0 = true/false
     * @param string $aOrderNumber      the order number
     * @param string $aPayPlan          the pay plan id
     * @param string $aIDInvoiceEdited  id of the invoice
     * @param string $aSetupDate        the clear sense setup date
     * @param string $aDuration         duration in monthe of the clear sense
     * @param string $aCsSetupInvoiceId id of the clear sense setup invoice
     * @param string $aIdBTWhitepages   id of the white pages book type
     * @param string $csPayPlanOrder    the order among the management invoices (cs)
     * @param string $csPayPlanPrev     id of the previous management invoice (cs)
     * @param string $csPayPlanSendDate the date when the invoice should be sent
     * @param string &$aInvoiceNumber  Invoice Number
     *   
     * @return string id of the invoice
     * 
     */
    public function addInvoice($aBookSectionId, $aCampaignId, $aContractDate,
 $aNXClientNumber, $aNXAgencyNumber, $aSellerID, $aIDSentStatus, $aIsHandMade,
 $aOrderNumber, $aPayPlan, $aIDInvoiceEdited, $aSetupDate, $aDuration,
 $aCsSetupInvoiceId, $aIdBTWhitepages, $csPayPlanOrder, $csPayPlanPrev,
 $csPayPlanSendDate, $csOriginalPayplan, &$aInvoiceNumber)
    {
        $arrUzivatelOsUdaje = &$GLOBALS['arrUzivatelOsUdaje'];

        $workstation = $_SERVER['REMOTE_ADDR'];
        $username    = $arrUzivatelOsUdaje['email'];

        $aIDInvoiceEdited  = self::_prapareIdParam($aIDInvoiceEdited);
        $csPayPlanPrev     = self::_prapareIdParam($csPayPlanPrev);
        $aIDSentStatus     = self::_prapareIdParam($aIDSentStatus);
        $aNXAgencyNumber   = self::_prapareNullableParam($aNXAgencyNumber);
        $aSellerID         = self::_prapareNullableParam($aSellerID);
        $aCsSetupInvoiceId = self::_prapareNullableParam($aCsSetupInvoiceId);
        $aPayPlan          = self::_prapareNullableParam($aPayPlan);
        $csOriginalPayplan = self::_prapareNullableParam($csOriginalPayplan);
        $csPayPlanOrder    = self::_prapareNullableParam($csPayPlanOrder);
        $csPayPlanSendDate = self::_prapareDateParam($csPayPlanSendDate, 'SYSDATE');
        $aSetupDate        = self::_prapareDateParam($aSetupDate, 'NULL');

        $dsmpSectionVal = 'NULL';

        $sql  = 'begin '.SCHEMA.".addInvoice('";
        $sql .= CDBTools::ToDB($username)."', '";
        $sql .= CDBTools::ToDB($workstation)."', '";
        $sql .= CDBTools::ToDB($aNXClientNumber)."', ";
        $sql .= $aNXAgencyNumber.', ';
        $sql .= $aSellerID.', ';
        $sql .= $aIDSentStatus.", '";
        $sql .= CDBTools::ToDB($aIsHandMade)."', HEXTORAW('";
        $sql .= CDBTools::ToDB($aCampaignId)."'), TO_DATE('";
        $sql .= CDBTools::ToDB($aContractDate)."', '".DATE_FORMAT_SQL."'), ";
        $sql .= CDBTools::ToDB($dsmpSectionVal).", HEXTORAW('";
        $sql .= CDBTools::ToDB($aBookSectionId)."'), '";
        $sql .= CDBTools::ToDB($aOrderNumber)."', ";
        $sql .= $aPayPlan.', ';
        $sql .= $csPayPlanOrder.', ';
        $sql .= $csPayPlanPrev.', ';
        $sql .= ':IDInvoice, :InvoiceNumber, '.$aIDInvoiceEdited.', ';
        $sql .= 'SYSDATE, ';
        $sql .= $aSetupDate.", '";
        $sql .= CDBTools::ToDB($aDuration)."', ";
        $sql .= $aCsSetupInvoiceId.', ';
        $sql .= $csPayPlanSendDate.', ';
        $sql .= $csOriginalPayplan.', ';
        $sql .= $aIdBTWhitepages.' ';
        $sql .= ' ); end;';

        $errorCode = 0;
        unset($params);
        $params = array();

        $idInvoice = '';

        $param1['name'] = ':IDInvoice';
        $param1['var']  = &$idInvoice;
        $param1['size'] = 32;
        $param1['type'] = '';
        $params[]       = &$param1;

        $param2['name'] = ':InvoiceNumber';
        $param2['var']  = &$aInvoiceNumber;
        $param2['size'] = 32;
        $param2['type'] = '';
        $params[]       = &$param2;

        $this->oDB->ExecuteWithBindings($sql, $params);

        return $idInvoice;

    }//end addInvoice()


    /**
     * function addInvoiceProduct - inserts a new item for an invoice using sp
     *
     * @param string $aIDInvoice  id of the invoice
     * @param string $aIDProduct  id of the product
     * @param string $aFinalPrice final price
     * @param string $aQuantity   quatity
     * @param string $aItemPrice  the item price
     *   
     * @return boolean true
     * 
     */
    public function addInvoiceProduct($aIDInvoice, $aIDProduct, $aFinalPrice,
 $aQuantity, $aItemPrice)
    {
        $sql  = 'begin '.SCHEMA.".addInvoiceProduct(HEXTORAW('";
        $sql .= CDBTools::ToDB($aIDInvoice)."'), HEXTORAW('";
        $sql .= CDBTools::ToDB($aIDProduct)."'), TO_NUMBER('";
        $sql .= CDBTools::ToDB($aFinalPrice)."'), '";
        $sql .= CDBTools::ToDB($aQuantity)."', TO_NUMBER('";
        $sql .= CDBTools::ToDB($aItemPrice)."') ); end;";

        $errorCode = 0;
        unset($params);
        $params = array();

        $this->oDB->ExecuteWithBindings($sql, $params);

        return true;

    }//end addInvoiceProduct()


    /**
     * function finalizeInvoice - finalizes the invoice insertion
     *
     * @param string $aIDInvoice id of the invoice
     *   
     * @return boolean true
     *
     */
    public function finalizeInvoice($aIDInvoice)
    {
        $sql  = 'begin '.SCHEMA.".finalizeInvoice(HEXTORAW('";
        $sql .= CDBTools::ToDB($aIDInvoice)."') ); end;";

        $errorCode = 0;
        unset($params);
        $params = array();

        $this->oDB->ExecuteWithBindings($sql, $params);

        return true;

    }//end finalizeInvoice()


    /**
     * function finalizeCSManagementInvoices - finalizes the Clear Sense 
     *   management invoices insertion
     *
     * @param string $csSetupInvoiceId id of the setup invoice
     *   
     * @return boolean true
     *
     */
    public function finalizeCSManagementInvoices($csSetupInvoiceId)
    {
        $sql  = 'begin '.SCHEMA.".finalizeCSManagementInvoices(HEXTORAW('";
        $sql .= CDBTools::ToDB($csSetupInvoiceId)."') ); end;";

        $errorCode = 0;
        unset($params);
        $params = array();

        $this->oDB->ExecuteWithBindings($sql, $params);

        return true;

    }//end finalizeCSManagementInvoices()


    /**
     * function cancelInvoice - selects the active products
     *
     * @param string $aIDInvoice id of the invoice
     *   
     * @return boolean true
     * 
     */
    public function cancelInvoice($aIDInvoice)
    {
        $arrUzivatelOsUdaje = &$GLOBALS['arrUzivatelOsUdaje'];

        $workstation = $_SERVER['REMOTE_ADDR'];
        $username    = $arrUzivatelOsUdaje['email'];

        $sql  = 'begin '.SCHEMA.".cancelInvoice(HEXTORAW('";
        $sql .= CDBTools::ToDB($aIDInvoice)."'), '";
        $sql .= CDBTools::ToDB($username)."', '";
        $sql .= CDBTools::ToDB($workstation)."' ); end;";

        $errorCode = 0;
        unset($params);
        $params = array();

        $this->oDB->ExecuteWithBindings($sql, $params);

        return true;

    }//end cancelInvoice()


    /**
     * function getInvoiceStatuses - selects all invoice statuses
     *
     * @return array of all invoice statuses
     * 
     */
    public function getInvoiceStatuses()
    {
        $allInvoiceStatuses = $this->oDB->MyDbQueryRev(ALL_INVOICE_STATUSES);

        $isISFakturaCanceled[0] = INVOICESTATUS_ID_ISFAKTURACANCELED;
        $isISFakturaCanceled[1] = INVOICESTATUS_ID_ISFAKTURACANCELED;
        $isISFakturaCanceled[2] = 'ISFaktura (stornováno)';
        $allInvoiceStatuses[]   = $isISFakturaCanceled;

        return $allInvoiceStatuses;

    }//end getInvoiceStatuses()


    /**
     * function getSentStatuses - selects all sent statuses
     *
     * @return array of all sent statuses
     * 
     */
    public function getSentStatuses()
    {
        return $this->oDB->MyDbQueryRev(ALL_SENT_STATUSES);

    }//end getSentStatuses()


    /**
     * function _toDateCond - generates a date condition for a where clause
     *
     * @param datetime $aDate the date
     *
     * @return string a date condition for a where clause
     * 
     */
    private static function _toDateCond($aDate)
    {
        return "TO_DATE('".CDBTools::ToDB($aDate)."', '".DATE_FORMAT_SQL."')";

    }//end _toDateCond()


    /**
     * function _addDateCond - adds a date condition to a where clause
     *
     * @param datetime $aValue           the date
     * @param string   $aFieldName       the field name
     * @param string   $aCompareOperator the compare operator
     * @param string   &$aWhere          the where clause
     * @param string   $aQuotation       the Quotation
     *
     * @return boolean if a condition was added to the where clause
     * 
     */
    private static function _addDateCond($aValue, $aFieldName, $aCompareOperator,
 &$aWhere, $aQuotation="'")
    {
        $res = false;

        if (trim($aValue) !== '') {
            CDBTools::AddConditionToWhereClause($aFieldName,
                $aCompareOperator, self::_toDateCond($aValue),
                $aWhere, $aQuotation);
            $res = true;
        }

        return $res;

    }//end _addDateCond()


    /**
     * function _addTxtCond - adds a text condition to a where clause
     *
     * @param string $aValue          the value
     * @param string $aFieldName      the field name
     * @param string $aIsInnerRequest if is ajax
     * @param string &$aWhere         the where clause
     *
     * @return boolean if a condition was added to the where clause
     * 
     */
    private static function _addTxtCond($aValue, $aFieldName, $aIsInnerRequest,
 &$aWhere)
    {
        $res = false;

        if (trim($aValue) !== '') {
            $val = CDBTools::ToDB(CTools::StrToUpper_mb($aValue, $aIsInnerRequest));
            $val = '%'.$val.'%';
            CDBTools::AddConditionToWhereClause('UPPER('.$aFieldName.')', 'like',
                $val, $aWhere);
            $res = true;
        }

        return $res;

    }//end _addTxtCond()


    /**
     * function _addIdCond - adds an id condition to a where clause
     *
     * @param string $aValue     the id
     * @param string $aFieldName the field name
     * @param string &$aWhere    the where clause
     *
     * @return boolean if a condition was added to the where clause
     * 
     */
    private static function _addIdCond($aValue, $aFieldName, &$aWhere)
    {
        $res = false;

        if (trim($aValue) !== '') {
            CDBTools::AddConditionToWhereClause($aFieldName,
                '=', "HEXTORAW('".CDBTools::ToDB($aValue)."')", $aWhere, '');
            $res = true;
        }

        return $res;

    }//end _addIdCond()


    /**
     * function _addInvStatCond - adds an invoice status condition to a where clause
     *
     * @param array  $aAllInvoiceStatuses All the Invoice Status values
     * @param string &$aWhere             the where clause
     *
     * @return boolean if a condition was added to the where clause
     * 
     */
    private static function _addInvStatCond($aAllInvoiceStatuses, &$aWhere)
    {
        $res = false;

        $invoiceStatuses          = '';
        $isThereISFakturaCanceled = in_array(INVOICESTATUS_ID_ISFAKTURACANCELED,
            $aAllInvoiceStatuses);
        $isThereISFaktura         = in_array(INVOICESTATUS_ID_ISINVOICE,
            $aAllInvoiceStatuses);

        foreach ($aAllInvoiceStatuses as $invoiceStatus) {
            if ($invoiceStatus !== '') {
                switch ($invoiceStatus) {
                case INVOICESTATUS_ID_ISFAKTURACANCELED :
                    if ($isThereISFaktura === false) {
                        $invStatIdIsInv   = "HEXTORAW('";
                        $invStatIdIsInv  .=
                            CDBTools::ToDB(INVOICESTATUS_ID_ISINVOICE)."')";
                        $isIsInvoiceWhere =
                            CDBTools::generateCondition(INVOICE_STATUS_ID, '=',
                                $invStatIdIsInv, '');

                        CDBTools::AddConditionToWhereClause_(' CI.ID IS NOT NULL ',
                            $isIsInvoiceWhere);

                        CDBTools::AddConditionToORClause_($isIsInvoiceWhere,
                            $invoiceStatuses);
                    }

                    break;

                case INVOICESTATUS_ID_ISINVOICE :
                    $isIsInvoiceWhere =
                        CDBTools::generateCondition(INVOICE_STATUS_ID, '=',
                            "HEXTORAW('".CDBTools::ToDB($invoiceStatus)."')", '');

                    if ($isThereISFakturaCanceled === false) {
                        CDBTools::AddConditionToWhereClause_(' CI.ID IS NULL ',
                            $isIsInvoiceWhere);
                    }

                    CDBTools::AddConditionToORClause_($isIsInvoiceWhere,
                        $invoiceStatuses);

                    break;

                default :
                    CDBTools::AddConditionToORClause(INVOICE_STATUS_ID, '=',
                        "HEXTORAW('".CDBTools::ToDB($invoiceStatus)."')",
                        $invoiceStatuses, '');
                }//end switch

                $res = true;
            }//end if
        }//end foreach

        CDBTools::AddConditionToWhereClause_($invoiceStatuses, $aWhere);

        return $res;

    }//end _addInvStatCond()


    /**
     * function _addSntStatCond - adds a sent status condition to a where clause
     *
     * @param array  $aAllSentStatuses All the Sent Status values
     * @param string &$aWhere          the where clause
     *
     * @return boolean if a condition was added to the where clause
     * 
     */
    private static function _addSntStatCond($aAllSentStatuses, &$aWhere)
    {
        $res = false;

        $sentStatuses = '';
        foreach ($aAllSentStatuses as $sentStatus) {
            if ($sentStatus !== '') {
                CDBTools::AddConditionToORClause(SENT_STATUS_NAME, '=',
                    CDBTools::ToDB($sentStatus), $sentStatuses);
                $res = true;
            }
        }

        CDBTools::AddConditionToWhereClause_($sentStatuses, $aWhere);

        return $res;

    }//end _addSntStatCond()


    /**
     * function searchInvoice - search for invoices
     *
     * @param string  &$asel                     where cl. if selecting another page
     * @param string  &$acount                   the count of records found
     * @param string  $acontractDateBegin        contract Date Begin
     * @param string  $acontractDateCompareBegin contract Date CompareBegin
     * @param string  $acontractDateEnd          contract Date End
     * @param string  $acontractDateCompareEnd   contract Date CompareEnd
     * @param string  $acreatedDateBegin         created Date Begin
     * @param string  $acreatedDateCompareBegin  created Date Compare Begin
     * @param string  $acreatedDateEnd           created Date End
     * @param string  $acreatedDateCompareEnd    created Date CompareEnd
     * @param string  $bookId                    id of the book
     * @param string  $campaignId                id of the campaign
     * @param string  $productTypeId             id of the product
     * @param string  $invoiceNumber             invoice number
     * @param string  $isHandMade                if is hand made '1'/'0'/''->T/F/''
     * @param string  $userEmail                 user email
     * @param string  $sClientID                 id of the client
     * @param string  $sAgencyID                 id of the agency
     * @param string  $sSellerID                 id of the seller
     * @param string  $invoiceStatusValues       invoice Status Values
     * @param string  $sentStatusValues          sent Status Values
     * @param string  $orderNumber               the order number
     * @param string  $payPlanId                 the id of the pay plan
     * @param string  $bookTypeId                the id of the book type
     * @param string  $aFirst                    the First record to return
     * @param string  $aLast                     the Last  record to return
     * @param boolean $noFetch                   true/false - if true the method
     *                                           will no fetch the result and will
     *                                           return empty array
     *
     * @return array of all invoices found
     * 
     */
    public function searchInvoice(&$asel, &$acount,
 $acontractDateBegin, $acontractDateCompareBegin,
 $acontractDateEnd, $acontractDateCompareEnd,
 $acreatedDateBegin, $acreatedDateCompareBegin,
 $acreatedDateEnd, $acreatedDateCompareEnd,
 $bookId, $campaignId, $productTypeId, $invoiceNumber, $isHandMade, $userEmail,
 $sClientID, $sAgencyID, $sSellerID,
 $invoiceStatusValues, $sentStatusValues, $orderNumber,
 $payPlanId, $bookTypeId, $aFirst, $aLast,
 $noFetch=false)
    {
        $bIsInnerRequest = &$GLOBALS['bIsInnerRequest'];

        if ($asel === '') {
            // Prepare the where clause.
            $where = SEARCH_INVOICES_WHERE;

            self::_addDateCond($acontractDateBegin, INVOICE_DATECONTRACT,
                $acontractDateCompareBegin, $where, '');

            self::_addDateCond($acontractDateEnd, INVOICE_DATECONTRACT,
                $acontractDateCompareEnd, $where, '');

            $dateAddedField  = 'TO_DATE(TO_CHAR('.INVOICE_DATEADDED.', \'';
            $dateAddedField .= DATE_FORMAT_SQL.'\'), \''.DATE_FORMAT_SQL.'\')';
            self::_addDateCond($acreatedDateBegin, $dateAddedField,
                $acreatedDateCompareBegin, $where, '');

            self::_addDateCond($acreatedDateEnd, INVOICE_DATEADDED,
                $acreatedDateCompareEnd, $where, '');

            CDBTools::AddConditionToWhereClause(INVOICE_PAYPLAN_ID,
                '=', CDBTools::ToDB($payPlanId), $where);

            self::_addIdCond($bookTypeId, BOOK_IDBOOKTYPE, $where);

            self::_addIdCond($campaignId, INVOICE_IDCAMPAIGN, $where);

            self::_addIdCond($bookId, GET_INVOICE_BOOKSECTION_ID, $where);

            CDBTools::AddConditionToWhereClause(INVOICE_INVOICENUMBER,
                '=', CDBTools::ToDB(strtoupper($invoiceNumber)), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_ORDERNUMBER,
                '=', CDBTools::ToDB($orderNumber), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_ISHANDMADE,
                '=', CDBTools::ToDB(strtoupper($isHandMade)), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_USERADDED,
                '=', CDBTools::ToDB($userEmail), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_IDCUSTOMER,
                '=', CDBTools::ToDB($sClientID), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_IDAGENCY,
                '=', CDBTools::ToDB($sAgencyID), $where);

            CDBTools::AddConditionToWhereClause(INVOICE_IDSELLER,
                '=', CDBTools::ToDB($sSellerID), $where);

            self::_addInvStatCond($invoiceStatusValues, $where);

            self::_addSntStatCond($sentStatusValues, $where);

            if ($productTypeId !== '') {
                $prodCond = sprintf(SIS_SEARCH_BY_PRODUCT_TYPE,
                    CDBTools::ToDB($productTypeId));
                CDBTools::AddConditionToWhereClause_($prodCond, $where);
            }

            $asel = base64_encode($where);
        } else {
            $where = base64_decode($asel);
        }//end if

        // Prepare the internal select.
        $selInternal = sprintf(SEARCH_INVOICES_SELECT_INTERNAL,
            SEARCH_INVOICES_ORDERBY, SEARCH_INVOICES_FROM, $where,
            SEARCH_INVOICES_ORDERBY);

        /**
         * Prepare the select for the count of all records
         *  according to the from and the where clauses.
         **/
        $selCount = sprintf(SEARCH_INVOICES_SELECT_COUNT, SEARCH_INVOICES_FROM,
            $where);

        $countAll = $this->oDB->MyDbQueryRevNullsAssoc($selCount);
        $acount   = $countAll[0][0];

        if ($aLast === 0) {
            $aLast = $acount;
        }

        // Prepare the main select with the page limits.
        $sel = sprintf(SEARCH_INVOICES_SELECT,
            $selInternal, $aFirst, $aLast);

        $res = array();
        if ($noFetch === false) {
            $res = $this->oDB->MyDbQueryRevNullsAssoc($sel);
        } else {
            $res = $this->oDB->MyDbExecute($sel, false, OCI_DEFAULT);
            if ($res === true) {
                $res = array();
            } else {
                $res = null;
            }
        }

        return $res;

    }//end searchInvoice()


    /**
     * function searchInvoiceFetchNext - fetches the next record from the
     *   last searchInvoice or any other select executed with no fetch
     *   
     * @return array of the invoice fields
     * 
     */
    public function searchInvoiceFetchNext()
    {
        return $this->oDB->fetchArray((OCI_NUM + OCI_RETURN_NULLS + OCI_ASSOC));

    }//end searchInvoiceFetchNext()


    /**
     * function searchInvoiceById - selects an invoice
     *
     * @param string $aId id of the invoice
     *   
     * @return array of the invoice fields
     * 
     */
    public function searchInvoiceById($aId)
    {
        $where = SEARCH_INVOICES_BY_ID_WHERE;

        self::_addIdCond($aId, INVOICE_ID, $where);

        $sel = sprintf(SEARCH_INVOICES_BY_ID_SELECT_INTERNAL,
            SEARCH_INVOICES_ORDERBY, SEARCH_INVOICES_BY_ID_FROM, $where,
            SEARCH_INVOICES_ORDERBY);

        return $this->oDB->MyDbQueryRevNullsAssoc($sel);

    }//end searchInvoiceById()


    /**
     * function getInvoiceNumber - returns the invoice number
     *
     * @param string $aId id of the invoice
     *   
     * @return string returns the invoice number
     * 
     */
    public function getInvoiceNumber($aId)
    {
        $sql = sprintf(SELECT_INVOICES_NUMBER_BY_ID, $aId);
        $res = $this->oDB->MyDbQueryRev($sql);

        return $res[0][0];

    }//end getInvoiceNumber()


    /**
     * function getPayPlanMonths - returns the number of months according to the
     *   pay plan
     *
     * @param string $aPayPlanId id of the invoice
     *   
     * @return integer returns the number of months according to the
     *   pay plan
     * 
     */
    public function getPayPlanMonths($aPayPlanId)
    {
        $sql = sprintf(SELECT_PAYPLAN_MONTHS, $aPayPlanId);
        $res = $this->oDB->MyDbQueryRev($sql);

        return intval($res[0][0]);

    }//end getPayPlanMonths()


    /**
     * function getManagementInvoiceID - returns the management invoice id or ''
     *
     * @param string $aSetupId id of the clear sense setup invoice
     *   
     * @return string returns the management invoice id or ''
     * 
     */
    public function getManagementInvoiceID($aSetupId)
    {
        $sql = sprintf(SELECT_MANAGEMENT_INVOICE_ID, $aSetupId);
        $res = $this->oDB->MyDbQueryRev($sql);

        if (count($res) > 0) {
            return strval($res[0][0]);
        } else {
            return '';
        }

    }//end getManagementInvoiceID()


    /**
     * function getManagementNextInvoiceID - returns the next management invoice
     *   id or ''
     *
     * @param string $aInvId id of the clear sense setup invoice
     *   
     * @return string returns the management invoice id or ''
     * 
     */
    public function getManagementNextInvoiceID($aInvId)
    {
        $sql = sprintf(SELECT_NEXT_MANAGEMENT_INVOICE_ID, $aInvId);
        $res = $this->oDB->MyDbQueryRev($sql);

        if (count($res) > 0) {
            return strval($res[0][0]);
        } else {
            return '';
        }

    }//end getManagementNextInvoiceID()


    /**
     * function searchInvoiceProductsById - selects the invoice products
     *
     * @param string $aId id of the invoice
     *   
     * @return array of all the invoice products
     * 
     */
    public function searchInvoiceProductsById($aId)
    {
        $sel = sprintf(SEARCH_INVOICES_PRODUCTS, $aId);

        return $this->oDB->MyDbQueryRevNullsAssoc($sel);

    }//end searchInvoiceProductsById()


    /**
     * function getBookType - returns the idbooktype of the book
     *
     * @param string $sIdBookSection Book section Id
     *   
     * @return string the idbooktype of the book
     * 
     */
    public function getBookType($sIdBookSection)
    {
        $sql = sprintf(SELECT_IDBOOKTYPE, $sIdBookSection);
        $res = $this->oDB->MyDbQueryRev($sql);
        return $res[0][0];

    }//end getBookType()


    /**
     * function savePayplanSendDate - save payplan send date,
     *  changes the payplan send date for a choosen clear sense management
     *  invoice and all management invoices after it
     *
     * @param string $idInvoice       Id of the invoice
     * @param string $payplanSendDate the new value of the send date
     *   
     * @return boolean true if no execution errors
     * 
     */
    public function savePayplanSendDate($idInvoice, $payplanSendDate)
    {
        $arrUzivatelOsUdaje = &$GLOBALS['arrUzivatelOsUdaje'];

        $workstation = $_SERVER['REMOTE_ADDR'];
        $username    = $arrUzivatelOsUdaje['email'];

        $payplanSendDate = self::_prapareDateParam($payplanSendDate, 'SYSDATE');

        $sql  = 'begin '.SCHEMA.".savePayplanSendDate(HEXTORAW('";
        $sql .= CDBTools::ToDB($idInvoice)."'), '";
        $sql .= CDBTools::ToDB($username)."', '";
        $sql .= CDBTools::ToDB($workstation)."', ";
        $sql .= $payplanSendDate;
        $sql .= '); end;';

        $errorCode = 0;
        unset($params);
        $params = array();

        $this->oDB->ExecuteWithBindings($sql, $params);

        return true;

    }//end savePayplanSendDate()


    /**
     * function getManagementCount - returns the count of management invoices
     *   existing for the given Clear Sense setup invoice
     *
     * @param string $csSetupInvoiceId the id of the Clear Sense setup invoice
     *   
     * @return string the count of management invoices
     * 
     */
    public function getManagementCount($csSetupInvoiceId)
    {
        $sql = sprintf(SELECT_MANAGEMENT_COUNT, $csSetupInvoiceId);
        $res = $this->oDB->MyDbQueryRev($sql);
        return $res[0][0];

    }//end getManagementCount()


}//end class

?>