import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Conn {
	public static Connection conn;
	public static Statement statmt;
	public static ResultSet resSet;

	// --------ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ--------
	public static void tryConn() throws ClassNotFoundException, SQLException    {
		   conn = null;
		   String url = "jdbc:sqlite:/home/rustam/SQLLITEDB/requests.s3db";
		   Class.forName("org.sqlite.JDBC");
		   conn = DriverManager.getConnection(url);
		   System.out.println("База Подключена!");
	   }

	// --------Создание таблицы--------
	public static void createDB() throws ClassNotFoundException, SQLException   {
		statmt = conn.createStatement();
		statmt.execute("CREATE TABLE if not exists 'requests'"
				+ " ('id_request' INTEGER PRIMARY KEY AUTOINCREMENT, 'date' DATE, 'id_target_program' INT,"
				+ " 'id_organization' INT,  'id_employee'  INT, 'id_target'  INT, 'sum' INT, 'status_processing' VARCHAR(45),"
				+ " 'result' VARCHAR(45));");

		System.out.println("Таблица создана или уже существует.");
	   }

	// --------Заполнение таблицы--------
	public static void writeDB() throws SQLException	{
		   statmt.execute("INSERT INTO 'requests' ('date', 'id_target_program', 'id_organization', 'id_employee', 'id_target', 'sum', 'status_processing', 'result' ) "
		   		+ "VALUES ('2016.01.03', 1, 1, 1,1, 1, 'ds', 'sd'); ");

		   statmt.execute("INSERT INTO 'requests' ('date', 'id_target_program', 'id_organization', 'id_employee', 'id_target', 'sum', 'status_processing', 'result' ) "
			   		+ "VALUES ('2017.02.03', 2, 2, 2,2, 1, 'ds', 'sd'); ");

		   System.out.println("Таблица заполнена");
	}

	// -------- Вывод таблицы--------
	public static String[][]  readDB() throws ClassNotFoundException, SQLException   {
	     int rows  = statmt.executeQuery("SELECT COUNT(*) from requests").getInt(1);
		resSet = statmt.executeQuery("SELECT * FROM requests");
		String[][]  data =  new String[rows][];
		int count = 0;
		while(resSet.next())		{
			String id = String.valueOf(resSet.getInt("id_request"));
			String  date = resSet.getString("date");
			String program = String.valueOf(resSet.getString("id_target_program"));
			String  org = String.valueOf (resSet.getInt("id_organization"));
			String employee  = String.valueOf(resSet.getInt("id_employee"));
			String target =  String.valueOf(resSet.getInt("id_target"));
			String sum  = String.valueOf(resSet.getInt("sum"));
			String process = resSet.getString("status_processing");
			String result = resSet.getString("result");

			data[count++] = new String[] { id, date , program, org, employee  , target, sum , process, result} ;
	         System.out.println( "ID = " + id );
	         System.out.println( "date = " + date );
	         System.out.println( "org = " + org );
	         System.out.println();

		}
		System.out.println("Таблица выведена");
		return data;
	}

		public static String[][] readByParam(String param) throws SQLException {

		    int rows  = statmt.executeQuery("SELECT COUNT(*) from requests Where date = '"  +  param + "'" ).getInt(1);
			resSet = statmt.executeQuery("SELECT * FROM requests Where date = '"  +  param + "'");
			System.out.println(param);
			String[][]  data =  new String[rows][];
			int count = 0;
			while(resSet.next())		{
				String id = String.valueOf(resSet.getInt("id_request"));
				String  date = resSet.getString("date");
				String program = String.valueOf(resSet.getString("id_target_program"));
				String  org = String.valueOf (resSet.getInt("id_organization"));
				String employee  = String.valueOf(resSet.getInt("id_employee"));
				String target =  String.valueOf(resSet.getInt("id_target"));
				String sum  = String.valueOf(resSet.getInt("sum"));
				String process = resSet.getString("status_processing");
				String result = resSet.getString("result");
				data[count++] = new String[] { id, date , program, org, employee  , target, sum , process, result} ;
		         System.out.println( "ID = " + id );
		         System.out.println( "date = " + date );
		         System.out.println( "org = " + org );
		         System.out.println();

			}
			if (rows == 0) {
				return null;
			}
			return data;

		}




		// --------Закрытие--------
		public static void closeDB() throws ClassNotFoundException, SQLException   {
			conn.close();
			statmt.close();
			resSet.close();

			System.out.println("Соединения закрыты");
		   }
}