<?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;
}
}
?>