<?
//■■■
//■■■学校TOPプレビュー表示（Y.Sawai 2007/12/6）
//■■■

//■■■外部ファイルのインクルード
//ini_set("display_errors","1");
//ini_set('error_reporting', E_ALL);

  function Getschoolup($type=null){
    
    $dir = str_replace(strstr(__FILE__,"/web/"),"",__FILE__);
    $dir = "/home/sites/www.sanpou-s.net";
  //■共通ファイルのインクルード
    require_once($dir."/web/school/common_acq_gs/inc_common_value.php");

  //学校区分
    require_once($dir."/web/search_event/common_acq_evt/inc_set_array.php");

  //schooldbから学校紹介契約校を取り出す
    $sql = "SELECT ad_url FROM schooldb.schooldb 
            WHERE ad_url like '%school%' 
            AND (b_code is null or b_code = 0 ) ";

    //ビューティ
    if($type == "beauty"){
      $sql .= " and (  (c43!=0 and c43 is not null)
            or (c44!=0 and c44 is not null)
            or (c45!=0 and c45 is not null) ) ";
    
    //フード系統
    }elseif($type == "food"){
      $sql .= " and (  (a51!=0 and a51 is not null)
              or (a52!=0 and a52 is not null) 
              or (c40!=0 and c40 is not null) 
              or (c41!=0 and c41 is not null) 
              or (c42!=0 and c42 is not null) ) ";
    
    //美術系統
    }elseif($type == "design"){        
      $sql .= " and ( (a56!=0 and a56 is not null) 
              or (a57!=0 and a57 is not null) 
              or (a58!=0 and a58 is not null) 
              or (a59!=0 and a59 is not null) 
              or (c53!=0 and c53 is not null) 
              or (c54!=0 and c54 is not null) 
              or (c55!=0 and c55 is not null) 
              or (c56!=0 and c56 is not null)) ";
    
    //音楽系統
    }elseif($type == "music"){
      $sql .= " and ( (a60!=0 and a60 is not null)
              or (a61!=0 and a61 is not null) 
              or (c11!=0 and c11 is not null) 
              or (c12!=0 and c12 is not null) 
              or (c13!=0 and c13 is not null) 
              or (c14!=0 and c14 is not null) 
              or (c15!=0 and c15 is not null) 
              or (c16!=0 and c16 is not null)) ";
    }

  //schooldbに接続
    require_once($dir."/lib/common/cnct_sch.php");
    $conn = dbConnect_schooldb();

    $res = mysql_query($sql, $conn) or die("(1).$db_err_message".$sql);  //■SQL実行
    $pattern = '/(\/html\/\d+)/';
    while($row = mysql_fetch_array($res, MYSQL_ASSOC)){
      $r_ad_url = trim($row['ad_url']);
      preg_match($pattern, $r_ad_url, $matches);
      $gakkou_id = ereg_replace("/html/","",$matches[0]);
      
      //年度があれば
      if(strpos($r_ad_url,"school_nendo") !==false){
          $nendo_array = explode("school_nendo/",$r_ad_url);
          $nendo = substr($nendo_array[1],0,4);
      } else {
          //通常の年度
          if(date("n") <= 3){
            $nendo = date("Y");
          } else if(date("n") >=4 && date("n") <=12){
            $nendo = date("Y")+1;
          }
      }
      $gakkou_id_array['gakkou_id'][$gakkou_id] = $gakkou_id;
      $gakkou_id_array['nendo'][$gakkou_id]     = $nendo;
      $gakkou_id_array['ad_url'][$gakkou_id]    = $r_ad_url;
    }

    //今年度より-1以上まで
    if(date("n") <= 3){
      $base_nendo = date("Y") -1;
    } else {
      $base_nendo = date("Y");
    }

    $today = date("Y-m-d");

   //sanpou01に接続
    require_once($dir."/lib/common/cnct_sp01.php");
    $conn = dbConnect_sanpou01();

    //■プレビュー時はプレビューデータのみを検索する。この場合、公開・非公開は条件に入れない。
    $sql = "SELECT * 
             FROM gs_gakkou_top_info_nendo ,gs_gakkou_user_mt 
             WHERE (gs_gtop_preview_flag = '0') 
               AND (gs_gtop_gakkou_id = gs_gu_gakkou_id ) 
               AND (gs_gu_mukou_flag = '0') 
               AND (gs_gtop_gakkou_id in (".implode(",",$gakkou_id_array['gakkou_id']).")) 
               AND (gs_gtop_nendo >= '{$base_nendo}' ) 
               AND (gs_gtop_message_date <= '{$today}' ) 
               ORDER BY gs_gtop_message_date DESC,gs_gtop_koushin_timestamp DESC";

    $res = mysql_query($sql, $conn) or die("(7).$db_err_message".$sql);  //■SQL実行
    $html = "";
    $i = 0;
    
    $type_icon_array = array("01"=>"college","02"=>"junior_college","03"=>"specialty","04"=>"other");

    //■変数に格納する。
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
      //学校紹介の年度と同じでテキストに入力されている学校
      if($gakkou_id_array['nendo'][$row["gs_gtop_gakkou_id"]] == $row["gs_gtop_nendo"] && $row["gs_gtop_message_text"]){
        $gs_gakkou_mei[]                    = mb_convert_encoding(mb_convert_encoding($row["gs_gu_gakkou_mei"],"sjis-win","EUC-JP"),"UTF-8","sjis-win");                  //■学校名
        $wk_gakkou_mei                      = mb_convert_encoding(mb_convert_encoding($row["gs_gu_gakkou_mei"],"sjis-win","EUC-JP"),"UTF-8","sjis-win");
        $gs_gtop_gakkou_id[]                = $row["gs_gtop_gakkou_id"];                 //■学校ID
        $gs_gtop_message_date[]             = $row["gs_gtop_message_date"];              //■メッセージ更新日
        $gs_gtop_message_text[]             = mb_convert_encoding(mb_convert_encoding($row["gs_gtop_message_text"],"sjis-win","EUC-JP"),"UTF-8","sjis-win");              //■メッセージ文章
        $wk_gtop_message_text               = mb_convert_encoding(mb_convert_encoding($row["gs_gtop_message_text"],"sjis-win","EUC-JP"),"UTF-8","sjis-win");
        $gs_stop_message_nendo[]            = $row["gs_gtop_nendo"];                     //■年度
        $gs_gtop_koushin_timestamp[]        = $row["gs_gtop_koushin_timestamp"];         //■更新日時
        $gs_gu_gakkou_kubun[]               = $gakkou_kubun_array[$row['gs_gu_gakkou_kubun']];  //■学校区分
        
        $koushin_date = sprintf("%d/%d更新",substr($row["gs_gtop_message_date"],5,2),substr($row["gs_gtop_message_date"],-2));
        
        //今日だったらnewアイコン
        $new_icon = "";
        /*
        if($row['gs_gtop_message_date'] == $today){
           $new_icon = " <img src=\"/images/new.gif\" alt=\"new\" /> ";
        } else {
           $new_icon = "";
        }*/
        //TOPページ
        if($type != ""){
          //学校名のかっこ以降はとる
          if(strstr($wk_gakkou_mei,"(")){
            $short_name = str_replace(strstr($wk_gakkou_mei,"("),"",$wk_gakkou_mei);
          } else if(strstr($wk_gakkou_mei,"（")){
            $short_name = str_replace(strstr($wk_gakkou_mei,"（"),"",$wk_gakkou_mei);
          } else {
            $short_name = $wk_gakkou_mei;
          }
          //$html  = "<div class=\"schoolupBox\"><img src=\"/search_school/result_image.php?gakkou_id={$row['gs_gtop_gakkou_id']}\" alt=\"{$row['gs_gu_gakkou_mei']}\" width=\"50\" height=\"50\" style=\"float:left;margin-bottom:5px;margin-right:5px;\"/>";
          
          $wk_gtop_message_text = str_replace(array("<br />","\r\n","\r","\n"),"",nl2br($wk_gtop_message_text));
          
          $html = "<dt>{$short_name}<span class=\"date\">（{$koushin_date}{$new_icon}）</span></dt>";
          $html .= "<dd><a href=\"{$gakkou_id_array['ad_url'][$row['gs_gtop_gakkou_id']]}\">".str_replace("　","",mb_substr($wk_gtop_message_text,0,50))."…</a></dd>";
	//一覧ページ
        } else {
          /*$html = "<div class=\"schoolUpBox\">\n<div class=\"schoolUpName\">\n";
          $html .= "<a href=\"{$gakkou_id_array['ad_url'][$row['gs_gtop_gakkou_id']]}\">".$row["gs_gu_gakkou_mei"]."</a><span class=\"date\">".str_replace("-",".",$row['gs_gtop_message_date'])."更新 {$new_icon} </span>\n";
          $html .= "<img src=\"/search_school/images/icon_{$type_icon_array[$row['gs_gu_gakkou_kubun']]}.gif\" alt=\"{$gakkou_kubun_array[$row['gs_gu_gakkou_kubun']]}\" width=\"30\" height=\"20\" class=\"typeIcon\" /></div>\n";
          $html .= "<div class=\"schoolUpTxt\"><a href=\"{$gakkou_id_array['ad_url'][$row['gs_gtop_gakkou_id']]}\"><img src=\"/search_school/result_image.php?gakkou_id={$row['gs_gtop_gakkou_id']}\" alt=\"{$row['gs_gu_gakkou_mei']}\" /></a>\n";
          $html .= "<p>".mb_substr(trim($row["gs_gtop_message_text"]),0,150)."…</p>\n";
          $html .= "<div class=\"reset\"></div></div></div>\n";*/


          $html = "<li><span class=\"date\">".str_replace("-",".",$row['gs_gtop_message_date'])."更新 {$new_icon} </span><br>\n";
          $html .= $wk_gakkou_mei."<br>\n";
          $html .= "<a href=\"{$gakkou_id_array['ad_url'][$row['gs_gtop_gakkou_id']]}\">".str_replace("　","",mb_substr(trim($wk_gtop_message_text),0,124))."…</a></li>\n";
          //$html = mb_convert_encoding($html,"UTF-8","EUC-JP");
        }
        $date_array[] = $html;
        $i++;
        
        //最新20件まで
        if($i == 30){
           break;
        }
      }
    }

  //■■■DB切断
    mysql_close($conn);

    return $date_array; 
  }
  
  
  function Geteventup($type=null){

    //DB接続
    $dir = str_replace(strstr(__FILE__,"/web/"),"",__FILE__);
    require_once($dir."/lib/common/cnct_sp01.php");
    $conn = dbConnect_sanpou01();

    if($type == "beauty"){
      $bunya_sql = " AND (e.evt_keitou_bunya_list like '%0304%' OR e.evt_keitou_bunya_list like '%0404%') ";
    
    //フード系統
    }elseif($type == "food"){ //0108-0205-0305-0402
      $bunya_sql = " AND (e.evt_keitou_bunya_list like '%0108%' OR e.evt_keitou_bunya_list like '%0205%' OR 
                          e.evt_keitou_bunya_list like '%0305%' OR e.evt_keitou_bunya_list like '%0402%'  ) ";
    
    //美術系統
    }elseif($type == "design"){ //0110-0206-0316-0408
      $bunya_sql = " AND (e.evt_keitou_bunya_list like '%0110%' OR e.evt_keitou_bunya_list like '%0206%' OR 
                          e.evt_keitou_bunya_list like '%0316%' OR e.evt_keitou_bunya_list like '%0408%'  ) ";
    
    //音楽系統
    }elseif($type == "music"){ //0110-0310-0408
      $bunya_sql = " AND (e.evt_keitou_bunya_list like '%0110%' OR e.evt_keitou_bunya_list like '%0310%' OR 
                          e.evt_keitou_bunya_list like '%0408%'  ) ";
    } else {
      $bunya_sql = "";
    }

    //最新のイベント10件とPDFがあればPDFリンク
    $all_sql = "
            SELECT DISTINCT d.evt_edt_event_id,e.*
              FROM evt_event_date_mt as d LEFT JOIN evt_event_mt as e
                ON d.evt_edt_event_id = e.evt_event_id
              WHERE (
                    evt_koukai_flag = '1'
                AND evt_mobile_kubun = 0
                AND e.evt_event_shurui_kubun in (1,2)
                AND (evt_pdf_path)
                AND (d.evt_edt_kaisai_date >= '".date("Ymd")."' )
                {$bunya_sql}
                    )
              ORDER BY e.evt_date_list DESC
              LIMIT 0 , 4 ";
    $res = mysql_query($all_sql, $conn) or die("(1).error");
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

      if($row["evt_pdf_path"]){
        $id   = str_replace("_event_pdf.pdf","",$row["evt_pdf_path"]);
        $link = "<a href=\"http://www.sanpou-s.net/search_event/event_img/{$id}/{$row["evt_pdf_path"]}\" target=\"_blank\">".$row["evt_title_gakkou_name"]."</a><img src=\"/common/images/windowicon.gif\" alt=\"別窓が開きます\" width=\"14\" height=\"12\" align=\"top\" /><img src=\"/common/images/pdficon.gif\" alt=\"pdfが開きます\" class=\"ml5\" align=\"top\" />";
      }
      
      $date = "";
      if($row["evt_date_list"]){
        $date = sprintf("%04d.%d.%d",substr($row["evt_date_list"],0,4),substr($row["evt_date_list"],5,2),substr($row["evt_date_list"],-2));
      }
      $link_array[] = "<li>[イベント]<br />{$link}　（{$date}）</li>";
    }
    
    $disp_str = implode("",$link_array);
    
    return $disp_str;
  }
  
?>