public Datadictionary Datadictionary(List <string> formnames) { DataTable dt_meta = dt_metadata.AsEnumerable().Where(f => formnames.Contains(f.Field <string>("form_name"))).CopyToDataTable(); Datadictionary dict = new Datadictionary(dt_meta, formnames, _studyID); return(dict); }
public Datadictionary Datadictionary(string formname) { Datadictionary dict = Datadictionary(new List <string> { formname }); return(dict); }
protected void AddPivot(ASPxPanel panel, DataTable dtvars, int mymeasureid, Datadictionary dict) { ASPxPivotGrid pivot = new ASPxPivotGrid(); pivot.ID = mymeasureid.ToString(); pivot.DataSource = dtvars; pivot.DataBind(); pivot.RetrieveFields(); pivot.OptionsPager.RowsPerPage = 50; pivot.OptionsCustomization.AllowExpand = false; pivot.Fields["varnum"].Area = PivotArea.RowArea; pivot.Fields["variablelabel"].Area = PivotArea.RowArea; pivot.Fields["varnum"].AreaIndex = 0; pivot.Fields["variablelabel"].AreaIndex = 1; //pivot.Fields["ord_pos"].Visible = false; pivot.Fields["timepoint_text"].Area = PivotArea.ColumnArea; pivot.Fields["timepoint_text"].AreaIndex = 0; if (mymeasureid == 4911 | mymeasureid == 4912) { pivot.Fields["studymeasname2"].Area = PivotArea.ColumnArea; pivot.Fields["studymeasname2"].AreaIndex = 1; } pivot.Fields["value"].Area = PivotArea.DataArea; pivot.Fields["value"].SummaryType = DevExpress.Data.PivotGrid.PivotSummaryType.Min; pivot.Fields["timepoint_text"].TotalsVisibility = PivotTotalsVisibility.None; pivot.Fields["varnum"].TotalsVisibility = PivotTotalsVisibility.None; pivot.Fields["id"].TotalsVisibility = PivotTotalsVisibility.None; pivot.Fields["value"].TotalsVisibility = PivotTotalsVisibility.None; pivot.OptionsView.ShowColumnGrandTotals = false; pivot.OptionsView.ShowRowGrandTotals = false; pivot.OptionsView.ShowColumnHeaders = false; pivot.OptionsView.ShowRowHeaders = false; pivot.OptionsView.ShowDataHeaders = false; pivot.OptionsView.ShowFilterHeaders = false; ASPxLabel lbl = new ASPxLabel(); lbl.EncodeHtml = false; lbl.Text = String.Format("<br/><b>{0}</b>", dict.measname); lbl.Font.Size = 14; panel.Controls.Add(lbl); panel.Controls.Add(pivot); }
protected void ExportDictionaryForNDAR(int measureID) { Datadictionary dict = new Datadictionary(measureID); if (dict.dt_ndardict.HasRows()) { string dictfilename = String.Format("{0}_definitions", dict.measname); dict.dt_ndardict.TableName = dictfilename; SpreadsheetGearUtils.SaveDataTableToExcel(dict.dt_ndardict, dictfilename); } }
private void RenameREDCapColumns(Datadictionary dict) { int x = 0; foreach (DataColumn col in _dtRC.Columns) { string newcolname = dict.Fldname_in_REDCap_TO_fldname(col.ColumnName); if (newcolname != null) { Debug.WriteLine(String.Format("fldname_in_redcap={0} fldname={1} ", col.ColumnName.ToLower(), newcolname.ToString())); _dtRC.RenameColumn(col.ColumnName, newcolname); } } }
private void CheckForRowMatch(SQL_utils sql, DataTable dt_inDB, Datadictionary dict) { //Here Apr 27 2021 // Compare Rows that are already in the DB if (dt_inDB.HasRows()) { int nrows_dt_inDB = dt_inDB.Rows.Count; studymeasids = _dtRC.AsEnumerable().Select(f => f.Field <int>("studymeasid")).Distinct().ToList(); foreach (DataRow row in _dtRC.Rows) { string row_all_match = CheckREDCapData_by_row(sql, row, dt_inDB, dict); } } }
protected void CompareLinkedRedcap() { if (cboMeas.Value != null) { int measureID = Convert.ToInt32(cboMeas.Value.ToString()); Datadictionary dict = new Datadictionary(measureID); REDCap redcap = new REDCap(Master.Master_studyID); DataTable foo = redcap.dt_metadata; var x = from a in dict.dt_dict.AsEnumerable() join b in redcap.dt_metadata.AsEnumerable() on a.Field <string>("fldname_in_redcap").ToLower() equals b.Field <string>("field_name").ToLower() into ab from b in ab.DefaultIfEmpty() select new { ord_pos = a.Field <double>("ord_pos"), fldname = a.Field <string>("fldname").ToLower(), fldlabel = a.Field <string>("fieldlabel"), redcap_formname = (b == null) ? null : b.Field <string>("form_name"), redcap_fldname = (b == null) ? null : b.Field <string>("field_name"), redcap_fldlabel = (b == null) ? null : b.Field <string>("field_label") }; DataTable dtMerged = x.CustomCopyToDataTable(); if (dtMerged.HasRows()) { gridMerged.DataSource = dtMerged; gridMerged.SettingsPager.PageSize = 200; gridMerged.DataBind(); gridMerged.Caption = "Matched Fields between DB & REDCap: " + cboMeas.Text; panelMerged.Visible = true; } } }
protected void ExportDictionaryForStudy() { Datadictionary dict = new Datadictionary(Master.Master_studyID, true); if (dict.dt_dict.HasRows()) { string dictfilename = String.Format("{0}_DataDictionary", Master.Master_studyname); dict.dt_dict.TableName = "DataDictionary"; DataSet ds = new DataSet(); ds.Tables.Add(dict.dt_dict); if (dict.dt_ndardict.HasRows()) { dict.dt_ndardict.TableName = "NDA_DataDictionary"; ds.Tables.Add(dict.dt_ndardict); } SpreadsheetGearUtils.SaveDataSetToExcel(ds, dictfilename); } }
protected void LoadVars() { // List <int> mymeasureids = new List <int> { 341, 111, 3840, 3841, 4912, 4911 }; int counter = 0; lblSubjectInfo.Text = Request.QueryString["id"]; foreach (int mymeasureid in mymeasureids) { Datadictionary dict = new Datadictionary(mymeasureid); DataTable dtvars = dict.GetAnalysisVars_as_StackedDatatable(Master.Master_studyID, Request.QueryString["id"]); counter++; ASPxPanel mypanel = ((counter % 2) == 0) ? panel2 : panel1; mypanel = (mymeasureid == 4912 | mymeasureid == 4911) ? panel_sleepdiary : mypanel; if (dtvars.HasRows() & dtvars.ContainsColumnName("id")) { AddPivot(mypanel, dtvars, mymeasureid, dict); } else { ASPxLabel lbl = new ASPxLabel(); lbl.EncodeHtml = false; lbl.Text = String.Format("<br/><b>{0}</b> - No Records", dict.measname); lbl.Font.Size = 14; mypanel.Controls.Add(lbl); } } }
public DataRowCompare(DataRow mynewrow, DataRow mysavedrow, Datadictionary mydict, bool myisREDCap) { // // TODO: Add constructor logic here // dict = mydict; isREDCap = myisREDCap; num_sharedcolumns = 0; num_matches = 0; num_mismatches = 0; log_mismatches = new ProcessLog("Data row mismatches"); log_matches = new ProcessLog("Data row matches"); log = new ProcessLog("Comparing data rows"); newrow = mynewrow; savedrow = mysavedrow; CompareRows(); if (AllMatch()) { log_matches.Log(id); } }
public REDCapDataImport(REDCap myredcap, string formname, int mystudyid, bool mycommitToDB) { logs = new ProcessLogs(); resultslog = new ProcessLog("Import from REDCap"); ProcessLog matchingrowslog = new ProcessLog("Matching Rows"); matching_ids = new List <string>(); logs.AddLog(resultslog); logs.AddLog(matchingrowslog); ids_not_in_DB = new List <string>(); resultslog.Log(String.Format("BEGIN processing REDCap form: <b>{0}</b>", formname)); commitToDB = mycommitToDB; redcap = myredcap; studyid = mystudyid; _dtRC = redcap.DataFromForm(formname); _dtRC.AddColumn("isvalidid", typeof(int)); _dtRC.AddColumn("isready", typeof(string)); _dtRC = redcap.AddStudymeasToREDCapFormData(_dtRC); readyForInsert = false; SQL_utils sql = new SQL_utils("data"); //idvarname = "record_id"; idvarname = redcap.REDCap_id_fldname_for_DB(sql, formname, studyid); if (_dtRC.HasRows()) { resultslog.Log(String.Format("{0} records present in REDCap", _dtRC.Rows.Count)); CheckValidityOfAllIDs(sql); int smid = _dtRC.AsEnumerable().Select(f => f.Field <int>("studymeasid")).First(); tblname = sql.StringScalar_from_SQLstring(String.Format("select tblname from def.tbl where measureid=(select measureid from uwautism_research_backend..tblstudymeas where studymeasid={0})", smid)); Datadictionary dict = new Datadictionary(tblname); RenameREDCapColumns(dict); string sqlcode = String.Format("select * from {0} where studymeasid in (select studymeasid from uwautism_research_backend..tblstudymeas where studyid={1})", tblname, studyid); DataTable dt_inDB = sql.DataTable_from_SQLstring(sqlcode); dt_inDB.ColumnNamesToLower(); int nrows_dt_inDB = (dt_inDB.HasRows()) ? dt_inDB.Rows.Count : 0; //Here Apr 27 2021 CheckForRowMatch(sql, dt_inDB, dict); //// Compare Rows that are already in the DB //if (dt_inDB.HasRows()) //{ // nrows_dt_inDB = dt_inDB.Rows.Count; // studymeasids = _dtRC.AsEnumerable().Select(f => f.Field<int>("studymeasid")).Distinct().ToList(); // foreach (DataRow row in _dtRC.Rows) // { // string row_all_match = CheckREDCapData_by_row(sql, row, dt_inDB, dict); // } //} //Handle rows that are not in the DB already. Make sure they have enough valid values if (nrows_dt_inDB < _dtRC.Rows.Count) //There are more rows in REDCap than in the UWAC DB { //No rows //Mark row "ready" if isvalidid = 1, the row is not in DB, and the row contains valid data List <string> ids_in_db = dt_inDB.AsEnumerable().Select(f => f.Field <string>("id").ToUpper()).Distinct().ToList(); foreach (DataRow row in _dtRC.Rows) { if (row["isvalidid"].ToString() != "1") { row["isready"] = "NotReady: ID not in DB"; } else if (row["isvalidid"].ToString() == "1") //(row["isready"].ToString() == "ready") { if (!ids_in_db.Contains(row["record_id"].ToString().ToUpper())) { bool isready = CheckREDCapData_that_row_contains_data(sql, row, .20f); //Needs 20% valid fields if (isready) { row["isready"] = "ready"; } else if (!isready) { row["isready"] = "NotReady: too much missing data"; } } } } } matchingrowslog.Log(String.Join(",", matching_ids.Distinct())); List <string> ids_with_mult_recs = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "multiple records") .Select(f => f.Field <string>("id")).Distinct().ToList(); int n_multrecs = ids_with_mult_recs.Count(); if (n_multrecs > 0) { resultslog.Log(String.Format("<b>WARNING:</b> {0} records with MULTIPLE RECORDS [{1}]", n_multrecs , String.Join(", ", ids_with_mult_recs))); } int n_tocompare = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "need to compare values").Count(); if (n_tocompare > 0) { resultslog.Log(String.Format("INFO: {0} records already exist in DB, NEED TO COMPARE VALUES", n_tocompare)); } int n_ready = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "ready").Count(); List <string> ready_ids = _dtRC.AsEnumerable().Where(f => f.Field <string>("isready") == "ready").Select(f => f.Field <string>("id")).ToList(); if (n_ready > 0) { resultslog.Log(String.Format("PASS: Ready to insert {0} records into {1} <br/>=> {2}", n_ready, tblname, String.Join(",", ready_ids.Distinct()))); } else if (n_ready == 0) { resultslog.Log(String.Format("INFO: 0 records are Ready to insert into {1}", n_ready, tblname)); } if (commitToDB) { if (n_ready > 0) { InsertREDCapData(); } } else { resultslog.Log(String.Format("** Check 'Save to DB?' to save these {0} records. **", n_ready)); } } else { resultslog.Log(String.Format("WARNING: 0 records present in REDCap")); } sql.Close(); }
public string CheckREDCapData_by_row(SQL_utils sql, DataRow row, DataTable dtdb, Datadictionary dict) { string result = ""; // Loop through each row of REDCap data // select only records with this id string id = row[idvarname].ToString().ToUpper(); int smid = Convert.ToInt32(row["studymeasid"].ToString()); bool isvalidsubject = (row["isvalidid"].ToString() == "1"); if (isvalidsubject & smid > 0) { int nrecs_for_id = 0; try { nrecs_for_id = dtdb.AsEnumerable().Where(f => f.Field <string>("id").ToUpper() == id).Count(); } catch (Exception ex) { } if (nrecs_for_id > 0) { var qry = dtdb.AsEnumerable().Where(f => f.Field <string>("id").ToUpper() == id & f.Field <int>("studymeasid") == smid); //.CopyToDataTable(); if (qry.Any()) { DataTable dtid = (DataTable)qry.AsEnumerable().CopyToDataTable(); if (dtid.HasRows()) { if (dtid.Rows.Count == 1) { DataRowCompare rowcompare = new DataRowCompare(row, dtid.Rows[0], dict, true); if (rowcompare.log_mismatches.Numlogs() > 0) { logs.AddLog(rowcompare.log_mismatches); } else { //matches += String.Format("{0},",rowcompare.log_matches.LogNotesToHtml()); //logs.AddLog(rowcompare.log_matches); result = String.Format("AllMatch{0}", id); matching_ids.Add(id); } row["isready"] = rowcompare.Results(); //CompareRows(row, dtid.Rows[0]); } else { row["isready"] = "multiple records"; result = String.Format("There are {0} matching records for id:{1}", id, dtid.Rows.Count); } } } else { row["isready"] = "ready"; result = String.Format("There are NO matching records for id:{0}", id); } } else { row["isready"] = "ready"; //Not in DB } } //ProcessLog log_matches = new ProcessLog("Matching rows"); //log_matches.Log(matches); //logs.AddLog(log_matches); return(result); }