140 lines
4.8 KiB
Python
140 lines
4.8 KiB
Python
import mysql.connector
|
|
import db_config
|
|
|
|
conn = mysql.connector.connect(
|
|
host=db_config.DB_HOST, port=db_config.DB_PORT,
|
|
user=db_config.DB_USER, password=db_config.DB_PASSWORD,
|
|
database=db_config.DB_NAME
|
|
)
|
|
c = conn.cursor()
|
|
|
|
# Přidat report_type do iwrs_import (pokud ještě neexistuje)
|
|
try:
|
|
c.execute("""ALTER TABLE iwrs_import
|
|
ADD COLUMN report_type VARCHAR(20) NOT NULL DEFAULT 'patients'
|
|
AFTER source_file""")
|
|
print("ALTER TABLE iwrs_import OK — report_type přidán")
|
|
except mysql.connector.errors.DatabaseError as e:
|
|
if "Duplicate column" in str(e):
|
|
print("report_type již existuje — přeskočeno")
|
|
else:
|
|
raise
|
|
|
|
stmts = [
|
|
(
|
|
"iwrs_shipments",
|
|
"""CREATE TABLE IF NOT EXISTS iwrs_shipments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
import_id INT NOT NULL,
|
|
study VARCHAR(20) NOT NULL,
|
|
shipment_id VARCHAR(20) NOT NULL,
|
|
status VARCHAR(50),
|
|
type VARCHAR(30),
|
|
ship_from VARCHAR(50),
|
|
ship_to_site VARCHAR(50),
|
|
location VARCHAR(50),
|
|
request_date DATE,
|
|
shipped_date DATE,
|
|
received_date DATE,
|
|
received_by VARCHAR(100),
|
|
delivered_date_utc DATE,
|
|
delivery_recipient VARCHAR(100),
|
|
delivery_details VARCHAR(200),
|
|
cancelled_date DATE,
|
|
total_medication_ids SMALLINT,
|
|
tracking_no VARCHAR(100),
|
|
shipping_category VARCHAR(50),
|
|
expected_arrival DATE,
|
|
FOREIGN KEY (import_id) REFERENCES iwrs_import(import_id),
|
|
INDEX idx_import (import_id),
|
|
INDEX idx_study_shipment (study, shipment_id)
|
|
)"""
|
|
),
|
|
(
|
|
"iwrs_shipment_items",
|
|
"""CREATE TABLE IF NOT EXISTS iwrs_shipment_items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
import_id INT NOT NULL,
|
|
study VARCHAR(20) NOT NULL,
|
|
shipment_id VARCHAR(20) NOT NULL,
|
|
destination_location VARCHAR(50),
|
|
shipment_status VARCHAR(50),
|
|
shipment_type VARCHAR(30),
|
|
destination_site VARCHAR(50),
|
|
investigator VARCHAR(100),
|
|
medication_description VARCHAR(200),
|
|
medication_type VARCHAR(50),
|
|
medication_id VARCHAR(20),
|
|
packaged_lot_no VARCHAR(50),
|
|
packaged_lot_description VARCHAR(100),
|
|
container_id VARCHAR(50),
|
|
quantity SMALLINT,
|
|
expiration_date DATE,
|
|
item_status VARCHAR(50),
|
|
FOREIGN KEY (import_id) REFERENCES iwrs_import(import_id),
|
|
INDEX idx_import (import_id),
|
|
INDEX idx_med_id (medication_id)
|
|
)"""
|
|
),
|
|
(
|
|
"iwrs_inventory",
|
|
"""CREATE TABLE IF NOT EXISTS iwrs_inventory (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
import_id INT NOT NULL,
|
|
study VARCHAR(20) NOT NULL,
|
|
site VARCHAR(50),
|
|
investigator VARCHAR(100),
|
|
location VARCHAR(50),
|
|
medication_id VARCHAR(20),
|
|
packaged_lot_no VARCHAR(50),
|
|
original_expiration_date DATE,
|
|
expiration_date DATE,
|
|
received_date DATE,
|
|
receipt_user VARCHAR(100),
|
|
subject_identifier VARCHAR(20),
|
|
quantity_assigned SMALLINT,
|
|
irt_transaction VARCHAR(100),
|
|
date_assigned DATE,
|
|
assignment_user VARCHAR(100),
|
|
dispensation_status VARCHAR(50),
|
|
dispensing_date DATE,
|
|
quantity_dispensed SMALLINT,
|
|
dispensing_user VARCHAR(100),
|
|
quantity_returned SMALLINT,
|
|
date_returned DATE,
|
|
return_user VARCHAR(100),
|
|
FOREIGN KEY (import_id) REFERENCES iwrs_import(import_id),
|
|
INDEX idx_import (import_id),
|
|
INDEX idx_site (study, site)
|
|
)"""
|
|
),
|
|
(
|
|
"iwrs_destruction",
|
|
"""CREATE TABLE IF NOT EXISTS iwrs_destruction (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
study VARCHAR(20) NOT NULL,
|
|
site_id VARCHAR(50),
|
|
investigator VARCHAR(100),
|
|
location VARCHAR(50),
|
|
basket_id VARCHAR(20) NOT NULL,
|
|
destruction_date DATE,
|
|
medication_description VARCHAR(200),
|
|
medication_id VARCHAR(20),
|
|
packaged_lot_description VARCHAR(100),
|
|
comments VARCHAR(500),
|
|
imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_destruction (study, basket_id, medication_id),
|
|
INDEX idx_study_basket (study, basket_id)
|
|
)"""
|
|
),
|
|
]
|
|
|
|
for name, sql in stmts:
|
|
c.execute(sql)
|
|
print(f"OK: {name}")
|
|
|
|
conn.commit()
|
|
c.close()
|
|
conn.close()
|
|
print("\nVšechny tabulky připraveny.")
|