<?php

require_once('./Regist_data.php');

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Regist_DB
 * M_registへの登録関係を集めたクラス
 * @author 一基
 */
class Regist_DB {

    //put your code here
    private $con;
    //private $selectDB;
    private $sql;
    private $res;
    private $regist_tableName;
    private $text_tableName;

    //コンストラクタ
    function __construct() {
        
    }

    private function DBAccess() {
        //$this->con = new PDO('mysql:host=localhost;dbname=oshietyadb;charset=utf8', 'tanaka', 'udRXg3Yx');
        $this->con = new PDO('mysql:host=localhost;dbname=eventdb;charset=utf8', 'event', 'L523GpA');
        $this->con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $this->con->setAttribute(PDO::NULL_TO_STRING, true);             
    }

    private function dbTextSet($sql, $regist_id, $sentence, $row) {
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            //文章の登録
            $this->res = $this->con->prepare($sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $this->res->bindValue(':row', $row, PDO::PARAM_INT);
            $this->res->bindValue(':sentence', $sentence, PDO::PARAM_STR);
            $data = $this->res->execute();
            if ($data == 1) {
                $this->con->commit();
            }
        } catch (Exception $ex) {
            $this->con->rollback();
            //echo "データ登録エラー2";
        }
        unset($sql, $regist_id, $row, $sentence, $data);
    }

    /**
     * 次に使用するIdを求める
     * @return int
     */
    public function getNextId() {
        $this->DBAccess();
        try {
            $this->res = $this->con->query("select max(regist_id)+1 as nextId from " . $this->regist_tableName . " where regist_id != 999");
            $id = $this->res->fetch();
        } catch (Exception $ex) {
            //echo "ID取得エラー";
        }
        if ($id['nextId'] != null) {
            return $id['nextId'];
        } else {
            return 1;
        }
    }

    /**
     * データの追加
     * @param Regist_data $regist_data
     * @return int
     */
    public function regist_data(Regist_data $regist_data) {
        $result = 0;
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        try {
            $this->con->exec("delete from " . $this->regist_tableName . " where regist_id=999");

            $this->sql = "insert into " . $this->regist_tableName . "(regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date) values(:regist_id,:regist_name,:regist_lat,:regist_lng,:category_id,:file_name,now(),now())";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_data->getRegist_id(), PDO::PARAM_INT);
            $this->res->bindValue(':regist_name', $regist_data->getRegist_name(), PDO::PARAM_STR);
            $this->res->bindValue(':regist_lat', $regist_data->getRegist_lat(), PDO::PARAM_STR);
            $this->res->bindValue(':regist_lng', $regist_data->getRegist_lng(), PDO::PARAM_STR);
            $this->res->bindValue(':category_id', $regist_data->getCategory_id(), PDO::PARAM_STR);
            $this->res->bindValue(':file_name', $regist_data->getFile_name(), PDO::PARAM_STR);
            $data = $this->res->execute();

            $this->sql = "insert into " . $this->regist_tableName . "(regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date) values(999,:regist_name,:regist_lat,:regist_lng,:category_id,:file_name,now(),now())";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_name', $regist_data->getRegist_name(), PDO::PARAM_STR);
            $this->res->bindValue(':regist_lat', $regist_data->getRegist_lat(), PDO::PARAM_STR);
            $this->res->bindValue(':regist_lng', $regist_data->getRegist_lng(), PDO::PARAM_STR);
            $this->res->bindValue(':category_id', $regist_data->getCategory_id(), PDO::PARAM_STR);
            $this->res->bindValue(':file_name', $regist_data->getFile_name(), PDO::PARAM_STR);
            $bac = $this->res->execute();
            if ($data == 1 && $bac == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
           //echo "データ登録エラー";
        }
        unset($data, $bac);
        return $result;
    }

    /**
     * 追加の取り消し
     * @param type $regist_id
     * @return int
     */
    public function back_regist_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            $this->sql = "delete from " . $this->regist_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();
            $this->con->exec("delete from " . $this->regist_tableName . " where regist_id=999");
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            //echo "データ登録エラー";
        }
        unset($data);
        return $result;
    }

    /**
     * 文章の登録
     * @param type $regist_id
     * @param type $sentenceArray
     * @param type $row
     * @return int
     */
    public function regist_text_data($regist_id, $sentenceArray, $row) {
        $result = 1;
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            $this->con->exec("delete from " . $this->text_tableName . " where regist_id=999");
            $this->con->commit();
            //文章の登録
            $this->sql = "insert into " . $this->text_tableName . " values(:regist_id,:row,:sentence)";
            for ($cnt = 0; $cnt < $row; $cnt++) {
                if ($sentenceArray[$cnt] !== "") {
                    $this->dbTextSet($this->sql, $regist_id, $sentenceArray[$cnt], $cnt);
                    $this->dbTextSet($this->sql, 999, $sentenceArray[$cnt], $cnt);
                } else {
                    $this->dbTextSet($this->sql, $regist_id, "", $cnt);
                    $this->dbTextSet($this->sql, 999, "", $cnt);
                }
            }
            //空白行の登録
            for ($cnt = $row; $cnt < 8; $cnt++) {
                $this->dbTextSet($this->sql, $regist_id, "", $cnt);
                $this->dbTextSet($this->sql, 999, "", $cnt);
            }
        } catch (Exception $ex) {
            $result = 0;
            //echo $ex;
            //$this->con->rollback();
           // echo "データ登録エラー1";
        }
        unset($regist_id, $row, $sentenceArray, $cnt);
        return $result;
    }

    /**
     * 文章追加の取り消し
     * @param type $regist_id
     * @return int
     */
    public function back_regist_text_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        //トランザクションをはじめる準備
        mysql_query("set autocommit=0");
        //トランザクション開始
        mysql_query("begin");
        //バックアップ削除
        try {
            $this->sql = "delete from " . $this->text_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();
            $this->con->exec("delete from " . $this->text_tableName . " where regist_id=999");
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            $result = 0;
            //echo "データ登録エラー";
        }
        unset($regist_id, $data);
        return $result;
    }

    /**
     * データの更新
     * @param Regist_data $regist_data
     * @return int
     */
    public function update_data(Regist_data $regist_data) {
        $result = 0;
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            $this->con->exec("delete from " . $this->regist_tableName . " where regist_id=999");
            //バックアップ用データの登録
            $this->sql = "insert into " . $this->regist_tableName . "(regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date,visiflag) select 999 as regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date,visiflag from " . $this->regist_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue('regist_id', $regist_data->getRegist_id(), PDO::PARAM_INT);
            $bac = $this->res->execute();
            $this->sql = "update " . $this->regist_tableName . " set regist_name=:regist_name,category_id=:category_id,update_date=now()";
            if ($regist_data->getFile_name() != "toshinaga.jpg") {
                $this->sql .= ",file_name=:file_name";
            }
            if ($regist_data->getRegist_lat() != null) {
                $this->sql .= ",regist_lat=:regist_lat,regist_lng=:regist_lng";
            }
            $this->sql .= " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_name', $regist_data->getRegist_name(), PDO::PARAM_STR);
            $this->res->bindValue(':category_id', $regist_data->getCategory_id(), PDO::PARAM_INT);
            if ($regist_data->getFile_name() != "toshinaga.jpg") {
                $this->res->bindValue(':file_name', $regist_data->getFile_name(), PDO::PARAM_STR);
            }
            if ($regist_data->getRegist_lat() != null) {
                $this->res->bindValue(':regist_lat', $regist_data->getRegist_lat(), PDO::PARAM_STR);
                $this->res->bindValue(':regist_lng', $regist_data->getRegist_lng(), PDO::PARAM_STR);
            }
            $this->res->bindValue(':regist_id', $regist_data->getRegist_id(), PDO::PARAM_INT);
            $data = $this->res->execute();
            if ($data == 1 && $bac == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            //echo "データ登録エラー";
        }
        unset($data, $bac);
        return $result;
    }

    /**
     * 更新の取り消し
     * @param Regist_data $regist_data
     * @return int
     */
    public function back_update_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        // トランザクション処理を開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            $this->sql = "delete from " . $this->regist_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $this->res->execute();

            $this->sql = "insert into " . $this->regist_tableName . "(regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date,visiflag) select :regist_id as regist_id,regist_name,regist_lat,regist_lng,category_id,file_name,regist_date,update_date,visiflag from " . $this->regist_tableName . " where regist_id=999";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();

            $this->con->exec("delete from " . $this->regist_tableName . " where regist_id=999");
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            //echo "データ登録エラー";
        }
        unset($data);
        return $result;
    }

    /**
     * 文章の更新
     * @param type $regist_id
     * @param type $sentenceArray
     * @param type $row
     * @return int
     */
    public function update_text_data($regist_id, $sentenceArray, $row) {
        $result = 1;
        $this->DBAccess();
        //トランザクションの開始
        $this->con->beginTransaction();
        //バックアップ削除
        try {
            $this->con->exec("delete from " . $this->text_tableName . " where regist_id=999");
            //文章の登録(backup)
            $this->sql = "insert into " . $this->text_tableName . "(regist_id,line_id,text_data) select 999 as regist_id,line_id,text_data from " . $this->text_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(1, $regist_id, PDO::PARAM_INT);
            $this->res->execute();
            $this->con->commit();

            //文章の登録
            $this->sql = "update " . $this->text_tableName . " set text_data=:sentence where regist_id=:regist_id and line_id=:row";
            for ($cnt = 0; $cnt < $row; $cnt++) {
                $this->dbTextSet($this->sql, $regist_id, $sentenceArray[$cnt], $cnt);
            }
            //空白行の登録
            for ($cnt = $row; $cnt < 8; $cnt++) {
                $this->dbTextSet($this->sql, $regist_id, "", $cnt);
            }
        } catch (Exception $ex) {
            $result = 0;
            //echo "文章データ更新エラー";
        }
        unset($regist_id, $row, $sentenceArray, $cnt);
        return $result;
    }

    /**
     * 文章更新の取り消し
     * @param type $regist_id
     * @return int
     */
    public function back_update_text_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        //トランザクションの開始
        $this->con->beginTransaction();
        try {
            //バックアップ用データの追加
            $this->sql = "delete from " . $this->text_tableName . " where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue('regist_id', $regist_id, PDO::PARAM_INT);
            $this->res->execute();
            $this->sql = "insert into " . $this->text_tableName . "(regist_id,line_id,text_data) select :regist_id as regist_id,line_id,text_data from " . $this->text_tableName . " where regist_id=999";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();
            //バックアップ削除
            $this->con->exec("delete from " . $this->text_tableName . " where regist_id=999");
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            $result = 0;
            //echo "データ登録エラー";
        }
        unset($regist_id);
        return $result;
    }

    /**
     * 投稿データの削除
     * @param type $regist_id
     * @return int
     */
    public function delete_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        //トランザクションの開始
        $this->con->beginTransaction();
        try {
            $this->sql = "update " . $this->regist_tableName . " set visiflag=0,file_name='toshinaga.jpg' where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            //echo "データ登録エラー";
        }
        unset($data);
        return $result;
    }

    /**
     * 削除の取り消し
     * @param type $regist_id
     * @return int\\\
     */
    public function back_delete_data($regist_id) {
        $result = 0;
        $this->DBAccess();
        //トランザクションの開始
        $this->con->beginTransaction();
        try {
            $this->sql = "update " . $this->regist_tableName . " set visiflag=1,file_name='toshinaga.jpg' where regist_id=:regist_id";
            $this->res = $this->con->prepare($this->sql);
            $this->res->bindValue(':regist_id', $regist_id, PDO::PARAM_INT);
            $data = $this->res->execute();
            if ($data == 1) {
                //コミット
                $this->con->commit();
                $result = 1;
            } else {
                //ロールバック
                $this->con->rollback();
            }
        } catch (Exception $ex) {
            //echo "データ登録エラー";
        }
        unset($data);
        return $result;
    }

    public function getRegist_tableName() {
        return $this->regist_tableName;
    }

    public function getText_tableName() {
        return $this->text_tableName;
    }

    public function setRegist_tableName($regist_tableName) {
        $this->regist_tableName = $regist_tableName;
    }

    public function setText_tableName($text_tableName) {
        $this->text_tableName = $text_tableName;
    }

}