通过执行这个功能,整个ERP系统变得很慢(有时甚至无法登录),并且当这种情况发生时,会丢失一些随机记录。
请你帮我解决这个问题。
程序-
合并数据库()
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;
功能
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
