modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/ad_reports/UnreconciledBankStatement.jrxml
changeset 9076 c4f1a5c7d7c2
child 14158 5a31fb989baf
equal deleted inserted replaced
9075:b3ea04de2722 9076:c4f1a5c7d7c2
       
     1 <?xml version="1.0" encoding="UTF-8"  ?>
       
     2 <!-- Created with iReport - A designer for JasperReports -->
       
     3 <!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
       
     4 <jasperReport
       
     5 		 name="UnreconciledBankStatement"
       
     6 		 columnCount="1"
       
     7 		 printOrder="Vertical"
       
     8 		 orientation="Portrait"
       
     9 		 pageWidth="495"
       
    10 		 pageHeight="802"
       
    11 		 columnWidth="495"
       
    12 		 columnSpacing="0"
       
    13 		 leftMargin="0"
       
    14 		 rightMargin="0"
       
    15 		 topMargin="0"
       
    16 		 bottomMargin="0"
       
    17 		 whenNoDataType="NoPages"
       
    18 		 isTitleNewPage="false"
       
    19 		 isSummaryNewPage="false">
       
    20 	<property name="ireport.scriptlethandling" value="0" />
       
    21 	<property name="ireport.encoding" value="UTF-8" />
       
    22 	<import value="java.util.*" />
       
    23 	<import value="net.sf.jasperreports.engine.*" />
       
    24 	<import value="net.sf.jasperreports.engine.data.*" />
       
    25 
       
    26 	<parameter name="RECONCILIATION_ID" isForPrompting="false" class="java.lang.String"/>
       
    27 	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
       
    28 	<queryString><![CDATA[SELECT bsl.datetrx as BSLINE_DATE, coalesce(TO_CHAR(bp.name), TO_CHAR(bsl.bpartnername), '') as DESCRIPTION,
       
    29        bsl.referenceno as REF, (bsl.cramount - bsl.dramount) as AMOUNT
       
    30 FROM fin_bankstatementline bsl join fin_bankstatement bs on (bsl.fin_bankstatement_id=bs.fin_bankstatement_id)
       
    31      left join c_bpartner bp on (bp.c_bpartner_id = bsl.c_bpartner_id)
       
    32 WHERE bs.fin_financial_account_id = (SELECT rec.fin_financial_account_id
       
    33                                      FROM fin_reconciliation rec
       
    34                                      WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})
       
    35       and bsl.datetrx <= (SELECT rec.dateto
       
    36                           FROM fin_reconciliation rec
       
    37                           WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})
       
    38       and (bsl.fin_finacc_transaction_id is null 
       
    39           or bsl.fin_finacc_transaction_id in (select fin_finacc_transaction_id
       
    40 						from fin_finacc_transaction, fin_reconciliation
       
    41 						where fin_finacc_transaction.fin_reconciliation_id = fin_reconciliation.fin_reconciliation_id
       
    42 						and fin_reconciliation.created > (SELECT rec.created
       
    43 							     FROM fin_reconciliation rec
       
    44 							     WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})))
       
    45 ORDER BY BSLINE_DATE, DESCRIPTION, AMOUNT]]></queryString>
       
    46 
       
    47 	<field name="BSLINE_DATE" class="java.util.Date"/>
       
    48 	<field name="DESCRIPTION" class="java.lang.String"/>
       
    49 	<field name="REF" class="java.lang.String"/>
       
    50 	<field name="AMOUNT" class="java.math.BigDecimal"/>
       
    51 
       
    52 	<variable name="SUM_LINEAMT" class="java.math.BigDecimal" resetType="Report" calculation="Sum">
       
    53 		<variableExpression><![CDATA[$F{AMOUNT}]]></variableExpression>
       
    54 	</variable>
       
    55 		<background>
       
    56 			<band height="0"  isSplitAllowed="true" >
       
    57 			</band>
       
    58 		</background>
       
    59 		<title>
       
    60 			<band height="0"  isSplitAllowed="true" >
       
    61 			</band>
       
    62 		</title>
       
    63 		<pageHeader>
       
    64 			<band height="0"  isSplitAllowed="true" >
       
    65 			</band>
       
    66 		</pageHeader>
       
    67 		<columnHeader>
       
    68 			<band height="0"  isSplitAllowed="true" >
       
    69 			</band>
       
    70 		</columnHeader>
       
    71 		<detail>
       
    72 			<band height="15"  isSplitAllowed="true" >
       
    73 				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
       
    74 					<reportElement
       
    75 						x="0"
       
    76 						y="0"
       
    77 						width="70"
       
    78 						height="15"
       
    79 						forecolor="#000000"
       
    80 						key="textField"/>
       
    81 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
       
    82 					<textElement textAlignment="Left" verticalAlignment="Middle">
       
    83 						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
       
    84 					</textElement>
       
    85 				<textFieldExpression   class="java.util.Date"><![CDATA[$F{BSLINE_DATE}]]></textFieldExpression>
       
    86 				</textField>
       
    87 				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
       
    88 					<reportElement
       
    89 						x="70"
       
    90 						y="0"
       
    91 						width="188"
       
    92 						height="15"
       
    93 						forecolor="#000000"
       
    94 						key="textField"/>
       
    95 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
       
    96 					<textElement textAlignment="Left" verticalAlignment="Middle">
       
    97 						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
       
    98 					</textElement>
       
    99 				<textFieldExpression   class="java.lang.String"><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>
       
   100 				</textField>
       
   101 				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
       
   102 					<reportElement
       
   103 						x="258"
       
   104 						y="0"
       
   105 						width="122"
       
   106 						height="15"
       
   107 						forecolor="#000000"
       
   108 						key="textField"/>
       
   109 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
       
   110 					<textElement textAlignment="Left" verticalAlignment="Middle">
       
   111 						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
       
   112 					</textElement>
       
   113 				<textFieldExpression   class="java.lang.String"><![CDATA[$F{REF}]]></textFieldExpression>
       
   114 				</textField>
       
   115 				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
       
   116 					<reportElement
       
   117 						x="380"
       
   118 						y="0"
       
   119 						width="115"
       
   120 						height="15"
       
   121 						forecolor="#000000"
       
   122 						key="textField"/>
       
   123 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
       
   124 					<textElement textAlignment="Right" verticalAlignment="Middle">
       
   125 						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
       
   126 					</textElement>
       
   127 				<textFieldExpression   class="java.lang.String"><![CDATA[($F{AMOUNT}!=null) ? $P{NUMBERFORMAT}.format($F{AMOUNT}).toString() : new String(" ")]]></textFieldExpression>
       
   128 				</textField>
       
   129 			</band>
       
   130 		</detail>
       
   131 		<columnFooter>
       
   132 			<band height="0"  isSplitAllowed="true" >
       
   133 			</band>
       
   134 		</columnFooter>
       
   135 		<pageFooter>
       
   136 			<band height="0"  isSplitAllowed="true" >
       
   137 			</band>
       
   138 		</pageFooter>
       
   139 		<summary>
       
   140 			<band height="25"  isSplitAllowed="true" >
       
   141 				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None"  hyperlinkTarget="Self" >
       
   142 					<reportElement
       
   143 						x="380"
       
   144 						y="0"
       
   145 						width="115"
       
   146 						height="15"
       
   147 						key="textField-2"/>
       
   148 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
       
   149 					<textElement textAlignment="Right" verticalAlignment="Middle">
       
   150 						<font size="8"/>
       
   151 					</textElement>
       
   152 				<textFieldExpression   class="java.lang.String"><![CDATA[($V{SUM_LINEAMT}!=null) ? $P{NUMBERFORMAT}.format($V{SUM_LINEAMT}).toString() : new String(" ")]]></textFieldExpression>
       
   153 				</textField>
       
   154 				<staticText>
       
   155 					<reportElement
       
   156 						x="211"
       
   157 						y="0"
       
   158 						width="169"
       
   159 						height="15"
       
   160 						key="staticText-1"/>
       
   161 					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
       
   162 					<textElement textAlignment="Right" verticalAlignment="Middle">
       
   163 						<font size="8" isBold="false"/>
       
   164 					</textElement>
       
   165 				<text><![CDATA[Total Un-reconciled Bank Statement Lines]]></text>
       
   166 				</staticText>
       
   167 			</band>
       
   168 		</summary>
       
   169 </jasperReport>