const { parse } = require('dotenv');
var mysql = require('mysql');

var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',    
    password : '',
    database : 'imeetu'   
    });
connection.connect();
function upMeetingDate(Type,UID,MEETING_ID){
    connection.query(`INSERT INTO \`update\`(UPDATE_UID,UPDATE_TYPE,UPDATE_TIME,UPDATE_MID) VALUES (${UID},'${Type}',NOW(),${MEETING_ID})`, function (error, results, fields) {
        if (error) throw error;
       //console.log(results)
       else {
        console.log('Update紀錄新增成功')
       }
      });
}

//查看記錄總數量
exports.selectCountAllMeeting=function (){
    connection.query(`select* from meeting_records`, function (error, results, fields) {
        if (error) throw error;
       //console.log(results)
       else {
        console.log(results.length)
        return (results.length)
       }
      });
    
}
//新增使用者
exports.addUser=function(Name,ID,PWD){
    connection.query(`INSERT INTO user( USER_NAME, USER_USERNAME, USER_PASSWORD,USER_UPdataTime,USER_Delete) VALUES ('${Name}','${ID}','${PWD}',NOW(),'1')`, function (error, results, fields) {
        if (error) throw error;
       //console.log(results)
       else {
        console.log('新增成功')
       }
      });
}

//刪除使用者
exports.DropUser=function(Name){
    if(Name!='master'){
        connection.query(`UPDATE 'user' SET USER_Delete='0' WHERE USER_NAME='${Name}'`, function (error, results, fields) {
            if (error) throw error;
           //console.log(results)
            else {
                console.log(Name+'刪除成功')
                return Name+'刪除成功'
            }
    })
    }
    else if(Name=='master'){
        return '不可以刪除管理者'
    }
}

//修改使用者密碼
exports.UpdateUserPWD=function(UserID,UserNewPWD){
    connection.query(`UPDATE 'user' SET USER_ID='${UserNewPWD}' WHERE UserID='${UserID}'`, function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            return '更新成功'
        }
})
}

//讀取UserPWD
exports.UpdateUser=function(UserID){
    connection.query(`select USER_USER_PASSWORD from user WHERE USER_ID='${UserID}'`, function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            return results
        }
})
}

//增加會議記錄
exports.addmeeting=function(MEETING_TITLE, MEETING_DATE, MEETING_DATE_am,MEETING_DATE_H,MEETING_VENUE, MEETING_CHARPERSON, MEETING_PARTTICIPANT, MEETING_MINUTE, MEETING_Doc_String,MEETING_Doc_BLOB, MEETING_ATTACH, MEETING_TAGS){
    connection.query(`INSERT INTO meeting_records(MEETING_TITLE, MEETING_DATE,MEETING_DATE_apm,MEETING_DATE_H,MEETING_UpDateTIME, MEETING_VENUE, MEETING_CHARPERSON, MEETING_PARTTICIPANT, MEETING_MINUTE, MEETING_DOC_String,MEETING_DOC_BLOB, MEETING_ATTACH_NAME, MEETING_TAGS,MEETING_Delete) 
    VALUES ('${MEETING_TITLE}', '${MEETING_DATE}','${MEETING_DATE_am}','${MEETING_DATE_H}',NOW(), '${MEETING_VENUE}', '${MEETING_CHARPERSON}', '${MEETING_PARTTICIPANT}', '${MEETING_MINUTE}', '${MEETING_Doc_String}','${MEETING_Doc_BLOB}', '${MEETING_ATTACH}', '${MEETING_TAGS}','1')
    `, function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            console.log(MEETING_TITLE+'新增成功')
            return MEETING_TITLE+'新增成功'
        }
})
}


//刪除會議記錄
exports.deletemeeting=function(UID,MEETING_ID){
    upMeetingDate('刪除',UID,MEETING_ID)
    connection.query(`UPDATE meeting_records SET MEETING_Delete=0,MEETING_UpDateTIME=NOW() WHERE MEETING_ID=${MEETING_ID}`, function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            console.log('刪除成功')
            return '刪除成功'
        }
})
}

//查詢會議紀錄
exports.FindMeeting=function (){
    var FirstTime = document.getElementById("Find_FirstTime");
    var LastTime = document.getElementById("Find_LastTime");
    var CHARPERSON = document.getElementById("Find_CHARPERSON");
    var MINUTE=document.getElementById("Find_MINUTE");
    var String = document.getElementById("Find_String");
    var Tage = document.getElementById("Find_Tage");
   //判斷接收值是否為空值，若有值則累加SQL語法
    if (CHARPERSON != null && CHARPERSON != '') {
        CHARPERSON=`and MEETING_CHARPERSON='${CHARPERSON}'`
    }
    else{
        CHARPERSON=''
    }
    if (MINUTE != null && MINUTE != '') {
        MINUTE=`and MEETING_MINUTE='${MINUTE}'`
    }
    else{
        MINUTE=''
    }
    if (String != null && String != '') {
        String=`and MEETING_Doc_String like'%${String}%'`
    }
    else{
        String=''
    }
    if (Tage != null && Tage != '') {
        Tage=`and MEETING_TAGS like'%${Tage}%'`
    }
    else{
        Tage=''
    }    
    connection.query(`select * from meeting_records where MEETING_DATE BETWEEN '${FirstTime}' AND '${LastTime}' ${CHARPERSON} ${MINUTE} ${String} ${Tage}`,function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            console.log(FirstTime)
            console.log(LastTime)
            console.log(CHARPERSON)
            console.log(MINUTE)
            console.log(String)
            console.log(Tage)
            if(results==''){
                console.log('無資料')
                return '無資料'
            }
            else {
                console.log(results)
                return results
            }            
            }
        });
}


//查詢時間區間
exports.Inquiremeeting=function(firstTime,lastTime){
    connection.query(`SELECT * FROM meeting_records WHERE MEETING_DATE>='${firstTime}' AND MEETING_DATE<'${lastTime}'`,function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            return results
            }
        });
}


//會議記錄顯示
exports.SelectAllMeeting=function(){
    connection.query(`select MEETING_ID,MEETING_TITLE,MEETING_DATE,MEETING_CHARPERSON, MEETING_MINUTE from meeting_records order by 'MEETING_ID'`,function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            console.log(results)
            return results
            }
        });
}

//輸入頁數回傳JSON
exports.SelectPageMeeting=function(page){
    var meeting=SelectAllMeeting()
    var meetingLeing=meeting.length
    //第一頁第一篇=page
    var meeting=2
    return
}

//中文數字轉阿拉伯數字
function zhDigitToArabic(digit) { 
    const zh = ['零', '一', '二', '三', '四', '五', '六', '七', '八', '九'];
    const unit = ['千', '百', '十']; 
    const quot = ['萬', '億', '兆', '京', '垓', '秭', '穰', '溝', '澗', '正', '載', '極', '恆河沙', '阿僧祗', '那由他', '不可思議', '無量', '大數']; 
    let result = 0, quotFlag; 
    for (let i = digit.length - 1; i >= 0; i--) 
    { 
        if (zh.indexOf(digit[i]) > -1) { 
            // 數字 
            if (quotFlag) { 
                result += quotFlag * getNumber(digit[i]);
            } else {
                result += getNumber(digit[i]); 
            } 
            } else if (unit.indexOf(digit[i]) > -1) { 
                // 十分位 
                if (quotFlag) {
                    result += quotFlag * getUnit(digit[i]) * getNumber(digit[i - 1]); 
                } else { 
                    result += getUnit(digit[i]) * getNumber(digit[i - 1]);
                } --i; 
            } else if (quot.indexOf(digit[i]) > -1) { 
                // 萬分位
                if (unit.indexOf(digit[i - 1]) > -1) {
                    if (getNumber(digit[i - 1])) {
                        result += getQuot(digit[i]) * getNumber(digit[i - 1]); 
                    } else { 
                        result += getQuot(digit[i]) * getUnit(digit[i - 1]) * getNumber(digit[i - 2]); 
                        quotFlag = getQuot(digit[i]); --i; 
                    } 
                } else {
                    result += getQuot(digit[i]) * getNumber(digit[i - 1]); 
                    quotFlag = getQuot(digit[i]);
                } --i;
                } 
                } return result;
                // 返回中文大寫數字對應的阿拉伯數字 
                function getNumber(num) { 
                    for (let i = 0; i < zh.length; i++) {
                        if (zh[i] == num) {
                            return i;
                        } 
                    } 
                } // 取單位 
                function getUnit(num) { 
                    for (let i = unit.length; i > 0; i--) { 
                        if (num == unit[i - 1]) { 
                            return Math.pow (10, 4 - i); 
                        } 
                    } 
                } // 取分段 
                function getQuot(q) { 
                    for (var i = 0; i < quot.length; i++) {
                        if (q == quot[i]) { return Math.pow(10, (i + 1) * 4); 
                        } 
                    } 
                } 
            } 



//阿拉伯轉中文
function chineseNumber(alaberNumber) {
    var chineseNumber = ("零一二三四五六七八九").split('');
    var amountSmallUnit = ['', '十', '百', '千'];
    var amountBigUnit = ['', '萬', '億', '兆', '京', '垓', '秭', '穰', '溝', '澗', '正', '載'];
    var alaberNumberSplit = new Array();
    var alaberNumberSplitCount = parseInt(alaberNumber.length / 4);
    
    for (var i = 0; i <= alaberNumberSplitCount; i++) {
        if (i == 0 && alaberNumber.length % 4 != 0) {
            alaberNumberSplit[i] = alaberNumber.substr(0, alaberNumber.length % 4);
            alaberNumber = alaberNumber.slice(alaberNumber.length % 4);
        } else if (alaberNumber != '') {
            alaberNumberSplit[i] = alaberNumber.substr(0, 4);
            alaberNumber = alaberNumber.slice(4);
        }
    }
    
    var chineseBigNumber = '';
    
    for (var i = 0; i < alaberNumberSplit.length; i++) {
        for (var j = 0; j < alaberNumberSplit[i].length; j++) {
            if (alaberNumberSplit[i][0] != 0 && j == 1 
                && alaberNumberSplit[i][j] == 0 
                && alaberNumberSplit[i].length == 4 
                && alaberNumberSplit[i][2] != 0) {
                chineseBigNumber += chineseNumber[alaberNumberSplit[i][j]];
            } else if (alaberNumberSplit[i][j] != 0) {
                chineseBigNumber += chineseNumber[alaberNumberSplit[i][j]];
                chineseBigNumber += amountSmallUnit[alaberNumberSplit[i].length - 1 - j];
            } else if (alaberNumberSplit[i][j] == 0 && alaberNumberSplit[i][j - 1] != 0) {
                if (alaberNumberSplit[i][alaberNumberSplit[i].length - 1] != 0) {
                    chineseBigNumber += chineseNumber[alaberNumberSplit[i][j]];
                }
            }
        }
        if (parseInt(alaberNumberSplit[i]) != 0) {
            chineseBigNumber += amountBigUnit[alaberNumberSplit.length - 1 - i];
        }
    }

    if (chineseBigNumber != '') {
        /* chineseBigNumber += '元整'; */
        chineseBigNumber = '' + chineseBigNumber + '';
    }
    return chineseBigNumber;
}


//讀取會議記錄標題次數數字
exports.SelectMeetingSum=function(meetID){

    
    connection.query(`select MEETING_TITLE from meeting_records where MEETING_ID=${meetID}`,function (error, results, fields) {
        if (error) throw error;
        //console.log(results)
        else {
            //讀取SQL 轉為字串
            jsonStr=JSON.stringify(results)
            plainObj=JSON.parse(jsonStr)[0].MEETING_TITLE
            console.log(plainObj)
            var firststr
            var last
            //抓取'('與')'在哪邊
            for(var i=0;i<=plainObj.length;i++){
                if(plainObj[i]=='（'||plainObj[i]=='('){
                    firststr=i+1
                }
                if(plainObj[i]=='）'||plainObj[i]==')'){
                    last=i
                }
            }
            //str->存入中文
            var strNum=plainObj.substring(last,firststr)
            //過濾掉'第'、'次'
            strNum=strNum.replace('第','')
            strNum=strNum.replace('次','')
            }

            //判斷是中文還是數字
            var pattern1 = new RegExp(`[\u4E00-\u9FA5]+`);
            var pattern2 = new RegExp("[0-9]+");
            //提供回傳直
            var returnstr
            //中文
            if(pattern1.test(strNum)){
                returnstr=zhDigitToArabic(strNum)
                console.log(returnstr)
            }
            //數字
            else if(pattern2.test(strNum)){
                returnstr=chineseNumber(strNum)
                console.log(returnstr)
            }
            //回傳國字或數字
            return returnstr
        });
}



