
<?php
    
    function leftBrackets(){
        // $leftBrackets = "\^\[\^" では誤作動
        //	return "\^\[\^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
        return "^[^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
    }
    function rightBrackets(){
        return "^]^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
        //	return "\^\]\^"; // encode 中で使う '(' -- クライアント側との約束 lib.js
    }
    function decodeObject($buff){
        // "key(value)" 形式の文字列を OBJECT に decode して返す
        $obj = array();
        //	echo "decodeObject ===\n$buff\n"; //##
        for ($p=$begin=$nest=0, $len=strlen($buff); $p < $len; $p++){
            $ch = substr($buff, $p, 1);
            if (strcmp($ch, "(") == 0){
                if ($nest == 0){
                    $key = trim(substr($buff, $begin, $p - $begin)); // key 終端
                    $begin = $p + 1; // value 開始位置
                }
                $nest++;
            } else if (strcmp($ch, ")") == 0){
                if (--$nest == 0){
                    if ($nest == 0){
                        // value 終端 ( trim しない )
                        $value = substr($buff,$begin,$p-$begin);
                        $begin = $p + 1; // key 開始位置
                    }
                    if (ereg(")", $value)){ // まだ入れ子になっているなら
                        $obj[$key] = decodeObject($value);
                    } else {
                        // 特定文字を "(", ")" へ戻す
                        $array = explode(leftBrackets(), $value);
                        $value = join("(", $array);
                        $array = explode(rightBrackets(), $value);
                        $value = join(")", $array);
                        
                        $obj[$key] = $value;
                    }
                    //echo "$key{".$obj[$key]."}\n"; //##
                }
            }
        }
        return $obj;
    }
    
    function hasDB($_dbname){
        // $db_name があれば TRUE を、なければ FALSE を返す
        $sql="SHOW TABLES FROM $_dbname";
        $result = mysql_query($sql);
        while ($row = mysql_fetch_row($result)) {
            return TRUE;
        }
        return FALSE;
    }
    
    function hasTable($_dbname, $_tablename){
        // $db_name 中に $_tablename があれば TRUE を、なければ FALSE を返す
        $sql="SHOW TABLES FROM $_dbname";
        $result = mysql_query($sql);
        while ($row = mysql_fetch_row($result)) {
            if ($row[0]==$_tablename) return TRUE;
        }
        return FALSE;
    }
	
    function hasField($_dbname, $_tablename, $_fieldname){
        // $db_name 中に $_tablename があれば 1 を、なければ 0 を返す
        $result = mysql_query("SHOW COLUMNS FROM $_tablename");
        if ($result) {
            if (mysql_num_rows($result) > 0) {
                while ($row = mysql_fetch_assoc($result)) {
                    $fieldname = $row['Field'];
                    //echo "$_tablename ..... $fieldname \n";
                    if (strcmp($fieldname, $_fieldname) == 0) return TRUE;
                }
            }
        } else 
            echo 'Could not run query: ' . mysql_error();
        return FALSE;
    }
	
    function createTableIfNeeded($_dbname){
        // DB が存在しなければ生成
        if (hasDB($_dbname) == FALSE){
            $sql="CREATE DATABASE $_dbname";
            echo "$sql \n"; //##
            mysql_query($sql);
        }
        // TABLE が存在しなければ生成
        if (! hasTable($_dbname, "MoneyTable")){
            $sql="CREATE TABLE `MoneyTable` (
            `entryDate` TIMESTAMP NULL ,
            `value1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `value2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `staff1` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `staff2` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `income` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `payment` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `memo` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
            `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
            `owner` VARCHAR( 32 ) NULL ,
            `rowid` DOUBLE NOT NULL AUTO_INCREMENT PRIMARY KEY
            )";
            echo "$sql \n"; //##
            mysql_query($sql);
        }
        // income フィールドがなければ追加
        if (! hasField('NOA', 'MoneyTable', 'income')){
            $sql = "ALTER TABLE `MoneyTable` 
            ADD `income` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
            AFTER `staff2`";
            echo "$sql ======\n"; //##
            $result = mysql_query($sql);
        }
        // payment フィールドがなければ追加
        if (! hasField('NOA', 'MoneyTable', 'payment')){
            $sql = "ALTER TABLE `MoneyTable` 
            ADD `payment` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
            AFTER `income`";
            echo "$sql ======\n"; //##
            $result = mysql_query($sql);
        }
    }
    
    function getStaffs(){
        // 職員名リストを entryDate == '' のレコードの value1 から取得
        $sql = "SELECT * FROM `MoneyTable` WHERE `value1`='STAFF_LIST'";
        echo "$sql -----\n"; //##
        
        $result=mysql_query($sql);
        if (mysql_num_rows ($result) > 0){
            $row=mysql_fetch_array($result);
            return $row['value2'];
        }
        return NULL;
    }
    
    

    $command=$_GET['command']; $command=htmlspecialchars($command);
    $value=$_GET['value']; $value=htmlspecialchars($value);
    
    // DB を開く
    // ### セキュリティーを保つには、以下のファイルを外からアクセスできない 
    // ### directory に置き、以下の cfg.php の pass をそこへ変更
    require_once('../XNOA/cfg.php');
    
    $dbc = mysql_connect($db['host'], $db['user'], $db['pwd']);
    $db_select=mysql_select_db($db['dbname']);
    mysql_query("set names utf8");
    
    if (strcmp($command,"CREATE_TABLE_IF_NEEDED") == 0){
        // NOA の各テーブルをチェックし存在しなければ生成する
        echo "<SEPARATOR>";
        createTableIfNeeded($db['dbname']);
    } else if (strcmp($command,"GET_HISTORY") == 0){
        // 日付リストと職員リストを返す
        $sql = "SELECT * FROM `MoneyTable` 
        ORDER BY `entryDate` DESC";
        $result=mysql_query($sql);
        echo "$sql \n";//###

        $array = array();
        if (mysql_num_rows ($result) > 0){
            while ($row=mysql_fetch_array($result)){
                if ($row['value1'] == "STAFF_LIST") continue;
                
                $array[] = $row['entryDate'];
            }
        }
        $records = array();
        $records['history'] = $array;
        
        $st = getStaffs();
        $records['staffs'] = explode(",", $st);
        
        echo "<SEPARATOR>";
        echo json_encode($records);
    } else if (strcmp($command,"PUT_MONY") == 0){
        $args = decodeObject($value);
        // client 側との約束ごと
        $entryDate = $args['entryDate'];
        $val1 = $args['val1'];
        $staff1 = $args['staff1'];
        $val2 = $args['val2'];
        $staff2 = $args['staff2'];
        $memo = $args['memo'];
        $income = $args['income'];
        $payment = $args['payment'];
        
        $sql = "SELECT * FROM `MoneyTable` WHERE `entryDate`='$entryDate'";
        echo "$sql -----\n"; //##
        $result=mysql_query($sql);

        if ($row=mysql_fetch_array($result)){
            if ($staff1){
                $sql = "UPDATE `MoneyTable` 
                SET `value1`='$val1',`staff1`='$staff1',`income`='$income',`payment`='$payment',`memo`='$memo'
                WHERE `entryDate`='$entryDate'";
            } else {
                $sql = "UPDATE `MoneyTable` 
                SET `value2`='$val2',`staff2`='$staff2',`income`='$income',`payment`='$payment',`memo`='$memo'
                WHERE `entryDate`='$entryDate'";
            }
        } else {
            $sql = "INSERT INTO `MoneyTable`
            (`entryDate`,`value1`,`value2`,`staff1`,`staff2`,`income`,`payment`,`memo`) 
            VALUES ('$entryDate','$val1','$val2','$staff1','$staff2','$income','$payment','$memo')";
        }
        echo "----- update MoneyTable \n $sql \n"; //##
		mysql_query($sql);
        echo "<SEPARATOR>点検結果を保存しました";
    } else if (strcmp($command,"GET_MONY") == 0){
        $args = decodeObject($value);
        // client 側との約束ごと
        $entryDate = $args['entryDate'];
        
        $sql = "SELECT * FROM `MoneyTable` 
        WHERE `entryDate`='$entryDate'
        ORDER BY `entryDate` DESC";
        $result=mysql_query($sql);
        echo "$sql \n";//###
        
        $items = array();
        if ($row=mysql_fetch_array($result)){
            $items['entryDate'] = $row['entryDate'];
            $items['value1'] = $row['value1'];
            $items['value2'] = $row['value2'];
            $items['staff1'] = $row['staff1'];
            $items['staff2'] = $row['staff2'];
            $items['income'] = $row['income'];
            $items['payment'] = $row['payment'];
            $items['memo'] = $row['memo'];
            $items['updateTime'] = $row['updateTime'];
        }
        
        echo "<SEPARATOR>";
        echo json_encode($items);
    } else if (strcmp($command,"PUT_STAFFS") == 0){
        $args = decodeObject($value);
        // client 側との約束ごと
        $value1 = "STAFF_LIST";
        $value2 = $args['staffs'];

        $sql = "SELECT * FROM `MoneyTable` WHERE `value1`='$value1'";
        echo "$sql -----\n"; //##
        $result=mysql_query($sql);
        
        if ($row=mysql_fetch_array($result)){
            $sql = "UPDATE `MoneyTable` 
            SET `value2`='$value2'
            WHERE `value1`='$value1'";
        } else {
            $sql = "INSERT INTO `MoneyTable`
            (`value1`,`value2`) 
            VALUES ('$value1','$value2')";
        }
        echo "----- $sql \n"; //##
		mysql_query($sql);
        $staffs = getStaffs();
        echo "<SEPARATOR>";
        echo $staffs;
    } else {
        echo "*** Unknown command: $command ***";
    }
    
?>
