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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

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

import com.vci.common.objects.UserEntity;
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.base.persistence.dao.JDBCCallback;
import com.vci.server.base.persistence.dao.JDBCRunType;
import com.vci.server.base.persistence.dao.JDBCTemplate;
import com.vci.server.framework.systemConfig.stafforgmanage.user.User;

public class MachSecurityService  extends BaseService{
	public MachSecurityService() {
	}

	public MachSecurityService(UserEntity userEnt) {
		super(userEnt);
	}

	
	public void saveMachSecurity(final MachSecurity security) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				security.setUserEntity(userEntity);
				impl.save(security);
				return security;
			}
		});
	}
	
	public void saveMachSecurities(final MachSecurity[] securities) throws SQLException {
		String sql = "INSERT INTO PLMACHSECURITY (PLUID, PLNAME, MACADDRESS, IPADDRESS, PLSECRETGRADE, PLCREATETIME, PLCREATOR, PLMODIFYTIME, PLMODIFIER, PLDESC)" + 
						" VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		Timestamp currentTime = new Timestamp(System.currentTimeMillis());
		PreparedStatement pst = null;
		int index = 1;
		int batchSize = 200;
		for (int i = 0; i < securities.length; i++) {
			index = 1;
			if(pst == null){
				pst = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
			}
			pst.setString(index++, securities[i].getId());
			pst.setString(index++, securities[i].getName());
			pst.setString(index++, securities[i].getMacAddress());
			pst.setString(index++, securities[i].getIpAddress());
			pst.setInt(index++, securities[i].getSecretGrade());
			pst.setTimestamp(index++, currentTime);
			pst.setString(index++, userEntity.getUserName());
			pst.setTimestamp(index++, currentTime);
			pst.setString(index++, userEntity.getUserName());
			pst.setTimestamp(index++, currentTime);
			pst.setString(index++, securities[i].getDescription());

			pst.addBatch();
			
			// 是否达到批量执行阀值
			if((i+1) % batchSize == 0){
				// 达到则执行
				pst.executeBatch();
			}
		}
		
		pst.executeBatch();

		closePreparedStatement(pst);
	}
	

	public void updateMachSecurities(MachSecurity[] securities) throws SQLException {
		String sql = "UPDATE PLMACHSECURITY SET PLNAME=?, MACADDRESS=?, IPADDRESS=?, PLSECRETGRADE=?, PLMODIFYTIME=?, PLMODIFIER=?, PLDESC=? WHERE PLUID=?";
		
		Timestamp curTime = new Timestamp(System.currentTimeMillis());
		PreparedStatement pst = null;
		int index = 1;
		int batchSize = 200;
		for (int i = 0; i < securities.length; i++) {
			index = 1;
			if(pst == null){
				pst = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
			}
			pst.setString(index++, securities[i].getName());
			pst.setString(index++, securities[i].getMacAddress());
			pst.setString(index++, securities[i].getIpAddress());
			pst.setInt(index++, securities[i].getSecretGrade());
			pst.setString(index++, userEntity.getUserName());
			pst.setTimestamp(index++, curTime);
			pst.setString(index++, securities[i].getDescription());
			pst.setString(index++, securities[i].getId());
		
			pst.addBatch();
			
			// 是否达到批量执行阀值
			if((i+1) % batchSize == 0){
				// 达到则执行
				pst.executeBatch();
			}
		}
		
		pst.executeBatch();
		
		closePreparedStatement(pst);
	}

	
	private void closePreparedStatement(PreparedStatement pst) throws SQLException{
		if(pst != null){
			pst.clearBatch();
			pst.clearParameters();
			pst.close();
			pst = null;
		}
	}
	
	
	public boolean deleteMachSecurity(final String id) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				MachSecurity security = impl.loadById(id);
				security.setUserEntity(userEntity);
				impl.delete(security);
				return true;
			}
		});
	}
	
	public boolean deleteMachSecurityByMQL(final String id) {
		return deleteMachSecurityByMQL(new String[]{id});
	}
	
	public boolean deleteMachSecurityByMQL(final String[] ids) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int len = ids.length;
				String inStr = "";
				for (int i = 0; i < len; i++) {
					inStr += "?";
					if (i != len - 1) {
						inStr += ",";
					}
				}
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				
				String hql = "delete MachSecurity ms where ms.id in (" + inStr + ")";
				impl.deleteQueryObject(hql, ids, userEntity);
				return true;
			}
		});
	}
	
	public boolean updateMachSecurity(final MachSecurity security) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				MachSecurity secTmp = impl.getById(security.getId());
				if (secTmp == null) {
					security.setUserEntity(userEntity);
					impl.saveOrUpdate(security);
				} else {
					secTmp.setName(security.getName());
					secTmp.setIpAddress(security.getIpAddress());
					secTmp.setMacAddress(security.getMacAddress());
					secTmp.setSecretGrade(security.getSecretGrade());
					secTmp.setDescription(security.getDescription());
					secTmp.setModifyTime(security.getModifyTime() );
					secTmp.setModifier(security.getModifier());
					secTmp.setUserEntity(userEntity);
					impl.saveOrUpdate(secTmp);
				}
				return true;
			}
		});
	}

	public MachSecurity selectMachSecurity(final String id) {
		return (MachSecurity)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				String hsql = "from MachSecurity ms where ms.id = :id";
				return impl.findEntity(hsql, "id", id);
			}
		});
	}
	
	public MachSecurity selectMachSecurityByIP(final String ip) {
		return (MachSecurity)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				String hsql = "from MachSecurity ms where ms.ipAddress = :ip";
				return impl.findEntity(hsql, "ip", ip);
			}
		});
	}
	
	public List getMachSecurityList() {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				return impl.loadAll();
			}
		});
	}

	public int getMachSecurityTolal() {
		int res = 0;
		String sql = "SELECT COUNT(*) FROM PLMACHSECURITY S";
//		String sql = "SELECT COUNT(*) FROM PLROLE R WHERE R.PLTYPE in ('1','2')";
		res = (Integer)new JDBCTemplate().run(new JDBCCallback(
				new HibernateTemplate().getSessionConnection(), 
				sql, JDBCRunType.SQL, 0, false, false,new Object[]{}) {
			@Override
			public Object execute(ResultSet rst) throws SQLException {
				int ress = 0;
				while(rst.next()){
					ress = rst.getInt(1);
					
				}
				return ress;
			}
		});
		return res;
	}
	

	public int getMachSecurityTolal(final String name, final String ipAddress, final int security) {
		int res = 0;
		
		StringBuffer sbSQL = new StringBuffer();

		sbSQL.append(" select COUNT(*) from PLMACHSECURITY m ");
		
		if (security > 0) {
			sbSQL.append(" where m.PLSECRETGRADE= ").append(security);
		} else {
			sbSQL.append(" where 1=1 ");
		}

		if (!"".equals(name)) {
			sbSQL.append(" and m.PLNAME like '%" + name + "%' ");
		}
		
		if (!"".equals(ipAddress)) {
			sbSQL.append(" and m.IPADDRESS  like '%" + ipAddress + "%' ");
		}
		
		res = (Integer)new JDBCTemplate().run(new JDBCCallback(
				new HibernateTemplate().getSessionConnection(), 
				sbSQL.toString(), JDBCRunType.SQL, 0, false, false,new Object[]{}) {
			@Override
			public Object execute(ResultSet rst) throws SQLException {
				int ress = 0;
				while(rst.next()){
					ress = rst.getInt(1);
					
				}
				return ress;
			}
		});
		return res;
	}

	
//	public int getMachSecurityTolal() {
//		return (Integer)new HibernateTemplate().run(new HibernateCallback() {
//			
//			public Object execute() throws HibernateException {
//				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
//				return impl.getCount();
//			}
//		});
//	}

	public List fetchMachSecurityByPage(final int pageNo, final int pageSize) {
		return (List)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				Order order = Order.asc("ipAddress");//按IP排序
				return impl.findList(pageNo, pageSize, order);
			}
		});
	}

	public List fetchMachSecurityByConditionPage(final String name, final String ipAddress, final int security, final int pageNo,
			final int pageSize) {
		return (List)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				MachSecurityDAOImpl impl = new MachSecurityDAOImpl();
				
				int V_PAGE_ROWNUM_START = (pageNo - 1) * pageSize;
				int V_PAGE_ROWNUM_END = (pageNo) * pageSize;

				StringBuffer sb = new StringBuffer();
				sb.append(" SELECT * ");
				sb.append("   FROM (SELECT ROW_.*, ROWNUM RN ");
				sb.append("      FROM (");

				if (security > 0) {
					sb.append(" select m.* from PLMACHSECURITY m where m.PLSECRETGRADE= ").append(security);
				} else {
					sb.append(" select m.* from PLMACHSECURITY m where 1=1 ");
				}

				if (!"".equals(name)) {
					sb.append(" and m.PLNAME like '%" + name + "%' ");
				}
				if (!"".equals(ipAddress)) {
					sb.append(" and m.IPADDRESS  like '%" + ipAddress + "%' ");
				}
				
				sb.append(" order by m.IPADDRESS ");

				sb.append("  )ROW_)");
				sb.append("   WHERE RN <= ");
				sb.append(V_PAGE_ROWNUM_END);
				sb.append(" AND RN > ");
				sb.append(V_PAGE_ROWNUM_START);

				Object[] values = new Object[0];

				List<MachSecurity> list = impl.findEntites(sb.toString(), values, "u", MachSecurity.class);
				return list;
			}
		});
	}
}