# HG changeset patch
# User Tero Marttila <terom@fixme.fi>
# Date 1295558474 -7200
# Node ID de6abcbd3c03907004d82e39c8008a8855e6d799
# Parent  4f4150296cd3f8f3bd98e4a53ba795fb8fa2b63e
items: add inventory-import script, and tweak listing

diff -r 4f4150296cd3 -r de6abcbd3c03 bin/inventory-import.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/bin/inventory-import.py	Thu Jan 20 23:21:14 2011 +0200
@@ -0,0 +1,238 @@
+#!/usr/bin/env python
+
+"""
+    Import inventory table from .csv
+"""
+
+from svv import application
+from svv import database as db
+from svv.items import Item
+
+import csv
+
+import optparse, logging
+
+log = logging.getLogger('inventory-import')
+
+def load_csv (path, encoding='utf-8') :
+    """
+        Load CSV data from given fs path, yielding the data as unicode strings.
+    """
+
+    reader = csv.reader(open(path, 'r'))
+
+    for row in reader :
+        # decode
+        yield [cell.decode(encoding) for cell in row]
+
+def parse_csv (csv,
+    header_rows=3,
+    header_columns=[u"Sijainti", u"Artikkeli", u"Lkm", u"Selite"],
+    column_offset=0,
+    column_count=4,
+    normalize_qty=True,
+) :
+    """
+        Parse item data from given csv:
+            
+            csv             - the csv.Reader object
+
+            header_rows     - number of column header row
+            header_columns  - set of column headers to detect
+            column_offset   - number of columns to skip
+            column_count    - number of columns to use
+            normalize_qty   - items with qty=1 aren't countable
+        
+        Yields a series of
+
+            (row_id, parent_row, name, qty, descr)
+
+        tuples.
+    """
+
+    # column def
+    column_def = ['location', 'name', 'qty', 'descr']
+
+    # row_id's for loaded items
+    row_by_name = {}
+
+
+    for row, columns in enumerate(csv) :
+        # fix up row number
+        row += 1
+
+        # fix up columns
+        columns = columns[column_offset:column_offset + column_count]
+
+        # header
+        if row < header_rows :
+            # skip
+            continue
+
+        elif row == header_rows :
+            # check header
+            if columns != header_columns :
+                # fail
+                raise Exception("Header mismatch, excepting %r, got %r on row %r" % (header_columns, columns, row))
+
+            # ok
+            continue
+
+        elif all(not col.strip() for col in columns) :
+            # empty row
+            continue
+        
+        elif len(columns) != len(column_def) :
+            # bad column count
+            raise Exception("Column count mismatch, expecting %r, got %r on row %r" % (column_def, columns, row))
+        
+        # load column
+        parent, name, qty, descr = columns
+        
+        # normalize
+        parent = parent.strip()
+        name = name.strip()
+        qty = qty.strip()
+        descr = descr.strip()
+        
+        # warn on whitespace
+        if [parent, name, qty, descr] != columns :
+            log.warning("Extra whitespace on row %d", row)
+
+        # convert
+        try :
+            qty = int(qty) if qty else None
+
+        except ValueError :
+            raise Exception("Invalid item quantity, got %r on row %r" % (qty, row))
+
+        # normalize quantity
+        if qty == 0 :
+            # skip
+            log.warning("Skipping qty=0 item %r on row %d", name, row)
+            
+        elif qty == 1 and normalize_qty :
+            # one item is a single item
+            qty = None
+
+        # map
+        if not parent :
+            parent = None
+
+        elif parent not in row_by_name :
+            raise Exception("Invalid location, got %r on row %r" % (parent, row))
+
+        else :
+            # lookup row_id
+            parent = row_by_name[parent]
+        
+        # debug
+        log.debug("Load %d: %s %s x%s -> %d", row, name, descr, qty, parent)
+        
+        # store
+        row_by_name[name] = row
+
+        # yield
+        yield row, parent, name, qty, descr
+
+def create_items (session, items) :
+    """
+        Create the given items and add them to the session
+    """
+
+    # lookup parents
+    items_by_row = {}
+
+    for row, parent, name, qty, descr in items :
+        # look up parent
+        if parent :
+            parent = items_by_row[parent]
+
+        # build
+        item = Item(name, descr, qty, parent)
+
+        # store
+        items_by_row[row] = item
+
+        # add
+        session.add(item)
+
+    # number of items added
+    return len(items_by_row)
+
+def main () :
+    parser = optparse.OptionParser(usage="Usage: %prog [options] CSV")
+
+    parser.add_option('-q', '--quiet', action='store_true', help='More output')
+    parser.add_option('-v', '--verbose', action='store_true', help='More output')
+    parser.add_option('-D', '--debug', action='store_true', help="Even more output (SQL queries)")
+
+    parser.add_option('-d', '--database', help="Database connection URI", metavar='URL')
+    parser.add_option(      '--init-database', action='store_true', help="Initialize database (CREATE)")
+
+    parser.add_option('-C', '--charset', help="CSV charset", metavar='CHARSET', default='utf-8')
+
+    parser.add_option('--dry-run', action='store_true', help="Execute INSERTs but don't actually commit()")
+
+    (options, args) = parser.parse_args()
+
+    # CSV from args
+    csv_path, = args
+    csv_encoding = options.charset
+    
+    # defaults
+    level = logging.INFO
+    sql_echo = False
+
+    if options.quiet :
+        level = logging.WARN
+
+    if options.verbose :
+        level = logging.DEBUG
+    
+    if options.debug :
+        level = logging.DEBUG
+        sql_echo = True
+
+    logging.basicConfig(format="[%(levelname)5s] %(funcName)25s : %(message)s", level=level)
+
+    if sql_echo :
+        # echo SQL queries
+        logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
+
+    # app state
+    app = application.Application(
+            options.database,
+    )
+
+    # init?
+    if options.init_database :
+        app.create_tables()
+
+    # load CSV data
+    csv = load_csv(csv_path, encoding=csv_encoding)
+
+    # parse info
+    item_data = list(parse_csv(csv))
+    
+    # db session
+    session = app.session()
+
+    # inserts
+    count = create_items(session, item_data)
+    
+    if options.dry_run :
+        log.info("Rolling back %d INSERTs for --dry-run...", count)
+
+        # revert
+        session.rollback()
+
+    else :
+        log.info("Committing %d new items..", count)
+
+        # ok, commit
+        session.commit()
+
+if __name__ == '__main__' :
+    main()
+
diff -r 4f4150296cd3 -r de6abcbd3c03 static/tables.css
--- a/static/tables.css	Thu Jan 20 23:14:07 2011 +0200
+++ b/static/tables.css	Thu Jan 20 23:21:14 2011 +0200
@@ -52,7 +52,7 @@
 
 tr.alternate
 {
-    background-color: #e8e8e8;
+    background-color: #f1f1f1;
 }
 
 /* Link to tr with URL #foo fragment */
@@ -68,6 +68,12 @@
     padding: 0.25em 1em;
 }
 
+table.with-borders td,
+table.with-borders th
+{
+    border: thin dotted #aaa;
+}
+
 td a,
 th a
 {
diff -r 4f4150296cd3 -r de6abcbd3c03 svv/database.py
--- a/svv/database.py	Thu Jan 20 23:14:07 2011 +0200
+++ b/svv/database.py	Thu Jan 20 23:21:14 2011 +0200
@@ -7,6 +7,11 @@
 from sqlalchemy import *
 
 #
+# SQL clauses
+#
+from sqlalchemy.sql import functions
+
+#
 # Object Mapping
 # (used externally)
 #
diff -r 4f4150296cd3 -r de6abcbd3c03 svv/items.py
--- a/svv/items.py	Thu Jan 20 23:14:07 2011 +0200
+++ b/svv/items.py	Thu Jan 20 23:21:14 2011 +0200
@@ -14,6 +14,13 @@
         Data-mapping for the items table
     """
 
+    def __init__ (self, name, detail=None, quantity=None, parent=None) :
+
+        self.name = name
+        self.detail = detail
+        self.quantity = quantity
+        self.parent = parent
+
 db.mapper(Item, db.items, properties=dict(
     # forward ref to parent
     parent      = db.relation(Item, remote_side=db.items.c.id, 
@@ -547,6 +554,42 @@
         """
 
         return self.handler.url_for(*args, **kwargs)
+    
+    def render_rows (self, items, parent=None) :
+        """
+            Render induvidual rows
+        """
+        
+        for row, item in enumerate(items) :
+            yield html.tr(id=('item-%d' % item.id), class_=('alternate' if row % 2 else None))(
+                html.td(
+                    html.a(href=self.url_for(InventoryView, fragment=('item-%d' % item.id)))(
+                        u'#%d' % item.id
+                    )
+                ),
+
+                (
+                    html.td(
+                        html.a(href=self.url_for(ItemView, id=item.parent.id))(
+                            item.parent.name
+                        ) if item.parent else None
+                    ),
+                ) if not parent else None,
+
+                html.td(
+                    html.a(href=self.url_for(ItemView, id=item.id))(
+                        item.name
+                    )
+                ),
+
+                html.td(
+                    item.detail
+                ),
+
+                html.td(
+                    "%d kpl" % item.quantity if item.quantity else None
+                ),
+            )
 
     def render (self, items, parent=None) :
         """
@@ -554,7 +597,7 @@
             item.
         """
 
-        return html.table(
+        return html.table(class_='with-borders')(
             html.caption(
                 u"Kalustolistaus",
                 (
@@ -577,33 +620,7 @@
             ),
 
             html.tbody(
-                html.tr(id=('item-%d' % item.id))(
-                    html.td(
-                        html.a(href=self.url_for(InventoryView, fragment=('item-%d' % item.id)))(
-                            u'#%d' % item.id
-                        )
-                    ),
-
-                    html.td(
-                        html.a(href=self.url_for(ItemView, id=item.parent.id))(
-                            item.parent.name
-                        ) if item.parent else None
-                    ) if not parent else None,
-
-                    html.td(
-                        html.a(href=self.url_for(ItemView, id=item.id))(
-                            item.name
-                        )
-                    ),
-
-                    html.td(
-                        item.detail
-                    ),
-
-                    html.td(
-                        "%d kpl" % item.quantity if item.quantity else None
-                    ),
-                ) for item in items
+                self.render_rows(items, parent)
             )
         )
 
@@ -623,7 +640,9 @@
         """
 
         # listing of inventory items
-        items = self.session.query(Item).order_by(Item.parent).all()
+        # group items together by parent; along with the parent item itself
+        # XXX: this only works correctly for top-level parents
+        items = self.session.query(Item).order_by(db.functions.coalesce(Item.parent_id, Item.id), Item.id).all()
         
         return ItemTable(self).render(items)