def _get_substitutions(to, comment=None, idea=None, fi=None, previous_di=None, comment_author=None, **kw):
# initialize useful values for substitutions
base_url = get_url_service().base_url
shop_url = get_url_service().expand_url(['shop'], relative=False)
idea_url = get_url_service().expand_url(['idea', idea.id], relative=False) if idea else u''
fi = fi or (idea.wf_context.assignated_fi if idea else None)
di = idea.wf_context.assignated_di if idea else None
mailer = mail.get_mailer()
# creates the substitutions
substitutions = {
u'RECIPIENT_LOGIN': to.uid if to else u'',
u'RECIPIENT_FIRSTNAME': to.firstname if to else u'',
u'RECIPIENT_LASTNAME': to.lastname if to else u'',
u'RECIPIENT_EMAIL': to.email or u'' if to else u'',
u'FI_FIRSTNAME': fi.firstname if fi else u'',
u'FI_LASTNAME': fi.lastname if fi else u'',
u'FI_EMAIL': fi.email if fi and fi.email else u'',
u'DI_FIRSTNAME': di.firstname if di else u'',
u'DI_LASTNAME': di.lastname if di else u'',
u'DI_EMAIL': di.email if di and di.email else u'',
u'IDEA_ID': str(idea.id) if idea else u'',
u'IDEA_TITLE': text_to_html(idea.title) if idea else u'',
u'IDEA_LINK_HERE': render_link(_(u'here'), idea_url),
u'SUBMISSION_DATE': format_date(datetime.today(), format='medium'),
u'COMMENT': text_to_html(comment or u''),
u'COMMENT_AUTHOR_FIRSTNAME': comment_author.firstname if comment_author else '',
u'COMMENT_AUTHOR_LASTNAME': comment_author.lastname if comment_author else '',
u'EUREKA_LINK': render_link(_(u'Eurêka'), base_url),
u'EUREKA_URL_LINK': render_link(base_url, base_url),
u'SHOP_LINK': render_link(_(u'shop'), shop_url),
u'PREVIOUS_DI_EMAIL': (previous_di.email if (previous_di and previous_di.email) else u''),
u'PREVIOUS_DI_FIRSTNAME': (previous_di.firstname if (previous_di and previous_di.firstname) else u''),
u'PREVIOUS_DI_LASTNAME': (previous_di.lastname if (previous_di and previous_di.lastname) else u''),
}
substitutions.update(mailer.get_substitutions())
substitutions.update({k.upper(): v for k, v in kw.iteritems()})
return substitutions
def render_portal(self, h, comp, *args):
with h.rss(version='0.91'):
with h.channel:
h << h.title(APP_TITLE)
h << h.link(get_url_service().base_url)
h << h.description(_('Ideas'))
for idea in get_all_published_ideas().limit(10):
with h.item:
h << h.title(idea.title)
h << h.link(get_url_service().expand_url(['idea', idea.id], relative=False))
h << h.description(idea.description)
h << h.pubDate(idea.publication_date.strftime('%a, %d %b %Y %H:%M:%S'))
return h.root
def __init__(self, editor_id):
gallery_url = get_url_service().expand_url(['gallery'])
gallery_dir = get_fs_service().expand_path(['gallery'])
self.rte_insert_image = RichTextEditorInsertImageCallback(editor_id)
super(Gallery, self).__init__(gallery_url, gallery_dir,
self.rte_insert_image.callback_name)
def send_email_direction(self, direction_label, message):
user = get_current_user()
if user:
mailer = mail.get_mailer()
base_url = get_url_service().base_url
substitutions = {
u'SENDER': user.email,
u'FIRSTNAME': user.firstname,
u'LASTNAME': user.lastname,
u'IDEA_LINK': self.idea.absolute_url,
u'IDEA_ID': str(self.idea.id),
u'DIRECTION_LABEL': direction_label,
u'MESSAGE': message,
u'EUREKA_LINK': render_link(_(u'Eurêka'), base_url),
u'RECIPIENTS': mailer.get_substitutions()[u'DSIG_EMAIL']
}
content = _perform_substitutions(
'mail-idea-duplicated-direction.html',
'fr', substitutions)
(content, subject, sender, to_recipients,
cc_recipients, bcc_recipients) = _extract_metadata(content)
mailer.send_mail(subject, from_=sender, to=to_recipients,
cc=cc_recipients, bcc=bcc_recipients,
content=content, type='html')
def send_email_direction(self, direction_label, message):
user = get_current_user()
if user:
mailer = mail.get_mailer()
base_url = get_url_service().base_url
substitutions = {
u'SENDER': user.email,
u'FIRSTNAME': user.firstname,
u'LASTNAME': user.lastname,
u'IDEA_LINK': self.idea.absolute_url,
u'IDEA_ID': str(self.idea.id),
u'DIRECTION_LABEL': direction_label,
u'MESSAGE': message,
u'EUREKA_LINK': render_link(_(u'Eurêka'), base_url),
u'RECIPIENTS': mailer.get_substitutions()[u'DSIG_EMAIL']
}
content = _perform_substitutions(
'mail-idea-duplicated-direction.html', 'fr', substitutions)
(content, subject, sender, to_recipients, cc_recipients,
bcc_recipients) = _extract_metadata(content)
mailer.send_mail(subject,
from_=sender,
to=to_recipients,
cc=cc_recipients,
bcc=bcc_recipients,
content=content,
type='html')
def commit(self):
if not super(ResetPassword, self).commit((), ('login',)):
return False
uid = self.login.value
password_reset_url = get_url_service().expand_url(['password_reset', uid], relative=False)
ResetPasswordConfirmation(uid).send_email(password_reset_url)
flashmessage.set_flash(_(u"A password reset email has been sent to your contact email address."))
return True
def init_portal_submit(self, url, *args):
challenge_id = None
if len(url) == 2:
challenge_id = to_int(url[1])
# make sure the challenge exists and is active; otherwise, redirect to the "normal" submission
challenge = ChallengeRepository().get_by_id(challenge_id)
now = datetime.now()
if not challenge or not challenge.is_active(now):
raise redirect_to('/submit', permanent=True, base_url=get_url_service().base_url)
self.show_submit_idea(challenge_id)
def commit(self):
if not super(ResetPassword, self).commit((), ('login', )):
return False
uid = self.login.value
password_reset_url = get_url_service().expand_url(
['password_reset', uid], relative=False)
ResetPasswordConfirmation(uid).send_email(password_reset_url)
flashmessage.set_flash(
_(u"A password reset email has been sent to your contact email address."
))
return True
def last_board_export(self):
board_dir = get_fs_service().expand_path(['board'])
if not os.path.exists(board_dir):
return None, None
zip_files = sorted(glob.glob(os.path.join(board_dir, '*.zip')))
if not zip_files:
return None, None
last_export_filename = os.path.basename(zip_files[-1])
date_string = os.path.splitext(last_export_filename)[0].split('_')[1]
date = datetime.strptime(date_string, '%Y%m%d')
url = get_url_service().expand_url(['board', last_export_filename])
return url, date
def last_board_export(self):
board_dir = get_fs_service().expand_path(['board'])
if not os.path.exists(board_dir):
return None, None
zip_files = sorted(glob.glob(os.path.join(board_dir, '*.zip')))
if not zip_files:
return None, None
last_export_filename = os.path.basename(zip_files[-1])
date_string = os.path.splitext(last_export_filename)[0].split('_')[1]
date = datetime.strptime(date_string, '%Y%m%d')
url = get_url_service().expand_url(['board', last_export_filename])
return url, date
def url(self):
return get_url_service().expand_url(['attachments', self.attachment.filepath])
def render_shell_head(self, h, comp, *args):
html_conf = self.configuration['html']
# title
h.head << h.head.title(APP_TITLE)
# rss feed
h.head << h.head.link(href=get_url_service().expand_url(['rss', 'ideas.rss']), rel='', type='application/rss+xml', title=_('Ideas'))
# favicon
h.head << h.head.link(rel="icon", type="image/png",
href=h.head.static_url + "favicon.png")
h.head << h.head.link(rel="shortcut icon", type="image/x-icon",
href=h.head.static_url + "favicon.ico")
# prevent indexing by robots
h.head << h.head.meta(name='robots', content='noindex,nofollow')
# additional meta information
h.head << h.head.meta({'http-equiv': 'Content-Type',
'content': 'text/html; charset=UTF-8'})
h.head << h.head.meta({'http-equiv': 'Content-Script-Type',
'content': 'text/javascript'})
css_files = [YUI_PREFIX + 'assets/skins/sam/skin.css',
'css/base.css',
'css/design.css',
'css/layout.css',
'css/fonts.css']
for css_file in css_files:
h.head.css_url(css_file, media='all')
js_files = ['js/vendor/html5shiv.js',
'js/vendor/nwmatcher-1.2.5-min.js',
'js/vendor/selectivizr-min.js']
ie_js = """[if (gte IE 6)&(lte IE 8)]>
<script src="%s"></script>
<script src="%s"></script>
<script src="%s"></script>
<![endif]""" % tuple(
xhtml.absolute_url(u, h.head.static_url) for u in js_files)
h.head << h.head.comment(ie_js)
# Javascript
if html_conf['use_combined_js']:
js_files = ('js/desktop-combined.js',)
else:
js_files = (
YUI_PREFIX + 'utilities/utilities.js',
YUI_PREFIX + 'element/element-min.js',
YUI_PREFIX + 'datasource/datasource-min.js',
YUI_PREFIX + 'datatable/datatable-min.js',
YUI_PREFIX + 'autocomplete/autocomplete-min.js',
YUI_PREFIX + 'container/container-min.js',
YUI_PREFIX + 'menu/menu-min.js',
YUI_PREFIX + 'button/button-min.js',
YUI_PREFIX + 'editor/editor-min.js',
YUI_PREFIX + 'calendar/calendar-min.js',
'js/notification.js',
'js/rte.js',
'js/desktop.js',
'yui-3.4.1/yui/yui-min.js',
)
for js_file in js_files:
h.head.javascript_url(js_file)
return h.root
def launched_ideas_url(self):
return get_url_service().expand_url(['launched-ideas'])
def submit_idea_url(self):
return get_url_service().expand_url(['submit'])
def idea_url(self):
return get_url_service().expand_url(['idea', self.idea_id],
relative=False)
def profile_url(self):
return get_url_service().expand_url(['profile', self.uid])
def get_url(self):
return get_url_service().expand_url(['idea', '%d#comment%d' % (self.comment.idea.id, self.comment.id)])
def gallery_url(self, editor_id):
return get_url_service().expand_url(['gallery-for', editor_id])
def photo_url(self):
return get_url_service().expand_url(['profile-photo', self._photo_filename])
def profile_url(self, uid=None):
return get_url_service().expand_url(['profile', (uid or self.uid)])
def thumbnail_url(self, size):
return get_url_service().expand_url(['profile-thumbnails', size, self._photo_filename])
def __init__(self, editor_id):
gallery_url = get_url_service().expand_url(['gallery'])
gallery_dir = get_fs_service().expand_path(['gallery'])
self.rte_insert_image = RichTextEditorInsertImageCallback(editor_id)
super(Gallery, self).__init__(gallery_url, gallery_dir, self.rte_insert_image.callback_name)
def render_shell_head(self, h, comp, *args):
html_conf = self.configuration['html']
# title
h.head << h.head.title(APP_TITLE)
# rss feed
h.head << h.head.link(href=get_url_service().expand_url(
['rss', 'ideas.rss']),
rel='',
type='application/rss+xml',
title=_('Ideas'))
# favicon
h.head << h.head.link(
rel="icon", type="image/png", href=h.head.static_url + "favicon.png")
h.head << h.head.link(rel="shortcut icon",
type="image/x-icon",
href=h.head.static_url + "favicon.ico")
# prevent indexing by robots
h.head << h.head.meta(name='robots', content='noindex,nofollow')
# additional meta information
h.head << h.head.meta({
'http-equiv': 'Content-Type',
'content': 'text/html; charset=UTF-8'
})
h.head << h.head.meta({
'http-equiv': 'Content-Script-Type',
'content': 'text/javascript'
})
css_files = [
YUI_PREFIX + 'assets/skins/sam/skin.css', 'css/base.css',
'css/design.css', 'css/layout.css', 'css/fonts.css'
]
for css_file in css_files:
h.head.css_url(css_file, media='all')
js_files = [
'js/vendor/html5shiv.js', 'js/vendor/nwmatcher-1.2.5-min.js',
'js/vendor/selectivizr-min.js'
]
ie_js = """[if (gte IE 6)&(lte IE 8)]>
<script src="%s"></script>
<script src="%s"></script>
<script src="%s"></script>
<![endif]""" % tuple(
xhtml.absolute_url(u, h.head.static_url) for u in js_files)
h.head << h.head.comment(ie_js)
# Javascript
if html_conf['use_combined_js']:
js_files = ('js/desktop-combined.js', )
else:
js_files = (
YUI_PREFIX + 'utilities/utilities.js',
YUI_PREFIX + 'element/element-min.js',
YUI_PREFIX + 'datasource/datasource-min.js',
YUI_PREFIX + 'datatable/datatable-min.js',
YUI_PREFIX + 'autocomplete/autocomplete-min.js',
YUI_PREFIX + 'container/container-min.js',
YUI_PREFIX + 'menu/menu-min.js',
YUI_PREFIX + 'button/button-min.js',
YUI_PREFIX + 'editor/editor-min.js',
YUI_PREFIX + 'calendar/calendar-min.js',
'js/notification.js',
'js/rte.js',
'js/desktop.js',
'yui-3.4.1/yui/yui-min.js',
)
for js_file in js_files:
h.head.javascript_url(js_file)
return h.root
def idea_url(self):
return get_url_service().expand_url(['idea', self.idea_id], relative=False)
def url(self):
return get_url_service().expand_url(
['attachments', self.attachment.filepath])
def submit_idea_url(self):
if not self.challenge_id:
return get_url_service().expand_url(['submit'])
return get_url_service().expand_url(['submit', self.challenge_id])
def improvements_url(self):
return get_url_service().expand_url(['improvements'])
def submit_idea_url(self):
if not self.challenge_id:
return get_url_service().expand_url(['submit'])
return get_url_service().expand_url(['submit', self.challenge_id])
def profile_url(self):
return get_url_service().expand_url(['profile', self.uid])
def submit_idea_url(self):
return get_url_service().expand_url(['submit'])
def url(self):
return get_url_service().expand_url(['article', self.id])
def ideas_url(self):
return get_url_service().expand_url(['ideas'])
def thumbnail_url(self):
if not self.article.thumbnail_filename:
return None
return get_url_service().expand_url(['articles-thumbnails', self.article.thumbnail_filename])
def idea_url(self, idea):
return get_url_service().expand_url(['idea', idea.id])
def init_shell_news_gallery(self, url, *args):
raise redirect_to(get_url_service().expand_url(['gallery', url[1]]),
permanent=True,
base_url=get_url_service().base_url)
def thumbnail_url(self):
if not self.article.thumbnail_filename:
return None
return get_url_service().expand_url(
['articles-thumbnails', self.article.thumbnail_filename])
def get_url(self):
return get_url_service().expand_url(
['idea',
'%d#comment%d' % (self.comment.idea.id, self.comment.id)])
def gallery_url(self, editor_id):
return get_url_service().expand_url(['gallery-for', editor_id])
def challenge_ideas_url(self):
return get_url_service().expand_url(['challenge_ideas', self.challenge.id])
def url(self):
return get_url_service().expand_url(['idea', self.id])
def export(idea_ids, permission=None, filename_prefix='export'):
# gets the information to export
columns = [(_(u'Id'), 1500),
(_(u'Title'), 20000),
(_(u'Challenge'), 10000),
(_(u'Description'), 15000),
(_(u'Origin'), 10000),
(_(u'Impact'), 10000),
(_(u'Submission date'), 5000),
(_(u'Author name'), 7000),
(_(u'Position'), 3000),
(_(u'Corporation'), 7000),
(_(u'Direction'), 7000),
(_(u'Service'), 7000),
(_(u'Site'), 7000),
(_(u'Votes count'), 5000),
(_(u'Comments count'), 5000),
(_(u'Domain'), 8000),
(_(u'State'), 5000),
(_(u'Url'), 8000),
(_(u'Facilitator'), 5000),
(_(u'Developer'), 5000),
(_(u'Benefit_department'), 5000),
]
# launches the query
# FIXME: don't create a query: use the appropriate IdeaRepository's method instead
AuthorUser = aliased(UserData)
FIUser = aliased(UserData)
DIUser = aliased(UserData)
Corporation = aliased(OrganizationData)
Direction = aliased(OrganizationData)
Service = aliased(OrganizationData)
Site = aliased(OrganizationData)
SubSite = aliased(OrganizationData)
q = session.query(
IdeaData.id,
IdeaData.submission_date,
IdeaData.challenge_id,
ChallengeData.title.label('challenge'),
IdeaData.title,
IdeaData.description,
IdeaData.origin,
IdeaData.impact,
IdeaData.benefit_department,
AuthorUser.uid,
AuthorUser.firstname,
AuthorUser.lastname,
AuthorUser.position,
Corporation.label.label('corporation'),
Direction.label.label('direction'),
Service.label.label('service'),
Site.label.label('site'),
DomainData.label.label('domain'),
FIUser.uid.label('fi_uid'),
FIUser.firstname.label('fi_firstname'),
FIUser.lastname.label('fi_lastname'),
DIUser.uid.label('di_uid'),
DIUser.firstname.label('di_firstname'),
DIUser.lastname.label('di_lastname'),
StateData.label.label('state'),
IdeaData.total_votes,
IdeaData.total_comments,
IdeaEvalContextData.target_date.label('target_date'),
IdeaEvalContextData.goal.label('goal'),
IdeaEvalContextData.revenues_first_year.label('revenues_first_year'),
IdeaEvalContextData.revenues_first_year_value.label(
'revenues_first_year_value'),
IdeaEvalContextData.revenues_second_year.label('revenues_second_year'),
IdeaEvalContextData.revenues_second_year_value.label(
'revenues_second_year_value'),
IdeaEvalContextData.expenses_first_year.label('expenses_first_year'),
IdeaEvalContextData.expenses_first_year_value.label(
'expenses_first_year_value'),
IdeaEvalContextData.expenses_second_year.label('expenses_second_year'),
IdeaEvalContextData.expenses_second_year_value.label(
'expenses_second_year_value'),
IdeaEvalContextData.evaluation_impact.label('evaluation_impact'),
).outerjoin(AuthorData)
q = q.join((AuthorUser, AuthorData.user))
q = q.outerjoin((Corporation, AuthorUser.corporation))
q = q.outerjoin((Direction, AuthorUser.direction))
q = q.outerjoin((Service, AuthorUser.service))
q = q.outerjoin((Site, AuthorUser.site))
q = q.outerjoin((SubSite, AuthorUser.subsite))
q = q.outerjoin(IdeaData.wf_context)
q = q.outerjoin(IdeaData.eval_context)
q = q.outerjoin(IdeaData.domain)
q = q.outerjoin(IdeaWFContextData.state)
q = q.outerjoin((DIUser, IdeaWFContextData.assignated_di))
q = q.outerjoin((FIUser, IdeaWFContextData.assignated_fi))
q = q.outerjoin(IdeaData.challenge)
q = q.filter(IdeaData.id.in_(idea_ids))
years_revenues = []
years_expenses = []
if permission in ('dsig', 'developer'):
columns = columns + [(_(u'target_date'), 5000),
(_(u'goal'), 5000)]
for row in q:
years_revenues = years_revenues + [row.revenues_first_year, row.revenues_second_year]
years_revenues = list(set(years_revenues))
years_revenues = [y for y in years_revenues if y]
years_revenues.sort()
years_expenses = years_expenses + [row.expenses_first_year, row.expenses_second_year]
years_expenses = list(set(years_expenses))
years_expenses = [y for y in years_expenses if y]
years_expenses.sort()
columns = columns + [(_(u'Revenue %s') % y, 5000) for y in years_revenues]
columns = columns + [(_(u'Expenses %s') % y, 5000) for y in years_expenses]
columns = columns + [(_(u'CF/MSCV'), 3000)]
authors = {}
for id, ideas in itertools.groupby(q, key=lambda row: row.id):
for idea in ideas:
authors.setdefault(id, []).append(
'%s %s' % (idea.firstname, idea.lastname))
# creates the workbook
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet(u'Export')
f = xlwt.Formatting.Font()
f.underline = xlwt.Formatting.Font.UNDERLINE_SINGLE
f.colour_index = 4 # built-in blue
link_style = xlwt.Style.XFStyle()
link_style.font = f
date_style = xlwt.easyxf(num_format_str='DD-MM-YYYY HH:MM')
# write header row & define columns width
for col_index, (name, width) in enumerate(columns):
ws.write(0, col_index, unicode(name))
ws.col(col_index).width = width
# write ideas
duplicates = set()
row_index = 1
for row in q:
# Remove duplicates
# EV 592 : all author names in the same cell
if row.id in duplicates:
continue
else:
duplicates.add(row.id)
idea_url = get_url_service().expand_url(['idea', row.id], relative=False)
row_data = [
unicode(row.id),
row.title,
u'%s (%s)' % (row.challenge, row.challenge_id) if row.challenge_id else u'',
row.description,
row.origin,
row.impact,
(row.submission_date, date_style),
'; '.join(authors.get(row.id, [])),
row.position,
row.corporation,
row.direction,
row.service,
row.site,
unicode(row.total_votes),
unicode(row.total_comments),
_(row.domain),
_(row.state),
(xlwt.Formula(u'HYPERLINK("%s";"%s")' % (idea_url, idea_url)),
link_style),
u'%s %s' % (row.fi_firstname, row.fi_lastname) if row.fi_uid else u'',
u'%s %s' % (row.di_firstname, row.di_lastname) if row.di_uid else u'',
row.benefit_department,
]
if permission in ('dsig', 'developer'):
row_data = row_data + [
row.target_date.strftime(
'%d/%m/%Y') if row.target_date else '',
row.goal,
]
row_year_revenues = ['' for y in years_revenues]
row_year_expenses = ['' for y in years_expenses]
if row.revenues_first_year:
index = years_revenues.index(row.revenues_first_year)
row_year_revenues[index] = row.revenues_first_year_value
if row.revenues_second_year:
index = years_revenues.index(row.revenues_second_year)
row_year_revenues[index] = row.revenues_second_year_value
if row.expenses_first_year:
index = years_expenses.index(row.expenses_first_year)
row_year_expenses[index] = row.expenses_first_year_value
if row.expenses_second_year:
index = years_expenses.index(row.expenses_second_year)
row_year_expenses[index] = row.expenses_second_year_value
row_data = row_data + row_year_revenues + row_year_expenses
row_data = row_data + [row.evaluation_impact]
for col_index, data in enumerate(row_data):
if hasattr(data, '__iter__'):
ws.write(row_index, col_index, *data)
else:
ws.write(row_index, col_index, data)
row_index += 1
# saves it in a memory buffer
stream = StringIO()
wb.save(stream)
content = stream.getvalue()
stream.close()
timestamp = datetime.now().strftime('%Y-%m-%d')
filename = filename_prefix + '_' + timestamp + '.xls'
return content, filename
def url(self):
return get_url_service().expand_url(['article', self.id])
def init_shell_news_gallery(self, url, *args):
raise redirect_to(get_url_service().expand_url(['gallery', url[1]]), permanent=True, base_url=get_url_service().base_url)
def url(self):
return get_url_service().expand_url(['idea', self.id])
def launched_ideas_url(self):
return get_url_service().expand_url(['launched-ideas'])
def challenge_ideas_url(self):
return get_url_service().expand_url(
['challenge_ideas', self.challenge.id])
def ideas_url(self):
return get_url_service().expand_url(['ideas'])
def export(idea_ids, permission=None, filename_prefix='export'):
# gets the information to export
columns = [
(_(u'Id'), 1500),
(_(u'Title'), 20000),
(_(u'Challenge'), 10000),
(_(u'Description'), 15000),
(_(u'Origin'), 10000),
(_(u'Impact'), 10000),
(_(u'Submission date'), 5000),
(_(u'Author name'), 7000),
(_(u'Position'), 3000),
(_(u'Corporation'), 7000),
(_(u'Direction'), 7000),
(_(u'Service'), 7000),
(_(u'Site'), 7000),
(_(u'Votes count'), 5000),
(_(u'Comments count'), 5000),
(_(u'Domain'), 8000),
(_(u'State'), 5000),
(_(u'Url'), 8000),
(_(u'Facilitator'), 5000),
(_(u'Developer'), 5000),
(_(u'Benefit_department'), 5000),
]
# launches the query
# FIXME: don't create a query: use the appropriate IdeaRepository's method instead
AuthorUser = aliased(UserData)
FIUser = aliased(UserData)
DIUser = aliased(UserData)
Corporation = aliased(OrganizationData)
Direction = aliased(OrganizationData)
Service = aliased(OrganizationData)
Site = aliased(OrganizationData)
SubSite = aliased(OrganizationData)
q = session.query(
IdeaData.id,
IdeaData.submission_date,
IdeaData.challenge_id,
ChallengeData.title.label('challenge'),
IdeaData.title,
IdeaData.description,
IdeaData.origin,
IdeaData.impact,
IdeaData.benefit_department,
AuthorUser.uid,
AuthorUser.firstname,
AuthorUser.lastname,
AuthorUser.position,
Corporation.label.label('corporation'),
Direction.label.label('direction'),
Service.label.label('service'),
Site.label.label('site'),
DomainData.label.label('domain'),
FIUser.uid.label('fi_uid'),
FIUser.firstname.label('fi_firstname'),
FIUser.lastname.label('fi_lastname'),
DIUser.uid.label('di_uid'),
DIUser.firstname.label('di_firstname'),
DIUser.lastname.label('di_lastname'),
StateData.label.label('state'),
IdeaData.total_votes,
IdeaData.total_comments,
IdeaEvalContextData.target_date.label('target_date'),
IdeaEvalContextData.goal.label('goal'),
IdeaEvalContextData.revenues_first_year.label('revenues_first_year'),
IdeaEvalContextData.revenues_first_year_value.label(
'revenues_first_year_value'),
IdeaEvalContextData.revenues_second_year.label('revenues_second_year'),
IdeaEvalContextData.revenues_second_year_value.label(
'revenues_second_year_value'),
IdeaEvalContextData.expenses_first_year.label('expenses_first_year'),
IdeaEvalContextData.expenses_first_year_value.label(
'expenses_first_year_value'),
IdeaEvalContextData.expenses_second_year.label('expenses_second_year'),
IdeaEvalContextData.expenses_second_year_value.label(
'expenses_second_year_value'),
IdeaEvalContextData.evaluation_impact.label('evaluation_impact'),
).outerjoin(AuthorData)
q = q.join((AuthorUser, AuthorData.user))
q = q.outerjoin((Corporation, AuthorUser.corporation))
q = q.outerjoin((Direction, AuthorUser.direction))
q = q.outerjoin((Service, AuthorUser.service))
q = q.outerjoin((Site, AuthorUser.site))
q = q.outerjoin((SubSite, AuthorUser.subsite))
q = q.outerjoin(IdeaData.wf_context)
q = q.outerjoin(IdeaData.eval_context)
q = q.outerjoin(IdeaData.domain)
q = q.outerjoin(IdeaWFContextData.state)
q = q.outerjoin((DIUser, IdeaWFContextData.assignated_di))
q = q.outerjoin((FIUser, IdeaWFContextData.assignated_fi))
q = q.outerjoin(IdeaData.challenge)
q = q.filter(IdeaData.id.in_(idea_ids))
years_revenues = []
years_expenses = []
if permission in ('dsig', 'developer'):
columns = columns + [(_(u'target_date'), 5000), (_(u'goal'), 5000)]
for row in q:
years_revenues = years_revenues + [
row.revenues_first_year, row.revenues_second_year
]
years_revenues = list(set(years_revenues))
years_revenues = [y for y in years_revenues if y]
years_revenues.sort()
years_expenses = years_expenses + [
row.expenses_first_year, row.expenses_second_year
]
years_expenses = list(set(years_expenses))
years_expenses = [y for y in years_expenses if y]
years_expenses.sort()
columns = columns + [(_(u'Revenue %s') % y, 5000)
for y in years_revenues]
columns = columns + [(_(u'Expenses %s') % y, 5000)
for y in years_expenses]
columns = columns + [(_(u'CF/MSCV'), 3000)]
authors = {}
for id, ideas in itertools.groupby(q, key=lambda row: row.id):
for idea in ideas:
authors.setdefault(id, []).append('%s %s' %
(idea.firstname, idea.lastname))
# creates the workbook
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet(u'Export')
f = xlwt.Formatting.Font()
f.underline = xlwt.Formatting.Font.UNDERLINE_SINGLE
f.colour_index = 4 # built-in blue
link_style = xlwt.Style.XFStyle()
link_style.font = f
date_style = xlwt.easyxf(num_format_str='DD-MM-YYYY HH:MM')
# write header row & define columns width
for col_index, (name, width) in enumerate(columns):
ws.write(0, col_index, unicode(name))
ws.col(col_index).width = width
# write ideas
duplicates = set()
row_index = 1
for row in q:
# Remove duplicates
# EV 592 : all author names in the same cell
if row.id in duplicates:
continue
else:
duplicates.add(row.id)
idea_url = get_url_service().expand_url(['idea', row.id],
relative=False)
row_data = [
unicode(row.id),
row.title,
u'%s (%s)' %
(row.challenge, row.challenge_id) if row.challenge_id else u'',
row.description,
row.origin,
row.impact,
(row.submission_date, date_style),
'; '.join(authors.get(row.id, [])),
row.position,
row.corporation,
row.direction,
row.service,
row.site,
unicode(row.total_votes),
unicode(row.total_comments),
_(row.domain),
_(row.state),
(xlwt.Formula(u'HYPERLINK("%s";"%s")' % (idea_url, idea_url)),
link_style),
u'%s %s' %
(row.fi_firstname, row.fi_lastname) if row.fi_uid else u'',
u'%s %s' %
(row.di_firstname, row.di_lastname) if row.di_uid else u'',
row.benefit_department,
]
if permission in ('dsig', 'developer'):
row_data = row_data + [
row.target_date.strftime('%d/%m/%Y')
if row.target_date else '',
row.goal,
]
row_year_revenues = ['' for y in years_revenues]
row_year_expenses = ['' for y in years_expenses]
if row.revenues_first_year:
index = years_revenues.index(row.revenues_first_year)
row_year_revenues[index] = row.revenues_first_year_value
if row.revenues_second_year:
index = years_revenues.index(row.revenues_second_year)
row_year_revenues[index] = row.revenues_second_year_value
if row.expenses_first_year:
index = years_expenses.index(row.expenses_first_year)
row_year_expenses[index] = row.expenses_first_year_value
if row.expenses_second_year:
index = years_expenses.index(row.expenses_second_year)
row_year_expenses[index] = row.expenses_second_year_value
row_data = row_data + row_year_revenues + row_year_expenses
row_data = row_data + [row.evaluation_impact]
for col_index, data in enumerate(row_data):
if hasattr(data, '__iter__'):
ws.write(row_index, col_index, *data)
else:
ws.write(row_index, col_index, data)
row_index += 1
# saves it in a memory buffer
stream = StringIO()
wb.save(stream)
content = stream.getvalue()
stream.close()
timestamp = datetime.now().strftime('%Y-%m-%d')
filename = filename_prefix + '_' + timestamp + '.xls'
return content, filename
def idea_url(self, idea):
return get_url_service().expand_url(['idea', idea.id])