140 lines
3.8 KiB
Markdown
140 lines
3.8 KiB
Markdown
# Job Creation Form with Excel Sync
|
|
|
|
> Current Version: v1.0.0-beta3a
|
|
|
|
See CHANGELOG.md for release notes.
|
|
|
|
**Unified system** that combines job creation form with immediate Excel synchronization.
|
|
|
|
## Architecture
|
|
|
|
This system merges:
|
|
- **JobSubmissionFlow**: User form with auto-incrementing Job_Number
|
|
- **SyncToExcelFromPb**: Microsoft Graph API Excel sync logic
|
|
|
|
### Flow
|
|
|
|
1. User submits job form
|
|
2. Server authenticates with PocketBase (agent credentials)
|
|
3. Auto-calculates `Job_Number = max(existing) + 1`
|
|
4. Creates record in PocketBase `Job_Info_Prod`
|
|
5. Captures `record.id` from creation response
|
|
6. Acquires Microsoft Graph API token
|
|
7. Gets Excel table columns
|
|
8. Maps PocketBase fields to Excel columns (case-insensitive, only existing columns)
|
|
9. Adds `pb_id = record.id` to Excel row
|
|
10. POSTs new row to Excel `Test_Table`
|
|
|
|
## Configuration
|
|
|
|
All credentials are in `.env` (copied from existing systems):
|
|
|
|
```bash
|
|
# Azure AD
|
|
CLIENT_ID=3c846e71-9609-40e1-b458-0eb805e21b9f
|
|
CLIENT_SECRET=7aD8Q~d5K~_PzQv6KqDdrEnmyXHE60eVDpbcnaK_
|
|
TENANT_ID=3fd97ea7-b124-41f1-855f-52d8ac3b16c7
|
|
|
|
# PocketBase Agent
|
|
PB_DB=https://pocketbase.ccllc.pro
|
|
PB_AGENT_EMAIL=pbserviceupdate@cardoza.construction
|
|
PB_AGENT_PASSWORD=gJFjYx5y0_BSMgX
|
|
PB_COLLECTION=Job_Info_Prod
|
|
PB_AUTH_COLLECTION=Users
|
|
|
|
# Excel
|
|
DRIVE_ID=b!9YOqqr2xM0G2DFBwMEJYY4UzQgocFddEtpLYWuS9_AtkCKl2q8yjQJteQ7Ti4QSx
|
|
ITEM_ID=01SPNXLDQRICHB63BFUNGKZ3GE6RL7LGBT
|
|
EXCEL_TABLE=/drives/.../tables/Test_Table
|
|
|
|
# Server
|
|
PORT=3020
|
|
```
|
|
|
|
## Installation
|
|
|
|
**Use npm install** (not bun install) to avoid Windows/OneDrive symlink issues:
|
|
|
|
```bash
|
|
npm install
|
|
```
|
|
|
|
## Usage
|
|
|
|
Start the server:
|
|
|
|
```bash
|
|
bun run dev
|
|
```
|
|
|
|
Server runs at: http://localhost:3020
|
|
|
|
## Key Features
|
|
|
|
### Auto-Increment Job Numbers
|
|
- Queries all existing `Job_Number` values
|
|
- Calculates `max + 1` for new job
|
|
- No manual input required
|
|
|
|
### Excel Column Mapping
|
|
- **Case-insensitive**: Matches `Job_Number` to `job_number`, `Company_Client` to `company_client`, etc.
|
|
- **Only existing columns**: Ignores PocketBase fields not in Excel table
|
|
- **Special mapping**: `record.id` → Excel column `pb_id`
|
|
|
|
### Error Handling
|
|
- If Excel sync fails, PocketBase record is still created
|
|
- Returns warning with partial success
|
|
- Logs Excel error details
|
|
|
|
## Endpoints
|
|
|
|
- `POST /api/submit` - Submit job form (creates in PB + Excel)
|
|
- `GET /api/health` - Health check
|
|
|
|
## Technical Details
|
|
|
|
### Dependencies
|
|
- **Hono**: Web framework
|
|
- **PocketBase SDK**: Database client
|
|
- **@microsoft/microsoft-graph-client**: Excel API
|
|
- **dotenv**: Environment config
|
|
|
|
### Authentication
|
|
- **PocketBase**: Service account (`pbserviceupdate@cardoza.construction`)
|
|
- **Microsoft Graph**: OAuth2 client credentials flow
|
|
|
|
### Excel Operations
|
|
- Table path: `EXCEL_TABLE` from .env
|
|
- Operation: `POST /rows` with `{ values: [[...]] }`
|
|
- Row format: 2D array matching column order
|
|
|
|
## Differences from Other Systems
|
|
|
|
### vs. SyncToExcelFromPb
|
|
- **No batch sync**: Creates one record at a time
|
|
- **No duplicate detection**: Always adds new row
|
|
- **No change comparison**: Simple create operation
|
|
- **No collision warnings**: Assumes Job_Number is unique
|
|
|
|
### vs. JobSubmissionFlow
|
|
- **Excel sync included**: Adds to Excel immediately after PB creation
|
|
- **pb_id captured**: Stores PocketBase record.id in Excel
|
|
- **Graph token acquisition**: Handles Microsoft authentication
|
|
- **Column-aware**: Only maps fields that exist in Excel table
|
|
|
|
## Troubleshooting
|
|
|
|
### Bun Package Resolution Error
|
|
**Symptom**: `Cannot find package 'hono'`
|
|
**Solution**: Use `npm install` instead of `bun install`
|
|
|
|
### Excel Sync Fails
|
|
- Check `EXCEL_TABLE` path in .env
|
|
- Verify Azure app has Files.ReadWrite.All permission
|
|
- Check Excel table name matches exactly (case-sensitive in Graph API)
|
|
|
|
### PocketBase Auth Fails
|
|
- Verify `PB_AGENT_EMAIL` and `PB_AGENT_PASSWORD` are correct
|
|
- Check agent account exists in `Users` collection
|
|
- Ensure `PB_DB` URL is accessible
|