<!DOCTYPE openRPTDef>
<report>
 <title>Payment Register</title>
 <name>CheckRegister</name>
 <description></description>
 <parameter default="" active="false" type="" name="bankaccnt_id" ></parameter>
 <parameter default="" active="false" type="" name="endDate" ></parameter>
 <parameter default="" active="false" type="" name="startDate" ></parameter>
 <size>Letter</size>
 <portrait/>
 <topmargin>50</topmargin>
 <bottommargin>50</bottommargin>
 <rightmargin>50</rightmargin>
 <leftmargin>50</leftmargin>
 <querysource>
  <name>head</name>
  <sql>SELECT bankaccnt_name,
       bankaccnt_descrip,
       formatDate(&lt;? value("startDate") ?>, 'Earliest') AS startdate,
       formatDate(&lt;? value("endDate") ?>, 'Latest') AS enddate,
       curr_symbol
  FROM bankaccnt, curr_symbol
 WHERE ((bankaccnt_id=&lt;? value("bankaccnt_id") ?>)
   AND  (bankaccnt_curr_id = curr_id));</sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT checkhead_id AS checkid,
       CASE WHEN(checkhead_void) THEN -1
            WHEN(checkhead_posted) THEN 1
            ELSE 0
       END AS extra,
       -1 AS checkitem_id,
       formatBoolYN(checkhead_void) AS f_void,
       formatBoolYN(checkhead_misc) AS f_misc,
       formatBoolYN(checkhead_printed) AS f_printed,
       formatBoolYN(checkhead_posted) AS f_posted,
       CASE when checkhead_number = -1 THEN
         'Unspecified'
       ELSE TEXT(checkhead_number) END AS number,
       COALESCE(checkrecip_number || '-' || checkrecip_name,
		checkhead_recip_type || '-' || checkhead_recip_id ) AS description,
       formatDate(checkhead_checkdate) AS f_checkdate,
       formatMoney(checkhead_amount) AS f_amount, 
       currConcat(checkhead_curr_id) AS currAbbr,
       checkhead_number,
       checkhead_ach_batch,
       1 AS orderby
  FROM checkhead LEFT OUTER JOIN
       checkrecip ON ((checkhead_recip_id=checkrecip_id)
		 AND  (checkhead_recip_type=checkrecip_type))
 WHERE ((checkhead_checkdate BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
   AND   (checkhead_bankaccnt_id=&lt;? value("bankaccnt_id") ?>)
   &lt;? if exists("check_number") ?>
      AND   (CAST(checkhead_number AS text) ~ &lt;? value("check_number") ?>)
   &lt;? endif ?>
   &lt;? if exists("recip") ?>
      &lt;? if exists("recip_type_v") ?>
	 AND   (checkhead_recip_type = 'V' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_c") ?>
	 AND   (checkhead_recip_type = 'C' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_t") ?>
	 AND   (checkhead_recip_type = 'T' )
      &lt;? endif ?>
      &lt;? if exists("recip_id") ?>
	 AND   (checkhead_recip_id = &lt;? value("recip_id") ?> )
      &lt;? endif ?>
   &lt;? endif ?> 
   )
&lt;? if exists("showDetail") ?>
UNION
SELECT checkitem_checkhead_id AS checkid, 0 AS extra, checkitem_id,
       '' AS f_void, '' AS f_misc, '' AS f_printed, '' AS f_posted,
       checkitem_vouchernumber AS number,
       checkitem_invcnumber AS description,
       '' AS f_checkdate,
       formatMoney(checkitem_amount) AS f_amount,
       currConcat(checkitem_curr_id) AS currAbbr, 
       checkhead_number, 
       NULL,
       2 AS orderby 
  FROM checkitem, checkhead 
 WHERE ( (checkitem_checkhead_id=checkhead_id)
   AND   (checkhead_checkdate BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
   AND   (checkhead_bankaccnt_id=&lt;? value("bankaccnt_id") ?>)
      &lt;? if exists("check_number") ?>
      AND   (CAST(checkhead_number AS text) ~ &lt;? value("check_number") ?>)
   &lt;? endif ?>
   &lt;? if exists("recip") ?>
      &lt;? if exists("recip_type_v") ?>
         AND   (checkhead_recip_type = 'V' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_c") ?>
         AND   (checkhead_recip_type = 'C' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_t") ?>
         AND   (checkhead_recip_type = 'T' )
      &lt;? endif ?>
      &lt;? if exists("recip_id") ?>
         AND   (checkhead_recip_id = &lt;? value("recip_id") ?> )
      &lt;? endif ?>
   &lt;? endif ?> )
&lt;? endif ?>
 ORDER BY checkhead_number, checkid, orderby;</sql>
 </querysource>
 <querysource>
  <name>total</name>
  <sql>SELECT formatMoney(SUM(currToCurr(checkhead_curr_id, bankaccnt_curr_id, checkhead_amount, checkhead_checkdate))) AS f_amount,
       currConcat(bankaccnt_curr_id) AS currAbbr
  FROM checkhead, checkrecip, bankaccnt
 WHERE ( (checkhead_recip_id=checkrecip_id)
   AND   (checkhead_recip_type=checkrecip_type)
   AND   (NOT checkhead_void)
   AND   (checkhead_checkdate BETWEEN &lt;? value("startDate") ?> AND &lt;? value("endDate") ?>)
   AND   (checkhead_bankaccnt_id=bankaccnt_id)
   AND   (checkhead_bankaccnt_id=&lt;? value("bankaccnt_id") ?>)
   &lt;? if exists("check_number") ?>
      AND   (CAST(checkhead_number AS text) ~ &lt;? value("check_number") ?>)
   &lt;? endif ?>
   &lt;? if exists("recip") ?>
      &lt;? if exists("recip_type_v") ?>
         AND   (checkhead_recip_type = 'V' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_c") ?>
         AND   (checkhead_recip_type = 'C' )
      &lt;? endif ?>
      &lt;? if exists("recip_type_t") ?>
         AND   (checkhead_recip_type = 'T' )
      &lt;? endif ?>
      &lt;? if exists("recip_id") ?>
         AND   (checkhead_recip_id = &lt;? value("recip_id") ?> )
      &lt;? endif ?>
   &lt;? endif ?> )
GROUP BY bankaccnt_curr_id;</sql>
 </querysource>
 <rpthead>
  <height>168</height>
  <field>
   <rect>
    <x>550</x>
    <y>65</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>startdate</column>
   </data>
  </field>
  <label>
   <rect>
    <x>560</x>
    <y>140</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Payment Date</string>
  </label>
  <label>
   <rect>
    <x>60</x>
    <y>140</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Misc.</string>
  </label>
  <field>
   <rect>
    <x>140</x>
    <y>65</y>
    <width>150</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>bankaccnt_name</column>
   </data>
  </field>
  <label>
   <rect>
    <x>441</x>
    <y>85</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>End Date:</string>
  </label>
  <label>
   <rect>
    <x>330</x>
    <y>140</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Recipient</string>
  </label>
  <field>
   <rect>
    <x>550</x>
    <y>85</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>enddate</column>
   </data>
  </field>
  <field>
   <rect>
    <x>140</x>
    <y>85</y>
    <width>300</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>bankaccnt_descrip</column>
   </data>
  </field>
  <label>
   <rect>
    <x>225</x>
    <y>140</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Payment #</string>
  </label>
  <label>
   <rect>
    <x>5</x>
    <y>140</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Void</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>155</ystart>
   <xend>745</xend>
   <yend>155</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>441</x>
    <y>65</y>
    <width>100</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Start Date:</string>
  </label>
  <label>
   <rect>
    <x>655</x>
    <y>140</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Amount</string>
  </label>
  <label>
   <rect>
    <x>111</x>
    <y>140</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Printed</string>
  </label>
  <label>
   <rect>
    <x>25</x>
    <y>65</y>
    <width>110</width>
    <height>20</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Bank Account:</string>
  </label>
  <label>
   <rect>
    <x>170</x>
    <y>140</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Posted</string>
  </label>
  <label>
   <rect>
    <x>270</x>
    <y>5</y>
    <width>475</width>
    <height>40</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Payment Register</string>
  </label>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>12</height>
 </pghead>
 <pghead>
  <height>28</height>
  <label>
   <rect>
    <x>330</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Recipient</string>
  </label>
  <label>
   <rect>
    <x>560</x>
    <y>140</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Payment Date</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>15</ystart>
   <xend>745</xend>
   <yend>15</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>60</x>
    <y>0</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Misc.</string>
  </label>
  <label>
   <rect>
    <x>111</x>
    <y>0</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Printed</string>
  </label>
  <label>
   <rect>
    <x>225</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Payment #</string>
  </label>
  <label>
   <rect>
    <x>170</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <string>Posted</string>
  </label>
  <label>
   <rect>
    <x>655</x>
    <y>0</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Amount</string>
  </label>
  <label>
   <rect>
    <x>5</x>
    <y>0</y>
    <width>50</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Void</string>
  </label>
 </pghead>
 <section>
  <name>detail</name>
  <detail>
   <key>
    <query>detail</query>
   </key>
   <height>31</height>
   <field>
    <rect>
     <x>111</x>
     <y>0</y>
     <width>50</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_printed</column>
    </data>
   </field>
   <field>
    <rect>
     <x>560</x>
     <y>0</y>
     <width>90</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_checkdate</column>
    </data>
   </field>
   <field>
    <rect>
     <x>170</x>
     <y>0</y>
     <width>50</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_posted</column>
    </data>
   </field>
   <field>
    <rect>
     <x>5</x>
     <y>0</y>
     <width>50</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_void</column>
    </data>
   </field>
   <field>
    <rect>
     <x>330</x>
     <y>0</y>
     <width>225</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>description</column>
    </data>
   </field>
   <field>
    <rect>
     <x>225</x>
     <y>15</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>checkhead_ach_batch</column>
    </data>
   </field>
   <field>
    <rect>
     <x>225</x>
     <y>0</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>number</column>
    </data>
   </field>
   <field>
    <rect>
     <x>750</x>
     <y>0</y>
     <width>35</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <top/>
    <data>
     <query>detail</query>
     <column>currAbbr</column>
    </data>
   </field>
   <field>
    <rect>
     <x>60</x>
     <y>0</y>
     <width>50</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_misc</column>
    </data>
   </field>
   <field>
    <rect>
     <x>655</x>
     <y>0</y>
     <width>90</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_amount</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>28</height>
  <label>
   <rect>
    <x>0</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Report Date:</string>
  </label>
  <field>
   <rect>
    <x>101</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>report_date</column>
   </data>
  </field>
  <label>
   <rect>
    <x>600</x>
    <y>0</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Page:</string>
  </label>
  <field>
   <rect>
    <x>705</x>
    <y>0</y>
    <width>40</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>page_number</column>
   </data>
  </field>
 </pgfoot>
 <rptfoot>
  <height>28</height>
  <line>
   <xstart>565</xstart>
   <ystart>0</ystart>
   <xend>745</xend>
   <yend>0</yend>
   <weight>0</weight>
  </line>
  <field>
   <rect>
    <x>750</x>
    <y>0</y>
    <width>35</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>curr_symbol</column>
   </data>
  </field>
  <field>
   <rect>
    <x>655</x>
    <y>0</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <data>
    <query>total</query>
    <column>f_amount</column>
   </data>
  </field>
  <label>
   <rect>
    <x>500</x>
    <y>0</y>
    <width>90</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Total:</string>
  </label>
 </rptfoot>
</report>
