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