Location: PHPKode > projects > TEMS -Time and Expense Management System > tems/invoices/DbObj.php
<?php

/* * *******************************************************************************
 * TES is a Time and Expense Management program developed by
 * Initechs, LLC. Copyright (C) 2009 - 2010 Initechs LLC.
 *
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 3 as published by the
 * Free Software Foundation with the addition of the following permission added
 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
 * IN WHICH THE COPYRIGHT IS OWNED BY INITECHS, INITECHS DISCLAIMS THE WARRANTY
 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, see http://www.gnu.org/licenses or write to the Free
 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 *
 * You can contact Initechs headquarters at 1841 Piedmont Road, Suite 301,
 * Marietta, GA, USA. or at email address hide@address.com
 *
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 *
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the display od the "Initechs" logo.
 * If the display of the logo is not reasonably feasible for technical reasons,
 * the Appropriate Legal Notices must display the words "Powered by Initechs".
 * ****************************************************************************** */

$basedir = dirname(__FILE__) . '/..';
require_once("$basedir/baseclass/DBCommonFunctions.php");
require_once("$basedir/baseclass/GetAuthorization.php");

class InvoiceData extends DBCommonFunctions {

    public function CreateInvoice($AccountId) {

        if ($AccountId == '')
            throw new iInvalidArgumentException();

        $recid = '';
        $invoicePeriod = array(
                        'begindate' => null,
                        'enddate' => null);
        
        $Data = new dbObj();

        $sAccountId = $this->escapeString($AccountId);
        $InvoiceId = "@OpenInv" . $sAccountId;
        $loggedinUser = loggedUserID();

        do {
            $invoicePeriod = $this->findInvoicePeriod($sAccountId, $invoicePeriod['enddate']);
            if ($invoicePeriod['enddate'] == null)
                throw new iBLError('nocategory', 'er0077');;

            $where = "invoices_id = '$InvoiceId' and weekenddate <= '{$invoicePeriod['enddate']}' and status = '30'";
            $expRecs = $Data->countRows('expenseheaders', $where);
            $TimeRecs = $Data->countRows('times', $where);
        } while (($expRecs + $TimeRecs) == 0);

        //See if any time or expense records are not approved and not on hold.

        $errorid = $this->checkUnapprovedEntry($sAccountId, $invoicePeriod['enddate']);
        if ($errorid <> null)
            throw new iBLError('nocategory', $errorid);

        $this->beginTransaction();
        try {

            // List projects that are billed separately and call invoicing

            $query = "Select projects_id
                        FROM projects
                       WHERE billtoaccount_id = '$sAccountId'
                         and separate_billing = '1'";

            $RowData = $Data->getDatabyQuery($query);

            foreach ($RowData as $Row){
                $this->CreateNewInvoice($sAccountId, $invoicePeriod, $Row['projects_id']);
            }

            $recid = $this->CreateNewInvoice($sAccountId, $invoicePeriod);  // For all projects those are billed together

            // Update accounts
            $query = "UPDATE accounts
                      SET
			lastbilldate = '{$invoicePeriod['enddate']}',
			changeat = now(),
			changeby = '$loggedinUser'
                    WHERE accounts_id = '$sAccountId'";

            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $this->commitTransaction();
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }

        $this->DeleteOpenInvoice($sAccountId, $InvoiceId);

        return $recid;
    }

    protected function CreateNewInvoice($AccountId, $invoicePeriod, $projects_id='') {

        // First check if there is any record exist in this time period to be invoiced
        $Data = new dbObj();

        $where = " weekenddate <= '{$invoicePeriod['enddate']}' and status = '30'";
        if ($projects_id != null)
            $where .= " and projects_id = '$projects_id'";

        if ($Data->countRows('times', $where) + $Data->countRows('expenseheaders', $where) == 0)
            return null;

        // Elligigle record exists to be invoiced -- so proceed further

        $loggedinUser = loggedUserID();
        $InvoiceId = "@OpenInv" . $AccountId;
        
        // Determine the next invoice number.
        $newInvoiceid = $this->nextInvoiceNumber($AccountId);

        // Write new Invoice Header
        $query = "INSERT INTO invoiceheaders (accounts_id, invoices_id, begindate,
                                              enddate, invoicedate, status,
                                              createat, createby, changeat, changeby)
				VALUES('$AccountId', '$newInvoiceid', '{$invoicePeriod['begindate']}',
                                       '{$invoicePeriod['enddate']}', curdate(), '90',
                                       now(), '$loggedinUser', now(), '$loggedinUser')";

        $conn = $this->getConnection();
        $conn->query($query);
        $recid = $conn->insert_id;
        $this->chkQueryError($conn, $query);

        //update times records

        $query = "UPDATE times t
                        left join users_projects_roles upr
                             on t.users_id = upr.users_id
                                and t.projects_id = upr.projects_id
			left join projects_roles pr
                             on upr.projects_id = pr.projects_id
                                and upr.roles_id = pr.roles_id
                                and pr.effective_date <= t.workdate
                                and pr.effective_date = (select max(pr1.effective_date) from projects_roles pr1
                                                                where pr.projects_id = pr1.projects_id
                                                                  and pr.roles_id = pr1.roles_id
                                                                  and pr1.effective_date <= t.workdate)
			SET
				t.invoices_id = '$newInvoiceid',
				t.rate = pr.rate,
				t.status = '90',
				t.changeat = now(),
				t.changeby = '$loggedinUser'
			WHERE t.invoices_id = '$InvoiceId'
			  and t.workdate <= '{$invoicePeriod['enddate']}'
			  and t.status = '30'";

        if ($projects_id != null)
            $query = $query . " and t.projects_id = '$projects_id'";

        $conn = $this->getConnection();
        $conn->query($query);
        $this->chkQueryError($conn, $query);

        // Update expense Header

        $query = "UPDATE expenseheaders
			 SET
                            invoices_id = '$newInvoiceid',
                            InvoiceDate = curdate(),
                            status = '90',
                            changeat = now(),
                            changeby = '$loggedinUser'
                    WHERE invoices_id = '$InvoiceId'
                        and weekenddate <= '{$invoicePeriod['enddate']}'
                        and status = '30'";

        if ($projects_id != null)
            $query = $query . " and projects_id = '$projects_id'";

        $conn = $this->getConnection();
        $conn->query($query);
        $this->chkQueryError($conn, $query);

        // Update expense Details

        $query = "UPDATE expensedetails ED
                        SET
                            ED.invoices_id = '$newInvoiceid',
                            ED.changeat = now(),
                            ED.changeby = '$loggedinUser'
                      WHERE exists (select * from expenseheaders EH
                                    where EH.users_id = ED.users_id
                                        and EH.projects_id = ED.projects_id
                                        and EH.weekenddate = ED.weekenddate
                                        and EH.invoices_id = '$newInvoiceid')";

        $conn = $this->getConnection();
        $conn->query($query);
        $this->chkQueryError($conn, $query);

        return $recid;
    }

    public function createOpenInvoice($billToAccountId) {

        $user = loggedUserID();

        $DbObj = new dbObj();
        $openInvoiceId = '@OpenInv' . $billToAccountId;

        //See if open Invoice record exist or not, if not create one

        $whereclause = "invoices_id = '$openInvoiceId'";
        $rowData = $DbObj->fetchRowbyWhereClause('invoiceheaders', $whereclause);

        If ($rowData == null) {
            $this->beginTransaction();
            try {
                $query = "INSERT INTO invoiceheaders (accounts_id, invoices_id, status, createat, createby, changeby)
                                        VALUES ('$billToAccountId', '$openInvoiceId', '10', now(), '$user', '$user')";

                $conn = $this->getConnection();
                $conn->query($query);
                $this->chkQueryError($conn, $query);

                $this->commitTransaction();
            } catch (Exception $e) {
                $this->rollbackTransaction();
                throw $e;
            }
        }
        return $openInvoiceId;
    }


    public function DeleteOpenInvoice($Accountid, $InvoiceId) {

        $loggedinUser = loggedUserID();

        //See if any time and expenses records exists.

        $where = "invoices_id = '$InvoiceId'";
        $DbObj = new dbObj();

        $RowData = $DbObj->fetchRowbyWhereClause('invoicetimedetails', $where);
        $OpenTimeData = $RowData <> null ? 'Yes' : 'No';

        $RowData = $DbObj->fetchRowbyWhereClause('invoiceexpensedetails', $where);
        $OpenExpenseDetail = $RowData <> null ? 'Yes' : 'No';

        $RowData = $DbObj->fetchRow('expenseheaders', 'invoices_id', $InvoiceId);
        $OpenExpenseHeader = $RowData <> null ? 'Yes' : 'No';

        if (($OpenTimeData == 'No') and ($OpenExpenseDetail == 'No') and ($OpenExpenseHeader == 'No')) {
            $where = "accounts_id = '$Accountid' and invoices_id = '$InvoiceId'";
            $RowData = $DbObj->fetchRowbyWhereClause('invoiceheaders', $where);
            if ($RowData <> null) {
                $uid = $RowData['uid'];
                $this->deleteRow('invoiceheaders', $uid);
            }
        }
    }

    public function checkUnapprovedEntry($AccountId, $invoiceEndDate) {

        $DbObj = new dbObj();
        $where = "billaccount_id = '$AccountId' and weekenddate <= '{$invoiceEndDate}' and status < '30'";
        $RowData = $DbObj->fetchRowbyWhereClause('invoicetimedetails', $where);
        $UneligibleTimeData = $RowData <> null ? 'Yes' : 'No';

        $where = "billaccount_id = '$AccountId' and weekenddate <= '{$invoiceEndDate}' and status < '30'";
        $RowData = $DbObj->fetchRowbyWhereClause('invoiceexpensedetails', $where);
        $UneligibleExpenseDetail = $RowData <> null ? 'Yes' : 'No';

        $where = "billaccount_id = '$AccountId' and weekenddate <= '{$invoiceEndDate}' and status < '30'";
        $RowData = $DbObj->fetchRowbyWhereClause('invoiceexpenseheaders', $where);
        $UneligibleExpenseHeader = $RowData <> null ? 'Yes' : 'No';


        if ($UneligibleTimeData == 'Yes')
            return 'er0073';
        if ($UneligibleExpenseDetail == 'Yes')
            return 'er0057';
        if ($UneligibleExpenseHeader == 'Yes')
            return 'er0057';

        return null;
    }

    protected function findInvoicePeriod($accounts_id, $lastBillDate=null) {

        $weekendday = $_SESSION['company']['weekendday'];
        $invoicePeriod = array('begindate' => '',
            'enddate' => '');

        $DbObj = new dbObj();
        $query = "select ac.lastbilldate,
			 ac.billcycle,
			 ac.createat
                    from accounts ac
		   where ac.accounts_id = '$accounts_id'";

        $dataset = $DbObj->getDatabyQuery($query);
        if ($dataset == null)
            return null;

        if ($lastBillDate <> null)
            $dataset[0]['lastbilldate'] = $lastBillDate;

        if ($dataset[0]['lastbilldate'] == null)
            $dataset[0]['lastbilldate'] = tesAddDate($dataset[0]['createat'], -1);

        $invoicePeriod['begindate'] = tesAddDate($dataset[0]['lastbilldate'], 1);

        $dataset[0]['billcycle'] = strtolower($dataset[0]['billcycle']);

        switch ($dataset[0]['billcycle']) {
            case 'weekly':
                $invoicePeriod['enddate'] = getWeekEndDate($invoicePeriod['begindate'], $weekendday);
                break;

            case 'bi-weekly':
                $invoicePeriod['enddate'] = getWeekEndDate($invoicePeriod['begindate'], $weekendday);
                $invoicePeriod['enddate'] = tesAddDate($invoicePeriod['enddate'], 7);
                break;

            case 'fortnightly':
                $invoicePeriod['enddate'] = getBiMonthlyDate($invoicePeriod['begindate'], 'Next');
                break;

            case 'monthly':
                $invoicePeriod['enddate'] = getMonthEnd($invoicePeriod['begindate'], 'Next');
                break;

            default:
                $invoicePeriod['enddate'] = getMonthEnd($invoicePeriod['begindate'], 'Next');
                break;
        }
        $today = tesAddDate('today');
        if ($invoicePeriod['enddate'] > $today)
            $invoicePeriod['enddate'] = null;

        return $invoicePeriod;
    }

    public function DeleteInvoice($uid) {

        if ($uid == '')
            throw new iInvalidArgumentException();

        $loggedinUser = loggedUserID();

        $suid = $this->escapeString($uid);

        $DbObj = new dbObj();
        $InvRec = $DbObj->fetchRow('invoiceheaders', 'uid', $suid);

        $this->beginTransaction();
        try {
            // Create Open Invoice

            $openInvoiceId = $this->createOpenInvoice($InvRec['accounts_id']);

            // Update expense Details

            $query = "UPDATE expensedetails ED
			 SET
                             ED.invoices_id = '$openInvoiceId',
                             ED.changeat = now(),
                             ED.changeby = '$loggedinUser'
		       WHERE ED.invoices_id = '{$InvRec['invoices_id']}'";
            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            // Update expense Header

            $query = "UPDATE expenseheaders
			 SET
                             invoices_id = '$openInvoiceId',
                             InvoiceDate = null,
                             status = '30',
                             changeat = now(),
                             changeby = '$loggedinUser'
                       WHERE invoices_id = '{$InvRec['invoices_id']}'";
            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            //Update times records

            $query = "UPDATE times t
			 SET
                            t.invoices_id = '$openInvoiceId',
                            t.status = '30',
                            t.changeat = now(),
                            t.changeby = '$loggedinUser'
                      WHERE t.invoices_id = '{$InvRec['invoices_id']}'";
            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $endDate = $this->findLastInvoicePeriod($InvRec['invoices_id'], $InvRec['accounts_id']);

            // Update accounts

            if ($endDate != null) {
                $query = "UPDATE accounts
                             SET
                                 lastbilldate = '$endDate',
                                 changeat = now(),
                                 changeby = '$loggedinUser'
                           WHERE accounts_id = '{$InvRec['accounts_id']}'";
                $conn = $this->getConnection();
                $conn->query($query);
                $this->chkQueryError($conn, $query);
            }

            // Delete Invoice Header

            $query = "DELETE From invoiceheaders WHERE invoices_id = '{$InvRec['invoices_id']}'";
            $conn = $this->getConnection();
            $conn->query($query);
            $this->chkQueryError($conn, $query);

            $this->commitTransaction();
        } catch (Exception $e) {
            $this->rollbackTransaction();
            throw $e;
        }
    }

    protected function findLastInvoicePeriod($invoices_id, $accounts_id) {

        $DbObj = new dbObj();

        // If this is not the last invoice of the account, we cannot change the last invoice date, hence send null.

        $query = "SELECT max(cast(invoices_id as unsigned)) as lastinv
                    FROM invoiceheaders
                   where accounts_id = '$accounts_id'
                     and invoices_id not like '@%'";

        $dataset = $DbObj->getDatabyQuery($query);

        if ($dataset[0]['lastinv'] == null)
            return null;

        if (sprintf("%05s", $dataset[0]['lastinv']) != sprintf("%05s", $invoices_id))
            return null;

        // Find the last bill date of the last invoice of this account prior to this invoice.
        $query = "SELECT max(enddate) as newlastbilldate
                    FROM invoiceheaders
                   where accounts_id = '$accounts_id'
                     and invoices_id < '$invoices_id'
                     and invoices_id not like '@%'";

        $dataset = $DbObj->getDatabyQuery($query);

        if ($dataset[0]['newlastbilldate'] == null) {    // Make last bill date as one day prior to account create date
            $query = "SELECT date_sub(createat, INTERVAL 1 DAY) as newlastbilldate
                        FROM accounts
                       where accounts_id = '$accounts_id'";

            $dataset = $DbObj->getDatabyQuery($query);
        }
        if ($dataset[0]['newlastbilldate'] == null)
            return null;

        return $dataset[0]['newlastbilldate'];
    }

    protected function nextInvoiceNumber($AccountId) {

        // Determine the next invoice number.

        $Data = new dbObj();
        $query = "select max(cast(invoices_id as unsigned)) as lastInvoiceId
                    from invoiceheaders
		   where invoices_id not like '@%'";
        $RowData = $Data->getDatabyQuery($query);
        $newInvoiceid = $RowData[0]['lastInvoiceId'] == null ? 1 : $RowData[0]['lastInvoiceId'] + 1;
        $newInvoiceid = sprintf("%05s", $newInvoiceid);

        return $newInvoiceid;
    }


}

?>
Return current item: TEMS -Time and Expense Management System