Welcome Anonymous

What would you like to do?


Paste code remember you name for one cookie

Pasted by Anonymous

Description None

On Monday 17 Jul 06:06

Language Java

Url http://paste.ideaslabs.com/show/RXCMT6EGVQ

Download Here

  1. package control;
  2.  
  3. import java.io.IOException;
  4. import java.net.InetAddress;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.time.Instant;
  9. import java.time.LocalDate;
  10. import java.time.ZoneId;
  11. import model.DBConnection;
  12. import org.apache.commons.net.ntp.NTPUDPClient;
  13. import org.apache.commons.net.ntp.TimeInfo;
  14.  
  15. /**
  16.  * This class provides the user with the basic CRUD operations.
  17.  *
  18.  * @author Daniele Cuomo, Luca Gaudino, Mattia Formisano
  19.  */
  20. public class DataController{
  21.     public static DBConnection db;
  22.    
  23.     private static Double FIXED_COST = 1.50, SHIPPING_COST = 5.0;
  24.    
  25.     public static final String INJUNCTIONQUERY = "SELECT idInjunction, Amount, Customer FROM injunction JOIN customer WHERE Customer = FC %s";
  26.     public static final String EVADERQUERY = "SELECT name, surname, FC FROM customer C JOIN expiredbill B WHERE idInjunction IS NULL AND (C.FC = B.customer) %s GROUP BY FC";
  27.    
  28.     /**
  29.      * Establishes connection with the database and updates it if necessary
  30.      */
  31.     public static void init(){
  32.         db = DBConnection.getInstance();
  33.        
  34.         DataController.generateBills();
  35.         DataController.updateExpiryBills();
  36.     }
  37.    
  38.     /**
  39.      * Asks the database to update the injunction composed by the operator
  40.      *
  41.      * @param bills the sequence of bills identifiers
  42.      * @param id    the primary injunction key
  43.      */    
  44.     public static void updateInjunction(String bills, Float amount, Object id) {
  45.         try {
  46.             update("injunction SET ExpiredBills = ?, Amount = ? WHERE idInjunction = ?", bills, amount, id);
  47.         } catch (SQLException e) {
  48.             ViewController.printErr(e);
  49.             return;
  50.         }
  51.         ViewController.successPopup();
  52.     }
  53.  
  54.     /**
  55.      * Asks the database to delete the injunction selected by the operator
  56.      *
  57.      * @param id    the primary injunction key
  58.      */
  59.     public static void deleteInjunction(Object id) {
  60.         try {
  61.             delete("injunction WHERE idInjunction = ?", id);
  62.         } catch (SQLException e) {
  63.             ViewController.printErr(e);
  64.         }
  65.         ViewController.successPopup();
  66.     }
  67.  
  68.     /**
  69.      * Asks the database to create the injunction composed by the operator
  70.      *
  71.      * @param bills     the sequence of bills identifiers
  72.      * @param cust      the primary customer key
  73.      * @param amount    value obtained through bills
  74.      */
  75.     public static void createInjunction(String bills, Object cust, Float amount) {
  76.         try {
  77.             insert("injunction(amount, customer, expiredbills) VALUES( ?, ?, ? )", amount, cust, bills);
  78.         } catch (SQLException e) {
  79.             ViewController.printErr(e);
  80.             return;
  81.         }
  82.         ViewController.successPopup();
  83.     }
  84.    
  85.     /**
  86.      * Generate bills every quarter
  87.      */
  88.     public static void generateBills(){
  89.         try{
  90.             int request = 0;
  91.             ResultSet answer = query("call lgaudino_ingdb.checkCreateBill();");
  92.             if( answer.first() )
  93.                 request = answer.getInt(1);
  94.             if(request == 0){ return; }
  95.  
  96.             ResultSet customers = query("SELECT FC FROM customer");
  97.             String fc;
  98.             while( customers.next() ){
  99.                 fc = customers.getString(1);
  100.                 LocalDate today = getToday();
  101.                 insert("unpaidbill(Amount, ReleaseDate, ExpiryDate, Customer) VALUES( ?, ?, ? )",
  102.                     getAmount(), today.toString(), today.plusMonths(2).toString(), fc);
  103.             }
  104.         }
  105.         catch(SQLException e){ ViewController.printErr(e); }
  106.     }
  107.    
  108.     /**
  109.      * Move the expired bills in the appropriate table
  110.      */
  111.     public static void updateExpiryBills(){
  112.         try{
  113.             ResultSet rs = query("SELECT * FROM unpaidbill WHERE ExpiryDate <= " + getToday().toString());
  114.             while( rs.next() ){
  115.                 insert("expiredbill(Amount, Issue, Expiry, Customer) VALUES(?, ?, ?, ?)",
  116.                         rs.getFloat(2), rs.getDate(3), rs.getDate(4), rs.getString(5) );
  117.                 delete("unpaidbill WHERE idUnpaidBill = ?", rs.getInt(1));
  118.             }
  119.         }
  120.         catch(SQLException e){ ViewController.printErr(e); }
  121.     }
  122.  
  123.     /**
  124.      * Returns a pseudo random number that simulates the costs of a water bill
  125.      *
  126.      * @return average consumption * fixed cost + shipping costs
  127.      */
  128.     private static String getAmount(){
  129.         Double tmp = 20 + Math.random()%40;
  130.         tmp = tmp*FIXED_COST + SHIPPING_COST;
  131.         return String.valueOf(tmp);
  132.     }
  133.    
  134.     /**
  135.      * Asks the network the current day or alternatively asks the system
  136.      *
  137.      * @return current day
  138.      */
  139.     private static LocalDate getToday(){
  140.         try{
  141.             String TIME_SERVER = "time-a.nist.gov";
  142.             NTPUDPClient timeClient = new NTPUDPClient();
  143.             InetAddress inetAddress = InetAddress.getByName(TIME_SERVER);
  144.             TimeInfo timeInfo = timeClient.getTime(inetAddress);
  145.             long returnTime = timeInfo.getMessage().getTransmitTimeStamp().getTime();
  146.            
  147.             LocalDate time = Instant.ofEpochMilli(returnTime).atZone(ZoneId.systemDefault()).toLocalDate();
  148.             return time;
  149.         }
  150.         catch( IOException e ){ return LocalDate.now(); }
  151.     }
  152.  
  153.     /**
  154.      * Returns all bills under the same injunction.
  155.      *
  156.      * @param idInjunction injunction primary key
  157.      * @return  bills formatted in a square matrix
  158.      */
  159.     public static String[][] getBills(String idInjunction) {
  160.         String[][] matrix = null;
  161.         try {
  162.             ResultSet rs = query("SELECT idexpiredbill, Issue, Expiry, Amount" + " FROM expiredbill WHERE idInjunction = " + idInjunction);
  163.             int j = 0;
  164.             int nClmn = rs.getMetaData().getColumnCount();
  165.             rs.last();
  166.             int nElem = rs.getRow();
  167.             matrix = new String[nElem][nClmn];
  168.             rs.beforeFirst();
  169.             while (rs.next()) {
  170.                 for (int i = 1; i <= nClmn; i++) {
  171.                     matrix[j][i - 1] = rs.getString(i);
  172.                 }
  173.                 j++;
  174.             }
  175.         } catch (SQLException e) {
  176.             ViewController.printErr(e);
  177.         }
  178.         return matrix;
  179.     }
  180.  
  181.     /**
  182.      * Asks database to insert a new row
  183.      *
  184.      * @param format    user's request
  185.      * @param values    values passed separately to protect the database
  186.      */
  187.     public static void insert(String format, Object... values) throws SQLException {
  188.         String exe = String.format("INSERT INTO %s", format);
  189.         PreparedStatement stmt = db.conn.prepareStatement(exe);
  190.         for (int i = 0; i < values.length; i++) {
  191.             stmt.setString(i + 1, values[i].toString());
  192.         }
  193.         stmt.executeUpdate();
  194.     }
  195.  
  196.     /**
  197.      * Asks database to update a row
  198.      *
  199.      * @param format    user's request
  200.      * @param values    values passed separately to protect the database
  201.      */
  202.     public static void update(String format, Object... values) throws SQLException {
  203.         String exe = String.format("UPDATE %s", format);
  204.         PreparedStatement stmt = db.conn.prepareStatement(exe);
  205.         for (int i = 0; i < values.length; i++) {
  206.             stmt.setString(i + 1, values[i].toString());
  207.         }
  208.         stmt.executeUpdate();
  209.     }
  210.  
  211.     /**
  212.      * Asks database to execute a query
  213.      *
  214.      * @param Q the query string
  215.      * @return  the result returned by the database
  216.      */
  217.     public static ResultSet query(String Q)throws SQLException {
  218.         PreparedStatement stmt;
  219.         stmt = db.conn.prepareStatement(Q, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  220.         return stmt.executeQuery();
  221.     }
  222.  
  223.     /**
  224.      * Asks database to delete a row
  225.      *
  226.      * @param format    user's request
  227.      * @param values    values passed separately to protect the database
  228.      */
  229.     public static void delete(String format, Object... values) throws SQLException {
  230.         String exe = String.format("DELETE FROM %s", format);
  231.         PreparedStatement stmt = db.conn.prepareStatement(exe);
  232.         for (int i = 0; i < values.length; i++) {
  233.             stmt.setString(i + 1, values[i].toString());
  234.         }
  235.         stmt.executeUpdate();
  236.     }
  237.  
  238.     /**
  239.      * Verifies if the operator is present in the database
  240.      *
  241.      * @param usr   the operator's username
  242.      * @param pwd   the operator's password
  243.      * @return true if the connection is successful, false otherwise
  244.      */
  245.     public static boolean access(String usr, String pwd) {
  246.         /* Query composition */
  247.         String Q = "SELECT * FROM operator WHERE Username = '" + usr + "' AND Password = '" + pwd + "'";
  248.         try{
  249.             ResultSet rs = query(Q);
  250.             return rs.first();
  251.         }catch(SQLException e){ ViewController.printErr(e); }
  252.         return false;
  253.     }
  254. }