People ======================== The object which holds these methods and properties is made available inside your script as ``q``. .. py:function:: q.BlueToolbarCount(guidStr) :return: The count of people in the current BlueToolbar query :rtype: int :param str guidStr: \*Optional, the GUID string for the BlueToolbar query This function returns the count of people matching the current BlueToolbar query. If no guidStr is provided, it will use the BlueToolbarGuid from the current context. .. py:function:: q.BlueToolbarReport(*sort) :return: a list of people, up to a maximum of 1,000, matching other reports in the current BlueToolbar :rtype: An enumerable list of Person objects. See :doc:`../ObjectTables/PeopleTable` :param str sort: \*Optional, see :doc:`Sort` This function is used to create a Python report suitable for use in the Blue Toolbar, See :ref:`CustomReports`. For example, if you create the following Python script and call it TestBlueToolbarReport:: people = q.BlueToolbarReport("name") template = """ {{#each this}} {{/each}}
NameAgeMaritalMembership
{{Name}} {{Age}} {{MaritalStatus.Description}} {{MemberStatus.Description}}
""" 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. .. code-block:: xml :emphasize-lines: 1 Now you can go to your favorite list of people, and use the BlueToolbar to run this report from the CustomMenu section. See :func:`RenderTemplate` .. py:function:: q.GetWhereClause(code) :return: the sql where clause string corresponding to the search builder query code. :rtype: str :param str code: The code used in search builder (use the view code function) .. py:function:: q.QueryCount(query) :return: The count of the people matching the query :rtype: int :param str query: see :doc:`../Model/QueryParameter` .. py:function:: q.QueryList(query, *sort) :return: A list of people, up to a maximum of 1,000, all matching the query :rtype: An enumerable list of Person objects. See :doc:`../ObjectTables/PeopleTable` :param str query: See :doc:`../Model/QueryParameter` :param str sort: \*Optional, see :doc:`Sort` .. py:function:: q.QueryPeopleIds(query) :return: A list of PeopleIds :rtype: An enumerable list of int :param str query: See :doc:`../Model/QueryParameter` .. py:function:: q.QuerySql(sql, *p1, *declarations) :return: The rows and columns of the SQL select statement :rtype: An enumerable list of dynamic objects having properties matching the select columns. :param str sql: The SQL code to execute :param p1: \*optional parameter to pass into the SQL as ``@p1`` :param dictionary declarations: \*optional additional parameters passed in, named by their dictionary keys. .. py:function:: q.QuerySqlInts(sql) :return: A list of PeopleIds :rtype: An enumerable list of int :param str sql: a sql statement that returns a single column of ints .. py:function:: q.QuerySqlInt(sql) :return: A single integer :rtype: int :param str sql: a sql statement that returns a single scalar int value in one row, one column .. py:function:: q.QuerySqlJsonArray(sql) :return: A JSON array representation of the SQL query results :rtype: str :param str sql: The SQL code to execute This function executes the SQL query and returns the results as a JSON array. Each row becomes an array element, and each column value becomes an array item. .. py:function:: q.QuerySqlPeopleIds(sql) :return: A list of PeopleIds :rtype: An enumerable list of int :param str sql: a sql statement that returns a single column of ints .. py:function:: q.QuerySqlScalar(sql) :return: A single string, varchar, char value :rtype: str :param str sql: a sql statement that returns a single scalar string value in one row, one column .. py:function:: q.QuerySqlStr(sql) :return: A single string value :rtype: str :param str sql: a sql statement that returns a single scalar string value in one row, one column .. py:function:: q.QuerySqlTop1(sql, *p1, *declarations) :return: The first row and columns of the SQL select statement :rtype: A single dynamic object having properties matching the select columns. :param str sql: The SQL code to execute :param p1: \*optional parameter to pass into the SQL as ``@p1`` :param dictionary declarations: \*optional additional parameters passed in, named by their dictionary keys. .. py:function:: q.SqlNameCountArray(title, sql) :return: An array of name,value pairs :rtype: JSON string :param str title: the general for the data points :param str sql: 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 :doc:`../Scripts/PythonCharts` 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 `_. .. py:function:: q.StatusCount(flags) :return: The count of people who have each one of the status flags indicated :rtype: int :param str flags: 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 .. py:function:: q.TagCount(tagid) :return: The count of the peopleid in the tag identified by tagid :rtype: int :param int tagid: presumably the return value of the TagQueryList function below .. py:function:: q.TagQueryList(query) :return: The id of a new tempoary Tag of the people matching the query :rtype: int :param str query: See :doc:`../Model/QueryParameter` See :doc:`../Scripts/RecentInactiveToday` for an example of usage. .. py:function:: q.TagSqlPeopleIds(sql) :return: the id of a new temporary Tag of the people matching the query :rtype: int :param str sql: the sql query returning a single column of PeopleIds .. py:function:: q.SqlFirstColumnRowKey(sql, declarations) :return: a DynamicData object containing as many properties as there are rows in the result set :rtype: DynamicData :param str sql: The SQL that will be executed :param dictionary declarations: \*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. Note that the first column data should be unique to avoid overwriting previous rows. The number of rows is limited to 100 to avoid slurping into memory an entire table of People for example. example:: sql = ''' select mt.Description as MemberType, count(*) as Cnt, (select sum(ContributionAmount) from dbo.Contributions c where c.PeopleId = p.PeopleId) as TotalGiven from dbo.People p join lookup.MemberStatus mt on p.MemberStatusId = mt.Id group by mt.Description ''' params = { 'FundId': 123, 'TagMembers': 456 } results = q.SqlFirstColumnRowKey(sql, params) # Access results using the first column values as keys member_data = results.Member print member_data.Cnt print member_data.TotalGiven .. py:function:: q.SqlNameValues(sql, namecol, valuecol) :return: a DynamicData object :rtype: DynamicData :param str sql: The SQL that will be executed :param str namecol: the the name of the column containing names of the property :param str valuecol: the the name of the column containing the values of the property associated with with the namecol .. py:function:: q.SqlPeopleIdsToQuery(sql) :rtype: str :param str sql: 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.