org.fao.fenix.wds.web.rest.faostat.FAOSTATDownloadTable.java Source code

Java tutorial

Introduction

Here is the source code for org.fao.fenix.wds.web.rest.faostat.FAOSTATDownloadTable.java

Source

/**
 *
 * FENIX (Food security and Early warning Network and Information Exchange)
 *
 * Copyright (c) 2011, by FAO of UN under the EC-FAO Food Security
 Information for Action Programme
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 *
 */
package org.fao.fenix.wds.web.rest.faostat;

import com.google.gson.Gson;
import com.google.gson.JsonSyntaxException;
import com.mongodb.*;
import com.mongodb.util.JSON;
import org.fao.fenix.wds.core.bean.*;
import org.fao.fenix.wds.core.constant.DATASOURCE;
import org.fao.fenix.wds.core.constant.SQL;
import org.fao.fenix.wds.core.datasource.DatasourcePool;
import org.fao.fenix.wds.core.exception.WDSException;
import org.fao.fenix.wds.core.exception.WDSExceptionStreamWriter;
import org.fao.fenix.wds.core.jdbc.JDBCConnector;
import org.fao.fenix.wds.core.jdbc.JDBCIterable;
import org.fao.fenix.wds.core.jdbc.MongoDBConnectionManager;
import org.fao.fenix.wds.core.sql.Bean2SQL;
import org.fao.fenix.wds.core.utils.Wrapper;
import org.fao.fenix.wds.core.utils.WrapperConfigurations;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.ws.rs.*;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.StreamingOutput;
import java.io.*;
import java.net.UnknownHostException;
import java.sql.SQLException;
import java.util.*;

/**
 * @author <a href="mailto:guido.barbaglia@fao.org">Guido Barbaglia</a>
 * @author <a href="mailto:guido.barbaglia@gmail.com">Guido Barbaglia</a>
 */
@Component
@Path("/table")
public class FAOSTATDownloadTable {

    @Autowired
    private Wrapper wrapper;

    @Autowired
    private DatasourcePool datasourcePool;

    private String SCHEMA = "faostat3";

    private Gson g = new Gson();

    @POST
    @Path("/excelWithQuotes")
    public Response createExcelWithQuotes(@FormParam("datasource_WQ") String datasource,
            @FormParam("json_WQ") String json, @FormParam("cssFilename_WQ") String cssFilename,
            @FormParam("valueIndex_WQ") String valueIndex,
            @FormParam("thousandSeparator_WQ") String thousandSeparator,
            @FormParam("decimalSeparator_WQ") String decimalSeparator,
            @FormParam("decimalNumbers_WQ") String decimalNumbers, @FormParam("quote_WQ") String quote,
            @FormParam("title_WQ") String title, @FormParam("subtitle_WQ") String subtitle) {

        try {

            // Log the request
            save(new Date(), "/table/excelWithQuotes", json);

            // compute result
            //            Gson g = new Gson();
            DATASOURCE ds = DATASOURCE.valueOf(datasource.toUpperCase());
            SQLBean sql = g.fromJson(json, SQLBean.class);
            DBBean db = new DBBean(ds);

            // alter the query to switch from LIMIT to TOP
            if (datasource.toUpperCase().startsWith("FAOSTAT"))
                sql.setQuery(replaceLimitWithTop(sql));

            // query the DB
            List<List<String>> table = JDBCConnector.query(db, sql, true);

            List<String> headers = new ArrayList<String>();
            for (int i = 0; i < sql.getSelects().size(); i++)
                headers.add(sql.getSelects().get(i).getAlias());
            table.add(0, headers);

            // Add quote
            List<String> tmp = new ArrayList<String>();
            tmp.add("Downloaded from FAOSTAT");
            table.add(0, tmp);
            if (title != null && title.length() > 0) {
                tmp = new ArrayList<String>();
                tmp.add(title);
                table.add(1, tmp);
            }
            tmp = new ArrayList<String>();
            tmp.add("");
            if (title != null && title.length() > 0)
                table.add(2, tmp);
            else
                table.add(1, tmp);

            // configure output
            WrapperConfigurations wc = new WrapperConfigurations();
            wc.setCssName(cssFilename);
            wc.setDecimalNumbers(Integer.valueOf(decimalNumbers));
            wc.setDecimalSeparator(decimalSeparator);
            wc.setThousandSeparator(thousandSeparator);
            try {
                wc.setValueColumnIndex(Integer.valueOf(valueIndex));
            } catch (Exception e) {

            }

            // create HTML
            StringBuilder excel = wrapper.wrapAsExcel(table, UUID.randomUUID().toString() + ".xls");

            /* Stream result */
            return Response.status(200).entity(excel.toString()).build();

        } catch (WDSException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        } catch (SQLException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        } catch (InstantiationException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        } catch (UnknownHostException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excelWithQuotes' service: " + e.getMessage()).build();
        }

    }

    /**
     * @param datasource e.g. FAOSTAT
     * @param json       Parameters to build SQL query
     * @return HTML formatted <code>String</code>
     * <p/>
     * Produces a HTML table.
     */
    @POST
    @Produces(MediaType.TEXT_HTML)
    @Path("/html")
    public Response createTable(@FormParam("datasource") String datasource, @FormParam("json") String json,
            @FormParam("cssFilename") String cssFilename, @FormParam("valueIndex") String valueIndex,
            @FormParam("thousandSeparator") String thousandSeparator,
            @FormParam("decimalSeparator") String decimalSeparator,
            @FormParam("decimalNumbers") String decimalNumbers) {

        try {

            // Log the request
            save(new Date(), "/table/html", json);

            // compute result
            //         Gson g = new Gson();
            DATASOURCE ds = DATASOURCE.valueOf(datasource.toUpperCase());
            SQLBean sql = g.fromJson(json, SQLBean.class);
            List<String> headers = new ArrayList<String>();

            // alter the query to switch from LIMIT to TOP
            if (sql.getLimit() != null && sql.getLimit().length() > 0) {
                for (SelectBean sel : sql.getSelects())
                    headers.add(sel.getAlias().replaceAll("_", " "));
                String script = Bean2SQL.convert(sql).toString();
                script = script.replaceAll("SELECT ", "SELECT TOP " + sql.getLimit() + " ");
                int idx = script.indexOf("LIMIT");
                if (idx > -1)
                    script = script.substring(0, idx);
                sql.setQuery(script);
            }

            //            System.out.println(sql.getQuery());

            // compute result
            DBBean db = new DBBean(ds);
            List<List<String>> table = JDBCConnector.query(db, sql, true);

            // add the headers
            if (sql.getLimit() != null && sql.getLimit().length() > 0) {
                table.add(0, headers);
            }

            // configure output
            WrapperConfigurations wc = new WrapperConfigurations();
            wc.setCssName(cssFilename);
            wc.setDecimalNumbers(Integer.valueOf(decimalNumbers));
            wc.setDecimalSeparator(decimalSeparator);
            wc.setThousandSeparator(thousandSeparator);

            try {
                wc.setValueColumnIndex(Integer.valueOf(valueIndex));
            } catch (Exception ignored) {

            }

            // create HTML
            StringBuilder html = wrapper.wrapAsHTML4FAOSTAT(table, true, wc);

            /* Stream result */
            return Response.status(200).entity(html.toString()).build();

        } catch (WDSException e) {
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        } catch (ClassNotFoundException e) {
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        } catch (SQLException e) {
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        } catch (InstantiationException e) {
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        } catch (IllegalAccessException e) {
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        } catch (UnknownHostException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html' service: " + e.getMessage()).build();
        }

    }

    /**
     * @param datasource e.g. FAOSTAT
     * @param json       Parameters to build SQL query
     * @return HTML formatted <code>String</code>
     * <p/>
     * Produces a HTML table.
     */
    @POST
    @Produces(MediaType.TEXT_HTML)
    @Path("/html2")
    public Response createTable2(@FormParam("datasource") String datasource, @FormParam("json") String json,
            @FormParam("cssFilename") String cssFilename, @FormParam("valuesIndex") String valuesIndex,
            @FormParam("thousandSeparator") String thousandSeparator,
            @FormParam("decimalSeparator") String decimalSeparator,
            @FormParam("decimalNumbers") String decimalNumbers, @FormParam("nowrap") Boolean nowrap,
            @FormParam("addHeaders") Boolean addHeaders) {

        try {

            // Log the request
            save(new Date(), "/table/html2", json);

            // compute result
            //         Gson g = new Gson();
            DATASOURCE ds = DATASOURCE.valueOf(datasource.toUpperCase());
            SQLBean sql = g.fromJson(json, SQLBean.class);
            List<String> headers = new ArrayList<String>();

            if (addHeaders != null)
                sql.setAddHeaders(addHeaders);

            if (sql.isAddHeaders()) {
                for (SelectBean sel : sql.getSelects()) {
                    headers.add(sel.getAlias().replaceAll("_", " "));
                }
            }

            // alter the query to switch from LIMIT to TOP
            if (datasource.toUpperCase().startsWith("FAOSTAT"))
                sql.setQuery(replaceLimitWithTop(sql));

            boolean isNoWrap = true;
            if (nowrap != null) {
                isNoWrap = nowrap;
            }

            // compute result
            DBBean db = new DBBean(ds);
            List<List<String>> table = JDBCConnector.query(db, sql, isNoWrap);

            // add the headers
            if (sql.isAddHeaders()) {
                table.add(0, headers);
            }

            // configure output
            WrapperConfigurations wc = new WrapperConfigurations();
            wc.setCssName(cssFilename);
            wc.setDecimalNumbers(Integer.valueOf(decimalNumbers));
            wc.setDecimalSeparator(decimalSeparator);
            wc.setThousandSeparator(thousandSeparator);

            if (valuesIndex != null) {
                // Parsing the String (i.e. 2,4,6,8);
                List<Integer> indexes = new ArrayList<Integer>();
                try {
                    String[] tokens = valuesIndex.split(",");
                    for (String token : tokens) {
                        indexes.add(Integer.valueOf(token));
                    }
                    wc.setValuesColumnIndex(indexes);
                } catch (Exception ignored) {

                }
            }

            // create HTML
            StringBuilder html = wrapper.wrapAsHTML4FAOSTAT2(table, isNoWrap, wc);

            /* Stream result */
            return Response.status(200).entity(html.toString()).build();

        } catch (WDSException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        } catch (SQLException e) {
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        } catch (InstantiationException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        } catch (UnknownHostException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/html2' service: " + e.getMessage()).build();
        }

    }

    @POST
    @Path("/excel")
    public Response createExcel(@FormParam("datasource") String datasource, @FormParam("json") String json,
            @FormParam("cssFilename") String cssFilename, @FormParam("valueIndex") String valueIndex,
            @FormParam("thousandSeparator") String thousandSeparator,
            @FormParam("decimalSeparator") String decimalSeparator,
            @FormParam("decimalNumbers") String decimalNumbers) {

        try {

            // Log the request
            save(new Date(), "/table/excel", json);

            // compute result
            //            Gson g = new Gson();
            DATASOURCE ds = DATASOURCE.valueOf(datasource.toUpperCase());
            SQLBean sql = g.fromJson(json, SQLBean.class);
            DBBean db = new DBBean(ds);

            // alter the query to switch from LIMIT to TOP
            if (datasource.toUpperCase().startsWith("FAOSTAT"))
                sql.setQuery(replaceLimitWithTop(sql));

            // query the DB
            List<List<String>> table = JDBCConnector.query(db, sql, true);

            List<String> headers = new ArrayList<String>();
            for (int i = 0; i < sql.getSelects().size(); i++)
                headers.add(sql.getSelects().get(i).getAlias());
            table.add(0, headers);

            // configure output
            WrapperConfigurations wc = new WrapperConfigurations();
            wc.setCssName(cssFilename);
            wc.setDecimalNumbers(Integer.valueOf(decimalNumbers));
            wc.setDecimalSeparator(decimalSeparator);
            wc.setThousandSeparator(thousandSeparator);
            try {
                wc.setValueColumnIndex(Integer.valueOf(valueIndex));
            } catch (Exception e) {

            }

            // create HTML
            StringBuilder excel = wrapper.wrapAsExcel(table, UUID.randomUUID().toString() + ".xls");

            /* Stream result */
            return Response.status(200).entity(excel.toString()).build();

        } catch (WDSException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        } catch (SQLException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        } catch (InstantiationException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        } catch (UnknownHostException e) {
            e.printStackTrace();
            return Response.status(500).entity("REST '/table/excel' service: " + e.getMessage()).build();
        }

    }

    public String replaceLimitWithTop(SQLBean sql) {
        for (NestedWhereBean nwb : sql.getNestedWheres()) {
            SQLBean sql2 = nwb.getNestedCondition();
            String script2 = Bean2SQL.convert(sql2).toString();
            if (sql2.getLimit() != null && sql2.getLimit().length() > 0) {
                if (sql.getLimit() != SQL.NONE.name()) {
                    script2 = script2.replaceFirst("SELECT ", "SELECT TOP " + sql2.getLimit() + " ");
                    script2 = script2.replaceFirst("LIMIT " + sql2.getLimit(), "");
                }
            }
            sql2.setQuery(script2);
        }
        String script = Bean2SQL.convert(sql).toString();
        if (sql.getLimit() != null && sql.getLimit().length() > 0) {
            if (sql.getLimit() != SQL.NONE.name()) {
                script = script.replaceFirst("SELECT ", "SELECT TOP " + sql.getLimit() + " ");
                script = script.replaceFirst("LIMIT " + sql.getLimit(), "");
            }
        }
        return script;
    }

    /**
     * @param datasource e.g. FAOSTAT
     * @param json       Parameters to build SQL query
     * @return HTML formatted <code>String</code>
     * <p/>
     * Produces a HTML table.
     */
    @POST
    @Produces(MediaType.APPLICATION_JSON)
    @Path("/json")
    public Response createJSON(@FormParam("datasource") final String datasource,
            @FormParam("json") final String json) {

        // Initiate the stream
        StreamingOutput stream = new StreamingOutput() {

            @Override
            public void write(OutputStream os) throws IOException, WebApplicationException {

                // compute result
                Writer writer = new BufferedWriter(new OutputStreamWriter(os));
                SQLBean sql = null;
                try {
                    sql = g.fromJson(json, SQLBean.class);
                } catch (JsonSyntaxException e) {
                    e.printStackTrace();
                }
                DatasourceBean db = datasourcePool.getDatasource(datasource.toUpperCase());

                // alter the query to switch from LIMIT to TOP
                if (datasource.toUpperCase().startsWith("FAOSTAT"))
                    sql.setQuery(replaceLimitWithTop(sql));

                // compute result
                JDBCIterable it = new JDBCIterable();

                try {

                    // Log the request
                    save(new Date(), "/table/json", json);

                    // Query DB
                    it.query(db, sql.getQuery());

                } catch (IllegalAccessException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/json' thrown an error: " + e.getMessage()));
                } catch (InstantiationException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/json' thrown an error: " + e.getMessage()));
                } catch (SQLException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/json' thrown an error: " + e.getMessage()));
                } catch (ClassNotFoundException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/json' thrown an error: " + e.getMessage()));
                } catch (Exception e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/json' thrown an error: " + e.getMessage()));
                }

                // write the result of the query
                writer.write("[");
                while (it.hasNext()) {
                    List<String> s = it.next();
                    try {
                        writer.write(g.toJson(s));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (it.hasNext())
                        writer.write(",");
                }
                writer.write("]");

                // Convert and write the output on the stream
                writer.flush();

            }

        };

        /* Stream result */
        return Response.status(200).entity(stream).build();

    }

    @POST
    @Produces(MediaType.APPLICATION_JSON)
    @Path("/jsonobjects")
    public Response createJSONObjects(@FormParam("datasource") final String datasource,
            @FormParam("json") final String json) {

        /* Initiate the stream */
        StreamingOutput stream = new StreamingOutput() {

            @Override
            public void write(OutputStream os) throws IOException, WebApplicationException {

                /* Compute result. */
                Writer writer = new BufferedWriter(new OutputStreamWriter(os));
                SQLBean sql = null;
                try {
                    sql = g.fromJson(json, SQLBean.class);
                } catch (JsonSyntaxException e) {
                    e.printStackTrace();
                }
                DatasourceBean db = datasourcePool.getDatasource(datasource.toUpperCase());

                /* Alter the query to switch from LIMIT to TOP. */
                if (datasource.toUpperCase().startsWith("FAOSTAT"))
                    sql.setQuery(replaceLimitWithTop(sql));

                /* Compute result. */
                JDBCIterable it = new JDBCIterable();
                List<String> headers = new ArrayList<String>();

                try {

                    /* Query DB. */
                    it.query(db, sql.getQuery());

                    /* Get column names. */
                    headers = it.getColumnNames();

                } catch (IllegalAccessException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/jsonobjects' thrown an error: " + e.getMessage()));
                } catch (InstantiationException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/jsonobjects' thrown an error: " + e.getMessage()));
                } catch (SQLException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/jsonobjects' thrown an error: " + e.getMessage()));
                } catch (ClassNotFoundException e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/jsonobjects' thrown an error: " + e.getMessage()));
                } catch (Exception e) {
                    e.getMessage();
                    WDSExceptionStreamWriter.streamException(os,
                            ("REST '/table/jsonobjects' thrown an error: " + e.getMessage()));
                }

                /* Write the result of the query. */
                writer.write("[");
                while (it.hasNext()) {
                    List<String> s = it.next();
                    try {
                        StringBuilder sb = new StringBuilder();
                        sb.append("{");
                        for (int i = 0; i < s.size(); i++) {
                            sb.append("\"").append(headers.get(i)).append("\": ").append("\"").append(s.get(i))
                                    .append("\"");
                            if (i < s.size() - 1)
                                sb.append(",");
                        }
                        sb.append("}");
                        writer.write(sb.toString());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (it.hasNext())
                        writer.write(",");
                }
                writer.write("]");

                /* Flush the scream. */
                writer.flush();

            }

        };

        /* Stream result */
        return Response.status(200).entity(stream).build();

    }

    private ArrayList<String> getHeadersFromSQL(String sql) {
        ArrayList<String> l = new ArrayList<String>();
        sql = sql.toLowerCase();
        String selects = sql.substring("select ".length() + sql.indexOf("select "), sql.indexOf("from"));
        StringTokenizer st = new StringTokenizer(selects, ",");
        while (st.hasMoreTokens())
            l.add(st.nextToken().trim());
        return l;
    }

    private void save(Date date, String rest, String payload) throws UnknownHostException {
        MongoDBConnectionManager mgr = MongoDBConnectionManager.getInstance();
        Mongo mongo = mgr.getMongo(null);
        DB db = mongo.getDB(SCHEMA);
        DBCollection collection = db.getCollection("logs");
        BasicDBObject document = new BasicDBObject();
        document.put("date", date);
        document.put("rest", rest);
        DBObject dbObject = (DBObject) JSON.parse(payload);
        document.put("payload", dbObject);
        collection.insert(document);
    }

}