package com.aptech.cdjj.sex.action;

/**
 * <p>Title: 控制層</p>
 *
 * <p>Description: 接收用戶輸入的數(shù)據(jù)對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作</p>
 *
 * <p>Copyright: SEX</p>
 *
 * <p>Company: 北大青鳥</p>
 *
 * @author 楊凱
 * @version 1.0
 *
 */

/** 導(dǎo)包*/
import java.sql.*;
import java.util.ArrayList;
import com.aptech.cdjj.sex.database.SDBManager;
import java.util.Vector;

public class SPerControl {
    //實(shí)例化一個(gè) Statement 對(duì)象 s 為空
    Statement s = null;
    //實(shí)例化一個(gè) ResultSet 對(duì)象 rs 為空
    ResultSet rs = null;
    //實(shí)例化一個(gè) ArrayList 對(duì)象 arraylist
    ArrayList arraylist = new ArrayList();
    /**
     * 輸入增加人員的信息
     * @param id String 編號(hào)
     * @param name String 姓名
     * @param time String 時(shí)間
     * @param sex String 性別
     * @param age String 年齡
     * @param natives String 籍貫
     * @param dept String 部門
     * @param duty String 職務(wù)
     * @param phone String 電話
     * @return ArrayList
     */
    public ArrayList cont(String id,String name,String sex,String age,String stat,String phone,String add,String duty,String time){
        SDBManager.ConnToDB();
        //向數(shù)據(jù)庫(kù)增加新增人員數(shù)據(jù)信息對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作
        String sql ="insert into person values('"+id+"','"+name+"','"+sex+"','"+age+"','"+stat+"','"+phone+"','"+add+"','"+duty+"','"+time+"')";
        SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return arraylist;
    }
    public ArrayList selectOne(String sql){
        SDBManager.ConnToDB();
        ResultSet rs = SDBManager.executeQuery(sql);
        ArrayList temp = new ArrayList();
        try {
            while (rs.next()) {

                temp.add(rs.getString(1));
            }
        } catch (SQLException ex) {
        }
        return temp;
    }

    /**
     * 輸入修改人員的信息
     * @param id String 編號(hào)
     * @param name String 姓名
     * @param time String 入職時(shí)間
     * @param sex String 性別
     * @param age String 年齡
     * @param natives String 籍貫
     * @param dept String 部門
     * @param duty String 職務(wù)
     * @param phone String 電話
     * @return ArrayList
     */
    public ArrayList amend(String id,String name,String time,String sex,String age,String add,String phone,String stat,String duty){
        SDBManager.ConnToDB();
        //向數(shù)據(jù)庫(kù)增加修改人員數(shù)據(jù)信息對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作
        String sql="update person set pid='"+id+"',pname='"+name+"',psex='"+sex+"',page='"+age+"',pstep='"+duty+"',pphone='"+phone+"',paddress='"+add+"',pstate='"+stat+"',pdate='"+time+"' where pname='"+name+"'";
        SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return arraylist;
    }

    //輸入編號(hào)以編號(hào)進(jìn)行刪除
    public int amend1(String id){
        SDBManager.ConnToDB();
        String sql = "delete  from  person where pid='"+id+"'";
        int str = SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入姓名以姓名進(jìn)行刪除
    public int amend2(String name){
        SDBManager.ConnToDB();
        String sql = "delete from person where pname='"+name+"'";
        int str=  SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入性別以性別進(jìn)行刪除
    public int amend3(String sex){
        SDBManager.ConnToDB();
        String sql = "delete from person where psex='"+sex+"'";
        int str = SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入年齡以年齡進(jìn)行刪除
    public int amend4(String age){
        SDBManager.ConnToDB();
        String sql = "delete from person where page='"+age+"'";
        int str = SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入地址以地址進(jìn)行刪除
    public int amend5(String add){
        SDBManager.ConnToDB();
        String sql = "delete from person where paddress='"+add+"'";
        int str = SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入職務(wù)以職務(wù)進(jìn)行刪除
    public int amend6(String stat){
        SDBManager.ConnToDB();
        String sql = "delete from person where pstep='"+stat+"'";
        int str = SDBManager.executeUpdate(sql);
        SDBManager.closeObj();
        return str;
    }
    //輸入電話以電話進(jìn)行刪除
    public int amend7(String phone){
        SDBManager.ConnToDB();
        String sql = "delete from person where pphone = '"+phone+"'";
       int str = SDBManager.executeUpdate(sql);
       SDBManager.closeObj();
        return str;
    }
    //查詢所有人員信息
    public Vector que() throws SQLException {
        SDBManager.ConnToDB();
        String sql = "select * from person order by pid";
        rs = SDBManager.executeQuery(sql);
        Vector vect = new Vector();
        while(rs.next()){
            Vector vec = new Vector();
            vec.add(rs.getString("pid"));
            vec.add(rs.getString("pname"));
            vec.add(rs.getString("psex"));
            vec.add(rs.getString("page"));
            vec.add(rs.getString("pstep"));
            vec.add(rs.getString("pphone"));
            vec.add(rs.getString("paddress"));
            vec.add(rs.getString("pstate"));
            vec.add(rs.getString("pdate"));
            vect.add(vec);
        }
        SDBManager.closeObj();
        return vect;
    }


    //以編號(hào)進(jìn)行查詢
    public Vector query1(String id) throws SQLException {
        SDBManager.ConnToDB();
        String sql = "select * from Person where pid='"+id+"'";
        rs = SDBManager.executeQuery(sql);
        Vector vect = new Vector();
        while(rs.next()){
            Vector vec = new Vector();
            vec.add(rs.getString("pid"));
            vec.add(rs.getString("pname"));
            vec.add(rs.getString("psex"));
            vec.add(rs.getString("page"));
            vec.add(rs.getString("pstep"));
            vec.add(rs.getString("pphone"));
            vec.add(rs.getString("paddress"));
            vec.add(rs.getString("pstate"));
            vec.add(rs.getString("pdate"));
            vect.add(vec);
        }
        SDBManager.closeObj();
       return vect;
    }

    //以姓名進(jìn)行查詢
    public Vector query2(String name) throws SQLException {
      SDBManager.ConnToDB();
      String sql = "select * from Person where pname='"+name+"'";
      rs = SDBManager.executeQuery(sql);
      Vector vect = new Vector();
      while(rs.next()){
          Vector vec = new Vector();
          vec.add(rs.getString("pid"));
          vec.add(rs.getString("pname"));
          vec.add(rs.getString("psex"));
          vec.add(rs.getString("page"));
          vec.add(rs.getString("pstep"));
          vec.add(rs.getString("pphone"));
          vec.add(rs.getString("paddress"));
          vec.add(rs.getString("pstate"));
          vec.add(rs.getString("pdate"));
          vect.add(vec);
      }
      SDBManager.closeObj();
     return vect;
  }
  //以性別進(jìn)行查詢
  public Vector query3(String sex) throws SQLException {
      SDBManager.ConnToDB();
      String sql = "select * from Person where psex='" + sex + "'";
      rs = SDBManager.executeQuery(sql);
      Vector vect = new Vector();
      while (rs.next()) {
          Vector vec = new Vector();
          vec.add(rs.getString("pid"));
          vec.add(rs.getString("pname"));
          vec.add(rs.getString("psex"));
          vec.add(rs.getString("page"));
          vec.add(rs.getString("pstep"));
          vec.add(rs.getString("pphone"));
          vec.add(rs.getString("paddress"));
          vec.add(rs.getString("pstate"));
          vec.add(rs.getString("pdate"));
          vect.add(vec);
      }
      SDBManager.closeObj();
      return vect;
  }

  //以年齡進(jìn)行查詢
  public Vector query4(String age) throws SQLException {
      SDBManager.ConnToDB();
      String sql = "select * from Person where page='" + age + "'";
      rs = SDBManager.executeQuery(sql);
      Vector vect = new Vector();
      while (rs.next()) {
          Vector vec = new Vector();
          vec.add(rs.getString("pid"));
          vec.add(rs.getString("pname"));
          vec.add(rs.getString("psex"));
          vec.add(rs.getString("page"));
          vec.add(rs.getString("pstep"));
          vec.add(rs.getString("pphone"));
          vec.add(rs.getString("paddress"));
          vec.add(rs.getString("pstate"));
          vec.add(rs.getString("pdate"));
          vect.add(vec);
      }
      SDBManager.closeObj();
      return vect;
  }

  //以地址進(jìn)行查詢
  public Vector query5(String add) throws SQLException {
      SDBManager.ConnToDB();
      String sql = "select * from Person where paddress='" + add + "'";
      rs = SDBManager.executeQuery(sql);
      Vector vect = new Vector();
      while (rs.next()) {
          Vector vec = new Vector();
          vec.add(rs.getString("pid"));
          vec.add(rs.getString("pname"));
          vec.add(rs.getString("psex"));
          vec.add(rs.getString("page"));
          vec.add(rs.getString("pstep"));
          vec.add(rs.getString("pphone"));
          vec.add(rs.getString("paddress"));
          vec.add(rs.getString("pstate"));
          vec.add(rs.getString("pdate"));
          vect.add(vec);
      }
      SDBManager.closeObj();
      return vect;
  }

  //以職務(wù)進(jìn)行查詢
  public Vector query6(String stat) throws SQLException {
    SDBManager.ConnToDB();
    String sql = "select * from Person where pstep='" + stat + "'";
    rs = SDBManager.executeQuery(sql);
    Vector vect = new Vector();
    while (rs.next()) {
        Vector vec = new Vector();
        vec.add(rs.getString("pid"));
        vec.add(rs.getString("pname"));
        vec.add(rs.getString("psex"));
        vec.add(rs.getString("page"));
        vec.add(rs.getString("pstep"));
        vec.add(rs.getString("pphone"));
        vec.add(rs.getString("paddress"));
        vec.add(rs.getString("pstate"));
        vec.add(rs.getString("pdate"));
        vect.add(vec);
    }
    SDBManager.closeObj();
    return vect;
  }

  //以電話進(jìn)行查詢
  public Vector query7(String phone) throws SQLException {
   SDBManager.ConnToDB();
   String sql = "select * from Person where pphone='" + phone + "'";
   rs = SDBManager.executeQuery(sql);
   Vector vect = new Vector();
   while (rs.next()) {
       Vector vec = new Vector();
       vec.add(rs.getString("pid"));
       vec.add(rs.getString("pname"));
       vec.add(rs.getString("psex"));
       vec.add(rs.getString("page"));
       vec.add(rs.getString("pstep"));
       vec.add(rs.getString("pphone"));
       vec.add(rs.getString("paddress"));
       vec.add(rs.getString("pstate"));
       vec.add(rs.getString("pdate"));
       vect.add(vec);
   }
   SDBManager.closeObj();
   return vect;
  }

}