CREATE VIEW dbo.View_HJGL_WeldJointDetectionType AS
    WITH BaseData AS (
        SELECT
            jot.[ProjectId]                                         --项目Id
            , jot.[PipelineCode]                                      --管线号
            , pipe.DetectionType                                      --探伤类型Id(可能多个，中间用|分隔)
            , pipe.PipelineId                                         --管线Id
            , STUFF((SELECT ',' + DetectionTypeCode
                     FROM Base_DetectionType
                     WHERE CHARINDEX('|' + LTRIM(DetectionTypeId) + '|', '|' + pipe.DetectionType + '|') > 0
                     FOR XML PATH('')), 1, 1, '') AS DetectionTypeStr --探伤类型（可能多个，中间用,分隔）
            , (case when charindex('/', jot.WeldJointCode) > 0
                then RIGHT(jot.WeldJointCode, CHARINDEX('/', REVERSE(jot.WeldJointCode)) - 1)
                else jot.WeldJointCode end) as WeldJointNumStr
            , jot.[WeldJointId]                                       --焊口Id
            , jot.[WeldJointCode]                                     --焊口号
        FROM [dbo].[HJGL_WeldJoint] jot
        LEFT JOIN dbo.HJGL_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
    )
    SELECT
        ProjectId,
        PipelineCode,
        DetectionType,
        DetectionTypeStr,
        -- 修改后的JointDetectionTypeStr计算
        (CASE
            -- 当DetectionTypeStr仅为'/'时，返回'/'
            WHEN DetectionTypeStr = '/' THEN '/'
            -- 当WeldJointNumStr第一个字符是字母时
            WHEN WeldJointNumStr LIKE '[A-Za-z]%' THEN
                CASE
                    -- 如果DetectionTypeStr包含'/'，则返回'/'后面的字符串
                    WHEN CHARINDEX('/', DetectionTypeStr) > 0 THEN
                        SUBSTRING(DetectionTypeStr, CHARINDEX('/', DetectionTypeStr) + 1, LEN(DetectionTypeStr))
                    -- 如果不包含'/'，则返回整个DetectionTypeStr
                    ELSE DetectionTypeStr
                END
            -- 当WeldJointNumStr第一个字符不是字母时，返回'/'前面的字符串
            ELSE
                CASE
                    -- 如果DetectionTypeStr包含'/'，则返回'/'前面的字符串
                    WHEN CHARINDEX('/', DetectionTypeStr) > 0 THEN
                        LEFT(DetectionTypeStr, CHARINDEX('/', DetectionTypeStr) - 1)
                    -- 如果不包含'/'，则返回整个DetectionTypeStr
                    ELSE DetectionTypeStr
                END
        END) AS JointDetectionTypeStr,
        -- 继续选择其他所有字段
        WeldJointId,
        WeldJointCode,
        WeldJointNumStr
    FROM BaseData

GO

CREATE TABLE [dbo].[Base_MaterialColor] (
    [MaterialColorId] nvarchar(50) NOT NULL DEFAULT(NEWID()) PRIMARY KEY,
    [UnitId] nvarchar(50),
    [ProjectId] nvarchar(50),
    [MaterialId] nvarchar(50) ,
    [ColorName] NVARCHAR(50) ,
    [ColorCardNo] NVARCHAR(50),
    [RGB] NVARCHAR(20),
    [Remark] NVARCHAR(200)
);

go
exec sp_addextendedproperty 'MS_Description', N'单位id', 'SCHEMA', 'dbo', 'TABLE', 'Base_MaterialColor', 'COLUMN',
     'UnitId'
go

exec sp_addextendedproperty 'MS_Description', N'材质id', 'SCHEMA', 'dbo', 'TABLE', 'Base_MaterialColor', 'COLUMN',
     'MaterialId'
go


exec sp_addextendedproperty 'MS_Description', N'颜色名称', 'SCHEMA', 'dbo', 'TABLE', 'Base_MaterialColor', 'COLUMN',
     'ColorName'
go

exec sp_addextendedproperty 'MS_Description', N'色卡号', 'SCHEMA', 'dbo', 'TABLE', 'Base_MaterialColor', 'COLUMN',
     'ColorCardNo'
go

exec sp_addextendedproperty 'MS_Description', N'备注', 'SCHEMA', 'dbo', 'TABLE', 'Base_MaterialColor', 'COLUMN',
     'Remark'
go

alter table dbo.HJGL_PackagingManageDetail
    add TwOutputDetailId nvarchar(50)
go

exec sp_addextendedproperty 'MS_Description', N'出库明细表主键', 'SCHEMA', 'dbo', 'TABLE', 'HJGL_PackagingManageDetail',
     'COLUMN', 'TwOutputDetailId'
go
INSERT INTO  dbo.Sys_Menu (MenuId, MenuName, Icon, Url, SortIndex, SuperMenu, MenuType, IsOffice, IsEnd, IsUsed) VALUES (N'F6194C00-D256-485D-9056-171FAB75928A', N'管道颜色标识库', null, N'HJGL/BaseInfo/MaterialColor.aspx', 30, N'60F4B988-4D1D-48D6-A959-2EA4BD2978A1', N'Menu_HJGL', 0, 1, 1);
INSERT INTO  dbo.Sys_ButtonToMenu (ButtonToMenuId, MenuId, ButtonName, SortIndex) VALUES (N'E89C96E4-8EE2-4E56-83FE-863B2764CFAE', N'F6194C00-D256-485D-9056-171FAB75928A', N'查看', 5);
INSERT INTO  dbo.Sys_ButtonToMenu (ButtonToMenuId, MenuId, ButtonName, SortIndex) VALUES (N'0041192A-6156-4D4B-AE61-438168E6DB81', N'F6194C00-D256-485D-9056-171FAB75928A', N'删除', 3);
INSERT INTO  dbo.Sys_ButtonToMenu (ButtonToMenuId, MenuId, ButtonName, SortIndex) VALUES (N'4E763EE2-9C82-403A-B861-3BF9DFE7EFD4', N'F6194C00-D256-485D-9056-171FAB75928A', N'保存', 4);
INSERT INTO  dbo.Sys_ButtonToMenu (ButtonToMenuId, MenuId, ButtonName, SortIndex) VALUES (N'173A4716-5C54-491A-B9CF-21FF6442587E', N'F6194C00-D256-485D-9056-171FAB75928A', N'增加', 1);
INSERT INTO  dbo.Sys_ButtonToMenu (ButtonToMenuId, MenuId, ButtonName, SortIndex) VALUES (N'FD24CAF3-E28E-458E-9DE6-CFF90C74AE4D', N'F6194C00-D256-485D-9056-171FAB75928A', N'修改', 2);