path: root/lib/d2/bin/d2_svg_processor.py

import os
import argparse
import json
import stat
import re
import xlrd
from collections import namedtuple
from lxml import etree
from copy import deepcopy
from copy import copy
from sqlalchemy import MetaData
from d2.app.adapters.detail import DetailAdapter
from d2.app.adapters.search import SearchAdapter
from d2.app.adapters.forge import ForgeAdapter
from d2.app.model.static import StaticData
from d2.config import Config
from d2.db import Base
from d2.db import Detail 
from d2.db import Plot
from d2.db import LabelCoordinate
from d2.db import Occupant

CLEVELAND_MAP_ID = 1

ROOM_3D1_INKSCAPE_XMAX = 1800

ROOM_X_RANGE = {'3D1': {'xmin': 0,
                        'xmax': 1800},
                '3A1': {'xmin': 3450,
                        'xmax': 4500}
               }

NSMAP = {'svg': 'http://www.w3.org/2000/svg',
     'xlink': "http://www.w3.org/1999/xlink"}
NSMAP2 = {None: 'http://www.w3.org/2000/svg'}

ROOMS = [u'3A1', u'3B1', u'3C1', u'3D1']

NTWK_NUMBER_ID = {'3D13A1': 'NTWK_NUMBER',
               '3C1': 'NTWK_NUMBER-7',
               '3B1': 'NTWK_NUMBER-1'}

NAME_LABEL_ID = {'3D13A1': 'NAME',
                 '3C1': 'NAME-2',
                 '3B1': 'NAME-6'}

CONFERENCE_LABEL_ID = {'3C1': 'AREA_ID-6',
                       '3B1': 'AREA_ID-0',
                       '3D13A1': 'AREA_ID'}

MAIN_ROOM_NODES = ['draft', 'draft-3', 'draft-4']

GROUP_ID_ROOM = {'draft': '3D13A1',
                 'draft-3': '3C1',
                 'draft-4': '3B1'}

DISCARD_IDS = ['#REFR', '#REFR-3']

NAME_CORRECTIONS = {u'Deb Smith': u'Deborah Smith',
                    u'Ron Lacey': u'Ron Lacy',
                    u'Ben Smith': u'Benjamin Smith',
                    u'Heather Zong': u'Heather Zhong',
                    u'Andrew Schulack': u'Andrew Schulak',
                    u'Chris Miller': u'Christopher Miller',
                    u'Joseph Betcher': u'Joseph Bechter',
                    u'Jenn Miller': u'Jennifer Miller',
                    u'Dave Passarello': u'Dave Passerallo',
                    u'Jason Stavicak': u'Jason Staviscak',
                    u'Eric Fenderbosh': u'Eric Fenderbosch',
                    u'Len Thomas': u'Leonard Thomas',
                    u'Carrie Daple': u'Carrie Dalpe',
                    u'Noha Yousseff': u'Noha Youssef',
                    u'Sarah Dodge (to be Kopacko)': u'Sarah Dodge',
                    u'Monica Fisher (was Hrivnak)': u'Monica Fisher',
                    u'Keith Cocoran': u'Keith Corcoran',
                    u'Jane Carpenter-Lamb': u'Jane Carpenter',
                    u'Nanette Weidl': u'Nanette Wiedl',
                    u'Kathi Fai': u'Kathi Fay',
                    u'Sally Babcock': u'Sally Schriner',
                    u'Brian Staneck': u'Brian Stanek'}

floor_plan_by_ntwk_id = {}

floor_plan_by_name = {}

ntwk_number_data = {}

manually_added_plot = {}

NTWK_ID_PATTERN = re.compile("([A-Z]+)(\d+)")

class Args(object):

    DESCRIPTION = "Process svg for d2"
    DEFAULT_MASTER_SVG = 'combined_master_v6.svg'
    DEFAULT_FLOOR_PLAN_XLS = 'FloorPlan1.xls'
    DEFAULT_CONFERENCE_ROOM_XLS = 'ConferenceWorkroom.xls'

    MASTER_SVG_HELP = "The full path of the master svg. "\
                      "(default: {default})"

    FLOOR_PLAN_XLS_HELP = "The full path of the floor plan xls. "\
                      "(default: {default})"

    CONFERENCE_ROOM_XLS_HELP = "The full path of the conference "\
                      "room xls. (default: {default})"

    MASTER_OUTPUT_DIR_HELP = "The full path of the output directory. "\
                     '(default: {default})'

    def __init__(self, config):
        self._config = config
        self._parser_ = None

    @classmethod
    def load(cls, config=None):
        config = config or Config.load()
        return cls(config)

    @classmethod
    def parse(cls, config=None):
        config = config or Config.load()
        obj = cls(config)
        return obj()

    def __call__(self):
        self._master_svg_file()
        self._floor_plan_xls()
        self._conference_room_xls()
        self._output_directory()
        args = self.parser.parse_args()
        args.config = self._config
        args.project = self._config.project
        args.db = self._config.db
        args.log = self._config.log
        args.data_directory = self._config.data_directory

        return args

    @property
    def parser(self):
        if not self._parser_:
            self._parser_ = argparse.ArgumentParser(
                            description=self.DESCRIPTION)
        return self._parser_

    def _master_svg_file(self):
        default_path = os.path.join(
                          self._config.static_directory,
                          'svgs',
                          self.DEFAULT_MASTER_SVG)
        self.parser.add_argument(
                '--master-svg',
                type=argparse.FileType('r'),
                default=default_path,
                help=self.MASTER_SVG_HELP.format(default=default_path)
        )

    def _floor_plan_xls(self):
        default_path = os.path.join(
                         self._config.data_directory,
                         self.DEFAULT_FLOOR_PLAN_XLS)
        self.parser.add_argument(
                '--floor-plan-xls',
                type=str,
                default=default_path,
                help=self.FLOOR_PLAN_XLS_HELP.format(default=default_path)
        )

    def _conference_room_xls(self):
        default_path = os.path.join(
                         self._config.data_directory,
                         self.DEFAULT_CONFERENCE_ROOM_XLS)
        self.parser.add_argument(
                '--conference-room-xls',
                type=str,
                default=default_path,
                help=self.CONFERENCE_ROOM_XLS_HELP.format(default=default_path)
        )

    def _output_directory(self):
        default_path = os.path.join(
                          self._config.static_directory,
                          'svgs')
        self.parser.add_argument(
                '--output-directory',
                type=str,
                default=default_path,
                help=self.MASTER_OUTPUT_DIR_HELP.format(default=default_path)
        )

class ParseFloorPlanConferenceRoomXLS(object):
    """
    after process the xls sheet, info is kept in two dictionaries:

    floor_plan_by_ntwk_id[room_name][ntwk_id] = [name, net_jack1, net_jack2]
    floor_plan_by_name[room_name][name] = [ntwk_id]
    """

    MULTIPLE_NTWK_ID_IN_ONE_ROOM_ERROR = "Data Error: floor plan xls has "\
                             "multiple records for ntwk_id {0} in room {1}"
    MULTIPLE_NAME_IN_ONE_ROOM_ERROR = "Warning: floor plan xls has "\
                             "multiple records for name {0} in room {1}"

    def __init__(self):
        self._log_obj = None 
        self._make_cube_info = namedtuple('cubeInfo', 'name net_jack1 net_jack2')
        self._make_plot = namedtuple('addedPlot', 'abs_x, abs_y')

    def _determine_room(self, s):
        """@params s: spread sheet's name, such as "QA 3A1" contains
                      the room name
           @return: return the room name
        """

        for room in ROOMS:
            if s.find(room) > -1:
                return room
        return ''

    def _process_sheet(self, sh, room_name):
        for rownum in range(2, sh.nrows):
            name = unicode(sh.cell(rowx=rownum, colx=0).value)
            cube = sh.cell(rowx=rownum, colx=2).value
            if name and cube:
                net_jack1 = unicode(sh.cell(rowx=rownum, colx=3).value)
                net_jack2 = unicode(sh.cell(rowx=rownum, colx=4).value)
                if room_name not in floor_plan_by_ntwk_id:
                    floor_plan_by_ntwk_id[room_name] = {}
                if room_name not in floor_plan_by_name:
                    floor_plan_by_name[room_name] = {}

                if cube in floor_plan_by_ntwk_id[room_name]:
                    self._log_obj.error(
                        self.MULTIPLE_NTWK_ID_IN_ONE_ROOM_ERROR.format(
                                                                  cube,
                                                            room_name))
                else:
                    floor_plan_by_ntwk_id[room_name][cube] = \
                            self._make_cube_info(name, net_jack1, net_jack2)

                if name.lower() in floor_plan_by_name[room_name]:
                    self._log_obj.error(
                        self.MULTIPLE_NAME_IN_ONE_ROOM_ERROR.format(
                                                            name.lower(),
                                                              room_name))
                else:
                    floor_plan_by_name[room_name][name.lower()] = cube 
 
                if cube[:2] == 'ID':
                    if room_name not in manually_added_plot:
                        manually_added_plot[room_name] = {}
                    manually_added_plot[room_name][cube] = self._make_plot( 
                                         sh.cell(rowx=rownum, colx=8).value,
                                         sh.cell(rowx=rownum, colx=9).value)



    def __call__(self, args):
        self._log_obj = args.log
        for file_obj in (args.floor_plan_xls, args.conference_room_xls):
            wb = xlrd.open_workbook(file_obj)
            for sheet_name in wb.sheet_names():
                room_name = self._determine_room(sheet_name)
                if room_name:
                    self._process_sheet(wb.sheet_by_name(sheet_name), room_name)


class CreateStructureSVG(object):

    OUTPUT_FILENAME = 'structure.svg'

    REG_KEEP_ELEMENTS = re.compile("(FIXED_OUTLINE$)|(FIXED_OUTLINE-\d+)|"\
         "(FURN-8$)|(FINISHES-8$)|"\
         "(OUTLN$)|(OUTLN-\d+)|"\
         "(PANELS$)|(PANELS-\d+)|"\
         "(g141075$)|(g163196$)|(g55578$)|"\
         "(WALL$)|(WALL-\d+)")

    REG_PAT_1 = re.compile('ID_(.*)_PANEL', re.IGNORECASE)
    REG_PAT_2 = re.compile('ID_(.*)_pan', re.IGNORECASE)
    REG_PAT_3 = re.compile('ID_(.*)_panels', re.IGNORECASE)

    REG_PANELS = [REG_PAT_1, REG_PAT_2, REG_PAT_3]

    def __init__(self):
        self.input_svg = ""

    def _process_def(self, parent_el, def_el):
        """remove panel detail numbers.
           There are numbers, such as 25/42, 45/65, etc around the panel
            walls, we remove them from the element in def section
        """
        def_node = etree.SubElement(parent_el, def_el.tag,
                                              def_el.attrib)
        for el in def_el:
            if el.tag == '{{{0}}}g'.format(NSMAP['svg']):
                el_id = el.attrib['id']
                is_panel_node = False
                for reg_pat in self.REG_PANELS:
                    if reg_pat.match(el_id):
                        Util.remove_text_node(def_node, el)
                        is_panel_node = True
                        break
                if not is_panel_node:
                    def_node.append(el)
            else:
                def_node.append(el)

    def _remove_all_texts(self, parent_node, the_el):
        """ we do not want to include WA, CAGE and bunch of other text nodes
        """
        the_node = etree.SubElement(parent_node, the_el.tag,
                                               the_el.attrib)
        for el in the_el:
            if el.tag != '{{{0}}}text'.format(NSMAP['svg']):
                the_node.append(el)

    def _get_room_structure_svg(self, parent_el, el, room_name):
        group_node = etree.SubElement(parent_el, el.tag,
                                                   el.attrib)
        for sub_el in el:
            if self.REG_KEEP_ELEMENTS.match(sub_el.attrib['id']):
                self._remove_all_texts(group_node, sub_el)

    def _get_structure_svg(self):
        data = etree.XML(self.input_svg)

        #create svg root element:
        parser = etree.XMLParser()
        structure_svg = parser.makeelement(
           '{{{0}}}{1}'.format(NSMAP['svg'], 'svg'), nsmap = NSMAP2)
        #copy the root element attributes to the newly created svgs:
        root = data.getroottree().getroot()

        for key, value in root.attrib.iteritems():
            structure_svg.attrib[key] = value

        #main_el includes defs, meta info and one main group node
        #that includes a translation
        for main_el in data:
            if main_el.tag == '{{{0}}}defs'.format(NSMAP['svg']):
                self._process_def(structure_svg, main_el)
            elif main_el.tag == '{{{0}}}g'.format(NSMAP['svg']):
                group_node = etree.SubElement(structure_svg, main_el.tag,
                                                   main_el.attrib)
                for el in main_el:
                    if el.attrib['id'] in MAIN_ROOM_NODES:
                        room_name = GROUP_ID_ROOM.get(el.attrib['id'])
                        self._get_room_structure_svg(group_node, el, room_name)
                else:
                    main_el.append(el)
            else:
                structure_svg.append(main_el)

        self._remove_extra_nodes(structure_svg)

        return etree.tostring(structure_svg)

    def _remove_extra_nodes(self, top_node):
        for href_to_avoid in DISCARD_IDS:
            Util.remove_node_by_href_id(top_node, href_to_avoid)

    def __call__(self, args):
        master_svg_fileobj = args.master_svg
        output_filepath = os.path.join(
                             args.output_directory,
                             args.project + '_' + self.OUTPUT_FILENAME
                          )
        self.input_svg = master_svg_fileobj.read()
        master_svg_fileobj.seek(0, os.SEEK_SET)

        with open(output_filepath, 'wb') as output_fileobj:
            output_fileobj.write(self._get_structure_svg())

class PopulateTables(object):

    OUTPUT_NTWK_ID_FILENAME = 'ntwk_id.svg'
    TABLES = [u'forge', u'label_coordinate', u'plot']

    TRANSFORM_MATRIX_ERROR = "Error: transform matrix of {0} not implemented"
    DUPLICATE_NTWK_LABEL_ERROR = "Data Error: master svg has duplicate "\
                                 "network  label in same room: {0}, {1}"    
    NO_CLOSEBY_NTWK_ID_ERROR = "Data Error: cannot find closest_ntwk_id "\
               "in room_name: {0}, x: {1} y: {2}, current name shown {3}"
    NO_AREA_INFO_ERROR = "Data Error: cannot find area info for {0} {1}"
    NO_OCCUPANT_DETAIL = "Data Error: cannot find {0} in occupant_detail"


    def __init__(self):
        self.input_svg = ""
        self._db = None
        self._log_obj = None

    def _get_inkscape_coord(self, matrices, x, y,
                                  view_box_xmin,
                                  view_box_ymin,
                                  view_box_height):
        (x_new, y_new) = self._get_new_coord(matrices, float(x), float(y))

        x_inkscape = x_new - view_box_xmin
        y_inkscape = view_box_height - y_new + view_box_ymin

        return (x_inkscape, y_inkscape)

    def _get_new_coord(self, matrices, x, y):
        """ matrix is of format: matrix(a b c d e f)
            ret_x = a * x + c * y + e
            ret_y = b * x + d * y + f

            or translate(tx, ty)
            ret_x = x + tx
            ret_y = y + ty
        """

        ret_x = x
        ret_y = y

        for matrix in matrices:
            if matrix[0:6] == "matrix":
                matrix_element_list = map(float, matrix[7:-1].split(','))
                ret_x = matrix_element_list[0] * x \
                        + matrix_element_list[2] * y \
                        + matrix_element_list[4]
                ret_y = matrix_element_list[1] * x \
                        + matrix_element_list[3] * y \
                        + matrix_element_list[5]
            elif matrix[0:9] == "translate":
                (tx, ty) = map(float, matrix[10:-1].split(','))
                ret_x = x + tx
                ret_y = y + ty
            else:
                raise Exception(self.TRANSFORM_MATRIX_ERROR.format(matrix))

            x = ret_x
            y = ret_y

        return (ret_x, ret_y)

    def _get_room_ntwk_id_svg(self, parent_node, node, room_name,
                              view_box_xmin,
                              view_box_ymin,
                              view_box_height,
                              main_el_matrix):
        # save the room's transform matrix
        room_matrix = node.attrib['transform']

        base_matrices = [room_matrix, main_el_matrix]

        # Append group element with id to ntwk_id_svg
        ntwk_group = etree.SubElement(parent_node, "g",
                                   transform=node.attrib['transform'],
                                   id="{0}_NTWK_NUMBER".format(room_name))

        room_ntwk_id = NTWK_NUMBER_ID.get(room_name)
        for el in node:
            if el.attrib['id'] == room_ntwk_id:
                for sub_el in el:
                    if sub_el.tag == '{{{0}}}text'.format(NSMAP['svg']):
                        tspan_elem = sub_el.xpath('./svg:tspan',
                                                  namespaces=NSMAP)[0]
                        ntwk_id = tspan_elem.text
                        #tspan_elem.attrib['font-size'] = "36"
                        ntwk_group.append(sub_el)
                        transform_matrix = sub_el.attrib['transform']
                        matrices = list(base_matrices)
                        matrices.insert(0, transform_matrix)
                        (abs_x, abs_y) = self._get_inkscape_coord(matrices,
                                                     sub_el.attrib['x'],
                                                     sub_el.attrib['y'],
                                                     view_box_xmin,
                                                     view_box_ymin,
                                                     view_box_height)
                        #print abs_x, abs_y
                        plot_id = self._add_to_tables(abs_x, abs_y, ntwk_id)
                        self._add_to_ntwk_number_data(abs_x, abs_y,
                                                      room_name,
                                                      ntwk_id,
                                                      plot_id)

    def _add_manually_added_plots(self):
        #TODO:  manually added plots need to be in svg as well
        for (room_name, info) in manually_added_plot.items():
            for (ntwk_id, xy_info) in info.items():
                print ntwk_id, xy_info.abs_x, xy_info.abs_y
                plot_id = self._add_to_tables(xy_info.abs_x, 
                                              xy_info.abs_y, 
                                              ntwk_id)
                self._add_to_ntwk_number_data(xy_info.abs_x, 
                                              xy_info.abs_y,
                                              room_name,
                                              ntwk_id,
                                              plot_id)


    def _add_to_tables(self, x, y, ntwk_id):
        """ insert into plot table, 
            add an empty user to the plot in forge table,
            and ntwk_id in detail tables
        """
        plot_data = dict(x = x,
                         y = y,
                         rotation = 0,
                         width = None,
                         height = None,
                         altitude = None,
                         map_id = CLEVELAND_MAP_ID)
        obj = Plot.from_dict(plot_data)
        self._db.session.add(obj)
        self._db.session.commit()
        plot_id = obj.id

        self._forge_adapter.add_empty_user(plot_id)

        self._detail_adapter.insert_plot_detail(plot_id, 
                                  self._static.detail_type.network_id,
                                  unicode(ntwk_id))

        return plot_id

    def _add_to_ntwk_number_data(self,
                                 x,
                                 y,
                                 room_name,
                                 ntwk_id,
                                 plot_id):
        """ ntwk_number_data has the structure of
            {room_name: {ntwk_id: {x: x, y:y, plot_id:plot_id}}}
        """
        if room_name == '3D13A1':
            room_name = self._determine_3D1_or_3A1(x)

        if room_name not in ntwk_number_data:
            ntwk_number_data[room_name] = {}
        if ntwk_id in ntwk_number_data[room_name]:
            self._log_obj.error(self.DUPLICATE_NTWK_LABEL_ERROR.format(
                                                                room_name,
                                                                  ntwk_id))
        else:
            ntwk_number_data[room_name][ntwk_id] = {'x': x,
                                                    'y': y,
                                                    'plot_id': plot_id}

    def _determine_3D1_or_3A1(self, x):

        room = ''

        if (x > ROOM_X_RANGE['3D1']['xmin']) and\
           (x < ROOM_X_RANGE['3D1']['xmax']):
            room = '3D1'
        elif x > ROOM_X_RANGE['3A1']['xmin']:
            room = '3A1'

        return room

    def _get_ntwk_id_svg(self):
        data = etree.XML(self.input_svg)

        #create svg root element:
        parser = etree.XMLParser()
        ntwk_id_svg = parser.makeelement(
                '{{{0}}}{1}'.format(NSMAP['svg'], 'svg'),
                                          nsmap = NSMAP2)
        #copy the root element attributes to the newly created svgs:
        root = data.getroottree().getroot()
        for key, value in root.attrib.iteritems():
            ntwk_id_svg.attrib[key] = value

        view_box = root.attrib['viewBox']
        (view_box_xmin, view_box_ymin, view_box_width, view_box_height) = \
                                    map(float, re.split('\s+', view_box[:]))

        for main_el in data:
            if main_el.tag == '{{{0}}}g'.format(NSMAP['svg']):
                main_el_matrix = main_el.attrib['transform']
                group_node = etree.SubElement(ntwk_id_svg, main_el.tag,
                                                   main_el.attrib)
                for el in main_el:
                    if el.attrib['id'] in MAIN_ROOM_NODES:
                        room_name = GROUP_ID_ROOM.get(el.attrib['id'])
                        self._get_room_ntwk_id_svg(group_node, el, room_name,
                                                   view_box_xmin,
                                                   view_box_ymin,
                                                   view_box_height,
                                                   main_el_matrix)
        self._add_manually_added_plots()
        return etree.tostring(ntwk_id_svg)


    def _get_name_label_svg(self):
        data = etree.XML(self.input_svg)

        #create svg root element:
        parser = etree.XMLParser()
        name_label_svg = parser.makeelement(
                             '{{{0}}}{1}'.format(NSMAP['svg'], 'svg'), 
                             nsmap = NSMAP2)
        #copy the root element attributes to the newly created svgs:
        root = data.getroottree().getroot()
        for key, value in root.attrib.iteritems():
            name_label_svg.attrib[key] = value

        view_box = root.attrib['viewBox']
        (view_box_xmin, view_box_ymin, view_box_width, view_box_height) = \
                                    map(float, re.split('\s+', view_box[:]))

        for main_el in data:
            if main_el.tag == '{{{0}}}g'.format(NSMAP['svg']):
                main_el_matrix = main_el.attrib['transform']
                group_node = etree.SubElement(name_label_svg, main_el.tag,
                                                   main_el.attrib)
                for el in main_el:
                    if el.attrib['id'] in MAIN_ROOM_NODES:
                        room_name = GROUP_ID_ROOM.get(el.attrib['id'])
                        self._get_room_name_label_svg(group_node,
                                                   el,
                                                   room_name,
                                                   view_box_xmin,
                                                   view_box_ymin,
                                                   view_box_height,
                                                   main_el_matrix)
        return etree.tostring(name_label_svg)


    def _get_room_name_label_svg(self,
                                 parent_node,
                                 node,
                                 approx_room_name,
                                 view_box_xmin,
                                 view_box_ymin,
                                 view_box_height,
                                 main_el_matrix):

        # save the room's transform matrix
        room_matrix = node.attrib['transform']

        base_matrices = [room_matrix, main_el_matrix]

        name_label_group = etree.SubElement(parent_node, "g",
                                   transform=node.attrib['transform'],
                                   id="{0}_NAME_LABEL".format(approx_room_name))

        room_name_label_id = NAME_LABEL_ID.get(approx_room_name)
        room_area_label_id = CONFERENCE_LABEL_ID.get(approx_room_name)
        for el in node:
            if el.attrib['id'] == room_name_label_id or \
               el.attrib['id'] == room_area_label_id:
                for sub_el in el:
                    if sub_el.tag == '{{{0}}}text'.format(NSMAP['svg']):
                        tspan_elem = sub_el.xpath('./svg:tspan',
                                                  namespaces=NSMAP)[0]
                        current_name_shown = ""
                        tspan_elem = sub_el.xpath('./svg:tspan', 
                                               namespaces=NSMAP)
                        for ts in tspan_elem:
                            current_name_shown += ts.text + " "
                        current_name_shown = current_name_shown[:-1]

                        transform_matrix = sub_el.attrib['transform']
                        matrices = list(base_matrices)
                        matrices.insert(0, transform_matrix)
                        (abs_x, abs_y) = self._get_inkscape_coord(matrices,
                                                     sub_el.attrib['x'],
                                                     sub_el.attrib['y'],
                                                     view_box_xmin,
                                                     view_box_ymin,
                                                     view_box_height)
                        if approx_room_name == '3D13A1':
                            room_name = self._determine_3D1_or_3A1(abs_x)
                        else:
                            room_name = approx_room_name

                        if room_name: #ignore cubes in btw rooms
                            closest_ntwk_id = self._get_closest_ntwk_id(
                                                         room_name,
                                                         abs_x,
                                                         abs_y)
                            if el.attrib['id'] == "AREA_ID-6":
                                print "closest_ntwk_id: ", closest_ntwk_id, room_name

                            if not closest_ntwk_id:
                                print "looking for XXX{0}XXX".format(current_name_shown.lower())
                                # try to find ntwk_id using floor_plan_by_name
                                if current_name_shown.lower() in \
                                       floor_plan_by_name[room_name]:
                                    closest_ntwk_id = floor_plan_by_name[
                                                         room_name][
                                         current_name_shown.lower()]
                                    print "USE floor_plan_by_name ", closest_ntwk_id
                                else:
                                    # try to trim "conference room" for match
                                    conf_room_name = current_name_shown.lower()\
                                                .replace(" conference room", "")
                                    print "looking for XXX{0}XXX".format(conf_room_name)
                                    if conf_room_name in \
                                       floor_plan_by_name[room_name]:
                                        closest_ntwk_id = floor_plan_by_name[
                                                         room_name][
                                                     conf_room_name]
                                        print "USE floor_plan_by_name ", closest_ntwk_id
                            if not closest_ntwk_id:
                                self._log_obj.error(
                                     self.NO_CLOSEBY_NTWK_ID_ERROR.format(
                                                                   room_name, 
                                                                       abs_x, 
                                                                       abs_y,
                                                          current_name_shown))
                            else:
                                info = self._get_area_info(room_name,
                                                           closest_ntwk_id)
                                if info:
                                    plot_id = ntwk_number_data[room_name]\
                                                          [closest_ntwk_id]\
                                                          ['plot_id']
                                    self._add_ntwk_jacks(plot_id, info)
                                    self._add_label_coord(plot_id, abs_x, abs_y)
                                    occupant_id = self._find_occupant_id(info)
                                    if occupant_id:
                                        self._add_to_forge(plot_id, occupant_id)


    def _create_tables(self):
        for name in reversed(self.TABLES):
            table = Base.metadata.tables[name]
            table.create(bind=self._config.db.engine)
            self._log_obj.info("Creating {0}".format(name))

    def _drop_tables(self):
        for name in self.TABLES:
            if name in self.current_db_metadata.tables.keys():
                table = self.current_db_metadata.tables[name]
                table.drop(bind=self._config.db.engine)
                self._log_obj.info("Dropping {0}".format(name))

    def _drop_plot_details_records(self):
        # fetch all plot detail rows from detail table
        records = self._db.session.query(Detail).filter(
                Detail.plot_id != None).all()

        for record in records:
            self._db.session.delete(record)
            self._db.session.commit()

    @property
    def current_db_metadata(self):
        meta = MetaData()

        if ('schema' in self._config.sections['db']
                and self._config.sections['db']['schema']):
            meta.reflect(
                bind=self._config.db.engine,
                schema=self._config.sections['db']['schema']
            )
        else:
            meta.reflect(
                bind=self._config.db.engine
            )
        return meta

    def _add_label_coord(self, plot_id, x, y):
        label_coord_data = dict(plot_id = plot_id,
            detail_type_id = self._static.detail_type.name, 
            x = x,
            y = y)
        obj = LabelCoordinate.from_dict(label_coord_data)
        self._db.session.add(obj)
        self._db.session.commit()

    def _add_to_forge(self, plot_id, occupant_id):
        self._forge_adapter.add_occupant_to_plot(plot_id,
                                                occupant_id)

    def _get_closest_ntwk_id(self, room_name, x, y):
        """ given x, y, looping through all ntwk_id x and y
            values in the same room finding the closet ntwk_id
        """
        min_dist = 9999
        closest_ntwk_id = ""
        for (ntwk_id, coord) in ntwk_number_data[room_name].iteritems():
            dist = Util.get_distance(x, y, coord["x"], coord["y"])
            if dist < min_dist:
                min_dist = dist
                closest_ntwk_id = ntwk_id

        return closest_ntwk_id


    def _get_normalized_ntwk_id(self, ntwk_id):
        """ netwk_id is listed as AF09 in floor_plan,
            it is listed as AF9 in dwg file
            @params ntwk_id is of the format shown in dwg file
            @return floor_plan format
        """
        normalized_ntwk_id = ntwk_id
        matched = NTWK_ID_PATTERN.match(ntwk_id)
        if matched:
            (letters, numbers) = matched.groups()
            if len(numbers) == 1:
                normalized_ntwk_id = letters + '0' + numbers
        return normalized_ntwk_id

    def _get_area_info(self, room_name, ntwk_id):
        """ given room_name, ntwk_id, look up in floor_plan
            to return occupant's name, associated net_jack1,
            net_jack2 information
        """
        info = None
        if ntwk_id in floor_plan_by_ntwk_id[room_name]:
            info = floor_plan_by_ntwk_id[room_name][ntwk_id]
        else:
            normalized_id = self._get_normalized_ntwk_id(ntwk_id)
            if normalized_id in floor_plan_by_ntwk_id[room_name]:
                info = floor_plan_by_ntwk_id[room_name][normalized_id]
            else:
                # try to add these weird symbols at the end...
                normalized_id_with_C = normalized_id + "-C"
                if normalized_id_with_C in floor_plan_by_ntwk_id[room_name]:
                    info = floor_plan_by_ntwk_id[room_name][
                                       normalized_id_with_C]
                else:
                    # try to add these weird symbols...
                    normalized_id_IDF = normalized_id + "/IDF"
                    if normalized_id_IDF in floor_plan_by_ntwk_id[room_name]:
                        info = floor_plan_by_ntwk_id[room_name][
                                              normalized_id_IDF]
                    else:
                        self._log_obj.error(self.NO_AREA_INFO_ERROR.format(
                                                              room_name,
                                                                ntwk_id))
        return info

    def _get_first_last_name(self, s):
        """ fix bad spellings and other variations of the names
            appearing in the spreadsheet
        """
        if NAME_CORRECTIONS.get(s):
             s = NAME_CORRECTIONS[s]
        first_space_idx = unicode(s).find(' ')
        first_name = s[0:first_space_idx]
        last_name = s[first_space_idx+1:]
        return (first_name.lower().strip(), last_name.lower().strip())

    def _find_occupant_id(self, info):
        occupant_id = None
        if info.name == "Open":
            empty = self._db.session.query(Occupant).filter(
                 "occupant_type_id=:occupant_type_id").params(
                 occupant_type_id=self._static.occupant_type.empty).first()
            occupant_id = empty.id
        else:
            (first_name, last_name) = self._get_first_last_name(info.name)
            records = self._detail_adapter.fetch_detail_by_type_data(
                        self._static.detail_type.last_name,
                        last_name)
            if len(records) == 1:
                occupant_id = records[0].occupant_id
            else:
                for record in records:
                    rec = self._detail_adapter.fetch_occupant_detail(
                            record.occupant_id,
                            self._static.detail_type.first_name)
                    if first_name == rec.data.lower():
                        occupant_id = rec.occupant_id
                        break

        # last resort try to do a search
        if not occupant_id:
            detail = self._search_adapter.search(info.name)
            if len(detail) == 1:
                occupant_id = detail[0].occupant_id

        if not occupant_id:
            self._log_obj.error(self.NO_OCCUPANT_DETAIL.format(info.name))

        return occupant_id

    def _add_ntwk_jacks(self, plot_id, info):
        if info: 
            self._detail_adapter.insert_plot_detail(plot_id,
                    self._static.detail_type.network_jack,
                    info.net_jack1)

            self._detail_adapter.insert_plot_detail(plot_id,
                    self._static.detail_type.network_jack,
                    info.net_jack2)

    def __call__(self, args):
        self._config = args.config
        self._db = args.db
        self._log_obj = args.log
        self._static = StaticData.load(self._db)()
        self._detail_adapter = DetailAdapter.load(self._config)
        self._forge_adapter = ForgeAdapter.load(self._config)
        self._search_adapter = SearchAdapter.load(self._config)

        # clean up database records from previous run
        self._drop_tables()
        self._create_tables()
        self._drop_plot_details_records()

        master_svg_fileobj = args.master_svg
        self.input_svg = master_svg_fileobj.read()
        master_svg_fileobj.close()

        output_ntwk_id_filepath = os.path.join(
                             args.output_directory,
                             args.project + '_' + \
                             self.OUTPUT_NTWK_ID_FILENAME
                          )
        with open(output_ntwk_id_filepath, 'wb') as \
                                   output_ntwk_id_fileobj:
            output_ntwk_id_fileobj.write(self._get_ntwk_id_svg())

        self._get_name_label_svg()

class Util(object):

    @staticmethod
    def remove_text_node(parent_node, el):
        """ keep the element wrapper node, but remove
            all text nodes within
        """
        el_wrapper_node = etree.SubElement(parent_node,
                                           el.tag,
                                           el.attrib)
        for sub_el in el:
           if sub_el.tag != '{{{0}}}text'.format(NSMAP['svg']):
               el_wrapper_node.append(sub_el)


    @staticmethod
    def remove_node_by_href_id(top_node, href_id):
        use_nodes = top_node.xpath('//svg:use',
                                    namespaces = NSMAP)

        for el in use_nodes:
            href = '{{{0}}}href'.format(NSMAP['xlink'])
            if href in el.attrib:
                el_href = el.attrib['{{{0}}}href'.format(NSMAP['xlink'])]
                if el_href == href_id:
                    el.getparent().remove(el)

    @staticmethod
    def get_distance(x1, y1, x2, y2):
        return (x2-x1)**2 + (y2-y1)**2


class Process(object):

    def __init__(self):
        self._chain = [
            CreateStructureSVG(),
            ParseFloorPlanConferenceRoomXLS(),
            PopulateTables(),
        ]

    @classmethod
    def run(cls, args):
        obj = cls()
        obj(args)

    def __call__(self, args):
        for obj in self._chain:
            obj(args)

def main():
    #print Args.parse()
    Process.run(Args.parse())