using System.Data.SqlClient;
namespace Verisource.OrderToCash.Application.Base_Classes
public abstract class RepositoryBase
protected IDbTransaction Transaction { get; }
protected IDbConnection Connection => Transaction.Connection;
protected RepositoryBase(IDbTransaction transaction)
Transaction = transaction;
using System.Collections.Generic;
using System.Threading.Tasks;
using Verisource.OrderToCash.Application.Base_Classes;
using Verisource.OrderToCash.Application.Interfaces.Repositories;
using Verisource.OrderToCash.Domain.Entities;
using Verisource.OrderToCash.Domain.Enums;
namespace Verisource.OrderToCash.Persistence.Repositories
public class CandidateHistoryRepository : RepositoryBase, ICandidateHistoryRepository
private static readonly string SqlQueryGetCandidateCountWithBillingStartDate = @"SELECT BillingStartDate,
(SELECT CandHistPersonnelID,
DATEADD(DAY, DATEDIFF(DAY, 0, BillingStartDate), 0) AS BillingStartDate,
MAX(CandHistModDate) AS LastModeDate
FROM[tblCandidateHistory]
WHERE OQBillingTypeID IS NOT NULL
AND BillingStartDate IS NOT NULL
AND OQBillingTypeID = @billingType
AND CandHistCompanyID = @companyId
GROUP BY CandHistPersonnelID, DATEADD(DAY, DATEDIFF(DAY, 0, BillingStartDate), 0)) AS FilteredCandidateHistory
GROUP BY BillingStartDate
ORDER BY BillingStartDate";
private static readonly string SqlQueryGetNewCandidatesJoinZuora = @"SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, ch.BillingStartDate), 0) AS BillingStartDate,
zid.CandidateHistoryID as ZuoraSyncCandidateHistoryID
FROM [tblCandidateHistory] ch
LEFT JOIN tblZuoraIntegrationData zid
ON ch.CandHistoryID = zid.CandidateHistoryID
WHERE ch.OQBillingTypeID IS NOT NULL
AND ch.BillingStartDate IS NOT NULL
AND ch.OQBillingTypeID = @billingType
AND zid.LastSyncDate IS NULL";
private static readonly string SqlQueryGetNewCandidatesJoinZuoraTest = $@"{SqlQueryGetNewCandidatesJoinZuora} AND ch.CandHistCompanyID IN @ids";
public CandidateHistoryRepository(IDbTransaction transaction) : base(transaction)
public Task<IEnumerable<CandidateHistory>> GetCandidateCountWithBillingStartDateAsync(OQBillingType billingType, int companyId)
return Connection.QueryAsync<CandidateHistory>(SqlQueryGetCandidateCountWithBillingStartDate,
new {billingType, companyId}, Transaction);
public Task<IEnumerable<CandidateToSyncInfo>> GetCandidatesToCreateAsync(OQBillingType billingType)
return Connection.QueryAsync<CandidateToSyncInfo>(SqlQueryGetNewCandidatesJoinZuora, new { billingType }, Transaction);
public Task<IEnumerable<CandidateToSyncInfo>> GetCandidatesToCreateAsyncTest(OQBillingType billingType, int[] testCompanyIds)
return Connection.QueryAsync<CandidateToSyncInfo>(SqlQueryGetNewCandidatesJoinZuoraTest, new { billingType, ids = testCompanyIds }, Transaction);
public async Task<IEnumerable<CandidateToSyncInfo>> GetCandidatesToUpdateAsync(OQBillingType billingType)
var candidates = await Connection.QueryAsync<CandidateToSyncInfo>(SqlQueryGetNewCandidatesJoinZuora, new { billingType }, Transaction);
return candidates.Where(x => x.CandHistModDate > DateTime.UtcNow);
public async Task<IEnumerable<CandidateToSyncInfo>> GetCandidatesToUpdateAsyncTest(OQBillingType billingType, int[] testCompanyIds)
var candidates = await Connection.QueryAsync<CandidateToSyncInfo>(SqlQueryGetNewCandidatesJoinZuoraTest, new { billingType, ids = testCompanyIds }, Transaction);
return candidates.Where(x => x.CandHistModDate > DateTime.UtcNow);