s4: f2971e549199 s4-funcs.sh

s4

view s4-funcs.sh @ 700:f2971e549199

New team name regularization fixed

author	HIROSE Yuuji <yuuji@gentei.org>
date	Fri, 22 May 2020 07:47:53 +0900
parents	b33d6e7730ac
children	2c5ddb0b5f4c

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 skey) val=$_skey ;;

678 esac

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

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

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

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

683 fi

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

685 case "$var" in

686 owner)

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

688 echo $user; return

689 elif ismember $user $val; then

690 echo $val; return

691 fi ;;

692 esac

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

694 echo "$val"

695 }

696 setskey() {

697 # For quick response...(?)

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

699 }

700 chkskey() {

701 # $1=sesskey, $user=LoginUserName

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

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

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

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

706 return 0

707 fi

708 return 1

709 }

710 resetskey() {

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

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

713 fi

714 }

715 getpartype() {

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

717 }

718 getparcount() {

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

720 }

721 getparfilename() {

722 # null if type of $1 is not file

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

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

725 }

726 sqlquote() {

727 (v="$1"

728 case "$v" in

729 "") return ;; # null

730 "X'"*) # quoted hex string

731 echo $1 ;;

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

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

734 echo "\"$v\""

735 return ;;

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

737 echo "\"$v\""

738 return ;;

739 *)

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

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

742 else

743 echo "\"$v\""

744 fi ;;

745 esac)

746 }

747 sqlquotestr() (

748 case "$1" in

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

750 echo "'$v'" ;;

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

752 esac

753 )

754 mktempd() {

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

756 }

757 getcachedir() { # $1=maintable

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

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

760 else

761 echo $tmpd/$thumbxy

762 fi

763 }

764 getval() {

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

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

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

768 echo "$user" ;;

769 stamp|datetime)

770 date "+%F %T" ;;

771 serial)

772 (s=`getpar $2`

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

774 *)

775 getpar "$2" "$3";;

776 esac

777 }

778

779 getvalquote() {

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

781 (v=`getval "$@"`

782 case "$v" in

783 "") echo NULL ;;

784 *) sqlquote "$v" ;;

785 esac)

786 }

787 getparquote() {

788 sqlquote "`getpar $1`"

789 }

790 getbinbyid() {

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

792

793 }

794 getvalbyid() {

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

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

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

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

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

800

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

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

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

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

805 return

806 fi

807 done

808 rowid=$3

809 pk=`gettblpkey $1`

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

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

812 td=${4:-$tmpd}

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

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

815 for kt in s m; do

816 t=${1}_$kt

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

818 vcount=1 # count(val)

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

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

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

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

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

824 if [ $kt = m ]; then

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

826 # Reset val to store filenames if type is string

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

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

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

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

831 i=0

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

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

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

835 i=$((i+1))

836 fn=$c.$i

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

838 case $type in

839 file:*)

840 #file=$td/$val

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

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

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

844 # FOR SPEED: Skip file generation if imgcache exists

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

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

847 && continue

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

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

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

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

852 select rowid from $cond $slice;

853 .output '$td/$fn'

854 select val from $cond $slice;

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

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

857 .output stdout

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

859 EOF

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

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

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

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

864 ls -lh "$file" |

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

866 case "$type" in

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

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

869 *|*:[Aa]pplication*)

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

871 png:- > "$file"

872 ;;

873 esac

874 ;;

875 *)

876 sq $db<<EOF

877 .output $td/$fn.rowid

878 select rowid from $cond $slice;

879 .output $td/$fn

880 select val from $cond $slice;

881 EOF

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

883 ;;

884 esac

885 done

886 else

887 rm -f $td/$c.count

888 case $type in

889 file:*)

890 echo "$val" \

891 | while read fn; do

892 file=$td/$fn

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

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

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

896 | unhexize > "$file"

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

898 case $type in

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

900 *:[Aa]pplication*)

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

902 png:- > $file ;;

903 esac

904 fi

905 done

906 ;;

907 esac

908 fi

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

910 return

911 fi

912 done

913 done)

914 }

915 getvalbypkey() (

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

917 pk=`gettblpkey $1`

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

919 getvalbyid "$1" "$2" $rowid $4

920 )

921 getvalbycond() {

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

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

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

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

926 getvalbyid "$1" "$2" $rowid "$4"

927 fi

928 }

929 getpwfield() {

930 # getpwfield user column

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

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

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

934 echo "$val"

935 return 0

936 else

937 return 1

938 fi

939 }

940 numericalize() {

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

942 }

943 encode() {

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

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

946 sha1=sha1

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

948 sha1=sha1sum

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

950 sha1=gsha1sum

951 fi

952 fi

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

954 }

955 enjpeg() {

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

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

958 cjpeg="cjpeg"

959 else

960 cjpeg="convert - jpeg:-"

961 fi

962 fi

963 $cjpeg "$@"

964 }

965 mycrypt() (

966 key=$1 salt=$2

967 # err \$2=$2

968 case $2 in

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

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

971 esac

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

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

974 )

975 hexize() {

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

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

978 hexize="xxd -p"

979 else

980 hexize_hd() {

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

982 }

983 hexize="hexize_hd"

984 fi

985 fi

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

987 }

988 unhexize() {

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

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

991 unhex="xxd -p -r"

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

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

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

995 EOF

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

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

998 # world writable...:)

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

1000 fi

1001 fi

1002 cat "$@" | $unhex

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

1004 }

1005 percenthex() {

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

1007 }

1008 htmlescape() {

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

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

1011 }

1012 enascii() {

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

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

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

1016 else

1017 enascii_now=`date +%FT%T`

1018 enascii_sed() {

1019 nkf -Z0Z1Z2 \

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

1021 }

1022 enascii="enascii_sed"

1023 fi

1024 fi

1025 cat "$@" | $enascii

1026 }

1027 size_h() {

1028 i="$1" oi=$1

1029 set -- B B KB MB GB TB

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

1031 oi=$i

1032 i=$((i/1024))

1033 shift

1034 done

1035 echo ${oi}$1

1036 }

1037 gettblconf() {

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

1039 ## tconfs=`sq $db \

1040 tconfs=`query \

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

1042 fi

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

1044 }

1045 gettblkeys() {

1046 # $1=tbl

1047 gettblconf

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

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

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

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

1052 case $type in

1053 *p*)

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

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

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

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

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

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

1060 esac

1061 case $type in

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

1063 esac

1064 done

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

1066 }

1067 gettblpkey() {

1068 # $1=tbl

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

1070 }

1071 gettblfkey() {

1072 (x=`gettblkeys $1`

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

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

1075 }

1076 gettblcols() {

1077 (x=`gettblkeys $1`

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

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

1080 }

1081 gettbl_s_cols() {

1082 (x=`gettblkeys $1`

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

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

1085 }

1086 gettbl_m_cols() {

1087 (x=`gettblkeys $1`

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

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

1090 }

1091 gettbl_h_cols() {

1092 (x=`gettblkeys $1`

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

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

1095 }

1096 gettbl_coltype() (

1097 gettblconf

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

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

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

1101 )

1102 is_hidden() {

1103 # $1=Tbl $2=col

1104 gettblconf

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

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

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

1108 case $x in

1109 *h*) return 0 ;;

1110 *) return 1 ;;

1111 esac

1112 }

1113

1114 dbsetbyid() {

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

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

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

1118 quotedp=$(sqlquotestr "$p")

1119 unset primary update

1120 gettblconf

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

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

1123 #err conf=$conf

1124 case ${conf#*=} in

1125 p*) primary=1 ;;

1126 f*) update=1 ;;

1127 u*) ;;

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

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

1130 esac

1131 #err t=$t

1132 type=string fn=""

1133 case $conf in

1134 */password)

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

1136 ;;

1137 */image*|*/document*)

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

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

1140 ;;

1141 esac

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

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

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

1145 if [ "$primary" ]; then

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

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

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

1149 elif [ "$update" ]; then

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

1151 else

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

1153 fi

1154 )

1155 }

1156 expire() (

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

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

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

1160 )

1161 addsession() {

1162 # expireをセット

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

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

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

1166 # Remove old session parameters

1167 now=`expire now`

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

1169 }

1170 gencookie() (

1171 for kv; do

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

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

1174 done

1175 )

1176 contenttype() {

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

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

1179 }

1180 putheader() {

1181

1182 }

1183 putfooter() {

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

1185 }

1186 getcookie() {

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

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

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

1190 case "$k" in

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

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

1193 esac

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

1195 done

1196 }

1197 genrandom() {

1198 # $1=columns (default: 10)

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

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

1201 }

1202 genserial() {

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

1204 }

1205 smail() {

1206 # smail rcpts subj (file)

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

1208 # $MAIL_FROM <- From: header value

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

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

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

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

1213 sender=${SENDER:-$admin}

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

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

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

1217 }

1218 smail_queue_flush() {

1219 # $1=timelimit

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

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

1222 SELECT rowid FROM smailq

1223 GROUP BY rcpts, subj

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

1225 EOF

1226 )

1227 for rid in $rowids; do

1228 sq $workdb \

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

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

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

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

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

1234

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

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

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

1238 .separator "\n" "------------------\n\n"

1239 SELECT time, text FROM smailq

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

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

1242 ORDER by time;

1243 EOF

1244 then

1245 echo rowid=$rid

1246 cat <<-EOF | sq $workdb

1247 DELETE FROM smailq

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

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

1250 EOF

1251 fi

1252 fi

1253 done

1254 }

1255 smail_queue() {

1256 # $1=Rcpts, $@=subj

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

1258 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1267 EOF

1268 )

1269 ## XXX: Adhoc load mitigation

1270 case "$now" in

1271 *[01])

1272 err flush_queue=$mintime

1273 smail_queue_flush ;;

1274 esac

1275 fi

1276 }

1277 setviastring() {

1278 table=par

1279 oifs="$IFS"

1280 IFS="&"

1281 for us in $1; do

1282 k=${us%%=*}

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

1284 setpar "$k" "string" "$v"

1285 done

1286 IFS="$oifs"

1287 }

1288 checkdomain() (

1289 # Check the validity of domain by referring DNS

1290 item=$1

1291 err checkdomain $1

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

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

1294 )

1295 pwcheck() {

1296 # $1=passwd

1297 dbpswd=`getpwfield $user pswd`

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

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

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

1301 }

1302 mypwhash() {

1303 mycrypt "`cat`" `genrandom 5`

1304 }

1305 wasureta() {

1306 user=$1

1307 if ! checkdomain $user; then

1308 contenttype; echo

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

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

1311 putfooter

1312 exit 0

1313 fi

1314 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1316 encpswd=`echo $newpswd|mypwhash`

1317 dbsetbyid user $user pswd "$encpswd"

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

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

1320 $msgdir/mail-newaccount.m4 \

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

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

1323 }

1324 checkauth() {

1325 user=`getpar user`

1326 skc=`getpar skey` # from cookie

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

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

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

1330 if chkskey "$skc"; then

1331 return 0

1332 fi

1333 fi

1334 pswd=`getpar pswd`

1335 quser=`sqlquotestr "$user"`

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

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

1338 return 4 # 4=server too heavy

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

1340 return 2 # 2=login fail

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

1342 wasureta "$user"

1343 return 1 # wasureta error

1344 fi

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

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

1347 if pwcheck "$pswd"; then

1348 newsession=`genrandom 50`

1349 if setskey "$user" "$newsession" &&

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

1351 gencookie "user=$user" "skey=$newsession"

1352 return 0

1353 else

1354 return 4 # Heavy load??

1355 fi

1356 fi

1357 return 2 # Password mismatch

1358 }

1359 showlogin() {

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

1361 test -z "$args" && resetskey

1362 s4name=${S4NAME:-s4}

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

1364 cat $layout/login.m4.html

1365 echo '</body></html>'

1366 ) | _m4 \

1367 -D_BODYCLASS_="login" \

1368 -D_TITLE_="$s4name" \

1369 -D_SYSNAME_="Welcome to $s4name" \

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

1371 exit 0

1372 }

1373 dologin() {

1374 checkauth

1375 st=$?

1376 if [ $st != 0 ]; then

1377 contenttype; echo

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

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

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

1381 showlogin # and EXIT

1382 fi

1383 }

1384

1385 # Do instant jobs here

1386 dbsetup

1387 trap cleanup INT HUP EXIT TERM PIPE

1388 # trap cleanup INT HUP

1389

1390 err() {

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

1392 }

1393

1394 cgiinit() {

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

1396 tmpf=tmp/stream

1397 tmpd=`tmpd=$tmpdir mktempd`

1398 tmpfiles=$tmpfiles" $tmpd"

1399 addsession $session

1400 getcookie

1401 case "$REQUEST_METHOD" in

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

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

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

1405 # cat > $tmpf # too much?

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

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

1408 s="`cat tmp/stream`"

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

1410 ;;

1411 esac

1412 case "$CONTENT_TYPE" in

1413 *boundary*)

1414 bndry=${CONTENT_TYPE#*boundary=}

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

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

1417 do

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

1419 #echo u=$us

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

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

1422 # err k=$k v=$v

1423 case "$k" in

1424 *:filename)

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

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

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

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

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

1430 :

1431 fi

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

1433 case "$mimetype" in

1434 [Ii]mage/x-xcf)

1435 bzip2 "$tmpd/$v"

1436 v=${v}.bz2

1437 ;;

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

1439 [Ii]mage/*)

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

1441 ;;

1442 esac

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

1444 then

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

1446 continue

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

1448 replpar text string \

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

1450 continue

1451 fi

1452 ;;

1453 *)

1454 type='string'

1455 ;;

1456 esac

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

1458 setpar "$k" "$type" "$v"

1459 done

1460 ;;

1461 *)

1462 setviastring "$s"

1463 ;;

1464 esac

1465 }

1466 email4group() {

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

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

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

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

1471 sql="WITH

1472 grpemails AS (

1473 SELECT gname, user, val email

1474 FROM grp_mem NATURAL JOIN grp_mem_s

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

1476 useremails AS (

1477 SELECT user.name, val email

1478 FROM user

1479 LEFT JOIN user_m

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

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

1482 FROM useremails u LEFT JOIN grpemails g

1483 ON u.name=g.user

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

1485 query "$sql"

1486 }

1487 email4groupbyuid() {

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

1489 g=$1; shift

1490 uids=`echo "$@"`

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

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

1493 email4group "$g" `query "$sql"`

1494 }

1495 myemail4group() {

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

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

1498 }

1499 collectmembersbyid() {

1500 # Collect user names of group specified by grid

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

1502 query "SELECT user FROM grp_mem \

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

1504 }

1505 collectmembersbyid() {

1506 # Collect user names of group name

1507 qgrp=`sqlquote "$1"`

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

1509 }

1510 collectgecosesbyid() {

1511 # Collect user gecoses of group

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

1513 query<<-EOF

1514 SELECT gecos

1515 FROM gecoses

1516 WHERE name IN (SELECT user FROM grp_mem

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

1518 EOF

1519 }

1520 collectemail() (

1521 # Collect email addresses for group $1

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

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

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

1525 for e; do

1526 if isuser "$e"; then

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

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

1529 else

1530 qgrp=`sqlquote "$e"`

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

1532 gmem="grp_mem"

1533 else

1534 tm=`sqlquote "$TEAM"`

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

1536 fi

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

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

1539 $gmem g left join grp_mem_s s

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

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

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

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

1544 query "$sql"

1545 fi

1546 done

1547 )

1548 sendinvitation() (

1549 # $1=email

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

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

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

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

1554 gecos=`gecos`

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

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

1557 _m4 -D_URL_="$urlbase" \

1558 -D_USER_="$name" \

1559 -D_EMAIL_="$1" \

1560 -D_REGIST_="$regist" \

1561 -D_ADMIN_="$admin" \

1562 $msgdir/mail-invite.m4 \

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

1564 return 0

1565 )

1566 emaildomaincheck() {

1567 case "$1" in

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

1569 *@*)

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

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

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

1573 return 2

1574 fi

1575 return 0

1576 ;;

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

1578 esac

1579 }

1580 invite() {

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

1582 case "$email" in

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

1584 *@*)

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

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

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

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

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

1590 elif sendinvitation $email; then

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

1592 else # Cannot be reached here

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

1594 fi ;;

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

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

1597 esac

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

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

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

1601 fi

1602 if [ -f $invite_policy ]; then

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

1604 else

1605 pol="$invite_policy"

1606 fi

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

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

1609 -D_INVITE_POLICY_="$pol" \

1610 $layout/html.m4.html $layout/invite.m4.html

1611 }

1612 regist() {

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

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

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

1616 echo "bye bye" | html p

1617 reutrn

1618 fi

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

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

1621 cat<<EOF

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

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

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

1625 EOF

1626 return

1627 fi

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

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

1630 # Fake login password to wasureta

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

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

1633 wasureta $email

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

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

1636 addsession $1 # for removal after 1 minute

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

1638 $layout/login.m4.html

1639 return

1640 }

1641 group_safename() {

1642 # Convert $1 to safe group name

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

1644 }

1645 groupupdate() {

1646 gname=`getpar gname`

1647 qgname=`sqlquote "$gname"`

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

1649 # See ALSO same job in showgroup()

1650 newgname=`group_safename "$gname"`

1651 err newgname=$newgname

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

1653 err NewGNAME: gname=$newgname

1654 gname=$newgname

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

1656 replpar gname string "$gname"

1657 fi

1658 # Name confliction check

1659 parow=`getpar rowid`

1660 ## err parow=$parow

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

1662 query "BEGIN EXCLUSIVE;"

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

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

1665 if [ $count -gt 0 ]; then

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

1667 query "END;"

1668 return

1669 fi

1670 par2table $formdir/grp.def

1671 query "END TRANSACTION;"

1672 # Remove orphan

1673 : <<EOF

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

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

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

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

1678 EOF

1679 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1683 sql="delete from blog where id in

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

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

1686 query "$sql";

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

1688 return # and return

1689 fi

1690 fi

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

1692 fi

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

1694 grid=$(query $sql)

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

1696 grp $grid

1697 }

1698 groupclone() {

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

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

1701 case "$1" in

1702 */noteam)

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

1704 esac

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

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

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

1708 return

1709 fi

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

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

1712 return

1713 fi

1714 i=0

1715 while true; do

1716 copy="-copy$i"

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

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

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

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

1721 i=$((i++))

1722 continue

1723 fi

1724 break

1725 done

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

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

1728 query<<-EOF

1729 BEGIN;

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

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

1732 SELECT $newqname, key, val

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

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

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

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

1737 -- Copy members and their configuration --

1738 REPLACE INTO grp_mem SELECT $newqname, user

1739 FROM grp_mem WHERE gname=$qgrp;

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

1741 FROM grp_mem_s WHERE gname=$qgrp;

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

1743 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1744 -- Copy administrators --

1745 REPLACE INTO grp_adm SELECT $newqname, user

1746 FROM grp_adm WHERE gname=$qgrp;

1747 COMMIT;

1748 EOF

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

1750 STOPCLONEMSG=1 groupconf "$newrowid"

1751 }

1752 groupman() {

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

1754

1755 GF_STAGE="grpconf"

1756 GF_STAGE=groupupdate

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

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

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

1760 -D_DUMPTABLE_="syscmd(cat)" \

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

1762 }

1763 userconf() {

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

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

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

1767 }

1768 groupconf() {

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

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

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

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

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

1774

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

1776 grp=`getgroupbyid $rowid`

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

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

1779 return

1780 fi

1781

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

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

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

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

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

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

1788 <div>

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

1790 <table>

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

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

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

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

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

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

1797 </table>

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

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

1800 </div>

1801 EOF

1802 fi

1803 }

1804 mems() {

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

1806 kwd=`getpar kwd`

1807 listmember $kwd

1808 }

1809 grps() {

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

1811 kwd=`getpar kwd`

1812 listgroup $kwd \

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

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

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

1816 $layout/form+dump.m4.html

1817 }

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

1819 gpg=`getpar grp`

1820 grid=${1:-$gpg}

1821 grp=`getgroupbyid "$grid"`

1822 ## . ./s4-blog.sh

1823 jg=`getpar joingrp`

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

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

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

1827 fi

1828 htmlheader=$layout/html.m4.html

1829 showgroup "$grid"

1830 }

1831 sql4interestblogs() {

1832 cat<<EOF

1833 CREATE TEMPORARY VIEW interestblogs AS

1834 SELECT blog.rowid rid, id, author

1835 FROM blog

1836 NATURAL JOIN

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

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

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

1840 WHEN (SELECT user FROM grp_mem

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

1842 THEN 0

1843 ELSE 1

1844 END;

1845 EOF

1846 }

1847 listnewblogsql() { # $1=user

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

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

1850 newdays=${newdays:-14}

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

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

1853 WHERE user='$user'

1854 AND tblrowid IN

1855 ($blogreadflagrowid,

1856 $blogcutoffflagrowid)),

1857 $basetime -- "0"

1858 );"`

1859 cat<<EOF

1860 `sql4interestblogs`

1861 WITH article_ctime as (

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

1863 FROM article join article_s s using(id)

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

1865 GROUP BY id

1866 ), blog_title_owner as (

1867 SELECT blg.rid brid, id,

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

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

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

1871 ), blogall as (

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

1873 ), news as (

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

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

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

1877 FROM blogall bl

1878 LEFT JOIN

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

1880 ON bl.brid=al.tblrowid

1881 WHERE atime < bl.ctime

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

1883 LIMIT 10

1884 ) SELECT brid LINK, "新着",

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

1886 ctime, title,

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

1888 FROM news;

1889 EOF

1890 }

1891

1892 search_form() {

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

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

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

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

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

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

1899 @week → 最近一週間

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

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

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

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

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

1905 auth=""

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

1907 case "$1" in

1908 author=*)

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

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

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

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

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

1914 ;;

1915 grp=*)

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

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

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

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

1920 ;;

1921 esac

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

1923 cat<<-EOF

1924 <div class="right">

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

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

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

1928

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

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

1931

1932 </form>

1933 </div>

1934 EOF

1935 }

1936

1937 imgsrc_cache() (

1938 # $1 = directory for cache'ing

1939 # $2 = table (user_m or grp_m)

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

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

1942 dir="$1" tbl="$2"

1943 keycond="$3"

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

1945 ORDER BY rowid DESC LIMIT 1"

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

1947 tmpf=$tmpd/imgsrc_cache.$$

1948 case "$4" in

1949 [Ss]) size=S ;;

1950 [Oo]) size=O ;;

1951 *) size=M ;;

1952 esac

1953 # ImageCache filename storing schema:

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

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

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

1957 valtype=`query "$sql0"`

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

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

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

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

1962 case "$filetype" in

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

1964 esac

1965 fi

1966 cacheimg_S=$dir/S_$filename

1967 cacheimg_M=$dir/M_$filename

1968 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1976 else

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

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

1979 fifo2=$fifo.2

1980 mkfifo $fifo $fifo2

1981 fmt=${filename##*.}

1982 ## [[ NOTE ]]

1983 ## a. convert oldimage newimage

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

1985 ## b is much smaller than a

1986 cat $tmpf | unhexize \

1987 | tee $fifo \

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

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

1990 | tee $fifo2 \

1991 | convert - "$cacheimg_M" &

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

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

1994 | convert - "$cacheimg_S" &

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

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

1997 echo '">'

1998 echo "$sum" > $sumfile

1999 fi

2000 ## Now preparing cache image, done.

2001 ## Store this information to DB

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

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

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

2005 query <<-EOF

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

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

2008 EOF

2009 )

2010

2011 showhome() {

2012 # $1=userRowIdToShow

2013 err showhome \$1=$1

2014 case "$1" in

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

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

2017 esac

2018 ## err ShowHome: uname=$uname

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

2020 gecos=`gecos "$uname"`

2021 ## err SH:gecos=$gecos

2022 GF_VIEWONLY=1

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

2024 search_form_args=""

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

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

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

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

2029 # Display folders

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

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

2032 and type like 'file:%';"

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

2034 nfile=`query "$sql"`

2035 # err nfile=$nfile

2036 if [ $nfile -gt 0 ]; then

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

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

2039 fi

2040 else

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

2042 GROUP BY user;"`

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

2044 search_form_args="author=$uname"

2045 fi

2046 . ./s4-blog.sh

2047

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

2049 search_form "$search_form_args" > $sf

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

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

2052 viewtable $formdir/user.def user $1

2053 echo "</div>"

2054 } > $pf

2055

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

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

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

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

2060 if true; then

2061 tbl=user_m

2062 enticond="name='$uname'"

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

2064 else

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

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

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

2068 echo '">'

2069 }

2070 fi > $imf

2071 fi

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

2073 val='$uname';"`

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

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

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

2077 listblog $uname > $bf

2078 ## unset ddd

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

2080

2081 hometail=$tmpd/tail.$$

2082 mkfifo $hometail

2083

2084 #Calling listgroupbytable, originally here

2085

2086 (

2087 # Display Most Recent Entry

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

2089 shortval=${shortval:-val}

2090

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

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

2093 ctime,

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

2095 (SELECT gecos FROM gecoses

2096 WHERE name=(SELECT val FROM blog_s

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

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

2099 FROM blog b

2100 JOIN

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

2102 FROM article a, article_s s

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

2104 GROUP BY blogid ORDER BY val DESC LIMIT 50

2105 ) m

2106 ON b.id=m.blogid;"

2107 # This should be as follows

2108 : <<EOF

2109 WITH arts AS(

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

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

2112 FROM article a NATURAL JOIN article_s s

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

2114 GROUP by s.id

2115 )

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

2117 FROM arts a0

2118 JOIN

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

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

2121 ORDER BY ctime DESC LIMIT 50;

2122 EOF

2123

2124 cat<<-EOF

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

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

2127 <div class="lcto">

2128 `DT_VIEW=replyblog dumptable html blog`

2129 </div>

2130 EOF

2131 unset DT_SQL

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

2133 # Display NEWS

2134 # 2016-06-26

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

2136 acclog blog $blogreadflagrowid

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

2138 fi

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

2140 sql=`listnewblogsql "$user"`

2141 # echo "$sql" > tmp/listnew

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

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

2144 cont=$((cont-1))

2145 err newcount=$cont

2146 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2153 `cgi_submit '確定'`

2154 `cgi_hidden read yes`

2155 EOF

2156 echo "$new10 "

2157 echo "</div>"

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

2159 acclog blog $blogcutoffflagrowid # for speed

2160 fi

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

2162 : # DT_SQL=

2163 fi

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

2165 #

2166 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2172 -D_NBLOG_="$nblog" \

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

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

2175 $layout/html.m4.html $layout/home.m4.html

2176

2177 # Record access log

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

2179 }

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

2181 contenttype; echo

2182 ## err commission: "$@"

2183 gname=`getgroupbyid $1`

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

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

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

2187 grp_reg_adm "$@"

2188 else

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

2190 fi

2191 }

2192 listgroupbytable() {

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

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

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

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

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

2198 NGsql="select distinct tag from\

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

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

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

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

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

2204 for tag in `query "$sql"`

2205 do

2206 ## err ListGrp: tag=$tag

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

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

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

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

2211 as rowid,

2212 gname,

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

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

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

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

2217 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2222 echo '</table>'

2223 done

2224 echo '</div>'

2225 }

2226 iconhref() (

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

2228 data=`percenthex "$1"`

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

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

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

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

2233 )

2234 iconhref2() (

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

2236 src=$1

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

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

2239 )

2240 listentry() (

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

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

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

2244 cond='' hiddens=''

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

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

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

2248 offset=$((offset-1))

2249 fi

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

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

2252 limit=$listentlimit

2253 dir=`getcachedir "$1"`

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

2255 hrb="$myname?home"

2256 deficon=person-default.png

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

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

2259 gcs=gecos

2260 else # if group

2261 hrb="$myname?grp"

2262 deficon=group-default.png

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

2264 gcs=name

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

2266 if [ -n "$tagline" ]; then

2267 tagconv=`echo $tagline|sed 's/$[^= :]*$=$[^= :]*$/-D\2=\1/g'`

2268 ## err tagconv=$tagconv

2269 fi

2270 fi

2271 if [ ! -d $dir ]; then

2272 mkdir -p $dir

2273 fi

2274 if [ ! -s $dir/$deficon ]; then

2275 convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon

2276 fi

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

2278 kwd=`echo $2 | tr -d '";\n' | tr -d "'"`

2279 cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"

2280 fi

2281 tag=`getpar tag` tag2=`getpar tag2`

2282 if [ x"$tag" = x"NULL" ]; then

2283 tag="" tag2=""

2284 fi

2285 if [ -n "$tag$tag2" ]; then

2286 tag=${tag:-$tag2}

2287 qtag=`sqlquote "$tag"`

2288 cond2="tag=$qtag"

2289 fi

2290 if [ -n "$cond1$cond2" ]; then

2291 cond="$cond1${cond2:+ AND $cond2}"

2292 cond="WHERE ${cond# AND }"

2293 fi

2294

2295 # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08

2296 # grpは呼出し元の動的スコープ変数でよくないな...

2297 ##qgrp=`sqlquote $grp`

2298 getgrp="(select gname from grp where rowid=${rowid:--1})"

2299 sql="select a.rowid, a.$link,

2300 coalesce(b.$gcs, a.$nm) as nick,

2301 quote(a.$nm) as qname,

2302 (SELECT val FROM ${tbl}_s

2303 WHERE $nm=a.$nm AND key='$iconcachekey') icon,

2304 coalesce(b.gecos, a.$nm) /* If group, concat (Nusers) */

2305 || case when a.$nm in (select gname from grp)

2306 then printf('(%d名)',

2307 (select count(user) from grp_mem where gname=a.$nm))

2308 else ' <'||a.$nm||'>'

2309 end

2310 as name,

2311 b.tag,

2312 case when a.$nm in (select user from grp_adm

2313 where gname=$getgrp) then '管理者'

2314 when '$user' in (select user from grp_adm where gname=a.$nm)

2315 then 'ADMIN'

2316 when '$user' in (select user from grp_mem where gname=a.$nm)

2317 then 'Member'

2318 when '$iamowner' = '' then ''

2319 else ',not='||a.rowid end as ownerlink,

2320 CASE '$entity'

2321 WHEN 'グループ'

2322 THEN coalesce(

2323 (SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),

2324 'open')

2325 ||

2326 CASE WHEN '$user'

2327 IN (SELECT user FROM grp_mem WHERE gname=a.$nm)

2328 THEN ' ismember'

2329 ELSE ''

2330 END

2331 ELSE 'user'

2332 END regmode

2333 from $tbl a left join

2334 (select $nm as name,

2335 max(case key when 'gecos' then val end) as gecos,

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

2337 max(case key when 'mtime' then val end) as mtime,

2338 max(case key when 'wtime' then val end) as wtime,

2339 max(case key when 'login' then val end) as login

2340 from ${tbl}_s group by $nm)

2341 b on a.$nm=b.name $cond $3

2342 order by b.wtime desc, b.login desc,

2343 b.mtime desc, b.tag desc, a.rowid asc"

2344 # Give precedence to newer maintained groups (2016-09-24)

2345 # Note that mtime is stored only in grp_s.

2346 ## err LE:sql.1="$sql"

2347 total=`query "with x as ($sql) select count(*) from x;"`

2348 echo "${entity} 一覧" | html h2

2349 echo '<div class="listentry">' # List-entry div

2350 # Show owner/member filter button

2351 METHOD=GET

2352 hiddens="$hiddens

2353 `cgi_hidden kwd \"$kwd\"`

2354 `cgi_hidden stage \"$stage\"`"

2355 if [ x"$tbl" = x"grp" ]; then

2356 args=`grep "^種別:" $formdir/grp.def | cut -d: -f5`

2357 fh="<select name=\"tag\">$nl"

2358 fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"

2359 for l in $args; do

2360 val=${l#*=} tname=${l%=*}

2361 if [ x"$val" = x"$tag" ]; then

2362 s=" selected"

2363 selectedtags="(種別[${tname}]のみ)"

2364 else

2365 s=""

2366 fi

2367 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2368 done

2369 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2370 cat<<-EOF

2371 <form action="$myname" method="$METHOD">

2372 </form>

2373 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2374 for="ismembtn">参加中以外隠す</label>

2375 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2376 for="isadmbtn">管理者参加以外隠す</label>

2377 EOF

2378 # limit=3

2379 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2380 fi

2381 if [ $total -gt $limit ]; then

2382 echo '<div>'

2383 METHOD=GET cgi_form $stage <<EOF

2384 $form

2385 <label>次の語を含む${entity}で検索:

2386 `cgi_text kwd "$kwd"`</label>

2387 $hiddens

2388 EOF

2389 echo '</div>'

2390 else

2391 echo $selectedtags | html p

2392 fi

2393 cat<<EOF

2394 <form action="$myname" method="$METHOD">

2395 <p>${total}件中の<input class="hidesub" type="text" name="start"

2396 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2397 <input type="submit" value="確定"></p>

2398 </form>

2399 EOF

2400 if [ $((offset+limit)) -lt $total ]; then

2401 nextbtn=$(

2402 cat<<EOF

2403 <div class="right clear"><form action="$myname" method="$METHOD">

2404 `cgi_submit 次の${limit}件`

2405 $hiddens

2406 `cgi_hidden offset $((offset + limit))`</form></div>

2407 EOF

2408 )

2409 fi

2410 if [ $offset -gt 0 ]; then

2411 prevbtn=$(

2412 cat<<EOF

2413 <form action="$myname" method="$METHOD">

2414 `cgi_submit 前の${limit}件`

2415 $hiddens

2416 `cgi_hidden offset $((offset - limit))`</form>

2417 EOF

2418 )

2419 fi

2420 pnbtn="$nextbtn$prevbtn"

2421 echo $pnbtn

2422

2423 ## err ListEntry: `echo "$sql"\;`

2424 # sq $db here??? 2016-11-28

2425 query "$sql limit $limit ${offset:+offset $offset};" \

2426 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2427 # err name=$name owner=$ownerp lnk=$lnk

2428 # err newlnk=$lnk regmode=$regmode

2429 icondir=$dir/$id

2430 # Pick up only last icon

2431 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2432 <p class=\"tag _$tag\">$tag</p>" \

2433 | _m4 $tagconv

2434 if [ -n "$NOSPEEDUP" ]; then

2435 files=`getvalbyid $tbl profimg $id $icondir`

2436 if [ -n "$files" ]; then

2437 icon=`echo "$files"|tail -1`

2438 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2439 else

2440 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2441 fi

2442 elif [ -n "$icon" -a -s "$icon" ]; then

2443 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2444 else

2445 cond="$nm=$qname"

2446 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2447 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2448 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2449 # err "img=[$img]"

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

2451 echo "<a href=\"$hrb+$lnk\">"

2452 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2453 echo "</a>"

2454 else

2455 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2456 fi

2457 fi

2458 echo "<br>$name${ownerp:+<br>($ownerp)}"

2459 echo "</div>"

2460 done

2461 echo "</div>" # End of List-entry div

2462 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2463 )

2464 listmember() {

2465 listentry user "$@"

2466 }

2467 listgroup() {

2468 listentry group "$@"

2469 }

2470 hexteams() { # $1=gname, $2(optional)=user

2471 cond=${2:+" AND user='$2'"}

2472 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2473 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2474 }

2475 showgroup() { # $1=group-rowid

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

2477 grid=`getpar grid`

2478 grid=${grid%%[!0-9]*}

2479 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2480 else

2481 grid=$1

2482 fi

2483 grp=`getgroupbyid $grid`

2484 qgrp=`sqlquote "$grp"`

2485 htmlgrp=`echo "$grp"|htmlescape`

2486 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2487 if isgroup "$grp"; then

2488 tf=$tmpd/title.$$

2489 sf=$tmpd/search.$$

2490 bodyclass=`query "SELECT val FROM grp_s

2491 WHERE gname=$qgrp AND key='regmode';"`

2492 if ismember "$user" "$grp"; then

2493 ismember="ismember"

2494 bodyclass="$bodyclass${bodyclass:+ }ismember"

2495 else

2496 ismember="" # bodyclass="group"

2497 fi

2498 bodyclass="$bodyclass grouphome"

2499 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2500 echo "グループ $htmlgrp" > $tf

2501

2502 showgroupsub $formdir/grp.def "$grid" | \

2503 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

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

2505 -D_BODYCLASS_="$bodyclass" \

2506 -D_DUMPTABLE_="" \

2507 $htmlheader $sf $layout/form+dump.m4.html

2508 # $htmlheader $layout/form+dump.m4.html

2509 # $htmlheader is defined in grp()

2510 else # if $grp is removed at par2table

2511 listgroup

2512 fi

2513 }

2514 showgroupsub() {

2515 # $1=def-file $2=group-rowid

2516 # Using $ismember

2517 rowid=$2

2518 grp=`getgroupbyid $2`

2519 qgrp=`sqlquote "$grp"`

2520 td=`getcachedir grp/"$2"`

2521 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

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

2523 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2524 #grp=`sq $db "select gname from grp where rowid=$grp"`

2525 echo "showgroupsub: invalid argument($1 $2)" | html p

2526 return

2527 fi

2528 val=`getvalbyid grp profimg $rowid $tmpd`

2529 enticond="gname=$qgrp"

2530 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

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

2532 cat<<-EOF

2533 <p class="groupimg">

2534 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2535 EOF

2536 fi

2537 echo "<div class=\"noprofimg\">"

2538 viewtable $1 grp $rowid

2539 echo "</div>"

2540 if isgrpowner "$user" "$grp"; then

2541 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2542 iamowner=$rowid

2543 colmd=" mode"

2544 fi

2545 if [ -n "$ismember" ]; then

2546 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2547 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2548 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2549 cat<<EOF

2550 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2551 <div class="fold clear">

2552 `cgi_checkbox send yes id="send"`<label

2553 for="send">グループ全員にメッセージ送信</label>

2554 <div>

2555 `cgi_textarea message "" "cols=60"`

2556 `cgi_submit 送信`

2557 `cgi_reset リセット`

2558 </div>

2559 `cgi_hidden grp $rowid`

2560 </div></form>

2561 EOF

2562 fi

2563 # 加入ボタン + 加入者リスト

2564 if [ -n "$ismember" ]; then

2565 ismem='checked' state="(参加中)"

2566 else

2567 nomem='checked' state="(現在非加入)"

2568 fi

2569 # このグループでの加入アドレス

2570 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2571 and key='email';"`

2572 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2573 ## and key='email';"

2574 ##err email=$eml

2575 cat <<EOF

2576 <div class="fold clear">

2577 `cgi_checkbox reg yes id="reg"`<label

2578 for="reg">自身の加入状態を操作する</label>$state

2579 <div>

2580 EOF

2581 cgi_form grp <<EOF

2582 <p>このグループに</p>

2583 <table class="b">

2584 <tr><th>メンバーとして</th><td>

2585 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2586 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2587 <tr><th>参加する場合のメイルアドレス<br>

2588 <small>(メインのアドレスとは違うものにする場合に記入<br>

2589 同じでよい場合は空欄に)</small></th>

2590 <td>`cgi_text email $eml`</td></tr>

2591 </table>

2592 `cgi_hidden grp $rowid`

2593 EOF

2594 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2595 echo "moderated (承認加入の)グループなので実際に参加できるのは

2596 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2597 fi

2598 echo '</div></div>'

2599 echo '<h2>話題一覧</h2>'

2600 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2601 cat<<-EOF

2602 <form class="summary" action="$myname" title="$thelp">

2603 `cgi_hidden owner "$grp"`

2604 `cgi_hidden kwd "@week"`

2605 `cgi_hidden stage searchart`

2606 `cgi_submit "一週間のまとめ"`

2607 </form>

2608 EOF

2609 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2610 colstate="state:稼動状態:frozen=rowclass=凍結"

2611 DT_CHLD=article:blogid \

2612 DT_QOWNER="$qgrp" \

2613 DT_VIEW=replyblog dumptable html blog \

2614 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2615

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

2617 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2618 cm="?commission+$rowid"

2619 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2620 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2621 # team list

2622 hexteams=`hexteams "$grp"`

2623 if [ -n "$hexteams" ]; then

2624 echo "チーム一覧" | html h2

2625 echo '<div class="dumptable"><table class="b">'

2626 sq $db -html -header<<-EOF

2627 SELECT val TEAM,

2628 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2629 MEMBERS

2630 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2631 EOF

2632 echo '</table></div>'

2633 fi

2634 }

2635 grp_getbodyclass() {

2636 # Get css class name for document.

2637 # `moderated' for moderated groups

2638 # `ismember' for groups where user belongs

2639 # $1=GroupName (w/o quote)

2640 # $user=userNameCurrentlyLogin

2641 ## err grp_getbodyclass: 1="$1"

2642 qgrp=`sqlquote "$1"`

2643 query<<-EOF

2644 SELECT coalesce(

2645 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2646 'open')

2647 ||

2648 CASE WHEN '$user'

2649 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2650 THEN ' ismember'

2651 ELSE ''

2652 END;

2653 EOF

2654 }

2655 grpaction() { # $1=group-rowid

2656 err GRP_ACTION:IN

2657 grid=${1:-`getpar grp`}

2658 grp=`getgroupbyid "$grid"`

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

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

2661 echo "無効な指定です。" | html p; return

2662 fi

2663 if ! ismember $user "$grp"; then

2664 echo "加入者のみに許可された操作です。" | html p; return

2665 fi

2666 echo "グループ $grp 個別選択操作" \

2667 | _m4 -D_TITLE_="syscmd(\`cat')" \

2668 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2669 $layout/html.m4.html

2670

2671 isowner=""

2672 isgrpowner "$user" "$grp" && isowner="yes"

2673 usel=`getpar usel`

2674 if [ -n "$usel" ]; then

2675 uids=$(echo `echo $usel`|tr ' ' ',')

2676 ## err grpaction-1: grp=$grp, `echo $sql`

2677 text=`getpar text|tr -d '\r'`

2678

2679 rm=`getpar rm` cfm=`getpar confirm`

2680 ## err rm=$rm cfm=$cfm

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

2682 if [ "$isowner" ]; then

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

2684 # Eliminate

2685 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2686 for tbl in grp_mem grp_mem_s grp_mem_m; do

2687 sql="delete from $tbl $cond;"

2688 # echo "sql=$sql"

2689 query "$sql"

2690 err rmGRPuser "$sql"

2691 done

2692 num=`query "select count(*) from user where rowid in ($uids);"`

2693 #err num=$num

2694 if [ 0$num -gt 0 ]; then

2695 sql="select coalesce(b.val,a.name) from user a left join \

2696 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2697 # err `echo "$sql"`

2698 html pre<<EOF

2699 以下の${num}名のグループ $grp 登録を解除しました。

2700 `query "$sql"`

2701 EOF

2702 fi

2703 else

2704 echo "確認のチェックがないのでやめておきます。" | html p

2705 return

2706 fi

2707 else # not Group Owner

2708 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2709 return

2710 fi

2711 cat<<EOF

2712

2713 EOF

2714 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2715 if [ -z "$text" ]; then # if msg is empty

2716 echo "なにかメッセージを..." | html p

2717 return 0

2718 fi

2719 gecos=`gecos $user`

2720 safegc=`echo "$gecos" | tr -d '<>@'`

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

2722 fromad=`myemail4group "$grp"`

2723 ###mail_from="$safegc <$fromad>"

2724 mail_from="$safegc <$user>" # TEST: 2020/5/13

2725 test -n `getpar sender` &&

2726 export SENDER=$user # TEST: 2020/5/15

2727 replyto=$fromad

2728

2729 ## Start parse of attachment files

2730 if [ -n "`getpar email`" ]; then

2731 ar=`getpar supprcpt`

2732 if [ -n "$ar" ]; then

2733 for a in $ar; do

2734 checkdomain "$a" && supprcpt="$supprcpt $a"

2735 done

2736 fi

2737 subj=`getpar subject`

2738 afiles=""

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

2740 do

2741 f=$tmpd/$fn

2742 if [ -s $f ]; then

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

2744 fi

2745 done

2746 else

2747 preface=$(cat <<-EOF

2748 $url

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

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

2751 ----------------------------------------------------------

2752 EOF

2753 )

2754 fi

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

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

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

2758 REPLYTO=$replyto

2759 MAIL_FROM=$mail_from

2760 export REPLYTO SMAIL_TO MAIL_FROM

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

2762 for r in $rcpts; do

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

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

2765 else

2766 # Show sender and rcpts address for guest

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

2768 fi

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

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

2771 else

2772 smail "$r" "$subj"

2773 fi <<EOF

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

2775 EOF

2776 done

2777 if [ $? = 0 ]; then

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

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

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

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

2782 html pre<<EOF

2783 `query "$sql"`

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

2785 EOF

2786 err SendDone: `echo $sql`

2787 fi

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

2789 grp_reg_adm $grid $usel

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

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

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

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

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

2795 team=$newteam

2796 fi

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

2798 cat<<-EOF | html p

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

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

2801 EOF

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

2803 else

2804 grp_add_team $grid "$team" $usel

2805 fi

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

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

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

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

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

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

2812 grp_rm_team $grid "$rmteam" $usel

2813 else

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

2815 fi

2816 else

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

2818 fi

2819 fi

2820 fi

2821 # POST count summary

2822 from=`getpar from`; to=`getpar to`

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

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

2825 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2826 # New entry

2827 sql="WITH mems AS (

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

2829 ON gm.user=g.name

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

2831 ), target_article AS (

2832 SELECT id FROM article_s

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

2834 ), posts AS (

2835 SELECT author, count(author) post

2836 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2837 WHERE blogid IN (SELECT id FROM blog_s

2838 WHERE key='owner'

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

2840 AND key='text'

2841 GROUP BY author

2842 ), teams AS (

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

2844 FROM grp_mem_m

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

2846 AND key='team'

2847 GROUP BY user

2848 ), user_post AS (

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

2850 FROM mems m LEFT JOIN posts

2851 ON m.name=posts.author

2852 GROUP by m.rowid

2853 )

2854 SELECT

2855 CASE

2856 WHEN (SELECT user FROM grp_adm

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

2858 AND user=up.name) IS NOT NULL

2859 then 'k'

2860 ELSE ''

2861 END || rowid || ',' || gecos NAME,

2862 post POST, team _TEAM_

2863 FROM user_post up LEFT JOIN teams t

2864 ON up.name=t.user

2865 ORDER BY gecos;"

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

2867 tf=$tmpd/title.$$

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

2869 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

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

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

2872 </p></div>"

2873 excmsg="`cgi_radio rm yes id=\"conf\"`<label

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

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

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

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

2878 </div>"

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

2880 allhexteams=$(hexteams "$grp")

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

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

2883 else

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

2885 fi

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

2887 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

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

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

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

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

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

2893 </div>

2894 "

2895 fi

2896 stf=$tmpd/selteam.$$

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

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

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

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

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

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

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

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

2905 cgi_form grpaction<<EOF \

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

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

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

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

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

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

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

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

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

2915 <div class="foldtabs">

2916 `cgi_radio rm addteam 'id="cmteam"'`<label

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

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

2919 `cgi_datalist_h teams $allhexteams`

2920 </div>

2921 ${rmteammsg}

2922 `cgi_radio rm send id="sendmsg"`<label

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

2924 <div>

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

2926 >email書式を使う</label>

2927 <div class="folded">

2928 <table>

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

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

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

2932 </td></tr>

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

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

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

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

2937 </table>

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

2939 </div>

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

2941 </div>

2942 </div>

2943 ${isowner:+$cmmsg$excmsg}

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

2945 </div>

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

2947 <table class="td2r thl">

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

2949 </table>

2950 `cgi_hidden grp $grid`

2951 `cgi_hidden myuid $myuid id="myuid"`

2952 EOF

2953 }

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

2955 # Create TEMPORARY VIEW

2956 query<<EOF

2957 CREATE TEMPORARY VIEW writeusers AS

2958 SELECT DISTINCT author FROM article

2959 WHERE id in (

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

2961 );

2962 CREATE TEMPORARY VIEW movablegroups AS

2963 SELECT g.rowid growid , g.gname

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

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

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

2967 WHERE (SELECT author FROM writeusers

2968 EXCEPT

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

2970 IS NULL;

2971 $2

2972 EOF

2973 }

2974 sql4readableblogs() {

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

2976 # Blog is readable when:

2977 # 1: blog owner is an user

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

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

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

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

2982 CREATE TEMPORARY VIEW readableblogs AS

2983 SELECT blog.rowid rid, id, author

2984 FROM blog

2985 NATURAL JOIN

2986 (SELECT id,

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

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

2989 FROM blog_s GROUP by id) bs

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

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

2992 WHEN (SELECT val FROM grp_s

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

2994 AND

2995 (SELECT user FROM grp_mem

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

2997 THEN 0

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

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

3000 ELSE 1

3001 END;

3002 EOF

3003 }

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

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

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

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

3008 return

3009 fi

3010 owner=`getvalbyid blog owner $rowid`

3011 title=`getvalbyid blog title $rowid`

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

3013 | _m4 -D_TITLE_="修正" \

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

3015 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

3018 # Move to group

3019 if isuser "$owner"; then

3020 crview4article $rowid

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

3022 ## err N=$n

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

3024 ## err ROWID=$rowid

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

3026 cat<<-EOF

3027 <div class="fold">

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

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

3030 <div>

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

3032 移動先グループ:

3033 <select name="mv2grp">

3034 EOF

3035 query ".mode html"

3036 query<<-EOF |

3037 $sql

3038 .mode list

3039 EOF

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

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

3042 cat<<-EOF

3043 </select>

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

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

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

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

3048 `cgi_hidden blogrowid $rowid`

3049 `cgi_submit 移動`

3050 `cgi_reset Reset`

3051 </form>

3052 </div>

3053 </div>

3054 EOF

3055 fi

3056 # end of isuser "$owner"

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

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

3059 none="`echo なし|hexize`"

3060 cat<<-EOF

3061 <div class="fold">

3062 `cgi_checkbox mv2team send id="mv2team"`<label

3063 for="mv2team">この話題を以下のチームのものにする</label>

3064 <div><p>現在の所属チーム設定:

3065 `query "SELECT

3066 coalesce((SELECT val FROM blog_s

3067 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3068 AND key='team'),

3069 ':なし');"`</p>

3070 <form action="?mvart" method="POST" enctype="multipart/form-data">

3071 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3072 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3073 `cgi_hidden blogrowid $rowid`<br>

3074 `cgi_submit 移動`

3075 `cgi_reset Reset`

3076 </form></div></div>

3077 EOF

3078 fi

3079 }

3080 mvart() { # move diary to some group or team

3081 # or move blog of group to team which belong to the group

3082 blogrowid=`getpar blogrowid`

3083 cfm=`getpar cfm`

3084 ##### echo move blog:$blogrowid to $mv2grp | html p

3085 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3086 . ./s4-blog.sh

3087 if [ -z "$blogrowid" ]; then

3088 echo "無効な指定です(mvart)。" | html p

3089 return

3090 elif [ x"$cfm" != x"yes" ]; then

3091 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3092 elif { mv2grp=`getpar mv2grp`

3093 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3094 [ -n "$mv2grp" ]; }; then

3095 crview4article $blogrowid

3096 ########## TRANSACTION BEGIN

3097 query "BEGIN;"

3098 n=`query "SELECT count(*) FROM writeusers;"`

3099 ## err Nwriteuser=$n

3100 if [ $((n)) -gt 0 ]; then

3101 query<<-EOF

3102 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3103 WHERE key='owner'

3104 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3105 AND $mv2grp IN (SELECT growid FROM movablegroups);

3106 EOF

3107 fi

3108 query "END;"

3109 ########## TRANSACTION END

3110 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3111 [ -n "$mv2team" ];}; then

3112 # blog owner can move it to ANY team

3113 case "$mv2team" in

3114 'なし')

3115 cat<<-EOF

3116 DELETE FROM blog_s

3117 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3118 AND key='team';

3119 EOF

3120 ;;

3121 "") ;;

3122 *)cat<<-EOF

3123 BEGIN;

3124 REPLACE INTO blog_s(id, key, val)

3125 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3126 'team', '$mv2team');

3127 REPLACE INTO blog_s(id, key, val)

3128 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3129 'notify', 'all'); -- Change notify to all

3130 END;

3131 EOF

3132 esac | query

3133 fi

3134 blog_reply $blogrowid

3135 echo yes | html p

3136 }

3137 editart() { # $1=article-rowid $2=blogrowid

3138 rowid=${1%%[!A-Z0-9a-z_]*}

3139 blogrowid=${2%%[!A-Z0-9a-z_]*}

3140 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3141 echo "表示する記事番号が未指定です。" | html p

3142 return

3143 fi

3144 owner=`getvalbyid blog owner $blogrowid`

3145 title=`getvalbyid blog title $blogrowid`

3146 author=`getvalbyid article author $rowid`

3147 ## err EDITart: owner=$owner, author=$author

3148 if isgrpowner "$user" "$owner"; then

3149 : EDIT OK

3150 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3151 echo "本人か所有者しか編集できません." | html p

3152 return

3153 fi

3154 aid=`query "select id from article where rowid=$rowid;"`

3155 tmpout=$tmpd/editart.$$.out

3156 GF_ACTION="?replyblog+$blogrowid#$aid" \

3157 edittable $formdir/article.def article $rowid \

3158 > $tmpout

3159 rm -f /tmp/editart.out

3160 # Cannot use pipelining to m4 with genform() because of stdin stack

3161 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3162 -D_FORM_="syscmd(cat $tmpout)" \

3163 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3164 -D_BLOGS_= -D_DUMPTABLE_= \

3165 $layout/html.m4.html $layout/form+dump.m4.html

3166 }

3167 send2mem() {

3168 rowid=`getpar grp`

3169 rowid=${rowid%%[!0-9]*} # Cleaning

3170 if [ -z "$rowid" ]; then

3171 echo "グループが未指定です。" | html p

3172 return

3173 fi

3174 message=`getpar message`

3175 if [ -z "$message" ]; then

3176 echo "文章を入れてください。" | html p

3177 return

3178 fi

3179 grp=`getgroupbyid $rowid`

3180 members=`collectemail $grp`

3181 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3182 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3183 mailfrom="`gecos "$user"` <$mailfrom>"

3184 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3185 msg=$(cat<<-EOF

3186 $urlbase?grp+$rowid

3187 グループ $grp に所属する

3188 `gecos $user` さんよりメッセージ:

3189

3190 $message

3191 EOF

3192 )

3193 # smail rcpt subj (file)

3194 for m in $members; do

3195 echo "$msg" |

3196 MAIL_FROM=$mailfrom \

3197 SENDER=$noreply \

3198 REPLYTO=$mailfrom \

3199 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3200 smail "$m" "$sj"

3201 done

3202 cat<<EOF

3203 <p>以下のユーザに送信しました。</p>

3204 <pre>

3205 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3206 </pre>

3207 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3208 EOF

3209 }

3210 joingrpadmit() {

3211 # $1=yes/no $2=session-key

3212 if [ -z "$2" ]; then

3213 echo "bye bye" | html p; return

3214 fi

3215 t_usr=`session=$2 getpar user`

3216 t_grp=`session=$2 getpar group`

3217 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3218 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3219 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3220 echo "無効な加入依頼です。" | html p

3221 echo "有効期限が切れたか、

3222 他の管理者がいる場合は処理済みの可能性があります。" | html p

3223 return

3224 fi

3225 if ! isgrpowner "$user" "$t_grp"; then

3226 echo "グループ管理者のみの機能です。" | html p; return

3227 fi

3228 case $1 in

3229 yes) joingrp "$t_grp" "$t_usr" yes ;;

3230 no) joingrp "$t_grp" "$t_usr" no ;;

3231 *)

3232 echo "無効な指定です($1)。" | html p

3233 return ;;

3234 esac

3235 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3236 rcpts="`getgroupadminmails $t_grp` $user"

3237 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3238 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3239 に

3240 $t_usr

3241 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3242 しました。"

3243 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3244 query "delete from session where id='$2';"

3245 echo "$body" | html p

3246 }

3247

3248 joingrprequest() {

3249 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3250 jss="joingrp-`date +%s`-`genrandom 12`"

3251 addsession $jss +${memoplimitdays}days

3252 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3253 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3254 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3255 $url

3256 $user さんから

3257 グループ $1

3258 に加入依頼がありました。

3259

3260 承認する:

3261 $urlbase?joingrpadmit+yes+$jss

3262

3263 白紙に戻す:

3264 $urlbase?joingrpadmit+no+$jss

3265 EOF

3266 echo "管理者に加入依頼を出しました。

3267 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3268 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3269 }

3270 joingrp() {

3271 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3272 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3273 if isgrpowner "$user" "$1"; then

3274 isowner="yes"

3275 elif [ -n "$5" ]; then

3276 isowner="yes"

3277 else

3278 isowner=""

3279 fi

3280 ## err jg:isgrpowner: isowner="$isowner"

3281 if [ -n "$isowner" ]; then

3282 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3283 elif [ x"$2" != x"$user" ]; then # if user is not login user

3284 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3285 return

3286 elif [ x"$3" = x"no" ]; then

3287 : # Do not pursue those who leave

3288 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3289 : # Member can change own email address for the joining moderated group

3290 else # adding user is $user itself

3291 case `getgroupattr $1 regmode` in

3292 moderated)

3293 joingrprequest "$@" # Request only

3294 return

3295 ;;

3296 *)

3297 ;;

3298 esac

3299 fi

3300 qgname=`sqlquote "$1"`

3301 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3302 cond="where gname=$qgname and user='$2'"

3303 if [ x"$3" = x"yes" ]; then

3304 query "replace into grp_mem values($qgname, '$2');"

3305 # Notify joingrp to admin

3306 action="に加入しました。"

3307 if [ -n "$4" ]; then

3308 if msg=`emaildomaincheck "$4"`; then

3309 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3310 'string', '$4', NULL);"

3311 else

3312 echo $msg

3313 fi

3314 else

3315 query "delete from grp_mem_s $cond and key='email';"

3316 fi

3317 if [ -n "$5" ]; then # as ADMIN

3318 # Coming here means newly created group

3319 sql="select case\

3320 when (select count(*) from grp_mem where gname=$qgname)=1\

3321 then (select user from grp_mem\

3322 where gname=$qgname and user='$user')\

3323 else '' end; "

3324 err NewGrpChk: $sql

3325 if [ -n "`query \"$sql\"`" ]; then

3326 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3327 query "replace into grp_adm values($qgname, '$user');"

3328 fi

3329 fi

3330 else

3331 query "begin;

3332 delete from grp_mem $cond;

3333 delete from grp_mem_s $cond;

3334 delete from grp_mem_m $cond;

3335 delete from grp_adm $cond;

3336 delete from grp_adm_s $cond;

3337 delete from grp_adm_m $cond;

3338 end;"

3339 action="から脱退しました。"

3340 fi

3341 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3342 $url?grp+$grid

3343 $user (`gecos $user`)さんが

3344 グループ $1

3345 $action

3346 EOF

3347 }

3348 grp_add_team() (

3349 # $1=grp-rowid $2=team $3...=user-rowid(s)

3350 grp=`getgroupbyid $1`

3351 team=$2; shift; shift

3352 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3353 { echo "BEGIN;"

3354 for user; do

3355 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3356 '$grp',\

3357 (SELECT name FROM user WHERE rowid=$user),\

3358 'team', 'string', '$team');"

3359 done

3360 echo "END;"

3361 } | query

3362 )

3363 grp_rm_team() (

3364 # $1=grp-rowid $2=team $3...=user-rowid(s)

3365 grid=$1

3366 qgrp=$(sqlquote "`getgroupbyid $grid`")

3367 team=$2; shift; shift

3368 [ -z "$grid" -o -z "$team" ] && return

3369 { echo "BEGIN;"

3370 for user; do

3371 echo "DELETE FROM grp_mem_m\

3372 WHERE gname=$qgrp \

3373 AND user=(SELECT name FROM user WHERE rowid=$user)\

3374 AND key='team' AND val='$team';"

3375 done

3376 cat<<-EOF

3377 DELETE FROM blog_s

3378 WHERE rowid=(

3379 SELECT rowid

3380 FROM blog_s a

3381 WHERE key='team'

3382 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3383 AND NOT EXISTS (SELECT * FROM grp_mem_m

3384 WHERE key='team' AND val=a.val -- a.val=team

3385 AND gname = (SELECT val FROM blog_s b

3386 WHERE a.id=b.id AND key='owner')

3387 ));

3388 EOF

3389

3390 echo "END;"

3391 } | query

3392 )

3393 grp_reg_adm() {

3394 # $1=grp-rowid $2...=user-rowid

3395 grid=$1

3396 grp=`getgroupbyid "$1"`

3397 if [ -z "$grp" ]; then

3398 echo "無効なグループIDです" | html p; return

3399 fi

3400 if ! isgrpowner "$user" "$grp"; then

3401 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3402 fi

3403 shift

3404 for urid; do

3405 newadm=`query "select name from user where rowid=$urid;"`

3406 if [ -z "$newadm" ]; then

3407 echo "指定ユーザIDがおかしいようです。" | html p; return

3408 fi

3409 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3410 err ismember $newadm $grp

3411 if ismember $newadm "$grp"; then

3412 # OK, go ahead

3413 getgname="(select gname from grp where rowid=$grid)"

3414 query "replace into grp_adm values($getgname, '$newadm');"

3415 # confirm insertion

3416 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3417 if [ -n "`query \"$sql;\"`" ]; then

3418 echo "追加完了: $newadm" | html p

3419 else

3420 echo "追加失敗($1 $urid)" | html p

3421 fi

3422 fi

3423 showgroup $grid

3424 done

3425 }

3426 dt_rowhack() {

3427 # From: <TR>

3428 # ....

3429 # <TD>rowclass=foo</TD>

3430 # </TR>

3431 # To: <TR class="foo">....<TD>foo</TD></TR>

3432 sed -e '

3433 /^<TR>/ {

3434 :loop

3435 s/\n//

3436 N

3437 /<\/TR>/ {

3438 s/\n//

3439 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3440 n

3441 }

3442 $q

3443 b loop

3444 }'

3445 }

3446 dumptable() {

3447 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3448 # textのフィールドだけ全てダンプにしたほうがいいか

3449 # $DT_VIEW sets link

3450 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3451 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3452 VIEW=${DT_VIEW-replyblog}

3453 if [ -n "$VIEW" ]; then

3454 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3455 fi

3456 sqlfile=$tmpd/dump.sql

3457 : > $sqlfile # ensure to be empty

3458 printf '.mode html\n.header 1\n' > $sqlfile

3459 # $DT_CHLD=ChildTable:BindColumn

3460 if [ -n "$DT_CHLD" ]; then

3461 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3462 cat<<-EOF >> $sqlfile

3463 -- presql

3464 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3465 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3466 EOF

3467 # Speed up counting of new articles

3468 cat<<-EOF >> $sqlfile

3469 -- presql2

3470 DROP TABLE IF EXISTS _counts;

3471 CREATE TEMPORARY TABLE _counts AS

3472 SELECT $_i, count($_i) cnt

3473 FROM $_t GROUP BY $_i;

3474 /* Prepare NEW count table */

3475 CREATE TEMPORARY TABLE _target AS

3476 SELECT b.rowid trowid, b.id

3477 FROM "$2" b JOIN "$2_s" s

3478 ON b.id=s.id AND s.key='owner'

3479 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3480

3481 DROP TABLE IF EXISTS _children;

3482 CREATE TEMPORARY TABLE _children AS

3483 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3484 FROM (SELECT t.trowid, t.id $_i, a.id

3485 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3486 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3487

3488 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3489 DROP TABLE IF EXISTS _news;

3490 DROP VIEW IF EXISTS _news;

3491

3492 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3493 -- INSERT INTO _news

3494 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3495 CREATE TEMPORARY VIEW _news AS

3496 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3497 FROM (SELECT DISTINCT id FROM _target)

3498 a LEFT JOIN

3499 (SELECT $_i, count(ctime) newcnt

3500 FROM _children x

3501 WHERE ctime > coalesce((SELECT time from myacclog

3502 WHERE tblrowid=x.trowid),

3503 '1970-01-01')

3504 GROUP BY $_i) b

3505 ON a.id=b.$_i;

3506 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3507 EOF

3508 # REMOVE next line until 2019/5/1

3509 cntall="(select count($_i) from $_t where $_i=a.id)"

3510 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3511 # REMOVE next assignment until 2019/5/1

3512 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3513 and id in (select id from $_t where $_i=a.id) \

3514 and val > coalesce((select time from myacclog where \

3515 tblrowid=a.rowid),\

3516 '1970-01-01'))"

3517 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3518 cnt="$cntnew as '新着', $cntall as '総数',"

3519 dt_class=" td2r td3r dumpblogs"

3520 fi

3521 # Construct join expression

3522 eav="" scols=""

3523 pk=`gettblpkey $2`

3524 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3525 substr=${substr:-%s}

3526 for col in ${3:-`gettbl_s_cols $2`}; do

3527 valvar=val

3528 case $col in

3529 gecos) scols="$scols${scols:+, }${col#}"

3530 continue ;; # built-in column name

3531 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3532 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3533 case "$as" in

3534 *:*=*) cnd=${as#*:}

3535 h=${cnd%%=*} v=${cnd#*=}

3536 h=`sqlquotestr "$h"`

3537 v=`sqlquotestr "$v"`

3538 valvar="CASE val WHEN $h THEN $v END"

3539 as=${as%%:*} ;;

3540 esac

3541 ;;

3542 *) as=${col} ;;

3543 esac

3544 ss=`printf "$substr" "$valvar"`

3545 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3546 scols="$scols${scols:+, }b.$as"

3547 done

3548 #case author when '$user' then a.rowid else '---' end as ID,

3549 if [ -n "$DT_SQL" ]; then

3550 echo "$DT_SQL"

3551 else

3552 cat<<-EOF

3553 SELECT a.rowid as LINK, $cnt $scols

3554 FROM $2 a LEFT JOIN

3555 (SELECT $pk,$eav,

3556 max(CASE key

3557 WHEN 'owner'

3558 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3559 as gecos

3560 FROM ${2}_s c GROUP BY $pk)

3561 b ON a.$pk=b.$pk $4;

3562 EOF

3563 fi >> $sqlfile

3564 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3565 sqlog<<-EOF

3566 *** SQL-file: $sqlfile ***

3567 `cat $sqlfile`

3568 EOF

3569 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3570 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3571 cat $sqlfile >> tmp/sql

3572 # query ".read $sqlfile" > $tmpd/foo

3573 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3574 cp $tmpd/foo tmp/

3575 err "----- `gdate +%FT%T.%3N` ------------555555"

3576 ## $ddd LINE exists at the end of this function

3577 fi

3578 printf '.mode list\n.header 0\n' >> $sqlfile

3579 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3580 <div>

3581 <div class="dumptable">

3582 <table class="b$dt_class">

3583 `query ".read $sqlfile"`

3584 </table>

3585 </div>

3586 </div>

3587 EOF

3588 ### `query ".read $sqlfile"`

3589 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3590

3591 ## REMOVE THIS!

3592 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3593 }

3594

3595 par2table() (

3596 # copy current parameters of par into destination table

3597 # $1=definition-file

3598 # Using $user and $session

3599 # Return value:

3600 # 0: Stored successfully

3601 # 1: Insufficient fillings

3602 # 2: No permission to modify the record

3603 # 3: Invalid rowid

3604 # 4: SUCCESS to delete

3605 # 5: Stop deletion for lack of confirm check

3606 # 6: Password length too short

3607 # 7: Password mismatch

3608 # 8: Old password incorrect

3609 rowid=`getpar rowid`

3610 if [ ! -e $1 ]; then

3611 echo "テーブル定義ファイルが見付かりません" | html p

3612 exit 1

3613 fi

3614 tbl=${1%.def}

3615 tbl=${tbl##*/}

3616 if [ -n "$rowid" ]; then # Modify existing entry

3617 if [ x"$tbl" = x"user" ]; then

3618 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3619 elif [ x"$tbl" = x"grp" ]; then

3620 sql="select gname from $tbl where rowid=$rowid;"

3621 ##err p2t:grp:q $sql

3622 isgrpowner "$user" "`query $sql`" && rowowner=$user

3623 elif [ x"$tbl" = x"blog" ]; then

3624 # Check if owner in blog_s

3625 blogowner=`getvalbyid blog owner "$rowid"`

3626 if isgrpowner "$user" "$blogowner"; then

3627 rowowner=$user

3628 else

3629 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3630 fi

3631 else

3632 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3633 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3634 fi

3635 ### err rowowner=$rowowner

3636 if [ x"$user" != x"$rowowner" ]; then

3637 echo "他人のレコードはいじれないの" | html p

3638 return 2

3639 elif [ -z "$rowowner" ]; then

3640 echo "指定したレコードはないみたい" | html p

3641 return 3

3642 fi

3643 rm=`getpar rm` cfm=`getpar confirm`

3644 # Editing existent entry

3645 if [ x"$rm" = x"yes" ]; then

3646 if [ x"$rm$cfm" = x"yesyes" ]; then

3647 query "delete from $tbl where rowid=$rowid;"

3648 return 4

3649 else

3650 echo "消去確認のチェックがないので消さなかったの..." | html p

3651 return 5

3652 fi

3653 fi

3654 fi

3655

3656 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3657 if [ -n "$rowid" ]; then

3658 # Update of existing record

3659 for col in `gettblcols $tbl`; do

3660 val=`getparquote $col`

3661 [ -z "$val" ] && continue

3662 ## err query "update $tbl set $col=$val where rowid=$rowid"

3663 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3664 sql="update $tbl set $col=$val where rowid=$rowid;"

3665 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3666 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3667 ## User name cannot be changed with interface provided with this

3668 ## script. But we offer the trigger to change owner user

3669 ## of blog_s table.

3670 #err "select quote($col) from $tbl where rowid=$rowid;"

3671 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3672 cat<<-EOF | query

3673 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3674 -- should use EXCLUSIVE transaction outside of this.

3675 SAVEPOINT par2table;

3676 $sql

3677 update blog_s set val=$val

3678 where key='owner' and val=$old;

3679 RELEASE SAVEPOINT par2table;

3680 EOF

3681 ## XX: DIRTY Hack Ends here

3682 ## We should keep blog's owner as a single column which has

3683 ## foreign key constraint with primary key of grp/user.

3684 else

3685 query "$sql"

3686 fi

3687 done

3688 # Then, set up $pval for further insertion of tbl_s and tbl_m

3689 for col in `gettblpkey $tbl`; do

3690 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3691 pval="$pval${pval:+, }\"$val\""

3692 done

3693 else

3694 # New entry

3695 # Generate values() for primary keys

3696 for col in `gettblpkey $tbl`; do

3697 # Genuine primary keys for _m and _s

3698 val=`getvalquote $tbl $col`

3699 [ -z "$val" ] && continue

3700 pval="$pval${pval:+, }$val"

3701 done

3702 ##err pval=$pval

3703 for col in `gettblfkey $tbl`; do

3704 # args for values() to insertion into master table

3705 val=`getvalquote $tbl $col`

3706 [ -z "$val" ] && continue

3707 formaster=$formaster"${formaster:+, }$val"

3708 done

3709 formaster="$pval${formaster:+, }$formaster"

3710 ## err formaster=$formaster

3711 if [ -z "$formaster" ]; then

3712 echo "項目を全て埋めてください" | html pre

3713 return 1

3714 fi

3715 ## err "replace into $tbl values($formaster);"

3716 query "replace into $tbl values($formaster);"

3717 ## Insertion to master table, done

3718 fi

3719

3720 transaction=$tmpd/sqlfile.sql; touch $transaction

3721 for kt in s m; do

3722 tb2=${tbl}_$kt

3723 for col in `gettbl_${kt}_cols $tbl`; do

3724 ptype=`getpartype $col "limit 1"`

3725

3726 # First, check update of existing entries in _m

3727 if [ $kt = m ]; then

3728 # sessID|address.1.22|string|Somewhere-x.y.z

3729 sql=""

3730 ##err dots from query "select var from par where var like '$col.%';"

3731 for v in `query "select var from par where var like '$col.%';"`; do

3732 # v=address.1.22

3733 st_rowid=${v##*.}

3734 origcol=${v%%.*} # original column derived from

3735 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3736 ##case `getpartype $v` in

3737 ## err CASE `gettbl_coltype $tbl/$origcol` in

3738 ## err edit flag = `getpar action.$v`

3739 case `getpar action.$v` in

3740 rm)

3741 if [ x`getpar confirm.$v` = x"yes" ]; then

3742 newsql="delete from $tb2"

3743 else

3744 echo "削除確認未チェック" | html p

3745 fi ;;

3746 edit)

3747 case `gettbl_coltype $tbl/$origcol` in

3748 image|document|binary)

3749 file=$tmpd/`getparfilename $v`

3750 ## err type=file=$file

3751 [ -z "$file" ] && continue

3752 bn=`sqlquotestr "${file##*/}"`

3753 bin="X'"$(hexize "$file")"'"

3754 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3755 type=\"file:$ct\"

3756 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3757 cachedir=`getcachedir "$tbl/$rowid"`

3758 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3759 rm -rf $cachedir

3760 ;;

3761 *)

3762 newsql="update $tb2 set val=(select val from par where var \

3763 like '$col.%.$st_rowid')"

3764 ;;

3765 esac

3766 ;;

3767 *) # maybe "keep", do not modify value

3768 continue

3769 ;;

3770 esac

3771 # err newsql=$newsql

3772 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3773 done

3774

3775 if [ x"$bin" = x"NULL" ]; then

3776 ## err repl:normal sql=`echo $sql`

3777 if [ -n "$transaction" ]; then

3778 cat<<-EOF >> $transaction

3779 $sql

3780 DELETE FROM $tb2 WHERE type='string' AND val='';

3781 EOF

3782 else

3783 query "$sql

3784 delete from $tb2 where type='string' and val='';"

3785 ## err repl:normal done

3786 fi

3787 else

3788 # Binary update line is TOO LONG to pipelining

3789 sqlfile="$tmpd/sqlf.$$"

3790 if [ -n "$transaction" ]; then

3791 echo "$sql" >> $transaction

3792 else

3793 echo "$sql" > $sqlfile

3794 query ".read $sqlfile"

3795 fi

3796 fi

3797 # Rest of kt==m: set multiple mode

3798 nr=`getparcount $col`

3799 else

3800 nr=1 # for kt==s, number of records is 1

3801 fi

3802

3803 i=0

3804 while [ $i -lt $nr ]; do

3805 limit="limit 1 offset $i"

3806 i=$((i+1)) # increase beforehand against continue

3807 val=`getvalquote $tbl $col "$limit"`

3808 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3809 ## err $col=$val

3810 bin=NULL

3811 ## err partype$col=`getpartype $col "$limit"`

3812 case $ptype in

3813 file) file=$tmpd/`getparfilename $col "$limit"`

3814 ## err parfile-$col=$file

3815 [ -z "$file" ] && continue

3816 bin="X'"$(hexize "$file")"'"

3817 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3818 type=\"file:$ct\" ;;

3819 "*"*) continue ;; # foreign table

3820 *) type=\"string\" ;;

3821 esac

3822 case `gettbl_coltype $tbl/$col` in

3823 password) # special care for password

3824 # name={password,pswd1,pswd2}

3825 p1=`getpar pswd1 "$limit"`

3826 if [ -z "$p1" ]; then

3827 continue # SKIP password setting, if p1 is empty

3828 else

3829 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3830 ## err pswd=$pswd

3831 if pwcheck "$pswd"; then

3832 if [ x"$p1" = x"$p2" ]; then

3833 case "$p1" in

3834 ??????????*) ;;

3835 *) echo "パスワードは10字以上にしてください。" | html p

3836 return 6;;

3837 esac

3838 val="\"`echo $p1|mypwhash`\""

3839 else

3840 echo "2つの新パスワード不一致" | html p

3841 return 7

3842 fi

3843 else

3844 echo "旧パスワード違います" | html p

3845 return 8

3846 fi

3847 fi

3848 ;;

3849 esac

3850 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3851 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3852 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3853 if [ x"$bin" = x"NULL" ]; then

3854 ## err Normal-query: `echo $sql`

3855 if [ -n "$transaction" ]; then

3856 echo "$sql" >> $transaction

3857 else

3858 query "$sql"

3859 fi

3860 else

3861 sqlfile="$tmpd/query.$$"

3862 ## err sqlfile=`ls -lF $sqlfile`

3863 if [ -n "$transaction" ]; then

3864 echo "$sql" >> $transaction

3865 else

3866 echo "$sql" > $sqlfile

3867 query ".read $sqlfile"

3868 fi

3869 fi

3870 ## err p2t done

3871 done

3872 done

3873 done

3874 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3875 -- We cannot use transaction here, because groupupdate may use it.

3876 SAVEPOINT pa2table_insert;

3877 .read $transaction

3878 RELEASE SAVEPOINT pa2table_insert;

3879 EOF

3880 return $?

3881 ##err donee

3882 )

3883 genform() {

3884 # $1 = form definition file

3885 # $2, $3 (optional)= table name and ROWID

3886 # If $GF_VIEWONLY set and nonNull, output values without form

3887 # If $GF_ARGS set, use it as content-strings in the form

3888 # If $GF_OWNER set, use it as value of name="owner"

3889 # If $GF_STAGE set, use it as value of name="stage"

3890 forms="" hiddens="" rowid=$3

3891 if [ ! -e "$1" ]; then

3892 echo "そのようなデータベースはないようです($2)。" | html p

3893 return

3894 elif [ -n "$2" ]; then

3895 rec=`query "select * from $2 where rowid='$rowid';"`

3896 if [ -z "$rec" ]; then

3897 pk=`gettblpkey $2`

3898 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3899 rec=`query "select rowid from $2 where $pk='$rowid';"`

3900 rowid=$rec

3901 rec=$3

3902 fi

3903 if [ -z "$rec" ]; then

3904 echo "そんなレコードはないみたいね..." | html p

3905 return

3906 fi

3907 fi

3908 if [ -z "$GF_VIEWONLY" ]; then

3909 rm='<input id="rm" name="rm" type="checkbox"

3910 value="yes"><label for="rm">このエントリの削除</label>

3911 <span>ほんとうに消しますよ(確認)!

3912 <input name="confirm" type=checkbox value="yes">はい</span>'

3913 fi

3914 # Image Cache dir

3915 ## err genform: getcache=$2/$rowid

3916 td=`getcachedir "$2/$rowid"`

3917 while IFS=: read prompt name keytype type args; do

3918 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3919 sp="${args:+ }"

3920 form="" val=""

3921 if [ -n "$rowid" ]; then

3922 # err genform2a: Seeking for "$2.$name, type=$type"

3923 rawval=`getvalbyid $2 $name $rowid $td`

3924 val=`echo "$rawval"|htmlescape`

3925 ## err genform3a: getvalbyid $2 $name $rowid $td

3926 ## err genform3b: val="[$val]" type="$type"

3927 fi

3928 if [ -n "$GF_VIEWONLY" ]; then

3929 is_hidden "$2" "$name" && continue

3930 fi

3931 case "$type" in

3932 text*)

3933 cgiform=cgi_multi_$type

3934 if [ -s $td/$name.count -a -n "$val" ]; then

3935 form=`$cgiform $name $td`

3936 val=$(echo "$val"|

3937 while read fn; do

3938 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3939 </td></tr>$nl"

3940 done)

3941 val="<table>$nl$val$nl</table>"

3942 else

3943 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3944 form=`cgi_$type $name "$rawval" "$args"`

3945 fi

3946 ;;

3947 [Rr][Aa][Dd][Ii][Oo])

3948 fh="<label><input type=\"radio\" name=\"$name\""

3949 form="`echo $args|sed -e \

3950 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3951 ;;

3952 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3953 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3954 ;;

3955 [Ss][Ee][Ll][Ee][Cc][Tt])

3956 fh="<select name=\"$name\">$nl"

3957 form=$(for l in $args; do

3958 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3959 done)

3960 if [ -n "$val" ]; then

3961 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3962 fi

3963 form="$fh$form</select>"

3964 ;;

3965 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3966 if [ -s $td/$name.count ]; then

3967 form=`cgi_multi_file $name $td "$args"`

3968 if [ -n "$val" ]; then

3969 hrfb="$myname?showattc+$2_m"

3970 val=$(echo "$rawval" \

3971 | while read fn; do

3972 data=`percenthex "$td/$fn"`

3973 #ct=`cat $td/$fn.content-type`

3974 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3975 ri=`cat "$td/$fn.rowid"`

3976 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3977 #imgsrc="<img src=\"data:$ct,$data\">"

3978 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3979 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3980 done)

3981 fi

3982 else

3983 form="<input type=\"file\" name=\"$name\" $args>"

3984 if [ -n "$val" ]; then

3985 imgs=$(echo "$rawval"\

3986 |while read fn;do

3987 data=`percenthex "$td/$fn"`

3988 echo "<img src=\"data:image/png,$data\">$fn<br>"

3989 done)

3990 form=$form"<br>$imgs"

3991 val=$imgs # 2015-06-15

3992 else

3993 form="<input type=\"file\" name=\"$name\" $args>"

3994 fi

3995 fi

3996 ;;

3997 [Hh][Ii][Dd][Dd][Ee][Nn])

3998 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3999 args="value=\"$GF_STAGE\""

4000 fi

4001 form="<input type=\"hidden\" name=\"$name\" $args>"

4002 prompt='' # Remove prompt

4003 ;;

4004 [Aa][Uu][Tt][Hh][Oo][Rr])

4005 [ -n "$GF_VIEWONLY" ] && continue

4006 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

4007 prompt="" ;;

4008 [Oo][Ww][Nn][Ee][Rr])

4009 [ -n "$GF_VIEWONLY" ] && continue

4010 val=${GF_OWNER:-$val}

4011 val=${val:-$user}

4012 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

4013 prompt="" ;;

4014 [Uu][Ss][Ee][Rr])

4015 # XXX: is null $user ok?

4016 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

4017 [ -n "$GF_VIEWONLY" ] && continue

4018 form="$user"

4019 ;;

4020 [Pp]assword)

4021 [ -n "$GF_VIEWONLY" ] && continue

4022 form="`cgi_passwd`"

4023 val=""

4024 ;;

4025 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

4026 [ -n "$GF_VIEWONLY" ] && continue

4027 if [ -z "$rowid" ]; then

4028 val=`genserial`

4029 fi

4030 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

4031 prompt="" ;;

4032 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

4033 prompt=""

4034 ;;

4035 parent|path|blog*)

4036 prompt=""

4037 ;;

4038 "*"*)

4039 tail=$tail"``"

4040 continue ;;

4041 esac

4042 if [ -n "$prompt" ]; then

4043 if [ -n "${GF_VIEWONLY}" ]; then

4044 form=$val

4045 else

4046 :

4047 fi

4048 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

4049 else

4050 hiddens=$hiddens$nl"$form"

4051 fi

4052 done < $1

4053 # enctype="multipart/form-data"

4054 cat<<EOF

4055 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4056 ${rowid:+$rm}

4057 <table class="b $2">

4058 $forms

4059 </table>$hiddens

4060 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4061 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4062 EOF

4063 if [ -z $GF_VIEWONLY ]; then

4064 cat<<EOF

4065 <input type="submit" name="sub" value="OK">

4066 <input type="reset" name="res" value="Reset">

4067 EOF

4068 fi

4069 cat<<EOF

4070 $GF_ARGS</form>

4071 $tail

4072 EOF

4073 }

4074 edittable() {

4075 # $1=form-def $2=table $3 rowid

4076 genform "$@"

4077 }

4078 viewtable() {

4079 GF_VIEWONLY=1 genform "$@"

4080 }

4081 showattc() {

4082 # $1=table_m $2=rowid &optional $3=RawFlag

4083 ## err \$1=$1 \$2=$2 \$3=$3

4084 if ! isfilereadable $user $1 $2; then

4085 contenttype; echo

4086 echo "このファイルは管理者のみしか見られません" | html p

4087 putfooter; exit

4088 fi

4089 idir=`umask 002; mktempd` || exit 1

4090 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4091 bin=$idir/$myname-$$.bin

4092 sql="select quote(bin) from $1 where rowid='$2';"

4093 ## err showattc: sql: $sql

4094 sq $db "$sql" | unhexize > $bin

4095 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4096 type=${tv%//*} fn=${tv#*//}

4097 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4098 ct=${type#file:}

4099 case $ct in # all text/* changed to text/plain

4100 text/*)

4101 charset=`nkf -g $bin|cut -d' ' -f1`

4102 case $charset in

4103 ASCII*) charset="" ;;

4104 esac

4105 if [ -z "$3" ]; then

4106 ct="text/html${charset:+; charset=$charset}"

4107 link="?showattc+$1+$2+raw"

4108 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4109 | sed 's,^,<span></span>,' \

4110 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4111 -D_LINK_="$link" \

4112 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4113 exit $?

4114 fi

4115 ct="text/plain${charset:+; charset=$charset}"

4116 ;;

4117 esac

4118 contenttype "$ct"

4119 echo "Content-Disposition: filename=\"$fn\""

4120 echo "Content-Length: " `cat $bin | wc -c`; echo

4121 #echo "Content-Type: " ${type#file:}; echo

4122 cat $bin

4123 }

4124 #

4125 # Some default stupid handler on CGI values

4126 #

4127 default_storedb() {

4128 # ARG: $1=table-def-file

4129 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4130 tbl=`basename $1`

4131 tbl=${tbl%.def}

4132 cols="`grep :text $1|cut -d: -f2`"

4133 col=`echo "$cols"|head -1`

4134 vcol=`getpar $col`

4135 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4136 if [ -n "$vcol" ]; then

4137 par2table $1

4138 else

4139 return 2 # No insertion occurred

4140 fi

4141 }

4142

4143 default_view() { # $1=def-file

4144 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4145 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4146 default_storedb "$@"

4147 query "select rowid from $tbl order by rowid desc;" \

4148 | while read rowid; do

4149 viewtable $1 $tbl $rowid

4150 done | _m4 -D_TITLE_="$tbl" \

4151 -D_FORM_="`genform $1`" \

4152 -D_DUMPTABLE_="syscmd(cat)" \

4153 $layout/html.m4.html $layout/form+dump.m4.html

4154 }

4155 default_viewtext() { # $1=def-file

4156 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4157 default_storedb "$@"

4158 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4159 | _m4 -D_TITLE_="$tbl" \

4160 -D_FORM_="`genform $1`" \

4161 -D_DUMPTABLE_="syscmd(cat)" \

4162 $layout/html.m4.html $layout/form+dump.m4.html

4163 }

4164 default_smail() {

4165 default_storedb "$@"

4166 if [ $? -eq 2 ]; then

4167 _m4 -D_TITLE_="入力" \

4168 -D_FORM_="`genform $1`" \

4169 -D_DUMPTABLE_="" \

4170 $layout/html.m4.html $layout/form+dump.m4.html

4171 return

4172 fi

4173 cond=""

4174 for pk in `gettblpkey $tbl`; do

4175 pv=$(sqlquote "$(getpar $pk)")

4176 cond="$cond${cond:+ and }$pk=$pv"

4177 done

4178 sql="select rowid from $tbl where $cond;"

4179 rowid=`query "$sql"`

4180 ## err smail1 - "$sql" "-> rowid=$rowid"

4181

4182 while IFS=: read prompt name keytype type args; do # Read from $1

4183 val=`getpar $name`

4184 if [ -n "$val" ]; then

4185 text="$text

4186 $prompt

4187 $name=$val

4188 ---------------------------------------------------------"

4189 fi

4190 case "$type" in

4191 image|document|file)

4192 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4193 fns=$(echo "$fn"|while read fn; do

4194 err mv $tmpd/$fn.orig $tmpd/$fn

4195 mv $tmpd/$fn.orig $tmpd/$fn

4196 rm $tmpd/$fn.rowid # Remove cache flag

4197 ## err "`ls $tmpd/$fn`"

4198 echo $fn

4199 done)

4200 files="$files $fns"

4201 ;;

4202 esac

4203 done < $1

4204 ## err FILES=$files "`ls -lF $tmpd`"

4205 subj="from ${REMOTE_ADDR}"

4206 (echo "$url"

4207 echo "への書き込みがありました。"

4208 echo "------"

4209 echo "$text"

4210 ) | (cd $tmpd &&

4211 err LS="`ls -lF`" &&

4212 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4213 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4214 echo "以下の内容で送信しました。" | html p

4215 viewtable $1 $tbl \

4216 `query "select rowid from $tbl order by rowid desc limit 1;"`

4217 echo "戻る" | html a "href=\"?\""

4218 }