public virtual Task <IDataReader> ExecuteReader(DbProtocol protocol, DbExtract extract)
{
var sourceConnection = GetConnection(protocol);
if (null == sourceConnection)
{
throw new Exception("Data connection not initialized");
}
if (null == extract)
{
throw new Exception("Extract settings not configured");
}
if (sourceConnection.State != ConnectionState.Open)
{
sourceConnection.Open();
}
Connection = sourceConnection;
var commandDefinition = new CommandDefinition(extract.ExtractSql, null, null, 0);
if (sourceConnection is SqliteConnection)
{
return(Task.FromResult <IDataReader>(sourceConnection.ExecuteReader(commandDefinition)));
}
return(sourceConnection.ExecuteReaderAsync(commandDefinition, CommandBehavior.CloseConnection));
}
public int Find(DbProtocol protocol, DbExtract extract)
{
Log.Debug($"Finding {nameof(PsmartSource)}...");
_mapper = GetMapper(extract.Emr);
int extractCount = 0;
var connection = GetConnection(protocol);
using (connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
//Extract SQL
command.CommandText = extract.GetCountSQL();
int.TryParse(command.ExecuteScalar().ToString(), out extractCount);
}
}
Log.Debug($"Finding {nameof(PsmartSource)} Completed");
return(extractCount);
}
public async Task <int> Extract(DbExtract extract, DbProtocol dbProtocol)
{
int batch = 500;
var list = new List <TempPatientAdverseEventExtract>();
int count = 0;
int loaded = 0;
using (var rdr = await _reader.ExecuteReader(dbProtocol, extract))
{
while (rdr.Read())
{
count++;
// AutoMapper profiles
var extractRecord = Mapper.Map <IDataRecord, TempPatientAdverseEventExtract>(rdr);
extractRecord.Id = LiveGuid.NewGuid();
if (extractRecord.HasData())
{
loaded++;
list.Add(extractRecord);
}
if (count == batch)
{
_extractRepository.BatchInsert(list);
count = 0;
DomainEvents.Dispatch(
new ExtractActivityNotification(extract.Id, new DwhProgress(
nameof(PatientAdverseEventExtract),
nameof(ExtractStatus.Finding),
loaded, 0, 0, 0, 0)));
list = new List <TempPatientAdverseEventExtract>();
}
}
if (count > 0)
{
// save remaining list;
_extractRepository.BatchInsert(list);
}
_extractRepository.CloseConnection();
}
// TODO: Notify Completed;
DomainEvents.Dispatch(
new ExtractActivityNotification(extract.Id, new DwhProgress(
nameof(PatientAdverseEventExtract),
nameof(ExtractStatus.Found),
loaded, 0, 0, 0, 0)));
return(loaded);
}
public Task <int> Extract(DbExtract extract, DbProtocol dbProtocol)
{
var mapper = dbProtocol.SupportsDifferential ? ExtractDiffMapper.Instance : ExtractMapper.Instance;
int batch = 500;
var list = new List <TempCwcVisitExtract>();
int count = 0;
int loaded = 0;
using (var rdr = _reader.ExecuteReaderSync(dbProtocol, extract))
{
while (rdr.Read())
{
count++;
loaded++;
// AutoMapper profiles
var extractRecord = mapper.Map <IDataRecord, TempCwcVisitExtract>(rdr);
extractRecord.Id = LiveGuid.NewGuid();
list.Add(extractRecord);
if (count == batch)
{
_extractRepository.BatchInsert(list);
count = 0;
DomainEvents.Dispatch(
new MnchExtractActivityNotification(extract.Id, new DwhProgress(
nameof(CwcVisitExtract),
nameof(ExtractStatus.Finding),
loaded, 0, 0, 0, 0)));
list = new List <TempCwcVisitExtract>();
}
}
if (count > 0)
{
// save remaining list;
_extractRepository.BatchInsert(list);
}
_extractRepository.CloseConnection();
}
// TODO: Notify Completed;
DomainEvents.Dispatch(
new MnchExtractActivityNotification(extract.Id, new DwhProgress(
nameof(CwcVisitExtract),
nameof(ExtractStatus.Found),
loaded, 0, 0, 0, 0)));
return(Task.FromResult(loaded));
}
public void SetUp()
{
_mssql = new DbProtocol(DatabaseType.MicrosoftSQL, @".\koske14", "sa", "maun", "IQTools_KeHMIS");
_extractA = new DbExtract {
ExtractSql = @"select [Id],[shr],[date_created],[status],[status_date],[uuid] FROM psmart_store", Emr = "IQCare"
};
_mysql = new DbProtocol(DatabaseType.MySQL, @"localhost", "root", "test", "openmrs");
_extractB = new DbExtract {
ExtractSql = @"select id,shr,date_created,status,status_date,uuid FROM psmart_store", Emr = "KenyaEMR"
};
_psmartSourceReader = new PsmartSourceReader();
}
public IEnumerable <PsmartSource> Read(DbProtocol protocol, DbExtract extract)
{
_mapper = GetMapper(extract.Emr);
IList <PsmartSource> extracts = new List <PsmartSource>();
var connection = GetConnection(protocol);
using (connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
//Extract SQL
command.CommandText = extract.ExtractSql;
extracts = _mapper.Map <IDataReader, IList <PsmartSource> >(command.ExecuteReader());
}
}
if (extracts.Count > 0)
{
using (connection = GetConnection(protocol))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var updateCommand = connection.CreateCommand())
{
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < extracts.Count; i++)
{
if (i == extracts.Count - 1)
{
stringBuilder.Append($"'{extracts[i].Uuid}'");
}
else
{
stringBuilder.Append($"'{extracts[i].Uuid}',");
}
}
updateCommand.CommandText = $"update psmart_store set Status = 'Collected', Status_date = '{DateTime.Now.Date:yyyy-MM-dd HH:mm:ss}' where UUID in ({stringBuilder.ToString()})";
updateCommand.ExecuteNonQuery();
}
}
}
return(extracts);
}
public int ProcessRejected(Guid extractId, int rejectedCount, DbExtract extract, bool checkDb = true)
{
int count = 0;
if (checkDb)
{
var sql = $@" select count(a.PatientPK)
from {extract.TempTableName}s a
inner join {extract.MainName} b on a.PatientPK=b.PatientPK and a.SiteCode=b.SiteCode
where a.ErrorType=1";
count = ExecQuery <int>(sql);
Log.Debug(sql);
}
DwhUpdateStatus(extractId, ExtractStatus.Excluded, count);
// DwhUpdateStatus(extractId, ExtractStatus.Rejected,rejectedCount-count);
DwhUpdateStatus(extractId, ExtractStatus.Rejected, rejectedCount);
return(count);
}
//public int ProcessExcluded(Guid extractId,int rejectedCount,DbExtract extract)
//{
// var sql = $@"
// select count(PatientPK)
// from {extract.TempTableName}s a where a.PatientPk in (select PatientPK
// from {extract.MainName} where ErrorType=1 and a.SiteCode=SiteCode )
// ";
// int count = ExecQuery<int>(sql);
// Log.Debug(sql);
// DwhUpdateStatus(extractId, ExtractStatus.Excluded, count);
// // DwhUpdateStatus(extractId, ExtractStatus.Rejected,rejectedCount-count);
// DwhUpdateStatus(extractId, ExtractStatus.Rejected,rejectedCount);
// return count;
//}
public int ProcessExcluded(Guid extractId, int rejectedCount, DbExtract extract, bool checkDb = true)
{
int count = 0;
if (checkDb)
{
var sql = $@"
select count(id)
from {extract.TempTableName}s a where CheckError=1";
count = ExecQuery <int>(sql);
Log.Debug(sql);
}
DwhUpdateStatus(extractId, ExtractStatus.Excluded, count);
// DwhUpdateStatus(extractId, ExtractStatus.Rejected,rejectedCount-count);
DwhUpdateStatus(extractId, ExtractStatus.Rejected, rejectedCount);
return(count);
}
public void SetUp()
{
_context = new ExtractsContext(_options);
_context.Database.EnsureDeleted();
_context.Database.EnsureCreated();
_dbExtractProtocolDtos = new List <DbExtractProtocolDTO>();
_mssql = new DbProtocol(DatabaseType.MicrosoftSQL, @".\koske14", "sa", "maun", "IQTools_KeHMIS");
_extractA = new DbExtract {
Id = _iqcareId, Emr = "IQCare", ExtractSql = @" select [Id],[shr],[date_created],[status],[status_date],[uuid] FROM [psmart_store]"
};
_mysql = new DbProtocol(DatabaseType.MySQL, @"localhost", "root", "test", "openmrs");
_extractB = new DbExtract {
Id = _kenyaEmrId, Emr = "KenyaEMR", ExtractSql = @" select id,shr,date_created,status,status_date,uuid FROM psmart_store"
};
_dbExtractProtocolDtos.Add(new DbExtractProtocolDTO(_extractA, _mssql));
_dbExtractProtocolDtos.Add(new DbExtractProtocolDTO(_extractB, _mysql));
_extractHistoryRepository = new ExtractHistoryRepository(_context);
_psmartExtractService =
new PsmartExtractService(new PsmartSourceReader(), new PsmartStageRepository(_context), _extractHistoryRepository);
}
public int Find(DbProtocol protocol, DbExtract extract)
{
throw new NotImplementedException();
}
public Task <int> ReadExtract(DbExtract extract, DbProtocol dbProtocol)
{
throw new NotImplementedException();
}
public IEnumerable <PsmartSource> Extract(DbProtocol protocol, DbExtract extract)
{
_emr = extract.Emr;
return(_psmartSourceReader.Read(protocol, extract));
}
public Task <IDataReader> ExecuteReader(DbProtocol protocol, DbExtract extract)
{
throw new NotImplementedException();
}
public async Task <int> Extract(DbExtract extract, DbProtocol dbProtocol)
{
var mapper = dbProtocol.SupportsDifferential ? ExtractDiffMapper.Instance : ExtractMapper.Instance;
int batch = 500;
DomainEvents.Dispatch(new CbsNotification(new ExtractProgress(nameof(MasterPatientIndex), "extracting...")));
//DomainEvents.Dispatch(new CbsStatusNotification(extract.Id,ExtractStatus.Loading));
var list = new List <TempMasterPatientIndex>();
int count = 0;
int totalCount = 0;
using (var rdr = await _reader.ExecuteReader(dbProtocol, extract))
{
while (rdr.Read())
{
totalCount++;
count++;
// AutoMapper profiles
var extractRecord = mapper.Map <IDataRecord, TempMasterPatientIndex>(rdr);
extractRecord.Id = LiveGuid.NewGuid();
if (!string.IsNullOrWhiteSpace(extractRecord.sxdmPKValueDoB))
{
list.Add(extractRecord);
}
if (count == batch)
{
// TODO: batch and save
_extractRepository.BatchInsert(list);
try
{
DomainEvents.Dispatch(new CbsNotification(new ExtractProgress(nameof(MasterPatientIndex), "extracting...", totalCount, count, 0, 0, 0)));
}
catch (Exception e)
{
Log.Error(e, "Notification error");
}
count = 0;
list = new List <TempMasterPatientIndex>();
}
// TODO: Notify progress...
}
if (count > 0)
{
_extractRepository.BatchInsert(list);
}
_extractRepository.CloseConnection();
}
try
{
DomainEvents.Dispatch(new CbsNotification(new ExtractProgress(nameof(MasterPatientIndex), "extracted", totalCount, 0, 0, 0, 0)));
DomainEvents.Dispatch(new CbsStatusNotification(extract.Id, ExtractStatus.Found, totalCount));
DomainEvents.Dispatch(new CbsStatusNotification(extract.Id, ExtractStatus.Loaded, totalCount));
}
catch (Exception e)
{
Log.Error(e, "Notification error");
}
return(totalCount);
}
public DbExtractProtocolDTO(DbExtract extract, DbProtocol databaseProtocol)
{
Extract = extract;
DatabaseProtocol = databaseProtocol;
}
public int Find(DbProtocol protocol, DbExtract extract)
{
// TODO: Allow User Variables=True
throw new NotImplementedException();
}
public async Task <int> Extract(DbExtract extract, DbProtocol dbProtocol)
{
int batch = 500;
DomainEvents.Dispatch(new MgsNotification(new ExtractProgress(nameof(MetricMigrationExtract), "extracting...")));
//DomainEvents.Dispatch(new CbsStatusNotification(extract.Id,ExtractStatus.Loading));
var list = new List <TempMetricMigrationExtract>();
int count = 0;
int totalCount = 0;
using (var rdr = await _reader.ExecuteReader(dbProtocol, extract))
{
while (rdr.Read())
{
totalCount++;
count++;
// AutoMapper profiles
var extractRecord = Mapper.Map <IDataRecord, TempMetricMigrationExtract>(rdr);
extractRecord.Id = LiveGuid.NewGuid();
list.Add(extractRecord);
if (count == batch)
{
// TODO: batch and save
_extractRepository.BatchInsert(list);
try
{
DomainEvents.Dispatch(new MgsNotification(new ExtractProgress(nameof(MetricMigrationExtract), "extracting...", totalCount, count, 0, 0, 0)));
}
catch (Exception e)
{
Log.Error(e, "Notification error");
}
count = 0;
list = new List <TempMetricMigrationExtract>();
}
// TODO: Notify progress...
}
if (count > 0)
{
_extractRepository.BatchInsert(list);
}
_extractRepository.CloseConnection();
}
try
{
DomainEvents.Dispatch(new MgsNotification(new ExtractProgress(nameof(MetricMigrationExtract), "extracted", totalCount, 0, 0, 0, 0)));
DomainEvents.Dispatch(new MgsStatusNotification(extract.Id, ExtractStatus.Found, totalCount));
DomainEvents.Dispatch(new MgsStatusNotification(extract.Id, ExtractStatus.Loaded, totalCount));
}
catch (Exception e)
{
Log.Error(e, "Notification error");
}
return(totalCount);
}
public IDataReader ExecuteReaderSync(DbProtocol protocol, DbExtract extract)
{
throw new NotImplementedException();
}