Home› Users Mailing List
We are currently working on the forum. For the short-term, all forum content will be in read-only format. We apologize for the interruption and look forward to collaborating with you shortly. All the best in your research!

Am I missing something here?

cra360cra360 Posts: 56 ✭
in Users Mailing List
Hi,

I'm trying to verify some data here with the sample database that OC kindly provided. particularly I'm looking at Discrepancy Notes.  And if my understanding is correct, the three table of discrepancy_note, discrepancy_note_type and resolution_status would provide us with all the key info to us.

Here I'm looking into three things or from three angles.  The data and sql queries that I construct to get on-demand data view, the existing data view such as Views that came with OC, and the OC web portal.  And I'd like to see if they are all or largely consistent.

I'm a bit puzzled by the following finding.

-- run two sql queries
-- a) with the existing View about discrepancy notes
SELECT * 
FROM view_discrepancy_note
order by discrepancy_note_type_id;
-- results
/*
3 of discrepancy_note_type_id   1       Failed Validation Check
1 of                            2       Incomplete
2 of                            3       Unclear/Unreadable
1 of                            4       Annotation
                                5       Other
                                6       Query
                                7       Reason for Change
*/

-- b)
-- construct a straight-forward sql query to get discrepany notes, its type and status                          
select distinct dnt.name as note_type, dn.*
from discrepancy_note dn, discrepancy_note_type dnt
where dn.discrepancy_note_type_id = dnt.discrepancy_note_type_id
and dn.parent_dn_id is null
order by dn.discrepancy_note_type_id

Both returns identical results.  However, the following baffles me.
According the sql queries,
Q1:
discrepancy_note_id of 5 has data of 2 for discrepancy_note_type_id.  According to the discrepany_note_type look up table, discrepancy_note_type_id of 2 is for "Incomplete" notes, but it seems the OC portal has it for "Annotation".
Q2:
discrepancy_note_id of 9 has data of 3 for discrepancy_note_type_id.  According to the discrepany_note_type look up table, discrepancy_note_type_id of 3 is for "Unclear/Unreadable" notes, but it seems the OC portal has it for "Query".
Q3:
discrepancy_note_id of 11 has data of 4 for discrepancy_note_type_id.  According to the discrepany_note_type look up table, discrepancy_note_type_id of 4 is for "Annotation" notes, but it seems the OC portal has it for "Annotation".

So, what are correct?

Many thanks.

Don
P.S. 
A note on the OC new forum, if you're using Chrome browser, the way to move from Discussion Title to [ Body ] area is to use the Tab key, attempt to use cursor directly won't work while it works fine with IE11
Tagged:
«1

Answers

  • cra360cra360 Posts: 56 ✭
    Gerben,

    Thanks for your thoughtful note.  Ok, it caught me by surprise that we're not using the discrepancy_note_type table though the disprepancy_note_type_id is clearly a foreign key from this table.

    I couldn't find the DiscrepancyNoteType.java or DiscrepancyNoteType.class file in my OC installation, however, I found  DiscrepancyNoteRow.class file, and with Jad, it seems that the getDiscrepancyNoteTypeId() method would shed light on its actual reference of note type, question, which class file has this method?

    TIA.

    Don
  • haenselhaensel Posts: 602 ✭✭✭
    via Email
    Hi

    Is "DiscrepancyNoteBean" what your looking for ?
    If you're looking for the class file on your web server, it's within the
    OpenClinica-core*.jar (that's in your webapp lib folder, e.g.
    webapps/OpenClinica/WEB-INF/classes/OpenClinica-core-*.jar). But isn't
    it easier to have a look at the source code?

    Regards,
    Christian
  • ccollinsccollins Posts: 383 admin
    Hi Don,

    You're not the first to have been misled by the lookup table discrepancy_note_type in the schema. It is not used and there is a ticket to clean up the schema (http://jira.openclinica.com/browse/OC-2083) but that has not been done yet. It will probably be refactored next time there are significant changes to that module of the system.

    Cal
  • cra360cra360 Posts: 56 ✭
    Hi Cal,

    So, how do we "translate" the discrepancy_note_type_id value to discrepancy_note_type?

    Btw, I couldn't log into the jira, not sure if it requires another type of registration.

    Thanks.

    Don

  • ccollinsccollins Posts: 383 admin
    Hi Don,

    Yes - at the moment you do have to register separately to get into Jira. It's self registration; let me know if you have any issues.

    Per your question, I think you'd have to look in the source code, as Christian suggested. What is it you are trying to accomplish? The type_id is an internal implementation of the system, not something that is exposed in the UI or data interfaces anywhere (as far as I'm aware). So you should only need this if you're adding or changing functionality. If you are, it would be good to know what your goal is as we may be able to be more helpful. 

    Cal
  • cra360cra360 Posts: 56 ✭
    Cal,

    Ok, I'm now in for the jira but I'm new to it... where do i look for OC source code?

    Regarding What is it you are trying to accomplish?
    As mentioned, I'd like to be able to "translate" the discrepancy_note_type_id value in the discrepancy_note table to discrepancy_note_type because we'd like to be able to review the data from the db directly otherwise the OC portal.

    Thanks in advance.

    Don



  • ccollinsccollins Posts: 383 admin
    Hi Don, 
    We don't recommend interfacing with the db directly. Use one of the defined interfaces - ODM extracts with Discrepancy Notes included, or the CSV download format from the discrepancy manager. The schema may change arbitrarily in any future release and break your queries/scripts. 

    That being said, it's open source and you can do what you like. You won't be the first one to have done it ;-) 

    Info on getting or browsing the source code:

    Best,
    Cal

  • cra360cra360 Posts: 56 ✭
    Hi Cal,

    That's helpful.  Now, I wonder who wrote the DiscrepancyNoteBean.java code or are familiar with it, the getDisType() method seems critical in translating a discrepany_note_type_id, not sure where it's defined.

    Thanks.

    Don
  • jamuna269jamuna269 Posts: 109 ✭
    Don,
    The discrepancy note type is a bunch of static variables its been defined here
    DiscrepancyNoteType.java under this directory org.akaza.openclinica.bean.core

    Please note that we have hibernate files in 3.1-SNAPSHOT which look similar and will have similar name but it will be under dataMap folder. These files are not tied to the entire system yet.


This discussion has been closed.