postgresqlpython-2.7openerp-7

Openerp create function doesn't execute query


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)

Solution

  • 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))