s4: 9bac1300f259 s4-funcs.sh

s4

view s4-funcs.sh @ 708:9bac1300f259

Remove superfluous SQL

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 28 May 2020 19:26:07 +0900
parents	6f07cfc1ba86
children	025ef5578ae0

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 done

2740 fi

2741 subj=`getpar subject`

2742 afiles=""

2743 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2744 do

2745 f=$tmpd/$fn

2746 if [ -s $f ]; then

2747 afiles=$afiles"${afiles:+ }$f"

2748 fi

2749 done

2750 else

2751 preface=$(cat <<-EOF

2752 $url

2753 のグループ「$grp」のメンバーである $gecos さんから、

2754 あなた宛のメッセージです。

2755 ----------------------------------------------------------

2756 EOF

2757 )

2758 fi

2759 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2760 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`

2761 subj="${subj:-$gecos さんからのメッセージ}"

2762 REPLYTO=$replyto

2763 MAIL_FROM=$mail_from

2764 export REPLYTO SMAIL_TO MAIL_FROM

2765 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2766 for r in $rcpts; do

2767 if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then

2768 SMAIL_TO=$rcpts # Show all rcpts to sender oneself

2769 else

2770 # Show sender and rcpts address for guest

2771 SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`

2772 fi

2773 if [ -n "$afiles" ];then

2774 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2775 else

2776 smail "$r" "$subj"

2777 fi <<EOF

2778 ${preface:+$preface$nl}$text

2779 EOF

2780 done

2781 if [ $? = 0 ]; then

2782 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2783 sql="select coalesce(b.val, a.name) from

2784 (select name from user where rowid in ($uids)) a

2785 left join user_s b on a.name=b.name and b.key='gecos';"

2786 html pre<<EOF

2787 `query "$sql"`

2788 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2789 EOF

2790 err SendDone: `echo $sql`

2791 fi

2792 elif [ x"$rm" = x"commission" ]; then

2793 grp_reg_adm $grid $usel

2794 elif [ x"$rm" = x"addteam" ]; then

2795 team=`getpar team|sed "s/'/''/g"` # for single quotation

2796 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2797 if [ x"$team" != x"$newteam" ]; then

2798 echo "チーム名に使えない文字を除去しました" | html p

2799 team=$newteam

2800 fi

2801 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2802 cat<<-EOF | html p

2803 有効なチーム名を入力してください。

2804 カンマだけ、「なし」という名前は使えません。

2805 EOF

2806 echo "有効なチーム名を入力してください。" | html p

2807 else

2808 grp_add_team $grid "$team" $usel

2809 fi

2810 elif [ x"$rm" = x"rmteam" ]; then

2811 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2812 rmteam=`getpar rmteam|sed "s/'/''/g"`

2813 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2814 gname='$grp' AND user='$user' AND key='team'\

2815 AND val='$rmteam';\"`" ]; then

2816 grp_rm_team $grid "$rmteam" $usel

2817 else

2818 echo "所属していないチームの除去操作はできません。"|html p

2819 fi

2820 else

2821 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2822 fi

2823 fi

2824 fi

2825 # POST count summary

2826 from=`getpar from`; to=`getpar to`

2827 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2828 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2829 fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p>"

2830 # New entry

2831 sql="WITH mems AS (

2832 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2833 ON gm.user=g.name

2834 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2835 ), target_article AS (

2836 SELECT id FROM article_s

2837 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2838 ), posts AS (

2839 SELECT author, count(author) post

2840 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2841 WHERE blogid IN (SELECT id FROM blog_s

2842 WHERE key='owner'

2843 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2844 AND key='text'

2845 GROUP BY author

2846 ), teams AS (

2847 SELECT user, group_concat(val, ', ') team

2848 FROM grp_mem_m

2849 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2850 AND key='team'

2851 GROUP BY user

2852 ), user_post AS (

2853 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2854 FROM mems m LEFT JOIN posts

2855 ON m.name=posts.author

2856 GROUP by m.rowid

2857 )

2858 SELECT

2859 CASE

2860 WHEN (SELECT user FROM grp_adm

2861 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2862 AND user=up.name) IS NOT NULL

2863 then 'k'

2864 ELSE ''

2865 END || rowid || ',' || gecos NAME,

2866 post POST, team _TEAM_

2867 FROM user_post up LEFT JOIN teams t

2868 ON up.name=t.user

2869 ORDER BY gecos;"

2870 ## err grpaction: "`echo \"$sql\"`"

2871 tf=$tmpd/title.$$

2872 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2873 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"

2874 title=\"Shortcut: f${nl}Add to Administrator of the Group\"

2875 for=\"cmadmin\">管理者委任</label>

2876 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2877 </p></div>"

2878 excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"

2879 title=\"Shortcut: g${nl}Remove from the Group\"

2880 for=\"conf\">GRP登録解除</label>

2881 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2882 <p>この操作による通知は本人に行きません。

2883 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2884 </div>"

2885 # Get team list to which current user belongs into $hexteams

2886 allhexteams=$(hexteams "$grp")

2887 if [ -n "$isowner" ]; then

2888 myhexteams="$allhexteams" # admin can remove all teams' attr

2889 else

2890 myhexteams=$(hexteams "$grp" "$user")

2891 fi

2892 if [ -n "$myhexteams" ]; then

2893 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"

2894 title=\"Shortcut: s${nl}Strip a team tag from\"

2895 for=\"cmrmteam\">チーム属性除去</label>

2896 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2897 を除去します: `cgi_checkbox teamconfirm yes` 確認

2898 <p>この操作による通知は本人に行きません。

2899 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2900 </div>

2901 "

2902 fi

2903 stf=$tmpd/selteam.$$

2904 cgi_select_h selteam "5445414d" $allhexteams > $stf

2905 b1='<label> <input type="checkbox" name="usel" value="'

2906 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2907 br='<span id="reverse" title="Reverse Selection"></span>'

2908 #b2='"> <span>' b3='</span></label>'

2909 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2910 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2911 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2912 cgi_form grpaction<<EOF \

2913 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2914 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2915 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2916 | _m4 -D_TITLE_="spaste(\`$tf')" \

2917 -D_SUBTITLE_="チェック後操作ボタン" \

2918 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2919 $layout/form+dump.m4.html \

2920 | _m4 -D_TEAM_="spaste(\`$stf')"

2921 <p>下でチェックした人を対象として:</p>

2922 <div class="foldtabs">

2923 `cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"

2924 title="Shortcut: a${nl}Add a team tag to"

2925 for="cmteam">同じチーム属性を付与</label>

2926 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2927 `cgi_datalist_h teams $allhexteams`

2928 </div>

2929 ${rmteammsg}

2930 `cgi_radio rm send id="sendmsg"`<label accesskey="d"

2931 title="Shortcut: d${nl}DirectMail to"

2932 for="sendmsg" title="Direct Message">DM送信</label>

2933 <div>

2934 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2935 title="Using email format">email書式を使う</label>

2936 <div class="folded">

2937 <table>

2938 <tr><td>From: </td><td>$user</td></tr>

2939 <tr><td>このFrom:で送る</td>

2940 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2941 </td></tr>

2942 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2943 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2944 <tr><td>ファイル添付: </td>

2945 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2946 </table>

2947 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2948 </div>

2949 <div>本文:`cgi_textarea text "" cols=72`

2950 </div>

2951 </div>

2952 ${isowner:+$cmmsg$excmsg}

2953 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2954 </div>

2955 <h4>$grp 参加者一覧</h4>$fromtonote

2956 <table class="td2r thl">

2957 `sq $db -header -html "$sql"`

2958 </table>

2959 `cgi_hidden grp $grid`

2960 `cgi_hidden myuid $myuid id="myuid"`

2961 EOF

2962 }

2963 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2964 # Create TEMPORARY VIEW

2965 query<<EOF

2966 CREATE TEMPORARY VIEW writeusers AS

2967 SELECT DISTINCT author FROM article

2968 WHERE id in (

2969 select id from article where blogid=(select id from blog where rowid=$1)

2970 );

2971 CREATE TEMPORARY VIEW movablegroups AS

2972 SELECT g.rowid growid , g.gname

2973 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2974 ON grp.gname=gm.gname -- そのユーザが属している

2975 AND user='$user') g -- グループに絞る

2976 WHERE (SELECT author FROM writeusers

2977 EXCEPT

2978 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2979 IS NULL;

2980 $2

2981 EOF

2982 }

2983 sql4readableblogs() {

2984 # Create view of blogs that can be readable to $user

2985 # Blog is readable when:

2986 # 1: blog owner is an user

2987 # 2: else, 2.1: owner-group where the $user belongs

2988 # 2.2: else, owner-group is not moderated

2989 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2990 cat<<EOF ## | tee tmp/sql.out

2991 CREATE TEMPORARY VIEW readableblogs AS

2992 SELECT blog.rowid rid, id, author

2993 FROM blog

2994 NATURAL JOIN

2995 (SELECT id,

2996 max(CASE key WHEN 'owner' THEN val END) owner,

2997 max(CASE key WHEN 'mode' THEN val END) mode

2998 FROM blog_s GROUP by id) bs

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

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

3001 WHEN (SELECT val FROM grp_s

3002 WHERE gname=bs.owner AND key='regmode') = 'moderated'

3003 AND

3004 (SELECT user FROM grp_mem

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

3006 THEN 0

3007 WHEN mode IN ('quiz', 'enquete')

3008 THEN 0 -- "quiz" mode blog cannot be searched

3009 ELSE 1

3010 END;

3011 EOF

3012 }

3013 editheading() { # $1=rowid-of-heading

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

3015 if [ -z "$rowid" ]; then

3016 echo "話題番号が未指定です。" | html p

3017 return

3018 fi

3019 owner=`getvalbyid blog owner $rowid`

3020 title=`getvalbyid blog title $rowid`

3021 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

3022 | _m4 -D_TITLE_="修正" \

3023 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

3024 -D_BLOGS_="" -D_DUMPTABLE_="" \

3025 -D_FORM_="syscmd(\`cat')" \

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

3027 # Move to group

3028 if isuser "$owner"; then

3029 crview4article $rowid

3030 n=`query "SELECT count(*) FROM writeusers;"`

3031 ## err N=$n

3032 if [ $((n)) -gt 0 ]; then

3033 ## err ROWID=$rowid

3034 sql="SELECT growid || ':' || gname FROM movablegroups;"

3035 cat<<-EOF

3036 <div class="fold">

3037 `cgi_checkbox mv send id="mv"`<label

3038 for="mv">この話題をグループ所有に移動する</label>

3039 <div>

3040 <form action="?mvart" method="POST" enctype="multipart/form-data">

3041 移動先グループ:

3042 <select name="mv2grp">

3043 EOF

3044 query ".mode html"

3045 query<<-EOF |

3046 $sql

3047 .mode list

3048 EOF

3049 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

3050 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

3051 cat<<-EOF

3052 </select>

3053 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

3054 そのグループに加入しているものに限られます)</p>

3055 <p>`cgi_checkbox cfm yes`<label>確認

3056 (この操作は元に戻すことができません)</label></p>

3057 `cgi_hidden blogrowid $rowid`

3058 `cgi_submit 移動`

3059 `cgi_reset Reset`

3060 </form>

3061 </div>

3062 </div>

3063 EOF

3064 fi

3065 # end of isuser "$owner"

3066 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3067 [ -n "$hexteams" ];}; then

3068 none="`echo なし|hexize`"

3069 cat<<-EOF

3070 <div class="fold">

3071 `cgi_checkbox mv2team send id="mv2team"`<label

3072 for="mv2team">この話題を以下のチームのものにする</label>

3073 <div><p>現在の所属チーム設定:

3074 `query "SELECT

3075 coalesce((SELECT val FROM blog_s

3076 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3077 AND key='team'),

3078 ':なし');"`</p>

3079 <form action="?mvart" method="POST" enctype="multipart/form-data">

3080 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3081 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3082 `cgi_hidden blogrowid $rowid`<br>

3083 `cgi_submit 移動`

3084 `cgi_reset Reset`

3085 </form></div></div>

3086 EOF

3087 fi

3088 }

3089 mvart() { # move diary to some group or team

3090 # or move blog of group to team which belong to the group

3091 blogrowid=`getpar blogrowid`

3092 cfm=`getpar cfm`

3093 ##### echo move blog:$blogrowid to $mv2grp | html p

3094 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3095 . ./s4-blog.sh

3096 if [ -z "$blogrowid" ]; then

3097 echo "無効な指定です(mvart)。" | html p

3098 return

3099 elif [ x"$cfm" != x"yes" ]; then

3100 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3101 elif { mv2grp=`getpar mv2grp`

3102 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3103 [ -n "$mv2grp" ]; }; then

3104 crview4article $blogrowid

3105 ########## TRANSACTION BEGIN

3106 query "BEGIN;"

3107 n=`query "SELECT count(*) FROM writeusers;"`

3108 ## err Nwriteuser=$n

3109 if [ $((n)) -gt 0 ]; then

3110 query<<-EOF

3111 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3112 WHERE key='owner'

3113 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3114 AND $mv2grp IN (SELECT growid FROM movablegroups);

3115 EOF

3116 fi

3117 query "END;"

3118 ########## TRANSACTION END

3119 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3120 [ -n "$mv2team" ];}; then

3121 # blog owner can move it to ANY team

3122 case "$mv2team" in

3123 'なし')

3124 cat<<-EOF

3125 DELETE FROM blog_s

3126 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3127 AND key='team';

3128 EOF

3129 ;;

3130 "") ;;

3131 *)cat<<-EOF

3132 BEGIN;

3133 REPLACE INTO blog_s(id, key, val)

3134 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3135 'team', '$mv2team');

3136 REPLACE INTO blog_s(id, key, val)

3137 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3138 'notify', 'all'); -- Change notify to all

3139 END;

3140 EOF

3141 esac | query

3142 fi

3143 blog_reply $blogrowid

3144 echo yes | html p

3145 }

3146 editart() { # $1=article-rowid $2=blogrowid

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

3148 blogrowid=${2%%[!A-Z0-9a-z_]*}

3149 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3150 echo "表示する記事番号が未指定です。" | html p

3151 return

3152 fi

3153 owner=`getvalbyid blog owner $blogrowid`

3154 title=`getvalbyid blog title $blogrowid`

3155 author=`getvalbyid article author $rowid`

3156 ## err EDITart: owner=$owner, author=$author

3157 if isgrpowner "$user" "$owner"; then

3158 : EDIT OK

3159 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3160 echo "本人か所有者しか編集できません." | html p

3161 return

3162 fi

3163 aid=`query "select id from article where rowid=$rowid;"`

3164 tmpout=$tmpd/editart.$$.out

3165 GF_ACTION="?replyblog+$blogrowid#$aid" \

3166 edittable $formdir/article.def article $rowid \

3167 > $tmpout

3168 rm -f /tmp/editart.out

3169 # Cannot use pipelining to m4 with genform() because of stdin stack

3170 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3171 -D_FORM_="syscmd(cat $tmpout)" \

3172 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3173 -D_BLOGS_= -D_DUMPTABLE_= \

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

3175 }

3176 send2mem() {

3177 rowid=`getpar grp`

3178 rowid=${rowid%%[!0-9]*} # Cleaning

3179 if [ -z "$rowid" ]; then

3180 echo "グループが未指定です。" | html p

3181 return

3182 fi

3183 message=`getpar message`

3184 if [ -z "$message" ]; then

3185 echo "文章を入れてください。" | html p

3186 return

3187 fi

3188 grp=`getgroupbyid $rowid`

3189 members=`collectemail $grp`

3190 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3191 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3192 mailfrom="`gecos "$user"` <$mailfrom>"

3193 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3194 msg=$(cat<<-EOF

3195 $urlbase?grp+$rowid

3196 グループ $grp に所属する

3197 `gecos $user` さんよりメッセージ:

3198

3199 $message

3200 EOF

3201 )

3202 # smail rcpt subj (file)

3203 for m in $members; do

3204 echo "$msg" |

3205 MAIL_FROM=$mailfrom \

3206 SENDER=$noreply \

3207 REPLYTO=$mailfrom \

3208 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3209 smail "$m" "$sj"

3210 done

3211 cat<<EOF

3212 <p>以下のユーザに送信しました。</p>

3213 <pre>

3214 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3215 </pre>

3216 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3217 EOF

3218 }

3219 joingrpadmit() {

3220 # $1=yes/no $2=session-key

3221 if [ -z "$2" ]; then

3222 echo "bye bye" | html p; return

3223 fi

3224 t_usr=`session=$2 getpar user`

3225 t_grp=`session=$2 getpar group`

3226 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3227 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3228 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3229 echo "無効な加入依頼です。" | html p

3230 echo "有効期限が切れたか、

3231 他の管理者がいる場合は処理済みの可能性があります。" | html p

3232 return

3233 fi

3234 if ! isgrpowner "$user" "$t_grp"; then

3235 echo "グループ管理者のみの機能です。" | html p; return

3236 fi

3237 case $1 in

3238 yes) joingrp "$t_grp" "$t_usr" yes ;;

3239 no) joingrp "$t_grp" "$t_usr" no ;;

3240 *)

3241 echo "無効な指定です($1)。" | html p

3242 return ;;

3243 esac

3244 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3245 rcpts="`getgroupadminmails $t_grp` $user"

3246 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3247 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3248 に

3249 $t_usr

3250 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3251 しました。"

3252 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3253 query "delete from session where id='$2';"

3254 echo "$body" | html p

3255 }

3256

3257 joingrprequest() {

3258 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3259 jss="joingrp-`date +%s`-`genrandom 12`"

3260 addsession $jss +${memoplimitdays}days

3261 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3262 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3263 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3264 $url

3265 $user さんから

3266 グループ $1

3267 に加入依頼がありました。

3268

3269 承認する:

3270 $urlbase?joingrpadmit+yes+$jss

3271

3272 白紙に戻す:

3273 $urlbase?joingrpadmit+no+$jss

3274 EOF

3275 echo "管理者に加入依頼を出しました。

3276 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3277 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3278 }

3279 joingrp() {

3280 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3281 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3282 if isgrpowner "$user" "$1"; then

3283 isowner="yes"

3284 elif [ -n "$5" ]; then

3285 isowner="yes"

3286 else

3287 isowner=""

3288 fi

3289 ## err jg:isgrpowner: isowner="$isowner"

3290 if [ -n "$isowner" ]; then

3291 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3292 elif [ x"$2" != x"$user" ]; then # if user is not login user

3293 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3294 return

3295 elif [ x"$3" = x"no" ]; then

3296 : # Do not pursue those who leave

3297 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3298 : # Member can change own email address for the joining moderated group

3299 else # adding user is $user itself

3300 case `getgroupattr $1 regmode` in

3301 moderated)

3302 joingrprequest "$@" # Request only

3303 return

3304 ;;

3305 *)

3306 ;;

3307 esac

3308 fi

3309 qgname=`sqlquote "$1"`

3310 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3311 cond="where gname=$qgname and user='$2'"

3312 if [ x"$3" = x"yes" ]; then

3313 query "replace into grp_mem values($qgname, '$2');"

3314 # Notify joingrp to admin

3315 action="に加入しました。"

3316 if [ -n "$4" ]; then

3317 if msg=`emaildomaincheck "$4"`; then

3318 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3319 'string', '$4', NULL);"

3320 else

3321 echo $msg

3322 fi

3323 else

3324 query "delete from grp_mem_s $cond and key='email';"

3325 fi

3326 if [ -n "$5" ]; then # as ADMIN

3327 # Coming here means newly created group

3328 sql="select case\

3329 when (select count(*) from grp_mem where gname=$qgname)=1\

3330 then (select user from grp_mem\

3331 where gname=$qgname and user='$user')\

3332 else '' end; "

3333 err NewGrpChk: $sql

3334 if [ -n "`query \"$sql\"`" ]; then

3335 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3336 query "replace into grp_adm values($qgname, '$user');"

3337 fi

3338 fi

3339 else

3340 query "begin;

3341 delete from grp_mem $cond;

3342 delete from grp_mem_s $cond;

3343 delete from grp_mem_m $cond;

3344 delete from grp_adm $cond;

3345 delete from grp_adm_s $cond;

3346 delete from grp_adm_m $cond;

3347 end;"

3348 action="から脱退しました。"

3349 fi

3350 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3351 $url?grp+$grid

3352 $user (`gecos $user`)さんが

3353 グループ $1

3354 $action

3355 EOF

3356 }

3357 grp_add_team() (

3358 # $1=grp-rowid $2=team $3...=user-rowid(s)

3359 grp=`getgroupbyid $1`

3360 team=$2; shift; shift

3361 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3362 { echo "BEGIN;"

3363 for user; do

3364 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3365 '$grp',\

3366 (SELECT name FROM user WHERE rowid=$user),\

3367 'team', 'string', '$team');"

3368 done

3369 echo "END;"

3370 } | query

3371 )

3372 grp_rm_team() (

3373 # $1=grp-rowid $2=team $3...=user-rowid(s)

3374 grid=$1

3375 qgrp=$(sqlquote "`getgroupbyid $grid`")

3376 team=$2; shift; shift

3377 [ -z "$grid" -o -z "$team" ] && return

3378 { echo "BEGIN;"

3379 for user; do

3380 echo "DELETE FROM grp_mem_m\

3381 WHERE gname=$qgrp \

3382 AND user=(SELECT name FROM user WHERE rowid=$user)\

3383 AND key='team' AND val='$team';"

3384 done

3385 cat<<-EOF

3386 DELETE FROM blog_s

3387 WHERE rowid=(

3388 SELECT rowid

3389 FROM blog_s a

3390 WHERE key='team'

3391 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3392 AND NOT EXISTS (SELECT * FROM grp_mem_m

3393 WHERE key='team' AND val=a.val -- a.val=team

3394 AND gname = (SELECT val FROM blog_s b

3395 WHERE a.id=b.id AND key='owner')

3396 ));

3397 EOF

3398

3399 echo "END;"

3400 } | query

3401 )

3402 grp_reg_adm() {

3403 # $1=grp-rowid $2...=user-rowid

3404 grid=$1

3405 grp=`getgroupbyid "$1"`

3406 if [ -z "$grp" ]; then

3407 echo "無効なグループIDです" | html p; return

3408 fi

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

3410 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3411 fi

3412 shift

3413 for urid; do

3414 newadm=`query "select name from user where rowid=$urid;"`

3415 if [ -z "$newadm" ]; then

3416 echo "指定ユーザIDがおかしいようです。" | html p; return

3417 fi

3418 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3419 err ismember $newadm $grp

3420 if ismember $newadm "$grp"; then

3421 # OK, go ahead

3422 getgname="(select gname from grp where rowid=$grid)"

3423 query "replace into grp_adm values($getgname, '$newadm');"

3424 # confirm insertion

3425 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3426 if [ -n "`query \"$sql;\"`" ]; then

3427 echo "追加完了: $newadm" | html p

3428 else

3429 echo "追加失敗($1 $urid)" | html p

3430 fi

3431 fi

3432 showgroup $grid

3433 done

3434 }

3435 dt_rowhack() {

3436 # From: <TR>

3437 # ....

3438 # <TD>rowclass=foo</TD>

3439 # </TR>

3440 # To: <TR class="foo">....<TD>foo</TD></TR>

3441 sed -e '

3442 /^<TR>/ {

3443 :loop

3444 s/\n//

3445 N

3446 /<\/TR>/ {

3447 s/\n//

3448 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3449 n

3450 }

3451 $q

3452 b loop

3453 }'

3454 }

3455 dumptable() {

3456 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3457 # textのフィールドだけ全てダンプにしたほうがいいか

3458 # $DT_VIEW sets link

3459 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3460 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3461 VIEW=${DT_VIEW-replyblog}

3462 if [ -n "$VIEW" ]; then

3463 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3464 fi

3465 sqlfile=$tmpd/dump.sql

3466 : > $sqlfile # ensure to be empty

3467 printf '.mode html\n.header 1\n' > $sqlfile

3468 # $DT_CHLD=ChildTable:BindColumn

3469 if [ -n "$DT_CHLD" ]; then

3470 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3471 cat<<-EOF >> $sqlfile

3472 -- presql

3473 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3474 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3475 EOF

3476 # Speed up counting of new articles

3477 cat<<-EOF >> $sqlfile

3478 -- presql2

3479 DROP TABLE IF EXISTS _counts;

3480 CREATE TEMPORARY TABLE _counts AS

3481 SELECT $_i, count($_i) cnt

3482 FROM $_t GROUP BY $_i;

3483 /* Prepare NEW count table */

3484 CREATE TEMPORARY TABLE _target AS

3485 SELECT b.rowid trowid, b.id

3486 FROM "$2" b JOIN "$2_s" s

3487 ON b.id=s.id AND s.key='owner'

3488 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3489

3490 DROP TABLE IF EXISTS _children;

3491 CREATE TEMPORARY TABLE _children AS

3492 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3493 FROM (SELECT t.trowid, t.id $_i, a.id

3494 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3495 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3496

3497 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3498 DROP TABLE IF EXISTS _news;

3499 DROP VIEW IF EXISTS _news;

3500

3501 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3502 -- INSERT INTO _news

3503 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3504 CREATE TEMPORARY VIEW _news AS

3505 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3506 FROM (SELECT DISTINCT id FROM _target)

3507 a LEFT JOIN

3508 (SELECT $_i, count(ctime) newcnt

3509 FROM _children x

3510 WHERE ctime > coalesce((SELECT time from myacclog

3511 WHERE tblrowid=x.trowid),

3512 '1970-01-01')

3513 GROUP BY $_i) b

3514 ON a.id=b.$_i;

3515 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3516 EOF

3517 # REMOVE next line until 2019/5/1

3518 cntall="(select count($_i) from $_t where $_i=a.id)"

3519 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3520 # REMOVE next assignment until 2019/5/1

3521 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3522 and id in (select id from $_t where $_i=a.id) \

3523 and val > coalesce((select time from myacclog where \

3524 tblrowid=a.rowid),\

3525 '1970-01-01'))"

3526 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3527 cnt="$cntnew as '新着', $cntall as '総数',"

3528 dt_class=" td2r td3r dumpblogs"

3529 fi

3530 # Construct join expression

3531 eav="" scols=""

3532 pk=`gettblpkey $2`

3533 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3534 substr=${substr:-%s}

3535 for col in ${3:-`gettbl_s_cols $2`}; do

3536 valvar=val

3537 case $col in

3538 gecos) scols="$scols${scols:+, }${col#}"

3539 continue ;; # built-in column name

3540 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3541 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3542 case "$as" in

3543 *:*=*) cnd=${as#*:}

3544 h=${cnd%%=*} v=${cnd#*=}

3545 h=`sqlquotestr "$h"`

3546 v=`sqlquotestr "$v"`

3547 valvar="CASE val WHEN $h THEN $v END"

3548 as=${as%%:*} ;;

3549 esac

3550 ;;

3551 *) as=${col} ;;

3552 esac

3553 ss=`printf "$substr" "$valvar"`

3554 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3555 scols="$scols${scols:+, }b.$as"

3556 done

3557 #case author when '$user' then a.rowid else '---' end as ID,

3558 if [ -n "$DT_SQL" ]; then

3559 echo "$DT_SQL"

3560 else

3561 cat<<-EOF

3562 SELECT a.rowid as LINK, $cnt $scols

3563 FROM $2 a LEFT JOIN

3564 (SELECT $pk,$eav,

3565 max(CASE key

3566 WHEN 'owner'

3567 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3568 as gecos

3569 FROM ${2}_s c GROUP BY $pk)

3570 b ON a.$pk=b.$pk $4;

3571 EOF

3572 fi >> $sqlfile

3573 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3574 sqlog<<-EOF

3575 *** SQL-file: $sqlfile ***

3576 `cat $sqlfile`

3577 EOF

3578 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3579 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3580 cat $sqlfile >> tmp/sql

3581 # query ".read $sqlfile" > $tmpd/foo

3582 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3583 cp $tmpd/foo tmp/

3584 err "----- `gdate +%FT%T.%3N` ------------555555"

3585 ## $ddd LINE exists at the end of this function

3586 fi

3587 printf '.mode list\n.header 0\n' >> $sqlfile

3588 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3589 <div>

3590 <div class="dumptable">

3591 <table class="b$dt_class">

3592 `query ".read $sqlfile"`

3593 </table>

3594 </div>

3595 </div>

3596 EOF

3597 ### `query ".read $sqlfile"`

3598 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3599

3600 ## REMOVE THIS!

3601 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3602 }

3603

3604 par2table() (

3605 # copy current parameters of par into destination table

3606 # $1=definition-file

3607 # Using $user and $session

3608 # Return value:

3609 # 0: Stored successfully

3610 # 1: Insufficient fillings

3611 # 2: No permission to modify the record

3612 # 3: Invalid rowid

3613 # 4: SUCCESS to delete

3614 # 5: Stop deletion for lack of confirm check

3615 # 6: Password length too short

3616 # 7: Password mismatch

3617 # 8: Old password incorrect

3618 rowid=`getpar rowid`

3619 if [ ! -e $1 ]; then

3620 echo "テーブル定義ファイルが見付かりません" | html p

3621 exit 1

3622 fi

3623 tbl=${1%.def}

3624 tbl=${tbl##*/}

3625 if [ -n "$rowid" ]; then # Modify existing entry

3626 if [ x"$tbl" = x"user" ]; then

3627 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3628 elif [ x"$tbl" = x"grp" ]; then

3629 sql="select gname from $tbl where rowid=$rowid;"

3630 ##err p2t:grp:q $sql

3631 isgrpowner "$user" "`query $sql`" && rowowner=$user

3632 elif [ x"$tbl" = x"blog" ]; then

3633 # Check if owner in blog_s

3634 blogowner=`getvalbyid blog owner "$rowid"`

3635 if isgrpowner "$user" "$blogowner"; then

3636 rowowner=$user

3637 else

3638 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3639 fi

3640 else

3641 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3642 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3643 fi

3644 ### err rowowner=$rowowner

3645 if [ x"$user" != x"$rowowner" ]; then

3646 echo "他人のレコードはいじれないの" | html p

3647 return 2

3648 elif [ -z "$rowowner" ]; then

3649 echo "指定したレコードはないみたい" | html p

3650 return 3

3651 fi

3652 rm=`getpar rm` cfm=`getpar confirm`

3653 # Editing existent entry

3654 if [ x"$rm" = x"yes" ]; then

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

3656 query "delete from $tbl where rowid=$rowid;"

3657 return 4

3658 else

3659 echo "消去確認のチェックがないので消さなかったの..." | html p

3660 return 5

3661 fi

3662 fi

3663 fi

3664

3665 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

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

3667 # Update of existing record

3668 for col in `gettblcols $tbl`; do

3669 val=`getparquote $col`

3670 [ -z "$val" ] && continue

3671 ## err query "update $tbl set $col=$val where rowid=$rowid"

3672 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3673 sql="update $tbl set $col=$val where rowid=$rowid;"

3674 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3675 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3676 ## User name cannot be changed with interface provided with this

3677 ## script. But we offer the trigger to change owner user

3678 ## of blog_s table.

3679 #err "select quote($col) from $tbl where rowid=$rowid;"

3680 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3681 cat<<-EOF | query

3682 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3683 -- should use EXCLUSIVE transaction outside of this.

3684 SAVEPOINT par2table;

3685 $sql

3686 update blog_s set val=$val

3687 where key='owner' and val=$old;

3688 RELEASE SAVEPOINT par2table;

3689 EOF

3690 ## XX: DIRTY Hack Ends here

3691 ## We should keep blog's owner as a single column which has

3692 ## foreign key constraint with primary key of grp/user.

3693 else

3694 query "$sql"

3695 fi

3696 done

3697 # Then, set up $pval for further insertion of tbl_s and tbl_m

3698 for col in `gettblpkey $tbl`; do

3699 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3700 pval="$pval${pval:+, }\"$val\""

3701 done

3702 else

3703 # New entry

3704 # Generate values() for primary keys

3705 for col in `gettblpkey $tbl`; do

3706 # Genuine primary keys for _m and _s

3707 val=`getvalquote $tbl $col`

3708 [ -z "$val" ] && continue

3709 pval="$pval${pval:+, }$val"

3710 done

3711 ##err pval=$pval

3712 for col in `gettblfkey $tbl`; do

3713 # args for values() to insertion into master table

3714 val=`getvalquote $tbl $col`

3715 [ -z "$val" ] && continue

3716 formaster=$formaster"${formaster:+, }$val"

3717 done

3718 formaster="$pval${formaster:+, }$formaster"

3719 ## err formaster=$formaster

3720 if [ -z "$formaster" ]; then

3721 echo "項目を全て埋めてください" | html pre

3722 return 1

3723 fi

3724 ## err "replace into $tbl values($formaster);"

3725 query "replace into $tbl values($formaster);"

3726 ## Insertion to master table, done

3727 fi

3728

3729 transaction=$tmpd/sqlfile.sql; touch $transaction

3730 for kt in s m; do

3731 tb2=${tbl}_$kt

3732 for col in `gettbl_${kt}_cols $tbl`; do

3733 ptype=`getpartype $col "limit 1"`

3734

3735 # First, check update of existing entries in _m

3736 if [ $kt = m ]; then

3737 # sessID|address.1.22|string|Somewhere-x.y.z

3738 sql=""

3739 ##err dots from query "select var from par where var like '$col.%';"

3740 for v in `query "select var from par where var like '$col.%';"`; do

3741 # v=address.1.22

3742 st_rowid=${v##*.}

3743 origcol=${v%%.*} # original column derived from

3744 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3745 ##case `getpartype $v` in

3746 ## err CASE `gettbl_coltype $tbl/$origcol` in

3747 ## err edit flag = `getpar action.$v`

3748 case `getpar action.$v` in

3749 rm)

3750 if [ x`getpar confirm.$v` = x"yes" ]; then

3751 newsql="delete from $tb2"

3752 else

3753 echo "削除確認未チェック" | html p

3754 fi ;;

3755 edit)

3756 case `gettbl_coltype $tbl/$origcol` in

3757 image|document|binary)

3758 file=$tmpd/`getparfilename $v`

3759 ## err type=file=$file

3760 [ -z "$file" ] && continue

3761 bn=`sqlquotestr "${file##*/}"`

3762 bin="X'"$(hexize "$file")"'"

3763 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3764 type=\"file:$ct\"

3765 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3766 cachedir=`getcachedir "$tbl/$rowid"`

3767 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3768 rm -rf $cachedir

3769 ;;

3770 *)

3771 newsql="update $tb2 set val=(select val from par where var \

3772 like '$col.%.$st_rowid')"

3773 ;;

3774 esac

3775 ;;

3776 *) # maybe "keep", do not modify value

3777 continue

3778 ;;

3779 esac

3780 # err newsql=$newsql

3781 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3782 done

3783

3784 if [ x"$bin" = x"NULL" ]; then

3785 ## err repl:normal sql=`echo $sql`

3786 if [ -n "$transaction" ]; then

3787 cat<<-EOF >> $transaction

3788 $sql

3789 DELETE FROM $tb2 WHERE type='string' AND val='';

3790 EOF

3791 else

3792 query "$sql

3793 delete from $tb2 where type='string' and val='';"

3794 ## err repl:normal done

3795 fi

3796 else

3797 # Binary update line is TOO LONG to pipelining

3798 sqlfile="$tmpd/sqlf.$$"

3799 if [ -n "$transaction" ]; then

3800 echo "$sql" >> $transaction

3801 else

3802 echo "$sql" > $sqlfile

3803 query ".read $sqlfile"

3804 fi

3805 fi

3806 # Rest of kt==m: set multiple mode

3807 nr=`getparcount $col`

3808 else

3809 nr=1 # for kt==s, number of records is 1

3810 fi

3811

3812 i=0

3813 while [ $i -lt $nr ]; do

3814 limit="limit 1 offset $i"

3815 i=$((i+1)) # increase beforehand against continue

3816 val=`getvalquote $tbl $col "$limit"`

3817 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3818 ## err $col=$val

3819 bin=NULL

3820 ## err partype$col=`getpartype $col "$limit"`

3821 case $ptype in

3822 file) file=$tmpd/`getparfilename $col "$limit"`

3823 ## err parfile-$col=$file

3824 [ -z "$file" ] && continue

3825 bin="X'"$(hexize "$file")"'"

3826 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3827 type=\"file:$ct\" ;;

3828 "*"*) continue ;; # foreign table

3829 *) type=\"string\" ;;

3830 esac

3831 case `gettbl_coltype $tbl/$col` in

3832 password) # special care for password

3833 # name={password,pswd1,pswd2}

3834 p1=`getpar pswd1 "$limit"`

3835 if [ -z "$p1" ]; then

3836 continue # SKIP password setting, if p1 is empty

3837 else

3838 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3839 ## err pswd=$pswd

3840 if pwcheck "$pswd"; then

3841 if [ x"$p1" = x"$p2" ]; then

3842 case "$p1" in

3843 ??????????*) ;;

3844 *) echo "パスワードは10字以上にしてください。" | html p

3845 return 6;;

3846 esac

3847 val="\"`echo $p1|mypwhash`\""

3848 else

3849 echo "2つの新パスワード不一致" | html p

3850 return 7

3851 fi

3852 else

3853 echo "旧パスワード違います" | html p

3854 return 8

3855 fi

3856 fi

3857 ;;

3858 esac

3859 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3860 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3861 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3862 if [ x"$bin" = x"NULL" ]; then

3863 ## err Normal-query: `echo $sql`

3864 if [ -n "$transaction" ]; then

3865 echo "$sql" >> $transaction

3866 else

3867 query "$sql"

3868 fi

3869 else

3870 sqlfile="$tmpd/query.$$"

3871 ## err sqlfile=`ls -lF $sqlfile`

3872 if [ -n "$transaction" ]; then

3873 echo "$sql" >> $transaction

3874 else

3875 echo "$sql" > $sqlfile

3876 query ".read $sqlfile"

3877 fi

3878 fi

3879 ## err p2t done

3880 done

3881 done

3882 done

3883 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3884 -- We cannot use transaction here, because groupupdate may use it.

3885 SAVEPOINT pa2table_insert;

3886 .read $transaction

3887 RELEASE SAVEPOINT pa2table_insert;

3888 EOF

3889 return $?

3890 ##err donee

3891 )

3892 genform() {

3893 # $1 = form definition file

3894 # $2, $3 (optional)= table name and ROWID

3895 # If $GF_VIEWONLY set and nonNull, output values without form

3896 # If $GF_ARGS set, use it as content-strings in the form

3897 # If $GF_OWNER set, use it as value of name="owner"

3898 # If $GF_STAGE set, use it as value of name="stage"

3899 forms="" hiddens="" rowid=$3

3900 if [ ! -e "$1" ]; then

3901 echo "そのようなデータベースはないようです($2)。" | html p

3902 return

3903 elif [ -n "$2" ]; then

3904 rec=`query "select * from $2 where rowid='$rowid';"`

3905 if [ -z "$rec" ]; then

3906 pk=`gettblpkey $2`

3907 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3908 rec=`query "select rowid from $2 where $pk='$rowid';"`

3909 rowid=$rec

3910 rec=$3

3911 fi

3912 if [ -z "$rec" ]; then

3913 echo "そんなレコードはないみたいね..." | html p

3914 return

3915 fi

3916 fi

3917 if [ -z "$GF_VIEWONLY" ]; then

3918 rm='<input id="rm" name="rm" type="checkbox"

3919 value="yes"><label for="rm">このエントリの削除</label>

3920 <span>ほんとうに消しますよ(確認)!

3921 <input name="confirm" type=checkbox value="yes">はい</span>'

3922 fi

3923 # Image Cache dir

3924 ## err genform: getcache=$2/$rowid

3925 td=`getcachedir "$2/$rowid"`

3926 while IFS=: read prompt name keytype type args; do

3927 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3928 sp="${args:+ }"

3929 form="" val=""

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

3931 # err genform2a: Seeking for "$2.$name, type=$type"

3932 rawval=`getvalbyid $2 $name $rowid $td`

3933 val=`echo "$rawval"|htmlescape`

3934 ## err genform3a: getvalbyid $2 $name $rowid $td

3935 ## err genform3b: val="[$val]" type="$type"

3936 fi

3937 if [ -n "$GF_VIEWONLY" ]; then

3938 is_hidden "$2" "$name" && continue

3939 fi

3940 case "$type" in

3941 text*)

3942 cgiform=cgi_multi_$type

3943 if [ -s $td/$name.count -a -n "$val" ]; then

3944 form=`$cgiform $name $td`

3945 val=$(echo "$val"|

3946 while read fn; do

3947 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3948 </td></tr>$nl"

3949 done)

3950 val="<table>$nl$val$nl</table>"

3951 else

3952 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3953 form=`cgi_$type $name "$rawval" "$args"`

3954 fi

3955 ;;

3956 [Rr][Aa][Dd][Ii][Oo])

3957 fh="<label><input type=\"radio\" name=\"$name\""

3958 form="`echo $args|sed -e \

3959 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3960 ;;

3961 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3962 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3963 ;;

3964 [Ss][Ee][Ll][Ee][Cc][Tt])

3965 fh="<select name=\"$name\">$nl"

3966 form=$(for l in $args; do

3967 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3968 done)

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

3970 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3971 fi

3972 form="$fh$form</select>"

3973 ;;

3974 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3975 if [ -s $td/$name.count ]; then

3976 form=`cgi_multi_file $name $td "$args"`

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

3978 hrfb="$myname?showattc+$2_m"

3979 val=$(echo "$rawval" \

3980 | while read fn; do

3981 data=`percenthex "$td/$fn"`

3982 #ct=`cat $td/$fn.content-type`

3983 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3984 ri=`cat "$td/$fn.rowid"`

3985 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3986 #imgsrc="<img src=\"data:$ct,$data\">"

3987 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3988 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3989 done)

3990 fi

3991 else

3992 form="<input type=\"file\" name=\"$name\" $args>"

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

3994 imgs=$(echo "$rawval"\

3995 |while read fn;do

3996 data=`percenthex "$td/$fn"`

3997 echo "<img src=\"data:image/png,$data\">$fn<br>"

3998 done)

3999 form=$form"<br>$imgs"

4000 val=$imgs # 2015-06-15

4001 else

4002 form="<input type=\"file\" name=\"$name\" $args>"

4003 fi

4004 fi

4005 ;;

4006 [Hh][Ii][Dd][Dd][Ee][Nn])

4007 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4008 args="value=\"$GF_STAGE\""

4009 fi

4010 form="<input type=\"hidden\" name=\"$name\" $args>"

4011 prompt='' # Remove prompt

4012 ;;

4013 [Aa][Uu][Tt][Hh][Oo][Rr])

4014 [ -n "$GF_VIEWONLY" ] && continue

4015 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4016 prompt="" ;;

4017 [Oo][Ww][Nn][Ee][Rr])

4018 [ -n "$GF_VIEWONLY" ] && continue

4019 val=${GF_OWNER:-$val}

4020 val=${val:-$user}

4021 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4022 prompt="" ;;

4023 [Uu][Ss][Ee][Rr])

4024 # XXX: is null $user ok?

4025 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4026 [ -n "$GF_VIEWONLY" ] && continue

4027 form="$user"

4028 ;;

4029 [Pp]assword)

4030 [ -n "$GF_VIEWONLY" ] && continue

4031 form="`cgi_passwd`"

4032 val=""

4033 ;;

4034 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4035 [ -n "$GF_VIEWONLY" ] && continue

4036 if [ -z "$rowid" ]; then

4037 val=`genserial`

4038 fi

4039 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4040 prompt="" ;;

4041 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4042 prompt=""

4043 ;;

4044 parent|path|blog*)

4045 prompt=""

4046 ;;

4047 "*"*)

4048 tail=$tail"``"

4049 continue ;;

4050 esac

4051 if [ -n "$prompt" ]; then

4052 if [ -n "${GF_VIEWONLY}" ]; then

4053 form=$val

4054 else

4055 :

4056 fi

4057 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4058 else

4059 hiddens=$hiddens$nl"$form"

4060 fi

4061 done < $1

4062 # enctype="multipart/form-data"

4063 cat<<EOF

4064 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4065 ${rowid:+$rm}

4066 <table class="b $2">

4067 $forms

4068 </table>$hiddens

4069 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4070 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4071 EOF

4072 if [ -z $GF_VIEWONLY ]; then

4073 cat<<EOF

4074 <input type="submit" name="sub" value="OK">

4075 <input type="reset" name="res" value="Reset">

4076 EOF

4077 fi

4078 cat<<EOF

4079 $GF_ARGS</form>

4080 $tail

4081 EOF

4082 }

4083 edittable() {

4084 # $1=form-def $2=table $3 rowid

4085 genform "$@"

4086 }

4087 viewtable() {

4088 GF_VIEWONLY=1 genform "$@"

4089 }

4090 showattc() {

4091 # $1=table_m $2=rowid &optional $3=RawFlag

4092 ## err \$1=$1 \$2=$2 \$3=$3

4093 if ! isfilereadable $user $1 $2; then

4094 contenttype; echo

4095 echo "このファイルは管理者のみしか見られません" | html p

4096 putfooter; exit

4097 fi

4098 idir=`umask 002; mktempd` || exit 1

4099 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4100 bin=$idir/$myname-$$.bin

4101 sql="select quote(bin) from $1 where rowid='$2';"

4102 ## err showattc: sql: $sql

4103 sq $db "$sql" | unhexize > $bin

4104 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4105 type=${tv%//*} fn=${tv#*//}

4106 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4107 ct=${type#file:}

4108 case $ct in # all text/* changed to text/plain

4109 text/*)

4110 charset=`nkf -g $bin|cut -d' ' -f1`

4111 case $charset in

4112 ASCII*) charset="" ;;

4113 esac

4114 if [ -z "$3" ]; then

4115 ct="text/html${charset:+; charset=$charset}"

4116 link="?showattc+$1+$2+raw"

4117 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4118 | sed 's,^,<span></span>,' \

4119 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4120 -D_LINK_="$link" \

4121 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4122 exit $?

4123 fi

4124 ct="text/plain${charset:+; charset=$charset}"

4125 ;;

4126 esac

4127 contenttype "$ct"

4128 echo "Content-Disposition: filename=\"$fn\""

4129 echo "Content-Length: " `cat $bin | wc -c`; echo

4130 #echo "Content-Type: " ${type#file:}; echo

4131 cat $bin

4132 }

4133 #

4134 # Some default stupid handler on CGI values

4135 #

4136 default_storedb() {

4137 # ARG: $1=table-def-file

4138 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4139 tbl=`basename $1`

4140 tbl=${tbl%.def}

4141 cols="`grep :text $1|cut -d: -f2`"

4142 col=`echo "$cols"|head -1`

4143 vcol=`getpar $col`

4144 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4145 if [ -n "$vcol" ]; then

4146 par2table $1

4147 else

4148 return 2 # No insertion occurred

4149 fi

4150 }

4151

4152 default_view() { # $1=def-file

4153 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4154 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4155 default_storedb "$@"

4156 query "select rowid from $tbl order by rowid desc;" \

4157 | while read rowid; do

4158 viewtable $1 $tbl $rowid

4159 done | _m4 -D_TITLE_="$tbl" \

4160 -D_FORM_="`genform $1`" \

4161 -D_DUMPTABLE_="syscmd(cat)" \

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

4163 }

4164 default_viewtext() { # $1=def-file

4165 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4166 default_storedb "$@"

4167 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4168 | _m4 -D_TITLE_="$tbl" \

4169 -D_FORM_="`genform $1`" \

4170 -D_DUMPTABLE_="syscmd(cat)" \

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

4172 }

4173 default_smail() {

4174 default_storedb "$@"

4175 if [ $? -eq 2 ]; then

4176 _m4 -D_TITLE_="入力" \

4177 -D_FORM_="`genform $1`" \

4178 -D_DUMPTABLE_="" \

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

4180 return

4181 fi

4182 cond=""

4183 for pk in `gettblpkey $tbl`; do

4184 pv=$(sqlquote "$(getpar $pk)")

4185 cond="$cond${cond:+ and }$pk=$pv"

4186 done

4187 sql="select rowid from $tbl where $cond;"

4188 rowid=`query "$sql"`

4189 ## err smail1 - "$sql" "-> rowid=$rowid"

4190

4191 while IFS=: read prompt name keytype type args; do # Read from $1

4192 val=`getpar $name`

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

4194 text="$text

4195 $prompt

4196 $name=$val

4197 ---------------------------------------------------------"

4198 fi

4199 case "$type" in

4200 image|document|file)

4201 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4202 fns=$(echo "$fn"|while read fn; do

4203 err mv $tmpd/$fn.orig $tmpd/$fn

4204 mv $tmpd/$fn.orig $tmpd/$fn

4205 rm $tmpd/$fn.rowid # Remove cache flag

4206 ## err "`ls $tmpd/$fn`"

4207 echo $fn

4208 done)

4209 files="$files $fns"

4210 ;;

4211 esac

4212 done < $1

4213 ## err FILES=$files "`ls -lF $tmpd`"

4214 subj="from ${REMOTE_ADDR}"

4215 (echo "$url"

4216 echo "への書き込みがありました。"

4217 echo "------"

4218 echo "$text"

4219 ) | (cd $tmpd &&

4220 err LS="`ls -lF`" &&

4221 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4222 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4223 echo "以下の内容で送信しました。" | html p

4224 viewtable $1 $tbl \

4225 `query "select rowid from $tbl order by rowid desc limit 1;"`

4226 echo "戻る" | html a "href=\"?\""

4227 }