import pandas as pd
from flask_app import db_query
from aurapi_src.handlers.aura_spreadsheet import AuraExcel
query_sl ="""
with fields as (
select l.id as id_lote, l.nombre as lote, e2.nombre as campo
from login_usuarios_escritorios lue
join permisos p on p.id_usuario =lue.id_usuario and p.fecha_fin is null and p.id_rol =1
join lotes l on l.id =p.id_lote and l.fecha_fin is null
join estancias e2 on l.id_estancia =e2.id
where l.id = any ( %(lotes)s )
)
select fields.*,
fecha_captura as "fecha",
replace(ndvi_mean::text,'.',',') as "ndvi medio",
replace(gndvi_mean::text,'.',',') as "gndvi medio",
10 as "Resolución(m)"
from capas
join fields using (id_lote)
join sentinel_layers sl on capas.id=layer_id
where not cloudy
and ndvi_mean is not null and gndvi_mean is not null
and fecha_captura between %(from)s::date and %(to)s::date
order by campo, lote, fecha_captura desc
"""
query_pl = """
with fields as (
select l.id as id_lote, l.nombre as lote, e2.nombre as campo
from login_usuarios_escritorios lue
join permisos p on p.id_usuario =lue.id_usuario and p.fecha_fin is null and p.id_rol =1
join lotes l on l.id =p.id_lote and l.fecha_fin is null
join estancias e2 on l.id_estancia =e2.id
where l.id = any ( %(lotes)s )
),
images as (
select distinct on (id_lote, fecha_captura::date) id_lote, fecha_captura::date as "fecha",
replace(ndvi_mean::text,'.',',') as "ndvi medio",
replace(gndvi_mean::text,'.',',') as "gndvi medio",
3 as "Resolución(m)"
from capas
join fields using (id_lote)
join planet_layers pl on capas.id=layer_id
where not cloudy
and ndvi_mean is not null and gndvi_mean is not null
and fecha_captura between %(from)s::date and %(to)s::date
order by id_lote, fecha_captura::date desc, (metadata->'properties'->>'view_angle')::float asc
)
select fields.*,
images."fecha",
"ndvi medio",
"gndvi medio",
"Resolución(m)"
from images
join fields using (id_lote)
order by campo, lote, "fecha" desc
"""
sent = db_query(query_sl, {'lotes': [],
'from': '2015-01-01',
'to': '2021-10-01'}, dict=True).fetchall()
planet = db_query(query_pl, {'lotes': [],
'from': '2015-01-01',
'to': '2021-10-01'}, dict=True).fetchall()
ex = AuraExcel('datos.xlsx')
sent=pd.DataFrame(sent)
planet=pd.DataFrame(planet)
ndvi_sent = sent.drop(['id_lote', 'Resolución(m)'], axis=1)\
.set_index(['campo', 'lote'])\
.pivot(columns='fecha')['ndvi medio']
gndvi_sent = sent.drop(['id_lote', 'Resolución(m)'], axis=1)\
.set_index(['campo', 'lote'])\
.pivot(columns='fecha')['gndvi medio']
ndvi_planet = planet.drop(['id_lote', 'Resolución(m)'], axis=1)\
.set_index(['campo', 'lote'])\
.pivot(columns='fecha')['ndvi medio']
gndvi_planet = planet.drop(['id_lote', 'Resolución(m)'], axis=1)\
.set_index(['campo', 'lote'])\
.pivot(columns='fecha')['gndvi medio']
ex.add_sheet(ndvi_sent, 'NDVI', index=True)
ex.add_sheet(ndvi_planet, 'NDVI HD', index=True)
ex.add_sheet(gndvi_sent, 'GNDVI', index=True)
ex.add_sheet(gndvi_planet, 'GNDVI HD', index=True)
ex.close_excel()
with open(ex.filename, 'wb') as f:
f.write(ex.get_mem_file().read())