Newer
Older
package com.application.DB;
import org.joda.time.DateTime;
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
/**
* Launching the functions
*
* @param args default param
* @throws Exception throws exception in case of error
*/
public static void main(String[] args) throws Exception {
Eilert Tunheim
committed
//getName();
//getZeroPointDate();
Eilert Tunheim
committed
System.out.println(setInputParameters());
Eilert Tunheim
committed
public static Map<Integer, Map<String, Number>> setInputParameters() throws Exception {
Map<Integer, Map<String, Number>> allDryingPeriods = new HashMap<>();
Eilert Tunheim
committed
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;
Eilert Tunheim
committed
String extraInputParameter = "";
Eilert Tunheim
committed
String treeSpecies = "";
String dimensions = "";
String sawset = "";
String moistureGoal = "";
String nameParameter;
String kilinName;
int kilinID;
String startDryingTime;
String stopDryingTime;
String valmeticsTableName;
int limit;
String kwhTableName;
String KwhName;
String timestamp;
String valueIDName;
int valueID;
Eilert Tunheim
committed
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 = "";
Eilert Tunheim
committed
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());
Eilert Tunheim
committed
//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();
Eilert Tunheim
committed
}
if (location.getKey() == 174) {
if (TREE_SPECIES.equalsIgnoreCase("Fura")) {
TREE_SPECIES = "Furu";
}
locationTables = getArjangTableNames();
valmeticsChamberIDS = getArjangValmeticsChamberIDS();
kwhChamberIDS = getArjangKwhChamberIDS();
Eilert Tunheim
committed
}
// 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"));
Eilert Tunheim
committed
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 + "\\\\%" + "%" + '"' + ") ";
Eilert Tunheim
committed
// 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);
Eilert Tunheim
committed
//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");
Eilert Tunheim
committed
System.out.printf("\nExtra parameters:\n %s\n\n", extraInputParameter);
Eilert Tunheim
committed
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) {
Eilert Tunheim
committed
//System.out.println("\nInni if, sletter parameter:\n");
if (extraInputParameter.contains(sawset) && !sawsetRemoved) {
extraInputParameter = extraInputParameter.replace(sawset, "");
System.out.println("Sawset is removed");
Eilert Tunheim
committed
sawsetRemoved = true;
//setInputParameters(true,false,false,false);
} else if (extraInputParameter.contains(treeSpecies) && !treespeciesRemoved) {
extraInputParameter = extraInputParameter.replace(treeSpecies, "");
System.out.println("Tree species is removed");
Eilert Tunheim
committed
treespeciesRemoved = true;
//setInputParameters(true,true,false,false);
} else if (extraInputParameter.contains(dimensions) && !dimensionsRemoved) {
extraInputParameter = extraInputParameter.replace(dimensions, "");
System.out.println("Dimensions is removed");
Eilert Tunheim
committed
dimensionsRemoved = true;
//setInputParameters(true,true,true,false);
} else if (extraInputParameter.contains(moistureGoal) && !moistureRemoved) {
extraInputParameter = extraInputParameter.replace(moistureGoal, "");
System.out.println("Moisture goal is removed");
Eilert Tunheim
committed
moistureRemoved = true;
//setInputParameters(true,true,true,true);
Eilert Tunheim
committed
} 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;
Eilert Tunheim
committed
* 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;
Eilert Tunheim
committed
Eilert Tunheim
committed
Map<Integer, Map<String, Number>> finalResults = new HashMap<>();
for (Map.Entry<String, String> entry : dates.entrySet()) {
Eilert Tunheim
committed
//System.out.printf("Intid: \t%s\t\t\tOuttid: \t%s\n",entry.getKey(),entry.getValue());
Eilert Tunheim
committed
// 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);
Eilert Tunheim
committed
// Iterating through the results
Eilert Tunheim
committed
TableResult result = HelpingFunctions.createQueryJob(sqlStatement);
//System.out.println("Timestamp \t kWh");
Eilert Tunheim
committed
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);
Eilert Tunheim
committed
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);
}
}
Eilert Tunheim
committed
}
System.out.println("\nFinal results size: " + finalResults.size()+"\n\n");
// Defining a treemap to sort the data incrementally
Eilert Tunheim
committed
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
}
}
Eilert Tunheim
committed
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());
Eilert Tunheim
committed
}
Eilert Tunheim
committed
return sortedFinalResults;
Eilert Tunheim
committed
/**
* 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.
Eilert Tunheim
committed
* 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 {
Eilert Tunheim
committed
// Defining variables to be used later
String treeSort = "";
String thickness = "";
String width = "";
String sawSet = "";
String moistureMeasured = "";
String formatedInTidTork = "";
String formatedUtTidTork = "";
String formatedUtTidTorkPlussDays = "";
Eilert Tunheim
committed
// Initializing maps to store the results
Eilert Tunheim
committed
Map<String, String> dates = new HashMap<>();
Eilert Tunheim
committed
Map<String, String> manMoistData = new HashMap<>();
Eilert Tunheim
committed
// Defining extra parameters if required
String extraInputParameter = "";
extraInputParameter += extraUserInput;
extraInputParameter += "AND CalculatedStart BETWEEN \"1990-01-01 00:00:00\" AND \"" + TODAYS_DATE + "\" ";
}
// KILIN_ID starts at 0 not 1 in the database.
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);
Eilert Tunheim
committed
Eilert Tunheim
committed
// Increments kilinID back to the correct id nr
if(locationID == 174){
kilinID += 1;
}
Eilert Tunheim
committed
Eilert Tunheim
committed
// Retrieves the results from the queryjob
Eilert Tunheim
committed
TableResult result = HelpingFunctions.createQueryJob(sqlStatement);
Eilert Tunheim
committed
//System.out.println("InTidTork\t\t\tUtTidTork");
Eilert Tunheim
committed
// Iterating through the results
Eilert Tunheim
committed
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";
}
Eilert Tunheim
committed
if (nameParameters[i].matches("\\d+x\\d+")) {
String[] dimensions = nameParameters[i].split("x");
Eilert Tunheim
committed
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());
Eilert Tunheim
committed
// 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
Eilert Tunheim
committed
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!!@@@@");
Eilert Tunheim
committed
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()))) {
Eilert Tunheim
committed
}
// 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
Eilert Tunheim
committed
formatedUtTidTork = HelpingFunctions.getDateFormat().format(utTidTorkLong);
} catch(NumberFormatException e) {
//not long value, must be of type string
Eilert Tunheim
committed
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();
Eilert Tunheim
committed
Eilert Tunheim
committed
// 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];
Eilert Tunheim
committed
if (locationID == ARJANG_LOCATION_ID) {
Eilert Tunheim
committed
// 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);
Eilert Tunheim
committed
// Retrieves the results from the queryjob
TableResult resultManMeasurement = HelpingFunctions.createQueryJob(manMeasurementStatement);
Eilert Tunheim
committed
//System.out.println("InTidTork\t\t\tUtTidTork");
// Iterating through the results
for (FieldValueList manMeasurement : resultManMeasurement.iterateAll()) {
Eilert Tunheim
committed
// 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);
}
Eilert Tunheim
committed
}
Eilert Tunheim
committed
//System.out.println("Inn formated: "+formatedInTidTork);
//System.out.println("Ut formated: "+formatedUtTidTork+"\n");
Eilert Tunheim
committed
// 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());
Eilert Tunheim
committed
System.out.printf("Size of dates: %s\n\n", sortedFinalResults.size());
Eilert Tunheim
committed
System.out.println("manMoistData size: " + manMoistData.size());
Eilert Tunheim
committed
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);
Eilert Tunheim
committed
}
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);
}
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
/**
* 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);
}