DB.java

package com.application.DB;

import com.google.cloud.bigquery.*;
import org.joda.time.DateTime;

import java.util.*;

import static com.application.DB.Constants.*;

/**
 * This class is responsible for handling database related activities
 *
 * @author Eilert Tunheim, Karin Pettersen, Mads Arnesen
 * @version 1.0
 */
public class DB {

    /**
     * Launching the functions
     *
     * @param args default param
     * @throws Exception throws exception in case of error
     */
    public static void main(String[] args) throws Exception {
        //getKwh();
        //getName();
        //getZeroPointDate();
        System.out.println(setInputParameters());

    }


    public static Map<Integer, Map<String, Number>> setInputParameters() throws Exception {

        Map<Integer, Map<String, Number>> allDryingPeriods = new HashMap<>();

        boolean sawsetRemoved = false;
        boolean treespeciesRemoved = false;
        boolean dimensionsRemoved = false;
        boolean moistureRemoved = false;


        HashMap<java.lang.String, java.lang.String> locationTables = null;
        ArrayList<Integer> valmeticsChamberIDS = null;
        ArrayList<Integer> kwhChamberIDS = null;
        int numberOfChambers;

        String extraInputParameter = "";
        int locationID;
        int index = 0;

        String treeSpecies = "";
        String dimensions = "";
        String sawset = "";
        String moistureGoal = "";

        // Valmetics parameters
        String nameParameter;
        String kilinName;
        int kilinID;
        String startDryingTime;
        String stopDryingTime;
        String valmeticsTableName;
        int limit;

        // Kwh parameters
        String kwhTableName;
        String KwhName;
        String timestamp;
        String valueIDName;
        int valueID;


        while(true){

            Map<Integer, Map<String, Number>> intermediateHashMap = new HashMap<>();

            for (HashMap.Entry<Integer, HashMap<String, HashMap<String, String>>> location : Constants.getSawmills().entrySet()) {

                // Clears the extraInputs variable
                extraInputParameter = "";

                System.out.printf("\nLocation ID: \t%s\t\t\tRest of map: \t%s\n", location.getKey(), location.getValue());
                System.out.println("Number of sawmills: "+Constants.getSawmills().size());

                //allDryingPeriods = new TreeMap<>();

                locationID = location.getKey();

                //System.out.println(location.getValue());

                if (location.getKey() == 124) {
                    // If location is Valasen, then the database stores furu as fura, swedish.
                    if (TREE_SPECIES.equalsIgnoreCase("Furu")) {
                        TREE_SPECIES = "Fura";
                    }
                    locationTables = getValasenTableNames();
                    valmeticsChamberIDS = getValasenValmeticsChamberIDS();
                    kwhChamberIDS = getValasenKwhChamberIDS();
                }
                if (location.getKey() == 174) {
                    if (TREE_SPECIES.equalsIgnoreCase("Fura")) {
                        TREE_SPECIES = "Furu";
                    }
                    locationTables = getArjangTableNames();
                    valmeticsChamberIDS = getArjangValmeticsChamberIDS();
                    kwhChamberIDS = getArjangKwhChamberIDS();
                }

                // Valmetics
                valmeticsTableName = locationTables.get("Valmetics");
                nameParameter = location.getValue().get(locationTables.get("Valmetics")).get("Name");
                startDryingTime = location.getValue().get(locationTables.get("Valmetics")).get("DryingStarted");
                stopDryingTime = location.getValue().get(locationTables.get("Valmetics")).get("DryingStopped");
                kilinName = location.getValue().get(locationTables.get("Valmetics")).get("KilnName");
                //kilinID = Integer.parseInt(location.getValue().get(locationTables.get("Valmetics")).get("KilinID"));
                limit = Integer.parseInt(location.getValue().get(locationTables.get("Valmetics")).get("Limit"));

                // Kwh
                kwhTableName = locationTables.get("Kwh");
                KwhName = location.getValue().get(locationTables.get("Kwh")).get("KwhName");
                timestamp = location.getValue().get(locationTables.get("Kwh")).get("Timestamp");
                valueIDName = location.getValue().get(locationTables.get("Kwh")).get("ValueIDName");
                //valueID = Integer.parseInt(location.getValue().get(locationTables.get("Kwh")).get("ValueID"));


                System.out.printf("Tree species: \t%s\n", TREE_SPECIES);
                System.out.printf("Width: \t\t\t%s\n", DIMENSIONS);
                System.out.printf("Sawset: \t\t%s\n", SAWSET);
                System.out.printf("Moisture: \t\t%s\n", MOISTURE_GOAL);


                // Defining the extra sql statements for extra parameters
                treeSpecies = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + TREE_SPECIES + "%" + '"' + ") ";
                dimensions = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + DIMENSIONS + "%" + '"' + ") ";
                sawset = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + SAWSET + "%" + '"' + ") ";
                moistureGoal = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + MOISTURE_GOAL + "\\\\%" + "%" + '"' + ") ";


                // 1. Checking if user input is empty.
                // 2. Checking if the extraInputParameter variable already contains the extra parameters,
                //    if so not adding a second time.
                // 3. Checking if the while loop have previously removed the parameter, if so, not adding again.
                if (!TREE_SPECIES.isEmpty() && !extraInputParameter.contains(treeSpecies) && !treespeciesRemoved) {
                    extraInputParameter += treeSpecies;
                }
                if (!DIMENSIONS.isEmpty() && !extraInputParameter.contains(dimensions) && !dimensionsRemoved) {
                    extraInputParameter += dimensions;
                }
                if (!SAWSET.isEmpty() && !extraInputParameter.contains(sawset) && !sawsetRemoved) {
                    extraInputParameter += sawset;
                }
                if (!MOISTURE_GOAL.isEmpty() && !extraInputParameter.contains(moistureGoal) && !moistureRemoved) {
                    extraInputParameter += moistureGoal;
                }

                System.out.printf("\nExtra parameters:\n %s\n\n", extraInputParameter);


                // Sets the lowest number of chambers to iterate through
                numberOfChambers = Math.min(valmeticsChamberIDS.size(), kwhChamberIDS.size());

                System.out.printf("\nnumberOfChambers: \t%d\n",numberOfChambers);

                for (int i = 0; i < numberOfChambers; i++) {
                    // Sets the kiln number
                    kilinID = valmeticsChamberIDS.get(i);
                    valueID = kwhChamberIDS.get(i);

                    System.out.printf("KilinID: \t%d\n",kilinID);
                    System.out.printf("valueID: \t%d\n",valueID);

                //System.out.println("Før results");
                Map<Integer, Map<String, Number>> results = new TreeMap<>(getKwh(getZeroPointDate(locationID, extraInputParameter, nameParameter, kilinName, kilinID, startDryingTime, stopDryingTime, valmeticsTableName, limit),
                        locationID, kwhTableName, KwhName, timestamp, valueIDName, valueID, index));

                //System.out.println("\n\nResults size: "+results.size()+"\n\n");
                intermediateHashMap.putAll(results);
                index += results.size();
                //System.out.println("Etter results");
                }
            }


            System.out.printf("\nExtra parameters:\n %s\n\n", extraInputParameter);
            System.out.println("\n\nAll drying periods size: "+intermediateHashMap.size()+"\n\n");
            // Retrieves the dates
            //results = getKwh(getZeroPointDate(locationID, extraInputParameter, nameParameter, kilinName, kilinID, startDryingTime, stopDryingTime, valmeticsTableName, limit));

            // Checks if any dates where found, if not parameters are removed until dates are found
            if (intermediateHashMap.size() < NUMBER_OF_PERIODS) {
                //System.out.println("\nInni if, sletter parameter:\n");
                if (extraInputParameter.contains(sawset) && !sawsetRemoved) {
                    extraInputParameter = extraInputParameter.replace(sawset, "");
                    System.out.println("Sawset is removed");
                    sawsetRemoved = true;
                    //setInputParameters(true,false,false,false);
                } else if (extraInputParameter.contains(treeSpecies) && !treespeciesRemoved) {
                    extraInputParameter = extraInputParameter.replace(treeSpecies, "");
                    System.out.println("Tree species is removed");
                    treespeciesRemoved = true;
                    //setInputParameters(true,true,false,false);
                } else if (extraInputParameter.contains(dimensions) && !dimensionsRemoved) {
                    extraInputParameter = extraInputParameter.replace(dimensions, "");
                    System.out.println("Dimensions is removed");
                    dimensionsRemoved = true;
                    //setInputParameters(true,true,true,false);
                } else if (extraInputParameter.contains(moistureGoal) && !moistureRemoved) {
                    extraInputParameter = extraInputParameter.replace(moistureGoal, "");
                    System.out.println("Moisture goal is removed");
                    moistureRemoved = true;
                    //setInputParameters(true,true,true,true);
                } else break;
            } else {
                // Adding all periods to the hashmap to return
                allDryingPeriods.putAll(intermediateHashMap);
                break;
            }
        }

        System.out.println(allDryingPeriods.size());
        for (Map.Entry<Integer, Map<String, Number>> entry : allDryingPeriods.entrySet()) {
            System.out.printf("Timestamp: \t%s\t\t\tkWh: \t%s\n", entry.getKey(), entry.getValue());
        }

        return allDryingPeriods;
    }


    /**
     * Retrieves information about kWh and the corresponding date
     *
     * @return the results
     * @throws Exception returns potential error
     */
    public static Map<Integer, Map<String, Number>> getKwh(Map<String, String> dates, int locationID,
                                                           String kwhTableName, String KwhName,
                                                           String timestamp, String valueIDName,
                                                           int valueID, int index) throws Exception {

        int indx=  0;

        // Initializing the data map to store the results
        Map<Integer, Map<String, Number>> finalResults = new HashMap<>();

        for (Map.Entry<String, String> entry : dates.entrySet()) {
            //System.out.printf("Intid: \t%s\t\t\tOuttid: \t%s\n",entry.getKey(),entry.getValue());

            // Initializing the data map to store the results
            Map<String, Number> data = new HashMap<>();

            // Preparing a query statement
            // Query statement 124 Valåsen
            final String sqlStatement = "SELECT `" + timestamp + "`, `" + KwhName + "` " +
                    "FROM `" + PROJECT_ID + "." + locationID + "." + kwhTableName + "` " +
                    "WHERE " + timestamp + " BETWEEN " + '"' + entry.getKey() + '"' +
                    " AND " + '"' + entry.getValue() + '"' +
                    " AND " + valueIDName + " = " + valueID + " " +
                    " AND " + KwhName + " <> 0 " +
                    " ORDER BY " + timestamp + " ASC";


            System.out.println(sqlStatement);

            // Iterating through the results
            TableResult result = HelpingFunctions.createQueryJob(sqlStatement);

            //System.out.println("Timestamp \t kWh");
            int baseline = 0;
            HelpingFunctions.iterateKwhValues(data, baseline, result, KwhName, timestamp);

            System.out.println("Data size: " + data.size());

            NavigableMap<String, Number> sortedData = new TreeMap<>(data);
            if (!sortedData.isEmpty() && sortedData.size() > 50) {
                finalResults.put(index, sortedData);
                index += 1;

                // entry.value burde matche med en av de og går igjennom alle
                for (Map.Entry<String, String> moistureEntry : HelpingFunctions.getManMoist().entrySet()) {

                    if(moistureEntry.getKey().equals(entry.getValue())){
                        // Adjust kwh
                        System.out.println("Inni!!@@@@@");
                        System.out.printf("Mouisture key: \t%s",moistureEntry.getKey());
                        System.out.printf("\nFormatedUtTid: \t%s",entry.getValue());
                        System.out.println("\nMoisture diff: " + moistureEntry.getValue());
                        indx++;
                        System.out.println(indx);
                    }
                }
            }
        }

        System.out.println("\nFinal results size: " + finalResults.size()+"\n\n");

        // Defining a treemap to sort the data incrementally
        NavigableMap<Integer, Map<String, Number>> sortedFinalResults = new TreeMap<>(finalResults);

        for (Map.Entry<String, String> entry : dates.entrySet()) {

        }

        for (Map.Entry<Integer, Map<String, Number>> entry : sortedFinalResults.entrySet()) {

            Map<String, Number> data = entry.getValue();
            for (Map.Entry<String, Number> moistureEntry : data.entrySet()) {

                //System.out.println("Data key: "+moistureEntry.getKey());        // Key = datetime: 2022-01-18 20:23:36
                //System.out.println("Data Value: "+moistureEntry.getValue());    // Value = kwh: 5422
            }

        }

        for (Map.Entry<Integer, Map<String, Number>> entry : sortedFinalResults.entrySet()) {
            System.out.printf("Timestamp: \t%s\t\t\tkWh: \t%s\n", entry.getKey(), entry.getValue());
        }

        return sortedFinalResults;
    }


    /**
     * This function retrieves the intidtork and uttidtork dates from the database.
     * This variables are not sorted and thus this function iterates through the data in order to
     * find the last intidtork date and the corresponding uttidtork data.
     * These values will be used to reset the kWh.
     *
     * A possible extention of this function could be to limit the number of dring periodes.
     * This could be done with counting the number of entries in
     *
     * @return Returns a treemap that sorts the Start- and End time for each drying period incrementally
     * @throws Exception Throws exception if an error occurs
     */
    private static Map<String, String> getZeroPointDate(int locationID, String extraUserInput, String nameParameter,
                                                        String kilinName, int kilinID, String startDryingTime,
                                                        String stopDryingTime, String valmeticsTableName, int limit)
                                                        throws Exception {

        // Defining variables to be used later
        String treeSort = "";
        String thickness = "";
        String width = "";
        String sawSet = "";
        String moistureMeasured = "";

        String formatedInTidTork = "";
        String formatedUtTidTork = "";
        String formatedUtTidTorkPlussDays = "";

        // Initializing maps to store the results
        Map<String, String> dates = new HashMap<>();
        Map<String, String> manMoistData = new HashMap<>();


        // Defining extra parameters if required
        String extraInputParameter = "";
        extraInputParameter += extraUserInput;
        if(locationID == 124){
            extraInputParameter += "AND CalculatedStart BETWEEN \"1990-01-01 00:00:00\" AND \"" + TODAYS_DATE + "\" ";
        }
        if(locationID == 174){
            // KILIN_ID starts at 0 not 1 in the database.
            kilinID -= 1;
        }


        // Sqlstatement
        final String sqlStatement =
                        "SELECT MAX("+ nameParameter +") as DryingSchedule, MAX("+kilinName+") as Kiln_ID, " + startDryingTime + ", MAX("+stopDryingTime+") as DryingCompleted " +
                        "FROM `" + PROJECT_ID + "." + locationID + "." + valmeticsTableName + "` " +
                        "WHERE " + kilinName + " = " + kilinID + " " +
                        "AND "+startDryingTime+" BETWEEN \"1990-01-01 00:00:00\" AND \"" + TODAYS_DATE + "\" " +
                        "AND "+stopDryingTime+" BETWEEN \"1990-01-01 00:00:00\" AND \"" + TODAYS_DATE + "\" " +
                                extraInputParameter +
                        "AND LOWER(" +nameParameter+ ") NOT LIKE LOWER("+'"'+"%"+ "test" +"%"+'"'+") " +
                        "Group by "+ startDryingTime + " " +
                        "Order by "+ startDryingTime + " ASC " +
                        "LIMIT " + limit;

        System.out.println(sqlStatement);

        // Increments kilinID back to the correct id nr
        if(locationID == 174){
            kilinID += 1;
        }



        // Retrieves the results from the queryjob
        TableResult result = HelpingFunctions.createQueryJob(sqlStatement);

        //System.out.println("InTidTork\t\t\tUtTidTork");
        // Iterating through the results
        for (FieldValueList row : result.iterateAll()) {

            // Gets parameters from DryingSchedule
            System.out.println(row.get("DryingSchedule").getStringValue());
            if(!row.get("DryingSchedule").isNull()){
                String[] nameParameters = row.get("DryingSchedule").getValue().toString().split(" ");

                for (int i = 0; i < nameParameters.length; i++) {
                    if (nameParameters[i].contains("Fur")) {
                        treeSort = "F";
                    }
                    if (nameParameters[i].contains("Gran")) {
                        treeSort = "G";
                    }
                    if (nameParameters[i].matches("\\d+x\\d+")) {
                        String[] dimensions = nameParameters[i].split("x");
                        thickness = dimensions[0];
                        width = dimensions[1];
                    }
                    if (nameParameters[i].matches("\\d{1}ex")) {
                        sawSet = nameParameters[i];
                    }
                }
            }


            //System.out.println("Start: "+row.get("DryingStarted").getTimestampValue());
            //System.out.println("Stop: "+row.get("DryingCompleted").getTimestampValue());


            // Retrieving the data
            // DryingStarted:
            if(!row.get("DryingStarted").isNull()){

                // Check if response is given in millis
                try{
                    long doubleValue = row.get("DryingStarted").getTimestampValue();
                    long InTidTorkLong = doubleValue/1000;
                    // Formating the data from long to a string in the correct date format
                    formatedInTidTork = HelpingFunctions.getDateFormat().format(InTidTorkLong);
                    //System.out.println("LONG!!@@@@");
                } catch(NumberFormatException e){
                    //not long value, must be of type string

                    //System.out.println("Must be a string!!@@@@");

                    if(row.get("DryingStarted").getValue().toString().contains("T")){
                        // stores the value
                        String value = (String) row.get("DryingStarted").getValue();
                        // Splits the string based on 'T'
                        String[] splitValue = value.split("T");
                        // Combines the values into a new format
                        formatedInTidTork = splitValue[0]+" "+splitValue[1];
                    }
                    else formatedInTidTork = row.get("DryingStarted").getValue().toString();
                }

                // Checks if response is given in a string date format
                //if(row.get("DryingStarted").getValue().equals(Long.parseLong(row.get("DryingStarted").getValue().toString()))) {


            }

            // CalculatedStop:
            // DryingCompleted:
            // Check if response is given in millis
            try{
                long doubleValue = row.get("DryingCompleted").getTimestampValue();
                long utTidTorkLong = doubleValue/1000;
                // Formating the data from long to a string in the correct date format
                formatedUtTidTork = HelpingFunctions.getDateFormat().format(utTidTorkLong);
            } catch(NumberFormatException e) {
                //not long value, must be of type string

                if(row.get("DryingCompleted").getValue().toString().contains("T")){
                    // stores the value
                    String value = (String) row.get("DryingCompleted").getValue();
                    // Splits the string based on 'T'
                    String[] splitValue = value.split("T");
                    // Combines the values into a new format
                    formatedUtTidTork = splitValue[0] + " " + splitValue[1];
                }
                else formatedUtTidTork = row.get("DryingCompleted").getValue().toString();

            }



            // Getting manual measurement:
            // Adding days to search between
            String date = formatedUtTidTork.split(" ")[0];
            DateTime addedDays = new DateTime(date);
            formatedUtTidTorkPlussDays = addedDays.plusDays(ARJANG_NR_ADDED_DAYS).toString();
            formatedUtTidTorkPlussDays = formatedUtTidTorkPlussDays.split("T")[0];

            /*
            if (locationID == ARJANG_LOCATION_ID) {

                // Sqlstatement
                final String manMeasurementStatement = "SELECT Tork, Dato, Treslag, Tykkelse, Bredde, Planketype, Diff " +
                        "FROM `" + PROJECT_ID + "." + locationID + "." + ARJANG_MANFUKTREG + "` " +
                        "WHERE Tork LIKE \"%" + kilinID + "%\" " +
                        "AND Treslag LIKE \"%" + treeSort + "%\" " +
                        "AND Tykkelse = " + thickness + " " +
                        "AND Bredde = " + width + " " +
                        "AND Planketype = \"" + sawSet + "\" " +
                        "AND Dato BETWEEN \"" + date + "\" AND \"" + formatedUtTidTorkPlussDays + "\" " +
                        "ORDER BY Dato";

                System.out.println(manMeasurementStatement);


                // Retrieves the results from the queryjob
                TableResult resultManMeasurement = HelpingFunctions.createQueryJob(manMeasurementStatement);

                //System.out.println("InTidTork\t\t\tUtTidTork");
                // Iterating through the results
                for (FieldValueList manMeasurement : resultManMeasurement.iterateAll()) {

                    // Checks if value is null
                    if(!manMeasurement.get("Diff").isNull()){
                        moistureMeasured = manMeasurement.get("Diff").getValue().toString();
                    }
                }
                //System.out.println("moistureMeasured: " + moistureMeasured);
                if(!moistureMeasured.isEmpty()){
                    manMoistData.put(formatedUtTidTork,moistureMeasured);
                }


            }

             */




            //System.out.println("Inn formated: "+formatedInTidTork);
            //System.out.println("Ut formated: "+formatedUtTidTork+"\n");

            // Checks if intidtork or outtidtork is empty, if so they are ignored and not added to the list
            if (!formatedInTidTork.isEmpty() && !formatedUtTidTork.isEmpty()){
                // Adds the data to the dates map
                dates.put(formatedInTidTork,formatedUtTidTork);
            }
            //System.out.printf("%s\t\t\t%s\n",formatedInTidTork,formatedUtTidTork);
        }

        // Defining a treemap to sort the data incrementally
        NavigableMap<String, String> sortedFinalResults = new TreeMap<>(dates);

        System.out.println("\n");

        for (Map.Entry<String, String> entry : sortedFinalResults.entrySet()) {
            System.out.printf("Intid: \t%s\t\t\tUttid: \t%s\n",entry.getKey(),entry.getValue());
        }

        System.out.printf("Size of dates: %s\n\n", sortedFinalResults.size());


        System.out.println("manMoistData size: " + manMoistData.size());

        for (Map.Entry<String, String> entry : manMoistData.entrySet()) {
            HelpingFunctions.getManMoist().put(entry.getKey(), entry.getValue());
        }

        // Returns a treemap that sorts the dates incrementally
        return new TreeMap<>(sortedFinalResults);
    }


    public static void pushManMoisture(String moisture) throws Exception {
        // Sqlstatement
        final String sqlStatement =
                "INSERT INTO " + PROJECT_ID + "." + LOCATION_ID + "." + MAN_MOISTURE_TABLE + "(moisture) " +
                        "VALUES("+moisture+") ";

        System.out.println(sqlStatement);

        HelpingFunctions.createQueryJob(sqlStatement);
    }

    /**
     *  Retrieves data from current drying process
     *
     * @return a map consisting of Timestamp(date&time) and Kwh value
     * @throws Exception throws execution if anything is wrong
     */
    public static Map<String, Number> getCurrentDrying() throws Exception {

        // Initializing the data map to store the results
        Map<String, Number> data = new HashMap<>();
        // Initializing baseline
        int baseline = 0;

        // Sqlstatement
        final String sqlStatement = "SELECT `" + KWH_TIMESTAMP_NAME_PARAMETER + "`, `" + KWH_NAME_PARAMETER + "` " +
                "FROM `" + PROJECT_ID + "." + LOCATION_ID + "." + KWH_TABLE_NAME + "` " +
                "WHERE " + KWH_TIMESTAMP_NAME_PARAMETER + " BETWEEN " + '"' + START_TIME + '"' +
                " AND " + '"' + STOP_TIME + '"' +
                " AND " + KWH_VALUE_ID_NAME_PARAMETER + " = " + KWH_VALUE_ID_VALUE_PARAMETER + " " +
                " AND " + KWH_NAME_PARAMETER + " <> 0 " +
                " ORDER BY " + KWH_TIMESTAMP_NAME_PARAMETER + " ASC";

        System.out.println(sqlStatement);

        // Retrieves the results from the queryjob
        TableResult result = HelpingFunctions.createQueryJob(sqlStatement);

        //System.out.println("InTidTork\t\t\tUtTidTork");
        // Iterating through the results
        HelpingFunctions.iterateKwhValues(data, baseline, result, KWH_NAME_PARAMETER, KWH_TIMESTAMP_NAME_PARAMETER);
        return new TreeMap<>(data);
    }


}