FINAL_FINAL_FINAL

import com.sun.org.apache.bcel.internal.generic.Select;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

@SuppressWarnings("Duplicates") //JUST TO AVOID DUPLICATE CONTENT MARKING - REMOVE THIS BEFORE HAND IN

public class waterwork {

    private static int menuItem = 0;
    private static boolean exit = false;
    private static boolean quit = false;
    private static int MaxCostumerID;
    private static int costumerID;
    private static double diff;
    private static double receivedamount;

    public static void main(String[] args) {

        //We start running a loop that will only exit if the user presses 0 - Otherwise the program will continue to run
        while (!exit) {
            do {
                System.out.println("***************** UPTOWN WATERWORK SYSTEM ****************");
                System.out.println("Welcome. Please choose your role in the company. Press:");
                System.out.println("1: for Admin");
                System.out.println("2: for Employee");
                System.out.println("0: to Quit the program");
                System.out.print("Choose your role: ");
                menuItem = validateIntInput(); // CHECKS IF THE INPUT IS INDEED AN VALID INT TYPE
                System.out.println(" ");
                System.out.println("**********************************************************");
                System.out.println(" ");
            } while (menuItem != 1 && menuItem != 2 && menuItem != 0); // WILL KEEP RUNNING UNTIL INPUT IS 1, 2 or 0
            if (menuItem == 1) {
                adminLogIn(); // KØRER ADMIN DELEN AF PROGRAMMET
            } else if (menuItem == 2) {
                employeeLogIn(); // KØRER EMPLOYEE DELEN AF PROGRAMMET
            } else {
                exit = true; // AFSLUTTER PROGRAMMET
            }
        }
    }

    private static void adminLogIn() {
        Scanner scanner = new Scanner(System.in);

        String username = "Admin"; // OUR HARDCODED ADMIN USERNAME - SHOULD IDEALLY NOT BE LIKE THIS BUT TIME...
        String password = "Admin123"; // OUR HARDCODED PASSWORD USERNAME - SHOULD IDEALLY NOT BE LIKE THIS BUT TIME...

        System.out.println("********************** ADMIN LOGIN ***********************");
        System.out.println(" ");

        for (int i = 0; i < 3; i++) {
            System.out.println("Please enter your username:");
            String usernameInput = scanner.next(); // LET'S THE USER TYPE IN A USERNAME

            System.out.println("Please confirm your password:");
            String passwordInput = scanner.next(); // LET'S THE USER TYPE IN A PASSWORD

            if (username.equals(usernameInput) && password.equals(passwordInput)) // IF USERNAME & PASSWORD IS CORRECT, ACCESS IS GRANTED.
            {
                System.out.println("You're logged in");
                adminInterface();
                break;
            } else { // ALLOWS THE USER TWO MORE TRIES TO GET IT RIGHT
                if (i < 2) {
                    System.out.println("Log-in invalid. You have " + (2 - i) + " tries left");
                } else {
                    System.out.println("You have no more tries left"); // IF USERNAME OR PASSWORD WAS WRONG, ACCESS IS NOT GRANTED.
                }
            }
        }
        System.out.println(" ");
    }

    private static void adminInterface() {
        String employees;
        String username;
        String password;
        int menuItem;

        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED
        do {
            System.out.println(" ");
            System.out.println("********************** ADMIN INTERFACE *********************");
            System.out.println(" ");
            System.out.println("1: Add Employee");
            System.out.println("2: Delete Employee:");
            System.out.println("0. Go back to Log In Screen");
            System.out.print("Choose menu number: ");
            menuItem = validateIntInput(); // CHECKS IF THE INPUT IS INDEED AN VALID INT TYPE
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");

            Scanner name = new Scanner(System.in);
            Scanner password1 = new Scanner(System.in);

            switch (menuItem) {
                case 1:
                    System.out.println("Enter employees name");
                    employees = name.next();
                    System.out.println("Enter a Password");
                    password = password1.next();
                    DB.insertSQL("INSERT INTO tblLogin (fldUsername , fldPassword) VALUES  ('" + employees + "' , '" + password + "')");
                    //CODE THAT WILL ADD EMPLOYEE FROM DATABASE
                    break;

                case 2:
                    System.out.println("Enter the username of the employee you want to delete");
                    username = name.next();
                    System.out.println("user: '" + username + "' will now be deleted");
                    boolean ok = false;
                    ok = DB.deleteSQL("DELETE FROM tblLogin WHERE fldUsername = '" + username + "'");
                    if (ok) {
                        System.out.println("The employee is now deleted from the system");
                    } else {
                        System.out.println("Error. Please try again - User might not have been in the system.");
                    }
                    //CODE THAT WILL DELETE EMPLOYEE FROM DATABASE
                    break;

                case 0:
                    quit = true; // BREAKS THE LOOP WHICH LEADS US BACK ONE-STEP IN THE MENU
                    System.out.println("Going back!");
                    break;

                default: // INVALID INPUTS RECEIVES THIS MESSAGE
                    System.out.println("Invalid choice. Try again:");
            }
        } while (!quit);
    }

    private static void employeeLogIn() {
        Scanner scanner = new Scanner(System.in);

        System.out.println("********************* EMPLOYEE LOGIN *********************");

        for (int i = 0; i < 3; i++) {

            System.out.println("Please enter your username:");
            String usernameInput = scanner.next();

            DB.selectSQL("SELECT fldUsername from tblLogin WHERE fldUsername='" + usernameInput + "'");
            String dbUsername = DB.getData();
            //System.out.println(dbUsername);// FOR US TO CHECK THE Username

            DB.selectSQL("SELECT fldPassword FROM tblLogin WHERE fldUsername ='" + dbUsername + "'");
            String dbPassword = DB.getData();
            //System.out.println(dbPassword);// FOR US TO CHECK THE PASSWORD

            if (usernameInput.equals(dbUsername)) {
                for (int j = 0; j < 3; j++) {
                    System.out.println("Please enter your password:");
                    String passwordInput = scanner.next();

                    if (passwordInput.equals(dbPassword)) {
                        System.out.println("You're logged in");
                        employeeInterface();
                        break;
                    } else {
                        if (i < 2) {
                            System.out.println("Log-in invalid. You have " + (2 - j) + " tries left");
                        } else {
                            System.out.println("You have no more tries left"); // IF USERNAME OR PASSWORD WAS WRONG, ACCESS IS NOT GRANTED.
                        }
                    }
                }
            } else { // ALLOWS THE USER TWO MORE TRIES TO GET IT RIGHT
                if (i < 2) {
                    System.out.println("Log-in invalid. You have " + (2 - i) + " tries left");
                } else {
                    System.out.println("You have no more tries left"); // IF USERNAME OR PASSWORD WAS WRONG, ACCESS IS NOT GRANTED.
                }
            }
        }
        System.out.println(" ");
    }

    private static void employeeInterface() {
        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED
        do {
            System.out.println(" ");
            System.out.println("********************** EMPLOYEE MENU *********************");
            System.out.println(" ");
            System.out.println("1: Manage customers");
            System.out.println("2: Pull Statistics");
            System.out.println("0. Go back to Log In Screen");
            System.out.print("Choose menu number: ");
            menuItem = validateIntInput(); // CHECKS IF THE INPUT IS INDEED AN VALID INT TYPE
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");
            switch (menuItem) {
                case 1:
                    employeeMenuManageCustomerBilling(); // HANDLES CUSTOMER BILLING MODULE
                    break;
                case 2:
                    employeeMenuExtractStatistics(); // HANDLES STATISTICS
                    break;
                case 0:
                    quit = true; // BREAKS THE LOOP WHICH LEADS US BACK ONE-STEP IN THE MENU
                    System.out.println("Going back!");
                    break;
                default: // INVALID INPUTS RECEIVES THIS MESSAGE
                    System.out.println("Invalid choice. Try again:");
            }
        } while (!quit);
    }

    private static void employeeMenuManageCustomerBilling() {
        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED
        do {
            System.out.println("********************** BILLING MENU **********************");
            System.out.println(" ");
            System.out.println("Where do you want to navigate?");
            System.out.println(" ");
            System.out.println("1: Billing Settings");
            System.out.println("2: Enter Water Measurements");
            System.out.println("3: Create Giro Cards");
            System.out.println("4: Register Cash Payment");
            System.out.println("5: Create Missed Payment Reminder");
            System.out.println("6: Shut Off Water Supply");
            System.out.println("7: Customer Management");
            System.out.println("8: Import csv.file");
            System.out.println("9: Check daily payment");
            System.out.println("10: Accumulated Tax Overview");
            System.out.println("0. Navigate back one step");
            System.out.print("Choose menu number: ");
            menuItem = validateIntInput();
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");
            switch (menuItem) {
                case 1:
                    billingSettings();
                    break;
                case 2:
                    waterMeasurementInput();
                    break;
                case 3:
                    createGiroCardFiles();
                    break;
                case 4:
                    registerCashPayment();
                    break;
                case 5:
                    //createMissingPaymentReminder();
                    System.out.println("System under developement");
                    break;
                case 6:
                    //shutOffWaterSupply();
                    System.out.println("System under developement");
                    break;
                case 7:
                    customerManagement();
                    break;
                case 8:
                    //importCsvFile();
                    System.out.println("System under developement");
                    break;
                case 9:
                    //dailyPaymentOverview();
                    System.out.println("System under developement");
                    break;
                case 10:
                    //accumulatedTaxOverview();
                    System.out.println("System under developement");
                    break;
                case 0:
                    quit = true;
                    System.out.println("Going back one step!");
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }


    private static void employeeMenuExtractStatistics() {
        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED
        do {
            System.out.println("********************* STATISTICS MENU ********************");
            System.out.println(" ");
            System.out.println("Where do you want to navigate?");
            System.out.println(" ");
            System.out.println("1: Extract statistic 1");
            System.out.println("2: Extract statistic 2");
            System.out.println("3: Extract statistic 3");
            System.out.println("0. Go back one step");
            System.out.print("Choose menu number: ");
            menuItem = validateIntInput();
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");
            switch (menuItem) {
                case 1:
                    extractStatisticOne();
                    break;
                case 2:
                    //extractStatisticTwo();
                    System.out.println("System under developement");
                    break;
                case 3:
                    //extractStatisticThree();
                    System.out.println("System under developement");
                    break;
                case 0:
                    // Quit program
                    quit = true;
                    System.out.println("Going back one step!");
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void billingSettings() {
        boolean quit = false;
        int settingID = 0;

        Scanner input = new Scanner(System.in);


        System.out.println("Billing Settings");
        System.out.println("Would you like to make changes in billing settings?");

        //Select and display billing data from sql payment table
        DB.selectSQL("SELECT * FROM tblSettings");


        do {

            int menuItem; //This will save your menu choice

            System.out.println("/////////////////////////////////////////");
            System.out.println("Press '1' for: Yes");
            System.out.println("Press '2' for No");
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput(); //Keep this to validate menu
            System.out.println("/////////////////////////////////////////");

            switch (menuItem) {
                case 1:

                    do {

                        System.out.println("/////////////////////////////////////////");
                        System.out.print("Please choose which setting would you like to change:");
                        System.out.println();
                        System.out.println("Press '1' for: Manual Fee Rate Industrial");
                        System.out.println("Press '2' for: Manual Fee Rate Private");
                        System.out.println("Press '3' for: Manual Fee Rate Agricultural");
                        System.out.println("Press '4' for: Water Tax Rate Industrial");
                        System.out.println("Press '5' for: Water Tax Rate Agricultural");
                        System.out.println("Press '6' for: Water Tax Rate Private");
                        System.out.println("Press '7' for: Drainage Tax Rate Private");
                        System.out.println("Press '8' for: Drainage Tax Rate Agricultural");
                        System.out.println("Press '9' for: Drainage Tax Rate Industrial");
                        System.out.println("Press '10' for: Basic Water Price");
                        System.out.println("Press '11' for: Go back to main menu");


                        menuItem = validateIntInput(); //Keep this to validate menu
                        System.out.println("/////////////////////////////////////////");

                        switch (menuItem) {
                            case 1:
                                System.out.println("Please input the new value:");
                                double manualFeeRateIndustrial = input.nextDouble();
                                //Update data in Manual Fee Rate Industrial field
                                DB.updateSQL("UPDATE tblSettings SET fldManualFeeRateIndustrial=" + manualFeeRateIndustrial + "");
                                System.out.println("Manual Fee Rate Industrial has been changed to " + manualFeeRateIndustrial);
                                break;

                            case 2:
                                System.out.println("Please input the new value:");//Enter the code that needs to run if 1 was the option choosed.
                                double manualFeeRatePrivate = input.nextDouble();
                                //Update data in Manual Fee Rate Private field
                                DB.updateSQL("UPDATE tblSettings SET fldManualFeeRatePrivate= " + manualFeeRatePrivate + "");
                                break;

                            case 3:
                                System.out.println("Please input the new value:");
                                double manualFeeRateAgriculture = input.nextDouble();
                                //Update data in Manual Fee Rate Agriculture field
                                DB.updateSQL("UPDATE tblSettings SET fldManualFeeRateAgriculture= " + manualFeeRateAgriculture + "");
                                break;

                            case 4:
                                System.out.println("Please input the new value:");
                                double waterTaxRateIndustrial = input.nextDouble();
                                //Update data in Water Tax Rate Industrial field
                                DB.updateSQL("UPDATE tblSettings SET fldWaterTaxRateIndustrial= " + waterTaxRateIndustrial + "");
                                break;

                            case 5:
                                System.out.println("Please input the new value:");
                                double waterTaxRateAgriculture = input.nextDouble();
                                //Update data in Water Tax Rate Agriculture field
                                DB.updateSQL("UPDATE tblSettings SET fldWaterTaxRateAgriculture= " + waterTaxRateAgriculture + "");
                                break;

                            case 6:
                                System.out.println("Please input the new value:");
                                double waterTaxRatePrivate = input.nextDouble();
                                //Update data in Water Tax Rate Private field
                                DB.updateSQL("UPDATE tblSettings SET fldWaterTaxRatePrivate= " + waterTaxRatePrivate + "");
                                break;

                            case 7:
                                System.out.println("Please input the new value:");
                                double drainageTaxRateAgriculture = input.nextDouble();
                                //Update data in Drainage Tax Rate Agriculture field
                                DB.updateSQL("UPDATE tblSettings SET fldDrainageTaxRateAgriculture= " + drainageTaxRateAgriculture + "");
                                break;

                            case 8:
                                System.out.println("Please input the new value:");
                                double drainageTaxRatePrivate = input.nextDouble();
                                //Update data in Drainage Tax Rate Private field
                                DB.updateSQL("UPDATE tblSettings SET fldDrainageTaxRatePrivate= " + drainageTaxRatePrivate + "");
                                break;

                            case 9:
                                System.out.println("Please input the new value:");
                                double drainageTaxRateIndustrial = input.nextDouble();
                                //Update data in Drainage Tax Rate Industrial field
                                DB.updateSQL("UPDATE tblSettings SET fldDrainageTaxRateIndustrial= " + drainageTaxRateIndustrial + "");
                                break;

                            case 10:
                                System.out.println("Please input the new value:");
                                double basicWaterPrice = input.nextDouble();
                                //Update data in Basic Water Price field
                                DB.updateSQL("UPDATE tblSettings SET fldBasicWaterPrice= " + basicWaterPrice + "");
                                break;

                            case 11:
                                quit = true; //This will make the loop stop after running the program - Used when user want to go back
                                break;
                            default:
                                System.out.println("Invalid choice. Try again: ");
                        }

                    } while (!quit);
                    break;

                case 2:
                    //Enter the code that needs to run if 2 was the option choosed.
                    quit = true; //This will make the loop stop after running the program - Used when user want to go back
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }

        } while (!quit);
    }

    private static void waterMeasurementInput() {
        //boolean quit = false; //This will make the loop run until you want it to quit
        int menuItem; //This will save your menu choice

        do {
            System.out.println("/////////////////////////////////////////");
            System.out.println("Do you want to input a new water meter level? ");
            System.out.println("Press '1' for: Yes"); //Change these depending on the number of chioces
            System.out.println("Press '2' for No"); //Change these depending on the number of chioces
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput(); //Keep this to validate menu
            System.out.println("/////////////////////////////////////////");
            switch (menuItem) {
                case 1:

                    double newWaterMeterLevel = 0;
                    boolean done = false;


                    //CostumerID input
                    while (!done) {
                        Scanner in = new Scanner(System.in);
                        getMaxID();

                        System.out.println("Input costumer ID:");
                        if (in.hasNextInt()) {
                            costumerID = in.nextInt();

                            if (costumerID > 0 && costumerID < MaxCostumerID) {
                                DB.selectSQL("select fldCustomerName from tblCustomer where fldCustomerID = '" + costumerID + "'");
                                String customerName = DB.getData();
                                System.out.println(customerName);
                                String StopValue = DB.getData();
                                done = true;
                            } else {
                                System.out.println("Invalid input");
                            }
                        } else {
                            System.out.println("Invalid input");
                        }
                    }
                    done = false;
                    ////////////////////////////////////

                    //Input Waterlevel
                    while (!done) {
                        Scanner in = new Scanner(System.in);

                        System.out.println("input new water meter level: ");
                        if (in.hasNextDouble()) {
                            newWaterMeterLevel = in.nextDouble();

                            if (newWaterMeterLevel > 0) {

                                done = true;
                            } else {
                                System.out.println("Invalid input");
                            }
                        } else {
                            System.out.println("Invalid input");
                        }
                    }
                    //Update fldWaterRadingThisTime
                    DB.updateSQL("UPDATE tblWaterMeter SET fldWaterReadingThisTime = '" + newWaterMeterLevel + "' WHERE fldCustomerID = '" + costumerID + "';");

                    System.out.printf("CostumerID: %d \nNew water meter level: %.2f\n", costumerID, newWaterMeterLevel);

                    //Update fldAmountPaid
                    DB.updateSQL("UPDATE tblPayment SET fldAmountPaid=0 WHERE fldWaterMeterID='" + FindWaterMeterID() + "'");
                    break;
                case 2:
                    //Enter the code that needs to run if 2 was the option choosed.
                    quit = true; //This will make the loop stop after running the program - Used when user want to go back
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void createGiroCardFiles() {

        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED

        DB.selectSQL("SELECT COUNT(fldCustomerID) FROM tblWaterMeter WHERE fldWaterReadingThisTime>0");
        String dbNumberOfCustomers = DB.getData();
        //String stopValue3 = DB.getData();
        int numberOfCustomers = Integer.parseInt(dbNumberOfCustomers);
        //System.out.println(numberOfCustomers);

        DB.selectSQL("SELECT fldBasicWaterPrice FROM tblSettings");
        String dbBasicWaterPrice = DB.getData();
        //String stopValue4 = DB.getData();
        double basicWaterPrice = Double.parseDouble(dbBasicWaterPrice);
        //System.out.println(basicWaterPrice);

        DB.selectSQL("SELECT fldWaterTaxRatePrivate FROM tblSettings");
        String dbWaterTaxPrivate = DB.getData();
        //String stopValue5 = DB.getData();
        double waterTaxPrivate = Double.parseDouble(dbWaterTaxPrivate);
        //System.out.println(waterTaxPrivate);

        DB.selectSQL("SELECT fldWaterTaxRateAgriculture FROM tblSettings");
        String dbWaterTaxAgriculture = DB.getData();
        //String stopValue6 = DB.getData();
        double waterTaxAgriculture = Double.parseDouble(dbWaterTaxAgriculture);
        //System.out.println(waterTaxAgriculture);

        DB.selectSQL("SELECT fldWaterTaxRateIndustrial FROM tblSettings");
        String dbWaterTaxIndustrial = DB.getData();
        //String stopValue7 = DB.getData();
        double waterTaxIndustrial = Double.parseDouble(dbWaterTaxIndustrial);
        //System.out.println(waterTaxIndustrial);

        DB.selectSQL("SELECT fldDrainageTaxRatePrivate FROM tblSettings");
        String dbDrainageTaxPrivate = DB.getData();
        //String stopValue8 = DB.getData();
        double drainageTaxPrivate = Double.parseDouble(dbDrainageTaxPrivate);
        //System.out.println(drainageTaxPrivate);

        DB.selectSQL("SELECT fldDrainageTaxRateAgriculture FROM tblSettings");
        String dbDrainageTaxAgriculture = DB.getData();
        //String stopValue9 = DB.getData();
        double drainageTaxAgriculture = Double.parseDouble(dbDrainageTaxAgriculture);
        //System.out.println(drainageTaxAgriculture);

        DB.selectSQL("SELECT fldDrainageTaxRateIndustrial FROM tblSettings");
        String dbDrainageTaxIndustrial = DB.getData();
        //String stopValue10 = DB.getData();
        double drainageTaxIndustrial = Double.parseDouble(dbDrainageTaxIndustrial);
        //System.out.println(drainageTaxIndustrial);

        DB.selectSQL("SELECT fldManualFeeRatePrivate FROM tblSettings");
        String dbManualWaterReadingFeePrivate = DB.getData();
        //String stopValue11 = DB.getData();
        double manualWaterReadingFeePrivate = Double.parseDouble(dbManualWaterReadingFeePrivate);
        //System.out.println(manualWaterReadingFeePrivate);

        DB.selectSQL("SELECT fldManualFeeRateAgriculture FROM tblSettings");
        String dbManualWaterReadingFeeAgriculture = DB.getData();
        //String stopValue12 = DB.getData();
        double manualWaterReadingFeeAgriculture = Double.parseDouble(dbManualWaterReadingFeeAgriculture);
        //System.out.println(dbManualWaterReadingFeeAgriculture);

        DB.selectSQL("SELECT fldManualFeeRateIndustrial FROM tblSettings");
        String dbManualWaterReadingFeeIndustrial = DB.getData();
        //String stopValue13 = DB.getData();
        double manualWaterReadingFeeIndustrial = Double.parseDouble(dbManualWaterReadingFeeIndustrial);
        //System.out.println(manualWaterReadingFeeIndustrial);

        double amountDue = 0; //IS ONLY INITIALIZED - VALUE WILL CHANGE IN THE LOOP

        do {

            System.out.println(" ");
            System.out.println("****************** CREATE GIRO CARDS *********************");
            System.out.println(" ");
            System.out.println("Press '1' for: Yes");
            System.out.println("Press '2' for No");
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput();
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");
            switch (menuItem) {
                case 1:
                    for (int i = 0; i < numberOfCustomers; i++) {
                        //System.out.println(numberOfCustomers + i); //6
                        DB.selectSQL("SELECT fldCustomerID FROM tblWaterMeter WHERE fldWaterReadingThisTime>0");
                        String dbCustomerID = DB.getData();
                        //String stopValue14 = DB.getData();
                        int customerID = Integer.parseInt(dbCustomerID);
                        //System.out.println(customerID); //1

                        DB.selectSQL("SELECT fldSegment FROM tblCustomer WHERE fldCustomerID=" + customerID );
                        String dbCustomerSegment = DB.getData();
                        //String stopValue15 = DB.getData();
                        int customerSegment = Integer.parseInt(dbCustomerSegment);
                        //System.out.println(customerSegment); //1

                        DB.selectSQL("SELECT fldManuelFee FROM tblWaterMeter WHERE fldCustomerID=" + customerID);
                        String dbManuelWaterReading = DB.getData();
                        //String stopValue16 = DB.getData();
                        int manualWaterReading = Integer.parseInt(dbManuelWaterReading);
                        //System.out.println(manualWaterReading); //0

                        DB.selectSQL("SELECT fldWaterReadingLastTime FROM tblWaterMeter WHERE fldCustomerID=" + customerID );
                        String dbWaterReadingLastTime = DB.getData();
                        //String stopValue17 = DB.getData();
                        double waterReadingLastTime = Double.parseDouble(dbWaterReadingLastTime);
                        //System.out.println(waterReadingLastTime); //4569.0

                        DB.selectSQL("SELECT fldWaterReadingThisTime FROM tblWaterMeter WHERE fldCustomerID=" + customerID);
                        String dbWaterReadingThisTime = DB.getData();
                        String stopValue18 = DB.getData();
                        double waterReadingThisTime = Double.parseDouble(dbWaterReadingThisTime);
                        //System.out.println(waterReadingThisTime); //5000.0

                        double waterUsage = waterReadingThisTime - waterReadingLastTime;
                        //System.out.println(waterUsage); //431.0

                        DB.selectSQL("SELECT fldPaymentID from tblPayment WHERE fldWaterMeterID = (SELECT fldCustomerID from tblCustomer WHERE fldCustomerID = " + customerID);
                        String dbPaymentIDString = DB.getData();
                        String dbPaymentIDString1 = DB.getData();
                        int paymentID = Integer.parseInt(dbPaymentIDString);
                        //System.out.println(paymentID); //1

                        boolean ok = false;
                        ok = DB.updateSQL("UPDATE tblWaterMeter SET fldWaterUsageDifference='" + waterUsage + "' WHERE fldWaterMeterID = (SELECT fldWaterMeterID FROM tblWaterMeter WHERE fldWaterMeterID ='" + paymentID + "'");
                        System.out.println(ok);

                        if (customerSegment == 1 && manualWaterReading == 0) { //PRICE WILL CHANGE DEPENDING ON SEGMENT AND IF THEY'VE CHECKED THE WATER METER THEMSELVES
                            amountDue = waterUsage * basicWaterPrice * waterTaxPrivate + waterUsage * basicWaterPrice * drainageTaxPrivate;
                            //System.out.println("if else 1");
                        } else if (customerSegment == 2 && manualWaterReading == 0) {
                            amountDue = waterUsage * basicWaterPrice * waterTaxAgriculture + waterUsage * basicWaterPrice * drainageTaxAgriculture;
                            //System.out.println("if else 2");
                        } else if (customerSegment == 3 && manualWaterReading == 0) {
                            amountDue = waterUsage * basicWaterPrice * waterTaxIndustrial + waterUsage * basicWaterPrice * drainageTaxIndustrial;
                            //System.out.println("if else 3");
                        } else if (customerSegment == 1 && manualWaterReading == 1) {
                            amountDue = waterUsage * basicWaterPrice * waterTaxPrivate + waterUsage * basicWaterPrice * drainageTaxPrivate + manualWaterReadingFeePrivate;
                            //System.out.println("if else 4");
                        } else if (customerSegment == 2 && manualWaterReading == 1) {
                            amountDue = waterUsage * basicWaterPrice * waterTaxAgriculture + waterUsage * basicWaterPrice * drainageTaxAgriculture + manualWaterReadingFeeAgriculture;
                            //System.out.println("if else 1");
                        } else if (customerSegment == 3 && manualWaterReading == 1) {
                            amountDue = waterUsage * basicWaterPrice * waterTaxIndustrial + waterUsage * basicWaterPrice * drainageTaxIndustrial + manualWaterReadingFeeIndustrial;
                            //System.out.println("if else 1");
                        } else {
                            System.out.println("Error");
                        }

                        //THIS WILL UPDATE THE AMOUNT DUE FOR EACH CUSTOMER IN THE LOOP
                        boolean ok2 = false;
                        ok2 = DB.updateSQL("UPDATE tblPayment SET fldAmountDue='" + amountDue + "' WHERE fldPaymentID ='" + paymentID + "'");
                        System.out.println(ok2); //true

                        double basicWaterPriceAmount = waterUsage * basicWaterPrice;
                        //System.out.println(basicWaterPriceAmount); //1293.0
                        double waterTaxAmountPrivate = waterUsage * waterTaxPrivate;
                        //System.out.println(waterTaxAmountPrivate); //129.299999998
                        double waterTaxAmountAgriculture = waterUsage * waterTaxAgriculture;
                        //System.out.println(waterTaxAmountAgriculture); // 150.85
                        double waterTaxAmountIndustrial = waterUsage * waterTaxIndustrial;
                        //System.out.println(waterTaxAmountIndustrial); //172.4
                        double drainageTaxAmountPrivate = waterUsage * drainageTaxPrivate;
                        //System.out.println(drainageTaxAmountPrivate);//172.4
                        double drainageTaxAmountAgriculture = waterUsage * drainageTaxAgriculture;
                        //System.out.println(drainageTaxAmountAgriculture); //193.9500000000002
                        double drainageTaxAmountIndustrial = waterUsage * drainageTaxIndustrial;
                        //System.out.println(drainageTaxAmountIndustrial); //215.5

                        String filename = Integer.toString(customerID);
                        //System.out.println(filename); //1

                        DB.selectSQL("SELECT fldCustomerName FROM tblCustomer WHERE fldCustomerID='" + customerID + "'");
                        String customerName = DB.getData();
                        //String stopValue21 = DB.getData();
                        //System.out.println(customerName); // Bob Hansen

                        DB.selectSQL("SELECT fldAddress FROM tblCustomer WHERE fldCustomerID='" + customerID + "'");
                        String address = DB.getData();
                        String address2 = DB.getData();
                        //String stopValue22 = DB.getData();
                        //System.out.println(address); // Havneborg 1

                        String waterReadingLastTimeString = Double.toString(waterReadingLastTime);
                        String waterReadingThisTimeString = Double.toString(waterReadingThisTime);
                        String waterUsageString = Double.toString(waterUsage);
                        String basicWaterPriceAmountString = Double.toString(basicWaterPriceAmount);
                        String waterTaxAmountPrivateString = Double.toString(waterTaxAmountPrivate);
                        String waterTaxAmountAgricultureString = Double.toString(waterTaxAmountAgriculture);
                        String waterTaxAmountIndustrialString = Double.toString(waterTaxAmountIndustrial);
                        String drainageTaxAmountPrivateString = Double.toString(drainageTaxAmountPrivate);
                        String drainageTaxAmountAgricultureString = Double.toString(drainageTaxAmountAgriculture);
                        String drainageTaxAmountIndustrialString = Double.toString(drainageTaxAmountIndustrial);
                        String amountDueString = Double.toString(amountDue);
                        String manuelReadingFeePrivateString = Double.toString(manualWaterReadingFeePrivate);
                        String manuelReadingFeeAgricultureString = Double.toString(manualWaterReadingFeeAgriculture);
                        String manuelReadingFeeIndustrialString = Double.toString(manualWaterReadingFeeIndustrial);
                        String accountNumber = "Reg nr: 4578 - Account Number: 0004578988"; //IDEALLY THIS WOULD HAVE BEEN A SETTING SO IT COULD HAVE BEEN CHANGED LATER ON - TIME THOUGH.

                        // ADDS A DEADLINE 14 DAYS FROM TODAY
                        int deadlineDifferenceFromTodaysDate = 14;
                        Calendar cal = Calendar.getInstance();
                        cal.add(Calendar.DAY_OF_YEAR, deadlineDifferenceFromTodaysDate);
                        Date date = cal.getTime();
                        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                        String deadlineDate = df.format(date);

                        // ADDS TODAY's DATE
                        Calendar cal1 = Calendar.getInstance();
                        Date datesDate = cal1.getTime();
                        DateFormat datesDateFormat = new SimpleDateFormat("dd/MM/yyyy");
                        String todaysDate = datesDateFormat.format(datesDate);

                        final String FILELOCATION = "F:\\waterwork giro cards\\" + filename + ".doc"; //This is where we store giro-card files
                        try (BufferedWriter bw = new BufferedWriter(new FileWriter(FILELOCATION))) {

                            bw.write("\t" + customerName + "\n");
                            bw.write("\t" + address + "\n");
                            bw.write("\t" + todaysDate + "\n");
                            bw.newLine();
                            bw.newLine();
                            bw.write("Consumption\n");
                            bw.write("Water reading last time: " + waterReadingLastTimeString + " m3\n");
                            bw.write("Water reading this time: " + waterReadingThisTimeString + " m3\n");
                            bw.write("Water used in m3:        " + waterUsageString + " m3\n");
                            bw.newLine();
                            bw.write("Price breakdown\n");
                            if (customerSegment == 1 && manualWaterReading == 0) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountPrivateString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountPrivateString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            } else if (customerSegment == 2 && manualWaterReading == 0) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountAgricultureString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountAgricultureString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            } else if (customerSegment == 3 && manualWaterReading == 0) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountIndustrialString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountIndustrialString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            } else if (customerSegment == 1 && manualWaterReading == 1) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountPrivateString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountPrivateString + ",- incl. VAT\n");
                                bw.write("Manual Reading Fee:      " + manuelReadingFeePrivateString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            } else if (customerSegment == 2 && manualWaterReading == 1) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountAgricultureString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountAgricultureString + ",- incl. VAT\n");
                                bw.write("Manual Reading Fee:      " + manuelReadingFeeAgricultureString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            } else if (customerSegment == 3 && manualWaterReading == 1) {
                                bw.write("Base Water Price Total:  " + basicWaterPriceAmountString + ",- incl. VAT\n");
                                bw.write("Water Tax Total:         " + waterTaxAmountIndustrialString + ",- incl. VAT\n");
                                bw.write("Drainage Tax Total:      " + drainageTaxAmountIndustrialString + ",- incl. VAT\n");
                                bw.write("Manual Reading Fee:      " + manuelReadingFeeIndustrialString + ",- incl. VAT\n");
                                bw.newLine();
                                bw.write("Total Amount Due:        " + amountDueString + ",- incl. VAT\n");
                            }
                            bw.newLine();
                            bw.write("Payment is due before the:   " + deadlineDate + "\n");
                            bw.write("Please use this Payment ID when transferring the money:   " + dbPaymentIDString + "\n");
                            bw.write("Account number:   " + accountNumber + "\n");
                            bw.newLine();
                            bw.write("Uptown WaterWork - Waterwork Road 1, 6400 Sønderborg - CVR: 12345678");

                            /** TEST **/
                            //System.out.println("Giro Card for CustomerID: " + customerID + " was saved correctly"); // This lets the employee see that it was successful.
                            //System.out.println("WaterReadingThisTime: " + waterReadingThisTimeString);
                            //System.out.println("CustomerID: " + customerID);
                            //System.out.println("AmountDue: " + amountDueString);
                            //System.out.println("PaymentID: " + paymentID);
                            //System.out.println(basicWaterPriceAmountString);
                            //System.out.println(waterTaxAmountPrivateString);
                            //System.out.println(drainageTaxAmountPrivateString);

                            boolean ok3 = false;
                            ok3 = DB.updateSQL("UPDATE tblWaterMeter SET fldWaterReadingLastTime='" + waterReadingThisTimeString + "' WHERE fldCustomerID ='" + customerID + "'");
                            System.out.println(ok3);
                            boolean ok4 = false;
                            ok4 = DB.updateSQL("UPDATE tblWaterMeter SET fldWaterReadingThisTime = 0 WHERE fldCustomerID ='" + customerID + "'");
                            System.out.println(ok4);
                        } catch (IOException e) { //This is an error message (debugging safety) that will give a notice if something fails.

                            e.printStackTrace();

                        }
                    }
                    System.out.println("All Giro-cards have been created.");
                    quit = true; //Stops the loop - Task finished
                    break;
                case 2:
                    quit = true; //Goes back to previous menu.
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void registerCashPayment() {

        do {
            //boolean quit = false; //This will make the loop run until you want it to quit
            int menuItem; //This will save your menu choice

            System.out.println("/////////////////////////////////////////");
            System.out.println("Do you want to input a Manual payment? ");
            System.out.println("Press '1' for: Yes"); //Change these depending on the number of chioces
            System.out.println("Press '2' for No"); //Change these depending on the number of chioces
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput(); //Keep this to validate menu
            System.out.println("/////////////////////////////////////////");
            switch (menuItem) {
                case 1:
                    boolean IDDone = false;


                    while (!IDDone)
                        if (CostumerIDtjeck()) {
                            DB.selectSQL("select fldCustomerName from tblCustomer where fldCustomerID = '"+costumerID+"'");
                            String customerName = DB.getData();
                            System.out.println(customerName);
                            String StopValue = DB.getData();
                            System.out.println("CostumerID: " + costumerID);
                            IDDone = true;
                        } else {
                            System.out.println("Invalid input\n");
                        }

                    int diffInt = paymentjeck();
                    if (diffInt == 0) {
                        System.out.println("The amount due is now payed");
                        DB.updateSQL("UPDATE tblPayment SET fldAmountDue=0, fldPaymentType='Cash',fldAmountPaid='"+ receivedamount+"' WHERE fldWaterMeterID='"+FindWaterMeterID()+"'");


                    }
                    if (diffInt == 1) {
                        System.out.println("The costumer payed to much");
                        double payBack = Math.abs(diff);
                        System.out.println("The costumer should have " + payBack + " kr. back");
                        DB.updateSQL("UPDATE tblPayment SET fldAmountDue=0, fldPaymentType='Cash', fldAmountPaid='"+findOldAmount() +"' WHERE fldWaterMeterID='"+FindWaterMeterID()+"'");

                    }
                    if (diffInt == 2)
                    {
                        System.out.println("The costumer didn't pay enough");
                        System.out.println("New amount due: "+ diff );
                        DB.updateSQL("UPDATE tblPayment SET fldAmountDue='"+diff+"', fldAmountPaid='"+receivedamount +"',fldPaymentType='Cash'  WHERE fldWaterMeterID='"+FindWaterMeterID()+"'");
                    }
                    System.out.println("input payment date: (YYYY-MM-DD");
                    Scanner in = new Scanner(System.in);
                    String date = in.next();
                    DB.updateSQL("UPDATE tblPayment SET fldPaymentDate ='"+date+"' WHERE fldWaterMeterID='"+FindWaterMeterID()+"'");
                    break;
                case 2:
                    //Enter the code that needs to run if 2 was the option choosed.
                    quit = true; //This will make the loop stop after running the program - Used when user want to go back
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void createMissingPaymentReminder() {
        boolean quit = false; // IS NEEDED AS THE BOOLEAN OUTSIDE OF THIS METHOD CAN'T BE ACCESSED

        DB.selectSQL("SELECT COUNT(fldwaterMeterID) FROM tblPayment WHERE fldAmountDue > 0"); //Will create a reminder for everyone who has an amount due
        String dbAmountOfCustomers = DB.getData();
        int amountOfCustomers = Integer.parseInt(dbAmountOfCustomers);

        do {
            System.out.println(" ");
            System.out.println("****************** CREATE Reminders **********************");
            System.out.println(" ");
            System.out.println("Press '1' for: Yes");
            System.out.println("Press '2' for No");
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput();
            System.out.println(" ");
            System.out.println("**********************************************************");
            System.out.println(" ");
            switch (menuItem) {
                case 1:
                    for (int i = 0; i < amountOfCustomers; i++) {

                        System.out.println(amountOfCustomers); //6
                        DB.selectSQL("SELECT fldWaterMeterID FROM tblPayment WHERE fldAmountDue>0");
                        String dbCustomerID = DB.getData();
                        String stopValue14 = DB.getData();
                        int customerID = Integer.parseInt(dbCustomerID);
                        System.out.println(customerID);

                        DB.selectSQL("SELECT fldPaymentID from tblPayment WHERE fldWaterMeterID = (SELECT fldCustomerID from tblCustomer WHERE fldCustomerID = '" + customerID + "')");
                        String dbPaymentIDString = DB.getData();
                        //String dbPaymentIDString1 = DB.getData();
                        int paymentID = Integer.parseInt(dbPaymentIDString);
                        System.out.println(paymentID);

                        DB.selectSQL("SELECT fldAmountDue from tblPayment WHERE fldWaterMeterID = (SELECT fldCustomerID from tblCustomer WHERE fldCustomerID = '" + customerID + "')");
                        String dbAmountDueString = DB.getData();
                        double amountDue = Double.parseDouble(dbAmountDueString);
                        System.out.println(amountDue);

                        DB.selectSQL("SELECT fldCustomerName FROM tblCustomer WHERE fldCustomerID='" + customerID + "'");
                        String customerName = DB.getData();
                        System.out.println(customerName);

                        String filename = Integer.toString(customerID);

                        DB.selectSQL("SELECT fldAddress FROM tblCustomer WHERE fldCustomerID='" + customerID + "'");
                        String address = DB.getData();
                        System.out.println(address);

                        String accountNumber = "Reg nr: 4578 - Account Number: 0004578988"; //IDEALLY THIS WOULD HAVE BEEN A SETTING SO IT COULD HAVE BEEN CHANGED LATER ON - TIME THOUGH...
                        String paymentIDString = Integer.toString(paymentID);
                        String amountDueString = Double.toString(amountDue);

                        // ADDS TODAY's DATE
                        Calendar cal1 = Calendar.getInstance();
                        Date datesDate = cal1.getTime();
                        DateFormat datesDateFormat = new SimpleDateFormat("dd/MM/yyyy");
                        String todaysDate = datesDateFormat.format(datesDate);

                        final String FILELOCATION = "F:\\waterwork reminders\\" + filename + "reminder.doc"; //This is where we store giro-card files
                        try (BufferedWriter bw = new BufferedWriter(new FileWriter(FILELOCATION))) {

                            bw.write("\t" + customerName + "\n");
                            bw.write("\t" + address + "\n");
                            bw.write("\t" + todaysDate + "\n");
                            bw.newLine();
                            bw.newLine();
                            bw.write("Total Amount Overdue:        " + amountDueString + ",- incl. VAT\n");
                            bw.write("Please use this Payment ID when transferring the money:   " + dbPaymentIDString + "\n");
                            bw.write("Account number:   " + accountNumber + "\n");
                            bw.newLine();
                            bw.write("Please pay within 7 days or you'll receive another reminder. At the third reminder we'll shut off the water supply to your address.\n");
                            bw.newLine();
                            bw.write("Uptown WaterWork - Waterwork Road 1, 6400 Sønderborg - CVR: 12345678");


                            System.out.println("Giro Card for CustomerID: " + customerID + " was saved correctly"); // This lets the employee see that it was successful.

                        } catch (IOException e) { //This is an error message (debugging safety) that will give a notice if something fails.

                            e.printStackTrace();

                        }
                        DB.selectSQL("SELECT fldReminders FROM tblPayment WHERE fldWaterMeterID=(SELECT fldCustomerID FROM tblWaterMeter WHERE fldCustomerID = '" + customerID + "')");
                        String dbNumberOfReminders = DB.getData();
                        String stopValue = DB.getData();
                        int numberOfReminders = Integer.parseInt(dbNumberOfReminders);
                        numberOfReminders++;

                        boolean ok = false;
                        ok = DB.updateSQL("UPDATE tblPayment SET fldReminders = '" + numberOfReminders + "'");
                        System.out.println(ok);
                    }
                    System.out.println("All reminders have been created.");
                    quit = true; //Stops the loop - Task finished
                    break;
                case 2:
                    quit = true; //Goes back to previous menu.
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void shutOffWaterSupply() {
        System.out.println("Shut Off Water Supply");
    }

    private static void customerManagement() {
        Scanner input = new Scanner(System.in);
        String customerName;


        String customerNewName = "";
        String customerOldName;
        String oldAddress;
        String newAddress;
        String oldMoveInDate;
        String newMoveInDate;
        String oldSegment;
        String newSegment;
        int oldNoOfWaterMeters;
        int newNoOfWaterMeters;
        int oldZipcode;
        int newZipcode;
        String moveInDate;
        String zipcode;
        String address;
        String segment;
        String waterMeter;
        String moveOutDate;
        String customerID;
        boolean quit = false;


        do {

            int menuItem; //This will save your menu choice


            System.out.println("/////////////////////////////////////////");
            System.out.println("Press '1' for: Adding new customer"); //Change these depending on the number of choices
            System.out.println("Press '2' for: Deleting customer"); //Change these depending on the number of choices
            System.out.println("Press '3' for: Update customer");
            System.out.println("Press '4' for: Returning to the main menu"); //
            System.out.print("Enter your choice here: ");
            menuItem = validateIntInput(); //Keep this to validate menu
            System.out.println("/////////////////////////////////////////");
            switch (menuItem) {
                case 1: //


                    System.out.println("Enter the customer name:");
                    customerName = input.next();

                    System.out.println("Enter the address:");
                    input.nextLine();
                    address = input.nextLine();


                    System.out.println("Enter the zipcode:");
                    zipcode = input.next();

                    System.out.println("Enter the move in date:");
                    moveInDate = input.next();

                    System.out.println("Enter the move out date");
                    moveOutDate = input.next();

                    System.out.println("Enter the segment:");
                    segment = input.next();

                    System.out.println("Enter the number of water meters:");
                    waterMeter = input.next();


                    //Inserting new data into the customer table

                    DB.insertSQL("INSERT INTO tblCustomer (fldCustomerName, fldAddress, fldZipcode, fldMoveInDate, fldSegment, fldNumberOfWaterMeters, fldMoveOutDate) VALUES('" + customerName + "', '" + address + "', '" + zipcode + "', '" + moveInDate + "', '" + segment + "', '" + waterMeter + "', '" + moveOutDate + "')");
                    System.out.println(customerName + " " + address + " " + zipcode + " " + moveInDate + " " + segment + " " + waterMeter + "  " + moveOutDate);
                    break;


                case 2:

                    DB.selectSQL("SELECT fldCustomerName FROM tblCustomer");
                    do {
                        String data = DB.getDisplayData();
                        if (data.equals(DB.NOMOREDATA)) {
                            break;
                        } else
                            System.out.print(data);
                    }
                    while (true);

                    System.out.println("You can enter the customer name here you want to delete from the system:");
                    customerName = input.next();

                    //Delete data
                    DB.deleteSQL("DELETE from tblCustomer where fldCustomerName='" + customerName + "';");
                    System.out.println(customerName + " is removed from the list");

                    break;

                case 3:

                    System.out.println("You can update information about customers here:");

                    DB.selectSQL("SELECT fldCustomerName FROM tblCustomer");
                    do {
                        String data = DB.getDisplayData();
                        if (data.equals(DB.NOMOREDATA)) {
                            break;
                        } else
                            System.out.print(data);
                    }
                    while (true);

                    do {

                        System.out.println("/////////////////////////////////////////");
                        System.out.print("Please choose which information would you like to update:");
                        System.out.println();
                        System.out.println("Press '1' for: Customer Name");
                        System.out.println("Press '2' for: Address");
                        System.out.println("Press '3' for: Zipcode");
                        System.out.println("Press '4' for: Move in date");
                        System.out.println("Press '5' for: Segment");
                        System.out.println("Press '6' for: Number of Water meters");
                        System.out.println("Press '7' for: Go back to main menu");


                        menuItem = validateIntInput(); //Keep this to validate menu
                        System.out.println("/////////////////////////////////////////");

                        switch (menuItem) {
                            case 1:
                                System.out.println("Please input the customer name what you want to change:");//Enter the code that needs to run if 1 was the option choosed.
                                //input = input.reset();
                                input.nextLine();
                                customerOldName = input.nextLine();
                                System.out.println(customerOldName);
                                DB.selectSQL("SELECT fldCustomerName, fldCustomerID FROM tblCustomer WHERE fldCustomerName='" + customerOldName + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);
                                System.out.println("Insert Customer ID");
                                customerID = input.next();
                                System.out.println("Insert New Name");
                                input.nextLine();
                                customerNewName = input.nextLine();

                                //Update data in Customer Name field
                                if (customerNewName.length() > 0) {
                                    DB.updateSQL("UPDATE tblCustomer SET fldCustomerName='" + customerNewName + "' WHERE fldCustomerID='" + customerID + "'");
                                    System.out.println("Customer number " + customerID + " has his name changed to " + customerNewName);
                                }
                                break;

                            case 2:
                                System.out.println("Please input the old address which you want to change:");
                                oldAddress = input.nextLine();

                                System.out.println(oldAddress);
                                DB.selectSQL("SELECT fldAddress, fldCustomerID FROM tblCustomer WHERE fldAddress='" + oldAddress + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);

                                System.out.println("Insert Customer ID");
                                customerID = input.next();

                                System.out.println("Please input the new address:");
                                input.nextLine();
                                newAddress = input.nextLine();

                                //Update data in Address field
                                DB.updateSQL("UPDATE tblCustomer SET fldAddress='" + newAddress + "' WHERE fldCustomerID='" + customerID + "'");
                                System.out.println("Customer number " + customerID + " has his name changed to " + newAddress);
                                break;

                            case 3://
                                System.out.println("Please input the old zipcode which you want to change:");
                                oldZipcode = input.nextInt();

                                System.out.println(oldZipcode);
                                DB.selectSQL("SELECT fldZipcode, fldCustomerID FROM tblCustomer WHERE fldZipcode='" + oldZipcode + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);

                                System.out.println("Insert Customer ID");
                                customerID = input.next();

                                System.out.println("Please input the new zipcode:");
                                input.nextLine();
                                newZipcode = input.nextInt();

                                //Update data in Address field
                                DB.updateSQL("UPDATE tblCustomer SET fldZipcode='" + newZipcode + "' WHERE fldCustomerID='" + customerID + "'");
                                System.out.println("Customer number " + customerID + " has his name changed to " + newZipcode);
                                break;


                            case 4:
                                System.out.println("Please input the old move in date which you want to change:");
                                oldMoveInDate = input.next();

                                System.out.println(oldMoveInDate);
                                DB.selectSQL("SELECT fldMoveInDate, fldCustomerID FROM tblCustomer WHERE fldMoveInDate='" + oldMoveInDate + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);

                                System.out.println("Insert Customer ID");
                                customerID = input.next();

                                System.out.println("Please input the new date of move in:");
                                input.nextLine();
                                newMoveInDate = input.next();

                                //Update data in Address field
                                DB.updateSQL("UPDATE tblCustomer SET fldMoveInDate='" + newMoveInDate + "' WHERE fldCustomerID='" + customerID + "'");
                                System.out.println("Customer number " + customerID + " has his name changed to " + newMoveInDate);
                                System.out.println();
                                break;


                            case 5:
                                System.out.println("Please input the old segment type which you want to change:");
                                oldSegment = input.next();

                                System.out.println(oldSegment);
                                DB.selectSQL("SELECT fldSegment, fldCustomerID FROM tblCustomer WHERE fldSegment='" + oldSegment + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);

                                System.out.println("Insert Customer ID");
                                customerID = input.next();

                                System.out.println("Please input the new segment type:");
                                input.nextLine();
                                newSegment = input.next();

                                //Update data in Address field
                                DB.updateSQL("UPDATE tblCustomer SET fldSegment='" + newSegment + "' WHERE fldCustomerID='" + customerID + "'");
                                System.out.println("Customer number " + customerID + " has his name changed to " + newSegment);
                                System.out.println();
                                break;

                            case 6:
                                System.out.println("Please input the number of water meters which you want to change:");
                                oldNoOfWaterMeters = input.nextInt();

                                System.out.println(oldNoOfWaterMeters);
                                DB.selectSQL("SELECT fldNumberOfWaterMeters, fldCustomerID FROM tblCustomer WHERE fldNumberOfWaterMeters='" + oldNoOfWaterMeters + "';");//
                                do {
                                    String data = DB.getDisplayData();
                                    if (data.equals(DB.NOMOREDATA)) {
                                        break;
                                    } else
                                        System.out.print(data);
                                }
                                while (true);

                                System.out.println("Insert Customer ID");
                                customerID = input.next();

                                System.out.println("Please input the new number of water meters:");
                                input.nextLine();
                                newNoOfWaterMeters = input.nextInt();

                                //Update data in Address field
                                DB.updateSQL("UPDATE tblCustomer SET fldNoOfWaterMeters='" + newNoOfWaterMeters + "' WHERE fldCustomerID='" + customerID + "'");
                                System.out.println("Customer number " + customerID + " has his name changed to " + newNoOfWaterMeters);
                                System.out.println();
                                break;

                            case 7:
                                quit = true; //This will make the loop stop after running the program - Used when user want to go back
                                break;
                            default:
                                System.out.println("Invalid choice. Try again: ");
                        }

                    } while (!quit);
                    break;

                case 4:
                    quit = true; //This will make the loop stop after running the program - Used when user want to go back
                    break;
                default:
                    System.out.println("Invalid choice. Try again: ");
            }
        } while (!quit);
    }

    private static void importCsvFile() {
        System.out.println("Import .csv File");
    }

    private static void dailyPaymentOverview() {
        System.out.println("Daily Payment Overview");
    }

    private static void accumulatedTaxOverview() {
        System.out.println("Accumulated Tax Overview");
    }

    private static void extractStatisticOne() {
        System.out.println("Extract statistic 1");


        DB.selectSQL("SELECT * from tblCustomer where fldSegment=1 (select avg(fldWaterReadingLastTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");

        DB.selectSQL("SELECT * from tblCustomer where fldSegment=2 (select (fldWaterReadingLastTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");


        DB.selectSQL("SELECT * from tblCustomer where fldSegment=1 (select avg(fldWaterReadingThisTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");

        DB.selectSQL("SELECT * from tblCustomer where fldSegment=2 (select avg(fldWaterReadingThisTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");

        DB.selectSQL("SELECT * from tblCustomer where fldSegment=3 (select avg(fldWaterReadingThisTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");

        DB.selectSQL("SELECT * from tblCustomer where fldSegment=2 (select avg(fldWaterReadingLastTime) from tblWaterMeter)");

        do {
            String data = DB.getDisplayData();
            if (data.equals(DB.NOMOREDATA)) {
                break;
            } else
                System.out.print(data);
        }
        while (true);
        System.out.println("***********************************");
    }


    private static void extractStatisticTwo() {
        System.out.println("Extract statistic 2");
    }

    private static void extractStatisticThree() {
        System.out.println("Extract statistic 3");
    }

    private static int validateIntInput() {
        // VALIDATES IF THE INPUT IS AN INT
        Scanner input = new Scanner(System.in);
        while (true) {
            try {
                int a = input.nextInt();
                if (a == 0) {
                    return 0;
                } else {
                    return a;
                }
            } catch (java.util.InputMismatchException e) {
                System.out.println("You didn't enter a number - Try again: ");
                input.nextLine();
            }
        }
    }

    private static int getMaxID() {
        DB.selectSQL("select Max(fldCustomerID) from tblWaterMeter");
        String MaxCostumer = DB.getData();
        MaxCostumerID = Integer.parseInt(MaxCostumer);
        String stopValue = DB.getData();
        return MaxCostumerID;
    }

    private static int FindWaterMeterID() {
        DB.selectSQL("select fldWaterMeterID from tblWaterMeter where fldCustomerID = '" + costumerID + "'");
        String costumer = DB.getData();
        costumerID = Integer.parseInt(costumer);
        String stopValue = DB.getData();
        return costumerID;
    }

    private static double findOldAmount() {
        DB.selectSQL("select fldAmountDue from tblPayment where fldWaterMeterID = '" + FindWaterMeterID() + "'");
        double dueamount;
        String amount = DB.getData();
        dueamount = Double.parseDouble(amount);
        String stopValue = DB.getData();
        return dueamount;
    }

    private static boolean CostumerIDtjeck() {
        boolean tjeck = false;
        Scanner in = new Scanner(System.in);
        //int maxcostumerID = //max coustumer ID ud fra SQL
        getMaxID();
        System.out.println("Input customer ID:");
        if (in.hasNextInt()) {
            costumerID = in.nextInt();

            if (costumerID > 0 && costumerID < MaxCostumerID) {
                tjeck = true;
            }

        }
        return tjeck;
    }

    private static int paymentjeck() {


        System.out.printf("Amount due: %.2f\n", findOldAmount());
        System.out.println("input received amount: ");

        Scanner in = new Scanner(System.in);

        receivedamount = in.nextDouble();

        diff = findOldAmount() - receivedamount;
        if (diff < 0) {
            return 1;//the costumer payed to much
        }
        if (diff > 0) {
            return 2;//the costumer didn't pay enough
        }
        return 0;
    }

}