--Energy - The meter readings from the E columns
.echo on
.headers on
.mode column

.open 'ramdisk/aiox.db'
select load_extension('/usr/lib/arm-linux-gnueabihf/libsqlitefunctions.so');  --rpi

--date range of 
select count(), min(dt), max(dt) from ir;

--By Day
select count(), date(dt) day, min(dt), max(dt) from ir group by day;

--View 
drop view if exists Etoday;
create view Etoday as
 select  date(dt) day
, (max(Epv1_total) - min(Epv1_total)) Epv1
, (max(Eac_chrTotal)    -  min(Eac_chrTotal)) Eac_chr
, (max(Ebat_dischrTotal)-  min(Ebat_dischrTotal)) Ebat_dischr
, (max(Eac_dischrTotal)-  min(Eac_dischrTotal)) Eac_dischr
, (max(Eop_dischrTotal)-  min(Eop_dischrTotal)) Eop_dischr
, avg(dcdctemp) * .024 * (0+strftime('%H', max(ir.dt))*.0435)  frzr
, 50 * .024 * (0+strftime('%H', max(ir.dt))*.0435) self
 from ir group by day;
.schema Etoday
select * from Etoday;

select   day,         Epv1,        Eac_chr,     Ebat_dischr,  frzr,        self 
, Epv1  +  Eac_chr Charged
, Ebat_dischr + self + frzr Discharged
, (Epv1  +  Eac_chr) - (Ebat_dischr + self + frzr) PutTake
 from Etoday;

/*
--select  date(dt) day
, round(-(min(Epv1_total) - max(Epv1_total)),1) Epv1
, round(-(min(Eac_chrTotal)    -  max(Eac_chrTotal)),1) Eac_chr
, round(-(min(Ebat_dischrTotal)-  max(Ebat_dischrTotal)),1) Ebat_dischr
, round(-(min(Eac_dischrTotal)-  max(Eac_dischrTotal)),1) Eac_dischr
, round(-(min(Eop_dischrTotal)-  max(Eop_dischrTotal)),1) Eop_dischr
, round((avg(dcdctemp) * .024 * (0+strftime('%H', max(ir.dt))*.0435) ), 3) frzr
, round(50 * .024 * ((0+strftime('%H', max(ir.dt))*.0435)) , 1) self
 from ir group by day;

--select   day,         Epv1,        Eac_chr,     Ebat_dischr,  frzr,        self 
, round(Epv1  +  Eac_chr, 1) Charged
, round(Ebat_dischr + self + frzr, 1) Discharged
, round((Epv1  +  Eac_chr) - (Ebat_dischr + self + frzr), 1) PutTake
 from Etoday;
*/

