After working with audit journal then next is working with accounting data.
It can be useful as an example of how to read accounting data.
But it can also be useful as an example of how more general to work with binary files, relational databases and reporting tools. Including how to generate PDF files on VMS. And this time witrh graphics!
All the code is tested as working on VMS Alpha. All the code is available for download. But to get it working on your VMS system may still take some effort and require some knowledge about involved technologies.
The data flow will look like:
Everything will be build using Java technology.
For reading the accounting data we will use the Record library.
For storing in database we will use JPA.
For scripting we will use Jython (JVM Python).
For reporting (generate PDF file) we will use JasperReports,
Both the Record library and JPA are pretty heavy (use lot of reflection etc.), so unless run on a fast VMS system the code may take some time and burn some CPU cycles. You have been warned!
The benefits are that:
Note that when the data has been imported into a database with proper indexes then reporting based on the database becomes reasonable speed wise.
What you will need is:
*) the easiest way to get all this is to get a copy of my javalib bundle. Note that this is all versions that will run on Java 1.5 on VMS Alpha - if you use Java 1.8 on VMS Itanium or Java 8/11/17 on Linux/Windows then you can get newer versions if you want to.
The VMS Accounting library comes with support for some of the most common/relevant data structures.
If something relevant for you case is missing please get the source kit and extend it to meet your requirements.
The database tables look like:
| acctinfo |
|---|
| aiid(INTEGER),PK |
| aitype(VARCHAR) |
| aisystime(DATETIME) |
| aiusername(VARCHAR) |
| aipid(CHAR) |
| acctinfoimage |
|---|
| aiid(INTEGER),PK |
| aeiiimagename(VARCHAR) |
| acctinfoprint |
|---|
| aiid(INTEGER),PK |
| aippagecount(INTEGER) |
| acctinfoprocess |
|---|
| aiid(INTEGER),PK |
| aipproctype(VARCHAR) |
| aiplogintime(DATETIME) |
| aipcputime(INTEGER) |
| aipmemory(INTEGER) |
| aipbio(INTEGER) |
| aipdio(INTEGER) |
SQL creation script:
CREATE TABLE acctinfo (
aiid INTEGER NOT NULL,
aitype VARCHAR(32),
aisystime DATETIME,
aiusername VARCHAR(16),
aipid CHAR(8),
PRIMARY KEY(aiid)
);
CREATE INDEX ix_aitype ON acctinfo(aitype);
CREATE INDEX ix_aisystime ON acctinfo(aisystime);
CREATE INDEX ix_aiusername ON acctinfo(aiusername);
CREATE TABLE acctinfoimage (
aiid INTEGER NOT NULL,
aiiimagename VARCHAR(255),
PRIMARY KEY(aiid)
);
CREATE INDEX ix_aiiimagename ON acctinfoimage(aiiimagename);
CREATE TABLE acctinfoprint (
aiid INTEGER NOT NULL,
aippagecount INTEGER,
PRIMARY KEY(aiid)
);
CREATE TABLE acctinfoprocess (
aiid INTEGER NOT NULL,
aipproctype VARCHAR(16),
aiplogintime DATETIME,
aipcputime INTEGER,
aipmemory INTEGER,
aipbio INTEGER,
aipdio INTEGER,
PRIMARY KEY(aiid)
);
CREATE INDEX ix_aipaipproctypee ON acctinfoprocess(aipproctype);
CREATE INDEX ix_aiplogintime ON acctinfoprocess(aiplogintime);
The data classes look like:
META-INF/persistence.xml:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
...
<persistence-unit name="prod_acct">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>dk.vajhoej.vms.acct.db.AcctDbInfo</class>
<class>dk.vajhoej.vms.acct.db.AcctDbInfoImage</class>
<class>dk.vajhoej.vms.acct.db.AcctDbInfoPrint</class>
<class>dk.vajhoej.vms.acct.db.AcctDbInfoProcess</class>
<exclude-unlisted-classes/>
<properties>
<!--<property name="hibernate.show_sql" value="true"/>-->
<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
<property name="hibernate.connection.url" value="jdbc:h2:file:./acct;FILE_LOCK=FS"/>
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value="hemmeligt"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
<property name="hibernate.connection.pool_size" value="1"/>
</properties>
</persistence-unit>
...
</persistence>
Load:
$ hibpath = "/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ pipe java -cp /javalib/h2-1_2_147.jar "org.h2.tools.Shell" -url "jdbc:h2:./acct;FILE_LOCK=FS" -user sa -password hemmeligt < acct.sql
$ java "-Xmx512m" -cp .:vmsacct.jar:record.jar:'hibpath':/javalib/h2-1_2_147.jar "dk.vajhoej.vms.acct.tools.ToDb" accountng.dat prod_acct
For reports we will use JasperReports. JasperReports is maybe sligtly oldfashioned, but it is a powerful reporting tool.
Basically JasperReports take as input:
and generate a report from that.
Several output formats including PDF and HTML are supported.
This example will generate a PDF report.
Report template usage.jrxml:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
name="usage" language="groovy" printOrder="Horizontal"
pageWidth="600" pageHeight="850" columnWidth="560" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"
isSummaryWithPageHeaderAndFooter="true">
<!-- Note: The SQL below is not standard SQL and may need to be modified for different databases. -->
<!-- But the report people should now the SQL dialect of their database. -->
<queryString><![CDATA[
SELECT YEAR(aisystime) AS y, MONTHNAME(aisystime) AS m,COUNT(*) AS sessions
FROM acctinfo ai JOIN acctinfoprocess aip ON ai.aiid = aip.aiid
WHERE aipproctype = 'Interactive'
GROUP BY y,m
ORDER BY y,m
]]></queryString>
<field name="y" class="java.lang.Integer"/>
<field name="m" class="java.lang.String"/>
<field name="sessions" class="java.lang.Integer"/>
<title>
<band height="100">
<staticText>
<reportElement x="0" y="25" width="600" height="50"/>
<textElement textAlignment="Center">
<font size="24"/>
</textElement>
<text><![CDATA[Usage Report]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band height="0"/>
</pageHeader>
<columnHeader>
<band height="40">
<staticText>
<reportElement x="50" y="0" width="100" height="20"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="12"/>
</textElement>
<text><![CDATA[Month Year]]></text>
</staticText>
<staticText>
<reportElement x="200" y="0" width="300" height="20"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="12"/>
</textElement>
<text><![CDATA[Number of interactive sessions]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="20">
<textField>
<reportElement x="50" y="0" width="100" height="20"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="10"/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{m} + " " + $F{y}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="200" y="0" width="100" height="20"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="10"/>
</textElement>
<textFieldExpression class="java.lang.Integer"><![CDATA[$F{sessions}]]></textFieldExpression>
</textField>
</band>
</detail>
<pageFooter>
<band height="50">
<textField evaluationTime="Report">
<reportElement x="0" y="0" width="250" height="25"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="12"/>
</textElement>
<textFieldExpression><![CDATA["Generated: " + dk.vajhoej.vms.util.Util.t2s(new java.util.Date())]]></textFieldExpression>
</textField>
<textField>
<reportElement x="250" y="0" width="250" height="25"/>
<textElement textAlignment="Right" verticalAlignment="Top">
<font size="12"/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField evaluationTime="Report">
<reportElement x="501" y="0" width="100" height="25"/>
<textElement textAlignment="Left" verticalAlignment="Top">
<font size="12"/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[" of " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
</band>
</pageFooter>
<summary>
<band height="550">
<barChart>
<chart>
<reportElement x="0" y="0" width="500" height="500">
<property name="net.sf.jasperreports.chart.range.axis.tick.interval" value="10"/>
</reportElement>
<chartTitle>
<titleExpression><![CDATA["Sessions per Month"]]></titleExpression>
</chartTitle>
</chart>
<categoryDataset>
<categorySeries>
<seriesExpression><![CDATA["Sessions"]]></seriesExpression>
<categoryExpression><![CDATA[$F{y} + " " + $F{m}.substring(0,3)]]></categoryExpression>
<valueExpression><![CDATA[$F{sessions}]]></valueExpression>
</categorySeries>
</categoryDataset>
<barPlot>
<plot/>
<categoryAxisFormat labelRotation="90.0">
<axisFormat/>
</categoryAxisFormat>
<valueAxisFormat>
<axisFormat tickLabelMask="###"/>
</valueAxisFormat>
</barPlot>
</barChart>
</band>
</summary>
</jasperReport>
Run:
$ jrpath = "/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-collections-3_1.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging.jar:/javalib/groovy-all-1_7_5.jar:/javalib/itext-2_1_7.jar:/javalib/jasperreports-3_7_6.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar"
$ java "-Djava.awt.headless=true" "-Xmx512m" -cp .:vmsacct.jar:'jrpath':/javalib/h2-1_2_147.jar "dk.vajhoej.vms.util.RepGen" "org.h2.Driver" "jdbc:h2:file:./acct;FILE_LOCK=FS" "sa" "hemmeligt" usage.jrxml usage.pdf
Page 1 of generated PDF file:
Page 2 of generated PDF file:
This example is only showing a tiny fraction of what JasperReports is capable of. Those interested in utilizing JasperReports should study the documentation.
Note that there is absolutely no magic in my RepGen code - it is easy to do the same in any JVM application.
package dk.vajhoej.vms.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
public class RepGen {
public static void main(String[] args) {
try {
if(args.length >= 6) {
Class.forName(args[0]);
Connection con = DriverManager.getConnection(args[1], args[2], args[3]);
JasperReport rep = JasperCompileManager.compileReport(args[4]);
JasperPrint prt = JasperFillManager.fillReport(rep, null, con);
JasperExportManager.exportReportToPdfFile(prt, args[5]);
con.close();
} else {
System.out.printf("Usage: java -cp ... %s <driver-class> <connection-url> <connection-username> <connection-password> <report-template> <output-pdf>\n", RepGen.class.getName());
}
} catch(Throwable ex) {
ex.printStackTrace();
}
}
}
| Version | Date | Description |
|---|---|---|
| 1.0 | May 2nd 2023 | Initial version |
eve See list of all articles here
Please send comments to Arne Vajhøj