Adding custom reports

From ISPWiki

Jump to: navigation, search

This document provides information on how to create a custom report. All you need is basic knowledge in XML, SQL and a database structure.

Locate the etc/billmgr.xml file to view the structure of the reports in the billing panel. The name of the meta data that describes a report should start with the prefix "report2.".

Contents

Report example

Following is two reports that show income per server.

To display this report in the interface, you need to create a file with meta data. Create the /usr/local/ispmgr/etc/billmgr_mod_report2incomeserver.xml file with the following content:

For BILLmanager Standard and Advanced:

<?xml version="1.0" encoding="UTF-8"?>
<mgrdata>
	<metadata name="report2.incomeserver" type="report" level="7" group="mygroup" firstrun="no">
	<text name="title"/>
	<form>
		<period/>
	</form>
	<band name="project" headcolor="#f4d0bc" psort="date">
		<query>select s.id as id, s.name as name, sum(e.amount) as amount from server s left join item i on s.id=i.server left join item ia on i.id = ia.parent or 
ia.id=i.id left join expense e on e.item = ia.id  where e.cdate between '[[periodstart]]' and '[[periodend]]' group by s.id</query>
		<diagram label="name" data="amount" type="line"/>
			<col name="id" type="data" total="count" sort="digit"/>
			<col name="name" type="data" sort="alpha"/>
			<col name="amount" type="data" total="sum" sort="digit" sorted="desc"/>
	</band>
	</metadata>
	<lang name="ru">
		<messages name="report2.incomeserver">
			<msg name="amount">Income</msg>
			<msg name="title">Income per server</msg>
			<msg name="id">ID</msg>
			<msg name="name">Name</msg>
		</messages>
		<messages name="reportlist">
			<msg name="name_incomeserver">Income per server</msg>
			<msg name="name_mygroup">My reports</msg>
		</messages>
	</lang>
</mgrdata>

For BILLmanager Corporate:

<?xml version="1.0" encoding="UTF-8"?>
<mgrdata>
	<metadata name="report2.incomeserver" type="report" level="6 7" group="mygroup" firstrun="no">
	<text name="title"/>
	<form>
		<period/>
	</form>
	<band dist="3" name="project" headcolor="#f4d0bc" psort="date">
	  		<query>select id, name from project where account=[[ses.account]]<nowiki></query>
 	  		<col name="name" type="data"/>
 	  		<band name="income" headcolor="#333333">
 		  		<query>select s.id as id, s.name as name, ifnull(sum((select sum(e.amount) from expense e where e.item=ia.id and e.cdate between 
 '<nowiki>[[periodstart]]' and '[[periodend]]')), 0) as amount from server s, item i, item ia, pricelist pl where (i.id = ia.parent or ia.id=i.id) and s.id=i.server and 
pl.id=i.price and pl.project=[[project.id]] group by s.id order by amount desc</query>
				<diagram label="name" data="amount" type="histogram"/>
				<col name="id" type="data" total="count" sort="digit"/>
				<col name="name" type="data" sort="alpha"/>
				<col name="amount" type="data" total="sum" sort="digit" sorted="desc"/>
			</band>
	  </band>
	</metadata>
	<lang name="ru">
		<messages name="report2.incomeserver">
			<msg name="amount">Income</msg>
			<msg name="title">Income per server</msg>
			<msg name="id">ID</msg>
			<msg name="name">Name</msg>
		</messages>
		<messages name="reportlist">
			<msg name="name_incomeserver">Income per server</msg>
			<msg name="name_mygroup">My reports</msg>
		</messages>
	</lang>
</mgrdata>


Restart the billing (killall billmgr).

XML structure

Attributes of the metadata tag

name - name; it must always start with the prefix report2.
type - mandatory parameter, it must be 'report'.
level - space separated level of access a user must have to view this report: '6 7'
group - group where this report will be shown: finance, marketing, promotion, support. You can specify your own group.
firstrun - do not generate a report upon the first setup. This option can be used if it may take a long time to generate a report, and you first need to choose parameters to generate it.

<text name="title"/> - shows the report heading

The form tag

Describes the form's fields.

If you want a date span to be shown on the form, use the <period/> tag.
If you need additional fields, you can use the input and select elements of the form.

The input element

Attributes:
name - parameter's name; it is used in the sql query.
type - possible values: text, checkbox.
date - if specified, the field will have a calendar button. Only for type="text".
default - default value. Only for type="text".

The select element

Attributes:
name - parameter's name that is used in the в sql query of the query element.
query - sql query that provides values for the current element: For example: "select u.id, u.name from user u where isgroup=1 and account=[[ses.account]]" - sorting of all the departments.
any - additional value: "Any".
values - list of the current element's values space separated. For example: "today,currentmonth,currentyear,lastday,lastmonth,lastyear,other,nodate".
default - default value.

In the select element you can use the if tag that allows to hide fields, if this element has a specific value.
For example:

<select name="period"  values="today,currentmonth,currentyear,lastday,lastmonth,lastyear,other,nodate" default="lastday">
	<if value="other" show="'startd','endd'"/>
</select>
<input type="text" name="periodstart" date="yes" id="startd"/>
<input type="text" name="periodend" date="yes" id="endd"/>

The if element

Attributes:

value - if the value of the select element matches this value, the elements listed in the show attribute will be shown
show - list of the elements' ids to be shown
hide - list of the elements' ids to be hidden

The band element

The band element is used to describe the output interface. Structure of XML band:

<band>
	<query/>
	<diagram/>
	<col/>
	[[<band>]]
</band>

As we can see from the structure, the band element can be included into another band element. From there you can address to the data of the parent one in the sql query. See the example below:

Attributes:

name - name that is used in the sql query.
headcolor - heading color
handrep - handler module that is used, if it is impossible to get required data using the sql query. The script must locate in the /usr/local/ispmgr/sbin directory. In this article we are not going to describe this parameter.

The query element

Contains the sql query that is used for displaying information. In the query you need to use aliases for columns to bind them to the col element. For example, "s.name as name" will be bound to the column "<col name="name" ...".

In the sql query you can use parameters form the report form; if query is located in the band element, the values of the band located on the higher level can be used as well. You need to put the parameters' names into double round brackets. For example:

.....
<form>
	<input name="testinput" type="text"/>
</form>
<band name="project">
	<query>select id , name from project where account = [[ses.account]]</query>
	<col name="name" type="data"/>
	<band name="testdata">
		<query>
			select .... from ... where param=[[testinput]] and project=[[project.id]]
		</query>
.....

The diagram element

the type attribute

The type of diagram. Possible values: line, histogram, pie.

the data attribute

The column from which data for a pie diagram should be taken (only for type="pie").

the label attribute

The column from which the values' captions should be taken.

the line element

One or several elements describe the data that should be shown on the diagram.

the data element

The name of the column from which data for a diagram should be taken.

For example:

<diagram label="cname" type="histogram">
	<line data="amount"/>
	<line data="icnt"/>
</diagram>

The col element

the name attribute

A column name. It should be unique within one metadata. It can be used to bound a query from the query element to the columns.

the type attribute

The type of data in the column. Possible values: data (data from the sql query), msg (shows messages corresponding to the value passed from the query).

the sort attribute

The type of sorting in the column. Possible values: alpha (alphabetical sorting), digit (sorting in increasing order)

the sorted attribute

If sorted="yes", the table will be sorted by this column as default. if sorted="desc", the table will be sorted in descending order.

the total attribute

A final value. Possible values: sum - sum; count - the number of lines; avg - average value.

Was this helpful? Yes | No
Personal tools