Atšķirības

Norādītais vecais variants no patreizējās lapas atšķiras ar:

--- lv:procedures [2020/10/01 15:30]
marija dentoflex pvn
+++ lv:procedures [2020/10/27 19:06] (patlaban)
marija ALG_rek_0610
@@ Rinda 1: / Rinda 1: @@
-<code sql>
+<code>
-ALTER PROCEDURE dbo.int_hooldus_klient_pvn @aeg1 datetime, @aeg2 datetime AS
+<?xml version="1.0" encoding="utf-8"?>
+<xsl:stylesheet version="1.0"
---static vars
+xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
-DECLARE @vat_declar_name nvarchar(200) SET @vat_declar_name = 'PVN'
+xmlns:fo="http://www.w3.org/1999/XSL/Format"
-DECLARE @stat_vat decimal SET @stat_vat = 21
+xmlns:msxsl="urn:schemas-microsoft-com:xslt"
-DECLARE @lim decimal SET @lim = ISNULL((SELECT TOP 1 CAST(ISNULL(kontod, 1000) AS decimal) FROM fin_aru_kaive_read WITH (NOLOCK) WHERE number=9000 AND kood=@vat_declar_name AND tyyp = 3), 1000)
+xmlns:js="urn:formulas"
-DECLARE @account int SET @account = 57211
+exclude-result-prefixes="msxsl js fo">
-DECLARE @i int SET @i = 1
+<xsl:output method="html"/>
-DECLARE @x nvarchar(10) SET @x = '1'
+<xsl:decimal-format name="N" NaN="0" decimal-separator='.' grouping-separator=' ' />
-DECLARE @y nvarchar(10) SET @y = '1'
+  <xsl:template match="/">
+   <xsl:variable name="item_per_page">25</xsl:variable>
---numbers helper
+ <xsl:variable name="eiro">
-DECLARE @numbers TABLE (n int) INSERT INTO @numbers
+		<xsl:choose>
-  SELECT TOP 1000 row_number() over(ORDER BY t1.number)
+		<xsl:when test="/documents/document/valuuta='LVL'">
-  FROM master..spt_values t1
+.42287181
-  CROSS JOIN master..spt_values t2
+		</xsl:when>
---vat summary
+		<xsl:when test="/documents/document/valuuta=''">
-DECLARE @vat_declar_sums TABLE
+.42287181
-(
+		</xsl:when>
-  row_nr int,
+		<xsl:when test="/documents/document/valuuta='EUR'">
-  row_type int,
+.702804
-  c_class int,
+		</xsl:when>
-  vat_code nvarchar(30),
+		<xsl:when test="/documents/document/valuuta!='EUR' and  /documents/document/valuuta!='LVL'">
-  nr nvarchar(30),
+		<xsl:value-of select="/documents/document/kurssbv1"/>
-  description nvarchar(200),
+		</xsl:when>
-  range_formula nvarchar(200),
+		</xsl:choose>
-  doc_sum decimal(15,2)
+		 </xsl:variable>
-)
+		  <xsl:variable name="nauda">
-INSERT INTO @vat_declar_sums
+		<xsl:choose>
-  SELECT ROW_NUMBER() OVER (ORDER BY kood), tyyp, kontoklass, kmkood, number, tekst, kontod, 0
+		<xsl:when test="/documents/document/valuuta='LVL'">LVL</xsl:when>
-  FROM fin_aru_kaive_read WITH (NOLOCK)
+		<xsl:when test="/documents/document/valuuta=''">LVL</xsl:when>
-  WHERE kood=@vat_declar_name
+		<xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'">
-    AND (tyyp = 1 OR tyyp = 2 OR tyyp = 8  OR tyyp = 7)
+		<xsl:value-of select="/documents/document/valuuta"/>
-  ORDER BY number
+		</xsl:when>
+		</xsl:choose>
---account range elements
+		 </xsl:variable>
-DECLARE @elements TABLE (nr nvarchar(30), e nvarchar(200))
+		 <xsl:variable name="nauda2">
-INSERT INTO @elements
+		<xsl:choose>
-  SELECT
+		<xsl:when test="/documents/document/valuuta='LVL'">EUR</xsl:when>
-    nr,
+		<xsl:when test="/documents/document/valuuta=''">EUR</xsl:when>
-    RTRIM(LTRIM(SUBSTRING('+' + range_formula + '+',n+1,CHARINDEX('+','+' + range_formula + '+',n+1)-n-1)))
+		<xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'">LVL</xsl:when>
-  FROM @numbers, @vat_declar_sums
+		</xsl:choose>
-  WHERE n < LEN('+' + range_formula + '+')
+		 </xsl:variable>
-    AND SUBSTRING('+' + range_formula + '+',n,1) = '+'
+   <xsl:variable name="rekina_summa">
-    AND range_formula LIKE '_%'
+     <xsl:choose>
-    AND (row_type = 1 or row_type = 8 or row_type = 7)
+   <xsl:when test="$nauda='LVL'">
+  <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) * $eiro, '0.00')" />
---account ranges start-end
+   </xsl:when>
-DECLARE @element_ranges TABLE (row_nr int, nr nvarchar(30), e_start nvarchar(200), e_end nvarchar(200))
+     <xsl:when test="$nauda='EUR'">
-INSERT INTO @element_ranges
+  <xsl:value-of select="format-number(/documents/document/converted_tasuda div 100, '0.00')" />
-  SELECT
+   </xsl:when>
-    ROW_NUMBER() OVER (ORDER BY nr),
+   <xsl:when test="$nauda!='LVL' or $nauda!='EUR'">
-    nr,
+   <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/>
-    CASE WHEN CHARINDEX(':', e) > 0 THEN LEFT(e, CHARINDEX(':', e)-1) ELSE e END,
+   </xsl:when>
-    CASE WHEN CHARINDEX(':', e) > 0 THEN SUBSTRING(e, CHARINDEX(':', e)+1, LEN(e)) ELSE e END
+   </xsl:choose>
-  FROM @elements
+   </xsl:variable>
+   <xsl:variable name="saldo">
---update vat declaration sums according to account ranges
+   <xsl:choose>
-WHILE @i <= (SELECT COUNT(row_nr) FROM @element_ranges)
+   <xsl:when test="$nauda='LVL' or $nauda='EUR'">
-  BEGIN
+   <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/>
-    SET @x = (SELECT (CASE WHEN ISNUMERIC(er.e_start) = 1 THEN er.e_start ELSE '0' END) FROM @element_ranges er WHERE er.row_nr = @i)
+   </xsl:when>
-    SET @y = (SELECT (CASE WHEN ISNUMERIC(er.e_end) = 1 THEN er.e_end ELSE '0' END) FROM @element_ranges er WHERE er.row_nr = @i)
+   <xsl:when test="$nauda!='LVL' or $nauda!='EUR'">
+   <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/>
-    UPDATE @vat_declar_sums SET doc_sum = doc_sum +
+   </xsl:when>
-      ISNULL((
+   </xsl:choose>
-        SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0))
-        FROM fin_kanded_read WITH (NOLOCK)
+   </xsl:variable>
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+    <xsl:variable name="parads">
-          AND konto BETWEEN @x AND @y
+   <xsl:value-of select="format-number($saldo - $rekina_summa ,'0.00')"/>
-          AND kmkood = vat_code
+   </xsl:variable>
-      ),0)
+   <xsl:variable name="neapm_summa">
-    WHERE ISNUMERIC(vat_code) = 1 AND c_class = 3 and row_type=1
+   <xsl:choose>
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
+   <xsl:when test="/documents/document/valuuta!=''">
-	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
+   <xsl:value-of select="format-number($parads, '0.00')"/>
-      ISNULL((
+   </xsl:when>
-        SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0))
+   <xsl:when test="/documents/document/valuuta=''">
-        FROM fin_kanded_read WITH (NOLOCK)
+   <xsl:value-of select="$parads"/>
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+   </xsl:when>
-          AND konto BETWEEN @x AND @y
+   </xsl:choose>
-          AND kmkood = vat_code
+   </xsl:variable>
-      ),0)
+   <xsl:variable name="atlaide">
-    WHERE ISNUMERIC(vat_code) = 0 AND c_class = 3 and row_type=1
+    <xsl:choose><xsl:when test="sum(documents/document/rows/row[pross &gt; 0]/pross) &gt; 0">1</xsl:when>
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
+    <xsl:otherwise>0</xsl:otherwise>
+    </xsl:choose>
+   </xsl:variable>
+   <xsl:variable name="atlaides_summa">
+    <xsl:for-each select="/documents/document/rows/row[pross &gt; 0]">
+	<a_summa>
+	<xsl:value-of select="format-number(hind*kogus*pross div 100, '0.0000')"/>
+	</a_summa>
+    </xsl:for-each>
+   </xsl:variable>
+   <script language="JavaScript" type="text/javascript"><![CDATA[
+    if (document.getElementById('first')) {
+     document.write('<div class="pbreak"></div>');
+    } else {
+     document.write('<div id="first"></div>');
+    }
+   ]]></script>
+   <html>
+   <head>
+   <title><xsl:value-of select="/documents/language/arve_nr" /><!--Rēķins Nr.-->.  <xsl:value-of select="/documents/document/number" /></title>
+   <style type="text/css">
+    body {
+    text-align:center;
+    font-family:Century Gothic;
+    font-size:10px;
+    }
+    .container {
+    display:table;
+    text-align:left;
+    width:660px;
+    margin:auto;
+    }
+    .main_table {
+    width:660px;
+    border-collapse:collapse;
+    }
+    .main_table th {
+    font-size:14px;
+    font-family:Century Gothic;
+    font-weight:normal;
+    text-align:right;
+    padding:1px;
+    border:1px solid #B4B4B4;
+    }
+    .main_table td {
+    font-size:10px;
+    font-family:Century Gothic;
+    padding:1px;
+    border:1px solid #B4B4B4;
+    }
+    .main_table td.nos, .main_table td.kods, .main_table th.nos, .main_table th.kods {
+    text-align:left;
+    }
+    .main_table tr.cont {
+    height:16px;
+    }
+    .left {
+    float:left;
+    padding:30px 0px 0px 30px;
+    font-size:14px;
+    }
+    .right {
+    float:right;
+    text-align:right;
+    font-size:14px;
+    }
+	.left2 {
+    float:left;
+    text-align:left;
+    font-size:14px;
+    }
+    .right_small {
+    float:right;
+    text-align:right;
+    }
+    .spacer {
+    height:20px;
+    clear:both;
+    }
+    .divider {
+    clear:both;
+    }
+    .pbreak {
+    page-break-after:always;
+    }
+    h4.pb {
+    display:none;
+    }
+   </style>
+   </head>
+   <body>
+   <div class="container">
+    <div class="spacer" style="height:0px;"></div>
+    <div style="float:left;" class="left2" valign="bottom">
+		<IMG height="90" alt="Logo"><xsl:attribute name="SRC" ><xsl:value-of select="/documents/footer/firma_logo"/></xsl:attribute></IMG>
+    </div>
+    <div class="right">
+	<br />
+    <xsl:choose><!--Rēķins Nr.-->
+     <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel=''"><xsl:value-of select="/documents/document/maksetingimus_nimi" /> Nr.&#160;</xsl:when>
+     <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credit note no.&#160;</xsl:when>
+     <xsl:when test="/documents/document/keel=''">Rēķins Nr.&#160;</xsl:when>
+     <xsl:otherwise><xsl:value-of select="/documents/language/arve_nr" />.</xsl:otherwise></xsl:choose><xsl:value-of select="/documents/document/number" /><br />
+     <xsl:choose><!--Kreditē rēķinu Nr.-->
+     <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel=''">Kreditē rēķinu Nr.&#160;<xsl:value-of select="/documents/document/kredarve" /><br /></xsl:when>
+     <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credited invoice no.&#160;<xsl:value-of select="/documents/document/kredarve" /><br /></xsl:when>
+     </xsl:choose>
+     <xsl:choose><!--Datums-->
+     <xsl:when test="/documents/document/keel=''">Datums</xsl:when>
+     <xsl:otherwise><xsl:value-of select="/documents/language/aeg" /></xsl:otherwise></xsl:choose>:&#160;
+     <xsl:variable name="day" select="substring(/documents/document/aeg_date,1,2)" />
+     <xsl:variable name="month" select="substring(/documents/document/aeg_date,4,2)" />
+     <xsl:variable name="year" select="substring(/documents/document/aeg_date,7,4)" />
+     <xsl:variable name="menesis">
+      <xsl:choose>
+       <xsl:when test="/documents/document/keel=''">
+        <xsl:if test="$month='01'">Janvāris</xsl:if>
+        <xsl:if test="$month='02'">Februāris</xsl:if>
+        <xsl:if test="$month='03'">Marts</xsl:if>
+        <xsl:if test="$month='04'">Aprīlis</xsl:if>
+        <xsl:if test="$month='05'">Maijs</xsl:if>
+        <xsl:if test="$month='06'">Jūnijs</xsl:if>
+        <xsl:if test="$month='07'">Jūlijs</xsl:if>
+        <xsl:if test="$month='08'">Augusts</xsl:if>
+        <xsl:if test="$month='09'">Septembris</xsl:if>
+        <xsl:if test="$month='10'">Oktobris</xsl:if>
+        <xsl:if test="$month='11'">Novembris</xsl:if>
+        <xsl:if test="$month='12'">Decembris</xsl:if>
+       </xsl:when>
+       <xsl:otherwise>
+        <xsl:if test="$month='01'">January</xsl:if>
+        <xsl:if test="$month='02'">February</xsl:if>
+        <xsl:if test="$month='03'">March</xsl:if>
+        <xsl:if test="$month='04'">April</xsl:if>
+        <xsl:if test="$month='05'">May</xsl:if>
+        <xsl:if test="$month='06'">June</xsl:if>
+        <xsl:if test="$month='07'">July</xsl:if>
+        <xsl:if test="$month='08'">August</xsl:if>
+        <xsl:if test="$month='09'">September</xsl:if>
+        <xsl:if test="$month='10'">October</xsl:if>
+        <xsl:if test="$month='11'">November</xsl:if>
+        <xsl:if test="$month='12'">December</xsl:if>
+       </xsl:otherwise>
+      </xsl:choose>
+     </xsl:variable>
+     <xsl:choose>
+      <xsl:when test="/documents/document/keel=''"><xsl:value-of select="concat($year,'. gada',' ',$day,'. ',$menesis)" /></xsl:when>
+      <xsl:otherwise><xsl:value-of select="concat($menesis,' ',$day,', ',$year)" /></xsl:otherwise>
+     </xsl:choose>
+	 <xsl:choose>
+		<xsl:when test="/documents/document/tellimusenr!=''">
+			 <br />
+			 <xsl:choose>
+				<xsl:when test="/documents/document/keel=''">Saskaņā ar priekšapmaksas rēķinu </xsl:when>
+				<xsl:otherwise>According to proforma invoice </xsl:otherwise>
+			 </xsl:choose>
+			 <xsl:value-of select="/documents/document/tellimusenr" />
+		</xsl:when>
+	</xsl:choose>
+    </div>
+    <div class="spacer"></div>
+    <div class="left2" valign="top">
+     <xsl:if test="/documents/kontakt/kontakt!=''"><xsl:value-of select="/documents/kontakt/kontakt" /><br /></xsl:if>
+     <xsl:value-of select="/documents/document/klient_nimi" /><br />
+     <xsl:if test="/documents/document/aadress1!=''"><xsl:value-of select="/documents/document/aadress1" /><br /></xsl:if>
+     <xsl:if test="/documents/document/aadress2!=''"><xsl:value-of select="/documents/document/aadress2" /></xsl:if>
+     <xsl:if test="/documents/document/aadress3!=''">&#160;<xsl:value-of select="/documents/document/aadress3" /><br /></xsl:if>
+     <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Reģ. Nr.: </xsl:when>
+	 <xsl:otherwise>Reg. No.: </xsl:otherwise>
+	 </xsl:choose>
+	 <xsl:value-of select="/documents/kontakt/regnr" /><br />
+     <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">PVN Reģ. Nr.: </xsl:when>
+	 <xsl:otherwise>VAT Reg. No.: </xsl:otherwise>
+	 </xsl:choose>
+	 <xsl:value-of select="/documents/kontakt/kmregnr" /><br />
+     <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Banka: </xsl:when>
+	 <xsl:otherwise>Bank: </xsl:otherwise>
+	 </xsl:choose>
+	 <xsl:value-of select="/documents/kontakt/pank" /><br />
+     <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Konta Nr.: </xsl:when>
+	 <xsl:otherwise>Account No.: </xsl:otherwise>
+	 </xsl:choose>
+	 <xsl:value-of select="/documents/kontakt/arveldusarve" /><br />
+	<xsl:choose>
+		<xsl:when test="/documents/kontakt/lahaadress2!=''">
+			<xsl:choose>
+			<xsl:when test="/documents/document/keel=''">Piegādes adrese: </xsl:when>
+			<xsl:otherwise>Delivery address: </xsl:otherwise>
+			</xsl:choose>
+			<xsl:value-of select="/documents/kontakt/lahaadress2" /><br />
+		</xsl:when>
+	</xsl:choose>
+    </div>
+    <div class="right">
+     <xsl:value-of select="/documents/footer/firma_nimi" /><br />
+     <xsl:if test="/documents/footer/firma_aadress!=''"><xsl:value-of select="/documents/footer/firma_aadress" /><br /></xsl:if>
+     <xsl:if test="/documents/footer/firma_aadress2!=''"><xsl:value-of select="/documents/footer/firma_aadress2" /></xsl:if>
+     <xsl:if test="/documents/footer/firma_aadress3!=''">&#160;<xsl:value-of select="/documents/footer/firma_aadress3" /><br /></xsl:if>
+     <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Reģ. #: </xsl:when>
+	 <xsl:otherwise>Reg. #: </xsl:otherwise>
+	 </xsl:choose><!--Reģ. Nr--><xsl:value-of select="/documents/footer/firma_regnr" /><br />
+    <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">PVN Reģ. #: </xsl:when>
+	 <xsl:otherwise>VAT Reg. #: </xsl:otherwise>
+	 </xsl:choose><!--PVN Reģ. Nr--><xsl:value-of select="/documents/footer/firma_kmnr" /><br />
+    </div>
+    <div class="spacer" style="height:60px;"></div>
+    <table class="main_table" height="380" cellspacing="0" cellpadding="0">
+     <tr class="cont">
+      <th class="kods" width="100">
+	  <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Kods</xsl:when>
+	 <xsl:otherwise>Item</xsl:otherwise>
+	 </xsl:choose><!--Kods--></th>
+      <th class="nos" width="300"><!--Nosaukums-->
+	  <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Nosaukums</xsl:when>
+	 <xsl:otherwise>Description</xsl:otherwise>
+	 </xsl:choose>
+	  </th>
+      <th> <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Mērv.</xsl:when>
+	 <xsl:otherwise>Unit</xsl:otherwise>
+	 </xsl:choose><!--Mērvienība--></th>
+      <th> <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Daudz.</xsl:when>
+	 <xsl:otherwise>Qty total</xsl:otherwise>
+	 </xsl:choose><!--Daudz.--></th>
+      <xsl:if test="$atlaide!=0">
+       <th><xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Atlaide</xsl:when>
+	 <xsl:otherwise>Discount</xsl:otherwise>
+	 </xsl:choose><!--Atlaide--></th>
+      </xsl:if>
+      <th><xsl:choose>
-    UPDATE @vat_declar_sums SET doc_sum = doc_sum +
+	 <xsl:when test="/documents/document/keel=''">Cena <xsl:value-of select="$nauda"/></xsl:when>
-      ISNULL((
+	 <xsl:otherwise>Price <xsl:value-of select="$nauda"/></xsl:otherwise>
-        SELECT SUM(ISNULL(baas1kreedit, 0)) - SUM(ISNULL(baas1deebet, 0))
+	 </xsl:choose></th>
-        FROM fin_kanded_read WITH (NOLOCK)
+      <th><xsl:choose>
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+	 <xsl:when test="/documents/document/keel=''">Summa <xsl:value-of select="$nauda"/></xsl:when>
-          AND konto BETWEEN @x AND @y
+	 <xsl:otherwise>Total <xsl:value-of select="$nauda"/></xsl:otherwise>
-          AND kmkood = vat_code
+	 </xsl:choose></th>
-      ),0)
+     </tr>
-    WHERE ISNUMERIC(vat_code) = 1 AND c_class = 4 and row_type=1
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
+     <xsl:for-each select="/documents/document/rows/row">
+     <tr class="cont">
+     <xsl:choose>
+      <xsl:when test="artikkel!=''">
+      <td class="kods" width="100"><xsl:value-of select="artikkel" /></td>
+      <td class="nos" width="250"><xsl:value-of disable-output-escaping="yes" select="keelne_seletus" /></td>
+      <td align="center"><xsl:value-of select="yhik" /></td>
+      <td align="center"><xsl:value-of select="kogus" /></td>
+      <xsl:if test="$atlaide!=0">
+       <td class="atl">
+        <xsl:choose>
+         <xsl:when test="pross!=''"><xsl:value-of select="pross" /> %</xsl:when>
+         <xsl:otherwise>&#160;</xsl:otherwise>
+        </xsl:choose>
+       </td>
+      </xsl:if>
+      <td align="right"><xsl:value-of select="format-number((converted_hind) div 100,'0.00')"/></td>
+      <td align="right"><xsl:value-of select="format-number((converted_summa) div 100,'0.00')" /></td>
+      </xsl:when>
+     <xsl:otherwise>
+	<td>&#160;</td>
+	<td class="nos" width="250"><xsl:value-of disable-output-escaping="yes" select="keelne_seletus" /></td>
+	<td>&#160;</td>
+         <td>&#160;</td>
+	<td>&#160;</td>
+	<td>&#160;</td>
+         <xsl:if test="$atlaide!=0">
+	<td>&#160;</td>
+	</xsl:if>
+     </xsl:otherwise>
+     </xsl:choose>
+     </tr>
+     <xsl:if test="((position() mod $item_per_page)=0) and (position()!=last())">
+      <tr><td colspan="6" style="border-width:0px;">&#160;</td></tr>
+      <xsl:text disable-output-escaping="yes">&lt;/table&gt;</xsl:text>
+      <div style="text-align:right;"><!--Turpinājums-->...</div>
+      <div style="text-align:center; margin-left:10px; margin-top:215px; width:35px; height:29px; #height:37px; border:1px solid #000000; padding:8px 0px 0px 0px;">
+       <xsl:value-of select="floor((position()+1) div $item_per_page)" />&#160;
+      </div>
+      <xsl:text disable-output-escaping="yes">&lt;/div&gt;</xsl:text>
+      <div class="pbreak"></div>
+      <xsl:text disable-output-escaping="yes">&lt;div class="container"&gt;</xsl:text>
+      <div style="text-align:right; padding-bottom:5px;">
+       <xsl:value-of select="floor((position()+1) div $item_per_page)+1" />
+       <xsl:text>/</xsl:text>
+       <xsl:value-of select="ceiling(/documents/document/ridu div $item_per_page)" />
+      </div>
+      <xsl:text disable-output-escaping="yes">&lt;table class="main_table" height="740" cellspacing="0" cellpadding="0"&gt;</xsl:text>
+     <tr class="cont">
+      <th class="kods" width="100">
+	  <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Kods</xsl:when>
+	 <xsl:otherwise>Item</xsl:otherwise>
+	 </xsl:choose><!--Kods--></th>
+      <th class="nos" width="300"><!--Nosaukums-->
+	  <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Nosaukums</xsl:when>
+	 <xsl:otherwise>Description</xsl:otherwise>
+	 </xsl:choose>
+	  </th>
+      <th> <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Mērv.</xsl:when>
+	 <xsl:otherwise>Unit</xsl:otherwise>
+	 </xsl:choose><!--Mērvienība--></th>
+      <th> <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Daudz.</xsl:when>
+	 <xsl:otherwise>Qty total</xsl:otherwise>
+	 </xsl:choose><!--Daudz.--></th>
+      <xsl:if test="$atlaide!=0">
+       <th><xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Atlaide</xsl:when>
+	 <xsl:otherwise>Discount</xsl:otherwise>
+	 </xsl:choose><!--Atlaide--></th>
+      </xsl:if>
-	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
+      <th><xsl:choose>
-      ISNULL((
+	 <xsl:when test="/documents/document/keel=''">Cena <xsl:value-of select="$nauda"/></xsl:when>
-        SELECT SUM(ISNULL(baas1kreedit, 0)) - SUM(ISNULL(baas1deebet, 0))
+	 <xsl:otherwise>Price <xsl:value-of select="$nauda"/></xsl:otherwise>
-        FROM fin_kanded_read WITH (NOLOCK)
+	 </xsl:choose></th>
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+      <th><xsl:choose>
-          AND konto BETWEEN @x AND @y
+	 <xsl:when test="/documents/document/keel=''">Summa <xsl:value-of select="$nauda"/></xsl:when>
-          AND kmkood = vat_code
+	 <xsl:otherwise>Total <xsl:value-of select="$nauda"/></xsl:otherwise>
-      ),0)
+	 </xsl:choose></th>
-    WHERE ISNUMERIC(vat_code) = 0 AND c_class = 4 and row_type=1
+     </tr>
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
+     </xsl:if>
-	  	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
+     <xsl:if test="position()=last()">
-      ISNULL((
+      <tr><td colspan="6" style="border-width:0px;">&#160;</td></tr>
-        SELECT SUM(ISNULL(baas1kreedit, 0)) + sum(isnull(baas1deebet * (-1),0))
+     </xsl:if>
-        FROM fin_kanded_read WITH (NOLOCK)
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+     </xsl:for-each>
-          AND konto BETWEEN @x AND @y
-          and (baas1kreedit > 0 or baas1deebet < 0)
+    </table>
-      ),0)
-    WHERE ISNUMERIC(vat_code) = 0 AND c_class = 4 and row_type=8
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
-	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
-      ISNULL((
-        SELECT SUM(ISNULL(baas1kreedit, 0)) + sum(isnull(baas1deebet * (-1),0))
-        FROM fin_kanded_read WITH (NOLOCK)
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-          AND konto BETWEEN @x AND @y
-          and (baas1kreedit > 0 or baas1deebet < 0)
-		  AND kmkood = vat_code
-      ),0)
-    WHERE ISNUMERIC(vat_code) = 1 AND c_class = 4 and row_type=8
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
-	    	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
-      ISNULL((
-        SELECT SUM(ISNULL(baas1kreedit, 0)) + sum(isnull(baas1deebet * (-1),0))
-        FROM fin_kanded_read WITH (NOLOCK)
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-          AND konto BETWEEN @x AND @y
-          and (baas1kreedit < 0 or baas1deebet > 0)
-      ),0)
-    WHERE ISNUMERIC(vat_code) = 0 AND c_class = 3 and row_type=7
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
-	  UPDATE @vat_declar_sums SET doc_sum = doc_sum +
-      ISNULL((
-        SELECT SUM(ISNULL(baas1kreedit, 0)) + sum(isnull(baas1deebet * (-1),0))
-        FROM fin_kanded_read WITH (NOLOCK)
-        WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-          AND konto BETWEEN @x AND @y
-          and (baas1kreedit < 0 or baas1deebet > 0)
-		  AND kmkood = vat_code
-      ),0)
-    WHERE ISNUMERIC(vat_code) = 1 AND c_class = 3 and row_type=7
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i)
-    UPDATE @vat_declar_sums SET doc_sum = doc_sum +
-      ISNULL((
-        SELECT SUM(kaive/ISNULL(kurss_e,1))
-        FROM int_konto_ajalugu WITH (NOLOCK)
-        WHERE CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-          AND konto BETWEEN @x AND @y
-      ),0)
-    WHERE ISNUMERIC(vat_code) <> 1
-      AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) and row_type=1
-    SET @i = @i + 1
-  END
------------------
------- 1-1 ------
------------------
-set nocount on
-DECLARE @pvn13_vat_codes_1_1 TABLE
-(
-  code nvarchar(30),
-  rate decimal,
-  proportion decimal,
-  description nvarchar(200),
-  sales_account nvarchar(30),
-  purchase_account nvarchar(30),
-  info nvarchar(200),
-  dar_veids nvarchar(5),
-  do_limit varchar(2)
-)
-INSERT INTO @pvn13_vat_codes_1_1
-  SELECT kood, ilmakm, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_3' AND klass='kmk'), '41') AS nvarchar(10)) AS dar_veids,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS varchar(2)) AS do_limit
-  FROM fin_kmkoodid fk
-  WHERE lisainfo LIKE '%pvn13%'
-DECLARE @pvn11_vat_codes TABLE
-(
-  code nvarchar(30),
-  rate decimal,
-  reverse_rate decimal,
-  proportion decimal,
-  description nvarchar(200),
-  sales_account nvarchar(30),
-  purchase_account nvarchar(30),
-  info nvarchar(200),
-  dar_veids nvarchar(5),
-  do_limit varchar(2)
-)
-INSERT INTO @pvn11_vat_codes
-  SELECT kood, ilmakm, poord, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_1' AND klass='kmk'), 'I') AS nvarchar(10)) AS dar_veids,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS varchar(2)) AS do_limit
-  FROM fin_kmkoodid fk
-  WHERE lisainfo LIKE '%pvn11%'
-Declare @pvn1_1_temp TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    directo_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_date nvarchar(30),
-    vatc_prepayment decimal(15,2)
-)
-Declare @pvn1_1_r_rows TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    directo_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_date nvarchar(30),
-    vatc_prepayment decimal(15,2)
-)
-Declare @pvn1_1_values_by_doc TABLE
-(
-    rn int,
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    directo_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,2),
-    vatc_prepayment decimal(15,2)
-)
-Declare @pvn1_1_top_sums_supplier TABLE
-(
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2)
-)
-Declare @pvn1_1_sums_supplier TABLE
-(
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2)
-)
-Declare @pvn1_1_top_table TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    directo_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_date nvarchar(30),
-    rn int
-)
-Declare @pvn1_1_top_r_table TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    directo_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_date nvarchar(30)
-    ,rn int
-)
- declare @pvn_1_1_v_totals TABLE
-  (
-    dok_veids nvarchar(32),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(32),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2)
-  )
-declare @pvn_1_1_v_totals2 TABLE
-  (
-    dok_veids nvarchar(32),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(32),
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2)
-  )
- declare @pvn_1_1_t_totals TABLE
-  (
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2),
-    doc_nr nvarchar(255)
-  )
-   declare @pvn_1_1_totals TABLE
-  (
-    doc_sum decimal(15,2),
-    doc_vat decimal(15,2)
-  )
-insert @pvn1_1_temp --sales invoices
- SELECT
-      (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids,
-      (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS do_limit,
-      case when (isnull(ettemaks,0) = 0) then '1' else '1' end AS dok_veids,
-      mr_arved_read.number AS doc_nr,
-      mr_arved_read.number AS doc_nr,
-      kmk as vat_code,
-      CAST((klient_nimi) AS nvarchar(30)) AS client_name,
-      cast(case when (kmregnumber is not null or kmregnumber!=' ') then kmregnumber else (select top 1 kmregnr from kliendid where kood=mr_arved.klient_kood)end as nvarchar(30)) AS vat_reg_nr,
-      CAST((ROUND(ISNULL(summa, 0), 4)) AS decimal(15,2)) AS doc_sum,
-      isnull(cast((ROUND(ISNULL(summa, 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS decimal(15,2)),'0.00') AS doc_vat,
-      REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-')  AS doc_date,
-      (select sum(summa) from mr_arved_ettemaksud mae where mae.arve=mr_arved_read.number and mae.kmk=mr_arved_read.kmk)
-    FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number
-    WHERE
-      CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-      AND (kinnitatud = 1)
-      and kmk in (SELECT code FROM @pvn11_vat_codes)
-      and artikkel is not null
-      AND ((ISNULL(NULLIF(kredarve, ''), '') = '') OR (CAST((SELECT TOP 1 ma_inner.aeg FROM mr_arved ma_inner WHERE ma_inner.number = mr_arved.kredarve) AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)))
-UNION ALL
-SELECT
-      (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes_1_1 vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids,
-      (SELECT TOP 1 do_limit FROM @pvn13_vat_codes_1_1 vc WHERE vc.code = mr_arved_read.kmk) AS do_limit,
-      case when (isnull(ettemaks,0) = 0) then '4' else 'Z' end AS dok_veids,
-      mr_arved_read.number AS doc_nr,
-      mr_arved_read.number AS doc_nr,
-      kmk as vat_code,
-      CAST((klient_nimi) AS nvarchar(30)) AS client_name,
-      cast(case when (kmregnumber is not null or kmregnumber!=' ') then kmregnumber else (select top 1 kmregnr from kliendid where kood=mr_arved.klient_kood)end as nvarchar(30)) AS vat_reg_nr,
-      CAST((ROUND(ISNULL(summa * (-1), 0), 4)) AS decimal(15,2)) AS doc_sum,
-      isnull(cast((ROUND(ISNULL(summa * (-1), 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes_1_1 WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS decimal(15,2)),'0.00') AS doc_vat,
-      REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-')  AS doc_date,
-      (select sum(summa) * (-1) from mr_arved_ettemaksud mae where mae.arve=mr_arved_read.number and mae.kmk=mr_arved_read.kmk)
-    FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number
-    WHERE
-      CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-      AND (kinnitatud = 1)
-      and kmk in (SELECT code FROM @pvn13_vat_codes_1_1)
-      and artikkel is not null
-      and (kredarve is not null or mr_arved.kokku <=0 )
-      --AND ((ISNULL(NULLIF(kredarve, ''), '') = '') OR (CAST((SELECT TOP 1 ma_inner.aeg FROM mr_arved ma_inner WHERE ma_inner.number = mr_arved.kredarve) AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)))
-insert @pvn1_1_temp --purchase invoices
-SELECT
-    isnull((SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood),'A') AS dar_veids,
-    (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood) AS do_limit,
-    '1',
-    CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr,
-	-- CAST(oa.number AS nvarchar(30)) AS client_doc_nr,
-    CAST(oa.number AS nvarchar(30)) AS client_doc_nr,
-    CAST(oar.kmkood AS nvarchar(30)) AS vat_code,
-    CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name,
-    CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood = oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr,
-    --CAST(isnull(oar.a_summa,oar.summa)  * ISNULL(oa.kurssbv1, 1) AS decimal(15,2)) AS doc_sum,
-    (CASE
-		WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 and oar.konto!=@account)
-			THEN
-				CAST(oar.summa * ISNULL(oa.kurssbv1, 1) AS decimal(15,4))
-		WHEN
-			((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 and (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 and  oar.konto!=@account)
-			THEN
-			CAST(oar.summa * ISNULL(oa.kurssbv1, 1)AS decimal(15,4))
-        when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 and oar.konto!=@account)
-			then
-				CAST((oar.a_summa * ISNULL(oa.kurssbv1, 1)) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0) / 100)  AS decimal(15,4))
-		when (oar.konto=@account) then 0
-    END) AS doc_sum,
-    (CASE
-		WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 and oar.konto!=@account)
-			THEN
-				CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS decimal(15,4))
-		WHEN
-			((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 and (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 and oar.konto!=@account)
-			THEN
-			--CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS decimal(15,4))
-			cast(km as decimal(15,4))
-        when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 and oar.konto!=@account)
-			then
-				CAST(oar.km * ISNULL(oa.kurssbv1, 1) AS decimal(15,4))
-				/* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100)) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0) / 100) */
-		when (oar.konto=@account) then CAST(oar.summa * ISNULL(oa.kurssbv1, 1) AS decimal(15,4))
-    END) AS doc_vat,
-    REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-') AS doc_date,
-    (select sum(summa) from or_arved_ettemaksud mae where mae.arve=oar.number and mae.kmk=oar.kmkood)
-  FROM or_arved oa, or_arved_read oar WITH (NOLOCK)
-  WHERE CAST(oa.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (oa.kinnitatud = 'True')
-    AND (oa.number = oar.number)
-   -- AND NOT (oar.konto = CAST(@account AS nvarchar))
-    and (oar.kmkood in (SELECT code FROM @pvn11_vat_codes) or oa.hankija_kood='859')
-	--select * from @pvn1_1_temp
-insert @pvn1_1_temp --payments
-  SELECT
-    (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = otr.kmk) AS dar_veids,
-    (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = otr.kmk) AS do_limit,
-   '3',
-    CAST(ot.number AS nvarchar(30)) AS client_doc_nr,
-    CAST(ot.number AS nvarchar(30)) AS client_doc_nr,
-    CAST(otr.kmk AS nvarchar(30)) AS vat_code,
-    CAST((SELECT CAST(nimi AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS client_name,
-    CAST((SELECT CAST(kmregnr AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS vat_reg_nr,
-    --CAST(otr.summa_p AS decimal(15,2)) AS doc_sum,
-    --CAST((otr.summa_p * (@stat_vat / 100)) AS decimal(15,2)) AS doc_vat,
-    (CASE
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) != 0)
-        THEN CAST(otr.summa_p AS decimal(15,2))
-        ELSE CAST((otr.summa_p / (1 + @stat_vat / 100)) AS decimal(15,2))
-    END) AS doc_sum,
-    (CASE
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) != 0)
-        THEN CAST(otr.summa_p * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) / 100) AS decimal(15,2))
-        ELSE CAST((otr.summa_p / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS decimal(15,2))
-    END) AS doc_vat,
-    REPLACE(CONVERT(VARCHAR, ot.aeg, 111), '/', '-')  AS doc_date,
-  FROM or_tasumised ot, or_tasumised_read otr WITH (NOLOCK)
-  WHERE CAST(ot.aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (ot.kinnitatud='True')
-    AND (ot.number=otr.number)
-    AND (EXISTS(SELECT 1 FROM @pvn11_vat_codes WHERE code = otr.kmk))
-UNION
-  select
-    (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = oae.kmk) AS dar_veids,
-    (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = oae.kmk) AS do_limit,
-    '9',
-    cast((select hankija_arve from or_arved where number = oae.arve )as nvarchar(30)),
-    cast((select hankija_arve from or_arved where number = oae.arve )as nvarchar(30)),
-    oae.kmk,
-    cast((select nimi from hankijad h where h.kood = (select hankija_kood from or_arved oa where oa.number=oae.arve)) as nvarchar(30)),
-    cast((select kmregnr from hankijad h where h.kood = (select hankija_kood from or_arved oa where oa.number=oae.arve)) as nvarchar(30)),
-    (CASE
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) != 0)
-        THEN CAST(oae.summa AS decimal(15,2)) - isnull(cast((select sum(summa) from or_arved_read oar where oar.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0)
-        ELSE CAST((oae.summa / (1 + @stat_vat / 100)) AS decimal(15,2)) - isnull(cast((select sum(summa) from or_arved_read oar where oar.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0)
-    END) AS doc_sum,
-    (CASE
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) != 0)
-        THEN CAST(oae.summa * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) / 100) AS decimal(15,2))
-        -
-        isnull(cast((select sum(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk),0)/100) from or_arved_read oar where oar.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0)
-        ELSE isnull(CAST((oae.summa / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS decimal(15,2)),0)
-        -
-        isnull(cast((select (sum(summa) * (1 +  @stat_vat / 100))  from or_arved_read oar where oar.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0)
-    END) AS doc_vat,
-    REPLACE(CONVERT(VARCHAR, (select kande_aeg from  or_arved where number=oae.arve), 111), '/', '-')  AS doc_date,
-  from or_arved_ettemaksud oae
-  where (select kande_aeg from or_arved oa where oa.number=oae.arve) between @aeg1 and @aeg2 and kmk in (select code from @pvn11_vat_codes)
-INSERT INTO @pvn1_1_temp --expenses
-/*SELECT
-    (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids,
-    (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit,
-    '9',
-    CAST(fk.number AS nvarchar(30)) AS doc_nr,
-    CAST(fkr.dokument AS nvarchar(30)) AS client_doc_nr,
-    CAST(fkr.kmkood AS nvarchar(30)) AS vat_code,
-    CAST(fkr.hankija_nimi AS nvarchar(30)) AS client_name,
-    CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=fkr.hankija_kood) AS nvarchar(30)) AS vat_reg_nr,
-    CAST(ISNULL(fkr.summa, 0) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) AS doc_sum,
-    (CASE WHEN (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code=fkr.kmkood),0) != 0)
-      THEN
-        cast(CAST(ISNULL(fkr.summa, 0) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood),0) / 100) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) * (-1) as decimal(15,2))
-      ELSE
-        cast(CAST(ISNULL(fkr.reakm, 0) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) * (-1) as decimal(15,2))
-    END) AS doc_vat,
-    REPLACE(CONVERT(VARCHAR, fkr.aeg, 111), '/', '-')  AS doc_date,
-  FROM fin_kulutused fk, fin_kulutused_read fkr WITH (NOLOCK)
-  WHERE CAST(fk.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (fk.kinnitatud='True')
-    AND (fk.number=fkr.number)
-    AND (EXISTS(SELECT 1 FROM @pvn11_vat_codes WHERE code = fkr.kmkood))*/
-	SELECT
-        (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids,
-        (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit,
-		'8',
-        cast(fkr.dokument as nvarchar(30)) AS client_doc_nr,
-		CAST(fkr.number AS nvarchar(30)) AS doc_nr,
-        cast(fkr.kmkood as nvarchar(30)) AS vat_code,
-        cast(fkr.hankija_nimi as nvarchar(30)) AS client_name,
-        cast((select kmregnr from hankijad where kood=fkr.hankija_kood) as nvarchar(30)),
-	   (CASE
-		WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0)
-			THEN
-				CAST(fkr.summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS decimal(15,4))
-		WHEN
-			((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0 and (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0)
-			THEN
-			CAST(fkr.summa *  isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS decimal(15,4))
-        when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0)
-			then
-				CAST((fkr.a_summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1))) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0) / 100)  AS decimal(15,4))
-    END) as doc_sum_wo_vat,
-	 (CASE
-		WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0)
-			THEN
-				CAST(
-					((fkr.summa) * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1))) *
-					((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) AS decimal(15,4))
-				--  isnull(cast((select sum(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr2.kmk),0)/100) from fin_kulutused_read fkr2 where fkr.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0)
-		WHEN
-			((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0 and (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0)
-			THEN
-			--CAST((fkr.summa * isnull(fk.kurssbv1,1)) * ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) AS decimal(15,4))
-			cast(reakm as decimal(15,4))
-        when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0)
-			then
-				CAST((fkr.reakm *isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)))
-				/* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0) / 100) */ AS decimal(15,4))
-    END),
-        cast(isnull(fkr.aeg,fk.aeg) as date),
-       '0'
-from fin_kulutused_read fkr with(nolock)
-left join fin_kulutused fk on fk.number=fkr.number
-where
-      cast(isnull(fkr.aeg,fk.aeg) as date) between cast(@aeg1 as date) and cast(@aeg2 as date)
-      and fkr.kmkood in (select code from @pvn11_vat_codes)
-      AND (kinnitatud='True')
-insert @pvn1_1_values_by_doc
-select
-        row_number() over(order by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date,directo_nr,vatc_prepayment) nr
-        ,   dar_veids
-        ,   do_limit
-        ,   case when (dok_veids = '9' or dok_veids = '8') then '3' else dok_veids end
-        ,   doc_nr
-        ,   directo_nr
-        ,   vat_code
-        ,   client_name
-        ,   isnull(vat_reg_nr,' ')
-                ,   case
-                      when (dok_veids!='9')
-                        then sum(doc_sum)
-                      else
-                      (case
-                          when
-                            (sum(doc_sum) < 0 )
-                            then 0
-                          else
-                            sum(doc_sum) * (-1) end)
-                    end  -
-                          case when (dok_veids!='3')
-                            then (
-                                  CASE
-                                    WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)!=0)
-                                      THEN
-                                        CAST(ISNULL(vatc_prepayment,0) AS DECIMAL(15,4))
-                                    WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)=0)
-                                      THEN
-                                        isnull(cast(vatc_prepayment / cast(1 + (select TOP 1 (ilmakm  / 100) from fin_kmkoodid where kood=a.vat_code) as decimal(15,4)) as decimal(15,4)),0)
-                              END
-                              )
-                          else
-                          end
-        ,   case
-                      when (dok_veids!='9' or dok_veids='8')
-                        then sum(doc_vat)
-                      else
-                      (case when (sum(doc_vat) < 0 ) then 0 else sum(doc_vat)*(-1) end)
-                    end -
-                            case
-                                when (dok_veids!='3')
-                                  then (
-                                          CASE
-                                    WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)!=0)
-                                      THEN
-                                        isnull((cast((vatc_prepayment * cast((0 + (select POORD / 100 from fin_kmkoodid where kood=a.vat_code)) as decimal(15,4))) as decimal(15,4)) ),0)
-                                    WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)=0)
-                                      THEN
-                                        isnull((cast(vatc_prepayment - (vatc_prepayment / cast(1+ (select ilmakm / 100 from fin_kmkoodid where kood=a.vat_code) as decimal(15,4))) as decimal(15,4)) ),0)
-                                   END
-                                        )
-                                    else
-                                    end
-		,   doc_date
-        ,
-			case
-				when (a.dok_veids='1')
-					then
-						(select sum(doc_sum) from @pvn1_1_temp b where a.directo_nr=b.directo_nr and b.dok_veids=a.dok_veids)
-					else
-						(select sum(doc_sum) from @pvn1_1_temp b where a.directo_nr=b.directo_nr and b.dok_veids=a.dok_veids and a.doc_nr=b.doc_nr)
-				end
-			-
-				case
-					when (a.dok_veids='1')
-						then
-							(isnull((select sum(summa) from or_arved_ettemaksud where arve=a.directo_nr and kmk in (select distinct kmkood from or_arved_read where number=a.directo_nr)),0))
-						else
-				end
-        , vatc_prepayment
-from @pvn1_1_temp a group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date,directo_nr, vatc_prepayment
---update @pvn1_1_values_by_doc set dar_veids = 'N' where vat_reg_nr = ' '
---update @pvn1_1_values_by_doc set dok_veids = '4' where doc_sum < 0 and dok_veids = '1'
-insert @pvn_1_1_totals
-select sum(doc_sum), sum(doc_vat) from @pvn1_1_values_by_doc
-insert @pvn1_1_sums_supplier
-select
-            isnull(vat_reg_nr,' ')
-        ,   sum(doc_sum)
-        ,   sum(doc_vat)
-from @pvn1_1_values_by_doc
-where isnull(vat_reg_nr,' ')!=''
-GROUP BY vat_reg_nr
-insert @pvn1_1_top_table
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   directo_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        ,   rn
-from @pvn1_1_values_by_doc
-where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1)))
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat,directo_nr, rn
-delete from @pvn1_1_values_by_doc where rn in (select rn from @pvn1_1_top_table)
-insert @pvn1_1_top_r_table
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   directo_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        , rn
-from @pvn1_1_values_by_doc
-where (dar_veids in ('R1','R2','R3','R4','R5','R6','R7','R8','R9') or dar_veids in (select dar_veids from @pvn11_vat_codes where substring(do_limit,1,1)='N' and substring(dar_veids,1,1)!='R'))
-and directo_nr+'('+dar_veids+')'+'['+dok_veids+']' not in (select directo_nr+'('+dar_veids+')'+'['+dok_veids+']' from @pvn1_1_top_table)
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat,directo_nr,rn
---select * from @pvn11_vat_codes
+    <table class="main_table" cellspacing="0" cellpadding="0">
-delete from @pvn1_1_values_by_doc where rn in (select rn from @pvn1_1_top_r_table)
+    <tr><td style="text-align:left; border-width:0px;"><xsl:value-of select="documents/document/kommentaar"/></td></tr>
+    </table>
-insert @pvn_1_1_v_totals
+    <table class="main_table" height="260" cellspacing="0" cellpadding="0">
-select
+    <tr style="height:220px;">
-            'V'
+    <td style="text-align:left; border-width:0px;">
-        ,   CAST((SELECT TOP 1 SUBSTRING(NIMI,1,30) FROM hankijad Y WHERE Y.KMREGNR=Z.vat_reg_nr) AS NVARCHAR(30))
-        ,   vat_reg_nr
+    <div style="border:solid #B4B4B4; border-width:2px 0px 2px 0px; display:table; width:660px;">
-        ,   cast(doc_sum - cast(isnull((select sum(doc_sum) from @pvn1_1_top_table x where x.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4)) - cast(isnull((select sum(doc_sum) from @pvn1_1_top_r_table u where u.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4)) as decimal(15,4))
-        ,   cast(cast(doc_vat as decimal(15,2)) -
+     <div style="float:left; width:350px;" valign="top" align="left">
-		cast(isnull((select sum(doc_vat) from @pvn1_1_top_table x where x.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,2)) - cast(isnull((select sum(cast(doc_vat as decimal(15,2))) from @pvn1_1_top_r_table u where u.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,2)) as decimal(15,2))
+	 <table width="100%" border="0" cellapdding="0" cellspacing="0" align="left">
-from @pvn1_1_sums_supplier z
+	  <tr>
-where (doc_sum >= @lim or doc_sum <= (@lim * (-1)))
+	  <td align="left" style="border-width: 0px 0px 0px 0px;">
-and cast(doc_sum  - isnull((select sum(doc_sum) from @pvn1_1_top_table x where x.vat_reg_nr=z.vat_reg_nr),0) - isnull((select sum(doc_sum) from @pvn1_1_top_r_table u where u.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4)) <> 0
+	  <xsl:choose>
-and vat_reg_nr!=''
+      <xsl:when test="/documents/document/keel=''">
+      <xsl:choose>
+       <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
-insert @pvn_1_1_v_totals2
+	   <xsl:choose>
-select 'V', client_name, vat_reg_nr, doc_sum, doc_vat from @pvn_1_1_v_totals where (doc_sum >= @lim or doc_sum <= (@lim * (-1)))
+	  <xsl:when test="$nauda='LVL' or $nauda='EUR'">
-delete from @pvn1_1_values_by_doc where vat_reg_nr in (select vat_reg_nr from @pvn_1_1_v_totals2)
+        <xsl:value-of select="js:numText(string(format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00', 'N')),string($nauda))" /></xsl:when>
-insert @pvn_1_1_t_totals
+	    </xsl:choose>
-select            cast(sum(doc_sum) as decimal(15,4))
+	   </xsl:when>
-        ,   cast(sum(doc_vat) as decimal(15,4))
+       <xsl:otherwise>
-        ,''
+	   <xsl:choose>
-from @pvn1_1_values_by_doc z
+	  <xsl:when test="$nauda='LVL' or $nauda='EUR'">
+        <xsl:value-of select="js:numText(string(format-number(/documents/document/converted_tasuda div 100, '0.00', 'N')),string($nauda))" /></xsl:when>
-/*
+	    </xsl:choose>
-select
+       </xsl:otherwise>
-           cast(sum(doc_sum * (-1)) as decimal(15,4))
+      </xsl:choose>
-        ,   cast(sum(doc_vat * (-1)) as decimal(15,4))
+      </xsl:when>
-from @pvn1_1_values_by_doc z
+	        <xsl:when test="/documents/document/keel!=''">
-where (doc_sum between (@lim * (-1)) and @lim)
+      <xsl:choose>
-and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='')
+       <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
-and dar_veids not in ('R1','R2','R3','R4','R5','R6','R7','R8','R9')
+	   <xsl:choose>
-UNION
+	  <xsl:when test="$nauda='LVL' or $nauda='EUR'">
-select
+        <xsl:value-of select="js:numTextEng(string(format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00')),string($nauda))" />
-           cast(sum(doc_sum) as decimal(15,4))
+		 </xsl:when>
-        ,   cast(sum(doc_vat) as decimal(15,4))
+	    </xsl:choose>
-from @pvn1_1_values_by_doc z
+	   </xsl:when>
-where (doc_sum between (@lim * (-1)) and @lim)
+       <xsl:otherwise>
-and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='')
+	   <xsl:choose>
-UNION
+	  <xsl:when test="$nauda='LVL' or $nauda='EUR'">
-select
+        <xsl:value-of select="js:numTextEng(string(format-number((/documents/document/converted_tasuda div 100), '0.00')),string($nauda))" /></xsl:when>
-           cast(sum(doc_sum) as decimal(15,4))
+	    </xsl:choose>
-        ,   cast(sum(doc_vat) as decimal(15,4))
+       </xsl:otherwise>
-from @pvn1_1_values_by_doc z
+      </xsl:choose>
-where (doc_sum between (@lim * (-1)) and @lim)
+      </xsl:when>
-and (vat_reg_nr in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='') and vat_reg_nr not in (select vat_reg_nr from  @pvn_1_1_v_totals2 where isnull(vat_reg_nr,'')=''))
+      </xsl:choose>
-and dar_veids not in ('R1','R2','R3','R4','R5','R6','R7','R8','R9')
+	  </td>
-UNION
+	  </tr>
-select
+	  <xsl:for-each select="/documents/kmkoodid/kmkood">
-           cast(sum(doc_sum) as decimal(15,4))
+	  <tr>
-        ,   cast(sum(doc_vat) as decimal(15,4))
+	  <td align="left" style="border-width: 0px 0px 0px 0px;">
-from @pvn1_1_values_by_doc z
+	      <xsl:choose>
-where (doc_sum between (@lim * (-1)) and @lim)
+	  <xsl:when test="km='9'">
-and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')='')*/
+	        <xsl:choose>
+	 <xsl:when test="/documents/document/keel!=''">Article 138 (1) of the EU VAT Directive (2006/112/EC)</xsl:when>
------------------
+	 <xsl:otherwise>Direktīvas 2006/112/EK 138. panta 1. punkts</xsl:otherwise>
------- 1-2 ------
+	 </xsl:choose><!--Summa--><br />
------------------
+	  </xsl:when>
+	   <xsl:when test="km!='9'">
-DECLARE @pvn12_vat_codes TABLE
+	  &#160;
-(
+	  </xsl:when>
-  code nvarchar(30),
+	  </xsl:choose>
-  rate decimal,
+	  </td>
-  reverse_rate decimal,
+	  </tr>
-  proportion decimal,
+      </xsl:for-each>
-  description nvarchar(200),
+	  <tr>
-  sales_account nvarchar(30),
+	  <td  align="left" style="border-width: 0px 0px 0px 0px;">
-  purchase_account nvarchar(30),
+ <xsl:choose>
-  info nvarchar(200),
+	 <xsl:when test="/documents/document/keel=''">Izrakstīja:</xsl:when>
-  dar_veids nvarchar(5),
+	 <xsl:otherwise>Salesman:</xsl:otherwise>
-  do_limit varchar(2)
+	 </xsl:choose><!--Izrakstīja-->&#160;<xsl:value-of select="/documents/document/myyja_nimi" />
+	  </td>
-)
+	  </tr>
-INSERT INTO @pvn12_vat_codes
+	  </table>
-  SELECT kood, ilmakm, poord, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo,
+     </div>
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_2' AND klass='kmk'), 'G') AS nvarchar(10)) AS dar_veids,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS varchar(2)) AS do_limit
+     <div style="float:left; width:150px; text-align:right;">
-  FROM fin_kmkoodid fk
+	  <table width="100%" border="0" cellapdding="0" cellspacing="0">
-  WHERE lisainfo LIKE '%pvn12%'
+	  <xsl:if test="$atlaide!=0">
+	  <tr>
-DECLARE @pvn12_currencies TABLE (currency nvarchar(3))
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-INSERT INTO @pvn12_currencies (currency)
+	  <xsl:choose>
-  SELECT DISTINCT kood FROM curr_rates
+	 <xsl:when test="/documents/document/keel=''">Atlaides summa:</xsl:when>
+	 <xsl:otherwise>Discount total:</xsl:otherwise>
---pvn12 main table
+	 </xsl:choose><!--Kopējā summa-->
-DECLARE @pvn12_main_table TABLE
+	  </td>
-(
+	  </tr>
-  dar_veids nvarchar(5),
+	  </xsl:if>
-  do_limit varchar(2),
+	  <tr>
-  doc_nr nvarchar(30),
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-  client_doc_nr nvarchar(30),
+	  <xsl:choose>
-  vat_code nvarchar(30),
+	 <xsl:when test="/documents/document/keel=''">Summa bez PVN:</xsl:when>
-  directo_table nvarchar(30),
+	 <xsl:otherwise>Total:</xsl:otherwise>
-  doc_type nvarchar(30),
+	 </xsl:choose><!--Summa-->
-  client_name nvarchar(30),
+	  </td>
-  vat_reg_nr nvarchar(30),
+	  </tr>
-  country_code nvarchar(2),
+	  <xsl:for-each select="/documents/kmkoodid/kmkood">
-  doc_sum decimal(15,2),
+	  <tr>
-  doc_vat decimal(15,2),
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-  rate decimal(28,18),
+	    <xsl:choose>
-  currency nvarchar(30),
+	 <xsl:when test="/documents/document/keel=''">PVN:&#160;<xsl:value-of select="ilmakm" />%:</xsl:when>
-  alt_rate decimal(28, 18),
+	 <xsl:otherwise>VAT&#160;<xsl:value-of select="ilmakm" />%:</xsl:otherwise>
-  alt_currency nvarchar(30),
+	 </xsl:choose>
-  alt_doc_currency_sum nvarchar(30),
+	  </td>
-  doc_date nvarchar(30)
+	  </tr>
-)
+      </xsl:for-each>
+	  <tr>
---pvn12 temp table
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-DECLARE @pvn12_temp_table TABLE
+ <xsl:choose>
-(
+	 <xsl:when test="/documents/document/keel=''">Kopā ar PVN:</xsl:when>
-  dar_veids nvarchar(5),
+	 <xsl:otherwise>Total with VAT:</xsl:otherwise>
-  do_limit varchar(2),
+	 </xsl:choose>
-  doc_nr nvarchar(30),
+	  </td>
-  client_doc_nr nvarchar(30),
+	  </tr>
-  vat_code nvarchar(30),
+	  <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
-  directo_table nvarchar(30),
+	  <tr>
-  doc_type nvarchar(30),
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-  client_name nvarchar(30),
+<xsl:choose>
-  vat_reg_nr nvarchar(30),
+	 <xsl:when test="/documents/document/keel=''">Priekšapmaksa:<xsl:value-of select="format-number(/documents/document/ettemaks div (/documents/document/converted_tasuda div 10000), '0.00')" /> %:</xsl:when>
-  country_code nvarchar(2),
+	 <xsl:otherwise>Prepayment:<xsl:value-of select="format-number(/documents/document/ettemaks div (/documents/document/converted_tasuda div 10000), '0.00')" /> %:</xsl:otherwise>
-  doc_sum decimal(15,4),
+	 </xsl:choose>
-  doc_vat decimal(15,4),
+	  </td>
-  rate decimal(28,18),
+	  </tr>
-  currency nvarchar(30),
+	  </xsl:if>
-  alt_rate decimal(28, 18),
+	  <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
-  alt_currency nvarchar(30),
+	  <tr>
-  alt_doc_currency_sum nvarchar(30),
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-  doc_date nvarchar(30),
+<xsl:choose>
-  vat_c_prepayment decimal
+	 <xsl:when test="/documents/document/keel=''">Summa apmaksai:</xsl:when>
-)
+	 <xsl:otherwise>Sum To pay:</xsl:otherwise>
+	 </xsl:choose>
-INSERT INTO @pvn12_temp_table --purchase invoices - temp
+	  </td>
-  SELECT
+	  </tr>
-		(SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code =oar.kmkood) AS dar_veids,
+	  </xsl:if>
-		'' AS do_limit,
+	  </table>
-		CAST(oa.number AS nvarchar(30)) AS doc_nr,
+     </div>
-		CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr,
-		CAST(oar.kmkood AS nvarchar(30)) AS vat_code,
+     <div class="right_small">
-		'purchase_invoice' AS directo_table,
+	  <table width="100%" border="0" cellapdding="0" cellspacing="0">
-		CAST(oa.tyyp AS nvarchar(30)) AS doc_type,
+	  <xsl:if test="$atlaide!=0">
-		CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name,
+	  <tr>
-		CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr,
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-		'--' AS country_code,
+	  <xsl:value-of select="format-number(sum(msxsl:node-set($atlaides_summa)/a_summa),'0.00')" />&#160;<xsl:value-of select="$nauda" />
-		CAST(((select sum(summa) from or_arved_read where number=oar.number and kmkood=oar.kmkood) * ISNULL(oa.kurssbv1,1)) AS decimal(15,4)) AS doc_sum,
+	  </td>
-		(CASE
+	  </tr>
-			WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmkood), 0)) != 0)
+	  </xsl:if>
-			THEN CAST((select sum(summa) from or_arved_read where number=oar.number and kmkood=oar.kmkood) * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS decimal(15,4))
+	  <tr>
-			ELSE CAST((select sum(summa) from or_arved_read where number=oar.number and kmkood=oar.kmkood)*(@stat_vat/100)*ISNULL(oa.kurssbv1,1) AS decimal(15,4))
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-		END) AS doc_vat,
+	  <xsl:value-of select="format-number(/documents/document/kokku,'0.00')" />&#160;<xsl:value-of select="$nauda" />
-    CAST(ISNULL(oa.kurssbv1,1) AS decimal(28,18)) AS rate,
+	  </td>
-    CAST(oa.valuuta AS nvarchar(30)) AS currency,
+	  </tr>
-AS alt_rate,
+	  <xsl:for-each select="/documents/kmkoodid/kmkood">
-    'EUR' AS alt_currency,
+	  <tr>
-    CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum,
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-    REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-')  AS doc_date,
+	    <xsl:value-of select="format-number(summakm div 100,'0.00')" />&#160;<xsl:value-of select="$nauda" />
-	(select sum(summa) from or_arved_ettemaksud oae where oae.arve=oa.number and oae.kmk=oar.kmkood)
+	  </td>
-  FROM or_arved_read oar, or_arved oa WITH (NOLOCK)
+	  </tr>
-  WHERE CAST(oa.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+      </xsl:for-each>
-    AND (kinnitatud='True')
+	  <tr>
-    AND (oa.number=oar.number)
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-    AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = oar.kmkood))
+<xsl:value-of select="format-number(/documents/document/converted_tasuda div 100,'0.00')" />&#160;<xsl:value-of select="$nauda" />
-UNION
+	  </td>
-SELECT
+	  </tr>
-    (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = otr.kmk) AS dar_veids,
+	  <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
-    (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = otr.kmk) AS do_limit,
+	  <tr>
-    CAST(isnull((select hankija_arve from or_arved where number=otr.ostuarve),ot.number) AS nvarchar(30)) AS client_doc_nr,
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
-    CAST(ot.number AS nvarchar(30)) AS client_doc_nr,
+<xsl:value-of select="format-number(/documents/document/ettemaks, '0.00')" />&#160;<xsl:value-of select="$nauda" />
+	  </td>
+	  </tr>
+	  </xsl:if>
+	  <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
+	  <tr>
+	  <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;">
+<xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) - /documents/document/ettemaks, '0.00')" />&#160;<xsl:value-of select="$nauda" />
+	  </td>
+	  </tr>
+	  </xsl:if>
+	  </table>
+     </div>
+     <div class="spacer"></div>
+     <div style="float:left; width:350px;" >
+      <xsl:choose><xsl:when test="/documents/document/keel=''">Banka: </xsl:when><xsl:otherwise>Bank: </xsl:otherwise></xsl:choose><xsl:value-of select="/documents/footer/firma_pank" /><br />
+      <xsl:choose><xsl:when test="/documents/document/keel=''">Bankas kods: </xsl:when><xsl:otherwise>Bank SWIFT: </xsl:otherwise></xsl:choose><xsl:value-of select="/documents/footer/firma_swift" /><br />
+      <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Bankas konts:</xsl:when>
+	 <xsl:otherwise>Bank account:</xsl:otherwise>
+	 </xsl:choose><!--Konts-->&#160;<xsl:value-of select="/documents/footer/firma_aa" />
+     </div>
+     <div style="float:left; width:150px;  text-align:right;">
+      <!--<div style="padding-top:3px;">
+	  <div style="padding-top:3px;"></div>
+	  <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Kopā apmaksai:</xsl:when>
+	 <xsl:otherwise>Total:</xsl:otherwise>
+	 </xsl:choose></div>-->
+      <xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Apmaksas termiņš:</xsl:when>
+	 <xsl:otherwise>Due date:</xsl:otherwise>
+	 </xsl:choose><!--Apmaksas termiņš-->
+     </div>
+     <div class="right_small" width="130px">
+	  <table width="100%" cellpadding="1" cellspacing="1">
+	<!--   <xsl:if test="$saldo &gt; $rekina_summa">
+	 <tr>
+	  <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%">
+	  &#160;
+	  </td>
+	   <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;">
+<xsl:value-of select="$neapm_summa" />&#160;
+<xsl:value-of select="$nauda" />
+	  </td>
+	  </tr>
+	  </xsl:if>
+	  <tr>
+	  <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%">
+	  &#160;
+	  </td>
+	  <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;">
+	  <xsl:choose>
+        <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0">
+<xsl:value-of select="format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00')" />
+	  &#160;
+<xsl:value-of select="$nauda" />
+        </xsl:when>
+        <xsl:otherwise>
+	<xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) + $parads, '0.00')" />&#160;
+<xsl:value-of select="$nauda" />
+        </xsl:otherwise>
+       </xsl:choose>
+	  </td>
+	  </tr>-->
+	  <tr>
+	  <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%">
+	  &#160;
+	  </td>
+	  <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;" align="right">
+	<xsl:value-of select="/documents/document/aeg2_date" />
+	  </td>
+	  </tr>
+	  </table>
+     </div>
+    </div>
+	<!--<xsl:if test="$neapm_summa&gt;$rekina_summa">
+    <div style="clear:both; padding:5px 0px 0px 0px; font-size:12px; font-weight:bold;">
+	<xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">
+	 <xsl:choose>
+	 <xsl:when test="/documents/document/valuuta=''">
+	  Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/>&#160;<xsl:value-of select="$nauda"/><br />
+	  Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/>&#160;<xsl:value-of select="$nauda2"/><br />
+	 </xsl:when>
+	 <xsl:when test="/documents/document/valuuta!=''">
+	  Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/>&#160;<xsl:value-of select="$nauda2"/><br />
+	  Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/>&#160;<xsl:value-of select="$nauda"/><br />
+	 </xsl:when>
+	 </xsl:choose>
+	 Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/>
+	 </xsl:when>
+	 <xsl:otherwise>
+	 <xsl:choose>
+	 <xsl:when test="/documents/document/valuuta=''">
+	 Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/>&#160;<xsl:value-of select="$nauda"/><br />
+	  Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/>&#160;<xsl:value-of select="$nauda2"/><br />
+	 </xsl:when>
+	 <xsl:when test="/documents/document/valuuta!=''">
+	  Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo , '0.00')"/>&#160;<xsl:value-of select="$nauda2"/><br />
+	  Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/>&#160;<xsl:value-of select="$nauda"/><br />
+	 </xsl:when>
+	 </xsl:choose>
+	 </xsl:otherwise>
+	 </xsl:choose>
+     <br />
+    </div>
+	</xsl:if>-->
+    <div style="clear:both; padding:5px 0px 0px 0px; font-size:14px;">
+	<xsl:choose>
+	 <xsl:when test="/documents/document/keel=''">Dokuments ir sagatavots elektroniski un derīgs bez paraksta.</xsl:when>
+	 <xsl:otherwise>The invoice is produced electronically and valid without signature.</xsl:otherwise>
+	 </xsl:choose>
+    </div>
+    </td>
+    </tr>
+    <!--<tr>
+		<td style="height:0px; line-height:0px; border-width:0px; font-size:0px;">
+			<img SRC="http://login.directo.ee/logos/files/nordcity_lv_3.png" width="660"/>
+		</td>
+	</tr>-->
+    <tr>
+    <td style="text-align:left; border-width:0px;">
-    CAST(otr.kmk AS nvarchar(30)) AS vat_code,
-    'payments' AS directo_table,
+    </td>
-    '' doc_type,
+    </tr>
-    CAST((SELECT CAST(nimi AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS client_name,
+    </table>
-    CAST((SELECT CAST(kmregnr AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS vat_reg_nr,
-    --CAST(otr.summa_p AS decimal(15,2)) AS doc_sum,
+   </div> <!-- Konteinera beigas -->
-    --CAST((otr.summa_p * (@stat_vat / 100)) AS decimal(15,2)) AS doc_vat,
+   </body>
-    '--' AS country_code,
-    (CASE
+   </html>
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) != 0)
+  </xsl:template>
-        THEN CAST(otr.summa_p AS decimal(15,2))
+<msxsl:script language="JScript" implements-prefix="js"><![CDATA[
-        ELSE CAST((otr.summa_p / (1 + @stat_vat / 100)) AS decimal(15,2))
+var cipariEng=['','one ','two ','three ','four ','five ','six ','seven ','eight ','nine '];
-    END) AS doc_sum,
+ var padsmitiEng=['ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen '];
-    (CASE
+ var desmitiEng=['','','twenty ','thirty ','fourty ','fifty ','sixty ','seventy ','eighty ','ninety '];
-      WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) != 0)
-        THEN CAST(otr.summa_p * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) / 100) AS decimal(15,2))
-        ELSE CAST((otr.summa_p / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS decimal(15,2))
-    END) AS doc_vat,
-    CAST(ISNULL(otr.kurss_a,1) AS decimal(28,18)) AS rate,
-    CAST(otr.valuuta_a AS nvarchar(30)) AS currency,
-AS alt_rate,
-    'EUR' AS alt_currency,
-    '',--CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum,
-    REPLACE(CONVERT(VARCHAR, ot.aeg, 111), '/', '-')  AS doc_date,
-  FROM or_tasumised ot, or_tasumised_read otr WITH (NOLOCK)
-  WHERE CAST(ot.aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (ot.kinnitatud='True')
-    AND (ot.number=otr.number)
-    AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = otr.kmk))
-	and isnull(otr.ostuarve,'')=''
-UNION
-  SELECT
-		(SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code =oar.kmk) AS dar_veids,
-		'' AS do_limit,
-		CAST(oa.number AS nvarchar(30)) AS doc_nr,
-		CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr,
-		CAST(oar.kmk AS nvarchar(30)) AS vat_code,
-		'prepayment' AS directo_table,
-		CAST(oa.tyyp AS nvarchar(30)) AS doc_type,
-		CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name,
-		CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr,
-		'--' AS country_code,
-		CAST((oar.summa)*ISNULL(oa.kurssbv1,1) AS decimal(15,4)) AS doc_sum,
-		(CASE
-			WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmk), 0)) != 0)
-			THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmk), 0)) / 100) AS decimal(15,4))
-			ELSE CAST((oar.summa)*(@stat_vat/100)*ISNULL(oa.kurssbv1,1) AS decimal(15,4))
-		END) AS doc_vat,
-    CAST(ISNULL(oa.kurssbv1,1) AS decimal(28,18)) AS rate,
-    CAST(oa.valuuta AS nvarchar(30)) AS currency,
-AS alt_rate,
-    'EUR' AS alt_currency,
-    CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum,
-    REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-')  AS doc_date,
-  FROM or_arved_ettemaksud oar, or_arved oa WITH (NOLOCK)
-  WHERE CAST(oa.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (oa.kinnitatud='True')
-   AND (oa.number=oar.arve)
-    AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = oar.kmk))
-/*
-Select * from table a
-left join table b on a.blabla=b.blable
-Select * from table a , table b
+ function simtiEng(str) {
-Where tablea.blabla = tableb.blabla
+  res='';
+  // pirmais cipars no labaas
+  if (str.charAt(1)=='1') // chekojam pirmspeedeejo ciparu - ja viens tad vai nu desmit vai padsmit
+   res=padsmitiEng[parseInt(str.charAt(2))];
+  else res=cipariEng[parseInt(str.charAt(2))];
+  // otrais cipars no labaas
+  if (str.charAt(0)=='1') // ja treshais cipars no labaas ir 1 tad jaaraksta simts
+   res='hundred '+desmitiEng[parseInt(str.charAt(1))]+res;
+  else if (str.charAt(0)!='0') // ja treshais cipars no labaas nav 1 vai 0 tad jaaraksta simti
+   res='hundred '+desmitiEng[parseInt(str.charAt(1))]+res;
+  else // ja nav simtu
+   res=desmitiEng[parseInt(str.charAt(1))]+res;
+  // treshais cipars no labaas
+  res=cipariEng[parseInt(str.charAt(0))]+res;
+  return res;
+ }
-*/
+ function numTextEng(skaitlis,nauda) {
-INSERT INTO @pvn12_main_table --purchase invoices - main
+  fullStr='0000000000';
-  SELECT
+  tempStr='';
-				dar_veids
+  tempRes='';
-			,	do_limit
+  resStr='';
-			,	doc_nr
+  minus='';
-			,	client_doc_nr
-			, '' as vat_code
+  punkts=skaitlis.indexOf('.');
-			, directo_table
+  if (punkts==-1) {
-			,	doc_type
+   pirmspStr=skaitlis;
-			,	client_name
+   aizpStr='0';
-			,	vat_reg_nr
+  } else {
-			,	country_code
+   pirmspStr=skaitlis.substring(0,punkts);
-			, case
+   aizpStr=skaitlis.substring(punkts+1);
-				when (directo_table='purchase_invoice')
+  }
-					then  SUM(doc_sum) - isnull((select sum(doc_sum) from @pvn12_temp_table where directo_table='prepayment' and doc_nr=b.doc_nr),0)
-				else
+  if (pirmspStr.charAt(0)=='-') { // ja ar miinus ziimi
-					SUM(doc_sum)
+   pirmspStr=pirmspStr.substring(1);
-				end
+   minus='minus ';
-			, case
+  }
-				when (directo_table='purchase_invoice')
-					then  SUM(doc_vat) - isnull((select sum(doc_vat) from @pvn12_temp_table where directo_table='prepayment' and doc_nr=b.doc_nr),0)
+  for (i=pirmspStr.length-1, j=9; i>=0; i--, j--) { // paarliekam uz fullstr, kas sastaav no desmit cipariem
-				else
+   fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1);
-					SUM(doc_vat)
+  }
-				end, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date
-  FROM @pvn12_temp_table b
+  if (fullStr.length>10) fullStr=fullStr.substring(fullStr.length-10); // ja vairaak par 10 cipariem, tad trim
-  where
-  --doc_sum > 0 or doc_sum < 0 or
+  if (nauda=='EUR') {
-  directo_table not in ('prepayment')
+   if (parseInt(fullStr,10)==1) resStr+='euro'; // lats vai lati
-  GROUP BY dar_veids, do_limit, doc_nr, client_doc_nr, directo_table, doc_type, client_name, vat_reg_nr, country_code, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date
+    else if (parseInt(fullStr,10)!=0) resStr+='euros';
+    else resStr+='Null euros';
+  } else if (parseInt(fullStr,10)!=0) {
+   resStr+=nauda;
+  } else resStr+='Null '+nauda;
+  if (parseInt(fullStr.substring(7),10)>0) {
+   tempStr=fullStr.substring(7); // simti
+   tempRes=simtiEng(tempStr);
+   resStr=tempRes+resStr;
+  }
-INSERT INTO @pvn12_main_table --expenses
-/*
+  if (parseInt(fullStr.substring(4,7),10)>0) {
-  SELECT
+   tempStr=fullStr.substring(4,7); // tūkstoši
-    '' AS dar_veids,
+   tempRes=simtiEng(tempStr);
-    '' AS do_limit,
+   if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='thousand ';
-    CAST(number AS nvarchar(30)) AS doc_nr,
+    else if (parseInt(tempStr,10)!=0) tempRes+='thousand ';
-    CAST((SELECT TOP 1 dokument FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS client_doc_nr,
+   resStr=tempRes+resStr;
-    CAST((SELECT TOP 1 kmkood FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS vat_code,
+  }
-    'expense' AS directo_table,
-    'Fakturrekins' AS doc_type,
+  if (parseInt(fullStr.substring(1,4),10)>0) {
-    CAST((SELECT TOP 1 hankija_nimi FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number) AS nvarchar(30)) AS client_name,
+   tempStr=fullStr.substring(1,4); // miljoni
-    CAST((SELECT kmregnr FROM hankijad WHERE hankijad.kood=(SELECT TOP 1 hankija_kood FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS vat_reg_nr,
+   tempRes=simtiEng(tempStr);
-    '--' AS country_code,
+   if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='million ';
-    CAST(summa*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_sum,
+    else if (parseInt(tempStr,10)!=0) tempRes+='million ';
-    CAST(summa*(@stat_vat/100)*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_vat,
+   resStr=tempRes+resStr;
-    CAST(ISNULL(kurssbv1,1) AS decimal(28,18)) AS rate,
+  }
-    CAST(valuuta AS nvarchar(30)) AS currency,
-AS alt_rate,
+  if (fullStr.charAt(0)!='0') { // miljardi
-    'EUR' AS alt_currency,
+   if (fullStr.charAt(0)=='1') resStr='one billion '+resStr;
-    CAST(lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum,
+    else resStr=cipariEng[parseInt(fullStr.charAt(0),10)]+'billion '+resStr;
-    REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-')  AS doc_date
+  }
-  FROM fin_kulutused WITH (NOLOCK) WHERE CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-    AND (kinnitatud='True')
+  if (resStr!='') { // pirmais burts lielais
-    AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code IN (SELECT kmkood FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number))))
+   resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1);
-*/
+  }
-SELECT
-        (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids,
+  santimi=parseInt(aizpStr,10); //santimi
-        (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit,
+  if (santimi>0) {
-        CAST(fkr.number AS nvarchar(30)) AS doc_nr,
+   resStr+=' '+santimi;
-        cast(fkr.dokument as nvarchar(30)) AS client_doc_nr,
+   if (santimi==1) {
-        cast(fkr.kmkood as nvarchar(30)) AS vat_code,
+    resStr+=' cent';
-        'expense' AS directo_table,
+   } else {
-        'Fakturrekins' AS doc_type,
+    resStr+=' cents';
-        cast(fkr.hankija_nimi as nvarchar(30)) AS client_name,
+   }
-        cast((select kmregnr from hankijad where kood=fkr.hankija_kood) as nvarchar(30)),
+  } else {
-        '--',
+    resStr+=' 00 cents';
-        cast(fkr.summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) as decimal(15,2)),
+  }
-        cast((fkr.summa * (@stat_vat / 100)) *  isnull(r_kurss,1) as decimal(15,2)),
-        cast(isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) as decimal(28,18)),
+  return (minus+resStr);
-        cast(isnull(fkr.r_valuuta,isnull(fk.valuuta,'EUR')) as nvarchar(255)),
-,
-        'EUR',
-        CAST(fk.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum,
-        REPLACE(CONVERT(VARCHAR, isnull(fkr.aeg,fk.aeg), 111), '/', '-')  AS doc_date
-from fin_kulutused_read fkr with(nolock)
-left join fin_kulutused fk on fk.number=fkr.number
-where
-      cast(isnull(fkr.aeg,fk.aeg) as date) between cast(@aeg1 as date) and cast(@aeg2 as date)
-      and fkr.kmkood in (select code from @pvn12_vat_codes)
-      AND (kinnitatud='True')
-UPDATE @pvn12_main_table SET country_code = SUBSTRING(vat_reg_nr, 1, 2)
-UPDATE @pvn12_main_table
-SET alt_currency =
-    (CASE
-      WHEN country_code = 'BG' THEN 'BGN'
-      WHEN country_code = 'CZ' THEN 'CZK'
-      WHEN country_code = 'DK' THEN 'DKK'
-      WHEN country_code = 'GB' THEN 'GBP'
-      WHEN country_code = 'HU' THEN 'HUF'
-      WHEN country_code = 'LT' AND CAST(doc_date AS date) < CAST('2015.01.01' AS date) THEN 'LTL'
-      WHEN country_code = 'PL' THEN 'PLN'
-      WHEN country_code = 'RO' THEN 'RON'
-      WHEN country_code = 'SE' THEN 'SEK'
-      ELSE 'EUR' END)
-UPDATE @pvn12_main_table
-SET alt_rate =
-  ISNULL((SELECT TOP 1 CAST(curr_rates.kurss1 AS decimal(28,18))
-  FROM curr_rates
-  WHERE curr_rates.kood = alt_currency
-    AND CAST(curr_rates.aeg AS date) <= DATEADD(day, -1, CAST(doc_date AS date))
-  ORDER BY curr_rates.aeg DESC), 1)
-UPDATE @pvn12_main_table SET dar_veids = (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = vat_code) where dar_veids is null
-UPDATE @pvn12_main_table SET do_limit = (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = vat_code)
------------------
------- 1-3 ------
------------------
---izveidojam PVN 13 kodu tabulu
-  DECLARE @pvn13_vat_codes TABLE
-  (
-    code nvarchar(30),
-    rate decimal,
-    description nvarchar(200),
-    sales_account nvarchar(30),
-    purchase_account nvarchar(30),
-    info nvarchar(200),
-    dar_veids nvarchar(5),
-    do_limit varchar(2)
-  )
-  INSERT INTO @pvn13_vat_codes
-    SELECT kood, ilmakm, seletus, myykkonto, ostuKMkonto, lisainfo,
-      CAST(ISNULL((SELECT TOP 1 replace(sisu,'_','.') FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_3' AND klass='kmk'), '41') AS nvarchar(10)) AS dar_veids,
-      CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS varchar(2)) AS do_limit
-    FROM fin_kmkoodid fk
-    WHERE lisainfo LIKE '%pvn13%'
-Declare @pvn1_3_temp TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,4),
-    vatc_prepayment decimal(15,4)
-)
-Declare @pvn1_3_values_by_doc TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,4),
-    vatc_prepayment decimal(15,4),
-	rn int
-)
-Declare @pvn1_3_top_table TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,4),
-    vatc_prepayment decimal(15,4),
-	rn int
-)
-Declare @pvn1_3_top_tablex TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,4),
-    vatc_prepayment decimal(15,4),
-	rn int
-)
-Declare @tdeals TABLE
-(
-    dar_veids nvarchar(5),
-    do_limit varchar(2),
-    dok_veids nvarchar(1),
-    doc_nr nvarchar(30),
-    vat_code nvarchar(30),
-    client_name nvarchar(30),
-    vat_reg_nr nvarchar(30),
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4),
-    doc_date nvarchar(30),
-    doc_sum_total decimal(15,4),
-    vatc_prepayment decimal(15,4),
-	rn int
-)
-Declare @pvn1_3_top_sums_customer TABLE
-(
-    vat_reg_nr nvarchar(30),
+}
-    doc_sum decimal(15,4),
+ var cipari=['','viens ','divi ','trīs ','četri ','pieci ','seši ','septiņi ','astoņi ','deviņi '];
-    doc_vat decimal(15,4)
+ var padsmiti=['desmit ','vienpadsmit ','divpadsmit ','trīspadsmit ','četrpadsmit ','piecpadsmit ','sešpadsmit ','septiņpadsmit ','astoņpadsmit ','deviņpadsmit '];
-)
+ var desmiti=['','','divdesmit ','trīsdesmit ','četrdesmit ','piecdesmit ','sešdesmit ','septiņdesmit ','astoņdesmit ','deviņdesmit '];
-Declare @pvn1_3_sums_customer TABLE
+ function simti(str) {
-(
+  res='';
-    vat_reg_nr nvarchar(30),
+  // pirmais cipars no labaas
-    doc_sum decimal(15,4),
+  if (str.charAt(1)=='1') // chekojam pirmspeedeejo ciparu - ja viens tad vai nu desmit vai padsmit
-    doc_vat decimal(15,4)
+   res=padsmiti[parseInt(str.charAt(2))];
-)
+  else res=cipari[parseInt(str.charAt(2))];
- declare @v_totals TABLE
-  (
+  // otrais cipars no labaas
-    dok_veids nvarchar(32),
+  if (str.charAt(0)=='1') // ja treshais cipars no labaas ir 1 tad jaaraksta simts
-    client_name nvarchar(30),
+   res='simts '+desmiti[parseInt(str.charAt(1))]+res;
-    vat_reg_nr nvarchar(34),
+  else if (str.charAt(0)!='0') // ja treshais cipars no labaas nav 1 vai 0 tad jaaraksta simti
-    doc_sum decimal(15,4),
+   res='simti '+desmiti[parseInt(str.charAt(1))]+res;
-    doc_vat decimal(15,4)
+  else // ja nav simtu
-  )
+   res=desmiti[parseInt(str.charAt(1))]+res;
-  declare @x_totals TABLE
-  (
+  // treshais cipars no labaas
-    dok_veids nvarchar(32),
+  res=cipari[parseInt(str.charAt(0))]+res;
-    client_name nvarchar(30),
+  return res;
-    vat_reg_nr nvarchar(32),
+ }
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4)
-  )
-   declare @t_totals TABLE
-  (
-    doc_sum decimal(15,4),
-    doc_vat decimal(15,4)
-  )
---ielasam visas rēķina rindas kuras ir ar 1 - 3 atšifrējamajiem pvn kodiem
-insert @pvn1_3_temp
+ function numText(skaitlis,nauda) {
- SELECT
+  fullStr='0000000000';
-      (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids,
+  tempStr='';
-      (SELECT TOP 1 do_limit FROM @pvn13_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS do_limit,
+  tempRes='';
-      case when (isnull(ettemaks,0) = 0) then '1' else '1' end AS dok_veids,
+  resStr='';
-      mr_arved_read.number AS doc_nr,
+  minus='';
-      kmk as vat_code,
-      CAST((klient_nimi) AS nvarchar(30)) AS client_name,
+  punkts=skaitlis.indexOf('.');
-      case when (kmregnumber is not null or kmregnumber!=' ') then kmregnumber else (select top 1 kmregnr from kliendid where kood=mr_arved.klient_kood)end AS vat_reg_nr,
+  if (punkts==-1) {
-      CAST((ROUND(ISNULL(summa, 0), 4)) AS decimal(15,4)) AS doc_sum,
+   pirmspStr=skaitlis;
-      isnull(cast((ROUND(ISNULL(summa, 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS decimal(15,4)),'0.00') AS doc_vat,
+   aizpStr='0';
-      REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-')  AS doc_date
+  } else {
-    ,(select sum(summa) from mr_arved_read mar1 where mar1.number=mr_arved_read.number and kmk in (select code from @pvn13_vat_codes))
+   pirmspStr=skaitlis.substring(0,punkts);
-    ,(select sum(summa) from mr_arved_ettemaksud mae where mae.arve=mr_arved_read.number and mae.kmk=mr_arved_read.kmk)
+   aizpStr=skaitlis.substring(punkts+1);
-    FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number
+  }
-    WHERE
-      CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+  if (pirmspStr.charAt(0)=='-') { // ja ar miinus ziimi
-      AND (kinnitatud = 1)
+   pirmspStr=pirmspStr.substring(1);
-      and kmk in (SELECT code FROM @pvn13_vat_codes)
+   minus='mīnus ';
-      and artikkel is not null
+  }
-		and mr_arved.kokku >=0
+  for (i=pirmspStr.length-1, j=9; i>=0; i--, j--) { // paarliekam uz fullstr, kas sastaav no desmit cipariem
-insert @pvn1_3_temp
+   fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1);
-  SELECT
+  }
-       (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes vc WHERE vc.code = mlr.kmk) AS dar_veids,
-      (SELECT TOP 1 do_limit FROM @pvn13_vat_codes vc WHERE vc.code = mlr.kmk) AS do_limit,
+  if (fullStr.length>10) fullStr=fullStr.substring(fullStr.length-10); // ja vairaak par 10 cipariem, tad trim
-      '3' AS dok_veids,
-      CAST(ml.number AS nvarchar(30)) AS doc_nr,
+  if (parseInt(fullStr,10)!=0) {
-      CAST(mlr.kmk AS nvarchar(30)) AS vat_code,
+    resStr+=nauda;
-      CAST((SELECT TOP 1 nimi FROM kliendid WITH (NOLOCK) WHERE (kliendid.kood=mlr.klient_kood)) AS nvarchar(30)) AS client_name,
+  } else resStr+='Nulle '+nauda;
-     CAST((SELECT TOP 1 kmregnr FROM kliendid WITH (NOLOCK) WHERE (kliendid.kood=mlr.klient_kood)) AS nvarchar(30)) AS vat_reg_nr,
-      CAST((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1)) AS decimal(15,4)) AS doc_sum,
+  if (parseInt(fullStr.substring(7),10)>0) {
-      CAST(((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1))*(ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)) AS decimal(15,4)) AS doc_vat,
+   tempStr=fullStr.substring(7); // simti
-      REPLACE(CONVERT(VARCHAR, ml.aeg, 111), '/', '-')  AS doc_date,
+   tempRes=simti(tempStr);
-      CAST((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1)) AS decimal(15,4)),
+   resStr=tempRes+resStr;
+  }
-FROM mr_laekumised ml, mr_laekumised_read mlr WITH (NOLOCK)
-WHERE CAST(ml.aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
+  if (parseInt(fullStr.substring(4,7),10)>0) {
-AND (ml.kinnitatud='True')
+   tempStr=fullStr.substring(4,7); // t�ksto�i
-AND (ml.number=mlr.number)
+   tempRes=simti(tempStr);
-and mlr.kmk in (SELECT code FROM @pvn13_vat_codes)
+   if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='tūkstotis ';
-AND ((ISNULL(NULLIF(mlr.ettemaks,0),0)!=0) OR ((ISNULL(NULLIF(mlr.arvenumber,0),0)=0)))
+    else if (parseInt(tempStr,10)!=0) tempRes+='tūkstoši ';
+   resStr=tempRes+resStr;
-insert @pvn1_3_values_by_doc
+  }
-select      dar_veids
-        ,   do_limit
+  if (parseInt(fullStr.substring(1,4),10)>0) {
-        ,   dok_veids
+   tempStr=fullStr.substring(1,4); // miljoni
-        ,   doc_nr
+   tempRes=simti(tempStr);
-        ,   vat_code
+   if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='miljons ';
-        ,   client_name
+    else if (parseInt(tempStr,10)!=0) tempRes+='miljoni ';
-        ,   isnull(vat_reg_nr,' ')
+   resStr=tempRes+resStr;
-        ,   sum(doc_sum)- case when (dok_veids!='3') then (isnull((SELECT cast(summa / cast(1 + (select ilmakm  / 100 from fin_kmkoodid where kood=mae.kmk) as decimal(15,4)) as decimal(15,4)) FROM mr_arved_ettemaksud mae where mae.arve=pvn13temp.doc_nr and mae.kmk=pvn13temp.vat_code),0))  else 0 end
+  }
-        ,   sum(doc_vat) - case when (dok_veids!='3') then (isnull((SELECT cast(summa - (summa / cast(1 + (select ilmakm  / 100 from fin_kmkoodid where kood=mae.kmk) as decimal(15,4))) as decimal(15,4)) FROM mr_arved_ettemaksud mae where mae.arve=pvn13temp.doc_nr and mae.kmk=pvn13temp.vat_code),0)) else 0 end
-        ,   doc_date
+  if (fullStr.charAt(0)!='0') { // miljardi
-        ,   doc_sum_total
+   if (fullStr.charAt(0)=='1') resStr='viens miljards '+resStr;
-        ,   vatc_prepayment
+    else resStr=cipari[parseInt(fullStr.charAt(0),10)]+'miljardi '+resStr;
-		,	row_number() over(order by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, vatc_prepayment) nr
+  }
-from @pvn1_3_temp pvn13temp group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, vatc_prepayment
-insert @pvn1_3_sums_customer
+  if (resStr!='') { // pirmais burts lielais
-select
+   resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1);
-            isnull(vat_reg_nr,' ')
+  }
-        ,   sum(doc_sum)
-        ,   sum(doc_vat)
+  santimi=parseInt(aizpStr,10); //santimi
-from @pvn1_3_values_by_doc
+  if (santimi>0) {
-where isnull(vat_reg_nr,'')!=''
+   resStr+=' '+santimi;
-GROUP BY vat_reg_nr
+   tmpSant=santimi.toString();
+   if ((tmpSant.charAt(tmpSant.length-1)=='1') && (tmpSant.charAt(tmpSant.length-2)!='1')) {
---delete from @pvn1_3_temp where dok_veids in ('1','Z')
+    resStr+=' cents';
---only for anvol
+   } else {
-declare @fin_kanded_read table
+    resStr+=' centi';
-(
+   }
-sum_wo_vat decimal(15,4),
+  } else {
-vat_sum decimal(15,4)
+    resStr+=' 00 centi';
-)
+  }
-insert @fin_kanded_read
-select
+  return (minus+resStr);
-,sum(
+}
-			case
-				when (baas1deebet < 0) then (baas1deebet   * (-1))
-				when (baas1kreedit > 0) then (baas1kreedit)
-				end
-		) - sum(
-			case
-				when (baas1deebet > 0) then (baas1deebet)
-				when (baas1kreedit < 0) then (baas1kreedit * (-1))
-				end
-		) from fin_kanded_read where tyyp='fin'  and cast(r_aeg as date) between cast(@aeg1 as date) and cast(@aeg2 as date) and konto in ('5721','57210') and kmkood in ('1','16')
-union
-select
-		sum(
-			case
-				when (baas1deebet < 0) then (baas1deebet   * (-1))
-				when (baas1kreedit > 0) then (baas1kreedit)
-				end
-		) - sum(
-			case
-				when (baas1deebet > 0) then (baas1deebet)
-				when (baas1kreedit < 0) then (baas1kreedit * (-1))
-				end
-		),0  from fin_kanded_read where tyyp='fin'  and cast(r_aeg as date) between cast(@aeg1 as date) and cast(@aeg2 as date) and substring(convert(nvarchar(32),konto),1,1)='6' and kmkood in ('16','1')
-declare @pvn1_3tots table
-(
-doc_sum decimal(15,4),
-doc_vat decimal(15,4)
-)
-insert @pvn1_3tots
-SELECT CAST(SUM(doc_sum) + (select sum(sum_wo_vat) from @fin_kanded_read) AS decimal(15,4)) AS sum_total, CAST(SUM(doc_vat) + (select sum(vat_sum) from @fin_kanded_read) AS decimal(15,4)) AS vat_total
-  FROM @pvn1_3_values_by_doc
-insert @pvn1_3_top_table
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        ,   doc_sum_total
-        ,   vatc_prepayment
-		,	rn
-from @pvn1_3_values_by_doc
-where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1))) and doc_sum<>0 and isnull(vat_reg_nr,'')!=''
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn
-delete from @pvn1_3_values_by_doc where rn in (select rn from @pvn1_3_top_table)
-insert @pvn1_3_top_tablex
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        ,   doc_sum_total
-        ,   vatc_prepayment
-		, rn
-from @pvn1_3_values_by_doc
-where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1))) and isnull(vat_reg_nr,'')=''
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn
-delete from @pvn1_3_values_by_doc where rn in (select rn from @pvn1_3_top_tablex)
-insert @pvn1_3_top_sums_customer
-select
-            vat_reg_nr
-        ,   sum(doc_sum)
-        ,   sum(doc_vat)
-from @pvn1_3_top_table
-GROUP BY vat_reg_nr
-insert @v_totals
-select
-            'V'
-        ,   CAST((SELECT TOP 1 SUBSTRING(NIMI,1,30) FROM KLIENDID Y WHERE Y.KMREGNR=Z.vat_reg_nr) AS NVARCHAR(30))
-        ,   vat_reg_nr
-        ,   cast(doc_sum  - isnull((select top 1 doc_sum from @pvn1_3_top_sums_customer x where x.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4))
-        ,   cast(doc_vat  - isnull((select top 1 doc_vat from @pvn1_3_top_sums_customer x where x.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4))
-from @pvn1_3_sums_customer z
-where (doc_sum >= @lim or doc_sum <= (@lim * (-1)))
-and cast(doc_sum  - isnull((select top 1 doc_sum from @pvn1_3_top_sums_customer x where x.vat_reg_nr=z.vat_reg_nr),0) as decimal(15,4)) > 0
-and isnull(vat_reg_nr,'')!=''
-delete from @pvn1_3_values_by_doc where vat_reg_nr in (select vat_reg_nr from @v_totals)
-insert @tdeals
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        ,   doc_sum_total
-        ,   vatc_prepayment
-		,	rn
-from @pvn1_3_values_by_doc
-where (doc_sum between (@lim * (-1)) and @lim) and isnull(vat_reg_nr,'')!=''
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn
-union
-select
-            dar_veids
-        ,   do_limit
-        ,   dok_veids
-        ,   doc_nr
-        ,   vat_code
-        ,   client_name
-        ,   vat_reg_nr
-        ,   doc_sum
-        ,   doc_vat
-        ,   doc_date
-        ,   doc_sum_total
-        ,   vatc_prepayment
-		,	rn
-from @pvn1_3_values_by_doc
-where (doc_sum between (@lim * (-1)) and @lim) and isnull(vat_reg_nr,'')=''
-group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn
-delete from @pvn1_3_values_by_doc where rn in (select rn from @tdeals)
-insert @t_totals
-select
-           cast(sum(doc_sum) as decimal(15,4))
-        ,   cast(sum(doc_vat) as decimal(15,4))
-from @tdeals z
-insert @x_totals
-select
-            'X'
-        ,   ''
-        ,   ''
-        ,   cast(sum(doc_sum) as decimal(15,4))
-        ,   cast(sum(doc_vat)  as decimal(15,4))
-from @pvn1_3_top_tablex z
---where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1)))  and doc_sum<>0 and isnull(vat_reg_nr,'')=''
------------------
------- 2-1 ------
------------------
-DECLARE @pvn21_vat_codes TABLE
-(
-  code nvarchar(30),
-  rate decimal,
-  proportion decimal,
-  description nvarchar(200),
-  sales_account nvarchar(30),
-  purchase_account nvarchar(30),
-  info nvarchar(200),
-  dar_veids nvarchar(5),
-  do_limit varchar(2)
-)
-INSERT INTO @pvn21_vat_codes
-  SELECT kood, ilmakm, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS_2' AND klass='kmk'), 'G') AS nvarchar(10)) AS dar_veids,
-    CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS varchar(2)) AS do_limit
-  FROM fin_kmkoodid fk
-  WHERE lisainfo LIKE '%pvn21%'
-DECLARE @invoices TABLE
-(
-  pazime nvarchar(5),
-  do_limit varchar(2),
-  vat_reg_nr nvarchar(30),
-  doc_nr nvarchar(30),
-  vat_code nvarchar(30),
-  article_sum decimal(15,2)
-)
-INSERT INTO @invoices
-/*
- SELECT
-   '' AS pazime,
-   '' AS do_limit,
-   (SELECT TOP 1 kmregnr FROM kliendid, mr_arved WHERE (kliendid.kood=mr_arved.klient_kood) AND (mr_arved.number=mr_arved_read.number)) AS vat_reg_nr,
-   number AS doc_nr,
-   CAST(kmk AS nvarchar(30)) AS vat_code,
-   ISNULL(summa,0)*(SELECT ISNULL(kurssbv1,1) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number) AS article_sum
- FROM mr_arved_read WITH (NOLOCK)
- WHERE (SELECT CAST(aeg AS date) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-   AND ((SELECT kinnitatud FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number)=1)
-   AND (EXISTS(SELECT 1 FROM @pvn21_vat_codes WHERE code = kmk))
-*/
-select
-	distinct
-		(select dar_veids from @pvn21_vat_codes where code=kmk),
-		(select do_limit from @pvn21_vat_codes where code=kmk),
-		substring(isnull((SELECT TOP 1 kmregnr FROM kliendid WHERE (kliendid.kood=(select klient_kood from mr_arved where mr_arved.number=mar.number))),''),1,30),
-		mar.number,
-		mar.kmk,
-		cast(sum(summa) * (SELECT ISNULL(kurssbv1,1) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number) as decimal(15,4))
-from mr_arved_read mar
-WHERE (SELECT CAST(aeg AS date) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date)
-   AND ((SELECT kinnitatud FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number)=1)
-   AND (EXISTS(SELECT 1 FROM @pvn21_vat_codes WHERE code = kmk))
-   and kmk in (select code from @pvn21_vat_codes)
-group by number, kmk
-union
-select
-		(select dar_veids from @pvn21_vat_codes where code=mlr.kmk),
-		(select do_limit from @pvn21_vat_codes where code=mlr.kmk),
-		substring(isnull((SELECT TOP 1 kmregnr FROM kliendid WHERE (kliendid.kood=mlr.klient_kood)),''),1,30),
-		mlr.number,
-		mlr.kmk,
-		cast(sum(summa_p) * (ISNULL(kurss_p,1)) as decimal(15,4))
-from mr_laekumised_read mlr
-left join mr_laekumised ml on ml.number=mlr.number
-where cast(ml.aeg as date) between cast(@aeg1 as date) and cast(@aeg2 as date)
-and isnull(ml.kinnitatud,0)=1
-and mlr.kmk in (select code from @pvn21_vat_codes)
-group by mlr.number,mlr.kmk, mlr.klient_kood, mlr.summa_p,mlr.kurss_p
---UPDATE @invoices SET pazime = (SELECT TOP 1 dar_veids FROM @pvn21_vat_codes vc WHERE vc.code = vat_code)
---UPDATE @invoices SET do_limit = (SELECT TOP 1 do_limit FROM @pvn21_vat_codes vc WHERE vc.code = vat_code)
-DECLARE @invoices_grouped TABLE (pazime nvarchar(1), vat_reg_nr nvarchar(30), doc_nr nvarchar(30), article_sum decimal(15,2))
-INSERT INTO @invoices_grouped (pazime, vat_reg_nr, doc_nr, article_sum)
-  SELECT pazime, vat_reg_nr, doc_nr, SUM(article_sum) FROM @invoices GROUP BY vat_reg_nr, pazime, doc_nr
---xml out
-SELECT
-(
-  SELECT @aeg1 AS date1, @aeg2 AS date2, CURRENT_TIMESTAMP AS date_now
-  FOR XML PATH('dates'), TYPE, ELEMENTS
-),
-(
-  SELECT * FROM @pvn11_vat_codes
-  FOR XML PATH('code'),TYPE, ELEMENTS
-) AS [pvn11_vat_codes],
-(
-  SELECT * FROM @pvn12_vat_codes
-  FOR XML PATH('code'),TYPE, ELEMENTS
-) AS [pvn12_vat_codes],
-(
-  SELECT * FROM @pvn13_vat_codes
-  FOR XML PATH('code'),TYPE, ELEMENTS
-) AS [pvn13_vat_codes],
-(
-  SELECT * FROM @pvn21_vat_codes
-  FOR XML PATH('code'),TYPE, ELEMENTS
-) AS [pvn21_vat_codes],
-(
-  SELECT @vat_declar_name AS vat_declar_name, @stat_vat AS stat_vat, @lim AS lim, @account AS account
-  FOR XML PATH('filters'),TYPE, ELEMENTS
-),
-(
-  SELECT setting AS name,
-  (SELECT setting FROM settings WHERE id='firma_kmnr') AS vat_reg_nr,
-  (SELECT setting FROM settings WHERE id='firma_regnr') AS reg_nr,
-  (SELECT setting FROM settings WHERE id='firma_telefon') AS tel,
-  (SELECT setting FROM settings WHERE id='firma_faks') AS fax,
-  (SELECT setting FROM settings WHERE id='firma_aadress') AS address1,
-  (SELECT setting FROM settings WHERE id='firma_aadress2') AS address2,
-  (SELECT setting FROM settings WHERE id='firma_aadress3') AS address3,
-  (SELECT setting FROM settings WHERE id='firma_tegevusaadress') AS biz_address1,
-  (SELECT setting FROM settings WHERE id='firma_tegevusaadress2') AS biz_address2,
-  (SELECT setting FROM settings WHERE id='firma_tegevusaadress3') AS biz_address3,
-  (SELECT setting FROM settings WHERE id='firma_pank') AS bank,
-  (SELECT setting FROM settings WHERE id='firma_swift') AS bank_swift,
-  (SELECT setting FROM settings WHERE id='firma_aa') AS account,
-  (SELECT setting FROM settings WHERE id='firma_iban') AS iban,
-  (SELECT setting FROM settings WHERE id='firma_juht') AS head
-  FROM settings WHERE id='firma_nimi'
-  FOR XML PATH('company_info'), TYPE, ELEMENTS
-),
-(
-  SELECT * FROM @vat_declar_sums
-  FOR XML PATH('vat_declar_sum'), TYPE, ELEMENTS
-) AS [vat_declar_totals],
-( --pvn11
-  SELECT dar_veids, do_limit, doc_nr, doc_nr as client_doc_nr, vat_code, dok_veids AS type, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat, doc_date AS date, rn
-  FROM @pvn1_1_top_table
-where (doc_sum <> 0 or doc_vat <> 0)
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn11_docs_above],
-(
-   SELECT dar_veids, do_limit, doc_nr, doc_nr as client_doc_nr, vat_code, dok_veids AS type, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat, doc_date as date
-  FROM @pvn1_1_top_r_table
-where (doc_sum <> 0 or doc_vat <> 0)
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn11_docs_above],
-(
-   SELECT 'V' as dar_veids, '' as do_limit, '' as doc_nr, '' as client_doc_nr, '' as vat_code, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat, '' as doc_date
-  FROM @pvn_1_1_v_totals2
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn11_docs_above],
-(
-  SELECT CAST(SUM(doc_sum) AS decimal(15,2)) AS sum_total, CAST(SUM(doc_vat) AS decimal(15,2)) AS vat_total
-  FROM @pvn_1_1_t_totals FOR XML PATH('pvn11_totals_below'), TYPE, ELEMENTS
-),
-(
+ ]]></msxsl:script>
-  SELECT doc_sum AS sum_total,doc_vat AS vat_total
-  FROM  @pvn_1_1_totals
-  FOR XML PATH('pvn11_totals'), TYPE, ELEMENTS
-),
-/*
-(
-  SELECT CAST(SUM((doc_sum) * (-1)) AS decimal(15,2)) AS sum_total, CAST(SUM((doc_vat) * (-1)) AS decimal(15,2)) AS vat_total
-  FROM @pvn13_main_table_11
-  FOR XML PATH('pvn11_totals'), TYPE, ELEMENTS
-),--/pvn11 */
-( --pvn12
-  SELECT dar_veids, do_limit, doc_nr, client_doc_nr, vat_code, directo_table, doc_type AS type, client_name, vat_reg_nr, country_code, doc_sum AS sum, doc_vat AS vat, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date AS date
-  FROM @pvn12_main_table
-  where  (doc_sum <> 0)
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn12_docs],
-(
-  SELECT CAST(SUM(doc_sum) AS decimal(15,2)) AS sum_total, CAST(SUM(doc_vat) AS decimal(15,2)) AS vat_total
-  FROM @pvn12_main_table
-  FOR XML PATH('pvn12_totals'), TYPE, ELEMENTS
-),
-( -- rates
-  SELECT currency, (SELECT TOP 1 CAST(kurss1 AS decimal(28,18)) AS rate, aeg AS date FROM curr_rates WHERE kood=currency ORDER BY aeg DESC FOR XML PATH(''),TYPE)
-  FROM @pvn12_currencies
-  FOR XML PATH('rate'), TYPE, ELEMENTS
-) AS [pvn12_currency_rates], --/pvn12
- ( --pvn13
-    SELECT dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat, doc_date AS date
-    FROM @pvn1_3_top_table
-    FOR XML PATH('doc'), TYPE, ELEMENTS
-  ) AS [pvn13_docs_above],
-  (
+ </xsl:stylesheet>
-  SELECT dok_veids, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat
-  FROM @v_totals
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn13_docs_above],
-  (
-  SELECT dok_veids, client_name, vat_reg_nr, doc_sum AS sum, doc_vat AS vat
-  FROM @x_totals
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn13_docs_above],
-(
-  SELECT isnull(sum(doc_sum),0) + (select sum(sum_wo_vat) from @fin_kanded_read) AS sum_total, isnull(sum(doc_vat),0) + (select sum(vat_sum) from @fin_kanded_read) AS vat_total
-  FROM @t_totals
-  FOR XML PATH('pvn13_totals_below'), TYPE, ELEMENTS
-),
-(
-  SELECT CAST(SUM(doc_sum) AS decimal(15,2)) AS sum_total, CAST(SUM(doc_vat) AS decimal(15,2)) AS vat_total
-  FROM @pvn1_3tots
-  FOR XML PATH('pvn13_totals'), TYPE, ELEMENTS
-), --/pvn13
-/*
-  (
-  SELECT dok_veids, client_name, vat_reg_nr, sum(doc_sum) AS sum, sum(doc_vat) AS vat
-  FROM @pvn13_sums_above_table where dok_veids!='T' group by dok_veids, client_name, vat_reg_nr
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn13_docs_above],
-(
-  SELECT CAST(SUM(doc_sum) AS decimal(15,2)) AS sum_total, CAST(SUM(doc_vat) AS decimal(15,2)) AS vat_total
-  FROM @pvn13_sums_above_table m where dok_veids='T'
-  FOR XML PATH('pvn13_totals_below'), TYPE, ELEMENTS
-),
-  (
-    SELECT CAST((SUM(toptabledoc)) AS decimal(15,2)) AS sum_total, CAST(SUM(toptablevat) AS decimal(15,2)) AS vat_total
-    FROM @pvn13sums
-    FOR XML PATH('pvn13_totals'), TYPE, ELEMENTS
-  ),*/ --/pvn13
-( --pvn21
-  SELECT pazime, vat_reg_nr, CAST(SUM(article_sum) AS decimal(15,2)) AS sum
-  FROM @invoices_grouped
-    GROUP BY vat_reg_nr, pazime
-  FOR XML PATH('doc'), TYPE, ELEMENTS
-) AS [pvn21_docs],
-(
-  SELECT CAST(SUM(article_sum) AS decimal(15,2)) AS sum_total
-  FROM @invoices_grouped
-  FOR XML PATH('pvn21_totals'), TYPE, ELEMENTS
-) --/pvn21
-FOR XML PATH('document'), TYPE, ELEMENTS
 </code>

Directo palīdzība

Lietotāja rīki

Vietnes rīki

Atšķirības

Lapas rīki