<?xml version="1.0" encoding="windows-1251"?>
<!DOCTYPE jasperReport
        PUBLIC "-//JasperReports//DTD Report Design//EN"
        "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport name="Clinic_TotalSumForTypeVisit" pageWidth="600" pageHeight="450">
	<style name="Normal" 
		isDefault="true" 
		fontSize="8" 
		pdfFontName="data/jasper/times.ttf"
		pdfEncoding="Cp1251"
		isPdfEmbedded="false" />
	<style name="pHeader" style="Normal" isBold="true" fontSize="10" />
	
	<parameter name="period1" class="java.lang.String"/>
	<parameter name="period2" class="java.lang.String"/>
	
	<queryString><![CDATA[
		SELECT 1 as s,date as d1,
			getVisitCount(date, 3) as vc1, (select SUM(sl_visits.price) from registry 
				join sl_visits on registry.visit_type = sl_visits.id 
				where date=d1 and registry.visit_type=3) as sum1, 
			getVisitCount(date, 6) as vc2, (select SUM(sl_visits.price) from registry 
				join sl_visits on registry.visit_type = sl_visits.id 
				where date=d1 and registry.visit_type=6) as sum2,
			getVisitCount(date, 16) as vc5, (select SUM(sl_visits.price) from registry 
				join sl_visits on registry.visit_type = sl_visits.id 
				where date=d1 and registry.visit_type=16) as sum5 
		FROM registry 
		
		WHERE
			(registry.visit_type=3 OR registry.visit_type=6 OR registry.visit_type=16) AND
			date >= $P{period1} AND date <= $P{period2}
		GROUP by date
		ORDER by DATE
		]]>
	</queryString>
  
	<field name="s" class="java.lang.Integer" />
	<field name="d1" class="java.util.Date" />
	<field name="vc1" class="java.lang.Integer" />
	<field name="sum1" class="java.lang.Double" />
	<field name="vc2" class="java.lang.Integer" />
	<field name="sum2" class="java.lang.Double" />
	<field name="vc5" class="java.lang.Integer" />
	<field name="sum5" class="java.lang.Double" />
	
	<variable name="sum_vc1" class="java.lang.Integer" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{vc1}]]></variableExpression>
	</variable>
	<variable name="sum_vc2" class="java.lang.Integer" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{vc2}]]></variableExpression>
	</variable>
	<variable name="sum_vc5" class="java.lang.Integer" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{vc5}]]></variableExpression>
	</variable>
	<variable name="summ1" class="java.lang.Double" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{sum1}]]></variableExpression>
	</variable>
	<variable name="summ2" class="java.lang.Double" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{sum2}]]></variableExpression>
	</variable>
	<variable name="summ5" class="java.lang.Double" resetType="Column" calculation="Sum">
		<variableExpression><![CDATA[$F{sum5}]]></variableExpression>
	</variable>
	
	<group name="TotalSums" isReprintHeaderOnEachPage="true">
		<groupExpression><![CDATA[$F{s}]]></groupExpression>
		<groupFooter>
		<band height="11">
			<rectangle>
				<reportElement x="0" y="0" width="560" height="11" backcolor="#333333"/>
				<graphicElement/>
			</rectangle>
			<staticText>
				<reportElement mode="Opaque" x="5" y="0" width="140" height="11" forecolor="#ffffff" backcolor="#333333" />
				<text><![CDATA[Total:]]></text>
			</staticText>
			<textField>
				<reportElement mode="Opaque" x="70" y="0" width="50" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{sum_vc1}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="180" y="0" width="50" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{sum_vc2}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="290" y="0" width="50" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{sum_vc5}]]></textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement mode="Opaque" x="95" y="0" width="40" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{summ1}]]></textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement mode="Opaque" x="205" y="0" width="40" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{summ2}]]></textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement mode="Opaque" x="310" y="0" width="40" height="11" forecolor="#ffffff" backcolor="#333333" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double"><![CDATA[$V{summ5}]]></textFieldExpression>
			</textField>
		</band>
		</groupFooter>
	</group>
	
	<title>
		<band height="12">
			<textField>
				<reportElement x="250" y="-10" width="300" height="12"/>
					<textElement isStyledText="true"/>
						<textFieldExpression><![CDATA["<style fontSize=\"16\" isBold=\"true\">Cliniqum Activity</style>"]]>
						</textFieldExpression>
			</textField>
		</band>
	</title>
	
	<pageHeader>
		<band height="21">
			<staticText>
				<reportElement x="10" y="1" width="515" height="15" style="pHeader"/>
				<text><![CDATA[Date         Fist visits    Sum      Second visits   Sum      Procedures   Sum]]></text>
			</staticText>
			<line>
				<reportElement x="-15" y="20" width="585" height="1"/>
				<graphicElement/>
			</line>
		</band>
	</pageHeader>
	<detail>
		<band height="16">
			<textField>
				<reportElement x="0" y="0" width="50" height="16" />
				<textFieldExpression class="java.util.Date">
					<![CDATA[$F{d1}]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement x="70" y="0" width="50" height="16" />
				<textFieldExpression class="java.lang.Integer">
					<![CDATA[$F{vc1}]]>
				</textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement x="95" y="0" width="40" height="16" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double">
					<![CDATA[($F{sum1} != null)?($F{sum1}):(new Double(0))]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement x="180" y="0" width="50" height="16" />
				<textFieldExpression class="java.lang.Integer">
					<![CDATA[$F{vc2}]]>
				</textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement x="205" y="0" width="40" height="16" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double">
					<![CDATA[($F{sum2} != null)?($F{sum2}):(new Double(0))]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement x="290" y="0" width="50" height="16" />
				<textFieldExpression class="java.lang.Integer">
					<![CDATA[$F{vc5}]]>
				</textFieldExpression>
			</textField>
			<textField pattern="0.00">
				<reportElement x="310" y="0" width="40" height="16" />
				<textElement textAlignment="Right" />
				<textFieldExpression class="java.lang.Double">
					<![CDATA[($F{sum5} != null)?($F{sum5}):(new Double(0))]]>
				</textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>