from lippukala.models import Order
from dateutil.tz import tzlocal
from datetime import datetime
 
tz = tzlocal()
loerdag_start = datetime(2015, 9, 5, 0, 0, 0, tzinfo=tz)
soendag_start = datetime(2015, 9, 6, 0, 0, 0, tzinfo=tz)

Order.objects.filter(code__used_on__gte=loerdag_start, code__used_on__lt=soendag_start).distinct().count()
# 2600 

SELECT date_trunc('minutes', used_on) as ts, count(order_id) FROM lippukala_code 
WHERE used_on between '2015-09-05 00:07:00'::timestamp AND '2015-09-05 14:00:00'::timestamp 
GROUP BY ts ORDER BY ts;

SELECT date_trunc('minutes', used_on) as ts, count(*) FROM lippukala_order o, lippukala_code c
WHERE o.id=c.id AND c.used_on between '2015-09-05 00:07:00'::timestamp AND '2015-09-05 14:00:00'::timestamp 
GROUP BY ts ORDER BY ts;

SELECT ti_o.id,ti_op.count AS _cnt FROM tickets_order AS ti_o, tickets_orderproduct AS ti_op 
WHERE ti_o.id=ti_op.order_id AND ti_o.event_id=49 AND ti_o.payment_date is not null
GROUP BY ti_o.id, ti_op.count, ti_op.product_id
HAVING (ti_op.product_id=130 AND ti_op.count > 4)
ORDER BY _cnt DESC;

from tickets.models import OrderProduct
from collections import Counter
  
ops = OrderProduct.objects.filter(
    order__event__slug='tracon2018',
    order__confirm_time__isnull=False,
    order__payment_date__isnull=False,
    order__cancellation_time__isnull=True,
    count__gte=1,
    product__name__icontains='viikonloppu',
)
c = Counter()
for op in ops:
   c[op.count] += 1
c
# Counter({1: 1296, 2: 631, 3: 199, 4: 63, 5: 33, 6: 7, 7: 2, 8: 1, 9: 3})

select date_trunc('hour', used_on) as hour, count(*)
from lippukala_code c
inner join lippukala_order o on (c.order_id = o.id)
where o.event = 'tracon2018'
group by hour
order by hour;

from programme.models import Programme
for programme in Programme.objects.filter(category__event__slug='traconx', programmerole__person__email='').distinct():
    print programme.title

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'core_person';