<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Taro cheque print" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="142"/>
	<property name="ireport.y" value="0"/>
	<parameter name="as_tranid_fr" class="java.lang.String"/>
	<parameter name="as_tranid_to" class="java.lang.String"/>
	<queryString language="SQL">
		<![CDATA[select
		misc_payment.tran_id as tran_id,
		misc_payment.sundry_type as sundry_type,
		misc_payment.sundry_code as sundry_code ,
		misc_payment.ref_date as ref_date ,
		misc_payment.net_amt as net_amt ,
		misc_payment.bank_charges as bank_charges ,
		LPAD(to_char(misc_payment.net_amt + misc_payment.bank_charges),14,'*') as amt,
		misc_payment.net_amt + misc_payment.bank_charges as word_amt,
		FN_SUNDRY_NAME(misc_payment.SUNDRY_TYPE,misc_payment.SUNDRY_CODE,'C') as sunname,
		FDF_GET_SUNDRY_DETAILS_tarochq( misc_payment.SUNDRY_TYPE,misc_payment.SUNDRY_CODE,'D') as sundrydet,
		UPPER(REPLACE(DDF_GET_AMT_TO_WORDS_CURR_CD(misc_payment.net_amt + misc_payment.bank_charges , misc_payment.curr_code),'-',' ')) AS numtoword,
		bank.ac_no as bank_ac_no,
		bank.bank_refno as micr_no,
		1 as pagecnt,
		site.descr as site_desc,
		site.add1 as site_add1,
		site.add2 as site_add2,
		site.city as site_city,
		site.state_code as site_state_code,
		site.pin as site_pin
  from misc_payment , bank,site
 where
		misc_payment.bank_code = bank.bank_code
	and	misc_payment.site_code = site.site_code
	and  tran_id >= '$P!{as_tranid_fr}'
	and  tran_id <= '$P!{as_tranid_to}'
	and confirmed = 'Y'

union

select
		payment_exp.tran_id as tran_id ,
		payment_exp.sundry_type as sundry_type ,
		payment_exp.sundry_code as sundry_code ,
		payment_exp.ref_date as ref_date ,
		net_amt__bc  as net_amt ,
		0 as bank_charges,
		LPAD(to_char(payment_exp.net_amt__bc ),14,'*') as amt,
		payment_exp.net_amt__bc as word_amt,
		FN_SUNDRY_NAME(payment_exp.SUNDRY_TYPE,payment_exp.SUNDRY_CODE,'C') as sunname,
		FDF_GET_SUNDRY_DETAILS_tarochq( payment_exp.SUNDRY_TYPE,payment_exp.SUNDRY_CODE,'D') as sundrydet,
		UPPER(REPLACE(DDF_GET_AMT_TO_WORDS_CURR_CD(payment_exp.net_amt__bc , payment_exp.curr_code),'-',' ')) AS numtoword,
		bank.ac_no as bank_ac_no,
		bank.bank_refno as micr_no,
		CEIL((ROWNUM/13)) as pagecnt,
		site.descr as site_desc,
		site.add1 as site_add1,
		site.add2 as site_add2,
		site.city as site_city,
		site.state_code as site_state_code,
		site.pin as site_pin
  from payment_exp , bank,site
 where
	payment_exp.bank_code = bank.bank_code
	and payment_exp.site_code = site.site_code
	and  tran_id >= '$P!{as_tranid_fr}'
	and  tran_id <= '$P!{as_tranid_to}'
	and confirmed = 'Y']]>
	</queryString>
	<field name="TRAN_ID" class="java.lang.String"/>
	<field name="SUNDRY_TYPE" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="SUNDRY_CODE" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="REF_DATE" class="java.util.Date">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="NET_AMT" class="java.lang.Double">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="BANK_CHARGES" class="java.lang.Double">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="AMT" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="WORD_AMT" class="java.lang.Double">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="sundrydet" class="java.lang.String"/>
	<field name="numtoword" class="java.lang.String"/>
	<field name="bank_ac_no" class="java.lang.String"/>
	<field name="micr_no" class="java.lang.String"/>
	<field name="PAGECNT" class="java.lang.Integer"/>
	<field name="sunname" class="java.lang.String"/>
	<field name="site_desc" class="java.lang.String"/>
	<field name="site_add1" class="java.lang.String"/>
	<field name="site_add2" class="java.lang.String"/>
	<field name="site_city" class="java.lang.String"/>
	<field name="site_state_code" class="java.lang.String"/>
	<field name="site_pin" class="java.lang.String"/>
	<variable name="variable2" class="java.lang.Integer" resetType="Group" resetGroup="TranID">
		<variableExpression><![CDATA[$V{PAGE_NUMBER}]]></variableExpression>
	</variable>
	<variable name="compute_1" class="java.lang.String" resetType="None">
		<variableExpression><![CDATA[new java.lang.String((ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_desc}) ? " " : ($F{site_desc}).trim())+"\n  "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_add1}) ? " " : ($F{site_add1}).trim())+"\n  "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_add2}) ? " " : ($F{site_add2}).trim())+"\n  "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_city}) ? " " : ($F{site_city}).trim())+",  "+(ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_state_code} ) ? " " : ($F{site_state_code}).trim())+" "+ (ibase.webitm.reports.utility.BaseReportMethods.isNull($F{site_pin}) ? " " :($F{site_pin}).trim()))]]></variableExpression>
	</variable>
	<group name="TranID" isStartNewPage="true" isResetPageNumber="true">
		<groupExpression><![CDATA[$F{TRAN_ID}]]></groupExpression>
		<groupHeader>
			<band height="32">
				<subreport>
					<reportElement positionType="Float" x="0" y="1" width="552" height="31" printWhenGroupChanges="TranID"/>
					<parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
					<subreportParameter name="AS_TRAN_ID">
						<subreportParameterExpression><![CDATA[$F{TRAN_ID}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression class="java.io.InputStream"><![CDATA[new FileInputStream("/data/jboss-5.1.0.GA/server/default/deploy/ibase.ear/ibase.war/webitm/reports/jrxml/d_taro_cheque_print_sub.jasper")]]></subreportExpression>
				</subreport>
			</band>
		</groupHeader>
		<groupFooter>
			<band/>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<pageHeader>
		<band/>
	</pageHeader>
	<detail>
		<band splitType="Stretch"/>
	</detail>
	<pageFooter>
		<band height="214" splitType="Stretch">
			<staticText>
				<reportElement x="24" y="64" width="255" height="24">
					<printWhenExpression><![CDATA[new Boolean($V{variable2}!=$V{PAGE_NUMBER}?true:false)]]></printWhenExpression>
				</reportElement>
				<textElement>
					<font fontName="Times New Roman" size="9"/>
				</textElement>
				<text><![CDATA[* VOID * VOID * VOID * VOID * VOID * VOID ]]></text>
			</staticText>
			<textField pattern="0.00" isBlankWhenNull="true">
				<reportElement x="439" y="97" width="100" height="20">
					<printWhenExpression><![CDATA[new Boolean($V{variable2}==$V{PAGE_NUMBER}?true:false)]]></printWhenExpression>
				</reportElement>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{AMT}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement x="24" y="66" width="338" height="22">
					<printWhenExpression><![CDATA[new Boolean($V{variable2}==$V{PAGE_NUMBER}?true:false)]]></printWhenExpression>
				</reportElement>
				<textElement>
					<font size="8"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{numtoword}]]></textFieldExpression>
			</textField>
			<textField pattern="MM/dd/yy" isBlankWhenNull="true">
				<reportElement x="384" y="30" width="100" height="20">
					<printWhenExpression><![CDATA[new Boolean($V{variable2}==$V{PAGE_NUMBER}?true:false)]]></printWhenExpression>
				</reportElement>
				<textElement/>
				<textFieldExpression class="java.util.Date"><![CDATA[$F{REF_DATE}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="71" y="94" width="226" height="67"/>
				<textElement>
					<font fontName="Times New Roman" size="9"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{sundrydet}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement x="24" y="2" width="255" height="62"/>
				<textElement textAlignment="Center">
					<font fontName="Times New Roman" size="9" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{compute_1}]]></textFieldExpression>
			</textField>
		</band>
	</pageFooter>
	<summary>
		<band/>
	</summary>
</jasperReport>
