| 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 ID","invoices.id"); //0 |
| 57 | | $this->addGroup("Product","concat(products.partnumber,' - ',products.partname)"); //1 |
| 58 | | $this->addGroup("Product Category","concat(productcategories.id,' - ',productcategories.name)",NULL,"INNER JOIN productcategories ON products.categoryid=productcategories.uuid"); //2 |
| 59 | | |
| 60 | | $this->addGroup("Invoice Date - Year","YEAR(invoices.invoicedate)"); //3 |
| 61 | | $this->addGroup("Invoice Date - Quarter","QUARTER(invoices.invoicedate)"); //4 |
| 62 | | $this->addGroup("Invoice Date - Month","MONTH(invoices.invoicedate)"); //5 |
| 63 | | $this->addGroup("Invoice Date","invoices.invoicedate","date"); //6 |
| 64 | | |
| 65 | | $this->addGroup("Order Date - Year","YEAR(invoices.orderdate)"); //7 |
| 66 | | $this->addGroup("Order Date - Quarter","QUARTER(invoices.orderdate)");//8 |
| 67 | | $this->addGroup("Order Date - Month","MONTH(invoices.orderdate)");//9 |
| 68 | | $this->addGroup("Order Date","invoices.orderdate","date");//10 |
| 69 | | |
| 70 | | $this->addGroup("Client","if(clients.lastname!='',concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company)");//11 |
| 71 | | |
| 72 | | $this->addGroup("Client Sales Person","concat(salesPerson.firstname,' ',salesPerson.lastname)",NULL, "LEFT JOIN users AS salesPerson ON clients.salesmanagerid = salesPerson.id");//12 |
| 73 | | |
| 74 | | $this->addGroup("Client Lead Source","clients.leadsource");//13 |
| 75 | | |
| 76 | | $this->addGroup("Invoice Lead Source","invoices.leadsource");//14 |
| 77 | | |
| 78 | | $this->addGroup("Payment Method","paymentmethods.name");//15 |
| 79 | | |
| 80 | | $this->addGroup("Shipping Method","shippingmethods.name");//16 |
| 81 | | $this->addGroup("Invoice Shipping Country","invoices.shiptocountry");//17 |
| 82 | | $this->addGroup("Invoice Shipping State / Province","invoices.shiptostate");//18 |
| 83 | | $this->addGroup("Invoice Shipping Postal Code","invoices.shiptopostalcode");//19 |
| 84 | | $this->addGroup("Invoice Shipping City","invoices.shiptocity");//20 |
| 85 | | |
| 86 | | $this->addGroup("Web Order","invoices.weborder","boolean");//21 |
| 87 | | |
| 88 | | $this->addGroup("Invoice billing Country","invoices.country");//22 |
| 89 | | $this->addGroup("Invoice Billing State / Province","invoices.state");//23 |
| 90 | | $this->addGroup("Invoice Billing Postal Code","invoices.postalcode");//24 |
| 91 | | $this->addGroup("Invoice Billing City","invoices.city");//25 |
| 92 | | |
| 93 | | //next we do the columns |
| 94 | | $this->addColumn("Record Count","count(lineitems.id)");//0 |
| 95 | | $this->addColumn("Extended Price","sum(lineitems.unitprice*lineitems.quantity)","currency");//1 |
| 96 | | $this->addColumn("Average Extended Price","avg(lineitems.unitprice*lineitems.quantity)","currency");//2 |
| 97 | | $this->addColumn("Unit Price","sum(lineitems.unitprice)","currency");//3 |
| 98 | | $this->addColumn("Average Unit Price","avg(lineitems.unitprice)","currency");//4 |
| 99 | | $this->addColumn("Quantity","sum(lineitems.quantity)","real");//5 |
| 100 | | $this->addColumn("Average Quantity","avg(lineitems.quantity)","real");//6 |
| 101 | | $this->addColumn("Unit Cost","sum(lineitems.unitcost)","currency");//7 |
| 102 | | $this->addColumn("Average Unit Cost","avg(lineitems.unitcost)","currency");//8 |
| 103 | | $this->addColumn("Extended Cost","sum(lineitems.unitcost*lineitems.quantity)","currency");//9 |
| 104 | | $this->addColumn("Average Extended Cost","avg(lineitems.unitcost*lineitems.quantity)","currency");//10 |
| 105 | | $this->addColumn("Unit Weight","sum(lineitems.unitweight)","real");//11 |
| 106 | | $this->addColumn("Average Unit Weight","avg(lineitems.unitweight)","real");//12 |
| 107 | | $this->addColumn("Extended Unit Weight","sum(lineitems.unitweight*lineitems.quantity)","real");//13 |
| 108 | | $this->addColumn("Extended Average Unit Weight","avg(lineitems.unitweight*lineitems.quantity)","real");//14 |
| 109 | | |
| 110 | | |
| 111 | | if($variables){ |
| 112 | | $tempArray = explode("::", $variables["columns"]); |
| 113 | | |
| 114 | | foreach($tempArray as $id) |
| 115 | | $this->selectcolumns[] = $this->columns[$id]; |
| 116 | | $this->selectcolumns = array_reverse($this->selectcolumns); |
| 117 | | |
| 118 | | //change |
| 119 | | $this->selecttable="(((((lineitems LEFT JOIN products ON lineitems.productid=products.uuid) |
| 120 | | INNER JOIN invoices ON lineitems.invoiceid=invoices.id) |
| 121 | | INNER JOIN clients ON invoices.clientid=clients.uuid) |
| 122 | | LEFT JOIN shippingmethods ON shippingmethods.uuid=invoices.shippingmethodid) |
| 123 | | LEFT JOIN paymentmethods ON paymentmethods.uuid=invoices.paymentmethodid) |
| 124 | | "; |
| 125 | | |
| 126 | | if($variables["groupings"] !== ""){ |
| 127 | | $this->group = explode("::",$variables["groupings"]); |
| 128 | | $this->group = array_reverse($this->group); |
| 129 | | } else |
| 130 | | $this->group = array(); |
| 131 | | |
| 132 | | foreach($this->group as $grp){ |
| 133 | | if($this->groupings[$grp]["table"]) |
| 134 | | $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; |
| 135 | | } |
| 136 | | |
| 137 | | $this->whereclause=$_SESSION["printing"]["whereclause"]; |
| 138 | | if($this->whereclause=="") $this->whereclause="WHERE invoices.id!=-1"; |
| 139 | | |
| 140 | | switch($variables["showwhat"]){ |
| 141 | | case "invoices": |
| 142 | | $this->showinvoices = true; |
| 143 | | $this->showlineitems = false; |
| 144 | | break; |
| 145 | | |
| 146 | | case "lineitems": |
| 147 | | $this->showinvoices = true; |
| 148 | | $this->showlineitems = true; |
| 149 | | break; |
| 150 | | |
| 151 | | default: |
| 152 | | $this->showinvoices = false; |
| 153 | | $this->showlineitems = false; |
| 154 | | }// endswitch |
| 155 | | |
| 156 | | if($this->whereclause!="") $this->whereclause=" WHERE (".substr($this->whereclause,6).") "; |
| 157 | | }// endif |
| | 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 ID","invoices.id"); //0 |
| | 58 | $this->addGroup("Product","concat(products.partnumber,' - ',products.partname)"); //1 |
| | 59 | $this->addGroup("Product Category","concat(productcategories.id,' - ',productcategories.name)",NULL,"INNER JOIN productcategories ON products.categoryid=productcategories.uuid"); //2 |
| | 60 | |
| | 61 | $this->addGroup("Invoice Date - Year","YEAR(invoices.invoicedate)"); //3 |
| | 62 | $this->addGroup("Invoice Date - Quarter","QUARTER(invoices.invoicedate)"); //4 |
| | 63 | $this->addGroup("Invoice Date - Month","MONTH(invoices.invoicedate)"); //5 |
| | 64 | $this->addGroup("Invoice Date","invoices.invoicedate","date"); //6 |
| | 65 | |
| | 66 | $this->addGroup("Order Date - Year","YEAR(invoices.orderdate)"); //7 |
| | 67 | $this->addGroup("Order Date - Quarter","QUARTER(invoices.orderdate)");//8 |
| | 68 | $this->addGroup("Order Date - Month","MONTH(invoices.orderdate)");//9 |
| | 69 | $this->addGroup("Order Date","invoices.orderdate","date");//10 |
| | 70 | |
| | 71 | $this->addGroup("Client","if(clients.lastname!='',concat(clients.lastname,', ',clients.firstname,if(clients.company!='',concat(' (',clients.company,')'),'')),clients.company)");//11 |
| | 72 | |
| | 73 | $this->addGroup("Client Sales Person","concat(salesPerson.firstname,' ',salesPerson.lastname)",NULL, "LEFT JOIN users AS salesPerson ON clients.salesmanagerid = salesPerson.id");//12 |
| | 74 | |
| | 75 | $this->addGroup("Client Lead Source","clients.leadsource");//13 |
| | 76 | |
| | 77 | $this->addGroup("Invoice Lead Source","invoices.leadsource");//14 |
| | 78 | |
| | 79 | $this->addGroup("Payment Method","paymentmethods.name");//15 |
| | 80 | |
| | 81 | $this->addGroup("Shipping Method","shippingmethods.name");//16 |
| | 82 | $this->addGroup("Invoice Shipping Country","invoices.shiptocountry");//17 |
| | 83 | $this->addGroup("Invoice Shipping State / Province","invoices.shiptostate");//18 |
| | 84 | $this->addGroup("Invoice Shipping Postal Code","invoices.shiptopostalcode");//19 |
| | 85 | $this->addGroup("Invoice Shipping City","invoices.shiptocity");//20 |
| | 86 | |
| | 87 | $this->addGroup("Web Order","invoices.weborder","boolean");//21 |
| | 88 | |
| | 89 | $this->addGroup("Invoice billing Country","invoices.country");//22 |
| | 90 | $this->addGroup("Invoice Billing State / Province","invoices.state");//23 |
| | 91 | $this->addGroup("Invoice Billing Postal Code","invoices.postalcode");//24 |
| | 92 | $this->addGroup("Invoice Billing City","invoices.city");//25 |
| | 93 | |
| | 94 | //next we do the columns |
| | 95 | $this->addColumn("Record Count","count(lineitems.id)");//0 |
| | 96 | $this->addColumn("Extended Price","sum(lineitems.unitprice*lineitems.quantity)","currency");//1 |
| | 97 | $this->addColumn("Average Extended Price","avg(lineitems.unitprice*lineitems.quantity)","currency");//2 |
| | 98 | $this->addColumn("Unit Price","sum(lineitems.unitprice)","currency");//3 |
| | 99 | $this->addColumn("Average Unit Price","avg(lineitems.unitprice)","currency");//4 |
| | 100 | $this->addColumn("Quantity","sum(lineitems.quantity)","real");//5 |
| | 101 | $this->addColumn("Average Quantity","avg(lineitems.quantity)","real");//6 |
| | 102 | $this->addColumn("Unit Cost","sum(lineitems.unitcost)","currency");//7 |
| | 103 | $this->addColumn("Average Unit Cost","avg(lineitems.unitcost)","currency");//8 |
| | 104 | $this->addColumn("Extended Cost","sum(lineitems.unitcost*lineitems.quantity)","currency");//9 |
| | 105 | $this->addColumn("Average Extended Cost","avg(lineitems.unitcost*lineitems.quantity)","currency");//10 |
| | 106 | $this->addColumn("Unit Weight","sum(lineitems.unitweight)","real");//11 |
| | 107 | $this->addColumn("Average Unit Weight","avg(lineitems.unitweight)","real");//12 |
| | 108 | $this->addColumn("Extended Unit Weight","sum(lineitems.unitweight*lineitems.quantity)","real");//13 |
| | 109 | $this->addColumn("Extended Average Unit Weight","avg(lineitems.unitweight*lineitems.quantity)","real");//14 |
| | 110 | |
| | 111 | //change |
| | 112 | $this->selecttable="(((((lineitems LEFT JOIN products ON lineitems.productid=products.uuid) |
| | 113 | INNER JOIN invoices ON lineitems.invoiceid=invoices.id) |
| | 114 | INNER JOIN clients ON invoices.clientid=clients.uuid) |
| | 115 | LEFT JOIN shippingmethods ON shippingmethods.uuid=invoices.shippingmethodid) |
| | 116 | LEFT JOIN paymentmethods ON paymentmethods.uuid=invoices.paymentmethodid) |
| | 117 | "; |
| | 118 | |
| | 119 | }//end function totalReport |
| | 120 | |
| | 121 | |
| | 122 | function processFromPost($variables){ |
| | 123 | |
| | 124 | $tempArray = explode("::", $variables["columns"]); |
| | 125 | |
| | 126 | foreach($tempArray as $id) |
| | 127 | $this->selectcolumns[] = $this->columns[$id]; |
| | 128 | |
| | 129 | $this->selectcolumns = array_reverse($this->selectcolumns); |
| | 130 | |
| | 131 | if($variables["groupings"] !== ""){ |
| | 132 | |
| | 133 | $this->group = explode("::",$variables["groupings"]); |
| | 134 | $this->group = array_reverse($this->group); |
| | 135 | |
| | 136 | } else |
| | 137 | $this->group = array(); |
| | 138 | |
| | 139 | foreach($this->group as $grp) |
| | 140 | if($this->groupings[$grp]["table"]) |
| | 141 | $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; |
| | 142 | |
| | 143 | switch($variables["showwhat"]){ |
| | 144 | |
| | 145 | case "invoices": |
| | 146 | $this->showinvoices = true; |
| | 147 | $this->showlineitems = false; |
| | 148 | break; |
| | 149 | |
| | 150 | case "lineitems": |
| | 151 | $this->showinvoices = true; |
| | 152 | $this->showlineitems = true; |
| | 153 | break; |
| | 154 | |
| | 155 | default: |
| | 156 | $this->showinvoices = false; |
| | 157 | $this->showlineitems = false; |
| | 158 | |
| | 159 | }// endswitch |
| | 160 | |
| | 161 | if($variables["reporttitle"]) |
| | 162 | $this->title = $variables["reporttitle"]; |
| | 163 | |
| | 164 | }//end function processFromPost |
| | 165 | |
| | 166 | |
| | 167 | function processFromSettings(){ |
| | 168 | |
| | 169 | foreach($this->settings as $key=>$value) |
| | 170 | if(strpos($key, "column") === 0) |
| | 171 | $this->selectcolumns[substr($key,6)-1] = $this->columns[$value]; |
| | 172 | |
| | 173 | ksort($this->selectcolumns); |
| | 174 | $this->selectcolumns = array_reverse($this->selectcolumns); |
| | 175 | |
| | 176 | $this->group = array(); |
| | 177 | |
| | 178 | foreach($this->settings as $key=>$value) |
| | 179 | if(strpos($key, "group") === 0) |
| | 180 | $this->group[substr($key,5)-1] = $value; |
| | 181 | |
| | 182 | ksort($this->group); |
| | 183 | $this->group = array_reverse($this->group); |
| | 184 | |
| | 185 | foreach($this->group as $grp) |
| | 186 | if($this->groupings[$grp]["table"]) |
| | 187 | $this->selecttable="(".$this->selecttable." ".$this->groupings[$grp]["table"].")"; |
| | 188 | |
| | 189 | |
| | 190 | if(isset($this->settings["showWhat"])) |
| | 191 | $showWhat = $this->settings["showWhat"]; |
| | 192 | else |
| | 193 | $showWhat = ""; |
| | 194 | |
| | 195 | switch($showWhat){ |
| | 196 | |
| | 197 | case "invoices": |
| | 198 | $this->showinvoices = true; |
| | 199 | $this->showlineitems = false; |
| | 200 | break; |
| | 201 | |
| | 202 | case "lineitems": |
| | 203 | $this->showinvoices = true; |
| | 204 | $this->showlineitems = true; |
| | 205 | break; |
| | 206 | |
| | 207 | default: |
| | 208 | $this->showinvoices = false; |
| | 209 | $this->showlineitems = false; |
| | 210 | |
| | 211 | }// endswitch |
| | 212 | |
| | 213 | if(isset($this->settings["reportTitle"])) |
| | 214 | $this->title = $this->settings["reportTitle"]; |
| | 215 | |
| | 216 | }//end function processFromSettings |
| | 217 | |
| | 218 | |
| | 219 | function addGroup($name, $field, $format = NULL, $tableAddition = NULL){ |
| | 220 | |
| | 221 | $temp = array(); |
| | 222 | $temp["name"] = $name; |
| | 223 | $temp["field"] = $field; |
| | 224 | $temp["format"] = $format; |
| | 225 | $temp["table"] = $tableAddition; |
| | 226 | |
| | 227 | $this->groupings[] = $temp; |
| | 228 | |