<?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="Product List report" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20">
	<property name="ireport.scriptlethandling" value="0"/>
	<property name="ireport.encoding" value="UTF-8"/>
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<import value="net.sf.jasperreports.engine.*"/>
	<import value="java.util.*"/>
	<import value="net.sf.jasperreports.engine.data.*"/>
	<parameter name="productName" class="java.lang.String">
		<defaultValueExpression><![CDATA["%"]]></defaultValueExpression>
	</parameter>
	<parameter name="includeServices" class="java.lang.Boolean">
		<defaultValueExpression><![CDATA[new Boolean("true")]]></defaultValueExpression>
	</parameter>
	<parameter name="includeMedications" class="java.lang.Boolean">
		<defaultValueExpression><![CDATA[new Boolean("true")]]></defaultValueExpression>
	</parameter>
	<parameter name="includeMerchandise" class="java.lang.Boolean">
		<defaultValueExpression><![CDATA[new Boolean("true")]]></defaultValueExpression>
	</parameter>
	<parameter name="productType" class="java.lang.String">
		<defaultValueExpression><![CDATA["%"]]></defaultValueExpression>
	</parameter>
	<parameter name="lowMarkup" class="java.lang.Float"/>
	<parameter name="highMarkup" class="java.lang.Float"/>
	<parameter name="IsEmail" class="java.lang.Boolean" isForPrompting="false">
		<parameterDescription><![CDATA[If true, indicates the report is being emailed, to enable different formatting]]></parameterDescription>
		<defaultValueExpression><![CDATA[Boolean.FALSE]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select e.entity_id as pid, e.name as product, e.arch_short_name as archetype,
p1.arch_short_name as priceType, p1.name as priceName, p1.price as price,
round(p1.price,2) as Price, p1.start_time as startDate, p1.end_time as endDate,
round(d1.value,2) as markup, d2.value as qtyBreak, round(d3.value,2) as cost, l.name as uom
from
entities e inner join products p on e.entity_id = p.product_id
left join entity_relationships r on e.entity_id = r.target_id and r.arch_short_name = "entityRelationship.productTypeProduct"
left join entities pt on r.source_id = pt.entity_id
left join product_prices p1 on p1.product_id = p.product_id
left join product_price_details d1 on d1.product_price_id = p1.product_price_id and d1.name = "markup"
left join product_price_details d2 on d2.product_price_id = p1.product_price_id and d2.name = "quantity"
left join product_price_details d3 on d3.product_price_id = p1.product_price_id and d3.name = "cost"
left join product_price_details d4 on d4.product_price_id = p1.product_price_id and d4.name = "qtyUom"
left join lookups l on l.code = d4.value and l.arch_short_name = "lookup.uom"
where e.name like $P{productName} and
e.active = true and
pt.name like $P{productType} and
(
  (curdate() between p1.start_time and p1.end_time) or
  ((p1.end_time is null) and (curdate() >= p1.start_time))
)and
(
	(e.arch_short_name = "product.service" and $P{includeServices}) or
	(e.arch_short_name = "product.medication" and $P{includeMedications}) or
	(e.arch_short_name = "product.merchandise" and $P{includeMerchandise})
) and
(
	(d1.value >= $P{lowMarkup}) and (d1.value <= $P{highMarkup})
)
order by e.name]]>
	</queryString>
	<field name="pid" class="java.lang.Long"/>
	<field name="product" class="java.lang.String"/>
	<field name="archetype" class="java.lang.String"/>
	<field name="priceType" class="java.lang.String"/>
	<field name="priceName" class="java.lang.String"/>
	<field name="price" class="java.math.BigDecimal"/>
	<field name="startDate" class="java.sql.Timestamp"/>
	<field name="endDate" class="java.sql.Timestamp"/>
	<field name="markup" class="java.lang.Float"/>
	<field name="qtyBreak" class="java.lang.Integer"/>
	<field name="cost" class="java.math.BigDecimal"/>
	<field name="uom" class="java.lang.String"/>
	<group name="product">
		<groupExpression><![CDATA[$F{pid}]]></groupExpression>
		<groupHeader>
			<band height="17" splitType="Stretch">
				<textField isBlankWhenNull="false">
					<reportElement key="textField" x="9" y="0" width="200" height="17"/>
					<box>
						<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
						<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
						<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
						<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					</box>
					<textElement/>
					<textFieldExpression class="java.lang.String"><![CDATA[$F{product}]]></textFieldExpression>
				</textField>
			</band>
		</groupHeader>
		<groupFooter>
			<band splitType="Stretch"/>
		</groupFooter>
	</group>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band splitType="Stretch"/>
	</title>
	<pageHeader>
		<band height="73" splitType="Stretch">
			<staticText>
				<reportElement key="staticText-1" x="120" y="0" width="290" height="24"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					<font size="18" isBold="true" isUnderline="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Product List ]]></text>
			</staticText>
			<textField isBlankWhenNull="false">
				<reportElement key="textField-1" x="358" y="27" width="100" height="18"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression>
			</textField>
			<textField evaluationTime="Report" isBlankWhenNull="false">
				<reportElement key="textField-2" x="462" y="27" width="73" height="18"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER} + ""]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement key="staticText-35" x="7" y="30" width="83" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Product Name :]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-36" x="8" y="49" width="83" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Product Type :]]></text>
			</staticText>
			<textField isBlankWhenNull="false">
				<reportElement key="textField" x="90" y="30" width="100" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{productName}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="false">
				<reportElement key="textField-9" x="91" y="48" width="100" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$P{productType}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement key="staticText-37" x="200" y="30" width="70" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Low Markup :]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-38" x="201" y="48" width="70" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[High Markup :]]></text>
			</staticText>
			<textField isBlankWhenNull="false">
				<reportElement key="textField-10" x="273" y="28" width="57" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.Float"><![CDATA[$P{lowMarkup}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="false">
				<reportElement key="textField-11" x="274" y="47" width="57" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.Float"><![CDATA[$P{highMarkup}]]></textFieldExpression>
			</textField>
		</band>
	</pageHeader>
	<columnHeader>
		<band height="25" splitType="Stretch">
			<staticText>
				<reportElement key="staticText-11" x="8" y="3" width="52" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Product]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-13" x="259" y="3" width="56" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Cost]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-14" x="360" y="4" width="56" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Price]]></text>
			</staticText>
			<line>
				<reportElement key="line-1" x="5" y="23" width="514" height="1"/>
			</line>
			<staticText>
				<reportElement key="staticText-27" x="194" y="3" width="59" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Units]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-31" x="108" y="3" width="84" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Left">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Name]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-32" x="319" y="3" width="40" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Markup]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-33" x="67" y="3" width="40" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Type]]></text>
			</staticText>
			<staticText>
				<reportElement key="staticText-34" x="422" y="4" width="56" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Center">
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Qty Break]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="15" splitType="Stretch">
			<textField isBlankWhenNull="true">
				<reportElement key="textField" x="197" y="0" width="60" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{uom}]]></textFieldExpression>
			</textField>
			<textField pattern="##0.00" isBlankWhenNull="true">
				<reportElement key="textField" x="263" y="0" width="50" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{cost}]]></textFieldExpression>
			</textField>
			<textField pattern="##0.00" isBlankWhenNull="true">
				<reportElement key="textField" x="320" y="0" width="40" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.lang.Float"><![CDATA[$F{markup}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement key="textField-5" x="66" y="0" width="44" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[($F{priceType}.equals("productPrice.fixedPrice")) ? "Fixed" : "Unit"]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement key="textField-6" x="113" y="0" width="77" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{priceName}]]></textFieldExpression>
			</textField>
			<textField pattern="##0.00" isBlankWhenNull="true">
				<reportElement key="textField-7" x="363" y="0" width="57" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{price}]]></textFieldExpression>
			</textField>
			<textField pattern="###0" isBlankWhenNull="true">
				<reportElement key="textField-8" x="426" y="0" width="54" height="15"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{qtyBreak}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="17" splitType="Stretch">
			<line>
				<reportElement key="line-2" x="5" y="9" width="514" height="1"/>
			</line>
		</band>
	</columnFooter>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<lastPageFooter>
		<band height="23" splitType="Stretch">
			<staticText>
				<reportElement key="staticText-39" x="9" y="5" width="83" height="17"/>
				<box>
					<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
					<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
				</box>
				<textElement>
					<font isBold="true" pdfFontName="Helvetica-Bold"/>
				</textElement>
				<text><![CDATA[Products :]]></text>
			</staticText>
		</band>
	</lastPageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>