package data;

import java.sql.*;

public class GoodsData {
  Connection conn = null;
  //取得數據庫連接的方法
  public void setupConn(){
    try{
      if(conn == null){
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        String url =
            "jdbc:sqlserver://localhost;databaseName=LoginC1";
        //使用DriverManager類的getConnection()方法建立連接
        conn = DriverManager.getConnection(url, "sa", "1234");
      }
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  //創建商品類別的方法
  public int createGoodsCategory(int parentId, String categoryName,
                                 String categoryDescription) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      //倒序排列，使序號最大的記錄成為第1條記錄
      String selectSql = "select * from goodsCategory order by categoryId desc";
      String insertSql = "INSERT INTO goodsCategory VALUES(?, ?, ?, ?)";
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(selectSql);
      //創建序號變量
      int categoryId = 1;
      if(rs.next()){
        //新序號是最大序號加1
        categoryId = rs.getInt(1) + 1;
      }
      PreparedStatement insertGoods = conn.prepareStatement(insertSql);
      //設置添加商品類別的4個參數
      insertGoods.setInt(1, categoryId);
      insertGoods.setInt(2, parentId);
      insertGoods.setString(3, categoryName);
      insertGoods.setString(4, categoryDescription);
      //成功添加記錄，返回1
      result = insertGoods.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //更新商品類別的方法
  public int updateGoodsCategory(int categoryId, int parentId,
                                 String categoryName,
                                 String categoryDescription) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      //第1個SCROLL_SENSITIVE參數表示反映其它用戶的更新操作
      //第2個CONCUR_UPDATABLE參數表示數據集可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      String sql = "select * from goodsCategory where categoryId = " +
          categoryId;
      //取得要修改的記錄
      ResultSet rs = stmt.executeQuery(sql);
      if(rs.next()){
        rs.updateInt(2, parentId);
        rs.updateString(3, categoryName);
        rs.updateString(4, categoryDescription);
        //向數據庫提交更新記錄
        rs.updateRow();
        result = 1;
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //刪除商品類別的方法
  public int deleteGoodsCategory(int categoryId) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select * from goods where categoryId = " + categoryId;
      //根據商品類別取得商品數據
      ResultSet rs = stmt.executeQuery(sql1);
      //只有商品類別沒有商品數量才可以刪除商品類別
      if(!rs.next()){
        String sql2 = "delete from goodsCategory where categoryId = " + categoryId;
        //刪除記錄
        result = stmt.executeUpdate(sql2);
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //取得所有商品類別的方法
  public String[][] getAllGoodsCategory() {
    String[][] detail = new String[0][4];
    //建立連接
    setupConn();
    try{
      //CONCUR_READ_ONLY表示數據集不可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);
      String sqlCount = "select count(*) from goodsCategory";
      String sql = "select * from goodsCategory";
      //取得商品類別數據表的記錄總數
      ResultSet rs = stmt.executeQuery(sqlCount);
      int count = 0;
      if(rs.next()){
        count = rs.getInt(1);
      }
      //重新創建數組
      detail = new String[count][4];
      //取得商品類別數據表的全部記錄
      rs = stmt.executeQuery(sql);
      int i = 0;
      //將記錄加入數組
      while (rs.next()) {
        for(int j = 0; j < 4; j++){
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //創建商品的方法
  public int createGoods(String[] goods) {
    int result = 0;
    if(goods.length != 13){
      return result;
    }
    //建立連接
    setupConn();
    try {
      //嘗試根據商品類別標識取得商品類別記錄
      String selectSql = "select * from goodsCategory where categoryId = " + goods[1];
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(selectSql);
      //商品類別數據表存在商品類別記錄才可以進行添加商品操作
      if(rs.next()){
        String insertSql = "INSERT INTO goods VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
            + "?, ?, ?)";
        //創建PreparedStatement接口
        PreparedStatement insertGoods = conn.prepareStatement(insertSql);
        //設置添加商品的13個參數, 數字類型可以應用設置字符串方法
        for(int i = 0; i < 13; i++){
          insertGoods.setString(i + 1, goods[i]);
        }
        //成功添加記錄，返回1
        result = insertGoods.executeUpdate();
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //更新商品的方法
  public int updateGoods(String[] goods) {
    int result = 0;
    if(goods.length != 13){
      return result;
    }
    //建立連接
    setupConn();
    try {
      //第1個SCROLL_SENSITIVE參數表示反映其它用戶的更新操作
      //第2個CONCUR_UPDATABLE參數表示數據集可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      String sql = "select * from goods where goodsBarCode = '" +
          goods[0] + "'";
      //取得要修改的記錄
      ResultSet rs = stmt.executeQuery(sql);
      //商品條形碼是主鍵，不進行修改
      if(rs.next()){
        for (int i = 1; i < 13; i++) {
          rs.updateString(i + 1, goods[i]);
        }
        //向數據庫提交更新記錄
        rs.updateRow();
        result = 1;
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //刪除商品的方法
  public int deleteGoods(String goodsBarCode) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql = "delete from goods where goodsBarCode = '" + goodsBarCode + "'";
      //刪除記錄
      result = stmt.executeUpdate(sql);
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //根據類別取得商品的方法
  public String[][] getGoodsByGoodsCategory(int categoryId){
    String[][] detail = new String[0][13];
    //建立連接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select count(*) from goods where categoryId = " + categoryId;
      String sql2 = "select * from goods where categoryId = " + categoryId;
      //根據商品類別取得商品總數
      ResultSet rs = stmt.executeQuery(sql1);
      if(rs.next()){
        //重新創建數組
        detail = new String[rs.getInt(1)][13];
      }
      //根據商品類別取得商品
      rs = stmt.executeQuery(sql2);
      int i = 0;
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          //將商品記錄寫入數組
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //根據字段取得商品的方法
  public String[][] getGoodsByField(String fieldName, String value){
    String[][] detail = new String[0][13];
    //建立連接
    setupConn();
    try{
      //CONCUR_READ_ONLY表示數據集不可更新
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);
      //%表示任意字符,%商品%表示查詢包括"商品"字符串的所有記錄
      String sqlCount = "select count(*) from goods where " + fieldName +
          " like '%" + value + "%'";
      String sql = "select * from goods where " + fieldName +
          " like '%" + value + "%'";
      //取得記錄總數
      ResultSet rs = stmt.executeQuery(sqlCount);
      int count = 0;
      if(rs.next()){
        count = rs.getInt(1);
      }
      //重新創建數組
      detail = new String[count][13];
      //取得商品數據表的全部記錄
      rs = stmt.executeQuery(sql);
      int i = 0;
      //將記錄加入數組
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //取得折扣商品的方法
  public String[][] getDiscountGoods() {
    String[][] detail = new String[0][13];
    //建立連接
    setupConn();
    try {
      Statement stmt = conn.createStatement();
      String sql1 = "select count(*) from goods where discount < 1";
      String sql2 = "select * from goods where discount < 1";
      //取得折扣商品記錄總數
      ResultSet rs = stmt.executeQuery(sql1);
      if(rs.next()){
        //重新創建數組
        detail = new String[rs.getInt(1)][13];
      }
      //取得折扣商品記錄
      rs = stmt.executeQuery(sql2);
      int i = 0;
      while (rs.next()) {
        for(int j = 0; j < 13; j++){
          //將商品記錄寫入數組
          detail[i][j] = rs.getString(j + 1);
        }
        i++;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //設置商品價格和折扣的方法
  public int setGoodsPriceDiscount(String goodsBarCode, double salePrice,
                                   double discount) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      //創建更新商品價格和折扣的SQL語句
      String sql = "update goods set salePrice = ?, discount = ? " +
          "where goodsBarCode = '" + goodsBarCode + "'";
      PreparedStatement updatePS = conn.prepareStatement(sql);
      //設置價格和折扣
      updatePS.setDouble(1, salePrice);
      updatePS.setDouble(2, discount);
      result = updatePS.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //設置商品庫存的方法
  public int setGoodsStock(String goodsBarCode, int upperLimit,
                                   int lowerLimit) {
    int result = 0;
    //建立連接
    setupConn();
    try {
      //創建更新商品庫存的SQL語句
      String sql = "update goods set upperLimit = ?, lowerLimit = ? " +
          "where goodsBarCode = '" + goodsBarCode + "'";
      PreparedStatement updatePS = conn.prepareStatement(sql);
      //設置庫存
      updatePS.setInt(1, upperLimit);
      updatePS.setInt(2, lowerLimit);
      result = updatePS.executeUpdate();
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //檢查是否是整數的方法
  public int checkInt(String str){
    int result = 0;
    try{
      Integer.parseInt(str);
      result = 1;
    }catch(Exception ex){ }
    return result;
  }
  //檢查數字是否大于0小于等于1的方法
  public int checkNumIn0To1(String str){
    int result = 0;
    try{
      double num = Double.parseDouble(str);
      if(num <= 1 & num > 0){
        result = 1;
      }
    }catch(Exception ex){ }
    return result;
  }
  //檢查是否是小數的方法
  public int checkDouble(String str){
    int result = 0;
    try{
      Double.parseDouble(str);
      result = 1;
    }catch(Exception ex){ }
    return result;
  }
}