← Back to branch summary

~openerp-commiter/openobject-addons/trunk-extra-addons

« back to all changes in this revision

Viewing changes to payroll/payroll-4.0.3/report/timesheet.py

bugfix in overlay creation system

Show diffs side-by-side

added added

removed removed

Lines of Context:
payroll/payroll-4.0.3/report/timesheet.py
 
1
# -*- encoding: utf-8 -*-
1
2
##############################################################################
2
3
#
3
4
# Copyright (c) 2005 TINY SPRL. (http://tiny.be) All Rights Reserved.
39
40
num2day = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
40
41
 
41
42
def to_hour(h):
42
 
        return int(h), int(round((h - int(h)) * 60, 0))
 
43
    return int(h), int(round((h - int(h)) * 60, 0))
43
44
 
44
45
class report_custom(report_rml):
45
 
        def create_xml(self, cr, uid, ids, datas, context):
46
 
                service = netsvc.LocalService('object_proxy')
47
 
 
48
 
                start_date = DateTime.strptime(datas['form']['init_date'], '%Y-%m-%d')
49
 
                end_date = DateTime.strptime(datas['form']['end_date'], '%Y-%m-%d')
50
 
                first_monday = start_date - DateTime.RelativeDateTime(days=start_date.day_of_week)
51
 
                last_monday = end_date + DateTime.RelativeDateTime(days=7 - end_date.day_of_week)
52
 
 
53
 
                if last_monday < first_monday:
54
 
                        first_monday, last_monday = last_monday, first_monday
55
 
 
56
 
                user_xml = []
57
 
                
58
 
                jf_sql = """select hol.date_from, hol.date_to from hr_holidays as hol, hr_holidays_status as stat
59
 
                                        where hol.holiday_status = stat.id and stat.name = 'Public holidays' """
60
 
                cr.execute(jf_sql)
61
 
                jfs = []
62
 
                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()]
63
 
                
64
 
                for employee_id in ids:
65
 
                        emp = service.execute(cr.dbname, uid, 'hr.employee', 'read', [employee_id], ['id', 'name'])[0]
66
 
                        monday, n_monday = first_monday, first_monday + one_week
67
 
                        stop, week_xml = False, []
68
 
                        user_repr = '''
69
 
                        <user>
70
 
                          <name>%s</name>
71
 
                          %%s
72
 
                        </user>
73
 
                        ''' % toxml(emp['name'])
74
 
                        while monday != last_monday:
75
 
                                #### Work hour calculation
76
 
                                sql = '''
77
 
                                select action, att.name
78
 
                                from hr_employee as emp inner join hr_attendance as att
79
 
                                     on emp.id = att.employee_id
80
 
                                where att.name between '%s' and '%s' and emp.id = %s
81
 
                                order by att.name
82
 
                                '''
83
 
                                for idx in range(7):
84
 
                                        cr.execute(sql, (monday, monday + DateTime.RelativeDateTime(days=idx+1), employee_id))
85
 
                                        attendences = cr.dictfetchall()
86
 
                                        week_wh = {}
87
 
                                        if attendences and attendences[0]['action'] == 'sign_out':
88
 
                                                attendences.insert(0, {'name': monday.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_in'})
89
 
                                        if attendences and attendences[-1]['action'] == 'sign_in':
90
 
                                                attendences.append({'name' : n_monday.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_out'})
91
 
                                        for att in attendences:
92
 
                                                dt = DateTime.strptime(att['name'], '%Y-%m-%d %H:%M:%S')
93
 
                                                if att['action'] == 'sign_out':
94
 
                                                        week_wh[ldt.day_of_week] = week_wh.get(ldt.day_of_week, 0) + (dt - ldt).hours
95
 
                                                ldt = dt
96
 
 
97
 
                                #### Theoretical workhour calculation
98
 
                                week_twh = {}
99
 
                                sql = '''
100
 
                                select t.hour_from, t.hour_to
101
 
                                from hr_timesheet as t
102
 
                                         inner join (hr_timesheet_group as g inner join hr_timesheet_employee_rel as rel
103
 
                                                         on rel.tgroup_id = g.id and rel.emp_id = %s)
104
 
                                         on t.tgroup_id = g.id
105
 
                                where dayofweek = %s 
106
 
                                          and date_from = (select max(date_from) 
107
 
                                                                           from hr_timesheet inner join (hr_timesheet_employee_rel 
108
 
                                                                                                                                                inner join hr_timesheet_group 
109
 
                                                                                                                                                on hr_timesheet_group.id = hr_timesheet_employee_rel.tgroup_id
110
 
                                                                                                                                                        and hr_timesheet_employee_rel.emp_id = %s)
111
 
                                                                                                                 on hr_timesheet.tgroup_id = hr_timesheet_group.id
112
 
                                                                           where dayofweek = %s and date_from <= '%s') 
113
 
                                order by date_from desc
114
 
                                '''
115
 
                                for idx in range(7):
116
 
                                        day = monday + DateTime.RelativeDateTime(days=idx+1)
117
 
                                        # Is this a public holiday ?
118
 
                                        isPH = False
119
 
                                        for jf_start, jf_end in jfs:
120
 
                                                if jf_start <= day < jf_end:
121
 
                                                        isPH = True
122
 
                                                        break
123
 
                                        if isPH:
124
 
                                                week_twh[idx] = 0
125
 
                                        else:
126
 
                                                cr.execute(sql, (emp['id'], day.day_of_week, emp['id'], day.day_of_week, day))
127
 
                                                dhs = cr.dictfetchall()
128
 
                                                week_twh[idx] = reduce(lambda x,y:x+(DateTime.strptime(y['hour_to'], '%H:%M:%S') - DateTime.strptime(y['hour_from'], '%H:%M:%S')).hours,dhs, 0)
129
 
 
130
 
                                #### Holiday calculation
131
 
                                sql = '''
132
 
                                select hol.date_from, hol.date_to, stat.name as status
133
 
                                from hr_employee as emp 
134
 
                                         inner join (hr_holidays as hol left join hr_holidays_status as stat
135
 
                                                     on hol.holiday_status = stat.id)
136
 
                                     on emp.id = hol.employee_id
137
 
                                where ((hol.date_from <= '%s' and hol.date_to >= '%s') 
138
 
                                        or (hol.date_from < '%s' and hol.date_to >= '%s')
139
 
                                            or (hol.date_from > '%s' and hol.date_to < '%s')
140
 
                                           and stat.name != 'Public holidays') and emp.id = %s
141
 
                                order by hol.date_from
142
 
                                '''
143
 
                                cr.execute(sql, (monday, monday, n_monday, n_monday, monday, n_monday, employee_id))
144
 
                                holidays = cr.dictfetchall()
145
 
                                week_hol = {}
146
 
                                for hol in holidays:
147
 
                                        df = DateTime.strptime(hol['date_from'], '%Y-%m-%d %H:%M:%S')
148
 
                                        dt = DateTime.strptime(hol['date_to'], '%Y-%m-%d %H:%M:%S')
149
 
                                        for idx in range(7):
150
 
                                                day = monday + DateTime.RelativeDateTime(days=idx+1)
151
 
                                                if (df.year, df.month, df.day) <= (day.year, day.month, day.day) <= (dt.year, dt.month, dt.day):
152
 
                                                        if (df.year, df.month, df.day) == (dt.year, dt.month, dt.day):
153
 
                                                                week_hol[idx] = {'status' : hol['status'], 'hours' : (dt - df).hours}
154
 
                                                        else:
155
 
                                                                week_hol[idx] = {'status' : hol['status'], 'hours' : week_twh[idx]}
156
 
                                
157
 
                                # Week xml representation
158
 
                                week_repr = ['<week>', '<weekstart>%s</weekstart>' % monday.strftime('%Y-%m-%d'), '<weekend>%s</weekend>' % n_monday.strftime('%Y-%m-%d')]
159
 
                                for idx in range(7):
160
 
                                        week_repr.append('<%s>' % num2day[idx])
161
 
                                        week_repr.append('<theoretical>%sh%02d</theoretical>' % to_hour(week_twh[idx]))
162
 
                                        if idx in week_wh:
163
 
                                                week_repr.append('<workhours>%sh%02d</workhours>' % to_hour(week_wh[idx]))
164
 
                                        if idx in week_hol and week_hol[idx]['hours']:
165
 
                                                week_repr.append('<holidayhours type="%(status)s">%(hours)s</holidayhours>' % week_hol[idx])
166
 
                                        week_repr.append('</%s>' % num2day[idx])
167
 
                                week_repr.append('<total>')
168
 
                                week_repr.append('<theoretical>%sh%02d</theoretical>' % to_hour(reduce(lambda x,y:x+y, week_twh.values(), 0)))
169
 
                                week_repr.append('<worked>%sh%02d</worked>' % to_hour(reduce(lambda x,y:x+y, week_wh.values(), 0)))
170
 
                                week_repr.append('<holiday>%sh%02d</holiday>' % to_hour(reduce(lambda x,y:x+y, [day['hours'] for day in week_hol.values()], 0)))
171
 
                                week_repr.append('</total>')
172
 
                                week_repr.append('</week>')
173
 
                                if len(week_repr) > 30: # 30 = minimal length of week_repr
174
 
                                        week_xml.append('\n'.join(week_repr))
175
 
                                
176
 
                                monday, n_monday = n_monday, n_monday + one_week
177
 
                        user_xml.append(user_repr % '\n'.join(week_xml))
178
 
                
179
 
                xml = '''<?xml version="1.0" encoding="UTF-8" ?>
180
 
                <report>
181
 
                %s
182
 
                </report>
183
 
                ''' % '\n'.join(user_xml)
184
 
                return self.post_process_xml_data(cr, uid, xml, context)
 
46
    def create_xml(self, cr, uid, ids, datas, context):
 
47
        service = netsvc.LocalService('object_proxy')
 
48
 
 
49
        start_date = DateTime.strptime(datas['form']['init_date'], '%Y-%m-%d')
 
50
        end_date = DateTime.strptime(datas['form']['end_date'], '%Y-%m-%d')
 
51
        first_monday = start_date - DateTime.RelativeDateTime(days=start_date.day_of_week)
 
52
        last_monday = end_date + DateTime.RelativeDateTime(days=7 - end_date.day_of_week)
 
53
 
 
54
        if last_monday < first_monday:
 
55
            first_monday, last_monday = last_monday, first_monday
 
56
 
 
57
        user_xml = []
 
58
        
 
59
        jf_sql = """select hol.date_from, hol.date_to from hr_holidays as hol, hr_holidays_status as stat
 
60
                    where hol.holiday_status = stat.id and stat.name = 'Public holidays' """
 
61
        cr.execute(jf_sql)
 
62
        jfs = []
 
63
        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()]
 
64
        
 
65
        for employee_id in ids:
 
66
            emp = service.execute(cr.dbname, uid, 'hr.employee', 'read', [employee_id], ['id', 'name'])[0]
 
67
            monday, n_monday = first_monday, first_monday + one_week
 
68
            stop, week_xml = False, []
 
69
            user_repr = '''
 
70
            <user>
 
71
              <name>%s</name>
 
72
              %%s
 
73
            </user>
 
74
            ''' % toxml(emp['name'])
 
75
            while monday != last_monday:
 
76
                #### Work hour calculation
 
77
                sql = '''
 
78
                select action, att.name
 
79
                from hr_employee as emp inner join hr_attendance as att
 
80
                     on emp.id = att.employee_id
 
81
                where att.name between '%s' and '%s' and emp.id = %s
 
82
                order by att.name
 
83
                '''
 
84
                for idx in range(7):
 
85
                    cr.execute(sql, (monday, monday + DateTime.RelativeDateTime(days=idx+1), employee_id))
 
86
                    attendences = cr.dictfetchall()
 
87
                    week_wh = {}
 
88
                    if attendences and attendences[0]['action'] == 'sign_out':
 
89
                        attendences.insert(0, {'name': monday.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_in'})
 
90
                    if attendences and attendences[-1]['action'] == 'sign_in':
 
91
                        attendences.append({'name' : n_monday.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_out'})
 
92
                    for att in attendences:
 
93
                        dt = DateTime.strptime(att['name'], '%Y-%m-%d %H:%M:%S')
 
94
                        if att['action'] == 'sign_out':
 
95
                            week_wh[ldt.day_of_week] = week_wh.get(ldt.day_of_week, 0) + (dt - ldt).hours
 
96
                        ldt = dt
 
97
 
 
98
                #### Theoretical workhour calculation
 
99
                week_twh = {}
 
100
                sql = '''
 
101
                select t.hour_from, t.hour_to
 
102
                from hr_timesheet as t
 
103
                     inner join (hr_timesheet_group as g inner join hr_timesheet_employee_rel as rel
 
104
                                 on rel.tgroup_id = g.id and rel.emp_id = %s)
 
105
                     on t.tgroup_id = g.id
 
106
                where dayofweek = %s 
 
107
                      and date_from = (select max(date_from) 
 
108
                                       from hr_timesheet inner join (hr_timesheet_employee_rel 
 
109
                                                                        inner join hr_timesheet_group 
 
110
                                                                        on hr_timesheet_group.id = hr_timesheet_employee_rel.tgroup_id
 
111
                                                                            and hr_timesheet_employee_rel.emp_id = %s)
 
112
                                                         on hr_timesheet.tgroup_id = hr_timesheet_group.id
 
113
                                       where dayofweek = %s and date_from <= '%s') 
 
114
                order by date_from desc
 
115
                '''
 
116
                for idx in range(7):
 
117
                    day = monday + DateTime.RelativeDateTime(days=idx+1)
 
118
                    # Is this a public holiday ?
 
119
                    isPH = False
 
120
                    for jf_start, jf_end in jfs:
 
121
                        if jf_start <= day < jf_end:
 
122
                            isPH = True
 
123
                            break
 
124
                    if isPH:
 
125
                        week_twh[idx] = 0
 
126
                    else:
 
127
                        cr.execute(sql, (emp['id'], day.day_of_week, emp['id'], day.day_of_week, day))
 
128
                        dhs = cr.dictfetchall()
 
129
                        week_twh[idx] = reduce(lambda x,y:x+(DateTime.strptime(y['hour_to'], '%H:%M:%S') - DateTime.strptime(y['hour_from'], '%H:%M:%S')).hours,dhs, 0)
 
130
 
 
131
                #### Holiday calculation
 
132
                sql = '''
 
133
                select hol.date_from, hol.date_to, stat.name as status
 
134
                from hr_employee as emp 
 
135
                     inner join (hr_holidays as hol left join hr_holidays_status as stat
 
136
                                 on hol.holiday_status = stat.id)
 
137
                     on emp.id = hol.employee_id
 
138
                where ((hol.date_from <= '%s' and hol.date_to >= '%s') 
 
139
                        or (hol.date_from < '%s' and hol.date_to >= '%s')
 
140
                        or (hol.date_from > '%s' and hol.date_to < '%s')
 
141
                       and stat.name != 'Public holidays') and emp.id = %s
 
142
                order by hol.date_from
 
143
                '''
 
144
                cr.execute(sql, (monday, monday, n_monday, n_monday, monday, n_monday, employee_id))
 
145
                holidays = cr.dictfetchall()
 
146
                week_hol = {}
 
147
                for hol in holidays:
 
148
                    df = DateTime.strptime(hol['date_from'], '%Y-%m-%d %H:%M:%S')
 
149
                    dt = DateTime.strptime(hol['date_to'], '%Y-%m-%d %H:%M:%S')
 
150
                    for idx in range(7):
 
151
                        day = monday + DateTime.RelativeDateTime(days=idx+1)
 
152
                        if (df.year, df.month, df.day) <= (day.year, day.month, day.day) <= (dt.year, dt.month, dt.day):
 
153
                            if (df.year, df.month, df.day) == (dt.year, dt.month, dt.day):
 
154
                                week_hol[idx] = {'status' : hol['status'], 'hours' : (dt - df).hours}
 
155
                            else:
 
156
                                week_hol[idx] = {'status' : hol['status'], 'hours' : week_twh[idx]}
 
157
                
 
158
                # Week xml representation
 
159
                week_repr = ['<week>', '<weekstart>%s</weekstart>' % monday.strftime('%Y-%m-%d'), '<weekend>%s</weekend>' % n_monday.strftime('%Y-%m-%d')]
 
160
                for idx in range(7):
 
161
                    week_repr.append('<%s>' % num2day[idx])
 
162
                    week_repr.append('<theoretical>%sh%02d</theoretical>' % to_hour(week_twh[idx]))
 
163
                    if idx in week_wh:
 
164
                        week_repr.append('<workhours>%sh%02d</workhours>' % to_hour(week_wh[idx]))
 
165
                    if idx in week_hol and week_hol[idx]['hours']:
 
166
                        week_repr.append('<holidayhours type="%(status)s">%(hours)s</holidayhours>' % week_hol[idx])
 
167
                    week_repr.append('</%s>' % num2day[idx])
 
168
                week_repr.append('<total>')
 
169
                week_repr.append('<theoretical>%sh%02d</theoretical>' % to_hour(reduce(lambda x,y:x+y, week_twh.values(), 0)))
 
170
                week_repr.append('<worked>%sh%02d</worked>' % to_hour(reduce(lambda x,y:x+y, week_wh.values(), 0)))
 
171
                week_repr.append('<holiday>%sh%02d</holiday>' % to_hour(reduce(lambda x,y:x+y, [day['hours'] for day in week_hol.values()], 0)))
 
172
                week_repr.append('</total>')
 
173
                week_repr.append('</week>')
 
174
                if len(week_repr) > 30: # 30 = minimal length of week_repr
 
175
                    week_xml.append('\n'.join(week_repr))
 
176
                
 
177
                monday, n_monday = n_monday, n_monday + one_week
 
178
            user_xml.append(user_repr % '\n'.join(week_xml))
 
179
        
 
180
        xml = '''<?xml version="1.0" encoding="UTF-8" ?>
 
181
        <report>
 
182
        %s
 
183
        </report>
 
184
        ''' % '\n'.join(user_xml)
 
185
        return self.post_process_xml_data(cr, uid, xml, context)
185
186
 
186
187
report_custom('report.hr.timesheet.allweeks', 'hr.employee', '', 'addons/hr/report/timesheet.xsl')
187
 
# vim:noexpandtab:tw=0
 
188
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 
189