SQL Queries
User statistics Marketing planner
select `user`.`username` as 'Nutzername',
if(`user`.`usergroup` like '%5%', 'ja', '-') as 'Administrator',
`bank`.`title` as 'Bank',
`bank`.`bank_code` as 'BLZ'
from `fe_users` `user`
join `tx_vrdatamanagement_bank` `bank` on `bank`.`uid` = `user`.`bank`
where `user`.`pid` = 107
and not `user`.`username` like '%@bynary%'
and not `user`.`username` like '%@fullhaus%'
and `user`.`usergroup` like '%4%'
and `user`.`disable` = 0
and `user`.`deleted` = 0
order by `bank`.`title`;
select `title` as 'Bank',
`bank_code` as 'BLZ'
from `tx_vrdatamanagement_bank`
where `hidden` = 0
and `deleted` = 0
order by `title`;
Get subscriptions for customer magazine
select (select `internal_id` from `tx_vrdatamanagement_bank` where `uid` = `sub`.`bank`) as 'Bank (interne ID)',
(select `title` from `tx_vrdatamanagement_bank` where `uid` = `sub`.`bank`) as 'Bank',
(select `name` from `fe_users` where `uid` = `sub`.`updated_by`) as 'Zuletzt aktualisiert von',
from_unixtime(`sub`.`updated_at`, '%d.%m.%Y') as 'Zuletzt aktualisiert am',
from_unixtime(`sub`.`tstamp`, '%d.%m.%Y') as 'Zuletzt geändert am',
from_unixtime(`sub`.`crdate`, '%d.%m.%Y') as 'Hinzugefügt am',
`tvdmp`.`title` as 'Produkt',
`tvdms`.`quantity` as 'Menge'
from `tx_vrmarketingplanner_domain_model_subscription` `sub`
join tx_vrmarketingplanner_domain_model_product tvdmp on sub.product = tvdmp.uid
join `tx_vrmarketingplanner_domain_model_subscriptionitem` `tvdms` on `sub`.`uid` = `tvdms`.`parent`
where `sub`.`product` in (23, 31)
and not `sub`.`status` = 20
order by `tvdms`.`uid` desc;
Get full list for subscription import command
select `bank`.`uid` as 'Bank (TYPO3 ID)',
`bank`.`deleted` as 'Bank gelöscht',
`bank`.`internal_id` as 'Bank (interne ID)',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 10
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 10
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
limit 1), 0)) as 'Plakat A1',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 9
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 9
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
limit 1), 0)) as 'Plakat A2',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 29),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 29
limit 1), 0)) as 'Fensterstreifen',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 23),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 23
limit 1), 0)) as 'Kundenmagazin neutral',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 31),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 31
limit 1), 0)) as 'Kundenmagazin individualisiert',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 25),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 25
limit 1), 0)) as 'X-Banner Set',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 30),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 30
limit 1), 0)) as 'X-Banner Austauschbanner',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 8),
(select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 8
limit 1), 0)) as 'Aufkleber Geldautomat'
from `tx_vrdatamanagement_bank` as `bank`
order by `bank`.`internal_id`;
Get user edited list for subscription import command
select `bank`.`uid` as 'Bank (TYPO3 ID)',
`bank`.`deleted` as 'Bank gelöscht',
`bank`.`internal_id` as 'Bank (interne ID)',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 10
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
and not `t2`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 10
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
limit 1), 0)) as 'Plakat A1',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2` on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 9
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
and not `t2`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t1`.`variant` = 9
and `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 15
limit 1), 0)) as 'Plakat A2',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 29
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 29
limit 1), 0)) as 'Fensterstreifen',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 23
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 23
limit 1), 0)) as 'Kundenmagazin neutral',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 31
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 31
limit 1), 0)) as 'Kundenmagazin individualisiert',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 25
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 25
limit 1), 0)) as 'X-Banner Set',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 30
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 30
limit 1), 0)) as 'X-Banner Austauschbanner',
(if((select count(*)
from `tx_vrmarketingplanner_domain_model_subscription` as `t1`
where `t1`.`bank` = `bank`.`uid`
and `t1`.`product` = 8
and not `t1`.`updated_by` = 0), (select `t1`.`quantity`
from `tx_vrmarketingplanner_domain_model_subscriptionitem` as `t1`
join `tx_vrmarketingplanner_domain_model_subscription` as `t2`
on `t2`.`uid` = `t1`.`parent`
where `t2`.`bank` = `bank`.`uid`
and `t2`.`product` = 8
limit 1), 0)) as 'Aufkleber Geldautomat'
from `tx_vrdatamanagement_bank` as `bank`
order by `bank`.`internal_id`;
Unsubscribe
# `product` requires update according to product you're about to cancel subscriptions for
update `tx_vrmarketingplanner_domain_model_subscription`
set `status` = 20,
`updated_at` = unix_timestamp(),
`updated_by` = 0,
`cancelled_at` = unix_timestamp(),
`cancelled_by` = 0
where `product` in (23, 31)
and `status` in (0, 10);
update `tx_vrmarketingplanner_domain_model_subscriptionshipment`
set `status`=30,
`updated_at` = unix_timestamp(),
`updated_by` = 0
where `subscription` in
(select GROUP_CONCAT(`uid`) from `tx_vrmarketingplanner_domain_model_subscription` where `product` in (23, 31))
and `status` in (0, 10);