<?
//####################################################################
//
//    [ファイル名]
//    en/baseball/news/index.php
//
//    [内容]
//    ニュース一覧
//
//    [特記事項]
//    キーワード検索($mode==keyword)は全年度から検索
//
//####################################################################
mb_internal_encoding("Shift_JIS");

require_once( "define.pplb" );
require_once( "en_baseball.def" );
require_once( "str_enc.pplb" );
require_once( "str_cnv.pplb" );
require_once( "common_tmpl.pplb" );

require_once( "HTML/templateUty_sjis.pplb" );
require_once( "db/db.pplb" );
require_once( "Image/Image.pplb" );
require_once( "db/DButil.pplb" );

$site_dir = $_SERVER['DOCUMENT_ROOT'] ."/" .SSKBB_DOMAIN;

  //------------------------------------------------------------------
  // 画面設定
  //------------------------------------------------------------------
  $tmpl_dir = $site_dir ."/whats_new/tmpl/";
  $tmpl_file = "index.tmpl";

  //------------------------------------------------------------------
  // 初期値
  //------------------------------------------------------------------
  if( $pidx=="" ) $pidx ="0";
  $limit  ="100";
  $offset = $limit * $pidx;
  define("START_YEAR", 2007); // ニュース開始年

  //------------------------------------------------------------------
  // DB接続
  //------------------------------------------------------------------
  $conn = logonDB();


  //------------------------------------------------------------------
  // パラメータチェック(年)
  //------------------------------------------------------------------
  $year = $_REQUEST["year"];

  // デフォルトは今年
  if(($year == "")&&($mode == "")) $year = sprintf(date('Y'), "%04d");

  // 数値4桁かチェック
  if((preg_match('/[^0-9]/', $year)) || (strlen($year) != 4)) $year = "";
  if(($year<START_YEAR) || ($year>sprintf(date('Y'), "%04d")))  $year = "";

  // パラメータエラー
  if(($year == "")&&($mode == "")) $param_err = true;
  $buf['YEAR'] = $year;

  //------------------------------------------------------------------
  // 検索キーワード
  //------------------------------------------------------------------
  if( $search_str != "" ) {
    $tmp = preg_replace( "/　/", " ", $search_str );
    $tmp = trim($tmp);
    $words = explode( " ", $tmp );
  }
  if( ! is_array($words) ) $words = array();
  $buf['MODE'] = $mode;


  //------------------------------------------------------------------
  // 左ナビ年
  //------------------------------------------------------------------
  $i = 0;
  $year_now = sprintf(date('Y'), "%04d");
  for($tmp_year=$year_now; $tmp_year>=START_YEAR; $tmp_year--){
    $buf['YEAR_LIST'][$i]['YEAR'] = $tmp_year;
    if($tmp_year == $buf['YEAR']) $buf['YEAR_LIST'][$i]['MATCH'] = true;
    $i++;
  }

  $_REQUEST['YEAR_LIST']=$buf['YEAR_LIST'];

  //------------------------------------------------------------------
  // 画像一覧取得
  //------------------------------------------------------------------

  if(!$param_err){
    $sql  =  " SELECT AA.IMGURL, TO_CHAR(AA.CREATE_DATE,'YYYY/MM/DD') AS CREATE_DATE, AA.ID, AA.FLAG ";
    $sql .= " FROM ";
    $sql .= " (";
    $sql .= " SELECT B.ID, '1' AS FLAG, B.IMGURL  AS IMGURL, B.CREATE_DATE FROM WLGUSER.SHINBDTBL A, WLGUSER.MKNEWSTBL B ";
    $sql .= " WHERE A.ID IN (" .NEWS_BRAND  .")";
    $sql .= " AND A.ID=B.BRAND AND B.DEL_FLG=0 AND B.IMGURL IS NOT NULL ";
//    $sql .= " AND B.NEWS_TYPE=0 ";
    reset($words);
    if( count($words) > 0 ) {
      $tmp = "";
      while( list($k,$v)=each($words) ) {
        $v = preg_replace( "/'/", "''", strtoupper($v) );
        if( $tmp != "" ) $tmp .= " AND ";
        $tmp .= "(UPPER(B.TITLE) LIKE '%{$v}%' OR UPPER(B.TEXT) LIKE '%{$v}%' )";
      }
      $sql .= " AND ( {$tmp} )";
    }

    $sql .= " )AA";
    if($year != "" && $search_str=="" ){
      $sql .= " WHERE TO_CHAR(AA.CREATE_DATE,'YYYY')=".$year;
    }
    $sql .= " ORDER BY";
    $sql .= " AA.ID DESC, AA.FLAG";

    $sql = mb_convert_encoding( $sql, "EUC", "UTF-8" );

    $sql = DButil::queryOffsetSql($sql,3,0);
    $rec = DButil::query($conn,$sql);
    $img_cnt = count($rec);

    for($i=0; $i<count($rec); $i++){

      //ウェブリーグサーバからファイル取得
      $cmd= sprintf("wget -xO %s http://%s/%s", $_SERVER['DOCUMENT_ROOT'] ."/" .SSKBB_DOMAIN ."/whats_new/".$rec[$i]['IMGURL'], WEBLEAGUE_SERVER,$rec[$i]['IMGURL']);
      if(DEF_HOST_NAME == "www.ssksports.com") $cmd = "/usr/local/bin/".$cmd;
      @exec($cmd);

      // リサイズ
      $srcURL   = "/" .SSKBB_DOMAIN ."/whats_new/".$rec[$i]['IMGURL'];
      $width    = 180;
      Image::getWidthAjustedImageUrl($srcURL, $width, $height, $tag, 1);
      $img_tag ="<img $tag border=\"1\" style=\"border-color:#cccccc\">";
      $buf['IMG_LIST'][$i]['IMG_TAG'] = $img_tag;
      $buf['IMG_LIST'][$i]['IMG_URL'] = "./news" .$rec[$i]['ID'] .".html";
    }
  }


  //------------------------------------------------------------------
  // ニュース一覧取得
  //------------------------------------------------------------------
  $all_cnt = 0;
  if(!$param_err){
    $sql_c  = " SELECT COUNT(*) AS CNT ";
    $sql_s  = " SELECT B.ID, A.NAME, B.TITLE, B.TEXT, TO_CHAR(B.CREATE_DATE,'YYYY/MM/DD') AS CREATE_DATE, TO_CHAR(B.CREATE_DATE,'YYYY') AS CREATE_YEAR ";
    $sql_f  = " FROM WLGUSER.SHINBDTBL A, WLGUSER.MKNEWSTBL B";
    $sql_w .= " WHERE A.ID IN (" .NEWS_BRAND  .")";
    $sql_w .= "   AND A.ID=B.BRAND";
    $sql_w .= "   AND B.DEL_FLG=0";
//    $sql_w .= "   AND B.NEWS_TYPE=0";
    if($year != "" && $search_str=="" ){
      $sql_w .= " AND TO_CHAR(B.CREATE_DATE,'YYYY')=".$year;
    }

    reset($words);
    if( count($words) > 0 ) {
      $tmp = "";
      while( list($k,$v)=each($words) ) {
        $v = preg_replace( "/'/", "''", strtoupper($v) );
        if( $tmp != "" ) $tmp .= " AND ";
        $tmp .= "(UPPER(B.TITLE) LIKE '%{$v}%' OR UPPER(B.TEXT) LIKE '%{$v}%' )";
      }
      $sql_w .= " AND ( {$tmp} )";
    }

    $sql_o  = " ORDER BY B.CREATE_DATE DESC";

    // 件数を取得
    $sql = $sql_c . $sql_f . $sql_w . $sql_o;
    $sql = mb_convert_encoding( $sql, "EUC", "UTF-8" );
    $rec = DButil::query($conn,$sql);
    $all_cnt = $rec[0]['CNT'];

    // 一覧を取得
    $sql = $sql_s . $sql_f . $sql_w . $sql_o;

    $sql = mb_convert_encoding( $sql, "EUC", "UTF-8" );

    $sql = DButil::queryOffsetSql($sql,$limit,$offset);
    $rec = DButil::query($conn,$sql);

    // 表示用に整形
    for($i=0; $i<count($rec); $i++){
      if( $i==0 ){
        $buf['NEWS_LIST'][$i]['1st'] = true;
      }

      // ID
      $buf['NEWS_LIST'][$i]['ID'] = $rec[$i]['ID'];
      // キーワード検索年表示用
      $new_year = $rec[$i]['CREATE_YEAR'];
      if($old_year != $new_year){
        $buf['NEWS_LIST'][$i]['CREATE_YEAR'] = $new_year; // 年が変わったら年を表示
        if($i>0) $buf['NEWS_LIST'][$i]['SPACE'] = true;   // 段差をあけるスペース
      }
      $old_year = $new_year;
      // 日付
      $tmp = explode("/", $rec[$i]['CREATE_DATE']);
//      $buf['NEWS_LIST'][$i]['DATE' ] = $tmp[1]. "月" . $tmp[2] . "日";
      $buf['NEWS_LIST'][$i]['DATE_Y'] = $tmp[0];
      $buf['NEWS_LIST'][$i]['DATE_M'] = $tmp[1];
      $buf['NEWS_LIST'][$i]['DATE_D'] = $tmp[2];
      //RFC2822形式
      $y = $tmp[0];
      $m = $tmp[1];
      $d = $tmp[2];
      $buf['NEWS_LIST'][$i]['DATE_2822']     = date("r", mktime(0, 0, 0, $m, $d, $y));
      $buf['NEWS_LIST'][$i]['DATE_2822']     = substr( $buf['NEWS_LIST'][$i]['DATE_2822'], 0, 16 );


      // タイトル
      $buf['NEWS_LIST'][$i]['TITLE'] = db_enc( $rec[$i]['TITLE'] );
      $buf['NEWS_LIST'][$i]['TEXT'] = cut_str( remove_CL(db_enc($rec[$i]['TEXT'])), 64 );
    }
  }
  if($all_cnt==0) $buf['NO_DATA'] = true; // 1件もない場合

  $buf['NEWS_MENU'] = getNewsMenu( $conn, "", START_YEAR, "TOP" );

  //------------------------------------------------------------------
  // DB切断
  //------------------------------------------------------------------
  logoffDB( $conn );

  //------------------------------------------------------------------
  // ページ処理
  //------------------------------------------------------------------
  $buf["START_IDX"]  = $offset +1;
  $buf["END_IDX"]    = $offset + $limit;
  $buf["LIMIT"]      = $limit;
  $buf["ALL_IDX"]    = $all_cnt;
  $buf["SEARCH_STR"] = $search_str;


  $buf['YEAR_LIST'] = $year_sel_str;

  $buf['HEADER'] = getHeader( MENU_NEWS,$page_title );
  $buf['FOOTER'] = getFooter();

  TmplUty::out( $tmpl_dir .$tmpl_file , $buf);
  exit;

?>