package com.aspose.slides.demos.jsf;

import java.io.Serializable;

import java.io.*;
import java.util.*;

import javax.servlet.*;
import javax.servlet.http.*;

import com.aspose.slides.*;

import javax.media.jai.JAI;
import javax.media.jai.RenderedOp;
import java.io.*;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import javax.faces.context.FacesContext;
import javax.faces.event.*;
import javax.faces.component.UIComponent;

import java.lang.reflect.*;

public class Northwind extends BaseDemo implements Serializable 
{
    String PresenterName, CompanyWebSite, ReportingMonth, ReportingYear,
        ReportingLocation, CompanyName, CompanyAddress, CompanyCity,
        CompanyState, CompanyZip, PicnicDate;
    Connection con = null;

    int daysInMonth[] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};

    String NameOfMonth[] = {
        "January", "February", "March", "April", "May",
        "June", "July", "August", "September", "October", "November", "December"};

    // Default Constructor
    public Northwind() {
        initLicense(new License());
    }
   
    private String _presenter = "John Doe";
    public String getPresenter() {
        return _presenter;
    }
    public void setPresenter(String v) {
        _presenter = v;
    }

    private String _companyweb = "www.aspose.com";
    public String getCompanyweb() {
        return _companyweb;
    }
    public void setCompanyweb(String v) {
        _companyweb = v;
    }

    private Boolean _chart3d = false;
    public Boolean getChart3d() {
        return _chart3d;
    }
    public void setChart3d(Boolean v) {
        _chart3d = v;
    }

    private String _employeeloc = "Kirkland";
    public String getEmployeeloc() {
        return _employeeloc;
    }
    public void setEmployeeloc(String v) {
        _employeeloc = v;
    }

    private String _companyname = "Aspose Pty.";
    public String getCompanyname() {
        return _companyname;
    }
    public void setCompanyname(String v) {
        _companyname = v;
    }

    private String _companyaddr = "126 First st.";
    public String getCompanyaddr() {
        return _companyaddr;
    }
    public void setCompanyaddr(String v) {
        _companyaddr = v;
    }

    private String _companycity = "Tyler";
    public String getCompanycity() {
        return _companycity;
    }
    public void setCompanycity(String v) {
        _companycity = v;
    }

    private String _companystate = "TX";
    public String getCompanystate() {
        return _companystate;
    }
    public void setCompanystate(String v) {
        _companystate = v;
    }

    private String _companyzip = "75704";
    public String getCompanyzip() {
        return _companyzip;
    }
    public void setCompanyzip(String v) {
        _companyzip = v;
    }

    private String _reportingyear = "1997";
    public String getReportingyear() {
        return _reportingyear;
    }
    public void setReportingyear(String v) {
        _reportingyear = v;
    }

    private String _reportingmonth = "1";
    public String getReportingmonth() {
        return _reportingmonth;
    }
    public void setReportingmonth(String v) {
        _reportingmonth = v;
    }

    /*
    private String _picknickdate = "mm/dd/yyyy";
    public String getPicknickdate() {
        return _picknickdate;
    }
    public void setPicknickdate(String d) {
        _picknickdate = d;
    }
    */
    private Date _picknickdate = new Date(2008, 1, 2);
    public Date getPicknickdate() {
        return _picknickdate;
    }
    public void setPicknickdate(Date d) {
        _picknickdate = d;
    }

    /*
    public void validateUsername(FacesContext facesContext, UIComponent uiComponent,
        Object value) throws ValidatorException 
    {
        // perform
        if (the validation fails) 
        {
            throw new ValidatorException(new FacesMessage("Invalid Picknick date"));
        }
    }
    */


    /**
     * Execute request
     *
     * @return success
     */
    public Boolean execute() {

        try
        {
            String path = "/resources/";

            context = FacesContext.getCurrentInstance();
            request = (HttpServletRequest) context.getExternalContext().getRequest();
            response = (HttpServletResponse) context.getExternalContext().getResponse();

            if (null==request) {
                System.err.println("REQUEST NULL??!");
            }

            // Get Path to save to updated file to
            Presentation pres = new Presentation(getFile(request, path + "demo_new.ppt"));

            // Open the database connection
            openDBConnection(getFileName(request, path + "Northwind.mdb"));

            // Declare Post Varaibles
            PresenterName = getPresenter();
            CompanyWebSite = getCompanyweb();
            ReportingMonth = getReportingmonth();
            ReportingYear = getReportingyear();
            ReportingLocation = getEmployeeloc();
            CompanyName = getCompanyname();
            CompanyAddress = getCompanyaddr();
            CompanyCity = getCompanycity();
            CompanyState = getCompanystate();
            CompanyZip = getCompanyzip();
            //PicnicDate = getPicknickdate();

            // Set SlideMaster Properties
            pres = setupSlideMaster(pres);

            // Get references to slides in presentation by position
            Slide slide1 = pres.getSlideByPosition(1);
            Slide slide2 = pres.getSlideByPosition(2);

            // Create a clone of slide2 and place it into position 3
            pres.cloneSlide(slide2, 3);
            Slide slide3 = pres.getSlideByPosition(3);
            Slide slide4 = pres.getSlideByPosition(4);
            Slide slide5 = pres.getSlideByPosition(5);
            Slide slide6 = pres.getSlideByPosition(6);
            Slide slide7 = pres.getSlideByPosition(7);
            Slide slide8 = pres.getSlideByPosition(8);

            // I have created methods for each slide to help seprate out the code

            slide1 = Slide1(slide1);
            slide2 = Slide2(slide2);
            slide3 = Slide3(slide3);
            slide4 = Slide4(slide4);
            slide5 = Slide5(slide5);
            slide6 = Slide6(slide6, pres);
            slide7 = Slide7(slide7);
            slide8 = Slide8(slide8, pres);
            pres.cloneSlide(slide1, 9);
            Slide slide9 = pres.getSlideByPosition(9);
            slide9 = Slide9(slide9);

            // Slide 7 is removed because it contains featues from the .NET version that the Java version does not support.
            pres.getSlides().remove(slide7);

            // Close the database connection
            closeDBConnection();

            // Save Presentation to a file
            //pres.write(destination);

            //destination.close();
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            //response.addHeader("Content-Disposition", "attachment; filename=demo_new.ppt");
            //pres.write(response.getOutputStream());
            pres.write(out);
            //context.responseComplete();
            setResponse("demo_new.ppt", out);
            return true;
        }catch(Exception ex)
        {
            ex.printStackTrace();
        }

        return false;
    }

    public void openDBConnection(String fileName) throws Exception {
        try {
            // Connect to the database
            //Class.forName("net.sourceforge.jtds.jdbc.Driver");
            //con = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/northwind", "northwind", "northwind");

            //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            //con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433/northwind;Integrated Security=SSPI");
            //con = DriverManager.getConnection("jdbc:mysql://localhost:3306/northwind", "root", "");

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + fileName.replace("\\", "/") + ";", "", "");
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
/*
    public void openDBConnection() throws Exception {
        try {
            // Connect to the database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection("jdbc:odbc:NorthWind", "", "");
        } catch (Exception e) {
            throw e;
        }
    }
*/
    public void closeDBConnection() throws Exception {
        try {
            // Close datebase connection
            con.close();
            con = null;
        } catch (Exception e) {
            throw e;
        }
    }

    public Presentation setupSlideMaster(Presentation pres) throws Exception {
        try {
            // Get slide master
            Slides slides = pres.getMasters();
            Slide slide = slides.get(0);
            // Set slide master propertires
            slide.getBackground().getFillFormat().setType(FillType.GRADIENT);
            slide.getBackground().getFillFormat().setGradientColorType(GradientColorType.TWO_COLORS);
            slide.getBackground().getFillFormat().setForeColor(java.awt.Color.decode("#f5f5dc"));
            slide.getBackground().getFillFormat().setBackColor(java.awt.Color.decode("#1E90FF"));
        } catch (Exception e) {
            throw e;
        }
        return pres;
    }

    public Slide Slide1(Slide slide1) throws Exception {
        // Introduction
        try {
            for (int j = 0; j < slide1.getShapes().size(); j++) {
                Shape shape = slide1.getShapes().get(j);
                if (shape.getTextFrame() != null) {
                    TextFrame tf = shape.getTextFrame();
                    Paragraph para = tf.getParagraphs().get(0);
                    if (para.getText().toString().startsWith("Presenter")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(false);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 30);
                        port.setText("Presented By: " + PresenterName);
                    } else if (para.getText().equals("HyperLink")) {
                        para.setAlignment(TextAlignment.LEFT);
                        Portion port = para.getPortions().get(0);
                        port.setFontHeight((short) 20);
                        port.setText(CompanyWebSite);
                        tf.getLinks().clear();
                        Link link = tf.getLinks().addLink();
                        link.setInternalHyperlink(slide1);
                        link.setBegin(0);
                        link.setEnd(tf.getText().length());
                    }
                }
            }

            // Create Shape to Hold Business Info
            Shape shape1 = slide1.getShapes().addRectangle(1550, 300, 2700, 1200);

            // Set Shape Properties
            shape1.getLineFormat().setWidth(3);
            shape1.getFillFormat().setType(FillType.TEXTURE);
            shape1.getFillFormat().setTextureStyle(TextureStyle.NEWSPRINT);

            // Add Transition Effects
            shape1.getAnimationSettings().setEntryEffect(ShapeEntryEffect.STRETCH_ACROSS);

            // Add A TextFrame to the Shape
            shape1.addTextFrame("Shape1");

            if (shape1.getTextFrame() != null) {
                // Add first paragraph to the shape
                TextFrame tfsh1 = shape1.getTextFrame();
                Paragraph parash1 = tfsh1.getParagraphs().get(0);
                parash1.setAlignment(TextAlignment.CENTER);
                Portion port = parash1.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.decode("#1E90FF"));
                port.setFontHeight((short) 30);
                port.setText(CompanyName);

                // Add second paragraph to the shape
                int phnum = tfsh1.getParagraphs().add(new Paragraph(tfsh1.getParagraphs().
                        get(0)));
                Paragraph para2sh1 = tfsh1.getParagraphs().get(phnum);
                Portion port2 = para2sh1.getPortions().get(0);
                port2.setFontBold(false);
                port2.setFontColor(java.awt.Color.decode("#1E90FF"));
                port2.setFontHeight((short) 30);
                port2.setText(CompanyAddress);

                // Add third paragraph to the shape
                phnum = tfsh1.getParagraphs().add(new Paragraph(tfsh1.getParagraphs().get(1)));
                Paragraph para3sh1 = tfsh1.getParagraphs().get(2);
                Portion port3 = para3sh1.getPortions().get(0);
                port3.setFontBold(false);
                port3.setFontColor(java.awt.Color.decode("#1E90FF"));
                port3.setFontHeight((short) 30);
                port3.setText(CompanyCity + ", " + CompanyState + "  " + CompanyZip);
            }

            // Add Slide Transition Effect
            slide1.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.FADE_SMOOTHLY);
        } catch (Exception e) {
            throw e;
        }
        return slide1;
    }

    public Slide Slide2(Slide slide2) throws Exception {
        // Employee Directory
        for (int j = 0; j < slide2.getShapes().size(); j++) {
            Shape shape = slide2.getShapes().get(j);
            if (shape.getTextFrame() != null) {
                TextFrame tf = shape.getTextFrame();
                if (tf.getParagraphs().size() != 0) {
                    Paragraph para = tf.getParagraphs().get(0);
                    if (para.getText().equals("Subtitle")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(false);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 30);
                        port.setText("Location: " + ReportingLocation);
                    } else if (para.getText().equals("CH1")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(true);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 20);
                        port.setText("Name");
                    } else if (para.getText().equals("CH2")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(true);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 20);
                        port.setText("Phone Number");
                    }
                }
            }
        }

        // Find table
        com.aspose.slides.Table table = null;
        for (int i = 0; i < slide2.getShapes().size(); i++) {
            if (slide2.getShapes().get(i) instanceof com.aspose.slides.Table) {
                table = (com.aspose.slides.Table) slide2.getShapes().get(i);
                break;
            }
        }
        if (table != null) {
            // Query Database
            String selectString =
                    "SELECT count(*) as NumberOfRows FROM Employees where City='" +
                    ReportingLocation + "'";
            // Create a new SQL statement object using the Conection Object
            Statement s = con.createStatement();
            // Create a Result Set using the Statement object and the select string defined above.

            ResultSet rs = s.executeQuery(selectString);

            int recordCount = 0;
            while (rs.next()) {
                recordCount = rs.getInt("NumberOfRows");
            }
            rs.close();

            /**/
            selectString =
                    //"SELECT top 7 LastName, FirstName,HomePhone FROM Employees where " +
                    //  LIMIT 0,7 for MySQL
                    "SELECT TOP 7 LastName, FirstName,HomePhone FROM Employees where " +
                    "City='" + ReportingLocation + "' order by LastName";

            // Create a new SQL statement object using the Conection Object
            s = con.createStatement();
            // Create a Result Set using the Statement object and the select string defined above.
            rs = s.executeQuery(selectString);

            // Make table contain correct number of rows
            if (recordCount == 1) {
                table.deleteRow(1);
            } else {
                for (int i = 2; i < recordCount; i++) {
                    table.addRow();
                }
            }
            int count = 0;

            while (rs.next()) {
                TextFrame tfName = table.getCell(0, count).getTextFrame();
                TextFrame tfPhoneNumber = table.getCell(1, count).getTextFrame();
                if (tfName != null) {
                    tfName.getParagraphs().get(0).getPortions().get(0).setText(rs.
                            getString("LastName") + ", " + rs.getString("FirstName"));
                    tfPhoneNumber.getParagraphs().get(0).getPortions().get(0).setText(rs.
                            getString("HomePhone"));
                    // Left justify first column
                    tfName.getParagraphs().get(0).setAlignment(TextAlignment.LEFT);
                    // Center justify second column
                    tfPhoneNumber.getParagraphs().get(0).setAlignment(TextAlignment.
                            CENTER);
                    // Disable default bullets
                    tfName.getParagraphs().get(0).setHasBullet(false);
                    tfPhoneNumber.getParagraphs().get(0).setHasBullet(false);
                    // Set Font Color
                    tfName.getParagraphs().get(0).getPortions().get(0).setFontColor(java.
                            awt.Color.decode("#DC143C"));
                    tfPhoneNumber.getParagraphs().get(0).getPortions().get(0).
                            setFontColor(java.awt.Color.decode("#3CB371"));
                }
                count++;
            }
            rs.close();
        }
        // Add entry effect to the table
        table.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SPIRAL);
        // Add Slide Transition Effect
        slide2.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.CIRCLE_OUT);
        return slide2;
    }

    protected String getYearSQL(int Year) {
        //return "'" + Year + "0101' ";
        return "#" + Year + "-01-01# ";
    }

    protected String getDateSQL(Integer Year, Integer Month, Integer Day, boolean to) {
        java.text.DecimalFormat nft = new java.text.DecimalFormat("00");
        //return "'" + Year.toString() + nft.format(Integer.parseInt(ReportingMonth)) + nft.format(Day) + "'";
        return "#" + Year.toString() 
            + "-" + nft.format(Integer.parseInt(ReportingMonth)) 
            + "-" + nft.format(Day) 
            + "#";
    }

    public Slide Slide3(Slide slide3) throws Exception {
        // Top 5 Employees for the year
        for (int j = 0; j < slide3.getShapes().size(); j++) {
            Placeholder holder = slide3.getPlaceholders().get(j);
            if (holder instanceof TextHolder) {
                Paragraph para = ((TextHolder) holder).getParagraphs().get(0);
                if (para.getText().equals("Employee Directory")) {
                    para.setAlignment(TextAlignment.DEFAULT);
                    para.setHasBullet(true); // -1 is a default value for a bullet
                    Portion port = para.getPortions().get(0);
                    port.setFontColor(java.awt.Color.BLACK);
                    port.setText("Top 5 Employees for " + ReportingYear);
                }
            }
            Shape shape = slide3.getShapes().get(j);
            if (shape.getTextFrame() != null) {
                TextFrame tf = shape.getTextFrame();
                if (tf.getParagraphs().size() != 0) {
                    Paragraph para = tf.getParagraphs().get(0);
                    if (para.getText().equals("Subtitle")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 30);
                        port.setText("Ranked by Order Volume");

                    } else if (para.getText().equals("CH1")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(true);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 20);
                        port.setText("Name");
                    } else if (para.getText().equals("CH2")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(true);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 20);
                        port.setText("Order Volume");
                    }
                }
            }
        }

        // Find table
        com.aspose.slides.Table table = null;

        for (int i = 0; i < slide3.getShapes().size(); i++) {
            if (slide3.getShapes().get(i) instanceof com.aspose.slides.Table) {
                table = (com.aspose.slides.Table) slide3.getShapes().get(i);
                break;
            }
        }
        if (table != null) {
            try {
                // Quary Database
                Integer NextYear = 1 + Integer.parseInt(ReportingYear);
                String selectString =
                        "SELECT TOP 5 LastName, FirstName, count(Orders.OrderID) as numOrders " +
                        "FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID " +
                        "WHERE  Orders.OrderDate >" + getYearSQL(Integer.parseInt(ReportingYear)) +
                        "and Orders.OrderDate <" + getYearSQL(NextYear) +
                        "Group by LastName, FirstName " +
                        "order by count(Orders.OrderID) desc" 
                        // + " limit 0,5"
                        ;

                // Create a new SQL statement object using the Conection Object
                Statement s = con.createStatement();
                // Create a Result Set using the Statement object and the select string defined above.
                ResultSet rs = s.executeQuery(selectString);

                // Make table contain correct number of rows
                for (int i = 2; i < 5; i++) {
                    table.addRow();
                }

                int count = 0;
                while (rs.next()) {
                    TextFrame tfName = table.getCell(0, count).getTextFrame();
                    TextFrame tfPhoneNumber = table.getCell(1, count).getTextFrame();
                    if (tfName != null) {
                        tfName.getParagraphs().get(0).getPortions().get(0).setText(rs.
                                getString("LastName") + ", " + rs.getString("FirstName"));
                        tfPhoneNumber.getParagraphs().get(0).getPortions().get(0).setText(rs.getString("numOrders"));
                        // Left justify first column
                        tfName.getParagraphs().get(0).setAlignment(TextAlignment.LEFT);
                        // Center justify second column
                        tfPhoneNumber.getParagraphs().get(0).setAlignment(TextAlignment.
                                CENTER);
                        // Disable default bullets
                        tfName.getParagraphs().get(0).setHasBullet(false);
                        tfPhoneNumber.getParagraphs().get(0).setHasBullet(false);
                        // Set Font Color
                        tfName.getParagraphs().get(0).getPortions().get(0).setFontColor(java.awt.Color.decode("#DC143C"));
                        tfPhoneNumber.getParagraphs().get(0).getPortions().get(0).
                                setFontColor(java.awt.Color.decode("#3CB371"));
                    }
                    count++;
                }
                rs.close();

                // Add entry effect to the table
                table.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SPIRAL);
            } catch (Exception e) {
                throw e;
            }
        }
        // Add Slide Transition Effect
        slide3.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.WIPE_RIGHT);
        return slide3;
    }

    public Slide Slide4(Slide slide4) throws Exception {
        // Employee of the Year
        try {
            // Quary Database
            String selectString = "SELECT count(*) as NumberOfRows FROM Employees";
            // Create a new SQL statement object using the Conection Object
            Statement s = con.createStatement();
            // Create a Result Set using the Statement object and the select string defined above.
            ResultSet rs = s.executeQuery(selectString);

            int recordCount = 0;
            while (rs.next()) {
                recordCount = rs.getInt("NumberOfRows");
            }
            rs.close();
            // Quary Database
            selectString = "SELECT LastName, FirstName, Title, City FROM Employees";

            // Create a new SQL statement object using the Conection Object
            s = con.createStatement();
            // Create a Result Set using the Statement object and the select string defined above.
            rs = s.executeQuery(selectString);

            String LastName = "", FirstName = "", Title = "", City = "";
            Random objRand = new Random();

            // Get a random employee
            while (City == "") {
                int intRand = (int) objRand.nextInt(recordCount);
                for (int i = 0; i < intRand; i++) {
                    rs.next();
                    LastName = rs.getString("LastName");
                    FirstName = rs.getString("FirstName");
                    Title = rs.getString("Title");
                    City = rs.getString("City");
                }
            }
            rs.close();

            for (int j = 0; j < slide4.getShapes().size(); j++) {
                Shape shape = slide4.getShapes().get(j);
                if (shape.getTextFrame() != null) {
                    TextFrame tf = shape.getTextFrame();
                    if (tf.getParagraphs().size() != 0) {
                        Paragraph para = tf.getParagraphs().get(0);
                        if (para.getText().equals("Name")) {
                            para.setAlignment(TextAlignment.CENTER);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(true);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 40);
                            port.setText(FirstName + " " + LastName);

                            // Add Entry Effect for the Shape
                            shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.FLY_FROM_LEFT);
                        } else if (para.getText().equals("Title")) {
                            para.setAlignment(TextAlignment.CENTER);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(false);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 30);
                            port.setText(Title);

                            // Add Entry Effect for the Shape
                            shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.FLY_FROM_BOTTOM_RIGHT);
                        } else if (para.getText().equals("Location")) {
                            para.setAlignment(TextAlignment.CENTER);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(false);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 30);
                            port.setText(City);

                            // Add Entry Effect for the Shape
                            shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.FLY_FROM_BOTTOM_LEFT);
                        }
                    }
                }
            }
            // Add Slide Transition Effect
            slide4.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.DISSOLVE);
        } catch (Exception e) {
            throw e;
        }
        return slide4;
    }

    public Slide Slide5(Slide slide5) throws Exception {
        // Item of the month

        // Quary Database
        String selectString =
                "SELECT TOP 1 ProductName, count([Order Details].ProductID) as numOrders " +
                "FROM " +
                "Products INNER JOIN (Orders INNER JOIN [Order Details] " +
                "ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID " +
                "WHERE " +
                "Orders.OrderDate >" + 
                    getDateSQL(Integer.parseInt(ReportingYear), 
                        Integer.parseInt(ReportingMonth), 1, false)
                    + " AND Orders.OrderDate<" + 
                    getDateSQL(Integer.parseInt(ReportingYear), 
                        Integer.parseInt(ReportingMonth),
                        daysInMonth[java.lang.Integer.parseInt(ReportingMonth) - 1], true) +
                " and [Order Details].OrderID = Orders.OrderID " +
                "Group by " +
                "ProductName " +
                "order by count([Order Details].ProductID) desc" 
                // +" limit 0,1"
                ;

        // Create a new SQL statement object using the Conection Object
        Statement s = con.createStatement();
        // Create a Result Set using the Statement object and the select string defined above.
        ResultSet rs = s.executeQuery(selectString);

        String ProductName = "", NumberOfOrders = "";

        while (rs.next()) {
            ProductName = rs.getString("ProductName");
            NumberOfOrders = rs.getString("numOrders");
        }
        rs.close();

        for (int j = 0; j < slide5.getShapes().size(); j++) {
            Shape shape = slide5.getShapes().get(j);
            if (shape.getTextFrame() != null) {
                TextFrame tf = shape.getTextFrame();
                if (tf.getParagraphs().size() != 0) {
                    Paragraph para = tf.getParagraphs().get(0);
                    if (para.getText().equals("Subtitle")) {
                        para.setAlignment(TextAlignment.CENTER);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(true);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 30);
                        port.setText("Top Selling Item by Volume for " +
                                NameOfMonth[java.lang.Integer.parseInt(ReportingMonth) -
                                1] + " " + ReportingYear);
                    } else if (para.getText().equals("Body1")) {
                        para.setAlignment(TextAlignment.LEFT);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(false);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 25);

                        if (ProductName != "") {
                            port.setText("Item Name: " + ProductName);
                        } else {
                            port.setFontColor(java.awt.Color.RED);
                            port.setText("No Sales Reported for this Time Period.");
                            para.setAlignment(TextAlignment.CENTER);
                        }
                        // Add Entry Effect for the Shape
                        shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SPLIT_HORIZONTAL_OUT);
                    } else if (para.getText().equals("Body2")) {
                        para.setAlignment(TextAlignment.LEFT);
                        Portion port = para.getPortions().get(0);
                        port.setFontBold(false);
                        port.setFontColor(java.awt.Color.BLACK);
                        port.setFontHeight((short) 25);

                        if (NumberOfOrders != "") {
                            port.setText("Number of Sales for the Month: " + NumberOfOrders);
                        } else {
                            port.setText("");
                        }
                        // Add Entry Effect for the Shape
                        shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SPLIT_HORIZONTAL_IN);
                    }
                }
            }
        }
        // Add Slide Transition Effect
        slide5.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.DIAMOND_OUT);
        return slide5;
    }

    public Slide Slide6(Slide slide6, Presentation pres) throws Exception {
        // Item of the Year
        try {
            // Quary Database
            Integer NextYear = 1 + Integer.parseInt(ReportingYear);
            String selectString =
                    "SELECT TOP 1 ProductName, count([Order Details].ProductID) as numOrders " +
                    "FROM " +
                    "Products INNER JOIN (Orders INNER JOIN [Order Details] " +
                    "ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID " +
                    "WHERE " +
                    "Orders.OrderDate >" 
                        + getYearSQL(Integer.parseInt(ReportingYear))
                        + " and Orders.OrderDate < " + getYearSQL(NextYear) +
                    "and [Order Details].OrderID = Orders.OrderID " +
                    "Group by " +
                    "ProductName " +
                    "order by count([Order Details].ProductID) desc" 
                    //+ " limit 0,1"
                    ;

            // Create a new SQL statement object using the Conection Object
            Statement s = con.createStatement();
            // Create a Result Set using the Statement object and the select string defined above.
            ResultSet rs = s.executeQuery(selectString);

            String ProductName = "", NumberOfOrders = "";

            while (rs.next()) {
                ProductName = rs.getString("ProductName");
                NumberOfOrders = rs.getString("numOrders");
            }
            rs.close();

            for (int j = 0; j < slide6.getShapes().size(); j++) {
                Shape shape = slide6.getShapes().get(j);
                if (shape.getTextFrame() != null) {
                    TextFrame tf = shape.getTextFrame();
                    if (tf.getParagraphs().size() != 0) {
                        Paragraph para = tf.getParagraphs().get(0);
                        if (para.getText().equals("Subtitle")) {
                            para.setAlignment(TextAlignment.CENTER);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(true);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 30);
                            port.setText("Top Selling Item by Volume for " +
                                    ReportingYear);
                        } else if (para.getText().equals("Body1")) {
                            para.setAlignment(TextAlignment.LEFT);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(false);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 25);

                            if (ProductName != "") {
                                port.setText("Item Name: " + ProductName);
                            }

                            // Add Entry Effect for the Shape
                            shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SWIVEL);
                        } else if (para.getText().equals("Body2")) {
                            para.setAlignment(TextAlignment.LEFT);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(false);
                            port.setFontColor(java.awt.Color.BLACK);
                            port.setFontHeight((short) 25);

                            if (NumberOfOrders != "") {
                                port.setText("Number of Sales for the Year: " + NumberOfOrders);
                            }
                            // Add Entry Effect for the Shape
                            shape.getAnimationSettings().setEntryEffect(ShapeEntryEffect.WIPE_UP);
                        }
                    }
                }
            }

            // Add picture of item
            com.aspose.slides.Picture pic1 = null;
            String imgFileName = "";
            if ("Gnocchi di nonna Alice".equals(ProductName)) {
                InputStream is = getFile(request, "/resources/Gnocchi.jpg");
                if (null!=is) {
                    pic1 = new com.aspose.slides.Picture(pres, is);
                    imgFileName = getFileName(request, "/resources/Gnocchi.jpg");
                }else {
                    System.err.println("Could not load /resources/Gnocchi.jpg from " + request);
                }
            } else if ("Konbu".equals(ProductName)) {
                InputStream is = getFile(request, "/resources/konbu.jpg");
                if (null!=is) {
                    pic1 = new com.aspose.slides.Picture(pres, is);
                    imgFileName = getFileName(request, "/resources/konbu.jpg");
                }else {
                    System.err.println("Could not load /resources/konbu.jpg from " + request);
                }
            } else if ("Gorgonzola Telino".equals(ProductName)) {
                InputStream is = getFile(request, "/resources/Gorgonzola.gif");
                if (null!=is) {
                    pic1 = new com.aspose.slides.Picture(pres, is);
                    imgFileName = getFileName(request, "/resources/Gorgonzola.jpg");
                }else {
                    System.err.println("Could not load /resources/Gorgonzola.jpg from " + request);
                }
            }

            if (null!=pic1)
            {
                int picid1 = pres.getPictures().add(pic1);
                RenderedOp img = JAI.create("fileload", imgFileName);
                int pictureWidth = img.getWidth() * 5;
                int pictureHeight = img.getHeight() * 5;
                int slideWidth = slide6.getBackground().getWidth();
                int slideHeight = slide6.getBackground().getHeight();
                int pictureFrameWidth = (slideWidth / 2 - pictureWidth / 2);
                int pictureFrameHeight = (slideHeight / 2 - pictureHeight / 2) + 1000;
                PictureFrame pf = slide6.getShapes().addPictureFrame(picid1, pictureFrameWidth, pictureFrameHeight,
                    pictureWidth, pictureHeight);

                //PictureFrame pf = slide6.getShapes().addPictureFrame(picid1, 1, 1, 1000, 1000);
                pf.getAnimationSettings().setEntryEffect(ShapeEntryEffect.ZOOM_IN);
            }

            // Add Slide Transition Effect
            slide6.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.CIRCLE_OUT);
        } catch (Exception e) {
            throw e;
        }
        return slide6;
    }

    public Slide Slide7(Slide slide7) {
        // Not used in Java Demo
        return slide7;
    }

    public Slide Slide8(Slide slide8, Presentation pres) throws Exception {
        // Company Picnic Promotion
        try {
            slide8.setFollowMasterBackground(false);
            slide8.getBackground().getFillFormat().setType(FillType.PICTURE);

            InputStream is = getFile(request, "/resources/picnic.jpg");
            if (null!=is) {
                com.aspose.slides.Picture pic1 = new com.aspose.slides.Picture(pres, is);
                int picid1 = pres.getPictures().add(pic1);
                slide8.getBackground().getFillFormat().setPictureId(picid1);
            }

            // Set Picnic Date
/*
            String month = "", day = "", year = "";

            if (!PicnicDate.equals("mm/dd/yyyy")) {
                try {
                    String[] pdate = PicnicDate.split("/");
                    if (Integer.parseInt(pdate[0]) < 13 && Integer.parseInt(pdate[0]) > 0) {
                        month = NameOfMonth[Integer.parseInt(pdate[0]) - 1];
                    }
                    if (Integer.parseInt(pdate[1]) < 32 && Integer.parseInt(pdate[1]) > 0) {
                        day = pdate[1];
                    }
                    if (Integer.parseInt(pdate[2]) < 2100 &&
                            Integer.parseInt(pdate[2]) > 1900) {
                        year = pdate[2];
                    }
                } catch (Exception e) {
                    month = day = year = "";
                }
            }
*/
            Calendar calendar = new GregorianCalendar();
            calendar.setTime(getPicknickdate());
            String month = NameOfMonth[calendar.get(Calendar.MONTH)-1];
            String day = new Integer(calendar.get(Calendar.DAY_OF_MONTH)).toString();
            String year = new Integer(calendar.get(Calendar.YEAR)).toString();
            //Date calendar = getPicknickdate();
            //String month = NameOfMonth[calendar.getMonth()-1];
            //String day = new Integer(calendar.getDate()).toString();
            //String year = new Integer(calendar.getYear()).toString();

            String date_text;
            if (month.equals("") || day.equals("") || year.equals("")) {
                date_text = new SimpleDateFormat("MMMM dd, yyyy").format(new Date());
            } else {
                date_text = month + " " + day + ", " + year;
            }
            for (int k = 0; k < slide8.getPlaceholders().size(); k++) {
                Placeholder holder = slide8.getPlaceholders().get(k);
                if (holder instanceof TextHolder) {
                    Paragraph para = ((TextHolder) holder).getParagraphs().get(0);
                    if (para.getText().equals("Company Picnic Announcement")) {
                        Portion port = para.getPortions().get(0);
                        port.setFontColor(java.awt.Color.RED);
                    }
                }
            }

            for (int j = 0; j < slide8.getShapes().size(); j++) {
                Shape shape = slide8.getShapes().get(j);
                if (shape.getTextFrame() != null) {
                    TextFrame tf = shape.getTextFrame();
                    if (tf.getParagraphs().size() != 0) {
                        Paragraph para = tf.getParagraphs().get(0);
                        if (para.getText().equals("Subtitle")) {
                            // Add first paragraph to the shape
                            para.setAlignment(TextAlignment.CENTER);
                            Portion port = para.getPortions().get(0);
                            port.setFontBold(true);
                            port.setFontColor(java.awt.Color.RED);
                            port.setFontHeight((short) 25);
                            port.setText("The Company Picnic will be held ");

                            // Add second paragraph to the shape
                            tf.getParagraphs().add(new Paragraph(tf.getParagraphs().get(0)));
                            Paragraph para2 = tf.getParagraphs().get(1);
                            Portion port2 = para2.getPortions().get(0);
                            port2.setFontBold(true);
                            port2.setFontColor(java.awt.Color.RED);
                            port2.setFontHeight((short) 25);
                            port2.setText(date_text + " at South Lake Shore Park");
                        }
                    }
                }
            }

            // Create 3 Shapes to hold text
            Shape shape1 = slide8.getShapes().addRectangle(400, 1400, 2300, 800);
            shape1.getFillFormat().setType(FillType.TEXTURE);
            shape1.getFillFormat().setTextureStyle(TextureStyle.PURPLE_MESH);

            Shape shape2 = slide8.getShapes().addRectangle(3100, 2200, 2300, 800);
            shape2.getFillFormat().setType(FillType.GRADIENT);
            shape2.getFillFormat().setGradientColorType(GradientColorType.TWO_COLORS);
            shape2.getFillFormat().setGradientFillAngle(40);
            shape2.getFillFormat().setForeColor(java.awt.Color.decode("#b0c4de"));
            shape2.getFillFormat().setBackColor(java.awt.Color.decode("#90ee90"));

            Shape shape3 = slide8.getShapes().addRectangle(400, 3000, 2300, 800);
            shape3.getFillFormat().setType(FillType.SOLID);
            shape3.getFillFormat().setForeColor(java.awt.Color.yellow);

            // Add a picture of a hotdog
            InputStream isHD = getFile(request, "/resources/hotdog.gif");
            String fileHD = getFileName(request, "/resources/hotdog.gif");
            if (null!=isHD) {
                Picture pic1HD = new com.aspose.slides.Picture(pres, isHD);
                int picid1HD = pres.getPictures().add(pic1HD);
                RenderedOp img2 = JAI.create("fileload", fileHD);
                int pictureWidth = img2.getWidth() * 12;
                int pictureHeight = img2.getHeight() * 12;
                int slideWidth = slide8.getBackground().getWidth();
                int slideHeight = slide8.getBackground().getHeight();
                PictureFrame pf1 = slide8.getShapes().addPictureFrame(picid1HD, 3400, 3200, pictureWidth, pictureHeight);
                pf1.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SPIRAL);
                pf1.getAnimationSettings().setAnimationOrder(5);
            }

            shape1.addTextFrame("Shape1");

            // Add text to the shapes
            if (shape1.getTextFrame() != null) {
                TextFrame tf = shape1.getTextFrame();
                Paragraph para = tf.getParagraphs().get(0);
                para.setAlignment(TextAlignment.CENTER);
                Portion port = para.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.green);
                port.setFontHeight((short) 20);
                port.setText("Fun for the Whole Family!");
            }

            shape2.addTextFrame("Shape2");

            if (shape2.getTextFrame() != null) {
                TextFrame tf = shape2.getTextFrame();
                Paragraph para = tf.getParagraphs().get(0);
                para.setAlignment(TextAlignment.CENTER);
                Portion port = para.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.decode("#1E90FF"));
                port.setFontHeight((short) 20);
                port.setFontShadow(true);
                port.setText("Live Entertainment!");
            }

            shape3.addTextFrame("Shape3");

            if (shape3.getTextFrame() != null) {
                TextFrame tf = shape3.getTextFrame();
                Paragraph para = tf.getParagraphs().get(0);
                para.setAlignment(TextAlignment.CENTER);
                Portion port = para.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.decode("#800080"));
                port.setFontHeight((short) 20);
                port.setFontShadow(true);
                port.setText("Catered");

                // Add second paragraph to the shape
                tf.getParagraphs().add(new Paragraph(tf.getParagraphs().get(0)));
                Paragraph para2 = tf.getParagraphs().get(1);
                port = para2.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.decode("#800080"));
                port.setFontHeight((short) 20);
                port.setFontShadow(true);
                port.setText("by");

                // Add third paragraph to the shape
                tf.getParagraphs().add(new Paragraph(tf.getParagraphs().get(0)));
                Paragraph para3 = tf.getParagraphs().get(2);
                port = para3.getPortions().get(0);
                port.setFontBold(false);
                port.setFontColor(java.awt.Color.decode("#800080"));
                port.setFontHeight((short) 20);
                port.setFontShadow(true);
                port.setText("Ed's Catfish Saloon!");
            }

            // Set Entry Effects
            slide8.getBackground().getAnimationSettings().setEntryEffect(ShapeEntryEffect.STRETCH_ACROSS);
            slide8.getBackground().getAnimationSettings().setAnimationOrder(1);
            shape1.getAnimationSettings().setEntryEffect(ShapeEntryEffect.SWIVEL);
            shape1.getAnimationSettings().setAnimationOrder(2);
            shape2.getAnimationSettings().setEntryEffect(ShapeEntryEffect.WEDGE);
            shape2.getAnimationSettings().setAnimationOrder(3);
            shape3.getAnimationSettings().setEntryEffect(ShapeEntryEffect.DISSOLVE);
            shape3.getAnimationSettings().setAnimationOrder(4);
            // Add Slide Transition Effect
            slide8.getSlideShowTransition().setEntryEffect(SlideTransitionEffect.WEDGE);
        } catch (Exception e) {
            throw e;
        }
        return slide8;
    }

    public Slide Slide9(Slide slide9) {
        // Closeing Slide
        // Clone of first slide
        return slide9;
    }

    /**
     *
     * Initializes license (if demo license is available).
     * you may insert your licensing code here that would use obj's
     * setLicense based on InputStream rather than passing it on
     * to 3rd-party class.
     *
     */
    public static void initLicense(Object obj) {
        try {

            Class myclass = Class.forName("com.aspose.demos.Common");
            if (null==myclass) {
                System.out.println("initLicense not found");
                return ;
            }

            //Use reflection to list methods and invoke them
            Method[] methods = myclass.getMethods();
            Object object = myclass.newInstance();

            Class partypes[] = new Class[1];
            partypes[0] = Object.class;
           
            Method meth = myclass.getMethod("initLicense", partypes);
            if (null!=meth) {

                    System.out.println("Calling " + meth.getName());
                    Object arglist[] = new Object[1];
                    arglist[0] = obj;

                    meth.invoke(object, arglist);
            }
        } catch (ClassNotFoundException cnfe) {
            // Ignore
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}