package mil.dds.anet.search.mssql;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.Query;

import jersey.repackaged.com.google.common.base.Joiner;
import mil.dds.anet.beans.Task;
import mil.dds.anet.beans.lists.AbstractAnetBeanList.TaskList;
import mil.dds.anet.beans.search.TaskSearchQuery;
import mil.dds.anet.database.mappers.TaskMapper;
import mil.dds.anet.search.ITaskSearcher;
import mil.dds.anet.utils.DaoUtils;
import mil.dds.anet.utils.Utils;

public class MssqlTaskSearcher implements ITaskSearcher {

	@Override
	public TaskList runSearch(TaskSearchQuery query, Handle dbHandle) {
		StringBuilder sql = new StringBuilder("/* MssqlTaskSearch */ SELECT tasks.*, COUNT(*) OVER() AS totalCount FROM tasks");
		Map<String,Object> args = new HashMap<String,Object>();

		sql.append(" WHERE ");
		List<String> whereClauses = new LinkedList<String>();
		String commonTableExpression = null;

		TaskList result =  new TaskList();
		result.setPageNum(query.getPageNum());
		result.setPageSize(query.getPageSize());

		String text = query.getText();
		if (text != null && text.trim().length() > 0) {
			whereClauses.add("(CONTAINS((longName, customField), :text) OR shortName LIKE :likeQuery)");
			args.put("text", Utils.getSqlServerFullTextQuery(text));
			args.put("likeQuery", Utils.prepForLikeQuery(text) + "%");
			args.put("text", Utils.getSqlServerFullTextQuery(text));
		}

		if (query.getResponsibleOrgId() != null) {
			if (query.getIncludeChildrenOrgs() != null && query.getIncludeChildrenOrgs()) {
				commonTableExpression = "WITH parent_orgs(id) AS ( "
						+ "SELECT id FROM organizations WHERE id = :orgId "
					+ "UNION ALL "
						+ "SELECT o.id from parent_orgs po, organizations o WHERE o.parentOrgId = po.id "
					+ ") ";
				whereClauses.add(" organizationId IN (SELECT id from parent_orgs)");
			} else {
				whereClauses.add("organizationId = :orgId");
			}
			args.put("orgId", query.getResponsibleOrgId());
		}

		if (query.getCategory() != null) {
			whereClauses.add("category = :category");
			args.put("category", query.getCategory());
		}

		if (query.getStatus() != null) {
			whereClauses.add("status = :status");
			args.put("status", DaoUtils.getEnumId(query.getStatus()));
		}

		if (query.getProjectStatus() != null) {
			whereClauses.add("customFieldEnum LIKE :projectStatus");
			args.put("projectStatus", query.getProjectStatus());
		}

		if (query.getPlannedCompletionStart() != null) {
			whereClauses.add("plannedCompletion >= :plannedCompletionStart");
			args.put("plannedCompletionStart", Utils.handleRelativeDate(query.getPlannedCompletionStart()));
		}

		if (query.getPlannedCompletionEnd() != null) {
			whereClauses.add("plannedCompletion <= :plannedCompletionStart");
			args.put("plannedCompletionStart", Utils.handleRelativeDate(query.getPlannedCompletionEnd()));
		}

		if (query.getProjectedCompletionStart() != null) {
			whereClauses.add("projectedCompletion >= :projectedCompletionStart");
			args.put("projectedCompletionStart", Utils.handleRelativeDate(query.getProjectedCompletionStart()));
		}

		if (query.getProjectedCompletionEnd() != null) {
			whereClauses.add("projectedCompletion <= :projectedCompletionEnd");
			args.put("projectedCompletionEnd", Utils.handleRelativeDate(query.getProjectedCompletionEnd()));
		}

		if (query.getCustomField() != null) {
			whereClauses.add("[customField] LIKE :customField");
			args.put("customField", Utils.prepForLikeQuery(query.getCustomField()) + "%");
		}

		if (whereClauses.size() == 0) { return result; }

		sql.append(Joiner.on(" AND ").join(whereClauses));
		sql.append(" ORDER BY shortName ASC, longName ASC, id ASC");

		if (commonTableExpression != null) {
			sql.insert(0, commonTableExpression);
		}

		final Query<Task> sqlQuery = MssqlSearcher.addPagination(query, dbHandle, sql, args)
			.map(new TaskMapper());
		return TaskList.fromQuery(sqlQuery, query.getPageNum(), query.getPageSize());
	}

}

@@ -3459,9 +3459,8 @@
 dd(%22
-%5B
 cust
@@ -3470,9 +3470,8 @@
 ield
-%5D
  LIK

Remove unneed [] from query