from Libmod.psql import BaseDatos
from Libmod.reporte import Reporte
repo = Reporte()
db = BaseDatos()

db.SELECT = """SELECT dom.name as dominio, pf.create_date, pf.pfs_fecha, cli.name, cli.street, cli.street2, loc.name AS localidad, prov.name AS provincia, cli.zip AS codigo_postal, pais.name AS pais, pf.domicilio AS domicilio_pf FROM 
(
	(koozo_planillafosa AS pf LEFT JOIN account_invoice AS comp ON pf.pfs_acc_inv = comp.id) 
	LEFT JOIN 
	res_partner AS cli ON comp.partner_id = cli.id
	LEFT JOIN
	res_country_state AS prov ON prov.id = cli.state_id
	LEFT JOIN
	res_country AS pais ON pais.id = cli.country_id
	LEFT JOIN
	koozo_dominios AS dom ON dom.id = comp.ain_dom_id
	LEFT JOIN
	koozo_localidad AS loc ON loc.id = cli.par_loc_id OR loc.id = pf.pfs_loc_id
) WHERE  pf."tws_Tipo" = 'cent' AND pf.create_date >= '01/01/2019 12:00:00 AM' AND pf.create_date <= '01/02/2020 12:00:00 AM'"""

db.Leer()
#query = db.resultado

repo.QUERY = db.resultado
repo.Encabesado = ["DOMINIO", "DATETIME", "FECHA", "CLENTE", "CALLE", "CALLE2", "LOCALIDAD", "PROBINCIA", "COD POSTAL", "PAIS", "DOMICILIO"]
repo.ArmarLibro()