Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
4| Name | Data Type | Length | Description |
|---|---|---|---|
| @p_accion | varchar | 40 | |
| @p_plataforma | varchar | -1 | |
| @p_idTeam | int | 4 | |
| @p_user_id | int | 4 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/* SP IV_panel_teams */
--IV_panel_teams 'PANEL_TEAMS', '47',null,null
--IV_panel_teams 'PANEL_TODOS', '47',null,null
CREATE OR ALTER PROCEDURE dbo.IV_panel_teams
@p_accion varchar(40),
@p_plataforma varchar(max),
@p_idTeam int = null,
@p_user_id int = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @country_code VARCHAR(2)
DECLARE @clave_anterior VARCHAR(80)
DECLARE @lstDocksOK VARCHAR(10)
/* DECLARACION DE VARIABLES PARA PREVENIR PARAMETER SNIFFING EN EL QUERY PLAN */
DECLARE @V_accion varchar(40) = @p_accion
DECLARE @V_user_id int = @p_user_id
DECLARE @V_idTeam int = @p_idTeam
DECLARE @V_plataforma varchar(max) = @p_plataforma
if (@V_idTeam = 0) set @V_idTeam = null
if (@V_plataforma is null)
begin
set @V_plataforma = '47'
end
if (@V_accion='PANEL_TEAMS')
BEGIN
if (@V_user_id is NOT null) /* SE BUSCA UN USUARIO EN PARTICULAR */
begin
select distinct informes.cliente, iPPAl.estado_informe as estado_padre,iPPAL.informe as infPadre, ln_contrapartes.contraparte,ln_demo.gestion, informes.informe,
informes.estado_informe,padrones.nombre_persona,
padrones.identificador,padrones.identificacion,LN_tareas.nombre_tarea,clientes.nombre_cliente,
LN_tipo_informes.nombre_tipo_informe,informes.fecha_comprometida,informes.ready_to_start,informes.pausa,
--informes.fecha_inicio,
iif(informes.proximo_llamado is not null and informes.proximo_llamado > isnull(informes.fecha_inicio,'1901-01-01'),informes.proximo_llamado,informes.fecha_inicio) as fecha_inicio,
LN_DEMO.idTeam,LN_Teams.nombre as nombre_team
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
inner join padrones on ln_contrapartes.id_padron=padrones.id_padron
inner join LN_tareas on informes.idTarea = LN_tareas.idTarea
inner join clientes on ln_contrapartes.cliente = clientes.cliente
inner join LN_tipo_informes on LN_tipo_informes.idTipoInforme =iPPAL.id_tipo_informe
inner join LN_informe_design on (LN_informe_design.idTipoInforme=LN_tipo_informes.idTipoInforme and informes.idTarea=LN_informe_design.idElem and LN_informe_design.activo=1)
inner join LN_Teams on LN_Teams.id_team=LN_DEMO.idteam
where informes.informe_padre IS NOT NULL
and informes.asignado = @V_user_id
and ln_contrapartes.activo=1
and convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and iPPAL.estado_informe not in ('K','N','F','X','C')
and informes.estado_informe not in ('K','N','F','X','C')
and LN_informe_design.tipo_elem='T'
and LN_informe_design.activo=1
--and ln_demo.idTeam=@V_idTeam
and iif(@V_idTeam is null, 1, iif(ln_demo.idTeam=@V_idTeam,1,0)) = 1
and LN_contrapartes.servicio_contratado = @V_plataforma
order by informes.fecha_comprometida asc
end
if (@V_user_id is null)
begin
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,
totales.total_tareas,
isnull(ready.ready2start,0) as ready2start,
isnull(proceso.proceso,0) as proceso,
isnull(pausa.pausa,0) as pausa,
ln_demo.idTeam,
LN_Teams.nombre as nombre_team,
LN_Team_Members.activo
--,informes.informe as tarea
from users
inner join informes on (users.user_id = informes.asignado and informes.informe_padre is not null and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F'))
INNER JOIN LN_DEMO ON (INFORMES.informe_padre=LN_DEMO.INFORME)
inner join LN_Team_Members on (LN_Team_Members.id_team = LN_demo.idteam and LN_Team_Members.activo=1)
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
/*inner join LN_Teams on LN_Teams.id_team=totales.idteam*/
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join
(
select COUNT(*) as total_tareas,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)
) totales ON (users.user_id = totales.asignado and LN_Team_Members.activo=1 and LN_Team_Members.id_team=totales.idTeam)
left join
(select COUNT(*) as ready2start,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) ready ON
(users.user_id = ready.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=ready.idTeam)
left join
(select COUNT(*) as proceso,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) proceso ON
(users.user_id = proceso.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=proceso.idTeam)
left join
(select COUNT(*) as pausa,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) pausa ON
(users.user_id = pausa.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=pausa.idTeam)
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
and LN_Teams.activo=1
and LN_Team_Members.activo=1
order by ln_demo.idTeam asc, users.first_name, users.last_name
end
if (@V_user_id is null and @V_idTeam=-10)
BEGIN
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,totales.idTeam
,LN_Teams.nombre as nombre_team
from users inner join informes on users.user_id = informes.asignado
/*
inner join ln_familias on informes.cliente=ln_familias.cliente
inner join clientes_productos on clientes_productos.cliente=ln_familias.cliente
inner join LN_Familia_teams on ln_familias.familia = LN_Familia_teams.familia
inner join LN_Teams on LN_Teams.id_team=LN_Familia_teams.id_team
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
*/
inner join
(
select COUNT(*) as total_tareas,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)
/*select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
group by informes.asignado
*/) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) pausa ON users.user_id = pausa.asignado
inner join LN_Teams on LN_Teams.id_team=totales.idteam
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
/*
inner join ln_familias on informes.cliente=ln_familias.cliente
inner join clientes_productos on clientes_productos.cliente=ln_familias.cliente
inner join LN_Familia_teams on ln_familias.familia = LN_Familia_teams.familia
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
*/
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
/*and LN_Teams.servicio_contratado=10*/
and LN_Teams.activo=1
and LN_Team_Members.activo=1
/*and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)*/
/* and ln_familias.visible=1
and LN_Teams.servicio_contratado=10
and clientes_productos.id_producto=10
and clientes_productos.activo=1
and LN_Team_Members.activo=1
*/
--and isnull(totales.total_tareas,0) > 0
order by totales.idteam asc, users.first_name, users.last_name
END
if (@V_user_id is null and 1=0) /*SI NO SE ESPCIFICA UN USUARIO EN PARTICULAR */
begin
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,1 as idTeam,'TEAM 1' as nombre_team
from users inner join informes on users.user_id = informes.asignado
left join
(select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.pausa=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) pausa ON users.user_id = pausa.asignado
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)
/*
UNION
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,2 as idTeam,'TEAM 2' as nombre_team
from users inner join informes on users.user_id = informes.asignado
left join
(select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
group by informes.asignado) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
group by informes.asignado) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.estado_informe='E' and informes.pausa=0
group by informes.asignado) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.pausa=1
group by informes.asignado) pausa ON users.user_id = pausa.asignado
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)*/
order by users.first_name, users.last_name
end
END
if (@V_accion='PANEL_TODOS')
BEGIN
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,
totales.total_tareas,
isnull(ready.ready2start,0) as ready2start,
isnull(proceso.proceso,0) as proceso,
isnull(pausa.pausa,0) as pausa,
LN_Team_Members.activo
from users
inner join informes on (users.user_id = informes.asignado and informes.informe_padre is not null and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F'))
INNER JOIN LN_DEMO ON (INFORMES.informe_padre=LN_DEMO.INFORME)
inner join LN_Team_Members on (LN_Team_Members.id_team = LN_demo.idteam and LN_Team_Members.activo=1)
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join
(
select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) totales ON (users.user_id = totales.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) ready ON (users.user_id = ready.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) proceso ON (users.user_id = proceso.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) pausa ON (users.user_id = pausa.asignado and LN_Team_Members.activo=1)
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
and LN_Teams.activo=1
and LN_Team_Members.activo=1
order by users.first_name, users.last_name
END
END
GO
GO
/* SP IV_panel_teams */
--IV_panel_teams 'PANEL_TEAMS', '47',null,null
--IV_panel_teams 'PANEL_TODOS', '47',null,null
CREATE OR ALTER PROCEDURE dbo.IV_panel_teams
@p_accion varchar(40),
@p_plataforma varchar(max),
@p_idTeam int = null,
@p_user_id int = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @country_code VARCHAR(2)
DECLARE @clave_anterior VARCHAR(80)
DECLARE @lstDocksOK VARCHAR(10)
/* DECLARACION DE VARIABLES PARA PREVENIR PARAMETER SNIFFING EN EL QUERY PLAN */
DECLARE @V_accion varchar(40) = @p_accion
DECLARE @V_user_id int = @p_user_id
DECLARE @V_idTeam int = @p_idTeam
DECLARE @V_plataforma varchar(max) = @p_plataforma
if (@V_idTeam = 0) set @V_idTeam = null
if (@V_plataforma is null)
begin
set @V_plataforma = '47'
end
if (@V_accion='PANEL_TEAMS')
BEGIN
if (@V_user_id is NOT null) /* SE BUSCA UN USUARIO EN PARTICULAR */
begin
select distinct informes.cliente, iPPAl.estado_informe as estado_padre,iPPAL.informe as infPadre, ln_contrapartes.contraparte,ln_demo.gestion, informes.informe,
informes.estado_informe,padrones.nombre_persona,
padrones.identificador,padrones.identificacion,LN_tareas.nombre_tarea,clientes.nombre_cliente,
LN_tipo_informes.nombre_tipo_informe,informes.fecha_comprometida,informes.ready_to_start,informes.pausa,
--informes.fecha_inicio,
iif(informes.proximo_llamado is not null and informes.proximo_llamado > isnull(informes.fecha_inicio,'1901-01-01'),informes.proximo_llamado,informes.fecha_inicio) as fecha_inicio,
LN_DEMO.idTeam,LN_Teams.nombre as nombre_team
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
inner join padrones on ln_contrapartes.id_padron=padrones.id_padron
inner join LN_tareas on informes.idTarea = LN_tareas.idTarea
inner join clientes on ln_contrapartes.cliente = clientes.cliente
inner join LN_tipo_informes on LN_tipo_informes.idTipoInforme =iPPAL.id_tipo_informe
inner join LN_informe_design on (LN_informe_design.idTipoInforme=LN_tipo_informes.idTipoInforme and informes.idTarea=LN_informe_design.idElem and LN_informe_design.activo=1)
inner join LN_Teams on LN_Teams.id_team=LN_DEMO.idteam
where informes.informe_padre IS NOT NULL
and informes.asignado = @V_user_id
and ln_contrapartes.activo=1
and convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and iPPAL.estado_informe not in ('K','N','F','X','C')
and informes.estado_informe not in ('K','N','F','X','C')
and LN_informe_design.tipo_elem='T'
and LN_informe_design.activo=1
--and ln_demo.idTeam=@V_idTeam
and iif(@V_idTeam is null, 1, iif(ln_demo.idTeam=@V_idTeam,1,0)) = 1
and LN_contrapartes.servicio_contratado = @V_plataforma
order by informes.fecha_comprometida asc
end
if (@V_user_id is null)
begin
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,
totales.total_tareas,
isnull(ready.ready2start,0) as ready2start,
isnull(proceso.proceso,0) as proceso,
isnull(pausa.pausa,0) as pausa,
ln_demo.idTeam,
LN_Teams.nombre as nombre_team,
LN_Team_Members.activo
--,informes.informe as tarea
from users
inner join informes on (users.user_id = informes.asignado and informes.informe_padre is not null and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F'))
INNER JOIN LN_DEMO ON (INFORMES.informe_padre=LN_DEMO.INFORME)
inner join LN_Team_Members on (LN_Team_Members.id_team = LN_demo.idteam and LN_Team_Members.activo=1)
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
/*inner join LN_Teams on LN_Teams.id_team=totales.idteam*/
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join
(
select COUNT(*) as total_tareas,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)
) totales ON (users.user_id = totales.asignado and LN_Team_Members.activo=1 and LN_Team_Members.id_team=totales.idTeam)
left join
(select COUNT(*) as ready2start,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) ready ON
(users.user_id = ready.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=ready.idTeam)
left join
(select COUNT(*) as proceso,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) proceso ON
(users.user_id = proceso.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=proceso.idTeam)
left join
(select COUNT(*) as pausa,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) pausa ON
(users.user_id = pausa.asignado and LN_Team_Members.activo=1 and LN_Teams.id_team=pausa.idTeam)
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
and LN_Teams.activo=1
and LN_Team_Members.activo=1
order by ln_demo.idTeam asc, users.first_name, users.last_name
end
if (@V_user_id is null and @V_idTeam=-10)
BEGIN
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,totales.idTeam
,LN_Teams.nombre as nombre_team
from users inner join informes on users.user_id = informes.asignado
/*
inner join ln_familias on informes.cliente=ln_familias.cliente
inner join clientes_productos on clientes_productos.cliente=ln_familias.cliente
inner join LN_Familia_teams on ln_familias.familia = LN_Familia_teams.familia
inner join LN_Teams on LN_Teams.id_team=LN_Familia_teams.id_team
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
*/
inner join
(
select COUNT(*) as total_tareas,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)
/*select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
group by informes.asignado
*/) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado,isnull(LN_demo.idteam,1) as idTeam
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado,isnull(LN_demo.idteam,1)) pausa ON users.user_id = pausa.asignado
inner join LN_Teams on LN_Teams.id_team=totales.idteam
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
/*
inner join ln_familias on informes.cliente=ln_familias.cliente
inner join clientes_productos on clientes_productos.cliente=ln_familias.cliente
inner join LN_Familia_teams on ln_familias.familia = LN_Familia_teams.familia
inner join LN_Team_Members on LN_Team_Members.id_team=LN_Teams.id_team
*/
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
/*and LN_Teams.servicio_contratado=10*/
and LN_Teams.activo=1
and LN_Team_Members.activo=1
/*and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)*/
/* and ln_familias.visible=1
and LN_Teams.servicio_contratado=10
and clientes_productos.id_producto=10
and clientes_productos.activo=1
and LN_Team_Members.activo=1
*/
--and isnull(totales.total_tareas,0) > 0
order by totales.idteam asc, users.first_name, users.last_name
END
if (@V_user_id is null and 1=0) /*SI NO SE ESPCIFICA UN USUARIO EN PARTICULAR */
begin
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,1 as idTeam,'TEAM 1' as nombre_team
from users inner join informes on users.user_id = informes.asignado
left join
(select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.pausa=1
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado) pausa ON users.user_id = pausa.asignado
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)
/*
UNION
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,totales.total_tareas,ready.ready2start,proceso.proceso,pausa.pausa,2 as idTeam,'TEAM 2' as nombre_team
from users inner join informes on users.user_id = informes.asignado
left join
(select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
group by informes.asignado) totales ON users.user_id = totales.asignado
left join
(select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
group by informes.asignado) ready ON users.user_id = ready.asignado
left join
(select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.estado_informe='E' and informes.pausa=0
group by informes.asignado) proceso ON users.user_id = proceso.asignado
left join
(select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and informes.pausa=1
group by informes.asignado) pausa ON users.user_id = pausa.asignado
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and users.user_id in (select distinct user_id from LN_Team_Members where activo=1 and id_team=1)*/
order by users.first_name, users.last_name
end
END
if (@V_accion='PANEL_TODOS')
BEGIN
select distinct users.user_id, users.username, users.first_name,
users.last_name, users.email,
totales.total_tareas,
isnull(ready.ready2start,0) as ready2start,
isnull(proceso.proceso,0) as proceso,
isnull(pausa.pausa,0) as pausa,
LN_Team_Members.activo
from users
inner join informes on (users.user_id = informes.asignado and informes.informe_padre is not null and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F'))
INNER JOIN LN_DEMO ON (INFORMES.informe_padre=LN_DEMO.INFORME)
inner join LN_Team_Members on (LN_Team_Members.id_team = LN_demo.idteam and LN_Team_Members.activo=1)
inner join LN_Teams on ln_demo.idTeam=ln_teams.id_team
inner join clientes_productos on clientes_productos.cliente=informes.cliente
inner join
(
select COUNT(*) as total_tareas,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and informes.estado_informe NOT IN ('X', 'I', 'C', 'N', 'K','F')
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) totales ON (users.user_id = totales.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as ready2start,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and informes.ready_to_start=1 and informes.estado_informe='A'
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) ready ON (users.user_id = ready.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as proceso,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and informes.estado_informe='E' and informes.pausa=0
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) proceso ON (users.user_id = proceso.asignado and LN_Team_Members.activo=1)
left join
(
select COUNT(*) as pausa,informes.asignado
from informes
INNER JOIN LN_DEMO ON INFORMES.informe_padre=LN_DEMO.INFORME
INNER JOIN LN_CONTRAPARTES ON LN_DEMO.CONTRAPARTE = LN_CONTRAPARTES.CONTRAPARTE
-- agregado
INNER JOIN LN_gestiones ON LN_gestiones.gestion=LN_demo.gestion
--
--INNER JOIN dbo.DelimitedSplit8K('10', ',') Segmentado on LN_contrapartes.servicio_contratado = Segmentado.Item
-- se agrego and LN_contrapartes.servicio_contratado = @V_plataforma
inner join users on informes.asignado = users.user_id
inner join informes iPPAL on (informes.informe_padre = iPPAL.informe and iPPAL.estado_informe not in ('K','N','F','X','C'))
where
-- agregado
LN_gestiones.desde <= CONVERT(date, GETDATE())
AND LN_gestiones.hasta >= CONVERT(date, GETDATE())
--
AND informes.informe_padre IS NOT NULL
and ln_contrapartes.activo=1
and isnull(ln_demo.visible,0)=0
and LN_demo.idTeam is not null
and informes.pausa=1
and informes.estado_informe in ('E')
and LN_contrapartes.servicio_contratado = @V_plataforma
group by informes.asignado
) pausa ON (users.user_id = pausa.asignado and LN_Team_Members.activo=1)
where convert(varchar(100),users.preferences) like '%' + @V_plataforma+ '%'
and users.active_user = 'Y'
and clientes_productos.id_producto=@V_plataforma
and clientes_productos.activo=1
and LN_Teams.activo=1
and LN_Team_Members.activo=1
order by users.first_name, users.last_name
END
END
GO
Depends On
13Used By
No items found