I have a problem with my CASE part of my script (third $query). I'm creating filter which filters an interval between shifts. I don't know wheres the problem. What am I doing wrong?
} elseif ($this->shift == DeliveryItem::SECOND_SHIFT) { //druga zmiana
$query->andFilterWhere(['>=', "(cast(to_char(".Production::tableName().'."created_at"'.", 'HH') as int))" , DeliveryItem::SECOND_SHIFT_START_HOUR]);
$query->andFilterWhere(['<', "(cast(to_char(".Production::tableName().'."created_at"'.", 'HH') as int))" , DeliveryItem::SECOND_SHIFT_END_HOUR]);
$query->andFilterWhere(['<=', "
(
CASE
cast(to_char(".Production::tableName().'."created_at"'.", 'HH') as int)
WHEN
".DeliveryItem::SECOND_SHIFT_END_HOUR."
THEN (cast(to_char(".Production::tableName().'."created_at"'.", 'MI') as int))
ELSE -1
)
"
, DeliveryItem::SECOND_SHIFT_END_MINUTE]);
I have tried also in another way like:
$query->andWhere("
CASE
cast(to_char(".Production::tableName().'."created_at"'.", 'HH') as int)
WHEN
".DeliveryItem::SECOND_SHIFT_END_HOUR."
THEN
((cast(to_char(".Production::tableName().'."created_at"'.", 'MI') as int)) <= ".DeliveryItem::SECOND_SHIFT_END_MINUTE. ")");
It should work like: If rows created_at belongs to an interval and DeliveryItem::SECOND_SHIFT_END_HOUR
== rows created_at hour, then compare DeliveryItem::SECOND_SHIFT_END_MINUTE
with rows_created_at minute and filter it.
Regards