package com.nsi.persistence;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.nsi.control.exceptions.NsiEventException;

public abstract class DataSrcImpl implements IsqlDataSource
{
	/**
	 * @see com.nsi.persistence.IsqlDataSource#closeConn(java.sql.Connection)
	 */
	public void closeConn(Connection conn) throws NsiEventException
	{
	   try
	   {
		   if ( conn != null && !conn.isClosed() )
		   {
		   	conn.close();
		   }
	   }
	   catch( SQLException se ) 
	   {
			throw new NsiEventException( "try to  close connection caught SQLException: ", se );
		} 
	}
	/**
	 * @see com.nsi.persistence.IsqlDataSource#executeRetrieve(java.sql.Connection, java.lang.String)
	 */
	public List<Map<String, String>> executeRetrieve(Connection conn, String sSql) throws Exception, SQLException
	{
		List<Map<String, String>> outputRows = new ArrayList<Map<String, String>>();
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement( sSql ) ;
			ResultSet result = stmt.executeQuery();
			outputRows = extractFromResultSet(result);
		}
		catch(SQLException eSQL)
		{
			if(stmt != null)
			{
				stmt.close();
			}
			throw eSQL;
		}
		return outputRows;
	}
	public Map<String, String> retrieveSingleRow(Connection conn, String sSql) throws Exception, SQLException
	{
		Map<String, String> resultMap = new HashMap<String, String>();
		List<Map<String, String>> outputRows = executeRetrieve(conn,sSql);
		if( ( outputRows != null ) && ( outputRows.size() > 0) )
		{
			resultMap = outputRows.get(0);
		}
		return resultMap;
	}
	/**
	 * @see com.nsi.persistence.IsqlDataSource#executeSQLstmt(java.sql.Statement, java.lang.String)
	 */
	public List<Map<String, String>> executeSQLstmt(Statement stmt, String sSql) throws Exception, SQLException
	{
		List<Map<String, String>> outputRows = new ArrayList<Map<String, String>>();
		try
		{
			ResultSet result = stmt.executeQuery(sSql);
			outputRows = extractFromResultSet(result);
			result.close();
			stmt.close();
		}
		catch(SQLException eSQL)
		{
			if(stmt != null)
			{
				stmt.close();
			}
			throw eSQL;
		}
		return outputRows;
	}
	/**
	 * @see com.nsi.persistence.IsqlDataSource#executeStoredProc(java.sql.CallableStatement)
	 */
	public List<Map<String, String>> executeStoredProc(CallableStatement cstmt) throws Exception, SQLException
	{
		List<Map<String, String>> outputRows = new ArrayList<Map<String, String>>();
		short retryCount = 0;
		try
		{
			while (retryCount < 3)
			{
				try
				{
					cstmt.execute();
					retryCount = 3;
				}
				catch(SQLException eSQL)
				{
					++retryCount;
					if(retryCount == 3)
					{
						throw eSQL;
					}
				}
			}
			try
			{
				ResultSet result = (ResultSet)cstmt.getObject(1);
				outputRows = extractFromResultSet(result);
				result.close();
				cstmt.close();
			}
			catch(SQLException eSQL)
			{
				throw eSQL;
			}
		}
		catch(Exception e)
		{
			throw e;
		}
		return outputRows;
	}
	/**
	 * @see com.nsi.persistence.IsqlDataSource#executeUpdate(java.sql.Statement, java.lang.String)
	 */
	public int executeUpdate(Statement stmt, String sSql) throws Exception, SQLException
	{
		int resultCount = 0;
		try
		{
			resultCount = stmt.executeUpdate(sSql);
			if(resultCount == 1)
			{
				stmt.executeUpdate("COMMIT");
			}
			stmt.close();
		}
		catch(SQLException eSQL)
		{
			if(stmt != null)
			{
				stmt.close();
			}
			throw eSQL;
		}
		return resultCount;
	}
	protected List<Map<String, String>> extractFromResultSet(ResultSet result) throws Exception, SQLException
	{
		List<Map<String, String>> finalResult = new ArrayList<Map<String, String>>();
		int columnCnt = 0;
		try
		{
			columnCnt = result.getMetaData().getColumnCount();
			while (result.next())
			{
				Map<String, String> hashRecord = new HashMap<String, String>();
				for (int indexColumn = 1; indexColumn <= columnCnt; indexColumn++)
				{
					hashRecord.put((result.getMetaData().getColumnName(indexColumn)).toLowerCase().trim(), result.getString(indexColumn));
				}
				finalResult.add(hashRecord);
			}
		}
		catch(SQLException eSQL)
		{
			throw eSQL;
		}
		return finalResult;
	}
}