/// <summary>
/// Loads the DT list from excel of recipients to DB
/// </summary>
public void LoadToTable()
{
if (this.StrEmailsRecipientsDT.Rows.Count > 0)
{
MyDbUtils mduForDelete = new MyDbUtils(this.DbPath);
mduForDelete.DeleteAllEntries(TABLE_NAME);
foreach (DataRow drX in this.StrEmailsRecipientsDT.AsEnumerable())
{
Dictionary <string, string> recordsX = new Dictionary <string, string>()
{
["Email"] = drX["Email"]?.ToString().Trim() ?? string.Empty,
["EmailUsername"] = drX["EmailUsername"]?.ToString().Trim() ?? string.Empty,
["LastName"] = drX["LastName"]?.ToString().Trim() ?? string.Empty,
["FirstName"] = drX["FirstName"]?.ToString().Trim() ?? string.Empty,
["IsMandatory"] = drX["IsMandatory"]?.ToString().Trim() ?? string.Empty,
["IsAvailable"] = drX["IsAvailable"]?.ToString().Trim() ?? string.Empty,
["RecipientGroup"] = drX["RecipientGroup"]?.ToString().Trim() ?? string.Empty,
["Investigators"] = drX["Investigators"]?.ToString().Trim() ?? string.Empty
};
MyDbUtils mdu = new MyDbUtils(this.DbPath);
mdu.CreateEntry(TABLE_NAME, recordsX);
}
}
else
{
Console.WriteLine($"[StrEmailsRecipientsHandler] DataTable is empty");
}
}
public void InsertUpdateToDbTests_ShouldInsertUpdateInboundEmailsToDb()
{
foreach (List <string> emailX in InboundEmailsList)
{
string fileName = emailX[0];
string subject = emailX[1];
string emailUsername = emailX[2];
string response = emailX[3];
string responseDate = emailX[4];
string phase = emailX[5];
CreateEntryInboundStrEmails(fileName, emailUsername, "InboundStrEmails", subject, response, responseDate, phase);
}
foreach (List <string> emailX in OutboundEmailsList)
{
string fileName = emailX[0];
string subject = emailX[1];
string emailUsername = emailX[2];
string sentDate = emailX[3];
string phase = emailX[4];
CreateEntryOutboundStrEmails(fileName, emailUsername, "OutboundStrEmails", subject, sentDate, phase);
}
MyDbUtils muForRead = new MyDbUtils(this.DbPath);
long actualCount = muForRead.CountEntries("InboundStrEmails");
long expectedCount = 11;
Assert.Equal(expectedCount, actualCount);
}
public StrEmailsFixture()
{
this.RecipientsFile = Path.Combine(Directory.GetCurrentDirectory(), "..", "..", "Inputs", "str_emails_recipients.xlsx");
this.DbPath = Path.Combine(Directory.GetCurrentDirectory(), "..", "..", "_Db", "StrEmailMonitoring.db");
LoadRecipientsFromFile();// load to db
this.DbUtils = new MyDbUtils(this.DbPath);
this.OutboundEmailsList = SetOutboundEmailsList();
this.InboundEmailsList = SetInboundEmailsList();
}
/// <summary>
/// Load an entry based from a given Username
/// </summary>
/// <param name="perBatch"></param>
/// <param name="currentBatch"></param>
public DataTable LoadFromDbViaUsername(string emailUsername, int perBatch = 50, int currentBatch = 1)
{
Dictionary <string, string> criteriaX = new Dictionary <string, string>()
{
["EmailUsername"] = emailUsername
};
MyDbUtils mdu = new MyDbUtils(this.DbPath);
return(mdu.LoadEntries(tableName: TABLE_NAME, orderBy: "EmailUsername", perBatch: perBatch, currentBatch: currentBatch, criteria: criteriaX));
}
public void CountEntriesViaSubqueryTests_ShouldReturnCorrectCount()
{
Dictionary <string, string> inCritX = new Dictionary <string, string>()
{
["Response"] = "REJECTED",
["Phase"] = "PHASE1"
};
Dictionary <string, string> critX = new Dictionary <string, string>()
{
["Response"] = "REJECTED",
};
MyDbUtils muForRead = new MyDbUtils(this.DbPath);
long expectedCount = 3;
long actualCount = muForRead.CountEntriesViaSubquery("InboundStrEmails", criteria: critX, innerCriteria: inCritX, innerColumn: "FileName", innerTable: "OutboundStrEmails");
Assert.Equal(expectedCount, actualCount);
}
public void InsertToDbTest_ShouldInsertEntriesToOuboundStrEmails()
{
foreach (List <string> entryX in this.OutboundEmailsList)
{
string fileName = entryX[0];
string subject = entryX[1];
string emailUsername = entryX[2];
string sentDate = entryX[3];
OutboundStrEmailsHandler outH = new OutboundStrEmailsHandler(this.DbPath, fileName, subject, emailUsername, sentDate);
outH.InsertToDb();
}
MyDbUtils muForRead = new MyDbUtils(this.DbPath);
long actualCount = muForRead.CountEntries("OutboundStrEmails");
long expectedCount = 3;
Assert.Equal(expectedCount, actualCount);
}
/// <summary>
/// Load an entry based from a given Username
/// </summary>
/// <param name="perBatch"></param>
/// <param name="currentBatch"></param>
/// <param name="phaseX">PHASE1 PHASE2 or PHASE3</param>
public DataTable LoadFromDbIsAvailable(string phaseX, int perBatch = 50, int currentBatch = 1)
{
string groupX = string.Empty;
if (!string.IsNullOrEmpty(phaseX))
{
groupX = phaseX?.Last().ToString().Trim() ?? string.Empty;
}
Dictionary <string, string> criteriaX = new Dictionary <string, string>()
{
["IsAvailable"] = "YES",
["RecipientGroup"] = groupX
};
MyDbUtils mdu = new MyDbUtils(this.DbPath);
return(mdu.LoadEntries(tableName: TABLE_NAME, orderBy: "RecipientGroup", perBatch: perBatch, currentBatch: currentBatch, criteria: criteriaX));
}
public void InsertToDbTests_ShouldInsertInboundEmailsToDb()
{
foreach (List <string> emailX in InboundEmailsList)
{
string fileName = emailX[0];
string subject = emailX[1];
string emailUsername = emailX[2];
string response = emailX[3];
string responseDate = emailX[4];
InboundStrEmailsHandler iSEHandler = new InboundStrEmailsHandler(this.DbPath, fileName, subject, emailUsername, response, responseDate);
iSEHandler.InsertToDb();
}
MyDbUtils muForRead = new MyDbUtils(this.DbPath);
long actualCount = muForRead.CountEntries("InboundStrEmails");
long expectedCount = 12;
Assert.Equal(expectedCount, actualCount);
}
private void CreateEntryInboundStrEmails(string FileName, string EmailUsername, string TABLE_NAME, string Subject, string Response, string ResponseDate, string Phase)
{
Dictionary <string, string> criteriaX = new Dictionary <string, string>()
{
["FileName"] = FileName,
["EmailUsername"] = EmailUsername,
};
MyDbUtils mduForCount = new MyDbUtils(this.DbPath);
long resultCount = mduForCount.CountEntries(TABLE_NAME, criteriaX);
if (resultCount < 1)
{
Dictionary <string, string> recordsX = new Dictionary <string, string>()
{
["FileName"] = FileName,
["Subject"] = Subject,
["EmailUsername"] = EmailUsername,
["Response"] = Response,
["ResponseDate"] = ResponseDate,
["Phase"] = Phase
};
MyDbUtils mdu = new MyDbUtils(this.DbPath);
mdu.CreateEntry(TABLE_NAME, recordsX);
}
else
{
Dictionary <string, string> recordsX = new Dictionary <string, string>()
{
["Subject"] = Subject,
["Response"] = Response,
["ResponseDate"] = ResponseDate,
["Phase"] = Phase
};
MyDbUtils mduForUpdate = new MyDbUtils(this.DbPath);
mduForUpdate.UpdateEntry(TABLE_NAME, criteriaX, recordsX);
}
}
public void Dispose()
{
this.DbUtils = null;
this.OutboundEmailsList = null;
}
public InboundStrEmailsHandlerTests(StrEmailsFixture seFix)
{
this.DbPath = seFix.DbPath;
this.InboundEmailsList = seFix.InboundEmailsList;
this.DbUtilsX = seFix.DbUtils;
}
