function _showOpenARSelect($clientid, $type)
{
$queryType = $type;
if ($type == "credit") {
$queryType = "credit";
}
$querystatement = "\n\t\t\t\tSELECT\n\t\t\t\t\t`aritems`.`id`,\n\t\t\t\t\t`aritems`.`uuid`,\n\t\t\t\t\t`aritems`.`amount`,\n\t\t\t\t\t`aritems`.`paid`,\n\t\t\t\t\t`aritems`.`relatedid`,\n\t\t\t\t\t`aritems`.`itemdate`,\n\t\t\t\t\t`invoices`.`id` AS `invoiceid`\n\t\t\t\tFROM\n\t\t\t\t\t`aritems` LEFT JOIN `invoices` ON `aritems`.`relatedid`=`invoices`.`uuid`\n\t\t\t\tWHERE\n\t\t\t\t\t`aritems`.`status` = 'open'\n\t\t\t\t\tAND\n\t\t\t\t\t`aritems`.`clientid` = '" . mysql_real_escape_string($clientid) . "'\n\t\t\t\t\tAND\n\t\t\t\t\t`aritems`.`type` = '" . $queryType . "'\n\t\t\t\t\tAND\n\t\t\t\t\t`aritems`.`posted` = '1'\n\t\t\t\tORDER BY\n\t\t\t";
if ($type == "credit") {
$querystatement .= "itemdate";
} else {
$querystatement .= "relatedid";
}
$queryresult = $this->db->query($querystatement);
if ($this->db->numRows($queryresult)) {
?>
<select id="newItem<?php
echo str_replace(" ", "", ucwords($type));
?>
ARID">
<?php
while ($therecord = $this->db->fetchArray($queryresult)) {
?>
<option value="<?php
echo $therecord["uuid"];
?>
"><?php
if ($therecord["invoiceid"]) {
echo $therecord["invoiceid"] . ": ";
}
echo formatFromSQLDate($therecord["itemdate"]) . " ";
echo formatVariable($therecord["amount"], "currency");
if ($therecord["paid"] != 0) {
echo " (" . formatVariable((double) $therecord["amount"] - (double) $therecord["paid"], "currency") . ")";
}
?>
</option><?php
}
//endwhile
?>
</select><?php
} else {
?>
<p class="disabledtext">No existing open <?php
echo $type;
?>
AR items found for client.</p><?php
}
// endif
}
function _getItems()
{
$querystatement = "\n\t\t\tSELECT\n\t\t\t\t`aritems`.`id`,\n\t\t\t\t`aritems`.`type`,\n\t\t\t\tIF(`invoices`.`id`,`invoices`.`id`,`receipts`.`id`) AS `relatedid`,\n\t\t\t\t`aritems`.`itemdate`,\n\t\t\t\t`aritems`.`amount`,\n\t\t\t\t`aritems`.`amount` - `paid` AS due\n\t\t\tFROM\n\t\t\t\t(`aritems` LEFT JOIN `invoices` ON `aritems`.`relatedid` = `invoices`.`uuid`) LEFT JOIN `receipts` ON `aritems`.`relatedid` = `receipts`.`uuid`\n\t\t\tWHERE\n\t\t\t\t`aritems`.posted = 1\n\t\t\t\tAND `aritems`.clientid = '" . mysql_real_escape_string($this->clientrecord["clientid"]) . "'\n\t\t";
if (!$this->showClosed) {
$querystatement .= "\n\t\t\t\tAND `aritems`.`status` = 'open'";
}
$querystatement .= "\n\t\t\t\tAND `aritems`.itemdate <= '" . dateToString($this->statementDate, "SQL") . "'\n\t\t\tORDER BY\n\t\t\t\t`aritems`.itemdate,\n\t\t\t\t`aritems`.id";
$queryresult = $this->db->query($querystatement);
$returnArray = array();
$serviceChargeAmount = 0;
while ($therecord = $this->db->fetchArray($queryresult)) {
if ($therecord["type"] == "service charge") {
$serviceChargeAmount += $therecord["due"];
} else {
$itemdate = stringToDate($therecord["itemdate"], "SQL");
if ($therecord["type"] != "invoice") {
$therecord["dayspd"] = "";
} else {
$daysover = floor(($this->statementDate - $itemdate) / 86400) - TERM1_DAYS;
if ($daysover + TERM1_DAYS > TERM3_DAYS) {
$this->term3Total += $therecord["due"];
} elseif ($daysover + TERM1_DAYS > TERM2_DAYS) {
$this->term2Total += $therecord["due"];
} elseif ($daysover > 0) {
$this->term1Total += $therecord["due"];
} else {
$this->currentTotal += $therecord["due"];
$daysover = "";
}
//end if
if ($therecord["due"] > 0) {
$therecord["dayspd"] = $daysover;
} else {
$therecord["dayspd"] = "(paid in full)";
}
}
//endif
$keydate = $therecord["itemdate"];
$therecord["itemdate"] = formatFromSQLDate($therecord["itemdate"]);
$therecord["amount"] = formatVariable($therecord["amount"], "currency");
$therecord["due"] = formatVariable($therecord["due"], "currency");
$returnArray[$keydate . "-" . $therecord["id"]] = $therecord;
}
//end if
}
//endwhile
if ($serviceChargeAmount) {
$serviceRecord["itemdate"] = dateToString($this->statementDate);
$serviceRecord["dayspd"] = "";
$serviceRecord["type"] = "service charges";
$serviceRecord["relatedid"] = "";
$serviceRecord["amount"] = formatVariable($serviceChargeAmount, "currency");
$serviceRecord["due"] = formatVariable($serviceChargeAmount, "currency");
$this->currentTotal += $serviceChargeAmount;
$returnArray[dateToString($this->statementDate, "SQL") . "-XXX"] = $serviceRecord;
}
//endif
if ($this->showPayments) {
//add in receipts in laste term1_days
$lastDays = dateToString(strtotime("-" . TERM1_DAYS . " days", $this->statementDate), "SQL");
$querystatement = "SELECT\n\t\t\t\tid,\n\t\t\t\treceiptdate,\n\t\t\t\tamount\n\t\t\tFROM\n\t\t\t\treceipts\n\t\t\tWHERE\n\t\t\t\tclientid = '" . mysql_real_escape_string($this->clientrecord["clientid"]) . "'\n\t\t\t\tAND receiptdate <= '" . dateToString($this->statementDate, "SQL") . "'\n\t\t\t\tAND receiptdate >= '" . $lastDays . "'\n\t\t\t\tAND posted = 1";
$receiptresult = $this->db->query($querystatement);
while ($receiptrecord = $this->db->fetchArray($receiptresult)) {
$rcptRecord["itemdate"] = formatFromSQLDate($receiptrecord["receiptdate"]);
$rcptRecord["dayspd"] = "";
$rcptRecord["type"] = "payment";
$rcptRecord["relatedid"] = "";
$rcptRecord["amount"] = "(" . formatVariable($receiptrecord["amount"], "currency") . ")";
$rcptRecord["due"] = "";
$returnArray[$receiptrecord["receiptdate"] . "-D" . $receiptrecord["id"]] = $rcptRecord;
}
//endwhile
}
//end if
ksort($returnArray);
return $returnArray;
}
function showTasks($type)
{
$querystatement = "\n\t\t\tSELECT\n\t\t\t\tid,\n\t\t\t\ttype,\n\t\t\t\tsubject,\n\t\t\t\tcompleted,\n\t\t\t\tif(enddate < CURDATE(),1,0) AS ispastdue,\n\t\t\t\tif(assignedtodate < CURDATE(),1,0) AS ispastassigneddate,\n\t\t\t\tstartdate,\n\t\t\t\tenddate,\n\t\t\t\tassignedtodate,\n\t\t\t\tprivate,\n\t\t\t\tassignedbyid,\n\t\t\t\tassignedtoid,\n\t\t\t\tIF(assignedtodate IS NOT NULL, assignedtodate, IF((enddate IS NOT NULL && type = 'TS'), enddate, IF((startdate IS NOT NULL && type = 'EV'), startdate, CURDATE()))) AS xdate\n\t\t\tFROM\n\t\t\t\tnotes\n\t\t\tWHERE";
switch ($type) {
case "ReceivedAssignments":
$querystatement .= "\n\t\t\t\t\t((\n\t\t\t\t\t\tassignedtoid = '" . $this->useruuid . "'\n\t\t\t\t\t\tOR \t(\n\t\t\t\t\t\t\ttype = 'TS'\n\t\t\t\t\t\t\tAND (assignedtoid = '' OR assignedtoid IS NULL)\n\t\t\t\t\t\t\tAND createdby = " . $this->userid . "\n\t\t\t\t\t\t\t)\n\t\t\t\t\t)\n\t\t\t\t\t\tAND \t(\n\t\t\t\t\t\t\tcompleted = 0\n\t\t\t\t\t\t\tOR \t(\n\t\t\t\t\t\t\t\tcompleted = 1\n\t\t\t\t\t\t\t\tAND completeddate >= CURDATE()\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t)\n\t\t\t\t\t)";
$title = "Assignments";
$id = "AS";
break;
case "GivenAssignments":
$querystatement .= "\n\t\t\t\t\t(assignedbyid = '" . $this->useruuid . "'\n\t\t\t\t\tAND (completed = 0\n\t\t\t\t\t\tOR (completed = 1 AND completeddate >= CURDATE())\n\t\t\t\t\t))";
$title = "Delegations";
$id = "DG";
break;
}
//endswitch
$querystatement .= "AND (\n\t\t\t\t\t(startdate IS NULL AND enddate IS NULL AND assignedtodate IS NULL)\n\t\t\t\t\tOR (startdate IS NOT NULL AND startdate <= DATE_ADD(CURDATE(),INTERVAL 30 DAY) AND enddate IS NULL AND assignedtodate IS NULL)\n\t\t\t\t\tOR (enddate IS NOT NULL AND enddate <= DATE_ADD(CURDATE(),INTERVAL 30 DAY))\n\t\t\t\t\tOR (assignedtodate IS NOT NULL AND assignedtodate <= DATE_ADD(CURDATE(),INTERVAL 30 DAY))\n\t\t\t\t )";
$querystatement .= " ORDER BY\n\t\t\t\timportance DESC,\n\t\t\t\txdate,\n\t\t\t\tsubject";
$queryresult = $this->db->query($querystatement);
$numRows = $this->db->numRows($queryresult);
?>
<h3 class="tasksLinks"><?php
echo $title;
if ($numRows) {
?>
<span class="small">(<?php
echo $numRows;
?>
)</span><?php
}
?>
</h3>
<div class="tasksDivs">
<div>
<?php
if ($numRows) {
$linkStart = getAddEditFile($this->db, "tbld:a4cdd991-cf0a-916f-1240-49428ea1bdd1");
$section["title"] = "Today";
$section["date"] = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
while ($therecord = $this->db->fetchArray($queryresult)) {
$className = "tasks";
if ($therecord["completed"]) {
$className .= " complete";
} else {
if ($therecord["ispastdue"] || $therecord["ispastassigneddate"]) {
$className .= " pastDue";
}
}
if ($therecord["private"]) {
$className .= " private";
}
$className .= " " . $therecord["type"];
$checkBoxID = $id . $therecord["type"] . "C" . $therecord["id"];
$link = $linkStart . "?id=" . $therecord["id"] . "&backurl=" . APP_PATH . "modules/base/snapshot.php";
$rightSide = "";
if ($therecord["assignedtodate"]) {
$rightSide .= "FUP: " . formatFromSQLDate($therecord["assignedtodate"]) . "<br />";
}
switch ($therecord["type"]) {
case "TS":
if ($therecord["enddate"]) {
$rightSide .= "Due: " . formatFromSQLDate($therecord["enddate"]) . "<br />";
}
break;
case "EV":
$rightSide .= "Start: " . formatFromSQLDate($therecord["startdate"]) . "<br />";
$rightSide .= "End: " . formatFromSQLDate($therecord["enddate"]) . "<br />";
break;
}
//endswitch
if (!$rightSide) {
$rightSide = " ";
}
$bottomInfo = "";
switch ($type) {
case "ReceivedAssignments":
if ($therecord["assignedbyid"]) {
$bottomInfo = "Assigned By: " . htmlQuotes($this->phpbms->getUserName($therecord["assignedbyid"], true));
}
break;
case "GivenAssignments":
$bottomInfo = "Assigned To: " . htmlQuotes($this->phpbms->getUserName($therecord["assignedtoid"], true));
break;
}
//endswitch
// Looking for grouping changes in headers (3 days, 4-7 days, > 7 days)
$xdate = stringToDate($therecord["xdate"], "SQL");
if ($xdate > $section["date"]) {
while ($xdate > $section["date"]) {
switch ($section["title"]) {
case "Today":
$section["title"] = "Soon";
$section["date"] = mktime(0, 0, 0, date("m"), date("d") + 7, date("Y"));
break;
case "Soon":
$section["title"] = "Later";
$section["date"] = mktime(0, 0, 0, 1, 1, 2038);
break;
case "Later":
//should never be here
$section["date"] = $xdate;
}
//end switch
}
//endwhile
?>
<div class="taskSection"><?php
echo $section["title"];
?>
</div><?php
}
//end if
?>
<div id="<?php
echo $id . $therecord["id"];
?>
" class="<?php
echo $className;
?>
">
<span class="taskRight"><?php
echo $rightSide;
?>
</span>
<input class="radiochecks taskChecks" id="<?php
echo $checkBoxID;
?>
" name="<?php
echo $checkBoxID;
?>
" type="checkbox" value="1" <?php
if ($therecord["completed"]) {
echo 'checked="checked"';
}
?>
align="middle" />
<a href="<?php
echo $link;
?>
"><?php
echo htmlQuotes($therecord["subject"]);
?>
</a>
<?php
if ($bottomInfo) {
?>
<p><?php
echo $bottomInfo;
?>
</p>
<?php
}
//endif
?>
</div>
<?php
}
//endwhile
} else {
?>
<p class="small disabledtext">no <?php
echo strtolower($title);
?>
</p><?php
}
?>
</div>
</div> <?php
}
function display($clientInfo)
{
$invoiceEditFile = getAddEditFile($this->db, "tbld:62fe599d-c18f-3674-9e54-b62c2d6b1883");
$noteEditFile = getAddEditFile($this->db, "tbld:a4cdd991-cf0a-916f-1240-49428ea1bdd1");
$clientEditFile = getAddEditFile($this->db, "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083");
?>
<div class="bodyline" id="theDetails">
<div id="rightSideDiv">
<fieldset>
<legend>sales</legend>
<ul class="recordCommands">
<li class="firstToolbarItem"><a href="#" class="newRecord" onclick="addEditRecord('new','invoice','<?php
echo getAddEditFile($this->db, "tbld:62fe599d-c18f-3674-9e54-b62c2d6b1883", "add");
?>
')" title="new sales order"><span>new</span></a></li>
<li><a href="#" id="invoiceedit" class="editRecordDisabled" onclick="addEditRecord('edit','invoice','<?php
echo $invoiceEditFile;
?>
')" title="edit"><span>edit</span></a></li>
</ul>
<div class="recordContainers">
<div id="salesTable" class="smallQueryTableHolder">
<?php
if (!count($clientInfo["invoices"])) {
?>
<div class="small"><em>no records</em></div>
<?php
} else {
?>
<table border="0" cellpadding="0" cellspacing="0" class="smallQueryTable">
<tr>
<th align="left">ID</th>
<th align="left">Type</th>
<th align="left">Date</th>
<th align="right" width="100%">Total</th>
</tr>
<?php
foreach ($clientInfo["invoices"] as $invoicerecord) {
if ($invoicerecord["type"] == "VOID") {
$invoicerecord["totalti"] = "-----";
}
?>
<tr onclick="selectEdit(this,<?php
echo $invoicerecord["id"];
?>
,'invoice')" ondblclick="selectedInvoice=<?php
echo $invoicerecord["id"];
?>
;addEditRecord('edit','invoice','<?php
echo $invoiceEditFile;
?>
')">
<td><?php
echo $invoicerecord["id"];
?>
</td>
<td><?php
echo $invoicerecord["type"];
?>
</td>
<td nowrap="nowrap"><?php
echo formatFromSQLDate($invoicerecord["thedate"]);
?>
</td>
<td align="right"><?php
echo numberToCurrency($invoicerecord["totalti"]);
?>
</td>
</tr>
<?php
}
?>
</table><?php
}
?>
</div>
</div>
</fieldset>
<fieldset>
<legend>notes</legend>
<ul class="recordCommands">
<li class="firstToolbarItem"><a href="#" title="new note" class="newRecord" onclick="addEditRecord('new','note','<?php
echo getAddEditFile($this->db, "tbld:a4cdd991-cf0a-916f-1240-49428ea1bdd1", "add");
?>
')"><span>new</span></a></li>
<li><a href="#" title="edit" id="noteedit" class="editRecordDisabled" onclick="addEditRecord('edit','note','<?php
echo $noteEditFile;
?>
')"><span>edit</span></a></li>
</ul>
<div class="recordContainers">
<div id="notesTable" class="smallQueryTableHolder">
<?php
if (!count($clientInfo["notes"])) {
?>
<div class="small"><em>no records</em></div>
<?php
} else {
?>
<table border="0" cellpadding="0" cellspacing="0" class="smallQueryTable">
<tr>
<th align="left">type</th>
<th align="left">category</th>
<th align="left" width="100%">title</th>
<th align="center">done</th>
</tr>
<?php
foreach ($clientInfo["notes"] as $noterecord) {
if (strlen($noterecord["subject"]) > 17) {
$noterecord["subject"] = substr($noterecord["subject"], 0, 17) . "...";
}
if (strlen($noterecord["category"]) > 17) {
$noterecord["category"] = substr($noterecord["category"], 0, 17) . "...";
}
?>
<tr onclick="selectEdit(this,<?php
echo $noterecord["id"];
?>
,'note')" ondblclick="selectedNote=<?php
echo $noterecord["id"];
?>
;addEditRecord('edit','note','<?php
echo $noteEditFile;
?>
')">
<td><?php
echo $noterecord["type"];
?>
</td>
<td><?php
echo $noterecord["category"];
?>
</td>
<td><?php
echo $noterecord["subject"];
?>
</td>
<td align="center"><?php
echo booleanFormat($noterecord["completed"]);
?>
</td>
</tr>
<?php
}
?>
</table><?php
}
?>
</div>
</div>
</fieldset>
</div>
<div id="leftSideDiv">
<fieldset id="crTile" class="fs<?php
echo $clientInfo["type"];
?>
">
<h1>
<input type="hidden" id="theid" value="<?php
echo $clientInfo["id"];
?>
" />
<input type="hidden" id="theuuid" value="<?php
echo $clientInfo["uuid"];
?>
" />
<?php
if ($clientInfo["company"]) {
echo htmlQuotes($clientInfo["company"]);
} else {
echo htmlQuotes($clientInfo["firstname"] . " " . $clientInfo["lastname"]);
}
?>
<button id="viewClientButton" type="button" title="view client" class="graphicButtons buttonInfo" onclick="addEditRecord('edit','client','<?php
echo $clientEditFile;
?>
')"><span>view client</span></button></h1>
<?php
if ($clientInfo["company"] && $clientInfo["firstname"] && $clientInfo["lastname"]) {
?>
<p id="crName"><?php
echo htmlQuotes($clientInfo["firstname"]);
?>
<?php
echo htmlQuotes($clientInfo["lastname"]);
?>
</p><?php
}
//endif
?>
<?php
$location = "";
$location .= htmlQuotes($clientInfo["address1"]);
if ($clientInfo["address2"]) {
$location .= "<br />" . htmlQuotes($clientInfo["address2"]);
}
if ($clientInfo["city"] || $clientInfo["state"] || $clientInfo["postalcode"]) {
$location .= "<br/>" . htmlQuotes($clientInfo["city"]);
if ($clientInfo["city"] && $clientInfo["state"]) {
$location .= ", ";
}
$location .= htmlQuotes($clientInfo["state"]);
$location .= " " . htmlQuotes($clientInfo["postalcode"]);
}
//endif
if ($clientInfo["country"]) {
$location .= "<br />" . htmlQuotes($clientInfo["country"]);
}
if ($location == "") {
$location = "unspecified location";
}
?>
<p id="crLocation"><?php
echo $location;
?>
</p>
</fieldset>
<fieldset>
<legend>Contact</legend>
<?php
if ($clientInfo["workphone"] || $clientInfo["homephone"] || $clientInfo["mobilephone"] || $clientInfo["otherphone"] || $clientInfo["fax"]) {
?>
<p class="RDNames">phone</p>
<div class="fauxP RDData">
<ul>
<?php
if ($clientInfo["workphone"]) {
?>
<li><?php
echo $clientInfo["workphone"];
?>
(w)</li>
<?php
}
?>
<?php
if ($clientInfo["homephone"]) {
?>
<li><?php
echo $clientInfo["homephone"];
?>
(h)</li>
<?php
}
?>
<?php
if ($clientInfo["mobilephone"]) {
?>
<li><?php
echo $clientInfo["mobilephone"];
?>
(m)</li>
<?php
}
?>
<?php
if ($clientInfo["otherphone"]) {
?>
<li><?php
echo $clientInfo["otherphone"];
?>
(o)</li>
<?php
}
?>
<?php
if ($clientInfo["fax"]) {
?>
<li><?php
echo $clientInfo["fax"];
?>
(fax)</li>
<?php
}
?>
</ul>
</div>
<?php
}
?>
<?php
if ($clientInfo["email"]) {
?>
<p class="RDNames">e-mail</p>
<p class="RDData">
<button type="button" class="graphicButtons buttonEmail" onclick="document.location='mailto:<?php
echo $clientInfo["email"];
?>
'"><span>send email</span></button>
<a href="mailto:<?php
echo $clientInfo["email"];
?>
"><?php
echo htmlQuotes($clientInfo["email"]);
?>
</a>
</p>
<?php
}
?>
<?php
if ($clientInfo["webaddress"]) {
?>
<p class="RDNames">web site</p>
<p class="RDData">
<button type="button" class="graphicButtons buttonWWW" onclick="window.open('<?php
echo $clientInfo["webaddress"];
?>
')"><span>visit site</span></button>
<a href="<?php
echo $clientInfo["webaddress"];
?>
" target="_blank"><?php
echo htmlQuotes($clientInfo["webaddress"]);
?>
</a>
</p>
<?php
}
?>
</fieldset>
<fieldset>
<legend>Details</legend>
<?php
if ($clientInfo["becameclient"]) {
?>
<p class="RDNames">became client</p>
<p class="RDData">
<?php
echo formatVariable($clientInfo["becameclient"], "date");
?>
</p>
<?php
}
?>
<?php
if ($clientInfo["category"]) {
?>
<p class="RDNames">category</p>
<p class="RDData">
<?php
echo htmlQuotes($clientInfo["category"]);
?>
</p>
<?php
}
?>
<?php
if ($clientInfo["leadsource"]) {
?>
<p class="RDNames">lead source</p>
<p class="RDData">
<?php
echo htmlQuotes($clientInfo["leadsource"]);
?>
</p>
<?php
}
?>
<?php
if ($clientInfo["salesmanagerid"]) {
global $phpbms;
?>
<p class="RDNames">sales person</p>
<p class="RDData">
<?php
echo htmlQuotes($phpbms->getUserName($clientInfo["salesmanagerid"]));
?>
</p>
<?php
}
?>
</fieldset>
<?php
if ($clientInfo["comments"]) {
?>
<fieldset>
<legend>memo</legend>
<p>
<?php
echo htmlQuotes($clientInfo["comments"]);
?>
</p>
</fieldset>
<?php
}
?>
</div>
<p id="theclear"> </p>
</div>
<?php
}
function showSalesHistory($id)
{
$thestatus = "(invoices.type =\"";
switch ($this->view) {
case "Orders/Invoices":
$thestatus .= "Order\" or invoices.type=\"Invoice\")";
$searchdate = "orderdate";
break;
case "Invoices":
$thestatus .= "Invoice\")";
$searchdate = "invoicedate";
break;
case "Orders":
$thestatus .= "Order\")";
$searchdate = "orderdate";
break;
}
$mysqlfromdate = sqlDateFromString($_POST["fromdate"]);
$mysqltodate = sqlDateFromString($_POST["todate"]);
$querystatement = "\n\t\t\tSELECT\n\t\t\t\t`invoices`.`id`,\n\t\t\t\t`invoices`.`orderdate`,\n\t\t\t\t`invoices`.`invoicedate`,\n\t\t\t\tIF(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) AS `client`,\n\t\t\t\t`lineitems`.`quantity` AS `qty`,\n\t\t\t\t`lineitems`.`unitprice`*`lineitems`.`quantity` AS `extended`,\n\t\t\t\t`lineitems`.`unitprice` AS `price`,\n\t\t\t\t`lineitems`.`unitcost` AS `cost`,\n\t\t\t\t`lineitems`.`unitcost`*`lineitems`.`quantity` AS extendedcost\n\t\t\tFROM\n\t\t\t\t((products INNER JOIN lineitems on products.uuid=lineitems.productid)\n\t\t\t\t\tINNER JOIN `invoices` ON lineitems.invoiceid=invoices.id)\n\t\t\t\t\t\tINNER JOIN `clients` on `invoices`.`clientid`=`clients`.`uuid`\n\t\t\tWHERE\n\t\t\t\t`products`.`id`=" . $id . "\n\t\t\t\tAND\n\t\t\t\t`invoices`." . $searchdate . ">=\"" . $mysqlfromdate . "\"\n\t\t\t\tAND\n\t\t\t\t`invoices`." . $searchdate . "<=\"" . $mysqltodate . "\"\n\t\t\t\tAND\n\t\t\t\t" . $thestatus . "\n\t\t\tORDER BY\n\t\t\t\t`invoices`.`invoicedate`,\n\t\t\t\t`invoices`.`orderdate`\n\t\t";
$thequery = $this->db->query($querystatement);
$thequery ? $numrows = $this->db->numRows($thequery) : ($numrows = 0);
?>
<table border="0" cellpadding="3" cellspacing="0">
<tr>
<th align="center" nowrap="nowrap" >ID</th>
<th align="center" nowrap="nowrap" >Order Date</th>
<th align="center" nowrap="nowrap" >Invc. Date</th>
<th nowrap="nowrap" width="100%" align="left">Client</th>
<th align="center" nowrap="nowrap" >Qty.</th>
<th align="right" nowrap="nowrap" >Unit Cost</th>
<th align="right" nowrap="nowrap" >Cost Ext.</th>
<th align="right" nowrap="nowrap" >Unit Price</th>
<th align="right" nowrap="nowrap">Price Ext.</th>
</tr>
<?php
$totalextended = 0;
$totalcostextended = 0;
$totalquantity = 0;
$avgprice = 0;
$avgcost = 0;
while ($therecord = $this->db->fetchArray($thequery)) {
$avgcost += $therecord["cost"];
$avgprice += $therecord["price"];
$totalquantity += $therecord["qty"];
$totalextended += $therecord["extended"];
$totalcostextended += $therecord["extendedcost"];
?>
<tr>
<td align="center" nowrap="nowrap"><?php
echo $therecord["id"];
?>
</td>
<td align="center" nowrap="nowrap"><?php
echo $therecord["orderdate"] ? formatFromSQLDate($therecord["orderdate"]) : " ";
?>
</td>
<td align="center" nowrap="nowrap"><?php
echo $therecord["invoicedate"] ? formatFromSQLDate($therecord["invoicedate"]) : " ";
?>
</td>
<td nowrap="nowrap"><?php
echo $therecord["client"];
?>
</td>
<td align="center" nowrap="nowrap"><?php
echo number_format($therecord["qty"], 2);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["cost"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["extendedcost"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["price"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["extended"]);
?>
</td>
</tr>
<?php
}
?>
<tr>
<td align="center" class="grandtotals"> </td>
<td align="center" class="grandtotals"> </td>
<td class="grandtotals"> </td>
<td class="grandtotals"> </td>
<td align="center" class="grandtotals"><?php
echo number_format($totalquantity, 2);
?>
</td>
<td align="right" nowrap="nowrap"class="grandtotals">avg. = <?php
$numrows ? $avgcost = $avgcost / $numrows : ($avgcost = 0);
echo numberToCurrency($avgcost);
?>
</td>
<td align="right" class="grandtotals"><?php
echo numberToCurrency($totalcostextended);
?>
</td>
<td align="right" nowrap="nowrap" class="grandtotals">avg. = <?php
$numrows ? $avgprice = $avgprice / $numrows : ($avgprice = 0);
echo numberToCurrency($avgprice);
?>
</td>
<td align="right" class="grandtotals"><?php
echo numberToCurrency($totalextended);
?>
</td>
</tr>
</table>
<?php
}
function display()
{
if ($this->displayLabel) {
$this->showLabel();
}
$value = formatFromSQLDate($this->value);
if (!isset($this->_attributes["onchange"])) {
$this->_attributes["onchange"] = "";
}
$this->_attributes["onchange"] = "formatDateField(this);" . $this->_attributes["onchange"];
?>
<input name="<?php
echo $this->name;
?>
" id="<?php
echo $this->id;
?>
" type="text" value="<?php
echo $value;
?>
" <?php
$this->displayAttributes();
?>
/><button id="<?php
echo $this->id;
?>
Button" type="button" class="graphicButtons buttonDate" onclick="showDP('<?php
echo APP_PATH;
?>
','<?php
echo $this->id;
?>
');"><span>pick date</span></button><?php
}
function generateSingleClientHistory($clientUUID)
{
$theStatus = "(invoices.type = '";
switch ($this->view) {
case "Orders and Invoices":
$theStatus .= "Order' OR invoices.type ='Invoice')";
$searchDate = "orderdate";
break;
case "Invoices":
$theStatus .= "Invoice')";
$searchDate = "invoicedate";
break;
case "Orders":
$theStatus .= "Order')";
$searchDate = "orderdate";
break;
}
//endswitch
$mysqlFromDate = sqlDateFromString($this->fromDate);
$mysqlToDate = sqlDateFromString($this->toDate);
$querystatement = "\n SELECT\n invoices.id,\n if(invoices.type = 'Invoice', invoices.invoicedate, invoices.orderdate) AS thedate,\n invoices.type,\n products.partname AS partname,\n products.partnumber AS partnumber,\n lineitems.quantity AS qty,\n lineitems.unitprice*lineitems.quantity AS extended,\n lineitems.unitprice AS price\n FROM\n ((`clients` INNER JOIN `invoices` ON `clients`.`uuid`=`invoices`.`clientid`)\n INNER JOIN `lineitems` ON `invoices`.`id`=`lineitems`.`invoiceid`)\n INNER JOIN `products` ON `lineitems`.`productid`=`products`.`uuid`\n WHERE\n `clients`.`uuid`='" . $clientUUID . "'\n AND " . $theStatus . "\n HAVING\n thedate >= '" . $mysqlFromDate . "'\n AND thedate <= '" . $mysqlToDate . "'\n ORDER BY\n thedate,\n invoices.id";
$queryresult = $this->db->query($querystatement);
ob_start();
?>
<table border="0" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th align="left" colspan="3">invoice</th>
<th align="left" colspan="3">product</th>
<th align="left" nowrap="nowrap" colspan="2" class="lastHeader">line item</th>
</tr>
<tr>
<th align="center" nowrap="nowrap">id</th>
<th align="left" nowrap="nowrap" >type</th>
<th align="left" nowrap="nowrap" >date</th>
<th align="left" nowrap="nowrap" >part #</th>
<th width="100%" nowrap="nowrap" align="left">name</th>
<th align="right" nowrap="nowrap" >price</th>
<th align="right" nowrap="nowrap" >qty.</th>
<th align="right" nowrap="nowrap" class="lastHeader">ext.</th>
</tr>
</thead>
<tbody>
<?php
$totalextended = 0;
while ($therecord = $this->db->fetchArray($queryresult)) {
$totalextended += $therecord["extended"];
?>
<tr>
<td align="left" nowrap="nowrap"><?php
echo $therecord["id"] ? $therecord["id"] : " ";
?>
</td>
<td align="left" nowrap="nowrap"><?php
echo $therecord["type"] ? formatVariable($therecord["type"]) : " ";
?>
</td>
<td align="left" nowrap="nowrap"><?php
echo $therecord["thedate"] ? formatFromSQLDate($therecord["thedate"]) : " ";
?>
</td>
<td nowrap="nowrap"><?php
echo formatVariable($therecord["partnumber"]);
?>
</td>
<td nowrap="nowrap"><?php
echo formatVariable($therecord["partname"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["price"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo $therecord["qty"];
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["extended"]);
?>
</td>
</tr>
<?php
}
//endwhile
?>
<tr class="grandTotals">
<td colspan="7" align="right">total</td>
<td align="right"><?php
echo numberToCurrency($totalextended);
?>
</td>
</tr>
</tbody>
</table><?php
$output = ob_get_contents();
ob_end_clean();
return $output;
}
function formatVariable($value, $format = NULL)
{
switch ($format) {
case "real":
$value = number_format($value, 2);
break;
case "currency":
$value = htmlQuotes(numberToCurrency($value));
break;
case "boolean":
$value = booleanFormat($value);
break;
case "date":
$value = formatFromSQLDate($value);
break;
case "time":
$value = formatFromSQLTime($value);
break;
case "datetime":
$value = formatFromSQLDatetime($value);
break;
case "filelink":
$value = "<button class=\"graphicButtons buttonDownload\" type=\"button\" onclick=\"document.location='" . APP_PATH . "servefile.php?i=" . $value . "'\"><span>download</span></button>";
//$value="<a href=\"".APP_PATH."servefile.php?i=".$value."\" style=\"display:block;\"><img src=\"".APP_PATH."common/stylesheet/".STYLESHEET."/image/button-download.png\" align=\"middle\" alt=\"view\" width=\"16\" height=\"16\" border=\"0\" /></a>";
break;
case "invoice":
if ($value > 0 and $value != 9999999) {
$value = "<a href=\"" . APP_PATH . "modules/bms/invoices_addedit.php?id={$value}\">{$value}</a>";
}
break;
case "client":
$value = "<a href=\"" . APP_PATH . "modules/bms/clients_addedit.php?id={$value}\">{$value}</a>";
break;
case "noencoding":
$value = $value;
break;
case "bbcode":
$value = htmlQuotes($value);
// This list needs to be expanded
$bbcodelist["[b]"] = "<strong>";
$bbcodelist["[/b]"] = "</strong>";
$bbcodelist["[br]"] = "<br />";
$bbcodelist["[space]"] = " ";
foreach ($bbcodelist as $bbcode => $translation) {
$value = str_replace($bbcode, $translation, $value);
}
break;
default:
$value = htmlQuotes($value);
}
return $value;
}
function showInvoices($where, $indent)
{
$querystatement = "SELECT ";
foreach ($this->selectcolumns as $thecolumn) {
$querystatement .= $thecolumn["field"] . " AS `" . $thecolumn["name"] . "`,";
}
$querystatement .= " invoices.id as theid, if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) as thename,\n\t\t\t\t\t\t\tinvoices.orderdate,invoices.invoicedate";
$querystatement .= " FROM " . $this->selecttable . $this->whereClause . $where . " GROUP BY invoices.id";
$queryresult = $this->db->query($querystatement);
while ($therecord = $this->db->fetchArray($queryresult)) {
?>
<tr>
<td width="100%" style="padding-left:<?php
echo $indent + 2;
?>
px;" class="invoices">
<?php
echo $therecord["theid"] . ": ";
if ($therecord["invoicedate"]) {
echo "(" . formatFromSQLDate($therecord["invoicedate"]) . ") ";
} else {
echo "<strong>(" . formatFromSQLDate($therecord["orderdate"]) . ")</strong> ";
}
echo $therecord["thename"];
?>
</td>
<?php
foreach ($this->selectcolumns as $thecolumn) {
if ($thecolumn["name"] != "count") {
?>
<td align="right" class="invoices"><?php
echo formatVariable($therecord[$thecolumn["name"]], $thecolumn["format"]);
?>
</td><?php
} else {
echo "<td class=invoices> </td>";
}
}
//end foreach
?>
</tr>
<?php
if ($this->showlineitems) {
$this->showLineItems($therecord["theid"], $indent + $this->padamount);
}
}
//end while
}
</p>
<p><?php
$theform->showField("inactive");
?>
</p>
<p id="becameclientDiv" <?php
if ($therecord["type"] == "prospect") {
echo "style=\"display:none;\"";
}
?>
>
<label for="becameclient">became a client</label><br />
<input type="text" id="becameclient" name="becameclient" readonly="readonly" class="uneditable" value="<?php
echo formatFromSQLDate($therecord["becameclient"]);
?>
" size="8" />
<input type="hidden" id="hascredit" name="hascredit" value="<?php
echo $therecord["hascredit"];
?>
"/>
<input type="hidden" id="creditlimit" name="creditlimit" value="<?php
echo $therecord["creditlimit"];
?>
"/>
</p>
<p><?php
$theform->showField("category");
?>
function show($itemslist, $receiptPosted, $receiptid)
{
$count = 1;
foreach ($itemslist as $therecord) {
$recID = "i" . $count;
if ($therecord["type"] == "invoice") {
$tempDate = stringToDate($therecord["itemdate"], "SQL");
$dueDate = dateToString(strtotime(TERM1_DAYS . " days", $tempDate));
} else {
$dueDate = " ";
}
if ($therecord["type"] == "credit" && $therecord["relatedid"] == $receiptid) {
$therecord["relatedid"] = "";
$therecord["amount"] = 0;
$therecord["aritemid"] = "";
$therecord["invoiceid"] = "";
}
if ($receiptPosted) {
$docDue = $therecord["amount"] - $therecord["paid"];
} elseif ($therecord["relatedid"]) {
$docDue = $therecord["amount"] - $therecord["paid"] - $therecord["applied"] - $therecord["discount"] - $therecord["taxadjustment"];
} else {
$docDue = 0;
}
?>
<tr id="<?php
echo $recID;
?>
" class="receiptTR">
<td>
<input type="hidden" id="<?php
echo $recID;
?>
ARID" value="<?php
echo $therecord["aritemid"];
?>
" />
<input type="hidden" id="<?php
echo $recID;
?>
RecID" value="<?php
echo $therecord["relatedid"];
?>
" />
<input id="<?php
echo $recID;
?>
DocRef" class="invisibleTextField" readonly="readonly" value="<?php
echo $therecord["invoiceid"];
?>
" size="4"/>
</td>
<td><input id="<?php
echo $recID;
?>
Type" class="invisibleTextField" readonly="readonly" value="<?php
echo $therecord["type"];
?>
" size="12" /></td>
<td><input id="<?php
echo $recID;
?>
DocDate" class="invisibleTextField" readonly="readonly" value="<?php
echo formatFromSQLDate($therecord["itemdate"]);
?>
" size="9" /></td>
<td><input id="<?php
echo $recID;
?>
DueDate" class="invisibleTextField" readonly="readonly" value="<?php
echo $dueDate;
?>
" size="9" /></td>
<td align="right">
<input type="hidden" id="<?php
echo $recID;
?>
DocPaid" value="<?php
echo $therecord["paid"];
?>
" />
<input id="<?php
echo $recID;
?>
DocAmount" class="invisibleTextField currency" readonly="readonly" value="<?php
echo formatVariable($therecord["amount"], "currency");
?>
" size="10" maxlength="12"/>
</td>
<td><input id="<?php
echo $recID;
?>
DocDue" class="invisibleTextField currency dueFields" readonly="readonly" value="<?php
echo formatVariable($docDue, "currency");
?>
" size="10" maxlength="12"/></td>
<td><input id="<?php
echo $recID;
?>
Applied" class="currency appliedFields" value="<?php
echo formatVariable($therecord["applied"], "currency");
?>
" size="10" maxlength="12"/></td>
<td><input id="<?php
echo $recID;
?>
Discount" class="currency" value="<?php
echo formatVariable($therecord["discount"], "currency");
?>
" size="10" maxlength="12"/></td>
<td><input id="<?php
echo $recID;
?>
TaxAdj" class="currency" value="<?php
echo formatVariable($therecord["taxadjustment"], "currency");
?>
" size="10" maxlength="12"/></td>
<td><button type="button" id="<?php
echo $recID;
?>
RemoveARItemButton" class="graphicButtons buttonMinus" title="remove item"><span>-</span></button></td>
</tr>
<?php
$count++;
}
//end foreach
}
function show()
{
?>
<table border="0" cellpadding="0" cellspacing="0" class="querytable" id="paymentTable">
<thead>
<tr>
<th align="left">status</th>
<th align="left" nowrap="nowrap">receipt id</th>
<th align="left">date</th>
<th align="right" width="100%">payment</th>
<th align="right">applied</th>
<th align="right">discount</th>
<th align="right" nowrap="nowrap">tax adj.</th>
</tr>
</thead>
<tfoot>
<tr class="queryfooter">
<td colspan="4" align="right">total: <strong><?php
echo formatVariable($this->totals["applied"] + $this->totals["discount"] + $this->totals["taxadjustment"], "currency");
?>
</strong> </td>
<td align="right"><?php
echo formatVariable($this->totals["applied"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($this->totals["discount"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($this->totals["taxadjustment"], "currency");
?>
</td>
</tr>
</tfoot>
<tbody>
<?php
if ($this->db->numRows($this->queryresult)) {
$postedGroup = "";
$row = 1;
while ($therecord = $this->db->fetchArray($this->queryresult)) {
$row = $row == 1 ? 2 : 1;
if ($postedGroup != $therecord["posted"]) {
$postedGroup = $therecord["posted"];
$title = $therecord["posted"] == 1 ? "Posted Payments" : "Pending Payments (non-posted)";
?>
<tr class="queryGroup">
<td colspan="7"><?php
echo $title;
?>
</td>
</tr><?php
}
//end if
if (!$therecord["name"]) {
$therecord["name"] = "other";
}
if ($therecord["id"] == $this->aritem["relatedid"] && $this->aritem["type"] == "credit") {
$therecord["applied"] = -1 * $therecord["applied"];
}
?>
<tr class="row<?php
echo $row;
?>
">
<td><?php
echo $therecord["status"];
?>
</td>
<td><?php
echo $therecord["id"];
?>
</td>
<td><?php
echo formatFromSQLDate($therecord["receiptdate"]);
?>
</td>
<td align="right"><?php
echo $therecord["name"];
?>
</td>
<td align="right"><?php
echo formatVariable($therecord["applied"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($therecord["discount"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($therecord["taxadjustment"], "currency");
?>
</td>
</tr><?php
}
//end while
} else {
?>
<tr class="norecords"><td colspan="7" >No payments recorded</td></tr><?php
}
//endif
?>
</tbody>
</table><?php
}
function showHistory($clientid)
{
$querystatement = "\n\t\t\t\tSELECT \n\t\t\t\t\tid, \n\t\t\t\t\tuuid\n\t\t\t\tFROM\n\t\t\t\t\tclients\n\t\t\t\tWHERE\n\t\t\t\t\tid=" . (int) $clientid;
$queryresult = $this->db->query($querystatement);
$therecord = $this->db->fetchArray($queryresult);
$querystatement = "\n\t\t\t\tSELECT\n\t\t\t\t\trelatedid,\n\t\t\t\t\tamount,\n\t\t\t\t\t`type`,\n\t\t\t\t\tpaid,\n\t\t\t\t\titemdate,\n\t\t\t\t\tposted\n\t\t\t\tFROM\n\t\t\t\t\taritems\n\t\t\t\tWHERE\n\t\t\t\t\tclientid = '" . $therecord["uuid"] . "'\n\t\t\t\t\tAND `status` = 'open'\n\t\t\t\tORDER BY\n\t\t\t\t\tposted";
$queryresult = $this->db->query($querystatement);
$querystatement = "\n\t\t\t\tSELECT\n\t\t\t\t\tSUM(amount) AS theamount,\n\t\t\t\t\tSUM(amount - paid) AS thedue\n\t\t\t\tFROM\n\t\t\t\t\taritems\n\t\t\t\tWHERE\n\t\t\t\t\tclientid = '" . $therecord["uuid"] . "'\n\t\t\t\t\tAND `status` = 'open'\n\t\t\t\tORDER BY\n\t\t\t\t\tposted";
$sumresult = $this->db->query($querystatement);
$sumrecord = $this->db->fetchArray($sumresult);
?>
<table border="0" cellpadding="0" cellspacing="0" class="querytable" id="openItems">
<thead>
<tr>
<th align="left" width="35%" nowrap="nowrap">doc ref</th>
<th align="left" nowrap="nowrap">type</th>
<th align="left" nowrap="nowrap">doc date</th>
<th align="left" nowrap="nowrap">due date</th>
<th align="right" width="65%" nowrap="nowrap">amount</th>
<th align="right">due</th>
</tr>
</thead>
<tfoot>
<tr class="queryfooter">
<td colspan="5" align="right"><?php
echo formatVariable($sumrecord["theamount"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($sumrecord["thedue"], "currency");
?>
</td>
</tr>
</tfoot>
<tbody>
<?php
if ($this->db->numRows($queryresult)) {
$postedGroup = "";
$row = 1;
while ($therecord = $this->db->fetchArray($queryresult)) {
$row = $row == 1 ? 2 : 1;
if ($postedGroup != $therecord["posted"]) {
$postedGroup = $therecord["posted"];
$title = $therecord["posted"] == 1 ? "Current" : "Pending (non-posted)";
?>
<tr class="queryGroup">
<td colspan="6"><?php
echo $title;
?>
</td>
</tr><?php
}
//end if
$dueDate = strtotime(TERM1_DAYS . " days", stringToDate($therecord["itemdate"], "SQL"));
?>
<tr class="row<?php
echo $row;
?>
">
<td><?php
echo $therecord["relatedid"];
?>
</td>
<td nowrap="nowrap"><?php
echo $therecord["type"];
?>
</td>
<td><?php
echo formatFromSQLDate($therecord["itemdate"]);
?>
</td>
<td <?php
if ($dueDate < mktime(0, 0, 0)) {
echo 'class="important"';
}
?>
><?php
if ($therecord["type"] == "invoice") {
echo dateToString($dueDate);
} else {
echo " ";
}
?>
</td>
<td align="right"><?php
echo formatVariable($therecord["amount"], "currency");
?>
</td>
<td align="right"><?php
echo formatVariable($therecord["amount"] - $therecord["paid"], "currency");
?>
</td>
</tr><?php
}
//end while
} else {
?>
<tr class="norecords"><td colspan="6" >No open items</td></tr><?php
}
//endif
?>
</tbody>
</table><?php
}
function showLineItems($where, $indent)
{
$querystatement = "\n\t\t\tSELECT\n\t\t\t\t`lineitems`.`invoiceid`,\n\t\t\t\tIF(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) AS `thename`,\n\t\t\t\t`invoices`.`invoicedate`,\n\t\t\t\t`invoices`.`orderdate`,\n\t\t\t\t`lineitems`.`id`,\n\t\t\t\t`products`.`partnumber`,\n\t\t\t\t`products`.`partname`,\n\t\t\t\t`quantity`,\n\t\t\t\t`lineitems`.`unitprice`,\n\t\t\t\t`quantity`*`lineitems`.`unitprice` AS `extended`\n\t\t\tFROM " . $this->selecttable . $this->whereClause . $where . " GROUP BY lineitems.id\n\t\t";
$queryresult = $this->db->query($querystatement);
if ($this->db->numRows($queryresult)) {
?>
<tr><td class="invoices" style="padding-left:<?php
echo $indent + 2;
?>
px;">
<table border="0" cellspacing="0" cellpadding="0" id="lineitems">
<tr>
<th align="left">id</th>
<th align="left">date</th>
<th width="20%" align="left" >client</th>
<th width="60%" align="left">product</th>
<th width="9%" align="right" nowrap="nowrap">price</th>
<th width="8%" align="right" nowrap="nowrap">qty.</th>
<th width="7%" align="right" nowrap="nowrap">ext.</th>
</tr>
<?php
while ($therecord = $this->db->fetchArray($queryresult)) {
?>
<tr>
<td nowrap="nowrap"><?php
echo $therecord["invoiceid"];
?>
</td>
<td nowrap="nowrap"><?php
if ($therecord["invoicedate"]) {
echo formatFromSQLDate($therecord["invoicedate"]);
} else {
echo "<strong>" . formatFromSQLDate($therecord["orderdate"]) . "</strong>";
}
?>
</td>
<td><?php
echo $therecord["thename"];
?>
</td>
<td width="60%" nowrap="nowrap"><?php
echo $therecord["partnumber"];
?>
<?php
echo $therecord["partname"];
?>
</td>
<td width="9%" align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["unitprice"]);
?>
</td>
<td width="8%" align="center" nowrap="nowrap"><?php
echo formatVariable($therecord["quantity"], "real");
?>
</td>
<td width="7%" align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["extended"]);
?>
</td>
</tr>
<?php
}
// endwhile
?>
</table></td>
<?php
for ($i = 1; $i < count($this->selectcolumns); $i++) {
echo "<td> </td>";
}
?>
</tr><?php
}
// endif
}
echo urlencode($_SERVER["REQUEST_URI"]);
?>
'"><img src="<?php
echo APP_PATH;
?>
common/stylesheet/<?php
echo STYLESHEET;
?>
/image/button-edit.png" align="middle" alt="edit" width="16" height="16" border="0" /></button>
</td>
<td align="center" nowrap="nowrap"><?php
echo $therecord["id"];
?>
</td>
<td align="center" nowrap="nowrap"><?php
echo $therecord["thedate"] ? formatFromSQLDate($therecord["thedate"]) : " ";
?>
</td>
<td nowrap="nowrap"><?php
echo $therecord["client"];
?>
</td>
<td align="center" nowrap="nowrap"><?php
echo number_format($therecord["qty"], 2);
?>
</td>
<td align="right" nowrap="nowrap"><?php
echo numberToCurrency($therecord["cost"]);
?>
</td>
<td align="right" nowrap="nowrap"><?php
<fieldset>
<legend>Repeat Statistics</legend>
<p>
first recurred<br />
<input readonly="readonly" class="uneditable" type="text" size="12" value="<?php
if ($therecord["lastrepeat"]) {
echo formatFromSQLDate($therecord["firstrepeat"]);
}
?>
"/>
</p>
<p>
last recurred<br />
<input readonly="readonly" class="uneditable" type="text" size="12" value="<?php
if ($therecord["lastrepeat"]) {
echo formatFromSQLDate($therecord["lastrepeat"]);
}
?>
"/>
</p>
<p>
number of recurrences to date<br/>
<input readonly="readonly" class="uneditable" type="text" size="4" value="<?php
echo $therecord["timesrepeated"];
?>
"/>
</p>
</fieldset>
</div>
<div id="leftSideDiv">
function _getItems()
{
$querystatement = "\n\t\t\tSELECT\n\t\t\t\t`aritems`.`id`,\n\t\t\t\t`aritems`.`type`,\n\t\t\t\tIF(`invoices`.`id`,`invoices`.`id`,`receipts`.`id`) AS `relatedid`,\n\t\t\t\t`aritems`.`itemdate`,\n\t\t\t\t`aritems`.`amount`,\n\t\t\t\t`aritems`.`amount` - `aritems`.`paid` AS `due`\n\t\t\tFROM\n\t\t\t\t(`aritems` LEFT JOIN `invoices` ON `aritems`.`relatedid` = `invoices`.`uuid`) LEFT JOIN `receipts` ON `aritems`.`relatedid` = `receipts`.`uuid`\n\t\t\tWHERE\n\t\t\t\t`aritems`.`posted` =1\n\t\t\t\tAND `aritems`.`clientid` = '" . mysql_real_escape_string($this->clientrecord["clientid"]) . "'\n\t\t";
if (!$this->showClosed) {
$querystatement .= "\n\t\t\t\tAND `aritems`.`status` = 'open'";
}
$querystatement .= "\n\t\t\t\tAND `aritems`.itemdate <= '" . dateToString($this->statementDate, "SQL") . "'\n\t\t\tORDER BY\n\t\t\t\t`aritems`.itemdate,\n\t\t\t\t`aritems`.id";
$queryresult = $this->db->query($querystatement);
$returnArray = array();
$this->clientTotals = array("docamount" => 0, "current" => 0, "term1" => 0, "term2" => 0, "term3" => 0);
while ($therecord = $this->db->fetchArray($queryresult)) {
$itemdate = stringToDate($therecord["itemdate"], "SQL");
$therecord["current"] = " ";
$therecord["term1"] = " ";
$therecord["term2"] = " ";
$therecord["term3"] = " ";
if ($therecord["type"] != "invoice") {
$this->clientTotals["current"] += $therecord["due"];
$therecord["current"] = formatVariable($therecord["due"], "currency");
} else {
$daysover = floor(($this->statementDate - $itemdate) / 86400);
if ($daysover > TERM3_DAYS) {
$this->clientTotals["term3"] += $therecord["due"];
$therecord["term3"] = formatVariable($therecord["due"], "currency");
} elseif ($daysover > TERM2_DAYS) {
$this->clientTotals["term2"] += $therecord["due"];
$therecord["term2"] = formatVariable($therecord["due"], "currency");
} elseif ($daysover > TERM1_DAYS) {
$this->clientTotals["term1"] += $therecord["due"];
$therecord["term1"] = formatVariable($therecord["due"], "currency");
} else {
$this->clientTotals["current"] += $therecord["due"];
$therecord["current"] = formatVariable($therecord["due"], "currency");
}
}
//endif
$therecord["itemdate"] = formatFromSQLDate($therecord["itemdate"]);
$this->clientTotals["docamount"] += $therecord["amount"];
$therecord["amount"] = formatVariable($therecord["amount"], "currency");
$therecord["due"] = formatVariable($therecord["due"], "currency");
$returnArray[$therecord["itemdate"] . "-" . $therecord["id"]] = $therecord;
}
//endwhile
if ($this->showPayments != "none") {
//add in receipts in laste term1_days
$lastDays = dateToString(strtotime("-" . TERM1_DAYS . " days", $this->statementDate), "SQL");
$querystatement = "\n\t\t\t\tSELECT\n\t\t\t\t\tid,\n\t\t\t\t\treceiptdate,\n\t\t\t\t\tamount\n\t\t\t\tFROM\n\t\t\t\t\treceipts\n\t\t\t\tWHERE\n\t\t\t\t\tclientid = '" . mysql_real_escape_string($this->clientrecord["clientid"]) . "'\n\t\t\t";
if ($this->showPayments == "new") {
$querystatement .= "\n\t\t\t\tAND receiptdate <= '" . dateToString($this->statementDate, "SQL") . "'\n\t\t\t\tAND receiptdate >= '" . $lastDays . "'";
}
$querystatement .= "\n\t\t\t\tAND posted = 1";
$receiptresult = $this->db->query($querystatement);
while ($receiptrecord = $this->db->fetchArray($receiptresult)) {
$rcptRecord["itemdate"] = formatFromSQLDate($receiptrecord["receiptdate"]);
$rcptRecord["type"] = "payment";
$rcptRecord["relatedid"] = " ";
$rcptRecord["amount"] = "(" . formatVariable($receiptrecord["amount"], "currency") . ")";
$rcptRecord["current"] = " ";
$rcptRecord["term1"] = " ";
$rcptRecord["term2"] = " ";
$rcptRecord["term3"] = " ";
$rcptRecord["due"] = " ";
$returnArray[$receiptrecord["receiptdate"] . "-D" . $receiptrecord["id"]] = $rcptRecord;
}
//endwhile
}
//end if
ksort($returnArray);
return $returnArray;
}
function showItemLines($where, $indent)
{
$querystatement = "\n (SELECT\n " . $this->generateColumns("invoices") . ",\n invoices.id AS theid,\n if(clients.lastname!='', concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company) AS `thename`,\n invoices.invoicedate AS `docdate`\n FROM\n (" . $this->tableClause["invoices"] . ") INNER JOIN clients ON invoices.clientid = clients.uuid\n " . $this->whereClauses["invoices"] . $this->typeSubstitute($where, "invoices") . "\n GROUP BY `theid`)\n UNION\n (SELECT\n " . $this->generateColumns("receipts") . ",\n receipts.id AS `theid`,\n if(clients.lastname!='', concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company) AS `thename`,\n receipts.receiptdate AS `docdate`\n FROM\n (" . $this->tableClause["receipts"] . ") INNER JOIN clients ON receipts.clientid = clients.uuid\n " . $this->whereClauses["receipts"] . $this->typeSubstitute($where, "receipts") . "\n GROUP BY `theid`)\n ORDER BY `docdate`";
$queryresult = $this->db->query($querystatement);
while ($therecord = $this->db->fetchArray($queryresult)) {
?>
<tr>
<td width="100%" style="padding-left:<?php
echo $indent + 2;
?>
px;" class="invoices">
<?php
echo '<div style="float:right">' . $therecord["thename"] . '</div>';
echo formatFromSQLDate($therecord["docdate"]) . " (" . $therecord["theid"] . ")";
?>
</td>
<?php
foreach ($this->selectcolumns as $thecolumn) {
if ($thecolumn["name"] != "count") {
?>
<td align="right" class="invoices"><?php
echo formatVariable($therecord[$thecolumn["name"]], $thecolumn["format"]);
?>
</td><?php
} else {
echo "<td class=invoices> </td>";
}
}
//end foreach
?>
</tr>
<?php
}
//end while
}
