/** * function prepareFieldForSQL * * Given a value, and field type, prepare the * value for SQL insertion (replacing nul with the SQL string NULL, * and typeing variables) * * @param string/int $value to be prepared. * @param string $type mysql field type * @param string $flags A list of flags seperated by spaces (" "). */ function prepareFieldForSQL($value, $type, $flags) { switch ($type) { case "blob": case "string": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = "''"; } } else { $value = "'" . $value . "'"; } break; case "real": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = 0; } } else { $value = (double) $value; } break; case "int": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = 0; } } else { $value = (int) $value; } break; case "date": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = "'" . dateToString(mktime(), "SQL") . "'"; } } else { $value = "'" . sqlDateFromString($value, $this->dateFormat) . "'"; } break; case "time": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = "'" . timeToString(mktime(), "SQL") . "'"; } } else { $value = "'" . sqlTimeFromString($value, $this->timeFormat) . "'"; } break; case "year": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = @strftime("%Y"); } } break; case "datetime": case "timestamp": if ($value === "" or $value === NULL) { if (strpos($flags, "not_null") === false) { $value = NULL; } else { $value = "'" . dateToString(mktime(), "SQL") . " " . timeToString(mktime(), "24 Hour") . "'"; } } else { $datetimearray = explode(" ", $value); $date = null; $time = null; //If the value can be split by spaces we assume we // are looking at a "date time" if (count($datetimearray) > 1) { $date = sqlDateFromString($datetimearray[0], $this->dateFormat); //times can have spaces... so we need //to resemble in some cases. if (count($datetimearray) > 2) { $datetimearray[1] = $datetimearray[1] . " " . $datetimearray[2]; } $time = sqlTimeFromString($datetimearray[1], $this->timeFormat); } //endif //If we don't have a date, perhaps only a date was passed if (!$date) { $date = sqlDateFromString($value, $this->dateFormat); //still no date?, then assume only a time was passed, // so we need to set the time to the deafult // date. if (!$date) { $date = "0000-00-00"; } } //endif //if we don't have a time, let's try the getting the //time from the full value. if (!$time) { $time = sqlTimeFromString($value, $this->timeFormat); } $value = "'" . trim($date . " " . $time) . "'"; } //end if break; case "password": $value = "ENCODE('" . $value . "','" . ENCRYPTION_SEED . "')"; break; } //end switch if ($value === NULL) { $value = "NULL"; } return $value; }
function generateSingleClientHistory($clientUUID) { $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 = "\n SELECT\n invoices.id,\n if(invoices.type = 'Invoice', invoices.invoicedate, invoices.orderdate) AS thedate,\n invoices.type,\n products.partname AS partname,\n products.partnumber AS partnumber,\n lineitems.quantity AS qty,\n lineitems.unitprice*lineitems.quantity AS extended,\n lineitems.unitprice AS price\n FROM\n ((`clients` INNER JOIN `invoices` ON `clients`.`uuid`=`invoices`.`clientid`)\n INNER JOIN `lineitems` ON `invoices`.`id`=`lineitems`.`invoiceid`)\n INNER JOIN `products` ON `lineitems`.`productid`=`products`.`uuid`\n WHERE\n `clients`.`uuid`='" . $clientUUID . "'\n AND " . $theStatus . "\n HAVING\n thedate >= '" . $mysqlFromDate . "'\n AND thedate <= '" . $mysqlToDate . "'\n ORDER BY\n thedate,\n invoices.id"; $queryresult = $this->db->query($querystatement); ob_start(); ?> <table border="0" cellpadding="0" cellspacing="0"> <thead> <tr> <th align="left" colspan="3">invoice</th> <th align="left" colspan="3">product</th> <th align="left" nowrap="nowrap" colspan="2" class="lastHeader">line item</th> </tr> <tr> <th align="center" nowrap="nowrap">id</th> <th align="left" nowrap="nowrap" >type</th> <th align="left" nowrap="nowrap" >date</th> <th align="left" nowrap="nowrap" >part #</th> <th width="100%" nowrap="nowrap" align="left">name</th> <th align="right" nowrap="nowrap" >price</th> <th align="right" nowrap="nowrap" >qty.</th> <th align="right" nowrap="nowrap" class="lastHeader">ext.</th> </tr> </thead> <tbody> <?php $totalextended = 0; while ($therecord = $this->db->fetchArray($queryresult)) { $totalextended += $therecord["extended"]; ?> <tr> <td align="left" nowrap="nowrap"><?php echo $therecord["id"] ? $therecord["id"] : " "; ?> </td> <td align="left" nowrap="nowrap"><?php echo $therecord["type"] ? formatVariable($therecord["type"]) : " "; ?> </td> <td align="left" nowrap="nowrap"><?php echo $therecord["thedate"] ? formatFromSQLDate($therecord["thedate"]) : " "; ?> </td> <td nowrap="nowrap"><?php echo formatVariable($therecord["partnumber"]); ?> </td> <td nowrap="nowrap"><?php echo formatVariable($therecord["partname"]); ?> </td> <td align="right" nowrap="nowrap"><?php echo numberToCurrency($therecord["price"]); ?> </td> <td align="right" nowrap="nowrap"><?php echo $therecord["qty"]; ?> </td> <td align="right" nowrap="nowrap"><?php echo numberToCurrency($therecord["extended"]); ?> </td> </tr> <?php } //endwhile ?> <tr class="grandTotals"> <td colspan="7" align="right">total</td> <td align="right"><?php echo numberToCurrency($totalextended); ?> </td> </tr> </tbody> </table><?php $output = ob_get_contents(); ob_end_clean(); return $output; }
function showSalesHistory($id) { $thestatus = "(invoices.type =\""; switch ($this->view) { case "Orders/Invoices": $thestatus .= "Order\" or invoices.type=\"Invoice\")"; $searchdate = "orderdate"; break; case "Invoices": $thestatus .= "Invoice\")"; $searchdate = "invoicedate"; break; case "Orders": $thestatus .= "Order\")"; $searchdate = "orderdate"; break; } $mysqlfromdate = sqlDateFromString($_POST["fromdate"]); $mysqltodate = sqlDateFromString($_POST["todate"]); $querystatement = "\n\t\t\tSELECT\n\t\t\t\t`invoices`.`id`,\n\t\t\t\t`invoices`.`orderdate`,\n\t\t\t\t`invoices`.`invoicedate`,\n\t\t\t\tIF(clients.lastname!=\"\",concat(clients.lastname,\", \",clients.firstname,if(clients.company!=\"\",concat(\" (\",clients.company,\")\"),\"\")),clients.company) AS `client`,\n\t\t\t\t`lineitems`.`quantity` AS `qty`,\n\t\t\t\t`lineitems`.`unitprice`*`lineitems`.`quantity` AS `extended`,\n\t\t\t\t`lineitems`.`unitprice` AS `price`,\n\t\t\t\t`lineitems`.`unitcost` AS `cost`,\n\t\t\t\t`lineitems`.`unitcost`*`lineitems`.`quantity` AS extendedcost\n\t\t\tFROM\n\t\t\t\t((products INNER JOIN lineitems on products.uuid=lineitems.productid)\n\t\t\t\t\tINNER JOIN `invoices` ON lineitems.invoiceid=invoices.id)\n\t\t\t\t\t\tINNER JOIN `clients` on `invoices`.`clientid`=`clients`.`uuid`\n\t\t\tWHERE\n\t\t\t\t`products`.`id`=" . $id . "\n\t\t\t\tAND\n\t\t\t\t`invoices`." . $searchdate . ">=\"" . $mysqlfromdate . "\"\n\t\t\t\tAND\n\t\t\t\t`invoices`." . $searchdate . "<=\"" . $mysqltodate . "\"\n\t\t\t\tAND\n\t\t\t\t" . $thestatus . "\n\t\t\tORDER BY\n\t\t\t\t`invoices`.`invoicedate`,\n\t\t\t\t`invoices`.`orderdate`\n\t\t"; $thequery = $this->db->query($querystatement); $thequery ? $numrows = $this->db->numRows($thequery) : ($numrows = 0); ?> <table border="0" cellpadding="3" cellspacing="0"> <tr> <th align="center" nowrap="nowrap" >ID</th> <th align="center" nowrap="nowrap" >Order Date</th> <th align="center" nowrap="nowrap" >Invc. 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> <?php $totalextended = 0; $totalcostextended = 0; $totalquantity = 0; $avgprice = 0; $avgcost = 0; while ($therecord = $this->db->fetchArray($thequery)) { $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"]) : " "; ?> </td> <td align="center" nowrap="nowrap"><?php echo $therecord["invoicedate"] ? formatFromSQLDate($therecord["invoicedate"]) : " "; ?> </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 } ?> <tr> <td align="center" class="grandtotals"> </td> <td align="center" class="grandtotals"> </td> <td class="grandtotals"> </td> <td class="grandtotals"> </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 }
$mysqlfromdate = sqlDateFromString($_POST["fromdate"]); $mysqltodate = sqlDateFromString($_POST["todate"]); $refquery = "select partname from products where id=" . (int) $_GET["id"]; $refquery = $db->query($refquery); $refrecord = $db->fetchArray($refquery); $querystatement = "\n SELECT\n invoices.id AS id,\n IF(invoices.type = 'Invoice', invoices.invoicedate, invoices.orderdate) AS thedate,\n CONCAT('<strong>',IF(clients.lastname != '', CONCAT(clients.lastname,', ', clients.firstname, IF(clients.company != '', CONCAT(' (', clients.company, ')'),'')), clients.company), '</strong>') AS client,\n lineitems.quantity AS qty,\n lineitems.unitprice * lineitems.quantity AS extended,\n lineitems.unitprice AS price,\n lineitems.unitcost AS cost,\n lineitems.unitcost * lineitems.quantity AS extendedcost\n FROM\n ((products INNER JOIN lineitems ON products.uuid = lineitems.productid)\n INNER JOIN invoices ON lineitems.invoiceid=invoices.id)\n INNER JOIN clients ON invoices.clientid = clients.uuid\n WHERE\n products.id=" . (int) $_GET["id"] . "\n AND " . $thestatus . "\n HAVING\n thedate >= '" . $mysqlfromdate . "'\n AND thedate <= '" . $mysqltodate . "'\n ORDER BY\n thedate " . $dateOrder; $queryresult = $db->query($querystatement); $numrows = $queryresult ? $db->numRows($queryresult) : 0; $pageTitle = "Product Sales History: " . $refrecord["partname"]; $phpbms->cssIncludes[] = "pages/products.css"; //Form Elements //============================================================== $theform = new phpbmsForm(); $theinput = new inputDatePicker("fromdate", sqlDateFromString($_POST["fromdate"]), "from", true); $theform->addField($theinput); $theinput = new inputDatePicker("todate", sqlDateFromString($_POST["todate"]), "to", true); $theform->addField($theinput); $theform->jsMerge(); //============================================================== //End Form Elements include "header.php"; $phpbms->showTabs("products entry", "tab:cd09d4a1-7d32-e08a-bd6e-5850bc9af88e", $_GET["id"]); ?> <div class="bodyline"> <h1><span><?php echo $pageTitle; ?> </span></h1> <form action="<?php echo htmlentities($_SERVER["REQUEST_URI"]); ?>
function processFromPost($variables) { $this->selectcolumns = $this->columns; $this->fromDate = $variables["fromdate"]; $this->toDate = $variables["todate"]; 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"]) { foreach ($this->tableClause as $key => $value) { } $this->tableClause[$key] = "(" . $this->tableClause[$key] . " " . $this->groupings[$grp]["table"] . ")"; } //endif } //endforeach $this->whereClauses["invoices"] = "\n WHERE\n (invoices.type = 'Invoice'\n AND paymentmethods.type != 'receivable'\n AND invoicedate >= '" . sqlDateFromString($variables["fromdate"]) . "'\n AND invoicedate <= '" . sqlDateFromString($variables["todate"]) . "')\n "; $this->whereClauses["receipts"] = "\n WHERE\n (receipts.posted = 1\n AND receiptdate >= '" . sqlDateFromString($variables["fromdate"]) . "'\n AND receiptdate <= '" . sqlDateFromString($variables["todate"]) . "')\n "; $this->showItems = isset($variables["showitems"]); }
$refid = (int) $_GET["id"]; $refquery = "\n\t\tSELECT\n\t\t\t`invoices`.`id`,\n\t\t\t`invoices`.`uuid`,\n\t\t\tIF(`clients`.`lastname`!='',concat(`clients`.`lastname`,', ',`clients`.`firstname`,IF(`clients`.`company`!='',concat(' (',`clients`.`company`,')'),'')),`clients`.`company`) AS `name`,\n\t\t\t`invoices`.`type`\n\t\tFROM\n\t\t\t`invoices` INNER JOIN `clients` ON `invoices`.`clientid`=`clients`.`uuid`\n\t\tWHERE\n\t\t\t`invoices`.`id`='" . $refid . "'\n\t"; $refquery = $db->query($refquery); $refrecord = $db->fetchArray($refquery); $pageTitle = "Status History: " . $refrecord["id"] . ": " . $refrecord["name"]; //================================================================ if (isset($_POST["command"])) { foreach ($_POST as $key => $value) { if (strpos($key, "sh") === 0) { $historyid = substr($key, 2); $assignedtoid = mysql_real_escape_string($_POST["as" . $historyid]); $querystatement = "\n\t\t\t\t\t\tUPDATE\n\t\t\t\t\t\t\t`invoicestatushistory`\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\t`statusdate`="; if ($value == "" || $value == "0/0/0000") { $tempdate = "NULL"; } else { $tempdate = "'" . sqlDateFromString($value) . "'"; } $querystatement .= $tempdate; $querystatement .= ",`assignedtoid`='" . $assignedtoid . "'"; $querystatement .= " WHERE `id`='" . (int) $historyid . "'"; $queryresult = $db->query($querystatement); } //end find if } //end for each $statusmessage = "Statuses Updated"; } //end command if //================================================================ $querystatement = "SELECT uuid,name FROM invoicestatuses WHERE inactive='0' ORDER BY priority,name"; $statusresult = $db->query($querystatement);
function updateStatus($invoiceid, $statusid, $statusdate, $assignedtoid, $replace = false) { $invoiceuuid = getUuid($this->db, $this->uuid, (int) $invoiceid); $statusid = mysql_real_escape_string($statusid); $assignedtoid = mysql_real_escape_string($assignedtoid); if (!$replace) { $querystatement = "\n\t\t\t\t\tDELETE FROM\n\t\t\t\t\t\t`invoicestatushistory`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`invoiceid`='" . $invoiceuuid . "'\n\t\t\t\t\t\tAND\n\t\t\t\t\t\t`invoicestatusid`='" . $statusid . "'\n\t\t\t\t"; } else { $querystatement = "\n\t\t\t\t\tDELETE FROM\n\t\t\t\t\t\t`invoicestatushistory`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`invoiceid` = '" . $invoiceuuid . "'\n\t\t\t\t"; } //end if $queryresult = $this->db->query($querystatement); $querystatement = "\n\t\t\t\tINSERT INTO\n\t\t\t\t\t`invoicestatushistory`\n\t\t\t\t\t(invoiceid,invoicestatusid,statusdate,assignedtoid) VALUES ("; $querystatement .= "'" . $invoiceuuid . "', "; $querystatement .= "'" . $statusid . "', "; if ($statusdate == "" || $statusdate == "0/0/0000") { $tempdate = "NULL"; } else { $tempdate = "\"" . sqlDateFromString($statusdate) . "\""; } $querystatement .= $tempdate . ","; if ($assignedtoid == "") { $querystatement .= "NULL"; } else { $querystatement .= "'" . $assignedtoid . "'"; } $querystatement .= ")"; $queryresult = $this->db->query($querystatement); }
function insert($variables) { $querystatement = "\n\t\t\tINSERT INTO\n\t\t\t\t`recurringinvoices`\n\t\t\t\t(\n\t\t\t\t\t`invoiceid`,\n\t\t\t\t\t`type`,\n\t\t\t\t\t`every`,\n\t\t\t\t\t`eachlist`,\n\t\t\t\t\t`ontheday`,\n\t\t\t\t\t`ontheweek`,\n\t\t\t\t\t`times`,\n\t\t\t\t\t`until`,\n\t\t\t\t\t`name`,\n\t\t\t\t\t`includepaymenttype`,\n\t\t\t\t\t`statusid`,\n\t\t\t\t\t`assignedtoid`,\n\t\t\t\t\t`notificationroleid`\n\t\t\t\t) VALUES ("; $thename = "Every "; $querystatement .= "'" . $this->invoiceuuid . "', "; $querystatement .= "'" . $variables["type"] . "', "; $querystatement .= (int) $variables["every"] . ", "; switch ($variables["type"]) { case "Daily": if ($variables["every"] != 1) { $thename .= $variables["every"] . " days"; } else { $thename .= " day "; } $querystatement .= "NULL, NULL, NULL, "; break; case "Weekly": if ($variables["every"] != 1) { $thename .= $variables["every"] . " weeks on"; } else { $thename .= "week on"; } foreach (explode("::", $variables["eachlist"]) as $dayNum) { $tempday = $dayNum != 7 ? $dayNum + 1 : 1; $thename .= " " . nl_langinfo(constant("ABDAY_" . $tempday)) . ", "; } $thename = substr($thename, 0, strlen($thename) - 2); if (strpos($thename, ",") != false) { $thename = strrev(preg_replace("/,/", "dna ", strrev($thename), 1)); } $querystatement .= "'" . $variables["eachlist"] . "', NULL, NULL, "; break; case "Monthly": if ($variables["every"] != 1) { $thename .= $variables["every"] . " months"; } else { $thename .= "month"; } $thename .= " on the"; if ($variables["monthlyWhat"] == 1) { foreach (explode("::", $variables["eachlist"]) as $dayNum) { $thename .= " " . ordinal($dayNum) . ", "; } $thename = substr($thename, 0, strlen($thename) - 2); if (strpos($thename, ",") != false) { $thename = strrev(preg_replace("/,/", "dna ", strrev($thename), 1)); } $querystatement .= "'" . $variables["eachlist"] . "', NULL, NULL, "; } else { foreach ($this->weekArray as $key => $value) { if ($value == $variables["monthlyontheweek"]) { $thename .= " " . strtolower($key); } } foreach ($this->dayOfWeekArray as $key => $value) { if ($value == $variables["monthlyontheday"]) { $thename .= " " . $key; } } $querystatement .= "NULL, " . (int) $variables["monthlyontheday"] . ", " . (int) $variables["monthlyontheweek"] . ", "; } break; case "Yearly": if ($variables["every"] > 1) { $thename .= $variables["every"] . " years"; } else { $thename .= "year"; } $thename .= " in"; foreach (explode("::", $variables["eachlist"]) as $monthNum) { $thename .= " " . nl_langinfo(constant("MON_" . $monthNum)) . ", "; } $thename = substr($thename, 0, strlen($thename) - 2); if (strpos($thename, ",") != false) { $thename = strrev(preg_replace("/,/", "dna ", strrev($thename), 1)); } $querystatement .= "'" . $variables["eachlist"] . "', "; if (isset($variables["yearlyOnThe"])) { $thename .= " on the"; foreach ($this->weekArray as $key => $value) { if ($value == $variables["yearlyontheweek"]) { $thename .= " " . strtolower($key); } } foreach ($this->dayOfWeekArray as $key => $value) { if ($value == $variables["yearlyontheday"]) { $thename .= " " . $key; } } $querystatement .= (int) $variables["yearlyontheday"] . ", "; $querystatement .= (int) $variables["yearlyontheweek"] . ", "; } else { $querystatement .= "NULL, NULL, "; } break; } switch ($variables["end"]) { case "never": $querystatement .= "NULL, NULL, "; break; case "after": $thename .= " for " . $variables["times"]; $querystatement .= (int) $variables["times"] . ", NULL, "; break; case "on date": $thename .= " until " . $variables["until"]; $querystatement .= "NULL, '" . sqlDateFromString($variables["until"]) . "', "; break; } $thename = trim($thename) . "."; $querystatement .= "'" . mysql_real_escape_string($thename) . "', "; if (!isset($variables["includepaymenttype"])) { $variables["includepaymenttype"] = 0; } $querystatement .= (int) $variables["includepaymenttype"] . ", "; $querystatement .= "'" . $variables["statusid"] . "', "; $querystatement .= "'" . $variables["assignedtoid"] . "', "; $querystatement .= "'" . $variables["notificationroleid"] . "'"; $querystatement .= ")"; $this->db->query($querystatement); }