'XLS Representation'

def xls(self, r, **attr):

    from ..s3.s3codecs import S3XLS
    try:
        import xlwt
    except ImportError:
        error = S3XLS.ERROR.XLWT_ERROR
        current.log.error(error)
        raise HTTP(503, body=error)
    T = current.T
    (dates, years, goals) = self._extract(r, **attr)
    if (not years):
        current.session.warning = T('No    Indicator    Data    available')
        redirect(('/%s/project/project/%s' % (current.request.application, r.id)))
    s3db = current.s3db
    NONE = current.messages['NONE']
    record = r.record
    date_represent = s3db.project_indicator_data.end_date.represent
    labels11 = [s3_unicode(T('Number')), s3_unicode(T('Indicators')), s3_unicode(T('Total    Target'))]
    labels12 = []
    lappend = labels12.append
    y = 0
    year = years[y]
    for d in dates:
        if (d.year != year):
            lappend(str(year))
            y += 1
            year = years[y]
        lappend(date_represent(d))
    lappend(str(year))
    labels13 = [s3_unicode(T('Actual    Total')), s3_unicode(T('%    Achieved'))]
    labels22 = []
    lappend = labels22.append
    y = 0
    year = years[y]
    TARGET = s3_unicode(T('Target'))
    ACTUAL = s3_unicode(T('Actual'))
    for d in dates:
        if (d.year != year):
            lappend(TARGET)
            lappend(ACTUAL)
            y += 1
            year = years[y]
        lappend(TARGET)
        lappend(ACTUAL)
    lappend(TARGET)
    lappend(ACTUAL)
    COL_WIDTH_MULTIPLIER = S3XLS.COL_WIDTH_MULTIPLIER
    styles = S3XLS._styles(use_colour=True, evenodd=False)
    large_header_style = styles['large_header']
    large_header_style.alignment.horz = large_header_style.alignment.HORZ_LEFT
    NO_PATTERN = large_header_style.pattern.NO_PATTERN
    large_header_style.pattern.pattern = NO_PATTERN
    notes_style = styles['notes']
    book = xlwt.Workbook(encoding='utf-8')
    sheet = book.add_sheet(s3_unicode(T('Report')))
    col_index = 0
    column_widths = []
    for label in ((labels11 + labels22) + labels13):
        width = max((len(label) * COL_WIDTH_MULTIPLIER), 2000)
        width = min(width, 65535)
        column_widths.append(width)
        sheet.col(col_index).width = width
        col_index += 1
    title = s3_unicode(T('Summary    of    Progress    Indicators    for    Outcomes    and    Indicators'))
    current_row = sheet.row(0)
    current_row.height = 500
    current_row.write(0, title, large_header_style)
    project_represent = s3db.project_project_represent(None, record)
    current_row = sheet.row(1)
    current_row.height = 500
    label = ('%s:' % T('Project'))
    current_row.write(0, label, large_header_style)
    current_row.write(1, project_represent, large_header_style)
    if (((len(label) * COL_WIDTH_MULTIPLIER) * 2) > sheet.col(0).width):
        sheet.col(0).width = ((len(label) * COL_WIDTH_MULTIPLIER) * 2)
    current_row = sheet.row(2)
    current_row.write(0, ('%s:' % T('Date    Exported')), notes_style)
    current_row.write(1, current.request.now, notes_style)
    current_row = sheet.row(3)
    header_style = styles['header']
    HORZ_CENTER = header_style.alignment.HORZ_CENTER
    header_style.alignment.horz = HORZ_CENTER
    header_style.pattern.pattern = NO_PATTERN
    col_index = 0
    for label in labels11:
        current_row.write(col_index, label, header_style)
        col_index += 1
    for label in labels12:
        sheet.write_merge(3, 3, col_index, (col_index + 1), label, header_style)
        col_index += 2
    for label in labels13:
        current_row.write(col_index, label, header_style)
        col_index += 1
    current_row = sheet.row(4)
    col_index = 3
    for label in labels22:
        current_row.write(col_index, label, header_style)
        col_index += 1
    red_style = xlwt.XFStyle()
    SOLID_PATTERN = red_style.pattern.SOLID_PATTERN
    red_style.pattern.pattern = SOLID_PATTERN
    red_style.pattern.pattern_fore_colour = 10
    red_style.alignment.horz = HORZ_CENTER
    green_style = xlwt.XFStyle()
    green_style.pattern.pattern = SOLID_PATTERN
    green_style.pattern.pattern_fore_colour = 17
    green_style.alignment.horz = HORZ_CENTER
    yellow_style = xlwt.XFStyle()
    yellow_style.pattern.pattern = SOLID_PATTERN
    yellow_style.pattern.pattern_fore_colour = 13
    yellow_style.alignment.horz = HORZ_CENTER
    goal_style = xlwt.XFStyle()
    goal_style.pattern.pattern = SOLID_PATTERN
    outcome_style = xlwt.XFStyle()
    outcome_style.pattern.pattern = SOLID_PATTERN
    output_style = xlwt.XFStyle()
    output_style.pattern.pattern = SOLID_PATTERN
    try:
        add_palette_colour = xlwt.add_palette_colour
    except:
        current.log.warning('Custom    Excel    Palette    requires    xlwt    0.7.5+,    using    approximate    values')
        goal_style.pattern.pattern_fore_colour = 44
        outcome_style.pattern.pattern_fore_colour = 46
        output_style.pattern.pattern_fore_colour = 50
    else:
        add_palette_colour('goal', 33)
        book.set_colour_RGB(33, 68, 170, 255)
        goal_style.pattern.pattern_fore_colour = 33
        add_palette_colour('outcome', 34)
        book.set_colour_RGB(34, 204, 193, 218)
        outcome_style.pattern.pattern_fore_colour = 34
        add_palette_colour('output', 35)
        book.set_colour_RGB(35, 194, 214, 155)
        output_style.pattern.pattern_fore_colour = 35
    status_represent = (lambda v: IS_FLOAT_AMOUNT.represent(v, precision=2))
    def status_style(value):
        if (value >= 80):
            return green_style
        elif (value >= 60):
            return yellow_style
        else:
            return red_style
    colspan = (((2 * len(dates)) + (2 * len(years))) + 3)
    row_index = 5
    for goal_id in goals:
        current_row = sheet.row(row_index)
        goal = goals[goal_id]
        current_row.write(0, ('%s:    %s' % (T('Goal'), goal['code'])), goal_style)
        sheet.write_merge(row_index, row_index, 1, colspan, goal['name'], goal_style)
        status = goal['status']
        current_row.write((colspan + 1), status_represent(status), status_style(status))
        row_index += 1
        outcomes = goal['outcomes']
        for output_id in outcomes:
            current_row = sheet.row(row_index)
            outcome = outcomes[output_id]
            current_row.write(0, ('%s:    %s' % (T('Outcome'), outcome['code'])), outcome_style)
            sheet.write_merge(row_index, row_index, 1, colspan, outcome['name'], outcome_style)
            status = outcome['status']
            current_row.write((colspan + 1), status_represent(status), status_style(status))
            row_index += 1
            outputs = outcome['outputs']
            for p in outputs:
                current_row = sheet.row(row_index)
                output = outputs[p]
                current_row.write(0, ('%s:    %s' % (T('Output'), output['code'])), output_style)
                sheet.write_merge(row_index, row_index, 1, colspan, output['name'], output_style)
                status = output['status']
                current_row.write((colspan + 1), status_represent(status), status_style(status))
                row_index += 1
                indicators = output['indicators']
                for i in indicators:
                    current_row = sheet.row(row_index)
                    indicator = indicators[i]
                    current_row.write(0, ('%s:    %s' % (T('Indicator'), indicator['code'])))
                    current_row.write(1, indicator['name'])
                    current_row.write(2, indicator['target'])
                    col_index = 3
                    y = 0
                    year = years[y]
                    for d in dates:
                        if (d.year != year):
                            iyear = indicator['years'].get(year)
                            if iyear:
                                current_row.write(col_index, iyear['target'])
                                col_index += 1
                                current_row.write(col_index, iyear['actual'])
                                col_index += 1
                            else:
                                current_row.write(col_index, NONE)
                                col_index += 1
                                current_row.write(col_index, NONE)
                                col_index += 1
                            y += 1
                            year = years[y]
                        date = indicator['dates'].get(d)
                        if date:
                            current_row.write(col_index, date['target'])
                            col_index += 1
                            current_row.write(col_index, date['actual'])
                            col_index += 1
                        else:
                            current_row.write(col_index, NONE)
                            col_index += 1
                            current_row.write(col_index, NONE)
                            col_index += 1
                    iyear = indicator['years'].get(year)
                    if iyear:
                        current_row.write(col_index, iyear['target'])
                        col_index += 1
                        current_row.write(col_index, iyear['actual'])
                        col_index += 1
                    else:
                        current_row.write(col_index, NONE)
                        col_index += 1
                        current_row.write(col_index, NONE)
                        col_index += 1
                    current_row.write(col_index, indicator['actual'])
                    col_index += 1
                    status = indicator['status']
                    current_row.write(col_index, status_represent(status), status_style(status))
                    row_index += 1
    current_row = sheet.row(row_index)
    label = s3_unicode(T('Overall    Project    Status'))
    if ((len(label) * COL_WIDTH_MULTIPLIER) > sheet.col(colspan).width):
        sheet.col(colspan).width = (len(label) * COL_WIDTH_MULTIPLIER)
    current_row.write(colspan, label)
    status = record.overall_status_by_indicators
    current_row.write((colspan + 1), status_represent(status), status_style(status))
    try:
        from cStringIO import StringIO
    except:
        from StringIO import StringIO
    output = StringIO()
    try:
        book.save(output)
    except:
        import sys
        error = sys.exc_info()[1]
        current.log.error(error)
    output.seek(0)
    filename = ('%s.xls' % title.encode('utf8'))
    response = current.response
    from gluon.contenttype import contenttype
    response.headers['Content-Type'] = contenttype('.xls')
    disposition = ('attachment;    filename="%s"' % filename)
    response.headers['Content-disposition'] = disposition
    return output.read()
