-- This MySQL script produces tab-delimited output suitable for reading into an 
-- EXCEL Spreadsheet from the MOVES MySQL database output tables.
-- 2008-07-07 changed "distance" to the more general "activity" in the activity output table.
-- 2009-07-14 removed MOVESOutputRowID from the script.

-- Three separate text files are produced.  They are:
--      MovesOutput.txt
--      MovesActivityOutput.txt
--      MovesRun.txt

-- These correspond directly to the similarily named MOVES output tables.

-- The script does not write out the MovesError Table.

FLUSH TABLES;

-- This section writes the Moves Output Table to the text file MovesOutput.txt
		
		
DROP TABLE IF EXISTS Temp;
CREATE TABLE Temp  (
		MOVESRunID			CHAR(15),	
		YearID				CHAR(15),	   
        MonthID				CHAR(15),
        DayID				CHAR(15),
        HourID				CHAR(15),
        StateID				CHAR(15),
        CountyID 			CHAR(15),
        ZoneID 				CHAR(15),
        LinkID 				CHAR(15),
        PollutantID			CHAR(15),
        ProcessID 			CHAR(15),
        SourceTypeID 		CHAR(15),
        FuelTypeID			CHAR(15),
        ModelYearID 		CHAR(15),
        RoadTypeID			CHAR(15),
        SCC					CHAR(15),
        EmissionQuant		CHAR(30),
        EmissionQuantMean	CHAR(30),
        EmissionQuantSigma	CHAR(30),
        IterationID			CHAR(15)
);

INSERT INTO Temp VALUES ('MOVESRunID', 'YearID', 'MonthID',
		'DayID', 'HourID', 'StateID', 'CountyID', 'ZoneID', 'LinkID', 'PollutantID',
		'ProcessID', 'SourceTypeID', 'FuelTypeID','ModelYearID', 'RoadTypeID',
		'SCC', 'EmissionQuant', 'EmissionQuantMean', 'EmissionQuantSigma', 'IterationID');
  
INSERT INTO Temp (MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID, PollutantID,
		ProcessID, SourceTypeID, FuelTypeID,ModelYearID, RoadTypeID,
		SCC, EmissionQuant, EmissionQuantMean, EmissionQuantSigma, IterationID)
	SELECT MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID, PollutantID,
		ProcessID, SourceTypeID, FuelTypeID,ModelYearID, RoadTypeID,
		SCC, EmissionQuant, EmissionQuantMean, EmissionQuantSigma, IterationID
	FROM movesoutput;  



SELECT MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID, PollutantID,
		ProcessID, SourceTypeID, FuelTypeID,ModelYearID, RoadTypeID,
		SCC, EmissionQuant, EmissionQuantMean, EmissionQuantSigma, IterationID
	INTO OUTFILE 'MovesOutput.txt'
		FIELDS TERMINATED BY '\t' 
		LINES TERMINATED BY '\r\n' 
		FROM Temp;       
		



-- This section writes the Moves Activity Output Table to the text file 
-- MovesActivityOutput.txt

DROP TABLE if EXISTS Temp;
CREATE TABLE Temp  (
		MOVESRunID			CHAR(15),	
		YearID				CHAR(15),	   
        MonthID				CHAR(15),
        DayID				CHAR(15),
        HourID				CHAR(15),
        StateID				CHAR(15),
        CountyID 			CHAR(15),
        ZoneID 				CHAR(15),
        LinkID 				CHAR(15),
	    SourceTypeID 		CHAR(15),
        FuelTypeID			CHAR(15),
        ModelYearID 		CHAR(15),
        RoadTypeID			CHAR(15),
        SCC					CHAR(15),
        ActivityTypeID		CHAR(15),
        Activity			CHAR(30),
        ActivityMean		CHAR(30),
        ActivitySigma		CHAR(30),
        IterationID			CHAR(15)
);

INSERT INTO Temp VALUES ('MOVESRunID', 'YearID', 'MonthID',
		'DayID', 'HourID', 'StateID', 'CountyID', 'ZoneID', 'LinkID',
		'SourceTypeID', 'FuelTypeID','ModelYearID', 'RoadTypeID',
		'SCC', 'ActivityTypeID', 'Activity', 'ActivityMean', 'ActivitySigma', 'IterationID');
  
INSERT INTO Temp (MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID,
		SourceTypeID, FuelTypeID, ModelYearID, RoadTypeID,
		SCC, ActivityTypeID, Activity, ActivityMean, ActivitySigma, IterationID)
	SELECT MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID,
		SourceTypeID, FuelTypeID,ModelYearID, RoadTypeID,
		SCC, ActivityTypeID, Activity, ActivityMean, ActivitySigma, IterationID
	FROM movesactivityoutput;  



SELECT MOVESRunID, YearID, MonthID,
		DayID, HourID, StateID, CountyID, ZoneID, LinkID,
		SourceTypeID, FuelTypeID,ModelYearID, RoadTypeID,
		SCC, ActivityTypeID, Activity, ActivityMean, ActivitySigma, IterationID
	INTO OUTFILE 'MovesActivityOutput.txt'
		FIELDS TERMINATED BY '\t' 
		LINES TERMINATED BY '\r\n' 
		FROM Temp;       





-- This section writes the Moves Run to the text file MovesRun.txt

DROP TABLE IF EXISTS Temp;
CREATE TABLE Temp  (
		MOVESRunID				CHAR(20),       
		outputTimePeriod 		CHAR(20),
		timeUnits        		CHAR(20),
		distanceUnits			CHAR(20),
		massUnits				CHAR(20),
		energyUnits				CHAR(20),
		runSpecFileName			CHAR(20),
		runSpecDescription      VARCHAR(255),
		runSpecFileDateTime     CHAR(20),
		runDateTime				CHAR(20),
		scale					CHAR(20)
);

INSERT INTO Temp VALUES ('MOVESRunID', 'outputTimePeriod', 'timeUnits', 'distanceUnits',
		'massUnits', 'energyUnits', 'runSpecFileName', 'runSpecDescription', 
		'runSpecFileDateTime', 'runDateTime', 'scale' );
  
INSERT INTO Temp (MOVESRunID, outputTimePeriod, timeUnits, distanceUnits,
		massUnits, energyUnits, runSpecFileName, runSpecDescription, 
		runSpecFileDateTime, runDateTime, scale )
	SELECT MOVESRunID, outputTimePeriod, timeUnits, distanceUnits,
		massUnits, energyUnits, runSpecFileName, 
		TRIM(LEFT(runSpecDescription,255)), runSpecFileDateTime, runDateTime, scale
	FROM movesrun;  


SELECT MOVESRunID, outputTimePeriod, timeUnits, distanceUnits,
		massUnits, energyUnits, runSpecFileName, 
		runSpecDescription, runSpecFileDateTime, runDateTime, scale
	INTO OUTFILE 'MovesRun.txt'
		FIELDS TERMINATED BY '\t' 
		LINES TERMINATED BY '\r\n' 
		FROM Temp;       
		
DROP TABLE IF EXISTS Temp;

FLUSH TABLES;