package com.winsun.mapper;


import com.winsun.bean.Page;
import com.winsun.utils.Is;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

/**
 * springjdbc baseDao
 * @author lisilin
 * @since 0.1
 */
public abstract class Basemapper<T>  {


	@Autowired
	protected JdbcTemplate jdbcTemplate;

	public LobHandler lobHandler = new  DefaultLobHandler();

	/**
	 * 适用于更新数据库,insert,update, delete
	 *
	 * @param namedSql:命名参数的ＳＱＬ语句，而且参数的命名必须和ＪａｖａＢｅａｎ中的属性名对应
	 * @param javaBean
	 * @return
	 */

	/**
	 * 带参数更新
	 * @param sql
	 * @param paramValue
	 * @return
	 */
	protected int update(String sql, Object... paramValue) {
		return jdbcTemplate.update(sql, paramValue);
	}

	/**
	 * 批量带参数更新（针对更新SQL一致，参数不一致）
	 * @param sql
	 * @param paramValue
	 * @return
	 */
	protected int[] batchUpdate(String sql, List<Object[]> paramValue) {
		return jdbcTemplate.batchUpdate(sql, paramValue);
	}

	/**
	 * 批量sql更新（针对更新SQL不一致）
	 * @param sql
	 * @return
	 */
	protected int[] batchUpdate(String[] sql) {
		return jdbcTemplate.batchUpdate(sql);
	}



	@SuppressWarnings("unchecked")
	protected Page<?> findDwon (@SuppressWarnings("rawtypes") Page page, String sql, Object[] pars,RowMapper<T> rowMapper) throws Exception{

		String pageQueryString = getMysqlPageSQL(sql);
		String countSql = this.getCountQuerySQL(sql);
		if(null==rowMapper){
			List<Map<String,Object>> list = jdbcTemplate.queryForList(pageQueryString, pars);
			page.setResult(list);
		}else{
			List<T> list = jdbcTemplate.query(pageQueryString, pars, rowMapper);
			page.setResult(list);
		}

		int count = jdbcTemplate.queryForObject(countSql, pars, Integer.class);
		if (count > 0) {
			page.setTotalCount(count);
		} else {
			page.setTotalCount(0);
		}

		return page;
	}

	/**
	 * 构造Mysql数据分页SQL
	 * @param queryString
	 * @param startIndex
	 * @param pageSize
	 * @return
	 */
	protected String getMysqlPageSQL(String queryString, Integer startIndex, Integer pageSize) {
		if (StringUtils.isEmpty(queryString))
		{
			return null;
		}
		startIndex = (startIndex - 1) * pageSize;
		int endIndex = pageSize;
		String endSql = queryString;
		if(endIndex>0){
			endSql += " limit " + startIndex +"," + endIndex;
		}
		return endSql;
	}
	protected String getMysqlPageSQL(String queryString) {
		if (StringUtils.isEmpty(queryString))
		{
			return null;
		}

		return queryString;
	}

	/**
	 * 构造 Oracle数据分页SQL
	 * @param queryString
	 * @param startIndex
	 * @param pageSize
	 * @return
	 */
	protected String getOraclePageSQL(String queryString, Integer startIndex, Integer pageSize)
	{
		if (StringUtils.isEmpty(queryString))
		{
			return null;
		}
		//  String itemSource =
		queryString.toLowerCase();
		startIndex = (startIndex - 1) * pageSize;
		int endIndex = startIndex + pageSize + 1;
		String endSql = "select * from (select rOraclePageSQL.*,ROWNUM as currentRow from (" +
				queryString + ") rOraclePageSQL where rownum <=" + endIndex + ") where currentRow>" + startIndex;
		return endSql;
	}

	/**
	 * 构造统计SQL
	 * @param queryString
	 * @return
	 */
	protected String getCountQuerySQL(String queryString) {
		String sql = "";
		if (StringUtils.isNotEmpty(queryString))
		{
			sql = "select count(*) from (" + queryString + ") xCount";
		}
		return sql;
	}
	/**
	 * 带条件查询,组装并返回sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Map<String,Object> select(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		List<Object> pars = new ArrayList<Object>();
		sf.append("select * from "+table+" where 1=1");
		if(null!=map){
			/*Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(Is.isNoEmpty(map.get(key))){
					sf.append(" and "+key+" like ? ");
					pars.add("%"+map.get(key)+"%");
				}
			}*/
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				if(Is.isNoEmpty(entry.getValue())){
					sf.append(" and "+key+" like ? ");
					pars.add("%"+entry.getValue()+"%");
				}
			}
			/* for (Entry<String, Object> entry : map.entrySet()) {
			       if(!entry.getKey().equals("id")){
						sf.append(entry.getKey()+"=?,");
						pars.add(entry.getValue());
					}
			  }*/
		}
		map.put("sql", sf.toString());
		map.put("pars", pars);
		return map;
	}
	/**
	 * 带条件查询
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> selectList(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		List<Object> pars = new ArrayList<Object>();
		sf.append("select * from "+table+" where 1=1");
		if(null!=map){
		/*	Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(Is.isNoEmpty(map.get(key))){
					sf.append(" and "+key+" like ? ");
					pars.add("%"+map.get(key)+"%");
				}
			}*/

			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				if(Is.isNoEmpty(entry.getValue())){
					sf.append(" and "+key+" like ? ");
					pars.add("%"+entry.getValue()+"%");
				}
			}
		}
		return jdbcTemplate.queryForList(sf.toString(), pars.toArray());
	}

	/**
	 * 带条件查询
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> selectListForEquest(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		List<Object> pars = new ArrayList<Object>();
		sf.append("select * from "+table+" where 1=1");
		if(null!=map){
			/*Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(Is.isNoEmpty(map.get(key))){
					sf.append(" and "+key+" = ? ");
					pars.add(map.get(key));
				}
			}*/
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				if(Is.isNoEmpty(entry.getValue())){
					sf.append(" and "+key+" = ? ");
					pars.add(entry.getValue());
				}
			}
		}

		return jdbcTemplate.queryForList(sf.toString(), pars.toArray());
	}


	/**
	 * 带参数插入
	 * @param map
	 * @throws Exception
	 */
	public void insert(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		map.remove("color");//去除多余的参数
		map.remove("phoneLogo");
		map.remove("rom");
		map.remove("price");
		map.remove("packageName");
		String sql=" values(";
		List<Object> pars = new ArrayList<Object>();
		sf.append("insert into "+table+"(");
		if(null!=map){
			/*Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				sf.append(key+",");
				sql+="?,";
				pars.add(map.get(key)==null?"":map.get(key));
			}*/

			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				sf.append(key+",");
				sql+="?,";
				pars.add(entry.getValue()==null?"":entry.getValue());
			}

			sql=sql.substring(0, sql.length()-1)+")";
			sql=sf.substring(0,sf.length()-1).toString()+")"+sql;
		}
		jdbcTemplate.update(sql, pars.toArray());
	}

	/**
	 * 带参数插入
	 * @param table
	 * @param list
	 * @param pars
	 * @throws Exception
	 */
	public void insert(String table,List<Object> list,List<Object[]> pars)throws Exception{
		StringBuffer sf = new StringBuffer();
		String sql=" values(";
		sf.append("insert into "+table+"(");
		if(null!=list){
			for(int i=0;i<list.size();i++){
				sf.append(list.get(i)+",");
				sql+="?,";
			}
			sql=sql.substring(0, sql.length()-1)+")";
			sql=sf.substring(0,sf.length()-1).toString()+")"+sql;
		}
		jdbcTemplate.batchUpdate(sql, pars);
	}
	/**
	 * 修改
	 * @param map
	 * @throws Exception
	 */
	public int update(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		String sql="";
		List<Object> pars = new ArrayList<Object>();
		sf.append("update "+table+" set ");
		if(null!=map){
		/*	Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(!key.equals("id")){
					sf.append(key+"=?,");
					pars.add(map.get(key));
				}

			}*/
			for (Entry<String, Object> entry : map.entrySet()) {
				if(!entry.getKey().equals("id")){
					sf.append(entry.getKey()+"=?,");
					pars.add(entry.getValue());
				}
			}
			pars.add(map.get("id"));
			sql = sf.substring(0, sf.length()-1)+" where id=?";
		}
		return jdbcTemplate.update(sql, pars.toArray());
	}
	public int updateOrderView(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		String sql="";
		List<Object> pars = new ArrayList<Object>();
		sf.append("update "+table+" set ");
		if(null!=map){
			for (Entry<String, Object> entry : map.entrySet()) {
				if(!entry.getKey().equals("order_id")){
					sf.append(entry.getKey()+"=?,");
					pars.add(entry.getValue());
				}
			}
			pars.add(map.get("order_id"));
			sql = sf.substring(0, sf.length()-1)+" where order_id=?";
		}
		return jdbcTemplate.update(sql, pars.toArray());
	}


	/**
	 * 修改
	 * @throws Exception
	 */
	public void updateBatch(String sql,List<Object[]> pars)throws Exception{
		jdbcTemplate.batchUpdate(sql, pars);
	}
	/**
	 * 带参数修改，自带where条件
	 * @param table
	 * @param map
	 * @param id
	 * @throws Exception
	 */
	public void update(String table,Map<String,Object> map,String id)throws Exception{
		StringBuffer sf = new StringBuffer();
		String sql="";
		List<Object> pars = new ArrayList<Object>();
		sf.append("update "+table+" set ");
		if(null!=map){
			/*Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(!key.equals(id)){
					sf.append(key+"=?,");
					pars.add(map.get(key));
				}

			}*/
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				if(!key.equals("id")){
					sf.append(key+"=?,");
					pars.add(entry.getValue());
				}
			}
			pars.add(map.get(id));
			sql = sf.substring(0, sf.length()-1)+" where "+id+"=?";
		}
		jdbcTemplate.update(sql, pars.toArray());
	}

	public int updatebyId(String table,Map<String,Object> map,String id)throws Exception{
		StringBuffer sf = new StringBuffer();
		String sql="";
		List<Object> pars = new ArrayList<Object>();
		sf.append("update "+table+" set ");
		if(null!=map){
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				String value = entry.getValue().toString();
				sf.append(key+"=");
				sf.append("'");
				sf.append(value);
				sf.append("'");
				sf.append(",");
			}
			pars.add(id);
			sql = sf.substring(0, sf.length()-1)+" where id='"+id+"'";
		}
		return jdbcTemplate.update(sql);

	}

	public void delete(String table,String id)throws Exception{
		String sql = "delete from "+table+" where id=?";
		if(StringUtils.isNotBlank(id)){
			jdbcTemplate.update(sql, id);
		}
	}


	public void delete(String table,Map<String,Object> map)throws Exception{
		StringBuffer sf = new StringBuffer();
		List<Object> pars = new ArrayList<Object>();
		sf.append("delete from "+table+" where 1=1 ");
		if(null!=map){
			/*Set<String> set = map.keySet();
			Iterator<String> iter = set.iterator();
			while(iter.hasNext()){
				String key = (String)iter.next();
				if(Is.isNoEmpty(map.get(key))){
					sf.append(" and "+key+" = ? ");
					pars.add(map.get(key));
				}
			}*/
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				if(Is.isNoEmpty(entry.getValue())){
					sf.append(" and "+key+" = ? ");
					pars.add(entry.getValue());
				}
			}
		}
		jdbcTemplate.update(sf.toString(), pars.toArray());
	}


	public void createField(String table,List<Map<String,Object>> fieldName){
		String sql = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = DATABASE()  AND table_name = '"+table+"' ";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		StringBuffer sb = new StringBuffer();
		sb.append("alter table "+table);
		int i=0;
		for(i=0;i<fieldName.size();i++){
			boolean falg = true;
			for(int j=0;j<list.size();j++){
				if(fieldName.get(i).get("fieldEngName").equals(list.get(j).get("COLUMN_NAME"))) falg = false;
			}

			if(falg){
				sb.append(" add "+fieldName.get(i).get("fieldEngName")+"  VARCHAR(100)  DEFAULT NULL COMMENT '"+fieldName.get(i).get("fieldName")+"'");
				if(i<fieldName.size()-1){
					sb.append(",");
				}
			}
		}
		jdbcTemplate.update(sb.toString());
	}
}