Difference between revisions of "Notes"
m (→Question) |
m (→Questionnaire Question) |
||
| Line 143: | Line 143: | ||
=== Questionnaire Question === | === Questionnaire Question === | ||
| + | <source lang="sql"> | ||
| + | DECLARE @Q_Id INT | ||
| + | SET @Q_Id = <Project_Questionnaire_Id> | ||
| − | + | SELECT q.*, | |
| − | + | (CASE q.[Question_Type] | |
| − | SELECT q.*, | + | WHEN 1 THEN (SELECT 'Single') |
| + | WHEN 2 THEN (SELECT 'Multi') | ||
| + | WHEN 3 THEN (SELECT 'Number') | ||
| + | WHEN 4 THEN (SELECT 'Text') | ||
| + | WHEN 5 THEN (SELECT 'Open') | ||
| + | WHEN 6 THEN (SELECT 'SingleGrid') | ||
| + | WHEN 7 THEN (SELECT 'MultiGrid') | ||
| + | WHEN 8 THEN (SELECT 'TextGrid') | ||
| + | WHEN 9 THEN (SELECT 'Page') | ||
| + | WHEN 10 THEN (SELECT 'Multimedia') | ||
| + | WHEN 11 THEN (SELECT 'Scale') | ||
| + | WHEN 12 THEN (SELECT 'ScaleGrid') | ||
| + | ELSE '' | ||
| + | END) AS [Type_Description], | ||
| + | qq.[Question_Index] | ||
| + | FROM [Question] q INNER JOIN | ||
| + | [Questionnaire_Question] qq ON q.[id] = qq.[Question_Id] | ||
| + | WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id) | ||
| + | ORDER BY qq.[Question_Index] | ||
| + | </source> | ||
=== Questionnaire Group === | === Questionnaire Group === | ||
Revision as of 03:14, 9 March 2009
Contents
Quota
RPQ is registered in the quota_resource_project_questionnaire table when:
- checkAllQuotas() cgs function is called.
- UpdateQuota function on quota tab is called
- User moves to the last question of the rpq. In this case, rpq must be completed to be in the quota_resource_project_questionnaire table. However, if user moves to the previous question from the last question, the rpq is still in the table even though it is not completed.
The difference between checkAllQuotas(), CGS function, and UpdateQuota, GUI feature:
- CheckAllQuotas(): update the RPQ's quotas status.
- UpdateQuota: update all RPQs' quotas status
Testing tips
Property Definition
Questionnaire
CREATE TABLE #Questionnaire_Property(
P_Id int,
P_Description nvarchar(50)
)
INSERT INTO #Questionnaire_Property VALUES(1,'Required')
INSERT INTO #Questionnaire_Property VALUES(16,'AllRequired')
INSERT INTO #Questionnaire_Property VALUES(17,'NotRequired')
INSERT INTO #Questionnaire_Property VALUES(18,'BackButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(19,'BackButtonText')
INSERT INTO #Questionnaire_Property VALUES(20,'CloseButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(21,'CloseButtonText')
INSERT INTO #Questionnaire_Property VALUES(22,'NextButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(23,'NextButtonText')
INSERT INTO #Questionnaire_Property VALUES(32,'Script')
INSERT INTO #Questionnaire_Property VALUES(34,'QuestionnaireNotOpenText')
INSERT INTO #Questionnaire_Property VALUES(35,'QuestionnaireUnauthorizedAccessText')
INSERT INTO #Questionnaire_Property VALUES(36,'QuestionnaireClosedText')
INSERT INTO #Questionnaire_Property VALUES(37,'QuestionnaireEndText')
INSERT INTO #Questionnaire_Property VALUES(38,'QuestionnairePausedText')
INSERT INTO #Questionnaire_Property VALUES(39,'QuestionnaireCompletedText')
INSERT INTO #Questionnaire_Property VALUES(40,'QuestionnaireBeforeStartDateText')
INSERT INTO #Questionnaire_Property VALUES(41,'QuestionnaireAfterEndDateText')
INSERT INTO #Questionnaire_Property VALUES(42,'NotRequiredAll')
INSERT INTO #Questionnaire_Property VALUES(43,'ResetButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(44,'ResetButtonText')
INSERT INTO #Questionnaire_Property VALUES(47,'QuestionnaireRequiredText')
INSERT INTO #Questionnaire_Property VALUES(48,'QuestionnaireMinRequiredText')
INSERT INTO #Questionnaire_Property VALUES(49,'QuestionnaireMaxRequiredText')
INSERT INTO #Questionnaire_Property VALUES(50,'QuestionnaireNumberRequiredText')
INSERT INTO #Questionnaire_Property VALUES(51,'QuestionnaireMinValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(52,'QuestionnaireMaxValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(53,'QuestionnaireNoFloatValText')
INSERT INTO #Questionnaire_Property VALUES(54,'QuestionnaireNumberOverflow')
INSERT INTO #Questionnaire_Property VALUES(55,'QuestionnaireInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(56,'QuestionnaireMinInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(57,'QuestionnaireMaxInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(58,'QuestionnaireIllegalTypeText')
INSERT INTO #Questionnaire_Property VALUES(68,'Countdown')
DROP TABLE #Questionnaire_Property
Question
CREATE TABLE #Question_Property(
P_Id int,
P_Description nvarchar(50)
)
INSERT INTO #Questionnaire_Property VALUES(2,'RandomizeAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(3,'RotateAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(4,'Maximum')
INSERT INTO #Questionnaire_Property VALUES(5,'Minimum')
INSERT INTO #Questionnaire_Property VALUES(6,'Dummy')
INSERT INTO #Questionnaire_Property VALUES(8,'End')
INSERT INTO #Questionnaire_Property VALUES(10,'Step')
INSERT INTO #Questionnaire_Property VALUES(11,'Discrete')
INSERT INTO #Questionnaire_Property VALUES(13,'MinimumText')
INSERT INTO #Questionnaire_Property VALUES(14,'MaximumText')
INSERT INTO #Questionnaire_Property VALUES(15,'Reverse')
INSERT INTO #Questionnaire_Property VALUES(18,'BackButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(19,'BackButtonText')
INSERT INTO #Questionnaire_Property VALUES(20,'CloseButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(21,'CloseButtonText')
INSERT INTO #Questionnaire_Property VALUES(22,'NextButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(23,'NextButtonText')
INSERT INTO #Questionnaire_Property VALUES(24,'RandomizeSubQuestions')
INSERT INTO #Questionnaire_Property VALUES(25,'RotateSubQuestions')
INSERT INTO #Questionnaire_Property VALUES(32,'Script')
INSERT INTO #Questionnaire_Property VALUES(43,'ResetButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(44,'ResetButtonText')
INSERT INTO #Questionnaire_Property VALUES(45,'Layout')
INSERT INTO #Questionnaire_Property VALUES(46,'Impsys')
INSERT INTO #Questionnaire_Property VALUES(47,'QuestionnaireRequiredText')
INSERT INTO #Questionnaire_Property VALUES(48,'QuestionnaireMinRequiredText')
INSERT INTO #Questionnaire_Property VALUES(49,'QuestionnaireMaxRequiredText')
INSERT INTO #Questionnaire_Property VALUES(50,'QuestionnaireNumberRequiredText')
INSERT INTO #Questionnaire_Property VALUES(51,'QuestionnaireMinValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(52,'QuestionnaireMaxValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(53,'QuestionnaireNoFloatValText')
INSERT INTO #Questionnaire_Property VALUES(54,'QuestionnaireNumberOverflow')
INSERT INTO #Questionnaire_Property VALUES(55,'QuestionnaireInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(56,'QuestionnaireMinInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(57,'QuestionnaireMaxInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(58,'QuestionnaireIllegalTypeText')
INSERT INTO #Questionnaire_Property VALUES(59,'EndPoints')
INSERT INTO #Questionnaire_Property VALUES(60,'PointText')
INSERT INTO #Questionnaire_Property VALUES(61,'Unit')
INSERT INTO #Questionnaire_Property VALUES(64,'AutoArrangeAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(65,'AnswerOptionColumns')
INSERT INTO #Questionnaire_Property VALUES(66,'AnswerOptionRows')
INSERT INTO #Questionnaire_Property VALUES(68,'Countdown')
INSERT INTO #Questionnaire_Property VALUES(69,'AnswerOptionUniqueChoice')
INSERT INTO #Questionnaire_Property VALUES(70,'QuestionStylesheet')
INSERT INTO #Questionnaire_Property VALUES(71,'FlashPath')
INSERT INTO #Questionnaire_Property VALUES(72,'FlashHeight')
INSERT INTO #Questionnaire_Property VALUES(73,'FlashWidth')
INSERT INTO #Questionnaire_Property VALUES(76,'RPQStatus')
INSERT INTO #Questionnaire_Property VALUES(77,'ImpSysRequiredText')
INSERT INTO #Questionnaire_Property VALUES(78,'ImpSysSelectionText')
INSERT INTO #Questionnaire_Property VALUES(79,'ImpSysChoiceText')
INSERT INTO #Questionnaire_Property VALUES(80,'ImpSysGender')
INSERT INTO #Questionnaire_Property VALUES(81,'AllowDecimals')
INSERT INTO #Questionnaire_Property VALUES(82,'AllowDecimalsInterval')
INSERT INTO #Questionnaire_Property VALUES(83,'NumericalInterval')
INSERT INTO #Questionnaire_Property VALUES(84,'ExportPosition')
INSERT INTO #Questionnaire_Property VALUES(85,'ExportLength')
INSERT INTO #Questionnaire_Property VALUES(86,'OpenAnswerRequired')
INSERT INTO #Questionnaire_Property VALUES(87,'AlphabeticalTab')
INSERT INTO #Questionnaire_Property VALUES(88,'RepresentAsDropDown')
INSERT INTO #Questionnaire_Property VALUES(89,'IsShadowQuestion')
INSERT INTO #Questionnaire_Property VALUES(90,'QuestionInCludeInPage')
DROP TABLE #Question_Property
Questionnaire Question
DECLARE @Q_Id INT
SET @Q_Id = <Project_Questionnaire_Id>
SELECT q.*,
(CASE q.[Question_Type]
WHEN 1 THEN (SELECT 'Single')
WHEN 2 THEN (SELECT 'Multi')
WHEN 3 THEN (SELECT 'Number')
WHEN 4 THEN (SELECT 'Text')
WHEN 5 THEN (SELECT 'Open')
WHEN 6 THEN (SELECT 'SingleGrid')
WHEN 7 THEN (SELECT 'MultiGrid')
WHEN 8 THEN (SELECT 'TextGrid')
WHEN 9 THEN (SELECT 'Page')
WHEN 10 THEN (SELECT 'Multimedia')
WHEN 11 THEN (SELECT 'Scale')
WHEN 12 THEN (SELECT 'ScaleGrid')
ELSE ''
END) AS [Type_Description],
qq.[Question_Index]
FROM [Question] q INNER JOIN
[Questionnaire_Question] qq ON q.[id] = qq.[Question_Id]
WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index]
Questionnaire Group
DECLARE @Q_Id INT
SET @Q_Id = <Project_Questionnaire_Id>
-- Question Group
SELECT qg.[Id],qg.[Name],qg.[Description],
(
CASE qg.[Sequence_Command_Id]
WHEN 0 THEN (SELECT 'Fixed')
WHEN 1 THEN (SELECT 'Randomized')
WHEN 2 THEN (SELECT 'Rotated')
WHEN 3 THEN (SELECT 'Randomized Subset')
WHEN 4 THEN (SELECT 'Rotated Subset')
ELSE ''
END
) AS 'Group_Type',
qg.[Select_Count],
(
SELECT [Question_Index] FROM [Questionnaire_Question] WHERE [Question_Id] = qq.[Question_Id]
) AS 'Question_Index',
(
SELECT [Label] FROM [Question] WHERE [Id] = qq.[Question_Id]
) AS 'Question_Label'
FROM [Questiongroup] qg LEFT JOIN
[Questionnaire_Question]qq ON qg.[Id] = qq.[Questiongroup_Id]
WHERE qg.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
AND qg.[Id] NOT IN (SELECT [Parent_Questiongroup_Id] FROM [Questiongroup] WHERE [Parent_Questiongroup_Id] IS NOT NULL)
ORDER BY qg.[Id]
-- Higher Level Question Group
SELECT qg_1.[Id],qg_1.[Name] AS 'Higher Group',qg_1.[description],
(
CASE qg_1.[Sequence_Command_Id]
WHEN 0 THEN (SELECT 'Fixed')
WHEN 1 THEN (SELECT 'Randomized')
WHEN 2 THEN (SELECT 'Rotated')
WHEN 3 THEN (SELECT 'Randomized Subset')
WHEN 4 THEN (SELECT 'Rotated Subset')
ELSE ''
END
) AS 'Group_Type',
qg_1.[Select_Count],
qg_2.[Name] AS 'Sub_Group'
FROM [Questiongroup] qg_1 INNER JOIN
[Questiongroup] qg_2 ON qg_1.[Id] = qg_2.[Parent_Questiongroup_Id]
WHERE qg_1.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qg_1.[Id]
Questionnaire Property
DECLARE @Q_Id INT
SET @Q_Id = <Project_Questionnaire_Id>
SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',
qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text]
FROM [Questionnaire_Property] qnp LEFT JOIN
[Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id]
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qnp.[Property_Type]
Language Independent
DECLARE @Q_Id INT
SET @Q_Id = <Project_Questionnaire_Id>
SELECT qnp.[Id],qnp.[Property_Type], qnp.[Value]
FROM [Questionnaire_Property] qnp
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
AND qnp.[id] NOT IN (SELECT [Questionnaire_Property_id] FROM [Questionnaire_Property_Language])
ORDER BY qnp.[Property_Type]
Language Dependent
DECLARE @Q_Id INT
SET @Q_Id = <Project_Questionnaire_Id>
SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',
qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text]
FROM [Questionnaire_Property] qnp INNER JOIN
[Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id]
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qnp.[Property_Type]
Sample query
Template Property
DECLARE @Template_Id INT
SET @Template_Id = <Resource_Template_Id>
SELECT rtr.[Id] AS 'Template_Id', rtr.[Short_Name] AS 'Template',
co.[Index],co.[Id] AS 'Constant_Option_Id', co.[Label] AS 'Constant_Option_Label',
( cASe co.[Type]
WHEN 1 THEN (SELECT 'Number')
WHEN 2 THEN (SELECT 'Boolean')
WHEN 3 THEN (SELECT 'String')
WHEN 4 THEN (SELECT 'DateTime')
WHEN 5 THEN (SELECT 'Single')
WHEN 6 THEN (SELECT 'Multi')
WHEN 7 THEN (SELECT 'Resource')
ELSE ''
END
)AS 'Constant_Type',
ocl.[Option_Choice_Id], ocl.[Language_Id],
(SELECT [Name] FROM [Language] WHERE [Id] = ocl.[Language_Id]) AS 'Language Name',
ocl.[Name] AS 'Option_Choice_Name'
FROM [Resource_Template_Resource] rtr INNER JOIN
[Constant_Option]co ON rtr.[Resource_UniqueId] = co.[Resource_Id_fk] LEFT JOIN
[Option_Choice] oc ON co.[Id] = oc.[Constant_Option_Id] LEFT JOIN
[Option_Choice_Language] ocl ON oc.[Id] = ocl.[Option_Choice_Id]
WHERE rtr.[Id] = @Template_Id
ORDER BY co.[Index]
