How do I get the Status with the latest TimestampCreate date time? 1

[momo2000]

I would like to get the Status whose TimestampCreate matches ControlPoint Timestamp.

I am not sure how to change my xslt to get matching Status TimestampCreate.

To compare Status TimestampCreate to ControlPoint TimeStamp I am using a fuction to convert these to numeric
Here is my function

<xsl:value-of select="mscef:formatDateTimeNumeric(mscef:fixOdysseyTimestamp(string(TimeStampCreate)))"/>

Expected output

<Statuses>
	<Status Op="A">
		<Current>false</Current>
		<Date Op="A">05/09/2016</Date>
		<Type Op="A" Word="SBJO">Signed</Type>
		<TimestampCreate>20160509143434737</TimestampCreate>
	</Status>
</Statuses>

My xml doc

<?xml version="1.0" encoding="UTF-8"?>
<Integration>
	<ControlPoint Timestamp="5/9/2016 2:34:34 PM" UserID="Kuku">SAVE</ControlPoint>
	<ProtectionOrders>
		<ProtectionOrder Op="E" InternalProtectionOrderID="11831">
			<Statuses>
				<Status>
					<Current>true</Current>
					<Active>No</Active>
					<Date>05/09/2016</Date>
					<Type Word="DISMISSED">Dismissed</Type>
					<TimestampCreate Op="A">05/09/2016 14:34:48:633</TimestampCreate>
				</Status>
				<Status Op="A">
					<Current>false</Current>
					<Active>Yes</Active>
					<Date Op="A">05/09/2016</Date>
					<Type Op="A" Word="SBJO">Signed</Type>
					<TimestampCreate>05/09/2016 14:34:34:737</TimestampCreate>
				</Status>
				<Status>
					<Current>false</Current>
					<Active>No</Active>
					<Date>12/30/2014</Date>
					<Type Word="DRAFT">Draft</Type>
					<TimestampCreate>05/09/2016 14:34:14:987</TimestampCreate>
				</Status>
			</Statuses>
		</ProtectionOrder>
	</ProtectionOrders>
</Integration>

xslt 1.0 code

<xsl:value-of select="/Integration/ProtectionOrder/Statuses/Status/Date"/>

 

[k5tm]

Howdy, momo.

Toss the function. Not really needed, and very well may get in the way.

I modified your data in order to place two nodes slightly separated by the millisecond field. This allows me to make sure I am picking the 'first' (more about this later) node that qualifies. So here is the new data:

<Integration>
	<ControlPoint Timestamp="5/9/2016 2:34:34 PM" UserID="Kuku">SAVE</ControlPoint>
	<ProtectionOrders>
		<ProtectionOrder Op="E" InternalProtectionOrderID="11831">
			<Statuses>
				<Status>
					<Current>true</Current>
					<Active>No</Active>
					<Date>05/09/2016</Date>
					<Type Word="DISMISSED">Dismissed</Type>
					<TimestampCreate Op="A">05/09/2016 14:34:48:633</TimestampCreate>
				</Status>
				<Status Op="A">
					<Current>false</Current>
					<Active>Yes</Active>
					<Date Op="A">05/09/2016</Date>
					<Type Op="A" Word="SBJO">Signed</Type>
					<TimestampCreate>05/09/2016 14:34:34:737</TimestampCreate>
				</Status>
				<Status Op="A">
					<Current>false</Current>
					<Active>Yes</Active>
					<Date Op="A">05/09/2016</Date>
					<Type Op="A" Word="SBJO">XSignedX</Type>
					<TimestampCreate>05/09/2016 14:34:34:937</TimestampCreate>
				</Status>
				<Status>
					<Current>false</Current>
					<Active>No</Active>
					<Date>12/30/2014</Date>
					<Type Word="DRAFT">Draft</Type>
					<TimestampCreate>05/09/2016 14:34:14:987</TimestampCreate>
				</Status>
			</Statuses>
		</ProtectionOrder>
	</ProtectionOrders>
</Integration>

My technique assumes that there are not a huge number of <Status> nodes (though I know a case can get a lot of status changes).

First, I create a global variable, named cpTime, that contains the timestamp contained in the control point, converted from the local culture AM/PM representation, to a normalized 14 character string, YYYYMMDDHHMMSS.

Then, where I want to insert the correct <Statuses> <Status> nodes, I use an apply-templates that sorts the node-set in ascending timestamp order. This is assuming that you want the chronologically earliest node when two nodes are recorded in the same second. To choose the latest such node, simply add order="descending" attribute to each of the <xsl:sort> instructions.

This results in a string that has the node ID and timestamp value of all the nodes that have timestamps that match the control point timestamp. The id of the first such node is retreived from the string and is used to make a copy of the <Status> node, replacing the <TimestampCreate> value with the normalized value.

So, on to the XSLT:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:output method="xml" indent="yes" encoding="utf-8"/>

<xsl:variable name="cpTime"><xsl:call-template name="convertAMPMTime">
    <xsl:with-param name="ts" select="normalize-space(Integration/ControlPoint/@Timestamp)"/>
</xsl:call-template></xsl:variable>


<xsl:template match="/">
<xsl:variable name="matchingStatusNodes"><xsl:apply-templates select="Integration/ProtectionOrders/ProtectionOrder/Statuses/Status" mode="getStatusId">
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(substring-after(substring-after(TimestampCreate,'/'),'/'),' ')"/><!-- year -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(TimestampCreate,'/')"/><!-- month -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(substring-after(TimestampCreate,'/'),'/')"/><!-- day -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(substring-after(TimestampCreate,' '),':')"/><!-- hour -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(substring-after(TimestampCreate,':'),':')"/><!-- minute -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-before(substring-after(substring-after(TimestampCreate,':'),':'),' ')"/><!-- second -->
                                            <xsl:sort data-type="number" 
                                                      select="substring-after(TimestampCreate,'.')"/><!-- millisecond -->
                                         </xsl:apply-templates></xsl:variable>
<xsl:variable name="firstMatchingNode" select="substring-before($matchingStatusNodes,' ')"/>
<Statuses>
<xsl:for-each select="//*[generate-id() = $firstMatchingNode]">
<xsl:copy><xsl:apply-templates mode="copyStatus" select="@* | *">
            <xsl:with-param name="ts" select="substring-before(substring-after($matchingStatusNodes,' '),' ')"/>
          </xsl:apply-templates>
</xsl:copy>
</xsl:for-each> 
</Statuses>
</xsl:template>

<xsl:template mode="copyStatus" match="TimestampCreate">
<xsl:param name="ts"/>
<TimestampCreate><xsl:value-of select="$ts"/></TimestampCreate>
</xsl:template>

<xsl:template mode="copyStatus" match="@* | *">
<xsl:param name="ts"/>
<xsl:copy-of select="."/> 
</xsl:template>

<xsl:template match="Status" mode="getStatusId">
<xsl:variable name="year"   select="format-number(substring-before(substring-after(substring-after(TimestampCreate,'/'),'/'),' '),'0000')"/>
<xsl:variable name="month"  select="format-number(substring-before(TimestampCreate,'/'),'00')"/>
<xsl:variable name="day"    select="format-number(substring-before(substring-after(TimestampCreate,'/'),'/'),'00')"/>
<xsl:variable name="hour"   select="format-number(substring-before(substring-after(TimestampCreate,' '),':'),'00')"/>
<xsl:variable name="minute" select="format-number(substring-before(substring-after(TimestampCreate,':'),':'),'00')"/>
<xsl:variable name="second" select="format-number(substring-before(substring-after(substring-after(TimestampCreate,':'),':'),':'),'00')"/>
<xsl:variable name="msec"   select="format-number(substring-after(substring-after(substring-after(TimestampCreate,':'),':'),':'),'00')"/>
<xsl:variable name="myTimestamp" select="concat($year,$month,$day,$hour,$minute,$second)"/>
<xsl:if test="$myTimestamp = $cpTime">
<xsl:value-of select="concat(generate-id(.),' ', $myTimestamp,$msec,' ')"/>
</xsl:if>
</xsl:template>

<xsl:template name="convertAMPMTime">
<xsl:param name="ts"/>
<xsl:variable name="dPart" select="substring-before(normalize-space($ts),' ')"/>
<xsl:variable name="tPart" select="substring-before(substring-after(normalize-space($ts),' '),' ')"/>
<xsl:variable name="AMPM" select="substring-after(substring-after(normalize-space($ts),' '),' ')"/>
<xsl:variable name="year" select="format-number(substring-after(substring-after($dPart,'/'),'/'),'0000')"/>
<xsl:variable name="month" select="format-number(substring-before($dPart,'/'),'00')"/>
<xsl:variable name="day" select="format-number(substring-before(substring-after($dPart,'/'),'/'),'00')"/>
<xsl:variable name="tHour" select="format-number(substring-before($tPart,':'),'00')"/>
<xsl:variable name="minute" select="format-number(substring-before(substring-after($tPart,':'),':'),'00')"/>
<xsl:variable name="second" select="format-number(substring-after(substring-after($tPart,':'),':'),'00')"/>
<xsl:variable name="hour"><xsl:choose>
                          <xsl:when test="$AMPM = 'AM'"><xsl:choose>
                                                        <xsl:when test="$tHour = '12'">00</xsl:when>
                                                        <xsl:otherwise><xsl:value-of select="$tHour"/></xsl:otherwise>
                                                        </xsl:choose></xsl:when>
                          <xsl:otherwise><xsl:choose>
                                         <xsl:when test="$tHour = '12'">12</xsl:when>
                                         <xsl:otherwise><xsl:value-of select="format-number($tHour + 12,'00')"/></xsl:otherwise>
                                         </xsl:choose></xsl:otherwise>
                          </xsl:choose></xsl:variable>
<xsl:value-of select="concat($year,$month,$day,$hour,$minute,$second)"/>
</xsl:template>

</xsl:stylesheet>

And here is the result:

<?xml version='1.0' encoding='utf-8' ?>
<Statuses>
  <Status Op="A">
    <Current>false</Current>
    <Active>Yes</Active>
    <Date Op="A">05/09/2016</Date>
    <Type Op="A" Word="SBJO">Signed</Type>
    <TimestampCreate>20160509143434737</TimestampCreate>
  </Status>
</Statuses>

Feel free to ask questions...

Tom Morrison
Hill Country Software

 

[k5tm]

By the way, the timestamp conversion routines may look like a lot of code, but they are something I keep in my toolbox of helpful templates. I find that there is a lot of need for date conversion when integrating XML based data into legacy systems.

Tom Morrison
Hill Country Software

 

[momo2000]

Hello k5tm
Thanks for your help. My boss asked me to use the function/s we have instead.
We have 2 functions. One function converts TimestampCreate into numeric.
The other function converts ControlPoint TimeStamp. This second function is used because our system has a problem converting TimeStamp. This function is fixOdysseyTimestamp

Here is the template with the 2 function I am supposed to call.
Here is an example of how I used the two function to compare TimestampCreate with a date (20160608170000) in numeric format.

xsl:when test="mscef:formatDateTimeNumeric(mscef:fixOdysseyTimestamp(string($vTimeStampCreate))) < 20160608170000">

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] xmlns:mscef="courts.state.mn.us/extfun" xmlns:msxsl="urn:schemas-microsoft-com:xslt" extension-element-prefixes="mscef msxsl" exclude-result-prefixes="mscef msxsl"> 
	<msxsl:script language="JScript" implements-prefix="mscef">
		<![CDATA[

		function formatDateTimeNumeric(sDate){
			if(sDate.length==0){
				return "";
			}
			else{
				var oDate=new Date(sDate);
				var str = oDate.getSeconds();
				return "" + oDate.getFullYear().toString() + padZeroes(oDate.getMonth() + 1,2) + padZeroes(oDate.getDate(),2) + padZeroes(oDate.getHours().toString(),2) + padZeroes(oDate.getMinutes(),2) + padZeroes(oDate.getSeconds(),2);  			
			}
		}

		function fixOdysseyTimestamp(sDate){
		/* Replace the ":" between seconds and miliseconds */
			if(sDate.length==0){
				return "";
			}
			else{
				var strParts1 = sDate.split(" ");
				var strTime = strParts1[1];
				var strParts2 = strTime.split(":");
				return strParts1[0] + " " + strParts2[0] + ":" + strParts2[1] + ":" + strParts2[2];
			}
		}
	]]>
	</msxsl:script>   
</xsl:stylesheet>

 

[k5tm]

Well, I am not one to overrule your boss. I just don't see the benefit of combining three technology requirements into a relatively simple one technology problem. But what do I know, after only 48 years in the business, because everyone knows that it takes at least 50, or less than 5, to be a true expert...

In my opinion, there is absolutely no need to tie this solution to JScript or Micro$oft'$ version of XSL extensions. When you get promoted to boss, you will have to find a programmer who understands XSL, JScript and Microsoft's XSL extension mechanism to maintain your old code. And, you have built in additional points of failure when M$ decides to drop its support of XSL 1.0. (Is your boss aware of MS's inattention - if not downright hostility - to XSL? Has MS even deigned to produce an XSL 2.0 compliant processor, when the standard is now moving to 3.0?)

My day job for the last few months has been to build an integration for the same Tyler product for a CMS coded in a legacy language. Every bit of the XML processing (and there is a lot of it) is in version 1.0, no extensions, no reliance on proprietary gimcracks like JScript. And, surprisingly, it is not wildly convoluted. As I noted above, I have a bag of tricks - mostly callable XSL templates that act as -- functions.

So, not to get too cranky , but I am going to limit my help to XSL. If your functions produce what I call 'normalized timestamp strings' (i.e. YYYYMMDDHHMMSSmmm) then you should be able to plug in the functions in appropriate spots in my proposed solution.



Tom Morrison
Hill Country Software

 

[momo2000]

Thanks again Tom. I like what you said. I don't know what my boss's thinking is and or why we have created these (various function in js) I will try to do what you suggested i.e. plug in the functions in appropriate spots in your proposed solution. I don't know how but I will try. Your suggested solution worked and I feel my energy was sucked out when I found out that I need to call our functions!

 

[k5tm]

Well, sorry to get you down. It was not my intent.

Looking at your functions, I can tell from the expressions on the return statements that these functions do not return results that can be directly used in any comparison. But perhaps the output of fixOdysseyTimestamp can be passed to formatDateTimeNumeric so that you get strings that are identically formatted.

If I understand your requirement, you have to test for equality down to the second. Only the leftmost n characters need to be tested for equality (where n is the number of characters needed to compare everything except the milliseconds), suggesting substring()

And, nothing you have said removes the need to sort, so the sort instructions remain the same.

Tom Morrison
Hill Country Software

 

[momo2000]

I will continue to work on this one. It seems too complicated though.

Here is my xslt 1.0 code

<ProtectionOrderStatus>
	<ProtectionOrderStatusCode>
		<xsl:value-of select="Statuses/Status/Type/@Word"/>
	</ProtectionOrderStatusCode>
<ProtectionOrderStatusDate>
	<xsl:value-of select="mscef:formatDate(string(Statuses/Status[Current='true']/Date))"/>
	<xsl:value-of select="mscef:formatDate(string(Statuses/Status/Date))"/>
</ProtectionOrderStatusDate>
</ProtectionOrderStatus>

Here is expected result

<ProtectionOrderStatus>
	<ProtectionOrderStatusCode>SBJO</ProtectionOrderStatusCode>
	<ProtectionOrderStatusDate>2016-05-09</ProtectionOrderStatusDate>
</ProtectionOrderStatus>

Here is what my current xslt code is returning which is wrong

<ProtectionOrderStatus>
	<ProtectionOrderStatusCode>DISMISSED</ProtectionOrderStatusCode>
	<ProtectionOrderStatusDate>2016-05-09</ProtectionOrderStatusDate>
</ProtectionOrderStatus>

 

[k5tm]

Here is my XSLT, converted to use your functions (had to supply PadZeroes):

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL]
							xmlns:mscef="courts.state.mn.us/extfun" 
							xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
							extension-element-prefixes="mscef msxsl" 
							exclude-result-prefixes="mscef msxsl">

<xsl:output method="xml" indent="yes" encoding="utf-8"/>

<msxsl:script language="JScript" implements-prefix="mscef">
	<![CDATA[
	function formatDateTimeNumeric(sDate){
		if(sDate.length==0){
			return "";
		}
		else{
			var oDate=new Date(sDate);
			var str = oDate.getSeconds();
			return "" + oDate.getFullYear().toString() + padZeroes(oDate.getMonth() + 1,2) + padZeroes(oDate.getDate(),2) + padZeroes(oDate.getHours().toString(),2) + padZeroes(oDate.getMinutes(),2) + padZeroes(oDate.getSeconds(),2);  			
		}
	}
	function fixOdysseyTimestamp(sDate){
	/* Replace the ":" between seconds and miliseconds */
		if(sDate.length==0){
			return "";
		}
		else{
			var strParts1 = sDate.split(" ");
			var strTime = strParts1[1];
			var strParts2 = strTime.split(":");
			return strParts1[0] + " " + strParts2[0] + ":" + strParts2[1] + ":" + strParts2[2];
		}
	}
	function padZeroes(n, width, z) {
	  z = z || '0';
	  n = n + '';
	  return n.length >= width ? n : new Array(width - n.length + 1).join(z) + n;
	}
]]>
</msxsl:script>   

<xsl:variable name="cpTime" select="mscef:formatDateTimeNumeric(string(Integration/ControlPoint/@Timestamp))"/>


<xsl:template match="/">
<xsl:variable name="matchingStatusNodes"><xsl:apply-templates select="Integration/ProtectionOrders/ProtectionOrder/Statuses/Status" mode="getStatusId">
											<xsl:sort data-type="number" 
											          select="substring-before(substring-after(substring-after(TimestampCreate,'/'),'/'),' ')"/><!-- year -->
											<xsl:sort data-type="number" 
											          select="substring-before(TimestampCreate,'/')"/><!-- month -->
											<xsl:sort data-type="number" 
											          select="substring-before(substring-after(TimestampCreate,'/'),'/')"/><!-- day -->
											<xsl:sort data-type="number" 
											          select="substring-before(substring-after(TimestampCreate,' '),':')"/><!-- hour -->
											<xsl:sort data-type="number" 
											          select="substring-before(substring-after(TimestampCreate,':'),':')"/><!-- minute -->
											<xsl:sort data-type="number" 
											          select="substring-before(substring-after(substring-after(TimestampCreate,':'),':'),' ')"/><!-- second -->
											<xsl:sort data-type="number" 
											          select="substring-after(TimestampCreate,'.')"/><!-- millisecond -->
										 </xsl:apply-templates></xsl:variable>
<xsl:variable name="firstMatchingNode" select="substring-before($matchingStatusNodes,' ')"/>
<Statuses>
<xsl:for-each select="//*[generate-id() = $firstMatchingNode]">
<xsl:copy><xsl:apply-templates mode="copyStatus" select="@* | *">
			<xsl:with-param name="ts" select="substring-before(substring-after($matchingStatusNodes,' '),' ')"/>
	      </xsl:apply-templates>
</xsl:copy>
</xsl:for-each> 
</Statuses>
</xsl:template>

<xsl:template mode="copyStatus" match="TimestampCreate">
<xsl:param name="ts"/>
<TimestampCreate><xsl:value-of select="$ts"/></TimestampCreate>
</xsl:template>

<xsl:template mode="copyStatus" match="@* | *">
<xsl:param name="ts"/>
<xsl:copy-of select="."/> 
</xsl:template>

<xsl:template match="Status" mode="getStatusId">
<xsl:variable name="myTimestamp" select="mscef:formatDateTimeNumeric(mscef:fixOdysseyTimestamp(string(TimestampCreate)))"/> 
<xsl:if test="$myTimestamp = $cpTime">
<xsl:value-of select="concat(generate-id(.),' ', $myTimestamp,' ')"/>
</xsl:if>
</xsl:template>


</xsl:stylesheet>

Result:

<?xml version="1.0" encoding="utf-8"?>
<Statuses>
<Status Op="A">
<Current>false</Current>
<Active>Yes</Active>
<Date Op="A">05/09/2016</Date>
<Type Op="A" Word="SBJO">Signed</Type>
<TimestampCreate>20160509143434</TimestampCreate>
</Status>
</Statuses>

You can limit the <Status> nodes that are considered by either (1) using XPath predicates (those things in square brackets) on the apply-templates select, or (2) you can use <xsl:if> in the template (mode getStatusId) to limit which <Status> nodes get to add their generate-id() values.

Tom Morrison
Hill Country Software