Modify user json -DB connector

[Som9800]

Hello Everyone ,

We are having three Usecase which we have to accomodate in modify user json of DB connector.

1. We have to set users status as inactive when user current date  is greater than = 21 days from startdate and customproperty35 is null.

2.We have to set users status as inactive when user enddate =current date or enddate is less than current date.

3.When user enddate is greater than 1 year from the current date or enddate is null then we are setting the enddate as 1year from the current date.

We are using below json for that : And here the issue is the 1st and 2nd usecase is not working together. we are unable to inactive the users when user current date  is greater than = 21 days from startdate and customproperty35 is null.

-----------------------------------------------------------------------------------------------------------------------------------

{
"ADDITIONALTABLES": {
"USERS": "SELECT enddate, username, statuskey, systemusername, startdate, customproperty35,employeeType FROM users"},
"COMPUTEDCOLUMNS": ["statuskey","enddate"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET statuskey = CASE WHEN customproperty35 IS NULL AND DATEDIFF(SYSDATE(), DATE_FORMAT(NEWUSERDATA.startdate, '%Y-%m-%d %H:%i:%s')) >= 21 and employeeType='I' THEN 0 ELSE 1 END",
"UPDATE NEWUSERDATA SET NEWUSERDATA.statuskey =case when NEWUSERDATA.enddate is not null and Date(NEWUSERDATA.enddate)<=Date(now()) and statuskey<>0 then 'inactive' else 'active' end",
"UPDATE NEWUSERDATA SET enddate = ADDDATE(current_date, INTERVAL 1 YEAR) where (enddate is null OR enddate > ADDDATE(current_date, INTERVAL 1 YEAR)) and employeeType='E'"
]
}

Thanks in Advance

[rushikeshvartak · Accepted solution]

{
"ADDITIONALTABLES": {
"USERS": "SELECT enddate, username, statuskey, systemusername, startdate, customproperty35, employeeType FROM users"
},
"COMPUTEDCOLUMNS": ["statuskey", "enddate"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET statuskey = CASE WHEN customproperty35 IS NULL AND DATEDIFF(SYSDATE(), DATE_FORMAT(NEWUSERDATA.startdate, '%Y-%m-%d %H:%i:%s')) >= 21 and employeeType='I' THEN 'inactive' ELSE statuskey END",
"UPDATE NEWUSERDATA SET statuskey = CASE WHEN enddate IS NOT NULL AND DATE(enddate) <= DATE(NOW()) THEN 'inactive' ELSE statuskey END",
"UPDATE NEWUSERDATA SET enddate = ADDDATE(current_date, INTERVAL 1 YEAR) WHERE (enddate IS NULL OR enddate > ADDDATE(current_date, INTERVAL 1 YEAR)) AND employeeType='E'"
]
}

[Som9800]

Thanks for your Help .