# 75 — MED Addendum: Clinic Ops, Customer Service, SMS, Declarations, Archive & Vote

**Source deployment:** `/home/medgreennatureco/public_html/med` (live: med.greennature.com.sa) — newer/extended "center/hospital" edition of the OB/GYN codebase. Cross-checked against the original 312-table dump (`/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`). Read-only, schema inferred from code only (RedBeanPHP + raw SQL + Smarty).

## Files analyzed

| File | Role |
|---|---|
| `core/controllers/reserve_clinic.php` | Weekly room/doctor reservation grid (h/v versions, branches) |
| `core/controllers/clinic_visits.php` | In-clinic reception board: enter/out per room |
| `core/controllers/visits00.php` | Extended visit booking + department/doctor/reserve routing |
| `core/controllers/visits_common.php` | Shared helpers: SMS send, short URLs, archive/device tracking, enc_id |
| `core/controllers/customer_service.php` | Doctor performance, waiting-time, complaints, price list |
| `core/controllers/sms_control.php` | SMS template/campaign CRUD |
| `core/controllers/sms_sample.php` | Standalone SMS test (hardcoded smsmisr creds) |
| `core/controllers/decleration.php` | Consent/declaration templates |
| `core/controllers/patientdecleration.php` | Per-patient signed consents w/ file upload |
| `core/controllers/fast_options.php` | Quick-link shortcuts |
| `core/controllers/archive.php` | Device-based file/patient archive tracking |
| `core/controllers/case_summary.php` | Read-only clinical case aggregator |
| `vote/controllers/index.php` | Kiosk satisfaction-survey app (Arabic kioskboard) |
| `core/controllers/vote.php`, `vote_devices.php` | Admin/reporting for the vote app |

## 1. Clinic operations

- **`reserve_clinic.php`** builds a 7-day × hourly grid from `programesetting.start_hour`/`end_hour`. Rooms in `clinic_rooms`; recurring weekly reservations in `clinic_reserves` (`room_id`, `doctor_id`, `reserve_day_no` 1=Sat..7=Fri, `reserve_hour`, `start_date`, `cancel_date`). Supports horizontal/vertical layouts and multi-branch via `programesetting.branches`/`rooms`. Day-number mapping: Sun=2, Mon=3 … Sat=1 (`getDayN0`/`getTodayNumber`). Cancel = `cancel_date` + `is_active=0` + `deleted=1`. Generic `update()`/`del()` accept `tableName`/`colName` from POST (mass-assignment surface — note for security review).
- **`clinic_visits.php`** (`index`): for each active `clinic_rooms` row, lists today's reserved doctors (`clinic_reserves` join `awusers`) and their `visits` (today, `for_doctor`, `deleted=0`). `enter()` sets `visits.clinic_entered=1`, `out()` sets `visits.clinic_out=1`.
- **`visits00.php`**: routes a visit to `for_department`/`for_doctor`, may bind a clinic reservation (`reserveId`, dept 13), and pushes to department queues (`sendToDept`/`removeFromDept` for depts 5 & 12). Confirms `visits` carries new columns: `clinic_entered, clinic_out, arrived_time, arrived_userid, kashf_enterance, enc_id, visit_order, visit_time, rfc, branch_id, customer_add, view`.

## 2. Customer service (`customer_service.php`)

- **Prices**: merges `detections`, `invests`/`investcats`, `rays`/`rayscats`.
- **Doctor performance** (`getvisits`/`visitbydoctor`/`printvisits`): counts visits, confirmed (`customer_add != 1`), attended (`view = 1`) per doctor/dept/branch; computes waiting time = `kashf_enterance − arrived_time`. Medical depts gated by `awrole.doctor_visit_show = 1`; excludes `detectionid in (999, 9999, -88)`. Doctor scoping via `awusers.related_dr` for role 19.
- **Arrival**: `arrivedHos()` stamps `visits.arrived_time`/`arrived_userid`.
- **Complaints**: full CRUD on NEW table `patients_complaints` (`patientid, complaiv, reply, call_date_time, user_id, deleted`); button perms via `awrolebtn` (btn 5 edit / 6 delete).

## 3. SMS campaigns (`sms_control.php` + `visits_common.php`)

- Templates in NEW `sms_control`: `name, message, message_type (1..5), message_place, sender_name, fixed_link, en_link, for_depts (-1=all), status, deleted`. Global settings in NEW `sms_control_setting` (`server_type`).
- `sendSms($sourceId, $messagePlace)` (called from `visits.php`, `patients.php`, `operations_reports.php`): resolves phone (`patients.phone/mobile/hus_mobile`), active templates for the place, dept allow-list (`allowedDept`), then builds message by type — plain / +link / +patient name / +visit short link. Generates unique `visits.enc_id`, persists `short_urls` (`origin_url, short_url, create_date`).
- Two providers: legacy `smsmisr.com` (`sendItSms`) and current `sms.naslab.gt4it.com` (`sendItSmsApi`). When `server_type=1`, pushes visit details to external portal `royal-fc.net/appv/visit_details/{add,update}` (`prepareToSite`/`sendToSite`/`updateToSite`).
- **Security:** `sms_sample.php` hardcodes smsmisr username/password/sender; `visits_common.php` hardcodes a naslab JWT Bearer token. Both must be externalized and rotated.

## 4. Declarations / consents

- `decleration.php` — NEW `decleration` template table (`title, content, doctor_id, doctor_name, husband, wife, reception, date`).
- `patientdecleration.php` — NEW `patientdecleration` linking a signed consent to a patient (`decleration_id, patientid, uploadfile` → `upload/decleration_files`, `user_id, date`); print views pull patient + doctor + `programesetting`. Note: `$patientId` vs `$patientid` case bug in `addit()` (loads wrong patient var); `edit_file` used unquoted constant in `updateit()`.

## 5. Case summary (`case_summary.php`)

Read-only aggregator over many clinical tables. Mostly known `ph*` history tables, but references NEW extended-sheet tables: `obstetric_case`, `obstetric_case_ex`, `summary_obstetric`, `summary_obstetric_abortion`, `previous_pregnancies`, `previous_puerperium`, `pre_anaesthetic`, `blood_transfusion`, `complaint_analysis`, `recommend`, `symptoms` (these belong to the extended clinical-sheets domain, listed here only as encountered).

## 6. Archive tracking (`archive.php` + `visits_common.php`)

- Device scan flow: `devices.location` 4=in / 5=out. NEW `archive_tracking` (`patient_id, in_date/in_time/in_device_id, out_date/out_time/out_device_id, target_id, target_table, receiver, notes, control, user_id, deleted`). An open "out" record (out set, in empty) is completed on the next "in" scan. Parallel generic `device_tracking` (KNOWN). Floor location resolved via `devices.floor_no` → `floors`. Search by patient or by date; "exited but not returned" list.

## 7. Vote / satisfaction kiosk (`/med/vote` + `vote.php`/`vote_devices.php`)

- Standalone Smarty app with Arabic kioskboard (`kioskboard-2.2.0`, `kioskboard-keys-arabic.json`, jQKeyboard).
- Schema (all NEW): `vote_devices` (`name, local_ip`), `votes` (`vote_device_id, voter_required, email, role_id`), `vote_questions` (`vote_id, question`), `vote_answers` (`vote_question_id, answer, face, send_mail`), `clients_votes` (`vote_device_id, vote_id, user_id, voter_name, voter_phone, ip/local_ip/remote_ip, user_agent, is_contact, reply, created_at`), `clients_votes_answers` (`client_vote_id, question_id, answer_id, answer_face, answer_text, user_id`).
- Flow (`vote/controllers/index.php`): `?ac=vote&device=ID` → verify `vote_devices` exists → resolve logged-in employee from NEW `login_tacking` (latest `user_id` where `login_ip = device.local_ip` and `status=1`) → load `votes`+`vote_questions`+`vote_answers` → `submit()` writes `clients_votes` + `clients_votes_answers` → redirect to `thank_you`.
- Reporting (`vote.php`): per-employee satisfaction reports (`report`/`reportDetails`/`empReport`) with date range, distinct question/answer counts, answer-text distribution; complaint follow-up via `updateClientVoteContact` (`is_contact`) and `updateVoteReply` (`reply`). Email-on-answer via `vote_answers.send_mail` + `votes.email` tied to `votes.role_id`.

## NEW tables (not in the 312-table dump)

`clinic_rooms`, `clinic_reserves`, `patients_complaints`, `sms_control`, `sms_control_setting`, `short_urls`, `visits_site` (likely external API only), `decleration`, `patientdecleration`, `fastoptions`, `archive_tracking`, `vote_devices`, `votes`, `vote_questions`, `vote_answers`, `clients_votes`, `clients_votes_answers`, `login_tacking`, plus extended-sheet tables referenced by `case_summary` (`obstetric_case`, `obstetric_case_ex`, `summary_obstetric`, `summary_obstetric_abortion`, `previous_pregnancies`, `previous_puerperium`, `pre_anaesthetic`, `blood_transfusion`, `complaint_analysis`, `recommend`, `symptoms`).

KNOWN tables reused: `visits` (extended w/ new columns), `patients`, `awusers`, `awrole`, `awrolebtn`, `detections`, `invests`/`investcats`, `rays`/`rayscats`, `programesetting`, `branches`, `devices`, `device_tracking`, `floors`, `wifetypes`, `refer`.

## ERP/HIS impact

1. New **Clinic Scheduling / Roster** domain (`clinic_rooms`/`clinic_reserves`) absent from the 312-table blueprint — model with branch support.
2. New **Patient Experience / Customer Service** domain: waiting-time KPIs, confirmed/attended visit metrics, complaints (`patients_complaints`).
3. Unify **Notifications**: two SMS providers + hardcoded secrets → single Notification service, externalized/rotated secrets.
4. **Short-URL engine + external portal push** (`royal-fc.net`) = integration interface to document.
5. **Digital consents** (`decleration`/`patientdecleration` + uploads) → Consent/Document Management.
6. **Physical MRD tracking** (`archive_tracking` + `devices.location` + `floors`) → Medical Records Tracking module.
7. **Patient Satisfaction Surveys** (6 vote tables) attributed to employees via `login_tacking` → Surveys + Employee Performance + complaint follow-up.
8. `visits` schema in the new edition is wider than the original — update the visits-module blueprint.