使用份额汇总动态多个项目成本 [英] Summarizing dynamic multiple project costs with shares
问题描述
我需要计算过帐到会计系统的总和，考虑到它们可能是在转移时分配的多个项目，具有不同的总数份额.
I need to calculate sums on postings to an accounting system taking into consideration their might be multiple projects assigned on the transfer with different shares of the totals.
以下是我的 XML 的简化片段:
Below is a simplified snippet of my XML:
<DATA>
<ACCOUNTING>
<POSTINGS>
<POSTING id="1">
<TYPE>D</TYPE>
<ACCOUNT_ID NAME="Expense Account 555777">555777</ACCOUNT_ID>
<AMOUNT>700</AMOUNT>
<DESCRIPTION>Expense1</DESCRIPTION>
<PROJECT_ID NAME="abc">123</PROJECT_ID>
<SHARE>70</SHARE>
</POSTING>
<POSTING id="2">
<TYPE>D</TYPE>
<ACCOUNT_ID NAME="Expense Account 555777">555777</ACCOUNT_ID>
<AMOUNT>300</AMOUNT>
<DESCRIPTION>Expense1</DESCRIPTION>
<PROJECT_ID NAME="def">456</PROJECT_ID>
<SHARE>30</SHARE>
</POSTING>
<POSTING id="5">
<TYPE>C</TYPE>
<ACCOUNT_ID NAME="Credit Account 111333">111333</ACCOUNT_ID>
<AMOUNT>-1000</AMOUNT>
<DESCRIPTION>Expense1</DESCRIPTION>
</POSTING>
<POSTING id="6">
<TYPE>D</TYPE>
<ACCOUNT_ID NAME="Expense Account 666999">666999</ACCOUNT_ID>
<AMOUNT>7000</AMOUNT>
<DESCRIPTION>Expense2</DESCRIPTION>
<PROJECT_ID NAME="abc">123</PROJECT_ID>
<SHARE>70</SHARE>
</POSTING>
<POSTING id="7">
<TYPE>D</TYPE>
<ACCOUNT_ID NAME="Expense Account 666999">666999</ACCOUNT_ID>
<AMOUNT>3000</AMOUNT>
<DESCRIPTION>Expense2</DESCRIPTION>
<PROJECT_ID NAME="def">456</PROJECT_ID>
<SHARE>30</SHARE>
</POSTING>
<POSTING id="10">
<TYPE>C</TYPE>
<ACCOUNT_ID NAME="Credit Account 444888">444888</ACCOUNT_ID>
<AMOUNT>-10000</AMOUNT>
<DESCRIPTION>Expense2</DESCRIPTION>
</POSTING>
</POSTINGS>
</ACCOUNTING>
</DATA>
在这个例子中，转移分配了两个项目:
In this example the transfer has assigned two projects:
"abc" having id '123' with a share of 70 %
"def" having id '456' with a share of 30 %
转移包括两个过帐:
"Expense1" with the amount of 1,000
"Expense2" with the amount of 10,000
理想情况下，我需要根据项目的份额百分比总结每个项目的借方 (TYPE='D') 过帐，并显示带有值的消息.
Ideally I need to summarize the debit (TYPE='D') postings per project given their share percents, and show a message with the values.
在上面的例子中，这将导致:
In above example this would result in:
Project "abc" has an amount of 7700
Project "def" has an amount of 3300
我遗漏了税务信息.如果解决方案可以从总计中排除这些税收过帐，那就太好了.税务过帐将通过以下方式标识:
I have left out tax postings. Would be great if the solution could exclude those tax posting from the totals. Tax postings will be identified by:
<ACCOUNT_ID NAME="Tax Account">
AMOUNT 字段包含计算的共享过帐，这意味着无需先计算金额，然后再将其添加到项目的总和中.
The AMOUNT fields contains the calculated shared posting, meaning there's no need to first calculate the amount before adding it to the project's total sum.
希望这是有道理的.
更新 2015-03-20
Update 2015-03-20
谢谢迈克尔，
那个指南让我找到了正确的方向.
That guide put me in the right direction.
我进行了分组，现在可以使用项目名称引用和单个金额输出每个项目的消息，但无法执行金额的总和:
I got the grouping to work, and am now able to output messages per project with the project name reference and the individual amounts, but not able to perform the summation of the amounts:
<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxml="urn:schemas-microsoft-com:xslt" extension-element-prefixes="msxml">
<xsl:key name="postings-by-project" match="POSTING" use="PROJECT_ID" />
<xsl:template match="/DATA">
<messages>
<message class="1">
<xsl:for-each select="(ACCOUNTING/POSTINGS/POSTING[count(. | key('postings-by-project', PROJECT_ID)[1]) = 1])">
<xsl:sort select="PROJECT_ID" />
Project "<xsl:value-of select="PROJECT_ID" />" has an amount of
<xsl:for-each select="key('postings-by-project', PROJECT_ID)">
<xsl:sort select="AMOUNT" />
<xsl:if test="(TYPE='D')">
<xsl:if test="not(ACCOUNT_ID/@NAME = 'Tax Account')">
<xsl:value-of select="AMOUNT" />+
</xsl:if>
</xsl:if>
</xsl:for-each>
<BR>
</xsl:for-each>
</message>
</messages>
</xsl:template>
</xsl:stylesheet>
输出:
Project "38305" has an amount of 56+ 595+
Project "70491" has an amount of 24+ 255+
以上是通过实际过账的实际项目进行的测试，代码正确排除了税行.
Above was tested with actual projects with actual postings, and the code correctly excludes the tax lines.
现在我只需要进行最终求和.
Now I just need to perform final summation.
我已经尝试在 for-each POSTING 循环中创建变量并在最后一个 position() 中对它们进行 sum()，但随后只写入了第一个 Amount:
I've tried inside the for-each POSTING loop to create variables and sum() them in last position(), but then only the first Amount is written:
<xsl:variable name="vAmount"><number><xsl:value-of select="AMOUNT"/></number></xsl:variable>
<xsl:choose>
<xsl:when test="position() = last()">
<message class="1">
<xsl:value-of select="sum(msxml:node-set($vAmount))"/>
</message>
</xsl:when>
</xsl:choose>
推荐答案
很难弄清楚您到底在追求什么，主要是因为您的示例令人困惑.您的第一个示例包含没有 PROJECT_ID 的 POSTING，并且您显示的结果与实际金额不符.在任何情况下，都可以使用 sum() 函数轻松完成求和 - 无需任何详细说明.
It is quite difficult to figure what exactly you're after, mainly because your examples are confusing. Your first example has POSTINGs without PROJECT_ID, and the results you show don't match the actual amounts. In any case, summing can be done easily by using the sum() function - no need for anything elaborate.
我建议你看下面的简化例子:
I suggest you look at the following simplified example:
XML
<DATA>
<ACCOUNTING>
<POSTINGS>
<POSTING id="1">
<ACCOUNT_ID NAME="Expense Account 555777">555777</ACCOUNT_ID>
<AMOUNT>700</AMOUNT>
<PROJECT_ID NAME="abc">123</PROJECT_ID>
</POSTING>
<POSTING id="2">
<ACCOUNT_ID NAME="Expense Account 555777">555777</ACCOUNT_ID>
<AMOUNT>300</AMOUNT>
<PROJECT_ID NAME="def">456</PROJECT_ID>
</POSTING>
<POSTING id="6">
<ACCOUNT_ID NAME="Expense Account 666999">666999</ACCOUNT_ID>
<AMOUNT>7000</AMOUNT>
<PROJECT_ID NAME="abc">123</PROJECT_ID>
</POSTING>
<POSTING id="7">
<ACCOUNT_ID NAME="Expense Account 666999">666999</ACCOUNT_ID>
<AMOUNT>3000</AMOUNT>
<PROJECT_ID NAME="def">456</PROJECT_ID>
</POSTING>
<POSTING id="8">
<ACCOUNT_ID NAME="Tax Account">1000001</ACCOUNT_ID>
<AMOUNT>600</AMOUNT>
<PROJECT_ID NAME="def">456</PROJECT_ID>
</POSTING>
</POSTINGS>
</ACCOUNTING>
</DATA>
应用以下样式表:
XSLT 1.0
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
<xsl:key name="postings-by-project" match="POSTING" use="PROJECT_ID" />
<xsl:template match="/DATA">
<result>
<xsl:for-each select="ACCOUNTING/POSTINGS/POSTING[count(. | key('postings-by-project', PROJECT_ID)[1]) = 1]">
<project name="{PROJECT_ID/@NAME}">
<amounts>
<xsl:for-each select="key('postings-by-project', PROJECT_ID)">
<amount account="{ACCOUNT_ID/@NAME}">
<xsl:value-of select="AMOUNT" />
</amount>
</xsl:for-each>
</amounts>
<total>
<xsl:value-of select="sum(key('postings-by-project', PROJECT_ID)/AMOUNT)" />
</total>
<total-without-tax>
<xsl:value-of select="sum(key('postings-by-project', PROJECT_ID)[not(ACCOUNT_ID/@NAME='Tax Account')]/AMOUNT)" />
</total-without-tax>
</project>
</xsl:for-each>
</result>
</xsl:template>
</xsl:stylesheet>
将产生以下结果:
<?xml version="1.0" encoding="utf-8"?>
<result>
<project name="abc">
<amounts>
<amount account="Expense Account 555777">700</amount>
<amount account="Expense Account 666999">7000</amount>
</amounts>
<total>7700</total>
<total-without-tax>7700</total-without-tax>
</project>
<project name="def">
<amounts>
<amount account="Expense Account 555777">300</amount>
<amount account="Expense Account 666999">3000</amount>
<amount account="Tax Account">600</amount>
</amounts>
<total>3900</total>
<total-without-tax>3300</total-without-tax>
</project>
</result>
为了方便，我把结果输出做成了XML，方便大家一目了然.
For convenience, I have made the result output XML, so that you can easily see what is what.
这篇关于使用份额汇总动态多个项目成本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
