618 lines
26 KiB
JavaScript
618 lines
26 KiB
JavaScript
const mysql = require('mysql2/promise');
|
|
|
|
// Database connection configuration
|
|
const dbConfig = {
|
|
host: 'localhost',
|
|
user: 'analytics_user',
|
|
password: process.env.DB_PASSWORD,
|
|
database: 'tilbudgivern'
|
|
};
|
|
|
|
// Secondary connection for ordrestyring data
|
|
const ordrestyringConfig = {
|
|
host: 'localhost',
|
|
user: 'analytics_user',
|
|
password: process.env.DB_PASSWORD,
|
|
database: 'ordrestyring_local'
|
|
};
|
|
|
|
class AnalyticsDataPopulator {
|
|
constructor() {
|
|
this.connection = null;
|
|
}
|
|
|
|
async connect() {
|
|
try {
|
|
this.connection = await mysql.createConnection(dbConfig);
|
|
console.log('✓ Connected to tilbudgivern database');
|
|
} catch (error) {
|
|
console.error('Database connection failed:', error.message);
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
async disconnect() {
|
|
if (this.connection) {
|
|
await this.connection.end();
|
|
console.log('✓ Database connection closed');
|
|
}
|
|
}
|
|
|
|
// ======================================
|
|
// 1. POPULATE PROJECT ANALYTICS
|
|
// ======================================
|
|
async populateProjectAnalytics() {
|
|
console.log('\n🔄 Populating Project Analytics...');
|
|
|
|
const query = `
|
|
INSERT INTO project_analytics (
|
|
project_id, project_name, customer_id, customer_name,
|
|
employee_id, employee_name, total_hours, total_material_cost,
|
|
total_labor_cost, total_quote_value, actual_cost, profit_amount,
|
|
profit_margin, quote_accuracy_percentage, project_start_date,
|
|
project_end_date, project_duration_days, quote_created_date,
|
|
is_active
|
|
)
|
|
SELECT DISTINCT
|
|
s.id as project_id,
|
|
s.navn as project_name,
|
|
s.kunde_id as customer_id,
|
|
k.navn as customer_name,
|
|
s.medarbejder_id as employee_id,
|
|
m.navn as employee_name,
|
|
|
|
-- Calculate total hours from all timeposts
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0))
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0) as total_hours,
|
|
|
|
-- Calculate total material cost
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) as total_material_cost,
|
|
|
|
-- Calculate labor cost (hours * hourly rate)
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0) as total_labor_cost,
|
|
|
|
-- Total quote value
|
|
COALESCE(s.samlet_pris, 0) as total_quote_value,
|
|
|
|
-- Actual cost (materials + labor)
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) + COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0) as actual_cost,
|
|
|
|
-- Profit amount (quote - actual cost)
|
|
GREATEST(0, COALESCE(s.samlet_pris, 0) - (
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) + COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0)
|
|
)) as profit_amount,
|
|
|
|
-- Profit margin percentage
|
|
CASE
|
|
WHEN COALESCE(s.samlet_pris, 0) > 0 THEN
|
|
GREATEST(0, (COALESCE(s.samlet_pris, 0) - (
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) + COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0)
|
|
)) / COALESCE(s.samlet_pris, 0)) * 100
|
|
ELSE 0
|
|
END as profit_margin,
|
|
|
|
-- Quote accuracy (simplified - actual vs estimated)
|
|
CASE
|
|
WHEN COALESCE(s.samlet_pris, 0) > 0 AND (
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) + COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0)
|
|
) > 0 THEN
|
|
LEAST(100, (COALESCE(s.samlet_pris, 0) / (
|
|
COALESCE((
|
|
SELECT SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0))
|
|
FROM sag_items si
|
|
WHERE si.sag_id = s.id
|
|
), 0) + COALESCE((
|
|
SELECT SUM(COALESCE(tp.timer, 0)) * 650
|
|
FROM timeposts tp
|
|
WHERE tp.sag_id = s.id
|
|
), 0)
|
|
)) * 100)
|
|
ELSE 100
|
|
END as quote_accuracy_percentage,
|
|
|
|
s.oprettet as project_start_date,
|
|
s.afsluttet as project_end_date,
|
|
CASE
|
|
WHEN s.afsluttet IS NOT NULL THEN
|
|
DATEDIFF(s.afsluttet, s.oprettet)
|
|
ELSE
|
|
DATEDIFF(CURRENT_DATE, s.oprettet)
|
|
END as project_duration_days,
|
|
s.oprettet as quote_created_date,
|
|
CASE WHEN s.status = 'aktiv' THEN 1 ELSE 0 END as is_active
|
|
|
|
FROM sager s
|
|
LEFT JOIN kunder k ON s.kunde_id = k.id
|
|
LEFT JOIN medarbejdere m ON s.medarbejder_id = m.id
|
|
WHERE s.id IS NOT NULL
|
|
ON DUPLICATE KEY UPDATE
|
|
total_hours = VALUES(total_hours),
|
|
total_material_cost = VALUES(total_material_cost),
|
|
total_labor_cost = VALUES(total_labor_cost),
|
|
profit_amount = VALUES(profit_amount),
|
|
profit_margin = VALUES(profit_margin),
|
|
quote_accuracy_percentage = VALUES(quote_accuracy_percentage),
|
|
last_calculated = CURRENT_TIMESTAMP
|
|
`;
|
|
|
|
try {
|
|
const [result] = await this.connection.execute(query);
|
|
console.log(`✓ Project Analytics: ${result.affectedRows} records processed`);
|
|
return result.affectedRows;
|
|
} catch (error) {
|
|
console.error('Error populating project analytics:', error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
// ======================================
|
|
// 2. POPULATE EMPLOYEE ANALYTICS
|
|
// ======================================
|
|
async populateEmployeeAnalytics() {
|
|
console.log('\n🔄 Populating Employee Analytics...');
|
|
|
|
const query = `
|
|
INSERT INTO employee_performance_analytics (
|
|
employee_id, employee_name, period_month, period_year,
|
|
total_projects, total_revenue, total_profit, avg_profit_margin,
|
|
avg_quote_accuracy, projects_over_budget, projects_under_budget,
|
|
avg_project_duration, total_hours_worked, revenue_per_hour
|
|
)
|
|
SELECT
|
|
pa.employee_id,
|
|
pa.employee_name,
|
|
MONTH(pa.project_start_date) as period_month,
|
|
YEAR(pa.project_start_date) as period_year,
|
|
COUNT(*) as total_projects,
|
|
SUM(pa.total_quote_value) as total_revenue,
|
|
SUM(pa.profit_amount) as total_profit,
|
|
AVG(pa.profit_margin) as avg_profit_margin,
|
|
AVG(pa.quote_accuracy_percentage) as avg_quote_accuracy,
|
|
SUM(CASE WHEN pa.quote_accuracy_percentage < 100 THEN 1 ELSE 0 END) as projects_over_budget,
|
|
SUM(CASE WHEN pa.quote_accuracy_percentage >= 100 THEN 1 ELSE 0 END) as projects_under_budget,
|
|
AVG(pa.project_duration_days) as avg_project_duration,
|
|
SUM(pa.total_hours) as total_hours_worked,
|
|
CASE
|
|
WHEN SUM(pa.total_hours) > 0 THEN SUM(pa.total_quote_value) / SUM(pa.total_hours)
|
|
ELSE 0
|
|
END as revenue_per_hour
|
|
FROM project_analytics pa
|
|
WHERE pa.employee_id IS NOT NULL
|
|
AND pa.project_start_date >= DATE_SUB(CURRENT_DATE, INTERVAL 24 MONTH)
|
|
GROUP BY pa.employee_id, pa.employee_name,
|
|
YEAR(pa.project_start_date), MONTH(pa.project_start_date)
|
|
ON DUPLICATE KEY UPDATE
|
|
total_projects = VALUES(total_projects),
|
|
total_revenue = VALUES(total_revenue),
|
|
total_profit = VALUES(total_profit),
|
|
avg_profit_margin = VALUES(avg_profit_margin),
|
|
avg_quote_accuracy = VALUES(avg_quote_accuracy),
|
|
revenue_per_hour = VALUES(revenue_per_hour),
|
|
last_calculated = CURRENT_TIMESTAMP
|
|
`;
|
|
|
|
try {
|
|
const [result] = await this.connection.execute(query);
|
|
console.log(`✓ Employee Analytics: ${result.affectedRows} records processed`);
|
|
|
|
// Update rankings
|
|
await this.updateEmployeeRankings();
|
|
return result.affectedRows;
|
|
} catch (error) {
|
|
console.error('Error populating employee analytics:', error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
async updateEmployeeRankings() {
|
|
// Update profit rankings
|
|
await this.connection.execute(`
|
|
SET @rank := 0;
|
|
UPDATE employee_performance_analytics e1
|
|
JOIN (
|
|
SELECT employee_id, period_year, period_month, @rank := @rank + 1 as new_rank
|
|
FROM employee_performance_analytics
|
|
WHERE period_year = YEAR(CURRENT_DATE) AND period_month = MONTH(CURRENT_DATE)
|
|
ORDER BY avg_profit_margin DESC
|
|
) e2 ON e1.employee_id = e2.employee_id
|
|
AND e1.period_year = e2.period_year
|
|
AND e1.period_month = e2.period_month
|
|
SET e1.profit_rank = e2.new_rank
|
|
`);
|
|
|
|
console.log(' ✓ Employee rankings updated');
|
|
}
|
|
|
|
// ======================================
|
|
// 3. POPULATE CUSTOMER ANALYTICS
|
|
// ======================================
|
|
async populateCustomerAnalytics() {
|
|
console.log('\n🔄 Populating Customer Analytics...');
|
|
|
|
const query = `
|
|
INSERT INTO customer_analytics (
|
|
customer_id, customer_name, total_projects, total_revenue,
|
|
avg_project_value, total_profit, avg_profit_margin,
|
|
first_project_date, last_project_date, customer_lifespan_days,
|
|
project_frequency_days, projects_over_budget, avg_quote_accuracy,
|
|
customer_tier, is_high_value, is_frequent
|
|
)
|
|
SELECT
|
|
pa.customer_id,
|
|
pa.customer_name,
|
|
COUNT(*) as total_projects,
|
|
SUM(pa.total_quote_value) as total_revenue,
|
|
AVG(pa.total_quote_value) as avg_project_value,
|
|
SUM(pa.profit_amount) as total_profit,
|
|
AVG(pa.profit_margin) as avg_profit_margin,
|
|
MIN(pa.project_start_date) as first_project_date,
|
|
MAX(pa.project_start_date) as last_project_date,
|
|
DATEDIFF(MAX(pa.project_start_date), MIN(pa.project_start_date)) as customer_lifespan_days,
|
|
CASE
|
|
WHEN COUNT(*) > 1 THEN
|
|
DATEDIFF(MAX(pa.project_start_date), MIN(pa.project_start_date)) / (COUNT(*) - 1)
|
|
ELSE 0
|
|
END as project_frequency_days,
|
|
SUM(CASE WHEN pa.quote_accuracy_percentage < 100 THEN 1 ELSE 0 END) as projects_over_budget,
|
|
AVG(pa.quote_accuracy_percentage) as avg_quote_accuracy,
|
|
CASE
|
|
WHEN SUM(pa.total_quote_value) >= 500000 THEN 'Platinum'
|
|
WHEN SUM(pa.total_quote_value) >= 200000 THEN 'Gold'
|
|
WHEN SUM(pa.total_quote_value) >= 50000 THEN 'Silver'
|
|
ELSE 'Bronze'
|
|
END as customer_tier,
|
|
CASE WHEN SUM(pa.total_quote_value) >= 100000 THEN 1 ELSE 0 END as is_high_value,
|
|
CASE WHEN COUNT(*) >= 5 THEN 1 ELSE 0 END as is_frequent
|
|
FROM project_analytics pa
|
|
WHERE pa.customer_id IS NOT NULL
|
|
GROUP BY pa.customer_id, pa.customer_name
|
|
ON DUPLICATE KEY UPDATE
|
|
total_projects = VALUES(total_projects),
|
|
total_revenue = VALUES(total_revenue),
|
|
avg_project_value = VALUES(avg_project_value),
|
|
total_profit = VALUES(total_profit),
|
|
avg_profit_margin = VALUES(avg_profit_margin),
|
|
customer_tier = VALUES(customer_tier),
|
|
is_high_value = VALUES(is_high_value),
|
|
is_frequent = VALUES(is_frequent),
|
|
last_calculated = CURRENT_TIMESTAMP
|
|
`;
|
|
|
|
try {
|
|
const [result] = await this.connection.execute(query);
|
|
console.log(`✓ Customer Analytics: ${result.affectedRows} records processed`);
|
|
return result.affectedRows;
|
|
} catch (error) {
|
|
console.error('Error populating customer analytics:', error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
// ======================================
|
|
// 4. POPULATE MATERIAL ANALYTICS
|
|
// ======================================
|
|
async populateMaterialAnalytics() {
|
|
console.log('\n🔄 Populating Material Analytics...');
|
|
|
|
const query = `
|
|
INSERT INTO material_analytics (
|
|
material_name, material_category, varenr, total_usage_count,
|
|
total_quantity_used, total_cost, avg_unit_price, min_price,
|
|
max_price, current_price, projects_used_in, last_used_date,
|
|
profit_contribution
|
|
)
|
|
SELECT
|
|
COALESCE(si.materiale_navn, 'Unknown Material') as material_name,
|
|
COALESCE(si.kategori, 'General') as material_category,
|
|
si.varenr,
|
|
COUNT(*) as total_usage_count,
|
|
SUM(COALESCE(si.antal, 0)) as total_quantity_used,
|
|
SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0)) as total_cost,
|
|
AVG(COALESCE(si.pris, 0)) as avg_unit_price,
|
|
MIN(COALESCE(si.pris, 0)) as min_price,
|
|
MAX(COALESCE(si.pris, 0)) as max_price,
|
|
(SELECT COALESCE(si2.pris, 0)
|
|
FROM sag_items si2
|
|
WHERE si2.varenr = si.varenr
|
|
ORDER BY si2.id DESC LIMIT 1) as current_price,
|
|
COUNT(DISTINCT si.sag_id) as projects_used_in,
|
|
MAX(pa.project_start_date) as last_used_date,
|
|
SUM(COALESCE(si.pris, 0) * COALESCE(si.antal, 0)) * 0.3 as profit_contribution
|
|
FROM sag_items si
|
|
JOIN project_analytics pa ON si.sag_id = pa.project_id
|
|
WHERE si.varenr IS NOT NULL AND si.varenr != ''
|
|
GROUP BY si.varenr, si.materiale_navn, si.kategori
|
|
ON DUPLICATE KEY UPDATE
|
|
total_usage_count = VALUES(total_usage_count),
|
|
total_quantity_used = VALUES(total_quantity_used),
|
|
total_cost = VALUES(total_cost),
|
|
avg_unit_price = VALUES(avg_unit_price),
|
|
projects_used_in = VALUES(projects_used_in),
|
|
last_used_date = VALUES(last_used_date),
|
|
profit_contribution = VALUES(profit_contribution),
|
|
last_calculated = CURRENT_TIMESTAMP
|
|
`;
|
|
|
|
try {
|
|
const [result] = await this.connection.execute(query);
|
|
console.log(`✓ Material Analytics: ${result.affectedRows} records processed`);
|
|
|
|
// Update material rankings
|
|
await this.updateMaterialRankings();
|
|
return result.affectedRows;
|
|
} catch (error) {
|
|
console.error('Error populating material analytics:', error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
async updateMaterialRankings() {
|
|
// Update usage rankings
|
|
await this.connection.execute(`
|
|
SET @rank := 0;
|
|
UPDATE material_analytics m1
|
|
JOIN (
|
|
SELECT id, @rank := @rank + 1 as new_rank
|
|
FROM material_analytics
|
|
ORDER BY total_usage_count DESC
|
|
) m2 ON m1.id = m2.id
|
|
SET m1.usage_rank = m2.new_rank
|
|
`);
|
|
|
|
console.log(' ✓ Material rankings updated');
|
|
}
|
|
|
|
// ======================================
|
|
// 5. POPULATE DAILY METRICS
|
|
// ======================================
|
|
async populateDailyMetrics() {
|
|
console.log('\n🔄 Populating Daily Business Metrics...');
|
|
|
|
const query = `
|
|
INSERT INTO daily_business_metrics (
|
|
metric_date, new_projects, total_daily_revenue, total_daily_profit,
|
|
avg_daily_profit_margin, active_projects, avg_quote_accuracy
|
|
)
|
|
SELECT
|
|
DATE(pa.project_start_date) as metric_date,
|
|
COUNT(*) as new_projects,
|
|
SUM(pa.total_quote_value) as total_daily_revenue,
|
|
SUM(pa.profit_amount) as total_daily_profit,
|
|
AVG(pa.profit_margin) as avg_daily_profit_margin,
|
|
(SELECT COUNT(*) FROM project_analytics WHERE is_active = 1) as active_projects,
|
|
AVG(pa.quote_accuracy_percentage) as avg_quote_accuracy
|
|
FROM project_analytics pa
|
|
WHERE pa.project_start_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
|
|
GROUP BY DATE(pa.project_start_date)
|
|
ON DUPLICATE KEY UPDATE
|
|
new_projects = VALUES(new_projects),
|
|
total_daily_revenue = VALUES(total_daily_revenue),
|
|
total_daily_profit = VALUES(total_daily_profit),
|
|
avg_daily_profit_margin = VALUES(avg_daily_profit_margin),
|
|
avg_quote_accuracy = VALUES(avg_quote_accuracy),
|
|
last_calculated = CURRENT_TIMESTAMP
|
|
`;
|
|
|
|
try {
|
|
const [result] = await this.connection.execute(query);
|
|
console.log(`✓ Daily Metrics: ${result.affectedRows} records processed`);
|
|
return result.affectedRows;
|
|
} catch (error) {
|
|
console.error('Error populating daily metrics:', error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
// ======================================
|
|
// 6. UPDATE BENCHMARKS
|
|
// ======================================
|
|
async updateBenchmarks() {
|
|
console.log('\n🔄 Updating Business Benchmarks...');
|
|
|
|
const benchmarkUpdates = [
|
|
{
|
|
name: 'avg_profit_margin',
|
|
query: 'SELECT AVG(profit_margin) as value FROM project_analytics WHERE profit_margin > 0'
|
|
},
|
|
{
|
|
name: 'quote_accuracy',
|
|
query: 'SELECT AVG(quote_accuracy_percentage) as value FROM project_analytics WHERE quote_accuracy_percentage > 0'
|
|
},
|
|
{
|
|
name: 'employee_productivity',
|
|
query: 'SELECT AVG(revenue_per_hour) as value FROM employee_performance_analytics WHERE revenue_per_hour > 0'
|
|
},
|
|
{
|
|
name: 'project_overrun_rate',
|
|
query: 'SELECT (SUM(CASE WHEN quote_accuracy_percentage < 100 THEN 1 ELSE 0 END) / COUNT(*)) * 100 as value FROM project_analytics'
|
|
}
|
|
];
|
|
|
|
for (const benchmark of benchmarkUpdates) {
|
|
try {
|
|
const [rows] = await this.connection.execute(benchmark.query);
|
|
const currentValue = rows[0]?.value || 0;
|
|
|
|
await this.connection.execute(`
|
|
UPDATE business_benchmarks
|
|
SET current_value = ?,
|
|
variance_from_target = current_value - target_value,
|
|
performance_score = CASE
|
|
WHEN target_value > 0 THEN (current_value / target_value) * 100
|
|
ELSE 100
|
|
END,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
WHERE metric_name = ?
|
|
`, [currentValue, benchmark.name]);
|
|
|
|
console.log(` ✓ ${benchmark.name}: ${currentValue.toFixed(2)}`);
|
|
} catch (error) {
|
|
console.error(`Error updating benchmark ${benchmark.name}:`, error.message);
|
|
}
|
|
}
|
|
}
|
|
|
|
// ======================================
|
|
// 7. LOG CALCULATION PROCESS
|
|
// ======================================
|
|
async logCalculation(type, startTime, endTime, recordsProcessed, status = 'completed', errorMessage = null) {
|
|
const duration = Math.round((endTime - startTime) / 1000);
|
|
|
|
await this.connection.execute(`
|
|
INSERT INTO analytics_calculation_log (
|
|
calculation_type, start_time, end_time, duration_seconds,
|
|
records_processed, status, error_message
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
`, [type, new Date(startTime), new Date(endTime), duration, recordsProcessed, status, errorMessage]);
|
|
}
|
|
|
|
// ======================================
|
|
// 8. MAIN POPULATION PROCESS
|
|
// ======================================
|
|
async populateAllAnalytics() {
|
|
const startTime = Date.now();
|
|
let totalRecords = 0;
|
|
|
|
console.log('🚀 Starting Complete Analytics Data Population...\n');
|
|
|
|
try {
|
|
await this.connect();
|
|
|
|
// 1. Project Analytics (foundation)
|
|
totalRecords += await this.populateProjectAnalytics();
|
|
|
|
// 2. Employee Analytics (depends on project analytics)
|
|
totalRecords += await this.populateEmployeeAnalytics();
|
|
|
|
// 3. Customer Analytics
|
|
totalRecords += await this.populateCustomerAnalytics();
|
|
|
|
// 4. Material Analytics
|
|
totalRecords += await this.populateMaterialAnalytics();
|
|
|
|
// 5. Daily Metrics
|
|
totalRecords += await this.populateDailyMetrics();
|
|
|
|
// 6. Update Benchmarks
|
|
await this.updateBenchmarks();
|
|
|
|
const endTime = Date.now();
|
|
|
|
// Log successful completion
|
|
await this.logCalculation('full_refresh', startTime, endTime, totalRecords);
|
|
|
|
console.log('\n✅ Analytics Population Complete!');
|
|
console.log(`📊 Total Records Processed: ${totalRecords}`);
|
|
console.log(`⏱️ Duration: ${Math.round((endTime - startTime) / 1000)}s`);
|
|
|
|
// Show summary stats
|
|
await this.showSummaryStats();
|
|
|
|
} catch (error) {
|
|
const endTime = Date.now();
|
|
console.error('\n❌ Analytics Population Failed:', error.message);
|
|
|
|
// Log failure
|
|
await this.logCalculation('full_refresh', startTime, endTime, totalRecords, 'failed', error.message);
|
|
throw error;
|
|
|
|
} finally {
|
|
await this.disconnect();
|
|
}
|
|
}
|
|
|
|
async showSummaryStats() {
|
|
console.log('\n📈 ANALYTICS SUMMARY:');
|
|
|
|
try {
|
|
// Project stats
|
|
const [projectStats] = await this.connection.execute(`
|
|
SELECT
|
|
COUNT(*) as total_projects,
|
|
SUM(total_quote_value) as total_revenue,
|
|
AVG(profit_margin) as avg_profit_margin,
|
|
AVG(quote_accuracy_percentage) as avg_accuracy
|
|
FROM project_analytics
|
|
`);
|
|
|
|
const stats = projectStats[0];
|
|
console.log(` Projects: ${stats.total_projects}`);
|
|
console.log(` Revenue: ${(stats.total_revenue / 1000000).toFixed(1)}M DKK`);
|
|
console.log(` Avg Profit: ${stats.avg_profit_margin?.toFixed(1)}%`);
|
|
console.log(` Avg Accuracy: ${stats.avg_accuracy?.toFixed(1)}%`);
|
|
|
|
// Employee & Customer counts
|
|
const [employeeCount] = await this.connection.execute('SELECT COUNT(DISTINCT employee_id) as count FROM employee_performance_analytics');
|
|
const [customerCount] = await this.connection.execute('SELECT COUNT(DISTINCT customer_id) as count FROM customer_analytics');
|
|
|
|
console.log(` Active Employees: ${employeeCount[0].count}`);
|
|
console.log(` Customers: ${customerCount[0].count}`);
|
|
|
|
} catch (error) {
|
|
console.log(' (Summary stats unavailable)');
|
|
}
|
|
}
|
|
}
|
|
|
|
// Main execution
|
|
async function main() {
|
|
const populator = new AnalyticsDataPopulator();
|
|
|
|
try {
|
|
await populator.populateAllAnalytics();
|
|
process.exit(0);
|
|
} catch (error) {
|
|
console.error('Population failed:', error.message);
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
// Run if called directly
|
|
if (require.main === module) {
|
|
main();
|
|
}
|
|
|
|
module.exports = AnalyticsDataPopulator; |