s4: 8a31b90f7815 s4-funcs.sh

s4

view s4-funcs.sh @ 710:8a31b90f7815

Output to error-log when supprcpt is specified in DM rcpt field.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 29 May 2020 06:18:50 +0900
parents	025ef5578ae0
children	695ab5c2c1a5

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 if checkdomain "$a"; then

2739 supprcpt="$supprcpt $a"

2740 else

2741 err "SupprcptErr=[$a] by $user"

2742 removercpt="$removercpt $a"

2743 fi

2744 done

2745 fi

2746 subj=`getpar subject`

2747 afiles=""

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

2749 do

2750 f=$tmpd/$fn

2751 if [ -s $f ]; then

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

2753 fi

2754 done

2755 else

2756 preface=$(cat <<-EOF

2757 $url

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

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

2760 ----------------------------------------------------------

2761 EOF

2762 )

2763 fi

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

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

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

2767 REPLYTO=$replyto

2768 MAIL_FROM=$mail_from

2769 export REPLYTO SMAIL_TO MAIL_FROM

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

2771 for r in $rcpts; do

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

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

2774 else

2775 # Show sender and rcpts address for guest

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

2777 fi

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

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

2780 else

2781 smail "$r" "$subj"

2782 fi <<EOF

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

2784 EOF

2785 done

2786 if [ $? = 0 ]; then

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

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

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

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

2791 html pre<<EOF

2792 `query "$sql"`

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

2794 ${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}

2795 EOF

2796 err SendDone: `echo $sql`

2797 fi

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

2799 grp_reg_adm $grid $usel

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

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

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

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

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

2805 team=$newteam

2806 fi

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

2808 cat<<-EOF | html p

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

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

2811 EOF

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

2813 else

2814 grp_add_team $grid "$team" $usel

2815 fi

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

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

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

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

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

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

2822 grp_rm_team $grid "$rmteam" $usel

2823 else

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

2825 fi

2826 else

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

2828 fi

2829 fi

2830 fi

2831 # POST count summary

2832 from=`getpar from`; to=`getpar to`

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

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

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

2836 # New entry

2837 sql="WITH mems AS (

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

2839 ON gm.user=g.name

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

2841 ), target_article AS (

2842 SELECT id FROM article_s

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

2844 ), posts AS (

2845 SELECT author, count(author) post

2846 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2847 WHERE blogid IN (SELECT id FROM blog_s

2848 WHERE key='owner'

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

2850 AND key='text'

2851 GROUP BY author

2852 ), teams AS (

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

2854 FROM grp_mem_m

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

2856 AND key='team'

2857 GROUP BY user

2858 ), user_post AS (

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

2860 FROM mems m LEFT JOIN posts

2861 ON m.name=posts.author

2862 GROUP by m.rowid

2863 )

2864 SELECT

2865 CASE

2866 WHEN (SELECT user FROM grp_adm

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

2868 AND user=up.name) IS NOT NULL

2869 then 'k'

2870 ELSE ''

2871 END || rowid || ',' || gecos NAME,

2872 post POST, team _TEAM_

2873 FROM user_post up LEFT JOIN teams t

2874 ON up.name=t.user

2875 ORDER BY gecos;"

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

2877 tf=$tmpd/title.$$

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

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

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

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

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

2883 </p></div>"

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

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

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

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

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

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

2890 </div>"

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

2892 allhexteams=$(hexteams "$grp")

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

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

2895 else

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

2897 fi

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

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

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

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

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

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

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

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

2906 </div>

2907 "

2908 fi

2909 stf=$tmpd/selteam.$$

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

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

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

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

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

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

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

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

2918 cgi_form grpaction<<EOF \

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

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

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

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

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

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

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

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

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

2928 <div class="foldtabs">

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

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

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

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

2933 `cgi_datalist_h teams $allhexteams`

2934 </div>

2935 ${rmteammsg}

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

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

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

2939 <div>

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

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

2942 <div class="folded">

2943 <table>

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

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

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

2947 </td></tr>

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

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

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

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

2952 </table>

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

2954 </div>

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

2956 </div>

2957 </div>

2958 ${isowner:+$cmmsg$excmsg}

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

2960 </div>

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

2962 <table class="td2r thl">

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

2964 </table>

2965 `cgi_hidden grp $grid`

2966 `cgi_hidden myuid $myuid id="myuid"`

2967 EOF

2968 }

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

2970 # Create TEMPORARY VIEW

2971 query<<EOF

2972 CREATE TEMPORARY VIEW writeusers AS

2973 SELECT DISTINCT author FROM article

2974 WHERE id in (

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

2976 );

2977 CREATE TEMPORARY VIEW movablegroups AS

2978 SELECT g.rowid growid , g.gname

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

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

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

2982 WHERE (SELECT author FROM writeusers

2983 EXCEPT

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

2985 IS NULL;

2986 $2

2987 EOF

2988 }

2989 sql4readableblogs() {

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

2991 # Blog is readable when:

2992 # 1: blog owner is an user

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

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

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

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

2997 CREATE TEMPORARY VIEW readableblogs AS

2998 SELECT blog.rowid rid, id, author

2999 FROM blog

3000 NATURAL JOIN

3001 (SELECT id,

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

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

3004 FROM blog_s GROUP by id) bs

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

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

3007 WHEN (SELECT val FROM grp_s

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

3009 AND

3010 (SELECT user FROM grp_mem

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

3012 THEN 0

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

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

3015 ELSE 1

3016 END;

3017 EOF

3018 }

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

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

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

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

3023 return

3024 fi

3025 owner=`getvalbyid blog owner $rowid`

3026 title=`getvalbyid blog title $rowid`

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

3028 | _m4 -D_TITLE_="修正" \

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

3030 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

3033 # Move to group

3034 if isuser "$owner"; then

3035 crview4article $rowid

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

3037 ## err N=$n

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

3039 ## err ROWID=$rowid

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

3041 cat<<-EOF

3042 <div class="fold">

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

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

3045 <div>

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

3047 移動先グループ:

3048 <select name="mv2grp">

3049 EOF

3050 query ".mode html"

3051 query<<-EOF |

3052 $sql

3053 .mode list

3054 EOF

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

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

3057 cat<<-EOF

3058 </select>

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

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

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

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

3063 `cgi_hidden blogrowid $rowid`

3064 `cgi_submit 移動`

3065 `cgi_reset Reset`

3066 </form>

3067 </div>

3068 </div>

3069 EOF

3070 fi

3071 # end of isuser "$owner"

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

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

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

3075 cat<<-EOF

3076 <div class="fold">

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

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

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

3080 `query "SELECT

3081 coalesce((SELECT val FROM blog_s

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

3083 AND key='team'),

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

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

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

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

3088 `cgi_hidden blogrowid $rowid`<br>

3089 `cgi_submit 移動`

3090 `cgi_reset Reset`

3091 </form></div></div>

3092 EOF

3093 fi

3094 }

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

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

3097 blogrowid=`getpar blogrowid`

3098 cfm=`getpar cfm`

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

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

3101 . ./s4-blog.sh

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

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

3104 return

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

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

3107 elif { mv2grp=`getpar mv2grp`

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

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

3110 crview4article $blogrowid

3111 ########## TRANSACTION BEGIN

3112 query "BEGIN;"

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

3114 ## err Nwriteuser=$n

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

3116 query<<-EOF

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

3118 WHERE key='owner'

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

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

3121 EOF

3122 fi

3123 query "END;"

3124 ########## TRANSACTION END

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

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

3127 # blog owner can move it to ANY team

3128 case "$mv2team" in

3129 'なし')

3130 cat<<-EOF

3131 DELETE FROM blog_s

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

3133 AND key='team';

3134 EOF

3135 ;;

3136 "") ;;

3137 *)cat<<-EOF

3138 BEGIN;

3139 REPLACE INTO blog_s(id, key, val)

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

3141 'team', '$mv2team');

3142 REPLACE INTO blog_s(id, key, val)

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

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

3145 END;

3146 EOF

3147 esac | query

3148 fi

3149 blog_reply $blogrowid

3150 echo yes | html p

3151 }

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

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

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

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

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

3157 return

3158 fi

3159 owner=`getvalbyid blog owner $blogrowid`

3160 title=`getvalbyid blog title $blogrowid`

3161 author=`getvalbyid article author $rowid`

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

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

3164 : EDIT OK

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

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

3167 return

3168 fi

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

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

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

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

3173 > $tmpout

3174 rm -f /tmp/editart.out

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

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

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

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

3179 -D_BLOGS_= -D_DUMPTABLE_= \

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

3181 }

3182 send2mem() {

3183 rowid=`getpar grp`

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

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

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

3187 return

3188 fi

3189 message=`getpar message`

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

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

3192 return

3193 fi

3194 grp=`getgroupbyid $rowid`

3195 members=`collectemail $grp`

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

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

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

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

3200 msg=$(cat<<-EOF

3201 $urlbase?grp+$rowid

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

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

3204

3205 $message

3206 EOF

3207 )

3208 # smail rcpt subj (file)

3209 for m in $members; do

3210 echo "$msg" |

3211 MAIL_FROM=$mailfrom \

3212 SENDER=$noreply \

3213 REPLYTO=$mailfrom \

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

3215 smail "$m" "$sj"

3216 done

3217 cat<<EOF

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

3219 <pre>

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

3221 </pre>

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

3223 EOF

3224 }

3225 joingrpadmit() {

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

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

3228 echo "bye bye" | html p; return

3229 fi

3230 t_usr=`session=$2 getpar user`

3231 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

3238 return

3239 fi

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

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

3242 fi

3243 case $1 in

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

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

3246 *)

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

3248 return ;;

3249 esac

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

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

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

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

3254 に

3255 $t_usr

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

3257 しました。"

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

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

3260 echo "$body" | html p

3261 }

3262

3263 joingrprequest() {

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

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

3266 addsession $jss +${memoplimitdays}days

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

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

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

3270 $url

3271 $user さんから

3272 グループ $1

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

3274

3275 承認する:

3276 $urlbase?joingrpadmit+yes+$jss

3277

3278 白紙に戻す:

3279 $urlbase?joingrpadmit+no+$jss

3280 EOF

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

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

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

3284 }

3285 joingrp() {

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

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

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

3289 isowner="yes"

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

3291 isowner="yes"

3292 else

3293 isowner=""

3294 fi

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

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

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

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

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

3300 return

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

3302 : # Do not pursue those who leave

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

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

3305 else # adding user is $user itself

3306 case `getgroupattr $1 regmode` in

3307 moderated)

3308 joingrprequest "$@" # Request only

3309 return

3310 ;;

3311 *)

3312 ;;

3313 esac

3314 fi

3315 qgname=`sqlquote "$1"`

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

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

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

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

3320 # Notify joingrp to admin

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

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

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

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

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

3326 else

3327 echo $msg

3328 fi

3329 else

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

3331 fi

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

3333 # Coming here means newly created group

3334 sql="select case\

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

3336 then (select user from grp_mem\

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

3338 else '' end; "

3339 err NewGrpChk: $sql

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

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

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

3343 fi

3344 fi

3345 else

3346 query "begin;

3347 delete from grp_mem $cond;

3348 delete from grp_mem_s $cond;

3349 delete from grp_mem_m $cond;

3350 delete from grp_adm $cond;

3351 delete from grp_adm_s $cond;

3352 delete from grp_adm_m $cond;

3353 end;"

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

3355 fi

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

3357 $url?grp+$grid

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

3359 グループ $1

3360 $action

3361 EOF

3362 }

3363 grp_add_team() (

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

3365 grp=`getgroupbyid $1`

3366 team=$2; shift; shift

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

3368 { echo "BEGIN;"

3369 for user; do

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

3371 '$grp',\

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

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

3374 done

3375 echo "END;"

3376 } | query

3377 )

3378 grp_rm_team() (

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

3380 grid=$1

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

3382 team=$2; shift; shift

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

3384 { echo "BEGIN;"

3385 for user; do

3386 echo "DELETE FROM grp_mem_m\

3387 WHERE gname=$qgrp \

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

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

3390 done

3391 cat<<-EOF

3392 DELETE FROM blog_s

3393 WHERE rowid=(

3394 SELECT rowid

3395 FROM blog_s a

3396 WHERE key='team'

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

3398 AND NOT EXISTS (SELECT * FROM grp_mem_m

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

3400 AND gname = (SELECT val FROM blog_s b

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

3402 ));

3403 EOF

3404

3405 echo "END;"

3406 } | query

3407 )

3408 grp_reg_adm() {

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

3410 grid=$1

3411 grp=`getgroupbyid "$1"`

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

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

3414 fi

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

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

3417 fi

3418 shift

3419 for urid; do

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

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

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

3423 fi

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

3425 err ismember $newadm $grp

3426 if ismember $newadm "$grp"; then

3427 # OK, go ahead

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

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

3430 # confirm insertion

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

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

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

3434 else

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

3436 fi

3437 fi

3438 showgroup $grid

3439 done

3440 }

3441 dt_rowhack() {

3442 # From: <TR>

3443 # ....

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

3445 # </TR>

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

3447 sed -e '

3448 /^<TR>/ {

3449 :loop

3450 s/\n//

3451 N

3452 /<\/TR>/ {

3453 s/\n//

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

3455 n

3456 }

3457 $q

3458 b loop

3459 }'

3460 }

3461 dumptable() {

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

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

3464 # $DT_VIEW sets link

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

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

3467 VIEW=${DT_VIEW-replyblog}

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

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

3470 fi

3471 sqlfile=$tmpd/dump.sql

3472 : > $sqlfile # ensure to be empty

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

3474 # $DT_CHLD=ChildTable:BindColumn

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

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

3477 cat<<-EOF >> $sqlfile

3478 -- presql

3479 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

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

3481 EOF

3482 # Speed up counting of new articles

3483 cat<<-EOF >> $sqlfile

3484 -- presql2

3485 DROP TABLE IF EXISTS _counts;

3486 CREATE TEMPORARY TABLE _counts AS

3487 SELECT $_i, count($_i) cnt

3488 FROM $_t GROUP BY $_i;

3489 /* Prepare NEW count table */

3490 CREATE TEMPORARY TABLE _target AS

3491 SELECT b.rowid trowid, b.id

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

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

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

3495

3496 DROP TABLE IF EXISTS _children;

3497 CREATE TEMPORARY TABLE _children AS

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

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

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

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

3502

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

3504 DROP TABLE IF EXISTS _news;

3505 DROP VIEW IF EXISTS _news;

3506

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

3508 -- INSERT INTO _news

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

3510 CREATE TEMPORARY VIEW _news AS

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

3512 FROM (SELECT DISTINCT id FROM _target)

3513 a LEFT JOIN

3514 (SELECT $_i, count(ctime) newcnt

3515 FROM _children x

3516 WHERE ctime > coalesce((SELECT time from myacclog

3517 WHERE tblrowid=x.trowid),

3518 '1970-01-01')

3519 GROUP BY $_i) b

3520 ON a.id=b.$_i;

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

3522 EOF

3523 # REMOVE next line until 2019/5/1

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

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

3526 # REMOVE next assignment until 2019/5/1

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

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

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

3530 tblrowid=a.rowid),\

3531 '1970-01-01'))"

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

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

3534 dt_class=" td2r td3r dumpblogs"

3535 fi

3536 # Construct join expression

3537 eav="" scols=""

3538 pk=`gettblpkey $2`

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

3540 substr=${substr:-%s}

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

3542 valvar=val

3543 case $col in

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

3545 continue ;; # built-in column name

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

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

3548 case "$as" in

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

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

3551 h=`sqlquotestr "$h"`

3552 v=`sqlquotestr "$v"`

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

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

3555 esac

3556 ;;

3557 *) as=${col} ;;

3558 esac

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

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

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

3562 done

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

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

3565 echo "$DT_SQL"

3566 else

3567 cat<<-EOF

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

3569 FROM $2 a LEFT JOIN

3570 (SELECT $pk,$eav,

3571 max(CASE key

3572 WHEN 'owner'

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

3574 as gecos

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

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

3577 EOF

3578 fi >> $sqlfile

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

3580 sqlog<<-EOF

3581 *** SQL-file: $sqlfile ***

3582 `cat $sqlfile`

3583 EOF

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

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

3586 cat $sqlfile >> tmp/sql

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

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

3589 cp $tmpd/foo tmp/

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

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

3592 fi

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

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

3595 <div>

3596 <div class="dumptable">

3597 <table class="b$dt_class">

3598 `query ".read $sqlfile"`

3599 </table>

3600 </div>

3601 </div>

3602 EOF

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

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

3605

3606 ## REMOVE THIS!

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

3608 }

3609

3610 par2table() (

3611 # copy current parameters of par into destination table

3612 # $1=definition-file

3613 # Using $user and $session

3614 # Return value:

3615 # 0: Stored successfully

3616 # 1: Insufficient fillings

3617 # 2: No permission to modify the record

3618 # 3: Invalid rowid

3619 # 4: SUCCESS to delete

3620 # 5: Stop deletion for lack of confirm check

3621 # 6: Password length too short

3622 # 7: Password mismatch

3623 # 8: Old password incorrect

3624 rowid=`getpar rowid`

3625 if [ ! -e $1 ]; then

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

3627 exit 1

3628 fi

3629 tbl=${1%.def}

3630 tbl=${tbl##*/}

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

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

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

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

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

3636 ##err p2t:grp:q $sql

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

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

3639 # Check if owner in blog_s

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

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

3642 rowowner=$user

3643 else

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

3645 fi

3646 else

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

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

3649 fi

3650 ### err rowowner=$rowowner

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

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

3653 return 2

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

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

3656 return 3

3657 fi

3658 rm=`getpar rm` cfm=`getpar confirm`

3659 # Editing existent entry

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

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

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

3663 return 4

3664 else

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

3666 return 5

3667 fi

3668 fi

3669 fi

3670

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

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

3673 # Update of existing record

3674 for col in `gettblcols $tbl`; do

3675 val=`getparquote $col`

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

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

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

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

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

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

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

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

3684 ## of blog_s table.

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

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

3687 cat<<-EOF | query

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

3689 -- should use EXCLUSIVE transaction outside of this.

3690 SAVEPOINT par2table;

3691 $sql

3692 update blog_s set val=$val

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

3694 RELEASE SAVEPOINT par2table;

3695 EOF

3696 ## XX: DIRTY Hack Ends here

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

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

3699 else

3700 query "$sql"

3701 fi

3702 done

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

3704 for col in `gettblpkey $tbl`; do

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

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

3707 done

3708 else

3709 # New entry

3710 # Generate values() for primary keys

3711 for col in `gettblpkey $tbl`; do

3712 # Genuine primary keys for _m and _s

3713 val=`getvalquote $tbl $col`

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

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

3716 done

3717 ##err pval=$pval

3718 for col in `gettblfkey $tbl`; do

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

3720 val=`getvalquote $tbl $col`

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

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

3723 done

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

3725 ## err formaster=$formaster

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

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

3728 return 1

3729 fi

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

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

3732 ## Insertion to master table, done

3733 fi

3734

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

3736 for kt in s m; do

3737 tb2=${tbl}_$kt

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

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

3740

3741 # First, check update of existing entries in _m

3742 if [ $kt = m ]; then

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

3744 sql=""

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

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

3747 # v=address.1.22

3748 st_rowid=${v##*.}

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

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

3751 ##case `getpartype $v` in

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

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

3754 case `getpar action.$v` in

3755 rm)

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

3757 newsql="delete from $tb2"

3758 else

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

3760 fi ;;

3761 edit)

3762 case `gettbl_coltype $tbl/$origcol` in

3763 image|document|binary)

3764 file=$tmpd/`getparfilename $v`

3765 ## err type=file=$file

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

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

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

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

3770 type=\"file:$ct\"

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

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

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

3774 rm -rf $cachedir

3775 ;;

3776 *)

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

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

3779 ;;

3780 esac

3781 ;;

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

3783 continue

3784 ;;

3785 esac

3786 # err newsql=$newsql

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

3788 done

3789

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

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

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

3793 cat<<-EOF >> $transaction

3794 $sql

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

3796 EOF

3797 else

3798 query "$sql

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

3800 ## err repl:normal done

3801 fi

3802 else

3803 # Binary update line is TOO LONG to pipelining

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

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

3806 echo "$sql" >> $transaction

3807 else

3808 echo "$sql" > $sqlfile

3809 query ".read $sqlfile"

3810 fi

3811 fi

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

3813 nr=`getparcount $col`

3814 else

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

3816 fi

3817

3818 i=0

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

3820 limit="limit 1 offset $i"

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

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

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

3824 ## err $col=$val

3825 bin=NULL

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

3827 case $ptype in

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

3829 ## err parfile-$col=$file

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

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

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

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

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

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

3836 esac

3837 case `gettbl_coltype $tbl/$col` in

3838 password) # special care for password

3839 # name={password,pswd1,pswd2}

3840 p1=`getpar pswd1 "$limit"`

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

3842 continue # SKIP password setting, if p1 is empty

3843 else

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

3845 ## err pswd=$pswd

3846 if pwcheck "$pswd"; then

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

3848 case "$p1" in

3849 ??????????*) ;;

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

3851 return 6;;

3852 esac

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

3854 else

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

3856 return 7

3857 fi

3858 else

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

3860 return 8

3861 fi

3862 fi

3863 ;;

3864 esac

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

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

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

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

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

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

3871 echo "$sql" >> $transaction

3872 else

3873 query "$sql"

3874 fi

3875 else

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

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

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

3879 echo "$sql" >> $transaction

3880 else

3881 echo "$sql" > $sqlfile

3882 query ".read $sqlfile"

3883 fi

3884 fi

3885 ## err p2t done

3886 done

3887 done

3888 done

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

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

3891 SAVEPOINT pa2table_insert;

3892 .read $transaction

3893 RELEASE SAVEPOINT pa2table_insert;

3894 EOF

3895 return $?

3896 ##err donee

3897 )

3898 genform() {

3899 # $1 = form definition file

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

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

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

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

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

3905 forms="" hiddens="" rowid=$3

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

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

3908 return

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

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

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

3912 pk=`gettblpkey $2`

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

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

3915 rowid=$rec

3916 rec=$3

3917 fi

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

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

3920 return

3921 fi

3922 fi

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

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

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

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

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

3928 fi

3929 # Image Cache dir

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

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

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

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

3934 sp="${args:+ }"

3935 form="" val=""

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

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

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

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

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

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

3942 fi

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

3944 is_hidden "$2" "$name" && continue

3945 fi

3946 case "$type" in

3947 text*)

3948 cgiform=cgi_multi_$type

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

3950 form=`$cgiform $name $td`

3951 val=$(echo "$val"|

3952 while read fn; do

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

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

3955 done)

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

3957 else

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

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

3960 fi

3961 ;;

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

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

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

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

3966 ;;

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

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

3969 ;;

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

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

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

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

3974 done)

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

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

3977 fi

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

3979 ;;

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

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

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

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

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

3985 val=$(echo "$rawval" \

3986 | while read fn; do

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

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

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

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

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

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

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

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

3995 done)

3996 fi

3997 else

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

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

4000 imgs=$(echo "$rawval"\

4001 |while read fn;do

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

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

4004 done)

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

4006 val=$imgs # 2015-06-15

4007 else

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

4009 fi

4010 fi

4011 ;;

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

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

4014 args="value=\"$GF_STAGE\""

4015 fi

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

4017 prompt='' # Remove prompt

4018 ;;

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

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

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

4022 prompt="" ;;

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

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

4025 val=${GF_OWNER:-$val}

4026 val=${val:-$user}

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

4028 prompt="" ;;

4029 [Uu][Ss][Ee][Rr])

4030 # XXX: is null $user ok?

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

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

4033 form="$user"

4034 ;;

4035 [Pp]assword)

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

4037 form="`cgi_passwd`"

4038 val=""

4039 ;;

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

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

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

4043 val=`genserial`

4044 fi

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

4046 prompt="" ;;

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

4048 prompt=""

4049 ;;

4050 parent|path|blog*)

4051 prompt=""

4052 ;;

4053 "*"*)

4054 tail=$tail"``"

4055 continue ;;

4056 esac

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

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

4059 form=$val

4060 else

4061 :

4062 fi

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

4064 else

4065 hiddens=$hiddens$nl"$form"

4066 fi

4067 done < $1

4068 # enctype="multipart/form-data"

4069 cat<<EOF

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

4071 ${rowid:+$rm}

4072 <table class="b $2">

4073 $forms

4074 </table>$hiddens

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

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

4077 EOF

4078 if [ -z $GF_VIEWONLY ]; then

4079 cat<<EOF

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

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

4082 EOF

4083 fi

4084 cat<<EOF

4085 $GF_ARGS</form>

4086 $tail

4087 EOF

4088 }

4089 edittable() {

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

4091 genform "$@"

4092 }

4093 viewtable() {

4094 GF_VIEWONLY=1 genform "$@"

4095 }

4096 showattc() {

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

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

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

4100 contenttype; echo

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

4102 putfooter; exit

4103 fi

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

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

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

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

4108 ## err showattc: sql: $sql

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

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

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

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

4113 ct=${type#file:}

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

4115 text/*)

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

4117 case $charset in

4118 ASCII*) charset="" ;;

4119 esac

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

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

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

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

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

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

4126 -D_LINK_="$link" \

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

4128 exit $?

4129 fi

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

4131 ;;

4132 esac

4133 contenttype "$ct"

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

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

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

4137 cat $bin

4138 }

4139 #

4140 # Some default stupid handler on CGI values

4141 #

4142 default_storedb() {

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

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

4145 tbl=`basename $1`

4146 tbl=${tbl%.def}

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

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

4149 vcol=`getpar $col`

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

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

4152 par2table $1

4153 else

4154 return 2 # No insertion occurred

4155 fi

4156 }

4157

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

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

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

4161 default_storedb "$@"

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

4163 | while read rowid; do

4164 viewtable $1 $tbl $rowid

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

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

4167 -D_DUMPTABLE_="syscmd(cat)" \

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

4169 }

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

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

4172 default_storedb "$@"

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

4174 | _m4 -D_TITLE_="$tbl" \

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

4176 -D_DUMPTABLE_="syscmd(cat)" \

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

4178 }

4179 default_smail() {

4180 default_storedb "$@"

4181 if [ $? -eq 2 ]; then

4182 _m4 -D_TITLE_="入力" \

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

4184 -D_DUMPTABLE_="" \

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

4186 return

4187 fi

4188 cond=""

4189 for pk in `gettblpkey $tbl`; do

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

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

4192 done

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

4194 rowid=`query "$sql"`

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

4196

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

4198 val=`getpar $name`

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

4200 text="$text

4201 $prompt

4202 $name=$val

4203 ---------------------------------------------------------"

4204 fi

4205 case "$type" in

4206 image|document|file)

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

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

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

4210 mv $tmpd/$fn.orig $tmpd/$fn

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

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

4213 echo $fn

4214 done)

4215 files="$files $fns"

4216 ;;

4217 esac

4218 done < $1

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

4220 subj="from ${REMOTE_ADDR}"

4221 (echo "$url"

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

4223 echo "------"

4224 echo "$text"

4225 ) | (cd $tmpd &&

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

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

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

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

4230 viewtable $1 $tbl \

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

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

4233 }