def save_condition_form(request, form, template_name, action, condition, is_filter): """ Function to process the AJAX form to create and update conditions and filters. :param request: HTTP request :param form: Form being used to ask for the fields :param template_name: Template being used to render the form :param action: The action to which the condition is attached to :param condition: Condition object being manipulated or None if creating :param is_filter: The condition is a filter :return: """ # Ajax response data = dict() # In principle we re-render until proven otherwise data['form_is_valid'] = False # The condition is new if no value is given is_new = condition is None if is_new: condition_id = -1 else: condition_id = condition.id # Context for rendering context = {'form': form, 'action_id': action.id, 'condition_id': condition_id, 'add': is_new} # If the method is GET or the form is not valid, re-render the page. if request.method == 'GET' or not form.is_valid(): data['html_form'] = render_to_string(template_name, context, request=request) return JsonResponse(data) # If the request has the 'action_content' field, update the action action_content = request.POST.get('action_content', None) if action_content: action.set_content(action_content) action.save() if is_filter: # Process the filter form # If this is a create filter operation, but the action has one, # flag the error if is_new and Condition.objects.filter(action=action, is_filter=True).exists(): # Should not happen. Go back to editing the action data['form_is_valid'] = True data['html_redirect'] = '' return JsonResponse(data) log_type = 'filter' else: # Verify that the condition name does not exist yet (Uniqueness FIX) qs = Condition.objects.filter( name=form.cleaned_data['name'], action=action, is_filter=False) if (is_new and qs.exists()) or \ (not is_new and qs.filter(~Q(id=condition_id)).exists()): form.add_error( 'name', _('A condition with that name already exists in this action') ) data['html_form'] = render_to_string(template_name, context, request=request) return JsonResponse(data) # Verify that the condition name does not collide with column names workflow = get_workflow(request, action.workflow.id) if not workflow: # Workflow is not accessible. Go back to the index. data['form_is_valid'] = True data['html_redirect'] = reverse('workflow:index') return JsonResponse(data) # New condition name does not collide with column name if form.cleaned_data['name'] in workflow.get_column_names(): form.add_error( 'name', _('A column name with that name already exists.') ) context = {'form': form, 'action_id': action.id, 'condition_id': condition_id, 'add': is_new} data['html_form'] = render_to_string(template_name, context, request=request) return JsonResponse(data) # New condition name does not collide with attribute names if form.cleaned_data['name'] in workflow.attributes.keys(): form.add_error( 'name', _('The workflow has an attribute with this name.') ) context = {'form': form, 'action_id': action.id, 'condition_id': condition_id, 'add': is_new} data['html_form'] = render_to_string(template_name, context, request=request) return JsonResponse(data) # If condition name has changed, rename appearances in the content # field of the action. if form.old_name and 'name' in form.changed_data: # Performing string substitution in the content and saving # TODO: Review! replacing = '{{% if {0} %}}' action.content = action.content.replace( replacing.format(form.old_name), replacing.format(condition.name)) action.save() log_type = 'condition' # Ok, here we can say that the data in the form is correct. data['form_is_valid'] = True # Proceed to update the DB if is_new: # Get the condition from the form, but don't commit as there are # changes pending. condition = form.save(commit=False) condition.action = action condition.is_filter = is_filter condition.save() else: condition = form.save() # Update the number of selected rows for the conditions condition.update_n_rows_selected() # Update the columns field condition.columns.set( action.workflow.columns.filter( name__in=get_variables(condition.formula)) ) # Update the condition condition.save() # Log the event formula, _ = evaluate_node_sql(condition.formula) if is_new: log_type += '_create' else: log_type += '_update' # Log the event logs.ops.put(request.user, log_type, condition.action.workflow, {'id': condition.id, 'name': condition.name, 'selected_rows': condition.n_rows_selected, 'formula': formula}) data['html_redirect'] = '' return JsonResponse(data)
def search_table_rows(workflow_id, cv_tuples=None, any_join=True, order_col_name=None, order_asc=True, column_names=None, pre_filter=None): """ Select rows where for every (column, value) pair, column contains value ( as in LIKE %value%, these are combined with OR if any is TRUE, or AND if any is false, and the result is ordered by the given column and type (if given) :param workflow_id: workflow object to get to the table :param cv_tuples: A column, value, type tuple to search the value in the column :param any_join: Boolean encoding if values should be combined with OR (or AND) :param order_col_name: Order results by this column :param order_asc: Order results in ascending values (or descending) :param column_names: Optional list of column names to select :param pre_filter: Optional filter condition to pre filter the query set. the query is built with these terms as requirement AND the cv_tuples. :return: The resulting query set """ # Create the query if column_names: safe_column_names = [fix_pctg_in_name(x) for x in column_names] query = 'SELECT "{0}"'.format('", "'.join(safe_column_names)) else: query = 'SELECT *' # Add the table query += ' FROM "{0}"'.format(create_table_name(workflow_id)) # Calculate the first suffix to add to the query filter_txt = '' filter_fields = [] if pre_filter: filter_txt, filter_fields = evaluate_node_sql(pre_filter) if cv_tuples: likes = [] tuple_fields = [] for name, value, data_type in cv_tuples: # Make sure we escape the name and search as text name = fix_pctg_in_name(name) mod_name = '(CAST("{0}" AS TEXT) LIKE %s)'.format(name) # Create the second part of the query setting column LIKE '%value%' likes.append(mod_name) tuple_fields.append('%' + value + '%') # Combine the search subqueries if any_join: tuple_txt = '(' + ' OR '.join(likes) + ')' else: tuple_txt = '(' + ' AND '.join(likes) + ')' # Build the query so far appending the filter and/or the cv_tuples if filter_txt or cv_tuples: query += ' WHERE ' fields = [] # If there has been a suffix from the filter, add it. if filter_txt and filter_fields: query += filter_txt fields.extend(filter_fields) # If there is a pre-filter, the suffix needs to be "AND" with the ones # just calculated if filter_txt and cv_tuples: query += ' AND ' if cv_tuples: query += tuple_txt fields.extend(tuple_fields) # Add the order if needed if order_col_name: query += ' ORDER BY "{0}"'.format(fix_pctg_in_name(order_col_name)) if not order_asc: query += ' DESC' # Execute the query cursor = connection.cursor() cursor.execute(query, fields) # Get the data return cursor.fetchall()