::CLASS DatabaseHandler PUBLIC
::ATTRIBUTE conn
::ATTRIBUTE DB_URL
::ATTRIBUTE DriverManager
::ATTRIBUTE PasswordHandler
::ATTRIBUTE FXCollections
::METHOD init
 EXPOSE DriverManager PasswordHandler  FXCollections
 DriverManager = bsf.import("java.sql.DriverManager")
 FXCollections = bsf.import("javafx.collections.FXCollections")
 PasswordHandler = .PasswordHandler~new
------------------------------------------------------------------------------------------------------------------------
::METHOD connect PUBLIC
 EXPOSE DriverManager DB_URL conn
 SIGNAL ON ANY
 conn = DriverManager~getConnection(DB_URL)
 RETURN .true
 EXIT 0

 ANY:
    Say "ERROR: Failed to connect to database."
    EXIT -1
------------------------------------------------------------------------------------------------------------------------
::METHOD disconnectDB PUBLIC
 EXPOSE DriverManager DB_URL conn
 conn~close()
------------------------------------------------------------------------------------------------------------------------
::METHOD setDBsettings PUBLIC
 EXPOSE DriverManager DB_URL conn DB_NAME
 USE ARG fileName
 DBpath = .my.app~homeDir || "DataBase"
 CALL directory  DBpath

 databaseData = .stream~new(fileName)~~open
DO line over databaseData
    PARSE VAR line "DB_NAME = " DB_NAME
    PARSE VAR line "DB_URL = " DB_URL
END
 databaseData~close
 CALL directory .my.app~homeDir
------------------------------------------------------------------------------------------------------------------------
::METHOD insertTables PUBLIC
 EXPOSE DriverManager DB_URL conn

 tableDesc = .array~of("DEPReceipt", "companyDetails", "user", "tax", "product", "order", "contains")
 tableStatements = .array~of('CREATE TABLE "DEPReceipt" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "certificate" TEXT,"Date_paid" TEXT,' -
                                '"jws_header" TEXT, "jws_payload" TEXT, "jws_signature" TEXT);', -
                        'CREATE TABLE "companyDetails" ("company_ID" INTEGER PRIMARY KEY AUTOINCREMENT, "vat_ID" TEXT, "name" TEXT, "telephone" TEXT, "address" TEXT, "city" TEXT,'-
                                '"postcode" INTEGER, "e_mail" TEXT, "cashbox_ID" TEXT,"aes256key" TEXT, "JWSkey" TEXT,'-
                                 '"se_sessionid" TEXT, "se_sessionkey" TEXT, "se_lastLogin" TEXT, "turnoverCounter" INTEGER);', -
                        'CREATE TABLE "user" ("user_ID"	INTEGER PRIMARY KEY AUTOINCREMENT, "first_name" TEXT, "last_name" TEXT, "user_name" TEXT, "password" TEXT, "rightsIndex" INTEGER);',-
                        'CREATE TABLE "tax" ("tax_ID"	INTEGER PRIMARY KEY AUTOINCREMENT, "taxRate" NUMERIC);',-
                        'CREATE TABLE "product" ("prod_ID"	INTEGER PRIMARY KEY AUTOINCREMENT, "name"	TEXT, "price" NUMERIC,'-
                                    '"quantity"	INTEGER, "property_ind" INTEGER, "tax_ID" INTEGER, FOREIGN KEY("tax_ID") REFERENCES tax("tax_ID"));',-
                        'CREATE TABLE "order" ("order_ID"	INTEGER PRIMARY KEY AUTOINCREMENT, "Date_paid" TEXT, "table_nr" INTEGER , "receipt_type" TEXT,'-
                                    '"jws_header" TEXT, "jws_payload" TEXT, "jws_signature" TEXT, "user_ID" INTEGER,'-
                        	        'FOREIGN KEY("user_ID") REFERENCES user("user_ID"));',-
                        'CREATE TABLE "contains" ("contains_ID"	INTEGER PRIMARY KEY AUTOINCREMENT, "status_order" TEXT,'-
                        	        '"prod_ID"	INTEGER, "order_ID" INTEGER,'-
                        	         'FOREIGN KEY("order_ID") REFERENCES "order"("order_ID"), FOREIGN KEY("prod_ID") REFERENCES product("prod_ID"));'	-
                         )
DO i = 1 TO tableDesc~size
    tableExist = self~checkIFtableExists(tableDesc[i])
    IF \tableExist THEN DO
        prepStatement = conn~createStatement
        prepStatement~execute(tableStatements[i])
    END
    ELSE DO
        SAY pp("Table already exists! --> " tableDesc[i])
    END
END
SAY pp("Database setup was successfull...")
------------------------------------------------------------------------------------------------------------------------
::METHOD insertTaxValues PUBLIC
 EXPOSE DriverManager conn
 tax = .array~of("20%", "10%", "13%", "0%", "19%")
 taxValueArray = .array~of("INSERT INTO  tax (taxRate) VALUES (0.20);","INSERT INTO  tax (taxRate) VALUES (0.10);","INSERT INTO  tax (taxRate) VALUES (0.13);",-
                           "INSERT INTO  tax (taxRate) VALUES (0.00);","INSERT INTO  tax (taxRate) VALUES (0.19);")

DO i=1 to tax~size
    prepStatement = conn~createStatement
    prepStatement~execute(taxValueArray[i])
END
------------------------------------------------------------------------------------------------------------------------
::METHOD checkIFtableExists PUBLIC
 EXPOSE DriverManager conn
 USE ARG tableName
 query = "PRAGMA table_info(["|| tableName || "]);"
 prepStatement = conn~prepareStatement(query)
 queryResults = prepStatement~executeQuery
 IF queryResults~next THEN DO
    RETURN .true
 END
 ELSE DO
    RETURN .false
 END
------------------------------------------------------------------------------------------------------------------------
::METHOD checkIFPOSAdminExists PUBLIC
 EXPOSE DriverManager DB_URL conn
 query = "SELECT * FROM 'user' WHERE rightsIndex = 3;"
 prepStatement = conn~prepareStatement(query)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    RETURN .true
END
ELSE DO
    RETURN .false
END
------------------------------------------------------------------------------------------------------------------------
::METHOD getJWSSigningKey PUBLIC
 EXPOSE DriverManager DB_URL conn
 query = "SELECT JWSkey FROM 'companyDetails' WHERE company_ID = 1;"
 prepStatement = conn~prepareStatement(query)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    key = queryResults~getString("JWSkey")
    RETURN key
END
------------------------------------------------------------------------------------------------------------------------
::METHOD checkIFCompanyDataExists PUBLIC
 EXPOSE DriverManager DB_URL conn
 query = "SELECT * FROM companyDetails WHERE company_ID = 1;"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
   RETURN .true
END
ELSE DO
    RETURN .false
END
------------------------------------------------------------------------------------------------------------------------
::METHOD insertUser PUBLIC
 EXPOSE DriverManager DB_URL conn PasswordHandler
 USE ARG firstName, lastName, userName, password, rightsIndex

 password = PasswordHandler~hashPassword(password)

 query = "INSERT INTO 'user' (first_name, last_name, user_name, password, rightsIndex) VALUES (?,?,?,?,?);"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, firstName)
 prepStatement~setString(2, lastName)
 prepStatement~setString(3, userName)
 prepStatement~setString(4, password)
 prepStatement~setString(5, rightsIndex)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateUserPassword PUBLIC
 EXPOSE conn PasswordHandler
 USE ARG userID, password

 password = PasswordHandler~hashPassword(password)

 query = "UPDATE 'user' set password = ? WHERE user_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, password)
 prepStatement~setString(2, userID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD checkPassword PUBLIC
 EXPOSE conn PasswordHandler
 USE ARG userID, password
IF userID = 0 THEN DO   -- user doent exist
    return 0
END
ELSE DO
    query = "SELECT password FROM 'user' WHERE user_ID = ?;"
    prepStatement = conn~prepareStatement(query)
    prepStatement~setString(1, userID)
    queryResults =prepStatement~executeQuery
    DO  queryResults~next
        hashedPasswordDB = queryResults~getString("password")
    END
    check = PasswordHandler~checkPass(password, hashedPasswordDB)
    RETURN check
END
------------------------------------------------------------------------------------------------------------------------
::METHOD checkIfUserNameExist PUBLIC
 EXPOSE conn
 USE ARG username
 query = "SELECT * FROM 'user' WHERE user_name = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, username)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    RETURN 1
END
 RETURN 0
------------------------------------------------------------------------------------------------------------------------
::METHOD getUserIDByUsername PUBLIC
 EXPOSE conn
 USE ARG username

 query = "SELECT user_ID FROM 'user' WHERE user_name = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, username)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    userID = queryResults~getString("user_ID")
    RETURN userID
END
ELSE DO
    RETURN 0
END
------------------------------------------------------------------------------------------------------------------------
::METHOD getUserInfosSetActive PUBLIC
 EXPOSE conn
 USE ARG username

 query = "SELECT user.user_ID, user.first_name, user.last_name, user.rightsIndex FROM 'user' WHERE user_name = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, username)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    userID = queryResults~getString("user_ID")
    firstname = queryResults~getString("first_name")
    lastname = queryResults~getString("last_name")
    rightsInd = queryResults~getString("rightsIndex")
END
 RETURN .ActiveUser~new(userID, firstname, lastname, rightsInd)
------------------------------------------------------------------------------------------------------------------------
::METHOD deleteUser PUBLIC
 EXPOSE conn
 USE ARG userID

 query = "DELETE FROM 'user' WHERE user_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, userID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getAlluserArrayList PUBLIC
 EXPOSE conn FXCollections
 usersArrayList = FXCollections~observableArrayList
 query = "SELECT * FROM user;"

 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery

DO WHILE queryResults~next
    user_ID = queryResults~getString("user_ID")
    first_name = queryResults~getString("first_name")
    last_name = queryResults~getString("last_name")
    user_name = queryResults~getString("user_name")
    password = queryResults~getString("password")
    rightsIndex = queryResults~getString("rightsIndex")
    usersArrayList~add(.User~new(user_ID, first_name, last_name,user_name, password, rightsIndex))
END
 RETURN usersArrayList
------------------------------------------------------------------------------------------------------------------------
::METHOD updateUser PUBLIC
 EXPOSE conn
 USE ARG user_ID, first_name, last_name, user_name, rightsIndex

 query = "UPDATE 'user' SET first_name = ?, last_name = ?, user_name = ?, rightsIndex = ? WHERE user_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, first_name)
 prepStatement~setString(2, last_name)
 prepStatement~setString(3, user_name)
 prepStatement~setString(4, rightsIndex)
 prepStatement~setString(5, user_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateSELogin PUBLIC
 EXPOSE conn
 USE ARG time, company_ID = 1

 query = "UPDATE companyDetails SET se_lastLogin = ? WHERE company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, time)
 prepStatement~setString(2, company_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getSELoginTime PUBLIC
 EXPOSE conn
 USE ARG company_ID = 1
 query = "SELECT se_lastLogin FROM companyDetails WHERE company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, company_ID)
 queryResults = prepStatement~executeQuery
IF queryResults~next THEN DO
    time = queryResults~getString("se_lastLogin")
END
 RETURN time
------------------------------------------------------------------------------------------------------------------------
::METHOD insertOrder PUBLIC
 EXPOSE conn
 USE ARG table_nr, receipt_type
 say "table_nr" table_nr
 say "receipt_type" receipt_type
 query = 'INSERT INTO "order" (table_nr, receipt_type, user_ID) VALUES (?,?,?);'
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, table_nr)
 prepStatement~setString(2, receipt_type)
 prepStatement~setString(3, .my.app~ActiveUser~userID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getAllOpenOrders PUBLIC
 EXPOSE conn FXCollections
 USE ARG table_nr, receipt_type, user_ID
 openOrderArrayList = FXCollections~observableArrayList
 query = "SELECT * FROM 'order' WHERE Date_paid IS NULL;"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
DO WHILE queryResults~next
    user_ID = queryResults~getString("order_ID")
    table_nr = queryResults~getString("table_nr")
    type = queryResults~getString("receipt_type")
    openOrderArrayList~add(.Order~new(user_ID, table_nr, type))
END
 RETURN openOrderArrayList
------------------------------------------------------------------------------------------------------------------------
::METHOD checkOrders PUBLIC
 EXPOSE conn FXCollections
 query = "SELECT * FROM 'order';"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    RETURN .true
END
ELSE DO
    RETURN .false
END
------------------------------------------------------------------------------------------------------------------------
::METHOD updateOrderJWS PUBLIC
 EXPOSE  conn
 USE ARG order_ID ,jws_header, jws_payload, jws_signature

 query = "UPDATE 'order'  SET jws_header = ?, jws_payload = ?, jws_signature = ? WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, jws_header)
 prepStatement~setString(2, jws_payload)
 prepStatement~setString(3, jws_signature)
 prepStatement~setString(4, order_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getDatePayment PUBLIC
 EXPOSE  conn
 USE ARG order_ID
 PARSE VAR jwsdetails jws_header jws_payload jws_signature

 query = "UPDATE 'order'  SET Date_paid = datetime('now','localtime') WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 prepStatement~execute

 query = "SELECT Date_paid  FROM 'order' WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    datePaid = queryResults~getString("Date_paid")
END
  RETURN datePaid
------------------------------------------------------------------------------------------------------------------------
::METHOD getTaxID PUBLIC
 EXPOSE conn
 USE ARG taxRate

 query = "SELECT tax_ID FROM tax WHERE taxRate = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, taxRate)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
    tax_ID = queryResults~getString("tax_ID")
END
 RETURN tax_ID
------------------------------------------------------------------------------------------------------------------------
::METHOD getTaxRateByProdID PUBLIC
 EXPOSE conn
 USE ARG ProdID

 query = "SELECT taxRate FROM product INNER JOIN tax ON product.tax_ID = tax.tax_ID WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, ProdID)
 queryResults = prepStatement~executeQuery

IF queryResults~next THEN DO
  taxRate = queryResults~getString("taxRate")
END
 RETURN taxRate
------------------------------------------------------------------------------------------------------------------------
::METHOD insertProduct PUBLIC
 EXPOSE conn
 USE ARG name, price, quantity, property_ind, taxPercent
 PARSE VAR taxPercent taxRate "%"
 taxRate = taxRate/100
 tax_ID = self~getTaxID(taxRate)

 query = "INSERT INTO product (name, price, quantity, property_ind, tax_ID) VALUES (?, ?, ?, ?, ?);"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, name)
 prepStatement~setString(2, price)
 prepStatement~setString(3, quantity)
 prepStatement~setString(4, property_ind)
 prepStatement~setString(5, tax_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateProduct PUBLIC
 EXPOSE conn
 USE ARG prod_ID, name, price, quantity, property_ind, taxPercent
 PARSE VAR taxPercent taxRate "%"
 taxRate = taxRate/100
 tax_ID = self~getTaxID(taxRate)

 query = "UPDATE product SET name = ?, price  = ? , quantity = ? , property_ind = ?, tax_ID = ? WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, name)
 prepStatement~setString(2, price)
 prepStatement~setString(3, quantity)
 prepStatement~setString(4, property_ind)
 prepStatement~setString(5, tax_ID)
 prepStatement~setString(6, prod_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateProductLunchInd PUBLIC
 EXPOSE conn
 USE ARG prod_ID, property_ind

 query = "UPDATE product SET property_ind = ? WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, property_ind)
 prepStatement~setString(2, prod_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD addProductQuantity PUBLIC
 EXPOSE conn
 USE ARG prod_ID
 query = "UPDATE product SET quantity = quantity + 1 WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, prod_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD removeProductQuantity PUBLIC
 EXPOSE conn
 USE ARG prod_ID
 query = "UPDATE product SET quantity = quantity -1 WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, prod_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD deleteProduct PUBLIC
 EXPOSE conn
 USE ARG prod_ID

 query = "DELETE FROM product WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, prod_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getAllProductsArrayListByID PUBLIC
 EXPOSE conn FXCollections
 USE ARG orderID, orderDetailsHandler
 orderDetailsHandler~orderDetailsArrayList = FXCollections~observableArrayList

 query = "SELECT product.prod_ID, product.name, product.price, contains.contains_ID, contains.status_order FROM contains INNER JOIN product ON product.prod_ID = contains.prod_ID WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, orderID)
 queryResults =prepStatement~executeQuery

DO WHILE queryResults~next
    prod_ID = queryResults~getString("prod_ID")
    name = queryResults~getString("name")
    price = queryResults~getString("price")
    contains_ID = queryResults~getString("contains_ID")
    status_order = queryResults~getString("status_order")
    IF status_order <> "paid" THEN DO
        orderDetailsHandler~addItem(.OrderDetails~new(contains_ID,prod_ID, name, price, status_order))
    END
END
------------------------------------------------------------------------------------------------------------------------
::METHOD getAllProductsForNewOrderByID PUBLIC
 EXPOSE conn FXCollections
 USE ARG order_ID, prod_ID = .nil
 newOrderDetailsArrayList = FXCollections~observableArrayList

 query = "SELECT product.prod_ID, product.name, product.quantity FROM contains INNER JOIN product ON product.prod_ID = contains.prod_ID WHERE order_ID = ? GROUP BY contains.prod_ID;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 queryResults =prepStatement~executeQuery

DO WHILE queryResults~next
    prod_ID = queryResults~getString("prod_ID")
    name = queryResults~getString("name")
    quantity = self~countProdID(order_ID, prod_ID)
    newOrderDetailsArrayList~add(.newOrderDetails~new(prod_ID, name, quantity))
END
 RETURN newOrderDetailsArrayList
------------------------------------------------------------------------------------------------------------------------
::METHOD countProdID PUBLIC
 EXPOSE conn
 USE ARG order_ID, prod_ID = .nil

 query = "SELECT COUNT(prod_ID) AS sum FROM contains WHERE order_ID = ? AND prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 prepStatement~setString(2, prod_ID)
 queryResults = prepStatement~executeQuery
IF queryResults~next THEN DO
    quantity = queryResults~getString("sum")
END
IF prod_ID = .nil THEN
    RETURN 1
ELSE
    RETURN quantity
------------------------------------------------------------------------------------------------------------------------
::METHOD getTypeOfReceipt PUBLIC
 EXPOSE conn
 USE ARG order_ID

 query = "SELECT receipt_type FROM 'order' WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    receipt_type = queryResults~getString("receipt_type")
END
 RETURN receipt_type
------------------------------------------------------------------------------------------------------------------------
::METHOD getAllProductsArrayList PUBLIC
 EXPOSE conn FXCollections
 productsArrayList = FXCollections~observableArrayList

 query = "SELECT product.prod_ID, product.name, product.price, product.quantity, product.property_ind, tax.taxRate FROM product INNER JOIN tax ON product.tax_ID = tax.tax_ID;"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
DO WHILE queryResults~next
    prod_ID = queryResults~getString("prod_ID")
    name = queryResults~getString("name")
    price = queryResults~getString("price")
    quantity = queryResults~getString("quantity")
    lunchInd = queryResults~getString("property_ind")
    tax = queryResults~getString("taxRate")
    productsArrayList~add(.Product~new(prod_ID, name, price,tax, quantity, lunchInd))
END
 RETURN productsArrayList
------------------------------------------------------------------------------------------------------------------------
::METHOD productAlreadyExists PUBLIC
 EXPOSE conn
 USE ARG prodID

 query = "SELECT * FROM product WHERE prod_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, prodID)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    RETURN .true
END
ELSE DO
    RETURN .false
END
------------------------------------------------------------------------------------------------------------------------
::METHOD getLunchMenuStarter PUBLIC
 EXPOSE conn
 query = "SELECT name FROM product WHERE property_ind = 1;"

 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF  queryResults~next THEN DO
    name = queryResults~getString("name")
END
 RETURN name
------------------------------------------------------------------------------------------------------------------------
::METHOD getLunchMenuMain PUBLIC
 EXPOSE conn

 query = "SELECT name FROM product WHERE property_ind = 2;"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF  queryResults~next THEN DO
    name = queryResults~getString("name")
END
 RETURN name
------------------------------------------------------------------------------------------------------------------------
::METHOD getLunchMenuDessert PUBLIC
 EXPOSE conn

 query = "SELECT name FROM product WHERE property_ind = 3;"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF  queryResults~next THEN DO
    name = queryResults~getString("name")
END
 RETURN name
------------------------------------------------------------------------------------------------------------------------
::METHOD insertCompanyDetails PUBLIC
 EXPOSE conn
 USE ARG vat_ID, name, telephone, address, city, postcode, e_mail, cashbox_ID, aes256key, JWSkey

 query = "INSERT INTO companyDetails (vat_ID, name, telephone, address, city, postcode,e_mail, cashbox_ID, aes256key, turnoverCounter, JWSkey) VALUES (?, ?, ?, ?, ?, ?,?,?,?,0,?);"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, vat_ID)
 prepStatement~setString(2, name)
 prepStatement~setString(3, telephone)
 prepStatement~setString(4, address)
 prepStatement~setString(5, city)
 prepStatement~setString(6, postcode)
 prepStatement~setString(7, e_mail)
 prepStatement~setString(8, cashbox_ID)
 prepStatement~setString(9, aes256key)
 prepStatement~setString(10, JWSkey)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD insertContains PUBLIC
 EXPOSE conn
 USE ARG status_order, prod_ID, order_ID
 query = "INSERT INTO  contains (status_order, prod_ID, order_ID) VALUES (?, ?, ?);"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, status_order)
 prepStatement~setString(2, prod_ID)
 prepStatement~setString(3, order_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getLastOrderID PUBLIC
 EXPOSE conn
 query = "SELECT seq FROM sqlite_sequence WHERE name = 'order';"
 prepStatement = conn~prepareStatement(query)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    id = queryResults~getString("seq")
END
 RETURN id
------------------------------------------------------------------------------------------------------------------------
::METHOD updateContainsStatus PUBLIC
 EXPOSE conn
 USE ARG status_order, contains_ID

 query = "UPDATE contains SET status_order = ? WHERE contains_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, status_order)
 prepStatement~setString(2, contains_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD removeOrdersFromDB PUBLIC
 EXPOSE conn
 USE ARG order_ID
 query = "DELETE FROM contains WHERE order_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, order_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateSessionData PUBLIC
 EXPOSE conn
 USE ARG companyID, sessionID, sessionKey

 query = "UPDATE companyDetails SET se_sessionid = ?, se_sessionkey = ? WHERE company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, sessionID)
 prepStatement~setString(2, sessionKey)
 prepStatement~setString(3, companyID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD updateTurnoverValue PUBLIC
 EXPOSE conn
 USE ARG companyID, value

 query = "UPDATE companyDetails SET turnoverCounter = ? where company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, value)
 prepStatement~setString(2, companyID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getSessionData PUBLIC
 EXPOSE conn
 USE ARG companyID

 query = "SELECT se_sessionid, se_sessionkey FROM companyDetails WHERE company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, companyID)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    sessionID = queryResults~getString("se_sessionid")
    sessionKey = queryResults~getString("se_sessionkey")
END
 RETURN sessionID sessionKey
------------------------------------------------------------------------------------------------------------------------
::METHOD getCompanyData PUBLIC
 EXPOSE conn
 USE ARG companyID = 1
 company = .table~new
 query = "SELECT company_ID, vat_ID, name, telephone, address, e_mail, city, postcode, aes256key, cashbox_ID, JWSkey FROM companyDetails WHERE company_ID =  ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, companyID)
 queryResults =prepStatement~executeQuery
IF queryResults~next THEN DO
    company["company_ID"] = queryResults~getString("company_ID")
    company["vat_ID"] = queryResults~getString("vat_ID")
    company["name"] = queryResults~getString("name")
    company["telephone"] = queryResults~getString("telephone")
    company["address"] = queryResults~getString("address")
    company["e_mail"] = queryResults~getString("e_mail")
    company["city"] = queryResults~getString("city")
    company["postcode"] = queryResults~getString("postcode")
    company["aes256key"] = queryResults~getString("aes256key")
    company["cashbox_ID"] = queryResults~getString("cashbox_ID")
    company["JWSkey"] = queryResults~getString("JWSkey")
END
  RETURN company
------------------------------------------------------------------------------------------------------------------------
::METHOD updateCompanyData PUBLIC
 EXPOSE conn
 USE ARG vat_ID, name, telephone, address, city, postcode, e_mail, cashbox_ID, aes256key, company_ID = 1

 query = "UPDATE companyDetails SET vat_ID = ?, 'name' = ?, telephone = ?, 'address' = ?, city = ?, postcode = ?, e_mail = ?, cashbox_ID = ?, aes256key = ? where company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, vat_ID)
 prepStatement~setString(2, name)
 prepStatement~setString(3, telephone)
 prepStatement~setString(4, address)
 prepStatement~setString(5, city)
 prepStatement~setString(6, postcode)
 prepStatement~setString(7, e_mail)
 prepStatement~setString(8, cashbox_ID)
 prepStatement~setString(9, aes256key)
 prepStatement~setString(10, company_ID)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getTurnoverValue PUBLIC
 EXPOSE conn
 USE ARG companyID

 query = "SELECT turnoverCounter FROM companyDetails WHERE company_ID = ?;"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, companyID)
 queryResults =prepStatement~executeQuery
IF  queryResults~next THEN DO
    turnover = queryResults~getString("turnoverCounter")
END
 RETURN turnover
------------------------------------------------------------------------------------------------------------------------
::METHOD getLastJWSData PUBLIC
 EXPOSE conn

 query = "SELECT jws_header, jws_payload, jws_signature FROM 'order' ORDER BY Date_paid desc LIMIT 2;"
 prepStatement = conn~prepareStatement(query)
 queryResults = prepStatement~executeQuery
IF  queryResults~next THEN DO
    jws_header = queryResults~getString("jws_header")
    jws_payload = queryResults~getString("jws_payload")
    jws_signature = queryResults~getString("jws_signature")
END
IF jws_header = .nil THEN DO
    RETURN .nil
END
 jws_compact = jws_header || "." || jws_payload || "." || jws_signature
 RETURN jws_compact
------------------------------------------------------------------------------------------------------------------------
::METHOD insertDEPReceipt PUBLIC
 EXPOSE conn
 USE ARG certificate,datetime,  jws_header, jws_payload, jws_signature
 query = "INSERT INTO  DEPReceipt (certificate, Date_paid, jws_header, jws_payload, jws_signature) VALUES (?, ?, ?, ?, ?);"
 prepStatement = conn~prepareStatement(query)
 prepStatement~setString(1, certificate)
 prepStatement~setString(2, datetime)
 prepStatement~setString(3, jws_header)
 prepStatement~setString(4, jws_payload)
 prepStatement~setString(5, jws_signature)
 prepStatement~execute
------------------------------------------------------------------------------------------------------------------------
::METHOD getDEP PUBLIC
 EXPOSE conn FXCollections
 receiptArrayList = FXCollections~observableArrayList

 query = "SELECT * FROM 'DEPReceipt' ORDER BY Date_paid asc ;"
 prepStatement = conn~prepareStatement(query)
 queryResults = prepStatement~executeQuery
DO WHILE  queryResults~next
    certificate = queryResults~getString("certificate")
    jws_header = queryResults~getString("jws_header")
    jws_payload = queryResults~getString("jws_payload")
    jws_signature = queryResults~getString("jws_signature")
    receiptArrayList~add(.receiptDEP~new(certificate,jws_header, jws_payload, jws_signature ))
END
 RETURN receiptArrayList
------------------------------------------------------------------------------------------------------------------------
::REQUIRES "BSF.CLS"
::REQUIRES "PasswordHandler.CLS"
::REQUIRES "TableH.CLS"
::REQUIRES "collection.rexx"


/*   License:

     ------------------------ Apache Version 2.0 license -------------------------
        Copyright (C) 2021 Manuel Schwarzer

        Licensed under the Apache License, Version 2.0 (the "License");
        you may not use this file except in compliance with the License.
        You may obtain a copy of the License at

            http://www.apache.org/licenses/LICENSE-2.0

        Unless required by applicable law or agreed to in writing, software
        distributed under the License is distributed on an "AS IS" BASIS,
        WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
        See the License for the specific language governing permissions and
        limitations under the License.
     -----------------------------------------------------------------------------
 */