/**
 *AssetModel.java：建立固定資產實體類。
 *包括：與數據庫進行增、刪、改、查操作。
 */

package zichan;

import java.sql.*;
import java.math.*;
import zichan.DBConnt;

public class AssetModel {
   String assetid;
   String assetname;
   String cateid;
   String subcateid;
   String model;
   String price;
   String purchasedate;
   String status;
   String useby;
   String remarks;
   DBConnt dbc;
   Connection conn;
   public AssetModel() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
   public AssetModel(String s0,
                     String s1,
                     String s2,
                     String s3,
                     String s4,
                     String s5,
                     String s6,
                     String s7,
                     String s8,
                     String s9) {
        assetid = s0;
        assetname = s1;
        cateid = s2;
        subcateid = s3;
        model = s4;
        price = s5;
        purchasedate = s6;
        status = s7;
        useby = s8;
        remarks = s9;
   }
//重新設定各項信息
  public void setAll(
                     String s1,
                     String s2,
                     String s3,
                     String s4,
                     String s5,
                     String s6,
                     String s7,
                     String s8,
                     String s9){
        assetname = s1;
        cateid = s2;
        subcateid = s3;
       model = s4;
       price = s5;
       purchasedate = s6;
       status = s7;
       useby = s8;
       remarks = s9;
  }

   public String getAssetId() {
        return assetid;
   }
   public String getAssetName() {
        return assetname;
   }
   public String getCateId() {
        return cateid;
   }
   public String getSubCateId() {
        return subcateid;
   }
   public String getModel() {
        return model;
   }
   public String getPrice() {
        return price;
   }
   public String getPurchaseDate() {
        return purchasedate;
   }
   public String getStatus() {
        return status;
   }
   public String getUseby() {
        return useby;
   }
   public String getRemarks() {
        return remarks;
   }
   public void insert() {
      dbc = new DBConnt();
      conn = dbc.getConnection();
      try {
      Statement stmt = conn.createStatement();
      ResultSet rset = stmt.executeQuery ("select max(assetid)+1 from asset");
      while (rset.next()) {
        assetid = rset.getString(1);
      }
      if (assetid == null) assetid = "1";
      PreparedStatement pstmt =
      conn.prepareStatement("insert into ASSET (assetid,asset_name,cateid,subcateid,model,price,purchase_date,status,useby,remarks) values (?,?,?,?,?,?,?,?,?,?)");
      pstmt.setInt(1, Integer.parseInt(assetid));
      pstmt.setString(2, assetname);
      pstmt.setInt(3, Integer.parseInt(cateid));
      pstmt.setInt(4, Integer.parseInt(subcateid));
      pstmt.setString(5, model);
      pstmt.setFloat(6, Float.parseFloat(price));
      pstmt.setString(7, purchasedate);
      pstmt.setString(8, status);
      pstmt.setString(9, useby);
      pstmt.setString(10, remarks);
      pstmt.execute();

      PreparedStatement pstmt1 =
      conn.prepareStatement("insert into LEND (assetid) values (?)");
      pstmt1.setInt(1, Integer.parseInt(assetid));
      pstmt1.execute();

      conn.commit();
      conn.close();
      }
      catch (java.sql.SQLException s) {
        s.printStackTrace();
      }
   }

   public void select() {
      String sql;
      dbc = new DBConnt();
      conn = dbc.getConnection();
      String sql = "SELECT assetid,rtrim(asset_name),cateid,subcateid,rtrim(model),price,purchase_date,status,useby,rtrim(remarks) FROM asset where assetid = "+Integer.parseInt(assetid);
      try {
      Statement stmt = conn.createStatement();
      ResultSet rset = stmt.executeQuery (sql);
      while (rset.next()) {
                assetname = rset.getString(2);
                cateid = rset.getString(3);
                subcateid = rset.getString(4);
                model = rset.getString(5);
                price = rset.getString(6);
                purchasedate = rset.getString(7);
                status = rset.getString(8);
                useby = rset.getString(9);
                remarks = rset.getString(10);
      }
      conn.commit();
      rset.close();
      stmt.close();
      conn.close();
      }
      catch (java.sql.SQLException s) {
        System.out.println("exception: " + s.getMessage());
      }
    }
//操作數據庫，更新各項信息

    public void update() {
      dbc = new DBConnt();
      conn = dbc.getConnection();
      try {
      PreparedStatement pstmt =
      conn.prepareStatement("update ASSET set asset_name=?,model=?,price=?,purchase_date=?,status=?,useby=?,remarks=? where assetid = ?");
      pstmt.setString(1, assetname);
      pstmt.setString(2, model);
      pstmt.setFloat(3, Float.parseFloat(price));
      pstmt.setString(4, purchasedate);
      pstmt.setString(5, status);
      pstmt.setString(6, useby);
      pstmt.setString(7, remarks);
      pstmt.setInt(8, Integer.parseInt(assetid));
      pstmt.execute();

      conn.commit();
      conn.close();
      }
      catch (java.sql.SQLException s) {
        System.out.println("exception: " + s.getMessage());
      }
   }

   public void delete() {
      dbc = new DBConnt();
      conn = dbc.getConnection();
      String sql = "delete asset where assetid = "+Integer.parseInt(assetid) ;
      try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      String sql1 = "delete lend where assetid = "+Integer.parseInt(assetid) ;
      PreparedStatement pstmt1 = conn.prepareStatement(sql1);
      pstmt1.execute();

      conn.commit();
      conn.close();

      assetid = null;
      assetname = null;
      cateid = "";
      subcateid = "";
      model = null;
      price = null;
      purchasedate = null;
      status = "";
      useby = null;
      remarks = null;

      }
      catch (java.sql.SQLException s) {
        System.out.println("exception: " + s.getMessage());
      }
   }

    private void jbInit() throws Exception {
    }
}