People¶

The object which holds these methods and properties is made available inside your script as q.

q.BlueToolbarReport(*sort)¶

Returns:

a list of people, up to a maximum of 1,000, matching other reports in the current BlueToolbar

Return type:

An enumerable list of Person objects. See Person Object

Parameters:

sort (str) – *Optional, see Sort Parameter

This function is used to create a Python report suitable for use in the Blue Toolbar, See Custom Reports.

For example, if you create the following Python script and call it TestBlueToolbarReport:

people = q.BlueToolbarReport("name")
template = """
    <table class="table" style="width:auto">
        <tr><th>Name</th><th>Age</th><th>Marital</td><th>Membership</th></tr>
        {{#each this}}
        <tr>
            <td><a href="/Person2/{{PeopleId}}" target="person">{{Name}}</a></td>
            <td>{{Age}}</td>
            <td>{{MaritalStatus.Description}}</td>
            <td>{{MemberStatus.Description}}</td>
        </tr>
        {{/each}}
    </table>
"""
print model.RenderTemplate(template, people)

And then add the following line to the XML file called CustomReports under Special Content > Text Content. I’ve included the final line so you can see in context where the line goes, but you only need to add the one highlighted line. This file is very particular about how the XML text is structured.

    <Report name="TestBlueToolbarReport" type="PyScript" role="Admin" />
</CustomReports>

Now you can go to your favorite list of people, and use the BlueToolbar to run this report from the CustomMenu section.

See RenderTemplate()

q.QueryCount(query)¶

Returns:

The count of the people matching the query

Return type:

int

Parameters:

query (str) – see The Query Parameter

q.SqlPeopleIdsToQuery(sql)¶

Return type:

str

Parameters:

sql (str) – The SQL code to execute

The SQL should return rows with only one PeopleId column. The string returned will be a comma separated list of PeopleIds formatted like peopleids='1001,1002,1003'. This string is suitable to use as a query in any function that expects query as the first argument.

q.QueryList(query, *sort)¶

Returns:

A list of people, up to a maximum of 1,000, all matching the query

Return type:

An enumerable list of Person objects. See Person Object

Parameters:

• query (str) – See The Query Parameter

• sort (str) – *Optional, see Sort Parameter

q.QueryPeopleIds(query)¶

Returns:

A list of PeopleIds

Return type:

An enumerable list of int

Parameters:

query (str) – See The Query Parameter

q.QuerySqlPeopleIds(sql)¶

Returns:

A list of PeopleIds

Return type:

An enumerable list of int

Parameters:

sql (str) – a sql statement that returns a single column of ints

q.QuerySqlInts(sql)¶

Returns:

A list of PeopleIds

Return type:

An enumerable list of int

Parameters:

sql (str) – a sql statement that returns a single column of ints

q.QuerySqlInt(sql)¶

Returns:

A single integer

Return type:

int

Parameters:

sql (str) – a sql statement that returns a single scalar int value in one row, one column

q.QuerySqlTop1(sql, *p1, *declarations)¶

Returns:

The first row and columns of the SQL select statement

Return type:

A single dynamic object having properties matching the select columns.

Parameters:

• sql (str) – The SQL code to execute

• p1 – *optional parameter to pass into the SQL as @p1

• declarations (dictionary) – *optional additional parameters passed in, named by their dictionary keys.

q.QuerySql(sql, *p1, *declarations)¶

Returns:

The rows and columns of the SQL select statement

Return type:

An enumerable list of dynamic objects having properties matching the select columns.

Parameters:

• sql (str) – The SQL code to execute

• p1 – *optional parameter to pass into the SQL as @p1

• declarations (dictionary) – *optional additional parameters passed in, named by their dictionary keys.

q.QuerySqlScalar(sql)¶

Returns:

A single string, varchar, char value

Return type:

str

Parameters:

sql (str) – a sql statement that returns a single scalar string value in one row, one column

q.SqlNameCountArray(title, sql)¶

Returns:

An array of name,value pairs

Return type:

JSON string

Parameters:

• title (str) – the general for the data points

• sql (str) – the SQL code which must return two columns Name, Cnt grouped by some value

This function is specifically designed to be used with Google Charts. See also a full sample you can use with TouchPoint Python Charts

Example:

a = q.SqlNameCountArray("test", '''
    SELECT ms.Description, COUNT(*)
    FROM dbo.People p
    JOIN lookup.MemberStatus ms ON ms.Id = p.MemberStatusId
    GROUP BY ms.Description
''')
print a

Result:

[
  ['test', 'Count'],
  ['Just Added', 1605],
  ['Member', 30702],
  ['Not Member', 172518],
  ['Pending Member', 685],
  ['Previous Member', 20541]
]

Each row after the first row is a label/count combination. See Google Charts arrayToDatatable function.

q.StatusCount(flags)¶

Returns:

The count of people who have each one of the status flags indicated

Return type:

int

Parameters:

flags (str) – A comma separated list of status flags (F01–F99). F00 is a built-in StatusFlag that returns everybody.

Example:

c = StatusCount('F01,F02')
print c

This results in the variable c having a count of people who have both StatusFlag F01 and StatusFlag F02

q.TagCount(tagid)¶

Returns:

The count of the peopleid in the tag identified by tagid

Return type:

int

Parameters:

tagid (int) – presumably the return value of the TagQueryList function below

q.TagQueryList(query)¶

Returns:

The id of a new tempoary Tag of the people matching the query

Return type:

int

Parameters:

query (str) – See The Query Parameter

See Recent Inactive Today for an example of usage.

q.TagSqlPeopleIds(sql)¶

Returns:

the id of a new temporary Tag of the people matching the query

Return type:

int

Parameters:

sql (str) – the sql query returning a single column of PeopleIds

q.GetWhereClause(code)¶

Returns:

the sql where clause string corresponding to the search builder query code.

Return type:

str

Parameters:

code (str) – The code used in search builder (use the view code function)

q.SqlNameValues(sql, namecol, valuecol)¶

Returns:

a DynamicData object

Return type:

DynamicData

Parameters:

• sql (str) – The SQL that will be executed

• namecol (str) – the the name of the column containing names of the property

• valuecol (str) – the the name of the column containing the values of the property associated with with the namecol

q.SqlFirstColumnRowKey(sql, declarations)¶

Returns:

a DynamicData object containing as many properties as there are rows in the result set

Return type:

DynamicData

Parameters:

• sql (str) – The SQL that will be executed

• declarations (dictionary) – *optional additional parameters passed in, named by their dictionary keys.

The first column in each row returned by the SQL will be used as a property name for a DynamicData object having the properties named by the columns in the SQL query.

example::

sql = ‘’’ select

mt.Description as MemberType, count(p.*) Cnt, (select sum(ContributionAmount) Amt from dbo.Contributions where c.PeopleId = p.PeopleId)

from dbo.People p join lookup.MemberStatus mt on p.MemberStatusId = mt.Id group by mt.Description ‘’’

maindd = q.SqlFirstColumnRowKey(sql,