com.softlyinspired.jlw.script.validationScript.java Source code

Java tutorial

Introduction

Here is the source code for com.softlyinspired.jlw.script.validationScript.java

Source

/*
 * This code is part of JLW Warehouse Wonder
 * Copyright (c) 2016-  Julie Kenny @ Softly Inspired  All rights reserved. 
 *
 * 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 com.softlyinspired.jlw.script;

import javax.swing.JOptionPane;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;

import com.mongodb.BasicDBObject;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.DBObject;
import com.softlyinspired.jlw.JLWProperties;
import com.softlyinspired.jlw.JLWUtilities;
import com.softlyinspired.jlw.connections.dbConnection;
import com.softlyinspired.jlw.mongodb.repoConnection;

import java.net.UnknownHostException;
import java.sql.*;
import java.util.*;

public class validationScript {

    public int scriptCount;
    public int currentScriptId;
    public String currentScriptText;
    public String currentScriptCategory;
    public String currentScriptTitle;
    public String currentConnection;

    /**
     * Lists all the scripts available
     * @return  Two dimensional array
     */
    public String[][] listall() {
        String scriptList[][] = new String[100][2];
        String tempId = new String();
        String tempTitle = new String();

        try {
            DBCollection coll = repoConnection.getScriptCollection();

            DBObject doc;
            BasicDBObject query = new BasicDBObject();
            BasicDBObject fields = new BasicDBObject();
            BasicDBObject sort = new BasicDBObject();

            fields.put("scriptText", 1);
            fields.put("scriptId", 1);
            fields.put("scriptTitle", 1);
            fields.put("_id", 0);

            sort.put("scriptId", 1);

            try {
                DBCursor allScripts = coll.find(query, fields); //.sort(sort);   
                allScripts.sort(sort);
                scriptCount = -1;
                while (allScripts.hasNext()) {
                    doc = allScripts.next();
                    scriptCount = scriptCount + 1;
                    tempId = doc.get("scriptId").toString();
                    doc.get("scriptText").toString();
                    try {
                        tempTitle = doc.get("scriptTitle").toString();
                    } catch (Exception e) {
                        tempTitle = "";
                    }

                    scriptList[scriptCount][0] = tempId;
                    scriptList[scriptCount][1] = tempTitle;
                }

            } catch (Exception e) {
                System.out.println("Script Missing");
            }

        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage(e.toString());

        }
        return scriptList;

    }

    /**
     * Accepts a menuId and sets the currentScriptText string
     * @param MenuId
     */
    public void fetchScriptFromMenu(int MenuId) {
        try {
            int ScriptId = -1;
            String scriptValue = null;

            ScriptId = fetchMenuScriptId(MenuId);
            currentScriptId = ScriptId;

            if (ScriptId != -1) {
                fetchScriptValue(ScriptId);
                currentScriptText = scriptValue;
            }
        } catch (Exception ex) {
            JLWUtilities.scriptErrorMessage("Error fetching menu script");
        }
    }

    /**
     * Accepts the menuId and returns the scriptId 
     * @param MenuId
     * @return
     */
    public int fetchMenuScriptId(int MenuId) {
        int ScriptId = -1;
        try {
            ScriptId = readMongoMenuScriptId(MenuId);
            if (ScriptId == -1) {
                JLWUtilities.scriptErrorMessage("No Script Found");
            }
        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage("Error fetching script");
        }
        return ScriptId;
    }

    /**
     * returns a scriptId
     * @param MenuId
     * @throws UnknownHostException
     */
    private int readMongoMenuScriptId(int MenuId) throws UnknownHostException {
        int scriptId = -1;

        DBCollection coll = repoConnection.getScriptCollection();

        BasicDBObject query = new BasicDBObject();
        BasicDBObject fields = new BasicDBObject();
        query.put("menuId", MenuId);

        fields.put("scriptIds", 1);
        fields.put("_id", 0);

        try {
            DBObject scriptIdQuery = coll.findOne(query, fields);
            scriptId = Integer.parseInt(scriptIdQuery.get("scriptIds").toString());

        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage("Error fetching mongo script");
        }

        return scriptId;
    }

    /**
     * Gets the value of the script given the ID
     * @param ScriptId
     * @return
     */
    public int fetchScriptValue(int ScriptId) {
        /** this method accepts a script id and sets all the values on the script 
         *  Int value returned, 0 if all fine , 1 if not
         **/
        int scriptStatus = 0;
        try {
            scriptStatus = readMongoScript(ScriptId);
        } catch (Exception e) {

            System.out.println("error in fetchScriptValue");
            return 1;
        }
        if (scriptStatus != 0) {
            return 1;
        }
        return scriptStatus;
    }

    /**
     * Gets the full details of the script using the script id
     * @param ScriptId
     * @return
     * @throws UnknownHostException
     */
    private int readMongoScript(int ScriptId) throws UnknownHostException { //String scriptText = new String();
        int errorId = 0;

        DBCollection coll = repoConnection.getScriptCollection();
        BasicDBObject query = new BasicDBObject();
        BasicDBObject fields = new BasicDBObject();

        query.put("scriptId", ScriptId);

        fields.put("scriptText", 1);
        fields.put("ScriptCategory", 1);
        fields.put("scriptTitle", 1);
        fields.put("scriptConnection", 1);
        fields.put("_id", 0);

        try {
            DBObject customQuery = coll.findOne(query, fields);

            try {
                currentScriptText = customQuery.get("scriptText").toString();
            } catch (Exception e) {
                currentScriptText = "unknown";
            }
            try {
                currentScriptCategory = customQuery.get("ScriptCategory").toString();
            } catch (Exception e) {
                currentScriptCategory = "unknown";
            }
            try {
                currentScriptTitle = customQuery.get("scriptTitle").toString();
            } catch (Exception e) {
                currentScriptTitle = "unknown";
            }
            try {
                currentConnection = customQuery.get("scriptConnection").toString();
            } catch (Exception e) {
                currentConnection = "unknown";
            }
        } finally {
        }

        return errorId;
    }

    /**
     * delete the script given the scriptid
     * @param scriptId
     */
    public void delete(int scriptId) {
        try {
            deleteScript(scriptId);
        } catch (UnknownHostException e) {
            JLWUtilities.scriptErrorMessage("Error attempting delete");
        }
    }

    /**
     * delete the script from the mongodb
     * @param ScriptId
     * @throws UnknownHostException
     */
    public void deleteScript(int ScriptId) throws UnknownHostException {
        DBCollection coll = repoConnection.getScriptCollection();

        try {
            BasicDBObject query = new BasicDBObject();
            query.put("scriptId", ScriptId);
            coll.remove(query);
        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage("Error deleting script");
        }
    }

    /**
     * create script with given details
     * @param scriptId
     * @param scriptValue
     * @param scriptCategory
     * @param scriptTitle
     * @param scriptConnection
     */
    public void create(int scriptId, String scriptValue, String scriptCategory, String scriptTitle,
            String scriptConnection) {

        try {
            writeNewScript(scriptId, scriptValue, scriptCategory, scriptTitle, scriptConnection);
            JLWUtilities.scriptInfoMessage("Successfully saved script");
        } catch (UnknownHostException e) {
            JLWUtilities.scriptErrorMessage("Unknown Host");
        }
    }

    /**
     * Update an existing script, all details passed.
     * @param scriptId
     * @param scriptValue
     * @param scriptCategory
     * @param scriptTitle
     * @param scriptConnection
     */
    public void updateScript(int scriptId, String scriptValue, String scriptCategory, String scriptTitle,
            String scriptConnection) {
        try {
            writeExistingScript(scriptId, scriptValue, scriptCategory, scriptTitle, scriptConnection);
            JLWUtilities.scriptInfoMessage("Successfully updated script");
        } catch (UnknownHostException e) {
            JLWUtilities.scriptErrorMessage("Unknown Host");
        }
    }

    /**
     * Write details of existing script to mongodb
     * @param scriptId
     * @param scriptText
     * @param scriptCategory
     * @param scriptTitle
     * @param scriptConnection
     * @throws UnknownHostException
     */
    private void writeExistingScript(int scriptId, String scriptText, String scriptCategory, String scriptTitle,
            String scriptConnection) throws UnknownHostException {
        DBCollection coll = repoConnection.getScriptCollection();
        BasicDBObject updateQuery = new BasicDBObject();

        BasicDBObject searchQuery = new BasicDBObject().append("scriptId", scriptId);

        DBObject changes = new BasicDBObject().append("scriptText", scriptText)
                .append("ScriptCategory", scriptCategory).append("scriptTitle", scriptTitle)
                .append("scriptConnection", scriptConnection);
        updateQuery.append("$set", changes);

        try {
            coll.update(searchQuery, updateQuery);
        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage("Error creating script");
        }

    }

    /**
     * write new script to mongodb given full details
     * @param scriptId
     * @param scriptText
     * @param scriptCategory
     * @param scriptTitle
     * @param scriptConnection
     * @throws UnknownHostException
     */
    private void writeNewScript(int scriptId, String scriptText, String scriptCategory, String scriptTitle,
            String scriptConnection) throws UnknownHostException {
        DBCollection coll = repoConnection.getScriptCollection();

        BasicDBObject query = new BasicDBObject();

        query.put("scriptId", scriptId);
        query.put("scriptText", scriptText);
        query.put("scriptTitle", scriptTitle);
        query.put("ScriptCategory", scriptCategory);
        query.put("scriptConnection", scriptConnection);

        try {
            coll.insert(query);
        } catch (Exception e) {
            JLWUtilities.scriptErrorMessage("Error creating script");
        }

    }

    public String getOutputFile(int scriptId) {
        String fn = new String();
        String fileName = new String();
        fileName = currentScriptTitle + '_' + Integer.toString(scriptId) + "_output.csv";
        fileName = JLWProperties.scriptOutput + "\\" + fileName;
        return "fileName";
    }

    /**
     * run script given the id
     * @param scriptId
     */
    public int runScript(int scriptId) {
        /** This will run a script and output the result to a file as csv **/

        int result = 0;
        String tracking = "";
        try {
            fetchScriptValue(scriptId);
        } catch (Exception e) {
            result = 1;
            System.out.println("error getting script details");
        }
        ;

        try {
            String fileName = new String();
            fileName = getOutputFile(scriptId);
            File file = new File(fileName);

            // if file doesn't exists, then create it
            if (!file.exists()) {
                file.createNewFile();
            }
            tracking = "\n" + "output location set";

            FileWriter fw = new FileWriter(file.getAbsoluteFile());
            BufferedWriter bw = new BufferedWriter(fw);

            tracking = tracking + "\n" + "Buffer opened";

            dbConnection cn = new dbConnection();

            cn.fetchDetails(currentConnection);
            tracking = tracking + "\n" + "Connection details fetched";

            Class.forName(cn.connectionDriver);
            tracking = tracking + "\n" + "driver set";

            // Database credentials
            Connection conn = null;
            Statement stmt = null;

            conn = DriverManager.getConnection(cn.connectionURL, cn.connectionUser,
                    String.valueOf(cn.connectionPassword));
            stmt = conn.createStatement();
            tracking = tracking + "\n" + "statement prepared";

            ResultSet rs = stmt.executeQuery(currentScriptText);
            ResultSetMetaData rsmd = rs.getMetaData();
            tracking = tracking + "\n" + "result returned";

            int numberOfColumns = rsmd.getColumnCount();
            String columnName = new String("");
            tracking = tracking + "\n" + "result prep step 1";

            // Output column names
            for (int i = 1; i <= numberOfColumns; i++) {
                if (i == 1) {
                    columnName = rsmd.getColumnName(i);
                } else {
                    columnName = columnName + "," + rsmd.getColumnName(i);
                }
            }
            bw.write(columnName);
            tracking = tracking + ",2";

            String rowValues = new String("");
            while (rs.next()) {
                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i == 1) {
                        rowValues = rs.getObject(i).toString();
                    } else {
                        rowValues = rowValues + ',' + rs.getObject(i).toString();
                    }
                }
                bw.newLine();
                bw.write(rowValues);
            }
            tracking = tracking + ",3";

            bw.close();
            rs.close();
            stmt.close();
            conn.close();

        } catch (Exception e) {
            result = 1;
            JLWUtilities.scriptErrorMessage(tracking + "\n" + "Error running script");
        }

        return result;
    }

}