CREATE TABLE WA001_TODO_DETAIL(
DB_INS_TMSP TEXT
, DB_UPD_TMSP TEXT
, DB_UPD_CNT INTEGER
, DB_DEL_TMSP TEXT
, NODE INTEGER
, WID INTEGER PRIMARY KEY AUTOINCREMENT
, TITLE TEXT
, STATUS TEXT
, MEMO TEXT
)
<style>
li.aitem span{
display: inline;
margin:20px 5px;
text-align: left;
}
li.aitem:hover{
background-color:#CEE;
}
li.citem span{
display: inline;
margin:20px 5px;
}
div.flexBox{
display:flex;
}
div.flexBox ul{
text-align: left;
font-size: 1.5em;
}
li.todoli-0{
list-style-type:"🔜";
}
li.todoli-1{
list-style-type:"💯";
}
li.todoli-2{
list-style-type:"🚧";
}
li.todoli-3{
list-style-type:"⛓️💥";
}
.tooltip {
font-size: 1.5em;
position: absolute;
background-color: #ADD;
color: #333;
text-align: center;
border-radius: 5px;
padding: 5px;
z-index: 1;
visibility: hidden;
opacity: 0;
transition: opacity 0.3s;
}
.tooltip.visible {
visibility: visible;
opacity: 1;
}
.modal {
--display: none; /* Hidden by default */
position: fixed; /* Stay in place */
z-index: 1; /* Sit on top */
left: 0;
top: 0;
width: 100%; /* Full width */
height: 100%; /* Full height */
overflow: auto; /* Enable scroll if needed */
background-color: rgb(0,0,0); /* Fallback color */
background-color: rgba(0,0,0,0.4); /* Black w/ opacity */
}
.modal-content {
background-color: #fefefe;
margin: 15% auto; /* 15% from the top and centered */
padding: 20px;
border: 1px solid #888;
width: 40%; /* Could be more or less, depending on screen size */
}
.close,.save {
color: #aaa;
float: right;
font-size: 28px;
font-weight: bold;
}
.close:hover,
.close:focus,.save:hover,.save:focus {
color: black;
text-decoration: none;
cursor: pointer;
}
input.mdtitle{
display: inline;
width: 80%;
}
input.mdstatus{
width: 5%;
display: none;
}
textarea.mdmemo{
width: 85%;
min-height: 8em;
}
li span.tmsp{
color:#aaa;
font-size:medium;
}
</style>
<template>
<h2><a href="https://to-do.office.com/tasks/inbox" target="_blank">📐Todo</a></h2>
<div class="flexBox">
<ul>
<li class="aitem" @click="openEmptyModal">new task</li>
<li class="aitem" :class="statusMapping(it.STATUS)" v-for="it in items" @contextmenu.prevent="showTooltip($event,it.NODE)">
<span class="title" > {{it.TITLE}} </span> <span class="tmsp" >({{it.DB_INS_TMSP}})</span>
<ul class="children" v-show="it.children">
<li class="citem" v-for="it in it.children">
<span class="status">c-{{it.STATUS}}</span>
<span class="title" >c-{{it.TITLE}}</span>
</li>
</ul>
</li>
</ul>
</div>
<div class="flexBox">
<ul style="height: 150px;overflow-y: scroll;">
<li class="aitem" :class="statusMapping(it.STATUS)" v-for="it in items_arch" @contextmenu.prevent="showTooltip($event,it.NODE)">
<span class="title" > {{it.TITLE}} </span> <span class="tmsp" >({{it.DB_INS_TMSP}})</span>
<ul class="children" v-show="it.children">
<li class="citem" v-for="it in it.children">
<span class="status">c-{{it.STATUS}}</span>
<span class="title" >c-{{it.TITLE}}</span>
</li>
</ul>
</li>
</ul>
</div>
<div class="tooltip" :class="{ visible: isTooltipVisible }" :style="{ top: tooltipY + 'px', left: tooltipX + 'px' }">
<span class="doing"><li @click="setTodoStatus(0)" class="todoli-0">Start</li></span>
<span class="finish"><li @click="setTodoStatus(1)" class="todoli-1">Finish</li></span>
<span class="postpone"><li @click="setTodoStatus(2)" class="todoli-2">Pause</li></span>
<span class="cancel"><li @click="setTodoStatus(3)" class="todoli-3">Cancel</li></span>
<span class="doing"><li @click="openModal">Detail</li></span>
</div>
<div class="modal" v-show="isModalVisible">
<div class="modal-content">
<span class="close" @click="closeModal">×</span>
<span class="save" @click="saveModal">©</span>
<div> <input class="mdtitle" v-model="modelCtx.TITLE" /> <input class="mdstatus" v-model="modelCtx.STATUS"/></div>
<br/>
<div> <textarea class="mdmemo" v-model="modelCtx.MEMO"></textarea></div>
</div>
</div>
</template>
<script setup>
import { ref, onMounted } from 'vue'
const items = ref([])
const items_arch = ref([])
onMounted(async () => {
await fetchHeadData()
})
const fetchHeadData = async() => {
const res = await fetch('http://localhost:3000/todo/heads');
let retJson = await res.json()
items.value = retJson.filter(it=>{ return ['0','2'].indexOf(it.STATUS)>=0})
items_arch.value = retJson.filter(it=>{ return ['1','3'].indexOf(it.STATUS)>=0})
}
const statusMapping = (status) => { return "todoli-"+status; }
const isTooltipVisible= ref(false)
const tooltipX = ref(0)
const tooltipY = ref(0)
var currentNode = ref(null)
const modelCtx = ref({
TITLE:"",
STATUS:"",
MEMO:""}
)
const showTooltip = (event,node) => {
tooltipX.value = event.clientX
tooltipY.value = event.clientY
isTooltipVisible.value = true
currentNode.value = node
document.addEventListener('click', hideTooltip);
}
const hideTooltip = () => {
isTooltipVisible.value = false
document.removeEventListener('click', hideTooltip);
}
const isModalVisible =ref(false)
const openModal = async () => {
const loading= ref(true);
const error = ref(null);
const data = ref()
try {
const response = await fetch('http://localhost:3000/todo/getDetail', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({ node: currentNode.value })
});
if (!response.ok) {
throw new Error('Network response was not ok');
}
const result = await response.json();
modelCtx.value = result[0];
modelCtx.value.TITLE = items.value.filter( o=> o.NODE == currentNode.value).at(0).TITLE
} catch (err) {
error.value = 'Failed to post data';
} finally {
loading.value = false;
}
isModalVisible.value = true
}
const openEmptyModal = () => {
modelCtx.value = {
TITLE:"<===>",
STATUS:"0",
MEMO:""}
isModalVisible.value = true
currentNode.value = "C"
}
const setTodoStatus = async (i) => {
updateTodoStatus(i)
window.location.reload()
}
const updateTodoStatus = async (i) => {
const loading= ref(true);
const error = ref(null);
const data = ref({
TITLE:"",
STATUS:"",
MEMO:""}
)
data.value.STATUS = i
try {
const response = await fetch('http://localhost:3000/todo/updateStatus', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({ node: currentNode.value,
status : data.value.STATUS
})
});
if (!response.ok) {
throw new Error('Network response was not ok');
}
const result = await response.json();
} catch (err) {
error.value = 'Failed to post data';
} finally {
loading.value = false;
}
}
const saveModal = async () => {
const loading= ref(true);
const error = ref(null);
const data = ref({
TITLE:"",
STATUS:"",
MEMO:""})
data.value.TITLE = modelCtx.value.TITLE
data.value.STATUS = modelCtx.value.STATUS
data.value.MEMO = modelCtx.value.MEMO
console.log(currentNode.value)
console.log(data);
try {
const response = await fetch('http://localhost:3000/todo/updateDetail', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({ node: currentNode.value,
title : data.value.TITLE,
status : data.value.STATUS,
memo: data.value.MEMO
})
});
if (!response.ok) {
throw new Error('Network response was not ok');
}
const result = await response.json();
console.log(result);
} catch (err) {
error.value = 'Failed to post data';
} finally {
loading.value = false;
isModalVisible.value = false
window.location.reload()
}
}
const closeModal = () => {
isModalVisible.value = false
window.location.reload()
}
</script>const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;
app.use(cors());
app.use(express.json());
const db = new sqlite3.Database('./Main.db');
// データ取得API
app.get('/api/items', (req, res) => {
db.all('SELECT * FROM PT001_P_LIST_HDR', [], (err, rows) => {
if (err) {
res.status(500).json({ error: err.message });
return;
}
res.json(rows);
});
});
app.post('/getAll', (req, res) => {
db.all(`SELECT * FROM ${req.body.table} WHERE ${req.body.cond}`, [], (err, rows) => {
if (err) {
res.status(500).json({ error: err.message });
return;
}
res.json(rows);
});
});
app.get('/todo/heads', (req, res) => {
db.all(`SELECT * FROM (
SELECT NODE, TITLE, STATUS,DB_INS_TMSP,MAX(WID)
FROM WA001_TODO_DETAIL
GROUP BY NODE ORDER BY NODE DESC
) WHERE STATUS IN (0,2)
UNION ALL
SELECT * FROM (
SELECT NODE, TITLE, STATUS,DB_INS_TMSP,MAX(WID)
FROM WA001_TODO_DETAIL
GROUP BY NODE ORDER BY DB_INS_TMSP DESC
) WHERE STATUS IN (1,3) `, [], (err, rows) => {
if (err) {
res.status(500).json({ error: err.message });
return;
}
res.json(rows);
});
});
app.post('/todo/getDetail', (req, res) => {
db.all(`SELECT NODE, TITLE, STATUS, MEMO, DB_INS_TMSP, MAX(WID)
FROM WA001_TODO_DETAIL
WHERE NODE = ${req.body.node}
GROUP BY NODE `, [], (err, rows) => {
if (err) {
res.status(500).json({ error: err.message });
return;
}
res.json(rows);
});
});
app.post('/todo/updateDetail', (req, res) => {
var sql = "";
if(req.body.node == "C"){
sql = `INSERT
INTO WA001_TODO_DETAIL( DB_INS_TMSP, DB_UPD_TMSP, DB_UPD_CNT, DB_DEL_TMSP, NODE, TITLE, STATUS, MEMO)
SELECT
DATETIME('now', 'localtime') as DB_INS_TMSP
, '0001-1-1 0:00:00' as DB_UPD_TMSP
, 0 as DB_UPD_CNT
, '9999-12-31 23:59:59' as DB_DEL_TMSP
, 1 + MAX(NODE) as NODE
, '${req.body.title}' as TITLE
, '${req.body.status}' as STATUS
, '${req.body.memo}' as memo
FROM
WA001_TODO_DETAIL`;
} else {
sql = `INSERT
INTO WA001_TODO_DETAIL( DB_INS_TMSP, DB_UPD_TMSP, DB_UPD_CNT, DB_DEL_TMSP, NODE, TITLE, STATUS, MEMO)
VALUES ( DATETIME('now', 'localtime'), '0001-1-1 0:00:00', 0, '9999-12-31 23:59:59'
, '${req.body.node}'
, '${req.body.title}'
, '${req.body.status}'
, '${req.body.memo}')`;
}
db.run(sql, [],
function (err) {
if (err) {
return console.error('Insert error:', err.message);
}
console.log(`A row has been inserted with rowid ${this.lastID}`);
}
);
});
app.post('/todo/updateStatus', (req, res) => {
db.run(
`INSERT INTO WA001_TODO_DETAIL( DB_INS_TMSP, DB_UPD_TMSP, DB_UPD_CNT, DB_DEL_TMSP, NODE,TITLE, STATUS, MEMO)
SELECT DATETIME('now', 'localtime'), '0001-1-1 0:00:00', 0, '9999-12-31 23:59:59',
NODE,
TITLE,
'${req.body.status}',
MEMO
FROM
(SELECT MAX(WID),* FROM WA001_TODO_DETAIL
WHERE NODE = ${req.body.node}
GROUP BY NODE)`, [], function (err) {
if (err) {
return console.error('Insert error:', err.message);
}
console.log(`A row has been inserted with rowid ${this.lastID}`);
}
);
});
app.listen(port, () => {
console.log(`Server running at http://localhost:${port}`);
});
# Navigate to first project and run Node.js server
Start-Process powershell -ArgumentList "cd 'playground\pj1\sqlite-db1'; node server.js" -NoNewWindow
# Navigate to second project and run Vue dev server
Start-Process powershell -ArgumentList "cd 'playground\pj1'; npm run dev" -NoNewWindow
Start-Process explorer "http://localhost:5099"