import java.io.*;
import java.text.*;
import java.util.*;
import java.sql.*;

public class erotima4 {
    public static void main(String args[]){
        String url = "jdbc:sqlserver://altebaran.dmst.aueb.gr:1433;" +
                           "databaseName=DB32;user=G532;password=49bs95959;";
        Connection dbcon ;
        Statement stmt;
        ResultSet rs;

        int card_for_search, month_for_search, cust_code=0,cust_number,cust_area_code, pur_number, pur_bank_code,      pur_store_code,store_service;
    String cust_name, cust_surname, cust_street, cust_city, cust_tel, cust_afm, pur_datetime;
    float pur_amount,  sum=0;
        System.out.println("Δώσε τον αριθμό της πιστωτικής κάρτας:");
        Scanner input = new Scanner(System.in);
        card_for_search = input.nextInt();
        System.out.println("Δώσε τον αριθμό του μήνα:");
        month_for_search = input.nextInt();
        System.out.println("\n");

        /* declare ODBC conectivity */
        try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");}
        catch(java.lang.ClassNotFoundException e)
        {System.out.print("ClassNotFoundException: ");
        System.out.println(e.getMessage());}
            /* execute SQL statements */
        try {
                dbcon = DriverManager.getConnection(url);  //sindesu me tin vash
            stmt = dbcon.createStatement();  //ftiaksei ena statement
            rs = stmt.executeQuery("SELECT * FROM credit_card WHERE card_number="+card_for_search); //rs = result set
            while (rs.next()) {
                cust_code = rs.getInt("customer_code");
                System.out.println("ΚΩΔΙΚΟΣ ΠΕΛΑΤΗ:"+cust_code);
            }
            rs.close();

            stmt = dbcon.createStatement();
            rs = stmt.executeQuery("SELECT * FROM customer WHERE customer_code="+cust_code); //kainourgio querry
            while (rs.next()) {
                 cust_name = rs.getString("name");
                 cust_surname = rs.getString("surname");
                 cust_number = rs.getInt("number");
                 cust_street = rs.getString("street");
                 cust_city = rs.getString("city");
                 cust_tel = rs.getString("Telephone_number");
                 cust_afm = rs.getString("AFM");
                 cust_area_code = rs.getInt("area_code");

                 System.out.println("ΟΝΟΜΑ ΠΕΛΑΤΗ:"+cust_name);
                 System.out.println("ΕΠΙΘΕΤΟ ΠΕΛΑΤΗ:"+cust_surname);
                 System.out.println("ΑΡΙΘΜΟΣ ΚΑΤΟΙΚΙΑΣ:"+cust_number);
                 System.out.println("ΟΔΟΣ:"+cust_street);
                 System.out.println("ΠΟΛΗ:"+cust_city);
                 System.out.println("ΤΗΛΕΦΩΝΟ:"+cust_tel);
                 System.out.println("ΑΦΜ:"+cust_afm);
                 System.out.println("ΚΩΔΙΚΟΣ ΠΕΡΙΟΧΗΣ:"+cust_area_code);
                 System.out.println("\n");
            }
            rs.close();

            stmt = dbcon.createStatement();
            rs = stmt.executeQuery("SELECT * FROM purchase P, store S WHERE MONTH(date_time)="+month_for_search+" AND card_number="+card_for_search+" AND P.store_code = S.store_code");
            while (rs.next()) {
                pur_number = rs.getInt("Confirmation_number");
                pur_datetime = rs.getString("date_time");
                pur_bank_code = rs.getInt("bank_code");
                pur_store_code = rs.getInt("store_code");
                pur_amount = rs.getFloat("amount");
                store_service = rs.getInt("service_type");
                sum = sum + pur_amount;

                System.out.println("ΑΡΙΘΜΟΣ ΕΠΙΒΕΒΑΙΩΣΗΣ ΣΥΝΑΛΛΑΓΗΣ:"+pur_number);
                System.out.println("ΗΜΕΡΟΜΗΝΙΑ ΚΑΙ ΩΡΑ ΣΥΝΑΛΛΑΓΗΣ:"+pur_datetime);
                System.out.println("ΚΩΔΙΚΟΣ ΤΡΑΠΕΖΑΣ:"+pur_bank_code);
                System.out.println("ΚΩΔΙΚΟΣ ΚΑΤΑΣΤΗΜΑΤΟΣ:"+pur_store_code);
                System.out.println("ΠΟΣΟ ΣΥΝΑΛΛΑΓΗΣ:"+pur_amount);
                System.out.println("ΕΙΔΟΣ ΚΑΤΑΣΤΗΜΑΤΟΣ:"+store_service);
                System.out.println("\n");
                System.out.println("\n");
                }
            rs.close();
            System.out.println("ΝΕΟ ΤΡΕΧΩΝ ΥΠΟΛΟΙΠΟ:"+sum);


            stmt.close();
            dbcon.close();

            }
        catch(SQLException e)
        {
        System.out.print("SQLException: ");
        System.out.println(e.getMessage());}

    }
}