VoteRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using ExpressPoll.Core.Common.QueryBuilder;
using ExpressPoll.Core.Common.QueryBuilder.Enums;
using ExpressPoll.Core.Common.SQLite;
using ExpressPoll.Core.Domain.Models;
using SQLite.Net;
using SQLite.Net.Async;

namespace ExpressPoll.Core.Domain
{
    public class VoterRepository : IVoterRepository
    {
        private readonly SQLiteConnection _db;
        private readonly SQLiteAsyncConnection _dbAsync;

        public VoterRepository(ISQLiteConnectionFactory sqLiteConnectionFactory)
        {
            _db = sqLiteConnectionFactory.Create();
            _dbAsync = sqLiteConnectionFactory.CreateAsync();
        }

        public void Add(Voter voter)
        {
            _db.Insert(voter);
        }

        public IEnumerable<Voter> FindVoters(string lastName = null,
                                             string firstName = null,
                                             string middleName = null,
                                             string streetName = null,
                                             string houseNumber = null,
                                             string aptNumber = null,
                                             string zipcode = null,
                                             string voterId = null,
                                             string driversLicense = null,
                                             string affidavitNumber = null,
                                             int consolidationId = -1,
                                             PrecinctIdList precinctIdList = null,
                                             int skip = -1,
                                             int take = -1)
        {
            var query = BuildFindVotersQuery(lastName,
                                             firstName,
                                             middleName,
                                             streetName,
                                             houseNumber,
                                             aptNumber,
                                             zipcode,
                                             voterId,
                                             driversLicense,
                                             affidavitNumber,
                                             consolidationId,
                                             precinctIdList,
                                             skip,
                                             take);

            return _db.Query<Voter>(query);
            
        }

        public async Task<IEnumerable<Voter>> FindVotersAsync(string lastName = null,
                                                              string firstName = null,
                                                              string middleName = null,
                                                              string streetName = null,
                                                              string houseNumber = null,
                                                              string aptNumber = null,
                                                              string zipcode = null,
                                                              string voterId = null,
                                                              string driversLicense = null,
                                                              string affidavitNumber = null,
                                                              int consolidationId = -1,
                                                              PrecinctIdList precinctIdList = null,
                                                              int skip = -1,
                                                              int take = -1)
        {
            var query = BuildFindVotersQuery(lastName,
                                             firstName,
                                             middleName,
                                             streetName,
                                             houseNumber,
                                             aptNumber,
                                             zipcode,
                                             voterId,
                                             driversLicense,
                                             affidavitNumber,
                                             consolidationId,
                                             precinctIdList,
                                             skip,
                                             take);

            return await _dbAsync.QueryAsync<Voter>(query);
        }

        private static string BuildFindVotersQuery(string lastName,
                                                   string firstName,
                                                   string middleName,
                                                   string streetName,
                                                   string houseNumber,
                                                   string aptNumber,
                                                   string zipcode,
                                                   string voterId,
                                                   string driversLicense,
                                                   string affidavitNumber,
                                                   int consolidationId,
                                                   PrecinctIdList precinctIdList,
                                                   int skip = -1,
                                                   int take = -1)
        {
            var selectQueryBuilder = new SelectQueryBuilder();

            selectQueryBuilder.SelectFromTable("Voters");
            selectQueryBuilder.SelectAllColumns();

            if (lastName != null)
                selectQueryBuilder.AddWhere("nameLast", Comparison.Like, lastName.ToUpper() + "%");

            if (firstName != null)
                selectQueryBuilder.AddWhere("nameFirst", Comparison.Like, firstName.ToUpper() + "%");

            if (middleName != null)
                selectQueryBuilder.AddWhere("middleInitial", Comparison.Like, middleName.ToUpper() + "%");

            if (streetName != null)
                selectQueryBuilder.AddWhere("streetName", Comparison.Like, streetName.ToUpper() + "%");

            if (houseNumber != null)
                selectQueryBuilder.AddWhere("strehouseNumberetName", Comparison.Like, houseNumber.ToUpper() + "%");

            if (aptNumber != null)
                selectQueryBuilder.AddWhere("aptNumber", Comparison.Like, aptNumber.ToUpper() + "%");

            if (zipcode != null)
                selectQueryBuilder.AddWhere("zip", Comparison.Like, zipcode.ToUpper() + "%");

            if (voterId != null)
                selectQueryBuilder.AddWhere("voterId", Comparison.Like, voterId.ToUpper() + "%");

            if (driversLicense != null)
                selectQueryBuilder.AddWhere("driversLicense", Comparison.Like, driversLicense.ToUpper() + "%");

            if (affidavitNumber != null)
                selectQueryBuilder.AddWhere("affidavitNumber", Comparison.Like, affidavitNumber.ToUpper() + "%");

            if (consolidationId > 0)
                selectQueryBuilder.AddWhere("consolidationId", Comparison.Equals, consolidationId);

            if (precinctIdList != null && precinctIdList.HasPrecincts)
                selectQueryBuilder.AddWhere("precinctId", Comparison.In, precinctIdList);

            if (skip > 0)
                selectQueryBuilder.Skip(skip);

            if (take > 0)
                selectQueryBuilder.Take(take);

            return selectQueryBuilder.BuildQuery();
        }
    }
}