wp/nwyj/.svn/pristine/99/99cbdf1ad8df05b2609f310b5f3495e459af715d.svn-base

package com.sinosoft.lz.system.menu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.sinosoft.lz.system.menu.vo.Module;

public class SysModuleDao {
	private final static Logger	log	= Logger.getLogger(SysModuleDao.class);

	public List<Module> getModuleByUserId(String user_id) {
		StringBuffer sql = new StringBuffer();
		//sql.append(" select FD_OBJECTID,USER_ID,UL_ID,UPDATEDATE,IS_DEL,LI_ID,MODULE_FUN,SORT,TITLE_NAME from SYS_INDEX_MODULE where IS_DEL = '0' ");
		sql.append(" select distinct m.*,a.sort from SYS_INDEX_MODULE a,SYS_MODULE m where a.LI_ID = m.SYS_LI_ID and a.is_del = '0' ");
    	sql.append(" and a.USER_ID = ? ");
    	//sql.append("'"+user_id+"'");
	    sql.append(" ORDER BY a.SORT ASC ");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Module> menuList = new ArrayList<Module>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1,user_id);
			rs = pstm.executeQuery();
			while(rs.next()){
				Module module = new Module();
				module.setFd_objectid(rs.getString("FD_OBJECTID"));
				//module.setUser_id(rs.getString("USER_ID"));
				module.setUl_id(rs.getString("SYS_UL_ID"));
				module.setLi_id(rs.getString("SYS_LI_ID"));
				module.setUpdatedate(rs.getString("UPDATEDATE"));
				module.setSort(rs.getString("SORT"));
				module.setModule_fun(rs.getString("SYS_MODULE_FUN"));
				module.setIs_del(rs.getString("IS_DEL"));
				module.setTitle(rs.getString("SYS_TITLE_NAME"));
				module.setModule_url(rs.getString("MODULE_URL"));
				menuList.add(module);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;		
	}



	public String addModuleByUserId(Module module) {
		String result = "error";
		String sql = "INSERT INTO SYS_INDEX_MODULE (FD_OBJECTID,USER_ID,UPDATEDATE,IS_DEL,LI_ID,SORT) VALUES(?, ?, ?, ?, ?, ?)";
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				//设置参数值
				pstm.setString(1, module.getFd_objectid());
				pstm.setString(2, module.getUser_id());
				pstm.setString(3, module.getUpdatedate());
				pstm.setString(4, module.getIs_del());
				pstm.setString(5, module.getLi_id());
				pstm.setString(6, module.getSort());
				
				int rs  = pstm.executeUpdate();
				if(rs==1){
					result = "insert";
				}
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pstm.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					log.error("关闭数据库连接错误:"+e.getMessage());
				}
			}
	return result;
	}

	public int checkAddModule(String user_id, String li_id,String sort,String is_del) {
		int result = 0;
		String sql = "update SYS_INDEX_MODULE set IS_DEL=?,UPDATEDATE=?, SORT=? where USER_ID=? and LI_ID=?";
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				//设置参数值
				Date date = new Date();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				pstm.setString(1, is_del);
				pstm.setString(2, sdf.format(date));
				pstm.setString(3, sort);
				pstm.setString(4, user_id);
				pstm.setString(5, li_id);
				result  = pstm.executeUpdate();
				
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pstm.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					log.error("关闭数据库连接错误:"+e.getMessage());
				}
			}
	return result;
	}



	public List<Module> getAllModule(String user_id, String parent_menu,String corp_id) {
		StringBuffer sql = new StringBuffer();
		//sql.append("select FD_OBJECTID,SYS_TITLE_NAME,UPDATEDATE,IMG_URL,SYS_LI_ID,PARENT_MENU from SYS_MODULE where IS_DEL = '0' ");
		sql.append(" select distinct a.FD_OBJECTID,a.SYS_TITLE_NAME,a.UPDATEDATE,a.IMG_URL,a.SYS_LI_ID,a.PARENT_MENU ,a.MODULE_DEPT_LEVEL ");
		sql.append(" from SYS_MODULE a left join SYS_DEPARTMENT d on d.CORP_ID = ? ");
		sql.append(" where a.IS_DEL = '0' AND ( a.MODULE_DEPT_LEVEL = d.DEPT_LEVEL OR a.MODULE_DEPT_LEVEL IS NULL) ");
    	
		if(parent_menu!=null&&!"".equals(parent_menu)){
    		sql.append(" and a.PARENT_MENU like ? ");
    		//sql.append("'%"+parent_menu+"%'");
    	}
	
	    //sql.append(" ORDER BY SORT DESC ");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Module> menuList = new ArrayList<Module>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1, corp_id);
			if(parent_menu!=null&&!"".equals(parent_menu)){
				pstm.setString(2, "%"+parent_menu+"%");
	    	}
			rs = pstm.executeQuery();
			while(rs.next()){
				Module module = new Module();
				module.setFd_objectid(rs.getString("FD_OBJECTID"));
				module.setLi_id(rs.getString("SYS_LI_ID"));
				module.setUpdatedate(rs.getString("UPDATEDATE"));
				module.setImg_url(rs.getString("IMG_URL"));
				module.setTitle(rs.getString("SYS_TITLE_NAME"));
				module.setParent_menu(rs.getString("PARENT_MENU"));
				menuList.add(module);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;
	}



	public int delectModult(String user_id, String li_id) {
		int result = 0;
		String sql = "update SYS_INDEX_MODULE set IS_DEL='1' where USER_ID=? and LI_ID=?";
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				//设置参数值

				pstm.setString(1, user_id);
				pstm.setString(2, li_id);
				result  = pstm.executeUpdate();
				
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pstm.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					log.error("关闭数据库连接错误:"+e.getMessage());
				}
			}
	return result;
	}

	public List<Module> getSearchModule(String user_id, String moduleName,String corp_id) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select distinct a.FD_OBJECTID,a.SYS_TITLE_NAME,a.UPDATEDATE,a.IMG_URL,a.SYS_LI_ID,a.PARENT_MENU ,a.MODULE_DEPT_LEVEL ");
		sql.append(" from SYS_MODULE a left join SYS_DEPARTMENT d on d.CORP_ID = ? ");
		sql.append(" where a.IS_DEL = '0' AND ( a.MODULE_DEPT_LEVEL = d.DEPT_LEVEL OR a.MODULE_DEPT_LEVEL IS NULL) ");
    	
		if(moduleName!=null&&!"".equals(moduleName)){
    		sql.append(" and a.SYS_TITLE_NAME like ? ");
    		//sql.append("'%"+moduleName+"%'");
    	}
	
	    //sql.append(" ORDER BY SORT DESC ");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Module> menuList = new ArrayList<Module>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1, corp_id);
			if(moduleName!=null&&!"".equals(moduleName)){
				pstm.setString(2, "%"+moduleName+"%");
	    	}
			rs = pstm.executeQuery();
			while(rs.next()){
				Module module = new Module();
				module.setFd_objectid(rs.getString("FD_OBJECTID"));
				module.setLi_id(rs.getString("SYS_LI_ID"));
				module.setUpdatedate(rs.getString("UPDATEDATE"));
				module.setIs_del(rs.getString("IS_DEL"));
				module.setImg_url(rs.getString("IMG_URL"));
				module.setTitle(rs.getString("SYS_TITLE_NAME"));
				module.setParent_menu(rs.getString("PARENT_MENU"));
				menuList.add(module);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;
	}
	
	
	/***************以上是模块的数据操作*********************************/
	
	/***************以下是个别模块里面的内容******************************/
	
	public List<Map<String, String>> queryURLData(String user_id,String com_id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select TOP 0 , 5 FD_OBJECTID,TM_NAME,TM_URL from ECM_CM_THEMATIC  where IS_DEL = '0' and TM_AREAID='0006' and IS_EXTERNAL='0' and TM_USE = '1' and ( SC_DEPT_ID = ? or SC_DEPT_ID = '' or SC_DEPT_ID is null ) ");
    	//sql.append(" and USER_ID = ");
    	//sql.append("'"+user_id+"'");
		sql.append(" ORDER BY UPDATEDATE  DESC ");
		log.info("===集成URL=====sql===="+sql);
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Map<String, String>> menuList = new ArrayList<Map<String, String>>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1, com_id);
			rs = pstm.executeQuery();
			while(rs.next()){
				Map<String, String> map = new HashMap<String, String>();
				map.put("url_name", rs.getString("TM_NAME"));
				map.put("url_url", rs.getString("TM_URL"));
				menuList.add(map);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;
	}

	public List<Map<String, String>> queryGongGaoData(String deptIds,String deptNames) {
		StringBuffer sql = new StringBuffer();
		//sql.append("select TOP 0 , 1 * from SYS_NOTICE where NOTICE_AREA LIKE ? AND NOTICE_AREA_NAME LIKE ? ");
		sql.append("select TOP 0 , 1 * from SYS_NOTICE where NOTICE_AREA LIKE ? ");
    	
	    sql.append(" ORDER BY UPDATEDATE  DESC ");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Map<String, String>> menuList = new ArrayList<Map<String, String>>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1, "%"+deptIds+",%");
			//pstm.setString(2, "%"+deptNames+"%");
			rs = pstm.executeQuery();
			while(rs.next()){
				Map<String, String> map = new HashMap<String, String>();
				map.put("FD_OBJECTID", rs.getString("FD_OBJECTID"));
				map.put("TITLE", rs.getString("TITLE"));
				map.put("SUBTIME", rs.getString("SUBTIME"));
				map.put("CONTENT", rs.getString("CONTENT"));
				map.put("DEPT_NAME", rs.getString("DEPT_NAME"));
				map.put("PERSON", rs.getString("PERSON"));
				menuList.add(map);
			}			
			
		}catch(Exception e){
			log.error("查询首页公告错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;
	}



	public List<Map<String, String>> queryAccessoryData(String mainId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select top 5 fd_objectid, name,filepath,fileext,savename,updatedate,UPLOADTIME from Sys_Comm_FileImg where is_del = '0' and MAINID = '"+mainId+"' order by updatedate desc");
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Map<String, String>> menuList = new ArrayList<Map<String, String>>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while(rs.next()){
				Map<String, String> map = new HashMap<String, String>();
				map.put("fd_id", rs.getString("FD_OBJECTID"));
				map.put("name", rs.getString("NAME"));
				map.put("filepath", rs.getString("FILEPATH"));
				map.put("fileext", rs.getString("FILEEXT"));
				map.put("savename", rs.getString("SAVENAME"));
				map.put("uploadtime", rs.getString("UPLOADTIME"));
				menuList.add(map);
			}			
			
		}catch(Exception e){
			log.error("查询首页附件信息错误"+e.getMessage());
			
		}finally{
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;
	}
}