package com.ejie.aa21b.dao;

import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.aa21b.dao.ProcesoPidDao;
import com.ejie.aa21b.model.Aa21b23s01;

@Repository
@Transactional
public class ProcesoPidDaoImpl implements ProcesoPidDao {
	private JdbcTemplate jdbcTemplate;
	private static final Logger logger = LoggerFactory
			.getLogger(ProcesoPidDaoImpl.class);
	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Aa21b23s01> rwMap = new RowMapper<Aa21b23s01>() {
		public Aa21b23s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b23s01 res = new Aa21b23s01();
			res.setId(resultSet.getInt("ndocu23"));
			res.setCodPid(resultSet.getString("CODPID23"));
			return res;
		}
	};
	private RowMapper<Aa21b23s01> rwMap2 = new RowMapper<Aa21b23s01>() {
		public Aa21b23s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b23s01 res = new Aa21b23s01();
			Blob blob = resultSet.getBlob("texto23");
			byte[] array = blob.getBytes(1, (int) blob.length());
			res.setId(resultSet.getInt("ndocu23"));
			res.setExtension(resultSet.getString("EXTDOCU01"));
			res.setDoc(array);
			return res;
		}
	};

	/**
	 * Method use to set the datasource.
	 * 
	 * @param dataSource
	 *            DataSource
	 * @return
	 */
	@Resource
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	@Transactional(readOnly = true)
	public ArrayList<Aa21b23s01> obtenerLista() {
		// TODO Auto-generated method stub
		ProcesoPidDaoImpl.logger.info("Método obtenerLista");

		String query = "Select ndocu23,CODPID23 from  aa21b23t00 order by CODPID23,ndocu23";

		ProcesoPidDaoImpl.logger.info("SQL = " + query);

		return (ArrayList<Aa21b23s01>) this.jdbcTemplate.query(query,
				this.rwMap);
	}

	@Override
	public Aa21b23s01 obtenerDocumento(int id) {
		// TODO Auto-generated method stub
		ProcesoPidDaoImpl.logger.info("Método obtenerLista");

		String query = "select aa21b01t00.EXTDOCU01,ndocu23,CODPID23,texto23 from aa21b01t00 inner join aa21b23t00 on ndocu01=ndocu23 order by CODPID23";
		query = "select case when aa21b01t00.EXTDOCU01 is not null then EXTDOCU01 else 'pdf' end as EXTDOCU01,ndocu23,CODPID23,texto23 from aa21b01t00 right join aa21b23t00 on ndocu01=ndocu23 where ndocu23=? ";
		ProcesoPidDaoImpl.logger.info("SQL = " + query);

		return ((ArrayList<Aa21b23s01>) this.jdbcTemplate.query(query,
				this.rwMap2, id)).get(0);
	}

	@Override
	public Aa21b23s01 guardarCodPid(Aa21b23s01 entrada) {
		// TODO Auto-generated method stub
		ProcesoPidDaoImpl.logger.info("Método guardarCodPid");

		String query = "UPDATE aa21b23t00 SET CODPID23=? WHERE ndocu23=? ";

		ProcesoPidDaoImpl.logger.info("SQL = " + query);

		ProcesoPidDaoImpl.logger.info("PARAM1=" + entrada.getCodPid());
		ProcesoPidDaoImpl.logger.info("PARAM2pk=" + entrada.getId());

		this.jdbcTemplate.update(query, entrada.getCodPid(), entrada.getId());
		return entrada;
	}

}
