s4: 025ef5578ae0 s4-funcs.sh

s4

view s4-funcs.sh @ 709:025ef5578ae0

Warn undeliverable recipient(s) that was removed from rcptlist.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 28 May 2020 19:27:03 +0900
parents	9bac1300f259
children	8a31b90f7815

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}

58 tconfs=""

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

60 conftbl=_tblconf

61 nl="

62 "

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

64 iconcachekey="profimgcache_S"

65 case "$HTTP_USER_AGENT" in

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

67 *) touchpanel="" ;;

68 esac

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

71 : <<EOF

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

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

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

76 レポート提出管理

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

79 [2016]

80 7/12 根本への反省

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

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

84 [以下2015]

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

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

87 Web

88 締切設定

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

91 ○自分の提出物リスト

93 7/19 ○設置

94 ○一斉送信

95 ○getparfilename の tmpd の扱い

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

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

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

99 ○添付ファイル回収

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

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

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

103

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

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

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

107 ○レポート提出モード

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

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

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

111

112

113 7/9 ○管理者の追加

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

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

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

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

118

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

120

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

122 ○グループ検索

123

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

125

126

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

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

129

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

131 ○6/1 par2tableを triplex 対応に

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

133 →とすると一気に

134

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

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

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

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

139

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

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

142 ## 1. passwd

143 ## 2. grp

144 ## 3. grp_mem

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

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

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

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

149

150 ## ● listの定義:

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

152 ## ● hashの定義:

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

154

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

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

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

158 ## csv2sq3 で .csv.sq3 の Makefile

159

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

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

162 ## type := root | comment

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

164 ## unique(id, filename)

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

166

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

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

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

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

171 ## できたー!

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

173 ↓

174 ↓以下に変更

175 with recursive allmem as

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

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

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

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

180

181

182 with recursive allmem as

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

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

185 where allmem.val=grp_m.gname)

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

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

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

189

190

191 ## triggerもできた。

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

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

194

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

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

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

198 ## グループ管理

199 ## 個人情報管理

200 ##

201

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

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

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

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

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

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

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

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

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

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

212

213 ## ログテーブル

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

215

216

217

218 ■表設計

219 * 3つの表に分散管理

220 id格納表 + hash表 + list表

221 * *_s *_m

222

223

224

225 user, user_map, user_col

226

227 ■抽象エントリタイプ

228 * user

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

230 * group

231 権限判定に利用

232 * serial

233 自動idとして機能

234 * password

235 入力 type=passwordで入力

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

237 * session

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

239 * text

240 入力 type=text

241 * textarea

242 入力 textarea

243 * image|document

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

245 * owner

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

247 * gowner

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

249 * timestamp

250 datetime()

251 * parent

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

253 * path

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

255

256 格納タイプ

257 * list

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

259 * hash

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

261

262 オブジェクトタイプ

263 * entry

264 id, title, owner

265 * textpart

266 id, parentID, text

267 * binarypart

268 id, parentID, contenttype, filename, content

269 * content

270 hash(textpart), list(binarypart)

271 * topic

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

273 * reply

274 id, parentID, content

275 * blog

276 list(entry)

277 blog = [topic, list(reply)]

278

279

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

281 "text" => "hogehoge ..",

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

283 "author" => "taro",

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

285 "parent" => "/",

286 "path" => "/1",

287 "text" => "blah, blah, ....",

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

289 {"serial" => 2,

290 "author" => "hanako",

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

292 "parent" => "/",

293 "path" => "/2",

294 "text" => "blah, blah, ....",

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

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

297

298

299 user:=

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

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

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

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

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

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

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

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

308

309 変換表

310 /user/email=m

311

312 blog:=

313 シリアル:id:p:serial

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

315 所有者:owner:s:owner:

316 時刻:ctime:s:stamp:

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

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

319

320 article:=

321 シリアル:id:p:serial

322 筆者:author:s:owner

323 時刻:ctime:s:stamp:

324 参照元:parent:s:parent:

325 パス:path:s:path:

326 本文:text:s:textarea:

327 画像:image:m:image:

328

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

330

331

332 EOF

333

334 logstart() {

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

336 }

337 logend() {

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

339 }

340 sqlog() {

341 logstart

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

343 cat >> $querylog

344 else

345 echo "$*" >> $querylog

346 fi

347 logend

348 }

349 sq() {

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

351 logstart

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

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

354 else

355 echo "$@" >> $querylog

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

357 fi

358 logend

359 }

360 dbsetup() {

361 [ -d $tmpdir ] || mkdir -m 1777 $tmpdir

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

363 sqi=$tmpdir/sqi.$$

364 sqo=$tmpdir/sqo.$$

365 mkfifo $sqi $sqo

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

367 sq $db < $sqi &

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

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

370 exec 2>> $tmpdir/error.out

371 fi

372 exec 3>> $tmpdir/debug.out

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

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

375 rm $sqi

376 # Attach supplemental DB

377 cat >&5 <<-EOF

378 .output /dev/null

379 ATTACH DATABASE "$sessdb" AS tmp;

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

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

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

383 .output stdout

384 EOF

385 }

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

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

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

389 fi

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

391 }

392 cleanup() {

393 trap '' INT HUP EXIT TERM PIPE

394 echo .quit >&5

395 kill $sq3pid

396 kill $sq3pid

397 rm -f $sqo $sqi

398 rm -rf $tmpfiles

399 cleanup2

400 }

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

402 # simple redirection for the sake of speed.

403 query() {

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

405 echo ".output $sqo" >&5

406 logstart

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

408 tee -a $querylog

409 else

410 echo "$@" >> $querylog

411 echo "$@"

412 fi >&5

413 echo ".output stdout" >&5

414 cat $sqo

415 logend

416 }

417 _m4() {

418 #S4NAME=f,f,f

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

420 }

421 ismember() {

422 # $1=user, $2=group

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

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

425 }

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

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

428 }

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

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

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

432 }

433 isgrpowner() (

434 # $1=user, $2=group

435 gn=`sqlquote "$2"`

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

437 err isgrpowner: $sql

438 test -n "`query $sql`"

439 )

440 isgrpownerbygid() (

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

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

443 err isgrpownerbygid: $sql

444 test -n "`query $sql`"

445 )

446 getgroupadminmails() {

447 # $1=group

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

449 email4group "$1" "$i" ;

450 done

451 }

452 getgroupadmins() { # $1=group

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

454 qgrp=`sqlquote "$1"`

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

456 }

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

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

459 getvalbyid grp $2 \

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

461 }

462 getgroupbyid() {

463 # $1=id|gname

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

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

466 # err ggbyid: `echo $sql`

467 query $sql

468 }

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

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

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

472

473 # Return true when anonymous mode

474 [ "$anonymousmode" ] && return 0

475 # case `getvalbyid blog mode $2` in

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

477 # *closed*)

478 # owner=`getvalbyid blog owner $2`

479 # if isgrp $owner; then

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

481 # elif isuser $owner; then

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

483 # fi

484 # esac

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

486 sql="with getblog as (

487 select key,val from blog_s where id=(

488 select blogid from article where id in

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

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

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

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

493 isgrpadm as (select user from grp_adm where

494 gname=(select val from getowner) and

495 user='$1'),

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

497 select case

498 when (select author from article where

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

500 then 'author'

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

502 then 'open'

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

504 then CASE

505 WHEN (SELECT val FROM isgrp) IS NULL

506 THEN

507 CASE WHEN (SELECT val from getowner)

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

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

510 ELSE ''

511 END

512 WHEN (select user from isgrpadm) IS NOT NULL

513 THEN 'i-am-admin'

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

515 END

516 when (select val from getmode) is null

517 then 'open'

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

519 then (SELECT user FROM isgrpadm)

520 when (select author from article where

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

522 then 'user+author'

523 else '' end;"

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

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

526 # result=`query "$sql"`

527 # err FileAccessibility=$result

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

529 }

530 linkhome() {

531 # $1=UserOrGroup

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

533 if isuser $1; then

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

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

536 else

537 echo -n "grp+$1"

538 fi

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

540 }

541 hreflink() {

542 # s4 specific notation:

543 # ^href=URL

544 # ^iframe=URL

545 # ^video=URL

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

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

548 # OSM umap Wikistyle Notation:

549 # [[URL]] - Simle Link

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

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

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

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

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

555 # Other Style

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

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

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

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

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

561 # SPC+SPC+$ - <br>

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

563 checkboxON="${cb} checked>"

564 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

589

590 }

591 minitbl() {

592 sed -n '

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

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

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

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

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

598 H; # Concat this line to HoldSpace

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

600 $ b cont

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

602 }

603 :cont

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

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

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

607 # . of ^. is workaround for FreeBSD sed

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

609 p; # Print whole "table" element

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

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

612 }

613 x; # Back to the newest line

614 p; # Print rest' | miniul

615 }

616 miniul() {

617 sed -e '

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

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

620 :top

621 s/\n//;

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

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

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

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

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

627 $ b cont

628 N; # 次の行を読む

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

630 /^ /b top

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

632 p; s/.*//;

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

634 /^\* /b top

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

636 }

637

638 :cont

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

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

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

642 H; x

643 }

644 x'

645 }

646 acclog() (

647 # $1=table, $2=rowid

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

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

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

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

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

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

654 fi

655 )

656 gecos() (

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

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

659 )

660 setpar() {

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

662 case "$1" in

663 user) _user="$v" ;;

664 skey) _skey="$v" ;;

665 esac

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

667 }

668 replpar() {

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

670 }

671 getpar() {

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

673 val=""

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

675 user) val=$_user ;;

676 skey) val=$_skey ;;

677 esac

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

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

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

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

682 fi

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

684 case "$var" in

685 owner)

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

687 echo $user; return

688 elif ismember $user $val; then

689 echo $val; return

690 fi ;;

691 esac

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

693 echo "$val"

694 }

695 setskey() {

696 # For quick response...(?)

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

698 }

699 chkskey() {

700 # $1=sesskey, $user=LoginUserName

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

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

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

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

705 return 0

706 fi

707 return 1

708 }

709 resetskey() {

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

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

712 fi

713 }

714 getpartype() {

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

716 }

717 getparcount() {

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

719 }

720 getparfilename() {

721 # null if type of $1 is not file

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

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

724 }

725 sqlquote() {

726 (v="$1"

727 case "$v" in

728 "") return ;; # null

729 "X'"*) # quoted hex string

730 echo $1 ;;

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

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

733 echo "\"$v\""

734 return ;;

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

736 echo "\"$v\""

737 return ;;

738 *)

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

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

741 else

742 echo "\"$v\""

743 fi ;;

744 esac)

745 }

746 sqlquotestr() (

747 case "$1" in

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

749 echo "'$v'" ;;

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

751 esac

752 )

753 mktempd() {

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

755 }

756 getcachedir() { # $1=maintable

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

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

759 else

760 echo $tmpd/$thumbxy

761 fi

762 }

763 getval() {

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

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

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

767 echo "$user" ;;

768 stamp|datetime)

769 date "+%F %T" ;;

770 serial)

771 (s=`getpar $2`

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

773 *)

774 getpar "$2" "$3";;

775 esac

776 }

777

778 getvalquote() {

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

780 (v=`getval "$@"`

781 case "$v" in

782 "") echo NULL ;;

783 *) sqlquote "$v" ;;

784 esac)

785 }

786 getparquote() {

787 sqlquote "`getpar $1`"

788 }

789 getbinbyid() {

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

791

792 }

793 getvalbyid() {

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

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

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

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

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

799

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

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

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

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

804 return

805 fi

806 done

807 rowid=$3

808 pk=`gettblpkey $1`

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

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

811 td=${4:-$tmpd}

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

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

814 for kt in s m; do

815 t=${1}_$kt

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

817 vcount=1 # count(val)

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

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

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

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

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

823 if [ $kt = m ]; then

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

825 # Reset val to store filenames if type is string

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

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

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

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

830 i=0

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

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

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

834 i=$((i+1))

835 fn=$c.$i

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

837 case $type in

838 file:*)

839 #file=$td/$val

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

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

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

843 # FOR SPEED: Skip file generation if imgcache exists

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

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

846 && continue

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

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

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

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

851 select rowid from $cond $slice;

852 .output '$td/$fn'

853 select val from $cond $slice;

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

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

856 .output stdout

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

858 EOF

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

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

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

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

863 ls -lh "$file" |

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

865 case "$type" in

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

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

868 *|*:[Aa]pplication*)

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

870 png:- > "$file"

871 ;;

872 esac

873 ;;

874 *)

875 sq $db<<EOF

876 .output $td/$fn.rowid

877 select rowid from $cond $slice;

878 .output $td/$fn

879 select val from $cond $slice;

880 EOF

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

882 ;;

883 esac

884 done

885 else

886 rm -f $td/$c.count

887 case $type in

888 file:*)

889 echo "$val" \

890 | while read fn; do

891 file=$td/$fn

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

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

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

895 | unhexize > "$file"

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

897 case $type in

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

899 *:[Aa]pplication*)

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

901 png:- > $file ;;

902 esac

903 fi

904 done

905 ;;

906 esac

907 fi

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

909 return

910 fi

911 done

912 done)

913 }

914 getvalbypkey() (

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

916 pk=`gettblpkey $1`

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

918 getvalbyid "$1" "$2" $rowid $4

919 )

920 getvalbycond() {

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

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

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

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

925 getvalbyid "$1" "$2" $rowid "$4"

926 fi

927 }

928 getpwfield() {

929 # getpwfield user column

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

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

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

933 echo "$val"

934 return 0

935 else

936 return 1

937 fi

938 }

939 numericalize() {

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

941 }

942 encode() {

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

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

945 sha1=sha1

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

947 sha1=sha1sum

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

949 sha1=gsha1sum

950 fi

951 fi

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

953 }

954 enjpeg() {

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

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

957 cjpeg="cjpeg"

958 else

959 cjpeg="convert - jpeg:-"

960 fi

961 fi

962 $cjpeg "$@"

963 }

964 mycrypt() (

965 key=$1 salt=$2

966 # err \$2=$2

967 case $2 in

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

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

970 esac

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

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

973 )

974 hexize() {

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

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

977 hexize="xxd -p"

978 else

979 hexize_hd() {

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

981 }

982 hexize="hexize_hd"

983 fi

984 fi

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

986 }

987 unhexize() {

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

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

990 unhex="xxd -p -r"

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

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

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

994 EOF

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

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

997 # world writable...:)

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

999 fi

1000 fi

1001 cat "$@" | $unhex

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

1003 }

1004 percenthex() {

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

1006 }

1007 htmlescape() {

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

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

1010 }

1011 enascii() {

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

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

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

1015 else

1016 enascii_now=`date +%FT%T`

1017 enascii_sed() {

1018 nkf -Z0Z1Z2 \

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

1020 }

1021 enascii="enascii_sed"

1022 fi

1023 fi

1024 cat "$@" | $enascii

1025 }

1026 size_h() {

1027 i="$1" oi=$1

1028 set -- B B KB MB GB TB

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

1030 oi=$i

1031 i=$((i/1024))

1032 shift

1033 done

1034 echo ${oi}$1

1035 }

1036 gettblconf() {

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

1038 ## tconfs=`sq $db \

1039 tconfs=`query \

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

1041 fi

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

1043 }

1044 gettblkeys() {

1045 # $1=tbl

1046 gettblconf

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

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

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

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

1051 case $type in

1052 *p*)

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

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

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

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

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

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

1059 esac

1060 case $type in

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

1062 esac

1063 done

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

1065 }

1066 gettblpkey() {

1067 # $1=tbl

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

1069 }

1070 gettblfkey() {

1071 (x=`gettblkeys $1`

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

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

1074 }

1075 gettblcols() {

1076 (x=`gettblkeys $1`

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

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

1079 }

1080 gettbl_s_cols() {

1081 (x=`gettblkeys $1`

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

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

1084 }

1085 gettbl_m_cols() {

1086 (x=`gettblkeys $1`

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

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

1089 }

1090 gettbl_h_cols() {

1091 (x=`gettblkeys $1`

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

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

1094 }

1095 gettbl_coltype() (

1096 gettblconf

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

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

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

1100 )

1101 is_hidden() {

1102 # $1=Tbl $2=col

1103 gettblconf

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

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

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

1107 case $x in

1108 *h*) return 0 ;;

1109 *) return 1 ;;

1110 esac

1111 }

1112

1113 dbsetbyid() {

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

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

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

1117 quotedp=$(sqlquotestr "$p")

1118 unset primary update

1119 gettblconf

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

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

1122 #err conf=$conf

1123 case ${conf#*=} in

1124 p*) primary=1 ;;

1125 f*) update=1 ;;

1126 u*) ;;

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

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

1129 esac

1130 #err t=$t

1131 type=string fn=""

1132 case $conf in

1133 */password)

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

1135 ;;

1136 */image*|*/document*)

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

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

1139 ;;

1140 esac

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

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

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

1144 if [ "$primary" ]; then

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

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

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

1148 elif [ "$update" ]; then

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

1150 else

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

1152 fi

1153 )

1154 }

1155 expire() (

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

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

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

1159 )

1160 addsession() {

1161 # expireをセット

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

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

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

1165 # Remove old session parameters

1166 now=`expire now`

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

1168 }

1169 gencookie() (

1170 for kv; do

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

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

1173 done

1174 )

1175 contenttype() {

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

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

1178 }

1179 putheader() {

1180

1181 }

1182 putfooter() {

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

1184 }

1185 getcookie() {

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

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

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

1189 case "$k" in

1190 user) _user="$v" ;;

1191 skey) _skey="$v" ;;

1192 esac

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

1194 done

1195 }

1196 genrandom() {

1197 # $1=columns (default: 10)

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

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

1200 }

1201 genserial() {

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

1203 }

1204 smail() {

1205 # smail rcpts subj (file)

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

1207 # $MAIL_FROM <- From: header value

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

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

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

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

1212 sender=${SENDER:-$admin}

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

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

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

1216 }

1217 smail_queue_flush() {

1218 # $1=timelimit

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

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

1221 SELECT rowid FROM smailq

1222 GROUP BY rcpts, subj

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

1224 EOF

1225 )

1226 for rid in $rowids; do

1227 sq $workdb \

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

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

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

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

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

1233

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

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

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

1237 .separator "\n" "------------------\n\n"

1238 SELECT time, text FROM smailq

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

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

1241 ORDER by time;

1242 EOF

1243 then

1244 echo rowid=$rid

1245 cat <<-EOF | sq $workdb

1246 DELETE FROM smailq

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

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

1249 EOF

1250 fi

1251 fi

1252 done

1253 }

1254 smail_queue() {

1255 # $1=Rcpts, $@=subj

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

1257 if [ $? -eq 0 ]; then

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

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

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

1261 err "rcpts=[$rcpts] s=[$subj] t=[$text]"

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

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

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

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

1266 EOF

1267 )

1268 ## XXX: Adhoc load mitigation

1269 case "$now" in

1270 *[01])

1271 err flush_queue=$mintime

1272 smail_queue_flush ;;

1273 esac

1274 fi

1275 }

1276 setviastring() {

1277 table=par

1278 oifs="$IFS"

1279 IFS="&"

1280 for us in $1; do

1281 k=${us%%=*}

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

1283 setpar "$k" "string" "$v"

1284 done

1285 IFS="$oifs"

1286 }

1287 checkdomain() (

1288 # Check the validity of domain by referring DNS

1289 item=$1

1290 err checkdomain $1

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

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

1293 )

1294 pwcheck() {

1295 # $1=passwd

1296 dbpswd=`getpwfield $user pswd`

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

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

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

1300 }

1301 mypwhash() {

1302 mycrypt "`cat`" `genrandom 5`

1303 }

1304 wasureta() {

1305 user=$1

1306 if ! checkdomain $user; then

1307 contenttype; echo

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

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

1310 putfooter

1311 exit 0

1312 fi

1313 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1315 encpswd=`echo $newpswd|mypwhash`

1316 dbsetbyid user $user pswd "$encpswd"

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

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

1319 $msgdir/mail-newaccount.m4 \

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

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

1322 }

1323 checkauth() {

1324 user=`getpar user`

1325 skc=`getpar skey` # from cookie

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

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

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

1329 if chkskey "$skc"; then

1330 return 0

1331 fi

1332 fi

1333 pswd=`getpar pswd`

1334 quser=`sqlquotestr "$user"`

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

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

1337 return 4 # 4=server too heavy

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

1339 return 2 # 2=login fail

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

1341 wasureta "$user"

1342 return 1 # wasureta error

1343 fi

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

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

1346 if pwcheck "$pswd"; then

1347 newsession=`genrandom 50`

1348 if setskey "$user" "$newsession" &&

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

1350 gencookie "user=$user" "skey=$newsession"

1351 return 0

1352 else

1353 return 4 # Heavy load??

1354 fi

1355 fi

1356 return 2 # Password mismatch

1357 }

1358 showlogin() {

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

1360 test -z "$args" && resetskey

1361 s4name=${S4NAME:-s4}

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

1363 cat $layout/login.m4.html

1364 echo '</body></html>'

1365 ) | _m4 \

1366 -D_BODYCLASS_="login" \

1367 -D_TITLE_="$s4name" \

1368 -D_SYSNAME_="Welcome to $s4name" \

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

1370 exit 0

1371 }

1372 dologin() {

1373 checkauth

1374 st=$?

1375 if [ $st != 0 ]; then

1376 contenttype; echo

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

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

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

1380 showlogin # and EXIT

1381 fi

1382 }

1383

1384 # Do instant jobs here

1385 dbsetup

1386 trap cleanup INT HUP EXIT TERM PIPE

1387 # trap cleanup INT HUP

1388

1389 err() {

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

1391 }

1392

1393 cgiinit() {

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

1395 tmpf=tmp/stream

1396 tmpd=`tmpd=$tmpdir mktempd`

1397 tmpfiles=$tmpfiles" $tmpd"

1398 addsession $session

1399 getcookie

1400 case "$REQUEST_METHOD" in

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

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

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

1404 # cat > $tmpf # too much?

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

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

1407 s="`cat tmp/stream`"

1408 tmpfiles=$tmpfiles"${tmpfiles+ }$tmpf"

1409 ;;

1410 esac

1411 case "$CONTENT_TYPE" in

1412 *boundary*)

1413 bndry=${CONTENT_TYPE#*boundary=}

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

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

1416 do

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

1418 #echo u=$us

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

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

1421 # err k=$k v=$v

1422 case "$k" in

1423 *:filename)

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

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

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

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

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

1429 :

1430 fi

1431 # (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3

1432 case "$mimetype" in

1433 [Ii]mage/x-xcf)

1434 bzip2 "$tmpd/$v"

1435 v=${v}.bz2

1436 ;;

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

1438 [Ii]mage/*)

1439 mogrify -resize $maximagexy'>' "$tmpd/$v"

1440 ;;

1441 esac

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

1443 then

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

1445 continue

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

1447 replpar text string \

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

1449 continue

1450 fi

1451 ;;

1452 *)

1453 type='string'

1454 ;;

1455 esac

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

1457 setpar "$k" "$type" "$v"

1458 done

1459 ;;

1460 *)

1461 setviastring "$s"

1462 ;;

1463 esac

1464 }

1465 email4group() {

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

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

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

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

1470 sql="WITH

1471 grpemails AS (

1472 SELECT gname, user, val email

1473 FROM grp_mem NATURAL JOIN grp_mem_s

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

1475 useremails AS (

1476 SELECT user.name, val email

1477 FROM user

1478 LEFT JOIN user_m

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

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

1481 FROM useremails u LEFT JOIN grpemails g

1482 ON u.name=g.user

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

1484 query "$sql"

1485 }

1486 email4groupbyuid() {

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

1488 g=$1; shift

1489 uids=`echo "$@"`

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

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

1492 email4group "$g" `query "$sql"`

1493 }

1494 myemail4group() {

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

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

1497 }

1498 collectmembersbyid() {

1499 # Collect user names of group specified by grid

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

1501 query "SELECT user FROM grp_mem \

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

1503 }

1504 collectmembersbyid() {

1505 # Collect user names of group name

1506 qgrp=`sqlquote "$1"`

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

1508 }

1509 collectgecosesbyid() {

1510 # Collect user gecoses of group

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

1512 query<<-EOF

1513 SELECT gecos

1514 FROM gecoses

1515 WHERE name IN (SELECT user FROM grp_mem

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

1517 EOF

1518 }

1519 collectemail() (

1520 # Collect email addresses for group $1

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

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

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

1524 for e; do

1525 if isuser "$e"; then

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

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

1528 else

1529 qgrp=`sqlquote "$e"`

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

1531 gmem="grp_mem"

1532 else

1533 tm=`sqlquote "$TEAM"`

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

1535 fi

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

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

1538 $gmem g left join grp_mem_s s

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

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

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

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

1543 query "$sql"

1544 fi

1545 done

1546 )

1547 sendinvitation() (

1548 # $1=email

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

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

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

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

1553 gecos=`gecos`

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

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

1556 _m4 -D_URL_="$urlbase" \

1557 -D_USER_="$name" \

1558 -D_EMAIL_="$1" \

1559 -D_REGIST_="$regist" \

1560 -D_ADMIN_="$admin" \

1561 $msgdir/mail-invite.m4 \

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

1563 return 0

1564 )

1565 emaildomaincheck() {

1566 case "$1" in

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

1568 *@*)

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

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

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

1572 return 2

1573 fi

1574 return 0

1575 ;;

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

1577 esac

1578 }

1579 invite() {

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

1581 case "$email" in

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

1583 *@*)

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

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

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

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

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

1589 elif sendinvitation $email; then

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

1591 else # Cannot be reached here

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

1593 fi ;;

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

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

1596 esac

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

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

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

1600 fi

1601 if [ -f $invite_policy ]; then

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

1603 else

1604 pol="$invite_policy"

1605 fi

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

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

1608 -D_INVITE_POLICY_="$pol" \

1609 $layout/html.m4.html $layout/invite.m4.html

1610 }

1611 regist() {

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

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

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

1615 echo "bye bye" | html p

1616 reutrn

1617 fi

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

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

1620 cat<<EOF

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

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

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

1624 EOF

1625 return

1626 fi

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

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

1629 # Fake login password to wasureta

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

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

1632 wasureta $email

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

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

1635 addsession $1 # for removal after 1 minute

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

1637 $layout/login.m4.html

1638 return

1639 }

1640 group_safename() {

1641 # Convert $1 to safe group name

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

1643 }

1644 groupupdate() {

1645 gname=`getpar gname`

1646 qgname=`sqlquote "$gname"`

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

1648 # See ALSO same job in showgroup()

1649 newgname=`group_safename "$gname"`

1650 err newgname=$newgname

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

1652 err NewGNAME: gname=$newgname

1653 gname=$newgname

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

1655 replpar gname string "$gname"

1656 fi

1657 # Name confliction check

1658 parow=`getpar rowid`

1659 ## err parow=$parow

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

1661 query "BEGIN EXCLUSIVE;"

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

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

1664 if [ $count -gt 0 ]; then

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

1666 query "END;"

1667 return

1668 fi

1669 par2table $formdir/grp.def

1670 query "END TRANSACTION;"

1671 # Remove orphan

1672 : <<EOF

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

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

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

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

1677 EOF

1678 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1682 sql="delete from blog where id in

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

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

1685 query "$sql";

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

1687 return # and return

1688 fi

1689 fi

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

1691 fi

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

1693 grid=$(query $sql)

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

1695 grp $grid

1696 }

1697 groupclone() {

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

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

1700 case "$1" in

1701 */noteam)

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

1703 esac

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

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

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

1707 return

1708 fi

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

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

1711 return

1712 fi

1713 i=0

1714 while true; do

1715 copy="-copy$i"

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

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

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

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

1720 i=$((i++))

1721 continue

1722 fi

1723 break

1724 done

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

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

1727 query<<-EOF

1728 BEGIN;

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

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

1731 SELECT $newqname, key, val

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

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

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

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

1736 -- Copy members and their configuration --

1737 REPLACE INTO grp_mem SELECT $newqname, user

1738 FROM grp_mem WHERE gname=$qgrp;

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

1740 FROM grp_mem_s WHERE gname=$qgrp;

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

1742 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1743 -- Copy administrators --

1744 REPLACE INTO grp_adm SELECT $newqname, user

1745 FROM grp_adm WHERE gname=$qgrp;

1746 COMMIT;

1747 EOF

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

1749 STOPCLONEMSG=1 groupconf "$newrowid"

1750 }

1751 groupman() {

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

1753

1754 GF_STAGE="grpconf"

1755 GF_STAGE=groupupdate

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

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

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

1759 -D_DUMPTABLE_="syscmd(cat)" \

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

1761 }

1762 userconf() {

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

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

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

1766 }

1767 groupconf() {

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

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

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

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

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

1773

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

1775 grp=`getgroupbyid $rowid`

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

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

1778 return

1779 fi

1780

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

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

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

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

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

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

1787 <div>

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

1789 <table>

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

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

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

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

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

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

1796 </table>

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

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

1799 </div>

1800 EOF

1801 fi

1802 }

1803 mems() {

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

1805 kwd=`getpar kwd`

1806 listmember $kwd

1807 }

1808 grps() {

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

1810 kwd=`getpar kwd`

1811 listgroup $kwd \

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

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

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

1815 $layout/form+dump.m4.html

1816 }

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

1818 gpg=`getpar grp`

1819 grid=${1:-$gpg}

1820 grp=`getgroupbyid "$grid"`

1821 ## . ./s4-blog.sh

1822 jg=`getpar joingrp`

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

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

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

1826 fi

1827 htmlheader=$layout/html.m4.html

1828 showgroup "$grid"

1829 }

1830 sql4interestblogs() {

1831 cat<<EOF

1832 CREATE TEMPORARY VIEW interestblogs AS

1833 SELECT blog.rowid rid, id, author

1834 FROM blog

1835 NATURAL JOIN

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

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

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

1839 WHEN (SELECT user FROM grp_mem

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

1841 THEN 0

1842 ELSE 1

1843 END;

1844 EOF

1845 }

1846 listnewblogsql() { # $1=user

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

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

1849 newdays=${newdays:-14}

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

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

1852 WHERE user='$user'

1853 AND tblrowid IN

1854 ($blogreadflagrowid,

1855 $blogcutoffflagrowid)),

1856 $basetime -- "0"

1857 );"`

1858 cat<<EOF

1859 `sql4interestblogs`

1860 WITH article_ctime as (

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

1862 FROM article join article_s s using(id)

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

1864 GROUP BY id

1865 ), blog_title_owner as (

1866 SELECT blg.rid brid, id,

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

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

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

1870 ), blogall as (

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

1872 ), news as (

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

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

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

1876 FROM blogall bl

1877 LEFT JOIN

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

1879 ON bl.brid=al.tblrowid

1880 WHERE atime < bl.ctime

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

1882 LIMIT 10

1883 ) SELECT brid LINK, "新着",

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

1885 ctime, title,

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

1887 FROM news;

1888 EOF

1889 }

1890

1891 search_form() {

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

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

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

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

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

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

1898 @week → 最近一週間

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

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

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

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

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

1904 auth=""

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

1906 case "$1" in

1907 author=*)

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

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

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

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

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

1913 ;;

1914 grp=*)

1915 a=`echo "${1#grp=}"|htmlescape`

1916 g=`gecos ${1#grp=}`

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

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

1919 ;;

1920 esac

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

1922 cat<<-EOF

1923 <div class="right">

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

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

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

1927

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

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

1930

1931 </form>

1932 </div>

1933 EOF

1934 }

1935

1936 imgsrc_cache() (

1937 # $1 = directory for cache'ing

1938 # $2 = table (user_m or grp_m)

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

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

1941 dir="$1" tbl="$2"

1942 keycond="$3"

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

1944 ORDER BY rowid DESC LIMIT 1"

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

1946 tmpf=$tmpd/imgsrc_cache.$$

1947 case "$4" in

1948 [Ss]) size=S ;;

1949 [Oo]) size=O ;;

1950 *) size=M ;;

1951 esac

1952 # ImageCache filename storing schema:

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

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

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

1956 valtype=`query "$sql0"`

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

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

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

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

1961 case "$filetype" in

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

1963 esac

1964 fi

1965 cacheimg_S=$dir/S_$filename

1966 cacheimg_M=$dir/M_$filename

1967 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1975 else

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

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

1978 fifo2=$fifo.2

1979 mkfifo $fifo $fifo2

1980 fmt=${filename##*.}

1981 ## [[ NOTE ]]

1982 ## a. convert oldimage newimage

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

1984 ## b is much smaller than a

1985 cat $tmpf | unhexize \

1986 | tee $fifo \

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

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

1989 | tee $fifo2 \

1990 | convert - "$cacheimg_M" &

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

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

1993 | convert - "$cacheimg_S" &

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

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

1996 echo '">'

1997 echo "$sum" > $sumfile

1998 fi

1999 ## Now preparing cache image, done.

2000 ## Store this information to DB

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

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

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

2004 query <<-EOF

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

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

2007 EOF

2008 )

2009

2010 showhome() {

2011 # $1=userRowIdToShow

2012 err showhome \$1=$1

2013 case "$1" in

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

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

2016 esac

2017 ## err ShowHome: uname=$uname

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

2019 gecos=`gecos "$uname"`

2020 ## err SH:gecos=$gecos

2021 GF_VIEWONLY=1

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

2023 search_form_args=""

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

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

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

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

2028 # Display folders

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

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

2031 and type like 'file:%';"

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

2033 nfile=`query "$sql"`

2034 # err nfile=$nfile

2035 if [ $nfile -gt 0 ]; then

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

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

2038 fi

2039 else

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

2041 GROUP BY user;"`

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

2043 search_form_args="author=$uname"

2044 fi

2045 . ./s4-blog.sh

2046

2047 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2048 search_form "$search_form_args" > $sf

2049 printf "%s さん" "$gecos" > $tf

2050 { echo "<div class=\"noprofimg\">"

2051 viewtable $formdir/user.def user $1

2052 echo "</div>"

2053 } > $pf

2054

2055 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2056 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2057 imf=$tmpd/profimg.$$; touch $imf

2058 if [ -n "$img" ]; then

2059 if true; then

2060 tbl=user_m

2061 enticond="name='$uname'"

2062 imgsrc_cache "$td/main" user_m "$enticond" M

2063 else

2064 { printf '%s' "<IMG src=\"data:${img#file:},"

2065 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2066 | sed 's/$..$/%\1/g'

2067 echo '">'

2068 }

2069 fi > $imf

2070 fi

2071 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2072 val='$uname';"`

2073 ## REMOVE This comment block until 2019/7/1

2074 ## err "----- `gdate +%FT%T.%3N` ------------C"

2075 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2076 listblog $uname > $bf

2077 ## unset ddd

2078 ## err "----- `gdate +%FT%T.%3N` ------------D"

2079

2080 hometail=$tmpd/tail.$$

2081 mkfifo $hometail

2082

2083 #Calling listgroupbytable, originally here

2084

2085 (

2086 # Display Most Recent Entry

2087 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2088 shortval=${shortval:-val}

2089

2090 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2091 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2092 ctime,

2093 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2094 (SELECT gecos FROM gecoses

2095 WHERE name=(SELECT val FROM blog_s

2096 WHERE key='owner' AND id=b.id)) owner,

2097 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2098 FROM blog b

2099 JOIN

2100 (SELECT distinct blogid, a.id aid, max(val) ctime

2101 FROM article a, article_s s

2102 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2103 GROUP BY blogid ORDER BY val DESC LIMIT 50

2104 ) m

2105 ON b.id=m.blogid;"

2106 # This should be as follows

2107 : <<EOF

2108 WITH arts AS(

2109 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2110 a.blogid, a.id id, s.val ctime

2111 FROM article a NATURAL JOIN article_s s

2112 WHERE s.key = 'ctime' AND a.author='$user'

2113 GROUP by s.id

2114 )

2115 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2116 FROM arts a0

2117 JOIN

2118 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2119 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2120 ORDER BY ctime DESC LIMIT 50;

2121 EOF

2122

2123 cat<<-EOF

2124 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2125 for="mre" title="Shortcut: D${nl}Recent Post">最近の書き込み先</label>

2126 <div class="lcto">

2127 `DT_VIEW=replyblog dumptable html blog`

2128 </div>

2129 EOF

2130 unset DT_SQL

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

2132 # Display NEWS

2133 # 2016-06-26

2134 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2135 acclog blog $blogreadflagrowid

2136 # echo "全部既読にしました" | html p

2137 fi

2138 # 2016-02-19 Counting NEWS without using dumptable.

2139 sql=`listnewblogsql "$user"`

2140 # echo "$sql" > tmp/listnew

2141 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2142 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2143 cont=$((cont-1))

2144 err newcount=$cont

2145 if [ $cont -gt 0 ]; then

2146 #echo "全体の新着記事${cont}傑" | html h2

2147 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2148 echo "<label for=\"new10\" title=\"Shortcut: F${nl}NEWS\">新着${cont}傑</label><div>"

2149 cat<<-EOF | html form 'action="?home"'

2150 `cgi_checkbox readchk yes 'id="read"'`<label

2151 for="read">新着ふくめて全部読んだことにする</label>

2152 `cgi_submit '確定'`

2153 `cgi_hidden read yes`

2154 EOF

2155 echo "$new10 "

2156 echo "</div>"

2157 else # If news is 0, set log cut off flag

2158 acclog blog $blogcutoffflagrowid # for speed

2159 fi

2160 else # Not My Home ($user != $uname)

2161 : # DT_SQL=

2162 fi

2163 ) > $hometail & # Is background call safe to m4??

2164 #

2165 listgroupbytable $formdir/grp.def $cond |

2166 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2167 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2168 -D_PROFIMG_="spaste(\`$imf')" \

2169 -D_BLOGS_="spaste(\`$bf')" \

2170 -D_SEARCH_="spaste(\`$sf')" \

2171 -D_NBLOG_="$nblog" \

2172 -D_GROUPS_="syscmd(\`cat')" \

2173 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2174 $layout/html.m4.html $layout/home.m4.html

2175

2176 # Record access log

2177 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2178 }

2179 commission() { # $1=grp-rowid $2=user-rowid

2180 contenttype; echo

2181 ## err commission: "$@"

2182 gname=`getgroupbyid $1`

2183 echo "グループ $gname 管理者委任" \

2184 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2185 if [ -n "$2" ]; then

2186 grp_reg_adm "$@"

2187 else

2188 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2189 fi

2190 }

2191 listgroupbytable() {

2192 # $1=deffile $2...=condition

2193 tagline=`grep :tag: $1`; shift

2194 and="${1:+and }" where=${1:+where }

2195 href="<a href=\"$myname?grp+"

2196 echo '<div class="listgroup">'

2197 NGsql="select distinct tag from\

2198 (select gname, max(case key when 'tag' then val end) as tag, \

2199 max(case key when 'ctime' then val end) as ctime\

2200 from grp_s group by gname order by ctime);"

2201 sql="select val from grp_s where key='tag' $and$* group by val;"

2202 ## err ListGRP: query sql="$sql"

2203 for tag in `query "$sql"`

2204 do

2205 ## err ListGrp: tag=$tag

2206 tn=${tagline%%=${tag}*}

2207 tn=${tn##*[ :]}

2208 sql="select rowid||':'||gname as 'グループ名',説明 from

2209 (select (select rowid from grp g where g.gname=grp_s.gname)

2210 as rowid,

2211 gname,

2212 max(case key when 'gecos' then val end) as '説明',

2213 max(case key when 'tag' then val end) as 'tag',

2214 max(case key when 'mtime' then val end) as mtime from grp_s

2215 $where$* group by gname having tag='$tag' order by mtime desc);"

2216 ## err PersonalGroupList= `echo $sql`

2217 echo "<h2>$tn</h2>"

2218 echo '<table class="b listgroup">'

2219 sq -header -html $db "$sql" \

2220 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2221 echo '</table>'

2222 done

2223 echo '</div>'

2224 }

2225 iconhref() (

2226 # $1=icon-file, $2=Href $3=title $4...=anchor

2227 data=`percenthex "$1"`

2228 ct=`file --mime-type - < "$1"|cut -d' ' -f2`

2229 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2230 href=$2; title=$3; shift 3

2231 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2232 )

2233 iconhref2() (

2234 # $1=icon-file, $2=Href $3=title $4...=anchor

2235 src=$1

2236 href=$2; title=$3; shift 3

2237 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2238 )

2239 listentry() (

2240 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2241 # Referring variable $iamowner=$grp to attach owner-request links

2242 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2243 cond='' hiddens=''

2244 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2245 if [ -z "$offset" ]; then

2246 offset=`getpar start`; offset=${offset%%[!0-9]*}

2247 offset=$((offset-1))

2248 fi

2249 offset=$((offset + 0)) # change to numeric forcibly

2250 [ $offset -lt 0 ] && offset=0

2251 limit=$listentlimit

2252 dir=`getcachedir "$1"`

2253 if [ x"$1" = x"user" ]; then

2254 hrb="$myname?home"

2255 deficon=person-default.png

2256 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2257 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2258 gcs=gecos

2259 else # if group

2260 hrb="$myname?grp"

2261 deficon=group-default.png

2262 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2263 gcs=name

2264 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2265 if [ -n "$tagline" ]; then

2266 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2267 ## err tagconv=$tagconv

2268 fi

2269 fi

2270 if [ ! -d $dir ]; then

2271 mkdir -p $dir

2272 fi

2273 if [ ! -s $dir/$deficon ]; then

2274 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2275 fi

2276 if [ -n "$2" ]; then

2277 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2278 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2279 fi

2280 tag=`getpar tag` tag2=`getpar tag2`

2281 if [ x"$tag" = x"NULL" ]; then

2282 tag="" tag2=""

2283 fi

2284 if [ -n "$tag$tag2" ]; then

2285 tag=${tag:-$tag2}

2286 qtag=`sqlquote "$tag"`

2287 cond2="tag=$qtag"

2288 fi

2289 if [ -n "$cond1$cond2" ]; then

2290 cond="$cond1${cond2:+ AND $cond2}"

2291 cond="WHERE ${cond# AND }"

2292 fi

2293

2294 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2295 # grpは呼出し元の動的スコープ変数でよくないな...

2296 ##qgrp=`sqlquote $grp`

2297 getgrp="(select gname from grp where rowid=${rowid:--1})"

2298 sql="select a.rowid, a.$link,

2299 coalesce(b.$gcs, a.$nm) as nick,

2300 quote(a.$nm) as qname,

2301 (SELECT val FROM ${tbl}_s

2302 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2303 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2304 || case when a.$nm in (select gname from grp)

2305 then printf('(%d名)',

2306 (select count(user) from grp_mem where gname=a.$nm))

2307 else ' <'||a.$nm||'>'

2308 end

2309 as name,

2310 b.tag,

2311 case when a.$nm in (select user from grp_adm

2312 where gname=$getgrp) then '管理者'

2313 when '$user' in (select user from grp_adm where gname=a.$nm)

2314 then 'ADMIN'

2315 when '$user' in (select user from grp_mem where gname=a.$nm)

2316 then 'Member'

2317 when '$iamowner' = '' then ''

2318 else ',not='||a.rowid end as ownerlink,

2319 CASE '$entity'

2320 WHEN 'グループ'

2321 THEN coalesce(

2322 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2323 'open')

2324 ||

2325 CASE WHEN '$user'

2326 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2327 THEN ' ismember'

2328 ELSE ''

2329 END

2330 ELSE 'user'

2331 END regmode

2332 from $tbl a left join

2333 (select $nm as name,

2334 max(case key when 'gecos' then val end) as gecos,

2335 max(case key when 'tag' then val end) as tag,

2336 max(case key when 'mtime' then val end) as mtime,

2337 max(case key when 'wtime' then val end) as wtime,

2338 max(case key when 'login' then val end) as login

2339 from ${tbl}_s group by $nm)

2340 b on a.$nm=b.name $cond $3

2341 order by b.wtime desc, b.login desc,

2342 b.mtime desc, b.tag desc, a.rowid asc"

2343 # Give precedence to newer maintained groups (2016-09-24)

2344 # Note that mtime is stored only in grp_s.

2345 ## err LE:sql.1="$sql"

2346 total=`query "with x as ($sql) select count(*) from x;"`

2347 echo "${entity} 一覧" | html h2

2348 echo '<div class="listentry">' # List-entry div

2349 # Show owner/member filter button

2350 METHOD=GET

2351 hiddens="$hiddens

2352 `cgi_hidden kwd \"$kwd\"`

2353 `cgi_hidden stage \"$stage\"`"

2354 if [ x"$tbl" = x"grp" ]; then

2355 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2356 fh="<select name=\"tag\">$nl"

2357 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2358 for l in $args; do

2359 val=${l#*=} tname=${l%=*}

2360 if [ x"$val" = x"$tag" ]; then

2361 s=" selected"

2362 selectedtags="(種別[${tname}]のみ)"

2363 else

2364 s=""

2365 fi

2366 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2367 done

2368 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2369 cat<<-EOF

2370 <form action="$myname" method="$METHOD">

2371 </form>

2372 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2373 for="ismembtn">参加中以外隠す</label>

2374 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2375 for="isadmbtn">管理者参加以外隠す</label>

2376 EOF

2377 # limit=3

2378 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2379 fi

2380 if [ $total -gt $limit ]; then

2381 echo '<div>'

2382 METHOD=GET cgi_form $stage <<EOF

2383 $form

2384 <label>次の語を含む${entity}で検索:

2385 `cgi_text kwd "$kwd"`</label>

2386 $hiddens

2387 EOF

2388 echo '</div>'

2389 else

2390 echo $selectedtags | html p

2391 fi

2392 cat<<EOF

2393 <form action="$myname" method="$METHOD">

2394 <p>${total}件中の<input class="hidesub" type="text" name="start"

2395 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2396 <input type="submit" value="確定"></p>

2397 </form>

2398 EOF

2399 if [ $((offset+limit)) -lt $total ]; then

2400 nextbtn=$(

2401 cat<<EOF

2402 <div class="right clear"><form action="$myname" method="$METHOD">

2403 `cgi_submit 次の${limit}件`

2404 $hiddens

2405 `cgi_hidden offset $((offset + limit))`</form></div>

2406 EOF

2407 )

2408 fi

2409 if [ $offset -gt 0 ]; then

2410 prevbtn=$(

2411 cat<<EOF

2412 <form action="$myname" method="$METHOD">

2413 `cgi_submit 前の${limit}件`

2414 $hiddens

2415 `cgi_hidden offset $((offset - limit))`</form>

2416 EOF

2417 )

2418 fi

2419 pnbtn="$nextbtn$prevbtn"

2420 echo $pnbtn

2421

2422 ## err ListEntry: `echo "$sql"\;`

2423 # sq $db here??? 2016-11-28

2424 query "$sql limit $limit ${offset:+offset $offset};" \

2425 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2426 # err name=$name owner=$ownerp lnk=$lnk

2427 # err newlnk=$lnk regmode=$regmode

2428 icondir=$dir/$id

2429 # Pick up only last icon

2430 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2431 <p class=\"tag _$tag\">$tag</p>" \

2432 | _m4 $tagconv

2433 if [ -n "$NOSPEEDUP" ]; then

2434 files=`getvalbyid $tbl profimg $id $icondir`

2435 if [ -n "$files" ]; then

2436 icon=`echo "$files"|tail -1`

2437 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2438 else

2439 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2440 fi

2441 elif [ -n "$icon" -a -s "$icon" ]; then

2442 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2443 else

2444 cond="$nm=$qname"

2445 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2446 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2447 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2448 # err "img=[$img]"

2449 if [ -n "$img" ]; then

2450 echo "<a href=\"$hrb+$lnk\">"

2451 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2452 echo "</a>"

2453 else

2454 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2455 fi

2456 fi

2457 echo "<br>$name${ownerp:+<br>($ownerp)}"

2458 echo "</div>"

2459 done

2460 echo "</div>" # End of List-entry div

2461 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2462 )

2463 listmember() {

2464 listentry user "$@"

2465 }

2466 listgroup() {

2467 listentry group "$@"

2468 }

2469 hexteams() { # $1=gname, $2(optional)=user

2470 cond=${2:+" AND user='$2'"}

2471 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2472 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2473 }

2474 showgroup() { # $1=group-rowid

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

2476 grid=`getpar grid`

2477 grid=${grid%%[!0-9]*}

2478 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2479 else

2480 grid=$1

2481 fi

2482 grp=`getgroupbyid $grid`

2483 qgrp=`sqlquote "$grp"`

2484 htmlgrp=`echo "$grp"|htmlescape`

2485 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2486 if isgroup "$grp"; then

2487 tf=$tmpd/title.$$

2488 sf=$tmpd/search.$$

2489 bodyclass=`query "SELECT val FROM grp_s

2490 WHERE gname=$qgrp AND key='regmode';"`

2491 if ismember "$user" "$grp"; then

2492 ismember="ismember"

2493 bodyclass="$bodyclass${bodyclass:+ }ismember"

2494 else

2495 ismember="" # bodyclass="group"

2496 fi

2497 bodyclass="$bodyclass grouphome"

2498 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2499 echo "グループ $htmlgrp" > $tf

2500

2501 showgroupsub $formdir/grp.def "$grid" | \

2502 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2503 -D_FORM_="syscmd(\`cat')" \

2504 -D_BODYCLASS_="$bodyclass" \

2505 -D_DUMPTABLE_="" \

2506 $htmlheader $sf $layout/form+dump.m4.html

2507 # $htmlheader $layout/form+dump.m4.html

2508 # $htmlheader is defined in grp()

2509 else # if $grp is removed at par2table

2510 listgroup

2511 fi

2512 }

2513 showgroupsub() {

2514 # $1=def-file $2=group-rowid

2515 # Using $ismember

2516 rowid=$2

2517 grp=`getgroupbyid $2`

2518 qgrp=`sqlquote "$grp"`

2519 td=`getcachedir grp/"$2"`

2520 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2521 if [ -z "$rowid" ]; then

2522 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2523 #grp=`sq $db "select gname from grp where rowid=$grp"`

2524 echo "showgroupsub: invalid argument($1 $2)" | html p

2525 return

2526 fi

2527 val=`getvalbyid grp profimg $rowid $tmpd`

2528 enticond="gname=$qgrp"

2529 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2530 if [ -n "$img" ]; then

2531 cat<<-EOF

2532 <p class="groupimg">

2533 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2534 EOF

2535 fi

2536 echo "<div class=\"noprofimg\">"

2537 viewtable $1 grp $rowid

2538 echo "</div>"

2539 if isgrpowner "$user" "$grp"; then

2540 echo "<p><a href=\"?groupconf+$rowid\" accesskey=\"e\"

2541 title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"

2542 iamowner=$rowid

2543 colmd=" mode"

2544 fi

2545 if [ -n "$ismember" ]; then

2546 #echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2547 #echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2548 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2549 cat<<-EOF

2550 ${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"

2551 href="?blog+$rowid">グループの新規話題作成</a>

2552 / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"

2553 href="?grpaction+$rowid">メンバーを個別選択しての操作</a></p>

2554 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2555 <div class="fold clear">

2556 `cgi_checkbox send yes id="send"`<label

2557 for="send">グループ全員にメッセージ送信</label>

2558 <div>

2559 `cgi_textarea message "" "cols=60"`

2560 `cgi_submit 送信`

2561 `cgi_reset リセット`

2562 </div>

2563 `cgi_hidden grp $rowid`

2564 </div></form>

2565 EOF

2566 fi

2567 # 加入ボタン + 加入者リスト

2568 if [ -n "$ismember" ]; then

2569 ismem='checked' state="(参加中)"

2570 else

2571 nomem='checked' state="(現在非加入)"

2572 fi

2573 # このグループでの加入アドレス

2574 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2575 and key='email';"`

2576 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2577 ## and key='email';"

2578 ##err email=$eml

2579 cat <<EOF

2580 <div class="fold clear">

2581 `cgi_checkbox reg yes id="reg"`<label

2582 for="reg">自身の加入状態を操作する</label>$state

2583 <div>

2584 EOF

2585 cgi_form grp <<EOF

2586 <p>このグループに</p>

2587 <table class="b">

2588 <tr><th>メンバーとして</th><td>

2589 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2590 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2591 <tr><th>参加する場合のメイルアドレス<br>

2592 <small>(メインのアドレスとは違うものにする場合に記入<br>

2593 同じでよい場合は空欄に)</small></th>

2594 <td>`cgi_text email $eml`</td></tr>

2595 </table>

2596 `cgi_hidden grp $rowid`

2597 EOF

2598 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2599 echo "moderated (承認加入の)グループなので実際に参加できるのは

2600 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2601 fi

2602 echo '</div></div>'

2603 echo '<h2>話題一覧</h2>'

2604 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2605 cat<<-EOF

2606 <form class="summary" action="$myname" title="$thelp">

2607 `cgi_hidden owner "$grp"`

2608 `cgi_hidden kwd "@week"`

2609 `cgi_hidden stage searchart`

2610 `cgi_submit "一週間のまとめ"`

2611 </form>

2612 EOF

2613 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2614 colstate="state:稼動状態:frozen=rowclass=凍結"

2615 DT_CHLD=article:blogid \

2616 DT_QOWNER="$qgrp" \

2617 DT_VIEW=replyblog dumptable html blog \

2618 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2619

2620 getgname="(select gname from grp where rowid=$rowid)"

2621 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2622 cm="?commission+$rowid"

2623 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2624 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2625 # team list

2626 hexteams=`hexteams "$grp"`

2627 if [ -n "$hexteams" ]; then

2628 echo "チーム一覧" | html h2

2629 echo '<div class="dumptable"><table class="b">'

2630 sq $db -html -header<<-EOF

2631 SELECT val TEAM,

2632 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2633 MEMBERS

2634 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2635 EOF

2636 echo '</table></div>'

2637 fi

2638 }

2639 grp_getbodyclass() {

2640 # Get css class name for document.

2641 # `moderated' for moderated groups

2642 # `ismember' for groups where user belongs

2643 # $1=GroupName (w/o quote)

2644 # $user=userNameCurrentlyLogin

2645 ## err grp_getbodyclass: 1="$1"

2646 qgrp=`sqlquote "$1"`

2647 query<<-EOF

2648 SELECT coalesce(

2649 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2650 'open')

2651 ||

2652 CASE WHEN '$user'

2653 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2654 THEN ' ismember'

2655 ELSE ''

2656 END;

2657 EOF

2658 }

2659 grpaction() { # $1=group-rowid

2660 err GRP_ACTION:IN

2661 grid=${1:-`getpar grp`}

2662 grp=`getgroupbyid "$grid"`

2663 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2664 if [ -z "$grp" ]; then

2665 echo "無効な指定です。" | html p; return

2666 fi

2667 if ! ismember $user "$grp"; then

2668 echo "加入者のみに許可された操作です。" | html p; return

2669 fi

2670 echo "グループ $grp 個別選択操作" \

2671 | _m4 -D_TITLE_="syscmd(\`cat')" \

2672 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2673 $layout/html.m4.html

2674

2675 isowner=""

2676 isgrpowner "$user" "$grp" && isowner="yes"

2677 usel=`getpar usel`

2678 if [ -n "$usel" ]; then

2679 uids=$(echo `echo $usel`|tr ' ' ',')

2680 ## err grpaction-1: grp=$grp, `echo $sql`

2681 text=`getpar text|tr -d '\r'`

2682

2683 rm=`getpar rm` cfm=`getpar confirm`

2684 ## err rm=$rm cfm=$cfm

2685 if [ x"$rm" = x"yes" ]; then

2686 if [ "$isowner" ]; then

2687 if [ x"$rm$cfm" = x"yesyes" ]; then

2688 # Eliminate

2689 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2690 for tbl in grp_mem grp_mem_s grp_mem_m; do

2691 sql="delete from $tbl $cond;"

2692 # echo "sql=$sql"

2693 query "$sql"

2694 err rmGRPuser "$sql"

2695 done

2696 num=`query "select count(*) from user where rowid in ($uids);"`

2697 #err num=$num

2698 if [ 0$num -gt 0 ]; then

2699 sql="select coalesce(b.val,a.name) from user a left join \

2700 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2701 # err `echo "$sql"`

2702 html pre<<EOF

2703 以下の${num}名のグループ $grp 登録を解除しました。

2704 `query "$sql"`

2705 EOF

2706 fi

2707 else

2708 echo "確認のチェックがないのでやめておきます。" | html p

2709 return

2710 fi

2711 else # not Group Owner

2712 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2713 return

2714 fi

2715 cat<<EOF

2716

2717 EOF

2718 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2719 if [ -z "$text" ]; then # if msg is empty

2720 echo "なにかメッセージを..." | html p

2721 return 0

2722 fi

2723 gecos=`gecos $user`

2724 safegc=`echo "$gecos" | tr -d '<>@'`

2725 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2726 fromad=`myemail4group "$grp"`

2727 ###mail_from="$safegc <$fromad>"

2728 mail_from="$safegc <$user>" # TEST: 2020/5/13

2729 test -n `getpar sender` &&

2730 export SENDER=$user # TEST: 2020/5/15

2731 replyto=$fromad

2732

2733 ## Start parse of attachment files

2734 if [ -n "`getpar email`" ]; then

2735 ar=`getpar supprcpt`

2736 if [ -n "$ar" ]; then

2737 for a in $ar; do

2738 checkdomain "$a" && supprcpt="$supprcpt $a" ||

2739 removercpt="$removercpt $a"

2740 done

2741 fi

2742 subj=`getpar subject`

2743 afiles=""

2744 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2745 do

2746 f=$tmpd/$fn

2747 if [ -s $f ]; then

2748 afiles=$afiles"${afiles:+ }$f"

2749 fi

2750 done

2751 else

2752 preface=$(cat <<-EOF

2753 $url

2754 のグループ「$grp」のメンバーである $gecos さんから、

2755 あなた宛のメッセージです。

2756 ----------------------------------------------------------

2757 EOF

2758 )

2759 fi

2760 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2761 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2762 subj="${subj:-$gecos さんからのメッセージ}"

2763 REPLYTO=$replyto

2764 MAIL_FROM=$mail_from

2765 export REPLYTO SMAIL_TO MAIL_FROM

2766 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2767 for r in $rcpts; do

2768 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2769 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2770 else

2771 # Show sender and rcpts address for guest

2772 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2773 fi

2774 if [ -n "$afiles" ];then

2775 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2776 else

2777 smail "$r" "$subj"

2778 fi <<EOF

2779 ${preface:+$preface$nl}$text

2780 EOF

2781 done

2782 if [ $? = 0 ]; then

2783 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2784 sql="select coalesce(b.val, a.name) from

2785 (select name from user where rowid in ($uids)) a

2786 left join user_s b on a.name=b.name and b.key='gecos';"

2787 html pre<<EOF

2788 `query "$sql"`

2789 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2790 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2791 EOF

2792 err SendDone: `echo $sql`

2793 fi

2794 elif [ x"$rm" = x"commission" ]; then

2795 grp_reg_adm $grid $usel

2796 elif [ x"$rm" = x"addteam" ]; then

2797 team=`getpar team|sed "s/'/''/g"` # for single quotation

2798 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2799 if [ x"$team" != x"$newteam" ]; then

2800 echo "チーム名に使えない文字を除去しました" | html p

2801 team=$newteam

2802 fi

2803 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2804 cat<<-EOF | html p

2805 有効なチーム名を入力してください。

2806 カンマだけ、「なし」という名前は使えません。

2807 EOF

2808 echo "有効なチーム名を入力してください。" | html p

2809 else

2810 grp_add_team $grid "$team" $usel

2811 fi

2812 elif [ x"$rm" = x"rmteam" ]; then

2813 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2814 rmteam=`getpar rmteam|sed "s/'/''/g"`

2815 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2816 gname='$grp' AND user='$user' AND key='team'\

2817 AND val='$rmteam';\"`" ]; then

2818 grp_rm_team $grid "$rmteam" $usel

2819 else

2820 echo "所属していないチームの除去操作はできません。"|html p

2821 fi

2822 else

2823 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2824 fi

2825 fi

2826 fi

2827 # POST count summary

2828 from=`getpar from`; to=`getpar to`

2829 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2830 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2831 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2832 # New entry

2833 sql="WITH mems AS (

2834 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2835 ON gm.user=g.name

2836 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2837 ), target_article AS (

2838 SELECT id FROM article_s

2839 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2840 ), posts AS (

2841 SELECT author, count(author) post

2842 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2843 WHERE blogid IN (SELECT id FROM blog_s

2844 WHERE key='owner'

2845 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2846 AND key='text'

2847 GROUP BY author

2848 ), teams AS (

2849 SELECT user, group_concat(val, ', ') team

2850 FROM grp_mem_m

2851 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2852 AND key='team'

2853 GROUP BY user

2854 ), user_post AS (

2855 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2856 FROM mems m LEFT JOIN posts

2857 ON m.name=posts.author

2858 GROUP by m.rowid

2859 )

2860 SELECT

2861 CASE

2862 WHEN (SELECT user FROM grp_adm

2863 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2864 AND user=up.name) IS NOT NULL

2865 then 'k'

2866 ELSE ''

2867 END || rowid || ',' || gecos NAME,

2868 post POST, team _TEAM_

2869 FROM user_post up LEFT JOIN teams t

2870 ON up.name=t.user

2871 ORDER BY gecos;"

2872 ## err grpaction: "`echo \"$sql\"`"

2873 tf=$tmpd/title.$$

2874 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2875 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2876 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2877 for=\"cmadmin\">管理者委任</label>

2878 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2879 </p></div>"

2880 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2881 title=\"Shortcut: g${nl}Remove from the Group\"

2882 for=\"conf\">GRP登録解除</label>

2883 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2884 <p>この操作による通知は本人に行きません。

2885 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2886 </div>"

2887 # Get team list to which current user belongs into $hexteams

2888 allhexteams=$(hexteams "$grp")

2889 if [ -n "$isowner" ]; then

2890 myhexteams="$allhexteams" # admin can remove all teams' attr

2891 else

2892 myhexteams=$(hexteams "$grp" "$user")

2893 fi

2894 if [ -n "$myhexteams" ]; then

2895 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2896 title=\"Shortcut: s${nl}Strip a team tag from\"

2897 for=\"cmrmteam\">チーム属性除去</label>

2898 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2899 を除去します: `cgi_checkbox teamconfirm yes` 確認

2900 <p>この操作による通知は本人に行きません。

2901 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2902 </div>

2903 "

2904 fi

2905 stf=$tmpd/selteam.$$

2906 cgi_select_h selteam "5445414d" $allhexteams > $stf

2907 b1='<label> <input type="checkbox" name="usel" value="'

2908 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2909 br='<span id="reverse" title="Reverse Selection"></span>'

2910 #b2='"> <span>' b3='</span></label>'

2911 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2912 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2913 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2914 cgi_form grpaction<<EOF \

2915 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2916 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2917 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2918 | _m4 -D_TITLE_="spaste(\`$tf')" \

2919 -D_SUBTITLE_="チェック後操作ボタン" \

2920 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2921 $layout/form+dump.m4.html \

2922 | _m4 -D_TEAM_="spaste(\`$stf')"

2923 <p>下でチェックした人を対象として:</p>

2924 <div class="foldtabs">

2925 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2926 title="Shortcut: a${nl}Add a team tag to"

2927 for="cmteam">同じチーム属性を付与</label>

2928 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2929 `cgi_datalist_h teams $allhexteams`

2930 </div>

2931 ${rmteammsg}

2932 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2933 title="Shortcut: d${nl}DirectMail to"

2934 for="sendmsg" title="Direct Message">DM送信</label>

2935 <div>

2936 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2937 title="Using email format">email書式を使う</label>

2938 <div class="folded">

2939 <table>

2940 <tr><td>From: </td><td>$user</td></tr>

2941 <tr><td>このFrom:で送る</td>

2942 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2943 </td></tr>

2944 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2945 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2946 <tr><td>ファイル添付: </td>

2947 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2948 </table>

2949 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2950 </div>

2951 <div>本文:`cgi_textarea text "" cols=72`

2952 </div>

2953 </div>

2954 ${isowner:+$cmmsg$excmsg}

2955 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2956 </div>

2957 <h4>$grp 参加者一覧</h4>$fromtonote

2958 <table class="td2r thl">

2959 `sq $db -header -html "$sql"`

2960 </table>

2961 `cgi_hidden grp $grid`

2962 `cgi_hidden myuid $myuid id="myuid"`

2963 EOF

2964 }

2965 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2966 # Create TEMPORARY VIEW

2967 query<<EOF

2968 CREATE TEMPORARY VIEW writeusers AS

2969 SELECT DISTINCT author FROM article

2970 WHERE id in (

2971 select id from article where blogid=(select id from blog where rowid=$1)

2972 );

2973 CREATE TEMPORARY VIEW movablegroups AS

2974 SELECT g.rowid growid , g.gname

2975 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2976 ON grp.gname=gm.gname -- そのユーザが属している

2977 AND user='$user') g -- グループに絞る

2978 WHERE (SELECT author FROM writeusers

2979 EXCEPT

2980 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2981 IS NULL;

2982 $2

2983 EOF

2984 }

2985 sql4readableblogs() {

2986 # Create view of blogs that can be readable to $user

2987 # Blog is readable when:

2988 # 1: blog owner is an user

2989 # 2: else, 2.1: owner-group where the $user belongs

2990 # 2.2: else, owner-group is not moderated

2991 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2992 cat<<EOF ## | tee tmp/sql.out

2993 CREATE TEMPORARY VIEW readableblogs AS

2994 SELECT blog.rowid rid, id, author

2995 FROM blog

2996 NATURAL JOIN

2997 (SELECT id,

2998 max(CASE key WHEN 'owner' THEN val END) owner,

2999 max(CASE key WHEN 'mode' THEN val END) mode

3000 FROM blog_s GROUP by id) bs

3001 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

3002 THEN 1 -- blog owner is an user, READABLE

3003 WHEN (SELECT val FROM grp_s

3004 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3005 AND

3006 (SELECT user FROM grp_mem

3007 WHERE gname=bs.owner AND user='$user') IS NULL

3008 THEN 0

3009 WHEN mode IN ('quiz', 'enquete')

3010 THEN 0 -- "quiz" mode blog cannot be searched

3011 ELSE 1

3012 END;

3013 EOF

3014 }

3015 editheading() { # $1=rowid-of-heading

3016 rowid=${1%%[!A-Z0-9a-z_]*}

3017 if [ -z "$rowid" ]; then

3018 echo "話題番号が未指定です。" | html p

3019 return

3020 fi

3021 owner=`getvalbyid blog owner $rowid`

3022 title=`getvalbyid blog title $rowid`

3023 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3024 | _m4 -D_TITLE_="修正" \

3025 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3026 -D_BLOGS_="" -D_DUMPTABLE_="" \

3027 -D_FORM_="syscmd(\`cat')" \

3028 $layout/html.m4.html $layout/form+dump.m4.html

3029 # Move to group

3030 if isuser "$owner"; then

3031 crview4article $rowid

3032 n=`query "SELECT count(*) FROM writeusers;"`

3033 ## err N=$n

3034 if [ $((n)) -gt 0 ]; then

3035 ## err ROWID=$rowid

3036 sql="SELECT growid || ':' || gname FROM movablegroups;"

3037 cat<<-EOF

3038 <div class="fold">

3039 `cgi_checkbox mv send id="mv"`<label

3040 for="mv">この話題をグループ所有に移動する</label>

3041 <div>

3042 <form action="?mvart" method="POST" enctype="multipart/form-data">

3043 移動先グループ:

3044 <select name="mv2grp">

3045 EOF

3046 query ".mode html"

3047 query<<-EOF |

3048 $sql

3049 .mode list

3050 EOF

3051 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3052 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3053 cat<<-EOF

3054 </select>

3055 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3056 そのグループに加入しているものに限られます)</p>

3057 <p>`cgi_checkbox cfm yes`<label>確認

3058 (この操作は元に戻すことができません)</label></p>

3059 `cgi_hidden blogrowid $rowid`

3060 `cgi_submit 移動`

3061 `cgi_reset Reset`

3062 </form>

3063 </div>

3064 </div>

3065 EOF

3066 fi

3067 # end of isuser "$owner"

3068 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3069 [ -n "$hexteams" ];}; then

3070 none="`echo なし|hexize`"

3071 cat<<-EOF

3072 <div class="fold">

3073 `cgi_checkbox mv2team send id="mv2team"`<label

3074 for="mv2team">この話題を以下のチームのものにする</label>

3075 <div><p>現在の所属チーム設定:

3076 `query "SELECT

3077 coalesce((SELECT val FROM blog_s

3078 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3079 AND key='team'),

3080 ':なし');"`</p>

3081 <form action="?mvart" method="POST" enctype="multipart/form-data">

3082 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3083 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3084 `cgi_hidden blogrowid $rowid`<br>

3085 `cgi_submit 移動`

3086 `cgi_reset Reset`

3087 </form></div></div>

3088 EOF

3089 fi

3090 }

3091 mvart() { # move diary to some group or team

3092 # or move blog of group to team which belong to the group

3093 blogrowid=`getpar blogrowid`

3094 cfm=`getpar cfm`

3095 ##### echo move blog:$blogrowid to $mv2grp | html p

3096 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3097 . ./s4-blog.sh

3098 if [ -z "$blogrowid" ]; then

3099 echo "無効な指定です(mvart)。" | html p

3100 return

3101 elif [ x"$cfm" != x"yes" ]; then

3102 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3103 elif { mv2grp=`getpar mv2grp`

3104 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3105 [ -n "$mv2grp" ]; }; then

3106 crview4article $blogrowid

3107 ########## TRANSACTION BEGIN

3108 query "BEGIN;"

3109 n=`query "SELECT count(*) FROM writeusers;"`

3110 ## err Nwriteuser=$n

3111 if [ $((n)) -gt 0 ]; then

3112 query<<-EOF

3113 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3114 WHERE key='owner'

3115 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3116 AND $mv2grp IN (SELECT growid FROM movablegroups);

3117 EOF

3118 fi

3119 query "END;"

3120 ########## TRANSACTION END

3121 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3122 [ -n "$mv2team" ];}; then

3123 # blog owner can move it to ANY team

3124 case "$mv2team" in

3125 'なし')

3126 cat<<-EOF

3127 DELETE FROM blog_s

3128 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3129 AND key='team';

3130 EOF

3131 ;;

3132 "") ;;

3133 *)cat<<-EOF

3134 BEGIN;

3135 REPLACE INTO blog_s(id, key, val)

3136 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3137 'team', '$mv2team');

3138 REPLACE INTO blog_s(id, key, val)

3139 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3140 'notify', 'all'); -- Change notify to all

3141 END;

3142 EOF

3143 esac | query

3144 fi

3145 blog_reply $blogrowid

3146 echo yes | html p

3147 }

3148 editart() { # $1=article-rowid $2=blogrowid

3149 rowid=${1%%[!A-Z0-9a-z_]*}

3150 blogrowid=${2%%[!A-Z0-9a-z_]*}

3151 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3152 echo "表示する記事番号が未指定です。" | html p

3153 return

3154 fi

3155 owner=`getvalbyid blog owner $blogrowid`

3156 title=`getvalbyid blog title $blogrowid`

3157 author=`getvalbyid article author $rowid`

3158 ## err EDITart: owner=$owner, author=$author

3159 if isgrpowner "$user" "$owner"; then

3160 : EDIT OK

3161 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3162 echo "本人か所有者しか編集できません." | html p

3163 return

3164 fi

3165 aid=`query "select id from article where rowid=$rowid;"`

3166 tmpout=$tmpd/editart.$$.out

3167 GF_ACTION="?replyblog+$blogrowid#$aid" \

3168 edittable $formdir/article.def article $rowid \

3169 > $tmpout

3170 rm -f /tmp/editart.out

3171 # Cannot use pipelining to m4 with genform() because of stdin stack

3172 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3173 -D_FORM_="syscmd(cat $tmpout)" \

3174 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3175 -D_BLOGS_= -D_DUMPTABLE_= \

3176 $layout/html.m4.html $layout/form+dump.m4.html

3177 }

3178 send2mem() {

3179 rowid=`getpar grp`

3180 rowid=${rowid%%[!0-9]*} # Cleaning

3181 if [ -z "$rowid" ]; then

3182 echo "グループが未指定です。" | html p

3183 return

3184 fi

3185 message=`getpar message`

3186 if [ -z "$message" ]; then

3187 echo "文章を入れてください。" | html p

3188 return

3189 fi

3190 grp=`getgroupbyid $rowid`

3191 members=`collectemail $grp`

3192 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3193 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3194 mailfrom="`gecos "$user"` <$mailfrom>"

3195 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3196 msg=$(cat<<-EOF

3197 $urlbase?grp+$rowid

3198 グループ $grp に所属する

3199 `gecos $user` さんよりメッセージ:

3200

3201 $message

3202 EOF

3203 )

3204 # smail rcpt subj (file)

3205 for m in $members; do

3206 echo "$msg" |

3207 MAIL_FROM=$mailfrom \

3208 SENDER=$noreply \

3209 REPLYTO=$mailfrom \

3210 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3211 smail "$m" "$sj"

3212 done

3213 cat<<EOF

3214 <p>以下のユーザに送信しました。</p>

3215 <pre>

3216 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3217 </pre>

3218 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3219 EOF

3220 }

3221 joingrpadmit() {

3222 # $1=yes/no $2=session-key

3223 if [ -z "$2" ]; then

3224 echo "bye bye" | html p; return

3225 fi

3226 t_usr=`session=$2 getpar user`

3227 t_grp=`session=$2 getpar group`

3228 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3229 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3230 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3231 echo "無効な加入依頼です。" | html p

3232 echo "有効期限が切れたか、

3233 他の管理者がいる場合は処理済みの可能性があります。" | html p

3234 return

3235 fi

3236 if ! isgrpowner "$user" "$t_grp"; then

3237 echo "グループ管理者のみの機能です。" | html p; return

3238 fi

3239 case $1 in

3240 yes) joingrp "$t_grp" "$t_usr" yes ;;

3241 no) joingrp "$t_grp" "$t_usr" no ;;

3242 *)

3243 echo "無効な指定です($1)。" | html p

3244 return ;;

3245 esac

3246 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3247 rcpts="`getgroupadminmails $t_grp` $user"

3248 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3249 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3250 に

3251 $t_usr

3252 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3253 しました。"

3254 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3255 query "delete from session where id='$2';"

3256 echo "$body" | html p

3257 }

3258

3259 joingrprequest() {

3260 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3261 jss="joingrp-`date +%s`-`genrandom 12`"

3262 addsession $jss +${memoplimitdays}days

3263 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3264 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3265 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3266 $url

3267 $user さんから

3268 グループ $1

3269 に加入依頼がありました。

3270

3271 承認する:

3272 $urlbase?joingrpadmit+yes+$jss

3273

3274 白紙に戻す:

3275 $urlbase?joingrpadmit+no+$jss

3276 EOF

3277 echo "管理者に加入依頼を出しました。

3278 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3279 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3280 }

3281 joingrp() {

3282 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3283 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3284 if isgrpowner "$user" "$1"; then

3285 isowner="yes"

3286 elif [ -n "$5" ]; then

3287 isowner="yes"

3288 else

3289 isowner=""

3290 fi

3291 ## err jg:isgrpowner: isowner="$isowner"

3292 if [ -n "$isowner" ]; then

3293 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3294 elif [ x"$2" != x"$user" ]; then # if user is not login user

3295 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3296 return

3297 elif [ x"$3" = x"no" ]; then

3298 : # Do not pursue those who leave

3299 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3300 : # Member can change own email address for the joining moderated group

3301 else # adding user is $user itself

3302 case `getgroupattr $1 regmode` in

3303 moderated)

3304 joingrprequest "$@" # Request only

3305 return

3306 ;;

3307 *)

3308 ;;

3309 esac

3310 fi

3311 qgname=`sqlquote "$1"`

3312 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3313 cond="where gname=$qgname and user='$2'"

3314 if [ x"$3" = x"yes" ]; then

3315 query "replace into grp_mem values($qgname, '$2');"

3316 # Notify joingrp to admin

3317 action="に加入しました。"

3318 if [ -n "$4" ]; then

3319 if msg=`emaildomaincheck "$4"`; then

3320 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3321 'string', '$4', NULL);"

3322 else

3323 echo $msg

3324 fi

3325 else

3326 query "delete from grp_mem_s $cond and key='email';"

3327 fi

3328 if [ -n "$5" ]; then # as ADMIN

3329 # Coming here means newly created group

3330 sql="select case\

3331 when (select count(*) from grp_mem where gname=$qgname)=1\

3332 then (select user from grp_mem\

3333 where gname=$qgname and user='$user')\

3334 else '' end; "

3335 err NewGrpChk: $sql

3336 if [ -n "`query \"$sql\"`" ]; then

3337 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3338 query "replace into grp_adm values($qgname, '$user');"

3339 fi

3340 fi

3341 else

3342 query "begin;

3343 delete from grp_mem $cond;

3344 delete from grp_mem_s $cond;

3345 delete from grp_mem_m $cond;

3346 delete from grp_adm $cond;

3347 delete from grp_adm_s $cond;

3348 delete from grp_adm_m $cond;

3349 end;"

3350 action="から脱退しました。"

3351 fi

3352 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3353 $url?grp+$grid

3354 $user (`gecos $user`)さんが

3355 グループ $1

3356 $action

3357 EOF

3358 }

3359 grp_add_team() (

3360 # $1=grp-rowid $2=team $3...=user-rowid(s)

3361 grp=`getgroupbyid $1`

3362 team=$2; shift; shift

3363 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3364 { echo "BEGIN;"

3365 for user; do

3366 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3367 '$grp',\

3368 (SELECT name FROM user WHERE rowid=$user),\

3369 'team', 'string', '$team');"

3370 done

3371 echo "END;"

3372 } | query

3373 )

3374 grp_rm_team() (

3375 # $1=grp-rowid $2=team $3...=user-rowid(s)

3376 grid=$1

3377 qgrp=$(sqlquote "`getgroupbyid $grid`")

3378 team=$2; shift; shift

3379 [ -z "$grid" -o -z "$team" ] && return

3380 { echo "BEGIN;"

3381 for user; do

3382 echo "DELETE FROM grp_mem_m\

3383 WHERE gname=$qgrp \

3384 AND user=(SELECT name FROM user WHERE rowid=$user)\

3385 AND key='team' AND val='$team';"

3386 done

3387 cat<<-EOF

3388 DELETE FROM blog_s

3389 WHERE rowid=(

3390 SELECT rowid

3391 FROM blog_s a

3392 WHERE key='team'

3393 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3394 AND NOT EXISTS (SELECT * FROM grp_mem_m

3395 WHERE key='team' AND val=a.val -- a.val=team

3396 AND gname = (SELECT val FROM blog_s b

3397 WHERE a.id=b.id AND key='owner')

3398 ));

3399 EOF

3400

3401 echo "END;"

3402 } | query

3403 )

3404 grp_reg_adm() {

3405 # $1=grp-rowid $2...=user-rowid

3406 grid=$1

3407 grp=`getgroupbyid "$1"`

3408 if [ -z "$grp" ]; then

3409 echo "無効なグループIDです" | html p; return

3410 fi

3411 if ! isgrpowner "$user" "$grp"; then

3412 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3413 fi

3414 shift

3415 for urid; do

3416 newadm=`query "select name from user where rowid=$urid;"`

3417 if [ -z "$newadm" ]; then

3418 echo "指定ユーザIDがおかしいようです。" | html p; return

3419 fi

3420 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3421 err ismember $newadm $grp

3422 if ismember $newadm "$grp"; then

3423 # OK, go ahead

3424 getgname="(select gname from grp where rowid=$grid)"

3425 query "replace into grp_adm values($getgname, '$newadm');"

3426 # confirm insertion

3427 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3428 if [ -n "`query \"$sql;\"`" ]; then

3429 echo "追加完了: $newadm" | html p

3430 else

3431 echo "追加失敗($1 $urid)" | html p

3432 fi

3433 fi

3434 showgroup $grid

3435 done

3436 }

3437 dt_rowhack() {

3438 # From: <TR>

3439 # ....

3440 # <TD>rowclass=foo</TD>

3441 # </TR>

3442 # To: <TR class="foo">....<TD>foo</TD></TR>

3443 sed -e '

3444 /^<TR>/ {

3445 :loop

3446 s/\n//

3447 N

3448 /<\/TR>/ {

3449 s/\n//

3450 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3451 n

3452 }

3453 $q

3454 b loop

3455 }'

3456 }

3457 dumptable() {

3458 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3459 # textのフィールドだけ全てダンプにしたほうがいいか

3460 # $DT_VIEW sets link

3461 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3462 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3463 VIEW=${DT_VIEW-replyblog}

3464 if [ -n "$VIEW" ]; then

3465 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3466 fi

3467 sqlfile=$tmpd/dump.sql

3468 : > $sqlfile # ensure to be empty

3469 printf '.mode html\n.header 1\n' > $sqlfile

3470 # $DT_CHLD=ChildTable:BindColumn

3471 if [ -n "$DT_CHLD" ]; then

3472 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3473 cat<<-EOF >> $sqlfile

3474 -- presql

3475 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3476 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3477 EOF

3478 # Speed up counting of new articles

3479 cat<<-EOF >> $sqlfile

3480 -- presql2

3481 DROP TABLE IF EXISTS _counts;

3482 CREATE TEMPORARY TABLE _counts AS

3483 SELECT $_i, count($_i) cnt

3484 FROM $_t GROUP BY $_i;

3485 /* Prepare NEW count table */

3486 CREATE TEMPORARY TABLE _target AS

3487 SELECT b.rowid trowid, b.id

3488 FROM "$2" b JOIN "$2_s" s

3489 ON b.id=s.id AND s.key='owner'

3490 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3491

3492 DROP TABLE IF EXISTS _children;

3493 CREATE TEMPORARY TABLE _children AS

3494 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3495 FROM (SELECT t.trowid, t.id $_i, a.id

3496 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3497 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3498

3499 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3500 DROP TABLE IF EXISTS _news;

3501 DROP VIEW IF EXISTS _news;

3502

3503 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3504 -- INSERT INTO _news

3505 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3506 CREATE TEMPORARY VIEW _news AS

3507 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3508 FROM (SELECT DISTINCT id FROM _target)

3509 a LEFT JOIN

3510 (SELECT $_i, count(ctime) newcnt

3511 FROM _children x

3512 WHERE ctime > coalesce((SELECT time from myacclog

3513 WHERE tblrowid=x.trowid),

3514 '1970-01-01')

3515 GROUP BY $_i) b

3516 ON a.id=b.$_i;

3517 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3518 EOF

3519 # REMOVE next line until 2019/5/1

3520 cntall="(select count($_i) from $_t where $_i=a.id)"

3521 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3522 # REMOVE next assignment until 2019/5/1

3523 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3524 and id in (select id from $_t where $_i=a.id) \

3525 and val > coalesce((select time from myacclog where \

3526 tblrowid=a.rowid),\

3527 '1970-01-01'))"

3528 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3529 cnt="$cntnew as '新着', $cntall as '総数',"

3530 dt_class=" td2r td3r dumpblogs"

3531 fi

3532 # Construct join expression

3533 eav="" scols=""

3534 pk=`gettblpkey $2`

3535 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3536 substr=${substr:-%s}

3537 for col in ${3:-`gettbl_s_cols $2`}; do

3538 valvar=val

3539 case $col in

3540 gecos) scols="$scols${scols:+, }${col#}"

3541 continue ;; # built-in column name

3542 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3543 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3544 case "$as" in

3545 *:*=*) cnd=${as#*:}

3546 h=${cnd%%=*} v=${cnd#*=}

3547 h=`sqlquotestr "$h"`

3548 v=`sqlquotestr "$v"`

3549 valvar="CASE val WHEN $h THEN $v END"

3550 as=${as%%:*} ;;

3551 esac

3552 ;;

3553 *) as=${col} ;;

3554 esac

3555 ss=`printf "$substr" "$valvar"`

3556 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3557 scols="$scols${scols:+, }b.$as"

3558 done

3559 #case author when '$user' then a.rowid else '---' end as ID,

3560 if [ -n "$DT_SQL" ]; then

3561 echo "$DT_SQL"

3562 else

3563 cat<<-EOF

3564 SELECT a.rowid as LINK, $cnt $scols

3565 FROM $2 a LEFT JOIN

3566 (SELECT $pk,$eav,

3567 max(CASE key

3568 WHEN 'owner'

3569 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3570 as gecos

3571 FROM ${2}_s c GROUP BY $pk)

3572 b ON a.$pk=b.$pk $4;

3573 EOF

3574 fi >> $sqlfile

3575 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3576 sqlog<<-EOF

3577 *** SQL-file: $sqlfile ***

3578 `cat $sqlfile`

3579 EOF

3580 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3581 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3582 cat $sqlfile >> tmp/sql

3583 # query ".read $sqlfile" > $tmpd/foo

3584 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3585 cp $tmpd/foo tmp/

3586 err "----- `gdate +%FT%T.%3N` ------------555555"

3587 ## $ddd LINE exists at the end of this function

3588 fi

3589 printf '.mode list\n.header 0\n' >> $sqlfile

3590 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3591 <div>

3592 <div class="dumptable">

3593 <table class="b$dt_class">

3594 `query ".read $sqlfile"`

3595 </table>

3596 </div>

3597 </div>

3598 EOF

3599 ### `query ".read $sqlfile"`

3600 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3601

3602 ## REMOVE THIS!

3603 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3604 }

3605

3606 par2table() (

3607 # copy current parameters of par into destination table

3608 # $1=definition-file

3609 # Using $user and $session

3610 # Return value:

3611 # 0: Stored successfully

3612 # 1: Insufficient fillings

3613 # 2: No permission to modify the record

3614 # 3: Invalid rowid

3615 # 4: SUCCESS to delete

3616 # 5: Stop deletion for lack of confirm check

3617 # 6: Password length too short

3618 # 7: Password mismatch

3619 # 8: Old password incorrect

3620 rowid=`getpar rowid`

3621 if [ ! -e $1 ]; then

3622 echo "テーブル定義ファイルが見付かりません" | html p

3623 exit 1

3624 fi

3625 tbl=${1%.def}

3626 tbl=${tbl##*/}

3627 if [ -n "$rowid" ]; then # Modify existing entry

3628 if [ x"$tbl" = x"user" ]; then

3629 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3630 elif [ x"$tbl" = x"grp" ]; then

3631 sql="select gname from $tbl where rowid=$rowid;"

3632 ##err p2t:grp:q $sql

3633 isgrpowner "$user" "`query $sql`" && rowowner=$user

3634 elif [ x"$tbl" = x"blog" ]; then

3635 # Check if owner in blog_s

3636 blogowner=`getvalbyid blog owner "$rowid"`

3637 if isgrpowner "$user" "$blogowner"; then

3638 rowowner=$user

3639 else

3640 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3641 fi

3642 else

3643 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3644 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3645 fi

3646 ### err rowowner=$rowowner

3647 if [ x"$user" != x"$rowowner" ]; then

3648 echo "他人のレコードはいじれないの" | html p

3649 return 2

3650 elif [ -z "$rowowner" ]; then

3651 echo "指定したレコードはないみたい" | html p

3652 return 3

3653 fi

3654 rm=`getpar rm` cfm=`getpar confirm`

3655 # Editing existent entry

3656 if [ x"$rm" = x"yes" ]; then

3657 if [ x"$rm$cfm" = x"yesyes" ]; then

3658 query "delete from $tbl where rowid=$rowid;"

3659 return 4

3660 else

3661 echo "消去確認のチェックがないので消さなかったの..." | html p

3662 return 5

3663 fi

3664 fi

3665 fi

3666

3667 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3668 if [ -n "$rowid" ]; then

3669 # Update of existing record

3670 for col in `gettblcols $tbl`; do

3671 val=`getparquote $col`

3672 [ -z "$val" ] && continue

3673 ## err query "update $tbl set $col=$val where rowid=$rowid"

3674 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3675 sql="update $tbl set $col=$val where rowid=$rowid;"

3676 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3677 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3678 ## User name cannot be changed with interface provided with this

3679 ## script. But we offer the trigger to change owner user

3680 ## of blog_s table.

3681 #err "select quote($col) from $tbl where rowid=$rowid;"

3682 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3683 cat<<-EOF | query

3684 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3685 -- should use EXCLUSIVE transaction outside of this.

3686 SAVEPOINT par2table;

3687 $sql

3688 update blog_s set val=$val

3689 where key='owner' and val=$old;

3690 RELEASE SAVEPOINT par2table;

3691 EOF

3692 ## XX: DIRTY Hack Ends here

3693 ## We should keep blog's owner as a single column which has

3694 ## foreign key constraint with primary key of grp/user.

3695 else

3696 query "$sql"

3697 fi

3698 done

3699 # Then, set up $pval for further insertion of tbl_s and tbl_m

3700 for col in `gettblpkey $tbl`; do

3701 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3702 pval="$pval${pval:+, }\"$val\""

3703 done

3704 else

3705 # New entry

3706 # Generate values() for primary keys

3707 for col in `gettblpkey $tbl`; do

3708 # Genuine primary keys for _m and _s

3709 val=`getvalquote $tbl $col`

3710 [ -z "$val" ] && continue

3711 pval="$pval${pval:+, }$val"

3712 done

3713 ##err pval=$pval

3714 for col in `gettblfkey $tbl`; do

3715 # args for values() to insertion into master table

3716 val=`getvalquote $tbl $col`

3717 [ -z "$val" ] && continue

3718 formaster=$formaster"${formaster:+, }$val"

3719 done

3720 formaster="$pval${formaster:+, }$formaster"

3721 ## err formaster=$formaster

3722 if [ -z "$formaster" ]; then

3723 echo "項目を全て埋めてください" | html pre

3724 return 1

3725 fi

3726 ## err "replace into $tbl values($formaster);"

3727 query "replace into $tbl values($formaster);"

3728 ## Insertion to master table, done

3729 fi

3730

3731 transaction=$tmpd/sqlfile.sql; touch $transaction

3732 for kt in s m; do

3733 tb2=${tbl}_$kt

3734 for col in `gettbl_${kt}_cols $tbl`; do

3735 ptype=`getpartype $col "limit 1"`

3736

3737 # First, check update of existing entries in _m

3738 if [ $kt = m ]; then

3739 # sessID|address.1.22|string|Somewhere-x.y.z

3740 sql=""

3741 ##err dots from query "select var from par where var like '$col.%';"

3742 for v in `query "select var from par where var like '$col.%';"`; do

3743 # v=address.1.22

3744 st_rowid=${v##*.}

3745 origcol=${v%%.*} # original column derived from

3746 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3747 ##case `getpartype $v` in

3748 ## err CASE `gettbl_coltype $tbl/$origcol` in

3749 ## err edit flag = `getpar action.$v`

3750 case `getpar action.$v` in

3751 rm)

3752 if [ x`getpar confirm.$v` = x"yes" ]; then

3753 newsql="delete from $tb2"

3754 else

3755 echo "削除確認未チェック" | html p

3756 fi ;;

3757 edit)

3758 case `gettbl_coltype $tbl/$origcol` in

3759 image|document|binary)

3760 file=$tmpd/`getparfilename $v`

3761 ## err type=file=$file

3762 [ -z "$file" ] && continue

3763 bn=`sqlquotestr "${file##*/}"`

3764 bin="X'"$(hexize "$file")"'"

3765 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3766 type=\"file:$ct\"

3767 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3768 cachedir=`getcachedir "$tbl/$rowid"`

3769 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3770 rm -rf $cachedir

3771 ;;

3772 *)

3773 newsql="update $tb2 set val=(select val from par where var \

3774 like '$col.%.$st_rowid')"

3775 ;;

3776 esac

3777 ;;

3778 *) # maybe "keep", do not modify value

3779 continue

3780 ;;

3781 esac

3782 # err newsql=$newsql

3783 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3784 done

3785

3786 if [ x"$bin" = x"NULL" ]; then

3787 ## err repl:normal sql=`echo $sql`

3788 if [ -n "$transaction" ]; then

3789 cat<<-EOF >> $transaction

3790 $sql

3791 DELETE FROM $tb2 WHERE type='string' AND val='';

3792 EOF

3793 else

3794 query "$sql

3795 delete from $tb2 where type='string' and val='';"

3796 ## err repl:normal done

3797 fi

3798 else

3799 # Binary update line is TOO LONG to pipelining

3800 sqlfile="$tmpd/sqlf.$$"

3801 if [ -n "$transaction" ]; then

3802 echo "$sql" >> $transaction

3803 else

3804 echo "$sql" > $sqlfile

3805 query ".read $sqlfile"

3806 fi

3807 fi

3808 # Rest of kt==m: set multiple mode

3809 nr=`getparcount $col`

3810 else

3811 nr=1 # for kt==s, number of records is 1

3812 fi

3813

3814 i=0

3815 while [ $i -lt $nr ]; do

3816 limit="limit 1 offset $i"

3817 i=$((i+1)) # increase beforehand against continue

3818 val=`getvalquote $tbl $col "$limit"`

3819 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3820 ## err $col=$val

3821 bin=NULL

3822 ## err partype$col=`getpartype $col "$limit"`

3823 case $ptype in

3824 file) file=$tmpd/`getparfilename $col "$limit"`

3825 ## err parfile-$col=$file

3826 [ -z "$file" ] && continue

3827 bin="X'"$(hexize "$file")"'"

3828 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3829 type=\"file:$ct\" ;;

3830 "*"*) continue ;; # foreign table

3831 *) type=\"string\" ;;

3832 esac

3833 case `gettbl_coltype $tbl/$col` in

3834 password) # special care for password

3835 # name={password,pswd1,pswd2}

3836 p1=`getpar pswd1 "$limit"`

3837 if [ -z "$p1" ]; then

3838 continue # SKIP password setting, if p1 is empty

3839 else

3840 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3841 ## err pswd=$pswd

3842 if pwcheck "$pswd"; then

3843 if [ x"$p1" = x"$p2" ]; then

3844 case "$p1" in

3845 ??????????*) ;;

3846 *) echo "パスワードは10字以上にしてください。" | html p

3847 return 6;;

3848 esac

3849 val="\"`echo $p1|mypwhash`\""

3850 else

3851 echo "2つの新パスワード不一致" | html p

3852 return 7

3853 fi

3854 else

3855 echo "旧パスワード違います" | html p

3856 return 8

3857 fi

3858 fi

3859 ;;

3860 esac

3861 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3862 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3863 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3864 if [ x"$bin" = x"NULL" ]; then

3865 ## err Normal-query: `echo $sql`

3866 if [ -n "$transaction" ]; then

3867 echo "$sql" >> $transaction

3868 else

3869 query "$sql"

3870 fi

3871 else

3872 sqlfile="$tmpd/query.$$"

3873 ## err sqlfile=`ls -lF $sqlfile`

3874 if [ -n "$transaction" ]; then

3875 echo "$sql" >> $transaction

3876 else

3877 echo "$sql" > $sqlfile

3878 query ".read $sqlfile"

3879 fi

3880 fi

3881 ## err p2t done

3882 done

3883 done

3884 done

3885 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3886 -- We cannot use transaction here, because groupupdate may use it.

3887 SAVEPOINT pa2table_insert;

3888 .read $transaction

3889 RELEASE SAVEPOINT pa2table_insert;

3890 EOF

3891 return $?

3892 ##err donee

3893 )

3894 genform() {

3895 # $1 = form definition file

3896 # $2, $3 (optional)= table name and ROWID

3897 # If $GF_VIEWONLY set and nonNull, output values without form

3898 # If $GF_ARGS set, use it as content-strings in the form

3899 # If $GF_OWNER set, use it as value of name="owner"

3900 # If $GF_STAGE set, use it as value of name="stage"

3901 forms="" hiddens="" rowid=$3

3902 if [ ! -e "$1" ]; then

3903 echo "そのようなデータベースはないようです($2)。" | html p

3904 return

3905 elif [ -n "$2" ]; then

3906 rec=`query "select * from $2 where rowid='$rowid';"`

3907 if [ -z "$rec" ]; then

3908 pk=`gettblpkey $2`

3909 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3910 rec=`query "select rowid from $2 where $pk='$rowid';"`

3911 rowid=$rec

3912 rec=$3

3913 fi

3914 if [ -z "$rec" ]; then

3915 echo "そんなレコードはないみたいね..." | html p

3916 return

3917 fi

3918 fi

3919 if [ -z "$GF_VIEWONLY" ]; then

3920 rm='<input id="rm" name="rm" type="checkbox"

3921 value="yes"><label for="rm">このエントリの削除</label>

3922 <span>ほんとうに消しますよ(確認)!

3923 <input name="confirm" type=checkbox value="yes">はい</span>'

3924 fi

3925 # Image Cache dir

3926 ## err genform: getcache=$2/$rowid

3927 td=`getcachedir "$2/$rowid"`

3928 while IFS=: read prompt name keytype type args; do

3929 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3930 sp="${args:+ }"

3931 form="" val=""

3932 if [ -n "$rowid" ]; then

3933 # err genform2a: Seeking for "$2.$name, type=$type"

3934 rawval=`getvalbyid $2 $name $rowid $td`

3935 val=`echo "$rawval"|htmlescape`

3936 ## err genform3a: getvalbyid $2 $name $rowid $td

3937 ## err genform3b: val="[$val]" type="$type"

3938 fi

3939 if [ -n "$GF_VIEWONLY" ]; then

3940 is_hidden "$2" "$name" && continue

3941 fi

3942 case "$type" in

3943 text*)

3944 cgiform=cgi_multi_$type

3945 if [ -s $td/$name.count -a -n "$val" ]; then

3946 form=`$cgiform $name $td`

3947 val=$(echo "$val"|

3948 while read fn; do

3949 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3950 </td></tr>$nl"

3951 done)

3952 val="<table>$nl$val$nl</table>"

3953 else

3954 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3955 form=`cgi_$type $name "$rawval" "$args"`

3956 fi

3957 ;;

3958 [Rr][Aa][Dd][Ii][Oo])

3959 fh="<label><input type=\"radio\" name=\"$name\""

3960 form="`echo $args|sed -e \

3961 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3962 ;;

3963 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3964 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3965 ;;

3966 [Ss][Ee][Ll][Ee][Cc][Tt])

3967 fh="<select name=\"$name\">$nl"

3968 form=$(for l in $args; do

3969 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3970 done)

3971 if [ -n "$val" ]; then

3972 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3973 fi

3974 form="$fh$form</select>"

3975 ;;

3976 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3977 if [ -s $td/$name.count ]; then

3978 form=`cgi_multi_file $name $td "$args"`

3979 if [ -n "$val" ]; then

3980 hrfb="$myname?showattc+$2_m"

3981 val=$(echo "$rawval" \

3982 | while read fn; do

3983 data=`percenthex "$td/$fn"`

3984 #ct=`cat $td/$fn.content-type`

3985 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3986 ri=`cat "$td/$fn.rowid"`

3987 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3988 #imgsrc="<img src=\"data:$ct,$data\">"

3989 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3990 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3991 done)

3992 fi

3993 else

3994 form="<input type=\"file\" name=\"$name\" $args>"

3995 if [ -n "$val" ]; then

3996 imgs=$(echo "$rawval"\

3997 |while read fn;do

3998 data=`percenthex "$td/$fn"`

3999 echo "<img src=\"data:image/png,$data\">$fn<br>"

4000 done)

4001 form=$form"<br>$imgs"

4002 val=$imgs # 2015-06-15

4003 else

4004 form="<input type=\"file\" name=\"$name\" $args>"

4005 fi

4006 fi

4007 ;;

4008 [Hh][Ii][Dd][Dd][Ee][Nn])

4009 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4010 args="value=\"$GF_STAGE\""

4011 fi

4012 form="<input type=\"hidden\" name=\"$name\" $args>"

4013 prompt='' # Remove prompt

4014 ;;

4015 [Aa][Uu][Tt][Hh][Oo][Rr])

4016 [ -n "$GF_VIEWONLY" ] && continue

4017 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4018 prompt="" ;;

4019 [Oo][Ww][Nn][Ee][Rr])

4020 [ -n "$GF_VIEWONLY" ] && continue

4021 val=${GF_OWNER:-$val}

4022 val=${val:-$user}

4023 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4024 prompt="" ;;

4025 [Uu][Ss][Ee][Rr])

4026 # XXX: is null $user ok?

4027 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4028 [ -n "$GF_VIEWONLY" ] && continue

4029 form="$user"

4030 ;;

4031 [Pp]assword)

4032 [ -n "$GF_VIEWONLY" ] && continue

4033 form="`cgi_passwd`"

4034 val=""

4035 ;;

4036 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4037 [ -n "$GF_VIEWONLY" ] && continue

4038 if [ -z "$rowid" ]; then

4039 val=`genserial`

4040 fi

4041 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4042 prompt="" ;;

4043 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4044 prompt=""

4045 ;;

4046 parent|path|blog*)

4047 prompt=""

4048 ;;

4049 "*"*)

4050 tail=$tail"``"

4051 continue ;;

4052 esac

4053 if [ -n "$prompt" ]; then

4054 if [ -n "${GF_VIEWONLY}" ]; then

4055 form=$val

4056 else

4057 :

4058 fi

4059 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4060 else

4061 hiddens=$hiddens$nl"$form"

4062 fi

4063 done < $1

4064 # enctype="multipart/form-data"

4065 cat<<EOF

4066 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4067 ${rowid:+$rm}

4068 <table class="b $2">

4069 $forms

4070 </table>$hiddens

4071 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4072 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4073 EOF

4074 if [ -z $GF_VIEWONLY ]; then

4075 cat<<EOF

4076 <input type="submit" name="sub" value="OK">

4077 <input type="reset" name="res" value="Reset">

4078 EOF

4079 fi

4080 cat<<EOF

4081 $GF_ARGS</form>

4082 $tail

4083 EOF

4084 }

4085 edittable() {

4086 # $1=form-def $2=table $3 rowid

4087 genform "$@"

4088 }

4089 viewtable() {

4090 GF_VIEWONLY=1 genform "$@"

4091 }

4092 showattc() {

4093 # $1=table_m $2=rowid &optional $3=RawFlag

4094 ## err \$1=$1 \$2=$2 \$3=$3

4095 if ! isfilereadable $user $1 $2; then

4096 contenttype; echo

4097 echo "このファイルは管理者のみしか見られません" | html p

4098 putfooter; exit

4099 fi

4100 idir=`umask 002; mktempd` || exit 1

4101 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4102 bin=$idir/$myname-$$.bin

4103 sql="select quote(bin) from $1 where rowid='$2';"

4104 ## err showattc: sql: $sql

4105 sq $db "$sql" | unhexize > $bin

4106 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4107 type=${tv%//*} fn=${tv#*//}

4108 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4109 ct=${type#file:}

4110 case $ct in # all text/* changed to text/plain

4111 text/*)

4112 charset=`nkf -g $bin|cut -d' ' -f1`

4113 case $charset in

4114 ASCII*) charset="" ;;

4115 esac

4116 if [ -z "$3" ]; then

4117 ct="text/html${charset:+; charset=$charset}"

4118 link="?showattc+$1+$2+raw"

4119 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4120 | sed 's,^,<span></span>,' \

4121 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4122 -D_LINK_="$link" \

4123 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4124 exit $?

4125 fi

4126 ct="text/plain${charset:+; charset=$charset}"

4127 ;;

4128 esac

4129 contenttype "$ct"

4130 echo "Content-Disposition: filename=\"$fn\""

4131 echo "Content-Length: " `cat $bin | wc -c`; echo

4132 #echo "Content-Type: " ${type#file:}; echo

4133 cat $bin

4134 }

4135 #

4136 # Some default stupid handler on CGI values

4137 #

4138 default_storedb() {

4139 # ARG: $1=table-def-file

4140 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4141 tbl=`basename $1`

4142 tbl=${tbl%.def}

4143 cols="`grep :text $1|cut -d: -f2`"

4144 col=`echo "$cols"|head -1`

4145 vcol=`getpar $col`

4146 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4147 if [ -n "$vcol" ]; then

4148 par2table $1

4149 else

4150 return 2 # No insertion occurred

4151 fi

4152 }

4153

4154 default_view() { # $1=def-file

4155 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4156 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4157 default_storedb "$@"

4158 query "select rowid from $tbl order by rowid desc;" \

4159 | while read rowid; do

4160 viewtable $1 $tbl $rowid

4161 done | _m4 -D_TITLE_="$tbl" \

4162 -D_FORM_="`genform $1`" \

4163 -D_DUMPTABLE_="syscmd(cat)" \

4164 $layout/html.m4.html $layout/form+dump.m4.html

4165 }

4166 default_viewtext() { # $1=def-file

4167 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4168 default_storedb "$@"

4169 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4170 | _m4 -D_TITLE_="$tbl" \

4171 -D_FORM_="`genform $1`" \

4172 -D_DUMPTABLE_="syscmd(cat)" \

4173 $layout/html.m4.html $layout/form+dump.m4.html

4174 }

4175 default_smail() {

4176 default_storedb "$@"

4177 if [ $? -eq 2 ]; then

4178 _m4 -D_TITLE_="入力" \

4179 -D_FORM_="`genform $1`" \

4180 -D_DUMPTABLE_="" \

4181 $layout/html.m4.html $layout/form+dump.m4.html

4182 return

4183 fi

4184 cond=""

4185 for pk in `gettblpkey $tbl`; do

4186 pv=$(sqlquote "$(getpar $pk)")

4187 cond="$cond${cond:+ and }$pk=$pv"

4188 done

4189 sql="select rowid from $tbl where $cond;"

4190 rowid=`query "$sql"`

4191 ## err smail1 - "$sql" "-> rowid=$rowid"

4192

4193 while IFS=: read prompt name keytype type args; do # Read from $1

4194 val=`getpar $name`

4195 if [ -n "$val" ]; then

4196 text="$text

4197 $prompt

4198 $name=$val

4199 ---------------------------------------------------------"

4200 fi

4201 case "$type" in

4202 image|document|file)

4203 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4204 fns=$(echo "$fn"|while read fn; do

4205 err mv $tmpd/$fn.orig $tmpd/$fn

4206 mv $tmpd/$fn.orig $tmpd/$fn

4207 rm $tmpd/$fn.rowid # Remove cache flag

4208 ## err "`ls $tmpd/$fn`"

4209 echo $fn

4210 done)

4211 files="$files $fns"

4212 ;;

4213 esac

4214 done < $1

4215 ## err FILES=$files "`ls -lF $tmpd`"

4216 subj="from ${REMOTE_ADDR}"

4217 (echo "$url"

4218 echo "への書き込みがありました。"

4219 echo "------"

4220 echo "$text"

4221 ) | (cd $tmpd &&

4222 err LS="`ls -lF`" &&

4223 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4224 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4225 echo "以下の内容で送信しました。" | html p

4226 viewtable $1 $tbl \

4227 `query "select rowid from $tbl order by rowid desc limit 1;"`

4228 echo "戻る" | html a "href=\"?\""

4229 }