#4464 Updating Patient CPP via New UI Adds Duplicate Entries into Casemgmt_issue table

[Keith Chung]

OSCAR build date: 2016-09-26 01:21 PM
OSCAR build tag: oscar15BetaMaster-457
Client browser: firefox
Client OS: windows
Preconditions:

1. Please provide all information needed before this issue will present

None

Steps to reproduce:

1. Please give all steps to reproduce this issue (if there is data
   required to reproduce, please attach that to the issue)

a) create a new patient
b) open the patient's chart in the new UI
c) add a first of any of the following: other meds, ongoing concerns, medical history, social history, reminders, risk factors
d) add a second of the same

Expected Result:

In the casemgmt_issue table, only one of each issue would exist for this demographic.

I can continue to use all aspects of this patient's chart.

Observed:

In the casemgmt_issue table, two issues exist for each.

Cannot save notes via the classic UI anymore.

Please attach relevant log entries, screenshots

Error when trying to save note via classic UI.

javax.persistence.NonUniqueResultException: Expected 1 result got more : 2(9788,MedHistory)
org.oscarehr.casemgmt.dao.CaseManagementIssueDAO.getIssuebyIssueCode(CaseManagementIssueDAO.java:82)
org.oscarehr.casemgmt.web.CaseManagementEntryAction.saveCheckedIssues_newCme(CaseManagementEntryAction.java:1578)
org.oscarehr.casemgmt.web.CaseManagementEntryAction.noteSave(CaseManagementEntryAction.java:1331)
org.oscarehr.casemgmt.web.CaseManagementEntryAction.saveAndExit(CaseManagementEntryAction.java:1940)
sun.reflect.GeneratedMethodAccessor859.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:160)
oscar.oscarSecurity.LoginFilter.doFilter(LoginFilter.java:200)
net.sf.cookierevolver.servlet.CRFilterImpl.doFilter(CRFilterImpl.java:60)
org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
org.oscarehr.util.LoggedInUserFilter.doFilter(LoggedInUserFilter.java:56)
org.oscarehr.util.DbConnectionFilter.doFilter(DbConnectionFilter.java:66)
org.oscarehr.common.printing.PrivacyStatementAppendingFilter.doFilter(PrivacyStatementAppendingFilter.java:118)
org.oscarehr.util.ProblemCheckFilter.doFilter(ProblemCheckFilter.java:194)
org.oscarehr.util.ResponseDefaultsFilter.doFilter(ResponseDefaultsFilter.java:109)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:160)

This makes sense as CaseManagementIssueDAO.java is deliberately checking to ensure only one result is retrieved:

" public CaseManagementIssue getIssuebyIssueCode(String demo, String issueCode) {
@SuppressWarnings("unchecked")
List<casemanagementissue> list = this.getHibernateTemplate().find("select cmi from CaseManagementIssue cmi, Issue issue where cmi.issue_id=issue.id and issue.code = ? and cmi.demographic_no = ?",new Object[]{issueCode,demo});</casemanagementissue>

    if(list == null || list.size()<1) return(null);

    if (list.size() == 1 ) return list.get(0);

    throw(new NonUniqueResultException("Expected 1 result got more : "+list.size() + "(" + demo + "," + issueCode + ")"));          
}"

Other details:

[Keith Chung]

I think the bug is somewhere in NotesServices.java within saveIssueNote

The following code seems to be designed to prevent this issue and is presumably buggy in some respect.

//cIssue2 will be loaded with existing CaseManagementIssue if there is one for this patient
Issue cppIssue = caseManagementMgr.getIssueInfoByCode(issueCode);
CaseManagementIssue cIssue2;
String issueAlphaCode = cppIssue.getId().toString();
if (issueAlphaCode != null && issueAlphaCode.length() > 0){
//load by demo,issue code
cIssue2 = this.caseManagementMgr.getIssueByIssueCode(demo, issueAlphaCode);
}else{
cIssue2 = this.caseManagementMgr.getIssueById(demo,issueCode);
}

    //no issue existing for this type of CPP note..create and save it
    if( cIssue2 == null ) {
        Date creationDate = new Date();

        cIssue2 = new CaseManagementIssue();
        cIssue2.setAcute(false);
        cIssue2.setCertain(false);
        cIssue2.setDemographic_no(demo);
        cIssue2.setIssue_id(cppIssue.getId());
        cIssue2.setMajor(false);
        cIssue2.setProgram_id(Integer.parseInt(programId));
        cIssue2.setResolved(false);
        cIssue2.setType(cppIssue.getRole()); 
        cIssue2.setUpdate_date(creationDate);

        caseManagementMgr.saveCaseIssue(cIssue2);
    }

[Keith Chung]

To correct the issue, I need to manually correct the database using the following queries:

Query A: select count(*) as cnt,casemgmt_issue.demographic_no,issue_id,demographic.last_name,demographic.first_name,demographic.provider_no from casemgmt_issue join demographic on casemgmt_issue.demographic_no = demographic.demographic_no group by casemgmt_issue.demographic_no,issue_id;

Query B: SELECT * FROM casemgmt_issue WHERE demographic_no = ___ and issue_id = ___ ORDER BY id;

Query C: select * from casemgmt_issue_notes join casemgmt_note on casemgmt_issue_notes.note_id = casemgmt_note.note_id where casemgmt_issue_notes.id in (___);

In particular, use Query A to identify pairs of demographic_no and issue_id where the count is incorrectly > 1

For each pair:

Use Query B to identify the issue ids --> you'll need to delete all but one;  identify the ones you need to delete and use those ids to populate query c

Use Query C to identify the notes corresponding to the issues you plan to delete --> delete these, then go back to query B to manually delete the corresponding issues.

The data will still technically be visible in the chart, but before deleting the rows from casemgmt_issue_notes, it's probably a good idea to make a note of what you're deleting since this information will be removed from the cpp.

[Keith Chung]

potentially related to the following bugs:

https://sourceforge.net/p/oscarmcmaster/bugs/3492/
https://sourceforge.net/p/oscarmcmaster/bugs/4078/