set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_sel_cat_resumenActividad_m]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[a_sel_cat_resumenActividad_m]
GO
CREATE PROCEDURE a_sel_cat_resumenActividad_m
@p_id_catalogo as int = 0,
@p_eid as varchar (50)=''
AS
BEGIN
select 'Reportadas'as descripcion, convert(varchar, count(id)) as dato
from(select id_reporteTecnico,max(id) as id
from [ort_programacion]
where [id_catalogo.actividad]= @p_id_catalogo and active=1 and eid like @p_eid+'%' group by id_reporteTecnico) A

select 'Planeadas'as descripcion,  convert(varchar,count(doc_itemDocumento.id)) as dato
from doc_itemDocumento inner join [doc_documento.ot]  as ot on ot.id=doc_itemDocumento.id_documento
where id_catalogo=@p_id_catalogo and doc_itemDocumento.active=1 and doc_itemDocumento.eid  like @p_eid+'%' and cantidadPlaneado>0

select 'Horas ejecutadas' as descripcion, convert(varchar, sum(duracion)) as dato
from [ort_programacion]
where [id_catalogo.actividad]= @p_id_catalogo and active=1 and  eid like @p_eid+'%'

select 'Uso en rutinas' as descripcion ,count(id) as 	dato
from cat_detalleTempario
where id_catalogo= @p_id_catalogo and active=1 and  eid like @p_eid+'%'

select 'Veces cotizada' as descripcion ,  convert(varchar,count(doc_itemDocumento.id)) as 	dato
from doc_itemDocumento inner join [doc_documento.cotizacion]  as cotizacion on cotizacion.id=doc_itemDocumento.id_documento
where id_documento>0 and id_catalogo=@p_id_catalogo and doc_itemDocumento.active=1 and  doc_itemDocumento.eid like @p_eid+'%'

select 'Promedio cotizado' as descripcion ,  convert(varchar,case when (count(doc_itemDocumento.id)) = 0 then 0 else sum(doc_itemDocumento.valorUnitarioPlaneado*doc_itemDocumento.cantidadPlaneado)/ count(doc_itemDocumento.id) end )as 	dato
from doc_itemDocumento inner join [doc_documento.cotizacion]  as cotizacion 	on cotizacion.id=doc_itemDocumento.id_documento where id_documento>0 and id_catalogo=@p_id_catalogo and doc_itemDocumento.active=1 and  doc_itemDocumento.eid like @p_eid+'%'

END
GO
