แนวนอน – หน้า 2 – PlusMagi's Blog By Pitt Phunsanit

Byphunsanit

Databae: สลับตารางแนวนอนและตารางแนวตั้ง

จากการที่สามารถสลับตารางแนวนอนและตารางแนวตั้งได้แล้ว จะจำลองการ insert ลง table ใน database
table_Matrix_ reshaping_SQL_script_generator.html

<!DOCTYPE html>
<html lang="th">
<head>
    <meta charset="UTF-8">
    <title>RBAC Matrix Reshaper & SQL Export</title>
    <style>
        body { font-family: 'Chakra Petch', sans-serif; padding: 20px; background-color: #f4f7f6; }
        .container { max-width: 1200px; margin: auto; background: white; padding: 25px; border-radius: 15px; box-shadow: 0 10px 30px rgba(0,0,0,0.1); }
        .panel { background: #2c3e50; color: white; padding: 20px; border-radius: 10px; margin-bottom: 20px; display: flex; gap: 20px; flex-wrap: wrap; align-items: flex-end; }
        .input-group { display: flex; flex-direction: column; gap: 5px; }
        input { padding: 8px; border-radius: 4px; border: 1px solid #ddd; }
        button { padding: 12px 25px; cursor: pointer; border: none; border-radius: 5px; font-weight: bold; transition: 0.2s; }
        .btn-execute { background: #27ae60; color: white; }
        .btn-sql { background: #f39c12; color: white; }
        button:hover { filter: brightness(1.2); }

        table { border-collapse: collapse; width: 100%; margin-top: 15px; font-size: 11px; }
        th { background: #ecf0f1; padding: 8px; border: 1px solid #bdc3c7; }
        td { border: 1px solid #dee2e6; padding: 6px; text-align: center; background: white; }
        .static-col { background: #f8f9fa !important; font-weight: bold; color: #2980b9; }
        .scroll-box { overflow-x: auto; max-height: 250px; border: 1px solid #ddd; margin-top: 10px; }
        #sqlOutput { width: 100%; height: 180px; margin-top: 15px; font-family: monospace; font-size: 13px; background: #1e1e1e; color: #dcdcdc; padding: 15px; border-radius: 5px; }
    </style>
</head>
<body>

<div class="container">
    <h2>Database Matrix Reshaper & SQL Export</h2>

    <div class="panel">
        <div class="input-group">
            <label>1. Static Columns</label>
            <input type="text" id="staticCols" value="id, program_id" oninput="initSource()">
        </div>
        <div class="input-group">
            <label>2. Source (R x P_Cols)</label>
            <div style="display:flex; gap:5px;">
                <input type="number" id="sRow" value="3" style="width: 50px;" oninput="initSource()"> x
                <input type="number" id="sCol" value="24" style="width: 50px;" oninput="initSource()">
            </div>
        </div>
        <div class="input-group">
            <label>3. Target (R x New_Cols)</label>
            <div style="display:flex; gap:5px;">
                <input type="number" id="tRow" value="72" style="width: 60px;"> x
                <input type="number" id="tCol" value="1" style="width: 60px;">
            </div>
        </div>
        <button class="btn-execute" onclick="runReshape()">Execute Reshape</button>
        <button class="btn-sql" onclick="generateSQL()">Generate SQL Script</button>
    </div>

    <h4>Source Table (Raw Data):</h4>
    <div class="scroll-box"><table id="sourceTable"></table></div>

    <h4>Target Table (Result):</h4>
    <div class="scroll-box"><table id="targetTable"></table></div>

    <h4>SQL Script (INSERT Only):</h4>
    <textarea id="sqlOutput" readonly placeholder="กดปุ่มเพื่อสร้างสคริปต์ SQL..."></textarea>
</div>

<script>
    let sourceData = [];
    let finalResult = [];

    function initSource() {
        const rows = parseInt(document.getElementById('sRow').value) || 0;
        const cols = parseInt(document.getElementById('sCol').value) || 0;
        const staticNames = document.getElementById('staticCols').value.split(',').map(s => s.trim()).filter(s => s);
        const table = document.getElementById('sourceTable');

        sourceData = [];
        let html = "<thead><tr>";
        staticNames.forEach(name => html += `<th class="static-col">${name}</th>`);
        for(let i=1; i<=cols; i++) html += `<th>P_${i}</th>`;
        html += "</tr></thead><tbody>";

        for(let i=0; i<rows; i++) {
            let rowObj = { static: {}, perms: [] };
            html += "<tr>";
            staticNames.forEach(name => {
                const val = `${name}_${i+1}`;
                html += `<td class="static-col">${val}</td>`;
                rowObj.static[name] = val;
            });
            for(let j=1; j<=cols; j++) {
                const pVal = `P_${i+1}_${j}`;
                html += `<td>${pVal}</td>`;
                rowObj.perms.push(pVal);
            }
            html += "</tr>";
            sourceData.push(rowObj);
        }
        table.innerHTML = html + "</tbody>";
    }

    function runReshape() {
        const tR = parseInt(document.getElementById('tRow').value) || 1;
        const tC = parseInt(document.getElementById('tCol').value) || 1;
        const staticNames = document.getElementById('staticCols').value.split(',').map(s => s.trim()).filter(s => s);

        finalResult = [];
        let flatItems = [];
        sourceData.forEach(row => {
            row.perms.forEach(p => flatItems.push({ pVal: p, static: row.static }));
        });

        let html = "<thead><tr>";
        staticNames.forEach(name => html += `<th>${name}</th>`);
        for(let i=1; i<=tC; i++) html += `<th>New_P_${i}</th>`;
        html += "</tr></thead><tbody>";

        let k = 0;
        for(let r=0; r<tR; r++) {
            if (k >= flatItems.length) break;
            let rowData = { static: flatItems[k].static, permissions: [] };
            html += "<tr>";
            staticNames.forEach(name => html += `<td class="static-col">${rowData.static[name]}</td>`);
            for(let c=0; c<tC; c++) {
                if (k < flatItems.length) {
                    html += `<td style="background:#d4edda">${flatItems[k].pVal}</td>`;
                    rowData.permissions.push(flatItems[k].pVal);
                    k++;
                }
            }
            html += "</tr>";
            finalResult.push(rowData);
        }
        document.getElementById('targetTable').innerHTML = html + "</tbody>";
    }

    function generateSQL() {
        if (finalResult.length === 0) { runReshape(); }
        const staticNames = document.getElementById('staticCols').value.split(',').map(s => s.trim()).filter(s => s);
        const tC = finalResult[0].permissions.length;

        let sql = "-- SQL Script Export\nINSERT INTO [Target_Table] (" + staticNames.join(", ");
        for(let i=1; i<=tC; i++) sql += `, New_Perm_${i}`;
        sql += ") VALUES \n";

        const values = finalResult.map(row => {
            let rowValues = staticNames.map(name => `'${row.static[name]}'`);
            row.permissions.forEach(p => rowValues.push(`'${p}'`));
            return `(${rowValues.join(", ")})`;
        });

        sql += values.join(",\n") + ";";
        document.getElementById('sqlOutput').value = sql;
    }

    window.onload = initSource;
</script>
</body>
</html>

จะเห็นได้ว่าสามารถทำให้ insert data ลงใน table ได้จริง ๆ
แต่ไม่ควรทำแบบนี้เพราะ ไม่ควรมาสร้าง SQL ที่เกี่ยวกับการ insert, update, delete ในฝั่ง client โดยเฉพาะ query เต็ม ๆ ที่สามารถ run ได้จริง ๆ ใช้เพื่อการ DEMO เท่านั้น

อ่านเพิ่มเติม

Tag Archive แนวนอน

Databae: สลับตารางแนวนอนและตารางแนวตั้ง

Recent Posts