Location: PHPKode > projects > TEMS -Time and Expense Management System > tems/invoices/PrintInvoiceReport.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/initialize.php");
require_once("$basedir/baseclass/DBCommonFunctions.php");
require_once("$basedir/baseclass/PageSection.php");
require_once("$basedir/expenseheaders/PrintCommonFunctions.php");

class dbObj extends DBCommonFunctions {}

class Report extends PageSection
{
	public function printReport()
	{
		$dateFormat = getUserDateFormat();
		
		$DbObj = new dbObj();
		$companyRec = $DbObj->getCompanyRec();

		$uid = $_GET['uid'];
		$headerRec = $this->getInvoiceHeaderRec($uid);

		$str = '';

		$str .= $this->makeInvoiceHeaderBlock($companyRec, $headerRec);
		$str .= $this->makeInvoiceDetailBlock($headerRec['invoices_id']);

		return $str;
	}


	protected function makeInvoiceHeaderBlock($companyRec, $headerRec)
	{
		$dateFormat = getUserDateFormat();

		$begindate = cvtDateIso2Dsp($headerRec['begindate'], $dateFormat);
		$enddate = cvtDateIso2Dsp($headerRec['enddate'], $dateFormat);

		$str = '';
		$str .= "<h1>".changeLiteral('Invoice')."</h1>";
		
		$str .= "<h2>{$companyRec['name']}<br />";
		$str .= "{$companyRec['address1']}";
		if ($companyRec['address2'] <> '')
			$str .= ", {$companyRec['address2']}";
		$str .= "<br />{$companyRec['city']}, {$companyRec['state']}-{$companyRec['postalcode']}</h2>";
		
		$str .= "<h3>".changeLiteral('Client').": {$headerRec['Account_name']}</h3>";
		if ($begindate == null
			or $enddate == null)
			$str .= "<h4>".changeLiteral('Period').": ".changeLiteral('Open Invoice')."</h4>";
		else
			$str .= "<h4>".changeLiteral('Period').": ".changeLiteral('From')." $begindate ".changeLiteral('to')." $enddate</h4>";

		return $str;
	}

	protected function makeInvoiceDetailBlock($invoices_id)
	{
		$dateFormat = getUserDateFormat();

		$summaryTimeRecs = $this->getInvoiceTimeSummaryRec($invoices_id);
		$summaryExpRecs = $this->getInvoiceExpSummaryRec($invoices_id);

		$currentProject = '';
		$totallBillAmount = 0;
		$totalExpAmount = 0;
		$grandlBillAmount = 0;
		$grandInvoiceAmont = 0;

		$str = '';

		$str .= "\n<table id = 'report'>\n";

		if (count($summaryTimeRecs) > 0)
		{
			$str .= "<tr>";
			$str .= "<td colspan=6><strong>".changeLiteral('Time Summary')."</strong></td>";
			$str .= "</tr>\n";
			$str .= "<tr>";
			$str .= "<td>".changeLiteral('Consultant')."</td><td>".changeLiteral('Billable')."<br />".changeLiteral('Hours')."</td><td>".changeLiteral('Non-billable')."<br />".changeLiteral('Hours')."</td><td>".changeLiteral('Total')."<br />".changeLiteral('Hours')."</td><td>".changeLiteral('Rate')."</td><td>".changeLiteral('Billed')."<br />".changeLiteral('Amount')."</td>";
			$str .= "</tr>\n";
			foreach ($summaryTimeRecs as $summaryTimeRec)
			{
				if ($currentProject <> $summaryTimeRec['Project_name'])
				{
					if ($currentProject <> '')
					{
						$str .= "<tr>";
						$str .= "<td colspan=4>&nbsp;</td>";
						$str .= "<td>{$totallBillAmount}</td>";
						$str .= "</tr>\n";
						$totallBillAmount = 0;
					}

					$str .= "<tr>";
					$str .= "<td colspan=6><strong>".changeLiteral('Project').": {$summaryTimeRec['Project_name']}</strong></td>";
					$str .= "</tr>\n";

					$str .= "<tr>";
					$str .= "<td>".changeLiteral('Consultant')."</td><td align='right'>".changeLiteral('Billable Hours')."</td><td align='right'>".changeLiteral('Non-billable Hours')."</td><td align='right'>".changeLiteral('Total Hours')."</td><td align='right'>".changeLiteral('Rate')."</td><td align='right'>".changeLiteral('Amount')."</td>";
					$str .= "</tr>\n";

					$currentProject = $summaryTimeRec['Project_name'];
				}

				$str .= "<tr>";
				$str .= "<td>{$summaryTimeRec['user_name']}</td>";
				$str .= "<td align='right'>{$summaryTimeRec['billablehours']}</td>";
				$str .= "<td align='right'>{$summaryTimeRec['nonbillablehours']}</td>";
				$str .= "<td align='right'>{$summaryTimeRec['actualhours']}</td>";
				$str .= "<td align='right'>{$summaryTimeRec['rate']}</td>";
				$billamount = number_format($summaryTimeRec['billamount'], 2);
				$str .= "<td align='right'>$billamount</td>";
				$str .= "</tr>\n";
				$totallBillAmount += $summaryTimeRec['billamount'];
				$grandlBillAmount += $summaryTimeRec['billamount'];
				$grandInvoiceAmont += $summaryTimeRec['billamount'];
			}
		}

		$totallBillAmount = number_format($totallBillAmount, 2);
		$str .= "<tr>";
		$str .= "<td colspan=5><strong>".changeLiteral('Project Total')."</strong></td>";
		$str .= "<td align='right'>$totallBillAmount</td>";
		$str .= "</tr>\n";

		$grandlBillAmount = number_format($grandlBillAmount, 2);
		$str .= "<tr>";
		$str .= "<td colspan=5><strong>".changeLiteral('All projects Total')."</strong></td>";
		$str .= "<td align='right'>$grandlBillAmount</td>";
		$str .= "</tr>\n";

		If (Count($summaryExpRecs) > 0)
		{
			$str .= "<tr>";
			$str .= "<td colspan=6><strong>".changeLiteral('Expense Summary')."</strong></td>";
			$str .= "</tr>\n";
			$str .= "<tr>";
			$str .= "<td colspan=2>".changeLiteral('Consultant')."</td><td>".changeLiteral('From')."</td><td>".changeLiteral('To (W/E)')."</td><td colspan=2 align='right'>".changeLiteral('Amount')."</td>";
			$str .= "</tr>\n";

			foreach ($summaryExpRecs as $summaryExpRec)
			{
				$str .= "<tr>";
				$str .= "<td colspan=2>{$summaryExpRec['user_name']}</td>";
				$weekBeginDate = tesAddDate($summaryExpRec['weekendDate'], -6);
				$weekBeginDate = cvtDateIso2Dsp($weekBeginDate, $dateFormat);
				$str .= "<td>$weekBeginDate</td>";
				$weekendDate = cvtDateIso2Dsp($summaryExpRec['weekendDate'], $dateFormat);
				$str .= "<td>$weekendDate</td>";
				$expAmount = number_format($summaryExpRec['expAmount'], 2);
				$str .= "<td colspan=2 align='right'>$expAmount</td>";
				$str .= "</tr>\n";

				$grandInvoiceAmont += $summaryExpRec['expAmount'];
			}
		}


		$grandInvoiceAmont = number_format($grandInvoiceAmont, 2);
		$str .= "<tr>";
		$str .= "<td colspan=4><strong>".changeLiteral('Invoice Total')."</strong></td>";
		$str .= "<td colspan=2 align='right'><strong>$grandInvoiceAmont</strong></td>";
		$str .= "</tr>\n";

		$str .= "</table>";
		$str .= "\f <br>";

		// End of Invoice summary ---- xxxxx ----

		// Print detail time records

		$groupTimeRecs = $this->getInvoiceTimeGroupRec($invoices_id);

		if (count($groupTimeRecs) == 0)
			return $str;

		$str .= '<p class="breakhere" />';
		$str .= "<h1>".changeLiteral('Time Details')."</h1>";

		$str .= "\n<table id = 'report'>";

		foreach ($groupTimeRecs as $groupTimeRec)
		{
			$detailTimeRecs = $this->getInvoiceTimeDetailRec($invoices_id, $groupTimeRec['user_id']);

			$str .= "<tr>";
			$str .= "<td><strong>".changeLiteral('Consultant')."</strong></td>";
			$str .= "<td colspan=5><strong>{$groupTimeRec['user_name']}</strong></td>";
			$str .= "</tr>\n";

			$str .= "<tr>";
			$str .= "<td>".changeLiteral('Date')."</td>";
			$str .= "<td>".changeLiteral('Billable Hours')."</td>";
			$str .= "<td>".changeLiteral('Non-billable Hours')."</td>";
			$str .= "<td>".changeLiteral('Total Hours')."</td>";
			$str .= "<td>".changeLiteral('Billed Amount')."</td>";
			$str .= "<td>".changeLiteral('Description')."</td>";
			$str .= "</tr>\n";

			foreach ($detailTimeRecs as $detailTimeRec)
			{
				$str .= "<tr>";
				$workDate = cvtDateIso2Dsp($detailTimeRec['workdate'], $dateFormat);
				$str .= "<td>$workDate</td>";
				$str .= "<td align='right'>{$detailTimeRec['billablehours']}</td>";
				$str .= "<td align='right'>{$detailTimeRec['nonbillablehours']}</td>";
				$str .= "<td align='right'>{$detailTimeRec['actualhours']}</td>";
				$str .= "<td align='right'>{$detailTimeRec['billamount']}</td>";
				$str .= "<td>{$detailTimeRec['description']}</td>";
				$str .= "</tr>\n";
			}

			$str .= "<tr>";
			$str .= "<td>".changeLiteral('Total')."</td>";
			$str .= "<td align='right'>{$groupTimeRec['billablehours']}</td>";
			$str .= "<td align='right'>{$groupTimeRec['nonbillablehours']}</td>";
			$str .= "<td align='right'>{$groupTimeRec['actualhours']}</td>";
			$str .= "<td align='right'>{$groupTimeRec['billamount']}</td>";
			$str .= "<td>&nbsp;</td>";
			$str .= "</tr>\n";
		}
		$str .= "</table>";
		$str .= "\f <br> <br>";

		return $str;
	}

	protected function getInvoiceHeaderRec($uid)
	{
		$DbObj = new dbObj();
		$query = "select ih.invoices_id as invoices_id,
						 ih.accounts_id as billaccount_id,
						 ac.name as Account_name,
						 ih.begindate as begindate,
						 ih.enddate as enddate
				from	 invoiceheaders ih, accounts ac
				where	 ih.uid = $uid
				  and	 ih.accounts_id = ac.accounts_id";

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


	protected function getInvoiceTimeSummaryRec($invoices_id)
	{
		$DbObj = new dbObj();
		$query = "select i.projects_id as projects_id,
						 i.project_name as project_name,
						 i.users_id as user_id,
						 i.User_name as user_name,
						 i.rate as rate,
						 sum(i.billablehours) as billablehours,
						 sum(i.nonbillablehours) as nonbillablehours,
						 sum(i.actualhours) as actualhours,
						 sum(i.billamount) as billamount
					from invoicetimedetails i
				   where i.invoices_id = '$invoices_id'
				group by i.projects_id, i.users_id, i.rate
				order by i.projects_id, i.user_name";

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

	protected function getInvoiceTimeGroupRec($invoices_id)
	{
		$DbObj = new dbObj();
		$query = "select i.projects_id as projects_id,
						 i.Project_name as Project_name,
						 i.users_id as user_id,
						 i.User_name as user_name,
						 i.rate as rate,
						 sum(i.billablehours) as billablehours,
						 sum(i.nonbillablehours) as nonbillablehours,
						 sum(i.actualhours) as actualhours,
						 sum(i.billamount) as billamount
					from invoicetimedetails i
				   where i.invoices_id = '$invoices_id'
				group by i.projects_id, i.users_id
				order by i.projects_id, i.user_name";

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

	public function getInvoiceExpSummaryRec($invoices_id)
	{
		$DbObj = new dbObj();
		$query = "select i.users_id as user_id,
						 i.User_name as user_name,
						 i.weekenddate as weekendDate,
						 sum(i.amount) as expAmount,
						 i.Expense_uid as Expense_uid
					from invoiceexpensedetails i
				   where i.invoices_id = '$invoices_id'
				group by i.users_id, i.weekenddate
				order by i.user_name, i.weekenddate";

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


	protected function getInvoiceTimeDetailRec($invoices_id, $users_id)
	{
		$DbObj = new dbObj();
		$query = "select i.workdate as workdate,
						 i.billablehours as billablehours,
						 i.nonbillablehours as nonbillablehours,
						 i.actualhours as actualhours,
						 i.billamount as billamount,
						 i.description as description
					from invoicetimedetails i
				   where i.invoices_id = '$invoices_id'
				     and i.users_id = '$users_id'
				order by i.workdate";

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


}

class ExpReport extends ExpenseReportCommon
{
	public function printReport()
	{
		$str = '';

		$uid = $_GET['uid'];
		$DbObj = new dbObj();
		$query = "select ih.invoices_id as invoices_id
				from	 invoiceheaders ih
				where	 ih.uid = $uid";

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

		$SummaryReport = new Report();
		$summaryExpRecs = $SummaryReport->getInvoiceExpSummaryRec($dataset[0]['invoices_id']);
		foreach ($summaryExpRecs as $summaryExpRec)
		{
			$where = "uid = '{$summaryExpRec['Expense_uid']}'";
			$EH = $DbObj->fetchRowbyWhereClause('expenseheaders', $where);

			$str .= '<p class="breakhere" />';
			$str .= $this->makeHeaderBlock($EH);
			$str .= "\n<table id = 'report'>";
			$str .= $this->makeColHeading($EH);
			$str .= $this->makeDetailBlock($EH);
			$str .= "\n</table>";
		}
		return $str;
	}
}

$report = new Report();
$HtmlHead = $report->getReportHtmlHead();
$HtmlBody = $report->printReport();

$ExpReport = new ExpReport();
$ExpenseReport = $ExpReport->printReport();

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<?php echo $HtmlHead; ?>

<body>

<?php echo $HtmlBody; ?>
<?php echo $ExpenseReport; ?>
 			
<input type='button' value='Print' onclick='window.print()'>
<!-- <input type='button' value='Create PDF' onclick='createPDF()'>  -->
<input type='button' value='Go Back' onclick='window.history.go(-1)'>

</body>

</html>
Return current item: TEMS -Time and Expense Management System