/***************************************************
 * Copyright 2014-7-23, -sinosoft.com.cn All rights reserved.
 *【创建日期】 : 2014-7-23
 *【创建人】 : 徐杨 
 *【类名】：SysCustomMenuDaoImpl.java
 *【功能】：本类是菜单角色自定义数据层实现类
 *【修改日志】
 * --------------------------------------------------
 * VERSION    DATE    修改人           修改内容
 * --------------------------------------------------
 * 1.0      2014-7-23         
 * --------------------------------------------------
 **************************************************/
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.List;

import org.apache.log4j.Logger;

import com.formaction.Utils;
import com.persistence.DbConnection;
import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.assitant.generator.IdentityGenerator;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.lz.system.menu.vo.IndexMenu;
import com.sinosoft.lz.system.menu.vo.SysMenu;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class SysCustomMenuDaoImpl implements ISysCustomMenuDao{

	private static SysModel			sysmodel	= ModelFactory.getSysmodel();

	private static SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);

	private final static Logger		log			= Logger.getLogger(SysCustomMenuDaoImpl.class);

	/**
	 * 功能： 查询源菜单数据
	 * 
	 * @return List
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List loadMenuInfo() {
/*		String sql = "select code id, parentcode pid, RightName name, "
				+ " sort sort, Handler handler, RightType type,img img,functionlink functionlink "
				+ " from Sys_Menu where RightType='1'  and  is_del='0'   order by  sort asc ";*/
		String sql = "select m.menu_id id, m.parent_menu_id pid, m.menu_name name, "
				+ " m.sortno sort, m.menu_statu type,m.menu_img img,m.menu_url functionlink "
				+ " from Sys_Menu m where  m.is_del='0'   order by  m.menu_id asc ";
		log.info(sql);
		return this.loadInfo(sql);
	}

	/**
	 * 功能： 根据角色查询源菜单数据
	 * 
	 * @param roleid
	 *            角色编号
	 * @return List
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List loadMenuInfoByRoleId(String roleid) {
/*		String sql = " select code id, parentcode pid, RightName name, t1.sort sort, "
				+ " t1.Handler handler, t1.RightType type, roleid, menuid  "
				+ "      from Sys_Menu t1 left join  "
				+ "      (select * from SYS_CUSTOM_MENU where roleid='"
				+ roleid
				+ "' )t2 "
				+ "	  on t1.code = t2.menuid where t1.RightType='1' and t1.is_del='0'   order by t1.sort asc ";*/
		String roleids = "";
		if(roleid != null && !"".equals(roleid)){
			String[] ids = roleid.split(",");
			for(int i=0;i<ids.length;i++){
				roleids += "'"+ids[i]+"',";
			}
			roleids = roleids.substring(0, roleids.lastIndexOf(","));
		}
		String sql = " select m.menu_id id, m.parent_menu_id pid, m.menu_name name, m.sortno sort, "
				+ "  m.menu_statu type, r.role_id, m.menu_id  "
				+ "      from Sys_Menu m, sys_role_right_rel r "
				+ "	  where m.menu_id = r.menu_id and  m.is_del='0' "
				+ "   and r.role_id in ("+roleids+")"
				+ "  order by m.menu_id asc ";

		return this.loadInfo(sql);
	}

	/**
	 * 功能：根据角色查询自定义菜单数据
	 * 
	 * @param roleid
	 *            角色编号
	 * @return List
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List loadCustomMenuInfoByRoleId(String roleid) {
		StringBuffer sql = new StringBuffer();
/*		sql.append("select id, pid, name, roleid, RightName srcname, t1.sort, type, t1.handler,t2.img,t2.functionlink "
				+ "	from "
				+ "    	SYS_CUSTOM_MENU t1"
				+ "    	left join "
				+ "		Sys_Menu t2"
				+ "	    on t1.menuid = t2.code"
				+ " where roleid = '"
				+ roleid
				+ "' and t2.is_del='0'  and type='1'  order by t1.sort asc");*/
		
		String roleids = "";
		if(roleid != null && !"".equals(roleid)){
			String[] ids = roleid.split(",");
			for(int i=0;i<ids.length;i++){
				roleids += "'"+ids[i]+"',";
			}
			roleids = roleids.substring(0, roleids.lastIndexOf(","));
		}
		
		sql.append("select m.menu_id id, m.parent_menu_id pid, menu_name name, r.role_id roleid, m.sortno sort,menu_statu type,m.menu_img img, m.menu_url functionlink "
				+ "	from "
				+ "		Sys_Menu m  , "
				+ "	    sys_role_right_rel r "
				+ " where  m.menu_id = r.menu_id and  r.role_id in ("
				+ roleids
				+ ") and m.is_del='0'  order by m.menu_id asc");
		
		return this.loadInfo(sql.toString());
	}

	/**
	 * 根据角色查询显示的菜单信息
	 * 
	 * @param roleid
	 * @param nodeId
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List loadShowMenuInfoByRoleId(String roleid) {
/*		String sql = "select id, pid , name text, Target target,"
				+ " FunctionLink href, Method method, Width width, Height height,"
				+ " t1.handler hashandler, t1.type, t2.Img img, t1.sort  "
				+ " from SYS_CUSTOM_MENU t1 " + " left join Sys_Menu t2 "
				+ " on t1.menuid = t2.code" + " where t1.roleid='" + roleid
				+ "' and t2.is_del='0' order by t1.sort asc";*/
		String roleids = "";
		if(roleid != null && !"".equals(roleid)){
			String[] ids = roleid.split(",");
			for(int i=0;i<ids.length;i++){
				roleids += "'"+ids[i]+"',";
			}
			roleids = roleids.substring(0, roleids.lastIndexOf(","));
		}
		String sql = "select m.menu_id id, m.parent_menu_id pid , m.menu_name text, "
				+ " m.menu_url href, "
				+ " m.menu_statu type, m.menu_img img, m.sortno sort  "
				+ " from SYS_MENU m Sys_Role_right_rel r "
				+ " where m.menu_id = r.menu_id and r.role_id in (" + roleids
				+ " ) and m.is_del='0' order by m.menu_id asc";

		return this.loadInfo(sql);
	}

	/**
	 * 功能：根据角色清空自定义菜单表数据
	 * 
	 * @param roleid
	 *            角色编号
	 * @return int 影响的行数
	 */
	@Override
	public int clearCustomMenuByRoleId(String roleid) {
		String roleids = "";
		if(roleid != null && !"".equals(roleid)){
			String[] ids = roleid.split(",");
			for(int i=0;i<ids.length;i++){
				roleids += "'"+ids[i]+"',";
			}
			roleids = roleids.substring(0, roleids.lastIndexOf(","));
		}
		String sql = "delete from sys_role_right_rel where role_id in (" + roleids + ")";
		int res = 0;

		try {
			res = persistence.executeUpdateSQL(886, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		return res;
	}

	/***
	 * 功能：根据菜单编号级联删除自定义菜单
	 * 
	 * @param menuid
	 * @return int 影响条数
	 */
	@Override
	public int deleteById(String menuid) {
		String sql = "delete from SYS_CUSTOM_MENU where menuid = '" + menuid + "'";

		int res = 0;

		try {
			res = persistence.executeUpdateSQL(892, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		return res;
	}

	/**
	 * 查询数据
	 * 
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private List loadInfo(String sql) {
		List list = null;
		try {
			log.info("sql--  " + sql);
			list = persistence.getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		return list;
	}
	
	public List<SysMenu> getIndexMenu(String menuId,String roleIds){
		
		String[] rids = roleIds.split(",");
		String ins = "";
		for(int i=0;i<rids.length;i++){
			ins += "?,";
		}
		ins = ins.substring(0, ins.lastIndexOf(","));
		
		StringBuffer sql = new StringBuffer();
//		sql.append(" select distinct m.fd_objectid,m.menu_id,m.menu_name,m.menu_url,m.parent_menu_id,m.menu_info,m.sortno,m.menu_statu,m.is_leaf,m.in_control,m.is_del,m.mod_date,m.menu_img ");
//		sql.append(" from sys_menu m  ");
//		sql.append(" inner join sys_role_right_rel r on m.menu_id = r.menu_id  and r.role_id in ( "+ins+")");
//		
//		sql.append(" where  CHAR_LENGTH(m.menu_id)>3 ");
//		sql.append(" START WITH m.MENU_ID= ? ");
//		sql.append(" CONNECT BY PRIOR m.menu_ID=m.PARENT_MENU_ID ");
//		sql.append(" order by m.menu_id  ");
		
		sql.append(" select distinct m.fd_objectid,m.menu_id,m.menu_name,m.menu_url,m.parent_menu_id,m.menu_info,m.sortno,m.menu_statu,m.is_leaf,m.in_control,m.is_del,m.mod_date,m.menu_img ");
		sql.append(" from sys_menu m  ");
		sql.append(" inner join sys_role_right_rel r on m.menu_id = r.menu_id  and r.role_id in ( "+ins+")");
		sql.append(" where  CHAR_LENGTH(m.menu_id)>3 ");
		sql.append(" and left(m.menu_id,1)= ? ");
		sql.append(" and m.IS_DEL = '0' ");
		sql.append(" order by m.menu_id  ");
		
		log.info(sql.toString());
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<SysMenu> menuList = new ArrayList<SysMenu>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			for(int i=0;i<rids.length;i++){
				//ins += "?,";
				pstm.setString(i+1, rids[i]);
			}
			pstm.setString(rids.length+1, menuId);
			rs = pstm.executeQuery();
			while(rs.next()){
				SysMenu menu = new SysMenu();
				menu.setFd_objectid(rs.getString("fd_objectid"));
				menu.setMenu_id(rs.getString("menu_id"));
				menu.setMenu_name(rs.getString("menu_name"));
				menu.setMenu_url(rs.getString("menu_url"));
				menu.setParent_menu_id(rs.getString("parent_menu_id"));
				menu.setMenu_info(rs.getString("menu_info"));
				menu.setSortno(rs.getString("sortno"));
				menu.setMenu_statu(rs.getString("menu_statu"));
				menu.setIs_leaf(rs.getString("is_leaf"));
				menu.setIn_control(rs.getString("in_control"));
				menu.setIs_del(rs.getString("is_del"));
				menu.setMod_date(rs.getString("mod_date"));
				menu.setMenu_img(rs.getString("menu_img"));
				menuList.add(menu);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;		
	}
	
	public List<SysMenu> queryIndexMenu(String menuName,String roleIds){
		
		String[] rids = roleIds.split(",");
		String ins = "";
		for(int i=0;i<rids.length;i++){
			ins += "?,";
		}
		ins = ins.substring(0, ins.lastIndexOf(","));
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select distinct c.menu_id,c.menu_name   ");
		sql.append(" from sys_menu a   ");
		sql.append(" inner join sys_menu b on a.parent_menu_id = b.menu_id  ");
		sql.append(" inner join sys_menu c on b.parent_menu_id = c.menu_id  ");
		sql.append(" inner join sys_role_right_rel r on a.menu_id = r.menu_id  and r.role_id in ( "+ins+")"); 
		if(menuName!=null&&!"".equals(menuName)){
			sql.append(" where   a.menu_name like ?  ");
		}
		sql.append(" order by c.menu_id  ");
		
		log.info(sql.toString());
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<SysMenu> menuList = new ArrayList<SysMenu>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			for(int i=0;i<rids.length;i++){
				//ins += "?,";
				pstm.setString(i+1, rids[i]);
			}
			pstm.setString(rids.length+1, "%"+menuName+"%");
			rs = pstm.executeQuery();
			while(rs.next()){
				SysMenu menu = new SysMenu();
				menu.setMenu_id(rs.getString("menu_id"));
				menu.setMenu_name(rs.getString("menu_name"));
				menuList.add(menu);
			}			
			
		}catch(Exception e){
			log.error("查询菜单错误"+e.getMessage());
			
		}finally{
			try {
				if(rs!=null){
					
					rs.close();
				}
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				log.error("关闭数据库连接错误:"+e.getMessage());
			}
		}
		return menuList;		
	}
	
public List<SysMenu> queryIndexSubMenu(String menuName,String roleIds){
		
		String[] rids = roleIds.split(",");
		String ins = "";
		for(int i=0;i<rids.length;i++){
			ins += "?,";
		}
		ins = ins.substring(0, ins.lastIndexOf(","));
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select distinct a.MENU_ID,a.MENU_NAME,a.MENU_URL,a.SORTNO,a.MENU_IMG,c.menu_id as parent_menu_id ");
		sql.append(" from sys_menu a   ");
		sql.append(" inner join sys_menu b on a.parent_menu_id = b.menu_id  ");
		sql.append(" inner join sys_menu c on b.parent_menu_id = c.menu_id  ");
		sql.append(" inner join sys_role_right_rel r on a.menu_id = r.menu_id  and r.role_id in ( "+ins+")"); 
		if(menuName!=null&&!"".equals(menuName)){
			sql.append(" where   a.menu_name like ?  ");
		}
		sql.append(" order by a.menu_id  ");
		
		log.info(sql.toString());
		
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<SysMenu> menuList = new ArrayList<SysMenu>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			for(int i=0;i<rids.length;i++){
				//ins += "?,";
				pstm.setString(i+1, rids[i]);
			}
			pstm.setString(rids.length+1, "%"+menuName+"%");
			rs = pstm.executeQuery();
			while(rs.next()){
				SysMenu menu = new SysMenu();
				menu.setMenu_id(rs.getString("menu_id"));
				menu.setMenu_name(rs.getString("menu_name"));
				menu.setMenu_url(rs.getString("menu_url"));
				menu.setParent_menu_id(rs.getString("parent_menu_id"));
				menu.setSortno(rs.getString("sortno"));
				menu.setMenu_img(rs.getString("menu_img"));
				menuList.add(menu);
			}			
			
		}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 addIndexMenu(IndexMenu indexMenu){
		String result = "error";
			String sql = "INSERT  INTO SYS_INDEX_MENU (FD_OBJECTID, IMAGE_URL, TITLE_NAME, BUSINESS_URL, IS_DEL, USER_ID, SORT, STATE, UPDATEDATE, MENU_ID, CLICK_NUM) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			DbConnection dbConn = new DbConnection();
			Connection conn = null;
			PreparedStatement pstm = null;
				try {
					conn = dbConn.getConnection();
					pstm = conn.prepareStatement(sql);
					//设置参数值
					pstm.setString(1, indexMenu.getFD_OBJECTID());
					pstm.setString(2, indexMenu.getIMAGE_URL());
					pstm.setString(3, indexMenu.getTITLE_NAME());
					pstm.setString(4, indexMenu.getBUSINESS_URL());
					pstm.setString(5, indexMenu.getIS_DEL());
					pstm.setString(6, indexMenu.getUSER_ID());
					pstm.setString(7, indexMenu.getSORT());
					pstm.setString(8, indexMenu.getSTATE());
					pstm.setString(9, indexMenu.getUPDATEDAE());
					pstm.setString(10, indexMenu.getMENU_ID());
					pstm.setInt(11, indexMenu.getCLICK_NUM());
					int rs  = pstm.executeUpdate();
					if(rs==1){
						result = "ok";
					}
				} 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 checkAddMenu(String user_id, String menu_id) {
		int result = 0;
		String sql = "update SYS_INDEX_MENU set STATE=0,UPDATEDATE=? where USER_ID=? and MENU_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, sdf.format(date));
				pstm.setString(2, user_id);
				pstm.setString(3, menu_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 String deleteAddMenu(String fd_id) {
		String result = "error";
		String sql = "update SYS_INDEX_MENU set STATE=1 where  FD_OBJECTID=?";
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				//设置参数值
				pstm.setString(1, fd_id);
				int i = pstm.executeUpdate();
				if(i==1){
					result = "success";
				}
			} 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;
	}
	
	/**
	 * 根据user_id查询个人偏好菜单
	 * @param user_id
	 * @return
	 */
	
	public List<IndexMenu> queryIndexMenu(String user_id){
		StringBuffer sql = new StringBuffer();
		//sql.append(" select FD_OBJECTID,IMAGE_URL,TITLE_NAME,BUSINESS_URL,IS_DEL,USER_ID,SORT,STATE,UPDATEDATE,MENU_ID from SYS_INDEX_MENU where IS_DEL = '0' and STATE = '0' ");
		sql.append(" select SIM.FD_OBJECTID,SM.MENU_URL,SM.MENU_ID,SM.MENU_NAME,SM.MENU_IMG from SYS_MENU SM LEFT JOIN SYS_INDEX_MENU SIM ON SM.MENU_ID = SIM.MENU_ID where SM.IS_DEL = '0'  AND SIM.STATE = '0' ");
    	sql.append(" and SIM.user_id = ");
    	sql.append("'"+user_id+"'");
	
	    sql.append(" ORDER BY SIM.CLICK_NUM DESC ");
	    log.info(sql);
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<IndexMenu> menuList = new ArrayList<IndexMenu>();
		try{
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while(rs.next()){
				IndexMenu menu = new IndexMenu();
				menu.setFD_OBJECTID(rs.getString("FD_OBJECTID"));
				menu.setBUSINESS_URL(rs.getString("MENU_URL"));
				menu.setMENU_ID(rs.getString("MENU_ID"));
				menu.setTITLE_NAME(rs.getString("MENU_NAME"));
				menu.setIMAGE_URL(rs.getString("MENU_IMG"));
				menuList.add(menu);
			}			
			
		}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 setUserClickNum(String menu_id, String user_id) {
		String result = "error";
		String sql = "update SYS_INDEX_MENU set CLICK_NUM=CLICK_NUM+1 where  MENU_ID=? AND USER_ID=?";
		DbConnection dbConn = new DbConnection();
		Connection conn = null;
		PreparedStatement pstm = null;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				//设置参数值
				pstm.setString(1, menu_id);
				pstm.setString(2, user_id);
				int i = pstm.executeUpdate();
				if(i==1){
					result = "success";
				}
			} 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;
	}

	
}