Select Git revision
editsalamander.py
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
DB.java 28.52 KiB
package com.application.DB;
import com.google.cloud.bigquery.*;
import org.joda.time.DateTime;
import java.util.*;
import static com.application.DB.Constants.*;
import static com.application.DB.HelpingFunctions.IS_FINISHED;
import static com.application.DB.HelpingFunctions.getNoOfChambers;
/**
* 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());
getNoOfChambers();
}
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;
getNoOfChambers();
int chamberNo = 1;
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 (HelpingFunctions.TREE_SPECIES.equalsIgnoreCase("Furu")) {
HelpingFunctions.TREE_SPECIES = "Fura";
}
locationTables = getValasenTableNames();
valmeticsChamberIDS = getValasenValmeticsChamberIDS();
kwhChamberIDS = getValasenKwhChamberIDS();
}
if (location.getKey() == 174) {
if (HelpingFunctions.TREE_SPECIES.equalsIgnoreCase("Fura")) {
HelpingFunctions.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", HelpingFunctions.TREE_SPECIES);
System.out.printf("Width: \t\t\t%s\n", HelpingFunctions.DIMENSIONS);
System.out.printf("Sawset: \t\t%s\n", HelpingFunctions.SAWSET);
System.out.printf("Moisture: \t\t%s\n", HelpingFunctions.MOISTURE_GOAL);
// Defining the extra sql statements for extra parameters
treeSpecies = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + HelpingFunctions.TREE_SPECIES + "%" + '"' + ") "; dimensions = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + HelpingFunctions.DIMENSIONS + "%" + '"' + ") ";
sawset = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + HelpingFunctions.SAWSET + "%" + '"' + ") ";
moistureGoal = "AND LOWER(" + nameParameter + ") LIKE LOWER(" + '"' + "%" + HelpingFunctions.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 (!HelpingFunctions.TREE_SPECIES.isEmpty() && !extraInputParameter.contains(treeSpecies) && !treespeciesRemoved) {
extraInputParameter += treeSpecies;
}
if (!HelpingFunctions.DIMENSIONS.isEmpty() && !extraInputParameter.contains(dimensions) && !dimensionsRemoved) {
extraInputParameter += dimensions;
}
if (!HelpingFunctions.SAWSET.isEmpty() && !extraInputParameter.contains(sawset) && !sawsetRemoved) {
extraInputParameter += sawset;
}
if (!HelpingFunctions.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
assert valmeticsChamberIDS != null;
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<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 \"" + CURRENT_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 \"" + CURRENT_DATE + "\" " +
"AND "+stopDryingTime+" BETWEEN \"1990-01-01 00:00:00\" AND \"" + CURRENT_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 {
String startTime = null;
String stopTime = null;
String treeSpecies;
String dimensions;
String sawset;
String moistureGoal;
// Checks if variables is not null, if so, formats correctly for the sql statement
if(!HelpingFunctions.START_TIME.equals("")){
startTime = '"'+ HelpingFunctions.START_TIME+'"';
}
if(HelpingFunctions.STOP_TIME != null){
stopTime = '"'+ HelpingFunctions.STOP_TIME+'"';
}
if(!HelpingFunctions.TREE_SPECIES.equals("")){
treeSpecies = '"'+ HelpingFunctions.TREE_SPECIES+'"';
} else {
treeSpecies = null;
}
if(!HelpingFunctions.DIMENSIONS.equals("")){
dimensions = '"'+ HelpingFunctions.DIMENSIONS+'"';
} else {
dimensions = null;
}
if(!HelpingFunctions.SAWSET.equals("")){
sawset = '"'+ HelpingFunctions.SAWSET+'"';
} else {
sawset = null;
}
if(!HelpingFunctions.MOISTURE_GOAL.equals("")){
moistureGoal = '"'+ HelpingFunctions.MOISTURE_GOAL+'"';
} else {
moistureGoal = null;
}
// Sqlstatement
final String sqlStatement =
"INSERT INTO " + PROJECT_ID + "." + LOCATION_ID + "." + MAN_MOISTURE_TABLE + "(moisture,tree_species,dimensions,sawset,moisture_goal,no_moisture_check,start_time,stop_time,finished) " +
"VALUES("+moisture+","+treeSpecies+","+dimensions+","+sawset+","+moistureGoal+","+NUMBER_OF_CHECKS+","+startTime+","+stopTime+","+IS_FINISHED+") ";
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 " + '"' + HelpingFunctions.START_TIME + '"' +
" AND " + '"' + HelpingFunctions.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);
}
}