s4: b036a06cad90 s4-funcs.sh

s4

view s4-funcs.sh @ 698:b036a06cad90

Make sure to chmod o-r

author	HIROSE Yuuji <yuuji@gentei.org>
date	Thu, 21 May 2020 23:35:27 +0900
parents	4461d596150d
children	b33d6e7730ac

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 SELECT * FROM $sesstb;

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

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

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

384 .output stdout

385 EOF

386 }

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

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

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

390 fi

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

392 }

393 cleanup() {

394 trap '' INT HUP EXIT TERM PIPE

395 echo .quit >&5

396 kill $sq3pid

397 kill $sq3pid

398 rm -f $sqo $sqi

399 rm -rf $tmpfiles

400 cleanup2

401 }

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

403 # simple redirection for the sake of speed.

404 query() {

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

406 echo ".output $sqo" >&5

407 logstart

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

409 tee -a $querylog

410 else

411 echo "$@" >> $querylog

412 echo "$@"

413 fi >&5

414 echo ".output stdout" >&5

415 cat $sqo

416 logend

417 }

418 _m4() {

419 #S4NAME=f,f,f

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

421 }

422 ismember() {

423 # $1=user, $2=group

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

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

426 }

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

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

429 }

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

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

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

433 }

434 isgrpowner() (

435 # $1=user, $2=group

436 gn=`sqlquote "$2"`

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

438 err isgrpowner: $sql

439 test -n "`query $sql`"

440 )

441 isgrpownerbygid() (

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

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

444 err isgrpownerbygid: $sql

445 test -n "`query $sql`"

446 )

447 getgroupadminmails() {

448 # $1=group

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

450 email4group "$1" "$i" ;

451 done

452 }

453 getgroupadmins() { # $1=group

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

455 qgrp=`sqlquote "$1"`

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

457 }

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

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

460 getvalbyid grp $2 \

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

462 }

463 getgroupbyid() {

464 # $1=id|gname

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

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

467 # err ggbyid: `echo $sql`

468 query $sql

469 }

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

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

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

473

474 # Return true when anonymous mode

475 [ "$anonymousmode" ] && return 0

476 # case `getvalbyid blog mode $2` in

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

478 # *closed*)

479 # owner=`getvalbyid blog owner $2`

480 # if isgrp $owner; then

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

482 # elif isuser $owner; then

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

484 # fi

485 # esac

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

487 sql="with getblog as (

488 select key,val from blog_s where id=(

489 select blogid from article where id in

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

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

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

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

494 isgrpadm as (select user from grp_adm where

495 gname=(select val from getowner) and

496 user='$1'),

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

498 select case

499 when (select author from article where

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

501 then 'author'

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

503 then 'open'

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

505 then CASE

506 WHEN (SELECT val FROM isgrp) IS NULL

507 THEN

508 CASE WHEN (SELECT val from getowner)

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

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

511 ELSE ''

512 END

513 WHEN (select user from isgrpadm) IS NOT NULL

514 THEN 'i-am-admin'

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

516 END

517 when (select val from getmode) is null

518 then 'open'

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

520 then (SELECT user FROM isgrpadm)

521 when (select author from article where

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

523 then 'user+author'

524 else '' end;"

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

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

527 # result=`query "$sql"`

528 # err FileAccessibility=$result

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

530 }

531 linkhome() {

532 # $1=UserOrGroup

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

534 if isuser $1; then

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

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

537 else

538 echo -n "grp+$1"

539 fi

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

541 }

542 hreflink() {

543 # s4 specific notation:

544 # ^href=URL

545 # ^iframe=URL

546 # ^video=URL

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

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

549 # OSM umap Wikistyle Notation:

550 # [[URL]] - Simle Link

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

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

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

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

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

556 # Other Style

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

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

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

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

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

562 # SPC+SPC+$ - <br>

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

564 checkboxON="${cb} checked>"

565 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

590

591 }

592 minitbl() {

593 sed -n '

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

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

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

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

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

599 H; # Concat this line to HoldSpace

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

601 $ b cont

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

603 }

604 :cont

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

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

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

608 # . of ^. is workaround for FreeBSD sed

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

610 p; # Print whole "table" element

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

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

613 }

614 x; # Back to the newest line

615 p; # Print rest' | miniul

616 }

617 miniul() {

618 sed -e '

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

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

621 :top

622 s/\n//;

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

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

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

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

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

628 $ b cont

629 N; # 次の行を読む

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

631 /^ /b top

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

633 p; s/.*//;

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

635 /^\* /b top

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

637 }

638

639 :cont

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

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

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

643 H; x

644 }

645 x'

646 }

647 acclog() (

648 # $1=table, $2=rowid

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

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

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

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

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

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

655 fi

656 )

657 gecos() (

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

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

660 )

661 setpar() {

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

663 case "$1" in

664 user) _user="$v"; err SETPARRRRR:user=$v ;;

665 skey) _skey="$v"; err SETPARRRRR:skey=$v ;;

666 esac

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

668 }

669 replpar() {

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

671 }

672 getpar() {

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

674 val=""

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

676 user) val=$_user

677 err GETPAR____USER=$val;;

678 skey) val=$_skey; err SKEY=$val ;;

679 esac

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

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

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

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

684 fi

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

686 case "$var" in

687 owner)

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

689 echo $user; return

690 elif ismember $user $val; then

691 echo $val; return

692 fi ;;

693 esac

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

695 echo "$val"

696 }

697 setskey() {

698 # For quick response...(?)

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

700 }

701 chkskey() {

702 # $1=sesskey, $user=LoginUserName

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

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

705 err CHKskey:rowid=$rowid

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

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

708 return 0

709 fi

710 return 1

711 }

712 resetskey() {

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

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

715 fi

716 }

717 getpartype() {

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

719 }

720 getparcount() {

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

722 }

723 getparfilename() {

724 # null if type of $1 is not file

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

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

727 }

728 sqlquote() {

729 (v="$1"

730 case "$v" in

731 "") return ;; # null

732 "X'"*) # quoted hex string

733 echo $1 ;;

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

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

736 echo "\"$v\""

737 return ;;

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

739 echo "\"$v\""

740 return ;;

741 *)

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

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

744 else

745 echo "\"$v\""

746 fi ;;

747 esac)

748 }

749 sqlquotestr() (

750 case "$1" in

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

752 echo "'$v'" ;;

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

754 esac

755 )

756 mktempd() {

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

758 }

759 getcachedir() { # $1=maintable

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

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

762 else

763 echo $tmpd/$thumbxy

764 fi

765 }

766 getval() {

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

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

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

770 echo "$user" ;;

771 stamp|datetime)

772 date "+%F %T" ;;

773 serial)

774 (s=`getpar $2`

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

776 *)

777 getpar "$2" "$3";;

778 esac

779 }

780

781 getvalquote() {

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

783 (v=`getval "$@"`

784 case "$v" in

785 "") echo NULL ;;

786 *) sqlquote "$v" ;;

787 esac)

788 }

789 getparquote() {

790 sqlquote "`getpar $1`"

791 }

792 getbinbyid() {

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

794

795 }

796 getvalbyid() {

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

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

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

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

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

802

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

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

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

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

807 return

808 fi

809 done

810 rowid=$3

811 pk=`gettblpkey $1`

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

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

814 td=${4:-$tmpd}

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

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

817 for kt in s m; do

818 t=${1}_$kt

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

820 vcount=1 # count(val)

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

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

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

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

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

826 if [ $kt = m ]; then

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

828 # Reset val to store filenames if type is string

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

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

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

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

833 i=0

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

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

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

837 i=$((i+1))

838 fn=$c.$i

839 err td=$td, fn=$fn, type=$type, val="[$val]"

840 case $type in

841 file:*)

842 #file=$td/$val

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

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

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

846 # FOR SPEED: Skip file generation if imgcache exists

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

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

849 && continue

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

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

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

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

854 select rowid from $cond $slice;

855 .output '$td/$fn'

856 select val from $cond $slice;

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

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

859 .output stdout

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

861 EOF

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

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

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

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

866 ls -lh "$file" |

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

868 case "$type" in

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

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

871 *|*:[Aa]pplication*)

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

873 png:- > "$file"

874 ;;

875 esac

876 ;;

877 *)

878 sq $db<<EOF

879 .output $td/$fn.rowid

880 select rowid from $cond $slice;

881 .output $td/$fn

882 select val from $cond $slice;

883 EOF

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

885 ;;

886 esac

887 done

888 else

889 rm -f $td/$c.count

890 case $type in

891 file:*)

892 echo "$val" \

893 | while read fn; do

894 file=$td/$fn

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

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

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

898 | unhexize > "$file"

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

900 case $type in

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

902 *:[Aa]pplication*)

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

904 png:- > $file ;;

905 esac

906 fi

907 done

908 ;;

909 esac

910 fi

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

912 return

913 fi

914 done

915 done)

916 }

917 getvalbypkey() (

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

919 pk=`gettblpkey $1`

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

921 getvalbyid "$1" "$2" $rowid $4

922 )

923 getvalbycond() {

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

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

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

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

928 getvalbyid "$1" "$2" $rowid "$4"

929 fi

930 }

931 getpwfield() {

932 # getpwfield user column

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

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

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

936 echo "$val"

937 return 0

938 else

939 return 1

940 fi

941 }

942 numericalize() {

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

944 }

945 encode() {

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

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

948 sha1=sha1

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

950 sha1=sha1sum

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

952 sha1=gsha1sum

953 fi

954 fi

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

956 }

957 enjpeg() {

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

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

960 cjpeg="cjpeg"

961 else

962 cjpeg="convert - jpeg:-"

963 fi

964 fi

965 $cjpeg "$@"

966 }

967 mycrypt() (

968 key=$1 salt=$2

969 # err \$2=$2

970 case $2 in

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

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

973 esac

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

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

976 )

977 hexize() {

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

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

980 hexize="xxd -p"

981 else

982 hexize_hd() {

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

984 }

985 hexize="hexize_hd"

986 fi

987 fi

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

989 }

990 unhexize() {

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

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

993 unhex="xxd -p -r"

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

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

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

997 EOF

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

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

1000 # world writable...:)

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

1002 fi

1003 fi

1004 cat "$@" | $unhex

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

1006 }

1007 percenthex() {

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

1009 }

1010 htmlescape() {

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

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

1013 }

1014 enascii() {

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

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

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

1018 else

1019 enascii_now=`date +%FT%T`

1020 enascii_sed() {

1021 nkf -Z0Z1Z2 \

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

1023 }

1024 enascii="enascii_sed"

1025 fi

1026 fi

1027 cat "$@" | $enascii

1028 }

1029 size_h() {

1030 i="$1" oi=$1

1031 set -- B B KB MB GB TB

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

1033 oi=$i

1034 i=$((i/1024))

1035 shift

1036 done

1037 echo ${oi}$1

1038 }

1039 gettblconf() {

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

1041 ## tconfs=`sq $db \

1042 tconfs=`query \

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

1044 fi

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

1046 }

1047 gettblkeys() {

1048 # $1=tbl

1049 gettblconf

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

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

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

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

1054 case $type in

1055 *p*)

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

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

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

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

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

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

1062 esac

1063 case $type in

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

1065 esac

1066 done

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

1068 }

1069 gettblpkey() {

1070 # $1=tbl

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

1072 }

1073 gettblfkey() {

1074 (x=`gettblkeys $1`

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

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

1077 }

1078 gettblcols() {

1079 (x=`gettblkeys $1`

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

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

1082 }

1083 gettbl_s_cols() {

1084 (x=`gettblkeys $1`

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

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

1087 }

1088 gettbl_m_cols() {

1089 (x=`gettblkeys $1`

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

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

1092 }

1093 gettbl_h_cols() {

1094 (x=`gettblkeys $1`

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

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

1097 }

1098 gettbl_coltype() (

1099 gettblconf

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

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

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

1103 )

1104 is_hidden() {

1105 # $1=Tbl $2=col

1106 gettblconf

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

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

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

1110 case $x in

1111 *h*) return 0 ;;

1112 *) return 1 ;;

1113 esac

1114 }

1115

1116 dbsetbyid() {

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

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

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

1120 quotedp=$(sqlquotestr "$p")

1121 unset primary update

1122 gettblconf

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

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

1125 #err conf=$conf

1126 case ${conf#*=} in

1127 p*) primary=1 ;;

1128 f*) update=1 ;;

1129 u*) ;;

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

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

1132 esac

1133 #err t=$t

1134 type=string fn=""

1135 case $conf in

1136 */password)

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

1138 ;;

1139 */image*|*/document*)

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

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

1142 ;;

1143 esac

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

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

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

1147 if [ "$primary" ]; then

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

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

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

1151 elif [ "$update" ]; then

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

1153 else

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

1155 fi

1156 )

1157 }

1158 expire() (

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

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

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

1162 )

1163 addsession() {

1164 # expireをセット

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

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

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

1168 # Remove old session parameters

1169 now=`expire now`

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

1171 }

1172 gencookie() (

1173 for kv; do

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

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

1176 done

1177 )

1178 contenttype() {

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

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

1181 }

1182 putheader() {

1183

1184 }

1185 putfooter() {

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

1187 }

1188 getcookie() {

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

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

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

1192 case "$k" in

1193 user) _user="$v"; ;; ## err FromCookie:user=$_user ;;

1194 skey) _skey="$v"; ;; ## err FromCookie:skey=$_skey ;;

1195 esac

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

1197 done

1198 }

1199 genrandom() {

1200 # $1=columns (default: 10)

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

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

1203 }

1204 genserial() {

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

1206 }

1207 smail() {

1208 # smail rcpts subj (file)

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

1210 # $MAIL_FROM <- From: header value

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

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

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

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

1215 sender=${SENDER:-$admin}

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

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

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

1219 }

1220 smail_queue_flush() {

1221 # $1=timelimit

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

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

1224 SELECT rowid FROM smailq

1225 GROUP BY rcpts, subj

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

1227 EOF

1228 )

1229 for rid in $rowids; do

1230 sq $workdb \

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

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

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

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

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

1236

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

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

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

1240 .separator "\n" "------------------\n\n"

1241 SELECT time, text FROM smailq

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

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

1244 ORDER by time;

1245 EOF

1246 then

1247 echo rowid=$rid

1248 cat <<-EOF | sq $workdb

1249 DELETE FROM smailq

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

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

1252 EOF

1253 fi

1254 fi

1255 done

1256 }

1257 smail_queue() {

1258 # $1=Rcpts, $@=subj

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

1260 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1269 EOF

1270 )

1271 ## XXX: Adhoc load mitigation

1272 case "$now" in

1273 *[01])

1274 err flush_queue=$mintime

1275 smail_queue_flush ;;

1276 esac

1277 fi

1278 }

1279 setviastring() {

1280 table=par

1281 oifs="$IFS"

1282 IFS="&"

1283 for us in $1; do

1284 k=${us%%=*}

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

1286 setpar "$k" "string" "$v"

1287 done

1288 IFS="$oifs"

1289 }

1290 checkdomain() (

1291 # Check the validity of domain by referring DNS

1292 item=$1

1293 err checkdomain $1

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

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

1296 )

1297 pwcheck() {

1298 # $1=passwd

1299 dbpswd=`getpwfield $user pswd`

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

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

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

1303 }

1304 mypwhash() {

1305 mycrypt "`cat`" `genrandom 5`

1306 }

1307 wasureta() {

1308 user=$1

1309 if ! checkdomain $user; then

1310 contenttype; echo

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

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

1313 putfooter

1314 exit 0

1315 fi

1316 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1318 encpswd=`echo $newpswd|mypwhash`

1319 dbsetbyid user $user pswd "$encpswd"

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

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

1322 $msgdir/mail-newaccount.m4 \

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

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

1325 }

1326 checkauth() {

1327 user=`getpar user`

1328 skc=`getpar skey` # from cookie

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

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

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

1332 if chkskey "$skc"; then

1333 return 0

1334 fi

1335 fi

1336 pswd=`getpar pswd`

1337 quser=`sqlquotestr "$user"`

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

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

1340 return 4 # 4=server too heavy

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

1342 return 2 # 2=login fail

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

1344 wasureta "$user"

1345 return 1 # wasureta error

1346 fi

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

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

1349 if pwcheck "$pswd"; then

1350 newsession=`genrandom 50`

1351 if setskey "$user" "$newsession" &&

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

1353 gencookie "user=$user" "skey=$newsession"

1354 return 0

1355 else

1356 return 4 # Heavy load??

1357 fi

1358 fi

1359 return 2 # Password mismatch

1360 }

1361 showlogin() {

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

1363 test -z "$args" && resetskey

1364 s4name=${S4NAME:-s4}

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

1366 cat $layout/login.m4.html

1367 echo '</body></html>'

1368 ) | _m4 \

1369 -D_BODYCLASS_="login" \

1370 -D_TITLE_="$s4name" \

1371 -D_SYSNAME_="Welcome to $s4name" \

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

1373 exit 0

1374 }

1375 dologin() {

1376 checkauth

1377 st=$?

1378 if [ $st != 0 ]; then

1379 contenttype; echo

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

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

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

1383 showlogin # and EXIT

1384 fi

1385 }

1386

1387 # Do instant jobs here

1388 dbsetup

1389 trap cleanup INT HUP EXIT TERM PIPE

1390 # trap cleanup INT HUP

1391

1392 err() {

1393 echo "$@" 1>&3

1394 }

1395

1396 cgiinit() {

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

1398 tmpf=tmp/stream

1399 tmpd=`tmpd=$tmpdir mktempd`

1400 tmpfiles=$tmpfiles" $tmpd"

1401 addsession $session

1402 getcookie

1403 case "$REQUEST_METHOD" in

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

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

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

1407 # cat > $tmpf # too much?

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

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

1410 s="`cat tmp/stream`"

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

1412 ;;

1413 esac

1414 case "$CONTENT_TYPE" in

1415 *boundary*)

1416 bndry=${CONTENT_TYPE#*boundary=}

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

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

1419 do

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

1421 #echo u=$us

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

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

1424 # err k=$k v=$v

1425 case "$k" in

1426 *:filename)

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

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

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

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

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

1432 :

1433 fi

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

1435 case "$mimetype" in

1436 [Ii]mage/x-xcf)

1437 bzip2 "$tmpd/$v"

1438 v=${v}.bz2

1439 ;;

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

1441 [Ii]mage/*)

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

1443 ;;

1444 esac

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

1446 then

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

1448 continue

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

1450 replpar text string \

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

1452 continue

1453 fi

1454 ;;

1455 *)

1456 type='string'

1457 ;;

1458 esac

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

1460 setpar "$k" "$type" "$v"

1461 done

1462 ;;

1463 *)

1464 setviastring "$s"

1465 ;;

1466 esac

1467 }

1468 email4group() {

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

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

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

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

1473 sql="WITH

1474 grpemails AS (

1475 SELECT gname, user, val email

1476 FROM grp_mem NATURAL JOIN grp_mem_s

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

1478 useremails AS (

1479 SELECT user.name, val email

1480 FROM user

1481 LEFT JOIN user_m

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

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

1484 FROM useremails u LEFT JOIN grpemails g

1485 ON u.name=g.user

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

1487 query "$sql"

1488 }

1489 email4groupbyuid() {

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

1491 g=$1; shift

1492 uids=`echo "$@"`

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

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

1495 email4group "$g" `query "$sql"`

1496 }

1497 myemail4group() {

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

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

1500 }

1501 collectmembersbyid() {

1502 # Collect user names of group specified by grid

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

1504 query "SELECT user FROM grp_mem \

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

1506 }

1507 collectmembersbyid() {

1508 # Collect user names of group name

1509 qgrp=`sqlquote "$1"`

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

1511 }

1512 collectgecosesbyid() {

1513 # Collect user gecoses of group

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

1515 query<<-EOF

1516 SELECT gecos

1517 FROM gecoses

1518 WHERE name IN (SELECT user FROM grp_mem

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

1520 EOF

1521 }

1522 collectemail() (

1523 # Collect email addresses for group $1

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

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

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

1527 for e; do

1528 if isuser "$e"; then

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

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

1531 else

1532 qgrp=`sqlquote "$e"`

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

1534 gmem="grp_mem"

1535 else

1536 tm=`sqlquote "$TEAM"`

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

1538 fi

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

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

1541 $gmem g left join grp_mem_s s

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

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

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

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

1546 query "$sql"

1547 fi

1548 done

1549 )

1550 sendinvitation() (

1551 # $1=email

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

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

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

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

1556 gecos=`gecos`

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

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

1559 _m4 -D_URL_="$urlbase" \

1560 -D_USER_="$name" \

1561 -D_EMAIL_="$1" \

1562 -D_REGIST_="$regist" \

1563 -D_ADMIN_="$admin" \

1564 $msgdir/mail-invite.m4 \

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

1566 return 0

1567 )

1568 emaildomaincheck() {

1569 case "$1" in

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

1571 *@*)

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

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

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

1575 return 2

1576 fi

1577 return 0

1578 ;;

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

1580 esac

1581 }

1582 invite() {

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

1584 case "$email" in

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

1586 *@*)

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

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

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

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

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

1592 elif sendinvitation $email; then

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

1594 else # Cannot be reached here

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

1596 fi ;;

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

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

1599 esac

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

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

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

1603 fi

1604 if [ -f $invite_policy ]; then

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

1606 else

1607 pol="$invite_policy"

1608 fi

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

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

1611 -D_INVITE_POLICY_="$pol" \

1612 $layout/html.m4.html $layout/invite.m4.html

1613 }

1614 regist() {

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

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

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

1618 echo "bye bye" | html p

1619 reutrn

1620 fi

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

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

1623 cat<<EOF

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

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

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

1627 EOF

1628 return

1629 fi

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

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

1632 # Fake login password to wasureta

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

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

1635 wasureta $email

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

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

1638 addsession $1 # for removal after 1 minute

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

1640 $layout/login.m4.html

1641 return

1642 }

1643 group_safename() {

1644 # Convert $1 to safe group name

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

1646 }

1647 groupupdate() {

1648 gname=`getpar gname`

1649 qgname=`sqlquote "$gname"`

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

1651 # See ALSO same job in showgroup()

1652 newgname=`group_safename "$gname"`

1653 err newgname=$newgname

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

1655 err NewGNAME: gname=$newgname

1656 gname=$newgname

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

1658 replpar gname string "$gname"

1659 fi

1660 # Name confliction check

1661 parow=`getpar rowid`

1662 ## err parow=$parow

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

1664 query "BEGIN EXCLUSIVE;"

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

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

1667 if [ $count -gt 0 ]; then

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

1669 query "END;"

1670 return

1671 fi

1672 par2table $formdir/grp.def

1673 query "END TRANSACTION;"

1674 # Remove orphan

1675 : <<EOF

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

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

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

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

1680 EOF

1681 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1685 sql="delete from blog where id in

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

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

1688 query "$sql";

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

1690 return # and return

1691 fi

1692 fi

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

1694 fi

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

1696 grid=$(query $sql)

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

1698 grp $grid

1699 }

1700 groupclone() {

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

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

1703 case "$1" in

1704 */noteam)

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

1706 esac

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

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

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

1710 return

1711 fi

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

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

1714 return

1715 fi

1716 i=0

1717 while true; do

1718 copy="-copy$i"

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

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

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

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

1723 i=$((i++))

1724 continue

1725 fi

1726 break

1727 done

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

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

1730 query<<-EOF

1731 BEGIN;

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

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

1734 SELECT $newqname, key, val

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

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

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

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

1739 -- Copy members and their configuration --

1740 REPLACE INTO grp_mem SELECT $newqname, user

1741 FROM grp_mem WHERE gname=$qgrp;

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

1743 FROM grp_mem_s WHERE gname=$qgrp;

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

1745 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1746 -- Copy administrators --

1747 REPLACE INTO grp_adm SELECT $newqname, user

1748 FROM grp_adm WHERE gname=$qgrp;

1749 COMMIT;

1750 EOF

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

1752 STOPCLONEMSG=1 groupconf "$newrowid"

1753 }

1754 groupman() {

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

1756

1757 GF_STAGE="grpconf"

1758 GF_STAGE=groupupdate

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

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

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

1762 -D_DUMPTABLE_="syscmd(cat)" \

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

1764 }

1765 userconf() {

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

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

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

1769 }

1770 groupconf() {

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

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

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

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

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

1776

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

1778 grp=`getgroupbyid $rowid`

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

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

1781 return

1782 fi

1783

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

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

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

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

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

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

1790 <div>

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

1792 <table>

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

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

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

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

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

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

1799 </table>

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

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

1802 </div>

1803 EOF

1804 fi

1805 }

1806 mems() {

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

1808 kwd=`getpar kwd`

1809 listmember $kwd

1810 }

1811 grps() {

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

1813 kwd=`getpar kwd`

1814 listgroup $kwd \

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

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

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

1818 $layout/form+dump.m4.html

1819 }

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

1821 gpg=`getpar grp`

1822 grid=${1:-$gpg}

1823 grp=`getgroupbyid "$grid"`

1824 ## . ./s4-blog.sh

1825 jg=`getpar joingrp`

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

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

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

1829 fi

1830 htmlheader=$layout/html.m4.html

1831 showgroup "$grid"

1832 }

1833 sql4interestblogs() {

1834 cat<<EOF

1835 CREATE TEMPORARY VIEW interestblogs AS

1836 SELECT blog.rowid rid, id, author

1837 FROM blog

1838 NATURAL JOIN

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

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

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

1842 WHEN (SELECT user FROM grp_mem

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

1844 THEN 0

1845 ELSE 1

1846 END;

1847 EOF

1848 }

1849 listnewblogsql() { # $1=user

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

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

1852 newdays=${newdays:-14}

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

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

1855 WHERE user='$user'

1856 AND tblrowid IN

1857 ($blogreadflagrowid,

1858 $blogcutoffflagrowid)),

1859 $basetime -- "0"

1860 );"`

1861 cat<<EOF

1862 `sql4interestblogs`

1863 WITH article_ctime as (

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

1865 FROM article join article_s s using(id)

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

1867 GROUP BY id

1868 ), blog_title_owner as (

1869 SELECT blg.rid brid, id,

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

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

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

1873 ), blogall as (

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

1875 ), news as (

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

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

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

1879 FROM blogall bl

1880 LEFT JOIN

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

1882 ON bl.brid=al.tblrowid

1883 WHERE atime < bl.ctime

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

1885 LIMIT 10

1886 ) SELECT brid LINK, "新着",

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

1888 ctime, title,

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

1890 FROM news;

1891 EOF

1892 }

1893

1894 search_form() {

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

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

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

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

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

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

1901 @week → 最近一週間

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

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

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

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

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

1907 auth=""

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

1909 case "$1" in

1910 author=*)

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

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

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

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

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

1916 ;;

1917 grp=*)

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

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

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

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

1922 ;;

1923 esac

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

1925 cat<<-EOF

1926 <div class="right">

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

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

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

1930

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

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

1933

1934 </form>

1935 </div>

1936 EOF

1937 }

1938

1939 imgsrc_cache() (

1940 # $1 = directory for cache'ing

1941 # $2 = table (user_m or grp_m)

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

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

1944 dir="$1" tbl="$2"

1945 keycond="$3"

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

1947 ORDER BY rowid DESC LIMIT 1"

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

1949 tmpf=$tmpd/imgsrc_cache.$$

1950 case "$4" in

1951 [Ss]) size=S ;;

1952 [Oo]) size=O ;;

1953 *) size=M ;;

1954 esac

1955 # ImageCache filename storing schema:

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

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

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

1959 valtype=`query "$sql0"`

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

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

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

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

1964 case "$filetype" in

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

1966 esac

1967 fi

1968 cacheimg_S=$dir/S_$filename

1969 cacheimg_M=$dir/M_$filename

1970 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1978 else

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

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

1981 fifo2=$fifo.2

1982 mkfifo $fifo $fifo2

1983 fmt=${filename##*.}

1984 ## [[ NOTE ]]

1985 ## a. convert oldimage newimage

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

1987 ## b is much smaller than a

1988 cat $tmpf | unhexize \

1989 | tee $fifo \

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

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

1992 | tee $fifo2 \

1993 | convert - "$cacheimg_M" &

1994 cat $fifo | convert -define ${fmt}:size=${iconxy_S} \

1995 -resize ${iconxy_S}'>' - ${fmt}:- \

1996 | convert - "$cacheimg_S" &

1997 printf '%s' "<img src=\"data:${filetype},"

1998 hexize "$fifo2" |sed 's/$..$/%\1/g' # Use medium as pre-cached image

1999 echo '">'

2000 echo "$sum" > $sumfile

2001 fi

2002 ## Now preparing cache image, done.

2003 ## Store this information to DB

2004 stbl=${tbl%_m}_s # user_s or grp_s

2005 pkey=${keycond%%=*} # Primary Key name

2006 pval=${keycond#*=} # Primary Key value

2007 query <<-EOF

2008 REPLACE INTO $stbl($pkey, key, type, val)

2009 VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);

2010 EOF

2011 )

2012

2013 showhome() {

2014 # $1=userRowIdToShow

2015 err showhome \$1=$1

2016 case "$1" in

2017 *@*) uname=`getvalbypkey user name "$1"` ;;

2018 *) uname=`getvalbyid user name $1` ;;

2019 esac

2020 ## err ShowHome: uname=$uname

2021 td=`getcachedir home/"$1"`

2022 gecos=`gecos "$uname"`

2023 ## err SH:gecos=$gecos

2024 GF_VIEWONLY=1

2025 cond="gname in (select gname from grp_mem where user='$uname')"

2026 search_form_args=""

2027 if [ x"$user" = x"$uname" ]; then

2028 usermenu="<a href=\"?userconf\" accesskey=\"e\"

2029 title=\"E\">プロフィールの編集</a> /

2030 <a href=\"?blog\" accesskey=\"n\" title=\"N\">新規話題の作成</a>"

2031 # Display folders

2032 sql="select count(id) from article_m where id

2033 in (select id from article where author='$user')

2034 and type like 'file:%';"

2035 ## err nfile-sql=`echo "$sql"`

2036 nfile=`query "$sql"`

2037 # err nfile=$nfile

2038 if [ $nfile -gt 0 ]; then

2039 usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"

2040 title=\"L\">過去の提出ファイル</a>"

2041 fi

2042 else

2043 latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \

2044 GROUP BY user;"`

2045 usermenu="<p>Last seen on $latestlog</p>"

2046 search_form_args="author=$uname"

2047 fi

2048 . ./s4-blog.sh

2049

2050 tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$

2051 search_form "$search_form_args" > $sf

2052 printf "%s さん" "$gecos" > $tf

2053 { echo "<div class=\"noprofimg\">"

2054 viewtable $formdir/user.def user $1

2055 echo "</div>"

2056 } > $pf

2057

2058 sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"

2059 img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`

2060 imf=$tmpd/profimg.$$; touch $imf

2061 if [ -n "$img" ]; then

2062 if true; then

2063 tbl=user_m

2064 enticond="name='$uname'"

2065 imgsrc_cache "$td/main" user_m "$enticond" M

2066 else

2067 { printf '%s' "<IMG src=\"data:${img#file:},"

2068 query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \

2069 | sed 's/$..$/%\1/g'

2070 echo '">'

2071 }

2072 fi > $imf

2073 fi

2074 nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \

2075 val='$uname';"`

2076 ## REMOVE This comment block until 2019/7/1

2077 ## err "----- `gdate +%FT%T.%3N` ------------C"

2078 ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1

2079 listblog $uname > $bf

2080 ## unset ddd

2081 ## err "----- `gdate +%FT%T.%3N` ------------D"

2082

2083 hometail=$tmpd/tail.$$

2084 mkfifo $hometail

2085

2086 #Calling listgroupbytable, originally here

2087

2088 (

2089 # Display Most Recent Entry

2090 shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}

2091 shortval=${shortval:-val}

2092

2093 # The m.aid in the next line is suspicious. But works fine in SQLite3...

2094 DT_SQL="SELECT b.rowid || '#' || m.aid LINK,

2095 ctime,

2096 (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,

2097 (SELECT gecos FROM gecoses

2098 WHERE name=(SELECT val FROM blog_s

2099 WHERE key='owner' AND id=b.id)) owner,

2100 (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text

2101 FROM blog b

2102 JOIN

2103 (SELECT distinct blogid, a.id aid, max(val) ctime

2104 FROM article a, article_s s

2105 ON a.id=s.id AND a.author='$uname' AND s.key='ctime'

2106 GROUP BY blogid ORDER BY val DESC LIMIT 50

2107 ) m

2108 ON b.id=m.blogid;"

2109 # This should be as follows

2110 : <<EOF

2111 WITH arts AS(

2112 SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,

2113 a.blogid, a.id id, s.val ctime

2114 FROM article a NATURAL JOIN article_s s

2115 WHERE s.key = 'ctime' AND a.author='$user'

2116 GROUP by s.id

2117 )

2118 SELECT a0.brid,a0.blogid,a0.id,a0.ctime

2119 FROM arts a0

2120 JOIN

2121 (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1

2122 ON a0.blogid=a1.blogid AND a0.ctime=a1.mct

2123 ORDER BY ctime DESC LIMIT 50;

2124 EOF

2125

2126 cat<<-EOF

2127 `cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label

2128 for="mre" title="D">最近の書き込み先</label>

2129 <div class="lcto">

2130 `DT_VIEW=replyblog dumptable html blog`

2131 </div>

2132 EOF

2133 unset DT_SQL

2134 if [ x"$user" = x"$uname" ]; then

2135 # Display NEWS

2136 # 2016-06-26

2137 if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then

2138 acclog blog $blogreadflagrowid

2139 # echo "全部既読にしました" | html p

2140 fi

2141 # 2016-02-19 Counting NEWS without using dumptable.

2142 sql=`listnewblogsql "$user"`

2143 # echo "$sql" > tmp/listnew

2144 new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`

2145 cont=`echo "$new10"|grep "^<TR>"|wc -l`

2146 cont=$((cont-1))

2147 err newcount=$cont

2148 if [ $cont -gt 0 ]; then

2149 #echo "全体の新着記事${cont}傑" | html h2

2150 cgi_radio foldtabs yes 'id="new10" accesskey="f"'

2151 echo "<label for=\"new10\" title=\"F\">新着${cont}傑</label><div>"

2152 cat<<-EOF | html form 'action="?home"'

2153 `cgi_checkbox readchk yes 'id="read"'`<label

2154 for="read">新着ふくめて全部読んだことにする</label>

2155 `cgi_submit '確定'`

2156 `cgi_hidden read yes`

2157 EOF

2158 echo "$new10 "

2159 echo "</div>"

2160 else # If news is 0, set log cut off flag

2161 acclog blog $blogcutoffflagrowid # for speed

2162 fi

2163 else # Not My Home ($user != $uname)

2164 : # DT_SQL=

2165 fi

2166 ) > $hometail & # Is background call safe to m4??

2167 #

2168 listgroupbytable $formdir/grp.def $cond |

2169 _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \

2170 -D_PROFILE_="spaste(\`$pf')$usermenu" \

2171 -D_PROFIMG_="spaste(\`$imf')" \

2172 -D_BLOGS_="spaste(\`$bf')" \

2173 -D_SEARCH_="spaste(\`$sf')" \

2174 -D_NBLOG_="$nblog" \

2175 -D_GROUPS_="syscmd(\`cat')" \

2176 -D_HOMETAIL_="syscmd(\`cat $hometail')" \

2177 $layout/html.m4.html $layout/home.m4.html

2178

2179 # Record access log

2180 [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1

2181 }

2182 commission() { # $1=grp-rowid $2=user-rowid

2183 contenttype; echo

2184 ## err commission: "$@"

2185 gname=`getgroupbyid $1`

2186 echo "グループ $gname 管理者委任" \

2187 | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html

2188 if [ -n "$2" ]; then

2189 grp_reg_adm "$@"

2190 else

2191 echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p

2192 fi

2193 }

2194 listgroupbytable() {

2195 # $1=deffile $2...=condition

2196 tagline=`grep :tag: $1`; shift

2197 and="${1:+and }" where=${1:+where }

2198 href="<a href=\"$myname?grp+"

2199 echo '<div class="listgroup">'

2200 NGsql="select distinct tag from\

2201 (select gname, max(case key when 'tag' then val end) as tag, \

2202 max(case key when 'ctime' then val end) as ctime\

2203 from grp_s group by gname order by ctime);"

2204 sql="select val from grp_s where key='tag' $and$* group by val;"

2205 ## err ListGRP: query sql="$sql"

2206 for tag in `query "$sql"`

2207 do

2208 ## err ListGrp: tag=$tag

2209 tn=${tagline%%=${tag}*}

2210 tn=${tn##*[ :]}

2211 sql="select rowid||':'||gname as 'グループ名',説明 from

2212 (select (select rowid from grp g where g.gname=grp_s.gname)

2213 as rowid,

2214 gname,

2215 max(case key when 'gecos' then val end) as '説明',

2216 max(case key when 'tag' then val end) as 'tag',

2217 max(case key when 'mtime' then val end) as mtime from grp_s

2218 $where$* group by gname having tag='$tag' order by mtime desc);"

2219 ## err PersonalGroupList= `echo $sql`

2220 echo "<h2>$tn</h2>"

2221 echo '<table class="b listgroup">'

2222 sq -header -html $db "$sql" \

2223 | sed "s,$<TR><TD>$$[0-9]*$:$[^<]*$</TD>,\1$href\2\">\3</a>,"

2224 echo '</table>'

2225 done

2226 echo '</div>'

2227 }

2228 iconhref() (

2229 # $1=icon-file, $2=Href $3=title $4...=anchor

2230 data=`percenthex "$1"`

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

2232 ## err iconhref: \$1=$1 \$2=$2 \$3="$@"

2233 href=$2; title=$3; shift 3

2234 echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"

2235 )

2236 iconhref2() (

2237 # $1=icon-file, $2=Href $3=title $4...=anchor

2238 src=$1

2239 href=$2; title=$3; shift 3

2240 echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"

2241 )

2242 listentry() (

2243 # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)

2244 # Referring variable $iamowner=$grp to attach owner-request links

2245 ## err listentry: \$1=$1 \$2=$2 \$3=$3

2246 cond='' hiddens=''

2247 offset=`getpar offset`; offset=${offset%%[!0-9]*}

2248 if [ -z "$offset" ]; then

2249 offset=`getpar start`; offset=${offset%%[!0-9]*}

2250 offset=$((offset-1))

2251 fi

2252 offset=$((offset + 0)) # change to numeric forcibly

2253 [ $offset -lt 0 ] && offset=0

2254 limit=$listentlimit

2255 dir=`getcachedir "$1"`

2256 if [ x"$1" = x"user" ]; then

2257 hrb="$myname?home"

2258 deficon=person-default.png

2259 entity="ユーザ" tbl=user link=rowid nm=name # stage=mems

2260 [ -n "$4" ] && hiddens=`cgi_hidden grid $4`

2261 gcs=gecos

2262 else # if group

2263 hrb="$myname?grp"

2264 deficon=group-default.png

2265 entity="グループ" tbl=grp link=rowid nm=gname stage=grps

2266 gcs=name

2267 tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`

2268 if [ -n "$tagline" ]; then

2269 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2270 ## err tagconv=$tagconv

2271 fi

2272 fi

2273 if [ ! -d $dir ]; then

2274 mkdir -p $dir

2275 fi

2276 if [ ! -s $dir/$deficon ]; then

2277 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2278 fi

2279 if [ -n "$2" ]; then

2280 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2281 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2282 fi

2283 tag=`getpar tag` tag2=`getpar tag2`

2284 if [ x"$tag" = x"NULL" ]; then

2285 tag="" tag2=""

2286 fi

2287 if [ -n "$tag$tag2" ]; then

2288 tag=${tag:-$tag2}

2289 qtag=`sqlquote "$tag"`

2290 cond2="tag=$qtag"

2291 fi

2292 if [ -n "$cond1$cond2" ]; then

2293 cond="$cond1${cond2:+ AND $cond2}"

2294 cond="WHERE ${cond# AND }"

2295 fi

2296

2297 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2298 # grpは呼出し元の動的スコープ変数でよくないな...

2299 ##qgrp=`sqlquote $grp`

2300 getgrp="(select gname from grp where rowid=${rowid:--1})"

2301 sql="select a.rowid, a.$link,

2302 coalesce(b.$gcs, a.$nm) as nick,

2303 quote(a.$nm) as qname,

2304 (SELECT val FROM ${tbl}_s

2305 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2306 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2307 || case when a.$nm in (select gname from grp)

2308 then printf('(%d名)',

2309 (select count(user) from grp_mem where gname=a.$nm))

2310 else ' <'||a.$nm||'>'

2311 end

2312 as name,

2313 b.tag,

2314 case when a.$nm in (select user from grp_adm

2315 where gname=$getgrp) then '管理者'

2316 when '$user' in (select user from grp_adm where gname=a.$nm)

2317 then 'ADMIN'

2318 when '$user' in (select user from grp_mem where gname=a.$nm)

2319 then 'Member'

2320 when '$iamowner' = '' then ''

2321 else ',not='||a.rowid end as ownerlink,

2322 CASE '$entity'

2323 WHEN 'グループ'

2324 THEN coalesce(

2325 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2326 'open')

2327 ||

2328 CASE WHEN '$user'

2329 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2330 THEN ' ismember'

2331 ELSE ''

2332 END

2333 ELSE 'user'

2334 END regmode

2335 from $tbl a left join

2336 (select $nm as name,

2337 max(case key when 'gecos' then val end) as gecos,

2338 max(case key when 'tag' then val end) as tag,

2339 max(case key when 'mtime' then val end) as mtime,

2340 max(case key when 'wtime' then val end) as wtime,

2341 max(case key when 'login' then val end) as login

2342 from ${tbl}_s group by $nm)

2343 b on a.$nm=b.name $cond $3

2344 order by b.wtime desc, b.login desc,

2345 b.mtime desc, b.tag desc, a.rowid asc"

2346 # Give precedence to newer maintained groups (2016-09-24)

2347 # Note that mtime is stored only in grp_s.

2348 ## err LE:sql.1="$sql"

2349 total=`query "with x as ($sql) select count(*) from x;"`

2350 echo "${entity} 一覧" | html h2

2351 echo '<div class="listentry">' # List-entry div

2352 # Show owner/member filter button

2353 METHOD=GET

2354 hiddens="$hiddens

2355 `cgi_hidden kwd \"$kwd\"`

2356 `cgi_hidden stage \"$stage\"`"

2357 if [ x"$tbl" = x"grp" ]; then

2358 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2359 fh="<select name=\"tag\">$nl"

2360 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2361 for l in $args; do

2362 val=${l#*=} tname=${l%=*}

2363 if [ x"$val" = x"$tag" ]; then

2364 s=" selected"

2365 selectedtags="(種別[${tname}]のみ)"

2366 else

2367 s=""

2368 fi

2369 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2370 done

2371 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2372 cat<<-EOF

2373 <form action="$myname" method="$METHOD">

2374 </form>

2375 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2376 for="ismembtn">参加中以外隠す</label>

2377 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2378 for="isadmbtn">管理者参加以外隠す</label>

2379 EOF

2380 # limit=3

2381 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2382 fi

2383 if [ $total -gt $limit ]; then

2384 echo '<div>'

2385 METHOD=GET cgi_form $stage <<EOF

2386 $form

2387 <label>次の語を含む${entity}で検索:

2388 `cgi_text kwd "$kwd"`</label>

2389 $hiddens

2390 EOF

2391 echo '</div>'

2392 else

2393 echo $selectedtags | html p

2394 fi

2395 cat<<EOF

2396 <form action="$myname" method="$METHOD">

2397 <p>${total}件中の<input class="hidesub" type="text" name="start"

2398 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2399 <input type="submit" value="確定"></p>

2400 </form>

2401 EOF

2402 if [ $((offset+limit)) -lt $total ]; then

2403 nextbtn=$(

2404 cat<<EOF

2405 <div class="right clear"><form action="$myname" method="$METHOD">

2406 `cgi_submit 次の${limit}件`

2407 $hiddens

2408 `cgi_hidden offset $((offset + limit))`</form></div>

2409 EOF

2410 )

2411 fi

2412 if [ $offset -gt 0 ]; then

2413 prevbtn=$(

2414 cat<<EOF

2415 <form action="$myname" method="$METHOD">

2416 `cgi_submit 前の${limit}件`

2417 $hiddens

2418 `cgi_hidden offset $((offset - limit))`</form>

2419 EOF

2420 )

2421 fi

2422 pnbtn="$nextbtn$prevbtn"

2423 echo $pnbtn

2424

2425 ## err ListEntry: `echo "$sql"\;`

2426 # sq $db here??? 2016-11-28

2427 query "$sql limit $limit ${offset:+offset $offset};" \

2428 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2429 # err name=$name owner=$ownerp lnk=$lnk

2430 # err newlnk=$lnk regmode=$regmode

2431 icondir=$dir/$id

2432 # Pick up only last icon

2433 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2434 <p class=\"tag _$tag\">$tag</p>" \

2435 | _m4 $tagconv

2436 if [ -n "$NOSPEEDUP" ]; then

2437 files=`getvalbyid $tbl profimg $id $icondir`

2438 if [ -n "$files" ]; then

2439 icon=`echo "$files"|tail -1`

2440 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2441 else

2442 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2443 fi

2444 elif [ -n "$icon" -a -s "$icon" ]; then

2445 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2446 else

2447 cond="$nm=$qname"

2448 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2449 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2450 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2451 # err "img=[$img]"

2452 if [ -n "$img" ]; then

2453 echo "<a href=\"$hrb+$lnk\">"

2454 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2455 echo "</a>"

2456 else

2457 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2458 fi

2459 fi

2460 echo "<br>$name${ownerp:+<br>($ownerp)}"

2461 echo "</div>"

2462 done

2463 echo "</div>" # End of List-entry div

2464 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2465 )

2466 listmember() {

2467 listentry user "$@"

2468 }

2469 listgroup() {

2470 listentry group "$@"

2471 }

2472 hexteams() { # $1=gname, $2(optional)=user

2473 cond=${2:+" AND user='$2'"}

2474 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2475 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2476 }

2477 showgroup() { # $1=group-rowid

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

2479 grid=`getpar grid`

2480 grid=${grid%%[!0-9]*}

2481 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2482 else

2483 grid=$1

2484 fi

2485 grp=`getgroupbyid $grid`

2486 qgrp=`sqlquote "$grp"`

2487 htmlgrp=`echo "$grp"|htmlescape`

2488 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2489 if isgroup "$grp"; then

2490 tf=$tmpd/title.$$

2491 sf=$tmpd/search.$$

2492 bodyclass=`query "SELECT val FROM grp_s

2493 WHERE gname=$qgrp AND key='regmode';"`

2494 if ismember "$user" "$grp"; then

2495 ismember="ismember"

2496 bodyclass="$bodyclass${bodyclass:+ }ismember"

2497 else

2498 ismember="" # bodyclass="group"

2499 fi

2500 bodyclass="$bodyclass grouphome"

2501 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2502 echo "グループ $htmlgrp" > $tf

2503

2504 showgroupsub $formdir/grp.def "$grid" | \

2505 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

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

2507 -D_BODYCLASS_="$bodyclass" \

2508 -D_DUMPTABLE_="" \

2509 $htmlheader $sf $layout/form+dump.m4.html

2510 # $htmlheader $layout/form+dump.m4.html

2511 # $htmlheader is defined in grp()

2512 else # if $grp is removed at par2table

2513 listgroup

2514 fi

2515 }

2516 showgroupsub() {

2517 # $1=def-file $2=group-rowid

2518 # Using $ismember

2519 rowid=$2

2520 grp=`getgroupbyid $2`

2521 qgrp=`sqlquote "$grp"`

2522 td=`getcachedir grp/"$2"`

2523 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

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

2525 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2526 #grp=`sq $db "select gname from grp where rowid=$grp"`

2527 echo "showgroupsub: invalid argument($1 $2)" | html p

2528 return

2529 fi

2530 val=`getvalbyid grp profimg $rowid $tmpd`

2531 enticond="gname=$qgrp"

2532 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2533 if [ -n "$img" ]; then

2534 cat<<-EOF

2535 <p class="groupimg">

2536 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2537 EOF

2538 fi

2539 echo "<div class=\"noprofimg\">"

2540 viewtable $1 grp $rowid

2541 echo "</div>"

2542 if isgrpowner "$user" "$grp"; then

2543 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2544 iamowner=$rowid

2545 colmd=" mode"

2546 fi

2547 if [ -n "$ismember" ]; then

2548 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2549 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2550 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2551 cat<<EOF

2552 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2553 <div class="fold clear">

2554 `cgi_checkbox send yes id="send"`<label

2555 for="send">グループ全員にメッセージ送信</label>

2556 <div>

2557 `cgi_textarea message "" "cols=60"`

2558 `cgi_submit 送信`

2559 `cgi_reset リセット`

2560 </div>

2561 `cgi_hidden grp $rowid`

2562 </div></form>

2563 EOF

2564 fi

2565 # 加入ボタン + 加入者リスト

2566 if [ -n "$ismember" ]; then

2567 ismem='checked' state="(参加中)"

2568 else

2569 nomem='checked' state="(現在非加入)"

2570 fi

2571 # このグループでの加入アドレス

2572 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2573 and key='email';"`

2574 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2575 ## and key='email';"

2576 ##err email=$eml

2577 cat <<EOF

2578 <div class="fold clear">

2579 `cgi_checkbox reg yes id="reg"`<label

2580 for="reg">自身の加入状態を操作する</label>$state

2581 <div>

2582 EOF

2583 cgi_form grp <<EOF

2584 <p>このグループに</p>

2585 <table class="b">

2586 <tr><th>メンバーとして</th><td>

2587 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2588 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2589 <tr><th>参加する場合のメイルアドレス<br>

2590 <small>(メインのアドレスとは違うものにする場合に記入<br>

2591 同じでよい場合は空欄に)</small></th>

2592 <td>`cgi_text email $eml`</td></tr>

2593 </table>

2594 `cgi_hidden grp $rowid`

2595 EOF

2596 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2597 echo "moderated (承認加入の)グループなので実際に参加できるのは

2598 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2599 fi

2600 echo '</div></div>'

2601 echo '<h2>話題一覧</h2>'

2602 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2603 cat<<-EOF

2604 <form class="summary" action="$myname" title="$thelp">

2605 `cgi_hidden owner "$grp"`

2606 `cgi_hidden kwd "@week"`

2607 `cgi_hidden stage searchart`

2608 `cgi_submit "一週間のまとめ"`

2609 </form>

2610 EOF

2611 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2612 colstate="state:稼動状態:frozen=rowclass=凍結"

2613 DT_CHLD=article:blogid \

2614 DT_QOWNER="$qgrp" \

2615 DT_VIEW=replyblog dumptable html blog \

2616 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2617

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

2619 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2620 cm="?commission+$rowid"

2621 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2622 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2623 # team list

2624 hexteams=`hexteams "$grp"`

2625 if [ -n "$hexteams" ]; then

2626 echo "チーム一覧" | html h2

2627 echo '<div class="dumptable"><table class="b">'

2628 sq $db -html -header<<-EOF

2629 SELECT val TEAM,

2630 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2631 MEMBERS

2632 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2633 EOF

2634 echo '</table></div>'

2635 fi

2636 }

2637 grp_getbodyclass() {

2638 # Get css class name for document.

2639 # `moderated' for moderated groups

2640 # `ismember' for groups where user belongs

2641 # $1=GroupName (w/o quote)

2642 # $user=userNameCurrentlyLogin

2643 ## err grp_getbodyclass: 1="$1"

2644 qgrp=`sqlquote "$1"`

2645 query<<-EOF

2646 SELECT coalesce(

2647 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2648 'open')

2649 ||

2650 CASE WHEN '$user'

2651 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2652 THEN ' ismember'

2653 ELSE ''

2654 END;

2655 EOF

2656 }

2657 grpaction() { # $1=group-rowid

2658 err GRP_ACTION:IN

2659 grid=${1:-`getpar grp`}

2660 grp=`getgroupbyid "$grid"`

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

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

2663 echo "無効な指定です。" | html p; return

2664 fi

2665 if ! ismember $user "$grp"; then

2666 echo "加入者のみに許可された操作です。" | html p; return

2667 fi

2668 echo "グループ $grp 個別選択操作" \

2669 | _m4 -D_TITLE_="syscmd(\`cat')" \

2670 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2671 $layout/html.m4.html

2672

2673 isowner=""

2674 isgrpowner "$user" "$grp" && isowner="yes"

2675 usel=`getpar usel`

2676 if [ -n "$usel" ]; then

2677 uids=$(echo `echo $usel`|tr ' ' ',')

2678 ## err grpaction-1: grp=$grp, `echo $sql`

2679 text=`getpar text|tr -d '\r'`

2680

2681 rm=`getpar rm` cfm=`getpar confirm`

2682 ## err rm=$rm cfm=$cfm

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

2684 if [ "$isowner" ]; then

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

2686 # Eliminate

2687 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2688 for tbl in grp_mem grp_mem_s grp_mem_m; do

2689 sql="delete from $tbl $cond;"

2690 # echo "sql=$sql"

2691 query "$sql"

2692 err rmGRPuser "$sql"

2693 done

2694 num=`query "select count(*) from user where rowid in ($uids);"`

2695 #err num=$num

2696 if [ 0$num -gt 0 ]; then

2697 sql="select coalesce(b.val,a.name) from user a left join \

2698 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2699 # err `echo "$sql"`

2700 html pre<<EOF

2701 以下の${num}名のグループ $grp 登録を解除しました。

2702 `query "$sql"`

2703 EOF

2704 fi

2705 else

2706 echo "確認のチェックがないのでやめておきます。" | html p

2707 return

2708 fi

2709 else # not Group Owner

2710 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2711 return

2712 fi

2713 cat<<EOF

2714

2715 EOF

2716 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2717 if [ -z "$text" ]; then # if msg is empty

2718 echo "なにかメッセージを..." | html p

2719 return 0

2720 fi

2721 gecos=`gecos $user`

2722 safegc=`echo "$gecos" | tr -d '<>@'`

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

2724 fromad=`myemail4group "$grp"`

2725 ###mail_from="$safegc <$fromad>"

2726 mail_from="$safegc <$user>" # TEST: 2020/5/13

2727 test -n `getpar sender` &&

2728 export SENDER=$user # TEST: 2020/5/15

2729 replyto=$fromad

2730

2731 ## Start parse of attachment files

2732 if [ -n "`getpar email`" ]; then

2733 ar=`getpar supprcpt`

2734 if [ -n "$ar" ]; then

2735 for a in $ar; do

2736 checkdomain "$a" && supprcpt="$supprcpt $a"

2737 done

2738 fi

2739 subj=`getpar subject`

2740 afiles=""

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

2742 do

2743 f=$tmpd/$fn

2744 if [ -s $f ]; then

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

2746 fi

2747 done

2748 else

2749 preface=$(cat <<-EOF

2750 $url

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

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

2753 ----------------------------------------------------------

2754 EOF

2755 )

2756 fi

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

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

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

2760 REPLYTO=$replyto

2761 MAIL_FROM=$mail_from

2762 export REPLYTO SMAIL_TO MAIL_FROM

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

2764 for r in $rcpts; do

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

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

2767 else

2768 # Show sender and rcpts address for guest

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

2770 fi

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

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

2773 else

2774 smail "$r" "$subj"

2775 fi <<EOF

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

2777 EOF

2778 done

2779 if [ $? = 0 ]; then

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

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

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

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

2784 html pre<<EOF

2785 `query "$sql"`

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

2787 EOF

2788 err SendDone: `echo $sql`

2789 fi

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

2791 grp_reg_adm $grid $usel

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

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

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

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

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

2797 team=newteam

2798 fi

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

2800 cat<<-EOF | html p

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

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

2803 EOF

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

2805 else

2806 grp_add_team $grid "$team" $usel

2807 fi

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

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

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

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

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

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

2814 grp_rm_team $grid "$rmteam" $usel

2815 else

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

2817 fi

2818 else

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

2820 fi

2821 fi

2822 fi

2823 # POST count summary

2824 from=`getpar from`; to=`getpar to`

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

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

2827 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2828 # New entry

2829 sql="WITH mems AS (

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

2831 ON gm.user=g.name

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

2833 ), target_article AS (

2834 SELECT id FROM article_s

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

2836 ), posts AS (

2837 SELECT author, count(author) post

2838 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2839 WHERE blogid IN (SELECT id FROM blog_s

2840 WHERE key='owner'

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

2842 AND key='text'

2843 GROUP BY author

2844 ), teams AS (

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

2846 FROM grp_mem_m

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

2848 AND key='team'

2849 GROUP BY user

2850 ), user_post AS (

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

2852 FROM mems m LEFT JOIN posts

2853 ON m.name=posts.author

2854 GROUP by m.rowid

2855 )

2856 SELECT

2857 CASE

2858 WHEN (SELECT user FROM grp_adm

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

2860 AND user=up.name) IS NOT NULL

2861 then 'k'

2862 ELSE ''

2863 END || rowid || ',' || gecos NAME,

2864 post POST, team _TEAM_

2865 FROM user_post up LEFT JOIN teams t

2866 ON up.name=t.user

2867 ORDER BY gecos;"

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

2869 tf=$tmpd/title.$$

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

2871 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

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

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

2874 </p></div>"

2875 excmsg="`cgi_radio rm yes id=\"conf\"`<label

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

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

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

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

2880 </div>"

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

2882 allhexteams=$(hexteams "$grp")

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

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

2885 else

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

2887 fi

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

2889 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

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

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

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

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

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

2895 </div>

2896 "

2897 fi

2898 stf=$tmpd/selteam.$$

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

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

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

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

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

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

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

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

2907 cgi_form grpaction<<EOF \

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

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

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

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

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

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

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

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

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

2917 <div class="foldtabs">

2918 `cgi_radio rm addteam 'id="cmteam"'`<label

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

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

2921 `cgi_datalist_h teams $allhexteams`

2922 </div>

2923 ${rmteammsg}

2924 `cgi_radio rm send id="sendmsg"`<label

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

2926 <div>

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

2928 >email書式を使う</label>

2929 <div class="folded">

2930 <table>

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

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

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

2934 </td></tr>

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

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

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

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

2939 </table>

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

2941 </div>

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

2943 </div>

2944 </div>

2945 ${isowner:+$cmmsg$excmsg}

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

2947 </div>

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

2949 <table class="td2r thl">

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

2951 </table>

2952 `cgi_hidden grp $grid`

2953 `cgi_hidden myuid $myuid id="myuid"`

2954 EOF

2955 }

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

2957 # Create TEMPORARY VIEW

2958 query<<EOF

2959 CREATE TEMPORARY VIEW writeusers AS

2960 SELECT DISTINCT author FROM article

2961 WHERE id in (

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

2963 );

2964 CREATE TEMPORARY VIEW movablegroups AS

2965 SELECT g.rowid growid , g.gname

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

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

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

2969 WHERE (SELECT author FROM writeusers

2970 EXCEPT

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

2972 IS NULL;

2973 $2

2974 EOF

2975 }

2976 sql4readableblogs() {

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

2978 # Blog is readable when:

2979 # 1: blog owner is an user

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

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

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

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

2984 CREATE TEMPORARY VIEW readableblogs AS

2985 SELECT blog.rowid rid, id, author

2986 FROM blog

2987 NATURAL JOIN

2988 (SELECT id,

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

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

2991 FROM blog_s GROUP by id) bs

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

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

2994 WHEN (SELECT val FROM grp_s

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

2996 AND

2997 (SELECT user FROM grp_mem

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

2999 THEN 0

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

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

3002 ELSE 1

3003 END;

3004 EOF

3005 }

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

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

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

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

3010 return

3011 fi

3012 owner=`getvalbyid blog owner $rowid`

3013 title=`getvalbyid blog title $rowid`

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

3015 | _m4 -D_TITLE_="修正" \

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

3017 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

3020 # Move to group

3021 if isuser "$owner"; then

3022 crview4article $rowid

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

3024 ## err N=$n

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

3026 ## err ROWID=$rowid

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

3028 cat<<-EOF

3029 <div class="fold">

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

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

3032 <div>

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

3034 移動先グループ:

3035 <select name="mv2grp">

3036 EOF

3037 query ".mode html"

3038 query<<-EOF |

3039 $sql

3040 .mode list

3041 EOF

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

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

3044 cat<<-EOF

3045 </select>

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

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

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

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

3050 `cgi_hidden blogrowid $rowid`

3051 `cgi_submit 移動`

3052 `cgi_reset Reset`

3053 </form>

3054 </div>

3055 </div>

3056 EOF

3057 fi

3058 # end of isuser "$owner"

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

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

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

3062 cat<<-EOF

3063 <div class="fold">

3064 `cgi_checkbox mv2team send id="mv2team"`<label

3065 for="mv2team">この話題を以下のチームのものにする</label>

3066 <div><p>現在の所属チーム設定:

3067 `query "SELECT

3068 coalesce((SELECT val FROM blog_s

3069 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3070 AND key='team'),

3071 ':なし');"`</p>

3072 <form action="?mvart" method="POST" enctype="multipart/form-data">

3073 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3074 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3075 `cgi_hidden blogrowid $rowid`<br>

3076 `cgi_submit 移動`

3077 `cgi_reset Reset`

3078 </form></div></div>

3079 EOF

3080 fi

3081 }

3082 mvart() { # move diary to some group or team

3083 # or move blog of group to team which belong to the group

3084 blogrowid=`getpar blogrowid`

3085 cfm=`getpar cfm`

3086 ##### echo move blog:$blogrowid to $mv2grp | html p

3087 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3088 . ./s4-blog.sh

3089 if [ -z "$blogrowid" ]; then

3090 echo "無効な指定です(mvart)。" | html p

3091 return

3092 elif [ x"$cfm" != x"yes" ]; then

3093 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3094 elif { mv2grp=`getpar mv2grp`

3095 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3096 [ -n "$mv2grp" ]; }; then

3097 crview4article $blogrowid

3098 ########## TRANSACTION BEGIN

3099 query "BEGIN;"

3100 n=`query "SELECT count(*) FROM writeusers;"`

3101 ## err Nwriteuser=$n

3102 if [ $((n)) -gt 0 ]; then

3103 query<<-EOF

3104 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3105 WHERE key='owner'

3106 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3107 AND $mv2grp IN (SELECT growid FROM movablegroups);

3108 EOF

3109 fi

3110 query "END;"

3111 ########## TRANSACTION END

3112 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3113 [ -n "$mv2team" ];}; then

3114 # blog owner can move it to ANY team

3115 case "$mv2team" in

3116 'なし')

3117 cat<<-EOF

3118 DELETE FROM blog_s

3119 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3120 AND key='team';

3121 EOF

3122 ;;

3123 "") ;;

3124 *)cat<<-EOF

3125 BEGIN;

3126 REPLACE INTO blog_s(id, key, val)

3127 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3128 'team', '$mv2team');

3129 REPLACE INTO blog_s(id, key, val)

3130 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3131 'notify', 'all'); -- Change notify to all

3132 END;

3133 EOF

3134 esac | query

3135 fi

3136 blog_reply $blogrowid

3137 echo yes | html p

3138 }

3139 editart() { # $1=article-rowid $2=blogrowid

3140 rowid=${1%%[!A-Z0-9a-z_]*}

3141 blogrowid=${2%%[!A-Z0-9a-z_]*}

3142 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3143 echo "表示する記事番号が未指定です。" | html p

3144 return

3145 fi

3146 owner=`getvalbyid blog owner $blogrowid`

3147 title=`getvalbyid blog title $blogrowid`

3148 author=`getvalbyid article author $rowid`

3149 ## err EDITart: owner=$owner, author=$author

3150 if isgrpowner "$user" "$owner"; then

3151 : EDIT OK

3152 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3153 echo "本人か所有者しか編集できません." | html p

3154 return

3155 fi

3156 aid=`query "select id from article where rowid=$rowid;"`

3157 tmpout=$tmpd/editart.$$.out

3158 GF_ACTION="?replyblog+$blogrowid#$aid" \

3159 edittable $formdir/article.def article $rowid \

3160 > $tmpout

3161 rm -f /tmp/editart.out

3162 # Cannot use pipelining to m4 with genform() because of stdin stack

3163 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3164 -D_FORM_="syscmd(cat $tmpout)" \

3165 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3166 -D_BLOGS_= -D_DUMPTABLE_= \

3167 $layout/html.m4.html $layout/form+dump.m4.html

3168 }

3169 send2mem() {

3170 rowid=`getpar grp`

3171 rowid=${rowid%%[!0-9]*} # Cleaning

3172 if [ -z "$rowid" ]; then

3173 echo "グループが未指定です。" | html p

3174 return

3175 fi

3176 message=`getpar message`

3177 if [ -z "$message" ]; then

3178 echo "文章を入れてください。" | html p

3179 return

3180 fi

3181 grp=`getgroupbyid $rowid`

3182 members=`collectemail $grp`

3183 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3184 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3185 mailfrom="`gecos "$user"` <$mailfrom>"

3186 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3187 msg=$(cat<<-EOF

3188 $urlbase?grp+$rowid

3189 グループ $grp に所属する

3190 `gecos $user` さんよりメッセージ:

3191

3192 $message

3193 EOF

3194 )

3195 # smail rcpt subj (file)

3196 for m in $members; do

3197 echo "$msg" |

3198 MAIL_FROM=$mailfrom \

3199 SENDER=$noreply \

3200 REPLYTO=$mailfrom \

3201 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3202 smail "$m" "$sj"

3203 done

3204 cat<<EOF

3205 <p>以下のユーザに送信しました。</p>

3206 <pre>

3207 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3208 </pre>

3209 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3210 EOF

3211 }

3212 joingrpadmit() {

3213 # $1=yes/no $2=session-key

3214 if [ -z "$2" ]; then

3215 echo "bye bye" | html p; return

3216 fi

3217 t_usr=`session=$2 getpar user`

3218 t_grp=`session=$2 getpar group`

3219 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3220 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3221 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3222 echo "無効な加入依頼です。" | html p

3223 echo "有効期限が切れたか、

3224 他の管理者がいる場合は処理済みの可能性があります。" | html p

3225 return

3226 fi

3227 if ! isgrpowner "$user" "$t_grp"; then

3228 echo "グループ管理者のみの機能です。" | html p; return

3229 fi

3230 case $1 in

3231 yes) joingrp "$t_grp" "$t_usr" yes ;;

3232 no) joingrp "$t_grp" "$t_usr" no ;;

3233 *)

3234 echo "無効な指定です($1)。" | html p

3235 return ;;

3236 esac

3237 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3238 rcpts="`getgroupadminmails $t_grp` $user"

3239 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3240 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3241 に

3242 $t_usr

3243 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3244 しました。"

3245 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3246 query "delete from session where id='$2';"

3247 echo "$body" | html p

3248 }

3249

3250 joingrprequest() {

3251 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3252 jss="joingrp-`date +%s`-`genrandom 12`"

3253 addsession $jss +${memoplimitdays}days

3254 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3255 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3256 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3257 $url

3258 $user さんから

3259 グループ $1

3260 に加入依頼がありました。

3261

3262 承認する:

3263 $urlbase?joingrpadmit+yes+$jss

3264

3265 白紙に戻す:

3266 $urlbase?joingrpadmit+no+$jss

3267 EOF

3268 echo "管理者に加入依頼を出しました。

3269 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3270 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3271 }

3272 joingrp() {

3273 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3274 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3275 if isgrpowner "$user" "$1"; then

3276 isowner="yes"

3277 elif [ -n "$5" ]; then

3278 isowner="yes"

3279 else

3280 isowner=""

3281 fi

3282 ## err jg:isgrpowner: isowner="$isowner"

3283 if [ -n "$isowner" ]; then

3284 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3285 elif [ x"$2" != x"$user" ]; then # if user is not login user

3286 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3287 return

3288 elif [ x"$3" = x"no" ]; then

3289 : # Do not pursue those who leave

3290 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3291 : # Member can change own email address for the joining moderated group

3292 else # adding user is $user itself

3293 case `getgroupattr $1 regmode` in

3294 moderated)

3295 joingrprequest "$@" # Request only

3296 return

3297 ;;

3298 *)

3299 ;;

3300 esac

3301 fi

3302 qgname=`sqlquote "$1"`

3303 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3304 cond="where gname=$qgname and user='$2'"

3305 if [ x"$3" = x"yes" ]; then

3306 query "replace into grp_mem values($qgname, '$2');"

3307 # Notify joingrp to admin

3308 action="に加入しました。"

3309 if [ -n "$4" ]; then

3310 if msg=`emaildomaincheck "$4"`; then

3311 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3312 'string', '$4', NULL);"

3313 else

3314 echo $msg

3315 fi

3316 else

3317 query "delete from grp_mem_s $cond and key='email';"

3318 fi

3319 if [ -n "$5" ]; then # as ADMIN

3320 # Coming here means newly created group

3321 sql="select case\

3322 when (select count(*) from grp_mem where gname=$qgname)=1\

3323 then (select user from grp_mem\

3324 where gname=$qgname and user='$user')\

3325 else '' end; "

3326 err NewGrpChk: $sql

3327 if [ -n "`query \"$sql\"`" ]; then

3328 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3329 query "replace into grp_adm values($qgname, '$user');"

3330 fi

3331 fi

3332 else

3333 query "begin;

3334 delete from grp_mem $cond;

3335 delete from grp_mem_s $cond;

3336 delete from grp_mem_m $cond;

3337 delete from grp_adm $cond;

3338 delete from grp_adm_s $cond;

3339 delete from grp_adm_m $cond;

3340 end;"

3341 action="から脱退しました。"

3342 fi

3343 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3344 $url?grp+$grid

3345 $user (`gecos $user`)さんが

3346 グループ $1

3347 $action

3348 EOF

3349 }

3350 grp_add_team() (

3351 # $1=grp-rowid $2=team $3...=user-rowid(s)

3352 grp=`getgroupbyid $1`

3353 team=$2; shift; shift

3354 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3355 { echo "BEGIN;"

3356 for user; do

3357 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3358 '$grp',\

3359 (SELECT name FROM user WHERE rowid=$user),\

3360 'team', 'string', '$team');"

3361 done

3362 echo "END;"

3363 } | query

3364 )

3365 grp_rm_team() (

3366 # $1=grp-rowid $2=team $3...=user-rowid(s)

3367 grid=$1

3368 qgrp=$(sqlquote "`getgroupbyid $grid`")

3369 team=$2; shift; shift

3370 [ -z "$grid" -o -z "$team" ] && return

3371 { echo "BEGIN;"

3372 for user; do

3373 echo "DELETE FROM grp_mem_m\

3374 WHERE gname=$qgrp \

3375 AND user=(SELECT name FROM user WHERE rowid=$user)\

3376 AND key='team' AND val='$team';"

3377 done

3378 cat<<-EOF

3379 DELETE FROM blog_s

3380 WHERE rowid=(

3381 SELECT rowid

3382 FROM blog_s a

3383 WHERE key='team'

3384 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3385 AND NOT EXISTS (SELECT * FROM grp_mem_m

3386 WHERE key='team' AND val=a.val -- a.val=team

3387 AND gname = (SELECT val FROM blog_s b

3388 WHERE a.id=b.id AND key='owner')

3389 ));

3390 EOF

3391

3392 echo "END;"

3393 } | query

3394 )

3395 grp_reg_adm() {

3396 # $1=grp-rowid $2...=user-rowid

3397 grid=$1

3398 grp=`getgroupbyid "$1"`

3399 if [ -z "$grp" ]; then

3400 echo "無効なグループIDです" | html p; return

3401 fi

3402 if ! isgrpowner "$user" "$grp"; then

3403 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3404 fi

3405 shift

3406 for urid; do

3407 newadm=`query "select name from user where rowid=$urid;"`

3408 if [ -z "$newadm" ]; then

3409 echo "指定ユーザIDがおかしいようです。" | html p; return

3410 fi

3411 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3412 err ismember $newadm $grp

3413 if ismember $newadm "$grp"; then

3414 # OK, go ahead

3415 getgname="(select gname from grp where rowid=$grid)"

3416 query "replace into grp_adm values($getgname, '$newadm');"

3417 # confirm insertion

3418 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3419 if [ -n "`query \"$sql;\"`" ]; then

3420 echo "追加完了: $newadm" | html p

3421 else

3422 echo "追加失敗($1 $urid)" | html p

3423 fi

3424 fi

3425 showgroup $grid

3426 done

3427 }

3428 dt_rowhack() {

3429 # From: <TR>

3430 # ....

3431 # <TD>rowclass=foo</TD>

3432 # </TR>

3433 # To: <TR class="foo">....<TD>foo</TD></TR>

3434 sed -e '

3435 /^<TR>/ {

3436 :loop

3437 s/\n//

3438 N

3439 /<\/TR>/ {

3440 s/\n//

3441 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3442 n

3443 }

3444 $q

3445 b loop

3446 }'

3447 }

3448 dumptable() {

3449 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3450 # textのフィールドだけ全てダンプにしたほうがいいか

3451 # $DT_VIEW sets link

3452 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3453 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3454 VIEW=${DT_VIEW-replyblog}

3455 if [ -n "$VIEW" ]; then

3456 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3457 fi

3458 sqlfile=$tmpd/dump.sql

3459 : > $sqlfile # ensure to be empty

3460 printf '.mode html\n.header 1\n' > $sqlfile

3461 # $DT_CHLD=ChildTable:BindColumn

3462 if [ -n "$DT_CHLD" ]; then

3463 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3464 cat<<-EOF >> $sqlfile

3465 -- presql

3466 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3467 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3468 EOF

3469 # Speed up counting of new articles

3470 cat<<-EOF >> $sqlfile

3471 -- presql2

3472 DROP TABLE IF EXISTS _counts;

3473 CREATE TEMPORARY TABLE _counts AS

3474 SELECT $_i, count($_i) cnt

3475 FROM $_t GROUP BY $_i;

3476 /* Prepare NEW count table */

3477 CREATE TEMPORARY TABLE _target AS

3478 SELECT b.rowid trowid, b.id

3479 FROM "$2" b JOIN "$2_s" s

3480 ON b.id=s.id AND s.key='owner'

3481 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3482

3483 DROP TABLE IF EXISTS _children;

3484 CREATE TEMPORARY TABLE _children AS

3485 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3486 FROM (SELECT t.trowid, t.id $_i, a.id

3487 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3488 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3489

3490 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3491 DROP TABLE IF EXISTS _news;

3492 DROP VIEW IF EXISTS _news;

3493

3494 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3495 -- INSERT INTO _news

3496 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3497 CREATE TEMPORARY VIEW _news AS

3498 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3499 FROM (SELECT DISTINCT id FROM _target)

3500 a LEFT JOIN

3501 (SELECT $_i, count(ctime) newcnt

3502 FROM _children x

3503 WHERE ctime > coalesce((SELECT time from myacclog

3504 WHERE tblrowid=x.trowid),

3505 '1970-01-01')

3506 GROUP BY $_i) b

3507 ON a.id=b.$_i;

3508 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3509 EOF

3510 # REMOVE next line until 2019/5/1

3511 cntall="(select count($_i) from $_t where $_i=a.id)"

3512 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3513 # REMOVE next assignment until 2019/5/1

3514 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3515 and id in (select id from $_t where $_i=a.id) \

3516 and val > coalesce((select time from myacclog where \

3517 tblrowid=a.rowid),\

3518 '1970-01-01'))"

3519 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3520 cnt="$cntnew as '新着', $cntall as '総数',"

3521 dt_class=" td2r td3r dumpblogs"

3522 fi

3523 # Construct join expression

3524 eav="" scols=""

3525 pk=`gettblpkey $2`

3526 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3527 substr=${substr:-%s}

3528 for col in ${3:-`gettbl_s_cols $2`}; do

3529 valvar=val

3530 case $col in

3531 gecos) scols="$scols${scols:+, }${col#}"

3532 continue ;; # built-in column name

3533 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3534 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3535 case "$as" in

3536 *:*=*) cnd=${as#*:}

3537 h=${cnd%%=*} v=${cnd#*=}

3538 h=`sqlquotestr "$h"`

3539 v=`sqlquotestr "$v"`

3540 valvar="CASE val WHEN $h THEN $v END"

3541 as=${as%%:*} ;;

3542 esac

3543 ;;

3544 *) as=${col} ;;

3545 esac

3546 ss=`printf "$substr" "$valvar"`

3547 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3548 scols="$scols${scols:+, }b.$as"

3549 done

3550 #case author when '$user' then a.rowid else '---' end as ID,

3551 if [ -n "$DT_SQL" ]; then

3552 echo "$DT_SQL"

3553 else

3554 cat<<-EOF

3555 SELECT a.rowid as LINK, $cnt $scols

3556 FROM $2 a LEFT JOIN

3557 (SELECT $pk,$eav,

3558 max(CASE key

3559 WHEN 'owner'

3560 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3561 as gecos

3562 FROM ${2}_s c GROUP BY $pk)

3563 b ON a.$pk=b.$pk $4;

3564 EOF

3565 fi >> $sqlfile

3566 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3567 sqlog<<-EOF

3568 *** SQL-file: $sqlfile ***

3569 `cat $sqlfile`

3570 EOF

3571 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3572 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3573 cat $sqlfile >> tmp/sql

3574 # query ".read $sqlfile" > $tmpd/foo

3575 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3576 cp $tmpd/foo tmp/

3577 err "----- `gdate +%FT%T.%3N` ------------555555"

3578 ## $ddd LINE exists at the end of this function

3579 fi

3580 printf '.mode list\n.header 0\n' >> $sqlfile

3581 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3582 <div>

3583 <div class="dumptable">

3584 <table class="b$dt_class">

3585 `query ".read $sqlfile"`

3586 </table>

3587 </div>

3588 </div>

3589 EOF

3590 ### `query ".read $sqlfile"`

3591 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3592

3593 ## REMOVE THIS!

3594 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3595 }

3596

3597 par2table() (

3598 # copy current parameters of par into destination table

3599 # $1=definition-file

3600 # Using $user and $session

3601 # Return value:

3602 # 0: Stored successfully

3603 # 1: Insufficient fillings

3604 # 2: No permission to modify the record

3605 # 3: Invalid rowid

3606 # 4: SUCCESS to delete

3607 # 5: Stop deletion for lack of confirm check

3608 # 6: Password length too short

3609 # 7: Password mismatch

3610 # 8: Old password incorrect

3611 rowid=`getpar rowid`

3612 if [ ! -e $1 ]; then

3613 echo "テーブル定義ファイルが見付かりません" | html p

3614 exit 1

3615 fi

3616 tbl=${1%.def}

3617 tbl=${tbl##*/}

3618 if [ -n "$rowid" ]; then # Modify existing entry

3619 if [ x"$tbl" = x"user" ]; then

3620 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3621 elif [ x"$tbl" = x"grp" ]; then

3622 sql="select gname from $tbl where rowid=$rowid;"

3623 ##err p2t:grp:q $sql

3624 isgrpowner "$user" "`query $sql`" && rowowner=$user

3625 elif [ x"$tbl" = x"blog" ]; then

3626 # Check if owner in blog_s

3627 blogowner=`getvalbyid blog owner "$rowid"`

3628 if isgrpowner "$user" "$blogowner"; then

3629 rowowner=$user

3630 else

3631 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3632 fi

3633 else

3634 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3635 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3636 fi

3637 ### err rowowner=$rowowner

3638 if [ x"$user" != x"$rowowner" ]; then

3639 echo "他人のレコードはいじれないの" | html p

3640 return 2

3641 elif [ -z "$rowowner" ]; then

3642 echo "指定したレコードはないみたい" | html p

3643 return 3

3644 fi

3645 rm=`getpar rm` cfm=`getpar confirm`

3646 # Editing existent entry

3647 if [ x"$rm" = x"yes" ]; then

3648 if [ x"$rm$cfm" = x"yesyes" ]; then

3649 query "delete from $tbl where rowid=$rowid;"

3650 return 4

3651 else

3652 echo "消去確認のチェックがないので消さなかったの..." | html p

3653 return 5

3654 fi

3655 fi

3656 fi

3657

3658 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3659 if [ -n "$rowid" ]; then

3660 # Update of existing record

3661 for col in `gettblcols $tbl`; do

3662 val=`getparquote $col`

3663 [ -z "$val" ] && continue

3664 ## err query "update $tbl set $col=$val where rowid=$rowid"

3665 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3666 sql="update $tbl set $col=$val where rowid=$rowid;"

3667 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3668 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3669 ## User name cannot be changed with interface provided with this

3670 ## script. But we offer the trigger to change owner user

3671 ## of blog_s table.

3672 #err "select quote($col) from $tbl where rowid=$rowid;"

3673 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3674 cat<<-EOF | query

3675 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3676 -- should use EXCLUSIVE transaction outside of this.

3677 SAVEPOINT par2table;

3678 $sql

3679 update blog_s set val=$val

3680 where key='owner' and val=$old;

3681 RELEASE SAVEPOINT par2table;

3682 EOF

3683 ## XX: DIRTY Hack Ends here

3684 ## We should keep blog's owner as a single column which has

3685 ## foreign key constraint with primary key of grp/user.

3686 else

3687 query "$sql"

3688 fi

3689 done

3690 # Then, set up $pval for further insertion of tbl_s and tbl_m

3691 for col in `gettblpkey $tbl`; do

3692 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3693 pval="$pval${pval:+, }\"$val\""

3694 done

3695 else

3696 # New entry

3697 # Generate values() for primary keys

3698 for col in `gettblpkey $tbl`; do

3699 # Genuine primary keys for _m and _s

3700 val=`getvalquote $tbl $col`

3701 [ -z "$val" ] && continue

3702 pval="$pval${pval:+, }$val"

3703 done

3704 ##err pval=$pval

3705 for col in `gettblfkey $tbl`; do

3706 # args for values() to insertion into master table

3707 val=`getvalquote $tbl $col`

3708 [ -z "$val" ] && continue

3709 formaster=$formaster"${formaster:+, }$val"

3710 done

3711 formaster="$pval${formaster:+, }$formaster"

3712 ## err formaster=$formaster

3713 if [ -z "$formaster" ]; then

3714 echo "項目を全て埋めてください" | html pre

3715 return 1

3716 fi

3717 ## err "replace into $tbl values($formaster);"

3718 query "replace into $tbl values($formaster);"

3719 ## Insertion to master table, done

3720 fi

3721

3722 transaction=$tmpd/sqlfile.sql; touch $transaction

3723 for kt in s m; do

3724 tb2=${tbl}_$kt

3725 for col in `gettbl_${kt}_cols $tbl`; do

3726 ptype=`getpartype $col "limit 1"`

3727

3728 # First, check update of existing entries in _m

3729 if [ $kt = m ]; then

3730 # sessID|address.1.22|string|Somewhere-x.y.z

3731 sql=""

3732 ##err dots from query "select var from par where var like '$col.%';"

3733 for v in `query "select var from par where var like '$col.%';"`; do

3734 # v=address.1.22

3735 st_rowid=${v##*.}

3736 origcol=${v%%.*} # original column derived from

3737 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3738 ##case `getpartype $v` in

3739 ## err CASE `gettbl_coltype $tbl/$origcol` in

3740 ## err edit flag = `getpar action.$v`

3741 case `getpar action.$v` in

3742 rm)

3743 if [ x`getpar confirm.$v` = x"yes" ]; then

3744 newsql="delete from $tb2"

3745 else

3746 echo "削除確認未チェック" | html p

3747 fi ;;

3748 edit)

3749 case `gettbl_coltype $tbl/$origcol` in

3750 image|document|binary)

3751 file=$tmpd/`getparfilename $v`

3752 ## err type=file=$file

3753 [ -z "$file" ] && continue

3754 bn=`sqlquotestr "${file##*/}"`

3755 bin="X'"$(hexize "$file")"'"

3756 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3757 type=\"file:$ct\"

3758 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3759 cachedir=`getcachedir "$tbl/$rowid"`

3760 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3761 rm -rf $cachedir

3762 ;;

3763 *)

3764 newsql="update $tb2 set val=(select val from par where var \

3765 like '$col.%.$st_rowid')"

3766 ;;

3767 esac

3768 ;;

3769 *) # maybe "keep", do not modify value

3770 continue

3771 ;;

3772 esac

3773 # err newsql=$newsql

3774 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3775 done

3776

3777 if [ x"$bin" = x"NULL" ]; then

3778 ## err repl:normal sql=`echo $sql`

3779 if [ -n "$transaction" ]; then

3780 cat<<-EOF >> $transaction

3781 $sql

3782 DELETE FROM $tb2 WHERE type='string' AND val='';

3783 EOF

3784 else

3785 query "$sql

3786 delete from $tb2 where type='string' and val='';"

3787 ## err repl:normal done

3788 fi

3789 else

3790 # Binary update line is TOO LONG to pipelining

3791 sqlfile="$tmpd/sqlf.$$"

3792 if [ -n "$transaction" ]; then

3793 echo "$sql" >> $transaction

3794 else

3795 echo "$sql" > $sqlfile

3796 query ".read $sqlfile"

3797 fi

3798 fi

3799 # Rest of kt==m: set multiple mode

3800 nr=`getparcount $col`

3801 else

3802 nr=1 # for kt==s, number of records is 1

3803 fi

3804

3805 i=0

3806 while [ $i -lt $nr ]; do

3807 limit="limit 1 offset $i"

3808 i=$((i+1)) # increase beforehand against continue

3809 val=`getvalquote $tbl $col "$limit"`

3810 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3811 ## err $col=$val

3812 bin=NULL

3813 ## err partype$col=`getpartype $col "$limit"`

3814 case $ptype in

3815 file) file=$tmpd/`getparfilename $col "$limit"`

3816 ## err parfile-$col=$file

3817 [ -z "$file" ] && continue

3818 bin="X'"$(hexize "$file")"'"

3819 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3820 type=\"file:$ct\" ;;

3821 "*"*) continue ;; # foreign table

3822 *) type=\"string\" ;;

3823 esac

3824 case `gettbl_coltype $tbl/$col` in

3825 password) # special care for password

3826 # name={password,pswd1,pswd2}

3827 p1=`getpar pswd1 "$limit"`

3828 if [ -z "$p1" ]; then

3829 continue # SKIP password setting, if p1 is empty

3830 else

3831 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3832 ## err pswd=$pswd

3833 if pwcheck "$pswd"; then

3834 if [ x"$p1" = x"$p2" ]; then

3835 case "$p1" in

3836 ??????????*) ;;

3837 *) echo "パスワードは10字以上にしてください。" | html p

3838 return 6;;

3839 esac

3840 val="\"`echo $p1|mypwhash`\""

3841 else

3842 echo "2つの新パスワード不一致" | html p

3843 return 7

3844 fi

3845 else

3846 echo "旧パスワード違います" | html p

3847 return 8

3848 fi

3849 fi

3850 ;;

3851 esac

3852 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3853 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3854 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3855 if [ x"$bin" = x"NULL" ]; then

3856 ## err Normal-query: `echo $sql`

3857 if [ -n "$transaction" ]; then

3858 echo "$sql" >> $transaction

3859 else

3860 query "$sql"

3861 fi

3862 else

3863 sqlfile="$tmpd/query.$$"

3864 ## err sqlfile=`ls -lF $sqlfile`

3865 if [ -n "$transaction" ]; then

3866 echo "$sql" >> $transaction

3867 else

3868 echo "$sql" > $sqlfile

3869 query ".read $sqlfile"

3870 fi

3871 fi

3872 ## err p2t done

3873 done

3874 done

3875 done

3876 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3877 -- We cannot use transaction here, because groupupdate may use it.

3878 SAVEPOINT pa2table_insert;

3879 .read $transaction

3880 RELEASE SAVEPOINT pa2table_insert;

3881 EOF

3882 return $?

3883 ##err donee

3884 )

3885 genform() {

3886 # $1 = form definition file

3887 # $2, $3 (optional)= table name and ROWID

3888 # If $GF_VIEWONLY set and nonNull, output values without form

3889 # If $GF_ARGS set, use it as content-strings in the form

3890 # If $GF_OWNER set, use it as value of name="owner"

3891 # If $GF_STAGE set, use it as value of name="stage"

3892 forms="" hiddens="" rowid=$3

3893 if [ ! -e "$1" ]; then

3894 echo "そのようなデータベースはないようです($2)。" | html p

3895 return

3896 elif [ -n "$2" ]; then

3897 rec=`query "select * from $2 where rowid='$rowid';"`

3898 if [ -z "$rec" ]; then

3899 pk=`gettblpkey $2`

3900 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3901 rec=`query "select rowid from $2 where $pk='$rowid';"`

3902 rowid=$rec

3903 rec=$3

3904 fi

3905 if [ -z "$rec" ]; then

3906 echo "そんなレコードはないみたいね..." | html p

3907 return

3908 fi

3909 fi

3910 if [ -z "$GF_VIEWONLY" ]; then

3911 rm='<input id="rm" name="rm" type="checkbox"

3912 value="yes"><label for="rm">このエントリの削除</label>

3913 <span>ほんとうに消しますよ(確認)!

3914 <input name="confirm" type=checkbox value="yes">はい</span>'

3915 fi

3916 # Image Cache dir

3917 ## err genform: getcache=$2/$rowid

3918 td=`getcachedir "$2/$rowid"`

3919 while IFS=: read prompt name keytype type args; do

3920 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3921 sp="${args:+ }"

3922 form="" val=""

3923 if [ -n "$rowid" ]; then

3924 # err genform2a: Seeking for "$2.$name, type=$type"

3925 rawval=`getvalbyid $2 $name $rowid $td`

3926 val=`echo "$rawval"|htmlescape`

3927 ## err genform3a: getvalbyid $2 $name $rowid $td

3928 ## err genform3b: val="[$val]" type="$type"

3929 fi

3930 if [ -n "$GF_VIEWONLY" ]; then

3931 is_hidden "$2" "$name" && continue

3932 fi

3933 case "$type" in

3934 text*)

3935 cgiform=cgi_multi_$type

3936 if [ -s $td/$name.count -a -n "$val" ]; then

3937 form=`$cgiform $name $td`

3938 val=$(echo "$val"|

3939 while read fn; do

3940 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3941 </td></tr>$nl"

3942 done)

3943 val="<table>$nl$val$nl</table>"

3944 else

3945 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3946 form=`cgi_$type $name "$rawval" "$args"`

3947 fi

3948 ;;

3949 [Rr][Aa][Dd][Ii][Oo])

3950 fh="<label><input type=\"radio\" name=\"$name\""

3951 form="`echo $args|sed -e \

3952 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3953 ;;

3954 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3955 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3956 ;;

3957 [Ss][Ee][Ll][Ee][Cc][Tt])

3958 fh="<select name=\"$name\">$nl"

3959 form=$(for l in $args; do

3960 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3961 done)

3962 if [ -n "$val" ]; then

3963 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3964 fi

3965 form="$fh$form</select>"

3966 ;;

3967 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3968 if [ -s $td/$name.count ]; then

3969 form=`cgi_multi_file $name $td "$args"`

3970 if [ -n "$val" ]; then

3971 hrfb="$myname?showattc+$2_m"

3972 val=$(echo "$rawval" \

3973 | while read fn; do

3974 data=`percenthex "$td/$fn"`

3975 #ct=`cat $td/$fn.content-type`

3976 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3977 ri=`cat "$td/$fn.rowid"`

3978 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3979 #imgsrc="<img src=\"data:$ct,$data\">"

3980 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3981 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3982 done)

3983 fi

3984 else

3985 form="<input type=\"file\" name=\"$name\" $args>"

3986 if [ -n "$val" ]; then

3987 imgs=$(echo "$rawval"\

3988 |while read fn;do

3989 data=`percenthex "$td/$fn"`

3990 echo "<img src=\"data:image/png,$data\">$fn<br>"

3991 done)

3992 form=$form"<br>$imgs"

3993 val=$imgs # 2015-06-15

3994 else

3995 form="<input type=\"file\" name=\"$name\" $args>"

3996 fi

3997 fi

3998 ;;

3999 [Hh][Ii][Dd][Dd][Ee][Nn])

4000 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

4001 args="value=\"$GF_STAGE\""

4002 fi

4003 form="<input type=\"hidden\" name=\"$name\" $args>"

4004 prompt='' # Remove prompt

4005 ;;

4006 [Aa][Uu][Tt][Hh][Oo][Rr])

4007 [ -n "$GF_VIEWONLY" ] && continue

4008 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4009 prompt="" ;;

4010 [Oo][Ww][Nn][Ee][Rr])

4011 [ -n "$GF_VIEWONLY" ] && continue

4012 val=${GF_OWNER:-$val}

4013 val=${val:-$user}

4014 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4015 prompt="" ;;

4016 [Uu][Ss][Ee][Rr])

4017 # XXX: is null $user ok?

4018 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4019 [ -n "$GF_VIEWONLY" ] && continue

4020 form="$user"

4021 ;;

4022 [Pp]assword)

4023 [ -n "$GF_VIEWONLY" ] && continue

4024 form="`cgi_passwd`"

4025 val=""

4026 ;;

4027 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4028 [ -n "$GF_VIEWONLY" ] && continue

4029 if [ -z "$rowid" ]; then

4030 val=`genserial`

4031 fi

4032 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4033 prompt="" ;;

4034 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4035 prompt=""

4036 ;;

4037 parent|path|blog*)

4038 prompt=""

4039 ;;

4040 "*"*)

4041 tail=$tail"``"

4042 continue ;;

4043 esac

4044 if [ -n "$prompt" ]; then

4045 if [ -n "${GF_VIEWONLY}" ]; then

4046 form=$val

4047 else

4048 :

4049 fi

4050 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4051 else

4052 hiddens=$hiddens$nl"$form"

4053 fi

4054 done < $1

4055 # enctype="multipart/form-data"

4056 cat<<EOF

4057 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4058 ${rowid:+$rm}

4059 <table class="b $2">

4060 $forms

4061 </table>$hiddens

4062 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4063 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4064 EOF

4065 if [ -z $GF_VIEWONLY ]; then

4066 cat<<EOF

4067 <input type="submit" name="sub" value="OK">

4068 <input type="reset" name="res" value="Reset">

4069 EOF

4070 fi

4071 cat<<EOF

4072 $GF_ARGS</form>

4073 $tail

4074 EOF

4075 }

4076 edittable() {

4077 # $1=form-def $2=table $3 rowid

4078 genform "$@"

4079 }

4080 viewtable() {

4081 GF_VIEWONLY=1 genform "$@"

4082 }

4083 showattc() {

4084 # $1=table_m $2=rowid &optional $3=RawFlag

4085 ## err \$1=$1 \$2=$2 \$3=$3

4086 if ! isfilereadable $user $1 $2; then

4087 contenttype; echo

4088 echo "このファイルは管理者のみしか見られません" | html p

4089 putfooter; exit

4090 fi

4091 idir=`umask 002; mktempd` || exit 1

4092 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4093 bin=$idir/$myname-$$.bin

4094 sql="select quote(bin) from $1 where rowid='$2';"

4095 ## err showattc: sql: $sql

4096 sq $db "$sql" | unhexize > $bin

4097 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4098 type=${tv%//*} fn=${tv#*//}

4099 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4100 ct=${type#file:}

4101 case $ct in # all text/* changed to text/plain

4102 text/*)

4103 charset=`nkf -g $bin|cut -d' ' -f1`

4104 case $charset in

4105 ASCII*) charset="" ;;

4106 esac

4107 if [ -z "$3" ]; then

4108 ct="text/html${charset:+; charset=$charset}"

4109 link="?showattc+$1+$2+raw"

4110 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4111 | sed 's,^,<span></span>,' \

4112 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4113 -D_LINK_="$link" \

4114 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4115 exit $?

4116 fi

4117 ct="text/plain${charset:+; charset=$charset}"

4118 ;;

4119 esac

4120 contenttype "$ct"

4121 echo "Content-Disposition: filename=\"$fn\""

4122 echo "Content-Length: " `cat $bin | wc -c`; echo

4123 #echo "Content-Type: " ${type#file:}; echo

4124 cat $bin

4125 }

4126 #

4127 # Some default stupid handler on CGI values

4128 #

4129 default_storedb() {

4130 # ARG: $1=table-def-file

4131 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4132 tbl=`basename $1`

4133 tbl=${tbl%.def}

4134 cols="`grep :text $1|cut -d: -f2`"

4135 col=`echo "$cols"|head -1`

4136 vcol=`getpar $col`

4137 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4138 if [ -n "$vcol" ]; then

4139 par2table $1

4140 else

4141 return 2 # No insertion occurred

4142 fi

4143 }

4144

4145 default_view() { # $1=def-file

4146 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4147 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4148 default_storedb "$@"

4149 query "select rowid from $tbl order by rowid desc;" \

4150 | while read rowid; do

4151 viewtable $1 $tbl $rowid

4152 done | _m4 -D_TITLE_="$tbl" \

4153 -D_FORM_="`genform $1`" \

4154 -D_DUMPTABLE_="syscmd(cat)" \

4155 $layout/html.m4.html $layout/form+dump.m4.html

4156 }

4157 default_viewtext() { # $1=def-file

4158 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4159 default_storedb "$@"

4160 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4161 | _m4 -D_TITLE_="$tbl" \

4162 -D_FORM_="`genform $1`" \

4163 -D_DUMPTABLE_="syscmd(cat)" \

4164 $layout/html.m4.html $layout/form+dump.m4.html

4165 }

4166 default_smail() {

4167 default_storedb "$@"

4168 if [ $? -eq 2 ]; then

4169 _m4 -D_TITLE_="入力" \

4170 -D_FORM_="`genform $1`" \

4171 -D_DUMPTABLE_="" \

4172 $layout/html.m4.html $layout/form+dump.m4.html

4173 return

4174 fi

4175 cond=""

4176 for pk in `gettblpkey $tbl`; do

4177 pv=$(sqlquote "$(getpar $pk)")

4178 cond="$cond${cond:+ and }$pk=$pv"

4179 done

4180 sql="select rowid from $tbl where $cond;"

4181 rowid=`query "$sql"`

4182 ## err smail1 - "$sql" "-> rowid=$rowid"

4183

4184 while IFS=: read prompt name keytype type args; do # Read from $1

4185 val=`getpar $name`

4186 if [ -n "$val" ]; then

4187 text="$text

4188 $prompt

4189 $name=$val

4190 ---------------------------------------------------------"

4191 fi

4192 case "$type" in

4193 image|document|file)

4194 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4195 fns=$(echo "$fn"|while read fn; do

4196 err mv $tmpd/$fn.orig $tmpd/$fn

4197 mv $tmpd/$fn.orig $tmpd/$fn

4198 rm $tmpd/$fn.rowid # Remove cache flag

4199 ## err "`ls $tmpd/$fn`"

4200 echo $fn

4201 done)

4202 files="$files $fns"

4203 ;;

4204 esac

4205 done < $1

4206 ## err FILES=$files "`ls -lF $tmpd`"

4207 subj="from ${REMOTE_ADDR}"

4208 (echo "$url"

4209 echo "への書き込みがありました。"

4210 echo "------"

4211 echo "$text"

4212 ) | (cd $tmpd &&

4213 err LS="`ls -lF`" &&

4214 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4215 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4216 echo "以下の内容で送信しました。" | html p

4217 viewtable $1 $tbl \

4218 `query "select rowid from $tbl order by rowid desc limit 1;"`

4219 echo "戻る" | html a "href=\"?\""

4220 }