0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@wtmhk

Exmentのデータ一覧画面に簡易集計機能を付けてみた

0
Last updated at Posted at 2026-03-28

※　2026年3月30日　追記

前回、請求書のPDFは出力できるようになりましたが、今度はフィルタ検索時にパッと集計できないかと要望がありました。

Exmentには集計ビュー機能がありますが、もっと手軽に集計させたいとの要望でした。

機能

  • データ一覧画面のtdからデータを取得する仕様

  • ページングに関係なく、条件に合致した件数を全件取得

  • フィルタ検索にも対応し、ページ読み込みの際に自動で再集計

  • 定義したカスタム列がデータ一覧にない場合は0円表示

  • 平均・中央値・最頻値・最大値・最小値に対応

  • 読み込み中は集計中であることを表示し、取得後は集計日時を表示
    2026-03-28 16 46 08.png

  • 画面の上部に配置し、データ一覧のテーブルスクロールを制御することで上部固定化も可能

  • データ一覧画面・特定のカスタムテーブル（URL）で動作するように設定

chatGPTに以下のコードを書いてもらい、プラグインのスクリプトで実装しました。

calc_grid.js
(function () {
    const TARGET_PATH = "/exment/admin/data/send_invoice";
    if (!location.pathname.includes(TARGET_PATH)) return;
    if (!document.querySelector(".block_custom_value_grid")) return;

    if (window.__exmentSummaryRunning) return;
    window.__exmentSummaryRunning = true;

    document.getElementById("summary-cards")?.remove();

    const box = document.createElement("div");
    box.id = "summary-cards";
    box.style = `
        display:grid;
        grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
        gap:10px;
        margin-bottom:5px;
    `;

    function card(label) {
        const el = document.createElement("div");
        el.style = `
            background:#fff;
            border-radius:0px;
            padding: 8px 10px 4px;
            box-shadow:0 2px 6px rgba(0,0,0,0.1);
            border-left:5px solid darkgreen;
        `;
        el.innerHTML = `
            <div style="font-size:12px;color:#444">${label}</div>
            <div style="font-size:18px;font-weight:bold">集計中･･･</div>
            <div class="summary-datetime" style="font-size:11px;color:#444;margin-top:0px;"></div>
        `;
        return el;
    }

    // =========================
    // カード定義
    // =========================
    const cards = {
        total: card("合計金額(税込)"),
        count: card("件数"),
        avg: card("平均金額(税込)"),
        median: card("中央金額(税込)"),
        mode: card("最頻金額(税込)"),
        max: card("最大金額(税込)/件"),
        min: card("最小金額(税込)/件"),
        tax10_total: card("消費税額計"),
        gross_total: card("粗利金額"),
        gross_avg: card("平均粗利金額"),
        gross_rate: card("粗利率(%)"),
        totalexe_total: card("原価合計"),
        totalexe_avg: card("平均原価(税込)"),
        subtotal_total: card("小計金額"),
        subtotal_avg: card("平均小計金額"),
        progress: card("処理進捗")
    };

    [
        cards.total,
        cards.count,
        cards.avg,
        cards.median,
        cards.mode,
        cards.max,
        cards.min,
        cards.tax10_total,
        cards.gross_total,
        cards.gross_avg,
        cards.gross_rate,
        cards.totalexe_total,
        cards.totalexe_avg,
        cards.subtotal_total,
        cards.subtotal_avg,
        cards.progress
    ].forEach(c => box.appendChild(c));
    document.querySelector(".content")?.prepend(box);

    const set = (c, v) => c.children[1].innerText = v;

    async function fetchWithRetry(url, retries = 3) {
        for (let i = 0; i < retries; i++) {
            try {
                const res = await fetch(url, {
                    credentials: "same-origin",
                    headers: { "X-Requested-With": "XMLHttpRequest" }
                });
                return await res.text();
            } catch (e) {
                if (i === retries - 1) throw e;
                await new Promise(r => setTimeout(r, 500));
            }
        }
    }

    async function run() {
        let total = 0, count = 0, max = 0, min = null;
        let grossTotal = 0;
        let totalExeTotal = 0;
        let subtotalTotal = 0;
        let tax10Total = 0;
        const values = [];

        const baseParams = new URLSearchParams(location.search);
        baseParams.set("per_page", 1000);

        let page = 1;
        let hasNext = true;

        set(cards.progress, "開始");

        while (hasNext) {
            baseParams.set("page", page);
            const url = location.pathname + "?" + baseParams.toString();
            set(cards.progress, `データ取得中`);

            let text;
            try {
                text = await fetchWithRetry(url);
            } catch (e) {
                set(cards.progress, "通信エラー");
                return;
            }

            let rows = [];
            try {
                const json = JSON.parse(text);
                rows = json.data || [];
                hasNext = !!json.next_page_url;
            } catch (e) {
                const doc = new DOMParser().parseFromString(text, "text/html");

                const grandTotals = doc.querySelectorAll("td.column-grandtotal");
                const grossProfits = doc.querySelectorAll("td.column-gross_profit");
                const totalExes = doc.querySelectorAll("td.column-totalexe");
                const subtotals = doc.querySelectorAll("td.column-subtotal");
                const tax10s = doc.querySelectorAll("td.column-tax10");

                const length = Math.max(grandTotals.length, grossProfits.length, totalExes.length, subtotals.length, tax10s.length);
                rows = [];
                for (let i = 0; i < length; i++) {
                    rows.push({
                        grandtotal: grandTotals[i]?.innerText,
                        gross_profit: grossProfits[i]?.innerText,
                        totalexe: totalExes[i]?.innerText,
                        subtotal: subtotals[i]?.innerText,
                        tax10: tax10s[i]?.innerText
                    });
                }

                hasNext = !!doc.querySelector(".pagination .next:not(.disabled)");
            }

            if (rows.length === 0) break;

            rows.forEach(row => {
                // 合計金額
                let value = row.grandtotal;
                if (!value) return;
                value = parseInt(String(value).replace(/[^\d]/g, ""), 10);
                if (isNaN(value)) return;

                total += value;
                count++;
                values.push(value);

                if (value > max) max = value;
                if (min === null || value < min) min = value;

                // 粗利
                let gp = row.gross_profit || "0";
                gp = parseInt(String(gp).replace(/[^\d]/g, ""), 10);
                if (!isNaN(gp)) grossTotal += gp;

                // 原価
                let te = row.totalexe || "0";
                te = parseInt(String(te).replace(/[^\d]/g, ""), 10);
                if (!isNaN(te)) totalExeTotal += te;

                // 小計
                let st = row.subtotal || "0";
                st = parseInt(String(st).replace(/[^\d]/g, ""), 10);
                if (!isNaN(st)) subtotalTotal += st;

                // 消費税
                let tax = row.tax10 || "0";
                tax = parseInt(String(tax).replace(/[^\d]/g, ""), 10);
                if (!isNaN(tax)) tax10Total += tax;
            });

            page++;
            if (page > 1000) break;
        }

        const avg = count ? Math.round(total / count) : 0;
        const grossAvg = count ? Math.round(grossTotal / count) : 0;
        const totalExeAvg = count ? Math.round(totalExeTotal / count) : 0;
        const subtotalAvg = count ? Math.round(subtotalTotal / count) : 0;

        let median = 0;
        if (values.length) {
            values.sort((a, b) => a - b);
            const mid = Math.floor(values.length / 2);
            median = values.length % 2 !== 0
                ? values[mid]
                : Math.round((values[mid - 1] + values[mid]) / 2);
        }

        let mode = 0;
        if (values.length) {
            const freq = {};
            let maxCount = 0;
            values.forEach(v => {
                freq[v] = (freq[v] || 0) + 1;
                if (freq[v] > maxCount) maxCount = freq[v];
            });
            mode = Math.min(...Object.keys(freq).filter(k => freq[k] === maxCount).map(Number));
        }

        // 表示
        set(cards.total, total.toLocaleString() + "円");
        set(cards.count, count + "件");
        set(cards.avg, avg.toLocaleString() + "円");
        set(cards.median, median.toLocaleString() + "円");
        set(cards.mode, mode.toLocaleString() + "円");
        set(cards.max, max.toLocaleString() + "円");
        set(cards.min, (min ?? 0).toLocaleString() + "円");

        set(cards.tax10_total, tax10Total.toLocaleString() + "円");

        set(cards.gross_total, grossTotal.toLocaleString() + "円");
        set(cards.gross_avg, grossAvg.toLocaleString() + "円");
        set(cards.gross_rate, total ? ((grossTotal / total) * 100).toFixed(1) + "%" : "0%");

        set(cards.totalexe_total, totalExeTotal.toLocaleString() + "円");
        set(cards.totalexe_avg, totalExeAvg.toLocaleString() + "円");

        set(cards.subtotal_total, subtotalTotal.toLocaleString() + "円");
        set(cards.subtotal_avg, subtotalAvg.toLocaleString() + "円");

        const now = new Date();
        const datetimeStr = `${now.getFullYear()}/${(now.getMonth()+1).toString().padStart(2,'0')}/${now.getDate().toString().padStart(2,'0')} ` +
                            `${now.getHours().toString().padStart(2,'0')}:${now.getMinutes().toString().padStart(2,'0')}:${now.getSeconds().toString().padStart(2,'0')}`;
        set(cards.progress, `データ取得完了`);
        cards.progress.querySelector(".summary-datetime").innerText = `集計日時: ${datetimeStr}`;
    }

    run();

    let current = location.href;
    setInterval(() => {
        if (current !== location.href) {
            current = location.href;
            window.__exmentSummaryRunning = false;
            location.reload();
        }
    }, 1000);
})();

実装した実際の画面

2026-03-28 16 20 58.png

実装部拡大

2026-03-28 16 48 33.png

課題

動作が重い

  • 今はブラウザベースなので、ちょっともっさり気味。Laravel側に組み込むとなると、Exment本体側のカスタマイズが必要そう･･･。
  • ページ読み込み時にうまく表示されないことがある。これはExment:list_loadedにすれば改善するのか？
    だいぶ改善したが、件数が多いと読み込みが遅い。

任意の項目ON・OFFに対応していない

  • 現在は合計・小計・消費税・粗利・原価のカスタム列に固定されている
  • 上記項目を表に表示していないデータ一覧では0円表示→　非表示にできるといいなぁ･･･

見た目を調整した結果

calc_grid.js(改良）
$(function () {
    $(window)
        .off('exment:list_loaded', RowAction)
        .on('exment:list_loaded', RowAction);

    function RowAction() {
        window.__exmentSummaryRunning = false;
        CalcGrid1();
    }

    function CalcGrid1() {

        if (!document.querySelector(".block_custom_value_grid.custom_value_send_invoice.row")) return;
        if (window.__exmentSummaryRunning) return;
        window.__exmentSummaryRunning = true;

        document.getElementById("summary-cards")?.remove();

        const box = document.createElement("div");
        box.id = "summary-cards";
        box.style = `
            display:grid;
            grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
            gap:12px;
            margin-bottom:8px;
        `;

        function card(label) {
            const el = document.createElement("div");

            el.style = `
                background:#ffffff;
                color:#333;
                padding:10px 12px 6px;
                border-radius:0px;
                box-shadow:0 4px 12px rgba(0,0,0,0.08);
                border-left:6px solid cornflowerblue;
                transition: all 0.25s ease;
                position:relative;
            `;

            el.onmouseenter = () => {
                el.style.transform = "translateY(-2px)";
                el.style.boxShadow = "0 6px 18px rgba(0,0,0,0.12)";
            };
            el.onmouseleave = () => {
                el.style.transform = "translateY(0)";
                el.style.boxShadow = "0 4px 12px rgba(0,0,0,0.08)";
            };

            el.innerHTML = `
                <div style="font-size:12px;color:#666;margin-bottom:2px;">${label}</div>
                <div style="font-size:18px;font-weight:600;">集計中･･･</div>
                <div class="summary-datetime" style="font-size:10px;color:#888;margin-top:2px;"></div>
            `;
            return el;
        }

        const cards = {
            total: card("合計金額(税込)"),
            count: card("件数"),
            avg: card("平均金額(税込)"),
            median: card("中央金額(税込)"),
            mode: card("最頻金額(税込)"),
            max: card("最大金額(税込)/件"),
            min: card("最小金額(税込)/件"),
            tax10_total: card("消費税額計"),
            gross_total: card("粗利金額"),
            gross_avg: card("平均粗利金額"),
            gross_rate: card("粗利率(%)"),
            totalexe_total: card("原価合計"),
            totalexe_avg: card("平均原価(税込)"),
            subtotal_total: card("小計金額"),
            subtotal_avg: card("平均小計金額"),
            progress: card("処理進捗")
        };

        Object.values(cards).forEach(c => box.appendChild(c));

        function applyCardStyle(cardList, bg, color) {
            cardList.forEach(c => {
                c.style.background = bg;
                c.style.color = color;
                c.style.borderLeft = `6px solid ${color}`;
                c.children[0].style.color = color;
                c.children[1].style.color = color;
            });
        }

        applyCardStyle([cards.totalexe_total, cards.totalexe_avg], "#e6f4ff", "mediumblue");
        applyCardStyle([cards.gross_total, cards.gross_avg, cards.gross_rate], "#e9f7ef", "forestgreen");
        applyCardStyle([cards.subtotal_total, cards.subtotal_avg], "#f3e8ff", "indigo");

        document.querySelector(".content")?.prepend(box);

        // ✅ 非表示＋詰め＋progress固定
        const set = (c, v, rawValue = null, isPercent = false) => {

            c.children[1].innerText = v;

            // progressは常に表示＆最後へ
            if (c === cards.progress) {
                box.appendChild(c);
                return;
            }

            let num = rawValue;
            if (num === null) {
                num = parseFloat(String(v).replace(/[^\d.-]/g, ""));
            }

            const isZero = isPercent ? num === 0 : num === 0;

            if (isZero) {
                if (c.parentNode) c.parentNode.removeChild(c);
            } else {
                if (!c.parentNode) box.appendChild(c);
            }
        };

        async function fetchWithRetry(url, retries = 3) {
            for (let i = 0; i < retries; i++) {
                try {
                    const res = await fetch(url, {
                        credentials: "same-origin",
                        headers: { "X-Requested-With": "XMLHttpRequest" }
                    });
                    return await res.text();
                } catch (e) {
                    if (i === retries - 1) throw e;
                    await new Promise(r => setTimeout(r, 500));
                }
            }
        }

        async function run() {

            set(cards.progress, "データ取得中...");

            let total = 0, count = 0, max = 0, min = null;
            let grossTotal = 0;
            let totalExeTotal = 0;
            let subtotalTotal = 0;
            let tax10Total = 0;
            const values = [];

            const baseParams = new URLSearchParams(location.search);
            baseParams.set("per_page", 1000);

            let page = 1;
            let hasNext = true;

            while (hasNext) {
                baseParams.set("page", page);
                const url = location.pathname + "?" + baseParams.toString();

                let text;
                try {
                    text = await fetchWithRetry(url);
                } catch (e) {
                    set(cards.progress, "通信エラー");
                    return;
                }

                let rows = [];
                try {
                    const json = JSON.parse(text);
                    rows = json.data || [];
                    hasNext = !!json.next_page_url;
                } catch (e) {
                    const doc = new DOMParser().parseFromString(text, "text/html");

                    const grandTotals = doc.querySelectorAll("td.column-grandtotal");
                    const grossProfits = doc.querySelectorAll("td.column-gross_profit");
                    const totalExes = doc.querySelectorAll("td.column-totalexe");
                    const subtotals = doc.querySelectorAll("td.column-subtotal");
                    const tax10s = doc.querySelectorAll("td.column-tax10");

                    const length = Math.max(
                        grandTotals.length,
                        grossProfits.length,
                        totalExes.length,
                        subtotals.length,
                        tax10s.length
                    );

                    rows = [];
                    for (let i = 0; i < length; i++) {
                        rows.push({
                            grandtotal: grandTotals[i]?.innerText,
                            gross_profit: grossProfits[i]?.innerText,
                            totalexe: totalExes[i]?.innerText,
                            subtotal: subtotals[i]?.innerText,
                            tax10: tax10s[i]?.innerText
                        });
                    }

                    hasNext = !!doc.querySelector(".pagination .next:not(.disabled)");
                }

                if (rows.length === 0) break;

                rows.forEach(row => {
                    let value = parseInt(String(row.grandtotal || "").replace(/[^\d]/g, ""), 10);
                    if (isNaN(value)) return;

                    total += value;
                    count++;
                    values.push(value);

                    if (value > max) max = value;
                    if (min === null || value < min) min = value;

                    let gp = parseInt(String(row.gross_profit || "0").replace(/[^\d]/g, ""), 10);
                    if (!isNaN(gp)) grossTotal += gp;

                    let te = parseInt(String(row.totalexe || "0").replace(/[^\d]/g, ""), 10);
                    if (!isNaN(te)) totalExeTotal += te;

                    let st = parseInt(String(row.subtotal || "0").replace(/[^\d]/g, ""), 10);
                    if (!isNaN(st)) subtotalTotal += st;

                    let tax = parseInt(String(row.tax10 || "0").replace(/[^\d]/g, ""), 10);
                    if (!isNaN(tax)) tax10Total += tax;
                });

                page++;
                if (page > 1000) break;
            }

            const avg = count ? Math.round(total / count) : 0;
            const grossAvg = count ? Math.round(grossTotal / count) : 0;
            const totalExeAvg = count ? Math.round(totalExeTotal / count) : 0;
            const subtotalAvg = count ? Math.round(subtotalTotal / count) : 0;

            let median = 0;
            if (values.length) {
                values.sort((a, b) => a - b);
                const mid = Math.floor(values.length / 2);
                median = values.length % 2 !== 0
                    ? values[mid]
                    : Math.round((values[mid - 1] + values[mid]) / 2);
            }

            let mode = 0;
            if (values.length) {
                const freq = {};
                let maxCount = 0;
                values.forEach(v => {
                    freq[v] = (freq[v] || 0) + 1;
                    if (freq[v] > maxCount) maxCount = freq[v];
                });
                mode = Math.min(...Object.keys(freq).filter(k => freq[k] === maxCount).map(Number));
            }

            const grossRate = total ? (grossTotal / total) * 100 : 0;

            set(cards.total, total.toLocaleString() + "円", total);
            set(cards.count, count + "件", count);
            set(cards.avg, avg.toLocaleString() + "円", avg);
            set(cards.median, median.toLocaleString() + "円", median);
            set(cards.mode, mode.toLocaleString() + "円", mode);
            set(cards.max, max.toLocaleString() + "円", max);
            set(cards.min, (min ?? 0).toLocaleString() + "円", min ?? 0);

            set(cards.tax10_total, tax10Total.toLocaleString() + "円", tax10Total);
            set(cards.gross_total, grossTotal.toLocaleString() + "円", grossTotal);
            set(cards.gross_avg, grossAvg.toLocaleString() + "円", grossAvg);
            set(cards.gross_rate, grossRate.toFixed(1) + "%", grossRate, true);

            set(cards.totalexe_total, totalExeTotal.toLocaleString() + "円", totalExeTotal);
            set(cards.totalexe_avg, totalExeAvg.toLocaleString() + "円", totalExeAvg);

            set(cards.subtotal_total, subtotalTotal.toLocaleString() + "円", subtotalTotal);
            set(cards.subtotal_avg, subtotalAvg.toLocaleString() + "円", subtotalAvg);

            // ✅ 集計日時
            const now = new Date();
            const datetimeStr =
                now.getFullYear() + "/" +
                String(now.getMonth() + 1).padStart(2, "0") + "/" +
                String(now.getDate()).padStart(2, "0") + " " +
                String(now.getHours()).padStart(2, "0") + ":" +
                String(now.getMinutes()).padStart(2, "0") + ":" +
                String(now.getSeconds()).padStart(2, "0");

            set(cards.progress, "データ取得完了");
            cards.progress.querySelector(".summary-datetime").innerText =
                "集計日時: " + datetimeStr;

            // ✅ データなし判定
            const visibleCards = Array.from(box.children);
            const hasOnlyProgress = visibleCards.every(el => el === cards.progress);

            if (hasOnlyProgress) {
                cards.progress.children[1].innerText = "集計対象データがありません";
                cards.progress.children[1].style.color = "#999";
            }
        }

        run();
    }
});

実装した実際の画面(改良版）

2026-03-30 12 36 35.png

ちょっとカラフルになりましたね。
確実に読み込むようになりました。

さらに集計データが0のカードは自動で非表示に
2026-03-30 12 55 50.png

全く表示するデータが無い場合は
2026-03-30 12 57 46.png

あとは動作の重さをなんとか改善できれば良いのですが･･･

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?