gistfile1

class schoolActions extends autoSchoolActions
{

  public function buildQuery()
  {
    if (!$orgId = $this->getRequestParameter('id'))
    {
      if (!$orgId = $this->getUser()->getGuardUser()->Organization->id)
      {
        // if an org ID was not passed, and this user is not an org admin,
        // we should not have ended up here:
        $this->redirect("@no_schools");
      }
    }
    // get the commission percentage for this organization:
    $org = Doctrine::getTable('Organization')->findOneById($orgId);
    // set the org on the object so we can reference it in the template in
    // order to display the name as the title
    $this->org = $org;
    $percent = $org->commission_percent;
    // Here we want to pull the normal query for the schools, but add an
    // inner join for the orders, so we're only pulling schools for which
    // orders exist matching the organization:
   
    $user = $this->getUser()->getGuardUser();
    $query = parent::buildQuery();
    $alias = $query->getRootAlias();
    $query->InnerJoin("$alias.BookOrders o WITH o.school_id = $alias.id");
    $query->addWhere("o.organization_id = ?", $orgId);
    // We want to tally up the sales and commission for display:
    $query->addSelect("$alias.*, SUM(o.total) as sales, SUM(o.commission_total) AS commission, (SUM(o.total) * $percent) AS payout");
    $query->groupBy('o.school_id');
   
    return $query;
  }

  public function executeNone()
  {
  }
}