alter table HJGL_WeldTask
    add CreateMan varchar(50),
        AuditMan varchar(50),
        AuditDate datetime,
        AuditMan2 varchar(50),
        AuditDate2 datetime;
go
alter table Tw_OutputMaster
     add   AuditMan2 varchar(50),
           AuditDate2 datetime;
go
alter view dbo.View_HJGL_Pipeline as
    SELECT pipeline.PipelineId,
           pipeline.ProjectId,
           pipeline.UnitId,
           pipeline.UnitWorkId,
           pipeline.PipelineCode,
           pipeline.SingleName,
           pipeline.SingleNumber,
           pipeline.PipingClassId,
           pipeline.MediumId,
           pipingClass.PipingClassCode,
           pipeline.DetectionRateId,
           rate.DetectionRateCode,
           pipeline.DetectionType,
           pipeline.TestPressure,
           pipeline.TestMedium,
           pipeline.PressurePipingClassId,
           pc.PressurePipingClassCode,
           pipeline.PipeLenth,
           pipeline.DesignPress,
           pipeline.DesignTemperature,
           pipeline.Remark,
           pipeline.LeakPressure,
           pipeline.LeakMedium,
           pipeline.VacuumPressure,
           pipeline.PCMedium,
           pipeline.PCtype,
           pipeline.MaterialId,
           pipeline.PipeArea,
           case pipeline.PipeArea when '1' then '工厂预制' when '2' then '现场施工' else '' end as PipeAreaStr,
           pipeline.FlowingSection,
           mat.MaterialCode,
           dType.DetectionTypeCode,
           lea.MediumName                                                                       AS LeakMediumName,
           pur.PurgeMethodName                                                                  AS PCMediumName,
           CAST((SELECT SUM(ISNULL(Size, 0))
                 FROM dbo.HJGL_WeldJoint
                 WHERE PipelineId = pipeline.PipelineId) AS DECIMAL(8, 3))                      AS TotalDin,
           CAST((SELECT ISNULL(SUM(ISNULL(jot.Size, 0)), 0)
                 FROM dbo.HJGL_WeldJoint jot
                 WHERE jot.PipelineId = pipeline.PipelineId
                   AND jot.WeldingDailyId IS NOT NULL) AS DECIMAL(8, 3))                        AS FinishSize,
           (SELECT COUNT(WeldJointId)
            FROM dbo.HJGL_WeldJoint
            WHERE PipelineId = pipeline.PipelineId and IsTwoJoint is null)                      AS JointCount,
           (SELECT COUNT(WeldJointId)
            FROM dbo.HJGL_WeldJoint
            WHERE PipelineId = pipeline.PipelineId
              and IsTwoJoint is null
              and JointAttribute = '预制口')                                                    AS ShopJointCount,
           (SELECT COUNT(WeldJointId)
            FROM dbo.HJGL_WeldJoint
            WHERE PipelineId = pipeline.PipelineId
              and IsTwoJoint is null
              and WeldingDailyId IS NOT NULL)                                                   AS FinishJointCount,
           unit.UnitName,
           workArea.UnitWorkCode,
           medium.MediumCode,
           medium.MediumName                                                                    AS MediumName,
           testMedium.MediumCode                                                                AS TestMediumCode,
           pipeline.ActEndDate                                                                  AS FinishedDate,
           pipeline.IsFinished
    FROM dbo.HJGL_Pipeline AS pipeline
             LEFT JOIN Base_PipingClass AS pipingClass ON pipingClass.PipingClassId = pipeline.PipingClassId
             LEFT JOIN dbo.Base_Unit AS unit ON unit.UnitId = pipeline.UnitId
             LEFT JOIN WBS_UnitWork AS workArea ON workArea.UnitWorkId = pipeline.UnitWorkId
             LEFT JOIN Base_Medium AS medium ON medium.MediumId = pipeline.MediumId
             LEFT JOIN dbo.Base_TestMedium AS testMedium ON testMedium.TestMediumId = pipeline.TestMedium
             LEFT JOIN dbo.Base_DetectionRate rate ON rate.DetectionRateId = pipeline.DetectionRateId
             LEFT JOIN dbo.Base_PressurePipingClass pc ON pc.PressurePipingClassId = pipeline.PressurePipingClassId
             LEFT JOIN dbo.Base_TestMedium AS lea ON lea.TestMediumId = pipeline.LeakMedium
             LEFT JOIN dbo.Base_PurgeMethod AS pur ON pur.PurgeMethodId = pipeline.PCMedium
             LEFT JOIN dbo.Base_Material AS mat ON mat.MaterialId = pipeline.MaterialId
             LEFT JOIN dbo.Base_DetectionType dType on dType.DetectionTypeId = pipeline.DetectionType
go
create index HJGL_WeldTask_UnitWorkId_index
    on dbo.HJGL_WeldTask (UnitWorkId)
go

create index HJGL_WeldTask_TaskDate_index
    on dbo.HJGL_WeldTask (TaskDate)
go