/*
 * 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 consulta;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author syd
 */
public class ConsultaV2 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        int input;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://DESKTOP-NFDQP8C;databaseName=projectBD;user=sa;password=123qwe;";

           Connection con = DriverManager.getConnection(connectionUrl);

            do {

                System.out.println("\n*** O que pretende fazer? ***\n1-Consultar dados das Consultas\n2-Marcar nova Consulta\n3-Sair");
                input = Ler.umInt();
                Statement stmt = null;
                ResultSet rs = null;
                switch (input) {
                    case 1: {
                        System.out.println("Insira ID do utente");
                        int idUtente= Ler.umInt();
                        String SQL = "Select *\n"
                                + "From Consulta\n"
                                +"Where UtenteID="+idUtente+"\n";

                        stmt = con.createStatement();
                        rs = stmt.executeQuery(SQL);
                        while (rs.next()) {
                            System.out.println("ConsultaID: " + rs.getString(1) + " Anotação: " + rs.getString(2) + " MedicoID: " + rs.getString(3)+ " UtenteID: " + rs.getString(4)+ " CentroID: " + rs.getString(5) );
                        }

                    }
                    continue;
                    case 2: {
                        System.out.println("*** Insira o nº da Conta ***\n");
                        int t = Ler.umInt();

                           String SQL = "Select MesaNum\n"
                                + "From TalaoDeConferencia\n"
                                + "where TCNum="+t;
                        stmt = con.createStatement();
                        rs = stmt.executeQuery(SQL);
                        int mesa=0;
                        int af=0;
                         while (rs.next()) {
                             mesa=Integer.valueOf(rs.getString(1));

                        }
                             SQL = "Select AFNum\n"
                                + "From TalaoDeConferencia\n"
                                + "where TCNum="+t;
                        stmt = con.createStatement();
                        rs = stmt.executeQuery(SQL);


                         while (rs.next()) {
                             af=Integer.valueOf(rs.getString(1));

                        }

                      SQL = "Select SUM(custo) as Total\n"
                                + "From (Select  P.Custo * ATC.Quantidade as custo\n"
                                + "From TalaoDeConferencia T, AtribuicaoProdTC ATC, Produto P\n"
                                + "Where T.TCNum=ATC.TCNum and ATC.ProdutoNum=P.ProdutoNum and T.MesaNum=" + mesa + " and T.AFNum=" + af + " and T.Pago=0)asd";
                        stmt = con.createStatement();
                        rs = stmt.executeQuery(SQL);
                        int v=0;
                        while (rs.next()) {
                           v=rs.getInt(1);
                        }




                        PreparedStatement ps = con.prepareStatement(
                                "UPDATE TalaoDeConferencia SET Pago = 1 WHERE TCNum =" +t);

                        ps.executeUpdate();
                        ps.close();



                         ps = con.prepareStatement(
                                "INSERT INTO Fatura(TCNum,ValorTotal)  values("+t+","+v+")");

                        ps.executeUpdate();
                        ps.close();

                    }
                    continue;

                }
            } while (input != 3);

        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.toString());
        } catch (ClassNotFoundException cE) {
            System.out.println("Class Not Found Exception: " + cE.toString());
        }

    }
}