People ======================== The object which holds these methods and properties is made available inside your script as ``q``. .. 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.QueryCount(query) :return: The count of the people matching the query :rtype: int :param str query: see :doc:`../Model/QueryParameter` .. 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. .. 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.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.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.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.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.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.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.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.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.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. 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,