117 lines
4.5 KiB
JavaScript
117 lines
4.5 KiB
JavaScript
const mysql = require('mysql2/promise');
|
|
|
|
const dbConfig = {
|
|
host: 'localhost',
|
|
user: 'ordrestyring_user',
|
|
password: process.env.DB_PASSWORD,
|
|
database: 'ordrestyring_local'
|
|
};
|
|
|
|
async function debugQuoteCalculation() {
|
|
const connection = await mysql.createConnection(dbConfig);
|
|
|
|
console.log('=== DEBUG QUOTE ALGORITHM CASE 1469 ===\n');
|
|
|
|
// Actual case data
|
|
const [actualData] = await connection.execute(`
|
|
SELECT
|
|
SUM(oh.duration_hours) as actual_hours,
|
|
COUNT(*) as actual_entries,
|
|
AVG(oh.duration_hours) as avg_hours_per_entry,
|
|
oc.total_amount as case_amount
|
|
FROM ordrestyring_hours oh
|
|
LEFT JOIN ordrestyring_cases oc ON oh.case_number = oc.case_number
|
|
WHERE oh.case_number = '1469'
|
|
GROUP BY oc.total_amount
|
|
`);
|
|
|
|
console.log('ACTUAL CASE DATA:');
|
|
console.log('- Faktiske timer:', actualData[0].actual_hours);
|
|
console.log('- Antal registreringer:', actualData[0].actual_entries);
|
|
console.log('- Gennemsnit per registrering:', Number(actualData[0].avg_hours_per_entry).toFixed(2));
|
|
console.log('- Case beløb:', actualData[0].case_amount);
|
|
|
|
// Historical customer analysis
|
|
const [customerHistory] = await connection.execute(`
|
|
SELECT
|
|
customer_id,
|
|
COUNT(DISTINCT case_number) as total_cases,
|
|
AVG(total_amount) as avg_amount,
|
|
SUM(total_amount) as total_amount
|
|
FROM ordrestyring_cases
|
|
WHERE customer_id = (
|
|
SELECT customer_id FROM ordrestyring_cases WHERE case_number = '1469'
|
|
)
|
|
`);
|
|
|
|
console.log('\nCUSTOMER HISTORICAL DATA:');
|
|
console.log('- Customer ID:', customerHistory[0].customer_id);
|
|
console.log('- Antal sager:', customerHistory[0].total_cases);
|
|
console.log('- Gennemsnit beløb:', customerHistory[0].avg_amount);
|
|
console.log('- Total beløb:', customerHistory[0].total_amount);
|
|
|
|
// Similar cases analysis
|
|
const [similarCases] = await connection.execute(`
|
|
SELECT
|
|
case_number,
|
|
total_amount,
|
|
actual_hours,
|
|
(total_amount / actual_hours) as hourly_rate
|
|
FROM (
|
|
SELECT
|
|
oc.case_number,
|
|
oc.total_amount,
|
|
SUM(oh.duration_hours) as actual_hours
|
|
FROM ordrestyring_cases oc
|
|
LEFT JOIN ordrestyring_hours oh ON oc.case_number = oh.case_number
|
|
WHERE oc.total_amount BETWEEN 500000 AND 800000
|
|
AND oh.duration_hours IS NOT NULL
|
|
GROUP BY oc.case_number, oc.total_amount
|
|
HAVING actual_hours > 0
|
|
) t
|
|
ORDER BY ABS(total_amount - 680000)
|
|
LIMIT 5
|
|
`);
|
|
|
|
console.log('\nSIMILAR CASES (500k-800k beløb):');
|
|
similarCases.forEach(case_ => {
|
|
console.log(`- Sag ${case_.case_number}: Beløb ${case_.total_amount}kr, Timer ${case_.actual_hours}h, Timepris: ${(case_.hourly_rate || 0).toFixed(0)}kr`);
|
|
});
|
|
|
|
// Identify the algorithmic issue
|
|
console.log('\n=== ALGORITHM DEBUG ===');
|
|
|
|
// Check if we're multiplying by case count instead of using averages
|
|
const totalCases = customerHistory[0].total_cases;
|
|
const totalAmount = customerHistory[0].total_amount;
|
|
const avgAmount = customerHistory[0].avg_amount;
|
|
|
|
console.log('Potentielle problemer:');
|
|
console.log(`- Hvis vi bruger total_amount (${totalAmount}) i stedet for avg (${avgAmount}): ${totalAmount / avgAmount}x fejl`);
|
|
console.log(`- Hvis vi ganger med antal sager (${totalCases}): ${totalCases}x fejl`);
|
|
|
|
// Calculate expected hourly rate
|
|
const actualHours = actualData[0].actual_hours;
|
|
const actualAmount = actualData[0].case_amount;
|
|
const expectedHourlyRate = actualAmount / actualHours;
|
|
console.log(`- Faktisk timepris for sag 1469: ${expectedHourlyRate.toFixed(0)}kr/time`);
|
|
|
|
// Check material multiplier issues
|
|
const [materialStats] = await connection.execute(`
|
|
SELECT
|
|
COUNT(*) as material_entries,
|
|
SUM(quantity * unit_price) as total_material_cost,
|
|
AVG(quantity * unit_price) as avg_item_cost
|
|
FROM ordrestyring_materials
|
|
WHERE case_number = '1469'
|
|
`);
|
|
|
|
console.log('\nMATERIAL DATA:');
|
|
console.log('- Material entries:', materialStats[0].material_entries);
|
|
console.log('- Total material cost:', materialStats[0].total_material_cost);
|
|
console.log('- Avg item cost:', materialStats[0].avg_item_cost);
|
|
|
|
await connection.end();
|
|
}
|
|
|
|
debugQuoteCalculation().catch(console.error); |