#!/bin/csh -fx
# Create day specific emissions for nonCEM sources as sets of day
# specific files using a single base-year annual input SMOKE-formatted (IDA)
# dataset.
#
# You must have write permissions for the directory that you use this program in.
#  James Beidler <beidler.james@epa.gov> Revised: 6/8/06

# Updated on 12/8/2007 by M. Houyoux to be run from the EMF.

# The following environment variables must be set prior to running this script
#
#   Variable            Description                 Example
#   --------            -----------                 -------
#   EMF_CLIENT          EMF command line client    "false" or "<insert here>
#   DAILY_CASE          Case Name for data          2002ce
#   DAT_ROOT            SMOKE data directory        /orchid/oaqps/2002/em_v4
#   EMISINV_A           ptipm annual inventory CAPs /orchid/oaqps/2002/em_v4/inputs/2002ae/ptipm/ptinv_ptipm_cap2002v2_02apr2007_v4_orl.txt
#   EMISINV_B           ptipm annual inventory HAPs /orchid/oaqps/2002/em_v4/inputs/2002ae/ptipm/ptinv_ptipm_hap2002nei_05apr2007_v0_orl.txt
#   COSTCY              Country, state, county file /orchid/oaqps/2002/em_v4/inputs/ge_dat/costcy_for_2002_fix_30jul2007_v0.txt
#   STATE_HEAT_MONTH    monthly heat input profiles emis_2002/heat_month_2001-2003.csv
#   STATE_NOX_MONTH     monthly NOx input profiles  emis_2002/nox_month_2001-2003.csv
#   STATE_SO2_MONTH     monthly SO2 input profiles  emis_2002/so2_month_2001-2003.csv
#   STATE_HEAT_DAILY    daily heat input profiles   emis_2002/heat_date_2002.csv
#   STATE_NOX_DAILY     daily NOx input profiles    emis_2002/nox_date_2002.csv
#   STATE_SO2_DAILY     daily SO2 input profiles    emis_2002/so2_date_2002.csv

# Define mysql configuration
set mysql_user = dayspecific 
set mysql_pass = non_cem_daily 
set mysql_db_name = non_cem_daily

# Set CAS numbers to extra from HAP inventory
set toxcas = ( 7647010 )

#############################################################################################
### End of user configuration

# Initializations and constants
set cemscan_head    = 1   # How many lines of header to ignore in cemscan file
set exitstat = 0

## log w/ EMF server that script is running
$EMF_CLIENT -k $EMF_JOBKEY -s "Running" 

#################
# Check that required environment variables are defined before they are used
foreach var (EMF_CLIENT DAILY_CASE DAT_ROOT EMISINV_A EMISINV_B COSTCY STATE_HEAT_MONTH \
             STATE_NOX_MONTH STATE_SO2_MONTH STATE_HEAT_DAILY STATE_NOX_DAILY STATE_SO2_DAILY)

   set isset = 0

   set line = `env | grep $var`
   if ( $status == 1 ) set isset = 1

   ### Now check to make sure it's actually set to something by counting characters
   else
      set check = `echo $line | cut -d\= -f1`
      if ( $check == "" ) set isset = 1
   endif
 
   ### echo and send error message to EMF client
   if ( $isset == 1 ) then
      echo "SCRIPT ERROR: Environment variable $var is not set in script"
      $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Environment variable $var is not set in script" -t "e"
      set exitstat = 1
   endif
end

if ( $exitstat > 0 ) then
   echo "SCRIPT ERROR: Script is ending due to previously listed errors"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Script is ending due to previously listed errors" -t "e" 
   exit ( $exitstat )
endif


#################
# Define output directory -- must be writable by user mysql
set ida_out = $DAT_ROOT/inputs/$DAILY_CASE/$SECTOR/ptday
if ( ! -e $ida_out ) then
   mkdir -p $ida_out
   if ( $status == 1 ) then
      echo 'SCRIPT ERROR: Could not create directory "'$ida_out'"'
      $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Could not create directory $ida_out" -t "e" 
      exit ( 1 )
   endif

   chmod ug+rwx $ida_out

endif

#################
# Record directory permissions
echo "SCRIPT NOTE: The permissions on the output file directory are:"
/bin/ls -l -d $ida_out

#################
# The path to a temporary directory writable by user mysql
set sql_tmp = $DAT_ROOT/tmp/create_daily

#################
# Define tmp file names and delete them if they are already there
set orl_annual = $sql_tmp/ptinv_ptipm_cap+hcl_tmp_orl_$$.txt   # $$ includes process number in name
if ( -e $orl_annual ) /bin/rm -rf $orl_annual

set county_work = $sql_tmp/Counties-workfile_$$.txt
set county_fips = $sql_tmp/Counties-with_time_zones_$$.txt
set cemscan_tmp = $sql_tmp/cemscan_$$.tmp 

/bin/rm -rf $orl_annual $county_work $county_fips $cemscan_tmp

#################
# Get input setting and files from EMF variables
set file_label       = $DAILY_CASE
set sector           = $SECTOR
set state_HEAT_month = $STATE_HEAT_MONTH # emis_2002/heat_month_2001-2003.csv
set state_NOX_month  = $STATE_NOX_MONTH  # emis_2002/nox_month_2001-2003.csv
set state_SO2_month  = $STATE_SO2_MONTH  # emis_2002/so2_month_2001-2003.csv
set state_HEAT_daily = $STATE_HEAT_DAILY # emis_2002/heat_date_2002.csv
set state_NOX_daily  = $STATE_NOX_DAILY  # emis_2002/nox_date_2002.csv
set state_SO2_daily  = $STATE_SO2_DAILY  # emis_2002/so2_date_2002.csv
set cemscan_out      = $IMD_ROOT/$CASE/$SECTOR/cemsum_${SECTOR}_$CASE.txt   # Same as CEMSUM in the ASSIGNS.emf file


$EMF_CLIENT -k $EMF_JOBKEY -m "Creating county-timezone list from COSTCY" -t "i"   ## log w/ EMF server
#################
# Create counties list from the COSTCY file and put in the sql_tmp directory
set cl = `grep -n \/COUNTY\/ $COSTCY | cut -d: -f1`  # Get first line
set el = `grep -n 99999 $COSTCY | cut -d: -f1`       # Get last US line
set nl = `cat $COSTCY | wc -l`                       # Count total lines in file

set tailno = $nl
@ tailno = $tailno - $cl

set headno = $el
@ headno = $headno - $cl

tail -$tailno $COSTCY | head -$headno | cut -c27-28,29-31,1,40-42 > $county_work
sed 's/ /0/g;s/C/,C/;s/E/,E/;s/H/,H/;s/M/,M/;s/P/,P/;s/Y/,Y/' $county_work > $county_fips 
/bin/rm -rf $county_work


$EMF_CLIENT -k $EMF_JOBKEY -m "Concatenating CAP and selected HAP annual ORL data" -t "i"   ## log w/ EMF server
#################
# Create intermediate inventory file from the CAP file and the HAP file to get toxics specified by toxcas.
#   While doing this, remove the header lines, so that won't have to ignore any lines.
grep -v "^#" $EMISINV_A > $orl_annual

# Loop through toxics CAS numbers to put into intermediate file
foreach cas ( $toxcas )
   grep \"$cas\" $EMISINV_B >> $orl_annual
   if ( $status == 1 ) set exitstat = 1
end

if ( $exitstat > 0 ) then
   echo "SCRIPT ERROR: Could not find one or more specified toxic pollutants in HAP inventory"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Could not find one or more specified toxic pollutants in HAP inventory" -t "e" 
   exit ( $exitstat ) 
endif


$EMF_CLIENT -k $EMF_JOBKEY -m "Converting $cemscan_out to CSV format" -t "i"   ## log w/ EMF server
#################
# Convert cemscan to CSV 
awk '{printf substr($0,1,6)",";printf substr($0,7,7)",";printf substr($0,14,18)",";printf substr($0,32,18)","; \
printf substr($0,50,18)",";printf substr($0,68,18)",";printf substr($0,86,18)",";printf substr($0,104,18)"\n"}' ${cemscan_out}  > $cemscan_tmp

if ( $status != 0 ) then
   echo "SCRIPT ERROR: conversion of CEMSUM file to CSV format failed"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: conversion of CEMSUM file to CSV format failed" -t "e" 
   exit ( 1 ) 
endif

echo "Creating MySQL tables..."
$EMF_CLIENT -k $EMF_JOBKEY -m "Creating MySQL tables" -t "i"   ## log w/ EMF server
#################
# Create MySQL tables

/usr/bin/mysql -u ${mysql_user} -p${mysql_pass} --local-infile -t <<STOP
-- Create mysql database 
DROP DATABASE IF EXISTS ${mysql_db_name} ;
CREATE DATABASE ${mysql_db_name} ;
-- Create the tables
USE ${mysql_db_name} ;
CREATE TABLE non_cem_tmp (region varchar(5), plantid varchar(16), pointid varchar(16), stackid varchar(13), segment varchar(10), plant varchar(40), scc varchar(11), sic int, cas varchar(8), ann_emis real(12,6), orisid varchar(7), blrid varchar(7));
CREATE TABLE state_HEAT_month (fips varchar(2), state varchar(30), month int, frac real(12,6), index(month, fips)) ;
CREATE TABLE state_NOX_month (fips varchar(2), state varchar(30), month int, frac real(12,6), index(month, fips)) ;
CREATE TABLE state_SO2_month (fips varchar(2), state varchar(30), month int, frac real(12,6), index(month, fips)) ;
CREATE TABLE county_fips (fips varchar(5), tz varchar(8)) ;
CREATE TABLE cemscan (oris varchar(7), boiler varchar(7), NOX float, SO2 float, optime float, gload float, sload float, htinput float) ;
CREATE TABLE state_HEAT_daily (fips varchar(2), state varchar(10), dateandtime varchar(20), emis float, index(emis(4)), month int, index(month, fips)) ;
CREATE TABLE state_NOX_daily (fips varchar(2), state varchar(10), dateandtime varchar(20), emis float, index(emis(4)), month int, index(month, fips)) ;
CREATE TABLE state_SO2_daily (fips varchar(2), state varchar(10), dateandtime varchar(20), emis float, index(emis(4)), month int, index(month, fips)) ;
-- Import the tables
LOAD DATA LOCAL INFILE '${orl_annual}' INTO TABLE non_cem_tmp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (region, plantid, pointid, stackid, segment, plant, scc, @dummy8, @dummy9, @dummy10, @dummy11, @dummy12, @dummy13, @dummy14, sic, @dummy16, @dummy17, @dummy18, @dummy19, @dummy20, @dummy21, cas, ann_emis, @dummy24, @dummy25, @dummy26, @dummy27, @dummy28, @dummy29, orisid, blrid, @dummy32, @dummy33, @dummy34, @dummy35, @dummy36, @dummy37, @dummy38, @dummy39) ;
LOAD DATA LOCAL INFILE '${state_HEAT_month}' INTO TABLE state_HEAT_month FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
LOAD DATA LOCAL INFILE '${state_NOX_month}' INTO TABLE state_NOX_month FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
LOAD DATA LOCAL INFILE '${state_SO2_month}' INTO TABLE state_SO2_month FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
LOAD DATA LOCAL INFILE '${county_fips}' INTO TABLE county_fips FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
LOAD DATA LOCAL INFILE '${cemscan_tmp}' INTO TABLE cemscan FIELDS TERMINATED BY ',' IGNORE ${cemscan_head} LINES ; 
LOAD DATA LOCAL INFILE '${state_HEAT_daily}' INTO TABLE state_HEAT_daily FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
LOAD DATA LOCAL INFILE '${state_NOX_daily}' INTO TABLE state_NOX_daily FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
LOAD DATA LOCAL INFILE '${state_SO2_daily}' INTO TABLE state_SO2_daily FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
-- Create tables for individual pollutants and merge - do this because of the new ORL format
-- Commented out by M Houyoux and added to next mysql step, to take advantage of pollutant loop used there.
-- CREATE TABLE CO_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis CO FROM non_cem_tmp WHERE cas = "CO" ;
-- CREATE TABLE VOC_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis VOC FROM non_cem_tmp WHERE cas = "VOC" ;
-- CREATE TABLE NOX_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis NOX FROM non_cem_tmp WHERE cas = "NOX" ;
-- CREATE TABLE NH3_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis NH3 FROM non_cem_tmp WHERE cas = "NH3" ;
-- CREATE TABLE PM10_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis PM10 FROM non_cem_tmp WHERE cas = "PM10" ;
-- CREATE TABLE PM2_5_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis PM2_5 FROM non_cem_tmp WHERE cas = "PM2_5" ;
-- CREATE TABLE SO2_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis SO2 FROM non_cem_tmp WHERE cas = "SO2" ; 
\q
STOP
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful creation of MySQL tables"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful creation of MySQL tables" -t "e" 
   exit ( 1 ) 
endif

echo "Running Step 2: match annual records to CEM records..."
$EMF_CLIENT -k $EMF_JOBKEY -m "Running Step 2: match annual records to CEM records" -t "i"   ## log w/ EMF server
#################
# Match records from annual file to CEM data (orl_annual and cemscan_out)
#   This is determined by whether or not the ORIS/Boiler ID fields in orl_annual 
#   match the same fields in cemscan_out.
foreach pol (CO NOX VOC SO2 NH3 PM2_5 PM10 $toxcas) 

/usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -t <<STOP
USE ${mysql_db_name} ;
-- Create tables for individual pollutants and merge - do this because of the new ORL format
CREATE TABLE ${pol}_tmp SELECT region, plantid, pointid, stackid, segment, plant, scc, sic, orisid, blrid, ann_emis "$pol" FROM non_cem_tmp WHERE cas = "$pol" ;
-- Step 2: Perform mysql calculations and functions
CREATE TABLE match_${pol} SELECT ${pol}_tmp.* FROM ${pol}_tmp, cemscan WHERE trim(${pol}_tmp.orisid) = trim(cemscan.oris) AND trim(${pol}_tmp.blrid) = trim(cemscan.boiler) ; 
CREATE TABLE no_match_${pol} SELECT ${pol}_tmp.* FROM ${pol}_tmp LEFT OUTER JOIN match_${pol} ON ${pol}_tmp.blrid = match_${pol}.blrid AND ${pol}_tmp.orisid = match_${pol}.orisid WHERE match_${pol}.blrid IS NULL ;
\q
STOP

   if ( $status != 0 ) then
      echo "SCRIPT ERROR: Unable to match annual records to CEM records for pollutant $pol"
      $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unable to match annual records to CEM records for pollutant $pol" -t "e" 
      exit ( 1 ) 
   endif

end

echo "Running Step 3: Create monthly emissions..."
$EMF_CLIENT -k $EMF_JOBKEY -m "Running Step 3: Create monthly emissions..." -t "i"   ## log w/ EMF server
#################
# Step 3: Compute monthly emissions for all pollutants from sources matching the CEM data 
#
/usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -t <<STOP
USE ${mysql_db_name} ;
-- Create monthly emissions
CREATE TABLE NOX_month SELECT state_NOX_month.month, state_NOX_month.fips, no_match_NOX.region, no_match_NOX.plantid, no_match_NOX.pointid, no_match_NOX.stackid, no_match_NOX.segment, no_match_NOX.scc, no_match_NOX.sic, (no_match_NOX.NOX * state_NOX_month.frac) NOX FROM no_match_NOX, state_NOX_month WHERE (no_match_NOX.region LIKE CONCAT(IF(CHAR_LENGTH(state_NOX_month.fips) = 1, '0', ''), state_NOX_month.fips, '___')) ORDER BY state_NOX_month.month ;
CREATE TABLE SO2_month SELECT state_SO2_month.month, state_SO2_month.fips, no_match_SO2.region, no_match_SO2.plantid, no_match_SO2.pointid, no_match_SO2.stackid, no_match_SO2.segment, no_match_SO2.scc, no_match_SO2.sic, (no_match_SO2.SO2 * state_SO2_month.frac) SO2 FROM no_match_SO2, state_SO2_month WHERE (no_match_SO2.region LIKE CONCAT(IF(CHAR_LENGTH(state_SO2_month.fips) = 1, '0', ''), state_SO2_month.fips, '___')) ORDER BY state_SO2_month.month ;
ALTER TABLE NOX_month ADD INDEX(fips, month) ; 
ALTER TABLE SO2_month ADD INDEX(fips, month) ;
-- Create state-month totals
CREATE TABLE NOX_month_daily SELECT fips, state, sum(emis) NOX, month FROM state_NOX_daily GROUP BY fips, month;
CREATE TABLE SO2_month_daily SELECT fips, state, sum(emis) SO2, month FROM state_SO2_daily GROUP BY fips, month;
CREATE TABLE HEAT_month_daily SELECT fips, state, sum(emis) HEAT, month FROM state_HEAT_daily GROUP BY fips, month;
ALTER TABLE NOX_month_daily ADD INDEX(fips, month) ;
ALTER TABLE SO2_month_daily ADD INDEX(fips, month) ;
ALTER TABLE HEAT_month_daily ADD INDEX(fips, month) ;
-- Create days in month table
CREATE TABLE daysinmonth SELECT month, count(dateandtime) days FROM state_NOX_daily WHERE fips = 1 GROUP BY month;
\q
STOP
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful creation of monthly emissions tables"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful creation of monthly emissions tables" -t "e" 
   exit ( 1 ) 
endif

# Loop for HEAT data only
foreach pol (CO VOC NH3 PM10 PM2_5 $toxcas)
/usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -t <<STOP
USE ${mysql_db_name} ;
--- Continue step 3 monthly emissions with HEAT data
CREATE TABLE ${pol}_month SELECT state_HEAT_month.month, state_HEAT_month.fips, no_match_${pol}.region, no_match_${pol}.plantid, no_match_${pol}.pointid, no_match_${pol}.stackid, no_match_${pol}.segment, no_match_${pol}.scc, no_match_${pol}.sic, (no_match_${pol}.${pol} * state_HEAT_month.frac) "${pol}" FROM no_match_${pol}, state_HEAT_month WHERE (no_match_${pol}.region LIKE CONCAT(IF(CHAR_LENGTH(state_HEAT_month.fips) = 1, '0', ''), state_HEAT_month.fips, '___')) ORDER BY state_HEAT_month.month ;
ALTER TABLE ${pol}_month ADD INDEX(fips, month) ;
\q
STOP
   if ( $status != 0 ) then
      echo "SCRIPT ERROR: Unsuccessful creation of monthly emissions tables for $pol"
      $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful creation of monthly emissions tables for $pol" -t "e" 
      exit ( 1 ) 
   endif

end



echo "Running Step 4: Create daily emissions..."
$EMF_CLIENT -k $EMF_JOBKEY -m "Running Step 4: Create daily emissions..." -t "i"   ## log w/ EMF server

# Loop for HEAT related pols
foreach HEAT_emis (CO VOC NH3 PM2_5 $toxcas)
if (-e ${sql_tmp}/${file_label}_${HEAT_emis}_noncem.csv) then
        rm -f ${sql_tmp}/${file_label}_${HEAT_emis}_noncem.csv
endif
#Compute day-specific emissions by facility
echo "SELECT ${HEAT_emis}_month.region, ${HEAT_emis}_month.plantid, ${HEAT_emis}_month.pointid, ${HEAT_emis}_month.stackid, ${HEAT_emis}_month.segment, '${HEAT_emis}', state_HEAT_daily.dateandtime, county_fips.tz,\
TRUNCATE(IF(HEAT_month_daily.HEAT >= 0, ${HEAT_emis}_month.${HEAT_emis} * state_HEAT_daily.emis / HEAT_month_daily.HEAT, ${HEAT_emis}_month.${HEAT_emis} / daysinmonth.days), 9) '${HEAT_emis}', ${HEAT_emis}_month.scc\
FROM ${HEAT_emis}_month, HEAT_month_daily, daysinmonth, state_HEAT_daily, county_fips\
WHERE (((IF(HEAT_month_daily.HEAT >= 0, ${HEAT_emis}_month.${HEAT_emis} * state_HEAT_daily.emis / HEAT_month_daily.HEAT, ${HEAT_emis}_month.${HEAT_emis} / daysinmonth.days)) >= 0)) AND ${HEAT_emis}_month.month = HEAT_month_daily.month AND ${HEAT_emis}_month.fips = HEAT_month_daily.fips AND HEAT_month_daily.month = daysinmonth.month AND ${HEAT_emis}_month.month = state_HEAT_daily.month AND ${HEAT_emis}_month.fips = state_HEAT_daily.fips AND county_fips.fips = ${HEAT_emis}_month.region ;" | /usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -D ${mysql_db_name} | sed 's/\t/,/g' > ${sql_tmp}/${file_label}_${HEAT_emis}_noncem.csv

   if ( $status != 0 ) then
      echo "SCRIPT ERROR: Unsuccessful computing of day-specific emissions for $pol"
      $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful computing of day-specific emissions for $pol" -t "e" 
      exit ( 1 ) 
   endif

end

# Run for NOX
if (-e ${sql_tmp}/${file_label}_NOX_noncem.csv) then
        rm -f ${sql_tmp}/${file_label}_NOX_noncem.csv
endif
#Compute day-specific emissions by facility
echo "SELECT NOX_month.region, NOX_month.plantid, NOX_month.pointid, NOX_month.stackid, NOX_month.segment, 'NOX', state_NOX_daily.dateandtime, county_fips.tz,\
TRUNCATE(IF(NOX_month_daily.NOX >= 0, NOX_month.NOX * state_NOX_daily.emis / NOX_month_daily.NOX, NOX_month.NOX / daysinmonth.days), 9) NOX, NOX_month.scc\
FROM NOX_month, NOX_month_daily, daysinmonth, state_NOX_daily, county_fips\
WHERE (((IF(NOX_month_daily.NOX >= 0, NOX_month.NOX * state_NOX_daily.emis / NOX_month_daily.NOX, NOX_month.NOX / daysinmonth.days)) >= 0)) AND NOX_month.month = NOX_month_daily.month AND NOX_month.month = daysinmonth.month AND NOX_month.month = state_NOX_daily.month AND NOX_month.fips = NOX_month_daily.fips AND NOX_month.fips = state_NOX_daily.fips AND county_fips.fips = NOX_month.region ;" | /usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -D ${mysql_db_name} | sed 's/\t/,/g' > ${sql_tmp}/${file_label}_NOX_noncem.csv
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful computing of day-specific emissions for NOX"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful computing of day-specific emissions for NOX" -t "e" 
   exit ( 1 ) 
endif

# Run for SO2
if (-e ${sql_tmp}/${file_label}_SO2_noncem.csv) then
        rm -f ${sql_tmp}/${file_label}_SO2_noncem.csv
endif
#Compute day-specific emissions by facility
echo "SELECT SO2_month.region, SO2_month.plantid, SO2_month.pointid, SO2_month.stackid, SO2_month.segment, 'SO2', state_SO2_daily.dateandtime, county_fips.tz,\
TRUNCATE(IF(SO2_month_daily.SO2 >= 0, SO2_month.SO2 * state_SO2_daily.emis / SO2_month_daily.SO2, SO2_month.SO2 / daysinmonth.days), 9) SO2, SO2_month.scc\
FROM SO2_month, SO2_month_daily, daysinmonth, state_SO2_daily, county_fips\
WHERE (((IF(SO2_month_daily.SO2 >= 0, SO2_month.SO2 * state_SO2_daily.emis / SO2_month_daily.SO2, SO2_month.SO2 / daysinmonth.days)) >= 0)) AND SO2_month.month = SO2_month_daily.month AND SO2_month.fips = SO2_month_daily.fips AND SO2_month_daily.month = daysinmonth.month AND SO2_month.month = state_SO2_daily.month AND SO2_month.fips = state_SO2_daily.fips AND county_fips.fips = SO2_month.region ;" | /usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -D ${mysql_db_name} | sed 's/\t/,/g' > ${sql_tmp}/${file_label}_SO2_noncem.csv
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful computing of day-specific emissions for SO2"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful computing of day-specific emissions for SO2" -t "e" 
   exit ( 1 ) 
endif

# Run for PMC
if (-e ${sql_tmp}/${file_label}_PMC_noncem.csv) then
        rm -f ${sql_tmp}/${file_label}_PMC_noncem.csv
endif
#Compute day-specific emissions by facility
echo "CREATE TABLE PMC_month SELECT PM10_month.*, PM2_5_month.PM2_5 FROM PM10_month\
LEFT JOIN PM2_5_month USING (month, fips, region, plantid, pointid, stackid, segment, scc, sic) ;\
SELECT PMC_month.region, PMC_month.plantid, PMC_month.pointid, PMC_month.stackid, PMC_month.segment, 'PMC', state_HEAT_daily.dateandtime, county_fips.tz,\
TRUNCATE(IF(HEAT_month_daily.HEAT >= 0, (PMC_month.PM10 - PMC_month.PM2_5) * state_HEAT_daily.emis / HEAT_month_daily.HEAT, (PMC_month.PM10 - PMC_month.PM2_5) / daysinmonth.days), 9) PMC, PMC_month.scc\
FROM PMC_month, HEAT_month_daily, daysinmonth, state_HEAT_daily, county_fips\
WHERE (((IF(HEAT_month_daily.HEAT >= 0, (PMC_month.PM10 - PMC_month.PM2_5) * state_HEAT_daily.emis / HEAT_month_daily.HEAT, (PMC_month.PM10 - PMC_month.PM2_5) / daysinmonth.days)) >= 0)) AND PMC_month.month = HEAT_month_daily.month AND PMC_month.fips = HEAT_month_daily.fips AND HEAT_month_daily.month = daysinmonth.month AND PMC_month.month = state_HEAT_daily.month AND PMC_month.fips = state_HEAT_daily.fips AND county_fips.fips = PMC_month.region ;" | /usr/bin/mysql -u ${mysql_user} -p${mysql_pass} -D ${mysql_db_name} | sed 's/\t/,/g' > ${sql_tmp}/${file_label}_PMC_noncem.csv
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful computing of day-specific emissions for PMC"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful computing of day-specific emissions for PMC" -t "e" 
   exit ( 1 ) 
endif

echo "Formatting pollutant files..."
$EMF_CLIENT -k $EMF_JOBKEY -m "Formatting pollutant files..." -t "i"   ## log w/ EMF server

# Concat pollutant files
foreach month (jan feb mar apr may jun jul aug sep oct nov dec)
   if (-e ${ida_out}/${file_label}_${month}_noncem.tmp) then
      rm -f ${ida_out}/${file_label}_${month}_noncem.tmp
   endif

   foreach pol (CO VOC NOX NH3 PMC PM2_5 SO2 $toxcas)
      grep ${month} ${sql_tmp}/${file_label}_${pol}_noncem.csv >> ${ida_out}/${file_label}_${month}_noncem.tmp
      if ( $status != 0 ) then
         echo "SCRIPT ERROR: Could not find $month in temporary file for $pol"
         $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Could not find $month in temporary file for $pol" -t "e" 
         exit ( 1 ) 
      endif
   end

# Format pollutant files and output
awk 'BEGIN {FS = ","}; \
{printf "%-5s", $1}; {printf "%-15s", $2}; {printf "%-12s", $3}; \
{printf "%-12s", $4}; {printf "%-12s", $5}; {printf "%-5s", $6}; \
{ if (substr($7,3,3)=="jan") { month = "01"; } \
if (substr($7,3,3)=="feb") { month = "02"; } \
if (substr($7,3,3)=="mar") { month = "03"; } \
if (substr($7,3,3)=="apr") { month = "04"; } \
if (substr($7,3,3)=="may") { month = "05"; } \
if (substr($7,3,3)=="jun") { month = "06"; } \
if (substr($7,3,3)=="jul") { month = "07"; } \
if (substr($7,3,3)=="aug") { month = "08"; } \
if (substr($7,3,3)=="sep") { month = "09"; } \
if (substr($7,3,3)=="oct") { month = "10"; } \
if (substr($7,3,3)=="nov") { month = "11"; } \
if (substr($7,3,3)=="dec") { month = "12"; } \
printf "%s/%s/%s", month, substr($7,1,2), substr($7,8,2)}; \
{printf "%-3s", $8}; {printf "%-18s ", $9}; {printf "%-10s\n", $10}' ${ida_out}/${file_label}_${month}_noncem.tmp > ${ida_out}/ptday_${sector}_${month}_${file_label}_noncem.ida
/bin/rm -f ${ida_out}/${file_label}_${month}_noncem.tmp
if ( $status != 0 ) then
   echo "SCRIPT ERROR: Unsuccessful formatting output data for $month"
   $EMF_CLIENT -k $EMF_JOBKEY -m "ERROR: Unsuccessful formatting output data for $month" -t "e" 
   exit ( 1 ) 
endif


end

#########
# Delete temp files
/bin/rm -rf $orl_annual $county_fips $cemscan_tmp

foreach pol (CO VOC NOX NH3 PMC PM2_5 SO2 $toxcas)

    /bin/rm -rf ${sql_tmp}/${file_label}_${pol}_noncem.csv

end
