Change Email in preprocessor

[mansoorahmed1]

Im trying to change the email(change only the domain) for an user during internal company transfer occurs. During the transfer  sometime there will be transfer start date and end date will be passed from HR source and the date difference should be greater than 14 days ,Sometimes it will be NULL.  I have to consider both the scenario inorder to change the email domain. So I m trying to use preprocessor query. The transfer start date and end date are in custom property (which are stored as string ) I'm trying to use below query but its giving me date truncation error . How to over come this when the dates are NULL . 

NOTE: I cant use user update rule as it changes the user's email prefix as well as per the email generation logic which is not prefered by the customer.

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.EMAIL = CASE WHEN (NEWUSERDATA.customproperty49 IS NULL or NEWUSERDATA.customproperty48 IS NULL AND CURRENTUSERS.EMAIL LIKE '%@domain%' and NEWUSERDATA.customproperty16 = 'Active' and NEWUSERDATA.CUSTOMPROPERTY7 ='2001' OR NEWUSERDATA.DEPARTMENTNAME = 'Emerging Energy Solutions' THEN CONCAT ((SUBSTRING_INDEX(CURRENTUSERS.EMAIL, '@', 1)),'@domain.com.au')) WHEN (NEWUSERDATA.customproperty49 IS NOT NULL or NEWUSERDATA.customproperty48 IS NOT NULL AND CURRENTUSERS.EMAIL LIKE '%@domain%' and NEWUSERDATA.customproperty16 = 'Active' and ((datediff(STR_To_DATE(NEWUSERDATA.customproperty49, '%d/%m/%Y'),STR_To_DATE(NEWUSERDATA.customproperty48, '%d/%m/%Y'))) > 14) AND NEWUSERDATA.CUSTOMPROPERTY7 ='2001' OR NEWUSERDATA.DEPARTMENTNAME = 'Emerging Energy Solutions' THEN CONCAT ((SUBSTRING_INDEX(CURRENTUSERS.EMAIL, '@', 1)),'@domain.com.au')) END

Error : 

10-Mar-2024 11:35:04.607 WARNING [http-nio-8080-exec-1] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_37329 LEFT JOIN TEMPUSERS_37329 ON TEMPNEWUSERS_37329.USERNAME = TEMPUSERS_37329.USERNAME SET TEMPNEWUSERS_37329.CUSTOMPROPERTY16 = CASE WHEN ( TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Active' AND DATEDIFF(DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.STARTDATE, '%d/%m/%Y'), '%Y-%m-%d'),CURDATE()) > 21) THEN 'Registered' WHEN (TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Active' AND DATEDIFF(DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.STARTDATE, '%d/%m/%Y'), '%Y-%m-%d'),CURDATE()) BETWEEN 1 AND 21) THEN 'Prehire' WHEN (TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Active' AND (CURDATE() >= DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.STARTDATE, '%d/%m/%Y'), '%Y-%m-%d')) AND ((TEMPUSERS_37329.ENDDATE IS NULL AND TEMPUSERS_37329.TERMDATE IS NULL) OR (DATEDIFF(DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.TERMDATE, '%d/%m/%Y'), '%Y-%m-%d'), CURDATE()) > 10) OR ( DATEDIFF(DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.ENDDATE, '%d/%m/%Y'), '%Y-%m-%d'), CURDATE()) > 10)))  THEN 'Active' WHEN(TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Active' AND ( IF(TEMPNEWUSERS_37329.TERMDATE IS NULL,(DATEDIFF(DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.TERMDATE, '%m/%d/%Y'), '%Y-%m-%d'), CURDATE()) BETWEEN 1 AND 10), (DATEDIFF(TEMPUSERS_37329.ENDDATE, CURDATE()) BETWEEN 1 AND 10)))) THEN 'Activeleaving' WHEN (TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Active' AND (IF (TEMPNEWUSERS_37329.TERMDATE IS NULL ,(CURDATE() >= DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.ENDDATE, '%m/%d/%Y'), '%Y-%m-%d')),(IF (TEMPNEWUSERS_37329.ENDDATE IS NULL ,(CURDATE() >= (DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.TERMDATE, '%m/%d/%Y'), '%Y-%m-%d'))), (CURDATE() >= DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.ENDDATE, '%m/%d/%Y'), '%Y-%m-%d')))))))THEN 'Inactive' WHEN (TEMPNEWUSERS_37329.CUSTOMPROPERTY33 = 'Terminated' AND ( (TEMPUSERS_37329.ENDDATE IS NULL OR TEMPUSERS_37329.TERMDATE IS NULL) OR  (DATEDIFF(CURDATE(), DATE_FORMAT(STR_TO_DATE(TEMPNEWUSERS_37329.TERMDATE, '%m/%d/%Y'), '%Y-%m-%d')) > 10))) THEN 'Terminated' END because: Data truncation: Incorrect datetime value: '0000-00-00'

Error while processing data:

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '0000-00-00'

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)

at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)

at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108)

at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)

at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416)

at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405)

at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733)

at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686)

at com.saviynt.ecm.services.BulkUserUploadService.importUserFromRequest(BulkUserUploadService.groovy:265)

at com.saviynt.workflowmgt.grantaccess.doaccess(grantaccess.groovy:50)

at com.saviynt.workflowmgt.grantaccess$_createAccess_closure1.doCall(grantaccess.groovy:95)

at com.saviynt.workflowmgt.grantaccess.createAccess(grantaccess.groovy:94)

at org.jbpm.pvm.internal.util.ReflectUtil.invoke(ReflectUtil.java:184)

at org.jbpm.pvm.internal.wire.descriptor.ObjectDescriptor.invokeMethod(ObjectDescriptor.java:209)

at org.jbpm.jpdl.internal.activity.JavaActivity.perform(JavaActivity.java:76)

at org.jbpm.jpdl.internal.activity.JpdlAutomaticActivity.execute(JpdlAutomaticActivity.java:15)

at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)

at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)

at org.jbpm.pvm.internal.model.ExecutionImpl.fire(ExecutionImpl.java:582)

at org.jbpm.pvm.internal.model.ExecutionImpl.take(ExecutionImpl.java:487)

at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:123)

at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:53)

at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)

at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)

at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperation(ExecutionImpl.java:632)

at org.jbpm.pvm.internal.model.ExecutionImpl.start(ExecutionImpl.java:217)

at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:63)

at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:36)

at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42)

at org.jbpm.pvm.internal.tx.SpringCommandCallback.doInTransaction(SpringCommandCallback.java:45)

at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:49)

at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.executeInNewEnvironment(EnvironmentInterceptor.java:53)

at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:40)

at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:56)

at org.jbpm.pvm.internal.svc.ExecutionServiceImpl.startProcessInstanceByKey(ExecutionServiceImpl.java:71)

at com.saviynt.ecm.services.WorkflowService.workflowaccessreqStart(WorkflowService.groovy:1066)

at com.saviynt.ecm.services.BulkUserUploadService.createRequestForBulkUserUpload(BulkUserUploadService.groovy:91)

at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8687)

at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1716)

at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)

at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159)

at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)

at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)

at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)

at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)

at java.lang.Thread.run(Thread.java:750)

[CR]

May be it expecting different format date yyyy-mm-dd , change date format and again try..!

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.EMAIL = CASE WHEN (NEWUSERDATA.customproperty49 IS NULL or NEWUSERDATA.customproperty48 IS NULL AND CURRENTUSERS.EMAIL LIKE '%@domain%' and NEWUSERDATA.customproperty16 = 'Active' and NEWUSERDATA.CUSTOMPROPERTY7 ='2001' OR NEWUSERDATA.DEPARTMENTNAME = 'Emerging Energy Solutions' THEN CONCAT ((SUBSTRING_INDEX(CURRENTUSERS.EMAIL, '@', 1)),'@domain.com.au')) WHEN (NEWUSERDATA.customproperty49 IS NOT NULL or NEWUSERDATA.customproperty48 IS NOT NULL AND CURRENTUSERS.EMAIL LIKE '%@domain%' and NEWUSERDATA.customproperty16 = 'Active' and ((datediff(STR_To_DATE(NEWUSERDATA.customproperty49, '%Y-%m-%d'),STR_To_DATE(NEWUSERDATA.customproperty48, '%Y-%m-%d'))) > 14) AND NEWUSERDATA.CUSTOMPROPERTY7 ='2001' OR NEWUSERDATA.DEPARTMENTNAME = 'Emerging Energy Solutions' THEN CONCAT ((SUBSTRING_INDEX(CURRENTUSERS.EMAIL, '@', 1)),'@domain.com.au')) END

[rushikeshvartak]

The error message indicates that there's an issue with the date values in your MySQL query. It seems that you're trying to convert date strings to dates using the `STR_TO_DATE` function, but some of the date values might be invalid.

To fix this error, you should ensure that all date strings being converted to dates are in the correct format and are valid dates. Check the `customproperty49` and `customproperty48` columns in your `NEWUSERDATA` table to see if any of the values are '0000-00-00', which is an invalid date format in MySQL.

You may need to update your data to ensure that all date values are in the correct format, or handle invalid dates appropriately in your query. You can use the `DATE_FORMAT` function to format dates properly or use `NULL` for invalid dates if that's acceptable in your use case.