|
Odyssey
|
– common functions for creating a report based on an SQL and column array.
Configuration is required. The file that calls this needs to have the global variables:
| String | $defaultDateFormat | – for any date columns, this is the default format. If this is empty and it is empty at the lower level of configuration, then no format will be applied to dates. |
| Associative | Array $reportList – Each key is a particular report. This value is not used except internally to identify the report. $reportKey – The key that is necessary for the report page. It needs to be unique. This will show up on the URL for the report and is referenced in the menu. $title – The title of the report. This will show up as the name of the CSV file on download, the title at the top of the page for the report, and the link on the report menu. If $title is missing, then it uses the key. $sql – This is required. It is the base SQL of the report. Aliases are ignored. These come from the $col array. Any sorts are also ignored. Any sorts and filters are added to this SQL. If there is no sort, then the first column is sorted. Also, the columns are renamed as per the $col array. This is so any sorts and filters can refer to the column name. $cols – This is required. This should be in order of the columns mentioned in the SQL. It consists of $colTitle => $colValue pairs. $colTitle – This is the title of the column that will appear in the kendo grid. The kendo column name also comes from the camel case version of this string. If the title starts with a number, the kendo column name will have a "n" prepended. This is because kendo doesn't take kindly to columns starting with a number. Also, if two columns have the same title, they will still work. If there is a "." in the title, the actual title is everything before the period and the name takes everything. $colValue – This is the column type. Column can be one of the following: "number", "decimal", "string", "date", "boolean", "odata", "currency", "ignore", "list", "hidden", "datetime" "number" – refers to kendo's "number" format. Text aligns right. "decimal" – like number but also restrict the number of digits but no need for a $. "currency" – like number except also formats the number to $0.00. "string" – refers to kendo's "string" format. Filter is case sensitive and trims first. "list" – like string but also adds the "values" attribute to the kendo column. Option needs the $ddl option for the values. "date" – refers to kendo's "date" format. This will be formatted according to $defaultDateFormat. "datetime" – same as "date" but translate to CU time. This is for the "date with timezone" and other date formats in the database. ("date" works just fine.) "odata" – refers to kendo's "odata" format. Sort and filter are not well defined for this format. "boolean" – refers to kendo's "string" format. (The boolean format doesn't seem to work.) It has a "values" attribute according to what is true in the dataset and what is false. Assumed trues are true, "true", "Y", "yes", etc. Assumed falses are false, "false", "N", "no", etc. If both the false and true aren't found, then it gets the opposite. Regardless of what is true and what is false, it will show as "Y" and "N". "ignore" – The column shows up in the dataItem of the grid but doesn't show up in the column array. (So then you can still do stuff with it.) "hidden" – The columns shows up in the dataItem and the column array but it has the "hidden" attribute. (I added it for grouping on a column.) "description" – Like a "string" type but there is no text wrap and there are ellipses. "unencoded" – Sets the kendo's column attribute encoded to false. There is no additional formatting at all. $groupBy – This is an optional parameter. It refers to the $colTitle of the column to group. If this is set, then the attributes "serverSideGrouping" and "group" are added to dataSource. The data for the calls are returned in a format grouped by the column. The CSV becomes grouped as well. The attribute "group" is added to the schema to parse the data correctly. Sorts sort on the groupBy column first before the actual columns being sorted. $groupDescription – This is an optional parameter. It doesn't do anything if $groupBy is not set. It refers to the $colTitle of the description column. It adds the aggregate max to the description column underneath the group attribute. This allows that column's value to be displayed in the group header instead of the $groupBy column. $groupForceSortBy – Forces the group sort to be by the $groupBy when both $groupBy and $groupDescription are defined. $ddl – This is an optional parameter. This contains all the DDLs of the "list" columns. $ddlColumn – This refers to the $colTitle of the group with a DDL. It needs $value => $text pairs in the DDL. $value – Refers to the "value" attribute in the "values" attribute for a column. $text – Refers to the "text" attribute in the "values" attribute for a column. $dateFormat – This is what any dates should be formatted as for the report. If this is not set, then it gets the default date format for all the reports. $dateEmpty – This is what the text will be if the date is null. The default is an empty string. $href – Optional. If set, this the URL is different (versus the default $self&report=$report). $colHeader – Optional. If set, then columns will have another line for the column header. $schemaFunc – Optional. If set, then it refers to another function defined in this script. Function looks like addProfileTemplates($colTitle, $colType, $kendoCol, &$columnRecord). The function is called once per column in the readReportSchema function. $dataFunc – Optional. If set, then it referes to another function defined in this script. Function looks like addLinksToOrphanedRecordsReport($dbh, &$dataRecord). The function is called once per data row in the getData function. $dataFuncVariables – Optional array to send to custom function in this script defined by "dataFunc". $showOnCSV – Optional. If not set, then "ignore" and "hidden" columns are not shown on the CSV. Other columns are shown. If set, for the columns specified, true means the column shows up in the CSV. nonSqlDataFunc – Either this option or the SQL option needs to be defined. (If both are defined, SQL takes precedence.) This is set to function name. Function needs to be defined here. Function has one parameter: $dbh which is the database connection. It must return an array with "data" – array of what should go into the report, "status" – "000" if successful, "error" – "" if successful; otherwise it is the text of the error message, "sql" – optional array of SQL used (if the query is too complex for the report engine or if it needs functions.) defaultSort – If set, then use this instead of the default sort which is the first visible column ascending. autoBind – If set, then set the autoBind kendo property to this value. | |
| Associate | Array $reportMenu – Each key is section of the report menu. Each value is a (Numeric Array) of report keys in that section of the report (corresponds to $reportList). | |
| String | $self | – refers to the file with the data calls, now $_SERVER["PHP_SELF"] set at the main level. |
– common functions for creating a report based on an SQL and column array.
Configuration is required. The file that calls this needs to have the global variables:
| String | $defaultDateFormat | – for any date columns, this is the default format. If this is empty and it is empty at the lower level of configuration, then no format will be applied to dates. |
| Associative | Array $reportList – Each key is a particular report. This value is not used except internally to identify the report. Properties of report – String "title" – This value displays on the report list for that report and in the title of the report page, SQL "sql" – valid SQL for getting the report's data. Filter, sort, and paging is added to the initial query, Associate Array "cols": This is a map. Title of the column => Type of column. Types acceptable: "string", "number", "boolean", "date", "odata", "currency", "list" and "ignore". cols correspond to the select part of the SQL positionally. The first title, type is applied to the first column in the SQL select statement. Associate Array "ddl" : Title of the column => array(value => text) | |
| Associate | Array $reportMenu – Each key is section of the report menu. Each value is a (Numeric Array) of report keys in that section of the report (corresponds to $reportList). | |
| String | $dataFile | – refers to the file with the data calls, now $_SERVER["PHP_SELF"] set at the main level. |
1.8.15