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

public class Question4 {

public static void main(String args[]) {
    Scanner user_input = new Scanner( System.in );

    int cardnumber, month, custid, afm, phonenumber, areaid;
    String name, surname, address;

    System.out.print("Enter your card number : ");
    cardnumber = user_input.nextInt( );

    System.out.print("Enter the month: ");
    month = user_input.nextInt( );

    String url = "jdbc:sqlserver://altebaran.dmst.aueb.gr:1433;" +
                   "databaseName=DB47;user=G547;password=25g489030;";
    Connection dbcon ;
    Statement stmt;
    ResultSet rs;

    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch(java.lang.ClassNotFoundException e){
        System.out.print("ClassNotFoundException: ");
        System.out.println(e.getMessage());
    }

    try {
        dbcon = DriverManager.getConnection(url);
        stmt = dbcon.createStatement();
        rs = stmt.executeQuery("SELECT customerid FROM CreditCard WHERE CreditCard.card_number ="+ cardnumber +";");
        rs.next();
        custid = rs.getInt("customerid");
        rs = stmt.executeQuery("SELECT * FROM Customer WHERE Customer.customerid ="+ custid +";");
        rs.next();

        name = rs.getString("name");
        surname = rs.getString("surname");
        address = rs.getString("address");
        afm = rs.getInt("afm");
        phonenumber = rs.getInt("phone_number");
        areaid = rs.getInt("areaid");
        System.out.println("kwdikos: " +custid+ "\nonoma :" +name+"\nepitheto :" +surname+"\ndieuthunsi :" +address+"\nafm :" +afm+"\narithmos :" +phonenumber+"\nkwdikso perioxhs :" +areaid+"");

        rs = stmt.executeQuery("SELECT * FROM Transactions WHERE Transactions.card_number ="+ cardnumber +"AND MONTH(Transactions.transaction_date) ="+ month +";");


        int confirm_number=0;
        float charge_amount=0 ;
        int bankid=0 ;
        String transaction_date="" ;
        int storeid=0 ;
        int card_number=0 ;
        System.out.print("\n\n ---KINHSEIS TOU MHNA--- \n\n");
        while (rs.next()) {

        confirm_number = rs.getInt("confirm_number");
        charge_amount = rs.getFloat("charge_amount");
        bankid = rs.getInt("bankid");
        transaction_date = rs.getString("transaction_date");
        storeid = rs.getInt("storeid");
        card_number = rs.getInt("card_number");
        System.out.println("O pelatis me kwdiko :"+custid+"\nekane sunallagh me kwdiko :"+confirm_number+"\nposou :"+charge_amount+"\nsthn trapeza me kwdiko :"+bankid+"\nse katastima ke kwdiko :"+storeid+"\nxrhsimopoiwntas thn karta :"+card_number+"\nthn hmeromhnia :"+transaction_date+"\n");

        }


        rs = stmt.executeQuery("SELECT * FROM Payment WHERE Payment.customerid ="+ custid +" AND MONTH(Payment.payment_date) ="+ month +";");


        int payment_number=0;
        float payment_amount=0 ;
        String payment_date="" ;

        while (rs.next()) {

        payment_number = rs.getInt("payment_number");
        payment_amount = rs.getFloat("payment_amount");
        payment_date = rs.getString("payment_date");
        System.out.println("O pelatis me kwdiko :"+custid+"\nekane plhrwmh me kwdiko :"+payment_number+"\nposou :"+payment_amount+"\nthn hmeromhnia :"+payment_date+"\n");

        }


        rs = stmt.executeQuery("SELECT service FROM Store, Transactions WHERE Transactions.storeid = Store.storeid AND Transactions.card_number ="+ cardnumber +" AND MONTH(Transactions.transaction_date) ="+ month +";");

        String service="";

        while (rs.next()) {


        service = rs.getString("service");
        System.out.println("H sunallagh egine se katastima me eidos uphresias :"+ service+"\n");

        }

        rs = stmt.executeQuery("SELECT SUM(Transactions.charge_amount) AS sum FROM Transactions WHERE Transactions.card_number ="+ cardnumber +"AND MONTH(Transactions.transaction_date) ="+ month +";");

        float sum = 0;

        while (rs.next()) {

        sum = rs.getFloat("sum");
        System.out.println("Athroisma sunallagwn :" +sum+"\n");
        }

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