package zr.zrpower.ejb.sys;

import java.util.ArrayList;
import zr.zrpower.common.util.Log;
import zr.zrpower.common.db.*;
import zr.zrpower.common.util.SysPreperty;
import zr.zrpower.entity.sys.BPIP_MENU;
import zr.zrpower.entity.sys.BPIP_ROLE;
import zr.zrpower.entity.sys.BPIP_USER_ROLE;
import zr.zrpower.entity.sys.BPIP_USER;
import java.util.*;

/**
 * 系统管理
 **/
public class SecurityService
  {
  static private Log log;
  dbEngine dbengine;
  static private int clients=0;

  public SecurityService()
  {
    if (clients<1)
    {
      log = new Log();
      log.SetLogForClass("SecurityService");
      log.SetLogFile("SecurityEjb.log");

    }
    dbengine = new dbEngine(SysPreperty.getProperty().MainDataSource,
                              SysPreperty.getProperty().IsConvert);
    dbengine.initialize();

    clients++;
  }


  /**
   * 功能或作用：获取菜单列表
   * @param strWhere查询条件
   * @return returnValue 返回记录集
   */
  public BPIP_MENU[] GetMenuList(String strWhere) {
    BPIP_MENU[] returnValue = null;
    StringBuffer strSQL=new StringBuffer();
    strSQL.append("Select * From BPIP_MENU "+strWhere);
    dbSet dbset = dbengine.QuerySQL(strSQL.toString());
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        returnValue = new BPIP_MENU[dbset.RowCount()];
        for (int i = 0; i < dbset.RowCount(); i++) {
          returnValue[i] = new BPIP_MENU();
          returnValue[i].fullData(dbset.Row(i));
        }
      }
      dbset = null;
    }
    return returnValue;
  }
  /**
    * 功能或作用：获取菜单列表
    * @param strWhere查询条件
    * @return returnValue 返回记录集
    */
   public BPIP_MENU[] GetMenuList1(String strPARENTNO) {
     BPIP_MENU[] returnValue = null;
     String strSQL="";
     strSQL = "Select * From BPIP_MENU where MENUNO like '"+strPARENTNO+"___' order by MNCODE,MENUNO";
     dbSet dbset = dbengine.QuerySQL(strSQL.toString());
     if (dbset != null) {
       if (dbset.RowCount() > 0) {
         returnValue = new BPIP_MENU[dbset.RowCount()];
         for (int i = 0; i < dbset.RowCount(); i++) {
           returnValue[i] = new BPIP_MENU();
           returnValue[i].fullData(dbset.Row(i));
         }
       }
       dbset = null;
     }
     return returnValue;
   }


  /**
    * 功能或作用：获取菜单列表
    * @param strWhere查询条件
    * @return returnValue 返回记录集
    */
   public BPIP_MENU[] GetMenuAllList() {
     BPIP_MENU[] returnValue = null;
     String strSQL = new String();
     strSQL = "Select * From BPIP_MENU Where ISPOWER = '1' order by MNCODE,MENUNO ";
     dbSet dbset = dbengine.QuerySQL(strSQL);
     if (dbset != null) {
       if (dbset.RowCount() > 0) {
         returnValue = new BPIP_MENU[dbset.RowCount()];
         for (int i = 0; i < dbset.RowCount(); i++) {
           returnValue[i] = new BPIP_MENU();
           returnValue[i].fullData(dbset.Row(i));
         }
       }
       dbset = null;
     }
     return returnValue;
  }
  /**
   * 功能或作用：添加新菜单
   * @param Menu 菜单实体
   * @param strPARENTNO 父结点
   * @return returnValue 返回是否成功
   */
  public boolean AddMenu(BPIP_MENU Menu, String strPARENTNO) {
    if (Menu != null) {
      //得到最大编号
      String strMenu_No = "001";
      if (Menu != null && strPARENTNO != "") {
        int intMoxno = GetMaxNo(strPARENTNO, 1);
        if (intMoxno == 0) {
          strMenu_No = strPARENTNO + "001";
        }
        else {
          strMenu_No = strPARENTNO +
              String.valueOf(intMoxno + 1001).substring(1, 4);
        }

      }
      else {
        int intMoxno = GetMaxNo("", 0);
        strMenu_No = String.valueOf(intMoxno + 1001).substring(1, 4);
      }
      //得到菜单编号
      Menu.setMENUNO(strMenu_No);

      if (dbengine.ExecuteInsert(Menu.getData())) {
        return true;
      }
    }

    return false;
  }

  /**
   * 删除菜单
   * @param DepID String
   * @return boolean
   */
  //删除菜单
  public boolean DeleteMenu(String MenuID) {
    String  strSQL = "";
    //检查要删除的菜单是否有下级菜单
    strSQL = "Select count(*) as MenuCount From BPIP_MENU Where MENUNO like '"+ MenuID+"___'";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset.Row(0).Column("MenuCount").getInteger() > 0) {
      dbset = null;
      return false;
    }

    //将父节点的子菜单数减
    String[] strSQLs = new String[2];
    strSQL = "Delete From BPIP_ROLE_RERMISSISSON Where MENUNO='"+MenuID+"'";
    strSQLs[0] =strSQL;
    strSQL = "Delete From BPIP_MENU Where MENUNO='"+MenuID+"'";
    strSQLs[1] =strSQL;
    return dbengine.ExecuteSQLs(strSQLs);
  }

  /**
   * 编辑菜单
   * @param BPIP_MENU  菜单信息实体
   * @return boolean   保存是否成功,成功或是失败消息
   */
  public boolean UpdateMenu(BPIP_MENU Menu) {
    StringBuffer strSQL = new StringBuffer();

    if (Menu != null) {
      dbRow row = Menu.getData();
      strSQL.append("MENUNO='"+Menu.getMENUNO()+"'");
      return dbengine.ExecuteEdit(row, strSQL.toString());
    }
    return false;
  }

  //得到最大的菜单编号
  private int GetMaxNo(String strMenu_No, int intFlag) {
    int intResult = 0;
    StringBuffer strSQL = new StringBuffer();
    String strMaxNO = "0";
    switch (intFlag) {
      case 0: // '\0'
        strSQL.append("SELECT MAX(MENUNO) AS MAX_NO FROM BPIP_MENU WHERE MENUNO like '___'");
        break;
      case 1: // '\001'
        strSQL.append("SELECT MAX(MENUNO) AS MAX_NO FROM BPIP_MENU WHERE MENUNO like '"+
            strMenu_No+"___'");
        break;
    }
    try {
      dbSet dbset = dbengine.QuerySQL(strSQL.toString());
      if (dbset != null) {
        strMaxNO = dbset.Row(0).Column("MAX_NO").getString();
        if (strMaxNO.length() == 0) {
          strMaxNO = "0";
        }
        else {
          strMaxNO = strMaxNO.substring(strMaxNO.length() - 3, strMaxNO.length());
        }
        dbset = null;
      }
    }
    catch (Exception ex) {
      System.err.println(ex.toString());
    }
    intResult = Integer.parseInt(strMaxNO);
    return intResult;
  }


  /**
   * 功能或作用：获取角色列表
   * @param strWhere查询条件
   * @return returnValue 返回记录集
   */
  public BPIP_ROLE[] GetRoleList(String strWhere) {
    BPIP_ROLE[] returnValue = null;
    dbSet dbset = dbengine.QuerySQL("Select * From BPIP_ROLE "+strWhere);
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        returnValue = new BPIP_ROLE[dbset.RowCount()];
        for (int i = 0; i < dbset.RowCount(); i++) {
          returnValue[i] = new BPIP_ROLE();
          returnValue[i].fullData(dbset.Row(i));
        }
      }
      dbset = null;
    }
    return returnValue;
  }
  /**
  * 功能或作用：获取角色列表
  * @param strWhere查询条件
  * @return returnValue 返回记录集
  */
 public BPIP_ROLE[] GetRoleList2(String strRole_ID) {
   BPIP_ROLE[] returnValue = null;
   dbSet dbset = dbengine.QuerySQL("Select * From BPIP_ROLE where ROLEID="+strRole_ID);
   if (dbset != null) {
     if (dbset.RowCount() > 0) {
       returnValue = new BPIP_ROLE[dbset.RowCount()];
       for (int i = 0; i < dbset.RowCount(); i++) {
         returnValue[i] = new BPIP_ROLE();
         returnValue[i].fullData(dbset.Row(i));
       }
     }
     dbset = null;
   }
   return returnValue;
 }

  /**
    * 功能或作用：获取角色列表
    * @param strWhere查询条件
    * @return returnValue 返回记录集
    */
   public BPIP_ROLE[] GetRoleList1() {
     BPIP_ROLE[] returnValue = null;
     dbSet dbset = dbengine.QuerySQL("Select * From BPIP_ROLE  order by ROLEID");
     if (dbset != null) {
       if (dbset.RowCount() > 0) {
         returnValue = new BPIP_ROLE[dbset.RowCount()];
         for (int i = 0; i < dbset.RowCount(); i++) {
           returnValue[i] = new BPIP_ROLE();
           returnValue[i].fullData(dbset.Row(i));
         }
       }
       dbset = null;
     }
     return returnValue;
  }
  /**
   * 功能或作用：生成角色用户列表
   * @param strRole_No角色编号
   * @param Lst_Name列表名称
   * @param Is_Enabled是否只读
   * @return returnValue 返回记录集
   */
  public String Role_User_List(String strRole_No,String Lst_Name,
                               String Is_Enabled) {
    String strResult = "";
    String strSQL = "";
    strSQL = "Select a.ROLEID,a.USERID,a.UNITID,b.USERID,b.NAME,c.UNITID,c.UNITNAME from BPIP_USER_ROLE  a Left Join BPIP_USER b on a.USERID=b.USERID Left Join BPIP_UNIT c On a.UNITID=c.UNITID where a.ROLEID = '"+strRole_No+
        "'  order by a.ROLEID,a.USERID";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    strResult ="<select name='"+Lst_Name+
        "' class='SelectStyle2' size=20 multiple "+Is_Enabled+">\r\n";

    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        for (int i = 0; i < dbset.RowCount(); i++) {
          strResult = strResult + "<option value='"+
              dbset.Row(i).Column("USERID").getString()+"'>"+
              dbset.Row(i).Column("UNITNAME").getString()+"|"+
              dbset.Row(i).Column("NAME").getString()+
              "</option>\r\n";
        }
      }
      dbset = null;
    }
    strResult = strResult + "</select>\r\n";
    return strResult;
  }

  /**
   * 功能或作用：根据用户编号得到用户的单位名称
   * @param strUser_No用户编号
   * @return returnValue 返回单位名称
   */
  public String User_UnitName(String strUser_No) {
    String strUNITID = "";
    String strResult = "";
    String strSQL = "";
    strSQL = "Select UNITID from BPIP_USER where USERID = '"+strUser_No+"'";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset != null) {
      strUNITID = dbset.Row(0).Column("UNITID").getString();
      strSQL = "Select UNITNAME from BPIP_UNIT where UNITID = '"+strUNITID+ "'";
      dbSet dbset1 = dbengine.QuerySQL(strSQL);
      if (dbset1 != null) {
        strResult = dbset1.Row(0).Column("UNITNAME").getString();
        dbset1 = null;
      }
      dbset = null;
    }
    return strResult;
  }

  /**
   * 功能或作用：根据用户编号得到用户的真实名称
   * @param strUser_No用户编号
   * @return returnValue 返回用户名称
   */
  public String GetUserTrueName(String strUser_No) {
    String strResult = "";
    String strSQL = "";
    strSQL = "Select NAME from BPIP_USER where USERID = '"+strUser_No+"'";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset != null) {
      strResult = dbset.Row(0).Column("NAME").getString();
      dbset = null;
    }
    return strResult;
  }

  /**
   * 功能或作用：得到所有用户的选择列表
   * @param Lst_Name列表名称
   * @param Is_Enabled是否只读
   * @return returnValue 返回用户名称
   */
  public String User_AllList(String Lst_Name,String Is_Enabled) {
    String strResult = "";
    String strSQL = "";
    strSQL = "Select a.USERID,a.UNITID,a.NAME,b.UNITID,b.UNITNAME from BPIP_USER a Left Join BPIP_UNIT b on a.UNITID=b.UNITID where a.USERSTATE='0'  order by a.UNITID,a.USERID";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    strResult = "<select name='"+Lst_Name+
        "' class='SelectStyle2' size=20 multiple "+Is_Enabled+">\r\n";
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        for (int i = 0; i < dbset.RowCount(); i++) {
          strResult = strResult + "<option value='"+
              dbset.Row(i).Column("USERID").getString()+"'>"+
              dbset.Row(i).Column("UNITNAME").getString()+"|"+
              dbset.Row(i).Column("NAME").getString()+
              "</option>\r\n";
        }
      }
      dbset = null;
    }
    strResult = strResult + "</select>\r\n";
    return strResult;
  }

  /**
   * 功能或作用：添加角色
   * @param Role 角色实体
   * @param Menu_No 选择的菜单编号
   * @return returnValue 返回值
   */
  public boolean AddRole(BPIP_ROLE Role, String Menu_No) {
    boolean strResult = false;
    int i = 0;
    ArrayList arrList_Menu_No = new ArrayList();
    String strRoleName = Role.getROLENAME();
    String strRoleID = String.valueOf(Role.getROLEID());
    if (isExitRole_Name(strRoleID, strRoleName)) {
      return false;
    }
    String strSQL = "Select * From BPIP_ROLE Where ROLEID="+Role.getROLEID();
    dbSet mdbset = dbengine.QuerySQL(strSQL);
     if (mdbset != null) {
        return false;
       }
      else {
    if (dbengine.ExecuteInsert(Role.getData())) {
      //得到刚插入的角色ID
      strRoleID = String.valueOf(GetRoleID(strRoleName));
      Menu_No+=",";
        try
        {
          arrList_Menu_No = GetArrayList(Menu_No, ",");

        }catch(Exception ex)
        {
          log.WriteLog(ex.getMessage());
        }
    //保存角色对应菜单
      if (arrList_Menu_No.size() > 0) {
           for (i = 0; i < arrList_Menu_No.size(); i++) {
              strSQL = "Insert into BPIP_ROLE_RERMISSISSON(ROLEID,MENUNO) values ("+
               strRoleID+",'"+arrList_Menu_No.get(i).toString().trim()+"')";
              if(dbengine.ExecuteSQL(strSQL.toString())){
                 strResult = true;
              }
           }
      }

    }
    else {
      return false;
    }
  }
    return strResult;
  }

  /**
   * 功能或作用：判断是否已经存在角色
   * @param strRole_ID 角色ID
   * @param strRole_Name 角色名称
   * @return returnValue 返回是否
   */
  private boolean isExitRole_Name(String strRole_ID, String strRole_Name) {
    boolean blResult = false;
    String  strSQL = "";
    if (strRole_ID != "") {
      strSQL = "Select count(*) as RoleCount From BPIP_ROLE Where ROLENAME='"+
          strRole_Name+"' and ROLEID<>"+strRole_ID;
    }
    else {
      strSQL = "Select count(*) as RoleCount From BPIP_ROLE Where ROLENAME='"+ strRole_Name+"'";
    }
    dbSet dbset1 = dbengine.QuerySQL(strSQL);
    if (dbset1 != null) {
      if (dbset1.Row(0).Column("RoleCount").getInteger() > 0) {
        return true;
      }
      dbset1 = null;
    }
    return blResult;
  }

  /**
   * 功能或作用：分析规则字符串，生成数组
   * @param strItems 字符串
   * @param strItemMark 标识符
   * @return 返回数组
   */
  public ArrayList GetArrayList(String strItems, String strItemMark) {
    int intItemLen, i = 0, n = 0;
    String strItem;
    ArrayList strList = new ArrayList();
    intItemLen = strItems.length();
    while (i < intItemLen) {
      n = strItems.indexOf(strItemMark, i);
      strItem = strItems.substring(i, n);
      strList.add(strItem);
      i = n + 1;
    }
    return strList;
  }

  /**
   * 功能或作用：根据角色名称得到角色编号
   * @param strRoleName角色名称
   * @return returnValue 返回角色编号
   */
  public long GetRoleID(String strRoleName) {
    StringBuffer  strSQL = new   StringBuffer();
    long longResult = 0;
    strSQL.append("Select ROLEID from BPIP_ROLE where ROLENAME = '"+strRoleName+ "'");
    dbSet dbset = dbengine.QuerySQL(strSQL.toString());
    if (dbset != null) {
      longResult = dbset.Row(0).Column("ROLEID").getInteger();
      dbset = null;
    }
    return longResult;
  }

  /**
   * 功能或作用：根据角色ID得到拥有该角色的用户列表
   * @param strRoleID角色ID
   * @return returnValue 返回记录集
   */
  public BPIP_USER[] GetRoleUserList(String strRoleID) {
    BPIP_USER[] returnValue = null;
    String  strSQL = "";
    strSQL = "select * from BPIP_USER where USERID in (select USERID from BPIP_USER_ROLE where ROLEID="+
        strRoleID+") order by UNITID";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        returnValue = new BPIP_USER[dbset.RowCount()];
        for (int i = 0; i < dbset.RowCount(); i++) {
          returnValue[i] = new BPIP_USER();
          returnValue[i].fullData(dbset.Row(i));
        }
      }
      dbset = null;
    }
    return returnValue;
  }

  /**
   * 功能或作用：根据角色ID得到属于该角色的菜单列表
   * @param strRoleID角色ID
   * @return returnValue 返回记录集
   */
  public BPIP_MENU[] GetRoleMenuList(String strRoleID) {
    BPIP_MENU[] returnValue = null;
    String  strSQL = "";
    strSQL = "select * from BPIP_MENU where MENUNO in (select MENUNO from BPIP_ROLE_RERMISSISSON where ROLEID="+
        strRoleID+") order by MNCODE,MENUNO";
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        returnValue = new BPIP_MENU[dbset.RowCount()];
        for (int i = 0; i < dbset.RowCount(); i++) {
          returnValue[i] = new BPIP_MENU();
          returnValue[i].fullData(dbset.Row(i));
        }
      }
      dbset = null;
    }
    return returnValue;
  }

  /**
   * 功能或作用：得到当前编号的父菜单名称
   * @param strMenu_No角色ID
   * @param intFlag菜单层数
   * @return returnValue 返回菜单名称
   */
  public String Get_Menu_ParentName(String strMenu_No, int intFlag) {
    String strResult = "";
    String strSQL = "";
    switch (intFlag) {
      case 1: // '\000'
        strSQL = "SELECT MENUNAME FROM BPIP_MENU WHERE MENUNO = '"+
            strMenu_No.substring(0, 3)+"'";
        break;
      case 2: // '\000000'
        strSQL = "SELECT MENUNAME FROM BPIP_MENU WHERE MENUNO = '"+
            strMenu_No.substring(0, strMenu_No.length() - 3)+"'";
        break;
    }
    dbSet dbset = dbengine.QuerySQL(strSQL);
    if (dbset != null) {
      if (dbset.RowCount() > 0) {
        strResult = dbset.Row(0).Column("MENUNAME").getString();
      }
      dbset = null;
    }
    return strResult;
  }

  /**
   * 功能或作用：根据菜单编号和角色编号删除角色权限表的记录
   * @param strRole_No角色ID
   * @param strMenu_No菜单编号
   * @return returnValue 返回是否删除成功
   */
  public boolean Role_Menu_Del(String strRole_No, String strMenu_No) {
        strMenu_No=strMenu_No.replaceAll("\\,","','");
        String  strSQL = "";
        strSQL = "DELETE FROM BPIP_ROLE_RERMISSISSON WHERE ROLEID = "+
          strRole_No+" and MENUNO in ('"+strMenu_No+"')";
        return dbengine.ExecuteSQL(strSQL);
   }


  /**
   * 功能或作用：根据用户编号和角色编号删除用户角色表的记录
   * @param strRole_No角色ID
   * @param strUser_No用户编号
   * @return returnValue 返回是否删除成功
   */
  public boolean Role_User_Del(String strRole_No, String strUser_No) {
         strUser_No=strUser_No.replaceAll("\\,","','");
         String strSQL = "";
         strSQL = "DELETE FROM BPIP_USER_ROLE WHERE ROLEID = "+
         strRole_No+" and USERID in ('"+strUser_No+"')";
         return dbengine.ExecuteSQL(strSQL);
  }
  /**
     * 功能或作用：根据用户编号和角色编号删除用户角色表的记录
     * @param strRole_No角色ID
     * @param strUser_No用户编号
     * @return returnValue 返回是否删除成功
     */
    private boolean Role_User_Del1(String strRole_No, String strUser_No,String UnitId) {
        strUser_No=strUser_No.trim().replaceAll("\\,","','");
        String strSQL = "";
        strSQL = "DELETE FROM BPIP_USER_ROLE WHERE ROLEID = "+
          strRole_No+" and UNITID like '"+UnitId+"%' and USERID not in ('"+strUser_No.trim()+"')";
        return dbengine.ExecuteSQL(strSQL);
  }
  /**
  * 功能或作用：判断是否已经存在角色
  * @param strRole_ID 角色ID
  * @param strRole_Name 角色名称
  * @return returnValue 返回是否
  */
 private boolean isExitRole_ID(String strRole_ID) {
   boolean blResult = false;
   String  strSQL = "";
   if (strRole_ID != "") {
        strSQL = "Select count(*) as RoleCount From BPIP_USER_ROLE Where ROLEID ="+strRole_ID;
   }
   dbSet dbset1 = dbengine.QuerySQL(strSQL);
   if (dbset1 != null) {
     if (dbset1.Row(0).Column("RoleCount").getInteger() > 0) {
       return true;
     }
     dbset1 = null;
   }
   return blResult;
 }

  /**
   * 功能或作用：编辑角色
   * @param Role 角色实体
   * @param formfields 选择的列表(用户)
   * @param Menu_No 选择的菜单编号
   * @return returnValue 返回值
   */
  public boolean EditRole(BPIP_ROLE Role,String Menu_No) {
    boolean strResult = false;
    int i = 0;
    ArrayList arrList_Menu_No = new ArrayList();
    String  strSQL = "";
    String strRoleName = Role.getROLENAME();
    String strRoleID = String.valueOf(Role.getROLEID());
    if (isExitRole_Name(strRoleID, strRoleName)&&isExitRole_ID(strRoleID)) {
      return false;
    }
    dbRow row = Role.getData();
    strSQL = "DELETE FROM BPIP_ROLE_RERMISSISSON WHERE ROLEID = "+
       strRoleID+" ";
       dbengine.ExecuteSQL(strSQL);

       Menu_No+=",";
    if (dbengine.ExecuteEdit(row, "ROLEID=" + strRoleID)) {
        try
        {
          arrList_Menu_No = GetArrayList(Menu_No, ",");
        }catch(Exception ex)
        {
          log.WriteLog(ex.getMessage());
        }
     //保存角色对应菜单
     if (arrList_Menu_No.size() > 0) {
        for (i = 0; i < arrList_Menu_No.size(); i++) {
         strSQL = "Insert into BPIP_ROLE_RERMISSISSON(ROLEID,MENUNO) values ("+
              strRoleID+",'"+arrList_Menu_No.get(i).toString().trim()+ "')";
              strResult = dbengine.ExecuteSQL(strSQL);
        }
       }

    }
    else {
      return false;
    }
    return strResult;
  }
  /**
     * 功能或作用：编辑角色
     * @param Role 角色实体
     * @param formfields 选择的列表(用户)
     * @param Menu_No 选择的菜单编号
     * @return returnValue 返回值
     */
    public boolean EditRoleUser(String strRole,String UnitId, String formfields) {
      boolean strResult = true;
      int i = 0;
      ArrayList arrList_User_No = new ArrayList();
      String  strSQL = "";
      String strRoleID = strRole;
        //保存角色对应用户
        arrList_User_No = GetArrayList(formfields, ",");

        if(formfields.trim().length() == 0){
          strSQL = "DELETE FROM BPIP_USER_ROLE WHERE ROLEID = "+
              strRoleID+" and UNITID Like '"+UnitId+"%' ";
          return dbengine.ExecuteSQL(strSQL);
             }
          else{
             String strl = formfields.substring(0, formfields.length() - 1);
             Role_User_Del1(strRoleID, strl,UnitId);
            }

         for (i = 0; i < arrList_User_No.size(); i++) {
          //检查待插入的记录是否存在
          strSQL = "Select count(*) as RCount From BPIP_USER_ROLE Where ROLEID="+
              strRoleID+" and USERID='"+ arrList_User_No.get(i).toString().trim()+"'";
          dbSet dbset1 = dbengine.QuerySQL(strSQL);
          if (dbset1 != null) {
            if (dbset1.Row(0).Column("RCount").getInteger() == 0) {
              strSQL = "Insert into BPIP_USER_ROLE(USERID,ROLEID,UNITID) values ('"+
                  arrList_User_No.get(i).toString().trim()+"',"+strRoleID+
                  ",'"+GetUserUnitId(arrList_User_No.get(i).toString().trim())+"')";
              dbengine.ExecuteSQL(strSQL);
            }
            dbset1 = null;
          }
        }
      return strResult;
  }
  /**
   * 功能或作用：删除角色及相关的信息
   * @param strRoleID角色ID
   * @return returnValue 返回是否删除成功
   */
  public boolean Role_Del(String strRoleID) {
      boolean isreturn = false;
      String[] strSQLs = new String[3];
      strSQLs[0]="DELETE FROM BPIP_USER_ROLE WHERE ROLEID = "+strRoleID;
      strSQLs[1]="DELETE FROM BPIP_ROLE_RERMISSISSON WHERE ROLEID = "+strRoleID;
      strSQLs[2]="DELETE FROM BPIP_ROLE WHERE ROLEID = "+strRoleID;
      if (dbengine.ExecuteSQLs(strSQLs)) {
        isreturn = true;
      }
      return isreturn;
  }


  /**
  * 功能或作用：删除指定表下指定ID的记录
  * @param strTable 表名
  * @param strID ID值
  * @param strType 类型(1字符 2数字)
  * @return returnValue 返回是否删除成功
  */
  public boolean DelTableData(String strTable,String strID,String strType){
      String strSQL = "";
      String strPRIMARYKEY = "ID";
      boolean isreturn = false;
      //根据表名得到表的关键字段的名称
      strSQL = "Select PRIMARYKEY from BPIP_TABLE where TABLENAME = '"+strTable+"'";
      dbSet dbset = dbengine.QuerySQL(strSQL);
      if (dbset != null) {
        strPRIMARYKEY = dbset.Row(0).Column("PRIMARYKEY").getString();
        dbset = null;
      }
      if (strType.equals("1"))
       {
         strSQL = "DELETE FROM "+strTable+" WHERE "+strPRIMARYKEY+" = '"+strID+"'";}
      else
       {
         strSQL = "DELETE FROM "+strTable+" WHERE "+strPRIMARYKEY+" = "+strID;
       }
      isreturn = dbengine.ExecuteSQL(strSQL);
      return isreturn;
}
/**
  * 功能或作用：根据用户编号得到用户的单位编号
  * @param strUser_No用户编号
  * @return returnValue 返回单位编号
*/
 private String GetUserUnitId(String strUser_No) {
   String strSQL = "";
   String strResult = "";
   strSQL = "Select UNITID from BPIP_USER where USERID = '"+strUser_No+"'";
   dbSet dbset = dbengine.QuerySQL(strSQL);
   if (dbset != null) {
     strResult = dbset.Row(0).Column("UNITID").getString();
     dbset = null;
   }
   return strResult;
 }

/**
     * 功能或作用：保存用户角色管理
     * @param StrRoelListStr 角色ID字符串
     * @param StrUserListStr 用户ID字符串
     * @return returnValue 返回值
*/
public boolean UserRoleManage(String StrRoelListStr,String StrUserListStr) {
      boolean strResult = false;
      int i = 0;
      String strSQL = "";
      ArrayList arrList_RoelList = new ArrayList();
      ArrayList arrList_UserList = new ArrayList();
      StrRoelListStr += ",";
      StrUserListStr += ",";
      try
       {
         arrList_RoelList = GetArrayList(StrRoelListStr, ",");
         arrList_UserList = GetArrayList(StrUserListStr, ",");
       }catch(Exception ex)
       {
         log.WriteLog(ex.getMessage());
       }
       if(arrList_UserList.size()>0){
         for (i = 0; i < arrList_UserList.size(); i++) {
           //首先删除该用户已经分配的所有角色
           strSQL = "Delete from BPIP_USER_ROLE Where USERID = '"+
               arrList_UserList.get(i).toString().trim()+"'";
               dbengine.ExecuteSQL(strSQL);

          if(!StrRoelListStr.equals("0")){
            if (arrList_RoelList.size() > 0) {
              for (int k = 0; k < arrList_RoelList.size(); k++) {
                //保存用户对应的角色
                strSQL = "Insert into BPIP_USER_ROLE(USERID,ROLEID,UNITID) values ('"+
                    arrList_UserList.get(i).toString().trim()+"',"+
                    arrList_RoelList.get(k).toString().trim()+
                    ",'"+
                    GetUserUnitId(arrList_UserList.get(i).toString().trim())+
                    "')";
                strResult = dbengine.ExecuteSQL(strSQL);

               }
              }
             }
             strResult = true;
           }
         }
       return strResult;
    }
/**
      * 功能或作用：根据用户ID得到属于该用户的角色列表
      * @param strRoleID角色ID
      * @return returnValue 返回记录集
*/
public BPIP_USER_ROLE[] GetUserRoleList(String strUserID) {
       BPIP_USER_ROLE[] returnValue = null;
       String  strSQL = "";
       strSQL = "select * from BPIP_USER_ROLE where USERID = '"+strUserID+"' order by ROLEID";
       dbSet dbset = dbengine.QuerySQL(strSQL);
       if (dbset != null) {
         if (dbset.RowCount() > 0) {
           returnValue = new BPIP_USER_ROLE[dbset.RowCount()];
           for (int i = 0; i < dbset.RowCount(); i++) {
             returnValue[i] = new BPIP_USER_ROLE();
             returnValue[i].fullData(dbset.Row(i));
           }
         }
         dbset = null;
       }
       return returnValue;
  }
  /**
    * 功能或作用：根据角色编号得到角色名称
    * @param strUser_No用户编号
    * @return returnValue 返回用户名称
    */
   public String GetRoleTrueName(String strRole_No) {
     String strResult = "";
     String strSQL = "";
     strSQL = "Select ROLENAME from BPIP_ROLE where ROLEID = '"+strRole_No+"'";
     dbSet dbset = dbengine.QuerySQL(strSQL);
     if (dbset != null) {
       strResult = dbset.Row(0).Column("ROLENAME").getString();
       dbset = null;
     }
     return strResult;
  }
   /**
    * 功能或作用：获取最大的角色编号加1
    * @Return int 执行后返回一个最大的角色编号加1
    */
   public int GetRoleId() {
     int returnValue = 0;
     String strSQL;
     strSQL = "Select MAX(ROLEID) AS MaxNo from  BPIP_ROLE";
     dbSet dbset = dbengine.QuerySQL(strSQL);
     if (dbset != null && dbset.RowCount() > 0) {
       returnValue = dbset.Row(0).Column("MaxNo").getInteger()+1;
       dbset = null;
     }
     return returnValue;
   }

}
