def _constructQueryData(self, fields, form_table_name, REQUEST):
"""Parses form fields and prepares data for saving into the database.
:param fields: form fields
:param form_table_name: name of the table used for the form
:param REQUEST: request object
:returns: A list of tuples that contain two elements:
tuple[0] is the name of the SQL table to save the data in
tuple[1] is either a dict, or a list of dicts (for data grid
fields) with the values needed to execute the query
"""
query_data = [(form_table_name, {})]
query_args = query_data[0][1]
for field in fields:
# ignore fields that we want to exclude
if field.id in self.exclude_fields:
continue
field_id = cleanString(field.id)
# handle file fields
if field.isFileField():
field_name = field.fgField.getName()
file_upload = REQUEST.form.get('{0}_file'.format(field_name))
file_type = file_upload.filename.split('.')[-1]
table_name = form_table_name + '_' + \
cleanString(field_name)
query_data.append((
table_name,
[{
"data": buffer(file_upload.read()),
"type": file_type
}]
))
# handle datagrid fields
elif field.portal_type == "FormDataGridField":
field_name = form_table_name + '_' + field_id
grid_list = []
for row in REQUEST.form.get(field.id, ''):
# ignore form metadata
if row.get('orderindex_', '') != 'template_row_marker':
item = dict(row)
for key, value in item.iteritems():
item[key] = item[key].decode('utf8')
item[cleanString(key)] = \
item.pop(key)
del item['orderindex_']
# check for duplicates
if(item not in grid_list):
grid_list.append(item)
query_data.append((field_name, grid_list))
# handle date fields
# XXX: this is needed for e.g. postgresql, sqlite works fine if we
# insert an empty string
elif field.portal_type == "FormDateField":
date_str = REQUEST.form.get(field.id, '')
if date_str:
query_args[field_id] = date_str
# handle all other fields
else:
query_args[field_id] = REQUEST.form.get(
field.id, '').decode('utf8')
return query_data
def _write_to_zip(self, zipfile, vacancies=None):
"""Write form data to excel file and zip it together with uploaded
files.
File columns contain links to uploaded files. File structure of the
exported excel file and accompanying files will be like this:
form_id.xlsx
files
|
|--id_some_file1.txt
|--id_some_file2.pdf
...
There are a couple of assumptions about the rdb structure. Please see
slc.rdbploneformgenadapter.content.content.py
:param zipfile: ZipFile instance that will hold the data
:param vacancies: a list of job vacancies to fetch the data for. If
no job vacancies are provided, return data for all job vacancies.
"""
form = self.context.getParentNode()
form_table_name = cleanString(form.id)
db_utility_name = self.context.db_utility_name
# get the rdb connection
try:
db = getUtility(IDatabase, db_utility_name)
connection = db.connection.engine.connect()
except (ComponentLookupError, SQLAlchemyError):
logger.exception('Error connecting to database.')
raise
grid_fields = []
file_fields = []
for key in form.keys():
if(form[key].portal_type == 'FormDataGridField'):
grid_fields.append(key)
elif(form[key].portal_type == 'FormFileField'):
file_fields.append(key)
if not vacancies: # fetch data for all job vacancies
form_data = connection.execute(
"SELECT * FROM {0}".format(form_table_name)
)
else: # fetch data for selected job vacancies
form_data = connection.execute(
"SELECT * FROM {0} WHERE {1} IN ({2})".format(
form_table_name,
JOB_VACANCY_ID,
"'" + "','".join(vacancies) + "'",
)
)
# Extending form_keys with grid and file fields, so we can
# write them to excel easier inside the loop
form_keys = form_data.keys()
all_form_keys = form_keys + grid_fields + file_fields
# Create an excel workbook
book = Workbook()
sheet = book.worksheets[0]
sheet.default_column_dimension.auto_size = True
sheet.title = form_table_name
date_time = datetime.now().timetuple()[:6]
# write column names
for column_count, key in enumerate(all_form_keys):
sheet.cell(
row=0, column=column_count).value = all_form_keys[column_count]
# write data
for row_count, table_row in enumerate(form_data.fetchall()):
# write basic form fields
for form_column_count, key in enumerate(form_keys):
cell = sheet.cell(row=row_count + 1, column=form_column_count)
cell.value = table_row[key]
count = 1
# write grid form fields
for field in grid_fields:
grid_table_name = form_table_name + "_" + cleanString(field)
grid_field_data = connection.execute(
'SELECT * FROM {0} WHERE {1}_id={2}'.format(
grid_table_name, form_table_name, table_row[0])
)
# hack to get all grid rows in same cell
grid_field_string = ""
keys = grid_field_data.keys()
keys.remove(form_table_name + '_id')
grid_field_data_all = grid_field_data.fetchall()
for row in grid_field_data_all:
for key in keys:
grid_field_string += "{0}: {1}, ".format(key, row[key])
grid_field_string = grid_field_string[:-2] + '\n'
if grid_field_string:
cell = sheet.cell(
row=row_count + 1, column=form_column_count + count
)
cell.style.alignment.wrap_text = True
cell.value = grid_field_string[:-1]
# set width on longest row
sheet.column_dimensions[cell.column].width = max(
[len(grid_row) for
grid_row in grid_field_string.split("\n")]
)
count += 1
# write file form fields
for file_count, field in enumerate(file_fields):
file_table_name = form_table_name + "_" + cleanString(field)
file_field_data = connection.execute(
'SELECT * FROM {0} WHERE {1}_id={2}'.format(
file_table_name, form_table_name, table_row[0]))
filename = str(table_row[0]) + "_" + str(file_count)
for row in file_field_data.fetchall():
file_path = 'files/{0}.{1}'.format(filename, row["type"])
zip_info = ZipInfo(file_path, date_time)
# setting permissions manually to make it work on Linux
zip_info.external_attr = 0666 << 16L
zipfile.writestr(zip_info, row["data"])
cell = sheet.cell(
row=row_count + 1,
column=form_column_count + count
)
cell.value = "View file"
cell.hyperlink = file_path
count += 1
output_tmp = StringIO()
book.save(output_tmp)
zip_info = ZipInfo('{0}.xlsx'.format(form_table_name), date_time)
# setting permissions manually to make it work on Linux
zip_info.external_attr = 0666 << 16L
zipfile.writestr(zip_info, output_tmp.getvalue())
def onSuccess(self, fields, REQUEST=None):
"""Save the data from the form into a relational database.
It works in the following way: if a query is defined on the adapter,
it will use that query to insert the data into the database. If no
query is defined, it will try to automatically save all form fields.
"Normal" fields will be saved into the primary table, while
FormDataGridField and FormFileField fields will be saved into
separate tables.
For this to work, your database and tables have to be created and set
up according to the following rules:
- Name of the primary table for storing the form data is same as
the id of the form (with '-' changed to '_' and '.' to '')
- All "auxiliary" tables for FormDataGridField and FormFileField
fields are named in the convention of
form_table_name + _ + field.id. Also, each auxiliary table must
have a form_table_name_id column, which is a foreign key.
- All table colums must be named after form field id's (with '-'
changed to '_' and '.' to '')
:param fields: form fields
:param REQUEST: request object
"""
try:
db = getUtility(IDatabase, self.db_utility_name)
except ComponentLookupError:
logger.exception('Can not write to database, wrong configuration')
return {
FORM_ERROR_MARKER: _(
'Can not write to database, wrong configuration. Please '
'contact site owner.'
)
}
form_table_name = cleanString(self.getParentNode().id)
query_args = self._constructQueryData(
fields, form_table_name, REQUEST)
query = self.query
try:
connection = db.connection.engine.connect()
except:
logger.exception('Error connecting to database.')
return {
FORM_ERROR_MARKER: _(
'Can not write to database, wrong configuration. Please '
'contact site owner.'
)
}
try:
# begin transaction for all executions
trans = connection.begin()
if(query):
# custom query was defined on the adapter, let's use that
connection.execute(query, **query_args[0][1])
else:
# no query was defined, store all form fields
select_string = "SELECT MAX(ID) FROM {0}".format(
form_table_name)
form_id = (connection.execute(
select_string).scalar() or 0) + 1
if db.engine.url.drivername == 'postgresql':
paramstyle = 'pyformat'
else:
paramstyle = 'named'
for field in query_args:
table_name = field[0]
# insert FormDataGridField fields into separate tables
if(type(field[1]) == list):
for item in field[1][:]:
# Setting grid table name, based on the form name
item[form_table_name + "_id"] = form_id
params = item.keys()
query_string = \
"INSERT INTO {0} {1} VALUES {2}".format(
table_name,
self._join_params(params),
self._join_params(
params,
paramstyle=paramstyle
)
)
connection.execute(query_string, **item)
# insert "normal" fields into the primary table
else:
params = field[1].keys()
query_string = \
"INSERT INTO {0} {1} VALUES {2}".format(
table_name,
self._join_params(params),
self._join_params(
params,
paramstyle=paramstyle
)
)
connection.execute(query_string, **field[1])
# commit the transaction, saving the changes
trans.commit()
except:
trans.rollback()
logger.exception('Error writing to database.')
return {
FORM_ERROR_MARKER: _(
'Can not write to database, wrong configuration. Please '
'contact site owner.'
)
}
