package com.landray.kmss.km.coustomtable.dao.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

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

import com.landray.kmss.common.dao.HQLInfo;
import com.landray.kmss.common.model.IBaseModel;
import com.landray.kmss.km.coustomtable.dao.IKmCustbDataOperaDao;
import com.landray.kmss.km.coustomtable.model.KmCustbData;
import com.landray.kmss.km.coustomtable.model.KmCustbTb;
import com.landray.kmss.km.coustomtable.util.Constants;
import com.landray.kmss.util.DateUtil;
import com.sunbor.web.tag.Page;

/**
 * 创建日期 2009-五月-11
 * 
 * @author 罗小军 自定义表维护
 */
public class KmCustbDataOperaDaoImp implements IKmCustbDataOperaDao {
    private DataSource dataSource;
    private String dbType;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public String getDbType() {
        return dbType;
    }

    public void setDbType(String dbType) {
        this.dbType = dbType;
    }

    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public Long add(IBaseModel modelObj) throws Exception {
        Connection connection = getConnection();
        KmCustbTb kmCustbTb = null;
        if (modelObj instanceof KmCustbTb) {
            kmCustbTb = (KmCustbTb) modelObj;
            StringBuffer addStr = new StringBuffer();
            StringBuffer addValueStr = new StringBuffer();
            addStr.append("insert into ");
            addStr.append(kmCustbTb.getFdTableName());
            addStr.append(" ( ");
            addValueStr.append(" ) values ( ");
            // 判断数据库
            if (Constants.ORACLE.equalsIgnoreCase(dbType)) {
                // 暂未对主键生成方式(getPkType)做判断，默认为自动增长
                addStr.append(kmCustbTb.getFdPkName());
                addStr.append(",");
                addValueStr.append("hibernate_sequence.nextval,");
            }
            List dataList = kmCustbTb.getCustbDatas();
            for (int i = 0; i < dataList.size(); i++) {
                KmCustbData tempData = (KmCustbData) dataList.get(i);
                addStr.append(tempData.getFdCloumnName());
                if (Constants.DATE.equalsIgnoreCase(tempData.getFdDataType())
                        && Constants.ORACLE.equalsIgnoreCase(dbType)) {

                    addValueStr.append("to_date('");
                    addValueStr.append(tempData.getFdValue());
                    addValueStr.append("','yyyy-mm-dd hh24:mi:ss')");
                    continue;

                } else {
                    addValueStr.append("'");
                    addValueStr.append(tempData.getFdValue());
                    addValueStr.append("'");
                }
                if (i + 1 < dataList.size()) {
                    addStr.append(",");
                    addValueStr.append(",");
                }
            }
            addValueStr.append(")");
            addStr.append(addValueStr);
            logger.debug("insert statement : " + addStr);
            Statement statement = connection.createStatement();
            statement.execute(addStr.toString());
            close(connection, statement, null);
        }
        return kmCustbTb.getFdId();
    }

    public Page findPage(HQLInfo hqlInfo, IBaseModel model) throws Exception {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        StringBuffer selectStr = new StringBuffer();
        KmCustbTb kmCustbTb = null;
        if (model instanceof KmCustbTb) {
            kmCustbTb = (KmCustbTb) model;
            if (Constants.ORACLE.equalsIgnoreCase(dbType)) {
                selectStr
                        .append("select * from (select a.*,rownum rn from (select * from ");
                selectStr.append(kmCustbTb.getFdTableName());
                selectStr.append(" order by ");
                selectStr.append(kmCustbTb.getFdPkName());
                selectStr.append(" ) a where rownum<=");
                selectStr.append(hqlInfo.getPageNo() * hqlInfo.getRowSize());
                selectStr.append(") where rn>");
                selectStr.append((hqlInfo.getPageNo() - 1)
                        * hqlInfo.getRowSize());
                selectStr.append(" order by ");

            } else {

                selectStr.append("select top ");
                selectStr.append(hqlInfo.getRowSize());
                selectStr.append(" * from ");
                selectStr.append(kmCustbTb.getFdTableName());
                selectStr.append(" where ( ");
                selectStr.append(kmCustbTb.getFdPkName());
                selectStr.append(" not in ( select top ");
                selectStr.append((hqlInfo.getPageNo() - 1)
                        * hqlInfo.getRowSize());
                selectStr.append(" ");
                selectStr.append(kmCustbTb.getFdPkName());
                selectStr.append(" from ");
                selectStr.append(kmCustbTb.getFdTableName());
                selectStr.append(" order by ");
                selectStr.append(kmCustbTb.getFdPkName());
                selectStr.append(" )) order by ");
            }
            if (hqlInfo != null && hqlInfo.getOrderBy() != null) {
                selectStr.append(hqlInfo.getOrderBy());
            } else {
                selectStr.append(kmCustbTb.getFdPkName());
            }

            if (hqlInfo != null
                    && Constants.DOWN.equalsIgnoreCase(hqlInfo.getOrderBy())) {
                selectStr.append(" desc ");
            }
            logger.debug("select statement = " + selectStr);
        }
        // 查询得到ResultSet对象
        resultSet = statement.executeQuery(selectStr.toString());
        KmCustbTb kmCustbTbValue = new KmCustbTb();
        List valueList = new ArrayList();
        while (resultSet.next()) {
            // clone kmCustbTb对象
            KmCustbTb tempKmCustbTb = (KmCustbTb) kmCustbTb.clone();
            List dataList = tempKmCustbTb.getCustbDatas();
            // 给主键赋值
            tempKmCustbTb.setFdPkValue(resultSet.getLong(tempKmCustbTb
                    .getFdPkName()));
            // 通过循环把值set到custbData中
            for (int j = 0; j < dataList.size(); j++) {
                KmCustbData custbData = (KmCustbData) dataList.get(j);
                if (Constants.DATE.equals(custbData.getFdDataType())) {
                    custbData.setFdValue(DateUtil.convertDateToString(DateUtil
                            .convertStringToDate(resultSet.getString(custbData
                                    .getFdCloumnName()), "yyyy-MM-dd"),
                            "yyyy-MM-dd"));
                } else {
                    custbData.setFdValue(resultSet.getString(custbData
                            .getFdCloumnName()));
                }
            }
            valueList.add(tempKmCustbTb);
        }
        // 测试数据
        Page page = new Page();
        page.setList(valueList);
        close(connection, statement, resultSet);
        return page;
    }

    // select count(id) from tableName
    // 获取某个表中的数据数量
    public Long getCount(String tableName, String id) throws Exception {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        StringBuffer selectCount = new StringBuffer("select count(");
        selectCount.append(id);
        selectCount.append(") from ");
        selectCount.append(tableName);
        ResultSet resultSet = statement.executeQuery(selectCount.toString());
        if (resultSet != null) {
            resultSet.next();
            return resultSet.getLong(1);
        }
        close(connection, statement, resultSet);
        return null;
    }

    public void close(Connection connection, Statement statement,
            ResultSet resultSet) throws SQLException {
        if (resultSet != null) {
            resultSet.close();
            resultSet = null;
        }
        if (statement != null) {
            statement.close();
            statement = null;
        }
        if (connection != null) {
            connection.close();
            connection = null;
        }
    }
}