Location: PHPKode > projects > PhpBMS > phpbms/modules/bms/report/lineitems_totals.php
<?php
/*
 $Rev: 704 $ | $LastChangedBy: brieb $
 $LastChangedDate: 2010-01-01 23:10:02 -0700 (Fri, 01 Jan 2010) $
 +-------------------------------------------------------------------------+
 | 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 totalReport extends phpbmsReport{

	var $selectcolumns;
	var $selecttable;
	var $group = "";
	var $showinvoices = false;
	var $showlineitems = false;
	var $padamount = 20;
        var $title = "Totals";

	function totalReport($db, $reportUUID, $tabledefUUID, $variables = NULL){

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

            // first we define the available groups
            $this->addGroup("Invoice ID","invoices.id"); //0
            $this->addGroup("Product","concat(products.partnumber,' - ',products.partname)"); //1
            $this->addGroup("Product Category","concat(productcategories.id,' - ',productcategories.name)",NULL,"INNER JOIN productcategories ON products.categoryid=productcategories.uuid"); //2

            $this->addGroup("Invoice Date - Year","YEAR(invoices.invoicedate)"); //3
            $this->addGroup("Invoice Date - Quarter","QUARTER(invoices.invoicedate)"); //4
            $this->addGroup("Invoice Date - Month","MONTH(invoices.invoicedate)"); //5
            $this->addGroup("Invoice Date","invoices.invoicedate","date"); //6

            $this->addGroup("Order Date - Year","YEAR(invoices.orderdate)"); //7
            $this->addGroup("Order Date - Quarter","QUARTER(invoices.orderdate)");//8
            $this->addGroup("Order Date - Month","MONTH(invoices.orderdate)");//9
            $this->addGroup("Order Date","invoices.orderdate","date");//10

            $this->addGroup("Client","if(clients.lastname!='',concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company)");//11

            $this->addGroup("Client Sales Person","concat(salesPerson.firstname,' ',salesPerson.lastname)",NULL, "LEFT JOIN users AS salesPerson ON clients.salesmanagerid = salesPerson.id");//12

            $this->addGroup("Client Lead Source","clients.leadsource");//13

            $this->addGroup("Invoice Lead Source","invoices.leadsource");//14

            $this->addGroup("Payment Method","paymentmethods.name");//15

            $this->addGroup("Shipping Method","shippingmethods.name");//16
            $this->addGroup("Invoice Shipping Country","invoices.shiptocountry");//17
            $this->addGroup("Invoice Shipping State / Province","invoices.shiptostate");//18
            $this->addGroup("Invoice Shipping Postal Code","invoices.shiptopostalcode");//19
            $this->addGroup("Invoice Shipping City","invoices.shiptocity");//20

            $this->addGroup("Web Order","invoices.weborder","boolean");//21

            $this->addGroup("Invoice billing Country","invoices.country");//22
            $this->addGroup("Invoice Billing State / Province","invoices.state");//23
            $this->addGroup("Invoice Billing Postal Code","invoices.postalcode");//24
            $this->addGroup("Invoice Billing City","invoices.city");//25

            //next we do the columns
            $this->addColumn("Record Count","count(lineitems.id)");//0
            $this->addColumn("Extended Price","sum(lineitems.unitprice*lineitems.quantity)","currency");//1
            $this->addColumn("Average Extended Price","avg(lineitems.unitprice*lineitems.quantity)","currency");//2
            $this->addColumn("Unit Price","sum(lineitems.unitprice)","currency");//3
            $this->addColumn("Average Unit Price","avg(lineitems.unitprice)","currency");//4
            $this->addColumn("Quantity","sum(lineitems.quantity)","real");//5
            $this->addColumn("Average Quantity","avg(lineitems.quantity)","real");//6
            $this->addColumn("Unit Cost","sum(lineitems.unitcost)","currency");//7
            $this->addColumn("Average Unit Cost","avg(lineitems.unitcost)","currency");//8
            $this->addColumn("Extended Cost","sum(lineitems.unitcost*lineitems.quantity)","currency");//9
            $this->addColumn("Average Extended Cost","avg(lineitems.unitcost*lineitems.quantity)","currency");//10
            $this->addColumn("Unit Weight","sum(lineitems.unitweight)","real");//11
            $this->addColumn("Average Unit Weight","avg(lineitems.unitweight)","real");//12
            $this->addColumn("Extended Unit Weight","sum(lineitems.unitweight*lineitems.quantity)","real");//13
            $this->addColumn("Extended Average Unit Weight","avg(lineitems.unitweight*lineitems.quantity)","real");//14

            //change
            $this->selecttable="(((((lineitems LEFT JOIN products ON lineitems.productid=products.uuid)
                                                            INNER JOIN invoices ON lineitems.invoiceid=invoices.id)
                                                            INNER JOIN clients ON invoices.clientid=clients.uuid)
                                                            LEFT JOIN shippingmethods ON shippingmethods.uuid=invoices.shippingmethodid)
                                                            LEFT JOIN paymentmethods ON paymentmethods.uuid=invoices.paymentmethodid)
                                                            ";

	}//end function totalReport


        function processFromPost($variables){

            $tempArray = explode("::", $variables["columns"]);

            foreach($tempArray as $id)
                    $this->selectcolumns[] = $this->columns[$id];

            $this->selectcolumns = array_reverse($this->selectcolumns);

            if($variables["groupings"] !== ""){

                $this->group = explode("::",$variables["groupings"]);
                $this->group = array_reverse($this->group);

            } else
                $this->group = array();

            foreach($this->group as $grp)
                if($this->groupings[$grp]["table"])
                    $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")";

            switch($variables["showwhat"]){

                case "invoices":
                    $this->showinvoices = true;
                    $this->showlineitems = false;
                    break;

                case "lineitems":
                    $this->showinvoices = true;
                    $this->showlineitems = true;
                    break;

                default:
                    $this->showinvoices = false;
                    $this->showlineitems = false;

            }// endswitch

            if($variables["reporttitle"])
                $this->title = $variables["reporttitle"];

        }//end function processFromPost


        function processFromSettings(){

            foreach($this->settings as $key=>$value)
                if(strpos($key, "column") === 0)
                    $this->selectcolumns[substr($key,6)-1] = $this->columns[$value];

            ksort($this->selectcolumns);
            $this->selectcolumns = array_reverse($this->selectcolumns);

            $this->group = array();

            foreach($this->settings as $key=>$value)
                if(strpos($key, "group") === 0)
                    $this->group[substr($key,5)-1] = $value;

            ksort($this->group);
            $this->group = array_reverse($this->group);

            foreach($this->group as $grp)
                if($this->groupings[$grp]["table"])
                    $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")";


            if(isset($this->settings["showWhat"]))
                $showWhat = $this->settings["showWhat"];
            else
                $showWhat = "";

            switch($showWhat){

                case "invoices":
                    $this->showinvoices = true;
                    $this->showlineitems = false;
                    break;

                case "lineitems":
                    $this->showinvoices = true;
                    $this->showlineitems = true;
                    break;

                default:
                    $this->showinvoices = false;
                    $this->showlineitems = false;

            }// endswitch

            if(isset($this->settings["reportTitle"]))
                $this->title = $this->settings["reportTitle"];

        }//end function processFromSettings


	function addGroup($name, $field, $format = NULL, $tableAddition = NULL){

            $temp = array();
            $temp["name"] = $name;
            $temp["field"] = $field;
            $temp["format"] = $format;
            $temp["table"] = $tableAddition;

            $this->groupings[] = $temp;

	}//end method


	function addColumn($name, $field, $format = NULL){

            $temp = array();
            $temp["name"] = $name;
            $temp["field"] = $field;
            $temp["format"] = $format;

            $this->columns[] = $temp;

	}//end method


	function showReportTable(){

            ?><table border="0" cellspacing="0" cellpadding="0">
            <tr>
                    <th>&nbsp;</th>
            <?php
                    foreach($this->selectcolumns as $thecolumn){
                            ?><th align="right"><?php echo $thecolumn["name"]?></th><?php
                    }//end foreach
            ?>
            </tr>
            <?php $this->showGroup($this->group,"",10);?>
            <?php $this->showGrandTotals();?>
            </table>
            <?php

	}//end function showReportTable();


	function showGrandTotals(){

            $querystatement="SELECT ";
            foreach($this->selectcolumns as $thecolumn)
                    $querystatement.=$thecolumn["field"]." AS `".$thecolumn["name"]."`,";
            $querystatement.=" count(lineitems.id) as thecount ";
            $querystatement.=" FROM ".$this->selecttable.$this->whereClause;
            $queryresult=$this->db->query($querystatement);

            $therecord=$this->db->fetchArray($queryresult);
            ?>
            <tr>
                    <td class="grandtotals" align="right">Totals: (<?php echo $therecord["thecount"]?>)</td>
                    <?php
                            foreach($this->selectcolumns as $thecolumn){
                                    ?><td align="right" class="grandtotals"><?php echo formatVariable($therecord[$thecolumn["name"]],$thecolumn["format"])?></td><?php
                            }//end foreach
                    ?>
            </tr>
            <?php

        }//end function showGrandTotals


	function showGroup($group,$where,$indent){

		if(!$group){
			if($this->showlineitems)
				$this->showLineItems($where,$indent+$this->padamount);
		} else {
			$groupby = array_pop($group);


			$querystatement="SELECT ";
			foreach($this->selectcolumns as $thecolumn)
				$querystatement.=$thecolumn["field"]." AS `".$thecolumn["name"]."`,";
			$querystatement .= $this->groupings[$groupby]["field"]." AS thegroup, count(lineitems.id) as thecount ";
			$querystatement .= " FROM ".$this->selecttable.$this->whereClause.$where." GROUP BY ".$this->groupings[$groupby]["field"];
			$queryresult=$this->db->query($querystatement);

			while($therecord=$this->db->fetchArray($queryresult)){

				$showbottom=true;
				if($group or $this->showinvoices) {
					$showbottom=false;
					?>
					<tr><td colspan="<?php echo (count($this->selectcolumns)+1)?>" class="group" style="padding-left:<?php echo ($indent+2)?>px;"><?php echo $this->groupings[$groupby]["name"].": <strong>".formatVariable($therecord["thegroup"],$this->groupings[$groupby]["format"])."</strong>"?>&nbsp;</td></tr>
					<?php
				}//endif

				if($group) {
					$whereadd = $where." AND (".$this->groupings[$groupby]["field"]."= \"".$therecord["thegroup"]."\"";
					if(!$therecord["thegroup"])
						$whereadd .= " OR ISNULL(".$this->groupings[$groupby]["field"].")";
					$whereadd .= ")";
					$this->showGroup($group,$whereadd,$indent+$this->padamount);
				} elseif($this->showlineitems) {
					if($therecord["thegroup"])
						$this->showLineItems($where." AND (".$this->groupings[$groupby]["field"]."= \"".$therecord["thegroup"]."\")",$indent+$this->padamount);
					else
						$this->showLineItems($where." AND (".$this->groupings[$groupby]["field"]."= \"".$therecord["thegroup"]."\" or isnull(".$this->groupings[$groupby]["field"].") )",$indent+$this->padamount);
				}//endif

				?>
				<tr>
					<td width="100%" style=" <?php
						echo "padding-left:".($indent+2)."px";
					?>" class="groupFooter">
						<?php echo $this->groupings[$groupby]["name"].": <strong>".formatVariable($therecord["thegroup"],$this->groupings[$groupby]["format"])."</strong>&nbsp;";?>
					</td>
					<?php
						foreach($this->selectcolumns as $thecolumn){
							?><td align="right" class="groupFooter"><?php echo formatVariable($therecord[$thecolumn["name"]],$thecolumn["format"])?></td><?php
						}//end foreach
					?>
				</tr>
				<?php
			}//end while
		}//endif
	}//end function


	function showLineItems($where,$indent){

		$querystatement = "
			SELECT
				`lineitems`.`invoiceid`,
				IF(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) AS `thename`,
				`invoices`.`invoicedate`,
				`invoices`.`orderdate`,
				`lineitems`.`id`,
				`products`.`partnumber`,
				`products`.`partname`,
				`quantity`,
				`lineitems`.`unitprice`,
				`quantity`*`lineitems`.`unitprice` AS `extended`
			FROM ".$this->selecttable.$this->whereClause.$where." GROUP BY lineitems.id
		";

		$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"]?>&nbsp;&nbsp;<?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>&nbsp;</td>"
			?>
			</tr><?php
		}// endif

	}//end method


	function showReport(){

            if(!$this->whereClause)
                $this->whereClause = "invoices.id!=-1";

            $this->whereClause = " WHERE ".$this->whereClause;

            $pageTitle = $this->title;

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="<?php echo APP_PATH ?>common/stylesheet/<?php echo STYLESHEET ?>/pages/totalreports.css" rel="stylesheet" type="text/css" />
<title><?php echo $pageTitle?></title>
</head>

<body>
	<div id="toprint">
		<h1><span><?php echo $pageTitle?></span></h1>
		<h2>Source: <?php echo $_SESSION["printing"]["dataprint"]?></h2>
		<h2>Date: <?php echo dateToString(mktime())." ".timeToString(mktime())?></h2>

		<?php $this->showReportTable();?>
	</div>
</body>
</html><?php

	}// end method


	function showOptions($what){
		?><option value="0">----- Choose One -----</option>
		<?php
		$i=0;

		foreach($this->$what as $value){
			?><option value="<?php echo $i+1; ?>"><?php echo $value["name"];?></option>
			<?php
			$i++;
		}// endforeach

	}//end mothd


	function showSelectScreen(){

        global  $phpbms;

        $pageTitle="Line Items Total";
        $phpbms->showMenu = false;
        $phpbms->cssIncludes[] = "pages/totalreports.css";
        $phpbms->jsIncludes[] = "modules/bms/javascript/totalreports.js";

        include("header.php");

        ?>

        <div class="bodyline">
            <h1>Line Items Total Options</h1>
            <form id="GroupForm" action="<?php echo htmlentities($_SERVER["REQUEST_URI"]) ?>" method="post" name="GroupForm">

                <fieldset>

                    <legend>report</legend>
                    <p>
                        <label for="reporttitle">report title</label><br />
                        <input type="text" name="reporttitle" id="reporttitle" size="45"/>
                    </p>

		</fieldset>

                <fieldset>

                    <legend>groupings</legend>
                    <input id="groupings" type="hidden" name="groupings"/>
                    <div id="theGroups">
                        <div id="Group1">
                            <select id="Group1Field">
                                <?php $this->showOptions("groupings")?>
                            </select>
                            <button type="button" id="Group1Minus" class="graphicButtons buttonMinusDisabled"><span>-</span></button>
                            <button type="button" id="Group1Plus" class="graphicButtons buttonPlus"><span>+</span></button>
                        </div>
                    </div>

                </fieldset>

		<fieldset>

			<legend>columns</legend>
			<input id="columns" type="hidden" name="columns"/>
			<div id="theColumns">
				<div id="Column1">
					<select id="Column1Field">
						<?php $this->showOptions("columns")?>
					</select>
					<button type="button" id="Column1Minus" class="graphicButtons buttonMinusDisabled"><span>-</span></button>
					<button type="button" id="Column1Plus" class="graphicButtons buttonPlus"><span>+</span></button>
				</div>
			</div>
		</fieldset>

		<fieldset>
			<legend>Options</legend>
			<p>
			<label for="showwhat">information shown</label><br />
			<select name="showwhat" id="showwhat">
				<option selected="selected" value="totals">Totals Only</option>
				<option value="invoices">Invoices</option>
				<option value="lineitems">Invoices &amp; Line Items</option>
			</select>
			</p>
		</fieldset>

                <p align="right">
                    <button id="print" type="button" class="Buttons">Print</button>
                    <button id="cancel" type="button" class="Buttons">Cancel</button>
                </p>

            </form>
        </div>

        <?php

        include("footer.php");
    }//end method

}//end class


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

    require("../../../include/session.php");

    checkForReportArguments();

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

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

        $report->setupFromPrintScreen();
        $report->processFromPost($_POST);
        $report->showReport();

    } elseif(isset($report->settings["column1"])){

        $report->setupFromPrintScreen();
        $report->processFromSettings();
        $report->showReport();

    } else {

        $report->showSelectScreen();

    }//endif

}//endif

/**
 * 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 ="totalReport";

?>
Return current item: PhpBMS