package it.bisi.report.jasper;

import it.bisi.Utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import java.util.HashMap;
import java.util.Map;

import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.view.JasperViewer;

public class ExecuteReport {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		runReport(args[0],args[1],args[2],args[3]);
	}
	
	public static Connection connectDB(String databaseName, String userName, String password) {
	      Connection jdbcConnection = null;
	      try{
	         Class.forName("com.mysql.jdbc.Driver");
	         String host="jdbc:mysql://"+databaseName;
	         //System.out.println("host="+host);
	         jdbcConnection = DriverManager.getConnection("jdbc:mysql://"+databaseName,userName,password);
	      }catch(Exception ex) {
	         String connectMsg = "Could not connect to the database: " + ex.getMessage() + " " + ex.getLocalizedMessage();
	         
	         ex.printStackTrace();
	      }
	      return jdbcConnection;
	   }
	
	public static void runReport(String databaseName, String userName, String password,String report_identifier) {
	      try{
	         Connection conn = connectDB(databaseName, userName, password);
	         InputStream inputStream = Utils.class.getResourceAsStream("/it/bisi/resources/report1.jasper");
	         Map prms = new HashMap();
	         //prms.put("IDENTIFIER", identifier);
	         //minus group...
	       //find out the group on rows and other report options
				Statement stmt_rows=conn.createStatement();
				stmt_rows.execute("select * from report_definitions where id='"+report_identifier+"'");

				ResultSet rs_repdef =stmt_rows.getResultSet();
				rs_repdef.next();
				String identifier=rs_repdef.getString("data_set_id");
				String group_rows=rs_repdef.getString("group_question_id");
				String output_file_name=rs_repdef.getString("output_filename");
				String output_format=rs_repdef.getString("output_format");
				String report_type=rs_repdef.getString("report_type");
								
//				System.out.println(identifier);
//				prms.put("IDENTIFIER", identifier);
				prms.put("REPORT_IDENTIFIER", report_identifier);
				stmt_rows.close();
	         //
	         
	         String query="SELECT a.value title,b.value srtdt, " +
	         		"c.value enddt,d.value response,e.value percentage,type,group_id  " +
	         		"FROM info_"+identifier+" a,info_"+identifier+" b," +
	         				"info_"+identifier+" c, info_"+identifier+" d," +
	         				"info_"+identifier+" e," +
	         				"(SELECT distinct q.group_id,case when instr(type,'Closed_Percentage')=0 then " +
	         				"case when instr(type,'Closed_Scale')=0 then 'N' else 'AVG' end " +
	         				"else 'PERC' end type FROM questions_"+identifier+" q,meta_"+identifier+" m " +
	         				"where m.question_id=q.id and parent_id=0 and q.id!='"+group_rows+"' and " +
	         				"(type like '%Closed_Percentage%' or type like '%Closed_Scale%')) t "+
	         		"where a.id='Titel vragenlijst' " +
	         		"and b.id='Startdatum' " +
	         		"and c.id='Einddatum' " +
	         		"and d.id='unieke respondenten' " +
	         		"and e.id='Respons percentage' ";
	         
	         prms.put("QUERY", query);
	         JasperPrint print = JasperFillManager.fillReport(inputStream, prms, conn);
	         JasperViewer.viewReport(print);
	      }catch(Exception ex) {
	         String connectMsg = "Could not create the report " + ex.getMessage() + " " + ex.getLocalizedMessage();
	         
	         ex.printStackTrace();
	      }
	   }

}

@@ -423,16 +423,74 @@
 Viewer;%0A
+import net.sf.jasperreports.engine.JasperExportManager;%0A%0A%0A
 %0Apublic 
@@ -2321,45 +2321,9 @@
 %09%09%09%0A
-//%09%09%09%09System.out.println(identifier);
+%09
 %0A//%09
@@ -3584,24 +3584,357 @@
 ;%0A%09         
+// if output format is pdf: create the pdf (current dir for now), else lanch the viewer, so you can save it in other formats%0A%09         if(%22pdf%22.equals(output_format))%7B%0A%09        %09 System.out.println(%22to pdf%22);%0A%09        %09 JasperExportManager.exportReportToPdfFile(print, output_file_name+%22.%22+output_format);%0A%09         %7Delse%7B%0A%09        %09
 JasperViewer
@@ -3949,24 +3949,36 @@
 ort(print);%0A
+%09         %7D%0A
 %09      %7Dcatc

create pdf if output_format is pdf, else launch viewer