Welcome to the Lounge

Super Lloyd wrote:

Amongst other thing I don't quite think it is possible. I do a join with a user defined table type, which would be a join between an IList and an IQueryable. Not supported by LINQ

Super Lloyd wrote:

I jump happily at SQL for the remaining nasty bits!

DELETE FROM table

RTF
    int ID
    string Name
    AnswerSet Answer

AnswerSet
    DateTime LastModified
    string ModifiedBy
    List<Answer>

Answer
    int questionID
    AnswerType type // string / num / bool / date / List
    string vString
    decimal vNum
    Date vDate
    bool vBool
    List<Option> vOptions

Option
    int ID
    string Name

Criteria
    int groupID
    bool isGroupAndOrOr
    CriteriaType cType // LIKE / UNLIKE / MISSING / = != < >
    int? questionID // match questionID in Answer
    int? wellKnowID // or match RTF.Name, RTF.Answer.LastModified,, RTF.Answer.ModifiedBy
    object value

Super Lloyd wrote:

might put it on weird and wonderful tomorrow

BEGIN TRANSACTION

	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[question].[Search]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [question].[Search]

	IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'Criteria' AND ss.name = N'question')
	DROP TYPE [question].[Criteria]

	GO

	CREATE TYPE [question].[Criteria] AS TABLE(
		ID int not null,
		groupID int NOT NULL,
		groupType int NOT NULL, -- 0 any criteria / either of, 1 all criteria / all of
		questionID int NULL, -- question ID or null for questionType >= 10
		wellKnownQuestionID int NULL, -- for hard coded well known question, i.e. 1: Appli.Ident.UserName, 2:Appli.LastModified, ...
		questionScore int NULL,
		op int NOT NULL, -- operators: missing, like, not like, ==, !=, <, <=, >, >=
		valueText nvarchar(max) NULL,
		valueBit bit NULL,
		valueNum numeric(18, 5) NULL,
		valueDate datetime2(7) NULL,
		valueList INT NULL
	)
	GO

	-- This will need to search / return records like (RTF, Applicant, Identity, AnswerSet)
	-- History is implicit in the various version of the Identity and AnswerSet tables

	CREATE PROCEDURE [question].[Search] 
		@maxResult INT,
		@criteria question.Criteria READONLY
	AS
	BEGIN
		SET NOCOUNT ON;
	
		-- START CONSTANTS ------------------
		-- group type
		DECLARE @grEitherOf INT = 0
		DECLARE @grAllOf INT = 1

		-- question type, should match what's in question.Questions.type
		DECLARE @qBool INT = 0
		DECLARE @qText INT = 1
		DECLARE @qNum  INT = 2
		DECLARE @qDate INT = 3
		DECLARE @qList INT = 4

		-- op types, should match question.Criteria.op
		-- REMARK: 
		--      NO 'IN', 'NOT IN', 'CONTAINS' criteria, they might be confusing when grouped with other question
		--      User 'Either Of' filter and add multiple answers
		DECLARE @opMissing INT = 0
		DECLARE @opLike INT = 1
		DECLARE @opUnlike INT = 2
		DECLARE @opEQ INT = 3 -- ==
		DECLARE @opNEQ INT = 4 -- !=
		DECLARE @opLT INT = 5 -- <<
		DECLARE @opLTE INT = 6 -- <=
		DECLARE @opGT INT = 7 -- >
		DECLARE @opGTE INT = 8 -- >=

		-- default value when one is needed
		DECLARE @vBit  BIT = 0
		DECLARE @vInt  INT = -1 -- also wouldn't link to any record / i.e. is not a valid PK value
		DECLARE @vNum  decimal(18,5) = 0
		DECLARE @vChar nvarchar(max) = ''
		DECLARE @vDate datetime2(7) = sysdatetime()
		-- END CONSTANTS ------------------

		-- optimization table: all questions by questionnaire (it's an easy join, yet used so many times)
		DECLARE @questions TABLE (QQID INT, qID INT, qType INT)
		; WITH questions AS (
			SELECT QQ.ID QQID, q.ID qID, q.[type]
			FROM question.Questionaires QQ
			INNER JOIN question.QuestionaireGroups Qg ON QG.QuestionaireID = QQ.ID
			INNER JOIN question.QGroups g ON g.ID = Qg.QGroupID
			INNER JOIN question.QGroupQuestions gq on gq.QuestionGroupID = g.ID
			INNER JOIN question.Questions q on q.ID = gq.QuestionID
		)
		INSERT INTO @questions(QQID, qID, qType)
		SELECT * FROM questions

		-- results will be stored there, 
		DECLARE @Results TABLE (ID INT, Score INT);

		-- enumerate all RTF/AnswerSet chunk by chunk
		DECLARE @startID INT = 0
		DECLARE @maxPK INT = (SELECT MAX(ID) FROM question.QAnswerSets)
		WHILE @startID <= @maxPK
		BEGIN

			DECLARE @srcIds TABLE (ID INT) -- the ID to look for each iteration
			DELETE FROM @srcIds -- do I need to do that? it's because of the looping...

			-- looping the database by chunk, i.e. reduce memory (lots of cross join of humongous table, do not read the whole DB at once!!)
			-- apply security filter here to select TOP N available RTF
			INSERT INTO @srcIds (ID)
			SELECT TOP 10000 ID -- CHUNK SIZE SET HERE
			FROM question.QAnswerSets
			WHERE ID >= @startID
			-- TODO SECURITY FILTER HERE!!
			ORDER BY ID ASC

			IF NOT EXISTS (SELECT * FROM @srcIds) BREAK
			SELECT @startID = 1 + (SELECT MAX(ID) FROM @srcIds)

			-- check that there are any criteria
			IF NOT EXISTS (SELECT * FROM @criteria)
			BEGIN
				INSERT INTO @Results (ID, Score)
				SELECT ID, 0 FROM @srcIds

				IF (SELECT COUNT(*) FROM @Results) >= @maxResult BREAK
				CONTINUE
			END

			-- TODO here, find all combination of (RTFID, ApplicantID, IdentityID, AnswerSetId)
			-- (N) RTF -- N --> Applicant
			--                           -- 2 --> Identity (Applicant.Identity, Applicant.Contract_Letter.Identity) + Audit Log
			--                           -- 1 --> App Form, Assess Form, + Audit Log

			DECLARE @CritData TABLE (AnswerSetID INT, CriteriaID INT, AnswerCount INT, [TYPE] INT, BOOL BIT, [TEXT] nvarchar(max), [NUM] decimal(18, 5), [DATE] datetime2(7), [LIST] INT)
			DECLARE @CritResult TABLE (AnswerSetID INT, CriteriaID INT, Value BIT)
			DELETE FROM @CritData -- do I need to do that? it's because of the looping...
			DELETE FROM @CritResult

			-- get the relevant value for each criteria
			; WITH criteria_data AS (
				-- select well known fields
				SELECT [AS].ID AnswerSetID, c.ID CriteriaID, @vInt AnswerID,
					-- ====== START CUSTOM AREA ========
					-- well known IDs:
					-- 0: UserName, 1: LastModified
					(CASE c.wellKnownQuestionID
						WHEN 0 THEN @qText -- UserName
						WHEN 1 THEN @qDate -- LastModified
						ELSE @vInt END
					) [TYPE], 
					(CASE c.wellKnownQuestionID
						WHEN -1 THEN @vBit
						ELSE @vBit END
					) BOOL, 
					(CASE c.wellKnownQuestionID 
						WHEN 0 THEN [AS].UserName
						ELSE @vChar END
					) [TEXT], 
					(CASE c.wellKnownQuestionID 
						WHEN -1 THEN @vInt
						ELSE @vInt END
					) [NUM], 
					(CASE c.wellKnownQuestionID 
						WHEN 1 THEN [AS].LastModified
						ELSE @vDate END
					) [DATE], 
					(CASE c.wellKnownQuestionID 
						WHEN -1 THEN @vInt
						ELSE @vInt END
					) [LIST] 
					-- ====== END CUSTOM AREA ========
				FROM question.QAnswerSets [AS], @criteria c
				WHERE [AS].ID IN (SELECT * FROM @srcIds) AND c.wellKnownQuestionID IS NOT NULL
				-- and
				UNION ALL
				-- select dynamic forms
				SELECT 
					[AS].ID, c.ID, A.ID, qType
					, A.Abool, A.Atext, A.Anumeric, A.Adate, A.Alist
				FROM question.QAnswerSets [AS]
				INNER JOIN @questions QTN on QTN.QQID = [AS].QuestionaireID
				INNER JOIN @criteria c on c.questionID = QTN.qID
				LEFT OUTER JOIN question.QAnswer A ON A.SetID = [AS].ID AND A.QuestionID = QTN.qID
				WHERE [AS].ID IN (SELECT * FROM @srcIds) AND c.questionID IS NOT NULL
			)
			-- aggregate (multi) list into 1 value (number of match is what we search for)
			INSERT INTO @CritData (AnswerSetID, CriteriaID, AnswerCount, [TYPE], BOOL, [TEXT], [NUM], [DATE], [LIST])
			SELECT AnswerSetID, CriteriaID
				, COUNT(CASE AnswerID WHEN -1 THEN NULL ELSE 1 END)
				, [TYPE], BOOL, [TEXT], [NUM], [DATE]
				, COUNT(CASE WHEN cd.LIST = c.valueList THEN 1 ELSE NULL END) LIST
			FROM criteria_data cd
			INNER JOIN @criteria c ON c.ID = cd.CriteriaID
			GROUP BY AnswerSetID, CriteriaID, [TYPE], BOOL, [TEXT], [NUM], [DATE]

			-- now compute criteria value for each criteria
			INSERT INTO @CritResult (AnswerSetID, CriteriaID, Value)
			SELECT cd.AnswerSetID, cd.CriteriaID,
			(CASE c.op
				WHEN @opMissing THEN (CASE AnswerCount WHEN 0 THEN 1 ELSE 0 END)
				WHEN @opLike THEN (
					CASE [TYPE] 
					WHEN @qText THEN CASE WHEN [TEXT] LIKE ('%' + c.valueText + '%') THEN 1 ELSE 0 END
					WHEN @qNum  THEN CASE WHEN [NUM]  LIKE ('%' + c.valueNum  + '%') THEN 1 ELSE 0 END
					ELSE 0 END
				)
				WHEN @opUnlike THEN (
					CASE [TYPE] 
					WHEN @qText THEN CASE WHEN [TEXT] LIKE ('%' + c.valueText + '%') THEN 0 ELSE 1 END
					WHEN @qNum  THEN CASE WHEN [NUM]  LIKE ('%' + c.valueNum  + '%') THEN 0 ELSE 1 END
					ELSE 0 END
				)
				WHEN @opEQ THEN (
					CASE [TYPE] 
					WHEN @qText THEN CASE WHEN [TEXT] = c.valueText THEN 1 ELSE 0 END
					WHEN @qNum  THEN CASE WHEN [NUM]  = c.valueNum  THEN 1 ELSE 0 END
					WHEN @qBool THEN CASE WHEN [BOOL] = c.valueBit  THEN 1 ELSE 0 END
					WHEN @qDate THEN CASE WHEN [DATE] = c.valueDate THEN 1 ELSE 0 END
					WHEN @qList THEN CASE WHEN [LIST] > 0           THEN 1 ELSE 0 END
					ELSE 0 END
				)
				WHEN @opNEQ THEN (
					CASE [TYPE] 
					WHEN @qText THEN CASE WHEN [TEXT] = c.valueText THEN 0 ELSE 1 END
					WHEN @qNum  THEN CASE WHEN [NUM]  = c.valueNum  THEN 0 ELSE 1 END
					WHEN @qBool THEN CASE WHEN [BOOL] = c.valueBit  THEN 0 ELSE 1 END
					WHEN @qDate THEN CASE WHEN [DATE] = c.valueDate THEN 0 ELSE 1 END
					WHEN @qList THEN CASE WHEN [LIST] > 0           THEN 0 ELSE 1 END
					ELSE 0 END
				)
				WHEN @opLT THEN (
					CASE [TYPE] 
					WHEN @qNum  THEN CASE WHEN [NUM]  < c.valueNum  THEN 1 ELSE 0 END
					WHEN @qDate THEN CASE WHEN [DATE] < c.valueDate THEN 1 ELSE 0 END
					ELSE 0 END
				)
				WHEN @opLTE THEN (
					CASE [TYPE] 
					WHEN @qNum  THEN CASE WHEN [NUM]  <= c.valueNum  THEN 1 ELSE 0 END
					WHEN @qDate THEN CASE WHEN [DATE] <= c.valueDate THEN 1 ELSE 0 END
					ELSE 0 END
				)
				WHEN @opGT THEN (
					CASE [TYPE] 
					WHEN @qNum  THEN CASE WHEN [NUM]  > c.valueNum  THEN 1 ELSE 0 END
					WHEN @qDate THEN CASE WHEN [DATE] > c.valueDate THEN 1 ELSE 0 END
					ELSE 0 END
				)
				WHEN @opGTE THEN (
					CASE [TYPE] 
					WHEN @qNum  THEN CASE WHEN [NUM]  >= c.valueNum  THEN 1 ELSE 0 END
					WHEN @qDate THEN CASE WHEN [DATE] >= c.valueDate THEN 1 ELSE 0 END
					ELSE 0 END
				)
				-- should not go there
				ELSE 0 END
			) Value
			FROM @CritData cd
			INNER JOIN @criteria c on c.ID = cd.CriteriaID

			-- group criteria by answer set, and insert successful record in result table
			; WITH filter as (
				SELECT AnswerSetID, SUM(Score) Score
				FROM (
					SELECT rr.AnswerSetID, c.groupID
						, CASE 
							WHEN c.groupType = @grEitherOf AND (COUNT(CASE rr.Value WHEN 1 THEN 1 ELSE NULL END) > 0) THEN 1 -- either of, i.e. at least one success
							WHEN c.groupType = @grAllOf AND (COUNT(CASE rr.Value WHEN 0 THEN 1 ELSE NULL END) = 0) THEN 1 -- all of, i.e. no failure
							ELSE 0
							END GResult
						, SUM(CASE rr.Value WHEN 1 THEN c.questionScore ELSE 0 END) Score
					FROM @CritResult rr
					INNER JOIN @criteria c on c.ID = rr.CriteriaID
					GROUP BY rr.AnswerSetID, c.groupID, c.groupType
				) GroupResults
				GROUP BY AnswerSetID
				HAVING COUNT(CASE GResult WHEN 0 THEN 1 ELSE NULL END) = 0 -- only select answerSet with all groups successful
			)
			INSERT INTO @Results (ID, Score)
			SELECT AnswerSetID, Score FROM filter

			-- next iteration 
			IF (SELECT COUNT(*) FROM @Results) >= @maxResult BREAK
		END


		-- return desired result summary info
		SELECT
			[AS].ID ID, Score
			, QQ.ID QID, QQ.Name, [AS].LastModified, [AS].UserName
		FROM @Results AR
		INNER JOIN question.QAnswerSets [AS] on [AS].ID = AR.ID
		INNER JOIN question.Questionaires QQ ON QQ.ID = [AS].QuestionaireID

	END

	GO

IF @@error <> 0
    BEGIN
    RAISERROR ('problem',0,0)
    ROLLBACK TRANSACTION
    RETURN
    END
COMMIT

RTF
    int ID
    string Name
    AnswerSet Answer

AnswerSet
    DateTime LastModified
    string ModifiedBy
    List<Answer>

Answer
    int questionID
    AnswerType type // string / num / bool / date / List
    string vString
    decimal vNum
    Date vDate
    bool vBool
    List<Option> vOptions

Option
    int ID
    string Name

Criteria
    int groupID
    bool isGroupAndOrOr
    CriteriaType cType // LIKE / UNLIKE / MISSING / = != < >
    int? questionID // match questionID in Answer
    int? wellKnowID // or match RTF.Name, RTF.Answer.LastModified,, RTF.Answer.ModifiedBy
    object value

Super Lloyd wrote:

Now tell me how you can do this search in just 1 SIMPLE DB CONNECTION AND CALL