#use database
USE association;

#First make copy of Excel data
#treat all fields as text
DROP TABLE IF EXISTS `alldata`;

CREATE TABLE `alldata` (
  `lastname` VARCHAR(50) NOT NULL,
  `firstname` VARCHAR(50) NOT NULL,
  `certification` VARCHAR(10) NOT NULL,
  `expirydate` VARCHAR(10) NOT NULL,
  `streetaddress1` VARCHAR(50) NOT NULL,
  `streetaddress2` VARCHAR(50) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `zipcode` VARCHAR(10) NOT NULL,
  `certificationnumber` VARCHAR(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#get data from tab-separated file created from spreadsheet
#Unfortunately, variable below won't work with LOAD DATA
#SET @filename = "/home/peter/Documents/spreadsheet/data.tsv";
#so hard code
LOAD DATA INFILE "/home/peter/Documents/spreadsheet/data.tsv" 
  INTO TABLE alldata;
#Our server and client are on the same machine
#Don't need "LOCAL" if file is on the server (local means local to the client)
#will need the FILE privilege though
#but if it's there you can execute this script from somewhere else on your network
#Syntax with comma separated fields -- not as safe as tabs 
#LOAD DATA INFILE "C:/Documents and Settings/peter/My Documents/spreadsheet/data.csv" 
#  INTO TABLE alldata 
#  FIELDS TERMINATED BY ","; 

#create temporary tables
#Association members information
DROP TABLE IF EXISTS `tempmembers`;

CREATE TABLE `tempmembers` (
  `unique_value` VARCHAR(150) DEFAULT NULL,
  `id` INT(11)  NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL DEFAULT '',
  `lastname` VARCHAR(40) NOT NULL DEFAULT '',
  `streetaddress1` VARCHAR(60) NOT NULL DEFAULT '',
  `streetaddress2` VARCHAR(60) NOT NULL DEFAULT '',
  `city` VARCHAR(60) NOT NULL DEFAULT '',
  `state` VARCHAR(10) NOT NULL DEFAULT '',
  `zipcode` VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tempmembers 
  SELECT DISTINCT CONCAT(firstname,lastname,streetaddress1) AS unique_value, 
  NULL AS id, firstname, lastname, 
  streetaddress1, streetaddress2, city, state, zipcode 
  FROM alldata;  

#Member accreditations
DROP TABLE IF EXISTS `tempmemberaccreditations`;
CREATE TABLE `tempmemberaccreditations` (
  `unique_value` VARCHAR(150) DEFAULT NULL,
  `certification` VARCHAR(10) NOT NULL,
  `memberid` INT(11) NOT NULL DEFAULT '0',
  `certificationnumber` VARCHAR(10) NOT NULL,
  `expirydate` DATE DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tempmemberaccreditations 
  SELECT CONCAT(firstname,lastname,streetaddress1) AS unique_value, 
  certification, 0 AS memberid, 
  certificationnumber, 
  STR_TO_DATE(expirydate, "%d-%b-%y") 
  FROM alldata;  
#Above format for 31-Dec-07
#for 12/1/2007 use "%c/%e/%Y"

#now relate the two tables and insert ids into the tempmemberaccreditations

UPDATE tempmemberaccreditations t2 
  INNER JOIN tempmembers t ON  t.unique_value=t2.unique_value
  SET t2.memberid =  t.id;

#create final version of tables 
DROP TABLE IF EXISTS `members`;
ALTER TABLE `tempmembers` 
  RENAME TO `members`,
  DROP COLUMN `unique_value`;     

DROP TABLE IF EXISTS `memberaccreditations`;
ALTER TABLE `tempmemberaccreditations` 
  RENAME TO `memberaccreditations`,
  DROP COLUMN `unique_value`;  

#add indices
ALTER TABLE `members` 
  ADD KEY `lastname_idx` (`lastname`),
  ADD KEY `city_idx` (`city`); 

ALTER TABLE `memberaccreditations` 
  ADD PRIMARY KEY (`certification`, `memberid`);  

#create accreditations table
DROP TABLE IF EXISTS accreditations; 
CREATE TABLE  accreditations
  SELECT DISTINCT certification AS acronym, '' AS description
  FROM alldata;
  
ALTER TABLE `accreditations`
  ADD PRIMARY KEY (`acronym`);
#now add views
DROP VIEW IF EXISTS vwOrphanedMembers;
CREATE VIEW `vwOrphanedMembers` AS
  SELECT `t`.`id` ,`t`.`firstname`,`t`.`lastname`  
  FROM `members` `t` 
  LEFT JOIN `memberaccreditations` `tma` 
  ON `t`.`id` = `tma`.`memberid`  
  WHERE ISNULL(`tma`.`memberid`); 

DROP VIEW IF EXISTS vwOrphanedAccreditations;
CREATE VIEW `vwOrphanedAccreditations` AS
  SELECT `tma`.`certification`,
  `tma`.`memberid`,`tma`.`certificationnumber`,
  `tma`.`expirydate` 
  FROM (`memberaccreditations` `tma` 
  LEFT JOIN `members` `t` ON ((`tma`.`memberid` = `t`.`id`))) 
  WHERE ISNULL(`t`.`id`); 
#remove spreadsheet-based table  
DROP TABLE IF EXISTS `alldata`;