Overview

This module enables the user to generate execution report of batches on the basis of the batch class, start date and end date. Reports can be calculated per module, per plugin or on user basis in units of seconds, minutes or hours. The ways to generate report are as follows:

  • A cronjob expression property: dcma.report.cronjob.expression present at ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/dcma-reporting/dcma-report-scheduling.properties’ governs automatic execution of clean up on Ephesoft database and update of Report database.

For example: dcma.report.cronjob.expression=0 0 1 * * ? * . The scheduler service is scheduled to run at 1 a.m. every day by default. User can change the configuration from this file.

  • Click on SyncDb button. It updates report database with new set of finished batches in report database since last update time up to current time. Then it generates data for display for user on screen.
  • Click on GO button. This will generate data for display for user on screen from current state of report database content. Only difference with SyncDB button is that it does not update report database with new finished batches.

The module aggregates the report-data on the basis of user specified parameters.

Admin has the options of generating report for module, plugin or all/one users.
Admin can:

  • Get reports per batch/document/page for a Workflow Type/User for a specified time for the selected batch class/all batch classes.
  • Get total records (batches/documents/pages processed) for a specified time for the selected batch class/all batch classes.

To create a new report database to load the report data manually, run the “init-data.sql” found at

“{Ephesoft-Home}/WEB-INF/classes/META-INF/dcma-reporting/init-data.sql”.

Configuration

Property File Configurations

Property File: ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/dcma-performance-reporting/dcma-report-db.properties’

This property file needs to be configured for connecting to report database. By default, it is configured to point to the report database created by Ephesoft. If user wants to use different database, this property file needs to be configured accordingly:

 

Configurable property Type of value Value options Description
hibernate.connection.
password
String NA Password of report database.
hibernate.connection.
username
String NA Username of report database.
hibernate.connection.url String NA Connection string for report database. Example: For MySQL , it shall be jdbc:mysql://localhost:3306/report
hibernate.connection.
driver_class
String For MySQL: com.mysql.jdbc.Driver

For MSSQL:
jdbc:jtds:sqlserver://localhost;
databaseName=report;user=<databaseUsername>;password=<databasePassword>
Driver class for database connection. Example: for
MySQL, it should be set to com.mysql.jdbc.Driver
hibernate.show_sql Boolean true

false
Whether to show report SQL commands at console or not. Used for basic troubleshooting of report queries.
hibernate.dialect String NA Dialects used by hibernate to use with database.

 

Property file: {Ephesoft-Home}/WEB-INF/classes/META-INF/dcma-reporting/hibernate.cfg.xml’:-

 

Configurable property Type of value Value options Description
connection.driver_class String For MySQL: com.mysql.jdbc.Driver
For MSSQL: jdbc:jtds:sqlserver://localhost;
databaseName=report;user=<databaseUsername>;password=<databasePassword>
Driver class for database connection. Example: for MySQL, it should be set to com.mysql.jdbc.Driver
connection.url String NA Connection string for report database. Example: For MySQL, it shall be jdbc:mysql://localhost:3306/report
connection.username String NA Username of report database.
connection.password String NA Password of report database.
dialect String NA Dialects used by hibernate to use with database.
show_sql Boolean true

false
Whether to show report SQL commands from report database at console or not. Used for basic troubleshooting of report queries.

 

Property file: ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/dcma-reporting/hibernate-dcma.cfg.xml’:-

This file contains connection details to connect to existing ephesoft database.

 

Configurable property Type of value Value options Description
connection.driver_class String For MySQL: com.mysql.jdbc.Driver
For MSSQL: jdbc:jtds:sqlserver://localhost;
databaseName=ephesoft;user'=
<databaseUsername>;password=<databasePassword>
Driver class for database connection. Example: for MySQL, it should be set to com.mysql.jdbc.Driver
connection.url String NA Connection string for ephesoft database. Example: For MySQL, it shall be jdbc:mysql://localhost:3306/ephesoft
connection.username String NA Username of ephesoft database.
connection.password String NA Password of ephesoft database.
dialect String NA Dialects used by hibernate to use with database.
show_sql Boolean true
false
Whether to show report SQL commands from Ephesoft database at console or not. Used for basic troubleshooting of reporting queries.

 

Property file: ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/application.properties’:-

 

Configurable property Type of value Value options Description
enable.reporting String True
False
Whether or not report UI will be displayed to the user and back up report-data files would be created.

Property file: ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/ dcma-util/dcma-backup-service.properties’:-

 

Configurable property Type of value Value options Description
backup.report_folder String NA Path to the folder containing back up files for all executed plugins for batches. For example: C:\\Ephesoft\\SharedFolders/report-data

 

Clean Up procedure on Ephesoft database

A clean up procedure creation query is present in ‘{Ephesoft-Home}/WEB-INF/classes/META-INF/ dcmareporting/source.hbm.xml’ that cleans up the Ephesoft database with the details of a finished batch after reporting data for it has been executed. User shall run it on its ephesoft database manually as a query to add clean up procedure on Ephesoft database. The procedure must exist on Ephesoft database if Ephesoft reporting is used by user.

This procedure for MSSQL is:

Use <Ephesoft_db_name>;

CREATE procedure clean_batch_Instance(@batch_instance_id varchar(8000),@delimiter_char varchar(25))

as

BEGIN

SET NOCOUNT ON;

Declare @batch_Instance_identifier varchar(255)

Declare @Pos1 Int

Declare @Pos2 Int

Declare @DbId Int

Declare @LobId Int

 

Set @Pos1=1

Set @Pos2=1

While @Pos1 < Len(@batch_instance_id)

Begin

Set @Pos1 = CharIndex(@delimiter_char,@batch_instance_id,@Pos1)

Set @batch_Instance_identifier = Substring(@batch_instance_id,@Pos2,@Pos1-@Pos2);

 

DECLARE delete_variable CURSOR FOR

Select DBID_, LOB_ FROM jbpm4_variable where EXECUTION_ in (

Select DBID_ from jbpm4_execution where (ID_ LIKE ‘%’ + @batch_Instance_identifier + ‘.%’ OR ID_ LIKE ‘%’ + @batch_Instance_identifier));

 

DECLARE delete_execution CURSOR FOR

SELECT DBID_ FROM jbpm4_execution where ID_ LIKE ‘%’ + @batch_Instance_identifier + ‘.%’;

 

DECLARE delete_actinst CURSOR FOR

SELECT DBID_ FROM jbpm4_hist_actinst where (EXECUTION_ LIKE ‘%’ + @batch_Instance_identifier + ‘.%’ OR EXECUTION_ LIKE ‘%’ + @batch_Instance_identifier);

 

DECLARE delete_procinst CURSOR FOR

SELECT DBID_ FROM jbpm4_hist_procinst where (ID_ LIKE ‘%’ + @batch_Instance_identifier + ‘.%’ OR ID_ LIKE ‘%’ + @batch_Instance_identifier);

 

 

OPEN delete_variable

FETCH NEXT FROM delete_variable INTO @DbId,@LobId

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

PRINT @DbId;

PRINT @LobId;

DELETE FROM jbpm4_variable where DBID_ = @DbId;

DELETE FROM jbpm4_lob where DBID_ = @LobId;

END TRY

BEGIN CATCH

PRINT ‘EXCEPTION OCCURRED WHILE DELETING FROM jbpm4_variable’;

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM delete_variable INTO @DbId,@LobId

END

CLOSE delete_variable

DEALLOCATE delete_variable

 

OPEN delete_execution

FETCH NEXT FROM delete_execution INTO @DbId

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

DELETE FROM jbpm4_execution where DBID_ = @DbId;

END TRY

BEGIN CATCH

PRINT ‘EXCEPTION OCCURRED WHILE DELETING FROM jbpm4_execution’;

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM delete_execution

INTO @DbId

END

 

CLOSE delete_execution

DEALLOCATE delete_execution

 

OPEN delete_actinst

FETCH NEXT FROM delete_actinst INTO @DbId

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

DELETE FROM jbpm4_hist_actinst where DBID_ = @DbId;

END TRY

BEGIN CATCH

PRINT ‘EXCEPTION OCCURRED WHILE DELETING FROM jbpm4_hist_actinst’;

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM delete_actinst

INTO @DbId

END

 

CLOSE delete_actinst

DEALLOCATE delete_actinst

 

OPEN delete_procinst

FETCH NEXT FROM delete_procinst INTO @DbId

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

DELETE FROM jbpm4_hist_procinst where DBID_ = @DbId;

END TRY

BEGIN CATCH

PRINT ‘EXCEPTION OCCURRED WHILE DELETING FROM jbpm4_hist_procinst’;

END CATCH

FETCH NEXT FROM delete_procinst

INTO @DbId

END

 

CLOSE delete_procinst

DEALLOCATE delete_procinst

 

Set @Pos2=@Pos1+1

Set @Pos1 = @Pos1+1

End

END

 

This procedure for MySQL is:

Use <Ephesoft_db_name>;

DELIMITER //

 

CREATE procedure clean_batch_Instance(in batch_instance_id varchar(8000),in delimiter_char varchar(25))

BEGIN

Declare batch_Instance_identifier VARCHAR(255);

Declare Pos1 INT UNSIGNED DEFAULT 1;

Declare Pos2 INT UNSIGNED DEFAULT 1;

Declare DbId Int;

DECLARE no_more_rows BOOLEAN;

While Pos1 < Length(batch_instance_id) DO

Set Pos1 = LOCATE(delimiter_char,batch_instance_id,Pos1);

Set batch_Instance_identifier = Substring(batch_instance_id,Pos2,Pos1-Pos2);

SET foreign_key_checks = 0;

 

BEGIN

DECLARE delete_lob CURSOR FOR

Select LOB_ FROM jbpm4_variable where EXECUTION_ in (

Select DBID_ from jbpm4_execution where ID_ LIKE CONCAT(‘%’,batch_Instance_identifier,’.%’)

OR ID_ LIKE CONCAT(‘%’,batch_Instance_identifier));

DECLARE delete_variable CURSOR FOR

Select DBID_ FROM jbpm4_variable where EXECUTION_ in (

Select DBID_ from jbpm4_execution where ID_ LIKE CONCAT(‘%’,batch_Instance_identifier,’.%’)

OR ID_ LIKE CONCAT(‘%’,batch_Instance_identifier));

DECLARE delete_execution CURSOR FOR

SELECT DBID_ FROM jbpm4_execution where ID_ LIKE CONCAT(‘%’,batch_Instance_identifier,’.%’) OR ID_ LIKE CONCAT(‘%’,batch_Instance_identifier);

DECLARE delete_actinst CURSOR FOR

SELECT DBID_ FROM jbpm4_hist_actinst where EXECUTION_ LIKE CONCAT(‘%’,batch_Instance_identifier,’.%’) OR EXECUTION_ LIKE CONCAT(‘%’,batch_Instance_identifier);

DECLARE delete_procinst CURSOR FOR

SELECT DBID_ FROM jbpm4_hist_procinst where ID_ LIKE CONCAT(‘%’,batch_Instance_identifier,’.%’) OR ID_ LIKE CONCAT(‘%’,batch_Instance_identifier);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

 

SET no_more_rows = FALSE;

OPEN delete_lob;

read_loop: LOOP

FETCH delete_lob INTO DbId;

IF no_more_rows THEN

LEAVE read_loop;

END IF;

 

DELETE FROM jbpm4_lob where DBID_ = DbId;

 

END LOOP;

CLOSE delete_lob;

 

SET no_more_rows = FALSE;

OPEN delete_variable;

read_loop: LOOP

FETCH delete_variable INTO DbId;

IF no_more_rows THEN

LEAVE read_loop;

END IF;

 

DELETE FROM jbpm4_variable where DBID_ = DbId;

 

END LOOP;

CLOSE delete_variable;

 

SET no_more_rows = FALSE;

OPEN delete_execution;

read_loop: LOOP

FETCH delete_execution INTO DbId;

IF no_more_rows THEN

LEAVE read_loop;

END IF;

 

DELETE FROM jbpm4_execution where DBID_ = DbId;

 

END LOOP;

CLOSE delete_execution;

 

SET no_more_rows = FALSE;

OPEN delete_actinst;

read_loop: LOOP

FETCH delete_actinst INTO DbId;

IF no_more_rows THEN

LEAVE read_loop;

END IF;

 

DELETE FROM jbpm4_hist_actinst where DBID_ = DbId;

 

END LOOP;

CLOSE delete_actinst;

 

SET no_more_rows = FALSE;

OPEN delete_procinst;

read_loop: LOOP

FETCH delete_procinst INTO DbId;

IF no_more_rows THEN

LEAVE read_loop;

END IF;

 

DELETE FROM jbpm4_hist_procinst where DBID_ = DbId;

 

END LOOP;

CLOSE delete_procinst;

 

SET foreign_key_checks = 1;

Set Pos2=Pos1+1;

Set Pos1=Pos1+1;

END;

END WHILE;

END//

DELIMITER ;

Reports generation

Reports can be generated by user interface provided for reporting.

Reports generation from UI

In order to run Ephesoft reporting to update report database up to current state, user needs to click on syncDB button.

Select any option from

  • modules, plugin and user.
  • start and end date
  • Unit of time
  • Batch class.

then click on GO.

Reporting data will give total system statistics count for batches, documents and pages processed and time taken statistics displayed in tabular format for Batches, documents and pages processed.

 

400px-3.1_Performance_Reporting_10001

 

Troubleshooting

Following are few common error messages seen due to mal-functioning of the feature:

 

S. No. Error message Possible root cause
1 Another instance of reporting is already in progress. Reports database is already in use by some other user. Either wait for other user to complete its work and then try gain, or edit Is_Already_in_use column value in last_update_time table of report database to 0.
2 XPathExpression Exception occurred while executing reports. There was some error in reading back up xml files from report-data folder.
3 Parser Configuration Exception occurred while executing reports. There was some error in reading back up xml files from report-data folder.
4 SAX Exception occurred while executing reports. There was some error in reading back up xml files from report-data folder.
5 The ID_ field from hbpm4_proc_hist table is not correct. Data is inconsistent in jbpm tables.
6 Unable to clean up data If cleanup for ephesoft database and back up xml files for plugins failed.
7 Exception: file not found dcma-report-db.properties Properties file is not present.
8 Exception: IOException, cannot create input stream. dcma-report-db.properties Properties file is either not present or is corrupted
9 Cryptography Exception hibernate.connection.password in dcma-report-db.properties is either not encrypted or not present.
10 Exception occurred while getting report connection. Connection to the database could not be made. Check the dcma-report-db.properties Properties file for the configuration parameters. Either some parameter is missing or is invalid.

 

 

 

 

 

 

Was this article helpful to you?

wikiadmin

Comments are closed.