Skip to Content
Menu
This question has been flagged
1 Reply
1601 Views

i have wrote 3 views which are used in below compute code, queries i tested on pgAdmin but i don't know why it is throwing error.

please help to resolve the issue, i am badly stuck here.

for rec in self:
    # clean_id = self._origin.id
    clean_id = 448 # BECAUSE above line throws error...
    req_date = rec.request_date
    query = """SELECT id, name, tenure_days, tenure_months, tenure_years FROM employee_tenure WHERE id = %s"""
    self.env.cr.execute(query, (clean_id,))
    result = self.env.cr.dictfetchall()
    #
    for row in result:
        if row.get('tenure_months') > 12:
            # get payslip line amount for rule_id 107
            query2 = """SELECT pl_amount FROM emp_pf_amount WHERE employee_id = %s"""
            self.env.cr.execute(query2, (clean_id,))
            result2 = self.env.cr.dictfetchall()
            x_amount = 0
            for pl_row in result2:
                x_amount = pl_row.get('pl_amount')
                rec['x_loan_applicable_amount'] = x_amount
            dt_string = req_date.strftime("%Y-%m-%d")
            dt1 = datetime.datetime.strptime(dt_string, "%Y-%m-%d")
            dt_month = dt1.month
            dt_month1 = '07'
            dt_date1 = '01'
            dt_month2 = '06'
            dt_date2 = '30'
            if dt_month in (7,8,9,10,11,12):
                dt_year1 = dt1.year
                dt_year2 = dt1.year + 1
            else:
                dt_year1 = dt1.year - 1
                dt_year2 = dt1.year

            dt_range = 'to_date(\''+dt_year1+'-'+dt_month1+'-'+dt_date1+'\','+'\'YYYY-MM-DD\''+') AND '+'to_date(\''+dt_year2+'-'+dt_month2+'-'+dt_date2+'\','+'\'YYYY-MM-DD\''+')'

            query3 = """SELECT emp_allocation_amount FROM emp_ann_leave_amount WHERE employee_id = %s AND date_from between %s"""
            self.env.cr.execute(query3, (clean_id, (dt_range,)))
            result3 = self.env.cr.dictfetchall()
            for pi_row in result3:
                x_amount += pi_row.get('pi_amount')
                rec['x_loan_applicable_amount'] = x_amount
        else:
            rec['x_loan_applicable_amount'] = 1


tested this here...


regards

Avatar
Discard
Author

i tested dt_range for syntax and it is showing as below:

to_date('2023-07-01','YYYY-MM-DD') AND to_date('2024-06-30','YYYY-MM-DD')

may be i am overlooking or something else, seniors experts please help to have working code.

regards

Author Best Answer

i have managed, the code below is working fine. this is for juniors like me. just one thing i have work on which is clean_id , i have passed hard coded value because it is throwing error on the lines i have remarked.

for rec in self:

    # clean_id = rec.id

    # clean_id = self.id

    # clean_id = self._origin.id

    clean_id = 448

    req_date = rec.request_date

    query = """SELECT id, name, tenure_days, tenure_months, tenure_years FROM employee_tenure WHERE id = %s"""

    self.env.cr.execute(query, (clean_id,))

    result = self.env.cr.dictfetchall()

    # 

    for row in result:

        if row.get('tenure_months') > 12:

            # get payslip line amount for rule_id 107

            query2 = """SELECT pl_amount FROM emp_pf_amount WHERE employee_id = %s"""

            self.env.cr.execute(query2, (clean_id,))

            result2 = self.env.cr.dictfetchall()

            x_amount = 0

            for pl_row in result2:

                x_amount = pl_row.get('pl_amount')

                rec['x_loan_applicable_amount'] = x_amount

#

# get payslip input amount for rule_id 16

            # to create date_from and date_to from request_date year

            dt_string = req_date.strftime("%Y-%m-%d")

            dt1 = datetime.datetime.strptime(dt_string, "%Y-%m-%d")

            dt_month = dt1.month

            dt_month1 = '07'

            dt_date1 = '01'

            dt_month2 = '06'

            dt_date2 = '30'

            if dt_month in (7,8,9,10,11,12):

                dt_year1 = dt1.year

                dt_year2 = dt1.year + 1

            else:

                dt_year1 = dt1.year - 1

                dt_year2 = dt1.year

            dt_year1 = str(dt_year1)

            dt_year2 = str(dt_year2)

            # dt_range = 'to_date(\''+dt_year1+'-'+dt_month1+'-'+dt_date1+'\','+'\'YYYY-MM-DD\''+') AND '+'to_date(\''+dt_year2+'-'+dt_month2+'-'+dt_date2+'\','+'\'YYYY-MM-DD\''+')'

            dt_from = '\''+dt_year1+'-'+dt_month1+'-'+dt_date1+'\''

            dt_to = '\''+dt_year2+'-'+dt_month2+'-'+dt_date2+'\''

# query3 = """SELECT emp_allocation_amount FROM emp_ann_leave_amount WHERE employee_id = %s AND date_from between %s"""

            query3 = """SELECT emp_allocation_amount FROM emp_ann_leave_amount WHERE employee_id = %s AND date_from BETWEEN %s AND %s"""

            self.env.cr.execute(query3, (clean_id, dt_from, dt_to))

            result3 = self.env.cr.dictfetchall()

            for pi_row in result3:

                x_amount += pi_row.get('emp_allocation_amount')

                rec['x_loan_applicable_amount'] = x_amount

        else:

            rec['x_loan_applicable_amount'] = 1

happy coding.

regards

Avatar
Discard
Author

also the problem is resolved for clean_id which mentioned in my Answer above.

Related Posts Replies Views Activity
4
May 25
2293
2
May 25
5659
1
Mar 25
1499
4
Mar 25
4277
3
Feb 25
5263