-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_processoFinalizado`(IN mantenedor int(11))
BEGIN

select
        `p`.`protocolo` AS `protocolo`,
        `p`.`id` AS `idProcesso`,
        `p`.`der` AS `der`,
        `p`.`data_habilitacao` AS `dhab`,
        `p`.`id_usuario` AS `idUsuario`,
p.mantenedor as mantenedor,
        `ap`.`id_andamento` AS `codAndamento`,
        `ap`.`data` AS `dataAndamento`,
        `ap`.`observacao` AS `observacao`,
        `ap`.`id_usuario` AS `idUsuarioEvento`,
        `u`.`nome` AS `nome`,
        `u`.`matricula` AS `matricula`,
        `u`.`lotacao` AS `lotacao`,
        (to_days(`ap`.`data`) - to_days(`p`.`der`)) AS `tempoConclusao`,
        (to_days(`ap`.`data`) - to_days(`p`.`data_habilitacao`)) AS `tempoDecisao`,
        `a`.`descricao` AS `desAndamento`
    from
        (((`processo` `p`
        join `andamento_processo` `ap` ON ((`p`.`id` = `ap`.`id_processo`)))
        join `usuario` `u` ON ((`u`.`id` = `p`.`id_usuario`)))
        join `andamento` `a` ON ((`ap`.`id_andamento` = `a`.`id`)))
    where
        (`p`.`bol_finalizado` = 'S')
  and p.mantenedor  = mantenedor
    order by `p`.`der` , `p`.`data_habilitacao`;


END

CALL sp_processoFinalizado(2225566) where tempoDecisao < 30;