Add feature is disabled in 7.01 and beyond? or is it a bug?

Robert_James · May 19, 2023, 8:17am

Warren · May 26, 2023, 11:32pm

I had difficulty earlier adding insurance types. I had them do it another way. I will check on the above this weekend.

Robert_James · May 31, 2023, 3:51pm

even after todays update patch this bug is still there.

brady.miller · May 31, 2023, 5:00pm

Confirmed bug with following php error:

SQL Error with statement:query failed: SELECT * FROM list_options WHERE  list_id= ? AND option_id = ?AND activity = 1--You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;?AND activity = 1&#039; at line 1==&gt;/var/www/localhost/htdocs/openemr/library/ajax/addlistitem.php at 54:sqlQuery, referer: http://localhost:8300/interface/patient_file/summary/demographics_full.php

brady.miller · June 1, 2023, 6:10am

Just committed a fix for this in the codebase. It will be in the next patch (patch 2 that will likely go out in about 2 weeks).

(if need the fix now then check out the PR since is a simple one liner fix)

Robert_James · June 1, 2023, 10:37am

PR means?? can u send a code link or instructions pl?

Robert_James · June 1, 2023, 11:03am

ok got it line 52 around

" AND option_id = ?" . "AND activity = 1", array($list_id, $option_id));

replace with
" AND option_id = ? AND activity = 1", array($list_id, $option_id));

Warren · June 1, 2023, 7:41pm

Could you please forward the resolution to me as well? Thanks.

Robert_James · June 2, 2023, 1:23am

Warren go to library/ajax/addlistitem.php

line 54 find

" AND option_id = ?" . “AND activity = 1”, array($list_id, $option_id));

and replace with

" AND option_id = ? AND activity = 1", array($list_id, $option_id));

save the file and it works great.

Warren · June 2, 2023, 3:20am

I applied the syntax update however, I still get the same results.

Thank you though for the info. I appreciate it.

I attached a png to show it.

Robert_James · June 2, 2023, 3:24am

you can copy the complete code and paste it in your addlistiten.php file.
This one is working fine for me.

<?php

/**
 * This file is used to add an item to the list_options table
 *
 * OUTPUT
 *   on error = NULL
 *   on success = JSON data, array of "value":"title" for new list of options
 *
 * @package   OpenEMR
 * @link      https://www.open-emr.org
 * @author    Jason Morrill <jason@italktech.net>
 * @author    Brady Miller <brady.g.miller@gmail.com>
 * @author    Daniel Ehrlich <daniel.ehrlich1@gmail.com>
 * @copyright Copyright (c) 2009 Jason Morrill <jason@italktech.net>
 * @copyright Copyright (c) 2018 Brady Miller <brady.g.miller@gmail.com>
 * @copyright Copyright (c) 2018 Daniel Ehrlich <daniel.ehrlich1@gmail.com>
 * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
 */

require_once("../../interface/globals.php");

use OpenEMR\Common\Csrf\CsrfUtils;

//verify csrf
if (!CsrfUtils::verifyCsrfToken($_GET["csrf_token_form"])) {
    echo json_encode(array("error" => xl('Authentication Error') ));
    CsrfUtils::csrfNotVerified(false);
}

// check for required values
if ($_GET['listid'] == "" || trim($_GET['newitem']) == "" || trim($_GET['newitem_abbr']) == "") {
    exit;
}

// set the values for the new list item
$is_default = 0;
$list_id = $_GET['listid'];
$title = trim($_GET['newitem']);
$option_id = trim($_GET['newitem_abbr']);
$option_value = 0;

// make sure we're not adding a duplicate title or id
$exists_title = sqlQuery("SELECT * FROM list_options WHERE " .
    " list_id= ? " .
    " and title = ? AND activity = 1", array($list_id, $title));
if ($exists_title) {
    echo json_encode(array("error" => xl('Record already exist') ));
    exit;
}

$exists_id = sqlQuery("SELECT * FROM list_options WHERE " .
    " list_id= ?" .
    
    " AND option_id = ? AND activity = 1", array($list_id, $option_id));
if ($exists_id) {
    echo json_encode(array("error" => xl('Record already exist') ));
    exit;
}

// determine the sequential order of the new item,
// it should be the maximum number for the specified list plus one
$seq = 0;
$row = sqlQuery("SELECT max(seq) as maxseq FROM list_options WHERE list_id = ? AND activity = 1", array($list_id));
$seq = $row['maxseq'] + 1;

// add the new list item
$rc = sqlInsert("INSERT INTO list_options ( " .
    "list_id, option_id, title, seq, is_default, option_value ) VALUES ( ?, ?, ?, ?, ?, ? )", array($list_id, $option_id, $title, $seq, $is_default, $option_value));

// return JSON data of list items on success
echo '{ "error":"", "options": [';
// send the 'Unassigned' empty variable
echo '{"id":"","title":' . xlj('Unassigned') . '}';
$comma = ",";
$lres = sqlStatement("SELECT * FROM list_options WHERE list_id = ? AND activity = 1 ORDER BY seq", array($list_id));
while ($lrow = sqlFetchArray($lres)) {
    echo $comma;
    echo '{"id":' . js_escape($lrow['option_id']) . ',';

    // translate title if translate-lists flag set and not english
    if ($GLOBALS['translate_lists'] && $_SESSION['language_choice'] > 1) {
        echo '"title":' . xlj($lrow['title']) . '}';
    } else {
        echo '"title":' . js_escape($lrow['title']) . '}';
    }
}

echo "]}";
exit;

Warren · June 2, 2023, 5:15am

Thank you for the assistance I appreciate it. I applied the code and unfortunately I still get the error 500. I am unable to figure this one out?

Here are some logs for the demographics issues

Date	Event	Category	User	Certificate User	Group	Patient ID	Success	API logging	Comments
06/02/2023 01:05	patient-record-Query	Patient Demographics	drmainfo_openemr		Default	20	1		Query pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,

IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND
pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? (‘drmainfo_openemr’,)|
|06/02/2023 01:04|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND
pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? (‘drmainfo_openemr’,)|
|06/02/2023 01:03|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND
pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? (‘drmainfo_openemr’,)|
|06/02/2023 01:01|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND
pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? (‘drmainfo_openemr’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND
pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ? (‘drmainfo_openemr’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query DATEDIFF(?,deceased_date) AS days_deceased, deceased_date AS date_deceased FROM patient_data WHERE pid = ? AND (deceased_date IS NOT NULL AND deceased_date!= ‘0000-00-00 00:00:00’) AND deceased_date <= ? (‘2023-06-02 01:00:31’,‘20’,‘2023-06-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query DATEDIFF(?,deceased_date) AS days_deceased, deceased_date AS date_deceased FROM patient_data WHERE pid = ? AND (deceased_date IS NOT NULL AND deceased_date!= ‘0000-00-00 00:00:00’) AND deceased_date <= ? (‘2023-06-02 01:00:31’,‘20’,‘2023-06-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query DATEDIFF(?,deceased_date) AS days_deceased, deceased_date AS date_deceased FROM patient_data WHERE pid = ? AND (deceased_date IS NOT NULL AND deceased_date!= ‘0000-00-00 00:00:00’) AND deceased_date <= ? (‘2023-06-02 01:00:31’,‘20’,‘2023-06-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query DATEDIFF(?,deceased_date) AS days_deceased, deceased_date AS date_deceased FROM patient_data WHERE pid = ? AND (deceased_date IS NOT NULL AND deceased_date!= ‘0000-00-00 00:00:00’) AND deceased_date <= ? (‘2023-06-02 01:00:31’,‘20’,‘2023-06-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query DATEDIFF(?,deceased_date) AS days_deceased, deceased_date AS date_deceased FROM patient_data WHERE pid = ? AND (deceased_date IS NOT NULL AND deceased_date!= ‘0000-00-00 00:00:00’) AND deceased_date <= ? (‘2023-06-02 01:00:31’,‘20’,‘2023-06-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Social and Family History|drmainfo_openemr||Default|20|1||Query tobacco from history_data where tobacco is not null and pid = ? and date <= ? order by date DESC, id DESC limit 0,1 (‘20’,‘2023-05-02 01:00:31’,)|
|06/02/2023 01:00|patient-record-Query|Patient Demographics|drmainfo_openemr||Default|20|1||Query sex, DATE_FORMAT(DOB,’%Y %m %d’) as DOB_TS from patient_data where pid=? order by date DESC limit 0,1 (‘20’,)|
|06/02/2023 01:00|patient-record-Query|Social and Family History|drmainfo_openemr||Default|20|1||Query tobacco from history_data where tobacco is not null and pid = ? and date <= ? order by date DESC, id DESC limit 0,1 (‘20’,‘2023-06-02 01:00:31’,)|

Robert_James · June 2, 2023, 5:20am

try using this file, before pasting this file into that folder, pl rename ur old file.
addlistitem.php (3.0 KB)