OpenKM Reports

[dsmith_au]

Hello all,

Version: OpenKM version 6.3.7 Community
Browser: Firefox/Chrome
OS: Ubuntu - Server 18.10


I'm hoping someone can offer me a shred of assistance. I am slowly dying as I have been trying to nut this out now for days. I have written a custom report in Jasper reports and have uploaded it to the openkm instance I have installed in order to extract data from a patient clinical database.

I have written a custom DAO using jdbc to access this custom database but I cannot for the life of me work out how to make it work. I have tried using the standard SQL statement within the querystring parameters but when I upload it to openkm it continues to search for the mysql jdbc drivers. Hence my crafting of the custom jdbc DAO modules. I managed to find a pre-canned report and tried to copy it but when I try to upload it to openkm it continues to crash with an Error that reads as such

“sourced file: inline evaluation of: ``import com.dbs.dao.DNADAO; import com.dbs.com.models.DNAModel; List dnalist = n . . . '' : Class: DNAModel not found in namespace : at Line: 6 : in file: inline evaluation of: ``import com.dbs.dao.DNADAO; import com.dbs.com.models.DNAModel; List dnalist = n . . . '' : DNAModel “

I don't believe my report is complex nor do I believe that my Java datasource is complex I'm just struggling to try and pull it together.

the Java code is there such:

DNAModel.java

Code: Select all

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.dbs.models;

/**
 *
 * @author dsmith
 */
public class DNAModel {

    private int mon;
    private String month;
    private int year;
    private int dnacount;

    public DNAModel() {
    }

    public DNAModel(int mon, String month, int year, int dnacount) {
        this.mon = mon;
        this.month = month;
        this.year = year;
        this.dnacount = dnacount;
    }

    public int getMon() {
        return mon;
    }

    public void setMon(int mon) {
        this.mon = mon;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public int getDnacount() {
        return dnacount;
    }

    public void setDnacount(int dnacount) {
        this.dnacount = dnacount;
    }
}

DNADAO.java

Code: Select all

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.dbs.dao;

import com.dbs.models.DNAModel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author dsmith
 */
public class DNADAO {
    
    private static Connection conn = null;

    public DNADAO() {
    }
    
    
    public Connection getBPConnection() throws ClassNotFoundException, SQLException{
        String username = "xxxxx";
        String password = "xxxxx";
        String url = "jdbc:jtds:sqlserver://xx.xx.xx.xx:1435";
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(url, username, password);
        return conn;
    }
    
    public int getCurrentYear(){
        int year = 0;
        LocalDate date = LocalDate.now();
        year = date.getYear();
        return year;
    }
    
    public int getCurrentMonth(){
        int month = 0;
        LocalDate date = LocalDate.now();
        month = date.getMonthValue();
        return month;
    }
    
    public LocalDate getPastDate(int months){
        LocalDate pastDate = LocalDate.now().minusMonths(months);
        return pastDate;                
    }
    
    @SuppressWarnings("null")
    public List<DNAModel> getRollingDNAStats(){
        LocalDate pastDate = LocalDate.now().minusMonths(12);
        List<DNAModel> dnaStats = new ArrayList<>(); 
        PreparedStatement ps1 = null;
        ResultSet rs1 = null;
        @SuppressWarnings("LocalVariableHidesMemberVariable")
        Connection conn = null;
        try {
            conn = getBPConnection();
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(DNADAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        String sql = "select 	datepart(mm, a.appointmentdate) as mon " +
                    " ,CASE datepart(mm, a.appointmentdate)" +
                    "      WHEN 1 then 'January'" +
                    "      WHEN 2 then 'February'" +
                    "      WHEN 3 then 'March'" +
                    "      when 4 then 'April'" +
                    "      when 5 then 'May'" +
                    "      when 6 then 'June'" +
                    "      when 7 then 'July'" +
                    "      when 8 then 'August'" +
                    "      when 9 then 'September'" +
                    "      when 10 then 'October'" +
                    "      when 11 then 'November'" +
                    "      when 12 then 'December'" +
                    " END	as month " +
                    " , datepart(yyyy, a.APPOINTMENTDATE) as year " +
                    " , count( * ) as dnacount " +
                    " from 		bpspatients.dbo.appointments a " +    4
    Views
                    " inner join bpspatients.dbo.patients b on a.INTERNALID = b.INTERNALID " +
                    " where 		a.APPOINTMENTCODE = 10 " +
                    " and			a.RECORDSTATUS = 1 " +
                    " and 		datepart(yyyy, a.appointmentdate) >= ? " +
                    " and			b.PATIENTSTATUS = 1 " +
                    " group by 	datepart(yyyy, a.APPOINTMENTDATE), datepart(mm, a.appointmentdate) " +
                    " order by datepart(yyyy, a.APPOINTMENTDATE) asc, Mon asc";
        
        try{
            ps1 = conn.prepareStatement(sql);
            ps1.setInt(1, pastDate.getYear());
            
            rs1 = ps1.executeQuery();
            
            while(rs1.next()){
                dnaStats.add(new DNAModel(rs1.getInt("mon"),rs1.getString("month"), rs1.getInt("year") , rs1.getInt("dnacount")));
            }
            
            rs1.close();
            ps1.close();
            conn.close();
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DNADAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return dnaStats;
    }
    
}

And the report XML reads as follows:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="DNA_Reports" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a1f690a3-5977-4467-85f8-6b0bcd5b7210">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Broadway"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="Title" fontName="Arial" fontSize="26" isBold="true"/>
    <style name="SubTitle" forecolor="#666666" fontName="Arial" fontSize="18"/>
    <style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
    <style name="Detail" fontName="Arial" fontSize="12"/>
    <queryString>
   	 <![CDATA[import com.dbs.dao.DNADAO;
import com.dbs.com.models.DNAModel;

List dnalist = new ArrayList();
DNADAO dao = new DNADAO();
for(DNAModel dnauser : dao.getRollingDNAStats()){
    Map dna = new HashMap();
    dna.put("mon", dnauser.getMon());
    dna.put("month", dnauser.getMonth());
    dna.put("year", dnauser.getYear());
    dna.put("dnacount", dnauser.getDnacount());
    dnalist.add(dna);
}

return dnalist;]]>
    </queryString>
    <field name="mon" class="java.lang.Integer">
   	 <property name="com.jaspersoft.studio.field.label" value="mon"/>
    </field>
    <field name="month" class="java.lang.String">
   	 <property name="com.jaspersoft.studio.field.label" value="month"/>
    </field>
    <field name="year" class="java.lang.Integer">
   	 <property name="com.jaspersoft.studio.field.label" value="year"/>
    </field>
    <field name="dnacount" class="java.lang.Integer">
   	 <property name="com.jaspersoft.studio.field.label" value="dnacount"/>
    </field>
    <background>
   	 <band splitType="Stretch"/>
    </background>
    <title>
   	 <band height="70" splitType="Stretch">
   		 <image>
   			 <reportElement x="306" y="0" width="255" height="64" uuid="363fa2ad-70fc-43cb-9062-e6c62c7f48fc"/>
   			 <imageExpression><![CDATA["weblogo-2.png"]]></imageExpression>
   		 </image>
   		 <staticText>
   			 <reportElement style="Title" x="0" y="13" width="306" height="33" uuid="6f9c1004-6140-4588-9c4f-d75824835765"/>
   			 <textElement verticalAlignment="Middle"/>
   			 <text><![CDATA[DNA Count by Period]]></text>
   		 </staticText>
   	 </band>
    </title>
    <pageHeader>
   	 <band height="34" splitType="Stretch">
   		 <staticText>
   			 <reportElement x="0" y="13" width="150" height="16" uuid="001fddb7-bd24-45e9-ae32-e40c697c9359"/>
   			 <textElement verticalAlignment="Bottom">
   				 <font size="12" isBold="true"/>
   			 </textElement>
   			 <text><![CDATA[Period]]></text>
   		 </staticText>
   		 <staticText>
   			 <reportElement x="160" y="13" width="100" height="16" uuid="3e1bd212-27e8-4626-adc0-2ff283e7f24e"/>
   			 <textElement verticalAlignment="Bottom">
   				 <font size="12" isBold="true"/>
   			 </textElement>
   			 <text><![CDATA[DNA Count]]></text>
   		 </staticText>
   	 </band>
    </pageHeader>
    <detail>
   	 <band height="22" splitType="Stretch">
   		 <line>
   			 <reportElement positionType="FixRelativeToBottom" x="0" y="0" width="249" height="1" uuid="cb229859-ab9a-4aa3-afa2-84439642f4ee"/>
   			 <graphicElement>
   				 <pen lineWidth="0.5" lineColor="#999999"/>
   			 </graphicElement>
   		 </line>
   		 <textField>
   			 <reportElement x="0" y="0" width="150" height="20" uuid="6bc7a8b0-c9a3-4f21-8274-ef03afcc0944"/>
   			 <textFieldExpression><![CDATA[$F{month} + " " + $F{year}]]></textFieldExpression>
   		 </textField>
   		 <textField>
   			 <reportElement x="160" y="1" width="100" height="19" uuid="3dc73fae-7a0d-4bb3-be01-bd5e26abb939"/>
   			 <textFieldExpression><![CDATA[$F{dnacount}]]></textFieldExpression>
   		 </textField>
   	 </band>
    </detail>
    <columnFooter>
   	 <band height="45" splitType="Stretch">
   		 <line>
   			 <reportElement positionType="FixRelativeToBottom" x="0" y="-3" width="555" height="1" uuid="401e0e59-de38-4dd2-8257-418cb489177c"/>
   			 <graphicElement>
   				 <pen lineWidth="0.5" lineColor="#999999"/>
   			 </graphicElement>    4
    Views
   		 </line>
   	 </band>
    </columnFooter>
    <pageFooter>
   	 <band height="20" splitType="Stretch">
   		 <textField>
   			 <reportElement style="Column header" x="433" y="0" width="80" height="20" uuid="deb23d85-47bb-4de6-978e-113ff117188c"/>
   			 <textElement textAlignment="Right">
   				 <font size="10" isBold="false"/>
   			 </textElement>
   			 <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
   		 </textField>
   		 <textField evaluationTime="Report">
   			 <reportElement style="Column header" x="513" y="0" width="40" height="20" uuid="c6ce76ba-6e11-41e2-9415-72dc2eff001b"/>
   			 <textElement>
   				 <font size="10" isBold="false"/>
   			 </textElement>
   			 <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
   		 </textField>
   		 <textField pattern="EEEEE dd MMMMM yyyy">
   			 <reportElement style="Column header" x="0" y="0" width="197" height="20" uuid="5a79d2a0-bcde-4ad5-adf0-c4eb2066f6dd"/>
   			 <textElement>
   				 <font size="10" isBold="false"/>
   			 </textElement>
   			 <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
   		 </textField>
   	 </band>
    </pageFooter>
    <summary>
   	 <band height="206" splitType="Stretch">
   		 <lineChart>
   			 <chart evaluationTime="Report">
   				 <reportElement x="50" y="6" width="424" height="200" uuid="f18a1698-c3be-4ee1-964e-8a7814093ace"/>
   				 <chartTitle/>
   				 <chartSubtitle/>
   				 <chartLegend/>
   			 </chart>
   			 <categoryDataset>
   				 <categorySeries>
   					 <seriesExpression><![CDATA["SERIES 1"]]></seriesExpression>
   					 <categoryExpression><![CDATA[$F{month} + " " + $F{year}]]></categoryExpression>
   					 <valueExpression><![CDATA[$F{dnacount}]]></valueExpression>
   				 </categorySeries>
   			 </categoryDataset>
   			 <linePlot isShowLines="true">
   				 <plot labelRotation="75.0"/>
   				 <categoryAxisFormat labelRotation="75.0">
   					 <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
   				 </categoryAxisFormat>
   				 <valueAxisFormat>
   					 <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
   				 </valueAxisFormat>
   			 </linePlot>
   		 </lineChart>
   	 </band>
    </summary>
</jasperReport>

I am truly scratching my head as to how to make this work and I would really appreciate anyone's guidance as I'm at the point where I'm throwing my hands in the air. I can make this report work by running it through Java and using a main class to inject the values via the Jasper reports report filler etc, but when I upload it to the openkm reports server it just bombs every time. please let me know what I'm doing wrong if anyone has the insight to give me any guidance I would be very very appreciative

p.s. I have deployed my code into a JAR file and copied it to $TOMCAT_HOME/lib and $TOMCAT_HOME/webapps/OpenKM/WEB-INF/lib and it still cant find it

Many thanks!!

[jllort]

Can you share here the compiled jar to take a look into and test from our side?

Deploying jar into tomcat/lib folder should be enough, another option might be put the classes ( with complete path ) into webapps/classes/etc.. but before going in this direction upload here the jar file and we will take a look into.

[dsmith_au]

Hi,

thanks for your reply. Here is the attached JAR file as requested.

In order to upload it correctly, I had to zip it and change the file extension to .png. Simply remove the .png extension and then unzip it.

Many thanks

Darren

[dsmith_au]

Just in case the file upload did not work, the file can be found here

https://drive.google.com/open?id=1neGXX ... riHVFYa4tM

thanks

Darren

[jllort]

I have tested from scripting and for me worked fine. From Jasper reports should working too. Only I have copied the jar file into tomcatXXX/libs folder and then I have started the application ( nothing else ).

Selección_114.png (63.4 KiB) Viewed 1958 times

Only consider using older jasper reports, because if you look into OpenKM community dependencies now we are using:

Code: Select all

<groupId>net.sf.jasperreports</groupId>
      <artifactId>jasperreports</artifactId>
<version>5.6.1</version>

This is the file https://github.com/openkm/document-mana ... er/pom.xml