private void LoadDropDownLists()
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT [epidemiologycasedefinition] FROM [codeepicasedef1]");
DataTable dt = db.Select(selectQuery);
cmbEpiCaseClass.Items.Add(String.Empty);
foreach (DataRow row in dt.Rows)
{
cmbEpiCaseClass.Items.Add(row[0].ToString());
}
selectQuery = db.CreateQuery("SELECT [finallabclass] FROM [codefinallabclass1]");
dt = db.Select(selectQuery);
cmbFinalLabClass.Items.Add(String.Empty);
foreach (DataRow row in dt.Rows)
{
cmbFinalLabClass.Items.Add(row[0].ToString());
}
cmbFeverTempSource.Items.Add(String.Empty);
cmbFeverTempSource.Items.Add(Properties.Resources.FeverTempSourceAxillary);
cmbFeverTempSource.Items.Add(Properties.Resources.FeverTempSourceOral);
cmbFeverTempSourceFinal.Items.Add(String.Empty);
cmbFeverTempSourceFinal.Items.Add(Properties.Resources.FeverTempSourceAxillary);
cmbFeverTempSourceFinal.Items.Add(Properties.Resources.FeverTempSourceOral);
}
void lvLinkedFrom_ItemMouseHover(object sender, ListViewItemMouseHoverEventArgs e)
{
try
{
Point relativePosition = enterMainForm.PointToClient(MousePosition);
Query query = db.CreateQuery(@"Select * " + enterMainForm.View.Project.GetViewById(int.Parse(e.Item.SubItems[3].Text)).FromViewSQL + " where t.GlobalRecordId = @GlobalRecordId");
query.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.StringFixedLength, e.Item.SubItems[1].Text));
DataTable data = db.Select(query);
StringBuilder sb = new StringBuilder();
int counter = 0;
foreach (DataRow row in data.Rows)
{
foreach (DataColumn col in data.Columns)
{
if (!col.ColumnName.ToLowerInvariant().Contains("uniquekey") && !col.ColumnName.ToLowerInvariant().Contains("recstatus") && !col.ColumnName.ToLowerInvariant().Contains("globalrecordid"))
{
counter++;
sb.Append(col.ColumnName + " = " + row[col].ToString() + Environment.NewLine);
if (counter >= 5)
{
break;
}
}
}
}
this.toolTip1.ToolTipTitle = "Record preview:";
this.toolTip1.Show(sb.ToString().Substring(0, sb.Length - 2), enterMainForm, relativePosition.X + 5, relativePosition.Y - (15 + (counter * 13)));
}
catch { }
}
//public static bool Contains(this string source, string toCheck, StringComparison comp)
//{
// Contract.Requires(source != null);
// return source.IndexOf(toCheck, comp) >= 0;
//}
public static string GetCommentLegalLabel(DDLFieldOfCommentLegal commentLegalField, DataRow row, string fieldName, IDbDriver database)
{
if (commentLegalField != null)
{
string phaCode = row[fieldName].ToString();
string columnName = commentLegalField.TextColumnName;
string tableName = commentLegalField.SourceTableName;
Query selectQuery = database.CreateQuery("SELECT * FROM " + tableName); // may be inefficient if called over many contacts, perhaps look at caching this table and passing it in?
DataTable phaTable = database.Select(selectQuery);
foreach (DataRow phaRow in phaTable.Rows)
{
string rowValue = phaRow[0].ToString();
int dashIndex = rowValue.IndexOf("-");
if (dashIndex >= 0 && rowValue.Length >= dashIndex + 1)
{
string rowCode = rowValue.Substring(0, dashIndex);
string rowLabel = rowValue.Substring(dashIndex + 1).Trim();
if (rowCode.Equals(phaCode, StringComparison.OrdinalIgnoreCase))
{
return(rowLabel);
}
}
}
}
return(String.Empty);
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="IDbDriver">Database object</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(IDbDriver dbDriver, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
dbDriver.Select(dbDriver.CreateQuery(pSQL), result);
return(result);
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null && DataHelper.Project != null && DataHelper.Project.CollectedData != null)
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DataTable labTable = DataHelper.LabTable;
double patientsTested = 0;
double samplesCollected = labTable.Rows.Count;//0;
foreach (CaseViewModel caseVM in DataHelper.CaseCollection)
{
if (caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded)
{
double samplesForCurrentCase = labTable.Select("[FKEY] = '" + caseVM.RecordId + "'").Count();
//samplesCollected = samplesCollected + samplesForCurrentCase;
if (samplesForCurrentCase > 0)
{
patientsTested++;
}
}
}
// Commented below is the old method, which simply counted everyone with lab sample records. Customer noted this needs to change. See new code.
//result.PatientsTested = patientsTested.ToString();
// New code is below.
Epi.Fields.RenderableField finalLabClassField = DataHelper.CaseForm.Fields["FinalLabClass"] as Epi.Fields.RenderableField;
if (finalLabClassField != null && finalLabClassField.Page != null)
{
string finalLabClassTableName = finalLabClassField.Page.TableName;
string queryText = "select distinct lrf.FKEY from " + finalLabClassTableName + " crf INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where (crf.FinalLabClass <> '' AND crf.FinalLabClass is not null)";
Query selectQuery = db.CreateQuery(queryText);
int count = db.Select(selectQuery).Rows.Count;
// TODO: Remove excluded cases
result.PatientsTested = count.ToString();
result.SamplesCollected = samplesCollected.ToString();
}
e.Result = result;
}
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(string pFileString, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Query query = driver.CreateQuery(pSQL);
driver.Select(query, result);
}
return(result);
}
/// <summary>
/// Populate internal translation table using Epi 3.x db
/// </summary>
/// <param name="legacyLanguageDatabasePath"></param>
public void ReadDatabase(string legacyLanguageDatabasePath)
{
//IDbDriver db = DatabaseFactory.CreateDatabaseInstanceByFileExtension(legacyLanguageDatabasePath);
IDbDriverFactory dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(Epi.Configuration.AccessDriver);
OleDbConnectionStringBuilder dbCnnStringBuilder = new OleDbConnectionStringBuilder();
dbCnnStringBuilder.FileName = legacyLanguageDatabasePath;
IDbDriver db = dbFactory.CreateDatabaseObject(dbCnnStringBuilder);
List <string> tableNames = db.GetTableNames();
foreach (string tableName in tableNames)
{
List <string> columnNames = db.GetTableColumnNames(tableName);
if (columnNames.Contains("English") && columnNames.Count == 2)
{
DataTable span = db.Select(db.CreateQuery("select * from " + tableName));
int sourceColumnOrdinal, translationColumnOrdinal;
if (string.Compare(span.Columns[0].ColumnName, "English", true) == 0)
{
sourceColumnOrdinal = 0;
translationColumnOrdinal = 1;
}
else
{
sourceColumnOrdinal = 1;
translationColumnOrdinal = 0;
}
foreach (DataRow row in span.Rows)
{
AddTranslationTableEntry(sourceColumnOrdinal, translationColumnOrdinal, row);
}
}
}
}
/// <summary>
/// Gets a code table by name
/// </summary>
/// <param name="tableName">Name of code table</param>
/// <returns>Contents of the code table</returns>
//public override DataTable GetCodeTable(string tableName)
public DataTable GetCodeTable(string tableName)
{
Query query = db.CreateQuery("select * from " + tableName);
return(db.Select(query));
}
/// <summary>
/// Is version Epi Info 7 Metadata flag.
/// </summary>
/// <param name="db">Database driver</param>
/// <returns>True/False on test of EI7 Metadata.</returns>
public static bool IsEpi7Metadata(IDbDriver db)
{
#region Input Validation
if (db == null)
{
throw new ArgumentNullException("DB");
}
#endregion
try
{
// Open the database and look for dbInfo table, find Epi Version ...
bool isEpi7Metadata = false;
Query query = db.CreateQuery("select [EpiVersion], [Purpose] from metaDBInfo");
DataTable results = db.Select(query);
if (results.Rows.Count > 0)
{
foreach (DataRow row in results.Rows)
{
isEpi7Metadata = (row["EpiVersion"].ToString().Substring(0, 1) == "7") && ((row["Purpose"].ToString() == "0") || (row["Purpose"].ToString() == "1"));
}
}
return isEpi7Metadata;
}
finally
{
}
}
/// <summary>
/// Checks for problems in the source project
/// </summary>
protected void CheckForProblems()
{
IDbDriver driver = SourceProject.CollectedData.GetDatabase();
if (driver == null)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Data driver is null.", "999999");
throw new InvalidOperationException("Data driver cannot be null");
}
// Check #1 - Make sure the base table exists and that it has a Global Record Id field, Record status field, and Unique key field.
DataTable dt = driver.GetTableData(SourceForm.TableName, "GlobalRecordId, RECSTATUS, UniqueKey");
if (dt == null)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table is null.", "999998");
throw new InvalidOperationException("Source table cannot be null");
}
else if (dt.Columns.Count == 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table has zero columns.", "999997");
throw new InvalidOperationException("Source table cannot have zero columns");
}
else if (dt.Columns.Count == 1)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Source table has only one column.", "999996");
throw new InvalidOperationException("Source table cannot have only one column");
}
int baseTableRowCount = dt.Rows.Count;
// Check #2a - Make sure GlobalRecordId is a string.
if (!dt.Columns[0].DataType.ToString().Equals("System.String"))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_GUID_COLUMN, "101000");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_GUID_COLUMN);
}
// Check #2b - Make sure RECSTATUS is a number
if (!(dt.Columns[1].DataType.ToString().Equals("System.Byte") || dt.Columns[1].DataType.ToString().Equals("System.Int16") || dt.Columns[1].DataType.ToString().Equals("System.Int32")))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_RECSTATUS_COLUMN, "101001");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_INVALID_RECSTATUS_COLUMN);
}
// Check #3 - Make sure GlobalRecordId values haven't been replaced with something that isn't actually a GUID.
// For performance reasons only the first few values are checked.
if (baseTableRowCount >= 1)
{
string value = dt.Rows[0][0].ToString();
System.Guid guid = new Guid(value);
if (baseTableRowCount >= 30)
{
for (int i = 0; i < 30; i++)
{
value = dt.Rows[i][0].ToString();
guid = new Guid(value);
}
}
}
// Check #4a - See if global record ID values are distinct on the base table.
Query selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [GlobalRecordId] FROM [" + SourceForm.TableName + "]");
DataTable distinctTable = driver.Select(selectDistinctQuery);
if (distinctTable.Rows.Count != baseTableRowCount)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
// Check #4b - See if global record ID values are distinct on each page table.
foreach (Page page in SourceForm.Pages)
{
selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [GlobalRecordId] FROM [" + page.TableName + "]");
distinctTable = driver.Select(selectDistinctQuery);
if (distinctTable.Rows.Count != baseTableRowCount)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE_PAGE, "101003");
throw new ApplicationException(string.Format(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE_PAGE, page.TableName));
}
}
// Check #5 - Make sure RECSTATUS has valid values.
selectDistinctQuery = driver.CreateQuery("SELECT DISTINCT [RecStatus] FROM [" + SourceForm.TableName + "]");
distinctTable = driver.Select(selectDistinctQuery);
foreach (DataRow row in distinctTable.Rows)
{
if (!row[0].ToString().Equals("1") && !row[0].ToString().Equals("0"))
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RECSTATUS_VALUES_INVALID, "101004");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RECSTATUS_VALUES_INVALID);
}
}
// Check #7 - Should never get here because the UI should prevent it, but do a check just in case
if (SourceForm.IsRelatedView == true)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM, "101005");
throw new ApplicationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM);
}
distinctTable = null;
selectDistinctQuery = null;
driver.Dispose();
driver = null;
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null)
{
string format = "P1";
double totalConfirmedProbableCases = (from caseVM in DataHelper.CaseCollection
where caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable
select caseVM).Count();
Epi.Fields.DDLFieldOfCommentLegal feverField = DataHelper.Project.Views[ContactTracing.Core.Constants.CASE_FORM_NAME].Fields["Fever"] as Epi.Fields.DDLFieldOfCommentLegal;
Epi.Fields.DDLFieldOfCommentLegal caseDefField = DataHelper.Project.Views[ContactTracing.Core.Constants.CASE_FORM_NAME].Fields["EpiCaseDef"] as Epi.Fields.DDLFieldOfCommentLegal;
if (feverField != null && caseDefField != null && feverField.Page != null && caseDefField.Page != null)
{
Epi.Page symptomPage = feverField.Page;
Epi.Page firstPage = caseDefField.Page;
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT * FROM " + symptomPage.TableName + " symp INNER JOIN " + firstPage.TableName + " fp ON symp.GlobalRecordId = fp.GlobalRecordId WHERE fp.EpiCaseDef = '1' OR fp.EpiCaseDef = '2'");
DataTable table = db.Select(selectQuery);
double count = (from DataRow row in table.Rows
where row["Fever"].ToString() == "1"
select row).Count();
result.PresentFever = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Vomiting"].ToString() == "1"
select row).Count();
result.PresentVomiting = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Diarrhea"].ToString() == "1"
select row).Count();
result.PresentDiarrhea = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Fatigue"].ToString() == "1"
select row).Count();
result.PresentFatigue = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Anorexia"].ToString() == "1"
select row).Count();
result.PresentAnorexia = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["AbdPain"].ToString() == "1"
select row).Count();
result.PresentAbPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["ChestPain"].ToString() == "1"
select row).Count();
result.PresentChestPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["MusclePain"].ToString() == "1"
select row).Count();
result.PresentMusclePain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["JointPain"].ToString() == "1"
select row).Count();
result.PresentJointPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Headache"].ToString() == "1"
select row).Count();
result.PresentHeadache = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Cough"].ToString() == "1"
select row).Count();
result.PresentCough = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffBreathe"].ToString() == "1"
select row).Count();
result.PresentDiffBreath = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffSwallow"].ToString() == "1"
select row).Count();
result.PresentDiffSwallow = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["SoreThroat"].ToString() == "1"
select row).Count();
result.PresentSoreThroat = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Jaundice"].ToString() == "1"
select row).Count();
result.PresentJaundice = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Conjunctivitis"].ToString() == "1"
select row).Count();
result.PresentConj = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Rash"].ToString() == "1"
select row).Count();
result.PresentSkinRash = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Hiccups"].ToString() == "1"
select row).Count();
result.PresentHiccups = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["PainEyes"].ToString() == "1"
select row).Count();
result.PresentPainEye = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unconscious"].ToString() == "1"
select row).Count();
result.PresentComa = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Confused"].ToString() == "1"
select row).Count();
result.PresentConfused = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unexplainedbleeding"].ToString() == "1"
select row).Count();
result.PresentHemorrhagic = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Fever"].ToString() == "2"
select row).Count();
result.AbsentFever = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Vomiting"].ToString() == "2"
select row).Count();
result.AbsentVomiting = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Diarrhea"].ToString() == "2"
select row).Count();
result.AbsentDiarrhea = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Fatigue"].ToString() == "2"
select row).Count();
result.AbsentFatigue = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Anorexia"].ToString() == "2"
select row).Count();
result.AbsentAnorexia = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["AbdPain"].ToString() == "2"
select row).Count();
result.AbsentAbPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["ChestPain"].ToString() == "2"
select row).Count();
result.AbsentChestPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["MusclePain"].ToString() == "2"
select row).Count();
result.AbsentMusclePain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["JointPain"].ToString() == "2"
select row).Count();
result.AbsentJointPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Headache"].ToString() == "2"
select row).Count();
result.AbsentHeadache = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Cough"].ToString() == "2"
select row).Count();
result.AbsentCough = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffBreathe"].ToString() == "2"
select row).Count();
result.AbsentDiffBreath = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffSwallow"].ToString() == "2"
select row).Count();
result.AbsentDiffSwallow = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["SoreThroat"].ToString() == "2"
select row).Count();
result.AbsentSoreThroat = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Jaundice"].ToString() == "2"
select row).Count();
result.AbsentJaundice = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Conjunctivitis"].ToString() == "2"
select row).Count();
result.AbsentConj = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Rash"].ToString() == "2"
select row).Count();
result.AbsentSkinRash = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Hiccups"].ToString() == "2"
select row).Count();
result.AbsentHiccups = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["PainEyes"].ToString() == "2"
select row).Count();
result.AbsentPainEye = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unconscious"].ToString() == "2"
select row).Count();
result.AbsentComa = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Confused"].ToString() == "2"
select row).Count();
result.AbsentConfused = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unexplainedbleeding"].ToString() == "2"
select row).Count();
result.AbsentHemorrhagic = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Fever"].ToString() == "3" || String.IsNullOrEmpty(row["Fever"].ToString())
select row).Count();
result.UnknownFever = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Vomiting"].ToString() == "3" || String.IsNullOrEmpty(row["Vomiting"].ToString())
select row).Count();
result.UnknownVomiting = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Diarrhea"].ToString() == "3" || String.IsNullOrEmpty(row["Diarrhea"].ToString())
select row).Count();
result.UnknownDiarrhea = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Fatigue"].ToString() == "3" || String.IsNullOrEmpty(row["Fatigue"].ToString())
select row).Count();
result.UnknownFatigue = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Anorexia"].ToString() == "3" || String.IsNullOrEmpty(row["Anorexia"].ToString())
select row).Count();
result.UnknownAnorexia = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["AbdPain"].ToString() == "3" || String.IsNullOrEmpty(row["AbdPain"].ToString())
select row).Count();
result.UnknownAbPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["ChestPain"].ToString() == "3" || String.IsNullOrEmpty(row["ChestPain"].ToString())
select row).Count();
result.UnknownChestPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["MusclePain"].ToString() == "3" || String.IsNullOrEmpty(row["MusclePain"].ToString())
select row).Count();
result.UnknownMusclePain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["JointPain"].ToString() == "3" || String.IsNullOrEmpty(row["JointPain"].ToString())
select row).Count();
result.UnknownJointPain = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Headache"].ToString() == "3" || String.IsNullOrEmpty(row["Headache"].ToString())
select row).Count();
result.UnknownHeadache = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Cough"].ToString() == "3" || String.IsNullOrEmpty(row["Cough"].ToString())
select row).Count();
result.UnknownCough = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffBreathe"].ToString() == "3" || String.IsNullOrEmpty(row["DiffBreathe"].ToString())
select row).Count();
result.UnknownDiffBreath = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["DiffSwallow"].ToString() == "3" || String.IsNullOrEmpty(row["DiffSwallow"].ToString())
select row).Count();
result.UnknownDiffSwallow = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["SoreThroat"].ToString() == "3" || String.IsNullOrEmpty(row["SoreThroat"].ToString())
select row).Count();
result.UnknownSoreThroat = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Jaundice"].ToString() == "3" || String.IsNullOrEmpty(row["Jaundice"].ToString())
select row).Count();
result.UnknownJaundice = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Conjunctivitis"].ToString() == "3" || String.IsNullOrEmpty(row["Conjunctivitis"].ToString())
select row).Count();
result.UnknownConj = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Rash"].ToString() == "3" || String.IsNullOrEmpty(row["Rash"].ToString())
select row).Count();
result.UnknownSkinRash = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Hiccups"].ToString() == "3" || String.IsNullOrEmpty(row["Hiccups"].ToString())
select row).Count();
result.UnknownHiccups = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["PainEyes"].ToString() == "3" || String.IsNullOrEmpty(row["PainEyes"].ToString())
select row).Count();
result.UnknownPainEye = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unconscious"].ToString() == "3" || String.IsNullOrEmpty(row["Unconscious"].ToString())
select row).Count();
result.UnknownComa = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Confused"].ToString() == "3" || String.IsNullOrEmpty(row["Confused"].ToString())
select row).Count();
result.UnknownConfused = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
count = (from DataRow row in table.Rows
where row["Unexplainedbleeding"].ToString() == "3" || String.IsNullOrEmpty(row["Unexplainedbleeding"].ToString())
select row).Count();
result.UnknownHemorrhagic = count + " (" + (count / totalConfirmedProbableCases).ToString(format) + ")";
e.Result = result;
}
}
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
if (DataHelper != null && DataHelper.Project != null && DataHelper.Project.CollectedData != null)
{
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
int total = (from caseVM in DataHelper.CaseCollection
where caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
string format = "P1";
int count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedAcute && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedAcuteCount = count.ToString();
result.ConfirmedAcutePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.ConfirmedConvalescent && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.ConfirmedConvalescentCount = count.ToString();
result.ConfirmedConvalescentPercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NotCase && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NegativeCount = count.ToString();
result.NegativePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.Indeterminate && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.IndeterminateCount = count.ToString();
result.IndeterminatePercent = ((double)count / (double)total).ToString(format);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.FinalLabClass == Core.Enums.FinalLabClassification.NeedsFollowUpSample && caseVM.EpiCaseDef != Core.Enums.EpiCaseClassification.Excluded
select caseVM).Count();
result.NeedsFollowUpCount = count.ToString();
result.NeedsFollowUpPercent = ((double)count / (double)total).ToString(format);
Epi.Fields.RenderableField finalLabClassField = DataHelper.CaseForm.Fields["FinalLabClass"] as Epi.Fields.RenderableField;
Epi.Fields.RenderableField epiCaseDefField = DataHelper.CaseForm.Fields["EpiCaseDef"] as Epi.Fields.RenderableField;
if (finalLabClassField != null && epiCaseDefField != null && finalLabClassField.Page != null && epiCaseDefField.Page != null)
{
string finalLabClassTableName = finalLabClassField.Page.TableName;
string epiCaseClassTableName = epiCaseDefField.Page.TableName;
string queryText = "";
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select count(*) from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
Query selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select crfEpiCaseClass.ID from " + finalLabClassTableName + " AS crf INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
else
{
queryText = "select crfEpiCaseClass.ID from ((" + finalLabClassTableName + " AS crf) INNER JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) INNER JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((crf.FinalLabClass = '' OR crf.FinalLabClass is null) AND (crfEpiCaseClass.EpiCaseDef <> '4'))";
}
selectQuery = db.CreateQuery(queryText);
DataTable dt = db.Select(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (DataRow row in dt.Rows)
{
wb.Add(row["ID"].ToString());
}
result.PendingIDs = wb.ToString();
result.PendingCount = count.ToString();
result.PendingPercent = ((double)count / (double)total).ToString(format);
if (db.ToString().ToLower().Contains("sql"))
{
queryText = "select count(*) from CaseInformationForm AS crf LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
else
{
queryText = "select count(*) from ((CaseInformationForm AS crf) LEFT JOIN " + epiCaseClassTableName + " AS crfEpiCaseClass on crf.GlobalRecordId = crfEpiCaseClass.GlobalRecordId) LEFT JOIN LaboratoryResultsForm lrf on crf.GlobalRecordId = lrf.FKEY where ((lrf.GlobalRecordId = '' OR lrf.GlobalRecordId is null) AND (crfEpiCaseClass.EpiCaseDef <> '4') AND crf.RecStatus = 1)";
}
selectQuery = db.CreateQuery(queryText);
count = (int)db.ExecuteScalar(selectQuery);
result.NotSampledCount = count.ToString();
result.NotSampledPercent = ((double)count / (double)total).ToString(format);
e.Result = result;
}
else
{
throw new InvalidOperationException("FinalLabClass and EpiCaseDef must both be non-null fields in computeWorker_doWork in LabClassAllPatients.xaml.cs");
}
}
}
/// <summary>
/// Finds Epi Version and Purpose of the database.
/// </summary>
/// <param name="db">Abstract data type for database.</param>
/// <returns>Results of test.</returns>
public static bool IsEpi7CollectedData(IDbDriver dbDriver)
{
#region Input Validation
if (dbDriver == null)
{
throw new ArgumentNullException("DB");
}
#endregion
try
{
// Open the database and look for dbInfo table, find Epi Version and purpose ...
bool isEpi7CollectedData = false;
Query query = dbDriver.CreateQuery("select" + Util.InsertIn(dbDriver.InsertInEscape("EpiVersion"), StringLiterals.SPACE) + StringLiterals.COMMA + Util.InsertIn(dbDriver.InsertInEscape("Purpose"), StringLiterals.SPACE) +
"from" + StringLiterals.SPACE + "metaDBInfo");
DataTable results = dbDriver.Select(query);
if (results.Rows.Count > 0)
{
foreach (DataRow row in results.Rows)
{
isEpi7CollectedData = (row["EpiVersion"].ToString().Substring(0, 1) == "7") && ((row["Purpose"].ToString() == "0") || (row["Purpose"].ToString() == "2"));
}
}
return isEpi7CollectedData;
}
finally
{
}
}
/// <summary>
/// GetDataTable
/// </summary>
/// <param name="IDbDriver">Database object</param>
/// <param name="pSQL">SQL Statement</param>
/// <returns>DataTable of results</returns>
public static DataTable GetDataTable(IDbDriver dbDriver, string pSQL)
{
System.Data.DataTable result = new System.Data.DataTable();
dbDriver.Select(dbDriver.CreateQuery(pSQL), result);
return result;
}
private XmlDocument CreateCaseSyncFile(bool includeCases, bool includeCaseExposures, bool includeContacts, Epi.ImportExport.Filters.RowFilters filters, bool deIdentifyData, Epi.RecordProcessingScope recordProcessingScope)
{
TaskbarProgressValue = 0;
TaskbarProgressState = System.Windows.Shell.TaskbarItemProgressState.Normal;
ProgressValue = 0;
_increment = 0.25;
if (includeCaseExposures && includeContacts)
{
_increment = 0.25;
}
else if (includeCaseExposures && !includeContacts)
{
_increment = 0.34;
}
else if (!includeCaseExposures && !includeContacts)
{
_increment = 0.5;
}
IDbDriver database = _project.CollectedData.GetDatabase();
//#region Repair page tables
//RemoveExtraneousPageTableRecordsCommand.Execute(null);
//#endregion // Repair page tables
#region Case and Lab Data
//var packager = new ContactTracing.ExportView.XmlSqlDataPackager(CaseForm, "sync") //new Epi.ImportExport.ProjectPackagers.XmlDataPackager(CaseForm, "sync")
var packager = new Epi.ImportExport.ProjectPackagers.XmlDataPackager(CaseForm, "sync")
{
RecordProcessingScope = recordProcessingScope
};
packager.StatusChanged += unpackager_StatusChanged;
packager.UpdateProgress += unpackager_UpdateProgress;
if (filters == null)
{
filters = new Epi.ImportExport.Filters.RowFilters(database, Epi.ImportExport.Filters.ConditionJoinTypes.And);
}
if (includeCases == false)
{
// filter out all cases
var tfc = new Epi.ImportExport.TextRowFilterCondition("[EpiCaseDef] = @EpiCaseDef", "EpiCaseDef", "@EpiCaseDef", "1000")
{
Description = "EpiCaseDef is equal to 1000"
};
filters.Add(tfc);
}
DateTime dateValue = DateTime.MinValue;
DateTime today = DateTime.Now;
TimeSpan ts = new TimeSpan(int.Parse(Days), 0, 0, 0);
DateTime nDaysAgo = today - ts;
dateValue = nDaysAgo;
var daysAgoFilter = new Epi.ImportExport.DateRowFilterCondition("LastSaveTime >= @LastSaveTime", "LastSaveTime", "@LastSaveTime", dateValue);
filters.Add(daysAgoFilter);
packager.Filters = new Dictionary <string, Epi.ImportExport.Filters.RowFilters>
{
{ "CaseInformationForm", filters }
};
if (deIdentifyData)
{
if (!IsCountryUS)
{
packager.FieldsToNull.Add(CaseForm.Name, new List <string> {
"Surname", "OtherNames", "PhoneNumber", "PhoneOwner", "HeadHouse", "ContactName1", "ContactName2", "ContactName3", "FuneralName1", "FuneralName2", "HospitalBeforeIllPatient", "TradHealerName", "InterviewerName", "InterviewerPhone", "InterviwerEmail", "ProxyName"
});
packager.FieldsToNull.Add(LabForm.Name, new List <string> {
"SurnameLab", "OtherNameLab"
});
}
else
{
packager.FieldsToNull.Add(CaseForm.Name, new List <string> {
"Surname", "OtherNames", "PhoneNumber", "PhoneOwner", "HeadHouse", "ContactName1", "ContactName2", "ContactName3", "FuneralName1", "FuneralName2", "HospitalBeforeIllPatient", "TradHealerName", "InterviewerName", "InterviewerPhone", "InterviwerEmail", "ProxyName", "DOB", "Email", "AddressRes", "AddressOnset", "ProxyPhone", "ProxyEmail"
});
packager.FieldsToNull.Add(LabForm.Name, new List <string> {
"SurnameLab", "OtherNameLab", "PersonLabSubmit", "PhoneLabSubmit", "EmailLabSubmit"
});
}
}
packager.IncludeNullFieldData = false;
var doc = new XmlDocument {
XmlResolver = null
};
bool failed = false;
try
{
OverallSyncStatus = "Packaging case records...";
doc = packager.PackageForm();
TaskbarProgressValue = TaskbarProgressValue + _increment;
OverallSyncStatus = "Finished packaging case records";
if (packager.ExportInfo.RecordsPackaged.ContainsKey(LabForm))
{
RecordsExported = "Exported: " + RecordsExported + packager.ExportInfo.RecordsPackaged[CaseForm].ToString() + " cases, " + packager.ExportInfo.RecordsPackaged[LabForm].ToString() + " lab results";
}
else
{
RecordsExported = "Exported: " + RecordsExported + packager.ExportInfo.TotalRecordsPackaged.ToString() + " cases";
}
}
catch (Exception ex)
{
if (SyncProblemsDetected != null)
{
SyncProblemsDetected(ex, new EventArgs());
}
failed = true;
}
finally
{
packager.StatusChanged -= unpackager_StatusChanged;
packager.UpdateProgress -= unpackager_UpdateProgress;
}
if (failed)
{
return(doc);
}
#endregion // Case and Lab Data
#region Contact Data
if (includeContacts)
{
OverallSyncStatus = "Packaging contact records...";
//packager = new ContactTracing.ExportView.XmlSqlDataPackager(ContactForm, "sync") //new Epi.ImportExport.ProjectPackagers.XmlSqlDataPackager(ContactForm, "sync");
packager = new Epi.ImportExport.ProjectPackagers.XmlDataPackager(ContactForm, "sync")
{
RecordProcessingScope = recordProcessingScope
};
packager.StatusChanged += unpackager_StatusChanged;
packager.UpdateProgress += unpackager_UpdateProgress;
packager.RecordProcessingScope = recordProcessingScope;
filters = new Epi.ImportExport.Filters.RowFilters(database, Epi.ImportExport.Filters.ConditionJoinTypes.And);
daysAgoFilter = new Epi.ImportExport.DateRowFilterCondition("LastSaveTime >= @LastSaveTime", "LastSaveTime", "@LastSaveTime", dateValue);
filters.Add(daysAgoFilter);
packager.Filters = new Dictionary <string, Epi.ImportExport.Filters.RowFilters>
{
{ ContactForm.Name, filters }
};
if (deIdentifyData)
{
if (!IsCountryUS)
{
packager.FieldsToNull.Add(ContactForm.Name, new List <string> {
"ContactSurname", "ContactOtherNames", "ContactHeadHouse", "ContactPhone", "LC1"
});
}
else
{
packager.FieldsToNull.Add(ContactForm.Name, new List <string> {
"ContactSurname", "ContactOtherNames", "ContactHeadHouse", "ContactPhone", "LC1", "ContactDOB", "ContactAddress", "ContactEmail"
});
}
}
try
{
XmlDocument contactDoc = packager.PackageForm();
RecordsExported = RecordsExported + ", " + packager.ExportInfo.TotalRecordsPackaged.ToString() + " contacts";
XmlNodeList xnList = contactDoc.SelectNodes("/DataPackage/Form");
if (IsCountryUS)
{
foreach (XmlNode node in contactDoc.GetElementsByTagName("FieldInfo"))
{
if (node.Attributes[0].Value == "AdminOverride")
{
node.ParentNode.RemoveChild(node);
break;
}
}
}
if (xnList.Count == 1)
{
XmlNode nodeToCopy = doc.ImportNode(contactDoc.SelectSingleNode("/DataPackage/Form"), true); // note: target
XmlNode parentNode = doc.SelectSingleNode("/DataPackage");
parentNode.AppendChild(nodeToCopy);
//doc.Save(@"C:\Temp\ContactTest.xml");
}
}
catch (Exception ex)
{
//if (SyncProblemsDetected != null)
//{
// SyncProblemsDetected(ex, new EventArgs());
//}
// TODO: Re-work this
}
finally
{
packager.StatusChanged -= unpackager_StatusChanged;
packager.UpdateProgress -= unpackager_UpdateProgress;
}
}
TaskbarProgressValue = TaskbarProgressValue + _increment;
OverallSyncStatus = "Finished packaging contact records";
#endregion // Contact Data
#region Link Data
if (includeCaseExposures || includeContacts)
{
OverallSyncStatus = "Packaging relationship records...";
#region metaLinks table
XmlElement links = doc.CreateElement("Links");
Query selectQuery = database.CreateQuery("SELECT * FROM [metaLinks] ORDER BY [LastContactDate] DESC");
DataTable linksTable = database.Select(selectQuery);
foreach (DataRow row in linksTable.Rows)
{
XmlElement link = doc.CreateElement("Link");
var toViewId = (int)row["ToViewId"];
var fromViewId = (int)row["FromViewId"];
if (includeCaseExposures && toViewId == CaseFormId && fromViewId == CaseFormId)
{
// we have a case-to-case link, add it
foreach (DataColumn dc in linksTable.Columns)
{
XmlElement element = doc.CreateElement(dc.ColumnName);
if (row[dc] != DBNull.Value)
{
if (row[dc] is DateTime || dc.ColumnName.Equals("LastContactDate", StringComparison.OrdinalIgnoreCase))
{
var dt = (DateTime)row[dc];
element.InnerText = dt.Ticks.ToString();
}
else
{
element.InnerText = row[dc].ToString();
}
}
else
{
element.InnerText = String.Empty;
}
//if (!String.IsNullOrEmpty(element.InnerText) || !element.Name.StartsWith("Day", StringComparison.OrdinalIgnoreCase))
//{
link.AppendChild(element);
//}
}
}
if (includeContacts && toViewId == ContactFormId && fromViewId == CaseFormId)
{
// we have a case-to-contact link, add it
foreach (DataColumn dc in linksTable.Columns)
{
XmlElement element = doc.CreateElement(dc.ColumnName);
if (row[dc] != DBNull.Value)
{
if (row[dc] is DateTime || dc.ColumnName.Equals("LastContactDate", StringComparison.OrdinalIgnoreCase))
{
var dt = (DateTime)row[dc];
element.InnerText = dt.Ticks.ToString();
}
else
{
element.InnerText = row[dc].ToString();
}
}
else
{
element.InnerText = String.Empty;
}
//if (!String.IsNullOrEmpty(element.InnerText) || !element.Name.StartsWith("Day", StringComparison.OrdinalIgnoreCase))
//{
link.AppendChild(element);
//}
}
}
links.AppendChild(link);
}
doc.ChildNodes[0].AppendChild(links);
#endregion // metaLinks table
TaskbarProgressValue = TaskbarProgressValue + _increment;
RecordsExported = RecordsExported + ", " + linksTable.Rows.Count.ToString() + " relationships";
if (includeContacts)
{
if (database.TableExists("metaHistory"))
{
OverallSyncStatus = "Packaging daily follow-up records...";
#region metaHistory table
XmlElement followUps = doc.CreateElement("ContactFollowUps");
selectQuery = database.CreateQuery("SELECT * FROM [metaHistory] ORDER BY [ContactGUID] DESC, [FollowUpDate] DESC");
DataTable followUpsTable = database.Select(selectQuery);
foreach (DataRow row in followUpsTable.Rows)
{
XmlElement followUp = doc.CreateElement("ContactFollowUp");
XmlElement guid = doc.CreateElement("ContactGUID");
guid.InnerText = row["ContactGUID"].ToString();
followUp.AppendChild(guid);
CultureInfo format = CultureInfo.InvariantCulture;
XmlElement fuDate = doc.CreateElement("FollowUpDate");
fuDate.InnerText = Convert.ToDateTime(row["FollowUpDate"]).ToString(format.DateTimeFormat.ShortDatePattern);
followUp.AppendChild(fuDate);
XmlElement statusOnDate = doc.CreateElement("StatusOnDate");
statusOnDate.InnerText = row["StatusOnDate"].ToString();
followUp.AppendChild(statusOnDate);
XmlElement note = doc.CreateElement("Note");
note.InnerText = row["Note"].ToString();
followUp.AppendChild(note);
if (row.Table.Columns.Contains("Temp1"))
{
XmlElement temp1 = doc.CreateElement("Temp1");
if (row["Temp1"] != DBNull.Value)
{
temp1.InnerText = Convert.ToDouble(row["Temp1"]).ToString(System.Globalization.CultureInfo.InvariantCulture);
}
followUp.AppendChild(temp1);
XmlElement temp2 = doc.CreateElement("Temp2");
if (row["Temp2"] != DBNull.Value)
{
temp2.InnerText = Convert.ToDouble(row["Temp2"]).ToString(System.Globalization.CultureInfo.InvariantCulture);
}
followUp.AppendChild(temp2);
}
followUps.AppendChild(followUp);
}
#endregion // metaHistory table
doc.ChildNodes[0].AppendChild(followUps);
TaskbarProgressValue = TaskbarProgressValue + _increment;
RecordsExported = RecordsExported + ", " + followUpsTable.Rows.Count.ToString() + " follow-ups";
}
}
}
#endregion // Link Data
return(doc);
}
/// <summary>
/// Checks for problems in the source project
/// </summary>
protected override void CheckForProblems()
{
IDbDriver driver = SourceProject.CollectedData.GetDatabase();
if (driver == null)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError("Data driver is null.", "999999");
throw new InvalidOperationException("Data driver cannot be null");
}
int rows = 0;
foreach (View form in SourceForm.Project.Views)
{
Query selectDistinctQuery = driver.CreateQuery("SELECT GlobalRecordId, COUNT(GlobalRecordId) AS n FROM " + form.TableName + " GROUP BY GlobalRecordId HAVING COUNT(GlobalRecordId) > 1");
rows = driver.Select(selectDistinctQuery).Rows.Count;
if (rows > 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
foreach (Page page in form.Pages)
{
Query deleteQuery = driver.CreateQuery("DELETE FROM " + form.Name + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId from " + page.TableName + ")");
rows = driver.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
Query pageDeleteQuery = driver.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId NOT IN (SELECT GlobalRecordId from " + form.Name + ")");
rows = driver.ExecuteNonQuery(deleteQuery);
if (rows > 0)
{
// report ??
}
Query selectDistinctPageQuery = driver.CreateQuery("SELECT GlobalRecordId, COUNT(GlobalRecordId) AS n FROM " + page.TableName + " GROUP BY GlobalRecordId HAVING COUNT(GlobalRecordId) > 1");
rows = driver.Select(selectDistinctPageQuery).Rows.Count;
if (rows > 0)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE, "101002");
throw new InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_GUID_NOT_UNIQUE);
}
}
}
// Check #7 - Should never get here because the UI should prevent it, but do a check just in case
if (SourceForm.IsRelatedView == true)
{
ExportInfo.Succeeded = false;
ExportInfo.AddError(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM, "101005");
throw new InvalidOperationException(ImportExportSharedStrings.ERROR_PACKAGER_CHECK_RELATED_FORM);
}
driver.Dispose();
driver = null;
}
/// <summary>
/// Constructor with file path
/// </summary>
/// <param name="filePath">The file path to the location of the PRJ file</param>
public VhfProject(string filePath)
: base(filePath)
{
XDocument doc = XDocument.Load(filePath);
XElement root = doc.Root;
XElement isVhf = root.Element("IsVHF");
XElement isLab = root.Element("IsLabProject");
XElement outbreakDate = root.Element("OutbreakDate");
XElement outbreakName = root.Element("OutbreakName");
XElement culture = root.Element("Culture");
XElement smsModule = root.Element("SmsModule");
MacAddress = Core.Common.GetMacAddress();
IsVHF = bool.Parse(isVhf.Value);
IsLabProject = bool.Parse(isLab.Value);
OutbreakDate = new DateTime(long.Parse(outbreakDate.Value));
OutbreakName = outbreakName.Value;
if (culture == null)
{
Culture = String.Empty;
//throw new InvalidOperationException("Project's culture settings cannot be null.");
// probably don't need an exception here since we're moving to database-based culture checking
}
else
{
Culture = culture.Value;
}
SmsModule = new SmsModule();
// setup SMS module
if (smsModule != null)
{
XElement readReceipts = smsModule.Element("SendsReadReceipts");
if (readReceipts != null)
{
SmsModule.SendsReadReceipts = bool.Parse(readReceipts.Value);
}
XElement startupCommands = smsModule.Element("StartupCommands");
if (startupCommands != null)
{
SmsModule.StartupCommands = startupCommands.Value;
}
XElement authCode = smsModule.Element("SelfRegistrationCode");
if (authCode != null)
{
SmsModule.SelfRegistrationCode = authCode.Value;
}
XElement allowSelfReg = smsModule.Element("AllowsSelfRegistration");
if (allowSelfReg != null)
{
SmsModule.AllowsSelfRegistration = bool.Parse(allowSelfReg.Value);
}
XElement pollRate = smsModule.Element("PollRate");
if (pollRate != null)
{
SmsModule.PollRate = int.Parse(pollRate.Value);
}
XElement authorizedSenders = smsModule.Element("AuthorizedSmsSenders");
if (authorizedSenders != null)
{
SmsModule.AuthorizedSmsSenders.Clear();
var query = (from xml in authorizedSenders.Descendants()
select xml);
foreach (XElement element in query)
{
if (element.Name.ToString().Equals("AuthorizedSmsSender", StringComparison.OrdinalIgnoreCase))
{
string phoneNumber = element.Element("PhoneNumber").Value;
SmsSenderInfo sender = new SmsSenderInfo(phoneNumber);
sender.Name = element.Element("Name").Value;
sender.CanAddUpdateCases = bool.Parse(element.Element("CanAddUpdateCases").Value);
sender.CanAddUpdateContacts = bool.Parse(element.Element("CanAddUpdateContacts").Value);
sender.CanAddUpdateLabResults = bool.Parse(element.Element("CanAddUpdateLabResults").Value);
SmsModule.AuthorizedSmsSenders.Add(sender);
}
}
}
}
IDbDriver db = this.CollectedData.GetDatabase();
bool connected = db.TestConnection();
if (connected)
{
Query selectQuery = db.CreateQuery("SELECT * FROM [metaViews]");
DataTable dt = db.Select(selectQuery);
foreach (DataRow row in dt.Rows)
{
if (row["Name"].ToString().Equals(Core.Constants.CASE_FORM_NAME))
{
CaseFormId = int.Parse(row["ViewId"].ToString());
}
else if (row["Name"].ToString().Equals(Core.Constants.CONTACT_FORM_NAME))
{
ContactFormId = int.Parse(row["ViewId"].ToString());
}
}
}
else
{
throw new InvalidOperationException("No connection to database detected.");
}
}
private void btnOK_Click(object sender, EventArgs e)
{
KeyFields = new List <Field>();
if (lbxFields.SelectedItems.Count == 0)
{
return;
}
try
{
#region Check #1 - Make sure key is unique on parent form
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery("SELECT Count(*) FROM [" + Form.TableName + "]");
int recordCount = (int)db.ExecuteScalar(selectQuery);
WordBuilder wb = new WordBuilder(",");
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
wb.Add(field.Name);
}
}
selectQuery = db.CreateQuery("SELECT DISTINCT " + wb.ToString() + " " + Form.FromViewSQL);
int distinctCount = db.Select(selectQuery).Rows.Count; // probably better way to do this, but unsure if can be made generic... this query is most generic across DB types?
if (distinctCount == recordCount)
{
foreach (Field field in Form.Fields)
{
if (field is RenderableField && lbxFields.SelectedItems.Contains(field.Name))
{
KeyFields.Add(field);
}
}
}
else
{
if (lbxFields.SelectedItems.Count == 1)
{
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", lbxFields.SelectedItem.ToString()));
}
else if (lbxFields.SelectedItems.Count > 1)
{
WordBuilder keyFields = new WordBuilder(",");
foreach (string s in lbxFields.SelectedItems)
{
keyFields.Add(s);
}
Epi.Windows.MsgBox.ShowError(String.Format("The selected match key ({0}) is not unique.", keyFields.ToString()));
}
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
#endregion // Check #1 - Make sure key is unique on parent form
// Currently, disable match keys if related forms exist. TODO: Change this later?
foreach (View otherForm in Project.Views)
{
if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
{
Epi.Windows.MsgBox.ShowError("Custom match keys cannot be used to package a form that contains child forms.");
this.DialogResult = System.Windows.Forms.DialogResult.None;
return;
}
}
//#region Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
//foreach (View otherForm in Project.Views)
//{
// if (otherForm != Form && Epi.ImportExport.ImportExportHelper.IsFormDescendant(otherForm, Form))
// {
// foreach (Field field in KeyFields)
// {
// if (!otherForm.Fields.Contains(field.Name))
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} does not exist in the child form {1}. The keys selected in this dialog must exist across all child forms.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// else
// {
// Field otherField = otherForm.Fields[field.Name];
// if (otherField.FieldType != field.FieldType)
// {
// Epi.Windows.MsgBox.ShowError(
// String.Format(
// "The selected field {0} is implemented as a different field type on child form {1}. The keys selected in this dialog must exist across all child forms and those fields must be of the same field type.",
// field.Name, otherForm.Name));
// this.DialogResult = System.Windows.Forms.DialogResult.None;
// return;
// }
// }
// }
// }
//}
//#endregion // Check #2 - Make sure key exists in other forms in the hierarchy and that it's the same field type
}
catch (Exception ex)
{
Epi.Windows.MsgBox.ShowException(ex);
this.DialogResult = System.Windows.Forms.DialogResult.None;
}
}
/// <summary>
/// Executes a SQL query based on a valid SMS message
/// </summary>
/// <param name="updateType">The type of update</param>
/// <param name="message">The SMS message</param>
/// <returns>bool; whether the execution was successful</returns>
public bool ExecuteSmsUpdate(ShortMessage message)
{
#region Input Validation
if (message == null)
{
throw new ArgumentNullException("Message object cannot be null");
}
if (String.IsNullOrEmpty(message.Message))
{
throw new InvalidOperationException("SMS message cannot be empty");
}
string[] parts = message.Message.Split(' ');
if (parts.Length != 3)
{
throw new InvalidOperationException("SMS message must contain three parts.");
}
int updateType;
bool success = int.TryParse(parts[0], out updateType);
if (updateType < 1 || updateType > 3)
{
throw new InvalidOperationException("SMS message update type is invalid. Valid values are 1, 2, and 3");
}
#endregion // Input Validation
bool operationWasSuccessful = true;
int id;
success = int.TryParse(parts[1], out id);
if (success)
{
if (updateType == 1)
{
#region Case Data Update
// do nothing right now
operationWasSuccessful = false;
#endregion Case Data Update
}
else if (updateType == 2)
{
#region Daily Follow-up Update
int status;
success = int.TryParse(parts[2], out status);
if (status >= 0 && status <= 7)
{
// get the database
IDbDriver db = this.CollectedData.GetDatabase();
// get the global record ID for this contact
Query selectQuery = db.CreateQuery("SELECT GlobalRecordId FROM ContactEntryForm WHERE UniqueKey = @Id");
selectQuery.Parameters.Add(new QueryParameter("@Id", System.Data.DbType.Int32, id));
DataTable dt = db.Select(selectQuery);
string guid = dt.Rows[0][0].ToString();
// calculate the window so we know which day column to update
selectQuery = db.CreateQuery("SELECT LastContactDate FROM metaLinks WHERE " +
"ToRecordGuid = @ToRecordGuid AND " +
"FromViewId = @FromViewId AND " +
"ToViewId = @ToViewId");
selectQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", System.Data.DbType.String, guid));
selectQuery.Parameters.Add(new QueryParameter("@FromViewId", System.Data.DbType.Int32, CaseFormId));
selectQuery.Parameters.Add(new QueryParameter("@ToViewId", System.Data.DbType.Int32, ContactFormId));
dt = db.Select(selectQuery);
DateTime lastContactDate = (DateTime)(dt.Rows[0][0]);
DateTime today = DateTime.Today;
TimeSpan ts = today - lastContactDate;
int day = (int)ts.TotalDays; // this should never be a decimal since all dates stored should not have time componenets
// update the right row in metaLinks
Query updateQuery = db.CreateQuery("UPDATE metaLinks SET Day" + day.ToString() + " = @Status WHERE " +
"ToRecordGuid = @ToRecordGuid AND " +
"FromViewId = @FromViewId AND " +
"ToViewId = @ToViewId");
updateQuery.Parameters.Add(new QueryParameter("@Status", DbType.Byte, status));
updateQuery.Parameters.Add(new QueryParameter("@ToRecordGuid", System.Data.DbType.String, guid));
updateQuery.Parameters.Add(new QueryParameter("@FromViewId", System.Data.DbType.Int32, CaseFormId));
updateQuery.Parameters.Add(new QueryParameter("@ToViewId", System.Data.DbType.Int32, ContactFormId));
int records = db.ExecuteNonQuery(updateQuery);
if (records == 1)
{
operationWasSuccessful = true;
}
else
{
operationWasSuccessful = false;
}
// add a changeset message
if (db.ToString().ToLower().Contains("sql"))
{
System.Guid changesetGuid = System.Guid.NewGuid();
string changesetGuidString = changesetGuid.ToString();
DateTime now = DateTime.Now;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.ConnectionString + ";Connection Timeout=10"))
{
conn.Open();
System.Data.SqlClient.SqlCommand insertCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO Changesets (ChangesetID, UpdateType, UserID, MACADDR, Description, DestinationRecordID, CheckinDate) VALUES (" +
"@ChangesetID, @UpdateType, @UserID, @MACADDR, @Description, @DestinationRecordID, @CheckinDate)", conn);
insertCommand.Parameters.Add("@ChangesetID", SqlDbType.NVarChar).Value = changesetGuidString;
insertCommand.Parameters.Add("@UpdateType", SqlDbType.Int).Value = 15;
insertCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = "SMS server";
insertCommand.Parameters.Add("@MACADDR", SqlDbType.NVarChar).Value = "SMS";
insertCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = "SMS contact status update";
insertCommand.Parameters.Add("@DestinationRecordID", SqlDbType.NVarChar).Value = guid;
insertCommand.Parameters.Add("@CheckinDate", SqlDbType.DateTime2).Value = now;
records = insertCommand.ExecuteNonQuery();
if (records == 1)
{
success = true;
}
}
}
}
#endregion // Daily Follow-up Update
}
else if (updateType == 3)
{
#region Lab Data Update
// do nothing right now
operationWasSuccessful = false;
#endregion Lab Data Update
}
}
return(operationWasSuccessful);
}
protected override void OnOK()
{
try
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
System.Collections.Generic.List <string> ProblemVariableList = new System.Collections.Generic.List <string>();
if (cbxAllExcept.Checked)
{
foreach (string s in lbxVariables.Items)
{
if (!this.lbxVariables.SelectedItems.Contains(s))
{
if (s.IndexOf(' ') == 0)
{
if (ProblemVariableList.Count == 0)
{
sb.Append("[");
}
else
{
sb.Append(" ,[");
}
sb.Append(s);
sb.Append("] ");
ProblemVariableList.Add(s);
}
}
}
}
else
{
foreach (string s in lbxVariables.SelectedItems)
{
if (s.IndexOf(' ') == 0)
{
if (ProblemVariableList.Count == 0)
{
sb.Append("[");
}
else
{
sb.Append(" ,[");
}
sb.Append(s);
sb.Append("] ");
ProblemVariableList.Add(s);
}
}
}
if (ProblemVariableList.Count > 0)
{
Epi.Windows.MsgBox.ShowError(string.Format(SharedStrings.EXPORT_CANNOT_PROCEED_LEADING_TRAILING_SPACES, sb.ToString()));
return;
}
else if (selectedDataSource is IDbDriver)
{
Type csv = Type.GetType("Epi.Data.Office.CsvFile, Epi.Data.Office");
if (selectedDataSource.GetType().AssemblyQualifiedName == csv.AssemblyQualifiedName)
{
cmbDataTable.Text = cmbDataTable.Text.Replace('.', '#');
if (!cmbDataTable.Text.Contains("#"))
{
//cmbDataTable.Text = cmbDataTable.Text + "#txt";
cmbDataTable.Text = cmbDataTable.Text + "#csv";
}
}
IDbDriver db = selectedDataSource as IDbDriver;
if (db.TableExists(((Epi.Windows.Analysis.Dialogs.WriteDialog.ComboBoxItem)(cmbDataTable.SelectedItem)).Value.ToString()))
{
DataTable temp = db.Select(db.CreateQuery("SELECT COUNT (*) FROM " + ((Epi.Windows.Analysis.Dialogs.WriteDialog.ComboBoxItem)(cmbDataTable.SelectedItem)).Value.ToString()));
if (temp.Rows.Count > 0)
{
int count = (int)temp.Rows[0][0];
if (count > 0)
{
if (rdbAppend.Checked)
{
if (MessageBox.Show(string.Format(SharedStrings.EXISTING_TABLE_APPEND, cmbDataTable.Text, count.ToString()), "Existing Table", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
else
{
if (MessageBox.Show(string.Format(SharedStrings.EXISTING_TABLE_REPLACE, cmbDataTable.Text, count.ToString()), "Existing Table", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
}
}
}
}
}
catch (Exception ex)
{
//
}
base.OnOK();
}
public static DataTable JoinPageTables(IDbDriver db, View vw)
{
DataTable unfilteredTable = new DataTable();
// Get the form's field count, adding base table fields plus GUID field for each page. If less than 255, use SQL relate; otherwise, >255 exceeds OLE field capacity and we need to use a less efficient method
if (vw.Fields.DataFields.Count + vw.Pages.Count + 5 < 255 && vw.Pages.Count < 15)
{
unfilteredTable = db.Select(db.CreateQuery("SELECT * " + vw.FromViewSQL));
if (unfilteredTable.Columns["RecStatus"] == null && unfilteredTable.Columns["t.RecStatus"] != null)
{
unfilteredTable.Columns["t.RecStatus"].ColumnName = "RecStatus";
}
if (unfilteredTable.Columns.Contains("t.GlobalRecordId"))
{
unfilteredTable.Columns["t.GlobalRecordId"].ColumnName = "GlobalRecordId";
}
foreach (Epi.Page page in vw.Pages)
{
string pageGUIDName = page.TableName + "." + "GlobalRecordId";
if (unfilteredTable.Columns.Contains(pageGUIDName))
{
unfilteredTable.Columns.Remove(pageGUIDName);
}
}
}
else
{
DataTable viewTable = new DataTable();
viewTable = db.GetTableData(vw.TableName, "GlobalRecordId, UniqueKey, RECSTATUS");
viewTable.TableName = vw.TableName;
DataTable relatedTable = new DataTable("relatedTable");
//int total = 0;
//// Get totals for percent completion calculation
//foreach (Epi.Page page in vw.Pages)
//{
// string pageColumnsToSelect = String.Empty;
// foreach (Field field in page.Fields)
// {
// if (field is RenderableField && field is IDataField)
// {
// total++;
// }
// }
//}
//total = total * RecordCount;
//int counter = 0;
foreach (Epi.Page page in vw.Pages)
{
List <string> pageColumnsToSelect = new List <string>();
foreach (Field field in page.Fields)
{
if (field is RenderableField && field is IDataField)
{
pageColumnsToSelect.Add(field.Name);
}
}
pageColumnsToSelect.Add("GlobalRecordId");
DataTable pageTable = db.GetTableData(page.TableName, pageColumnsToSelect);
pageTable.TableName = page.TableName;
foreach (DataColumn dc in pageTable.Columns)
{
if (dc.ColumnName != "GlobalRecordId")
{
viewTable.Columns.Add(dc.ColumnName, dc.DataType);
}
}
//try
//{
// assume GlobalUniqueId column is unique and try to make it the primary key.
DataColumn[] parentPrimaryKeyColumns = new DataColumn[1];
parentPrimaryKeyColumns[0] = viewTable.Columns["GlobalRecordId"];
viewTable.PrimaryKey = parentPrimaryKeyColumns;
//}
//catch (Exception)
//{
//}
foreach (DataRow row in pageTable.Rows)
{
DataRow viewRow = viewTable.Rows.Find(row["GlobalRecordId"].ToString());
viewRow.BeginEdit();
if (viewRow["GlobalRecordId"].ToString().Equals(row["GlobalRecordId"].ToString()))
{
foreach (DataColumn dc in pageTable.Columns)
{
if (dc.ColumnName == "GlobalRecordId")
{
continue;
}
if (row[dc.ColumnName] != DBNull.Value)
{
viewRow[dc.ColumnName] = row[dc];
}
//counter++;
//if (counter % 200 == 0 && inputs != null)
//{
// if (counter > total)
// {
// counter = total;
// }
// inputs.UpdateGadgetProgress(((double)counter / (double)total) * 100);
// if (total == 0)
// {
// inputs.UpdateGadgetStatus(SharedStrings.DASHBOARD_GADGET_STATUS_RELATING_PAGES_NO_PROGRESS);
// }
// else
// {
// inputs.UpdateGadgetStatus(string.Format(SharedStrings.DASHBOARD_GADGET_STATUS_RELATING_PAGES, ((double)counter / (double)total).ToString("P0")));
// }
// if (inputs.IsRequestCancelled())
// {
// return null;
// }
//}
}
}
viewRow.EndEdit();
}
}
unfilteredTable = viewTable;
}
return(unfilteredTable);
}
/// <summary>
/// Creates an XmlElement representing an Epi Info 7 view's data.
/// </summary>
/// <param name="xmlDataPackage">The data package xml document that the XmlElement should be added to</param>
/// <param name="form">The form whose data will be serialized</param>
/// <returns>XmlElement; represents the form's data in Xml format, suitable for use in data packaging</returns>
protected override XmlElement CreateXmlFormDataElement(XmlDocument xmlDataPackage, View form)
{
#region Input Validation
if (xmlDataPackage == null)
{
throw new ArgumentNullException("xmlDataPackage");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
XmlElement data = xmlDataPackage.CreateElement("Data");
OnStatusChanged(String.Format("Packaging data for form {0}...", form.Name));
OnResetProgress();
/* This seems like an usual set of steps to just iterate over the data. The problem is that we can't "just
* iterate over the data" - the data is split up page tables, with one table representing one page on the
* form. While a JOIN might be able to bring everything together into one table, it might not - for example,
* if there are >255 fields after the JOIN, an OleDb exception will be thrown.
*
* To get around this issue: The code first iterates over the rows in the BASE TABLE, obtaining the GUID
* values for each. The GUIDs and their corresponding XmlElement go into a dictionary.
*
* Later, each row in each page is iterated over; as the GUIDs for each page table are accessed, the corresponding
* XmlElement is pulled from the dictionary. Field data is added to it for each field that has data. In this
* manner, it doesn't matter that each row is technically accessed out-of-order because they'll still show up
* in-order in the resulting Xml.
*
* Filtering adds another layer of complexity. To filter, a JOIN operation is needed so that the filters can
* be applied across all those tables, since the fields in the filter may be across different tables. The
* RowFilter class provides a way to handle this; we simply get the query from that object and apply it to the
* reader. Only GUIDs that match the filter are added to the dictionary of guids.
*/
// We need to exclude records from child forms that may now be orphaned as a result of a filter applied to the parent
if (form.IsRelatedView && PreviousDistanceFromRoot < CurrentDistanceFromRoot)
{
ParentIdList.Clear();
foreach (KeyValuePair <string, XmlElement> kvp in IdList)
{
ParentIdList.Add(kvp.Key);
}
}
IdList.Clear(); // Very important, this needs to be re-set in case we've already processed a form (this is a class level variable)
if (!ExportInfo.RecordsPackaged.ContainsKey(form))
{
ExportInfo.RecordsPackaged.Add(form, 0);
}
//bool filterThisForm = false;
RowFilters filters = null;
Query selectQuery = null;
IDbDriver db = SourceProject.CollectedData.GetDatabase();
string recStatusClause = String.Empty;
if (Filters != null && Filters.ContainsKey(form.Name) && Filters[form.Name].Count() > 0)
{
//filterThisForm = true;
filters = Filters[form.Name];
filters.RecordProcessingScope = RecordProcessingScope;
selectQuery = filters.GetGuidSelectQuery(form);
List <QueryParameter> paramsToAdd = selectQuery.Parameters;
selectQuery = db.CreateQuery(selectQuery.SqlStatement.Replace("[t].[GlobalRecordId], [t].[FKEY], [t].[RECSTATUS]", "*"));
selectQuery.Parameters = paramsToAdd;
}
else
{
recStatusClause = "RECSTATUS = 1";
if (RecordProcessingScope == Epi.RecordProcessingScope.Both)
{
recStatusClause = "RECSTATUS >= 0";
}
else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted)
{
recStatusClause = "RECSTATUS = 0";
}
string selectQueryText = "SELECT * " + form.FromViewSQL;
selectQueryText = "SELECT * " + form.FromViewSQL + " WHERE " + recStatusClause;
selectQuery = db.CreateQuery(selectQueryText);
}
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM " + form.TableName)));
var fieldInclusionList = new List <RenderableField>();
foreach (Field field in form.Fields)
{
if (field is IDataField && field is RenderableField && !(field is GridField) && !(FieldsToNull.ContainsKey(form.Name) && FieldsToNull[form.Name].Contains(field.Name)))
{
var fieldToAdd = field as RenderableField;
if (fieldToAdd != null)
{
fieldInclusionList.Add(fieldToAdd);
}
}
}
int processedRecords = 0;
//using (IDataReader guidReader = db.ExecuteReader(selectQuery))
//using (IDataReader guidReader = filterThisForm ? db.ExecuteReader(selectQuery) : db.GetTableDataReader(form.TableName))
DataTable fullTable = db.Select(selectQuery);
//int lowKey = (int)db.ExecuteScalar(db.CreateQuery("SELECT Min(UniqueKey) FROM " + form.TableName));
//int highKey = (int)db.ExecuteScalar(db.CreateQuery("SELECT Max(UniqueKey) FROM " + form.TableName));
////ProcessRows(fullTable.Select("UniqueKey >= " + lowKey + " AND UniqueKey <= " + (highKey / 4)), form, xmlDataPackage, fieldInclusionList);
string set1 = String.Empty;
//string set2 = String.Empty;
//string set3 = String.Empty;
//string set4 = String.Empty;
//Parallel.Invoke(
// () =>
// {
set1 = ProcessRows(fullTable.Rows, form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set2 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 4) + " AND UniqueKey < " + (highKey / 2)), form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set3 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 2) + " AND UniqueKey < " + (highKey / 1.5)), form, xmlDataPackage, fieldInclusionList);
//},
//() =>
//{
// set4 = ProcessRows(fullTable.Select("UniqueKey >= " + (highKey / 1.5) + " AND UniqueKey <= " + highKey), form, xmlDataPackage, fieldInclusionList);
//}
//);
//StringBuilder sb = new StringBuilder();
//foreach (XmlElement element in set1)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set2)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set3)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
//foreach (XmlElement element in set4)
//{
// sb.Append(element.OuterXml);
// //data.AppendChild(element);
//}
data.InnerText = set1;
return(data);
foreach (DataRow guidReader in fullTable.Rows)
{
//using(var conn = new System.Data.SqlClient.SqlConnection(db.ConnectionString + ";Connection Timeout=10"))
//{
// conn.Open();
// using (var selectCommand = new System.Data.SqlClient.SqlCommand(selectQueryText, conn))
// {
// using (var guidReader = selectCommand.ExecuteReader())
// {
// while (guidReader.Read())
// {
string guid = guidReader["GlobalRecordId"].ToString();// guidReader.GetString(0); // guidReader["GlobalRecordId"].ToString();
string fkey = guidReader["FKEY"].ToString();
string recstatus = guidReader["RECSTATUS"].ToString();
string firstSaveUserId = String.Empty;
DateTime?firstSaveTime = null;
string lastSaveUserId = String.Empty;
DateTime?lastSaveTime = null;
firstSaveUserId = guidReader["FirstSaveLogonName"].ToString();
if (guidReader["FirstSaveTime"] != DBNull.Value)
{
firstSaveTime = (DateTime)guidReader["FirstSaveTime"];
}
lastSaveUserId = guidReader["LastSaveLogonName"].ToString();
if (guidReader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = (DateTime)guidReader["LastSaveTime"];
}
if (!form.IsRelatedView || ParentIdList.Contains(fkey))
{
XmlElement record = xmlDataPackage.CreateElement("Record");
XmlAttribute id = xmlDataPackage.CreateAttribute("Id");
id.Value = guid;
record.Attributes.Append(id);
if (!string.IsNullOrEmpty(fkey))
{
XmlAttribute foreignKey = xmlDataPackage.CreateAttribute("Fkey");
foreignKey.Value = fkey;
record.Attributes.Append(foreignKey);
}
if (!string.IsNullOrEmpty(firstSaveUserId))
{
XmlAttribute firstSaveId = xmlDataPackage.CreateAttribute("FirstSaveUserId");
firstSaveId.Value = firstSaveUserId;
record.Attributes.Append(firstSaveId);
}
if (!string.IsNullOrEmpty(lastSaveUserId))
{
XmlAttribute lastSaveId = xmlDataPackage.CreateAttribute("LastSaveUserId");
lastSaveId.Value = lastSaveUserId;
record.Attributes.Append(lastSaveId);
}
if (firstSaveTime.HasValue)
{
XmlAttribute firstSaveDateTime = xmlDataPackage.CreateAttribute("FirstSaveTime");
firstSaveDateTime.Value = firstSaveTime.Value.Ticks.ToString();
record.Attributes.Append(firstSaveDateTime);
}
if (lastSaveTime.HasValue)
{
XmlAttribute lastSaveDateTime = xmlDataPackage.CreateAttribute("LastSaveTime");
lastSaveDateTime.Value = lastSaveTime.Value.Ticks.ToString();
record.Attributes.Append(lastSaveDateTime);
}
if (!String.IsNullOrEmpty(recstatus))
{
XmlAttribute recStatusAttribute = xmlDataPackage.CreateAttribute("RecStatus");
recStatusAttribute.Value = recstatus;
record.Attributes.Append(recStatusAttribute);
}
IdList.Add(guid, record);
ExportInfo.TotalRecordsPackaged++;
ExportInfo.RecordsPackaged[form]++;
foreach (RenderableField field in fieldInclusionList)
{
XmlElement fieldData = xmlDataPackage.CreateElement("Field");
XmlAttribute name = xmlDataPackage.CreateAttribute("Name");
name.Value = field.Name;
fieldData.Attributes.Append(name);
string value = guidReader[field.Name].ToString();
if (!String.IsNullOrEmpty(value))
{
if (field is DateTimeField)
{
DateTime dt = Convert.ToDateTime(value);
fieldData.InnerText = dt.Ticks.ToString();
}
else if (field is ImageField)
{
value = Convert.ToBase64String((Byte[])guidReader[field.Name]);
fieldData.InnerText = value;
}
else if (field is NumberField)
{
value = Convert.ToDouble(value).ToString(System.Globalization.CultureInfo.InvariantCulture);
fieldData.InnerText = value;
}
else
{
fieldData.InnerText = value;
}
}
if (String.IsNullOrEmpty(fieldData.InnerText) && IncludeNullFieldData == false)
{
// do nothing, for now...
}
else
{
record.AppendChild(fieldData);
}
data.AppendChild(record);
}
}
processedRecords++;
double progress = (((double)processedRecords) / ((double)totalRecords)) * 100;
OnProgressChanged(progress);
}
foreach (GridField gridField in form.Fields.GridFields)
{
data.AppendChild(CreateXmlGridElement(xmlDataPackage, form, gridField));
ExportInfo.GridsProcessed++;
}
return(data);
}
