| Version 1 (modified by , 15 years ago) ( diff ) |
|---|
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
Note:
See TracWiki
for help on using the wiki.
