564 lines
25 KiB
PHP
564 lines
25 KiB
PHP
<?php include('include/headscript.php');
|
|
|
|
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
|
|
die("Invalid template ID");
|
|
}
|
|
|
|
$id = intval($_GET['id']);
|
|
$db = DBHandlerSelect::getInstance();
|
|
$pdo = $db->getConnection();
|
|
$stmt = $pdo->prepare("SELECT name, header_row, start_column, target_table, sample_xlsx FROM excel_templates WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
$template = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
|
|
if (!$template) {
|
|
die("Template not found");
|
|
}
|
|
?>
|
|
<!doctype html>
|
|
<html lang="en">
|
|
|
|
<head>
|
|
<!-- Required meta tags -->
|
|
<meta charset="utf-8">
|
|
<meta name="viewport" content="width=device-width, initial-scale=1">
|
|
<!--favicon-->
|
|
<link rel="icon" href="assets/images/favicon-32x32.png" type="image/png" />
|
|
<?php include('cssinclude.php'); ?>
|
|
<title>Mapping XLS Template <?= htmlspecialchars($titlewebsite, ENT_QUOTES, 'UTF-8'); ?></title>
|
|
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
|
|
|
|
</head>
|
|
|
|
<body>
|
|
<!--wrapper-->
|
|
<div class="wrapper">
|
|
<!--sidebar wrapper -->
|
|
<?php include('include/navbar.php'); ?>
|
|
<!--end sidebar wrapper -->
|
|
<!--start header -->
|
|
<?php include('include/topbar.php'); ?>
|
|
<!--end header -->
|
|
<!--start page wrapper -->
|
|
<div class="page-wrapper">
|
|
<div class="page-content">
|
|
|
|
<?php include('top_stat_widget.php'); ?>
|
|
|
|
|
|
<div class="card radius-10">
|
|
<div class="card-header">
|
|
<div class="d-flex align-items-center">
|
|
<div>
|
|
<h6 class="mb-0">Associate Columns - Template: <span id="templateName"><?php echo htmlspecialchars($template['name']); ?></h6>
|
|
<p>Header Row: <span id="headerRow"><?php echo $template['header_row']; ?></span> | Start Column: <span id="startColumn"><?php echo htmlspecialchars($template['start_column']); ?></span></p>
|
|
</div>
|
|
|
|
</div>
|
|
</div>
|
|
<div class="card-body">
|
|
<!-- Upload Section -->
|
|
<div class="mb-4">
|
|
<label class="form-label">Upload XLS Example:</label>
|
|
<input type="file" id="xlsUpload" class="form-control">
|
|
<small id="uploadStatus" class="text-muted">
|
|
<?php if (!empty($template['sample_xlsx'])): ?>
|
|
✅ Current file: <a href="xlstemplates/<?php echo htmlspecialchars($template['sample_xlsx']); ?>" target="_blank">
|
|
<?php echo htmlspecialchars($template['sample_xlsx']); ?>
|
|
</a>
|
|
<?php else: ?>
|
|
No file uploaded yet.
|
|
<?php endif; ?>
|
|
</small>
|
|
</div>
|
|
|
|
|
|
|
|
|
|
<!-- Association Section -->
|
|
<div class="row">
|
|
<div class="col-md-5">
|
|
<h5>XLS Column</h5>
|
|
<ul id="xlsColumns" class="list-group border p-3" style="height: 300px; overflow-y: auto;"></ul>
|
|
</div>
|
|
<div class="col-md-2 text-center d-flex align-items-center justify-content-center">
|
|
<button class="btn btn-dark" id="addAssociation">➝ Add</button>
|
|
</div>
|
|
<div class="col-md-5">
|
|
<h5>Table Column</h5>
|
|
<ul id="tableColumns" class="list-group border p-3" style="height: 300px; overflow-y: auto;"></ul>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Associations List -->
|
|
<div class="mt-4">
|
|
<h5>Current Associations</h5>
|
|
<ul id="associationsList" class="list-group border p-3"></ul>
|
|
</div>
|
|
|
|
<!-- Default Values (Fixed fields) -->
|
|
<div class="mt-4">
|
|
<h5>Default Values (Fixed fields)</h5>
|
|
|
|
<div class="row g-3">
|
|
<div class="col-md-6">
|
|
<label class="form-label">MoltiplicatorePrezzo (default)</label>
|
|
<select id="defaultMoltiplicatorePrezzo" class="form-select">
|
|
<option value="">Loading...</option>
|
|
</select>
|
|
<small class="text-muted">Loaded from API: get_moltiplicatoreprezzo.php</small>
|
|
</div>
|
|
|
|
<div class="col-md-6">
|
|
<label class="form-label">ConsegnaRichiesta (default)</label>
|
|
<input type="date" id="defaultConsegnaRichiesta" class="form-control">
|
|
</div>
|
|
</div>
|
|
|
|
<div class="mt-3">
|
|
<button class="btn btn-success" id="saveDefaultsBtn">💾 Save defaults</button>
|
|
<span id="defaultsStatus" class="ms-2 text-muted"></span>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Save Button -->
|
|
<div class="mt-4 text-end">
|
|
<a href="templates_dashboard.php" class="btn btn-primary">⬅ Back to Template Dashboard</a>
|
|
</div>
|
|
|
|
</div>
|
|
</div>
|
|
|
|
</div>
|
|
</div>
|
|
<!--end page wrapper -->
|
|
<!--start overlay-->
|
|
<div class="overlay toggle-icon"></div>
|
|
<!--end overlay-->
|
|
<!--Start Back To Top Button-->
|
|
<a href="javaScript:;" class="back-to-top"><i class='bx bxs-up-arrow-alt'></i></a>
|
|
<!--End Back To Top Button-->
|
|
<?php include('include/footer.php'); ?>
|
|
</div>
|
|
<!--end wrapper-->
|
|
|
|
|
|
<!-- search modal -->
|
|
<?php //include('include/searchmodal.php');
|
|
?>
|
|
<!-- end search modal -->
|
|
|
|
|
|
<!--start switcher-->
|
|
<?php //include('include/themeswitcher.php');
|
|
?>
|
|
<!--end switcher-->
|
|
<?php include('jsinclude.php'); ?>
|
|
<script>
|
|
document.getElementById('xlsUpload').addEventListener('change', function(event) {
|
|
let file = event.target.files[0];
|
|
if (!file) {
|
|
console.error("❌ No file selected");
|
|
return;
|
|
}
|
|
|
|
console.log("📂 File selected:", file.name);
|
|
|
|
// 🔹 UPLOAD DEL FILE PRIMA DI PROCESSARE I DATI
|
|
let formData = new FormData();
|
|
formData.append("xls_file", file);
|
|
formData.append("template_id", <?php echo $id; ?>);
|
|
|
|
let statusText = document.getElementById('uploadStatus');
|
|
statusText.innerText = "Uploading...";
|
|
|
|
fetch('upload_xls_example.php', {
|
|
method: 'POST',
|
|
body: formData
|
|
})
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
if (!data.success) {
|
|
console.error("❌ Error uploading file:", data.message);
|
|
statusText.innerText = "❌ Upload failed: " + data.message;
|
|
return;
|
|
}
|
|
|
|
console.log("✅ File uploaded successfully:", data.filepath);
|
|
statusText.innerHTML = `✅ File uploaded: <a href="xlstemplates/${data.filename}" target="_blank">${data.filename}</a>`;
|
|
|
|
// 🔹 UNA VOLTA CARICATO, ESTRAIAMO LE COLONNE
|
|
processXLSX(file);
|
|
|
|
})
|
|
.catch(error => {
|
|
console.error("❌ Fetch error:", error);
|
|
statusText.innerText = "❌ Upload failed. Check console.";
|
|
});
|
|
});
|
|
|
|
// 🔹 FUNZIONE PER PROCESSARE IL FILE ESTRARRE LE COLONNE
|
|
function processXLSX(file) {
|
|
let reader = new FileReader();
|
|
reader.onload = function(e) {
|
|
let data = new Uint8Array(e.target.result);
|
|
let workbook = XLSX.read(data, {
|
|
type: 'array'
|
|
});
|
|
let sheet = workbook.Sheets[workbook.SheetNames[0]];
|
|
|
|
console.log("📄 Sheet found:", workbook.SheetNames[0]);
|
|
|
|
// Recupera "Header Row" e "Start Column" dal template
|
|
let rowIndex = parseInt(document.getElementById('headerRow').textContent) || 1;
|
|
let startColumn = parseInt(document.getElementById('startColumn').textContent) || 1;
|
|
|
|
console.log("📌 Using Header Row:", rowIndex, "Start Column:", startColumn);
|
|
|
|
// Legge il foglio SENZA eliminare righe vuote
|
|
let sheetData = XLSX.utils.sheet_to_json(sheet, {
|
|
header: 1,
|
|
defval: "",
|
|
raw: false,
|
|
range: 0
|
|
});
|
|
|
|
console.log("📊 Sheet Data:", sheetData);
|
|
|
|
// Verifica che la riga richiesta esista
|
|
if (!sheetData[rowIndex - 1]) {
|
|
console.warn("⚠️ No headers found in the specified row:", rowIndex);
|
|
document.getElementById('xlsColumns').innerHTML = "<li class='list-group-item text-danger'>No headers found</li>";
|
|
return;
|
|
}
|
|
|
|
// Prende la riga esatta specificata nel template
|
|
let headers = sheetData[rowIndex - 1] || [];
|
|
|
|
console.log("🔎 Raw Headers:", headers);
|
|
|
|
// Se la riga specificata è vuota, avvisa l'utente
|
|
if (!headers.length || headers.every(h => h.trim() === "")) {
|
|
console.warn("⚠️ Header row is empty at row:", rowIndex);
|
|
document.getElementById('xlsColumns').innerHTML = "<li class='list-group-item text-warning'>No headers found in the specified row</li>";
|
|
return;
|
|
}
|
|
|
|
// Rimuove le colonne prima di "Start Column"
|
|
let adjustedHeaders = headers.slice(startColumn - 1).filter(header => header !== undefined && header.trim() !== "");
|
|
|
|
console.log("✅ Extracted Headers:", adjustedHeaders);
|
|
|
|
// Popola la lista delle colonne XLSX
|
|
let xlsColumns = document.getElementById('xlsColumns');
|
|
xlsColumns.innerHTML = '';
|
|
|
|
adjustedHeaders.forEach(header => {
|
|
let li = document.createElement('li');
|
|
li.className = 'list-group-item';
|
|
li.textContent = header;
|
|
xlsColumns.appendChild(li);
|
|
});
|
|
|
|
};
|
|
|
|
reader.readAsArrayBuffer(file);
|
|
}
|
|
|
|
|
|
document.addEventListener("DOMContentLoaded", function() {
|
|
let selectedXLSColumn = null;
|
|
let selectedTableColumn = null;
|
|
let templateId = <?php echo $id; ?>;
|
|
|
|
/** =======================
|
|
* CARICAMENTO COLONNE MYSQL DAL DATABASE
|
|
* ======================= */
|
|
/* fetch('load_table_columns.php?table=<?php echo urlencode($template['target_table']); ?>')
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
if (!data.success) {
|
|
console.error("Error:", data.message);
|
|
return;
|
|
}
|
|
|
|
let tableColumns = document.getElementById('tableColumns');
|
|
tableColumns.innerHTML = '';
|
|
|
|
if (Array.isArray(data.columns)) {
|
|
data.columns.forEach(column => {
|
|
let li = document.createElement('li');
|
|
li.className = 'list-group-item';
|
|
li.textContent = column;
|
|
tableColumns.appendChild(li);
|
|
});
|
|
} else {
|
|
console.error("Unexpected data format:", data);
|
|
}
|
|
})
|
|
.catch(error => console.error('Error loading table columns:', error));
|
|
*/
|
|
/** =======================
|
|
* CARICAMENTO MAPPATURE GIÀ ESISTENTI
|
|
* ======================= */
|
|
function loadMappings() {
|
|
fetch('load_existing_mappings.php?template_id=' + templateId)
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
if (!data.success) {
|
|
console.error("❌ Error loading mappings:", data.message);
|
|
return;
|
|
}
|
|
|
|
let associationsList = document.getElementById('associationsList');
|
|
let xlsColumnsList = document.getElementById('xlsColumns');
|
|
let tableColumnsList = document.getElementById('tableColumns');
|
|
|
|
// 🔥 FIX: Pulisce sempre la lista prima di aggiornare
|
|
associationsList.innerHTML = '';
|
|
xlsColumnsList.innerHTML = '';
|
|
tableColumnsList.innerHTML = '';
|
|
|
|
// Carica le associazioni esistenti
|
|
data.mappings.forEach(mapping => {
|
|
let li = document.createElement('li');
|
|
li.className = 'list-group-item d-flex justify-content-between align-items-center';
|
|
li.innerHTML = `
|
|
${mapping.excel_column} ➝ ${mapping.mysql_column}
|
|
<button class="btn btn-danger btn-sm removeAssociation" data-excel="${mapping.excel_column}" data-mysql="${mapping.mysql_column}">X</button>
|
|
`;
|
|
associationsList.appendChild(li);
|
|
});
|
|
|
|
// 🔥 FIX: Ora carica SOLO le colonne MySQL rimaste disponibili!
|
|
if (Array.isArray(data.remaining_mysql_columns)) {
|
|
data.remaining_mysql_columns.forEach(column => {
|
|
let li = document.createElement('li');
|
|
li.className = 'list-group-item';
|
|
li.textContent = column;
|
|
tableColumnsList.appendChild(li);
|
|
});
|
|
}
|
|
|
|
// Carica le colonne XLSX rimanenti
|
|
if (Array.isArray(data.remaining_xls_columns)) {
|
|
data.remaining_xls_columns.forEach(header => {
|
|
let li = document.createElement('li');
|
|
li.className = 'list-group-item';
|
|
li.textContent = header;
|
|
xlsColumnsList.appendChild(li);
|
|
});
|
|
}
|
|
|
|
console.log("✅ Loaded existing mappings and remaining columns");
|
|
})
|
|
.catch(error => console.error("❌ Error fetching mappings:", error));
|
|
}
|
|
|
|
// 🔥 Chiama il caricamento iniziale
|
|
loadMappings();
|
|
|
|
/** =======================
|
|
* SELEZIONE COLONNE XLS & MYSQL
|
|
* ======================= */
|
|
document.getElementById('xlsColumns').addEventListener('click', function(event) {
|
|
if (event.target.tagName === 'LI') {
|
|
document.querySelectorAll('#xlsColumns li').forEach(el => el.classList.remove('active'));
|
|
event.target.classList.add('active');
|
|
selectedXLSColumn = event.target.textContent;
|
|
console.log("📌 Selected XLS Column:", selectedXLSColumn);
|
|
}
|
|
});
|
|
|
|
document.getElementById('tableColumns').addEventListener('click', function(event) {
|
|
if (event.target.tagName === 'LI') {
|
|
document.querySelectorAll('#tableColumns li').forEach(el => el.classList.remove('active'));
|
|
event.target.classList.add('active');
|
|
selectedTableColumn = event.target.textContent;
|
|
console.log("📌 Selected MySQL Column:", selectedTableColumn);
|
|
}
|
|
});
|
|
|
|
/** =======================
|
|
* AGGIUNTA ASSOCIAZIONE
|
|
* ======================= */
|
|
document.getElementById('addAssociation').addEventListener('click', function() {
|
|
if (!selectedXLSColumn || !selectedTableColumn) {
|
|
alert("Please select both an XLS and MySQL column.");
|
|
return;
|
|
}
|
|
|
|
// Salva l'associazione nel database
|
|
saveAssociation(selectedXLSColumn, selectedTableColumn);
|
|
|
|
// Reset selezioni
|
|
selectedXLSColumn = null;
|
|
selectedTableColumn = null;
|
|
});
|
|
|
|
function saveAssociation(excelColumn, mysqlColumn) {
|
|
let allHeaders = [...document.querySelectorAll('#xlsColumns li')].map(el => el.textContent).join(",");
|
|
|
|
fetch('save_column_mapping.php', {
|
|
method: 'POST',
|
|
headers: {
|
|
'Content-Type': 'application/json'
|
|
},
|
|
body: JSON.stringify({
|
|
template_id: templateId,
|
|
tablename: "<?php echo $template['target_table']; ?>",
|
|
excel_column: excelColumn,
|
|
mysql_column: mysqlColumn,
|
|
data_type: "VARCHAR",
|
|
is_required: 0,
|
|
default_value: "",
|
|
headerexcel: allHeaders
|
|
})
|
|
})
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
if (!data.success) {
|
|
console.error("❌ Error saving mapping:", data.message);
|
|
alert("Error saving mapping: " + data.message);
|
|
} else {
|
|
console.log("✅ Association saved:", data);
|
|
loadMappings(); // Ricarica le colonne dopo il salvataggio
|
|
}
|
|
})
|
|
.catch(error => console.error("❌ Fetch error:", error));
|
|
}
|
|
|
|
/** =======================
|
|
* RIMOZIONE ASSOCIAZIONE
|
|
* ======================= */
|
|
document.getElementById('associationsList').addEventListener('click', function(event) {
|
|
if (event.target.classList.contains('removeAssociation')) {
|
|
let excelColumn = event.target.getAttribute("data-excel");
|
|
let mysqlColumn = event.target.getAttribute("data-mysql");
|
|
|
|
removeAssociation(excelColumn, mysqlColumn);
|
|
}
|
|
});
|
|
|
|
function removeAssociation(excelColumn, mysqlColumn) {
|
|
fetch('remove_column_mapping.php', {
|
|
method: 'POST',
|
|
headers: {
|
|
'Content-Type': 'application/json'
|
|
},
|
|
body: JSON.stringify({
|
|
template_id: templateId,
|
|
excel_column: excelColumn,
|
|
mysql_column: mysqlColumn,
|
|
tablename: "<?php echo $template['target_table']; ?>"
|
|
})
|
|
})
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
if (!data.success) {
|
|
console.error("❌ Error removing mapping:", data.message);
|
|
alert("Error removing mapping: " + data.message);
|
|
} else {
|
|
console.log("🗑️ Association removed:", data);
|
|
loadMappings(); // Ricarica la lista dopo la rimozione
|
|
}
|
|
})
|
|
.catch(error => console.error("❌ Fetch error:", error));
|
|
}
|
|
|
|
/** =======================
|
|
* DEFAULTS (Fixed fields)
|
|
* - stored in localStorage for now
|
|
* ======================= */
|
|
|
|
const defaultsKey = "tpl_defaults_" + templateId;
|
|
|
|
function loadDefaultsFromLocal() {
|
|
try {
|
|
const raw = localStorage.getItem(defaultsKey);
|
|
if (!raw) return;
|
|
const saved = JSON.parse(raw);
|
|
|
|
if (saved && typeof saved === "object") {
|
|
if (saved.moltiplicatore_prezzo_id !== undefined) {
|
|
document.getElementById('defaultMoltiplicatorePrezzo').value = saved.moltiplicatore_prezzo_id || "";
|
|
}
|
|
if (saved.consegna_richiesta !== undefined) {
|
|
document.getElementById('defaultConsegnaRichiesta').value = saved.consegna_richiesta || "";
|
|
}
|
|
}
|
|
} catch (e) {
|
|
console.warn("⚠️ Cannot parse saved defaults:", e);
|
|
}
|
|
}
|
|
|
|
function saveDefaultsToLocal() {
|
|
const moltiplicatoreId = document.getElementById('defaultMoltiplicatorePrezzo').value || "";
|
|
const consegna = document.getElementById('defaultConsegnaRichiesta').value || "";
|
|
|
|
const payload = {
|
|
moltiplicatore_prezzo_id: moltiplicatoreId,
|
|
consegna_richiesta: consegna
|
|
};
|
|
|
|
localStorage.setItem(defaultsKey, JSON.stringify(payload));
|
|
|
|
const status = document.getElementById('defaultsStatus');
|
|
status.textContent = "✅ Defaults saved for this template (local)";
|
|
setTimeout(() => status.textContent = "", 2500);
|
|
}
|
|
|
|
function loadMoltiplicatoriPrezzo() {
|
|
const select = document.getElementById('defaultMoltiplicatorePrezzo');
|
|
select.innerHTML = `<option value="">-- Select --</option>`;
|
|
|
|
fetch('get_moltiplicatoreprezzo.php')
|
|
.then(r => r.json())
|
|
.then(data => {
|
|
// OData tipico: { value: [...] }
|
|
const rows = Array.isArray(data?.value) ? data.value : (Array.isArray(data) ? data : []);
|
|
if (!rows.length) {
|
|
select.innerHTML = `<option value="">(No data)</option>`;
|
|
return;
|
|
}
|
|
|
|
rows.forEach(item => {
|
|
// ⚠️ Qui i nomi campi dipendono dal JSON reale:
|
|
// - id: item.Id / item.ID / item.idMoltiplicatorePrezzo
|
|
// - label: item.Descrizione / item.Nome / item.Codice
|
|
const id = item.IdMoltiplicatorePrezzo;
|
|
const label = `${item.Descrizione} (x${item.Fattore})`;
|
|
|
|
|
|
if (id === undefined || id === null) return;
|
|
|
|
const opt = document.createElement('option');
|
|
opt.value = String(id);
|
|
opt.textContent = String(label);
|
|
select.appendChild(opt);
|
|
});
|
|
|
|
// dopo aver popolato, prova a rimettere il valore salvato
|
|
loadDefaultsFromLocal();
|
|
})
|
|
.catch(err => {
|
|
console.error("❌ Error loading MoltiplicatorePrezzo:", err);
|
|
select.innerHTML = `<option value="">(Load error)</option>`;
|
|
});
|
|
}
|
|
|
|
// Events
|
|
document.getElementById('saveDefaultsBtn').addEventListener('click', function() {
|
|
saveDefaultsToLocal();
|
|
});
|
|
|
|
// Init
|
|
loadMoltiplicatoriPrezzo();
|
|
loadDefaultsFromLocal();
|
|
|
|
});
|
|
</script>
|
|
</body>
|
|
|
|
</html>
|