OpenTestOfferingsOnOrAfter.hbm.xml

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="ACC.Testing.Core" namespace="ACC.Testing">
  <typedef name="Test" class="ACC.Testing.Data.TestType, ACC.Testing.Data" />
  <typedef class="NHibernate.Type.EnumStringType`1[[ACC.Testing.RegistrationStates, ACC.Testing.Core]], NHibernate"
             name="registrationStates" />
  <sql-query name="OpenTestOfferingsOnOrAfter">
    <return alias="t" class="TestOffering" />
    <query-param name="@Start" type="DateTime" />
    <query-param name="@Test" type="Test"/>
    <query-param name="@paidState" type="registrationStates"/>
    <![CDATA[ 
    SELECT
      t.Id AS [t.Id],
      t.Test AS [t.Test],
      t.Start AS [t.Start],
      t.Capacity AS [t.Capacity]
    FROM TestOffering t
    WHERE t.Start >= @Start
      AND t.Test = @Test
      AND t.Capacity > (
          SELECT COUNT(*)
          FROM Registration r
          WHERE r.TestOfferingId = t.Id
          AND r.State = @paidState
        ) + (
          SELECT COUNT(*)
          FROM PhantomRegistration pr
          WHERE pr.TestOfferingId = t.Id
        )
    ]]>
  </sql-query>
  <!--<query name="OpenTestOfferingsOnOrAfter">
    <![CDATA[ 
      from TestOffering t
      where t.Start >= :Start
      and t.Test = :Test
      and t.Capacity > ((
        select count(*)
        from Registration r 
        where r.Offering = t
        and r.State = :paidState
      ) + (
        select count(*)
        from PhantomRegistration pr
        where pr.Offering = t
      ))
      order by t.Start
    ]]>
  </query>
  -->
  <sql-query name="CountOpenTestOfferingsOnOrAfter">
    <return-scalar column="cnt" type="Int64"/>
    <query-param name="@Start" type="DateTime" />
    <query-param name="@Test" type="Test"/>
    <query-param name="@paidState" type="registrationStates"/>
    <![CDATA[ 
    SELECT COUNT(*) AS [cnt]
    FROM TestOffering t
    WHERE t.Start >= @Start
    AND t.Test = @Test
    AND t.Capacity > (
      SELECT COUNT(*)
      FROM Registration r
      WHERE r.TestOfferingId = t.Id
      AND r.State = @paidState
    ) + (
      SELECT COUNT(*)
      FROM PhantomRegistration pr
      WHERE pr.TestOfferingId = t.Id
    )
    ]]>
  </sql-query>
  <!--
  <query name="CountOpenTestOfferingsOnOrAfter">
    <![CDATA[ 
      select count(*) from TestOffering t
      where t.Start >= :Start
      and t.Test = :Test
      and t.Capacity > ((
        select count(*)
        from Registration r 
        where r.Offering = t
        and r.State = :paidState
      ) + 
      (
        select count(*)
        from PhantomRegistration pr
        where pr.Offering = t
      ))
    ]]>
  </query>-->
</hibernate-mapping>