staffbean.java

WAP PUSH后台源码,WAP PUSH后台源码

package com.sxit.wap.staff;
import java.sql.*;
import java.util.*;
import com.sxit.wap.common.*;
import com.sxit.wap.exception.*;

public class StaffBean extends StaffDao{
  public static Collection queryByDepartmentId(int departmentId) throws SysException {
    return queryBySql("SELECT * FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " ORDER BY ORDER_ID");
  }
  public static int getRowCountByDepartmentId(int departmentId) throws SysException {
    return getRowCountBySql("SELECT COUNT(*) FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId);
  }
  public static int getRowCountByGroupIdAndUserMdn(int groupId, String userMdn) throws SysException {
    String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userMdn)?" AND USER_MDN LIKE '%" + userMdn + "%'":"" + " ORDER BY USER_NAME");
    return getRowCountBySql(sql);
  }
  public static int getRowCountByGroupIdAndUserName(int groupId, String userName) throws SysException {
    String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userName)?" AND USER_NAME LIKE '%" + userName + "%'":"" + " ORDER BY USER_NAME");
    System.out.println(sql);
    return getRowCountBySql(sql);
  }
  public static Collection queryByDepartmentId(int departmentId, int beginRow, int endRow) throws SysException {
    return queryBySql("SELECT * FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " ORDER BY ORDER_ID", beginRow, endRow);
  }
  public static Collection queryByOrderDepartmentId(int departmentId) throws SysException {
    String sql = "SELECT * FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " ORDER BY ORDER_ID";
    return queryBySql(sql);
  }
  public static Collection queryByGroupIdAndUserMdn(int groupId, String userMdn) throws SysException {
    String sql = "SELECT * FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userMdn)?" AND USER_MDN LIKE '%" + userMdn + "%'":"" + " ORDER BY USER_NAME");
    return queryBySql(sql);
  }
  public static Collection queryByGroupIdAndUserName(int groupId, String userName) throws SysException {
    //userName = changeencode("7B548C22");
    String sql = "SELECT * FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userName)?" AND USER_NAME LIKE '%" + userName + "%'":"" + " ORDER BY USER_NAME");
    System.out.println(sql);
    return queryBySql(sql);
  }
  public static Collection queryByOrderDepartmentId(int departmentId, int beginRow, int endRow) throws SysException {
    String sql = "SELECT * FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " ORDER BY ORDER_ID";
    return queryBySql(sql, beginRow, endRow);
  }
  public static Collection queryByGroupIdAndUserMdn(int groupId, String userMdn, int beginRow, int endRow) throws SysException {
    String sql = "SELECT * FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userMdn)?" AND USER_MDN LIKE '%" + userMdn + "%'":"" + " ORDER BY USER_NAME");
    return queryBySql(sql, beginRow, endRow);
  }
  public static Collection queryByGroupIdAndUserName(int groupId, String userName, int beginRow, int endRow) throws SysException {
    String sql = "SELECT * FROM " + tableName + " WHERE GROUP_ID = " + groupId +
                 (StringUtil.isNotEmpty(userName)?" AND USER_NAME LIKE '%" + userName + "%'":"" + " ORDER BY USER_NAME");
    System.out.println(sql);
    return queryBySql(sql, beginRow, endRow);
  }
  public static String changeencode(String sinput){
    if(sinput!=null){
      try{
        byte[]btemp = new byte[sinput.length()/2];
        int j = 0 ;
        for(int i=0;i<btemp.length;i++){
          btemp[i]=(byte)Integer.parseInt(sinput.substring(j,j+2),16);
          j = j + 2;
        }
        return new String(btemp,"UTF-16BE");
      }catch(Exception e){
        System.out.println(e);
        return "";
      }
    }else{
      return "";
    }
  }
  public static int getMaxOrderId(int departmentId) throws SysException {
    String sql = "SELECT MAX(ORDER_ID) MAX_ORDER_ID FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId;
    Collection coll = queryBySql(sql);
    if (coll.size() == 0) return 1;
    else {
      Iterator it = coll.iterator();
      it.hasNext();
      Hashtable element = (Hashtable)it.next();
      return Integer.parseInt((String)element.get("maxOrderId")) + 1;
    }
  }
    public static int getRowCountByDepartmentId(String name,int departmentId) throws SysException {
    String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " AND (USER_MDN LIKE '" + name + "%' OR USER_NAME LIKE '" + name + "%')";
    return getRowCountBySql(sql);
  }
    public static Collection queryByDepartmentId(String name,int departmentId, int beginRow, int endRow) throws SysException {
     String sql = "SELECT * FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " AND (USER_MDN LIKE '" + name + "%' OR USER_NAME LIKE '" + name + "%') " + " ORDER BY ORDER_ID";
     return queryBySql(sql, beginRow, endRow);

  }
  public static Collection queryByDepartmentId(String name,int departmentId) throws SysException {
    String sql = "SELECT USER_MDN,USER_NAME,SEX,TEL,FAX,JOB,ADDRESS,POST,REMARK FROM " + tableName + " WHERE DEPARTMENT_ID = " + departmentId + " AND (USER_MDN LIKE '" + name + "%' OR USER_NAME LIKE '" + name + "%') " + " ORDER BY ORDER_ID";
    return queryBySql(sql);
 }

  public static String getEmailByGroupId(String userMdn,int groupId)throws SysException{
    String sql = "SELECT EMAIL FROM " + tableName + " WHERE USER_MDN = '" + userMdn + "' AND GROUP_ID = " + groupId;
    System.out.println(sql);
    Collection coll = queryBySql(sql);
    if (coll.size() == 0) return "";
    else {
          Iterator it = coll.iterator();
          it.hasNext();
          Hashtable element = (Hashtable)it.next();
          return (String)element.get("email");
        }
  }

  public static boolean setUesrEmail(String userMdn,String email,int groupId)throws SysException{
     String sql = "UPDATE " + tableName + " SET EMAIL = '" +email + "' WHERE USER_MDN = '" + userMdn + "' AND GROUP_ID = " + groupId;
     return 0 < updateBySql(sql);
  }
 public static Collection getGroupByUserMdn(String userMdn)throws SysException{
     String sql = "SELECT * FROM WAP_STAFF S,WAP_GROUP G,WAP_DEPARTMENT D WHERE USER_MDN = '" + userMdn + "' AND G.ID = S.GROUP_ID AND D.ID = S.DEPARTMENT_ID";
    return queryBySql(sql);
 }
 public static int getUserCountByDepartmentId(int groupId,int departmentId,String name)throws SysException{
   String sql = "SELECT COUNT(*) FROM WAP_STAFF S,WAP_DEPARTMENT D WHERE  (S.USER_MDN LIKE '" + name + "%' OR S.USER_NAME LIKE '" + name + "%' ) AND D.ID =S.DEPARTMENT_ID AND S.DEPARTMENT_ID IN (SELECT  ID FROM WAP_DEPARTMENT START WITH (ID = " + departmentId + " AND GROUP_ID = " + groupId + ") CONNECT BY PARENT_ID = PRIOR ID)";
   return getRowCountBySql(sql);
 }
 public static Collection getUserByDepartmentId(String groupId,int departmentId,String name) throws SysException {
  String sql = "SELECT * FROM WAP_STAFF S,WAP_DEPARTMENT D WHERE  (S.USER_MDN LIKE '" + name + "%' OR S.USER_NAME LIKE '" + name + "%' ) AND D.ID =S.DEPARTMENT_ID AND S.DEPARTMENT_ID IN (SELECT  ID FROM WAP_DEPARTMENT START WITH (ID = " + departmentId + " AND GROUP_ID = " + groupId + ") CONNECT BY PARENT_ID = PRIOR ID)";
  return queryBySql(sql);
}
 public static Collection getUserByDepartmentId(String groupId,int departmentId,String name, int beginRow, int endRow) throws SysException {
   String sql = "SELECT * FROM WAP_STAFF S,WAP_DEPARTMENT D WHERE  (S.USER_MDN LIKE '" + name + "%' OR S.USER_NAME LIKE '" + name + "%' ) AND D.ID =S.DEPARTMENT_ID AND S.DEPARTMENT_ID IN (SELECT  ID FROM WAP_DEPARTMENT START WITH (ID = " + departmentId + " AND GROUP_ID = " + groupId + ") CONNECT BY PARENT_ID = PRIOR ID)";
   return queryBySql(sql, beginRow, endRow);
}
 public static Collection getUsreGroupByUserMdn(String userMdn)throws SysException{
  String sql = "SELECT F.*,G.GROUP_NAME FROM WAP_STAFF F,WAP_GROUP G WHERE F.USER_MDN = '" + userMdn + "' AND F.GROUP_ID = G.ID ORDER BY F.GROUP_ID";
  return queryBySql(sql);
 }
 public static boolean updateStaff(String userMdn,String groupId,String userTel,String userJob,String userFex,String address,String email)throws SysException{
   String sql = "UPDATE WAP_STAFF SET TEL = '" +  userTel + "',JOB = '" + userJob + "',FAX = '" +  userFex + "',ADDRESS = '" + address + "',EMAIL = '" + email + "' WHERE USER_MDN = '" + userMdn + "' AND GROUP_ID = '" + groupId + "'";
   System.out.println(sql);
   return 0 < updateBySql(sql);
 }
 public static boolean updatePicture(String userMdn,String groupId,String picture)throws SysException{
  String sql = "UPDATE WAP_STAFF SET PICTURE = '" + picture + "' WHERE USER_MDN = '" + userMdn + "'";
  System.out.println(sql);
  return 0 < updateBySql(sql);
 }
 public static Hashtable getUserByDepartmentId(int departmentId)throws SysException{
  Hashtable ht = new Hashtable();
  String sql = "SELECT USER_MDN FROM WAP_STAFF WHERE DEPARTMENT_ID = " + departmentId;
  Collection coll = queryBySql(sql);
  Iterator it = coll.iterator();
  while(it.hasNext()){
    Hashtable element = (Hashtable)it.next();
    String userMdn =(String)element.get("userMdn");
    ht.put(userMdn,"1");
  }
  return ht;
}
public static boolean isExist(String userMdn)throws SysException{
  String sql = "SELECT COUNT(*) FROM WAP_STAFF WHERE USER_MDN = '" +userMdn + "'";
  return getRowCountBySql(sql) > 0;
}
  public static void main(String args[]) throws SysException {
    System.out.println(StaffBean.getUserCountByDepartmentId(333,1,""));
  }
}