private void CompareRows()
{
List <string> new_colnames = newrow.Table.ColumnNames("lower");
List <string> saved_colnames = savedrow.Table.ColumnNames("lower");
id = "";
for (int c = 0; c < newrow.ItemArray.Length; c++)
{
string newcolname = new_colnames[c];
if (newcolname == "id")
{
id = newrow.ItemArray[c].ToString();
}
if (saved_colnames.Contains(newcolname))
{
int savedcolidx = saved_colnames.FindIndex(a => a == newcolname);
if (savedcolidx >= 0)
{
num_sharedcolumns++;
string mm = (isREDCap) ? CompareStrings(dict.Fldname_TO_fldname_in_REDCap(newcolname), newcolname
, newrow.ItemArray[c].ToString(), savedrow.ItemArray[savedcolidx].ToString()) :
CompareStrings(newcolname, newcolname
, newrow.ItemArray[c].ToString(), savedrow.ItemArray[savedcolidx].ToString());
if (mm != null)
{
log_mismatches.Log(mm);
}
//if (newrow.ItemArray[c].ToString() == savedrow.ItemArray[savedcolidx].ToString())
//{
// num_matches++;
//}
//else // Mismatch
//{
// num_mismatches++;
// string space = " ";
// string spaces = "";
// for (int i=0; i < 55 - colname.Length; i++)
// {
// spaces += space;
// }
// string mm = String.Format("id:{3} [{0}]{5}[{2}] != [{3}]",
// colname, colname2 , newrow.ItemArray[c].ToString(), savedrow.ItemArray[savedcolidx].ToString(), id, spaces);
// log_mismatches.Log(mm);
//}
}
}
}
}
private void Initialize(int docversid, string filepath)
{
bool noColHeaders = true;
_docvers = new Docvers(docversid, filepath, noColHeaders);
_log = new ProcessLog(String.Format("Actigraphy Import (docversid={0})", _docvers.docversid));
_logs.AddLog(_docvers.processing_results);
_logs.AddLog(_log);
if (_docvers.HasTables())
{
_docvers.dset.Tables[0].AddRowNumColumn();
_log.Log(String.Format("origfilename = {0}", _docvers._origfilename));
}
}
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 void SaveFormDataToDB(string form, int studyid)
{
SQL_utils sql = new SQL_utils("data");
_import_results.Log("Processing <b>" + form + "</b>");
DataTable dt_redcap = DataFromForm(form);
dt_redcap = AddStudymeasToREDCapFormData(dt_redcap);
string idfld = REDCap_id_fldname_for_DB(sql, form, studyid);
_import_results.Log(String.Format("ID contained in REDCap field '{0}'", idfld));
dt_redcap = dataops.VerifyID(dt_redcap, idfld, studyid);
int num_notid = dt_redcap.AsEnumerable().Where(f => f.Field <string>("not_id") != null).Count();
if (num_notid > 0)
{
List <string> not_ids = dt_redcap.AsEnumerable().Where(f => f.Field <string>("not_id") != null).Select(f => f.Field <string>("not_id")).ToList();
_import_results.Log(String.Format("ERROR. Data contains {0} records with ID's not in the DB: {1}", num_notid, String.Join(",", not_ids)));
}
else
{
_import_results.Log("All ID's in REDCap data are present in the DB.");
dt_redcap = dataops.CopyColumntoID(dt_redcap, idfld);
dt_redcap.AddConstantInt("verified", 0);
string timestamp_column = "";
foreach (DataColumn col in dt_redcap.Columns)
{
if (col.ColumnName.EndsWith("_timestamp"))
{
timestamp_column = col.ColumnName;
dt_redcap.RenameColumn(timestamp_column, "redcap_timestamp");
}
}
int studymeasid1 = dt_redcap.AsEnumerable().Select(f => f.Field <int>("studymeasid")).Min();
int studymeasid2 = dt_redcap.AsEnumerable().Select(f => f.Field <int>("studymeasid")).Max();
int measureid1 = sql.IntScalar_from_SQLstring("select measureid from uwautism_research_backend..tblstudymeas where studymeasid=" + studymeasid1.ToString());
int measureid2 = sql.IntScalar_from_SQLstring("select measureid from uwautism_research_backend..tblstudymeas where studymeasid=" + studymeasid2.ToString());
if (measureid1 == measureid2)
{
string tblname = sql.StringScalar_from_SQLstring(String.Format("select tblname from def.tbl where measureid={0}", measureid1));
string result;
try
{
DataTable dt_redcap2 = dt_redcap.ConvertEmptyStringToDBNull();
result = sql.BulkInsert(dt_redcap2, tblname);
}
catch (Exception ex)
{
result = "ERROR: " + ex.Message;
}
_import_results.Log(result);
}
}
//return result;
//return "ok";
}
