E10——计算品号的使用情况（最近五年）：连续X年在用

-- =============================================
-- Author:        <Author,,AARON>
-- Create date: <Create Date,,2023-07-19>
-- Description:    <Description,,计算品号的使用情况（最近五年）：连续X年在用>
-- =============================================
CREATE PROCEDURE SP_GET_Item_UsedInfo
(@pITEMCODE VARCHAR(50))
AS
BEGIN

    SET NOCOUNT ON;

    --临时表
    IF EXISTS
    (
        SELECT *
        FROM tempdb.dbo.sysobjects
        WHERE id = OBJECT_ID(N'tempdb.dbo.#ItemUsedInfo')
    )
        DROP TABLE #ItemUsedInfo

    CREATE TABLE #ItemUsedInfo
    (
        ItemCode VARCHAR(50),
        UsedInfo NVARCHAR(100), --最后更新为：连续X在用
        UsedYears INT,          --记录连续使用的年数
        FirstUsedYear INT,      --连续使用开始年份
        LastUsedYear INT        --最后使用年份
    )

    --临时表
    IF EXISTS
    (
        SELECT *
        FROM tempdb.dbo.sysobjects
        WHERE id = OBJECT_ID(N'tempdb.dbo.#ItemTrans')
    )
        DROP TABLE #ItemTrans

    CREATE TABLE #ItemTrans
    (
        ItemCode VARCHAR(50),
        yyyy INT,
        qty DECIMAL(16, 8)
    )

    --临时表
    IF EXISTS
    (
        SELECT *
        FROM tempdb.dbo.sysobjects
        WHERE id = OBJECT_ID(N'tempdb.dbo.#ItemTrans2')
    )
        DROP TABLE #ItemTrans2

    CREATE TABLE #ItemTrans2
    (
        ItemCode VARCHAR(50),
        yyyy INT,
        qty DECIMAL(16, 8)
    )


    INSERT INTO #ItemTrans
    (
        ItemCode,
        yyyy,
        qty
    )
    SELECT i.ITEM_CODE,
           YEAR(tl.TRANSACTION_DATE) yyyy,
           SUM(tl.INVENTORY_QTY) chuku_qty
    FROM dbo.TRANSACTION_LINE AS tl
        INNER JOIN dbo.ITEM AS i
            ON i.ITEM_BUSINESS_ID = tl.ITEM_ID
    WHERE tl.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
          AND
          (
              tl.SOURCE_ID_RTK = 'SALES_ISSUE.SALES_ISSUE_D'
              OR
              (
                  tl.SOURCE_ID_RTK = 'ISSUE_RECEIPT.ISSUE_RECEIPT_D'
                  AND tl.INVENTORY_QTY > 0
              )
              OR
              (
                  tl.SOURCE_ID_RTK = 'TRANSACTION_DOC.TRANSACTION_DOC_D'
                  AND SUBSTRING(tl.DOC_NO, 1, 4) IN ( '1109', '1113' )
              )
          )
          AND tl.STOCK_ACTION = -1
          AND tl.CATEGORY <> '1G'
          AND YEAR(tl.TRANSACTION_DATE) >= YEAR(GETDATE()) - 5
          AND
          (
              i.ITEM_CODE LIKE (@pITEMCODE + '%')
              OR ISNULL(@pITEMCODE, '') = ''
          )
    GROUP BY YEAR(tl.TRANSACTION_DATE),
             i.ITEM_CODE
    HAVING SUM(tl.INVENTORY_QTY) > 0
    ORDER BY i.ITEM_CODE,
             yyyy DESC



    INSERT INTO #ItemUsedInfo
    (
        ItemCode,
        UsedInfo,
        UsedYears,
        FirstUsedYear,
        LastUsedYear
    )
    SELECT DISTINCT
           i.ITEM_CODE,
           '',
           0,
           0,
           0
    FROM dbo.ITEM AS i
    WHERE (
              i.ITEM_CODE LIKE (@pITEMCODE + '%')
              OR ISNULL(@pITEMCODE, '') = ''
          )
    ORDER BY i.ITEM_CODE


    DECLARE @SYear INT,
            @Year INT

    SELECT @SYear = YEAR(GETDATE()) - 5,
           @Year = YEAR(GETDATE())
    WHILE @Year >= @SYear --从今年开始倒推
    BEGIN

        IF @Year = YEAR(GETDATE()) --今年，第一年
        BEGIN
            UPDATE iui
            SET iui.UsedYears = iui.UsedYears + 1,
                iui.FirstUsedYear = @Year,
                iui.LastUsedYear = @Year
            FROM #ItemUsedInfo AS iui
            WHERE iui.FirstUsedYear = 0
                  AND EXISTS
            (
                SELECT 1
                FROM #ItemTrans AS it
                WHERE it.ItemCode = iui.ItemCode
                      AND it.yyyy = @Year
                      AND it.qty > 0
            )
        END
        ELSE --非今年，倒推找今年的更新
        BEGIN
            UPDATE iui
            SET iui.UsedYears = iui.UsedYears + 1,
                iui.FirstUsedYear = @Year
            FROM #ItemUsedInfo AS iui
            WHERE iui.FirstUsedYear = (@Year + 1)
                  AND EXISTS
            (
                SELECT 1
                FROM #ItemTrans AS it
                WHERE it.ItemCode = iui.ItemCode
                      AND it.yyyy = @Year
                      AND it.qty > 0
            )
        END

        SELECT @Year = @Year - 1

    END

    UPDATE #ItemUsedInfo
    SET UsedInfo = ('连续' + CAST(UsedYears AS VARCHAR(5)) + '年在用（' + CAST(FirstUsedYear AS VARCHAR(5)) + '—'
                    + CAST(YEAR(GETDATE()) AS VARCHAR(5)) + '）'
                   )
    WHERE UsedYears > 0

    --无连续使用的，更新最后使用年份
    INSERT INTO #ItemTrans2
    (
        ItemCode,
        yyyy,
        qty
    )
    SELECT t2.ItemCode,
           t2.yyyy,
           t2.qty
    FROM
    (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY t.ItemCode ORDER BY t.yyyy DESC) seq
        FROM
        (SELECT * FROM #ItemTrans AS it) t
    ) t2
    WHERE t2.seq = 1

    --更新最后使用年份
    UPDATE iui
    SET iui.LastUsedYear = it.yyyy,
        iui.UsedYears = (YEAR(GETDATE()) - it.yyyy)
    FROM #ItemUsedInfo AS iui
        INNER JOIN #ItemTrans2 AS it
            ON it.ItemCode = iui.ItemCode
    WHERE ISNULL(iui.UsedInfo, '') = ''
          AND iui.UsedYears = 0

    --按最后使用年份更新info
    UPDATE #ItemUsedInfo
    SET UsedInfo = '' + CAST((YEAR(GETDATE()) - LastUsedYear) AS VARCHAR(5)) + '年没使用（'
                   + CAST((LastUsedYear + 1) AS VARCHAR(5)) + '年至今）'
    WHERE ISNULL(UsedInfo, '') = ''
          AND FirstUsedYear = 0
          AND UsedYears > 0

    --把"1年没使用（2023年至今）"改为“0年没使用（2023年前创建）”
    UPDATE iui
    SET iui.UsedInfo = '0年没使用（' + CAST(YEAR(GETDATE()) AS VARCHAR(5)) + '年前创建）'
    FROM #ItemUsedInfo AS iui
        INNER JOIN dbo.ITEM AS i
            ON i.ITEM_CODE = iui.ItemCode
    WHERE iui.UsedYears = 1
          AND YEAR(i.CreateDate) < YEAR(GETDATE())

    UPDATE iui
    SET iui.UsedInfo = '0年没使用（' + CAST(YEAR(GETDATE()) AS VARCHAR(5)) + '年创建）'
    FROM #ItemUsedInfo AS iui
        INNER JOIN dbo.ITEM AS i
            ON i.ITEM_CODE = iui.ItemCode
    WHERE iui.UsedYears = 1
          AND YEAR(i.CreateDate) = YEAR(GETDATE())

    --更新一直没使用的
    UPDATE iui
    SET iui.LastUsedYear = YEAR(i.CreateDate),
        --iui.UsedInfo = '' + CAST((YEAR(GETDATE()) - YEAR(i.CreateDate)) AS VARCHAR(5)) + '年没使用（' + CAST(YEAR(i.CreateDate) AS VARCHAR(5)) + '年创建至今）'
        iui.UsedInfo = (CASE
                            WHEN (YEAR(GETDATE()) - YEAR(i.CreateDate) > 5) THEN
        ('6年没使用（' + CAST(YEAR(i.CreateDate) AS VARCHAR(5)) + '年创建）')
                            ELSE
        ('' + CAST((YEAR(GETDATE()) - YEAR(i.CreateDate) + 1) AS VARCHAR(5)) + '年没使用（'
         + CAST(YEAR(i.CreateDate) AS VARCHAR(5)) + '年创建）'
        )
                        END
                       )
    FROM #ItemUsedInfo AS iui
        INNER JOIN dbo.ITEM AS i
            ON i.ITEM_CODE = iui.ItemCode
    WHERE ISNULL(iui.UsedInfo, '') = ''
          AND iui.UsedYears = 0



    SELECT iui.ItemCode,
           iui.UsedInfo,
           iui.UsedYears,
           iui.FirstUsedYear,
           iui.LastUsedYear
    FROM #ItemUsedInfo AS iui
    ORDER BY iui.ItemCode


END