<commit_before># coding: utf8
# try something like
module = 'importer'
# Current Module (for sidebar title)
module_name = 'Spreadsheet importer' 

import gluon.contrib.simplejson as json

response.menu_options = [
    [
	    T('Spreadsheet'), False, URL(r=request, f='spreadsheet/create')
    ]
]

importer=local_import("importer")

def index(): 
    return dict(module_name=module_name)

@auth.requires_membership('Administrator')
def spreadsheet():
    crud.settings.create_onaccept = lambda form : redirect(URL(r=request, c="importer", f="spreadsheetview")) 
    return shn_rest_controller(module,'slist')

def spreadsheetview():
    k=db(db.importer_slist.id>0).select().last()
    k=k.Path;
    str=importer.pathfind(k)
    str=request.folder+str
    temp=importer.removerowcol(str)
    #appname=request.application
    v=importer.json(str,request.folder)
    #fields=shn_get_writecolumns(
    return dict(ss=v)

def slist():
    return shn_rest_controller(module,'slist',listadd=False)
    
def import_spreadsheet():
    spreadsheet = request.body.read()
    from StringIO import StringIO
    spreadsheet_json = StringIO(spreadsheet)
    spreadsheet_json.seek(0)
    #j = json.loads(spreadsheet)
    exec("j = " + spreadsheet)
    similar_rows = []
    importable_rows = []
    if j.has_key('header_row'):
         j['spreadsheet'].pop(j['header_row'])
         j['rows'] -= 1
    if j.has_key('re_import'):
        for x in range(0,len(j['map'])):
	    j['map'][x][2] = j['map'][x][2].replace('&gt;','>')
    if not j.has_key('re_import'):# and j['re_import'] is not True:
    	for x in range(0,len(j['spreadsheet'])):
	    for y in range(x+1,len(j['spreadsheet'])):
 	        k = importer.jaro_winkler_distance_row(j['spreadsheet'][x],j['spreadsheet'][y])
	        if k is True:
	           similar_rows.append(j['spreadsheet'][x])
	           similar_rows.append(j['spreadsheet'][y])
	        else:
	           pass 
	for k in similar_rows:
	    for l in k:
	       l = l.encode('ascii')
        session.similar_rows = similar_rows
    for k in j['spreadsheet']:
        if k in similar_rows:
	    j['spreadsheet'].remove(k)
	    j['rows'] -= 1
    i=k=0
    send_dict = {}
    resource = j['resource'].encode('ascii')
    send_dict[resource] = []
    while (i < j['rows']):
	res = {}
	k = 0
	while (k < j['columns']):
	    if ' --> ' in j['map'][k][2]:
		field,nested_resource,nested_field = j['map'][k][2].split(' --> ')
		field = "$k_" + field
		nr = nested_resource
		nested_resource= "$_" + nested_resource
		if res.has_key(field) is False: 
		   res[field] = {}
		   res[field]['@resource'] = nr 
		   res[field][nested_resource] = [{}]
		res[field][nested_resource][0][nested_field] = j['spreadsheet'][i][k].encode('ascii')
		k += 1
		continue
	    if "opt_" in j['map'][k][2]:
		res[j['map'][k][2].encode('ascii')]['@value'] = j['spreadsheet'][i][k].encode('ascii')
	    	res[j['map'][k][2].encode('ascii')]["$"] = j['spreadsheet'][i][k].encode('ascii')
	    else:
		res[j['map'][k][2].encode('ascii')] = j['spreadsheet'][i][k].encode('ascii')
		if j['map'][k][2].encode('ascii') == 'comments':
			res[j['map'][k][2].encode('ascii')] = j['map'][k][1] + '-->' + j['spreadsheet'][i][k].encode('ascii')
	    res['@modified_at'] = j['modtime']
	    k += 1
	send_dict[resource].append(res)
	i += 1
	
    word = json.dumps(send_dict[resource])
    #Removing all white spaces and newlines in the JSON
    new_word = ""
    cntr = 1
    for i in range(0,len(word)):
        if word[i] == "\"":
           cntr = cntr + 1
      	   cntr = cntr % 2
        if cntr == 0:
           new_word += word[i]
        if cntr == 1:
           if word[i] == " " or word[i] == "\n":
	      continue
           else:
    	      new_word += word[i] 
    #new_word is without newlines and whitespaces	  
    new_word  = "{\"$_" + resource + "\":"+ new_word + "}"
    #added resource name
    send = StringIO(new_word)
    tree = s3xrc.xml.json2tree(send)
    prefix, name = resource.split('_')
    res = s3xrc.resource(prefix, name)
    res.import_xml(source = tree, ignore_errors = True)
    returned_json = s3xrc.xml.tree2json(tree)
    if '@error' not in repr(returned_json):
	    return dict(module_name = module_name)
    invalid_rows = []
    exec('returned_json = ' + returned_json)
    for record in returned_json['$_'+resource]:
	 del record['@modified_at']
    for record in returned_json['$_'+resource]:
	wrong_dict = {}
        for field in record:
	    if field[0:3] == "$k_":
		nest_res = "$_" + record[field]['@resource']
		for nested_fields in record[field][nest_res][0]:
		    if '@error' in record[field][nest_res][0][nested_fields]:
		       	wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = "*_error_*" + record[field][nest_res][0][nested_fields.encode('ascii')]['@error'] + ' You entered ' + record[field][nest_res][0][nested_fields.encode('ascii')]['@value']
		    else:
			    try:	    
			        wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = record[field][nest_res][0][nested_fields.encode('ascii')]['@value']
			    except:
				    wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = record[field][nest_res][0][nested_fields.encode('ascii')]
	    else:
	        if '@error' in record[field]:
		    wrong_dict[field] = "*_error_*" + record[field]['@error'] + '. You entered ' + record[field]['@value']
		else:
		    wrong_dict[field]  = record[field]['@value']
	if 'Data Import Error' not in wrong_dict.values():
	    invalid_rows.append(wrong_dict)
    '''f.write("\n\nInvalid rows " + repr(invalid_rows))
    session.import_success = len(invalid_rows) 
    incorrect_rows = []
    correct_rows = []
    returned_tree = tree
    returned_json = s3xrc.xml.tree2json(returned_tree)
    returned_json = returned_json.encode('ascii')
    f.write("THIS IS ASCII returned_json " + returned_json + "\n\n\n")
    returned_json = json.loads(returned_json,encoding = 'ascii')
    f.write("-->\n\n\n<--" + repr(type(returned_json)))
    for resource_name in returned_json:
	for record in returned_json[resource_name]:
	     if '@error' in repr(record):
		incorrect_rows.append(record)
		f.write("\n\n\n" + repr(record))
	     else:
		correct_rows.append(record)
    correct_rows_send = {}
    correct_rows_send[('$_'+prefix+'_'+name)] = correct_rows
    f.write("The correct rows are " + repr(correct_rows_send))<commit_msg>Bug fix to reimport step<commit_after># coding: utf8
# try something like
module = 'importer'
# Current Module (for sidebar title)
module_name = 'Spreadsheet importer' 

import gluon.contrib.simplejson as json

response.menu_options = [
    [
	    T('Spreadsheet'), False, URL(r=request, f='spreadsheet/create')
    ]
]

importer=local_import("importer")

def index(): 
    return dict(module_name=module_name)

@auth.requires_membership('Administrator')
def spreadsheet():
    crud.settings.create_onaccept = lambda form : redirect(URL(r=request, c="importer", f="spreadsheetview")) 
    return shn_rest_controller(module,'slist')

def spreadsheetview():
    k=db(db.importer_slist.id>0).select().last()
    k=k.Path;
    str=importer.pathfind(k)
    str=request.folder+str
    temp=importer.removerowcol(str)
    #appname=request.application
    v=importer.json(str,request.folder)
    #fields=shn_get_writecolumns(
    return dict(ss=v)

def slist():
    return shn_rest_controller(module,'slist',listadd=False)
    
def import_spreadsheet():
    spreadsheet = request.body.read()
    from StringIO import StringIO
    spreadsheet_json = StringIO(spreadsheet)
    spreadsheet_json.seek(0)
    #j = json.loads(spreadsheet)
    exec("j = " + spreadsheet)
    similar_rows = []
    importable_rows = []
    if j.has_key('header_row'):
         j['spreadsheet'].pop(j['header_row'])
         j['rows'] -= 1
    if j.has_key('re_import'):
        for x in range(0,len(j['map'])):
	    j['map'][x][2] = j['map'][x][2].replace('&gt;','>')
    if not j.has_key('re_import'):# and j['re_import'] is not True:
    	for x in range(0,len(j['spreadsheet'])):
	    for y in range(x+1,len(j['spreadsheet'])):
 	        k = importer.jaro_winkler_distance_row(j['spreadsheet'][x],j['spreadsheet'][y])
	        if k is True:
	           similar_rows.append(j['spreadsheet'][x])
	           similar_rows.append(j['spreadsheet'][y])
	        else:
	           pass 
	for k in similar_rows:
	    for l in k:
	       l = l.encode('ascii')
        session.similar_rows = similar_rows
    for k in j['spreadsheet']:
        if k in similar_rows:
	    j['spreadsheet'].remove(k)
	    j['rows'] -= 1
    i=k=0
    send_dict = {}
    resource = j['resource'].encode('ascii')
    send_dict[resource] = []
    while (i < j['rows']):
	res = {}
	k = 0
	while (k < j['columns']):
	    if ' --> ' in j['map'][k][2]:
		field,nested_resource,nested_field = j['map'][k][2].split(' --> ')
		field = "$k_" + field
		nr = nested_resource
		nested_resource= "$_" + nested_resource
		if res.has_key(field) is False: 
		   res[field] = {}
		   res[field]['@resource'] = nr 
		   res[field][nested_resource] = [{}]
		res[field][nested_resource][0][nested_field] = j['spreadsheet'][i][k].encode('ascii')
		k += 1
		continue
	    if "opt_" in j['map'][k][2]:
		res[j['map'][k][2].encode('ascii')]['@value'] = j['spreadsheet'][i][k].encode('ascii')
	    	res[j['map'][k][2].encode('ascii')]["$"] = j['spreadsheet'][i][k].encode('ascii')
	    else:
		res[j['map'][k][2].encode('ascii')] = j['spreadsheet'][i][k].encode('ascii')
		if j['map'][k][2].encode('ascii') == 'comments':
			res[j['map'][k][2].encode('ascii')] = j['map'][k][1] + '-->' + j['spreadsheet'][i][k].encode('ascii')
	    res['@modified_at'] = j['modtime']
	    k += 1
	send_dict[resource].append(res)
	i += 1
	
    word = json.dumps(send_dict[resource])
    #Removing all white spaces and newlines in the JSON
    new_word = ""
    cntr = 1
    for i in range(0,len(word)):
        if word[i] == "\"":
           cntr = cntr + 1
      	   cntr = cntr % 2
        if cntr == 0:
           new_word += word[i]
        if cntr == 1:
           if word[i] == " " or word[i] == "\n":
	      continue
           else:
    	      new_word += word[i] 
    #new_word is without newlines and whitespaces	  
    new_word  = "{\"$_" + resource + "\":"+ new_word + "}"
    #added resource name
    send = StringIO(new_word)
    tree = s3xrc.xml.json2tree(send)
    prefix, name = resource.split('_')
    res = s3xrc.resource(prefix, name)
    res.import_xml(source = tree, ignore_errors = True)
    returned_json = s3xrc.xml.tree2json(tree)
    if '@error' not in repr(returned_json):
	    return dict(module_name = module_name)
    invalid_rows = []
    exec('returned_json = ' + returned_json)
    for record in returned_json['$_'+resource]:
	 del record['@modified_at']
    for record in returned_json['$_'+resource]:
	wrong_dict = {}
        for field in record:
	    if field[0:3] == "$k_":
		nest_res = "$_" + record[field]['@resource']
		for nested_fields in record[field][nest_res][0]:
		    if '@error' in record[field][nest_res][0][nested_fields]:
		       	wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = "*_error_*" + record[field][nest_res][0][nested_fields.encode('ascii')]['@error'] + ' You entered ' + record[field][nest_res][0][nested_fields.encode('ascii')]['@value']
		    else:
			    try:	    
			        wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = record[field][nest_res][0][nested_fields.encode('ascii')]['@value']
			    except:
				    wrong_dict[field + ' --> ' + nest_res + ' --> ' + nested_fields] = record[field][nest_res][0][nested_fields.encode('ascii')]
	    else:
	        if '@error' in record[field]:
		    wrong_dict[field] = "*_error_*" + record[field]['@error'] + '. You entered ' + record[field]['@value']
		else:
		    wrong_dict[field]  = record[field]['@value']
	if 'Data Import Error' not in wrong_dict.values():
	    invalid_rows.append(wrong_dict)
    temp = []
    for k in invalid_rows:
        for l in k.values():
	    if "*_error_*" in l:
	        temp.append(k)
    invalid_rows = temp 
    '''f.write("\n\nInvalid rows " + repr(invalid_rows))
    session.import_success = len(invalid_rows) 
    incorrect_rows = []
    correct_rows = []
    returned_tree = tree
    returned_json = s3xrc.xml.tree2json(returned_tree)
    returned_json = returned_json.encode('ascii')
    f.write("THIS IS ASCII returned_json " + returned_json + "\n\n\n")
    returned_json = json.loads(returned_json,encoding = 'ascii')
    f.write("-->\n\n\n<--" + repr(type(returned_json)))
    for resource_name in returned_json:
	for record in returned_json[resource_name]:
	     if '@error' in repr(record):
		incorrect_rows.append(record)
		f.write("\n\n\n" + repr(record))
	     else:
		correct_rows.append(record)
    correct_rows_send = {}
    correct_rows_send[('$_'+prefix+'_'+name)] = correct_rows
    f.write("The correct rows are " + repr(correct_rows_send))
{'fraction_non_alphanumeric': 0.18012374323279196, 'fraction_numerical': 0.00711523588553751, 'mean_word_length': 3.4118048447628797, 'pattern_counts': {'":': 8, '<': 9, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 8}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4476166', 'n_tokens_mistral': 4809, 'n_tokens_neox': 4528, 'n_words': 817}