GZ/nwyj/.svn/pristine/62/622d47cceceb441fd73797e689487b0cff435fa3.svn-base

package com.sinosoft.lz.unitsWH.unitsWHDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.sinosoft.em.alert.response.service.ResponseService;

public class unitsWHDAO {
	
	//日志对象
	private static final Logger log = Logger.getLogger(ResponseService.class);
	
	public String getAllUnitShow1(String corpId,String butVal){//查询本单位------根结点(初次加载)
		//dept_id='corp_id'
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' and CODES_ISSHOW like '%'|| (select fd_code from bm_mapvaluecanst where fd_description='"+butVal+"') ||'%' order by SORT";
		String fdcode = getFdcodeByFddesc(butVal);
		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' and CODES_ISSHOW like '%"+fdcode+"%' order by SORT";
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' order by SORT";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
//			pstm.setString(1, butVal);
			rs = pstm.executeQuery();
			while(rs.next()){
				String dept_id = rs.getString("DEPT_ID") == null ? "" : rs.getString("DEPT_ID");//单位ID
				String dept_name = rs.getString("DEPT_NAME") == null ? "" : rs.getString("DEPT_NAME");//单位NAME
				String parent_id = rs.getString("PARENT_ID") == null ? "" : rs.getString("PARENT_ID");//上级单位ID
				String dept_type = rs.getString("DEPT_TYPE") == null ? "" : rs.getString("DEPT_TYPE");//单位类型（单位--=1，部门--!=1）
				//根结点
				sb.append("{\"id\":\""+dept_id+"\",\"name\":\""+dept_name+"\",\"parentId\":\"0\",\"open\":\"true\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
				//下级部门
//				sb.append("{\"id\":\""+dept_id+";1"+"\",\"name\":\"本部\",\"parentId\":\""+dept_id+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
				sb.append(getAllUnitShow3(dept_id,butVal));
				//下级单位
				sb.append(getAllUnitShow2(dept_id,butVal));
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
//		System.out.println("111111111111111111111111"+sb.toString());
		return sb.toString();
	}
	
	public String getAllUnitShow5(String corpId,String butVal){//查询本单位------根结点(初次加载)
		//dept_id='corp_id'
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' and CODES_ISSHOW like '%'|| (select fd_code from bm_mapvaluecanst where fd_description='"+butVal+"') ||'%' order by SORT";
//		String fdcode = getFdcodeByFddesc(butVal);
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' and CODES_ISSHOW like '%"+fdcode+"%' order by SORT";
		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where DEPT_ID='"+corpId+"' order by SORT";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
//			pstm.setString(1, butVal);
			rs = pstm.executeQuery();
			while(rs.next()){
				String dept_id = rs.getString("DEPT_ID") == null ? "" : rs.getString("DEPT_ID");//单位ID
				String dept_name = rs.getString("DEPT_NAME") == null ? "" : rs.getString("DEPT_NAME");//单位NAME
				String parent_id = rs.getString("PARENT_ID") == null ? "" : rs.getString("PARENT_ID");//上级单位ID
				String dept_type = rs.getString("DEPT_TYPE") == null ? "" : rs.getString("DEPT_TYPE");//单位类型（单位--=1，部门--!=1）
				//根结点
				sb.append("{\"id\":\""+dept_id+"\",\"name\":\""+dept_name+"\",\"parentId\":\"0\",\"open\":\"true\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
				//下级部门
//				sb.append("{\"id\":\""+dept_id+";1"+"\",\"name\":\"本部\",\"parentId\":\""+dept_id+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
				sb.append(getAllUnitShow3(dept_id,butVal));
				//下级单位
				sb.append(getAllUnitShow2(dept_id,butVal));
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
//		System.out.println("111111111111111111111111"+sb.toString());
		return sb.toString();
	}
	
	public String getAllUnitShow4(String corpId,String butVal){//查询本单位------根结点(非初次)
		StringBuffer sb = new StringBuffer();
		//下级部门
//		int n = corpId.split(";").length;
//		System.out.println("***/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**8888888888888888888888888888----"+n+"--:"+corpId);
//		if(n<2){
//			sb.append("{\"id\":\""+corpId+";1"+"\",\"name\":\"本部\",\"parentId\":\""+corpId+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
//		}
		sb.append(getAllUnitShow3(corpId,butVal));
		
		//下级单位
		sb.append(getAllUnitShow2(corpId,butVal));
		
		if(sb.lastIndexOf(",")>-1){
			sb.deleteCharAt(sb.lastIndexOf(","));
		}
		return sb.toString();
	}
	
	public String getAllUnitShow2(String deptId,String butVal){//查询下级所有单位------(非部门)
		String fdcode = getFdcodeByFddesc(butVal);
		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where PARENT_ID='"+deptId+"' and DEPT_TYPE='1' and CODES_ISSHOW like '%"+fdcode+"%' order by SORT";
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where PARENT_ID='"+deptId+"' and DEPT_TYPE='1' order by SORT";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
//			pstm.setString(1, butVal);
			rs = pstm.executeQuery();
			while(rs.next()){
				String dept_id = rs.getString("DEPT_ID") == null ? "" : rs.getString("DEPT_ID");//单位ID
				String dept_name = rs.getString("DEPT_NAME") == null ? "" : rs.getString("DEPT_NAME");//单位NAME
				String parent_id = rs.getString("PARENT_ID") == null ? "" : rs.getString("PARENT_ID");//上级单位ID
				String dept_type = rs.getString("DEPT_TYPE") == null ? "" : rs.getString("DEPT_TYPE");//单位类型（单位--=1，部门--!=1）
				sb.append("{\"id\":\""+dept_id+"\",\"name\":\""+dept_name+"\",\"parentId\":\""+deptId+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
			}
//			if(sb.lastIndexOf(",")>-1){
//				sb.deleteCharAt(sb.lastIndexOf(","));
//			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
//		System.out.println("22222222222222222222222222"+sb.toString());
		return sb.toString();
	}
	
	public String getAllUnitShow3(String deptId,String butVal){//查询下级所有部门------(部门)
		String fdcode = getFdcodeByFddesc(butVal);
		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where PARENT_ID='"+deptId+"' and DEPT_TYPE!='1' and CODES_ISSHOW like '%"+fdcode+"%' order by SORT";
//		String sql="select DEPT_ID,DEPT_NAME,PARENT_ID,DEPT_TYPE from SYS_DEPARTMENT where PARENT_ID='"+deptId+"' and DEPT_TYPE!='1' order by SORT";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int a=0;
//		sb.append("{\"id\":\"benbu\",\"name\":\"本部\",\"parentId\":\""+deptId+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
//			pstm.setString(1, butVal);
			rs = pstm.executeQuery();
			
			while(rs.next()){
				if(a==0){
					sb.append("{\"id\":\""+deptId+";1"+"\",\"name\":\"本部\",\"parentId\":\""+deptId+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
					a++;
				}
				
				String dept_id = rs.getString("DEPT_ID") == null ? "" : rs.getString("DEPT_ID");//单位ID
				String dept_name = rs.getString("DEPT_NAME") == null ? "" : rs.getString("DEPT_NAME");//单位NAME
				String parent_id = rs.getString("PARENT_ID") == null ? "" : rs.getString("PARENT_ID");//上级单位ID
				String dept_type = rs.getString("DEPT_TYPE") == null ? "" : rs.getString("DEPT_TYPE");//单位类型（单位--=1，部门--!=1）
				sb.append("{\"id\":\""+dept_id+"\",\"name\":\""+dept_name+"\",\"parentId\":\""+deptId+";1"+"\",\"open\":\"false\",\"isParent\":\"true\",\"icon\":\"/nwyj/scripts/qui/libs/icons/home.png\",\"orgLevel\":\"null\"},");
			}
//			if(sb.lastIndexOf(",")>-1){
//				sb.deleteCharAt(sb.lastIndexOf(","));
//			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
//		System.out.println("333333333333333333333"+sb.toString());
		return sb.toString();
	}
	
	public String getFdcodeByFddesc(String desc){
		String sql = "select fd_code from bm_mapvaluecanst where fd_description=? and fd_type='BM_SHOW_UNITES'";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, desc);
			rs = pstm.executeQuery();
			while(rs.next()){
				String fdCode = rs.getString("fd_code") == null ? "" : rs.getString("fd_code");
				sb.append(fdCode);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return sb.toString();
	}
	
	public String getAllShowUnitCode(){//获取常量表中所有fd_type='BM_SHOW_UNITES'的fd_code和fd_description
		String sql = "select fd_code,fd_description from bm_mapvaluecanst where fd_type='BM_SHOW_UNITES'";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			sb.append("\"allrows\":[");
			while(rs.next()){
				String fdCode = rs.getString("fd_code") == null ? "" : rs.getString("fd_code");
				String fdDesc = rs.getString("fd_description") == null ? "" : rs.getString("fd_description");
				sb.append("{\"name\":\""+fdDesc+"\",\"id\":\""+fdCode+"\"},");
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			sb.append("],");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	
	public String getCodeAndDesc(String code){//根据fd_code值从常量表中查询fd_description
		String sql = "select fd_code,fd_description from bm_mapvaluecanst where fd_code=?";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, code);
			rs = pstm.executeQuery();
//			sb.append("\"forShow\":[");
			while(rs.next()){
				String fdCode = rs.getString("fd_code") == null ? "" : rs.getString("fd_code");
				String fdDesc = rs.getString("fd_description") == null ? "" : rs.getString("fd_description");
				sb.append("{\"name\":\""+fdDesc+"\",\"id\":\""+fdCode+"\"},");
			}
//			if(sb.lastIndexOf(",")>-1){
//				sb.deleteCharAt(sb.lastIndexOf(","));
//			}
//			sb.append("]");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	public int updateCodes(String deptId,String codes){//从组织机构表SYS_DEPARTMENT中查询对应部门下的CODES_ISSHOW字段值
		String sql = "update SYS_DEPARTMENT set CODES_ISSHOW=? where DEPT_ID=?";
		
		StringBuffer sb = new StringBuffer();
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		int n = 0;
		
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codes);
			pstm.setString(2, deptId);
			n = pstm.executeUpdate();
			
//			while(rs.next()){
//				String codesIsShow = rs.getString("CODES_ISSHOW") == null ? "" : rs.getString("CODES_ISSHOW");
//				sb.append(codesIsShow);
//			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
//				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}
	
	public String getcodes(String deptId){//从组织机构表SYS_DEPARTMENT中查询对应部门下的CODES_ISSHOW字段值
		String sql = "select CODES_ISSHOW from SYS_DEPARTMENT where DEPT_ID=?";
		
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, deptId);
			rs = pstm.executeQuery();
			while(rs.next()){
				String codesIsShow = rs.getString("CODES_ISSHOW") == null ? "" : rs.getString("CODES_ISSHOW");
				sb.append(codesIsShow);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	public int getUnitTypeNum(){//获取常量表中fd_type='BM_SHOW_UNITES'记录的条数
		String sql = "select count(1) from bm_mapvaluecanst where fd_type='BM_SHOW_UNITES'";
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = 0;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				n = rs.getInt("count(1)");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return n;
	}
	
	public int insertUnitTypes(String fdCode,String description,String fdType,int sortNum){//往常量表中新增记录
		String sql = "insert into bm_mapvaluecanst (FD_CODE,FD_DESCRIPTION,FD_TYPE,FD_SORT) values (?,?,?,?)";
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = 0;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, fdCode);
			pstm.setString(2, description);
			pstm.setString(3, "BM_SHOW_UNITES");
			pstm.setInt(4, sortNum);
			n=pstm.executeUpdate();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return n;
	}
	
}