s4: b8b1b8356b94 s4-funcs.sh

s4

view s4-funcs.sh @ 1038:b8b1b8356b94

Add translation of |>| for colspan, |^ for rowspan

author	HIROSE Yuuji <yuuji@gentei.org>
date	Sat, 06 Apr 2024 18:03:05 +0900
parents	ae88ca565d08
children	78e904f9be34

line source

1 #!/bin/sh

2 # Here's global variable table. Do not use this names.

3 # $HGid$

5 [ -f s4-config.sh ] && . ./s4-config.sh

7 test -n "$HTTP_HOST" && isCGI=true || isCGI=false

8 if $isCGI; then

9 case "$SCRIPT_NAME" in

10 *-world-*)

11 S4WORLD=${SCRIPT_NAME#*world-}

12 S4WORLD=${S4WORLD%.*}

13 echo S4WORLD=$S4WORLD >&2

14 worldconf=s4-config-${S4WORLD}.sh

15 ;;

16 *)

17 worldconf=s4-config.sh

18 ;;

19 esac

20 echo worldconf=$worldconf >&2

21 [ -n "$worldconf" -a -e "$worldconf" ] && . ./$worldconf

22 echo DB=$DB >&2

23 fi

24 myname=`basename ${SCRIPT_NAME:-$0}`

25 mydir=`dirname ${SCRIPT_FILENAME:-$0}`

26 cgiext=${CGIEXT:-.cgi}

27 myargs="$@"

28 PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH

29 tmpdir=${TMPDIR:-tmp}

30 dbdir=${DBDIR:-db}

31 logdir=${LOGDIR:-tmp}

32 tmpfiles=""

33 querylog=${QUERYLOG:-$logdir/query.log}

34 searchlog=${SEARCHLOG:-$logdir/search.log}

35 defaultdb=$dbdir/cgi.sq3

36 db=${S4INITDB:-${DB:-$defaultdb}}

37 sessdb=${SESSDB:-$dbdir/sess.sq3}

38 userupdateflag=$dbdir/userupdate

39 sesstb=tmp.sess

40 workdb=$dbdir/tmpdata.sq3

41 listentlimit=${LISTENTLIMIT:-30}

42 listartlimit=${LISTARTLIMIT:-50}

43 admin=${ADMIN:-hostmaster@example.org}

44 noreply=${NOREPLY:-noreply@example.org}

45 noreply_from="${S4NAME:-s4} message notification <$noreply>"

46 invite_policy=${INVITE_POLICY:-"このコミュニティに関りのあるあなたの信頼できる人を招きます。"}

47 templ=${TEMPL:-templ}

48 layout=${LAYOUT:-$templ/default}

49 formdir=${FORMDIR:-$templ/form}

50 imgdir=${IMGDIR:-img}

51 url=${URL:-"${REQUEST_SCHEME:-http${HTTPS:+s}}://$HTTP_HOST$REQUEST_URI"}

52 urlbase=${url%%\?*}

53 msgdir=$templ/msg

54 timeout="+2 days"

55 memoplimitdays="7"

56 dumpcollen=22

57 #thumbxy=120x120

58 thumbxy=96x96

59 iconxy_S=80x80

60 iconxy_M=400x400

61 maximagexy=1600x1600

62 ### maximagexy=400x400

63 filesize_max=${FILESIZE_MAX:-$((5*1024*1024))}

64 filesize_max_MB="$((filesize_max/1024/1024))MB"

65 file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*,application/sqlite*,application/csv"'

66 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.m4a,.m4v,.mkv,.obj,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.umap,.kml,.kmz,.html,.css,.js,.java,.el,.go,.cc,.rb,.rs,.py,.pl,lua,.awk,.sh,.c,.h,.log,.txt,.tex,.sty,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat,.db,.sq3,.blend,.gltf,.glb"'

68 file_accept_help="

69 添付可能ファイル: テキスト、画像、音声、動画、ODF、PDF、

70 圧縮ファイル、データベースファイル

71 (いずれも ${filesize_max_MB} 以内)

72 "

73 file_warn="$file_accept_help

74 [編集]リンクから修正してください。"

75 hidden_mode="('quiz', 'enquete')"

76 blogreadflagrowid=0

77 blogcutoffflagrowid=-1

78 nonewgroupworld=${NONEWGROUPWORLD:-*archive*}

79 whatsnewdays=${WHATS_NEW_DAYS:-14}

80 main_session=`date +%F-$$`

81 session=$main_session

82 mathjax=${MATHJAX:-'<script>MathJax = {

83 tex: {tags: "ams",

84 macros: {warn: ["\\fcolorbox{red}{white}{#1}", 1],

85 Warn: ["{\\Large \\warn{#1}}", 1]}}};</script>

86 <script src="https://polyfill.io/v3/polyfill.min.js?features=es6"></script><script id="MathJax-script" async src="https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js"></script>'}

88 tconfs=""

89 imgcached=cache/${S4WORLD:+$S4WORLD/}img.`date +%Y/%m`

90 conftbl=_tblconf

91 nl="

92 "

93 likeesc=`printf '\037'` # ESCAPE char of LIKE operator

94 iconcachekey="profimgcache_S"

95 asdelim=":::" # delimiter of dumptable td-class specifier

97 # Start debug logging

98 logtag="($$)${S4WORLD:+{$S4WORLD\}}"

99 exec 3>> $logdir/debug.out

100 err() {

101 # echo "[`date +%F-%T%z`]$logtag $@" 1>&3

102 # Avoid backslash escape sequences

103 cat<<EOF 1>&3

104 [`date +%F-%T%z`]$logtag $@

105 EOF

106 }

107 case "$HTTP_USER_AGENT" in

108 *i[Pp]hone*|*[Aa]ndroid*) touchpanel=1 ;;

109 *) touchpanel="" ;;

110 esac

111

112 # If S4MASTERDB is set, behave in another world

113 ### if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then

114 # If S4WORLDLIST is set, this s4 have world!

115 if [ -n "$S4WORLDLIST" ]; then

116 . ./s4-world.sh 2>> $logdir/debug.out

117 # Variables set in s4-world.sh

118 # $S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS

119 # Files created in s4-world.sh

120 # $worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile

121 fi

122

123

124 [ -f ./s4-cgi.sh ] && . ./s4-cgi.sh

125

126 : <<EOF

127

128 !! 検索等でblogテーブル参照時は sql4readableblogs() で定義される

129 !! readableblogs テーブルを使うこと

130 資料配布、グループ管理・ML、ファイル交換、クリッカー、アンケート

131 レポート提出管理

132 ひとつのarticleをheadingにして新規ツリーを作成、あるといいかも。

133

134 [2016]

135 7/12 根本への反省

136 * cgi自身の $1, $2 での切り替えでなく、CGI変数での受け渡しにすべき。

137 arg1/arg2/arg3 的に $1 に / 区切りでつけた方がよかったかな。

138

139 [以下2015]

140 8/4 ○グループに承認加入モードを追加

141 ○グループに参加していない場合は grpaction できない

142 Web

143 締切設定

144

145 8/2 ○s4.cgi生成系 → index.cgi生成

146 ○自分の提出物リスト

147

148 7/19 ○設置

149 ○一斉送信

150 ○getparfilename の tmpd の扱い

151 ○やっぱりs4にしようかな

152 7/18 ○書込著者からホームへのリンク

153 7/17 ○個人blogに「レポート提出用」がついたときの挙動

154 ○添付ファイル回収

155 ○imgcacheは別ディレクトリにしないと + .htaccess

156 7/15 ○レポート提出モードの表示を付ける

157 管理者権限での削除? → まだいいか

158

159 7/13 ○前回アクセス基準の新着数は欲しいなあ

160 ○レポート提出はどうしよう

161 → ○blogにモードを追加:

162 ○レポート提出モード

163 添付ファイル (誰が見たかログ)

164 クリッカーは別立てメニューにしないと(管理者がON/OFF)

165 ○添付ファイルの読み出し権(6/22から) ← モードで対処

166

167

168 7/9 ○管理者の追加

169 △グループメンバの操作 → 要不要を吟味

170 ○グループ情報編集の行先はそのグループがいい?

171 ○新規グループの作成はどこから入るか

172 △グループホームとユーザホームを揃える

173

174 7/8 ○グループ一覧をユーザ一覧と揃える。

175

176 7/6の次 ○グループのconf編集の入口

177 ○グループ検索

178

179 6/22の次 ○ホーム画面、○招待状、親記事追跡、○編集ボタン、削除ボタン、

180

181

182 6/7の次 ○blogを作ってみる || userconfig || _mのまとめ編集(削除)

183 6/7の次の次 ○userconfigの画面だけ作ってみる。

184

185 ○ 5/28の次 edittableに「削除」ボタンを足す

186 ○6/1 par2tableを triplex 対応に

187 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));

188 →とすると一気に

189

190 ## form.def を考えなおそう:

191 ## userのように必須カラムを決まった位置に付ける?

192 ## 必須カラム、owner(foreign key passwd(name)), update datetime

193 ## ユーザ管理とグループ管理はデフォルトで持たせてしまえ

194

195 ## 縦持ちデータの入力/編集を供給する関数 single + multi

196 ## 持てるテーブル構造はシステム標準5種 + ユーザ定義2種類

197 ## 1. passwd

198 ## 2. grp

199 ## 3. grp_mem

200 ## 4. topic 記事のIDとなる

201 ## 5. topic_cont 特定IDの記事の内容物

202 ## 6. list 繰り返し登場あり

203 ## 7. hash 繰り返し登場なし

204

205 ## ● listの定義:

206 ## create table list(id unique, parentID, type, value);

207 ## ● hashの定義:

208 ## create table hash(parentID, type, value, primary key(parentID, type));

209

210 ## グループ属性: community, friend

211 ## ○ blob使えるのかな。streamで行けるのか? xxdで行けた。ありがたい。

212 ## form-defとtableは1対1対応でいいか

213 ## csv2sq3 で .csv.sq3 の Makefile

214

215 ## 書き込みオブジェクトとは何か?

216 ## topic : id, belongto, title, owner, mode

217 ## type := root | comment

218 ## topic_cont : id, topicid(F), ppath, contenttype, filename, content,

219 ## unique(id, filename)

220 ## type := body(single) | attachment(multi)

221

222 ## group := name(P), tag, gecos, owner(F), mode

223 ## tag := personal | friend | ... any string

224 ## group_member := gname(F), type, name(F), UNIQUE(gname, type, name)

225 ## type := "u" | "g"

226 ## できたー!

227 ## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';

228 ↓

229 ↓以下に変更

230 with recursive allmem as

231 (select gname,val from grp_m where gname='foo'

232 union all select grp_m.gname,grp_m.val from

233 grp_m,allmem where allmem.val=grp_m.gname)

234 select val from allmem where val in (select name from user);

235

236

237 with recursive allmem as

238 (select gname,val from grp_m where gname='foo'

239 union all select grp_m.gname,grp_m.val from grp_m,allmem

240 where allmem.val=grp_m.gname)

241 select a.*, coalesce(b.val,a.val) from allmem a left join grp_mem_s b

242 on a.gname=b.gname and a.val=b.user and b.key='email'

243 where a.val in (select name from user);

244

245

246 ## triggerもできた。

247 ## 5/22から:グループ作成画面

248 ## 埋め込み画像 data:CONTENT-TYPE;base64,.....

249

250 ## 考え得るノードタイプ

251 ## 日報 - 個人所属かグループ所属か

252 ## 課題提出 - 個人所属かグループ所属か

253 ## グループ管理

254 ## 個人情報管理

255 ##

256

257 ## 例: group:sip - topic:1:sip:Aperture:yuuji:rw

258 ## - topic:2:sip:ISO:yuuji:rw

259 ## topic_cont 1:1:/:body:text...Aperture

260 ## 2:1:/1:body:text..Aperture

261 ## 3:1:/1:attachment:binary..Aperture

262 ## 4:1:/2:body:text..Aperture

263 ## 5:1:/2:attachment:binary..Aperture

264 ## 6:2:/:body:text..ISO

265 ## 7:2:/6:body:text..ISO

266 ## 8:2:/6:attachment:binary..

267

268 ## ログテーブル

269 ## time, who, action, tbl, id idなんか取れるかな

270

271

272

273 ■表設計

274 * 3つの表に分散管理

275 id格納表 + hash表 + list表

276 * *_s *_m

277

278

279

280 user, user_map, user_col

281

282 ■抽象エントリタイプ

283 * user

284 idとして機能 → table中の owner に自動挿入(?)

285 * group

286 権限判定に利用

287 * serial

288 自動idとして機能

289 * password

290 入力 type=passwordで入力

291 変更 oldpasswd, password×2 で確認後修正

292 * session

293 password認証後のセッションキーとして機能

294 * text

295 入力 type=text

296 * textarea

297 入力 textarea

298 * image|document

299 入力 type=fileで入力し、mime-typeを確認

300 * owner

301 入力時の $user で、外部キー制約が付く

302 * gowner

303 グループとしての所有者で、外部キー制約が付く

304 * timestamp

305 datetime()

306 * parent

307 木構造の場合の親の位置

308 * path

309 木構造の場合の自分の位置

310

311 格納タイプ

312 * list

313 表 parentID, key, val でUNIQUE(parentID, key, val)

314 * hash

315 表 parentID, key, val でUNIQUE(parentID, key)

316

317 オブジェクトタイプ

318 * entry

319 id, title, owner

320 * textpart

321 id, parentID, text

322 * binarypart

323 id, parentID, contenttype, filename, content

324 * content

325 hash(textpart), list(binarypart)

326 * topic

327 id, hash(content), list(reply)

328 * reply

329 id, parentID, content

330 * blog

331 list(entry)

332 blog = [topic, list(reply)]

333

334

335 blog = [ {"title" => "hoge", "owner" => "yuuji", "date" => "2015-04-27",

336 "text" => "hogehoge ..",

337 "reply" => [ {"serial" => 1,

338 "author" => "taro",

339 "date" => "2015-04-28",

340 "parent" => "/",

341 "path" => "/1",

342 "text" => "blah, blah, ....",

343 "image" => ["a.jpg", "b.jpg"] },

344 {"serial" => 2,

345 "author" => "hanako",

346 "date" => "2015-04-29",

347 "parent" => "/",

348 "path" => "/2",

349 "text" => "blah, blah, ....",

350 "image" => [] }]},

351 {"title" => "buha", ...} ]

352

353

354 user:=

355 ユーザ名(英数字):name:p:text:length="20" maxlength="40"

356 パスワード:pswd:s:password:length="20" maxlength="40"

357 説明(日本語OK):gecos:s:text:length="20" maxlength="40"

358 セッションキー:skey:s:session

359 メイルアドレス:email:m:text:length="20" maxlength="40"

360 住所:address:m:textarea:maxlength="400"

361 プロフィール画像:profimg:m:image:maxlength="400K"

362 履歴書:profpdf:m:document:maxlength="4M"

363

364 変換表

365 /user/email=m

366

367 blog:=

368 シリアル:id:p:serial

369 タイトル:title:s:text:

370 所有者:owner:s:owner:

371 時刻:ctime:s:stamp:

372 リード文:heading:s:textarea:

373 リプライ:reply:m:*article:

374

375 article:=

376 シリアル:id:p:serial

377 筆者:author:s:owner

378 時刻:ctime:s:stamp:

379 参照元:parent:s:parent:

380 パス:path:s:path:

381 本文:text:s:textarea:

382 画像:image:m:image:

383

384 履歴書:profpdf:m:document:maxlength="4M"

385

386

387 EOF

388

389 logstart() {

390 echo "`date '+%F %T'`:[${user:-NULL}]$logtag <<<" >> ${1:-$querylog}

391 }

392 logend() {

393 echo ">>>$logtag" >> ${1:-$querylog}

394 }

395 sqlog() {

396 logstart

397 if [ -z "$1" ]; then

398 cat >> $querylog

399 else

400 echo "$*" >> $querylog

401 fi

402 logend

403 }

404 sq() {

405 # ./args.rb -cmd ".timeout 3000" "$@"

406 logstart

407 if [ -z "$1" ]; then

408 tee -a $querylog|sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000"

409 else

410 echo "$@" >> $querylog

411 sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"

412 ###sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"

413 fi

414 logend

415 }

416 dbsetup() {

417 pipedir=$tmpdir/pipedir

418 [ -d $pipedir ] || mkdir -p -m 1777 $pipedir

419 [ -d $dbdir ] || mkdir -m 1775 $dbdir

420 suf=`date +%s`

421 sqi=$pipedir/sqi-$suf.$$

422 sqo=$pipedir/sqo-$suf.$$

423 mkfifo $sqi $sqo

424 #tail -f $sqi | sq $db & # "tail -f" is too heavy. DO NOT USE!!

425 sq $db < $sqi &

426 sq3pid="`jobs -p` $!"

427 if $isCGI; then

428 exec 2>> $logdir/error.out

429 chmod o-r $logdir/error.out

430 fi

431 exec 5> $sqi # Turning $sqi access through fd5 for continuous open state

432 chmod o-r $logdir/debug.out

433 rm $sqi

434 # Attach supplemental DB

435 cat >&5 <<-EOF

436 .output /dev/null

437 ATTACH DATABASE "$sessdb" AS tmp;

438 CREATE TABLE IF NOT EXISTS $sesstb(user, skey, expire, UNIQUE(user, skey));

439 DELETE FROM $sesstb WHERE expire < datetime('now', 'localtime');

440 DELETE FROM $sesstb WHERE expire is NULL or expire = '';

441 .output stdout

442 EOF

443 }

444 cleanup2() { # Dirty workaround for produced zombie processes

445 if [ -n "$HTTP_USER_AGENT" ]; then # When called from httpd

446 pkill -9 -u `id -u` -P 1

447 fi

448 chmod o-r $querylog $db $sessdb # make sure

449 }

450 cleanup() {

451 trap '' INT HUP EXIT TERM PIPE

452 echo .quit >&5

453 kill $sq3pid

454 kill $sq3pid

455 rm -f $sqo $sqi

456 rm -rf $tmpfiles

457 cleanup2

458 }

459 # We want to use piped function to put querylog, but we use

460 # simple redirection for the sake of speed.

461 query() {

462 # echo ".once $sqo" >&5

463 echo ".output $sqo" >&5

464 logstart

465 if [ -z "$1" ]; then

466 tee -a $querylog

467 else

468 printf '%s\n' "$@" >> $querylog

469 printf '%s\n' "$@"

470 fi >&5

471 echo ".output stdout" >&5

472 cat $sqo

473 rc=$?

474 logend

475 return $rc

476 }

477 _m4() {

478 #S4NAME=f,f,f

479 if m4 --version | grep -q GNU; then

480 if type om4; then # https://github.com/ibara/m4

481 m4() {

482 om4 "$@"

483 }

484 fi

485 fi >/dev/null 2>&1

486 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} \

487 ${S4WORLD:+-D_S4WORLD_="$S4WORLD"} \

488 ${S4WORLDNAME:+-D_S4WORLDNAME_="$S4WORLDNAME"} \

489 ${S4WORLDGRPS:+-D_S4WORLDGRPS_="$S4WORLDGRPS"} \

490 ${S4WORLDS:+-D_S4WORLDS_="$S4WORLDS"} "$@"

491 }

492 if ! type gdate && date --version | grep -q GNU; then

493 gdate() date "$@"

494 fi >/dev/null 2>&1

495 if ! type md5 && type md5sum && md5sum --version | grep -q GNU; then

496 md5() {

497 if [ -z "$1" ]; then

498 md5sum | cut -d' ' -f 1

499 else

500 for f; do

501 printf "MD5 (%s) = %s\n" "$f" "$(md5 < $f)"

502 done

503 fi

504 }

505 fi >/dev/null 2>&1

506 ismember() {

507 # $1=user, $2=group

508 #err ismem: "select user from grp_mem where gname=$(sqlquote $2) and user='$1';"

509 test -n "`query \"select user from grp_mem where gname=$(sqlquote \"$2\") and user='$1';\"`"

510 }

511 isuser() { # Check if $1 is a valid user

512 test -n "`query \"select name from user where name='$1';\"`"

513 }

514 isgroup() { # Check if $1 is a valid group

515 err isgroup: "select gname from grp where gname=$(sqlquote $1);"

516 test -n "`query \"select gname from grp where gname=$(sqlquote \"$1\");\"`"

517 }

518 isgrpowner() (

519 # $1=user, $2=group

520 gn=`sqlquote "$2"`

521 sql="select user from grp_adm where gname=$gn and user='$1';"

522 err isgrpowner: $sql

523 test -n "`query \"$sql\"`"

524 )

525 isgrpownerbygid() (

526 # $1=user, $2=group-rowid

527 sql="select user from grp_adm where gname=(select gname from grp where rowid=$2) and user='$1';"

528 err isgrpownerbygid: $sql

529 test -n "`query $sql`"

530 )

531 getgroupadminmails() {

532 # $1=group

533 for i in $(getgroupadmins "$1"); do

534 email4group "$1" "$i" ;

535 done

536 }

537 getgroupadmins() { # $1=group

538 # This function is called in a backquote, so needn't to be subshellized

539 qgrp=`sqlquote "$1"`

540 query "select user from grp_adm where gname=$qgrp;"

541 }

542 getgroupattr() { # $1=group $2=attr

543 # This function is called in a backquote, so needn't to be subshellized

544 getvalbyid grp $2 \

545 $(query "select rowid from grp where gname=`sqlquote \"$1\"`;")

546 }

547 getgroupbyid() {

548 # $1=id|gname

549 sql="select coalesce((select gname from grp where gname=$(sqlquote \"$1\")),

550 (select gname from grp where rowid=$(sqlquote $1)));"

551 # err ggbyid: `echo $sql`

552 query $sql

553 }

554 isfilereadable() { # $1=user $2=tbl $3=rowid

555 # Return true if user($1) can read attachment files in tbl($2):rowid($3)

556 [ -z "$1" -o -z "$2" -o -z "$3" ] && return 1 # invalid argument

557

558 # Return true when anonymous mode

559 [ "$anonymousmode" ] && return 0

560 # case `getvalbyid blog mode $2` in

561 # normal|*open*|"") return 0 ;;

562 # *closed*)

563 # owner=`getvalbyid blog owner $2`

564 # if isgrp $owner; then

565 # isgrpowner $1 $owner && return 0 || return 1

566 # elif isuser $owner; then

567 # [ x"$1" = x"$owner" ] && return 0 || return 1

568 # fi

569 # esac

570 # ↑ 要はこういう処理を↓で一気にやっている

571 case "$2" in

572 grp_*)

573 sql="SELECT 'owner'

574 FROM grp_adm

575 WHERE gname=(SELECT gname FROM $2 WHERE rowid=$3)

576 AND

577 user = '$user';"

578 ;;

579 *)

580 sql="with getblog as (

581 select key,val from blog_s where id=(

582 select blogid from article where id in

583 (select id from $2 where rowid=$3))),

584 getowner as (select val from getblog where key='owner'),

585 getauthor as (select author from article where id=(select id from $2 where rowid=$3)),

586 isgrp as (SELECT val from getowner WHERE val IN (select gname from grp)),

587 isgrpadm as (select user from grp_adm where

588 gname=(select val from getowner) and

589 user='$1'),

590 getmode as (select val from getblog where key='mode')

591 select case

592 when (select author from article where

593 id=(select id from $2 where rowid=$3))='$1'

594 then 'author'

595 when (select val from getmode) in ('report-open', 'normal')

596 then 'open'

597 when (select val from getmode) in ('quiz', 'enquete')

598 then CASE

599 WHEN (SELECT val FROM isgrp) IS NULL

600 THEN

601 CASE WHEN (SELECT val from getowner)

602 IN ('$user', (SELECT author FROM getauthor))

603 THEN 'owner-or-user-article-is-readable'

604 ELSE ''

605 END

606 WHEN (select user from isgrpadm) IS NOT NULL

607 THEN 'i-am-admin'

608 ELSE (SELECT author from getauthor WHERE author IN (SELECT user FROM grp_adm WHERE gname=(SELECT val FROM getowner)))

609 END

610 when (select val from getmode) is null

611 then 'open'

612 when (select val from getowner) in (select gname from grp)

613 then (SELECT user FROM isgrpadm)

614 when (select author from article where

615 id=(select id from $2 where rowid=$3))='$1'

616 then 'user+author'

617 else '' end;"

618 ;;

619 esac

620 ## err isfilereadable: sql="`echo $sql`"

621 # caseのネストで内側のcaseがスカラーtrueを返しても外側はtrue扱いにならない

622 # result=`query "$sql"`

623 # err FileAccessibility=$result

624 [ -n "`query $sql`" ] || return 2

625 }

626 linkhome() {

627 # $1=UserOrGroupRowid

628 echo -n "<a href=\"$myname?"

629 if isuser $1; then

630 err "select 'home+'||rowid from user where name='$1';"

631 query "select 'home+'||rowid from user where name='$1';"

632 name=`gecos $1|htmlescape`

633 else

634 _grid=`numericalize "$1"`

635 echo -n "grp+$1"

636 name=`query "SELECT gname FROM grp WHERE rowid=$_grid;"|htmlescape`

637 fi

638 echo "\">$name</a>"

639 }

640 hreflink() {

641 # s4 specific notation:

642 # ^href=URL

643 # ^iframe=URL

644 # ^video=URL

645 # [[#NUM]] - Jump to article ID NUM

646 # [[#Keyword] - Jump to keywrod search for "Keyword"

647 # OSM umap Wikistyle Notation:

648 # [[URL]] - Simle Link

649 # [[URL|Word]] - Link with anchor word

650 # {{URL}} - <img src="URL">

651 # {{URL|width}} - <img src="URL" width="width">

652 # {{{URL}} } - <iframe src="URL"></iframe>

653 # {{{URL|height}} - <iframe src="URL" height="height"></iframe>

654 # Other Style

655 # ---- - <hr> (In the beginning of line)

656 # *Word* - <em>Word</em>

657 # _Word_ - <em>Word</em>

658 # **Word** - <strong>Word</strong>

659 # __Word__ - <strong>Word</strong>

660 # SPC+SPC+$ - <br>

661 cb='<input type="checkbox" class="s4-checkbox" disabled'

662 checkboxON="${cb} checked>"

663 checkboxOFF="${cb}>"

664 _hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"

665 _hrefptn="[A-Za-z0-9/~%+?=@!.][^][()<> ]*" # URL should start with ASCII

666 sed -e "s|\[\[\#$[0-9][0-9]*$\]\]|<a href=\"?aid\1\">#\1</a>|g" \

667 -e "s|\[\[#$[^]&]*$\]\]|<a href=\"?kwd=\1\&stage=searchart\">\#\1</a>|g" \

668 -e "s|\[\[$$_hrefptn$\|$[^]]*$\]\]|<a href=\"\1\">\2</a>|g" \

669 -e "s|\[\[$$_hrefptn$\]\]|<a href=\"\1\">\1</a>|" \

670 -e "s|{{{$$_hrefptn$\|$.*$}}}|<iframe src=\"\1\" height=\"\2\"></iframe>|g" \

671 -e "s|{{{$$_hrefptn$}}}|<iframe src=\"\1\"></iframe>|g" \

672 -e "s|{{$$_hrefptn$\|$.*$}}|<img src=\"\1\" width=\"\2\">|g" \

673 -e "s|{{$$_hrefptn$}}|<img src=\"\1\">|g"\

674 -e "s|^href=$$_hrefptn$|<a &>\1</a>|" \

675 -e "s|^iframe=$$_hrefptn$|<iframe src=\"\1\"></iframe>|" \

676 -e "s|^video=$$_hrefptn$|<video controls><source height=\"320\" src=\"\1\"></video>|" \

677 -e "s,^#### *$.*$,<h4>\1</h4>," \

678 -e "s,^### *$.*$,<h3>\1</h3>," \

679 -e "s,^## *$.*$,<h2>\1</h2>," \

680 -e 's,^----*$,<hr>,' \

681 -e 's, \*\*$[^* |][^*|]*[^ |]$\*\* ,<strong>\1</strong>,g' \

682 -e 's, __$[^_ |][^_]*[^ ]$__ ,<strong>\1</strong>,g' \

683 -e 's, \*$[^* |][^*|]*[^ |]$\* ,<em>\1</em>,g' \

684 -e 's, _$[^_ ][^_]*[^ ]$_ ,<em>\1</em>,g' \

685 -e 's,~~$[^~][^~]*$~~,<s>\1</s>,g' \

686 -e 's,$[^\\]$;;;,\1<br>,g;s,\\;;;,;;;,g' \

687 -e 's, $,<br>,' \

688 -e "s,- \[ *\]$[^|-]*$,${checkboxOFF}<label>\\1</label>,g" \

689 -e "s,- \[[^ ]\]$[^|-]*$,${checkboxON}<label>\\1</label>,g" \

690

691 }

692 minitbl() {

693 sed -n '

694 /^|.*|/ {; # If the line begin with "|" and has 2 or more "|"

695 s,|$,,; # Remove trailing "|" first

696 s,|\* *>>>>>>|$[^|]*$ *,<th colspan="7">\1</th>,g;

697 s,|\* *>>>>>|$[^|]*$ *,<th colspan="6">\1</th>,g;

698 s,|\* *>>>>|$[^|]*$ *,<th colspan="5">\1</th>,g;

699 s,|\* *>>>|$[^|]*$ *,<th colspan="4">\1</th>,g;

700 s,|\* *>>|$[^|]*$ *,<th colspan="3">\1</th>,g;

701 s,|\* *>|$[^|]*$ *,<th colspan="2">\1</th>,g;

702 s,|\* *^^^^^^$[^|]*$ *,<th rowspan="7">\1</th>,g;

703 s,|\* *^^^^^$[^|]*$ *,<th rowspan="6">\1</th>,g;

704 s,|\* *^^^^$[^|]*$ *,<th rowspan="5">\1</th>,g;

705 s,|\* *^^^$[^|]*$ *,<th rowspan="4">\1</th>,g;

706 s,|\* *^^$[^|]*$ *,<th rowspan="3">\1</th>,g;

707 s,|\* *^$[^|]*$ *,<th rowspan="2">\1</th>,g;

708 s,|\* *$[^|]*$ *,<th>\1</th>,g; # "|*..." to "<th>...</th>"

709 s,| *>>>>>>|$[^|]*$ *,<td colspan="7">\1</td>,g;

710 s,| *>>>>>|$[^|]*$ *,<td colspan="6">\1</td>,g;

711 s,| *>>>>|$[^|]*$ *,<td colspan="5">\1</td>,g;

712 s,| *>>>|$[^|]*$ *,<td colspan="4">\1</td>,g;

713 s,| *>>|$[^|]*$ *,<td colspan="3">\1</td>,g;

714 s,| *>|$[^|]*$ *,<td colspan="2">\1</td>,g;

715 s,| *^^^^^^$[^|]*$ *,<td rowspan="7">\1</td>,g;

716 s,| *^^^^^$[^|]*$ *,<td rowspan="6">\1</td>,g;

717 s,| *^^^^$[^|]*$ *,<td rowspan="5">\1</td>,g;

718 s,| *^^^$[^|]*$ *,<td rowspan="4">\1</td>,g;

719 s,| *^^$[^|]*$ *,<td rowspan="3">\1</td>,g;

720 s,| *^$[^|]*$ *,<td rowspan="2">\1</td>,g;

721 s,| *$[^|]*$ *,<td>\1</td>,g; # "|..." to "<td>...</td>"

722 s,^,<tr>,; s,$,</tr>,; # Enclose with "<tr>" and "</tr>"

723 H; # Concat this line to HoldSpace

724 s/.*//; # Delete PatternSpace for finalization

725 $ b cont

726 d; # If in final line, output the rest, else jump to next turn

727 }

728 :cont

729 x; # For non-"|" lines, check HoldSpace

730 /^./ {; # If HoldSpace has "|" table elements

731 s|^.|<table class="mini">|; # Enclose whole elements like this:

732 # . of ^. is workaround for FreeBSD sed

733 # s|$|</table>|; # <table class="mini">..\n..</table>

734 p; # Print whole "table" element

735 s/.*//; # Erase all when done.

736 x; s|^|</table>|; x; # Preppend /table to the next line

737 }

738 x; # Back to the newest line

739 p; # Print rest' | miniul

740 }

741 miniul() {

742 sed -e '

743 /^\* / {; # 行頭 "* "

744 x; s,^,<ul>,; x; # 1周目: ホールドスペース先頭に <ul> を

745 :top

746 s/\n//;

747 s/^ *//; # 2周目以降: 行頭空白削除

748 s,\* ,,; # まず行頭の "* " を消しておく

749 H; # 置き換え結果をホールドスペースに追加

750 s/.*//; # パターンスペースは消しておく

751 # ↓最終行なら残ったホールドスペース処理のため :cont へ

752 $ b cont

753 N; # 次の行を読む

754 s/\n//; # 空白始まりは継続行

755 /^ /b top

756 x; s/\n/<li>/; s,$,</li>,; # 継続行でなければ <li></li> で囲む

757 p; s/.*//;

758 x; # 次も "* " ならループを抜けない

759 /^\* /b top

760 s,^,</ul>,; # 次が一般行なら箇条書終わり

761 }

762

763 :cont

764 x; # 行頭| 以外の行:

765 /./ {; # ホールドスペースに文字列があれば

766 s/^\n/<li>/; s,$,</li></ul>,; # 箇条書を書き切って終わり

767 H; x

768 }

769 x' | miniol

770 }

771 miniol() {

772 sed -e '

773 /^[1-9]\. / {; # 行頭 "N. "

774 h;x; # 1周目: ホールドスペース先頭に <ol> を

775 s,^$[1-9][0-9]*$\. .*,<ol start="\1">,; # 初期番号付きで追加

776 x;

777 :top

778 s/\n//;

779 s/^ *//; # 2周目以降: 行頭空白削除

780 H; # 置き換え結果をホールドスペースに追加

781 x;

782 s,[1-9][0-9]*\. ,,; # まず行頭の "N. " を消しておく

783 x;

784 s/.*//; # パターンスペースは消しておく

785 # ↓最終行なら残ったホールドスペース処理のため :cont へ

786 $ b cont

787 N; # 次の行を読む

788 s/\n//; # 空白始まりは継続行

789 /^ /b top

790 x; s/\n/<li>/; s,$,</li>,; # 継続行でなければ <li></li> で囲む

791 p; s/.*//;

792 x; # 次も "* " ならループを抜けない

793 /^[1-9][0-9]*\. /b top

794 s,^,</ol>,; # 次が一般行なら箇条書終わり

795 }

796

797 :cont

798 x; # 行頭| 以外の行:

799 /./ {; # ホールドスペースに文字列があれば

800 s/^\n/<li>/; s,$,</li></ol>,; # 箇条書を書き切って終わり

801 H; x

802 }

803 x'

804 }

805 acclog() (

806 # $1=table, $2=rowid

807 n=${2%%[!-0-9]*} # Remove non-digit chars from $2(should be rowid)

808 if [ -n "$n" ]; then

809 now=`date +"%F %T"`

810 #query "replace into acclog values('$user', '$1', '$n', '$now');"

811 #query "replace into acclog values('$user', '$1', $n, '$now');"

812 query "replace into tblaccesses values('$user', '$1', $n, '$now');"

813 fi

814 )

815 gecos() (

816 u=`sqlquote "${1:-$user}"`

817 query "select gecos from gecoses where name=$u;"

818 )

819 setpar() {

820 # 2020/5/14 Add dirty code to cache essential params

821 if [ x"$session" = x"$main_session" ]; then

822 case "$1" in

823 user) _user="$v" ;;

824 skey) _skey="$v" ;;

825 esac

826 fi

827 query "replace into par values('$session', '$1', '$2', \"$3\");"

828 }

829 unsetpar() {

830 for i; do

831 if [ x"$session" = x"$main_session" ]; then

832 case "$i" in

833 user|skey) unset _$i ;;

834 esac

835 fi

836 query "DELETE FROM par WHERE var='$i' AND sessid='$session';"

837 done

838 }

839 replpar() {

840 query "update par set val=\"$3\" where sessid='$session' and var='$1' and type='$2';"

841 }

842 getpar() {

843 # err GETPAR=$1, _user=$_user

844 val=""

845 if [ x"$session" = x"$main_session" ]; then

846 case "$1" in # Dirty cache mechanism for high-load average

847 user) val=$_user ;;

848 skey) val=$_skey ;;

849 esac

850 fi

851 val=${val:-`query "select val from par where var='$1' and sessid='$session' $2;"`}

852 ## err getpar/val1: "val=[$val]"

853 if [ -z "$val" ]; then

854 val=`query "select val from cookie where var='$1' and sessid='$session' $2;"`

855 fi

856 ## err getpar/val2: "val=[$val]"

857 case "$var" in

858 owner)

859 if [ x"$user" = x"$val" ]; then

860 echo $user; return

861 elif ismember $user $val; then

862 printf '%s' "$val"; return

863 fi ;;

864 esac

865 ## err getpar/ret: "val=[$val]"

866 printf '%s' "$val"

867 }

868 setskey() {

869 # For quick response...(?)

870 query "REPLACE INTO $sesstb VALUES('$1', '$2', datetime('now', 'localtime', '$timeout'));"

871 }

872 chkskey() {

873 # $1=sesskey, $user=LoginUserName

874 test -z "$1" && return 1

875 repl=`query "SELECT rowid,user FROM $sesstb WHERE user='$user' AND skey = '$1';"` || return 2

876 rowid=${repl%%\|*}; repuser=${repl#*\|}

877 if [ -n "$rowid" -a x"$user" = x"$repuser" ]; then

878 query "UPDATE $sesstb SET expire=datetime('now', 'localtime', '$timeout') WHERE rowid=$rowid;" # Errors can be ignored

879 return 0

880 fi

881 return 1

882 }

883 resetskey() {

884 if [ -n "$_user" ]; then

885 query "DELETE FROM $sesstb WHERE user='$_user';"

886 fi

887 }

888 getpartype() {

889 query "select type from par where var='$1' and sessid='$session' $2;"

890 }

891 getparcount() {

892 query "select count(*) from par where var='$1' and sessid='$session' $2;"

893 }

894 getparfilename() {

895 # null if type of $1 is not file

896 (f=`query "select val from par where var='$1' and sessid='$session' and type='file' $2;"`

897 [ -n "$f" ] && echo $f)

898 }

899 sqlquote() {

900 (v="$1"

901 case "$v" in

902 "") return ;; # null

903 "X'"*) # quoted hex string

904 echo $1 ;;

905 *\"*) # string including dbl-quote"

906 v=`printf '%s' "$v"|sed -e 's/\"/\"\"/g'`

907 printf '%s' "\"$v\""

908 return ;;

909 *.*.*|*-*-*|*[Ee]*[Ee]*|[Ee]*|*[\ -,:-df-~]*) # string

910 printf '%s' "\"$v\""

911 return ;;

912 *)

913 if expr "$v" : '[-0-9.Ee][-0-9.Ee]*$' >/dev/null 2>&1; then

914 printf '%s' $v # MAYBE numeric, maybe...

915 else

916 printf '%s' "\"$v\""

917 fi ;;

918 esac)

919 }

920 sqlquotestr() (

921 case "$1" in

922 *\'*) v=`printf '%s' "$1"| sed "s/'/''/g"`

923 printf '%s' "'$v'" ;;

924 *) printf '%s' "'$1'" ;;

925 esac

926 )

927 mktempd() {

928 TMPDIR=$tmpd mktemp -d -t $session.XXXX

929 }

930 getcachedir() { # $1=maintable

931 if [ -n "$imgcached" ]; then

932 echo $imgcached/$(echo ${1:-hoge}|md5)/$thumbxy

933 else

934 echo $tmpd/$thumbxy

935 fi

936 }

937 getval() {

938 # $1=table $2=col $3(optional)=condition

939 case `gettbl_coltype "/$1/$2"` in

940 user|author) # author added 2015-06-18 for article(author)

941 echo "$user" ;;

942 stamp|datetime)

943 date "+%F %T" ;;

944 serial)

945 (s=`getpar $2`

946 if [ -n "$s" ]; then echo $s; else echo "`date +%s`x$$"; fi) ;;

947 *)

948 getpar "$2" "$3";;

949 esac

950 }

951

952 getvalquote() {

953 # $1=table $2=col $3(optional)=condition

954 (v=`getval "$@"`

955 case "$v" in

956 "") echo NULL ;;

957 *) sqlquotestr "$v" ;;

958 esac)

959 }

960 getparquote() {

961 sqlquote "`getpar $1`"

962 }

963 getvalbyid() {

964 # $1=tbl $2=col $3=rowid $4=tmpdirForBinary

965 # If two or more values found, save them to $tmpd/${column}.$N and

966 # store the number of files into $tmpd/${column}.count and

967 # their each rowid stored into $tmpd/${column}.$N.rowid.

968 ## err gtb-$1=`gettblcols $1`, tbl=$1, col=$2, '$3'=$3

969

970 (for c in `gettblcols $1`; do

971 if [ x"$2" = x"$c" ]; then

972 ###sq $db "select $2 from $1 where rowid=$3"

973 query "select $2 from $1 where rowid=$3;"

974 return

975 fi

976 done

977 rowid=$3

978 pk=`gettblpkey $1`

979 key=`query "select $pk from $1 where rowid=$3;"`

980 getkey="(select $pk from $1 where rowid=$3)"

981 td=${4:-$tmpd}

982 [ -d $td ] || mkdir -p $td

983 ### err "select $pk from $1 where rowid=$3" - key=$key '$4(tmp)'=$4

984 for kt in s m; do

985 t=${1}_$kt

986 for c in `gettbl_${kt}_cols $1`; do

987 vcount=1 # count(val)

988 if [ x"$2" = x"$c" ]; then

989 #### cond="$t where $pk=\"$key\" and key=\"$c\"" #2015-07-22

990 cond="$t where $pk=$getkey and key=\"$c\""

991 val=`query "select val from $cond limit 1;"`

992 type=`query "select type from $cond limit 1;"`

993 if [ $kt = m ]; then

994 ###vcount=`sq $db "select count(val) from $cond"`

995 # Reset val to store filenames if type is string

996 val=`query "select val from $cond and type like 'file:%' order by rowid;"`

997 ## err gvb1-sql: "select count(val) from $cond;"

998 vcount=`query "select count(val) from $cond;"`

999 echo $vcount > $td/$c.count

1000 i=0

1001 ## err gvbid: i=$i vcount=$vcount

1002 while [ $i -lt $vcount ]; do

1003 slice="order by rowid limit 1 offset $i"

1004 i=$((i+1))

1005 fn=$c.$i

1006 ## err td=$td, fn=$fn, type=$type, val="[$val]"

1007 case $type in

1008 file:*)

1009 #file=$td/$val

1010 r_f=`query "select rowid||'//'||val from $cond $slice;"`

1011 f_rid=${r_f%%//*}

1012 file=$td/${r_f##*//}

1013 # FOR SPEED: Skip file generation if imgcache exists

1014 [ -s "$file" -a -s "$td/$fn.rowid" -a -s "$file.rowid" ] \

1015 && [ x"$f_rid" = x"`cat $td/$fn.rowid`" ] \

1016 && continue

1017 # err gvbid-get="select quote(bin) from $cond $slice;"

1018 ## err output: "fn=[$fn] file=[$file]"

1019 sq $db<<EOF | unhexize > "$file"

1020 .output '$td/$fn.rowid'

1021 select rowid from $cond $slice;

1022 .output '$td/$fn'

1023 select val from $cond $slice;

1024 .output '$td/${fn}.content-type'

1025 select substr(type, 6) from $cond $slice;

1026 .output stdout

1027 select quote(bin) from $cond $slice;

1028 EOF

1029 ## err gvbid-get2: "`ls -lF $file`"

1030 ## err i=$i - file=$file rowid=`cat $td/$fn.rowid`

1031 cp "$td/$fn.rowid" "$file.rowid" 2>&3 # for convenience

1032 cp "$file" "$file.orig" 2>&3

1033 ls -lh "$file" |

1034 awk '{print $5"B"}'|sed 's/BB/B/' > "$file.size"

1035 case "$type" in

1036 *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;

1037 ### ここのアイコンを増やしたい

1038 *|*:[Aa]pplication*)

1039 convert -geometry $thumbxy $imgdir/file-icon.png \

1040 png:- > "$file"

1041 ;;

1042 esac

1043 ;;

1044 *)

1045 sq $db<<EOF

1046 .output $td/$fn.rowid

1047 select rowid from $cond $slice;

1048 .output $td/$fn

1049 select val from $cond $slice;

1050 EOF

1051 val=$val${val:+$nl}"`echo $fn`" # should be delimited by newline

1052 ;;

1053 esac

1054 done

1055 else

1056 rm -f $td/$c.count

1057 case $type in

1058 file:*)

1059 printf '%s\n' "$val" \

1060 | while read fn; do

1061 file=$td/$fn

1062 if [ ! -s "$file" ]; then

1063 ## sq $db "select quote(bin) from $cond and val=\"$fn\"" \

1064 query "select quote(bin) from $cond and val=\"$fn\";" \

1065 | unhexize > "$file"

1066 ##@@## -- echo ${type#file:} > "$file.content-type"

1067 case $type in

1068 *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;

1069 *:[Aa]pplication*)

1070 convert -geometry $thumbxy $imgdir/file-icon.png \

1071 png:- > $file ;;

1072 esac

1073 fi

1074 done

1075 ;;

1076 esac

1077 fi

1078 printf '%s' "$val"

1079 return

1080 fi

1081 done

1082 done)

1083 }

1084 getvalbypkey() (

1085 # $1=tbl $2=col $3=pkey $4=tmpdirForBinary

1086 pk=`gettblpkey $1`

1087 rowid=`query "select rowid from $1 where $pk='$3';"`

1088 getvalbyid "$1" "$2" $rowid $4

1089 )

1090 getvalbycond() {

1091 # $1=tbl $2=col $3=SQL-Condition

1092 ###rowid=`sq $db "select rowid from $1 where $3"`

1093 rowid=`query "select rowid from $1 where $3;"`

1094 if [ -n "$rowid" ]; then

1095 getvalbyid "$1" "$2" $rowid "$4"

1096 fi

1097 }

1098 getpwfield() {

1099 # getpwfield user column

1100 # val=`sqlite3 $db "select $2 from passwd where name='$1' $3"`

1101 val=`getvalbycond user $2 "name='$1'"`

1102 if [ -n "$val" ]; then

1103 echo "$val"

1104 return 0

1105 else

1106 return 1

1107 fi

1108 }

1109 numericalize() {

1110 echo "${1%%[!0-9]*}"

1111 }

1112 encode() {

1113 if [ -z "$sha1" ]; then

1114 if type sha1 >/dev/null 2>&1; then

1115 sha1=sha1

1116 elif type sha1sum >/dev/null 2>&1; then

1117 sha1=sha1sum

1118 elif type gsha1sum >/dev/null 2>&1; then

1119 sha1=gsha1sum

1120 fi

1121 fi

1122 $sha1 "$@" | cut -d' ' -f1

1123 }

1124 if type gs >/dev/null 2>&1; then

1125 gs_pdfwrite() {

1126 gs -sDEVICE=pdfwrite -dPDFSETTINGS=/default \

1127 -dNOPAUSE -dQUIET -dBATCH -o "$2" "$1" >/dev/null 2>&1

1128 }

1129 fi

1130 enjpeg() {

1131 if [ -z "$cjpeg" ]; then

1132 if type cjpeg >/dev/null 2>&1; then

1133 cjpeg="cjpeg"

1134 else

1135 cjpeg="convert - jpeg:-"

1136 fi

1137 fi

1138 $cjpeg "$@"

1139 }

1140 mycrypt() (

1141 key=$1 salt=$2

1142 # err \$2=$2

1143 case $2 in

1144 '$'*'$'*) salt=${salt#\$4\$}

1145 salt=${salt%\$*} ;;

1146 esac

1147 echo -n '$4$'"$salt"'$'

1148 echo "$salt$key" | encode || exit 1 # Abort if fail to call encode

1149 )

1150 hexize() {

1151 if [ -z "$hexize" ]; then

1152 if type xxd >/dev/null 2>&1; then

1153 hexize="xxd -p"

1154 else

1155 hexize_hd() {

1156 hexdump -ve '1/1 "%.2x"'

1157 }

1158 hexize="hexize_hd"

1159 fi

1160 fi

1161 cat "$@" | $hexize | tr -d '\n'

1162 }

1163 unhexize() {

1164 if [ -z "$unhex" ]; then

1165 if type xxd >/dev/null 2>&1; then

1166 unhex="xxd -p -r"

1167 elif type perl >/dev/null 2>&1; then

1168 cat >$tmpd/unhex.pl<<EOF

1169 s/([0-9a-f]{2})/print chr hex \$1/gie

1170 EOF

1171 # Perl refuses -e in setuid circumstances, which can be absurdly

1172 # avoided by creating scripts in a file where its parent directory is

1173 # world writable...:)

1174 unhex="perl -n $tmpd/unhex.pl"

1175 fi

1176 fi

1177 cat "$@" | $unhex

1178 # cat $1 | tee /tmp/uh.in| $unhex | tee /tmp/uh.out

1179 }

1180 percenthex() {

1181 hexize "$@" | sed 's/$..$/%\1/g'

1182 }

1183 htmlescape() {

1184 sed -e 's/\&/\&/g' -e 's/"/\"/g' -e "s/'/\'/g" \

1185 -e "s/</\</g; s/>/\>/g" -e 's/`/\`/g' -e 's/(/\(/g' \

1186 -e 's/`/\&#96/'

1187 }

1188 enascii() {

1189 if [ -z "$enascii" ]; then

1190 if type kakasi >/dev/null 2>&1; then

1191 enascii="kakasi -Ha -Ka -Ja -Ea -ka"

1192 else

1193 enascii_now=`date +%FT%T`

1194 enascii_sed() {

1195 nkf -Z0Z1Z2 \

1196 | sed -e "s/^/$enascii_now/" -e "s|[^-0-9.A-z/,()_=]|x|g"

1197 }

1198 enascii="enascii_sed"

1199 fi

1200 fi

1201 cat "$@" | $enascii

1202 }

1203 size_h() {

1204 i="$1" oi=$1

1205 set -- B B KB MB GB TB

1206 while [ $((i)) -gt 9 -a -n "$1" ]; do # -gt 9 means $oi > 1024

1207 oi=$i

1208 i=$((i/1024))

1209 shift

1210 done

1211 echo ${oi}$1

1212 }

1213 gettblconf() {

1214 if [ -z "$tconfs" ]; then

1215 ## tconfs=`sq $db \

1216 tconfs=`query \

1217 "select tbl||'/'||col||'='||keytype||'/'||objtype from $conftbl;"`

1218 fi

1219 # /tb1/col1=p/text /tb1/col2=s/text /tb1/col3=m/image /tb2/col1=p/text ...

1220 }

1221 gettblkeys() {

1222 # $1=tbl

1223 gettblconf

1224 echo "$tconfs" | fgrep "/$1/" | \

1225 (type="" keys="" fks="" cols="" scols="" mcols="" hcols=""

1226 while IFS='=' read tc conf; do # tc=/tb1/col1 conf=s/text

1227 col=${tc##*/} type=${conf%%/*}

1228 case $type in

1229 *p*)

1230 cols=$cols"${cols:+:}$col"

1231 keys=$keys"${keys:+:}$col" ;;

1232 *f*) cols=$cols"${cols:+:}$col"

1233 fks=$fks"${fks:+:}$col" ;;

1234 *m*) mcols=$mcols"${mcols:+:}$col" ;;

1235 *s*) scols=$scols"${scols:+:}$col" ;;

1236 esac

1237 case $type in

1238 *h*) hcols=$hcols"${hcols:+:}$col" ;;

1239 esac

1240 done

1241 echo "_keys=$keys _fks=$fks _cols=$cols _scols=$scols _mcols=$mcols _hcols=$hcols")

1242 }

1243 gettblpkey() {

1244 # $1=tbl

1245 gettblkeys $1 | cut -d ' ' -f 1 | sed -e 's/.*=//' -e 's/:/ /g'

1246 }

1247 gettblfkey() {

1248 (x=`gettblkeys $1`

1249 x=${x#*_fks=} # cut before "_fks=" including

1250 echo ${x%% *} | tr ':' ' ')

1251 }

1252 gettblcols() {

1253 (x=`gettblkeys $1`

1254 x=${x#*_cols=} # cut before "_cols=" including

1255 echo ${x%% *} | tr ':' ' ')

1256 }

1257 gettbl_s_cols() {

1258 (x=`gettblkeys $1`

1259 x=${x#*_scols=} # cut before "_scols=" including

1260 echo ${x%% *} | tr ':' ' ')

1261 }

1262 gettbl_m_cols() {

1263 (x=`gettblkeys $1`

1264 x=${x#*_mcols=} # cut before "_mcols=" including

1265 echo ${x%% *} | tr ':' ' ')

1266 }

1267 gettbl_h_cols() {

1268 (x=`gettblkeys $1`

1269 x=${x#*_hcols=} # cut before "_hcols=" including

1270 echo ${x%% *} | tr ':' ' ')

1271 }

1272 gettbl_coltype() (

1273 gettblconf

1274 x=`echo "$tconfs"|fgrep $1=`

1275 x=${x#*=} # cut before =

1276 echo ${x#*/} # cut before p/ including

1277 )

1278 is_hidden() {

1279 # $1=Tbl $2=col

1280 gettblconf

1281 x=`echo "$tconfs"|fgrep /$1/$2=`

1282 x=${x#*=} # cut before =

1283 x=${x%%/*} # cut after /

1284 case $x in

1285 *h*) return 0 ;;

1286 *) return 1 ;;

1287 esac

1288 }

1289

1290 dbsetbyid() {

1291 # $1=tbl $2=id $3=col $4=val/filename - &optional - $5=content-type

1292 (t0=$1 t=$1 p=$2 c=$3

1293 tsc=$t/$c val=$4

1294 quotedp=$(sqlquotestr "$p")

1295 unset primary update

1296 gettblconf

1297 #err tsc=$tsc, tconfs="$tconfs"

1298 conf=`echo "$tconfs"|fgrep "$tsc"=`

1299 #err conf=$conf

1300 case ${conf#*=} in

1301 p*) primary=1 ;;

1302 f*) update=1 ;;

1303 u*) ;;

1304 m*) t=${t}_m;;

1305 s*) t=${t}_s;;

1306 esac

1307 #err t=$t

1308 type=string fn=""

1309 case $conf in

1310 */password)

1311 type=encoded ### val=`echo $val|encode`

1312 ;;

1313 */image*|*/document*)

1314 type=`file --mime-type - < "$val" | cut -d' ' -f2`

1315 bin="X'`hexize "$val"`'"

1316 ;;

1317 esac

1318 pkey=`echo "$tconfs"|grep "${t0}/.*=p"|sed 1q`

1319 pkey=${pkey#/*/} # cut $tbl/

1320 pkey=${pkey%=p/*} # cut =p/... -> primary key

1321 if [ "$primary" ]; then

1322 nulls=`echo "$tconfs"|grep "$t/.*=[fu]/"|sed 's/^.*/, NULL/'|tr -d '\n'`

1323 ###sq $db "replace into $t values(\"$val\"$nulls)"

1324 query "replace into $t values(\"$val\"$nulls);"

1325 elif [ "$update" ]; then

1326 query "update $1 set $c=\"$val\" where $pkey=$quotedp;"

1327 else

1328 query "replace into $t values($quotedp, \"$c\", \"$type\", \"$val\", \"$bin\");"

1329 fi

1330 )

1331 }

1332 expire() (

1333 at="${1:-$timeout}"

1334 FMT="${2:-%F %T}"

1335 TZ=GMT gdate -d "$at" +"$FMT"

1336 )

1337 addsession() {

1338 # expireをセット

1339 # loginの先にどの画面に行くかの状態遷移表書式を決める

1340 expire=`expire ${2:-"+1min"}`

1341 query "replace into session values('$1', '$expire');"

1342 # Remove old session parameters

1343 now=`expire now`

1344 query "delete from session where expire < '$now';"

1345 }

1346 gencookie() (

1347 path=${URL#*:/}

1348 path=${URL%/*}

1349 expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`"

1350 for kv; do

1351 # echo "Set-Cookie: $kv; expires=$expire; Path=$path"

1352 echo "Set-Cookie: $kv; expires=$expire;"

1353 done

1354 )

1355 contenttype() {

1356 echo "Content-type: ${1:-text/html; charset=utf-8}"

1357 contenttype() {} # Only need to work once

1358 }

1359 putheader() {

1360 :

1361 }

1362 putfooter() {

1363 _m4 -D_TITLE_="${TITLE:-$myname}" $layout/footer.m4.html

1364 }

1365 getcookie() {

1366 for kv in `echo $HTTP_COOKIE|sed 's/[;, ]/ /g'`; do

1367 k="${kv%%=*}"

1368 v="`echo ${kv#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1369 ## err "GetCookie: $k=[$v]"

1370 case "$k" in

1371 user) _user="$v" ;;

1372 skey) _skey="$v" ;;

1373 esac

1374 query "replace into cookie values('$session', '$k', 'string', \"$v\");"

1375 done

1376 }

1377 genrandom() {

1378 # $1=columns (default: 10)

1379 dd if=/dev/urandom count=1 2>/dev/null|nkf -MB \

1380 | tr -d '+=\n'|fold -w${1:-10}|sed -n 10p

1381 }

1382 genserial() {

1383 echo $((($(date +%s)-1433084400)/10))c$$

1384 }

1385 smail() {

1386 # smail rcpts subj (file)

1387 # $SMAIL_TO <- Recipient value of To: header

1388 # $MAIL_FROM <- From: header value

1389 from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr : /|tr -d '\n'`

1390 rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines

1391 ## Gmail rejects below(Duplicated headers)

1392 ##rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`

1393 rcptheader=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ','|sed 's/,$//'`

1394 subj=`echo $2|nkf -jM|tr -d '\n'`

1395 sender=${SENDER:-$admin}

1396 # Do not call m4 with directly passing text

1397 _r=$tmpd/rcpt

1398 echo -n "${SMAIL_TO:-$rcptheader}" > $_r

1399 replyto=${REPLYTO:+"Reply-to: $REPLYTO$LF"}

1400 (_m4 -D_RCPT_="spaste(\`$_r')" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

1401 cat $3 | nkf -jd ) | sendmail -f $sender $rcpt

1402 }

1403 smail_queue_flush() {

1404 # $1=timelimit

1405 timelimit=`query "SELECT datetime('now', 'localtime', '-6 hours');"`

1406 rowids=$(sq $workdb <<-EOF

1407 SELECT rowid FROM smailq

1408 GROUP BY rcpts, subj

1409 HAVING min(time) < '$timelimit';

1410 EOF

1411 )

1412 for rid in $rowids; do

1413 sq $workdb \

1414 "SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid in ($rid)" \

1415 | if IFS='|' read hexr hexs; then

1416 # err "hexrcpt=[$hexr] hexsubj=[$hexs]"

1417 rcpt=`echo "$hexr"|unhexize`

1418 subj=`echo "$hexs"|unhexize`

1419

1420 # err ROWID==$rowid "sql=<<SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid=$rowid;>>"

1421 # err "rcpt=[$rcpt] subj=[$subj]"

1422 if sq $workdb <<-EOF | smail "$rcpt" "$subj"

1423 .separator "\n" "------------------\n\n"

1424 SELECT time, text FROM smailq

1425 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1426 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid)

1427 ORDER by time;

1428 EOF

1429 then

1430 cat <<-EOF | sq $workdb

1431 DELETE FROM smailq

1432 WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)

1433 AND subj=(SELECT subj FROM smailq WHERE rowid=$rid);

1434 EOF

1435 fi

1436 fi

1437 done

1438 }

1439 smail_queue() {

1440 # $1=Rcpts, $@=subj

1441 now=`query "SELECT datetime('now', 'localtime');"`

1442 if [ $? -eq 0 ]; then

1443 rcpts="X'"`echo "$1"|hexize`"'"

1444 subj="X'"`echo "$2"|hexize`"'"

1445 text="X'"`cat | hexize`"'"

1446 err "smail_queue: rcpts=[$1] s=[$2] t:hex=[$text]"

1447 mintime=$(cat <<-EOF | sq $workdb

1448 CREATE TABLE IF NOT EXISTS smailq(rcpts, subj, text, time);

1449 INSERT INTO smailq VALUES($rcpts, $subj, $text, '$now');

1450 SELECT min(time) FROM smailq WHERE rcpts=$rcpts AND subj=$subj;

1451 EOF

1452 )

1453 ## XXX: Adhoc load mitigation

1454 case "$now" in

1455 *[01])

1456 err flush_queue=$mintime

1457 smail_queue_flush ;;

1458 esac

1459 fi

1460 }

1461 setviastring() {

1462 table=par

1463 oifs="$IFS"

1464 IFS="&"

1465 for us in $1; do

1466 k=${us%%=*}

1467 v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1468 setpar "$k" "string" "$v"

1469 done

1470 IFS="$oifs"

1471 }

1472 checkdomain() (

1473 # Check the validity of domain by referring DNS

1474 item=$1

1475 err checkdomain $1

1476 host ${item#*@} 1>&3 2>&3

1477 host ${item#*@} >/dev/null 2>&1

1478 )

1479 pwcheck() {

1480 # $1=passwd

1481 dbpswd=`getpwfield $user pswd`

1482 encpswd=`mycrypt "$1" "$dbpswd"`

1483 ## err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd

1484 [ x"$dbpswd" = x"$encpswd" ]

1485 }

1486 mypwhash() {

1487 mycrypt "`cat`" `genrandom 5`

1488 }

1489 flag_profupdate() {

1490 # XXX: Sorry to use undeclared column in user.def

1491 # This is useful to mitigate account sync load

1492 query <<-EOF

1493 REPLACE INTO user_s(name, key, type, val)

1494 VALUES('$user', 'profupdate', 'string', datetime('now', 'localtime'));

1495 EOF

1496 touch $userupdateflag

1497 }

1498 wasureta() {

1499 user=$1

1500 if ! checkdomain $user; then

1501 contenttype; echo

1502 _m4 -D_TITLE_='Invalid email' $layout/title-only.html

1503 echo "ユーザ名($user)には正しいメイルアドレスが必要です。" | html p

1504 putfooter

1505 exit 0

1506 fi

1507 newpswd=`genrandom` # newsalt=`genrandom 5`

1508 #encpswd=`mycrypt "$newpswd" "$newsalt"`

1509 encpswd=`echo $newpswd|mypwhash`

1510 dbsetbyid user $user pswd "$encpswd" && touch $userupdateflag

1511 # Avoid $user substitution with m4, because $url comes from user input.

1512 _m4 -D_PSWD_="$newpswd" -D_URL_="$url" -D_ADMIN_="$admin" \

1513 $msgdir/mail-newaccount.m4 \

1514 | sed "s/_USER_/$user/g" \

1515 | smail "`collectemail $user`" "New Account"

1516 }

1517 checkauth() {

1518 user=`getpar user`

1519 skc=`getpar skey` # from cookie

1520 [ -z "$user" ] && return 2

1521 ##skey="`getpwfield $user skey`"

1522 if [ -n "$skc" ]; then

1523 if chkskey "$skc"; then

1524 gencookie "user=$user" "skey=$skc" # 2021-12-24: Update expire

1525 return 0

1526 fi

1527 fi

1528 pswd=`getpar pswd`

1529 quser=`sqlquotestr "$user"`

1530 dbuser=`query "SELECT name FROM user WHERE name=$quser;"`

1531 if [ $? != 0 ]; then # Maybe DB locked

1532 return 4 # 4=server too heavy

1533 elif [ -z "$dbuser" ]; then

1534 err "Login USER failed: [$user]"

1535 return 2 # 2=login fail

1536 elif [ x"$pswd" = x"wasureta" ]; then

1537 if [ -n "$S4MASTERDB" ]; then

1538 contenttype; echo

1539 echo "Reset password is valid in BASE world." | html p

1540 echo "パスワードリセットはベースワールドでおこなってください。" | html p

1541 cat <<-EOF | html p

1542 <a href="$S4MASTERURL">BASE World</a>

1543 EOF

1544 exit 0 # XXXXX more smart...

1545 fi

1546 wasureta "$user"

1547 err wasureta issued for "$user"

1548 return 1 # wasureta error

1549 fi

1550 # dbpswd="`sq $db \"select pswd from passwd where name='$user'\"`"

1551 # putheader; echo; echo user=$user, db=$dbpswd, enc=$encpswd

1552 if pwcheck "$pswd"; then

1553 newsession=`genrandom 34`

1554 if setskey "$user" "$newsession" &&

1555 dbsetbyid user "$user" login "`date '+%F %T'`"; then

1556 gencookie "user=$user" "skey=$newsession"

1557 return 0

1558 else

1559 return 4 # Heavy load??

1560 fi

1561 fi

1562 err "Login failed: [$user]"

1563 return 2 # Password mismatch

1564 }

1565 showlogin() {

1566 args=`echo $myargs|tr ' ' '+'`

1567 test -z "$args" && resetskey

1568 s4name=${S4NAME:-s4}

1569 ( sed '/^<body/q' $layout/html.m4.html

1570 cat $layout/login.m4.html

1571 echo '</body></html>'

1572 ) | _m4 \

1573 -D_BODYCLASS_="login" \

1574 -D_TITLE_="$s4name" \

1575 -D_SYSNAME_="Welcome to $s4name" \

1576 -D_MYNAME_="$myname${args+?}$args" ${S4CSS:+-D_S4CSS_="$S4CSS"}

1577 exit 0

1578 }

1579 dologin() {

1580 test -n "$S4WORLD" && syncaccount

1581 checkauth

1582 st=$?

1583 if [ $st != 0 ]; then

1584 contenttype; echo

1585 _m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \

1586 -D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \

1587 $msgdir/login-fail-$st.m4.html

1588 showlogin # and EXIT

1589 fi

1590 err "Auth OK: [$user]"

1591 }

1592

1593 # Do instant jobs here

1594 dbsetup

1595 trap cleanup INT HUP EXIT TERM PIPE

1596 # trap cleanup INT HUP

1597

1598

1599 cgiinit() {

1600 tmpd=`tmpd=$tmpdir mktempd`

1601 tmpf=$tmpd/stream.$$

1602 tmpfiles=$tmpfiles" $tmpd"

1603 addsession $session

1604 getcookie

1605 case "$REQUEST_METHOD" in

1606 get|GET) s="$QUERY_STRING" ;;

1607 post|POST) ## dd count=$CONTENT_LENGTH bs=1 of=$tmpf 2>/dev/null #slow

1608 ## dd bs=$CONTENT_LENGTH count=1 of=$tmpf # NOT working

1609 # cat > $tmpf # too much?

1610 head -c $CONTENT_LENGTH > $tmpf # safe?

1611 err "CONTENT_LENGTH=$CONTENT_LENGTH$nl`ls -lF $tmpf`"

1612 s="`cat $tmpf`"

1613 ;;

1614 esac

1615 case "$CONTENT_TYPE" in

1616 *boundary*)

1617 bndry=${CONTENT_TYPE#*boundary=}

1618 #for us in `LC_CTYPE=C ./mpsplit.rb "$bndry" $tmpd < $tmpf`

1619 for us in `LC_CTYPE=C ./mpsplit.pl "$bndry" $tmpd < $tmpf`

1620 do

1621 k=${us%%\=*}

1622 #echo u=$us

1623 #v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"

1624 v="`echo ${us#*=}|unhexize|sed -e 's/\"/\"\"/g'`"

1625 case "$k" in

1626 *:filename)

1627 mimetype=`file --mime-type - < "$tmpd/$v"|cut -d' ' -f2`

1628 type='file'; k=${k%:filename}

1629 # DO NOT ALLOW Space and '|' in file names

1630 newv=`echo "$v"|sed 's/[ \|]/X/g'`

1631 if [ x"$v" != x"$newv" ]; then

1632 :

1633 fi

1634 err "k=[$k] v=[$v]" # debug@2020-05-31

1635 err "`ls -lF $tmpd/$v` MimeType=$mimetype"

1636 case "$mimetype" in

1637 [Ii]mage/x-xcf)

1638 bzip2 "$tmpd/$v"

1639 v=${v}.bz2

1640 ;;

1641 [Ii]mage/x-*|*/vnd.*) ;;

1642 [Ii]mage/[Hh][Ee][Ii][Ff])

1643 if type heif-convert >/dev/null 2>&1; then

1644 vjpg="${v%.heic}.jpg"

1645 err "Conv $v to $vjpg in $tmpd"

1646 convert -quality 75 -resize $maximagexy'>' \

1647 "$tmpd/$v" "$tmpd/$vjpg" >/dev/null 2>&1

1648 v=$vjpg

1649 else

1650 mimetype="Not supported"

1651 fi

1652 ;;

1653 [Ii]mage/*)

1654 mogrify -quality 75 -resize $maximagexy'>' "$tmpd/$v"

1655 err "Mogrified: `ls -lF $tmpd/$v`" # 2020-05-31

1656 ;;

1657 [Aa]pplication/[Pp][Dd][Ff])

1658 if [ x"`getpar comppdf`" = x"yes" ]; then

1659 if type gs_pdfwrite >/dev/null 2>&1; then

1660 nv=${v%.pdf}-compressed.pdf

1661 err Calling gs from $v to $nv

1662 if gs_pdfwrite "$tmpd/$v" "$tmpd/$nv"; then

1663 err "PDF compressed: `ls -lF $tmpd/*.pdf`"

1664 v=$nv

1665 fi

1666 fi

1667 fi

1668 esac

1669 if ! echo "$mimetype" | egrep "$file_accept_egrep" >/dev/null 2>&1

1670 then

1671 replpar text string " *添付できない形式です($v)* $file_warn"

1672 continue

1673 elif [ `wc -c < "$tmpd/$v"` -gt "$filesize_max" ]; then

1674 replpar text string \

1675 " *添付ファイル($v)は${filesize_max}バイト以下にしてください* $file_warn"

1676 continue

1677 fi

1678 ;;

1679 image|document|binary)

1680 # When no file name supplied to input[type="file"]

1681 continue ;;

1682 *)

1683 type='string'

1684 ;;

1685 esac

1686 #sq $db "replace into par values('$session', '$k', '$type', \"$v\")"

1687 setpar "$k" "$type" "$v"

1688 done

1689 ;;

1690 *)

1691 setviastring "$s"

1692 ;;

1693 esac

1694 }

1695 email4group() {

1696 # Get for-$1=group email address(es) for $2...=users

1697 qgrp=`sqlquote "$1"`; shift

1698 users=`for i; do sqlquote "$i"; echo; done`

1699 users=`echo $users|tr ' ' ','`

1700 sql="WITH

1701 grpemails AS (

1702 SELECT gname, user, val email

1703 FROM grp_mem NATURAL JOIN grp_mem_s

1704 WHERE key='email' AND gname=$qgrp),

1705 useremails AS (

1706 SELECT user.name, val email

1707 FROM user

1708 LEFT JOIN user_m

1709 ON user.name=user_m.name AND user_m.key='email')

1710 SELECT DISTINCT coalesce(g.email, u.email, u.name)

1711 FROM useremails u LEFT JOIN grpemails g

1712 ON u.name=g.user

1713 WHERE u.name in ($users);"

1714 query "$sql"

1715 }

1716 email4groupbyuid() {

1717 # Get for-$1=group email address(es) for $2...=user-ids

1718 g=$1; shift

1719 uids=`echo "$@"`

1720 uids=`echo $uids|tr ' ' ','`

1721 sql="SELECT DISTINCT name FROM user WHERE rowid IN ($uids);"

1722 email4group "$g" `query "$sql"`

1723 }

1724 myemail4group() {

1725 # Get my email address for $1-specified group

1726 email4group "$1" "$user" | sed -e 1q -e 's/[ ,].*//'

1727 }

1728 collectmembersbyid() {

1729 # Collect user names of group specified by grid

1730 rid=${1%%[!0-9]*} # Cleaning

1731 query "SELECT user FROM grp_mem \

1732 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid);"

1733 }

1734 collectmembersbyid() {

1735 # Collect user names of group name

1736 qgrp=`sqlquote "$1"`

1737 query "SELECT user FROM grp_mem WHERE gname=$qgrp;"

1738 }

1739 collectgecosesbyid() {

1740 # Collect user gecoses of group

1741 rid=${1%%[!0-9]*} # Cleaning

1742 query<<-EOF

1743 SELECT gecos

1744 FROM gecoses

1745 WHERE name IN (SELECT user FROM grp_mem

1746 WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid));

1747 EOF

1748 }

1749 collectemail() (

1750 # Collect email addresses for group $1

1751 # If $TEAM is set, filter by team name

1752 # If $EXCEPT is set as username(s) delimited by comma,

1753 # remove $EXCEPT from list: ....NOT IN ($EXCEPT)

1754 for e; do

1755 if isuser "$e"; then

1756 em=`query "select val from user_m where name='$e' and key='email';"`

1757 [ -n "$em" ] && echo "$em" || echo "$e"

1758 else

1759 qgrp=`sqlquote "$e"`

1760 if [ -z "$TEAM" ]; then

1761 gmem="grp_mem"

1762 else

1763 tm=`sqlquote "$TEAM"`

1764 gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"

1765 fi

1766 ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}

1767 sql="select coalesce(s.val,um.val,g.user) from

1768 $gmem g left join grp_mem_s s

1769 on g.gname=s.gname and g.user=s.user and s.key='email'

1770 left join user_m um on g.user=um.name and um.key='email'

1771 where g.gname=$qgrp $ex;"

1772 ## err CollectEmail: `echo "$sql"`

1773 query "$sql"

1774 fi

1775 done

1776 )

1777 sendinvitation() (

1778 # $1=email

1779 iss="invite-`date +%s`-$user"

1780 addsession $iss +${memoplimitdays}days # 1 week due date

1781 query "DELETE FROM par WHERE var='invite' AND val='$1';"

1782 query "REPLACE INTO par VALUES('$iss', 'invite', 'string', '$1');"

1783 gecos=`gecos`

1784 name=$user${gecos:+"($gecos)"}

1785 regist="$urlbase?reg+$iss"

1786 _m4 -D_URL_="$urlbase" \

1787 -D_USER_="$name" \

1788 -D_EMAIL_="$1" \

1789 -D_REGIST_="$regist" \

1790 -D_ADMIN_="$admin" \

1791 $msgdir/mail-invite.m4 \

1792 | smail $1 "SNSへの御招待"

1793 return 0

1794 )

1795 emaildomaincheck() {

1796 case "$1" in

1797 *@*@*) echo "無効なアドレスです"; return 1 ;;

1798 *@*)

1799 local=${1%@*} domain=${1#*@}

1800 if ! host $domain >/dev/null 2>&1; then

1801 echo "ドメイン($domain)が見付かりません。"

1802 return 2

1803 fi

1804 return 0

1805 ;;

1806 *) echo "正しいメイルアドレスをいれてください"; return 3 ;;

1807 esac

1808 }

1809 invite() {

1810 email=`getpar email | tr '[A-Z]' '[a-z]'`

1811 if [ -n "$INVITE_ONLYFROM" ]; then

1812 if ! echo "$user" | grep -E -e "$INVITE_ONLYFROM" >/dev/null 2>&1; then

1813 echo "招待可能ユーザに登録されていません" | html p

1814 return

1815 fi

1816 fi

1817 case "$email" in

1818 *@*@*|*\ *) repo="無効なアドレスです" ;;

1819 *@*)

1820 local=${email%@*} domain=${email#*@}

1821 if ! repo=`emaildomaincheck $email`; then

1822 repo="招待アドレスのエラー: $repo"

1823 elif [ -n "`query \"select * from user where name='$email';\"`" ]; then

1824 repo="$email さんは既に加入しています。"

1825 elif sendinvitation $email; then

1826 repo="アドレス($email)宛に案内を送信しました。"

1827 else # Cannot be reached here

1828 repo="自動登録できない状況です。管理者に依頼してください。"

1829 fi ;;

1830 "") repo="招待したい人のメイルアドレスを入力してください。" ;;

1831 *) repo="無効なアドレスです" ;;

1832 esac

1833 addr=`query "select val from par where sessid like 'invite-%-$user';"`

1834 if [ -n "$addr" ]; then

1835 susp="<h2>招待済みで加入待ちのアドレス</h2><pre>$addr</pre>"

1836 fi

1837 if [ -f $invite_policy ]; then

1838 pol="spaste(\`$invite_policy')"

1839 else

1840 pol="$invite_policy"

1841 fi

1842 _m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \

1843 -D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \

1844 -D_INVITE_POLICY_="$pol" \

1845 $layout/html.m4.html $layout/invite.m4.html

1846 }

1847 regist() {

1848 # $1=session-id-for-invitation

1849 _m4 -D_TITLE_="Invitation" $layout/html.m4.html

1850 if [ -z "$1" ]; then

1851 echo "bye bye" | html p

1852 reutrn

1853 fi

1854 email=`session=$1 getpar invite | tr '[A-Z]' '[a-z]'` # Ensure lower case

1855 if [ -z "$email" ];then

1856 cat<<EOF

1857 <p>無効な招待状チケットです。</p>

1858 <p>招待状の有効期限(1週間)が切れているか、チケット番号が異なっています。

1859 加入している人に、再度招待してもらいましょう。</p>

1860 EOF

1861 return

1862 fi

1863 echo "$email さんようこそ" | html h2

1864 query "replace into user values('$email');"

1865 # Fake login password to wasureta

1866 query "replace into par values('$session', 'pswd', 'string', 'wasureta'),

1867 ('$session', 'user', 'string', '$email');"

1868 wasureta $email

1869 echo "このアドレスに初期パスワードを送信しました。" |html p

1870 echo "新着メイルを確認してログインしてください。" |html p

1871 addsession $1 # for removal after 1 minute

1872 _m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \

1873 $layout/login.m4.html

1874 return

1875 }

1876 group_safename() {

1877 # Convert $1 to safe group name

1878 echo "$1" | tr -d '"'"'",

1879 }

1880 groupupdate() {

1881 gname=`getpar gname`

1882 qgname=`sqlquote "$gname"`

1883 if [ -n "$gname" ]; then

1884 # See ALSO same job in showgroup()

1885 newgname=`group_safename "$gname"`

1886 err newgname=$newgname

1887 if [ x"$newgname" != x"$gname" ]; then

1888 err NewGNAME: gname=$newgname

1889 gname=$newgname

1890 echo "使用禁止文字を除去し $gname としました。" | html p

1891 replpar gname string "$gname"

1892 fi

1893 # Name confliction check

1894 parow=`getpar rowid`

1895 ## err parow=$parow

1896 qgname=`sqlquote "$gname"` # Set again in case gname modified

1897 query "BEGIN EXCLUSIVE;"

1898 ## err "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;"

1899 count=$(query "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;")

1900 if [ $count -gt 0 ]; then

1901 echo "そのグループ名は既にあります。" | html p

1902 query "END;"

1903 return

1904 fi

1905 par2table $formdir/grp.def

1906 query "END TRANSACTION;"

1907 # Remove orphan

1908 : <<EOF

1909 select a.id,b.val from (select * from blog where id in

1910 (select id from blog_s where key='owner'

1911 and val not in (select name from user union select gname from grp)))

1912 a left join blog_s b on a.id=b.id and b.key='owner';

1913 EOF

1914 rm=`getpar rm` cfm=`getpar confirm`

1915 ## err groupupdate:::: after par2tbl rmcfm=$rm$cfm

1916 if [ x"$rm$cfm" = x"yesyes" ]; then

1917 if [ -z "`query \"select gname from grp where gname=$qgname;\"`" ]; then

1918 sql="delete from blog where id in

1919 (select id from blog_s where key='owner' and val=$qgname);"

1920 err rm-grp cleaning sql=`echo $sql`

1921 query "$sql";

1922 unsetpar tag kwd

1923 grps # When removing a group, switch to grp-list

1924 return # and return

1925 fi

1926 fi

1927 [ -z "$parow" ] && joingrp "$gname" "$user" yes "" as-admin

1928 fi

1929 sql="select rowid from grp where gname=$qgname;"

1930 grid=$(query $sql)

1931 ## err grpupdate:new-grid=$grid, sql=$sql

1932 grp $grid

1933 }

1934 groupclone() {

1935 # $1=grp-rowid of clone-base group

1936 rid=${1%%[!0-9]*} # Cleaning

1937 case "$1" in

1938 */noteam)

1939 noteam="AND key != 'team'" ;;

1940 esac

1941 qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$rid;"`

1942 if [ -z "$qgrp" ]; then

1943 echo "無効なグループIDです($1)" | html p

1944 return

1945 fi

1946 if ! isgrpownerbygid "$user" "$rid"; then

1947 echo "グループ管理者のみがクローン可能です" | html p

1948 return

1949 fi

1950 i=0

1951 while true; do

1952 copy="-copy$i"

1953 newqname=`query "SELECT quote($qgrp || '$copy');"`

1954 # err Trying new grp=$newqname with copy=$copy

1955 test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`

1956 if [ -n "$test" ]; then

1957 i=$((i++))

1958 continue

1959 fi

1960 break

1961 done

1962 # Creating New group "$newqname" with members of old group

1963 # err Creating new grp=$newqname with copy=$copy

1964 query<<-EOF

1965 BEGIN;

1966 INSERT INTO grp VALUES($newqname); -- Create NEW one

1967 REPLACE INTO grp_s(gname, key, val) -- Copy tag

1968 SELECT $newqname, key, val

1969 FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');

1970 REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"

1971 SELECT $newqname, key, type, val || '$copy'

1972 FROM grp_s WHERE gname=$qgrp AND key='gecos';

1973 -- Copy members and their configuration --

1974 REPLACE INTO grp_mem SELECT $newqname, user

1975 FROM grp_mem WHERE gname=$qgrp;

1976 REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin

1977 FROM grp_mem_s WHERE gname=$qgrp;

1978 REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin

1979 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1980 -- Copy administrators --

1981 REPLACE INTO grp_adm SELECT $newqname, user

1982 FROM grp_adm WHERE gname=$qgrp;

1983 COMMIT;

1984 EOF

1985 newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`

1986 STOPCLONEMSG=1 groupconf "$newrowid"

1987 }

1988 groupman() {

1989 note="<p>グループ名に使用できない文字は自動的に削除されます。</p>"

1990

1991 GF_STAGE="grpconf"

1992 GF_STAGE=groupupdate

1993 DT_VIEW=grp dumptable html grp 'gname gecos:DESC mtime:TIME' 'order by b.TIME desc' \

1994 |_m4 -D_TITLE_="グループ作成" \

1995 -D_FORM_="$note`genform $formdir/grp.def`" \

1996 -D_DUMPTABLE_="syscmd(cat)" \

1997 $layout/html.m4.html $layout/form+dump.m4.html

1998 }

1999 userconf() {

2000 [ -n "`getpar rowid`" ] && par2table $formdir/user.def

2001 _m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html

2002 GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user"

2003 }

2004 groupconf() {

2005 # $1=rowid in grp (2015-07-21 changed from gname)

2006 [ -n "`getpar rowid`" ] && par2table $formdir/grp.def

2007 _m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html

2008 #rowid=`query "select rowid from grp where gname='$1';"`

2009 rowid=${1%%[!A-Z0-9a-z_]*}

2010

2011 ### If user is not admin, lead to group home

2012 grp=`getgroupbyid $rowid`

2013 if ! isgrpowner "$user" "$grp"; then

2014 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2015 return

2016 fi

2017

2018 # GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804

2019 GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"

2020 if [ -z "$STOPCLONEMSG" ]; then

2021 ## Setup migration menu

2022 height="10em" ## Ugly!!

2023 if [ -n "$S4WORLDLIST" ]; then

2024 v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`

2025 err v=$v

2026 if [ -n "$v" ]; then

2027 migrate=$(cat<<-EOF

2028 `cgi_radio grpaction migrate id="migrate"`<label

2029 for="migrate">別Worldへ移住</label>

2030 <div style="height: $height;">

2031 <form action="?migrategrp">

2032 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

2033 <p>グループや掲示板のURLが変わります。

2034 外部からリンクしている場合は飛べなくなります。

2035 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

2036 「グループのクローン」で

2037 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

2038 <p><label>`cgi_checkbox emichk yes`確認</label></p>

2039 `cgi_hidden stage migrategrp`

2040 `cgi_hidden rowid $rowid`

2041 `cgi_submit OK`

2042 `cgi_reset Reset`

2043 </form>

2044 </div>

2045 EOF

2046 )

2047 fi

2048 fi

2049 html div 'class="foldtabs"' <<-EOF

2050 `cgi_radio grpaction clone id="clone"`<label

2051 for="clone">グループのクローン作成</label>

2052 <div style="height: $height;">

2053 <p>構成メンバーが同じ新規グループを作成します。</p>

2054 <table>

2055 <tr><td><a href="?groupclone+$rowid">

2056 <button>クローン作成(チームも複製)</button></a></td>

2057 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

2058 <tr><td><a href="?groupclone+$rowid/noteam">

2059 <button>作成(チームなし)</button></a></td>

2060 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

2061 </table>

2062 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

2063 削除してください。</p>

2064 </div>

2065 $migrate

2066 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2067 <div style="height: $height; background: transparent;"></div>

2068 EOF

2069 fi

2070 }

2071 migrategrp() {

2072 rowid=`getpar rowid`

2073 rowid=${rowid%%[!0-9]*}

2074 grp=`getgroupbyid $rowid`

2075 if ! isgrpowner "$user" "$grp"; then

2076 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2077 return

2078 fi

2079 if [ x`getpar emichk` != x"yes" ]; then

2080 echo "移住確認未チェックなので中止します。" | html p

2081 grp "$rowid"

2082 return

2083 fi

2084 destconf=`getpar migrateto`

2085 err destconf=$destconf

2086 if [ ! -e $destconf ]; then

2087 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2088 grp "$rowid"

2089 return

2090 fi

2091 if [ -n "$worldconf" ]; then

2092 srcconf=$worldconf

2093 else

2094 srcconf=s4-config.sh

2095 fi

2096 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2097 echo "移住操作" | html h1

2098 echo '<pre>'

2099 set -- "$srcconf" "$destconf" "$rowid"

2100 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2101 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2102 rc=$?

2103 echo "</pre>"

2104 if [ $rc -eq 0 ]; then

2105 echo "World [$world] への移住完了。" | html p

2106 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2107 elif [ -n "$faillist" ]; then

2108 echo "移住後削除失敗" | html p

2109 echo "空いている時間帯に再度試して下さい。" | html p

2110 else

2111 echo "移住失敗" | html p

2112 echo "移動先に重複がないか確認して下さい。" | html p

2113 fi

2114 return

2115 }

2116 mems() {

2117 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2118 kwd=`getpar kwd`

2119 listmember $kwd

2120 }

2121 grps() {

2122 case "$S4WORLD" in

2123 $nonewgroupworld) ;;

2124 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2125 esac

2126 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2127 kwd=`getpar kwd`

2128 listgroup "$kwd" \

2129 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2130 -D_TITLE_="グループ関連操作" \

2131 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2132 $layout/form+dump.m4.html

2133 }

2134 grp() { # $1=group-rowid

2135 gpg=`getpar grp`

2136 grid=${1:-$gpg}

2137 grp=`getgroupbyid "$grid"`

2138 ## . ./s4-blog.sh

2139 jg=`getpar joingrp`

2140 if [ -n "$jg" ]; then

2141 [ -n "$jg" -a -n "$grp" ] &&

2142 joingrp "$grp" "$user" "$jg" "`getpar email`"

2143 fi

2144 htmlheader=$layout/html.m4.html

2145 showgroup "$grid"

2146 }

2147 sql4interestblogs() {

2148 cat<<EOF

2149 CREATE TEMPORARY VIEW interestblogs AS

2150 SELECT blog.rowid rid, id, author

2151 FROM blog

2152 NATURAL JOIN

2153 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2154 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2155 THEN 1 -- blog owner is an user, READABLE when

2156 WHEN (SELECT user FROM grp_mem

2157 WHERE gname=bs.owner AND user='$user') IS NULL

2158 THEN 0

2159 ELSE 1

2160 END;

2161 EOF

2162 }

2163 listnewblogsql() { # $1=user

2164 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2165 newdays=${newdays%%[!0-9]*}

2166 newdays=${newdays:-14}

2167 basetime="datetime('now', 'localtime', '-${newdays} days')"

2168 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2169 WHERE user='$user'

2170 AND tblrowid IN

2171 ($blogreadflagrowid,

2172 $blogcutoffflagrowid)),

2173 $basetime -- "0"

2174 );"`

2175 cat<<EOF

2176 `sql4interestblogs`

2177 WITH article_ctime as (

2178 SELECT id,blogid,author,max(val) ctime

2179 FROM article join article_s s using(id)

2180 WHERE s.key='ctime' AND s.val > '$deftime'

2181 GROUP BY id

2182 ), blog_title_owner as (

2183 SELECT blg.rid brid, id,

2184 max(case key when 'title' then val end) title,

2185 max(case key when 'owner' then val end) owner

2186 FROM interestblogs blg, blog_s using(id) group by id

2187 ), blogall as (

2188 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2189 ), news as (

2190 SELECT brid, bl.id blid, bl.title, ctime,

2191 coalesce(al.time, '$deftime') atime,

2192 count(bl.id) "新着", bl.author

2193 FROM blogall bl

2194 LEFT JOIN

2195 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2196 ON bl.brid=al.tblrowid

2197 WHERE atime < bl.ctime

2198 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2199 LIMIT 10

2200 ) SELECT brid LINK, "新着",

2201 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2202 ctime, title,

2203 (SELECT gecos FROM gecoses WHERE name=author) gecos

2204 FROM news;

2205 EOF

2206 }

2207

2208 search_form() {

2209 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2210 # $2(optional) = pre-input keywords

2211 help="(1)空白区切りの単語で本文検索

2212 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2213 @2016-0[1-6] → 2016年1月から6月

2214 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2215 @week → 最近一週間

2216 (3)#番号で記事ID検索

2217 (1)と(2)は組み合わせOK

2218 例: @2016-10-0[1-9] 芋煮

2219 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2220 ※クイズ板は検索対象から外されます。"

2221 auth=""

2222 placeholder="全記事からの検索"

2223 case "$1" in

2224 author=*)

2225 a=`echo "${1#author=}"|htmlescape`

2226 g=`gecos ${1#author=}`

2227 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2228 placeholder="このユーザの書込検索"

2229 help="★★ $g さんの書き込みから検索します$nl$help"

2230 ;;

2231 grid=*)

2232 a=`echo "${1#grid=}"`; a=$((0 + $a))

2233 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2234 placeholder="このグループからの検索"

2235 ;;

2236 esac

2237 inikwd="$2" # no need to htmlescape

2238 cat<<-EOF

2239 <div class="right">

2240 <form action="$myname">$auth

2241 <input type="text" name="kwd" value="$inikwd" title="$help"

2242 placeholder=" $placeholder " width="10" accesskey="k">

2243

2244 ${touchpanel:+<p class="help">$help</p>}

2245 <input type="hidden" name="stage" value="searchart">

2246

2247 </form>

2248 </div>

2249 EOF

2250 }

2251

2252 imgsrc_cache() (

2253 # $1 = directory for cache'ing

2254 # $2 = table (user_m or grp_m)

2255 # $3 = keycond (was: condition for choosingowner)

2256 # $4 = size : S = Small, M = Medium, O = Original

2257 dir="$1" tbl="$2"

2258 keycond="$3"

2259 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2260 ORDER BY rowid DESC LIMIT 1"

2261 [ -d "$dir" ] || mkdir -p "$dir"

2262 tmpf=$tmpd/imgsrc_cache.$$

2263 case "$4" in

2264 [Ss]) size=S ;;

2265 [Oo]) size=O ;;

2266 *) size=M ;;

2267 esac

2268 # ImageCache filename storing schema:

2269 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2270 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2271 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2272 valtype=`query "$sql0"`

2273 filename=${valtype%%//*}

2274 filetype=${valtype##*//file:}

2275 if [ x"$filename" = x"${filename%.*}" ]; then

2276 # If nor filename extension found, set it to image type

2277 case "$filetype" in

2278 image/*) filename=$filename.${filetype#image/} ;;

2279 esac

2280 fi

2281 cacheimg_S=$dir/S_$filename

2282 cacheimg_M=$dir/M_$filename

2283 cacheimg_O=$dir/$filename

2284 cacheimg=$dir/${size}_$filename

2285 sumfile="$dir/$filename.sum"

2286 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2287 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2288 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2289 # if cache is fresh and has the same checksum,

2290 echo "<img src=\"$cacheimg\">"

2291 else

2292 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2293 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2294 fifo2=$fifo.2

2295 mkfifo $fifo $fifo2

2296 fmt=${filename##*.}

2297 ## [[ NOTE ]]

2298 ## a. convert oldimage newimage

2299 ## b. convert oldimage fmt:- | convert - newimage

2300 ## b is much smaller than a

2301 cat $tmpf | unhexize \

2302 | tee $fifo \

2303 | convert -define ${fmt}:size=${iconxy_M} \

2304 -resize ${iconxy_M}'>' - ${fmt}:- \

2305 | tee $fifo2 \

2306 | convert - "$cacheimg_M" &

2307 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2308 -resize ${iconxy_S}'>' - ${fmt}:- \

2309 | convert - "$cacheimg_S" &

2310 printf '%s' "<img src=\"data:${filetype},"

2311 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2312 echo '">'

2313 echo "$sum" > $sumfile

2314 fi

2315 ## Now preparing cache image, done.

2316 ## Store this information to DB

2317 stbl=${tbl%_m}_s # user_s or grp_s

2318 pkey=${keycond%%=*} # Primary Key name

2319 pval=${keycond#*=} # Primary Key value

2320 query <<-EOF

2321 REPLACE INTO $stbl($pkey, key, type, val)

2322 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2323 EOF

2324 )

2325

2326 showhome() {

2327 # $1=userRowIdToShow

2328 err showhome \$1=$1

2329 case "$1" in

2330 *@*) uname=`getvalbypkey user name "$1"` ;;

2331 *) uname=`getvalbyid user name $1` ;;

2332 esac

2333 ## err ShowHome: uname=$uname

2334 td=`getcachedir home/"$1"`

2335 gecos=`gecos "$uname"`

2336 ## err SH:gecos=$gecos

2337 GF_VIEWONLY=1

2338 cond="gname in (select gname from grp_mem where user='$uname')"

2339 search_form_args=""

2340 if [ x"$user" = x"$uname" ]; then

2341 if [ -z "$S4MASTERDB" ]; then

2342 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2343 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2344 elif [ -n "$S4MASTERURL" ]; then

2345 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2346 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2347 fi

2348 usermenu="$usermenu

2349 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2350 # Display folders

2351 sql="select count(id) from article_m where id

2352 in (select id from article where author='$user')

2353 and type like 'file:%';"

2354 ## err nfile-sql=`echo "$sql"`

2355 nfile=`query "$sql"`

2356 # err nfile=$nfile

2357 if [ $nfile -gt 0 ]; then

2358 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2359 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2360 fi

2361 else

2362 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2363 GROUP BY user;"`

2364 usermenu="<p>Last seen on $latestlog</p>"

2365 search_form_args="author=$uname"

2366 fi

2367 . ./s4-blog.sh

2368

2369 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2370 search_form "$search_form_args" > $sf

2371 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2372 | htmlescape > $tf

2373 { echo "<div class=\"noprofimg\">"

2374 viewtable $formdir/user.def user $1

2375 echo "</div>"

2376 } > $pf

2377

2378 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2379 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2380 imf=$tmpd/profimg.$$; touch $imf

2381 if [ -n "$img" ]; then

2382 if true; then

2383 tbl=user_m

2384 enticond="name='$uname'"

2385 imgsrc_cache "$td/main" user_m "$enticond" M

2386 else

2387 { printf '%s' "<IMG src=\"data:${img#file:},"

2388 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2389 | sed 's/$..$/%\1/g'

2390 echo '">'

2391 }

2392 fi > $imf

2393 fi

2394 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2395 val='$uname';"`

2396 listblog $uname > $bf

2397

2398 hometail=$tmpd/tail.$$

2399 mkfifo $hometail

2400

2401 #Calling listgroupbytable, originally here

2402

2403 (

2404 # Display Most Recent Entry

2405 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2406 shortval=${shortval:-val}

2407

2408 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2409 # $hidden_mode is defined in global section

2410 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2411 ctime,

2412 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2413 (SELECT gecos FROM gecoses

2414 WHERE name=(SELECT val FROM blog_s

2415 WHERE key='owner' AND id=b.id)) owner,

2416 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2417 FROM blog b

2418 JOIN

2419 (SELECT distinct blogid, a.id aid, max(val) ctime

2420 FROM article a, article_s s

2421 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2422 GROUP BY blogid ORDER BY val DESC LIMIT 50

2423 ) m

2424 ON b.id=m.blogid

2425 AND

2426 ('$uname' = '$user' -- user can read all own posts

2427 OR

2428 -- exclude posts of others in quiz/enquete blogs

2429 NOT EXISTS (SELECT * FROM blog_s

2430 WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"

2431 # This should be as follows

2432 : <<EOF

2433 WITH arts AS(

2434 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2435 a.blogid, a.id id, s.val ctime

2436 FROM article a NATURAL JOIN article_s s

2437 WHERE s.key = 'ctime' AND a.author='$user'

2438 GROUP by s.id

2439 )

2440 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2441 FROM arts a0

2442 JOIN

2443 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2444 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2445 ORDER BY ctime DESC LIMIT 50;

2446 EOF

2447

2448 cat<<-EOF

2449 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2450 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2451 <div class="lcto">

2452 `DT_VIEW=replyblog dumptable html blog`

2453 </div>

2454 EOF

2455 unset DT_SQL

2456 if [ x"$user" = x"$uname" ]; then

2457 # Display NEWS

2458 # 2016-06-26

2459 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2460 acclog blog $blogreadflagrowid

2461 # echo "全部既読にしました" | html p

2462 fi

2463 # 2016-02-19 Counting NEWS without using dumptable.

2464 sql=`listnewblogsql "$user"`

2465 # echo "$sql" > tmp/listnew

2466 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2467 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2468 cont=$((cont-1))

2469 ##err newcount=$cont

2470 if [ $cont -gt 0 ]; then

2471 #echo "全体の新着記事${cont}傑" | html h2

2472 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2473 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2474 cat<<-EOF | html form 'action="?home"'

2475 `cgi_checkbox readchk yes 'id="read"'`<label

2476 for="read">新着ふくめて全部読んだことにする</label>

2477 `cgi_submit '確定'`

2478 `cgi_hidden read yes`

2479 EOF

2480 echo "$new10 "

2481 echo "</div>"

2482 else # If news is 0, set log cut off flag

2483 acclog blog $blogcutoffflagrowid # for speed

2484 fi

2485 else # Not My Home ($user != $uname)

2486 : # DT_SQL=

2487 fi

2488 ) > $hometail & # Is background call safe to m4??

2489 #

2490 listgroupbytable $formdir/grp.def "$cond" $uname |

2491 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2492 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2493 -D_PROFIMG_="spaste(\`$imf')" \

2494 -D_BLOGS_="spaste(\`$bf')" \

2495 -D_SEARCH_="spaste(\`$sf')" \

2496 -D_NBLOG_="$nblog" \

2497 -D_GROUPS_="syscmd(\`cat')" \

2498 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2499 $layout/html.m4.html $layout/home.m4.html

2500

2501 # Record access log

2502 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2503 }

2504 commission() { # $1=grp-rowid $2=user-rowid

2505 contenttype; echo

2506 ## err commission: "$@"

2507 gname=`getgroupbyid $1`

2508 echo "グループ $gname 管理者委任" \

2509 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2510 if [ -n "$2" ]; then

2511 grp_reg_adm "$@"

2512 else

2513 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2514 fi

2515 }

2516 listgroupbytable() {

2517 # $1=deffile $2...=condition $3(optional)=uname

2518 tagline=`grep :tag: $1`;

2519 and="${2:+and }" where=${2:+where }

2520 href="<a href=\"$myname?grp+"

2521 echo '<div class="listgroup">'

2522 sql="select val from grp_s where key='tag' $and$2 group by val;"

2523 ## err ListGRP: query sql="$sql"

2524 for tag in `query "$sql"`

2525 do

2526 ## err ListGrp: tag=$tag

2527 tn=${tagline%%=${tag}*}

2528 tn=${tn##*[ :]}

2529 sql="select rowid||':'||gname as 'グループ名',説明 from

2530 (select (select rowid from grp g where g.gname=grp_s.gname)

2531 as rowid,

2532 gname,

2533 max(case key when 'gecos' then val end) as '説明',

2534 max(case key when 'tag' then val end) as 'tag',

2535 max(case key when 'mtime' then val end) as mtime from grp_s

2536 $where$2 group by gname having tag='$tag' order by mtime desc);"

2537 ## err PersonalGroupList= `echo $sql`

2538 echo "<h2>$tn</h2>"

2539 echo '<table class="b listgroup">'

2540 sq -header -html $db "$sql" \

2541 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2542 echo '</table>'

2543 done

2544 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2545 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2546 peekgrpworlds mem:"$3"

2547 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2548 fi

2549 echo '</div>'

2550 }

2551 iconhref() (

2552 # $1=icon-file, $2=Href $3=title $4...=anchor

2553 data=`percenthex "$1"`

2554 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2555 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2556 href=$2; title=$3; shift 3

2557 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2558 )

2559 iconhref2() (

2560 # $1=icon-file, $2=Href $3=title $4...=anchor

2561 src=$1

2562 href=$2; title=$3; shift 3

2563 anchor=`echo $@|htmlescape`

2564 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2565 )

2566 listentry() (

2567 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2568 # Referring variable $iamowner=$grp to attach owner-request links

2569 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2570 cond='' hiddens=''

2571 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2572 if [ -z "$offset" ]; then

2573 offset=`getpar start`; offset=${offset%%[!0-9]*}

2574 offset=$((offset-1))

2575 fi

2576 offset=$((offset + 0)) # change to numeric forcibly

2577 [ $offset -lt 0 ] && offset=0

2578 limit=$listentlimit

2579 dir=`getcachedir "$1"`

2580 if [ x"$1" = x"user" ]; then

2581 hrb="$myname?home"

2582 deficon=person-default.png

2583 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2584 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2585 gcs=gecos

2586 else # if group

2587 hrb="$myname?grp"

2588 deficon=group-default.png

2589 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2590 gcs=name

2591 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2592 if [ -n "$tagline" ]; then

2593 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2594 ## err tagconv=$tagconv

2595 fi

2596 fi

2597 if [ ! -d $dir ]; then

2598 mkdir -p $dir

2599 fi

2600 if [ ! -s $dir/$deficon ]; then

2601 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2602 fi

2603 kwd=${2:-`getpar kwd`}

2604 if [ -n "$kwd" ]; then

2605 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2606 case "$kwd" in

2607 mem:*@*)

2608 byuser=${kwd#*mem:}

2609 qusr=`sqlquote "$byuser"`

2610 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2611 ;;

2612 esac

2613 if [ x"$1" = x"group" ]; then

2614 if [ -n "$cond1" ]; then

2615 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2616 else

2617 cond1="(b.name like '%${kwd}%')"

2618 fi

2619 else

2620 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2621 fi

2622 fi

2623 tag=`getpar tag` tag2=`getpar tag2`

2624 if [ x"$tag" = x"NULL" ]; then

2625 tag="" tag2=""

2626 fi

2627 if [ -n "$tag$tag2" ]; then

2628 tag=${tag:-$tag2}

2629 qtag=`sqlquote "$tag"`

2630 cond2="tag=$qtag"

2631 fi

2632 if [ -n "$cond1$cond2" ]; then

2633 cond="$cond1${cond2:+ AND $cond2}"

2634 cond="WHERE ${cond# AND }"

2635 fi

2636

2637 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2638 # grpは呼出し元の動的スコープ変数でよくないな...

2639 ##qgrp=`sqlquote $grp`

2640 getgrp="(select gname from grp where rowid=${rowid:--1})"

2641 sql="select a.rowid, a.$link,

2642 coalesce(b.$gcs, a.$nm) as nick,

2643 quote(a.$nm) as qname,

2644 (SELECT val FROM ${tbl}_s

2645 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2646 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2647 || case when a.$nm in (select gname from grp)

2648 then printf('(%d名)',

2649 (select count(user) from grp_mem where gname=a.$nm))

2650 else ' <'||a.$nm||'>'

2651 end

2652 as name,

2653 b.tag,

2654 case when a.$nm in (select user from grp_adm

2655 where gname=$getgrp) then '管理者'

2656 when '$user' in (select user from grp_adm where gname=a.$nm)

2657 then 'ADMIN'

2658 when '$user' in (select user from grp_mem where gname=a.$nm)

2659 then 'Member'

2660 when '$iamowner' = '' then ''

2661 else ',not='||a.rowid end as ownerlink,

2662 CASE '$entity'

2663 WHEN 'グループ'

2664 THEN coalesce(

2665 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2666 'open')

2667 ||

2668 CASE WHEN '$user'

2669 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2670 THEN ' ismember'

2671 ELSE ''

2672 END

2673 ELSE 'user'

2674 END regmode

2675 from $tbl a left join

2676 (select $nm as name,

2677 max(case key when 'gecos' then val end) as gecos,

2678 max(case key when 'tag' then val end) as tag,

2679 max(case key when 'mtime' then val end) as mtime,

2680 max(case key when 'wtime' then val end) as wtime,

2681 max(case key when 'login' then val end) as login

2682 from ${tbl}_s group by $nm)

2683 b on a.$nm=b.name $cond $3

2684 order by b.wtime desc, b.login desc,

2685 b.mtime desc, b.tag desc, a.rowid asc"

2686 # Give precedence to newer maintained groups (2016-09-24)

2687 # Note that mtime is stored only in grp_s.

2688 ## err LE:sql.1="$sql"

2689 total=`query "with x as ($sql) select count(*) from x;"`

2690 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2691 echo '<div class="listentry">' # List-entry div

2692 # Show owner/member filter button

2693 METHOD=GET

2694 hiddens="$hiddens

2695 `cgi_hidden stage \"$stage\"`"

2696 if [ x"$tbl" = x"grp" ]; then

2697 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2698 fh="<select name=\"tag\">$nl"

2699 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2700 for l in $args; do

2701 val=${l#*=} tname=${l%=*}

2702 if [ x"$val" = x"$tag" ]; then

2703 s=" selected"

2704 selectedtags="(種別[${tname}]のみ)"

2705 else

2706 s=""

2707 fi

2708 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2709 done

2710 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2711 cat<<-EOF

2712 <form action="$myname" method="$METHOD">

2713 </form>

2714 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2715 for="ismembtn">参加中以外隠す</label>

2716 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2717 for="isadmbtn">管理者参加以外隠す</label>

2718 EOF

2719 # limit=3

2720 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2721 fi

2722 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2723 echo '<div>'

2724 METHOD=GET cgi_form $stage <<EOF

2725 $form

2726 <label>次の語を含む${entity}で検索:

2727 `cgi_text kwd "$kwd"`</label>

2728 $hiddens

2729 EOF

2730 echo '</div>'

2731 else

2732 echo $selectedtags | html p

2733 fi

2734 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2735 cat<<EOF

2736 <form action="$myname" method="$METHOD">

2737 <p>${total}件中の<input class="hidesub" type="text" name="start"

2738 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2739 <input type="submit" value="確定"></p>

2740 </form>

2741 EOF

2742 if [ $((offset+limit)) -lt $total ]; then

2743 nextbtn=$(

2744 cat<<EOF

2745 <div class="right clear"><form action="$myname" method="$METHOD">

2746 `cgi_submit 次の${limit}件`

2747 $hiddens

2748 `cgi_hidden offset $((offset + limit))`</form></div>

2749 EOF

2750 )

2751 fi

2752 if [ $offset -gt 0 ]; then

2753 prevbtn=$(

2754 cat<<EOF

2755 <form action="$myname" method="$METHOD">

2756 `cgi_submit 前の${limit}件`

2757 $hiddens

2758 `cgi_hidden offset $((offset - limit))`</form>

2759 EOF

2760 )

2761 fi

2762 pnbtn="$nextbtn$prevbtn"

2763 echo $pnbtn

2764

2765 ## err ListEntry: `echo "$sql"\;`

2766 # sq $db here??? 2016-11-28

2767 query "$sql limit $limit ${offset:+offset $offset};" \

2768 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2769 # err name=$name owner=$ownerp lnk=$lnk

2770 # err newlnk=$lnk regmode=$regmode

2771 icondir=$dir/$id

2772 # Pick up only last icon

2773 htmlname=`echo $name|htmlescape`

2774 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2775 <p class=\"tag _$tag\">$tag</p>" \

2776 | _m4 $tagconv

2777 if [ -n "$NOSPEEDUP" ]; then

2778 files=`getvalbyid $tbl profimg $id $icondir`

2779 if [ -n "$files" ]; then

2780 icon=`echo "$files"|tail -1`

2781 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2782 else

2783 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2784 fi

2785 elif [ -n "$icon" -a -s "$icon" ]; then

2786 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2787 else

2788 cond="$nm=$qname"

2789 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2790 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2791 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2792 # err "img=[$img]"

2793 if [ -n "$img" ]; then

2794 echo "<a href=\"$hrb+$lnk\">"

2795 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2796 echo "</a>"

2797 else

2798 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2799 fi

2800 fi

2801 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2802 echo "</div>"

2803 done

2804 echo "</div>" # End of List-entry div

2805 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2806 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2807 peekgrpworlds "$kwd"

2808 fi

2809 )

2810 listmember() {

2811 listentry user "$@"

2812 }

2813 listgroup() {

2814 listentry group "$@"

2815 }

2816 hexteams() { # $1=gname, $2(optional)=user

2817 cond=${2:+" AND user='$2'"}

2818 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2819 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2820 }

2821 showgroup() { # $1=group-rowid

2822 if [ -z "$1" ]; then

2823 grid=`getpar grid`

2824 grid=${grid%%[!0-9]*}

2825 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2826 else

2827 grid=$1

2828 fi

2829 grp=`getgroupbyid $grid`

2830 qgrp=`sqlquote "$grp"`

2831 htmlgrp=`echo "$grp"|htmlescape`

2832 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2833 if isgroup "$grp"; then

2834 tf=$tmpd/title.$$

2835 sf=$tmpd/search.$$

2836 bodyclass=`query "SELECT val FROM grp_s

2837 WHERE gname=$qgrp AND key='regmode';"`

2838 if ismember "$user" "$grp"; then

2839 ismember="ismember"

2840 bodyclass="$bodyclass${bodyclass:+ }ismember"

2841 else

2842 ismember="" # bodyclass="group"

2843 fi

2844 bodyclass="$bodyclass grouphome"

2845 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2846 echo "グループ $htmlgrp" > $tf

2847

2848 showgroupsub $formdir/grp.def "$grid" | \

2849 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2850 -D_FORM_="syscmd(\`cat')" \

2851 -D_BODYCLASS_="$bodyclass" \

2852 -D_DUMPTABLE_="" \

2853 $htmlheader $sf $layout/form+dump.m4.html

2854 # $htmlheader $layout/form+dump.m4.html

2855 # $htmlheader is defined in grp()

2856 else # if $grp is removed at par2table

2857 listgroup

2858 fi

2859 }

2860 showgroupsub() {

2861 # $1=def-file $2=group-rowid

2862 # Using $ismember

2863 rowid=$2

2864 grp=`getgroupbyid $2`

2865 qgrp=`sqlquote "$grp"`

2866 td=`getcachedir grp/"$2"`

2867 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2868 if [ -z "$rowid" ]; then

2869 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2870 #grp=`sq $db "select gname from grp where rowid=$grp"`

2871 echo "showgroupsub: invalid argument($1 $2)" | html p

2872 return

2873 fi

2874 val=`getvalbyid grp profimg $rowid $tmpd`

2875 enticond="gname=$qgrp"

2876 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2877 if [ -n "$img" ]; then

2878 cat<<-EOF

2879 <p class="groupimg">

2880 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2881 EOF

2882 fi

2883 echo "<div class=\"noprofimg\">"

2884 viewtable $1 grp $rowid

2885 echo "</div>"

2886 if isgrpowner "$user" "$grp"; then

2887 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2888 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2889 iamowner=$rowid

2890 colmd=" mode"

2891 # We have to specify report-type blog to lshandoutall

2892 repblog=`query "

2893 WITH grpblogs AS (

2894 SELECT id FROM blog_s

2895 WHERE key='owner' AND val=(SELECT gname FROM grp WHERE rowid=$rowid)

2896 ), ownerMode AS (

2897 SELECT id,

2898 max(CASE key WHEN 'owner' THEN val END) owner,

2899 max(CASE key WHEN 'mode' THEN val END) mode

2900 FROM blog_s

2901 GROUP BY id

2902 ), blogid AS (

2903 SELECT id

2904 FROM grpblogs NATURAL LEFT JOIN ownerMode

2905 WHERE mode LIKE 'report%' LIMIT 1

2906 ) SELECT blog.rowid FROM blogid NATURAL LEFT JOIN blog;"`

2907 if [ -n "$repblog" ]; then

2908 csvid="gethandoutcsv"

2909 replink="/ <a href=\"?lshandoutall+$repblog#$csvid\">レポート全集計</a>"

2910 fi

2911 fi

2912 if [ -n "$ismember" ]; then

2913 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2914 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2915 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2916 cat<<-EOF

2917 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2918 href="?blog+$rowid">グループの新規話題作成</a>

2919 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2920 href="?grpaction+$rowid">メンバーを個別選択しての操作</a>

2921 $replink</p>

2922 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2923 <div class="fold clear">

2924 `cgi_checkbox send yes id="send"`<label

2925 for="send">グループ全員にメッセージ送信</label>

2926 <div>

2927 `cgi_textarea message "" "cols=60"`

2928 `cgi_submit 送信`

2929 `cgi_reset リセット`

2930 </div>

2931 `cgi_hidden grp $rowid`

2932 </div></form>

2933 EOF

2934 fi

2935 # 加入ボタン + 加入者リスト

2936 if [ -n "$ismember" ]; then

2937 ismem='checked' state="(参加中)"

2938 else

2939 nomem='checked' state="(現在非加入)"

2940 fi

2941 # このグループでの加入アドレス

2942 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2943 and key='email';"`

2944 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2945 ## and key='email';"

2946 ##err email=$eml

2947 cat <<EOF

2948 <div class="fold clear">

2949 `cgi_checkbox reg yes id="reg"`<label

2950 for="reg">自身の加入状態を操作する</label>$state

2951 <div>

2952 EOF

2953 cgi_form grp <<EOF

2954 <p>このグループに</p>

2955 <table class="b">

2956 <tr><th>メンバーとして</th><td>

2957 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2958 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2959 <tr><th>参加する場合のメイルアドレス<br>

2960 <small>(メインのアドレスとは違うものにする場合に記入<br>

2961 同じでよい場合は空欄に)</small></th>

2962 <td>`cgi_text email $eml`</td></tr>

2963 </table>

2964 `cgi_hidden grp $rowid`

2965 EOF

2966 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2967 echo "moderated (承認加入の)グループなので実際に参加できるのは

2968 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2969 fi

2970 echo '</div></div>'

2971 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2972 cat<<-EOF

2973 <div class="fold">

2974 <h2>話題一覧</h2>

2975 <form class="summary inline" action="$myname" title="$thelp">

2976 `cgi_hidden owner "$grp"`

2977 `cgi_hidden kwd "@week"`

2978 `cgi_hidden stage searchart`

2979 `cgi_submit "一週間のまとめ"`

2980 </form>

2981 <input type="checkbox" id="dtcheck" checked><label

2982 for="dtcheck">話題一覧表示</label>

2983 EOF

2984 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2985 colstate="state:稼動状態:frozen=rowclass=凍結"

2986 frzbtn='<button class="toggle-frozen"></button>'

2987 if [ -n "$ismember" ]; then

2988 DT_COUNT=author DT_COUNT_HEADER="書" DT_COUNT_BY=$user

2989 fi

2990 DT_CHLD=article:blogid \

2991 DT_QOWNER="$qgrp" \

2992 DT_VIEW=replyblog dumptable html blog \

2993 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2994 | if [ -n "$iamowner" ]

2995 then

2996 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2997 else

2998 cat

2999 fi

3000 ## DO not convert to frzbtn when not admin

3001 echo "</div> "

3002

3003 getgname="(select gname from grp where rowid=$rowid)"

3004 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

3005 cm="?commission+$rowid"

3006 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

3007 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

3008 # team list

3009 hexteams=`hexteams "$grp"`

3010 if [ -n "$hexteams" ]; then

3011 echo "チーム一覧" | html h2

3012 echo '<div class="dumptable"><table class="b">'

3013 sq $db -html -header<<-EOF

3014 SELECT val TEAM,

3015 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

3016 MEMBERS

3017 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

3018 EOF

3019 echo '</table></div>'

3020 fi

3021 }

3022 grp_getbodyclass() {

3023 # Get css class name for document.

3024 # `moderated' for moderated groups

3025 # `ismember' for groups where user belongs

3026 # $1=GroupName (w/o quote)

3027 # $user=userNameCurrentlyLogin

3028 ## err grp_getbodyclass: 1="$1"

3029 qgrp=`sqlquote "$1"`

3030 query<<-EOF

3031 SELECT coalesce(

3032 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

3033 'open')

3034 ||

3035 CASE WHEN '$user'

3036 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

3037 THEN ' ismember'

3038 ELSE ''

3039 END;

3040 EOF

3041 }

3042 grpaction() { # $1=group-rowid

3043 err GRP_ACTION:IN

3044 grid=${1:-`getpar grp`}

3045 grp=`getgroupbyid "$grid"`

3046 htmlgrp=`echo "$grp" | htmlescape`

3047 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3048 if [ -z "$grp" ]; then

3049 echo "無効な指定です。" | html p; return

3050 fi

3051 if ! ismember $user "$grp"; then

3052 echo "加入者のみに許可された操作です。" | html p; return

3053 fi

3054 isowner=""

3055 isgrpowner "$user" "$grp" && isowner="yes"

3056 err 2=$2 3=$3

3057 case "$2" in

3058 get:teamcsv)

3059 teamcsv "$grid"

3060 return

3061 ;;

3062 esac

3063 echo "グループ $grp 個別選択操作" \

3064 | _m4 -D_TITLE_="syscmd(\`cat')" \

3065 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

3066 $layout/html.m4.html

3067

3068 usel=`getpar usel`

3069 if [ -n "$usel" ]; then

3070 uids=$(echo `echo $usel`|tr ' ' ',')

3071 ## err grpaction-1: grp=$grp, `echo $sql`

3072 text=`getpar text|tr -d '\r'`

3073

3074 rm=`getpar rm` cfm=`getpar confirm`

3075 ## err rm=$rm cfm=$cfm

3076 if [ x"$rm" = x"yes" ]; then

3077 if [ "$isowner" ]; then

3078 if [ x"$rm$cfm" = x"yesyes" ]; then

3079 # Eliminate

3080 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3081 for tbl in grp_mem grp_mem_s grp_mem_m grp_adm grp_adm_s grp_adm_m

3082 do

3083 sql="delete from $tbl $cond;"

3084 # echo "sql=$sql"

3085 query "$sql"

3086 err rmGRPuser "$sql"

3087 done

3088 num=`query "select count(*) from user where rowid in ($uids);"`

3089 #err num=$num

3090 if [ 0$num -gt 0 ]; then

3091 sql="select coalesce(b.val,a.name) from user a left join \

3092 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3093 # err `echo "$sql"`

3094 html pre<<EOF

3095 以下の${num}名のグループ $grp 登録を解除しました。

3096 `query "$sql"`

3097 EOF

3098 fi

3099 else

3100 echo "確認のチェックがないのでやめておきます。" | html p

3101 return

3102 fi

3103 else # not Group Owner

3104 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3105 return

3106 fi

3107 cat<<EOF

3108

3109 EOF

3110 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3111 if [ -z "$text" ]; then # if msg is empty

3112 echo "なにかメッセージを..." | html p

3113 return 0

3114 fi

3115 gecos=`gecos $user`

3116 safegc=`echo "$gecos" | tr -d '<>@,'`

3117 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3118 fromad=`myemail4group "$grp"`

3119 ###mail_from="$safegc <$fromad>"

3120 mail_from="$safegc <$user>" # TEST: 2020/5/13

3121 test -n `getpar sender` &&

3122 export SENDER=$user # TEST: 2020/5/15

3123 replyto=$fromad

3124

3125 ## Start parse of attachment files

3126 if [ -n "`getpar email`" ]; then

3127 ar=`getpar supprcpt`

3128 if [ -n "$ar" ]; then

3129 for a in $ar; do

3130 if checkdomain "$a"; then

3131 supprcpt="$supprcpt $a"

3132 else

3133 err "SupprcptErr=[$a] by $user"

3134 removercpt="$removercpt $a"

3135 fi

3136 done

3137 fi

3138 subj=`getpar subject`

3139 afiles=""

3140 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3141 do

3142 f=$tmpd/$fn

3143 if [ -s $f ]; then

3144 afiles=$afiles"${afiles:+ }$f"

3145 fi

3146 done

3147 else

3148 preface=$(cat <<-EOF

3149 $url

3150 のグループ「$grp」のメンバーである $gecos さんから、

3151 あなた宛のメッセージです。

3152 ----------------------------------------------------------

3153 EOF

3154 )

3155 fi

3156 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3157 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3158 subj="${subj:-$gecos さんからのメッセージ}"

3159 REPLYTO=$replyto

3160 MAIL_FROM=$mail_from

3161 export REPLYTO SMAIL_TO MAIL_FROM

3162 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3163 for r in $rcpts; do

3164 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3165 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3166 else

3167 # Show sender and rcpts address for guest

3168 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3169 fi

3170 if [ -n "$afiles" ];then

3171 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3172 else

3173 smail "$r" "$subj"

3174 fi <<EOF

3175 ${preface:+$preface$nl}$text

3176 EOF

3177 done

3178 if [ $? = 0 ]; then

3179 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3180 sql="select coalesce(b.val, a.name) from

3181 (select name from user where rowid in ($uids)) a

3182 left join user_s b on a.name=b.name and b.key='gecos';"

3183 html pre<<EOF

3184 `query "$sql"`

3185 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3186 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3187 EOF

3188 err SendDone: `echo $sql`

3189 fi

3190 elif [ x"$rm" = x"commission" ]; then

3191 grp_reg_adm $grid $usel

3192 elif [ x"$rm" = x"addteam" ]; then

3193 team=`getpar team|sed "s/'/''/g"` # for single quotation

3194 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3195 if [ x"$team" != x"$newteam" ]; then

3196 echo "チーム名に使えない文字を除去しました" | html p

3197 team=$newteam

3198 fi

3199 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3200 cat<<-EOF | html p

3201 有効なチーム名を入力してください。

3202 カンマだけ、「なし」という名前は使えません。

3203 EOF

3204 echo "有効なチーム名を入力してください。" | html p

3205 else

3206 grp_add_team $grid "$team" $usel

3207 fi

3208 elif [ x"$rm" = x"rmteam" ]; then

3209 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3210 rmteam=`getpar rmteam|sed "s/'/''/g"`

3211 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3212 gname='$grp' AND user='$user' AND key='team'\

3213 AND val='$rmteam';\"`" ]; then

3214 grp_rm_team $grid "$rmteam" $usel

3215 else

3216 echo "所属していないチームの除去操作はできません。"|html p

3217 fi

3218 else

3219 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3220 fi

3221 fi

3222 fi

3223 # POST count summary

3224 from=`getpar from`; to=`getpar to`

3225 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3226 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3227 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3228 # New entry

3229 sql="WITH mems AS (

3230 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3231 ON gm.user=g.name

3232 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3233 ), target_article AS (

3234 SELECT id FROM article_s

3235 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3236 ), posts AS (

3237 SELECT author, count(author) post

3238 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3239 WHERE blogid IN (SELECT id FROM blog_s

3240 WHERE key='owner'

3241 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3242 AND key='text'

3243 GROUP BY author

3244 ), teams AS (

3245 SELECT user, group_concat(val, ', ') team

3246 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3247 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3248 AND key='team'

3249 GROUP BY user

3250 ), user_post AS (

3251 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3252 FROM mems m LEFT JOIN posts

3253 ON m.name=posts.author

3254 GROUP by m.rowid

3255 ), user_view AS (

3256 SELECT user vuser,count(user) cnt

3257 FROM tblaccesses

3258 WHERE user IN (

3259 SELECT user FROM grp_mem

3260 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3261 AND tbl='blog'

3262 AND tblrowid IN (

3263 SELECT rowid FROM blog

3264 WHERE id IN (

3265 SELECT id FROM blog_s

3266 WHERE key='owner' AND val=(

3267 SELECT gname FROM grp WHERE rowid=$grid)))

3268 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3269 GROUP BY user ORDER BY cnt

3270 )

3271 SELECT

3272 CASE

3273 WHEN (SELECT user FROM grp_adm

3274 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3275 AND user=up.name) IS NOT NULL

3276 then 'k'

3277 ELSE ''

3278 END || rowid || ','

3279 || rtrim(substr(name, 1, instr(name, '@')), '@') UID,

3280 gecos NAME,

3281 post POST,

3282 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3283 team _TEAM_

3284 FROM user_post up LEFT JOIN teams t

3285 ON up.name=t.user

3286 ORDER BY up.name;"

3287 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3288 ## err grpaction: "`echo \"$sql\"`"

3289 tf=$tmpd/title.$$

3290 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3291 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3292 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3293 for=\"cmadmin\">管理者委任</label>

3294 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3295 </p></div>"

3296 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3297 title=\"Shortcut: g${nl}Remove from the Group\"

3298 for=\"conf\">GRP登録解除</label>

3299 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3300 <p>この操作による通知は本人に行きません。

3301 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3302 </div>"

3303 submitBTN=`cgi_submit 送信`

3304 # Get team list to which current user belongs into $hexteams

3305 allhexteams=$(hexteams "$grp")

3306 if [ -n "$isowner" ]; then

3307 myhexteams="$allhexteams" # admin can remove all teams' attr

3308 else

3309 myhexteams=$(hexteams "$grp" "$user")

3310 fi

3311 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3312 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3313 fi

3314 if [ -n "$myhexteams" ]; then

3315 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3316 title=\"Shortcut: s${nl}Strip a team tag from\"

3317 for=\"cmrmteam\">チーム属性除去</label>

3318 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3319 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3320 <p>この操作による通知は本人に行きません。

3321 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3322 </div>

3323 "

3324 fi

3325 stf=$tmpd/selteam.$$

3326 cgi_select_h selteam "5445414d" $allhexteams > $stf

3327 b1='<label> <input type="checkbox" name="usel" value="'

3328 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3329 br='<span id="reverse" title="Reverse Selection"></span>'

3330 # lnk='"> <span></span></label> [<a href="?home+\3">HOME</a>]'

3331 lnk='<a href="?home+\3">\5</a>'

3332 # (1) Join <TR> line and the next

3333 # (2) (<TR><TD)>(k?)(1234),(userid)</TD><TD>(GECOS)</TD>

3334 # ↓

3335 # <TR><TD>\2<label><input ...value="\3">\4</label></TD> \

3336 # <TD><a href="?home+\3">\5</a></TD>

3337 cgi_form grpaction<<EOF \

3338 | sed -e '/^<TR>/{; N; s/\n//; }' \

3339 | sed -E \

3340 -e "s|^(<TR><TD>)(k?)([0-9]*),(.*)</TD><TD>(.*)</TD>|\1\2$b1\3\">\4</label></td><td>$lnk</TD>|" \

3341 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3342 -e "s|^(<TR><TH>)(UID)|\1$br \2|" \

3343 | _m4 -D_TITLE_="spaste(\`$tf')" \

3344 -D_SUBTITLE_="チェック後操作ボタン" \

3345 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3346 $layout/form+dump.m4.html \

3347 | _m4 -D_TEAM_="spaste(\`$stf')"

3348 <p>下でチェックした人を対象として:</p>

3349 <div class="foldtabs">

3350 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3351 title="Shortcut: a${nl}Add a team tag to"

3352 for="cmteam">同じチーム属性を付与</label>

3353 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3354 `cgi_datalist_h teams $allhexteams`$submitBTN

3355 </div>

3356 ${rmteammsg}

3357 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3358 title="Shortcut: d${nl}DirectMail to"

3359 for="sendmsg" title="Direct Message">DM送信</label>

3360 <div>

3361 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3362 title="Using email format">email書式を使う</label>

3363 <div class="folded">

3364 <table>

3365 <tr><td>From: </td><td>$user</td></tr>

3366 <tr><td>このFrom:で送る</td>

3367 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3368 </td></tr>

3369 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3370 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3371 <tr><td>ファイル添付: </td>

3372 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3373 </table>

3374 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3375 </div>

3376 <div>本文:`cgi_textarea text "" cols=72`

3377 </div>

3378 </div>

3379 ${isowner:+$cmmsg$excmsg}

3380 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3381 </div>

3382 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3383 <table class="td3r td4r thl">

3384 `sq $db -header -html "$sql"`

3385 </table>

3386 `cgi_hidden grp $grid`

3387 `cgi_hidden myuid $myuid id="myuid"`

3388 EOF

3389 }

3390 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3391 # Create TEMPORARY VIEW

3392 query<<EOF

3393 CREATE TEMPORARY VIEW writeusers AS

3394 SELECT DISTINCT author FROM article

3395 WHERE id in (

3396 select id from article where blogid=(select id from blog where rowid=$1)

3397 );

3398 CREATE TEMPORARY VIEW movablegroups AS

3399 SELECT g.rowid growid , g.gname

3400 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3401 ON grp.gname=gm.gname -- そのユーザが属している

3402 AND user='$user') g -- グループに絞る

3403 WHERE (SELECT author FROM writeusers

3404 EXCEPT

3405 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3406 IS NULL;

3407 $2

3408 EOF

3409 }

3410 sql4readableblogs() {

3411 # Create view of blogs that can be readable by $user

3412 # Blog is readable when:

3413 # 1: blog owner is an user

3414 # 2: else, 2.1: owner-group where the $user belongs

3415 # 2.2: else, owner-group is not moderated

3416 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3417

3418 # $hidden_mode is defined in global section at head

3419

3420 cat<<EOF ## | tee tmp/sql.out

3421 CREATE TEMPORARY VIEW readableblogs AS

3422 SELECT blog.rowid rid, id, author

3423 FROM blog

3424 NATURAL JOIN

3425 (SELECT id,

3426 max(CASE key WHEN 'owner' THEN val END) owner,

3427 max(CASE key WHEN 'mode' THEN val END) mode

3428 FROM blog_s GROUP by id) bs

3429 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3430 THEN -- blog owner is an user, READABLE

3431 NOT mode IN $hidden_mode

3432 WHEN (SELECT val FROM grp_s

3433 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3434 AND

3435 (SELECT user FROM grp_mem

3436 WHERE gname=bs.owner AND user='$user') IS NULL

3437 THEN 0

3438 WHEN mode IN $hidden_mode

3439 THEN 0 -- "quiz" mode blog cannot be searched

3440 ELSE 1

3441 END;

3442 EOF

3443 }

3444 mvteamform() {

3445 owner=$1

3446 hexteams=$(hexteams "$owner" "$user")

3447 test -z "$hexteams" && return

3448 none="`echo なし|hexize`"

3449 cat<<-EOF

3450 <!-- <div class="fold">

3451 `cgi_checkbox mv2team send id="mv2team"`<label

3452 for="mv2team">この話題を以下のチームのものにする</label>

3453 <div> -->

3454 <p>この話題をチーム所有にする:

3455 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3456 </form></div></div>

3457 EOF

3458 }

3459 editheading() { # $1=rowid-of-heading

3460 rowid=${1%%[!A-Z0-9a-z_]*}

3461 if [ -z "$rowid" ]; then

3462 echo "話題番号が未指定です。" | html p

3463 return

3464 fi

3465 owner=`getvalbyid blog owner $rowid`

3466 title=`getvalbyid blog title $rowid`

3467 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3468 | _m4 -D_TITLE_="修正" \

3469 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3470 -D_BLOGS_="" -D_DUMPTABLE_="" \

3471 -D_FORM_="syscmd(\`cat')" \

3472 $layout/html.m4.html $layout/form+dump.m4.html

3473 # Move to group

3474 if isuser "$owner"; then

3475 crview4article $rowid

3476 n=`query "SELECT count(*) FROM writeusers;"`

3477 ## err N=$n

3478 if [ $((n)) -gt 0 ]; then

3479 ## err ROWID=$rowid

3480 sql="SELECT growid || ':' || gname FROM movablegroups;"

3481 cat<<-EOF

3482 <div class="fold">

3483 `cgi_checkbox mv send id="mv"`<label

3484 for="mv">この話題をグループ所有に移動する</label>

3485 <div>

3486 <form action="?mvart" method="POST" enctype="multipart/form-data">

3487 移動先グループ:

3488 <select name="mv2grp">

3489 EOF

3490 query ".mode html"

3491 query<<-EOF |

3492 $sql

3493 .mode list

3494 EOF

3495 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3496 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3497 cat<<-EOF

3498 </select>

3499 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3500 そのグループに加入しているものに限られます)</p>

3501 <p>`cgi_checkbox cfm yes`<label>確認

3502 (この操作は元に戻すことができません)</label></p>

3503 `cgi_hidden blogrowid $rowid`

3504 `cgi_submit 移動`

3505 `cgi_reset Reset`

3506 </form>

3507 </div>

3508 </div>

3509 EOF

3510 fi

3511 # end of isuser "$owner"

3512 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3513 [ -n "$hexteams" ];}; then

3514 none="`echo なし|hexize`"

3515 cat<<-EOF

3516 <div class="fold">

3517 `cgi_checkbox mv2team send id="mv2team"`<label

3518 for="mv2team">この話題を以下のチームのものにする</label>

3519 <div><p>現在の所属チーム設定:

3520 `query "SELECT

3521 coalesce((SELECT val FROM blog_s

3522 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3523 AND key='team'),

3524 ':なし');"`</p>

3525 <form action="?mvart" method="POST" enctype="multipart/form-data">

3526 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3527 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3528 `cgi_hidden blogrowid $rowid`<br>

3529 `cgi_submit 移動`

3530 `cgi_reset Reset`

3531 </form></div></div>

3532 EOF

3533 fi

3534 }

3535 mvart() { # move diary to some group or team

3536 # or move blog of group to team which belong to the group

3537 blogrowid=`getpar blogrowid`

3538 cfm=`getpar cfm`

3539 ##### echo move blog:$blogrowid to $mv2grp | html p

3540 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3541 . ./s4-blog.sh

3542 if [ -z "$blogrowid" ]; then

3543 echo "無効な指定です(mvart)。" | html p

3544 return

3545 elif [ x"$cfm" != x"yes" ]; then

3546 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3547 elif { mv2grp=`getpar mv2grp`

3548 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3549 [ -n "$mv2grp" ]; }; then

3550 crview4article $blogrowid

3551 ########## TRANSACTION BEGIN

3552 query "BEGIN;"

3553 n=`query "SELECT count(*) FROM writeusers;"`

3554 ## err Nwriteuser=$n

3555 if [ $((n)) -gt 0 ]; then

3556 query<<-EOF

3557 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3558 WHERE key='owner'

3559 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3560 AND $mv2grp IN (SELECT growid FROM movablegroups);

3561 EOF

3562 fi

3563 query "END;"

3564 ########## TRANSACTION END

3565 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3566 [ -n "$mv2team" ];}; then

3567 # blog owner can move it to ANY team

3568 case "$mv2team" in

3569 'なし')

3570 cat<<-EOF

3571 DELETE FROM blog_s

3572 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3573 AND key='team';

3574 EOF

3575 ;;

3576 "") ;;

3577 *)cat<<-EOF

3578 BEGIN;

3579 REPLACE INTO blog_s(id, key, val)

3580 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3581 'team', '$mv2team');

3582 REPLACE INTO blog_s(id, key, val)

3583 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3584 'notify', 'all'); -- Change notify to all

3585 END;

3586 EOF

3587 esac | query

3588 fi

3589 blog_reply $blogrowid

3590 echo yes | html p

3591 }

3592 editart() { # $1=article-rowid $2=blogrowid

3593 rowid=${1%%[!A-Z0-9a-z_]*}

3594 blogrowid=${2%%[!A-Z0-9a-z_]*}

3595 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3596 echo "表示する記事番号が未指定です。" | html p

3597 return

3598 fi

3599 owner=`getvalbyid blog owner $blogrowid`

3600 title=`getvalbyid blog title $blogrowid`

3601 author=`getvalbyid article author $rowid`

3602 math=`getvalbyid blog mathjax $blogrowid`

3603 ## err EDITart: owner=$owner, author=$author

3604 if isgrpowner "$user" "$owner"; then

3605 : EDIT OK

3606 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3607 echo "本人か所有者しか編集できません." | html p

3608 return

3609 fi

3610 aid=`query "select id from article where rowid=$rowid;"`

3611 tmpout=$tmpd/editart.$$.out

3612 GF_ACTION="?replyblog+$blogrowid#$aid" \

3613 edittable $formdir/article.def article $rowid \

3614 > $tmpout

3615 printf '%s' "${math:+$mathjax}" >> $tmpout

3616 # Cannot use pipelining to m4 with genform() because of stdin stack

3617 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3618 -D_FORM_="syscmd(cat $tmpout)" \

3619 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3620 -D_BLOGS_= -D_DUMPTABLE_= \

3621 $layout/html.m4.html $layout/form+dump.m4.html

3622 }

3623 send2mem() {

3624 rowid=`getpar grp`

3625 rowid=${rowid%%[!0-9]*} # Cleaning

3626 if [ -z "$rowid" ]; then

3627 echo "グループが未指定です。" | html p

3628 return

3629 fi

3630 message=`getpar message`

3631 if [ -z "$message" ]; then

3632 echo "文章を入れてください。" | html p

3633 return

3634 fi

3635 grp=`getgroupbyid $rowid`

3636 members=`collectemail "$grp"`

3637 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3638 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3639 mailfrom="`gecos "$user"` <$mailfrom>"

3640 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3641 msg=$(cat<<-EOF

3642 $urlbase?grp+$rowid

3643 グループ $grp に所属する

3644 `gecos $user` さんよりメッセージ:

3645

3646 $message

3647 EOF

3648 )

3649 # smail rcpt subj (file)

3650 for m in $members; do

3651 echo "$msg" |

3652 MAIL_FROM=$mailfrom \

3653 SENDER=$noreply \

3654 REPLYTO=$mailfrom \

3655 smail "$m" "$sj"

3656 done

3657 cat<<EOF

3658 <p>以下のユーザに送信しました。</p>

3659 <pre>

3660 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3661 </pre>

3662 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3663 EOF

3664 }

3665 joingrpadmit() {

3666 # $1=yes/no $2=session-key

3667 if [ -z "$2" ]; then

3668 echo "bye bye" | html p; return

3669 fi

3670 t_usr=`session=$2 getpar adduser`

3671 t_grp=`session=$2 getpar group`

3672 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3673 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3674 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3675 echo "無効な加入依頼です。" | html p

3676 echo "有効期限が切れたか、

3677 他の管理者がいる場合は処理済みの可能性があります。" | html p

3678 return

3679 fi

3680 if ! isgrpowner "$user" "$t_grp"; then

3681 echo "グループ管理者のみの機能です。" | html p; return

3682 fi

3683 case $1 in

3684 yes) joingrp "$t_grp" "$t_usr" yes ;;

3685 no) joingrp "$t_grp" "$t_usr" no ;;

3686 *)

3687 echo "無効な指定です($1)。" | html p

3688 return ;;

3689 esac

3690 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3691 rcpts="`getgroupadminmails "$t_grp"` $user"

3692 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3693 body="に

3694 $t_usr

3695 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3696 しました。"

3697 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3698 query "delete from session where id='$2';"

3699 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3700 }

3701

3702 joingrprequest() {

3703 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3704 jss="joingrp-`date +%s`-`genrandom 12`"

3705 gecos=`gecos "$user"`

3706 addsession $jss +${memoplimitdays}days

3707 grpadmins=`getgroupadmins "$1"`

3708 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3709 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3710 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3711 $url

3712 $user ${gecos:+($gecos)}さんから

3713 グループ $1

3714 に加入依頼がありました。

3715

3716 承認する:

3717 $urlbase?joingrpadmit+yes+$jss

3718

3719 白紙に戻す:

3720 $urlbase?joingrpadmit+no+$jss

3721 EOF

3722 echo "管理者に加入依頼を出しました。

3723 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3724 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3725 }

3726 joingrp() {

3727 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3728 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3729 if isgrpowner "$user" "$1"; then

3730 isowner="yes"

3731 elif [ -n "$5" ]; then

3732 isowner="yes"

3733 else

3734 isowner=""

3735 fi

3736 ## err jg:isgrpowner: isowner="$isowner"

3737 if [ -n "$isowner" ]; then

3738 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3739 elif [ x"$2" != x"$user" ]; then # if user is not login user

3740 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3741 return

3742 elif [ x"$3" = x"no" ]; then

3743 : # Do not pursue those who leave

3744 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3745 : # Member can change own email address for the joining moderated group

3746 else # adding user is $user itself

3747 case `getgroupattr "$1" regmode` in

3748 moderated)

3749 joingrprequest "$@" # Request only

3750 return

3751 ;;

3752 *)

3753 ;;

3754 esac

3755 fi

3756 qgname=`sqlquote "$1"`

3757 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3758 cond="where gname=$qgname and user='$2'"

3759 if [ x"$3" = x"yes" ]; then

3760 query "replace into grp_mem values($qgname, '$2');"

3761 # Notify joingrp to admin

3762 action="に加入しました。"

3763 if [ -n "$4" ]; then

3764 if msg=`emaildomaincheck "$4"`; then

3765 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3766 'string', '$4', NULL);"

3767 else

3768 echo $msg

3769 fi

3770 else

3771 query "delete from grp_mem_s $cond and key='email';"

3772 fi

3773 if [ -n "$5" ]; then # as ADMIN

3774 # Coming here means newly created group

3775 sql="select case\

3776 when (select count(*) from grp_mem where gname=$qgname)=1\

3777 then (select user from grp_mem\

3778 where gname=$qgname and user='$user')\

3779 else '' end; "

3780 err NewGrpChk: $sql

3781 if [ -n "`query \"$sql\"`" ]; then

3782 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3783 query "replace into grp_adm values($qgname, '$user');"

3784 fi

3785 fi

3786 else

3787 query "begin;

3788 delete from grp_mem $cond;

3789 delete from grp_mem_s $cond;

3790 delete from grp_mem_m $cond;

3791 delete from grp_adm $cond;

3792 delete from grp_adm_s $cond;

3793 delete from grp_adm_m $cond;

3794 end;"

3795 action="から脱退しました。"

3796 fi

3797 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3798 $url?grp+$grid

3799 $user (`gecos $user`)さんが

3800 グループ $1

3801 $action

3802 EOF

3803 }

3804 grp_add_team() (

3805 # $1=grp-rowid $2=team $3...=user-rowid(s)

3806 grp=`getgroupbyid $1`

3807 team=$2; shift; shift

3808 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3809 { echo "BEGIN;"

3810 for user; do

3811 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3812 '$grp',\

3813 (SELECT name FROM user WHERE rowid=$user),\

3814 'team', 'string', '$team');"

3815 done

3816 echo "END;"

3817 } | query

3818 )

3819 grp_rm_team() (

3820 # $1=grp-rowid $2=team $3...=user-rowid(s)

3821 grid=$1

3822 qgrp=$(sqlquote "`getgroupbyid $grid`")

3823 team=$2; shift; shift

3824 [ -z "$grid" -o -z "$team" ] && return

3825 { echo "BEGIN;"

3826 for user; do

3827 echo "DELETE FROM grp_mem_m\

3828 WHERE gname=$qgrp \

3829 AND user=(SELECT name FROM user WHERE rowid=$user)\

3830 AND key='team' AND val='$team';"

3831 done

3832 cat<<-EOF

3833 DELETE FROM blog_s

3834 WHERE rowid=(

3835 SELECT rowid

3836 FROM blog_s a

3837 WHERE key='team'

3838 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3839 AND NOT EXISTS (SELECT * FROM grp_mem_m

3840 WHERE key='team' AND val=a.val -- a.val=team

3841 AND gname = (SELECT val FROM blog_s b

3842 WHERE a.id=b.id AND key='owner')

3843 ));

3844 EOF

3845

3846 echo "END;"

3847 } | query

3848 )

3849 grp_reg_adm() {

3850 # $1=grp-rowid $2...=user-rowid

3851 grid=$1

3852 grp=`getgroupbyid "$1"`

3853 if [ -z "$grp" ]; then

3854 echo "無効なグループIDです" | html p; return

3855 fi

3856 if ! isgrpowner "$user" "$grp"; then

3857 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3858 fi

3859 shift

3860 for urid; do

3861 newadm=`query "select name from user where rowid=$urid;"`

3862 if [ -z "$newadm" ]; then

3863 echo "指定ユーザIDがおかしいようです。" | html p; return

3864 fi

3865 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3866 err ismember $newadm $grp

3867 if ismember $newadm "$grp"; then

3868 # OK, go ahead

3869 getgname="(select gname from grp where rowid=$grid)"

3870 query "replace into grp_adm values($getgname, '$newadm');"

3871 # confirm insertion

3872 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3873 if [ -n "`query \"$sql;\"`" ]; then

3874 echo "追加完了: $newadm" | html p

3875 else

3876 echo "追加失敗($1 $urid)" | html p

3877 fi

3878 fi

3879 showgroup $grid

3880 done

3881 }

3882 dt_colhack() {

3883 # FROM: <TD>xxx:::yyy</TD>

3884 # TO: <TD class="xxx">yyy</TD>

3885 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3886 }

3887 dt_rowhack() {

3888 # From: <TR>

3889 # ....

3890 # <TD>rowclass=foo</TD>

3891 # </TR>

3892 # To: <TR class="foo">....<TD>foo</TD></TR>

3893 sed -e '

3894 /^<TR>/ {

3895 :loop

3896 s/\n//

3897 N

3898 /<\/TR>/ {

3899 s/\n//

3900 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3901 n

3902 }

3903 $q

3904 b loop

3905 }'

3906 }

3907 dumptable() {

3908 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3909 # textのフィールドだけ全てダンプにしたほうがいいか

3910 # $DT_VIEW sets link

3911 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3912 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3913 VIEW=${DT_VIEW-replyblog}

3914 if [ -n "$VIEW" ]; then

3915 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3916 fi

3917 sqlfile=$tmpd/dump.sql

3918 : > $sqlfile # ensure to be empty

3919 printf '.mode html\n.header 1\n' > $sqlfile

3920 # $DT_CHLD=ChildTable:BindColumn (eg. article:blogid)

3921 if [ -n "$DT_CHLD" ]; then

3922 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3923 cat<<-EOF >> $sqlfile

3924 -- presql

3925 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3926 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3927 EOF

3928 # Speed up counting of new articles

3929 dt_count=${DT_COUNT:+"CREATE TEMPORARY VIEW _dtcount AS

3930 SELECT a.id $_i, $DT_COUNT, coalesce(cnt2, 0) cnt2

3931 FROM (SELECT DISTINCT id FROM _target) a

3932 LEFT JOIN

3933 (SELECT blogid,$DT_COUNT,count($DT_COUNT) cnt2

3934 FROM $_t

3935 WHERE $DT_COUNT = '$DT_COUNT_BY'

3936 GROUP BY $_i,$DT_COUNT) b

3937 ON a.id=b.$_i

3938 ;"}

3939 cat<<-EOF >> $sqlfile

3940 -- presql2

3941 DROP TABLE IF EXISTS _counts;

3942 CREATE TEMPORARY TABLE _counts AS

3943 SELECT $_i, count($_i) cnt

3944 FROM $_t GROUP BY $_i;

3945 /* Prepare NEW count table */

3946 CREATE TEMPORARY TABLE _target AS

3947 SELECT b.rowid trowid, b.id

3948 FROM "$2" b JOIN "$2_s" s

3949 ON b.id=s.id AND s.key='owner'

3950 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3951

3952 DROP TABLE IF EXISTS _children;

3953 CREATE TEMPORARY TABLE _children AS

3954 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3955 FROM (SELECT t.trowid, t.id $_i, a.id

3956 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3957 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3958

3959 DROP TABLE IF EXISTS _news;

3960 DROP VIEW IF EXISTS _news;

3961

3962 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3963 -- INSERT INTO _news

3964 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3965 CREATE TEMPORARY VIEW _news AS

3966 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3967 FROM (SELECT DISTINCT id FROM _target)

3968 a LEFT JOIN

3969 (SELECT $_i, count(ctime) newcnt

3970 FROM _children x

3971 WHERE ctime > coalesce((SELECT time from myacclog

3972 WHERE tblrowid=x.trowid),

3973 '1970-01-01')

3974 GROUP BY $_i) b

3975 ON a.id=b.$_i;

3976 $dt_count

3977 EOF

3978 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3979 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3980 cntmine=${DT_COUNT:+"(SELECT cnt2 FROM _dtcount WHERE $_i=a.id)"}

3981 cnt="$cntnew as '新着', $cntall as '総数', "

3982 cnt=$cnt${DT_COUNT:+"$cntmine as '$DT_COUNT_HEADER',"}

3983 dt_class=" td2r td3r td4r dumpblogs"

3984 fi

3985 # Construct join expression

3986 eav="" scols=""

3987 pk=`gettblpkey $2`

3988 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3989 substr=${substr:-%s}

3990 for col in ${3:-`gettbl_s_cols $2`}; do

3991 valvar=val

3992 fromtbl=b

3993 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3994 # If $col belongs to master table

3995 fromtbl=a; col=${col#a.}

3996 fi

3997 case $col in

3998 gecos) scols="$scols${scols:+, }${col#}"

3999 continue ;; # built-in column name

4000 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

4001 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

4002 case "$as" in

4003 *:*=*) cnd=${as#*:}

4004 h=${cnd%%=*} v=${cnd#*=}

4005 h=`sqlquotestr "$h"`

4006 v=`sqlquotestr "$v"`

4007 if [ x"$fromtbl" = x"b" ]; then

4008 valvar="CASE val WHEN $h THEN $v END"

4009 else

4010 valvar="$h"

4011 fi

4012 as=${as%%:*} ;;

4013 esac

4014 ;;

4015 *) as=${col} ;;

4016 esac

4017 ss=`printf "$substr" "$valvar"`

4018 if [ x"$fromtbl" = x"b" ]; then

4019 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

4020 else

4021 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

4022 fi

4023 scols="$scols${scols:+, }${fromtbl}.$as"

4024 done

4025 #case author when '$user' then a.rowid else '---' end as ID,

4026 if [ -n "$DT_SQL" ]; then

4027 echo "$DT_SQL"

4028 else

4029 cat<<-EOF

4030 SELECT a.rowid as LINK, $cnt $scols

4031 FROM $2 a LEFT JOIN

4032 (SELECT $pk,$eav,

4033 max(CASE key

4034 WHEN 'owner'

4035 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

4036 as gecos

4037 FROM ${2}_s c GROUP BY $pk)

4038 b ON a.$pk=b.$pk $4;

4039 EOF

4040 fi >> $sqlfile

4041 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

4042 sqlog<<-EOF

4043 *** SQL-file: $sqlfile ***

4044 `cat $sqlfile`

4045 EOF

4046 printf '.mode list\n.header 0\n' >> $sqlfile

4047 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

4048 <div>

4049 <div class="dumptable">

4050 <table class="b$dt_class">

4051 `query ".read $sqlfile"`

4052 </table>

4053 </div>

4054 </div>

4055 EOF

4056 }

4057

4058 clean_orphaned() {

4059 # This shoud be done by foreign_key rules, but some db lack them

4060 query<<-EOF

4061 -- Find blogs that have no parent

4062 WITH orphanedblog AS (

4063 SELECT blog.id,val FROM blog JOIN blog_s bs

4064 ON blog.id=bs.id AND key='owner'

4065 WHERE val NOT IN (SELECT gname FROM grp)

4066 AND val NOT IN (SELECT name FROM user)

4067 ) -- Remove them

4068 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

4069

4070 -- Find articles that have no parent blog

4071 WITH orphanedarticle AS (

4072 SELECT id FROM article

4073 WHERE blogid NOT IN (SELECT id FROM blog)

4074 ) -- Remove them

4075 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

4076 EOF

4077 }

4078

4079 clearcachedir() (

4080 td=`getcachedir "$1"`

4081 err td=$td: ls- `ls $td`

4082 if [ -w "$td/image.1" ]; then

4083 err Removing td=$td

4084 rm -fr $td/ # Clear icon-image cache!

4085 fi

4086 )

4087

4088 par2table() (

4089 # copy current parameters of par into destination table

4090 # $1=definition-file

4091 # Using $user and $session

4092 # Return value:

4093 # 0: Stored successfully

4094 # 1: Insufficient fillings

4095 # 2: No permission to modify the record

4096 # 3: Invalid rowid

4097 # 4: SUCCESS to delete

4098 # 5: Stop deletion for lack of confirm check

4099 # 6: Password length too short

4100 # 7: Password mismatch

4101 # 8: Old password incorrect

4102 # 9: Duplicated post

4103 rowid=`getpar rowid`

4104 if [ ! -e $1 ]; then

4105 echo "テーブル定義ファイルが見付かりません" | html p

4106 exit 1

4107 fi

4108 tbl=${1%.def}

4109 tbl=${tbl##*/}

4110 if [ -n "$rowid" ]; then # Modify existing entry

4111 if [ x"$tbl" = x"user" ]; then

4112 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4113 elif [ x"$tbl" = x"grp" ]; then

4114 sql="select gname from $tbl where rowid=$rowid;"

4115 ##err p2t:grp:q $sql

4116 isgrpowner "$user" "`query $sql`" && rowowner=$user

4117 elif [ x"$tbl" = x"blog" ]; then

4118 # Check if owner in blog_s

4119 blogowner=`getvalbyid blog owner "$rowid"`

4120 if isgrpowner "$user" "$blogowner"; then

4121 rowowner=$user

4122 origauthor=`getvalbyid blog author $rowid`

4123 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4124 then # Keep original author

4125 setpar author string "$origauthor" # if differs from $user

4126 fi # 2021-11-06 suggd.by Ruri

4127 else

4128 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4129 fi

4130 else

4131 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4132 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4133 fi

4134 ### err rowowner=$rowowner

4135 if [ x"$user" != x"$rowowner" ]; then

4136 echo "他人のレコードはいじれないの" | html p

4137 return 2

4138 elif [ -z "$rowowner" ]; then

4139 echo "指定したレコードはないみたい" | html p

4140 return 3

4141 fi

4142 rm=`getpar rm` cfm=`getpar confirm`

4143 # Editing existent entry

4144 if [ x"$rm" = x"yes" ]; then

4145 if [ x"$rm$cfm" = x"yesyes" ]; then

4146 query "delete from $tbl where rowid=$rowid;"

4147 clearcachedir "$tbl/$rowid"

4148 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4149 clean_orphaned

4150 fi

4151 return 4

4152 else

4153 echo "消去確認のチェックがないので消さなかったの..." | html p

4154 return 5

4155 fi

4156 fi

4157 fi

4158

4159 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

4160 if [ -n "$rowid" ]; then

4161 # Update of existing record

4162 for col in `gettblcols $tbl`; do

4163 val=`getparquote $col`

4164 [ -z "$val" ] && continue

4165 ## err query "update $tbl set $col=$val where rowid=$rowid"

4166 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4167 sql="update $tbl set $col=$val where rowid=$rowid;"

4168 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4169 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4170 ## User name cannot be changed with interface provided with this

4171 ## script. But we offer the trigger to change owner user

4172 ## of blog_s table.

4173 #err "select quote($col) from $tbl where rowid=$rowid;"

4174 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4175 cat<<-EOF | query

4176 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4177 -- should use EXCLUSIVE transaction outside of this.

4178 SAVEPOINT par2table;

4179 $sql

4180 update blog_s set val=$val

4181 where key='owner' and val=$old;

4182 RELEASE SAVEPOINT par2table;

4183 EOF

4184 ## XX: DIRTY Hack Ends here

4185 ## We should keep blog's owner as a single column which has

4186 ## foreign key constraint with primary key of grp/user.

4187 else

4188 query "$sql"

4189 fi

4190 done

4191 # Then, set up $pval for further insertion of tbl_s and tbl_m

4192 for col in `gettblpkey $tbl`; do

4193 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4194 pval="$pval${pval:+, }\"$val\""

4195 done

4196 else

4197 # New entry

4198 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4199 # Avoid empty repost of article.

4200 if [ x"$tbl" = x"article" ]; then

4201 # If rowid is empty and ID exists in article-table, that is REPOST!

4202 aid=`getpar id`

4203 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4204 if [ -n "$xaid" ]; then

4205 # REPOST of article

4206 html p <<-EOF

4207 書き込み直後のリロードなので上書きを回避します。

4208 最新記事は末尾の「再読み込み」ボタンから見てください。

4209 EOF

4210 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4211 return 9 # STOP Duplicated posting

4212 fi

4213 fi

4214 # Generate values() for primary keys

4215 for col in `gettblpkey $tbl`; do

4216 # Genuine primary keys for _m and _s

4217 val=`getvalquote $tbl $col`

4218 [ -z "$val" ] && continue

4219 pval="$pval${pval:+, }$val"

4220 done

4221 ##err pval=$pval

4222 for col in `gettblfkey $tbl`; do

4223 # args for values() to insertion into master table

4224 val=`getvalquote $tbl $col`

4225 [ -z "$val" ] && continue

4226 formaster=$formaster"${formaster:+, }$val"

4227 done

4228 formaster="$pval${formaster:+, }$formaster"

4229 ## err formaster=$formaster

4230 if [ -z "$formaster" ]; then

4231 echo "項目を全て埋めてください" | html pre

4232 return 1

4233 fi

4234 ## err "replace into $tbl values($formaster);"

4235 query "replace into $tbl values($formaster);"

4236 ## Insertion to master table, done

4237 fi

4238

4239 transaction=$tmpd/sqlfile.sql; touch $transaction

4240 for kt in s m; do

4241 tb2=${tbl}_$kt

4242 for col in `gettbl_${kt}_cols $tbl`; do

4243 ptype=`getpartype $col "limit 1"`

4244

4245 # First, check update of existing entries in _m

4246 if [ $kt = m ]; then

4247 # sessID|address.1.22|string|Somewhere-x.y.z

4248 sql=""

4249 ##err dots from query "select var from par where var like '$col.%';"

4250 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4251 # v=address.1.22

4252 st_rowid=${v##*.}

4253 origcol=${v%%.*} # original column derived from

4254 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4255 ##case `getpartype $v` in

4256 ## err CASE `gettbl_coltype $tbl/$origcol` in

4257 ## err edit flag = `getpar action.$v`

4258 case `getpar action.$v` in

4259 rm)

4260 if [ x`getpar confirm.$v` = x"yes" ]; then

4261 newsql="delete from $tb2"

4262 else

4263 echo "削除確認未チェック" | html p

4264 fi ;;

4265 edit)

4266 case `gettbl_coltype $tbl/$origcol` in

4267 image|document|binary)

4268 file=$tmpd/`getparfilename $v`

4269 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4270 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4271 continue

4272 fi

4273 ## err type=file=$file

4274 [ -z "$file" ] && continue

4275 bn=`sqlquotestr "${file##*/}"`

4276 bin="X'"$(hexize "$file")"'"

4277 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4278 type=\"file:$ct\"

4279 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4280 cachedir=`getcachedir "$tbl/$rowid"`

4281 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4282 rm -rf $cachedir

4283 ;;

4284 *)

4285 newsql="update $tb2 set val=(select val from par where var \

4286 like '$col.%.$st_rowid' AND sessid='$session')"

4287 ;;

4288 esac

4289 ;;

4290 mv)

4291 # regularize filename and strip directory part

4292 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4293 newname=`basename $newname`

4294 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4295 oldext=`expr "$oldname" : '.*\.$.*$'`

4296 newext=`expr "$newname" : '.*\.$.*$'`

4297 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4298 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4299 then

4300 newsql="UPDATE $tb2 SET val='$newname'"

4301 else

4302 html p<<-EOF

4303 $newname は取り扱えないファイル名です。

4304 空白を含まない名前にして下さい。

4305 拡張子の変更もできません。

4306 EOF

4307 continue

4308 fi

4309 ;;

4310 *) # maybe "keep", do not modify value

4311 continue

4312 ;;

4313 esac

4314 # err newsql=$newsql

4315 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4316 clearcachedir "$tbl/$rowid"

4317 done

4318

4319 if [ x"$bin" = x"NULL" ]; then

4320 ## err repl:normal sql=`echo $sql`

4321 if [ -n "$transaction" ]; then

4322 cat<<-EOF >> $transaction

4323 $sql

4324 DELETE FROM $tb2 WHERE type='string' AND val='';

4325 EOF

4326 else

4327 query "$sql

4328 delete from $tb2 where type='string' and val='';"

4329 ## err repl:normal done

4330 fi

4331 else

4332 # Binary update line is TOO LONG to pipelining

4333 sqlfile="$tmpd/sqlf.$$"

4334 if [ -n "$transaction" ]; then

4335 cat<<-EOF >> $transaction

4336 $sql

4337 EOF

4338 else

4339 cat<<-EOF > $sqlfile

4340 $sql

4341 EOF

4342 query ".read $sqlfile"

4343 fi

4344 fi

4345 # Rest of kt==m: set multiple mode

4346 nr=`getparcount $col`

4347 else

4348 nr=1 # for kt==s, number of records is 1

4349 fi

4350

4351 i=0

4352 while [ $i -lt $nr ]; do

4353 limit="limit 1 offset $i"

4354 i=$((i+1)) # increase beforehand against continue

4355 val=`getvalquote $tbl $col "$limit"`

4356 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4357 [ -z "$val" -o x"$val" = x'""' ] && continue

4358 ## err $col=$val

4359 bin=NULL

4360 ## err partype$col=`getpartype $col "$limit"`

4361 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4362 case $ptype in

4363 file) file=$tmpd/`getparfilename $col "$limit"`

4364 ## err parfile-$col=$file

4365 [ -z "$file" ] && continue

4366 bin="X'"$(hexize "$file")"'"

4367 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4368 type=\"file:$ct\" ;;

4369 "*"*) continue ;; # foreign table

4370 *) type=\"string\" ;;

4371 esac

4372 case `gettbl_coltype $tbl/$col` in

4373 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4374 test x"$val" = x"NULL" && val="''"

4375 ;;

4376 password) # special care for password

4377 # name={password,pswd1,pswd2}

4378 p1=`getpar pswd1 "$limit"`

4379 if [ -z "$p1" ]; then

4380 continue # SKIP password setting, if p1 is empty

4381 else

4382 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4383 ## err pswd=$pswd

4384 if pwcheck "$pswd"; then

4385 if [ x"$p1" = x"$p2" ]; then

4386 case "$p1" in

4387 ??????????*) ;;

4388 *) echo "パスワードは10字以上にしてください。" | html p

4389 return 6;;

4390 esac

4391 val="\"`echo $p1|mypwhash`\""

4392 else

4393 echo "2つの新パスワード不一致" | html p

4394 return 7

4395 fi

4396 else

4397 echo "旧パスワード違います" | html p

4398 return 8

4399 fi

4400 fi

4401 ;;

4402 esac

4403 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4404 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4405 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4406 continue # Do not insert completely NULL record 2021-11-08

4407 fi

4408 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4409 if [ x"$bin" = x"NULL" ]; then

4410 ## err Normal-query: `echo $sql`

4411 if [ -n "$transaction" ]; then

4412 printf '%s' "$sql" >> $transaction

4413 else

4414 query "$sql"

4415 fi

4416 else

4417 sqlfile="$tmpd/query.$$"

4418 ## err sqlfile=`ls -lF $sqlfile`

4419 if [ -n "$transaction" ]; then

4420 cat<<-EOF >> $transaction

4421 $sql

4422 EOF

4423 else

4424 cat<<-EOF >> $sqlfile

4425 $sql

4426 EOF

4427 query ".read $sqlfile"

4428 fi

4429 fi

4430 ## err p2t done

4431 done

4432 done

4433 done

4434 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4435 -- We cannot use transaction here, because groupupdate may use it.

4436 SAVEPOINT pa2table_insert;

4437 .read $transaction

4438 RELEASE SAVEPOINT pa2table_insert;

4439 EOF

4440 rc=$?

4441 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4442 ## err "Table:$tbl update done "

4443 return $rc

4444 )

4445 genform() {

4446 # $1 = form definition file

4447 # $2, $3 (optional)= table name and ROWID

4448 # If $GF_VIEWONLY set and nonNull, output values without form

4449 # If $GF_ARGS set, use it as content-strings in the form

4450 # If $GF_OWNER set, use it as value of name="owner"

4451 # If $GF_STAGE set, use it as value of name="stage"

4452 forms="" hiddens="" rowid=$3

4453 if [ ! -e "$1" ]; then

4454 echo "そのようなデータベースはないようです($2)。" | html p

4455 return

4456 elif [ -n "$2" ]; then

4457 rec=`query "select * from $2 where rowid='$rowid';"`

4458 if [ -z "$rec" ]; then

4459 pk=`gettblpkey $2`

4460 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4461 rec=`query "select rowid from $2 where $pk='$rowid';"`

4462 rowid=$rec

4463 rec=$3

4464 fi

4465 if [ -z "$rec" ]; then

4466 echo "そんなレコードはないみたいね..." | html p

4467 return

4468 fi

4469 fi

4470 if [ -z "$GF_VIEWONLY" ]; then

4471 rm='<input id="rm" name="rm" type="checkbox"

4472 value="yes"><label for="rm">このエントリの削除</label>

4473 <span>ほんとうに消しますよ(確認)!

4474 <input name="confirm" type=checkbox value="yes">はい</span>'

4475 fi

4476 # Image Cache dir

4477 ## err genform: getcache=$2/$rowid

4478 td=`getcachedir "$2/$rowid"`

4479 while IFS=: read -r prompt name keytype type args; do

4480 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4481 sp="${args:+ }"

4482 form="" val=""

4483 if [ -n "$rowid" ]; then

4484 # err genform2a: Seeking for "$2.$name, type=$type"

4485 rawval=`getvalbyid $2 $name $rowid $td`

4486 val=`printf '%s\n' "$rawval"|htmlescape`

4487 ## err genform3a: getvalbyid $2 $name $rowid $td

4488 ## err genform3b: val="[$val]" type="$type"

4489 fi

4490 if [ -n "$GF_VIEWONLY" ]; then

4491 is_hidden "$2" "$name" && continue

4492 fi

4493 case "$type" in

4494 text*)

4495 cgiform=cgi_multi_$type

4496 if [ -s $td/$name.count -a -n "$val" ]; then

4497 form=`$cgiform $name $td`

4498 val=$(printf '%s\n' "$val"|

4499 while read fn; do

4500 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4501 </td></tr>$nl"

4502 done)

4503 val="<table>$nl$val$nl</table>"

4504 else

4505 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4506

4507 form=`cgi_$type $name "$rawval" "$args"`

4508 fi

4509 ;;

4510 [Rr][Aa][Dd][Ii][Oo])

4511 fh="<label><input type=\"radio\" name=\"$name\""

4512 form="`echo $args|sed -e \

4513 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4514 ;;

4515 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4516 checked=${val:+ checked}

4517 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4518 ;;

4519 [Ss][Ee][Ll][Ee][Cc][Tt])

4520 fh="<select name=\"$name\">$nl"

4521 form=$(for l in $args; do

4522 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4523 done)

4524 if [ -n "$val" ]; then

4525 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4526 fi

4527 form="$fh$form</select>"

4528 ;;

4529 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4530 if [ -s $td/$name.count ]; then

4531 form=`cgi_multi_file $name $td "$args"`

4532 if [ -n "$val" ]; then

4533 hrfb="$myname?showattc+$2_m"

4534 val=$(echo "$rawval" \

4535 | while read fn; do

4536 data=`percenthex "$td/$fn"`

4537 #ct=`cat $td/$fn.content-type`

4538 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4539 ri=`cat "$td/$fn.rowid"`

4540 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4541 #imgsrc="<img src=\"data:$ct,$data\">"

4542 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4543 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4544 done)

4545 fi

4546 else

4547 form="<input type=\"file\" name=\"$name\" $args>"

4548 if [ -n "$val" ]; then

4549 imgs=$(echo "$rawval"\

4550 |while read fn;do

4551 data=`percenthex "$td/$fn"`

4552 echo "<img src=\"data:image/png,$data\">$fn<br>"

4553 done)

4554 form=$form"<br>$imgs"

4555 val=$imgs # 2015-06-15

4556 else

4557 form="<input type=\"file\" name=\"$name\" $args>"

4558 fi

4559 fi

4560 ;;

4561 [Hh][Ii][Dd][Dd][Ee][Nn])

4562 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4563 args="value=\"$GF_STAGE\""

4564 fi

4565 form="<input type=\"hidden\" name=\"$name\" $args>"

4566 prompt='' # Remove prompt

4567 ;;

4568 [Aa][Uu][Tt][Hh][Oo][Rr])

4569 [ -n "$GF_VIEWONLY" ] && continue

4570 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4571 prompt="" ;;

4572 [Oo][Ww][Nn][Ee][Rr])

4573 [ -n "$GF_VIEWONLY" ] && continue

4574 val=${GF_OWNER:-$val}

4575 val=${val:-$user}

4576 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4577 prompt="" ;;

4578 [Uu][Ss][Ee][Rr])

4579 # XXX: is null $user ok?

4580 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4581 [ -n "$GF_VIEWONLY" ] && continue

4582 form="$user"

4583 ;;

4584 [Pp]assword)

4585 [ -n "$GF_VIEWONLY" ] && continue

4586 form="`cgi_passwd`"

4587 val=""

4588 ;;

4589 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4590 [ -n "$GF_VIEWONLY" ] && continue

4591 if [ -z "$rowid" ]; then

4592 val=`genserial`

4593 fi

4594 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4595 prompt="" ;;

4596 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4597 prompt=""

4598 ;;

4599 parent|path|blog*)

4600 prompt=""

4601 ;;

4602 "*"*)

4603 tail=$tail"``"

4604 continue ;;

4605 esac

4606 if [ -n "$prompt" ]; then

4607 if [ -n "${GF_VIEWONLY}" ]; then

4608 form=$val

4609 else

4610 :

4611 fi

4612 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4613 else

4614 hiddens=$hiddens$nl"$form"

4615 fi

4616 done < $1

4617 # enctype="multipart/form-data"

4618 cat<<-EOF

4619 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4620 EOF

4621 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4622 cat<<EOF

4623 <table class="b $2">

4624 $forms

4625 </table>$hiddens

4626 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4627 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4628 EOF

4629 if [ -z $GF_VIEWONLY ]; then

4630 cat<<EOF

4631 <input type="submit" name="sub" value="OK">

4632 <input type="reset" name="res" value="Reset">

4633 EOF

4634 fi

4635 cat<<EOF

4636 $GF_ARGS</form>

4637 $tail

4638 EOF

4639 }

4640 edittable() {

4641 # $1=form-def $2=table $3 rowid

4642 genform "$@"

4643 }

4644 viewtable() {

4645 GF_VIEWONLY=1 genform "$@"

4646 }

4647 showattc() {

4648 # $1=table_m $2=rowid &optional $3=RawFlag

4649 ## err \$1=$1 \$2=$2 \$3=$3

4650 if ! isfilereadable $user $1 $2; then

4651 contenttype; echo

4652 echo "このファイルは管理者のみしか見られません" | html p

4653 putfooter; exit

4654 fi

4655 idir=`umask 002; mktempd` || exit 1

4656 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4657 bin=$idir/$myname-$$.bin

4658 sql="select quote(bin) from $1 where rowid='$2';"

4659 ## err showattc: sql: $sql

4660 sq $db "$sql" | unhexize > $bin

4661 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4662 type=${tv%//*} fn=${tv#*//}

4663 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4664 ct=${type#file:}

4665 case $ct in # all text/* changed to text/plain

4666 text/*|application/csv|application/json)

4667 charset=`nkf -g $bin|cut -d' ' -f1`

4668 case $charset in

4669 ASCII*) charset="" ;;

4670 esac

4671 if [ -z "$3" ]; then

4672 ct="text/html${charset:+; charset=$charset}"

4673 link="?showattc+$1+$2+raw"

4674 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4675 | sed 's,^,<span></span>,' \

4676 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4677 -D_LINK_="$link" \

4678 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4679 exit $?

4680 fi

4681 ct="text/plain${charset:+; charset=$charset}"

4682 ;;

4683 video/*)

4684 if [ -z "$3" ]; then

4685 _m4 -D_TITLE_="$fn" \

4686 -D_SRC_="?showattc+$1+$2+raw" $layout/videoplay.m4.html

4687 exit $?

4688 fi

4689 esac

4690 contenttype "$ct"

4691 echo "Content-Disposition: filename=\"$fn\""

4692 echo "Content-Length: " `cat $bin | wc -c`; echo

4693 #echo "Content-Type: " ${type#file:}; echo

4694 cat $bin

4695 }

4696 #

4697 # Some default stupid handler on CGI values

4698 #

4699 default_storedb() {

4700 # ARG: $1=table-def-file

4701 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4702 tbl=`basename $1`

4703 tbl=${tbl%.def}

4704 cols="`grep :text $1|cut -d: -f2`"

4705 col=`echo "$cols"|head -1`

4706 vcol=`getpar $col`

4707 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4708 if [ -n "$vcol" ]; then

4709 par2table $1

4710 else

4711 return 2 # No insertion occurred

4712 fi

4713 }

4714

4715 default_view() { # $1=def-file

4716 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4717 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4718 default_storedb "$@"

4719 query "select rowid from $tbl order by rowid desc;" \

4720 | while read rowid; do

4721 viewtable $1 $tbl $rowid

4722 done | _m4 -D_TITLE_="$tbl" \

4723 -D_FORM_="`genform $1`" \

4724 -D_DUMPTABLE_="syscmd(cat)" \

4725 $layout/html.m4.html $layout/form+dump.m4.html

4726 }

4727 default_viewtext() { # $1=def-file

4728 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4729 default_storedb "$@"

4730 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4731 | _m4 -D_TITLE_="$tbl" \

4732 -D_FORM_="`genform $1`" \

4733 -D_DUMPTABLE_="syscmd(cat)" \

4734 $layout/html.m4.html $layout/form+dump.m4.html

4735 }

4736 default_smail() {

4737 default_storedb "$@"

4738 if [ $? -eq 2 ]; then

4739 _m4 -D_TITLE_="入力" \

4740 -D_FORM_="`genform $1`" \

4741 -D_DUMPTABLE_="" \

4742 $layout/html.m4.html $layout/form+dump.m4.html

4743 return

4744 fi

4745 cond=""

4746 for pk in `gettblpkey $tbl`; do

4747 pv=$(sqlquote "$(getpar $pk)")

4748 cond="$cond${cond:+ and }$pk=$pv"

4749 done

4750 sql="select rowid from $tbl where $cond;"

4751 rowid=`query "$sql"`

4752 ## err smail1 - "$sql" "-> rowid=$rowid"

4753

4754 while IFS=: read prompt name keytype type args; do # Read from $1

4755 val=`getpar $name`

4756 if [ -n "$val" ]; then

4757 text="$text

4758 $prompt

4759 $name=$val

4760 ---------------------------------------------------------"

4761 fi

4762 case "$type" in

4763 image|document|file)

4764 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4765 fns=$(echo "$fn"|while read fn; do

4766 err mv $tmpd/$fn.orig $tmpd/$fn

4767 mv $tmpd/$fn.orig $tmpd/$fn

4768 rm $tmpd/$fn.rowid # Remove cache flag

4769 ## err "`ls $tmpd/$fn`"

4770 echo $fn

4771 done)

4772 files="$files $fns"

4773 ;;

4774 esac

4775 done < $1

4776 ## err FILES=$files "`ls -lF $tmpd`"

4777 subj="from ${REMOTE_ADDR}"

4778 (echo "$url"

4779 echo "への書き込みがありました。"

4780 echo "------"

4781 echo "$text"

4782 ) | (cd $tmpd &&

4783 err LS="`ls -lF`" &&

4784 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4785 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4786 echo "以下の内容で送信しました。" | html p

4787 viewtable $1 $tbl \

4788 `query "select rowid from $tbl order by rowid desc limit 1;"`

4789 echo "戻る" | html a "href=\"?\""

4790 }