public static object GetScalar(string pFileString, string pSQL)
{
object retval = null;
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
retval = driver.ExecuteScalar(driver.CreateQuery(pSQL));
}
return(retval);
}
/// <summary>
/// Returns the count of tables
/// </summary>
/// <returns></returns>
public int GetRecordCount(Epi2000.View view)
{
try
{
#region Input Validation
if (view == null)
{
throw new System.ArgumentNullException("view");
}
#endregion Input Validation
string qryString = " select count(*) from " + db.InsertInEscape(view.TableNames[0]);
Query query = db.CreateQuery(qryString);
return(Int32.Parse((db.ExecuteScalar(query)).ToString()));
}
catch (Exception ex)
{
throw new ApplicationException("Could not retrieve record count.", ex); //TODO: move to shared strings
}
finally
{
}
}
///// <summary>
///// Creates a view in a specified project
///// </summary>
///// <old-param name="isrelatedview">Whether or not this view is a related (child) view</old-param>
///// <old-param name="viewname">Name of the view</old-param>
//public void InsertView(View view)
//{
// #region Input Validation
// if (view == null)
// {
// throw new ArgumentNullException("view");
// }
// #endregion Input Validation
// try
// {
// Query insertQuery = db.CreateQuery("insert into metaViews([Name], [IsRelatedView]) values (@Name, @IsRelatedView)");
// insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, view.Name));
// insertQuery.Parameters.Add(new QueryParameter("@IsRelatedView", DbType.Boolean, view.IsRelatedView));
// db.ExecuteNonQuery(insertQuery);
// view.Id = this.GetMaxViewId();
// // Insert system fields .. RECSTATUS and UNIQUEKEY
// RecStatusField recStatusField = new RecStatusField(view);
// UniqueKeyField uniqueKeyField = new UniqueKeyField(view);
// uniqueKeyField.SaveToDb();
// recStatusField.SaveToDb();
// }
// catch (Exception ex)
// {
// throw new GeneralException("Could not create view in the database", ex);
// }
// finally
// {
// }
//}
/// <summary>
/// Returns the Id of the last view added
/// </summary>
/// <returns></returns>
public int GetMaxViewId()
{
Query selectQuery = db.CreateQuery("select MAX(ViewId) from metaViews");
return((int)db.ExecuteScalar(selectQuery));
}
void computeWorker_DoWork(object sender, DoWorkEventArgs e)
{
Result result = new Result();
EpiDataHelper DataHelper = e.Argument as EpiDataHelper;
Epi.Fields.DateTimeField dtField = DataHelper.LabForm.Fields["DateSampleCollected"] as Epi.Fields.DateTimeField;
if (DataHelper != null && dtField != null)
{
DateTime today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
int count = IsoCount; // (DataHelper.IsolatedCollectionView as ListCollectionView).Count;//.View.Cast<CaseViewModel>().Count();
result.IsoCurrentT = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where caseVM.DateDeathCurrentOrFinal.HasValue &&
(caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable) &&
caseVM.DateDeathCurrentOrFinal.HasValue &&
caseVM.DateDeathCurrentOrFinal.Value.Day == today.Day &&
caseVM.DateDeathCurrentOrFinal.Value.Month == today.Month &&
caseVM.DateDeathCurrentOrFinal.Value.Year == today.Year
select caseVM).Count();
result.NewDeathsT = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where (caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Suspect) &&
caseVM.DateReport.HasValue &&
caseVM.DateReport.Value.Day == today.Day &&
caseVM.DateReport.Value.Month == today.Month &&
caseVM.DateReport.Value.Year == today.Year
select caseVM).Count();
result.NewCasesT = count.ToString();
IDbDriver db = DataHelper.Project.CollectedData.GetDatabase();
string queryText = "select count(*) FROM " + dtField.Page.TableName + " " +
"WHERE [DateSampleCollected] >= @Today";
Query selectQuery = db.CreateQuery(queryText);
selectQuery.Parameters.Add(new QueryParameter("@Today", System.Data.DbType.Date, DateTime.Today));
count = (int)db.ExecuteScalar(selectQuery);
result.PendingT = count.ToString();
#region Yesterday
DateTime yesterday = (DateTime.Today).AddDays(-1);
count = (from caseVM in DataHelper.CaseCollection
where caseVM.DateDeathCurrentOrFinal.HasValue &&
(caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable) &&
caseVM.DateDeathCurrentOrFinal.HasValue &&
caseVM.DateDeathCurrentOrFinal.Value.Day == yesterday.Day &&
caseVM.DateDeathCurrentOrFinal.Value.Month == yesterday.Month &&
caseVM.DateDeathCurrentOrFinal.Value.Year == yesterday.Year
select caseVM).Count();
result.NewDeathsY = count.ToString();
count = (from caseVM in DataHelper.CaseCollection
where (caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Confirmed || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Probable || caseVM.EpiCaseDef == Core.Enums.EpiCaseClassification.Suspect) &&
caseVM.DateReport.HasValue &&
caseVM.DateReport.Value.Day == yesterday.Day &&
caseVM.DateReport.Value.Month == yesterday.Month &&
caseVM.DateReport.Value.Year == yesterday.Year
select caseVM).Count();
result.NewCasesY = count.ToString();
queryText = "select count(*) FROM " + dtField.Page.TableName + " " +
"WHERE [DateSampleCollected] < @Today AND [DateSampleCollected] >= @Yesterday";
selectQuery = db.CreateQuery(queryText);
selectQuery.Parameters.Add(new QueryParameter("@Today", System.Data.DbType.Date, DateTime.Today));
selectQuery.Parameters.Add(new QueryParameter("@Yesterday", System.Data.DbType.Date, yesterday));
count = (int)db.ExecuteScalar(selectQuery);
result.PendingY = count.ToString();
#endregion // Yesterday
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");
}
}
}
protected virtual void WriteFollowUpsData(XmlWriter writer)
{
MinorProgress = 0;
OnMinorProgressChanged();
string selectQueryText = "SELECT * FROM metaHistory";
IDbDriver db = Project.CollectedData.GetDatabase();
CultureInfo format = CultureInfo.InvariantCulture;
if (!db.TableExists("metaHistory"))
{
return;
}
writer.WriteStartElement("ContactFollowUps");
Query selectQuery = db.CreateQuery(selectQueryText);
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM metaHistory")));
double inc = 100 / totalRecords;
bool filter = _contactGuids != null;
string contactFormId = Project.Views[Core.Constants.CONTACT_FORM_NAME].Id.ToString();
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
while (reader.Read())
{
string contactGuid = reader["ContactGUID"].ToString();
if (filter && !_contactGuids.Contains(contactGuid))
{
continue;
}
writer.WriteStartElement("ContactFollowUp");
#region Followup Fields
writer.WriteStartElement("ContactGUID");
writer.WriteString(contactGuid);
writer.WriteEndElement();
writer.WriteStartElement("FollowUpDate");
writer.WriteString(Convert.ToDateTime(reader["FollowUpDate"]).ToString(format.DateTimeFormat.ShortDatePattern));
writer.WriteEndElement();
if (!String.IsNullOrEmpty(reader["StatusOnDate"].ToString()))
{
writer.WriteStartElement("StatusOnDate");
writer.WriteString(reader["StatusOnDate"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Note"].ToString()))
{
writer.WriteStartElement("Note");
writer.WriteString(reader["Note"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Temp1"].ToString()))
{
writer.WriteStartElement("Temp1");
writer.WriteString(reader["Temp1"] == DBNull.Value ? String.Empty : Convert.ToDouble(reader["Temp1"]).ToString(System.Globalization.CultureInfo.InvariantCulture));
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Temp2"].ToString()))
{
writer.WriteStartElement("Temp2");
writer.WriteString(reader["Temp2"] == DBNull.Value ? String.Empty : Convert.ToDouble(reader["Temp2"]).ToString(System.Globalization.CultureInfo.InvariantCulture));
writer.WriteEndElement();
}
#endregion // Followup Fields
writer.WriteEndElement();
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of contact tracing records exported...", (MinorProgress / 100).ToString("P0")));
}
}
writer.WriteEndElement();
}
protected virtual void WriteLinksData(XmlWriter writer)
{
MinorProgress = 0;
OnMinorProgressChanged();
writer.WriteStartElement("Links");
string selectQueryText = "SELECT * FROM metaLinks";
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = db.CreateQuery(selectQueryText);
CultureInfo format = CultureInfo.InvariantCulture;
double totalRecords = Convert.ToDouble(db.ExecuteScalar(db.CreateQuery("SELECT COUNT(*) FROM metaLinks")));
double inc = 100 / totalRecords;
bool filter = _contactGuids != null;
string contactFormId = Project.Views[Core.Constants.CONTACT_FORM_NAME].Id.ToString();
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
while (reader.Read())
{
string fromRecordGuid = reader["FromRecordGuid"].ToString();
string toRecordGuid = reader["ToRecordGuid"].ToString();
string toViewId = reader["ToViewId"].ToString();
if (!_includeContacts && toViewId == contactFormId)
{
continue;
}
if (filter && toViewId == contactFormId && !_contactGuids.Contains(toRecordGuid))
{
continue;
}
writer.WriteStartElement("Link");
#region Link Fields
writer.WriteStartElement("FromRecordGuid");
writer.WriteString(fromRecordGuid);
writer.WriteEndElement();
writer.WriteStartElement("ToRecordGuid");
writer.WriteString(toRecordGuid);
writer.WriteEndElement();
writer.WriteStartElement("FromViewId");
writer.WriteString(reader["FromViewId"].ToString());
writer.WriteEndElement();
writer.WriteStartElement("ToViewId");
writer.WriteString(toViewId);
writer.WriteEndElement();
writer.WriteStartElement("LastContactDate");
//writer.WriteString(Convert.ToDateTime(reader["LastContactDate"]).Ticks.ToString());
writer.WriteString(Convert.ToDateTime(reader["LastContactDate"]).ToString(format.DateTimeFormat.ShortDatePattern));
writer.WriteEndElement();
if (!String.IsNullOrEmpty(reader["ContactType"].ToString()))
{
writer.WriteStartElement("ContactType");
writer.WriteString(reader["ContactType"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["RelationshipType"].ToString()))
{
writer.WriteStartElement("RelationshipType");
writer.WriteString(reader["RelationshipType"].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader["Tentative"].ToString()))
{
writer.WriteStartElement("Tentative");
writer.WriteString(reader["Tentative"].ToString());
writer.WriteEndElement();
}
writer.WriteStartElement("IsEstimatedContactDate");
writer.WriteString(reader["IsEstimatedContactDate"].ToString());
writer.WriteEndElement();
for (int i = 1; i <= 21; i++)
{
string dayName = "Day" + i.ToString();
string dayNotesName = dayName + "Notes";
if (!String.IsNullOrEmpty(reader[dayName].ToString()))
{
writer.WriteStartElement(dayName);
writer.WriteString(reader[dayName].ToString());
writer.WriteEndElement();
}
if (!String.IsNullOrEmpty(reader[dayNotesName].ToString()))
{
writer.WriteStartElement(dayNotesName);
writer.WriteString(reader[dayNotesName].ToString());
writer.WriteEndElement();
}
}
writer.WriteStartElement("LinkId");
writer.WriteString(reader["LinkId"].ToString());
writer.WriteEndElement();
#endregion // Link Fields
writer.WriteEndElement();
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of relationship records exported...", (MinorProgress / 100).ToString("P0")));
}
}
writer.WriteEndElement();
}
protected virtual void WriteFormData(XmlWriter writer, View form)
{
if (form.Fields.DataFields.Count > Core.Constants.EXPORT_FIELD_LIMIT && Project.CollectedData.GetDatabase() is Epi.Data.Office.OleDbDatabase)
{
// OleDB can't handle a SELECT * with a lot of fields, so do page-by-page processing instead
WriteFormPagedData(writer, form);
return;
}
MinorProgress = 0;
OnMinorProgressChanged();
List <string> fieldsToNull = FieldsToNull[form.Name];
writer.WriteStartElement("Form");
writer.WriteAttributeString("Name", form.Name);
writer.WriteAttributeString("Pages", form.Pages.Count.ToString());
writer.WriteAttributeString("IsRelatedForm", form.IsRelatedView.ToString());
WriteFormMetadata(writer, form);
writer.WriteStartElement("Data");
IDbDriver db = Project.CollectedData.GetDatabase();
Query selectQuery = GetFormSelectQuery(form);
List <string> labGuids = null;
OnMinorStatusChanged("Applying filters for " + form.Name + "...");
if (Filters.ContainsKey(Core.Constants.CASE_FORM_NAME) && Filters[Core.Constants.CASE_FORM_NAME].Count() > 0 && _caseGuids != null)
{
if (form.Name.Equals(Core.Constants.CONTACT_FORM_NAME, StringComparison.OrdinalIgnoreCase))
{
_contactGuids = new List <string>();
Query guidSelectQuery = db.CreateQuery("SELECT C.GlobalRecordId, M.FromRecordGuid FROM (ContactEntryForm C INNER JOIN metaLinks M ON C.GlobalRecordId = M.ToRecordGuid) WHERE M.ToViewId = @ToViewId AND M.FromViewId = 1");
guidSelectQuery.Parameters.Add(new QueryParameter("@ToViewId", DbType.Int32, ContactFormId));
using (IDataReader reader = db.ExecuteReader(guidSelectQuery))
{
while (reader.Read())
{
string caseGuid = reader["FromRecordGuid"].ToString();
if (_caseGuids.Contains(caseGuid))
{
_contactGuids.Add(reader["GlobalRecordId"].ToString());
}
}
}
}
else if (form.Name.Equals(Core.Constants.LAB_FORM_NAME))
{
labGuids = new List <string>();
Query guidSelectQuery = db.CreateQuery("SELECT L.GlobalRecordId, L.FKEY FROM (CaseInformationForm C INNER JOIN LaboratoryResultsForm L ON C.GlobalRecordId = L.FKEY)");
using (IDataReader reader = db.ExecuteReader(guidSelectQuery))
{
while (reader.Read())
{
string labGuid = reader["GlobalRecordId"].ToString();
string caseGuid = reader["FKEY"].ToString();
if (_caseGuids.Contains(caseGuid))
{
labGuids.Add(reader["GlobalRecordId"].ToString());
}
}
}
}
}
OnMinorStatusChanged("Getting total row counts for " + form.Name + "...");
string recStatusClause = "RECSTATUS = 1";
if (Scope == Epi.RecordProcessingScope.Both)
{
recStatusClause = "RECSTATUS >= 0";
}
else if (Scope == Epi.RecordProcessingScope.Deleted)
{
recStatusClause = "RECSTATUS = 0";
}
Query countQuery = db.CreateQuery("SELECT COUNT(*) FROM " + form.TableName + " WHERE " + recStatusClause + " AND ((LastSaveTime >= @StartDate AND LastSaveTime <= @EndDate) OR LastSaveTime IS NULL)");
countQuery.Parameters.Add(new QueryParameter("@StartDate", DbType.DateTime, StartDate));
countQuery.Parameters.Add(new QueryParameter("@EndDate", DbType.DateTime, EndDate));
double totalRecords = Convert.ToDouble(db.ExecuteScalar(countQuery));
double inc = 100 / totalRecords;
bool isCaseForm = form.Name.Equals(Core.Constants.CASE_FORM_NAME, StringComparison.OrdinalIgnoreCase);
if (isCaseForm)
{
_caseGuids = new HashSet <string>();
}
using (IDataReader reader = db.ExecuteReader(selectQuery))
{
//int i = 1;
while (reader.Read())
{
string recordGuid = reader["t.GlobalRecordId"].ToString();
string lastSaveTimeStr = String.Empty;
long? lastSaveTimeLong = null;
DateTime?lastSaveTime = null;
if (reader["LastSaveTime"] != DBNull.Value)
{
lastSaveTime = Convert.ToDateTime(reader["LastSaveTime"]);
lastSaveTimeLong = lastSaveTime.Value.Ticks;
lastSaveTimeStr = lastSaveTimeLong.ToString();
if (lastSaveTime < StartDate || lastSaveTime > EndDate)
{
MinorProgress += inc;
OnMinorProgressChanged();
OnMinorStatusChanged(String.Format("{0} of records exported from " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
if (isCaseForm)
{
// we want to add the GUID here so related records (e.g. contacts and labs) don't get excluded because
// their case wasn't in the date range.
_caseGuids.Add(recordGuid);
}
continue;
}
}
if (form.Name.Equals(Core.Constants.CONTACT_FORM_NAME, StringComparison.OrdinalIgnoreCase) && _contactGuids != null && !_contactGuids.Contains(recordGuid))
{
continue;
}
if (form.Name.Equals(Core.Constants.LAB_FORM_NAME, StringComparison.OrdinalIgnoreCase) && labGuids != null && !labGuids.Contains(recordGuid))
{
continue;
}
writer.WriteStartElement("Record");
writer.WriteAttributeString("Id", recordGuid);
writer.WriteAttributeString("FKEY", reader["FKEY"] == DBNull.Value ? String.Empty : reader["FKEY"].ToString());
writer.WriteAttributeString("FirstSaveUserId", reader["FirstSaveLogonName"].ToString());
writer.WriteAttributeString("LastSaveUserId", reader["LastSaveLogonName"].ToString());
if (reader["FirstSaveTime"] != DBNull.Value)
{
writer.WriteAttributeString("FirstSaveTime", Convert.ToDateTime(reader["FirstSaveTime"]).Ticks.ToString());
}
else
{
writer.WriteAttributeString("FirstSaveTime", String.Empty);
}
writer.WriteAttributeString("LastSaveTime", lastSaveTimeStr);
writer.WriteAttributeString("RecStatus", reader["RecStatus"].ToString());
foreach (IDataField dataField in form.Fields.DataFields)
{
RenderableField field = dataField as RenderableField;
if (field == null || dataField is UniqueKeyField || fieldsToNull.Contains(field.Name))
{
continue;
}
else
{
if (reader[field.Name] != DBNull.Value && !String.IsNullOrEmpty(reader[field.Name].ToString()))
{
writer.WriteStartElement(field.Name);
WriteFieldValue(writer, reader, field);
writer.WriteEndElement();
}
}
}
writer.WriteEndElement(); // record
ExportInfo.RecordsPackaged[form]++;
MinorProgress += inc;
OnMinorProgressChanged();
//OnMinorStatusChanged(String.Format("{1} ({0}) of records exported from " + form.Name + "...", i.ToString(), (MinorProgress / 100).ToString("P0")));
OnMinorStatusChanged(String.Format("{0} of records exported from " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
//i++;
}
}
writer.WriteEndElement(); // data element
writer.WriteEndElement(); // form element
}
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>
/// 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);
}
