Script to export all client and member data in printable format

[lamontfr]

Since data was converted from Feast to Souschef in January 2017, the Roulant staff have made changes to the Feast database that are not present in Souschef. In order to identify the differences, a printable list of client and member data is required. This will allow volunteers to flag differences and later manually enter the changes in Souschef just before going in Production.

[lamontfr]

@kousu @JeanFrancoisFournier I wrote the script (file clientcsv.py) below to export files clientsinfo.csv and membersinfo.csv. I executed it in the Test environment to check that it works. Nick, please ask Jean-Francois when he wants to run the script in Production.

"""
script to export SousChef client (active, paused, pending) info
  and member info as two separate .csv files

by @lamontfr - Francois R Lamontagne - 2017-10-05

(Usage)

(1 using ftp, upload file clientscsv.py into sous-chef/src)

(2 copy file clientscsv.py into container code/src)
cd sous-chef/src
docker cp clientscsv.py souschef_web_1:code/src/clientscsv.py

(3 to generate clientsinfo.csv and membersinfo.csv)
docker-compose exec web bash
cd src
python3 manage.py shell --command="from clientscsv import go; go()"
(wait a few minutes)
exit

(4 to get .csv files)
docker cp souschef_web_1:code/src/clientsinfo.csv clientsinfo.csv
docker cp souschef_web_1:code/src/membersinfo.csv membersinfo.csv

(5 using ftp, download files clientsinfo.csv and membersinfo.csv)

"""

import csv
from member.models import (Member, Client, Client_avoid_ingredient,
                           Restriction, Relationship, DAYS_OF_WEEK,
                           Client_option, OPTION_GROUP_CHOICES_PREPARATION)

def go():
    clients = Client.objects \
        .filter(status__in=['A', 'D', 'S']) \
        .order_by('pk') \
        .select_related(
            'member',
            'route').prefetch_related('member__member_contact')

    restrictions = Restriction.objects.all().select_related(
        'client',
        'restricted_item')

    avoidingredients = Client_avoid_ingredient.objects.all().select_related(
        'client',
        'ingredient')

    relationships = Relationship.objects.all().select_related(
        'member')

    clientoptions = Client_option.objects \
        .select_related('option') \
        .filter(option__option_group=
                OPTION_GROUP_CHOICES_PREPARATION)

    member_client = {}  # dict of members who are clients
    member_payer = {}  # dict of members who are payers for a client
    member_relationship = {}  # dict of members who are relationships

    with open('clientsinfo.csv', 'w', newline='') as f:
        writer = csv.writer(f, dialect=csv.excel)
        writer.writerow([
            "ID",
            "Client information etc.",
            "Meal Defaults etc.",
            "Meal_Schedule",
            "Relationships",
        ])

        for obj in clients:
            if obj.route is None:
                route = ""
            else:
                route = obj.route.name

            meals_def = [
                (day[0] + " : " +
                 ",".join([str((k,v)) for (k,v) in day[1].items() if v])
                 )
                for day in obj.meals_default]

            meals_sch = [
                str(day) if day in obj.simple_meals_schedule else " "
                for day, trans in DAYS_OF_WEEK]

            # get restricted items for this client
            restricted_items = [
                ri.restricted_item.name
                for ri in restrictions if ri.client == obj]

            # get ingredients that this client avoids
            ingredients_to_avoid = [
                ia.ingredient.name
                for ia in avoidingredients if ia.client == obj]

            # get food preparations for this client
            preparations = [
                copt.option.name
                for copt in clientoptions if copt.client == obj]

            # increment member is client count
            member_client[obj.member.pk] = \
                member_client.get(obj.member.pk, 0) + 1
            # increment member is payer count
            member_payer[obj.billing_member.pk] = \
                member_payer.get(obj.billing_member.pk, 0) + 1
            # get members that have a relationship with this client
            related = []
            for rel in relationships:
                if rel.client == obj:
                    related.append(
                        str(rel.member.pk) + " " +
                        rel.member.firstname + " " +
                        rel.member.lastname + " " +
                        "Nature=(" + rel.nature + ") " +
                        "Type=(" + rel.get_type_display() + ") " +
                        str(rel.extra_fields)
                    )
                    # increment member is relationship count
                    member_relationship[rel.member.pk] = \
                        member_relationship.get(rel.member.pk, 0) + 1

            writer.writerow([
                obj.id,
                (obj.member.firstname + " " + obj.member.lastname + "\n" +
                 "Status: " + obj.get_status_display() + "\n" +
                 "Gender: " + obj.gender + "\n" +
                 "Language: " + str(obj.language).upper() + "\n" +
                 "Birthdate: " + str(obj.birthdate) + "\n" +
                 "Delivery type: " + obj.delivery_type + "\n" +
                 "Payment type : " + str(obj.billing_payment_type) + "\n" +
                 "Rate type : " + str(obj.rate_type) + "\n" +
                 "Route: " + route + "\n" +
                 "Feast MID: " + str(obj.member.mid) + "\n\n" +
                 "Alert: [" + obj.alert + "]"  + "\n\n" +
                 "ADDRESS : " + obj.member.address.street + "\n" +
                 "Apt. " + obj.member.address.apartment + "\n" +
                  obj.member.address.city + "\n" +
                  obj.member.address.postal_code + "\n\n" +
                 "Home ph: " + obj.member.home_phone + "\n" +
                 "Cell ph: " + obj.member.cell_phone + "\n" +
                 "Work ph: " + obj.member.work_phone + "\n" +
                 "Email: " + obj.member.email),
                ("\n".join(meals_def) + "\n\n" +
                 "Restriction categories : " + str(restricted_items) + "\n\n" +
                 "Ingredients to avoid : " + str(ingredients_to_avoid) +"\n\n"+
                 "Food preparation : " + str(preparations) +"\n\n"+
                 "DELIVERY NOTE : " + obj.delivery_note),
                "\n".join(meals_sch),
                ("\n\n".join(related) + "\n\n" +
                 "Billing member : " + str(obj.billing_member.pk) + " " +
                 str(obj.billing_member))
            ])

    members = Member.objects.all().order_by('pk')
    with open('membersinfo.csv', 'w', newline='') as f:
        writer = csv.writer(f, dialect=csv.excel)
        writer.writerow([
            "ID",
            "Member Firstname",
            "Member Lastname",
            "# times client",
            "# times payer",
            "# times relationship",
            "Member Home Phone",
            "Member Cell Phone",
            "Member Work Phone",
            "Member Email",
            "Member Street",
            "Member Apartment",
            "Member City",
            "Member Postal Code",
            "Member work information",
        ])
        # members that are client, and/or payer, and/or relationship
        for obj in members:
            if member_client.get(obj.pk) or \
               member_payer.get(obj.pk) or \
               member_relationship.get(obj.pk):
                writer.writerow([
                    obj.id,
                    obj.firstname,
                    obj.lastname,
                    member_client.get(obj.pk, 0),
                    member_payer.get(obj.pk, 0),
                    member_relationship.get(obj.pk, 0),
                    obj.home_phone,
                    obj.cell_phone,
                    obj.work_phone,
                    obj.email,
                    obj.address.street if obj.address else "",
                    obj.address.apartment if obj.address else "",
                    obj.address.city if obj.address else "",
                    obj.address.postal_code if obj.address else "",
                    obj.work_information,
                ])
        # members that are neither client, nor payer, nor relationship
        for obj in members:
            if not member_client.get(obj.pk) and \
               not member_payer.get(obj.pk) and \
               not member_relationship.get(obj.pk):
                writer.writerow([
                    obj.id,
                    obj.firstname,
                    obj.lastname,
                    0,
                    0,
                    0,
                    obj.home_phone,
                    obj.cell_phone,
                    obj.work_phone,
                    obj.email,
                    obj.address.street if obj.address else "",
                    obj.address.apartment if obj.address else "",
                    obj.address.city if obj.address else "",
                    obj.address.postal_code if obj.address else "",
                    obj.work_information,
                ])

[kousu]

That's great. Thanks for your work @lamontfr;

I have a suggestion: why not get a computer to do the diff? We're probably not at a point where we can trust a computer to do the patch but we can at least automate finding the problems.

For csv, normal diff is a bit noisy: it'll flag entire records that have changed, instead of which fields. We can use wdiff on it instead to zero in on the changes, e.g. apparently this works

wdiff -n -w $'\033[30;41m' -x $'\033[0m' -y $'\033[30;42m' -z $'\033[0m' | less -R

Alternatively, instead of outputting in csv, if we changed the above script to output fields line-by-line, like vcf, then so long as we also ensure the records are consistently sorted between the two databases normal diff -u will immediately zero-in on the changes.

[kousu]

Oh sorry, I misread. I assumed you'd also written a script to dump the data from Feast in the same format.

I'm confused, then; is printing just to make the workflow easier?

[kousu]

By the way it looks like this is almost built into MySQL:

SELECT * INTO OUTFILE 'clientinfo.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM client;
SELECT * INTO OUTFILE 'memberinfo.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM member;

((and a side note: postgres has the slightly better \copy my_table to 'filename' csv header which outputs the header line too))

but that will miss the relationships data, so I guess it's no good at the moment.

[lamontfr]

@kousu Thanks for your ideas. The problem is that the Feast database structure suffers from lack of integrity, making it very difficult to extract complete client information. Therefore, printing the data from Souschef will facilitate the workflow by enabling a volunteer, who knows Feast but not Souschef, to identify all the required changes.

[lamontfr]

@kousu @JeanFrancoisFournier I updated the script to add the Feast MID for each client.

[kousu]

I've run this on prod and sent the results over to @JeanFrancoisFournier. Your instructions were excellent, @lamontfr: every command you gave worked as directed the first time, and I can see you double-checked everything.

I have some nitpicks

• The script should be checked in directly under src/; if you do this on a branch this issue here could then be a Pull Request and we could work from that; then instead of needing to put install instructions in the script, it just becomes part of the codebase
• The way to actually run it is nested like seventy billion times: python in django-manage in docker in ssh. It would be cleaner and more framework-friendly to at least write a proper management command so you could say "python3 manage.py export-clientdata"
• Feedback (to stderr) would be useful as it runs
• I would prefer if there were two separate scripts for the two separate files, and that each output to stdout; that way we can see results immediately, and skip the docker cp steps by instead using > membersinfo.csv and > clientsinfo.csv

I suppose we won't be running this script very often, so maybe cleaning it up isn't the most worthwhile investment; take this list as a roadmap, not a demand.

[lamontfr]

@kousu I am glad that the instructions were correct and that all worked well. I did not put this in the code base because this script is meant to be run only once in its current form. If @JeanFrancoisFournier wants to reuse this in the future I will take into account your recommendations.

[lamontfr]

The script was used successfully in October 2017 by Santropol Roulant to print the information needed to make changes to Souschef data before going in production.