BILLmanager contains the most popular reports and allows you to add custom reports according to your specific needs.

If you want to write a more complex report, you should be familiar with SQL, BILLmanager database structure, and have programming skills. 

Example of a simple report 


You need to complete the following steps:

  • Add a report into the interface menu or list of reports
  • Describe the parameter input form (if needed)
  • Describe the report layout 
  • Create a SQL request to receive data 

To describe a report, use the xml file /usr/local/mgr5/etc/xml/<mgr>_mod_<name>.xml, where <mgr> is a product name, <name> is a plug-in name. Eg. we create the plug-in myreport for BILLmanager, so the file will have the name

 /usr/local/mgr5/etc/xml/billmgr_mod_myreport.xml

Let's create an XML- description 

<mgrdata>
        <mainmenu level="29">
                <node name="stat">
                        <node name="myreport"/>
                </node>
        </mainmenu>
 
        <metadata name="myreport" type="report">
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                </band>
        </metadata>
 
        <lang name="en">
                <messages name="desktop">
                        <msg name="menu_myreport">My first report</msg>
                </messages>
                <messages name="myreport">
                        <msg name="title">Heading of my first report</msg>
                </messages>
        </lang>
</mgrdata>
XML

In the above example, we added a link to our report into BILLmanager main menu →  "Statistics". At the beginning of the report, we will show a list of provider's companies.

If everything is correct and you have  several companies in the billing system, you will see something like this:

Inserted data


Let's generate a more complex report and display a list of payment methods for every company. To do so, add the inserted band. The report description will look like the following:  

<metadata name="myreport" type="report">
        <band name="company">
                <query>select id, name from profile where account=1</query>
                <col name="name" type="data"/>
                <band name="paymethod">
                        <query>select p.name_ru as paymethod from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]]</query>
                        <col name="paymethod" type="data"/>
                </band>
        </band>
</metadata>
CODE


Enter parameters to generate a report


Let's consider how we can use the form. For example, we need to display only those payment methods which minimum payment amount is larger than a specified value. We need to add a form and parameter into the SQL-request. Add the firstrun="no" attribute to prevent the system from generating the report with any parameters before filling out the form.  

Please note: escape the text inside the XML. For example:

select p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount>=[[minamount]]

XML

Correct variant

select p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[minamount]]
CODE
<metadata name="myreport" type="report" firstrun="no">
        <form>
                <field name="minamount">
                        <input type="text" name="minamount" save="yes" required="yes" check="int"/>
                </field>
        </form>
        <band name="company">
                <query>select id, name from profile where account=1</query>
                <col name="name" type="data"/>
                <band name="paymethod">
                        <query>select p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[minamount]]</query>
                        <col name="paymethod" type="data"/>
                        <col name="minamount" type="data" sort="digit"/>
                </band>
        </band>
 </metadata>
XML

Go from reports to other modules


If we want to switch from a report to a payment method edit form, edit our description: 

<metadata name="myreport" type="report" firstrun="no">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="paymethod.edit"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
</metadata>
XML

We added the column with the payment method id with the nestedreport="paymethod.edit" attribute. It indicates that we need to add the link that will open a new tab with the paymethod.edit function. A column value will be passed as the key.  Besides, all the form fields will be passed to the function. Since the paymethod.edit function has its parameter minamount to prevent changing it into the parameter from our form, I renamed the parameter on the form into repminamount.

Go to into an associated report


We can switch not only to other modules but also to associated reports. In the nestedreport attribute you need to specify the name of the report you want to switch to. Eg. I will create a report that will show payment statistics by different statuses associated with a certain payment method. 

In the report, I will modify the description of the column with the link

<col name="id" type="data" nestedreport="myreport.detail"/>
XML

and describe the new report 

<metadata name="myreport.detail" type="report">
                <band name="payments">
                        <query>select status, sum(paymethodamount) as amount, count(*) as cnt from payment where paymethod=[[elid]] group by status</query>
                        <col name="status" type="msg"/>
                        <col name="amount" type="data" convert="money" sort="digit" total="sum"/>
                        <col name="cnt" type="data" sort="digit" total="sum"/>
                </band>
</metadata>
XML

Add the message section

<messages name="myreport.detail">
                        <msg name="title">Payment methods statsitics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
</messages>
XML

Note how I have converted the status codes into their names.

Add charts and diagrams 


Let's add a chart to our report. To do so, add the following record into band 

<diagram name="statuspie" label="status" data="amount" type="pie"/>
XML

If everything is correct, you will see something like this: 



The complete file of our plug-in: 

<mgrdata>
        <mainmenu level="29">
                <node name="stat">
                        <node name="myreport"/>
                </node>
        </mainmenu>
 
        <metadata name="myreport" type="report" firstrun="no">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="myreport.detail"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
        </metadata>
        <metadata name="myreport.detail" type="report">
                <band name="payments">
                        <diagram name="statuspie" label="status" data="amount" type="pie"/>
                        <query>select status, sum(paymethodamount) as amount, count(*) as cnt from payment where paymethod=[[elid]] group by status</query>
                        <col name="status" type="msg"/>
                        <col name="amount" type="data" convert="money" sort="digit" total="sum"/>
                        <col name="cnt" type="data" sort="digit" total="sum"/>
                </band>
        </metadata>
 
        <lang name="ru">
                <messages name="desktop">
                        <msg name="menu_myreport">My first report</msg>
                </messages>
                <messages name="myreport">
                        <msg name="title">Heading of my first report</msg>
                        <msg name="paymethod">Payment method</msg>
                        <msg name="repminamount">Minimum payment</msg>
                        <msg name="hint_repminamount">Show only the payment methods with minimum payment amounts larger than the specified value</msg>
                </messages>
                <messages name="myreport.detail">
                        <msg name="title">Payment methods statistics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
                </messages>
        </lang>
</mgrdata>
XML

For more information, please refer to these articles

Form data with a script 


Sometimes XML-descriptions are not enough for generating a report.  

You may need to write script handlers in one of the following cases: 

  • To generate a report using the data not from the database, for example, a list of files, output of external services, etc. 
  • Dynamic SQL generation  based on input parameters 
  • Dynamic generation of the report structure, columns, additional statistics strings, etc.
  • Complex database structure which cannot be extracted with a single request 

In this article, we are not going to describe how to write a custom handler in details. You can find this information in other articles of this Documentation (see the links at the end of this article). Here we will describe only the structure of the output data that your handler must generate. 

In our example, the data look like that

<doc>
  <reportdata>
    <company>
      <elem>
        <id>1</id>
        <name>company 1</name>
        <paymethod>
          <elem>
            <id>1</id>
            <paymethod>Wire transfer company 1</paymethod>
            <minamount>100.0000</minamount>
          </elem>
          <elem>
            <id>2</id>
            <paymethod>WebMoney (WMR)</paymethod>
            <minamount>0.0000</minamount>
          </elem>
        </paymethod>
      </elem>
      <elem>
        <id>2</id>
        <name>company 2</name>
        <paymethod>
          <elem>
            <id>2</id>
            <paymethod>WebMoney (WMR)</paymethod>
            <minamount>0.0000</minamount>
          </elem>
        </paymethod>
      </elem>
    </company>
  </reportdata>
</doc>
XML

The data must contain the "reportdata" tag, next - the band tag name (it is "company" in our example). You can place several bands in one report, one after another.  

In every band, a separate data string is specified with the "elem" tag. Every column has the corresponding tag with a column name (in our example this is id, name, etc.).

The data are added in the same way for every inserted band, i.e. you first add a band name, then strings (elem), columns. You can use any number of inserted bands, however, it's useless to add more than three bands as it will be difficult to use such reports. 

Text description 


To make your report more comprehensive, we recommend that you add descriptions and also describe its blocks. The text messages (the msg tags from messages) with pre-defined names will help you 

  • Report description  - report_info
  • Data table description  - table_[BANDNAME]
  • Diagram description - diagram_[DIAGRAMNAME]

Add your report into the list of standard reports 


You can find different reports in  BILLmanager →  "Reports".

To add your report into this section, make sure that

  • the report name begins with the  "report" prefix
  • the metadata tag contains the "group" attribute
<metadata name="report.myreport" type="report" firstrun="no" group="mygroup">
XML

You can use any name for a group name or you can add your report into the existing group (finance, account, item, marketing, support).

Do not forget about localized messages

<messages name="reportlist">
  <msg name="report_mygroup">My report</msg>
  <msg name="report_myreport">My first report</msg>
</messages>
XML

Access permissions 


By default, all authorized users can execute the plug-in. Even if it is not added to the panel menu, it is available through API. If you want to show the report only to "Admin" users, add the level attribute to the metadata tag

<metadata name="report.myreport" type="report" firstrun="no" group="mygroup" level="admin+">
XML

The report will be available only to root and administrators will full permissions. You can allow other users to view this report using standard access functions in the control panel.  

The final result of our report will look like this:

<mgrdata>
        <metadata name="report.myreport" type="report" firstrun="no" group="mygroup" level="admin+">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="myreport.detail"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
        </metadata>
        <metadata name="myreport.detail" type="report" level="admin+">
                <band name="payments">
                        <diagram name="d1" label="status" data="amount" type="pie"/>
                        <query>select status, sum(paymethodamount) as amount, count(*) as cnt from payment where paymethod=[[elid]] group by status</query>
                        <col name="status" type="msg"/>
                        <col name="amount" type="data" convert="money" sort="digit" total="sum"/>
                        <col name="cnt" type="data" sort="digit" total="sum"/>
                </band>
        </metadata>
 
        <lang name="ru">
                <messages name="reportlist">
                        <msg name="report_mygroup">My reports</msg>
                        <msg name="report_myreport">My first report</msg>
                </messages>
                <messages name="report.myreport">
                        <msg name="title">Heading of my first report</msg>
                        <msg name="paymethod">Payment method</msg>
                        <msg name="repminamount">Minimum payment amount</msg>
                        <msg name="hint_repminamount">Show only the payment methods with minimum payment amounts larger than the specified value</msg>
                </messages>
                <messages name="myreport.detail">
                        <msg name="title">Payment method statistics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
                </messages>
        </lang>
</mgrdata>
XML

See also