source: main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/controller/JDBCAjaxDataSourceServlet.java@ 34511

package jquery.datatables.controller;

import java.io.IOException;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import jquery.datatables.model.RecordHashmap;
import jquery.datatables.model.DataRepository;
import jquery.datatables.controller.DataTableRequestParam;

/**
 * JDBCAjaxDataSourceServlet provides data to the JQuery DataTables
 */
// The following specified through @annotations was found to not work
// when combined with additional settings specified in web.xml
// Consequently all the parameter settings were moved to web.xml
//
// Defunct:
//   AT WebServlet(name="JDBCAjaxDataSource", urlPatterns={"/JDBCAjaxDataSource"})
//   AT WebServlet("/JDBCAjaxDataSource")
// Handled in web.xml (technically web.xml.in) so init-param can specify
// where on the file system the database file is to be found

public class JDBCAjaxDataSourceServlet extends HttpServlet
{
    private static final long serialVersionUID = 1L;
    
    /**
     * @see Servlet#init(ServletConfig)
     */
    public void init(ServletConfig config) throws ServletException
    {
    super.init(config);
    
    String jdbc_uri = config.getInitParameter("jdbc.uri");
    String jdbc_db_table= config.getInitParameter("jdbc.db.table");
    
    System.err.println("*** jdbc_url = " + jdbc_uri);
    System.err.println("*** jdbc_db_table = " + jdbc_db_table);
    
    DataRepository.init(jdbc_uri,jdbc_db_table);
    }
    
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
    final DataTableRequestParam param = DataTablesParamUtility.getParam(request);
    
    String sEcho = param.sEcho;
    int iTotalRecords;                // total number of records (unfiltered)
    int iTotalDisplayRecords;         //value will be set when code filters records by keyword

    JSONArray data = new JSONArray(); //data that will be shown in the table
    
    iTotalRecords = DataRepository.GetRecords().size();
    List<RecordHashmap> records = new LinkedList<RecordHashmap>();

    List<RecordHashmap> all_records = DataRepository.GetRecords(); // Triggers reading in Database, first time called

    // Filter records based on value specified in search box (matched only to columns are marked as searchable)
    for (RecordHashmap rh : all_records) {

        ArrayList<String> column_headings = DataRepository.getColumnHeadings();
        int num_columns = column_headings.size();
        
        // Don't want search/filter by column 0 (id), so start at index pos 1
        for (int i=1; i<num_columns; i++) {
        String field = column_headings.get(i);
                        
        if (param.bSearchable[i] &&
            rh.getFieldValue(field).toLowerCase().contains(param.sSearchKeyword.toLowerCase())) {
            records.add(rh); // Add a record that matches search criterion
            break;
        }
        }
        
        /*              
        if (param.bSearchable[1] &&
        rh.getFieldValue("Year").toLowerCase().contains(param.sSearchKeyword.toLowerCase())
        ||
        param.bSearchable[2] &&
        rh.getFieldValue("Name").toLowerCase().contains(param.sSearchKeyword.toLowerCase())
        ||
        param.bSearchable[3] &&
        rh.getFieldValue("Address").toLowerCase().contains(param.sSearchKeyword.toLowerCase()))
        {
            records.add(rh); // Add a record that matches search criterion
        }
        */
    }
    
    iTotalDisplayRecords = records.size();//Number of records that matches search criterion should be returned

    //
    // Move on to sorting results
    //
    Collections.sort(records, new Comparator<RecordHashmap>() {
        @Override
        public int compare(RecordHashmap rh1, RecordHashmap rh2) {
            int result = 0;

            ArrayList<String> column_headings = DataRepository.getColumnHeadings();
            
            for (int i=0; i<param.iSortingCols; i++) {
            int sortBy = param.iSortCol[i];

            if (param.bSortable[sortBy]) {
                if (sortBy == 0) {
                result = 0; //sort by id is not allowed
                }
                else {
                String field = column_headings.get(sortBy);
                result = rh1.getFieldValue(field).compareToIgnoreCase(rh2.getFieldValue(field)) * 
                    (param.sSortDir[i].equals("asc") ? -1 : 1);
                }

                /*
                switch(sortBy) {
                case 0:
                result = 0; //sort by id is not allowed
                break;
                default:
                String field = _columnHeadings.get(sortBy);
                result = rh1.getFieldValue(field).compareToIgnoreCase(rh2.getFieldValue(field)) * 
                    (param.sSortDir[i].equals("asc") ? -1 : 1);
                break;
                / *             
                case 1:
                result = rh1.getFieldValue("Year").compareToIgnoreCase(rh2.getFieldValue("Year")) * 
                    (param.sSortDir[i].equals("asc") ? -1 : 1);
                break;
                case 2:
                result = rh1.getFieldValue("Name").compareToIgnoreCase(rh2.getFieldValue("Name")) * 
                    (param.sSortDir[i].equals("asc") ? -1 : 1);
                break;
                case 3:
                result = rh1.getFieldValue("Address").compareToIgnoreCase(rh2.getFieldValue("Address")) *
                    (param.sSortDir[i].equals("asc") ? -1 : 1);
                    break;* /
                
                    } */
            } 

            if (result != 0) {
                return result;
            }
            else {
                continue;
            }
            }
            
            return result;
        }
        });
    
    if (records.size() < param.iDisplayStart + param.iDisplayLength) {
        records = records.subList(param.iDisplayStart, records.size());
    }
    else {
        records = records.subList(param.iDisplayStart, param.iDisplayStart + param.iDisplayLength);
    }
    
    // Convert resulting (filtered & sorted) records into JSON for output
    
    try {
        JSONObject jsonResponse = new JSONObject();
        
        jsonResponse.put("sEcho", sEcho);
        jsonResponse.put("iTotalRecords", iTotalRecords);
        jsonResponse.put("iTotalDisplayRecords", iTotalDisplayRecords);
        
        for(RecordHashmap rh : records) {
        JSONArray row = new JSONArray();

        row.put(rh.getId());
        List<String> field_keys = rh.getFields();
        
        for (String field : field_keys) {
            String field_value = rh.getFieldValue(field);
            row.put(field_value);
        }

        /*
        row.put(rh.getId()).put(rh.getFieldValue("Year")).put(rh.getFieldValue("Name")).put(rh.getFieldValue("Address")).put(rh.getFieldValue("Field4")).put(rh.getFieldValue("Field5"));
        */
        
        data.put(row);
        }
        
        jsonResponse.put("aaData", data);
        
        response.setContentType("application/json");
        response.getWriter().print(jsonResponse.toString());
    }
    catch (JSONException e) {
        e.printStackTrace();
        response.setContentType("text/html");
        response.getWriter().print(e.getMessage());
    }
    
    }
}