s4: 4961963431f9 s4-funcs.sh

s4

view s4-funcs.sh @ 957:4961963431f9

Exclude quiz/enquete articles from new posts list of other users

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 09 Dec 2021 08:40:17 +0900
parents	946f2db59413
children	a0b1e62df613

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=${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=$((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/csv"'

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

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 fi

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

431 chmod o-r $logdir/error.out $logdir/debug.out

432 rm $sqi

433 # Attach supplemental DB

434 cat >&5 <<-EOF

435 .output /dev/null

436 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

440 .output stdout

441 EOF

442 }

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

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

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

446 fi

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

448 }

449 cleanup() {

450 trap '' INT HUP EXIT TERM PIPE

451 echo .quit >&5

452 kill $sq3pid

453 kill $sq3pid

454 rm -f $sqo $sqi

455 rm -rf $tmpfiles

456 cleanup2

457 }

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

459 # simple redirection for the sake of speed.

460 query() {

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

462 echo ".output $sqo" >&5

463 logstart

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

465 tee -a $querylog

466 else

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

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

469 fi >&5

470 echo ".output stdout" >&5

471 cat $sqo

472 rc=$?

473 logend

474 return $rc

475 }

476 _m4() {

477 #S4NAME=f,f,f

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

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

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

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

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

483 }

484 ismember() {

485 # $1=user, $2=group

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

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

488 }

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

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

491 }

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

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

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

495 }

496 isgrpowner() (

497 # $1=user, $2=group

498 gn=`sqlquote "$2"`

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

500 err isgrpowner: $sql

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

502 )

503 isgrpownerbygid() (

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

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

506 err isgrpownerbygid: $sql

507 test -n "`query $sql`"

508 )

509 getgroupadminmails() {

510 # $1=group

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

512 email4group "$1" "$i" ;

513 done

514 }

515 getgroupadmins() { # $1=group

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

517 qgrp=`sqlquote "$1"`

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

519 }

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

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

522 getvalbyid grp $2 \

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

524 }

525 getgroupbyid() {

526 # $1=id|gname

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

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

529 # err ggbyid: `echo $sql`

530 query $sql

531 }

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

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

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

535

536 # Return true when anonymous mode

537 [ "$anonymousmode" ] && return 0

538 # case `getvalbyid blog mode $2` in

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

540 # *closed*)

541 # owner=`getvalbyid blog owner $2`

542 # if isgrp $owner; then

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

544 # elif isuser $owner; then

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

546 # fi

547 # esac

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

549 case "$2" in

550 grp_*)

551 sql="SELECT 'owner'

552 FROM grp_adm

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

554 AND

555 user = '$user';"

556 ;;

557 *)

558 sql="with getblog as (

559 select key,val from blog_s where id=(

560 select blogid from article where id in

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

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

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

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

565 isgrpadm as (select user from grp_adm where

566 gname=(select val from getowner) and

567 user='$1'),

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

569 select case

570 when (select author from article where

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

572 then 'author'

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

574 then 'open'

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

576 then CASE

577 WHEN (SELECT val FROM isgrp) IS NULL

578 THEN

579 CASE WHEN (SELECT val from getowner)

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

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

582 ELSE ''

583 END

584 WHEN (select user from isgrpadm) IS NOT NULL

585 THEN 'i-am-admin'

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

587 END

588 when (select val from getmode) is null

589 then 'open'

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

591 then (SELECT user FROM isgrpadm)

592 when (select author from article where

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

594 then 'user+author'

595 else '' end;"

596 ;;

597 esac

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

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

600 # result=`query "$sql"`

601 # err FileAccessibility=$result

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

603 }

604 linkhome() {

605 # $1=UserOrGroupRowid

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

607 if isuser $1; then

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

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

610 name=`gecos $1|htmlescape`

611 else

612 _grid=`numericalize "$1"`

613 echo -n "grp+$1"

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

615 fi

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

617 }

618 hreflink() {

619 # s4 specific notation:

620 # ^href=URL

621 # ^iframe=URL

622 # ^video=URL

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

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

625 # OSM umap Wikistyle Notation:

626 # [[URL]] - Simle Link

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

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

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

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

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

632 # Other Style

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

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

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

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

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

638 # SPC+SPC+$ - <br>

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

640 checkboxON="${cb} checked>"

641 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

666

667 }

668 minitbl() {

669 sed -n '

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

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

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

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

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

675 H; # Concat this line to HoldSpace

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

677 $ b cont

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

679 }

680 :cont

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

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

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

684 # . of ^. is workaround for FreeBSD sed

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

686 p; # Print whole "table" element

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

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

689 }

690 x; # Back to the newest line

691 p; # Print rest' | miniul

692 }

693 miniul() {

694 sed -e '

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

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

697 :top

698 s/\n//;

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

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

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

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

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

704 $ b cont

705 N; # 次の行を読む

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

707 /^ /b top

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

709 p; s/.*//;

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

711 /^\* /b top

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

713 }

714

715 :cont

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

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

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

719 H; x

720 }

721 x' | miniol

722 }

723 miniol() {

724 sed -e '

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

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

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

728 x;

729 :top

730 s/\n//;

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

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

733 x;

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

735 x;

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

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

738 $ b cont

739 N; # 次の行を読む

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

741 /^ /b top

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

743 p; s/.*//;

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

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

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

747 }

748

749 :cont

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

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

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

753 H; x

754 }

755 x'

756 }

757 acclog() (

758 # $1=table, $2=rowid

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

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

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

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

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

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

765 fi

766 )

767 gecos() (

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

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

770 )

771 setpar() {

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

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

774 case "$1" in

775 user) _user="$v" ;;

776 skey) _skey="$v" ;;

777 esac

778 fi

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

780 }

781 unsetpar() {

782 for i; do

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

784 case "$i" in

785 user|skey) unset _$i ;;

786 esac

787 fi

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

789 done

790 }

791 replpar() {

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

793 }

794 getpar() {

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

796 val=""

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

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

799 user) val=$_user ;;

800 skey) val=$_skey ;;

801 esac

802 fi

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

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

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

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

807 fi

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

809 case "$var" in

810 owner)

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

812 echo $user; return

813 elif ismember $user $val; then

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

815 fi ;;

816 esac

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

818 printf '%s' "$val"

819 }

820 setskey() {

821 # For quick response...(?)

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

823 }

824 chkskey() {

825 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

831 return 0

832 fi

833 return 1

834 }

835 resetskey() {

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

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

838 fi

839 }

840 getpartype() {

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

842 }

843 getparcount() {

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

845 }

846 getparfilename() {

847 # null if type of $1 is not file

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

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

850 }

851 sqlquote() {

852 (v="$1"

853 case "$v" in

854 "") return ;; # null

855 "X'"*) # quoted hex string

856 echo $1 ;;

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

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

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

860 return ;;

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

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

863 return ;;

864 *)

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

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

867 else

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

869 fi ;;

870 esac)

871 }

872 sqlquotestr() (

873 case "$1" in

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

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

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

877 esac

878 )

879 mktempd() {

880 TMPDIR=$tmpd mktemp -d -t $session

881 }

882 getcachedir() { # $1=maintable

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

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

885 else

886 echo $tmpd/$thumbxy

887 fi

888 }

889 getval() {

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

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

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

893 echo "$user" ;;

894 stamp|datetime)

895 date "+%F %T" ;;

896 serial)

897 (s=`getpar $2`

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

899 *)

900 getpar "$2" "$3";;

901 esac

902 }

903

904 getvalquote() {

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

906 (v=`getval "$@"`

907 case "$v" in

908 "") echo NULL ;;

909 *) sqlquote "$v" ;;

910 esac)

911 }

912 getparquote() {

913 sqlquote "`getpar $1`"

914 }

915 getbinbyid() {

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

917

918 }

919 getvalbyid() {

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

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

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

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

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

925

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

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

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

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

930 return

931 fi

932 done

933 rowid=$3

934 pk=`gettblpkey $1`

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

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

937 td=${4:-$tmpd}

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

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

940 for kt in s m; do

941 t=${1}_$kt

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

943 vcount=1 # count(val)

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

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

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

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

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

949 if [ $kt = m ]; then

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

951 # Reset val to store filenames if type is string

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

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

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

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

956 i=0

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

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

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

960 i=$((i+1))

961 fn=$c.$i

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

963 case $type in

964 file:*)

965 #file=$td/$val

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

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

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

969 # FOR SPEED: Skip file generation if imgcache exists

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

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

972 && continue

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

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

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

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

977 select rowid from $cond $slice;

978 .output '$td/$fn'

979 select val from $cond $slice;

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

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

982 .output stdout

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

984 EOF

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

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

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

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

989 ls -lh "$file" |

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

991 case "$type" in

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

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

994 *|*:[Aa]pplication*)

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

996 png:- > "$file"

997 ;;

998 esac

999 ;;

1000 *)

1001 sq $db<<EOF

1002 .output $td/$fn.rowid

1003 select rowid from $cond $slice;

1004 .output $td/$fn

1005 select val from $cond $slice;

1006 EOF

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

1008 ;;

1009 esac

1010 done

1011 else

1012 rm -f $td/$c.count

1013 case $type in

1014 file:*)

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

1016 | while read fn; do

1017 file=$td/$fn

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

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

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

1021 | unhexize > "$file"

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

1023 case $type in

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

1025 *:[Aa]pplication*)

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

1027 png:- > $file ;;

1028 esac

1029 fi

1030 done

1031 ;;

1032 esac

1033 fi

1034 printf '%s' "$val"

1035 return

1036 fi

1037 done

1038 done)

1039 }

1040 getvalbypkey() (

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

1042 pk=`gettblpkey $1`

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

1044 getvalbyid "$1" "$2" $rowid $4

1045 )

1046 getvalbycond() {

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

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

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

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

1051 getvalbyid "$1" "$2" $rowid "$4"

1052 fi

1053 }

1054 getpwfield() {

1055 # getpwfield user column

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

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

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

1059 echo "$val"

1060 return 0

1061 else

1062 return 1

1063 fi

1064 }

1065 numericalize() {

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

1067 }

1068 encode() {

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

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

1071 sha1=sha1

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

1073 sha1=sha1sum

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

1075 sha1=gsha1sum

1076 fi

1077 fi

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

1079 }

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

1081 gs_pdfwrite() {

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

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

1084 }

1085 fi

1086 enjpeg() {

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

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

1089 cjpeg="cjpeg"

1090 else

1091 cjpeg="convert - jpeg:-"

1092 fi

1093 fi

1094 $cjpeg "$@"

1095 }

1096 mycrypt() (

1097 key=$1 salt=$2

1098 # err \$2=$2

1099 case $2 in

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

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

1102 esac

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

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

1105 )

1106 hexize() {

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

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

1109 hexize="xxd -p"

1110 else

1111 hexize_hd() {

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

1113 }

1114 hexize="hexize_hd"

1115 fi

1116 fi

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

1118 }

1119 unhexize() {

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

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

1122 unhex="xxd -p -r"

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

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

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

1126 EOF

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

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

1129 # world writable...:)

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

1131 fi

1132 fi

1133 cat "$@" | $unhex

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

1135 }

1136 percenthex() {

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

1138 }

1139 htmlescape() {

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

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

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

1143 }

1144 enascii() {

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

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

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

1148 else

1149 enascii_now=`date +%FT%T`

1150 enascii_sed() {

1151 nkf -Z0Z1Z2 \

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

1153 }

1154 enascii="enascii_sed"

1155 fi

1156 fi

1157 cat "$@" | $enascii

1158 }

1159 size_h() {

1160 i="$1" oi=$1

1161 set -- B B KB MB GB TB

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

1163 oi=$i

1164 i=$((i/1024))

1165 shift

1166 done

1167 echo ${oi}$1

1168 }

1169 gettblconf() {

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

1171 ## tconfs=`sq $db \

1172 tconfs=`query \

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

1174 fi

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

1176 }

1177 gettblkeys() {

1178 # $1=tbl

1179 gettblconf

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

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

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

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

1184 case $type in

1185 *p*)

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

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

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

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

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

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

1192 esac

1193 case $type in

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

1195 esac

1196 done

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

1198 }

1199 gettblpkey() {

1200 # $1=tbl

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

1202 }

1203 gettblfkey() {

1204 (x=`gettblkeys $1`

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

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

1207 }

1208 gettblcols() {

1209 (x=`gettblkeys $1`

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

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

1212 }

1213 gettbl_s_cols() {

1214 (x=`gettblkeys $1`

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

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

1217 }

1218 gettbl_m_cols() {

1219 (x=`gettblkeys $1`

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

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

1222 }

1223 gettbl_h_cols() {

1224 (x=`gettblkeys $1`

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

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

1227 }

1228 gettbl_coltype() (

1229 gettblconf

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

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

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

1233 )

1234 is_hidden() {

1235 # $1=Tbl $2=col

1236 gettblconf

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

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

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

1240 case $x in

1241 *h*) return 0 ;;

1242 *) return 1 ;;

1243 esac

1244 }

1245

1246 dbsetbyid() {

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

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

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

1250 quotedp=$(sqlquotestr "$p")

1251 unset primary update

1252 gettblconf

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

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

1255 #err conf=$conf

1256 case ${conf#*=} in

1257 p*) primary=1 ;;

1258 f*) update=1 ;;

1259 u*) ;;

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

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

1262 esac

1263 #err t=$t

1264 type=string fn=""

1265 case $conf in

1266 */password)

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

1268 ;;

1269 */image*|*/document*)

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

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

1272 ;;

1273 esac

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

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

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

1277 if [ "$primary" ]; then

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

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

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

1281 elif [ "$update" ]; then

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

1283 else

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

1285 fi

1286 )

1287 }

1288 expire() (

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

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

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

1292 )

1293 addsession() {

1294 # expireをセット

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

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

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

1298 # Remove old session parameters

1299 now=`expire now`

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

1301 }

1302 gencookie() (

1303 path=${URL#*:/}

1304 path=${URL%/*}

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

1306 for kv; do

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

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

1309 done

1310 )

1311 contenttype() {

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

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

1314 }

1315 putheader() {

1316

1317 }

1318 putfooter() {

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

1320 }

1321 getcookie() {

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

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

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

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

1326 case "$k" in

1327 user) _user="$v" ;;

1328 skey) _skey="$v" ;;

1329 esac

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

1331 done

1332 }

1333 genrandom() {

1334 # $1=columns (default: 10)

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

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

1337 }

1338 genserial() {

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

1340 }

1341 smail() {

1342 # smail rcpts subj (file)

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

1344 # $MAIL_FROM <- From: header value

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

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

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

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

1349 sender=${SENDER:-$admin}

1350 # Do not call m4 with directly passing text

1351 _r=$tmpd/rcpt

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

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

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

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

1356 }

1357 smail_queue_flush() {

1358 # $1=timelimit

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

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

1361 SELECT rowid FROM smailq

1362 GROUP BY rcpts, subj

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

1364 EOF

1365 )

1366 for rid in $rowids; do

1367 sq $workdb \

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

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

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

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

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

1373

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

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

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

1377 .separator "\n" "------------------\n\n"

1378 SELECT time, text FROM smailq

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

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

1381 ORDER by time;

1382 EOF

1383 then

1384 cat <<-EOF | sq $workdb

1385 DELETE FROM smailq

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

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

1388 EOF

1389 fi

1390 fi

1391 done

1392 }

1393 smail_queue() {

1394 # $1=Rcpts, $@=subj

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

1396 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1405 EOF

1406 )

1407 ## XXX: Adhoc load mitigation

1408 case "$now" in

1409 *[01])

1410 err flush_queue=$mintime

1411 smail_queue_flush ;;

1412 esac

1413 fi

1414 }

1415 setviastring() {

1416 table=par

1417 oifs="$IFS"

1418 IFS="&"

1419 for us in $1; do

1420 k=${us%%=*}

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

1422 setpar "$k" "string" "$v"

1423 done

1424 IFS="$oifs"

1425 }

1426 checkdomain() (

1427 # Check the validity of domain by referring DNS

1428 item=$1

1429 err checkdomain $1

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

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

1432 )

1433 pwcheck() {

1434 # $1=passwd

1435 dbpswd=`getpwfield $user pswd`

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

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

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

1439 }

1440 mypwhash() {

1441 mycrypt "`cat`" `genrandom 5`

1442 }

1443 flag_profupdate() {

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

1445 # This is useful to mitigate account sync load

1446 query <<-EOF

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

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

1449 EOF

1450 touch $userupdateflag

1451 }

1452 wasureta() {

1453 user=$1

1454 if ! checkdomain $user; then

1455 contenttype; echo

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

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

1458 putfooter

1459 exit 0

1460 fi

1461 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1463 encpswd=`echo $newpswd|mypwhash`

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

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

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

1467 $msgdir/mail-newaccount.m4 \

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

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

1470 }

1471 checkauth() {

1472 user=`getpar user`

1473 skc=`getpar skey` # from cookie

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

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

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

1477 if chkskey "$skc"; then

1478 return 0

1479 fi

1480 fi

1481 pswd=`getpar pswd`

1482 quser=`sqlquotestr "$user"`

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

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

1485 return 4 # 4=server too heavy

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

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

1488 return 2 # 2=login fail

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

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

1491 contenttype; echo

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

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

1494 cat <<-EOF | html p

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

1496 EOF

1497 exit 0 # XXXXX more smart...

1498 fi

1499 wasureta "$user"

1500 err wasureta issued for "$user"

1501 return 1 # wasureta error

1502 fi

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

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

1505 if pwcheck "$pswd"; then

1506 newsession=`genrandom 34`

1507 if setskey "$user" "$newsession" &&

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

1509 gencookie "user=$user" "skey=$newsession"

1510 return 0

1511 else

1512 return 4 # Heavy load??

1513 fi

1514 fi

1515 err "Login failed: [$user]"

1516 return 2 # Password mismatch

1517 }

1518 showlogin() {

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

1520 test -z "$args" && resetskey

1521 s4name=${S4NAME:-s4}

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

1523 cat $layout/login.m4.html

1524 echo '</body></html>'

1525 ) | _m4 \

1526 -D_BODYCLASS_="login" \

1527 -D_TITLE_="$s4name" \

1528 -D_SYSNAME_="Welcome to $s4name" \

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

1530 exit 0

1531 }

1532 dologin() {

1533 test -n "$S4WORLD" && syncaccount

1534 checkauth

1535 st=$?

1536 if [ $st != 0 ]; then

1537 contenttype; echo

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

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

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

1541 showlogin # and EXIT

1542 fi

1543 err "Auth OK: [$user]"

1544 }

1545

1546 # Do instant jobs here

1547 dbsetup

1548 trap cleanup INT HUP EXIT TERM PIPE

1549 # trap cleanup INT HUP

1550

1551

1552 cgiinit() {

1553 tmpd=`tmpd=$tmpdir mktempd`

1554 tmpf=$tmpd/stream.$$

1555 tmpfiles=$tmpfiles" $tmpd"

1556 addsession $session

1557 getcookie

1558 case "$REQUEST_METHOD" in

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

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

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

1562 # cat > $tmpf # too much?

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

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

1565 s="`cat $tmpf`"

1566 ;;

1567 esac

1568 case "$CONTENT_TYPE" in

1569 *boundary*)

1570 bndry=${CONTENT_TYPE#*boundary=}

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

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

1573 do

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

1575 #echo u=$us

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

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

1578 case "$k" in

1579 *:filename)

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

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

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

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

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

1585 :

1586 fi

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

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

1589 case "$mimetype" in

1590 [Ii]mage/x-xcf)

1591 bzip2 "$tmpd/$v"

1592 v=${v}.bz2

1593 ;;

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

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

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

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

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

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

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

1601 v=$vjpg

1602 else

1603 mimetype="Not supported"

1604 fi

1605 ;;

1606 [Ii]mage/*)

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

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

1609 ;;

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

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

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

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

1614 err Calling gs from $v to $nv

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

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

1617 v=$nv

1618 fi

1619 fi

1620 fi

1621 esac

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

1623 then

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

1625 continue

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

1627 replpar text string \

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

1629 continue

1630 fi

1631 ;;

1632 image|document|binary)

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

1634 continue ;;

1635 *)

1636 type='string'

1637 ;;

1638 esac

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

1640 setpar "$k" "$type" "$v"

1641 done

1642 ;;

1643 *)

1644 setviastring "$s"

1645 ;;

1646 esac

1647 }

1648 email4group() {

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

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

1651 users=`for i; do sqlquote "$i"; done`

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

1653 sql="WITH

1654 grpemails AS (

1655 SELECT gname, user, val email

1656 FROM grp_mem NATURAL JOIN grp_mem_s

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

1658 useremails AS (

1659 SELECT user.name, val email

1660 FROM user

1661 LEFT JOIN user_m

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

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

1664 FROM useremails u LEFT JOIN grpemails g

1665 ON u.name=g.user

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

1667 query "$sql"

1668 }

1669 email4groupbyuid() {

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

1671 g=$1; shift

1672 uids=`echo "$@"`

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

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

1675 email4group "$g" `query "$sql"`

1676 }

1677 myemail4group() {

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

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

1680 }

1681 collectmembersbyid() {

1682 # Collect user names of group specified by grid

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

1684 query "SELECT user FROM grp_mem \

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

1686 }

1687 collectmembersbyid() {

1688 # Collect user names of group name

1689 qgrp=`sqlquote "$1"`

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

1691 }

1692 collectgecosesbyid() {

1693 # Collect user gecoses of group

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

1695 query<<-EOF

1696 SELECT gecos

1697 FROM gecoses

1698 WHERE name IN (SELECT user FROM grp_mem

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

1700 EOF

1701 }

1702 collectemail() (

1703 # Collect email addresses for group $1

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

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

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

1707 for e; do

1708 if isuser "$e"; then

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

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

1711 else

1712 qgrp=`sqlquote "$e"`

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

1714 gmem="grp_mem"

1715 else

1716 tm=`sqlquote "$TEAM"`

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

1718 fi

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

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

1721 $gmem g left join grp_mem_s s

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

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

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

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

1726 query "$sql"

1727 fi

1728 done

1729 )

1730 sendinvitation() (

1731 # $1=email

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

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

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

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

1736 gecos=`gecos`

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

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

1739 _m4 -D_URL_="$urlbase" \

1740 -D_USER_="$name" \

1741 -D_EMAIL_="$1" \

1742 -D_REGIST_="$regist" \

1743 -D_ADMIN_="$admin" \

1744 $msgdir/mail-invite.m4 \

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

1746 return 0

1747 )

1748 emaildomaincheck() {

1749 case "$1" in

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

1751 *@*)

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

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

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

1755 return 2

1756 fi

1757 return 0

1758 ;;

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

1760 esac

1761 }

1762 invite() {

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

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

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

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

1767 return

1768 fi

1769 fi

1770 case "$email" in

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

1772 *@*)

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

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

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

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

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

1778 elif sendinvitation $email; then

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

1780 else # Cannot be reached here

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

1782 fi ;;

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

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

1785 esac

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

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

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

1789 fi

1790 if [ -f $invite_policy ]; then

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

1792 else

1793 pol="$invite_policy"

1794 fi

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

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

1797 -D_INVITE_POLICY_="$pol" \

1798 $layout/html.m4.html $layout/invite.m4.html

1799 }

1800 regist() {

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

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

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

1804 echo "bye bye" | html p

1805 reutrn

1806 fi

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

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

1809 cat<<EOF

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

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

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

1813 EOF

1814 return

1815 fi

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

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

1818 # Fake login password to wasureta

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

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

1821 wasureta $email

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

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

1824 addsession $1 # for removal after 1 minute

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

1826 $layout/login.m4.html

1827 return

1828 }

1829 group_safename() {

1830 # Convert $1 to safe group name

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

1832 }

1833 groupupdate() {

1834 gname=`getpar gname`

1835 qgname=`sqlquote "$gname"`

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

1837 # See ALSO same job in showgroup()

1838 newgname=`group_safename "$gname"`

1839 err newgname=$newgname

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

1841 err NewGNAME: gname=$newgname

1842 gname=$newgname

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

1844 replpar gname string "$gname"

1845 fi

1846 # Name confliction check

1847 parow=`getpar rowid`

1848 ## err parow=$parow

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

1850 query "BEGIN EXCLUSIVE;"

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

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

1853 if [ $count -gt 0 ]; then

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

1855 query "END;"

1856 return

1857 fi

1858 par2table $formdir/grp.def

1859 query "END TRANSACTION;"

1860 # Remove orphan

1861 : <<EOF

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

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

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

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

1866 EOF

1867 rm=`getpar rm` cfm=`getpar confirm`

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

1869 if [ x"$rm$cfm" = x"yesyes" ]; then

1870 if [ -z "`query \"select gname from grp where gname=$qgname;\"`" ]; then

1871 sql="delete from blog where id in

1872 (select id from blog_s where key='owner' and val=$qgname);"

1873 err rm-grp cleaning sql=`echo $sql`

1874 query "$sql";

1875 unsetpar tag kwd

1876 grps # When removing a group, switch to grp-list

1877 return # and return

1878 fi

1879 fi

1880 [ -z "$parow" ] && joingrp "$gname" "$user" yes "" as-admin

1881 fi

1882 sql="select rowid from grp where gname=$qgname;"

1883 grid=$(query $sql)

1884 ## err grpupdate:new-grid=$grid, sql=$sql

1885 grp $grid

1886 }

1887 groupclone() {

1888 # $1=grp-rowid of clone-base group

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

1890 case "$1" in

1891 */noteam)

1892 noteam="AND key != 'team'" ;;

1893 esac

1894 qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$rid;"`

1895 if [ -z "$qgrp" ]; then

1896 echo "無効なグループIDです($1)" | html p

1897 return

1898 fi

1899 if ! isgrpownerbygid "$user" "$rid"; then

1900 echo "グループ管理者のみがクローン可能です" | html p

1901 return

1902 fi

1903 i=0

1904 while true; do

1905 copy="-copy$i"

1906 newqname=`query "SELECT quote($qgrp || '$copy');"`

1907 # err Trying new grp=$newqname with copy=$copy

1908 test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`

1909 if [ -n "$test" ]; then

1910 i=$((i++))

1911 continue

1912 fi

1913 break

1914 done

1915 # Creating New group "$newqname" with members of old group

1916 # err Creating new grp=$newqname with copy=$copy

1917 query<<-EOF

1918 BEGIN;

1919 INSERT INTO grp VALUES($newqname); -- Create NEW one

1920 REPLACE INTO grp_s(gname, key, val) -- Copy tag

1921 SELECT $newqname, key, val

1922 FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');

1923 REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"

1924 SELECT $newqname, key, type, val || '$copy'

1925 FROM grp_s WHERE gname=$qgrp AND key='gecos';

1926 -- Copy members and their configuration --

1927 REPLACE INTO grp_mem SELECT $newqname, user

1928 FROM grp_mem WHERE gname=$qgrp;

1929 REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin

1930 FROM grp_mem_s WHERE gname=$qgrp;

1931 REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin

1932 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1933 -- Copy administrators --

1934 REPLACE INTO grp_adm SELECT $newqname, user

1935 FROM grp_adm WHERE gname=$qgrp;

1936 COMMIT;

1937 EOF

1938 newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`

1939 STOPCLONEMSG=1 groupconf "$newrowid"

1940 }

1941 groupman() {

1942 note="<p>グループ名に使用できない文字は自動的に削除されます。</p>"

1943

1944 GF_STAGE="grpconf"

1945 GF_STAGE=groupupdate

1946 DT_VIEW=grp dumptable html grp 'gname gecos:DESC mtime:TIME' 'order by b.TIME desc' \

1947 |_m4 -D_TITLE_="グループ作成" \

1948 -D_FORM_="$note`genform $formdir/grp.def`" \

1949 -D_DUMPTABLE_="syscmd(cat)" \

1950 $layout/html.m4.html $layout/form+dump.m4.html

1951 }

1952 userconf() {

1953 [ -n "`getpar rowid`" ] && par2table $formdir/user.def

1954 _m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html

1955 GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user"

1956 }

1957 groupconf() {

1958 # $1=rowid in grp (2015-07-21 changed from gname)

1959 [ -n "`getpar rowid`" ] && par2table $formdir/grp.def

1960 _m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html

1961 #rowid=`query "select rowid from grp where gname='$1';"`

1962 rowid=${1%%[!A-Z0-9a-z_]*}

1963

1964 ### If user is not admin, lead to group home

1965 grp=`getgroupbyid $rowid`

1966 if ! isgrpowner "$user" "$grp"; then

1967 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

1968 return

1969 fi

1970

1971 # GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804

1972 GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"

1973 if [ -z "$STOPCLONEMSG" ]; then

1974 ## Setup migration menu

1975 height="10em" ## Ugly!!

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

1977 v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`

1978 err v=$v

1979 if [ -n "$v" ]; then

1980 migrate=$(cat<<-EOF

1981 `cgi_radio grpaction migrate id="migrate"`<label

1982 for="migrate">別Worldへ移住</label>

1983 <div style="height: $height;">

1984 <form action="?migrategrp">

1985 <p>移住先:<select name="migrateto">$nl$v$nl</select></p>

1986 <p>グループや掲示板のURLが変わります。

1987 外部からリンクしている場合は飛べなくなります。

1988 すでにリンクされた掲示板を多数含む場合は既存グループを温存し、

1989 「グループのクローン」で

1990 メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>

1991 <p><label>`cgi_checkbox emichk yes`確認</label></p>

1992 `cgi_hidden stage migrategrp`

1993 `cgi_hidden rowid $rowid`

1994 `cgi_submit OK`

1995 `cgi_reset Reset`

1996 </form>

1997 </div>

1998 EOF

1999 )

2000 fi

2001 fi

2002 html div 'class="foldtabs"' <<-EOF

2003 `cgi_radio grpaction clone id="clone"`<label

2004 for="clone">グループのクローン作成</label>

2005 <div style="height: $height;">

2006 <p>構成メンバーが同じ新規グループを作成します。</p>

2007 <table>

2008 <tr><td><a href="?groupclone+$rowid">

2009 <button>クローン作成(チームも複製)</button></a></td>

2010 <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>

2011 <tr><td><a href="?groupclone+$rowid/noteam">

2012 <button>作成(チームなし)</button></a></td>

2013 <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>

2014 </table>

2015 <p>ボタンを押すと即作成します。不要な場合はグループ編集で

2016 削除してください。</p>

2017 </div>

2018 $migrate

2019 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>

2020 <div style="height: $height; background: transparent;"></div>

2021 EOF

2022 fi

2023 }

2024 migrategrp() {

2025 rowid=`getpar rowid`

2026 rowid=${rowid%%[!0-9]*}

2027 grp=`getgroupbyid $rowid`

2028 if ! isgrpowner "$user" "$grp"; then

2029 echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"

2030 return

2031 fi

2032 if [ x`getpar emichk` != x"yes" ]; then

2033 echo "移住確認未チェックなので中止します。" | html p

2034 grp "$rowid"

2035 return

2036 fi

2037 destconf=`getpar migrateto`

2038 err destconf=$destconf

2039 if [ ! -e $destconf ]; then

2040 echo "移住先Worldが認識できないので中止します($destconf)。" | html p

2041 grp "$rowid"

2042 return

2043 fi

2044 if [ -n "$worldconf" ]; then

2045 srcconf=$worldconf

2046 else

2047 srcconf=s4-config.sh

2048 fi

2049 _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html

2050 echo "移住操作" | html h1

2051 echo '<pre>'

2052 set -- "$srcconf" "$destconf" "$rowid"

2053 err ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"

2054 . ./s4-migrate.sh # Dot(.) sourcing might not pass arguments

2055 rc=$?

2056 echo "</pre>"

2057 if [ $rc -eq 0 ]; then

2058 echo "World [$world] への移住完了。" | html p

2059 echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"

2060 elif [ -n "$faillist" ]; then

2061 echo "移住後削除失敗" | html p

2062 echo "空いている時間帯に再度試して下さい。" | html p

2063 else

2064 echo "移住失敗" | html p

2065 echo "移動先に重複がないか確認して下さい。" | html p

2066 fi

2067 return

2068 }

2069 mems() {

2070 _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html

2071 kwd=`getpar kwd`

2072 listmember $kwd

2073 }

2074 grps() {

2075 case "$S4WORLD" in

2076 $nonewgroupworld) ;;

2077 *) LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;

2078 esac

2079 _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html

2080 kwd=`getpar kwd`

2081 listgroup "$kwd" \

2082 | _m4 -D_DUMPTABLE_="syscmd(cat)" \

2083 -D_TITLE_="グループ関連操作" \

2084 -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \

2085 $layout/form+dump.m4.html

2086 }

2087 grp() { # $1=group-rowid

2088 gpg=`getpar grp`

2089 grid=${1:-$gpg}

2090 grp=`getgroupbyid "$grid"`

2091 ## . ./s4-blog.sh

2092 jg=`getpar joingrp`

2093 if [ -n "$jg" ]; then

2094 [ -n "$jg" -a -n "$grp" ] &&

2095 joingrp "$grp" "$user" "$jg" "`getpar email`"

2096 fi

2097 htmlheader=$layout/html.m4.html

2098 showgroup "$grid"

2099 }

2100 sql4interestblogs() {

2101 cat<<EOF

2102 CREATE TEMPORARY VIEW interestblogs AS

2103 SELECT blog.rowid rid, id, author

2104 FROM blog

2105 NATURAL JOIN

2106 (SELECT id, val owner FROM blog_s WHERE key='owner') bs

2107 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2108 THEN 1 -- blog owner is an user, READABLE when

2109 WHEN (SELECT user FROM grp_mem

2110 WHERE gname=bs.owner AND user='$user') IS NULL

2111 THEN 0

2112 ELSE 1

2113 END;

2114 EOF

2115 }

2116 listnewblogsql() { # $1=user

2117 newdays=${WHATS_NEW_DAYS##*[!0-9]} # Shave non-digits

2118 newdays=${newdays%%[!0-9]*}

2119 newdays=${newdays:-14}

2120 basetime="datetime('now', 'localtime', '-${newdays} days')"

2121 deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog

2122 WHERE user='$user'

2123 AND tblrowid IN

2124 ($blogreadflagrowid,

2125 $blogcutoffflagrowid)),

2126 $basetime -- "0"

2127 );"`

2128 cat<<EOF

2129 `sql4interestblogs`

2130 WITH article_ctime as (

2131 SELECT id,blogid,author,max(val) ctime

2132 FROM article join article_s s using(id)

2133 WHERE s.key='ctime' AND s.val > '$deftime'

2134 GROUP BY id

2135 ), blog_title_owner as (

2136 SELECT blg.rid brid, id,

2137 max(case key when 'title' then val end) title,

2138 max(case key when 'owner' then val end) owner

2139 FROM interestblogs blg, blog_s using(id) group by id

2140 ), blogall as (

2141 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid

2142 ), news as (

2143 SELECT brid, bl.id blid, bl.title, ctime,

2144 coalesce(al.time, '$deftime') atime,

2145 count(bl.id) "新着", bl.author

2146 FROM blogall bl

2147 LEFT JOIN

2148 (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al

2149 ON bl.brid=al.tblrowid

2150 WHERE atime < bl.ctime

2151 GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id

2152 LIMIT 10

2153 ) SELECT brid LINK, "新着",

2154 (SELECT count(*) FROM article WHERE blogid=blid) "総数",

2155 ctime, title,

2156 (SELECT gecos FROM gecoses WHERE name=author) gecos

2157 FROM news;

2158 EOF

2159 }

2160

2161 search_form() {

2162 # $1 = { author=<AUTHOR> | grid=<GroupRowid> }

2163 # $2(optional) = pre-input keywords

2164 help="(1)空白区切りの単語で本文検索

2165 (2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索

2166 @2016-0[1-6] → 2016年1月から6月

2167 @>2016-01 @<2016-02-15 → 2016年1月から2月14日までの期間

2168 @week → 最近一週間

2169 (3)#番号で記事ID検索

2170 (1)と(2)は組み合わせOK

2171 例: @2016-10-0[1-9] 芋煮

2172 → 2016年10月上旬でキーワード「芋煮」を含む記事検索

2173 ※クイズ板は検索対象から外されます。"

2174 auth=""

2175 placeholder="全記事からの検索"

2176 case "$1" in

2177 author=*)

2178 a=`echo "${1#author=}"|htmlescape`

2179 g=`gecos ${1#author=}`

2180 auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"

2181 placeholder="このユーザの書込検索"

2182 help="★★ $g さんの書き込みから検索します$nl$help"

2183 ;;

2184 grid=*)

2185 a=`echo "${1#grid=}"`; a=$((0 + $a))

2186 auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"

2187 placeholder="このグループからの検索"

2188 ;;

2189 esac

2190 inikwd="$2" # no need to htmlescape

2191 cat<<-EOF

2192 <div class="right">

2193 <form action="$myname">$auth

2194 <input type="text" name="kwd" value="$inikwd" title="$help"

2195 placeholder=" $placeholder " width="10" accesskey="k">

2196

2197 ${touchpanel:+<p class="help">$help</p>}

2198 <input type="hidden" name="stage" value="searchart">

2199

2200 </form>

2201 </div>

2202 EOF

2203 }

2204

2205 imgsrc_cache() (

2206 # $1 = directory for cache'ing

2207 # $2 = table (user_m or grp_m)

2208 # $3 = keycond (was: condition for choosingowner)

2209 # $4 = size : S = Small, M = Medium, O = Original

2210 dir="$1" tbl="$2"

2211 keycond="$3"

2212 whos="$keycond AND key='profimg' AND type LIKE 'file:image%'

2213 ORDER BY rowid DESC LIMIT 1"

2214 [ -d "$dir" ] || mkdir -p "$dir"

2215 tmpf=$tmpd/imgsrc_cache.$$

2216 case "$4" in

2217 [Ss]) size=S ;;

2218 [Oo]) size=O ;;

2219 *) size=M ;;

2220 esac

2221 # ImageCache filename storing schema:

2222 # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}

2223 sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"

2224 sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"

2225 valtype=`query "$sql0"`

2226 filename=${valtype%%//*}

2227 filetype=${valtype##*//file:}

2228 if [ x"$filename" = x"${filename%.*}" ]; then

2229 # If nor filename extension found, set it to image type

2230 case "$filetype" in

2231 image/*) filename=$filename.${filetype#image/} ;;

2232 esac

2233 fi

2234 cacheimg_S=$dir/S_$filename

2235 cacheimg_M=$dir/M_$filename

2236 cacheimg_O=$dir/$filename

2237 cacheimg=$dir/${size}_$filename

2238 sumfile="$dir/$filename.sum"

2239 sum=`query "$sql1" | tee $tmpf | encode` # encode() is maybe sha1

2240 if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \

2241 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2242 # if cache is fresh and has the same checksum,

2243 echo "<img src=\"$cacheimg\">"

2244 else

2245 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2246 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2247 fifo2=$fifo.2

2248 mkfifo $fifo $fifo2

2249 fmt=${filename##*.}

2250 ## [[ NOTE ]]

2251 ## a. convert oldimage newimage

2252 ## b. convert oldimage fmt:- | convert - newimage

2253 ## b is much smaller than a

2254 cat $tmpf | unhexize \

2255 | tee $fifo \

2256 | convert -define ${fmt}:size=${iconxy_M} \

2257 -resize ${iconxy_M}'>' - ${fmt}:- \

2258 | tee $fifo2 \

2259 | convert - "$cacheimg_M" &

2260 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2261 -resize ${iconxy_S}'>' - ${fmt}:- \

2262 | convert - "$cacheimg_S" &

2263 printf '%s' "<img src=\"data:${filetype},"

2264 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2265 echo '">'

2266 echo "$sum" > $sumfile

2267 fi

2268 ## Now preparing cache image, done.

2269 ## Store this information to DB

2270 stbl=${tbl%_m}_s # user_s or grp_s

2271 pkey=${keycond%%=*} # Primary Key name

2272 pval=${keycond#*=} # Primary Key value

2273 query <<-EOF

2274 REPLACE INTO $stbl($pkey, key, type, val)

2275 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2276 EOF

2277 )

2278

2279 showhome() {

2280 # $1=userRowIdToShow

2281 err showhome \$1=$1

2282 case "$1" in

2283 *@*) uname=`getvalbypkey user name "$1"` ;;

2284 *) uname=`getvalbyid user name $1` ;;

2285 esac

2286 ## err ShowHome: uname=$uname

2287 td=`getcachedir home/"$1"`

2288 gecos=`gecos "$uname"`

2289 ## err SH:gecos=$gecos

2290 GF_VIEWONLY=1

2291 cond="gname in (select gname from grp_mem where user='$uname')"

2292 search_form_args=""

2293 if [ x"$user" = x"$uname" ]; then

2294 if [ -z "$S4MASTERDB" ]; then

2295 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2296 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "

2297 elif [ -n "$S4MASTERURL" ]; then

2298 usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"

2299 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "

2300 fi

2301 usermenu="$usermenu

2302 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2303 # Display folders

2304 sql="select count(id) from article_m where id

2305 in (select id from article where author='$user')

2306 and type like 'file:%';"

2307 ## err nfile-sql=`echo "$sql"`

2308 nfile=`query "$sql"`

2309 # err nfile=$nfile

2310 if [ $nfile -gt 0 ]; then

2311 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2312 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2313 fi

2314 else

2315 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2316 GROUP BY user;"`

2317 usermenu="<p>Last seen on $latestlog</p>"

2318 search_form_args="author=$uname"

2319 fi

2320 . ./s4-blog.sh

2321

2322 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2323 search_form "$search_form_args" > $sf

2324 printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \

2325 | htmlescape > $tf

2326 { echo "<div class=\"noprofimg\">"

2327 viewtable $formdir/user.def user $1

2328 echo "</div>"

2329 } > $pf

2330

2331 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2332 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2333 imf=$tmpd/profimg.$$; touch $imf

2334 if [ -n "$img" ]; then

2335 if true; then

2336 tbl=user_m

2337 enticond="name='$uname'"

2338 imgsrc_cache "$td/main" user_m "$enticond" M

2339 else

2340 { printf '%s' "<IMG src=\"data:${img#file:},"

2341 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2342 | sed 's/$..$/%\1/g'

2343 echo '">'

2344 }

2345 fi > $imf

2346 fi

2347 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2348 val='$uname';"`

2349 listblog $uname > $bf

2350

2351 hometail=$tmpd/tail.$$

2352 mkfifo $hometail

2353

2354 #Calling listgroupbytable, originally here

2355

2356 (

2357 # Display Most Recent Entry

2358 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2359 shortval=${shortval:-val}

2360

2361 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2362 # $hidden_mode is defined in global section

2363 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2364 ctime,

2365 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2366 (SELECT gecos FROM gecoses

2367 WHERE name=(SELECT val FROM blog_s

2368 WHERE key='owner' AND id=b.id)) owner,

2369 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2370 FROM blog b

2371 JOIN

2372 (SELECT distinct blogid, a.id aid, max(val) ctime

2373 FROM article a, article_s s

2374 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2375 GROUP BY blogid ORDER BY val DESC LIMIT 50

2376 ) m

2377 ON b.id=m.blogid

2378 AND

2379 ('$uname' = '$user' -- user can read all own posts

2380 OR

2381 -- exclude posts of others in quiz/enquete blogs

2382 NOT EXISTS (SELECT * FROM blog_s

2383 WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"

2384 # This should be as follows

2385 : <<EOF

2386 WITH arts AS(

2387 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2388 a.blogid, a.id id, s.val ctime

2389 FROM article a NATURAL JOIN article_s s

2390 WHERE s.key = 'ctime' AND a.author='$user'

2391 GROUP by s.id

2392 )

2393 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2394 FROM arts a0

2395 JOIN

2396 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2397 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2398 ORDER BY ctime DESC LIMIT 50;

2399 EOF

2400

2401 cat<<-EOF

2402 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2403 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2404 <div class="lcto">

2405 `DT_VIEW=replyblog dumptable html blog`

2406 </div>

2407 EOF

2408 unset DT_SQL

2409 if [ x"$user" = x"$uname" ]; then

2410 # Display NEWS

2411 # 2016-06-26

2412 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2413 acclog blog $blogreadflagrowid

2414 # echo "全部既読にしました" | html p

2415 fi

2416 # 2016-02-19 Counting NEWS without using dumptable.

2417 sql=`listnewblogsql "$user"`

2418 # echo "$sql" > tmp/listnew

2419 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2420 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2421 cont=$((cont-1))

2422 ##err newcount=$cont

2423 if [ $cont -gt 0 ]; then

2424 #echo "全体の新着記事${cont}傑" | html h2

2425 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2426 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2427 cat<<-EOF | html form 'action="?home"'

2428 `cgi_checkbox readchk yes 'id="read"'`<label

2429 for="read">新着ふくめて全部読んだことにする</label>

2430 `cgi_submit '確定'`

2431 `cgi_hidden read yes`

2432 EOF

2433 echo "$new10 "

2434 echo "</div>"

2435 else # If news is 0, set log cut off flag

2436 acclog blog $blogcutoffflagrowid # for speed

2437 fi

2438 else # Not My Home ($user != $uname)

2439 : # DT_SQL=

2440 fi

2441 ) > $hometail & # Is background call safe to m4??

2442 #

2443 listgroupbytable $formdir/grp.def "$cond" $uname |

2444 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2445 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2446 -D_PROFIMG_="spaste(\`$imf')" \

2447 -D_BLOGS_="spaste(\`$bf')" \

2448 -D_SEARCH_="spaste(\`$sf')" \

2449 -D_NBLOG_="$nblog" \

2450 -D_GROUPS_="syscmd(\`cat')" \

2451 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2452 $layout/html.m4.html $layout/home.m4.html

2453

2454 # Record access log

2455 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2456 }

2457 commission() { # $1=grp-rowid $2=user-rowid

2458 contenttype; echo

2459 ## err commission: "$@"

2460 gname=`getgroupbyid $1`

2461 echo "グループ $gname 管理者委任" \

2462 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2463 if [ -n "$2" ]; then

2464 grp_reg_adm "$@"

2465 else

2466 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2467 fi

2468 }

2469 listgroupbytable() {

2470 # $1=deffile $2...=condition $3(optional)=uname

2471 tagline=`grep :tag: $1`;

2472 and="${2:+and }" where=${2:+where }

2473 href="<a href=\"$myname?grp+"

2474 echo '<div class="listgroup">'

2475 sql="select val from grp_s where key='tag' $and$2 group by val;"

2476 ## err ListGRP: query sql="$sql"

2477 for tag in `query "$sql"`

2478 do

2479 ## err ListGrp: tag=$tag

2480 tn=${tagline%%=${tag}*}

2481 tn=${tn##*[ :]}

2482 sql="select rowid||':'||gname as 'グループ名',説明 from

2483 (select (select rowid from grp g where g.gname=grp_s.gname)

2484 as rowid,

2485 gname,

2486 max(case key when 'gecos' then val end) as '説明',

2487 max(case key when 'tag' then val end) as 'tag',

2488 max(case key when 'mtime' then val end) as mtime from grp_s

2489 $where$2 group by gname having tag='$tag' order by mtime desc);"

2490 ## err PersonalGroupList= `echo $sql`

2491 echo "<h2>$tn</h2>"

2492 echo '<table class="b listgroup">'

2493 sq -header -html $db "$sql" \

2494 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2495 echo '</table>'

2496 done

2497 if [ -n "$S4WORLDLIST" -a -n "$3" ]; then

2498 err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"

2499 peekgrpworlds mem:"$3"

2500 err "peekgrpworlds($user) END: `gdate +%S.%03N`"

2501 fi

2502 echo '</div>'

2503 }

2504 iconhref() (

2505 # $1=icon-file, $2=Href $3=title $4...=anchor

2506 data=`percenthex "$1"`

2507 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2508 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2509 href=$2; title=$3; shift 3

2510 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2511 )

2512 iconhref2() (

2513 # $1=icon-file, $2=Href $3=title $4...=anchor

2514 src=$1

2515 href=$2; title=$3; shift 3

2516 anchor=`echo $@|htmlescape`

2517 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</a>"

2518 )

2519 listentry() (

2520 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2521 # Referring variable $iamowner=$grp to attach owner-request links

2522 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2523 cond='' hiddens=''

2524 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2525 if [ -z "$offset" ]; then

2526 offset=`getpar start`; offset=${offset%%[!0-9]*}

2527 offset=$((offset-1))

2528 fi

2529 offset=$((offset + 0)) # change to numeric forcibly

2530 [ $offset -lt 0 ] && offset=0

2531 limit=$listentlimit

2532 dir=`getcachedir "$1"`

2533 if [ x"$1" = x"user" ]; then

2534 hrb="$myname?home"

2535 deficon=person-default.png

2536 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2537 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2538 gcs=gecos

2539 else # if group

2540 hrb="$myname?grp"

2541 deficon=group-default.png

2542 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2543 gcs=name

2544 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2545 if [ -n "$tagline" ]; then

2546 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2547 ## err tagconv=$tagconv

2548 fi

2549 fi

2550 if [ ! -d $dir ]; then

2551 mkdir -p $dir

2552 fi

2553 if [ ! -s $dir/$deficon ]; then

2554 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2555 fi

2556 kwd=${2:-`getpar kwd`}

2557 if [ -n "$kwd" ]; then

2558 kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`

2559 case "$kwd" in

2560 mem:*@*)

2561 byuser=${kwd#*mem:}

2562 qusr=`sqlquote "$byuser"`

2563 cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"

2564 ;;

2565 esac

2566 if [ x"$1" = x"group" ]; then

2567 if [ -n "$cond1" ]; then

2568 enthead="`gecos "$byuser"|htmlescape` さんの所属"

2569 else

2570 cond1="(b.name like '%${kwd}%')"

2571 fi

2572 else

2573 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2574 fi

2575 fi

2576 tag=`getpar tag` tag2=`getpar tag2`

2577 if [ x"$tag" = x"NULL" ]; then

2578 tag="" tag2=""

2579 fi

2580 if [ -n "$tag$tag2" ]; then

2581 tag=${tag:-$tag2}

2582 qtag=`sqlquote "$tag"`

2583 cond2="tag=$qtag"

2584 fi

2585 if [ -n "$cond1$cond2" ]; then

2586 cond="$cond1${cond2:+ AND $cond2}"

2587 cond="WHERE ${cond# AND }"

2588 fi

2589

2590 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2591 # grpは呼出し元の動的スコープ変数でよくないな...

2592 ##qgrp=`sqlquote $grp`

2593 getgrp="(select gname from grp where rowid=${rowid:--1})"

2594 sql="select a.rowid, a.$link,

2595 coalesce(b.$gcs, a.$nm) as nick,

2596 quote(a.$nm) as qname,

2597 (SELECT val FROM ${tbl}_s

2598 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2599 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2600 || case when a.$nm in (select gname from grp)

2601 then printf('(%d名)',

2602 (select count(user) from grp_mem where gname=a.$nm))

2603 else ' <'||a.$nm||'>'

2604 end

2605 as name,

2606 b.tag,

2607 case when a.$nm in (select user from grp_adm

2608 where gname=$getgrp) then '管理者'

2609 when '$user' in (select user from grp_adm where gname=a.$nm)

2610 then 'ADMIN'

2611 when '$user' in (select user from grp_mem where gname=a.$nm)

2612 then 'Member'

2613 when '$iamowner' = '' then ''

2614 else ',not='||a.rowid end as ownerlink,

2615 CASE '$entity'

2616 WHEN 'グループ'

2617 THEN coalesce(

2618 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2619 'open')

2620 ||

2621 CASE WHEN '$user'

2622 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2623 THEN ' ismember'

2624 ELSE ''

2625 END

2626 ELSE 'user'

2627 END regmode

2628 from $tbl a left join

2629 (select $nm as name,

2630 max(case key when 'gecos' then val end) as gecos,

2631 max(case key when 'tag' then val end) as tag,

2632 max(case key when 'mtime' then val end) as mtime,

2633 max(case key when 'wtime' then val end) as wtime,

2634 max(case key when 'login' then val end) as login

2635 from ${tbl}_s group by $nm)

2636 b on a.$nm=b.name $cond $3

2637 order by b.wtime desc, b.login desc,

2638 b.mtime desc, b.tag desc, a.rowid asc"

2639 # Give precedence to newer maintained groups (2016-09-24)

2640 # Note that mtime is stored only in grp_s.

2641 ## err LE:sql.1="$sql"

2642 total=`query "with x as ($sql) select count(*) from x;"`

2643 echo "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2

2644 echo '<div class="listentry">' # List-entry div

2645 # Show owner/member filter button

2646 METHOD=GET

2647 hiddens="$hiddens

2648 `cgi_hidden stage \"$stage\"`"

2649 if [ x"$tbl" = x"grp" ]; then

2650 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2651 fh="<select name=\"tag\">$nl"

2652 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2653 for l in $args; do

2654 val=${l#*=} tname=${l%=*}

2655 if [ x"$val" = x"$tag" ]; then

2656 s=" selected"

2657 selectedtags="(種別[${tname}]のみ)"

2658 else

2659 s=""

2660 fi

2661 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2662 done

2663 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2664 cat<<-EOF

2665 <form action="$myname" method="$METHOD">

2666 </form>

2667 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2668 for="ismembtn">参加中以外隠す</label>

2669 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2670 for="isadmbtn">管理者参加以外隠す</label>

2671 EOF

2672 # limit=3

2673 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2674 fi

2675 if [ $total -gt $limit -o $ -n "$S4WORLDLIST" -a x"$tbl" = x"grp" $ ]; then

2676 echo '<div>'

2677 METHOD=GET cgi_form $stage <<EOF

2678 $form

2679 <label>次の語を含む${entity}で検索:

2680 `cgi_text kwd "$kwd"`</label>

2681 $hiddens

2682 EOF

2683 echo '</div>'

2684 else

2685 echo $selectedtags | html p

2686 fi

2687 test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"

2688 cat<<EOF

2689 <form action="$myname" method="$METHOD">

2690 <p>${total}件中の<input class="hidesub" type="text" name="start"

2691 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2692 <input type="submit" value="確定"></p>

2693 </form>

2694 EOF

2695 if [ $((offset+limit)) -lt $total ]; then

2696 nextbtn=$(

2697 cat<<EOF

2698 <div class="right clear"><form action="$myname" method="$METHOD">

2699 `cgi_submit 次の${limit}件`

2700 $hiddens

2701 `cgi_hidden offset $((offset + limit))`</form></div>

2702 EOF

2703 )

2704 fi

2705 if [ $offset -gt 0 ]; then

2706 prevbtn=$(

2707 cat<<EOF

2708 <form action="$myname" method="$METHOD">

2709 `cgi_submit 前の${limit}件`

2710 $hiddens

2711 `cgi_hidden offset $((offset - limit))`</form>

2712 EOF

2713 )

2714 fi

2715 pnbtn="$nextbtn$prevbtn"

2716 echo $pnbtn

2717

2718 ## err ListEntry: `echo "$sql"\;`

2719 # sq $db here??? 2016-11-28

2720 query "$sql limit $limit ${offset:+offset $offset};" \

2721 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2722 # err name=$name owner=$ownerp lnk=$lnk

2723 # err newlnk=$lnk regmode=$regmode

2724 icondir=$dir/$id

2725 # Pick up only last icon

2726 htmlname=`echo $name|htmlescape`

2727 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2728 <p class=\"tag _$tag\">$tag</p>" \

2729 | _m4 $tagconv

2730 if [ -n "$NOSPEEDUP" ]; then

2731 files=`getvalbyid $tbl profimg $id $icondir`

2732 if [ -n "$files" ]; then

2733 icon=`echo "$files"|tail -1`

2734 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2735 else

2736 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2737 fi

2738 elif [ -n "$icon" -a -s "$icon" ]; then

2739 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2740 else

2741 cond="$nm=$qname"

2742 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2743 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2744 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2745 # err "img=[$img]"

2746 if [ -n "$img" ]; then

2747 echo "<a href=\"$hrb+$lnk\">"

2748 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2749 echo "</a>"

2750 else

2751 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2752 fi

2753 fi

2754 echo "<br>$htmlname${ownerp:+<br>($ownerp)}"

2755 echo "</div>"

2756 done

2757 echo "</div>" # End of List-entry div

2758 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2759 if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then

2760 peekgrpworlds "$kwd"

2761 fi

2762 )

2763 listmember() {

2764 listentry user "$@"

2765 }

2766 listgroup() {

2767 listentry group "$@"

2768 }

2769 hexteams() { # $1=gname, $2(optional)=user

2770 cond=${2:+" AND user='$2'"}

2771 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2772 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2773 }

2774 showgroup() { # $1=group-rowid

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

2776 grid=`getpar grid`

2777 grid=${grid%%[!0-9]*}

2778 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2779 else

2780 grid=$1

2781 fi

2782 grp=`getgroupbyid $grid`

2783 qgrp=`sqlquote "$grp"`

2784 htmlgrp=`echo "$grp"|htmlescape`

2785 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2786 if isgroup "$grp"; then

2787 tf=$tmpd/title.$$

2788 sf=$tmpd/search.$$

2789 bodyclass=`query "SELECT val FROM grp_s

2790 WHERE gname=$qgrp AND key='regmode';"`

2791 if ismember "$user" "$grp"; then

2792 ismember="ismember"

2793 bodyclass="$bodyclass${bodyclass:+ }ismember"

2794 else

2795 ismember="" # bodyclass="group"

2796 fi

2797 bodyclass="$bodyclass grouphome"

2798 echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf

2799 echo "グループ $htmlgrp" > $tf

2800

2801 showgroupsub $formdir/grp.def "$grid" | \

2802 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2803 -D_FORM_="syscmd(\`cat')" \

2804 -D_BODYCLASS_="$bodyclass" \

2805 -D_DUMPTABLE_="" \

2806 $htmlheader $sf $layout/form+dump.m4.html

2807 # $htmlheader $layout/form+dump.m4.html

2808 # $htmlheader is defined in grp()

2809 else # if $grp is removed at par2table

2810 listgroup

2811 fi

2812 }

2813 showgroupsub() {

2814 # $1=def-file $2=group-rowid

2815 # Using $ismember

2816 rowid=$2

2817 grp=`getgroupbyid $2`

2818 qgrp=`sqlquote "$grp"`

2819 td=`getcachedir grp/"$2"`

2820 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2821 if [ -z "$rowid" ]; then

2822 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2823 #grp=`sq $db "select gname from grp where rowid=$grp"`

2824 echo "showgroupsub: invalid argument($1 $2)" | html p

2825 return

2826 fi

2827 val=`getvalbyid grp profimg $rowid $tmpd`

2828 enticond="gname=$qgrp"

2829 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2830 if [ -n "$img" ]; then

2831 cat<<-EOF

2832 <p class="groupimg">

2833 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2834 EOF

2835 fi

2836 echo "<div class=\"noprofimg\">"

2837 viewtable $1 grp $rowid

2838 echo "</div>"

2839 if isgrpowner "$user" "$grp"; then

2840 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2841 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2842 iamowner=$rowid

2843 colmd=" mode"

2844 fi

2845 if [ -n "$ismember" ]; then

2846 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2847 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2848 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2849 cat<<-EOF

2850 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2851 href="?blog+$rowid">グループの新規話題作成</a>

2852 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2853 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2854 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2855 <div class="fold clear">

2856 `cgi_checkbox send yes id="send"`<label

2857 for="send">グループ全員にメッセージ送信</label>

2858 <div>

2859 `cgi_textarea message "" "cols=60"`

2860 `cgi_submit 送信`

2861 `cgi_reset リセット`

2862 </div>

2863 `cgi_hidden grp $rowid`

2864 </div></form>

2865 EOF

2866 fi

2867 # 加入ボタン + 加入者リスト

2868 if [ -n "$ismember" ]; then

2869 ismem='checked' state="(参加中)"

2870 else

2871 nomem='checked' state="(現在非加入)"

2872 fi

2873 # このグループでの加入アドレス

2874 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2875 and key='email';"`

2876 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2877 ## and key='email';"

2878 ##err email=$eml

2879 cat <<EOF

2880 <div class="fold clear">

2881 `cgi_checkbox reg yes id="reg"`<label

2882 for="reg">自身の加入状態を操作する</label>$state

2883 <div>

2884 EOF

2885 cgi_form grp <<EOF

2886 <p>このグループに</p>

2887 <table class="b">

2888 <tr><th>メンバーとして</th><td>

2889 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2890 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2891 <tr><th>参加する場合のメイルアドレス<br>

2892 <small>(メインのアドレスとは違うものにする場合に記入<br>

2893 同じでよい場合は空欄に)</small></th>

2894 <td>`cgi_text email $eml`</td></tr>

2895 </table>

2896 `cgi_hidden grp $rowid`

2897 EOF

2898 if [ x`getgroupattr "$grp" regmode` = x'moderated' -a -z "$ismem" ]; then

2899 echo "moderated (承認加入の)グループなので実際に参加できるのは

2900 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2901 fi

2902 echo '</div></div>'

2903 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2904 cat<<-EOF

2905 <div class="fold">

2906 <h2>話題一覧</h2>

2907 <form class="summary inline" action="$myname" title="$thelp">

2908 `cgi_hidden owner "$grp"`

2909 `cgi_hidden kwd "@week"`

2910 `cgi_hidden stage searchart`

2911 `cgi_submit "一週間のまとめ"`

2912 </form>

2913 <input type="checkbox" id="dtcheck" checked><label

2914 for="dtcheck">話題一覧表示</label>

2915 EOF

2916 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by 稼動状態, ctime desc"

2917 colstate="state:稼動状態:frozen=rowclass=凍結"

2918 frzbtn='<button class="toggle-frozen"></button>'

2919 DT_CHLD=article:blogid \

2920 DT_QOWNER="$qgrp" \

2921 DT_VIEW=replyblog dumptable html blog \

2922 "ctime title heading team notify:通知$colmd $colstate" "$cond" \

2923 | if [ -n "$iamowner" ]

2924 then

2925 sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"

2926 else

2927 cat

2928 fi

2929 ## DO not convert to frzbtn when not admin

2930 echo "</div> "

2931

2932 getgname="(select gname from grp where rowid=$rowid)"

2933 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2934 cm="?commission+$rowid"

2935 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2936 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2937 # team list

2938 hexteams=`hexteams "$grp"`

2939 if [ -n "$hexteams" ]; then

2940 echo "チーム一覧" | html h2

2941 echo '<div class="dumptable"><table class="b">'

2942 sq $db -html -header<<-EOF

2943 SELECT val TEAM,

2944 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2945 MEMBERS

2946 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2947 EOF

2948 echo '</table></div>'

2949 fi

2950 }

2951 grp_getbodyclass() {

2952 # Get css class name for document.

2953 # `moderated' for moderated groups

2954 # `ismember' for groups where user belongs

2955 # $1=GroupName (w/o quote)

2956 # $user=userNameCurrentlyLogin

2957 ## err grp_getbodyclass: 1="$1"

2958 qgrp=`sqlquote "$1"`

2959 query<<-EOF

2960 SELECT coalesce(

2961 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2962 'open')

2963 ||

2964 CASE WHEN '$user'

2965 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2966 THEN ' ismember'

2967 ELSE ''

2968 END;

2969 EOF

2970 }

2971 grpaction() { # $1=group-rowid

2972 err GRP_ACTION:IN

2973 grid=${1:-`getpar grp`}

2974 grp=`getgroupbyid "$grid"`

2975 htmlgrp=`echo "$grp" | htmlescape`

2976 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2977 if [ -z "$grp" ]; then

2978 echo "無効な指定です。" | html p; return

2979 fi

2980 if ! ismember $user "$grp"; then

2981 echo "加入者のみに許可された操作です。" | html p; return

2982 fi

2983 isowner=""

2984 isgrpowner "$user" "$grp" && isowner="yes"

2985 err 2=$2 3=$3

2986 case "$2" in

2987 get:teamcsv)

2988 teamcsv "$grid"

2989 return

2990 ;;

2991 esac

2992 echo "グループ $grp 個別選択操作" \

2993 | _m4 -D_TITLE_="syscmd(\`cat')" \

2994 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2995 $layout/html.m4.html

2996

2997 usel=`getpar usel`

2998 if [ -n "$usel" ]; then

2999 uids=$(echo `echo $usel`|tr ' ' ',')

3000 ## err grpaction-1: grp=$grp, `echo $sql`

3001 text=`getpar text|tr -d '\r'`

3002

3003 rm=`getpar rm` cfm=`getpar confirm`

3004 ## err rm=$rm cfm=$cfm

3005 if [ x"$rm" = x"yes" ]; then

3006 if [ "$isowner" ]; then

3007 if [ x"$rm$cfm" = x"yesyes" ]; then

3008 # Eliminate

3009 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

3010 for tbl in grp_mem grp_mem_s grp_mem_m; do

3011 sql="delete from $tbl $cond;"

3012 # echo "sql=$sql"

3013 query "$sql"

3014 err rmGRPuser "$sql"

3015 done

3016 num=`query "select count(*) from user where rowid in ($uids);"`

3017 #err num=$num

3018 if [ 0$num -gt 0 ]; then

3019 sql="select coalesce(b.val,a.name) from user a left join \

3020 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

3021 # err `echo "$sql"`

3022 html pre<<EOF

3023 以下の${num}名のグループ $grp 登録を解除しました。

3024 `query "$sql"`

3025 EOF

3026 fi

3027 else

3028 echo "確認のチェックがないのでやめておきます。" | html p

3029 return

3030 fi

3031 else # not Group Owner

3032 echo "グループ管理者でないのでメンバー操作はできません。" | html p

3033 return

3034 fi

3035 cat<<EOF

3036

3037 EOF

3038 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

3039 if [ -z "$text" ]; then # if msg is empty

3040 echo "なにかメッセージを..." | html p

3041 return 0

3042 fi

3043 gecos=`gecos $user`

3044 safegc=`echo "$gecos" | tr -d '<>@,'`

3045 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3046 fromad=`myemail4group "$grp"`

3047 ###mail_from="$safegc <$fromad>"

3048 mail_from="$safegc <$user>" # TEST: 2020/5/13

3049 test -n `getpar sender` &&

3050 export SENDER=$user # TEST: 2020/5/15

3051 replyto=$fromad

3052

3053 ## Start parse of attachment files

3054 if [ -n "`getpar email`" ]; then

3055 ar=`getpar supprcpt`

3056 if [ -n "$ar" ]; then

3057 for a in $ar; do

3058 if checkdomain "$a"; then

3059 supprcpt="$supprcpt $a"

3060 else

3061 err "SupprcptErr=[$a] by $user"

3062 removercpt="$removercpt $a"

3063 fi

3064 done

3065 fi

3066 subj=`getpar subject`

3067 afiles=""

3068 for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`

3069 do

3070 f=$tmpd/$fn

3071 if [ -s $f ]; then

3072 afiles=$afiles"${afiles:+ }$f"

3073 fi

3074 done

3075 else

3076 preface=$(cat <<-EOF

3077 $url

3078 のグループ「$grp」のメンバーである $gecos さんから、

3079 あなた宛のメッセージです。

3080 ----------------------------------------------------------

3081 EOF

3082 )

3083 fi

3084 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

3085 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

3086 subj="${subj:-$gecos さんからのメッセージ}"

3087 REPLYTO=$replyto

3088 MAIL_FROM=$mail_from

3089 export REPLYTO SMAIL_TO MAIL_FROM

3090 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

3091 for r in $rcpts; do

3092 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

3093 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

3094 else

3095 # Show sender and rcpts address for guest

3096 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

3097 fi

3098 if [ -n "$afiles" ];then

3099 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

3100 else

3101 smail "$r" "$subj"

3102 fi <<EOF

3103 ${preface:+$preface$nl}$text

3104 EOF

3105 done

3106 if [ $? = 0 ]; then

3107 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

3108 sql="select coalesce(b.val, a.name) from

3109 (select name from user where rowid in ($uids)) a

3110 left join user_s b on a.name=b.name and b.key='gecos';"

3111 html pre<<EOF

3112 `query "$sql"`

3113 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

3114 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

3115 EOF

3116 err SendDone: `echo $sql`

3117 fi

3118 elif [ x"$rm" = x"commission" ]; then

3119 grp_reg_adm $grid $usel

3120 elif [ x"$rm" = x"addteam" ]; then

3121 team=`getpar team|sed "s/'/''/g"` # for single quotation

3122 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

3123 if [ x"$team" != x"$newteam" ]; then

3124 echo "チーム名に使えない文字を除去しました" | html p

3125 team=$newteam

3126 fi

3127 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

3128 cat<<-EOF | html p

3129 有効なチーム名を入力してください。

3130 カンマだけ、「なし」という名前は使えません。

3131 EOF

3132 echo "有効なチーム名を入力してください。" | html p

3133 else

3134 grp_add_team $grid "$team" $usel

3135 fi

3136 elif [ x"$rm" = x"rmteam" ]; then

3137 if [ x"yes" = x"`getpar teamconfirm`" ]; then

3138 rmteam=`getpar rmteam|sed "s/'/''/g"`

3139 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

3140 gname='$grp' AND user='$user' AND key='team'\

3141 AND val='$rmteam';\"`" ]; then

3142 grp_rm_team $grid "$rmteam" $usel

3143 else

3144 echo "所属していないチームの除去操作はできません。"|html p

3145 fi

3146 else

3147 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

3148 fi

3149 fi

3150 fi

3151 # POST count summary

3152 from=`getpar from`; to=`getpar to`

3153 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

3154 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

3155 fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p>"

3156 # New entry

3157 sql="WITH mems AS (

3158 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

3159 ON gm.user=g.name

3160 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3161 ), target_article AS (

3162 SELECT id FROM article_s

3163 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

3164 ), posts AS (

3165 SELECT author, count(author) post

3166 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

3167 WHERE blogid IN (SELECT id FROM blog_s

3168 WHERE key='owner'

3169 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

3170 AND key='text'

3171 GROUP BY author

3172 ), teams AS (

3173 SELECT user, group_concat(val, ', ') team

3174 FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user

3175 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3176 AND key='team'

3177 GROUP BY user

3178 ), user_post AS (

3179 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

3180 FROM mems m LEFT JOIN posts

3181 ON m.name=posts.author

3182 GROUP by m.rowid

3183 ), user_view AS (

3184 SELECT user vuser,count(user) cnt

3185 FROM tblaccesses

3186 WHERE user IN (

3187 SELECT user FROM grp_mem

3188 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))

3189 AND tbl='blog'

3190 AND tblrowid IN (

3191 SELECT rowid FROM blog

3192 WHERE id IN (

3193 SELECT id FROM blog_s

3194 WHERE key='owner' AND val=(

3195 SELECT gname FROM grp WHERE rowid=$grid)))

3196 AND time BETWEEN '${from:-0000}' AND '${to:-9999}'

3197 GROUP BY user ORDER BY cnt

3198 )

3199 SELECT

3200 CASE

3201 WHEN (SELECT user FROM grp_adm

3202 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

3203 AND user=up.name) IS NOT NULL

3204 then 'k'

3205 ELSE ''

3206 END || rowid || ','

3207 || rtrim(substr(name, 1, instr(name, '@')), '@') || ','

3208 || gecos NAME,

3209 post POST,

3210 (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,

3211 team _TEAM_

3212 FROM user_post up LEFT JOIN teams t

3213 ON up.name=t.user

3214 ORDER BY up.name;"

3215 ## Want to count ACCESS with JOIN, but failed to produce user-unique rows

3216 ## err grpaction: "`echo \"$sql\"`"

3217 tf=$tmpd/title.$$

3218 echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf

3219 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

3220 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

3221 for=\"cmadmin\">管理者委任</label>

3222 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

3223 </p></div>"

3224 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

3225 title=\"Shortcut: g${nl}Remove from the Group\"

3226 for=\"conf\">GRP登録解除</label>

3227 <div>本当に消します! `cgi_checkbox confirm yes` 確認

3228 <p>この操作による通知は本人に行きません。

3229 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3230 </div>"

3231 submitBTN=`cgi_submit 送信`

3232 # Get team list to which current user belongs into $hexteams

3233 allhexteams=$(hexteams "$grp")

3234 if [ -n "$isowner" ]; then

3235 myhexteams="$allhexteams" # admin can remove all teams' attr

3236 else

3237 myhexteams=$(hexteams "$grp" "$user")

3238 fi

3239 if [ -n "$isowner" -a -n "$allhexteams" ]; then

3240 gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"

3241 fi

3242 if [ -n "$myhexteams" ]; then

3243 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

3244 title=\"Shortcut: s${nl}Strip a team tag from\"

3245 for=\"cmrmteam\">チーム属性除去</label>

3246 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

3247 を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN

3248 <p>この操作による通知は本人に行きません。

3249 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

3250 </div>

3251 "

3252 fi

3253 stf=$tmpd/selteam.$$

3254 cgi_select_h selteam "5445414d" $allhexteams > $stf

3255 b1='<label> <input type="checkbox" name="usel" value="'

3256 ba='<label class="admin"><input type="checkbox" name="usel" value="'

3257 br='<span id="reverse" title="Reverse Selection"></span>'

3258 #b2='"> <span>' b3='</span></label>'

3259 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

3260 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

3261 lnk='"> <span>\5</span></label> [<a href="?home+\3">HOME</a>]'

3262 cgi_form grpaction<<EOF \

3263 | sed -E \

3264 -e "s|^(<TR><TD)>(k?)([0-9]*),([^,]+),([^<]*)|\1 title=\"\4\">\2$b1\3$lnk|" \

3265 -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \

3266 -e "s|^(<TR><TH>)(NAME)|\1$br \2|" \

3267 | _m4 -D_TITLE_="spaste(\`$tf')" \

3268 -D_SUBTITLE_="チェック後操作ボタン" \

3269 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

3270 $layout/form+dump.m4.html \

3271 | _m4 -D_TEAM_="spaste(\`$stf')"

3272 <p>下でチェックした人を対象として:</p>

3273 <div class="foldtabs">

3274 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

3275 title="Shortcut: a${nl}Add a team tag to"

3276 for="cmteam">同じチーム属性を付与</label>

3277 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

3278 `cgi_datalist_h teams $allhexteams`$submitBTN

3279 </div>

3280 ${rmteammsg}

3281 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

3282 title="Shortcut: d${nl}DirectMail to"

3283 for="sendmsg" title="Direct Message">DM送信</label>

3284 <div>

3285 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

3286 title="Using email format">email書式を使う</label>

3287 <div class="folded">

3288 <table>

3289 <tr><td>From: </td><td>$user</td></tr>

3290 <tr><td>このFrom:で送る</td>

3291 <td>`cgi_checkbox sender yes 'checked'`<small></small>

3292 </td></tr>

3293 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

3294 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

3295 <tr><td>ファイル添付: </td>

3296 <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

3297 </table>

3298 <p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>

3299 </div>

3300 <div>本文:`cgi_textarea text "" cols=72`

3301 </div>

3302 </div>

3303 ${isowner:+$cmmsg$excmsg}

3304 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

3305 </div>

3306 <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote

3307 <table class="td2r td3r thl">

3308 `sq $db -header -html "$sql"`

3309 </table>

3310 `cgi_hidden grp $grid`

3311 `cgi_hidden myuid $myuid id="myuid"`

3312 EOF

3313 }

3314 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3315 # Create TEMPORARY VIEW

3316 query<<EOF

3317 CREATE TEMPORARY VIEW writeusers AS

3318 SELECT DISTINCT author FROM article

3319 WHERE id in (

3320 select id from article where blogid=(select id from blog where rowid=$1)

3321 );

3322 CREATE TEMPORARY VIEW movablegroups AS

3323 SELECT g.rowid growid , g.gname

3324 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3325 ON grp.gname=gm.gname -- そのユーザが属している

3326 AND user='$user') g -- グループに絞る

3327 WHERE (SELECT author FROM writeusers

3328 EXCEPT

3329 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3330 IS NULL;

3331 $2

3332 EOF

3333 }

3334 sql4readableblogs() {

3335 # Create view of blogs that can be readable by $user

3336 # Blog is readable when:

3337 # 1: blog owner is an user

3338 # 2: else, 2.1: owner-group where the $user belongs

3339 # 2.2: else, owner-group is not moderated

3340 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3341

3342 # $hidden_mode is defined in global section at head

3343

3344 cat<<EOF ## | tee tmp/sql.out

3345 CREATE TEMPORARY VIEW readableblogs AS

3346 SELECT blog.rowid rid, id, author

3347 FROM blog

3348 NATURAL JOIN

3349 (SELECT id,

3350 max(CASE key WHEN 'owner' THEN val END) owner,

3351 max(CASE key WHEN 'mode' THEN val END) mode

3352 FROM blog_s GROUP by id) bs

3353 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3354 THEN -- blog owner is an user, READABLE

3355 NOT mode IN $hidden_mode

3356 WHEN (SELECT val FROM grp_s

3357 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3358 AND

3359 (SELECT user FROM grp_mem

3360 WHERE gname=bs.owner AND user='$user') IS NULL

3361 THEN 0

3362 WHEN mode IN $hidden_mode

3363 THEN 0 -- "quiz" mode blog cannot be searched

3364 ELSE 1

3365 END;

3366 EOF

3367 }

3368 mvteamform() {

3369 owner=$1

3370 hexteams=$(hexteams "$owner" "$user")

3371 test -z "$hexteams" && return

3372 none="`echo なし|hexize`"

3373 cat<<-EOF

3374 <!-- <div class="fold">

3375 `cgi_checkbox mv2team send id="mv2team"`<label

3376 for="mv2team">この話題を以下のチームのものにする</label>

3377 <div> -->

3378 <p>この話題をチーム所有にする:

3379 チーム: `cgi_select_h mv2team $none $hexteams`</p>

3380 </form></div></div>

3381 EOF

3382 }

3383 editheading() { # $1=rowid-of-heading

3384 rowid=${1%%[!A-Z0-9a-z_]*}

3385 if [ -z "$rowid" ]; then

3386 echo "話題番号が未指定です。" | html p

3387 return

3388 fi

3389 owner=`getvalbyid blog owner $rowid`

3390 title=`getvalbyid blog title $rowid`

3391 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3392 | _m4 -D_TITLE_="修正" \

3393 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3394 -D_BLOGS_="" -D_DUMPTABLE_="" \

3395 -D_FORM_="syscmd(\`cat')" \

3396 $layout/html.m4.html $layout/form+dump.m4.html

3397 # Move to group

3398 if isuser "$owner"; then

3399 crview4article $rowid

3400 n=`query "SELECT count(*) FROM writeusers;"`

3401 ## err N=$n

3402 if [ $((n)) -gt 0 ]; then

3403 ## err ROWID=$rowid

3404 sql="SELECT growid || ':' || gname FROM movablegroups;"

3405 cat<<-EOF

3406 <div class="fold">

3407 `cgi_checkbox mv send id="mv"`<label

3408 for="mv">この話題をグループ所有に移動する</label>

3409 <div>

3410 <form action="?mvart" method="POST" enctype="multipart/form-data">

3411 移動先グループ:

3412 <select name="mv2grp">

3413 EOF

3414 query ".mode html"

3415 query<<-EOF |

3416 $sql

3417 .mode list

3418 EOF

3419 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3420 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3421 cat<<-EOF

3422 </select>

3423 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3424 そのグループに加入しているものに限られます)</p>

3425 <p>`cgi_checkbox cfm yes`<label>確認

3426 (この操作は元に戻すことができません)</label></p>

3427 `cgi_hidden blogrowid $rowid`

3428 `cgi_submit 移動`

3429 `cgi_reset Reset`

3430 </form>

3431 </div>

3432 </div>

3433 EOF

3434 fi

3435 # end of isuser "$owner"

3436 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3437 [ -n "$hexteams" ];}; then

3438 none="`echo なし|hexize`"

3439 cat<<-EOF

3440 <div class="fold">

3441 `cgi_checkbox mv2team send id="mv2team"`<label

3442 for="mv2team">この話題を以下のチームのものにする</label>

3443 <div><p>現在の所属チーム設定:

3444 `query "SELECT

3445 coalesce((SELECT val FROM blog_s

3446 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3447 AND key='team'),

3448 ':なし');"`</p>

3449 <form action="?mvart" method="POST" enctype="multipart/form-data">

3450 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3451 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3452 `cgi_hidden blogrowid $rowid`<br>

3453 `cgi_submit 移動`

3454 `cgi_reset Reset`

3455 </form></div></div>

3456 EOF

3457 fi

3458 }

3459 mvart() { # move diary to some group or team

3460 # or move blog of group to team which belong to the group

3461 blogrowid=`getpar blogrowid`

3462 cfm=`getpar cfm`

3463 ##### echo move blog:$blogrowid to $mv2grp | html p

3464 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3465 . ./s4-blog.sh

3466 if [ -z "$blogrowid" ]; then

3467 echo "無効な指定です(mvart)。" | html p

3468 return

3469 elif [ x"$cfm" != x"yes" ]; then

3470 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3471 elif { mv2grp=`getpar mv2grp`

3472 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3473 [ -n "$mv2grp" ]; }; then

3474 crview4article $blogrowid

3475 ########## TRANSACTION BEGIN

3476 query "BEGIN;"

3477 n=`query "SELECT count(*) FROM writeusers;"`

3478 ## err Nwriteuser=$n

3479 if [ $((n)) -gt 0 ]; then

3480 query<<-EOF

3481 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3482 WHERE key='owner'

3483 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3484 AND $mv2grp IN (SELECT growid FROM movablegroups);

3485 EOF

3486 fi

3487 query "END;"

3488 ########## TRANSACTION END

3489 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3490 [ -n "$mv2team" ];}; then

3491 # blog owner can move it to ANY team

3492 case "$mv2team" in

3493 'なし')

3494 cat<<-EOF

3495 DELETE FROM blog_s

3496 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3497 AND key='team';

3498 EOF

3499 ;;

3500 "") ;;

3501 *)cat<<-EOF

3502 BEGIN;

3503 REPLACE INTO blog_s(id, key, val)

3504 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3505 'team', '$mv2team');

3506 REPLACE INTO blog_s(id, key, val)

3507 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3508 'notify', 'all'); -- Change notify to all

3509 END;

3510 EOF

3511 esac | query

3512 fi

3513 blog_reply $blogrowid

3514 echo yes | html p

3515 }

3516 editart() { # $1=article-rowid $2=blogrowid

3517 rowid=${1%%[!A-Z0-9a-z_]*}

3518 blogrowid=${2%%[!A-Z0-9a-z_]*}

3519 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3520 echo "表示する記事番号が未指定です。" | html p

3521 return

3522 fi

3523 owner=`getvalbyid blog owner $blogrowid`

3524 title=`getvalbyid blog title $blogrowid`

3525 author=`getvalbyid article author $rowid`

3526 math=`getvalbyid blog mathjax $blogrowid`

3527 ## err EDITart: owner=$owner, author=$author

3528 if isgrpowner "$user" "$owner"; then

3529 : EDIT OK

3530 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3531 echo "本人か所有者しか編集できません." | html p

3532 return

3533 fi

3534 aid=`query "select id from article where rowid=$rowid;"`

3535 tmpout=$tmpd/editart.$$.out

3536 GF_ACTION="?replyblog+$blogrowid#$aid" \

3537 edittable $formdir/article.def article $rowid \

3538 > $tmpout

3539 printf '%s' "${math:+$mathjax}" >> $tmpout

3540 # Cannot use pipelining to m4 with genform() because of stdin stack

3541 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3542 -D_FORM_="syscmd(cat $tmpout)" \

3543 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3544 -D_BLOGS_= -D_DUMPTABLE_= \

3545 $layout/html.m4.html $layout/form+dump.m4.html

3546 }

3547 send2mem() {

3548 rowid=`getpar grp`

3549 rowid=${rowid%%[!0-9]*} # Cleaning

3550 if [ -z "$rowid" ]; then

3551 echo "グループが未指定です。" | html p

3552 return

3553 fi

3554 message=`getpar message`

3555 if [ -z "$message" ]; then

3556 echo "文章を入れてください。" | html p

3557 return

3558 fi

3559 grp=`getgroupbyid $rowid`

3560 members=`collectemail "$grp"`

3561 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3562 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3563 mailfrom="`gecos "$user"` <$mailfrom>"

3564 sj="グループ「$grp」宛メッセージ(from `gecos $user`)"

3565 msg=$(cat<<-EOF

3566 $urlbase?grp+$rowid

3567 グループ $grp に所属する

3568 `gecos $user` さんよりメッセージ:

3569

3570 $message

3571 EOF

3572 )

3573 # smail rcpt subj (file)

3574 for m in $members; do

3575 echo "$msg" |

3576 MAIL_FROM=$mailfrom \

3577 SENDER=$noreply \

3578 REPLYTO=$mailfrom \

3579 smail "$m" "$sj"

3580 done

3581 cat<<EOF

3582 <p>以下のユーザに送信しました。</p>

3583 <pre>

3584 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3585 </pre>

3586 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3587 EOF

3588 }

3589 joingrpadmit() {

3590 # $1=yes/no $2=session-key

3591 if [ -z "$2" ]; then

3592 echo "bye bye" | html p; return

3593 fi

3594 t_usr=`session=$2 getpar adduser`

3595 t_grp=`session=$2 getpar group`

3596 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3597 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3598 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3599 echo "無効な加入依頼です。" | html p

3600 echo "有効期限が切れたか、

3601 他の管理者がいる場合は処理済みの可能性があります。" | html p

3602 return

3603 fi

3604 if ! isgrpowner "$user" "$t_grp"; then

3605 echo "グループ管理者のみの機能です。" | html p; return

3606 fi

3607 case $1 in

3608 yes) joingrp "$t_grp" "$t_usr" yes ;;

3609 no) joingrp "$t_grp" "$t_usr" no ;;

3610 *)

3611 echo "無効な指定です($1)。" | html p

3612 return ;;

3613 esac

3614 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3615 rcpts="`getgroupadminmails "$t_grp"` $user"

3616 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3617 body="に

3618 $t_usr

3619 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3620 しました。"

3621 echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"

3622 query "delete from session where id='$2';"

3623 echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p

3624 }

3625

3626 joingrprequest() {

3627 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3628 jss="joingrp-`date +%s`-`genrandom 12`"

3629 addsession $jss +${memoplimitdays}days

3630 grpadmins=`getgroupadmins "$1"`

3631 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3632 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3633 smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF

3634 $url

3635 $user さんから

3636 グループ $1

3637 に加入依頼がありました。

3638

3639 承認する:

3640 $urlbase?joingrpadmit+yes+$jss

3641

3642 白紙に戻す:

3643 $urlbase?joingrpadmit+no+$jss

3644 EOF

3645 echo "管理者に加入依頼を出しました。

3646 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3647 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3648 }

3649 joingrp() {

3650 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3651 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3652 if isgrpowner "$user" "$1"; then

3653 isowner="yes"

3654 elif [ -n "$5" ]; then

3655 isowner="yes"

3656 else

3657 isowner=""

3658 fi

3659 ## err jg:isgrpowner: isowner="$isowner"

3660 if [ -n "$isowner" ]; then

3661 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3662 elif [ x"$2" != x"$user" ]; then # if user is not login user

3663 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3664 return

3665 elif [ x"$3" = x"no" ]; then

3666 : # Do not pursue those who leave

3667 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3668 : # Member can change own email address for the joining moderated group

3669 else # adding user is $user itself

3670 case `getgroupattr "$1" regmode` in

3671 moderated)

3672 joingrprequest "$@" # Request only

3673 return

3674 ;;

3675 *)

3676 ;;

3677 esac

3678 fi

3679 qgname=`sqlquote "$1"`

3680 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3681 cond="where gname=$qgname and user='$2'"

3682 if [ x"$3" = x"yes" ]; then

3683 query "replace into grp_mem values($qgname, '$2');"

3684 # Notify joingrp to admin

3685 action="に加入しました。"

3686 if [ -n "$4" ]; then

3687 if msg=`emaildomaincheck "$4"`; then

3688 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3689 'string', '$4', NULL);"

3690 else

3691 echo $msg

3692 fi

3693 else

3694 query "delete from grp_mem_s $cond and key='email';"

3695 fi

3696 if [ -n "$5" ]; then # as ADMIN

3697 # Coming here means newly created group

3698 sql="select case\

3699 when (select count(*) from grp_mem where gname=$qgname)=1\

3700 then (select user from grp_mem\

3701 where gname=$qgname and user='$user')\

3702 else '' end; "

3703 err NewGrpChk: $sql

3704 if [ -n "`query \"$sql\"`" ]; then

3705 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3706 query "replace into grp_adm values($qgname, '$user');"

3707 fi

3708 fi

3709 else

3710 query "begin;

3711 delete from grp_mem $cond;

3712 delete from grp_mem_s $cond;

3713 delete from grp_mem_m $cond;

3714 delete from grp_adm $cond;

3715 delete from grp_adm_s $cond;

3716 delete from grp_adm_m $cond;

3717 end;"

3718 action="から脱退しました。"

3719 fi

3720 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3721 $url?grp+$grid

3722 $user (`gecos $user`)さんが

3723 グループ $1

3724 $action

3725 EOF

3726 }

3727 grp_add_team() (

3728 # $1=grp-rowid $2=team $3...=user-rowid(s)

3729 grp=`getgroupbyid $1`

3730 team=$2; shift; shift

3731 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3732 { echo "BEGIN;"

3733 for user; do

3734 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3735 '$grp',\

3736 (SELECT name FROM user WHERE rowid=$user),\

3737 'team', 'string', '$team');"

3738 done

3739 echo "END;"

3740 } | query

3741 )

3742 grp_rm_team() (

3743 # $1=grp-rowid $2=team $3...=user-rowid(s)

3744 grid=$1

3745 qgrp=$(sqlquote "`getgroupbyid $grid`")

3746 team=$2; shift; shift

3747 [ -z "$grid" -o -z "$team" ] && return

3748 { echo "BEGIN;"

3749 for user; do

3750 echo "DELETE FROM grp_mem_m\

3751 WHERE gname=$qgrp \

3752 AND user=(SELECT name FROM user WHERE rowid=$user)\

3753 AND key='team' AND val='$team';"

3754 done

3755 cat<<-EOF

3756 DELETE FROM blog_s

3757 WHERE rowid=(

3758 SELECT rowid

3759 FROM blog_s a

3760 WHERE key='team'

3761 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3762 AND NOT EXISTS (SELECT * FROM grp_mem_m

3763 WHERE key='team' AND val=a.val -- a.val=team

3764 AND gname = (SELECT val FROM blog_s b

3765 WHERE a.id=b.id AND key='owner')

3766 ));

3767 EOF

3768

3769 echo "END;"

3770 } | query

3771 )

3772 grp_reg_adm() {

3773 # $1=grp-rowid $2...=user-rowid

3774 grid=$1

3775 grp=`getgroupbyid "$1"`

3776 if [ -z "$grp" ]; then

3777 echo "無効なグループIDです" | html p; return

3778 fi

3779 if ! isgrpowner "$user" "$grp"; then

3780 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3781 fi

3782 shift

3783 for urid; do

3784 newadm=`query "select name from user where rowid=$urid;"`

3785 if [ -z "$newadm" ]; then

3786 echo "指定ユーザIDがおかしいようです。" | html p; return

3787 fi

3788 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3789 err ismember $newadm $grp

3790 if ismember $newadm "$grp"; then

3791 # OK, go ahead

3792 getgname="(select gname from grp where rowid=$grid)"

3793 query "replace into grp_adm values($getgname, '$newadm');"

3794 # confirm insertion

3795 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3796 if [ -n "`query \"$sql;\"`" ]; then

3797 echo "追加完了: $newadm" | html p

3798 else

3799 echo "追加失敗($1 $urid)" | html p

3800 fi

3801 fi

3802 showgroup $grid

3803 done

3804 }

3805 dt_colhack() {

3806 # FROM: <TD>xxx:::yyy</TD>

3807 # TO: <TD class="xxx">yyy</TD>

3808 sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'

3809 }

3810 dt_rowhack() {

3811 # From: <TR>

3812 # ....

3813 # <TD>rowclass=foo</TD>

3814 # </TR>

3815 # To: <TR class="foo">....<TD>foo</TD></TR>

3816 sed -e '

3817 /^<TR>/ {

3818 :loop

3819 s/\n//

3820 N

3821 /<\/TR>/ {

3822 s/\n//

3823 s,^<TR>$.*$<TD$[^>]*$>rowclass=$.*$$</TD></TR>$,<TR class="\3">\1<TD\2>\3\4,

3824 n

3825 }

3826 $q

3827 b loop

3828 }'

3829 }

3830 dumptable() {

3831 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3832 # textのフィールドだけ全てダンプにしたほうがいいか

3833 # $DT_VIEW sets link

3834 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3835 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3836 VIEW=${DT_VIEW-replyblog}

3837 if [ -n "$VIEW" ]; then

3838 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3839 fi

3840 sqlfile=$tmpd/dump.sql

3841 : > $sqlfile # ensure to be empty

3842 printf '.mode html\n.header 1\n' > $sqlfile

3843 # $DT_CHLD=ChildTable:BindColumn

3844 if [ -n "$DT_CHLD" ]; then

3845 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3846 cat<<-EOF >> $sqlfile

3847 -- presql

3848 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3849 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3850 EOF

3851 # Speed up counting of new articles

3852 cat<<-EOF >> $sqlfile

3853 -- presql2

3854 DROP TABLE IF EXISTS _counts;

3855 CREATE TEMPORARY TABLE _counts AS

3856 SELECT $_i, count($_i) cnt

3857 FROM $_t GROUP BY $_i;

3858 /* Prepare NEW count table */

3859 CREATE TEMPORARY TABLE _target AS

3860 SELECT b.rowid trowid, b.id

3861 FROM "$2" b JOIN "$2_s" s

3862 ON b.id=s.id AND s.key='owner'

3863 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3864

3865 DROP TABLE IF EXISTS _children;

3866 CREATE TEMPORARY TABLE _children AS

3867 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3868 FROM (SELECT t.trowid, t.id $_i, a.id

3869 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3870 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3871

3872 DROP TABLE IF EXISTS _news;

3873 DROP VIEW IF EXISTS _news;

3874

3875 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3876 -- INSERT INTO _news

3877 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3878 CREATE TEMPORARY VIEW _news AS

3879 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3880 FROM (SELECT DISTINCT id FROM _target)

3881 a LEFT JOIN

3882 (SELECT $_i, count(ctime) newcnt

3883 FROM _children x

3884 WHERE ctime > coalesce((SELECT time from myacclog

3885 WHERE tblrowid=x.trowid),

3886 '1970-01-01')

3887 GROUP BY $_i) b

3888 ON a.id=b.$_i;

3889 EOF

3890 # REMOVE next line until 2019/5/1

3891 cntall="(select count($_i) from $_t where $_i=a.id)"

3892 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3893 # REMOVE next assignment until 2019/5/1

3894 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3895 and id in (select id from $_t where $_i=a.id) \

3896 and val > coalesce((select time from myacclog where \

3897 tblrowid=a.rowid),\

3898 '1970-01-01'))"

3899 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3900 cnt="$cntnew as '新着', $cntall as '総数',"

3901 dt_class=" td2r td3r dumpblogs"

3902 fi

3903 # Construct join expression

3904 eav="" scols=""

3905 pk=`gettblpkey $2`

3906 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3907 substr=${substr:-%s}

3908 for col in ${3:-`gettbl_s_cols $2`}; do

3909 valvar=val

3910 fromtbl=b

3911 if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then

3912 # If $col belongs to master table

3913 fromtbl=a; col=${col#a.}

3914 fi

3915 case $col in

3916 gecos) scols="$scols${scols:+, }${col#}"

3917 continue ;; # built-in column name

3918 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3919 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3920 case "$as" in

3921 *:*=*) cnd=${as#*:}

3922 h=${cnd%%=*} v=${cnd#*=}

3923 h=`sqlquotestr "$h"`

3924 v=`sqlquotestr "$v"`

3925 if [ x"$fromtbl" = x"b" ]; then

3926 valvar="CASE val WHEN $h THEN $v END"

3927 else

3928 valvar="$h"

3929 fi

3930 as=${as%%:*} ;;

3931 esac

3932 ;;

3933 *) as=${col} ;;

3934 esac

3935 ss=`printf "$substr" "$valvar"`

3936 if [ x"$fromtbl" = x"b" ]; then

3937 eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"

3938 else

3939 eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"

3940 fi

3941 scols="$scols${scols:+, }${fromtbl}.$as"

3942 done

3943 #case author when '$user' then a.rowid else '---' end as ID,

3944 if [ -n "$DT_SQL" ]; then

3945 echo "$DT_SQL"

3946 else

3947 cat<<-EOF

3948 SELECT a.rowid as LINK, $cnt $scols

3949 FROM $2 a LEFT JOIN

3950 (SELECT $pk,$eav,

3951 max(CASE key

3952 WHEN 'owner'

3953 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3954 as gecos

3955 FROM ${2}_s c GROUP BY $pk)

3956 b ON a.$pk=b.$pk $4;

3957 EOF

3958 fi >> $sqlfile

3959 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3960 sqlog<<-EOF

3961 *** SQL-file: $sqlfile ***

3962 `cat $sqlfile`

3963 EOF

3964 printf '.mode list\n.header 0\n' >> $sqlfile

3965 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack

3966 <div>

3967 <div class="dumptable">

3968 <table class="b$dt_class">

3969 `query ".read $sqlfile"`

3970 </table>

3971 </div>

3972 </div>

3973 EOF

3974 }

3975

3976 clean-orphaned() {

3977 # This shoud be done by foreign_key rules, but some db lack them

3978 query<<-EOF

3979 -- Find blogs that have no parent

3980 WITH orphanedblog AS (

3981 SELECT blog.id,val FROM blog JOIN blog_s bs

3982 ON blog.id=bs.id AND key='owner'

3983 WHERE val NOT IN (SELECT gname FROM grp)

3984 AND val NOT IN (SELECT name FROM user)

3985 ) -- Remove them

3986 DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

3987

3988 -- Find articles that have no parent blog

3989 WITH orphanedarticle AS (

3990 SELECT id FROM article

3991 WHERE blogid NOT IN (SELECT id FROM blog)

3992 ) -- Remove them

3993 DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);

3994 EOF

3995 }

3996

3997 clearcachedir() (

3998 td=`getcachedir "$1"`

3999 err td=$td: ls- `ls $td`

4000 if [ -w "$td/image.1" ]; then

4001 err Removing td=$td

4002 rm -fr $td/ # Clear icon-image cache!

4003 fi

4004 )

4005

4006 par2table() (

4007 # copy current parameters of par into destination table

4008 # $1=definition-file

4009 # Using $user and $session

4010 # Return value:

4011 # 0: Stored successfully

4012 # 1: Insufficient fillings

4013 # 2: No permission to modify the record

4014 # 3: Invalid rowid

4015 # 4: SUCCESS to delete

4016 # 5: Stop deletion for lack of confirm check

4017 # 6: Password length too short

4018 # 7: Password mismatch

4019 # 8: Old password incorrect

4020 # 9: Duplicated post

4021 rowid=`getpar rowid`

4022 if [ ! -e $1 ]; then

4023 echo "テーブル定義ファイルが見付かりません" | html p

4024 exit 1

4025 fi

4026 tbl=${1%.def}

4027 tbl=${tbl##*/}

4028 if [ -n "$rowid" ]; then # Modify existing entry

4029 if [ x"$tbl" = x"user" ]; then

4030 rowowner=`query "select name from $tbl where rowid=$rowid;"`

4031 elif [ x"$tbl" = x"grp" ]; then

4032 sql="select gname from $tbl where rowid=$rowid;"

4033 ##err p2t:grp:q $sql

4034 isgrpowner "$user" "`query $sql`" && rowowner=$user

4035 elif [ x"$tbl" = x"blog" ]; then

4036 # Check if owner in blog_s

4037 blogowner=`getvalbyid blog owner "$rowid"`

4038 if isgrpowner "$user" "$blogowner"; then

4039 rowowner=$user

4040 origauthor=`getvalbyid blog author $rowid`

4041 if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];

4042 then # Keep original author

4043 setpar author string "$origauthor" # if differs from $user

4044 fi # 2021-11-06 suggd.by Ruri

4045 else

4046 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4047 fi

4048 else

4049 # 2016-12-05 There's no owner column in $tbl (need confirmation)

4050 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

4051 fi

4052 ### err rowowner=$rowowner

4053 if [ x"$user" != x"$rowowner" ]; then

4054 echo "他人のレコードはいじれないの" | html p

4055 return 2

4056 elif [ -z "$rowowner" ]; then

4057 echo "指定したレコードはないみたい" | html p

4058 return 3

4059 fi

4060 rm=`getpar rm` cfm=`getpar confirm`

4061 # Editing existent entry

4062 if [ x"$rm" = x"yes" ]; then

4063 if [ x"$rm$cfm" = x"yesyes" ]; then

4064 query "delete from $tbl where rowid=$rowid;"

4065 clearcachedir "$tbl/$rowid"

4066 if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then

4067 clean-orphaned

4068 fi

4069 return 4

4070 else

4071 echo "消去確認のチェックがないので消さなかったの..." | html p

4072 return 5

4073 fi

4074 fi

4075 fi

4076

4077 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

4079 # Update of existing record

4080 for col in `gettblcols $tbl`; do

4081 val=`getparquote $col`

4082 [ -z "$val" ] && continue

4083 ## err query "update $tbl set $col=$val where rowid=$rowid"

4084 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

4085 sql="update $tbl set $col=$val where rowid=$rowid;"

4086 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

4087 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

4088 ## User name cannot be changed with interface provided with this

4089 ## script. But we offer the trigger to change owner user

4090 ## of blog_s table.

4091 #err "select quote($col) from $tbl where rowid=$rowid;"

4092 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

4093 cat<<-EOF | query

4094 -- Here we cannot use BEGIN-COMMIT because groupupdate()

4095 -- should use EXCLUSIVE transaction outside of this.

4096 SAVEPOINT par2table;

4097 $sql

4098 update blog_s set val=$val

4099 where key='owner' and val=$old;

4100 RELEASE SAVEPOINT par2table;

4101 EOF

4102 ## XX: DIRTY Hack Ends here

4103 ## We should keep blog's owner as a single column which has

4104 ## foreign key constraint with primary key of grp/user.

4105 else

4106 query "$sql"

4107 fi

4108 done

4109 # Then, set up $pval for further insertion of tbl_s and tbl_m

4110 for col in `gettblpkey $tbl`; do

4111 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

4112 pval="$pval${pval:+, }\"$val\""

4113 done

4114 else

4115 # New entry

4116 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

4117 # Avoid empty repost of article.

4118 if [ x"$tbl" = x"article" ]; then

4119 # If rowid is empty and ID exists in article-table, that is REPOST!

4120 aid=`getpar id`

4121 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

4122 if [ -n "$xaid" ]; then

4123 # REPOST of article

4124 html p <<-EOF

4125 書き込み直後のリロードなので上書きを回避します。

4126 最新記事は末尾の「再読み込み」ボタンから見てください。

4127 EOF

4128 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

4129 return 9 # STOP Duplicated posting

4130 fi

4131 fi

4132 # Generate values() for primary keys

4133 for col in `gettblpkey $tbl`; do

4134 # Genuine primary keys for _m and _s

4135 val=`getvalquote $tbl $col`

4136 [ -z "$val" ] && continue

4137 pval="$pval${pval:+, }$val"

4138 done

4139 ##err pval=$pval

4140 for col in `gettblfkey $tbl`; do

4141 # args for values() to insertion into master table

4142 val=`getvalquote $tbl $col`

4143 [ -z "$val" ] && continue

4144 formaster=$formaster"${formaster:+, }$val"

4145 done

4146 formaster="$pval${formaster:+, }$formaster"

4147 ## err formaster=$formaster

4148 if [ -z "$formaster" ]; then

4149 echo "項目を全て埋めてください" | html pre

4150 return 1

4151 fi

4152 ## err "replace into $tbl values($formaster);"

4153 query "replace into $tbl values($formaster);"

4154 ## Insertion to master table, done

4155 fi

4156

4157 transaction=$tmpd/sqlfile.sql; touch $transaction

4158 for kt in s m; do

4159 tb2=${tbl}_$kt

4160 for col in `gettbl_${kt}_cols $tbl`; do

4161 ptype=`getpartype $col "limit 1"`

4162

4163 # First, check update of existing entries in _m

4164 if [ $kt = m ]; then

4165 # sessID|address.1.22|string|Somewhere-x.y.z

4166 sql=""

4167 ##err dots from query "select var from par where var like '$col.%';"

4168 for v in `query "select var from par where var like '$col.%' AND sessid='$session';"`; do

4169 # v=address.1.22

4170 st_rowid=${v##*.}

4171 origcol=${v%%.*} # original column derived from

4172 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

4173 ##case `getpartype $v` in

4174 ## err CASE `gettbl_coltype $tbl/$origcol` in

4175 ## err edit flag = `getpar action.$v`

4176 case `getpar action.$v` in

4177 rm)

4178 if [ x`getpar confirm.$v` = x"yes" ]; then

4179 newsql="delete from $tb2"

4180 else

4181 echo "削除確認未チェック" | html p

4182 fi ;;

4183 edit)

4184 case `gettbl_coltype $tbl/$origcol` in

4185 image|document|binary)

4186 file=$tmpd/`getparfilename $v`

4187 if [ ! -s "$file" ]; then # Maybe stupid REPOST

4188 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

4189 continue

4190 fi

4191 ## err type=file=$file

4192 [ -z "$file" ] && continue

4193 bn=`sqlquotestr "${file##*/}"`

4194 bin="X'"$(hexize "$file")"'"

4195 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

4196 type=\"file:$ct\"

4197 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

4198 cachedir=`getcachedir "$tbl/$rowid"`

4199 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

4200 rm -rf $cachedir

4201 ;;

4202 *)

4203 newsql="update $tb2 set val=(select val from par where var \

4204 like '$col.%.$st_rowid' AND sessid='$session')"

4205 ;;

4206 esac

4207 ;;

4208 mv)

4209 # regularize filename and strip directory part

4210 newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`

4211 newname=`basename $newname`

4212 oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`

4213 oldext=`expr "$oldname" : '.*\.$.*$'`

4214 newext=`expr "$newname" : '.*\.$.*$'`

4215 err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"

4216 if [ -n "$newname" -a x"$oldext" = x"$newext" ];

4217 then

4218 newsql="UPDATE $tb2 SET val='$newname'"

4219 else

4220 html p<<-EOF

4221 $newname は取り扱えないファイル名です。

4222 空白を含まない名前にして下さい。

4223 拡張子の変更もできません。

4224 EOF

4225 continue

4226 fi

4227 ;;

4228 *) # maybe "keep", do not modify value

4229 continue

4230 ;;

4231 esac

4232 # err newsql=$newsql

4233 sql=$sql$nl"$newsql where rowid=$st_rowid;"

4234 clearcachedir "$tbl/$rowid"

4235 done

4236

4237 if [ x"$bin" = x"NULL" ]; then

4238 ## err repl:normal sql=`echo $sql`

4239 if [ -n "$transaction" ]; then

4240 cat<<-EOF >> $transaction

4241 $sql

4242 DELETE FROM $tb2 WHERE type='string' AND val='';

4243 EOF

4244 else

4245 query "$sql

4246 delete from $tb2 where type='string' and val='';"

4247 ## err repl:normal done

4248 fi

4249 else

4250 # Binary update line is TOO LONG to pipelining

4251 sqlfile="$tmpd/sqlf.$$"

4252 if [ -n "$transaction" ]; then

4253 printf '%s' "$sql" >> $transaction

4254 else

4255 printf '%s' "$sql" > $sqlfile

4256 query ".read $sqlfile"

4257 fi

4258 fi

4259 # Rest of kt==m: set multiple mode

4260 nr=`getparcount $col`

4261 else

4262 nr=1 # for kt==s, number of records is 1

4263 fi

4264

4265 i=0

4266 while [ $i -lt $nr ]; do

4267 limit="limit 1 offset $i"

4268 i=$((i+1)) # increase beforehand against continue

4269 val=`getvalquote $tbl $col "$limit"`

4270 ##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

4271 [ -z "$val" -o x"$val" = x'""' ] && continue

4272 ## err $col=$val

4273 bin=NULL

4274 ## err partype$col=`getpartype $col "$limit"`

4275 ptype=`getpartype $col "$limit"` # partype should be obtained each time

4276 case $ptype in

4277 file) file=$tmpd/`getparfilename $col "$limit"`

4278 ## err parfile-$col=$file

4279 [ -z "$file" ] && continue

4280 bin="X'"$(hexize "$file")"'"

4281 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

4282 type=\"file:$ct\" ;;

4283 "*"*) continue ;; # foreign table

4284 *) type=\"string\" ;;

4285 esac

4286 case `gettbl_coltype $tbl/$col` in

4287 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])

4288 test x"$val" = x"NULL" && val="''"

4289 ;;

4290 password) # special care for password

4291 # name={password,pswd1,pswd2}

4292 p1=`getpar pswd1 "$limit"`

4293 if [ -z "$p1" ]; then

4294 continue # SKIP password setting, if p1 is empty

4295 else

4296 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

4297 ## err pswd=$pswd

4298 if pwcheck "$pswd"; then

4299 if [ x"$p1" = x"$p2" ]; then

4300 case "$p1" in

4301 ??????????*) ;;

4302 *) echo "パスワードは10字以上にしてください。" | html p

4303 return 6;;

4304 esac

4305 val="\"`echo $p1|mypwhash`\""

4306 else

4307 echo "2つの新パスワード不一致" | html p

4308 return 7

4309 fi

4310 else

4311 echo "旧パスワード違います" | html p

4312 return 8

4313 fi

4314 fi

4315 ;;

4316 esac

4317 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

4318 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4319 if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then

4320 continue # Do not insert completely NULL record 2021-11-08

4321 fi

4322 sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"

4323 if [ x"$bin" = x"NULL" ]; then

4324 ## err Normal-query: `echo $sql`

4325 if [ -n "$transaction" ]; then

4326 printf '%s' "$sql" >> $transaction

4327 else

4328 query "$sql"

4329 fi

4330 else

4331 sqlfile="$tmpd/query.$$"

4332 ## err sqlfile=`ls -lF $sqlfile`

4333 if [ -n "$transaction" ]; then

4334 printf '%s' "$sql" >> $transaction

4335 else

4336 printf '%s' "$sql" > $sqlfile

4337 query ".read $sqlfile"

4338 fi

4339 fi

4340 ## err p2t done

4341 done

4342 done

4343 done

4344 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

4345 -- We cannot use transaction here, because groupupdate may use it.

4346 SAVEPOINT pa2table_insert;

4347 .read $transaction

4348 RELEASE SAVEPOINT pa2table_insert;

4349 EOF

4350 rc=$?

4351 [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate

4352 ## err "Table:$tbl update done "

4353 return $rc

4354 )

4355 genform() {

4356 # $1 = form definition file

4357 # $2, $3 (optional)= table name and ROWID

4358 # If $GF_VIEWONLY set and nonNull, output values without form

4359 # If $GF_ARGS set, use it as content-strings in the form

4360 # If $GF_OWNER set, use it as value of name="owner"

4361 # If $GF_STAGE set, use it as value of name="stage"

4362 forms="" hiddens="" rowid=$3

4363 if [ ! -e "$1" ]; then

4364 echo "そのようなデータベースはないようです($2)。" | html p

4365 return

4366 elif [ -n "$2" ]; then

4367 rec=`query "select * from $2 where rowid='$rowid';"`

4368 if [ -z "$rec" ]; then

4369 pk=`gettblpkey $2`

4370 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

4371 rec=`query "select rowid from $2 where $pk='$rowid';"`

4372 rowid=$rec

4373 rec=$3

4374 fi

4375 if [ -z "$rec" ]; then

4376 echo "そんなレコードはないみたいね..." | html p

4377 return

4378 fi

4379 fi

4380 if [ -z "$GF_VIEWONLY" ]; then

4381 rm='<input id="rm" name="rm" type="checkbox"

4382 value="yes"><label for="rm">このエントリの削除</label>

4383 <span>ほんとうに消しますよ(確認)!

4384 <input name="confirm" type=checkbox value="yes">はい</span>'

4385 fi

4386 # Image Cache dir

4387 ## err genform: getcache=$2/$rowid

4388 td=`getcachedir "$2/$rowid"`

4389 while IFS=: read -r prompt name keytype type args; do

4390 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

4391 sp="${args:+ }"

4392 form="" val=""

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

4394 # err genform2a: Seeking for "$2.$name, type=$type"

4395 rawval=`getvalbyid $2 $name $rowid $td`

4396 val=`printf '%s\n' "$rawval"|htmlescape`

4397 ## err genform3a: getvalbyid $2 $name $rowid $td

4398 ## err genform3b: val="[$val]" type="$type"

4399 fi

4400 if [ -n "$GF_VIEWONLY" ]; then

4401 is_hidden "$2" "$name" && continue

4402 fi

4403 case "$type" in

4404 text*)

4405 cgiform=cgi_multi_$type

4406 if [ -s $td/$name.count -a -n "$val" ]; then

4407 form=`$cgiform $name $td`

4408 val=$(printf '%s\n' "$val"|

4409 while read fn; do

4410 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4411 </td></tr>$nl"

4412 done)

4413 val="<table>$nl$val$nl</table>"

4414 else

4415 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4416

4417 form=`cgi_$type $name "$rawval" "$args"`

4418 fi

4419 ;;

4420 [Rr][Aa][Dd][Ii][Oo])

4421 fh="<label><input type=\"radio\" name=\"$name\""

4422 form="`echo $args|sed -e \

4423 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4424 ;;

4425 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4426 checked=${val:+ checked}

4427 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${args%=*}</label>"

4428 ;;

4429 [Ss][Ee][Ll][Ee][Cc][Tt])

4430 fh="<select name=\"$name\">$nl"

4431 form=$(for l in $args; do

4432 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4433 done)

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

4435 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4436 fi

4437 form="$fh$form</select>"

4438 ;;

4439 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4440 if [ -s $td/$name.count ]; then

4441 form=`cgi_multi_file $name $td "$args"`

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

4443 hrfb="$myname?showattc+$2_m"

4444 val=$(echo "$rawval" \

4445 | while read fn; do

4446 data=`percenthex "$td/$fn"`

4447 #ct=`cat $td/$fn.content-type`

4448 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4449 ri=`cat "$td/$fn.rowid"`

4450 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4451 #imgsrc="<img src=\"data:$ct,$data\">"

4452 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4453 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4454 done)

4455 fi

4456 else

4457 form="<input type=\"file\" name=\"$name\" $args>"

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

4459 imgs=$(echo "$rawval"\

4460 |while read fn;do

4461 data=`percenthex "$td/$fn"`

4462 echo "<img src=\"data:image/png,$data\">$fn<br>"

4463 done)

4464 form=$form"<br>$imgs"

4465 val=$imgs # 2015-06-15

4466 else

4467 form="<input type=\"file\" name=\"$name\" $args>"

4468 fi

4469 fi

4470 ;;

4471 [Hh][Ii][Dd][Dd][Ee][Nn])

4472 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4473 args="value=\"$GF_STAGE\""

4474 fi

4475 form="<input type=\"hidden\" name=\"$name\" $args>"

4476 prompt='' # Remove prompt

4477 ;;

4478 [Aa][Uu][Tt][Hh][Oo][Rr])

4479 [ -n "$GF_VIEWONLY" ] && continue

4480 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4481 prompt="" ;;

4482 [Oo][Ww][Nn][Ee][Rr])

4483 [ -n "$GF_VIEWONLY" ] && continue

4484 val=${GF_OWNER:-$val}

4485 val=${val:-$user}

4486 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4487 prompt="" ;;

4488 [Uu][Ss][Ee][Rr])

4489 # XXX: is null $user ok?

4490 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4491 [ -n "$GF_VIEWONLY" ] && continue

4492 form="$user"

4493 ;;

4494 [Pp]assword)

4495 [ -n "$GF_VIEWONLY" ] && continue

4496 form="`cgi_passwd`"

4497 val=""

4498 ;;

4499 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4500 [ -n "$GF_VIEWONLY" ] && continue

4501 if [ -z "$rowid" ]; then

4502 val=`genserial`

4503 fi

4504 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4505 prompt="" ;;

4506 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4507 prompt=""

4508 ;;

4509 parent|path|blog*)

4510 prompt=""

4511 ;;

4512 "*"*)

4513 tail=$tail"``"

4514 continue ;;

4515 esac

4516 if [ -n "$prompt" ]; then

4517 if [ -n "${GF_VIEWONLY}" ]; then

4518 form=$val

4519 else

4520 :

4521 fi

4522 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4523 else

4524 hiddens=$hiddens$nl"$form"

4525 fi

4526 done < $1

4527 # enctype="multipart/form-data"

4528 cat<<-EOF

4529 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4530 EOF

4531 test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh

4532 cat<<EOF

4533 <table class="b $2">

4534 $forms

4535 </table>$hiddens

4536 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4537 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4538 EOF

4539 if [ -z $GF_VIEWONLY ]; then

4540 cat<<EOF

4541 <input type="submit" name="sub" value="OK">

4542 <input type="reset" name="res" value="Reset">

4543 EOF

4544 fi

4545 cat<<EOF

4546 $GF_ARGS</form>

4547 $tail

4548 EOF

4549 }

4550 edittable() {

4551 # $1=form-def $2=table $3 rowid

4552 genform "$@"

4553 }

4554 viewtable() {

4555 GF_VIEWONLY=1 genform "$@"

4556 }

4557 showattc() {

4558 # $1=table_m $2=rowid &optional $3=RawFlag

4559 ## err \$1=$1 \$2=$2 \$3=$3

4560 if ! isfilereadable $user $1 $2; then

4561 contenttype; echo

4562 echo "このファイルは管理者のみしか見られません" | html p

4563 putfooter; exit

4564 fi

4565 idir=`umask 002; mktempd` || exit 1

4566 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4567 bin=$idir/$myname-$$.bin

4568 sql="select quote(bin) from $1 where rowid='$2';"

4569 ## err showattc: sql: $sql

4570 sq $db "$sql" | unhexize > $bin

4571 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4572 type=${tv%//*} fn=${tv#*//}

4573 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4574 ct=${type#file:}

4575 case $ct in # all text/* changed to text/plain

4576 text/*|application/csv|application/json)

4577 charset=`nkf -g $bin|cut -d' ' -f1`

4578 case $charset in

4579 ASCII*) charset="" ;;

4580 esac

4581 if [ -z "$3" ]; then

4582 ct="text/html${charset:+; charset=$charset}"

4583 link="?showattc+$1+$2+raw"

4584 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4585 | sed 's,^,<span></span>,' \

4586 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4587 -D_LINK_="$link" \

4588 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4589 exit $?

4590 fi

4591 ct="text/plain${charset:+; charset=$charset}"

4592 ;;

4593 esac

4594 contenttype "$ct"

4595 echo "Content-Disposition: filename=\"$fn\""

4596 echo "Content-Length: " `cat $bin | wc -c`; echo

4597 #echo "Content-Type: " ${type#file:}; echo

4598 cat $bin

4599 }

4600 #

4601 # Some default stupid handler on CGI values

4602 #

4603 default_storedb() {

4604 # ARG: $1=table-def-file

4605 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4606 tbl=`basename $1`

4607 tbl=${tbl%.def}

4608 cols="`grep :text $1|cut -d: -f2`"

4609 col=`echo "$cols"|head -1`

4610 vcol=`getpar $col`

4611 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4612 if [ -n "$vcol" ]; then

4613 par2table $1

4614 else

4615 return 2 # No insertion occurred

4616 fi

4617 }

4618

4619 default_view() { # $1=def-file

4620 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4621 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4622 default_storedb "$@"

4623 query "select rowid from $tbl order by rowid desc;" \

4624 | while read rowid; do

4625 viewtable $1 $tbl $rowid

4626 done | _m4 -D_TITLE_="$tbl" \

4627 -D_FORM_="`genform $1`" \

4628 -D_DUMPTABLE_="syscmd(cat)" \

4629 $layout/html.m4.html $layout/form+dump.m4.html

4630 }

4631 default_viewtext() { # $1=def-file

4632 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4633 default_storedb "$@"

4634 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4635 | _m4 -D_TITLE_="$tbl" \

4636 -D_FORM_="`genform $1`" \

4637 -D_DUMPTABLE_="syscmd(cat)" \

4638 $layout/html.m4.html $layout/form+dump.m4.html

4639 }

4640 default_smail() {

4641 default_storedb "$@"

4642 if [ $? -eq 2 ]; then

4643 _m4 -D_TITLE_="入力" \

4644 -D_FORM_="`genform $1`" \

4645 -D_DUMPTABLE_="" \

4646 $layout/html.m4.html $layout/form+dump.m4.html

4647 return

4648 fi

4649 cond=""

4650 for pk in `gettblpkey $tbl`; do

4651 pv=$(sqlquote "$(getpar $pk)")

4652 cond="$cond${cond:+ and }$pk=$pv"

4653 done

4654 sql="select rowid from $tbl where $cond;"

4655 rowid=`query "$sql"`

4656 ## err smail1 - "$sql" "-> rowid=$rowid"

4657

4658 while IFS=: read prompt name keytype type args; do # Read from $1

4659 val=`getpar $name`

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

4661 text="$text

4662 $prompt

4663 $name=$val

4664 ---------------------------------------------------------"

4665 fi

4666 case "$type" in

4667 image|document|file)

4668 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4669 fns=$(echo "$fn"|while read fn; do

4670 err mv $tmpd/$fn.orig $tmpd/$fn

4671 mv $tmpd/$fn.orig $tmpd/$fn

4672 rm $tmpd/$fn.rowid # Remove cache flag

4673 ## err "`ls $tmpd/$fn`"

4674 echo $fn

4675 done)

4676 files="$files $fns"

4677 ;;

4678 esac

4679 done < $1

4680 ## err FILES=$files "`ls -lF $tmpd`"

4681 subj="from ${REMOTE_ADDR}"

4682 (echo "$url"

4683 echo "への書き込みがありました。"

4684 echo "------"

4685 echo "$text"

4686 ) | (cd $tmpd &&

4687 err LS="`ls -lF`" &&

4688 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4689 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4690 echo "以下の内容で送信しました。" | html p

4691 viewtable $1 $tbl \

4692 `query "select rowid from $tbl order by rowid desc limit 1;"`

4693 echo "戻る" | html a "href=\"?\""

4694 }