# LoadZmh.plx
# Importer.bat, created by runspec_generator,
#  runs the MOVES CDM importer using the XML created by 
#  the SM movesdriver script.  That takes too long, so I am running this
#  script to simply convert the zmh csv files into mysql databases.
#  The databases will be named _zmh.  
# We don't want to run this conversion for the entire case by itself,
#  because it will dump 16,198 databases into c:\mysql\data,
#  so they are run a batch a time, transferred to S drive, and then
#  deleted from the mysql data folder.
# I'm putting them into a single folder.
# They are broken into batch sections for ease of handling.

$project='2021';
# CASE
$case='';
$year=2021;
$mysqldata="\"C:\\ProgramData\\MariaDB\\MariaDB 10.4\\data\"";
$homedir="C:\\EPA_MOVES_5-19\\On-road\\2021\\SmokeMovesRunSpecGenerator_2021";
$repcdblist="C:\\EPA_MOVES_5-19\\On-road\\2021\\254RepCos_CONUS_MetProc_20220802.txt";
# MOVES driver output
$mvdroutput="$homedir\\RunSpecGenOutput_2021";
# MySQL infile has to be local:
#$mysql='mysql --user=moves --password=moves';
$mysql='mysql --port=3307 --user=root';
#`mysql_config_editor set --login-path=local --host=localhost --user=moves --password`;
$mvdroutputlocal_backslash="C:\\a\\${project}_runspecgenoutput_zmh";
$mvdroutputlocal_fwdslash="C:/a/${project}_runspecgenoutput_zmh";
# Where these DBs will go:
$zmhdbs="$homedir\\2021_zmh";
#
print "project = $project\n";
print "case = $case\n";
print "year = $year\n";
print "mysqldata = $mysqldata\n";
print "homedir = $homedir\n";
print "repcdblist = $repcdblist\n";
print "mysql = $mysql\n";
print "mvdroutput = $mvdroutput\n";
print "mvdroutputlocal_backslash = $mvdroutputlocal_backslash\n";
print "mvdroutputlocal_fwdslash = $mvdroutputlocal_fwdslash\n";
print "zmhdbs = $zmhdbs\n";
# CREATE SMMVCDBDIR DIRECTORY
`mkdir $zmhdbs`;
`mkdir $mvdroutputlocal_backslash`;

# COPY *_ZMH.CSV TO LOCAL DRIVE--COMMENT OUT AFTER COPY COMPLETE
#`xcopy $mvdroutput\\*_zmh.csv $mvdroutputlocal_backslash /I/S/Y/D/Q`;
# Get list of zmh files
#`dir /b $mvdroutputlocal_backslash\\*.csv > $project${case}_zmh_list.txt`;
# MONTH

#die;

foreach $month(
	'1',
	'7',
){
print "month = $month\n";

# COUNTY
open(cdb1,"$repcdblist");
$cntycnt=0;
foreach $xxx(<cdb1>)
{
$cntycnt=$cntycnt+1;
#if($cntycnt>2){last;} # diagnostic limit
if(substr($xxx,0,1) eq '#'){next;}
($cnty,$cdbvers)=split(/ /,$xxx);
chomp($cnty);
chomp($cdbvers);
print "cnty = $cnty\n";
print "cdbvers = $cdbvers\n";
print "cnty = $cnty\n";
$batch="$project${case}_${cnty}_$month";
$batchpath="$zmhdbs\\$batch";
print "cnty = $cnty\n";
print "batch = $batch\n";
print "batchpath = $batchpath\n";
`mkdir $batchpath`;
# CONVERT _ZMH.CSV FILES TO MYSQL DATABASES AND TRANSFER BACK TO S.
$count=0;
print "mvdroutput = $mvdroutput\n";
foreach $zmhcsv (`dir /b $mvdroutput\\*_${cnty}_${year}_${month}_*_zmh.csv`)
{
	print "zmhcsv = $zmhcsv\n";
	$count=$count+1;
#	if($count>2){last;} # diagnostic limit
	chomp($zmhcsv);
	# Peel off the trailing _imp.xml:
	$job=substr($zmhcsv,0,-8);
	# Convert $job to lower case
	$job=lc($job);
	$zmhdb="${job}_zmh";
	# Substitute tn for t- in database name
	$_=$zmhdb;
	s/t-/tn/;
	$zmhdb=$_;
	$infile="$mvdroutputlocal_fwdslash/$zmhcsv";
	$destination="$batchpath\\$zmhdb";
	print "job = $job\n";
	print "zmhdb = $zmhdb\n";
	print "infile = $infile\n";
	print "destination = $destination\n";
	$sql="
	create database if not exists $zmhdb;
	use $zmhdb;
	drop table if exists zonemonthhour;
	CREATE TABLE zonemonthhour (
	monthID smallint(6) NOT NULL DEFAULT '0',
	zoneID int(11) NOT NULL DEFAULT '0',
	hourID smallint(6) NOT NULL DEFAULT '0',
	temperature double DEFAULT NULL,
	relHumidity double DEFAULT NULL,
	heatIndex double DEFAULT NULL,
	specificHumidity double DEFAULT NULL,
	molWaterFraction double DEFAULT NULL
	)
	;
	load data infile '$infile'
	into table zonemonthhour
	fields terminated by ','
	lines terminated by '\\r\\n'
	ignore 1 lines
	(monthID,zoneID,hourID,temperature,relHumidity)
	;
	alter table zonemonthhour
	add PRIMARY KEY (hourID,monthID,zoneID),
	add KEY monthID (monthID),
	add KEY zoneID (zoneID),
	add KEY hourID (hourID),
	add KEY monthID_2 (monthID,zoneID,hourID)
	;
	";
	open(out1,">script.sql");
	print out1 $sql;
	close(out1);
	#`mysql --login-path=local < script.sql`;
	`mysql --port=3307 --user=root < script.sql`;
	`mkdir $destination`;
	`xcopy $mysqldata\\$zmhdb\\*.* $destination /I/S/Y`;
	#`xcopy $mysqldata\\$zmhdb $batchpath\\$zmhdb /I/S/Y`;
	#print "^^xcopy\n";
	#`mysql --login-path=local -e \"flush tables\";`;
	`mysql --port=3307 --user=root -e \"flush tables\";`;
	#print "^^flush tables\n";
	#`rmdir /S/Q $mysqldata\\$zmhdb`;
	#print "^^rmdir\n";
} # ZMH FILE
} # MONTH
} # COUNTY