package gov.va.vss.persistence.impl.volunteer.demographics;

import static gov.va.vss.model.volunteer.VolunteerStatus.VolunteerStatusType.ACTIVE;
import static gov.va.vss.model.volunteer.VolunteerStatus.VolunteerStatusType.INACTIVE;
import static gov.va.vss.model.volunteer.VolunteerStatus.VolunteerStatusType.TERMINATED;
import static gov.va.vss.model.volunteer.VolunteerStatus.VolunteerStatusType.TERMINATED_WITH_CAUSE;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.ACTIVE_ASSIGNMENTS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.PARKING_STICKERS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.SUPERVISORS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.TOTAL_DONATIONS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.UNIFORMS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.ACTIVE_ORGANIZATIONS;
import static gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn.YEARS_VOLUNTEERING;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import gov.va.shared.persistence.impl.AbstractAppDAOImpl;
import gov.va.vss.model.volunteer.VolunteerDemographics;
import gov.va.vss.persistence.dao.volunteer.demographics.VolDemoColumn;
import gov.va.vss.persistence.dao.volunteer.demographics.VolDemoDAO;
import gov.va.vss.persistence.dao.volunteer.demographics.VolDemoSearchParams;
import gov.va.vss.util.DateUtil;

@Repository
public class VolDemoDAOImpl extends AbstractAppDAOImpl<VolunteerDemographics> implements VolDemoDAO {
	@SuppressWarnings("unused")
	private static final Logger log = LoggerFactory.getLogger(VolDemoDAOImpl.class);

	static class SearchContext {
		VolDemoSearchParams searchParams;
		Map<String, Object> params = new HashMap<>();
		StringBuilder sb = new StringBuilder();

		public SearchContext(VolDemoSearchParams searchParams) {
			this.searchParams = searchParams;
		}

		SearchContext append(Object s) {
			sb.append(s);
			return this;
		}

		SearchContext append(String s) {
			sb.append(s);
			return this;
		}

		void addParam(String name, Object val) {
			params.put(name, val);
		}
	}

	@Autowired
	private DateUtil dateUtil;

	@Override
	public List<VolunteerDemographics> findDemographics(VolDemoSearchParams searchParams, int start, int length) {
		if (!searchParams.isIncludeActive() && !searchParams.isIncludeInactive() && !searchParams.isIncludeTerminated()
				&& !searchParams.isIncludeTerminatedByCause())
			return new ArrayList<>();

		SearchContext sc = new SearchContext(searchParams);

		boolean includeAssignments = searchParams.displayCols.contains(ACTIVE_ASSIGNMENTS)
				|| searchParams.displayCols.contains(SUPERVISORS);
		boolean includeParkingStickers = searchParams.displayCols.contains(PARKING_STICKERS);
		boolean includeUniforms = searchParams.displayCols.contains(UNIFORMS);
		boolean includeDonations = searchParams.displayCols.contains(TOTAL_DONATIONS);
		boolean includeActiveOrganizations = searchParams.displayCols.contains(ACTIVE_ORGANIZATIONS);
		boolean includeYearsVolunteering = searchParams.displayCols.contains(YEARS_VOLUNTEERING);
		sc.append("WITH");

		if (searchParams.isLocal()) {
			sc.append("			vols_at_facility AS (") //
					.append("		SELECT DISTINCT TOP 2000000000 va.VssVolunteersFK") //
					.append("		FROM vss.VolunteerAssignments va") //
					.append("		WHERE va.RootFacilityFK = :facilityId");
			if (searchParams.onlyActive())
				sc.append(" 			AND va.IsInactive = 0");
			sc.append("			), ");
			sc.addParam("facilityId", searchParams.facilityId);
		}

		sc.append("				all_assigned_vols AS (") //
				.append("			SELECT *") //
				.append("			FROM vss.Volunteers v");
		if (searchParams.isLocal())
			sc.append("					LEFT JOIN vols_at_facility vaf ON v.id = vaf.VssVolunteersFK");
		sc.append("					WHERE 1=1"); //
		appendWhereClauseItemsForVolunteerRestrictions(sc);
		sc.append("				)");

		if (includeAssignments)
			appendCTEForAssignments(sc);
		if (includeParkingStickers)
			appendCTEForParkingStickers(sc);
		if (includeUniforms)
			includeCTEForUniforms(sc);
		if (includeActiveOrganizations)
			appendActiveOrganizations(sc);

		sc.append("	SELECT v.id,") //
				.append("		v.IdentifyingCode,") //
				.append("		v.LastName, v.FirstName, v.MiddleName, v.NameSuffix, v.Nickname,") //
				.append("		v.DateOfBirth, v.Age, v.IsYouth,") //
				.append("		Gender = g.Name,") //
				.append("		StatusName = vs.name, v.StatusDate,") //
				.append("		v.StreetAddress1, v.StreetAddress2, v.City, State = st.Name, StateId = st.Id, v.Zipcode,") //
				.append(includeParkingStickers ? " vps.combined_parking_stickers," : " combined_parking_stickers = '',") //
				.append(includeUniforms ? " vu.combined_uniforms," : " combined_uniforms = '',") //
				.append("		v.Telephone, v.AlternateTelephone, v.AlternateTelephone2,") //
				.append("		v.EmailAddress,") //
				.append("		v.EmergencyContactName, v.EmergencyContactRelationship, v.EmergencyContactTelephone, v.EmergencyContactAlternateTelephone,") //
				.append("		v.PrimaryFacilityFK, PrimaryFacilityName = ci.nameofinstitution + ' (' + ci.StationNumber + ')',") //
				.append("		v.EntryDate,") //
				.append(includeAssignments ? "va.combined_assignments," : " combined_assignments = '',") //
				.append("		svh.LastVolunteeredDate,") //
				.append("		svh.CurrentYearHours,") //
				.append("		svh.PriorYearHours,") //
				.append("		svh.TotalAdjustedHours,") //
				.append("		svh.TotalHours,") //
				.append(includeDonations //
						? "		TotalDonations = (select ISNULL(sum(dd.DonationValue), 0)" //
								+ "	from vss.DonationDetail dd" //
								+ "	join vss.DonationSummary ds on dd.DonationSummaryFK = ds.id" //
								+ "	join vss.Donor d on ds.DonorFK = d.id" //
								+ "	where d.VssVolunteersFK = v.id),"
						: "	TotalDonations = 0,") //
				.append("		v.HoursLastAward,") //
				.append("		v.DateLastAward,") //
				.append("		PrimaryOrganization = o.OrganizationName,") //
				.append(includeActiveOrganizations ? " ao.active_organizations," : " active_organizations = '',") //
				.append(includeYearsVolunteering
						? "       YearsVolunteering = (select YearsWorked "
								+ "from VolunteerYearsWorked "
								+ "where VolunteerId = v.id)"
						: "	YearsVolunteering = 0")
		;

		sc.append("	FROM all_assigned_vols v") //
				.append("		JOIN sdsadm.std_gender g ON v.std_genderfk = g.id") //
				.append("		JOIN vss.VSS_STD_VolunteerStatus vs ON v.VSS_STD_VolunteerStatusFK = vs.id") //
				.append("		LEFT JOIN sdsadm.std_state st ON v.std_statefk = st.id") //
				.append("		LEFT JOIN dbo.FinalOrganizationName o ON v.primaryorganizationfk = o.id") //
				.append("		LEFT JOIN dbo.combinedinstitutions ci ON v.primaryfacilityfk = ci.id") //
				.append("		LEFT JOIN vss.SUMM_Volunteer_Hours svh on v.id = svh.VolunteerId"); //
		if (includeAssignments)
			sc.append("			LEFT JOIN vol_assignments va on v.id = va.VolunteerId");
		if (includeParkingStickers)
			sc.append("		LEFT JOIN vol_parking_stickers vps on v.id = vps.VolunteerId");
		if (includeUniforms)
			sc.append("		LEFT JOIN vol_uniforms vu on v.id = vu.VolunteerId");
		if (includeActiveOrganizations)
			sc.append("		LEFT JOIN active_orgs ao on v.id = ao.VolunteerId");
		
		sc.append("	WHERE 1=1");
		appendFilterWhereClauseItems(sc);
		appendWhereClauseItemsForAdvancedRestrictions(sc);

		String[] orderByCols = getOrderByCols(searchParams.sortAscending);
		String[] ascendingOrderByCols = getOrderByCols(true);
		sc.append("	ORDER BY ").append(orderByCols[searchParams.sortColIndex]);
		if (searchParams.sortColIndex != 1)
			sc.append(", ").append(ascendingOrderByCols[1]);

		sc.append(" OFFSET " + start + " ROWS");
		if (length != -1)
			sc.append(" FETCH NEXT " + length + " ROWS ONLY");

		Query query = em.createNativeQuery(sc.sb.toString());

		for (Entry<String, Object> entry : sc.params.entrySet())
			query.setParameter(entry.getKey(), entry.getValue());

		@SuppressWarnings("unchecked")
		List<Object[]> queryResults = query.getResultList();

		List<VolunteerDemographics> results = new ArrayList<>(queryResults.size());
		for (Object[] row : queryResults) {
			VolunteerDemographics vd = buildFromRow(row);
			results.add(vd);
		}
		return results;
	}

	@Override
	public int[] findDemographicsTotalAndFilteredNumber(VolDemoSearchParams searchParams) {
		SearchContext sc = new SearchContext(searchParams);

		boolean includeAssignments = searchParams.displayCols.contains(ACTIVE_ASSIGNMENTS)
				|| searchParams.displayCols.contains(SUPERVISORS);

		sc.append("WITH");

		if (searchParams.isLocal()) {
			sc.append("			vols_at_facility AS (") //
					.append("		SELECT DISTINCT TOP 2000000000 va.VssVolunteersFK") //
					.append("		FROM vss.VolunteerAssignments va") //
					.append("		WHERE va.RootFacilityFK = :facilityId");

			if (searchParams.onlyActive())
				sc.append(" 			AND va.IsInactive = 0");
			sc //
					.append("	), ");
			sc.addParam("facilityId", searchParams.facilityId);
		}

		sc.append("				all_assigned_vols AS (") //
				.append("			SELECT v.*") //
				.append("			FROM vss.Volunteers v");
		if (searchParams.isLocal())
			sc.append("					LEFT JOIN vols_at_facility vaf ON v.id = vaf.VssVolunteersFK");
		sc.append("					WHERE 1=1"); //
		appendWhereClauseItemsForVolunteerRestrictions(sc);
		sc.append("				)"); //

		if (includeAssignments)
			appendCTEForAssignments(sc);

		sc.append(" SELECT allCount = count(*), filteredCount = sum(case when (1=1");
		appendFilterWhereClauseItems(sc);
		sc.append(") then 1 else 0 end)") //
				.append("	FROM all_assigned_vols v") //
				.append("		LEFT JOIN sdsadm.std_state st ON v.std_statefk = st.id") //
				.append("		LEFT JOIN dbo.FinalOrganizationName o ON v.primaryorganizationfk = o.id") //
				.append("		LEFT JOIN dbo.combinedinstitutions ci ON v.primaryfacilityfk = ci.id") //
				.append("		LEFT JOIN vss.SUMM_Volunteer_Hours svh on v.id = svh.VolunteerId"); //
		if (includeAssignments)
			sc.append("			LEFT JOIN vol_assignments va on v.id = va.VolunteerId");
		sc.append("					WHERE 1=1"); //
		appendWhereClauseItemsForAdvancedRestrictions(sc);

		Query query = em.createNativeQuery(sc.sb.toString());

		for (Entry<String, Object> entry : sc.params.entrySet())
			query.setParameter(entry.getKey(), entry.getValue());

		Object[] r = (Object[]) query.getSingleResult();
		int totalCount = ((Number) r[0]).intValue();
		Number subsetCount = ((Number) r[1]);
		return new int[] { totalCount, subsetCount == null ? 0 : subsetCount.intValue() };
	}

	private void appendCTEForAssignments(SearchContext sb) {
		sb.append("				,vol_assignments AS (") //
				.append("			SELECT VolunteerId = v.Id") //
				.append("				,combined_assignments = STUFF((") //
				.append("					SELECT ';;' + CONCAT(bs.ServiceName, case when ISNULL(LTRIM(RTRIM(bs.Subdivision)), '') <> '' then ' - ' + bs.Subdivision else '' end") //
				.append("						, ' - ', bsr.Name, '|', bsr.contactName, '|', bsr.contactEmail, '|', bsr.contactPhone)") //
				.append("					FROM vss.VolunteerAssignments va") //
				.append("						JOIN vss.BenefitingServices bs on va.VssBenefitingServicesFK = bs.Id") //
				.append("						JOIN vss.BenefitingServiceRoles bsr on va.VssBenefitingServiceRolesFK = bsr.Id") //
				.append("					WHERE va.VssVolunteersFK = v.Id") //
				.append("						AND va.IsInactive = 0") //
				.append("					ORDER BY bs.ServiceName, bs.Subdivision, bsr.Name") //
				.append("				FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 2, '')") //
				.append("			FROM all_assigned_vols v") //
				.append("			GROUP BY v.Id") //
				.append("		)");
	}

	public void includeCTEForUniforms(SearchContext sb) {
		sb.append("				,vol_uniforms AS (") //
				.append("			SELECT VolunteerId = v.Id") //
				.append("				,combined_uniforms = STUFF((") //
				.append("					SELECT CONCAT(';', ss.SizeName, '|', u.NumberOfShirts)") //
				.append("					FROM vss.Uniforms u") //
				.append("					JOIN vss.ShirtSizes ss ON u.ShirtSizesFK = ss.id") //
				.append("					WHERE u.VssVolunteersFK = v.Id"); //
		if (sb.searchParams.isLocal())
			sb.append("					AND u.FacilityFK = :facilityId"); //
		sb.append("					ORDER BY ss.SizeOrder, u.NumberOfShirts") //
				.append("				FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 1, '')") //
				.append("			FROM all_assigned_vols v") //
				.append("			GROUP BY v.Id") //
				.append("		)");
	}

	public void appendCTEForParkingStickers(SearchContext sb) {
		sb.append("				,vol_parking_stickers AS (") //
				.append("			SELECT VolunteerId = v.Id") //
				.append("				,combined_parking_stickers = STUFF((") //
				.append("					SELECT ';' + ps.stickerNumber + '|' + st.name + '|' + ps.licensePlate") //
				.append("					FROM vss.ParkingSticker ps") //
				.append("					LEFT JOIN sdsadm.std_state st ON ps.STD_StateFK = st.id") //
				.append("					WHERE ps.VssVolunteersFK = v.Id"); //
		if (sb.searchParams.isLocal())
			sb.append("					AND ps.FacilityFK = :facilityId"); //
		sb.append("					ORDER BY ps.stickerNumber, st.name, ps.licensePlate") //
				.append("				FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 1, '')") //
				.append("			FROM all_assigned_vols v") //
				.append("			GROUP BY v.Id") //
				.append("		)");
	}
	
	public void appendActiveOrganizations(SearchContext sb) {
		sb.append("				,active_orgs AS (") //
				.append("			SELECT VolunteerId = v.Id") //
				.append("				,active_organizations = STUFF((") //
				.append("					SELECT ';; ' + fon.OrganizationName ") //
				.append("					FROM vss.VolunteerOrganizations vo, vss.Organizations o, dbo.FinalOrganizationName fon") //
				.append("					WHERE v.id = vo.VssVolunteersFK and o.Id = vo.OrganizationFK and fon.Id = o.id and vo.isInactive = 0") //
				.append("					ORDER BY fon.OrganizationName") //
				.append("				FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 2, '')") //
				.append("			FROM all_assigned_vols v") //
				.append("			GROUP BY v.Id") //
				.append("		)");
	}
	


	private void appendWhereClauseItemsForVolunteerRestrictions(SearchContext sb) {
		VolDemoSearchParams searchParams = sb.searchParams;

		if (sb.searchParams.isLocal()) {
			sb.append("				AND (") //
					.append("			ISNULL(v.PrimaryFacilityFK, v.OriginalFacilityCreatedFK) = :facilityId") //
					.append("			OR vaf.VssVolunteersFK IS NOT NULL)"); //
		}

		/*
		 * If they want to include all statuses, no need to add any restrictions
		 * - CPB
		 */
		if (searchParams.isIncludeActive() && searchParams.isIncludeInactive() && searchParams.isIncludeTerminated()
				&& searchParams.isIncludeTerminatedByCause())
			return;

		sb.append("	AND v.VSS_STD_VolunteerStatusFK in (-1");
		if (searchParams.isIncludeActive())
			sb.append(", ").append(ACTIVE.getId());
		if (searchParams.isIncludeInactive())
			sb.append(", ").append(INACTIVE.getId());
		if (searchParams.isIncludeTerminated())
			sb.append(", ").append(TERMINATED.getId());
		if (searchParams.isIncludeTerminatedByCause())
			sb.append(", ").append(TERMINATED_WITH_CAUSE.getId());
		sb.append(")");
	}

	private void appendWhereClauseItemsForAdvancedRestrictions(SearchContext sb) {
		VolDemoSearchParams searchParams = sb.searchParams;

		// ------------ Last Volunteered Options

		processAdvancedOption(sb, "lastVolOptions=have, haveLastVolOption=haveLastVolLast30",
				"DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) <= 30");
		processAdvancedOption(sb, "lastVolOptions=have, haveLastVolOption=haveLastVolLast60",
				"DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) <= 60");
		processAdvancedOption(sb, "lastVolOptions=have, haveLastVolOption=haveLastVolLast90",
				"DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) <= 90");
		processAdvancedOption(sb, "lastVolOptions=have, haveLastVolOption=haveLastVolThisFiscalYear",
				"svh.LastVolunteeredDate >= :fyStart", "fyStart",
				dateUtil.getCurrentFiscalYearStartDate(ZoneId.systemDefault()));
		String haveLastVolAfter = searchParams.restrictions.get("haveLastVolAfter");
		if (StringUtils.isNotBlank(haveLastVolAfter))
			processAdvancedOption(sb, "lastVolOptions=have, haveLastVolOption=haveLastVolAfter",
					"svh.LastVolunteeredDate >= :haveLastVolAfter", "haveLastVolAfter",
					LocalDate.parse(haveLastVolAfter, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

		processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolIn30",
				"svh.LastVolunteeredDate is null or DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) >= 30");
		processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolIn60",
				"svh.LastVolunteeredDate is null or DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) >= 60");
		processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolIn90",
				"svh.LastVolunteeredDate is null or DATEDIFF(day, svh.LastVolunteeredDate, SYSDATETIME()) >= 90");
		processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolInYear",
				"svh.LastVolunteeredDate is null or svh.LastVolunteeredDate < :fyStart", "fyStart",
				dateUtil.getCurrentFiscalYearStartDate(ZoneId.systemDefault()));
		processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolEver",
				"svh.LastVolunteeredDate is null");
		String haventlastVolSince = searchParams.restrictions.get("haventLastVolSince");
		if (StringUtils.isNotBlank(haventlastVolSince))
			processAdvancedOption(sb, "lastVolOptions=havent, haventLastVolOption=haventLastVolSince",
					"svh.LastVolunteeredDate is null or svh.LastVolunteeredDate < :haventlastVolSince",
					"haventlastVolSince", LocalDate.parse(haventlastVolSince, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

		// ------------ Status Date options

		String statusDateBefore = searchParams.restrictions.get("statusDateBefore");
		if (StringUtils.isNotBlank(statusDateBefore))
			processAdvancedOption(sb, "statusDateOptions=before", "v.StatusDate <= :statusDateBefore",
					"statusDateBefore", LocalDate.parse(statusDateBefore, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

		String statusDateAfter = searchParams.restrictions.get("statusDateAfter");
		if (StringUtils.isNotBlank(statusDateAfter))
			processAdvancedOption(sb, "statusDateOptions=after", "v.StatusDate >= :statusDateAfter", "statusDateAfter",
					LocalDate.parse(statusDateAfter, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

		String statusDateBetweenStart = searchParams.restrictions.get("statusDateBetweenStart");
		String statusDateBetweenEnd = searchParams.restrictions.get("statusDateBetweenEnd");
		if (StringUtils.isNotBlank(statusDateBetweenStart) && StringUtils.isNotBlank(statusDateBetweenEnd)) {
			Map<String, Object> newParams = new HashMap<>();
			newParams.put("statusDateAfter",
					LocalDate.parse(statusDateBetweenStart, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));
			newParams.put("statusDateBefore",
					LocalDate.parse(statusDateBetweenEnd, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));
			processAdvancedOption(sb, "statusDateOptions=between",
					"v.StatusDate >= :statusDateAfter and v.StatusDate <= :statusDateBefore", newParams);
		}
		
		processAdvancedOption(sb, "statusDateOptions=within2FY", "v.StatusDate >= :statusDateAfter2FY", "statusDateAfter2FY",
				dateUtil.getPreviousFiscalYearStartDate(ZoneId.systemDefault()));
		processAdvancedOption(sb, "statusDateOptions=within1FY", "v.StatusDate >= :statusDateAfter1FY", "statusDateAfter1FY",
				dateUtil.getCurrentFiscalYearStartDate(ZoneId.systemDefault()));
		
		// ------------ Entry Date options

				String entryDateBefore = searchParams.restrictions.get("entryDateBefore");
				if (StringUtils.isNotBlank(entryDateBefore))
					processAdvancedOption(sb, "entryDateOptions=before", "v.EntryDate <= :entryDateBefore",
							"entryDateBefore", LocalDate.parse(entryDateBefore, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

				String entryDateAfter = searchParams.restrictions.get("entryDateAfter");
				if (StringUtils.isNotBlank(entryDateAfter))
					processAdvancedOption(sb, "entryDateOptions=after", "v.EntryDate >= :entryDateAfter", "entryDateAfter",
							LocalDate.parse(entryDateAfter, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));

				String entryDateBetweenStart = searchParams.restrictions.get("entryDateBetweenStart");
				String entryDateBetweenEnd = searchParams.restrictions.get("entryDateBetweenEnd");
				if (StringUtils.isNotBlank(entryDateBetweenStart) && StringUtils.isNotBlank(entryDateBetweenEnd)) {
					Map<String, Object> newParams = new HashMap<>();
					newParams.put("entryDateAfter",
							LocalDate.parse(entryDateBetweenStart, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));
					newParams.put("entryDateBefore",
							LocalDate.parse(entryDateBetweenEnd, DateUtil.TWO_DIGIT_DATE_ONLY_FORMAT));
					processAdvancedOption(sb, "entryDateOptions=between",
							"v.EntryDate >= :entryDateAfter and v.EntryDate <= :entryDateBefore", newParams);
				}
				
				processAdvancedOption(sb, "entryDateOptions=within2FY", "v.EntryDate >= :entryDateAfter2FY", "entryDateAfter2FY",
						dateUtil.getPreviousFiscalYearStartDate(ZoneId.systemDefault()));
				processAdvancedOption(sb, "entryDateOptions=within1FY", "v.EntryDate >= :entryDateAfter1FY", "entryDateAfter1FY",
						dateUtil.getCurrentFiscalYearStartDate(ZoneId.systemDefault()));
		
	}

	private void processAdvancedOption(SearchContext sb, String nameValCSVs, String whereClauseItem) {
		processAdvancedOption(sb, nameValCSVs, whereClauseItem, null);
	}

	private void processAdvancedOption(SearchContext sb, String nameValCSVs, String whereClauseItem, String paramName,
			Object paramValue) {
		Map<String, Object> paramsToAdd = new HashMap<>();
		if (paramName != null)
			paramsToAdd.put(paramName, paramValue);
		processAdvancedOption(sb, nameValCSVs, whereClauseItem, paramsToAdd);
	}

	private void processAdvancedOption(SearchContext sb, String nameValCSVs, String whereClauseItem,
			Map<String, Object> newParams) {
		Map<String, String> rMap = sb.searchParams.restrictions;

		String[] tokens = nameValCSVs.split(",");
		for (String t : tokens) {
			String[] nameVal = t.split("=");
			if (!nameVal[1].trim().equals(rMap.get(nameVal[0].trim())))
				return;
		}

		sb.append(" AND (").append(whereClauseItem).append(")");
		if (newParams != null)
			sb.params.putAll(newParams);
	}

	private void appendFilterWhereClauseItems(SearchContext sb) {
		VolDemoSearchParams searchParams = sb.searchParams;

		boolean includeAssignments = searchParams.displayCols.contains(ACTIVE_ASSIGNMENTS)
				|| searchParams.displayCols.contains(SUPERVISORS);

		List<String> whereClauseItems = new ArrayList<>();
		String searchValue = searchParams.searchValue;

		if (StringUtils.isNotBlank(searchValue)) {
			searchValue = searchValue.replaceAll("[^\\p{Print}]", "");
			String[] tokens = searchValue.split("\\s");
			for (int i = 0; i < tokens.length; i++) {
				if (StringUtils.isBlank(tokens[i]))
					continue;
				whereClauseItems.add("(" //
						+ "    v.lastName like :search" + i //
						+ " or v.firstName like :search" + i //
						+ " or v.middleName like :search" + i //
						+ " or v.nameSuffix like :search" + i //
						+ " or v.StreetAddress1 like :search" + i //
						+ " or v.StreetAddress2 like :search" + i //
						+ " or v.City like :search" + i //
						+ " or st.Name like :search" + i //
						+ " or v.ZipCode like :search" + i //
						+ " or v.EmailAddress like :search" + i //
						+ " or v.EmergencyContactName like :search" + i //
						+ " or ci.nameofinstitution like :search" + i //
						+ " or ci.stationnumber like :search" + i //
						+ " or o.OrganizationName like :search" + i //
						+ (includeAssignments ? " or va.combined_assignments like :search" + i : "") //
						+ ")");
				sb.addParam("search" + i, "%" + tokens[i] + "%");
			}
		}

		for (Entry<VolDemoColumn, String> entry : searchParams.filters.entrySet()) {
			VolDemoColumn colIndex = entry.getKey();
			String filterText = entry.getValue();
			/*
			 * all the where clause table references below have to exist in both
			 * the main query and the totalAndFilteredNumber query - CPB
			 */
			if (colIndex == VolDemoColumn.DOB) {
				// Birth month
				whereClauseItems.add("DATEPART(month, v.DateOfBirth) = :monthIndex");
				sb.addParam("monthIndex", filterText);
			} else if (colIndex == VolDemoColumn.AGE_GROUP) {
				// Age Group
				whereClauseItems.add("v.IsYouth = :isYouth");
				sb.addParam("isYouth", "Adult".equalsIgnoreCase(filterText) ? "0" : "1");
			} else if (colIndex == VolDemoColumn.GENDER) {
				// Gender
				whereClauseItems.add("v.std_genderfk = :genderId");
				sb.addParam("genderId", filterText);
			} else if (colIndex == VolDemoColumn.ENTRY_DATE) {
				String[] tokens = filterText.split("/", -1);
				if (!"".equals(tokens[0])) {
					whereClauseItems.add("DATEPART(month, v.EntryDate) = :entryDateMonthIndex");
					sb.addParam("entryDateMonthIndex", tokens[0]);
				}
				if (!"".equals(tokens[1])) {
					whereClauseItems.add("DATEPART(year, v.EntryDate) = :entryDateYearIndex");
					sb.addParam("entryDateYearIndex", tokens[1]);
				}
			} else if (colIndex == VolDemoColumn.STATE) {
				// State
				whereClauseItems.add("st.id = :stateId");
				sb.addParam("stateId", filterText);
			} else if (colIndex == VolDemoColumn.STATUS_DATE) {
				String[] tokens = filterText.split("/", -1);
				if (!"".equals(tokens[0])) {
					whereClauseItems.add("DATEPART(month, v.StatusDate) = :statusDateMonthIndex");
					sb.addParam("statusDateMonthIndex", tokens[0]);
				}
				if (!"".equals(tokens[1])) {
					whereClauseItems.add("DATEPART(year, v.StatusDate) = :statusDateYearIndex");
					sb.addParam("statusDateYearIndex", tokens[1]);
				}
			} else if (colIndex == VolDemoColumn.PRIMARY_FACILITY) {
				// Primary Facility - mine vs others
				if ("mine".equals(filterText)) {
					whereClauseItems.add("ci.id = :workingFacilityId");
					sb.addParam("workingFacilityId", searchParams.workingFacilityId);
				} else if ("others".equals(filterText)) {
					whereClauseItems.add("(ci.id is null or ci.id <> :workingFacilityId)");
					sb.addParam("workingFacilityId", searchParams.workingFacilityId);
				}
			} else if (colIndex == VolDemoColumn.LAST_VOLUNTEERED_DATE) {
				String[] tokens = filterText.split("/", -1);
				if (!"".equals(tokens[0])) {
					whereClauseItems.add("DATEPART(month, svh.LastVolunteeredDate) = :lastVolunteeredDateMonthIndex");
					sb.addParam("lastVolunteeredDateMonthIndex", tokens[0]);
				}
				if (!"".equals(tokens[1])) {
					whereClauseItems.add("DATEPART(year, svh.LastVolunteeredDate) = :lastVolunteeredDateYearIndex");
					sb.addParam("lastVolunteeredDateYearIndex", tokens[1]);
				}
			} else if (colIndex == VolDemoColumn.DATE_LAST_AWARD) {
				String[] tokens = filterText.split("/", -1);
				if (!"".equals(tokens[0])) {
					whereClauseItems.add("DATEPART(month, v.DateLastAward) = :dateLastAwardMonthIndex");
					sb.addParam("dateLastAwardMonthIndex", tokens[0]);
				}
				if (!"".equals(tokens[1])) {
					whereClauseItems.add("DATEPART(year, v.DateLastAward) = :dateLastAwardYearIndex");
					sb.addParam("dateLastAwardYearIndex", tokens[1]);
				}
			}
		}

		if (!whereClauseItems.isEmpty())
			// requires that multi-expression criteria be surrounded earlier
			// with parens - CPB
			sb.append(" AND ").append(StringUtils.join(whereClauseItems, " AND "));
	}

	private String[] getOrderByCols(boolean asc) {
		String orderDir = asc ? "" : " desc";
		String[] orderByCols = { "v.LastName" + orderDir, // dummy value for
															// checkbox column
				"v.LastName" + orderDir + ", v.FirstName" + orderDir + ", v.MiddleName" + orderDir + ", v.NameSuffix"
						+ orderDir, //
				"v.DateOfBirth" + orderDir, //
				"v.Age" + orderDir, //
				"v.IsYouth" + orderDir, //
				"g.Name" + orderDir, //
				"v.IdentifyingCode" + orderDir, //
				"v.EntryDate" + orderDir, //
				"ISNULL(v.StreetAddress1, '')" + orderDir + ", ISNULL(v.StreetAddress2, '')" + orderDir
						+ ", ISNULL(v.City, '')" + orderDir, //
				"ISNULL(v.StreetAddress1, '')" + orderDir + ", ISNULL(v.StreetAddress2, '')" + orderDir, //
				"ISNULL(v.City, '')" + orderDir, //
				"ISNULL(v.StateName, '')" + orderDir, //
				"ISNULL(v.ZipCode, '')" + orderDir, //
				"vps.combined_parking_stickers" + orderDir, //
				"vu.combined_uniforms" + orderDir, //
				"ISNULL(v.Telephone, '')" + orderDir + ", ISNULL(v.AlternateTelephone, '')" + orderDir
						+ ", ISNULL(v.AlternateTelephone2, '')" + orderDir, //
				"ISNULL(v.Telephone, '')" + orderDir, //
				"ISNULL(v.AlternateTelephone, '')" + orderDir, //
				"ISNULL(v.AlternateTelephone2, '')" + orderDir, //
				"ISNULL(v.EmailAddress, '')" + orderDir, //
				"ISNULL(v.EmergencyContactName, '')" + orderDir + ", ISNULL(v.EmergencyContactRelationship, '')"
						+ orderDir, //
				"vs.Name" + orderDir, //
				"v.StatusDate" + orderDir, //
				"ci.nameofinstitution" + orderDir, //
				"va.combined_assignments" + orderDir, //
				"STUFF(va.combined_assignments, 1, CHARINDEX('|', va.combined_assignments), '')" + orderDir, //
				"svh.LastVolunteeredDate" + orderDir, //
				"o.OrganizationName" + orderDir, //
				"ao.active_organizations" + orderDir, 
				"svh.CurrentYearHours" + orderDir, //
				"svh.PriorYearHours" + orderDir, //
				"svh.TotalAdjustedHours" + orderDir, //
				"svh.TotalHours" + orderDir, //
				"TotalDonations" + orderDir, //
				"v.HoursLastAward" + orderDir, //
				"v.DateLastAward" + orderDir,
				"YearsVolunteering" + orderDir
		};
		return orderByCols;
	}

	private VolunteerDemographics buildFromRow(Object[] row) {
		int index = 0;
		long id = ((Number) row[index++]).longValue();
		String identifyingCode = (String) row[index++];
		String lastName = (String) row[index++];
		String firstName = (String) row[index++];
		String middleName = (String) row[index++];
		String nameSuffix = (String) row[index++];
		String nickname = (String) row[index++];
		LocalDate birthDate = ((Timestamp) row[index++]).toLocalDateTime().toLocalDate();
		int age = ((Number) row[index++]).intValue();
		boolean youth = 1 == ((Integer) row[index++]);
		String gender = (String) row[index++];
		String status = (String) row[index++];
		LocalDate statusDate = DateUtil.asLocalDate((Date) row[index++]);
		String streetAddress1 = (String) row[index++];
		String streetAddress2 = (String) row[index++];
		String city = (String) row[index++];
		String state = (String) row[index++];
		// possibly null so we don't convert until below
		Number stateId = (Number) row[index++];
		String zip = (String) row[index++];
		String combinedParkingStickers = (String) row[index++];
		String combinedUniforms = (String) row[index++];
		String phone = (String) row[index++];
		String altPhone = (String) row[index++];
		String altPhone2 = (String) row[index++];
		String email = (String) row[index++];
		String emerContactName = (String) row[index++];
		String emerContactRelationship = (String) row[index++];
		String emerContactPhone = (String) row[index++];
		String emerContactAltPhone = (String) row[index++];
		// possibly null so we don't convert until below
		Number primaryFacilityId = (Number) row[index++];
		String primaryFacilityName = (String) row[index++];
		LocalDate entryDate = DateUtil.asLocalDate((Date) row[index++]);
		String combinedAssignments = (String) row[index++];
		// possibly null so we don't convert until below
		LocalDate lastVolunteeredDate = DateUtil.asLocalDate((Date) row[index++]);
		// possibly null so we don't convert until below
		Number currentYearHours = (Number) row[index++];
		// possibly null so we don't convert until below
		Number priorHours = (Number) row[index++];
		// possibly null so we don't convert until below
		Number adjustedHours = (Number) row[index++];
		// possibly null so we don't convert until below
		Number totalHours = (Number) row[index++];
		BigDecimal totalDonations = new BigDecimal(((Number) row[index++]).toString());
		// possibly null so we don't convert until below
		Number hoursLastAward = (Number) row[index++];
		// possibly null so we don't convert until below
		LocalDate dateLastAward = DateUtil.asLocalDate((Date) row[index++]);
		String primaryOrganization = (String) row[index++];
		String activeOrganizations = (String) row[index++];
		int yearsVolunteering = ((Number) row[index++]).intValue();
		

		VolunteerDemographics vd = new VolunteerDemographics(id, identifyingCode, lastName, firstName, middleName,
				nameSuffix, birthDate, age, youth, nickname, gender, status, statusDate, streetAddress1, streetAddress2,
				city, state, stateId == null ? null : stateId.longValue(), zip, combinedParkingStickers,
				combinedUniforms, phone, altPhone, altPhone2, email, emerContactName, emerContactRelationship,
				emerContactPhone, emerContactAltPhone, primaryFacilityId == null ? null : primaryFacilityId.longValue(),
				primaryFacilityName, entryDate, combinedAssignments, lastVolunteeredDate,
				currentYearHours == null ? 0 : currentYearHours.doubleValue(),
				priorHours == null ? 0 : priorHours.doubleValue(),
				adjustedHours == null ? 0 : adjustedHours.doubleValue(),
				totalHours == null ? 0 : totalHours.doubleValue(), totalDonations,
				hoursLastAward == null || hoursLastAward.doubleValue() == 0 ? null : hoursLastAward.doubleValue(),
				dateLastAward, primaryOrganization, activeOrganizations, yearsVolunteering
				);
		return vd;
	}

}
