migrate.py 28.7 KB
Newer Older
Skia's avatar
Skia committed
1 2 3 4
import MySQLdb
import os
import django
import random
5
import datetime
Skia's avatar
Skia committed
6
from io import StringIO
7
from pytz import timezone
Skia's avatar
Skia committed
8
from os import listdir
Skia's avatar
Skia committed
9 10

os.environ["DJANGO_SETTINGS_MODULE"] = "sith.settings"
Skia's avatar
Skia committed
11
os.environ['DJANGO_COLORS'] = 'nocolor'
Skia's avatar
Skia committed
12 13 14 15
django.setup()

from django.db import IntegrityError
from django.conf import settings
Skia's avatar
Skia committed
16 17
from django.core.management import call_command
from django.db import connection
18
from django.forms import ValidationError
Skia's avatar
Skia committed
19
from django.core.files import File
Skia's avatar
Skia committed
20

Skia's avatar
Skia committed
21

Skia's avatar
Skia committed
22 23
from core.models import User, SithFile
from club.models import Club, Membership
24
from counter.models import Customer, Counter, Selling, Refilling, Product, ProductType, Permanency
Skia's avatar
Skia committed
25
from subscription.models import Subscription, Subscriber
26
from eboutic.models import Invoice, InvoiceItem
Skia's avatar
Skia committed
27
from accounting.models import BankAccount, ClubAccount, GeneralJournal, Operation, AccountingType, Company, SimplifiedAccountingType
Skia's avatar
Skia committed
28 29 30 31 32 33 34 35

db = MySQLdb.connect(
        host="ae-db",
        user="taiste_rw",
        passwd=input("password: "),
        db="ae2-taiste",
        charset='utf8',
        use_unicode=True)
Skia's avatar
Skia committed
36 37 38 39 40 41

def reset_index(*args):
    sqlcmd = StringIO()
    call_command("sqlsequencereset", *args, stdout=sqlcmd)
    cursor = connection.cursor()
    cursor.execute(sqlcmd.getvalue())
Skia's avatar
Skia committed
42 43 44 45 46 47

def to_unicode(s):
    if s:
        return bytes(s, 'cp1252', errors="replace").decode('utf-8', errors='replace')
    return ""

Skia's avatar
Skia committed
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

def migrate_users():
    SEX = {'1': 'MAN', '2': 'WOMAN', None: 'MAN'}
    TSHIRT = {
            None: '-',
            '': '-',
            'NULL': '-',
            'XS': 'XS',
            'S': 'S',
            'M': 'M',
            'L': 'L',
            'XL': 'XL',
            'XXL': 'XXL',
            'XXXL': 'XXXL',
            }
    ROLE = {
            'doc': 'DOCTOR',
            'etu': 'STUDENT',
            'anc': 'FORMER STUDENT',
            'ens': 'TEACHER',
            'adm': 'ADMINISTRATIVE',
            'srv': 'SERVICE',
            'per': 'AGENT',
            None: '',
            }
    DEPARTMENTS = {
            'tc': 'TC',
            'gi': 'GI',
            'gesc': 'GESC',
            'na': 'NA',
            'mc': 'MC',
            'imap': 'IMAP',
            'huma': 'HUMA',
            'edim': 'EDIM',
            'ee': 'EE',
            'imsi': 'IMSI',
            'truc': 'NA',
            None: 'NA',
            }

    def get_random_free_email():
Skia's avatar
Skia committed
89
        email = "no_email_%s@git.an" % random.randrange(4000, 40000)
Skia's avatar
Skia committed
90 91 92 93
        while User.objects.filter(email=email).exists():
            email = "no_email_%s@git.an" % random.randrange(4000, 40000)
        return email

94
    c = db.cursor(MySQLdb.cursors.SSDictCursor)
Skia's avatar
Skia committed
95 96 97
    c.execute("""
    SELECT *
    FROM utilisateurs utl
Skia's avatar
Skia committed
98
    LEFT JOIN utl_etu ue
Skia's avatar
Skia committed
99
    ON ue.id_utilisateur = utl.id_utilisateur
Skia's avatar
Skia committed
100
    LEFT JOIN utl_etu_utbm ueu
Skia's avatar
Skia committed
101
    ON ueu.id_utilisateur = utl.id_utilisateur
Skia's avatar
Skia committed
102
    LEFT JOIN utl_extra uxtra
Skia's avatar
Skia committed
103
    ON uxtra.id_utilisateur = utl.id_utilisateur
Skia's avatar
Skia committed
104
    LEFT JOIN loc_ville ville
Skia's avatar
Skia committed
105
    ON utl.id_ville = ville.id_ville
106
    -- WHERE utl.id_utilisateur = 9360
Skia's avatar
Skia committed
107 108 109 110
    """)
    User.objects.filter(id__gt=0).delete()
    print("Users deleted")

Skia's avatar
Skia committed
111
    for u in c:
Skia's avatar
Skia committed
112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
        try:
            new = User(
                    id=u['id_utilisateur'],
                    last_name=to_unicode(u['nom_utl']) or "Bou",
                    first_name=to_unicode(u['prenom_utl']) or "Bi",
                    email=u['email_utl'],
                    second_email=u['email_utbm'] or "",
                    date_of_birth=u['date_naissance_utl'],
                    last_update=u['date_maj_utl'],
                    nick_name=to_unicode(u['surnom_utbm']),
                    sex=SEX[u['sexe_utl']],
                    tshirt_size=TSHIRT[u['taille_tshirt_utl']],
                    role=ROLE[u['role_utbm']],
                    department=DEPARTMENTS[u['departement_utbm']],
                    dpt_option=to_unicode(u['filiere_utbm']),
                    semester=u['semestre_utbm'] or 0,
                    quote=to_unicode(u['citation']),
                    school=to_unicode(u['nom_ecole_etudiant']),
                    promo=u['promo_utbm'] or 0,
                    forum_signature=to_unicode(u['signature_utl']),
                    address=(to_unicode(u['addresse_utl']) + ", " + to_unicode(u['cpostal_ville']) + " " + to_unicode(u['nom_ville'])),
                    parent_address=(to_unicode(u['adresse_parents']) + ", " + to_unicode(u['cpostal_parents']) + " " + to_unicode(u['ville_parents'])),
                    phone=u['tel_portable_utl'] or "",
                    parent_phone=u['tel_parents'] or "",
Skia's avatar
Skia committed
136
                    is_subscriber_viewable=bool(u['publique_utl']),
Skia's avatar
Skia committed
137 138
            )
            new.generate_username()
Skia's avatar
Skia committed
139
            new.save()
Skia's avatar
Skia committed
140 141 142 143 144 145 146 147
        except IntegrityError as e:
            if "Key (email)" in repr(e):
                new.email = get_random_free_email()
                new.save()
                print("New email generated")
            else:
                print("FAIL for user %s: %s" % (u['id_utilisateur'], repr(e)))
        except Exception as e:
Skia's avatar
Skia committed
148
            print("FAIL for user %s: %s" % (u['id_utilisateur'], repr(e)))
Skia's avatar
Skia committed
149
    c.close()
Skia's avatar
Skia committed
150

Skia's avatar
Skia committed
151 152
def migrate_profile_pict():
    PROFILE_ROOT = "/data/matmatronch/"
Skia's avatar
Skia committed
153

Skia's avatar
Skia committed
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183
    profile = SithFile.objects.filter(parent=None, name="profiles").first()
    profile.children.all().delete()
    print("Profiles pictures deleted")
    for filename in listdir(PROFILE_ROOT):
        if filename.split('.')[-2] != "mini":
            try:
                uid = filename.split('.')[0].split('-')[0]
                user = User.objects.filter(id=int(uid)).first()
                if user:
                    f = File(open(PROFILE_ROOT + '/' + filename, 'rb'))
                    f.name = f.name.split('/')[-1]
                    t = filename.split('.')[1]
                    new_file = SithFile(parent=profile, name=filename,
                            file=f, owner=user, is_folder=False, mime_type="image/jpeg", size=f.size)
                    if t == "identity":
                        new_file.save()
                        user.profile_pict = new_file
                        user.save()
                    elif t == "blouse":
                        new_file.save()
                        user.scrub_pict = new_file
                        user.save()
                    else:
                        new_file.save()
                        user.avatar_pict = new_file
                        user.save()
            except Exception as e:
                print(repr(e))

def migrate_clubs():
184
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
Skia's avatar
Skia committed
185 186 187 188 189 190 191 192 193 194 195
    cur.execute("""
    SELECT *
    FROM asso asso
    WHERE nom_unix_asso <> "ae"
        AND nom_unix_asso <> "bdf"
        AND nom_unix_asso <> "laverie"
    """)
    # club = cur.fetchone()
    # for k,v in club.items():
    #     print("%40s | %40s" % (k, v))

Skia's avatar
Skia committed
196
    for c in cur:
Skia's avatar
Skia committed
197 198 199 200 201 202 203 204 205 206 207 208 209 210
        try:
            new = Club(
                    id=c['id_asso'],
                    name=to_unicode(c['nom_asso']),
                    unix_name=to_unicode(c['nom_unix_asso']),
                    address=to_unicode(c['adresse_postale']),
                    )
            new.save()
        except Exception as e:
                print("FAIL for club %s: %s" % (c['nom_unix_asso'], repr(e)))
    cur.execute("""
    SELECT *
    FROM asso
    """)
Skia's avatar
Skia committed
211
    for c in cur:
Skia's avatar
Skia committed
212 213 214 215 216 217 218
        club = Club.objects.filter(id=c['id_asso']).first()
        parent = Club.objects.filter(id=c['id_asso_parent']).first()
        club.parent = parent
        club.save()
    cur.close()

def migrate_club_memberships():
219
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
Skia's avatar
Skia committed
220 221 222 223 224 225 226
    cur.execute("""
    SELECT *
    FROM asso_membre
    """)

    Membership.objects.all().delete()
    print("Memberships deleted")
Skia's avatar
Skia committed
227
    for m in cur:
Skia's avatar
Skia committed
228
        try:
Skia's avatar
Skia committed
229 230 231 232 233 234 235 236 237 238 239 240
            club = Club.objects.filter(id=m['id_asso']).first()
            user = User.objects.filter(id=m['id_utilisateur']).first()
            if club and user:
                new = Membership(
                        club=club,
                        user=user,
                        start_date=m['date_debut'],
                        end_date=m['date_fin'],
                        role=m['role'],
                        description=to_unicode(m['desc_role']),
                        )
                new.save()
Skia's avatar
Skia committed
241
        except Exception as e:
Skia's avatar
Skia committed
242 243 244
                print("FAIL for club membership %s: %s" % (m['id_asso'], repr(e)))
    cur.close()

Skia's avatar
Skia committed
245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
def migrate_subscriptions():
    LOCATION = {
            5: "SEVENANS",
            6: "BELFORT",
            9: "MONTBELIARD",
            None: "SEVENANS",
            }
    TYPE = {
            0: 'un-semestre',
            1: 'deux-semestres',
            2: 'cursus-tronc-commun',
            3: 'cursus-branche',
            4: 'membre-honoraire',
            5: 'assidu',
            6: 'amicale/doceo',
            7: 'reseau-ut',
            8: 'crous',
            9: 'sbarro/esta',
            10: 'cursus-alternant',
            None: 'un-semestre',
            }
    PAYMENT = {
            1: "CHECK",
            2: "CARD",
            3: "CASH",
            4: "OTHER",
            5: "EBOUTIC",
            0: "OTHER",
            }
274
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
Skia's avatar
Skia committed
275 276 277 278 279 280 281 282 283
    cur.execute("""
    SELECT *
    FROM ae_cotisations
    """)

    Subscription.objects.all().delete()
    print("Subscriptions deleted")
    Customer.objects.all().delete()
    print("Customers deleted")
Skia's avatar
Skia committed
284
    for r in cur:
Skia's avatar
Skia committed
285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302
        try:
            user = Subscriber.objects.filter(id=r['id_utilisateur']).first()
            if user:
                new = Subscription(
                        id=r['id_cotisation'],
                        member=user,
                        subscription_start=r['date_cotis'],
                        subscription_end=r['date_fin_cotis'],
                        subscription_type=TYPE[r['type_cotis']],
                        payment_method=PAYMENT[r['mode_paiement_cotis']],
                        location=LOCATION[r['id_comptoir']],
                        )
                new.save()
        except Exception as e:
                print("FAIL for subscription %s: %s" % (r['id_cotisation'], repr(e)))
    cur.close()

def update_customer_account():
303
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
Skia's avatar
Skia committed
304 305 306 307 308 309
    cur.execute("""
    SELECT *
    FROM ae_carte carte
    JOIN ae_cotisations cotis
    ON carte.id_cotisation = cotis.id_cotisation
    """)
Skia's avatar
Skia committed
310
    for r in cur:
Skia's avatar
Skia committed
311 312 313 314 315 316 317 318
        try:
            user = Customer.objects.filter(user_id=r['id_utilisateur']).first()
            if user:
                user.account_id = str(r['id_carte_ae']) + r['cle_carteae'].lower()
                user.save()
        except Exception as e:
                print("FAIL to update customer account for %s: %s" % (r['id_cotisation'], repr(e)))
    cur.close()
Skia's avatar
Skia committed
319

320 321 322 323 324 325 326
def migrate_counters():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_comptoir
    """)
    Counter.objects.all().delete()
Skia's avatar
Skia committed
327
    for r in cur:
328 329 330 331 332 333 334 335 336 337 338 339
        try:
            club = Club.objects.filter(id=r['id_assocpt']).first()
            new = Counter(
                    id=r['id_comptoir'],
                    name=to_unicode(r['nom_cpt']),
                    club=club,
                    type="OFFICE",
                    )
            new.save()
        except Exception as e:
                print("FAIL to migrate counter %s: %s" % (r['id_comptoir'], repr(e)))
    cur.close()
Skia's avatar
Skia committed
340 341 342
    eboutic = Counter.objects.filter(id=3).first()
    eboutic.type = "EBOUTIC"
    eboutic.save()
343

Skia's avatar
Skia committed
344 345 346 347 348 349 350 351 352
def reset_customer_amount():
    Refilling.objects.all().delete()
    Selling.objects.all().delete()
    Invoice.objects.all().delete()
    for c in Customer.objects.all():
        c.amount = 0
        c.save()
    print("Customer amount reset")

353 354 355 356 357 358 359 360 361 362 363 364 365 366 367
def migrate_refillings():
    BANK = {
            0: "OTHER",
            1: "SOCIETE-GENERALE",
            2: "BANQUE-POPULAIRE",
            3: "BNP",
            4: "CAISSE-EPARGNE",
            5: "CIC",
            6: "CREDIT-AGRICOLE",
            7: "CREDIT-MUTUEL",
            8: "CREDIT-LYONNAIS",
            9: "LA-POSTE",
            100: "OTHER",
            None: "OTHER",
            }
368 369 370 371 372
    PAYMENT = {
            2: "CARD",
            1: "CASH",
            0: "CHECK",
            }
373 374 375 376 377 378 379
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_rechargements
    """)
    root_cust = Customer.objects.filter(user__id=0).first()
    mde = Counter.objects.filter(id=1).first()
380 381
    Refilling.objects.all().delete()
    print("Refillings deleted")
382
    fail = 100
Skia's avatar
Skia committed
383
    for r in cur:
384 385 386 387 388 389 390 391 392 393 394 395 396 397 398
        try:
            cust = Customer.objects.filter(user__id=r['id_utilisateur']).first()
            user = User.objects.filter(id=r['id_utilisateur']).first()
            if not cust:
                if not user:
                    cust = root_cust
                else:
                    cust = Customer(user=user, amount=0, account_id=Customer.generate_account_id(fail))
                    cust.save()
                    fail += 1
            op = User.objects.filter(id=r['id_utilisateur_operateur']).first()
            counter = Counter.objects.filter(id=r['id_comptoir']).first()
            new = Refilling(
                    id=r['id_rechargement'],
                    counter=counter or mde,
Skia's avatar
Skia committed
399
                    customer=cust or root_cust,
400 401
                    operator=op or root_cust.user,
                    amount=r['montant_rech']/100,
402
                    payment_method=PAYMENT[r['type_paiement_rech']],
403
                    bank=BANK[r['banque_rech']],
404
                    date=r['date_rech'].replace(tzinfo=timezone('Europe/Paris')),
405 406 407 408 409 410 411 412 413 414 415 416 417 418
                    )
            new.save()
        except Exception as e:
                print("FAIL to migrate refilling %s for %s: %s" % (r['id_rechargement'], r['id_utilisateur'], repr(e)))
    cur.close()

def migrate_typeproducts():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_type_produit
    """)
    ProductType.objects.all().delete()
    print("Product types deleted")
Skia's avatar
Skia committed
419
    for r in cur:
420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438
        try:
            new = ProductType(
                    id=r['id_typeprod'],
                    name=to_unicode(r['nom_typeprod']),
                    description=to_unicode(r['description_typeprod']),
                    )
            new.save()
        except Exception as e:
                print("FAIL to migrate product type %s: %s" % (r['nom_typeprod'], repr(e)))
    cur.close()

def migrate_products():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_produits
    """)
    Product.objects.all().delete()
    print("Product deleted")
Skia's avatar
Skia committed
439
    for r in cur:
440 441 442 443 444 445 446 447 448
        try:
            type = ProductType.objects.filter(id=r['id_typeprod']).first()
            club = Club.objects.filter(id=r['id_assocpt']).first()
            new = Product(
                    id=r['id_produit'],
                    product_type=type,
                    name=to_unicode(r['nom_prod']),
                    description=to_unicode(r['description_prod']),
                    code=to_unicode(r['cbarre_prod']),
449 450 451
                    purchase_price=r['prix_achat_prod']/100,
                    selling_price=r['prix_vente_prod']/100,
                    special_selling_price=r['prix_vente_barman_prod']/100,
452
                    club=club,
453 454
                    limit_age=r['mineur'] or 0,
                    tray=bool(r['plateau']),
455 456 457 458 459 460
                    )
            new.save()
        except Exception as e:
                print("FAIL to migrate product %s: %s" % (r['nom_prod'], repr(e)))
    cur.close()

Skia's avatar
Skia committed
461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480
def migrate_product_pict():
    FILE_ROOT = "/data/files/"

    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_produits
    WHERE id_file IS NOT NULL
    """)
    for r in cur:
        try:
            prod = Product.objects.filter(id=r['id_produit']).first()
            if prod:
                f = File(open(FILE_ROOT + '/' + str(r['id_file']) + ".1", 'rb'))
                f.name = prod.name
                prod.icon = f
                prod.save()
        except Exception as e:
            print(repr(e))

481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536
def migrate_products_to_counter():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_mise_en_vente
    """)
    for r in cur:
        try:
            product = Product.objects.filter(id=r['id_produit']).first()
            counter = Counter.objects.filter(id=r['id_comptoir']).first()
            counter.products.add(product)
            counter.save()
        except Exception as e:
                print("FAIL to set product %s in counter %s: %s" % (product, counter, repr(e)))
    cur.close()

def migrate_invoices():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_vendu ven
    LEFT JOIN cpt_debitfacture fac
    ON ven.id_facture = fac.id_facture
    WHERE fac.mode_paiement = 'SG'
    """)
    Invoice.objects.all().delete()
    print("Invoices deleted")
    Refilling.objects.filter(payment_method="CARD").delete()
    print("Card refillings deleted")
    Selling.objects.filter(payment_method="CARD").delete()
    print("Card sellings deleted")
    root = User.objects.filter(id=0).first()
    for r in cur:
        try:
            product = Product.objects.filter(id=r['id_produit']).first()
            user = User.objects.filter(id=r['id_utilisateur_client']).first()
            i = Invoice.objects.filter(id=r['id_facture']).first() or Invoice(id=r['id_facture'])
            i.user = user or root
            for f in i._meta.local_fields:
                if f.name == "date":
                    f.auto_now = False
            i.date = r['date_facture'].replace(tzinfo=timezone('Europe/Paris'))
            i.save()
            InvoiceItem(invoice=i, product_id=product.id, product_name=product.name, type_id=product.product_type.id,
                    product_unit_price=r['prix_unit']/100, quantity=r['quantite']).save()
        except ValidationError as e:
            print(repr(e) + " for %s (%s)" % (customer, customer.user.id))
        except Exception as e:
            print("FAIL to migrate invoice %s: %s" % (r['id_facture'], repr(e)))
    cur.close()
    for i in Invoice.objects.all():
        for f in i._meta.local_fields:
            if f.name == "date":
                f.auto_now = False
        i.validate()

537 538 539 540 541 542 543 544 545
def migrate_sellings():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_vendu ven
    LEFT JOIN cpt_debitfacture fac
    ON ven.id_facture = fac.id_facture
    WHERE fac.mode_paiement = 'AE'
    """)
546 547
    Selling.objects.filter(payment_method="SITH_ACCOUNT").delete()
    print("Sith account selling deleted")
548 549 550 551
    for c in Customer.objects.all():
        c.amount = sum([r.amount for r in c.refillings.all()])
        c.save()
    print("Customer amount reset to sum of refillings")
Skia's avatar
Skia committed
552 553 554 555
    ae = Club.objects.filter(unix_name="ae").first()
    mde = Counter.objects.filter(id=1).first()
    root = User.objects.filter(id=0).first()
    beer = Product.objects.filter(id=1).first()
556 557
    for r in cur:
        try:
Skia's avatar
Skia committed
558 559 560 561 562
            product = Product.objects.filter(id=r['id_produit']).first() or beer
            club = Club.objects.filter(id=r['id_assocpt']).first() or ae
            counter = Counter.objects.filter(id=r['id_comptoir']).first() or mde
            op = User.objects.filter(id=r['id_utilisateur']).first() or root
            customer = Customer.objects.filter(user__id=r['id_utilisateur_client']).first() or root.customer
563
            new = Selling(
Skia's avatar
Skia committed
564
                    label=product.name or "Produit inexistant",
565 566 567 568 569 570 571
                    counter=counter,
                    club=club,
                    product=product,
                    seller=op,
                    customer=customer,
                    unit_price=r['prix_unit']/100,
                    quantity=r['quantite'],
572 573
                    payment_method="SITH_ACCOUNT",
                    date=r['date_facture'].replace(tzinfo=timezone('Europe/Paris')),
574 575 576
                    )
            new.save()
        except ValidationError as e:
Skia's avatar
Skia committed
577
            print(repr(e) + " for %s (%s)" % (customer, customer.user.id))
578 579 580 581
        except Exception as e:
            print("FAIL to migrate selling %s: %s" % (r['id_facture'], repr(e)))
    cur.close()

582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603
def migrate_permanencies():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpt_tracking
    """)
    Permanency.objects.all().delete()
    print("Permanencies deleted")
    for r in cur:
        try:
            counter = Counter.objects.filter(id=r['id_comptoir']).first()
            user = User.objects.filter(id=r['id_utilisateur']).first()
            new = Permanency(
                    user=user,
                    counter=counter,
                    start=r['logged_time'].replace(tzinfo=timezone('Europe/Paris')),
                    end=r['closed_time'].replace(tzinfo=timezone('Europe/Paris')),
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate permanency: %s" % (repr(e)))
    cur.close()
604

Skia's avatar
Skia committed
605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732
### Accounting

def migrate_bank_accounts():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_cpbancaire
    """)
    BankAccount.objects.all().delete()
    print("Bank accounts deleted")
    ae = Club.objects.filter(unix_name='ae').first()
    for r in cur:
        try:
            new = BankAccount(
                    id=r['id_cptbc'],
                    club=ae,
                    name=to_unicode(r['nom_cptbc']),
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate bank account: %s" % (repr(e)))
    cur.close()

def migrate_club_accounts():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_cpasso
    """)
    ClubAccount.objects.all().delete()
    print("Club accounts deleted")
    ae = Club.objects.filter(id=1).first()
    for r in cur:
        try:
            club = Club.objects.filter(id=r['id_asso']).first() or ae
            bank_acc = BankAccount.objects.filter(id=r['id_cptbc']).first()
            new = ClubAccount(
                    id=r['id_cptasso'],
                    club=club,
                    name=club.name[:30],
                    bank_account=bank_acc,
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate club account: %s" % (repr(e)))
    cur.close()

def migrate_journals():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_classeur
    """)
    GeneralJournal.objects.all().delete()
    print("General journals deleted")
    for r in cur:
        try:
            club_acc = ClubAccount.objects.filter(id=r['id_cptasso']).first()
            new = GeneralJournal(
                    id=r['id_classeur'],
                    club_account=club_acc,
                    name=to_unicode(r['nom_classeur']),
                    start_date=r['date_debut_classeur'],
                    end_date=r['date_fin_classeur'],
                    closed=bool(r['ferme']),
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate general journal: %s" % (repr(e)))
    cur.close()

def migrate_accounting_types():
    MOVEMENT = {
            -1: "DEBIT",
            0: "NEUTRAL",
            1: "CREDIT",
            }
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_op_plcptl
    """)
    AccountingType.objects.all().delete()
    print("Accounting types deleted")
    for r in cur:
        try:
            new = AccountingType(
                    id=r['id_opstd'],
                    code=str(r['code_plan']),
                    label=to_unicode(r['libelle_plan']).capitalize(),
                    movement_type=MOVEMENT[r['type_mouvement']],
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate accounting type: %s" % (repr(e)))
    cur.close()

def migrate_simpleaccounting_types():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_op_clb
    WHERE id_asso IS NULL
    """)
    SimplifiedAccountingType.objects.all().delete()
    print("Simple accounting types deleted")
    for r in cur:
        try:
            at = AccountingType.objects.filter(id=r['id_opstd']).first()
            new = SimplifiedAccountingType(
                    id=r['id_opclb'],
                    label=to_unicode(r['libelle_opclb']).capitalize(),
                    accounting_type=at,
                    )
            new.save()
        except Exception as e:
            print("FAIL to migrate simple type: %s" % (repr(e)))
    cur.close()

def migrate_operations():
    MODE = {
            1: "CHECK",
            2: "CASH",
            3: "TRANSFERT",
            4: "CARD",
            0: "CASH",
            None: "CASH",
            }
Skia's avatar
Skia committed
733 734 735 736 737 738
    MOVEMENT_TYPE = {
            -1: "DEBIT",
            0: "NEUTRAL",
            1: "CREDIT",
            None: "NEUTRAL",
            }
Skia's avatar
Skia committed
739 740 741
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
Skia's avatar
Skia committed
742 743 744
    FROM cpta_operation op
    LEFT JOIN cpta_op_clb clb
    ON op.id_opclb = clb.id_opclb
Skia's avatar
Skia committed
745 746 747 748 749 750 751 752 753 754 755 756 757 758
    """)
    Operation.objects.all().delete()
    print("Operation deleted")
    for r in cur:
        try:
            simple_type = None
            accounting_type = None
            if r['id_opclb']:
                simple_type = SimplifiedAccountingType.objects.filter(id=r['id_opclb']).first()
            if r['id_opstd']:
                accounting_type = AccountingType.objects.filter(id=r['id_opstd']).first()
            if not accounting_type and simple_type:
                accounting_type = simple_type.accounting_type
            if not accounting_type:
Skia's avatar
Skia committed
759
                accounting_type = AccountingType.objects.filter(movement_type=MOVEMENT_TYPE[r['type_mouvement']]).first()
Skia's avatar
Skia committed
760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815
            journal = GeneralJournal.objects.filter(id=r['id_classeur']).first()
            def get_target_type():
                if r['id_utilisateur']:
                    return "USER"
                if r['id_asso']:
                    return "CLUB"
                if r['id_ent']:
                    return "COMPANY"
                if r['id_classeur']:
                    return "ACCOUNT"
            def get_target_id():
                return int(r['id_utilisateur'] or r['id_asso'] or r['id_ent'] or r['id_classeur']) or None
            new = Operation(
                    id=r['id_op'],
                    journal=journal,
                    amount=r['montant_op']/100,
                    date=r['date_op'] or journal.end_date,
                    remark=to_unicode(r['commentaire_op']),
                    mode=MODE[r['mode_op']],
                    cheque_number=str(r['num_cheque_op']),
                    done=bool(r['op_effctue']),
                    simpleaccounting_type=simple_type,
                    accounting_type=accounting_type,
                    target_type=get_target_type(),
                    target_id=get_target_id(),
                    target_label="-",
                    )
            try:
                new.clean()
            except:
                new.target_id = get_target_id()
                new.target_type = "OTHER"
            new.save()
        except Exception as e:
            print("FAIL to migrate operation: %s" % (repr(e)))
    cur.close()

def make_operation_links():
    cur = db.cursor(MySQLdb.cursors.SSDictCursor)
    cur.execute("""
    SELECT *
    FROM cpta_operation
    """)
    for r in cur:
        if r['id_op_liee']:
            try:
                op1 = Operation.objects.filter(id=r['id_op']).first()
                op2 = Operation.objects.filter(id=r['id_op_liee']).first()
                op1.linked_operation = op2
                op1.save()
                op2.linked_operation = op1
                op2.save()
            except Exception as e:
                print("FAIL to link operations: %s" % (repr(e)))
    cur.close()

Skia's avatar
Skia committed
816
def main():
Skia's avatar
Skia committed
817 818
    start = datetime.datetime.now()
    print("Start at %s" % start)
Skia's avatar
Skia committed
819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846
    # Core
    migrate_users()
    migrate_profile_pict()
    # Club
    migrate_clubs()
    migrate_club_memberships()
    # Subscriptions
    migrate_subscriptions()
    # Counters
    update_customer_account()
    migrate_counters()
    migrate_permanencies()
    migrate_typeproducts()
    migrate_products()
    migrate_product_pict()
    migrate_products_to_counter()
    reset_customer_amount()
    migrate_invoices()
    reset_index('counter')
    migrate_refillings()
    migrate_sellings()
    # Accounting
    migrate_accounting_types()
    migrate_simpleaccounting_types()
    migrate_bank_accounts()
    migrate_club_accounts()
    migrate_journals()
    migrate_operations()
Skia's avatar
Skia committed
847
    make_operation_links()
Skia's avatar
Skia committed
848
    reset_index('core', 'club', 'subscription', 'accounting', 'eboutic', 'launderette', 'counter')
Skia's avatar
Skia committed
849 850 851 852
    end = datetime.datetime.now()
    print("End at %s" % end)
    print("Running time: %s" % (end-start))

Skia's avatar
Skia committed
853 854
if __name__ == "__main__":
    main()