/// <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."); } } } } //); }