← 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/bymonth.py

merging new development from indian accounting

Show diffs side-by-side

added added

removed removed

Lines of Context:
payroll/payroll-4.0.3/report/bymonth.py
 
1
# -*- encoding: utf-8 -*-
1
2
##############################################################################
2
3
#
3
4
# Copyright (c) 2005-2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
39
40
month2name = [0,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
40
41
 
41
42
def hour2str(h):
42
 
        hours = int(h)
43
 
        minutes = int(round((h - hours) * 60, 0))
44
 
        return '%02dh%02d' % (hours, minutes)
 
43
    hours = int(h)
 
44
    minutes = int(round((h - hours) * 60, 0))
 
45
    return '%02dh%02d' % (hours, minutes)
45
46
 
46
47
class report_custom(report_rml):
47
 
        def create_xml(self, cr, uid, ids, datas, context):
48
 
                service = netsvc.LocalService('object_proxy')
49
 
 
50
 
                month = DateTime.DateTime(datas['form']['year'], datas['form']['month'], 1)
51
 
                
52
 
                user_xml = ['<month>%s</month>' % month2name[month.month], '<year>%s</year>' % month.year]
53
 
                
54
 
                # Public holidays
55
 
                jf_sql = """select hol.date_from, hol.date_to from hr_holidays as hol, hr_holidays_status as stat
56
 
                                        where hol.holiday_status = stat.id and stat.name = 'Public holidays' """
57
 
                cr.execute(jf_sql)
58
 
                jfs = []
59
 
                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()]
60
 
                
61
 
                for employee_id in ids:
62
 
                        emp = service.execute(cr.dbname, uid, 'hr.employee', 'read', [employee_id])[0]
63
 
                        stop, days_xml = False, []
64
 
                        user_repr = '''
65
 
                        <user>
66
 
                          <name>%s</name>
67
 
                          <regime>%s</regime>
68
 
                          <holiday>%s</holiday>
69
 
                          %%s
70
 
                        </user>
71
 
                        ''' % (toxml(emp['name']),emp['regime'],emp['holiday_max'])
72
 
                        today, tomor = month, month + one_day
73
 
                        while today.month == month.month:
74
 
                                #### Work hour calculation
75
 
                                sql = '''
76
 
                                select action, att.name
77
 
                                from hr_employee as emp inner join hr_attendance as att
78
 
                                     on emp.id = att.employee_id
79
 
                                where att.name between '%s' and '%s' and emp.id = %s
80
 
                                order by att.name
81
 
                                '''
82
 
                                cr.execute(sql, (today, tomor, employee_id))
83
 
                                attendences = cr.dictfetchall()
84
 
                                wh = 0
85
 
                                if attendences and attendences[0]['action'] == 'sign_out':
86
 
                                        attendences.insert(0, {'name': today.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_in'})
87
 
                                if attendences and attendences[-1]['action'] == 'sign_in':
88
 
                                        attendences.append({'name' : tomor.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_out'})
89
 
                                for att in attendences:
90
 
                                        dt = DateTime.strptime(att['name'], '%Y-%m-%d %H:%M:%S')
91
 
                                        if att['action'] == 'sign_out':
92
 
                                                wh += (dt - ldt).hours
93
 
                                        ldt = dt
94
 
 
95
 
                                #### Theoretical workhour calculation
96
 
                                sql = '''
97
 
                                select t.hour_from, t.hour_to
98
 
                                from hr_timesheet as t
99
 
                                         inner join (hr_timesheet_group as g inner join hr_timesheet_employee_rel as rel
100
 
                                                         on rel.tgroup_id = g.id and rel.emp_id = %s)
101
 
                                         on t.tgroup_id = g.id
102
 
                                where dayofweek = %s 
103
 
                                          and date_from = (select max(date_from) 
104
 
                                                                           from hr_timesheet inner join (hr_timesheet_employee_rel 
105
 
                                                                                                                                                inner join hr_timesheet_group 
106
 
                                                                                                                                                on hr_timesheet_group.id = hr_timesheet_employee_rel.tgroup_id
107
 
                                                                                                                                                        and hr_timesheet_employee_rel.emp_id = %s)
108
 
                                                                                                                 on hr_timesheet.tgroup_id = hr_timesheet_group.id
109
 
                                                                           where dayofweek = %s and date_from <= '%s') 
110
 
                                order by date_from desc
111
 
                                '''
112
 
                                isPH = False
113
 
                                for jf_start, jf_end in jfs:
114
 
                                        if jf_start <= today <= jf_end:
115
 
                                                isPH = True
116
 
                                                break
117
 
                                if isPH:
118
 
                                        twh = 0
119
 
                                else:
120
 
                                        cr.execute(sql, (emp['id'], today.day_of_week, emp['id'], today.day_of_week, today))
121
 
                                        ths = cr.dictfetchall()
122
 
                                        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)
123
 
 
124
 
                                #### Holiday calculation
125
 
                                hh = 0
126
 
                                sql = '''
127
 
                                select hol.date_from, hol.date_to, stat.name as status
128
 
                                from hr_employee as emp 
129
 
                                         inner join (hr_holidays as hol left join hr_holidays_status as stat
130
 
                                                     on hol.holiday_status = stat.id)
131
 
                                     on emp.id = hol.employee_id
132
 
                                where ((hol.date_from <= '%s' and hol.date_to >= '%s') 
133
 
                                       or (hol.date_from < '%s' and hol.date_to >= '%s')
134
 
                                           or (hol.date_from > '%s' and hol.date_to < '%s'))
135
 
                                          and emp.id = %s
136
 
                                order by hol.date_from
137
 
                                '''
138
 
                                cr.execute(sql, (today, today, tomor, tomor, today, tomor, employee_id))
139
 
                                holidays = cr.dictfetchall()
140
 
                                for hol in holidays:
141
 
                                        df = DateTime.strptime(hol['date_from'], '%Y-%m-%d %H:%M:%S')
142
 
                                        dt = DateTime.strptime(hol['date_to'], '%Y-%m-%d %H:%M:%S')
143
 
                                        if (df.year, df.month, df.day) <= (today.year, today.month, today.day) <= (dt.year, dt.month, dt.day):
144
 
                                                if (df.year, df.month, df.day) == (dt.year, dt.month, dt.day):
145
 
                                                        hh += (dt - df).hours
146
 
                                                else:
147
 
                                                        hh = twh
148
 
                                
149
 
                                # Week xml representation
150
 
                                twh, wh, hh = map(hour2str, (twh, wh, hh))
151
 
                                today_xml = '<day num="%s"><th>%s</th><wh>%s</wh><hh>%s</hh></day>' % ((today - month).days+1, twh, wh, hh)
152
 
                                days_xml.append(today_xml)
153
 
                                today, tomor = tomor, tomor + one_day
154
 
                                
155
 
                        user_xml.append(user_repr % '\n'.join(days_xml))
156
 
                
157
 
                xml = '''<?xml version="1.0" encoding="UTF-8" ?>
158
 
                <report>
159
 
                %s
160
 
                </report>
161
 
                ''' % '\n'.join(user_xml)
162
 
 
163
 
                return xml
 
48
    def create_xml(self, cr, uid, ids, datas, context):
 
49
        service = netsvc.LocalService('object_proxy')
 
50
 
 
51
        month = DateTime.DateTime(datas['form']['year'], datas['form']['month'], 1)
 
52
        
 
53
        user_xml = ['<month>%s</month>' % month2name[month.month], '<year>%s</year>' % month.year]
 
54
        
 
55
        # Public holidays
 
56
        jf_sql = """select hol.date_from, hol.date_to from hr_holidays as hol, hr_holidays_status as stat
 
57
                    where hol.holiday_status = stat.id and stat.name = 'Public holidays' """
 
58
        cr.execute(jf_sql)
 
59
        jfs = []
 
60
        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()]
 
61
        
 
62
        for employee_id in ids:
 
63
            emp = service.execute(cr.dbname, uid, 'hr.employee', 'read', [employee_id])[0]
 
64
            stop, days_xml = False, []
 
65
            user_repr = '''
 
66
            <user>
 
67
              <name>%s</name>
 
68
              <regime>%s</regime>
 
69
              <holiday>%s</holiday>
 
70
              %%s
 
71
            </user>
 
72
            ''' % (toxml(emp['name']),emp['regime'],emp['holiday_max'])
 
73
            today, tomor = month, month + one_day
 
74
            while today.month == month.month:
 
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
                cr.execute(sql, (today, tomor, employee_id))
 
84
                attendences = cr.dictfetchall()
 
85
                wh = 0
 
86
                if attendences and attendences[0]['action'] == 'sign_out':
 
87
                    attendences.insert(0, {'name': today.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_in'})
 
88
                if attendences and attendences[-1]['action'] == 'sign_in':
 
89
                    attendences.append({'name' : tomor.strftime('%Y-%m-%d %H:%M:%S'), 'action':'sign_out'})
 
90
                for att in attendences:
 
91
                    dt = DateTime.strptime(att['name'], '%Y-%m-%d %H:%M:%S')
 
92
                    if att['action'] == 'sign_out':
 
93
                        wh += (dt - ldt).hours
 
94
                    ldt = dt
 
95
 
 
96
                #### Theoretical workhour calculation
 
97
                sql = '''
 
98
                select t.hour_from, t.hour_to
 
99
                from hr_timesheet as t
 
100
                     inner join (hr_timesheet_group as g inner join hr_timesheet_employee_rel as rel
 
101
                                 on rel.tgroup_id = g.id and rel.emp_id = %s)
 
102
                     on t.tgroup_id = g.id
 
103
                where dayofweek = %s 
 
104
                      and date_from = (select max(date_from) 
 
105
                                       from hr_timesheet inner join (hr_timesheet_employee_rel 
 
106
                                                                        inner join hr_timesheet_group 
 
107
                                                                        on hr_timesheet_group.id = hr_timesheet_employee_rel.tgroup_id
 
108
                                                                            and hr_timesheet_employee_rel.emp_id = %s)
 
109
                                                         on hr_timesheet.tgroup_id = hr_timesheet_group.id
 
110
                                       where dayofweek = %s and date_from <= '%s') 
 
111
                order by date_from desc
 
112
                '''
 
113
                isPH = False
 
114
                for jf_start, jf_end in jfs:
 
115
                    if jf_start <= today <= jf_end:
 
116
                        isPH = True
 
117
                        break
 
118
                if isPH:
 
119
                    twh = 0
 
120
                else:
 
121
                    cr.execute(sql, (emp['id'], today.day_of_week, emp['id'], today.day_of_week, today))
 
122
                    ths = cr.dictfetchall()
 
123
                    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)
 
124
 
 
125
                #### Holiday calculation
 
126
                hh = 0
 
127
                sql = '''
 
128
                select hol.date_from, hol.date_to, stat.name as status
 
129
                from hr_employee as emp 
 
130
                     inner join (hr_holidays as hol left join hr_holidays_status as stat
 
131
                                 on hol.holiday_status = stat.id)
 
132
                     on emp.id = hol.employee_id
 
133
                where ((hol.date_from <= '%s' and hol.date_to >= '%s') 
 
134
                       or (hol.date_from < '%s' and hol.date_to >= '%s')
 
135
                       or (hol.date_from > '%s' and hol.date_to < '%s'))
 
136
                      and emp.id = %s
 
137
                order by hol.date_from
 
138
                '''
 
139
                cr.execute(sql, (today, today, tomor, tomor, today, tomor, employee_id))
 
140
                holidays = cr.dictfetchall()
 
141
                for hol in holidays:
 
142
                    df = DateTime.strptime(hol['date_from'], '%Y-%m-%d %H:%M:%S')
 
143
                    dt = DateTime.strptime(hol['date_to'], '%Y-%m-%d %H:%M:%S')
 
144
                    if (df.year, df.month, df.day) <= (today.year, today.month, today.day) <= (dt.year, dt.month, dt.day):
 
145
                        if (df.year, df.month, df.day) == (dt.year, dt.month, dt.day):
 
146
                            hh += (dt - df).hours
 
147
                        else:
 
148
                            hh = twh
 
149
                
 
150
                # Week xml representation
 
151
                twh, wh, hh = map(hour2str, (twh, wh, hh))
 
152
                today_xml = '<day num="%s"><th>%s</th><wh>%s</wh><hh>%s</hh></day>' % ((today - month).days+1, twh, wh, hh)
 
153
                days_xml.append(today_xml)
 
154
                today, tomor = tomor, tomor + one_day
 
155
                
 
156
            user_xml.append(user_repr % '\n'.join(days_xml))
 
157
        
 
158
        xml = '''<?xml version="1.0" encoding="UTF-8" ?>
 
159
        <report>
 
160
        %s
 
161
        </report>
 
162
        ''' % '\n'.join(user_xml)
 
163
 
 
164
        return xml
164
165
 
165
166
report_custom('report.hr.timesheet.bymonth', 'hr.employee', '', 'addons/hr/report/bymonth.xsl')
166
 
# vim:noexpandtab:tw=0
 
167
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 
168