<?php
final class OfferDao extends Dao {
  private static $ALL = <<<'SQL'
select offer_code,
       shop_name,
       prefecture_id,
       city_id,
       ward_id,
       address,
       tanto_name,
       tel_no,
       lat,
       lng,
       access,
       new_flag,
       celebrate,
       title,
       message,
       photo_1,
       photo_2,
       create_date,
       update_date
       del_flag,
       break_flag
  from offer
 order by offer_code
SQL;
  private static $PK = <<<'SQL'
select offer_code,
       shop_name,
       prefecture_id,
       city_id,
       ward_id,
       address,
       tanto_name,
       tel_no,
       lat,
       lng,
       access,
       new_flag,
       celebrate,
       title,
       message,
       photo_1,
       photo_2,
       create_date,
       update_date,
       del_flag,
       break_flag,
       (select p.prefecture_name
          from prefecture p
         where p.prefecture_id = o.prefecture_id
           and p.display_flag = 1) as prefecture_name,
       (select c.city_name
          from city c
         where c.prefecture_id = o.prefecture_id
           and c.city_id = o.city_id
           and c.display_flag = 1) as city_name,
       (select w.ward_name
          from ward w
         where w.prefecture_id = o.prefecture_id
           and w.city_id = o.city_id
           and w.ward_id = o.ward_id) as ward_name,
       del_flag
  from offer o
 where offer_code = '%s'
SQL;
  private static $OFFER_LIST = <<<'SQL'
select offer_code,
       shop_name,
       prefecture_id,
       city_id,
       ward_id,
       address,
       tanto_name,
       tel_no,
       lat,
       lng,
       access,
       new_flag,
       celebrate,
       title,
       message,
       photo_1,
       photo_2,
       create_date,
       update_date,
       del_flag,
       break_flag,
       (select p.prefecture_name
          from prefecture p
         where p.prefecture_id = o.prefecture_id
           and p.display_flag = 1) as prefecture_name,
       (select c.city_name
          from city c
         where c.prefecture_id = o.prefecture_id
           and c.city_id = o.city_id
           and c.display_flag = 1) as city_name,
       (select w.ward_name
          from ward w
         where w.prefecture_id = o.prefecture_id
           and w.city_id = o.city_id
           and w.ward_id = o.ward_id) as ward_name
  from offer o
 where 1 = 1
SQL;
  private static $DELETE = <<<'SQL'
update offer
   set del_flag = 1
 where offer_code = '%s'
SQL;
  private static $INSERT = <<<'SQL'
insert into offer
            (offer_code,
             shop_name,
             prefecture_id,
             city_id,
             ward_id,
             address,
             tanto_name,
             tel_no,
             lat,
             lng,
             access,
             new_flag,
             celebrate,
             title,
             message,
             photo_1,
             photo_2,
             create_date,
             update_date,
             break_flag)
     values ('%s',
             '%s',
             '%s',
             '%s',
             '%s',
             '%s',
             '%s',
             '%s',
             %s,
             %s,
             '%s',
             %s,
             %s,
             '%s',
             '%s',
             '%s',
             '%s',
             null,
             null,
             %s)
SQL;
  private static $UPDATE = <<<'SQL'
update offer
   set shop_name = '%s',
       address = '%s',
       tanto_name = '%s',
       tel_no = '%s',
       lat = %s,
       lng = %s,
       access = '%s',
       new_flag = %s,
       celebrate = %s,
       title = '%s',
       message = '%s',
       photo_1 = '%s',
       photo_2 = '%s',
       break_flag = %s
 where offer_code = '%s'
SQL;
  private static $RESTORE = <<<'SQL'
update offer
   set del_flag = 0
 where offer_code = '%s'
SQL;
  private static $JOB_LIST = <<<'SQL'
select SQL_CALC_FOUND_ROWS offer_code,
       shop_name,
       title,
       celebrate,
       photo_1,
       message,
       address,
       (select p.prefecture_name
          from prefecture p
         where p.prefecture_id = o.prefecture_id
           and p.display_flag = 1) as prefecture_name,
       (select c.city_name
          from city c
         where c.prefecture_id = o.prefecture_id
           and c.city_id = o.city_id
           and c.display_flag = 1) as city_name,
       (select w.ward_name
          from ward w
         where w.prefecture_id = o.prefecture_id
           and w.city_id = o.city_id
           and w.ward_id = o.ward_id) as ward_name
  from offer o
 where 1 = 1
SQL;
  private static $FOUND_ROWS = <<<'SQL'
select found_rows()
SQL;
  public function getAll() {
    return $this->execute(self::$ALL);
  }
  public function getByPk($pk) {
    return $this->execute(self::$PK, $pk);
  }
  public function getOfferList($offerCode, $shopName, $prefectureId, $cityId, $wardId, $address, $status) {
    $sql = self::$OFFER_LIST;
    $params = array();
    if (!empty($offerCode)) {
      $sql .= " and o.offer_code like '%s%%'";
      $params[] = $offerCode;
    }
    if (!empty($shopName)) {
      $sql .= " and o.shop_name like '%%%s%%'";
      $params[] = $shopName;
    }
    if (!empty($prefectureId)) {
      $sql .= " and o.prefecture_id = '%s'";
      $params[] = $prefectureId;
    }
    if (!empty($cityId)) {
      $sql .= " and o.city_id = '%s'";
      $params[] = $cityId;
    }
    if (!empty($wardId)) {
      $sql .= " and o.ward_id = '%s'";
      $params[] = $wardId;
    }
    if (!empty($address)) {
      $sql .= " and o.address like '%%%s%%'";
      $params[] = $address;
    }
    switch ($status) {
      case 'all':
        break;
      case 'registerd':
        $sql .= " and o.del_flag = 0 and o.break_flag = 0";
        break;
      case 'edited':
        $sql .= " and o.del_flag = 0 and o.break_flag = 1";
        break;
      case 'deleted':
        $sql .= " and o.del_flag = 1";
    }
    $sql .= ' order by o.offer_code';
    return $this->execute($sql, $params);
  }
  public function delete($code) {
    return $this->execute(self::$DELETE, $code);
  }
  public function insert(array $values) {
    return $this->execute(self::$INSERT, $values);
  }
  public function update(array $values) {
    return $this->execute(self::$UPDATE, $values);
  }
  public function restore($offerCode) {
    return $this->execute(self::$RESTORE, $offerCode);
  }
  public function getRecent($start, $count) {
    $sql = self::$JOB_LIST;
    $sql .= ' and o.new_flag = 1 and o.del_flag = 0 and o.break_flag = 0 order by o.update_date desc, o.offer_code asc limit %s, %s';
    return $this->execute($sql, array($start, $count));
  }
  public function getJobList(array $area, array $category, array $time, array $condition, array $compensation, $start, $count) {
    $sql = self::$JOB_LIST;
    $param = array();
    if (!empty($area)) {
      $sql .= ' and (o.prefecture_id, o.city_id, o.ward_id) in ((%s))';
      $tmp = array();
      foreach ($area as $val) {
        list($prefecture, $city, $ward) = explode(',', $val);
        $prefecture = "'{$prefecture}'";
        $city = "'{$city}'";
        $ward = "'{$ward}'";
        $tmp[] = "{$prefecture},{$city},{$ward}";
      }
      $param[] = implode('),(', $tmp);
    }
    if (!empty($category)) {
      $sql .= ' and exists(select 1 from offer_outline oo where oo.offer_code = o.offer_code and (oo.occupation_id, oo.category_id) in ((%s)))';
      $param[] = implode('),(', $category);
    }
    if (!empty($time)) {
      $sql .= " and exists(select 1 from offer_time ot where ot.offer_code = o.offer_code and ot.search_time in (%s))";
      $param[] = implode(",", $time);
    }
    if (!empty($condition)) {
      $sql .= ' and exists(select 1 from offer_condition oc where oc.offer_code = o.offer_code and oc.condition_id in (%s))';
      $param[] = implode(',', $condition);
    }
    if (!empty($compensation)) {
      $sql .= ' and exists(select 1 from offer_compensation oc where oc.offer_code = o.offer_code and oc.compensation_id in (%s))';
      $param[] = implode(',', $compensation);
    }
    $sql .= ' and o.del_flag = 0 and o.break_flag = 0 order by o.update_date desc, o.offer_code asc limit %s, %s';
    $param[] = $start;
    $param[] = $count;
    return $this->execute($sql, $param, FALSE);
  }
  public function foundRows() {
    return mysql_result($this->execute(self::$FOUND_ROWS), 0);
  }
}
// EOF.
