<?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="OrderBookApplicationSubReport" pageWidth="2960" pageHeight="555" orientation="Landscape" columnWidth="2960" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="32326d1e-706d-49dc-b343-d4f36a248f17">
	<property name="ireport.zoom" value="0.7513148009015777"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="Serial_Number" class="java.lang.String"/>
	<parameter name="LangCD" class="java.lang.String"/>
	<parameter name="OrderType" class="java.lang.String"/>
	<queryString>
		<![CDATA[SELECT distinct  APPLICATION_CD,  a.LANG_CD, a.STATE_CD, a.DISTRICT_CD,
a.PS_CD, TYPE, RECEIVED_FROM, RECEIPT_DATE,
APPLICATION_LETTER_NUM, APPLICANT_NAME,
a.RELATION_TYPE_CD, RELATIVE_NAME,
concat(
ifnull(if(a.HOUSE_NUMBER=" ","",CONCAT(a.HOUSE_NUMBER,", ")),""),
ifnull(if(STREET_NAME=" ","",CONCAT(STREET_NAME,", ")),""),
ifnull(if(COLONY_LOCALITY_AREA=" ","",CONCAT(COLONY_LOCALITY_AREA,", ")),""),
ifnull(if(VILLAGE_TOWN_CITY=" ","",CONCAT(VILLAGE_TOWN_CITY,", ")),""),
ifnull(if(TEHSIL=" ","",CONCAT(TEHSIL,", ")),""),
ifnull(if(g.PS=" ","",CONCAT(g.PS,", ")),""),
ifnull(if(e.DISTRICT=" ","",CONCAT(e.DISTRICT,", ")),""),
ifnull(if(f.STATE=" ","",CONCAT(f.STATE,"")),""),
ifnull(if(PIN_CODE=0 or PIN_CODE is null," ",CONCAT(", ",PIN_CODE )),""),
ifnull(if(d.NATIONALITY=" ","",CONCAT(", ",d.NATIONALITY)),"")

) AS Address,


 SUBJECT,
PERSON_FULL_NAME, LONG_DESC, RETURNING_DESC, RETURNING_DATE,  c.RELATION_TYPE,  EXPECTED_RETURNING_DATE
FROM fr_tb_order_book_application_t a
INNER join fr_tb_complaint_application_source_m j on a.RECEIVED_FROM = j.SOURCE_CD and a.LANG_CD = j.LANG_CD
Left join t_user_info AS tui ON tui.USER_SRNO = a.IO_NAME_CD   Left join m_ps_staff_info AS mpsi ON tui.LOGIN_ID = mpsi.GPF_NO
Left join m_relation_type c on a.RELATION_TYPE_CD = c.RELATION_TYPE_CD and a.LANG_CD = c.LANG_CD ,   m_nationality d, m_district e, m_state f, m_police_station g
where a.ADDRESS_COUNTRY_CD = d.NATIONALITY_CD and a.LANG_CD = d.LANG_CD and a.ADDRESS_DISTRICT_CD = e.DISTRICT_CD and a.LANG_CD = e.LANG_CD and a.ADDRESS_STATE_CD = f.STATE_CD
and a.LANG_CD = f.LANG_CD and a.ADDRESS_PS_CD = g.PS_CD
and a.IS_DELETED = '0' and a.RECORD_STATUS = 'C'
and a.Lang_CD = $P{LangCD}
and (a.RECEIVED_FROM = $P{OrderType} )
group by a.RECEIVED_FROM]]>
	</queryString>
	<field name="APPLICATION_CD" class="java.lang.Long">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="LANG_CD" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="STATE_CD" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="DISTRICT_CD" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PS_CD" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="TYPE" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RECEIVED_FROM" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RECEIPT_DATE" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="APPLICATION_LETTER_NUM" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="APPLICANT_NAME" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RELATION_TYPE_CD" class="java.lang.Integer">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RELATIVE_NAME" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="Address" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="SUBJECT" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="PERSON_FULL_NAME" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="LONG_DESC" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RETURNING_DESC" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RETURNING_DATE" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="RELATION_TYPE" class="java.lang.String">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<field name="EXPECTED_RETURNING_DATE" class="java.sql.Timestamp">
		<fieldDescription><![CDATA[]]></fieldDescription>
	</field>
	<detail>
		<band height="100" splitType="Stretch">
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement uuid="bdbc53e4-0ffa-485d-9dd1-23c787e6ed0d" stretchType="RelativeToTallestObject" x="0" y="0" width="100" height="100" isPrintWhenDetailOverflows="true"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="true"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$P{Serial_Number}+"."+$V{REPORT_COUNT}]]></textFieldExpression>
			</textField>
			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
				<reportElement uuid="ebf0d31a-65a9-4f4b-b05a-94d98bf2f06b" stretchType="RelativeToTallestObject" x="100" y="0" width="265" height="100" isPrintWhenDetailOverflows="true"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="true"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="84e6b2fb-578b-4339-b73a-130d7f9d7858" positionType="FixRelativeToBottom" mode="Opaque" x="365" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{APPLICATION_LETTER_NUM}]]></textFieldExpression>
			</textField>
			<textField pattern="dd/MM/yyyy">
				<reportElement uuid="ef92e4e6-7b3b-4ce6-bcf0-c45456c110d3" positionType="FixRelativeToBottom" mode="Opaque" x="735" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement textAlignment="Center">
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{RECEIPT_DATE}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="47a0a13c-15c1-43b5-a3e2-5a3b3b6b0aef" positionType="FixRelativeToBottom" mode="Opaque" x="1105" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{APPLICANT_NAME}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="ec2fe358-d63a-46ea-bd74-693cda75bdb0" positionType="FixRelativeToBottom" mode="Opaque" x="1475" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{Address}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="4c182094-73a0-4809-ab11-c51f38e60c07" positionType="FixRelativeToBottom" mode="Opaque" x="1845" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{SUBJECT}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="732ec859-b43f-41ea-a023-38c319b9ba9b" positionType="FixRelativeToBottom" mode="Opaque" x="2215" y="0" width="370" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{PERSON_FULL_NAME}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement uuid="4a7a505f-670e-4619-819a-c7258ce9052b" positionType="FixRelativeToBottom" mode="Opaque" x="2585" y="0" width="375" height="100" backcolor="#FFFFFF"/>
				<box>
					<pen lineWidth="1.0"/>
					<topPen lineWidth="1.0"/>
					<leftPen lineWidth="1.0"/>
					<bottomPen lineWidth="1.0"/>
					<rightPen lineWidth="1.0"/>
				</box>
				<textElement>
					<font size="30" isBold="false" pdfFontName="Akshar.ttf" pdfEncoding="Identity-H" isPdfEmbedded="false"/>
					<paragraph leftIndent="10" rightIndent="10"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{RETURNING_DESC}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>
