Computer Lab on the Cheap

Budget – a bit over $3000. Goal – a 26 seat computer lab for a high school. Is it possible?

Requirements

The computers need to run basic office productivity, web browsing software, authentication, and monitoring what students are doing.

Software

I went with Linux Terminal Services Project (LTSP). It doesn’t require a hard drive on the clients. The Ubuntu LTSP wiki is a good resource for setting this up. I tried using Proxmox (openvz) at first so I could have 2 LTSP nodes mixed on the same hardware as less intensive applications. This became problematic as openvz doesn’t easily support fuse. When I plugged in a USB drive to a thin client it wouldn’t work. I could have tried to build my own kernel with openvz and fuse support but decided against it. Instead I placed everything on one dedicated server.

For authentication I use existing OpenLDAP and samba servers. I won’t go into specific on setting these up in this post. I will say use the Ubuntu server guide and not the community wiki for openldap. The wiki is terribly out of date and in many cases incomplete or wrong. LTSP uses the underlying authentication so if you can log into your server with ldap you should be good to go. I didn’t find anything different about LTSP. pam_mount, pam_ldap, etc all work as they would on a traditional desktop. I used pam_mount to mount the student’s document folder automatically. I wrote about pam_mount before when messing with Active Directory.

For printing I used cups 1.5. Cups 1.4 in Ubuntu has some nasty bugs that make the server hang in some cases in my experience. I used an existing cups server but it could also just exist on the LTSP server. By using a central cups server though you can have non thin client desktops use it as well. This makes administration pretty easy. I will say I hate how it always show all printers even ones the user doesn’t have permission for! Also just set your MaxClients to some really high number! I found you can reach the default (100) pretty easily even with just 40 or so users! Why is the default so low? A modern computer can probably handle millions of clients.

To customize the user interface, just drop files in /etc/skel. Setup the desktop how you want it and copy in ~/.config

To view what students are doing you can use italc. The wiki makes it sound super easy to install on LTSP. The wiki rather confusing. I put the lts.conf file at /var/lib/tftpboot/ltsp/i386/lts.conf The man page on lts.conf doesn’t say anything about START_ITALC. My file looks like this:

[Default]
 START_ITALC = True

Windows Applications on LTSP

While I would have rather have used LibreOffice, it was determined we needed Office 2007. This is somewhat of a project of it’s own. For personal use wine works great with Office. For multiple, non-technical users? Forget it! I used Crossover Professional which comes with multi-user support and hooks to do extra scripting. Of course it does cost money and I sank about a third of my budget into this. Even with crossover be ready for lots of bugs and hacks. My issues included

  • Windows desktop is not the actual ~/Desktop folder! Why not!? I used a script to symlink them. I named by script according to their convention 01.create-stub so it gets called when a user logs into the first time and gets a new MS Office “stub”, which is just the user specific parts owned by the local user instead of root. In regular wine you would only have this and thus you can’t have multi-user support.
    #!/bin/sh
    rm -rf “$WINEPREFIX/dosdevices/c:/users/crossover/Desktop”
    ln -s -f “$HOME/Desktop” “$WINEPREFIX/dosdevices/c:/users/crossover/Desktop”
  • Can’t print to PDF. wine/crossover won’t allow it. It sort of works if you install cups-pdf but it’s too buggy for production use. Office 2007 has an add-on to save directly as PDF. So install it and any SP’s for Office.
  • wine remembers every printer forever! If you connect a printer via cups and remove it, Office will forever think it still exists. Again fine for personal use but in a school forget it! And what if you ever want to rename printers in cups? Duplicates. Delete all printer references in /opt/cxoffice/support/ms_office/system.reg and user.reg. At least this way the user starts out with no dead printers. I need to make a script to delete them on startup or something, it’s on my to do list.
  • Default applications – It doesn’t work. I wanted to make .doc open by default with Office. Crossover people tell me it’s a bug in Ubuntu. You can fix it by editing
    /etc/X11/Xsession.d/55gnome-session_gnomerc
    Change
    XDG_DATA_DIRS=/usr/share/gnome:/usr/local/share:/usr/share
    to
    XDG_DATA_DIRS=/usr/local/share:/usr/share/gnome:/usr/share
  • Lots of minor glitchy bugs such as the “Office button” menu often disappearing.
  • Excel uses a mdi form that doesn’t display correctly on the gnome task bar. I have no solution to this other than starting new Excel processes (by starting Excel each time, not just opening xls files). Why do MDI forms still exists? This is not DOS. We have operating system level window management these days.
Finally you can package your wine “bottle” as a deb for easy deployment. Fun fact – because Word is a single process application if you log into 2 LTSP as the same user and both run it, two windows will come up on the first users screen and none on the other! But with Excel you can run multiple processes. Ha and people say LibreOffice has a messy code base. This doesn’t happen when using different users so it’s not a problem.

Hardware

I find it pretty easy to come across donated Pentium 4 desktops and servers. My dedicated server was a HP ProLiant G5 with 16GB ram and two four-core Xeon processors. That’s 8 cores total. A very nice server. One could probably get away with using slightly older models or could just add many into a cluster. For a non profit it seems reasonable to me that you can find such equipment for free, though I do live in NYC which might make it easier as there are not shortage of mega financial firms with cycling through equipment.

Old desktops are gross, so hide them under the desk if possible.

Flat screen monitors are usually never given away. Be prepared to buy them. About a 1/3rd of my budget went to this and keyboards/mice.

It’s easier to find it easier to get old non networked printers. If they have a parallel port you can buy a “Print Server” and get it online.

Networking

I ran 2 gigabits of bandwidth from the server to the lab over two 1 gigabit cables/switches. Check out the Edubuntu LTSP requirements page. I used 2 switches in the lab each with a 1 gigabit backend and 100 megabits to each thin client. Another third of my budget went into networking equipment. Thin Clients are of course going to be network intensive! I found some volunteers to help with running cables and computers. Thanks volunteers!!

Expenses

The Volunteers and I

Results

LTSP runs fast with the right hardware! If you ever used VNC (please don’t it’s terribly slow!) you might be scared of remote access. LTSP uses X’s natural networking capabilities and it might not be as fast as RDP, but it’s close. Mouse input is very smooth. Programs start up super fast (they are probably all already in ram). I’ll probably try playing around with deploying some fat clients on some of the better machines to increase performance.

If you read my other posts you know I really get annoyed with all the Linux Desktop bugs. But most of that is mitigated by using thin clients. The server is always on and there are no laptop users. Syncing files and fighting upstart just becomes a moot point.

Installing new application is dead simple. I can apt-get install them on the ltsp server and they just appear even if a user is already logged in.

I just set up the lab, so I’ll update this post with results of actual student usage! And be sure to donate to Cristo Rey Brooklyn High School so we can get more computers! Otherwise I’ll assume you hate education.

Django End User Defined Custom Fields

I want a way for end users to add fields to my Django app without programming. I’ll publish this eventually, but here how to implement it. Latest version is now at http://code.google.com/p/django-custom-field/

First I made 2 models. One for the field and one for the values associated with the field.

from django.contrib.contenttypes.models import ContentType
from django.db import models

class CustomField(models.Model):
    """
    A field abstract -- it describe what the field is.  There are one of these
    for each custom field the user configures.
    """
    name = models.CharField(max_length=75)
    content_type = models.ForeignKey(ContentType)
    field_type = models.CharField(max_length=1, choices=(('t','Text'),('i','Integer'),('b','Boolean (checkbox)'),), default='t')

    def get_value_for_object(self,obj):
        return CustomFieldValue.objects.get_or_create(field=self,object_id=obj.id)[0]

    def __unicode__(self):
        return unicode(self.name)

    class Meta:
        unique_together = ('name','content_type')

class CustomFieldValue(models.Model):
    """
    A field instance -- contains the actual data.  There are many of these, for
    each value that corresponds to a CustomField for a given model.
    """
    field = models.ForeignKey(CustomField, related_name='instance')
    value = models.CharField(max_length=255,blank=True,null=True)
    object_id = models.PositiveIntegerField()
    #content_type = models.ForeignKey(ContentType)

    def __unicode__(self):
        return unicode(self.value)

Next I wanted this to work with admin. So I extend any ModelAdmin you want with this I need. Notice we don’t handle errors! I have my 3 simple data types all do client side validation, then if the server validation comes up invalid, it just throws them away. It’s less than ideal but I wasn’t sure how to get it to display the errors correctly.

from django import forms
from django.contrib.contenttypes.models import ContentType
from django.contrib import admin
from django.forms.widgets import TextInput

from models import *

class NumberInput(TextInput):
    input_type = 'number'

class CustomFieldAdmin(admin.ModelAdmin):
    def __create_custom_form(self, obj_id=None):
        custom_fields = CustomField.objects.filter(content_type=ContentType.objects.get_for_model(self.model))

        custom_form = forms.Form(prefix="cstm")
        for field in custom_fields:
            if field.field_type == 'i':
                custom_form.fields[field.name] = forms.IntegerField(label=field.name, required=False, widget=NumberInput(attrs={'style':'text-align:right;','step':1}))
            elif field.field_type == 'b':
                custom_form.fields[field.name] = forms.BooleanField(label=field.name, required=False)
            else:
                custom_form.fields[field.name] = forms.CharField(label=field.name, max_length=255, required=False)
            if obj_id:
                value = CustomFieldValue.objects.get_or_create(field=field,object_id=obj_id)[0]
                custom_form.fields[field.name].initial = value
        return custom_form

    def render_change_form(self, request, context, *args, **kwargs):
        context['custom_form'] = self.__create_custom_form(context['original'].id)
        return super(CustomFieldAdmin, self).render_change_form(request, context, *args, **kwargs)

    def save_model(self, request, obj, form, change):
        custom_form = self.__create_custom_form()
        custom_form.data = request.POST
        custom_form.is_bound = True
        if custom_form.is_valid():
            data = custom_form.cleaned_data
            for key,data_field in data.items():
                custom_field = CustomField.objects.get_or_create(content_type=ContentType.objects.get_for_model(self.model), name=key)[0]
                custom_value = CustomFieldValue.objects.get_or_create(field=custom_field,object_id=obj.id)[0]
                custom_value.value = data_field
                custom_value.save()
        # Hope that client side validation works since we don't handle errors here!

        return super(CustomFieldAdmin, self).save_model(request, obj, form, change)

Now we have to edit the admin template. This isn’t ideal but I couldn’t think of any other way. Edit change_form.html and add

{% include "admin/includes/custom_field_fieldset.html" with custom_form=custom_form %}

wherever you want. I say it’s not ideal because if you make lots of customizations it’s hard to keep track. Blocks help with this, BUT Django’s admin content block is pretty big, and I wanted to add mine basically in the middle of the content. Now you need the referenced custom_field_fieldset.html

{% spaceless %}
{% if custom_form.fields %}

  <fieldset class="module">
    <h2 class="collapse-handler">Custom Fields</h2>

    {% for field in custom_form %}
      <div class="row cells-1 {{ custom_form.prefix }}-{{ field.name }}">
        <div class="column span-4">
          {{ field.label_tag }}
        </div>
        <div class="column span-flexible">
          {{ field }}
        </div>
      </div>
    {% endfor %}
  </fieldset>

{% endif %}
{% endspaceless %}

That’s it. So now for the real test, can I add a custom field to my custom field model?

Now you can customize your fields while you customize fields! If you need to access the custom fields programmatically you can make shortcuts like

def get_custom_fields(self):
    return CustomField.objects.filter(content_type=ContentType.objects.get_for_model(Whatever))

Next I need to add integration with my applications import tool, make something so you can extend Model to get that helper function, and integrate with django-admin-export. Maybe even create a dropdown field_type which would require another model to store the data in. Then I promise to post to pypi.

Fully automatic backup/sync script with Unison

I’ve been searching for something to replace offline files in Linux. It’s a great feature that Linux just doesn’t have where files on a share can be stored locally and synced when back on the network. A Dropbox like solution seems like a close enough alternative, but these programs are expensive and can’t be centrally managed. So I made my own, albeit less fully featured. Unison is an open source bidirectional sync tool. It can be set to use time based conflict resolution without notifying the user. Let’s hope the clocks are correct.

One problem with Unison is deploying it automatically. I have a file server that I mount with pam_mount. I mount it to ~/.whatever so it stays hidden to the user. Next I put a script to insert it into crontab. Now all the user has to do is run that script and they files are backed up. libnotify even tells them so. The script also takes care of the initial server to local sync. If we didn’t do this unison would think we wanted to delete all files on the server (don’t worry it would throw an error unless you used a specific argument). So here it is.

import os

# Place this in a cron job for say every 2 minutes. To script this use
# crontab -l | { cat; echo "0 0 0 0 0 some entry"; } | crontab -

# You may also want to run ntpd on all clients to keep the time in sync.

# Array of locations of local and remote locations. I recommend mounting remote folders in
# Example (('/home/user/Documents', '/remotehostname/somefolder'),)
sync_locations = (
    ("/home/david/local", "/home/david/server"),
)
# Check if host is up
check_host = "localhost"

# Only allow this program to run once!
try:
    import socket
    s = socket.socket()
    host = socket.gethostname()
    port = 35636    #make sure this port is not used on this system
    s.bind((host, port))
except:
    exit()
if not os.path.exists(os.getenv("HOME") + '/.unison_backup'):
    os.mkdir(os.getenv("HOME") + '/.unison_backup')

if os.path.exists(sync_locations[0][0]) and 0 == os.system('ping -c 1 ' + check_host):
    if not os.path.isfile(os.getenv("HOME") + "/.unison_backup/first_sync_complete"):
        os.system('notify-send "Unison Backup" "Starting Initial Sync. You will be notified when finished."')
        for sync_location in sync_locations:
            exit_code = os.system('rsync -r ' + sync_location[1] + "/ " + sync_location[0])
            if exit_code != 0:
                os.system('notify-send "Could not sync!"')
                exit()
        open(os.getenv("HOME") + '/.unison_backup/first_sync_complete', 'w').close()
        os.system('notify-send "Unison Backup" "Initial Sync Complete"')

    # Run Unison
    for sync_location in sync_locations:
        os.system('unison %s %s -batch -prefer newer -times=true' % (sync_location[0], sync_location[1]))

Mail Merge in Libreoffice

Update 2013-08-20: Here is a bug report I submitted about unclear documentation.

A less developer oriented post today. I find there to be an incredible lack of tutorials on how to mail merge in LibreOffice. So I made my own.

How to Mail Merge with LibreOffice

This tutorial assumes you have an xls or ods file with data you want to “merge” to a document.

Making labels?

Then do this instead. The documentation is still rather unclear. You will need to use the Edit, Exchange databases as described below. I’ve also noticed if I try to print directly as the documentation would leave you to believe, it gives me blank pages in between each sheet! To solve this, set up the labels but instead of hitting print, select Tools, Mail Merge Wizard as proceed as described below. Essentially you are still performing a mail merge, except by starting from New, Labels, it will take care of the otherwise complex formatting of a label.

Step 0 if using Ubuntu – Ubuntu doesn’t include the full LibreOffice suite by default (see bug report). It includes a minimal version. If you try to use a feature that isn’t installed it will crash or just not work. Lovely. Install the full version of libreoffice in by opening Ubuntu software center, search for Libreoffice, click install. Or via command line
sudo apt-get install libreoffice

  1. Ensure you have proper headers on your spreadsheet and save it. Take note where you save it to!

    Compiz why did you make this blue?

  2. In Writer, Click Edit then Exchange Database… Then click Browse.  Select the file you just made

    Why is it named exchange database?
  3. Click View, Data sources (or press F4)

  4. On the left is a list of data sources. These should include the file you just made. Select it, then Tables, then Sheet1 (or the name of the sheet you want)

  5. You will see the data from the spreadsheet. Click and drag the column you want into your document. For example if I wanted First Name, I would click and drag the First Name column title and not an individual cell like Bob. You will notice it appears gray in your document. You are free to cut and paste it or change the formatting. These words will be replaced with the data in your spreadsheet. Note that Libreoffice can’t do conditional fields. For example let’s say you have

    Address1
    Address2
    City, State, Zip

    Some people have one address but not the other. Libreoffice will insert a blank line no matter what. You can get around this by using the “address block” feature but that’s very limited and won’t work for every use case. You can also try making conditional sections but with such difficulty in doing that, you might as well just make your text document in Python.

  6. Click Tools, Mail Merge Wizard or click the envelope icon in data sources. This stuff is mostly stupid and deals with preformatted address blocks, etc. I’ve never under any circumstance use them but you will need to disable them all. Work through each step and click next to continue:

    1. Select starting Document: Select current document.
    2. Select document type: In this example we intend to print or save the file so select Letter.
    3. Insert address block: Ensure “This document shall contain an address block” is unchecked.
    4. Create salutation: Ensure “This document should contain salutation” is unchecked.
    5. Edit document: By clicking the left and right arrow we can preview each page.
    6. Personalize document: This screen shows us the finished document which is editable.
    7. Save, print or send: What do you want to do this the finished document? You may save it, print it, or email it (may require additional setup). Save merged document will save the finished multi page document and Save starting document will save your template for later use.

Suppressing blank mail merge lines

Thanks to Juanito for the comment. You can in fact suppress blank lines (ex address 2) though it’s anything but intuitive.

  1. Insert field
  2. Ensure you are entering paragraphs instead of line breaks. Click view, nonprinting characters to check. Ex paragraph mark Screenshot from 2013-03-19 14:29:10
  3. Hover over field until name appears, e.g. mailmergedata.Sheet1.Address2, and note the name. If the name has spaces do this [file with spaces.Sheet1.Adddress 2]
  4. Place the cursor to the left of the field. 
  5. Click Insert, Fields, Other
  6. Click the Functions tab
  7. Select Hidden Paragraph
  8. In the Condition box, enter: NOT [field name from step 2], e.g. NOT [mailmergedata.Sheet1.Address 2]
  9. Click Insert (this might not cause any visible change)
  10. Click Close
  11. Click the View menu and make sure Hidden Paragraphs is unchecked
  12. Merge!

Overall thoughts? Libreoffice is terrible. A hugely common use case is convoluted. Looks like Office 2010 also requires this conditional paragraph suppression too. The world marches backwards.

Adding new form in a formset

Everything I read about adding a new form to a formset with javascript involves cloning an existing form. This is a terrible method, what if the initial forms are 0? What about initial data? Here’s IMO better way to do it that uses empty_form, a function Django gives you to create a form where i is __prefix__ so you can easily replace it.

Add this under you “Add new FOO” button. In my case I have a question_form with many answers (answers_formset).

<script>
      var form_count_{{ question_form.prefix }} = {{ answers_formset.total_form_count }};
      $('#add_more_{{ question_form.prefix }}').click(function() {
          var form = '{{answers_formset.empty_form.as_custom|escapejs}}'.replace(/__prefix__/g, form_count_{{ question_form.prefix }});
          $('#answers_div_{{ question_form.prefix }}').append(form);
          form_count_{{ question_form.prefix }}++;
          $('#id_{{ answers_formset.prefix }}-TOTAL_FORMS').val(form_count_{{ question_form.prefix }});
      });
</script>

This creates you empty_form right in javascript, replaces the __prefix__ with the correct number and inserts it, in my case I made an answers_div. See empty_form.as_custom, you could just do empty_form but that would just give the you basic form html. I want custom html. Make a separate template for this. Here’s mine but this just an example.

{{ answer.non_field_errors }}
{% for hidden in answer.hidden_fields %} {{ hidden }} {% endfor %}
<table>
    <tr>
        <td>
            <span class="answer_span">{{ answer.answer }} {{ answer.answer.errors }}</span>
        </td>
        ......etc.......
    </tr>
</table>

In your original template you can add the forms like this {% include “omr/answer_form.html” with answer=answer %}
But for the as_custom you need to edit your form itself to add the function.

def as_custom(self):
    t = template.loader.get_template('answer_form.html')
    return t.render(Context({'answer': self},))

I find this method far more stable than trying to clone existing forms. It seems to play well with the javascript I have in some of my widgets. Clone on the other hand gave me tons of trouble and hacks needed to fix it.

Django get_or_default

Quick hack today. Often I find myself wanting to get some django object, but in the case it doesn’t exist default it to some value. Specially I keep my end user configurable settings in my database. Typically I set this up with initial data so all the settings are already there, but sometimes I’ll add a setting and forgot to add it on some site instance.

class Callable:
    def __init__(self, anycallable):
        self.__call__ = anycallable

def get_or_default(name, default=None):
        """ Get the config object or create it with a default. Always use this when gettings configs"""
        object, created = Configuration.objects.get_or_create(name=name)
        if created:
            object.value = default
            object.save()
        return object
    get_or_default = Callable(get_or_default)

Now I can safely call things like edit_all = Configuration.get_or_default(“Edit all fields”, “False”) which will return my configuration object with the value set as False if not specified. Much better than a 500 error. There are plenty of other uses for this type of logic. Get_or_return_none for example. The goal for me is to stop 500 errors from my own carelessness by having safe defaults.

Django admin: Export ForeignKey subfields to XLS

Last post I made an export to XLS tool for Django’s admin interface. A common request is to quickly export related field data as well. Today I’ll show you how to export foreign key fields, recursively. It doesn’t work for ManyToMany which are of course much more complicated since it can return more than one result! First I though it would be nice to have a view permission for my models. If I just want to export them, why would I need edit permission? This blog shows a quick way to get view permissions to all models. Note you could just use the edit permission instead if that works for you.

This time AJAX is a must. I’m using it to insert html where I want it to dynamically construct the export view. This has the advantage of not getting caught up in loops of references referencing other references…uck recursion. I’ve heavily modified my files from the last post. If you really want to understand how all this works, I suggest you start there. Note I still use the export_simple_selected_objects function from before, left untouched. Here are my new views:

def get_fields_for_model(request):
    """ Get the related fields of a selected foreign key """
    model_class = ContentType.objects.get(id=request.GET['ct']).model_class()
    queryset = model_class.objects.filter(pk__in=request.GET['ids'].split(','))
    
    rel_name = request.POST['rel_name']
    related = model_class
    for item in rel_name.split('__'):
        related = getattr(related, item).field.rel.to
    
    model = related
    model_fields = model._meta.fields
    previous_fields = rel_name
    
    for field in model_fields:
        if hasattr(field, 'related'):
            if request.user.has_perm(field.rel.to._meta.app_label + '.view_' + field.rel.to._meta.module_name):
                field.perm = True
    
    return render_to_response('sis/export_to_xls_related.html', {
        'model_name': model_class._meta.verbose_name,
        'model': model._meta.app_label + ":" + model._meta.module_name,
        'fields': model_fields,
        'previous_fields': previous_fields,
    }, RequestContext(request, {}),)
    
def admin_export_xls(request):
    model_class = ContentType.objects.get(id=request.GET['ct']).model_class()
    queryset = model_class.objects.filter(pk__in=request.GET['ids'].split(','))
    get_variables = request.META['QUERY_STRING']
    model_fields = model_class._meta.fields
    
    for field in model_fields:
        if hasattr(field, 'related'):
            if request.user.has_perm(field.rel.to._meta.app_label + '.view_' + field.rel.to._meta.module_name) or request.user.has_perm(field.rel.to._meta.app_label + '.change_' + field.rel.to._meta.module_name):
                field.perm = True
    
    if 'xls' in request.POST:
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(unicode(model_class._meta.verbose_name_plural))
        
        # Get field names from POST data
        fieldnames = []
        # request.POST reorders the data 🙁 There's little reason to go through all
        # the work of reordering it right again when raw data is ordered correctly.
        for value in request.raw_post_data.split('&'):
            if value[:7] == "field__" and value[-3:] == "=on":
                fieldname = value[7:-3]
                app = fieldname.split('__')[0].split('%3A')[0]
                model = fieldname.split('__')[0].split('%3A')[1]
                # Server side permission check, edit implies view.
                if request.user.has_perm(app + '.view_' + model) or request.user.has_perm(app + '.change_' + model):
                    fieldnames.append(fieldname)
                
        # Title
        for i, field in enumerate(fieldnames):
            #ex field 'sis%3Astudent__fname'
            field = field.split('__')
            model = get_model(field[0].split('%3A')[0], field[0].split('%3A')[1])
            txt = ""
            for sub_field in field[1:-1]:
                txt += sub_field + " "
            txt += model._meta.get_field_by_name(field[-1])[0].verbose_name
            worksheet.write(0,i, txt)
        
        # Data
        for ri, row in enumerate(queryset): # For Row iterable, data row in the queryset
            for ci, field in enumerate(fieldnames): # For Cell iterable, field, fields
               try:
                    field = field.split('__')
                    data = getattr(row, field[1])
                    for sub_field in field[2:]:
                        data = getattr(data, sub_field)
                    worksheet.write(ri+1, ci, unicode(data))
                except: # In case there is a None for a referenced field
                    pass 
        
        # Boring file handeling crap
        fd, fn = tempfile.mkstemp()
        os.close(fd)
        workbook.save(fn)
        fh = open(fn, 'rb')
        resp = fh.read()
        fh.close()
        response = HttpResponse(resp, mimetype='application/ms-excel')
        response['Content-Disposition'] = 'attachment; filename=%s.xls' % \
              (unicode(model_class._meta.verbose_name_plural),)
        return response
    
    return render_to_response('sis/export_to_xls.html', {
        'model_name': model_class._meta.verbose_name,
        'model': model_class._meta.app_label + ":" +  model_class._meta.module_name,
        'fields': model_fields,
        'get_variables': get_variables,
    }, RequestContext(request, {}),)

admin_export_xls does the bulk of the work and actually creates your xls reports. We use a similar syntax to django queries to find out exactly what data we are looking for. Something like “field__work_study:company__placement__company__id” where field just makes it a unique html name, followed by the app:model, then each object. So here we know we have a work_study.company model. We take our row in the queryset and say row.placement.company.id. Of course it uses a recursive getattr instead.

get_fields_for_model handles each ajax request for getting more fields. Now for the html I have two files
export_to_xls.html

<style type="text/css">
  td, th {
    padding: 2px 0px 3px 3px;
    text-align: left;
  }
</style>

<script type="text/javascript"> 
  $(document).ready(function()
  {
    $("#check_all").click(function()        
    {
      var checked_status = this.checked;
      $(".check_field").each(function()
      {
        this.checked = checked_status;
      });
    });
  });
  
  function get_related(name){
    $.post(  
      "/sis/export_to_xls_related/?{{ get_variables }}",  
      {get_related: "True", rel_name: name},
      function(data){
        $("#field_" + name).hide('fast');
        $("#field_" + name).html(data);
        $("#field_" + name).show('slow');
      }  
    );  
  }
</script>   

<h2> Export {{ model_name }} </h2>

<form method="post" action="/sis/export_to_xls/?{{ get_variables }}">
  <table>
    <tr>
      <th>
        <input type="checkbox" id="check_all" checked="checked" />
      </th>
      <th>
        Field
      </th>
    </tr>
  
    {% for field in fields %}
      <tr>
        <td>
          <input type="checkbox" class="check_field" checked="checked" name="field__{{ model }}__{{ field.name }}"/>
        </td>
        <td>
          {{ field.verbose_name }}
        </td>
      </tr>
      {% if field.related %}{% if field.perm %}
        <tr>
          <td></td>
          <td>
            <div id="field_{{ field.name }}">
              <a href="javascript:void(0)" onclick="get_related('{{ field.name }}')"> >> Expand {{ field.verbose_name }}</a>
            </div>
          </td>
        </tr>
      {% endif %}{% endif %}
    {% endfor %}
  </table>
  <input type="submit" name="xls" onclick='$("#export_xls_form").overlay().close();' value="Submit"/>
</form>

export_to_xls_related.html

<table>
  {% for field in fields %}
    <tr>
      <td>
        <input type="checkbox" class="check_field" checked="checked" name="field__{{ model }}__{{ previous_fields }}__{{ field.name }}"/>
      </td>
      <td>
        {{ field.verbose_name }}
      </td>
    </tr>
    {% if field.related %}{% if field.perm %}
      <tr>
        <td></td>
        <td>
          <div id="field_{{ previous_fields }}__{{ field.name }}">
            <a href="javascript:void(0)" onclick="get_related('{{ previous_fields }}__{{ field.name }}')"> >> Expand {{ field.verbose_name }}</a>
          </div>
        </td>
      </tr>
    {% endif %}{% endif %}
  {% endfor %}
</table>

I also use the modified change_list.html from last post. export_to_xls.html gets inserted into export_to_xls_related.html a potentially infinite amount of times which is all displayed on an overlay in the edit list view. When you click Submit you get an XLS file with all checked off fields. Hurray!

TODO: Many to Many fields! This will be much harder. The only way to show them is either by repeating rows or smashing them all in one cell. This is the peril of trying to fit a potentially infinite dimensional object into a 2D spreadsheet.

Django admin: better export to XLS


The goal here is to make a slick gui for selecting exactly what the user wants to export from Django’s Change List view. It will be an global action, so lets start there.

def export_simple_selected_objects(modeladmin, request, queryset):
  selected_int = queryset.values_list('id', flat=True)
    selected = []
    for s in selected_int:
        selected.append(str(s))
  ct = ContentType.objects.get_for_model(queryset.model)
  return HttpResponseRedirect("/export_to_xls/?ct=%s&ids=%s" % (ct.pk, ",".join(selected)))
export_simple_selected_objects.short_description = "Export selected items to XLS"
admin.site.add_action(export_simple_selected_objects)

This adds a global action called Export selected items to XLS. I went with xls instead of ods because xlwt is very mature and LibreOffice can open xls just fine. It’s limited by the max length of get variables because it just lists each id. See this bug report. Next is the view.

import xlwt
def admin_export_xls(request):
    model_class = ContentType.objects.get(id=request.GET['ct']).model_class()
    queryset = model_class.objects.filter(pk__in=request.GET['ids'].split(','))
    model_fields = model_class._meta.fields

    if 'xls' in request.POST:
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(unicode(model_class._meta.verbose_name_plural))
        fields = []
        # Get selected fields from POST data
        for field in model_fields:
            if 'field__' + field.name in request.POST:
                fields.append(field)
        # Title
        for i, field in enumerate(fields):
            worksheet.write(0,i, field.verbose_name)
        for ri, row in enumerate(queryset): # For Row iterable, data row in the queryset
            for ci, field in enumerate(fields): # For Cell iterable, field, fields
                worksheet.write(ri+1, ci, unicode(getattr(row, field.name)))
        # Boring file handeling crap
        fd, fn = tempfile.mkstemp()
        os.close(fd)
        workbook.save(fn)
        fh = open(fn, 'rb')
        resp = fh.read()
        fh.close()
        response = HttpResponse(resp, mimetype='application/ms-excel')
        response['Content-Disposition'] = 'attachment; filename=%s.xls' % \
              (unicode(model_class._meta.verbose_name_plural),)
        return response

    return render_to_response('export_to_xls.html', {
        'model_name': model_class._meta.verbose_name,
        'fields': model_fields,
    }, RequestContext(request, {}),)

Remember to set up your URLs. Next is the HTML. Maybe something like this

<script type="text/javascript">
	$(document).ready(function()
	{
		$("#check_all").click(function()
		{
			var checked_status = this.checked;
			$(".check_field").each(function()
			{
				this.checked = checked_status;
			});
		});
	});
</script>
<h2> Export {{ model_name }} </h2>
    <form method="post" action="">
		<table>
			<tr>
				<th>
					<input type="checkbox" id="check_all" checked="checked" />
				</th>
				<th>
					Field
				</th>
			</tr>
			{% for field in fields %}
				<tr>
					<td>
						<input type="checkbox" class="check_field" checked="checked" name="field__{{ field.name }}"/>
					</td>
					<td>
						{{ field.verbose_name }}
					</td>
				</tr>
			{% endfor %}
		</table>
        <input type="submit" name="xls" value="Submit"/>
    </form>

The javascript just makes the check all box work. Note I use jquery, if you don’t you will need to rewrite it. Very simple but it works. Now users won’t have to delete unwanted columns from xls reports. Notice how the user is left on the export screen and not happily back to the edit list. Some ajax can solve this. I’m overriding the global change_list.html which actually isn’t ideal if you use any plugins that also override it. Here’s what I added.

<script src="/static/js/jquery.tools.min.js" type="text/javascript"></script>
<script type="text/javascript">
  $(document).ready(function()
  {
    $(".button").click(function()
    {
     if (
       $("option[value=export_simple_selected_objects]:selected").length
       && $("input:checked").length
     ) {
        $.post(
          "",
          $("#changelist-form").serialize(),
          function(data){
              $("#export_xls_form").html(data);
          }
        );
        $("#export_xls_form").overlay({
          top: 60
        });
        $("#export_xls_form").overlay().load();
        return false;
      }
    });
  });
</script>
<!-- Overlay, when you edit CSS, make sure this display is set to none initially -->
  <div class="modal" id="export_xls_form"><button class="close"> Close </button></div>

I use jquery tools overlay to make a nice overlay screen while keeping the user on the change list page. Basically I want a div to appear and then load some stuff from ajax. What’s cool is that I just post the data to “” so the regular Django admin functions work without editing them for AJAX. Well I did add to the submit button onclick=’$(“#export_xls_form”).overlay().close();’ to close the window when submitting. Ok I’m a complete liar I also added get_variables = request.META[‘QUERY_STRING’] to the view as a cheap way to keep those GET variables. But hey it’s still works as a non ajax admin action and that’s cool.

In the screenshot I added a CSS3 shadow and rounded corners to make it look better.

What’s next? Well it would be nice if we could access foreign key fields. If this had some type of advanced search and saving mechanism, we’d have a full generic Django query builder. Hmm.

Isn’t IT supposed to save money?

From this NY Times article  the New York City Department of Education (DOE) is spending $700 million on a payroll system. You know the type of thing I made as a hobby or any number of existing projects. Meanwhile teachers are being laid off. There’s some real administrative problems here, but there’s also some very big IT ones when $700 million is spent on effectively nothing. I would take a project like that for $50k plus hardware expenses, but actually I’d feel bad ripping of the DOE.

IT is supposed to save money remember? Having machines work for you. If this isn’t happening you are doing it wrong. Last year I made a proposal to a certain school in New York to create a free and open source information system. It keeps payroll of student workers. It handles grades and general school data. It even integrates with products like SugarCRM and Engrade (free but not FOSS). The program brings what would have costs over $5000 a year to just $2000, a fee I charge for continued support. But the real savings is in efficiency. Student Worker Relational Database is saving time for teachers and staff. Remember, that’s what IT is supposed to do, computers working for you. Teachers can look up student data in seconds, rather than pour over various databases and Excel sheets. Principles can do complicated analytics in minutes rather than days.

[Student Worker Database] brings together all the information that different people in the school -Administration, Teachers, CWSP, College Counselors, Dean of Student, Social Workers, etc – used to keep in different and not always integrated software. I can now have all this information integrated in one common report saving vary valuable time. The grade analytic feature has also given us the possibility of accessing and processing data much faster and using it to inform teachers, students and parents.
– Maria Andreau, Assistant principle of Cristo Rey New York High School

The open source model allows other schools to potentially contribute as well. Unlike some industries, education isn’t really about “beating” your competition. Yes every school wants to be the best, but that doesn’t mean you want other schools to fail. If another school benefits from work done on an open source project, it actually benefits the early adopter school. That’s why I don’t believe proprietary systems have any place in the education environment. There may be some models that it makes sense, but not schools. Schools are the perfect environment for open source. They aren’t as competitive. They have very common needs. They attract people who are in it to help people, not just the money (which isn’t so glamorous). But where is open source in the industry? It’s near non existent. There are some exceptions, but most schools use Windows, MS Office, proprietary school information systems, proprietary fund raising tools, etc.

If there is a beacon of hope, it’s that these proprietary systems are awful. The opportunity for a hugely successful open source project is here. The information system I replaced with year was a 90’s DOS application ported to Windows. Updates generally include a new picture and marketing about how modern the system is. Even the newer web based systems are expensive and don’t offer integration outside their select partners.

I have more ideas, stay tuned!

Linux in schools

My most popular post is the Linux and Active Directory post. Today I’ll give an update to the project and what advantages and disadvantages Linux has. The project was to set up a trail run of Linux at the school I work at, in the hopes that by the discontinuation of Windows XP, we could move fully to Linux. Currently there are around 30 Netbooks, 4 desktops, and a mostly Linux servers with one Windows application server.

Advantages

In a short survey I found most people believed Linux to be faster. People also generally thought Linux was saving money. On two Pentium 4 desktops, memory was upgraded to 1GB and Ubuntu was installed. The difference was night and day in terms of performance. These computers would have had to been replaced otherwise. Surprisingly to me, users reported the Ubuntu machine to be much more stable! I’m probably a bit jaded after one too many x server crashes while switching monitors. I suspect I have more problems because I have higher demands, most work users don’t care about PPA’s and proprietary video card drivers. While not directly related, I’ve been installing Ubuntu on a considerable number of student’s laptops as they bring them in broken. I feel exposing them to an alternative to the consumer culture of buying the new version/computer every year is valuable. Most student’s don’t know there are other options than buying or pirating the latest software and replacing their hardware every two years. Ubuntu really shines on simple home applications, where the user just needs hassle free internet access. Being able to clone machines fairly easily is nice too. There only needs to be one image for the entire school. I don’t have the data to prove it yet, but I suspect the long term costs of supporting Linux will be minimal. The machines, once setup right, should be able to just sit and run. Only a few things, like Google Chrome and LibreOffice, really need updated. Chrome has a wonderful repo that it comes with that seems to work on any version.

Disadvantages

The time to get the image ready is immense! It’s about 20 times harder to get a Linux image ready than Windows. Printer support sucks. Auto detect never works ever for me. Some commercial laser printers just don’t work. Others require fiddling with the right ppd file. Even when you know what to do, the interface is terrible and involves waiting for it to time out over and over while searching for a driver you know it won’t find. Another problem is there just isn’t anything like folder redirection in Linux. You either have to put everything on nfs or samba. Or use a Dropbox like solution, which there aren’t even any decent open source implementations of. Our teachers need to take home work and have this be seamless. Can’t be done in Linux. Another issue is switching monitors. X will crash. Even one in 20 times is enough to not deploy, since that means lost work. Getting Active Directory support is a pain as previously discussed. The initial joining a domain isn’t hard, it’s all the little bugs and limitations. It also adds an extra minute to startup time.

Any Linux migration will probably involve LibreOffice. LO has problems. Power point import and export is terrible. Also it’s very buggy. It’s nice to have the latest version of LO, since it’s one the most used programs. But each update breaks the default configuration! I have to manually set the default file format each time, which is absurdly difficult.

sudo sed -i ‘s/<prop oor:name=”ooSetupFactoryDefaultFilter”><value>writer8<\/value>/<prop oor:name=”ooSetupFactoryDefaultFilter”><value>MS Word 97<\/value>/g’ /usr/lib/libreoffice/basis3.3/share/registry/writer.xcd

Calc is generally bad. Why does pressing ctrl-down go to row 1048576 instead of the last row with data? Why does EVERYTHING increment. If I type “ID 200” and drag it down, the next becomes “ID 201”, etc.  Auto filtering likes to remove itself when you switch sheets. It has some features though on Microsoft. I honestly don’t see how Excel gets away without having regular expressions. Also MS Office just can’t handle open document files. Students like to have lots of random file formats they somehow make. LibreOffice opens them all, while MS Office can’t even open MS works files! I’ve been a long time OpenOffice user. Recently I’ve been playing with MS Office just to see what others are using. Now I hate all office software. Don’t even get me started on that horrid ribbon.

Is it time to switch?

I have a lot of bad things to say about Linux. But trust me, if I reviewed Windows or OSX, well it would be like watching a Angry Video Game Nerd episode. I have little patience with bad technology. Linux is bad. It sucks. It really really sucks. But so does everything else. Building an operating system and surrounding environment is just hard. No one has it worked out yet. Linux is working out great in student netbooks and administrative staff desktops. I have no plans to deploy it on teacher laptops. No folder redirection and terrible monitor support are the blockers here. I’m really hoping by the end of life for XP, Linux will be better.

One huge factor in allowing me to play with switching users to Linux is web applications. I’m not a sys admin. My real job is a developer and I’ve been replacing costly, unfriendly applications with open source ones, including my own. For users who just need web access, Linux rocks! It’s faster, it’s stable, it’s better. It will reduce support costs and let you get away with not upgrading computers. Lose Outlook, Office, and your legacy Active X web apps, and the switch to Linux is easy. Next post I’ll update my progress on my school information system and future plans for educational administrative software. Stay tuned, I promise it will be more optimistic.