Dynamic queries in BIRT - query text replacement

var selectedParams = params["calltypes"].value;
var selectedString = "";
for (var counter=0; counter < selectedParams.length; counter++) {
 selectedString += selectedParams[counter] + ",";
}
selectedString = selectedString.substring(0,selectedString.length-1);
var before = this.queryText.toString();
var injection = "";
injection =  selectedString;
this.queryText = before.replace('53279',injection);

Today's links

Building Highly Scalable Servers with Java NIO
Scripting for the Java Platform
https://scripting.dev.java.net/
Javamex
Encrypting a String with DES
Fixed Floating Elements
http://fahadzia.wordpress.com/2008/08/30/using-inlike-clause-in-birt/

Copy files from/to between local and HTTP(s), (S)FTP, local, mail filesystems

Using Apache VFS. Sweet.

package jobs;

import org.apache.commons.vfs.FileFilter;
import org.apache.commons.vfs.FileFilterSelector;
import org.apache.commons.vfs.FileObject;
import org.apache.commons.vfs.FileSelectInfo;
import org.apache.commons.vfs.FileSystemException;
import org.apache.commons.vfs.FileSystemOptions;
import org.apache.commons.vfs.impl.StandardFileSystemManager;
import org.apache.commons.vfs.provider.sftp.SftpFileSystemConfigBuilder;
import org.quartz.Job;
import org.quartz.JobDataMap;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class FileCopyJob implements Job {
    Logger log = LoggerFactory.getLogger(getClass());
    String fileNameStartsWith = null;
    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {
        long startPoint = System.nanoTime();
        log.info("Reading config.");
        JobDataMap data = context.getJobDetail().getJobDataMap();
        String fromDirURL = data.getString("fromDirURL");
        String toDirURL = data.getString("toDirURL");
        fileNameStartsWith = data.getString("fileNameStartsWith");
        log.debug("fromDirURL=                 {}",fromDirURL);
        log.debug("toDirURL=                   {}",toDirURL);
        log.debug("fileNameStartsWith=       {}",fileNameStartsWith);
        log.info("Instantiating File System Manager (FSM).");
        StandardFileSystemManager fileSystemManager = null;
        FileObject fromDirFileObject = null;
        FileObject toDirFileObject   = null;
        try {
            fileSystemManager = new StandardFileSystemManager();
            log.info("Creating file objects.");
            FileSystemOptions opts = new FileSystemOptions();
            SftpFileSystemConfigBuilder.getInstance().setStrictHostKeyChecking(opts, "no");
            fileSystemManager.init();
            fromDirFileObject = fileSystemManager.resolveFile(fromDirURL,opts);
            toDirFileObject = fileSystemManager.resolveFile(toDirURL,opts);
            Boolean fromDirFileObjectExists = fromDirFileObject.exists();
            Boolean toDirFileObjectExists = toDirFileObject.exists();
            log.info("fromDirFileObjectExists? {}", fromDirFileObjectExists);
            log.info("toDirFileObjectExists?   {}", toDirFileObjectExists);
            if (fromDirFileObjectExists==false || toDirFileObjectExists==false) {
                log.error("Please check file paths, either directory does not exist.");
                throw new RuntimeException("Directory does not exists.");
            }
            FileFilter ff = new FileFilter()
            {
                public boolean accept(FileSelectInfo fileInfo)
                {
                    FileObject fo = fileInfo.getFile();
                    return fo.getName().getBaseName().startsWith(fileNameStartsWith);
                }
            };
            FileObject[] foundFiles = null;
            foundFiles = fromDirFileObject.findFiles(new FileFilterSelector(ff));
            if (foundFiles.length < 1 ) {
            log.info("No files found.");   
            } else {
                for (FileObject foundFile:foundFiles) {
                    String baseName = foundFile.getName().getBaseName();
                    String destinationFullName = toDirURL + toDirFileObject.getName().SEPARATOR + baseName;
                    log.debug("Trying to move {} into {}",baseName,destinationFullName);
                    foundFile.moveTo(fileSystemManager.resolveFile(destinationFullName,opts));
                    log.info("Moved {}",foundFile);
                }
            } // no code beyond this point
        } catch (FileSystemException e) {
            log.error("Something bad happened with the file system you are trying to reach. ", e);
        } finally {
            log.info("Closing FSM.");
            fileSystemManager.close();
        }
        long endPoint = System.nanoTime();
        log.info("Operation took {} nanoseconds.", (endPoint-startPoint));
    }

}

Number format and report title in BIRT, programmatically

Like this:
if (params["forexport"].value == 'n') {
this.getStyle().numberFormat = "Percent";
this.helpText = reportContext.getDesignHandle().title;
}

Nice article on CompletionService

Under-appreciated Java Classes Part I: CompletionService

Dynamic query in BIRT

Pretend, I have a dataset backed by the following SQL query:

select
COUNT(*)
from ipcc_baA.dbo.Personal_Callback_List pcl
where pcl.FirstName IN ('something')
and pcl.CallStatus <> 'C'

and a parameter, named cctype, type String, returning either value 'HIM' or 'AB'. No duplicate values.

Based on the parameter value, I want to change the query text. For instance, write something into the IN clause within the brackets. Or, change a whole row.

A not elegant, but working way is: using the dataset's beforeOpen method, like this:

if (params["cctype"].value == 'HIM') {
 var inject = '\'H1\',\'H2\',\'H3\',\'H4\',\'I\',\'M\'';
 this.queryText = this.queryText.replace('\'INJECT\'',inject);
}
if  (params["cctype"].value == 'AB') {
var inject = 'where pcl.AlternateVDN IN (\'reserveA1\',\'reserveA2\',\'reserveA3\',\'reserveB1\',\'reserveB2\')';
 this.queryText = this.queryText.replace('where pcl.FirstName IN (\'INJECT\')',inject);
}
}

And of course, modifying the query itself:

select
COUNT(*)
from ipcc_baA.dbo.Personal_Callback_List pcl
where pcl.FirstName IN ('INJECT') --neupravovat!!!
and pcl.CallStatus <> 'C'

And voilá, it works. Of course, one should take extra care not to change the original query, otherwise the replace method won't find the needle in the haystack.

The query, in the data set

The beforeOpen method

Finding BirtDateTime

I noticed this today: if the SQL dataset column type is DATETIME, and you set it to DateTime in BIRT as well, the report will show it like this (for 2nd August, 2010 00:00:00, Central European Summer Time):

Mon Aug 02 00:00:00 CEST 2010

This is not something Javascript can accept - although I tried the toLocaleString() method and it worked. However, I just needed the date part. toLocaleDateString() did not show anything.

This is where I tried the BirtDateTime class; it accepts the DateTime strings nicely. Now I can do this for instance:

BirtDateTime.day(row["Day"]) + '. ' + BirtDateTime.month(row["Day"],2) + ' ' + BirtDateTime.year(row["Day"]) 

It will show (provided the browser locale is set to Czech):
2. srpna 2010

Transact SQL loop

This came quite handy today:

DECLARE @FROM DATETIME
DECLARE @TO DATETIME
DECLARE @TEMP DATETIME
TRUNCATE TABLE administration.dbo.HalfHours
SET @FROM = '2006-01-01 00:00:00'
SET @TO = '2012-01-01 00:00:00'
SET @TEMP = @FROM
WHILE @TEMP < @TO
BEGIN
PRINT @TEMP
INSERT INTO administration.dbo.HalfHours (XDateTime) VALUES (@TEMP);
SET @TEMP = DATEADD(mi,30,@TEMP)
END

CREATE INDEX IDX_ONE ON administration.dbo.HalfHours(XDateTime)
SELECT * FROM administration.dbo.HalfHours

Bandwidth calculator

Via  http://www.bandcalc.com/

IP Precedence, TOS & DSCP conversion

IP Precedence, TOS & DSCP
Via http://www.bogpeople.com/