package com.scitel.gdnumcommon.utils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.scitel.gdnumcommon.entity.Pagination;
import com.scitel.gdnumcommon.entity.BaseVO;


public class BaseDAO {
 private static final Log log = LogFactory.getLog(BaseDAO.class);

 /**
  * 保存数据,新建和修改都用这个
  *
  * @param con
  * @param SQL
  * @param params
  * @throws Exception
  * @author
  */
 public void save(Connection con, String SQL, List params)
   throws Exception {
  PreparedStatement ps = null;
  try {
   ps = con.prepareStatement(SQL);
   if (SQL == null) {
    throw new Exception();
   }

   if (params != null && params.size() > 0) {
    int count = 0;
    for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
     Object object = iterator.next();
     setObjectValue(ps, count + 1, object);
    }
   }
   ps.executeUpdate();
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try{
    if(ps != null) {
     ps.close();
    }
   }catch(Exception e){

   }
  }
 }

 /**
  * 保存数据,新建和修改都用这个,通过字段名称匹配类型
  * @param con
  * @param SQL
  * @param voclass
  * @param paramMap
  * @throws Exception
  * @author
  */
 public void save(Connection con, String SQL, Class voclass, Map paramMap)
   throws Exception {
  PreparedStatement ps = null;
  try {
   ps = con.prepareStatement(SQL);
   if (SQL == null) {
    throw new Exception();
   }

   if (paramMap != null && paramMap.size() > 0) {
    int count = 0;
    for (Iterator iterator = paramMap.keySet().iterator(); iterator.hasNext(); count++) {

     String key = (String)iterator.next();
     Object object = paramMap.get(key);
     setObjectValue(ps, voclass, count+1, key, object);
    }
   }
   ps.executeUpdate();
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try{
    if(ps != null) {
     ps.close();
    }
   }catch(Exception e){

   }
  }
 }

 /**
  * 删除数据
  *
  * @param con
  * @param SQL
  * @param params
  * @throws Exception
  * @author
  */
 public void remove(Connection con, String SQL, List params)
   throws Exception {
  PreparedStatement ps = null;
  try {
   ps = con.prepareStatement(SQL);
   if (SQL == null) {
    throw new Exception();
   }
   if (params != null && params.size() > 0) {
    int count = 0;
    for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
     Object object = iterator.next();
     setObjectValue(ps, count + 1, object);
    }
   }
   ps.executeUpdate();
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try{
    if(ps != null) {
     ps.close();
    }
   }catch(Exception e){

   }
  }
 }

 /**
  * 根据ID选择数据
  *
  * @param con
  * @param SQL
  * @param id
  * @param voclass
  * @return
  * @throws Exception
  * @author
  */
 public BaseVO selectById(Connection con, String SQL, String id,
   Class voclass) throws Exception {
  Object po = null; // 承载值对象
  PreparedStatement ps = null;
  ResultSet rs = null;
  ResultSetMetaData rsm = null;
  try {
   ps = con.prepareStatement(SQL);
   if (SQL == null) {
    throw new Exception();
   }

   ps.setString(1, id);

   rs = ps.executeQuery();
   rsm = rs.getMetaData();
   if (rs.next()) {
    Map entity = new HashMap();
    for (int i = 1; i <= rsm.getColumnCount(); i++) {
     String columnName = rsm.getColumnName(i).toLowerCase();
     Object columnValue = getObjectValue(rs, voclass, i, columnName);
     entity.put(columnName, columnValue);
    }
    if (voclass != null) {
     po = voclass.newInstance();
     BeanUtils.populate(po, entity);
    }
    
   }
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {

   }
   try {
    if (ps != null) {
     ps.close();
    }
   } catch (Exception e) {

   }
  }
  return (BaseVO) po;
 }

 /**
  * 选择记录,不分页
  * @param con
  * @param SQL
  * @param params
  * @param voclass
  * @return
  * @throws Exception
  * @author
  */
 public List select(Connection con, String SQL, List params, Class voclass)
   throws Exception {
  Object vo = null; // 承载值对象
  PreparedStatement ps = null;
  ResultSet rs = null;
  ResultSetMetaData rsm = null;
  List relist = null;
  try {
   ps = con.prepareStatement(SQL);
   if (SQL == null) {
    throw new Exception();
   }
   if (params != null && params.size() > 0) {
    int count = 0;
    for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
     Object object = iterator.next();
     setObjectValue(ps, count + 1, object);
    }
   }
   rs = ps.executeQuery();
   rsm = rs.getMetaData();
   relist = new ArrayList();
   while (rs.next()) {
    Map entity = new HashMap();
    for (int i = 1; i <= rsm.getColumnCount(); i++) {
     String columnName = rsm.getColumnName(i).toLowerCase();
     Object columnValue = getObjectValue(rs, voclass, i, columnName);
     entity.put(columnName, columnValue);
     
    }
    if (voclass != null) {
     vo = voclass.newInstance();
     BeanUtils.populate(vo, entity);
     relist.add(vo);
    } else {
     relist.add(entity);
    }
   }
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {

   }
   try {
    if (ps != null) {
     ps.close();
    }
   } catch (Exception e) {

   }
   
  }
  return relist;
 }

 /**
  * 分页查询
  *
  * @param con
  * @param SQL
  * @param params
  * @param voclass
  * @param pagination
  * @return
  * @throws Exception
  * @author
  */
 public List selectPagination(Connection con, String SQL, List params,
   Class voclass, Pagination pagination) throws Exception {
  if (SQL == null) {
   throw new NullPointerException("SQL不能为空!");
  }
  if (pagination == null) {
   throw new NullPointerException("分页类不能为空!");
  }

  // TODO Auto-generated method stub
  Object vo = null; // 承载值对象
  PreparedStatement ps = null;
  ResultSet rs = null;
  ResultSetMetaData rsm = null;
  List relist = null;
  try {

   ps = con.prepareStatement("select count(1) as count_ from ( " + SQL + " )");

   if (params != null && params.size() > 0) {
    int count = 0;
    for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
     Object object = iterator.next();
     setObjectValue(ps, count + 1, object);
    }
   }

   rs = ps.executeQuery();
   if (rs.next()) {
    pagination.setTotalCount(rs.getInt(1));
    
   }
   

   if (pagination.getTotalCount() > 0) {
    /* 组成分页内容 */
    StringBuffer pagingSelect = new StringBuffer(100);
    pagingSelect
      .append("select * from ( select row_.*, rownum rownum_ from ( ");
    pagingSelect.append(SQL);
    pagingSelect
      .append(" ) row_ where rownum <= ?) where rownum_ > ?");

    ps = con.prepareStatement(pagingSelect.toString());
    int count = 0;
    if (params != null && params.size() > 0) {
     for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
      Object object = iterator.next();
      setObjectValue(ps, count + 1, object);
     }
    }
    
    ps.setInt(count + 1, pagination.getPage()
      * pagination.getCount());
    ps.setInt(count + 2, (pagination.getPage() - 1)
      * pagination.getCount());

    log.info("pagination.getPage():" + pagination.getPage());
    log.info("pagination.getCount():" + pagination.getCount());
    rs = ps.executeQuery();
    rsm = rs.getMetaData();
    relist = new ArrayList();
    while (rs.next()) {
     Map entity = new HashMap();
     for (int i = 1; i <= rsm.getColumnCount(); i++) {
      String columnName = rsm.getColumnName(i).toLowerCase();
      Object columnValue = getObjectValue(rs, voclass, i, columnName);
      entity.put(columnName, columnValue);
     }
     if (voclass != null) {
      vo = voclass.newInstance();
      BeanUtils.populate(vo, entity);
      relist.add(vo);
     } else {
      relist.add(entity);
     }
    }
   }
  } catch (Exception e) {
   log.error(e);
   throw e;
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {

   }
   try {
    if (ps != null) {
     ps.close();
    }
   } catch (Exception e) {

   }

  }
  return relist;
 }

 /**
  * 获得SequenceValue
  * @param sequenceName
  * @return
  * @throws Exception
  * @author
  */
 public Long getSequenceValue(Connection con, String sequenceName)throws Exception {
  PreparedStatement ps = null;
  ResultSet rs = null;
  Long sequenceValue = null;
  try{
   ps = con.prepareStatement("select " + sequenceName + ".nextval from dual");
   rs = ps.executeQuery();
   if(rs.next()) {
    sequenceValue = new Long(rs.getLong(1));
   }
  }catch(Exception e){
   log.error(e);
   throw e;
  }finally{
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {

   }
   try {
    if (ps != null) {
     ps.close();
    }
   } catch (Exception e) {

   }
  }
  return sequenceValue;
 }
 /**
  * 把对象传入数据库
  * @param ps
  * @param count
  * @param object
  * @author
  */
 private final void setObjectValue(PreparedStatement ps, int count, Object object) throws Exception {
  log.debug("count is " + count + " object is " + object);
  if(object != null) {
   if(object instanceof Integer){
    ps.setInt(count, ((Integer)object).intValue());
   }else if(object instanceof Long) {
    ps.setLong(count, ((Long)object).longValue());
   }else if(object instanceof BigDecimal){
    ps.setBigDecimal(count, (BigDecimal)object);
   }else if(object instanceof String){
    ps.setString(count, (String)object);
   }else if(object instanceof java.util.Date) {
    if(object!=null){
     long time = ((java.util.Date)object).getTime();
     ps.setDate(count, new java.sql.Date(time));
    }else{
     ps.setDate(count, null);
    }
   }else{
    ps.setObject(count, object);
   }
  }else{
   ps.setNull(count, Types.INTEGER);
  }
 }

 /**
  * 把对象传入数据库
  * @param ps
  * @param clazz
  * @param count
  * @param columnName
  * @param object
  * @throws Exception
  * @author
  */
 private final void setObjectValue(PreparedStatement ps, Class clazz, int count,
   String columnName, Object object)throws Exception {
  log.debug("count is " + count + " columnName is " + columnName + " object is " + object);
  String classType = clazz.getDeclaredField(columnName).getType().getName();
  if(classType.equals("java.lang.Integer")){
   if(object != null) {
    ps.setInt(count, ((Integer)object).intValue());
   }else{
    ps.setNull(count, Types.INTEGER);
   }
  }else if(classType.equals("java.lang.Long")) {
   if(object != null ) {
    ps.setLong(count, ((Long)object).longValue());
   }else{
    ps.setNull(count, Types.INTEGER);
   }
  }else if(classType.equals("java.math.BigDecimal")){
   if(object != null) {
    ps.setBigDecimal(count, (BigDecimal)object);
   }else{
    ps.setNull(count, Types.NUMERIC);
   }
  }else if(classType.equals("java.lang.String")){
   if(object != null) {
    ps.setString(count, (String)object);
   }else{
    ps.setString(count, null);
   }
  }else if(classType.equals("java.util.Date")) {
   if(object!=null){
    long time = ((java.util.Date)object).getTime();
    ps.setDate(count, new java.sql.Date(time));
   }else{
    ps.setDate(count, null);
   }
  }else{
   ps.setObject(count, object);
  }
 }

 /**
  * 把数据从数据取出来
  * @param rs
  * @param clazz
  * @param count
  * @param columnName
  * @return
  * @throws Exception
  * @author
  */
 private final Object getObjectValue(ResultSet rs, Class clazz, int count, String columnName) throws Exception {
  Object fieldValue = null;
  log.debug("columnName is " + columnName + " count is " + count);
  if(columnName != null) {
   if("rownum".equals(columnName)) {
    fieldValue = new Long(rs.getLong(count));
   }else if("rownum_".equals(columnName)) {
    fieldValue = new Long(rs.getLong(count));
   }else if("count_".equals(columnName)) {
    fieldValue = new Long(rs.getLong(count));
   }else{
    String classType = clazz.getDeclaredField(columnName).getType().getName();

    if(classType.equals("java.lang.Integer")){
     fieldValue =new Integer( rs.getInt(count));
    }else if(classType.equals("java.lang.Long")) {
     fieldValue =new Long( rs.getLong(count));
    }else if(classType.equals("java.math.BigDecimal")){
     fieldValue = rs.getBigDecimal(count);
    }else if(classType.equals("java.lang.String")){
     fieldValue = rs.getString(count);
    }else if(classType.equals("java.util.Date")) {
     java.sql.Date date = rs.getDate(count);
     if(date!= null){
      fieldValue = new java.util.Date(date.getTime());
     }
    }else{
     fieldValue = rs.getString(count);
    }
   }
  }
  return fieldValue;
 }

}