/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package classes;

import java.sql.*;

/**
 *
 * @author User
 */
public class Database {
    static Connection conn;
    static Statement statmt;
    static ResultSet resSet;
    static final String DBNAME = "pharmacy.db";

    // --------ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ--------

    /**
     * Connects to a fixed database.
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void conn() throws ClassNotFoundException, SQLException 
    {
        conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:"+DBNAME);
        statmt = conn.createStatement();
    }
    
    public static Object getRowFromTable(String table, String column, int index) throws ClassNotFoundException, SQLException{
        resSet = statmt.executeQuery("SELECT "+column+" FROM "+table+";");
        while(index-- > 0)
            resSet.next();
        Object obj = resSet.getObject(column);
        return obj;
    }
    
    public static Object getByParameter(String table, String column, String parameter, String value)throws ClassNotFoundException,SQLException{
        resSet = statmt.executeQuery("SELECT "+column+" FROM "+table+" WHERE "+parameter+"="+value+";");
        resSet.next();
        Object obj = resSet.getObject(column);
        return obj;
    }
    
    public static void closeDB() throws ClassNotFoundException, SQLException
    {
        conn.close();
        statmt.close();
        resSet.close();
    }

    static int getNumSales(int id_goods) throws SQLException {
        resSet = statmt.executeQuery("select sum(sale_goods.quantity) as A from sale_goods join goods on sale_goods.id_goods=goods.id_goods " +
                                     "where sale_goods.id_goods = "+id_goods+";");
        resSet.next();
        return resSet.getInt("A");
    }

    static int getNumWarehouse(int id_goods) throws SQLException {
        resSet = statmt.executeQuery("select sum(purchase.quantity) as A from purchase join goods on purchase.id_purchase=goods.id_purchase " +
                                     "where goods.id_goods = "+id_goods+";");
        resSet.next();
        return resSet.getInt("A");
    }
    
    static String getDate(int id_goods) throws SQLException {
        resSet = statmt.executeQuery("select purchase.date as A from purchase join goods on purchase.id_purchase=goods.id_purchase " +
                                     "where goods.id_goods = "+id_goods+";");
        resSet.next();
        return resSet.getString("A");
    }
    
    static Object getSaleInfo(String parameter, int id_goods, String dateStart, String dateEnd, int index) throws SQLException{
        Object obj;
        resSet = statmt.executeQuery("select "+parameter+" as A from " +
                                     "sale_goods join sale join goods join employee on "
                                   + "sale.id_employee=employee.id_employee and sale_goods.id_goods=goods.id_goods and sale_goods.id_sale=sale.id_sale "
                                   + "where goods.id_goods="+id_goods+" and sale.date between date('"+dateStart+"') and date('"+dateEnd+"') order by sale.date desc;");
        while(index-- > 0)
            resSet.next();
        obj = resSet.getObject("A");
        return obj;
    }
    
    public static int getSaleInfoNum(int id_goods, String dateStart, String dateEnd, int index) throws SQLException{
        int num;
        resSet = statmt.executeQuery("select count() from " +
                                     "sale_goods join sale join goods join employee on "
                                   + "sale.id_employee=employee.id_employee and sale_goods.id_goods=goods.id_goods and sale_goods.id_sale=sale.id_sale "
                                   + "where goods.id_goods="+id_goods+" and sale.date between date('"+dateStart+"') and date('"+dateEnd+"');");
        resSet.next();
        num = resSet.getInt("count()");
        return num;
    }
}