Redmine

Just adding this two functions to a controller and just adding a link to getxls
and you get an excel file with all issues for a particular month with all the spent time, projects, and user. Works
fine in Postgresql. You need spreadsheet-excel gem.

def getxls
      gen_xls
      send_file '/tmp/redmine_time.xls', :type => 'application/vnd.ms-excel', :filename => 'redmine.xls'
  end

def gen_xls

month = Time.now.strftime("%m")
      year = Time.now.strftime("%Y")

query = "    
        SELECT 
            DISTINCT ON (time_entries.issue_id)
            time_entries.tmonth
            , time_entries.tyear
            , users.login
            , projects.name as project_name
            , issues.subject
            , (
                SELECT 
                    SUM(hours) AS sp 
                FROM 
                    time_entries AS te 
                WHERE 
                    te.issue_id = time_entries.issue_id 
                AND te.tmonth = " + month.to_s + " 
                AND te.tyear = " + year.to_s + " 
            ) AS total_hours
            , time_entries.issue_id
        FROM 
            time_entries
            , issues
            , users
            , projects
        WHERE 
            time_entries.issue_id = issues.id
        AND
            users.id = time_entries.user_id
        AND
            projects.id = time_entries.project_id
        AND
            time_entries.tmonth = " + month.to_s + " 
        AND 
            time_entries.tyear = " + year.to_s + ";
    "

result = TimeEntry.find_by_sql(query)

wks = Excel.new('/tmp/redmine_time.xls')
    wsh = wks.add_worksheet
    x = 0
    for k in result

wsh.write(x,0, k.tmonth)
        wsh.write(x,1, k.tyear)
        wsh.write(x,2, k.login)
        wsh.write(x,3, k.project_name)
        wsh.write(x,4, k.subject)
        wsh.write(x,5, k.total_hours.sub(/\./, ','))
        wsh.write(x,6, k.issue_id)

x = x + 1
    end

wks.close
  end