Location: PHPKode > projects > PhpBMS > phpbms/modules/bms/report/aritems_clientstatement.php
<?php
/*
 $Rev: 290 $ | $LastChangedBy: brieb $
 $LastChangedDate: 2007-08-27 18:15:00 -0600 (Mon, 27 Aug 2007) $
 +-------------------------------------------------------------------------+
 | Copyright (c) 2004 - 2010, Kreotek LLC                                  |
 | All rights reserved.                                                    |
 +-------------------------------------------------------------------------+
 |                                                                         |
 | Redistribution and use in source and binary forms, with or without      |
 | modification, are permitted provided that the following conditions are  |
 | met:                                                                    |
 |                                                                         |
 | - Redistributions of source code must retain the above copyright        |
 |   notice, this list of conditions and the following disclaimer.         |
 |                                                                         |
 | - Redistributions in binary form must reproduce the above copyright     |
 |   notice, this list of conditions and the following disclaimer in the   |
 |   documentation and/or other materials provided with the distribution.  |
 |                                                                         |
 | - Neither the name of Kreotek LLC nor the names of its contributore may |
 |   be used to endorse or promote products derived from this software     |
 |   without specific prior written permission.                            |
 |                                                                         |
 | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS     |
 | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT       |
 | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A |
 | PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT      |
 | OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,   |
 | SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT        |
 | LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,   |
 | DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY   |
 | THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT     |
 | (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE   |
 | OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.    |
 |                                                                         |
 +-------------------------------------------------------------------------+
*/

if(!class_exists("phpbmsReport"))
	include("../../../report/report_class.php");

class aritemsClientStatements extends phpbmsReport{

	var $currentTotal = 0;
	var $term1Total = 0;
	var $term2Total = 0;
	var $term3Total = 0;
	var $showPayments = true;
	var $showClosed = false;

	function aritemsClientStatements($db, $reportUUID, $tabledefUUID, $statementDate = NULL, $showPayments = true, $showClosed = false){

                parent::phpbmsReport($db, $reportUUID, $tabledefUUID);

		if($statementDate)
			$this->statementDate = $statementDate;
		else
			$this->statementDate = mktime(0,0,0);

		$this->showPayments = $showPayments;
		$this->showClosed = $showClosed;

	}//end method aritemsClientStatements


	function generate($whereclause = ""){


		if($whereclause)
			$this->whereClause = $whereclause;

		if(!$this->whereClause)
			$this->whereClause = "
				aritems.status = 'open'
				AND aritems.posted = 1";


		//first we need to get the list of clients.
		$querystatement = "
			SELECT DISTINCT
				aritems.clientid,
				clients.id,
				clients.firstname,
				clients.lastname,
				clients.company,
				addresses.address1,
				addresses.address2,
				addresses.city,
				addresses.postalcode,
				addresses.state,
				addresses.country,
				clients.creditlimit,
				clients.homephone,
				clients.workphone,
				clients.email,
				users.firstname AS salesfirstname,
				users.lastname AS saleslastname
			FROM
				(aritems INNER JOIN clients
					ON aritems.clientid = clients.uuid
				INNER JOIN addresstorecord
					ON addresstorecord.tabledefid = 'tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary = 1
					AND addresstorecord.recordid = clients.uuid
				INNER JOIN addresses
					ON addresstorecord.addressid = addresses.uuid
				)LEFT JOIN users
					ON clients.salesmanagerid = users.uuid";

		$this->sortOrder = 'if(clients.lastname!="",concat(clients.lastname,", ",clients.firstname,if(clients.company!="",concat(" (",clients.company,")"),"")),clients.company)';

		$querystatement = $this->assembleSQL($querystatement);

		$clientresult = $this->db->query($querystatement);

		if(!class_exists("phpbmsPDFReport"))
			include("report/pdfreport_class.php");

		$this->pdf = new phpbmsPDFReport($this->db, "P","in","Letter");

		$pdf = &$this->pdf;

		$this->itemColumns = array(
			array("field" => "itemdate", "title" => "date", "size" => 1, "align" => "L", "nl" => 0),
			array("field" => "type", "title" => "type", "size" => 1, "align" => "L", "nl" => 0),
			array("field" => "relatedid", "title" => "doc ref", "size" => 1, "align" => "L", "nl" => 0),
			array("field" => "dayspd", "title" => "days past due", "size" => 1, "align" => "L", "nl" => 0),
			array("field" => "amount", "title" => "doc amount", "size" => 0, "align" => "R", "nl" => 0),
			array("field" => "due", "title" => "due", "size" => 1, "align" => "R", "nl" => 2),
		);

		$this->itemColumns[4]["size"] = $pdf->paperwidth - $pdf->leftmargin - $pdf->rightmargin - 5;

		//uncomment the following line to help troubleshoot formatting
		//$pdf->borderDebug = 1;
		$pdf->logoInHeader = true;
                $pdf->companyInfoInHeader = true;
		$pdf->SetMargins();

		while($clientrecord = $this->db->fetchArray($clientresult)){

			$this->page = 1;

			$this->clientrecord = $clientrecord;

			$this->_addPage();

			$this->_addItems();

			//footer
			$pdf->setXY($pdf->leftmargin, 8.5 + $pdf->topmargin);

			$pdf->setStyle("header");
			$pdf->Cell(1 - 0.01, 0.17, "Current", 1, 0, "R", 1);
			$pdf->SetX($pdf->GetX()+.02);
			$pdf->Cell(1 - 0.02, 0.17, (TERM1_DAYS+1)." - ".TERM2_DAYS, 1, 0, "R", 1);
			$pdf->SetX($pdf->GetX()+.02);
			$pdf->Cell(1 - 0.02, 0.17, (TERM2_DAYS+1)." - ".TERM3_DAYS, 1, 0, "R", 1);
			$pdf->SetX($pdf->GetX()+.02);
			$pdf->Cell(1 - 0.02, 0.17, "+".TERM3_DAYS, 1, 0, "R", 1);
			$pdf->SetX($pdf->GetX()+.02);
			$pdf->Cell(1 - 0.02, 0.17, "Total Due", 1, 2, "R", 1);

			$pdf->SetX($pdf->leftmargin);

			$pdf->setStyle("normal");
			$pdf->SetFont("Arial", "B");
			$pdf->Cell(1, 0.17, formatVariable($this->currentTotal, "currency"), 1, 0, "R", 1);
			$pdf->Cell(1, 0.17, formatVariable($this->term1Total, "currency"), 1, 0, "R", 1);
			$pdf->Cell(1, 0.17, formatVariable($this->term2Total, "currency"), 1, 0, "R", 1);
			$pdf->Cell(1, 0.17, formatVariable($this->term3Total, "currency"), 1, 0, "R", 1);

			$totalDue = $this->currentTotal + $this->term1Total + $this->term2Total + $this->term3Total;

			$pdf->Cell(1 - 0.01, 0.17, formatVariable($totalDue, "currency"), 1, 0, "R", 1);

			$this->currentTotal = 0;
			$this->term1Total = 0;
			$this->term2Total = 0;
			$this->term3Total = 0;

		}//end while

	}//end method


	function _addPage(){

		$pdf = &$this->pdf;

		$pdf->AddPage();

		$nextY = $pdf->getY();

		//title/info
		$title = "Statement";
		$titleWidth=2.5;
		$titleHeight=.25;
		$pdf->setStyle("title");
		$pdf->SetXY(-1*($titleWidth+$pdf->rightmargin), $pdf->topmargin);
		$pdf->Cell($titleWidth, $titleHeight,$title, $pdf->borderDebug,1,"R");

		$sideWidth = 1.25;
		$pdf->SetXY( -1 * ($sideWidth + $pdf->rightmargin), $nextY + 0.125);
		$pdf->setStyle("header");
		$pdf->Cell($sideWidth, .17, "date", 1, 2, "L", 1);
		$pdf->setStyle("normal");
		$pdf->Cell($sideWidth, .17, dateToString($this->statementDate), 1, 2, "L");

		$pdf->setStyle("header");
		$pdf->Cell($sideWidth, .17, "Account No.", 1, 2, "L", 1);
		$pdf->setStyle("normal");
		$pdf->Cell($sideWidth, .17, $this->clientrecord["id"], 1, 2, "L");

		$pdf->setStyle("header");
		$pdf->Cell($sideWidth, .17, "Sales Rep.", 1, 2, "L", 1);
		$pdf->setStyle("normal");
		$pdf->SetFontSize(6);
		$pdf->Cell($sideWidth, .17, $this->clientrecord["salesfirstname"]." ".$this->clientrecord["saleslastname"], 1, 2, "L");

		$pdf->SetFontSize(8);
		$pdf->Cell($sideWidth, .17, "page: ".$this->page, $pdf->borderDebug, 2, "L");

		//Client info
		$displayName = "";
		if($this->clientrecord["company"]){

			$displayName .= $this->clientrecord["company"];

			if($this->clientrecord["lastname"])
				$displayName .= " (".$this->clientrecord["firstname"]." ".$this->clientrecord["lastname"].")";

		} else
			$displayName .= $this->clientrecord["firstname"]." ".$this->clientrecord["lastname"];

		$address = $this->clientrecord["address1"];

		if ($this->clientrecord["address2"])
			$address.="\n".$this->clientrecord["address2"];

		$address .= "\n".$this->clientrecord["city"].", ".$this->clientrecord["state"]."  ".$this->clientrecord["postalcode"];

		if($this->clientrecord["country"])
			$address .= " ".$this->clientrecord["country"];

		$phoneemail = "";
		if($this->clientrecord["workphone"] || $this->clientrecord["homephone"]){

			$phoneemail = $this->clientrecord["homephone"]." (H)";

			if($this->clientrecord["workphone"])
				$phoneemail = $this->clientrecord["workphone"]." (W)";

			$phoneemail .= "\n";

		}//end if

		if($this->clientrecord["email"])
			$phoneemail .= $this->clientrecord["email"];

		if($phoneemail)
			$address .= "\n\n".$phoneemail;

		$clientWidth = ($pdf->paperwidth - $pdf->leftmargin - $pdf-> rightmargin)/2;
		$pdf->setXY($pdf->leftmargin, $nextY + 0.375);
		$pdf->SetFont("Arial","B",10);
		$pdf->Cell($clientWidth,.16,$displayName, $pdf->borderDebug,2,"L");
		$pdf->SetFont("Arial","B",9);
		$pdf->MultiCell($clientWidth,.13,$address, $pdf->borderDebug);


		//now for item headers
		$pdf->SetXY($pdf->leftmargin, 2.375 + $pdf->topmargin);
		$pdf->setStyle("header");

		$x = $pdf->GetX();
		$y = $pdf->GetY() + 0.17;
		$this->itmeAreaHeight = 5.5;
		$pdf->Line($x, $y, $x, $y + $this->itmeAreaHeight);

		foreach($this->itemColumns as $column){

			if($column["nl"] == 0){

				$x += $column["size"];
				$pdf->SetDrawColor(120,120,120);
				$pdf->Line($x -0.01, $y, $x -0.01, $y + $this->itmeAreaHeight);
				$pdf->SetDrawColor(0,0,0);

				$column["size"] -= 0.02;

			}//endif

			$pdf->Cell($column["size"], 0.17, $column["title"], 1, $column["nl"], $column["align"], 1);

			if($column["nl"] == 0)
				$pdf->SetX($pdf->GetX() + 0.02);

		}//endforeach
		$x = $pdf->paperwidth - $pdf->rightmargin;
		$pdf->Line($x, $y, $x, $y + $this->itmeAreaHeight);
		$pdf->Line($pdf->leftmargin, $y + $this->itmeAreaHeight, $x, $y + $this->itmeAreaHeight);

		$pdf->setStyle("normal");

	}//end method


	function _addItems(){

		$pdf = &$this->pdf;

		$itemsArray = $this->_getItems();

		$itemHeight = 0.25;
		$currHeight = 0;


		foreach($itemsArray as $item){

			$pdf->SetX($pdf->leftmargin);

			if($currHeight >= $this->itmeAreaHeight){

				//too many items requires multiple pages
				$this->page++;
				$this->_addPage();

			}//end if

			foreach($this->itemColumns as $column){

				$pdf->Cell($column["size"], $itemHeight, $item[$column["field"]], $pdf->borderDebug, $column["nl"], $column["align"]);

			}//endforeach

			$currHeight += $itemHeight;

		}//end foreach

	}//end method


	function _getItems(){

		$querystatement = "
			SELECT
				`aritems`.`id`,
				`aritems`.`type`,
				IF(`invoices`.`id`,`invoices`.`id`,`receipts`.`id`) AS `relatedid`,
				`aritems`.`itemdate`,
				`aritems`.`amount`,
				`aritems`.`amount` - `paid` AS due
			FROM
				(`aritems` LEFT JOIN `invoices` ON `aritems`.`relatedid` = `invoices`.`uuid`) LEFT JOIN `receipts` ON `aritems`.`relatedid` = `receipts`.`uuid`
			WHERE
				`aritems`.posted = 1
				AND `aritems`.clientid = '".mysql_real_escape_string($this->clientrecord["clientid"])."'
		";

		if(!$this->showClosed)
			$querystatement .= "
				AND `aritems`.`status` = 'open'";

		$querystatement .= "
				AND `aritems`.itemdate <= '".dateToString($this->statementDate,"SQL")."'
			ORDER BY
				`aritems`.itemdate,
				`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
				id,
				receiptdate,
				amount
			FROM
				receipts
			WHERE
				clientid = '".mysql_real_escape_string($this->clientrecord["clientid"])."'
				AND receiptdate <= '".dateToString($this->statementDate, "SQL")."'
				AND receiptdate >= '".$lastDays."'
				AND 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;

	}//end method


	function output($to = "screen"){

		$filename = 'Client_Statement_'.date('Ymd').'.pdf';
		$filename = cleanFilename($filename);
		$this->pdf->output($filename, 'D');

	}//end method


	function showOptions(){

		include("include/fields.php");
		global $phpbms;
		$db = &$this->db;

		$phpbms->cssIncludes[] = "pages/aritems_clientstatement.css";
		$phpbms->jsIncludes[] = "modules/bms/javascript/aritem_clientstatement.js";
		$phpbms->showMenu = false;

		$formSubmit = htmlentities($_SERVER['REQUEST_URI']);

		$theform = new phpbmsForm();

		$theinput = new inputDatePicker("statementdate", dateToString(mktime(0,0,0), "SQL"), "statement date", true);
		$theform->addField($theinput);

		$theinput = new inputCheckbox("showpayments",  true , "show new payments");
		$theform->addField($theinput);

		$theinput = new inputCheckbox("showclosed", false , "show closed items (history)");
		$theform->addField($theinput);

		$theform->jsMerge();

		include("header.php");

		?>

		<div class="bodyline" id="dialog">
			<h1><span>Client AR Statement</span></h1>
			<form action="<?php echo $formSubmit ?>" id="record" method="post">
			<input type="hidden" id="command" name="command" />

				<fieldset>
					<legend>options</legend>

					<p><?php $theform->showField("statementdate");?></p>

					<p><?php $theform->showField("showclosed");?></p>

					<p><?php $theform->showField("showpayments");?></p>

				</fieldset>

				<fieldset>
					<legend>Records</legend>

					<p>
						<input type="radio" name="selrecords" id="allOpen" value="allOpen" class="radiochecks" checked="checked"/>
						<label for="allOpen">all clients with open items</label>
					</p>

					<p>
						<input type="radio" name="selrecords" id="allClients" value="allClients" class="radiochecks"/>
						<label for="allClients">all clients with any credit history</label>
					</p>

					<p>
						<input type="radio" name="selrecords" id="fromPrint" value="fromPrint" class="radiochecks"/>
						<label for="fromPrint">clients based on records from print screen</label>
					</p>

				</fieldset>

				<p align="right">
					<button type="button" class="Buttons" id="printButton">print</button>
					<button type="button" class="Buttons" id="cancelButton">cancel</button>
				</p>
			</form>
		</div>

		<?php
		include("footer.php");

	}//end method


	function processDialog($variables){

		if(isset($variables["statementdate"]))
			$this->statementDate = stringToDate($variables["statementdate"]);

		$this->showClosed = isset($variables["showclosed"]);

		$this->showPayments = isset($variables["showpayments"]);

		if(!isset($variables["selrecords"]))
			$variables["selrecords"] = "allOpen";

		switch($variables["selrecords"]){

			case "allOpen":
				$this->generate();
				break;

			case "allClients":
				$this->generate("clients.hascredit = 1");
				break;

			case "fromPrint":
				$this->setupFromPrintScreen();
				$this->generate();
				break;

		}//endswitch

		$this->output();

	}//end method

}//end class




//PROCESSING
//=============================================================================
if(!isset($noOutput)){

    //IE needs caching to be set to private in order to display PDFS
    session_cache_limiter('private');

    //set encoding to latin1 (fpdf doesnt like utf8)
    $sqlEncoding = "latin1";
    require_once("../../../include/session.php");

    checkForReportArguments();

    $report = new aritemsClientStatements($db, $_GET["rid"], $_GET["tid"]);

	if(isset($_GET["cmd"])){

		$_POST["command"] = $_GET["cmd"];
		$_POST["statementdate"] = $_GET["sd"];

	}//endif

	if(isset($_POST["command"])) {

		switch($_POST["command"]){

			case "print":
				$report->processDialog($_POST);
				break;

		}//endswitch

	} else {

		$report->showOptions();

	}//end if

}//end if

/**
 * When adding a new report record, the add/edit needs to know what the class
 * name is so that it can instantiate it, and grab it's default settings.
 */
if(isset($addingReportRecord))
    $reportClass ="aritemsClientStatements";

?>
Return current item: PhpBMS