Workflow Recipes── Wording checks, batch slide checks, ledger tallies, request-mail tallies and library column audits

"""Check every .docx and .pptx in a folder against a style list and write one Excel workbook for review."""
import csv
import unicodedata
from collections import Counter
from pathlib import Path

from docx import Document
from openpyxl import Workbook
from pptx import Presentation

FOLDER = Path("samples")


def normalize(text):
    return unicodedata.normalize("NFKC", text).casefold()


def load_terms(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        return [(r["term"], r["action"], normalize(r["term"])) for r in csv.DictReader(f) if r["term"].strip()]


def docx_texts(path):
    doc = Document(path)
    for i, p in enumerate(doc.paragraphs, 1):
        yield "paragraph %d" % i, p.text
    for ti, table in enumerate(doc.tables, 1):
        for ri, row in enumerate(table.rows, 1):
            for ci, cell in enumerate(row.cells, 1):
                yield "table %d r%d c%d" % (ti, ri, ci), cell.text


def pptx_texts(path):
    for n, slide in enumerate(Presentation(path).slides, 1):
        for shape in slide.shapes:
            if shape.has_text_frame:
                yield "slide %d %s" % (n, shape.name), shape.text_frame.text
        if slide.has_notes_slide:
            yield "slide %d notes" % n, slide.notes_slide.notes_text_frame.text


READERS = {".docx": docx_texts, ".pptx": pptx_texts}


def check(folder, terms):
    rows = []
    for path in sorted(folder.iterdir()):
        reader = READERS.get(path.suffix.lower())
        if reader is None or path.name.startswith("~$"):
            continue
        for where, text in reader(path):
            key = normalize(text)
            rows += [[path.name, where, term, action, text.strip()] for term, action, k in terms if k in key]
    return rows


def write_book(rows, out="out_style_check.xlsx"):
    wb = Workbook()
    ws = wb.active
    ws.title = "hits"
    ws.append(["file", "where", "term", "action", "text"])
    for r in rows:
        ws.append(r)
    ws.auto_filter.ref = "A1:E%d" % (len(rows) + 1)
    summary = wb.create_sheet("summary")
    summary.append(["file", "hits"])
    for name, n in sorted(Counter(r[0] for r in rows).items()):
        summary.append([name, n])
    wb.save(out)


def main():
    rows = check(FOLDER, load_terms(FOLDER / "style_terms.csv"))
    write_book(rows)
    print("hits:", len(rows))
    for name, n in sorted(Counter(r[0] for r in rows).items()):
        print(" ", name, n)


if __name__ == "__main__":
    main()

"""List returned and overdue requests from the ledger, one sheet per owner, in a new workbook."""
import pandas as pd

AS_OF = pd.Timestamp("2026-09-12")  # fixed so the result can be reproduced; use pd.Timestamp.today() in daily use
LIMIT_DAYS = 10

ledger = pd.read_excel("samples/request_ledger.xlsx", sheet_name="依頼台帳")
ledger["依頼日"] = pd.to_datetime(ledger["依頼日"])
ledger["経過日数"] = (AS_OF - ledger["依頼日"]).dt.days
is_open = ledger["状態"].isin(["受付", "確認中"])
flag = (ledger["状態"] == "差戻し") | (is_open & (ledger["経過日数"] > LIMIT_DAYS))
cols = ["依頼ID", "文書名", "種別", "状態", "依頼日", "経過日数", "担当者"]
todo = ledger.loc[flag, cols].sort_values(["担当者", "経過日数"], ascending=[True, False])

with pd.ExcelWriter("out_followup.xlsx") as writer:
    for owner, rows in todo.groupby("担当者"):
        rows.drop(columns="担当者").to_excel(writer, sheet_name=str(owner), index=False)
print("rows to follow up:", len(todo))
print(todo.groupby("担当者").size().to_string())

"""Match request mail with the ledger: mail after completion, and open requests without any mail."""
import asyncio
import csv
import os
import re
from collections import defaultdict

from azure.identity import DeviceCodeCredential
from kiota_abstractions.base_request_configuration import RequestConfiguration
from msgraph import GraphServiceClient
from msgraph.generated.users.item.mail_folders.item.messages.messages_request_builder import (
    MessagesRequestBuilder,
)

SCOPES = ["Mail.ReadBasic"]  # subject and dates only; no message bodies
REQUEST_ID = re.compile(r"R-\d{4}-\d{3}")


def mail_by_request(messages):
    """messages: [(subject, received ISO string)] -> {request_id: (count, latest)}"""
    seen = defaultdict(list)
    for subject, received in messages:
        m = REQUEST_ID.search(subject or "")
        if m:
            seen[m.group(0)].append(received)
    return {rid: (len(v), max(v)) for rid, v in seen.items()}


def compare(ledger_rows, mail):
    out = []
    for r in ledger_rows:
        rid, state, done = r["依頼ID"], r["状態"], r.get("完了日") or ""
        count, latest = mail.get(rid, (0, ""))
        if state == "完了" and done and latest[:10] > done:
            out.append((rid, "mail after completion", count, latest))
        if count == 0 and state in ("受付", "確認中"):
            out.append((rid, "open request without mail", 0, ""))
    return out


async def fetch_messages(client):
    builder = client.me.mail_folders.by_mail_folder_id("inbox").messages
    query = MessagesRequestBuilder.MessagesRequestBuilderGetQueryParameters(
        select=["subject", "receivedDateTime"], top=100)
    page = await builder.get(request_configuration=RequestConfiguration(query_parameters=query))
    rows = []
    while page:
        rows += [(m.subject, m.received_date_time.isoformat() if m.received_date_time else "") for m in page.value or []]
        if not page.odata_next_link:
            break
        page = await builder.with_url(page.odata_next_link).get()
    return rows


async def main():
    cred = DeviceCodeCredential(client_id=os.environ["CLIENT_ID"], tenant_id=os.environ["TENANT_ID"])
    client = GraphServiceClient(credentials=cred, scopes=SCOPES)
    mail = mail_by_request(await fetch_messages(client))
    with open(os.environ.get("LEDGER_CSV", "ledger.csv"), encoding="utf-8", newline="") as f:
        findings = compare(list(csv.DictReader(f)), mail)
    for row in findings:
        print(*row, sep=" | ")
    print("findings:", len(findings))


if __name__ == "__main__":
    asyncio.run(main())

"""Compare a document library's Status column with the request ledger and list mismatches (report only)."""
import asyncio
import csv
import os

from azure.identity import DeviceCodeCredential
from kiota_abstractions.base_request_configuration import RequestConfiguration
from msgraph import GraphServiceClient
from msgraph.generated.sites.item.lists.item.items.items_request_builder import ItemsRequestBuilder

SCOPES = ["Sites.Read.All"]
COLUMNS = ["FileLeafRef", "DocId", "Status"]


def mismatches(library_rows, ledger):
    """library_rows: [{FileLeafRef, DocId, Status, webUrl}]; ledger: {request ID: status}"""
    out = []
    for r in library_rows:
        doc_id, name = r.get("DocId"), r.get("FileLeafRef")
        if not doc_id:
            out.append((name, "", "DocId is empty", r.get("webUrl")))
        elif doc_id not in ledger:
            out.append((name, doc_id, "not in the ledger", r.get("webUrl")))
        elif (r.get("Status") or "") != ledger[doc_id]:
            out.append((name, doc_id, "library %s / ledger %s" % (r.get("Status"), ledger[doc_id]), r.get("webUrl")))
    return out


async def fetch_rows(client):
    items_rb = client.sites.by_site_id(os.environ["SITE_ID"]).lists.by_list_id(os.environ["LIST_ID"]).items
    query = ItemsRequestBuilder.ItemsRequestBuilderGetQueryParameters(
        expand=["fields(select=%s)" % ",".join(COLUMNS)])
    page = await items_rb.get(request_configuration=RequestConfiguration(query_parameters=query))
    rows = []
    while page:
        for it in page.value or []:
            f = it.fields.additional_data if it.fields else {}
            rows.append({c: f.get(c) for c in COLUMNS} | {"webUrl": it.web_url})
        if not page.odata_next_link:
            break
        page = await items_rb.with_url(page.odata_next_link).get()
    return rows


async def main():
    cred = DeviceCodeCredential(client_id=os.environ["CLIENT_ID"], tenant_id=os.environ["TENANT_ID"])
    client = GraphServiceClient(credentials=cred, scopes=SCOPES)
    rows = await fetch_rows(client)
    with open(os.environ.get("LEDGER_CSV", "ledger.csv"), encoding="utf-8", newline="") as f:
        ledger = {r["依頼ID"]: r["状態"] for r in csv.DictReader(f)}
    found = mismatches(rows, ledger)
    with open("out_library_vs_ledger.csv", "w", encoding="utf-8-sig", newline="") as f:
        csv.writer(f).writerows([("file", "doc_id", "problem", "webUrl")] + found)
    print("library rows:", len(rows), "/ mismatches:", len(found))


if __name__ == "__main__":
    asyncio.run(main())

"""Check only the .docx files that changed since the last run (drive delta + python-docx); report only."""
import asyncio
import csv
import io
import json
import os
import unicodedata
from pathlib import Path

from azure.identity import DeviceCodeCredential
from docx import Document
from msgraph import GraphServiceClient

SCOPES = ["Files.Read.All"]
STATE = Path(os.environ.get("DELTA_STATE", "out_delta_state.json"))


def normalize(text):
    return unicodedata.normalize("NFKC", text).casefold()


def load_terms(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        return [(r["term"], r["action"], normalize(r["term"])) for r in csv.DictReader(f) if r["term"].strip()]


def check_docx(data, terms):
    """data: the bytes of a .docx file -> [(where, term, action)]"""
    doc = Document(io.BytesIO(data))
    hits = []
    for i, p in enumerate(doc.paragraphs, 1):
        key = normalize(p.text)
        hits += [("paragraph %d" % i, term, action) for term, action, k in terms if k in key]
    return hits


async def changed_docx(builder, drive_id):
    first = "https://graph.microsoft.com/v1.0/drives/%s/root/delta" % drive_id
    link = json.loads(STATE.read_text(encoding="utf-8"))["deltaLink"] if STATE.exists() else first
    page, items = await builder.with_url(link).get(), []
    while True:
        items += [i for i in page.value or []
                  if i.deleted is None and i.file is not None and (i.name or "").lower().endswith(".docx")]
        if not page.odata_next_link:
            return items, page.odata_delta_link
        page = await builder.with_url(page.odata_next_link).get()


async def main():
    drive_id = os.environ["DRIVE_ID"]
    cred = DeviceCodeCredential(client_id=os.environ["CLIENT_ID"], tenant_id=os.environ["TENANT_ID"])
    client = GraphServiceClient(credentials=cred, scopes=SCOPES)
    drive = client.drives.by_drive_id(drive_id)
    items, delta_link = await changed_docx(drive.items.by_drive_item_id("root").delta, drive_id)
    terms = load_terms(os.environ.get("TERMS_CSV", "style_terms.csv"))
    for item in items:
        data = await drive.items.by_drive_item_id(item.id).content.get()
        for where, term, action in check_docx(data, terms):
            print(item.name, where, term, action, sep=" | ")
    STATE.write_text(json.dumps({"deltaLink": delta_link}), encoding="utf-8")
    print("changed .docx files:", len(items))


if __name__ == "__main__":
    asyncio.run(main())