# Module: Financial & Reports (financial)

## Purpose
Covers the clinic's money flow and the entire reporting subsystem of the legacy OB/GYN app:
- Visit fees (recorded inline on the `visits` table, NOT in this module's tables — see workflows).
- A per-patient "total balance" (prepaid package / credit) stored in `totalbalance`, repaid via installment "visits".
- Daily/period financial reports (cash, visa, discounts, debts, refunds, new patients), outstanding-debt report, balance-movement report, drug-usage report.
- Aggregated clinical reports: full activity report, combined (epidemiological) report, complete patient report (+Excel export), complete sheets report, IVF statistics.
- DB maintenance utilities: mysqldump backup, old-DB merge, raw SQL import.

## Controllers & Views (file paths)
| Controller | Views | Role |
|---|---|---|
| /home/amrtechogate/public_html/obgy/core/controllers/financialreport.php | core/views/obgy/reports/financial.html, financialsearch.html, financialprint.html, balance.html, reports/payment/rest.html, restsearch.html, restprint.html, reports/drug/* | Daily/period revenue report, balance report, outstanding-debt ("rest") report, drug-usage report |
| /home/amrtechogate/public_html/obgy/core/controllers/fullreport.php | core/views/obgy/reports/full/full.html, fullsearch.html; reports/expectedprint.html | Per-patient activity matrix over a period (which clinical sections have data) |
| /home/amrtechogate/public_html/obgy/core/controllers/combinedreport.php | core/views/obgy/reports/combined.html, combinedsearch.html, combinedprint.html | Statistical search by age / gyna diagnosis / antenatal diagnosis / operation / termination, with % of patient base |
| /home/amrtechogate/public_html/obgy/core/controllers/completereport.php | core/views/obgy/reports/complete.html, completesearch.html, completeprint.html | Single-patient complete report (demographics + ph* history + examination + investigations + gyna + antenatal); Excel export via `exec(php excel.php)` to core/excel_backups/<statusno>/ ; registers patient in `excelinfopatients` |
| /home/amrtechogate/public_html/obgy/core/controllers/Completesreport.php | core/views/obgy/reports2/completesreport.html | Newer "sheets" report aggregating infertilitysheet + ivfsheet + ancsheet + gynasheet with dozens of lookup tables; has generic `Add()` writing any table/column from POST |
| /home/amrtechogate/public_html/obgy/core/controllers/Ivfstatistics.php | core/views/obgy/reports2/Ivfstatistics.html, Ivfstatisticssearch.html, Ivfstatisticsprint.html | IVF cycle statistics from `ivfsheet` filtered by lmpfresh/lmpfrozen; joins ovst, eprep, antype, icsiprotocol, sseemen, icsisemen; also generic `Add()` |
| /home/amrtechogate/public_html/obgy/core/controllers/backup.php | core/views/obgy/backup/add.html | mysqldump to core/db_backups/ + programesetting.backupdest, browser download; data-fix utilities (`solution()` backfills drugid in gynadrugs/mainantenentaldrugs; `getstatusno()` assigns missing patients.statusno) |
| /home/amrtechogate/public_html/obgy/core/controllers/merge.php | core/views/obgy/merge/add.html | Merges an old clinic DB into current via RedBeanPHP `R::addDatabase`/`selectDatabase`; copies ~40 tables (patients, visits, ph*, examination, investigations, gyna, mainantenental, drugs, sonar, gtimage, programesetting...) then `fixProblems()` |
| /home/amrtechogate/public_html/obgy/core/controllers/newdb.php | core/views/obgy/newdb/add.html | Uploads a .sql file (renamed .txt) and executes it line-by-line via mysqli — raw restore/import |

Related (not assigned but where the money actually lives):
- /home/amrtechogate/public_html/obgy/core/controllers/visits.php — `gettotalbalance()`, `addbalance()` (writes `totalbalance`), visit creation storing fee fields, `addAllDetectionToERP()` (syncs `detections` catalog to an external ERP DB named in `programesetting.erpdb`).
- /home/amrtechogate/public_html/obgy/core/controllers/monitoring.php — balance report search (totalbalance UNION installment visits, filter type 1=both / 2=additions / 3=installments).

## Tables

### totalbalance
Purpose: credit additions to a patient's running account (prepaid/package amount, "إضافة للمبلغ الإجمالي"). One row per top-up. Empty in this dump (AUTO_INCREMENT=1).
```sql
CREATE TABLE IF NOT EXISTS `totalbalance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patientid` int(11) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `deleted` int(11) DEFAULT '0',
  `userid` int(11) DEFAULT NULL,
  `adddate` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
```
Inferred FKs: `patientid` -> patients.id; `userid` -> awusers.user_id (session user who recorded the top-up) (استنتاج).
Written by: visits.php `addbalance()` (AJAX from visit screen). Read by: visits.php `gettotalbalance()` (remaining = SUM(balance) − SUM of visits with detectionid = -99 cash+visa+discount), financialreport.php `balance()`, monitoring.php balance search.

### totalbalancepaids
Purpose (intended): payments against the total balance. **ORPHAN/DEAD table — zero references in any controller or template.** Actual installment payments are recorded as `visits` rows with `detectionid = -99`. Empty in this dump.
```sql
CREATE TABLE IF NOT EXISTS `totalbalancepaids` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patientid` int(11) DEFAULT NULL,
  `paid` float DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `paydate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
```
Inferred FKs: `patientid` -> patients.id; `userid` -> awusers.user_id (استنتاج).

### Context: where visit fees are recorded (not assigned, for completeness)
`visits` carries the financial fields: `detectionid` (-> `detections.id`, the service/fee catalog: id, title, detectionval, del), `totaldetectionvalue`, `discount`, `detectionvalue_cash`, `detectionvalue_visa`, `restdetectionvalue` (all `decimal(10,0)`), plus `visitid` self-reference to the original visit for follow-up money rows.
Hardcoded sentinel detection ids (magic values, no lookup rows):
- `-99` = installment payment against totalbalance ("دفع قسط")
- `999` = payment of the remaining amount of an earlier visit ("دفع متبقى"), linked via `visits.visitid`
- `9999` = customer refund ("مرتجع العملاء"), linked via `visits.visitid`, subtracted in daily report

## Relationships (explicit list)
- totalbalance.patientid -> patients.id
- totalbalance.userid -> awusers.user_id (استنتاج)
- totalbalancepaids.patientid -> patients.id (استنتاج, dead table)
- totalbalancepaids.userid -> awusers.user_id (استنتاج, dead table)
- visits.patientid -> patients.id (fee source for all financial reports)
- visits.detectionid -> detections.id (or sentinel -99/999/9999)
- visits.visitid -> visits.id (self-reference: refund / rest-payment to original visit)
- excelinfopatients.patientid -> patients.id (completereport Excel export queue)
- Report read-paths: gynadrugs/mainantenentaldrugs/ancsheetdrugs/infertilitydrugs/gynasheetdrugs/infertilitysheetdrugs.{patientid,drugid} -> patients.id / drugs.id; ph* + examination + semen/hsg/ustv/laparoscopy/hysteroscopy/mrict/pathology/hormon + gyna + mainantenental + ultrasound* + sonar + operativedetails .patientid -> patients.id; ivfsheet.patientid -> patients.id; ovst.ivfsheetid / eprep.ivfsheetid -> ivfsheet.id; infertilitysheet child tables via infertilitysheetid.

## Business Workflows (traced from code)
1. **Visit fee capture**: reception creates a visit (visits.php), choosing a `detections` service; total/cash/visa/discount/rest are POSTed and stored on the visit row. Debt is whatever stays in `restdetectionvalue`.
2. **Total-balance (package) flow**: `visits.php?do=addbalance` inserts a `totalbalance` row (patientid, balance, userid, adddate, deleted=0) and immediately returns the remaining balance (SUM(totalbalance.balance) − cash/visa/discount of all visits with detectionid=-99 for that patient). Installment payments are entered as visits with detectionid=-99.
3. **Daily financial report** (financialreport index/search/showprint): SELECT over visits JOIN patients LEFT JOIN detections for a date range (+optional wife/husband/address filter); per row resolves sentinel ids to Arabic labels, computes refund per visit (sum of detectionid=9999 children), totals cost/cash/visa/debt/discount, flags new patients (patients.entrydate in range), lists new patients separately.
4. **Balance report** (financialreport `balance()`, search via monitoring.php): UNION ALL of totalbalance additions ("إضافة للمبلغ الاجمالى") and detectionid=-99 visits ("دفع قسط"), net total computed in PHP; filter type 1=both, 2=additions, 3=payments.
5. **Outstanding debt report** (`rest`/`searchPayment`/`printPayment`): visits with restdetectionvalue>0 in range; subtracts later detectionid=999 payments + their discounts; shows remaining dept per visit.
6. **Drug usage report** (`drug`/`drugsearch`/`drugprint`): UNION across 6 prescription tables for a drugid + date range, returning distinct patients.
7. **Full report** (fullreport `search`): distinct patients with visits in range (optionally one detection type); for each, boolean flags per clinical area computed by counting rows in ~30 tables; rendered as a matrix.
8. **Combined report**: dynamic WHERE built from age range / diagnosis ids (comma-joined string equality on gyna.diagnosisid — fragile) / operationid / obstermination over Patients LEFT JOIN gyna/antenalvisit/Operativedetails/phobstetric, grouped by wifename, with percentage of all patients.
9. **Complete report / Completesreport**: per-patient aggregation of all clinical data; completereport also queues patient into `excelinfopatients` and shells out `exec("$programesetting->phppath\php excel.php ...")`, then lists generated files from core/excel_backups/<statusno>/.
10. **IVF statistics**: ivfsheet rows in LMP range (fresh or frozen), per row loads patient, computes trial age, first/last ovst, eprep counts, protocol/semen lookups.
11. **Backup**: `takeit` runs mysqldump (optionally `$backupdriver\bin\mysqldump`) into ../db_backups/obgy_YYYY-MM-DD.sql and programesetting.backupdest, then streams the file as a download. `solution` backfills drugid and statusno.
12. **Merge / newdb**: one-shot migration tools; merge copies ~40 tables from a second DB connection then fixes orphan drug names; newdb executes an uploaded SQL file statement-by-statement.

## ERP Migration Notes (Laravel)
- **Models**: `PatientLedgerEntry` (replaces totalbalance + the sentinel-coded money visits; fields: patient_id, type enum [visit_fee, balance_topup, installment_payment, rest_payment, refund, discount], amount decimal(10,2), method enum [cash, visa], reference_id nullable to original entry/invoice, user_id, entry_date), `Invoice` + `InvoiceItem` (per visit, items from a `Service` model replacing `detections`), `Payment` (one row per tender instead of cash/visa column pair). Compute outstanding debt and remaining balance with queries/accessors — never store derived `rest` values.
- **Drop** `totalbalancepaids` (dead, no data path). Migrate `totalbalance` rows (if any in production) to `balance_topup` ledger entries; migrate visits with detectionid in (-99,999,9999) to typed ledger entries with proper references.
- **Eliminate magic ids**: -99/999/9999 must become enum types; `visits.visitid` self-link becomes `reference_id` FK.
- **Money precision/charset**: legacy uses decimal(10,0) and int for money, latin1 tables; convert to decimal(10,2), utf8mb4, real FK constraints, `deleted_at` soft deletes.
- **Reports**: implement as Laravel query/report classes + API resources for Angular (daily revenue, balance movement, outstanding debts, drug usage, full activity matrix, combined statistics, complete patient file, IVF stats). Replace exec-based Excel export with maatwebsite/excel or PDF generation; drop `excelinfopatients` queue.
- **Kill backup/merge/newdb controllers**: use spatie/laravel-backup or server-level dumps; old-DB merge becomes one-time artisan ETL commands; never allow SQL upload/execution from the UI.
- **Security debt to not carry over**: pervasive string-concatenated SQL (dates, address, husband name), generic `Add()` arbitrary-write endpoints in Completesreport/Ivfstatistics, DB password on shell command line, commented-out hardcoded credentials in backup.php.