customquery.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.sysmgr.uurm;

import com.doone.data.DacClient;
import com.doone.data.DataRow;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;
import com.doone.uurm.StateEnum;

public class CustomQuery {
	/**
	 * 组织管理页面查询组织信息列表。
	 * 
	 * @param db
	 *            数据访问连接。
	 * @param upOrgId
	 *            父组织编号。
	 * @return
	 */
	public static DataTable getOrganiseList(DacClient db, long upOrgId) {
		DataTable ret = null;
		try {
			StringBuffer sql = new StringBuffer("select o.*, s.staffNum from tf_organise o,");
            sql.append(" (select sao.organiseid, count(1) staffNum");
            sql.append(" from tf_staffandorg sao");
            sql.append(" where (sao.starttime < sysdate or sao.starttime is null) and");
            sql.append(" (sao.endtime > sysdate or sao.endtime is null) and sao.state = 'E' and exists (");
            sql.append(" select 1 from tf_staff s");
            sql.append(" where s.staffid = sao.staffid");
            sql.append(" and s.state='E'");
            sql.append(" ) group by sao.organiseid ) s");
            sql.append(" where o.organiseid = s.organiseid(+) and o.uporganiseid = ? ");
            sql.append(" order by o.state desc, o.organisemode, o.name");

			Object[] aParam = new Object[1];
			aParam[0] = new Long(upOrgId);

			if (db == null)
				db = new DacClient();
			ret = db.executeQuery(sql.toString(), aParam);
		} catch (Exception ex) {
			FileLogger.getLogger().warn("OrganiseList Error-->", ex);
		}
		return ret;
	}

	public static boolean getOrgEnableDelete(DacClient db, long orgId) {
		boolean ret = true;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select a1.cnt + a2.cnt cnt");
			sql.append(" from (select count(1) cnt");
			sql.append(" from tf_organise o");
			sql.append(" where (o.starttime < sysdate or o.starttime is null)");
			sql.append(" and (o.endtime > sysdate or o.endtime is null)");
			sql.append(" and o.state = ? and o.uporganiseid = ?) a1,");
			sql.append(" (select count(1) cnt");
			sql.append(" from tf_staffandorg s");
			sql.append(" where (s.starttime < sysdate or s.starttime is null) and");
			sql.append(" (s.endtime > sysdate or s.endtime is null) and s.state = ? and");
			sql.append(" s.organiseid = ?) a2");

			Object[] aParam = new Object[4];
			aParam[0] = StateEnum.ENABLED;
			aParam[1] = new Long(orgId);
			aParam[2] = StateEnum.ENABLED;
			aParam[3] = new Long(orgId);

			if (db == null)
				db = new DacClient();
			String result = db.getStringFromSqlQuery(sql.toString(), aParam);
			FileLogger.getLogger().info("组织编号：" + Long.toString(orgId) + "下有" + result + "个子组织或员工");
			if ( Integer.parseInt(result) > 0 ) {
				ret = false;
			}
		} catch (Exception ex) {
			FileLogger.getLogger().warn("OrganiseList Error-->", ex);
		}
		return ret;
	}
    
    public static DataTable getOrgPurvList(DacClient db, long purvId, int startIdx, int count) {
        DataTable ret = null;
        try {
            if ( startIdx < 0 ) startIdx = 0;
            if ( count < 0 ) count = 1000;

            StringBuffer sql = new StringBuffer();
            sql.append("select PURVANDORGID,");
            sql.append("ORGCODE,");
            sql.append("ORGFULLNAME,");
            sql.append("STARTTIME,");
            sql.append("ENDTIME,");
            sql.append("PURVTYPE,");
            sql.append("POSTATE,");
            sql.append("ORGSTATE,");
            sql.append("PURVIEWID,");
            sql.append("ROOTID,");
            sql.append("STATEMODIFYTIME");
            sql.append(" from (select t.PURVANDORGID,");
            sql.append("t.ORGCODE,");
            sql.append("t.ORGFULLNAME,");
            sql.append("t.STARTTIME,");
            sql.append("t.ENDTIME,");
            sql.append("t.PURVTYPE,");
            sql.append("t.POSTATE,");
            sql.append("t.ORGSTATE,");
            sql.append("t.STATEMODIFYTIME,");
            sql.append("t.PURVIEWID,");
            sql.append("t.ROOTID,");
            sql.append(" rownum num /*+ firstrow */");
            sql.append(" from vf_orgpurv t");
            sql.append(" where t.PURVIEWID = ? ");
            sql.append(" and rownum <= ? )");
            sql.append(" where num > ?");
            
            Object[] aParam = new Object[3];
            aParam[0] = new Long(purvId);
            aParam[1] = new Integer(startIdx + count);
            aParam[2] = new Integer(startIdx);

            if ( db == null ) db = new DacClient();
            DataTable dt = db.executeQuery(sql.toString(), aParam);
            // 权限合并，如果存在权限编号与组织编号相同，同时又是继承的权限时将去掉继承的权限。
            ret = dt.copyStruct();
            for ( int i=0; i<dt.getRows().getCount(); i++) {
                DataRow dr = dt.getRow(i);
                if ( dr.getLong("ROOTID") == dr.getLong("PURVIEWID") ) {
                    DataRow dr1 = ret.newRow();
                    for ( int j=0; j<ret.getColumns().getCount(); j++) {
                        dr1.setValue(j, dr.getValue(j));
                    }
                    ret.getRows().add(dr1);
                }
                else {
                    boolean hasSame = false;
                    for ( int j=i+1; j<dt.getRows().getCount();j++) {
                        DataRow dr1 = dt.getRow(j);
                        if ( dr.getString("ORGCODE").equalsIgnoreCase(dr1.getString("ORGCODE"))) {
                            hasSame = true;
                            break;
                        }
                    }
                    if ( ! hasSame ) {
                        DataRow dr1 = ret.newRow();
                        for ( int j=0; j<ret.getColumns().getCount(); j++) {
                            dr1.setValue(j, dr.getValue(j));
                        }
                        ret.getRows().add(dr1);
                    }
                }
            }
            
            ret.acceptChanges();
        }
        catch(Exception ex) {
            FileLogger.getLogger().warn("OrgPurvList-->",ex);
        }
        
        return ret;
    }
    
    public static int getOrgPurvCount(DacClient db, long purvId) {
    	int ret = 0;
        try {

        	StringBuffer sql = new StringBuffer();
            sql.append("select count(1) cnt");
            sql.append(" from vf_orgpurv t");
            sql.append(" where t.PURVIEWID = ? ");
            
            Object[] aParam = new Object[1];
            aParam[0] = new Long(purvId);
            
            if ( db == null ) db = new DacClient();
            String sCount = db.getStringFromSqlQuery(sql.toString(), aParam);
            
            ret = Integer.parseInt(sCount);
        }
        catch(Exception ex) {
            FileLogger.getLogger().warn("OrgPurvList-->",ex);
        }
        
        return ret;
    }
    
    public static DataTable getOrgListByStaff(DacClient db, long lStaffId, int startIdx, int count  ) {
        DataTable ret = null;
        try {
            if ( startIdx < 0 ) startIdx = 0;
            if ( count < 0 ) count = 1000;

            StringBuffer sql = new StringBuffer();
            sql.append("select ORGANISEID,");
            sql.append("STAFFANDORGID,");
            sql.append("ORGFULLNAME,");
            sql.append("ORGANISENAME,");
            sql.append("SO_STARTTIME,");
            sql.append("SO_ENDTIME,");
            sql.append("SO_STATE,");
            sql.append("SO_STATEMODIFYTIME,");
            sql.append("O_STARTTIME,");
            sql.append("O_ENDTIME,");
            sql.append("O_STATE,");
            sql.append("O_STATEMODIFYTIME,");
            sql.append("S_STATE,");
            sql.append("S_STATEMODIFYTIME");
            sql.append(" from (select t.ORGANISEID,");
            sql.append("t.STAFFANDORGID,");
            sql.append("substr(t.ORGFULLNAME, 4) ORGFULLNAME,");
            sql.append("t.ORGANISENAME,");
            sql.append("t.SO_STARTTIME,");
            sql.append("t.SO_ENDTIME,");
            sql.append("t.SO_STATE,");
            sql.append("t.SO_STATEMODIFYTIME,");
            sql.append("t.O_STARTTIME,");
            sql.append("t.O_ENDTIME,");
            sql.append("t.O_STATE,");
            sql.append("t.O_STATEMODIFYTIME,");
            sql.append("t.S_STATE,");
            sql.append("t.S_STATEMODIFYTIME,");
            sql.append("rownum num /*+ firstrow */");
            sql.append(" from vf_stafforg t");
            sql.append(" where t.staffid = ?");
            sql.append(" and rownum <= ?)");
            sql.append(" where num > ?");

            Object[] aParam = new Object[3];
            aParam[0] = new Long(lStaffId);
            aParam[1] = new Integer(startIdx + count);
            aParam[2] = new Integer(startIdx);
            
            if ( db == null ) db = new DacClient();
            ret = db.executeQuery(sql.toString(), aParam);
        }
        catch(Exception ex) {
            FileLogger.getLogger().warn("StaffOrgList-->",ex);
        }

        return ret;
    }
}