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.' ) }