package com.dao;

import com.tool.JDBConnection;
import java.util.*;
import com.domain.UnitVO;
import java.sql.*;

//對計量單位的操作
public class UnitDao {
  private JDBConnection connection = null;
  public UnitDao() {
    connection = new JDBConnection();
    this.connection.creatConnection(); //利用構造方法調用類中的對象對數據庫創建連接
  }

  //查詢全部的計量單位
  public List unitSelect() {
    List list = new ArrayList();
    UnitVO unit = null;
    String sql = "select * from tb_unit order by id";
    ResultSet rs = connection.executeQuery(sql);
    try {
      while (rs.next()) {
        unit = new UnitVO();
        unit.setId(Integer.valueOf(rs.getString(1)));
        unit.setUnit_number(rs.getString(2));
        unit.setUnit_name(rs.getString(3));
        unit.setUnit_remark(rs.getString(4));
        list.add(unit);
      }
    }
    catch (SQLException ex) {
    }
    connection.closeConnection();
    return list;
  }

//以員工編號為條件查詢信息
  public UnitVO unitSelectOne(String number) {
    UnitVO unit = null;
    String sql = "select * from tb_unit where unit_number='" + number + "'";
    ResultSet rs = connection.executeQuery(sql);
    try {
      while (rs.next()) {
        unit = new UnitVO();
        unit.setId(Integer.valueOf(rs.getString(1)));
        unit.setUnit_number(rs.getString(2));
        unit.setUnit_name(rs.getString(3));
        unit.setUnit_remark(rs.getString(4));
      }
    }
    catch (SQLException ex) {
    }
    connection.closeConnection();
    return unit;
  }

//以名稱為條件查詢
  public UnitVO unitSelectName(String name) {
    UnitVO unit = null;
    String sql = "select * from tb_unit where unit_name='" + name + "'";
    ResultSet rs = connection.executeQuery(sql);
    try {
      while (rs.next()) {
        unit = new UnitVO();
        unit.setId(Integer.valueOf(rs.getString(1)));
        unit.setUnit_number(rs.getString(2));
        unit.setUnit_name(rs.getString(3));
        unit.setUnit_remark(rs.getString(4));
      }
    }
    catch (SQLException ex) {
    }
    connection.closeConnection();
    return unit;
  }

//添加操作
  public void unitInsert(UnitVO vo) {
    String sql = "insert into tb_unit values ('" + vo.getUnit_number() + "','" +
        vo.getUnit_name() + "','" + vo.getUnit_remark() + "')";
    connection.executeUpdate(sql);
    connection.closeConnection();
  }

  //刪除操作
  public void unitDelete(Integer id) {
    String sql = "delete from tb_unit where id='" + id + "'";
    connection.executeUpdate(sql);
    connection.closeConnection();
  }

  //修改編號的操作
  public void unitUpdateNumber(Integer id, String number) {
    String unNumber = "unit-" + String.valueOf(id);
    String sql = "update tb_unit set unit_number='" + unNumber +
        "'  where unit_number='" + number + "'";
    connection.executeUpdate(sql);
    connection.closeConnection();
  }

  //修改操作
  public void unitUpdate(UnitVO vo) {
    String sql = "update tb_unit set unit_name='" + vo.getUnit_name() +
        "',unit_remark='" + vo.getUnit_remark() + "' where unit_number='" +
        vo.getUnit_number() + "'";
    connection.executeUpdate(sql);
    connection.closeConnection();
  }
}