<?php
class connect {
	//定数の宣言
	const DB_NAME = 'NARO';
	const HOST = 'localhost';
	const charset = 'utf8';
	const USER = 'NARO';
	const PASS = 'NARO';

	//データベースに接続する関数
	function pdo() {
		/*phpのバージョンが5.3.6よりも古い場合はcharset=".self::charsetが必要無くなり、array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.SELF::charset')が必要になり、5.3.6以上の場合は必要ないがcharset=".self::charsetは必要になる。*/
		$dsn="mysql:dbname=".self::DB_NAME.";host=".self::HOST.";charset=".self::charset;
		$user=self::USER;
		$pass=self::PASS;

		try {
            $pdo=new PDO($dsn,$user,$pass,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.SELF::charset));
            //エラーを表示してくれる。
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        }catch(PDOException $poe){
            echo 'PDOException error.'."<br>\n"."<br>\n";
            echo $poe -> getMessage()."<br>\n"."<br>\n";
            throw new Exception( $poe->getMessage( ) , $poe->getCode( ) );
            die();
        } catch(Exception $e) {
            echo 'Fatal Error.'.$e->getMessage();
            die();
        }

        return $pdo;
    }
 
    // SELECT文のときに使用する関数。
    // prepared statementではない場合。
    function select($sql) {
        $pdo = $this -> pdo();
        $stmt = $pdo -> query($sql);
        $items = $stmt -> fetchAll(PDO::FETCH_ASSOC);
        return $items;
    }

    // SELECT文のときに使用する関数。
    function selectSingleRecord($sql,$array) {
        $pdo = $this -> pdo();
        $stmt = $pdo -> prepare($sql);
        
        $result = [];
        foreach ($array as $asoArray) {
            $stmt -> execute($asoArray);
            $items = $stmt -> fetchAll(PDO::FETCH_ASSOC);
            $result = array_merge($result,$items);
        }
        
        return $result[0];
    }

    // INSERT,UPDATE,DELETE文の時に使用する関数。
    // prepared statementではない場合。
    function execute($sql) {
        $pdo = $this -> pdo();
        $stmt = $pdo -> exec($sql);

        return $stmt;
    }

    // INSERT,UPDATE,DELETE文の時に使用する関数。
    function ppstExecute($sql,$array) {
        $pdo = $this -> pdo();
        $stmt = $pdo -> prepare($sql);

        foreach($array as $asoArray) {
            $stmt -> execute($asoArray);
        }
        return $stmt;
    }

    // データが多かったので、分割して実行すれば大丈夫か確認。
    function ppstExecuteMultiTime($sql,$array) {
        foreach ($array as $asoArray) {
            $this -> ppstExecute($sql,$asoArray);
        }
    }

    // 特殊メソッド
    // uniqNo取得用
    function getUniqno($sql,$timeYmd,$timeHour) {
        $pdo = $this -> pdo();
        $stmt = $pdo -> prepare($sql);

        $stmt -> bindValue(':GET_YMD1',$timeYmd,PDO::PARAM_STR);
        $stmt -> bindValue(':GET_HOUR1',$timeHour,PDO::PARAM_STR);
        $stmt -> bindValue(':GET_YMD2',$timeYmd,PDO::PARAM_STR);
        $stmt -> bindValue(':GET_HOUR2',$timeHour,PDO::PARAM_STR);

        $stmt -> execute();
        $result = $stmt -> fetch(PDO::FETCH_ASSOC);

        return $result['UNIQNO'];
    }

    // Nコード登録用のBulkInsert作成
    function bulkInsertNcode($asoArray) {
        $sql = 'insert into M_NARONCOD (NCODE,TITLE) values ';
        $pdo = $this -> pdo();

        foreach($asoArray as $key => $value) {
            $sql = $sql
                    .'('.$pdo -> quote($value[':NCODE'])
                    .','.$pdo -> quote($value[':TITLE'])
                    .'),'
            ;
        }

        $sql = rtrim($sql,',');
        $pdo -> exec($sql);

        return;
    }

    // C_NAROSTRY登録用のBulkInsert作成
    function bulkInsertNarostry($timeYmd,$timeHour,$sysdate,$array) {
        $sql_header = 'insert into C_NAROSTRY '
                        .'(GET_YMD,GET_HOUR,NCODE,STORY,INDATE)'
                        .' values ';

        $pdo = $this -> pdo();

        foreach((array)$array as $asoArray) {
            $sql = $sql_header;
            
            foreach($asoArray as $key => $value) {
                $sql = $sql
                        .'('.$pdo -> quote($timeYmd)
                        .','.$pdo -> quote($timeHour)
                        .','.$pdo -> quote($value['ncode'])
                        .','.$pdo -> quote($value['story'])
                        .','.$pdo -> quote($sysdate)
                        .'),'
                ;
            }

            $sql = rtrim($sql,',');

            $pdo -> exec($sql);
        }

        return;
    }

    // C_NAROINFO登録用のBulkInsert作成
    function bulkInsertNaroinfo($timeYmd,$timeHour,$sysdate,$array) {
        $sql_header = 'insert into C_NAROINFO '
                        .'(GET_YMD,GET_HOUR,NCODE,TITLE,'
                        .'USERID,WRITER,BIGGENRE,GENRE,'
                        .'KEYWORD,GENERAL_FIRSTUP,GENERAL_LASTUP,'
                        .'NOVEL_TYPE,END,GENERAL_ALL_NO,LENGTH,TIME,'
                        .'ISSTOP,ISR15,ISBL,ISGL,ISZANKOKU,ISTENSEI,'
                        .'ISTENNI,PC_OR_K,SASIE_CNT,'
                        .'KAIWARITU,NOVELUPDATED_AT,UPDATED_AT,INDATE) values ';

        $pdo = $this -> pdo();

        foreach((array)$array as $asoArray) {
            $sql = $sql_header;
            
            foreach($asoArray as $key => $value) {
                $sql = $sql
                        .'('.$pdo -> quote($timeYmd)
                        .','.$pdo -> quote($timeHour)
                        .','.$pdo -> quote($value['ncode'])
                        .','.$pdo -> quote($value['title'])
                        .','.$pdo -> quote($value['userid'])
                        .','.$pdo -> quote($value['writer'])
                        .','.$pdo -> quote($value['biggenre'])
                        .','.$pdo -> quote($value['genre'])
                        .','.$pdo -> quote($value['keyword'])
                        .','.$pdo -> quote($value['general_firstup'])
                        .','.$pdo -> quote($value['general_lastup'])
                        .','.$pdo -> quote($value['noveltype'])
                        .','.$pdo -> quote($value['end'])
                        .','.$pdo -> quote($value['general_all_no'])
                        .','.$pdo -> quote($value['length'])
                        .','.$pdo -> quote($value['time'])
                        .','.$pdo -> quote($value['isstop'])
                        .','.$pdo -> quote($value['isr15'])
                        .','.$pdo -> quote($value['isbl'])
                        .','.$pdo -> quote($value['isgl'])
                        .','.$pdo -> quote($value['iszankoku'])
                        .','.$pdo -> quote($value['istensei'])
                        .','.$pdo -> quote($value['istenni'])
                        .','.$pdo -> quote($value['pc_or_k'])
                        .','.$pdo -> quote($value['sasie_cnt'])
                        .','.$pdo -> quote($value['kaiwaritu'])
                        .','.$pdo -> quote($value['novelupdated_at'])
                        .','.$pdo -> quote($value['updated_at'])
                        .','.$pdo -> quote($sysdate)
                        .'),'
                ;
            }

            $sql = rtrim($sql,',');

            $pdo -> exec($sql);
        }

        return;
    }

    // C_NAROHYKA登録用のBulkInsert作成
    function bulkInsertNarohyka($timeYmd,$timeHour,$uniqno,$sysdate,$array) {
        $sql_header = 'insert into C_NAROHYKA '
                        .'(GET_YMD,GET_HOUR,UNIQNO,NCODE,'
                        .'GLOBAL_POINT,DAILY_POINT,'
                        .'WEEKLY_POINT,MONTHLY_POINT,QUARTER_POINT,'
                        .'YEARLY_POINT,FAV_NOVEL_CNT,IMPRESSION_CNT,'
                        .'REVIEW_CNT,ALL_POINT,ALL_HYOKA_CNT,INDATE) values ';
        $pdo = $this -> pdo();

        foreach((array)$array as $asoArray) {
            $sql = $sql_header;
            
            foreach($asoArray as $key => $value) {
                $sql = $sql
                        .'('.$pdo -> quote($timeYmd)
                        .','.$pdo -> quote($timeHour)
                        .','.$pdo -> quote($uniqno)
                        .','.$pdo -> quote($value['ncode'])
                        .','.$pdo -> quote($value['global_point'])
                        .','.$pdo -> quote($value['daily_point'])
                        .','.$pdo -> quote($value['weekly_point'])
                        .','.$pdo -> quote($value['monthly_point'])
                        .','.$pdo -> quote($value['quarter_point'])
                        .','.$pdo -> quote($value['yearly_point'])
                        .','.$pdo -> quote($value['fav_novel_cnt'])
                        .','.$pdo -> quote($value['impression_cnt'])
                        .','.$pdo -> quote($value['review_cnt'])
                        .','.$pdo -> quote($value['all_point'])
                        .','.$pdo -> quote($value['all_hyoka_cnt'])
                        .','.$pdo -> quote($sysdate)
                        .'),'
                ;
            }

            $sql = rtrim($sql,',');

            $pdo -> exec($sql);
        }

        return;
    }

    /**
     * このメソッド内で以下を実行する。
     * 1.履歴フォルダへの登録
     * 2.収集テーブルのTruncate
     * 3.収集テーブルへのBulkinsert
     */
    function insertNaroinfo($array) {
        $sql_1 = 'insert into C_NAROINFO_H '
                .'select C1.* from C_NAROINFO C1 '
                .'where not exists( '
                .'select \'x\' from C_NAROINFO_H C2 '
                .'where C2.NCODE = C1.NCODE )'
        ;

        // 1.履歴フォルダへの登録
        $this -> execute($sql_1);

        $sql_2 = 'truncate table C_NAROINFO';

        // 2.収集テーブルのTruncate
        $this -> execute($sql_2);

        $sql_header = 'insert into C_NAROINFO '
                        .'(NCODE,TITLE,'
                        .'USERID,WRITER,BIGGENRE,GENRE,'
                        .'KEYWORD,GENERAL_FIRSTUP,GENERAL_LASTUP,'
                        .'NOVEL_TYPE,END,GENERAL_ALL_NO,LENGTH,TIME,'
                        .'ISSTOP,ISR15,ISBL,ISGL,ISZANKOKU,ISTENSEI,'
                        .'ISTENNI,PC_OR_K,SASIE_CNT,'
                        .'KAIWARITU,NOVELUPDATED_AT,UPDATED_AT) values ';

        $pdo = $this -> pdo();

        foreach((array)$array as $asoArray) {
            $sql = $sql_header;
            
            foreach($asoArray as $key => $value) {
                $sql = $sql
                        .'('.$pdo -> quote($value['ncode'])
                        .','.$pdo -> quote($value['title'])
                        .','.$pdo -> quote($value['userid'])
                        .','.$pdo -> quote($value['writer'])
                        .','.$pdo -> quote($value['biggenre'])
                        .','.$pdo -> quote($value['genre'])
                        .','.$pdo -> quote($value['keyword'])
                        .','.$pdo -> quote($value['general_firstup'])
                        .','.$pdo -> quote($value['general_lastup'])
                        .','.$pdo -> quote($value['noveltype'])
                        .','.$pdo -> quote($value['end'])
                        .','.$pdo -> quote($value['general_all_no'])
                        .','.$pdo -> quote($value['length'])
                        .','.$pdo -> quote($value['time'])
                        .','.$pdo -> quote($value['isstop'])
                        .','.$pdo -> quote($value['isr15'])
                        .','.$pdo -> quote($value['isbl'])
                        .','.$pdo -> quote($value['isgl'])
                        .','.$pdo -> quote($value['iszankoku'])
                        .','.$pdo -> quote($value['istensei'])
                        .','.$pdo -> quote($value['istenni'])
                        .','.$pdo -> quote($value['pc_or_k'])
                        .','.$pdo -> quote($value['sasie_cnt'])
                        .','.$pdo -> quote($value['kaiwaritu'])
                        .','.$pdo -> quote($value['novelupdated_at'])
                        .','.$pdo -> quote($value['updated_at'])
                        .'),'
                ;
            }
            $sql = rtrim($sql,',');
            $pdo -> exec($sql);
        }

        return;

    }

    // C_NARORANK登録用のBulkInsert作成
    function bulkInsertNarorank($timeYmd,$timeHour,$uniqno,$sysdate,$array) {
        $sql_header = 'insert into C_NARORANK '
                        .'(GET_YMD,GET_HOUR,UNIQNO,'
                        .'DATA_TYPE,GENRE,NCODE,RANKING,POINT,INDATE) values ';
        $pdo = $this -> pdo();

        foreach((array)$array as $asoArray) {
            $sql = $sql_header;
            
            foreach($asoArray as $key => $value) {
                $sql = $sql
                        .'('.$pdo -> quote($timeYmd)
                        .','.$pdo -> quote($timeHour)
                        .','.$pdo -> quote($uniqno)
                        .','.$pdo -> quote($value['point_cls'])
                        .','.$pdo -> quote($value['genre_cls'])
                        .','.$pdo -> quote($value['ncode'])
                        .','.$pdo -> quote($value['ranking'])
                        .','.$pdo -> quote($value['point'])
                        .','.$pdo -> quote($sysdate)
                        .'),'
                ;
            }

            $sql = rtrim($sql,',');

            $pdo -> exec($sql);
        }

        return;
    }

}
?>