• View
• Edit
• History
• Print

SQLSolutions

dbs> There are several 'questions' I need to have answered at various points.

blk> This page details the SQL that will help answer those questions. The functions that underlie these solutions can be found at the page SQLPropertyFunctions.

1) What is the value for such and such a property for a registrant? This would take into account global (0 cid) entries, as well as the 'default' value for a property (see config_properties)

ANSWER:

 SELECT get_prop(RID, CID, PNAME) AS prop_value;

This will call a function which returns the value (or default value) for any given registrant, conference, and property name.

EXAMPLE:

 mysql>  SELECT get_prop(6623, 16, 'Administrator') AS prop_value;
 +------------+
 | prop_value |
 +------------+
 | 1          | 
 +------------+

2) Give me a list of properties for a registrant that differs from the system default for each property (including globals).

ANSWER:

 SELECT prop_name, get_person_prop(RID, CID, prop_name) AS prop_value
   FROM 
   ( SELECT distinct prop_name from reg_properties 
      WHERE prop_rid = RID
        AND (prop_cid = CID or prop_cid = 0) ) AS prop_list
  WHERE prop_is_default (RID, CID, prop_name) = 0;

This will return a list of properties and their value for all properties that registrant has, for a specific convention, which are different from their defaults.

EXAMPLE:

 mysql>  SELECT prop_name, get_person_prop(6623, 16, prop_name) AS prop_value
     ->    FROM 
     ->    ( SELECT distinct prop_name from reg_properties 
     ->       WHERE prop_rid = 6623
     ->         AND (prop_cid = 16 or prop_cid = 0) ) AS prop_list
     ->   WHERE prop_is_default (6623, 16, prop_name) = 0;
 +---------------+------------+
 | prop_name     | prop_value |
 +---------------+------------+
 | Administrator | 1          | 
 | Staff         | 1          | 
 | Referrer      | Other      | 
 +---------------+------------+

3) Give me a list of all properties available for a registrant, and their current values (including defaults). Denote in that view whether the current value being displayed comes from a global (all events) or default (system defined default) value.

ANSWER:

  SELECT prop_name, get_prop(RID, CID, prop_name) AS prop_value, 
                   prop_is_default(RID,CID,prop_name) AS 'default?', 
                   prop_is_global(RID,CID,prop_name) AS 'global?'
   FROM 
   ( SELECT distinct prop_name from reg_properties 
      WHERE prop_rid = RID
        AND (prop_cid = CID or prop_cid = 0) ) AS prop_list;

This will return a list of properties set for that registrant, their value, whether that value is the default value, and whether that value is a global value.

EXAMPLE:

 mysql>  SELECT prop_name, get_prop(6623, 16, prop_name) AS prop_value, 
     ->                    prop_is_default(6623,16,prop_name) AS 'default?', 
     ->                    prop_is_global(6623,16,prop_name) AS 'global?'
     ->    FROM 
     ->    ( SELECT distinct prop_name from reg_properties 
     ->       WHERE prop_rid = 6623
     ->         AND (prop_cid = 16 or prop_cid = 0) ) AS prop_list ;
 +---------------+------------+----------+---------+
 | prop_name     | prop_value | default? | global? |
 +---------------+------------+----------+---------+
 | Addr_Sharing  | 2          |        1 |       1 | 
 | Administrator | 1          |        0 |       0 | 
 | Staff         | 1          |        0 |       0 | 
 | Referrer      | Other      |        0 |       0 | 
 +---------------+------------+----------+---------+

(this last one will be used when an operator looks up a registrant and says "I want to edit their properties" - they need a list of all the properties available, and the UI needs to specify where the value came from, and allow the operator to either override the default with a local value, or set a global property for the registrant [or both?])