Difference between revisions of "Notes"
m (→PQ's Data) |
m (→Cati list) |
||
(14 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | == Test case == | ||
+ | |||
+ | === Test case #1 === | ||
+ | |||
+ | {| cellspacing="0" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="50%" | '''Test case #:''' 1 | ||
+ | | width="50%" | '''Test Case Name:''' Create a new RCS | ||
+ | |- | ||
+ | | '''System:''' Catglobe | ||
+ | | '''Subsystem:''' RCS | ||
+ | |- | ||
+ | | '''Designed by:''' Trong D.Bui | ||
+ | | '''Design Date:''' 30-03-2009 | ||
+ | |- | ||
+ | | '''Executed by:''' | ||
+ | | '''Execution Date:''' | ||
+ | |- | ||
+ | | colspan="2" | | ||
+ | '''Short description:''' | ||
+ | |||
+ | Create a new RCS of a resouce type having resource properties. | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Pre-conditions:''' | ||
+ | |||
+ | The user has enough NAV to generate RCS of the selected resource type.<br>The openning resource list's template has a resource property. | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="5%" | '''Step''' | ||
+ | | width="30%" | '''Action''' | ||
+ | | width="40%" | '''Expected System Response''' | ||
+ | | width="5%" | '''Pass/Fail''' | ||
+ | | width="20%" | '''Comment''' | ||
+ | |- | ||
+ | | 1. | ||
+ | | Generate the RCS from the openning resource list<br> (Tools/Generate resource cache...) | ||
+ | | The RCS Editor is opened.<br> Default search criteria is set to the resource template.<br> All resource properties are listed. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 2. | ||
+ | | Check/Uncheck a resource property | ||
+ | | The property is selected/not selected. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 3. | ||
+ | | Select a resource property and Save the RCS | ||
+ | | The new RCS is saved with the current setting. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 4. | ||
+ | | Go to the DCS resource list, search and open the RCS. | ||
+ | | The RCS Editor is opened with saved information. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 5. | ||
+ | | Open the RCS's Axis set tab | ||
+ | | There is an axis, named as "Property_<Property_Name>", on the Axis list. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 6. | ||
+ | | Select the axis | ||
+ | | The axis is created with following information:<br> + Axis text: "Property_"<Property_Name><br> + Axis math-base: "Property_"<Property_Id><br> + Math-base modifier: <empty><br> + Axis pct_base: "Property_"<Property_Id> != emtpy<br> + Use for weighting: <false><br> + Is default: <yes><br> + Options: an option is created for each uniqued resources selected in the resoures having this resource property:<br> _Option name: <selected resource's qualify name><br> _Option value: "Property_"<property_id> == <selected resource's uniqueID> | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 7. | ||
+ | | View the RCS | ||
+ | | The RCS's viewer is opened with built data.<br> A new column, named "Property_"<property_Id> is added for each resource property.<br> Value in the column is the resource uniqueId of the selected resource. | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 8. | ||
+ | | Close the Viewer | ||
+ | | The viewer is closed | ||
+ | | | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Post-conditions:''' | ||
+ | |||
+ | The RCS is created with correct information. | ||
+ | |||
+ | The property values can be collected correctly.<br> | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | === Test case #2 === | ||
+ | {| cellspacing="0" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="50%" | '''Test case #:''' | ||
+ | | width="50%" | '''Test Case Name:''' | ||
+ | |- | ||
+ | | '''System:''' | ||
+ | | '''Subsystem:''' | ||
+ | |- | ||
+ | | '''Designed by:''' | ||
+ | | '''Design Date:''' | ||
+ | |- | ||
+ | | '''Executed by:''' | ||
+ | | '''Execution Date:''' | ||
+ | |- | ||
+ | | colspan="2" | '''Short description:''' | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Pre-conditions:''' | ||
+ | |||
+ | <br> | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="5%" | '''Step''' | ||
+ | | width="30%" | '''Action''' | ||
+ | | width="40%" | '''Expected System Response''' | ||
+ | | width="5%" | '''Pass/Fail''' | ||
+ | | width="20%" | '''Comment''' | ||
+ | |- | ||
+ | | 1. | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 2. | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Post-conditions:''' | ||
+ | |||
+ | <br> | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | |||
+ | === Test case #3 === | ||
+ | {| cellspacing="0" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="50%" | '''Test case #:''' | ||
+ | | width="50%" | '''Test Case Name:''' | ||
+ | |- | ||
+ | | '''System:''' | ||
+ | | '''Subsystem:''' | ||
+ | |- | ||
+ | | '''Designed by:''' | ||
+ | | '''Design Date:''' | ||
+ | |- | ||
+ | | '''Executed by:''' | ||
+ | | '''Execution Date:''' | ||
+ | |- | ||
+ | | colspan="2" | '''Short description:''' | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Pre-conditions:''' | ||
+ | |||
+ | <br> | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | width="5%" | '''Step''' | ||
+ | | width="30%" | '''Action''' | ||
+ | | width="40%" | '''Expected System Response''' | ||
+ | | width="5%" | '''Pass/Fail''' | ||
+ | | width="20%" | '''Comment''' | ||
+ | |- | ||
+ | | 1. | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | 2. | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |- | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
+ | {| cellspacing="1" cellpadding="1" width="100%" border="1" | ||
+ | |- | ||
+ | | | ||
+ | '''Post-conditions:''' | ||
+ | |||
+ | <br> | ||
+ | |||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | |||
== Quota == | == Quota == | ||
Line 13: | Line 278: | ||
*CheckAllQuotas(): update the RPQ's quotas status. | *CheckAllQuotas(): update the RPQ's quotas status. | ||
*UpdateQuota: update all RPQs' quotas status | *UpdateQuota: update all RPQs' quotas status | ||
+ | |||
+ | == Cati list == | ||
+ | *1: Main list | ||
+ | *2: On going | ||
+ | *3: Appointment | ||
+ | *4: Finish | ||
+ | *5: Retry | ||
+ | |||
+ | |||
+ | *InterveiwSucceeded, InterviewFail, QuotaFull, Outside Target: is set to Finish. | ||
== Testing tips == | == Testing tips == | ||
Line 434: | Line 709: | ||
AND rpq.[Deleted_Date] IS NULL | AND rpq.[Deleted_Date] IS NULL | ||
AND rpq.[Test] = 0 | AND rpq.[Test] = 0 | ||
+ | </source> | ||
+ | |||
+ | === PQ's quotas === | ||
+ | <source lang="sql"> | ||
+ | DECLARE @pqName NVARCHAR(200) | ||
+ | DECLARE @pqId INT | ||
+ | |||
+ | SET @pqName = <Project_Questionnaire_Short_Name> | ||
+ | SELECT @pqId = [Id] | ||
+ | FROM [Project_Questionnaire_Resource] | ||
+ | WHERE [Short_Name] = @pqName | ||
+ | |||
+ | SELECT qo.[Id], qo.[Name], qo.[Target], | ||
+ | ( | ||
+ | SELECT count([Resource_Project_Questionnaire_Id]) | ||
+ | FROM [Quota_Resource_Project_Questionnaire] | ||
+ | WHERE [Quota_Id] = qo.[Id] | ||
+ | AND [Resource_Project_Questionnaire_Id] in (SELECT [Id] | ||
+ | FROM [Resource_Project_Questionnaire] | ||
+ | WHERE [Project_Questionnaire_Id] = @pqId | ||
+ | AND [Deleted_Date] IS NULL | ||
+ | AND [Test] = 0 | ||
+ | AND [Completed] = 1 | ||
+ | AND [End_Date] IS NOT NULL | ||
+ | AND [Status] in (1,5) | ||
+ | ) | ||
+ | ) AS [Current], qo.[Expression], qo.[Description], qo.[Disabled], qo.[Pre_Stratification] | ||
+ | FROM [Quota] qo | ||
+ | WHERE qo.[Project_Questionnaire_Id] = @pqId | ||
+ | |||
+ | SELECT [Status], count(*) | ||
+ | FROM [Resource_Project_Questionnaire] | ||
+ | WHERE [Project_Questionnaire_Id] = @pqId | ||
+ | GROUP BY [Status] | ||
+ | ORDER BY [Status] | ||
+ | |||
+ | SELECT * | ||
+ | FROM [Resource_Project_Questionnaire] | ||
+ | WHERE [Project_Questionnaire_Id] = @pqId | ||
+ | |||
</source> | </source> | ||
Line 468: | Line 783: | ||
WHERE rtr.[Id] = @Template_Id | WHERE rtr.[Id] = @Template_Id | ||
ORDER BY co.[Index] | ORDER BY co.[Index] | ||
+ | </source> | ||
+ | |||
+ | == Sample javascript == | ||
+ | === Email address validation === | ||
+ | Old: | ||
+ | <source lang="javascript"> | ||
+ | var normalQuestionCheck = questioncheck; | ||
+ | this.questioncheck = function(){ | ||
+ | var valid = normalQuestionCheck(); | ||
+ | if (valid) { | ||
+ | var val; | ||
+ | //The value below is the index value of the sub question where your e-mail question is asked. | ||
+ | //In this case we use the sub question has index 2 | ||
+ | val = document["query"][quest.label + "." + 2].value; | ||
+ | if (val.length > 0) | ||
+ | valid = validateEmail(val); | ||
+ | if (!valid) { | ||
+ | alert('Invalid email address'); | ||
+ | return false; | ||
+ | } | ||
+ | return true; | ||
+ | } | ||
+ | return false; | ||
+ | } | ||
+ | </source> | ||
+ | New: | ||
+ | |||
+ | <source lang="javascript"> | ||
+ | quest.validateEmail = true; | ||
+ | quest.subQuestionToValidateEmail = 2; | ||
</source> | </source> |
Latest revision as of 05:12, 23 June 2009
Contents
- 1 Test case
- 2 Quota
- 3 Cati list
- 4 Testing tips
- 5 Sample query
- 6 Sample javascript
Test case
Test case #1
Test case #: 1 | Test Case Name: Create a new RCS |
System: Catglobe | Subsystem: RCS |
Designed by: Trong D.Bui | Design Date: 30-03-2009 |
Executed by: | Execution Date: |
Short description: Create a new RCS of a resouce type having resource properties. |
Pre-conditions: The user has enough NAV to generate RCS of the selected resource type. |
Step | Action | Expected System Response | Pass/Fail | Comment |
1. | Generate the RCS from the openning resource list (Tools/Generate resource cache...) |
The RCS Editor is opened. Default search criteria is set to the resource template. All resource properties are listed. |
||
2. | Check/Uncheck a resource property | The property is selected/not selected. | ||
3. | Select a resource property and Save the RCS | The new RCS is saved with the current setting. | ||
4. | Go to the DCS resource list, search and open the RCS. | The RCS Editor is opened with saved information. | ||
5. | Open the RCS's Axis set tab | There is an axis, named as "Property_<Property_Name>", on the Axis list. | ||
6. | Select the axis | The axis is created with following information: + Axis text: "Property_"<Property_Name> + Axis math-base: "Property_"<Property_Id> + Math-base modifier: <empty> + Axis pct_base: "Property_"<Property_Id> != emtpy + Use for weighting: <false> + Is default: <yes> + Options: an option is created for each uniqued resources selected in the resoures having this resource property: _Option name: <selected resource's qualify name> _Option value: "Property_"<property_id> == <selected resource's uniqueID> |
||
7. | View the RCS | The RCS's viewer is opened with built data. A new column, named "Property_"<property_Id> is added for each resource property. Value in the column is the resource uniqueId of the selected resource. |
||
8. | Close the Viewer | The viewer is closed |
Post-conditions: The RCS is created with correct information. The property values can be collected correctly. |
Test case #2
Test case #: | Test Case Name: |
System: | Subsystem: |
Designed by: | Design Date: |
Executed by: | Execution Date: |
Short description: |
Pre-conditions:
|
Step | Action | Expected System Response | Pass/Fail | Comment |
1. | ||||
2. | ||||
Post-conditions:
|
Test case #3
Test case #: | Test Case Name: |
System: | Subsystem: |
Designed by: | Design Date: |
Executed by: | Execution Date: |
Short description: |
Pre-conditions:
|
Step | Action | Expected System Response | Pass/Fail | Comment |
1. | ||||
2. | ||||
Post-conditions:
|
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
Cati list
- 1: Main list
- 2: On going
- 3: Appointment
- 4: Finish
- 5: Retry
- InterveiwSucceeded, InterviewFail, QuotaFull, Outside Target: is set to Finish.
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
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
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
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
-- 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
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
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
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
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
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
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]
Questionnaire Condition
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
SELECT c.[Id], qq.[Question_Index],qq.[Question_Id],
c.[Condition_Type_Id],
(CASE (c.[Condition_Type_Id])
WHEN 1 THEN (SELECT 'Show Question If')
WHEN 2 THEN (SELECT 'GoTo Question If')
WHEN 3 THEN (SELECT 'Exclude Answer Options If')
WHEN 4 THEN (SELECT 'Include Answer Options If')
WHEN 5 THEN (SELECT 'Exclude Answer Options From')
WHEN 6 THEN (SELECT 'Include Answer Options From')
WHEN 7 THEN (SELECT 'Text Conditions')
WHEN 8 THEN (SELECT 'Exclude sub-question If')
WHEN 9 THEN (SELECT 'Include sub-question If')
ELSE ''
END
)AS 'Condition Type',
c.[Expression], c.[Label], c.[Interval],
rp.[Label],
rpl.[Language_Id],
rpl.[Replacement_Text]
from [Condition] c INNER JOIN
[Questionnaire_Question] qq ON c.[Questionnaire_Question_Id] = qq.[Id] LEFT JOIN
[Replacement] rp ON rp.[Condition_Id] = c.[Id] LEFT JOIN
[Replacement_Language] rpl ON rpl.[Replacement_Id] = rp.[Id]
WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] from [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index]
Project Questionnaire's Constant/Property values
Constant
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
SELECT c.[Id], co.[Label] AS 'Constant Option',
(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',
c.[Value],
ocl.[Name] as 'Option Choice Value', co.[Description]
FROM [Constant] c INNER JOIN
[Constant_Option] co ON c.[Constant_Option_Id] = co.[Id] LEFT JOIN
[Constant_Option_Choice] coc ON coc.[Constant_Id] = c.[Id] LEFT JOIN
[Option_Choice] oc ON oc.[Id] = coc.[Option_Choice_Id] LEFT JOIN
[Option_Choice_Language] ocl ON ocl.[Option_Choice_Id] = oc.[Id]
WHERE c.[Resource_Id_fk] = (SELECT [Resource_Uniqueid] FROM [Project_Questionnaire_Resource] WHERE [Id] = @Q_Id)
AND co.[Resource_Id_fk] = (SELECT [Resource_UniqueId]
FROM [Questionnaire_Resource]
WHERE [Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id))
ORDER BY co.[Index]
Property
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
SELECT c.[Id], co.[Label] AS 'Constant Option',
(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',
c.[Value],
ocl.[Name] as 'Option Choice Value', co.[Description]
FROM [Constant] c INNER JOIN
[Constant_Option] co ON c.[Constant_Option_Id] = co.[Id] LEFT JOIN
[Constant_Option_Choice] coc ON coc.[Constant_Id] = c.[Id] LEFT JOIN
[Option_Choice] oc ON oc.[Id] = coc.[Option_Choice_Id] LEFT JOIN
[Option_Choice_Language] ocl ON ocl.[Option_Choice_Id] = oc.[Id]
WHERE c.[Resource_Id_fk] = (SELECT [Resource_Uniqueid] FROM [Project_Questionnaire_Resource] WHERE [Id] = @Q_Id)
AND co.[Resource_Id_fk] = (SELECT [Resource_Uniqueid]
FROM [Resource_template_Resource]
WHERE [Id] = (SELECT [Resource_template_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id))
ORDER BY co.[Index]
Questionnaire's Question Property
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
SELECT q.[Id], qq.[Question_Index],q.[Label],
qp.[Property_Type], qp.[Value],
qpl.[Language_Id], qpl.[Question_Property_Text], qpl.[Is_Default]
FROM [Question] q INNER JOIN
[Questionnaire_Question] qq ON q.[Id] = qq.[Question_Id] LEFT JOIN
[Question_Property] qp ON q.[Id] = qp.[Question_Id] LEFT JOIN
[Question_Property_language] qpl ON qp.[Id] = qpl.[Question_Property_Id]
WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index],qp.[Property_Type],qpl.[Question_Property_Text]
PQ's Data
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
SELECT rpq.[id] AS 'RPQ_ID',d.[Id],rpq.[Completed], rpq.[Status], rpq.[Test],
(SELECT [Question_Index]
FROM [Questionnaire_Question]
WHERE [Question_Id] = d.[Question_Id]
AND [Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
)AS 'Question Index',
(SELECT [Label] FROM [Question] WHERE [Id] = d.[Question_Id])AS 'Question Label',
(CASE (SELECT [Question_Type] FROM [Question] WHERE [Id] = d.[Question_Id])
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 'Question Type',
d.[Sub_Question_Id],d.[Grid_Number],d.[Option_Number],d.[Selection_Order],d.[Value],
do.[Value]AS 'Open Values',d.[TimeStamp], d.[Duration_Seconds],d.[User_Id],d.[Resource_Id_fk]
FROM [Data] d LEFT JOIN
[Data_Open] do ON d.[Id] = do.[Data_Id] INNER JOIN
[Resource_Project_Questionnaire] rpq on d.[Resource_Project_Questionnaire_Id] = rpq.[Id]
WHERE d.[Project_Questionnaire_Id] = @Q_Id
AND rpq.[Deleted_Date] IS NULL
AND rpq.[Test] = 0
PQ's quotas
DECLARE @pqName NVARCHAR(200)
DECLARE @pqId INT
SET @pqName = <Project_Questionnaire_Short_Name>
SELECT @pqId = [Id]
FROM [Project_Questionnaire_Resource]
WHERE [Short_Name] = @pqName
SELECT qo.[Id], qo.[Name], qo.[Target],
(
SELECT count([Resource_Project_Questionnaire_Id])
FROM [Quota_Resource_Project_Questionnaire]
WHERE [Quota_Id] = qo.[Id]
AND [Resource_Project_Questionnaire_Id] in (SELECT [Id]
FROM [Resource_Project_Questionnaire]
WHERE [Project_Questionnaire_Id] = @pqId
AND [Deleted_Date] IS NULL
AND [Test] = 0
AND [Completed] = 1
AND [End_Date] IS NOT NULL
AND [Status] in (1,5)
)
) AS [Current], qo.[Expression], qo.[Description], qo.[Disabled], qo.[Pre_Stratification]
FROM [Quota] qo
WHERE qo.[Project_Questionnaire_Id] = @pqId
SELECT [Status], count(*)
FROM [Resource_Project_Questionnaire]
WHERE [Project_Questionnaire_Id] = @pqId
GROUP BY [Status]
ORDER BY [Status]
SELECT *
FROM [Resource_Project_Questionnaire]
WHERE [Project_Questionnaire_Id] = @pqId
Sample query
Template Property
DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)
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]
Sample javascript
Email address validation
Old:
var normalQuestionCheck = questioncheck;
this.questioncheck = function(){
var valid = normalQuestionCheck();
if (valid) {
var val;
//The value below is the index value of the sub question where your e-mail question is asked.
//In this case we use the sub question has index 2
val = document["query"][quest.label + "." + 2].value;
if (val.length > 0)
valid = validateEmail(val);
if (!valid) {
alert('Invalid email address');
return false;
}
return true;
}
return false;
}
New:
quest.validateEmail = true;
quest.subQuestionToValidateEmail = 2;