def insert_sheet_old_template(self, ijson):
if ijson.get('nw', ''):
return self.insert_new_template(ijson)
template_id = ijson['template_id']
user = ijson['user']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheet_id = sql_obj.max_sheet_id(template_id)
rows = []
data = ijson['data']
sheets = []
for sheet_info in data['sheets']:
sheet = self.clean_name(sheet_info['name'])
extra_info = copy.deepcopy(sheet_info)
del extra_info['rows']
sheets.append((template_id, '', sheet_id, sheet, user,
json.dumps(extra_info)))
for row in sheet_info['rows']:
for colinfo in row['cells']:
col = colinfo['index']
fromular_str = colinfo.get(
'formula', '') #self.get_formula_str(col, colinfo)
cell_alph = self.find_alp(col)
level_id = colinfo.get('level', '')
rows.append((template_id, sheet_id, row['index'], col,
str(colinfo), 'taxonomy', str(fromular_str),
cell_alph, level_id))
sheet_id = sheet_id + 1
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sql_obj = sqlite_api.sqlite_api(db_path)
sql_obj.insert_sheets(sheets)
sql_obj.insert_sheet_data(rows)
return [{'message': 'done'}]
def read_cell_wise_data(self, ijson, doc_id, map_info):
import sqlite_api as sq1
'''db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db"%(ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_info_full()
for dd in data:
table_id1, table_id2, r_c1, r_c2,relationship_Type = dd
map_info.setdefault(table_id1, {}).setdefault(r_c1, {})[relationship_Type] =1
row, col = r_c1.split('_')
map_info.setdefault(table_id1, {}).setdefault(row, {})[relationship_Type] = 1
map_info.setdefault(table_id1, {}).setdefault('%s_eq'%(r_c1), [])[table_id2] = 1'''
if ijson.get('pre_equality', '') == 'Y':
db_path = "/mnt/eMB_db/company_management/%s/equality/%s_numeq.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_num_info_full()
for dd in data:
gp1, row1, col1, gp2, row2, col2 = dd
rc1 = '%s_%s' % (row1, col1)
cgg = '_'.join(gp1.split('#')[:3])
print cgg, rc1
relationship_Type = 'DIST'
map_info.setdefault(cgg,
{}).setdefault(rc1,
{})[relationship_Type] = 1
map_info.setdefault(cgg,
{}).setdefault(str(row1),
{})[relationship_Type] = 1
else:
db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_info_full()
for dd in data:
table_id1, table_id2, r_c1, r_c2, relationship_Type = dd
table_id1 = '_'.join(table_id1.split('#')[:3])
table_id2 = '_'.join(table_id2.split('#')[:3])
r_c1 = str(r_c1)
map_info.setdefault(table_id1,
{}).setdefault(r_c1,
{})[relationship_Type] = 1
row, col = r_c1.split('_')
map_info.setdefault(table_id1,
{}).setdefault(row,
{})[relationship_Type] = 1
map_info.setdefault(table_id1,
{}).setdefault('%s_eq' % (r_c1),
{})[table_id2] = 1
return ''
def read_only_taxo(self, ijson):
project_id = ijson['project']
template_id = ijson['template_id']
db_path = config.Config.taxonomy_path.format(project_id,
template_id) + "info.db"
import sqlite_api
sql_obj = sqlite_api.sqlite_api(db_path)
res = sql_obj.get_taxo_and_level()
grid_data = {
'columns': [{
"width": 159,
"type": "text",
"title": "A"
}],
'data': [],
'map': {},
"style": {},
'nested_headers': {}
}
for row, dd in enumerate(res):
taxo, level = dd
grid_data['map'].setdefault('rowGroup', {})
grid_data['map']['rowGroup'][row] = level
grid_data['data'].append([taxo])
return [{'message': 'done', 'data': grid_data}]
def sheet_gridinfo(self, ijson):
template_id = ijson['template_id']
sheet_id = ijson['sheet_id']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheet_info = sql_obj.read_sheet_data(template_id, sheet_id)
extra_info = self.get_extra_info(template_id, sheet_id, sql_obj)
view_types = ijson['wf']
res = {"message": "done"}
for view_type in view_types:
if view_type['mod'] == 'grid':
res['grid_data'] = self.convert_grid_format_sheetinfo(
sheet_info)
if view_type['mod'] == 'table':
res['table_data'] = self.convert_table_format_sheetinfo(
sheet_info)
if view_type['mod'] == 'spreadsheet':
res['spreadsheet_data'] = self.convert_spreadsheet_format_sheetinfo(
sheet_info)
if extra_info.get('merge_cells', {}):
res['spreadsheet_data'][
'merge_cells'] = self.convert_merge_cells(
extra_info['merge_cells'])
if view_type['mod'] == 'spreadsheet_new':
res['spreadsheet_data'] = self.convert_spreadsheet_format_sheetinfo_new(
sheet_info)
if extra_info.get('merge_cells', {}):
res['spreadsheet_data']['merges'] = extra_info[
'merge_cells'].keys()
return res
def sheet_gridinfo_all(self, ijson):
template_id = ijson['template_id']
#sheet_id = ijson['sheet_id']
sheets = ijson.get('sheets')
industry = ijson.get('industry', '')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
if not sheets:
sheets = map(lambda x: x[0],
self.read_sheets(template_id, industry))
res = {"message": "done"}
view_types = ijson['wf']
for sheet_info in sheets:
sheet_id = sheet_info
sheet_info = sql_obj.read_sheet_data(template_id, sheet_id)
extra_info = self.get_extra_info(template_id, sheet_id, sql_obj)
for view_type in view_types:
if view_type['mod'] == 'spreadsheet':
res[sheet_id] = {}
res[sheet_id][
'spreadsheet_data'] = self.convert_spreadsheet_format_sheetinfo(
sheet_info)
if extra_info.get('merge_cells', {}):
res['sheet_id']['spreadsheet_data'][
'merge_cells'] = self.convert_merge_cells(
extra_info['merge_cells'])
return res
def create_excel(self, ijson):
template_id = ijson['template_id']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheets = sql_obj.read_sheets(template_id)
res = {}
property_dic = {}
order_sheets = []
for sheet in sheets:
idd, sheet_name = sheet
sheet_info = sql_obj.read_sheet_data(template_id, idd)
sheet_name, extra_info = self.get_extra_info(
template_id, idd, sql_obj)
order_sheets.append(idd)
res.setdefault(idd, {})['name'] = sheet_name
number_rows = 0
number_cols = 0
sheet_info = sql_obj.read_sheet_data(template_id, idd)
for rowinfo in sheet_info:
row, col, value, taxonomy, fromular_str, cell_alph = rowinfo
if row > number_rows:
number_rows = row
if col > number_cols:
number_cols = col
temp = {}
org_val = eval(value)
if org_val.get('formula', {}):
temp['excel_formula'] = org_val['formula']['src']
if isinstance(org_val['value'], dict):
temp['value'] = org_val['value']['code']
else:
temp['value'] = org_val['value']
else:
temp['value'] = org_val.get('value', '')
res[idd].setdefault('cell_dict',
{})["%s_%s" % (row, col)] = temp
#res[idd].setdefault('cell_dict', {})[(row, col)] = temp
for kk, vv in org_val.items():
property_dic[kk] = vv
res[idd]['nrows'] = number_rows + 1
res[idd]['ncols'] = number_cols + 1
res[idd]['merge_range'] = self.get_merged_range(
extra_info['mergedCells'])
import create_excel as ce
ce_obj = ce.xlsx_operation()
order_sheets.sort()
import datetime
cp = datetime.datetime.now().strftime("%m%d%Y%H%M%S")
path = "/var/www/html/sudhakar/%s.xlsx" % (cp)
ce_obj.writeExcel(path, res, order_sheets)
return [{
'message': 'done',
'path': path,
"data": res,
'pro': property_dic
}]
def read_forulas_info(self, ijson):
doc_id = ijson['doc_id']
import sqlite_api as sq11
db_path = "/mnt/eMB_db/company_management/%s/doc_builder/%s.db" % (
ijson['company_id'], doc_id)
sq11_obj = sq11.sqlite_api(db_path)
data = sq11_obj.read_fids()
return map(lambda x: x[0], data)
def read_sheets(self, ijson):
template_id = ijson['template_id']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheets = sql_obj.read_sheets(template_id)
res = {"message": "done"}
res['drop_down_data'] = self.convert_dp_format_sheets(sheets)
return [res]
def read_doc_wise_data(self, ijson, doc_id, doc_str, flag_info):
import sqlite_api as sq1
if doc_id not in flag_info:
if ijson.get('pre_equality', '') == 'Y':
db_path = "/mnt/eMB_db/company_management/%s/equality/%s_numeq.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_num_info(doc_str)
for dd in data:
cgg = '_'.join(dd[0].split('#')[:3])
flag_info.setdefault(doc_id, {}).setdefault('eq_num',
{})[cgg] = 1
db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
else:
db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_info(doc_str)
print 'cdata', data
for dd in data:
cgg = '_'.join(dd[0].split('#')[:3])
flag_info.setdefault(doc_id, {}).setdefault('eq',
{})[cgg] = 1
rr = sq1_obj.read_raw_info()
for cc in rr:
dd = '_'.join(cc[0].split('#')[:3])
flag_info.setdefault(doc_id, {}).setdefault('raw_db',
{})[dd] = 1
print 'flag', doc_id, flag_info
if ijson.get('pre_equality', '') == 'Y':
data = flag_info[doc_id].get('eq_num', {}).get(doc_str, '')
else:
data = flag_info[doc_id].get('eq', {}).get(doc_str, '')
rr = flag_info[doc_id].get('raw_db', {}).get(doc_str, '')
if data and rr:
return 'G'
elif data:
return 'O'
elif rr:
return 'P'
else:
return 'R'
def insert_new_template(self, ijson):
path = ijson['path']
industry, project, user, ttype = ijson.get('industry', ''), ijson.get(
'project', ''), ijson.get('user', ''), ijson.get('type', '')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
#data = ijson['data'] #self.get_sheets_new(ijson)
data = self.get_sheets_new(ijson)
template_id = sql_obj.max_template_id()
if ijson.get('template_name', ''):
template_name = ijson['template_name']
else:
template_name = self.clean_name(path.split('/')[-1].split('.')[0])
temp_extra_info = {}
temp_extra_info['activesheet'] = data.get('activesheet')
sql_obj.insert_template(template_id, template_name, industry, project,
ttype, user, json.dumps(temp_extra_info))
sheets = []
rows = []
sheet_id = 1
for sheet_info in data['sheets']:
sheet = self.clean_name(sheet_info['name'])
extra_info = copy.deepcopy(sheet_info)
del extra_info['rows']
if sheet_info['name'] == data['activeSheet']:
extra_info['activesheet'] = 1
sheets.append((template_id, template_name, sheet_id, sheet, user,
json.dumps(extra_info)))
for row in sheet_info['rows']:
for colinfo in row['cells']:
col = colinfo['index']
fromular_str = colinfo.get(
'formula', '') #self.get_formula_str(col, colinfo)
cell_alph = self.find_alp(col)
level_id = colinfo.get('level', '')
rows.append((template_id, sheet_id, row['index'], col,
str(colinfo), 'taxonomy', str(fromular_str),
cell_alph, level_id))
sheet_id = sheet_id + 1
sql_obj = sqlite_api.sqlite_api(db_path)
sql_obj.insert_sheets(sheets)
sql_obj.insert_sheet_data(rows)
return [{'message': 'done'}]
def read_row_info(self, ijson):
company_id = ijson['company_id']
project_id = ijson['project_id']
rid = ijson['rid']
db_path = config.Config.databuilder_path.format(company_id, project_id)
import sqlite_api as sq1
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_db_builder_v2(rid)
ref_info = sq1_obj.read_reference_table_rid_v1(rid)
return [data, ref_info]
def check_avail_name(self, ijson):
project = ijson.get('project', '')
new_name = ijson['name']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
templates = sql_obj.get_avail_name(project, new_name)
if templates:
return [{'message': 'done', 'avail': 'N'}]
else:
return [{'message': 'done', 'avail': 'Y'}]
def read_page_cords(self, ijson):
import sqlite_api as sq1
doc_id = ijson['d']
db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_page_cords()
res = {}
for dd in data:
res[dd[0]] = json.loads(dd[1])
return res
def get_eq_rows(self, ijson):
rc = ijson['rc']
#doc_id = ijson['d']
#page = ijson['p']
#grid = ijson['g']
tb_str = ijson.get('table_id', '')
doc_id, page, grid = tb_str.split('#')
tb1 = tb_str
#tb1 = '%s_%s_%s'%(doc_id, page, grid)
import sqlite_api as sq1
rc_info = {}
if ijson.get('pre_equality', '') == 'Y':
r, c = rc.split('_')
db_path = "/mnt/eMB_db/company_management/%s/equality/%s_numeq.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.cell_wise_eq(tb1, r, c)
fs = []
done_g = {}
for dd in data:
kt = '#'.join(dd[0].split('#')[0:3]) #need to see
print kt
rc2 = '%s_%s' % (dd[1], dd[2])
rc_info.setdefault(kt, {})[rc2] = 1
if kt in done_g: continue
done_g[kt] = 1
fs.append({'k': kt, 'n': kt})
else:
db_path = "/mnt/eMB_db/company_management/%s/equality/%s.db" % (
ijson['company_id'], doc_id)
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_eq_info_rc(tb1, rc)
fs = []
done_g = {}
for dd in data:
rc2 = dd[1]
rc_info.setdefault(dd[0], {})[rc2] = 1
if dd[0] in done_g: continue
done_g[dd[0]] = 1
fs.append({'k': dd[0], 'n': dd[0]})
return [{'message': 'done', 'data': fs, 'rc_info': rc_info}]
def get_page_cords(self, doc_info, company_id, page_dic):
doc, page, grid = doc_info
if page_dic.get(doc, ''):
return
db_path = '/mnt/eMB_db/company_management/{0}/equality/{1}.db'.format(
company_id, doc)
import sqlite_api as sqlapi
sqlapi_obj = sqlapi.sqlite_api(db_path)
page_info = sqlapi_obj.read_page_cords()
sqlapi_obj.cur.close()
for dd in page_info:
page_dic.setdefault(doc, {})[str(dd[0])] = eval(dd[1])
def read_meta_info(self, company_id):
cid_path = config.Config.comp_path.format(company_id)
db_path = os.path.join(cid_path, 'document_info.db')
import sqlite_api as sq_c1
sqc_obj = sq_c1.sqlite_api(db_path)
data = sqc_obj.read_meta_info()
map_info = {}
for dd in data:
period_type, period , mt = dd
mt = eval(mt)
map_info['%s%s'%(period_type, period)] = mt['Document To']
return map_info
def read_common_table_types(self, ijson):
import sqlite_api as sq1
import config
pid = ijson['project_id']
db_path = config.Config.gl_db
print db_path
sq1_obj = sq1.sqlite_api(db_path)
data = sq1_obj.read_all_tb()
tb_types = {}
for dd in data:
tb_types[str(dd[0])] = dd[1]
return tb_types
def read_templates(self, ijson):
project = ijson['project']
ttype = ijson.get('type', '')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
if ttype:
templates = sql_obj.read_templates_type(project, ttype)
else:
templates = sql_obj.read_templates(project)
res = {"message": "done"}
res['data'] = self.convert_dp_format_templates(templates)
return [res]
def update_cell_property(self, ijson):
template_id = ijson['template_id']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
for sheet_id, sinfo in ijson['data'].items():
for rc, rc_val in sinfo.items():
r, c = map(lambda x: int(x), rc.split('_'))
old_rc_info = sql_obj.get_row_id_info(template_id, sheet_id, r,
c)
old_rc_info.update(rc_val)
sql_obj.update_cell_value(json.dumps(old_rc_info), template_id,
sheet_id, r, c)
return [{'messge': 'done'}]
def cl_create(self, ijson):
path = config.Config.mapping_path.format(ijson['company_id'],
ijson['project_id'],
ijson['template_id'])
import sqlite_api as sq
sq_obj = sq.sqlite_api(path)
template_id = ijson['template_id']
import sqlite_api as sq
sheets = sq_obj.read_sheets(template_id)
error_data, Error_message = self.full_sheet_validation(
sheets, template_id, sq_obj, ijson)
if error_data:
return {'message': Error_message, 'data': error_data}
else:
return {'message': 'done', 'data': []}
def read_grouped_primary_keys(self, ijson):
company_id = ijson['company_id']
project_id = ijson['project_id']
db_path = config.Config.databuilder_path.format(company_id, project_id)
import sqlite_api as sq1
sq1_obj = sq1.sqlite_api(db_path)
res = sq1_obj.read_grouped_primary_keys()
grp_map_d = {}
for r in res:
primary_key, other_primary_key = r #map(lambda x: ''.join(x.lower().split()), r)
grp_map_d.setdefault(primary_key, {})[other_primary_key] = 1
if other_primary_key != primary_key:
grp_map_d[('REV', other_primary_key)] = primary_key
return grp_map_d
def sheet_gridinfo(self, ijson):
template_id = ijson['template_id']
sheet_id = ijson['sheet_id']
ttype = ijson.get('type', 'fixed')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheet_info = sql_obj.read_sheet_data(template_id, sheet_id)
sheet_name, extra_info = self.get_extra_info(template_id, sheet_id,
sql_obj)
res = {"message": "done"}
res['data'] = self.convert_spreadsheet_format_sheetinfo(
sheet_info, extra_info, ttype, sheet_name)
if extra_info.get('mergedCells', []):
res['data']['mergeCells'] = self.convert_merge_cells(
extra_info['mergedCells'])
return [res]
def read_sheets(self, ijson):
industry = ijson['industry']
template_id = ijson['template_id']
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheets = sql_obj.read_sheets(template_id, industry)
view_types = ijson['wf']
res = {"message": "done"}
for view_type in view_types:
if view_type == 'drop_down':
res['drop_down_data'] = self.convert_dp_format_sheets(sheets)
if view_type == 'grid':
res['grid_data'] = self.convert_grid_format_sheets(sheets)
if view_type['mod'] == 'tabs':
res['tabs_data'] = self.convert_dp_format_sheets(sheets)
return res
def read_grids(self, cmp_id, doc_ids=[]):
doc_d = {}
for d in doc_ids:
doc_d[str(d)] = 1
doc_wise_grids = {}
import sqlite_api as sql_api
db_path = '/mnt/eMB_db/company_management/{0}/{1}.db'.format(
cmp_id, 'table_info')
print db_path
sql_obj = sql_api.sqlite_api(db_path)
dd = sql_obj.get_grids()
for each in dd:
if doc_d and str(each[0]) not in doc_d: continue
#if str(each[1]) != '3':continue
doc_wise_grids.setdefault(each[0],
{})['%s_%s' % (each[1], each[2])] = 1
return doc_wise_grids
def get_grid_info(self, ijson):
import sqlite_api as sq
db = "/mnt/eMB_db/company_management/%s/table_info.db" % (
ijson['company_id'])
sq_obj = sq.sqlite_api(db)
db_info = sq_obj.get_grids_tb(ijson['doc_id'])
flag_info = {}
map_d, data, cols = {}, [], []
for cid, dd in enumerate(db_info, 1):
import pra_redis_exc as pr
pr_obj = pr.exe(ijson['company_id'], dd[0])
header = pr_obj.make_exec(dd[1], dd[2], 'gh')
cgrid = '%s_%s_%s' % dd
temp = {
'g': {
'v': cgrid
},
'gh': {
'v': header
},
'flg': {},
'cid': cid,
'rid': cid,
'sno': {
'v': cid
}
}
flgt = self.read_doc_wise_data(ijson, dd[0], cgrid, flag_info)
temp['flg'] = {'v': flgt}
map_key = "%s_%s" % (cid, '')
data.append(temp)
ff = [('sno', 'SNo'), ('g', 'Grid'), ('gh', 'Gheader'),
('flg', 'Flag')]
for hh in ff:
vtemp = {'k': hh[0], 'n': hh[1]}
if hh[0] == 'flg':
vtemp['v_opt'] = 2
cols.append(vtemp)
return [{
'message': 'done',
'coldef': cols,
'data': data,
'map': map_d
}]
def read_group_ids(self, ijson):
import sqlite_api as sq1
doc_id = ijson['doc_id']
db_path = "/mnt/eMB_db/company_management/%s/doc_builder/%s.db" % (
ijson['company_id'], doc_id)
if ijson.get('tab', '') == 'Builder':
db_path = config.Config.databuilder_path_test.format(
ijson['company_id'], ijson['project_id'])
sq1_obj = sq1.sqlite_api(db_path)
cdata = sq1_obj.read_db_builder()
done_g = {}
data = []
cols = []
map_d = {}
cid = 1
for dd in cdata:
if dd[0] != ijson['table_type']: continue
if dd[1] in done_g: continue
done_g[dd[1]] = 1
data.append({'k': dd[1], 'n': dd[1]})
return [{'message': 'done', 'data': data}]
def insert_model(self, ijson):
path = ijson['path']['filename']
industry, project, user = ijson['industry'], ijson.get('project',
''), ijson.get(
'user', '')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
sheets_info, order_of_sheet, extra_info = self.read_excel(path)
template_id = sql_obj.max_template_id()
if ijson.get('template_name', ''):
template_name = ijson['template_name']
else:
template_name = self.clean_name(path.split('/')[-1].split('.')[0])
sheets = []
rows = []
sheet_id = 1
for sheet in order_of_sheet:
sheet_info = sheets_info[sheet]
sheet = self.clean_name(sheet)
sheets.append((template_id, template_name, sheet_id, sheet, user,
json.dumps(extra_info.get(sheet, {}))))
for row in sheet_info:
for col, colinfo in row['cols'].items():
fromular_str = colinfo.get(
'fm', '') #self.get_formula_str(col, colinfo)
cell_alph = self.find_alp(col)
rows.append((template_id, sheet_id, row['row'], col,
self.clean_name(colinfo['v']), 'taxonomy',
fromular_str, cell_alph))
sheet_id = sheet_id + 1
sql_obj.insert_template(template_id, template_name, industry, project,
user)
sql_obj.insert_sheets(sheets)
sql_obj.insert_sheet_data(rows)
return [{'message': 'done'}]
def cl_create(self, ijson):
path = config.Config.mapping_path.format(ijson['company_id'], ijson['project_id'], ijson['template_id'])
import sqlite_api as sq
sq_obj = sq.sqlite_api(path)
template_id = ijson['template_id']
import sqlite_api as sq
sheets = sq_obj.read_sheets(template_id)
delim_map_d = {
'tab':'\t',
'comma':',',
}
cmp_meta_info = self.read_meta_info(ijson['company_id'])
#print 'cmp', cmp_meta_info
import os
fpath = config.Config.output.format(ijson['company_id'], ijson['project_id'], ijson['template_id'])
cmd = "mkdir -p '%s'"%(fpath)
os.system(cmd)
map_sheet_info = []
cc = "/mnt/eMB_db/company_management/project_configuration/%s/config.db"%(ijson['project_id'])
import sqlite_api as sq1
sq1_obj = sq1.sqlite_api(cc)
export_config, file_name_config, zip_file_name_config = sq1_obj.read_output_config(ijson['template_id'])
export_config = eval(export_config)
file_name_config = eval(file_name_config)
zip_file_name_config = eval(zip_file_name_config)
old_export_ctype = export_config.get('ctype', '')
if 'col_config' in export_config and export_config['col_config']:
export_config['ctype'] = 'column-group'
file_name_config['cmp'] = ijson['company_name']
ctype = export_config.get('ctype', '').lower()
print ctype
header_name_sheets = {}
unqick_header_name = {}
if ctype == 'all':
temp = {}
fname = ''
for sheet in sheets:
temp.setdefault('s', []).append((template_id, sheet[0], sheet[1]))
fname = fname +'_'+sheet[1]
name = self.fname_formatting(file_name_config, ijson['template_name'])
ftype = export_config.get('ftype', '')
temp['ftype'] = ftype
temp['org_sh'] = sheet[1]
temp['name'] = fpath + name
temp['sep'] = delim_map_d.get(export_config.get('delim', '').lower(), '')
temp['fpath'] = fpath
map_sheet_info.append(temp)
elif ctype == 'group':
sheet_map = {}
for sheet in sheets:
sheet_map[sheet[0]] = sheet[1]
fname = ''
for ginfo in ijson['ginfo']:
temp = {}
fname = ''
for sid in ginfo['sids']:
sname = sheet_map[sid]
temp.setdefault('s', []).append((template_id, sid, sname))
fname = fname + sname
ftype = export_config.get('ftype', '')
temp['ftype'] = ftype
name = self.fname_formatting(file_name_config, ijson['template_name'])
temp['name'] = fpath + name
temp['org_sh'] = sheet[1]
temp['sep'] = export_config.get('delim', '')
temp['fpath'] = fpath
#print temp
map_sheet_info.append(temp)
elif ctype == 'individual':
for sheet in sheets:
temp = {}
fname = sheet[1]
name = self.fname_formatting(file_name_config, fname)
temp.setdefault('s', []).append((template_id, sheet[0], sheet[1]))
ftype = export_config.get('ftype', '')
temp['ftype'] = ftype
temp['fpath'] = fpath
name = self.fname_formatting(file_name_config, fname)
temp['name'] =fpath + name
temp['org_sh'] = sheet[1]
temp['sep'] = delim_map_d.get(export_config.get('delim', '').lower(), '')
map_sheet_info.append(temp)
elif ctype == 'column-group':
fff = 1
for sheet in sheets:
#sid = sh['sid']
#colums = sh['colums']
#name = sh['name']
#if sheet[1] != 'transactions':continue
if ijson.get('ss','') and sheet[1] != ijson.get('ss',''):continue
sep_info = self.get_group_info(sq_obj, template_id, sheet[0], export_config['col_config'], sheet[1], header_name_sheets, unqick_header_name)
#print header_name_sheets
for vff in sep_info:
temp = {}
sid, name, colums, ph_flg, ph_rows = vff
#print '9090', [sid, name, colums]
vtuple = (template_id, sid, name, fff)
fff = fff + 1
temp.setdefault('s', []).append(vtuple)
temp[vtuple] = {'cols': colums, 'name': name, 'flg': ph_flg, 'ph_rows': ph_rows, 'org_sh': sheet[1]}
ftype = export_config.get('ftype', '')
temp['ftype'] = ftype
temp['org_sh'] = sheet[1]
temp['fpath'] = fpath
temp['ct'] = 'Y'
if ph_flg == 'Y':
if cmp_meta_info.get(name, ''):
#print 'with datetime', cmp_meta_info[name], name
ph_date = datetime.datetime.strptime(cmp_meta_info[name], '%m/%d/%Y')
file_name_config['ph_date'] = ph_date
name = self.fname_formatting(file_name_config, sheet[1])
#print 'nameee', name
else:
name = sheet[1]+"_"+cmp_meta_info.get(name, name)
#name = self.fname_formatting(file_name_config, sheet[1]+"_"+name)
temp['ph_name'] = name
else:
name = self.fname_formatting(file_name_config, sheet[1])
temp['name'] =fpath + name
temp['sep'] = delim_map_d.get(export_config.get('delim', '').lower(), '')
#if old_export_ctype != 'all':
map_sheet_info.append(copy.deepcopy(temp))
#print 'mmmmmmm',temp
#temp = {}
#if old_export_ctype == 'all':
# map_sheet_info.append(temp)
#print '---------',map_sheet_info
ex, files, return_info = self.start_creation(map_sheet_info, sq_obj, header_name_sheets, unqick_header_name)
if ex == 'N':
return [{'message': return_info}]
zzip_name = self.zipname_formatting(zip_file_name_config, ijson['company_name'])
des = ijson.get('zip_name', fpath+zzip_name+'.zip')
self.create_zip(files, des)
download_path = config.Config.download_path.format(ijson['company_id'], ijson['project_id'], ijson['template_id'], zzip_name)
return [{'message':'done', 'path': download_path, 'des':des}]
def read_data(self, path, template_id, sheet_id):
path = ijson['path']
import sqlite_api as sq
sq_obj = sq.sqlite_api(path)
info = sq_obj.read_sheet_data(template_id, sheet_id)
return info
def insert_model(self, ijson):
path = ijson['path']
industry, project, user, ttype = ijson.get('industry', ''), ijson.get(
'project', ''), ijson.get('user', ''), ijson.get('type', '')
import sqlite_api
db_path = config_obj.Template_storage
sql_obj = sqlite_api.sqlite_api(db_path)
if ijson.get('template_name', ''):
template_name = ijson['template_name']
else:
template_name = self.clean_name(path.split('/')[-1].split('.')[0])
only_temp = 'N'
if not ijson.get('path', ''):
data = {}
else:
only_temp = 'Y'
data = self.get_sheets_new(ijson)
temp_extra_info = {}
temp_extra_info['activesheet'] = data.get('activesheet')
if ijson.get('template_id', ''):
template_id = int(ijson['template_id'])
sql_obj.deleted_temp_info(template_id)
else:
template_id = sql_obj.max_template_id()
sql_obj.insert_template(template_id, template_name, industry,
project, ttype, user,
json.dumps(temp_extra_info))
if only_temp == 'N':
return [{'message': 'done'}]
sheets = []
rows = []
sheet_id = 1
taxo_creation = 'N'
for sheet_info in data['sheets']:
print 'sheete', sheet_info['name']
sheet = self.clean_name(sheet_info['name'])
if sheet == config_obj.taxonomy_sheet:
taxo_creation = 'Y'
extra_info = copy.deepcopy(sheet_info)
del extra_info['rows']
if sheet_info['name'] == data['activeSheet']:
extra_info['activesheet'] = 1
sheets.append((template_id, template_name, sheet_id, sheet, user,
json.dumps(extra_info)))
for row in sheet_info['rows']:
for colinfo in row['cells']:
col = colinfo['index']
fromular_str = colinfo.get(
'formula', '') #self.get_formula_str(col, colinfo)
cell_alph = self.find_alp(col)
level_id = colinfo.get('level', '')
rows.append((template_id, sheet_id, row['index'], col,
str(colinfo), 'taxonomy', str(fromular_str),
cell_alph, level_id))
sheet_id = sheet_id + 1
sql_obj = sqlite_api.sqlite_api(db_path)
sql_obj.insert_sheets(sheets)
sql_obj.insert_sheet_data(rows)
if taxo_creation == 'Y' and ttype == 'fixed':
import pyapi
py_obj = pyapi.PYAPI()
print 'insertion code', template_id, project
py_obj.insert_taxo_to_db(project, template_id)
return [{'message': 'done'}]
