Difference between revisions of "Notes"

From Catglobe Wiki
Jump to: navigation, search
m (→‎Questionnaire Group)
m (→‎Cati list)
 
(28 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.
 +
| &nbsp;Generate the RCS from the openning resource list<br>&nbsp;(Tools/Generate resource cache...)
 +
| &nbsp;The RCS Editor is opened.<br>&nbsp;Default search criteria is set to the resource template.<br>&nbsp;All resource properties are listed.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 2.
 +
| &nbsp;Check/Uncheck a resource property
 +
| &nbsp;The property is selected/not selected.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 3.
 +
| &nbsp;Select a resource property and Save the RCS
 +
| &nbsp;The new RCS is saved with the current setting.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 4.
 +
| &nbsp;Go to the DCS resource list, search and open the RCS.
 +
| &nbsp;The RCS Editor is opened with saved information.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 5.
 +
| &nbsp;Open the RCS's Axis set tab
 +
| &nbsp;There is an axis, named as "Property_&lt;Property_Name&gt;", on the Axis list.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 6.
 +
| &nbsp;Select the axis
 +
| &nbsp;The axis is created with following information:<br>&nbsp;&nbsp; + Axis text: "Property_"&lt;Property_Name&gt;<br>&nbsp;&nbsp; + Axis math-base: "Property_"&lt;Property_Id&gt;<br>&nbsp;&nbsp; + Math-base modifier: &lt;empty&gt;<br>&nbsp;&nbsp; + Axis pct_base: "Property_"&lt;Property_Id&gt; != emtpy<br>&nbsp;&nbsp; + Use for weighting: &lt;false&gt;<br>&nbsp;&nbsp; + Is default: &lt;yes&gt;<br>&nbsp;&nbsp; + Options: an option is created for each uniqued resources selected in the resoures having this resource property:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _Option name: &lt;selected resource's qualify name&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; _Option value: "Property_"&lt;property_id&gt; == &lt;selected resource's uniqueID&gt;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 7.
 +
| &nbsp;View the RCS
 +
| &nbsp;The RCS's viewer is opened with built data.<br>&nbsp;A new column, named "Property_"&lt;property_Id&gt; is added for each resource property.<br>&nbsp;Value in the column is the resource uniqueId of the selected resource.
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 8.
 +
| &nbsp;Close the Viewer
 +
| &nbsp;The viewer is closed
 +
| &nbsp;
 +
| &nbsp;
 +
|}
 +
 +
<br>
 +
 +
{| cellspacing="1" cellpadding="1" width="100%" border="1"
 +
|-
 +
|
 +
'''Post-conditions:'''
 +
 +
&nbsp;The RCS is created with correct information.
 +
 +
&nbsp;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.
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 2.
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|}
 +
 +
<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.
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| 2.
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|-
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
| &nbsp;
 +
|}
 +
 +
<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&nbsp;all&nbsp;RPQs'&nbsp;quotas status&nbsp;
 
*UpdateQuota: update&nbsp;all&nbsp;RPQs'&nbsp;quotas status&nbsp;
 +
 +
== 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 145: Line 420:
 
<source lang="sql">
 
<source lang="sql">
 
DECLARE @Q_Id INT
 
DECLARE @Q_Id INT
−
SET @Q_Id = <Project_Questionnaire_Id>  
+
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.*,  
 
SELECT q.*,  
Line 174: Line 451:
 
<source lang="sql">
 
<source lang="sql">
 
DECLARE @Q_Id INT
 
DECLARE @Q_Id INT
−
SET @Q_Id = <Project_Questionnaire_Id>
+
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  
 
-- Question Group  
Line 221: Line 500:
 
=== Questionnaire Property  ===
 
=== Questionnaire Property  ===
  
−
DECLARE @Q_Id INT<br>SET @Q_Id =&nbsp;&lt;Project_Questionnaire_Id&gt;
+
<source lang="sql">
 +
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)
 +
 
  
−
<br>SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text]
+
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&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id] <br>WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)<br>ORDER BY qnp.[Property_Type]
+
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]  
 +
</source>
  
 
==== Language Independent  ====
 
==== Language Independent  ====
 +
<source lang="sql">
 +
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)
  
−
DECLARE @Q_Id INT<br>SET @Q_Id =&nbsp;&lt;Project_Questionnaire_Id&gt;
 
  
−
<br>SELECT qnp.[Id],qnp.[Property_Type], qnp.[Value]<br>FROM [Questionnaire_Property] qnp <br>WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)<br>&nbsp;&nbsp;&nbsp;AND qnp.[id] NOT IN (SELECT [Questionnaire_Property_id] FROM [Questionnaire_Property_Language])<br>ORDER BY qnp.[Property_Type]  
+
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]  
 +
</source>
 +
 
  
 
==== Language Dependent  ====
 
==== Language Dependent  ====
 +
<source lang="sql">
 +
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]
 +
</source>
 +
 +
=== Questionnaire Condition ===
 +
<source lang="sql">
 +
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]
 +
 +
</source>
 +
 +
=== Project Questionnaire's Constant/Property values ===
 +
==== Constant ====
 +
<source lang="sql">
 +
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]
 +
</source>
 +
 +
==== Property ====
 +
<source lang="sql">
 +
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)
  
−
DECLARE @Q_Id INT<br>SET @Q_Id = &lt;Project_Questionnaire_Id&gt;
+
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]
 +
</source>
  
−
<br>SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text]
+
=== Questionnaire's Question Property ===
 +
<source lang="sql">
  
−
FROM [Questionnaire_Property] qnp INNER JOIN&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id] <br>WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)<br>ORDER BY qnp.[Property_Type]
+
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]
 +
 
 +
</source>
 +
 
 +
=== PQ's Data ===
 +
<source lang="sql">
 +
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
 +
</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>
  
 
== Sample query  ==
 
== Sample query  ==
Line 246: Line 756:
  
 
<source lang="sql">
 
<source lang="sql">
−
DECLARE @Template_Id INT
+
DECLARE @Q_Id INT
−
SET @Template_Id = <Resource_Template_Id>
+
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',
 
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',
 
       co.[Index],co.[Id] AS 'Constant_Option_Id', co.[Label] AS 'Constant_Option_Label',
−
       ( cASe co.[Type]
+
       ( CASE co.[Type]
 
             WHEN 1 THEN (SELECT 'Number')
 
             WHEN 1 THEN (SELECT 'Number')
 
             WHEN 2 THEN (SELECT 'Boolean')
 
             WHEN 2 THEN (SELECT 'Boolean')
Line 271: 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

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.
The openning resource list's template has a resource property.


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;
Retrieved from "http://wiki.catglobe.com/index.php?title=Notes&oldid=6763"