Changeset 693 for trunk/phpbms/modules/bms/report/products_saleshistory.php

Timestamp:

12/31/09 13:36:45 (2 years ago)

Author:

brieb

Message:

• Introduced administratively changeable settings to indvidual reports
• Modified all reports to work with new settings system
• Altered invoice and line item total reports to accept parameters from reportsettings: You can now bypass the grouping/column dialog by providing the infrmation administratively
• Altered all sales order PDF reports to accept parameters from reportsettings: You can now administratively change certain aspects of the print outs, including what parts of the top of the report to show, and what to title the report
• Altered label reports to accept parameters from reportsettings: printed data as well

as label measurements and layout are now defined by administratively

• Altered export and tableprint reports to accept parameters from reportsettings: You can specify individual columns and from table instead of just the defaults (all fields, main table only)
• Added var_dump-esque 'debug' function for development purposes to common functions
• Integrated FPDI functionality with sales order PDF reports: It is now possible to use a PDF saved in the files table as a background template for your invoices (e.g. watermarks)

Files:

• trunk/phpbms/modules/bms/report/products_saleshistory.php (modified) (3 diffs)

trunk/phpbms/modules/bms/report/products_saleshistory.php

@@ -38 +38 @@
 */
 
-if(!isset($fromProduct)) {
-        require_once("../../../include/session.php");
-}
-
-class salesHistoryReport{
+if(!class_exists("phpbmsReport"))
+    include("../../../report/report_class.php");
+
+class salesHistoryReport extends phpbmsReport{
+
+    var $fromDate;
+    var $toDate;
+    var $view;
+    var $productQueryresult;
+    var $dataPrint;
+
+    function salesHistoryReport($db, $reportUUID, $tabledefUUID){
+
+        parent::phpbmsReport($db, $reportUUID, $tabledefUUID);
+
+        //$this->checkForDefaultSettings();
+
+    }//end function init
+
+
+    function initialize($variables){
+
+        if(!isset($variables["fromdate"]) || !isset($variables["todate"]) || !isset($variables["status"]))
+            $error = new appError(300, "Missing Passed Parameters");
+
+        $this->fromDate = $variables["fromdate"];
+        $this->toDate = $variables["todate"];
+        $this->view = $variables["status"];
+
+        $this->dataPrint = $_SESSION["printing"]["dataprint"];
+
+        if(!$this->sortOrder)
+            $this->sortOrder = "  ORDER BY products.partnumber ";
+
+        if(!$this->whereClause)
+            $this->whereClause = "products.id!=-1";
+
+        $this->whereClause = " WHERE (".$this->whereClause.") ";
+
+        $querystatement = "
+            SELECT
+                products.uuid,
+                products.partnumber,
+                products.partname
+            FROM
+                products
+            ".$this->whereClause.$this->sortOrder;
+
+        $this->productQueryresult = $this->db->query($querystatement);
+
+    }//end function initialize
+
+
+    function generateSingleHistory($productUUID){
+
+        $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 = "
+            SELECT
+                `invoices`.`id`,
+                `invoices`.`orderdate`,
+                `invoices`.`invoicedate`,
+                IF(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) AS `client`,
+                `lineitems`.`quantity` AS `qty`,
+                `lineitems`.`unitprice`*`lineitems`.`quantity` AS `extended`,
+                `lineitems`.`unitprice` AS `price`,
+                `lineitems`.`unitcost` AS `cost`,
+                `lineitems`.`unitcost`*`lineitems`.`quantity` AS extendedcost
+            FROM
+                ((products INNER JOIN lineitems on products.uuid=lineitems.productid)
+                INNER JOIN `invoices` ON lineitems.invoiceid=invoices.id)
+                INNER JOIN `clients` on `invoices`.`clientid`=`clients`.`uuid`
+            WHERE
+                `products`.`uuid`='".$productUUID."'
+                AND
+                `invoices`.".$searchDate.">='".$mysqlFromDate."'
+                AND
+                `invoices`.".$searchDate."<='".$mysqlToDate."'
+                AND
+                ".$theStatus."
+            ORDER BY
+                `invoices`.`invoicedate`,
+                `invoices`.`orderdate`
+            ";
+
+        $queryresult = $this->db->query($querystatement);
+
+        ob_start();
+
+        ?>
+        <table border="0" cellpadding="3" cellspacing="0">
+            <thead>
+                <tr>
+                    <th align="center" nowrap="nowrap" >ID</th>
+                    <th align="center" nowrap="nowrap" >Order Date</th>
+                    <th align="center" nowrap="nowrap" >Invoice. 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>
+            </thead>
+        <?php
+
+            $totalextended = 0;
+            $totalcostextended = 0;
+            $totalquantity = 0;
+            $avgprice = 0;
+            $avgcost = 0;
+
+            $numrows = $this->db->numRows($queryresult);
+
+            while ($therecord = $this->db->fetchArray($queryresult)){
+
+                $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"]):"&nbsp;" ?></td>
+                <td align="center" nowrap="nowrap"><?php echo $therecord["invoicedate"]?formatFromSQLDate($therecord["invoicedate"]):"&nbsp;" ?></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 }//endwhile ?>
+            <tr>
+                <td align="center" class="grandtotals">&nbsp;</td>
+                <td align="center" class="grandtotals">&nbsp;</td>
+                <td class="grandtotals">&nbsp;</td>
+                <td class="grandtotals">&nbsp;</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
+
+        $output = ob_get_contents();
+        ob_end_clean();
+
+        return $output;
+
+    }//end function generateSingleHistory
+
+
+
+    function generate(){
+
+
+        ob_start();
+        ?>
+
+        <h1>Product Sales History</h1>
+
+        <ul>
+            <li>
+                source:<br />
+                <?php echo formatVariable($this->dataPrint); ?>
+            </li>
+            <li>
+                date generated:<br />
+                <?php echo dateToString(mktime())." ".timeToString(mktime()); ?>
+            </li>
+            <li>
+                view:<br />
+                <?php echo $this->view; ?>
+            </li>
+            <li>
+                from:<br />
+                <?php echo $this->fromDate; ?>
+            </li>
+            <li>
+                to:<br />
+                <?php echo $this->toDate; ?>
+            </li>
+        </ul>
+
+        <?php
+
+        $this->reportOutput = ob_get_contents();
+        ob_end_clean();
+
+        while($therecord = $this->db->fetchArray($this->productQueryresult)){
+
+            $this->reportOutput .= '<h2>'.$therecord["partnumber"].'<br />'.$therecord["partname"].'</h2>';
+
+            $this->reportOutput .= $this->generateSingleHistory($therecord["uuid"]);
+
+        }//endwhile
+
+    }//end function generate
+
+
+    function output(){
+
+        global $phpbms;
+        $db = &$this->db;
+
+        $phpbms->cssIncludes[] = "reports.css";
+        $phpbms->cssIncludes[] = "pages/bms/clienthistoryreport.css";
+
+        $phpbms->showMenu = false;
+        $phpbms->showFooter = false;
+
+        include("header.php");
+
+        echo $this->reportOutput;
+
+        include("footer.php");
+
+    }//end function output
+
+
+    function displayOptions(){
+
+        global $phpbms;
+        $db = &$this->db;
+
+        require("include/fields.php");
+
+        $pageTitle = "Product Sales History";
+        $phpbms->cssIncludes[] = "pages/historyreports.css";
+        $phpbms->showMenu = false;
+
+        //Form Elements
+        //==============================================================
+        $theform = new phpbmsForm();
+
+        $thedate = dateToString( mktime(0,0,0,date("m"),1),"SQL" );
+        $theinput = new inputDatePicker("fromdate", $thedate, "from",true);
+        $theform->addField($theinput);
+
+        $thedate = dateToString( mktime(0,0,0,date("m")+1,0,date("Y")), "SQL" );
+        $theinput = new inputDatePicker("todate", $thedate, "to",true);
+        $theform->addField($theinput);
+
+        $theform->jsMerge();
+        //==============================================================
+        //End Form Elements
+
+        include("header.php");
+        ?>
+        <form action="<?php echo str_replace("&", "&amp;", $_SERVER["REQUEST_URI"]); ?>" method="post" name="totals" onsubmit="return validateForm(this)">
+
+            <div class="bodyline" id="reportOptions">
+
+                <h1 id="topTitle"><span>Product Sales History Options</span></h1>
+
+                <fieldset>
+                    <legend>time frame</legend>
+
+                    <p id="fromP"><?php $theform->showField("fromdate");?></p>
+
+                    <p><?php $theform->showField("todate");?></p>
+                </fieldset>
+
+                <p>
+                    <label for="status">include products from...<br /></label>
+                    <select id="status" name="status">
+                        <option value="Orders and Invoices" selected="selected">Orders and Invoices</option>
+                        <option value="Invoices">Invoices</option>
+                        <option value="Orders">Orders</option>
+                    </select>
+                </p>
+
+                <div align="right">
+                    <input name="command" type="submit" class="Buttons" id="print" value="print" />
+                    <input name="cancel" type="button" class="Buttons" id="cancel" value="cancel" onclick="window.close();" />
+                </div>
+            </div>
+        </form>
+
+        <?php
+        include("footer.php");
+
+    }//end function displayOptions
+
+}//end class  salesHistoryReport
+
+
+/**
+ * PROCESSING
+ * =============================================================================
+ */
+if(!isset($noOutput)){
+
+    //IE needs caching to be set to private in order to display PDFS
+    session_cache_limiter('private');
+
+    require_once("../../../include/session.php");
+
+    checkForReportArguments();
+
+    $report = new salesHistoryReport($db, $_GET["rid"], $_GET["tid"]);
+
+    if(!isset($_POST["command"]) && !isset($_GET["status"]))
+        $report->displayOptions();
+    else{
+
+        if(isset($_GET["status"])){
+
+            $_POST["status"] = $_GET["status"];
+            $_POST["fromdate"] = $_GET["fromdate"];
+            $_POST["todate"] = $_GET["todate"];
+
+        }//endif
+
+        //need to set post variables here
+
+        $report->setupFromPrintScreen();
+        $report->initialize($_POST);
+        $report->generate();
+        $report->output();
+
+    }//endif
+
+
+}//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 ="salesHistoryReport";
+
+
+
+
+
+
+
+
+
+class s_salesHistoryReport{
 
         var $whereclause="";
@@ -187 +550 @@
 
         function showReport(){
-                
+
         ?>
 <head>
@@ -261 +624 @@
         }//end method
 }//end class
-
-if(isset($_POST["command"])){
-        $myreport= new salesHistoryReport();
-        $myreport->initialize($_POST,$db);
-
-        $myreport->showReport();
-} else {
-
-                require("include/fields.php");
-
-                $pageTitle = "Product Sales History";
-                $phpbms->cssIncludes[] = "pages/historyreports.css";
-                $phpbms->showMenu = false;
-
-                //Form Elements
-                //==============================================================
-                $theform = new phpbmsForm();
-
-
-                $thedate = dateToString( mktime(0,0,0,date("m"),1),"SQL" );
-                $theinput = new inputDatePicker("fromdate", $thedate, "from",true);
-                $theform->addField($theinput);
-
-                $thedate = dateToString( mktime(0,0,0,date("m")+1,0,date("Y")), "SQL" );
-                $theinput = new inputDatePicker("todate", $thedate, "to",true);
-                $theform->addField($theinput);
-
-                $theform->jsMerge();
-                //==============================================================
-                //End Form Elements
-
-                include("header.php");
 ?>
-<form action="<?php echo $_SERVER["PHP_SELF"]?>" method="post" name="totals" onsubmit="return validateForm(this)">
-<div class="bodyline" id="reportOptions">
-        <h1 id="topTitle"><span>Product Sales History Options</span></h1>
-                <fieldset>
-                        <legend>time frame</legend>
-
-                        <p id="fromP"><?php $theform->showField("fromdate");?></p>
-
-                        <p><?php $theform->showField("todate");?></p>
-                </fieldset>
-
-                <p>
-                        <label for="status">include line items from...<br /></label>
-                   <select id="status" name="status">
-                                <option value="Orders/Invoices" selected="selected">Orders/Invoices</option>
-                                <option value="Invoices">Invoices</option>
-                                <option value="Orders">Orders</option>
-                   </select>
-                </p>
-
-                <div align="right">
-                        <input name="command" type="submit" class="Buttons" id="print" value="print" />
-                        <input name="cancel" type="button" class="Buttons" id="cancel" value="cancel" onclick="window.close();" />
-                </div>
-</div>
-</form>
-<?php
-include("footer.php");
-}?>