234 lines
7.9 KiB
Python
234 lines
7.9 KiB
Python
"""
|
|
janssenpc_email_send_new v1.4
|
|
Verze: 1.4.1
|
|
Datum: 2026-05-29
|
|
Popis: Prochází složky Inbox, Deleted Items a Sent Items v Outlooku (MAPI),
|
|
ukládá emailové zprávy jako .msg soubory a uploaduje je na https://msgs.buzalka.cz.
|
|
Zaznamenává zpracované zprávy do SQLite DB (jnjemails.db) a DB uploaduje na server
|
|
jednou za 24 hodin (ne při každém běhu). Podporuje pokračování od posledního
|
|
zpracovaného emailu (resume). Folder cesta obsahuje celé jméno schránky
|
|
(např. /vbuzalka@its.jnj.com/Inbox). Chyby se logují do jnjemails_errors.log.
|
|
"""
|
|
import win32com.client
|
|
import requests
|
|
import sqlite3
|
|
import urllib3
|
|
import logging
|
|
from pathlib import Path
|
|
from datetime import datetime, timedelta
|
|
import tempfile
|
|
import io
|
|
|
|
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
|
|
|
|
TOKEN = "13e1bb01-9fd5-44a8-8ce9-4ee27133d340"
|
|
UPLOAD_URL = "https://msgs.buzalka.cz/upload"
|
|
DB_PATH = r"C:\Users\vbuzalka\SQLITE\jnjemails.db"
|
|
DB_UPLOAD_MARKER = r"C:\Users\vbuzalka\SQLITE\jnjemails_last_db_upload.txt"
|
|
DB_UPLOAD_INTERVAL_H = 24
|
|
LOG_PATH = r"C:\Users\vbuzalka\SQLITE\jnjemails_errors.log"
|
|
PR_INTERNET_MESSAGE_ID = "http://schemas.microsoft.com/mapi/proptag/0x1035001E"
|
|
|
|
# olFolderInbox=6, olFolderDeletedItems=3, olFolderSentMail=5
|
|
FOLDERS_TO_PROCESS = [6, 3, 5]
|
|
|
|
UPLOAD_LOG_PATH = r"C:\Users\vbuzalka\SQLITE\jnjemails_uploads.log"
|
|
|
|
logging.basicConfig(
|
|
filename=LOG_PATH,
|
|
level=logging.ERROR,
|
|
format="%(asctime)s | %(message)s",
|
|
datefmt="%Y-%m-%d %H:%M:%S",
|
|
encoding="utf-8",
|
|
)
|
|
|
|
# Separate upload logger — logs every upload attempt
|
|
_upload_log = logging.getLogger("uploads")
|
|
_upload_log.setLevel(logging.DEBUG)
|
|
_uh = logging.FileHandler(UPLOAD_LOG_PATH, encoding="utf-8")
|
|
_uh.setFormatter(logging.Formatter("%(asctime)s | %(message)s", datefmt="%Y-%m-%d %H:%M:%S"))
|
|
_upload_log.addHandler(_uh)
|
|
|
|
def init_db(conn):
|
|
conn.execute("""
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
message_id TEXT NOT NULL,
|
|
subject TEXT,
|
|
sender TEXT,
|
|
received_at TEXT,
|
|
folder TEXT,
|
|
source TEXT,
|
|
uploaded_at TEXT DEFAULT (datetime('now'))
|
|
)
|
|
""")
|
|
conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_message_id ON messages(message_id)")
|
|
conn.commit()
|
|
|
|
def is_uploaded(conn, message_id):
|
|
row = conn.execute(
|
|
"SELECT 1 FROM messages WHERE message_id = ? LIMIT 1", (message_id,)
|
|
).fetchone()
|
|
return row is not None
|
|
|
|
def save_to_db(conn, message_id, subject, sender, received_at, folder, source):
|
|
conn.execute("""
|
|
INSERT OR IGNORE INTO messages (message_id, subject, sender, received_at, folder, source)
|
|
VALUES (?, ?, ?, ?, ?, ?)
|
|
""", (message_id, subject, sender, received_at, folder, source))
|
|
conn.commit()
|
|
|
|
def _db_upload_due() -> bool:
|
|
"""Return True if 24h elapsed since last DB upload (or never uploaded)."""
|
|
marker = Path(DB_UPLOAD_MARKER)
|
|
if not marker.exists():
|
|
return True
|
|
try:
|
|
last = datetime.fromisoformat(marker.read_text().strip())
|
|
return (datetime.now() - last).total_seconds() >= DB_UPLOAD_INTERVAL_H * 3600
|
|
except Exception:
|
|
return True
|
|
|
|
def _db_upload_mark():
|
|
"""Write current timestamp to marker file."""
|
|
Path(DB_UPLOAD_MARKER).write_text(datetime.now().isoformat())
|
|
|
|
def upload_db(db_path, force=False):
|
|
if not force and not _db_upload_due():
|
|
return
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
filename = f"jnjemails_{timestamp}.db"
|
|
with open(db_path, "rb") as f:
|
|
resp = requests.post(
|
|
"https://msgs.buzalka.cz/upload-db",
|
|
headers={"Authorization": f"Bearer {TOKEN}"},
|
|
files={"file": (filename, f, "application/octet-stream")},
|
|
timeout=60
|
|
)
|
|
print(f" DB upload: {resp.json()}")
|
|
_db_upload_mark()
|
|
|
|
def upload_msg(msg_path, filename, folder=""):
|
|
_upload_log.info("UPLOAD %s | folder=%s", filename, folder)
|
|
with open(msg_path, "rb") as f:
|
|
resp = requests.post(
|
|
UPLOAD_URL,
|
|
headers={"Authorization": f"Bearer {TOKEN}"},
|
|
files={"file": (filename, f, "application/octet-stream")},
|
|
data={"folder": folder},
|
|
timeout=60
|
|
)
|
|
resp.raise_for_status()
|
|
result = resp.json()
|
|
_upload_log.info("RESPONSE %s | %s", filename, result)
|
|
return result["status"]
|
|
|
|
def get_folder_resume_date(conn, folder_path):
|
|
row = conn.execute(
|
|
"SELECT MAX(received_at) FROM messages WHERE folder = ?",
|
|
(folder_path,)
|
|
).fetchone()
|
|
if not row or not row[0]:
|
|
return None
|
|
last_dt = datetime.fromisoformat(row[0])
|
|
return last_dt - timedelta(hours=1)
|
|
|
|
def process_folder(conn, folder, source, folder_path="", counter=None):
|
|
if counter is None:
|
|
counter = [0]
|
|
|
|
current_path = f"{folder_path}/{folder.Name}"
|
|
|
|
try:
|
|
resume_dt = get_folder_resume_date(conn, current_path)
|
|
|
|
items = folder.Items
|
|
|
|
if resume_dt:
|
|
resume_str = resume_dt.strftime("%Y/%m/%d %H:%M:%S")
|
|
filter_str = f"@SQL=\"urn:schemas:httpmail:datereceived\" > '{resume_str}'"
|
|
items = folder.Items.Restrict(filter_str)
|
|
print(f"\n Složka: {current_path} | pokračuji od: {resume_str}")
|
|
else:
|
|
print(f"\n Složka: {current_path} | od začátku")
|
|
|
|
items.Sort("[ReceivedTime]", False)
|
|
|
|
count = 0
|
|
skipped = 0
|
|
|
|
for item in items:
|
|
try:
|
|
if not item.MessageClass.upper().startswith("IPM.NOTE"):
|
|
continue
|
|
|
|
try:
|
|
mid = item.PropertyAccessor.GetProperty(PR_INTERNET_MESSAGE_ID)
|
|
except:
|
|
mid = None
|
|
|
|
if not mid:
|
|
mid = f"entryid:{item.EntryID}"
|
|
|
|
if is_uploaded(conn, mid):
|
|
skipped += 1
|
|
continue
|
|
|
|
with tempfile.TemporaryDirectory() as tmp:
|
|
safe_name = f"{item.EntryID[-20:]}.msg"
|
|
tmp_path = Path(tmp) / safe_name
|
|
item.SaveAs(str(tmp_path), 3)
|
|
status = upload_msg(tmp_path, safe_name, current_path)
|
|
|
|
received = item.ReceivedTime.isoformat() if item.ReceivedTime else None
|
|
save_to_db(conn, mid, item.Subject, item.SenderEmailAddress,
|
|
received, current_path, source)
|
|
|
|
counter[0] += 1
|
|
count += 1
|
|
|
|
if counter[0] % 1000 == 0:
|
|
print(f" → celkem {counter[0]} emailů přeneseno, uploaduji DB...")
|
|
upload_db(DB_PATH)
|
|
|
|
print(f" {status.upper():6} | {item.Subject[:60]}")
|
|
|
|
except Exception as e:
|
|
subject = getattr(item, 'Subject', '?')
|
|
sender = getattr(item, 'SenderEmailAddress', '?')
|
|
received = getattr(item, 'ReceivedTime', '?')
|
|
print(f" CHYBA | {subject[:40]} | {e}")
|
|
logging.error("folder=%s | sender=%s | received=%s | subject=%s | error=%s",
|
|
current_path, sender, received, subject, e)
|
|
|
|
print(f" → složka hotova: přeneseno {count} | skip {skipped}")
|
|
|
|
except Exception as e:
|
|
print(f" CHYBA složka {current_path}: {e}")
|
|
logging.error("folder=%s | CHYBA SLOŽKY | error=%s", current_path, e)
|
|
|
|
for subfolder in folder.Folders:
|
|
process_folder(conn, subfolder, source, current_path, counter)
|
|
|
|
# --- MAIN ---
|
|
Path(DB_PATH).parent.mkdir(parents=True, exist_ok=True)
|
|
conn = sqlite3.connect(DB_PATH)
|
|
init_db(conn)
|
|
|
|
outlook = win32com.client.Dispatch("Outlook.Application")
|
|
ns = outlook.GetNamespace("MAPI")
|
|
|
|
counter = [0]
|
|
for folder_id in FOLDERS_TO_PROCESS:
|
|
folder = ns.GetDefaultFolder(folder_id)
|
|
mailbox_name = folder.Parent.Name
|
|
print(f"\n=== {folder.Name} ({mailbox_name}) ===")
|
|
process_folder(conn, folder, "mailbox", f"/{mailbox_name}", counter)
|
|
|
|
# Finální DB upload po dokončení
|
|
print("\nFinální upload DB...")
|
|
upload_db(DB_PATH)
|
|
|
|
conn.close()
|
|
print(f"\nHotovo. Chyby logovány do: {LOG_PATH}")
|