<?php
/**
* Deals API
* GET /api/deals.php — Hämta alla affärer (med filter)
* GET /api/deals.php?id=X — Hämta en affär med detaljer
* GET /api/deals.php?action=pipeline — Pipeline/Kanban-vy (grupperat per status)
* GET /api/deals.php?action=stats — Statistik för dashboard
* POST /api/deals.php — Skapa ny affär
* POST /api/deals.php?action=update — Uppdatera affär
* POST /api/deals.php?action=status — Ändra status
* POST /api/deals.php?action=products — Spara produkter för affär
* DELETE /api/deals.php?id=X — Ta bort affär
*/
require_once __DIR__ . '/config.php';
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(200); exit; }
try {
$db = getDB();
$action = $_GET['action'] ?? '';
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
if ($action === 'pipeline') {
$where = '1=1';
$params = [];
if (!empty($_GET['saljare_id'])) {
$where .= ' AND (d.saljare1_id = ? OR d.saljare2_id = ?)';
$params[] = (int)$_GET['saljare_id'];
$params[] = (int)$_GET['saljare_id'];
}
if (!empty($_GET['region'])) {
$where .= ' AND ap.region = ?';
$params[] = $_GET['region'];
}
if (!empty($_GET['date_from'])) {
$where .= ' AND d.datum_salj >= ?';
$params[] = $_GET['date_from'];
}
if (!empty($_GET['date_to'])) {
$where .= ' AND d.datum_salj <= ?';
$params[] = $_GET['date_to'];
}
if (!empty($_GET['ue'])) {
$where .= ' AND ap.tilldelad_ue LIKE ?';
$params[] = '%' . $_GET['ue'] . '%';
}
if (!empty($_GET['product_type'])) {
$where .= ' AND d.id IN (SELECT deal_id FROM deal_products WHERE product_type = ?)';
$params[] = $_GET['product_type'];
}
$stmt = $db->prepare("
SELECT d.*, c.name as customer_name, c.name2 as customer_name2,
p.address as property_address, p.city as property_city,
s1.name as saljare1_name, s2.name as saljare2_name,
GROUP_CONCAT(DISTINCT dp.product_type) as product_types,
ap.group_title as status_order, ap.avbrott_status, ap.region,
ap.tilldelad_ue, ap.skapa_faktura, ap.faktura_status,
ap.status as ap_status, ap.status_2 as ap_status_2, ap.status_3 as ap_status_3,
ap.ue_pris, ap.kostnader, ap.intakter,
ap.datum_avbrott, ap.datum_fardigstall, ap.projektering_bokad,
ap.projektor_name, ap.bestallare_name
FROM deals d
LEFT JOIN customers c ON d.customer_id = c.id
LEFT JOIN properties p ON d.property_id = p.id
LEFT JOIN staff s1 ON d.saljare1_id = s1.id
LEFT JOIN staff s2 ON d.saljare2_id = s2.id
LEFT JOIN deal_products dp ON dp.deal_id = d.id
LEFT JOIN active_projects ap ON ap.deal_id = d.id
WHERE $where
GROUP BY d.id
ORDER BY d.datum_salj DESC, d.created_at DESC
");
$stmt->execute($params);
$deals = $stmt->fetchAll();
$pipeline = [];
$statuses = ['offert','order','projektering','bestallning','leverans','montering','besiktning','fardigstall','anger','ej_godkand','avbrott'];
foreach ($statuses as $s) $pipeline[$s] = [];
foreach ($deals as $d) {
$pipeline[$d['status']][] = $d;
}
echo json_encode(['pipeline' => $pipeline, 'total' => count($deals)]);
} elseif ($action === 'stats') {
$stmt = $db->query("
SELECT
status,
COUNT(*) as count,
COALESCE(SUM(ordervarde_ink_moms), 0) as total_value
FROM deals
GROUP BY status
");
$stats = [];
while ($row = $stmt->fetch()) {
$stats[$row['status']] = [
'count' => (int)$row['count'],
'value' => (float)$row['total_value']
];
}
echo json_encode($stats);
} elseif ($action === 'top_sellers') {
$stmt = $db->query("
SELECT s.id, s.name,
COUNT(d.id) as deal_count,
COALESCE(SUM(d.ordervarde_ink_moms), 0) as total_value
FROM staff s
INNER JOIN deals d ON (d.saljare1_id = s.id OR d.saljare2_id = s.id)
WHERE d.status NOT IN ('anger','ej_godkand','avbrott')
GROUP BY s.id
ORDER BY total_value DESC
LIMIT 10
");
echo json_encode($stmt->fetchAll());
} elseif ($action === 'contractors') {
// List all UE contractors with project stats
$stmt = $db->query("SELECT * FROM contractors ORDER BY company_name, contact_name");
$rows = $stmt->fetchAll();
// Get project stats per UE name
$ueStats = [];
$ueStmt = $db->query("
SELECT tilldelad_ue,
COUNT(*) as total_projects,
SUM(CASE WHEN group_title IN ('Att beställa','Att boka','Att projektera','Bokat','Leveransbevakning','Ny order','Projektering bokad','Pågående','Support / Felsökning') THEN 1 ELSE 0 END) as active_projects,
SUM(CASE WHEN group_title = 'Färdigställt' THEN 1 ELSE 0 END) as completed_projects,
SUM(CASE WHEN group_title IN ('Avbrott','Avbrott färdigställt','Avbrott ny order / projektering','Ånger') THEN 1 ELSE 0 END) as cancelled_projects
FROM active_projects
WHERE tilldelad_ue IS NOT NULL AND tilldelad_ue != ''
GROUP BY tilldelad_ue
");
foreach ($ueStmt->fetchAll() as $us) {
$ueStats[$us['tilldelad_ue']] = $us;
}
// Group by company
$companies = [];
foreach ($rows as $r) {
$key = $r['company_name'];
if (!isset($companies[$key])) {
$stats = ['total_projects' => 0, 'active_projects' => 0, 'completed_projects' => 0, 'cancelled_projects' => 0];
foreach ($ueStats as $ueName => $us) {
if (stripos($ueName, $key) !== false || stripos($key, $ueName) !== false) {
$stats['total_projects'] += (int)$us['total_projects'];
$stats['active_projects'] += (int)$us['active_projects'];
$stats['completed_projects'] += (int)$us['completed_projects'];
$stats['cancelled_projects'] += (int)$us['cancelled_projects'];
}
}
$companies[$key] = ['name' => $key, 'specialty' => $r['company_specialty'], 'contacts' => [], 'stats' => $stats];
}
$companies[$key]['contacts'][] = $r;
}
echo json_encode(array_values($companies));
} elseif ($action === 'ue_projects') {
// Get all projects for a specific UE name
$ueName = $_GET['ue'] ?? '';
if (!$ueName) throw new Exception('ue parameter krävs');
$stmt = $db->prepare("
SELECT ap.*, d.deal_number, d.ordervarde_ink_moms, d.total_marginal_ink_moms,
d.status as deal_status, d.datum_salj,
c.name as customer_name, p.address as property_address, p.city as property_city
FROM active_projects ap
LEFT JOIN deals d ON d.id = ap.deal_id
LEFT JOIN customers c ON d.customer_id = c.id
LEFT JOIN properties p ON d.property_id = p.id
WHERE ap.tilldelad_ue LIKE ?
ORDER BY ap.created_at DESC
");
$stmt->execute(['%' . $ueName . '%']);
echo json_encode($stmt->fetchAll());
} elseif (!empty($_GET['id'])) {
$id = (int)$_GET['id'];
$stmt = $db->prepare("
SELECT d.*, c.name as customer_name, c.name2 as customer_name2,
c.personnummer, c.personnummer2,
p.address as property_address, p.city as property_city,
p.fastighetsbeteckning, p.huvudsakring, p.takmaterial,
p.taktyp, p.tak_lutning, p.natagare, p.anlaggningsid,
p.placering_elmatare, p.placering_vr,
s1.name as saljare1_name, s2.name as saljare2_name,
b.name as bokare_name,
ap.group_title as status_order, ap.avbrott_status, ap.region,
ap.tilldelad_ue, ap.skapa_faktura, ap.faktura_status,
ap.status as ap_status, ap.status_2 as ap_status_2, ap.status_3 as ap_status_3,
ap.ue_pris, ap.kostnader, ap.intakter, ap.ata,
ap.datum_avbrott, ap.datum_fardigstall, ap.projektering_bokad,
ap.projektor_name, ap.bestallare_name, ap.motesbokare_name,
ap.ansvarig_avbrott, ap.foranmalan, ap.betvillkor, ap.monday_item_id,
ap.vad_ar_salt, ap.antal_solceller, ap.marke_solceller,
ap.batteri_storlek, ap.marke_batteri, ap.projektnummer
FROM deals d
LEFT JOIN customers c ON d.customer_id = c.id
LEFT JOIN properties p ON d.property_id = p.id
LEFT JOIN staff s1 ON d.saljare1_id = s1.id
LEFT JOIN staff s2 ON d.saljare2_id = s2.id
LEFT JOIN staff b ON d.bokare_id = b.id
LEFT JOIN active_projects ap ON ap.deal_id = d.id
WHERE d.id = ?
");
$stmt->execute([$id]);
$deal = $stmt->fetch();
if (!$deal) {
http_response_code(404);
echo json_encode(['error' => 'Affär hittades inte']);
exit;
}
$stmt2 = $db->prepare("SELECT * FROM deal_products WHERE deal_id = ?");
$stmt2->execute([$id]);
$deal['products'] = $stmt2->fetchAll();
$stmt3 = $db->prepare("SELECT * FROM contact_methods WHERE customer_id = ? ORDER BY is_primary DESC");
$stmt3->execute([$deal['customer_id']]);
$deal['contact_methods'] = $stmt3->fetchAll();
$stmt4 = $db->prepare("SELECT sl.*, s.name as changed_by_name FROM deal_status_log sl LEFT JOIN staff s ON sl.changed_by = s.id WHERE sl.deal_id = ? ORDER BY sl.created_at DESC LIMIT 20");
$stmt4->execute([$id]);
$deal['status_log'] = $stmt4->fetchAll();
echo json_encode($deal);
} else {
// List all deals with filters
$where = '1=1';
$params = [];
if (!empty($_GET['status'])) {
$where .= ' AND d.status = ?';
$params[] = $_GET['status'];
}
if (!empty($_GET["status_exclude"])) { $excl = explode(",", $_GET["status_exclude"]); $placeholders = implode(",", array_fill(0, count($excl), "?")); $where .= " AND d.status NOT IN ($placeholders)"; $params = array_merge($params, $excl); }
if (!empty($_GET['salj_status'])) {
$where .= ' AND d.salj_status = ?';
$params[] = $_GET['salj_status'];
}
if (!empty($_GET['region'])) {
$where .= ' AND ap.region = ?';
$params[] = $_GET['region'];
}
if (!empty($_GET['saljare_id'])) {
$where .= ' AND (d.saljare1_id = ? OR d.saljare2_id = ?)';
$params[] = (int)$_GET['saljare_id'];
$params[] = (int)$_GET['saljare_id'];
}
if (!empty($_GET['search'])) {
$where .= ' AND (c.name LIKE ? OR d.deal_number LIKE ? OR p.address LIKE ? OR d.title LIKE ?)';
$s = '%' . $_GET['search'] . '%';
$params = array_merge($params, [$s, $s, $s, $s]);
}
if (!empty($_GET['month'])) {
$where .= ' AND d.datum_salj LIKE ?';
$params[] = $_GET['month'] . '%';
}
if (!empty($_GET['product_type'])) {
$where .= ' AND d.id IN (SELECT deal_id FROM deal_products WHERE product_type = ?)';
$params[] = $_GET['product_type'];
}
$limit = min((int)($_GET["limit"] ?? 50), 10000);
$offset = max((int)($_GET['offset'] ?? 0), 0);
$stmt = $db->prepare("
SELECT d.*, c.name as customer_name, c.name2 as customer_name2,
p.address as property_address, p.city as property_city,
s1.name as saljare1_name, s2.name as saljare2_name,
GROUP_CONCAT(DISTINCT dp.product_type) as product_types,
ap.group_title as status_order, ap.avbrott_status, ap.region,
ap.tilldelad_ue, ap.skapa_faktura, ap.faktura_status,
ap.status as ap_status, ap.status_2 as ap_status_2,
ap.ue_pris, ap.kostnader, ap.intakter,
ap.datum_fardigstall, ap.projektering_bokad
FROM deals d
LEFT JOIN customers c ON d.customer_id = c.id
LEFT JOIN properties p ON d.property_id = p.id
LEFT JOIN staff s1 ON d.saljare1_id = s1.id
LEFT JOIN staff s2 ON d.saljare2_id = s2.id
LEFT JOIN deal_products dp ON dp.deal_id = d.id
LEFT JOIN active_projects ap ON ap.deal_id = d.id
WHERE $where
GROUP BY d.id
ORDER BY d.created_at DESC
LIMIT $limit OFFSET $offset
");
$stmt->execute($params);
$deals = $stmt->fetchAll();
$countStmt = $db->prepare("SELECT COUNT(DISTINCT d.id) as total FROM deals d LEFT JOIN customers c ON d.customer_id = c.id LEFT JOIN properties p ON d.property_id = p.id LEFT JOIN deal_products dp ON dp.deal_id = d.id LEFT JOIN active_projects ap ON ap.deal_id = d.id WHERE $where");
$countStmt->execute($params);
$total = $countStmt->fetch()['total'];
echo json_encode(['deals' => $deals, 'total' => (int)$total]);
}
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST') {
$body = json_decode(file_get_contents('php://input'), true);
if ($action === 'status') {
$dealId = (int)($body['deal_id'] ?? 0);
$newStatus = $body['status'] ?? '';
$changedBy = (int)($body['staff_id'] ?? 0);
$note = $body['note'] ?? '';
if (!$dealId || !$newStatus) throw new Exception('deal_id och status krävs');
$stmt = $db->prepare('SELECT status FROM deals WHERE id = ?');
$stmt->execute([$dealId]);
$current = $stmt->fetch();
if (!$current) throw new Exception('Affär hittades inte');
$stmt = $db->prepare('UPDATE deals SET status = ?, updated_at = NOW() WHERE id = ?');
$stmt->execute([$newStatus, $dealId]);
$stmt = $db->prepare('INSERT INTO deal_status_log (deal_id, old_status, new_status, changed_by, note) VALUES (?, ?, ?, ?, ?)');
$stmt->execute([$dealId, $current['status'], $newStatus, $changedBy ?: null, $note ?: null]);
echo json_encode(['success' => true]);
} elseif ($action === 'update') {
$id = (int)($body['id'] ?? 0);
if (!$id) throw new Exception('id krävs');
$fields = [];
$params = [];
$allowed = ['title','status','salj_status','ordervarde_ink_moms','total_marginal_ink_moms',
'gt_belopp','rot_belopp','finans_via_oss','saljare1_id','saljare2_id','bokare_id',
'datum_salj','datum_anger','lev_adress','ovrig_info','bokat','customer_id','property_id'];
foreach ($allowed as $f) {
if (array_key_exists($f, $body)) {
$fields[] = "$f = ?";
$params[] = $body[$f] === '' ? null : $body[$f];
}
}
if (empty($fields)) throw new Exception('Inga fält att uppdatera');
$params[] = $id;
$stmt = $db->prepare("UPDATE deals SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?");
$stmt->execute($params);
echo json_encode(['success' => true]);
} elseif ($action === 'products') {
$dealId = (int)($body['deal_id'] ?? 0);
$products = $body['products'] ?? [];
if (!$dealId) throw new Exception('deal_id krävs');
$db->prepare('DELETE FROM deal_products WHERE deal_id = ?')->execute([$dealId]);
$stmt = $db->prepare('INSERT INTO deal_products (deal_id, product_type, antal, marke, modell, kwh, kvm, meter, details) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
foreach ($products as $p) {
$stmt->execute([
$dealId,
$p['product_type'],
$p['antal'] ?? null,
$p['marke'] ?? null,
$p['modell'] ?? null,
$p['kwh'] ?? null,
$p['kvm'] ?? null,
$p['meter'] ?? null,
isset($p['details']) ? json_encode($p['details']) : null
]);
}
echo json_encode(['success' => true]);
} else {
// Create new deal
$customerId = (int)($body['customer_id'] ?? 0);
if (!$customerId) {
$customerName = $body['customer_name'] ?? '';
if (!$customerName) throw new Exception('customer_id eller customer_name krävs');
$stmt = $db->prepare('INSERT INTO customers (name, personnummer, name2, personnummer2, created_by) VALUES (?, ?, ?, ?, ?)');
$stmt->execute([
$customerName,
$body['personnummer'] ?? null,
$body['customer_name2'] ?? null,
$body['personnummer2'] ?? null,
$body['staff_id'] ?? null
]);
$customerId = (int)$db->lastInsertId();
if (!empty($body['phone'])) {
$db->prepare('INSERT INTO contact_methods (customer_id, type, value, is_primary) VALUES (?, ?, ?, 1)')->execute([$customerId, 'phone', $body['phone']]);
}
if (!empty($body['email'])) {
$db->prepare('INSERT INTO contact_methods (customer_id, type, value, is_primary) VALUES (?, ?, ?, 1)')->execute([$customerId, 'email', $body['email']]);
}
}
$propertyId = (int)($body['property_id'] ?? 0);
if (!$propertyId && !empty($body['address'])) {
$stmt = $db->prepare('INSERT INTO properties (address, city, fastighetsbeteckning, huvudsakring, takmaterial, taktyp) VALUES (?, ?, ?, ?, ?, ?)');
$stmt->execute([
$body['address'],
$body['city'] ?? null,
$body['fastighetsbeteckning'] ?? null,
$body['huvudsakring'] ?? null,
$body['takmaterial'] ?? null,
$body['taktyp'] ?? null
]);
$propertyId = (int)$db->lastInsertId();
$db->prepare('INSERT IGNORE INTO customer_properties (customer_id, property_id) VALUES (?, ?)')->execute([$customerId, $propertyId]);
}
$year = date('Y');
$stmt = $db->query("SELECT COUNT(*) as cnt FROM deals WHERE YEAR(created_at) = $year");
$cnt = $stmt->fetch()['cnt'] + 1;
$dealNumber = "AFF-$year-" . str_pad($cnt, 3, '0', STR_PAD_LEFT);
$stmt = $db->prepare("INSERT INTO deals (deal_number, customer_id, property_id, title, status, salj_status, ordervarde_ink_moms, total_marginal_ink_moms, gt_belopp, rot_belopp, finans_via_oss, saljare1_id, saljare2_id, bokare_id, datum_salj, lev_adress, ovrig_info, bokat, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->execute([
$dealNumber,
$customerId,
$propertyId ?: null,
$body['title'] ?? null,
$body['status'] ?? 'offert',
$body['salj_status'] ?? 'ej_hanterad',
$body['ordervarde_ink_moms'] ?? null,
$body['total_marginal_ink_moms'] ?? null,
$body['gt_belopp'] ?? null,
$body['rot_belopp'] ?? null,
$body['finans_via_oss'] ?? null,
$body['saljare1_id'] ?? null,
$body['saljare2_id'] ?? null,
$body['bokare_id'] ?? null,
$body['datum_salj'] ?? null,
$body['lev_adress'] ?? null,
$body['ovrig_info'] ?? null,
$body['bokat'] ?? 0,
$body['staff_id'] ?? null
]);
$dealId = (int)$db->lastInsertId();
$db->prepare('INSERT INTO deal_status_log (deal_id, new_status, changed_by) VALUES (?, ?, ?)')->execute([$dealId, $body['status'] ?? 'offert', $body['staff_id'] ?? null]);
if (!empty($body['products'])) {
$pStmt = $db->prepare('INSERT INTO deal_products (deal_id, product_type, antal, marke, modell, kwh, kvm, meter) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
foreach ($body['products'] as $p) {
$pStmt->execute([
$dealId,
$p['product_type'],
$p['antal'] ?? null,
$p['marke'] ?? null,
$p['modell'] ?? null,
$p['kwh'] ?? null,
$p['kvm'] ?? null,
$p['meter'] ?? null
]);
}
}
echo json_encode(['success' => true, 'id' => $dealId, 'deal_number' => $dealNumber]);
}
} elseif ($_SERVER['REQUEST_METHOD'] === 'DELETE') {
$id = (int)($_GET['id'] ?? 0);
if (!$id) throw new Exception('id krävs');
$db->prepare('DELETE FROM deals WHERE id = ?')->execute([$id]);
echo json_encode(['success' => true]);
} else {
http_response_code(405);
echo json_encode(['error' => 'Method not allowed']);
}
} catch (Exception $e) {
http_response_code(500);
echo json_encode(['error' => $e->getMessage()]);
}