Das habe ich jetzt auf die Schnelle zusammengehaun. Da sind sicher einige Fehler drin, weil ich die OIriginaltabellen nicht kenne:
Das CASE-Statement muss ich mir noch mal genauer ansehen.
So einen komplexen Fall hatte ich aber in der Tat noch nicht. Ich sehe aber auch einige Fälle, die man drastisch vereinfachen kann. Beispielsweise gibt du beim UnionStatement -1 oder 1 als Typ zurück. Würdest du 0 und 1 zurückgeben, würdest du aus:
machen können.
Oder aus
eher
Wahrscheinlich kann man das noch weiter vereinfachen. Naja.
PHP-Code:
$startDate = null;
$endDate = null;
$articleId = null;
$tourIds = $select->select()
->field('tour_id')
->from('kv2', 'wh_kit_version');
$maxVersion = $select->select()
->field('MAX(version)')
->from('ttour', 'wh_transaction_tour')
->where('kv2.kit_id = k1.kit_id');
$toursRange2 = $select->select()
->field('tour1.tour_id')
->field('(-1)', 'act_type')
->from('tour1', 'tour')
->where('tour1.tour_begin >= ?', $startDate)
->where('tour1.tour_begin <= ?', $endDate);
$toursRange = $select->select()
->field('tour1.tour_id')
->field('(1)', 'act_type')
->from('tour1', 'tour')
->where('DATE_ADD(tour_begin, INTERVAL 7 DAY) >= ?', $startDate)
->where('DATE_ADD(tour_begin, INTERVAL 7 DAY) <= ?', $endDate)
->unionAll($toursRange2);
$inner = $select->select()
->field('DATE_ADD(DATE(act_begin), INTERVAL IF(tour.act_type = -1, 1, 0) WEEK)', 'act_date')
->field('CASE WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END) ELSE CASE WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1 ELSE MAX(ka1.amount) END END', 'amount')
->from('tour', $toursRange)
->joinInner('a', 'activity', 'tour.tour_id = a.tour_id')
->joinInner('ao', 'activity_operation', 'tour.activity_id = a.activity_id')
->joinInner('k1', 'wh_kit', 'tour.operation_id = k1.operation_id')
->joinInner('kv1', 'wh_kit_version', 'tour.kit_id = kv1kit_id')
->joinInner('ka1', 'wh_kit_article', 'k1.kit_id = ka1.kit_id AND ka1.version_id = kv1.version_id')
->joinInner('a1', 'wh_article', 'a1.article_id = ka1.article_id')
->joinInner('ag1', 'wh_article_group', 'ag1.article_group_id = a1.article_group_id')
->where('tour.tour_id IN (?)', $tourIds)
->where('kv1.version = ?', $maxVersion)
->where('a1.article_id = ?', $articleId)
->groupBy('tour.tour_id', 'tour.act_date', 'tour.act_type', 'a1.article_id', 'ka1.amount', 'ka1.aggregate');
echo $select->select()
->field('t.article_id', 'articleId')
->field('SUM(t.amount)', 'amount')
->field('t.act_date', 'act_date')
->from('t', $inner)
->groupBy('t.article_id', 't.act_date');
PHP-Code:
SELECT
t.article_id AS `articleId`,
SUM(t.amount) AS `amount`,
t.act_date AS `act_date`
FROM
(SELECT
DATE_ADD(DATE(act_begin), INTERVAL IF(tour.act_type = -1, 1, 0) WEEK) AS `act_date`,
CASE WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END) ELSE CASE WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1 ELSE MAX(ka1.amount) END END AS `amount`
FROM
(SELECT
tour1.tour_id,
(1) AS `act_type`
FROM
tour tour1
WHERE
(DATE_ADD(tour_begin, INTERVAL 7 DAY) >= NULL)
AND
(DATE_ADD(tour_begin, INTERVAL 7 DAY) <= NULL)
UNION ALL
SELECT
tour1.tour_id,
(-1) AS `act_type`
FROM
tour tour1
WHERE
(tour1.tour_begin >= NULL)
AND
(tour1.tour_begin <= NULL)) tour
INNER JOIN
activity a ON tour.tour_id = a.tour_id
INNER JOIN
activity_operation ao ON tour.activity_id = a.activity_id
INNER JOIN
wh_kit k1 ON tour.operation_id = k1.operation_id
INNER JOIN
wh_kit_version kv1 ON tour.kit_id = kv1kit_id
INNER JOIN
wh_kit_article ka1 ON k1.kit_id = ka1.kit_id AND ka1.version_id = kv1.version_id
INNER JOIN
wh_article a1 ON a1.article_id = ka1.article_id
INNER JOIN
wh_article_group ag1 ON ag1.article_group_id = a1.article_group_id
WHERE
(tour.tour_id IN ((SELECT
tour_id
FROM
wh_kit_version kv2
)))
AND
(kv1.version = (SELECT
MAX(version)
FROM
wh_transaction_tour ttour
WHERE
(kv2.kit_id = k1.kit_id)
))
AND
(a1.article_id = NULL)
GROUP BY
tour.tour_id,
tour.act_date,
tour.act_type,
a1.article_id,
ka1.amount,
ka1.aggregate) t
GROUP BY
t.article_id,
t.act_date
PHP-Code:
CASE WHEN type = -1 THEN DATE_ADD(DATE(act_begin), INTERVAL 1 WEEK)
ELSE DATE(act_begin)
END
PHP-Code:
DATE_ADD(DATE(act_begin), INTERVAL tour.act_type WEEK)
Oder aus
PHP-Code:
CASE
WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = -1 THEN CASE WHEN ag1.ag_consumable = 0 THEN ka1.amount * -1 END ELSE ka1.amount END)
ELSE
CASE
WHEN tour.act_type = -1 THEN MAX(ka1.amount) * -1
ELSE MAX(ka1.amount) END
END
PHP-Code:
CASE
WHEN ka1.aggregate = 1 THEN SUM(CASE WHEN tour.act_type = 0 AND ag1.ag_consumable = 0 THEN ka1.amount * -1 ELSE ka1.amount END)
ELSE MAX(ka1.amount) * (tour.act_type - 1)
END
Kommentar