With this particular function I am trying to process already uploaded raw attendance data into more informative way. Eg: late calculation, Regularize details etc. Also I am using this same function to update my attendance backup tables and making those attendance records against the calendar days by inserting those records to another table.
By executing this function whole ERP system getting slow (sometimes couldn't even login) and some random records are getting missed when this happens.
Please be kind enough and help me to solve this issue.
The proc - merge_db()
CREATE OR REPLACE FUNCTION merge_db()
RETURNS void AS
$BODY$
declare
r record;
recordCount integer;
inTime varchar(10);
outTime varchar(10);
mintime varchar(10);
maxtime varchar(10);
BEGIN
FOR r IN (select distinct name , to_char(date,'yy/mm/dd') as date ,late_check from text_based_attendance where status = 0 and date > CURRENT_TIMESTAMP - INTERVAL '20 days')
LOOP
select count(*) into recordCount
from attendance_processed a
where a.name = r.name and a.reldate = r.date ;
select to_char(min(a.date), 'HH24:MI'),
to_char(max(a.date), 'HH24:MI') into mintime,maxtime
from text_based_attendance a
where a.status = 0 and
a.name = r.name and to_char(a.date,'yy/mm/dd') = r.date;
if recordCount > 0 then
select sign_in , sign_out into inTime, outTime
from attendance_processed a
where a.name = r.name and a.reldate = r.date;
if to_timestamp(inTime,'HH24:MI') > to_timestamp(mintime,'HH24:MI') then
-- update signin
update attendance_processed a set sign_in = mintime
where a.name = r.name and a.reldate = r.date;
elsif to_timestamp(outTime,'HH24:MI') < to_timestamp(maxtime,'HH24:MI') then
-- update signout
update attendance_processed a set sign_out = maxtime
where a.name = r.name and a.reldate = r.date;
end if;
else
--insert
insert into attendance_processed
(name,reldate,sign_in,sign_out,late_check)
values (r.name,r.date,mintime,maxtime,r.late_check);
end if;
END LOOP;
--delete 62 days old records from text_based_attendance and backup it on text_based attancedance_backup table;
-- code written on python file hr_attendance.py
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION merge_db()
OWNER TO openerp;
The function
def text_files_process(self, cr, uid, ids, context=None):
search_condition1 = [('status', '=', 0)]
attendance_obj = self.pool.get('text.based.attendance')
attendance_ids = attendance_obj.search(cr, uid, search_condition1, context=context)
attendance_similar_objs = attendance_obj.browse(cr, uid, attendance_ids, context=context)
if attendance_similar_objs:
#Drop trigger if exists
cr.execute("""
UPDATE attendance_processed set cnt_update='',cnt_insert=''
""")
cr.execute("""
DROP TRIGGER IF EXISTS attendace_holidays_leave_update ON hr_holidays
""")
cr.execute("""
DROP TRIGGER IF EXISTS attendace_holidays_leave_insert ON hr_holidays
""")
cr.execute("""
DROP TRIGGER IF EXISTS attendace_holidays_update ON attendance_processed
""")
cr.execute("""
DROP TRIGGER IF EXISTS attendace_holidays_insert ON attendance_processed
""")
#Run the procediur
cr.callproc('merge_db')
for obj in attendance_similar_objs:
obj.write({'status': 1})
cr.execute(""" delete from attendance_processed where name is null """)
cr.execute(""" delete from attendance_logtrail where date < CURRENT_TIMESTAMP - INTERVAL '7 days """)
# to insert 65 days old data to text_based_attendace_backup table
cr.execute(""" insert into
text_based_attendance_backup(name,date,in_out,status,create_uid,create_date,write_uid,write_date,unique_record,
location_code,machine_code,deleted_date)
(select name,date,in_out,status,create_uid,create_date,write_uid,write_date,unique_record,
location_code,machine_code,current_date
from text_based_attendance t
where t.date <
CURRENT_TIMESTAMP - INTERVAL '65 days') """)
#to remove 65 days old data from text_based_attendace table
cr.execute(""" delete from text_based_attendance t
where t.date <
CURRENT_TIMESTAMP - INTERVAL '65 days' """)
#to convert char to date field
cr.execute("""
update attendance_processed a set date_calendar = to_date(a.reldate, 'YY-MM-DD')
where to_date(a.reldate,'YY-MM-DD') > CURRENT_TIMESTAMP - INTERVAL '60 days'
and a.date_calendar is null
""")
#new modification
cr.execute("""
update attendance_processed a set employee_id = hr_employee.employee_no, employe_name = hr_employee.id
from hr_employee
where hr_employee.card_no = a.name and a.employee_id is null and hr_employee.current_status = 'active'
""")
#to update the late time
cr.execute("""
update attendance_processed set
late_time_1 = (case when (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:30')) / 3600.0 > 0.05
then 0.05 else (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:30')) / 3600.0 end),
late_time_2 = (case when (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:30')) / 3600.0 < 0.06666
then 0.0 else
case when (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:33')) / 3600.0 > 0.2
then 0.2 else (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:33')) / 3600.0 end end),
late_time_3 = (case when (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:45')) / 3600.0 > 0.0
then (EXTRACT(EPOCH FROM (sign_in)::time) - EXTRACT(EPOCH FROM time '08:45')) / 3600.0 else 0.0 end)
where to_timestamp(reldate||' '||sign_in,'YY/MM/DD HH24:MI') > to_timestamp(reldate||' 08:30','YY/MM/DD HH24:MI')
and to_timestamp(reldate||' '||sign_in,'YY/MM/DD HH24:MI') < to_timestamp(reldate||' 10:30','YY/MM/DD HH24:MI') and to_date(reldate, 'YY-MM-DD') >= CURRENT_TIMESTAMP - INTERVAL '20 days'
""")
#and to_date(reldate, 'YY-MM-DD') >= CURRENT_TIMESTAMP - INTERVAL '20 days' and current_status = True
#To Flag Roster Attendance
cr.execute("""
update attendance_processed b set is_roster = True
where b.id IN (
select id from attendance_processed a where a.employee_id IN (
select h.employee_no from hr_employee h, employee_category_rel mc, hr_employee_category cc
where h.id = mc.emp_id and cc.id = mc.category_id
and cc.name = 'Roster' and h.current_status='active')
and to_date(a.reldate, 'YY-MM-DD') >= CURRENT_TIMESTAMP - INTERVAL '7 days' )
and id NOT IN (select id from attendance_processed aa where
aa.is_roster = True) """)
now = dt.datetime.now().date()
days = dt.timedelta(days=-8)
check = now + days
attendance_object = self.pool.get('attendance.processed')
search_condition2 = [('name', '!=', ''),('date_calendar','>', check )]
attendance_obj_ids=attendance_object.search(cr, uid, search_condition2, context=context)
attendance_objects=attendance_object.browse(cr, uid, attendance_obj_ids, context=context)
fulldate = False
if attendance_objects:
for att_obj in attendance_objects:
if att_obj.reldate:
fulldate = datetime.strptime(att_obj.reldate, '%y/%m/%d')
search_condition3 = [('date', '=', fulldate)]
company_holiday_obj = self.pool.get('company.holidays')
search_ids=company_holiday_obj.search(cr, uid, search_condition3, context=context)
browse_ids=company_holiday_obj.browse(cr, uid, search_ids, context=context)
try:
intime = datetime.strptime(att_obj.sign_in,'%H:%M')
outtime = datetime.strptime(att_obj.sign_out,'%H:%M')
except:
intime = datetime.strptime('11:11','%H:%M')
outtime = datetime.strptime('11:11','%H:%M')
bol = False
if browse_ids:
day_type='holiday'
elif fulldate.isoweekday() in (6, 7):
day_type='weekend'
else:
#delta.seconds
day_type='normal'
if day_type =='normal':
delta = (outtime - intime)
# print delta
minutes = (delta.seconds)/60
if minutes <= 240:
bol = True
action = 'r'
else:
bol = False
action = 'n'
elif intime == outtime:
bol = True
action = 'r'
else:
bol = False
action = 'n'
# in here 'need_regulaized' for check time
att_obj.write({'day_type': day_type,'action':action, 'need_regulaized':bol})
#Update attendance_processed_days
cr.execute(""" truncate attendance_processed_days """)
cr.execute("""
INSERT INTO attendance_processed_days (id, sign_out2,leave_or_holiday,sign_in,date,
sign_out,employee_id,late_time_2,late_time_3 ,late_time_1,employe_no,sign_in2,dept_name,division_name,section_name,category,action)
SELECT id, sign_out2,leave_or_holiday,sign_in,date,
sign_out,employee_id,late_time_2,late_time_3 ,late_time_1,employee_no,sign_in2,dept_name,division_name,section_name,category,action
from employee_attendance_with_holidays where employee_no is not null
""")
#to update missing company
cr.execute("""
update attendance_processed_days a set comp_id = c.company_id from hr_dept_n_registration c
where a.dept_name = c.id
""")
#to update missing dept/div/sec/com
cr.execute("""
UPDATE
attendance_processed_days a
SET
division_name = aa.division_name,
dept_name = aa.dept_name,
section_name = aa.section_name,
comp_id = aa.comp_id
From (select distinct employe_no, division_name,dept_name,section_name,comp_id
from attendance_processed_days where comp_id is not null) aa
WHERE
a.employe_no = aa.employe_no
and a.comp_id is null
""")
#to update late time
cr.execute("""
update attendance_processed_days
set late_time_1 = null , late_time_2 = null, late_time_3 = null
where (leave_or_holiday like '%Leave%'
or leave_or_holiday like '%Half Day%')
and late_time_1 > 0
""")
return False