Node-Red + SQLite (成績系統)

 Node-Red + SQLite (成績系統)

[{"id":"01709f95d77b4074","type":"inject","z":"4385b784d1b8f58f","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE Score (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT , chinese NUMERIC,  mathematics  NUMERIC,  english NUMERIC,  total NUMERIC, average NUMERIC , rank TEXT)","payload":"","payloadType":"date","x":220,"y":80,"wires":[["d46f5a111914eb18"]]},{"id":"5f3d6d4c8eb41c15","type":"inject","z":"4385b784d1b8f58f","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO Score (name ,  chinese,  mathematics ,  english )values(\"黃一誠\", 95 , 98, 92) ","payload":"","payloadType":"date","x":220,"y":120,"wires":[["d46f5a111914eb18"]]},{"id":"a776b24f756029c4","type":"inject","z":"4385b784d1b8f58f","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM Score","payload":"","payloadType":"date","x":220,"y":160,"wires":[["d46f5a111914eb18"]]},{"id":"87251b3b057665ed","type":"inject","z":"4385b784d1b8f58f","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from Score","payload":"","payloadType":"date","x":220,"y":200,"wires":[["d46f5a111914eb18"]]},{"id":"057101d4922a56b6","type":"inject","z":"4385b784d1b8f58f","name":"DROP TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE Score","payload":"","payloadType":"date","x":230,"y":240,"wires":[["d46f5a111914eb18"]]},{"id":"d46f5a111914eb18","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":480,"y":160,"wires":[["ae39d38c6f6a1b2d"]]},{"id":"ae39d38c6f6a1b2d","type":"debug","z":"4385b784d1b8f58f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":650,"y":160,"wires":[]},{"id":"6e26808bda8d42d2","type":"ui_form","z":"4385b784d1b8f58f","name":"","label":"成績  輸入暨更正 ","group":"94d57bc41398b0c3","order":1,"width":6,"height":1,"options":[{"label":"姓名","value":"Name","type":"text","required":true,"rows":null},{"label":"國文","value":"chinese","type":"number","required":true,"rows":null},{"label":"數學","value":"mathematics","type":"number","required":true,"rows":null},{"label":"英文","value":"english","type":"number","required":true,"rows":null}],"formValue":{"Name":"","chinese":"","mathematics":"","english":""},"payload":"","submit":"Submit","cancel":"Cancle","topic":"Form","topicType":"str","splitLayout":false,"className":"","x":120,"y":440,"wires":[["e1e6864d4d1728e1","91f54dbbddcc6f72"]]},{"id":"e1e6864d4d1728e1","type":"debug","z":"4385b784d1b8f58f","name":"debug 195","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":250,"y":500,"wires":[]},{"id":"91f54dbbddcc6f72","type":"function","z":"4385b784d1b8f58f","name":"計算 function ","func":"var name = msg.payload.Name;\nvar chin = msg.payload.chinese;\nvar math = msg.payload.mathematics;\nvar engl = msg.payload.english;\nvar tot= chin+ math +engl ;\nvar avg1= tot/3 ;\nvar avg=avg1.toFixed(2);\n\nmsg.payload={\n    Name:name ,\n    chinese: chin ,\n    mathematics : math ,\n    english : engl ,\n    total : tot ,\n    average : avg\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":440,"wires":[["a716b28db713c726","c965610fb33758da"]]},{"id":"a716b28db713c726","type":"debug","z":"4385b784d1b8f58f","name":"debug 196","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":500,"wires":[]},{"id":"ba28d79a1767ca31","type":"function","z":"4385b784d1b8f58f","name":"INSERT","func":"var name = msg.payload.Name;\nvar chin = msg.payload.chinese;\nvar math = msg.payload.mathematics;\nvar engl = msg.payload.english;\nvar tot= msg.payload.total;\nvar avg= msg.payload.average;\n\nmsg.topic = \"INSERT INTO Score (name,chinese,mathematics,english,total,average) VALUES ($name, $chin , $math , $engl , $tot , $avg)\" ;\nmsg.payload = [name, chin , math , engl , tot , avg]\nreturn msg;\n\n\n//CREATE TABLE Score \n//(id INTEGER PRIMARY KEY AUTOINCREMENT, \n//name TEXT , \n//chinese NUMERIC, \n//mathematics  NUMERIC,  \n//english NUMERIC,  \n//total NUMERIC, \n//average NUMERIC , \n//rank TEXT)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":480,"wires":[["bca28a42a8cf456c","d64b6dc6bd7e9b21"]]},{"id":"bca28a42a8cf456c","type":"debug","z":"4385b784d1b8f58f","name":"debug 197","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":950,"y":520,"wires":[]},{"id":"cbd569ca9d4fde8f","type":"comment","z":"4385b784d1b8f58f","name":"","info":"CREATE TABLE Score \n(id INTEGER PRIMARY KEY AUTOINCREMENT, \nname TEXT , \nchinese NUMERIC, \nmathematics  NUMERIC,  \nenglish NUMERIC,  \ntotal NUMERIC, \naverage NUMERIC , \nrank TEXT)","x":490,"y":660,"wires":[]},{"id":"7e23e443310bd97b","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"2777f0b548157a5a","order":1,"width":14,"height":1,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":600,"wires":[["a85946e1b419c15c","f7df5fd6adba9af0","3ad6543a5794e0ac"]]},{"id":"f7df5fd6adba9af0","type":"ui_toast","z":"4385b784d1b8f58f","position":"top right","displayTime":"3","highlight":"","sendall":true,"outputs":0,"ok":"OK","cancel":"Cancel","raw":false,"className":"","topic":"","name":"","x":330,"y":560,"wires":[]},{"id":"a85946e1b419c15c","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"2777f0b548157a5a","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":300,"y":640,"wires":[]},{"id":"3ad6543a5794e0ac","type":"function","z":"4385b784d1b8f58f","name":"檢視資料","func":"msg.topic = \"SELECT * FROM  Score \";\nreturn msg;\n\n//CREATE TABLE Score \n//(id INTEGER PRIMARY KEY AUTOINCREMENT, \n//name TEXT , \n//chinese NUMERIC, \n//mathematics  NUMERIC,  \n//english NUMERIC,  \n//total NUMERIC, ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":600,"wires":[["9b876e797a7a0229"]]},{"id":"6672fc3a3b0c1882","type":"debug","z":"4385b784d1b8f58f","name":"debug 198","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":600,"wires":[]},{"id":"325109e22afe9f80","type":"ui_table","z":"4385b784d1b8f58f","group":"2777f0b548157a5a","name":"Query","order":3,"width":14,"height":10,"columns":[{"field":"id","title":"座號","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"name","title":"姓名","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"chinese","title":"國文","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"mathematics","title":"數學","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"english","title":"英文","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"total","title":"總分","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"average","title":"平均","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"rank","title":"名次","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":670,"y":640,"wires":[[]]},{"id":"9b876e797a7a0229","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":460,"y":600,"wires":[["6672fc3a3b0c1882","325109e22afe9f80"]]},{"id":"c551c39408ef23ed","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":5,"width":3,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":720,"wires":[["c67f176cdae10630","54d483908c44e551"]]},{"id":"54d483908c44e551","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"2777f0b548157a5a","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":720,"wires":[]},{"id":"c67f176cdae10630","type":"function","z":"4385b784d1b8f58f","name":"CREATE　DATABASE","func":"\n\n//CREATE TABLE Score \n//(id INTEGER PRIMARY KEY AUTOINCREMENT, \n//name TEXT , chinese NUMERIC,  \n//mathematics  NUMERIC,  \n//english NUMERIC,  \n//total NUMERIC, \n//average NUMERIC , \n//rank TEXT)\nmsg.topic = \"CREATE TABLE Score (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT , chinese NUMERIC,  mathematics  NUMERIC,  english NUMERIC,  total NUMERIC, average NUMERIC , rank TEXT)\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":760,"wires":[["4c2be8005e67188a"]]},{"id":"4c2be8005e67188a","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":540,"y":760,"wires":[["a7ae56e4ebacc246"]]},{"id":"a7ae56e4ebacc246","type":"debug","z":"4385b784d1b8f58f","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":760,"wires":[]},{"id":"a2cde1a13fbb4d4b","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":7,"width":"3","height":1,"passthru":false,"label":"刪除所有資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":840,"wires":[["61b0a4ca6eeef6d4","3056687d45a913a7"]]},{"id":"5e64a0fa150a08e9","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":6,"width":3,"height":1,"passthru":false,"label":"刪除資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":960,"wires":[["cebf820a942a0f51","c84861b0eb14a49a"]]},{"id":"61b0a4ca6eeef6d4","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":840,"wires":[]},{"id":"3056687d45a913a7","type":"ui_toast","z":"4385b784d1b8f58f","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":270,"y":880,"wires":[["b625abe1aaa31f4d"]]},{"id":"c84861b0eb14a49a","type":"ui_toast","z":"4385b784d1b8f58f","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":290,"y":960,"wires":[["e22233f5fbc0151f"]]},{"id":"cebf820a942a0f51","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":1000,"wires":[]},{"id":"e22233f5fbc0151f","type":"function","z":"4385b784d1b8f58f","name":"function 刪除資料庫","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":470,"y":960,"wires":[["e43fbe399ab94c7a"],[]]},{"id":"b625abe1aaa31f4d","type":"function","z":"4385b784d1b8f58f","name":"function 刪除所有資料","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":460,"y":880,"wires":[["83caf860af9c609c"],[]]},{"id":"83caf860af9c609c","type":"function","z":"4385b784d1b8f58f","name":"刪除所有資料","func":"//DELETE from RFIDtable\nmsg.topic = \"DELETE from Score\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":840,"wires":[["fc3ee699d1bd3cb8"]]},{"id":"e43fbe399ab94c7a","type":"function","z":"4385b784d1b8f58f","name":"刪除資料庫","func":"//DROP TABLE RFIDtable\nmsg.topic = \"DROP TABLE Score\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":570,"y":1000,"wires":[["428530237e2d5665"]]},{"id":"428530237e2d5665","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":760,"y":1000,"wires":[["f94578c3a66c21ef"]]},{"id":"f94578c3a66c21ef","type":"debug","z":"4385b784d1b8f58f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":930,"y":920,"wires":[]},{"id":"fc3ee699d1bd3cb8","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":760,"y":840,"wires":[["f94578c3a66c21ef"]]},{"id":"d64b6dc6bd7e9b21","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":960,"y":480,"wires":[[]]},{"id":"40afc15e79affc27","type":"function","z":"4385b784d1b8f58f","name":"function parameter set","func":"\nif( msg.payload === true )\n{\n   msg.payload=true ;\n   global.set('UPDATE', true);\n}\nelse\n{\n    msg.payload=false;\n    global.set('UPDATE', false);\n}   \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":360,"wires":[["b41a1ac3c19c37e6"]]},{"id":"b41a1ac3c19c37e6","type":"debug","z":"4385b784d1b8f58f","name":"debug 203","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":360,"wires":[]},{"id":"602b69b0201a1981","type":"inject","z":"4385b784d1b8f58f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":300,"wires":[["0341110be8b44b73"]]},{"id":"0341110be8b44b73","type":"function","z":"4385b784d1b8f58f","name":"function flow set","func":"\nglobal.set('UPDATE', false);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":300,"wires":[["62514f504af2419f"]]},{"id":"62514f504af2419f","type":"debug","z":"4385b784d1b8f58f","name":"debug 204","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":450,"y":300,"wires":[]},{"id":"c965610fb33758da","type":"function","z":"4385b784d1b8f58f","name":"function UPDATE?","func":"var test=global.get(\"UPDATE\");\n\nif (test==true){\n    return [msg,null];\n    \n}\nif (test==false){\n    return [null,msg];\n    \n}\n\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":440,"wires":[["5e510264b78e9918","301e730dfb315e3a"],["ba28d79a1767ca31"]]},{"id":"5e510264b78e9918","type":"function","z":"4385b784d1b8f58f","name":"UPDATE","func":"var name = msg.payload.Name;\nvar chin = msg.payload.chinese;\nvar math = msg.payload.mathematics;\nvar engl = msg.payload.english;\nvar tot= msg.payload.total;\nvar avg= msg.payload.average;\n\n//let temp= \"update Score\" ;\n//temp=temp+ \" set (chinese,mathematics,english,total,average) values ( \" + \"\\'\" + chin + \"\\'\" + math + \"\\'\" +engl+ \"\\'\" + tot + \"\\'\" + avg + \"\\'\"\n//temp=temp+ \" where name VALUES (\" + \"\\'\" + name+ \")\";\n//msg.topic= temp;\n//msg.params = {$chinese:chin ,$mathematics: math , $english: engl , $total: tot , $average:avg , $name:name};\n//msg.payload=\"\";\nglobal.set('UPDATE', false);\n\nlet temp=\"update Score set\";\ntemp=temp+\"  chinese= \" + chin ;\ntemp=temp+\", mathematics= \"+ math ;\ntemp=temp+\", english= \" + engl ;\ntemp=temp+\", total= \" + tot ;\ntemp=temp+\", average= \" + avg ;\ntemp=temp+\" where name= '\"+ name +\"'\";\nmsg.topic=temp;\nreturn msg;\n\n//update Score set chinese=33 , mathematics=33,  english =44,  total = 110,  average = 34 where name = 'alex3';\n\n\n\n//CREATE TABLE Score \n//(id INTEGER PRIMARY KEY AUTOINCREMENT, \n//name TEXT , \n//chinese NUMERIC, \n//mathematics  NUMERIC,  \n//english NUMERIC,  \n//total NUMERIC, \n//average NUMERIC , \n//rank TEXT)\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":780,"y":360,"wires":[["c687b7cb05fe39cf","3c5f45c7804e707e"]]},{"id":"714f7d81e55603de","type":"debug","z":"4385b784d1b8f58f","name":"debug 205","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"params","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":280,"wires":[]},{"id":"c687b7cb05fe39cf","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"UPDATE Score SET (chinese,mathematics,english,total,average) WHERE (name=) values ($chinese,$mathematics,$english,$total,$average , $name) \n","name":"SQLite_SCORE","x":960,"y":360,"wires":[[]]},{"id":"301e730dfb315e3a","type":"function","z":"4385b784d1b8f58f","name":"function 84","func":"//UPDATE Score SET (chinese,\n//mathematics,\n//english,\n//total,\n//average) \n//VALUES ( $chin , $math , $engl , $tot , $avg) \n//WHERE (name) VALUE ($name)\n\nvar name1 = msg.payload.Name;\nvar chin = msg.payload.chinese;\nvar math = msg.payload.mathematics;\nvar engl = msg.payload.english;\nvar tot= msg.payload.total;\nvar avg= msg.payload.average;\n\n// Create a Params variable\n// with a time and value component\n//\nmsg.params ={\n    $chinese: chin ,\n    $mathematics:math , \n    $english:engl , \n    $total : tot , \n    $average :avg, \n    $name: name1\n    }\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":750,"y":300,"wires":[["714f7d81e55603de"]]},{"id":"7197011702f8c13e","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"2777f0b548157a5a","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":420,"y":400,"wires":[]},{"id":"b537295f9e560bbf","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":10,"width":"3","height":"1","passthru":false,"label":"更正資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"true","payloadType":"bool","topic":"topic","topicType":"msg","x":110,"y":360,"wires":[["40afc15e79affc27","ba2b52edfcd9a802"]]},{"id":"ba2b52edfcd9a802","type":"function","z":"4385b784d1b8f58f","name":"function 85","func":"msg.payload=\"更正資料庫\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":400,"wires":[["7197011702f8c13e"]]},{"id":"3c5f45c7804e707e","type":"debug","z":"4385b784d1b8f58f","name":"debug 207","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":950,"y":320,"wires":[]},{"id":"098881a2d8f69220","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":7,"width":"3","height":1,"passthru":false,"label":"刪除一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":1080,"wires":[["1337323819601e29","8d31e4313e6f8b74"]]},{"id":"1337323819601e29","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":280,"y":1100,"wires":[]},{"id":"8d31e4313e6f8b74","type":"function","z":"4385b784d1b8f58f","name":"function 刪除一筆資料","func":"global.set(\"dorq\",1);\nvar topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":1040,"wires":[["818ba9411b22f8a7"],[]]},{"id":"2db56226f9a3b0ea","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":980,"y":1040,"wires":[[]]},{"id":"6589915c40f6efe5","type":"function","z":"4385b784d1b8f58f","name":"刪除資料庫","func":"//\nvar name = msg.payload.Name;\nvar yesorno=global.get(\"dorq\");\nmsg.topic=\"\";\n\nif ( yesorno==1) {\n    let temp =\"DELETE FROM Score\";\n    temp=temp+\" WHERE name= '\"+ name +\"'\";\n    msg.topic=temp;\n    }\nreturn msg;\n\n//DELETE FROM table_name\n//WHERE [condition];","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":810,"y":1040,"wires":[["2db56226f9a3b0ea"]]},{"id":"818ba9411b22f8a7","type":"ui_form","z":"4385b784d1b8f58f","name":"","label":"成績  輸入姓名 刪除(查詢)一筆","group":"94d57bc41398b0c3","order":1,"width":6,"height":1,"options":[{"label":"姓名","value":"Name","type":"text","required":true,"rows":null}],"formValue":{"Name":""},"payload":"","submit":"Submit","cancel":"Cancle","topic":"Form","topicType":"str","splitLayout":false,"className":"","x":550,"y":1100,"wires":[["6589915c40f6efe5","f25e9678e087b605","cb2b360c47844fa7"]]},{"id":"f25e9678e087b605","type":"debug","z":"4385b784d1b8f58f","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":1080,"wires":[]},{"id":"da638b6ae1a5d0d9","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":7,"width":"3","height":1,"passthru":false,"label":"查詢一筆資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"查詢一筆資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":1160,"wires":[["7e05f0fe0bb39f6f","46ca3a78c2ca0997"]]},{"id":"46ca3a78c2ca0997","type":"function","z":"4385b784d1b8f58f","name":"function 查詢一筆資料","func":"global.set(\"dorq\",2);\nvar topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":1160,"wires":[["818ba9411b22f8a7"],[]]},{"id":"7e05f0fe0bb39f6f","type":"ui_audio","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":300,"y":1200,"wires":[]},{"id":"cb2b360c47844fa7","type":"function","z":"4385b784d1b8f58f","name":"查詢資料庫","func":"//\nvar name = msg.payload.Name;\nvar yesorno=global.get(\"dorq\");\nmsg.topic=\"\";\n\nif ( yesorno==2) {\n    let temp =\"SELECT * FROM Score\";\n    temp=temp+\" WHERE name LIKE '\"+ name +\"'\";\n    msg.topic=temp;\n}\nreturn msg;\n\n//SELECT * FROM COMPANY WHERE AGE  LIKE 'XXX%';\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":810,"y":1120,"wires":[["5125fd78beb36024","ebef49ba25faae38"]]},{"id":"ebef49ba25faae38","type":"debug","z":"4385b784d1b8f58f","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":990,"y":1120,"wires":[]},{"id":"5125fd78beb36024","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":900,"y":1200,"wires":[["45b407b8fc4427ab","cb7f36eead0ba341"]]},{"id":"45b407b8fc4427ab","type":"link out","z":"4385b784d1b8f58f","name":"link out 38","mode":"link","links":["b2459a1e26efe0a6"],"x":1065,"y":1200,"wires":[]},{"id":"b2459a1e26efe0a6","type":"link in","z":"4385b784d1b8f58f","name":"link in 35","links":["45b407b8fc4427ab"],"x":555,"y":680,"wires":[["325109e22afe9f80"]]},{"id":"60f09e1e0aec28f4","type":"ui_button","z":"4385b784d1b8f58f","name":"","group":"11d8514.a44dcaf","order":7,"width":"3","height":1,"passthru":false,"label":"排序成績資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"排序成績資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":1240,"wires":[["7e05f0fe0bb39f6f","25d45aef786b3888"]]},{"id":"25d45aef786b3888","type":"function","z":"4385b784d1b8f58f","name":"查詢資料庫","func":"//\nmsg.topic=\"\";\n\nlet temp =\"SELECT * FROM Score\";\ntemp=temp+\" ORDER BY average DESC\" ;\nmsg.topic=temp;\n\nreturn msg;\n\n//SELECT\n//   select_list\n//FROM\n//   table\n//ORDER BY\n//    column_1 ASC,\n//    column_2 DESC;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":1240,"wires":[["e7268a28d4efc508","04042a06f31ab477"]]},{"id":"cb7f36eead0ba341","type":"debug","z":"4385b784d1b8f58f","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1050,"y":1160,"wires":[]},{"id":"e7268a28d4efc508","type":"debug","z":"4385b784d1b8f58f","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":1220,"wires":[]},{"id":"587ba0aca441a06a","type":"function","z":"4385b784d1b8f58f","name":"function 排序","func":"var len=msg.payload.length;\n\nfor (let i=0;i<len;i++)\n { \n    msg.payload[i].rank=i+1;\n\n }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":1260,"wires":[["45b407b8fc4427ab","f873a54485259bc0"]]},{"id":"04042a06f31ab477","type":"sqlite","z":"4385b784d1b8f58f","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite_SCORE","x":480,"y":1260,"wires":[["587ba0aca441a06a"]]},{"id":"f873a54485259bc0","type":"debug","z":"4385b784d1b8f58f","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":850,"y":1280,"wires":[]},{"id":"4e71ffcc.32ba8","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\2023score.db","mode":"RWC"},{"id":"94d57bc41398b0c3","type":"ui_group","name":"輸入 更正","tab":"12c783f18313f3f2","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"2777f0b548157a5a","type":"ui_group","name":"查詢","tab":"12c783f18313f3f2","order":3,"disp":true,"width":"14","collapse":false,"className":""},{"id":"11d8514.a44dcaf","type":"ui_group","name":"顯示","tab":"12c783f18313f3f2","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"12c783f18313f3f2","type":"ui_tab","name":"SCORE","icon":"dashboard","disabled":false,"hidden":false}]