/// <summary>
/// Sets the appropriate properties for a given field, based on the specified column conversion information
/// </summary>
/// <param name="field">The Epi Info 7 field</param>
/// <param name="cci">The column conversion information</param>
private void SetFieldProperties(Field field, ColumnConversionInfo cci)
{
if (cci.Prompt == null)
{
cci.Prompt = cci.DestinationColumnName;
}
switch (field.FieldType)
{
case MetaFieldType.Checkbox:
CheckBoxField checkboxField = (CheckBoxField)field;
checkboxField.TabIndex = cci.TabIndex;
checkboxField.IsReadOnly = cci.IsReadOnly;
checkboxField.IsRequired = cci.IsRequired;
checkboxField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.YesNo:
YesNoField yesNoField = (YesNoField)field;
yesNoField.TabIndex = cci.TabIndex;
yesNoField.IsReadOnly = cci.IsReadOnly;
yesNoField.IsRequired = cci.IsRequired;
yesNoField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Text:
SingleLineTextField textField = (SingleLineTextField)field;
textField.TabIndex = cci.TabIndex;
textField.IsReadOnly = cci.IsReadOnly;
textField.IsRequired = cci.IsRequired;
textField.ShouldRepeatLast = cci.IsRepeatLast;
if (cci.UpperBound is int)
{
textField.MaxLength = (int)cci.UpperBound;
}
break;
case MetaFieldType.Multiline:
MultilineTextField multilineTextField = (MultilineTextField)field;
multilineTextField.TabIndex = cci.TabIndex;
multilineTextField.IsReadOnly = cci.IsReadOnly;
multilineTextField.IsRequired = cci.IsRequired;
multilineTextField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Date:
DateField dateField = (DateField)field;
dateField.TabIndex = cci.TabIndex;
dateField.IsReadOnly = cci.IsReadOnly;
dateField.IsRequired = cci.IsRequired;
dateField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.DateTime:
DateTimeField dateTimeField = (DateTimeField)field;
dateTimeField.TabIndex = cci.TabIndex;
dateTimeField.IsReadOnly = cci.IsReadOnly;
dateTimeField.IsRequired = cci.IsRequired;
dateTimeField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Time:
TimeField timeField = (TimeField)field;
timeField.TabIndex = cci.TabIndex;
timeField.IsReadOnly = cci.IsReadOnly;
timeField.IsRequired = cci.IsRequired;
timeField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.Number:
NumberField numberField = (NumberField)field;
numberField.TabIndex = cci.TabIndex;
numberField.IsReadOnly = cci.IsReadOnly;
numberField.IsRequired = cci.IsRequired;
numberField.ShouldRepeatLast = cci.IsRepeatLast;
break;
case MetaFieldType.LegalValues:
DDLFieldOfLegalValues legalValuesField = (DDLFieldOfLegalValues)field;
legalValuesField.TabIndex = cci.TabIndex;
legalValuesField.IsReadOnly = cci.IsReadOnly;
legalValuesField.IsRequired = cci.IsRequired;
legalValuesField.ShouldRepeatLast = cci.IsRepeatLast;
if (string.IsNullOrEmpty(cci.ListSourceTableName))
{
DataTable dt = new DataTable(cci.SourceColumnName);
dt.Columns.Add(new DataColumn(cci.SourceColumnName, typeof(string)));
// table is blank, so assume user wants to use a SELECT DISTINCT as the value source
Query selectDistinctQuery = sourceDriver.CreateQuery("SELECT DISTINCT [" + cci.SourceColumnName + "] FROM [" + tableName + "]");
IDataReader distinctReader = sourceDriver.ExecuteReader(selectDistinctQuery);
while (distinctReader.Read())
{
dt.Rows.Add(distinctReader[0].ToString());
}
cci.ListSourceTable = dt;
cci.ListSourceTableName = cci.SourceColumnName;
cci.ListSourceTextColumnName = cci.SourceColumnName;
IDbDriver db = project.CollectedData.GetDatabase();
if (!db.TableExists(cci.ListSourceTableName))
{
project.CreateCodeTable(cci.ListSourceTableName, cci.ListSourceTextColumnName);
project.SaveCodeTableData(cci.ListSourceTable, cci.ListSourceTableName, cci.ListSourceTextColumnName);
}
legalValuesField.SourceTableName = cci.ListSourceTableName;
legalValuesField.TextColumnName = cci.ListSourceTextColumnName;
legalValuesField.CodeColumnName = cci.ListSourceTextColumnName;
}
else
{
IDbDriver db = project.CollectedData.GetDatabase();
if (!db.TableExists(cci.ListSourceTableName))
{
project.CreateCodeTable(cci.ListSourceTableName, cci.ListSourceTextColumnName);
string[] columns = new string[1];
columns[0] = cci.ListSourceTextColumnName;
project.InsertCodeTableData(cci.ListSourceTable, cci.ListSourceTableName, columns);
}
legalValuesField.SourceTableName = cci.ListSourceTableName;
legalValuesField.TextColumnName = cci.ListSourceTextColumnName;
legalValuesField.CodeColumnName = cci.ListSourceTextColumnName;
}
break;
default:
throw new ApplicationException("Invalid field type");
//break;
}
double ControlHeightPercentage = 0.0;
double ControlWidthPercentage = 0.0;
if (field is FieldWithSeparatePrompt)
{
FieldWithSeparatePrompt fieldWithPrompt;
fieldWithPrompt = (FieldWithSeparatePrompt)field;
fieldWithPrompt.PromptText = cci.Prompt;
fieldWithPrompt.PromptFont = cci.PromptFont;
fieldWithPrompt.ControlFont = cci.ControlFont;
fieldWithPrompt.PromptLeftPositionPercentage = cci.ControlLeftPosition / 100;
fieldWithPrompt.PromptTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithPrompt.Name = cci.DestinationColumnName;
fieldWithPrompt.ControlHeightPercentage = ControlHeightPercentage / 100;
fieldWithPrompt.ControlWidthPercentage = ControlWidthPercentage / 100;
fieldWithPrompt.ControlTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithPrompt.ControlLeftPositionPercentage = (cci.ControlLeftPosition / 100) + 0.090702947845805;
fieldWithPrompt.UpdatePromptPosition();
fieldWithPrompt.UpdateControlPosition();
}
else
{
FieldWithoutSeparatePrompt fieldWithoutPrompt;
fieldWithoutPrompt = (FieldWithoutSeparatePrompt)field;
fieldWithoutPrompt.PromptText = cci.Prompt;
fieldWithoutPrompt.PromptFont = cci.PromptFont;
fieldWithoutPrompt.Name = cci.DestinationColumnName;
fieldWithoutPrompt.ControlHeightPercentage = ControlHeightPercentage / 100;
fieldWithoutPrompt.ControlWidthPercentage = ControlWidthPercentage / 100;
fieldWithoutPrompt.ControlTopPositionPercentage = cci.ControlTopPosition / 100;
fieldWithoutPrompt.ControlLeftPositionPercentage = (cci.ControlLeftPosition / 100) + 0.090702947845805;
fieldWithoutPrompt.UpdateControlPosition();
}
}
/// <summary>
/// Gets list of tables in the EI 3.x metadata database
/// </summary>
/// <returns>DataRow of table names</returns>
//public override DataTable GetViewsAsDataTable()
public DataTable GetViewsAsDataTable()
{
try
{
DataTable viewTable = GetDataTableTemplateForViewInfo();
foreach (string viewName in this.GetViewNames())
{
DataRow viewRow = viewTable.NewRow();
viewTable.Rows.Add(viewRow);
string queryText = string.Empty;
Query query;
DataTable tempTable;
// Name ...
viewRow[ColumnNames.NAME] = viewName;
List <string> tableNames = new List <string>();
Query selectQuery = db.CreateQuery("SELECT DISTINCT Datatable FROM " + viewName + "");
IDataReader reader = db.ExecuteReader(selectQuery);
reader = db.ExecuteReader(selectQuery);
while (reader.Read())
{
string name = reader["DATATABLE"].ToString();
if (name.StartsWith("DATA") && !name.Contains(","))
{
tableNames.Add(name);
}
}
if (tableNames.Count <= 1)
{
// Data Table
queryText = "select [DATATABLE] from " + viewName + " where [Name] = 'DATA1'";
query = db.CreateQuery(queryText);
tempTable = db.Select(query);
if (tempTable.Rows.Count > 0)
{
viewRow[ColumnNames.DATA_TABLE_NAME] = tempTable.Rows[0]["DATATABLE"].ToString();
}
}
else if (tableNames.Count > 1)
{
viewRow[ColumnNames.DATA_TABLE_NAME] = string.Empty;
foreach (string s in tableNames)
{
// Data Table
queryText = "select [DATATABLE] from " + viewName + " where [Name] = @Name";
query = db.CreateQuery(queryText);
query.Parameters.Add(new QueryParameter("@Name", DbType.String, s));
tempTable = db.Select(query);
if (tempTable.Rows.Count > 0)
{
viewRow[ColumnNames.DATA_TABLE_NAME] = viewRow[ColumnNames.DATA_TABLE_NAME].ToString() + tempTable.Rows[0]["DATATABLE"].ToString() + ";";
}
}
viewRow[ColumnNames.DATA_TABLE_NAME] = viewRow[ColumnNames.DATA_TABLE_NAME].ToString().TrimEnd(';');
}
// CheckCode variable definitions
query = db.CreateQuery("select [Checkcode] from " + viewName + " where [Name] = 'DEFINEDVARIABLES'");
tempTable = db.Select(query);
if (tempTable.Rows.Count > 0)
{
viewRow[ColumnNames.CHECK_CODE_VARIABLE_DEFINITIONS] = tempTable.Rows[0][ColumnNames.CHECK_CODE].ToString();
}
// CheckCode Before and After
query = db.CreateQuery("select [Checkcode] from " + viewName + " where [Name] = 'VIEW'");
tempTable = db.Select(query);
if (tempTable.Rows.Count > 0)
{
string checkCode = tempTable.Rows[0][ColumnNames.CHECK_CODE].ToString();
string checkCodeBefore = string.Empty;
string checkCodeAfter = string.Empty;
SplitCheckCode(checkCode, ref checkCodeBefore, ref checkCodeAfter);
viewRow[ColumnNames.CHECK_CODE_BEFORE] = checkCodeBefore;
viewRow[ColumnNames.CHECK_CODE_AFTER] = checkCodeAfter;
}
// Record Check code Before and Afters
query = db.CreateQuery("select [Checkcode] from " + viewName + " where [Name] = 'RECORD'");
tempTable = db.Select(query);
if (tempTable.Rows.Count > 0)
{
string recordCheckCode = tempTable.Rows[0][ColumnNames.CHECK_CODE].ToString();
string recordCheckCodeBefore = string.Empty;
string recordCheckCodeAfter = string.Empty;
SplitCheckCode(recordCheckCode, ref recordCheckCodeBefore, ref recordCheckCodeAfter);
viewRow[ColumnNames.RECORD_CHECK_CODE_BEFORE] = recordCheckCodeBefore;
viewRow[ColumnNames.RECORD_CHECK_CODE_AFTER] = recordCheckCodeAfter;
}
}
return(viewTable);
}
finally
{
}
}
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
}
protected virtual void WriteFormPagedData(XmlWriter writer, View form)
{
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();
using (OleDbConnection conn = new OleDbConnection(db.ConnectionString))
{
conn.Open();
double totalRecords = -1;
using (OleDbCommand command = new OleDbCommand("SELECT COUNT(*) FROM " + form.TableName, conn))
{
totalRecords = Convert.ToDouble(command.ExecuteScalar());
}
double inc = 100 / totalRecords;
//List<string> guids = new List<string>();
HashSet <string> guids = new HashSet <string>();
Query guidQuery = GetFormSelectQuery(form, false);
using (IDataReader guidReader = db.ExecuteReader(guidQuery))
{
int i = 0;
while (guidReader.Read())
{
string globalRecordId = guidReader["GlobalRecordId"].ToString();
//guids[i] = globalRecordId;
guids.Add(globalRecordId);
i++;
}
}
if (form.Name.Equals(Core.Constants.CASE_FORM_NAME, StringComparison.OrdinalIgnoreCase))
{
_caseGuids = guids;
}
HashSet <string> guidsWritten = new HashSet <string>();
foreach (Page page in form.Pages)
{
Query selectQuery = db.CreateQuery("SELECT * FROM " + page.TableName + " p INNER JOIN " + form.TableName + " f ON p.GlobalRecordId = f.GlobalRecordId");
using (OleDbCommand command = new OleDbCommand(selectQuery.SqlStatement, conn))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string globalRecordId = reader["p.GlobalRecordId"].ToString();
if (!guids.Contains(globalRecordId))
{
continue;
}
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} exported for " + form.Name + "...", (MinorProgress / 100).ToString("P0")));
continue;
}
}
if (!guidsWritten.Contains(globalRecordId))
{
guidsWritten.Add(globalRecordId);
}
writer.WriteStartElement("Record");
writer.WriteAttributeString("Id", globalRecordId);
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 (RenderableField field in page.Fields)
{
if (field == null || fieldsToNull.Contains(field.Name) || !(field is IDataField))
{
continue;
}
else
{
if (reader[field.Name] != DBNull.Value && !String.IsNullOrEmpty(reader[field.Name].ToString()))
{
writer.WriteStartElement(field.Name);
//writer.WriteAttributeString("Name", field.Name);
WriteFieldValue(writer, reader, field);
writer.WriteEndElement();
}
}
}
writer.WriteEndElement();
}
}
}
}
ExportInfo.RecordsPackaged[form] = guidsWritten.Count;
}
writer.WriteEndElement(); // data element
writer.WriteEndElement(); // form element
}
/// <summary>
/// Begins the process of importing records from the data package into the destination form
/// </summary>
/// <param name="form">The form that will receive the data</param>
/// <param name="formNode">The XmlNode representing the form</param>
/// <param name="records">The data to be imported</param>
protected override void ImportRecords(View form, XmlNode formNode, List <PackageFieldData> records)
{
if (!IsUsingCustomMatchkeys) // Calling class should instantiate normal data packager if custom keys aren't used
{
throw new ApplicationException("This class should not be used without custom match keys.");
}
ImportInfo.RecordsAppended.Add(form, 0);
ImportInfo.RecordsUpdated.Add(form, 0);
IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase();
DataTable destinationKeyTable = new DataTable();
destinationKeyTable.Columns.Add(new DataColumn("GlobalRecordId", typeof(string)));
destinationKeyTable.Columns.Add(new DataColumn("Update", typeof(bool)));
destinationKeyTable.Columns.Add(new DataColumn("KeyDictionary", typeof(Dictionary <Field, object>)));
DataColumn [] primaryKey = new DataColumn[1];
primaryKey[0] = destinationKeyTable.Columns["GlobalRecordId"];
destinationKeyTable.PrimaryKey = primaryKey;
WordBuilder wb = new WordBuilder(",");
wb.Add("t.GlobalRecordId");
foreach (Field field in KeyFields)
{
wb.Add(field.Name);
}
Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
var query = from record in records
group record by record.RecordGUID;
IEnumerable <IEnumerable <PackageFieldData> > fieldDataLists = query as IEnumerable <IEnumerable <PackageFieldData> >;
foreach (IEnumerable <PackageFieldData> fieldDataList in fieldDataLists)
{
PackageFieldData fieldData = fieldDataList.First();
bool found = false;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, fieldData.KeyValues))
{
found = true;
if (!Update)
{
row["Update"] = false;
}
else
{
ImportInfo.TotalRecordsUpdated++;
ImportInfo.RecordsUpdated[form]++;
}
}
}
if (!found && Append) // no match, this is a new record that must be inserted
{
CreateNewBlankRow(form, fieldData.RecordGUID, fieldData.KeyValues);
ImportInfo.TotalRecordsAppended++;
ImportInfo.RecordsAppended[form]++;
}
}
System.Threading.Thread.Sleep(2000); // give time for DB to update
destinationKeyTable.Clear();
selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
//Parallel.ForEach(records, rec =>
// TODO: Make this faster (note that Parallel foreach seems to make it worse)
foreach (PackageFieldData rec in records)
{
bool found = false;
string targetGuid = String.Empty;
bool shouldUpdate = true;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, rec.KeyValues))
{
found = true;
targetGuid = row["GlobalRecordId"].ToString();
shouldUpdate = (bool)row["Update"];
break;
}
}
if (shouldUpdate && found && !String.IsNullOrEmpty(targetGuid) && rec.FieldValue != null && !String.IsNullOrEmpty(rec.FieldValue.ToString()))
{
Query updateQuery = destinationDb.CreateQuery("UPDATE " + rec.Page.TableName + " SET " +
"[" + rec.FieldName + "] = @" + rec.FieldName + " WHERE [GlobalRecordId] = @GlobalRecordId");
QueryParameter fieldParam = GetQueryParameterForField(rec, form, rec.Page);
if (fieldParam != null)
{
updateQuery.Parameters.Add(fieldParam);
updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, targetGuid));
int rowsAffected = destinationDb.ExecuteNonQuery(updateQuery);
if (rowsAffected == 0)
{
throw new ApplicationException("No records affected.");
}
else if (rowsAffected > 1)
{
throw new ApplicationException("Too many records affected.");
}
}
}
}
//);
}
