package mark2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.table.DefaultTableModel;

public class ProductTable extends javax.swing.JFrame {

    /**
     * Creates new form ProductTable
     */
    Connection con = null;

    public Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // while in developers mode use verify Server certificate = false && useSSL=true
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/markone?verifyServerCertificate=false&useSSL=true", "root",
                    "nishant97");
            return con;
        } catch (Exception ex) {
            System.out.println(ex);
            return null;
        }
    }

    public ArrayList<User> getUser() {
        ArrayList<User> usersList = new ArrayList<>();
        try {
            String query = "select user_catalogue.User_name,user_catalogue.User_id,user_catalogue.group_id,user_catalogue.final_debt,user_contact.mobile_no,user_expenditure.amount_spent\r\n"
                    + "from user_catalogue,user_contact,user_expenditure\r\n"
                    + "where user_contact.User_name= user_catalogue.User_name\r\n"
                    + "and user_contact.mobile_no= user_expenditure.mobile_no order By user_expenditure.amount_spent";

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);

            User user;
            while (rs.next()) {
                //int userId,String name, String mobileNumber,float amountSpent,int groupId
                user = new User(rs.getInt("User_id"), rs.getString("User_name"), rs.getString("mobile_number"), rs.getFloat("amount_spent"), rs.getInt("group_id"), rs.getFloat("final_debt"));
                usersList.add(user);
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return usersList;
    }

    public ProductTable() {
        initComponents();
        Show_User_JTable();
    }

    public void Show_User_JTable() {
        ArrayList<User> list = getUser();
        DefaultTableModel model = (DefaultTableModel) jTable1.getModel();
        Object[] row = new Object[5];
        for (int i = 0; i < list.size(); i++) {
            row[0] = list.get(i).getId();
            row[1] = list.get(i).getName();
            row[2] = list.get(i).getGroupId();
            row[3] = list.get(i).getMobileNumber();
            row[4] = list.get(i).getAmountSeeker();

            model.addRow(row);
        }
    }