/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package sonderaufgabe;

import java.sql.*;
import java.util.*;

/**class MainWindow
 * @author usher
 */
public class MappingLayer
{

    /*Singleton Instanz vom Mapping Layer*/
    private static MappingLayer fmlInstance = null;
    /*Connection zur Datenbank*/
    private static Connection           con;
    /*Datenbank URL*/
    private static final String databaseURL = "jdbc:firebirdsql:127.0.0.1:E:\\studium\\sem3\\datenbank"
                                +"\\javasonderaufgabe\\SONDERAUFGABE.FDB";
    /*Datenbank Username*/
    private static final String userName = "SYSDBA";
    /*Datenbank Passwort*/
    private static final String password = "masterkey";
    /*Datenbanktreiber*/
    private static final String driverName = "org.firebirdsql.jdbc.FBDriver";

    /*constant states for external using*/
    public  static final int    SEARCH = 0;
    public  static final int    INSERT = 1;
    public  static final int    UPDATE = 2;

    /**Konstruktor zur Datenbank Connection
     *
     */
    private  MappingLayer()
    {
        try
        {
            Class.forName (driverName);
            con = DriverManager.getConnection(databaseURL, userName, password);

            System.out.println ("Database connected.");
        }
        catch (ClassNotFoundException e)
        {
            System.out.println ("Firebird driver not found");
            System.out.println (e.getMessage ());
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Unable to establish a connection.");
            showSQLException (e);
        }
    }

    /**Finalize zum schließen der Connection zur DB
     *
     */
    @Override
    protected void finalize()
    {
        try
        {
            con.close();

            System.out.println ("Database disconnected.");
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Unable to disconnect a connection.");
            showSQLException (e);
        }
    }

    /*Singleton Methode für getInstanc
     *
     */
    public static MappingLayer getInstance()
    {
        if(fmlInstance == null)
            fmlInstance = new MappingLayer();

        return fmlInstance;
    }

    /**close() Löschen der Singleton Methode zum schließen der Datenbank
     *
     */
    public static void close()
    {
        fmlInstance = null;
        System.gc();
    }

    /**showSQLException - shows detailed SQL error message
     *
     * @param e
     */
    private static void showSQLException (SQLException e)
    {
        SQLException message = e;
        while (message != null)
        {
        System.out.println (message.getMessage ());
        System.out.println ("Error Code: " + message.getErrorCode ());
        System.out.println ("SQL State: " + message.getSQLState ());
        message = message.getNextException ();
        }
    }

    /**Liste der Artikel in der DB erhalten
     *
     * @return list of clients
     */
    List<Article> getAllArticles()
    {
        List<Article> Article_List = new ArrayList<Article>();
        Statement statement = null;
        ResultSet rs = null;
        Article newArticle = null;
        String selectString = "SELECT ARTIKELID, ARTIKELNR, BEZEICHNUNG, ARTIKELGRUPPEID, " +
                                     "LIEFERANTID, BESTANDSMENGE, PREIS " +
                              "FROM ARTIKEL ORDER BY ARTIKELID";
        try
        {
            statement = con.createStatement();
            rs = statement.executeQuery(selectString);

            while (rs.next())
            {
                /*SELECT ARTIKELID, ARTIKELNR, BEZEICHNUNG, ARTIKELGRUPPEID,LIEFERANTID, BESTANDSMENGE, PREIS*/
                newArticle = new Article(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),rs.getInt(5), rs.getInt(6), rs.getFloat(7));

                Article_List.add(newArticle);
            }
            rs.close();
            statement.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
        }

        return Article_List;
    }

    /**searchArticles
     *
     * @param searchArticle
     * @return list of articles
     */
    public List<Article> searchArticles(Article searchArticle)
    {
        List<Article> Article_List = new ArrayList<Article>();
        Article newArticle = null;
        boolean iNotFirst = false;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String selectString = "SELECT ARTIKELID, ARTIKELNR, BEZEICHNUNG, ARTIKELGRUPPEID, " +
                                     "LIEFERANTID, BESTANDSMENGE, PREIS " +
                              "FROM ARTIKEL";
        String conditionString = null;

        if(searchArticle != null)
        {
            conditionString = " WHERE ";

            if(searchArticle.getArticleID() != -1)
            {
                conditionString += "ARTIKELID LIKE( '%" + searchArticle.getArticleID() + "%')";
                iNotFirst = true;
            }

            if(searchArticle.getArtikelNr() != null)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "ARTIKELNR LIKE ( '%" +searchArticle.getArtikelNr() + "%')";
                iNotFirst=true;
            }

            if(searchArticle.getBezeichnung() != null)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "BEZEICHNUNG LIKE( '%" + searchArticle.getBezeichnung()+ "%')";
                iNotFirst=true;
            }

            if(searchArticle.getArtikelgruppeID() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "ARTIKELGRUPPEID LIKE( '%" + searchArticle.getArtikelgruppeID() + "%')";
                iNotFirst=true;
            }
            if(searchArticle.getLieferantID() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "LIEFERANTID =  '" + searchArticle.getLieferantID() + "'";
                iNotFirst=true;
            }

            if(searchArticle.getBestandsmenge() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += " BESTANDSMENGE LIKE( '%" + searchArticle.getBestandsmenge()+ "%')";
                iNotFirst=true;
            }

            if(searchArticle.getPreis() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "PREIS LIKE( '%" + searchArticle.getPreis() + "%')";
                iNotFirst=true;
            }



            if(conditionString.equals(" WHERE "))
                conditionString = null;
        }
        try
        {
            if(conditionString != null)
                selectString += conditionString;

            ps = con.prepareStatement(selectString);
            rs = ps.executeQuery(selectString);

            while (rs.next())
            {
                /*get ARTIKELID, ArtikelNr, Bezeichnung, ArtikelgruppeID, LieferantID, Bestandsmenge, Preis*/
                newArticle = new Article(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
                                       rs.getInt(5), rs.getInt(6), rs.getFloat(7));

                Article_List.add(newArticle);
            }
            rs.close();
            ps.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
        }

        return Article_List;
    }



    /**insertArticle
     *
     * @param articleToInsert
     * @return -1 = error, 0 = not inserted, 1 = inserted
     */
    public int insertArticle(Article articleToInsert)
    {
        int ret = 1;
        try
		{
			PreparedStatement ps = con.prepareStatement("INSERT INTO ARTIKEL (ARTIKELID, ARTIKELNR, BEZEICHNUNG, ARTIKELGRUPPEID, LIEFERANTID, BESTANDSMENGE, PREIS) " +
                                                        "VALUES(?, ?, ?, ?, ?, ?, ?)");

			ps.setInt(1, articleToInsert.getArticleID());
			ps.setString(2, articleToInsert.getArtikelNr());
			ps.setString(3, articleToInsert.getBezeichnung());
			ps.setInt(4, articleToInsert.getArtikelgruppeID());
                        ps.setInt(5, articleToInsert.getLieferantID());
			ps.setInt(6, articleToInsert.getBestandsmenge());
			ps.setFloat(7, articleToInsert.getPreis());

			ret = ps.executeUpdate();

			ps.close();

		}
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
            return -1;
        }
        return ret;
    }


    /**updateArticle
     *
     * @param articleToUpdate
     * @return -1 = error, 0 = not inserted, 1 = inserted
     */
    public int updateArticle(Article articleToUpdate)
    {
        int ret = 1;
        try
		{
			PreparedStatement ps = con.prepareStatement("UPDATE ARTIKEL  " +
                                                        "SET ARTIKELNR = ?, BEZEICHNUNG = ?, ARTIKELGRUPPEID = ?, LIEFERANTID = ?, " +
                                                        "BESTANDSMENGE = ?, PREIS = ? " +
                                                        "WHERE ARTIKELID = ?");


                        ps.setString(1, articleToUpdate.getArtikelNr());
			ps.setString(2, articleToUpdate.getBezeichnung());
			ps.setInt(3, articleToUpdate.getArtikelgruppeID());
			ps.setInt(4, articleToUpdate.getLieferantID());
			ps.setInt(5, articleToUpdate.getBestandsmenge());
			ps.setFloat(6, articleToUpdate.getPreis());
                        ps.setInt(7, articleToUpdate.getArticleID());

			ret = ps.executeUpdate();

			ps.close();

		}
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
            return -1;
        }
        return ret;
    }

    /**deleteArticle
     *
     * @param articleID
     * @return -1 = error, 0 = not inserted, 1 = inserted
     */
    public int deleteArticle(int articleID)
    {
        int ret = 1;
        try
		{
			PreparedStatement ps = con.prepareStatement("DELETE FROM ARTIKEL  " +
                                                        "WHERE ARTIKELID = ?");

            ps.setInt(1, articleID);

			ret = ps.executeUpdate();

			ps.close();

		}
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
            return 0;
        }
        return ret;
    }

    /**getAllOrders
     *
     * @return list of orders
     */
    List<Order> getAllOrders()
    {
        List<Order> orderList = new ArrayList<Order>();
        Statement statement = null;
        ResultSet rs = null;
        Order newOrder = null;

        String selectString = "SELECT BESTELLID,KUNDEID,ARTIKELIDENT,MENGE,BESTELLDATUM,NAME,VORNAME,BEZEICHNUNG FROM BESTELLUNGEN,ARTIKEL,PERSON WHERE KUNDEID = PERSID AND ARTIKELIDENT = ARTIKELID ORDER BY BESTELLID";
        try
        {
            statement = con.createStatement();
            rs = statement.executeQuery(selectString);

            while (rs.next())
            {
                String name = rs.getString(6) + " " + rs.getString(7);
                String bezeichnung = rs.getString(8);
                /*get bestellid, kundeid, artikelid, menge, bestalldatum, kundennr, name, vorname,strasse,nr,plz,ort,land*/
                newOrder = new Order(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getTimestamp(5),name,bezeichnung);

                orderList.add(newOrder);
            }
            rs.close();
            statement.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Unable to disconnect a connection.");
            showSQLException (e);
        }

        return orderList;
    }

    /**searchOrders
     *
     * @param searchOrder
     * @return list of order
     */
    List<Order> searchOrders(Order searchOrder)
    {
        List<Order> orderList = new ArrayList<Order>();
        Order newOrder = null;
        boolean iNotFirst = false;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String selectString = "SELECT BESTELLID,KUNDEID,ARTIKELIDENT,MENGE,BESTELLDATUM,NAME,VORNAME,BEZEICHNUNG FROM BESTELLUNGEN,ARTIKEL INNER JOIN PERSON ON KUNDEID = PERSID AND ARTIKELIDENT = ARTIKELID;";
        String conditionString = null;

        if(searchOrder != null)
        {
            conditionString = "WHERE ";
            if(searchOrder.getBestellID() != -1)
            {
                conditionString += "BESTELLID LIKE( '%" + searchOrder.getBestellID() + "%')";
                iNotFirst = true;
            }

            if(searchOrder.getKundeID() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "KUNDEID LIKE( '%" + searchOrder.getKundeID() + "%')";
                iNotFirst=true;
            }

            if(searchOrder.getArtikelID() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "ARTIKELIDENT LIKE( '%" + searchOrder.getArtikelID() + "%')";
                iNotFirst=true;
            }

            if(searchOrder.getMenge() != -1)
            {
                if(iNotFirst)
                    conditionString += " AND ";

                conditionString += "MENGE LIKE( '%" + searchOrder.getMenge() + "%')";
                iNotFirst=true;
            }

            if(conditionString.equals(" WHERE "))
                conditionString = null;
        }
        try
        {
            if(conditionString != null)
                selectString += conditionString + " ORDER BY BESTELLID, BESTELLDATUM";

            ps = con.prepareStatement(selectString);
            rs = ps.executeQuery(selectString);

            while (rs.next())
            {
                String name = rs.getString(6) + " " + rs.getString(7);
                String bezeichnung = rs.getString(8);
                /*get bestellid, kundeid, artikelid, menge, bestalldatum, kundennr, name, vorname,strasse,nr,plz,ort,land*/
                newOrder = new Order(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4), rs.getTimestamp(5),name,bezeichnung);

                orderList.add(newOrder);
            }
            rs.close();
            ps.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
        }

        return orderList;
    }




    /**updateOrder
    *
    * @param orderToUpdate
    * @return -1 = error, 0 = not inserted, 1 = inserted
    */
    int updateOrder(Order orderToUpdate)
    {
        int ret = 1;
        try
		{
			PreparedStatement ps = con.prepareStatement("UPDATE BESTELLUNGEN SET MENGE = ?,BESTELLDATUM = 'NOW',KUNDEID = ? WHERE BESTELLID = ?");

                        ps.setInt(1,orderToUpdate.getMenge());
                        ps.setInt(2,orderToUpdate.getKundeID());
                        ps.setInt(3,orderToUpdate.getBestellID());

                        ret = ps.executeUpdate();
			ps.close();

		}
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
            return -1;
        }
        return ret;
    }

    /**deleteOrder
     *
     * @param orderID
     * @return -1 = error, 0 = not inserted, 1 = inserted
     */
    int deleteOrder(int orderID)
    {
        int ret = 1;
        try
		{
			PreparedStatement ps = con.prepareStatement("DELETE FROM BESTELLUNGEN  " +
                                                        "WHERE BESTELLID = ?");
            ps.setInt(1, orderID);

			ret = ps.executeUpdate();
			ps.close();
		}
        catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println ("Failure with SQL-Statement.");
            showSQLException (e);
            return 0;
        }
        return ret;
    }
}