s4: de9e56845da3 s4-funcs.sh

s4

view s4-funcs.sh @ 753:de9e56845da3

Temporary disable variable cache'ing for joingrp()

author	HIROSE Yuuji <yuuji@gentei.org>
date	Wed, 03 Jun 2020 21:33:25 +0900
parents	ebc1c0b19bf7
children	d78944aa1cd2 5bab8644024a

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 myname=`basename ${SCRIPT_NAME:-$0}`

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

9 myargs="$@"

10 test -n "$HTTP_HOST" && isCGI=true

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

12 tmpdir=${TMPDIR:-tmp}

13 dbdir=${DBDIR:-db}

14 tmpfiles=""

15 querylog=${QUERYLOG:-$tmpdir/query.log}

16 searchlog=${SEARCHLOG:-$tmpdir/search.log}

17 db=${DB:-$dbdir/cgi.sq3}

18 sessdb=$dbdir/sess.sq3

19 skey='skey' # Changed in s4-annex, if ANNEX mode

20 userupdateflag=$dbdir/userupdate

21 sesstb=tmp.sess

22 workdb=$dbdir/tmpdata.sq3

23 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

28 templ=${TEMPL:-templ}

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

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

31 imgdir=${IMGDIR:-img}

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

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

34 msgdir=$templ/msg

35 timeout="+2 days"

36 memoplimitdays="7"

37 dumpcollen=22

38 #thumbxy=120x120

39 thumbxy=96x96

40 iconxy_S=80x80

41 iconxy_M=400x400

42 maximagexy=1600x1600

43 ### maximagexy=400x400

44 filesize_max=$((5*1024*1024))

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

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

47 file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.kml,.html,.css,.rb,.c,.txt,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat"'

49 file_accept_help="

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

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

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

53 "

54 file_warn="$file_accept_help

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

56 blogreadflagrowid=0

57 blogcutoffflagrowid=-1

58 whatsnewdays=${WHATS_NEW_DAYS:-14}

60 tconfs=""

61 imgcached=cache/img.`date +%Y/%m`

62 conftbl=_tblconf

63 nl="

64 "

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

66 iconcachekey="profimgcache_S"

68 # Start debug logging

69 exec 3>> $tmpdir/debug.out

70 err() {

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

72 }

73 # If S4MASTERDB is set, behave as ANNEX s4

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

75 . ./s4-annex.sh 2>> tmp/debug.out

76 fi

79 case "$HTTP_USER_AGENT" in

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

81 *) touchpanel="" ;;

82 esac

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

85 : <<EOF

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

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

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

90 レポート提出管理

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

93 [2016]

94 7/12 根本への反省

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

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

98 [以下2015]

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

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

101 Web

102 締切設定

103

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

105 ○自分の提出物リスト

106

107 7/19 ○設置

108 ○一斉送信

109 ○getparfilename の tmpd の扱い

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

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

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

113 ○添付ファイル回収

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

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

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

117

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

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

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

121 ○レポート提出モード

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

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

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

125

126

127 7/9 ○管理者の追加

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

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

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

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

132

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

134

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

136 ○グループ検索

137

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

139

140

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

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

143

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

145 ○6/1 par2tableを triplex 対応に

146 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'));

147 →とすると一気に

148

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

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

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

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

153

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

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

156 ## 1. passwd

157 ## 2. grp

158 ## 3. grp_mem

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

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

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

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

163

164 ## ● listの定義:

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

166 ## ● hashの定義:

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

168

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

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

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

172 ## csv2sq3 で .csv.sq3 の Makefile

173

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

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

176 ## type := root | comment

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

178 ## unique(id, filename)

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

180

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

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

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

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

185 ## できたー!

186 ## 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';

187 ↓

188 ↓以下に変更

189 with recursive allmem as

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

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

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

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

194

195

196 with recursive allmem as

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

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

199 where allmem.val=grp_m.gname)

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

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

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

203

204

205 ## triggerもできた。

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

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

208

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

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

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

212 ## グループ管理

213 ## 個人情報管理

214 ##

215

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

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

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

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

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

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

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

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

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

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

226

227 ## ログテーブル

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

229

230

231

232 ■表設計

233 * 3つの表に分散管理

234 id格納表 + hash表 + list表

235 * *_s *_m

236

237

238

239 user, user_map, user_col

240

241 ■抽象エントリタイプ

242 * user

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

244 * group

245 権限判定に利用

246 * serial

247 自動idとして機能

248 * password

249 入力 type=passwordで入力

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

251 * session

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

253 * text

254 入力 type=text

255 * textarea

256 入力 textarea

257 * image|document

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

259 * owner

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

261 * gowner

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

263 * timestamp

264 datetime()

265 * parent

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

267 * path

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

269

270 格納タイプ

271 * list

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

273 * hash

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

275

276 オブジェクトタイプ

277 * entry

278 id, title, owner

279 * textpart

280 id, parentID, text

281 * binarypart

282 id, parentID, contenttype, filename, content

283 * content

284 hash(textpart), list(binarypart)

285 * topic

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

287 * reply

288 id, parentID, content

289 * blog

290 list(entry)

291 blog = [topic, list(reply)]

292

293

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

295 "text" => "hogehoge ..",

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

297 "author" => "taro",

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

299 "parent" => "/",

300 "path" => "/1",

301 "text" => "blah, blah, ....",

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

303 {"serial" => 2,

304 "author" => "hanako",

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

306 "parent" => "/",

307 "path" => "/2",

308 "text" => "blah, blah, ....",

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

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

311

312

313 user:=

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

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

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

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

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

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

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

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

322

323 変換表

324 /user/email=m

325

326 blog:=

327 シリアル:id:p:serial

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

329 所有者:owner:s:owner:

330 時刻:ctime:s:stamp:

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

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

333

334 article:=

335 シリアル:id:p:serial

336 筆者:author:s:owner

337 時刻:ctime:s:stamp:

338 参照元:parent:s:parent:

339 パス:path:s:path:

340 本文:text:s:textarea:

341 画像:image:m:image:

342

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

344

345

346 EOF

347

348 logstart() {

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

350 }

351 logend() {

352 echo ">>>" >> ${1:-$querylog}

353 }

354 sqlog() {

355 logstart

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

357 cat >> $querylog

358 else

359 echo "$*" >> $querylog

360 fi

361 logend

362 }

363 sq() {

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

365 logstart

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

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

368 else

369 echo "$@" >> $querylog

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

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

372 fi

373 logend

374 }

375 dbsetup() {

376 pipedir=$tmpdir/pipedir

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

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

379 suf=`date +%s`

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

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

382 mkfifo $sqi $sqo

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

384 sq $db < $sqi &

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

386 if [ -n "$isCGI" ]; then

387 exec 2>> $tmpdir/error.out

388 fi

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

390 chmod o-r $tmpdir/error.out $tmpdir/debug.out

391 rm $sqi

392 # Attach supplemental DB

393 cat >&5 <<-EOF

394 .output /dev/null

395 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

399 .output stdout

400 EOF

401 }

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

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

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

405 fi

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

407 }

408 cleanup() {

409 trap '' INT HUP EXIT TERM PIPE

410 echo .quit >&5

411 kill $sq3pid

412 kill $sq3pid

413 rm -f $sqo $sqi

414 rm -rf $tmpfiles

415 cleanup2

416 }

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

418 # simple redirection for the sake of speed.

419 query() {

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

421 echo ".output $sqo" >&5

422 logstart

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

424 tee -a $querylog

425 else

426 echo "$@" >> $querylog

427 echo "$@"

428 fi >&5

429 echo ".output stdout" >&5

430 cat $sqo

431 rc=$?

432 logend

433 return $rc

434 }

435 _m4() {

436 #S4NAME=f,f,f

437 m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} "$@"

438 }

439 ismember() {

440 # $1=user, $2=group

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

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

443 }

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

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

446 }

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

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

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

450 }

451 isgrpowner() (

452 # $1=user, $2=group

453 gn=`sqlquote "$2"`

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

455 err isgrpowner: $sql

456 test -n "`query $sql`"

457 )

458 isgrpownerbygid() (

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

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

461 err isgrpownerbygid: $sql

462 test -n "`query $sql`"

463 )

464 getgroupadminmails() {

465 # $1=group

466 for i in $(getgroupadmins $1); do

467 email4group "$1" "$i" ;

468 done

469 }

470 getgroupadmins() { # $1=group

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

472 qgrp=`sqlquote "$1"`

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

474 }

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

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

477 getvalbyid grp $2 \

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

479 }

480 getgroupbyid() {

481 # $1=id|gname

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

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

484 # err ggbyid: `echo $sql`

485 query $sql

486 }

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

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

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

490

491 # Return true when anonymous mode

492 [ "$anonymousmode" ] && return 0

493 # case `getvalbyid blog mode $2` in

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

495 # *closed*)

496 # owner=`getvalbyid blog owner $2`

497 # if isgrp $owner; then

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

499 # elif isuser $owner; then

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

501 # fi

502 # esac

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

504 sql="with getblog as (

505 select key,val from blog_s where id=(

506 select blogid from article where id in

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

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

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

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

511 isgrpadm as (select user from grp_adm where

512 gname=(select val from getowner) and

513 user='$1'),

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

515 select case

516 when (select author from article where

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

518 then 'author'

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

520 then 'open'

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

522 then CASE

523 WHEN (SELECT val FROM isgrp) IS NULL

524 THEN

525 CASE WHEN (SELECT val from getowner)

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

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

528 ELSE ''

529 END

530 WHEN (select user from isgrpadm) IS NOT NULL

531 THEN 'i-am-admin'

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

533 END

534 when (select val from getmode) is null

535 then 'open'

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

537 then (SELECT user FROM isgrpadm)

538 when (select author from article where

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

540 then 'user+author'

541 else '' end;"

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

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

544 # result=`query "$sql"`

545 # err FileAccessibility=$result

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

547 }

548 linkhome() {

549 # $1=UserOrGroup

550 echo -n '<a href="?'

551 if isuser $1; then

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

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

554 else

555 echo -n "grp+$1"

556 fi

557 echo "\">`gecos $1`</a>"

558 }

559 hreflink() {

560 # s4 specific notation:

561 # ^href=URL

562 # ^iframe=URL

563 # ^video=URL

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

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

566 # OSM umap Wikistyle Notation:

567 # [[URL]] - Simle Link

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

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

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

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

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

573 # Other Style

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

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

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

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

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

579 # SPC+SPC+$ - <br>

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

581 checkboxON="${cb} checked>"

582 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

607

608 }

609 minitbl() {

610 sed -n '

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

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

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

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

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

616 H; # Concat this line to HoldSpace

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

618 $ b cont

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

620 }

621 :cont

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

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

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

625 # . of ^. is workaround for FreeBSD sed

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

627 p; # Print whole "table" element

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

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

630 }

631 x; # Back to the newest line

632 p; # Print rest' | miniul

633 }

634 miniul() {

635 sed -e '

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

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

638 :top

639 s/\n//;

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

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

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

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

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

645 $ b cont

646 N; # 次の行を読む

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

648 /^ /b top

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

650 p; s/.*//;

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

652 /^\* /b top

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

654 }

655

656 :cont

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

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

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

660 H; x

661 }

662 x'

663 }

664 acclog() (

665 # $1=table, $2=rowid

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

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

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

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

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

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

672 fi

673 )

674 gecos() (

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

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

677 )

678 setpar() {

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

680 case "$1" in

681 user) _user="$v" ;;

682 skey) _skey="$v" ;;

683 esac

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

685 }

686 replpar() {

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

688 }

689 getpar() {

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

691 val=""

692 if false; then # 2020/6/3 Temporary disabled for joingrp()

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

694 user) val=$_user ;;

695 skey) val=$_skey ;;

696 esac

697 fi

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

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

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

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

702 fi

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

704 case "$var" in

705 owner)

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

707 echo $user; return

708 elif ismember $user $val; then

709 echo $val; return

710 fi ;;

711 esac

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

713 echo "$val"

714 }

715 setskey() {

716 # For quick response...(?)

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

718 }

719 chkskey() {

720 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

726 return 0

727 fi

728 return 1

729 }

730 resetskey() {

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

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

733 fi

734 }

735 getpartype() {

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

737 }

738 getparcount() {

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

740 }

741 getparfilename() {

742 # null if type of $1 is not file

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

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

745 }

746 sqlquote() {

747 (v="$1"

748 case "$v" in

749 "") return ;; # null

750 "X'"*) # quoted hex string

751 echo $1 ;;

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

753 v=`echo "$v"|sed -e 's/\"/\"\"/g'`

754 echo "\"$v\""

755 return ;;

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

757 echo "\"$v\""

758 return ;;

759 *)

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

761 echo $v # MAYBE numeric, maybe...

762 else

763 echo "\"$v\""

764 fi ;;

765 esac)

766 }

767 sqlquotestr() (

768 case "$1" in

769 *\'*) v=`echo "$1"| sed "s/'/''/g"`

770 echo "'$v'" ;;

771 *) echo "'$1'" ;;

772 esac

773 )

774 mktempd() {

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

776 }

777 getcachedir() { # $1=maintable

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

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

780 else

781 echo $tmpd/$thumbxy

782 fi

783 }

784 getval() {

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

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

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

788 echo "$user" ;;

789 stamp|datetime)

790 date "+%F %T" ;;

791 serial)

792 (s=`getpar $2`

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

794 *)

795 getpar "$2" "$3";;

796 esac

797 }

798

799 getvalquote() {

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

801 (v=`getval "$@"`

802 case "$v" in

803 "") echo NULL ;;

804 *) sqlquote "$v" ;;

805 esac)

806 }

807 getparquote() {

808 sqlquote "`getpar $1`"

809 }

810 getbinbyid() {

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

812

813 }

814 getvalbyid() {

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

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

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

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

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

820

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

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

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

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

825 return

826 fi

827 done

828 rowid=$3

829 pk=`gettblpkey $1`

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

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

832 td=${4:-$tmpd}

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

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

835 for kt in s m; do

836 t=${1}_$kt

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

838 vcount=1 # count(val)

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

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

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

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

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

844 if [ $kt = m ]; then

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

846 # Reset val to store filenames if type is string

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

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

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

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

851 i=0

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

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

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

855 i=$((i+1))

856 fn=$c.$i

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

858 case $type in

859 file:*)

860 #file=$td/$val

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

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

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

864 # FOR SPEED: Skip file generation if imgcache exists

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

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

867 && continue

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

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

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

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

872 select rowid from $cond $slice;

873 .output '$td/$fn'

874 select val from $cond $slice;

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

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

877 .output stdout

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

879 EOF

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

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

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

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

884 ls -lh "$file" |

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

886 case "$type" in

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

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

889 *|*:[Aa]pplication*)

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

891 png:- > "$file"

892 ;;

893 esac

894 ;;

895 *)

896 sq $db<<EOF

897 .output $td/$fn.rowid

898 select rowid from $cond $slice;

899 .output $td/$fn

900 select val from $cond $slice;

901 EOF

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

903 ;;

904 esac

905 done

906 else

907 rm -f $td/$c.count

908 case $type in

909 file:*)

910 echo "$val" \

911 | while read fn; do

912 file=$td/$fn

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

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

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

916 | unhexize > "$file"

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

918 case $type in

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

920 *:[Aa]pplication*)

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

922 png:- > $file ;;

923 esac

924 fi

925 done

926 ;;

927 esac

928 fi

929 echo "$val" # Keep newlines by ""

930 return

931 fi

932 done

933 done)

934 }

935 getvalbypkey() (

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

937 pk=`gettblpkey $1`

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

939 getvalbyid "$1" "$2" $rowid $4

940 )

941 getvalbycond() {

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

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

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

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

946 getvalbyid "$1" "$2" $rowid "$4"

947 fi

948 }

949 getpwfield() {

950 # getpwfield user column

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

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

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

954 echo "$val"

955 return 0

956 else

957 return 1

958 fi

959 }

960 numericalize() {

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

962 }

963 encode() {

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

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

966 sha1=sha1

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

968 sha1=sha1sum

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

970 sha1=gsha1sum

971 fi

972 fi

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

974 }

975 enjpeg() {

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

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

978 cjpeg="cjpeg"

979 else

980 cjpeg="convert - jpeg:-"

981 fi

982 fi

983 $cjpeg "$@"

984 }

985 mycrypt() (

986 key=$1 salt=$2

987 # err \$2=$2

988 case $2 in

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

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

991 esac

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

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

994 )

995 hexize() {

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

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

998 hexize="xxd -p"

999 else

1000 hexize_hd() {

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

1002 }

1003 hexize="hexize_hd"

1004 fi

1005 fi

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

1007 }

1008 unhexize() {

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

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

1011 unhex="xxd -p -r"

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

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

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

1015 EOF

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

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

1018 # world writable...:)

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

1020 fi

1021 fi

1022 cat "$@" | $unhex

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

1024 }

1025 percenthex() {

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

1027 }

1028 htmlescape() {

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

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

1031 }

1032 enascii() {

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

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

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

1036 else

1037 enascii_now=`date +%FT%T`

1038 enascii_sed() {

1039 nkf -Z0Z1Z2 \

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

1041 }

1042 enascii="enascii_sed"

1043 fi

1044 fi

1045 cat "$@" | $enascii

1046 }

1047 size_h() {

1048 i="$1" oi=$1

1049 set -- B B KB MB GB TB

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

1051 oi=$i

1052 i=$((i/1024))

1053 shift

1054 done

1055 echo ${oi}$1

1056 }

1057 gettblconf() {

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

1059 ## tconfs=`sq $db \

1060 tconfs=`query \

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

1062 fi

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

1064 }

1065 gettblkeys() {

1066 # $1=tbl

1067 gettblconf

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

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

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

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

1072 case $type in

1073 *p*)

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

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

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

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

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

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

1080 esac

1081 case $type in

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

1083 esac

1084 done

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

1086 }

1087 gettblpkey() {

1088 # $1=tbl

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

1090 }

1091 gettblfkey() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettblcols() {

1097 (x=`gettblkeys $1`

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

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

1100 }

1101 gettbl_s_cols() {

1102 (x=`gettblkeys $1`

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

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

1105 }

1106 gettbl_m_cols() {

1107 (x=`gettblkeys $1`

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

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

1110 }

1111 gettbl_h_cols() {

1112 (x=`gettblkeys $1`

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

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

1115 }

1116 gettbl_coltype() (

1117 gettblconf

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

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

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

1121 )

1122 is_hidden() {

1123 # $1=Tbl $2=col

1124 gettblconf

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

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

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

1128 case $x in

1129 *h*) return 0 ;;

1130 *) return 1 ;;

1131 esac

1132 }

1133

1134 dbsetbyid() {

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

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

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

1138 quotedp=$(sqlquotestr "$p")

1139 unset primary update

1140 gettblconf

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

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

1143 #err conf=$conf

1144 case ${conf#*=} in

1145 p*) primary=1 ;;

1146 f*) update=1 ;;

1147 u*) ;;

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

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

1150 esac

1151 #err t=$t

1152 type=string fn=""

1153 case $conf in

1154 */password)

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

1156 ;;

1157 */image*|*/document*)

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

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

1160 ;;

1161 esac

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

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

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

1165 if [ "$primary" ]; then

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

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

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

1169 elif [ "$update" ]; then

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

1171 else

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

1173 fi

1174 )

1175 }

1176 expire() (

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

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

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

1180 )

1181 addsession() {

1182 # expireをセット

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

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

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

1186 # Remove old session parameters

1187 now=`expire now`

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

1189 }

1190 gencookie() (

1191 path=${URL#*:/}

1192 path=${URL%/*}

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

1194 for kv; do

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

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

1197 done

1198 )

1199 contenttype() {

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

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

1202 }

1203 putheader() {

1204

1205 }

1206 putfooter() {

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

1208 }

1209 getcookie() {

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

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

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

1213 case "$k" in

1214 user) _user="$v" ;;

1215 $skey) _skey="$v" ;;

1216 esac

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

1218 done

1219 }

1220 genrandom() {

1221 # $1=columns (default: 10)

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

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

1224 }

1225 genserial() {

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

1227 }

1228 smail() {

1229 # smail rcpts subj (file)

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

1231 # $MAIL_FROM <- From: header value

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

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

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

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

1236 sender=${SENDER:-$admin}

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

1238 (_m4 -D_RCPT_="${SMAIL_TO:-$rcptheader}" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4

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

1240 }

1241 smail_queue_flush() {

1242 # $1=timelimit

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

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

1245 SELECT rowid FROM smailq

1246 GROUP BY rcpts, subj

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

1248 EOF

1249 )

1250 for rid in $rowids; do

1251 sq $workdb \

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

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

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

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

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

1257

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

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

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

1261 .separator "\n" "------------------\n\n"

1262 SELECT time, text FROM smailq

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

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

1265 ORDER by time;

1266 EOF

1267 then

1268 echo rowid=$rid

1269 cat <<-EOF | sq $workdb

1270 DELETE FROM smailq

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

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

1273 EOF

1274 fi

1275 fi

1276 done

1277 }

1278 smail_queue() {

1279 # $1=Rcpts, $@=subj

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

1281 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1290 EOF

1291 )

1292 ## XXX: Adhoc load mitigation

1293 case "$now" in

1294 *[01])

1295 err flush_queue=$mintime

1296 smail_queue_flush ;;

1297 esac

1298 fi

1299 }

1300 setviastring() {

1301 table=par

1302 oifs="$IFS"

1303 IFS="&"

1304 for us in $1; do

1305 k=${us%%=*}

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

1307 setpar "$k" "string" "$v"

1308 done

1309 IFS="$oifs"

1310 }

1311 checkdomain() (

1312 # Check the validity of domain by referring DNS

1313 item=$1

1314 err checkdomain $1

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

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

1317 )

1318 pwcheck() {

1319 # $1=passwd

1320 dbpswd=`getpwfield $user pswd`

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

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

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

1324 }

1325 mypwhash() {

1326 mycrypt "`cat`" `genrandom 5`

1327 }

1328 wasureta() {

1329 user=$1

1330 if ! checkdomain $user; then

1331 contenttype; echo

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

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

1334 putfooter

1335 exit 0

1336 fi

1337 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1339 encpswd=`echo $newpswd|mypwhash`

1340 dbsetbyid user $user pswd "$encpswd"

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

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

1343 $msgdir/mail-newaccount.m4 \

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

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

1346 }

1347 checkauth() {

1348 user=`getpar user`

1349 skc=`getpar skey` # from cookie

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

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

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

1353 if chkskey "$skc"; then

1354 return 0

1355 fi

1356 fi

1357 pswd=`getpar pswd`

1358 quser=`sqlquotestr "$user"`

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

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

1361 return 4 # 4=server too heavy

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

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

1364 return 2 # 2=login fail

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

1366 wasureta "$user"

1367 return 1 # wasureta error

1368 fi

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

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

1371 if pwcheck "$pswd"; then

1372 newsession=`genrandom 34`

1373 if setskey "$user" "$newsession" &&

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

1375 err gencookie "user=$user" "$skey=$newsession"

1376 gencookie "user=$user" "$skey=$newsession"

1377 return 0

1378 else

1379 return 4 # Heavy load??

1380 fi

1381 fi

1382 err "Login failed: [$user]"

1383 return 2 # Password mismatch

1384 }

1385 showlogin() {

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

1387 test -z "$args" && resetskey

1388 s4name=${S4NAME:-s4}

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

1390 cat $layout/login.m4.html

1391 echo '</body></html>'

1392 ) | _m4 \

1393 -D_BODYCLASS_="login" \

1394 -D_TITLE_="$s4name" \

1395 -D_SYSNAME_="Welcome to $s4name" \

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

1397 exit 0

1398 }

1399 dologin() {

1400 checkauth

1401 st=$?

1402 if [ $st != 0 ]; then

1403 contenttype; echo

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

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

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

1407 showlogin # and EXIT

1408 fi

1409 }

1410

1411 # Do instant jobs here

1412 dbsetup

1413 trap cleanup INT HUP EXIT TERM PIPE

1414 # trap cleanup INT HUP

1415

1416

1417 cgiinit() {

1418 session=`date +%F-$$`

1419 tmpd=`tmpd=$tmpdir mktempd`

1420 tmpf=$tmpd/stream.$$

1421 tmpfiles=$tmpfiles" $tmpd"

1422 addsession $session

1423 getcookie

1424 case "$REQUEST_METHOD" in

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

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

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

1428 # cat > $tmpf # too much?

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

1430 (echo CL=$CONTENT_LENGTH; ls -lF $tmpf) 1>&3

1431 s="`cat $tmpf`"

1432 ;;

1433 esac

1434 case "$CONTENT_TYPE" in

1435 *boundary*)

1436 bndry=${CONTENT_TYPE#*boundary=}

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

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

1439 do

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

1441 #echo u=$us

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

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

1444 # err k=$k v=$v

1445 case "$k" in

1446 *:filename)

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

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

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

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

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

1452 :

1453 fi

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

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

1456 case "$mimetype" in

1457 [Ii]mage/x-xcf)

1458 bzip2 "$tmpd/$v"

1459 v=${v}.bz2

1460 ;;

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

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

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

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

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

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

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

1468 v=$vjpg

1469 else

1470 mimetype="Not supported"

1471 fi

1472 ;;

1473 [Ii]mage/*)

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

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

1476 ;;

1477 esac

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

1479 then

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

1481 continue

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

1483 replpar text string \

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

1485 continue

1486 fi

1487 ;;

1488 *)

1489 type='string'

1490 ;;

1491 esac

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

1493 setpar "$k" "$type" "$v"

1494 done

1495 ;;

1496 *)

1497 setviastring "$s"

1498 ;;

1499 esac

1500 }

1501 email4group() {

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

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

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

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

1506 sql="WITH

1507 grpemails AS (

1508 SELECT gname, user, val email

1509 FROM grp_mem NATURAL JOIN grp_mem_s

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

1511 useremails AS (

1512 SELECT user.name, val email

1513 FROM user

1514 LEFT JOIN user_m

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

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

1517 FROM useremails u LEFT JOIN grpemails g

1518 ON u.name=g.user

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

1520 query "$sql"

1521 }

1522 email4groupbyuid() {

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

1524 g=$1; shift

1525 uids=`echo "$@"`

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

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

1528 email4group "$g" `query "$sql"`

1529 }

1530 myemail4group() {

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

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

1533 }

1534 collectmembersbyid() {

1535 # Collect user names of group specified by grid

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

1537 query "SELECT user FROM grp_mem \

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

1539 }

1540 collectmembersbyid() {

1541 # Collect user names of group name

1542 qgrp=`sqlquote "$1"`

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

1544 }

1545 collectgecosesbyid() {

1546 # Collect user gecoses of group

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

1548 query<<-EOF

1549 SELECT gecos

1550 FROM gecoses

1551 WHERE name IN (SELECT user FROM grp_mem

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

1553 EOF

1554 }

1555 collectemail() (

1556 # Collect email addresses for group $1

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

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

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

1560 for e; do

1561 if isuser "$e"; then

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

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

1564 else

1565 qgrp=`sqlquote "$e"`

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

1567 gmem="grp_mem"

1568 else

1569 tm=`sqlquote "$TEAM"`

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

1571 fi

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

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

1574 $gmem g left join grp_mem_s s

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

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

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

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

1579 query "$sql"

1580 fi

1581 done

1582 )

1583 sendinvitation() (

1584 # $1=email

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

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

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

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

1589 gecos=`gecos`

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

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

1592 _m4 -D_URL_="$urlbase" \

1593 -D_USER_="$name" \

1594 -D_EMAIL_="$1" \

1595 -D_REGIST_="$regist" \

1596 -D_ADMIN_="$admin" \

1597 $msgdir/mail-invite.m4 \

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

1599 return 0

1600 )

1601 emaildomaincheck() {

1602 case "$1" in

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

1604 *@*)

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

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

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

1608 return 2

1609 fi

1610 return 0

1611 ;;

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

1613 esac

1614 }

1615 invite() {

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

1617 case "$email" in

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

1619 *@*)

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

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

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

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

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

1625 elif sendinvitation $email; then

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

1627 else # Cannot be reached here

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

1629 fi ;;

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

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

1632 esac

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

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

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

1636 fi

1637 if [ -f $invite_policy ]; then

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

1639 else

1640 pol="$invite_policy"

1641 fi

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

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

1644 -D_INVITE_POLICY_="$pol" \

1645 $layout/html.m4.html $layout/invite.m4.html

1646 }

1647 regist() {

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

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

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

1651 echo "bye bye" | html p

1652 reutrn

1653 fi

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

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

1656 cat<<EOF

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

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

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

1660 EOF

1661 return

1662 fi

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

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

1665 # Fake login password to wasureta

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

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

1668 wasureta $email

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

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

1671 addsession $1 # for removal after 1 minute

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

1673 $layout/login.m4.html

1674 return

1675 }

1676 group_safename() {

1677 # Convert $1 to safe group name

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

1679 }

1680 groupupdate() {

1681 gname=`getpar gname`

1682 qgname=`sqlquote "$gname"`

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

1684 # See ALSO same job in showgroup()

1685 newgname=`group_safename "$gname"`

1686 err newgname=$newgname

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

1688 err NewGNAME: gname=$newgname

1689 gname=$newgname

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

1691 replpar gname string "$gname"

1692 fi

1693 # Name confliction check

1694 parow=`getpar rowid`

1695 ## err parow=$parow

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

1697 query "BEGIN EXCLUSIVE;"

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

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

1700 if [ $count -gt 0 ]; then

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

1702 query "END;"

1703 return

1704 fi

1705 par2table $formdir/grp.def

1706 query "END TRANSACTION;"

1707 # Remove orphan

1708 : <<EOF

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

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

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

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

1713 EOF

1714 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1718 sql="delete from blog where id in

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

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

1721 query "$sql";

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

1723 return # and return

1724 fi

1725 fi

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

1727 fi

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

1729 grid=$(query $sql)

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

1731 grp $grid

1732 }

1733 groupclone() {

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

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

1736 case "$1" in

1737 */noteam)

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

1739 esac

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

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

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

1743 return

1744 fi

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

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

1747 return

1748 fi

1749 i=0

1750 while true; do

1751 copy="-copy$i"

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

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

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

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

1756 i=$((i++))

1757 continue

1758 fi

1759 break

1760 done

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

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

1763 query<<-EOF

1764 BEGIN;

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

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

1767 SELECT $newqname, key, val

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

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

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

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

1772 -- Copy members and their configuration --

1773 REPLACE INTO grp_mem SELECT $newqname, user

1774 FROM grp_mem WHERE gname=$qgrp;

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

1776 FROM grp_mem_s WHERE gname=$qgrp;

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

1778 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1779 -- Copy administrators --

1780 REPLACE INTO grp_adm SELECT $newqname, user

1781 FROM grp_adm WHERE gname=$qgrp;

1782 COMMIT;

1783 EOF

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

1785 STOPCLONEMSG=1 groupconf "$newrowid"

1786 }

1787 groupman() {

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

1789

1790 GF_STAGE="grpconf"

1791 GF_STAGE=groupupdate

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

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

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

1795 -D_DUMPTABLE_="syscmd(cat)" \

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

1797 }

1798 userconf() {

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

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

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

1802 }

1803 groupconf() {

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

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

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

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

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

1809

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

1811 grp=`getgroupbyid $rowid`

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

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

1814 return

1815 fi

1816

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

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

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

1820 html div 'class="fold"' <<-EOF

1821 `cgi_checkbox clone yes id="clone"`<label

1822 for="clone">同一メンバーで別グループを作成する</label>

1823 <div>

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

1825 <table>

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

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

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

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

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

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

1832 </table>

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

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

1835 </div>

1836 EOF

1837 fi

1838 }

1839 mems() {

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

1841 kwd=`getpar kwd`

1842 listmember $kwd

1843 }

1844 grps() {

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

1846 kwd=`getpar kwd`

1847 listgroup $kwd \

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

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

1850 -D_FORM_="<a href=\"?groupman\">新規グループ作成</a>" \

1851 $layout/form+dump.m4.html

1852 }

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

1854 gpg=`getpar grp`

1855 grid=${1:-$gpg}

1856 grp=`getgroupbyid "$grid"`

1857 ## . ./s4-blog.sh

1858 jg=`getpar joingrp`

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

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

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

1862 fi

1863 htmlheader=$layout/html.m4.html

1864 showgroup "$grid"

1865 }

1866 sql4interestblogs() {

1867 cat<<EOF

1868 CREATE TEMPORARY VIEW interestblogs AS

1869 SELECT blog.rowid rid, id, author

1870 FROM blog

1871 NATURAL JOIN

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

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

1874 THEN 1 -- blog owner is an user, READABLE

1875 WHEN (SELECT user FROM grp_mem

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

1877 THEN 0

1878 ELSE 1

1879 END;

1880 EOF

1881 }

1882 listnewblogsql() { # $1=user

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

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

1885 newdays=${newdays:-14}

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

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

1888 WHERE user='$user'

1889 AND tblrowid IN

1890 ($blogreadflagrowid,

1891 $blogcutoffflagrowid)),

1892 $basetime -- "0"

1893 );"`

1894 cat<<EOF

1895 `sql4interestblogs`

1896 WITH article_ctime as (

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

1898 FROM article join article_s s using(id)

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

1900 GROUP BY id

1901 ), blog_title_owner as (

1902 SELECT blg.rid brid, id,

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

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

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

1906 ), blogall as (

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

1908 ), news as (

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

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

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

1912 FROM blogall bl

1913 LEFT JOIN

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

1915 ON bl.brid=al.tblrowid

1916 WHERE atime < bl.ctime

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

1918 LIMIT 10

1919 ) SELECT brid LINK, "新着",

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

1921 ctime, title,

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

1923 FROM news;

1924 EOF

1925 }

1926

1927 search_form() {

1928 # $1 = { author=<AUTHOR> | grp=<GROUP> }

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

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

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

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

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

1934 @week → 最近一週間

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

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

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

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

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

1940 auth=""

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

1942 case "$1" in

1943 author=*)

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

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

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

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

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

1949 ;;

1950 grp=*)

1951 a=`echo "${1#grp=}"|htmlescape`

1952 g=`gecos ${1#grp=}`

1953 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

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

1955 ;;

1956 esac

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

1958 cat<<-EOF

1959 <div class="right">

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

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

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

1963

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

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

1966

1967 </form>

1968 </div>

1969 EOF

1970 }

1971

1972 imgsrc_cache() (

1973 # $1 = directory for cache'ing

1974 # $2 = table (user_m or grp_m)

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

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

1977 dir="$1" tbl="$2"

1978 keycond="$3"

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

1980 ORDER BY rowid DESC LIMIT 1"

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

1982 tmpf=$tmpd/imgsrc_cache.$$

1983 case "$4" in

1984 [Ss]) size=S ;;

1985 [Oo]) size=O ;;

1986 *) size=M ;;

1987 esac

1988 # ImageCache filename storing schema:

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

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

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

1992 valtype=`query "$sql0"`

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

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

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

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

1997 case "$filetype" in

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

1999 esac

2000 fi

2001 cacheimg_S=$dir/S_$filename

2002 cacheimg_M=$dir/M_$filename

2003 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

2011 else

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

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

2014 fifo2=$fifo.2

2015 mkfifo $fifo $fifo2

2016 fmt=${filename##*.}

2017 ## [[ NOTE ]]

2018 ## a. convert oldimage newimage

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

2020 ## b is much smaller than a

2021 cat $tmpf | unhexize \

2022 | tee $fifo \

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

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

2025 | tee $fifo2 \

2026 | convert - "$cacheimg_M" &

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

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

2029 | convert - "$cacheimg_S" &

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

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

2032 echo '">'

2033 echo "$sum" > $sumfile

2034 fi

2035 ## Now preparing cache image, done.

2036 ## Store this information to DB

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

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

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

2040 query <<-EOF

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

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

2043 EOF

2044 )

2045

2046 showhome() {

2047 # $1=userRowIdToShow

2048 err showhome \$1=$1

2049 case "$1" in

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

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

2052 esac

2053 ## err ShowHome: uname=$uname

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

2055 gecos=`gecos "$uname"`

2056 ## err SH:gecos=$gecos

2057 GF_VIEWONLY=1

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

2059 search_form_args=""

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

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

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

2063 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2064 # Display folders

2065 sql="select count(id) from article_m where id

2066 in (select id from article where author='$user')

2067 and type like 'file:%';"

2068 ## err nfile-sql=`echo "$sql"`

2069 nfile=`query "$sql"`

2070 # err nfile=$nfile

2071 if [ $nfile -gt 0 ]; then

2072 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2073 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2074 fi

2075 else

2076 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2077 GROUP BY user;"`

2078 usermenu="<p>Last seen on $latestlog</p>"

2079 search_form_args="author=$uname"

2080 fi

2081 . ./s4-blog.sh

2082

2083 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2084 search_form "$search_form_args" > $sf

2085 printf "%s さん" "$gecos" > $tf

2086 { echo "<div class=\"noprofimg\">"

2087 viewtable $formdir/user.def user $1

2088 echo "</div>"

2089 } > $pf

2090

2091 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2092 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2093 imf=$tmpd/profimg.$$; touch $imf

2094 if [ -n "$img" ]; then

2095 if true; then

2096 tbl=user_m

2097 enticond="name='$uname'"

2098 imgsrc_cache "$td/main" user_m "$enticond" M

2099 else

2100 { printf '%s' "<IMG src=\"data:${img#file:},"

2101 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2102 | sed 's/$..$/%\1/g'

2103 echo '">'

2104 }

2105 fi > $imf

2106 fi

2107 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2108 val='$uname';"`

2109 ## REMOVE This comment block until 2019/7/1

2110 ## err "----- `gdate +%FT%T.%3N` ------------C"

2111 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2112 listblog $uname > $bf

2113 ## unset ddd

2114 ## err "----- `gdate +%FT%T.%3N` ------------D"

2115

2116 hometail=$tmpd/tail.$$

2117 mkfifo $hometail

2118

2119 #Calling listgroupbytable, originally here

2120

2121 (

2122 # Display Most Recent Entry

2123 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2124 shortval=${shortval:-val}

2125

2126 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2127 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2128 ctime,

2129 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2130 (SELECT gecos FROM gecoses

2131 WHERE name=(SELECT val FROM blog_s

2132 WHERE key='owner' AND id=b.id)) owner,

2133 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2134 FROM blog b

2135 JOIN

2136 (SELECT distinct blogid, a.id aid, max(val) ctime

2137 FROM article a, article_s s

2138 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2139 GROUP BY blogid ORDER BY val DESC LIMIT 50

2140 ) m

2141 ON b.id=m.blogid;"

2142 # This should be as follows

2143 : <<EOF

2144 WITH arts AS(

2145 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2146 a.blogid, a.id id, s.val ctime

2147 FROM article a NATURAL JOIN article_s s

2148 WHERE s.key = 'ctime' AND a.author='$user'

2149 GROUP by s.id

2150 )

2151 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2152 FROM arts a0

2153 JOIN

2154 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2155 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2156 ORDER BY ctime DESC LIMIT 50;

2157 EOF

2158

2159 cat<<-EOF

2160 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2161 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2162 <div class="lcto">

2163 `DT_VIEW=replyblog dumptable html blog`

2164 </div>

2165 EOF

2166 unset DT_SQL

2167 if [ x"$user" = x"$uname" ]; then

2168 # Display NEWS

2169 # 2016-06-26

2170 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2171 acclog blog $blogreadflagrowid

2172 # echo "全部既読にしました" | html p

2173 fi

2174 # 2016-02-19 Counting NEWS without using dumptable.

2175 sql=`listnewblogsql "$user"`

2176 # echo "$sql" > tmp/listnew

2177 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2178 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2179 cont=$((cont-1))

2180 err newcount=$cont

2181 if [ $cont -gt 0 ]; then

2182 #echo "全体の新着記事${cont}傑" | html h2

2183 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2184 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2185 cat<<-EOF | html form 'action="?home"'

2186 `cgi_checkbox readchk yes 'id="read"'`<label

2187 for="read">新着ふくめて全部読んだことにする</label>

2188 `cgi_submit '確定'`

2189 `cgi_hidden read yes`

2190 EOF

2191 echo "$new10 "

2192 echo "</div>"

2193 else # If news is 0, set log cut off flag

2194 acclog blog $blogcutoffflagrowid # for speed

2195 fi

2196 else # Not My Home ($user != $uname)

2197 : # DT_SQL=

2198 fi

2199 ) > $hometail & # Is background call safe to m4??

2200 #

2201 listgroupbytable $formdir/grp.def $cond |

2202 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2203 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2204 -D_PROFIMG_="spaste(\`$imf')" \

2205 -D_BLOGS_="spaste(\`$bf')" \

2206 -D_SEARCH_="spaste(\`$sf')" \

2207 -D_NBLOG_="$nblog" \

2208 -D_GROUPS_="syscmd(\`cat')" \

2209 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2210 $layout/html.m4.html $layout/home.m4.html

2211

2212 # Record access log

2213 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2214 }

2215 commission() { # $1=grp-rowid $2=user-rowid

2216 contenttype; echo

2217 ## err commission: "$@"

2218 gname=`getgroupbyid $1`

2219 echo "グループ $gname 管理者委任" \

2220 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2221 if [ -n "$2" ]; then

2222 grp_reg_adm "$@"

2223 else

2224 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2225 fi

2226 }

2227 listgroupbytable() {

2228 # $1=deffile $2...=condition

2229 tagline=`grep :tag: $1`; shift

2230 and="${1:+and }" where=${1:+where }

2231 href="<a href=\"$myname?grp+"

2232 echo '<div class="listgroup">'

2233 NGsql="select distinct tag from\

2234 (select gname, max(case key when 'tag' then val end) as tag, \

2235 max(case key when 'ctime' then val end) as ctime\

2236 from grp_s group by gname order by ctime);"

2237 sql="select val from grp_s where key='tag' $and$* group by val;"

2238 ## err ListGRP: query sql="$sql"

2239 for tag in `query "$sql"`

2240 do

2241 ## err ListGrp: tag=$tag

2242 tn=${tagline%%=${tag}*}

2243 tn=${tn##*[ :]}

2244 sql="select rowid||':'||gname as 'グループ名',説明 from

2245 (select (select rowid from grp g where g.gname=grp_s.gname)

2246 as rowid,

2247 gname,

2248 max(case key when 'gecos' then val end) as '説明',

2249 max(case key when 'tag' then val end) as 'tag',

2250 max(case key when 'mtime' then val end) as mtime from grp_s

2251 $where$* group by gname having tag='$tag' order by mtime desc);"

2252 ## err PersonalGroupList= `echo $sql`

2253 echo "<h2>$tn</h2>"

2254 echo '<table class="b listgroup">'

2255 sq -header -html $db "$sql" \

2256 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2257 echo '</table>'

2258 done

2259 echo '</div>'

2260 }

2261 iconhref() (

2262 # $1=icon-file, $2=Href $3=title $4...=anchor

2263 data=`percenthex "$1"`

2264 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2265 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2266 href=$2; title=$3; shift 3

2267 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2268 )

2269 iconhref2() (

2270 # $1=icon-file, $2=Href $3=title $4...=anchor

2271 src=$1

2272 href=$2; title=$3; shift 3

2273 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2274 )

2275 listentry() (

2276 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2277 # Referring variable $iamowner=$grp to attach owner-request links

2278 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2279 cond='' hiddens=''

2280 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2281 if [ -z "$offset" ]; then

2282 offset=`getpar start`; offset=${offset%%[!0-9]*}

2283 offset=$((offset-1))

2284 fi

2285 offset=$((offset + 0)) # change to numeric forcibly

2286 [ $offset -lt 0 ] && offset=0

2287 limit=$listentlimit

2288 dir=`getcachedir "$1"`

2289 if [ x"$1" = x"user" ]; then

2290 hrb="$myname?home"

2291 deficon=person-default.png

2292 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2293 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2294 gcs=gecos

2295 else # if group

2296 hrb="$myname?grp"

2297 deficon=group-default.png

2298 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2299 gcs=name

2300 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2301 if [ -n "$tagline" ]; then

2302 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2303 ## err tagconv=$tagconv

2304 fi

2305 fi

2306 if [ ! -d $dir ]; then

2307 mkdir -p $dir

2308 fi

2309 if [ ! -s $dir/$deficon ]; then

2310 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2311 fi

2312 if [ -n "$2" ]; then

2313 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2314 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2315 fi

2316 tag=`getpar tag` tag2=`getpar tag2`

2317 if [ x"$tag" = x"NULL" ]; then

2318 tag="" tag2=""

2319 fi

2320 if [ -n "$tag$tag2" ]; then

2321 tag=${tag:-$tag2}

2322 qtag=`sqlquote "$tag"`

2323 cond2="tag=$qtag"

2324 fi

2325 if [ -n "$cond1$cond2" ]; then

2326 cond="$cond1${cond2:+ AND $cond2}"

2327 cond="WHERE ${cond# AND }"

2328 fi

2329

2330 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2331 # grpは呼出し元の動的スコープ変数でよくないな...

2332 ##qgrp=`sqlquote $grp`

2333 getgrp="(select gname from grp where rowid=${rowid:--1})"

2334 sql="select a.rowid, a.$link,

2335 coalesce(b.$gcs, a.$nm) as nick,

2336 quote(a.$nm) as qname,

2337 (SELECT val FROM ${tbl}_s

2338 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2339 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2340 || case when a.$nm in (select gname from grp)

2341 then printf('(%d名)',

2342 (select count(user) from grp_mem where gname=a.$nm))

2343 else ' <'||a.$nm||'>'

2344 end

2345 as name,

2346 b.tag,

2347 case when a.$nm in (select user from grp_adm

2348 where gname=$getgrp) then '管理者'

2349 when '$user' in (select user from grp_adm where gname=a.$nm)

2350 then 'ADMIN'

2351 when '$user' in (select user from grp_mem where gname=a.$nm)

2352 then 'Member'

2353 when '$iamowner' = '' then ''

2354 else ',not='||a.rowid end as ownerlink,

2355 CASE '$entity'

2356 WHEN 'グループ'

2357 THEN coalesce(

2358 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2359 'open')

2360 ||

2361 CASE WHEN '$user'

2362 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2363 THEN ' ismember'

2364 ELSE ''

2365 END

2366 ELSE 'user'

2367 END regmode

2368 from $tbl a left join

2369 (select $nm as name,

2370 max(case key when 'gecos' then val end) as gecos,

2371 max(case key when 'tag' then val end) as tag,

2372 max(case key when 'mtime' then val end) as mtime,

2373 max(case key when 'wtime' then val end) as wtime,

2374 max(case key when 'login' then val end) as login

2375 from ${tbl}_s group by $nm)

2376 b on a.$nm=b.name $cond $3

2377 order by b.wtime desc, b.login desc,

2378 b.mtime desc, b.tag desc, a.rowid asc"

2379 # Give precedence to newer maintained groups (2016-09-24)

2380 # Note that mtime is stored only in grp_s.

2381 ## err LE:sql.1="$sql"

2382 total=`query "with x as ($sql) select count(*) from x;"`

2383 echo "${entity} 一覧" | html h2

2384 echo '<div class="listentry">' # List-entry div

2385 # Show owner/member filter button

2386 METHOD=GET

2387 hiddens="$hiddens

2388 `cgi_hidden kwd \"$kwd\"`

2389 `cgi_hidden stage \"$stage\"`"

2390 if [ x"$tbl" = x"grp" ]; then

2391 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2392 fh="<select name=\"tag\">$nl"

2393 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2394 for l in $args; do

2395 val=${l#*=} tname=${l%=*}

2396 if [ x"$val" = x"$tag" ]; then

2397 s=" selected"

2398 selectedtags="(種別[${tname}]のみ)"

2399 else

2400 s=""

2401 fi

2402 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2403 done

2404 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2405 cat<<-EOF

2406 <form action="$myname" method="$METHOD">

2407 </form>

2408 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2409 for="ismembtn">参加中以外隠す</label>

2410 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2411 for="isadmbtn">管理者参加以外隠す</label>

2412 EOF

2413 # limit=3

2414 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2415 fi

2416 if [ $total -gt $limit ]; then

2417 echo '<div>'

2418 METHOD=GET cgi_form $stage <<EOF

2419 $form

2420 <label>次の語を含む${entity}で検索:

2421 `cgi_text kwd "$kwd"`</label>

2422 $hiddens

2423 EOF

2424 echo '</div>'

2425 else

2426 echo $selectedtags | html p

2427 fi

2428 cat<<EOF

2429 <form action="$myname" method="$METHOD">

2430 <p>${total}件中の<input class="hidesub" type="text" name="start"

2431 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2432 <input type="submit" value="確定"></p>

2433 </form>

2434 EOF

2435 if [ $((offset+limit)) -lt $total ]; then

2436 nextbtn=$(

2437 cat<<EOF

2438 <div class="right clear"><form action="$myname" method="$METHOD">

2439 `cgi_submit 次の${limit}件`

2440 $hiddens

2441 `cgi_hidden offset $((offset + limit))`</form></div>

2442 EOF

2443 )

2444 fi

2445 if [ $offset -gt 0 ]; then

2446 prevbtn=$(

2447 cat<<EOF

2448 <form action="$myname" method="$METHOD">

2449 `cgi_submit 前の${limit}件`

2450 $hiddens

2451 `cgi_hidden offset $((offset - limit))`</form>

2452 EOF

2453 )

2454 fi

2455 pnbtn="$nextbtn$prevbtn"

2456 echo $pnbtn

2457

2458 ## err ListEntry: `echo "$sql"\;`

2459 # sq $db here??? 2016-11-28

2460 query "$sql limit $limit ${offset:+offset $offset};" \

2461 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2462 # err name=$name owner=$ownerp lnk=$lnk

2463 # err newlnk=$lnk regmode=$regmode

2464 icondir=$dir/$id

2465 # Pick up only last icon

2466 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2467 <p class=\"tag _$tag\">$tag</p>" \

2468 | _m4 $tagconv

2469 if [ -n "$NOSPEEDUP" ]; then

2470 files=`getvalbyid $tbl profimg $id $icondir`

2471 if [ -n "$files" ]; then

2472 icon=`echo "$files"|tail -1`

2473 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2474 else

2475 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2476 fi

2477 elif [ -n "$icon" -a -s "$icon" ]; then

2478 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2479 else

2480 cond="$nm=$qname"

2481 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2482 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2483 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2484 # err "img=[$img]"

2485 if [ -n "$img" ]; then

2486 echo "<a href=\"$hrb+$lnk\">"

2487 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2488 echo "</a>"

2489 else

2490 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2491 fi

2492 fi

2493 echo "<br>$name${ownerp:+<br>($ownerp)}"

2494 echo "</div>"

2495 done

2496 echo "</div>" # End of List-entry div

2497 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2498 )

2499 listmember() {

2500 listentry user "$@"

2501 }

2502 listgroup() {

2503 listentry group "$@"

2504 }

2505 hexteams() { # $1=gname, $2(optional)=user

2506 cond=${2:+" AND user='$2'"}

2507 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2508 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2509 }

2510 showgroup() { # $1=group-rowid

2511 if [ -z "$1" ]; then

2512 grid=`getpar grid`

2513 grid=${grid%%[!0-9]*}

2514 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2515 else

2516 grid=$1

2517 fi

2518 grp=`getgroupbyid $grid`

2519 qgrp=`sqlquote "$grp"`

2520 htmlgrp=`echo "$grp"|htmlescape`

2521 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2522 if isgroup "$grp"; then

2523 tf=$tmpd/title.$$

2524 sf=$tmpd/search.$$

2525 bodyclass=`query "SELECT val FROM grp_s

2526 WHERE gname=$qgrp AND key='regmode';"`

2527 if ismember "$user" "$grp"; then

2528 ismember="ismember"

2529 bodyclass="$bodyclass${bodyclass:+ }ismember"

2530 else

2531 ismember="" # bodyclass="group"

2532 fi

2533 bodyclass="$bodyclass grouphome"

2534 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2535 echo "グループ $htmlgrp" > $tf

2536

2537 showgroupsub $formdir/grp.def "$grid" | \

2538 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2539 -D_FORM_="syscmd(\`cat')" \

2540 -D_BODYCLASS_="$bodyclass" \

2541 -D_DUMPTABLE_="" \

2542 $htmlheader $sf $layout/form+dump.m4.html

2543 # $htmlheader $layout/form+dump.m4.html

2544 # $htmlheader is defined in grp()

2545 else # if $grp is removed at par2table

2546 listgroup

2547 fi

2548 }

2549 showgroupsub() {

2550 # $1=def-file $2=group-rowid

2551 # Using $ismember

2552 rowid=$2

2553 grp=`getgroupbyid $2`

2554 qgrp=`sqlquote "$grp"`

2555 td=`getcachedir grp/"$2"`

2556 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2557 if [ -z "$rowid" ]; then

2558 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2559 #grp=`sq $db "select gname from grp where rowid=$grp"`

2560 echo "showgroupsub: invalid argument($1 $2)" | html p

2561 return

2562 fi

2563 val=`getvalbyid grp profimg $rowid $tmpd`

2564 enticond="gname=$qgrp"

2565 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2566 if [ -n "$img" ]; then

2567 cat<<-EOF

2568 <p class="groupimg">

2569 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2570 EOF

2571 fi

2572 echo "<div class=\"noprofimg\">"

2573 viewtable $1 grp $rowid

2574 echo "</div>"

2575 if isgrpowner "$user" "$grp"; then

2576 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2577 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2578 iamowner=$rowid

2579 colmd=" mode"

2580 fi

2581 if [ -n "$ismember" ]; then

2582 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2583 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2584 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2585 cat<<-EOF

2586 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2587 href="?blog+$rowid">グループの新規話題作成</a>

2588 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2589 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2590 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2591 <div class="fold clear">

2592 `cgi_checkbox send yes id="send"`<label

2593 for="send">グループ全員にメッセージ送信</label>

2594 <div>

2595 `cgi_textarea message "" "cols=60"`

2596 `cgi_submit 送信`

2597 `cgi_reset リセット`

2598 </div>

2599 `cgi_hidden grp $rowid`

2600 </div></form>

2601 EOF

2602 fi

2603 # 加入ボタン + 加入者リスト

2604 if [ -n "$ismember" ]; then

2605 ismem='checked' state="(参加中)"

2606 else

2607 nomem='checked' state="(現在非加入)"

2608 fi

2609 # このグループでの加入アドレス

2610 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2611 and key='email';"`

2612 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2613 ## and key='email';"

2614 ##err email=$eml

2615 cat <<EOF

2616 <div class="fold clear">

2617 `cgi_checkbox reg yes id="reg"`<label

2618 for="reg">自身の加入状態を操作する</label>$state

2619 <div>

2620 EOF

2621 cgi_form grp <<EOF

2622 <p>このグループに</p>

2623 <table class="b">

2624 <tr><th>メンバーとして</th><td>

2625 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2626 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2627 <tr><th>参加する場合のメイルアドレス<br>

2628 <small>(メインのアドレスとは違うものにする場合に記入<br>

2629 同じでよい場合は空欄に)</small></th>

2630 <td>`cgi_text email $eml`</td></tr>

2631 </table>

2632 `cgi_hidden grp $rowid`

2633 EOF

2634 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2635 echo "moderated (承認加入の)グループなので実際に参加できるのは

2636 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2637 fi

2638 echo '</div></div>'

2639 echo '<h2>話題一覧</h2>'

2640 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2641 cat<<-EOF

2642 <form class="summary" action="$myname" title="$thelp">

2643 `cgi_hidden owner "$grp"`

2644 `cgi_hidden kwd "@week"`

2645 `cgi_hidden stage searchart`

2646 `cgi_submit "一週間のまとめ"`

2647 </form>

2648 EOF

2649 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2650 colstate="state:稼動状態:frozen=rowclass=凍結"

2651 DT_CHLD=article:blogid \

2652 DT_QOWNER="$qgrp" \

2653 DT_VIEW=replyblog dumptable html blog \

2654 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2655

2656 getgname="(select gname from grp where rowid=$rowid)"

2657 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2658 cm="?commission+$rowid"

2659 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2660 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2661 # team list

2662 hexteams=`hexteams "$grp"`

2663 if [ -n "$hexteams" ]; then

2664 echo "チーム一覧" | html h2

2665 echo '<div class="dumptable"><table class="b">'

2666 sq $db -html -header<<-EOF

2667 SELECT val TEAM,

2668 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2669 MEMBERS

2670 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2671 EOF

2672 echo '</table></div>'

2673 fi

2674 }

2675 grp_getbodyclass() {

2676 # Get css class name for document.

2677 # `moderated' for moderated groups

2678 # `ismember' for groups where user belongs

2679 # $1=GroupName (w/o quote)

2680 # $user=userNameCurrentlyLogin

2681 ## err grp_getbodyclass: 1="$1"

2682 qgrp=`sqlquote "$1"`

2683 query<<-EOF

2684 SELECT coalesce(

2685 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2686 'open')

2687 ||

2688 CASE WHEN '$user'

2689 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2690 THEN ' ismember'

2691 ELSE ''

2692 END;

2693 EOF

2694 }

2695 grpaction() { # $1=group-rowid

2696 err GRP_ACTION:IN

2697 grid=${1:-`getpar grp`}

2698 grp=`getgroupbyid "$grid"`

2699 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2700 if [ -z "$grp" ]; then

2701 echo "無効な指定です。" | html p; return

2702 fi

2703 if ! ismember $user "$grp"; then

2704 echo "加入者のみに許可された操作です。" | html p; return

2705 fi

2706 echo "グループ $grp 個別選択操作" \

2707 | _m4 -D_TITLE_="syscmd(\`cat')" \

2708 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2709 $layout/html.m4.html

2710

2711 isowner=""

2712 isgrpowner "$user" "$grp" && isowner="yes"

2713 usel=`getpar usel`

2714 if [ -n "$usel" ]; then

2715 uids=$(echo `echo $usel`|tr ' ' ',')

2716 ## err grpaction-1: grp=$grp, `echo $sql`

2717 text=`getpar text|tr -d '\r'`

2718

2719 rm=`getpar rm` cfm=`getpar confirm`

2720 ## err rm=$rm cfm=$cfm

2721 if [ x"$rm" = x"yes" ]; then

2722 if [ "$isowner" ]; then

2723 if [ x"$rm$cfm" = x"yesyes" ]; then

2724 # Eliminate

2725 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2726 for tbl in grp_mem grp_mem_s grp_mem_m; do

2727 sql="delete from $tbl $cond;"

2728 # echo "sql=$sql"

2729 query "$sql"

2730 err rmGRPuser "$sql"

2731 done

2732 num=`query "select count(*) from user where rowid in ($uids);"`

2733 #err num=$num

2734 if [ 0$num -gt 0 ]; then

2735 sql="select coalesce(b.val,a.name) from user a left join \

2736 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2737 # err `echo "$sql"`

2738 html pre<<EOF

2739 以下の${num}名のグループ $grp 登録を解除しました。

2740 `query "$sql"`

2741 EOF

2742 fi

2743 else

2744 echo "確認のチェックがないのでやめておきます。" | html p

2745 return

2746 fi

2747 else # not Group Owner

2748 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2749 return

2750 fi

2751 cat<<EOF

2752

2753 EOF

2754 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2755 if [ -z "$text" ]; then # if msg is empty

2756 echo "なにかメッセージを..." | html p

2757 return 0

2758 fi

2759 gecos=`gecos $user`

2760 safegc=`echo "$gecos" | tr -d '<>@'`

2761 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2762 fromad=`myemail4group "$grp"`

2763 ###mail_from="$safegc <$fromad>"

2764 mail_from="$safegc <$user>" # TEST: 2020/5/13

2765 test -n `getpar sender` &&

2766 export SENDER=$user # TEST: 2020/5/15

2767 replyto=$fromad

2768

2769 ## Start parse of attachment files

2770 if [ -n "`getpar email`" ]; then

2771 ar=`getpar supprcpt`

2772 if [ -n "$ar" ]; then

2773 for a in $ar; do

2774 if checkdomain "$a"; then

2775 supprcpt="$supprcpt $a"

2776 else

2777 err "SupprcptErr=[$a] by $user"

2778 removercpt="$removercpt $a"

2779 fi

2780 done

2781 fi

2782 subj=`getpar subject`

2783 afiles=""

2784 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2785 do

2786 f=$tmpd/$fn

2787 if [ -s $f ]; then

2788 afiles=$afiles"${afiles:+ }$f"

2789 fi

2790 done

2791 else

2792 preface=$(cat <<-EOF

2793 $url

2794 のグループ「$grp」のメンバーである $gecos さんから、

2795 あなた宛のメッセージです。

2796 ----------------------------------------------------------

2797 EOF

2798 )

2799 fi

2800 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2801 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2802 subj="${subj:-$gecos さんからのメッセージ}"

2803 REPLYTO=$replyto

2804 MAIL_FROM=$mail_from

2805 export REPLYTO SMAIL_TO MAIL_FROM

2806 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2807 for r in $rcpts; do

2808 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2809 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2810 else

2811 # Show sender and rcpts address for guest

2812 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2813 fi

2814 if [ -n "$afiles" ];then

2815 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2816 else

2817 smail "$r" "$subj"

2818 fi <<EOF

2819 ${preface:+$preface$nl}$text

2820 EOF

2821 done

2822 if [ $? = 0 ]; then

2823 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2824 sql="select coalesce(b.val, a.name) from

2825 (select name from user where rowid in ($uids)) a

2826 left join user_s b on a.name=b.name and b.key='gecos';"

2827 html pre<<EOF

2828 `query "$sql"`

2829 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2830 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2831 EOF

2832 err SendDone: `echo $sql`

2833 fi

2834 elif [ x"$rm" = x"commission" ]; then

2835 grp_reg_adm $grid $usel

2836 elif [ x"$rm" = x"addteam" ]; then

2837 team=`getpar team|sed "s/'/''/g"` # for single quotation

2838 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2839 if [ x"$team" != x"$newteam" ]; then

2840 echo "チーム名に使えない文字を除去しました" | html p

2841 team=$newteam

2842 fi

2843 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2844 cat<<-EOF | html p

2845 有効なチーム名を入力してください。

2846 カンマだけ、「なし」という名前は使えません。

2847 EOF

2848 echo "有効なチーム名を入力してください。" | html p

2849 else

2850 grp_add_team $grid "$team" $usel

2851 fi

2852 elif [ x"$rm" = x"rmteam" ]; then

2853 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2854 rmteam=`getpar rmteam|sed "s/'/''/g"`

2855 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2856 gname='$grp' AND user='$user' AND key='team'\

2857 AND val='$rmteam';\"`" ]; then

2858 grp_rm_team $grid "$rmteam" $usel

2859 else

2860 echo "所属していないチームの除去操作はできません。"|html p

2861 fi

2862 else

2863 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2864 fi

2865 fi

2866 fi

2867 # POST count summary

2868 from=`getpar from`; to=`getpar to`

2869 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2870 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2871 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2872 # New entry

2873 sql="WITH mems AS (

2874 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2875 ON gm.user=g.name

2876 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2877 ), target_article AS (

2878 SELECT id FROM article_s

2879 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2880 ), posts AS (

2881 SELECT author, count(author) post

2882 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2883 WHERE blogid IN (SELECT id FROM blog_s

2884 WHERE key='owner'

2885 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2886 AND key='text'

2887 GROUP BY author

2888 ), teams AS (

2889 SELECT user, group_concat(val, ', ') team

2890 FROM grp_mem_m

2891 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2892 AND key='team'

2893 GROUP BY user

2894 ), user_post AS (

2895 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2896 FROM mems m LEFT JOIN posts

2897 ON m.name=posts.author

2898 GROUP by m.rowid

2899 )

2900 SELECT

2901 CASE

2902 WHEN (SELECT user FROM grp_adm

2903 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2904 AND user=up.name) IS NOT NULL

2905 then 'k'

2906 ELSE ''

2907 END || rowid || ',' || gecos NAME,

2908 post POST, team _TEAM_

2909 FROM user_post up LEFT JOIN teams t

2910 ON up.name=t.user

2911 ORDER BY gecos;"

2912 ## err grpaction: "`echo \"$sql\"`"

2913 tf=$tmpd/title.$$

2914 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2915 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2916 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2917 for=\"cmadmin\">管理者委任</label>

2918 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2919 </p></div>"

2920 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2921 title=\"Shortcut: g${nl}Remove from the Group\"

2922 for=\"conf\">GRP登録解除</label>

2923 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2924 <p>この操作による通知は本人に行きません。

2925 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2926 </div>"

2927 # Get team list to which current user belongs into $hexteams

2928 allhexteams=$(hexteams "$grp")

2929 if [ -n "$isowner" ]; then

2930 myhexteams="$allhexteams" # admin can remove all teams' attr

2931 else

2932 myhexteams=$(hexteams "$grp" "$user")

2933 fi

2934 if [ -n "$myhexteams" ]; then

2935 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2936 title=\"Shortcut: s${nl}Strip a team tag from\"

2937 for=\"cmrmteam\">チーム属性除去</label>

2938 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2939 を除去します: `cgi_checkbox teamconfirm yes` 確認

2940 <p>この操作による通知は本人に行きません。

2941 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2942 </div>

2943 "

2944 fi

2945 stf=$tmpd/selteam.$$

2946 cgi_select_h selteam "5445414d" $allhexteams > $stf

2947 b1='<label> <input type="checkbox" name="usel" value="'

2948 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2949 br='<span id="reverse" title="Reverse Selection"></span>'

2950 #b2='"> <span>' b3='</span></label>'

2951 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2952 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2953 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2954 cgi_form grpaction<<EOF \

2955 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2956 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2957 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2958 | _m4 -D_TITLE_="spaste(\`$tf')" \

2959 -D_SUBTITLE_="チェック後操作ボタン" \

2960 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2961 $layout/form+dump.m4.html \

2962 | _m4 -D_TEAM_="spaste(\`$stf')"

2963 <p>下でチェックした人を対象として:</p>

2964 <div class="foldtabs">

2965 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2966 title="Shortcut: a${nl}Add a team tag to"

2967 for="cmteam">同じチーム属性を付与</label>

2968 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2969 `cgi_datalist_h teams $allhexteams`

2970 </div>

2971 ${rmteammsg}

2972 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2973 title="Shortcut: d${nl}DirectMail to"

2974 for="sendmsg" title="Direct Message">DM送信</label>

2975 <div>

2976 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2977 title="Using email format">email書式を使う</label>

2978 <div class="folded">

2979 <table>

2980 <tr><td>From: </td><td>$user</td></tr>

2981 <tr><td>このFrom:で送る</td>

2982 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2983 </td></tr>

2984 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2985 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2986 <tr><td>ファイル添付: </td>

2987 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2988 </table>

2989 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2990 </div>

2991 <div>本文:`cgi_textarea text "" cols=72`

2992 </div>

2993 </div>

2994 ${isowner:+$cmmsg$excmsg}

2995 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2996 </div>

2997 <h4>$grp 参加者一覧</h4>$fromtonote

2998 <table class="td2r thl">

2999 `sq $db -header -html "$sql"`

3000 </table>

3001 `cgi_hidden grp $grid`

3002 `cgi_hidden myuid $myuid id="myuid"`

3003 EOF

3004 }

3005 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

3006 # Create TEMPORARY VIEW

3007 query<<EOF

3008 CREATE TEMPORARY VIEW writeusers AS

3009 SELECT DISTINCT author FROM article

3010 WHERE id in (

3011 select id from article where blogid=(select id from blog where rowid=$1)

3012 );

3013 CREATE TEMPORARY VIEW movablegroups AS

3014 SELECT g.rowid growid , g.gname

3015 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3016 ON grp.gname=gm.gname -- そのユーザが属している

3017 AND user='$user') g -- グループに絞る

3018 WHERE (SELECT author FROM writeusers

3019 EXCEPT

3020 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3021 IS NULL;

3022 $2

3023 EOF

3024 }

3025 sql4readableblogs() {

3026 # Create view of blogs that can be readable to $user

3027 # Blog is readable when:

3028 # 1: blog owner is an user

3029 # 2: else, 2.1: owner-group where the $user belongs

3030 # 2.2: else, owner-group is not moderated

3031 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3032 cat<<EOF ## | tee tmp/sql.out

3033 CREATE TEMPORARY VIEW readableblogs AS

3034 SELECT blog.rowid rid, id, author

3035 FROM blog

3036 NATURAL JOIN

3037 (SELECT id,

3038 max(CASE key WHEN 'owner' THEN val END) owner,

3039 max(CASE key WHEN 'mode' THEN val END) mode

3040 FROM blog_s GROUP by id) bs

3041 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3042 THEN 1 -- blog owner is an user, READABLE

3043 WHEN (SELECT val FROM grp_s

3044 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3045 AND

3046 (SELECT user FROM grp_mem

3047 WHERE gname=bs.owner AND user='$user') IS NULL

3048 THEN 0

3049 WHEN mode IN ('quiz', 'enquete')

3050 THEN 0 -- "quiz" mode blog cannot be searched

3051 ELSE 1

3052 END;

3053 EOF

3054 }

3055 editheading() { # $1=rowid-of-heading

3056 rowid=${1%%[!A-Z0-9a-z_]*}

3057 if [ -z "$rowid" ]; then

3058 echo "話題番号が未指定です。" | html p

3059 return

3060 fi

3061 owner=`getvalbyid blog owner $rowid`

3062 title=`getvalbyid blog title $rowid`

3063 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3064 | _m4 -D_TITLE_="修正" \

3065 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3066 -D_BLOGS_="" -D_DUMPTABLE_="" \

3067 -D_FORM_="syscmd(\`cat')" \

3068 $layout/html.m4.html $layout/form+dump.m4.html

3069 # Move to group

3070 if isuser "$owner"; then

3071 crview4article $rowid

3072 n=`query "SELECT count(*) FROM writeusers;"`

3073 ## err N=$n

3074 if [ $((n)) -gt 0 ]; then

3075 ## err ROWID=$rowid

3076 sql="SELECT growid || ':' || gname FROM movablegroups;"

3077 cat<<-EOF

3078 <div class="fold">

3079 `cgi_checkbox mv send id="mv"`<label

3080 for="mv">この話題をグループ所有に移動する</label>

3081 <div>

3082 <form action="?mvart" method="POST" enctype="multipart/form-data">

3083 移動先グループ:

3084 <select name="mv2grp">

3085 EOF

3086 query ".mode html"

3087 query<<-EOF |

3088 $sql

3089 .mode list

3090 EOF

3091 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3092 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3093 cat<<-EOF

3094 </select>

3095 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3096 そのグループに加入しているものに限られます)</p>

3097 <p>`cgi_checkbox cfm yes`<label>確認

3098 (この操作は元に戻すことができません)</label></p>

3099 `cgi_hidden blogrowid $rowid`

3100 `cgi_submit 移動`

3101 `cgi_reset Reset`

3102 </form>

3103 </div>

3104 </div>

3105 EOF

3106 fi

3107 # end of isuser "$owner"

3108 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3109 [ -n "$hexteams" ];}; then

3110 none="`echo なし|hexize`"

3111 cat<<-EOF

3112 <div class="fold">

3113 `cgi_checkbox mv2team send id="mv2team"`<label

3114 for="mv2team">この話題を以下のチームのものにする</label>

3115 <div><p>現在の所属チーム設定:

3116 `query "SELECT

3117 coalesce((SELECT val FROM blog_s

3118 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3119 AND key='team'),

3120 ':なし');"`</p>

3121 <form action="?mvart" method="POST" enctype="multipart/form-data">

3122 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3123 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3124 `cgi_hidden blogrowid $rowid`<br>

3125 `cgi_submit 移動`

3126 `cgi_reset Reset`

3127 </form></div></div>

3128 EOF

3129 fi

3130 }

3131 mvart() { # move diary to some group or team

3132 # or move blog of group to team which belong to the group

3133 blogrowid=`getpar blogrowid`

3134 cfm=`getpar cfm`

3135 ##### echo move blog:$blogrowid to $mv2grp | html p

3136 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3137 . ./s4-blog.sh

3138 if [ -z "$blogrowid" ]; then

3139 echo "無効な指定です(mvart)。" | html p

3140 return

3141 elif [ x"$cfm" != x"yes" ]; then

3142 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3143 elif { mv2grp=`getpar mv2grp`

3144 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3145 [ -n "$mv2grp" ]; }; then

3146 crview4article $blogrowid

3147 ########## TRANSACTION BEGIN

3148 query "BEGIN;"

3149 n=`query "SELECT count(*) FROM writeusers;"`

3150 ## err Nwriteuser=$n

3151 if [ $((n)) -gt 0 ]; then

3152 query<<-EOF

3153 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3154 WHERE key='owner'

3155 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3156 AND $mv2grp IN (SELECT growid FROM movablegroups);

3157 EOF

3158 fi

3159 query "END;"

3160 ########## TRANSACTION END

3161 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3162 [ -n "$mv2team" ];}; then

3163 # blog owner can move it to ANY team

3164 case "$mv2team" in

3165 'なし')

3166 cat<<-EOF

3167 DELETE FROM blog_s

3168 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3169 AND key='team';

3170 EOF

3171 ;;

3172 "") ;;

3173 *)cat<<-EOF

3174 BEGIN;

3175 REPLACE INTO blog_s(id, key, val)

3176 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3177 'team', '$mv2team');

3178 REPLACE INTO blog_s(id, key, val)

3179 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3180 'notify', 'all'); -- Change notify to all

3181 END;

3182 EOF

3183 esac | query

3184 fi

3185 blog_reply $blogrowid

3186 echo yes | html p

3187 }

3188 editart() { # $1=article-rowid $2=blogrowid

3189 rowid=${1%%[!A-Z0-9a-z_]*}

3190 blogrowid=${2%%[!A-Z0-9a-z_]*}

3191 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3192 echo "表示する記事番号が未指定です。" | html p

3193 return

3194 fi

3195 owner=`getvalbyid blog owner $blogrowid`

3196 title=`getvalbyid blog title $blogrowid`

3197 author=`getvalbyid article author $rowid`

3198 ## err EDITart: owner=$owner, author=$author

3199 if isgrpowner "$user" "$owner"; then

3200 : EDIT OK

3201 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3202 echo "本人か所有者しか編集できません." | html p

3203 return

3204 fi

3205 aid=`query "select id from article where rowid=$rowid;"`

3206 tmpout=$tmpd/editart.$$.out

3207 GF_ACTION="?replyblog+$blogrowid#$aid" \

3208 edittable $formdir/article.def article $rowid \

3209 > $tmpout

3210 rm -f /tmp/editart.out

3211 # Cannot use pipelining to m4 with genform() because of stdin stack

3212 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3213 -D_FORM_="syscmd(cat $tmpout)" \

3214 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3215 -D_BLOGS_= -D_DUMPTABLE_= \

3216 $layout/html.m4.html $layout/form+dump.m4.html

3217 }

3218 send2mem() {

3219 rowid=`getpar grp`

3220 rowid=${rowid%%[!0-9]*} # Cleaning

3221 if [ -z "$rowid" ]; then

3222 echo "グループが未指定です。" | html p

3223 return

3224 fi

3225 message=`getpar message`

3226 if [ -z "$message" ]; then

3227 echo "文章を入れてください。" | html p

3228 return

3229 fi

3230 grp=`getgroupbyid $rowid`

3231 members=`collectemail $grp`

3232 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3233 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3234 mailfrom="`gecos "$user"` <$mailfrom>"

3235 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3236 msg=$(cat<<-EOF

3237 $urlbase?grp+$rowid

3238 グループ $grp に所属する

3239 `gecos $user` さんよりメッセージ:

3240

3241 $message

3242 EOF

3243 )

3244 # smail rcpt subj (file)

3245 for m in $members; do

3246 echo "$msg" |

3247 MAIL_FROM=$mailfrom \

3248 SENDER=$noreply \

3249 REPLYTO=$mailfrom \

3250 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3251 smail "$m" "$sj"

3252 done

3253 cat<<EOF

3254 <p>以下のユーザに送信しました。</p>

3255 <pre>

3256 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3257 </pre>

3258 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3259 EOF

3260 }

3261 joingrpadmit() {

3262 # $1=yes/no $2=session-key

3263 if [ -z "$2" ]; then

3264 echo "bye bye" | html p; return

3265 fi

3266 t_usr=`session=$2 getpar user`

3267 t_grp=`session=$2 getpar group`

3268 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3269 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3270 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3271 echo "無効な加入依頼です。" | html p

3272 echo "有効期限が切れたか、

3273 他の管理者がいる場合は処理済みの可能性があります。" | html p

3274 return

3275 fi

3276 if ! isgrpowner "$user" "$t_grp"; then

3277 echo "グループ管理者のみの機能です。" | html p; return

3278 fi

3279 case $1 in

3280 yes) joingrp "$t_grp" "$t_usr" yes ;;

3281 no) joingrp "$t_grp" "$t_usr" no ;;

3282 *)

3283 echo "無効な指定です($1)。" | html p

3284 return ;;

3285 esac

3286 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3287 rcpts="`getgroupadminmails $t_grp` $user"

3288 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3289 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3290 に

3291 $t_usr

3292 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3293 しました。"

3294 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3295 query "delete from session where id='$2';"

3296 echo "$body" | html p

3297 }

3298

3299 joingrprequest() {

3300 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3301 jss="joingrp-`date +%s`-`genrandom 12`"

3302 addsession $jss +${memoplimitdays}days

3303 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3304 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3305 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3306 $url

3307 $user さんから

3308 グループ $1

3309 に加入依頼がありました。

3310

3311 承認する:

3312 $urlbase?joingrpadmit+yes+$jss

3313

3314 白紙に戻す:

3315 $urlbase?joingrpadmit+no+$jss

3316 EOF

3317 echo "管理者に加入依頼を出しました。

3318 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3319 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3320 }

3321 joingrp() {

3322 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3323 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3324 if isgrpowner "$user" "$1"; then

3325 isowner="yes"

3326 elif [ -n "$5" ]; then

3327 isowner="yes"

3328 else

3329 isowner=""

3330 fi

3331 ## err jg:isgrpowner: isowner="$isowner"

3332 if [ -n "$isowner" ]; then

3333 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3334 elif [ x"$2" != x"$user" ]; then # if user is not login user

3335 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3336 return

3337 elif [ x"$3" = x"no" ]; then

3338 : # Do not pursue those who leave

3339 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3340 : # Member can change own email address for the joining moderated group

3341 else # adding user is $user itself

3342 case `getgroupattr $1 regmode` in

3343 moderated)

3344 joingrprequest "$@" # Request only

3345 return

3346 ;;

3347 *)

3348 ;;

3349 esac

3350 fi

3351 qgname=`sqlquote "$1"`

3352 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3353 cond="where gname=$qgname and user='$2'"

3354 if [ x"$3" = x"yes" ]; then

3355 query "replace into grp_mem values($qgname, '$2');"

3356 # Notify joingrp to admin

3357 action="に加入しました。"

3358 if [ -n "$4" ]; then

3359 if msg=`emaildomaincheck "$4"`; then

3360 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3361 'string', '$4', NULL);"

3362 else

3363 echo $msg

3364 fi

3365 else

3366 query "delete from grp_mem_s $cond and key='email';"

3367 fi

3368 if [ -n "$5" ]; then # as ADMIN

3369 # Coming here means newly created group

3370 sql="select case\

3371 when (select count(*) from grp_mem where gname=$qgname)=1\

3372 then (select user from grp_mem\

3373 where gname=$qgname and user='$user')\

3374 else '' end; "

3375 err NewGrpChk: $sql

3376 if [ -n "`query \"$sql\"`" ]; then

3377 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3378 query "replace into grp_adm values($qgname, '$user');"

3379 fi

3380 fi

3381 else

3382 query "begin;

3383 delete from grp_mem $cond;

3384 delete from grp_mem_s $cond;

3385 delete from grp_mem_m $cond;

3386 delete from grp_adm $cond;

3387 delete from grp_adm_s $cond;

3388 delete from grp_adm_m $cond;

3389 end;"

3390 action="から脱退しました。"

3391 fi

3392 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3393 $url?grp+$grid

3394 $user (`gecos $user`)さんが

3395 グループ $1

3396 $action

3397 EOF

3398 }

3399 grp_add_team() (

3400 # $1=grp-rowid $2=team $3...=user-rowid(s)

3401 grp=`getgroupbyid $1`

3402 team=$2; shift; shift

3403 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3404 { echo "BEGIN;"

3405 for user; do

3406 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3407 '$grp',\

3408 (SELECT name FROM user WHERE rowid=$user),\

3409 'team', 'string', '$team');"

3410 done

3411 echo "END;"

3412 } | query

3413 )

3414 grp_rm_team() (

3415 # $1=grp-rowid $2=team $3...=user-rowid(s)

3416 grid=$1

3417 qgrp=$(sqlquote "`getgroupbyid $grid`")

3418 team=$2; shift; shift

3419 [ -z "$grid" -o -z "$team" ] && return

3420 { echo "BEGIN;"

3421 for user; do

3422 echo "DELETE FROM grp_mem_m\

3423 WHERE gname=$qgrp \

3424 AND user=(SELECT name FROM user WHERE rowid=$user)\

3425 AND key='team' AND val='$team';"

3426 done

3427 cat<<-EOF

3428 DELETE FROM blog_s

3429 WHERE rowid=(

3430 SELECT rowid

3431 FROM blog_s a

3432 WHERE key='team'

3433 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3434 AND NOT EXISTS (SELECT * FROM grp_mem_m

3435 WHERE key='team' AND val=a.val -- a.val=team

3436 AND gname = (SELECT val FROM blog_s b

3437 WHERE a.id=b.id AND key='owner')

3438 ));

3439 EOF

3440

3441 echo "END;"

3442 } | query

3443 )

3444 grp_reg_adm() {

3445 # $1=grp-rowid $2...=user-rowid

3446 grid=$1

3447 grp=`getgroupbyid "$1"`

3448 if [ -z "$grp" ]; then

3449 echo "無効なグループIDです" | html p; return

3450 fi

3451 if ! isgrpowner "$user" "$grp"; then

3452 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3453 fi

3454 shift

3455 for urid; do

3456 newadm=`query "select name from user where rowid=$urid;"`

3457 if [ -z "$newadm" ]; then

3458 echo "指定ユーザIDがおかしいようです。" | html p; return

3459 fi

3460 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3461 err ismember $newadm $grp

3462 if ismember $newadm "$grp"; then

3463 # OK, go ahead

3464 getgname="(select gname from grp where rowid=$grid)"

3465 query "replace into grp_adm values($getgname, '$newadm');"

3466 # confirm insertion

3467 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3468 if [ -n "`query \"$sql;\"`" ]; then

3469 echo "追加完了: $newadm" | html p

3470 else

3471 echo "追加失敗($1 $urid)" | html p

3472 fi

3473 fi

3474 showgroup $grid

3475 done

3476 }

3477 dt_rowhack() {

3478 # From: <TR>

3479 # ....

3480 # <TD>rowclass=foo</TD>

3481 # </TR>

3482 # To: <TR class="foo">....<TD>foo</TD></TR>

3483 sed -e '

3484 /^<TR>/ {

3485 :loop

3486 s/\n//

3487 N

3488 /<\/TR>/ {

3489 s/\n//

3490 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3491 n

3492 }

3493 $q

3494 b loop

3495 }'

3496 }

3497 dumptable() {

3498 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3499 # textのフィールドだけ全てダンプにしたほうがいいか

3500 # $DT_VIEW sets link

3501 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3502 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3503 VIEW=${DT_VIEW-replyblog}

3504 if [ -n "$VIEW" ]; then

3505 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3506 fi

3507 sqlfile=$tmpd/dump.sql

3508 : > $sqlfile # ensure to be empty

3509 printf '.mode html\n.header 1\n' > $sqlfile

3510 # $DT_CHLD=ChildTable:BindColumn

3511 if [ -n "$DT_CHLD" ]; then

3512 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3513 cat<<-EOF >> $sqlfile

3514 -- presql

3515 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3516 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3517 EOF

3518 # Speed up counting of new articles

3519 cat<<-EOF >> $sqlfile

3520 -- presql2

3521 DROP TABLE IF EXISTS _counts;

3522 CREATE TEMPORARY TABLE _counts AS

3523 SELECT $_i, count($_i) cnt

3524 FROM $_t GROUP BY $_i;

3525 /* Prepare NEW count table */

3526 CREATE TEMPORARY TABLE _target AS

3527 SELECT b.rowid trowid, b.id

3528 FROM "$2" b JOIN "$2_s" s

3529 ON b.id=s.id AND s.key='owner'

3530 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3531

3532 DROP TABLE IF EXISTS _children;

3533 CREATE TEMPORARY TABLE _children AS

3534 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3535 FROM (SELECT t.trowid, t.id $_i, a.id

3536 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3537 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3538

3539 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3540 DROP TABLE IF EXISTS _news;

3541 DROP VIEW IF EXISTS _news;

3542

3543 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3544 -- INSERT INTO _news

3545 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3546 CREATE TEMPORARY VIEW _news AS

3547 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3548 FROM (SELECT DISTINCT id FROM _target)

3549 a LEFT JOIN

3550 (SELECT $_i, count(ctime) newcnt

3551 FROM _children x

3552 WHERE ctime > coalesce((SELECT time from myacclog

3553 WHERE tblrowid=x.trowid),

3554 '1970-01-01')

3555 GROUP BY $_i) b

3556 ON a.id=b.$_i;

3557 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3558 EOF

3559 # REMOVE next line until 2019/5/1

3560 cntall="(select count($_i) from $_t where $_i=a.id)"

3561 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3562 # REMOVE next assignment until 2019/5/1

3563 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3564 and id in (select id from $_t where $_i=a.id) \

3565 and val > coalesce((select time from myacclog where \

3566 tblrowid=a.rowid),\

3567 '1970-01-01'))"

3568 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3569 cnt="$cntnew as '新着', $cntall as '総数',"

3570 dt_class=" td2r td3r dumpblogs"

3571 fi

3572 # Construct join expression

3573 eav="" scols=""

3574 pk=`gettblpkey $2`

3575 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3576 substr=${substr:-%s}

3577 for col in ${3:-`gettbl_s_cols $2`}; do

3578 valvar=val

3579 case $col in

3580 gecos) scols="$scols${scols:+, }${col#}"

3581 continue ;; # built-in column name

3582 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3583 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3584 case "$as" in

3585 *:*=*) cnd=${as#*:}

3586 h=${cnd%%=*} v=${cnd#*=}

3587 h=`sqlquotestr "$h"`

3588 v=`sqlquotestr "$v"`

3589 valvar="CASE val WHEN $h THEN $v END"

3590 as=${as%%:*} ;;

3591 esac

3592 ;;

3593 *) as=${col} ;;

3594 esac

3595 ss=`printf "$substr" "$valvar"`

3596 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3597 scols="$scols${scols:+, }b.$as"

3598 done

3599 #case author when '$user' then a.rowid else '---' end as ID,

3600 if [ -n "$DT_SQL" ]; then

3601 echo "$DT_SQL"

3602 else

3603 cat<<-EOF

3604 SELECT a.rowid as LINK, $cnt $scols

3605 FROM $2 a LEFT JOIN

3606 (SELECT $pk,$eav,

3607 max(CASE key

3608 WHEN 'owner'

3609 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3610 as gecos

3611 FROM ${2}_s c GROUP BY $pk)

3612 b ON a.$pk=b.$pk $4;

3613 EOF

3614 fi >> $sqlfile

3615 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3616 sqlog<<-EOF

3617 *** SQL-file: $sqlfile ***

3618 `cat $sqlfile`

3619 EOF

3620 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3621 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3622 cat $sqlfile >> tmp/sql

3623 # query ".read $sqlfile" > $tmpd/foo

3624 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3625 cp $tmpd/foo tmp/

3626 err "----- `gdate +%FT%T.%3N` ------------555555"

3627 ## $ddd LINE exists at the end of this function

3628 fi

3629 printf '.mode list\n.header 0\n' >> $sqlfile

3630 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3631 <div>

3632 <div class="dumptable">

3633 <table class="b$dt_class">

3634 `query ".read $sqlfile"`

3635 </table>

3636 </div>

3637 </div>

3638 EOF

3639 ### `query ".read $sqlfile"`

3640 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3641

3642 ## REMOVE THIS!

3643 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3644 }

3645

3646 par2table() (

3647 # copy current parameters of par into destination table

3648 # $1=definition-file

3649 # Using $user and $session

3650 # Return value:

3651 # 0: Stored successfully

3652 # 1: Insufficient fillings

3653 # 2: No permission to modify the record

3654 # 3: Invalid rowid

3655 # 4: SUCCESS to delete

3656 # 5: Stop deletion for lack of confirm check

3657 # 6: Password length too short

3658 # 7: Password mismatch

3659 # 8: Old password incorrect

3660 # 9: Duplicated post

3661 rowid=`getpar rowid`

3662 if [ ! -e $1 ]; then

3663 echo "テーブル定義ファイルが見付かりません" | html p

3664 exit 1

3665 fi

3666 tbl=${1%.def}

3667 tbl=${tbl##*/}

3668 if [ -n "$rowid" ]; then # Modify existing entry

3669 if [ x"$tbl" = x"user" ]; then

3670 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3671 elif [ x"$tbl" = x"grp" ]; then

3672 sql="select gname from $tbl where rowid=$rowid;"

3673 ##err p2t:grp:q $sql

3674 isgrpowner "$user" "`query $sql`" && rowowner=$user

3675 elif [ x"$tbl" = x"blog" ]; then

3676 # Check if owner in blog_s

3677 blogowner=`getvalbyid blog owner "$rowid"`

3678 if isgrpowner "$user" "$blogowner"; then

3679 rowowner=$user

3680 else

3681 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3682 fi

3683 else

3684 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3685 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3686 fi

3687 ### err rowowner=$rowowner

3688 if [ x"$user" != x"$rowowner" ]; then

3689 echo "他人のレコードはいじれないの" | html p

3690 return 2

3691 elif [ -z "$rowowner" ]; then

3692 echo "指定したレコードはないみたい" | html p

3693 return 3

3694 fi

3695 rm=`getpar rm` cfm=`getpar confirm`

3696 # Editing existent entry

3697 if [ x"$rm" = x"yes" ]; then

3698 if [ x"$rm$cfm" = x"yesyes" ]; then

3699 query "delete from $tbl where rowid=$rowid;"

3700 return 4

3701 else

3702 echo "消去確認のチェックがないので消さなかったの..." | html p

3703 return 5

3704 fi

3705 fi

3706 fi

3707

3708 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3709 if [ -n "$rowid" ]; then

3710 # Update of existing record

3711 for col in `gettblcols $tbl`; do

3712 val=`getparquote $col`

3713 [ -z "$val" ] && continue

3714 ## err query "update $tbl set $col=$val where rowid=$rowid"

3715 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3716 sql="update $tbl set $col=$val where rowid=$rowid;"

3717 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3718 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3719 ## User name cannot be changed with interface provided with this

3720 ## script. But we offer the trigger to change owner user

3721 ## of blog_s table.

3722 #err "select quote($col) from $tbl where rowid=$rowid;"

3723 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3724 cat<<-EOF | query

3725 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3726 -- should use EXCLUSIVE transaction outside of this.

3727 SAVEPOINT par2table;

3728 $sql

3729 update blog_s set val=$val

3730 where key='owner' and val=$old;

3731 RELEASE SAVEPOINT par2table;

3732 EOF

3733 ## XX: DIRTY Hack Ends here

3734 ## We should keep blog's owner as a single column which has

3735 ## foreign key constraint with primary key of grp/user.

3736 else

3737 query "$sql"

3738 fi

3739 done

3740 # Then, set up $pval for further insertion of tbl_s and tbl_m

3741 for col in `gettblpkey $tbl`; do

3742 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3743 pval="$pval${pval:+, }\"$val\""

3744 done

3745 else

3746 # New entry

3747 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3748 # Avoid empty repost of article.

3749 if [ x"$tbl" = x"article" ]; then

3750 # If rowid is empty and ID exists in article-table, that is REPOST!

3751 aid=`getpar id`

3752 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3753 if [ -n "$xaid" ]; then

3754 # REPOST of article

3755 html p <<-EOF

3756 書き込み直後のリロードなので上書きを回避します。

3757 最新記事は末尾の「再読み込み」ボタンから見てください。

3758 EOF

3759 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3760 return 9 # STOP Duplicated posting

3761 fi

3762 fi

3763 # Generate values() for primary keys

3764 for col in `gettblpkey $tbl`; do

3765 # Genuine primary keys for _m and _s

3766 val=`getvalquote $tbl $col`

3767 [ -z "$val" ] && continue

3768 pval="$pval${pval:+, }$val"

3769 done

3770 ##err pval=$pval

3771 for col in `gettblfkey $tbl`; do

3772 # args for values() to insertion into master table

3773 val=`getvalquote $tbl $col`

3774 [ -z "$val" ] && continue

3775 formaster=$formaster"${formaster:+, }$val"

3776 done

3777 formaster="$pval${formaster:+, }$formaster"

3778 ## err formaster=$formaster

3779 if [ -z "$formaster" ]; then

3780 echo "項目を全て埋めてください" | html pre

3781 return 1

3782 fi

3783 ## err "replace into $tbl values($formaster);"

3784 query "replace into $tbl values($formaster);"

3785 ## Insertion to master table, done

3786 fi

3787

3788 transaction=$tmpd/sqlfile.sql; touch $transaction

3789 for kt in s m; do

3790 tb2=${tbl}_$kt

3791 for col in `gettbl_${kt}_cols $tbl`; do

3792 ptype=`getpartype $col "limit 1"`

3793

3794 # First, check update of existing entries in _m

3795 if [ $kt = m ]; then

3796 # sessID|address.1.22|string|Somewhere-x.y.z

3797 sql=""

3798 ##err dots from query "select var from par where var like '$col.%';"

3799 for v in `query "select var from par where var like '$col.%';"`; do

3800 # v=address.1.22

3801 st_rowid=${v##*.}

3802 origcol=${v%%.*} # original column derived from

3803 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3804 ##case `getpartype $v` in

3805 ## err CASE `gettbl_coltype $tbl/$origcol` in

3806 ## err edit flag = `getpar action.$v`

3807 case `getpar action.$v` in

3808 rm)

3809 if [ x`getpar confirm.$v` = x"yes" ]; then

3810 newsql="delete from $tb2"

3811 else

3812 echo "削除確認未チェック" | html p

3813 fi ;;

3814 edit)

3815 case `gettbl_coltype $tbl/$origcol` in

3816 image|document|binary)

3817 file=$tmpd/`getparfilename $v`

3818 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3819 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3820 continue

3821 fi

3822 ## err type=file=$file

3823 [ -z "$file" ] && continue

3824 bn=`sqlquotestr "${file##*/}"`

3825 bin="X'"$(hexize "$file")"'"

3826 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3827 type=\"file:$ct\"

3828 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3829 cachedir=`getcachedir "$tbl/$rowid"`

3830 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3831 rm -rf $cachedir

3832 ;;

3833 *)

3834 newsql="update $tb2 set val=(select val from par where var \

3835 like '$col.%.$st_rowid')"

3836 ;;

3837 esac

3838 ;;

3839 *) # maybe "keep", do not modify value

3840 continue

3841 ;;

3842 esac

3843 # err newsql=$newsql

3844 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3845 done

3846

3847 if [ x"$bin" = x"NULL" ]; then

3848 ## err repl:normal sql=`echo $sql`

3849 if [ -n "$transaction" ]; then

3850 cat<<-EOF >> $transaction

3851 $sql

3852 DELETE FROM $tb2 WHERE type='string' AND val='';

3853 EOF

3854 else

3855 query "$sql

3856 delete from $tb2 where type='string' and val='';"

3857 ## err repl:normal done

3858 fi

3859 else

3860 # Binary update line is TOO LONG to pipelining

3861 sqlfile="$tmpd/sqlf.$$"

3862 if [ -n "$transaction" ]; then

3863 echo "$sql" >> $transaction

3864 else

3865 echo "$sql" > $sqlfile

3866 query ".read $sqlfile"

3867 fi

3868 fi

3869 # Rest of kt==m: set multiple mode

3870 nr=`getparcount $col`

3871 else

3872 nr=1 # for kt==s, number of records is 1

3873 fi

3874

3875 i=0

3876 while [ $i -lt $nr ]; do

3877 limit="limit 1 offset $i"

3878 i=$((i+1)) # increase beforehand against continue

3879 val=`getvalquote $tbl $col "$limit"`

3880 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3881 ## err $col=$val

3882 bin=NULL

3883 ## err partype$col=`getpartype $col "$limit"`

3884 case $ptype in

3885 file) file=$tmpd/`getparfilename $col "$limit"`

3886 ## err parfile-$col=$file

3887 [ -z "$file" ] && continue

3888 bin="X'"$(hexize "$file")"'"

3889 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3890 type=\"file:$ct\" ;;

3891 "*"*) continue ;; # foreign table

3892 *) type=\"string\" ;;

3893 esac

3894 case `gettbl_coltype $tbl/$col` in

3895 password) # special care for password

3896 # name={password,pswd1,pswd2}

3897 p1=`getpar pswd1 "$limit"`

3898 if [ -z "$p1" ]; then

3899 continue # SKIP password setting, if p1 is empty

3900 else

3901 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3902 ## err pswd=$pswd

3903 if pwcheck "$pswd"; then

3904 if [ x"$p1" = x"$p2" ]; then

3905 case "$p1" in

3906 ??????????*) ;;

3907 *) echo "パスワードは10字以上にしてください。" | html p

3908 return 6;;

3909 esac

3910 val="\"`echo $p1|mypwhash`\""

3911 else

3912 echo "2つの新パスワード不一致" | html p

3913 return 7

3914 fi

3915 else

3916 echo "旧パスワード違います" | html p

3917 return 8

3918 fi

3919 fi

3920 ;;

3921 esac

3922 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3923 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3924 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3925 if [ x"$bin" = x"NULL" ]; then

3926 ## err Normal-query: `echo $sql`

3927 if [ -n "$transaction" ]; then

3928 echo "$sql" >> $transaction

3929 else

3930 query "$sql"

3931 fi

3932 else

3933 sqlfile="$tmpd/query.$$"

3934 ## err sqlfile=`ls -lF $sqlfile`

3935 if [ -n "$transaction" ]; then

3936 echo "$sql" >> $transaction

3937 else

3938 echo "$sql" > $sqlfile

3939 query ".read $sqlfile"

3940 fi

3941 fi

3942 ## err p2t done

3943 done

3944 done

3945 done

3946 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3947 -- We cannot use transaction here, because groupupdate may use it.

3948 SAVEPOINT pa2table_insert;

3949 .read $transaction

3950 RELEASE SAVEPOINT pa2table_insert;

3951 EOF

3952 rc=$?

3953 [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag

3954 ## err "Table:$tbl update done "

3955 return $rc

3956 )

3957 genform() {

3958 # $1 = form definition file

3959 # $2, $3 (optional)= table name and ROWID

3960 # If $GF_VIEWONLY set and nonNull, output values without form

3961 # If $GF_ARGS set, use it as content-strings in the form

3962 # If $GF_OWNER set, use it as value of name="owner"

3963 # If $GF_STAGE set, use it as value of name="stage"

3964 forms="" hiddens="" rowid=$3

3965 if [ ! -e "$1" ]; then

3966 echo "そのようなデータベースはないようです($2)。" | html p

3967 return

3968 elif [ -n "$2" ]; then

3969 rec=`query "select * from $2 where rowid='$rowid';"`

3970 if [ -z "$rec" ]; then

3971 pk=`gettblpkey $2`

3972 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3973 rec=`query "select rowid from $2 where $pk='$rowid';"`

3974 rowid=$rec

3975 rec=$3

3976 fi

3977 if [ -z "$rec" ]; then

3978 echo "そんなレコードはないみたいね..." | html p

3979 return

3980 fi

3981 fi

3982 if [ -z "$GF_VIEWONLY" ]; then

3983 rm='<input id="rm" name="rm" type="checkbox"

3984 value="yes"><label for="rm">このエントリの削除</label>

3985 <span>ほんとうに消しますよ(確認)!

3986 <input name="confirm" type=checkbox value="yes">はい</span>'

3987 fi

3988 # Image Cache dir

3989 ## err genform: getcache=$2/$rowid

3990 td=`getcachedir "$2/$rowid"`

3991 while IFS=: read prompt name keytype type args; do

3992 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3993 sp="${args:+ }"

3994 form="" val=""

3995 if [ -n "$rowid" ]; then

3996 # err genform2a: Seeking for "$2.$name, type=$type"

3997 rawval=`getvalbyid $2 $name $rowid $td`

3998 val=`echo "$rawval"|htmlescape`

3999 ## err genform3a: getvalbyid $2 $name $rowid $td

4000 ## err genform3b: val="[$val]" type="$type"

4001 fi

4002 if [ -n "$GF_VIEWONLY" ]; then

4003 is_hidden "$2" "$name" && continue

4004 fi

4005 case "$type" in

4006 text*)

4007 cgiform=cgi_multi_$type

4008 if [ -s $td/$name.count -a -n "$val" ]; then

4009 form=`$cgiform $name $td`

4010 val=$(echo "$val"|

4011 while read fn; do

4012 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4013 </td></tr>$nl"

4014 done)

4015 val="<table>$nl$val$nl</table>"

4016 else

4017 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4018 form=`cgi_$type $name "$rawval" "$args"`

4019 fi

4020 ;;

4021 [Rr][Aa][Dd][Ii][Oo])

4022 fh="<label><input type=\"radio\" name=\"$name\""

4023 form="`echo $args|sed -e \

4024 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4025 ;;

4026 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4027 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4028 ;;

4029 [Ss][Ee][Ll][Ee][Cc][Tt])

4030 fh="<select name=\"$name\">$nl"

4031 form=$(for l in $args; do

4032 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4033 done)

4034 if [ -n "$val" ]; then

4035 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4036 fi

4037 form="$fh$form</select>"

4038 ;;

4039 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4040 if [ -s $td/$name.count ]; then

4041 form=`cgi_multi_file $name $td "$args"`

4042 if [ -n "$val" ]; then

4043 hrfb="$myname?showattc+$2_m"

4044 val=$(echo "$rawval" \

4045 | while read fn; do

4046 data=`percenthex "$td/$fn"`

4047 #ct=`cat $td/$fn.content-type`

4048 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4049 ri=`cat "$td/$fn.rowid"`

4050 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4051 #imgsrc="<img src=\"data:$ct,$data\">"

4052 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4053 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4054 done)

4055 fi

4056 else

4057 form="<input type=\"file\" name=\"$name\" $args>"

4058 if [ -n "$val" ]; then

4059 imgs=$(echo "$rawval"\

4060 |while read fn;do

4061 data=`percenthex "$td/$fn"`

4062 echo "<img src=\"data:image/png,$data\">$fn<br>"

4063 done)

4064 form=$form"<br>$imgs"

4065 val=$imgs # 2015-06-15

4066 else

4067 form="<input type=\"file\" name=\"$name\" $args>"

4068 fi

4069 fi

4070 ;;

4071 [Hh][Ii][Dd][Dd][Ee][Nn])

4072 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4073 args="value=\"$GF_STAGE\""

4074 fi

4075 form="<input type=\"hidden\" name=\"$name\" $args>"

4076 prompt='' # Remove prompt

4077 ;;

4078 [Aa][Uu][Tt][Hh][Oo][Rr])

4079 [ -n "$GF_VIEWONLY" ] && continue

4080 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4081 prompt="" ;;

4082 [Oo][Ww][Nn][Ee][Rr])

4083 [ -n "$GF_VIEWONLY" ] && continue

4084 val=${GF_OWNER:-$val}

4085 val=${val:-$user}

4086 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4087 prompt="" ;;

4088 [Uu][Ss][Ee][Rr])

4089 # XXX: is null $user ok?

4090 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4091 [ -n "$GF_VIEWONLY" ] && continue

4092 form="$user"

4093 ;;

4094 [Pp]assword)

4095 [ -n "$GF_VIEWONLY" ] && continue

4096 form="`cgi_passwd`"

4097 val=""

4098 ;;

4099 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4100 [ -n "$GF_VIEWONLY" ] && continue

4101 if [ -z "$rowid" ]; then

4102 val=`genserial`

4103 fi

4104 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4105 prompt="" ;;

4106 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4107 prompt=""

4108 ;;

4109 parent|path|blog*)

4110 prompt=""

4111 ;;

4112 "*"*)

4113 tail=$tail"``"

4114 continue ;;

4115 esac

4116 if [ -n "$prompt" ]; then

4117 if [ -n "${GF_VIEWONLY}" ]; then

4118 form=$val

4119 else

4120 :

4121 fi

4122 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4123 else

4124 hiddens=$hiddens$nl"$form"

4125 fi

4126 done < $1

4127 # enctype="multipart/form-data"

4128 cat<<EOF

4129 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4130 ${rowid:+$rm}

4131 <table class="b $2">

4132 $forms

4133 </table>$hiddens

4134 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4135 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4136 EOF

4137 if [ -z $GF_VIEWONLY ]; then

4138 cat<<EOF

4139 <input type="submit" name="sub" value="OK">

4140 <input type="reset" name="res" value="Reset">

4141 EOF

4142 fi

4143 cat<<EOF

4144 $GF_ARGS</form>

4145 $tail

4146 EOF

4147 }

4148 edittable() {

4149 # $1=form-def $2=table $3 rowid

4150 genform "$@"

4151 }

4152 viewtable() {

4153 GF_VIEWONLY=1 genform "$@"

4154 }

4155 showattc() {

4156 # $1=table_m $2=rowid &optional $3=RawFlag

4157 ## err \$1=$1 \$2=$2 \$3=$3

4158 if ! isfilereadable $user $1 $2; then

4159 contenttype; echo

4160 echo "このファイルは管理者のみしか見られません" | html p

4161 putfooter; exit

4162 fi

4163 idir=`umask 002; mktempd` || exit 1

4164 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4165 bin=$idir/$myname-$$.bin

4166 sql="select quote(bin) from $1 where rowid='$2';"

4167 ## err showattc: sql: $sql

4168 sq $db "$sql" | unhexize > $bin

4169 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4170 type=${tv%//*} fn=${tv#*//}

4171 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4172 ct=${type#file:}

4173 case $ct in # all text/* changed to text/plain

4174 text/*)

4175 charset=`nkf -g $bin|cut -d' ' -f1`

4176 case $charset in

4177 ASCII*) charset="" ;;

4178 esac

4179 if [ -z "$3" ]; then

4180 ct="text/html${charset:+; charset=$charset}"

4181 link="?showattc+$1+$2+raw"

4182 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4183 | sed 's,^,<span></span>,' \

4184 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4185 -D_LINK_="$link" \

4186 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4187 exit $?

4188 fi

4189 ct="text/plain${charset:+; charset=$charset}"

4190 ;;

4191 esac

4192 contenttype "$ct"

4193 echo "Content-Disposition: filename=\"$fn\""

4194 echo "Content-Length: " `cat $bin | wc -c`; echo

4195 #echo "Content-Type: " ${type#file:}; echo

4196 cat $bin

4197 }

4198 #

4199 # Some default stupid handler on CGI values

4200 #

4201 default_storedb() {

4202 # ARG: $1=table-def-file

4203 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4204 tbl=`basename $1`

4205 tbl=${tbl%.def}

4206 cols="`grep :text $1|cut -d: -f2`"

4207 col=`echo "$cols"|head -1`

4208 vcol=`getpar $col`

4209 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4210 if [ -n "$vcol" ]; then

4211 par2table $1

4212 else

4213 return 2 # No insertion occurred

4214 fi

4215 }

4216

4217 default_view() { # $1=def-file

4218 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4219 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4220 default_storedb "$@"

4221 query "select rowid from $tbl order by rowid desc;" \

4222 | while read rowid; do

4223 viewtable $1 $tbl $rowid

4224 done | _m4 -D_TITLE_="$tbl" \

4225 -D_FORM_="`genform $1`" \

4226 -D_DUMPTABLE_="syscmd(cat)" \

4227 $layout/html.m4.html $layout/form+dump.m4.html

4228 }

4229 default_viewtext() { # $1=def-file

4230 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4231 default_storedb "$@"

4232 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4233 | _m4 -D_TITLE_="$tbl" \

4234 -D_FORM_="`genform $1`" \

4235 -D_DUMPTABLE_="syscmd(cat)" \

4236 $layout/html.m4.html $layout/form+dump.m4.html

4237 }

4238 default_smail() {

4239 default_storedb "$@"

4240 if [ $? -eq 2 ]; then

4241 _m4 -D_TITLE_="入力" \

4242 -D_FORM_="`genform $1`" \

4243 -D_DUMPTABLE_="" \

4244 $layout/html.m4.html $layout/form+dump.m4.html

4245 return

4246 fi

4247 cond=""

4248 for pk in `gettblpkey $tbl`; do

4249 pv=$(sqlquote "$(getpar $pk)")

4250 cond="$cond${cond:+ and }$pk=$pv"

4251 done

4252 sql="select rowid from $tbl where $cond;"

4253 rowid=`query "$sql"`

4254 ## err smail1 - "$sql" "-> rowid=$rowid"

4255

4256 while IFS=: read prompt name keytype type args; do # Read from $1

4257 val=`getpar $name`

4258 if [ -n "$val" ]; then

4259 text="$text

4260 $prompt

4261 $name=$val

4262 ---------------------------------------------------------"

4263 fi

4264 case "$type" in

4265 image|document|file)

4266 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4267 fns=$(echo "$fn"|while read fn; do

4268 err mv $tmpd/$fn.orig $tmpd/$fn

4269 mv $tmpd/$fn.orig $tmpd/$fn

4270 rm $tmpd/$fn.rowid # Remove cache flag

4271 ## err "`ls $tmpd/$fn`"

4272 echo $fn

4273 done)

4274 files="$files $fns"

4275 ;;

4276 esac

4277 done < $1

4278 ## err FILES=$files "`ls -lF $tmpd`"

4279 subj="from ${REMOTE_ADDR}"

4280 (echo "$url"

4281 echo "への書き込みがありました。"

4282 echo "------"

4283 echo "$text"

4284 ) | (cd $tmpd &&

4285 err LS="`ls -lF`" &&

4286 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4287 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4288 echo "以下の内容で送信しました。" | html p

4289 viewtable $1 $tbl \

4290 `query "select rowid from $tbl order by rowid desc limit 1;"`

4291 echo "戻る" | html a "href=\"?\""

4292 }