CREATE DEFINER=`root`@`localhost` PROCEDURE `SPExportarComprobantes`(person_id int,operaciones varchar(50), desde date, hasta date)
BEGIN
  
  declare parte1,parte2,parte3,union_query TEXT DEFAULT '';
  declare done int default 0;
  declare tax_id int;
  declare rotulo varchar(50);
  declare monto varchar(50);
  declare porcentaje decimal(20,2) default 0.00;

  DECLARE currents CURSOR FOR
    SELECT id, name as nombre, percent_iva, 'amount' as mount_taxAmount FROM system_taxes 
    union all
    SELECT id, IF(percent_iva>0,percent_iva,'') as nombre, percent_iva, 'tax_amount' as mount_taxAmount 
    FROM system_taxes 
    where percent_iva>0
    union all
    SELECT id,name,null as percent_iva,'amount' as mount_taxAmount  FROM other_taxes

    order by percent_iva desc,nombre
    ;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
  OPEN currents;

    REPEAT
      FETCH currents INTO tax_id, rotulo, porcentaje,monto;

        -- este if tiene que estar sino agrega un registro mas
        IF NOT done THEN

          SET parte2 = concat(parte2,"
            ((CASE
                  WHEN (`receipt_types`.`credit` = 1) THEN -(1)
                  ELSE 1
              END) * SUM((CASE
                  WHEN (`receipt_taxes`.`tax_id` =", tax_id," ) THEN `receipt_taxes`.
                  ", monto ,"
                  ELSE 0
              END))) AS `", rotulo,"`,");

          
        END IF; -- Fin If Done

    UNTIL done END REPEAT; -- fin del loop

  CLOSE currents;


  SET parte2 = TRIM(TRAILING ',' FROM parte2);
  set parte1 = "SELECT 
        `receipts`.`id` AS `IdComprobante`,
        `receipts`.`type_id` AS `IdTipoComprobante`,
        `persons`.`id` AS `IdProveedorCliente`,
        `receipts`.`person_id_owner` AS `IdEmpresa`,
        `b`.`document` AS `DocumentoEmpresa`,
        `b`.`field_name1` AS `NombreEmpresa`,
        `p`.`id` AS `IdPeriodo`,
        `p`.`date_from` AS `Desde`,
        `p`.`date_to` AS `Hasta`,
        `p`.`code` AS `Periodo`,
        `p`.`year` AS `anioPeriodo`,
        `p`.`period_number` AS `mesPeriodo`,
        `types`.`id` AS `IdOperacion`,
        `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`.`receipt_date` AS `FechaComprobante`,
        `receipts`.`reference` AS `Referencia`,
        `retention_types`.`name` AS `TipoDeRetencion`,
        `receipt_types`.`name` AS `TipoComprobante`,
        `receipts`.`code_ticket` AS `Suc`,
        `receipts`.`number` AS `Numero`,
        (CASE
            WHEN (`receipt_types`.`credit` = 1) THEN -(`receipts`.`amount`)
            ELSE `receipts`.`amount`
        END) AS `Total`,
        ";
  set parte3 = concat(" FROM
        ((((((((((((`receipts`
        LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
        LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`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` in ",operaciones,"
    and `receipts`.`person_id_owner` = ",person_id,"
    and `p`.`date_from` between  '",desde,"' and '",hasta,"'
    GROUP BY `receipts`.`id` , `receipts`.`type_id` , `persons`.`id` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1` , `p`.`id` , `p`.`date_from` , `p`.`date_to` , `p`.`code` , `p`.`year` , `p`.`period_number` , `types`.`id` , `p`.`code` , `types`.`name` , `persons`.`field_name1` , `persons`.`document_type` , `persons`.`document` , `zones`.`name` , `status`.`name` , `activities`.`name` , `receipts`.`receipt_date` , `receipts`.`reference` , `retention_types`.`name` , `receipt_types`.`name` , `receipts`.`code_ticket` , `receipts`.`number` , `receipts`.`amount`
    ORDER BY `p`.`code` , `persons`.`document` , `receipts`.`code_ticket` , `receipts`.`number`");
  set union_query = concat (parte1,parte2,parte3);
  
  set @queryfinal = union_query;

  PREPARE stmt1 FROM @queryfinal;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;

  END