コード例 #1
0
    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 = "&nbsp;";
                    //    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);
                    //}
                }
            }
        }
    }
コード例 #2
0
    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));
        }
    }
コード例 #3
0
    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);
        }
    }
コード例 #4
0
        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();
        }
コード例 #5
0
        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";
        }