s4: b5df3b16c9d8 s4-funcs.sh

s4

view s4-funcs.sh @ 740:b5df3b16c9d8

Too much html-escaping for group name fixed

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 05 Jun 2020 14:49:59 +0900
parents	441768a11b84
children	f2c7117f452e 42cc0aeaa498

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 db=${DB:-$dbdir/cgi.sq3}

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

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

18 workdb=$dbdir/tmpdata.sq3

19 sessdb=$dbdir/sess.sq3

20 sesstb=tmp.sess

21 listentlimit=${LISTENTLIMIT:-30}

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

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

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

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

26 templ=${TEMPL:-templ}

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

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

29 imgdir=${IMGDIR:-img}

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

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

32 msgdir=$templ/msg

33 timeout="+2 days"

34 memoplimitdays="7"

35 dumpcollen=22

36 #thumbxy=120x120

37 thumbxy=96x96

38 iconxy_S=80x80

39 iconxy_M=400x400

40 maximagexy=1600x1600

41 ### maximagexy=400x400

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

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

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

45 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"'

47 file_accept_help="

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

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

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

51 "

52 file_warn="$file_accept_help

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

54 blogreadflagrowid=0

55 blogcutoffflagrowid=-1

56 whatsnewdays=${WHATS_NEW_DAYS:-14}

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

58 session=$main_session

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"

67 case "$HTTP_USER_AGENT" in

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

69 *) touchpanel="" ;;

70 esac

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

73 : <<EOF

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

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

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

78 レポート提出管理

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

81 [2016]

82 7/12 根本への反省

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

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

86 [以下2015]

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

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

89 Web

90 締切設定

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

93 ○自分の提出物リスト

95 7/19 ○設置

96 ○一斉送信

97 ○getparfilename の tmpd の扱い

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

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

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

101 ○添付ファイル回収

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

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

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

105

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

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

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

109 ○レポート提出モード

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

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

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

113

114

115 7/9 ○管理者の追加

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

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

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

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

120

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

122

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

124 ○グループ検索

125

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

127

128

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

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

131

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

133 ○6/1 par2tableを triplex 対応に

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

135 →とすると一気に

136

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

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

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

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

141

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

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

144 ## 1. passwd

145 ## 2. grp

146 ## 3. grp_mem

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

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

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

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

151

152 ## ● listの定義:

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

154 ## ● hashの定義:

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

156

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

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

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

160 ## csv2sq3 で .csv.sq3 の Makefile

161

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

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

164 ## type := root | comment

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

166 ## unique(id, filename)

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

168

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

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

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

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

173 ## できたー!

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

175 ↓

176 ↓以下に変更

177 with recursive allmem as

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

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

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

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

182

183

184 with recursive allmem as

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

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

187 where allmem.val=grp_m.gname)

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

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

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

191

192

193 ## triggerもできた。

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

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

196

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

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

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

200 ## グループ管理

201 ## 個人情報管理

202 ##

203

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

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

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

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

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

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

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

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

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

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

214

215 ## ログテーブル

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

217

218

219

220 ■表設計

221 * 3つの表に分散管理

222 id格納表 + hash表 + list表

223 * *_s *_m

224

225

226

227 user, user_map, user_col

228

229 ■抽象エントリタイプ

230 * user

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

232 * group

233 権限判定に利用

234 * serial

235 自動idとして機能

236 * password

237 入力 type=passwordで入力

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

239 * session

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

241 * text

242 入力 type=text

243 * textarea

244 入力 textarea

245 * image|document

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

247 * owner

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

249 * gowner

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

251 * timestamp

252 datetime()

253 * parent

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

255 * path

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

257

258 格納タイプ

259 * list

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

261 * hash

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

263

264 オブジェクトタイプ

265 * entry

266 id, title, owner

267 * textpart

268 id, parentID, text

269 * binarypart

270 id, parentID, contenttype, filename, content

271 * content

272 hash(textpart), list(binarypart)

273 * topic

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

275 * reply

276 id, parentID, content

277 * blog

278 list(entry)

279 blog = [topic, list(reply)]

280

281

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

283 "text" => "hogehoge ..",

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

285 "author" => "taro",

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

287 "parent" => "/",

288 "path" => "/1",

289 "text" => "blah, blah, ....",

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

291 {"serial" => 2,

292 "author" => "hanako",

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

294 "parent" => "/",

295 "path" => "/2",

296 "text" => "blah, blah, ....",

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

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

299

300

301 user:=

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

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

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

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

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

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

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

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

310

311 変換表

312 /user/email=m

313

314 blog:=

315 シリアル:id:p:serial

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

317 所有者:owner:s:owner:

318 時刻:ctime:s:stamp:

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

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

321

322 article:=

323 シリアル:id:p:serial

324 筆者:author:s:owner

325 時刻:ctime:s:stamp:

326 参照元:parent:s:parent:

327 パス:path:s:path:

328 本文:text:s:textarea:

329 画像:image:m:image:

330

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

332

333

334 EOF

335

336 logstart() {

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

338 }

339 logend() {

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

341 }

342 sqlog() {

343 logstart

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

345 cat >> $querylog

346 else

347 echo "$*" >> $querylog

348 fi

349 logend

350 }

351 sq() {

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

353 logstart

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

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

356 else

357 echo "$@" >> $querylog

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

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

360 fi

361 logend

362 }

363 dbsetup() {

364 pipedir=$tmpdir/pipedir

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

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

367 suf=`date +%s`

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

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

370 mkfifo $sqi $sqo

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

372 sq $db < $sqi &

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

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

375 exec 2>> $tmpdir/error.out

376 fi

377 exec 3>> $tmpdir/debug.out

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

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

380 rm $sqi

381 # Attach supplemental DB

382 cat >&5 <<-EOF

383 .output /dev/null

384 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

388 .output stdout

389 EOF

390 }

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

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

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

394 fi

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

396 }

397 cleanup() {

398 trap '' INT HUP EXIT TERM PIPE

399 echo .quit >&5

400 kill $sq3pid

401 kill $sq3pid

402 rm -f $sqo $sqi

403 rm -rf $tmpfiles

404 cleanup2

405 }

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

407 # simple redirection for the sake of speed.

408 query() {

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

410 echo ".output $sqo" >&5

411 logstart

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

413 tee -a $querylog

414 else

415 echo "$@" >> $querylog

416 echo "$@"

417 fi >&5

418 echo ".output stdout" >&5

419 cat $sqo

420 rc=$?

421 logend

422 return $rc

423 }

424 _m4() {

425 #S4NAME=f,f,f

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

427 }

428 ismember() {

429 # $1=user, $2=group

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

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

432 }

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

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

435 }

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

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

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

439 }

440 isgrpowner() (

441 # $1=user, $2=group

442 gn=`sqlquote "$2"`

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

444 err isgrpowner: $sql

445 test -n "`query $sql`"

446 )

447 isgrpownerbygid() (

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

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

450 err isgrpownerbygid: $sql

451 test -n "`query $sql`"

452 )

453 getgroupadminmails() {

454 # $1=group

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

456 email4group "$1" "$i" ;

457 done

458 }

459 getgroupadmins() { # $1=group

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

461 qgrp=`sqlquote "$1"`

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

463 }

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

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

466 getvalbyid grp $2 \

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

468 }

469 getgroupbyid() {

470 # $1=id|gname

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

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

473 # err ggbyid: `echo $sql`

474 query $sql

475 }

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

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

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

479

480 # Return true when anonymous mode

481 [ "$anonymousmode" ] && return 0

482 # case `getvalbyid blog mode $2` in

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

484 # *closed*)

485 # owner=`getvalbyid blog owner $2`

486 # if isgrp $owner; then

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

488 # elif isuser $owner; then

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

490 # fi

491 # esac

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

493 sql="with getblog as (

494 select key,val from blog_s where id=(

495 select blogid from article where id in

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

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

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

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

500 isgrpadm as (select user from grp_adm where

501 gname=(select val from getowner) and

502 user='$1'),

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

504 select case

505 when (select author from article where

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

507 then 'author'

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

509 then 'open'

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

511 then CASE

512 WHEN (SELECT val FROM isgrp) IS NULL

513 THEN

514 CASE WHEN (SELECT val from getowner)

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

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

517 ELSE ''

518 END

519 WHEN (select user from isgrpadm) IS NOT NULL

520 THEN 'i-am-admin'

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

522 END

523 when (select val from getmode) is null

524 then 'open'

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

526 then (SELECT user FROM isgrpadm)

527 when (select author from article where

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

529 then 'user+author'

530 else '' end;"

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

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

533 # result=`query "$sql"`

534 # err FileAccessibility=$result

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

536 }

537 linkhome() {

538 # $1=UserOrGroup

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

540 if isuser $1; then

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

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

543 else

544 echo -n "grp+$1"

545 fi

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

547 }

548 hreflink() {

549 # s4 specific notation:

550 # ^href=URL

551 # ^iframe=URL

552 # ^video=URL

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

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

555 # OSM umap Wikistyle Notation:

556 # [[URL]] - Simle Link

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

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

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

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

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

562 # Other Style

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

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

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

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

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

568 # SPC+SPC+$ - <br>

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

570 checkboxON="${cb} checked>"

571 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

596

597 }

598 minitbl() {

599 sed -n '

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

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

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

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

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

605 H; # Concat this line to HoldSpace

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

607 $ b cont

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

609 }

610 :cont

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

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

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

614 # . of ^. is workaround for FreeBSD sed

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

616 p; # Print whole "table" element

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

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

619 }

620 x; # Back to the newest line

621 p; # Print rest' | miniul

622 }

623 miniul() {

624 sed -e '

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

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

627 :top

628 s/\n//;

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

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

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

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

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

634 $ b cont

635 N; # 次の行を読む

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

637 /^ /b top

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

639 p; s/.*//;

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

641 /^\* /b top

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

643 }

644

645 :cont

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

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

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

649 H; x

650 }

651 x'

652 }

653 acclog() (

654 # $1=table, $2=rowid

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

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

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

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

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

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

661 fi

662 )

663 gecos() (

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

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

666 )

667 setpar() {

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

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

670 case "$1" in

671 user) _user="$v" ;;

672 skey) _skey="$v" ;;

673 esac

674 fi

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

676 }

677 replpar() {

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

679 }

680 getpar() {

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

682 val=""

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

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

685 user) val=$_user ;;

686 skey) val=$_skey ;;

687 esac

688 fi

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

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

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

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

693 fi

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

695 case "$var" in

696 owner)

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

698 echo $user; return

699 elif ismember $user $val; then

700 echo $val; return

701 fi ;;

702 esac

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

704 echo "$val"

705 }

706 setskey() {

707 # For quick response...(?)

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

709 }

710 chkskey() {

711 # $1=sesskey, $user=LoginUserName

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

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

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

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

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

717 return 0

718 fi

719 return 1

720 }

721 resetskey() {

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

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

724 fi

725 }

726 getpartype() {

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

728 }

729 getparcount() {

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

731 }

732 getparfilename() {

733 # null if type of $1 is not file

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

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

736 }

737 sqlquote() {

738 (v="$1"

739 case "$v" in

740 "") return ;; # null

741 "X'"*) # quoted hex string

742 echo $1 ;;

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

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

745 echo "\"$v\""

746 return ;;

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

748 echo "\"$v\""

749 return ;;

750 *)

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

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

753 else

754 echo "\"$v\""

755 fi ;;

756 esac)

757 }

758 sqlquotestr() (

759 case "$1" in

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

761 echo "'$v'" ;;

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

763 esac

764 )

765 mktempd() {

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

767 }

768 getcachedir() { # $1=maintable

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

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

771 else

772 echo $tmpd/$thumbxy

773 fi

774 }

775 getval() {

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

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

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

779 echo "$user" ;;

780 stamp|datetime)

781 date "+%F %T" ;;

782 serial)

783 (s=`getpar $2`

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

785 *)

786 getpar "$2" "$3";;

787 esac

788 }

789

790 getvalquote() {

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

792 (v=`getval "$@"`

793 case "$v" in

794 "") echo NULL ;;

795 *) sqlquote "$v" ;;

796 esac)

797 }

798 getparquote() {

799 sqlquote "`getpar $1`"

800 }

801 getbinbyid() {

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

803

804 }

805 getvalbyid() {

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

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

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

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

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

811

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

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

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

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

816 return

817 fi

818 done

819 rowid=$3

820 pk=`gettblpkey $1`

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

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

823 td=${4:-$tmpd}

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

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

826 for kt in s m; do

827 t=${1}_$kt

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

829 vcount=1 # count(val)

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

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

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

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

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

835 if [ $kt = m ]; then

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

837 # Reset val to store filenames if type is string

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

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

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

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

842 i=0

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

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

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

846 i=$((i+1))

847 fn=$c.$i

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

849 case $type in

850 file:*)

851 #file=$td/$val

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

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

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

855 # FOR SPEED: Skip file generation if imgcache exists

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

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

858 && continue

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

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

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

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

863 select rowid from $cond $slice;

864 .output '$td/$fn'

865 select val from $cond $slice;

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

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

868 .output stdout

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

870 EOF

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

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

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

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

875 ls -lh "$file" |

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

877 case "$type" in

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

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

880 *|*:[Aa]pplication*)

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

882 png:- > "$file"

883 ;;

884 esac

885 ;;

886 *)

887 sq $db<<EOF

888 .output $td/$fn.rowid

889 select rowid from $cond $slice;

890 .output $td/$fn

891 select val from $cond $slice;

892 EOF

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

894 ;;

895 esac

896 done

897 else

898 rm -f $td/$c.count

899 case $type in

900 file:*)

901 echo "$val" \

902 | while read fn; do

903 file=$td/$fn

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

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

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

907 | unhexize > "$file"

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

909 case $type in

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

911 *:[Aa]pplication*)

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

913 png:- > $file ;;

914 esac

915 fi

916 done

917 ;;

918 esac

919 fi

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

921 return

922 fi

923 done

924 done)

925 }

926 getvalbypkey() (

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

928 pk=`gettblpkey $1`

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

930 getvalbyid "$1" "$2" $rowid $4

931 )

932 getvalbycond() {

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

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

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

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

937 getvalbyid "$1" "$2" $rowid "$4"

938 fi

939 }

940 getpwfield() {

941 # getpwfield user column

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

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

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

945 echo "$val"

946 return 0

947 else

948 return 1

949 fi

950 }

951 numericalize() {

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

953 }

954 encode() {

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

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

957 sha1=sha1

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

959 sha1=sha1sum

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

961 sha1=gsha1sum

962 fi

963 fi

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

965 }

966 enjpeg() {

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

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

969 cjpeg="cjpeg"

970 else

971 cjpeg="convert - jpeg:-"

972 fi

973 fi

974 $cjpeg "$@"

975 }

976 mycrypt() (

977 key=$1 salt=$2

978 # err \$2=$2

979 case $2 in

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

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

982 esac

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

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

985 )

986 hexize() {

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

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

989 hexize="xxd -p"

990 else

991 hexize_hd() {

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

993 }

994 hexize="hexize_hd"

995 fi

996 fi

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

998 }

999 unhexize() {

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

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

1002 unhex="xxd -p -r"

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

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

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

1006 EOF

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

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

1009 # world writable...:)

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

1011 fi

1012 fi

1013 cat "$@" | $unhex

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

1015 }

1016 percenthex() {

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

1018 }

1019 htmlescape() {

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

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

1022 }

1023 enascii() {

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

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

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

1027 else

1028 enascii_now=`date +%FT%T`

1029 enascii_sed() {

1030 nkf -Z0Z1Z2 \

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

1032 }

1033 enascii="enascii_sed"

1034 fi

1035 fi

1036 cat "$@" | $enascii

1037 }

1038 size_h() {

1039 i="$1" oi=$1

1040 set -- B B KB MB GB TB

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

1042 oi=$i

1043 i=$((i/1024))

1044 shift

1045 done

1046 echo ${oi}$1

1047 }

1048 gettblconf() {

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

1050 ## tconfs=`sq $db \

1051 tconfs=`query \

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

1053 fi

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

1055 }

1056 gettblkeys() {

1057 # $1=tbl

1058 gettblconf

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

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

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

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

1063 case $type in

1064 *p*)

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

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

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

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

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

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

1071 esac

1072 case $type in

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

1074 esac

1075 done

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

1077 }

1078 gettblpkey() {

1079 # $1=tbl

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

1081 }

1082 gettblfkey() {

1083 (x=`gettblkeys $1`

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

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

1086 }

1087 gettblcols() {

1088 (x=`gettblkeys $1`

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

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

1091 }

1092 gettbl_s_cols() {

1093 (x=`gettblkeys $1`

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

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

1096 }

1097 gettbl_m_cols() {

1098 (x=`gettblkeys $1`

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

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

1101 }

1102 gettbl_h_cols() {

1103 (x=`gettblkeys $1`

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

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

1106 }

1107 gettbl_coltype() (

1108 gettblconf

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

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

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

1112 )

1113 is_hidden() {

1114 # $1=Tbl $2=col

1115 gettblconf

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

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

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

1119 case $x in

1120 *h*) return 0 ;;

1121 *) return 1 ;;

1122 esac

1123 }

1124

1125 dbsetbyid() {

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

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

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

1129 quotedp=$(sqlquotestr "$p")

1130 unset primary update

1131 gettblconf

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

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

1134 #err conf=$conf

1135 case ${conf#*=} in

1136 p*) primary=1 ;;

1137 f*) update=1 ;;

1138 u*) ;;

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

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

1141 esac

1142 #err t=$t

1143 type=string fn=""

1144 case $conf in

1145 */password)

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

1147 ;;

1148 */image*|*/document*)

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

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

1151 ;;

1152 esac

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

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

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

1156 if [ "$primary" ]; then

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

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

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

1160 elif [ "$update" ]; then

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

1162 else

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

1164 fi

1165 )

1166 }

1167 expire() (

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

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

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

1171 )

1172 addsession() {

1173 # expireをセット

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

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

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

1177 # Remove old session parameters

1178 now=`expire now`

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

1180 }

1181 gencookie() (

1182 path=${URL#*:/}

1183 path=${URL%/*}

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

1185 for kv; do

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

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

1188 done

1189 )

1190 contenttype() {

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

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

1193 }

1194 putheader() {

1195

1196 }

1197 putfooter() {

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

1199 }

1200 getcookie() {

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

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

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

1204 case "$k" in

1205 user) _user="$v" ;;

1206 skey) _skey="$v" ;;

1207 esac

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

1209 done

1210 }

1211 genrandom() {

1212 # $1=columns (default: 10)

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

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

1215 }

1216 genserial() {

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

1218 }

1219 smail() {

1220 # smail rcpts subj (file)

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

1222 # $MAIL_FROM <- From: header value

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

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

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

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

1227 sender=${SENDER:-$admin}

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

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

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

1231 }

1232 smail_queue_flush() {

1233 # $1=timelimit

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

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

1236 SELECT rowid FROM smailq

1237 GROUP BY rcpts, subj

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

1239 EOF

1240 )

1241 for rid in $rowids; do

1242 sq $workdb \

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

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

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

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

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

1248

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

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

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

1252 .separator "\n" "------------------\n\n"

1253 SELECT time, text FROM smailq

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

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

1256 ORDER by time;

1257 EOF

1258 then

1259 echo rowid=$rid

1260 cat <<-EOF | sq $workdb

1261 DELETE FROM smailq

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

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

1264 EOF

1265 fi

1266 fi

1267 done

1268 }

1269 smail_queue() {

1270 # $1=Rcpts, $@=subj

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

1272 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1281 EOF

1282 )

1283 ## XXX: Adhoc load mitigation

1284 case "$now" in

1285 *[01])

1286 err flush_queue=$mintime

1287 smail_queue_flush ;;

1288 esac

1289 fi

1290 }

1291 setviastring() {

1292 table=par

1293 oifs="$IFS"

1294 IFS="&"

1295 for us in $1; do

1296 k=${us%%=*}

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

1298 setpar "$k" "string" "$v"

1299 done

1300 IFS="$oifs"

1301 }

1302 checkdomain() (

1303 # Check the validity of domain by referring DNS

1304 item=$1

1305 err checkdomain $1

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

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

1308 )

1309 pwcheck() {

1310 # $1=passwd

1311 dbpswd=`getpwfield $user pswd`

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

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

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

1315 }

1316 mypwhash() {

1317 mycrypt "`cat`" `genrandom 5`

1318 }

1319 wasureta() {

1320 user=$1

1321 if ! checkdomain $user; then

1322 contenttype; echo

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

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

1325 putfooter

1326 exit 0

1327 fi

1328 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1330 encpswd=`echo $newpswd|mypwhash`

1331 dbsetbyid user $user pswd "$encpswd"

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

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

1334 $msgdir/mail-newaccount.m4 \

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

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

1337 }

1338 checkauth() {

1339 user=`getpar user`

1340 skc=`getpar skey` # from cookie

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

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

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

1344 if chkskey "$skc"; then

1345 return 0

1346 fi

1347 fi

1348 pswd=`getpar pswd`

1349 quser=`sqlquotestr "$user"`

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

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

1352 return 4 # 4=server too heavy

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

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

1355 return 2 # 2=login fail

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

1357 wasureta "$user"

1358 return 1 # wasureta error

1359 fi

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

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

1362 if pwcheck "$pswd"; then

1363 newsession=`genrandom 34`

1364 if setskey "$user" "$newsession" &&

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

1366 gencookie "user=$user" "skey=$newsession"

1367 return 0

1368 else

1369 return 4 # Heavy load??

1370 fi

1371 fi

1372 err "Login failed: [$user]"

1373 return 2 # Password mismatch

1374 }

1375 showlogin() {

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

1377 test -z "$args" && resetskey

1378 s4name=${S4NAME:-s4}

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

1380 cat $layout/login.m4.html

1381 echo '</body></html>'

1382 ) | _m4 \

1383 -D_BODYCLASS_="login" \

1384 -D_TITLE_="$s4name" \

1385 -D_SYSNAME_="Welcome to $s4name" \

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

1387 exit 0

1388 }

1389 dologin() {

1390 checkauth

1391 st=$?

1392 if [ $st != 0 ]; then

1393 contenttype; echo

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

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

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

1397 showlogin # and EXIT

1398 fi

1399 }

1400

1401 # Do instant jobs here

1402 dbsetup

1403 trap cleanup INT HUP EXIT TERM PIPE

1404 # trap cleanup INT HUP

1405

1406 err() {

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

1408 }

1409

1410 cgiinit() {

1411 tmpd=`tmpd=$tmpdir mktempd`

1412 tmpf=$tmpd/stream.$$

1413 tmpfiles=$tmpfiles" $tmpd"

1414 addsession $session

1415 getcookie

1416 case "$REQUEST_METHOD" in

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

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

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

1420 # cat > $tmpf # too much?

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

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

1423 s="`cat $tmpf`"

1424 ;;

1425 esac

1426 case "$CONTENT_TYPE" in

1427 *boundary*)

1428 bndry=${CONTENT_TYPE#*boundary=}

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

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

1431 do

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

1433 #echo u=$us

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

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

1436 # err k=$k v=$v

1437 case "$k" in

1438 *:filename)

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

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

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

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

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

1444 :

1445 fi

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

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

1448 case "$mimetype" in

1449 [Ii]mage/x-xcf)

1450 bzip2 "$tmpd/$v"

1451 v=${v}.bz2

1452 ;;

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

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

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

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

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

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

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

1460 v=$vjpg

1461 else

1462 mimetype="Not supported"

1463 fi

1464 ;;

1465 [Ii]mage/*)

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

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

1468 ;;

1469 esac

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

1471 then

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

1473 continue

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

1475 replpar text string \

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

1477 continue

1478 fi

1479 ;;

1480 *)

1481 type='string'

1482 ;;

1483 esac

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

1485 setpar "$k" "$type" "$v"

1486 done

1487 ;;

1488 *)

1489 setviastring "$s"

1490 ;;

1491 esac

1492 }

1493 email4group() {

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

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

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

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

1498 sql="WITH

1499 grpemails AS (

1500 SELECT gname, user, val email

1501 FROM grp_mem NATURAL JOIN grp_mem_s

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

1503 useremails AS (

1504 SELECT user.name, val email

1505 FROM user

1506 LEFT JOIN user_m

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

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

1509 FROM useremails u LEFT JOIN grpemails g

1510 ON u.name=g.user

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

1512 query "$sql"

1513 }

1514 email4groupbyuid() {

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

1516 g=$1; shift

1517 uids=`echo "$@"`

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

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

1520 email4group "$g" `query "$sql"`

1521 }

1522 myemail4group() {

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

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

1525 }

1526 collectmembersbyid() {

1527 # Collect user names of group specified by grid

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

1529 query "SELECT user FROM grp_mem \

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

1531 }

1532 collectmembersbyid() {

1533 # Collect user names of group name

1534 qgrp=`sqlquote "$1"`

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

1536 }

1537 collectgecosesbyid() {

1538 # Collect user gecoses of group

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

1540 query<<-EOF

1541 SELECT gecos

1542 FROM gecoses

1543 WHERE name IN (SELECT user FROM grp_mem

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

1545 EOF

1546 }

1547 collectemail() (

1548 # Collect email addresses for group $1

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

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

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

1552 for e; do

1553 if isuser "$e"; then

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

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

1556 else

1557 qgrp=`sqlquote "$e"`

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

1559 gmem="grp_mem"

1560 else

1561 tm=`sqlquote "$TEAM"`

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

1563 fi

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

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

1566 $gmem g left join grp_mem_s s

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

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

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

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

1571 query "$sql"

1572 fi

1573 done

1574 )

1575 sendinvitation() (

1576 # $1=email

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

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

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

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

1581 gecos=`gecos`

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

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

1584 _m4 -D_URL_="$urlbase" \

1585 -D_USER_="$name" \

1586 -D_EMAIL_="$1" \

1587 -D_REGIST_="$regist" \

1588 -D_ADMIN_="$admin" \

1589 $msgdir/mail-invite.m4 \

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

1591 return 0

1592 )

1593 emaildomaincheck() {

1594 case "$1" in

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

1596 *@*)

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

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

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

1600 return 2

1601 fi

1602 return 0

1603 ;;

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

1605 esac

1606 }

1607 invite() {

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

1609 case "$email" in

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

1611 *@*)

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

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

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

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

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

1617 elif sendinvitation $email; then

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

1619 else # Cannot be reached here

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

1621 fi ;;

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

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

1624 esac

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

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

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

1628 fi

1629 if [ -f $invite_policy ]; then

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

1631 else

1632 pol="$invite_policy"

1633 fi

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

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

1636 -D_INVITE_POLICY_="$pol" \

1637 $layout/html.m4.html $layout/invite.m4.html

1638 }

1639 regist() {

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

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

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

1643 echo "bye bye" | html p

1644 reutrn

1645 fi

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

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

1648 cat<<EOF

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

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

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

1652 EOF

1653 return

1654 fi

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

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

1657 # Fake login password to wasureta

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

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

1660 wasureta $email

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

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

1663 addsession $1 # for removal after 1 minute

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

1665 $layout/login.m4.html

1666 return

1667 }

1668 group_safename() {

1669 # Convert $1 to safe group name

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

1671 }

1672 groupupdate() {

1673 gname=`getpar gname`

1674 qgname=`sqlquote "$gname"`

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

1676 # See ALSO same job in showgroup()

1677 newgname=`group_safename "$gname"`

1678 err newgname=$newgname

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

1680 err NewGNAME: gname=$newgname

1681 gname=$newgname

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

1683 replpar gname string "$gname"

1684 fi

1685 # Name confliction check

1686 parow=`getpar rowid`

1687 ## err parow=$parow

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

1689 query "BEGIN EXCLUSIVE;"

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

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

1692 if [ $count -gt 0 ]; then

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

1694 query "END;"

1695 return

1696 fi

1697 par2table $formdir/grp.def

1698 query "END TRANSACTION;"

1699 # Remove orphan

1700 : <<EOF

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

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

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

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

1705 EOF

1706 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1710 sql="delete from blog where id in

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

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

1713 query "$sql";

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

1715 return # and return

1716 fi

1717 fi

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

1719 fi

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

1721 grid=$(query $sql)

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

1723 grp $grid

1724 }

1725 groupclone() {

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

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

1728 case "$1" in

1729 */noteam)

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

1731 esac

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

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

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

1735 return

1736 fi

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

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

1739 return

1740 fi

1741 i=0

1742 while true; do

1743 copy="-copy$i"

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

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

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

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

1748 i=$((i++))

1749 continue

1750 fi

1751 break

1752 done

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

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

1755 query<<-EOF

1756 BEGIN;

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

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

1759 SELECT $newqname, key, val

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

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

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

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

1764 -- Copy members and their configuration --

1765 REPLACE INTO grp_mem SELECT $newqname, user

1766 FROM grp_mem WHERE gname=$qgrp;

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

1768 FROM grp_mem_s WHERE gname=$qgrp;

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

1770 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1771 -- Copy administrators --

1772 REPLACE INTO grp_adm SELECT $newqname, user

1773 FROM grp_adm WHERE gname=$qgrp;

1774 COMMIT;

1775 EOF

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

1777 STOPCLONEMSG=1 groupconf "$newrowid"

1778 }

1779 groupman() {

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

1781

1782 GF_STAGE="grpconf"

1783 GF_STAGE=groupupdate

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

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

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

1787 -D_DUMPTABLE_="syscmd(cat)" \

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

1789 }

1790 userconf() {

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

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

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

1794 }

1795 groupconf() {

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

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

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

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

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

1801

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

1803 grp=`getgroupbyid $rowid`

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

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

1806 return

1807 fi

1808

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

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

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

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

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

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

1815 <div>

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

1817 <table>

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

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

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

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

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

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

1824 </table>

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

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

1827 </div>

1828 EOF

1829 fi

1830 }

1831 mems() {

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

1833 kwd=`getpar kwd`

1834 listmember $kwd

1835 }

1836 grps() {

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

1838 kwd=`getpar kwd`

1839 listgroup $kwd \

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

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

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

1843 $layout/form+dump.m4.html

1844 }

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

1846 gpg=`getpar grp`

1847 grid=${1:-$gpg}

1848 grp=`getgroupbyid "$grid"`

1849 ## . ./s4-blog.sh

1850 jg=`getpar joingrp`

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

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

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

1854 fi

1855 htmlheader=$layout/html.m4.html

1856 showgroup "$grid"

1857 }

1858 sql4interestblogs() {

1859 cat<<EOF

1860 CREATE TEMPORARY VIEW interestblogs AS

1861 SELECT blog.rowid rid, id, author

1862 FROM blog

1863 NATURAL JOIN

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

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

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

1867 WHEN (SELECT user FROM grp_mem

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

1869 THEN 0

1870 ELSE 1

1871 END;

1872 EOF

1873 }

1874 listnewblogsql() { # $1=user

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

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

1877 newdays=${newdays:-14}

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

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

1880 WHERE user='$user'

1881 AND tblrowid IN

1882 ($blogreadflagrowid,

1883 $blogcutoffflagrowid)),

1884 $basetime -- "0"

1885 );"`

1886 cat<<EOF

1887 `sql4interestblogs`

1888 WITH article_ctime as (

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

1890 FROM article join article_s s using(id)

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

1892 GROUP BY id

1893 ), blog_title_owner as (

1894 SELECT blg.rid brid, id,

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

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

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

1898 ), blogall as (

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

1900 ), news as (

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

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

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

1904 FROM blogall bl

1905 LEFT JOIN

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

1907 ON bl.brid=al.tblrowid

1908 WHERE atime < bl.ctime

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

1910 LIMIT 10

1911 ) SELECT brid LINK, "新着",

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

1913 ctime, title,

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

1915 FROM news;

1916 EOF

1917 }

1918

1919 search_form() {

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

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

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

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

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

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

1926 @week → 最近一週間

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

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

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

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

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

1932 auth=""

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

1934 case "$1" in

1935 author=*)

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

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

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

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

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

1941 ;;

1942 grp=*)

1943 a=`echo "${1#grp=}"` # group name cannot have quoting marks

1944 auth="<input type=\"hidden\" name=\"owner\" value=\"$a\">"

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

1946 ;;

1947 esac

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

1949 cat<<-EOF

1950 <div class="right">

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

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

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

1954

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

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

1957

1958 </form>

1959 </div>

1960 EOF

1961 }

1962

1963 imgsrc_cache() (

1964 # $1 = directory for cache'ing

1965 # $2 = table (user_m or grp_m)

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

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

1968 dir="$1" tbl="$2"

1969 keycond="$3"

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

1971 ORDER BY rowid DESC LIMIT 1"

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

1973 tmpf=$tmpd/imgsrc_cache.$$

1974 case "$4" in

1975 [Ss]) size=S ;;

1976 [Oo]) size=O ;;

1977 *) size=M ;;

1978 esac

1979 # ImageCache filename storing schema:

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

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

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

1983 valtype=`query "$sql0"`

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

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

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

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

1988 case "$filetype" in

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

1990 esac

1991 fi

1992 cacheimg_S=$dir/S_$filename

1993 cacheimg_M=$dir/M_$filename

1994 cacheimg_O=$dir/$filename

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

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

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

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

1999 && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then

2000 # if cache is fresh and has the same checksum,

2001 echo "<img src=\"$cacheimg\">"

2002 else

2003 fifo=`mktemp "$tmpf.fifo.XXXXXXX"`

2004 rm -f $fifo # Safe, because $tmpf is in mktemp dir.

2005 fifo2=$fifo.2

2006 mkfifo $fifo $fifo2

2007 fmt=${filename##*.}

2008 ## [[ NOTE ]]

2009 ## a. convert oldimage newimage

2010 ## b. convert oldimage fmt:- | convert - newimage

2011 ## b is much smaller than a

2012 cat $tmpf | unhexize \

2013 | tee $fifo \

2014 | convert -define ${fmt}:size=${iconxy_M} \

2015 -resize ${iconxy_M}'>' - ${fmt}:- \

2016 | tee $fifo2 \

2017 | convert - "$cacheimg_M" &

2018 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

2019 -resize ${iconxy_S}'>' - ${fmt}:- \

2020 | convert - "$cacheimg_S" &

2021 printf '%s' "<img src=\"data:${filetype},"

2022 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

2023 echo '">'

2024 echo "$sum" > $sumfile

2025 fi

2026 ## Now preparing cache image, done.

2027 ## Store this information to DB

2028 stbl=${tbl%_m}_s # user_s or grp_s

2029 pkey=${keycond%%=*} # Primary Key name

2030 pval=${keycond#*=} # Primary Key value

2031 query <<-EOF

2032 REPLACE INTO $stbl($pkey, key, type, val)

2033 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2034 EOF

2035 )

2036

2037 showhome() {

2038 # $1=userRowIdToShow

2039 err showhome \$1=$1

2040 case "$1" in

2041 *@*) uname=`getvalbypkey user name "$1"` ;;

2042 *) uname=`getvalbyid user name $1` ;;

2043 esac

2044 ## err ShowHome: uname=$uname

2045 td=`getcachedir home/"$1"`

2046 gecos=`gecos "$uname"`

2047 ## err SH:gecos=$gecos

2048 GF_VIEWONLY=1

2049 cond="gname in (select gname from grp_mem where user='$uname')"

2050 search_form_args=""

2051 if [ x"$user" = x"$uname" ]; then

2052 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2053 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> /

2054 <a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</a>"

2055 # Display folders

2056 sql="select count(id) from article_m where id

2057 in (select id from article where author='$user')

2058 and type like 'file:%';"

2059 ## err nfile-sql=`echo "$sql"`

2060 nfile=`query "$sql"`

2061 # err nfile=$nfile

2062 if [ $nfile -gt 0 ]; then

2063 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2064 title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"

2065 fi

2066 else

2067 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2068 GROUP BY user;"`

2069 usermenu="<p>Last seen on $latestlog</p>"

2070 search_form_args="author=$uname"

2071 fi

2072 . ./s4-blog.sh

2073

2074 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2075 search_form "$search_form_args" > $sf

2076 printf "%s さん" "$gecos" > $tf

2077 { echo "<div class=\"noprofimg\">"

2078 viewtable $formdir/user.def user $1

2079 echo "</div>"

2080 } > $pf

2081

2082 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2083 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2084 imf=$tmpd/profimg.$$; touch $imf

2085 if [ -n "$img" ]; then

2086 if true; then

2087 tbl=user_m

2088 enticond="name='$uname'"

2089 imgsrc_cache "$td/main" user_m "$enticond" M

2090 else

2091 { printf '%s' "<IMG src=\"data:${img#file:},"

2092 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2093 | sed 's/$..$/%\1/g'

2094 echo '">'

2095 }

2096 fi > $imf

2097 fi

2098 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2099 val='$uname';"`

2100 ## REMOVE This comment block until 2019/7/1

2101 ## err "----- `gdate +%FT%T.%3N` ------------C"

2102 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2103 listblog $uname > $bf

2104 ## unset ddd

2105 ## err "----- `gdate +%FT%T.%3N` ------------D"

2106

2107 hometail=$tmpd/tail.$$

2108 mkfifo $hometail

2109

2110 #Calling listgroupbytable, originally here

2111

2112 (

2113 # Display Most Recent Entry

2114 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2115 shortval=${shortval:-val}

2116

2117 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2118 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2119 ctime,

2120 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2121 (SELECT gecos FROM gecoses

2122 WHERE name=(SELECT val FROM blog_s

2123 WHERE key='owner' AND id=b.id)) owner,

2124 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2125 FROM blog b

2126 JOIN

2127 (SELECT distinct blogid, a.id aid, max(val) ctime

2128 FROM article a, article_s s

2129 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2130 GROUP BY blogid ORDER BY val DESC LIMIT 50

2131 ) m

2132 ON b.id=m.blogid;"

2133 # This should be as follows

2134 : <<EOF

2135 WITH arts AS(

2136 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2137 a.blogid, a.id id, s.val ctime

2138 FROM article a NATURAL JOIN article_s s

2139 WHERE s.key = 'ctime' AND a.author='$user'

2140 GROUP by s.id

2141 )

2142 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2143 FROM arts a0

2144 JOIN

2145 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2146 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2147 ORDER BY ctime DESC LIMIT 50;

2148 EOF

2149

2150 cat<<-EOF

2151 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2152 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2153 <div class="lcto">

2154 `DT_VIEW=replyblog dumptable html blog`

2155 </div>

2156 EOF

2157 unset DT_SQL

2158 if [ x"$user" = x"$uname" ]; then

2159 # Display NEWS

2160 # 2016-06-26

2161 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2162 acclog blog $blogreadflagrowid

2163 # echo "全部既読にしました" | html p

2164 fi

2165 # 2016-02-19 Counting NEWS without using dumptable.

2166 sql=`listnewblogsql "$user"`

2167 # echo "$sql" > tmp/listnew

2168 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2169 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2170 cont=$((cont-1))

2171 err newcount=$cont

2172 if [ $cont -gt 0 ]; then

2173 #echo "全体の新着記事${cont}傑" | html h2

2174 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2175 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2176 cat<<-EOF | html form 'action="?home"'

2177 `cgi_checkbox readchk yes 'id="read"'`<label

2178 for="read">新着ふくめて全部読んだことにする</label>

2179 `cgi_submit '確定'`

2180 `cgi_hidden read yes`

2181 EOF

2182 echo "$new10 "

2183 echo "</div>"

2184 else # If news is 0, set log cut off flag

2185 acclog blog $blogcutoffflagrowid # for speed

2186 fi

2187 else # Not My Home ($user != $uname)

2188 : # DT_SQL=

2189 fi

2190 ) > $hometail & # Is background call safe to m4??

2191 #

2192 listgroupbytable $formdir/grp.def $cond |

2193 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2194 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2195 -D_PROFIMG_="spaste(\`$imf')" \

2196 -D_BLOGS_="spaste(\`$bf')" \

2197 -D_SEARCH_="spaste(\`$sf')" \

2198 -D_NBLOG_="$nblog" \

2199 -D_GROUPS_="syscmd(\`cat')" \

2200 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2201 $layout/html.m4.html $layout/home.m4.html

2202

2203 # Record access log

2204 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2205 }

2206 commission() { # $1=grp-rowid $2=user-rowid

2207 contenttype; echo

2208 ## err commission: "$@"

2209 gname=`getgroupbyid $1`

2210 echo "グループ $gname 管理者委任" \

2211 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2212 if [ -n "$2" ]; then

2213 grp_reg_adm "$@"

2214 else

2215 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2216 fi

2217 }

2218 listgroupbytable() {

2219 # $1=deffile $2...=condition

2220 tagline=`grep :tag: $1`; shift

2221 and="${1:+and }" where=${1:+where }

2222 href="<a href=\"$myname?grp+"

2223 echo '<div class="listgroup">'

2224 NGsql="select distinct tag from\

2225 (select gname, max(case key when 'tag' then val end) as tag, \

2226 max(case key when 'ctime' then val end) as ctime\

2227 from grp_s group by gname order by ctime);"

2228 sql="select val from grp_s where key='tag' $and$* group by val;"

2229 ## err ListGRP: query sql="$sql"

2230 for tag in `query "$sql"`

2231 do

2232 ## err ListGrp: tag=$tag

2233 tn=${tagline%%=${tag}*}

2234 tn=${tn##*[ :]}

2235 sql="select rowid||':'||gname as 'グループ名',説明 from

2236 (select (select rowid from grp g where g.gname=grp_s.gname)

2237 as rowid,

2238 gname,

2239 max(case key when 'gecos' then val end) as '説明',

2240 max(case key when 'tag' then val end) as 'tag',

2241 max(case key when 'mtime' then val end) as mtime from grp_s

2242 $where$* group by gname having tag='$tag' order by mtime desc);"

2243 ## err PersonalGroupList= `echo $sql`

2244 echo "<h2>$tn</h2>"

2245 echo '<table class="b listgroup">'

2246 sq -header -html $db "$sql" \

2247 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2248 echo '</table>'

2249 done

2250 echo '</div>'

2251 }

2252 iconhref() (

2253 # $1=icon-file, $2=Href $3=title $4...=anchor

2254 data=`percenthex "$1"`

2255 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2256 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2257 href=$2; title=$3; shift 3

2258 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2259 )

2260 iconhref2() (

2261 # $1=icon-file, $2=Href $3=title $4...=anchor

2262 src=$1

2263 href=$2; title=$3; shift 3

2264 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2265 )

2266 listentry() (

2267 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2268 # Referring variable $iamowner=$grp to attach owner-request links

2269 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2270 cond='' hiddens=''

2271 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2272 if [ -z "$offset" ]; then

2273 offset=`getpar start`; offset=${offset%%[!0-9]*}

2274 offset=$((offset-1))

2275 fi

2276 offset=$((offset + 0)) # change to numeric forcibly

2277 [ $offset -lt 0 ] && offset=0

2278 limit=$listentlimit

2279 dir=`getcachedir "$1"`

2280 if [ x"$1" = x"user" ]; then

2281 hrb="$myname?home"

2282 deficon=person-default.png

2283 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2284 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2285 gcs=gecos

2286 else # if group

2287 hrb="$myname?grp"

2288 deficon=group-default.png

2289 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2290 gcs=name

2291 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2292 if [ -n "$tagline" ]; then

2293 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2294 ## err tagconv=$tagconv

2295 fi

2296 fi

2297 if [ ! -d $dir ]; then

2298 mkdir -p $dir

2299 fi

2300 if [ ! -s $dir/$deficon ]; then

2301 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2302 fi

2303 if [ -n "$2" ]; then

2304 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2305 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2306 fi

2307 tag=`getpar tag` tag2=`getpar tag2`

2308 if [ x"$tag" = x"NULL" ]; then

2309 tag="" tag2=""

2310 fi

2311 if [ -n "$tag$tag2" ]; then

2312 tag=${tag:-$tag2}

2313 qtag=`sqlquote "$tag"`

2314 cond2="tag=$qtag"

2315 fi

2316 if [ -n "$cond1$cond2" ]; then

2317 cond="$cond1${cond2:+ AND $cond2}"

2318 cond="WHERE ${cond# AND }"

2319 fi

2320

2321 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2322 # grpは呼出し元の動的スコープ変数でよくないな...

2323 ##qgrp=`sqlquote $grp`

2324 getgrp="(select gname from grp where rowid=${rowid:--1})"

2325 sql="select a.rowid, a.$link,

2326 coalesce(b.$gcs, a.$nm) as nick,

2327 quote(a.$nm) as qname,

2328 (SELECT val FROM ${tbl}_s

2329 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2330 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2331 || case when a.$nm in (select gname from grp)

2332 then printf('(%d名)',

2333 (select count(user) from grp_mem where gname=a.$nm))

2334 else ' <'||a.$nm||'>'

2335 end

2336 as name,

2337 b.tag,

2338 case when a.$nm in (select user from grp_adm

2339 where gname=$getgrp) then '管理者'

2340 when '$user' in (select user from grp_adm where gname=a.$nm)

2341 then 'ADMIN'

2342 when '$user' in (select user from grp_mem where gname=a.$nm)

2343 then 'Member'

2344 when '$iamowner' = '' then ''

2345 else ',not='||a.rowid end as ownerlink,

2346 CASE '$entity'

2347 WHEN 'グループ'

2348 THEN coalesce(

2349 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2350 'open')

2351 ||

2352 CASE WHEN '$user'

2353 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2354 THEN ' ismember'

2355 ELSE ''

2356 END

2357 ELSE 'user'

2358 END regmode

2359 from $tbl a left join

2360 (select $nm as name,

2361 max(case key when 'gecos' then val end) as gecos,

2362 max(case key when 'tag' then val end) as tag,

2363 max(case key when 'mtime' then val end) as mtime,

2364 max(case key when 'wtime' then val end) as wtime,

2365 max(case key when 'login' then val end) as login

2366 from ${tbl}_s group by $nm)

2367 b on a.$nm=b.name $cond $3

2368 order by b.wtime desc, b.login desc,

2369 b.mtime desc, b.tag desc, a.rowid asc"

2370 # Give precedence to newer maintained groups (2016-09-24)

2371 # Note that mtime is stored only in grp_s.

2372 ## err LE:sql.1="$sql"

2373 total=`query "with x as ($sql) select count(*) from x;"`

2374 echo "${entity} 一覧" | html h2

2375 echo '<div class="listentry">' # List-entry div

2376 # Show owner/member filter button

2377 METHOD=GET

2378 hiddens="$hiddens

2379 `cgi_hidden kwd \"$kwd\"`

2380 `cgi_hidden stage \"$stage\"`"

2381 if [ x"$tbl" = x"grp" ]; then

2382 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2383 fh="<select name=\"tag\">$nl"

2384 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2385 for l in $args; do

2386 val=${l#*=} tname=${l%=*}

2387 if [ x"$val" = x"$tag" ]; then

2388 s=" selected"

2389 selectedtags="(種別[${tname}]のみ)"

2390 else

2391 s=""

2392 fi

2393 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2394 done

2395 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2396 cat<<-EOF

2397 <form action="$myname" method="$METHOD">

2398 </form>

2399 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2400 for="ismembtn">参加中以外隠す</label>

2401 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2402 for="isadmbtn">管理者参加以外隠す</label>

2403 EOF

2404 # limit=3

2405 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2406 fi

2407 if [ $total -gt $limit ]; then

2408 echo '<div>'

2409 METHOD=GET cgi_form $stage <<EOF

2410 $form

2411 <label>次の語を含む${entity}で検索:

2412 `cgi_text kwd "$kwd"`</label>

2413 $hiddens

2414 EOF

2415 echo '</div>'

2416 else

2417 echo $selectedtags | html p

2418 fi

2419 cat<<EOF

2420 <form action="$myname" method="$METHOD">

2421 <p>${total}件中の<input class="hidesub" type="text" name="start"

2422 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2423 <input type="submit" value="確定"></p>

2424 </form>

2425 EOF

2426 if [ $((offset+limit)) -lt $total ]; then

2427 nextbtn=$(

2428 cat<<EOF

2429 <div class="right clear"><form action="$myname" method="$METHOD">

2430 `cgi_submit 次の${limit}件`

2431 $hiddens

2432 `cgi_hidden offset $((offset + limit))`</form></div>

2433 EOF

2434 )

2435 fi

2436 if [ $offset -gt 0 ]; then

2437 prevbtn=$(

2438 cat<<EOF

2439 <form action="$myname" method="$METHOD">

2440 `cgi_submit 前の${limit}件`

2441 $hiddens

2442 `cgi_hidden offset $((offset - limit))`</form>

2443 EOF

2444 )

2445 fi

2446 pnbtn="$nextbtn$prevbtn"

2447 echo $pnbtn

2448

2449 ## err ListEntry: `echo "$sql"\;`

2450 # sq $db here??? 2016-11-28

2451 query "$sql limit $limit ${offset:+offset $offset};" \

2452 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2453 # err name=$name owner=$ownerp lnk=$lnk

2454 # err newlnk=$lnk regmode=$regmode

2455 icondir=$dir/$id

2456 # Pick up only last icon

2457 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2458 <p class=\"tag _$tag\">$tag</p>" \

2459 | _m4 $tagconv

2460 if [ -n "$NOSPEEDUP" ]; then

2461 files=`getvalbyid $tbl profimg $id $icondir`

2462 if [ -n "$files" ]; then

2463 icon=`echo "$files"|tail -1`

2464 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2465 else

2466 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2467 fi

2468 elif [ -n "$icon" -a -s "$icon" ]; then

2469 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2470 else

2471 cond="$nm=$qname"

2472 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2473 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2474 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2475 # err "img=[$img]"

2476 if [ -n "$img" ]; then

2477 echo "<a href=\"$hrb+$lnk\">"

2478 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2479 echo "</a>"

2480 else

2481 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2482 fi

2483 fi

2484 echo "<br>$name${ownerp:+<br>($ownerp)}"

2485 echo "</div>"

2486 done

2487 echo "</div>" # End of List-entry div

2488 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2489 )

2490 listmember() {

2491 listentry user "$@"

2492 }

2493 listgroup() {

2494 listentry group "$@"

2495 }

2496 hexteams() { # $1=gname, $2(optional)=user

2497 cond=${2:+" AND user='$2'"}

2498 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2499 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2500 }

2501 showgroup() { # $1=group-rowid

2502 if [ -z "$1" ]; then

2503 grid=`getpar grid`

2504 grid=${grid%%[!0-9]*}

2505 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2506 else

2507 grid=$1

2508 fi

2509 grp=`getgroupbyid $grid`

2510 qgrp=`sqlquote "$grp"`

2511 htmlgrp=`echo "$grp"|htmlescape`

2512 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2513 if isgroup "$grp"; then

2514 tf=$tmpd/title.$$

2515 sf=$tmpd/search.$$

2516 bodyclass=`query "SELECT val FROM grp_s

2517 WHERE gname=$qgrp AND key='regmode';"`

2518 if ismember "$user" "$grp"; then

2519 ismember="ismember"

2520 bodyclass="$bodyclass${bodyclass:+ }ismember"

2521 else

2522 ismember="" # bodyclass="group"

2523 fi

2524 bodyclass="$bodyclass grouphome"

2525 echo "<div class=\"search\">`search_form grp=\"$grp\"`</div>"> $sf

2526 echo "グループ $htmlgrp" > $tf

2527

2528 showgroupsub $formdir/grp.def "$grid" | \

2529 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2530 -D_FORM_="syscmd(\`cat')" \

2531 -D_BODYCLASS_="$bodyclass" \

2532 -D_DUMPTABLE_="" \

2533 $htmlheader $sf $layout/form+dump.m4.html

2534 # $htmlheader $layout/form+dump.m4.html

2535 # $htmlheader is defined in grp()

2536 else # if $grp is removed at par2table

2537 listgroup

2538 fi

2539 }

2540 showgroupsub() {

2541 # $1=def-file $2=group-rowid

2542 # Using $ismember

2543 rowid=$2

2544 grp=`getgroupbyid $2`

2545 qgrp=`sqlquote "$grp"`

2546 td=`getcachedir grp/"$2"`

2547 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2548 if [ -z "$rowid" ]; then

2549 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2550 #grp=`sq $db "select gname from grp where rowid=$grp"`

2551 echo "showgroupsub: invalid argument($1 $2)" | html p

2552 return

2553 fi

2554 val=`getvalbyid grp profimg $rowid $tmpd`

2555 enticond="gname=$qgrp"

2556 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2557 if [ -n "$img" ]; then

2558 cat<<-EOF

2559 <p class="groupimg">

2560 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2561 EOF

2562 fi

2563 echo "<div class=\"noprofimg\">"

2564 viewtable $1 grp $rowid

2565 echo "</div>"

2566 if isgrpowner "$user" "$grp"; then

2567 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2568 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2569 iamowner=$rowid

2570 colmd=" mode"

2571 fi

2572 if [ -n "$ismember" ]; then

2573 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2574 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2575 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2576 cat<<-EOF

2577 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2578 href="?blog+$rowid">グループの新規話題作成</a>

2579 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2580 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2581 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2582 <div class="fold clear">

2583 `cgi_checkbox send yes id="send"`<label

2584 for="send">グループ全員にメッセージ送信</label>

2585 <div>

2586 `cgi_textarea message "" "cols=60"`

2587 `cgi_submit 送信`

2588 `cgi_reset リセット`

2589 </div>

2590 `cgi_hidden grp $rowid`

2591 </div></form>

2592 EOF

2593 fi

2594 # 加入ボタン + 加入者リスト

2595 if [ -n "$ismember" ]; then

2596 ismem='checked' state="(参加中)"

2597 else

2598 nomem='checked' state="(現在非加入)"

2599 fi

2600 # このグループでの加入アドレス

2601 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2602 and key='email';"`

2603 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2604 ## and key='email';"

2605 ##err email=$eml

2606 cat <<EOF

2607 <div class="fold clear">

2608 `cgi_checkbox reg yes id="reg"`<label

2609 for="reg">自身の加入状態を操作する</label>$state

2610 <div>

2611 EOF

2612 cgi_form grp <<EOF

2613 <p>このグループに</p>

2614 <table class="b">

2615 <tr><th>メンバーとして</th><td>

2616 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2617 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2618 <tr><th>参加する場合のメイルアドレス<br>

2619 <small>(メインのアドレスとは違うものにする場合に記入<br>

2620 同じでよい場合は空欄に)</small></th>

2621 <td>`cgi_text email $eml`</td></tr>

2622 </table>

2623 `cgi_hidden grp $rowid`

2624 EOF

2625 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2626 echo "moderated (承認加入の)グループなので実際に参加できるのは

2627 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2628 fi

2629 echo '</div></div>'

2630 echo '<h2>話題一覧</h2>'

2631 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2632 cat<<-EOF

2633 <form class="summary" action="$myname" title="$thelp">

2634 `cgi_hidden owner "$grp"`

2635 `cgi_hidden kwd "@week"`

2636 `cgi_hidden stage searchart`

2637 `cgi_submit "一週間のまとめ"`

2638 </form>

2639 EOF

2640 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2641 colstate="state:稼動状態:frozen=rowclass=凍結"

2642 DT_CHLD=article:blogid \

2643 DT_QOWNER="$qgrp" \

2644 DT_VIEW=replyblog dumptable html blog \

2645 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2646

2647 getgname="(select gname from grp where rowid=$rowid)"

2648 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2649 cm="?commission+$rowid"

2650 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2651 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2652 # team list

2653 hexteams=`hexteams "$grp"`

2654 if [ -n "$hexteams" ]; then

2655 echo "チーム一覧" | html h2

2656 echo '<div class="dumptable"><table class="b">'

2657 sq $db -html -header<<-EOF

2658 SELECT val TEAM,

2659 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2660 MEMBERS

2661 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2662 EOF

2663 echo '</table></div>'

2664 fi

2665 }

2666 grp_getbodyclass() {

2667 # Get css class name for document.

2668 # `moderated' for moderated groups

2669 # `ismember' for groups where user belongs

2670 # $1=GroupName (w/o quote)

2671 # $user=userNameCurrentlyLogin

2672 ## err grp_getbodyclass: 1="$1"

2673 qgrp=`sqlquote "$1"`

2674 query<<-EOF

2675 SELECT coalesce(

2676 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2677 'open')

2678 ||

2679 CASE WHEN '$user'

2680 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2681 THEN ' ismember'

2682 ELSE ''

2683 END;

2684 EOF

2685 }

2686 grpaction() { # $1=group-rowid

2687 err GRP_ACTION:IN

2688 grid=${1:-`getpar grp`}

2689 grp=`getgroupbyid "$grid"`

2690 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2691 if [ -z "$grp" ]; then

2692 echo "無効な指定です。" | html p; return

2693 fi

2694 if ! ismember $user "$grp"; then

2695 echo "加入者のみに許可された操作です。" | html p; return

2696 fi

2697 echo "グループ $grp 個別選択操作" \

2698 | _m4 -D_TITLE_="syscmd(\`cat')" \

2699 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2700 $layout/html.m4.html

2701

2702 isowner=""

2703 isgrpowner "$user" "$grp" && isowner="yes"

2704 usel=`getpar usel`

2705 if [ -n "$usel" ]; then

2706 uids=$(echo `echo $usel`|tr ' ' ',')

2707 ## err grpaction-1: grp=$grp, `echo $sql`

2708 text=`getpar text|tr -d '\r'`

2709

2710 rm=`getpar rm` cfm=`getpar confirm`

2711 ## err rm=$rm cfm=$cfm

2712 if [ x"$rm" = x"yes" ]; then

2713 if [ "$isowner" ]; then

2714 if [ x"$rm$cfm" = x"yesyes" ]; then

2715 # Eliminate

2716 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2717 for tbl in grp_mem grp_mem_s grp_mem_m; do

2718 sql="delete from $tbl $cond;"

2719 # echo "sql=$sql"

2720 query "$sql"

2721 err rmGRPuser "$sql"

2722 done

2723 num=`query "select count(*) from user where rowid in ($uids);"`

2724 #err num=$num

2725 if [ 0$num -gt 0 ]; then

2726 sql="select coalesce(b.val,a.name) from user a left join \

2727 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2728 # err `echo "$sql"`

2729 html pre<<EOF

2730 以下の${num}名のグループ $grp 登録を解除しました。

2731 `query "$sql"`

2732 EOF

2733 fi

2734 else

2735 echo "確認のチェックがないのでやめておきます。" | html p

2736 return

2737 fi

2738 else # not Group Owner

2739 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2740 return

2741 fi

2742 cat<<EOF

2743

2744 EOF

2745 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2746 if [ -z "$text" ]; then # if msg is empty

2747 echo "なにかメッセージを..." | html p

2748 return 0

2749 fi

2750 gecos=`gecos $user`

2751 safegc=`echo "$gecos" | tr -d '<>@'`

2752 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2753 fromad=`myemail4group "$grp"`

2754 ###mail_from="$safegc <$fromad>"

2755 mail_from="$safegc <$user>" # TEST: 2020/5/13

2756 test -n `getpar sender` &&

2757 export SENDER=$user # TEST: 2020/5/15

2758 replyto=$fromad

2759

2760 ## Start parse of attachment files

2761 if [ -n "`getpar email`" ]; then

2762 ar=`getpar supprcpt`

2763 if [ -n "$ar" ]; then

2764 for a in $ar; do

2765 if checkdomain "$a"; then

2766 supprcpt="$supprcpt $a"

2767 else

2768 err "SupprcptErr=[$a] by $user"

2769 removercpt="$removercpt $a"

2770 fi

2771 done

2772 fi

2773 subj=`getpar subject`

2774 afiles=""

2775 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2776 do

2777 f=$tmpd/$fn

2778 if [ -s $f ]; then

2779 afiles=$afiles"${afiles:+ }$f"

2780 fi

2781 done

2782 else

2783 preface=$(cat <<-EOF

2784 $url

2785 のグループ「$grp」のメンバーである $gecos さんから、

2786 あなた宛のメッセージです。

2787 ----------------------------------------------------------

2788 EOF

2789 )

2790 fi

2791 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2792 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2793 subj="${subj:-$gecos さんからのメッセージ}"

2794 REPLYTO=$replyto

2795 MAIL_FROM=$mail_from

2796 export REPLYTO SMAIL_TO MAIL_FROM

2797 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2798 for r in $rcpts; do

2799 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2800 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2801 else

2802 # Show sender and rcpts address for guest

2803 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2804 fi

2805 if [ -n "$afiles" ];then

2806 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2807 else

2808 smail "$r" "$subj"

2809 fi <<EOF

2810 ${preface:+$preface$nl}$text

2811 EOF

2812 done

2813 if [ $? = 0 ]; then

2814 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2815 sql="select coalesce(b.val, a.name) from

2816 (select name from user where rowid in ($uids)) a

2817 left join user_s b on a.name=b.name and b.key='gecos';"

2818 html pre<<EOF

2819 `query "$sql"`

2820 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2821 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2822 EOF

2823 err SendDone: `echo $sql`

2824 fi

2825 elif [ x"$rm" = x"commission" ]; then

2826 grp_reg_adm $grid $usel

2827 elif [ x"$rm" = x"addteam" ]; then

2828 team=`getpar team|sed "s/'/''/g"` # for single quotation

2829 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2830 if [ x"$team" != x"$newteam" ]; then

2831 echo "チーム名に使えない文字を除去しました" | html p

2832 team=$newteam

2833 fi

2834 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2835 cat<<-EOF | html p

2836 有効なチーム名を入力してください。

2837 カンマだけ、「なし」という名前は使えません。

2838 EOF

2839 echo "有効なチーム名を入力してください。" | html p

2840 else

2841 grp_add_team $grid "$team" $usel

2842 fi

2843 elif [ x"$rm" = x"rmteam" ]; then

2844 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2845 rmteam=`getpar rmteam|sed "s/'/''/g"`

2846 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2847 gname='$grp' AND user='$user' AND key='team'\

2848 AND val='$rmteam';\"`" ]; then

2849 grp_rm_team $grid "$rmteam" $usel

2850 else

2851 echo "所属していないチームの除去操作はできません。"|html p

2852 fi

2853 else

2854 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2855 fi

2856 fi

2857 fi

2858 # POST count summary

2859 from=`getpar from`; to=`getpar to`

2860 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2861 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2862 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2863 # New entry

2864 sql="WITH mems AS (

2865 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2866 ON gm.user=g.name

2867 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2868 ), target_article AS (

2869 SELECT id FROM article_s

2870 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2871 ), posts AS (

2872 SELECT author, count(author) post

2873 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2874 WHERE blogid IN (SELECT id FROM blog_s

2875 WHERE key='owner'

2876 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2877 AND key='text'

2878 GROUP BY author

2879 ), teams AS (

2880 SELECT user, group_concat(val, ', ') team

2881 FROM grp_mem_m

2882 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2883 AND key='team'

2884 GROUP BY user

2885 ), user_post AS (

2886 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2887 FROM mems m LEFT JOIN posts

2888 ON m.name=posts.author

2889 GROUP by m.rowid

2890 )

2891 SELECT

2892 CASE

2893 WHEN (SELECT user FROM grp_adm

2894 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2895 AND user=up.name) IS NOT NULL

2896 then 'k'

2897 ELSE ''

2898 END || rowid || ',' || gecos NAME,

2899 post POST, team _TEAM_

2900 FROM user_post up LEFT JOIN teams t

2901 ON up.name=t.user

2902 ORDER BY gecos;"

2903 ## err grpaction: "`echo \"$sql\"`"

2904 tf=$tmpd/title.$$

2905 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2906 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2907 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2908 for=\"cmadmin\">管理者委任</label>

2909 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2910 </p></div>"

2911 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2912 title=\"Shortcut: g${nl}Remove from the Group\"

2913 for=\"conf\">GRP登録解除</label>

2914 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2915 <p>この操作による通知は本人に行きません。

2916 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2917 </div>"

2918 # Get team list to which current user belongs into $hexteams

2919 allhexteams=$(hexteams "$grp")

2920 if [ -n "$isowner" ]; then

2921 myhexteams="$allhexteams" # admin can remove all teams' attr

2922 else

2923 myhexteams=$(hexteams "$grp" "$user")

2924 fi

2925 if [ -n "$myhexteams" ]; then

2926 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2927 title=\"Shortcut: s${nl}Strip a team tag from\"

2928 for=\"cmrmteam\">チーム属性除去</label>

2929 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2930 を除去します: `cgi_checkbox teamconfirm yes` 確認

2931 <p>この操作による通知は本人に行きません。

2932 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2933 </div>

2934 "

2935 fi

2936 stf=$tmpd/selteam.$$

2937 cgi_select_h selteam "5445414d" $allhexteams > $stf

2938 b1='<label> <input type="checkbox" name="usel" value="'

2939 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2940 br='<span id="reverse" title="Reverse Selection"></span>'

2941 #b2='"> <span>' b3='</span></label>'

2942 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2943 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2944 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2945 cgi_form grpaction<<EOF \

2946 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2947 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2948 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2949 | _m4 -D_TITLE_="spaste(\`$tf')" \

2950 -D_SUBTITLE_="チェック後操作ボタン" \

2951 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2952 $layout/form+dump.m4.html \

2953 | _m4 -D_TEAM_="spaste(\`$stf')"

2954 <p>下でチェックした人を対象として:</p>

2955 <div class="foldtabs">

2956 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2957 title="Shortcut: a${nl}Add a team tag to"

2958 for="cmteam">同じチーム属性を付与</label>

2959 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2960 `cgi_datalist_h teams $allhexteams`

2961 </div>

2962 ${rmteammsg}

2963 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2964 title="Shortcut: d${nl}DirectMail to"

2965 for="sendmsg" title="Direct Message">DM送信</label>

2966 <div>

2967 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2968 title="Using email format">email書式を使う</label>

2969 <div class="folded">

2970 <table>

2971 <tr><td>From: </td><td>$user</td></tr>

2972 <tr><td>このFrom:で送る</td>

2973 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2974 </td></tr>

2975 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2976 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2977 <tr><td>ファイル添付: </td>

2978 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2979 </table>

2980 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2981 </div>

2982 <div>本文:`cgi_textarea text "" cols=72`

2983 </div>

2984 </div>

2985 ${isowner:+$cmmsg$excmsg}

2986 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2987 </div>

2988 <h4>$grp 参加者一覧</h4>$fromtonote

2989 <table class="td2r thl">

2990 `sq $db -header -html "$sql"`

2991 </table>

2992 `cgi_hidden grp $grid`

2993 `cgi_hidden myuid $myuid id="myuid"`

2994 EOF

2995 }

2996 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2997 # Create TEMPORARY VIEW

2998 query<<EOF

2999 CREATE TEMPORARY VIEW writeusers AS

3000 SELECT DISTINCT author FROM article

3001 WHERE id in (

3002 select id from article where blogid=(select id from blog where rowid=$1)

3003 );

3004 CREATE TEMPORARY VIEW movablegroups AS

3005 SELECT g.rowid growid , g.gname

3006 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

3007 ON grp.gname=gm.gname -- そのユーザが属している

3008 AND user='$user') g -- グループに絞る

3009 WHERE (SELECT author FROM writeusers

3010 EXCEPT

3011 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

3012 IS NULL;

3013 $2

3014 EOF

3015 }

3016 sql4readableblogs() {

3017 # Create view of blogs that can be readable to $user

3018 # Blog is readable when:

3019 # 1: blog owner is an user

3020 # 2: else, 2.1: owner-group where the $user belongs

3021 # 2.2: else, owner-group is not moderated

3022 # blog(id, author), blog_s(id, key='owner', val= ->owner)

3023 cat<<EOF ## | tee tmp/sql.out

3024 CREATE TEMPORARY VIEW readableblogs AS

3025 SELECT blog.rowid rid, id, author

3026 FROM blog

3027 NATURAL JOIN

3028 (SELECT id,

3029 max(CASE key WHEN 'owner' THEN val END) owner,

3030 max(CASE key WHEN 'mode' THEN val END) mode

3031 FROM blog_s GROUP by id) bs

3032 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3033 THEN 1 -- blog owner is an user, READABLE

3034 WHEN (SELECT val FROM grp_s

3035 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3036 AND

3037 (SELECT user FROM grp_mem

3038 WHERE gname=bs.owner AND user='$user') IS NULL

3039 THEN 0

3040 WHEN mode IN ('quiz', 'enquete')

3041 THEN 0 -- "quiz" mode blog cannot be searched

3042 ELSE 1

3043 END;

3044 EOF

3045 }

3046 editheading() { # $1=rowid-of-heading

3047 rowid=${1%%[!A-Z0-9a-z_]*}

3048 if [ -z "$rowid" ]; then

3049 echo "話題番号が未指定です。" | html p

3050 return

3051 fi

3052 owner=`getvalbyid blog owner $rowid`

3053 title=`getvalbyid blog title $rowid`

3054 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3055 | _m4 -D_TITLE_="修正" \

3056 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3057 -D_BLOGS_="" -D_DUMPTABLE_="" \

3058 -D_FORM_="syscmd(\`cat')" \

3059 $layout/html.m4.html $layout/form+dump.m4.html

3060 # Move to group

3061 if isuser "$owner"; then

3062 crview4article $rowid

3063 n=`query "SELECT count(*) FROM writeusers;"`

3064 ## err N=$n

3065 if [ $((n)) -gt 0 ]; then

3066 ## err ROWID=$rowid

3067 sql="SELECT growid || ':' || gname FROM movablegroups;"

3068 cat<<-EOF

3069 <div class="fold">

3070 `cgi_checkbox mv send id="mv"`<label

3071 for="mv">この話題をグループ所有に移動する</label>

3072 <div>

3073 <form action="?mvart" method="POST" enctype="multipart/form-data">

3074 移動先グループ:

3075 <select name="mv2grp">

3076 EOF

3077 query ".mode html"

3078 query<<-EOF |

3079 $sql

3080 .mode list

3081 EOF

3082 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3083 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3084 cat<<-EOF

3085 </select>

3086 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3087 そのグループに加入しているものに限られます)</p>

3088 <p>`cgi_checkbox cfm yes`<label>確認

3089 (この操作は元に戻すことができません)</label></p>

3090 `cgi_hidden blogrowid $rowid`

3091 `cgi_submit 移動`

3092 `cgi_reset Reset`

3093 </form>

3094 </div>

3095 </div>

3096 EOF

3097 fi

3098 # end of isuser "$owner"

3099 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3100 [ -n "$hexteams" ];}; then

3101 none="`echo なし|hexize`"

3102 cat<<-EOF

3103 <div class="fold">

3104 `cgi_checkbox mv2team send id="mv2team"`<label

3105 for="mv2team">この話題を以下のチームのものにする</label>

3106 <div><p>現在の所属チーム設定:

3107 `query "SELECT

3108 coalesce((SELECT val FROM blog_s

3109 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3110 AND key='team'),

3111 ':なし');"`</p>

3112 <form action="?mvart" method="POST" enctype="multipart/form-data">

3113 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3114 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3115 `cgi_hidden blogrowid $rowid`<br>

3116 `cgi_submit 移動`

3117 `cgi_reset Reset`

3118 </form></div></div>

3119 EOF

3120 fi

3121 }

3122 mvart() { # move diary to some group or team

3123 # or move blog of group to team which belong to the group

3124 blogrowid=`getpar blogrowid`

3125 cfm=`getpar cfm`

3126 ##### echo move blog:$blogrowid to $mv2grp | html p

3127 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3128 . ./s4-blog.sh

3129 if [ -z "$blogrowid" ]; then

3130 echo "無効な指定です(mvart)。" | html p

3131 return

3132 elif [ x"$cfm" != x"yes" ]; then

3133 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3134 elif { mv2grp=`getpar mv2grp`

3135 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3136 [ -n "$mv2grp" ]; }; then

3137 crview4article $blogrowid

3138 ########## TRANSACTION BEGIN

3139 query "BEGIN;"

3140 n=`query "SELECT count(*) FROM writeusers;"`

3141 ## err Nwriteuser=$n

3142 if [ $((n)) -gt 0 ]; then

3143 query<<-EOF

3144 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3145 WHERE key='owner'

3146 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3147 AND $mv2grp IN (SELECT growid FROM movablegroups);

3148 EOF

3149 fi

3150 query "END;"

3151 ########## TRANSACTION END

3152 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3153 [ -n "$mv2team" ];}; then

3154 # blog owner can move it to ANY team

3155 case "$mv2team" in

3156 'なし')

3157 cat<<-EOF

3158 DELETE FROM blog_s

3159 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3160 AND key='team';

3161 EOF

3162 ;;

3163 "") ;;

3164 *)cat<<-EOF

3165 BEGIN;

3166 REPLACE INTO blog_s(id, key, val)

3167 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3168 'team', '$mv2team');

3169 REPLACE INTO blog_s(id, key, val)

3170 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3171 'notify', 'all'); -- Change notify to all

3172 END;

3173 EOF

3174 esac | query

3175 fi

3176 blog_reply $blogrowid

3177 echo yes | html p

3178 }

3179 editart() { # $1=article-rowid $2=blogrowid

3180 rowid=${1%%[!A-Z0-9a-z_]*}

3181 blogrowid=${2%%[!A-Z0-9a-z_]*}

3182 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3183 echo "表示する記事番号が未指定です。" | html p

3184 return

3185 fi

3186 owner=`getvalbyid blog owner $blogrowid`

3187 title=`getvalbyid blog title $blogrowid`

3188 author=`getvalbyid article author $rowid`

3189 ## err EDITart: owner=$owner, author=$author

3190 if isgrpowner "$user" "$owner"; then

3191 : EDIT OK

3192 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3193 echo "本人か所有者しか編集できません." | html p

3194 return

3195 fi

3196 aid=`query "select id from article where rowid=$rowid;"`

3197 tmpout=$tmpd/editart.$$.out

3198 GF_ACTION="?replyblog+$blogrowid#$aid" \

3199 edittable $formdir/article.def article $rowid \

3200 > $tmpout

3201 rm -f /tmp/editart.out

3202 # Cannot use pipelining to m4 with genform() because of stdin stack

3203 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3204 -D_FORM_="syscmd(cat $tmpout)" \

3205 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3206 -D_BLOGS_= -D_DUMPTABLE_= \

3207 $layout/html.m4.html $layout/form+dump.m4.html

3208 }

3209 send2mem() {

3210 rowid=`getpar grp`

3211 rowid=${rowid%%[!0-9]*} # Cleaning

3212 if [ -z "$rowid" ]; then

3213 echo "グループが未指定です。" | html p

3214 return

3215 fi

3216 message=`getpar message`

3217 if [ -z "$message" ]; then

3218 echo "文章を入れてください。" | html p

3219 return

3220 fi

3221 grp=`getgroupbyid $rowid`

3222 members=`collectemail $grp`

3223 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3224 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3225 mailfrom="`gecos "$user"` <$mailfrom>"

3226 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3227 msg=$(cat<<-EOF

3228 $urlbase?grp+$rowid

3229 グループ $grp に所属する

3230 `gecos $user` さんよりメッセージ:

3231

3232 $message

3233 EOF

3234 )

3235 # smail rcpt subj (file)

3236 for m in $members; do

3237 echo "$msg" |

3238 MAIL_FROM=$mailfrom \

3239 SENDER=$noreply \

3240 REPLYTO=$mailfrom \

3241 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3242 smail "$m" "$sj"

3243 done

3244 cat<<EOF

3245 <p>以下のユーザに送信しました。</p>

3246 <pre>

3247 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3248 </pre>

3249 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3250 EOF

3251 }

3252 joingrpadmit() {

3253 # $1=yes/no $2=session-key

3254 if [ -z "$2" ]; then

3255 echo "bye bye" | html p; return

3256 fi

3257 t_usr=`session=$2 getpar adduser`

3258 t_grp=`session=$2 getpar group`

3259 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3260 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3261 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3262 echo "無効な加入依頼です。" | html p

3263 echo "有効期限が切れたか、

3264 他の管理者がいる場合は処理済みの可能性があります。" | html p

3265 return

3266 fi

3267 if ! isgrpowner "$user" "$t_grp"; then

3268 echo "グループ管理者のみの機能です。" | html p; return

3269 fi

3270 case $1 in

3271 yes) joingrp "$t_grp" "$t_usr" yes ;;

3272 no) joingrp "$t_grp" "$t_usr" no ;;

3273 *)

3274 echo "無効な指定です($1)。" | html p

3275 return ;;

3276 esac

3277 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3278 rcpts="`getgroupadminmails $t_grp` $user"

3279 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3280 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3281 に

3282 $t_usr

3283 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3284 しました。"

3285 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3286 query "delete from session where id='$2';"

3287 echo "$body" | html p

3288 }

3289

3290 joingrprequest() {

3291 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3292 jss="joingrp-`date +%s`-`genrandom 12`"

3293 addsession $jss +${memoplimitdays}days

3294 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3295 ('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"

3296 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3297 $url

3298 $user さんから

3299 グループ $1

3300 に加入依頼がありました。

3301

3302 承認する:

3303 $urlbase?joingrpadmit+yes+$jss

3304

3305 白紙に戻す:

3306 $urlbase?joingrpadmit+no+$jss

3307 EOF

3308 echo "管理者に加入依頼を出しました。

3309 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3310 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3311 }

3312 joingrp() {

3313 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3314 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3315 if isgrpowner "$user" "$1"; then

3316 isowner="yes"

3317 elif [ -n "$5" ]; then

3318 isowner="yes"

3319 else

3320 isowner=""

3321 fi

3322 ## err jg:isgrpowner: isowner="$isowner"

3323 if [ -n "$isowner" ]; then

3324 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3325 elif [ x"$2" != x"$user" ]; then # if user is not login user

3326 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3327 return

3328 elif [ x"$3" = x"no" ]; then

3329 : # Do not pursue those who leave

3330 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3331 : # Member can change own email address for the joining moderated group

3332 else # adding user is $user itself

3333 case `getgroupattr $1 regmode` in

3334 moderated)

3335 joingrprequest "$@" # Request only

3336 return

3337 ;;

3338 *)

3339 ;;

3340 esac

3341 fi

3342 qgname=`sqlquote "$1"`

3343 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3344 cond="where gname=$qgname and user='$2'"

3345 if [ x"$3" = x"yes" ]; then

3346 query "replace into grp_mem values($qgname, '$2');"

3347 # Notify joingrp to admin

3348 action="に加入しました。"

3349 if [ -n "$4" ]; then

3350 if msg=`emaildomaincheck "$4"`; then

3351 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3352 'string', '$4', NULL);"

3353 else

3354 echo $msg

3355 fi

3356 else

3357 query "delete from grp_mem_s $cond and key='email';"

3358 fi

3359 if [ -n "$5" ]; then # as ADMIN

3360 # Coming here means newly created group

3361 sql="select case\

3362 when (select count(*) from grp_mem where gname=$qgname)=1\

3363 then (select user from grp_mem\

3364 where gname=$qgname and user='$user')\

3365 else '' end; "

3366 err NewGrpChk: $sql

3367 if [ -n "`query \"$sql\"`" ]; then

3368 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3369 query "replace into grp_adm values($qgname, '$user');"

3370 fi

3371 fi

3372 else

3373 query "begin;

3374 delete from grp_mem $cond;

3375 delete from grp_mem_s $cond;

3376 delete from grp_mem_m $cond;

3377 delete from grp_adm $cond;

3378 delete from grp_adm_s $cond;

3379 delete from grp_adm_m $cond;

3380 end;"

3381 action="から脱退しました。"

3382 fi

3383 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3384 $url?grp+$grid

3385 $user (`gecos $user`)さんが

3386 グループ $1

3387 $action

3388 EOF

3389 }

3390 grp_add_team() (

3391 # $1=grp-rowid $2=team $3...=user-rowid(s)

3392 grp=`getgroupbyid $1`

3393 team=$2; shift; shift

3394 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3395 { echo "BEGIN;"

3396 for user; do

3397 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3398 '$grp',\

3399 (SELECT name FROM user WHERE rowid=$user),\

3400 'team', 'string', '$team');"

3401 done

3402 echo "END;"

3403 } | query

3404 )

3405 grp_rm_team() (

3406 # $1=grp-rowid $2=team $3...=user-rowid(s)

3407 grid=$1

3408 qgrp=$(sqlquote "`getgroupbyid $grid`")

3409 team=$2; shift; shift

3410 [ -z "$grid" -o -z "$team" ] && return

3411 { echo "BEGIN;"

3412 for user; do

3413 echo "DELETE FROM grp_mem_m\

3414 WHERE gname=$qgrp \

3415 AND user=(SELECT name FROM user WHERE rowid=$user)\

3416 AND key='team' AND val='$team';"

3417 done

3418 cat<<-EOF

3419 DELETE FROM blog_s

3420 WHERE rowid=(

3421 SELECT rowid

3422 FROM blog_s a

3423 WHERE key='team'

3424 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3425 AND NOT EXISTS (SELECT * FROM grp_mem_m

3426 WHERE key='team' AND val=a.val -- a.val=team

3427 AND gname = (SELECT val FROM blog_s b

3428 WHERE a.id=b.id AND key='owner')

3429 ));

3430 EOF

3431

3432 echo "END;"

3433 } | query

3434 )

3435 grp_reg_adm() {

3436 # $1=grp-rowid $2...=user-rowid

3437 grid=$1

3438 grp=`getgroupbyid "$1"`

3439 if [ -z "$grp" ]; then

3440 echo "無効なグループIDです" | html p; return

3441 fi

3442 if ! isgrpowner "$user" "$grp"; then

3443 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3444 fi

3445 shift

3446 for urid; do

3447 newadm=`query "select name from user where rowid=$urid;"`

3448 if [ -z "$newadm" ]; then

3449 echo "指定ユーザIDがおかしいようです。" | html p; return

3450 fi

3451 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3452 err ismember $newadm $grp

3453 if ismember $newadm "$grp"; then

3454 # OK, go ahead

3455 getgname="(select gname from grp where rowid=$grid)"

3456 query "replace into grp_adm values($getgname, '$newadm');"

3457 # confirm insertion

3458 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3459 if [ -n "`query \"$sql;\"`" ]; then

3460 echo "追加完了: $newadm" | html p

3461 else

3462 echo "追加失敗($1 $urid)" | html p

3463 fi

3464 fi

3465 showgroup $grid

3466 done

3467 }

3468 dt_rowhack() {

3469 # From: <TR>

3470 # ....

3471 # <TD>rowclass=foo</TD>

3472 # </TR>

3473 # To: <TR class="foo">....<TD>foo</TD></TR>

3474 sed -e '

3475 /^<TR>/ {

3476 :loop

3477 s/\n//

3478 N

3479 /<\/TR>/ {

3480 s/\n//

3481 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3482 n

3483 }

3484 $q

3485 b loop

3486 }'

3487 }

3488 dumptable() {

3489 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3490 # textのフィールドだけ全てダンプにしたほうがいいか

3491 # $DT_VIEW sets link

3492 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3493 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3494 VIEW=${DT_VIEW-replyblog}

3495 if [ -n "$VIEW" ]; then

3496 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3497 fi

3498 sqlfile=$tmpd/dump.sql

3499 : > $sqlfile # ensure to be empty

3500 printf '.mode html\n.header 1\n' > $sqlfile

3501 # $DT_CHLD=ChildTable:BindColumn

3502 if [ -n "$DT_CHLD" ]; then

3503 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3504 cat<<-EOF >> $sqlfile

3505 -- presql

3506 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3507 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3508 EOF

3509 # Speed up counting of new articles

3510 cat<<-EOF >> $sqlfile

3511 -- presql2

3512 DROP TABLE IF EXISTS _counts;

3513 CREATE TEMPORARY TABLE _counts AS

3514 SELECT $_i, count($_i) cnt

3515 FROM $_t GROUP BY $_i;

3516 /* Prepare NEW count table */

3517 CREATE TEMPORARY TABLE _target AS

3518 SELECT b.rowid trowid, b.id

3519 FROM "$2" b JOIN "$2_s" s

3520 ON b.id=s.id AND s.key='owner'

3521 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3522

3523 DROP TABLE IF EXISTS _children;

3524 CREATE TEMPORARY TABLE _children AS

3525 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3526 FROM (SELECT t.trowid, t.id $_i, a.id

3527 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3528 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3529

3530 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3531 DROP TABLE IF EXISTS _news;

3532 DROP VIEW IF EXISTS _news;

3533

3534 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3535 -- INSERT INTO _news

3536 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3537 CREATE TEMPORARY VIEW _news AS

3538 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3539 FROM (SELECT DISTINCT id FROM _target)

3540 a LEFT JOIN

3541 (SELECT $_i, count(ctime) newcnt

3542 FROM _children x

3543 WHERE ctime > coalesce((SELECT time from myacclog

3544 WHERE tblrowid=x.trowid),

3545 '1970-01-01')

3546 GROUP BY $_i) b

3547 ON a.id=b.$_i;

3548 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3549 EOF

3550 # REMOVE next line until 2019/5/1

3551 cntall="(select count($_i) from $_t where $_i=a.id)"

3552 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3553 # REMOVE next assignment until 2019/5/1

3554 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3555 and id in (select id from $_t where $_i=a.id) \

3556 and val > coalesce((select time from myacclog where \

3557 tblrowid=a.rowid),\

3558 '1970-01-01'))"

3559 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3560 cnt="$cntnew as '新着', $cntall as '総数',"

3561 dt_class=" td2r td3r dumpblogs"

3562 fi

3563 # Construct join expression

3564 eav="" scols=""

3565 pk=`gettblpkey $2`

3566 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3567 substr=${substr:-%s}

3568 for col in ${3:-`gettbl_s_cols $2`}; do

3569 valvar=val

3570 case $col in

3571 gecos) scols="$scols${scols:+, }${col#}"

3572 continue ;; # built-in column name

3573 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3574 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3575 case "$as" in

3576 *:*=*) cnd=${as#*:}

3577 h=${cnd%%=*} v=${cnd#*=}

3578 h=`sqlquotestr "$h"`

3579 v=`sqlquotestr "$v"`

3580 valvar="CASE val WHEN $h THEN $v END"

3581 as=${as%%:*} ;;

3582 esac

3583 ;;

3584 *) as=${col} ;;

3585 esac

3586 ss=`printf "$substr" "$valvar"`

3587 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3588 scols="$scols${scols:+, }b.$as"

3589 done

3590 #case author when '$user' then a.rowid else '---' end as ID,

3591 if [ -n "$DT_SQL" ]; then

3592 echo "$DT_SQL"

3593 else

3594 cat<<-EOF

3595 SELECT a.rowid as LINK, $cnt $scols

3596 FROM $2 a LEFT JOIN

3597 (SELECT $pk,$eav,

3598 max(CASE key

3599 WHEN 'owner'

3600 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3601 as gecos

3602 FROM ${2}_s c GROUP BY $pk)

3603 b ON a.$pk=b.$pk $4;

3604 EOF

3605 fi >> $sqlfile

3606 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3607 sqlog<<-EOF

3608 *** SQL-file: $sqlfile ***

3609 `cat $sqlfile`

3610 EOF

3611 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3612 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3613 cat $sqlfile >> tmp/sql

3614 # query ".read $sqlfile" > $tmpd/foo

3615 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3616 cp $tmpd/foo tmp/

3617 err "----- `gdate +%FT%T.%3N` ------------555555"

3618 ## $ddd LINE exists at the end of this function

3619 fi

3620 printf '.mode list\n.header 0\n' >> $sqlfile

3621 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3622 <div>

3623 <div class="dumptable">

3624 <table class="b$dt_class">

3625 `query ".read $sqlfile"`

3626 </table>

3627 </div>

3628 </div>

3629 EOF

3630 ### `query ".read $sqlfile"`

3631 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3632

3633 ## REMOVE THIS!

3634 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3635 }

3636

3637 par2table() (

3638 # copy current parameters of par into destination table

3639 # $1=definition-file

3640 # Using $user and $session

3641 # Return value:

3642 # 0: Stored successfully

3643 # 1: Insufficient fillings

3644 # 2: No permission to modify the record

3645 # 3: Invalid rowid

3646 # 4: SUCCESS to delete

3647 # 5: Stop deletion for lack of confirm check

3648 # 6: Password length too short

3649 # 7: Password mismatch

3650 # 8: Old password incorrect

3651 # 9: Duplicated post

3652 rowid=`getpar rowid`

3653 if [ ! -e $1 ]; then

3654 echo "テーブル定義ファイルが見付かりません" | html p

3655 exit 1

3656 fi

3657 tbl=${1%.def}

3658 tbl=${tbl##*/}

3659 if [ -n "$rowid" ]; then # Modify existing entry

3660 if [ x"$tbl" = x"user" ]; then

3661 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3662 elif [ x"$tbl" = x"grp" ]; then

3663 sql="select gname from $tbl where rowid=$rowid;"

3664 ##err p2t:grp:q $sql

3665 isgrpowner "$user" "`query $sql`" && rowowner=$user

3666 elif [ x"$tbl" = x"blog" ]; then

3667 # Check if owner in blog_s

3668 blogowner=`getvalbyid blog owner "$rowid"`

3669 if isgrpowner "$user" "$blogowner"; then

3670 rowowner=$user

3671 else

3672 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3673 fi

3674 else

3675 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3676 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3677 fi

3678 ### err rowowner=$rowowner

3679 if [ x"$user" != x"$rowowner" ]; then

3680 echo "他人のレコードはいじれないの" | html p

3681 return 2

3682 elif [ -z "$rowowner" ]; then

3683 echo "指定したレコードはないみたい" | html p

3684 return 3

3685 fi

3686 rm=`getpar rm` cfm=`getpar confirm`

3687 # Editing existent entry

3688 if [ x"$rm" = x"yes" ]; then

3689 if [ x"$rm$cfm" = x"yesyes" ]; then

3690 query "delete from $tbl where rowid=$rowid;"

3691 return 4

3692 else

3693 echo "消去確認のチェックがないので消さなかったの..." | html p

3694 return 5

3695 fi

3696 fi

3697 fi

3698

3699 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3700 if [ -n "$rowid" ]; then

3701 # Update of existing record

3702 for col in `gettblcols $tbl`; do

3703 val=`getparquote $col`

3704 [ -z "$val" ] && continue

3705 ## err query "update $tbl set $col=$val where rowid=$rowid"

3706 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3707 sql="update $tbl set $col=$val where rowid=$rowid;"

3708 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3709 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3710 ## User name cannot be changed with interface provided with this

3711 ## script. But we offer the trigger to change owner user

3712 ## of blog_s table.

3713 #err "select quote($col) from $tbl where rowid=$rowid;"

3714 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3715 cat<<-EOF | query

3716 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3717 -- should use EXCLUSIVE transaction outside of this.

3718 SAVEPOINT par2table;

3719 $sql

3720 update blog_s set val=$val

3721 where key='owner' and val=$old;

3722 RELEASE SAVEPOINT par2table;

3723 EOF

3724 ## XX: DIRTY Hack Ends here

3725 ## We should keep blog's owner as a single column which has

3726 ## foreign key constraint with primary key of grp/user.

3727 else

3728 query "$sql"

3729 fi

3730 done

3731 # Then, set up $pval for further insertion of tbl_s and tbl_m

3732 for col in `gettblpkey $tbl`; do

3733 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3734 pval="$pval${pval:+, }\"$val\""

3735 done

3736 else

3737 # New entry

3738 # XXX: WORK-AROUND FOR SOME STUPID BROWSER

3739 # Avoid empty repost of article.

3740 if [ x"$tbl" = x"article" ]; then

3741 # If rowid is empty and ID exists in article-table, that is REPOST!

3742 aid=`getpar id`

3743 xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`

3744 if [ -n "$xaid" ]; then

3745 # REPOST of article

3746 html p <<-EOF

3747 書き込み直後のリロードなので上書きを回避します。

3748 最新記事は末尾の「再読み込み」ボタンから見てください。

3749 EOF

3750 err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"

3751 return 9 # STOP Duplicated posting

3752 fi

3753 fi

3754 # Generate values() for primary keys

3755 for col in `gettblpkey $tbl`; do

3756 # Genuine primary keys for _m and _s

3757 val=`getvalquote $tbl $col`

3758 [ -z "$val" ] && continue

3759 pval="$pval${pval:+, }$val"

3760 done

3761 ##err pval=$pval

3762 for col in `gettblfkey $tbl`; do

3763 # args for values() to insertion into master table

3764 val=`getvalquote $tbl $col`

3765 [ -z "$val" ] && continue

3766 formaster=$formaster"${formaster:+, }$val"

3767 done

3768 formaster="$pval${formaster:+, }$formaster"

3769 ## err formaster=$formaster

3770 if [ -z "$formaster" ]; then

3771 echo "項目を全て埋めてください" | html pre

3772 return 1

3773 fi

3774 ## err "replace into $tbl values($formaster);"

3775 query "replace into $tbl values($formaster);"

3776 ## Insertion to master table, done

3777 fi

3778

3779 transaction=$tmpd/sqlfile.sql; touch $transaction

3780 for kt in s m; do

3781 tb2=${tbl}_$kt

3782 for col in `gettbl_${kt}_cols $tbl`; do

3783 ptype=`getpartype $col "limit 1"`

3784

3785 # First, check update of existing entries in _m

3786 if [ $kt = m ]; then

3787 # sessID|address.1.22|string|Somewhere-x.y.z

3788 sql=""

3789 ##err dots from query "select var from par where var like '$col.%';"

3790 for v in `query "select var from par where var like '$col.%';"`; do

3791 # v=address.1.22

3792 st_rowid=${v##*.}

3793 origcol=${v%%.*} # original column derived from

3794 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3795 ##case `getpartype $v` in

3796 ## err CASE `gettbl_coltype $tbl/$origcol` in

3797 ## err edit flag = `getpar action.$v`

3798 case `getpar action.$v` in

3799 rm)

3800 if [ x`getpar confirm.$v` = x"yes" ]; then

3801 newsql="delete from $tb2"

3802 else

3803 echo "削除確認未チェック" | html p

3804 fi ;;

3805 edit)

3806 case `gettbl_coltype $tbl/$origcol` in

3807 image|document|binary)

3808 file=$tmpd/`getparfilename $v`

3809 if [ ! -s "$file" ]; then # Maybe stupid REPOST

3810 err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"

3811 continue

3812 fi

3813 ## err type=file=$file

3814 [ -z "$file" ] && continue

3815 bn=`sqlquotestr "${file##*/}"`

3816 bin="X'"$(hexize "$file")"'"

3817 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3818 type=\"file:$ct\"

3819 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3820 cachedir=`getcachedir "$tbl/$rowid"`

3821 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3822 rm -rf $cachedir

3823 ;;

3824 *)

3825 newsql="update $tb2 set val=(select val from par where var \

3826 like '$col.%.$st_rowid')"

3827 ;;

3828 esac

3829 ;;

3830 *) # maybe "keep", do not modify value

3831 continue

3832 ;;

3833 esac

3834 # err newsql=$newsql

3835 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3836 done

3837

3838 if [ x"$bin" = x"NULL" ]; then

3839 ## err repl:normal sql=`echo $sql`

3840 if [ -n "$transaction" ]; then

3841 cat<<-EOF >> $transaction

3842 $sql

3843 DELETE FROM $tb2 WHERE type='string' AND val='';

3844 EOF

3845 else

3846 query "$sql

3847 delete from $tb2 where type='string' and val='';"

3848 ## err repl:normal done

3849 fi

3850 else

3851 # Binary update line is TOO LONG to pipelining

3852 sqlfile="$tmpd/sqlf.$$"

3853 if [ -n "$transaction" ]; then

3854 echo "$sql" >> $transaction

3855 else

3856 echo "$sql" > $sqlfile

3857 query ".read $sqlfile"

3858 fi

3859 fi

3860 # Rest of kt==m: set multiple mode

3861 nr=`getparcount $col`

3862 else

3863 nr=1 # for kt==s, number of records is 1

3864 fi

3865

3866 i=0

3867 while [ $i -lt $nr ]; do

3868 limit="limit 1 offset $i"

3869 i=$((i+1)) # increase beforehand against continue

3870 val=`getvalquote $tbl $col "$limit"`

3871 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3872 ## err $col=$val

3873 bin=NULL

3874 ## err partype$col=`getpartype $col "$limit"`

3875 case $ptype in

3876 file) file=$tmpd/`getparfilename $col "$limit"`

3877 ## err parfile-$col=$file

3878 [ -z "$file" ] && continue

3879 bin="X'"$(hexize "$file")"'"

3880 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3881 type=\"file:$ct\" ;;

3882 "*"*) continue ;; # foreign table

3883 *) type=\"string\" ;;

3884 esac

3885 case `gettbl_coltype $tbl/$col` in

3886 password) # special care for password

3887 # name={password,pswd1,pswd2}

3888 p1=`getpar pswd1 "$limit"`

3889 if [ -z "$p1" ]; then

3890 continue # SKIP password setting, if p1 is empty

3891 else

3892 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3893 ## err pswd=$pswd

3894 if pwcheck "$pswd"; then

3895 if [ x"$p1" = x"$p2" ]; then

3896 case "$p1" in

3897 ??????????*) ;;

3898 *) echo "パスワードは10字以上にしてください。" | html p

3899 return 6;;

3900 esac

3901 val="\"`echo $p1|mypwhash`\""

3902 else

3903 echo "2つの新パスワード不一致" | html p

3904 return 7

3905 fi

3906 else

3907 echo "旧パスワード違います" | html p

3908 return 8

3909 fi

3910 fi

3911 ;;

3912 esac

3913 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3914 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3915 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3916 if [ x"$bin" = x"NULL" ]; then

3917 ## err Normal-query: `echo $sql`

3918 if [ -n "$transaction" ]; then

3919 echo "$sql" >> $transaction

3920 else

3921 query "$sql"

3922 fi

3923 else

3924 sqlfile="$tmpd/query.$$"

3925 ## err sqlfile=`ls -lF $sqlfile`

3926 if [ -n "$transaction" ]; then

3927 echo "$sql" >> $transaction

3928 else

3929 echo "$sql" > $sqlfile

3930 query ".read $sqlfile"

3931 fi

3932 fi

3933 ## err p2t done

3934 done

3935 done

3936 done

3937 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3938 -- We cannot use transaction here, because groupupdate may use it.

3939 SAVEPOINT pa2table_insert;

3940 .read $transaction

3941 RELEASE SAVEPOINT pa2table_insert;

3942 EOF

3943 return $?

3944 ##err donee

3945 )

3946 genform() {

3947 # $1 = form definition file

3948 # $2, $3 (optional)= table name and ROWID

3949 # If $GF_VIEWONLY set and nonNull, output values without form

3950 # If $GF_ARGS set, use it as content-strings in the form

3951 # If $GF_OWNER set, use it as value of name="owner"

3952 # If $GF_STAGE set, use it as value of name="stage"

3953 forms="" hiddens="" rowid=$3

3954 if [ ! -e "$1" ]; then

3955 echo "そのようなデータベースはないようです($2)。" | html p

3956 return

3957 elif [ -n "$2" ]; then

3958 rec=`query "select * from $2 where rowid='$rowid';"`

3959 if [ -z "$rec" ]; then

3960 pk=`gettblpkey $2`

3961 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3962 rec=`query "select rowid from $2 where $pk='$rowid';"`

3963 rowid=$rec

3964 rec=$3

3965 fi

3966 if [ -z "$rec" ]; then

3967 echo "そんなレコードはないみたいね..." | html p

3968 return

3969 fi

3970 fi

3971 if [ -z "$GF_VIEWONLY" ]; then

3972 rm='<input id="rm" name="rm" type="checkbox"

3973 value="yes"><label for="rm">このエントリの削除</label>

3974 <span>ほんとうに消しますよ(確認)!

3975 <input name="confirm" type=checkbox value="yes">はい</span>'

3976 fi

3977 # Image Cache dir

3978 ## err genform: getcache=$2/$rowid

3979 td=`getcachedir "$2/$rowid"`

3980 while IFS=: read prompt name keytype type args; do

3981 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3982 sp="${args:+ }"

3983 form="" val=""

3984 if [ -n "$rowid" ]; then

3985 # err genform2a: Seeking for "$2.$name, type=$type"

3986 rawval=`getvalbyid $2 $name $rowid $td`

3987 val=`echo "$rawval"|htmlescape`

3988 ## err genform3a: getvalbyid $2 $name $rowid $td

3989 ## err genform3b: val="[$val]" type="$type"

3990 fi

3991 if [ -n "$GF_VIEWONLY" ]; then

3992 is_hidden "$2" "$name" && continue

3993 fi

3994 case "$type" in

3995 text*)

3996 cgiform=cgi_multi_$type

3997 if [ -s $td/$name.count -a -n "$val" ]; then

3998 form=`$cgiform $name $td`

3999 val=$(echo "$val"|

4000 while read fn; do

4001 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

4002 </td></tr>$nl"

4003 done)

4004 val="<table>$nl$val$nl</table>"

4005 else

4006 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

4007 form=`cgi_$type $name "$rawval" "$args"`

4008 fi

4009 ;;

4010 [Rr][Aa][Dd][Ii][Oo])

4011 fh="<label><input type=\"radio\" name=\"$name\""

4012 form="`echo $args|sed -e \

4013 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

4014 ;;

4015 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

4016 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

4017 ;;

4018 [Ss][Ee][Ll][Ee][Cc][Tt])

4019 fh="<select name=\"$name\">$nl"

4020 form=$(for l in $args; do

4021 echo "<option value=\"${l#*=}\">${l%=*}</option>"

4022 done)

4023 if [ -n "$val" ]; then

4024 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

4025 fi

4026 form="$fh$form</select>"

4027 ;;

4028 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

4029 if [ -s $td/$name.count ]; then

4030 form=`cgi_multi_file $name $td "$args"`

4031 if [ -n "$val" ]; then

4032 hrfb="$myname?showattc+$2_m"

4033 val=$(echo "$rawval" \

4034 | while read fn; do

4035 data=`percenthex "$td/$fn"`

4036 #ct=`cat $td/$fn.content-type`

4037 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

4038 ri=`cat "$td/$fn.rowid"`

4039 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

4040 #imgsrc="<img src=\"data:$ct,$data\">"

4041 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

4042 iconhref2 "$td/$fn" "$hrfb+$ri" ""

4043 done)

4044 fi

4045 else

4046 form="<input type=\"file\" name=\"$name\" $args>"

4047 if [ -n "$val" ]; then

4048 imgs=$(echo "$rawval"\

4049 |while read fn;do

4050 data=`percenthex "$td/$fn"`

4051 echo "<img src=\"data:image/png,$data\">$fn<br>"

4052 done)

4053 form=$form"<br>$imgs"

4054 val=$imgs # 2015-06-15

4055 else

4056 form="<input type=\"file\" name=\"$name\" $args>"

4057 fi

4058 fi

4059 ;;

4060 [Hh][Ii][Dd][Dd][Ee][Nn])

4061 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4062 args="value=\"$GF_STAGE\""

4063 fi

4064 form="<input type=\"hidden\" name=\"$name\" $args>"

4065 prompt='' # Remove prompt

4066 ;;

4067 [Aa][Uu][Tt][Hh][Oo][Rr])

4068 [ -n "$GF_VIEWONLY" ] && continue

4069 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4070 prompt="" ;;

4071 [Oo][Ww][Nn][Ee][Rr])

4072 [ -n "$GF_VIEWONLY" ] && continue

4073 val=${GF_OWNER:-$val}

4074 val=${val:-$user}

4075 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4076 prompt="" ;;

4077 [Uu][Ss][Ee][Rr])

4078 # XXX: is null $user ok?

4079 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4080 [ -n "$GF_VIEWONLY" ] && continue

4081 form="$user"

4082 ;;

4083 [Pp]assword)

4084 [ -n "$GF_VIEWONLY" ] && continue

4085 form="`cgi_passwd`"

4086 val=""

4087 ;;

4088 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4089 [ -n "$GF_VIEWONLY" ] && continue

4090 if [ -z "$rowid" ]; then

4091 val=`genserial`

4092 fi

4093 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4094 prompt="" ;;

4095 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4096 prompt=""

4097 ;;

4098 parent|path|blog*)

4099 prompt=""

4100 ;;

4101 "*"*)

4102 tail=$tail"``"

4103 continue ;;

4104 esac

4105 if [ -n "$prompt" ]; then

4106 if [ -n "${GF_VIEWONLY}" ]; then

4107 form=$val

4108 else

4109 :

4110 fi

4111 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4112 else

4113 hiddens=$hiddens$nl"$form"

4114 fi

4115 done < $1

4116 # enctype="multipart/form-data"

4117 cat<<EOF

4118 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4119 ${rowid:+$rm}

4120 <table class="b $2">

4121 $forms

4122 </table>$hiddens

4123 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4124 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4125 EOF

4126 if [ -z $GF_VIEWONLY ]; then

4127 cat<<EOF

4128 <input type="submit" name="sub" value="OK">

4129 <input type="reset" name="res" value="Reset">

4130 EOF

4131 fi

4132 cat<<EOF

4133 $GF_ARGS</form>

4134 $tail

4135 EOF

4136 }

4137 edittable() {

4138 # $1=form-def $2=table $3 rowid

4139 genform "$@"

4140 }

4141 viewtable() {

4142 GF_VIEWONLY=1 genform "$@"

4143 }

4144 showattc() {

4145 # $1=table_m $2=rowid &optional $3=RawFlag

4146 ## err \$1=$1 \$2=$2 \$3=$3

4147 if ! isfilereadable $user $1 $2; then

4148 contenttype; echo

4149 echo "このファイルは管理者のみしか見られません" | html p

4150 putfooter; exit

4151 fi

4152 idir=`umask 002; mktempd` || exit 1

4153 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4154 bin=$idir/$myname-$$.bin

4155 sql="select quote(bin) from $1 where rowid='$2';"

4156 ## err showattc: sql: $sql

4157 sq $db "$sql" | unhexize > $bin

4158 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4159 type=${tv%//*} fn=${tv#*//}

4160 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4161 ct=${type#file:}

4162 case $ct in # all text/* changed to text/plain

4163 text/*)

4164 charset=`nkf -g $bin|cut -d' ' -f1`

4165 case $charset in

4166 ASCII*) charset="" ;;

4167 esac

4168 if [ -z "$3" ]; then

4169 ct="text/html${charset:+; charset=$charset}"

4170 link="?showattc+$1+$2+raw"

4171 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4172 | sed 's,^,<span></span>,' \

4173 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4174 -D_LINK_="$link" \

4175 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4176 exit $?

4177 fi

4178 ct="text/plain${charset:+; charset=$charset}"

4179 ;;

4180 esac

4181 contenttype "$ct"

4182 echo "Content-Disposition: filename=\"$fn\""

4183 echo "Content-Length: " `cat $bin | wc -c`; echo

4184 #echo "Content-Type: " ${type#file:}; echo

4185 cat $bin

4186 }

4187 #

4188 # Some default stupid handler on CGI values

4189 #

4190 default_storedb() {

4191 # ARG: $1=table-def-file

4192 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4193 tbl=`basename $1`

4194 tbl=${tbl%.def}

4195 cols="`grep :text $1|cut -d: -f2`"

4196 col=`echo "$cols"|head -1`

4197 vcol=`getpar $col`

4198 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4199 if [ -n "$vcol" ]; then

4200 par2table $1

4201 else

4202 return 2 # No insertion occurred

4203 fi

4204 }

4205

4206 default_view() { # $1=def-file

4207 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4208 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4209 default_storedb "$@"

4210 query "select rowid from $tbl order by rowid desc;" \

4211 | while read rowid; do

4212 viewtable $1 $tbl $rowid

4213 done | _m4 -D_TITLE_="$tbl" \

4214 -D_FORM_="`genform $1`" \

4215 -D_DUMPTABLE_="syscmd(cat)" \

4216 $layout/html.m4.html $layout/form+dump.m4.html

4217 }

4218 default_viewtext() { # $1=def-file

4219 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4220 default_storedb "$@"

4221 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4222 | _m4 -D_TITLE_="$tbl" \

4223 -D_FORM_="`genform $1`" \

4224 -D_DUMPTABLE_="syscmd(cat)" \

4225 $layout/html.m4.html $layout/form+dump.m4.html

4226 }

4227 default_smail() {

4228 default_storedb "$@"

4229 if [ $? -eq 2 ]; then

4230 _m4 -D_TITLE_="入力" \

4231 -D_FORM_="`genform $1`" \

4232 -D_DUMPTABLE_="" \

4233 $layout/html.m4.html $layout/form+dump.m4.html

4234 return

4235 fi

4236 cond=""

4237 for pk in `gettblpkey $tbl`; do

4238 pv=$(sqlquote "$(getpar $pk)")

4239 cond="$cond${cond:+ and }$pk=$pv"

4240 done

4241 sql="select rowid from $tbl where $cond;"

4242 rowid=`query "$sql"`

4243 ## err smail1 - "$sql" "-> rowid=$rowid"

4244

4245 while IFS=: read prompt name keytype type args; do # Read from $1

4246 val=`getpar $name`

4247 if [ -n "$val" ]; then

4248 text="$text

4249 $prompt

4250 $name=$val

4251 ---------------------------------------------------------"

4252 fi

4253 case "$type" in

4254 image|document|file)

4255 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4256 fns=$(echo "$fn"|while read fn; do

4257 err mv $tmpd/$fn.orig $tmpd/$fn

4258 mv $tmpd/$fn.orig $tmpd/$fn

4259 rm $tmpd/$fn.rowid # Remove cache flag

4260 ## err "`ls $tmpd/$fn`"

4261 echo $fn

4262 done)

4263 files="$files $fns"

4264 ;;

4265 esac

4266 done < $1

4267 ## err FILES=$files "`ls -lF $tmpd`"

4268 subj="from ${REMOTE_ADDR}"

4269 (echo "$url"

4270 echo "への書き込みがありました。"

4271 echo "------"

4272 echo "$text"

4273 ) | (cd $tmpd &&

4274 err LS="`ls -lF`" &&

4275 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4276 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4277 echo "以下の内容で送信しました。" | html p

4278 viewtable $1 $tbl \

4279 `query "select rowid from $tbl order by rowid desc limit 1;"`

4280 echo "戻る" | html a "href=\"?\""

4281 }