Year xref

View Javadoc
1   /*
2    * Copyright (c) Patrick Magauran 2018.
3    *   Licensed under the AGPLv3. All conditions of said license apply.
4    *       This file is part of ABOS.
5    *
6    *       ABOS is free software: you can redistribute it and/or modify
7    *       it under the terms of the GNU Affero General Public License as published by
8    *       the Free Software Foundation, either version 3 of the License, or
9    *       (at your option) any later version.
10   *
11   *       ABOS is distributed in the hope that it will be useful,
12   *       but WITHOUT ANY WARRANTY; without even the implied warranty of
13   *       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14   *       GNU Affero General Public License for more details.
15   *
16   *       You should have received a copy of the GNU Affero General Public License
17   *       along with ABOS.  If not, see <http://www.gnu.org/licenses/>.
18   */
19  
20  package Utilities;/*
21   * Copyright (c) Patrick Magauran 2018.
22   *   Licensed under the AGPLv3. All conditions of said license apply.
23   *       This file is part of ABOS.
24   *
25   *       ABOS is free software: you can redistribute it and/or modify
26   *       it under the terms of the GNU Affero General Public License as published by
27   *       the Free Software Foundation, either version 3 of the License, or
28   *       (at your option) any later version.
29   *
30   *       ABOS is distributed in the hope that it will be useful,
31   *       but WITHOUT ANY WARRANTY; without even the implied warranty of
32   *       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
33   *       GNU Affero General Public License for more details.
34   *
35   *       You should have received a copy of the GNU Affero General Public License
36   *       along with ABOS.  If not, see <http://www.gnu.org/licenses/>.
37   */
38  
39  import Exceptions.AccessException;
40  import javafx.collections.ObservableList;
41  import javafx.scene.control.Alert;
42  import javafx.scene.control.ButtonType;
43  import org.apache.commons.lang3.RandomStringUtils;
44  
45  import java.math.BigDecimal;
46  import java.security.SecureRandom;
47  import java.sql.*;
48  import java.sql.Date;
49  import java.util.*;
50  
51  /**
52   * Created by patrick on 7/27/16.
53   */
54  public class Year {
55      private static final int retInteger = 1;
56      private static final int retString = 2;
57      private static final int retBigDec = 3;
58      private final String year;
59      private final String uName;
60  
61      public Year(String year) {
62          this(year, "");
63      }
64  
65      public Year(String year, String uName) {
66          this.uName = uName;
67          this.year = year;
68      }
69  
70      public void deleteYear() {
71          Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
72          alert.setTitle("WARNING!");
73          alert.setHeaderText("You are about to delete an entire Year. This cannot be reversed");
74          alert.setContentText("Would you like to continue with the deletion?");
75  
76  
77          Optional<ButtonType> result = alert.showAndWait();
78          if (result.get() == ButtonType.OK) {
79              //DROP USER [ IF EXISTS ] user_name
80              try {
81                  DbInt.getUsers().forEach(user -> {
82                      user.deleteFromYear(year);
83                  });
84              } catch (Exception ignored) {
85              }
86              DbInt.deleteDb(year);
87              String command;
88              if (DbInt.getDatabaseVersion().greaterThanOrEqual("5.7")) {
89                  command = "DROP USER IF EXISTS `" + year + "'@'localhost'";
90              } else {
91                  command = "DROP USER '" + year + "'@'localhost'";
92              }
93              try (Connection con = DbInt.getConnection("Commons");
94                   PreparedStatement prep = con.prepareStatement(command, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
95                  prep.execute();
96              } catch (SQLException e) {
97                  LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
98              }
99              try (Connection con = DbInt.getConnection("Commons");
100                  PreparedStatement prep = con.prepareStatement("DELETE FROM Years WHERE Year=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
101                 prep.setString(1, year);
102                 prep.execute();
103             } catch (SQLException e) {
104                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
105             }
106 
107 
108         }
109     }
110 
111     public boolean addressExists(String address, String zipCode) {
112         Boolean exists = false;
113         try (Connection con = DbInt.getConnection(year);
114              PreparedStatement prep = con.prepareStatement("SELECT Name FROM customerview WHERE streetAddress=? AND Zip=? AND uName=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
115             prep.setString(1, address);
116             prep.setString(2, zipCode);
117             prep.setString(3, uName);
118             ResultSet rs = prep.executeQuery();
119 
120             while (rs.next()) {
121 
122                 exists = true;
123 
124             }
125             ////Utilities.DbInt.pCon.close()
126 
127         } catch (SQLException e) {
128             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
129         }
130         return exists;
131     }
132 
133     /**
134      * Creates Database for the year specified.
135      */
136     public void CreateDb(ObservableList<formattedProductProps> products, Collection<category> rowsCats) {
137         String prefix = DbInt.prefix;
138 
139 
140         if (DbInt.createDb(year)) {
141             char[] possibleCharacters = ("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789").toCharArray();
142             String randomStr = RandomStringUtils.random(15, 0, possibleCharacters.length - 1, false, false, possibleCharacters, new SecureRandom());
143             String createAndGrantCommand = "CREATE USER '" + year + "'@'localhost' IDENTIFIED BY '" + randomStr + "'";
144 
145             if (DbInt.getDatabaseVersion().greaterThanOrEqual("5.7")) {
146                 createAndGrantCommand = "CREATE USER IF NOT EXISTS '" + year + "'@'localhost' IDENTIFIED BY '" + randomStr + "'";
147             }
148             try (Connection con = DbInt.getConnection();
149                  PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
150 
151                 prep.addBatch(createAndGrantCommand);
152                 prep.executeBatch();
153             } catch (SQLException e) {
154                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
155             }
156             try (Connection con = DbInt.getConnection();
157                  PreparedStatement prep = con.prepareStatement("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
158 
159                 prep.addBatch("GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, SHOW VIEW, TRIGGER ON `" + DbInt.prefix + year + "`.* TO '" + year + "'@'localhost'");
160                 prep.executeBatch();
161             } catch (SQLException e) {
162                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
163             }
164             //Create Tables
165             //Create groups Table
166             try (Connection con = DbInt.getConnection(year);
167                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `groups` (\n" +
168                          "  `ID` int(11) NOT NULL AUTO_INCREMENT,\n" +
169                          "  `Name` varchar(45) NOT NULL,\n" +
170                          "  PRIMARY KEY (`ID`)\n" +
171                          ")", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
172                 prep.execute();
173             } catch (SQLException e) {
174                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
175             }
176             try (Connection con = DbInt.getConnection(year);
177                  PreparedStatement prep = con.prepareStatement("INSERT INTO groups(Name) Values('Ungrouped')", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
178                 prep.execute();
179             } catch (SQLException e) {
180                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
181             }
182             //Create Users Table
183             try (Connection con = DbInt.getConnection(year);
184                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `users` (\n" +
185                          "  `idusers` int(11) NOT NULL AUTO_INCREMENT,\n" +
186                          "  `userName` varchar(255) NOT NULL,\n" +
187                          "  `fullName` varchar(255) NOT NULL,\n" +
188                          "  `uManage` varchar(255) NOT NULL,\n" +
189                          "  `Admin` int(11) DEFAULT NULL,\n" +
190                          "  `ACL` int(11) NOT NULL DEFAULT 1,\n" +
191                          "  `commonsID` int(11) NOT NULL,\n" +
192                          "  `groupId` int(11) NULL,\n" +
193                          "  PRIMARY KEY (`idusers`),\n" +
194                          "UNIQUE INDEX `userName_UNIQUE` (`userName` ASC)," +
195                          "CONSTRAINT `fk_users_1` FOREIGN KEY (`groupId`) REFERENCES `groups` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
196                 prep.execute();
197             } catch (SQLException e) {
198                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
199             }
200             //Create Customers Table
201             try (Connection con = DbInt.getConnection(year);
202                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `customers` (\n" +
203                          "  `idcustomers` int(11) NOT NULL AUTO_INCREMENT,\n" +
204                          "  `uName` varchar(255) NOT NULL,\n" +
205                          "  `Name` varchar(255) NOT NULL,\n" +
206                          "  `streetAddress` varchar(255) NOT NULL,\n" +
207                          "  `City` varchar(255) NOT NULL,\n" +
208                          "  `State` varchar(255) NOT NULL,\n" +
209                          "  `Zip` varchar(5) NOT NULL,\n" +
210                          "  `Phone` varchar(255) NULL,\n" +
211                          "  `Email` varchar(255) NULL,\n" +
212                          "  `Lat` double NOT NULL,\n" +
213                          "  `Lon` double NOT NULL,\n" +
214                          "  `Ordered` int(11) DEFAULT NULL,\n" +
215                          "  `nH` int(11) DEFAULT NULL,\n" +
216                          "  `nI` int(11) DEFAULT NULL,\n" +
217                          "  `orderID` varchar(45) DEFAULT NULL,\n" +
218                          "  `Donation` DECIMAL(9,2) NULL,\n" +
219                          "  PRIMARY KEY (`idcustomers`),\n" +
220                          "KEY `fk_customers_1_idx` (`uName`),\n" +
221                          "CONSTRAINT `fk_customers_1` FOREIGN KEY (`uName`) REFERENCES `users` (`userName`) ON DELETE CASCADE ON UPDATE CASCADE)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
222                 prep.execute();
223             } catch (SQLException e) {
224                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
225             }
226             //Create Categories Table
227             try (Connection con = DbInt.getConnection(year);
228                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `categories` (\n" +
229                          "  `idcategories` INT NOT NULL AUTO_INCREMENT,\n" +
230                          "  `catName` VARCHAR(255) NOT NULL,\n" +
231                          "  `catDate` DATE NULL,\n" +
232                          "UNIQUE INDEX `catName_UNIQUE` (`catName` ASC)," +
233                          "  PRIMARY KEY (`idcategories`));\n", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
234                 prep.execute();
235             } catch (SQLException e) {
236                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
237             }
238             //Create Products Table
239             try (Connection con = DbInt.getConnection(year);
240                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `products` (\n" +
241                          "  `idproducts` int(11) NOT NULL AUTO_INCREMENT,\n" +
242                          "  `ID` varchar(255) NOT NULL,\n" +
243                          "  `Name` varchar(255) NOT NULL,\n" +
244                          "  `UnitSize` varchar(255) NOT NULL,\n" +
245                          "  `Cost` decimal(9,2) NOT NULL,\n" +
246                          "  `Category` varchar(255) NOT NULL,\n" +
247                          "UNIQUE INDEX `ID_UNIQUE` (`ID` ASC)," +
248                          "  PRIMARY KEY (`idproducts`)\n" +
249                          ")", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
250                 prep.execute();
251             } catch (SQLException e) {
252                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
253             }
254 
255 
256             //Create orders Table
257             try (Connection con = DbInt.getConnection(year);
258                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `orders` (\n" +
259                          "  `idOrders` int(11) NOT NULL AUTO_INCREMENT,\n" +
260                          "  `uName` varchar(255) NOT NULL COMMENT '\t',\n" +
261                          "  `custId` int(11) NOT NULL,\n" +
262                          "  `Cost` decimal(9,2) NOT NULL DEFAULT 0,\n" +
263                          "  `Quant` int(11) NOT NULL DEFAULT 0,\n" +
264                          "  `paid` int(11) NULL DEFAULT 0,\n" +
265                          "  `delivered` int(11) NULL DEFAULT 0,\n" +
266                          "  PRIMARY KEY (`idOrders`),\n" +
267                          "  KEY `fk_Orders_1_idx` (`custId`),\n" +
268                          "  CONSTRAINT `fk_Orders_1` FOREIGN KEY (`custId`) REFERENCES `customers` (`idcustomers`) ON DELETE CASCADE ON UPDATE CASCADE\n" +
269                          ")", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
270                 prep.execute();
271             } catch (SQLException e) {
272                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
273             }
274             //Create orderedProducts Table
275             try (Connection con = DbInt.getConnection(year);
276                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `ordered_products` (\n" +
277                          "  `idordered_products` int(11) NOT NULL AUTO_INCREMENT,\n" +
278                          "  `uName` varchar(255) NOT NULL,\n" +
279                          "  `custID` int(11) NOT NULL,\n" +
280                          "  `orderID` int(11) NOT NULL,\n" +
281                          "  `ProductId` int(11) NOT NULL,\n" +
282                          "  `Quantity` int(11) DEFAULT NULL,\n" +
283                          "  `ExtendedCost` decimal(9,2) DEFAULT NULL,\n" +
284                          "  PRIMARY KEY (`idordered_products`),\n" +
285                          "  KEY `fk_ordered_products_1_idx` (`custID`),\n" +
286                          "  KEY `fk_ordered_products_2_idx` (`orderID`),\n" +
287                          "  KEY `fk_ordered_products_3_idx` (`ProductId`),\n" +
288                          "  CONSTRAINT `fk_ordered_products_1` FOREIGN KEY (`custID`) REFERENCES `customers` (`idcustomers`) ON DELETE CASCADE ON UPDATE CASCADE,\n" +
289                          "  CONSTRAINT `fk_ordered_products_2` FOREIGN KEY (`orderID`) REFERENCES `orders` (`idOrders`) ON DELETE CASCADE ON UPDATE CASCADE,\n" +
290                          "  CONSTRAINT `fk_ordered_products_3` FOREIGN KEY (`ProductId`) REFERENCES `products` (`idproducts`) ON DELETE CASCADE ON UPDATE CASCADE\n" +
291                          ")", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
292                 prep.execute();
293             } catch (SQLException e) {
294                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
295             }
296             try (Connection con = DbInt.getConnection(year);
297                  PreparedStatement prep = con.prepareStatement("CREATE TABLE `Settings` (\n" +
298                          "  `key` VARCHAR(45) NOT NULL,\n" +
299                          "  `Value` VARCHAR(255) NULL,\n" +
300                          "  PRIMARY KEY (`key`));\n", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
301                 prep.execute();
302             } catch (SQLException e) {
303                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
304             }
305             //Create Triggers
306             try (Connection con = DbInt.getConnection(year);
307                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`ordered_products_BEFORE_INSERT` BEFORE INSERT ON `ordered_products` FOR EACH ROW\n" +
308                          "BEGIN\n" +
309                          "SET NEW.ExtendedCost = (NEW.Quantity * (SELECT Cost FROM products WHERE idproducts = NEW.ProductId));\n" +
310                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
311                 prep.execute();
312             } catch (SQLException e) {
313                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
314             }
315 
316             //Create Triggers
317             try (Connection con = DbInt.getConnection(year);
318                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`ordered_products_AFTER_INSERT` AFTER INSERT ON `ordered_products` FOR EACH ROW\n" +
319                          "BEGIN\n" +
320                          "UPDATE orders \n" +
321                          "SET \n" +
322                          "    Cost = (SELECT \n" +
323                          "            SUM(ExtendedCost)\n" +
324                          "        FROM\n" +
325                          "            ordered_products\n" +
326                          "        WHERE\n" +
327                          "            orderID = NEW.orderID)\n" +
328                          "WHERE\n" +
329                          "    idOrders = NEW.orderID;\n" +
330                          "UPDATE orders \n" +
331                          "SET \n" +
332                          "    Quant = (SELECT \n" +
333                          "            SUM(Quantity)\n" +
334                          "        FROM\n" +
335                          "            ordered_products\n" +
336                          "        WHERE\n" +
337                          "            orderID = NEW.orderID)\n" +
338                          "WHERE\n" +
339                          "    idOrders = NEW.orderID;\n" +
340                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
341                 prep.execute();
342             } catch (SQLException e) {
343                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
344             }
345             //Create Triggers
346             try (Connection con = DbInt.getConnection(year);
347                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`ordered_products_BEFORE_UPDATE` BEFORE UPDATE ON `ordered_products` FOR EACH ROW\n" +
348                          "BEGIN\n" +
349                          "SET NEW.ExtendedCost = (NEW.Quantity * (SELECT Cost FROM products WHERE idproducts = NEW.ProductId));\n" +
350                          "\n" +
351                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
352                 prep.execute();
353             } catch (SQLException e) {
354                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
355             }
356             //Create Triggers
357             try (Connection con = DbInt.getConnection(year);
358                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`ordered_products_AFTER_UPDATE` AFTER UPDATE ON `ordered_products` FOR EACH ROW\n" +
359                          "BEGIN\n" +
360                          "UPDATE orders \n" +
361                          "SET \n" +
362                          "    Cost = (SELECT \n" +
363                          "            SUM(ExtendedCost)\n" +
364                          "        FROM\n" +
365                          "            ordered_products\n" +
366                          "        WHERE\n" +
367                          "            orderID = NEW.orderID)\n" +
368                          "WHERE\n" +
369                          "    idOrders = NEW.orderID;\n" +
370                          "UPDATE orders \n" +
371                          "SET \n" +
372                          "    Quant = (SELECT \n" +
373                          "            SUM(Quantity)\n" +
374                          "        FROM\n" +
375                          "            ordered_products\n" +
376                          "        WHERE\n" +
377                          "            orderID = NEW.orderID)\n" +
378                          "WHERE\n" +
379                          "    idOrders = NEW.orderID;\n" +
380                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
381                 prep.execute();
382             } catch (SQLException e) {
383                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
384             }
385             try (Connection con = DbInt.getConnection(year);
386                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`orders_AFTER_INSERT` AFTER INSERT ON `orders` FOR EACH ROW\n" +
387                          "BEGIN\n" +
388                          "UPDATE customers SET orderID = NEW.idOrders, Ordered=1 WHERE idcustomers=NEW.custId;\n" +
389                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
390                 prep.execute();
391             } catch (SQLException e) {
392                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
393             }
394             try (Connection con = DbInt.getConnection(year);
395                  PreparedStatement prep = con.prepareStatement("CREATE DEFINER=`" + year + "`@`localhost` TRIGGER `" + prefix + year + "`.`products_AFTER_UPDATE` AFTER UPDATE ON `products` FOR EACH ROW\n" +
396                          "BEGIN\n" +
397                          "UPDATE ordered_products SET extendedCost = (quantity * NEW.Cost) WHERE ProductId = NEW.idproducts;\n" +
398                          "END", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
399                 prep.execute();
400             } catch (SQLException e) {
401                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
402             }
403             //Create Views
404             try (Connection con = DbInt.getConnection(year);
405                  PreparedStatement prep = con.prepareStatement("CREATE ALGORITHM=UNDEFINED DEFINER=`" + year + "`@`localhost` SQL SECURITY DEFINER VIEW `" + prefix + year + "`.`usersview` AS select `" + prefix + year + "`.`users`.`idusers` AS `idusers`,`" + prefix + year + "`.`users`.`userName` AS `userName`,`" + prefix + year + "`.`users`.`fullName` AS `fullName`,`" + prefix + year + "`.`users`.`uManage` AS `uManage`,`" + prefix + year + "`.`users`.`Admin` AS `Admin`,`" + prefix + year + "`.`users`.`ACL` AS `ACL`,`" + prefix + year + "`.`users`.`commonsID` AS `commonsID`,\n" +
406                          "        `users`.`groupId` AS `groupId` from `" + prefix + year + "`.`users` where (`" + prefix + year + "`.`users`.`userName` = LEFT(USER(),LOCATE('@',USER()) - 1)) WITH CASCADED CHECK OPTION;\n", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
407                 prep.execute();
408             } catch (SQLException e) {
409                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
410             }
411 
412 
413             //Create Views
414             try (Connection con = DbInt.getConnection(year);
415                  PreparedStatement prep = con.prepareStatement("CREATE \n" +
416                          "    ALGORITHM = UNDEFINED \n" +
417                          "    DEFINER = `" + year + "`@`localhost` \n" +
418                          "    SQL SECURITY DEFINER\n" +
419                          "VIEW `customerview` AS\n" +
420                          "    SELECT \n" +
421                          "        `customers`.`idcustomers` AS `idcustomers`,\n" +
422                          "        `customers`.`uName` AS `uName`,\n" +
423                          "        `customers`.`Name` AS `Name`,\n" +
424                          "        `customers`.`streetAddress` AS `streetAddress`,\n" +
425                          "        `customers`.`City` AS `City`,\n" +
426                          "        `customers`.`State` AS `State`,\n" +
427                          "        `customers`.`Zip` AS `Zip`,\n" +
428                          "        `customers`.`Phone` AS `Phone`,\n" +
429                          "        `customers`.`Email` AS `Email`,\n" +
430                          "        `customers`.`Lat` AS `Lat`,\n" +
431                          "        `customers`.`Lon` AS `Lon`,\n" +
432                          "        `customers`.`Ordered` AS `Ordered`,\n" +
433                          "        `customers`.`nH` AS `nH`,\n" +
434                          "        `customers`.`nI` AS `nI`,\n" +
435                          "        `customers`.`orderID` AS `orderID`,\n" +
436                          "        `customers`.`Donation` AS `Donation`\n" +
437                          "    FROM\n" +
438                          "        `customers`\n" +
439                          "    WHERE\n" +
440                          "        FIND_IN_SET(`customers`.`uName`,\n" +
441                          "                (SELECT \n" +
442                          "                        `usersview`.`uManage`\n" +
443                          "                    FROM\n" +
444                          "                        `usersview`)) WITH CASCADED CHECK OPTION", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
445                 prep.execute();
446             } catch (SQLException e) {
447                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
448             }
449             try (Connection con = DbInt.getConnection(year);
450                  PreparedStatement prep = con.prepareStatement("CREATE \n" +
451                          "    ALGORITHM = UNDEFINED \n" +
452                          "    DEFINER = `" + year + "`@`localhost` \n" +
453                          "    SQL SECURITY DEFINER\n" +
454                          "VIEW `orderedproductsview` AS\n" +
455                          "    SELECT \n" +
456                          "        `ordered_products`.`idordered_products` AS `idordered_products`,\n" +
457                          "        `ordered_products`.`custID` AS `custID`,\n" +
458                          "        `ordered_products`.`uName` AS `uName`,\n" +
459                          "        `ordered_products`.`orderID` AS `orderID`,\n" +
460                          "        `ordered_products`.`ProductId` AS `ProductId`,\n" +
461                          "        `ordered_products`.`Quantity` AS `Quantity`,\n" +
462                          "        `ordered_products`.`ExtendedCost` AS `ExtendedCost`\n" +
463                          "    FROM\n" +
464                          "        `ordered_products`\n" +
465                          "    WHERE\n" +
466                          "        FIND_IN_SET(`ordered_products`.`uName`,\n" +
467                          "                (SELECT \n" +
468                          "                        `usersview`.`uManage`\n" +
469                          "                    FROM\n" +
470                          "                        `usersview`)) WITH CASCADED CHECK OPTION", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
471                 prep.execute();
472             } catch (SQLException e) {
473                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
474             }
475             try (Connection con = DbInt.getConnection(year);
476                  PreparedStatement prep = con.prepareStatement("CREATE \n" +
477                          "    ALGORITHM = UNDEFINED \n" +
478                          "    DEFINER = `" + year + "`@`localhost` \n" +
479                          "    SQL SECURITY DEFINER\n" +
480                          "VIEW `ordersview` AS\n" +
481                          "    SELECT \n" +
482                          "        `orders`.`idOrders` AS `idOrders`,\n" +
483                          "        `orders`.`uName` AS `uName`,\n" +
484                          "        `orders`.`custId` AS `custId`,\n" +
485                          "        `orders`.`Cost` AS `Cost`,\n" +
486                          "        `orders`.`Quant` AS `Quant`,\n" +
487                          "        `orders`.`paid` AS `paid`,\n" +
488                          "        `orders`.`delivered` AS `delivered`\n" +
489                          "    FROM\n" +
490                          "        `orders`\n" +
491                          "    WHERE\n" +
492                          "        FIND_IN_SET(`orders`.`uName`,\n" +
493                          "                (SELECT \n" +
494                          "                        `usersview`.`uManage`\n" +
495                          "                    FROM\n" +
496                          "                        `usersview`)) WITH CASCADED CHECK OPTION", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
497                 prep.execute();
498             } catch (SQLException e) {
499                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
500             }
501 
502 
503 
504 /*
505             //Create Categories Table
506             try (PreparedStatement prep = Utilities.DbInt.getPrep(year, "CREATE TABLE Categories(ID int(11) NOT NULL AUTO_INCREMENT,Name varchar(255), Date DATE )")) {
507                 prep.execute();
508             } catch (SQLException e) {
509                 Utilities.LogToFile.log(e, Utilities.Severity.SEVERE, Utilities.CommonErrors.returnSqlMessage(e));
510             }*/
511 
512             //Insert products into Product table
513             //Insert products into Product table
514             //  String col = "";
515 
516             // col = String.format("%s, \"%s\" VARCHAR(255)", col, Integer.toString(i));
517             try (Connection con = DbInt.getConnection(year);
518                  PreparedStatement prep = con.prepareStatement("INSERT INTO products(ID, Name, Cost, UnitSize, Category) VALUES (?,?,?,?,?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
519                 for (formattedProductProps curRow : products) {
520                     String cat = (curRow.getProductCategory() != null) ? curRow.getProductCategory() : "";
521                     prep.setString(1, curRow.getProductID());
522                     prep.setString(2, curRow.getProductName());
523                     prep.setBigDecimal(3, curRow.getProductUnitPrice());
524                     prep.setString(4, curRow.getProductSize());
525                     prep.setString(5, cat);
526                     prep.execute();
527                 }
528             } catch (SQLException e) {
529                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
530             }
531 
532             //Add Categories
533             rowsCats.forEach(cat -> {
534                 try (Connection con = DbInt.getConnection(year);
535                      PreparedStatement prep = con.prepareStatement("INSERT INTO categories(catName, catDate) VALUES (?,?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
536                     prep.setString(1, cat.catName);
537                     prep.setDate(2, Date.valueOf(cat.catDate));
538 
539                     prep.execute();
540                 } catch (SQLException e) {
541                     LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
542                 }
543             });
544 
545             try (Connection con = DbInt.getConnection(year);
546                  PreparedStatement prep = con.prepareStatement("INSERT INTO Settings(key, Value) Values (?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
547                 prep.setString(1, "Version");
548                 prep.setString(2, Config.getProgramVersion().toString());
549 
550                 prep.execute();
551             } catch (SQLException e) {
552                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
553             }
554 
555             addYear();
556         } else {
557             updateDb(year, products, rowsCats);
558         }
559 
560     }
561 
562     public void updateDb(String year, ObservableList<formattedProductProps> products, Collection<category> rowsCats) {
563         //Delete Utilities.Year Utilities.Customer table
564 
565        /* try (Connection con = Utilities.DbInt.getConnection(year);
566              PreparedStatement addCol = con.prepareStatement("DELETE FROM products", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
567             addCol.addBatch("DELETE FROM products");
568             addCol.addBatch("ALTER TABLE products AUTO_INCREMENT = 1");
569             addCol.executeBatch();
570         } catch (SQLException e) {
571             Utilities.LogToFile.log(e, Utilities.Severity.SEVERE, Utilities.CommonErrors.returnSqlMessage(e));
572         }
573 
574         try (Connection con = Utilities.DbInt.getConnection(year);
575              PreparedStatement addCol = con.prepareStatement("TRUCNATE TABLE categories", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
576             addCol.addBatch("DELETE FROM categories");
577             addCol.addBatch("ALTER TABLE categories AUTO_INCREMENT = 1");
578             addCol.executeBatch();
579         } catch (SQLException e) {
580             Utilities.LogToFile.log(e, Utilities.Severity.SEVERE, Utilities.CommonErrors.returnSqlMessage(e));
581         }*/
582         //Recreate Utilities.Year Utilities.Customer table
583 
584         //Create Products Table
585 
586 
587      /*   try (PreparedStatement prep = Utilities.DbInt.getPrep(year, "CREATE TABLE Categories(ID int PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),Name varchar(255), Date DATE)")) {
588             prep.execute();
589         } catch (SQLException e) {
590             Utilities.LogToFile.log(e, Utilities.Severity.SEVERE, Utilities.CommonErrors.returnSqlMessage(e));
591         }*/
592         //Add Categories
593         //Add Categories
594         rowsCats.forEach(cat -> {
595             try (Connection con = DbInt.getConnection(year);
596 
597                  PreparedStatement prep = con.prepareStatement("INSERT INTO categories(catName, catDate) VALUES (?,?)" +
598                          "ON DUPLICATE KEY UPDATE catName=?, catDate=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
599                 prep.setString(1, cat.catName);
600                 prep.setDate(2, Date.valueOf(cat.catDate));
601                 prep.setString(3, cat.catName);
602                 prep.setDate(4, Date.valueOf(cat.catDate));
603 
604                 prep.execute();
605             } catch (SQLException e) {
606                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
607             }
608         });
609         //Insert products into Product table
610 
611         try (Connection con = DbInt.getConnection(year);
612              PreparedStatement prep = con.prepareStatement("INSERT INTO products(ID, Name, Cost, UnitSize, Category) VALUES (?,?,?,?,?)" +
613                      "ON DUPLICATE KEY UPDATE ID=?, Name=?, Cost=?, UnitSize=?, Category=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
614             for (formattedProductProps curRow : products) {
615                 String cat = (curRow.getProductCategory() != null) ? curRow.getProductCategory() : "";
616                 prep.setString(1, curRow.getProductID());
617                 prep.setString(2, curRow.getProductName());
618                 prep.setBigDecimal(3, curRow.getProductUnitPrice());
619                 prep.setString(4, curRow.getProductSize());
620                 prep.setString(5, cat);
621 
622                 prep.setString(6, curRow.getProductID());
623                 prep.setString(7, curRow.getProductName());
624                 prep.setBigDecimal(8, curRow.getProductUnitPrice());
625                 prep.setString(9, curRow.getProductSize());
626                 prep.setString(10, cat);
627                 prep.execute();
628             }
629         } catch (SQLException e) {
630             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
631         }
632     }
633 
634     public void addYear() {
635         try (Connection con = DbInt.getConnection("Commons");
636              PreparedStatement prep = con.prepareStatement("INSERT INTO Years(Year) VALUES (?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
637             prep.setString(1, year);
638             prep.execute();
639         } catch (SQLException e) {
640             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
641         }
642     }
643 
644     public ArrayList<User> getUsers() throws AccessException {
645         if (DbInt.isAdmin()) {
646             ArrayList<User> ret = new ArrayList<>();
647             try (Connection con = DbInt.getConnection(year);
648                  PreparedStatement prep = con.prepareStatement("SELECT userName, fullName, Admin, groupId, uManage, ACL FROM users", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
649                  ResultSet rs = prep.executeQuery()) {
650                 while (rs.next()) {
651                     if (rs.getInt("ACL") > 0) {
652                         ret.add(new User(rs.getString("userName"), rs.getString("fullName"), rs.getString("uManage"), DbInt.getYearsForUser(rs.getString("userName")), rs.getInt("Admin") == 1, rs.getInt("groupId")));
653                     }
654 
655                 }
656                 ////Utilities.DbInt.pCon.close()
657 
658             } catch (SQLException e) {
659                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
660             }
661             return ret;
662         } else {
663             throw new AccessException("You are not Admin.");
664         }
665     }
666 
667     private Object getTots(String info, int retType) {
668         Object ret = "";
669         if (Objects.equals(info, "Donations")) {
670             try (Connection con = DbInt.getConnection(year);
671                  PreparedStatement prep = con.prepareStatement("SELECT Doantion FROM customerview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
672                 prep.setString(1, uName);
673 
674                 try (ResultSet rs = prep.executeQuery()) {
675 
676 
677                     //prep.setString(1, info);
678 
679 
680                     while (rs.next()) {
681                         switch (retType) {
682                             case retBigDec:
683                                 ret = rs.getBigDecimal("Donation");
684                                 break;
685 
686                         }
687 
688                     }
689                 }
690                 //////Utilities.DbInt.pCon.close()
691 
692             } catch (SQLException e) {
693                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
694             }
695         } else {
696             try (Connection con = DbInt.getConnection(year);
697                  PreparedStatement prep = con.prepareStatement("SELECT * FROM ordersview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
698                 prep.setString(1, uName);
699 
700                 try (ResultSet rs = prep.executeQuery()) {
701 
702                     //prep.setString(1, info);
703 
704 
705                     while (rs.next()) {
706                         switch (retType) {
707                             case retInteger:
708                                 ret = rs.getInt(info);
709                                 break;
710                             case retString:
711                                 ret = rs.getString(info);
712                                 break;
713                             case retBigDec:
714                                 ret = rs.getBigDecimal(info);
715                                 break;
716 
717                         }
718 
719                     }
720                     //////Utilities.DbInt.pCon.close();
721                 }
722             } catch (SQLException e) {
723                 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
724             }
725         }
726 
727         return ret;
728     }
729 
730     public formattedProduct[] getAllProducts() {
731         //String[] toGet = {"ID", "PNAME", "SIZE", "UNIT"};
732         List<formattedProduct> ProductInfoArray = new ArrayList<>(); //Single array to store all data to add to table.
733         //Get a prepared statement to retrieve data
734 
735         try (Connection con = DbInt.getConnection(year);
736              PreparedStatement prep = con.prepareStatement("SELECT * FROM products", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
737              ResultSet ProductInfoResultSet = prep.executeQuery()) {
738             //Run through Data set and add info to ProductInfoArray
739             while (ProductInfoResultSet.next()) {
740 
741                 ProductInfoArray.add(new formattedProduct(ProductInfoResultSet.getInt("idproducts"), ProductInfoResultSet.getString("ID"), ProductInfoResultSet.getString("Name"), ProductInfoResultSet.getString("UnitSize"), ProductInfoResultSet.getBigDecimal("Cost"), ProductInfoResultSet.getString("Category"), 0, BigDecimal.ZERO));
742             }
743         } catch (SQLException e) {
744             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
745         }
746         return ProductInfoArray.toArray(new formattedProduct[0]);
747 
748     }
749 
750     public Iterable<String> getCustomerNames() {
751         Collection<String> ret = new ArrayList<>();
752 
753         try (Connection con = DbInt.getConnection(year);
754              PreparedStatement prep = con.prepareStatement("SELECT Name FROM customerview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
755             prep.setString(1, uName);
756 
757             try (ResultSet rs = prep.executeQuery()) {
758 
759 
760                 while (rs.next()) {
761 
762                     ret.add(rs.getString("Name"));
763 
764                 }
765             }
766             ////Utilities.DbInt.pCon.close()
767 
768         } catch (SQLException e) {
769             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
770         }
771 
772 
773         return ret;
774     }
775 
776     public Iterable<String> getCustomerNames(String user) {
777         Collection<String> ret = new ArrayList<>();
778 
779         try (Connection con = DbInt.getConnection(year);
780              PreparedStatement prep = con.prepareStatement("SELECT Name FROM customerview  WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
781             prep.setString(1, uName);
782 
783             try (ResultSet rs = prep.executeQuery()) {
784 
785 
786                 while (rs.next()) {
787 
788                     ret.add(rs.getString("Name"));
789 
790                 }
791             }
792             ////Utilities.DbInt.pCon.close()
793 
794         } catch (SQLException e) {
795             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
796         }
797 
798 
799         return ret;
800     }
801 
802     public Iterable<Customer> getCustomers() {
803         return getCustomers(uName);
804     }
805 
806     public Iterable<Customer> getCustomers(String user) {
807         Collection<Customer> ret = new ArrayList<>();
808 
809         try (Connection con = DbInt.getConnection(year);
810              PreparedStatement prep = con.prepareStatement("SELECT `idCustomers`, `uName`,\n" +
811                      "    `Name`,\n" +
812                      "    `streetAddress`,\n" +
813                      "    `City`,\n" +
814                      "    `State`,\n" +
815                      "    `Zip`,\n" +
816                      "    `Phone`,\n" +
817                      "    `Email`,\n" +
818                      "    `Lat`,\n" +
819                      "    `Lon`,\n" +
820                      "    `Ordered`,\n" +
821                      "    `nH`,\n" +
822                      "    `nI`,\n" +
823                      "    `orderID`,\n" +
824                      "    `Donation` FROM customerview  WHERE " + (Objects.equals(user, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
825             prep.setString(1, user);
826 
827             try (ResultSet rs = prep.executeQuery()) {
828 
829 
830                 while (rs.next()) {
831 //        this(-1, name, year, null, null, null, null, null, null, null, false, false, null, null, null, Utilities.DbInt.getUserName());
832 
833                     ret.add(new Customer(rs.getInt("idCustomers"), rs.getString("Name"), year, rs.getString("streetAddress"), rs.getString("City"), rs.getString("State"), rs.getString("Zip"), rs.getDouble("lat"), rs.getDouble("lon"), rs.getString("Phone"), null, null, rs.getString("Email"), null, rs.getBigDecimal("Donation"), rs.getString("uName")));
834 
835                 }
836             }
837             ////Utilities.DbInt.pCon.close()
838 
839         } catch (SQLException e) {
840             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
841         }
842 
843 
844         return ret;
845     }
846 
847     public Iterable<category> getCategories() {
848         Collection<category> ret = new ArrayList<>();
849 
850         try (Connection con = DbInt.getConnection(year);
851              PreparedStatement prep = con.prepareStatement("SELECT * FROM categories", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
852              ResultSet rs = prep.executeQuery()) {
853             while (rs.next()) {
854 
855                 ret.add(new category(rs.getString("catName"), rs.getString("catDate")));
856                 ////Utilities.DbInt.pCon.close();
857             }
858         } catch (SQLException e) {
859             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
860         }
861 
862 
863         return ret;
864     }
865 
866     /**
867      * Gets the Total Donations Using getTots Function
868      *
869      * @return The total donation amount
870      */
871     public BigDecimal getDonations() {
872         BigDecimal ret = BigDecimal.ZERO;
873 
874         try (Connection con = DbInt.getConnection(year);
875              PreparedStatement prep = con.prepareStatement("SELECT Donation FROM customerview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
876             prep.setString(1, uName);
877 
878             try (ResultSet rs = prep.executeQuery()) {
879 
880                 //prep.setString(1, info);
881 
882 
883                 while (rs.next()) {
884                     ret = ret.add(rs.getBigDecimal("Donation"));
885 
886 
887                 }
888             }
889             //////Utilities.DbInt.pCon.close()
890 
891         } catch (SQLException e) {
892             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
893         }
894         return ret;
895     }
896 
897     public BigDecimal getOT() {
898         BigDecimal ret = BigDecimal.ZERO;
899         try (Connection con = DbInt.getConnection(year);
900              PreparedStatement prep = con.prepareStatement("SELECT Cost FROM ordersview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
901             prep.setString(1, uName);
902 
903             try (ResultSet rs = prep.executeQuery()) {
904 
905                 //prep.setString(1, info);
906 
907 
908                 while (rs.next()) {
909 
910                     ret = ret.add(rs.getBigDecimal("Cost"));
911 
912                 }
913             }
914 
915             //////Utilities.DbInt.pCon.close()
916 
917         } catch (SQLException e) {
918             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
919         }
920         return ret;
921     }
922 
923     /**
924      * Gets the Total Utilities.Customer Using getTots Function
925      *
926      * @return The total amount of Customers
927      */
928     public int getNoCustomers() {
929         int ret = 0;
930         try (Connection con = DbInt.getConnection(year);
931              PreparedStatement prep = con.prepareStatement("SELECT COUNT(*) FROM customerview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
932             prep.setString(1, uName);
933 
934             try (ResultSet rs = prep.executeQuery()) {
935 
936                 //prep.setString(1, info);
937 
938 
939                 while (rs.next()) {
940 
941                     ret = rs.getInt("COUNT(*)");
942 
943                 }
944             }
945 
946             //////Utilities.DbInt.pCon.close()
947 
948         } catch (SQLException e) {
949             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
950         }
951         return ret;
952     }
953 
954     /**
955      * Gets the Total Commissions Using getTots Function
956      *
957      * @return The total Commissions amount
958      */
959     public BigDecimal getCommis() {
960         BigDecimal totalCost = getGTot();
961         BigDecimal commision = BigDecimal.ZERO;
962         if ((totalCost.compareTo(new BigDecimal("299.99")) > 0) && (totalCost.compareTo(new BigDecimal("500.01")) < 0)) {
963             commision = totalCost.multiply(new BigDecimal("0.05"));
964         } else if ((totalCost.compareTo(new BigDecimal("500.01")) > 0) && (totalCost.compareTo(new BigDecimal("1000.99")) < 0)) {
965             commision = totalCost.multiply(new BigDecimal("0.1"));
966         } else if (totalCost.compareTo(new BigDecimal("1000")) >= 0) {
967             commision = totalCost.multiply(new BigDecimal("0.15"));
968         }
969         return commision;
970     }
971 
972     /**
973      * Gets the Grand Total Using getTots Function
974      * aeaeaeae
975      *
976      * @return The Grand total amount
977      */
978     public BigDecimal getGTot() {
979         return getDonations().add(getOT());
980     }
981 
982     public int getQuant() {
983         int ret = 0;
984         try (Connection con = DbInt.getConnection(year);
985              PreparedStatement prep = con.prepareStatement("SELECT SUM(Quant) FROM ordersview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?"), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
986             prep.setString(1, uName);
987 
988             try (ResultSet rs = prep.executeQuery()) {
989 
990                 //prep.setString(1, info);
991 
992 
993                 while (rs.next()) {
994 
995                     ret = rs.getInt("SUM(Quant)");
996 
997                 }
998             }
999 
1000             //////Utilities.DbInt.pCon.close()
1001 
1002         } catch (SQLException e) {
1003             LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
1004         }
1005         return ret;
1006     }
1007 
1008     public static class category {
1009         public String catName;
1010         public String catDate;
1011 
1012         public category(String name, String date) {
1013             catName = name;
1014             catDate = date;
1015         }
1016     }
1017 }