package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;

import bean.AppVariables;
import bean.FileBean;
import bean.query.ServiceUIBean;
import common.bean.UserData;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.ServiceUI;

@Repository
public class ServiceUIDAO extends GenericDAO<ServiceUI, Integer> implements IDAO<ServiceUI, Integer> {

	public Integer count(UserData userdata, AppVariables appVariables){

		Integer res=0;
		boolean tx = startTransaction();
		try {
			String sql = generateSQL(userdata.getUserId(), true);
			
			Query q = currentSession().createSQLQuery(sql);
			res = (Integer) q.uniqueResult();
			
		} finally {
			if (tx)
				commit();
		}
		
		return res;
	}
	
	public List<ServiceUI> list(UserData userdata, AppVariables appVariables){
	
		List<ServiceUI> res;
		
		boolean tx = startTransaction();
		try {
			String sql = generateSQL(userdata.getUserId(), false);
			
			Query q = currentSession().createSQLQuery(sql);
			q.setResultTransformer(new AliasToBeanResultTransformer(ServiceUIBean.class));
			res = q.list();
			
		} finally {
			if (tx)
				commit();
		}
		
		return res;
	}
	
	public FileBean  getFile(int idServiceUI) {
		
		FileBean res = null;
		
		ServiceUI serviceUI = read(idServiceUI);
		
		if (serviceUI!=null && serviceUI.getFileOut()!=null) {
			res = new FileBean();
			try {
				res.setBytes(serviceUI.getFileOut().getBytes(1, (int) serviceUI.getFileOut().length()));
				res.setFilename(serviceUI.getFilenameOut());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			return res;
		} else
			return null;
		
		
		/*
		boolean tx = startTransaction();
		String res="";
		try {
			String sql = "select fileOut from serviceUI where idServiceUI=" + idServiceUI;
			Query q = currentSession().createSQLQuery(sql);
			
			res = (String) q.uniqueResult();
			
			return res;
			
		} finally {
			if (tx)
				commit();
		}
		*/
	}
	
	private String generateSQL(long userID,boolean doCount) {
		return "select " + (doCount? "count(*)":
			" idServiceUI," + 
			" tipoRichiesta," + 
			" userIDRichiesta," + 
			" filenameIn, " +
			" filenameOut," + 
			" dataRichiesta," + 
			" dataCancellazioneFiles," + 
			" dataInizioEsecuzione," + 
			" dataFineEsecuzione," + 
			" cast(esitoEsecuzione as varchar(2048)) esitoEsecuzione," +
			" cast(logEsecuzione as varchar(2048)) as logEsecuzione," +
			" statoRichiesta "
			) +" from ServiceUI with (nolock) "+ //where DataCancellazioneFiles is null and UserIdRichiesta=" + userID;
			(doCount? "": " order by idServiceUI desc");
	}

	public boolean addFile(UserData userData, AppVariables appVariables, byte[] bytes, String filename) {
		
		try {
			Integer res = null;
			boolean tx = startTransaction();
			try {
			
				Connection connection = getConnection();
				
				CallableStatement proc;
		
				// crea richiesta
				proc = connection.prepareCall("{? = call spAddServiceUI (?,?,?,?,?)}");
					
				proc.registerOutParameter(1, Types.INTEGER);
				
				proc.setString(2, "EXPORTSCTLISTA"); // tipo richiesta
				proc.setObject(3, null); //parametri 1
				proc.setBytes(4, bytes); //parametri 1
				proc.setString(5, appVariables.getUserId());	
				proc.setString(6, filename);
				
				proc.execute();
				proc.getMoreResults();
					
				res = proc.getInt(1);	
				
				// invia richiesta
				proc = connection.prepareCall("{? = call spMI_NewServiceOperation (?,?,?,?)}");
				
				proc.registerOutParameter(1, Types.INTEGER);
				proc.setString(2, appVariables.getUserId());
				proc.setString(3, appVariables.getGuid());	
	
				proc.setString(4, "EXPORTSCTLISTA"); // nome attività
				proc.setObject(5, res); //parametri 1
				
				proc.execute();
				proc.getMoreResults();
					
				res = proc.getInt(1);	
				
				return true;
			} finally {
				if (tx)
					commit();
			}
			
		} catch (SQLException ex) {
			Logger.getRootLogger().error ("Exception in ServiceUIDAO.addFile: "+ex);
			ex.printStackTrace();
			return false;
		}
		
	
		
	}
}
