/*
  i-net software provides programming examples for illustration only, without warranty
  either expressed or implied, including, but not limited to, the implied warranties
  of merchantability and/or fitness for a particular purpose. This programming example
  assumes that you are familiar with the programming language being demonstrated and
  the tools used to create and debug procedures. i-net software support professionals
  can help explain the functionality of a particular procedure, but they will not modify
  these examples to provide added functionality or construct procedures to meet your
  specific needs.
  © i-net software 1998-2010
*/
package samples.jdbc;

import java.io.FileOutputStream;

import com.inet.report.*;

/**
 * This sample class shows how to create a report that is based on a SQL command.
 * The vital method call is Datasource.createTableSourceCommand(String, String).
 * This method checks the set sql statement against the database and creates all DatabaseFields.
 * 
 * To execute this sample the following is necessary:
 * - an installed JDBC driver for MS SQL Server (e.g. i-net SERO, bundled with i-net Clear Reports)
 * - the MS SQL Server northwind database
 * - Data Source Configuration for the northwind database, created with Data Source Manager 
 *   in i-net Designer or Configuration Manager
 */
public class ReportBasedOnSQLCommand {
    private static final String SQL_COMMAND = "SELECT productname, categoryname, picture FROM categories c INNER JOIN products p ON c.categoryid = p.categoryid WHERE p.unitprice>=30 ORDER BY c.categoryid";
    
    /**
     * main method of the sample - sets a SQL command as a datasource.
     * @param args not needed
     */
    public static void main(String[] args) {
        try{
            //create a new report
            Engine reportEngine = RDC.createEmptyEngine(Engine.EXPORT_PDF);
            
            //define the datasource. (in this sample it is a northwind database on the MS SQL Server)
            Datasource ds = reportEngine.getDatabaseTables().createDatasource("<dataSourceConfigurationName>");  // e.g. Northwind
            
            // set the password if it is not saved in the Data Source Configuration
            ds.setPassword( "<Password>" );
            
            //!!!  define a SQL statement as base of the report 
            TableSource sqlCommand = ds.createTableSourceCommand("productsAndCategories",SQL_COMMAND);
            
            //put database fields in the report
            Area dArea                      = reportEngine.getArea("D");
            Section detailSection           = dArea.getSection(0);
            DatabaseField fieldProductName  = sqlCommand.getDatabaseField("productname");
            DatabaseField fieldCategoryName = sqlCommand.getDatabaseField("categoryname");
            DatabaseField fieldPicture      = sqlCommand.getDatabaseField("picture");
            
            detailSection.setHeight(1100);
            detailSection.addFieldElement(fieldCategoryName,50,50,2000,100);
            detailSection.addDatabasePicture(fieldPicture,2100,50,1000,1000);
            detailSection.addFieldElement(fieldProductName,3200,50,2000,100);
            
            //generate the pdf document
            reportEngine.execute();
            
            //put the document on harddisk
            int pageCount = reportEngine.getPageCount();
            FileOutputStream fos = new FileOutputStream("c:/test.pdf");
            for(int i=0;i<pageCount;i++){
                fos.write(reportEngine.getPageData(i+1));
            }
            fos.flush();
            fos.close();
            System.exit(0);
            
        }catch(ReportException re){
            System.err.println("generating report failed");
            re.printStackTrace();
        } catch (Exception e) {
            System.err.println("creating pdf file failed");
            e.printStackTrace();
        }
    }
}