# ------------------------------------------------------------------------------
# -------- Create FF10 Script --------------------------------------------------
# ------------------------------------------------------------------------------
# ------------------------------------------------------------------------------
# -------- This script converts MOVES2014 formatted CDBs to create 5 SMOKE
# -------- ready tables. Do *NOT* apply this script to CDBs still in older
# -------- MOVES formats!!!
# ------------------------------------------------------------------------------
# -------- Script places all results in the external database draft_epa2017ff10_7jan2020
# -------- which is created using a separate script.
# ------------------------------------------------------------------------------
# -------- Script assumes access to the default MOVES database: movesdb20181022
# -------- which has the definition of the new HPMS vehicle type (25).
# ------------------------------------------------------------------------------
# ------ WARNING!!!!! LINE 108 has a hard-coded fuelyearID !!!! -----ALERT!-----
# by Ken Reader
# April 2014
# David Brzezinski
# April 14, 2014
# David Brzezinski
# June 09, 2014
# David Brzezinski
# June 20, 2016
# David Brzezinski
# July 28, 2016 - Added user supplied hotelling hours.
# Doug Jackson (ERG)
# July 28, 2016 - Incorporated changes to prevent dropping of motorcycle and HD gasoline activity
# Allison DenBleyker (ERG)
# July 29, 2016
# Updated one reference to use the current MOVES database and made a change to prevent doubling of user supplied hotelling hours
# Allison DenBleyker (ERG)
# April 24, 2019 - Expanded VMT calculations to read the SourceTypeYearVMT table of the CDB

###################################################################
# ERG update 24apr19 - adb
# Workaround for 'illegal mix of collations' MySQL error produced by new CDB submittals for 2017
ALTER DATABASE CHARACTER SET utf8;
ALTER DATABASE COLLATE utf8_unicode_ci;

# Calculate populations.
drop   table if exists sourceAgePop;
create table           sourceAgePop
Select   (select b.HPMSVtypeId
          from   movesdb20181022.sourceUseType as b
          where  a.sourceTypeId = b.sourceTypeId ) as HPMSVtypeId,
#          from   movesdb20121030.hpmsvtype as b
#          where  left(a.sourceTypeId,1) = left(b.hpmsvtypeid,1) ) as HPMSVtypeId,
       sourceTypeId,
       yearId,
       ageId,
       (yearId - ageId) as modelYearId,
       ageFraction,

         (select b.sourceTypePopulation
          from sourceTypeYear as b
          where a.sourceTypeId=b.sourceTypeId )  as souPop

from   sourceTypeAgeDistribution as a
where  a.yearId = (select YearId from year);

Alter table sourceAgePop add column population real;
Update      sourceAgePop set        population =  souPop * ageFraction;  -- pop by sou, age


alter table sourceAgePop add column relMAR double;
update      sourceAgePop as a set a.relMAR = (Select b.relativeMAR
                                              from movesdb20181022.sourceTypeAge as b
                                              where a.sourceTypeId = b.sourceTypeId
                                                and a.ageId        = b.ageId          );

alter table sourceAgePop add column travel double;
update      sourceAgePop set        travel = population * relMar;

alter table sourceAgePop add column sigTravelHPMStype double;
update      sourceAgePop as a set a.sigTravelHPMStype = (select sum(b.travel)
                                                         from (select * from sourceAgePop) as b
                                                         where a.HPMSVtypeId = b.HPMSVtypeId   );
# ERG update, 24apr19, adb
ALTER TABLE sourceAgePop add column sigTravelSourceType double;
UPDATE	    sourceAgePop as a SET a.sigTravelSourceType = (SELECT sum(b.travel)
							   FROM (SELECT * FROM sourceAgePop) AS b
							   WHERE a.sourceTypeID = b.sourceTypeID);
alter table sourceAgePop add column travelFraction double;
update      sourceAgePop set        travelFraction = travel / sigTravelHPMStype;

# ERG update, 24apr19, adb
ALTER TABLE sourceAgePop ADD COLUMN travelFractionST double;
UPDATE      sourceAgePop SET        travelFractionST = travel / sigTravelSourceType;


# obtain the default fuel type fractions from the default database.
drop   table if exists disFuelTypes ;
create table           disFuelTypes
SELECT distinct fuelTypeId
FROM movesdb20181022.samplevehiclepopulation
order by fuelTypeId;

drop   table  if exists sourceAgePopFt;
create table            sourceAgePopFt
select a.*,
       b.*
from sourceAgePop as a
join disFuelTypes as b;

alter table sourceAgePopFt add  column stmyFraction double;  -- distribution by fuelTypeId
update      sourceAgePopFt as a set  a.stmyFraction = ( Select sum(b.stmyFraction)
                                                        From  movesdb20181022.sampleVehiclePopulation as b
                                                        Where a.sourceTypeId = b.sourceTypeId
                                                          and a.modelYearId  = b.modelYearId
                                                          and a.fuelTypeId   = b.fuelTypeId   );

# ERG update, 14jul16, dwj
UPDATE sourceAgePopFt SET stmyFraction = 0 WHERE stmyFraction IS NULL;
                                                                                                                    
# -------------------------------------------------------------
# Adjust the default E85 population data to reflect actual use of E85 fuel.

# get the default fuel usage fractions, if the user
# has not supplied them already.
insert IGNORE into fuelusagefraction
select a.* 
from movesdb20181022.fuelusagefraction as a,
     county as b
where a.countyid=b.countyid and fuelyearid=2017;

# create a table to make the adjustments.
drop table if exists stmyfraction;
create table stmyfraction 
select yearid,countyid,sourcetypeid,modelyearid,fueltypeid,stmyfraction
from sourceAgePopFt as a, county as b;

alter table stmyfraction
add index myidx (sourcetypeid,fueltypeid,modelyearid),
add e85fraction double,
add finalfraction double;

# have the E85 capable fraction available to each 
# fuel type row for fuel use adjustments.
update stmyfraction as a, sourceAgePopFt as b
set e85fraction=b.stmyfraction
where b.fueltypeid=5
and a.yearid=b.yearid
and a.sourcetypeid=b.sourcetypeid
and a.modelyearid=b.modelyearid;

# default final fraction to be the stmyfraction.
update stmyfraction set finalfraction=0.0;
update stmyfraction set finalfraction=stmyfraction;

# adjust the gasoline fraction to include E85 vehicles
# that are using gasoline (assumes no model year groupings).
update stmyfraction as a, fuelusagefraction as b
set finalfraction=stmyfraction+(e85fraction*usagefraction)
where a.fueltypeid=fuelsupplyfueltypeid
and a.fueltypeid=1
and b.sourcebinfueltypeid=5
and a.countyid=b.countyid
and yearid=fuelyearid;

# adjust the E85 fraction to exclude E85 vehicles
# that are using gasoline (assumes no model year groupings).
update stmyfraction as a, fuelusagefraction as b
set finalfraction=(e85fraction*usagefraction)
where a.fueltypeid=fuelsupplyfueltypeid
and a.fueltypeid=5
and b.sourcebinfueltypeid=5
and a.countyid=b.countyid
and yearid=fuelyearid;

# --------------------------------------------------------
# adjust the stmyfractions to reflect the user supplied
# fuel information in the AVFT submission.
drop table if exists stmyfraction2;
create table stmyfraction2 
select yearid,countyid,sourcetypeid,modelyearid,fueltypeid,fuelengfraction
from avft as a, county as b, year as c;

alter table stmyfraction2
add index myidx (sourcetypeid,fueltypeid,modelyearid),
add e85fraction double,
add finalfraction double;

# have the E85 capable fraction available to each 
# fuel type row for fuel use adjustments.
update stmyfraction2 as a, avft as b
set e85fraction=b.fuelengfraction
where b.fueltypeid=5
and a.sourcetypeid=b.sourcetypeid
and a.modelyearid=b.modelyearid;

# ERG update, 14jul16, dwj
UPDATE stmyfraction2 SET e85fraction = 0 WHERE e85fraction IS NULL;

# default final fraction to be the stmyfraction.
update stmyfraction2 set finalfraction=0.0;
update stmyfraction2 set finalfraction=fuelengfraction;

# adjust the gasoline fraction to include E85 vehicles
# that are using gasoline (assumes no model year groupings).
update stmyfraction2 as a, fuelusagefraction as b
set finalfraction=fuelengfraction+(e85fraction*usagefraction)
where a.fueltypeid=fuelsupplyfueltypeid
and a.fueltypeid=1
and b.sourcebinfueltypeid=5
and a.countyid=b.countyid
and yearid=fuelyearid;

# adjust the E85 fraction to exclude E85 vehicles
# that are using gasoline (assumes no model year groupings).
update stmyfraction2 as a, fuelusagefraction as b
set finalfraction=(e85fraction*usagefraction)
where a.fueltypeid=fuelsupplyfueltypeid
and a.fueltypeid=5
and b.sourcebinfueltypeid=5
and a.countyid=b.countyid
and yearid=fuelyearid;

# replace adjusted fuel fractions with the
# adjusted user supplied fuel fractions.
# you need to delete rows, since the new data
# may have a different number of rows.
delete from stmyfraction
using stmyfraction, stmyfraction2
where stmyfraction.yearid=stmyfraction2.yearid
and stmyfraction.countyid=stmyfraction2.countyid
and stmyfraction.sourcetypeid=stmyfraction2.sourcetypeid
and stmyfraction.modelyearid=stmyfraction2.modelyearid;
insert into stmyfraction select * from stmyfraction2;
alter table stmyfraction add index myidx2 (sourcetypeid,yearid,modelyearid,fueltypeid);

# copy the adjusted fuel fractions to the working table.
# there should be no missing combinations.
update sourceAgePopFt set stmyfraction=0.0;
update sourceAgePopFt as a, stmyfraction as b
set a.stmyfraction=b.finalfraction
where a.sourcetypeid=b.sourcetypeid
and a.yearid=b.yearid
and a.modelyearid=b.modelyearid
and a.fueltypeid=b.fueltypeid;

# delete temporary tables:
drop table if exists stmyfraction;
drop table if exists stmyfraction2;


alter table   sourceAgePopFt add column finalPopulation double;

update        sourceAgePopFt set        finalPopulation = population * stmyFraction;

drop   table if exists prePopFF10;
create table           prePopFF10
select fuelTypeId,
       sourceTypeId,
       ageId,
       finalPopulation
from   sourceAgePopFt;

alter table prePopFF10 add column region_cd char(5);
update      prePopFF10      set   region_cd = (select LPAD(countyid, 5, '0') from county);

# Generate the SCC field.
alter table prePopFF10 add column scc       char(10);                                          -- Both
update      prePopFF10 as a set a.scc      = concat('220',      cast(fuelTypeId   as char) );  -- Both
update      prePopFF10 as a set a.scc      = concat(a.scc,      cast(sourceTypeId as char) );  -- Both
update      prePopFF10 as a set a.scc      = concat(a.scc, '01'                            );  -- for POP
update      prePopFF10 as a set a.scc      = concat(a.scc, '00'                            );  -- Both

alter table prePopFF10 add column  date_updated char(8);
update      prePopFF10 as a set  a.date_updated = concat( substring(curDate(), 1, 4),             -- year
                                                                  substring(curDate(), 6, 2),     -- month
                                                                  substring(curDate(), 9, 2)  );  -- day

# Place final population values in the FF10 formatted table.
drop table if exists ff10POP;      -- Populations only.
CREATE TABLE         ff10POP
  (  country_cd         char(2),   -- US
     region_cd          char(5),   -- county Id
     tribal_code        char(2),
     census_tract_cd    char(2),
     shape_id           char(2),
     scc                char(12),
     CD                 char(1),
     MSR                char(1),
     activity_type      char(8),  -- POP
     ann_value          double,   -- annual value for VMT or POP
     calc_year          smallint, -- calc year
     date_updated       char(12), -- date updated
     data_set_id        char(2),  -- vmt only
     jan_value          double,   -- vmt only
     feb_value          double,   -- vmt only
     mar_value          double,   -- vmt only
     apr_value          double,   -- vmt only
     may_value          double,   -- vmt only
     jun_value          double,   -- vmt only
     jul_value          double,   -- vmt only
     aug_value          double,   -- vmt only
     sep_value          double,   -- vmt only
     oct_value          double,   -- vmt only
     nov_value          double,   -- vmt only
     dec_value          double,
     comment            char(255)
  )
     ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO ff10pop      -- Population
    (  country_cd,
       region_cd,
       tribal_code,
       census_tract_cd,
       shape_id,
       scc,
       CD,
       MSR,
       activity_Type,
       ann_value,
       calc_year,
       date_updated,
       comment
    )
Select 'US',
       region_cd,
       null,
       null,
       null,
       scc,
       null,
       null,
       'POP',
       sum(finalPopulation),
       (select YearId from year),
       date_updated,
       concat((select database()),'  Results assume the CDB already has the new  HPMS system. No conversion of HPMS.')
from prePopFF10
group by scc;

# Remove rows with no population values.
delete from ff10pop where ann_value is null;

# Copy the final values into the aggregate remote database.
insert into draft_epa2017ff10_7jan2020.FF10POP select * from ff10pop;





############################################################################
# Calculate vehicle miles traveled (VMT).

drop   table if exists roadTypesFL ;
create table           roadTypesFL
select distinct roadTypeId
from   movesdb20181022.roadTypeDistribution;

drop   table if exists sourceAgePopFtRt;  -- with fuel types, with road types
create table           sourceAgePopFtRt
select a.*,
       b.*
from   sourceAgePopFt as a
join   roadTypesFl    as b;

alter table sourceAgePopFtRt add      column RTvmtFrac double;
update      sourceAgePopFtRt as a set      a.RTvmtFrac = ( select b.roadTypeVMTfraction
                                                           from roadTypeDistribution as b
                                                          where a.sourceTypeId = b.sourceTypeId
                                                            and a.roadTypeId   = b.roadTypeId           );

alter table   sourceAgePopFtRt add      column myVmt double;

update        sourceAgePopFtRt set myVmt = travelFraction * RTvmtFrac * stmyFraction;

# ERG update, 24apr19, adb
alter table   sourceAgePopFtRt add      column myVmtST double;
update        sourceAgePopFtRt set myVmtST = travelFractionST * RTvmtFrac * stmyFraction;


drop   table if exists preVmtFF10;
create table           preVmtFF10
select HPMSVTypeId,
       fuelTypeId,
       sourceTypeId,
       roadTypeId,
       modelyearid,
       ageId,
       myVmt,
       myVmtST
from   sourceAgePopFtRt;

# add countyid.
alter table preVMTFF10 add column region_cd char(5);
update      preVMTFF10      set   region_cd = (select LPAD(countyid, 5, '0') from county);
# add SCC.
alter table preVMTFF10 add column scc       char(10);                                          -- Both
update      preVMTFF10 as a set a.scc      = concat('220',      cast(fuelTypeId   as char) );  -- Both
update      preVMTFF10 as a set a.scc      = concat(a.scc,      cast(sourceTypeId as char) );  -- Both
update      preVMTFF10 as a set a.scc      = concat(a.scc, '0', cast(roadTypeId   as char) );  -- for VMT
update      preVMTFF10 as a set a.scc      = concat(a.scc, '00'                            );  -- Both
# add date.
alter table preVMTFF10 add column  date_updated char(8);
update      preVMTFF10 as a set  a.date_updated = concat( substring(curDate(), 1, 4),             -- year
                                                                  substring(curDate(), 6, 2),     -- month
                                                                  substring(curDate(), 9, 2)  );  -- day

# adjust the HPMS VMT to the new definitions (if necessary).
DROP TABLE IF EXISTS hpmsvtypeyear_temp;
CREATE TABLE hpmsvtypeyear_temp (
yearID	int(4),
ht_10	double NULL,
ht_25	double	NOT NULL,
ht_40	double	NOT NULL,
ht_50	double	NOT NULL,
ht_60	double	NOT NULL
);

# Equation is originalVMT[i] * conversionFactor[i] * (SUM originalVMT / SUM (originalVMT[i] * conversionFactor[i]))
INSERT INTO hpmsvtypeyear_temp (yearID, ht_10, ht_25, ht_40, ht_50, ht_60)
SELECT DISTINCT
	   t.yearID,
#	   (t10.HPMSBaseYearVMT * 1.295) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_10,
#	   (t20.HPMSBaseYearVMT * 1.224) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_20,
#	   (t30.HPMSBaseYearVMT * 0.550) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_30,
#	   (t40.HPMSBaseYearVMT * 1.950) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_40,
#	   (t50.HPMSBaseYearVMT * 1.439) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_50,
#	   (t60.HPMSBaseYearVMT * 1.223) * (t10.HPMSBaseYearVMT + t20.HPMSBaseYearVMT + t30.HPMSBaseYearVMT + t40.HPMSBaseYearVMT + t50.HPMSBaseYearVMT + t60.HPMSBaseYearVMT) / ((t10.HPMSBaseYearVMT * 1.295) + (t20.HPMSBaseYearVMT * 1.224) + (t30.HPMSBaseYearVMT * 0.550) + (t40.HPMSBaseYearVMT * 1.950) + (t50.HPMSBaseYearVMT * 1.439) + (t60.HPMSBaseYearVMT * 1.223)) AS ht_60
	   (t10.HPMSBaseYearVMT ) AS ht_10,
	   (t25.HPMSBaseYearVMT ) AS ht_25,
	   (t40.HPMSBaseYearVMT ) AS ht_40,
	   (t50.HPMSBaseYearVMT ) AS ht_50,
	   (t60.HPMSBaseYearVMT ) AS ht_60
FROM hpmsvtypeyear AS t
JOIN hpmsvtypeyear AS t10 #USING (yearID)
JOIN hpmsvtypeyear AS t25 #USING (yearID)
JOIN hpmsvtypeyear AS t40 #USING (yearID)
JOIN hpmsvtypeyear AS t50 #USING (yearID)
JOIN hpmsvtypeyear AS t60 #USING (yearID)
WHERE t10.HPMSVtypeID = 10
  AND t25.HPMSVtypeID = 25
  AND t40.HPMSVtypeID = 40
  AND t50.HPMSVtypeID = 50
  AND t60.HPMSVtypeID = 60;




drop  table  if exists hpmsvtypeyear2;
create table           hpmsvtypeyear2
       like            hpmsvtypeyear;
insert into            hpmsvtypeyear2
     select * from     hpmsvtypeyear;

#delete from hpmsvtypeyear2
#where hpmsvtypeId in (20,30);
alter table hpmsvtypeyear2 drop hpmsbaseyearvmt;
alter table hpmsvtypeyear2 add  hpmsbaseyearvmt double;
#insert into hpmsvtypeyear2 (hpmsvtypeid,yearid,vmtgrowthfactor,hpmsbaseyearvmt)
#                    values (25,(select min(yearId) from hpmsvtypeyear),0.0,0.0);

update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
 set hpmsbaseyearvmt=ht_10 where hpmsvtypeid=10;
#update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
# set hpmsbaseyearvmt=(ht_20+ht_30) where hpmsvtypeid=25;
update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
 set hpmsbaseyearvmt=ht_25 where hpmsvtypeid=25;
update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
 set hpmsbaseyearvmt=ht_40 where hpmsvtypeid=40;
update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
 set hpmsbaseyearvmt=ht_50 where hpmsvtypeid=50;
update hpmsvtypeyear2 as a, hpmsvtypeyear_temp as b
 set hpmsbaseyearvmt=ht_60 where hpmsvtypeid=60;

# allocate the VMT data into categories using the distribution data.
alter table preVMTFF10 add column HPMSBaseYearVmt double;
update      preVMTFF10 as a set a.HPMSBaseYearVmt = (select b.HPMSBaseYearVmt
                                                     from hpmsvtypeYear2 as b
                                                     where a.hpmsVTypeId = b.hpmsVTypeId);
# ERG update, 24apr19, adb
alter table preVMTFF10 add column SourceTypeYearVmt double;
create table if not exists sourceTypeYearVMT like movesdb20181022.sourceTypeYearVMT;
update      preVMTFF10 as a set a.SourceTypeYearVmt = (select b.VMT
                                                     from SourceTypeYearVMT as b
                                                     where a.sourceTypeId = b.sourceTypeId);
					
alter table   preVMTFF10 add      column justVmt double;


# fraction of total travel = travelFraction * RTvmtFrac * stmyFraction = myVmt.
update        preVMTFF10 set justVmt = HPMSBaseYearVmt * myVmt;

# ERG update, 24apr19, adb
update        preVMTFF10 set justVmt = SourceTypeYearVmt * myVmtST where justVMT is null;


# Place final vehicle miles traveled values locally in the FF10 formatted table.
drop table if exists ff10vmt;         -- vehicle miles traveled only.
CREATE TABLE         FF10VMT
  (  country_cd         char(2),   -- US
     region_cd          char(5),   -- county Id
     tribal_code        char(2),
     census_tract_cd    char(2),
     shape_id           char(2),
     scc                char(12),
     CD                 char(1),
     MSR                char(1),
     activity_type      char(8),  -- VMT
     ann_value          double,   -- annual value for VMT or POP
     calc_year          smallint, -- calc year
     date_updated       char(12), -- date updated
     data_set_id        char(2),  -- vmt only
     jan_value          double,   -- vmt only
     feb_value          double,   -- vmt only
     mar_value          double,   -- vmt only
     apr_value          double,   -- vmt only
     may_value          double,   -- vmt only
     jun_value          double,   -- vmt only
     jul_value          double,   -- vmt only
     aug_value          double,   -- vmt only
     sep_value          double,   -- vmt only
     oct_value          double,   -- vmt only
     nov_value          double,   -- vmt only
     dec_value          double,
     comment            char(255),
     sourceTypeId       int
  )
     ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO ff10vmt      -- VMT
    (  country_cd,
       region_cd,
       tribal_code,
       census_tract_cd,
       shape_id,
       scc,
       CD,
       MSR,
       activity_Type,
       ann_value,
       calc_year,
       date_updated,
       comment,
       sourceTypeId
    )
Select 'US',
       region_cd,
       null,
       null,
       null,
       scc,
       null,
       null,
       'VMT',
       sum(justVMT),
       (select YearId from year),
       date_updated,
       concat((select database()), '  Results assume the CDB  has the new  HPMS system. No conversion of HPMS.'),
       sourceTypeId
from preVmtFF10
group by SCC;

# Remove rows with no VMT.
delete from ff10vmt where ann_value is null;
delete from ff10vmt where ann_value=0.0;

# Distribute annual VMT values to months of the year.
Update ff10vmt as a set a.jan_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  1         )  * ann_value;

Update ff10vmt as a set a.feb_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  2          )  * ann_value;

Update ff10vmt as a set a.mar_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  3           )  * ann_value;

Update ff10vmt as a set a.apr_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  4            )  * ann_value;

Update ff10vmt as a set a.may_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  5            )  * ann_value;

Update ff10vmt as a set a.jun_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  6              )  * ann_value;

Update ff10vmt as a set a.jul_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  7          )  * ann_value;

Update ff10vmt as a set a.aug_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  8           )  * ann_value;

Update ff10vmt as a set a.sep_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  9          )  * ann_value;

Update ff10vmt as a set a.oct_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  10          )  * ann_value;

Update ff10vmt as a set a.nov_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  11           )  * ann_value;

Update ff10vmt as a set a.dec_value = (select b.monthVMTfraction
                                    from     monthVMTfraction as b
                                    where  a.activity_type = 'VMT'
                                      and  b.sourceTypeId  = a.sourceTypeId
                                      and  b.monthId       =  12           )  * ann_value;

alter table ff10vmt drop column sourceTypeId;

# Copy the final values into the aggregate remote database.
insert into draft_epa2017ff10_7jan2020.FF10VMT select * from ff10vmt;
/*
############################################################################
# Calculate average speeds for SMOKE.

# create some tables using default values.  Then replace the default values with
# the state supplied values.  This prevents missing rows.
# all distributions are assumed to be complete and sum to one (1.0).

# avgspeeddistribution.
drop table if exists avgspeedtemp;
create table avgspeedtemp like avgspeeddistribution;
insert into avgspeedtemp select * from movesdb20181022.avgspeeddistribution; -- default values.
# mark the default values with datasource=1.
alter table avgspeedtemp add datasource integer;
update avgspeedtemp set datasource=1;
# replace default values with state supplied values. datasource=2.
replace into avgspeedtemp select *,2 from avgspeeddistribution;
alter table avgspeedtemp add index myidx (sourcetypeid,roadtypeid,hourdayid);

# roadtypedistribution.
drop table if exists roaddisttemp;
create table roaddisttemp like roadtypedistribution;
alter table roaddisttemp modify roadtypevmtfraction double;
insert into roaddisttemp select * from movesdb20181022.roadtypedistribution; -- default values.
# mark the default values with datasource=1.
alter table roaddisttemp add datasource integer;
update roaddisttemp set datasource=1;
# replace default values with state supplied values. datasource=2.
replace into roaddisttemp select *,2 from roadtypedistribution;
alter table roaddisttemp add index myidx (sourcetypeid,roadtypeid);

# monthvmtfraction.
drop table if exists monthfractemp;
create table monthfractemp like monthvmtfraction;
alter table monthfractemp modify monthvmtfraction double;
insert into monthfractemp (sourcetypeid,monthid,monthvmtfraction)
 select sourcetypeid,monthid,monthvmtfraction 
 from movesdb20181022.monthvmtfraction -- default values.
# mark the default values with datasource=1.
;
alter table monthfractemp add datasource integer;

update monthfractemp set datasource=1;
# replace default values with state supplied values. datasource=2.
replace into monthfractemp select *,2 from monthvmtfraction;
alter table monthfractemp add index myidx (sourcetypeid,monthid);

# dayvmtfraction.
drop table if exists dayfractemp;
create table dayfractemp like dayvmtfraction;
alter table dayfractemp modify dayvmtfraction double;
insert into dayfractemp select * from movesdb20181022.dayvmtfraction; -- default values.
# mark the default values with datasource=1.
alter table dayfractemp add datasource integer;
update dayfractemp set datasource=1;
# replace default values with state supplied values. datasource=2.
replace into dayfractemp select *,2 from dayvmtfraction;
alter table dayfractemp add index myidx (sourcetypeid,roadtypeid,monthid);

# hourvmtfraction.
drop table if exists hourfractemp;
create table hourfractemp like hourvmtfraction;
alter table hourfractemp modify hourvmtfraction double;
insert into hourfractemp select * from movesdb20181022.hourvmtfraction; -- default values.
# mark the default values with datasource=1.
alter table hourfractemp add datasource integer;
update hourfractemp set datasource=1;
# replace default values with state supplied values. datasource=2.
replace into hourfractemp select *,2 from hourvmtfraction;
alter table hourfractemp add index myidx (sourcetypeid,roadtypeid,dayid);

# calculate the vmt by source type, road type, month, day and hour of the day.
# this will be used to weight together average speeds.

drop table if exists vmttemp1;
create table vmttemp1
 select sourcetypeid,fueltypeid,roadtypeid,sum(justvmt) as vmt
 from prevmtff10 group by sourcetypeid,fueltypeid,roadtypeid;

drop table if exists vmttemp2;
create table vmttemp2
 select a.sourcetypeid,a.fueltypeid,a.roadtypeid,d.monthid,b.dayid,c.hourid,
        (vmt*monthvmtfraction*dayvmtfraction*hourvmtfraction) as vmt
 from vmttemp1 as a, monthfractemp as d, dayfractemp as b, hourfractemp as c
 where a.sourcetypeid=b.sourcetypeid
   and a.sourcetypeid=c.sourcetypeid
   and a.sourcetypeid=d.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.roadtypeid=c.roadtypeid
   and d.monthid=b.monthid
   and b.dayid=c.dayid;
delete from vmttemp2 where roadtypeid=1; -- non-network (parking) 

# add average speeds.
# avgspeedfraction is in terms of time - calculate distance (miles).
alter table avgspeedtemp
 add avgspeed double,
 add miles double;
update avgspeedtemp as a,
       movesdb20181022.avgspeedbin as b
 set avgspeed=avgbinspeed,
     miles=(avgspeedfraction*avgbinspeed)
 where a.avgspeedbinid=b.avgspeedbinid;

# aggregate the speed bin results into average speed by sourcetype/roadtype/day/hourofday.
drop table if exists avgspeeds;
create table avgspeeds
 select sourcetypeid,roadtypeid,hourid,dayid,sum(miles) as mph
 from avgspeedtemp as a,
      movesdb20181022.hourday as b
 where a.hourdayid=b.hourdayid
 group by sourcetypeid,roadtypeid,a.hourdayid;
alter table avgspeeds add index myidx (sourcetypeid,roadtypeid,hourid,dayid);

# ---------------------------------------------------------------------------------------------
# add in the speed information to the hourly vmt data.
# ---------------------------------------------------------------------------------------------

alter table vmttemp2
 add hourdayid integer,
 add avgspeed double,
 add hours double;
update vmttemp2 set hourdayid=((hourid*10)+dayid);

# bring in the vmt values and calculate hours of travel.
update vmttemp2 as a,
       avgspeeds as b
 set a.avgspeed=b.mph,
     a.hours=(a.vmt/b.mph)
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid
   and a.hourid=b.hourid;

# ---------------------------------------------------------------------------------------------
# for the SPDPRO table,
# aggregate vmt and hours into just county and scc by day type and hour of the day.
# ---------------------------------------------------------------------------------------------

drop table if exists spdprotemp;
create table spdprotemp
select LPAD(countyid, 5, '0'),(2200000000+fueltypeid*1000000+sourcetypeid*10000+roadtypeid*100) as scc,
        fueltypeid,sourcetypeid,roadtypeid,
        dayid,hourid,sum(vmt) as miles, sum(hours) as hours,avgspeed
from vmttemp2 as a,
      county as b
group by `LPAD(countyid, 5, '0')`,sourcetypeid,fueltypeid,roadtypeid,dayid,hourid;
#delete from spdprotemp where miles is null;
#delete from spdprotemp where miles = 0.0;
alter table spdprotemp
#add avgspeed double,
add index myidx (`LPAD(countyid, 5, '0')`,scc,dayid,hourid);
#update spdprotemp set avgspeed=miles/hours;

# pivot the table for export to SPDPRO format.
drop table if exists spdpro;
create table spdpro
SELECT `LPAD(countyid, 5, '0')`,scc,
                     max(if(dayid=5,0,'WEEKDAY')) as 'weekday',
                     max(if(hourid=1, if(dayid=5,avgspeed,' '),' ')) as 'hr01a',
                     max(if(hourid=2, if(dayid=5,avgspeed,' '),' ')) as 'hr02a',
                     max(if(hourid=3, if(dayid=5,avgspeed,' '),' ')) as 'hr03a',
                     max(if(hourid=4, if(dayid=5,avgspeed,' '),' ')) as 'hr04a',
                     max(if(hourid=5, if(dayid=5,avgspeed,' '),' ')) as 'hr05a',
                     max(if(hourid=6, if(dayid=5,avgspeed,' '),' ')) as 'hr06a',
                     max(if(hourid=7, if(dayid=5,avgspeed,' '),' ')) as 'hr07a',
                     max(if(hourid=8, if(dayid=5,avgspeed,' '),' ')) as 'hr08a',
                     max(if(hourid=9, if(dayid=5,avgspeed,' '),' ')) as 'hr09a',
                     max(if(hourid=10,if(dayid=5,avgspeed,' '),' ')) as 'hr10a',
                     max(if(hourid=11,if(dayid=5,avgspeed,' '),' ')) as 'hr11a',
                     max(if(hourid=12,if(dayid=5,avgspeed,' '),' ')) as 'hr12a',
                     max(if(hourid=13,if(dayid=5,avgspeed,' '),' ')) as 'hr13a',
                     max(if(hourid=14,if(dayid=5,avgspeed,' '),' ')) as 'hr14a',
                     max(if(hourid=15,if(dayid=5,avgspeed,' '),' ')) as 'hr15a',
                     max(if(hourid=16,if(dayid=5,avgspeed,' '),' ')) as 'hr16a',
                     max(if(hourid=17,if(dayid=5,avgspeed,' '),' ')) as 'hr17a',
                     max(if(hourid=18,if(dayid=5,avgspeed,' '),' ')) as 'hr18a',
                     max(if(hourid=19,if(dayid=5,avgspeed,' '),' ')) as 'hr19a',
                     max(if(hourid=20,if(dayid=5,avgspeed,' '),' ')) as 'hr20a',
                     max(if(hourid=21,if(dayid=5,avgspeed,' '),' ')) as 'hr21a',
                     max(if(hourid=22,if(dayid=5,avgspeed,' '),' ')) as 'hr22a',
                     max(if(hourid=23,if(dayid=5,avgspeed,' '),' ')) as 'hr23a',
                     max(if(hourid=24,if(dayid=5,avgspeed,' '),' ')) as 'hr24a',
                     max(if(dayid=2,0,'WEEKEND')) as 'weekend',
                     max(if(hourid=1, if(dayid=2,avgspeed,' '),' ')) as 'hr01b',
                     max(if(hourid=2, if(dayid=2,avgspeed,' '),' ')) as 'hr02b',
                     max(if(hourid=3, if(dayid=2,avgspeed,' '),' ')) as 'hr03b',
                     max(if(hourid=4, if(dayid=2,avgspeed,' '),' ')) as 'hr04b',
                     max(if(hourid=5, if(dayid=2,avgspeed,' '),' ')) as 'hr05b',
                     max(if(hourid=6, if(dayid=2,avgspeed,' '),' ')) as 'hr06b',
                     max(if(hourid=7, if(dayid=2,avgspeed,' '),' ')) as 'hr07b',
                     max(if(hourid=8, if(dayid=2,avgspeed,' '),' ')) as 'hr08b',
                     max(if(hourid=9, if(dayid=2,avgspeed,' '),' ')) as 'hr09b',
                     max(if(hourid=10,if(dayid=2,avgspeed,' '),' ')) as 'hr10b',
                     max(if(hourid=11,if(dayid=2,avgspeed,' '),' ')) as 'hr11b',
                     max(if(hourid=12,if(dayid=2,avgspeed,' '),' ')) as 'hr12b',
                     max(if(hourid=13,if(dayid=2,avgspeed,' '),' ')) as 'hr13b',
                     max(if(hourid=14,if(dayid=2,avgspeed,' '),' ')) as 'hr14b',
                     max(if(hourid=15,if(dayid=2,avgspeed,' '),' ')) as 'hr15b',
                     max(if(hourid=16,if(dayid=2,avgspeed,' '),' ')) as 'hr16b',
                     max(if(hourid=17,if(dayid=2,avgspeed,' '),' ')) as 'hr17b',
                     max(if(hourid=18,if(dayid=2,avgspeed,' '),' ')) as 'hr18b',
                     max(if(hourid=19,if(dayid=2,avgspeed,' '),' ')) as 'hr19b',
                     max(if(hourid=20,if(dayid=2,avgspeed,' '),' ')) as 'hr20b',
                     max(if(hourid=21,if(dayid=2,avgspeed,' '),' ')) as 'hr21b',
                     max(if(hourid=22,if(dayid=2,avgspeed,' '),' ')) as 'hr22b',
                     max(if(hourid=23,if(dayid=2,avgspeed,' '),' ')) as 'hr23b',
                     max(if(hourid=24,if(dayid=2,avgspeed,' '),' ')) as 'hr24b'
       FROM spdprotemp
       GROUP BY `LPAD(countyid, 5, '0')`,scc;

# Copy the final values into the aggregate remote database.
insert into draft_epa2017ff10_7jan2020.SPDPRO select * from spdpro;

# ---------------------------------------------------------------------------------------------
# for the FF10 table,
# aggregate vmt and hours into into just county and scc by month of the year.
# ---------------------------------------------------------------------------------------------

drop table if exists spdtemp1;
create table spdtemp1
 select LPAD(countyid, 5, '0'),(2200000000+fueltypeid*1000000+sourcetypeid*10000+roadtypeid*100) as scc,
        monthid,sum(vmt) as miles, sum(hours) as hours
 from vmttemp2 as a,
      county as b
 group by `countyid`,fueltypeid,sourcetypeid,roadtypeid,monthid;
delete from spdtemp1 where miles is null;
delete from spdtemp1 where miles = 0.0;
alter table spdtemp1
 add avgspeed double,
 add index myidx (`LPAD(countyid, 5, '0')`,scc,monthid);
update spdtemp1 set avgspeed=miles/hours;
#update spdtemp1 set avgspeed=25 where hours=0; #ADB edit on 3.22.2016
# pivot the table for export to ff10 format.
drop table if exists spdtemp2;
create table spdtemp2
SELECT `LPAD(countyid, 5, '0')`,scc,
#ADB edit on 3/22/2016
                     #max(if(monthid=1, avgspeed,' ')) as 'mo01',
                     #max(if(monthid=2, avgspeed,' ')) as 'mo02',
                     #max(if(monthid=3, avgspeed,' ')) as 'mo03',
                     #max(if(monthid=4, avgspeed,' ')) as 'mo04',
                     #max(if(monthid=5, avgspeed,' ')) as 'mo05',
                     #max(if(monthid=6, avgspeed,' ')) as 'mo06',
                     #max(if(monthid=7, avgspeed,' ')) as 'mo07',
                     #max(if(monthid=8, avgspeed,' ')) as 'mo08',
                     #max(if(monthid=9, avgspeed,' ')) as 'mo09',
                     #max(if(monthid=10,avgspeed,' ')) as 'mo10',
                     #max(if(monthid=11,avgspeed,' ')) as 'mo11',
                     #max(if(monthid=12,avgspeed,' ')) as 'mo12'

                     max(if(monthid=1, avgspeed,0)) as 'mo01',
                     max(if(monthid=2, avgspeed,0)) as 'mo02',
                     max(if(monthid=3, avgspeed,0)) as 'mo03',
                     max(if(monthid=4, avgspeed,0)) as 'mo04',
                     max(if(monthid=5, avgspeed,0)) as 'mo05',
                     max(if(monthid=6, avgspeed,0)) as 'mo06',
                     max(if(monthid=7, avgspeed,0)) as 'mo07',
                     max(if(monthid=8, avgspeed,0)) as 'mo08',
                     max(if(monthid=9, avgspeed,0)) as 'mo09',
                     max(if(monthid=10,avgspeed,0)) as 'mo10',
                     max(if(monthid=11,avgspeed,0)) as 'mo11',
                     max(if(monthid=12,avgspeed,0)) as 'mo12'
       FROM spdtemp1
       GROUP BY `LPAD(countyid, 5, '0')`,scc;

# calculate the annual speed value.
drop table if exists spdtemp3;
create table spdtemp3
 select LPAD(countyid, 5, '0'),(2200000000+fueltypeid*1000000+sourcetypeid*10000+roadtypeid*100) as scc,
        sum(vmt) as miles, sum(hours) as hours
 from vmttemp2 as a,
      county as b
 group by `LPAD(countyid, 5, '0')`,fueltypeid,sourcetypeid,roadtypeid;
alter table spdtemp3
 add avgspeed double,
 add index myidx (`LPAD(countyid, 5, '0')`,scc);
update spdtemp3 set avgspeed=miles/hours;

# add annual value to pivot table.
alter table spdtemp2 add ann_value double;
update spdtemp2 as a,
       spdtemp3 as b
 set a.ann_value=b.avgspeed
 where a.`LPAD(countyid, 5, '0')`=b.`LPAD(countyid, 5, '0')`
   and a.scc=b.scc;

# add the date to the pivot table.
alter table spdtemp2 add column  date_updated char(8);
update      spdtemp2 as a set  a.date_updated = concat( substring(curDate(), 1, 4),     -- year
                                                        substring(curDate(), 6, 2),     -- month
                                                        substring(curDate(), 9, 2)  );  -- day

# Place final average speed values locally in the FF10 formatted table.
drop table if exists ff10spd;
CREATE TABLE FF10SPD (
country_cd          char(2)
,region_cd          char(5)
,tribal_code        char(2)
,census_tract_cd    char(2)
,shape_id           char(2)
,scc                char(12)
,CD                 char(1)
,MSR                char(1)
,activity_type      char(8)
,ann_value          double
,calc_year          smallint
,date_updated       char(12)
,data_set_id        char(2)
,jan_value          double
,feb_value          double
,mar_value          double
,apr_value          double
,may_value          double
,jun_value          double
,jul_value          double
,aug_value          double
,sep_value          double
,oct_value          double
,nov_value          double
,dec_value          double
,comment            char(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
alter table FF10SPD add primary key (region_cd,scc);

# put the speed data into the ff10 file.
insert into FF10SPD (
 country_cd,
 region_cd,
 scc,
 activity_type,
 ann_value,
 calc_year,
 date_updated,
 data_set_id,
 jan_value,
 feb_value,
 mar_value,
 apr_value,
 may_value,
 jun_value,
 jul_value,
 aug_value,
 sep_value,
 oct_value,
 nov_value,
 dec_value,
 comment )
select
 "US" as country_cd,
 `LPAD(countyid, 5, '0')` as region_cd,
 scc,
 "SPEED" as activity_type,
 ann_value,
 (select YearId from year) as year,
 date_updated,
 0 as data_set_id,
 mo01,
 mo02,
 mo03,
 mo04,
 mo05,
 mo06,
 mo07,
 mo08,
 mo09,
 mo10,
 mo11,
 mo12,
 concat((select database()), '  Results assume the CDB already has the new  HPMS system. No conversion of HPMS.')
 from spdtemp2;

 # Copy the final values into the aggregate remote database.
insert into draft_epa2017ff10_7jan2020.FF10SPD select * from ff10spd;

###################################################################
# Calculate hotelling hours.

## -----------------------------------------------------------------------------
## create an FF10 table for the hotelling hours.
## -----------------------------------------------------------------------------
drop table if exists ff10hot;
CREATE TABLE FF10hot (
country_cd          char(2)
,region_cd          char(5)
,tribal_code        char(2)
,census_tract_cd    char(2)
,shape_id           char(2)
,scc                char(12)
,CD                 char(1)
,MSR                char(1)
,activity_type      char(10)
,ann_value          double
,calc_year          smallint
,date_updated       char(12)
,data_set_id        char(2)
,jan_value          double
,feb_value          double
,mar_value          double
,apr_value          double
,may_value          double
,jun_value          double
,jul_value          double
,aug_value          double
,sep_value          double
,oct_value          double
,nov_value          double
,dec_value          double
,comment            char(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

# calculate the hours of hotelling from the diesel long haul combination truck VMT
# on both urban and rural restricted access roadways (freeways) using
# an hours/vmt hotelling rate calculated from a MOVES2014a run
# adjusted by the hotelling activity distribution by model year.

drop table if exists hourstemp;
create table hourstemp
 select fueltypeid,sourcetypeid,modelyearid,roadtypeid,sum(justvmt) as vmt
 from prevmtff10
 where fueltypeid=2 and sourcetypeid=62 and roadtypeid in (2,4)
 group by fueltypeid,sourcetypeid,modelyearid,roadtypeid;

alter table hourstemp
 add extfraction double,
 add apufraction double,
 add exthours double,
 add apuhours double;

# start with default hotelling activity values.
update hourstemp as a, movesdb20181022.hotellingactivitydistribution as b
 set extfraction=opmodefraction
 where a.modelyearid>=beginmodelyearid
   and a.modelyearid<=endmodelyearid
   and b.opmodeid=200;
update hourstemp as a, movesdb20181022.hotellingactivitydistribution as b
 set apufraction=opmodefraction
 where a.modelyearid>=beginmodelyearid
   and a.modelyearid<=endmodelyearid
   and b.opmodeid=201;
# replace defaults with user supplied hotelling activity (if any).
update hourstemp as a, hotellingactivitydistribution as b
 set extfraction=opmodefraction
 where a.modelyearid>=beginmodelyearid
   and a.modelyearid<=endmodelyearid
   and b.opmodeid=200;
update hourstemp as a, hotellingactivitydistribution as b
 set apufraction=opmodefraction
 where a.modelyearid>=beginmodelyearid
   and a.modelyearid<=endmodelyearid
   and b.opmodeid=201;

update hourstemp
 set exthours=vmt*0.027337*extfraction,
     apuhours=vmt*0.027337*apufraction;

alter table hourstemp add column  date_updated char(8);
update      hourstemp as a set  a.date_updated = concat( substring(curDate(), 1, 4),             -- year
                                                                  substring(curDate(), 6, 2),    -- month
                                                                  substring(curDate(), 9, 2)  ); -- day

# -- replace the annual hotelling hours with user supplied data (if any)
# -- found in the HotellingHours table of the CDB.
drop table if exists totalhourstemp;
create table totalhourstemp
select date_updated,fueltypeid,sourcetypeid,
       sum(exthours) as exthours,sum(apuhours) as apuhours
from hourstemp
group by fueltypeid,sourcetypeid;

alter table totalhourstemp
add totalhours double;
update totalhourstemp set totalhours=exthours+apuhours;

# -- calculate total annual hotelling hours from user data.
drop table if exists totaltemp1;
create table totaltemp1
select yearid,floor(zoneid/10) as countyid,sourcetypeid,monthid,
       (hourdayid-(10*floor(hourdayid/10))) as dayid,
       sum(hotellinghours) as hours
from hotellinghours
group by yearid,countyid,sourcetypeid,monthid,dayid;

alter table totaltemp1
add monthhours double;

update totaltemp1 as a,
       movesdb20181022.monthofanyyear as b
set monthhours=hours*noofdays/7.0
where a.monthid=b.monthid;

drop table if exists totaltemp2;
create table totaltemp2
select yearid,countyid,sourcetypeid,monthid,avg(monthhours) as monthhours
from totaltemp1
group by yearid,countyid,sourcetypeid,monthid;

drop table if exists totaltemp3;
create table totaltemp3
select yearid,countyid,sourcetypeid,sum(monthhours) as hours
from totaltemp2
group by yearid,countyid,sourcetypeid;

# -- determine the travel fraction for diesel lon-haul combination trucks.
drop table if exists travelfraction;
create table travelfraction
select yearid,fueltypeid,sourcetypeid,modelyearid,ageid,sum(myvmt) as vmt
from sourceagepopftrt
where fueltypeid=2 and sourcetypeid=62
group by yearid,fueltypeid,sourcetypeid,modelyearid;

alter table travelfraction
add totalvmt double,
add travelfraction double,
add index myidx (modelyearid);

# -- calculate travel fraction.
drop table if exists test.junktotal;
create table test.junktotal
select sum(vmt) as totalvmt from travelfraction; 
update travelfraction as a, test.junktotal as b
set a.totalvmt=b.totalvmt,
    a.travelfraction=a.vmt/b.totalvmt;

# -- calculate the APU fraction for the user supplied hours.
drop table if exists userhourstemp;
create table userhourstemp
select a.yearid,a.fueltypeid,a.sourcetypeid,a.modelyearid,ageid,
       roadtypeid,travelfraction,extfraction,apufraction,hours,
       (a.travelfraction*b.extfraction*c.hours) as exthours,
       (a.travelfraction*b.apufraction*c.hours) as apuhours
from travelfraction as a,
     hourstemp as b,
	 totaltemp3 as c
where a.sourcetypeid=62 
and a.fueltypeid=2
and b.roadtypeid in (2,4)                    -- fractions are the same for both road types.
and a.sourcetypeid=b.sourcetypeid
and a.modelyearid=b.modelyearid
and a.fueltypeid=b.fueltypeid;

# -- replace default hotelling hours with user supplied data (if any).
update hourstemp as a,userhourstemp as b
set a.exthours=b.exthours,
    a.apuhours=b.apuhours
where a.sourcetypeid=b.sourcetypeid 
and a.fueltypeid=b.fueltypeid
and a.roadtypeid=b.roadtypeid  
and a.sourcetypeid=b.sourcetypeid
and a.modelyearid=b.modelyearid
and a.fueltypeid=b.fueltypeid;

# -- Place final hotelling hours values locally in the FF10 formatted table.
insert into ff10hot (
 country_cd,
 region_cd,
 scc,
 activity_type,
 ann_value,
 calc_year,
 date_updated,
 data_set_id,
 comment )
select
 "US",
 LPAD(countyid, 5, '0'),
 "2202620153",
 "HOTELLING",
 sum(exthours) as ann_value,
 yearid,
 date_updated,
 1,
 "Annual extended idle hours calculated from a CDB or the NEI 2014 default method."
from county as a,
     year as b,
     hourstemp as c
 group by fueltypeid,sourcetypeid;
# APU hours.
insert into ff10hot (
 country_cd,
 region_cd,
 scc,
 activity_type,
 ann_value,
 calc_year,
 date_updated,
 data_set_id,
 comment )
select
 "US",
 LPAD(countyid, 5, '0'),
 "2202620191",
 "HOTELLING",
 sum(apuhours) as ann_value,
 yearid,
 date_updated,
 1,
 concat("Annual APU hours calculated from a CDB or the NEI 2014 default method.")
from county as a,
     year as b,
     hourstemp as c
 group by fueltypeid,sourcetypeid;
 
# Copy the final values into the aggregate remote database.
# **** Insert disabled until hotelling portion reviewed. *******
insert into draft_epa2017ff10_7jan2020.FF10HOT select * from ff10hot;
*/
############################################################################
# Drop the intermediate tables.

drop table if exists sourceAgePop;
drop table if exists sourceagepopft;
drop table if exists sourceagepopftrt;
drop table if exists disFuelTypes;
drop table if exists hpmsvtypeyear_temp;
drop table if exists hpmsvtypeyear2;
drop table if exists prepopff10;
drop table if exists preVMTff10;
drop table if exists roadtypesfl;
drop table if exists avgspeedtemp;
drop table if exists roaddisttemp;
drop table if exists monthfractemp;
drop table if exists dayfractemp;
drop table if exists hourfractemp;
drop table if exists vmttemp1;
drop table if exists vmttemp2;
drop table if exists avgspeedtemp;
drop table if exists avgspeeds;
drop table if exists spdprotemp;
drop table if exists spdtemp1;
drop table if exists spdtemp2;
drop table if exists spdtemp3;
drop table if exists hourstemp;
drop table if exists totalhourstemp;
drop table if exists totaltemp1;
drop table if exists totaltemp2;
drop table if exists totaltemp3;
drop table if exists userhourstemp;
drop table if exists travelfraction;
drop table if exists scctemp;
drop table if exists trucks;

drop table if exists ff10vmt;
drop table if exists ff10pop;
drop table if exists ff10spd;
drop table if exists spdpro;
drop table if exists ff10hot;

flush tables;
## ****************************************************************************************
## Results found in tables:
##      FF10VMT
##      FF10POP
##      FF10SPD
##      SPDPRO
##      FF10HOT
## Save tables in a comma separated variable (CSV) format.
## The quotes around any blank or text fields must be removed.
## ****************************************************************************************
