Versions Compared

Old Version 7

changes.mady.by.user Santtu "Japsu" Pajukanta

Saved on

compared with

New Version Current

changes.mady.by.user Santtu "Japsu" Pajukanta

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

Lippukauppa ja e-liput

Montako e-lipputilausta on lunastettu tiettynä päivänä

...

Code Block
languagesql
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;

Tilauksia, joissa yli 4 kpl viikonloppulippuja (Tracon 2018 event_id/product_id:t)

Code Block
languagesql
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;


Minkä kokoisia tilauksia ihmiset tilaa

Code Block
languagepy
titlepython manage.py shell
In [6]: from tickets.models import OrderProduct
from collections import Counter
  
ops = OrderProduct.objects.filter(
   ...:     order__event__slug='tracon2017tracon2018',
   ...:     order__confirm_time__isnull=False,
   ...:     order__payment_date__isnull=False,
   ...:     order__cancellation_time__isnull=True,
   ...:     count__gte=1,
   ...:     product__name__icontains='viikonloppu',
   ...: )
In [7]: ops.count()
Out[7]: 2170
In [8]: c = Counter()
In [9]: for op in ops:
   ...:     c[op.count] += 1
   ...:
In [10]: c
Out[10]:c
# Counter({1: 11711296, 2: 665631, 3: 211199, 4: 7663, 5: 3633, 6: 67, 7: 32, 8: 1, 109: 13})

 

Ohjelmanumerot, joista puuttuu ohjelmanjärjestäjän sähköpostiosoite

Siirrä valittujen tilausten majoittujat toiseen kouluun

Code Block
languagepy
titlepython manage.py shell
from programmetickets.models import Programme*

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

Listaa kaikki taulut, joilla on vierasavain core_personiin

 

# order_ids = {101375, 101577, 101577, 101696, 101696, 101724, 101839, 101879, 101903, 102041, 102054, 102078, 102078, 102086, 102094, 102094, 102094, 102094, 102228, 102228, 102228, 102269, 102272, 102464, 102464, 102464, 102467, 102504, 102591, 102596, 102596, 102596, 102835, 102835, 102917, 103034, 103412, 103509, 103677, 103701, 103724, 103900, 104173}
order_ids = {104958}

product_map = {
    173: 175,
    174: 176,
}

lgroup_map = {
    135: 137,
    136: 138,
}

# Due to braindamage, shop leaves ops with count=0 lying around. This will cause IntegrityError when updating product of ops. So clean up.
# OrderProduct.objects.filter(order__confirm_time__isnull=False, count=0).delete()

# Swap products in orders
for old_product_id, new_product_id in product_map.items():
    old_product = Product.objects.get(id=old_product_id)
    print(old_product)
    ops = OrderProduct.objects.filter(order__in=order_ids, product=old_product_id, count__gt=0)
    print(ops.count())

    # Uncomment this to actually move
    # ops.update(product=new_product_id)

# Update references in AccommodationInformation
for old_lg_id, new_lg_id in lgroup_map.items():
    old_lg = LimitGroup.objects.get(id=old_lg_id)
    new_lg = LimitGroup.objects.get(id=new_lg_id)
    ais = AccommodationInformation.objects.filter(order_product__order__in=order_ids, limit_groups=old_lg_id)

    print(old_lg)
    for ai in ais:
        print(ai)

        # Uncomment these to actually move
        # ai.limit_groups.remove(old_lg)
        # ai.limit_groups.add(new_lg)


Badget ja nimilistat

Kuinka monta badgea on noudettu milläkin tunnilla

Code Block
languagesql
titlepsql
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;

Ohjelmanhallinta 

Ohjelmanumerot, joista puuttuu ohjelmanjärjestäjän sähköpostiosoite

Code Block
languagepy
titlepython manage.py shell
from programme.models import Programme
for programme in Programme.objects.filter(category__event__slug='traconx', programmerole__person__email='').distinct():
    print programme.title

Yleiset

Listaa kaikki taulut, joilla on vierasavain core_personiin

StackOverflow

Code Block
languagesql
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';
Code Block
languagesql
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';
                         constraint_name                         |                table_name                | column_name | foreign_table_name | foreign_column_name
-----------------------------------------------------------------+------------------------------------------+-------------+--------------------+---------------------
 access_emailalias_person_id_d36631ab_fk_core_person_id          | access_emailalias                        | person_id   | core_person        | id
 access_grantedprivi_person_id_9ef28127a5df59f_fk_core_person_id | access_grantedprivilege                  | person_id   | core_person        | id
 access_smtppassword_person_id_89672f1a_fk_core_person_id        | access_smtppassword                      | person_id   | core_person        | id
 badges_badge_person_id_4cca74fd_fk_core_person_id               | badges_badge                             | person_id   | core_person        | id
 desucon2016_signupextrav2_person_id_29c53c5d_fk_core_person_id  | desucon2016_signupextrav2                | person_id   | core_person        | id
 desucon2017_signupextra_person_id_6c671a8f_fk_core_person_id    | desucon2017_signupextra                  | person_id   | core_person        | id
 desuprofile_integr_person_id_37c6ed018d41d481_fk_core_person_id | desuprofile_integration_confirmationcode | person_id   | core_person        | id
 enrollment_enrollment_person_id_7567f500_fk_core_person_id      | enrollment_enrollment                    | person_id   | core_person        | id
 event_log_entry_person_id_b043f99f_fk_core_person_id            | event_log_entry                          | person_id   | core_person        | id
 frostbite2017_signupextra_person_id_13d02157_fk_core_person_id  | frostbite2017_signupextra                | person_id   | core_person        | id
 frostbite2018_signupextra_person_id_1962eecb_fk_core_person_id  | frostbite2018_signupextra                | person_id   | core_person        | id
 kawacon2017_signupextra_person_id_2b2304ea_fk_core_person_id    | kawacon2017_signupextra                  | person_id   | core_person        | id
 kuplii2017_signupextra_person_id_b9148d52_fk_core_person_id     | kuplii2017_signupextra                   | person_id   | core_person        | id
 kuplii2018_signupextra_person_id_e53a0420_fk_core_person_id     | kuplii2018_signupextra                   | person_id   | core_person        | id
 labour_emptysignupextra_person_id_1927c3e9_fk_core_person_id    | labour_emptysignupextra                  | person_id   | core_person        | id
 labour_signup_person_id_69459f4c_fk_core_person_id              | labour_signup                            | person_id   | core_person        | id
 labour_surveyrecord_person_id_796a7ef7_fk_core_person_id        | labour_surveyrecord                      | person_id   | core_person        | id
 membership_members_person_id_6f2156d9e989a345_fk_core_person_id | membership_membership                    | person_id   | core_person        | id
 nippori2017_signupextra_person_id_d78ca762_fk_core_person_id    | nippori2017_signupextra                  | person_id   | core_person        | id
 person_id_refs_id_1b14485d                                      | labour_personqualification               | person_id   | core_person        | id
 person_id_refs_id_5fd30fad                                      | core_passwordresettoken                  | person_id   | core_person        | id
 person_id_refs_id_6aca4060                                      | mailings_personmessage                   | person_id   | core_person        | id
 person_id_refs_id_e80a198a                                      | core_emailverificationtoken              | person_id   | core_person        | id
 popcult2017_signupextra_person_id_a6b1ea92_fk_core_person_id    | popcult2017_signupextra                  | person_id   | core_person        | id
 programme_programmefeedbac_author_id_2b02e587_fk_core_person_id | programme_programmefeedback              | author_id   | core_person        | id
 programme_programmerole_person_id_c6c414c6_fk_core_person_id    | programme_programmerole                  | person_id   | core_person        | id
 shippocon2016_signupextra_person_id_19fe9525_fk_core_person_id  | shippocon2016_signupextra                | person_id   | core_person        | id
 tracon11_signupextrav2_person_id_715ff1f2_fk_core_person_id     | tracon11_signupextrav2                   | person_id   | core_person        | id
 tracon2017_signupextra_person_id_c74ce6f6_fk_core_person_id     | tracon2017_signupextra                   | person_id   | core_person        | id
 tracon2018_signupextra_person_id_907feec9_fk_core_person_id     | tracon2018_signupextra                   | person_id   | core_person        | id
 tylycon2017_signupextra_person_id_cde12b10_fk_core_person_id    | tylycon2017_signupextra                  | person_id   | core_person        | id
 worldcon75_signupextra_person_id_36c0d651_fk_core_person_id     | worldcon75_signupextra                   | person_id   | core_person        | id
 yukicon2017_signupextra_person_id_73d7b8f4_fk_core_person_id    | yukicon2017_signupextra                  | person_id   | core_person        | id
 yukicon2018_signupextra_person_id_17b24a02_fk_core_person_id    | yukicon2018_signupextra                  | person_id   | core_person        | id
 intra_teammember_person_id_d1a2a240_fk_core_person_id           | intra_teammember                         | person_id   | core_person        | id
(35 rows)