thetool/scripts/preorder/custom_notifications/send-preorder-custom-new-notification.php

<?php

global $db;
require_once __DIR__.'/init.php';


$earliest_order_date = new DateTime("2025-05-01");
$earliest_order_date->setTimezone(new DateTimeZone("Europe/Vienna"));

$allowed_netowners = [4807]; // vorerst nur RML

$sql = "SELECT 
    p.id,
    p.type,
    p.preordercampaign_id,
    p.adb_wohneinheit_id,
    pc.name as campaign_name,
    ps_current.code as current_status_code,
    ps_current.name as current_status_name,
    w.id as wohneinheit_id,
    adb_status.code as wohneinheit_status_code,
    adb_status.name as wohneinheit_status_name,
    pcsmt.mailtemplate_id,
    pcsmt.logical_config
FROM `" . FRONKDB_DBNAME . "`.Preorder p
INNER JOIN `" . FRONKDB_DBNAME . "`.Preordercampaign pc ON p.preordercampaign_id = pc.id
INNER JOIN `" . FRONKDB_DBNAME . "`.PreordercampaignStatusnotificationMailtemplate pcsmt ON pc.id = pcsmt.preordercampaign_id
INNER JOIN `" . FRONKDB_DBNAME . "`.Preorderstatus ps_current ON p.status_id = ps_current.id
INNER JOIN `" . ADDRESSDB_DBNAME . "`.Wohneinheit w ON p.adb_wohneinheit_id = w.id
INNER JOIN `" . ADDRESSDB_DBNAME . "`.Status adb_status ON w.status_id = adb_status.id
LEFT JOIN `" . FRONKDB_DBNAME . "`.PreorderStatusnotificationLog psnl ON p.id = psnl.preorder_id AND psnl.email_type = '300-custom-new-order'
WHERE p.type = 'order'
    AND p.adb_wohneinheit_id IS NOT NULL  -- Must have a wohneinheit linked
    AND adb_status.code < 300  -- Wohneinheit status must be less than 300
    -- AND adb_status.type = 'wohneinheit'  -- Ensure we're checking the right status type
    AND pcsmt.notification_type = 'logical'
    AND JSON_EXTRACT(pcsmt.logical_config, '$.type') = '300-custom-new-order'
    AND psnl.id IS NULL  -- No existing '300-custom-new-order' notification
    AND p.create > ".$earliest_order_date->getTimestamp()." -- no older than \$earliest_order_date
    AND p.create < (UNIX_TIMESTAMP(NOW()) - 7200)  -- Only consider preorders older than 2 hours
ORDER BY p.id ASC;";

$res = $db->query($sql);

if ($res->num_rows == 0) {
    logMessage("No preorders found for custom 300-new-order notification.");
    exit;
}

logMessage("Found " . $res->num_rows . " preorders for 300-custom-new-order notification.");

while($row = $res->fetch_assoc()) {
    logMessage("Processing Preorder ID: " . $row['id']);
    $preorder_id = $row['id'];
    $campaign_name = $row['campaign_name'];
    $mailtemplate_id = $row['mailtemplate_id'];
    $wohneinheit_status_code = $row['wohneinheit_status_code'];
    $wohneinheit_status_name = $row['wohneinheit_status_name'];

    logMessage("Wohneinheit Status: {$wohneinheit_status_code} ({$wohneinheit_status_name})");

    $preorder = new Preorder($preorder_id);
    if (!$preorder || !$preorder->id) {
        logMessage("Preorder with ID $preorder_id not found. Skipping.");
        continue;
    }

    if($preorder->status->code >= 899) {
        logMessage("Preorder with ID $preorder_id has status {$preorder->status->code} ({$preorder->status->name}). Skipping.");
        continue;
    }

    $preordercampaign = new Preordercampaign($preorder->preordercampaign_id);
    if (!$preordercampaign || !$preordercampaign->id) {
        logMessage("Preordercampaign with ID {$preorder->preordercampaign_id} not found. Skipping.");
        continue;
    }

    $netowner_id = $preordercampaign->network->owner_id;
    if(!$netowner_id) {
        logMessage("No Netowner ID found for Preorder ID $preorder_id. Skipping.");
        continue;
    }

    if(!in_array($netowner_id, $allowed_netowners)) {
        logMessage("Netowner not allowed");
        continue;
    }

    $mailtemplate = new Mailtemplate($mailtemplate_id);
    if(!$mailtemplate || !$mailtemplate->id) {
        logMessage("Mailtemplate with ID $mailtemplate_id not found. Skipping.");
        continue;
    }

    logMessage("Preparing to send email for Preorder ID: $preorder_id, Campaign: $campaign_name");


    //var_dump($preorder);exit;
    // find correct price embed based on actual setup price
    $po = PreordercampaignOperatorModel::getFirst(["preordercampaign_id" => $preordercampaign->id, "isp_id" => $preorder->partner_id]);
    if(!$po) {
        $po = PreordercampaignOperatorModel::getFirst(["preordercampaign_id" => $preordercampaign->id, "operator_id" => $preorder->partner_id]);
    }
    if(!$po) {
        logMessage("No Netoperator found for Preorder ID $preorder_id and Campaign ID {$preordercampaign->id}. Skipping.");
        continue;
    }
    //var_dump($po);exit;

    $netoperator = new Address($po->operator_id);
    if(!$netoperator) {
        logMessage("No Netoperator found for Preorder ID $preorder_id and Campaign ID {$preordercampaign->id}. Skipping.");
        continue;
    }

    if($preorder->order_date) {
        $order_date = new DateTime('@'.$preorder->order_date);
    } else {
        $order_date = new DateTime('@'.$preorder->create);
    }
    $order_date->setTimezone(new DateTimeZone("Europe/Vienna"));
    $order_date->setTime(4,0,0);



    $product = PreorderProduct::getFirst(["type" => "enduser_setup"]);
    if(!$product) {
        logMessage("operator_setup price not found! Preorder $preorder_id");
        continue;
    }

    $product->setNetownerId($netowner_id);
    $product->setNetoperatorId($netoperator->id);
    $price = $product->getCampaignPrice($preorder->preordercampaign_id, $order_date->format("Y-m-d"));

    if(!$price) {
        logMessage("No price found for Preorder ID $preorder_id and Campaign ID {$preordercampaign->id}. Skipping.");
        continue;
    }

    $price_setup = $price->price_setup;

    $template_embed_name = "{{EMBED:tpl-rml-neue-bestellung-preistext-option1-gratis}}";
    if($price_setup > 0.0000) {
        $template_embed_name = "{{EMBED:tpl-rml-neue-bestellung-preistext-option2-preis}}";
        $price_setup *= 1.2;
    }

    $log->debug(__FILE__.": Sending template $template_embed_name for Preorder ".$preorder->id." OAID ".$preorder->oaid);

    sendPreorderEmail(
        $mailtemplate,
        $preordercampaign,
        $preorder,
        "300-custom-new-order",
        [
            "RML_NEW_ORDER_PRICE_OPTION_EMBED" => $template_embed_name,
            "price_setup" => number_format($price_setup, 2, ",", "."),
        ],
    );

}

logMessage("Completed processing 300-custom-new-order notifications.");

?>