xml - XSLT Muenchian 分组和编码错误
问题描述
我在围绕 XSLT 编码的语法方面遇到问题。显然有什么错误或遗漏,但我无法弄清楚。
XML 文件从数据库中的一系列收入、扣除和税码中获取金额。当少数变量(描述、位置和成本中心)相同时,需要对大多数收入代码进行分组和求和,这就是 Muenchian 分组发挥作用的地方。
John 在 ABC 地点担任技术员,并获得以下收入:定期、假期和加班。这些收入的金额需要在输出中显示为总和值,并分组到称为总收入的组中。扣除额和税款不需要汇总。
下面是 XML 代码和下面的 XSLT 代码。关于如何解决这个问题的任何想法?
<?xml version="1.0" encoding="UTF-8"?>
<GLPayrollExportConfiguration>
<definitions>
<FileType Value="XML"/>
<FileName>
<Value Value="GL_Summary"/>
<Value Value="_"/>
<Value Value="paygroup_xref"/>
<Value Value="_"/>
<Value Value="payrun_payperiod_and_suffix"/>
<Value Value="_"/>
<Value Value="transaction_timestamp"/>
<Value Value=".csv"/>
</FileName>
<Settings>
<SplitMode Value="HOME_LOCATION_SPLIT"/>
<ChartOfAccountXRefCode Value="GL"/>
<RunOnPayRunCommit Value="True"/>
</Settings>
<Columns>
<Column Name="ColA" DataType="string" Source="mapping"/>
<Column Name="ColB" DataType="string" Source="data"/>
<Column Name="ColC" DataType="string" Source="data"/>
<Column Name="ColD" DataType="string" Source="data"/>
<Column Name="PayDate" DataType="datetime" Source="data" Value="payrun_pay_date" Sort="true"/>
<Column Name="PeriodEnd" DataType="datetime" Source="data" Value="payrun_pay_date" Sort="true"/>
<Column Name="ColG" DataType="string" Source="data"/>
<Column Name="AccountDesc" DataType="string" Source="data" Value="payrun_category_override_description" Sort="true" Function="group"/>
<Column Name="PayrunCategoryName" DataType="string" Source="data" Value="payrun_category_name" Sort="true" Function="group"/>
<Column Name="SiteName" DateType="string" Source="data" Value="charged_phys_loc_desc" Sort="true"/>
<Column Name="Amount" DataType="number" Source="mapping" Value="payrun_amount" Function="sum" />
<Column Name="GLAccount" DataType="string" Source="data" Value="override_segment_charged_position"/>
<Column Name="ColL" DataType="string" Source="data"/>
<Column Name="ColM" DataType="string" Source="data"/>
<Column Name="ColN" DataType="string" Source="data"/>
</Columns>
</definitions>
<MappingDefinitions>
<definition>
<criteriaset>
<criteria item="payrun_category_override_journal_number" op="ne">EXCLUDE</criteria>
<criteria item="payrun_category_override_journal_number" op="ne">Exclude</criteria>
<criteria item="payrun_category_override_journal_number" op="ne">exclude</criteria>
<criteria item="payrun_amount" op="ne" opDataType="number">0</criteria>
<criteria item="charged_phys_loc_desc" op="ne">ReliantRealtyServicesLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">BronxParkPhaseI</criteria>
<criteria item="charged_phys_loc_desc" op="ne">BronxParkPhaseII</criteria>
<criteria item="charged_phys_loc_desc" op="ne">BronxParkPhaseIII</criteria>
<criteria item="charged_phys_loc_desc" op="ne">OmniNYLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">OMPOfficeLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">ReliantRealtyServicesLLC3rdParty</criteria>
<criteria item="charged_phys_loc_desc" op="ne">ReliantSafetyLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">ReliantSafetyLLC3rdParty</criteria>
<criteria item="charged_phys_loc_desc" op="ne">RenewalConstructionServicesLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">RMSPartnersLLC</criteria>
<criteria item="charged_phys_loc_desc" op="ne">OMPParksideLP</criteria>
</criteriaset>
<mapping>
<mapto columnname="ColA">
<Value Value="J"/>
</mapto>
<mapto columnname="Amount">
<Value Value="	"/><Value Value="payrun_amount"/>
</mapto>
</mapping>
</definition>
</MappingDefinitions>
<ColumnFormats>
<ColumnFormat Name="ColA"/>
<ColumnFormat Name="ColB"/>
<ColumnFormat Name="ColC"/>
<ColumnFormat Name="ColD"/>
<ColumnFormat Name="PayDate" Format="MM/dd/yy"/>
<ColumnFormat Name="PeriodEnd" Format="MM/dd/yy"/>
<ColumnFormat Name="ColG"/>
<ColumnFormat Name="AccountDesc"/>
<ColumnFormat Name="SiteName"/>
<ColumnFormat Name="Amount" Format="{0:##.00}"/>
<ColumnFormat Name="GLAccount" WrapChar="""/>
<ColumnFormat Name="ColL"/>
<ColumnFormat Name="ColM"/>
<ColumnFormat Name="ColN"/>
</ColumnFormats>
</GLPayrollExportConfiguration>
这是我的 XSLT-1.0 代码:
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:cs="urn:cs" exclude-result-prefixes="xs">
<msxsl:script language="C#" implements-prefix="cs">
<msxsl:using namespace="System.IO"/>
<![CDATA[
public static int DayDifference(string rehiredate,string termdate)
{
String rehire=rehiredate;
String term=termdate;
if (string.IsNullOrEmpty(rehiredate)==true || string.IsNullOrEmpty(termdate)==true) return 0;
DateTime d1 = DateTime.Parse(rehire.Substring(6,4) + '-' + rehire.Substring(0,2) + '-' + rehire.Substring(3,2) );
DateTime d2 = DateTime.Parse(term.Substring(6,4) + '-' + term.Substring(0,2) + '-' + term.Substring(3,2) );
return (d1 - d2).Days;
}
public static int DayDifferenceSys(string rehiredate)
{
String rehire=rehiredate;
DateTime CurrentDate= DateTime.Now;
if (string.IsNullOrEmpty(rehiredate)==true) return 0;
DateTime d1 = DateTime.Parse(rehire.Substring(6,4) + '-' + rehire.Substring(0,2) + '-' + rehire.Substring(3,2));
return CurrentDate.Subtract(d1).Days;
}
public string DateAdd(string s1,int i){
DateTime d1= DateTime.Parse(s1.Substring(6,4) + '-' + s1.Substring(0,2) + '-' + s1.Substring(3,2));
return d1.AddDays(i).ToString("yyyy-MM-dd");
}
]]>
</msxsl:script>
<xsl:output method="text"/>
<xsl:key name="GrossEarnings" match="PayrunCategoryName" use="AccountDesc"/>
<xsl:template match="PayrunCategoryName">
<xsl:apply-templates select="key(GrossEarnings, AccountDesc)" />
<xsl:text>
</xsl:text>
<xsl:for-each select="Export/Record[Amount!=0 and group(.|key('key_GrossEarnings', concat(PayrunCategoryName))[1])=1]">
<xsl:value-of select="ColA"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColB"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColC"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColD"/><xsl:text>,</xsl:text>
<xsl:value-of select="PayDate"/><xsl:text>,</xsl:text>
<xsl:value-of select="PeriodEnd"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColG"/><xsl:text>,</xsl:text>
<xsl:value-of select="AccountDesc"/><xsl:text>,</xsl:text>
<xsl:value-of select="GrossEarnings"/><xsl:text>,</xsl:text>
<xsl:value-of select="SiteName"/><xsl:text>,</xsl:text>
<xsl:value-of select="Amount"/><xsl:text>,</xsl:text>
<xsl:choose>
<xsl:when test="GrossEarnings">
<xsl:value-of select="sum(Amount)" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="Amount"/>
</xsl:otherwise>
<xsl:text>,</xsl:text>
</xsl:choose>
<xsl:text>,</xsl:text>
<xsl:value-of select="GLAccount"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColL"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColM"/><xsl:text>,</xsl:text>
<xsl:value-of select="ColN"/><xsl:text>,</xsl:text>
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
编辑:谢谢@zx485。如果 George 有定期、病假和假期的收入,在 ABC 网站工作,并且收入分配给 Cost Ctr 12345,我正在寻找将收入汇总到一个名为 Gross Earnings 的交易以及由此产生的总价值。