312 lines
13 KiB
PHP
312 lines
13 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
require_once __DIR__ . '/../kontakte/service.php';
|
|
require_once __DIR__ . '/../artikel-mapping/service.php';
|
|
require_once __DIR__ . '/../lager/service.php';
|
|
|
|
function shopify_projection_payment_status(string $financialStatus): string
|
|
{
|
|
return match (strtoupper(trim($financialStatus))) {
|
|
'PAID', 'PARTIALLY_PAID' => 'paid',
|
|
'AUTHORIZED' => 'authorized',
|
|
'PARTIALLY_REFUNDED' => 'partially_refunded',
|
|
'REFUNDED' => 'refunded',
|
|
'VOIDED' => 'voided',
|
|
default => 'unknown',
|
|
};
|
|
}
|
|
|
|
function shopify_projection_order_status(array $order): string
|
|
{
|
|
if (($order['cancelledAt'] ?? null) !== null) {
|
|
return 'cancelled';
|
|
}
|
|
|
|
return strtoupper((string) ($order['displayFulfillmentStatus'] ?? '')) === 'FULFILLED'
|
|
? 'fulfilled'
|
|
: 'imported';
|
|
}
|
|
|
|
function shopify_projection_money(array $order, string $field): ?float
|
|
{
|
|
$amount = $order[$field]['shopMoney']['amount'] ?? null;
|
|
return $amount === null ? null : parse_number($amount);
|
|
}
|
|
|
|
function shopify_projection_customer(PDO $pdo, array $customer, bool $dryRun): ?int
|
|
{
|
|
$customerGid = trim((string) ($customer['id'] ?? ''));
|
|
if ($customerGid === '') {
|
|
return null;
|
|
}
|
|
|
|
$identity = $pdo->prepare(
|
|
'SELECT party_id FROM party_external_identity
|
|
WHERE source_system = \'shopify\' AND external_id = :external_id LIMIT 1'
|
|
);
|
|
$identity->execute([':external_id' => $customerGid]);
|
|
$partyId = $identity->fetchColumn();
|
|
if ($partyId !== false) {
|
|
return (int) $partyId;
|
|
}
|
|
|
|
$email = trim((string) ($customer['email'] ?? ''));
|
|
if ($dryRun) {
|
|
return null;
|
|
}
|
|
|
|
$name = trim((string) ($customer['firstName'] ?? '') . ' ' . (string) ($customer['lastName'] ?? ''));
|
|
if ($name === '') {
|
|
$name = 'Shopify-Kunde';
|
|
}
|
|
|
|
$partyStmt = $pdo->prepare(
|
|
"INSERT INTO party (type, name, email, status, created_at, updated_at)
|
|
VALUES ('customer', :name, :email, 'active', NOW(), NOW())
|
|
RETURNING id"
|
|
);
|
|
$partyStmt->execute([':name' => $name, ':email' => $email !== '' ? $email : null]);
|
|
$partyId = $partyStmt->fetchColumn();
|
|
if ($partyId === false) {
|
|
throw new RuntimeException('Shopify-Kunde konnte nicht angelegt werden');
|
|
}
|
|
|
|
$identityStmt = $pdo->prepare(
|
|
"INSERT INTO party_external_identity (party_id, source_system, external_id, observed_at, created_at, updated_at)
|
|
VALUES (:party_id, 'shopify', :external_id, NOW(), NOW(), NOW())"
|
|
);
|
|
$identityStmt->execute([':party_id' => (int) $partyId, ':external_id' => $customerGid]);
|
|
|
|
return (int) $partyId;
|
|
}
|
|
|
|
function shopify_projection_address(PDO $pdo, ?int $partyId, array $address, string $type, bool $dryRun): void
|
|
{
|
|
if ($partyId === null || $dryRun || $address === []) {
|
|
return;
|
|
}
|
|
|
|
$stmt = $pdo->prepare(
|
|
'INSERT INTO address (
|
|
party_id, type, first_name, last_name, company_name, street, house_number,
|
|
zip, city, state_code, country_name, country_iso2, created_at, updated_at
|
|
) VALUES (
|
|
:party_id, :type, :first_name, :last_name, :company_name, :street, :house_number,
|
|
:zip, :city, :state_code, :country_name, :country_iso2, NOW(), NOW()
|
|
)'
|
|
);
|
|
$street = trim((string) ($address['address1'] ?? ''));
|
|
$address2 = trim((string) ($address['address2'] ?? ''));
|
|
if ($address2 !== '') {
|
|
$street .= ($street !== '' ? ', ' : '') . $address2;
|
|
}
|
|
$stmt->execute([
|
|
':party_id' => $partyId,
|
|
':type' => $type,
|
|
':first_name' => trim((string) ($address['firstName'] ?? '')),
|
|
':last_name' => trim((string) ($address['lastName'] ?? '')),
|
|
':company_name' => trim((string) ($address['company'] ?? '')) ?: null,
|
|
':street' => $street,
|
|
':house_number' => null,
|
|
':zip' => trim((string) ($address['zip'] ?? '')),
|
|
':city' => trim((string) ($address['city'] ?? '')),
|
|
':state_code' => trim((string) ($address['provinceCode'] ?? '')) ?: null,
|
|
':country_name' => trim((string) ($address['country'] ?? '')) ?: null,
|
|
':country_iso2' => trim((string) ($address['countryCodeV2'] ?? '')) ?: null,
|
|
]);
|
|
}
|
|
|
|
function shopify_projection_line(PDO $pdo, array $line, bool $dryRun): array
|
|
{
|
|
$sku = trim((string) ($line['sku'] ?? ($line['variant']['sku'] ?? '')));
|
|
$variantGid = trim((string) ($line['variant']['id'] ?? ''));
|
|
$productGid = trim((string) ($line['variant']['product']['id'] ?? ''));
|
|
$sellableItemId = find_shopify_sellable_item_id($pdo, $sku, $variantGid);
|
|
if ($sellableItemId === null) {
|
|
throw new RuntimeException("Kein Shopify-Artikel-Mapping fuer SKU '{$sku}'");
|
|
}
|
|
|
|
if (!$dryRun) {
|
|
bind_shopify_item_identity($pdo, $sellableItemId, $sku, $productGid, $variantGid);
|
|
}
|
|
|
|
$qty = parse_number($line['quantity'] ?? null);
|
|
if ($qty === null || $qty <= 0) {
|
|
throw new RuntimeException("Ungueltige Shopify-Menge fuer SKU '{$sku}'");
|
|
}
|
|
|
|
return [
|
|
'line' => $line,
|
|
'sku' => $sku,
|
|
'variant_gid' => $variantGid,
|
|
'product_gid' => $productGid,
|
|
'sellable_item_id' => $sellableItemId,
|
|
'qty' => $qty,
|
|
'unit_price' => parse_number($line['originalUnitPriceSet']['shopMoney']['amount'] ?? null),
|
|
];
|
|
}
|
|
|
|
function shopify_projection_stock_check(PDO $pdo, array $resolvedLines): void
|
|
{
|
|
foreach ($resolvedLines as $resolvedLine) {
|
|
$components = get_item_components($pdo, (int) $resolvedLine['sellable_item_id']);
|
|
if ($components === []) {
|
|
throw new RuntimeException("Keine Komponenten fuer SKU '{$resolvedLine['sku']}'");
|
|
}
|
|
foreach ($components as $component) {
|
|
$required = (float) $resolvedLine['qty'] * (float) $component['qty_per_item'];
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COALESCE(SUM(v.qty_net), 0)
|
|
FROM v_stock_lot_balance v
|
|
JOIN stock_lot sl ON sl.id = v.stock_lot_id
|
|
WHERE sl.product_id = :product_id AND sl.status = 'current'"
|
|
);
|
|
$stmt->execute([':product_id' => (int) $component['product_id']]);
|
|
$available = (float) ($stmt->fetchColumn() ?: 0);
|
|
if ($available + 0.0000001 < $required) {
|
|
throw new RuntimeException("Nicht genuegend aktueller Bestand fuer SKU '{$resolvedLine['sku']}'");
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
function project_shopify_order(PDO $pdo, string $orderGid, callable $orderLoader, bool $dryRun = true): array
|
|
{
|
|
$orderGid = trim($orderGid);
|
|
if ($orderGid === '') {
|
|
throw new RuntimeException('Shopify Order-GID fehlt');
|
|
}
|
|
|
|
$order = $orderLoader($orderGid);
|
|
if (!is_array($order) || (string) ($order['id'] ?? '') !== $orderGid) {
|
|
throw new RuntimeException("Shopify order {$orderGid} konnte nicht geladen werden");
|
|
}
|
|
|
|
$existingStmt = $pdo->prepare('SELECT id FROM sales_order WHERE shopify_order_gid = :shopify_order_gid LIMIT 1');
|
|
$existingStmt->execute([':shopify_order_gid' => $orderGid]);
|
|
$existingId = $existingStmt->fetchColumn();
|
|
if ($existingId !== false) {
|
|
return ['status' => 'already_imported', 'shopify_order_gid' => $orderGid, 'sales_order_id' => (int) $existingId];
|
|
}
|
|
|
|
$resolvedLines = [];
|
|
foreach (($order['lineItems']['nodes'] ?? []) as $line) {
|
|
$resolvedLines[] = shopify_projection_line($pdo, $line, $dryRun);
|
|
}
|
|
if ($resolvedLines === []) {
|
|
throw new RuntimeException('Shopify-Bestellung enthaelt keine Positionen');
|
|
}
|
|
shopify_projection_stock_check($pdo, $resolvedLines);
|
|
|
|
if ($dryRun) {
|
|
$customer = is_array($order['customer'] ?? null) ? $order['customer'] : [];
|
|
shopify_projection_customer($pdo, $customer, true);
|
|
return [
|
|
'status' => 'dry_run_ready',
|
|
'shopify_order_gid' => $orderGid,
|
|
'sales_order_id' => null,
|
|
'line_count' => count($resolvedLines),
|
|
'allocation_count' => 0,
|
|
];
|
|
}
|
|
|
|
$pdo->beginTransaction();
|
|
try {
|
|
$customer = is_array($order['customer'] ?? null) ? $order['customer'] : [];
|
|
$partyId = shopify_projection_customer($pdo, $customer, false);
|
|
$orderStatus = shopify_projection_order_status($order);
|
|
$paymentStatus = shopify_projection_payment_status((string) ($order['displayFinancialStatus'] ?? ''));
|
|
$orderDate = new DateTimeImmutable((string) $order['createdAt']);
|
|
$externalRef = (string) ($order['name'] ?? $orderGid);
|
|
$orderStmt = $pdo->prepare(
|
|
'INSERT INTO sales_order (
|
|
external_ref, party_id, order_date, order_status, payment_status, amount_net,
|
|
amount_shipping, amount_tax, total_amount, currency, webhook_payload, imported_at,
|
|
created_at, updated_at, order_source, shopify_order_gid, shopify_order_name,
|
|
shopify_created_at, shopify_updated_at, shopify_financial_status, shopify_fulfillment_status
|
|
) VALUES (
|
|
:external_ref, :party_id, :order_date, :order_status, :payment_status, :amount_net,
|
|
:amount_shipping, :amount_tax, :total_amount, :currency, :webhook_payload::jsonb, NOW(),
|
|
NOW(), NOW(), \'shopify\', :shopify_order_gid, :shopify_order_name,
|
|
:shopify_created_at, :shopify_updated_at, :shopify_financial_status, :shopify_fulfillment_status
|
|
) RETURNING id'
|
|
);
|
|
$orderStmt->execute([
|
|
':external_ref' => $externalRef,
|
|
':party_id' => $partyId,
|
|
':order_date' => $orderDate->format('Y-m-d H:i:s'),
|
|
':order_status' => $orderStatus,
|
|
':payment_status' => $paymentStatus,
|
|
':amount_net' => shopify_projection_money($order, 'currentSubtotalPriceSet'),
|
|
':amount_shipping' => shopify_projection_money($order, 'totalShippingPriceSet'),
|
|
':amount_tax' => shopify_projection_money($order, 'totalTaxSet'),
|
|
':total_amount' => shopify_projection_money($order, 'totalPriceSet'),
|
|
':currency' => (string) (($order['totalPriceSet']['shopMoney']['currencyCode'] ?? 'CHF')),
|
|
':webhook_payload' => json_encode($order, JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
|
|
':shopify_order_gid' => $orderGid,
|
|
':shopify_order_name' => (string) ($order['name'] ?? ''),
|
|
':shopify_created_at' => $orderDate->format('Y-m-d H:i:s'),
|
|
':shopify_updated_at' => (new DateTimeImmutable((string) $order['updatedAt']))->format('Y-m-d H:i:s'),
|
|
':shopify_financial_status' => (string) ($order['displayFinancialStatus'] ?? ''),
|
|
':shopify_fulfillment_status' => (string) ($order['displayFulfillmentStatus'] ?? ''),
|
|
]);
|
|
$orderId = (int) $orderStmt->fetchColumn();
|
|
|
|
$locations = get_default_location_ids($pdo);
|
|
$lineNo = 0;
|
|
$allocationCount = 0;
|
|
foreach ($resolvedLines as $resolvedLine) {
|
|
$lineNo++;
|
|
$line = $resolvedLine['line'];
|
|
$lineStmt = $pdo->prepare(
|
|
'INSERT INTO sales_order_line (
|
|
sales_order_id, line_no, sellable_item_id, raw_external_article_number,
|
|
raw_external_title, qty, unit_price, line_total, shopify_line_item_gid,
|
|
shopify_variant_gid, shopify_product_gid, sku, created_at, updated_at
|
|
) VALUES (
|
|
:sales_order_id, :line_no, :sellable_item_id, :article_number,
|
|
:title, :qty, :unit_price, :line_total, :line_gid,
|
|
:variant_gid, :product_gid, :sku, NOW(), NOW()
|
|
) RETURNING id'
|
|
);
|
|
$qty = (float) $resolvedLine['qty'];
|
|
$unitPrice = $resolvedLine['unit_price'];
|
|
$lineStmt->execute([
|
|
':sales_order_id' => $orderId,
|
|
':line_no' => $lineNo,
|
|
':sellable_item_id' => $resolvedLine['sellable_item_id'],
|
|
':article_number' => $resolvedLine['sku'],
|
|
':title' => (string) ($line['name'] ?? $resolvedLine['sku']),
|
|
':qty' => $qty,
|
|
':unit_price' => $unitPrice,
|
|
':line_total' => $unitPrice === null ? null : round($qty * $unitPrice, 2),
|
|
':line_gid' => $resolvedLine['line']['id'],
|
|
':variant_gid' => $resolvedLine['variant_gid'],
|
|
':product_gid' => $resolvedLine['product_gid'],
|
|
':sku' => $resolvedLine['sku'],
|
|
]);
|
|
$lineId = (int) $lineStmt->fetchColumn();
|
|
$allocation = allocate_line_inventory($pdo, $orderId, $lineId, $lineNo, $qty, (int) $resolvedLine['sellable_item_id'], $locations, 'shopify-order');
|
|
$allocationCount += (int) ($allocation['allocationCount'] ?? 0);
|
|
}
|
|
|
|
shopify_projection_address($pdo, $partyId, (array) ($order['billingAddress'] ?? []), 'billing', false);
|
|
shopify_projection_address($pdo, $partyId, (array) ($order['shippingAddress'] ?? []), 'shipping', false);
|
|
$pdo->commit();
|
|
} catch (Throwable $exception) {
|
|
if ($pdo->inTransaction()) {
|
|
$pdo->rollBack();
|
|
}
|
|
throw $exception;
|
|
}
|
|
|
|
return [
|
|
'status' => 'imported',
|
|
'shopify_order_gid' => $orderGid,
|
|
'sales_order_id' => $orderId,
|
|
'line_count' => count($resolvedLines),
|
|
'allocation_count' => $allocationCount,
|
|
];
|
|
}
|