wiki:SpreadsheetImporterExample
def import_excel_sheet(table, filename, num_columns, sheet_index=0, header_row=1, truncate_table=True, cut_columns=(), append_column_names={}):
    """
    generic function for getting a table of data from an excel spreadheet
    header_row - index at 1 - like excel
    """
    import datetime
    
    try:
        import xlrd
    except ImportError:
       session.error = T('xlrd module not available within the running Python - this needs installing to do XLS Reporting!')
        
    #Import Workbooks
    workbook = xlrd.open_workbook(filename, formatting_info=True)
    
    sheet = workbook.sheet_by_index(sheet_index)   
    
    #Make a list of the rows in which the first cell is merged
    merged_rows = []
    for crange in sheet.merged_cells:
        rlo, rhi, clo, chi = crange
        if clo == 0:
            merged_rows.append(rlo) 

       
    #Simplfy Formatting, rename column headers
    rows = []
    rows.append(sheet.row(header_row-1))
    
    for i in xrange(header_row,sheet.nrows): 
        #if the first cell of this row is merged, skip the entire row
        if i not in merged_rows:
            rows.append(sheet.row(i))
            break        
    else:
        pass
        #print "ERROR! all rows merged"
    #should add error checking code here

    #rename column headers for columns with the same name
    for key,value in append_column_names.items():
        rows[0][key].value = value + rows[0][key].value
    
    #Cut Coulmns
    for i in [0,1]:
        for j in reversed(cut_columns):
            del rows[i][j]
        rows[i] = rows[i][:num_columns]
        
    #Get the headers for the field names
    row_string = "" 
    fields = []
    #There is a more optimize way of doing this
    for i in xrange(0,len(rows[1])):
        #Clean the field name
        field_name = rows[0][i].value.strip()
        field_name = field_name.replace(" ","_")
        field_name = field_name.replace(".","")
        field_name = field_name.replace("/","")
        field_name = field_name.replace("-","_")   
        field_name = field_name.replace("(","_")     
        field_name = field_name.replace(")","_")  
        field_name = field_name.replace(",","_")  
        field_name = field_name.lower()
        rows[0][i].value = field_name
        
        #store string and numbers as strings
        type = "string"
        if rows[1][i].ctype == 3:
            type = "date"   

        #print "Adding Field #" + str(i) + " Name:" + field_name 
        fields.append(db.Field(field_name , type))
    
    #This presumes that if the temp table is in the DB, it will have the same format, and the records can be appended
    if table not in db:
        db.define_table(table, migrate=True, *fields)
    if truncate_table:
       db[table].truncate()
    #db.commit()
        
    row_string = ""
    fields = {}
    
    for i in xrange(header_row,sheet.nrows): #TEST DATA - not getting it all sheet.nrows): #
        #Get a new row of data
        del rows[1]
        rows.append(sheet.row(i))
        #print rows
        
        #If the first 3 cells is empty, this is the end of the data
        if rows[1][0].value == '' and rows[1][1].value == '' and rows[1][2].value == '':
            break
            
        #if the first cell of this row is merged, skip the entire row
        if i in merged_rows:
            continue
        
        #Cut Columns
        for j in reversed(cut_columns):
            del rows[1][j]
        rows[1] = rows[1][:num_columns]

        for j in xrange(0, len(rows[0]) ): 
            cell_value = rows[1][j].value
            
            #print str(rows[0][j].value) + ":" + str(cell_value)
            
            if rows[1][j].ctype == 1: # String
                cell_value = cell_value.replace("," , "") # get rid of commas   
                cell_value = cell_value.replace("'" , "") # get rid of commas                   
                cell_value = cell_value.replace(r'\xa0' , "") # get rid of special char - not working   
                
                #remove '\x' and the 2 caracters that follow it  - THIS NEEDS TO BE FIXED
                index = cell_value.find(r'\x')
                if index <> -1: # #\x found
                    #print "removing messy chars: " + cell_value[index:index+4] 
                    del cell_value[index:index+4]
                    
            elif rows[1][j].ctype == 3: # Date                  
                #Convert Date
                date_tuple = xlrd.xldate_as_tuple(cell_value,0)
                date = datetime.date(*date_tuple[:3])
                cell_value = date.isoformat()
            else: # not Date
                cell_value = str(cell_value)   
            
            if db[table][str(rows[0][j].value)].type == "date" and rows[1][j].ctype <> 3: #Date
                if cell_value <> "" and cell_value <> "-" and cell_value <> "?":
                    #This should print errors to a file
                    #print "Date Error: '" + cell_value + "' (" + str(i) + "," + str(j)
                    pass
                cell_value = None

            if cell_value == '' or cell_value == '-':
                cell_value = None            
            
            #field:value distionary pair
            fields[str(rows[0][j].value)] = cell_value
        
        #print fields
            
        db[table].insert(**fields)   
        
    db.commit()
    
    return    
    
def add_lookup_data(lookup_table, 
                    lookup_field,
                    **args):
    """
    args:
    lookup_table - string - the table which the data is being added to
    lookup_field - string - the field which in the lookup_table which the new data is being added to 
    lookup_field_string_size - int - restriction on the length of the string
    lookup_field_is_int - validates the lookup aas an int
    lookup_field_alt - string - an altenative field for the new data to be added, this field should be a string 
    source_data - list.dict(table,fileld) - the (temporary) table(s) where the imported data is being read from     
    """
    db[lookup_table].truncate()
    if lookup_table == "pr_person":
        db.pr_pentity.truncate()
    
    read_table = args["source_data"][0]["table"]
    read_field = args["source_data"][0]["field"]
    
    #If >1 source tables, create a temporary table
    if len(args["source_data"]) > 1:
        read_table = "temp"
        read_field = lookup_field
        try:
            db.executesql("DROP TABLE temp")       
        except:
            pass
            
        db.executesql("CREATE TABLE temp( " + lookup_field+ " string)")        
        for source in args["source_data"]:       
            db.executesql("INSERT INTO temp(" + lookup_field+ ") SELECT DISTINCT " + source["table"] + "." + source["field"] + " FROM " + source["table"])  
            #print "INSERT INTO temp(" + lookup_field+ ") SELECT DISTINCT " + source["table"] + "." + source["field"] + " FROM " + source["table"]

    #Create additional columns to store IDs for the lookup data in the source tables                  
    for source in args["source_data"]:
        try:
            db.executesql("ALTER TABLE " + source["table"] + " ADD " + source["field"] + "_" + lookup_table + "_id integer")           
            #print  "ID field added ok"           
        except:
            pass            
    
    unique_i = 1
    
    #print db.executesql("SELECT DISTINCT " + read_field + " FROM " + read_table + " ORDER BY " + read_field)
    
    for data in db.executesql("SELECT DISTINCT " + read_field + " FROM " + read_table):    
        if data[0] == "NULL" or data[0] == None:
            continue
            
        if lookup_table == "pr_person":         
            person_split = data[0].split(" ")
            #pad the end, so that the are always three names
            for i in xrange(len(person_split), 3):
                person_split.append("")
            
            #Each person needs an entity
            pr_pe_id = db.pr_pentity.insert(opt_pr_entity_type=1,label=None)
            new_id = db.pr_person.insert(pr_pe_id = pr_pe_id, 
                                         pr_pe_label=None,
                                         preferred_name = data[0], 
                                         first_name = person_split[0] , 
                                         middle_name = person_split[1] , 
                                         last_name = person_split[2]) 
        
        #Data must be an Integer
        elif args.get("lookup_field_is_int",False): 
            data_int = validate_int(data)
            
            if data_int <> None:
                if db(db[lookup_table][lookup_field] == data_int).count() ==0:
                    #print "data int: " + str(data_int)
                    new_id = db[lookup_table].insert(**{lookup_field: data_int})                
                    #print "data int added"                
            else:
                unique_i = unique_i+1 # unique dummy value
                #print "non data int: " + data[0] + "unique_i: " + str(unique_i)                
                new_id = db[lookup_table].insert(**{lookup_field: unique_i, args["lookup_field_alt"]:data[0]})    
       
        #There is a restriction on the length of the data string
        elif "lookup_field_string_size" in args:        
            #FIX - Errors could occur if the cut data isn't unique
            if len(data[0]) > args["lookup_field_string_size"]:
                #cut the data and place the full string in the alternative field
                data_cut = data[0][args["lookup_field_string_size"]:]
                new_id = db[lookup_table].insert(**{lookup_field: data_cut, args["lookup_field_alt"]: data[0]})
            else:
                new_id = db[lookup_table].insert(**{lookup_field: data[0]})      
        
        #The field is a string with no restriction
        else:
            new_id = db[lookup_table].insert(**{lookup_field: data[0]})             
        
        #Add Lookup ID to the source tables
        for source in args["source_data"]:  
            #print "UPDATE " + source["table"] + " SET " + source["table"] + "_" + lookup_table + "_id = " + str(new_id) + " WHERE " + source["field"] + " = '" + str(data[0]) + "'"
            db.executesql("UPDATE " + source["table"] + " SET " + source["field"] + "_" + lookup_table + "_id = " + str(new_id) + " WHERE " + source["field"] + " = '" + str(data[0]) + "'")
            
        try:
            db.executesql("DROP TABLE temp")       
        except:
            pass
            
    return
	
def format_price(price):

    currency_map = dict( FEC= 'FEC', MMK = 'MMK', kyat= 'MMK', USD= 'USD', Kyats = 'MMK', THB = 'THB', GBP = 'GBP', USUSD = 'USD', Bath = 'THB', Kyat = 'MMK')        	
	
    #lookup currency - this code is duplicated
    if price <>  None:
        #print "Value: " + str(price)
        try:
            price = float(price)
            currency_id = db(db.fin_currency.name == "MMK").select(db.fin_currency.id)[0]['id']
        except:  
            #print "price except: " + str(value)
            price = price.replace("$","USD")
            #price = price.replace("£","GBP") Error
            price = price.replace(" ","")
            #print currency_map
            for key, data in currency_map.iteritems():
                if price.find(key) <> -1: 
                    price = price.replace(key, "")
                    #print "price: " + price + " Key: " + key 
                    currency_id = db(db.fin_currency.name == data).select(db.fin_currency.id)[0]['id']     
                    try:
                        price = float(price)
                    except:
                        #print "OT Price ERROR!!! '" + str(price) + "'"   
                        pass
                    break        
    else:
        currency_id = None
			
    return price, currency_id
	
def validate_date(date_str):
    if date_str <> None and date_str <> "":
        date_str = date_str.replace("NA","")    
        try:
            time.strptime(date_str,"%Y-%m-%d")
        except: 
            #print "ERROR Date: " + str(date_str)   
            date_str = None
    else:
        date_str = None
    return date_str

def validate_int(int_str):
    return_int = None 
    if int_str <> None:         
        try:
            return_int  = int(int_str[0].replace(".0","")) # trim all decimal
        except: 
            pass
    return return_int

def find_key(dic, val):
    #
    """return the key of dictionary dic given the value"""
    #
    return [k for k, v in dic.iteritems() if v == val][0]    
    

    
def import_excel_data():

    import time
    
    #print "--------------------------------------------------------------------------"

    #OT
    cut_columns = [5]   
    for i in xrange(19,32):
        cut_columns.append(i)
        
    cut_columns.append(41)
    
    append_column_names= {42:"a_",43:"a_",44:"a_",45:"b_",46:"b_",47:"b_",48:"c_",49:"c_",50:"c_"}
    #import_excel_sheet(table, filename, num_columns, sheet_index=0, header_row=0,  cut_columns=(), append_column_names={}):

    import_excel_sheet( "ot_temp",
                        r'/tmp/import/ot.xls',
                        39,
                        2,
                        5, 
                        True,
                        cut_columns,
                        append_column_names)
                        
    #ASSET
    append_column_names= {18:"cost_",20:"cost_",26:"sale_",27:"sale_"}
    import_excel_sheet( "asset_temp",
                        r'/tmp/import/asset.xls',
                        31,
                        0,
                        2,
                        True,
                        [],
                        append_column_names)
                        
    #CATALOG                     
    for i in xrange(1,8):
        if i == 1:
            truncate_table=True
            num_columns = 12 #for sub_cat
        else: 
            truncate_table=False
            num_columns = 11
        import_excel_sheet( "catalog_temp",
                            r'/tmp/import/catalog.xls',
                            num_columns,
                            i,
                            3,
                            truncate_table)                            

    #CATEGORIES    
    import_excel_sheet( table = "cat_temp",
                        filename = r'/tmp/import/cat.xls',
                        num_columns = 3,
                        header_row=1,
                        truncate_table=True)                                 

    import_excel_sheet( table = "cat_map_temp",
                        filename = r'/tmp/import/cat_map.xls',
                        num_columns = 4,
                        header_row=1,
                        truncate_table=True)                            

    #UPDATE LOOKUPS
    #Fix the IDs for these in dictionaries (with the string from the orig table as the key)
    #Could these be functions?
    #people
    add_lookup_data(    lookup_table = "pr_person",
                        lookup_field = "name",                        
                        source_data =   [{"table": "ot_temp", "field": "order_requestor"},
                                    {"table": "ot_temp", "field": "received_by"},
                                    {"table": "asset_temp", "field": "person_responsible"} ]
                       )    
     
    #code
    add_lookup_data(    lookup_table = "fin_code",
                        lookup_field = "name",      
                        lookup_field_is_int = True,
                        lookup_field_alt = "comments",
                        source_data =   [{"table": "asset_temp", "field": "account_code"}]
                       )       

    #contract
    add_lookup_data(    lookup_table = "fin_contract",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "contract"}],
                        lookup_field_string_size = 6,
                        lookup_field_alt = "comments"
                       )         
            
    #project
    add_lookup_data(    lookup_table = "fin_project",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "project_id"}],
                        lookup_field_string_size = 6,
                        lookup_field_alt = "comments"
                       )      
    #unit
    add_lookup_data(    lookup_table = "mat_unit",
                        lookup_field = "name",                        
                        source_data =   [{"table": "ot_temp", "field": "unit"},
                                    {"table": "catalog_temp", "field": "unit"} ]
                       )        
                       
    #supplier
    add_lookup_data(    lookup_table = "mat_supplier",
                        lookup_field = "name",                        
                        source_data =   [{"table": "ot_temp", "field": "sourced_from__supplier_name"},
                                         {"table": "ot_temp", "field": "a_supplier"},
                                         {"table": "ot_temp", "field": "b_supplier"},
                                         {"table": "ot_temp", "field": "c_supplier"},
                                         {"table": "catalog_temp", "field": "supplier"} ]
                       )     
                       
    #category
    try:
        db.executesql("ALTER TABLE cat_temp ADD mat_category_id integer")
    except:
        pass 
        
    try:
        db.executesql("ALTER TABLE cat_map_temp ADD mat_category_id integer")
    except:
        pass 

    try:
        db.executesql("ALTER TABLE cat_map_temp ADD mat_sub_category_id integer")
    except:
        pass 
    
    #Add categories
    db.mat_category.truncate()
    for data in db.executesql("SELECT DISTINCT category_name, category_description  FROM cat_temp"): 
        new_id = db.mat_category.insert(name= data[0], description= data[1])
        db.executesql("UPDATE cat_temp SET mat_category_id = " + str(new_id) + " WHERE category_name = '" + str(data[0]) + "'")      
        
    #Add Sub-categories
    db.mat_sub_category.truncate()
    for category_name, sub_category, mat_category_id in db.executesql("SELECT DISTINCT category_name, sub_category, mat_category_id  FROM cat_temp"):   
        new_id = db.mat_sub_category.insert(name= sub_category, category_id = mat_category_id)   
        
        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
        db.executesql("UPDATE cat_map_temp SET mat_sub_category_id = " + str(new_id) + ", mat_category_id = " +  str(mat_category_id) +
                      " WHERE category_name = '" + category_name + "' AND sub_category = '" + sub_category + "'")          
    
    #item
    db.mat_item.truncate()
    try:
        db.executesql("ALTER TABLE catalog_temp ADD mat_item_id integer")    
    except:
        pass
            
    currency_map = dict( FEC= 'FEC', MMK = 'MMK', kyat= 'MMK', USD= 'USD', Kyats = 'MMK', THB = 'THB', GBP = 'GBP', USUSD = "USD", Bath = 'THB', Kyat = 'MMK')        
    
    #Add All of the Items from the catalog (because this has the most complete details)   
    for name, \
        sub_category, \
        category, \
        unit_id, \
        price, \
        currency, \
        supplier_id, \
        comments \
        in db.executesql("SELECT DISTINCT  description__item, " + 
                                          "sub_category," + 
                                          "cat," + 
                                          "unit_mat_unit_id, " + 
                                          "price, " + 
                                          "currency, " + 
                                          "supplier_mat_supplier_id, " + 
                                          "remarks__quality__delay__discount_if_big_amount " + 
                         "FROM catalog_temp"):   
        
        if sub_category <>  None:
            #print "sub category: " +  str(sub_category)
            #print "SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(sub_category) + "'"
            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(sub_category) + "'")
            #print "cat_map_row length: " + str(len(cat_map_row))
            #print cat_map_row
        elif category <> None: 
            #print "category: " +  str(category)
            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(category) + "'")
            #print "cat_map_row length: " + str(len(cat_map_row))
            #print cat_map_row           
        else:
            #print "Item: " + name
            #print "NO CATEGORY LISTED!!!"
            pass
            
        if len(cat_map_row) >= 1:
            category_id = cat_map_row[0][0]
            sub_category_id = cat_map_row[0][1]       
        
        if currency <> None:       
            #print " Currency: " + str(currency)    
            currency = currency.replace("$","USD")            
            currency_id = db(db.fin_currency.name == currency_map[currency]).select(db.fin_currency.id)[0]['id']            
        else:
            currency_id =None
            
        if price <>  None:
            try:
                unit_cost = float(price)
            except:
                #print "Catalog Price ERROR!!! '" + str(price) + "'"
                pass
        else:
            unit_cost = None
            
        new_id = db.mat_item.insert(name= name, 
                                    category_id = category_id, 
                                    sub_category_id = sub_category_id, 
                                    unit_id = unit_id, 
                                    unit_cost = unit_cost, 
                                    currency_id = currency_id,
                                    supplier_id = supplier_id,
                                    comments = comments
                                    )   
        
        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
        db.executesql("UPDATE catalog_temp SET mat_item_id = " + str(new_id) + "WHERE description__item = '" + name + "'" )
    
    #Add Items from Asset
    try:
        db.executesql("ALTER TABLE asset_temp ADD mat_item_id integer")    
    except:
        pass    
        
    try:
        db.executesql("ALTER TABLE asset_temp ADD category_id integer")    
    except:
        pass            
        
    try:
        db.executesql("ALTER TABLE asset_temp ADD sub_category_id integer")    
    except:
        pass                
        
    for name, \
        category, \
        price, \
        currency, \
        comments \
        in db.executesql("SELECT DISTINCT  description, " + 
                                          "category," + 
                                          "cost_amount, " + 
                                          "asset_temp.cost_in_local_currency, " + 
                                          "comments " + 
                         "FROM asset_temp"):  
                        
        if category <> None: 
            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(category) + "'")          


        if len(cat_map_row) >= 1:
            sub_category_id = 0        
            category_id = cat_map_row[0][0]
            sub_category_id = cat_map_row[0][1]   
            
            #write these back to the Asset_temp table
            db.executesql("UPDATE asset_temp SET category_id = " + str(category_id) + ", sub_category_id = " + str(sub_category_id) + " WHERE description = '" + str(name) + "' AND category = '" + str(category) + "'")
        #else:
            #print "cat_map_row length: " + str(len(cat_map_row))
            #print str(name) + "cat " + str(category)

        if currency <> None:       
            #print " Currency: " + str(currency)         
            currency = currency.replace("$","USD")
            currency_id = db(db.fin_currency.name == currency_map[currency]).select(db.fin_currency.id)[0]['id']     
       
        else:
            currency_id =None
            
        if price <>  None:
            try:
                unit_cost = float(price)
            except:
                #print "Asset Price ERROR!!! '" + str(price) + "'"
                pass
                
        else:
            unit_cost = None   

        current_item_rows = db(db.mat_item.name == name).select(db.mat_item.id,db.mat_item.name,
                                                           db.mat_item.category_id,
                                                           db.mat_item.sub_category_id,
                                                           db.mat_item.unit_cost,
                                                           db.mat_item.currency_id)
        
        if len(current_item_rows) == 0:
            #Add Item
            item_id = db.mat_item.insert(name= name, 
                                        category_id = category_id, 
                                        sub_category_id = sub_category_id, 
                                        unit_cost = unit_cost, 
                                        currency_id = currency_id,
                                        comments = comments
                                        )                           
        else:            
            #test to see if any additional information can be added
            current_item = current_item_rows[0]
            
            item_id = current_item['id']
            
            if current_item['sub_category_id'] == None and sub_category_id <> 0:
                db(db.mat_item.name == name).update(category_id = category_id, sub_category_id = sub_category_id)
                #db(db.mat_item.name == name).update(db.mat_item.sub_category_id = sub_category_id)
                
            #don't change the currency if there is a cost but no currency
            if current_item['unit_cost'] == None and current_item['currency_id'] == None and currency_id <> None:
                db(db.mat_item.name == name).update(currency_id = currency_id)                       
                
            if current_item['unit_cost'] == None and unit_cost <> None:
                db(db.mat_item.name == name).update(unit_cost = unit_cost)            

        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
        db.executesql("UPDATE asset_temp SET mat_item_id = " + str(item_id) + "WHERE description = '" + name + "'" )            
    
    #Add Items from OT
    try:
        db.executesql("ALTER TABLE ot_temp ADD mat_item_id integer")    
    except:
        pass    
        
    for name, \
        unit_id, \
        supplier_id, \
        price \
        in db.executesql("SELECT DISTINCT description, " + 
                                          "unit_mat_unit_id," + 
                                          "sourced_from__supplier_name_mat_supplier_id," + 
                                          "actual_unit_price_in_currency " + 
                         "FROM ot_temp"):                    

        if name  == None:
            continue # skip this line 

        unit_cost, currency_id = format_price(price)

        current_item_rows = db(db.mat_item.name == name).select(db.mat_item.id,db.mat_item.name,
                                                           db.mat_item.unit_id,
                                                           db.mat_item.unit_cost,
                                                           db.mat_item.currency_id,
                                                           db.mat_item.supplier_id)
        
        if len(current_item_rows) == 0:
            #Add Item
            item_id = db.mat_item.insert(name= name, 
                                        unit_id = unit_id,
                                        unit_cost = unit_cost, 
                                        currency_id = currency_id,
                                        supplier_id = supplier_id
                                        )                           
        else:            
            #test to see if any additional information can be added
            current_item = current_item_rows[0]
            
            item_id = current_item['id']
            
            if current_item['unit_id'] == None and unit_id <> None:
                db(db.mat_item.name == name).update(unit_id = unit_id)               
                
            #don't change the currency if there is a cost but no currency
            if current_item['unit_cost'] == None and current_item['currency_id'] == None and currency_id <> None:
                db(db.mat_item.name == name).update(currency_id = currency_id)                       
                
            if current_item['unit_cost'] == None and unit_cost <> None:
                db(db.mat_item.name == name).update(unit_cost = unit_cost)      

            if current_item['supplier_id'] == None and supplier_id <> None:
                db(db.mat_item.name == name).update(supplier_id = supplier_id)                  

        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
        db.executesql("UPDATE ot_temp SET mat_item_id = " + str(item_id) + " WHERE description = '" + name + "'" )            
                         
    #donor
    add_lookup_data(    lookup_table = "asset_donor",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "donor"}]
                       ) 
                       
    #office
    add_lookup_data(    lookup_table = "asset_office",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "office"}],
                        lookup_field_string_size = 3,
                        lookup_field_alt = "comments"                        
                       ) 

    db.asset_office.insert(
        name = 'Y+D',
        comments = 'Yangon  + Dedaye'
    )   
    db.asset_office.insert(
        name = 'BOG',
        comments = 'Bogale'
    )
    db.asset_office.insert(
        name = 'DRY',
        comments = 'Dryzone'
    )                           
    
    #location
    add_lookup_data(    lookup_table = "asset_location",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "location"}]
                       ) 
                       
    #sub_location    
    add_lookup_data(    lookup_table = "asset_sub_location",
                        lookup_field = "name",                        
                        source_data =   [{"table": "asset_temp", "field": "sub_location"}]
                       ) 
    
    #Assets  
    db.asset_asset.truncate()
    
    #key = asset_asset fields, value = asset_temp fields
    #Used for storing the data to be inserted
    asset_insert_dict = dict(name = 'ref', 
    date = 'date_of_purchase', 
    financial_year = 'year', 
    donor = 'donor_asset_donor_id', 
    code = 'account_code_fin_code_id', 
    contract = 'contract_fin_contract_id', 
    project = 'project_id_fin_project_id', 
    type = 'type', 
    item_id = 'mat_item_id', 
    category_id = 'category_id',
    sub_category_id = 'sub_category_id',    
    person = 'person_responsible_pr_person_id', 
    tag = 'asset_tag_no', 
    serial = 'serial_no', 
    office_id = 'office_asset_office_id',                                     
    location = 'location_asset_location_id', 
    sub_location = 'sub_location_asset_sub_location_id', 
    po_no = 'po_no',
    purchase_currency = 'cost_in_local_currency', #LOOK UP REQ
    purchase_cost = 'cost_amount',
    purchase_exchange_rate = 'cost_mthly_exchange_rate',
    gbp_purchase_cost = 'cost_in_gbp',
    guarantee = 'date_of_guarantee__expiration',
    status = 'status',
    disposal = 'date_of_disposal',
    sale_currency = 'sale_value_in_local_currency',
    sale_value = 'sale_amount',
    sale_exchange_rate = 'sale_mthly_exchange_rate',
    gbp_sale_value = 'sale_value_gbp',
    donation = 'donation',
    comments = 'comments')   
    
    #key = asset_temp fields, value = asset_asset fields
    #Used for mapping the asset_temp fields to the asset_asset fields    
    asset_fields_dict = {}
    
    #list = asset_temp fields
    asset_fields = []
    sql_str = "SELECT "
    
    for key,value in asset_insert_dict.iteritems():
        asset_fields_dict[value] = key
        asset_fields.append(value)
        sql_str = sql_str + value + ","
        
    sql_str = sql_str[:-1] + " FROM asset_temp"

    #print sql_str
    
    type_map = { "str": "string", "unicode": "string", "int": "reference"}
    
    for asset_row in db.executesql(sql_str):
        for field, data in zip(asset_fields,asset_row):
            if field == 'cost_in_local_currency' and data <> None: # Map the currency to the lookup table
                data = data.replace("$","USD")
                data = db(db.fin_currency.name == currency_map[data]).select(db.fin_currency.id)[0]['id']   
            if type(data).__name__ <> "NoneType" and data <> None:
                #print "temp type: " + type(data).__name__ + "\tasset type: " + str(db.asset_asset[asset_fields_dict[field]].type)         
                table_field_type = db.asset_asset[asset_fields_dict[field]].type[:9]    # Cut to 9, so the different "reference" types can be treated the same
                if type_map[type(data).__name__] <> table_field_type: 
                    #try to convert the date
                    if table_field_type == 'date':
                        data = validate_date(data)    
                        
                    #try to convert to float
                    elif table_field_type == 'double':
                        try:
                            data = float(data)
                        except: 
                            #print "ERROR double: " + str(data)
                            #print "field: " + field + "\tdata: " + str(data)       
                            pass
                            
                    else:
                        #print "ERROR other type"
                        #print "type: " + table_field_type + "\tfield: " + field + "\tdata: " + str(data)                
                        pass
            asset_insert_dict[asset_fields_dict[field]] = data  
            
        #print asset_insert_dict
        new_id = db.asset_asset.insert(**asset_insert_dict)     
                
    #OT
    #add purchase_request
    db.ot_purchase_request.truncate()
    try:
        db.executesql("ALTER TABLE ot_temp ADD request_id integer")    
    except:
        pass        
    
    office_map = { "Yangon": "YGN" , "Dedaye": "DDY","Dedaya": "DDY", "Yangon  + Dedaye": "Y+D", "Bogale": "BOG", "Dryzone": "DRY"}

    for name, \
        date_received, \
        requestor, \
        office, \
        date_processed, \
        comments \
        in db.executesql("SELECT DISTINCT req_no, " + 
                                          "date_received," + 
                                          "order_requestor_pr_person_id," + 
                                          "location," + 
                                          "date_processed," + 
                                          "general_information_comments " + 
                                          "FROM ot_temp" ):            

        #convert office  - this code is duplicated   
        if office <> "" and office <> None:
            #print office
            #print "office code:" + office_map[office]
            office_id = db(db.asset_office.name == office_map[office]).select(db.asset_office.id)[0]['id']
        
        status = 2 #Closed  - check that all the items are either completed or cancelled XXX check
        
        #Check Dates
        date_received = validate_date(date_received)
        date_processed = validate_date(date_processed)                
        
        #Insert request
        request_id = db.ot_purchase_request.insert(name= name, 
                                                date_received = date_received,
                                                requestor = requestor,
                                                office_id = office_id,
                                                date_processed = date_processed,
                                                comments = comments,
                                                status = status
                                    )       
                                    
        #update request_id in temp table           
        #print "UPDATE ot_temp SET request_id = " + str(request_id) + " WHERE req_no = " + name 
        db.executesql("UPDATE ot_temp SET request_id = " + str(request_id) + " WHERE req_no = '" + name + "'")                                        
                
    #add order
    db.ot_order.truncate()
    try:
        db.executesql("ALTER TABLE ot_temp ADD order_id integer")    
    except:
        pass        

    for name, \
        supplier_id \
        in db.executesql("SELECT DISTINCT purchase_order_no, " + 
                                          "sourced_from__supplier_name_mat_supplier_id " + 
                         "FROM ot_temp"):            

        status = 2 #Closed  - check that all the items are either completed or cancelled XXX check
        
        #if there is a PO#
        if name <> "-" and name <> None:
            #Insert Purchase
            order_id = db.ot_order.insert(name= name, supplier_id = supplier_id, status = status )       
            #update order_id in temp table                            
            db.executesql("UPDATE ot_temp SET order_id = " + str(order_id) + " WHERE purchase_order_no = '" + name + "'")    
    
    #Loop through all the orders with no PO# (but only add if there is a supplier)
    i = 0    
    for name, supplier_id in db.executesql("SELECT purchase_order_no, " + 
                                          "sourced_from__supplier_name_mat_supplier_id " + 
                         "FROM ot_temp WHERE order_id Is Null"): 
        if supplier_id <> None:
            if name <> "-" or name <> None:
                name = "AUTO_PO_REF" + str(i)
                i = i+1
                #print "Adding dummy PO#" + name
                #Insert Purchase
                order_id = db.ot_order.insert(name= name, supplier_id = supplier_id, status = status )       
                #update order_id in temp table                            
                db.executesql("UPDATE ot_temp SET order_id = " + str(order_id) + " WHERE purchase_order_no = '" + name + "'" )          
            else:
                #print "Error these PO shouldn't include those with numbers Name: " + str(name)                                                                                                    
                pass

    #add invoice
    db.fin_invoice.truncate() 
    try:
        db.executesql("ALTER TABLE ot_temp ADD invoice_id integer")    
    except:
        pass       

    for id, \
        order_id, \
        date_in, \
        date_out, \
        value, \
        comments \
        in db.executesql("SELECT id, order_id, date_supplier_invoice_received, date_paid_by_finance, amount_paid, finance_comments FROM ot_temp"):  

        date_in = validate_date(date_in) 
        date_out = validate_date(date_out)   

        #lookup currency - this code is duplicated
        value, currency_id = format_price(value)    

        if value <>  None:						
            #Insert Invoice (if there is a value)
            invoice_id = db.fin_invoice.insert(order_id= order_id,
                                            date_in =date_in,
                                            date_out = date_out,
                                            value = value,
                                            comments = comments )    
                                            
            #print "Item id: " + str(id) + "\tinvoice_ id: " + str(invoice_id)                                  
                                            
            db.executesql("UPDATE ot_temp SET invoice_id = " + str(invoice_id) + " WHERE id = " + str(id) )  

    #add grn
    db.ot_grn.truncate() 
   
    try:
        db.executesql("ALTER TABLE ot_temp ADD grn_id integer")    
    except:
        pass  
    
    i=0    
    for id, \
        name, \
        date_received, \
        received_by, \
        office \
        in db.executesql("SELECT id, grn_no, date_arrived_in_location, received_by_pr_person_id, location FROM ot_temp"):  

        date_received = validate_date(date_received)                 
        
        #convert office            
        if office <> "" and office <> None:
            #print "Office: " + str(office)
            office_id = db(db.asset_office.name == office_map[office]).select(db.asset_office.id)[0]['id']                

        #If there is GRN Data
        if name <> None or date_received <> None or received_by <> None: 
            name = "AUTO_GRN_REF" + str(i)
            i = i+1        
            
            #Insert GRN
            grn_id = db.ot_grn.insert(name= name,
                                        date_received =date_received,
                                        received_by = received_by,
                                        office_id = office_id )
            db.executesql("UPDATE ot_temp SET grn_id = " + str(grn_id) + " WHERE id = " + str(id) )  
            
    #add waybills
    db.ot_waybill.truncate() 
    i=0
    for index in ('a','b','c'):
    
        try:
            db.executesql("ALTER TABLE ot_temp ADD waybill_id_" + index + "  integer")    
        except:
            pass  
            
            
        for id, \
            date_dispatched, \
            supplier_id, \
            despatched_qty \
            in db.executesql("SELECT id, " + index +  "_date, " + index +  "_supplier_mat_supplier_id, " + index +  "_despatched_qty FROM ot_temp"):  

            date_dispatched = validate_date(date_dispatched)                                

            #If there is WB Data
            if date_dispatched <> None or despatched_qty <> None: 
                name = "AUTO_WB_REF" + str(i)
                i = i+1     
                
                #InsertWB
                wb_id = db.ot_waybill.insert(name = name, date_dispatched= date_dispatched,
                                                supplier_id = supplier_id )
                db.executesql("UPDATE ot_temp SET waybill_id_" + index + " = " + str(wb_id) + " WHERE id = " + str(id) )       
    
    #add ot_line_items
    db.ot_line_item.truncate() 
    
    try:
        db.executesql("ALTER TABLE ot_temp ADD wb_id_" + index + "  integer")    
    except:
        pass  
        
    for id, \
        request_id, \
        line, \
        status, \
        order_id, \
        grn_id, \
        a_waybill_id, \
        b_waybill_id, \
        c_waybill_id, \
        invoice_id, \
        item_id, \
        quantity_pr, \
        quantity_po, \
        quantity_grn, \
        a_quantity_waybill, \
        b_quantity_waybill, \
        c_quantity_waybill, \
        unit_id, \
        budgeted_unit_price, \
        actual_unit_price, \
        actual_total_price, \
        comments \
        in db.executesql("SELECT id, " +
                            "request_id, " +
                            "line_item, " +
                            "status, " +
                            "order_id, " +
                            "grn_id, " +
                            "waybill_id_a, " +
                            "waybill_id_b, " +
                            "waybill_id_c, " +                            
                            "invoice_id, " +
                            "mat_item_id, " +
                            "quanity, " +
                            "qty, " +
                            "qty, " +
                            "a_despatched_qty, " +
                            "b_despatched_qty, " +
                            "c_despatched_qty, " +                            
                            "unit_mat_unit_id, " +
                            "budgeted_unit_price, " +
                            "actual_unit_price_in_currency, " +
                            "actual_total_price_in_currency, " +
                            "general_information_comments " +
                            "FROM ot_temp"):  
        
        #convert status
        status = find_key( ot_line_item_status_opts, status)
		
		#Calculate prices and currency
        budgeted_unit_price, budgeted_currency_id = format_price(budgeted_unit_price)
        actual_unit_price, actual_currency_id = format_price(actual_unit_price)
        
        line = validate_int(line)
        quantity_pr = validate_int(quantity_pr)
        quantity_po = validate_int(quantity_po)
        quantity_grn = validate_int(quantity_grn)        
        a_quantity_waybill = validate_int(a_quantity_waybill)
        b_quantity_waybill = validate_int(b_quantity_waybill)
        c_quantity_waybill = validate_int(c_quantity_waybill) 

		
        line_item_id = db.ot_line_item.insert( request_id = request_id,
                                line =  line ,
                                status = status,
                                item_id = item_id,   		
                                quantity_pr = quantity_pr,
                                unit_id = unit_id,
                                budgeted_unit_price = budgeted_unit_price,
                                comments = comments)   


        if order_id <> None:	
            db(db.ot_line_item.id == line_item_id).update(superseded=True)            
            line_item_id = db.ot_line_item.insert( request_id = request_id,
                                    line =  line ,
                                    status = status,
                                    order_id = order_id,
                                    invoice_id = invoice_id,
                                    item_id = item_id,   		
                                    quantity_pr = quantity_pr,
                                    quantity_po = quantity_po,
                                    quantity_grn = quantity_grn,
                                    quantity_waybill = a_quantity_waybill,
                                    unit_id = unit_id,
                                    budgeted_unit_price = budgeted_unit_price,
                                    actual_unit_price = actual_unit_price,
                                    actual_total_price = actual_total_price,
                                    comments = comments)   


        if grn_id <> None:		
            db(db.ot_line_item.id == line_item_id).update(superseded=True)
            line_item_id = db.ot_line_item.insert( request_id = request_id,
                                    line =  line ,
                                    status = status,
                                    order_id = order_id,
                                    grn_id = grn_id,
                                    invoice_id = invoice_id,
                                    item_id = item_id,   		
                                    quantity_pr = quantity_pr,
                                    quantity_po = quantity_po,
                                    quantity_grn = quantity_grn,
                                    quantity_waybill = a_quantity_waybill,
                                    unit_id = unit_id,
                                    budgeted_unit_price = budgeted_unit_price,
                                    actual_unit_price = actual_unit_price,
                                    actual_total_price = actual_total_price,
                                    comments = comments)                                       

        #if invoice_id <> None:	
        #    db(db.ot_line_item.id == line_item_id).update(superseded=True)
        #    db.ot_line_item.insert( request_id = request_id,
        #                            line =  line ,
        #                            status = status,
        #                            order_id = order_id,
        #                            grn_id = grn_id,
        #                            waybill_id = a_waybill_id,
        #                            invoice_id = invoice_id,
        #                            item_id = item_id,   		
        #                            quantity_pr = quantity_pr,
        #                            quantity_po = quantity_po,
        #                            quantity_grn = quantity_grn,
        #                            quantity_waybill = a_quantity_waybill,
        #                            unit_id = unit_id,
        #                            budgeted_unit_price = budgeted_unit_price,
        #                            actual_unit_price = actual_unit_price,
        #                            actual_total_price = actual_total_price,
        #                            comments = comments)        

        if a_waybill_id <> None:	
            db(db.ot_line_item.id == line_item_id).update(superseded=True)
            db.ot_line_item.insert( request_id = request_id,
                                    line =  line ,
                                    status = status,
                                    order_id = order_id,
                                    grn_id = grn_id,
                                    waybill_id = a_waybill_id,
                                    invoice_id = invoice_id,
                                    item_id = item_id,   		
                                    quantity_pr = quantity_pr,
                                    quantity_po = quantity_po,
                                    quantity_grn = quantity_grn,
                                    quantity_waybill = a_quantity_waybill,
                                    unit_id = unit_id,
                                    budgeted_unit_price = budgeted_unit_price,
                                    actual_unit_price = actual_unit_price,
                                    actual_total_price = actual_total_price,
                                    comments = comments)
        
        if b_waybill_id <> None:
            db.ot_line_item.insert( request_id = request_id,
                                    line =  line ,
                                    status = status,
                                    order_id = order_id,
                                    grn_id = grn_id,
                                    waybill_id = b_waybill_id,
                                    invoice_id = invoice_id,
                                    item_id = item_id,   		
                                    quantity_pr = quantity_pr,
                                    quantity_po = quantity_po,
                                    quantity_grn = quantity_grn,
                                    quantity_waybill = b_quantity_waybill,
                                    unit_id = unit_id,
                                    budgeted_unit_price = budgeted_unit_price,
                                    actual_unit_price = actual_unit_price,
                                    actual_total_price = actual_total_price,
                                    comments = comments)        

        if c_waybill_id <> None:
            db.ot_line_item.insert( request_id = request_id,
                                    line =  line ,
                                    status = status,
                                    order_id = order_id,
                                    grn_id = grn_id,
                                    waybill_id = c_waybill_id,
                                    invoice_id = invoice_id,
                                    item_id = item_id,   		
                                    quantity_pr = quantity_pr,
                                    quantity_po = quantity_po,
                                    quantity_grn = quantity_grn,
                                    quantity_waybill = c_quantity_waybill,
                                    unit_id = unit_id,
                                    budgeted_unit_price = budgeted_unit_price,
                                    actual_unit_price = actual_unit_price,
                                    actual_total_price = actual_total_price,
                                    comments = comments)         
                       
    return SQLTABLE(db(db.ot_line_item.id<>0).select()) 
    
def clean_excel_data():
    return dict(message="beginning import") 

def lookups_menu():
    resource = 'lookups'
    response.title = LOOKUPS
    s3.sub_options = [
        [T('Item Categories'), 'mat', 'category', T('')],
        [T('Item Sub-Categories'), 'mat', 'sub_category', T('')],
        [T('Units'), 'mat', 'unit', T('') ],
    ]
    s3.sub_options_menu = []
    for option in s3.sub_options:
        label = option[0]
        controller = option[1]
        function = option[2]
        s3.sub_options_menu.append([label, False, URL(request.application, controller, function)],)
    response.menu.insert(2, ['>> ' + str(response.title), False, URL(request.application, module, resource), s3.sub_options_menu])
    return
Last modified 13 years ago Last modified on 01/23/10 07:33:46
Note: See TracWiki for help on using the wiki.