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); }