drop view if exists gasroh; 
drop view if exists vgasroh; 
create view vgasroh as

SELECT id,zeitpunkt,gaszaehler,wetterinfo
,	 CAST(replace(gaszaehler ,',','.') as float) as gasval,
	 
	   lag ( CAST(replace(gaszaehler ,',','.') as float) ,1 ) over (order by id) 
	as vorgasval 
	
,	
case when
round(
 ( CAST(replace(gaszaehler ,',','.') as float)) - (lag ( CAST(replace(gaszaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
is null then 0.0 
else 	
round(
 ( CAST(replace(gaszaehler ,',','.') as float)) - (lag ( CAST(replace(gaszaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
end
as gasvolumen
	
,	( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0
		as zeitspannestd

, 
( ( CAST(replace(gaszaehler ,',','.') as float)) - (lag ( CAST(replace(gaszaehler ,',','.') as float),1 ) over (order by id)) )
/(  ( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0 )
 * 24.0 as gasvolumen24
 
, 10.0 as gasfaktor

, case when 
( ( CAST(replace(gaszaehler ,',','.') as float)) - (lag ( CAST(replace(gaszaehler ,',','.') as float),1 ) over (order by id)) )
/(  ( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0 )
is null then 0.0 
else
10.0 * ( 
( ( CAST(replace(gaszaehler ,',','.') as float)) - (lag ( CAST(replace(gaszaehler ,',','.') as float),1 ) over (order by id)) )
/(  ( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0 )
 * 24.0
 ) 

 end 
			as gasverbrauchKWhauf24

, cast ( substr(wetterinfo, 0,instr(wetterinfo,'°C|')) as float)
as GradCelsiusAussen
			
from eintraege where gaszaehler>0 ;
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------

drop view if exists gassum; 
drop view if exists vgassum; 
create view vgassum as

select 
cast ( strftime('%Y%m' ,zeitpunkt) as INTEGER )  as mon
,round ( sum(gasvolumen  ),3) * max(gasfaktor) as gasKWhSum
,max(gasfaktor) as gasfaktor
from vgasroh
group by mon
;


-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------

drop view if exists vstromroh; 
create view vstromroh as

SELECT id,zeitpunkt,stromzaehler,wetterinfo
,	 CAST(replace(stromzaehler ,',','.') as float) as stromval,
	 
	   lag ( CAST(replace(stromzaehler ,',','.') as float) ,1 ) over (order by id) 
	as vorstromval 
	
,	
case when
round(
 ( CAST(replace(stromzaehler ,',','.') as float)) - (lag ( CAST(replace(stromzaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
is null then 0.0 
else 	
round(
 ( CAST(replace(stromzaehler ,',','.') as float)) - (lag ( CAST(replace(stromzaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
end
as stromverzehr
	
,	( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0
		as zeitspannestd

, 
( ( CAST(replace(stromzaehler ,',','.') as float)) - (lag ( CAST(replace(stromzaehler ,',','.') as float),1 ) over (order by id)) )
/(  ( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0 )
 * 24.0 as stromverzehr24
 

			
from eintraege where stromzaehler>0 ;

-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------

drop view if exists vstromsum; 
create view vstromsum as

select 
cast ( strftime('%Y%m' ,zeitpunkt) as INTEGER )  as mon

,round ( sum(stromverzehr ),3) as stromKWhSum

from vstromroh
group by mon
;



-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------

drop view if exists vwasserroh; 
create view vwasserroh as

SELECT id,zeitpunkt,wasserzaehler,wetterinfo
,	 CAST(replace(wasserzaehler ,',','.') as float) as wasserval,
	 
	   lag ( CAST(replace(wasserzaehler ,',','.') as float) ,1 ) over (order by id) 
	as vorwasserval 
	
,	
case when
round(
 ( CAST(replace(wasserzaehler ,',','.') as float)) - (lag ( CAST(replace(wasserzaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
is null then 0.0 
else 	
round(
 ( CAST(replace(wasserzaehler ,',','.') as float)) - (lag ( CAST(replace(wasserzaehler ,',','.') as float),1 ) over (order by id)) 
	,3) 
end
as wasserverbrauch
	
,	( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0
		as zeitspannestd

, 
( ( CAST(replace(wasserzaehler ,',','.') as float)) - (lag ( CAST(replace(wasserzaehler ,',','.') as float),1 ) over (order by id)) )
/(  ( julianday(zeitpunkt) - julianday ( ( lag(zeitpunkt,1) over (order by id)  ) ) ) * 24.0 )
 * 24.0 as wasserverbrauch24
 

			
from eintraege where wasserzaehler>0 ;


-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------


drop view if exists vwassersum; 
create view vwassersum as

select 
cast ( strftime('%Y%m' ,zeitpunkt) as INTEGER )  as mon

,round ( sum(wasserverbrauch ),3) as wasserQmSum

from vwasserroh
group by mon
;

-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
drop view if EXISTS vmonatssummen;

create view vmonatssummen as 

SELECT w.mon, w.wasserQmSum, s.stromKWhSum, g.gasKWhSum

FROM vwassersum as w
left join vstromsum as s on w.mon = s.mon
left join vgassum as g on w.mon = g.mon
; 

-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
DROP VIEW if EXISTS vgasweeksum ;
CREATE VIEW vgasweeksum as

select 
cast ( strftime('%Y%W' ,zeitpunkt) as INTEGER )  as KW
,round ( sum(gasvolumen  ),3) * max(gasfaktor) as gasKWhSum
,max(gasfaktor) as gasfaktor
from vgasroh
group by KW;

-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
DROP VIEW if EXISTS vstromweeksum ;
CREATE VIEW vstromweeksum as

select 
cast ( strftime('%Y%W' ,zeitpunkt) as INTEGER )  as KW
,round ( sum(stromverzehr ),3) as stromKWhSum

from vstromroh
group by KW;

-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------

DROP VIEW if exists "main"."vwasserweeksum";
CREATE VIEW vwasserweeksum as

select 
cast ( strftime('%Y%W' ,zeitpunkt) as INTEGER )  as KW

,round ( sum(wasserverbrauch ),3) as wasserQmSum

from vwasserroh
group by KW


-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------


DROP VIEW if EXISTS "main"."vwochensummen";
CREATE VIEW vwochensummen as 

SELECT w.KW, w.wasserQmSum, s.stromKWhSum, g.gasKWhSum, g.gasfaktor

FROM vwasserweeksum as w
left join vstromweeksum as s on w.KW = s.KW
left join vgasweeksum as g on w.KW = g.KW