From ce48f5f4f5eb0ed6de75e80f8aded1d402e9977d Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Wed, 8 Nov 2017 16:34:01 -0500
Subject: [PATCH] Adding first db script into plugin

Using tables from uptravel.
---
 .../create_database_V0.0.1.sql                | 58 +++++++++++++++++++
 setup/databaseScripts/readme.txt              | 44 ++++++++++++++
 2 files changed, 102 insertions(+)
 create mode 100644 setup/databaseScripts/create_database_V0.0.1.sql
 create mode 100644 setup/databaseScripts/readme.txt

diff --git a/setup/databaseScripts/create_database_V0.0.1.sql b/setup/databaseScripts/create_database_V0.0.1.sql
new file mode 100644
index 0000000..fdb8a54
--- /dev/null
+++ b/setup/databaseScripts/create_database_V0.0.1.sql
@@ -0,0 +1,58 @@
+-- Gaslight Media Billing Module
+-- File Created: 11/08/2017
+-- Database Version: 0.0.1
+-- Database Creation Script
+--
+-- To permit each query below to be executed separately,
+-- all queries must be separated by a line with four dashes
+--
+-- **** BE SURE TO ALSO UPDATE drop_database_Vxxx.sql FILE WHEN CHANGING TABLES ****
+--
+
+-- transactions
+CREATE TABLE {prefix}transactions (
+    id INT NOT NULL AUTO_INCREMENT,
+    invoice_id INT NOT NULL,
+    transaction_time TIMESTAMP NOT NULL,
+    transaction_date DATE NOT NULL,
+    ref_dest_name INT,
+    ref_dest INT,
+    account INT,
+    account_number INT NOT NULL,
+    billing_type INT NOT NULL,
+    amount DECIMAL(8, 2) NOT NULL,
+    balance DECIMAL(8, 2) NOT NULL,
+    payment_method INT,
+    payment_data TEXT,
+    paid BOOLEAN DEFAULT '0',
+    invoice TEXT,
+    notes TINYTEXT,
+    PRIMARY_KEY (id)
+);
+
+----
+
+-- payment_types
+CREATE TABLE {prefix}payment_types (
+    id INT _NOT NULL AUTO_INCREMENT,
+    name TINYTEXT NOT NULL,
+    qcode INT,
+    category TEXT NOT NULL,
+    amount DECIMAL(8, 2),
+    notes TINYTEXT,
+    dynamic_amount BOOLEAN DEFAULT '0',
+    PRIMARY_KEY (id)
+);
+
+----
+
+-- ref_account
+CREATE TABLE {prefix}ref_account (
+    id INT NOT NULL AUTO_INCREMENT,
+    ref_dest INT NOT NULL,
+    payment_type INT NOT NULL,
+    email_invoice BOOLEAN DEFAULT '0',
+    usmail_invoice BOOLEAN DEFAULT '0',
+    fax_invoice BOOLEAN DEFAULT '0',
+    PRIMARY_KEY (id)
+);
diff --git a/setup/databaseScripts/readme.txt b/setup/databaseScripts/readme.txt
new file mode 100644
index 0000000..f1c238e
--- /dev/null
+++ b/setup/databaseScripts/readme.txt
@@ -0,0 +1,44 @@
+This directory contains database creation and update scripts for this add-on.
+
+The files in this directory are checked by the glmCheckDatabase() function in the
+main plugin classes/glmPluginSupport.php file.
+
+This directory is optional. If there are no data tables that need to be created
+for this add-on, there should be no files in this directory. The directory may
+also be deleted. 
+
+See the "examples" directory for a sample of what can go in this directory.
+Procedure to update database
+-----------------------------
+
+0) Make a backup copy of the site's database.
+
+1) Rename "create_database_Vx.x.x.sql" to new version number.
+    example: create_database_V0.0.9.sql -> create_database_V0.0.10.sql
+
+2) Edit renamed create database file and make desired changes
+
+3) Add a new "update_database_Vx.x.x.sql" named with the correct version #.
+
+4) Edit new update database files with SQL script to make the necessary changes
+   from the previous version to the new version. (i.e. to add new fields,
+   rename fields, insert records, ...)
+
+5) Optionally add an "update_database_Vx.x.x.php" file if PHP scripting is
+   needed to update database content. (i.e. to make changes to database content)
+
+6) Edit the "dbVersions.php" file and add a new line for the new version.
+   *** Now please be sure to add a date for each entry ***
+   i.e. '1.1.2' => array('version' => '1.1.2', 'tables' => 14, 'date' => '4/11/16')
+
+7) When this is all done, edit the index.php file for the plugin/add-on and 
+   change "GLM_MEMBERS_{addon}_PLUGIN_DB_VERSION" defined parameter where
+   {addon} is the add-on name.
+
+8) Go to an admin menu item for the main member db plugin or any add-on. If all
+   goes well, the main plugin should have detected the change and updated the
+   database. If not, restore the database and try again.
+
+9) Check the database to make sure the changes to fields and data are correct.
+
+10) Rename the drop_database_Vx.x.x.sql to new version number (same as create)
-- 
2.17.1