package com.vci.server.framework.systemConfig.log;

import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.criterion.Order;
import org.hibernate.type.Type;

import com.vci.common.resource.CommonProperties;
import com.vci.corba.common.data.VCIInvocationInfo;
import com.vci.server.base.persistence.dao.BaseService;
import com.vci.server.base.persistence.dao.HibernateCallback;
import com.vci.server.base.persistence.dao.HibernateCallbackExt;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.persistence.dao.HibernateTemplate;
import com.vci.server.framework.systemConfig.SystemCfg;
import com.vci.server.framework.systemConfig.SystemCfgDAOImpl;

/**
 * 日志模块提供服务的Service
 * @author xiong fei 
 * @time 2011-06-16
 */
public class LogService extends BaseService {
	
	private final String LOG_DELETE_AUTO = "log.delete.auto";//配置日志是否进行自动删除
	private final String LOG_SAVE_PERIODS = "log.save.periods";//配置的日志保存期限下拉框的值
	private final String LOG_QUERY_PAGESIZE = "log.query.pagesize";//配置的日志页面显示条数
	
	public LogService() {
		AlterTable();
	}
	
	/*
	 * 日志表增加日志内容字段
	 */
	private void AlterTable() {
		String sql = "SELECT COUNT(1) as COLNUM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='PLLOG' AND COLUMN_NAME='PLCONTENT'";
		Connection connection = HibernateSessionFactory.getSessionConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		try {
			pst = connection.prepareStatement(sql);
			rs = pst.executeQuery();

			int count = 0;
			while(rs.next()){
                count = rs.getInt("COLNUM");
			}
			rs.close();
			rs = null;
			pst.close();
			pst = null;
			
			if (count == 0) {
				sql = "ALTER TABLE PLLOG ADD PLCONTENT VARCHAR2(1000)";
				pst = connection.prepareStatement(sql);
				pst.execute();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				
				if(pst != null) {
					pst.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	/**
	 * 获取日志删除配置
	 * @return true表示自动删除，false表示手动删除
	 */
	public boolean getIsAutoDelete() {
		boolean res = false;
		String isAuto = CommonProperties.getStringProperty(LOG_DELETE_AUTO);
		if(isAuto.equals("Y")){
			res = true;
		}
		return res;
	}
	
	/**
	 * 获取配置好的保存和备份期限的下拉框的值
	 * @return
	 */
	public LogPeriod[] getPeriods() {
		String periods = CommonProperties.getStringProperty(LOG_SAVE_PERIODS);
		String[] period = periods.split("::");
		LogPeriod[] res = new LogPeriod[period.length];
		for(int i = 0;i<period.length;i++){
			String[] temp = period[i].split("#");
			LogPeriod obj = new LogPeriod();
			obj.setCode(temp[0]);
			obj.setValue(temp[1]);
			res[i] = obj;
		}
		return res;
	}
	
	/**
	 * 获取配置好的日志页面显示条数
	 * @return
	 */
	public int getPageSize() {
		String pageSize = CommonProperties.getStringProperty(LOG_QUERY_PAGESIZE);
		if(pageSize == null){//如果获取失败，返回0
			return 0;
		} else {
			return Integer.parseInt(pageSize);
		}
	}
	
	/**
	 * 保存日志
	 * <p>Description:保存日志 </p>
	 * 
	 * @author xf
	 * @time 2012-6-1
	 */
	public void saveLog(final Log log){
		new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				LogDAOImpl impl = new LogDAOImpl();
				impl.save(log);
				return true;
			}
		});
	}
	
	/**
	 * 获取本次查询日志总数
	 * @param sql 前台条件查询的SQL
	 * @return 日志总数
	 */
	public long getSumLogRows(final String sql) {
		return (Long)new HibernateTemplate().run(new HibernateCallback() {
			
			public Object execute() throws HibernateException {
				LogDAOImpl impl = new LogDAOImpl();
				return impl.getCountForLog(sql);
			}
		});
	}
	
	/**
	 * 使用SQL获取日志的列表
	 * @param sql sql语句
	 * @return 日志的对象
	 */
	public List<Log> getLogListBySql(final String sql){
		return (List)new HibernateTemplate().runExt(new HibernateCallbackExt() {
					
					public Object execute(Connection connection) throws HibernateException,
							SQLException {
						LogDAOImpl impl = new LogDAOImpl();
						return impl.findEntites("from Log where " + sql + " order by date desc ",new String[0]);
					}
				});
	}
	/**
	 * 分页查询，获取本次查询的日志集合
	 * @param pageNO 当前页数
	 * @param PageSize 页面大小
	 * @return
	 */
	public List getLogList(final int pageNo,final int pageSize,final String sql){
		return (List)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				LogDAOImpl impl = new LogDAOImpl();
				Order order = Order.desc("date");//按时间排序
				return impl.findList(pageNo, pageSize,sql,order);
			}
		});
	}
	
	public List getLogListByContion(final int pageNo,final int pageSize,final String sql){
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				int V_PAGE_ROWNUM_START = (pageNo - 1) * pageSize;
		        int V_PAGE_ROWNUM_END = (pageNo ) * pageSize;
				
				LogDAOImpl impl = new LogDAOImpl();
				StringBuffer sb = new StringBuffer();
				sb.append(" SELECT * FROM ( ");
				sb.append("   SELECT ROW_.*,ROWNUM RN FROM ( ");
				sb.append(" select * from pllog P WHERE ");
				sb.append(sql);
				sb.append(" ORDER BY PLDATE DESC ");
				sb.append("  ) ROW_ ");
				sb.append(" )WHERE RN <= ");
				sb.append(V_PAGE_ROWNUM_END);
				sb.append(" AND RN > ");
				sb.append(V_PAGE_ROWNUM_START);
				return impl.findEntites(sb.toString(), new String[]{}, "P", Log.class);
			}
		});
	}
	
	
	/**
	 * 通过SQL语句执行日志删除，效率更高
	 * @param sql
	 * @return
	 */
	public boolean deleteLogBySql(final String sql, final long curPeriod){
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				LogDAOImpl impl = new LogDAOImpl();
				impl.createSQLQuery(sql, new String[0]);
				
				String result = "删除 " + curPeriod + " 个月之前的日志 ";
				writeLogDeleteLog("logAutoDeleter", getServerIP(), result, impl);
				
				return true;
			}
		});
	}
	
	private String getServerIP(){
		String ip = "127.0.0.1";
		try {
			ip = java.net.InetAddress.getLocalHost().getHostAddress();
		} catch (UnknownHostException e) {
			e.printStackTrace();
		}
		return ip;
	}
	
	/**
	 * 保存日志保存/备份期限
	 * @param sysCfg 系统配置表的对象
	 * @return
	 */
	public boolean savePeriod(final SystemCfg sysCfg) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				String sql = "from SystemCfg s where s.name = '"+sysCfg.getName()+"'";
				SystemCfg temp = impl.findEntity(sql);
				if(temp == null) {//如果不存在配置，插入
					sysCfg.setUserEntity(userEntity);
					impl.save(sysCfg);
				} else {//如果存在配置，更新
					temp.setName(sysCfg.getName());
					temp.setValue(sysCfg.getValue());
					temp.setUserEntity(userEntity);
					impl.update(temp);
				}
				return true;
			}
		});
	}
	
	/**
	 * 获取保存/备份期限值
	 * @param type
	 * @return
	 */
	public int getCurPeriod(final String type) {
		return (Integer)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				SystemCfg sysCfg = new SystemCfg();
				int curPeriod = 0;
				String sql = " from SystemCfg s where s.name ='"+type+"'";
				sysCfg = impl.findEntity(sql);
				if(sysCfg != null){
					curPeriod = Integer.parseInt(sysCfg.getValue());
				} 
				return curPeriod;
			}
		});
	}
	
	/**
	 * 手动删除日志
	 * @param deleteDate 所选日期，将删除该日期之前的日志
	 * @return
	 */
	public boolean deleteLog(final String deleteDate) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				LogDAOImpl impl = new LogDAOImpl();
				String delDate = deleteDate;
				String sql = "delete from pllog where pltype<>'删除' and plmodule<>'日志管理' and pllogtype<>'一般操作' and pldate <= to_date('"+delDate+"','yyyy-MM-dd') ";
				
				if(deleteDate.contains("##")){
					String[] vvs = deleteDate.split("##");
					delDate = vvs[0];
					sql = "delete from pllog where pltype<>'删除' and plmodule<>'日志管理' and pllogtype<>'一般操作'  and pldate <= to_date('"+delDate+"','yyyy-MM-dd') and " + vvs[1];
				}
				
				impl.createSQLQuery(sql);

				VCIInvocationInfo vcii = HibernateSessionFactory.getVciSessionInfo();
				String result = "删除 " + delDate + " 之前的日志 ";
				writeLogDeleteLog(vcii.userName, vcii.clientIPInfo, result, impl);
				return true;
			}
		});
	}
	
	
	/**
	 * 记录日志删除的日志
	 */
	public void writeLogDeleteLog(String userName, String ip, String result, LogDAOImpl impl){
		if(impl == null){
			impl = new LogDAOImpl();
		}
		String sql = "insert into pllog(ploid, pluser, plip, plresult, pldate, pltype, plmodule, pllogtype) " +
				"values(" +
				"get_uuid()," +
				"'" + userName + "'," +
				"'" + ip + "'," + 
				"'" + result + "'," + 
				"sysdate," + 
				"'" + "删除" + "'," + 
				"'" + "日志管理" + "'," + 
				"'" + "一般操作" + "'" + 
				")";
		impl.createSQLQuery(sql);
	}
//	/**
//	 * 删除日志
//	 * @param list
//	 * @return
//	 */
//	public boolean deleteLog(final List<Log> list) {
//		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
//			public Object execute() throws HibernateException {		
//				LogDAOImpl impl = new LogDAOImpl();
//				for(Log entity:list){
//					impl.delete(entity);
//				}
//				return true;
//			}
//		});
//	}
//	
	/**
	 * 根据模块的源路径返回模块的别名
	 * @param modulePath
	 * @return
	 */
	public String getModuleAlias(final String modulePath){
		return (String)new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				String res = "";
				LogDAOImpl impl = new LogDAOImpl();
				String sql = "SELECT T.PLALIASNAME FROM PLFUNCTION T WHERE T.PLRESOURCEC = ?";
				Object[] values = { modulePath };
				LinkedHashMap<String, org.hibernate.type.Type> map = new LinkedHashMap<String, Type>();
				map.put("PLALIASNAME", org.hibernate.type.StringType.INSTANCE);
				List<String> list = impl.findEntitesBySQL(sql, values, map);
				if(list.size() != 0){
//					Object[] data = list.get(0);
//					if(data != null && data.length > 0){
//						res = (String) data[0];
//					}
					res = list.get(0);
					if(res == null || "null".equals(res)) {
						res = "";
					}
				}
				return res;
			}
		});
	}
	
	/**
	 * 根据模块的源路径返回角色名称
	 * 
	 */
	/*public String getRoleName(final String modulePath,final String userName){
		return (String)new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				String res = "";
				LogDAOImpl impl = new LogDAOImpl();
				StringBuilder logSql = new StringBuilder();
				logSql.append(" select pr.plname from plrole pr where pr.pluid in ( ");
				logSql.append("  select r.plroleoid  from Plroleright r where r.plfuncoid in ( ");
				logSql.append("   select pf.ploid  from plfunction pf  where pf.plresourcec = ");
				logSql.append(" ? ");
				logSql.append(" )");
				logSql.append("  )");
				logSql.append(" intersect ");
				logSql.append(" select pr.plname from plrole pr where pr.pluid in ( ");
				logSql.append("    select ur.plroleuid from pluserrole ur where ur.pluseruid in ( ");
				logSql.append("           select pu.pluid from pluser pu where pu.plusername= ");
				logSql.append(" ? ");
				logSql.append(" ) ");
				logSql.append(" ) ");
				Object[] values = { modulePath, userName};
				LinkedHashMap<String, org.hibernate.type.Type> map = new LinkedHashMap<String, Type>();
				List<String> list = impl.findEntitesBySQL(logSql.toString(), values, map);
				if(list.size() != 0){
					res = list.get(0);
					if(res == null || "null".equals(res)) {
						res = "";
					}
				}
				return res;
			}
		});
	}*/

	public String getRoleName(final String modulePath,final String userName){
		return (String)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			@Override
			public Object execute(Connection paramConnection)
					throws HibernateException, SQLException {
				String res = "";
				LogDAOImpl impl = new LogDAOImpl();
				StringBuilder logSql = new StringBuilder();
				logSql.append(" select pr.plname from plrole pr where pr.pluid in ( ");
				logSql.append("  select r.plroleoid  from Plroleright r where r.plfuncoid in ( ");
				logSql.append("   select pf.ploid  from plfunction pf  where pf.plresourcec = ");
				logSql.append(" ? ");
				logSql.append(" )");
				logSql.append("  )");
				logSql.append(" intersect ");
				logSql.append(" select pr.plname from plrole pr where pr.pluid in ( ");
				logSql.append("    select ur.plroleuid from pluserrole ur where ur.pluseruid in ( ");
				logSql.append("           select pu.pluid from pluser pu where pu.plusername= ");
				logSql.append(" ? ");
				logSql.append(" ) ");
				logSql.append(" ) ");
				Object[] values = { modulePath, userName};
				LinkedHashMap<String, org.hibernate.type.Type> map = new LinkedHashMap<String, Type>();
				List<String> list = impl.findEntitesBySQL(logSql.toString(), values, map);
				if(list.size() != 0){
					res = list.get(0);
				}else{
					res =getMessage(paramConnection,userName);
				}
				return res;
			}
		});
	}
	
	public String getMessage(Connection paramConnection,String userName){
		String res = "";
		StringBuilder logSql = new StringBuilder();
		logSql.append(" select pr.plname ,pr.pltype from plrole pr where pr.pluid in ( ");
		logSql.append("    select ur.plroleuid from pluserrole ur where ur.pluseruid in ( ");
		logSql.append("           select pu.pluid from pluser pu where pu.plusername= ");
		logSql.append(" '"+userName+"' ");
		logSql.append(" ) ");
		logSql.append(" ) ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = paramConnection.prepareStatement(logSql.toString());
			rs = pstmt.executeQuery();
			LinkedHashMap<String, String> value = new LinkedHashMap<String, String>();
			while(rs.next()){
                value.put(rs.getString(2),rs.getString(1));
			}
			if(value.get("1")!=null){
				res = value.get("1");
			}else{
				res = value.get("2");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				
				if(pstmt != null) {
					pstmt.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return res;
	}
	
}