Maybe you don’t understand your list well, but here are some ideas so you can create it. I hope it works for you.
For general appointment list one a date:
SELECT e.pc_eventDate, e.pc_endDate, e.pc_startTime, e.pc_endTime, e.pc_recurrfreq, e.pc_catid, e.pc_eid, e.pc_gid, e.pc_title, e.pc_hometext,
e.pc_apptstatus, CONCAT(p.fname, ' ', p.mname, ' ', p.lname) Patient, p.pid, p.pubpid,
p.hipaa_allowsms, p.phone_home, p.phone_cell, p.hipaa_voice, p.hipaa_allowemail, p.email,
u.fname AS ufname, u.mname AS umname, u.lname AS ulname, u.id AS uprovider_id,
f.name, e.pc_room, e.pc_pid, t.id, t.eid, t.pid,
CONCAT(u.fname, ' ', u.mname, ' ', u.lname) Provider, t.encounter, q.pt_tracker_id, q.room
FROM openemr_postcalendar_events AS e
LEFT OUTER JOIN patient_tracker AS t ON t.pid = e.pc_pid AND t.apptdate = e.pc_eventDate AND t.appttime = e.pc_starttime
AND t.eid = e.pc_eid
LEFT OUTER JOIN patient_tracker_element AS q ON q.pt_tracker_id = t.id AND q.seq = t.lastseq
LEFT OUTER JOIN list_options AS s ON s.list_id = 'apptstat' AND s.option_id = q.status AND s.activity = 1
LEFT OUTER JOIN facility AS f ON e.pc_facility = f.id
LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid
LEFT OUTER JOIN users AS u ON u.id = e.pc_aid
LEFT OUTER JOIN openemr_postcalendar_categories AS c ON c.pc_catid = e.pc_catid
WHERE e.pc_pid=p.pid AND e.pc_eventDate='2022-10-10'
ORDER BY e.pc_eventDate, e.pc_startTimeFor Insurances Companies for patients (PID patient = 1):
SELECT
CONCAT(id.subscriber_fname, ' ', id.subscriber_mname, ' ', id.subscriber_lname) Patient,
ic.name, id.type,
itc.type,
itc.claim_type
FROM insurance_data id
LEFT JOIN insurance_companies ic ON ic.id = id.provider
LEFT JOIN insurance_type_codes itc ON ic.ins_type_code = itc.id
WHERE id.pid = 1 AND id.provider <> ''
ORDER BY id.typeA General appointment with location:
SELECT
p.fname,
p.lname,
pc.pc_pid,
p.DOB,
pc.pc_eventDate,
pc.pc_startTime,
pc.pc_title,
pc.pc_hometext,
(SELECT ic.name FROM insurance_companies AS ic LEFT JOIN insurance_data AS id ON id.type = "primary" WHERE p.pid = id.pid GROUP BY id.type) SS,
CONCAT
(u.fname, ' ', u.mname, ' ', u.lname) Provider,
(SELECT lo.title FROM list_options lo WHERE lo.option_id = pc.pc_room AND lo.list_id='patient_flow_board_rooms') Location
FROM
patient_data p
LEFT JOIN
openemr_postcalendar_events pc ON p.pid = pc.pc_pid
LEFT JOIN
users u ON u.id = pc.pc_aid
WHERE
pc.pc_eventDate = "2022-10-10"
ORDER BY
pc.pc_startTime ASC;For places, you can create a new one (for example Telehealth) in Administration/Forms/Lists → rooms and there add Telesalus, then when creating an appointment choose Telehealth.
If you want to place an Active status. You can modify demographic forms Administration/Forms/designs → choose demographics and enable a field, for example generic name (genericname2) must be type option list (List tbox) choose list YES/NO,
In the same way you can add another field with the Asset modification date.