From c7d2396b42a12d7f7bf0166d9ae3d1d87edc2fe0 Mon Sep 17 00:00:00 2001 From: Rod Roark Date: Sat, 5 Nov 2011 16:09:41 -0700 Subject: [PATCH] Implemented maximum inventory level, min/max by product and warehouse, some escaping/security fixes. --- interface/drugs/add_edit_drug.php | 172 ++++++++++++++++++++++++++++---------- sql/4_1_0-to-4_1_1_upgrade.sql | 15 ++++ sql/database.sql | 12 ++- version.php | 2 +- 4 files changed, 155 insertions(+), 46 deletions(-) diff --git a/interface/drugs/add_edit_drug.php b/interface/drugs/add_edit_drug.php index 4605b8613..0635d0b3d 100644 --- a/interface/drugs/add_edit_drug.php +++ b/interface/drugs/add_edit_drug.php @@ -1,11 +1,14 @@ + // Copyright (C) 2006-2011 Rod Roark // // This program is free software; you can redistribute it and/or // modify it under the terms of the GNU General Public License // as published by the Free Software Foundation; either version 2 // of the License, or (at your option) any later version. +$sanitize_all_escapes = true; +$fake_register_globals = false; + require_once("../globals.php"); require_once("$srcdir/acl.inc"); require_once("drugs.inc.php"); @@ -28,6 +31,12 @@ function bucks($amount) { return ''; } +// Format a string to be used in a quoted HTML form attribute. +// +function htmlAttr($value) { + return htmlspecialchars($value, ENT_QUOTES); +} + // Write a line of data for one template to the form. // function writeTemplateLine($selector, $dosage, $period, $quantity, $refills, $prices, $taxrates) { @@ -36,41 +45,45 @@ function writeTemplateLine($selector, $dosage, $period, $quantity, $refills, $pr echo " \n"; echo " "; - echo ""; + echo ""; echo "\n"; echo " "; - echo ""; + echo ""; echo "\n"; echo " "; - generate_form_field(array('data_type'=>1,'field_id'=>'tmpl['.$tmpl_line_no.'][period]','list_id'=>'drug_interval','empty_title'=>'SKIP'), $period); + generate_form_field(array( + 'data_type' => 1, + 'field_id' => 'tmpl[' . $tmpl_line_no . '][period]', + 'list_id' => 'drug_interval', + 'empty_title' => 'SKIP' + ), $period); echo "\n"; echo " "; - echo ""; + echo ""; echo "\n"; echo " "; - echo ""; + echo ""; echo "\n"; foreach ($prices as $pricelevel => $price) { echo " "; - echo ""; + echo ""; echo "\n"; } $pres = sqlStatement("SELECT option_id FROM list_options " . "WHERE list_id = 'taxrate' ORDER BY seq"); while ($prow = sqlFetchArray($pres)) { echo " "; - echo "\n"; } echo " \n"; } -// Translation for form fields. +// Translation for form fields used in SQL queries. +// function escapedff($name) { - $field = trim($_POST[$name]); - if (!get_magic_quotes_gpc()) return addslashes($field); - return $field; + return mysql_real_escape_string(trim($_POST[$name])); } function numericff($name) { $field = trim($_POST[$name]) + 0; @@ -143,7 +156,7 @@ if ($_POST['form_save']) { "size = '" . escapedff('form_size') . "' AND " . "unit = '" . escapedff('form_unit') . "' AND " . "route = '" . escapedff('form_route') . "' AND " . - "drug_id != '$drug_id'"); + "drug_id != ?", array($drug_id)); if ($crow['count']) { $alertmsg = xl('Cannot add this entry because it already exists!'); } @@ -158,6 +171,7 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { "ndc_number = '" . escapedff('form_ndc_number') . "', " . "on_order = '" . escapedff('form_on_order') . "', " . "reorder_point = '" . escapedff('form_reorder_point') . "', " . + "max_level = '" . escapedff('form_max_level') . "', " . "form = '" . escapedff('form_form') . "', " . "size = '" . escapedff('form_size') . "', " . "unit = '" . escapedff('form_unit') . "', " . @@ -167,22 +181,22 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { "allow_multiple = " . (empty($_POST['form_allow_multiple' ]) ? 0 : 1) . ", " . "allow_combining = " . (empty($_POST['form_allow_combining']) ? 0 : 1) . ", " . "active = " . (empty($_POST['form_active']) ? 0 : 1) . " " . - "WHERE drug_id = '$drug_id'"); - sqlStatement("DELETE FROM drug_templates WHERE drug_id = '$drug_id'"); + "WHERE drug_id = ?", array($drug_id)); + sqlStatement("DELETE FROM drug_templates WHERE drug_id = ?", array($drug_id)); } else { // deleting if (acl_check('admin', 'super')) { - sqlStatement("DELETE FROM drug_inventory WHERE drug_id = '$drug_id'"); - sqlStatement("DELETE FROM drug_templates WHERE drug_id = '$drug_id'"); - sqlStatement("DELETE FROM drugs WHERE drug_id = '$drug_id'"); - sqlStatement("DELETE FROM prices WHERE pr_id = '$drug_id' AND pr_selector != ''"); + sqlStatement("DELETE FROM drug_inventory WHERE drug_id = ?", array($drug_id)); + sqlStatement("DELETE FROM drug_templates WHERE drug_id = ?", array($drug_id)); + sqlStatement("DELETE FROM drugs WHERE drug_id = ?", array($drug_id)); + sqlStatement("DELETE FROM prices WHERE pr_id = ? AND pr_selector != ''", array($drug_id)); } } } else if ($_POST['form_save']) { // saving a new drug $new_drug = true; $drug_id = sqlInsert("INSERT INTO drugs ( " . - "name, ndc_number, on_order, reorder_point, form, " . + "name, ndc_number, on_order, reorder_point, max_level, form, " . "size, unit, route, cyp_factor, related_code, " . "allow_multiple, allow_combining, active " . ") VALUES ( " . @@ -190,6 +204,7 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { "'" . escapedff('form_ndc_number') . "', " . "'" . escapedff('form_on_order') . "', " . "'" . escapedff('form_reorder_point') . "', " . + "'" . escapedff('form_max_level') . "', " . "'" . escapedff('form_form') . "', " . "'" . escapedff('form_size') . "', " . "'" . escapedff('form_unit') . "', " . @@ -207,9 +222,9 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { // If using the simplified drug form, then force the one and only // selector name to be the same as the product name. if ($GLOBALS['sell_non_drug_products'] == 2) { - $tmpl["1"]['selector'] = escapedff('form_name'); + $tmpl["1"]['selector'] = $_POST['form_name']; } - sqlStatement("DELETE FROM prices WHERE pr_id = '$drug_id' AND pr_selector != ''"); + sqlStatement("DELETE FROM prices WHERE pr_id = ? AND pr_selector != ''", array($drug_id)); for ($lino = 1; isset($tmpl["$lino"]['selector']); ++$lino) { $iter = $tmpl["$lino"]; $selector = trim($iter['selector']); @@ -222,15 +237,9 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { } sqlInsert("INSERT INTO drug_templates ( " . "drug_id, selector, dosage, period, quantity, refills, taxrates " . - ") VALUES ( " . - "$drug_id, " . - "'" . $selector . "', " . - "'" . trim($iter['dosage']) . "', " . - "'" . trim($iter['period']) . "', " . - "'" . trim($iter['quantity']) . "', " . - "'" . trim($iter['refills']) . "', " . - "'" . $taxrates . "' " . - ")"); + ") VALUES ( ?, ?, ?, ?, ?, ?, ? )", + array($drug_id, $selector, trim($iter['dosage']), trim($iter['period']), + trim($iter['quantity']), trim($iter['refills']), $taxrates)); // Add prices for this drug ID and selector. foreach ($iter['price'] as $key => $value) { @@ -238,11 +247,24 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { if ($value) { sqlStatement("INSERT INTO prices ( " . "pr_id, pr_selector, pr_level, pr_price ) VALUES ( " . - "'$drug_id', '$selector', '$key', '$value' )"); + "?, ?, ?, ? )", + array($drug_id, $selector, $key, $value)); } } // end foreach price } // end if selector is present } // end for each selector + // Save warehouse-specific mins and maxes for this drug. + sqlStatement("DELETE FROM product_warehouse WHERE pw_drug_id = ?", array($drug_id)); + foreach ($_POST['form_wh_min'] as $whid => $whmin) { + if (!get_magic_quotes_gpc()) $whid = addslashes($whid); + $whmin = 0 + $whmin; + $whmax = 0 + $_POST['form_wh_max'][$whid]; + if ($whmin != 0 || $whmax != 0) { + sqlStatement("INSERT INTO product_warehouse ( " . + "pw_drug_id, pw_warehouse, pw_min_level, pw_max_level ) VALUES ( " . + "?, ?, ?, ? )", array($drug_id, $whid, $whmin, $whmax)); + } + } } // end if saving a drug // Close this window and redisplay the updated list of drugs. @@ -260,9 +282,9 @@ if (($_POST['form_save'] || $_POST['form_delete']) && !$alertmsg) { } if ($drug_id) { - $row = sqlQuery("SELECT * FROM drugs WHERE drug_id = '$drug_id'"); + $row = sqlQuery("SELECT * FROM drugs WHERE drug_id = ?", array($drug_id)); $tres = sqlStatement("SELECT * FROM drug_templates WHERE " . - "drug_id = '$drug_id' ORDER BY selector"); + "drug_id = ? ORDER BY selector", array($drug_id)); } else { $row = array( @@ -273,6 +295,7 @@ else { 'ndc_number' => '', 'on_order' => '0', 'reorder_point' => '0', + 'max_level' => '0', 'form' => '', 'size' => '', 'unit' => '', @@ -291,7 +314,7 @@ else { : - ' style='width:100%' /> + ' style='width:100%' /> @@ -316,7 +339,7 @@ else { : ' style='width:100%' + value='' style='width:100%' onkeyup='maskkeyup(this,"")' onblur='maskblur(this,"")' /> @@ -326,14 +349,74 @@ else { : - ' /> + ' /> - : + : - ' /> + + + + +" . + htmlspecialchars($pwrow['title']) . "\n"; + } +?> + + + + +"; + echo ""; + echo "  \n"; + } +?> + + + + +"; + echo ""; + echo "\n"; + } +?> + +
 
  + ' + title='' + />   +
  + ' + title='' + /> +
@@ -349,7 +432,7 @@ else { : - ' /> + ' /> @@ -374,7 +457,7 @@ else { : - ' /> + ' /> @@ -382,7 +465,7 @@ else { : ' onclick='sel_related()' + value='' onclick='sel_related()' title='' style='width:100%' readonly /> @@ -432,9 +515,10 @@ else { $prices = array(); $pres = sqlStatement("SELECT lo.option_id, p.pr_price " . "FROM list_options AS lo LEFT OUTER JOIN prices AS p ON " . - "p.pr_id = '$drug_id' AND p.pr_selector = '$selector' AND " . + "p.pr_id = ? AND p.pr_selector = ? AND " . "p.pr_level = lo.option_id " . - "WHERE list_id = 'pricelevel' ORDER BY lo.seq"); + "WHERE list_id = 'pricelevel' ORDER BY lo.seq", + array($drug_id, $selector)); while ($prow = sqlFetchArray($pres)) { $prices[$prow['option_id']] = $prow['pr_price']; } diff --git a/sql/4_1_0-to-4_1_1_upgrade.sql b/sql/4_1_0-to-4_1_1_upgrade.sql index 62ea8c7ad..2baee95df 100644 --- a/sql/4_1_0-to-4_1_1_upgrade.sql +++ b/sql/4_1_0-to-4_1_1_upgrade.sql @@ -113,3 +113,18 @@ INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`) VALUES ('nat ALTER TABLE `insurance_data` ADD COLUMN `policy_type` varchar(25) NOT NULL default ''; #EndIf +#IfMissingColumn drugs max_level +ALTER TABLE drugs ADD max_level float NOT NULL DEFAULT 0.0; +ALTER TABLE drugs CHANGE reorder_point reorder_point float NOT NULL DEFAULT 0.0; +#EndIf + +#IfNotTable product_warehouse +CREATE TABLE `product_warehouse` ( + `pw_drug_id` int(11) NOT NULL, + `pw_warehouse` varchar(31) NOT NULL, + `pw_min_level` float DEFAULT 0, + `pw_max_level` float DEFAULT 0, + PRIMARY KEY (`pw_drug_id`,`pw_warehouse`) +) ENGINE=MyISAM; +#EndIf + diff --git a/sql/database.sql b/sql/database.sql index ae6c33aeb..6f1303914 100644 --- a/sql/database.sql +++ b/sql/database.sql @@ -735,7 +735,8 @@ CREATE TABLE `drugs` ( `name` varchar(255) NOT NULL DEFAULT '', `ndc_number` varchar(20) NOT NULL DEFAULT '', `on_order` int(11) NOT NULL default '0', - `reorder_point` int(11) NOT NULL default '0', + `reorder_point` float NOT NULL DEFAULT 0.0, + `max_level` float NOT NULL DEFAULT 0.0, `last_notify` date NOT NULL default '0000-00-00', `reactions` text, `form` int(3) NOT NULL default '0', @@ -5148,3 +5149,12 @@ CREATE TABLE `template_users` ( PRIMARY KEY (`tu_id`), UNIQUE KEY `templateuser` (`tu_user_id`,`tu_template_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; + +CREATE TABLE `product_warehouse` ( + `pw_drug_id` int(11) NOT NULL, + `pw_warehouse` varchar(31) NOT NULL, + `pw_min_level` float DEFAULT 0, + `pw_max_level` float DEFAULT 0, + PRIMARY KEY (`pw_drug_id`,`pw_warehouse`) +) ENGINE=MyISAM; + diff --git a/version.php b/version.php index abe5ec9b4..14b242398 100644 --- a/version.php +++ b/version.php @@ -17,5 +17,5 @@ $v_realpatch = '0'; // is a database change in the course of development. It is used // internally to determine when a database upgrade is needed. // -$v_database = 53; // last updated by sunsetsystems 2011-11-01 +$v_database = 54; // last updated by sunsetsystems 2011-11-22 ?> -- 2.11.4.GIT