Following is my create function for roster module. The problem is that only update query is not working. The query is working fine when it run alone in pgadmin but in here it doesn't. Both Select and Insert queries are working fine.
(I know using cr.execute is not a good practice but I am in little bit hurry with deadlines).
def create(self, cr, uid, values, context=None):
#rec_id=values['id']
sub_day=values['roster_day']
ros_time=values['time_slot']
emp = values['employee']
dept = values['department_id']
sub_emp = values['sub_employee']
#sub_day = datetime.datetime.strptime(sub_day, '%Y-%m-%d')
cr.execute("""SELECT ra.id , ra.emp_id FROM roster_allocation ra, roster_days_allocation rda
WHERE rda.roster_allocation_connection=ra.id and
rda.allocation_start_day='%s' and
rda.roster_time_list=%d and
ra.emp_id=%d"""%(sub_day,ros_time,emp))
exers=cr.fetchone()[0]
cr.execute("""INSERT INTO roster_allocation (write_uid,emp_id,department_id) VALUES(%d,%d,%d)""" %(context['uid'], sub_emp, dept))
print "Employee for substitution record inserted successfully"
cr.execute("""UPDATE roster_days_allocation SET roster_allocation_connection = (SELECT MAX(ra.id) FROM roster_allocation ra, roster_substitution rs
WHERE ra.emp_id=rs.sub_employee)
WHERE allocation_start_day = '%s' AND roster_time_list = %d AND roster_allocation_connection = %d""" %(sub_day, ros_time,exers))
print "Employee for substitution record updated successfully"
return super(roster_substitution, self).create(cr, uid, values, context=context)
I have edited UPDATE query and even though it's not the best practice, it worked.
cr.execute (SELECT MAX(ra.id) FROM roster_allocation ra, roster_substitution rs
WHERE ra.emp_id=rs.sub_employee)
val=cr.fetchone()
cr.execute("""UPDATE roster_days_allocation SET roster_allocation_connection = %d
WHERE allocation_start_day = '%s' AND roster_time_list = %d AND roster_allocation_connection = %d""" %(val,sub_day, ros_time,exers))