85 lines
2.9 KiB
PHP
85 lines
2.9 KiB
PHP
<?php
|
|
|
|
global $db;
|
|
require_once 'init.php';
|
|
|
|
$sql = "WITH first_status_300_plus AS (
|
|
SELECT
|
|
ph.preorder_id,
|
|
MIN(ph.create) as first_status_300_timestamp
|
|
FROM PreorderHistory ph
|
|
INNER JOIN Preorderstatus ps ON CAST(ph.new_value AS UNSIGNED) = ps.id
|
|
WHERE ph.key = 'status_id'
|
|
AND ps.code >= 300
|
|
AND ph.create <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 WEEK))
|
|
GROUP BY ph.preorder_id
|
|
)
|
|
SELECT
|
|
p.id,
|
|
p.type,
|
|
p.preordercampaign_id,
|
|
pc.name as campaign_name,
|
|
ps_current.code as current_status_code,
|
|
ps_current.name as current_status_name,
|
|
FROM_UNIXTIME(fs.first_status_300_timestamp) as first_reached_300_plus,
|
|
DATEDIFF(NOW(), FROM_UNIXTIME(fs.first_status_300_timestamp)) as days_since_first_300,
|
|
pcsmt.mailtemplate_id,
|
|
pcsmt.logical_config
|
|
FROM Preorder p
|
|
INNER JOIN Preordercampaign pc ON p.preordercampaign_id = pc.id
|
|
INNER JOIN PreordercampaignStatusnotificationMailtemplate pcsmt ON pc.id = pcsmt.preordercampaign_id
|
|
INNER JOIN `" . ADDRESSDB_DBNAME . "`.Hausnummer hn ON p.adb_wohneinheit_id = hn.id
|
|
INNER JOIN Preorderstatus ps_current ON p.status_id = ps_current.id
|
|
INNER JOIN first_status_300_plus fs ON p.id = fs.preorder_id
|
|
LEFT JOIN PreorderStatusnotificationLog psnl ON p.id = psnl.preorder_id AND psnl.email_type = '300-custom'
|
|
WHERE p.type = 'order'
|
|
AND ps_current.code >= 300
|
|
AND ps_current.code < 500
|
|
AND hn.id IS NOT NULL
|
|
AND hn.tool_building_type = 1 -- Only consider residential buildings
|
|
AND pcsmt.notification_type = 'logical'
|
|
AND JSON_EXTRACT(pcsmt.logical_config, '$.type') = '300-custom'
|
|
AND psnl.id IS NULL -- No existing '300-custom' notification
|
|
ORDER BY fs.first_status_300_timestamp ASC;";
|
|
|
|
$res = $db->query($sql);
|
|
|
|
if ($res->num_rows == 0) {
|
|
logMessage("No preorders found for custom 300 notification.");
|
|
exit;
|
|
}
|
|
|
|
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'];
|
|
|
|
$preorder = new Preorder($preorder_id);
|
|
if (!$preorder || !$preorder->id) {
|
|
logMessage("Preorder with ID $preorder_id not found. Skipping.");
|
|
continue;
|
|
}
|
|
|
|
$preordercampaign = new Preordercampaign($preorder->preordercampaign_id);
|
|
if (!$preordercampaign || !$preordercampaign->id) {
|
|
logMessage("Preordercampaign with ID {$preorder->preordercampaign_id} not found. Skipping.");
|
|
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");
|
|
|
|
sendPreorderEmail(
|
|
$mailtemplate,
|
|
$preordercampaign,
|
|
$preorder,
|
|
"300-custom"
|
|
);
|
|
|
|
} |