package com.vci.server.framework.systemConfig;


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

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.type.Type;

import com.vci.common.objects.UserEntity;
import com.vci.common.utility.ObjectUtility;
import com.vci.corba.framework.data.DataSourceInfo;
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;


/**
 * 系统配置的服务类，通过此类来完成特殊字符、属性、分隔符、字符集、补位方式
 * 流水方式以及补位字符的设置。
 * @author Administrator
 *
 */
public class SystemCfgService extends BaseService{

	public void saveSystemCfg(final SystemCfg cfg,final UserEntity userEntity) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				cfg.setUserEntity(userEntity);
				impl.save(cfg);
				return cfg;
			}
		});
	}

	/**
	 * 
	 * <p> 根据编码项ID获取日期格式字符对象
	 * 如何编码项ID为空，则查询系统所有的日期格式对象</p>
	 * 
	 * @author llb
	 * @time 2013-1-9
	 * @param type
	 * @param codeItemPid
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SystemCfg> selectSystemCfgByCodeType(final String type,
			final String codeItemPid) {
		return (List<SystemCfg>) new HibernateTemplate()
				.run(new HibernateCallback() {
					public Object execute() throws HibernateException {
						SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
						StringBuilder sql = new StringBuilder();
						sql.append("select * from plsystemconf cfg where cfg.plname = ");
						sql.append("'" + type + "'");
						sql.append(" and cfg.PLOID in (");
						sql.append("select p.PLBASECONFIGID  from plcodeclsfbasconfig p where p.PLCODEID='"
								+ codeItemPid
								+ "' and p.PLNAMETYPE='"
								+ type + "'");
						sql.append(")");
						Object[] values = new Object[0];
						return impl.findEntites(sql.toString(), values, "",	SystemCfg.class);
					}
				});
	}
	/**
	 * 
	 * <p>根据编码项ID 添加特殊字符，可用字符集 </p>
	 * 
	 * @author llb
	 * @time 2013-1-9
	 * @param cfg
	 * @param codeItemPid
	 * @param userEntity
	 */
	public void saveSystemCfgByCodeType(final SystemCfg cfg,final String codeItemPid,final UserEntity userEntity) {
		new HibernateTemplate().runExt(new HibernateCallbackExt() {
			public Object execute(Connection conn) throws HibernateException,
			SQLException{
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				cfg.setUserEntity(userEntity);
				impl.save(cfg);
				String id = ObjectUtility.getNewObjectID36();
				StringBuilder sql = new StringBuilder();
				sql.append("insert into plcodeclsfbasconfig (PLOID,PLCODEID,PLBASECONFIGID,PLNAMETYPE)");
				sql.append(" VALUES (");
				sql.append("'"+id+"',");
				sql.append("'"+codeItemPid+"',");
				sql.append("'"+cfg.getId()+"',");
				sql.append("'"+cfg.getName()+"'");
				sql.append(")");
				doFolowRelation( conn,sql.toString());
				return cfg;
			}
		});
	}
	// 处理基础配置和编码规则项关联关系
	public boolean doFolowRelation(Connection conn, final String sql)
			throws SQLException {
		boolean result = false;
		PreparedStatement psmt = conn.prepareStatement(sql);
		try {
			result = psmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			psmt.close();
		}
		return result;

	}
	public boolean saveIsCheck(final SystemCfg cfg) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				SystemCfg sysCfg = impl.getById(cfg.getId());
				if(sysCfg != null){
					sysCfg.setId(cfg.getId());
					sysCfg.setName(cfg.getName());
					sysCfg.setValue(cfg.getValue());
					sysCfg.setUserEntity(userEntity);
					impl.saveOrUpdate(sysCfg);
				}else{
					cfg.setUserEntity(userEntity);
					impl.saveOrUpdate(cfg);
				}
				return true;
			}
		});
	}
	
	public SystemCfg getIsCheck(){
		return (SystemCfg)new HibernateTemplate().run(new HibernateCallback() {
			
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				String hql = "from SystemCfg s where s.name = 'isCheck'";
				SystemCfg cfg = impl.findEntity(hql);
				return cfg;
			}
		});
	}
	
	public void updateSystemCfg(final SystemCfg cfg,final UserEntity userEntity) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				SystemCfg curCfg = impl.loadById(cfg.getId());
				curCfg.setName(cfg.getName());
				curCfg.setValue(cfg.getValue());
				curCfg.setUserEntity(userEntity);
				impl.saveOrUpdate(curCfg);
				return curCfg;
			}
		});
	}
	
	public boolean deleteSystemCfgByMQL(final String id) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql = "delete SystemCfg sc where sc.id =:id";
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				impl.deleteQueryObject(hql, "id", id, userEntity);
				return true;
			}
		});
	}
	
	public boolean deleteSystemCfgById(final String id) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				SystemCfg cfg = impl.loadById(id);
				cfg.setUserEntity(userEntity);
				impl.delete(cfg);
				return true;
			}
		});
	}
	
	public boolean deleteSystemCfgByMQL(final String[] ids,final UserEntity userEntity) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql = "delete SystemCfg sc where sc.id in (";
				int len = ids.length;
				for (int i = 0; i < len; i++) {
					hql += "?";
					if (i != len - 1) {
						hql += ",";
					}
				}
				hql += ")";
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				impl.deleteQueryObject(hql, ids, userEntity);
				return true;
			}
		});
	}
	
	public SystemCfg selectSystemCfg(final String id) {
		return (SystemCfg)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				return impl.loadById(id);
			}
		});
	}
	
	/**
	 * 获取根据类型获取字符对象
	 * @param type
	 * @return
	 */
	public List selectSystemCfgByType(final String type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				String hsql = "from SystemCfg cfg where cfg.name = :name order by cfg.value";
				return impl.findEntities(hsql, "name", type);
			}
		});
	}
	
	/**
	 * 通过类型和名称选择指定的系统配置
	 * @param type
	 * @param val
	 * @return
	 */
	public SystemCfg selectSystemCfgByTypeAndVal(final String type,final String val , final String ruleId) {
		return (SystemCfg)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				String hsql = " select * from plsystemconf cfg where cfg.plname = '"+type+"' and  cfg.plvalue = '"+val+"' and cfg.ploid in (" +
						" select p.PLBASECONFIGID from plcodeclsfbasconfig p where p.PLCODEID ='"+ruleId+"' and p.PLNAMETYPE = '"+type+"')";
//				String[] values = new String[2];
//				values[0] = type;
//				values[1] = val;
//				return impl.findEntity(hsql, values);
				Object[] values = new Object[0];
				List<SystemCfg> list = impl.findEntites(hsql, values, "",SystemCfg.class);
				if (list.size() != 0){
					return list.get(0);
				}else{
					return null;
				}
			}
		});
	}
	public SystemCfg selectSystemCfgByTypeAndValByCodeType(final String type,final String codeItemPid, final String val) {
		return (SystemCfg)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
			/**'在oracle中无法查询，必须进行转义**/
			String newVal = val;
			if("'".equals(val)) {
				newVal = "''";
			}
			/**
			 * Hibernate要验证保留字符.而以下SQL语句中就包含了Hibernate禁止的非法字符,导致{无法查询.
			 * 对SQLQuery采用setParameter的形式,将参数设置到Hibernate,Hibernate会自动进行转换.
			 * add by liujw
			 * **/
			if("{".equals(val)) {
				Session session=HibernateSessionFactory.getSession();
				String hsql = "select * from plsystemconf cfg where cfg.PLNAME = '"+type+
						"' and cfg.PLVALUE = ? and cfg.ploid in("+
						"select p.PLBASECONFIGID  from plcodeclsfbasconfig p where p.PLCODEID= '"+codeItemPid+"' and p.plnametype='"+type+"')";
				Query query = session.createSQLQuery(hsql).addEntity("", SystemCfg.class); 
				query.setParameter(0,"{");
				List<SystemCfg> list=query.list();
				SystemCfg SystemCfg   = null;
				if(list.size()!=0){
				 SystemCfg  = (SystemCfg)list.get(0);
				}
				return SystemCfg;	
			}
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				String hsql = "select * from plsystemconf cfg where cfg.PLNAME = '"+type+
						"' and cfg.PLVALUE= '"+newVal+"' and cfg.ploid in("+
						"select p.PLBASECONFIGID  from plcodeclsfbasconfig p where p.PLCODEID= '"+codeItemPid+"' and p.plnametype='"+type+"')";
				List<SystemCfg>	list = impl.findEntites(hsql, new Object[]{}, "", SystemCfg.class);
				SystemCfg SystemCfg   = null;
				if(list.size()!=0){
				 SystemCfg  = list.get(0);
				}
				return SystemCfg;
			}
		});
	}
	public List getSystemCfgList() {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				return impl.loadAll();
			}
		});
	}
	
	public List getSystemCfgPageList(final int pageNo, final int pageSize) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				return impl.findList(pageNo, pageSize);
			}
		});
	}
	/**
	 * 查看日期格式是否可删除
	 * @param value
	 * @return
	 */
	public boolean checkDateFmtCanDelete(final String ruleId, final String value){
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int count = 0;
				boolean res = false;
				String hql = "select count(*) from pldatesec p where p.plfrt = '"+value+"' and p.ploid in (" +
						"select t.ploid from plsection t where t.plsecgrpoid = '"+ruleId+"' AND T.PLSECTYPE = 3 )";
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				List list = new ArrayList();
				Object[] values = new Object[0];
				Map<String,Type> map = new HashMap<String,Type>();
				list = impl.findEntitesBySQL(hql, values,map);
				if (list != null){
					count = Integer.valueOf(String.valueOf(list.get(0))).intValue();
				}
				if (count >= 1 ){
					res = false;
				}else {
					res = true;
				}
				return res;
			}
		});
	}
	/**
	 * 查看单位能不能被删除
	 * @param unitOid
	 * @return
	 */
	public boolean checkUnitCanDelete(final String unitOid){
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int count = 0;
				boolean res = false;
				String hql = "select count(*) from plrmtemplateattrconf p where p.plunitoid = '"+unitOid+"'";
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				List list = new ArrayList();
				Object[] values = new Object[0];
				Map<String,Type> map = new HashMap<String,Type>();
				list = impl.findEntitesBySQL(hql, values,map);
				if (list != null){
					count = Integer.valueOf(String.valueOf(list.get(0))).intValue();
				}
				if (count >= 1 ){
					res = false;
				}else {
					res = true;
				}
				return res;
			}
		});
	}
	
	public boolean fillCharCanChange(final String value){
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int count = 0;
				boolean res = false;
				String hql = "select count(*) from PLFLOWSEC p where p.PLFILCHAR = ?";
				SystemCfgDAOImpl impl = new SystemCfgDAOImpl();
				List list = new ArrayList();
				Object[] values = new Object[1];
				Map<String,Type> map = new HashMap<String,Type>();
				values[0] = value;
				list = impl.findEntitesBySQL(hql, values,map);
				if (list != null){
					count = Integer.valueOf(String.valueOf(list.get(0))).intValue();
				}
				if (count >= 1 ){
					res = false;
				}else {
					res = true;
				}
				return res;
			}
		});
	}

	public void getDataSourceInfo(DataSourceInfo info) {
		//查询表空间大小
		String sql =" select b.tablespace_name ts_name, (max(b.bytes)/1024/1024) total_size, " +
				" (max(b.bytes) - sum(nvl(a.bytes, 0)))/1024/1024 used_size " +
				" from dba_free_space a, dba_data_files b " +
				" where a.file_id = b.file_id " +
				" and b.tablespace_name=(select default_tablespace from user_users) " + 
				" group by b.tablespace_name";
		try {
			PreparedStatement prep = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
			ResultSet query = prep.executeQuery();
			while(query.next()){
				info.tbSpaceName = query.getString("ts_name");
				info.tbSpaceTotal = query.getString("total_size");
				info.tbSpaceUsed = query.getString("used_size");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getLogSize(DataSourceInfo info) {
		String sql = " select t.table_name, t.blocks*8192/1024/1024 log_file_size, l.log_count " + 
				" from user_tables t, (select count(ploid) log_count from pllog) l " +
				" where t.table_name = 'PLLOG'";
		try {
			PreparedStatement prep = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
			ResultSet query = prep.executeQuery();
			while(query.next()){
				info.pllogCount = query.getString("log_count");
				info.pllogFileSize = query.getString("log_file_size");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}