From 15df7b7bf130ff1345943557a7a922c672ca526f Mon Sep 17 00:00:00 2001 From: Chuck Scott Date: Mon, 5 Mar 2018 15:39:06 -0500 Subject: [PATCH] Added more tables to the database and some items to plugin.ini --- config/plugin.ini | 54 +++++ .../create_database_V0.0.1.sql | 195 +++++++++++++++++- setup/databaseScripts/dbVersions.php | 2 +- 3 files changed, 245 insertions(+), 6 deletions(-) diff --git a/config/plugin.ini b/config/plugin.ini index 9222f7e..d739f93 100644 --- a/config/plugin.ini +++ b/config/plugin.ini @@ -6,3 +6,57 @@ ; [common] + +; +; Area Units +; +area_unit[10] = 'Square Feet' +area_unit[20] = 'Square Meters' + +area_unit_numb['SqFeet'] = 10 +area_unit_numb['SqMeters'] = 20 + +; +; Asset Use Type +; +use_type[10] = 'Timed Use' +use_type[20] = 'Consumeable' + +use_type_numb['Timed'] = 10 +use_type_numb['Consumeable'] = 20 + +; +; Map Item Type +; +map_item_type[10] = 'Pointer' +map_item_type[20] = 'Circle' +map_item_type[30] = 'Square' + +map_item_type_numb['Pointer'] = 10 +map_item_type_numb['Circle'] = 20 +map_item_type_numb['Square'] = 10 + +; +; Assignment Status - Use as list only +; +assignment_status[0] = 'SELECTED' +assignment_status[10] = 'COMPLETE' +assignment_status[20] = 'UNPAID' +assignment_status[30] = 'CC_PEND' +assignment_status[40] = 'CC_DECL' +assignment_status[50] = 'PAYMENT_PEND' +assignment_status[60] = 'ON ARRIVAL' +assignment_status[70] = 'ADMIN HOLD' +assignment_status[80] = 'FAILED' +assignment_status[99] = 'CANCELED' + +assignment_status_numb['SELECTED'] = 0 +assignment_status_numb['COMPLETE'] = 10 +assignment_status_numb['UNPAID'] = 20 +assignment_status_numb['CC_PEND'] = 30 +assignment_status_numb['CC_DECL'] = 40 +assignment_status_numb['PAYMENT_PEND'] = 50 +assignment_status_numb['ON_ARRIVAL'] = 60 +assignment_status_numb['ADMIN_HOLD'] = 70 +assignment_status_numb['FAILED'] = 80 +assignment_status_numb['CANCELED'] = 99 diff --git a/setup/databaseScripts/create_database_V0.0.1.sql b/setup/databaseScripts/create_database_V0.0.1.sql index 65ab5c6..7483a97 100644 --- a/setup/databaseScripts/create_database_V0.0.1.sql +++ b/setup/databaseScripts/create_database_V0.0.1.sql @@ -29,14 +29,183 @@ CREATE TABLE {prefix}settings_terms ( id INT NOT NULL AUTO_INCREMENT, assets_term_assets_name TINYTEXT NULL, -- Term "Asset Management" assets_term_asset TINYTEXT NULL, - assets_term_asset_cap TINYTEXT NULL, assets_term_asset_plur TINYTEXT NULL, + assets_term_asset_cap TINYTEXT NULL, assets_term_asset_plur_cap TINYTEXT NULL, + assets_term_facility TINYTEXT NULL, + assets_term_facility_plur TINYTEXT NULL, + assets_term_facility_cap TINYTEXT NULL, + assets_term_facility_plur_cap TINYTEXT NULL, + assets_term_item TINYTEXT NULL, + assets_term_item_plur TINYTEXT NULL, + assets_term_item_cap TINYTEXT NULL, + assets_term_item_plur_cap TINYTEXT NULL, + PRIMARY KEY (id) +); + +---- + +-- An account for a person requesting an asset +-- A single account may be assocaiated with multiple assignments +CREATE TABLE {prefix}account ( + id INT NOT NULL AUTO_INCREMENT, + active BOOLEAN NULL, -- Is active flag (may be accessed or used) - default is true + validated BOOLEAN NULL, -- Flag indicating that the account has been validated - Set to false when recovering password + validation_code TINYTEXT NULL, -- Validation code and timestamp ("{validation code}-{timestamp}) - Clear this after validation + member_id TINYTEXT NULL, -- Free-form field for a member ID (not a GLM Associate member ID) + fname TINYTEXT NULL, -- Account primary address + lname TINYTEXT NULL, + org TINYTEXT NULL, + title TINYTEXT NULL, + addr1 TINYTEXT NULL, + addr2 TINYTEXT NULL, + city TINYTEXT NULL, + state TINYTEXT NULL, + zip TINYTEXT NULL, + country TINYTEXT NULL, + phone TINYTEXT NULL, + fax TINYTEXT NULL, + bill_fname TINYTEXT NULL, -- Last used billing information + bill_lname TINYTEXT NULL, + bill_org TINYTEXT NULL, + bill_title TINYTEXT NULL, + bill_addr1 TINYTEXT NULL, + bill_addr2 TINYTEXT NULL, + bill_city TINYTEXT NULL, + bill_state TINYTEXT NULL, + bill_zip TINYTEXT NULL, + bill_country TINYTEXT NULL, + bill_phone TINYTEXT NULL, + bill_fax TINYTEXT NULL, + email TINYTEXT NULL, + password TINYTEXT NULL, -- Crypted password for login back into this account + email_ok BOOLEAN NULL, + is_member BOOLEAN NULL, -- Is a member of the entity that owns the site + contact_id INT NULL, -- Pointer to GLM Associate member contact record if account is for a member contact + contact_fname TINYTEXT NULL, + contact_lname TINYTEXT NULL, + contact_org TINYTEXT NULL, + contact_title TINYTEXT NULL, + contact_addr1 TINYTEXT NULL, + contact_addr2 TINYTEXT NULL, + contact_city TINYTEXT NULL, + contact_state TINYTEXT NULL, + contact_zip TINYTEXT NULL, + contact_country TINYTEXT NULL, + contact_phone TINYTEXT NULL, + contact_fax TINYTEXT NULL, + contact_email TINYTEXT NULL, + contact_email_ok BOOLEAN NULL, + date_created DATE NULL, + notes TEXT NULL, -- System operator's notes for this account - not visible to account owner + user_trace_info TINYTEXT NULL, -- IP Address of user computer and timestamp of last update + PRIMARY KEY (id), + INDEX (member_id(10)), + INDEX (fname(10)), + INDEX (lname(10)), + INDEX (org(10)), + INDEX (city(10)), + INDEX (state(5)), + INDEX (zip(5)), + INDEX (email(10)), + INDEX (contact_id) +); + +---- + +-- Facility - A place where multiple assets are located and can be mapped +CREATE TABLE {prefix}facility ( + id INT NOT NULL AUTO_INCREMENT, + name TINYTEXT NULL, -- Name of facility + descr TEXT NULL, -- Description of this facility + address TINYTEXT NULL, -- Street Address + city INT NULL, -- Pointer to city - references main plugin city table + state TINYTEXT NULL, -- Two character state abbreviation + zip TINYTEXT NULL, -- ZIP/Postal code + country TINYTEXT NULL, -- Country Code + lat FLOAT NULL, -- Latitude of location + lon FLOAT NULL, -- Longitude of location + phone TINYTEXT NULL, -- Location Phone # + url TINYTEXT NULL, -- Location URL + email TINYTEXT NULL, -- Location E-Mail Address + facility_map TINYTEXT NULL, -- Image of facility map - To use for mapping asset items + PRIMARY KEY (id) +); + +---- + +-- Asset Types - Defines a type of asset that has a group of asset items (i.e. room) +CREATE TABLE {prefix}asset_type ( + id INT NOT NULL AUTO_INCREMENT, + name TINYTEXT NULL, -- Name of asset type + descr TEXT NULL, -- Description of this asset type + use_type SMALLINT NULL, -- Type of useage - Config list "use_type" + lead_time INTEGER NULL, -- Default lead time required to request this type asset - Abstract "interval" type + reuse_time INTEGER NULL, -- Default time required to prepair this type for reuse (blocks out this amount of time after end_time) - Abstract "interval" type + cost FLOAT NULL, -- Default cost for use of this asset type + hold_time SMALLINT NULL, -- Amount of time ( Minutes ) that this type may be held in an assignment prior to securing the assignment (checkout) + map_item_type SMALLINT NULL, -- May item type - Config List "map_item_type" + -- Map item size info goes here PRIMARY KEY (id) ); ---- +-- Asset Item - Defines a specific asset of a particular asset type +CREATE TABLE {prefix}asset_item ( + id INT NOT NULL AUTO_INCREMENT, + asset_type INT NULL, -- Pointer to asset_type entry + name TINYTEXT NULL, -- Name of asset - Text identification of asset + number INTEGER NULL, -- Asset Number - Numeric identification of asset of specified type + quant INTEGER NULL, -- Quantity of this asset_item - Use if identical items (i.e. Chairs) - Defualt 1 (single item) + descr TEXT NULL, -- Description of this asset item + area SMALLINT NULL, -- Area + area_units SMALLINT NULL, -- Unit used for area - config list + lead_time INTEGER NULL, -- Actual lead time required to request this item - Abstract "interval" type + reuse_time INTEGER NULL, -- Actual time required to prepair this type for reuse for this item - Abstract "interval" type + cost FLOAT NULL, -- Default cost for use of this asset type + facility INTEGER NULL, -- Pointer to facility + -- Map location data has to go here + PRIMARY KEY (id), + INDEX (asset_type), + INDEX (facility) +); + +---- + +-- Assets Assignment - An assignment of a groups of asset items for (or at) a particular time. - This is a kind of "Cart" entry +CREATE TABLE {prefix}asset_assignment ( + id INT NOT NULL AUTO_INCREMENT, + status TINYINT NULL, -- Status of this assignment - Config list "assignment_status" + name TINYTEXT NULL, -- Name of this assignment (Event, requester, ...) + account INTEGER NULL, -- Pointer to account record + start_time DATETIME NULL, -- Start time of assignment + end_time DATETIME NULL, -- End time of assignment + PRIMARY KEY (id), + INDEX (start_time), + INDEX (end_time), + INDEX (account) +); + +---- + +-- Asset Assigned - List of assets assigned as part of an "assignment" +CREATE TABLE {prefix}asset_assigned ( + id INT NOT NULL AUTO_INCREMENT, + asset_assignment INTEGER NULL, -- Pointer to asset_assignment + asset_item INTEGER NULL, -- Pointer to asset_item + asset_type INTEGER NULL, -- Pointer to asset_type + quant INTEGER NULL, -- Quantity of asset_item assigned - Default to 1 + hold_release_time DATETIME NULL, -- Time at which hold on this asset item is released (0 if assignment is complete) + PRIMARY KEY (id), + INDEX (asset_assignment), + INDEX (asset_item), + INDEX (asset_type), + INDEX (hold_release_time) +); + +---- + -- Insert into management table INSERT INTO {prefix}management ( @@ -54,15 +223,31 @@ INSERT INTO {prefix}settings_terms ( assets_term_assets_name, assets_term_asset, - assets_term_asset_cap, assets_term_asset_plur, - assets_term_asset_plur_cap + assets_term_asset_cap, + assets_term_asset_plur_cap, + assets_term_facility, + assets_term_facility_plur, + assets_term_facility_cap, + assets_term_facility_plur_cap, + assets_term_item, + assets_term_item_plur, + assets_term_item_cap, + assets_term_item_plur_cap ) VALUES ( 'Asset Management', -- assets_term_assets_name 'asset', -- assets_term_asset - 'Asset', -- assets_term_asset_cap 'assets', -- assets_term_asset_plur - 'Assets' -- assets_term_asset_plur_cap + 'Asset', -- assets_term_asset_cap + 'Assets', -- assets_term_asset_plur_cap + 'facility', -- assets_term_facility + 'facilities', -- assets_term_facility_plur + 'Facility', -- assets_term_facility_cap + 'Facilities', -- assets_term_facility_plur_cap + 'item', -- assets_term_asset + 'items', -- assets_term_asset_plur + 'Item', -- assets_term_asset_cap + 'Items' -- assets_term_asset_plur_cap ); diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index ac27d09..b101fcc 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -14,7 +14,7 @@ */ $glmMembersAssetsDbVersions = array( - '0.0.1' => array('version' => '0.0.1', 'tables' => 2, 'date' => '03/2/2018') + '0.0.1' => array('version' => '0.0.1', 'tables' => 8, 'date' => '03/5/2018') ); -- 2.17.1