Changes between Initial Version and Version 1 of SpreadsheetImporterExample


Ignore:
Timestamp:
01/23/10 07:33:46 (12 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • SpreadsheetImporterExample

    v1 v1  
     1{{{
     2def import_excel_sheet(table, filename, num_columns, sheet_index=0, header_row=1, truncate_table=True, cut_columns=(), append_column_names={}):
     3    """
     4    generic function for getting a table of data from an excel spreadheet
     5    header_row - index at 1 - like excel
     6    """
     7    import datetime
     8   
     9    try:
     10        import xlrd
     11    except ImportError:
     12       session.error = T('xlrd module not available within the running Python - this needs installing to do XLS Reporting!')
     13       
     14    #Import Workbooks
     15    workbook = xlrd.open_workbook(filename, formatting_info=True)
     16   
     17    sheet = workbook.sheet_by_index(sheet_index)   
     18   
     19    #Make a list of the rows in which the first cell is merged
     20    merged_rows = []
     21    for crange in sheet.merged_cells:
     22        rlo, rhi, clo, chi = crange
     23        if clo == 0:
     24            merged_rows.append(rlo)
     25
     26       
     27    #Simplfy Formatting, rename column headers
     28    rows = []
     29    rows.append(sheet.row(header_row-1))
     30   
     31    for i in xrange(header_row,sheet.nrows):
     32        #if the first cell of this row is merged, skip the entire row
     33        if i not in merged_rows:
     34            rows.append(sheet.row(i))
     35            break       
     36    else:
     37        pass
     38        #print "ERROR! all rows merged"
     39    #should add error checking code here
     40
     41    #rename column headers for columns with the same name
     42    for key,value in append_column_names.items():
     43        rows[0][key].value = value + rows[0][key].value
     44   
     45    #Cut Coulmns
     46    for i in [0,1]:
     47        for j in reversed(cut_columns):
     48            del rows[i][j]
     49        rows[i] = rows[i][:num_columns]
     50       
     51    #Get the headers for the field names
     52    row_string = ""
     53    fields = []
     54    #There is a more optimize way of doing this
     55    for i in xrange(0,len(rows[1])):
     56        #Clean the field name
     57        field_name = rows[0][i].value.strip()
     58        field_name = field_name.replace(" ","_")
     59        field_name = field_name.replace(".","")
     60        field_name = field_name.replace("/","")
     61        field_name = field_name.replace("-","_")   
     62        field_name = field_name.replace("(","_")     
     63        field_name = field_name.replace(")","_") 
     64        field_name = field_name.replace(",","_") 
     65        field_name = field_name.lower()
     66        rows[0][i].value = field_name
     67       
     68        #store string and numbers as strings
     69        type = "string"
     70        if rows[1][i].ctype == 3:
     71            type = "date"   
     72
     73        #print "Adding Field #" + str(i) + " Name:" + field_name
     74        fields.append(db.Field(field_name , type))
     75   
     76    #This presumes that if the temp table is in the DB, it will have the same format, and the records can be appended
     77    if table not in db:
     78        db.define_table(table, migrate=True, *fields)
     79    if truncate_table:
     80       db[table].truncate()
     81    #db.commit()
     82       
     83    row_string = ""
     84    fields = {}
     85   
     86    for i in xrange(header_row,sheet.nrows): #TEST DATA - not getting it all sheet.nrows): #
     87        #Get a new row of data
     88        del rows[1]
     89        rows.append(sheet.row(i))
     90        #print rows
     91       
     92        #If the first 3 cells is empty, this is the end of the data
     93        if rows[1][0].value == '' and rows[1][1].value == '' and rows[1][2].value == '':
     94            break
     95           
     96        #if the first cell of this row is merged, skip the entire row
     97        if i in merged_rows:
     98            continue
     99       
     100        #Cut Columns
     101        for j in reversed(cut_columns):
     102            del rows[1][j]
     103        rows[1] = rows[1][:num_columns]
     104
     105        for j in xrange(0, len(rows[0]) ):
     106            cell_value = rows[1][j].value
     107           
     108            #print str(rows[0][j].value) + ":" + str(cell_value)
     109           
     110            if rows[1][j].ctype == 1: # String
     111                cell_value = cell_value.replace("," , "") # get rid of commas   
     112                cell_value = cell_value.replace("'" , "") # get rid of commas                   
     113                cell_value = cell_value.replace(r'\xa0' , "") # get rid of special char - not working   
     114               
     115                #remove '\x' and the 2 caracters that follow it  - THIS NEEDS TO BE FIXED
     116                index = cell_value.find(r'\x')
     117                if index <> -1: # #\x found
     118                    #print "removing messy chars: " + cell_value[index:index+4]
     119                    del cell_value[index:index+4]
     120                   
     121            elif rows[1][j].ctype == 3: # Date                 
     122                #Convert Date
     123                date_tuple = xlrd.xldate_as_tuple(cell_value,0)
     124                date = datetime.date(*date_tuple[:3])
     125                cell_value = date.isoformat()
     126            else: # not Date
     127                cell_value = str(cell_value)   
     128           
     129            if db[table][str(rows[0][j].value)].type == "date" and rows[1][j].ctype <> 3: #Date
     130                if cell_value <> "" and cell_value <> "-" and cell_value <> "?":
     131                    #This should print errors to a file
     132                    #print "Date Error: '" + cell_value + "' (" + str(i) + "," + str(j)
     133                    pass
     134                cell_value = None
     135
     136            if cell_value == '' or cell_value == '-':
     137                cell_value = None           
     138           
     139            #field:value distionary pair
     140            fields[str(rows[0][j].value)] = cell_value
     141       
     142        #print fields
     143           
     144        db[table].insert(**fields)   
     145       
     146    db.commit()
     147   
     148    return   
     149   
     150def add_lookup_data(lookup_table,
     151                    lookup_field,
     152                    **args):
     153    """
     154    args:
     155    lookup_table - string - the table which the data is being added to
     156    lookup_field - string - the field which in the lookup_table which the new data is being added to
     157    lookup_field_string_size - int - restriction on the length of the string
     158    lookup_field_is_int - validates the lookup aas an int
     159    lookup_field_alt - string - an altenative field for the new data to be added, this field should be a string
     160    source_data - list.dict(table,fileld) - the (temporary) table(s) where the imported data is being read from     
     161    """
     162    db[lookup_table].truncate()
     163    if lookup_table == "pr_person":
     164        db.pr_pentity.truncate()
     165   
     166    read_table = args["source_data"][0]["table"]
     167    read_field = args["source_data"][0]["field"]
     168   
     169    #If >1 source tables, create a temporary table
     170    if len(args["source_data"]) > 1:
     171        read_table = "temp"
     172        read_field = lookup_field
     173        try:
     174            db.executesql("DROP TABLE temp")       
     175        except:
     176            pass
     177           
     178        db.executesql("CREATE TABLE temp( " + lookup_field+ " string)")       
     179        for source in args["source_data"]:       
     180            db.executesql("INSERT INTO temp(" + lookup_field+ ") SELECT DISTINCT " + source["table"] + "." + source["field"] + " FROM " + source["table"]) 
     181            #print "INSERT INTO temp(" + lookup_field+ ") SELECT DISTINCT " + source["table"] + "." + source["field"] + " FROM " + source["table"]
     182
     183    #Create additional columns to store IDs for the lookup data in the source tables                 
     184    for source in args["source_data"]:
     185        try:
     186            db.executesql("ALTER TABLE " + source["table"] + " ADD " + source["field"] + "_" + lookup_table + "_id integer")           
     187            #print  "ID field added ok"           
     188        except:
     189            pass           
     190   
     191    unique_i = 1
     192   
     193    #print db.executesql("SELECT DISTINCT " + read_field + " FROM " + read_table + " ORDER BY " + read_field)
     194   
     195    for data in db.executesql("SELECT DISTINCT " + read_field + " FROM " + read_table):   
     196        if data[0] == "NULL" or data[0] == None:
     197            continue
     198           
     199        if lookup_table == "pr_person":         
     200            person_split = data[0].split(" ")
     201            #pad the end, so that the are always three names
     202            for i in xrange(len(person_split), 3):
     203                person_split.append("")
     204           
     205            #Each person needs an entity
     206            pr_pe_id = db.pr_pentity.insert(opt_pr_entity_type=1,label=None)
     207            new_id = db.pr_person.insert(pr_pe_id = pr_pe_id,
     208                                         pr_pe_label=None,
     209                                         preferred_name = data[0],
     210                                         first_name = person_split[0] ,
     211                                         middle_name = person_split[1] ,
     212                                         last_name = person_split[2])
     213       
     214        #Data must be an Integer
     215        elif args.get("lookup_field_is_int",False):
     216            data_int = validate_int(data)
     217           
     218            if data_int <> None:
     219                if db(db[lookup_table][lookup_field] == data_int).count() ==0:
     220                    #print "data int: " + str(data_int)
     221                    new_id = db[lookup_table].insert(**{lookup_field: data_int})               
     222                    #print "data int added"               
     223            else:
     224                unique_i = unique_i+1 # unique dummy value
     225                #print "non data int: " + data[0] + "unique_i: " + str(unique_i)               
     226                new_id = db[lookup_table].insert(**{lookup_field: unique_i, args["lookup_field_alt"]:data[0]})   
     227       
     228        #There is a restriction on the length of the data string
     229        elif "lookup_field_string_size" in args:       
     230            #FIX - Errors could occur if the cut data isn't unique
     231            if len(data[0]) > args["lookup_field_string_size"]:
     232                #cut the data and place the full string in the alternative field
     233                data_cut = data[0][args["lookup_field_string_size"]:]
     234                new_id = db[lookup_table].insert(**{lookup_field: data_cut, args["lookup_field_alt"]: data[0]})
     235            else:
     236                new_id = db[lookup_table].insert(**{lookup_field: data[0]})     
     237       
     238        #The field is a string with no restriction
     239        else:
     240            new_id = db[lookup_table].insert(**{lookup_field: data[0]})             
     241       
     242        #Add Lookup ID to the source tables
     243        for source in args["source_data"]: 
     244            #print "UPDATE " + source["table"] + " SET " + source["table"] + "_" + lookup_table + "_id = " + str(new_id) + " WHERE " + source["field"] + " = '" + str(data[0]) + "'"
     245            db.executesql("UPDATE " + source["table"] + " SET " + source["field"] + "_" + lookup_table + "_id = " + str(new_id) + " WHERE " + source["field"] + " = '" + str(data[0]) + "'")
     246           
     247        try:
     248            db.executesql("DROP TABLE temp")       
     249        except:
     250            pass
     251           
     252    return
     253       
     254def format_price(price):
     255
     256    currency_map = dict( FEC= 'FEC', MMK = 'MMK', kyat= 'MMK', USD= 'USD', Kyats = 'MMK', THB = 'THB', GBP = 'GBP', USUSD = 'USD', Bath = 'THB', Kyat = 'MMK')         
     257       
     258    #lookup currency - this code is duplicated
     259    if price <>  None:
     260        #print "Value: " + str(price)
     261        try:
     262            price = float(price)
     263            currency_id = db(db.fin_currency.name == "MMK").select(db.fin_currency.id)[0]['id']
     264        except: 
     265            #print "price except: " + str(value)
     266            price = price.replace("$","USD")
     267            #price = price.replace("£","GBP") Error
     268            price = price.replace(" ","")
     269            #print currency_map
     270            for key, data in currency_map.iteritems():
     271                if price.find(key) <> -1:
     272                    price = price.replace(key, "")
     273                    #print "price: " + price + " Key: " + key
     274                    currency_id = db(db.fin_currency.name == data).select(db.fin_currency.id)[0]['id']     
     275                    try:
     276                        price = float(price)
     277                    except:
     278                        #print "OT Price ERROR!!! '" + str(price) + "'"   
     279                        pass
     280                    break       
     281    else:
     282        currency_id = None
     283                       
     284    return price, currency_id
     285       
     286def validate_date(date_str):
     287    if date_str <> None and date_str <> "":
     288        date_str = date_str.replace("NA","")   
     289        try:
     290            time.strptime(date_str,"%Y-%m-%d")
     291        except:
     292            #print "ERROR Date: " + str(date_str)   
     293            date_str = None
     294    else:
     295        date_str = None
     296    return date_str
     297
     298def validate_int(int_str):
     299    return_int = None
     300    if int_str <> None:         
     301        try:
     302            return_int  = int(int_str[0].replace(".0","")) # trim all decimal
     303        except:
     304            pass
     305    return return_int
     306
     307def find_key(dic, val):
     308    #
     309    """return the key of dictionary dic given the value"""
     310    #
     311    return [k for k, v in dic.iteritems() if v == val][0]   
     312   
     313
     314   
     315def import_excel_data():
     316
     317    import time
     318   
     319    #print "--------------------------------------------------------------------------"
     320
     321    #OT
     322    cut_columns = [5]   
     323    for i in xrange(19,32):
     324        cut_columns.append(i)
     325       
     326    cut_columns.append(41)
     327   
     328    append_column_names= {42:"a_",43:"a_",44:"a_",45:"b_",46:"b_",47:"b_",48:"c_",49:"c_",50:"c_"}
     329    #import_excel_sheet(table, filename, num_columns, sheet_index=0, header_row=0,  cut_columns=(), append_column_names={}):
     330
     331    import_excel_sheet( "ot_temp",
     332                        r'/tmp/import/ot.xls',
     333                        39,
     334                        2,
     335                        5,
     336                        True,
     337                        cut_columns,
     338                        append_column_names)
     339                       
     340    #ASSET
     341    append_column_names= {18:"cost_",20:"cost_",26:"sale_",27:"sale_"}
     342    import_excel_sheet( "asset_temp",
     343                        r'/tmp/import/asset.xls',
     344                        31,
     345                        0,
     346                        2,
     347                        True,
     348                        [],
     349                        append_column_names)
     350                       
     351    #CATALOG                     
     352    for i in xrange(1,8):
     353        if i == 1:
     354            truncate_table=True
     355            num_columns = 12 #for sub_cat
     356        else:
     357            truncate_table=False
     358            num_columns = 11
     359        import_excel_sheet( "catalog_temp",
     360                            r'/tmp/import/catalog.xls',
     361                            num_columns,
     362                            i,
     363                            3,
     364                            truncate_table)                           
     365
     366    #CATEGORIES   
     367    import_excel_sheet( table = "cat_temp",
     368                        filename = r'/tmp/import/cat.xls',
     369                        num_columns = 3,
     370                        header_row=1,
     371                        truncate_table=True)                                 
     372
     373    import_excel_sheet( table = "cat_map_temp",
     374                        filename = r'/tmp/import/cat_map.xls',
     375                        num_columns = 4,
     376                        header_row=1,
     377                        truncate_table=True)                           
     378
     379    #UPDATE LOOKUPS
     380    #Fix the IDs for these in dictionaries (with the string from the orig table as the key)
     381    #Could these be functions?
     382    #people
     383    add_lookup_data(    lookup_table = "pr_person",
     384                        lookup_field = "name",                       
     385                        source_data =   [{"table": "ot_temp", "field": "order_requestor"},
     386                                    {"table": "ot_temp", "field": "received_by"},
     387                                    {"table": "asset_temp", "field": "person_responsible"} ]
     388                       )   
     389     
     390    #code
     391    add_lookup_data(    lookup_table = "fin_code",
     392                        lookup_field = "name",     
     393                        lookup_field_is_int = True,
     394                        lookup_field_alt = "comments",
     395                        source_data =   [{"table": "asset_temp", "field": "account_code"}]
     396                       )       
     397
     398    #contract
     399    add_lookup_data(    lookup_table = "fin_contract",
     400                        lookup_field = "name",                       
     401                        source_data =   [{"table": "asset_temp", "field": "contract"}],
     402                        lookup_field_string_size = 6,
     403                        lookup_field_alt = "comments"
     404                       )         
     405           
     406    #project
     407    add_lookup_data(    lookup_table = "fin_project",
     408                        lookup_field = "name",                       
     409                        source_data =   [{"table": "asset_temp", "field": "project_id"}],
     410                        lookup_field_string_size = 6,
     411                        lookup_field_alt = "comments"
     412                       )     
     413    #unit
     414    add_lookup_data(    lookup_table = "mat_unit",
     415                        lookup_field = "name",                       
     416                        source_data =   [{"table": "ot_temp", "field": "unit"},
     417                                    {"table": "catalog_temp", "field": "unit"} ]
     418                       )       
     419                       
     420    #supplier
     421    add_lookup_data(    lookup_table = "mat_supplier",
     422                        lookup_field = "name",                       
     423                        source_data =   [{"table": "ot_temp", "field": "sourced_from__supplier_name"},
     424                                         {"table": "ot_temp", "field": "a_supplier"},
     425                                         {"table": "ot_temp", "field": "b_supplier"},
     426                                         {"table": "ot_temp", "field": "c_supplier"},
     427                                         {"table": "catalog_temp", "field": "supplier"} ]
     428                       )     
     429                       
     430    #category
     431    try:
     432        db.executesql("ALTER TABLE cat_temp ADD mat_category_id integer")
     433    except:
     434        pass
     435       
     436    try:
     437        db.executesql("ALTER TABLE cat_map_temp ADD mat_category_id integer")
     438    except:
     439        pass
     440
     441    try:
     442        db.executesql("ALTER TABLE cat_map_temp ADD mat_sub_category_id integer")
     443    except:
     444        pass
     445   
     446    #Add categories
     447    db.mat_category.truncate()
     448    for data in db.executesql("SELECT DISTINCT category_name, category_description  FROM cat_temp"):
     449        new_id = db.mat_category.insert(name= data[0], description= data[1])
     450        db.executesql("UPDATE cat_temp SET mat_category_id = " + str(new_id) + " WHERE category_name = '" + str(data[0]) + "'")     
     451       
     452    #Add Sub-categories
     453    db.mat_sub_category.truncate()
     454    for category_name, sub_category, mat_category_id in db.executesql("SELECT DISTINCT category_name, sub_category, mat_category_id  FROM cat_temp"):   
     455        new_id = db.mat_sub_category.insert(name= sub_category, category_id = mat_category_id)   
     456       
     457        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
     458        db.executesql("UPDATE cat_map_temp SET mat_sub_category_id = " + str(new_id) + ", mat_category_id = " +  str(mat_category_id) +
     459                      " WHERE category_name = '" + category_name + "' AND sub_category = '" + sub_category + "'")         
     460   
     461    #item
     462    db.mat_item.truncate()
     463    try:
     464        db.executesql("ALTER TABLE catalog_temp ADD mat_item_id integer")   
     465    except:
     466        pass
     467           
     468    currency_map = dict( FEC= 'FEC', MMK = 'MMK', kyat= 'MMK', USD= 'USD', Kyats = 'MMK', THB = 'THB', GBP = 'GBP', USUSD = "USD", Bath = 'THB', Kyat = 'MMK')       
     469   
     470    #Add All of the Items from the catalog (because this has the most complete details)   
     471    for name, \
     472        sub_category, \
     473        category, \
     474        unit_id, \
     475        price, \
     476        currency, \
     477        supplier_id, \
     478        comments \
     479        in db.executesql("SELECT DISTINCT  description__item, " +
     480                                          "sub_category," +
     481                                          "cat," +
     482                                          "unit_mat_unit_id, " +
     483                                          "price, " +
     484                                          "currency, " +
     485                                          "supplier_mat_supplier_id, " +
     486                                          "remarks__quality__delay__discount_if_big_amount " +
     487                         "FROM catalog_temp"):   
     488       
     489        if sub_category <>  None:
     490            #print "sub category: " +  str(sub_category)
     491            #print "SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(sub_category) + "'"
     492            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(sub_category) + "'")
     493            #print "cat_map_row length: " + str(len(cat_map_row))
     494            #print cat_map_row
     495        elif category <> None:
     496            #print "category: " +  str(category)
     497            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(category) + "'")
     498            #print "cat_map_row length: " + str(len(cat_map_row))
     499            #print cat_map_row           
     500        else:
     501            #print "Item: " + name
     502            #print "NO CATEGORY LISTED!!!"
     503            pass
     504           
     505        if len(cat_map_row) >= 1:
     506            category_id = cat_map_row[0][0]
     507            sub_category_id = cat_map_row[0][1]       
     508       
     509        if currency <> None:       
     510            #print " Currency: " + str(currency)   
     511            currency = currency.replace("$","USD")           
     512            currency_id = db(db.fin_currency.name == currency_map[currency]).select(db.fin_currency.id)[0]['id']           
     513        else:
     514            currency_id =None
     515           
     516        if price <>  None:
     517            try:
     518                unit_cost = float(price)
     519            except:
     520                #print "Catalog Price ERROR!!! '" + str(price) + "'"
     521                pass
     522        else:
     523            unit_cost = None
     524           
     525        new_id = db.mat_item.insert(name= name,
     526                                    category_id = category_id,
     527                                    sub_category_id = sub_category_id,
     528                                    unit_id = unit_id,
     529                                    unit_cost = unit_cost,
     530                                    currency_id = currency_id,
     531                                    supplier_id = supplier_id,
     532                                    comments = comments
     533                                    )   
     534       
     535        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
     536        db.executesql("UPDATE catalog_temp SET mat_item_id = " + str(new_id) + "WHERE description__item = '" + name + "'" )
     537   
     538    #Add Items from Asset
     539    try:
     540        db.executesql("ALTER TABLE asset_temp ADD mat_item_id integer")   
     541    except:
     542        pass   
     543       
     544    try:
     545        db.executesql("ALTER TABLE asset_temp ADD category_id integer")   
     546    except:
     547        pass           
     548       
     549    try:
     550        db.executesql("ALTER TABLE asset_temp ADD sub_category_id integer")   
     551    except:
     552        pass               
     553       
     554    for name, \
     555        category, \
     556        price, \
     557        currency, \
     558        comments \
     559        in db.executesql("SELECT DISTINCT  description, " +
     560                                          "category," +
     561                                          "cost_amount, " +
     562                                          "asset_temp.cost_in_local_currency, " +
     563                                          "comments " +
     564                         "FROM asset_temp"): 
     565                       
     566        if category <> None:
     567            cat_map_row = db.executesql("SELECT mat_category_id, mat_sub_category_id FROM cat_map_temp WHERE key = '" + str(category) + "'")         
     568
     569
     570        if len(cat_map_row) >= 1:
     571            sub_category_id = 0       
     572            category_id = cat_map_row[0][0]
     573            sub_category_id = cat_map_row[0][1]   
     574           
     575            #write these back to the Asset_temp table
     576            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) + "'")
     577        #else:
     578            #print "cat_map_row length: " + str(len(cat_map_row))
     579            #print str(name) + "cat " + str(category)
     580
     581        if currency <> None:       
     582            #print " Currency: " + str(currency)         
     583            currency = currency.replace("$","USD")
     584            currency_id = db(db.fin_currency.name == currency_map[currency]).select(db.fin_currency.id)[0]['id']     
     585       
     586        else:
     587            currency_id =None
     588           
     589        if price <>  None:
     590            try:
     591                unit_cost = float(price)
     592            except:
     593                #print "Asset Price ERROR!!! '" + str(price) + "'"
     594                pass
     595               
     596        else:
     597            unit_cost = None   
     598
     599        current_item_rows = db(db.mat_item.name == name).select(db.mat_item.id,db.mat_item.name,
     600                                                           db.mat_item.category_id,
     601                                                           db.mat_item.sub_category_id,
     602                                                           db.mat_item.unit_cost,
     603                                                           db.mat_item.currency_id)
     604       
     605        if len(current_item_rows) == 0:
     606            #Add Item
     607            item_id = db.mat_item.insert(name= name,
     608                                        category_id = category_id,
     609                                        sub_category_id = sub_category_id,
     610                                        unit_cost = unit_cost,
     611                                        currency_id = currency_id,
     612                                        comments = comments
     613                                        )                           
     614        else:           
     615            #test to see if any additional information can be added
     616            current_item = current_item_rows[0]
     617           
     618            item_id = current_item['id']
     619           
     620            if current_item['sub_category_id'] == None and sub_category_id <> 0:
     621                db(db.mat_item.name == name).update(category_id = category_id, sub_category_id = sub_category_id)
     622                #db(db.mat_item.name == name).update(db.mat_item.sub_category_id = sub_category_id)
     623               
     624            #don't change the currency if there is a cost but no currency
     625            if current_item['unit_cost'] == None and current_item['currency_id'] == None and currency_id <> None:
     626                db(db.mat_item.name == name).update(currency_id = currency_id)                       
     627               
     628            if current_item['unit_cost'] == None and unit_cost <> None:
     629                db(db.mat_item.name == name).update(unit_cost = unit_cost)           
     630
     631        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
     632        db.executesql("UPDATE asset_temp SET mat_item_id = " + str(item_id) + "WHERE description = '" + name + "'" )           
     633   
     634    #Add Items from OT
     635    try:
     636        db.executesql("ALTER TABLE ot_temp ADD mat_item_id integer")   
     637    except:
     638        pass   
     639       
     640    for name, \
     641        unit_id, \
     642        supplier_id, \
     643        price \
     644        in db.executesql("SELECT DISTINCT description, " +
     645                                          "unit_mat_unit_id," +
     646                                          "sourced_from__supplier_name_mat_supplier_id," +
     647                                          "actual_unit_price_in_currency " +
     648                         "FROM ot_temp"):                   
     649
     650        if name  == None:
     651            continue # skip this line
     652
     653        unit_cost, currency_id = format_price(price)
     654
     655        current_item_rows = db(db.mat_item.name == name).select(db.mat_item.id,db.mat_item.name,
     656                                                           db.mat_item.unit_id,
     657                                                           db.mat_item.unit_cost,
     658                                                           db.mat_item.currency_id,
     659                                                           db.mat_item.supplier_id)
     660       
     661        if len(current_item_rows) == 0:
     662            #Add Item
     663            item_id = db.mat_item.insert(name= name,
     664                                        unit_id = unit_id,
     665                                        unit_cost = unit_cost,
     666                                        currency_id = currency_id,
     667                                        supplier_id = supplier_id
     668                                        )                           
     669        else:           
     670            #test to see if any additional information can be added
     671            current_item = current_item_rows[0]
     672           
     673            item_id = current_item['id']
     674           
     675            if current_item['unit_id'] == None and unit_id <> None:
     676                db(db.mat_item.name == name).update(unit_id = unit_id)               
     677               
     678            #don't change the currency if there is a cost but no currency
     679            if current_item['unit_cost'] == None and current_item['currency_id'] == None and currency_id <> None:
     680                db(db.mat_item.name == name).update(currency_id = currency_id)                       
     681               
     682            if current_item['unit_cost'] == None and unit_cost <> None:
     683                db(db.mat_item.name == name).update(unit_cost = unit_cost)     
     684
     685            if current_item['supplier_id'] == None and supplier_id <> None:
     686                db(db.mat_item.name == name).update(supplier_id = supplier_id)                 
     687
     688        #Set mat_category_id and mat_sub_category_id in cat_map_temp table
     689        db.executesql("UPDATE ot_temp SET mat_item_id = " + str(item_id) + " WHERE description = '" + name + "'" )           
     690                         
     691    #donor
     692    add_lookup_data(    lookup_table = "asset_donor",
     693                        lookup_field = "name",                       
     694                        source_data =   [{"table": "asset_temp", "field": "donor"}]
     695                       )
     696                       
     697    #office
     698    add_lookup_data(    lookup_table = "asset_office",
     699                        lookup_field = "name",                       
     700                        source_data =   [{"table": "asset_temp", "field": "office"}],
     701                        lookup_field_string_size = 3,
     702                        lookup_field_alt = "comments"                       
     703                       )
     704
     705    db.asset_office.insert(
     706        name = 'Y+D',
     707        comments = 'Yangon  + Dedaye'
     708    )   
     709    db.asset_office.insert(
     710        name = 'BOG',
     711        comments = 'Bogale'
     712    )
     713    db.asset_office.insert(
     714        name = 'DRY',
     715        comments = 'Dryzone'
     716    )                           
     717   
     718    #location
     719    add_lookup_data(    lookup_table = "asset_location",
     720                        lookup_field = "name",                       
     721                        source_data =   [{"table": "asset_temp", "field": "location"}]
     722                       )
     723                       
     724    #sub_location   
     725    add_lookup_data(    lookup_table = "asset_sub_location",
     726                        lookup_field = "name",                       
     727                        source_data =   [{"table": "asset_temp", "field": "sub_location"}]
     728                       )
     729   
     730    #Assets 
     731    db.asset_asset.truncate()
     732   
     733    #key = asset_asset fields, value = asset_temp fields
     734    #Used for storing the data to be inserted
     735    asset_insert_dict = dict(name = 'ref',
     736    date = 'date_of_purchase',
     737    financial_year = 'year',
     738    donor = 'donor_asset_donor_id',
     739    code = 'account_code_fin_code_id',
     740    contract = 'contract_fin_contract_id',
     741    project = 'project_id_fin_project_id',
     742    type = 'type',
     743    item_id = 'mat_item_id',
     744    category_id = 'category_id',
     745    sub_category_id = 'sub_category_id',   
     746    person = 'person_responsible_pr_person_id',
     747    tag = 'asset_tag_no',
     748    serial = 'serial_no',
     749    office_id = 'office_asset_office_id',                                     
     750    location = 'location_asset_location_id',
     751    sub_location = 'sub_location_asset_sub_location_id',
     752    po_no = 'po_no',
     753    purchase_currency = 'cost_in_local_currency', #LOOK UP REQ
     754    purchase_cost = 'cost_amount',
     755    purchase_exchange_rate = 'cost_mthly_exchange_rate',
     756    gbp_purchase_cost = 'cost_in_gbp',
     757    guarantee = 'date_of_guarantee__expiration',
     758    status = 'status',
     759    disposal = 'date_of_disposal',
     760    sale_currency = 'sale_value_in_local_currency',
     761    sale_value = 'sale_amount',
     762    sale_exchange_rate = 'sale_mthly_exchange_rate',
     763    gbp_sale_value = 'sale_value_gbp',
     764    donation = 'donation',
     765    comments = 'comments')   
     766   
     767    #key = asset_temp fields, value = asset_asset fields
     768    #Used for mapping the asset_temp fields to the asset_asset fields   
     769    asset_fields_dict = {}
     770   
     771    #list = asset_temp fields
     772    asset_fields = []
     773    sql_str = "SELECT "
     774   
     775    for key,value in asset_insert_dict.iteritems():
     776        asset_fields_dict[value] = key
     777        asset_fields.append(value)
     778        sql_str = sql_str + value + ","
     779       
     780    sql_str = sql_str[:-1] + " FROM asset_temp"
     781
     782    #print sql_str
     783   
     784    type_map = { "str": "string", "unicode": "string", "int": "reference"}
     785   
     786    for asset_row in db.executesql(sql_str):
     787        for field, data in zip(asset_fields,asset_row):
     788            if field == 'cost_in_local_currency' and data <> None: # Map the currency to the lookup table
     789                data = data.replace("$","USD")
     790                data = db(db.fin_currency.name == currency_map[data]).select(db.fin_currency.id)[0]['id']   
     791            if type(data).__name__ <> "NoneType" and data <> None:
     792                #print "temp type: " + type(data).__name__ + "\tasset type: " + str(db.asset_asset[asset_fields_dict[field]].type)         
     793                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
     794                if type_map[type(data).__name__] <> table_field_type:
     795                    #try to convert the date
     796                    if table_field_type == 'date':
     797                        data = validate_date(data)   
     798                       
     799                    #try to convert to float
     800                    elif table_field_type == 'double':
     801                        try:
     802                            data = float(data)
     803                        except:
     804                            #print "ERROR double: " + str(data)
     805                            #print "field: " + field + "\tdata: " + str(data)       
     806                            pass
     807                           
     808                    else:
     809                        #print "ERROR other type"
     810                        #print "type: " + table_field_type + "\tfield: " + field + "\tdata: " + str(data)               
     811                        pass
     812            asset_insert_dict[asset_fields_dict[field]] = data 
     813           
     814        #print asset_insert_dict
     815        new_id = db.asset_asset.insert(**asset_insert_dict)     
     816               
     817    #OT
     818    #add purchase_request
     819    db.ot_purchase_request.truncate()
     820    try:
     821        db.executesql("ALTER TABLE ot_temp ADD request_id integer")   
     822    except:
     823        pass       
     824   
     825    office_map = { "Yangon": "YGN" , "Dedaye": "DDY","Dedaya": "DDY", "Yangon  + Dedaye": "Y+D", "Bogale": "BOG", "Dryzone": "DRY"}
     826
     827    for name, \
     828        date_received, \
     829        requestor, \
     830        office, \
     831        date_processed, \
     832        comments \
     833        in db.executesql("SELECT DISTINCT req_no, " +
     834                                          "date_received," +
     835                                          "order_requestor_pr_person_id," +
     836                                          "location," +
     837                                          "date_processed," +
     838                                          "general_information_comments " +
     839                                          "FROM ot_temp" ):           
     840
     841        #convert office  - this code is duplicated   
     842        if office <> "" and office <> None:
     843            #print office
     844            #print "office code:" + office_map[office]
     845            office_id = db(db.asset_office.name == office_map[office]).select(db.asset_office.id)[0]['id']
     846       
     847        status = 2 #Closed  - check that all the items are either completed or cancelled XXX check
     848       
     849        #Check Dates
     850        date_received = validate_date(date_received)
     851        date_processed = validate_date(date_processed)               
     852       
     853        #Insert request
     854        request_id = db.ot_purchase_request.insert(name= name,
     855                                                date_received = date_received,
     856                                                requestor = requestor,
     857                                                office_id = office_id,
     858                                                date_processed = date_processed,
     859                                                comments = comments,
     860                                                status = status
     861                                    )       
     862                                   
     863        #update request_id in temp table           
     864        #print "UPDATE ot_temp SET request_id = " + str(request_id) + " WHERE req_no = " + name
     865        db.executesql("UPDATE ot_temp SET request_id = " + str(request_id) + " WHERE req_no = '" + name + "'")                                       
     866               
     867    #add order
     868    db.ot_order.truncate()
     869    try:
     870        db.executesql("ALTER TABLE ot_temp ADD order_id integer")   
     871    except:
     872        pass       
     873
     874    for name, \
     875        supplier_id \
     876        in db.executesql("SELECT DISTINCT purchase_order_no, " +
     877                                          "sourced_from__supplier_name_mat_supplier_id " +
     878                         "FROM ot_temp"):           
     879
     880        status = 2 #Closed  - check that all the items are either completed or cancelled XXX check
     881       
     882        #if there is a PO#
     883        if name <> "-" and name <> None:
     884            #Insert Purchase
     885            order_id = db.ot_order.insert(name= name, supplier_id = supplier_id, status = status )       
     886            #update order_id in temp table                           
     887            db.executesql("UPDATE ot_temp SET order_id = " + str(order_id) + " WHERE purchase_order_no = '" + name + "'")   
     888   
     889    #Loop through all the orders with no PO# (but only add if there is a supplier)
     890    i = 0   
     891    for name, supplier_id in db.executesql("SELECT purchase_order_no, " +
     892                                          "sourced_from__supplier_name_mat_supplier_id " +
     893                         "FROM ot_temp WHERE order_id Is Null"):
     894        if supplier_id <> None:
     895            if name <> "-" or name <> None:
     896                name = "AUTO_PO_REF" + str(i)
     897                i = i+1
     898                #print "Adding dummy PO#" + name
     899                #Insert Purchase
     900                order_id = db.ot_order.insert(name= name, supplier_id = supplier_id, status = status )       
     901                #update order_id in temp table                           
     902                db.executesql("UPDATE ot_temp SET order_id = " + str(order_id) + " WHERE purchase_order_no = '" + name + "'" )         
     903            else:
     904                #print "Error these PO shouldn't include those with numbers Name: " + str(name)                                                                                                   
     905                pass
     906
     907    #add invoice
     908    db.fin_invoice.truncate()
     909    try:
     910        db.executesql("ALTER TABLE ot_temp ADD invoice_id integer")   
     911    except:
     912        pass       
     913
     914    for id, \
     915        order_id, \
     916        date_in, \
     917        date_out, \
     918        value, \
     919        comments \
     920        in db.executesql("SELECT id, order_id, date_supplier_invoice_received, date_paid_by_finance, amount_paid, finance_comments FROM ot_temp"): 
     921
     922        date_in = validate_date(date_in)
     923        date_out = validate_date(date_out)   
     924
     925        #lookup currency - this code is duplicated
     926        value, currency_id = format_price(value)   
     927
     928        if value <>  None:                                             
     929            #Insert Invoice (if there is a value)
     930            invoice_id = db.fin_invoice.insert(order_id= order_id,
     931                                            date_in =date_in,
     932                                            date_out = date_out,
     933                                            value = value,
     934                                            comments = comments )   
     935                                           
     936            #print "Item id: " + str(id) + "\tinvoice_ id: " + str(invoice_id)                                 
     937                                           
     938            db.executesql("UPDATE ot_temp SET invoice_id = " + str(invoice_id) + " WHERE id = " + str(id) ) 
     939
     940    #add grn
     941    db.ot_grn.truncate()
     942   
     943    try:
     944        db.executesql("ALTER TABLE ot_temp ADD grn_id integer")   
     945    except:
     946        pass 
     947   
     948    i=0   
     949    for id, \
     950        name, \
     951        date_received, \
     952        received_by, \
     953        office \
     954        in db.executesql("SELECT id, grn_no, date_arrived_in_location, received_by_pr_person_id, location FROM ot_temp"): 
     955
     956        date_received = validate_date(date_received)                 
     957       
     958        #convert office           
     959        if office <> "" and office <> None:
     960            #print "Office: " + str(office)
     961            office_id = db(db.asset_office.name == office_map[office]).select(db.asset_office.id)[0]['id']               
     962
     963        #If there is GRN Data
     964        if name <> None or date_received <> None or received_by <> None:
     965            name = "AUTO_GRN_REF" + str(i)
     966            i = i+1       
     967           
     968            #Insert GRN
     969            grn_id = db.ot_grn.insert(name= name,
     970                                        date_received =date_received,
     971                                        received_by = received_by,
     972                                        office_id = office_id )
     973            db.executesql("UPDATE ot_temp SET grn_id = " + str(grn_id) + " WHERE id = " + str(id) ) 
     974           
     975    #add waybills
     976    db.ot_waybill.truncate()
     977    i=0
     978    for index in ('a','b','c'):
     979   
     980        try:
     981            db.executesql("ALTER TABLE ot_temp ADD waybill_id_" + index + "  integer")   
     982        except:
     983            pass 
     984           
     985           
     986        for id, \
     987            date_dispatched, \
     988            supplier_id, \
     989            despatched_qty \
     990            in db.executesql("SELECT id, " + index +  "_date, " + index +  "_supplier_mat_supplier_id, " + index +  "_despatched_qty FROM ot_temp"): 
     991
     992            date_dispatched = validate_date(date_dispatched)                               
     993
     994            #If there is WB Data
     995            if date_dispatched <> None or despatched_qty <> None:
     996                name = "AUTO_WB_REF" + str(i)
     997                i = i+1     
     998               
     999                #InsertWB
     1000                wb_id = db.ot_waybill.insert(name = name, date_dispatched= date_dispatched,
     1001                                                supplier_id = supplier_id )
     1002                db.executesql("UPDATE ot_temp SET waybill_id_" + index + " = " + str(wb_id) + " WHERE id = " + str(id) )       
     1003   
     1004    #add ot_line_items
     1005    db.ot_line_item.truncate()
     1006   
     1007    try:
     1008        db.executesql("ALTER TABLE ot_temp ADD wb_id_" + index + "  integer")   
     1009    except:
     1010        pass 
     1011       
     1012    for id, \
     1013        request_id, \
     1014        line, \
     1015        status, \
     1016        order_id, \
     1017        grn_id, \
     1018        a_waybill_id, \
     1019        b_waybill_id, \
     1020        c_waybill_id, \
     1021        invoice_id, \
     1022        item_id, \
     1023        quantity_pr, \
     1024        quantity_po, \
     1025        quantity_grn, \
     1026        a_quantity_waybill, \
     1027        b_quantity_waybill, \
     1028        c_quantity_waybill, \
     1029        unit_id, \
     1030        budgeted_unit_price, \
     1031        actual_unit_price, \
     1032        actual_total_price, \
     1033        comments \
     1034        in db.executesql("SELECT id, " +
     1035                            "request_id, " +
     1036                            "line_item, " +
     1037                            "status, " +
     1038                            "order_id, " +
     1039                            "grn_id, " +
     1040                            "waybill_id_a, " +
     1041                            "waybill_id_b, " +
     1042                            "waybill_id_c, " +                           
     1043                            "invoice_id, " +
     1044                            "mat_item_id, " +
     1045                            "quanity, " +
     1046                            "qty, " +
     1047                            "qty, " +
     1048                            "a_despatched_qty, " +
     1049                            "b_despatched_qty, " +
     1050                            "c_despatched_qty, " +                           
     1051                            "unit_mat_unit_id, " +
     1052                            "budgeted_unit_price, " +
     1053                            "actual_unit_price_in_currency, " +
     1054                            "actual_total_price_in_currency, " +
     1055                            "general_information_comments " +
     1056                            "FROM ot_temp"): 
     1057       
     1058        #convert status
     1059        status = find_key( ot_line_item_status_opts, status)
     1060               
     1061                #Calculate prices and currency
     1062        budgeted_unit_price, budgeted_currency_id = format_price(budgeted_unit_price)
     1063        actual_unit_price, actual_currency_id = format_price(actual_unit_price)
     1064       
     1065        line = validate_int(line)
     1066        quantity_pr = validate_int(quantity_pr)
     1067        quantity_po = validate_int(quantity_po)
     1068        quantity_grn = validate_int(quantity_grn)       
     1069        a_quantity_waybill = validate_int(a_quantity_waybill)
     1070        b_quantity_waybill = validate_int(b_quantity_waybill)
     1071        c_quantity_waybill = validate_int(c_quantity_waybill)
     1072
     1073               
     1074        line_item_id = db.ot_line_item.insert( request_id = request_id,
     1075                                line =  line ,
     1076                                status = status,
     1077                                item_id = item_id,             
     1078                                quantity_pr = quantity_pr,
     1079                                unit_id = unit_id,
     1080                                budgeted_unit_price = budgeted_unit_price,
     1081                                comments = comments)   
     1082
     1083
     1084        if order_id <> None:   
     1085            db(db.ot_line_item.id == line_item_id).update(superseded=True)           
     1086            line_item_id = db.ot_line_item.insert( request_id = request_id,
     1087                                    line =  line ,
     1088                                    status = status,
     1089                                    order_id = order_id,
     1090                                    invoice_id = invoice_id,
     1091                                    item_id = item_id,                 
     1092                                    quantity_pr = quantity_pr,
     1093                                    quantity_po = quantity_po,
     1094                                    quantity_grn = quantity_grn,
     1095                                    quantity_waybill = a_quantity_waybill,
     1096                                    unit_id = unit_id,
     1097                                    budgeted_unit_price = budgeted_unit_price,
     1098                                    actual_unit_price = actual_unit_price,
     1099                                    actual_total_price = actual_total_price,
     1100                                    comments = comments)   
     1101
     1102
     1103        if grn_id <> None:             
     1104            db(db.ot_line_item.id == line_item_id).update(superseded=True)
     1105            line_item_id = db.ot_line_item.insert( request_id = request_id,
     1106                                    line =  line ,
     1107                                    status = status,
     1108                                    order_id = order_id,
     1109                                    grn_id = grn_id,
     1110                                    invoice_id = invoice_id,
     1111                                    item_id = item_id,                 
     1112                                    quantity_pr = quantity_pr,
     1113                                    quantity_po = quantity_po,
     1114                                    quantity_grn = quantity_grn,
     1115                                    quantity_waybill = a_quantity_waybill,
     1116                                    unit_id = unit_id,
     1117                                    budgeted_unit_price = budgeted_unit_price,
     1118                                    actual_unit_price = actual_unit_price,
     1119                                    actual_total_price = actual_total_price,
     1120                                    comments = comments)                                       
     1121
     1122        #if invoice_id <> None:
     1123        #    db(db.ot_line_item.id == line_item_id).update(superseded=True)
     1124        #    db.ot_line_item.insert( request_id = request_id,
     1125        #                            line =  line ,
     1126        #                            status = status,
     1127        #                            order_id = order_id,
     1128        #                            grn_id = grn_id,
     1129        #                            waybill_id = a_waybill_id,
     1130        #                            invoice_id = invoice_id,
     1131        #                            item_id = item_id,                 
     1132        #                            quantity_pr = quantity_pr,
     1133        #                            quantity_po = quantity_po,
     1134        #                            quantity_grn = quantity_grn,
     1135        #                            quantity_waybill = a_quantity_waybill,
     1136        #                            unit_id = unit_id,
     1137        #                            budgeted_unit_price = budgeted_unit_price,
     1138        #                            actual_unit_price = actual_unit_price,
     1139        #                            actual_total_price = actual_total_price,
     1140        #                            comments = comments)       
     1141
     1142        if a_waybill_id <> None:       
     1143            db(db.ot_line_item.id == line_item_id).update(superseded=True)
     1144            db.ot_line_item.insert( request_id = request_id,
     1145                                    line =  line ,
     1146                                    status = status,
     1147                                    order_id = order_id,
     1148                                    grn_id = grn_id,
     1149                                    waybill_id = a_waybill_id,
     1150                                    invoice_id = invoice_id,
     1151                                    item_id = item_id,                 
     1152                                    quantity_pr = quantity_pr,
     1153                                    quantity_po = quantity_po,
     1154                                    quantity_grn = quantity_grn,
     1155                                    quantity_waybill = a_quantity_waybill,
     1156                                    unit_id = unit_id,
     1157                                    budgeted_unit_price = budgeted_unit_price,
     1158                                    actual_unit_price = actual_unit_price,
     1159                                    actual_total_price = actual_total_price,
     1160                                    comments = comments)
     1161       
     1162        if b_waybill_id <> None:
     1163            db.ot_line_item.insert( request_id = request_id,
     1164                                    line =  line ,
     1165                                    status = status,
     1166                                    order_id = order_id,
     1167                                    grn_id = grn_id,
     1168                                    waybill_id = b_waybill_id,
     1169                                    invoice_id = invoice_id,
     1170                                    item_id = item_id,                 
     1171                                    quantity_pr = quantity_pr,
     1172                                    quantity_po = quantity_po,
     1173                                    quantity_grn = quantity_grn,
     1174                                    quantity_waybill = b_quantity_waybill,
     1175                                    unit_id = unit_id,
     1176                                    budgeted_unit_price = budgeted_unit_price,
     1177                                    actual_unit_price = actual_unit_price,
     1178                                    actual_total_price = actual_total_price,
     1179                                    comments = comments)       
     1180
     1181        if c_waybill_id <> None:
     1182            db.ot_line_item.insert( request_id = request_id,
     1183                                    line =  line ,
     1184                                    status = status,
     1185                                    order_id = order_id,
     1186                                    grn_id = grn_id,
     1187                                    waybill_id = c_waybill_id,
     1188                                    invoice_id = invoice_id,
     1189                                    item_id = item_id,                 
     1190                                    quantity_pr = quantity_pr,
     1191                                    quantity_po = quantity_po,
     1192                                    quantity_grn = quantity_grn,
     1193                                    quantity_waybill = c_quantity_waybill,
     1194                                    unit_id = unit_id,
     1195                                    budgeted_unit_price = budgeted_unit_price,
     1196                                    actual_unit_price = actual_unit_price,
     1197                                    actual_total_price = actual_total_price,
     1198                                    comments = comments)         
     1199                       
     1200    return SQLTABLE(db(db.ot_line_item.id<>0).select())
     1201   
     1202def clean_excel_data():
     1203    return dict(message="beginning import")
     1204
     1205def lookups_menu():
     1206    resource = 'lookups'
     1207    response.title = LOOKUPS
     1208    s3.sub_options = [
     1209        [T('Item Categories'), 'mat', 'category', T('')],
     1210        [T('Item Sub-Categories'), 'mat', 'sub_category', T('')],
     1211        [T('Units'), 'mat', 'unit', T('') ],
     1212    ]
     1213    s3.sub_options_menu = []
     1214    for option in s3.sub_options:
     1215        label = option[0]
     1216        controller = option[1]
     1217        function = option[2]
     1218        s3.sub_options_menu.append([label, False, URL(request.application, controller, function)],)
     1219    response.menu.insert(2, ['>> ' + str(response.title), False, URL(request.application, module, resource), s3.sub_options_menu])
     1220    return
     1221}}}