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

/**
 *
 * @author frih0812
 */
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

public class DbConnection
{

    private static String dbURL = "jdbc:derby://localhost:1527/EkonomiKoll;create=true;user=tst;password=tst";
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    /*public static void main(String[] args)
    {
    createConnection();
    //insertRestaurants(5, "LaVals", "Berkeley");
    //insertRestaurants(20, "Mias grill", "Umeå");
    //insertUtgifter(7, "Invito", "Umeå");
    //selectUtgifter();
    //shutdown();
    }*/
    int summa, sparsumma, summamanad, sparid;
    String kategori, notering;
    String datum, sqlSats, sparmal;
    String id;

    private int antalRaderIDB()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) FROM TST.UTGIFTER");

            resultSet.next();
            int rowcount = resultSet.getInt(1);
            return rowcount;
        } catch (SQLException e)
        {
            return 0;
        }
    }

    public String[][] lasFranDB() throws IOException
    {
        try
        {
            createConnection();
            int antalRader = antalRaderIDB();
            // String tableName = "TST.UTGIFTER";
            ResultSet results = stmt.executeQuery("SELECT * FROM TST.UTGIFTER");
            String[][] post = new String[antalRader][5];
            for (int i = 0; i < antalRader; i++)
            {
                while (results.next())
                {
                    post[i][0] = results.getString("ID");
                    post[i][1] = results.getString("SUMMA");
                    post[i][2] = results.getString("DATUM");
                    post[i][3] = results.getString("NOTERING");
                    post[i][4] = results.getString("KATEGORI");
                    System.out.println("Första raden i Databasen: " + post[i][0] + " " + post[i][1] + " " + post[i][2] + " " + post[i][3] + " " + post[i][4]);
                }
            }
            return post;
        }
        catch (SQLException e)
        {
            System.out.println("NOOO!");
            return null;
        }

    }

    public String Utgifter(String id, int summa, String datum, String kategori, String notering)
    {
        this.id = id;
        this.summa = summa;
        this.datum = datum;
        this.kategori = kategori;
        this.notering = notering;
        String usqlSats = "insert into Utgifter (id, summa, kategori, notering, datum) values (" + "'" + id + "', " + summa + ", " + "'" + kategori + "', " + "'" + notering + "', " + "'" + datum + "')";
        return (usqlSats);
    //insertUtgifter(sqlSats);
    }

    public String Inkomster(String id, int summa, String datum, String kategori, String notering)
    {
        this.id = id;
        this.summa = summa;
        this.datum = datum;
        this.kategori = kategori;
        this.notering = notering;
        String isqlSats = "insert into Inkomster (id, summa, kategori, notering, datum) values (" +
                id + ", " + summa + ", " + "'" + notering + "', " + "'" + kategori + "', " + "'" + datum + "')";

        return (isqlSats);

    }

    public String Sparande(String id, String sparmal, Integer sparsumma, Integer summamanad)
    {
        this.id = id;
        this.sparmal = sparmal;
        this.sparsumma = sparsumma;
        this.summamanad = summamanad;

        String ssqlSats = "insert into Sparande (sparid, sparmal, sparsumma, summamanad) values (" +
                "'" + id + "', " + "'" + sparmal + "', " + sparsumma + ", " + summamanad + ")";

        return (ssqlSats);
    }

    public void ssok(String indata)
    {
        String indatan = indata;
        String ssqlSats = "SELECT * FROM TST.UTGIFTER WHERE notering LIKE '%" + indatan + "%'";
        insertLaggTillDatabas(ssqlSats);
    //System.out.println(indatan);
    }

    public static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); //org.apache.derby.jdbc.ClientDriver
            System.out.println("Hallelulja!");
        } catch (Exception except)
        {
            except.printStackTrace();
            System.out.println("ASS!!");
        }

    }

    public void insertLaggTillDatabas(String sqlSats)
    {
        this.sqlSats = sqlSats;
        System.out.println(sqlSats);
        try
        {
            stmt = conn.createStatement();
            stmt.execute(sqlSats);
            stmt.close();

        } catch (SQLException sqlExcept)
        {
            System.out.println("LLLLLLL");
            sqlExcept.printStackTrace();
        }
    //shutdown();
    }

    /*private static void selectUtgifter()
    {
    try
    {
    stmt = conn.createStatement();
    ResultSet results = stmt.executeQuery("select * from " + tableName);
    ResultSetMetaData rsmd = results.getMetaData();
    int numberCols = rsmd.getColumnCount();
    for (int i = 1; i <= numberCols; i++)
    {
    //print Column Names
    System.out.print(rsmd.getColumnLabel(i) + "\t\t");
    }

    System.out.println("\n-------------------------------------------------");

    while (results.next())
    {
    int id = results.getInt(1);
    String restName = results.getString(2);
    String cityName = results.getString(3);
    System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
    }
    results.close();
    stmt.close();
    } catch (SQLException sqlExcept)
    {
    sqlExcept.printStackTrace();
    }
    }*/
    public static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        } catch (SQLException sqlExcept)
        {
        }
    }
}