OpenEMM Technical Support Forum

Posted: **Fri Jan 22, 2016 4:52 am**

Does anyone know if there is a class or method to update the customer table?

I have been trying for days now to update a custom profile (customer) field called LAST_CLICK_DATE with the current date when a recipient clicks on a link in an email.
I have gotten everything working with a velocity script with the exception of being able to set the date variable. I have tried all of the date references I could find but nothing seems to work. Apparently they are dependent on the velocity tools that have been loaded (for which I can't find any reference).

I have tried all of these with no luck:

Code: Select all

#set ($current_date = $date.getCurrentDate())

2016-01-21 21:44:25,115: WARN [http-apr-8080-exec-28] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Error in velocity script: Error in line 10, column 23: Null reference $date.[]

Code: Select all

#set ($current_date = $date)

2016-01-21 20:10:19,968: WARN [http-apr-8080-exec-26] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Error in velocity script: Error in line 10, column 21: Null reference $date.[]

Code: Select all

#set ($current_date = $date())

2016-01-21 21:42:46,520: ERROR [http-apr-8080-exec-25] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Velocity error
org.apache.velocity.exception.ParseErrorException: Encountered "(" at line 10, column 28 of Company ID 1
Was expecting one of:
<RPAREN> ...
<WHITESPACE> ...
"-" ...
"+" ...
"*" ...
"/" ...
"%" ...
<LOGICAL_AND> ...
<LOGICAL_OR> ...
<LOGICAL_LT> ...
<LOGICAL_LE> ...
<LOGICAL_GT> ...
<LOGICAL_GE> ...
<LOGICAL_EQUALS> ...
<LOGICAL_NOT_EQUALS> ...
<DOT> ...

Code: Select all

#set ($current_date = $dateUtil.date())
#set ($current_date = $dateUtil.date)
#set ($date = $dateUtil) 
#set ($current_date = $date.getCurrentDate())

2016-01-21 20:14:30,341: WARN [http-apr-8080-exec-30] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Error in velocity script: Error in line 10, column 15: Null reference $dateUtil.[]

Code: Select all

#set ($current_date = $tools.date)

2016-01-21 21:34:06,706: WARN [http-apr-8080-exec-21] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Error in velocity script: Error in line 11, column 23: Null reference $tools.[]

Code: Select all

#set($currentDate = java.util.Date date)

2016-01-21 17:17:46,499: WARN [http-apr-8080-exec-12] org.agnitas.emm.core.action.service.impl.ActionOperationExecuteScriptImpl - Error in velocity script: Error in line 10, column 21: Null reference $java.[]

So if anyone knows how to set a variable to the current date (which shouldn't be this freaking hard), please enlighten me.

Posted: **Sat Jan 23, 2016 2:24 am**

Since apparently no one knows how to do this, I will try and save others the frustration and grief of trying to figure out how to do a couple fairly simple tasks using a non-standard implementation of a no longer used (because it suks) scripting language. First of all, if you have tried to make sense out of the velocity documents, don't waste your time because the implementation of the velocity engine does not include the most basic function or tools meaning none of the coding examples still available work.

First, to implement something along the lines of updated a custom date field with the current date (to track the last time a user actually clicked on a link in an email).
Assuming you have already created a date field called LAST_CLICK_DATE, ignore that for now because you can't set the current date using velocity...or at least this implementation of velocity. What you can do is create a numeric field called CLICK_INDICATOR and using velocity script, set that value to "1". Yes, that is a string reference to a number because if you try to set an actual number for a number field, it will bomb. This will come into play a little farther down the page. Once you set that variable, you can create a MySQL function that is scheduled to run every day at a certain time and scan through the table looking for the 1 you set in the CLICK_INDICATOR field and then setting the current date for the LAST_CLICK_DATE and resetting the CLICK_INDICATOR field to 0. I am going to assume you can figure this out because there is actual documentation that clearly explains how to do this.

The next sort of task you might want to implement is adding a number to an existing value in a numeric custom profile field. That should be quick and easy right? Yeah....not so much. What will likely throw you for a loop is that when you request the data from the database, number fields are turned into string values. So if you have a current value of 1 and add 1 to it, you get 11. So you have to convert the number value you pull from the database to a number (as redundant as that may seem). Hopefully the result is 2 but you can't post that 2 back to the numeric field in your database because it will bomb. You have to first convert the number 2 to a string "2" so the import routine can convert it back to a number. How is that for a model of efficiency??

Included is the code to do these two things and hopefully with the explanation above (included to let you know not to expect anything to be simple or straight forward (or logical for that matter), you will be able to use it to expand the functionality of openemm into something useful.

Code: Select all

$ScriptHelper.println("---------------START NEW CLICK TRACK----------------")
##these two lines somehow pull the customer ID from the uri...but you need it to pull the correct record from the db
$Customer.resetCustParameters()
$Customer.setCustomerID($customerID)

##this actually pulls the record into a structure called a HASH I think
$Customer.loadCustDBStructure()
#set($requestParameters=$Customer.getCustomerDataFromDb())

##You have to set this in order to let velocity know that you might like to do something with an integer value
#set( $Integer = 0 )

##this may not be necessary but I included it to clear any spaces from around the value
#set( $numberAsString = $requestParameters.interest_product.trim() )

##this converts the numeric database value into a number....um....yeah
#set( $interestProduct = $Integer.parseInt($numberAsString) )

##this adds 1 to the database value
#set( $interest_product = $interestProduct + 1)

##this converts the number value you just set to a string....so um....it can be converted back to a number
$requestParameters.put( "interest_product", $interest_product.toString() )

##this sets a numeric indicator value in the database...make sure you use the "" "" to indicate the number is a...um string...so it can...um be converted back to a number
$requestParameters.put( "click_indicator", "1" )

##this is important as it lets the database know that you changed something
#set($Customer.changeFlag = true)

##not really sure what this does but it is required
#if($Customer.importRequestParameters($requestParameters, null))
    $ScriptHelper.println("requestParameters SUCCESS")

     ##this actually imports the data into the database
     #if($Customer.updateInDB())
        $ScriptHelper.println("Customer update SUCCESS")      
    #else
        $ScriptHelper.println("Customer update FALSE")       
    #end
#else
    $ScriptHelper.println("requestParameters FALSE")
#end

$ScriptHelper.println("---------------END CLICK TRACK----------------")

#*the documentation says that the output from the ScriptHelper.println functions above will appear in the velocity log. 
You can go ahead and ignore that because it doesn't. All of the messages that let you know if
everything worked actually appear as blaring ERROR entries in the openemm log. The good news
is you can ignore the ERROR notation because they are not errors. Also, if you modify this and have problems, ignore 
pretty much every reference you may read to velocity files, properties files, xml files that all
are supposed to configure the velocity instance because none of them exist. *#


#set($scriptResult="1")

If anyone would like to add to any of this, please do so because there are undoubtedly others that were lured into learning this application by the numerous (what I assume are dubious) 5 star ratings just like we were and are now faced with writing off the weeks, months or years we spent to get this far or actually trying to make this a productive piece of software. But without everyone chipping in and sharing the hard fought victories, it will fade away and we will eventually have to start over with something else and right now, that is about the last thing I want to contemplate. Good Luck!!

OpenEMM Technical Support Forum

Using Actions to update Customer Table

Using Actions to update Customer Table

Re: Using Actions to update Customer Table