-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GNN_GetParametrizacionUsuarios]
    -- Add the parameters for the stored procedure here
    @Username nvarchar(max) = null,
    @NonBlocked int = 0 --1 para traer los no bloqueados
AS
BEGIN

/*
if(@Username is null)
begin
    SELECT    u.Username
            ,u.FirstName
            ,u.LastName
            ,dbo.GNN_RolesXUsuarios(u.Username) as Roles
            ,'False' as RamoGenerales
            , 'False' as RamoVida
            , 'False' as RamoAutos
            ,'False' as TipoSolicitudNegocioNuevo
            , 'False' as TipoSolicitudAnexos
            , 'False' as TipoSolicitudCorreciones
            , 'False' as TipoSolicitudRenovaciones
            , 'False' as PrioridadUrgente
            , 'False' as PrioridadNormal
            ,'False' as SolicitudMultipleSi
            , 'False' as SolicitudMultipleNo
            ,'False' as TiposAnexoCorrecion
            , 'False' as TiposAnexoActualizacion
            ,'False' as TiposAnexoModificacionConValor
            ,'False' as TiposAnexoModificacionSinValor
            ,'False' as TiposAnexoCancelacionAnulacion
            , 'False' as TiposCorrecionExterna
            ,'False' as TiposCorrecionInterna
FROM         DNN_Users u
Inner Join DNN_UserRoles UR
ON u.UserID = UR.UserID
 Inner Join DNN_Roles R
            ON UR.RoleID = R.RoleID
            AND R.RoleName = 'CentroExpedicion'

order by username
end
else
begin
*/
    SELECT    u.Username
            ,u.FirstName
            ,u.LastName
            ,dbo.GNN_RolesXUsuarios(u.Username) as Roles
            ,Coalesce(Min(Case PropertyName When 'RamoGenerales' Then PropertyValue  End), 'False') as RamoGenerales
            ,Coalesce(Min(Case PropertyName When 'RamoVida' Then PropertyValue End), 'False') as RamoVida
            ,Coalesce(Min(Case PropertyName When 'RamoAutos' Then PropertyValue End), 'False') as RamoAutos
            ,Coalesce(Min(Case PropertyName When 'TipoSolicitudNegocioNuevo' Then PropertyValue End), 'False') as TipoSolicitudNegocioNuevo
            ,Coalesce(Min(Case PropertyName When 'TipoSolicitudAnexos' Then PropertyValue End), 'False') as TipoSolicitudAnexos
            ,Coalesce(Min(Case PropertyName When 'TipoSolicitudCorreciones' Then PropertyValue End), 'False') as TipoSolicitudCorreciones
            ,Coalesce(Min(Case PropertyName When 'TipoSolicitudRenovaciones' Then PropertyValue End), 'False') as TipoSolicitudRenovaciones
            ,Coalesce(Min(Case PropertyName When 'PrioridadUrgente' Then PropertyValue End), 'False') as PrioridadUrgente
            ,Coalesce(Min(Case PropertyName When 'PrioridadNormal' Then PropertyValue End), 'False') as PrioridadNormal
            ,Coalesce(Min(Case PropertyName When 'SolicitudMultipleSi' Then PropertyValue End), 'False') as SolicitudMultipleSi
            ,Coalesce(Min(Case PropertyName When 'SolicitudMultipleNo' Then PropertyValue End), 'False') as SolicitudMultipleNo
            ,Coalesce(Min(Case PropertyName When 'TiposAnexoCorrecion' Then PropertyValue End), 'False') as TiposAnexoCorrecion
            ,Coalesce(Min(Case PropertyName When 'TiposAnexoActualizacion' Then PropertyValue End), 'False') as TiposAnexoActualizacion
            ,Coalesce(Min(Case PropertyName When 'TiposAnexoModificacionConValor' Then PropertyValue End), 'False') as TiposAnexoModificacionConValor
            ,Coalesce(Min(Case PropertyName When 'TiposAnexoModificacionSinValor' Then PropertyValue End), 'False') as TiposAnexoModificacionSinValor
            ,Coalesce(Min(Case PropertyName When 'TiposAnexoCancelacionAnulacion' Then PropertyValue End), 'False') as TiposAnexoCancelacionAnulacion
            ,Coalesce(Min(Case PropertyName When 'TiposCorrecionExterna' Then PropertyValue End), 'False') as TiposCorrecionExterna
            ,Coalesce(Min(Case PropertyName When 'TiposCorrecionInterna' Then PropertyValue End), 'False') as TiposCorrecionInterna

FROM         DNN_Users u INNER JOIN
                      DNN_UserProfile ON u.UserID = DNN_UserProfile.UserID INNER JOIN
                      DNN_ProfilePropertyDefinition ON DNN_UserProfile.PropertyDefinitionID = DNN_ProfilePropertyDefinition.PropertyDefinitionID

Inner Join DNN_UserRoles UR
ON u.UserID = UR.UserID
                      Inner Join DNN_Roles R
            ON UR.RoleID = R.RoleID
            AND R.RoleName = 'CentroExpedicion'
Inner Join vw_aspnet_MembershipUsers m
ON u.UserName = m.UserName
AND (m.IsLockedOut = 0 or @NonBlocked = 0)
AND (lower(IsNull(dbo.GetPropertyValueForUser(u.UserID, 'TieneInstancias'), 'false')) = 'false' or @NonBlocked = 0)
where u.UserName = @Username or @Username is null


group by u.Username,
            u.FirstName,
            u.LastName

--end
END