v0.4.2 moved payment_received to separate table/class payment(s)

2 files changed, 32 insertions, 1 deletions

diff --git a/install/index.php b/install/index.php
index 213eec7..f565696 100644
--- a/install/index.php
+++ b/install/index.php
@@ -82,13 +82,19 @@ if (isset($_GET['create_tables'])) {
 			  `invoice_date` date NOT NULL,
 			  `accepted` tinyint(1) unsigned NOT NULL DEFAULT '0',
 			  `invoice_fileId` smallint(5) unsigned DEFAULT NULL,
-			  `payment_received` date DEFAULT NULL,
 			  PRIMARY KEY (`id`),
 			  UNIQUE KEY `invoice_fileId` (`invoice_fileId`),
 			  KEY `contactId` (`contactId`),
 			  KEY `contactId_2` (`contactId`),
 			  KEY `invoice_fileId_2` (`invoice_fileId`)
 			) ENGINE=InnoDB  DEFAULT CHARSET=latin1");
+
+		$_pdo->query("CREATE TABLE IF NOT EXISTS `payment` (
+				`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
+				`offerId` smallint(5) unsigned NOT NULL,
+				`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+				PRIMARY KEY (`id`)
+			) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
 		
 		$_pdo->query("CREATE TABLE IF NOT EXISTS `".constants::db_prefix."user` (
 			  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
@@ -110,6 +116,9 @@ if (isset($_GET['create_tables'])) {
 			  ADD CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`invoice_fileId`) REFERENCES `".constants::db_prefix."file` (`id`),
 			  ADD CONSTRAINT `offer_ibfk_2` FOREIGN KEY (`contactId`) REFERENCES `".constants::db_prefix."contact` (`id`)");
 
+		$_pdo->query("ALTER TABLE `payment`
+				ADD CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`offerId`) REFERENCES `offer` (`id`);");
+
 		echo "Succeeded creating the database tables.";
 
 	} catch (PDOException $e) {
diff --git a/install/upgrade.php b/install/upgrade.php
index e145ba7..78f5dff 100644
--- a/install/upgrade.php
+++ b/install/upgrade.php
@@ -86,6 +86,28 @@ if (isset($_GET['upgrade'])) {
 		}
 	}
 
+	if (lower_version($_GET['upgrade'], '0.4.2')) {
+		try {
+			$_pdo->query("CREATE TABLE IF NOT EXISTS `".constants::db_prefix."payment` (
+					`id` smallint(5) unsigned NOT NULL,
+					`offerId` smallint(5) unsigned NOT NULL,
+					`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+				) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
+
+			$offers = $_pdo->query("SELECT `id`,`payment_received` FROM `".constants::db_prefix."offer` WHERE `payment_received` IS NOT NULL");
+			$offers = $offers->fetchAll(PDO::FETCH_ASSOC);
+			foreach ($offers as $offer) {
+				$received = $offer['payment_received'];
+				$offer = new offer($_pdo, $offer['id']);
+				$offer->createPayment($received);
+			}
+
+			$_pdo->query("ALTER TABLE `".constants::db_prefix."offer` DROP `payment_received`;");
+		} catch (PDOException $e) {
+			echo "Altering the database structure failed with a PDOException ({$e->getCode()}): {$e->getMessage()}<br/>" . $e->getTraceAsString();
+		}
+	}
+
 	echo "<br/>All done.";
 }
 ?>