DELETE FROM ProjectSpecificationMatrix
GO
DELETE FROM SpecificationMatrixDataColumnLayout
GO
DELETE FROM TestColumnObjectLayout
GO
DELETE FROM SpecificationTestColumnLayout
GO
DELETE FROM SpecificationMatrixTemplateLayout
GO
DELETE FROM TestColumnObjectData
GO
DELETE FROM TestColumnObject
GO
DELETE FROM SpecificationTestColumn
GO
DELETE FROM SpecificationMatrixDataColumnSpecification
GO
DELETE FROM SpecificationMatrixDataColumn
GO
DELETE FROM SpecificationMatrixSpecification
GO
DELETE FROM Specification
GO
DELETE FROM SpecificationMatrix
GO
DELETE FROM TracerTest
GO
DELETE FROM WeldTest
GO
DELETE FROM WeldActivityLog
GO
DELETE FROM Weld
GO
DELETE FROM ProcedureQualificationWeldProcedure
GO
DELETE FROM WeldProcedure
GO
DELETE FROM WeldTestType
GO
DELETE FROM WeldStatus
GO
DELETE FROM WelderProcedureQualificationRecord
GO
DELETE FROM ProcedureQualification
GO
DELETE FROM Welder
GO
DELETE FROM ProjectEmployee
GO
DELETE FROM Employee
GO
DELETE FROM EmployeePosition
GO
DELETE FROM WeldType
GO
DELETE FROM HydroTestDrawing
GO
DELETE FROM HydroTestSpool
GO
DELETE FROM HydroTest
GO
DELETE FROM HydroMedium
GO
DELETE FROM TestResult
GO
DELETE FROM SpoolRevision
GO
DELETE FROM Spool
GO
DELETE FROM Employee
GO
--ALTER TABLE DrawingRevision DROP CONSTRAINT Drawing_DrawingRevision_FK1
--GO
--ALTER TABLE Drawing DROP CONSTRAINT DrawingRevision_Drawing_FK1
--GO
DELETE FROM DrawingRevision
GO
DELETE FROM Drawing
GO
DELETE FROM DrawingType
GO
DELETE FROM Project
GO
DELETE FROM Customer
GO

/** Reset Identity Values **/
DBCC CHECKIDENT (Project, RESEED, 0)
DBCC CHECKIDENT (Customer, RESEED, 0)
DBCC CHECKIDENT (WeldProcedure, RESEED, 0)
DBCC CHECKIDENT (WeldTestType, RESEED, 0)
DBCC CHECKIDENT (SpecificationMatrixTemplateLayout, RESEED, 0)
DBCC CHECKIDENT (SpecificationTestColumnLayout, RESEED, 0)
DBCC CHECKIDENT (TestColumnObjectLayout, RESEED, 0)
DBCC CHECKIDENT (SpecificationMatrixDataColumnLayout, RESEED, 0)
DBCC CHECKIDENT (SpecificationMatrix, RESEED, 0)
DBCC CHECKIDENT (SpecificationMatrixDataColumn, RESEED, 0)
DBCC CHECKIDENT (Specification, RESEED, 0)
DBCC CHECKIDENT (SpecificationTestColumn, RESEED, 0)
DBCC CHECKIDENT (TestColumnObject, RESEED, 0)
DBCC CHECKIDENT (WeldTest, RESEED, 0)
DBCC CHECKIDENT (TracerTest, RESEED, 0)
DBCC CHECKIDENT (WelderProcedureQualificationRecord, RESEED, 0)
DBCC CHECKIDENT (ProcedureQualification,RESEED, 0)
DBCC CHECKIDENT (WeldStatus, RESEED, 0)
DBCC CHECKIDENT (WeldActivityLog, RESEED, 0)
DBCC CHECKIDENT (WeldProcedure, RESEED, 0)
DBCC CHECKIDENT (Weld, RESEED, 0)
DBCC CHECKIDENT (WeldType, RESEED, 0)
DBCC CHECKIDENT (TestResult, RESEED, 0)
DBCC CHECKIDENT (HydroMedium, RESEED, 0)
DBCC CHECKIDENT (HydroTest, RESEED, 0)
DBCC CHECKIDENT (Spool, RESEED, 0)
DBCC CHECKIDENT (Drawing, RESEED, 0)
DBCC CHECKIDENT (DrawingType, RESEED, 0)
DBCC CHECKIDENT (EmployeePosition, RESEED, 0)
DBCC CHECKIDENT (Employee, RESEED, 0)


/** Supporting Tables **/

/** Customer **/
INSERT INTO Customer (CustomerName) VALUES ('Cord Projects')
GO
INSERT INTO Customer (CustomerName) VALUES ('Epcor')
GO
INSERT INTO Customer (CustomerName) VALUES ('Syncrude')
GO
INSERT INTO Customer (CustomerName) VALUES ('Suncor')
GO
INSERT INTO Customer (CustomerName) VALUES ('Nexen')
GO

/** SpecificationMatrix **/
INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-825 NEXEN','B31.3')
GO
INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-826 NEXEN','B31.3')
GO
INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-827 NEXEN','B31.3')
GO
INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-828 NEXEN','B31.3')
GO
INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-829 NEXEN','B31.3')
GO

/** WeldTestType **/
INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Radiography Test','RT')
GO
INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Brinnel Hardness Test','BHN')
GO
INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Magnetic Particle Inspection','MPI')
GO
INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Liquid Penetrant Inspection','LPI')
GO
INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Ferrite Test','FT')
GO

/** SpecificationMatrixTemplateLayout **/
INSERT INTO SpecificationMatrixTemplateLayout (SpecificationCodeLayout) VALUES ('B31.3')
GO
INSERT INTO SpecificationMatrixTemplateLayout (SpecificationCodeLayout) VALUES ('Z662')
GO

/** DrawingType **/
INSERT INTO DrawingType (DrawingTypeName) VALUES ('Module Work Package')
GO
INSERT INTO DrawingType (DrawingTypeName) VALUES ('Construction Work Package')
GO
INSERT INTO DrawingType (DrawingTypeName) VALUES ('P&ID')
GO
INSERT INTO DrawingType (DrawingTypeName) VALUES ('Isometric')
GO

/** EmployeePosition **/
INSERT INTO EmployeePosition (PositionName) VALUES ('Welder')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Project Manager')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Turnover Co-Ordinator')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Quality Administrator')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Quality Services Manager')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Office Administrator')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Document Control')
GO
INSERT INTO EmployeePosition (PositionName) VALUES ('Data Administration Clerk')
GO

/** HydroMedium **/
INSERT INTO HydroMedium (Medium) VALUES ('Water')
GO
INSERT INTO HydroMedium (Medium) VALUES ('Glycol')
GO

/** TestResult **/
INSERT INTO TestResult (Result) VALUES ('Pass')
GO
INSERT INTO TestResult (Result) VALUES ('Fail')
GO
INSERT INTO TestResult (Result) VALUES ('Pending')
GO

/** WeldType **/
INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Socket Weld', 'SW')
GO
INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Gap Shot', 'GS')
GO
INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Butt Weld', 'BW')
GO
INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Threadolet', 'TH')
GO
INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Dummy Leg', 'DL')
GO

/** WeldStatus **/
INSERT INTO WeldStatus (StatusName) VALUES ('Not Started')
GO
INSERT INTO WeldStatus (StatusName) VALUES ('Pending')
GO
INSERT INTO WeldStatus (StatusName) VALUES ('Complete')
GO
INSERT INTO WeldStatus (StatusName) VALUES ('Reweld')
GO

/** Employee **/
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Jeff', 'O''Toole', 1)					/** 1 **/
GO
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES	('Trent', 'Sohnle', 1)				  /** 2 **/
GO
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES	('Andrij', 'Demianczuk', 1)	/** 3 **/
GO
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES	('Sean', 'Howat', 1)									/** 4 **/
GO	
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Alvar', 'Lindroth', 2)
GO
INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Kara', 'Fontaine', 3)
GO

/** ProcedureQualification **/
INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-02252')
GO
INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-5545')
GO
INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-5545')
GO
INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-21786')
GO
INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-6186')
GO


/** Child Tables **/

/** Project **/
INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Epcor Pass Route',1, 1)
GO
INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('BP Pipelines Pass Route',2,1)
GO
INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Syncrude Pass Route',3,1)
GO
INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Suncor Pass Route',4,1)
GO
INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('NEXEN Pass Route',5,1)
GO

/** Drawing **/
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-006', 1)/** 1 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-016', 1)/** 2 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-017', 1)/** 3 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-120', 1)/** 4 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-121', 1)/** 5 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-122', 1)/** 6 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-123', 1)/** 7 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-124', 1)/** 8 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-125', 1)/** 9 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-126', 1)/** 10 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-127', 1)/** 11 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-128', 1)/** 12 **/
GO
/** Drawing Children **/
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (3, '8101-PRM-400', 1, 1, 0)/** 13 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (3, '8102-PRM-401', 1, 1, 0)/** 14 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-402', 1, 13, 0)/** 15 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-403', 1, 13, 0)/** 16 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-404', 1, 13, 0)/** 17 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-405', 1, 13, 0)/** 18 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-406', 1, 13, 0)/** 19 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-407', 1, 13, 0)/** 20 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-408', 1, 13, 0)/** 21 **/
GO
INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-409', 1, 13, 0)/** 22 **/
GO

/** Spool **/
INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )
GO
INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )
GO
INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )
GO
INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('B', '8100-PRM-021', '003036', 3, 0 )
GO
INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('B', '8100-PRM-021', '003036', 3, 0 )
GO

/** ProjectSpecificationMatrix **/
INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (1,1)
GO
INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (2,2)
GO
INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (3,3)
GO
INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (4,4)
GO
INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (5,5)
GO

/** WeldProcedure **/
INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-6', 1)
GO
INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-7', 1)
GO
INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-14A', 1)
GO
INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-15', 1)
GO
INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-16', 1)
GO

/** Specification **/
INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (1, 'A')
GO
INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (2, 'A')
GO
INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (1, 'AC')
GO
INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (2, 'AC')
GO
INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (3, 'AD')
GO

/** SpecificationMatrixSpecification **/
INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,1)
GO
INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,2)
GO
INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,3)
GO
INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,4)
GO
INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,5)
GO

/** SpecificationMatrixDataColumnLayout **/
INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Pipe Class')
GO
INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Cord WPS')
GO
INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'P#')
GO
INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Material Type')
GO
INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'WPS max W.T')
GO

/** SpecificationTestColumnLayout **/
INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (1,1)
GO
INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (2,1)
GO
INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (3,1)
GO
INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (4,1)
GO
INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (5,1)
GO

/** TestColumnObjectLayout **/
INSERT INTO	TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'BW', 1)
GO
INSERT INTO	TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'SYN/MFG Service', 0)
GO
INSERT INTO	TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'SW Gap Shot', 1)
GO
INSERT INTO	TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (3, 'SW / Olets', 1)
GO
INSERT INTO	TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (3, 'ATT', 1)
GO

/** SpecificationTestColumn **/
INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (1,1)
GO
INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (2,1)
GO
INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (3,1)
GO
INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (4,1)
GO
INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (5,1)
GO

/** TestColumnObject **/
INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (1, 'BW', 1)
GO
INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (2, 'SYN/MFG Service', 0)
GO
INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (3, 'SW Gap Shot', 1)
GO
INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (4, 'SW/Olets', 1)
GO
INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (5, 'ATT', 0)
GO

/** TestColumnObjectData **/
INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (1,1, '10')
GO
INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (2,1, '5')
GO
INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (3,1, '10')
GO
INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (4,1, '100')
GO
INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (5,1, 'N/A')
GO

/** SpecificationMatrixDataColumn **/
INSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Weld Process', 1)
GO
INSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Mat. Type', 1)
GO
INSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Preheat', 1)
GO

/** SpecificationMatrixDataColumnSpecification **/
INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,1,'P1')
GO
INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,2,'P1')
GO
INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,3,'P1')
GO
INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (2,1,'A106 Gr B')
GO
INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (2,2,'A106 Gr B')
GO

/** Weld **/
INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A1', 1, 1, 0, 32.2, 'Std(.154)', 'TRACER', getDate(), 'A', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A2', 2, 1, 0, 32.2, 'Std(.154)', getDate(), 'B', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A3', 3, 2, 0, 30, 'Std(.154)', getDate(), 'C', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A4', 1, 2, 0, 30.5, 'Std(.154)', getDate(), 'D', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A1', 2, 2, 0, 22, 'Std(.154)', 'TRACER', getDate(), 'E', 13, 0)
GO

/** Weld With ParentId **/
INSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 1, 'A1', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (2, 2, 'A2', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)
GO
INSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (3, 3, 'A3', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)
GO

/** WelderProcedureQualificationRecord **/
INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (1, getDate(), 'A1')
GO
INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (1, getDate(), 'A2')
GO
INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (2, getDate(), 'A3')
GO
INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (3, getDate(), 'A4')
GO
INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (4, getDate(), 'A1')
GO

/** WeldTest **/
INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (1, 1, getDate(), 2)
GO
INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (2, 1, getDate(), 2)
GO
INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (3, 2, getDate(), 2)
GO
INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (4, 2, getDate(), 1)
GO
INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (5, 3, getDate(), 3)
GO

/** Tracertest **/
INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (1, 2)
GO
INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (1, 3)
GO
INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (2, 4)
GO
INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (2, 5)
GO
INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (3, 6)
GO

/** ProcedureQualificationWeldProcedure **/
INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (1, 1)
GO
INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (2, 1)
GO
INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (3, 1)
GO
INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (4, 4)
GO
INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (5, 4)
GO

/** WeldActivityLog **/
INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (1, 2, getDate())
GO
INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (2, 1, getDate())
GO
INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate, Comment) VALUES (3, 1, getDate(), 'THIS IS A TEST')
GO
INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (4, 2, getDate())
GO
INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (5, 3, getDate())
GO

/** ProjectEmployee **/
INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 1)
GO
INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 2)
GO
INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 3)
GO
INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (2, 4)
GO
INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (2, 2)
GO

/** HydroTest **/
INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)
GO
INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)
GO
INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)
GO
INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (1, getDate(), 1)
GO
INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (1, getDate(), 2)
GO

/** HydroTestSpool **/
INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (1, 1)
GO
INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (2, 2)
GO
INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (3, 3)
GO
INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (4, 4)
GO
INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (5, 5)
GO

/** HydroTestDrawing **/
INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (1, 13)
GO
INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (1, 14)
GO
INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (2, 15)
GO
INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (2, 16)
GO
INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (3, 17)
GO

/* Create procedure/function _DrawingRevision_GetNextDrawingRevisionNumber.                   */
/** Work Package Revision **/
CREATE PROCEDURE _DrawingRevision_GetNextDrawingRevisionNumber( @DrawingId int = NULL )
AS
	SET NOCOUNT ON
	DECLARE @NewDrawingRevisionNumber int
	BEGIN
		IF @DrawingId IS NULL
			BEGIN
				RAISERROR ('_DrawingRevision_GetNextDrawingRevisionNumber :: DrawingId must be assigned a value and cannot be null', 16, 1)
			END
		ELSE
			BEGIN
				IF Exists( SELECT MAX(DR.DrawingRevisionNumber)
						   FROM   DrawingRevision DR
						   WHERE  DR.DrawingId = @DrawingId
						 )
					BEGIN
						SET @NewDrawingRevisionNumber = ( SELECT MAX(DR.DrawingRevisionNumber) + 1
														  FROM   DrawingRevision DR
														  WHERE  DR.DrawingId = @DrawingId
														)
					END
				-- END IF
			END
		-- END IF
	END
RETURN @NewDrawingRevisionNumber
GO


go

/* Create procedure/function _DrawingRevision_InsertNewRevision.                              */
CREATE PROCEDURE _DrawingRevision_InsertNewRevision(@DrawingId int = NULL)
AS
	SET NOCOUNT ON
	DECLARE @NewDrawingRevisionNumber int

	BEGIN
		IF @DrawingId IS NULL
			BEGIN
				RAISERROR ('_DrawingRevision_InsertNewRevision :: DrawingId must be assigned a value and cannot be null', 16, 1)
			END
		ELSE
			BEGIN
				EXEC @NewDrawingRevisionNumber = _DrawingRevision_GetNextDrawingRevisionNumber @DrawingId
				IF @NewDrawingRevisionNumber IS NULL
					BEGIN
						RAISERROR ('_DrawingRevision_InsertNewRevision :: NewDrawingRevisionNumber was returned as null from procedure _DrawingRevision_GetNextDrawingRevisionNumber', 16, 1)
					END
				ELSE
					BEGIN
						-- Do the Insert All is Good
						INSERT INTO DrawingRevision (DrawingId, DrawingRevisionNumber) VALUES (@DrawingId, @NewDrawingRevisionNumber)
						IF @@ERROR <> 0 
							BEGIN
								RAISERROR ('_DrawingRevision_InsertNewRevision :: There was an issue while inserting new Drawing Revision', 16, 1)
							END
						-- END IF
					END
				-- END IF
			END
		-- END IF
	END
RETURN
GO


go

/* Create procedure/function _SpoolRevision_GetNextSpoolRevisionNumber.                       */
/** Spool Revision **/
CREATE PROCEDURE _SpoolRevision_GetNextSpoolRevisionNumber( @SpoolId int = NULL )
AS
	SET NOCOUNT ON
	DECLARE @NewSpoolRevisionNumber int
	BEGIN
  		IF @SpoolId IS NULL
			BEGIN
				RAISERROR ('_SpoolRevision_GetNextSpoolRevisionNumber :: SpoolId must be assigned a value and cannot be null', 16, 1)
			END
		ELSE
			BEGIN
				IF Exists( SELECT MAX(IR.SpoolRevisionNumber)
						   FROM	  SpoolRevision IR
						   WHERE  IR.SpoolId = @SpoolId
						 )
					BEGIN
						SET @NewSpoolRevisionNumber = ( SELECT MAX(IR.SpoolRevisionNumber) + 1
					    								FROM   SpoolRevision IR
					    								WHERE IR.SpoolId = @SpoolId
													  )
					END
				-- END IF
			END
		-- END IF
	END
RETURN @NewSpoolRevisionNumber
GO


go

/* Create procedure/function _SpoolRevision_InsertNewRevision.                                */
CREATE PROCEDURE _SpoolRevision_InsertNewRevision(@SpoolId int = NULL)
AS
SET NOCOUNT ON
	DECLARE @NewSpoolRevisionNumber int

	BEGIN
		IF @SpoolId IS NULL
			BEGIN
				RAISERROR ('_SpoolRevision_InsertNewRevision :: SpoolId must be assigned a value and cannot be null', 16, 1)
			END
		ELSE
			BEGIN
				EXEC @NewSpoolRevisionNumber = _SpoolRevision_GetNextSpoolRevisionNumber @SpoolId
				IF @NewSpoolRevisionNumber = NULL
					BEGIN
						RAISERROR ('_SpoolRevision_InsertNewRevision :: NewSpoolRevisionNumber was returned as null from procedure _SpoolRevision_GetNextSpoolRevisionNumber', 16, 1)
					END
				ELSE
					BEGIN
						-- Do the Insert All is Good
						INSERT INTO SpoolRevision (SpoolId, SpoolRevisionNumber) VALUES (@SpoolId, @NewSpoolRevisionNumber)
						IF @@ERROR <> 0 
							BEGIN
								RAISERROR ('_SpoolRevision_InsertNewRevision :: There was an issue while inserting new Work Package Revision', 16, 1)
							END
						-- END IF
					END
				-- END IF
			END
		-- END IF
	END
RETURN
GO


go

/* Create procedure/function _Welder_GetNextNewWelder.                                        */
CREATE PROCEDURE _Welder_GetNextNewWelder ( @WelderId varchar(3) = NULL OUTPUT, @WelderIdPrefix char(1) = NULL )
AS
	SET NOCOUNT ON
	DECLARE @WelderIdSuffix int,
			@WelderIdCounter int

	BEGIN
		-- TODO 
		-- Check to see if Temp Table Will Exist On Recursive Call
		CREATE TABLE #WelderRange ( WelderIdSuffix int )
	
		-- Lets Load Our Pretty Table
		SET @WelderIdCounter = 1
		WHILE @WelderIdCounter < 100
			BEGIN
				INSERT INTO #WelderRange ( WelderIdSuffix ) VALUES ( @WelderIdCounter ) 
				SET @WelderIdCounter = @WelderIdCounter + 1
			END
		-- END WHILE
	
		-- Now Lets Set Our Prefix
		IF @WelderIdPrefix IS NULL		
			BEGIN
				SELECT @WelderIdPrefix = 'A'
			END
  -- END IF

 
		SET @WelderIdSuffix = ( SELECT min(WelderIdSuffix) 
							    FROM   #WelderRange
								WHERE  WelderIdSuffix NOT IN ( SELECT cast( substring(WelderId, 2, len(WelderId) - 1) AS int )
															   FROM Welder
															   WHERE substring(WelderId, 1, 1) = @WelderIdPrefix
															 )
							   )

		IF @WelderIdSuffix IS NULL
			BEGIN
				IF @WelderIdPrefix = 'Z'
					BEGIN
						RAISERROR('This system has exceeded the number of possible WelderId combinations which is 2574, plese consult the System Designer',16 ,1)
					END
				ELSE
					BEGIN
						SET @WelderIdPrefix =  cast( ascii(@WelderIdPrefix) + 1 AS char)
						EXEC _Welder_GetNextNewWelder @WelderId OUTPUT, @WelderIdPrefix
					END
				-- END IF
			END
		ELSE
			BEGIN
				SET @WelderId = ( SELECT @WelderIdPrefix + cast(@WelderIdSuffix AS char) )
			END
		-- END IF
	END
GO


go

/* Create procedure/function FN_Spool_CountWeldStatus.                                        */
CREATE FUNCTION FN_Spool_CountWeldStatus ( @SpoolId int, @WeldStatusId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalActiveWeldCount int
		SET @TotalActiveWeldCount = ( SELECT count(W.WeldId)
									  FROM   Weld W
									  WHERE  W.SpoolId = @SpoolId
									  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
														  FROM   WeldActivityLog WAL
														  WHERE  WAL.WeldId = W.WeldId
														  AND    WAL.WeldStatusId = @WeldStatusId
														  ORDER BY LogDate DESC
														)
									)
		RETURN @TotalActiveWeldCount
	END
GO


go

/* Create procedure/function _Spool_CountWeldStatus.                                          */
CREATE PROCEDURE _Spool_CountWeldStatus ( @SpoolId int, @WeldStatusId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Spool_CountWeldStatus (@SpoolId, @WeldStatusId) )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function FN_Spool_CountNotWeldStatus.                                     */
CREATE FUNCTION FN_Spool_CountNotWeldStatus ( @SpoolId int, @WeldStatusId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalActiveWeldCount int
		SET @TotalActiveWeldCount = ( SELECT count(W.WeldId)
									  FROM   Weld W
									  WHERE  W.SpoolId = @SpoolId
									  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
														  FROM   WeldActivityLog WAL
														  WHERE  WAL.WeldId = W.WeldId
														  AND    WAL.WeldStatusId <> @WeldStatusId
														  ORDER BY LogDate DESC
														)
									)
		RETURN @TotalActiveWeldCount
	END
GO


go

/* Create procedure/function _Spool_CountNotWeldStatus.                                       */
CREATE PROCEDURE _Spool_CountNotWeldStatus ( @SpoolId int, @WeldStatusId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Spool_CountNotWeldStatus (@SpoolId, @WeldStatusId) )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function FN_Drawing_CountWeldStatus.                                      */
CREATE FUNCTION FN_Drawing_CountWeldStatus ( @DrawingId int, @WeldStatusId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalActiveWeldCount int
		
		SET @TotalActiveWeldCount = ( SELECT count(W.WeldId)
									  FROM   Weld W
									  WHERE  W.DrawingId = @DrawingId
									  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
														  FROM   WeldActivityLog WAL
														  WHERE  WAL.WeldId = W.WeldId
														  AND    WAL.WeldStatusId = @WeldStatusId
														  ORDER BY LogDate DESC
														)
									) + ( SELECT count(W.WeldId)
										  FROM   Drawing D, Spool S, Weld W
										  WHERE  D.DrawingId = S.DrawingId
										  AND    D.DrawingId = @DrawingId
										  AND    W.WeldId = S.DrawingId
										  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
															  FROM   WeldActivityLog WAL
															  WHERE  WAL.WeldId = W.WeldId
															  AND    WAL.WeldStatusId = @WeldStatusId
															  ORDER BY LogDate DESC
															)
										) + ( SELECT SUM(dbo.FN_Drawing_CountWeldStatus(D.DrawingId, @WeldStatusId))
											  FROM Drawing D
											  WHERE D.ParentDrawingId = @DrawingId
											)
			RETURN @TotalActiveWeldCount
	END	
GO


go

/* Create procedure/function _Drawing_CountWeldStatus.                                        */
CREATE PROCEDURE _Drawing_CountWeldStatus ( @DrawingId int, @WeldStatusId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Drawing_CountWeldStatus(@DrawingId, @WeldStatusId) )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function FN_Drawing_CountNotWeldStatus.                                   */
CREATE FUNCTION FN_Drawing_CountNotWeldStatus ( @DrawingId int, @WeldStatusId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalActiveWeldCount int
		
		SET @TotalActiveWeldCount = ( SELECT count(W.WeldId)
									  FROM   Weld W
									  WHERE  W.DrawingId = @DrawingId
									  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
														  FROM   WeldActivityLog WAL
														  WHERE  WAL.WeldId = W.WeldId
														  AND    WAL.WeldStatusId <> @WeldStatusId
														  ORDER BY LogDate DESC
														)
									) + ( SELECT count(W.WeldId)
										  FROM   Drawing D, Spool S, Weld W
										  WHERE  D.DrawingId = S.DrawingId
										  AND    D.DrawingId = @DrawingId
										  AND    W.WeldId = S.DrawingId
										  AND    W.WeldId = ( SELECT TOP 1 WAL.WeldId 
															  FROM   WeldActivityLog WAL
															  WHERE  WAL.WeldId = W.WeldId
															  AND    WAL.WeldStatusId <> @WeldStatusId
															  ORDER BY LogDate DESC
															)
										) + ( SELECT SUM(dbo.FN_Drawing_GetInActiveWeldCount(D.DrawingId, @WeldStatusId))
											  FROM Drawing D
											  WHERE D.ParentDrawingId <> @DrawingId
											)
		RETURN @TotalActiveWeldCount
	END	
GO


go

/* Create procedure/function _Drawing_CountNotWeldStatus.                                     */
CREATE PROCEDURE _Drawing_CountNotWeldStatus ( @DrawingId int, @WeldStatusId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Drawing_CountNotWeldStatus(@DrawingId, @WeldStatusId) )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function FN_Weld_CountWeldTestResult.                                     */
CREATE FUNCTION FN_Weld_CountWeldTestResult ( @WeldId int, @TestResultId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalTestStatusCount int
		SET @TotalTestStatusCount = ( SELECT count(WT.WeldId)
									  FROM WeldTest WT
									  WHERE WT.WeldId = @WeldId
									  AND WT.TestResultId = @TestResultId
									)
		RETURN @TotalTestStatusCount
	END
GO


go

/* Create procedure/function _Weld_CountWeldTestResult.                                       */
CREATE PROCEDURE _Weld_CountWeldTestResult ( @WeldId int, @TestResultId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalTestStatusCount int
	BEGIN
		SET @TotalTestStatusCount = ( SELECT * FROM dbo.FN_Weld_CountWeldTestResult(@WeldId, @TestResultId) )
		RETURN @TotalTestStatusCount
	END
GO



go

/* Create procedure/function FN_Weld_CountNotWeldTestResult.                                  */
CREATE FUNCTION FN_Weld_CountNotWeldTestResult ( @WeldId int, @TestResultId int )
RETURNS int
AS
	BEGIN
		DECLARE @TotalTestStatusCount int
		SET @TotalTestStatusCount = ( SELECT count(WT.WeldId)
									  FROM WeldTest WT
									  WHERE WT.WeldId = @WeldId
									  AND WT.TestResultId <> @TestResultId
									)
		RETURN @TotalTestStatusCount
	END
GO


go

/* Create procedure/function _Weld_CountNotWeldTestResult.                                    */
CREATE PROCEDURE _Weld_CountNotWeldTestResult ( @WeldId int, @TestResultId int )
AS
	SET NOCOUNT ON       
	DECLARE @TotalTestStatusCount int
	BEGIN
		SET @TotalTestStatusCount = ( SELECT * FROM dbo.FN_Weld_CountWeldNotTestResult(@WeldId, @TestResultId) )
		RETURN @TotalTestStatusCount
	END
GO


go

/* Create procedure/function _Project_CountWeldStatus.                                        */
CREATE PROCEDURE _Project_CountWeldStatus(@ProjectId int, @WeldStatusId int )
AS
	SET NOCOUNT ON  
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT SUM(dbo.FN_Drawing_CountWeldStatus(DrawingId, @WeldStatusId)) 
								FROM Drawing 
								WHERE ProjectId = @ProjectId
							  )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function _Project_CountNotWeldStatus.                                     */
CREATE PROCEDURE _Project_CountNotWeldStatus(@ProjectId int, @WeldStatusId int )
AS
	SET NOCOUNT ON      
	DECLARE @TotalWeldCount int
	BEGIN
		SET @TotalWeldCount = ( SELECT SUM(dbo.FN_Drawing_CountNotWeldStatus(DrawingId, @WeldStatusId)) 
								FROM Drawing 
								WHERE ProjectId = @ProjectId
							  )
		RETURN @TotalWeldCount
	END
GO


go

/* Create procedure/function FN_Weld_GetCurrentWeldStatus.                                    */
CREATE FUNCTION FN_Weld_GetCurrentWeldStatus ( @WeldId int )
RETURNS int
AS
	BEGIN
		DECLARE @WeldStatusId int
		SET @WeldStatusId = ( SELECT TOP 1 WeldStatusId 
							  FROM   WeldActivityLog Where WeldId = @WeldId 
							  AND WeldStatusId = 1 
							  ORDER BY LogDate DESC
							)
		RETURN @WeldStatusId  		
	END
GO


go

/* Create procedure/function _Weld_GetCurrentWeldStatus.                                      */
CREATE PROCEDURE _Weld_GetCurrentWeldStatus(@WeldId int )
AS
	SET NOCOUNT ON     
	DECLARE @WeldStatus int
	BEGIN
		SET @WeldStatus = ( SELECT * FROM dbo.FN_Weld_GetCurrentWeldStatus(@WeldId) )
		RETURN @WeldStatus
	END
GO


go

/* Create procedure/function _Welder_InsertNewWelder.                                         */
CREATE PROCEDURE _Welder_InsertNewWelder(@EmployeeId int = NULL)
AS
	SET NOCOUNT ON
	DECLARE @NewWelderId varchar(3)

	BEGIN
		IF @EmployeeId IS NULL
			BEGIN
				RAISERROR ('_Welder_InsertNewWelder :: EmployeeId must be assigned a value and cannot be null', 16, 1)
			END
		ELSE
			BEGIN
				EXEC _Welder_GetNextNewWelder @NewWelderId OUTPUT
				IF @NewWelderId IS NULL
					BEGIN
						RAISERROR ('_Welder_InsertNewWelder :: NewWelderId was returned as null from procedure _Welder_InsertNewWelder', 16, 1)
					END
				ELSE
					BEGIN
						-- Do the Insert All is Good
						INSERT INTO Welder (WelderId, EmployeeId) VALUES (ltrim(rtrim(@NewWelderId)), @EmployeeId)
						IF @@ERROR <> 0 
							BEGIN
								RAISERROR ('_Welder_InsertNewWelder :: There was an issue while inserting new Welder', 16, 1)
							END
						-- END IF
					END
				-- END IF
			END
		-- END IF
	END
RETURN
GO


go

/* Create procedure/function GetSpecMatrixTemplate.                                           */
DROP PROCEDURE GetSpecMatrixTemplate
GO
CREATE PROCEDURE GetSpecMatrixTemplate
AS
	DECLARE @TemplateCode varchar(32),
			@TestColumnCount int,
			@DataColumnCount int
	
	BEGIN
		SET @TemplateCode = 'B31.3'

		-- Check to see the requested code actually exists
		If Exists( SELECT SMTL.TemplateIdLayout
				   FROM SpecificationMatrixTemplateLayout SMTL
				   WHERE SMTL.SpecificationCodeLayout = @TemplateCode
				 )
			BEGIN
				-- Check to see if there are actually any test columns
				SET @TestColumnCount = ( SELECT Count(SpecificationTestColumnIdLayout)
										 FROM SpecificationTestColumnLayout STCL
										 WHERE STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout
																		 FROM SpecificationMatrixTemplateLayout SMTL
																		 WHERE SMTL.SpecificationCodeLayout = @TemplateCode
																	   )
									   )
				IF @TestColumnCount > 0
					BEGIN
						-- Go Get The Columns
						CREATE TABLE #TestColumnTable (
						 	Id int not null identity,
							TestColumnIdLayout int,
							WeldTestTypeId int
						)
						INSERT INTO #TestColumnTable (TestColumnIdLayout, WeldTestTypeId)
						SELECT SpecificationTestColumnIdLayout,
							   WeldTestTypeId
						FROM   SpecificationTestColumnLayout
						WHERE  TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout
													FROM SpecificationMatrixTemplateLayout SMTL
													WHERE SMTL.SpecificationCodeLayout = @TemplateCode
												  )
					END
				-- END IF

				-- Check to see if there are actually any data columns
				SET @DataColumnCount = ( SELECT Count(SpecificationMatrixDataColumnIdLayout)
										 FROM   SpecificationMatrixDataColumnLayout STCL
										 WHERE  STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout
																		  FROM SpecificationMatrixTemplateLayout SMTL
																		  WHERE SMTL.SpecificationCodeLayout = @TemplateCode
																	    )
									   )
				IF @DataColumnCount > 0
					BEGIN
						-- Go Get The Columns
						CREATE TABLE #DataColumnTable (
							Id int not null identity,
							DataColumnIdLayout int,
							ColumnName varchar(50)
						)
						INSERT INTO #DataColumnTable (DataColumnIdLayout, ColumnName)
						SELECT SpecificationMatrixDataColumnIdLayout,
							   NameLayout
						FROM   SpecificationMatrixDataColumnLayout
						WHERE  TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout
												   	FROM SpecificationMatrixTemplateLayout SMTL
													WHERE SMTL.SpecificationCodeLayout = @TemplateCode
												  )
					END
				-- END IF

				IF @DataColumnCount > 0 OR @TestColumnCount > 0
					BEGIN
						CREATE TABLE #MasterColumnHeadings (
							ColumnId int not null identity,
							ColumnIdLayout int not null,
							ColumnName varchar(50),
							ColumnType char(4)
						)
						INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType)
						SELECT DataColumnIdLayout,
							   ColumnName,
							   'DATA'
						FROM   #DataColumnTable

						INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType)
						SELECT TCT.TestColumnIdLayout,
							   WTT.Abbreviation,
							   'TEST'
						FROM   #TestColumnTable TCT,
							   WeldTestType WTT
						WHERE  TCT.WeldTestTypeId = WTT.WeldTestTypeId
          				SELECT * FROM #MasterColumnHeadings
					END
				-- END IF
	
		END
		-- END IF
	END
GO


go

/* Create/Recreate user defined triggers for all the newly create and changed tables.         */

/* Create table level triggers for table Spool.                                               */
CREATE TRIGGER Spool_RevisionStarter ON Spool
FOR INSERT AS
	
	DECLARE @SpoolId int
	  
	BEGIN
 		SET @SpoolId = ( SELECT SpoolId FROM Inserted )
		EXEC _SpoolRevision_InsertNewRevision @SpoolId		
	END
GO


go

/* Create table level triggers for table Drawing.                                             */
CREATE TRIGGER Drawing_RevisionStarter ON Drawing
FOR INSERT AS
	DECLARE @DrawingId int
	BEGIN
 		SET @DrawingId = ( SELECT DrawingId FROM Inserted )
		EXEC _DrawingRevision_InsertNewRevision @DrawingId		
	END
GO



go

/* Create table level triggers for table Employee.                                            */
CREATE TRIGGER Welder_WelderIdStarter ON Employee
FOR INSERT AS
	DECLARE @EmployeeId int
	
	BEGIN
 		SET @EmployeeId = ( SELECT EmployeeId FROM Inserted )
		EXEC _Welder_InsertNewWelder @EmployeeId		
	END
GO


go