addons/hr_timesheet_sheet/report/timesheet_report.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 import tools
  23 from osv import fields,osv
  24
  25 class timesheet_report(osv.osv):
  26     _name = "timesheet.report"
  27     _description = "Timesheet"
  28     _auto = False
  29     _columns = {
  30         'year': fields.char('Year',size=64,required=False, readonly=True),
  31         'month':fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
  32             ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
  33             ('10','October'), ('11','November'), ('12','December')], 'Month',readonly=True),
  34         'day': fields.char('Day', size=128, readonly=True),
  35         'name': fields.char('Description', size=64,readonly=True),
  36         'product_id' : fields.many2one('product.product', 'Product'),
  37         'general_account_id' : fields.many2one('account.account', 'General Account', readonly=True),
  38         'user_id': fields.many2one('res.users', 'User',readonly=True),
  39         'to_invoice': fields.many2one('hr_timesheet_invoice.factor', 'Type of Invoicing',readonly=True),
  40         'account_id': fields.many2one('account.analytic.account', 'Analytic Account',readonly=True),
  41         'nbr': fields.integer('#Nbr',readonly=True),
  42         'total_diff': fields.float('#Total Diff',readonly=True),
  43         'total_timesheet': fields.float('#Total Timesheet',readonly=True),
  44         'total_attendance': fields.float('#Total Attendance',readonly=True),
  45         'company_id': fields.many2one('res.company', 'Company',readonly=True),
  46         'department_id':fields.many2one('hr.department','Department',readonly=True),
  47         'date_from': fields.date('Date from',readonly=True,),
  48         'date_to': fields.date('Date to',readonly=True),
  49         'date_current': fields.date('Current date', required=True),
  50         'state' : fields.selection([
  51             ('new', 'New'),
  52             ('draft','Draft'),
  53             ('confirm','Confirmed'),
  54             ('done','Done')], 'State', readonly=True),
  55         'quantity': fields.float('#Quantity',readonly=True),
  56         'cost': fields.float('#Cost',readonly=True),
  57         }
  58
  59     def init(self, cr):
  60         tools.drop_view_if_exists(cr, 'timesheet_report')
  61         cr.execute("""
  62             create or replace view timesheet_report as (
  63                     select
  64                         min(aal.id) as id,
  65                         htss.date_current,
  66                         htss.name,
  67                         htss.date_from,
  68                         htss.date_to,
  69                         to_char(htss.date_current,'YYYY') as year,
  70                         to_char(htss.date_current,'MM') as month,
  71                         to_char(htss.date_current, 'YYYY-MM-DD') as day,
  72                         count(*) as nbr,
  73                         aal.unit_amount as quantity,
  74                         aal.amount as cost,
  75                         aal.account_id,
  76                         aal.product_id,
  77                         (SELECT   sum(day.total_difference)
  78                             FROM hr_timesheet_sheet_sheet AS sheet
  79                             LEFT JOIN hr_timesheet_sheet_sheet_day AS day
  80                             ON (sheet.id = day.sheet_id
  81                             AND day.name = sheet.date_current) where sheet.id=htss.id) as total_diff,
  82                         (SELECT sum(day.total_timesheet)
  83                             FROM hr_timesheet_sheet_sheet AS sheet
  84                             LEFT JOIN hr_timesheet_sheet_sheet_day AS day
  85                             ON (sheet.id = day.sheet_id
  86                             AND day.name = sheet.date_current) where sheet.id=htss.id) as total_timesheet,
  87                         (SELECT sum(day.total_attendance)
  88                             FROM hr_timesheet_sheet_sheet AS sheet
  89                             LEFT JOIN hr_timesheet_sheet_sheet_day AS day
  90                             ON (sheet.id = day.sheet_id
  91                             AND day.name = sheet.date_current) where sheet.id=htss.id) as total_attendance,
  92                         aal.to_invoice,
  93                         aal.general_account_id,
  94                         htss.user_id,
  95                         htss.company_id,
  96                         htss.department_id,
  97                         htss.state
  98                     from account_analytic_line as aal
  99                     left join hr_analytic_timesheet as hat ON (hat.line_id=aal.id)
 100                     left join hr_timesheet_sheet_sheet as htss ON (hat.line_id=htss.id)
 101                     group by
 102                         to_char(htss.date_current,'YYYY'),
 103                         to_char(htss.date_current,'MM'),
 104                         to_char(htss.date_current, 'YYYY-MM-DD'),
 105                         aal.account_id,
 106                         htss.date_from,
 107                         htss.date_to,
 108                         aal.unit_amount,
 109                         aal.amount,
 110                         htss.date_current,
 111                         aal.to_invoice,
 112                         aal.product_id,
 113                         aal.general_account_id,
 114                         htss.name,
 115                         htss.company_id,
 116                         htss.state,
 117                         htss.id,
 118                         htss.department_id,
 119                         htss.user_id
 120             )
 121         """)
 122 timesheet_report()