package gov.va.vinci.dart.db.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
//import java.util.Set;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import gov.va.vinci.dart.common.exception.ObjectNotFoundException;
import gov.va.vinci.dart.biz.Request;
import gov.va.vinci.dart.biz.RequestLocationDocumentSummary;
import gov.va.vinci.dart.biz.RequestParticipantDocumentSummary;
import gov.va.vinci.dart.biz.RequestStatus;
import gov.va.vinci.dart.biz.RequestSummary;
import gov.va.vinci.dart.biz.WorkflowSummary;
//import gov.va.vinci.dart.biz.RequestWorkflow;
//import gov.va.vinci.dart.biz.WorkflowTemplate;
import gov.va.vinci.dart.db.AllParticipantRequestsDAO;
import gov.va.vinci.dart.db.AllRequestsByGroupDAO;
import gov.va.vinci.dart.db.AllRequestsByIrmaDAO;
import gov.va.vinci.dart.db.AllRequestsDAO;
import gov.va.vinci.dart.db.RequestDAO;
import gov.va.vinci.dart.db.RequestLocationDocumentSummaryDAO;
import gov.va.vinci.dart.db.RequestParticipantDocumentSummaryDAO;
import gov.va.vinci.dart.db.RequestTrackingNumberDAO;
import gov.va.vinci.dart.db.util.HibernateDAO;

public class RequestDAOImpl extends HibernateDAO implements RequestDAO {
    
    private static final Logger LOG = LogManager.getLogger(RequestDAOImpl.class);

    @Override
    public void save(Request request) {
        if (request == null) {
            throw new IllegalArgumentException();
        }

        HibernateDAO.save(request);
    }

    @Override
    public Request findById(final int requestId) throws ObjectNotFoundException {

        Query q = createQuery("from Request where id=:rid");
        q.setParameter("rid", requestId);
        try {
            Request result = (Request) q.getSingleResult();

            if (result == null) {
                throw new ObjectNotFoundException("No Request found with id " + requestId);
            }

            return result;
        } catch (NoResultException e) {
            throw new ObjectNotFoundException("No Request found with id " + requestId, e);
        }
    }
    

    @Override
    public List<Request> findExpectedIRBSubmissionByDate(Date date) {

        Query q = createQuery("from Request where expectedIRBSubmissionDate=:rid");
        q.setParameter("rid", date);
        List<Request> result = null;
        try {
            for (Object object : q.getResultList()) {
                if (result == null)
                {
                    result = new ArrayList<Request>();
                }
                result.add((Request)object);
            }

            if (result == null) {
                LOG.debug("No Requests with Expected IRB Submission Date were found");
            }
            
        } catch (NoResultException e) {
            LOG.debug("No Requests with Expected IRB Submission Date were found");
        }

        return result;
    }

    @Override
    public int countRequestsInMonth() {
        Query q =
                createNativeQuery("SELECT count(*) FROM [hib].[request] WHERE YEAR(createdon) = YEAR(GetDate()) AND MONTH(createdon) = MONTH(GetDate())");
        return (Integer) q.getSingleResult();
    }

    @Override
    public int countRequestAmendments(int requestId) {
        Query q = createQuery("SELECT count(*) FROM Request where headId=:rid and amendment=true");
        q.setParameter("rid", requestId);
        Long lVal = (Long) q.getSingleResult();

        return (int) lVal.longValue();
    }

    /**
     * Retrieves the request by tracking number. Exact match on the tracking number.
     */
    @Override
    public Request findByTrackingNumber(final String trackingNumberStr) throws ObjectNotFoundException {

        if (trackingNumberStr == null)
            return null;

        Query q = createQuery("from Request r where r.trackingNumber=:trackNumStr");
        q.setParameter("trackNumStr", trackingNumberStr.trim());

        try {
            Request result = (Request) q.getSingleResult();

            if (result == null) {
                throw new ObjectNotFoundException("No Request found with trackingNumber " + trackingNumberStr);
            }

            return result;
        } catch (NoResultException e) {
            throw new ObjectNotFoundException("No Request found with trackingNumber " + trackingNumberStr);
        }
    }

    /**
     * Retrieves a list of requests, based on tracking number. Like-based match on the tracking number.
     */
    public List<RequestSummary> listByTrackingNumber(final String trackingNumber) {
        return new RequestTrackingNumberDAO(trackingNumber).build();
    }

    /**
     * Retrieves all requests (to be copied into a RequestSummary).
     * 
     * Only referenced by code that leads to test code - marking deprecated for now.
     * @return
     */
    @Deprecated 
    private static Query createNativeQueryToRetrieveAllRequestsWithoutContactName() {

        // Query query =
        // createQuery("select r.status,a.id,r.id,r.trackingNumber,a.name,a.officialName,r.submittedOn,r.updatedOn,TYPE(r),COUNT(v1),COUNT(v) from Request r join r.activity a left outer join r.reviews v1 left outer join r.reviews v where (v.updatedOn is not null) GROUP BY r.status,a.id,r.id,r.trackingNumber,a.name,a.officialName,r.submittedOn,r.updatedOn,TYPE(r)");
        Query query =
                createNativeQuery(" select  r.state as s1,r.activityid as s2,r.id as s3,r.trackingnumber as s4,a.name as s5,a.officialname as s6,r.submittedon as s7,r.updatedon as s8,r.requesttype as s9,"
                        + " (select count(*) from hib.review v where v.requestid=r.id) as cnt1,"
                        + " (select count(*) from hib.review v where v.requestid=r.id and v.updatedon is not null) as cnt2"
                        + " from hib.request r inner join hib.activity a on r.activityid=a.id order by r.id");

        return query;
    }

    @Deprecated
    private static RequestSummary populateRequestSummaryWithoutContactName(final Object[] oarray) {

        if (oarray != null) {

            RequestSummary rs = new RequestSummary();

            rs.setRequestId(((Integer) oarray[2]).intValue());
            rs.setActivityId(((Integer) oarray[1]).intValue());
            rs.setTrackingNumber((String) oarray[3]);

            if (oarray[4] == null) {
                rs.setActivityName((String) oarray[5]);
            } else if (((String) oarray[4]).trim().length() == 0) {
                rs.setActivityName((String) oarray[5]);
            } else {
                rs.setActivityName((String) oarray[4]);
            }

            rs.setSubmittedOn((java.sql.Date) oarray[6]);
            rs.setUpdatedOn((java.sql.Timestamp) oarray[7]);

            rs.setTotalReviews(((Integer) oarray[9]).intValue());
            rs.setCompletedReviews(((Integer) oarray[10]).intValue());

            rs.setType(((Integer) oarray[8]).intValue());

            // rs.setStatus(((Integer)oarray[0]).intValue());
            WorkflowSummary workflowSummary = new WorkflowSummary();
            workflowSummary.setStatus(((Integer) oarray[0]).intValue());
            rs.getWorkflowSummaryList().add(workflowSummary);

            rs.setRetrievedPrincipalInvestigatorName(false);

            return rs;
        }// end if

        return null;
    }

    @Override
    public List<RequestLocationDocumentSummary> listRequestLocationDocumentSummaryByReviewer(final int requestId,
            final String reviewer) {
        return new RequestLocationDocumentSummaryDAO(requestId, reviewer).build();
    }

    @Override
    public List<RequestParticipantDocumentSummary> listRequestParticipantDocumentSummaryByReviewer(final int requestId,
            final String reviewer) {
        return new RequestParticipantDocumentSummaryDAO(requestId, reviewer).build();
    }

    /**
     * Super-user Requestor tab: retrieve all requests (including initiated requests)
     * 
     * @return
     */
    @Override
    public List<RequestSummary> listAllRequestSummary() {

        try {
            return new AllRequestsDAO().build();
        } catch (SQLException e) {
            LOG.error("Failed to get List of RequestSummary: Exception message: " + e.getMessage());
            return new ArrayList<RequestSummary>();
        }

        // Query query = createNativeQueryToRetrieveAllRequests(); //retrieve all requests
        //
        // return new RequestSummaryListBuilder().build(query.getResultList());
    }

    /**
     * Request tab
     */
    @Override
    public List<RequestSummary> listAllRequestSummaryByParticipant(final String participantId) {

        try {
            return new AllParticipantRequestsDAO(participantId).build();
        } catch (SQLException e) {
            LOG.error("Failed to get List of ParticipantRequests: Exception message: " + e.getMessage());
            return new ArrayList<RequestSummary>();
        }
    }


    /**
     * Super-user Requestor tab: retrieve all requests (including initiated requests)
     * 
     * Only referenced by TestRequest and TestRequest DAO - marking Deprecated for now
     *
     * @return
     */
    @Override
    @Deprecated 
    public List<RequestSummary> listAllRequestSummaryWithoutContactName() {

        List<RequestSummary> result = new ArrayList<RequestSummary>();

        Query query = createNativeQueryToRetrieveAllRequestsWithoutContactName(); // retrieve all requests

        for (Object o : query.getResultList()) {
            if (o.getClass().isArray() == true) {
                Object[] oarray = (Object[]) o;

                // create the RequestSummary and add it to the list
                RequestSummary rs = populateRequestSummaryWithoutContactName(oarray);
                if (rs != null)
                    result.add(rs);
            }
        }

        return result;
    }

    /**
     * Reviewer tab -- retrieve all requests that have been submitted.
     * 
     * @return
     */
    @Override
    public List<RequestSummary> listAllButInitiated() {

        try {
            return new AllRequestsByIrmaDAO().build();
        } catch (SQLException e) {
            LOG.error("Failed to get List of RequestsByIrma: Exception message: " + e.getMessage());
            return new ArrayList<RequestSummary>();
        }
    }

    /**
     * Reviewer tab -- retrieve all requests that have been submitted.
     * 
     * Only referenced by test code.  Marked Deprecated for now.
     * @return
     */
    @Override
    @Deprecated
    public List<RequestSummary> listAllButInitiatedWithoutContactName() {

        List<RequestSummary> result = new ArrayList<RequestSummary>();

        Query query = createNativeQueryToRetrieveAllRequestsWithoutContactName(); // retrieve all requests

        for (Object o : query.getResultList()) {
            if (o.getClass().isArray() == true) {
                Object[] oarray = (Object[]) o;

                if (((Integer) oarray[0]).intValue() != RequestStatus.INITIATED.getId()) { // only keep requests NOT still in
                                                                                           // the initiated state

                    // create the RequestSummary and add it to the list
                    RequestSummary rs = populateRequestSummaryWithoutContactName(oarray);
                    if (rs != null)
                        result.add(rs);

                }
            }
        }

        return result;
    }

    // NOT CURRENTLY USED
    @Override
    @Deprecated
    public List<RequestSummary> listAllUserReviewable(final int userId) {

        Query query =
                createNativeQuery("select  distinct r.[id] as s3,r.[state] as s1,r.[activityid] as s2, r.[trackingnumber] as s4,a.[name] as s5,a.[officialname] as s6,r.[submittedon] as s7,r.[updatedon] as s8,r.[requesttype] as s9,"
                        + " (select count(*) from [hib].[review] v where v.[requestid]=r.[id]) as cnt1,"
                        + " (select count(*) from [hib].[review] v where v.[requestid]=r.[id] and v.[updatedon] is not null) as cnt2, v.[id] as s11"
                        + " from [hib].[persongroup] pg "
                        + " inner join [hib].[group] g on g.[id]=pg.[groupid]"
                        + " inner join [hib].[review] v on v.[groupid]=g.[id]"
                        + " inner join [hib].[request] r on v.[requestid]=r.[id]"
                        + " inner join [hib].[activity] a on r.[activityid]=a.[id]"
                        + " where pg.[personid]=:pid and v.[approved] is null and v.[rejected] is null" + " order by r.[id]");

        query.setParameter("pid", userId);

        List<RequestSummary> result = new ArrayList<RequestSummary>();

        for (Object o : query.getResultList()) {
            if (o.getClass().isArray() == true) {
                Object[] oarray = (Object[]) o;

                if (((Integer) oarray[1]).intValue() != RequestStatus.INITIATED.getId()) {
                    RequestSummary rs = new RequestSummary();

                    rs.setRequestId(((Integer) oarray[0]).intValue());
                    rs.setActivityId(((Integer) oarray[2]).intValue());
                    rs.setTrackingNumber((String) oarray[3]);

                    if (oarray[4] == null) {
                        rs.setActivityName((String) oarray[5]);
                    } else if (((String) oarray[4]).trim().length() == 0) {
                        rs.setActivityName((String) oarray[5]);
                    } else {
                        rs.setActivityName((String) oarray[4]);
                    }

                    rs.setSubmittedOn((java.sql.Date) oarray[6]);
                    rs.setUpdatedOn((java.sql.Timestamp) oarray[7]);

                    rs.setTotalReviews(((Integer) oarray[9]).intValue());
                    rs.setCompletedReviews(((Integer) oarray[10]).intValue());

                    rs.setType(((Integer) oarray[8]).intValue());

                    // rs.setStatus(((Integer)oarray[1]).intValue());
                    WorkflowSummary workflowSummary = new WorkflowSummary();
                    workflowSummary.setStatus(((Integer) oarray[1]).intValue());
                    rs.getWorkflowSummaryList().add(workflowSummary);

                    rs.setReviewId(((Integer) oarray[11]).intValue());

                    rs.setRetrievedPrincipalInvestigatorName(false);

                    result.add(rs);
                }
            }
        }

        return result;
    }

    /*------------------------------------------------------------------------------------------------------------------------------------------------*/
    @Override
    @SuppressWarnings("unchecked")
    public List<Request> listAllRequestByHeadId(final int headId) {

        // Query q = createQuery("from Request r where r.id=:headId or r.headId=:headId order by r.id");
        Query q = createQuery("from Request r where r.id=:headId or r.headId=:headId order by r.trackingNumber"); // the ID is
                                                                                                                  // out of
                                                                                                                  // order on
                                                                                                                  // some of the
                                                                                                                  // legacy
                                                                                                                  // requests
                                                                                                                  // (use the
                                                                                                                  // trackingnumber
                                                                                                                  // instead)
        q.setParameter("headId", headId);

        List<Request> result;

        try {
            result = (List<Request>) q.getResultList();
        } catch (NoResultException e) {
            return null;
        }

        if (result != null && result.size() > 0) {
            return result;
        }
        return null;
    }

    /**
     * Retrieves the list of previous requests in the chain, sorted by most recent createdOn timestamp first.
     * 
     * Returns null if no Requests are retrieved.
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<Request> listAllPreviousRequests(final int headId, final Date createdOn) {

        if (createdOn == null || headId == 0) // verify that we have a non-null createdOn date and that this is an amendment
            return null;

        Query q =
                createQuery("from Request r where (r.id=:headId or r.headId=:headId) and r.createdOn<:createdOn order by r.createdOn desc"); // the
                                                                                                                                             // ID
                                                                                                                                             // is
                                                                                                                                             // out
                                                                                                                                             // of
                                                                                                                                             // order
                                                                                                                                             // on
                                                                                                                                             // some
                                                                                                                                             // of
                                                                                                                                             // the
                                                                                                                                             // legacy
                                                                                                                                             // requests
        q.setParameter("headId", headId);
        q.setParameter("createdOn", createdOn);

        List<Request> result;
        try {
            result = (List<Request>) q.getResultList();
        } catch (NoResultException e) {
            return null;
        }

        if (result != null && result.size() > 0) {
            return result;
        }
        return null;
    }

    /**
     * Retrieves the list of previous requests in the chain, sorted by most recent tracking number first.
     * 
     * Returns null if no Requests are retrieved.
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<Request> listAllPreviousRequests(final int headId, final String trackingNumberStr) {

        if (trackingNumberStr == null || headId == 0) // verify that we have a non-null tracking number and that this is an
                                                      // amendment
            return null;

        Query q =
                createQuery("from Request r where (r.id=:headId or r.headId=:headId) and r.trackingNumber<:trackNumStr order by r.trackingNumber desc"); // the
                                                                                                                                                         // ID
                                                                                                                                                         // is
                                                                                                                                                         // out
                                                                                                                                                         // of
                                                                                                                                                         // order
                                                                                                                                                         // on
                                                                                                                                                         // some
                                                                                                                                                         // of
                                                                                                                                                         // the
                                                                                                                                                         // legacy
                                                                                                                                                         // requests
                                                                                                                                                         // (use
                                                                                                                                                         // the
                                                                                                                                                         // trackingnumber
                                                                                                                                                         // instead)
        q.setParameter("headId", headId);
        q.setParameter("trackNumStr", trackingNumberStr);

        List<Request> result;
        try {
            result = (List<Request>) q.getResultList();
        } catch (NoResultException e) {
            return null;
        }

        if (result != null && result.size() > 0) {
            return result;
        }
        return null;
    }

    /**
     * Retrieves the list of previous request IDs (request ID only) in the chain, sorted by most recent tracking number first.
     * 
     * Returns null if no request IDs are retrieved.
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Integer> listAllPreviousRequestIds(final int headId, final String trackingNumberStr) {

        if (trackingNumberStr == null || headId == 0) // verify that we have a non-null tracking number and that this is an
                                                      // amendment
            return null;

        Query q =
                createQuery("select r.id from Request r where (r.id=:headId or r.headId=:headId) and r.trackingNumber<:trackNumStr order by r.trackingNumber desc"); // the
                                                                                                                                                                     // ID
                                                                                                                                                                     // is
                                                                                                                                                                     // out
                                                                                                                                                                     // of
                                                                                                                                                                     // order
                                                                                                                                                                     // on
                                                                                                                                                                     // some
                                                                                                                                                                     // of
                                                                                                                                                                     // the
                                                                                                                                                                     // legacy
                                                                                                                                                                     // requests
                                                                                                                                                                     // (use
                                                                                                                                                                     // the
                                                                                                                                                                     // trackingnumber
                                                                                                                                                                     // instead)
        q.setParameter("headId", headId);
        q.setParameter("trackNumStr", trackingNumberStr);

        List<Integer> result;
        try {
            result = (List<Integer>) q.getResultList();
        } catch (NoResultException e) {
            return null;
        }

        if (result != null && result.size() > 0) {
            return result;
        }
        return null;
    }



    // NOT CURRENTLY USED
    /**
     * Retrieve requests that can be reviewed by the specified group.
     * 
     * @param groupId
     * @return
     */
    @Override
    @Deprecated
    public List<RequestSummary> listAllGroupReviewable_old(final int groupId) {

        Query query =
                createNativeQuery("select  distinct r.[id] as s3,r.[state] as s1,r.[activityid] as s2, r.[trackingnumber] as s4,a.[name] as s5,a.[officialname] as s6,r.[submittedon] as s7,r.[updatedon] as s8,r.[requesttype] as s9,"
                        + " (select count(*) from [hib].[review] v where v.[requestid]=r.[id]) as cnt1,"
                        + " (select count(*) from [hib].[review] v where v.[requestid]=r.[id] and v.[updatedon] is not null) as cnt2, v.[id] as s11"
                        + " from [hib].[group] g"
                        + " inner join [hib].[review] v on v.[groupid]=g.[id]"
                        + " inner join [hib].[request] r on v.[requestid]=r.[id]"
                        + " inner join [hib].[activity] a on r.[activityid]=a.[id]" + " where g.[id]=:gid order by r.[id]");

        query.setParameter("gid", groupId);

        List<RequestSummary> result = new ArrayList<RequestSummary>();

        for (Object o : query.getResultList()) {
            if (o.getClass().isArray() == true) {
                Object[] oarray = (Object[]) o;

                if (((Integer) oarray[1]).intValue() != RequestStatus.INITIATED.getId()) {
                    RequestSummary rs = new RequestSummary();

                    rs.setRequestId(((Integer) oarray[0]).intValue());
                    rs.setActivityId(((Integer) oarray[2]).intValue());
                    rs.setTrackingNumber((String) oarray[3]);

                    if (oarray[4] == null) {
                        rs.setActivityName((String) oarray[5]);
                    } else if (((String) oarray[4]).trim().length() == 0) {
                        rs.setActivityName((String) oarray[5]);
                    } else {
                        rs.setActivityName((String) oarray[4]);
                    }

                    rs.setSubmittedOn((java.sql.Date) oarray[6]);
                    rs.setUpdatedOn((java.sql.Timestamp) oarray[7]);

                    rs.setTotalReviews(((Integer) oarray[9]).intValue());
                    rs.setCompletedReviews(((Integer) oarray[10]).intValue());

                    rs.setType(((Integer) oarray[8]).intValue());

                    // rs.setStatus(((Integer)oarray[1]).intValue());
                    WorkflowSummary workflowSummary = new WorkflowSummary();
                    workflowSummary.setStatus(((Integer) oarray[1]).intValue());
                    rs.getWorkflowSummaryList().add(workflowSummary);

                    rs.setReviewId(((Integer) oarray[11]).intValue());

                    rs.setRetrievedPrincipalInvestigatorName(false);

                    result.add(rs);
                }
            }
        }

        return result;
    }

    // /**
    // * Review tab (intermediate review group): Retrieve requests that can be reviewed by the specified group.
    // * @param groupId
    // * @return
    // */
    // @SuppressWarnings({ "unchecked", "rawtypes" })
    // @Override
    // public List<RequestSummary> listAllGroupReviewable(final int groupId) {
    //
    // //Retrieve all of the dashboard columns for this review group (typically an intermediate review group)
    //
    // Query query = createNativeQuery(
    // "select  "+
    // " r.[state] as s1, "+
    // " r.activityid as s2, "+
    // " r.id as s3,   "+
    // " r.trackingnumber as s4, "+
    // " rev.ID as RevID, "+
    // " a.name as s5,   "+
    // " a.officialname as s6, "+
    // " r.submittedon as s7,   "+
    // " r.updatedon as s8,   "+
    // " r.requesttype as s9,   "+
    // " (select count(*) from hib.review v where v.requestid=r.id) as cnt1, "+
    // " (select count(*) from hib.review v where v.requestid=r.id and v.updatedon is not null) as cnt2, "+
    // " inv.Fullname,  "+
    //
    // " Case when r.State = 3 then 'Change Requested' "+
    // "		when r.State = 4 then 'Approved'  "+
    // "		when r.State = 5 then 'Denied'  "+
    // "		when r.State = 6 then 'Closed' "+
    // "		when r.RequestType = 4 and r.State = 1 then 'Initiated' "+
    // "		when r.RequestType = 4 and r.State = 2 and r.WorkflowState = 2 then '0%' "+
    // "		when r.RequestType = 4 and r.State = 2 and r.WorkFlowState = 3 then '50%'  "+
    // "		when r.RequestTYpe = 4 and r.State = 16 then 'Approved' "+
    // "		when r.State = 1 then 'Initiated'  "+
    // "		when r.State = 2 then Cast(Case when totCount = 0 then 0 "+
    // "									when initNDS.initRev = 0 then 0 "+
    // "									when totCount != 0 then ((apprejCount+ 1) * 100) / (totcount+2) end as Varchar(100))+'%' "+
    // " end as RequestStatus,	 "+
    //
    // " Case    "+
    // " 			 when r.RequestType = 4 and  r.WorkflowState = 1 then 1 "+
    // " 			 when r.State = 1 then 1  "+
    // " 			 when r.State = 3 then 1   "+
    // " 			 else 0 end as [editable],  "+
    // " 			 ammend.Ammendable as Ammendable, "+
    // " 			 TD.TimeDiff as FinalEventTimeDiffInDays, "+
    // " Case when r.RequestType = 2 then 'Research Data Access' "+
    // " 		when r.RequestType = 4 then 'Operations Data Access' end as RequestType, "+
    // " Case when r.RequestType = 2 then 1 else 0 end as isRes,  "+
    // " Case when r.RequestType = 4 then 1 else 0 end as isOp       "+
    // " from hib.request r    "+
    // " inner join hib.activity a on r.activityid=a.id "+
    // " inner join hib.Review rev on r.ID = rev.RequestID "+
    // " inner join hib.[Group] g on rev.GroupID = g.ID "+
    //
    // " left join hib.vwPrimarycontact inv on r.ID = inv.ID "+
    //
    // " left join (select r.ID, SUm(case when Coalesce(rev.approved,rev.rejected) is not null then 1 else 0 end) apprejCount, Count(rev.RequestID) as totcount "+
    // "							 from hib.Request R  "+
    // "							 left join hib.Review  rev on r.ID = rev.RequestID "+
    // "							 where r.state = 2  "+
    // "							group by r.ID) aggres on r.ID = aggRes.ID "+
    // " left join (select requestID, Max(Case when event.Name = 'Initial NDS Review Complete' then 1 else 0 end) as initRev "+
    // "														 from hib.event "+
    // "														group by requestID) initNDS on r.ID = initNDS.RequestID "+
    // " left join (select r.ID,r.activityID, Case when mxHead.mxH = 0 and r.State in (4,5,6) then 1 "+
    // "	  when r.CreatedOn = mxCr.mxAM  and r.state in (4,5,6) then 1  "+
    // "	  else 0 end as ammendable  "+
    // "	  from hib.request r   "+
    // "	  inner join (select ActivityID, Max(HeadID) mxH from hib.Request "+
    // "			  group by ActivityID) mxHead on r.ActivityID = mxHead.ActivityID "+
    // "	  inner join (select ActivityID, Max(CreatedOn) mxAm from hib.Request   "+
    // "			  group by ActivityID) mxCr on r.ActivityID = mxCr.ActivityID  "+
    // "		) ammend on r.ID = ammend.ID  "+
    // " left join ( select r.ID "+
    // "		, Cast(Round(Cast(DateDiff(minute,r.submittedon,Coalesce(mxE.mxcr,getdate())) / 60.0 / 24.0 as Numeric(5,2)),0) as int) as TimeDiff "+
    // "		from hib.Request r   "+
    // "		left join (select requestID, Max(e.createdON) mxcr from hib.[event] e "+
    // "						inner join hib.Request r on e.RequestID = r.ID "+
    // "							where r.State in (4,5,6) "+
    // "						group by requestID) mxE on r.ID = mxE.RequestID "+
    // "	  ) TD on r.ID = TD.ID  "+
    //
    // " where g.[id]=:gid "+
    // " order by r.[id] ");
    //
    // query.setParameter("gid", groupId);
    //
    //
    // List queryResultList = new ArrayList();
    //
    // for (Object o : query.getResultList()) {
    // if (o.getClass().isArray() == true) {
    // Object[] oarray = (Object[])o;
    //
    // if (((Integer)oarray[0]).intValue() != RequestStatus.INITIATED.getId()) { //only keep requests that are NOT still in the
    // Initiated state
    //
    // queryResultList.add(o); //keep the requests that are NOT still in the initiated state
    //
    // }//end if
    // }//end if
    // }//end for
    //
    // return new RequestSummaryListBuilder().buildIntermediateReviewGroupRequestSummary( queryResultList ); //intermediate
    // review group (keep the reviewId)
    //
    // }

    /**
     * Review tab (intermediate review group): Retrieve requests that can be reviewed by the specified group.
     * 
     * @param groupName
     * @return
     */
    @Override
    public List<RequestSummary> listAllGroupReviewable(final String groupName) {

        // Retrieve all of the dashboard columns for this review group (typically an intermediate review group)
        try {
            return new AllRequestsByGroupDAO(groupName).build();
        } catch (SQLException e) {
            LOG.error("Failed to get List of RequestsByGroup: Exception message: " + e.getMessage());
            return new ArrayList<RequestSummary>();
        }
    }


}
