phpBMS

Show
Ignore:
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:
1 modified

Legend:

Unmodified
Added
Removed

  • trunk/phpbms/modules/bms/report/invoices_totals.php

    r611 r693  
    3737 +-------------------------------------------------------------------------+ 
    3838*/ 
    39  
    40 require("../../../include/session.php"); 
    41  
    42 class totalReport{ 
    43  
    44         var $selectcolumns; 
    45         var $selecttable; 
    46         var $whereclause=""; 
    47         var $group=""; 
    48         var $showinvoices=false; 
    49         var $showlineitems=false; 
    50         var $padamount=20; 
    51  
    52         function totalReport($db,$variables = NULL){ 
    53                 $this->db = $db; 
    54  
    55                 // first we define the available groups 
    56                 $this->addGroup("Invoice Date - Year","YEAR(invoices.invoicedate)"); //0 
    57                 $this->addGroup("Invoice Date - Quarter","QUARTER(invoices.invoicedate)"); //1 
    58                 $this->addGroup("Invoice Date - Month","MONTH(invoices.invoicedate)"); //2 
    59                 $this->addGroup("Invoice Date","invoices.invoicedate","date"); //3 
    60  
    61                 $this->addGroup("Order Date - Year","YEAR(invoices.orderdate)"); //4 
    62                 $this->addGroup("Order Date - Quarter","QUARTER(invoices.orderdate)");//5 
    63                 $this->addGroup("Order Date - Month","MONTH(invoices.orderdate)");//6 
    64                 $this->addGroup("Order Date","invoices.orderdate","date");//7 
    65  
    66                 $this->addGroup("Required Date - Year","YEAR(invoices.requireddate)");//8 
    67                 $this->addGroup("Required Date - Quarter","QUARTER(invoices.requireddate)");//9 
    68                 $this->addGroup("Required Date - Month","MONTH(invoices.requireddate)");//10 
    69                 $this->addGroup("Required Date","invoices.requireddate","date");//11 
    70  
    71                 $this->addGroup("Client","if(clients.lastname!='',concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company)");//12 
    72  
    73                 $this->addGroup("Client Sales Person","concat(salesPerson.firstname,' ',salesPerson.lastname)",NULL, "LEFT JOIN users AS salesPerson ON clients.salesmanagerid = salesPerson.uuid");//13 
    74  
    75                 $this->addGroup("Client Lead Source","clients.leadsource");//14 
    76  
    77                 $this->addGroup("Invoice Lead Source","invoices.leadsource");//15 
    78  
    79                 $this->addGroup("Payment Method","paymentmethods.name");//16 
    80  
    81                 $this->addGroup("Shipping Method","shippingmethods.name");//17 
    82                 $this->addGroup("Invoice Shipping Country","invoices.shiptocountry");//18 
    83                 $this->addGroup("Invoice Shipping State / Province","invoices.shiptostate");//19 
    84                 $this->addGroup("Invoice Shipping Postal Code","invoices.shiptopostalcode");//20 
    85                 $this->addGroup("Invoice Shipping City","invoices.shiptocity");//21 
    86  
    87                 $this->addGroup("Web Order","invoices.weborder","boolean");//22 
    88  
    89                 $this->addGroup("Invoice billing Country","invoices.country");//23 
    90                 $this->addGroup("Invoice Billing State / Province","invoices.state");//24 
    91                 $this->addGroup("Invoice Billing Postal Code","invoices.postalcode");//25 
    92                 $this->addGroup("Invoice Billing City","invoices.city");//26 
    93  
    94  
    95                 //next we do the columns 
    96                 $this->addColumn("Record Count","count(invoices.id)");//0 
    97                 $this->addColumn("Invoice Total","sum(invoices.totalti)","currency");//1 
    98                 $this->addColumn("Average Invoice Total","avg(invoices.totalti)","currency");//2 
    99  
    100                 $this->addColumn("Subtotal","sum(invoices.totaltni)","currency");//3 
    101                 $this->addColumn("Average Subtotal","avg(invoices.totaltni)","currency");//4 
    102  
    103                 $this->addColumn("Tax","sum(invoices.tax)","currency");//5 
    104                 $this->addColumn("Average Tax","avg(invoices.tax)","currency");//6 
    105  
    106                 $this->addColumn("Shipping","sum(invoices.shipping)","currency");//7 
    107                 $this->addColumn("Average Shipping","avg(invoices.shipping)","currency");//8 
    108  
    109                 $this->addColumn("Amount Paid","sum(invoices.amountpaid)","currency");//9 
    110                 $this->addColumn("Average Amount Paid","avg(invoices.amountpaid)","currency");//10 
    111  
    112                 $this->addColumn("Amount Due","sum(invoices.totalti - invoices.amountpaid)","currency");//11 
    113                 $this->addColumn("Average Amount Due","avg(invoices.totalti - invoices.amountpaid)","currency");//12 
    114  
    115                 $this->addColumn("Cost","sum(invoices.totalcost)","currency");//13 
    116                 $this->addColumn("Average Cost","avg(invoices.totalcost)","currency");//14 
    117  
    118                 $this->addColumn("Total Weight","sum(invoices.totalweight)","real");//15 
    119                 $this->addColumn("Average Total Weight","avg(invoices.totalweight)","real");//16 
    120  
    121  
    122                 if($variables){ 
    123                         $tempArray = explode("::", $variables["columns"]); 
    124  
    125                         foreach($tempArray as $id) 
    126                                 $this->selectcolumns[] = $this->columns[$id]; 
    127                         $this->selectcolumns = array_reverse($this->selectcolumns); 
    128  
    129                         $this->selecttable="((`invoices` INNER JOIN `clients` ON `invoices`.`clientid`=`clients`.`uuid`) LEFT JOIN `shippingmethods` ON `shippingmethods`.`uuid` = `invoices`.`shippingmethodid`) LEFT JOIN `paymentmethods` ON `paymentmethods`.`id`=`invoices`.`paymentmethodid`"; 
    130  
    131                         if($variables["groupings"] !== ""){ 
    132                                 $this->group = explode("::",$variables["groupings"]); 
    133                                 $this->group = array_reverse($this->group); 
    134                         } else 
    135                                 $this->group = array(); 
    136  
    137                         foreach($this->group as $grp){ 
    138                                 if($this->groupings[$grp]["table"]) 
    139                                         $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; 
    140                         } 
    141  
    142                         $this->whereclause=$_SESSION["printing"]["whereclause"]; 
    143                         if($this->whereclause=="") $this->whereclause="WHERE invoices.id!=-1"; 
    144  
    145                         switch($variables["showwhat"]){ 
    146                                 case "invoices": 
    147                                         $this->showinvoices = true; 
    148                                         $this->showlineitems = false; 
    149                                         break; 
    150  
    151                                 case "lineitems": 
    152                                         $this->showinvoices = true; 
    153                                         $this->showlineitems = true; 
    154                                         break; 
    155  
    156                                 default: 
    157                                         $this->showinvoices = false; 
    158                                         $this->showlineitems = false; 
    159                         }// endswitch 
    160  
    161                         if($this->whereclause!="") $this->whereclause=" WHERE (".substr($this->whereclause,6).") "; 
    162                 }// endif 
     39if(!class_exists("phpbmsReport")) 
     40    include("../../../report/report_class.php"); 
     41 
     42class totalReport extends phpbmsReport{ 
     43 
     44    var $selectcolumns; 
     45    var $selecttable; 
     46    var $group = ""; 
     47    var $showinvoices = false; 
     48    var $showlineitems = false; 
     49    var $padamount = 20; 
     50    var $title = "Totals"; 
     51 
     52        function totalReport($db, $reportUUID, $tabledefUUID, $variables = NULL){ 
     53 
     54            parent::phpbmsReport($db, $reportUUID, $tabledefUUID); 
     55 
     56            // first we define the available groups 
     57            $this->addGroup("Invoice Date - Year","YEAR(invoices.invoicedate)"); //0 
     58            $this->addGroup("Invoice Date - Quarter","QUARTER(invoices.invoicedate)"); //1 
     59            $this->addGroup("Invoice Date - Month","MONTH(invoices.invoicedate)"); //2 
     60            $this->addGroup("Invoice Date","invoices.invoicedate","date"); //3 
     61 
     62            $this->addGroup("Order Date - Year","YEAR(invoices.orderdate)"); //4 
     63            $this->addGroup("Order Date - Quarter","QUARTER(invoices.orderdate)");//5 
     64            $this->addGroup("Order Date - Month","MONTH(invoices.orderdate)");//6 
     65            $this->addGroup("Order Date","invoices.orderdate","date");//7 
     66 
     67            $this->addGroup("Required Date - Year","YEAR(invoices.requireddate)");//8 
     68            $this->addGroup("Required Date - Quarter","QUARTER(invoices.requireddate)");//9 
     69            $this->addGroup("Required Date - Month","MONTH(invoices.requireddate)");//10 
     70            $this->addGroup("Required Date","invoices.requireddate","date");//11 
     71 
     72            $this->addGroup("Client","if(clients.lastname!='',concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company)");//12 
     73 
     74            $this->addGroup("Client Sales Person","concat(salesPerson.firstname,' ',salesPerson.lastname)",NULL, "LEFT JOIN users AS salesPerson ON clients.salesmanagerid = salesPerson.uuid");//13 
     75 
     76            $this->addGroup("Client Lead Source","clients.leadsource");//14 
     77 
     78            $this->addGroup("Invoice Lead Source","invoices.leadsource");//15 
     79 
     80            $this->addGroup("Payment Method","paymentmethods.name");//16 
     81 
     82            $this->addGroup("Shipping Method","shippingmethods.name");//17 
     83            $this->addGroup("Invoice Shipping Country","invoices.shiptocountry");//18 
     84            $this->addGroup("Invoice Shipping State / Province","invoices.shiptostate");//19 
     85            $this->addGroup("Invoice Shipping Postal Code","invoices.shiptopostalcode");//20 
     86            $this->addGroup("Invoice Shipping City","invoices.shiptocity");//21 
     87 
     88            $this->addGroup("Web Order","invoices.weborder","boolean");//22 
     89 
     90            $this->addGroup("Invoice billing Country","invoices.country");//23 
     91            $this->addGroup("Invoice Billing State / Province","invoices.state");//24 
     92            $this->addGroup("Invoice Billing Postal Code","invoices.postalcode");//25 
     93            $this->addGroup("Invoice Billing City","invoices.city");//26 
     94 
     95 
     96            //next we do the columns 
     97            $this->addColumn("Record Count","count(invoices.id)");//0 
     98            $this->addColumn("Invoice Total","sum(invoices.totalti)","currency");//1 
     99            $this->addColumn("Average Invoice Total","avg(invoices.totalti)","currency");//2 
     100 
     101            $this->addColumn("Subtotal","sum(invoices.totaltni)","currency");//3 
     102            $this->addColumn("Average Subtotal","avg(invoices.totaltni)","currency");//4 
     103 
     104            $this->addColumn("Tax","sum(invoices.tax)","currency");//5 
     105            $this->addColumn("Average Tax","avg(invoices.tax)","currency");//6 
     106 
     107            $this->addColumn("Shipping","sum(invoices.shipping)","currency");//7 
     108            $this->addColumn("Average Shipping","avg(invoices.shipping)","currency");//8 
     109 
     110            $this->addColumn("Amount Paid","sum(invoices.amountpaid)","currency");//9 
     111            $this->addColumn("Average Amount Paid","avg(invoices.amountpaid)","currency");//10 
     112 
     113            $this->addColumn("Amount Due","sum(invoices.totalti - invoices.amountpaid)","currency");//11 
     114            $this->addColumn("Average Amount Due","avg(invoices.totalti - invoices.amountpaid)","currency");//12 
     115 
     116            $this->addColumn("Cost","sum(invoices.totalcost)","currency");//13 
     117            $this->addColumn("Average Cost","avg(invoices.totalcost)","currency");//14 
     118 
     119            $this->addColumn("Total Weight","sum(invoices.totalweight)","real");//15 
     120            $this->addColumn("Average Total Weight","avg(invoices.totalweight)","real");//16 
     121 
     122            $this->selecttable="((`invoices` INNER JOIN `clients` ON `invoices`.`clientid`=`clients`.`uuid`) 
     123                                LEFT JOIN `shippingmethods` ON `shippingmethods`.`uuid` = `invoices`.`shippingmethodid`) 
     124                                LEFT JOIN `paymentmethods` ON `paymentmethods`.`id`=`invoices`.`paymentmethodid`"; 
     125 
    163126        }//end method 
    164127 
    165128 
     129        function processFromPost($variables){ 
     130 
     131            $tempArray = explode("::", $variables["columns"]); 
     132 
     133            foreach($tempArray as $id) 
     134                $this->selectcolumns[] = $this->columns[$id]; 
     135 
     136            $this->selectcolumns = array_reverse($this->selectcolumns); 
     137 
     138            if($variables["groupings"] !== ""){ 
     139 
     140                $this->group = explode("::",$variables["groupings"]); 
     141                $this->group = array_reverse($this->group); 
     142 
     143            } else 
     144                $this->group = array(); 
     145 
     146            foreach($this->group as $grp) 
     147                if($this->groupings[$grp]["table"]) 
     148                    $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; 
     149 
     150            switch($variables["showwhat"]){ 
     151 
     152                case "invoices": 
     153                    $this->showinvoices = true; 
     154                    $this->showlineitems = false; 
     155                    break; 
     156 
     157                case "lineitems": 
     158                    $this->showinvoices = true; 
     159                    $this->showlineitems = true; 
     160                    break; 
     161 
     162                default: 
     163                    $this->showinvoices = false; 
     164                    $this->showlineitems = false; 
     165 
     166            }// endswitch 
     167 
     168            if($variables["reporttitle"]) 
     169                $this->title = $variables["reporttitle"]; 
     170 
     171        }//end function processFromPost 
     172 
     173 
     174        function processFromSettings(){ 
     175 
     176            foreach($this->settings as $key=>$value) 
     177                if(strpos($key, "column") === 0) 
     178                    $this->selectcolumns[substr($key,6)-1] = $this->columns[$value]; 
     179 
     180            ksort($this->selectcolumns); 
     181            $this->selectcolumns = array_reverse($this->selectcolumns); 
     182 
     183            $this->group = array(); 
     184 
     185            foreach($this->settings as $key=>$value) 
     186                if(strpos($key, "group") === 0) 
     187                    $this->group[substr($key,5)-1] = $value; 
     188 
     189            ksort($this->group); 
     190            $this->group = array_reverse($this->group); 
     191 
     192            foreach($this->group as $grp) 
     193                if($this->groupings[$grp]["table"]) 
     194                    $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; 
     195 
     196 
     197            if(isset($this->settings["showWhat"])) 
     198                $showWhat = $this->settings["showWhat"]; 
     199            else 
     200                $showWhat = ""; 
     201 
     202            switch($showWhat){ 
     203 
     204                case "invoices": 
     205                    $this->showinvoices = true; 
     206                    $this->showlineitems = false; 
     207                    break; 
     208 
     209                case "lineitems": 
     210                    $this->showinvoices = true; 
     211                    $this->showlineitems = true; 
     212                    break; 
     213 
     214                default: 
     215                    $this->showinvoices = false; 
     216                    $this->showlineitems = false; 
     217 
     218            }// endswitch 
     219 
     220            if(isset($this->settings["reportTitle"])) 
     221                $this->title = $this->settings["reportTitle"]; 
     222 
     223        }//end function processFromSettings 
     224 
     225 
    166226        function addGroup($name, $field, $format = NULL, $tableAddition = NULL){ 
    167                 $temp = array(); 
    168                 $temp["name"] = $name; 
    169                 $temp["field"] = $field; 
    170                 $temp["format"] = $format; 
    171                 $temp["table"] = $tableAddition; 
    172  
    173                 $this->groupings[] = $temp; 
     227 
     228            $temp = array(); 
     229            $temp["name"] = $name; 
     230            $temp["field"] = $field; 
     231            $temp["format"] = $format; 
     232            $temp["table"] = $tableAddition; 
     233 
     234            $this->groupings[] = $temp; 
     235 
    174236        }//end method 
    175237 
    176238 
    177239        function addColumn($name, $field, $format = NULL){ 
    178                 $temp = array(); 
    179                 $temp["name"] = $name; 
    180                 $temp["field"] = $field; 
    181                 $temp["format"] = $format; 
    182  
    183                 $this->columns[] = $temp; 
     240 
     241            $temp = array(); 
     242            $temp["name"] = $name; 
     243            $temp["field"] = $field; 
     244            $temp["format"] = $format; 
     245 
     246            $this->columns[] = $temp; 
     247 
    184248        }//end method 
    185249 
    186250 
    187251        function showReportTable(){ 
     252 
    188253                ?><table border="0" cellspacing="0" cellpadding="0"> 
    189254                <tr> 
    … …  
    207272                        $querystatement.=$thecolumn["field"]." AS `".$thecolumn["name"]."`,"; 
    208273                $querystatement.=" count(invoices.id) as thecount "; 
    209                 $querystatement.=" FROM ".$this->selecttable.$this->whereclause; 
     274                $querystatement.=" FROM ".$this->selecttable.$this->whereClause; 
    210275                $queryresult=$this->db->query($querystatement); 
    211276 
    … …  
    236301                                $querystatement.=$thecolumn["field"]." AS `".$thecolumn["name"]."`,"; 
    237302                        $querystatement .= $this->groupings[$groupby]["field"]." AS thegroup, count(invoices.id) as thecount "; 
    238                         $querystatement .= " FROM ".$this->selecttable.$this->whereclause.$where." GROUP BY ".$this->groupings[$groupby]["field"]; 
     303                        $querystatement .= " FROM ".$this->selecttable.$this->whereClause.$where." GROUP BY ".$this->groupings[$groupby]["field"]; 
    239304                        $queryresult=$this->db->query($querystatement); 
    240305 
    … …  
    288353                $querystatement.=" invoices.id as theid, if(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) as thename, 
    289354                                                        invoices.orderdate,invoices.invoicedate"; 
    290                 $querystatement.=" FROM ".$this->selecttable.$this->whereclause.$where." GROUP BY invoices.id"; 
     355                $querystatement.=" FROM ".$this->selecttable.$this->whereClause.$where." GROUP BY invoices.id"; 
    291356                $queryresult=$this->db->query($querystatement); 
    292357 
    … …  
    370435        function showReport(){ 
    371436 
    372                 if($_POST["reporttitle"]) 
    373                         $pageTitle = $_POST["reporttitle"]; 
    374                 else 
    375                         $pageTitle = "Invoice Totals"; 
    376  
     437            if(!$this->whereClause) 
     438                $this->whereClause = "invoices.id!=-1"; 
     439 
     440            $this->whereClause = " WHERE ".$this->whereClause; 
     441 
     442            $pageTitle = $this->title; 
    377443 
    378444?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
    … …  
    427493        <div class="bodyline"> 
    428494            <h1>Invoice Total Options</h1> 
    429             <form id="GroupForm" action="<?php echo $_SERVER["PHP_SELF"]?>" method="post" name="GroupForm"> 
     495            <form id="GroupForm" action="<?php echo str_replace("&", "&amp;", $_SERVER["REQUEST_URI"]) ?>" method="post" name="GroupForm"> 
    430496 
    431497                <fieldset> 
    … …  
    495561    }//end method 
    496562 
    497 }//endclass 
    498  
    499 // Processing =================================================================================================================== 
    500 if(!isset($dontProcess)){ 
    501         if(isset($_POST["columns"])){ 
    502                 $myreport= new totalReport($db,$_POST); 
    503                 $myreport->showReport(); 
    504         } else { 
    505                 $myreport = new totalReport($db); 
    506                 $myreport->showSelectScreen(); 
    507         } 
    508 }?> 
     563}//end class totalReport 
     564 
     565 
     566/** 
     567 * PROCESSING 
     568 * ============================================================================= 
     569 */ 
     570if(!isset($noOutput)){ 
     571 
     572    require("../../../include/session.php"); 
     573 
     574    checkForReportArguments(); 
     575 
     576    $report = new totalReport($db, $_GET["rid"], $_GET["tid"]); 
     577 
     578    if(isset($_POST["columns"])){ 
     579 
     580        $report->setupFromPrintScreen(); 
     581        $report->processFromPost($_POST); 
     582        $report->showReport(); 
     583 
     584    } elseif(isset($report->settings["column1"])){ 
     585 
     586        $report->setupFromPrintScreen(); 
     587        $report->processFromSettings(); 
     588        $report->showReport(); 
     589 
     590    } else { 
     591 
     592        $report->showSelectScreen(); 
     593 
     594    }//endif 
     595 
     596}//endif 
     597 
     598/** 
     599 * When adding a new report record, the add/edit needs to know what the class 
     600 * name is so that it can instantiate it, and grab it's default settings. 
     601 */ 
     602if(isset($addingReportRecord)) 
     603    $reportClass ="totalReport"; 
     604 
     605?> 
phpBMS vulnerability assesment provided by Orvant Inc. Copyright © 2010 Kreotek, LLC. All Rights reserved.