Feature #98
closedSEZ Request PDF Download & Dynamic Frappe DocType Attachment Automation
Description
SEZ Request PDF Download & Dynamic Frappe DocType Attachment Automation¶
1. Overview¶
This project automates the complete workflow of retrieving SEZ Request
documents as PDFs from the SEZ Online portal and attaching the
generated PDF to the corresponding Frappe document through the
Frappe REST API.
The implementation combines two automation mechanisms:
Input Excel
(Request ID + Doctype Name)
|
v
Playwright Browser Automation
Login -> CAPTCHA OCR -> Search -> Print -> PDF
|
v
Local Request PDF
|
v
Frappe REST API Processing
Dynamic DocType -> Find Document -> Validate
-> Check Attachment -> Attach PDF
|
v
Updated Excel
Status + Timestamp + Filename + Response
The key design feature is that the Frappe DocType is read dynamically
from the Excel input for each request rather than being fixed in the
source code.
2. What Was Built¶
The final implementation is a bulk-processing automation runner.
Instead of manually performing:
Login to SEZ Portal
|
Search Request ID
|
Open Request
|
View / Print
|
Select Original
|
Generate PDF
|
Find Frappe DocType
|
Find Frappe Document
|
Validate Request ID
|
Validate DocType
|
Check Existing Attachment
|
Upload PDF
|
Update Excel
the program performs the workflow automatically for multiple requests.
The solution is deliberately split into two phases:
Phase 1 --- SEZ Portal¶
Playwright handles all browser/UI operations.
Phase 2 --- Frappe¶
Python Requests handles the Frappe REST API operations after the browser
stage is complete.
3. High-Level Architecture¶
request_list.xlsx
|
+---------------+---------------+
| |
v v
Request ID Doctype Name
| |
+---------------+---------------+
|
v
Python Automation
|
+--------------+--------------+
| |
v v
PLAYWRIGHT LAYER FRAPPE API LAYER
| |
v v
SEZ Online Portal Frappe Instance
| |
Login + CAPTCHA OCR Dynamic DocType
| |
Search Request Find Document
| |
View / Print Validate ID
| |
Generate PDF Validate DocType
| |
+-------------+---------------+
|
v
PDF Attachment
|
v
Updated Excel Tracker
4. Input Excel¶
The automation uses:
request_list.xlsx
The workbook acts as the control file for bulk processing.
Required columns¶
Column Purpose
Request ID Identifies the SEZ request
Doctype Name Identifies the Frappe DocType to process
The code validates that both columns exist before processing starts.
fileciteturn26file0L401-L435
Each valid row is converted into an internal request object containing:
Excel Row Number
Request ID
Doctype Name
fileciteturn26file0L438-L488
5. Excel as the Processing and Audit Tracker¶
The same workbook is updated during execution.
The automation records:
Download Status
Timestamp
Filename
Upload Status
Upload Timestamp
Upload response
This creates a simple audit trail:
Request
|
+--> PDF Download Result
|
+--> Generated Filename
|
+--> Frappe Attachment Result
|
+--> Timestamp
|
+--> Response / Error
The workbook is saved after processing so the latest status is retained.
fileciteturn26file0L490-L537
6. Dynamic DocType Logic¶
This is one of the most important parts of the implementation.
The Frappe DocType is not hardcoded.
Instead:
Excel
|
+-- Request ID
|
+-- Doctype Name
|
v
Python reads value
|
v
URL encode DocType
|
v
/api/resource/<Doctype>
For each request, the code takes the Doctype Name from Excel and uses
it to construct the Frappe REST endpoint.
fileciteturn26file0L947-L982
This allows the same automation to work with different Frappe DocTypes
without changing the core processing logic, provided the selected
DocType follows the expected field structure.
7. Request ID as the Linking Key¶
The Request ID is the main business identifier connecting the two
systems.
SEZ Portal Request
|
| Request ID
v
Excel Control File
|
| Request ID
v
Local PDF
|
| Request ID
v
Frappe Document
|
| Request ID
v
Frappe File Attachment
This creates an end-to-end traceable relationship between the source
request, generated PDF and Frappe document.
8. Playwright Browser Automation¶
The browser stage uses:
from playwright.sync_api import sync_playwright
Chromium is launched in visible mode with:
headless=False- maximized startup
--no-sandbox- custom Chrome user agent
- no fixed viewport
fileciteturn26file0L559-L577
The browser session is created once for the complete SEZ download phase
rather than launching a new browser for every request.
9. Automated SEZ Login¶
The login process is:
Open Login Page
|
v
Enter Username
|
Enter Password
|
v
Capture CAPTCHA
|
v
OCR Processing
|
v
Enter CAPTCHA
|
v
Submit Login
|
v
Handle Confirmation Screen
The implementation locates the username, password, CAPTCHA input, login
button and CAPTCHA image using Playwright.
fileciteturn26file0L199-L225
If the portal redirects to ConfirmUserCredentials.aspx, the automation
attempts to handle the confirmation action automatically.
fileciteturn26file0L243-L252
10. CAPTCHA Automation¶
CAPTCHA processing uses:
OpenCV
+
EasyOCR
The processing pipeline is:
CAPTCHA Screenshot
|
v
Resize 2.5x
|
v
Grayscale
|
v
Bilateral Filtering
|
v
Adaptive Thresholding
|
v
EasyOCR
|
v
Extract Text
|
v
Remove Non-Alphanumeric Characters
|
v
CAPTCHA Token
The OCR engine is initialized for English text with CPU execution.
fileciteturn26file0L67-L68
The image preprocessing and token cleanup are implemented in
resolve_captcha(). fileciteturn26file0L104-L124
11. Popup and Overlay Protection¶
The SEZ portal contains UI overlays/guided-tour elements that can
interfere with automation.
The solution therefore injects a permanent browser initialization
script.
It:
- neutralizes Driver.js behavior
- hides Driver.js overlays/popovers
- hides modal backdrops
- restores pointer events
- restores page scrolling
The CSS guard targets elements including:
.driver-overlay
.driver-popover
.modal-backdrop
.overlay
fileciteturn26file0L128-L165
A second runtime cleanup function removes overlay elements already
present in the page DOM. fileciteturn26file0L168-L184
12. Safe Click Strategy¶
The automation uses a custom safe_click() function.
Clean Popup/Overlay
|
v
Try Forced Playwright Click
|
+---- Success ---> Continue
|
+---- Failure
|
v
Dispatch DOM Click
This provides an additional fallback for UI elements affected by
overlays or browser interaction issues. fileciteturn26file0L187-L194
13. Request Search Workflow¶
For every Excel row:
Search Request
|
v
Clear Date Filters
|
v
Enter Request ID
|
v
Click Search
|
v
Locate Exact Request ID
|
v
Open Request
The date filters are cleared through JavaScript before the Request ID
search.
The request link is then located using the exact Request ID.
fileciteturn26file0L268-L302
14. View / Print Workflow¶
After opening the request:
Request Details
|
v
View / Print
|
v
Print Dialog
|
v
Print Iframe
|
v
Select "Original"
|
v
Click OK
The print dialog is inside:
#dialog-body
The automation therefore uses Playwright's frame_locator() to interact
with the iframe.
The Original checkbox is selected before continuing.
fileciteturn26file0L307-L324
15. New Window and PDF Generation¶
Clicking OK opens the print output in a new browser page.
The automation intercepts this new page using:
context.expect_page()
The flow becomes:
Print Iframe
|
v
OK
|
v
New Browser Page
|
v
Wait for Network Idle
|
v
Generate PDF
The PDF is generated using Playwright's page.pdf() functionality.
fileciteturn26file0L325-L347
16. Safe PDF File Handling¶
Generated PDFs are stored in:
<User Home>\Downloads
The preferred filename is:
<Request ID>.pdf
If that file already exists but is locked/unwritable, the code
automatically searches for a usable alternative:
123456.pdf
123456_1.pdf
123456_2.pdf
...
This avoids failures caused by an open/locked PDF file.
fileciteturn26file0L72-L99
17. PDF Output Validation¶
After generation, the automation verifies:
File Exists?
|
v
File Size > 0?
If the file does not exist or is empty, the request is treated as a
PDF-generation failure.
fileciteturn26file0L349-L361
18. Two-Phase Execution Model¶
The final code intentionally separates the browser and API phases.
Phase 1 --- Download¶
Login Once
|
v
Process All Excel Requests
|
v
Generate All PDFs
|
v
Logout
|
v
Close Browser
Phase 2 --- Frappe¶
Process Downloaded PDFs
|
v
Dynamic DocType Lookup
|
v
Document Validation
|
v
Attachment Check
|
v
PDF Upload
The Frappe stage starts only after the SEZ browser session has been
closed. fileciteturn26file0L748-L783
19. Why the Two Phases Are Separated¶
This design provides a clean boundary:
SEZ Portal
|
| Playwright
v
Local PDF
|
| Requests / REST API
v
Frappe
Benefits include:
- Browser automation is limited to the portal.
- Frappe processing does not require an active browser.
- Download failures and Frappe failures are independent.
- Downloaded PDFs can be processed after the portal session ends.
- The browser is not repeatedly opened and closed for API operations.
20. Frappe API Authentication¶
The Frappe API uses token authentication.
The authentication header is generated centrally:
Authorization:
token <API_KEY>:<API_SECRET>
with:
Accept: application/json
The implementation provides this through get_frappe_headers().
fileciteturn26file0L935-L944
Production deployments should keep credentials outside source code using
environment variables or a secure secret-management mechanism.
21. Dynamic Frappe Document Search¶
For each downloaded request, the automation performs:
Request ID
+
Doctype Name
|
v
/api/resource/<Doctype Name>
|
v
Filter:
request_id = Request ID
The search requests:
name
request_id
docstatus
from Frappe. fileciteturn26file0L970-L999
22. Exact Match Requirement¶
The automation deliberately rejects ambiguous search results.
Frappe Search
|
+-- 0 records --> FAIL
|
+-- 1 record ---> CONTINUE
|
+-- >1 records -> FAIL
This is important because the automation must never guess which Frappe
record should receive the PDF.
The code requires exactly one matching document and extracts its name.
fileciteturn27file0L18-L45
23. Retrieve Complete Frappe Document¶
After finding the document name, the complete document is fetched:
GET
/api/resource/<Doctype>/<Document Name>
The complete response is used for the safety validations that follow.
fileciteturn27file0L47-L72
24. Validation 1 --- Request ID¶
The first validation is:
Excel Request ID
==
Frappe request_id
Values are converted to strings and stripped before comparison.
MATCH
|
v
PASS
MISMATCH
|
v
FAIL
|
v
Stop Frappe Processing
fileciteturn27file0L74-L109
This prevents a PDF for one request from being attached to another
request.
25. Validation 2 --- DocType¶
The second validation is:
Excel Doctype Name
==
Frappe Document Doctype
The actual doctype returned by Frappe is compared with the DocType
specified in Excel.
A mismatch immediately stops processing.
fileciteturn27file0L111-L142
26. Why Both Validations Exist¶
The two validations protect different relationships:
Request ID Validation
Excel Request ID
|
v
Frappe request_id
DocType Validation
Excel Doctype
|
v
Frappe doctype
Therefore, both conditions must pass:
Request ID MATCH
AND
DocType MATCH
|
v
Attachment Processing Allowed
This provides an additional protection layer against incorrect document
mapping.
27. Frappe Document Status Protection¶
The code reads docstatus.
docstatus
|
+-- 0 Draft
| |
| v
| Continue
|
+-- 1 Submitted
| |
| v
| Stop
|
+-- 2 Cancelled
|
v
Stop
The automation therefore does not attach/reprocess the PDF for submitted
or cancelled documents. fileciteturn27file0L499-L551
28. Duplicate PDF Attachment Check¶
Before uploading the PDF, the automation checks whether the
corresponding file is already attached.
It queries the Frappe File resource using:
attached_to_doctype
attached_to_name
file_name
The expected filename is generated from the Request ID:
<Request ID>.pdf
rather than using the potentially suffixed local filename.
fileciteturn27file0L163-L233
29. Why Canonical PDF Naming Matters¶
Suppose the local directory contains:
123456_1.pdf
because:
123456.pdf
was locked.
The duplicate check still uses:
123456.pdf
as the canonical expected attachment name.
Therefore:
Local Filename
123456_1.pdf
|
X
|
Duplicate Check does NOT blindly use it
Canonical Request Filename
123456.pdf
|
v
Frappe File Check
This prevents a duplicate upload from bypassing the existing-attachment
check.
30. Duplicate Handling¶
The logic is:
Check Existing Attachment
|
+---- Found
| |
| v
| Skip Upload
| |
| v
| Status:
| Already Attached
|
+---- Not Found
|
v
Upload PDF
When an existing attachment is found, the function returns an
already_attached result and the upload is skipped.
fileciteturn27file0L258-L286
31. PDF Upload to Frappe¶
If no matching attachment exists, the PDF is uploaded through:
POST
/api/method/upload_file
The multipart request contains:
file
doctype
docname
is_private
The PDF is therefore attached directly to the identified Frappe
document. fileciteturn27file0L287-L406
32. Upload Response¶
After upload, the API response is parsed as JSON.
When available, the implementation extracts:
message.file_url
The process reports:
Frappe Document
Attached File
Frappe File URL
This provides traceability between the generated local PDF and its
Frappe attachment. fileciteturn27file0L427-L497
33. Frappe Processing Orchestrator¶
The main function for backend processing is:
process_pdf_in_frappe(
request_id,
doctype_name,
pdf_path
)
Its workflow is:
Find Document
|
v
Check docstatus
|
+---- Submitted/Cancelled --> Stop
|
v
Check Existing Attachment
|
+---- Existing --> Skip Upload
|
+---- Missing --> Upload PDF
|
v
Return Result
The function returns:
request_id
frappe_docname
system_doc_no
pdf_path
already_attached
fileciteturn27file0L499-L589
34. Bulk Processing Logic¶
The main runner performs:
Read Excel
|
v
Validate Input
|
v
Launch Browser
|
v
Login Once
|
v
Download PDFs for All Requests
|
v
Logout
|
v
Close Browser
|
v
Process Downloaded PDFs Through Frappe
|
v
Update Excel
|
v
Save Final Workbook
The main function explicitly collects successfully downloaded PDFs
before beginning the Frappe processing phase.
fileciteturn26file0L601-L783
35. Failure Isolation¶
The automation is designed so that one request failure does not stop the
remaining requests.
Example:
Request 1 → Success
Request 2 → Success
Request 3 → Failed
Request 4 → Success
Request 5 → Success
For a request-level failure:
Catch Exception
|
v
Update Excel
|
v
Save Workbook
|
v
Continue Next Request
The browser-stage download failure specifically prevents Frappe
processing for that request while allowing the remaining requests to
continue. fileciteturn26file0L674-L701
36. Frappe Failure Handling¶
A Frappe processing failure is recorded in Excel with:
Upload Status
Upload Timestamp
Upload response
The failed request is then skipped and the next downloaded request is
processed.
fileciteturn26file0L882-L917
37. Diagnostic Screenshot¶
If the overall SEZ browser pipeline encounters an unexpected fatal
error, the automation captures:
screenshots/failure.png
The screenshot is taken as a full-page diagnostic artifact before the
browser is closed. fileciteturn26file0L715-L741
38. Logout and Resource Cleanup¶
After all SEZ PDFs have been downloaded:
All Downloads Complete
|
v
Logout
|
v
Close Playwright Context
|
v
Close Browser
The logout function explicitly interacts with the portal logout control
and waits for the navigation to complete where possible.
fileciteturn26file0L363-L399
39. Dependencies¶
Python Standard Library¶
os
re
time
json
warnings
pathlib
datetime
urllib.parse
Used for:
- file handling
- string processing
- JSON processing
- timestamps
- URL encoding
- runtime/environment handling
Third-Party Packages¶
Package Purpose
playwright SEZ portal browser automation
requests Frappe REST API communication
openpyxl Read/update Excel control file
opencv-python / cv2 CAPTCHA image preprocessing
easyocr CAPTCHA text extraction
These dependencies are imported directly by the final implementation.
fileciteturn26file0L12-L24
40. Browser Dependency¶
Playwright requires a supported browser installation, specifically
Chromium for the current implementation.
The browser is launched through Playwright:
p.chromium.launch(...)
The automation therefore depends on Playwright's Chromium browser
environment in addition to the Python package.
41. External System Dependencies¶
The automation requires:
SEZ Online Portal
|
+-- Valid Login
+-- Search Request
+-- View / Print
+-- PDF Generation
Frappe Instance
|
+-- REST API Access
+-- Dynamic DocType
+-- request_id field
+-- docstatus field
+-- File attachment support
It also requires:
SEZ Credentials
Frappe API Key
Frappe API Secret
request_list.xlsx
42. Expected Frappe DocType Structure¶
Although the DocType name is dynamic, the integration expects the
selected DocType to provide:
Required lookup field¶
request_id
Document identifier¶
name
Processing state¶
docstatus
Result information¶
system_doc_no
Therefore, dynamic DocType support means that the DocType name is
configurable, while the selected DocType still needs to follow the
integration contract.
43. Frappe REST APIs Used¶
Search Dynamic DocType¶
GET /api/resource/<Doctype>
Purpose:
Find document where request_id = Excel Request ID
Retrieve Full Document¶
GET /api/resource/<Doctype>/<Document Name>
Purpose:
Validate request_id
Validate doctype
Read docstatus
Read system_doc_no
Search Existing File¶
GET /api/resource/File
Purpose:
Check whether Request-ID PDF is already attached
Upload PDF¶
POST /api/method/upload_file
Purpose:
Attach generated PDF to the Frappe document
44. Output¶
The system produces three practical outputs.
A. Generated PDFs¶
Stored in:
<User Home>\Downloads
with Request-ID-based names.
B. Updated Excel Tracker¶
Contains:
Request ID
Doctype Name
Download Status
Timestamp
Filename
Upload Status
Upload Timestamp
Upload response
C. Diagnostic Screenshot¶
Created for unexpected browser-stage failures:
screenshots/failure.png
45. Processing Status Model¶
The overall processing states can be represented as:
REQUEST
|
v
PDF DOWNLOAD
/ Success Failed
| |
v v
Frappe Processing Stop
|
v
Document Found?
/ No Yes
| |
Fail v
Request ID Match?
/ No Yes
| |
Fail v
DocType Match?
/ No Yes
| |
Fail v
Check Attachment
/ Found Missing
| |
v v
Already Upload PDF
Attached |
v
Success
46. Re-Run Safety¶
The automation contains several safeguards for rerunning the process.
Find Existing Document
|
v
Validate Request ID
|
v
Validate DocType
|
v
Validate Document Status
|
v
Check Existing Attachment
|
+---- Exists --> Skip
|
+---- Missing -> Upload
This makes the workflow safer for repeated execution and reduces the
risk of duplicate PDF attachments.
47. Important Design Decisions¶
1. Dynamic DocType¶
The DocType is controlled through Excel.
Excel → Doctype Name → Frappe REST Resource
2. Request ID-Based Mapping¶
The Request ID is used as the cross-system identifier.
3. Dual Validation¶
Both Request ID and DocType must match.
4. Exact Match Requirement¶
Zero or multiple Frappe documents are treated as errors.
5. Document-State Protection¶
Submitted and cancelled documents are not processed.
6. Duplicate Attachment Protection¶
Existing PDFs are detected before upload.
7. Two-Phase Processing¶
SEZ browser work completes before Frappe API processing begins.
8. Request-Level Failure Isolation¶
One failed request does not terminate the complete batch.
9. Live Excel Tracking¶
Processing results are written back to the same Excel control file.
48. What the Automation Actually Does¶
It does¶
✓ Read Request IDs from Excel
✓ Read dynamic DocType names from Excel
✓ Open SEZ Online
✓ Automate login
✓ Process CAPTCHA using OCR
✓ Search requests
✓ Open request details
✓ Open View / Print
✓ Handle print iframe
✓ Select Original document
✓ Capture new print window
✓ Generate PDF
✓ Validate PDF output
✓ Store PDF locally
✓ Update download status
✓ Logout from SEZ
✓ Query dynamic Frappe DocType
✓ Find existing Frappe document
✓ Validate Request ID
✓ Validate DocType
✓ Validate document status
✓ Check existing PDF attachment
✓ Upload PDF to Frappe
✓ Record upload status
✓ Continue after individual failures
It does not¶
✗ Create a new Frappe DocType
✗ Create a new Frappe document
✗ Submit a Frappe document
✗ Assume one fixed DocType
✗ Upload without validating the target document
The implementation finds an existing Frappe document and attaches
the generated PDF to it.
49. End-to-End Data Flow¶
INPUT
|
v
request_list.xlsx
|
+------------+------------+
| |
v v
Request ID Doctype Name
| |
+------------+------------+
|
v
Playwright Runner
|
v
SEZ Online Login
|
v
CAPTCHA OCR
|
v
Search Request
|
v
View / Print
|
v
Print Iframe
|
v
Select "Original"
|
v
New Print Page
|
v
Generate PDF
|
v
Validate PDF
|
v
Local PDF File
|
v
Frappe REST API
|
v
Dynamic DocType Search
|
v
Find Exact Document
|
v
Fetch Full Document
|
v
+------------+------------+
| |
v v
Request ID Check DocType Check
| |
+------------+------------+
|
v
Check Docstatus
|
v
Existing File Check
|
+---------+---------+
| |
Existing Missing
| |
v v
Already Attached Upload PDF
| |
+---------+---------+
|
v
Update Excel
|
v
COMPLETE
50. Final Implementation Outcome¶
The completed automation creates a controlled integration between the
SEZ Online portal, the Excel bulk-control file, and the Frappe
backend.
The complete relationship is:
SEZ Request
|
| Request ID
v
Excel Control File
|
| Request ID + Dynamic DocType
v
Playwright Automation
|
v
Generated PDF
|
v
Frappe REST API
|
+--> Find Existing Document
|
+--> Validate Request ID
|
+--> Validate DocType
|
+--> Validate Document Status
|
+--> Check Existing Attachment
|
+--> Attach PDF
|
v
Updated Excel Audit Status
The architecture uses Playwright for SEZ browser automation,
OpenCV + EasyOCR for CAPTCHA processing, OpenPyXL for Excel-driven
bulk control and status tracking, and Requests for direct Frappe
REST API integration.
The most significant implementation feature is the dynamic DocType
mapping from Excel, combined with Request ID validation, DocType
validation, document-status protection and duplicate attachment
detection before the PDF is attached.
This makes the solution a reusable bulk-processing utility rather than a
script tied to a single Frappe DocType or a single request.
Files