From 0b60b0ccc66cbcc26619ac762b8881f52fa85bf7 Mon Sep 17 00:00:00 2001 From: Camil Staps Date: Wed, 20 Jul 2016 10:54:07 +0200 Subject: Discounts --- classes/BusinessAdmin.class.php | 42 ++++++ classes/constants.class.php | 2 +- classes/discount.class.php | 285 ++++++++++++++++++++++++++++++++++++++++ classes/offer.class.php | 91 ++++++++++++- include/discounts-edit.php | 59 +++++++++ include/discounts-new.php | 44 +++++++ include/discounts-overview.php | 159 ++++++++++++++++++++++ include/discounts.php | 98 ++++++++++++++ include/offers-overview.php | 285 ++++++++++++++++++++-------------------- include/offers-view.php | 42 ++++++ index.php | 5 +- install/index.php | 19 ++- install/upgrade.php | 49 ++++--- nav.php | 5 +- 14 files changed, 1015 insertions(+), 170 deletions(-) create mode 100644 classes/discount.class.php create mode 100644 include/discounts-edit.php create mode 100644 include/discounts-new.php create mode 100644 include/discounts-overview.php create mode 100644 include/discounts.php diff --git a/classes/BusinessAdmin.class.php b/classes/BusinessAdmin.class.php index d90e341..23e9c74 100644 --- a/classes/BusinessAdmin.class.php +++ b/classes/BusinessAdmin.class.php @@ -193,6 +193,48 @@ class BusinessAdmin { return $assignments; } + /** + * Get all discount ids + * + * @see BusinessAdmin::getDiscounts() This funtion returns instances of the discount class instead of just the ids + * + * @param PDO $pdo The PDO class for database connection + * @param string[] $where An array of WHERE clauses that will be AND-ed + * + * @throws PDOException Is something went wrong with the database + * + * @return int[] The ids + */ + public static function getDiscountIds($pdo, $where = array()) { + $ids = array(); + $discounts = $pdo->query("SELECT `id` FROM `".constants::db_prefix."discount`" . ((count($where) > 0) ? (" WHERE (" . implode(') AND (', $where) . ")") : ""))->fetchAll(PDO::FETCH_ASSOC); + foreach ($discounts as $discount) { + $ids[] = $discount['id']; + } + return $ids; + } + + /** + * Get all discounts + * + * @see BusinessAdmin::getDiscountIds() This function returns just the ids of the discounts, and not instances of the discount class + * + * @param PDO $pdo The PDO class for database connection + * @param string[] $where An array of WHERE clauses that will be AND-ed + * + * @throws PDOException If something went wrong with the database + * + * @return discount[] An array indexed by id of instances of the discount class + */ + public static function getDiscounts($pdo, $where = array()) { + $ids = self::getDiscountIds($pdo, $where); + $discounts = array(); + foreach ($ids as $id) { + $discounts[$id] = new discount($pdo, $id); + } + return $discounts; + } + //------------------------------------------------------------------------------ // Other functions //------------------------------------------------------------------------------ diff --git a/classes/constants.class.php b/classes/constants.class.php index ad63521..09b9f98 100644 --- a/classes/constants.class.php +++ b/classes/constants.class.php @@ -70,5 +70,5 @@ class constants { const fa_valuta = 'eur'; /** @const version Version of BusinessAdmin. Don't change this yourself! */ - const version = '0.2.2'; + const version = '0.3'; } diff --git a/classes/discount.class.php b/classes/discount.class.php new file mode 100644 index 0000000..71aa370 --- /dev/null +++ b/classes/discount.class.php @@ -0,0 +1,285 @@ +. + */ + +/** + * An interface to the discount table in the database + */ +class discount { + /** + * @var pdo $pdo The PDO class for database communication + * @var int $id The id of the discount + * @var int $offerId The id of the offer this discount is linked to + * @var string $title The title of the discount + * @var string $description The description of the discount + * @var float $value The actual discount + * @var float $vat The percentage of VAT to calculate on this discount + */ + protected $pdo, $offerId, $id, $title, $description, $value, $vat; + + const SUBTOTAL = 1; + const VAT = 2; + const TOTAL = 3; + + /** + * Create a new instance + * + * @param PDO $pdo The PDO class, to access the database + * @param int $id The id of the discount to fetch + * + * @throws PDOException If something went wrong with the database + * @throws Exception If the discount could not be found + */ + public function __construct($pdo, $id) { + $this->pdo = $pdo; + + $stmt = $this->pdo->prepare("SELECT * FROM `".constants::db_prefix."discount` WHERE `id`=?"); + $stmt->execute(array($id)); + if ($stmt->rowCount() == 0) { + throw new Exception("The discount with id '$id' could not be found."); + } + $discount = $stmt->fetch(PDO::FETCH_ASSOC); + + $this->id = $discount['id']; + $this->offerId = $discount['offerId']; + $this->title = $discount['title']; + $this->description = $discount['description']; + $this->value = $discount['value']; + $this->vat = $discount['VAT_percentage']; + } + + //------------------------------------------------------------------------------ + // Getters and setters + //------------------------------------------------------------------------------ + + /** + * Get the ID of the discount + * + * @return int The ID + */ + public function getId() { + return $this->id; + } + + /** + * Get the ID of the offer that this discount is linked to + * + * @return int The ID + */ + public function getOfferId() { + return $this->offerId; + } + + /** + * Get the offer that this discount is linked to + * + * @return offer The offer + */ + public function getOffer() { + return new offer($this->pdo, $this->offerId); + } + + /** + * Get the title of the discount + * + * @return string The title + */ + public function getTitle() { + return $this->title; + } + + /** + * Set the title of the discount + * + * @param string $title The new title for the discount + * + * @throws PDOException If something went wrong with the database + * + * @return bool True on succes, false on failure + */ + public function setTitle($title) { + $stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."discount` SET `title`=? WHERE `id`=?"); + $stmt->execute(array($title, $this->id)); + if ($stmt->rowCount() == 1) { + $this->title = $title; + return true; + } else { + return false; + } + } + + /** + * Get the description of the discount + * + * @param bool $parseMarkdown Whether or not to parse markdown + * + * @return string The description + */ + public function getDescription($parseMarkdown = true) { + if ($parseMarkdown) { + $pd = new Parsedown; + return $pd->text($this->description); + } else { + return $this->description; + } + } + + /** + * Set the description of the discount + * + * @param string $description The new description for the discount + * + * @throws PDOException If something went wrong with the database + * + * @return bool True on succes, false on failure + */ + public function setDescription($description) { + $stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."discount` SET `description`=? WHERE `id`=?"); + $stmt->execute(array($description, $this->id)); + if ($stmt->rowCount() == 1) { + $this->description = $description; + return true; + } else { + return false; + } + } + + /** + * Get the value of the discount + * + * @return float The value + */ + public function getValue() { + return $this->value; + } + + /** + * Set the value of the discount + * + * @param float $value The new value for the discount + * + * @throws PDOException If something went wrong with the database + * + * @return bool True on succes, false on failure + */ + public function setValue($value) { + $stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."discount` SET `value`=? WHERE `id`=?"); + $stmt->execute(array($value, $this->id)); + if ($stmt->rowCount() == 1) { + $this->value = $value; + return true; + } else { + return false; + } + } + + /** + * Get the VAT percentage of the discount + * + * @return float The VAT percentage + */ + public function getVAT() { + return $this->vat; + } + + /** + * Set the VAT percentage of the discount + * + * @param float $vat The new VAT percentage for the discount + * + * @throws PDOException If something went wrong with the database + * + * @return bool True on succes, false on failure + */ + public function setVAT($vat) { + $stmt = $this->pdo->prepare("UPDATE `".constants::db_prefix."discount` SET `VAT_percentage`=? WHERE `id`=?"); + $stmt->execute(array($vat, $this->id)); + if ($stmt->rowCount() == 1) { + $this->vat = $vat; + return true; + } else { + return false; + } + } + + //------------------------------------------------------------------------------ + // Other functions + //------------------------------------------------------------------------------ + + /** + * Calculate useful numbers about the discount + * + * Subtotal: value + * + * VAT: subtotal \* VAT_percentage + * + * Total: subtotal + VAT + * + * @param int $what Any of discount::SUBTOTAL, discount::VAT, discount::TOTAL + * @param int $round How many decimals to round on + * @param bool $format Whether to format the number nicely (for output) or not (for calculations) + * + * @throws PDOException If something went wrong with the database + * + * @return float|bool The calculated value rounded on $round decimals, or false when the input is incorrect + */ + public function calculate($what = self::TOTAL, $round = 2, $format = true) { + $return = 0; + switch ($what) { + case self::SUBTOTAL: + $return = - $this->value; + break; + case self::VAT: + $return = $this->calculate(self::SUBTOTAL, $round + 1, false) * $this->getVAT() / 100; + break; + case self::TOTAL: + $return = $this->calculate(self::SUBTOTAL, $round + 1, false) + $this->calculate(self::VAT, $round + 1, false); + break; + default: + return false; + } + if ($format) { + return number_format($return, $round); + } else { + return round($return, $round); + } + } + + /** + * Remove this discount from the database + * + * If this doesn't succeed (i.e. false is returned), that means the discount was removed manually or by another instance of this class + * + * @throws PDOException If something went wrong with the database + * + * @return bool True on success, false on failure + */ + public function delete() { + $stmt = $this->pdo->prepare("DELETE FROM `".constants::db_prefix."discount` WHERE `id`=?"); + $stmt->execute(array($this->id)); + if ($stmt->rowCount() != 1) { + return false; + } else { + return true; + } + } +} diff --git a/classes/offer.class.php b/classes/offer.class.php index 1b80ce0..75cb4f6 100644 --- a/classes/offer.class.php +++ b/classes/offer.class.php @@ -144,6 +144,42 @@ class offer { return $assignments; } + /** + * Get all discount ids for this offer + * + * @see offer::getDiscounts() This funtion returns instances of the discount class instead of just the ids + * + * @throws PDOException Is something went wrong with the database + * + * @return int[] The ids + */ + public function getDiscountIds() { + $ids = array(); + $discounts = $this->pdo->query("SELECT `id` FROM `".constants::db_prefix."discount` WHERE `offerId`={$this->id}")->fetchAll(PDO::FETCH_ASSOC); + foreach ($discounts as $discount) { + $ids[] = $discount['id']; + } + return $ids; + } + + /** + * Get all discounts for this offer + * + * @see offer::getDiscountIds() This function returns just the ids of the discounts, and not instances of the discount class + * + * @throws PDOException If something went wrong with the database + * + * @return discount[] An array indexed by id of instances of the discount class + */ + public function getDiscounts() { + $ids = $this->getDiscountIds(); + $discounts = array(); + foreach ($ids as $id) { + $discounts[$id] = new discount($this->pdo, $id); + } + return $discounts; + } + /** * Get the start date of the assignment * @@ -359,16 +395,21 @@ class offer { $return = 0; switch ($what) { case self::SUBTOTAL: - $assignments = $this->getAssignments(); - foreach ($assignments as $assignment) { + foreach ($this->getAssignments() as $assignment) { $return += $assignment->calculate(assignment::SUBTOTAL, $round + 1, false); } + foreach ($this->getDiscounts() as $discount) { + $return += $discount->calculate(discount::SUBTOTAL, $round + 1, false); + } break; case self::VAT: $assignments = $this->getAssignments(); foreach ($assignments as $assignment) { $return += $assignment->calculate(assignment::VAT, $round + 1, false); } + foreach ($this->getDiscounts() as $discount) { + $return += $discount->calculate(discount::VAT, $round + 1, false); + } break; case self::TOTAL: $return = $this->calculate(self::SUBTOTAL, $round + 1, false) + $this->calculate(self::VAT, $round + 1, false); @@ -431,6 +472,36 @@ class offer { } else { $error = $stmt->errorInfo(); throw new Exception($error[2]); + } + } + + /** + * Make a new discount linked to this order + * + * @param string $title The title for this discount + * @param string $description The description for this discount + * @param float $value The value for this discount + * @param float $vat The VAT percentage (so, 21 for 21%, not 0.21!) + * + * @throws PDOException If something went wrong with the database + * @throws Exception If there was a problem with the input + * + * @return discount A new instance of the discount class containing the new discount + */ + public function createDiscount($title, $description, $value, $vat) { + $stmt = $this->pdo->prepare("INSERT INTO `".constants::db_prefix."discount` (`offerId`,`title`,`description`,`value`,`VAT_percentage`) VALUES (?,?,?,?,?)"); + $stmt->execute(array( + $this->id, + $title, + $description, + $value, + $vat + )); + if ($stmt->rowCount() == 1) { + return new discount($this->pdo, $this->pdo->lastInsertId()); + } else { + $error = $stmt->errorInfo(); + throw new Exception($error[2]); } } @@ -459,15 +530,21 @@ class offer { $invoice_nr = str_replace(array('invoice-','.pdf'), array('',''), $file->getFilename()); } - $assignments = $this->getAssignments(); $list = array(); - foreach ($assignments as $assignment) + foreach ($this->getAssignments() as $assignment) $list[] = array( - $assignment->getTitle(), - $assignment->getPricePerHour() * $assignment->getHours(), - $assignment->getVAT() . "%", + $assignment->getTitle(), + $assignment->getPricePerHour() * $assignment->getHours(), + $assignment->getVAT() . "%", $assignment->getPricePerHour() * $assignment->getHours() * (1 + $assignment->getVAT() / 100) ); + foreach ($this->getDiscounts() as $discount) + $list[] = array( + $discount->getTitle(), + $discount->calculate(discount::SUBTOTAL), + $discount->getVAT() . "%", + $discount->calculate(discount::TOTAL) + ); $pdf = new correspondence(); $pdf->SetContact($this->getContact()); diff --git a/include/discounts-edit.php b/include/discounts-edit.php new file mode 100644 index 0000000..e6859a1 --- /dev/null +++ b/include/discounts-edit.php @@ -0,0 +1,59 @@ +. + */ + +require_once('./conf.php'); + +$response = new response(); + +try { + $discount = new discount($_pdo, $_REQUEST['pk']); + + $name = explode('-', $_REQUEST['name']); + $what_to_edit = $name[count($name) - 1]; + switch ($what_to_edit) { + case 'title': + $response->success = $discount->setTitle($_REQUEST['value']); + break; + case 'value': + $response->success = $discount->setValue($_REQUEST['value']); + break; + case 'vat': + $response->success = $discount->setVAT($_REQUEST['value']); + break; + case 'description': + $response->success = $discount->setDescription($_REQUEST['value']); + break; + default: + $response->http_response_code(404); + $response->success = false; + } + if (!$response->success && $response->http_response_code() == 200) { + $response->http_response_code(500); + $response->message = "The discount could not be edited due to an error."; + } +} catch (PDOException $e) { + $response->http_response_code(500); + $response->success = false; + $response->message = "The discount could not be edited due to a PDO error ({$e->getMessage()})."; +} catch (Exception $e) { + $response->http_response_code(404); + $response->success = false; + $response->message = "The discount could not be edited due to an exception."; +} +echo $response->message; diff --git a/include/discounts-new.php b/include/discounts-new.php new file mode 100644 index 0000000..1900d47 --- /dev/null +++ b/include/discounts-new.php @@ -0,0 +1,44 @@ +. + */ + +require_once('./conf.php'); + +$response = new response(); + +try { + $offer = new offer($_pdo, $_REQUEST['offerId']); + + $discount = $offer->createDiscount( + $_REQUEST['title'], + $_REQUEST['description'], + $_REQUEST['value'], + $_REQUEST['vat'] + ); + $response->success = true; + $response->message = "Assignment {$discount->getTitle()} has been succesfully created. Refresh the page."; +} catch (PDOException $e) { + $response->http_response_code(500); + $response->success = false; + $response->message = "The discount could not be created due to a PDO error ({$e->getMessage()})."; +} catch (Exception $e) { + $response->http_response_code(404); + $response->success = false; + $response->message = "The discount could not be created due to an error."; +} +echo $response->getJson(); diff --git a/include/discounts-overview.php b/include/discounts-overview.php new file mode 100644 index 0000000..6160a8c --- /dev/null +++ b/include/discounts-overview.php @@ -0,0 +1,159 @@ +. + */ +?> + +
+
+
Overview
+
+ + + + + + + + + + + + getId()}' + data-mixer-order-offer='{$discount->getOffer()->getId()}' + data-mixer-order-value='{$discount->getValue()}'> + + + + + "; + } + if (count($discounts) == 0) { + echo ""; + } + ?> + +
#OfferBriefingValueTools
{$discount->getId()} + #{$discount->getOffer()->getId()} to + {$discount->getOffer()->getContact()->getName()} + ({$discount->getOffer()->getContact()->getClient()->getName()}) + + {$discount->getTitle()}
+

{$discount->getDescription(false)}

+ 		+ ".constants::invoice_valuta."{$discount->getValue()}
+ {$discount->getVAT()}% VAT + 		+ + +
There are no discounts in the database. Why not start with creating one, below?
+
+
+
+
+
+
Create new
+
+
+
+
+
+ + +
+
+ + +
+
+ + +
+
+
+
+ + +
+
+ + +
+
+
+ +
+
+ +
+
+
diff --git a/include/discounts.php b/include/discounts.php new file mode 100644 index 0000000..83cb1b8 --- /dev/null +++ b/include/discounts.php @@ -0,0 +1,98 @@ +. + */ + +require_once('./index.php'); +require('./header.php'); +?> + +
+ + + +
+
+ View information of the discount with id + // ?delete= Delete the discount with id + //------------------------------------------------------------------------------ + + // The header of the page + $header = 'Discounts'; + // Whether or not to show an individual discount in the end (false if not, or the id if yes) + $show_individual = false; + + // View discount + if (isset($_GET['id'])) { + $id = (int) $_GET['id']; + try { + $discount = new discount($_pdo, $id); + $header = "Discounts / {$discount->getTitle()}"; + $show_individual = $id; + } catch (PDOException $e) { + $alert = "
The discount with id $id could not be found.
"; + } catch (Exception $e) { + $alert = "
The discount with id $id could not be found.
"; + } + } + + // Show the header + echo "

$header

"; + if (isset($alert)) echo "
$alert
"; + + // Delete discount + if (isset($_GET['delete'])) { + echo "
"; + $id = (int) $_GET['delete']; + try { + $discount = new discount($_pdo, $id); + if ($discount->delete()) { + echo "
The discount with title {$discount->getTitle()} has been removed.
"; + } else { + echo "
The discount with title {$discount->getTitle()} could not be removed. Perhaps it's already removed?
"; + } + } catch (PDOException $e) { + echo "
The discount could not be removed due to a PDO error.
"; + } catch (Exception $e) { + echo "
The discount with id {$id} could not be found.
"; + } + + echo "
"; + } + + if ($show_individual !== false) { + $_id = $show_individual; + require('discounts-view.php'); + } else { + require('discounts-overview.php'); + } + ?> +
+ +
+ + +
+ + + diff --git a/include/offers-overview.php b/include/offers-overview.php index 566f387..594f01c 100644 --- a/include/offers-overview.php +++ b/include/offers-overview.php @@ -19,151 +19,154 @@ ?>
-
-
Overview
-
- - - - - - - - - - - - - getInvoiceFile(); +
+
Overview
+
+
#ContactAssignmentsDatesInvoiceTools
+ + + + + + + + + + + + getInvoiceFile(); - echo " - - - - - - - "; - } - if (count($offers) == 0) { - echo ""; - } - ?> - -
#ContactAssignments & discountsDatesInvoiceTools
{$offer->getId()}{$offer->getContact()->getName()}"; - foreach ($offer->getAssignments() as $assignment) { - echo "{$assignment->getTitle()}
(".constants::invoice_valuta."{$assignment->calculate(assignment::SUBTOTAL)} excl. VAT, ".constants::invoice_valuta."{$assignment->calculate(assignment::TOTAL)} incl. VAT)

{$assignment->getDescription()}

"; - } - echo "		 - - - - - - - - - - - - - - - - - -
From:".BusinessAdmin::formatDate($offer->getStartDate(),false,true)."
To:".BusinessAdmin::formatDate($offer->getEndDate(),false,true)."
Invoice:".BusinessAdmin::formatDate($offer->getInvoiceDate(),false,true)."
Payment received:".BusinessAdmin::formatDate($offer->getPaymentReceived(),false,true)."
- 		" - . (($invoiceFile instanceof file) - ? " - - " - : "") - . "
- - - - - - - - - - - - - -
Subtotal:".constants::invoice_valuta."{$offer->calculate(offer::SUBTOTAL)}
VAT:".constants::invoice_valuta."{$offer->calculate(offer::VAT)}
Total:".constants::invoice_valuta."{$offer->calculate(offer::TOTAL)}
- 		- -
There are no offers in the database. Why not start with creating one, below?
-
-
+ echo " + {$offer->getId()} + {$offer->getContact()->getName()} + "; + foreach ($offer->getAssignments() as $assignment) { + echo "{$assignment->getTitle()}
(".constants::invoice_valuta."{$assignment->calculate(assignment::SUBTOTAL)} excl. VAT, ".constants::invoice_valuta."{$assignment->calculate(assignment::TOTAL)} incl. VAT)

{$assignment->getDescription()}

"; + } + foreach ($offer->getDiscounts() as $discount) { + echo "{$discount->getTitle()}
(".constants::invoice_valuta."{$discount->calculate(discount::SUBTOTAL)} excl. VAT, ".constants::invoice_valuta."{$discount->calculate(discount::TOTAL)} incl. VAT)

{$discount->getDescription()}

"; + } + echo " + + + + + + + + + + + + + + + + + + +
From:".BusinessAdmin::formatDate($offer->getStartDate(),false,true)."
To:".BusinessAdmin::formatDate($offer->getEndDate(),false,true)."
Invoice:".BusinessAdmin::formatDate($offer->getInvoiceDate(),false,true)."
Payment received:".BusinessAdmin::formatDate($offer->getPaymentReceived(),false,true)."
+ + " + . (($invoiceFile instanceof file) + ? " + + " + : "") + . "
+ + + + + + + + + + + + + +
Subtotal:".constants::invoice_valuta."{$offer->calculate(offer::SUBTOTAL)}
VAT:".constants::invoice_valuta."{$offer->calculate(offer::VAT)}
Total:".constants::invoice_valuta."{$offer->calculate(offer::TOTAL)}
+ + + + + "; + } + if (count($offers) == 0) { + echo "There are no offers in the database. Why not start with creating one, below?"; + } + ?> + + +
+
-
-
Create new
-
-
-
-
- - -
- -
- -
-
-
\ No newline at end of file + // Callback for when form submission encountered an error + function newOfferError() { + $('#newOffer .ajaxify-response') + .addClass('alert-danger') + .show() + .find('.ajaxify-response-text') + .html('An unknown error occurred. Please contact support.'); + + $('#newOffer input, #newOffer button').prop('disabled', false); + $('#newOffer').data('bootstrapValidator').resetForm(); + } + + + + diff --git a/include/offers-view.php b/include/offers-view.php index c578522..eec7701 100644 --- a/include/offers-view.php +++ b/include/offers-view.php @@ -127,3 +127,45 @@ $_offer = new offer($_pdo, $_id); +
+
+
Discounts
+
+ + + + + + + + + + + getId()}")); + foreach ($discounts as $discount) { + echo " + + + + + "; + } + if (count($discounts) == 0) { + echo ""; + } + ?> + +
#BriefingValueTools
{$discount->getId()} + {$discount->getTitle()}
+

{$discount->getDescription()}

+ 		+ ".constants::invoice_valuta."{$discount->getValue()} / hr
+ {$discount->getVAT()}% VAT + 		+ + +
There are no discounts in the database. Why not start with creating one, below?
+
+
+
diff --git a/index.php b/index.php index 87c73c1..129c83c 100644 --- a/index.php +++ b/index.php @@ -49,6 +49,9 @@ $pages = array( '/assignments' => './include/assignments.php', '/assignments/new' => './include/assignments-new.php', '/assignments/edit' => './include/assignments-edit.php', + '/discounts' => './include/discounts.php', + '/discounts/new' => './include/discounts-new.php', + '/discounts/edit' => './include/discounts-edit.php', '/about' => './include/about.php', '/ajax/collapse' => './include/ajax-collapse.php' ); @@ -66,4 +69,4 @@ if ($_page === null) { $_page = '/404'; http_response_code(404); require('./include/404.php'); -} \ No newline at end of file +} diff --git a/install/index.php b/install/index.php index 8a3d0b0..6c57769 100644 --- a/install/index.php +++ b/install/index.php @@ -57,6 +57,16 @@ if (isset($_GET['create_tables'])) { KEY `clientId` (`clientId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1"); + $_pdo->query("CREATE TABLE IF NOT EXISTS `".constants::db_prefix."discount` ( + `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `offerId` smallint(5) unsigned NOT NULL, + `title` tinytext NOT NULL, + `description` text NOT NULL, + `value` float unsigned NOT NULL, + `VAT_percentage` float NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"); + $_pdo->query("CREATE TABLE IF NOT EXISTS `".constants::db_prefix."file` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `filename` varchar(100) NOT NULL, @@ -81,10 +91,13 @@ if (isset($_GET['create_tables'])) { ) ENGINE=InnoDB DEFAULT CHARSET=latin1"); $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` - ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`)"); + ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`)"); $_pdo->query("ALTER TABLE `".constants::db_prefix."contact` - ADD CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `".constants::db_prefix."client` (`id`)"); + ADD CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `".constants::db_prefix."client` (`id`)"); + + $_pdo->query("ALTER TABLE `".constants::db_prefix."discount` + ADD CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`);"); $_pdo->query("ALTER TABLE `".constants::db_prefix."offer` ADD CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`invoice_fileId`) REFERENCES `".constants::db_prefix."file` (`id`), @@ -118,4 +131,4 @@ if (isset($_GET['create_folders'])) {
  • Create folders
    • -

    When you're done, it would be the neatest to remove the /install folder (even though this whole control panel should not be accessible for the public).

    \ No newline at end of file +

    When you're done, it would be the neatest to remove the /install folder (even though this whole control panel should not be accessible for the public).

    diff --git a/install/upgrade.php b/install/upgrade.php index 55b4316..04cc03a 100644 --- a/install/upgrade.php +++ b/install/upgrade.php @@ -38,23 +38,40 @@ function lower_version($that, $new) { if (isset($_GET['upgrade'])) { - if (lower_version($_GET['upgrade'], '0.1')) { - try { - $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` CHANGE `hours` `hours` FLOAT UNSIGNED NOT NULL"); - } catch (PDOException $e) { - echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}
    " . $e->getTraceAsString(); - } - } - - if (lower_version($_GET['upgrade'], '0.2.2')) { - try { - $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` DROP FOREIGN KEY `assignment_ibfk_1`"); - $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE"); - } catch (PDOException $e) { - echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}
    " . $e->getTraceAsString(); - } + if (lower_version($_GET['upgrade'], '0.1')) { + try { + $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` CHANGE `hours` `hours` FLOAT UNSIGNED NOT NULL"); + } catch (PDOException $e) { + echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}
    " . $e->getTraceAsString(); + } + } - } + if (lower_version($_GET['upgrade'], '0.2.2')) { + try { + $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` DROP FOREIGN KEY `assignment_ibfk_1`"); + $_pdo->query("ALTER TABLE `".constants::db_prefix."assignment` ADD CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE"); + } catch (PDOException $e) { + echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}
    " . $e->getTraceAsString(); + } + } + + if (lower_version($_GET['upgrade'], '0.3')) { + try { + $_pdo->query("CREATE TABLE IF NOT EXISTS `".constants::db_prefix."discount` ( + `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `offerId` smallint(5) unsigned NOT NULL, + `title` tinytext NOT NULL, + `description` text NOT NULL, + `value` float unsigned NOT NULL, + `VAT_percentage` float NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"); + $_pdo->query("ALTER TABLE `".constants::db_prefix."discount` + ADD CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `".constants::db_prefix."offer` (`id`);"); + } catch (PDOException $e) { + echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}
    " . $e->getTraceAsString(); + } + } echo "
    All done."; } diff --git a/nav.php b/nav.php index 155a95a..4ff4a57 100644 --- a/nav.php +++ b/nav.php @@ -108,6 +108,9 @@
  • href="/assignments"> Assignments
    • +
  • + href="/discounts"> Discounts +
  • href="/about"> About
    • @@ -126,4 +129,4 @@ - \ No newline at end of file + -- cgit v1.2.3