package device;

import java.util.*;
import java.io.*;
import java.sql.*;
import java.text.*;
import comm.*;


public class Device {
    private String DevId;		// 設備編號
	private String DevName;		// 名稱
    private String DevType;		// 型號
	private String DevCate;		// 分類稱
	private String DevPro;		// 生產(chǎn)廠家
	private int DevNumber;		    // 數(shù)量
	private long Price;		// 單價
	private String Unit;		// 單位
	private String PostTime;	    // 提交時間
	private String Poster;		// 提交人編號

	StringOper so = new StringOper();

    public boolean getDevice()throws Exception
    {
        int conditionNo = 0;

        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        String sql = "select to_char(PostTime,'YYYY-MM-DD HH24:MI:SS') PostTime,DevId,DevName,DevType,DevCate,DevPro,";
		sql+= "DevNumber,Price,Poster,Unit from dev_info where DevId='"+DevId+"'";

        try
        {
            rs = o_DBOper.getResultSet(sql);
            if(rs.next())
            {
 				setDevId(so.ReplaceNull(rs.getString("DevId")));
                setDevName(so.ReplaceNull(rs.getString("DevName")));
				setDevType(so.ReplaceNull(rs.getString("DevType")));
				setDevCate(so.ReplaceNull(rs.getString("DevCate")));
				setDevPro(so.ReplaceNull(rs.getString("DevPro")));
				setUnit(so.ReplaceNull(rs.getString("Unit")));
				setPostTime(so.ReplaceNull(rs.getString("PostTime")));
 				setDevNumber(rs.getInt("DevNumber"));
				setPrice(rs.getLong("Price"));
				setPoster(so.ReplaceNull(rs.getString("Poster")));
               return true;
            }
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
        return false;
    }

	public Vector getMoreDevice() throws Exception
    {
        Vector v_Device = new Vector();
        int conditionNo = 0;

        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        String sql = "select to_char(PostTime,'YYYY-MM-DD HH24:MI:SS') PostTime,DevId,DevName,DevType,DevCate,DevPro,";
		sql+= "DevNumber,Price,Poster,Unit from dev_info";
        String condition = " where ";
        try
        {
            if(DevId != null)
            {
                condition += "DevId='"+DevId+"'";
                conditionNo++;
            }
			if(DevName != null)
            {
				if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += "DevName='"+DevName+"'";
                conditionNo++;
            }
            if(PostTime != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " PostTime='"+PostTime+"'";
                conditionNo++;
            }
			if(DevNumber != 0)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " DevNumber="+DevNumber;
                conditionNo++;
            }
			if(Poster != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " Poster='"+Poster+"'";
                conditionNo++;
            }
			if(DevType != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " DevType='"+DevType+"'";
                conditionNo++;
            }
			if(DevCate != null)
            {
                if(conditionNo > 0)
                {
                    condition += " and";
                }
                condition += " DevCate='"+DevCate+"'";
                conditionNo++;
            }
            if(conditionNo > 0)
            {
                sql += condition;
            }
            sql += " order by DevCate,DevId desc";

            rs = o_DBOper.getResultSet(sql);
	//System.out.println(sql);
			while(rs.next())
            {
                Device o_Device2 = new Device();
				o_Device2.setDevId(so.ReplaceNull(rs.getString("DevId")));
                o_Device2.setDevName(so.ReplaceNull(rs.getString("DevName")));
				o_Device2.setDevType(so.ReplaceNull(rs.getString("DevType")));
				o_Device2.setDevCate(so.ReplaceNull(rs.getString("DevCate")));
				o_Device2.setDevPro(so.ReplaceNull(rs.getString("DevPro")));
				o_Device2.setPostTime(so.ReplaceNull(rs.getString("PostTime")));
				o_Device2.setUnit(so.ReplaceNull(rs.getString("Unit")));
 				o_Device2.setDevNumber(rs.getInt("DevNumber"));
				o_Device2.setPrice(rs.getLong("Price"));
				o_Device2.setPoster(so.ReplaceNull(rs.getString("Poster")));
                v_Device.add(o_Device2);
            }
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
        return v_Device;
    }

//刪除設備
	public void DeleteDevice(String DevId) throws Exception
    {
        DBOper o_DBOper = new DBOper();
		ResultSet rs = null;

        String sql_dlt = "delete from dev_info where DevId ='" + DevId+"'";
        try
        {
            o_DBOper.getResultSet(sql_dlt);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }
//插入設備
	public void CreateDevice() throws Exception
    {
        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        String sql = "Insert into dev_info(DevId,DevName,DevType,DevCate,DevPro,PostTime,Poster,price,Unit,DevNumber)";
		sql = sql + "values('"+DevId+"','"+DevName+"','"+DevType+"','"+DevCate+"','"+DevPro;
		sql += "',sysdate,'"+Poster+"',"+Price+",'"+Unit+"',"+DevNumber+")";
        try
        {
            o_DBOper.getResultSet(sql);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }
//update設備
	public void UpdateDevice() throws Exception
    {
        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;

        String sql = "Update dev_info set DevId='"+DevId+"',DevName='"+DevName+"',DevType='"+DevType+"',DevCate='";
		sql = sql + DevCate+"',DevPro='"+DevPro+"',PostTime=sysdate,price="+Price;
		sql = sql + ",Unit='"+Unit+"',DevNumber="+DevNumber+" where to_char(PostTime,'YYYY-MM-DD HH24:MI:SS')='"+PostTime+"'";
	//System.out.println("UPDATE SQL :"+sql);
        try
        {
            o_DBOper.getResultSet(sql);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }
//update設備數(shù)量，包括領用iNum>0、歸還iNum<0
	public void UpdateNumber(int iNum) throws Exception
    {
        DBOper o_DBOper = new DBOper();
        ResultSet rs = null;
		String sql="";
		if(iNum>0) 
			sql = "update dev_info set DevNumber=DevNumber-"+iNum+" where DevId='"+DevId+"'";
		else
		{
			iNum = 0 - iNum;
			sql = "update dev_info set DevNumber=DevNumber+"+iNum+" where DevId='"+DevId+"'";
		}
        try
        {
            o_DBOper.getResultSet(sql);
        }
        catch(Exception e)
        {
            throw new Exception(e.getMessage());
        }
	    finally
	    {
            try {o_DBOper.close();} catch (Exception e) { System.out.print(e.toString());}
	    }
    }

	public void setDevId(String DevId){
        this.DevId = DevId;
    }
    public String getDevId(){
        return this.DevId;
    }
    public void setDevName(String DevName){
        this.DevName = DevName;
    }
    public String getDevName(){
        return this.DevName;
    }
    public void setDevType(String DevType){
        this.DevType = DevType;
    }
	public void setDevPro(String DevPro){
        this.DevPro = DevPro;
    }
    public String getDevPro(){
        return this.DevPro;
    }	
	public void setDevCate(String DevCate){
        this.DevCate = DevCate;
    }
    public String getDevCate(){
        return this.DevCate;
    }
	public void setUnit(String Unit){
        this.Unit = Unit;
    }
    public String getUnit(){
        return this.Unit;
    }

	public void setPrice(long Price){
        this.Price = Price;
    }
    public long getPrice(){
        return this.Price;
    }
	public void setDevNumber(int DevNumber){
        this.DevNumber = DevNumber;
    }
    public int getDevNumber(){
        return this.DevNumber;
    }
    public String getDevType(){
        return this.DevType;
    }
	public void setPostTime(String PostTime){
        this.PostTime = PostTime;
    }
    public String getPostTime(){
        return this.PostTime;
    }
	public void setPoster(String Poster){
        this.Poster = Poster;
    }
    public String getPoster(){
        return this.Poster;
    }
 }