Norādītais vecais variants no patreizējās lapas atšķiras ar:
Abās pusēs iepriekšējo versiju Iepriekšējā versija Nākamā versija | Iepriekšējā versija Jaunākā versija Abās pusēs nākamo versiju | ||
lv:procedures [2020/10/01 15:30] marija dentoflex pvn |
lv:procedures [2020/10/27 19:04] marija ALG_rekins_0610 |
||
---|---|---|---|
Rinda 1: | Rinda 1: | ||
- | <code sql> | + | <code> |
- | ALTER PROCEDURE dbo.int_hooldus_klient_pvn @aeg1 datetime, @aeg2 datetime AS | + | <!--intclientxsl:0:arve:5162:DIRECTO15\DIRECTO15B:ocra_algoritmsl_lv:[:::]--> |
- | + | <?xml version="1.0" encoding="utf-8"?> | |
- | --static vars | + | <xsl:stylesheet version="1.0" |
- | DECLARE @vat_declar_name nvarchar(200) SET @vat_declar_name = 'PVN' | + | xmlns:xsl="http://www.w3.org/1999/XSL/Transform" |
- | DECLARE @stat_vat decimal SET @stat_vat = 21 | + | xmlns:fo="http://www.w3.org/1999/XSL/Format" |
- | 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:msxsl="urn:schemas-microsoft-com:xslt" |
- | DECLARE @account int SET @account = 57211 | + | xmlns:js="urn:formulas" |
- | DECLARE @i int SET @i = 1 | + | exclude-result-prefixes="msxsl js fo"> |
- | DECLARE @x nvarchar(10) SET @x = '1' | + | <xsl:output method="html"/> |
- | DECLARE @y nvarchar(10) SET @y = '1' | + | <xsl:decimal-format name="N" NaN="0" decimal-separator='.' grouping-separator=' ' /> |
- | + | <xsl:template match="/"> | |
- | --numbers helper | + | <xsl:variable name="item_per_page">25</xsl:variable> |
- | DECLARE @numbers TABLE (n int) INSERT INTO @numbers | + | <xsl:variable name="eiro"> |
- | SELECT TOP 1000 row_number() over(ORDER BY t1.number) | + | <xsl:choose> |
- | FROM master..spt_values t1 | + | <xsl:when test="/documents/document/valuuta='LVL'"> |
- | CROSS JOIN master..spt_values t2 | + | 1.42287181 |
- | --vat summary | + | </xsl:when> |
- | DECLARE @vat_declar_sums TABLE | + | <xsl:when test="/documents/document/valuuta=''"> |
- | ( | + | 1.42287181 |
- | row_nr int, | + | </xsl:when> |
- | row_type int, | + | <xsl:when test="/documents/document/valuuta='EUR'"> |
- | c_class int, | + | 0.702804 |
- | vat_code nvarchar(30), | + | </xsl:when> |
- | nr nvarchar(30), | + | <xsl:when test="/documents/document/valuuta!='EUR' and /documents/document/valuuta!='LVL'"> |
- | description nvarchar(200), | + | <xsl:value-of select="/documents/document/kurssbv1"/> |
- | range_formula nvarchar(200), | + | </xsl:when> |
- | doc_sum decimal(15,2) | + | </xsl:choose> |
- | ) | + | </xsl:variable> |
- | INSERT INTO @vat_declar_sums | + | <xsl:variable name="nauda"> |
- | SELECT ROW_NUMBER() OVER (ORDER BY kood), tyyp, kontoklass, kmkood, number, tekst, kontod, 0 | + | <xsl:choose> |
- | FROM fin_aru_kaive_read WITH (NOLOCK) | + | <xsl:when test="/documents/document/valuuta='LVL'">LVL</xsl:when> |
- | WHERE kood=@vat_declar_name | + | <xsl:when test="/documents/document/valuuta=''">LVL</xsl:when> |
- | AND (tyyp = 1 OR tyyp = 2 OR tyyp = 8 OR tyyp = 7) | + | <xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'"> |
- | ORDER BY number | + | <xsl:value-of select="/documents/document/valuuta"/> |
- | + | </xsl:when> | |
- | --account range elements | + | </xsl:choose> |
- | DECLARE @elements TABLE (nr nvarchar(30), e nvarchar(200)) | + | </xsl:variable> |
- | INSERT INTO @elements | + | <xsl:variable name="nauda2"> |
- | SELECT | + | <xsl:choose> |
- | nr, | + | <xsl:when test="/documents/document/valuuta='LVL'">EUR</xsl:when> |
- | RTRIM(LTRIM(SUBSTRING('+' + range_formula + '+',n+1,CHARINDEX('+','+' + range_formula + '+',n+1)-n-1))) | + | <xsl:when test="/documents/document/valuuta=''">EUR</xsl:when> |
- | FROM @numbers, @vat_declar_sums | + | <xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'">LVL</xsl:when> |
- | WHERE n < LEN('+' + range_formula + '+') | + | </xsl:choose> |
- | AND SUBSTRING('+' + range_formula + '+',n,1) = '+' | + | </xsl:variable> |
- | AND range_formula LIKE '_%' | + | <xsl:variable name="rekina_summa"> |
- | AND (row_type = 1 or row_type = 8 or row_type = 7) | + | <xsl:choose> |
- | + | <xsl:when test="$nauda='LVL'"> | |
- | --account ranges start-end | + | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) * $eiro, '0.00')" /> |
- | DECLARE @element_ranges TABLE (row_nr int, nr nvarchar(30), e_start nvarchar(200), e_end nvarchar(200)) | + | </xsl:when> |
- | INSERT INTO @element_ranges | + | <xsl:when test="$nauda='EUR'"> |
- | SELECT | + | <xsl:value-of select="format-number(/documents/document/converted_tasuda div 100, '0.00')" /> |
- | ROW_NUMBER() OVER (ORDER BY nr), | + | </xsl:when> |
- | nr, | + | <xsl:when test="$nauda!='LVL' or $nauda!='EUR'"> |
- | CASE WHEN CHARINDEX(':', e) > 0 THEN LEFT(e, CHARINDEX(':', e)-1) ELSE e END, | + | <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/> |
- | CASE WHEN CHARINDEX(':', e) > 0 THEN SUBSTRING(e, CHARINDEX(':', e)+1, LEN(e)) ELSE e END | + | </xsl:when> |
- | FROM @elements | + | </xsl:choose> |
- | + | </xsl:variable> | |
- | --update vat declaration sums according to account ranges | + | <xsl:variable name="saldo"> |
- | WHILE @i <= (SELECT COUNT(row_nr) FROM @element_ranges) | + | <xsl:choose> |
- | BEGIN | + | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> |
- | 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:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> |
- | 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> |
- | + | <xsl:when test="$nauda!='LVL' or $nauda!='EUR'"> | |
- | UPDATE @vat_declar_sums SET doc_sum = doc_sum + | + | <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/> |
- | ISNULL(( | + | </xsl:when> |
- | SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0)) | + | </xsl:choose> |
- | FROM fin_kanded_read WITH (NOLOCK) | + | |
- | WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) | + | </xsl:variable> |
- | AND konto BETWEEN @x AND @y | + | <xsl:variable name="parads"> |
- | AND kmkood = vat_code | + | <xsl:value-of select="format-number($saldo - $rekina_summa ,'0.00')"/> |
- | ),0) | + | </xsl:variable> |
- | WHERE ISNUMERIC(vat_code) = 1 AND c_class = 3 and row_type=1 | + | <xsl:variable name="neapm_summa"> |
- | AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) | + | <xsl:choose> |
- | UPDATE @vat_declar_sums SET doc_sum = doc_sum + | + | <xsl:when test="/documents/document/valuuta!=''"> |
- | ISNULL(( | + | <xsl:value-of select="format-number($parads, '0.00')"/> |
- | SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0)) | + | </xsl:when> |
- | FROM fin_kanded_read WITH (NOLOCK) | + | <xsl:when test="/documents/document/valuuta=''"> |
- | WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) | + | <xsl:value-of select="$parads"/> |
- | AND konto BETWEEN @x AND @y | + | </xsl:when> |
- | AND kmkood = vat_code | + | </xsl:choose> |
- | ),0) | + | </xsl:variable> |
- | WHERE ISNUMERIC(vat_code) = 0 AND c_class = 3 and row_type=1 | + | <xsl:variable name="atlaide"> |
- | AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) | + | <xsl:choose><xsl:when test="sum(documents/document/rows/row[pross > 0]/pross) > 0">1</xsl:when> |
+ | <xsl:otherwise>0</xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="atlaides_summa"> | ||
+ | <xsl:for-each select="/documents/document/rows/row[pross > 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. </xsl:when> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credit note no. </xsl:when> | ||
+ | <xsl:when test="/documents/document/keel=''">Rēķins Nr. </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. <xsl:value-of select="/documents/document/kredarve" /><br /></xsl:when> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credited invoice no. <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>:  | ||
+ | <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!=''"> <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!=''"> <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> </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> </td> | ||
+ | <td class="nos" width="250"><xsl:value-of disable-output-escaping="yes" select="keelne_seletus" /></td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <xsl:if test="$atlaide!=0"> | ||
+ | <td> </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;"> </td></tr> | ||
+ | <xsl:text disable-output-escaping="yes"></table></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)" />  | ||
+ | </div> | ||
+ | <xsl:text disable-output-escaping="yes"></div></xsl:text> | ||
+ | | ||
+ | <div class="pbreak"></div> | ||
+ | |||
+ | <xsl:text disable-output-escaping="yes"><div class="container"></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"><table class="main_table" height="740" cellspacing="0" cellpadding="0"></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;"> </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, | + | |
- | 0 | + | |
- | 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, | + | |
- | 0 | + | |
- | 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, | + | |
- | 0 | + | |
- | 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 | + | |
- | 0 | + | |
- | 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 | + | |
- | 0 | + | |
- | 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 | + | |
- | 0 | + | |
- | 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 | + |   |
- | ( | + | </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--> <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: <xsl:value-of select="ilmakm" />%:</xsl:when> |
- | alt_rate decimal(28, 18), | + | <xsl:otherwise>VAT <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')" /> <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')" /> <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> |
- | 1 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')" /> <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')" /> <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')" /> <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')" /> <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--> <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 > $rekina_summa"> | ||
+ | <tr> | ||
+ | <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%"> | ||
+ |   | ||
+ | </td> | ||
+ | <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;"> | ||
+ | <xsl:value-of select="$neapm_summa" />  | ||
+ | <xsl:value-of select="$nauda" /> | ||
+ | </td> | ||
+ | </tr> | ||
+ | </xsl:if> | ||
+ | <tr> | ||
+ | <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%"> | ||
+ |   | ||
+ | </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')" /> | ||
+ |   | ||
+ | <xsl:value-of select="$nauda" /> | ||
+ | </xsl:when> | ||
+ | <xsl:otherwise> | ||
+ | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) + $parads, '0.00')" />  | ||
+ | <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%"> | ||
+ |   | ||
+ | </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>$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')"/> <xsl:value-of select="$nauda"/><br /> | ||
+ | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <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')"/> <xsl:value-of select="$nauda2"/><br /> | ||
+ | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <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')"/> <xsl:value-of select="$nauda"/><br /> | ||
+ | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <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')"/> <xsl:value-of select="$nauda2"/><br /> | ||
+ | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <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, | + | |
- | 1 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, | + | |
- | 0 | + | |
- | 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, | + | |
- | 1 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, | + | |
- | 0 | + | |
- | 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, | + | |
- | 1 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)), | + | |
- | 1, | + | |
- | '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; |
- | 0 | + | } |
- | 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); |
- | 0,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> | </code> |