110 lines
4.7 KiB
JavaScript
110 lines
4.7 KiB
JavaScript
const mysql = require('mysql2/promise');
|
|
|
|
async function recalculateCustomerAnalytics() {
|
|
const connection = await mysql.createConnection({
|
|
host: 'localhost',
|
|
user: 'analytics_user',
|
|
password: process.env.DB_PASSWORD,
|
|
database: 'tilbudgivern'
|
|
});
|
|
|
|
try {
|
|
console.log('Genberegner kunde analytics med nye profit margins...');
|
|
|
|
// Drop og genskab customer analytics tabel (hvis muligt)
|
|
try {
|
|
await connection.execute('DROP TABLE IF EXISTS customer_analytics_new');
|
|
await connection.execute(`
|
|
CREATE TABLE customer_analytics_new (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT NOT NULL,
|
|
customer_name VARCHAR(255),
|
|
total_projects INT DEFAULT 0,
|
|
total_revenue DECIMAL(15,2) DEFAULT 0.00,
|
|
avg_project_value DECIMAL(15,2) DEFAULT 0.00,
|
|
total_profit DECIMAL(15,2) DEFAULT 0.00,
|
|
avg_profit_margin DECIMAL(5,2) DEFAULT 0.00,
|
|
INDEX idx_customer_id (customer_id),
|
|
INDEX idx_total_revenue (total_revenue),
|
|
INDEX idx_avg_profit_margin (avg_profit_margin),
|
|
UNIQUE KEY unique_customer (customer_id)
|
|
)
|
|
`);
|
|
|
|
// Indsæt nye data
|
|
await connection.execute(`
|
|
INSERT INTO customer_analytics_new (
|
|
customer_id, customer_name, total_projects, total_revenue,
|
|
avg_project_value, total_profit, avg_profit_margin
|
|
)
|
|
SELECT
|
|
customer_id,
|
|
customer_name,
|
|
COUNT(*) as total_projects,
|
|
SUM(total_quote_value) as total_revenue,
|
|
AVG(total_quote_value) as avg_project_value,
|
|
SUM(profit_amount) as total_profit,
|
|
AVG(profit_margin) as avg_profit_margin
|
|
FROM project_analytics
|
|
WHERE customer_id IS NOT NULL
|
|
GROUP BY customer_id, customer_name
|
|
ORDER BY total_revenue DESC
|
|
`);
|
|
|
|
// Erstat gamle tabel
|
|
await connection.execute('DROP TABLE IF EXISTS customer_analytics_old');
|
|
await connection.execute('RENAME TABLE customer_analytics TO customer_analytics_old');
|
|
await connection.execute('RENAME TABLE customer_analytics_new TO customer_analytics');
|
|
await connection.execute('DROP TABLE IF EXISTS customer_analytics_old');
|
|
|
|
} catch (error) {
|
|
console.log('Kunne ikke droppe tabel, opdaterer eksisterende data i stedet...');
|
|
|
|
// Alternative: Update existing data
|
|
await connection.execute(`
|
|
UPDATE customer_analytics ca
|
|
JOIN (
|
|
SELECT
|
|
customer_id,
|
|
COUNT(*) as total_projects,
|
|
SUM(total_quote_value) as total_revenue,
|
|
AVG(total_quote_value) as avg_project_value,
|
|
SUM(profit_amount) as total_profit,
|
|
AVG(profit_margin) as avg_profit_margin
|
|
FROM project_analytics
|
|
WHERE customer_id IS NOT NULL
|
|
GROUP BY customer_id
|
|
) pa ON ca.customer_id = pa.customer_id
|
|
SET
|
|
ca.total_projects = pa.total_projects,
|
|
ca.total_revenue = pa.total_revenue,
|
|
ca.avg_project_value = pa.avg_project_value,
|
|
ca.total_profit = pa.total_profit,
|
|
ca.avg_profit_margin = pa.avg_profit_margin
|
|
`);
|
|
}
|
|
|
|
// Tjek resultatet
|
|
const [results] = await connection.execute(`
|
|
SELECT
|
|
MIN(avg_profit_margin) as min_margin,
|
|
MAX(avg_profit_margin) as max_margin,
|
|
AVG(avg_profit_margin) as overall_avg,
|
|
COUNT(*) as total_customers
|
|
FROM customer_analytics
|
|
`);
|
|
|
|
console.log('Kunde analytics opdateret:');
|
|
console.log(`- Antal kunder: ${results[0].total_customers}`);
|
|
console.log(`- Min profit margin: ${results[0].min_margin}%`);
|
|
console.log(`- Max profit margin: ${results[0].max_margin}%`);
|
|
console.log(`- Gennemsnit profit margin: ${results[0].overall_avg}%`);
|
|
|
|
} catch (error) {
|
|
console.error('Fejl ved genberegning af kunde analytics:', error);
|
|
} finally {
|
|
await connection.end();
|
|
}
|
|
}
|
|
|
|
recalculateCustomerAnalytics(); |