Return query from PostgreSQL function

1. Create a user defined type or a dummy table object. Fields and data type of the fields need to be same as the returning fields value of the function.

CREATE type production_int_time_figures_func_type AS(
project varchar,
language varchar,
daily_duration_sec numeric,
week_duration_sec numeric,
tot_duration_sec numeric
);

     2. Create the function
(Function to get interviewed time in seconds by giving start and end dates as parameters0

CREATE OR REPLACE FUNCTION production_int_time_figures_func(run_date date, from_date date)
  RETURNS SETOF production_int_time_figures_func_type AS
$BODY$
DECLARE
r production_int_time_figures_func_type%rowtype;
BEGIN
FOR r IN 
SELECT timecard.project, UPPER(trim(timecard.language)) ,
sum(CASE
            WHEN (date(Run_Date) - date(timecard.end_date)) <= 1 AND (date(timecard.end_date) > date(From_date)) THEN timecard.duration
            ELSE 0::numeric END) AS daily_duration_sec,
sum(CASE
            WHEN (date(Run_Date) - date(timecard.end_date)) <= 7 AND (date(timecard.end_date) > date(From_date)) THEN timecard.duration
            ELSE 0::numeric END) AS week_duration_sec,
sum(CASE WHEN (date(timecard.end_date) > date(From_date)) THEN timecard.duration ELSE 0::numeric END) AS tot_duration_sec
FROM accounts.timecard
WHERE (timecard.bcode::integer IN
(SELECT production_tasks_lookup.bcode FROM production_tasks_lookup
WHERE production_tasks_lookup.task_type::text = UPPER(TRIM('INT'::text))))
AND (timecard.project::text IN ( SELECT prodrep.project::character varying AS project
FROM accounts.prodrep))
GROUP BY timecard.project, UPPER(trim(timecard.language))
ORDER BY timecard.project, UPPER(trim(timecard.language))
LOOP 
RETURN NEXT r;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE


CREATE OR REPLACE FUNCTION production_int_time_figures_func(run_date date, from_date date)
  RETURNS SETOF production_int_time_figures_func_type AS
$BODY$
DECLARE
r production_int_time_figures_func_type%rowtype;
BEGIN
FOR r IN 
SELECT ....... FROM ..........
LOOP 
RETURN NEXT r;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

    3. Retrieve data from function
select * FROM production_int_time_figures_func('2011-10-12', '2009-09-12');

Pass parameters from main report to table's data source in iReport

Following steps help to pass parameters from main report to table data source in iReport (3.6.7 or 4.1.2)

1. Create required parameters in the main report
• right clicking on the parameters section of main report
• add parameter
• set the properties (data type)
• set default expression (optional)

2. Create parameters within the sub data source
• Set name , data type as same as main report parameters

3. Map the parameters in main report and data set
• Right click on the table and click on Edit Table Dataset
• Select the parameters
• Click on add
• Choose the parameter (created in dataset) from upper combo
• Assign value expression (parameter which added in step 1)

jasper variable expressions + ireport

Divide by zero error
use of following command in expression allows to eliminate decide by zero error in jasper. $F{feild_name1} and $F{feild_name2} are fields value return from query. if the denominator ($F{feild_name1}) is equal to 0 then command returns 0. Else this returns $F{feild_name2}.doubleValue() / $F{feild_name1}.doubleValue().

new Double(($F{feild_name1}.doubleValue() == 0.00) ? 0.00 : ($F{feild_name2}.doubleValue() / $F{feild_name1}.doubleValue() * 100))

new Double(($F{wave}.toString() == null) ? $F{project} : ($F{project}.toString() + $F{wave}.toString()))

Java servlet use in expression area.
Utils() is the Java class name. com.mycompany.utils is Java package name. filed value is used in Java servlet as a string value and return the output value.

(new com.mycompany.utils.Utils()).hourFormat($F{feild_name2}.toString())

Return current date
use of following  phase in parameter expression area will set set current date as default parameter value.

new java.util.Date()

Return exact date
use of following  phase in parameter expression area will set set "2000- 1- 1" as default parameter value.

new java.util.Date(new GregorianCalendar(2000, 1, 1).getTime().getTime())

If, Elsif and Else condition in Jasper 



new String(($F{project_type}.toString() == "I") ? "IT" :
    (new String(($F{project_type}.toString() == "M") ? "Medical" :
        ((new String(($F{project_type}.toString() == "C") ? "Consumer" :
            ((new String(($F{project_type}.toString() == "X") ? "Other/Dont know" :
            ("- ") )))
                )))
           )
     )
)

Read Java servlet in ireport + convert seconds value to hh-mm-ss format

1.Create a java file        

        $ vim utils.java

package ronin.java.servlets;
public class sec_convert
{
        public static String hourFormat(String[] args)
        {
                int duration = Integer.parseInt(args[0]);
                int seconds = duration % 60;
                int minutes = ( duration % 3600 ) / 60 ;
                int hours = duration / 3600 ;


                StringBuffer buffer = new StringBuffer( 10 );


                buffer.append( hours < 10 ? "0" + hours : hours );
                buffer.append( ":" );
                buffer.append( minutes < 10 ? "0" + minutes : minutes );
                buffer.append( ":" );
                buffer.append( seconds < 10 ? "0" + seconds : seconds );


                return( buffer.toString() );
//              System.out.println(buffer.toString() );
        } 
}

Navigate to folder where source file is

2. Compile the class using following command
               $  javac -d . utils.java
this creates "ronin/java/servlets" folder structure

3.To make the jar file
               $ JAR CF jar_file_name.jar class_name.class
               $ jar cf utils.jar utils.class

4. Use following command in Ireport variable expression area

(new ronin.java.servlets.utils()).hourFormat($F{tot_duration_sec}.toString())


return values of $F{tot_duration_sec} field will be displayed in hh-mm-ss format