addons/hr/report/bymonth.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2008 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   7 #
   8 #    This program is free software: you can redistribute it and/or modify
   9 #    it under the terms of the GNU General Public License as published by
  10 #    the Free Software Foundation, either version 3 of the License, or
  11 #    (at your option) any later version.
  12 #
  13 #    This program is distributed in the hope that it will be useful,
  14 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 #    GNU General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 from mx import DateTime
  24 from mx.DateTime import now
  25 import time
  26
  27 import netsvc
  28 import pooler
  29
  30 from report.interface import report_rml
  31 from report.interface import toxml
  32
  33 one_day = DateTime.RelativeDateTime(days=1)
  34 month2name = [0,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
  35
  36 def hour2str(h):
  37     hours = int(h)
  38     minutes = int(round((h - hours) * 60, 0))
  39     return '%02dh%02d' % (hours, minutes)
  40
  41 class report_custom(report_rml):
  42     def create_xml(self, cr, uid, ids, datas, context):
  43         service = netsvc.LocalService('object_proxy')
  44
  45         month = DateTime.DateTime(datas['form']['year'], datas['form']['month'], 1)
  46
  47         user_xml = ['<month>%s</month>' % month2name[month.month], '<year>%s</year>' % month.year]
  48
  49         # Public holidays
  50         jf_sql = """select hol.date_from, hol.date_to from hr_holidays as hol, hr_holidays_status as stat
  51                     where hol.holiday_status = stat.id and stat.name = 'Public holidays' """
  52         cr.execute(jf_sql)
  53         jfs = []
  54         jfs = [(DateTime.strptime(l['date_from'], '%Y-%m-%d %H:%M:%S'), DateTime.strptime(l['date_to'], '%Y-%m-%d %H:%M:%S')) for l in cr.dictfetchall()]
  55
  56         for employee_id in ids:
  57             emp = service.execute(cr.dbname, uid, 'hr.employee', 'read', [employee_id])[0]
  58             stop, days_xml = False, []
  59             user_repr = '''
  60             <user>
  61               <name>%s</name>
  62               <regime>%s</regime>
  63               <holiday>%s</holiday>
  64               %%s
  65             </user>
  66             ''' % (toxml(emp['name']),emp['regime'],emp['holiday_max'])
  67             today, tomor = month, month + one_day
  68             while today.month == month.month:
  69                 #### Work hour calculation
  70                 sql = '''
  71                 select action, att.name
  72                 from hr_employee as emp inner join hr_attendance as att
  73                      on emp.id = att.employee_id
  74                 where att.name between '%s' and '%s' and emp.id = %s
  75                 order by att.name
  76                 '''
  77                 cr.execute(sql, (today, tomor, employee_id))
  78                 attendences = cr.dictfetchall()
  79                 wh = 0
  80                 if attendences and attendences[0]['action'] == 'sign_out':
  81                     attendences.insert(0, {'name': today.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_in'})
  82                 if attendences and attendences[-1]['action'] == 'sign_in':
  83                     attendences.append({'name' : tomor.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_out'})
  84                 for att in attendences:
  85                     dt = DateTime.strptime(att['name'], '%Y-%m-%d %H:%M:%S')
  86                     if att['action'] == 'sign_out':
  87                         wh += (dt - ldt).hours
  88                     ldt = dt
  89
  90                 #### Theoretical workhour calculation
  91                 sql = '''
  92                 select t.hour_from, t.hour_to
  93                 from hr_timesheet as t
  94                      inner join (hr_timesheet_group as g inner join hr_timesheet_employee_rel as rel
  95                                  on rel.tgroup_id = g.id and rel.emp_id = %s)
  96                      on t.tgroup_id = g.id
  97                 where dayofweek = '%s'
  98                       and date_from = (select max(date_from)
  99                                        from hr_timesheet inner join (hr_timesheet_employee_rel
 100                                                                         inner join hr_timesheet_group
 101                                                                         on hr_timesheet_group.id = hr_timesheet_employee_rel.tgroup_id
 102                                                                             and hr_timesheet_employee_rel.emp_id = %s)
 103                                                          on hr_timesheet.tgroup_id = hr_timesheet_group.id
 104                                        where dayofweek = '%s' and date_from <= '%s')
 105                 order by date_from desc
 106                 '''
 107                 isPH = False
 108                 for jf_start, jf_end in jfs:
 109                     if jf_start <= today <= jf_end:
 110                         isPH = True
 111                         break
 112                 if isPH:
 113                     twh = 0
 114                 else:
 115                     cr.execute(sql, (emp['id'], today.day_of_week, emp['id'], today.day_of_week, today))
 116                     ths = cr.dictfetchall()
 117                     twh = reduce(lambda x,y:x+(DateTime.strptime(y['hour_to'], '%H:%M:%S') - DateTime.strptime(y['hour_from'], '%H:%M:%S')).hours,ths, 0)
 118
 119                 #### Holiday calculation
 120                 hh = 0
 121                 sql = '''
 122                 select hol.date_from, hol.date_to, stat.name as status
 123                 from hr_employee as emp
 124                      inner join (hr_holidays as hol left join hr_holidays_status as stat
 125                                  on hol.holiday_status = stat.id)
 126                      on emp.id = hol.employee_id
 127                 where ((hol.date_from <= '%s' and hol.date_to >= '%s')
 128                        or (hol.date_from < '%s' and hol.date_to >= '%s')
 129                        or (hol.date_from > '%s' and hol.date_to < '%s'))
 130                       and emp.id = %s
 131                 order by hol.date_from
 132                 '''
 133                 cr.execute(sql, (today, today, tomor, tomor, today, tomor, employee_id))
 134                 holidays = cr.dictfetchall()
 135                 for hol in holidays:
 136                     df = DateTime.strptime(hol['date_from'], '%Y-%m-%d %H:%M:%S')
 137                     dt = DateTime.strptime(hol['date_to'], '%Y-%m-%d %H:%M:%S')
 138                     if (df.year, df.month, df.day) <= (today.year, today.month, today.day) <= (dt.year, dt.month, dt.day):
 139                         if (df.year, df.month, df.day) == (dt.year, dt.month, dt.day):
 140                             hh += (dt - df).hours
 141                         else:
 142                             hh = twh
 143
 144                 # Week xml representation
 145                 twh, wh, hh = map(hour2str, (twh, wh, hh))
 146                 today_xml = '<day num="%s"><th>%s</th><wh>%s</wh><hh>%s</hh></day>' % ((today - month).days+1, twh, wh, hh)
 147                 days_xml.append(today_xml)
 148                 today, tomor = tomor, tomor + one_day
 149
 150             user_xml.append(user_repr % '\n'.join(days_xml))
 151
 152         xml = '''<?xml version="1.0" encoding="UTF-8" ?>
 153         <report>
 154         %s
 155         </report>
 156         ''' % '\n'.join(user_xml)
 157
 158         return xml
 159
 160 report_custom('report.hr.timesheet.bymonth', 'hr.employee', '', 'addons/hr/report/bymonth.xsl')
 161
 162 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 163