package system;

import java.io.Console;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;
import java.util.Scanner;

import javax.xml.bind.DatatypeConverter;

import oracle.jdbc.pool.OracleDataSource;

public class Aplikacja {

	String typUzytkownika ="klient";
	static String pesel = "92022565434";

	public void menu() throws SQLException, NoSuchAlgorithmException
	{
		String message ="";
		Connection conn = connect();
		do
		{
			message = login(conn);
			System.out.println(message);
		}
		while(message.equals("Niepoprawny pesel lub haslo"));

		if(typUzytkownika.equals("klient"))
		{
			menuCustomer(conn);
		}
		else if(typUzytkownika.equals("pracownik"))
		{
			menuWorker(conn);
		}
	}

	public void menuCustomer(Connection conn) throws SQLException
	{
		int action = 0;
		boolean end = false;
		Scanner scanner = new Scanner(System.in);

		while(!end)
		{
			System.out.println("MENU \n1.Wypisz produkty \n2.Zamow produkty \n3.Zobacz zamowienia \n4.Zakoncz");
			action = scanner.nextInt();

			switch(action)
			{
			case 1:
				showProducts(conn);
				break;
			case 2:
				String res = order(conn);
				System.out.println(res);
				break;
			case 3:
				PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM zamowienia WHERE pesel_klienta like " + pesel );
				ResultSet rslt1 = stmt1.executeQuery();

				while (rslt1.next())
				{
					 System.out.println(rslt1.getString(1) +" "+ rslt1.getString(2)+ " "+rslt1.getString(3)+ " "+rslt1.getString(4)+ " "+rslt1.getString(5));
				}
				break;
			case 4:
				end = true;
				break;
			default:
				System.out.println("Wprowadzono niewlasciwa liczbe");
				break;
			}
		}
	}


	public void menuWorker(Connection conn) throws SQLException
	{
		int action;
		boolean end = false;
		Scanner scanner = new Scanner(System.in);
		while(!end) {
		System.out.println("MENU "
				+ "\n1.Wypisz produkty "
				+ "\n2.Dodaj produkt do katalogu"
				+ "\n3.Usun produkt z katalogu"
				+ "\n4.Edytuj dane produktu "
				+ "\n5.Zobacz zamowienia "
				+ "\n6.Zrealizuj zamowienie"
				+ "\n7 Wyswietl klientow"
				+ "\n8.Dodaj klienta "
				+ "\n9.Usun klienta "
				+ "\n10. Zakoncz");

		System.out.println("Wybor: ");
		while(!scanner.hasNextInt()) {
			System.out.println("Podaj liczbe calkowita z zakresu 1-10: ");
			scanner.next();
		}
		action = scanner.nextInt();
		scanner.nextLine();


			switch(action)
			{
			case 1:
				showProducts(conn);
				break;
			case 2:
				insertProduct(conn);
				break;
			case 3:
				deleteProduct(conn);
				break;
			case 4:
				updateProduct(conn);
				break;
			case 5:
				showOrders(conn);
				break;
			case 6:
				executeOrder(conn);
				break;
			case 7:
				showClients(conn);
				break;
			case 8:
				addClient(conn);
				break;
			case 9:
				deleteClient(conn);
				break;
			case 10:
				end = true;
				break;
			default:
				System.out.println("Wprowadzono niewlasciwa liczbe");
				break;
			}
		}
	}

	public static Connection connect() throws SQLException
	{
		OracleDataSource ods = new OracleDataSource();
		ods.setURL("jdbc:oracle:thin:@localhost:1521/xe"); // jdbc:oracle:thin:@//[hostname]:[port]/[DB service name]
		ods.setUser("wasik"); // [username]
	ods.setPassword("121212as"); // [password]
		Connection conn = ods.getConnection();

		return conn;

//		Connection con = DriverManager.getConnection(
				//"jdbc:oracle:thin:@localhost:1521:xe","wasik","121212as");

	//	return con;
	}

	public void disconect()
	{

	}

	public String login(Connection conn) throws SQLException, NoSuchAlgorithmException
	{
		String password;
		Scanner scanner = new Scanner(System.in);
		System.out.println("Login: ");
		pesel = scanner.nextLine();
		System.out.println("haslo: ");
		password = scanner.nextLine();

		if(checkPassword(pesel, password, conn))
		{
			PreparedStatement stmt = conn.prepareStatement("SELECT pesel FROM klienci WHERE pesel like " + pesel );
			ResultSet rslt = stmt.executeQuery();
			if(rslt.next())
			{
				typUzytkownika = "klient";

			}
			else
			{
				typUzytkownika = "pracownik";
			}

			Statement stmt2 = conn.createStatement();
			String update = "UPDATE LOGOWANIE SET status = 'online' WHERE PESEL = '" + pesel +"'";
			stmt2.executeUpdate(update);
			return "Udane logowanie";
		}
		else
			return "Niepoprawny pesel lub haslo";
	}

	public boolean checkPassword(String pesel, String password, Connection conn) throws SQLException, NoSuchAlgorithmException
	{
		MessageDigest md = MessageDigest.getInstance("MD5");
	    md.update(password.getBytes());
	    byte[] digest = md.digest();
	    String passwordHash = DatatypeConverter.printHexBinary(digest).toUpperCase();

		PreparedStatement stmt = conn.prepareStatement("SELECT haslo FROM logowanie WHERE pesel like " + pesel );
		ResultSet rslt = stmt.executeQuery();
		if(rslt.next())
		{
			if(rslt.getString(1).equals(passwordHash))
				return true;
			else
				return false;
		}
		else
			return false;
	}

	public static String order(Connection conn) throws SQLException
	{
		int product = 0;
		int action = 0;
		String order = "";
		boolean end = false;
		Scanner scanner = new Scanner(System.in);

		while(!end)
		{
			System.out.println("MENU ZAMOWIEN \n1.Dodaj produkt do zamowienia \n2.Zloz zamowienie \n3.Anuluj");
			action = scanner.nextInt();

			switch(action)
			{
			case 1:
				System.out.println("Id produktu");
				product = scanner.nextInt();

				PreparedStatement stmt = conn.prepareStatement("SELECT * FROM katalog WHERE LICZBA_SZTUK > 0 AND PRODUKT_ID = " + Integer.toString(product) );
				ResultSet rslt = stmt.executeQuery();
				if(rslt.next())
				{
					order +=" INSERT INTO KATALOG_ZAMOWIENIE_RELACJA" +
							" VALUES  ( RELACJA_ID_SEQ.nextval,"+ Integer.toString(product)+", ZAMOWIENIE_ID_SEQ.currval);" +
							" UPDATE KATALOG" +
							" SET LICZBA_SZTUK = LICZBA_SZTUK -1" +
							" WHERE PRODUKT_ID like "+ Integer.toString(product)+";";
					System.out.println(order);
					System.out.println("Produkt "+ rslt.getString(2) + " dodany");
				}
				else
				{
					System.out.println("Nie ma produktu o tym indeksie");
				}
				break;
			case 2:
				if(!order.equals(""))
				{
					PreparedStatement stmt1 = conn.prepareStatement("BEGIN" +
							" INSERT INTO ZAMOWIENIA" +
							" VALUES  ( ZAMOWIENIE_ID_SEQ.nextval, "+pesel+", TO_DATE('20-11-2018', 'DD-MM-YYYY'), 'Założono', 0);"+
							order+
							" EXCEPTION" +
							" WHEN OTHERS  THEN" +
							" ROLLBACK;" +
							" END;");

					ResultSet rslt1 = stmt1.executeQuery();
					return "Zamowienie zlozone";
				}
				else
				{
					return "Zamowienie niemozliwe";
				}
			case 3:
				end = true;
				return "Zamowienie anulowane";
			default:
				System.out.println("Wprowadzono niewlasciwa liczbe");
				break;
			}
		}
		//
		//return "Zamowienie niemozliwe";
		return"";
	}

	public static void showProducts(Connection conn) throws SQLException {

		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM katalog WHERE LICZBA_SZTUK > 0" );
		ResultSet rslt = stmt.executeQuery();
		int counter = 0;
		while (rslt.next())
		{
			counter++;
			 System.out.println(counter + ". " + rslt.getString(1) +" "+ rslt.getString(2)+ " "+rslt.getString(3)+ " "+rslt.getString(4)+ " "+rslt.getString(5)+ " "+rslt.getString(6));

		}
	}

	public void showClients(Connection conn) throws SQLException {

		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM KLIENCI" );
		ResultSet rslt = stmt.executeQuery();
		int licznik = 0;
		while (rslt.next())
		{
			int dane_id = Integer.parseInt(rslt.getString(2));
			licznik++;
			String pesel = rslt.getString(1);
			 System.out.println(licznik + ". Klient:\n\nPesel: " + pesel );

			 //drukowanie danych
			 PreparedStatement stmt1 = conn.prepareStatement("SELECT imie,nazwisko,adres,email"
			 		+ ",numer_telefonu FROM DANE_OSOBOWE WHERE DANE_ID = " + dane_id);
			 ResultSet rslt1 = stmt1.executeQuery();
			 String imie = "";
			 String nazwisko = "";
			 String email = "";
			 String nr_tel = "";
			 int adres_id = 0;
			 while(rslt1.next()) {
				 imie = rslt1.getString(1);
				 nazwisko = rslt1.getString(2);
				 adres_id = Integer.parseInt(rslt1.getString(3));
				 email = rslt1.getString(4);
				 nr_tel = rslt1.getString(5);
			 }
			 System.out.println("Imie: " + imie + "\nNazwisko: " + nazwisko + "\nE-mail: " +
			 email + "\nNumer telefonu: " + nr_tel);

			 //drukowanie adresu
			 PreparedStatement stmt2 = conn.prepareStatement("SELECT miasto,ulica,numer_domu"
				 		+ " FROM ADRES WHERE ADRES_ID = " + adres_id );
				 ResultSet rslt2 = stmt2.executeQuery();
				 String miasto = "";
				 String ulica = "";
				 int nr_domu = 0;

				 while(rslt2.next()) {
					 miasto = rslt2.getString(1);
					 ulica = rslt2.getString(2);
					 nr_domu = Integer.parseInt(rslt2.getString(3));
				 }
				 System.out.println("Miasto: " + miasto + "\nUlica: " + ulica +  "\nNumer domu: " + nr_domu + "\n\n");
		}

	}

	public boolean insertProduct(Connection conn) throws SQLException {
		Scanner scanner = new Scanner(System.in);
		scanner.useLocale(Locale.US);
		try {
			String nazwa = "";
			System.out.println("\nPodaj nazwe produktu: ");
			nazwa = scanner.nextLine();

			String producent = "";
			System.out.println("\nPodaj producenta produktu: ");
			producent = scanner.nextLine();

			int ilosc = 0;
			System.out.println("\nPodaj ilosc sztuk produktu: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			ilosc = scanner.nextInt();
			scanner.nextLine();

			//int ilosc = 0;
			//System.out.println("\nPodaj ilosc sztuk produktu: ");
			//ilosc = scanner.nextInt();

			String typ = "";
			System.out.println("\nPodaj typ produktu: ");
			typ = scanner.nextLine();


			float cena = 0;
			System.out.println("\nPodaj cene produktu: ");
			while(!scanner.hasNextFloat()) {
				System.out.println("Podaj liczbe zmiennoprzecinkowa: ");
				scanner.next();
			}
			cena = scanner.nextFloat();
			scanner.nextLine();

			//float cena = 0;
			//System.out.println("\nPodaj cene produktu: ");
			//cena = scanner.nextFloat();

			Statement stmt = conn.createStatement();
			String update = "INSERT INTO KATALOG VALUES( PRODUKT_ID_SEQ.nextval,'"  + nazwa +
					"','" + producent + "'," + ilosc + ",'" + typ + "'," + cena + ")";

			System.out.println(update);
			stmt.executeUpdate(update);
		}

		catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;


	}

	public boolean updateProduct(Connection conn) throws SQLException {
		Scanner scanner = new Scanner(System.in);
		scanner.useLocale(Locale.US);
		try {
			showProducts(conn);

			int id = 0;
			System.out.println("\nPodaj id produktu, ktory chcesz edytowac: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			id = scanner.nextInt();
			scanner.nextLine();

			//System.out.println("\nPodaj id produktu, ktory chcesz edytowac: ");
			//id = scanner.nextInt();

			String nazwa = "";
			System.out.println("\nPodaj nazwe produktu: ");
			nazwa = scanner.nextLine();

			String producent = "";
			System.out.println("\nPodaj producenta produktu: ");
			producent = scanner.nextLine();

			int ilosc = 0;
			System.out.println("\nPodaj ilosc sztuk produktu: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			ilosc = scanner.nextInt();
			scanner.nextLine();

			//int ilosc = 0;
			//System.out.println("\nPodaj ilosc sztuk produktu: ");
			//ilosc = scanner.nextInt();

			String typ = "";
			System.out.println("\nPodaj typ produktu: ");
			typ = scanner.nextLine();

			float cena = 0;
			System.out.println("\nPodaj cene produktu: ");
			while(!scanner.hasNextFloat()) {
				System.out.println("Podaj liczbe zmiennoprzecinkowa: ");
				scanner.next();
			}
			cena = scanner.nextFloat();
			scanner.nextLine();

			//float cena = 0;
			//System.out.println("\nPodaj cene produktu: ");
			//cena = scanner.nextFloat();

			Statement stmt = conn.createStatement();
			String update = "UPDATE KATALOG SET nazwa = '" + nazwa +
					"', producent = '" + producent + "', liczba_sztuk = " + ilosc  +
					", typ_produktu = '" + typ  + "', cena = " + cena  +  " WHERE produkt_id = " + id + "";
			System.out.println(update);
			stmt.executeUpdate(update);
		}

		catch(SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public boolean addClient(Connection conn) throws SQLException {

		Scanner scanner = new Scanner(System.in);
		try {
			//dodawanie danych logowania
			String pesel = "";
			System.out.println("\nPodaj pesel klienta: ");
			pesel = scanner.nextLine();

			String haslo = "";
			System.out.println("\nPodaj haslo klienta: ");
			haslo = scanner.nextLine();

			Statement stmt = conn.createStatement();
			String update = "INSERT INTO LOGOWANIE VALUES('" + pesel + "', STANDARD_HASH('" + haslo + "','MD5'),'offline')";
			stmt.executeUpdate(update);


			//dodawanie adresu
			String miasto = "";
			System.out.println("\nPodaj dane zamieszkania\n\n Podaj miasto: ");
			miasto = scanner.nextLine();

			String ulica = "";
			System.out.println("\nPodaj ulice: ");
			ulica = scanner.nextLine();

			int nr_domu = 0;
			System.out.println("\nPodaj numer domu: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			nr_domu = scanner.nextInt();
			scanner.nextLine();

			//int nr_domu = 0;
		//	System.out.println("\nPodaj numer domu: ");
			//nr_domu = scanner.nextInt();

			Statement stmt1 = conn.createStatement();
			String update1 = "INSERT INTO ADRES VALUES(ADRES_ID_SEQ.nextval,'" + miasto + "','" + ulica + "'," + nr_domu + ")";
			stmt1.executeUpdate(update1);


			//dodawanie danych osobowych
			String imie = "";
			System.out.println("\nPodaj dane klienta\n\n Podaj imie: ");
			imie = scanner.nextLine();

			String nazwisko = "";
			System.out.println("\nPodaj nazwisko: ");
			nazwisko = scanner.nextLine();

			String email = "";
			System.out.println("\nPodaj email: ");
			email = scanner.nextLine();

			String nr_tel = "";
			System.out.println("\nPodaj numer telefonu: ");
			nr_tel = scanner.nextLine();

			Statement stmt2 = conn.createStatement();
			String update2 = "INSERT INTO DANE_OSOBOWE VALUES(DANE_OSOBOWE_ID_SEQ.nextval,'" +
							 imie + "','" + nazwisko + "',ADRES_ID_SEQ.currval,'" + email +
							 "','" + nr_tel + "')";
			stmt2.executeUpdate(update2);

			//dodawanie klienta
			Statement stmt3 = conn.createStatement();
			String update3 = "INSERT INTO KLIENCI VALUES('" + pesel + "', DANE_OSOBOWE_ID_SEQ.currval)";
			stmt3.executeUpdate(update3);


		}

		catch (SQLException e){
			e.printStackTrace();
			return false;
		}

		return true;

	}

	public boolean deleteClient(Connection conn) throws SQLException {
		Scanner scanner = new Scanner(System.in);
		try {

			showClients(conn);
			System.out.println("Podaj pesel klienta, ktorego chcesz usunac: ");
			String pesel;
			pesel = scanner.nextLine();

			int dane_id = 0;
			PreparedStatement stmt = conn.prepareStatement("SELECT DANE FROM KLIENCI WHERE PESEL = '" + pesel + "'");
			ResultSet rslt = stmt.executeQuery();
			if(!rslt.next())
				return false;
			else
				dane_id = Integer.parseInt(rslt.getString(1));

			int adres_id = 0;
			PreparedStatement stmt3 = conn.prepareStatement("SELECT ADRES FROM DANE_OSOBOWE WHERE DANE_ID =" + dane_id);
			ResultSet rslt3 = stmt3.executeQuery();

			if(rslt3.next())
				adres_id = Integer.parseInt(rslt3.getString(1));

			PreparedStatement stmt1 = conn.prepareStatement("BEGIN DELETE KLIENCI WHERE PESEL = '" + pesel
					+ "';DELETE DANE_OSOBOWE WHERE DANE_ID = " + dane_id
					+ ";DELETE ADRES WHERE ADRES_ID = " + adres_id
					+ ";DELETE LOGOWANIE WHERE PESEL = '" + pesel + "';        EXCEPTION" +
							"        WHEN OTHERS THEN" +
							"        ROLLBACK;COMMIT;END;");
			stmt1.executeUpdate();

			//PreparedStatement stmt2 = conn.prepareStatement(";DELETE DANE WHERE DANE_ID = " + dane_id);
			//stmt2.executeUpdate();

			//PreparedStatement stmt4 = conn.prepareStatement(";DELETE ADRES WHERE ADRES_ID = " + adres_id);
			//stmt4.executeUpdate();

			//PreparedStatement stmt5 = conn.prepareStatement(";DELETE LOGOWANIE WHERE PESEL = '" + pesel + "'");
			//stmt5.executeUpdate();

		} catch(SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;


	}

	public void showOrders(Connection conn) throws SQLException {

		int action;
		boolean end = false;
		Scanner scanner = new Scanner(System.in);
		while(!end)
		{
			System.out.println("MENU "
					+ "\n1.Zlozone"
					+ "\n2.Zrealizowane"
					+ "\n3. Wroc");

			System.out.println("\nWybor: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			action = scanner.nextInt();
			scanner.nextLine();


			switch(action) {
			case 1:
				showUnrealisedOrders(conn);
				break;

			case 2:
				showRealisedOrders(conn);
				break;

			case 3:
				end = true;
				break;
			default:
				System.out.println("Wprowadzono niewlasciwa liczbe");
				break;

			}
		}


	}

	public boolean executeOrder(Connection conn) throws SQLException {

		Scanner scanner = new Scanner(System.in);

		try {

			showOrders(conn);

			int wybor;
			System.out.println("Podaj id zamowienia, ktore chcesz zrealizowac");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			wybor = scanner.nextInt();
			scanner.nextLine();

			//System.out.println("Podaj id zamowienia, ktore chcesz zrealizowac");
			//int wybor;
			//wybor = scanner.nextInt();

			Statement stmt = conn.createStatement();
			String update = "UPDATE ZAMOWIENIA SET status = 'Zrealizowane' WHERE ZAMOWIENIE_ID = " + wybor;
			stmt.executeUpdate(update);

		} catch(SQLException e) {
			return false;
		}

		return true;


	}

	public boolean deleteProduct(Connection conn) throws SQLException {

		Scanner scanner = new Scanner(System.in);
		try {

			showProducts(conn);

			int prod_id;
			System.out.println("Podaj id produktu, ktory chcesz usunac: ");
			while(!scanner.hasNextInt()) {
				System.out.println("Podaj liczbe calkowita: ");
				scanner.next();
			}
			prod_id = scanner.nextInt();
			scanner.nextLine();

			//System.out.println("Podaj id produktu, ktory chcesz usunac: ");
			//int prod_id;
			//prod_id = scanner.nextInt();


			PreparedStatement stmt1 = conn.prepareStatement("DELETE KATALOG WHERE PRODUKT_ID = " + prod_id);
			stmt1.executeUpdate();


		} catch(SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public void showRealisedOrders(Connection conn) throws SQLException {
		PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM zamowienia WHERE status = 'Zrealizowane'");
		ResultSet rslt1 = stmt1.executeQuery();
		int licznik = 0;

		String id = "";
		String pesel = "";
		String data = "";
		String status = "";
		float cena = 0;

		//while (rslt1.next())
		//{
		//	 licznik++;
		//	 System.out.println(licznik + ". " + rslt1.getString(1) +" "+ rslt1.getString(2)+ " "+rslt1.getString(3)+ " "+rslt1.getString(4)+ " "+rslt1.getString(5));
		//}

		while(rslt1.next()) {
			id = rslt1.getString(1);
			pesel = rslt1.getString(2);
			data = rslt1.getString(3);
			status = rslt1.getString(4);
			cena = Float.parseFloat(rslt1.getString(5));

			licznik++;
			System.out.println("Zamowienie " + licznik + "\nZamowienie ID: " + id +
					"\nPesel klienta: " + pesel + "\nData zlozenia: " + data + "\nStatus: " + status +
					"\nCena: " + cena);

			String nazwa;
			String producent;
			String typ;
			String prod_id;

			PreparedStatement stmt2 = conn.prepareStatement("SELECT produkt_id FROM katalog_zamowienie_relacja WHERE zamowienie_id = " + id);
			ResultSet rslt2 = stmt2.executeQuery();
			int licznik2 = 0;

			while(rslt2.next()) {
				licznik2++;
				prod_id = rslt2.getString(1);
				System.out.println("\n" + licznik2 +". ID produktu: " + prod_id);
				PreparedStatement stmt3 = conn.prepareStatement("SELECT nazwa,producent,typ_produktu FROM katalog WHERE produkt_id = " + prod_id);
				ResultSet rslt3 = stmt3.executeQuery();
				while(rslt3.next()) {
					nazwa = rslt3.getString(1);
					producent = rslt3.getString(2);
					typ = rslt3.getString(3);
					System.out.println("   " + nazwa + " " + producent + " " + typ);
				}

			}


		}

	}

	public void showUnrealisedOrders(Connection conn) throws SQLException {
		//PreparedStatement stmt = conn.prepareStatement("SELECT * FROM zamowienia WHERE status = 'Założono'");
		//ResultSet rslt = stmt.executeQuery();
	//	int licznik = 0;

		//while (rslt1.next())
		//{
		//	 licznik++;
		//	 System.out.println(licznik + ". " + rslt1.getString(1) +" "+ rslt1.getString(2)+ " "+rslt1.getString(3)+ " "+rslt1.getString(4)+ " "+rslt1.getString(5));
		//}


		PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM zamowienia WHERE status = 'Założono'");
		ResultSet rslt1 = stmt1.executeQuery();
		int licznik1 = 0;

		String id = "";
		String pesel = "";
		String data = "";
		String status = "";
		float cena = 0;

		//while (rslt1.next())
		//{
		//	 licznik++;
		//	 System.out.println(licznik + ". " + rslt1.getString(1) +" "+ rslt1.getString(2)+ " "+rslt1.getString(3)+ " "+rslt1.getString(4)+ " "+rslt1.getString(5));
		//}

		while(rslt1.next()) {
			id = rslt1.getString(1);
			pesel = rslt1.getString(2);
			data = rslt1.getString(3);
			status = rslt1.getString(4);
			cena = Float.parseFloat(rslt1.getString(5));

			licznik1++;
			System.out.println("Zamowienie " + licznik1 + "\nZamowienie ID: " + id +
					"\nPesel klienta: " + pesel + "\nData zlozenia: " + data + "\nStatus: " + status +
					"\nCena: " + cena);

			String nazwa;
			String producent;
			String typ;
			String prod_id;

			PreparedStatement stmt2 = conn.prepareStatement("SELECT produkt_id FROM katalog_zamowienie_relacja WHERE zamowienie_id = " + id);
			ResultSet rslt2 = stmt2.executeQuery();
			int licznik2 = 0;

			while(rslt2.next()) {
				licznik2++;
				prod_id = rslt2.getString(1);
				System.out.println("\n" + licznik2 +". ID produktu: " + prod_id);
				PreparedStatement stmt3 = conn.prepareStatement("SELECT nazwa,producent,typ_produktu FROM katalog WHERE produkt_id = " + prod_id);
				ResultSet rslt3 = stmt3.executeQuery();
				while(rslt3.next()) {
					nazwa = rslt3.getString(1);
					producent = rslt3.getString(2);
					typ = rslt3.getString(3);
					System.out.println("   " + nazwa + " " + producent + " " + typ);
				}

			}


		}

	}

}