SELECT
	receipts.id as IdComprobante,
    persons.id as IdProveedorCliente,
    receipts.person_id_owner as Empresa,
    periods.id as IdPeriodo,
	periods.date_from as Desde,
    periods.date_to as Hasta,
	periods.code as Periodo,
    types.name as Operacion,
	persons.field_name1 as Proveedor,
    persons.document_type as TipoDocumento,
    persons.document as Documento,
    zones.name as Zona,
    status.name as Estado,
    activities.name as Actividad,
    receipts.reference as Referencia,
    retention_types.name as TipoDeRetencion,
    receipt_types.name as TipoComprobante, 
    receipts.code_ticket as Suc,
    receipts.number as Numero,
    receipts.amount as Total,
	sum(case when receipt_taxes.tax_id=3 then (receipt_taxes.amount) else 0 end) as gravado_27,
	sum(case when receipt_taxes.tax_id=3 then (receipt_taxes.tax_amount) else 0 end) as IVA_27,
    sum(case when receipt_taxes.tax_id=2 then (receipt_taxes.amount) else 0 end) as gravado_21,
	sum(case when receipt_taxes.tax_id=2 then (receipt_taxes.tax_amount) else 0 end) as IVA_21,
	sum(case when receipt_taxes.tax_id=1 then (receipt_taxes.amount) else 0 end) as gravado_105,
	sum(case when receipt_taxes.tax_id=1 then (receipt_taxes.tax_amount) else 0 end) as IVA_105,
    sum(case when receipt_taxes.tax_id=5 then (receipt_taxes.amount) else 0 end) as gravado_5,
	sum(case when receipt_taxes.tax_id=5 then (receipt_taxes.tax_amount) else 0 end) as IVA_5,
    sum(case when receipt_taxes.tax_id=4 then (receipt_taxes.amount) else 0 end) as gravado_2_5,
	sum(case when receipt_taxes.tax_id=4 then (receipt_taxes.tax_amount) else 0 end) as IVA_2_5,
    sum(case when receipt_taxes.tax_id=7 then (receipt_taxes.amount) else 0 end) as no_gravado,
    sum(case when receipt_taxes.tax_id=1000 then (receipt_taxes.amount) else 0 end) as percepIva,
    sum(case when receipt_taxes.tax_id=1001 then (receipt_taxes.amount) else 0 end) as Imps_Internos,
	sum(case when receipt_taxes.tax_id=1002 then (receipt_taxes.amount) else 0 end) as Imp_Gasoil,
	sum(case when receipt_taxes.tax_id=1003 then (receipt_taxes.amount) else 0 end) as Imp_Hídricos,
	sum(case when receipt_taxes.tax_id=1004 then (receipt_taxes.amount) else 0 end) as ITC,
	sum(case when receipt_taxes.tax_id=1005 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Buenos_Aires,
	sum(case when receipt_taxes.tax_id=1006 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Capital_Federal,
	sum(case when receipt_taxes.tax_id=1007 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Catamarca,
	sum(case when receipt_taxes.tax_id=1008 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Chaco,
	sum(case when receipt_taxes.tax_id=1009 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Chubut,
	sum(case when receipt_taxes.tax_id=1010 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Córdoba,
	sum(case when receipt_taxes.tax_id=1011 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Corrientes,
	sum(case when receipt_taxes.tax_id=1012 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Entre_Ríos,
	sum(case when receipt_taxes.tax_id=1013 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Formosa,
	sum(case when receipt_taxes.tax_id=1014 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Jujuy,
	sum(case when receipt_taxes.tax_id=1015 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_La_Pampa,
	sum(case when receipt_taxes.tax_id=1016 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_La_Rioja,
	sum(case when receipt_taxes.tax_id=1017 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Mendoza,
	sum(case when receipt_taxes.tax_id=1018 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Misiones,
	sum(case when receipt_taxes.tax_id=1019 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Neuquén,
	sum(case when receipt_taxes.tax_id=1020 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Pascua_Lama,
	sum(case when receipt_taxes.tax_id=1021 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Río_Negro,
	sum(case when receipt_taxes.tax_id=1022 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Salta,
	sum(case when receipt_taxes.tax_id=1023 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_San_Juan,
	sum(case when receipt_taxes.tax_id=1024 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_San_Luis,
	sum(case when receipt_taxes.tax_id=1025 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santa_Cruz,
	sum(case when receipt_taxes.tax_id=1026 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santa_Fe,
	sum(case when receipt_taxes.tax_id=1027 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Santiago_del_Estero,
	sum(case when receipt_taxes.tax_id=1028 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Tierra_del_Fuego,
	sum(case when receipt_taxes.tax_id=1029 then (receipt_taxes.amount) else 0 end) as Perc_IIBB_Tucuman

from receipts
left join periods on receipts.period_id = periods.id
left join receipt_types on receipts.type_receipt_id = receipt_types.id
left join retention_types on receipts.retention_type_id =retention_types.id
left join status on receipts.status_id = status.id
left join persons on receipts.person_id_relationed = persons.id
left join receipt_taxes on receipts.id = receipt_taxes.receipt_id
left join zones on receipts.zone_id = zones.id
left join activities on receipts.activity_id = activities.id
left join types on receipts.type_id = types.id
left join system_taxes on  receipt_taxes.tax_id = system_taxes.id
left join other_taxes on  receipt_taxes.tax_id = other_taxes.id

where 	receipts.type_id LIKE '%'
	and periods.id LIKE '%'
    and periods.code LIKE '%'
	and receipts.person_id_owner LIKE '%'
	and periods.date_from LIKE '%'
    and periods.date_to LIKE '%'

group by  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
order by Periodo,document, code_ticket,number