rendered paste bodypackage com.myth.base;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import javax.print.DocFlavor.STRING;import com.myth.gui.StatusBar;import com.myth.util.Bill;import com.myth.util.BillItem;import com.myth.util.Filiale;import com.myth.util.Login;import com.myth.util.Product;public class DBManager{ private static DBManager _instance; private static String QRY_STORE_PRODUCTS = "INSERT INTO transaction_items VALUES (?, ?, ?, ?)"; private static String QRY_INSERT_BILLING = "INSERT INTO transactions VALUES (?, ?, ?)"; private static String QRY_SELECT_PRODUCTS_BY_EAN = "SELECT artikel.id, artikel.ean, gattung.bez_deu AS gattung," + "fantasiename.bezeichnung AS fantasiename, ausfuehrung.ausf_deu AS ausfuehrung, farbe.farbe_deu AS farbe," + "groessen.groesse AS groesse, material.mat_deu AS material, artikel.menge AS menge, artikel.minmenge AS minmenge," + "artikel.ekmod AS ekmod, artikel.vkmod AS vkmod, artikelgruppen.ek AS ek, artikelgruppen.vk AS vk, mwstsaetze.satz AS mwst" + "FROM artikel" + "LEFT JOIN artikelgruppen ON artikel.gruppe = artikelgruppen.id" + "LEFT JOIN gattung ON artikelgruppen.gattung = gattung.id" + "LEFT JOIN fantasiename ON artikelgruppen.fantasiename = fantasiename.id" + "LEFT JOIN mwstsaetze ON artikelgruppen.ustsatz = mwstsaetze.id" + "LEFT JOIN ausfuehrung ON artikel.ausf = ausfuehrung.id" + "LEFT JOIN farbe ON artikel.farbe = farbe.id" + "LEFT JOIN groessen ON artikel.groesse = groessen.id" + "LEFT JOIN material ON artikel.material = material.id" + "WHERE artikel.ean = "; private static String QRY_CASH_CLOSING_7PERCENT = "SELECT sum(summe) FROM bestellungenartikel WHERE bestellung IN" + " (SELECT bestellungen_bar.id FROM bestellungen_bar WHERE storno = 0 AND bestellungen_bar.datum > (SELECT max(datum) FROM abschluesse))" + " AND artikel IN (SELECT artikel.id FROM artikel LEFT JOIN artikelgruppen ON artikel.gruppe = artikelgruppen.id" + " LEFT JOIN mwstsaetze ON artikelgruppen.ustsatz = mwstsaetze.id" + " WHERE mwstsaetze.satz = 1);"; private static String QRY_CASH_CLOSING_16PERCENT = "SELECT sum(summe) FROM bestellungenartikel WHERE bestellung IN" + " (SELECT bestellungen_bar.id FROM bestellungen_bar WHERE storno = 0 AND bestellungen_bar.datum > (SELECT max(datum) FROM abschluesse))" + " AND artikel IN (SELECT artikel.id FROM artikel LEFT JOIN artikelgruppen ON artikel.gruppe = artikelgruppen.id" + " LEFT JOIN mwstsaetze ON artikelgruppen.ustsatz = mwstsaetze.id" + " WHERE mwstsaetze.satz = 2);"; private static String INSERT_CASH_CLOSING = ""; private static final String STATUS_ID = "5"; private Login login; private Filiale filiale; private Connection conn; private DBManager () { try { // Select fitting database driver and connect: Class.forName("com.mysql.jdbc.Driver"); // conn = DriverManager.getConnection( // "jdbc:mysql://192.168.2.2:3306/warenw", "root", ""); // stmt = conn.createStatement(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static DBManager instance() { if (_instance == null) _instance = new DBManager(); return _instance; } public static DBManager instance(Login login) { if (_instance == null) _instance = new DBManager(); if (login(login)) return _instance; return null; } private Connection getConn() { if (conn == null) { try { // conn = DriverManager.getConnection( // "jdbc:mysql://192.168.2.2:3306/warenw2", "kasse", "kasse23"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/warenw2", "kasse", "kasse23"); } catch (SQLException e) { e.printStackTrace(); } } return conn; } private Statement getDBHandle() { Statement stmt = null; try { stmt = getConn().createStatement(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } assert stmt != null : "stmt != null"; return stmt; } public static boolean login(Login login) { return true; } /** * * * CREATE TABLE `bestellungen_bar` ( * `id` int(11) NOT NULL auto_increment, * `datum` int(11) NOT NULL default 0, * `kunde` int(11) NOT NULL default 0, * `status` int(11) NOT NULL DEFAULT 1, * `zahlungsart` int(11) DEFAULT 4, * `bezahlt` int(11) NOT NULL default 0, * `prioritaet` int(11) NOT NULL default 0, * `rechnungsnr` int(11) NOT NULL, * `storno` int(11) NOT NULL default 0, * PRIMARY KEY (`id`), * KEY `datum` (`datum`,`status`), * KEY `kunde` (`kunde`), * KEY `zahlungsart` (`zahlungsart`), * KEY `bezahlt` (`bezahlt`), * KEY `rechnungsnr` (`rechnungsnr`), * KEY `prioritaet` * (`prioritaet`) ) TYPE=MyISAM AUTO_INCREMENT=400000001; * * * CREATE TABLE `bestellungenartikel` ( * `id` int(11) NOT NULL auto_increment, * `bestellung` int(11) NOT NULL default '0', * `artikel` int(11) NOT NULL default '0', * `menge` int(11) NOT NULL default '0', * `summe` float(10,2) NOT NULL default '0.00', * `bearbeitet` int(11) NOT NULL default '0', * `status` int(11) NOT NULL default '0', * `kommission` int(11) NOT NULL default '0', * PRIMARY KEY (`id`), * KEY `bestellung` (`bestellung`,`artikel`,`bearbeitet`), * KEY `status` (`status`), * KEY `bearbeitet` (`bearbeitet`) ) * TYPE=MyISAM AUTO_INCREMENT=81 ; * * * * @param bill */@@ public void storeBill(Bill bill) { // storeMultipleProducts(bill); long milli = System.currentTimeMillis() / 1000; int cnt = 0; try { getNewBillingID(bill, milli); if (bill.getTransactionID() == 0) throw new StoreBillException(); switch (bill.getPaymentMethod()) { case Bill.PAYMENT_CASH: cnt = getDBHandle().executeUpdate(" UPDATE bestellungen_bar SET rechnungsnr = " + bill.getTransactionID() + " WHERE datum = " + milli); break; case Bill.PAYMENT_EC: cnt = getDBHandle().executeUpdate(" UPDATE bestellungen_ec SET rechnungsnr = " + bill.getTransactionID() + " WHERE datum = " + milli); } if (cnt == 1) // record exists { for (BillItem item : bill) { getDBHandle().executeUpdate( "INSERT INTO bestellungenartikel (bestellung, artikel, menge, summe, bearbeitet, status, kommission) VALUES (" + bill.getTransactionID() + ",' " + item.getEan() + "', " + item.getCount() + ", " + item.getBundlePrice() + ", " + milli + ", 7, 0)"); } StatusBar.instance().setInfo("Rechnung R" + bill.getTransactionID() + " gespeichert ..."); } else throw new StoreBillException(); } catch (SQLException e) { e.printStackTrace(); StatusBar.instance().setError("Fehler beim speichern der Rechnung ..."); } catch (StoreBillException e) { StatusBar.instance().setError("Fehler beim speichern der Rechnung ..."); e.printStackTrace(); } } /** * @param bill * ptr to the bill * @param time * Date in millisec * @throws StoreBillException */@@ private void getNewBillingID(Bill bill, long time) throws StoreBillException { try { // insert dummy record to get new id int cnt = 0; ResultSet rs = null; switch (bill.getPaymentMethod()) { case Bill.PAYMENT_CASH: { cnt = getDBHandle().executeUpdate(" INSERT INTO bestellungen_bar (datum, status, zahlungsart, bezahlt, rechnungsnr) VALUES (" + time + ", 7, 4, 1, " + 0 + ") "); rs = getDBHandle().executeQuery(" SELECT id FROM bestellungen_bar WHERE datum = " + time); break; } case Bill.PAYMENT_EC: { cnt = getDBHandle().executeUpdate(" INSERT INTO bestellungen_ec (datum, status, zahlungsart, bezahlt, rechnungsnr) VALUES (" + time + ", 7, 4, 1, " + 0 + ") "); rs = getDBHandle().executeQuery(" SELECT id FROM bestellungen_ec WHERE datum = " + time); break; } } if (rs == null) return; rs.next(); // set to first record else throw exception bill.setTransactionID(rs.getInt("id")); // set transaction ID } catch (SQLException e) { e.printStackTrace(); throw new StoreBillException(); } } private void storeMultipleProducts(Bill b) { // get transaction date Date now = new Date(System.currentTimeMillis()); PreparedStatement psBill; try { psBill = conn.prepareStatement(QRY_INSERT_BILLING); psBill.setString(1, login.getUsername()); // who psBill.setDate(3, now); // when psBill.setString(2, filiale.getFilialAbbrev()); // where int rowcount = psBill.executeUpdate(QRY_INSERT_BILLING, Statement.RETURN_GENERATED_KEYS); ResultSet rs = psBill.getGeneratedKeys(); assert rowcount == 1 : "affected rows != 1 [class DataStore]"; // get Transaction ID rs.next(); long id = rs.getLong(1); // // Insert products in batch mode // what // PreparedStatement ps = conn.prepareStatement(QRY_STORE_PRODUCTS); List<BillItem> items = b.getProducts(); for (BillItem p : items) { ps.setLong(1, id); // transaction id ps.setString(2, p.getEan()); // ean ps.setDouble(3, p.getBundlePrice()); // prod name ps.setInt(4, p.getCount()); // qty ps.addBatch(); } // exec batch insert ps.executeBatch(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public List<Product> getProductByEAN(String ean) { assert ean != null : "ean != null"; assert ean.length() > 0 : "ean.length() > 0"; List<Product> ret = new ArrayList<Product>(); // TEST // ret.add(new Product("100001", "Harold", 19.99d, 16.0d, 5)); // // if (ret.size() > 0) // return ret; // TEST try { String qry = QRY_SELECT_PRODUCTS_BY_EAN + "'" + ean + "'"; ResultSet rs = getDBHandle().executeQuery(qry); if (rs.next()) { String name = rs.getString("fantasiename"); String version = rs.getString("ausfuehrung"); String color = rs.getString("farbe"); String size = rs.getString("groesse"); String type = rs.getString("gattung"); String mat = rs.getString("gattung"); Double price = new Double(rs.getFloat("vk")) + new Double(rs.getFloat("vkmod")); Double tax = new Double(rs.getInt("mwst")); Integer count = rs.getInt("menge"); ret.add(new Product(ean, name, version, color, mat, size, type, tax, price, count)); } } catch (SQLException e) { e.printStackTrace(); } return ret; }}