Error saving Appraisals with Workflow

1

i have work-flow and date validation.

date validation code:

def validate_existing_appraisal(self):
chk = frappe.db.sql(“”“select name from tabAppraisal where employee=%s
and (status=‘Submitted’ or status=‘Draft’)
and ((start_date>=%s and start_date<=%s)
or (end_date>=%s and end_date<=%s))”“”,
(self.employee,self.start_date,self.end_date,self.start_date,self.end_date))
if chk:
frappe.throw(_(“Appraisal {0} created for Employee {1} in the given date range”).format(chk[0][0], self.employee_name))

so, when i save same document more than one, its giving date validation SMS and can’t go next step. if i don’t use work flow, it work, but i need work flow.
in work flow, Employee save and apply(save). and supervisor verify(save) and approver Approve(submitted).

so in this case employee have to save 2 time, (save and apply)

2

at which event is this function being called ?

One more thing this query string needs a small correction

You’ll face issues.

"""
select name from `tabAppraisal` 
where employee=%s
and (status=‘Submitted’ or status=‘Draft’)
and ((start_date&gt;=%s and start_date&lt;=%s)
or (end_date&gt;=%s and end_date&lt;=%s))"""%(self.employee,self.start_date,self.end_date,self.start_date,self.end_date)
``
3

@root13F, When employee apply(save) using work flow

4

can you elaborate more on this ?

do you mean to say that you are getting an error ?

also please post your Appraisal workflow and that error screenshot

5

@root13F i can save once only but i can’t apply(save) and save again

work flow:

work
work1223×519 29.9 KB

stat
stat916×426 21.5 KB

and while apply it give date validation error:
Massages
dd
dd965×402 24.2 KB

6

EDIT

However I think there could be a better solution and easier solution to run your code on event before_save to save this complex hassle.

Open this python file of Employee Appraisal in which function was your function called on.

I may have understood what is your problem. I may be wrong

Since your document is already saved, it is validating against itself and throwing you an error. Check this out:-

imageedit_4_6423198707
imageedit_4_6423198707965×402 36.5 KB

Do one thing. replace your code by this one and check if you are able to save or not

def validate_existing_appraisal(self):
    cond_exp = "and name <> '%s'"%(self.name) if self.get('__islocal') else '1=1'
    chk = frappe.db.sql("""
            select 
                name from tabAppraisal 
            where employee=%s
                and %s
                and (status=‘Submitted’ or status=‘Draft’)
                and ((start_date>=%s and start_date<=%s)
                or (end_date>=%s and end_date<=%s))"""%(self.employee, cond_exp, self.start_date,self.end_date,self.start_date,self.end_date))
    if chk:
        frappe.throw(_(“Appraisal {0} created for Employee {1} in the given date range”).format(chk[0][0], self.employee_name))
7

i used your code but its giving error while Apply

ff
ff625×557 41.8 KB

8

enclose each %s in single quotes like this - ‘%s’ and try again

9

@root13F, its given this error:

ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘CDCL1803006’’ \n\t\t\t\tand ‘‘1=1’’ \n\t\t\t\tand (status=‘Submitted’ or ‘status=‘Draft’)\n’ at line 1”)

code:

def validate_existing_appraisal(self):
cond_exp = “and name <> ‘%s’”%(self.name) if self.get(‘__islocal’) else ‘1=1’
chk = frappe.db.sql(“”“select name from tabAppraisal where employee=‘%s’
and ‘%s’
and (status=‘Submitted’ or ‘status=‘Draft’)
and ((start_date>=’%s’ and start_date<=‘%s’)
or (end_date>=‘%s’ and end_date<=‘%s’))”“”, (self.employee, cond_exp, self.start_date, self.end_date,self.start_date, self.end_date))
if chk:
frappe.throw(_(“Appraisal {0} created for Employee {1} in the given date range”).format(chk[0][0], self.employee_name))

10

Replace ‘1=1’ string by just number 1.

Also, remove quotes from the corresponding %s and then try again. I’m talking about the one after employee = %s

11

Is this a bug in the standard version or is this customized?

12

@rmehta standard version

13

were you able to solve this problem

14

yes solved by modifying code:

def validate_existing_appraisal(self):
		chk = frappe.db.sql("""select name, start_date, end_date
			from `tabAppraisal` 
			where employee = %(employee)s and docstatus < 2 and status in ("Draft", "Approved")
			and end_date >= %(start_date)s and start_date <= %(end_date)s
			and name != %(name)s""", {
				"employee": self.employee,
				"start_date":self.start_date,
				"end_date":self.end_date,
				"name":self.name
			})