uas-eis/eis-wms/src/main/java/com/uas/eiswms/dao/BaseDao.java

package com.uas.eiswms.dao;





import com.uas.eiswms.model.SqlRowList;
import com.uas.eiswms.util.BaseUtil;

import com.uas.eiswms.util.Constant;
import com.uas.eiswms.util.DateUtil;
import com.uas.eiswms.util.MasterManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

@Repository("baseDao")
public class BaseDao {


	static final String CREATE_SEQ = "CREATE SEQUENCE ?" + // 创建序列
			" MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 3000 CACHE 20 NOORDER NOCYCLE ";

	private JdbcTemplate jdbcTemplate;

	private String onLineMaster;

	private Logger logger = LoggerFactory.getLogger(BaseDao.class);

	public synchronized void setJdbcTemplate(String master){
		logger.info("切换到账套:" + master);
		DataSource dataSource = MasterManager.getDataSource(master);
		if (dataSource == null){
			BaseUtil.showError("数据源不存在");
		}
		if (this.jdbcTemplate == null) {
			this.jdbcTemplate = new JdbcTemplate(dataSource);
			this.onLineMaster = master;
		} else {
			if (this.onLineMaster != null && this.onLineMaster.equals(master)){
				return;
			}
			logger.info("进行切换数据源...");
			this.onLineMaster = master;
			this.jdbcTemplate.setDataSource(dataSource);
		}
	}

	/*public DataSource getDataSource(String master) {
		DruidDataSource dataSource = new DruidDataSource();
		System.out.println(env.getProperty("spring.datasource."+master+".url"));
		System.out.println(env.getProperty("spring.datasource."+master+".username"));
		System.out.println(env.getProperty("spring.datasource."+master+".password"));
		System.out.println(env.getProperty("spring.datasource."+master+".driver-class-name"));
		dataSource.setUrl(env.getProperty("spring.datasource."+master+".url"));
		dataSource.setUsername(env.getProperty("spring.datasource."+master+".username"));//用户名
		dataSource.setPassword(env.getProperty("spring.datasource."+master+".password"));//密码
		dataSource.setDriverClassName(env.getProperty("spring.datasource."+master+".driver-class-name"));
		dataSource.setInitialSize(2);//初始化时建立物理连接的个数
		dataSource.setMaxActive(20);//最大连接池数量
		dataSource.setMinIdle(0);//最小连接池数量
		dataSource.setMaxWait(60000);//获取连接时最大等待时间，单位毫秒。
		dataSource.setValidationQuery("SELECT 1 FROM DUAL");//用来检测连接是否有效的sql
		dataSource.setTestOnBorrow(false);//申请连接时执行validationQuery检测连接是否有效
		dataSource.setTestWhileIdle(true);//建议配置为true，不影响性能，并且保证安全性。
		dataSource.setPoolPreparedStatements(false);//是否缓存preparedStatement，也就是PSCache
		dataSource.setDefaultAutoCommit(true);
		return dataSource;
	}*/

	/**
	 * 查询结果集
	 *
	 * @param sql
	 *            查询语句
	 */
	public int getCount(String sql, Object...args) {
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, args);
		if (rowSet.next()) {
			return rowSet.getInt(1);
		}
		return 0;
	}

	public void execute(List<String> sqls) {
		if (sqls.size() > 0) {
			StringBuffer sb = new StringBuffer("begin ");
			for (String sql : sqls) {
				sb.append("execute immediate '").append(sql.replace("'", "''")).append("';");
			}
			sb.append("end;");
			jdbcTemplate.execute(sb.toString());
		}
	}

	public void execute(String sql) {
		jdbcTemplate.execute(sql);
	}

	public String callProcedure(final String procedureName, final Object...args) {
		try {
			return jdbcTemplate.execute(new CallableStatementCreator() {

				@Override
				public CallableStatement createCallableStatement(Connection connection) throws SQLException {
					StringBuffer storedProcName = new StringBuffer("{call ");
					int i = 0;
					storedProcName.append(procedureName + "(");
					for (i = 0; i < args.length; i++) {
						if (storedProcName.toString().contains("?")) {
							storedProcName.append(",");
						}
						storedProcName.append("?");
					}
					if (storedProcName.toString().contains("?")) {
						storedProcName.append(",");
					}
					storedProcName.append("?");
					storedProcName.append(")}");
					CallableStatement cs = connection.prepareCall(storedProcName.toString());
					for (i = 0; i < args.length; i++) {
						cs.setObject(i + 1, args[i]);
					}
					cs.registerOutParameter(args.length + 1, java.sql.Types.VARCHAR);
					return cs;
				}
			}, new CallableStatementCallback<String>() {
				@Nullable
				@Override
				public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
					cs.execute();
					return cs.getString(args.length + 1);
				}
			});
		}catch (Exception e){
			throw new RuntimeException(e.getMessage());
		}
	}

	public synchronized String sGetMaxNumber(String myTable, int thisType) {
		return callProcedure("Sp_GetMaxNumber", new Object[] { myTable, thisType });
	}

	public int getSeq(String seq) {
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet("select " + seq + ".nextval from dual");
		if (rowSet.next()) {
			return rowSet.getInt(1);
		}
		return 0;
	}

	public Object[] getFieldsDataByCondition(String table,String[] fields, String condition) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append(BaseUtil.parseArray2Str(fields, ","));
		sql.append(" from " + table + " where " + condition);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		Iterator<Map<String, Object>> iter = list.iterator();
		int length = fields.length;
		Object[] results = new Object[length];
		Object value = null;
		if (iter.hasNext()) {
			Map<String, Object> m = iter.next();
			for (int i = 0; i < length; i++) {
				String upperField = fields[i].toUpperCase();
				if (upperField.indexOf(" AS ") > 0) {
					upperField = upperField.split(" AS ")[1].trim();
				}
				value = m.get(upperField);
				if (value != null && value.getClass().getSimpleName().toUpperCase().equals("TIMESTAMP")) {
					Timestamp time = (Timestamp) value;
					try {
						value = BaseUtil.parseDateToString(new Date(time.getTime()), "yyyy-MM-dd HH:mm:ss");
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				results[i] = value;
			}
			return results;
		}
		return null;
	}

	/**
	 * 一个字段，多条结果
	 *
	 * @param tableName
	 *            对应要查询的表
	 * @param field
	 *            要查询的字段
	 * @param condition
	 *            查询条件
	 * @return field对应的数据
	 */
	public List<Object> getFieldDatasByCondition(String tableName, String field, String condition) {
		StringBuffer sb = new StringBuffer("SELECT ");
		sb.append(field);
		sb.append(" FROM ");
		sb.append(tableName);
		sb.append(((condition == null || "".equals(condition)) ? "" : (" WHERE " + condition)));
		SqlRowSet srs = jdbcTemplate.queryForRowSet(sb.toString());
		List<Object> list = new ArrayList<Object>();
		while (srs.next()) {
			list.add(srs.getObject(1));
		}
		return list;
	}

	/**
	 * 一个字段，一条结果
	 *
	 * @param tableName
	 *            对应要查询的表
	 * @param field
	 *            要查询的字段
	 * @param condition
	 *            查询条件
	 * @return field对应的数据
	 */
	public Object getFieldDataByCondition(String tableName, String field, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(field);
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		SqlRowSet srs = jdbcTemplate.queryForRowSet(sql.toString());
		if (srs.next()) {
			return srs.getObject(1);
		} else {
			return null;
		}
	}

	/**
	 * 多个字段，多条结果
	 *
	 * @param tableName
	 *            对应要查询的表
	 * @param fields
	 *            要查询的字段集合
	 * @param condition
	 *            查询条件
	 * @return fields对应的数据
	 */
	public List<Object[]> getFieldsDatasByCondition(String tableName, String[] fields, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(BaseUtil.parseArray2Str(fields, ","));
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		Iterator<Map<String, Object>> iter = list.iterator();
		List<Object[]> datas = new ArrayList<Object[]>();
		Object value = null;
		Map<String, Object> m = null;
		Object[] results = null;
		int length = fields.length;
		while (iter.hasNext()) {
			results = new Object[length];
			m = iter.next();
			for (int i = 0; i < length; i++) {
				value = m.get(fields[i].toUpperCase());
				if (value != null && value.getClass().getSimpleName().toUpperCase().equals("TIMESTAMP")) {
					Timestamp time = (Timestamp) value;
					try {
						value = BaseUtil.parseDateToString(new Date(time.getTime()), "yyyy-MM-dd HH:mm:ss");
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				results[i] = value;
			}
			datas.add(results);
		}
		return datas;
	}

	public synchronized boolean execute(String sql, Object... objs) {
		try {
			jdbcTemplate.update(sql, objs);
			return true;
		} catch (Exception e) {
			BaseUtil.showError(e.getMessage());
			return false;
		}
	}

	public boolean isDBSetting(String caller, String code) {
		Integer i = jdbcTemplate.queryForObject("select count(1) from configs where data =1 and caller='" + caller + "' and code='" + code + "'",
				Integer.class);
		if (i >0) {
			return true;
		} else
			return false;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public boolean isProdIn(String caller) {
		int count = getCount("select count(1) from documentsetup where ds_table='" + caller + "' and ds_inorout in ('IN','-OUT')");
		if (count > 0) {
			return true;
		}else return false;
	}

	public boolean isDBSetting(String inOverNow) {
		return isDBSetting("sys", inOverNow);
	}

	/**
	 * 查询结果集
	 *
	 * @param sql
	 *            查询语句
	 * @param arg
	 *            参数
	 */
	public SqlRowList queryForRowSet(String sql, Object arg) {
		SqlRowList rs = new SqlRowList();
		rs.setResultList(getJdbcTemplate().queryForList(sql, arg));
		return rs;
	}


	/**
	 * 查询结果集
	 *
	 * @param sql
	 *            查询语句
	 */
	public SqlRowList queryForRowSet(String sql) {
		SqlRowList rs = new SqlRowList();
		rs.setResultList(getJdbcTemplate().queryForList(sql));
		return rs;
	}

	public Object getBatchcode(String caller) {
		if (isProdIn(caller)) {
			return callProcedure("utl_rseq", "BatchCode", "/yyMMdd/5");
		}
		return null;

	}

	public String getDBSetting(String caller, String code) {
		Object data = getFieldDataByCondition("configs", "data", "caller='" + caller + "' and code='" + code + "'");
		if (data != null) {
			return data.toString();
		}
		return null;
	}

	public String getDBSetting(String defaultCurrency) {
		return getDBSetting("sys", defaultCurrency);
	}

	public boolean checkIf(String tableName, String condition) {
		int i = 0;
		StringBuffer sql = new StringBuffer("SELECT count(1) FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		SqlRowList srs = queryForRowSet(sql.toString());
		if (srs.next()) {
			 i = srs.getInt(1);
		}
		if (i > 0)return true;
		else return false;
	}

	public double getSummaryByField(String summaryField, String tablename, String condition) {
		SqlRowList rs = queryForRowSet("SELECT sum(nvl(" + summaryField + ",0)) FROM " + tablename + " WHERE " + condition);
		if (rs.next()) {
			return rs.getGeneralDouble(1, 6);
		}
		return 0.0;
	}

	/**
	 * 根据物料中的有效期天数更新有效期止
	 */
	public void getEndDate(String caller, Object id) {
		SqlRowList rs = queryForRowSet("select ds_inorout from DOCUMENTSETUP where ds_table=?", caller);
		if (rs.next()) {
			String Code = rs.getObject("ds_inorout").toString();
			if (Code.equals("IN") || Code.equals("-OUT")) {
				execute("update prodiodetail set pd_replydate=pd_prodmadedate + (select nvl(pr_validdays,0) from product where  pd_prodcode=pr_code) where pd_piid=? and pd_prodmadedate is not null",
						id);
			}
		}
	}

	/**
	 * 查询结果集
	 *
	 * @param sql
	 *            查询语句
	 * @param args
	 *            参数
	 */
	public SqlRowList queryForRowSet(String sql, Object... args) {
		SqlRowList rs = new SqlRowList();
		rs.setResultList(getJdbcTemplate().queryForList(sql, args));
		return rs;
	}

	public void updateByCondition(String tableName, String update, String condition) {
		StringBuffer sb = new StringBuffer("UPDATE ");
		sb.append(tableName);
		sb.append(" SET ");
		sb.append(update);
		sb.append(" WHERE ");
		sb.append(condition);
		execute(sb.toString());
	}

		/**
		 * 调用存储过程 无返回值
		 *
		 * @param procedureName
		 *            存储过程名称
		 * @param args
		 *            参数
		 */
		public void procedure(String procedureName, Object[] args) {
			StringBuffer sql = new StringBuffer("{call ").append(procedureName).append("(");
			for (int i = 0; i < args.length; i++) {
				if (i > 0) {
					sql.append(",");
				}
				sql.append("?");
			}
			sql.append(")}");
			getJdbcTemplate().update(sql.toString(), args);
		}

	public boolean isOut(String caller) {
		int count = getCount("select count(1) from documentsetup where ds_table='" + caller + "' and ds_inorout in ('-IN','OUT')");
		if (count > 0) {
			return true;
		}else return false;
	}

	/**
	 * 修改单据为已审核(包括审核人+审核日期)
	 *
	 * @param tableName
	 * @param condition
	 * @param statusField
	 * @param statusCodeField
	 * @param auditdateField
	 * @param auditorField
	 */
	public void audit(String tableName, String condition, String statusField, String statusCodeField, String auditdateField,
					  String auditorField, String name) {
		updateByCondition(tableName, statusCodeField + "='AUDITED'," + statusField + "='已审核',"
				+ auditdateField + "=" + DateUtil.parseDateToOracleString(Constant.YMD_HMS, new Date()) + "," + auditorField + "='"
				+name+"'", condition);
	}

	public int getSeqId(String seq) {
		try {
		/*	if ("true".equals(BaseUtil.getXmlSetting("group"))) {
				boolean isBase = checkIf("basedataset", "upper(bds_sequence)=upper('" + seq + "') and nvl(bds_editable,0)=1");
				if (isBase) {
					// 集团版基础资料(客户，供应商...)统一到资料中心取号
					String dataCenter = BaseUtil.getXmlSetting("dataSob");
					seq = dataCenter + "." + seq;
				}
			}*/
			String sql = "select " + seq + ".nextval from dual";
			SqlRowList rs = queryForRowSet(sql);
			if (rs.next()) {
				return rs.getInt(1);
			} else {// 如果不存在就创建序列
				int count = getCountByCondition("user_sequences", "Sequence_Name='" + seq.toUpperCase() + "'");
				if (count == 0)
					getJdbcTemplate().execute(CREATE_SEQ.replace("?", seq));
				return getSeqId(seq);
			}
		} catch (Exception e) {
			int count = getCountByCondition("user_sequences", "Sequence_Name='" + seq.toUpperCase() + "'");
			if (count == 0)
				getJdbcTemplate().execute(CREATE_SEQ.replace("?", seq));
			return getSeqId(seq);
		}
	}

	/**
	 * @param tableName
	 *            对应要查询的表
	 * @param condition
	 *            查询条件
	 * @return Count
	 */
	public int getCountByCondition(String tableName, String condition) {
		StringBuffer sql = new StringBuffer("SELECT count(1) FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		SqlRowList srs = queryForRowSet(sql.toString());
		if (srs.next()) {
			return srs.getInt(1);
		} else {
			try {
				throw new Exception("Condition:" + condition + " is wrong!");
			} catch (Exception e) {
				return -1;
			}
		}
	}
}