280 lines
17 KiB
Transact-SQL
280 lines
17 KiB
Transact-SQL
|
||
INSERT INTO Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
|
||
VALUES('3291F5E4-D24F-4233-8EF3-BDC0B64056DC','보묏옘桿<EFBFBD>헝','HJGL/PersonManage/TestApplication.aspx',40,'3EFCE9C3-1983-4AEC-8FA1-D7A90F08CBCB','Menu_HJGL',0,1,1)
|
||
GO
|
||
INSERT INTO Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
||
VALUES('AD6B60A8-47DE-4DA0-BB74-F14D3F423FFD','3291F5E4-D24F-4233-8EF3-BDC0B64056DC','藤속',1)
|
||
INSERT INTO Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
||
VALUES('6EF105E6-665F-475E-A44C-764FE62A3B5F','3291F5E4-D24F-4233-8EF3-BDC0B64056DC','錦맣',2)
|
||
INSERT INTO Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
||
VALUES('F4CBA1D7-3471-4517-A621-72746EC0D876','3291F5E4-D24F-4233-8EF3-BDC0B64056DC','<EFBFBD>뇜',3)
|
||
INSERT INTO Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
||
VALUES('F1F32117-D0E1-4827-B8F1-BB721E1F5354','3291F5E4-D24F-4233-8EF3-BDC0B64056DC','괏닸',4)
|
||
GO
|
||
|
||
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
|
||
values('CC825E48-2B8B-4DBD-933F-177211778718','묏論밗돛탠튬뮌좆깊','HJGL/WeldingReport/FilmJointStatistics.aspx',180,'F3B157B7-9BEE-4150-80CB-087828F1C51D','Menu_HJGL',0,1,1)
|
||
GO
|
||
|
||
CREATE TABLE [dbo].[Welder_TestApplication](
|
||
[ApplicationTestId] [nvarchar](50) NOT NULL,
|
||
[ProjectId] [nvarchar](50) NULL,
|
||
[ConUnit] [nvarchar](50) NULL,
|
||
[ConUnitWeldingEngineer] [nvarchar](50) NULL,
|
||
[Certificate] [nvarchar](50) NULL,
|
||
[CertificateValidity] [datetime] NULL,
|
||
[PreTestDate] [datetime] NULL,
|
||
[WelderClass] [nvarchar](50) NULL,
|
||
[WelderCode] [nvarchar](50) NULL,
|
||
[WelderName] [nvarchar](50) NULL,
|
||
[IdentityCard] [nvarchar](50) NULL,
|
||
[WeldingPosition] [nvarchar](50) NULL,
|
||
[WeldingMethod] [nvarchar](50) NULL,
|
||
[SpecimenSize] [nvarchar](50) NULL,
|
||
[MaterialCategory] [nvarchar](50) NULL,
|
||
[AppearanceEvaluation] [nvarchar](50) NULL,
|
||
[FirstExamination] [decimal](18, 1) NULL,
|
||
[SecondExamination] [decimal](18, 1) NULL,
|
||
[WelderCondition] [nvarchar](50) NULL,
|
||
[ExamDate] [datetime] NULL,
|
||
[InvigilatorConfirmed] [nvarchar](50) NULL,
|
||
[Area] [nvarchar](50) NULL,
|
||
[ExamTrustDate] [datetime] NULL,
|
||
[ExamTrustCode] [nvarchar](50) NULL,
|
||
[CheckDate] [datetime] NULL,
|
||
[CheckResult] [nvarchar](50) NULL,
|
||
[NDTFilm] [int] NULL,
|
||
[CheckUnit] [nvarchar](50) NULL,
|
||
CONSTRAINT [PK_Welder_TestApplication] PRIMARY KEY CLUSTERED
|
||
(
|
||
[ApplicationTestId] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] WITH CHECK ADD CONSTRAINT [FK_Welder_TestApplication_Base_Project] FOREIGN KEY([ProjectId])
|
||
REFERENCES [dbo].[Base_Project] ([ProjectId])
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] CHECK CONSTRAINT [FK_Welder_TestApplication_Base_Project]
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] WITH CHECK ADD CONSTRAINT [FK_Welder_TestApplication_Base_Unit] FOREIGN KEY([ConUnit])
|
||
REFERENCES [dbo].[Base_Unit] ([UnitId])
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] CHECK CONSTRAINT [FK_Welder_TestApplication_Base_Unit]
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] WITH CHECK ADD CONSTRAINT [FK_Welder_TestApplication_Base_Unit1] FOREIGN KEY([CheckUnit])
|
||
REFERENCES [dbo].[Base_Unit] ([UnitId])
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] CHECK CONSTRAINT [FK_Welder_TestApplication_Base_Unit1]
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] WITH CHECK ADD CONSTRAINT [FK_Welder_TestApplication_Base_WeldingMethod] FOREIGN KEY([WeldingMethod])
|
||
REFERENCES [dbo].[Base_WeldingMethod] ([WeldingMethodId])
|
||
GO
|
||
|
||
ALTER TABLE [dbo].[Welder_TestApplication] CHECK CONSTRAINT [FK_Welder_TestApplication_Base_WeldingMethod]
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'寮숩' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ApplicationTestId'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'淃커Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ProjectId'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'嘉묏데貫' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ConUnit'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'嘉묏데貫보쌈묏넋可' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ConUnitWeldingEngineer'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'<EFBFBD>룀攣、보묏聯숭<EFBFBD>눈/꾸鱗聯<E9B197>눈' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'Certificate'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'聯숭唐槻퍅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'CertificateValidity'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'渡옘桿휑퍅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'PreTestDate'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏잚깎（皐쉔、써뭐、<EFBFBD>구、밗돛）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WelderClass'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏긍뵀' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WelderCode'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'檎츰' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WelderName'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'<EFBFBD>룀聯뵀' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'IdentityCard'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보쌈貫零' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WeldingPosition'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보쌈렘랬' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WeldingMethod'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보쌈桿숭넹당（야竟넹당君끝꿎좆）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'SpecimenSize'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'꼼醴잚깎(FeII、FeIII、FeIV、Ni、Al)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'MaterialCategory'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'棍밖팀땍' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'AppearanceEvaluation'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'寧늴옘桿' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'FirstExamination'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'랗늴옘桿' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'SecondExamination'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏榴檄（덤옘桿、북목瞳멋、북목잼멋、옘桿꼇북목藁끝）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'WelderCondition'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'휑퍅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ExamDate'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'솰옘훙횅훰' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'InvigilatorConfirmed'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'맏得陋零혐堵' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'Area'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏옘桿官辜휑퍅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ExamTrustDate'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏옘桿官辜데뵀' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'ExamTrustCode'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'쇱꿎휑퍅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'CheckDate'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'轟愆쇱꿎써벎' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'CheckResult'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'轟愆쇱꿎탠튬鑒좆' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'NDTFilm'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'쇱꿎데貫' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication', @level2type=N'COLUMN',@level2name=N'CheckUnit'
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'보묏옘桿<EFBFBD>헝깊' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Welder_TestApplication'
|
||
GO
|
||
|
||
CREATE PROC [dbo].[sp_rpt_FilmJointStatistics]
|
||
@projectId NVARCHAR(50) = NULL,
|
||
@unitId NVARCHAR(50)=NULL,
|
||
@workAreaId NVARCHAR(50) = NULL
|
||
|
||
AS
|
||
/*묏論밗돛탠튬보왯固셕깊(뮌炬）*/
|
||
SELECT workArea.WorkAreaId,
|
||
workArea.WorkAreaCode,--혐뵀
|
||
workArea.ProjectId,
|
||
workArea.UnitId,
|
||
/*****댐凜좆******/
|
||
ISNULl(total_jot.total_jot,0) AS total_jot,--보왯鑒
|
||
ISNULL(total_jot.total_size,0) AS total_size,--끉
|
||
/****************/
|
||
/*****5%******/
|
||
ISNULL(five_rate_bw_jot.five_bw_jot,0) AS five_bw_jot,--뚤쌈보왯（돛）
|
||
CEILING(ISNULL(five_rate_bw_jot.five_bw_jot,0) * 0.05) AS five_extract_jot,--녜탠鑒（돛）
|
||
ISNULL(five_rate_fw_jot.five_fw_jot,0) AS five_fw_jot,--실보룹보왯鑒
|
||
CAST(ROUND((ISNULL(five_rate_fw_jot.five_fw_jot,0) * 0.05),0) AS int) AS five_pt_jot,--PT(돛）
|
||
/****************/
|
||
/*****10%******/
|
||
ISNULL(ten_rate_bw_jot.ten_bw_jot,0) AS ten_bw_jot,--뚤쌈보왯（돛）
|
||
CEILING(ISNULL(ten_rate_bw_jot.ten_bw_jot,0)*0.1) AS ten_extract_jot,--녜탠鑒（돛）
|
||
ISNULL(ten_rate_fw_jot.ten_fw_jot,0) AS ten_fw_jot,--실보룹보왯鑒
|
||
CAST(ROUND((ISNULL(ten_rate_fw_jot.ten_fw_jot,0)*0.1),0) AS int) AS ten_pt_jot,--PT(돛）
|
||
/****************/
|
||
/*****20%******/
|
||
ISNULL(twenty_rate_bw_jot.twenty_bw_jot,0) AS twenty_bw_jot, --뚤쌈보왯（돛）
|
||
CEILING(ISNULL(twenty_rate_bw_jot.twenty_bw_jot,0)*0.2) AS twenty_extract_jot,--녜탠鑒（돛）
|
||
ISNULL(twenty_rate_fw_jot.twenty_fw_jot,0) AS twenty_fw_jot,--실보룹보왯鑒
|
||
CAST(ROUND((ISNULL(twenty_rate_fw_jot.twenty_fw_jot,0)*0.2),0) AS int) AS twenty_pt_jot,--PT(돛）
|
||
/****************/
|
||
/*****100%******/
|
||
ISNULL(onHundred_rate_bw_jot.onHundred_bw_jot,0) AS onHundred_bw_jot,--뚤쌈보왯（돛）
|
||
CEILING(ISNULL(onHundred_rate_bw_jot.onHundred_bw_jot,0)*1) AS onHundred_extract_jot,--녜탠鑒（돛）
|
||
ISNULL(onHundred_rate_fw_jot.onHundred_fw_jot,0) AS onHundred_fw_jot,--실보룹보왯鑒
|
||
CAST(ROUND((ISNULL(onHundred_rate_fw_jot.onHundred_fw_jot,0)*1),0) AS int) AS onHundred_pt_jot,--PT(돛）
|
||
/****************/
|
||
ISNULL(((CEILING(ISNULL(five_rate_bw_jot.five_bw_jot,0) * 0.05)+
|
||
CEILING(ISNULL(ten_rate_bw_jot.ten_bw_jot,0)*0.1)+
|
||
CEILING(ISNULL(twenty_rate_bw_jot.twenty_bw_jot,0)*0.2)+
|
||
CEILING(ISNULL(onHundred_rate_bw_jot.onHundred_bw_jot,0)*1))*6),0) AS estimate_check_film,--뮌셕쇱꿎탠튬鑒좆（6蠟）셕炬
|
||
unit.UnitName--嘉묏데貫
|
||
FROM ProjectData_WorkArea AS workArea
|
||
LEFT JOIN Base_Unit AS unit ON unit.UnitId = workArea.UnitId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) AS total_jot,SUM(JOT_Size) as total_size,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
GROUP BY WorkAreaId ) AS total_jot ON total_jot.WorkAreaId=workArea.WorkAreaId
|
||
--뚤쌈보왯（돛）5%
|
||
LEFT JOIN (SELECT COUNT(*) AS five_bw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='BW' AND Base_DetectionRate.DetectionRateCode='5'
|
||
GROUP BY WorkAreaId ) AS five_rate_bw_jot ON five_rate_bw_jot.WorkAreaId=workArea.WorkAreaId
|
||
--실보룹보왯鑒5%
|
||
LEFT JOIN (SELECT COUNT(*) AS five_fw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='FW' AND Base_DetectionRate.DetectionRateCode='5'
|
||
GROUP BY WorkAreaId ) AS five_rate_fw_jot ON five_rate_fw_jot.WorkAreaId=workArea.WorkAreaId
|
||
|
||
--뚤쌈보왯（돛）10%
|
||
LEFT JOIN (SELECT COUNT(*) AS ten_bw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='BW' AND Base_DetectionRate.DetectionRateCode='10'
|
||
GROUP BY WorkAreaId ) AS ten_rate_bw_jot ON ten_rate_bw_jot.WorkAreaId=workArea.WorkAreaId
|
||
--실보룹보왯鑒10%
|
||
LEFT JOIN (SELECT COUNT(*) AS ten_fw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='FW' AND Base_DetectionRate.DetectionRateCode='10'
|
||
GROUP BY WorkAreaId ) AS ten_rate_fw_jot ON ten_rate_fw_jot.WorkAreaId=workArea.WorkAreaId
|
||
|
||
--뚤쌈보왯（돛）20%
|
||
LEFT JOIN (SELECT COUNT(*) AS twenty_bw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='BW' AND Base_DetectionRate.DetectionRateCode='20'
|
||
GROUP BY WorkAreaId ) AS twenty_rate_bw_jot ON twenty_rate_bw_jot.WorkAreaId=workArea.WorkAreaId
|
||
--실보룹보왯鑒20%
|
||
LEFT JOIN (SELECT COUNT(*) AS twenty_fw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='FW' AND Base_DetectionRate.DetectionRateCode='20'
|
||
GROUP BY WorkAreaId ) AS twenty_rate_fw_jot ON twenty_rate_fw_jot.WorkAreaId=workArea.WorkAreaId
|
||
--뚤쌈보왯（돛）100%
|
||
LEFT JOIN (SELECT COUNT(*) AS onHundred_bw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='BW' AND Base_DetectionRate.DetectionRateCode='20'
|
||
GROUP BY WorkAreaId ) AS onHundred_rate_bw_jot ON onHundred_rate_bw_jot.WorkAreaId=workArea.WorkAreaId
|
||
--실보룹보왯鑒100%
|
||
LEFT JOIN (SELECT COUNT(*) AS onHundred_fw_jot,WorkAreaId FROM PW_JointInfo
|
||
LEFT JOIN PW_IsoInfo ON PW_IsoInfo.ISO_ID=PW_JointInfo.ISO_ID
|
||
LEFT JOIN Base_WeldType ON Base_WeldType.WeldTypeId=PW_JointInfo.JOTY_ID
|
||
LEFT JOIN Base_DetectionRate ON Base_DetectionRate.DetectionRateId=PW_IsoInfo.DetectionRateId
|
||
WHERE Base_WeldType.WeldTypeCode='FW' AND Base_DetectionRate.DetectionRateCode='20'
|
||
GROUP BY WorkAreaId ) AS onHundred_rate_fw_jot ON onHundred_rate_fw_jot.WorkAreaId=workArea.WorkAreaId
|
||
WHERE (workArea.ProjectId=@projectId OR @projectId IS NULL)
|
||
AND (workArea.UnitId=@unitId OR @unitId IS NULL)
|
||
AND (workArea.WorkAreaId=@workAreaId OR @workAreaId IS NULL) |