pr_autocomplete

CREATE PROCEDURE [dbo].[pr_autocomplete] @TYPE VARCHAR(30), @Advogado VARCHAR(10) = NULL, @Cliente INT = NULL, @Assunto INT = NULL
AS
BEGIN



SET NOCOUNT ON

DECLARE @Idioma VARCHAR(1)

IF @TYPE = 'HonrDescritivo'
BEGIN
    SELECT
        @Idioma = CASE WHEN Assuntos.MoedaDig IN ('US$','€') THEN 'I' ELSE 'P' END
    FROM
        dbo.BMD03  AS Assuntos
    WHERE
        Assunto = @Assunto

    SET @Idioma = IsNUll(@Idioma, 'P')

    --6 - Secretarias
    --9 - Financeiro
    SELECT
        REPLACE(Descritivo, '[ADVOGADO]', IsNull((SELECT DISTINCT DsAdvogado FROM dbo.BMD01 INNER JOIN tb_aux_equipe_advogado ON tb_aux_equipe_advogado.Advogado = BMD01.Advogado WHERE BMD01.Advogado = @Advogado AND tb_aux_equipe_advogado.Equipe NOT IN ('6','9') ), '[ADVOGADO]')) AS Descritivo
        --cast(DsAssunto as varchar(100)) collate SQL_Latin1_General_Cp1251_CS_AS as DsAssunto
    FROM
        dbo.tb_Descritivo
    WHERE
        tb_Descritivo.Area IN (SELECT Equipe FROM dbo.tb_aux_equipe_advogado WHERE Advogado = @Advogado OR Advogado IN (SELECT Advogado FROM dbo.tb_aux_Colaborador WHERE Secretaria = @Advogado))
    AND
        Tipo = 'H'
    AND
        Idioma = @Idioma
END


IF @TYPE = 'DespDescritivo'
BEGIN

    SELECT
        @Idioma = CASE WHEN Assuntos.MoedaDig IN ('US$','€') THEN 'I' ELSE 'P' END
    FROM
        dbo.BMD03  AS Assuntos
    WHERE
        Assunto = @Assunto

    SET @Idioma = IsNUll(@Idioma, 'P')

    SELECT
        REPLACE(Descritivo, '[ADVOGADO]', IsNull((SELECT DISTINCT DsAdvogado FROM dbo.BMD01 INNER JOIN tb_aux_equipe_advogado ON tb_aux_equipe_advogado.Advogado = BMD01.Advogado WHERE BMD01.Advogado = @Advogado AND tb_aux_equipe_advogado.Equipe NOT IN ('6','9') ), '[ADVOGADO]')) AS Descritivo
        --cast(DsAssunto as varchar(100)) collate SQL_Latin1_General_Cp1251_CS_AS as DsAssunto
    FROM
        dbo.tb_Descritivo
    WHERE
        tb_Descritivo.Area IN (SELECT Equipe FROM dbo.tb_aux_equipe_advogado WHERE Advogado = @Advogado OR Advogado IN (SELECT Advogado FROM dbo.tb_aux_Colaborador WHERE Secretaria = @Advogado))
    AND
        Tipo = 'D'
    AND
        Idioma = @Idioma
    ORDER BY
        Descritivo

END

IF @TYPE = 'Cliente'
BEGIN

        SELECT
            Cliente
            , CAST(DsCliente AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsCliente
            , CAST(Fantasia AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS Fantasia
        FROM
            dbo.BMD02
        WHERE
            Desativado = 0
        AND
            Ordemfat >= 0
        ORDER BY DsCliente
END

IF @TYPE = 'Assunto'
BEGIN
        SELECT
            Assunto
            , CAST(DsAssunto AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAssunto
        FROM
            dbo.BMD03 AS Assuntos
        WHERE
            Assuntos.Desativado = 0
        AND
            Assuntos.LancHonor = 1
        AND
            Assuntos.Cliente = @Cliente
        ORDER BY DsAssunto
END

IF @TYPE = 'AssuntoD'
BEGIN
        SELECT
            Assunto
            , CAST(DsAssunto AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAssunto
        FROM
            dbo.BMD03 AS Assuntos
        WHERE
            Assuntos.Desativado = 0
        AND
            Assuntos.LancDesp = 1
        AND
            Assuntos.Cliente = @Cliente
        ORDER BY DsAssunto
END

IF @TYPE = 'Categoria'
BEGIN

        SELECT
            TipoDesp, DsTipoDesp
        FROM
            dbo.BMD22
        ORDER BY DsTipoDesp

END

IF @TYPE = 'AssuntoDetalhe'
BEGIN
        SELECT
              CASE WHEN Assuntos.MoedaDig IN ('US$','€') THEN 'I' ELSE 'P' END AS Linguagem
            --, IsNull(Obs, '') as Obs
            --, IsNull(Convert(varchar(250),Clientes.DsClienteEtq) + '<BR>','') + IsNull(Convert(varchar(250),Assuntos.DescrCompFR), '') as Obs
            , IsNull(CONVERT(VARCHAR(250),Clientes.DsClienteEtq) + CASE WHEN CONVERT(VARCHAR(250),Clientes.DsClienteEtq) <> '' THEN '<BR>' ELSE '' END,'') + IsNull(CONVERT(VARCHAR(250),Assuntos.DescrCompFR), '') AS Obs
            , CONVERT(VARCHAR(15), Assuntos.DtUltDesp, 103) AS DtUltDesp
        FROM
            dbo.BMD03 AS Assuntos
        INNER JOIN
            BMD02 AS Clientes
        ON
            Clientes.Cliente = Assuntos.Cliente
        WHERE
            Assuntos.Desativado = 0
        AND
            Assuntos.Assunto = @Assunto
END

IF @TYPE = 'Colaborador'
BEGIN

        SELECT
            Advogado
            , CAST(DsAdvogado AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAdvogado
        FROM
            dbo.BMD01
        WHERE
            (Desativado = 0 OR dateDiff(DAY, Desligamento, getdate()) < 180)
        AND
            Tipoadv NOT IN ('E', 'A', 'S')
        --Or
        --  Advogado = @Advogado
        ORDER  BY
            DsAdvogado
END


IF @TYPE = 'Advogado'
BEGIN
        SELECT
            Advogado
            , CAST(DsAdvogado AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAdvogado
        FROM
            dbo.BMD01
        WHERE
            Desativado = 0
        --And
        --  Tipoadv in ('E', 'A', 'S')
        --Or
        --  Advogado = @Advogado
        ORDER  BY
            DsAdvogado
END

IF @TYPE = 'Secretaria'
BEGIN
        SELECT
            Advogado
            , CAST(DsAdvogado AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAdvogado
        FROM
            dbo.BMD01
        WHERE
            Desativado = 0
        AND
            Tipoadv  = 'T'
        --Or
        --  Advogado = @Advogado
        ORDER  BY
            DsAdvogado
END

IF @TYPE = 'AdvogadoEquipe'
BEGIN
        SELECT
            DISTINCT BMD01.Advogado
            , CAST(DsAdvogado AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAdvogado
        FROM
            dbo.BMD01
        LEFT JOIN
            dbo.tb_aux_equipe_advogado
        ON
            tb_aux_equipe_advogado.Advogado = BMD01.Advogado
        WHERE
            BMD01.Desativado = 0
        AND
            ((@Advogado = 'TGO' AND BMD01.Tipoadv IN ('E', 'A', 'S', 'D'))
        OR
            (BMD01.Tipoadv IN ('E', 'A', 'S')
        AND
            tb_aux_equipe_advogado.Equipe IN (SELECT Equipe FROM tb_aux_equipe_advogado WHERE Advogado = @Advogado)))
END
IF @TYPE = 'TipoHonorario'
BEGIN
        SELECT
            TipoHonorarioID, TipoHonorarioDesc
        FROM
            tb_TipoHonorario
        ORDER BY
            TipoHonorarioDesc
END
IF @TYPE = 'tipojustificativa'
BEGIN
        SELECT
            JustificativaID, DsJustificativa
        FROM
            dbo.tb_Justificativas
        ORDER BY
            DsJustificativa

END


IF @TYPE = 'Equipe'
BEGIN
        SELECT
            Equipe, BMD68.DsArea AS DsEquipe
        FROM
            tb_aux_equipe_advogado
        INNER JOIN
            BMD68
        ON
            BMD68.Area = tb_aux_equipe_advogado.Equipe
        WHERE
            Advogado = @Advogado
END
IF @TYPE = 'AdvogadoSecretaria'
BEGIN
        SELECT
            DISTINCT BMD01.Advogado, DsAdvogado
        FROM
            dbo.BMD01
        WHERE
            BMD01.Desativado = 0
        AND
            (BMD01.Tipoadv IN ('S', 'A', 'E')
-- AND BMD01.Classe like '%Senior%'
        OR
            BMD01.Advogado = @Advogado)
END

IF @TYPE = 'Semestre'
BEGIN

    DECLARE @dt datetime, @dtEnd datetime


    SET @dtEnd = DateAdd(MONTH, datediff(MONTH, 0, getdate()) - ((datediff(MONTH, 0, getdate()) ) % 6), 0)
    SET @dt = DateAdd(MONTH, -30, @dtEnd)

    SELECT
        NUMBER,
          YEAR(dateadd(MONTH, NUMBER, @dt)) * 100 + CASE WHEN MONTH(dateadd(MONTH, NUMBER, @dt)) > 6 THEN 2 ELSE 1 END AS Semestre
        , CONVERT(VARCHAR(5), YEAR(dateadd(MONTH, NUMBER, @dt))) + '-' + CONVERT(VARCHAR(5), CASE WHEN MONTH(dateadd(MONTH, NUMBER, @dt)) > 6 THEN 2 ELSE 1 END) + 'o Semestre' AS DsSemestre
        , SemestreAtual.Semestre AS SemestreCorrente
    FROM
        (SELECT DISTINCT NUMBER * 6 AS NUMBER FROM master.dbo.spt_values
         WHERE name IS NULL
        ) n
    LEFT JOIN
        (SELECT MIN(Semestre) AS Semestre FROM dbo.tb_aux_FechamentoSemestre WHERE dateAdd(DAY, 1, DtUltFaturamento) > GetDate()) AS SemestreAtual
    ON
        SemestreAtual.Semestre = YEAR(dateadd(MONTH, NUMBER, @dt)) * 100 + CASE WHEN MONTH(dateadd(MONTH, NUMBER, @dt)) > 6 THEN 2 ELSE 1 END
    WHERE dateadd(MONTH, NUMBER, @dt) <= @dtEnd
    ORDER BY
        SemestreCorrente DESC, NUMBER DESC

END
IF @TYPE = 'AdvogadoLancamento'
BEGIN
        SELECT
             DISTINCT
              BMD01.Advogado
            , CAST(DsAdvogado AS VARCHAR(100)) COLLATE SQL_Latin1_General_Cp1251_CS_AS AS DsAdvogado
            , CASE WHEN tb_aux_equipe_advogado.Equipe IN (SELECT Equipe FROM tb_aux_equipe_advogado WHERE Advogado = @Advogado) THEN 0 ELSE 1 END AS Ordena
        FROM
            dbo.BMD01
        LEFT JOIN
            dbo.tb_aux_equipe_advogado
        ON
            tb_aux_equipe_advogado.Advogado = BMD01.Advogado
        WHERE
            BMD01.Desativado = 0
        AND
            BMD01.Tipoadv IN ('E', 'A', 'S')
END


END

GO