CSV export of drafts proposals
# bundle exec rails c
proposal_component_id = 33 # choose a proposal component id
drafts = Decidim::Proposals::Proposal.where(decidim_component_id: proposal_component_id).drafts
CSV.open("tmp/proposals-drafts.csv", "wb") do |csv|
csv << Decidim::Proposals::Proposal.attribute_names
drafts.each do |draft|
csv << draft.attributes.values.map do |value|
if value.is_a?(Hash) && value.has_key?("fr")
value["fr"]
else
value
end
end
end
end
require "csv"
require "fileutils"
min_date = [Decidim::ParticipatoryProcessStep.minimum(:created_at), Decidim::User.minimum(:confirmed_at), Decidim::Follow.minimum(:created_at), Decidim::ParticipatoryProcess.minimum(:created_at), Decidim::Assembly.minimum(:created_at)].compact.min
host = Decidim::Organization.first.host
file = "tmp/#{Date.today.strftime("%Y-%m-%d")}-#{host}-decidim-stats.csv"
CSV.open(file, "wb") do |csv|
followables = Decidim::Follow.select("DISTINCT(decidim_followable_type)")
csv << ["platform","date", "sign in count", "user registration count", "admin registration count", "process created count", "phase changes", "assembly created count", "component created count", "proposals", "proposals votes"] + followables.map { |followable| followable.decidim_followable_type.demodulize.titleize }
d = min_date
while d < Date.today
values = [
Decidim::User.joins(:versions).where("versions.event = 'update' AND versions.object_changes LIKE '%current_sign_in_at%'").where(versions: {created_at: d.beginning_of_day..d.end_of_day}).count,
Decidim::User.where.not(admin: true).where(confirmed_at: d.beginning_of_day..d.end_of_day, roles: []).count,
Decidim::User.where.not(roles: []).where(confirmed_at: d.beginning_of_day..d.end_of_day).count + Decidim::User.where(admin: true, confirmed_at: d.beginning_of_day..d.end_of_day).count,
Decidim::ParticipatoryProcess.where(created_at: d.beginning_of_day..d.end_of_day).count,
Decidim::ParticipatoryProcessStep.joins(:versions).where(versions: {created_at: d.beginning_of_day..d.end_of_day}).count,
Decidim::Assembly.where(created_at: d.beginning_of_day..d.end_of_day).count,
Decidim::Component.where(created_at: d.beginning_of_day..d.end_of_day).count,
Decidim::Proposals::Proposal.where.not(published_at: nil).where(created_at: d.beginning_of_day..d.end_of_day).count,
Decidim::Proposals::ProposalVote.where(created_at: d.beginning_of_day..d.end_of_day).count,
] + followables.map { |followable| Decidim::Follow.where(decidim_followable_type: followable.decidim_followable_type, created_at: d.beginning_of_day..d.end_of_day).count }
if values.any? { |value| value > 0 }
csv << [host, d.strftime("%Y-%m-%d")] + values
end
d += 1.day
end
end
FileUtils.cp(file, "tmp/latest-decidim-stats.csv")
CSV export of authors of proposals
proposal_component_id = 33 # choose a proposal component id
proposals = Decidim::Proposals::Proposal.joins(:coauthorships).where(decidim_component_id: proposal_component_id).published
CSV.open("tmp/#{Time.current.to_i}-proposals.csv", "wb") do |csv|
csv << [*Decidim::Proposals::Proposal.attribute_names, "emails", "nicknames"]
proposals.each do |proposal|
line = proposal.attributes.values.map do |value|
if value.is_a?(Hash) && value.has_key?("fr")
value["fr"]
elsif value.is_a?(Hash)
value.to_json
else
value
end
end
line.push(proposal.authors.map do |author|
if author.is_a?(Decidim::Organization)
"official"
else
author.email
end
end.join(" "))
line.push(proposal.authors.map do |author|
if author.is_a?(Decidim::Organization)
"official"
else
author.nickname
end
end.join(" "))
csv << line
end
end
Find proposals for authored by a user.
# bundle exec rails c
user_id = 2081 # a user id
drafts = Decidim::Proposals::Proposal.drafts.joins(:coauthorships).where(decidim_coautho
rships: {decidim_author_id: user_id})
published = Decidim::Proposals::Proposal.joins(:coauthorships).where(decidim_coautho
rships: {decidim_author_id: user_id})
puts "drafts", drafts.map {|d| d.title["fr"]}
puts "published", published.map {|d| d.title["fr"]}
Export all emails registered to the newsletter in a csv
# bundle exec rails c
CSV.open("tmp/newsletter.csv", "wb") do |csv|
users = Decidim::User.where("newsletter_notifications_at IS NOT NULL AND confirmed_at IS NOT NULL AND blocked_at IS NULL AND email IS NOT NULL").not_deleted
csv << ["name", "nickname", "email", "subscribed_at"]
users.pluck(:name, :nickname, :email, :newsletter_notifications_at).each do |user|
csv << user
end
end
Import proposals CSV with custom fields
require 'csv'
csv_file = "2024_EF_CartoParticipative_POINTS_WGS84_transformed.csv"
csv_file_path = "#{Rails.root}/tmp/#{csv_file}"
author_id = 1424
component_id = 267
# Categories mapping
categories = {
"Point d’eau" => 34,
"Espace vert" => 35,
"Loisirs" => 36,
"A l’ombre" => 37,
"Intérieur" => 38,
"Autre" => 39
}
author = Decidim::Organization.first # Official proposition, use user if you want to create a proposal for user_id.
# Read the CSV file
CSV.foreach(csv_file_path, headers: true) do |row|
# Extract data from the CSV row
title = row['title']
comment = row['Comment']
category_name = row['Category']
latitude = row["Longitude"]
longitude = row["Longitude"]
# Create the body with the required structure
body = <<~XML
<xml><dl class="decidim_awesome-custom_fields" data-generator="decidim_awesome" data-version="0.10.2">
<dt name="commentaire-espace-frais">Commentaire sur l'espace frais</dt>
<dd id="commentaire-espace-frais" name="textarea"><div>#{comment}</div></dd>
<dt name="confirm-lausanne-address">L'adresse mentionnée est à Lausanne</dt>
<dd id="confirm-lausanne-address" name="checkbox-group"><div alt="answer-yes">Oui</div></dd>
</dl></xml>
XML
# Find the category id (assuming you have a column in the CSV that maps to a category)
# Replace with the actual column name if different
category_id = categories[category_name]
# Create the proposal
proposal = Decidim::Proposals::Proposal.new(
title: { "fr" => title },
body: { "fr" => body },
decidim_component_id: component_id,
category: Decidim::Category.find(category_id)
)
proposal.add_coauthor(author, user_group: nil)
proposal.address = title
proposal.latitude = latitude
proposal.longitude = longitude
proposal.save!
end
Delete a confidential file from a published proposal
# bundle exec rails c
to_delete = {
"321": ["passport.pdf"],
"331": ["id-card.pdf", "phone-contacts.pdf", "DOC287.pdf", "DOC288.pdf"],
"342": ["project-holder-data.pdf", "Sign-scan.pdf"]
}
to_delete.each do |proposal_id, filenames|
filenames.each do |filename|
proposal = Decidim::Proposals::Proposal.find(proposal_id.to_s.to_i)
next unless proposal
matches = proposal.attachments.where(file: filename)
if matches.size == 0
puts "file #{filename} not found in #{proposal_id}"
next
end
matches.each do |file_to_destroy|
puts "Are you sure to delete #{file_to_destroy.url} from #{proposal.title['fr']}? y/n"
if STDIN.gets.chomp == 'y'
FileUtils.cp("/home/decidim/app/public/#{file_to_destroy.file.url}", "/home/decidim/app/tmp/deleted/#{file_to_destroy.id}_#{file_to_destroy.title['fr']}")
file_to_destroy.destroy
end
end
end
end
Add a scope to a proposal
How to find resources
scope_id: Go in proposal page, and filter by scope. the scope_id appears in the URLproposal_id : Click in proposal detail, the proposal_id appears in the URL.# bundle exec rails c
scope_id = 8
proposal_id = 456
scope = Decidim::Scope.find(scope_id)
proposal = Decidim::Proposals::Proposal.find(proposal_id)
proposal.update(scope: scope)