301 lines
12 KiB
JavaScript
301 lines
12 KiB
JavaScript
const mysql = require('mysql2/promise');
|
|
|
|
class FixedHistoricalQuoteCalculator {
|
|
constructor() {
|
|
this.db = mysql.createConnection({
|
|
host: 'localhost',
|
|
user: 'ordrestyring_user',
|
|
password: process.env.DB_PASSWORD,
|
|
database: 'ordrestyring_local'
|
|
});
|
|
}
|
|
|
|
async generateIntelligentQuote(customerNumber, description, complexity = 3, size = 'medium', requirements = {}) {
|
|
try {
|
|
console.log(`🧮 Generating FIXED intelligent quote for customer ${customerNumber}...`);
|
|
|
|
// Get customer history WITH PROPER AGGREGATION
|
|
const customerHistory = await this.findSimilarCasesFIXED(customerNumber);
|
|
|
|
// Get market data
|
|
const historicalHours = await this.calculateHistoricalHoursFIXED();
|
|
const historicalMaterials = await this.calculateHistoricalMaterialsFIXED();
|
|
|
|
// Calculate base estimate with FIXED algorithm
|
|
const baseEstimate = this.calculateBaseEstimateFIXED(
|
|
customerHistory,
|
|
historicalHours,
|
|
historicalMaterials,
|
|
complexity,
|
|
size
|
|
);
|
|
|
|
// Apply intelligent adjustments
|
|
const adjustments = this.applyIntelligentAdjustments(
|
|
baseEstimate,
|
|
customerHistory,
|
|
description,
|
|
requirements
|
|
);
|
|
|
|
// Calculate confidence score
|
|
const confidence = this.calculateConfidenceScore(
|
|
customerHistory.length,
|
|
historicalHours.total_entries,
|
|
complexity
|
|
);
|
|
|
|
return {
|
|
success: true,
|
|
quote: {
|
|
...baseEstimate,
|
|
adjustments,
|
|
finalQuote: Math.round(baseEstimate.totalQuote * adjustments.totalMultiplier)
|
|
},
|
|
confidence,
|
|
metadata: {
|
|
customerNumber,
|
|
description,
|
|
complexity,
|
|
size,
|
|
generatedAt: new Date().toISOString()
|
|
},
|
|
historicalContext: {
|
|
customerProjects: customerHistory.length,
|
|
totalMarketData: historicalHours.total_entries,
|
|
avgMarketHourlyRate: parseFloat(historicalHours.avg_hourly_rate || 0),
|
|
avgProfitMargin: parseFloat(historicalMaterials.avg_profit_margin || 0)
|
|
}
|
|
};
|
|
|
|
} catch (error) {
|
|
console.error('Error generating intelligent quote:', error);
|
|
return {
|
|
success: false,
|
|
error: error.message
|
|
};
|
|
}
|
|
}
|
|
|
|
async findSimilarCasesFIXED(customerNumber, limit = 10) {
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
oc.case_number,
|
|
oc.description,
|
|
oc.customer_number,
|
|
-- Get hours separately to avoid JOIN multiplication
|
|
(SELECT SUM(duration_hours) FROM ordrestyring_hours WHERE case_number = oc.case_number) as total_hours,
|
|
(SELECT COUNT(*) FROM ordrestyring_hours WHERE case_number = oc.case_number) as hour_entries,
|
|
-- Get materials separately to avoid JOIN multiplication
|
|
(SELECT SUM(total_cost) FROM ordrestyring_case_materials WHERE case_number = oc.case_number) as total_material_cost,
|
|
(SELECT SUM(total_sales) FROM ordrestyring_case_materials WHERE case_number = oc.case_number) as total_material_sales,
|
|
(SELECT COUNT(*) FROM ordrestyring_case_materials WHERE case_number = oc.case_number) as material_entries,
|
|
oc.total_amount as case_value,
|
|
oc.start_date,
|
|
oc.end_date
|
|
FROM ordrestyring_cases oc
|
|
WHERE oc.customer_number = ?
|
|
ORDER BY oc.case_number DESC
|
|
LIMIT ?
|
|
`;
|
|
|
|
const [rows] = await (await this.db).execute(query, [customerNumber, limit]);
|
|
return rows;
|
|
} catch (error) {
|
|
console.error('Error finding similar cases:', error);
|
|
return [];
|
|
}
|
|
}
|
|
|
|
async calculateHistoricalHoursFIXED(filters = {}) {
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
COUNT(*) as total_entries,
|
|
AVG(oh.duration_hours) as avg_hours_per_task,
|
|
SUM(oh.duration_hours) as total_hours,
|
|
COUNT(DISTINCT oh.case_number) as total_cases,
|
|
AVG(ocm_agg.avg_hourly_rate) as avg_hourly_rate
|
|
FROM ordrestyring_hours oh
|
|
LEFT JOIN (
|
|
SELECT
|
|
case_number,
|
|
CASE
|
|
WHEN SUM(total_sales) > 0 AND SUM(duration_hours) > 0
|
|
THEN SUM(total_sales) / SUM(duration_hours)
|
|
ELSE 450
|
|
END as avg_hourly_rate
|
|
FROM (
|
|
SELECT
|
|
ocm.case_number,
|
|
SUM(ocm.total_sales) as total_sales
|
|
FROM ordrestyring_case_materials ocm
|
|
WHERE ocm.total_sales > 0
|
|
GROUP BY ocm.case_number
|
|
) materials
|
|
JOIN (
|
|
SELECT
|
|
case_number,
|
|
SUM(duration_hours) as duration_hours
|
|
FROM ordrestyring_hours
|
|
WHERE duration_hours > 0
|
|
GROUP BY case_number
|
|
) hours ON materials.case_number = hours.case_number
|
|
GROUP BY materials.case_number
|
|
) ocm_agg ON oh.case_number = ocm_agg.case_number
|
|
WHERE oh.duration_hours > 0
|
|
`;
|
|
|
|
const [rows] = await (await this.db).execute(query);
|
|
return rows[0];
|
|
} catch (error) {
|
|
console.error('Error calculating historical hours:', error);
|
|
return { avg_hours_per_task: 8, avg_hourly_rate: 450, total_entries: 0 };
|
|
}
|
|
}
|
|
|
|
async calculateHistoricalMaterialsFIXED(filters = {}) {
|
|
try {
|
|
const query = `
|
|
SELECT
|
|
COUNT(*) as total_materials,
|
|
AVG(ocm.cost_price) as avg_cost_price,
|
|
AVG(ocm.sales_price) as avg_sales_price,
|
|
AVG(CASE
|
|
WHEN ocm.cost_price > 0
|
|
THEN ((ocm.sales_price - ocm.cost_price) / ocm.cost_price) * 100
|
|
ELSE 20
|
|
END) as avg_profit_margin,
|
|
COUNT(DISTINCT ocm.case_number) as cases_with_materials
|
|
FROM ordrestyring_case_materials ocm
|
|
WHERE ocm.cost_price > 0
|
|
`;
|
|
|
|
const [rows] = await (await this.db).execute(query);
|
|
return rows[0];
|
|
} catch (error) {
|
|
console.error('Error calculating material costs:', error);
|
|
return { avg_profit_margin: 20, total_materials: 0 };
|
|
}
|
|
}
|
|
|
|
calculateBaseEstimateFIXED(customerHistory, marketHours, marketMaterials, complexity, size) {
|
|
// Size multipliers
|
|
const sizeMultipliers = {
|
|
small: 0.7,
|
|
medium: 1.0,
|
|
large: 1.4,
|
|
xlarge: 2.0
|
|
};
|
|
|
|
// Complexity multipliers
|
|
const complexityMultiplier = 0.8 + (complexity * 0.1);
|
|
|
|
// Calculate base hours - FIXED ALGORITHM
|
|
let baseHours = 40; // Realistic default
|
|
|
|
if (customerHistory.length > 0) {
|
|
// Use FIXED customer history calculation
|
|
const validCases = customerHistory.filter(c => c.total_hours > 0);
|
|
if (validCases.length > 0) {
|
|
const avgCustomerHours = validCases.reduce((sum, case_) =>
|
|
sum + parseFloat(case_.total_hours), 0) / validCases.length;
|
|
baseHours = avgCustomerHours;
|
|
console.log(`📊 Customer avg hours: ${avgCustomerHours.toFixed(1)}h from ${validCases.length} cases`);
|
|
}
|
|
} else {
|
|
// For new customers, use realistic market baseline
|
|
const marketAvg = parseFloat(marketHours.avg_hours_per_task) || 8;
|
|
baseHours = marketAvg * 5; // Realistic project size multiplier
|
|
console.log(`📊 New customer baseline: ${marketAvg.toFixed(1)}h * 5 = ${baseHours}h`);
|
|
}
|
|
|
|
// Apply multipliers
|
|
const estimatedHours = baseHours * sizeMultipliers[size] * complexityMultiplier;
|
|
const hourlyRate = parseFloat(marketHours.avg_hourly_rate) || 450;
|
|
const laborCost = estimatedHours * hourlyRate;
|
|
|
|
// Calculate materials - FIXED
|
|
const materialRatio = customerHistory.length > 0 ?
|
|
this.calculateCustomerMaterialRatio(customerHistory) : 0.4;
|
|
const materialCost = laborCost * materialRatio;
|
|
|
|
// Profit margin
|
|
const profitMargin = parseFloat(marketMaterials.avg_profit_margin) || 20;
|
|
const subtotal = laborCost + materialCost;
|
|
const profit = subtotal * (profitMargin / 100);
|
|
|
|
return {
|
|
estimatedHours: Math.round(estimatedHours * 10) / 10,
|
|
hourlyRate,
|
|
laborCost: Math.round(laborCost),
|
|
materialCost: Math.round(materialCost),
|
|
subtotal: Math.round(subtotal),
|
|
profitMargin: profitMargin,
|
|
profit: Math.round(profit),
|
|
totalQuote: Math.round(subtotal + profit)
|
|
};
|
|
}
|
|
|
|
calculateCustomerMaterialRatio(customerHistory) {
|
|
const casesWithBothCosts = customerHistory.filter(c =>
|
|
c.total_hours > 0 && c.total_material_cost > 0
|
|
);
|
|
|
|
if (casesWithBothCosts.length === 0) return 0.4;
|
|
|
|
const ratios = casesWithBothCosts.map(c => {
|
|
const laborCost = c.total_hours * 450; // Assume standard rate
|
|
return c.total_material_cost / laborCost;
|
|
});
|
|
|
|
return ratios.reduce((sum, ratio) => sum + ratio, 0) / ratios.length;
|
|
}
|
|
|
|
applyIntelligentAdjustments(baseEstimate, customerHistory, description, requirements) {
|
|
let multiplier = 1.0;
|
|
const adjustments = [];
|
|
|
|
// Customer loyalty adjustment
|
|
if (customerHistory.length > 5) {
|
|
multiplier *= 0.95;
|
|
adjustments.push('Loyal customer discount: -5%');
|
|
}
|
|
|
|
// Complexity based on description
|
|
if (description.toLowerCase().includes('renovering')) {
|
|
multiplier *= 1.1;
|
|
adjustments.push('Renovation complexity: +10%');
|
|
}
|
|
|
|
return {
|
|
totalMultiplier: multiplier,
|
|
adjustments
|
|
};
|
|
}
|
|
|
|
calculateConfidenceScore(customerProjects, marketData, complexity) {
|
|
let confidence = 50; // Base confidence
|
|
|
|
// Customer history boost
|
|
confidence += Math.min(customerProjects * 10, 30);
|
|
|
|
// Market data boost
|
|
if (marketData > 1000) confidence += 15;
|
|
else if (marketData > 100) confidence += 10;
|
|
|
|
// Complexity penalty
|
|
confidence -= (complexity - 3) * 5;
|
|
|
|
return Math.max(10, Math.min(95, confidence));
|
|
}
|
|
|
|
async close() {
|
|
if (this.db) {
|
|
await (await this.db).end();
|
|
}
|
|
}
|
|
}
|
|
|
|
module.exports = FixedHistoricalQuoteCalculator; |