set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_sel_cat_resumenRepuesto]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[a_sel_cat_resumenRepuesto]
GO
CREATE PROCEDURE a_sel_cat_resumenRepuesto
@p_id_catalogo as int
AS
BEGIN

select 'Veces Cotizado' 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

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

select 'Veces Usado' as descripcion ,convert(varchar,case when (sum(doc_itemDocumento.cantidadEjecutado))is null then 0 else sum(doc_itemDocumento.cantidadEjecutado) end ) 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

select 'Supercedidos' as descripcion ,convert(varchar,A.supercedido)+'/'+convert(varchar,B.reemplazo)+'/'+convert(varchar,C.supercedio) as dato
from (select count(id) as supercedido from cat_reemplazo 
where active=1 and id_tipoReemplazo=2 and [id_catalogo.repuesto]=@p_id_catalogo) A
,(select count(id) as reemplazo from cat_reemplazo
where active=1 and id_tipoReemplazo=1 and [id_catalogo.repuesto]=@p_id_catalogo) B
,(select count(id) as supercedio from cat_reemplazo 
where active=1 and id_tipoReemplazo=2 and [id_catalogo.repuesto_1]=@p_id_catalogo) C

select 'En contratos' as descripcion ,convert(varchar,count(id)) as dato
from cnt_contrato_catalogo
where id_catalogo=@p_id_catalogo

END
GO
