/*
 * 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 function.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import object.itemmanager.ItemApp;
import object.itemmanager.ProductApp;
import object.UserApp;
import object.billmanager.Bill;
import object.billmanager.BillItem;
import object.itemmanager.Size;

/**
 *
 * @author Administrator
 */
public class AdminProcess {

    //    
    public static ArrayList<BillItem> getBillItem(java.sql.Connection connection, int idHD) {
        ArrayList<BillItem> alBillItems = new ArrayList<>();
        String sql = "SELECT * FROM ct_hoa_don JOIN hoa_don ON ct_hoa_don.id_hd = hoa_don.id_hd JOIN san_pham ON ct_hoa_don.id_sp = san_pham.id_sp WHERE ct_hoa_don.id_hd=?;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, idHD);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                BillItem billItem = new BillItem();
                billItem.setId(resultSet.getInt("id_hd"));
                billItem.setIdBillItem(resultSet.getInt("id_cthd"));
                billItem.setDate(resultSet.getDate("time"));
                //
                ProductApp productApp = new ProductApp();
                productApp.setIdSP(resultSet.getString("id_sp"));
                productApp.setNameSP(resultSet.getString("name_sp"));
                billItem.setProductApp(productApp);
                //
                Size size = new Size();
                size.setName(resultSet.getString("sizes"));
                billItem.setSize(size);
                //
                billItem.setAmount(resultSet.getInt("amount"));
                billItem.setTotalMoney(resultSet.getInt("total_money"));
                billItem.setNote(resultSet.getString("note"));
                alBillItems.add(billItem);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return alBillItems;
    }

    public static Bill getBill(java.sql.Connection connection, int idHD) {
        String sql = "SELECT * FROM hoa_don WHERE id_hd=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, idHD);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Bill bill = new Bill();
                bill.setId(resultSet.getInt("id_hd"));
                bill.setDate(resultSet.getTimestamp("time"));
                bill.setTotalMoney(resultSet.getInt("total_money"));
                bill.setIdTable(resultSet.getString("id_ban"));
                return bill;
            }
        } catch (SQLException ex) {
        }
        return null;
    }

    public static ArrayList<Bill> getBill(java.sql.Connection connection) {
        ArrayList<Bill> albBills = new ArrayList<>();
        String sql = "SELECT * FROM hoa_don JOIN ban ON hoa_don.id_ban = ban.id_ban ORDER BY (time) DESC";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Bill bill = new Bill();
                bill.setId(resultSet.getInt("id_hd"));
                bill.setDate(resultSet.getTimestamp("time"));
                bill.setTotalMoney(resultSet.getInt("total_money"));
                bill.setIdTable(resultSet.getString("id_ban"));
                bill.setNameTable(resultSet.getString("name_ban"));
                albBills.add(bill);
            }
        } catch (SQLException ex) {
        }
        return albBills;
    }

    //
    public static boolean deleteProduct(java.sql.Connection connection, String idSP) {
        String sql = "DELETE FROM san_pham WHERE id_sp=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, idSP);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
        }
        return false;
    }

    public static boolean updateProduct(java.sql.Connection connection, String idSP, ProductApp productApp) {
        String sql = "UPDATE san_pham SET id_sp=?, name_sp=?, price_sp=?, status_sp=?, id_mh=? WHERE id_sp=?;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, productApp.getIdSP());
            preparedStatement.setString(2, productApp.getNameSP());
            preparedStatement.setInt(3, productApp.getPriceSP());
            preparedStatement.setInt(4, productApp.isActiveSP() ? 1 : 0);
            preparedStatement.setString(5, productApp.getId());
            preparedStatement.setString(6, idSP);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
        }
        return false;
    }

    public static boolean addProduct(java.sql.Connection connection, ProductApp productApp) {
        String sql = "INSERT INTO san_pham VALUES(?, ?, ?, ?, ?);";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, productApp.getIdSP());
            preparedStatement.setString(2, productApp.getNameSP());
            preparedStatement.setInt(3, productApp.getPriceSP());
            preparedStatement.setInt(4, productApp.isActiveSP() ? 1 : 0);
            preparedStatement.setString(5, productApp.getId());
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
        }
        return false;
    }

    public static ArrayList<ProductApp> getArrayListProduct(java.sql.Connection connection) {
        ArrayList<ProductApp> alProductApps = new ArrayList<>();
        String sql = "SELECT * FROM san_pham JOIN mat_hang ON san_pham.id_mh = mat_hang.id_mh;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ProductApp productApp = new ProductApp();
                productApp.setId(resultSet.getString("id_mh"));
                productApp.setName(resultSet.getString("name"));
                productApp.setActive(resultSet.getInt("status") == 1);
                productApp.setIdSP(resultSet.getString("id_sp"));
                productApp.setNameSP(resultSet.getString("name_sp"));
                productApp.setPriceSP(resultSet.getInt("price_sp"));
                productApp.setActiveSP(resultSet.getInt("status_sp") == 1);
                alProductApps.add(productApp);
            }
        } catch (SQLException ex) {
        }
        return alProductApps;
    }

    //
    public static boolean deleteItem(java.sql.Connection connection, String id) {
        String sql = "DELETE FROM mat_hang WHERE id=?;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
        }
        return false;
    }

    public static boolean updateItem(java.sql.Connection connection, String id, ItemApp itemApp) {
        String sql = "UPDATE mat_hang SET id_mh=?, name=?, sizes=?, options=?, status=? WHERE id_mh=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, itemApp.getId());
            preparedStatement.setString(2, itemApp.getName());
            preparedStatement.setString(3, itemApp.sizeToString());
            preparedStatement.setString(4, itemApp.optionToString());
            preparedStatement.setInt(5, itemApp.isActive() ? 1 : 0);
            preparedStatement.setString(6, id);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }
        return false;
    }

    public static boolean addItem(java.sql.Connection connection, ItemApp itemApp) {
        String sql = "INSERT INTO mat_hang VALUES(?, ?, ?, ?, ?);";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, itemApp.getId());
            preparedStatement.setString(2, itemApp.getName());
            preparedStatement.setString(3, itemApp.sizeToString());
            preparedStatement.setString(4, itemApp.optionToString());
            preparedStatement.setInt(5, itemApp.isActive() ? 1 : 0);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
        }
        return false;
    }

    public static ArrayList<ItemApp> getArrayListItem(java.sql.Connection connection) {
        ArrayList<ItemApp> alItemApps = new ArrayList<>();
        String sql = "SELECT * FROM mat_hang;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ItemApp itemApp = new ItemApp();
                itemApp.setId(resultSet.getString("id_mh"));
                itemApp.setName(resultSet.getString("name"));
                itemApp.setListSize(parseListSizeFromItemApp(resultSet.getString("sizes")));
                itemApp.setListOption(parseListOptionFromItemApp(resultSet.getString("options")));
                itemApp.setActive(resultSet.getInt("status") == 1);
                alItemApps.add(itemApp);
            }
        } catch (SQLException ex) {
        }
        return alItemApps;
    }

    public static String[] parseListOptionFromItemApp(String listOption) {
        return listOption.split("\\|");
    }

    public static Size[] parseListSizeFromItemApp(String listSize) {
        ArrayList<Size> alSizes = new ArrayList<>();
        String[] splitSize = listSize.split("\\|");
        for (String size : splitSize) {
            String sizeName = size.substring(0, size.indexOf("["));
            String sizePercent = size.substring(size.indexOf("[") + 1, size.indexOf("]"));

            if (sizeName.trim().length() == 0) {
                continue;
            }
            double percent = 0;
            try {
                percent = Double.parseDouble(sizePercent);
            } catch (NumberFormatException e) {
                continue;
            }
            alSizes.add(new Size(sizeName, percent));
        }
        Size[] size = new Size[alSizes.size()];
        for (int i = 0; i < alSizes.size(); i++) {
            size[i] = alSizes.get(i);
        }
        return alSizes.size() > 0 ? size : null;
    }

    ///
    public static boolean deleteAccount(java.sql.Connection connection, String username) {
        String sql = "DELETE FROM tai_khoan WHERE username=?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
        }
        return false;
    }

    public static boolean updateAccount(java.sql.Connection connection, String username, UserApp userApp) {
        String sql = "UPDATE tai_khoan SET username=?, password=?, name=?, birthday=?, phone=?, andress=?, status=?, roles=? WHERE username=?;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, userApp.getUsername());
            preparedStatement.setString(2, userApp.getPassword());
            preparedStatement.setString(3, userApp.getName());
            preparedStatement.setString(4, userApp.getBirthday());
            preparedStatement.setString(5, userApp.getPhone());
            preparedStatement.setString(6, userApp.getAndress());
            preparedStatement.setInt(7, userApp.isStatus() ? 1 : 0);
            preparedStatement.setInt(8, userApp.getRoles());
            preparedStatement.setString(9, username);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static boolean addAccount(java.sql.Connection connection, UserApp userApp) {
        String sql = "INSERT INTO tai_khoan VALUES(?, ?, ?, ?, ?, ?, ?, ?);";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, userApp.getUsername());
            preparedStatement.setString(2, userApp.getPassword());
            preparedStatement.setString(3, userApp.getName());
            preparedStatement.setString(4, userApp.getBirthday());
            preparedStatement.setString(5, userApp.getPhone());
            preparedStatement.setString(6, userApp.getAndress());
            preparedStatement.setInt(7, userApp.isStatus() ? 1 : 0);
            preparedStatement.setInt(8, userApp.getRoles());
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static ArrayList<UserApp> getArrayListAccount(java.sql.Connection connection) {
        ArrayList<UserApp> alUserApps = new ArrayList<>();
        String sql = "SELECT * FROM tai_khoan;";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserApp userApp = new UserApp();
                userApp.setUsername(resultSet.getString("username"));
                userApp.setPassword(resultSet.getString("password"));
                userApp.setName(resultSet.getString("name"));
                userApp.setPhone(resultSet.getString("phone"));
                userApp.setBirthday(resultSet.getString("birthday"));
                userApp.setAndress(resultSet.getString("andress"));
                userApp.setStatus(resultSet.getInt("status") == 1);
                userApp.setRoles(resultSet.getInt("roles"));
                alUserApps.add(userApp);
            }
        } catch (SQLException ex) {
        }
        return alUserApps;
    }
}
