Untitled

public static function GetRegistrantsByFilter($page, $filter)
  {
    $rList = new RegistrantList();
    if($page !== null)
      {
        $page = $page-1;
        $limitStart = $page*100;
        $limitEnd = 100;
      }
    else
      {
        $limitStart = 0;
        $limitEnd = 100;
      }
    if($filter == 'Submitted Registrations')
    {
      $sql = '
    SELECT
    SELECT r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate,
    CartStatus,r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation,
    p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull, rownum rn
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE r.Processed = "yes" AND p.Active="yes"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC';
    $query = $rList->db->query($sql);
    }
    else if($filter == 'Incomplete Registrations')
    {
       $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE r.Processed = "no" AND p.Active="yes"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
    else if($filter == 'All Registrations')
    {
        $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="yes"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
    else if($filter == 'Deleted Registrations')
    {
       $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="no"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
    else if($filter == 'SMD')
    {
      $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="yes" AND Abbreviation = "SMD"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
     else if($filter == 'Paid')
    {
      $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="yes" AND CartStatus = "Paid"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
     else if($filter == 'InCart')
    {
      $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="yes" AND CartStatus = "InCart"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
     else if($filter == 'InPayment')
    {
      $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, t.DateRecordCreated IS NULL as isnull
    FROM Registrants as r
    LEFT JOIN People  as p on p.PersonID = r.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE  p.Active="yes" AND CartStatus = "InPayment"
    GROUP BY PID ORDER BY isnull ASC,Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
    else
    {
    $sql = 'SELECT SQL_CALC_FOUND_ROWS r.RegistrantsID as RID, p.PersonID as PID, p.Email as Email, p.FirstName as FirstName,
    p.MaidenName as MaidenName, p.LastName as LastName, p.RegFeePaid as RegFeePaid, r.CreatedDT as CreatedDate, CartStatus,
    r.Processed as Processed, t.DateRecordCreated as Transaction, Abbreviation, p.EmailOptOut as EmailOptOut, pgr.RegistrantsID as OID, t.DateRecordCreated IS NULL as isnull
    , pr.LastName as OwnerLast, pr.FirstName as OwnerFirst
    FROM People as p
    LEFT JOIN Registrants as r on p.PersonID = r.PersonID
    LEFT JOIN Registrants_Guests_Rel as pgr on pgr.PersonID = p.PersonID
    LEFT JOIN Registrants as rg on pgr.RegistrantsID = rg.RegistrantsID
    LEFT JOIN People as pr on pr.PersonID = rg.PersonID
    LEFT JOIN People_Affiliations_Rel as par on par.PersonID = p.PersonID
    LEFT JOIN Schools as s on par.SchoolID = s.ID
    LEFT JOIN Transactions as t on r.RegistrantsID = t.RegistrantsID
    LEFT JOIN EventCart as ec on r.RegistrantsID = ec.RegistrantsID
    WHERE (p.FirstName LIKE "%'.$filter.'%" OR p.LastName LIKE "%'.$filter.'%"
       OR p.MaidenName LIKE "%'.$filter.'%") AND p.Active = "yes" AND (r.RegistrantsID IS NOT NULL OR pgr.RegistrantsID IS NOT NULL)
    GROUP BY PID ORDER BY isnull ASC, Transaction ASC LIMIT '.$limitStart.' , '.$limitEnd;
    $query = $rList->db->query($sql);
    }
    $listing = array();
    $query2 = $rList->db->query("SELECT FOUND_ROWS() as found");
    $results = $query2->result();
    $listing['number'] = $results[0]->found;
    $results = $query->result();
    $listing['results'] = array();
    foreach ($results as $row)
    {

        if($row->MaidenName != '')
          $name = $row->FirstName ." ($row->MaidenName)";
        else
          $name = $row->FirstName;
      if(isset($row->OID))
      {
          array_push( $listing['results'], (array(
            'EMail' => $row->Email
            ,'RegistrantID' => $row->RID
            ,'ID'=> $row->OID
            ,'EmailOptOut' => $row->EmailOptOut
            ,'FirstName' => $name
            ,'LastName' => $row->LastName
            ,'OwnerName' => $row->OwnerLast . ", " .$row->OwnerFirst
            ,'Processed' => $row->Processed
            ,'Transaction' => $row->Transaction
            ,'Notes' => $row->Abbreviation
            ,'CartStatus' => $row->CartStatus
          )));
       }
      else
      {
          array_push( $listing['results'], (array(
            'EMail' => $row->Email
            ,'RegistrantID' => $row->RID
            ,'FirstName' => $name
            ,'LastName' => $row->LastName
            ,'Processed' => $row->Processed
            ,'Transaction' => $row->Transaction
            ,'Notes' => $row->Abbreviation
            ,'CartStatus' => $row->CartStatus
          )));
      }
    }
    return $listing;
  }