IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWeldsByNdtRuleId]'))
DROP PROCEDURE [dbo].[GetWeldsByNdtRuleId]
GO

CREATE PROCEDURE GetWeldsByNdtRuleId
@RuleId Uniqueidentifier
AS
BEGIN
DECLARE @Welds TABLE (WeldId Uniqueidentifier)
DECLARE @WeldId Uniqueidentifier
DECLARE @HasPassed Bit

DECLARE cur CURSOR LOCAL FOR SELECT WeldId FROM Weld --WHERE WeldCompletedDate IS NOT NULL AND ExcludeFromLotCheck <> 1
OPEN cur
FETCH NEXT FROM cur INTO @WeldId

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC CheckWeldAgainstRule @WeldId, @RuleId, @HasPassed OUTPUT
IF (@HasPassed = 1)
INSERT INTO @Welds (WeldId) VALUES (@WeldId)
FETCH NEXT FROM cur INTO @WeldId
END

SELECT * FROM Weld WHERE WeldId IN (SELECT WeldId FROM @Welds)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckWeldAgainstRule]'))
DROP PROCEDURE [dbo].[CheckWeldAgainstRule]
GO

CREATE PROCEDURE CheckWeldAgainstRule
@WeldId Uniqueidentifier,
@RuleId Uniqueidentifier,
@HasPassed Bit OUTPUT
AS
BEGIN

DECLARE @Conditions TABLE (
ConditionId Uniqueidentifier,
Section Varchar(50),
Field VarChar(50),
ConditionType Varchar(50),
ConditionValue Varchar(Max),
BasedOnField Varchar(50),
BasedOnCondition Varchar(50),
BasedOnValue Varchar(Max),
RuleSet Int,
HasPassed Int
)

INSERT INTO @Conditions
SELECT NdtRuleConditionId, SectionName, F1.FieldName, C1.ConditionName, ConditionValue, F2.FieldName, C2.ConditionName, BasedOnValue, RuleSet, 0
FROM NdtRuleCondition
INNER JOIN DatasheetSection ON DatasheetSection.SectionId = NdtRuleCondition.SectionId
INNER JOIN DatasheetField F1 ON F1.FieldId = NdtRuleCondition.FieldId
LEFT JOIN DatasheetField F2 ON F2.FieldId = NdtRuleCondition.BasedonFieldId
INNER JOIN ConditionType C1 ON C1.ConditionTypeId = NdtRuleCondition.ConditionTypeId
LEFT JOIN ConditionType C2 ON C2.ConditionTypeId = NdtRuleCondition.BasedOnConditionId
WHERE NdtRuleId = @RuleId

DECLARE @ConditionId Uniqueidentifier
DECLARE @Section Varchar(50)
DECLARE @Field VarChar(50)
DECLARE @Condition Varchar(50)
DECLARE @Value Varchar(Max)
DECLARE @BasedOnField Varchar(50)
DECLARE @BasedOnCondition Varchar(50)
DECLARE @BasedOnValue Varchar(Max)

DECLARE cur CURSOR LOCAL FOR SELECT ConditionId, Section, Field, ConditionType, ConditionValue, BasedOnField, BasedOnCondition, BasedOnValue FROM @Conditions
OPEN cur
FETCH NEXT FROM cur INTO @ConditionId, @Section, @Field, @Condition, @Value, @BasedOnField, @BasedOnCondition, @BasedOnValue

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC CheckRuleCondition @WeldId, @Section, @Field, @Condition, @Value, @BasedOnField, @BasedOnCondition, @BasedOnValue, @HasPassed OUTPUT
UPDATE @Conditions SET HasPassed = @HasPassed WHERE ConditionId = @ConditionId
FETCH NEXT FROM cur INTO @WeldId, @Section, @Field, @Condition, @Value, @BasedOnField, @BasedOnCondition, @BasedOnValue
END

CLOSE cur
DEALLOCATE cur

SET @HasPassed = CASE WHEN EXISTS (SELECT RuleSet FROM @Conditions GROUP BY RuleSet HAVING MIN(HasPassed) <> 0) THEN 1 ELSE 0 END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckRuleCondition]'))
DROP PROCEDURE [dbo].[CheckRuleCondition]
GO

CREATE PROCEDURE CheckRuleCondition
@WeldId Uniqueidentifier,
@Section Varchar(50),
@Field Varchar(50),
@Condition Varchar(50),
@ExpectedValue Varchar(Max),
@BasedOnField Varchar(50),
@BasedOnCondition Varchar(50),
@BasedOnValue Varchar(Max),
@HasPassed Bit OUTPUT
AS
BEGIN

DECLARE @SqlQuery NVarchar(4000)
DECLARE @ActualValue Varchar(Max)
DECLARE @Values TABLE ( ActualValue Varchar(Max) )

IF (@Section IN ('Welder', 'Filler'))
SET @SqlQuery = 'INSERT INTO @Values SELECT DISTINCT ' + @Field + ' FROM ' + @Section + ', Pass WHERE Pass.WeldId = Weld.WeldId AND Weld.WeldId = @WeldId';
ELSE
SET @SqlQuery = 'SELECT TOP 1 @ActualValue = ' + @Field + ' FROM ' + @Section + ' WHERE WeldId = @WeldId';

IF (@BasedOnField IS NOT NULL AND @BasedOnCondition = 'Minimum')
SET @SqlQuery = @SqlQuery + ' AND ' + @BasedOnField + ' = Min(' + @BasedOnField + ')'
ELSE IF (@BasedOnField IS NOT NULL AND @BasedOnCondition = 'Maximum')
SET @SqlQuery = @SqlQuery + ' AND ' + @BasedOnField + ' = Max(' + @BasedOnField + ')'
ELSE IF (@BasedOnField IS NOT NULL AND @BasedOnCondition = 'Equals')
SET @SqlQuery = @SqlQuery + ' AND ' + @BasedOnField + ' = @BasedOnValue'

EXEC sp_executesql @SqlQuery, N'@WeldId Uniqueidentifier, @BasedOnValue Varchar(4000), @ActualValue Varchar(4000) OUTPUT', @Weldid, @BasedOnValue, @ActualValue OUTPUT

IF (@Section IN('Welder', 'Filler'))
BEGIN

DECLARE cur CURSOR LOCAL FOR SELECT ActualValue FROM @Values
OPEN cur
FETCH NEXT FROM cur INTO @ActualValue
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @HasPassed = dbo.CompareValues(@ActualValue, @ExpectedValue, @Condition)
END TRY
BEGIN CATCH
SET @HasPassed = 0
END CATCH
IF (@HasPassed = 0)
FETCH NEXT FROM cur INTO @ActualValue
END
CLOSE cur
DEALLOCATE cur
END

ELSE
BEGIN TRY
SET @HasPassed = dbo.CompareValues(@ActualValue, @ExpectedValue, @Condition)
END TRY
BEGIN CATCH
SET @HasPassed = 0
END CATCH
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompareValues]'))
DROP FUNCTION CompareValues
GO

CREATE FUNCTION CompareValues (@ActualValue Varchar(Max), @ExpectedValue Varchar(Max), @Condition Varchar(50))
RETURNS Bit
AS
BEGIN
SET @ActualValue = LTRIM(RTRIM(LOWER(@ActualValue)))
SET @ExpectedValue = LTRIM(RTRIM(LOWER(@ExpectedValue)))

RETURN CASE
WHEN @Condition = 'Equals' AND @ActualValue = @ExpectedValue THEN 1
WHEN @Condition = 'Not Equal' AND @ActualValue <> @ExpectedValue THEN 1
WHEN @Condition = 'Greater Than' AND @ActualValue > @ExpectedValue THEN 1
WHEN @Condition = 'Greater Than or Equal' AND @ActualValue >= @ExpectedValue THEN 1
WHEN @Condition = 'Less Than' AND @ActualValue < @ExpectedValue THEN 1
WHEN @Condition = 'Less Than or Equal' AND @ActualValue <= @ExpectedValue THEN 1
WHEN @Condition = 'Contains' AND @ActualValue LIKE '%' + @ExpectedValue + '%' THEN 1
WHEN @Condition = 'Does Not Contain' AND @ActualValue NOT LIKE '%' + @ExpectedValue + '%' THEN 1
WHEN @Condition = 'Starts With' AND @ActualValue LIKE '%' + @ExpectedValue THEN 1
WHEN @Condition = 'Ends With' AND @ActualValue LIKE @ExpectedValue + '%' THEN 1
WHEN @Condition = 'Is One Of' AND ',' + @ExpectedValue + ',' LIKE '%,' + @ActualValue + ',%' THEN 1
WHEN @Condition = 'Is Not One Of' AND ',' + @ExpectedValue + ',' NOT LIKE '%,' + @ActualValue + ',%' THEN 1
WHEN @Condition = 'Has Any Value' AND @ActualValue IS NOT NULL THEN 1
ELSE 0 END
END
GO

EXEC GetWeldsByNdtRuleId '1227862D-6337-429E-9C07-2DC9CA0FF5BE'