<?php
final class ContentDao extends Dao {
  private static $ALL = <<<'SQL'
select occupation_id,
       category_id,
       content_id,
       content_name
  from content
 order by content_id
SQL;
  private static $PK = <<<'SQL'
select occupation_id,
       category_id,
       content_id,
       content_name
  from content
 where occupation_id = %s
   and category_id = %s
   and content_id = %s
SQL;
  private static $UPDATE = <<<'SQL'
update content
   set content_name = '%s'
 where occupation_id = %s
   and category_id = %s
   and content_id = %s
SQL;
  private static $OCCUPATION_CATEGORY_ID = <<<'SQL'
select occupation_id,
       category_id,
       content_id,
       content_name
  from content
 where occupation_id = %s
   and category_id = %s
 order by content_id
SQL;
  private static $INSERT = <<<'SQL'
insert into content
            (occupation_id,
             category_id,
             content_id,
             content_name)
     values (%s,
             %s,
             %s,
             '%s')
SQL;
  private static $MAX = <<<'SQL'
select ifnull(max(content_id) + 1, 1)
  from content
 where occupation_id = %s
   and category_id = %s
SQL;
  private static $DELETE = <<<'SQL'
delete from content
 where occupation_id = %s
   and category_id = %s
   and content_id = %s
SQL;
  private static $DELETE_OFFER = <<<'SQL'
delete from offer_outline
 where occupation_id = %s
   and category_id = %s
   and content_id = %s
SQL;
  private static $NAME_LIST = <<<'SQL'
select (select occupation_name
          from occupation oc
         where oc.occupation_id = co.occupation_id) as occupation_name,
       (select category_name
          from category ca
         where ca.occupation_id = co.occupation_id
           and ca.category_id = co.category_id) as category_name,
       content_name
  from content co
 where (occupation_id, category_id, content_id) in ((%s))
SQL;
  public function getAll() {
    return $this->execute(self::$ALL);
  }
  public function getByPk($pk) {
    return $this->execute(self::$PK, $pk);
  }
  public function update($name, $occupationId, $categoryId, $contentId) {
    $this->execute(self::$UPDATE, array($name, $occupationId, $categoryId, $contentId));
  }
  public function getByOccupationCategoryId($key) {
    return $this->execute(self::$OCCUPATION_CATEGORY_ID, $key);
  }
  public function insert($occupationId, $categoryId, $contentName) {
    $max = mysql_result($this->execute(self::$MAX, array($occupationId, $categoryId)), 0);
    $this->execute(self::$INSERT, array($occupationId, $categoryId, $max, $contentName));
    return $max;
  }
  public function delete(array $param) {
    $this->execute(self::$DELETE, $param);
    $this->execute(self::$DELETE_OFFER, $param);
  }
  public function getNameList($values) {
    return $this->execute(self::$NAME_LIST, $values);
  }
}
// EOF.
