จากการที่สามารถสลับตารางแนวนอนและตารางแนวตั้งได้แล้ว จะจำลองการ 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 เท่านั้น
อ่านเพิ่มเติม