'file is sql query for sub region demand
'created by bt 10/5/2020
cd c:\warem32\
'import sql history
%start = @str(@dateval(@date)-364)
%end = @str(@dateval(@date)+14)
%today = @date
%fstart = @str(@dateval(@date)+1)
wfcreate(wf=sqldemandhist) d7 %start %end
pagecreate(page=sqldemandhist) u 1
%path = "P:\ple\dailyreports\sharedfiles\storage\storage models\sqldemandhist.txt"
shell(out=sqldemandhist) type %path
%sqldemandhist = sqldemandhist(1,1)
wfclose
'unstack history
wfopen(type=odbc, link wf=sqldemandhist) dsn=plserver %sqldemandhist
series date = @dateval(pointreadingaggregate_endingdate, "yyyy-mm-dd")
pageunstack(namepat=*?) regprod date @ *
pageselect untitled1
delete pleproduct* 
pagecreate(page=history) d7 %start %today
copy untitled1\pointreading* history\pointreading*
'group g_all pointreading*
'g_all.drop pointreadingaggregate_endingdate
'%vars = g_all.@members
'for %x {%vars}
'  series {%x} = {%x}(-1)
'next
wfsave sql_demand_history
wfclose sql_demand_history

'import sql forecast
wfcreate(wf=sqldemandfore) d7 %fstart %end
pagecreate(page=sqldemandfore) u 1
%path = "p:\ple\dailyreports\sharedfiles\storage\storage models\sqldemandfore.txt"
shell(out=sqldemandfore) type %path
%sqldemandfore = sqldemandfore(1,1)
wfclose
'unstack forecast
wfopen(type=odbc, link wf=sqldemandfore) dsn=plserver %sqldemandfore
series date = @dateval(pointreadingaggregate_endingdate, "yyyy-mm-dd")
pageunstack(namepat=*?) regprod date @ *
pageselect untitled1
delete pleproduct*
pagecreate(page=forecast) d7 %fstart %end
copy untitled1\pointreading* forecast\pointreading*
wfsave sql_demand_forecast

wfopen sql_demand_history
wfcreate(wf=sql_demand) d7 %start %end
pagerename untitled data
copy sql_demand_history::history\pointreading* sql_demand::data\pointreading*
wfclose sql_demand_history
copy(m) sql_demand_forecast::forecast\pointreading* sql_demand::data\pointreading*
wfclose sql_demand_forecast

rename pointreadingaggregate_value262491 DPOWNEAT
rename pointreadingaggregate_value262501 DPOWNEAP
rename pointreadingaggregate_value262511 DPOWWTCA
rename pointreadingaggregate_value262521 DPOWMCLO
rename pointreadingaggregate_value262531 DPOWMCMW
rename pointreadingaggregate_value262541 DPOWMCPL
rename pointreadingaggregate_value262551 DPOWNENE
rename pointreadingaggregate_value262561 DPOWNENN
rename pointreadingaggregate_value262571 DPOWWTPN
rename pointreadingaggregate_value262581 DPOWROMO
rename pointreadingaggregate_value262591 DPOWSEAT
rename pointreadingaggregate_value262601 DPOWSESC
rename pointreadingaggregate_value262611 DPOWWTDS
rename pointreadingaggregate_value262621 DPOWNOSELA
rename pointreadingaggregate_value262631 DPOWSOSELA
rename pointreadingaggregate_value262641 DPOWWETEXA
rename pointreadingaggregate_value262651 DPOWNOTEXA
rename pointreadingaggregate_value262661 DPOWSOTEXA
rename pointreadingaggregate_value262492 DINDNEAT
rename pointreadingaggregate_value262502 DINDNEAP
rename pointreadingaggregate_value262512 DINDWTCA
rename pointreadingaggregate_value262522 DINDMCLO
rename pointreadingaggregate_value262532 DINDMCMW
rename pointreadingaggregate_value262542 DINDMCPL
rename pointreadingaggregate_value262552 DINDNENE
rename pointreadingaggregate_value262562 DINDNENN
rename pointreadingaggregate_value262572 DINDWTPN
rename pointreadingaggregate_value262582 DINDROMO
rename pointreadingaggregate_value262592 DINDSEAT
rename pointreadingaggregate_value262602 DINDSESC
rename pointreadingaggregate_value262612 DINDWTDS
rename pointreadingaggregate_value262622 DINDNOSELA
rename pointreadingaggregate_value262632 DINDSOSELA
rename pointreadingaggregate_value262642 DINDWETEXA
rename pointreadingaggregate_value262652 DINDNOTEXA
rename pointreadingaggregate_value262662 DINDSOTEXA
rename pointreadingaggregate_value262493 DRESCOMNEAT
rename pointreadingaggregate_value262503 DRESCOMNEAP
rename pointreadingaggregate_value262513 DRESCOMWTCA
rename pointreadingaggregate_value262523 DRESCOMMCLO
rename pointreadingaggregate_value262533 DRESCOMMCMW
rename pointreadingaggregate_value262543 DRESCOMMCPL
rename pointreadingaggregate_value262553 DRESCOMNENE
rename pointreadingaggregate_value262563 DRESCOMNENN
rename pointreadingaggregate_value262573 DRESCOMWTPN
rename pointreadingaggregate_value262583 DRESCOMROMO
rename pointreadingaggregate_value262593 DRESCOMSEAT
rename pointreadingaggregate_value262603 DRESCOMSESC
rename pointreadingaggregate_value262613 DRESCOMWTDS
rename pointreadingaggregate_value262623 DRESCOMNOSELA
rename pointreadingaggregate_value262633 DRESCOMSOSELA
rename pointreadingaggregate_value262643 DRESCOMWETEXA
rename pointreadingaggregate_value262653 DRESCOMNOTEXA
rename pointreadingaggregate_value262663 DRESCOMSOTEXA
for %x drescom dind dpow
  series {%x}texa = {%x}notexa+{%x}wetexa+{%x}sotexa
  series {%x}sela = {%x}nosela+{%x}sosela
next
delete *sotexa *notexa *wetexa *nosela *sosela

%mstart = @str(@datepart(@dateval(@date),"yyyy")-6)
%mend = @str(@datepart(@dateval(@date),"yyyy"))
pagecreate(page=monthly) m %mstart %mend
dbopen demandcurrent
fetch dpow* dres* dind* dcom*
close demandcurrent
wfopen Y:\regionals\regstrings
copy regstrings::pl\*pl* sql_demand::monthly\*pl*
wfclose regstrings
delete d*ab d*bc d*mb d*nb d*ns d*on d*qb d*sk d*can* *pl*can* d*ak
string plregions = @wdrop(plregions,"cane canw cana mdct west noea soea")
group g_all dres*
%all = g_all.@members
%all = @replace(%all,"DRES","")
for %x {%all}
  series drescom{%x} = dres{%x}+dcom{%x}
next
for %z drescom dind dpow
  for %x {plregions}
    for %y {statespl{%x}}
      group g_sh{%z}{%y} {%z}{%y}(-60)/{%z}pl{%x}(-60) {%z}{%y}(-12)/{%z}pl{%x}(-12) {%z}{%y}(-24)/{%z}pl{%x}(-24) {%z}{%y}(-36)/{%z}pl{%x}(-36) {%z}{%y}(-48)/{%z}pl{%x}(-48)
      series sh{%z}{%y} = @rmean(g_sh{%z}{%y})
    next
  next
next

wfcreate(wf=weekly) w(Friday) %start %end
pagerename untitled weekly
copy(c=a) sql_demand::data\d* weekly::weekly\d*
'copy sql_demand::monthly\sh* weekly::weekly\sh* 
'copy sql_demand::monthly\d* weekly::weekly\d*
stop
for %x dpow drescom dind
  series {%x}east = {%x}neap+{%x}neat+{%x}nene+{%x}nenn+{%x}seat
  series {%x}midwest = {%x}mcmw
  series {%x}pacific = {%x}wtpn-({%x}wtpn*sh{%x}id)+{%x}ca
  series {%x}mountain = {%x}romo+{%x}wtds+{%x}mcpl
  series {%x}southcentral = {%x}sela+{%x}texa+{%x}sesc+{%x}mclo
next
pagecreate(page=export) w(monday) %start %end
for %x east midwest mountain pacific southcentral
  copy weekly\*{%x} export\*{%x}
next