s4: 9ff4603fc920 s4-funcs.sh

s4

view s4-funcs.sh @ 692:9ff4603fc920

Run getcookie() in the same process

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 19 May 2020 09:16:05 +0900
parents	39879f81afc8
children	5f3e94a9b7b3

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 }

392 cleanup() {

393 trap '' INT HUP EXIT TERM PIPE

394 echo .quit >&5

395 kill $sq3pid

396 kill $sq3pid

397 rm -f $sqo $sqi

398 rm -rf $tmpfiles

399 cleanup2

400 }

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

402 # simple redirection for the sake of speed.

403 query() {

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

405 echo ".output $sqo" >&5

406 logstart

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

408 tee -a $querylog

409 else

410 echo "$@" >> $querylog

411 echo "$@"

412 fi >&5

413 echo ".output stdout" >&5

414 cat $sqo

415 logend

416 }

417 _m4() {

418 #S4NAME=f,f,f

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

420 }

421 ismember() {

422 # $1=user, $2=group

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

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

425 }

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

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

428 }

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

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

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

432 }

433 isgrpowner() (

434 # $1=user, $2=group

435 gn=`sqlquote "$2"`

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

437 err isgrpowner: $sql

438 test -n "`query $sql`"

439 )

440 isgrpownerbygid() (

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

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

443 err isgrpownerbygid: $sql

444 test -n "`query $sql`"

445 )

446 getgroupadminmails() {

447 # $1=group

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

449 email4group "$1" "$i" ;

450 done

451 }

452 getgroupadmins() { # $1=group

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

454 qgrp=`sqlquote "$1"`

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

456 }

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

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

459 getvalbyid grp $2 \

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

461 }

462 getgroupbyid() {

463 # $1=id|gname

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

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

466 # err ggbyid: `echo $sql`

467 query $sql

468 }

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

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

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

472

473 # Return true when anonymous mode

474 [ "$anonymousmode" ] && return 0

475 # case `getvalbyid blog mode $2` in

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

477 # *closed*)

478 # owner=`getvalbyid blog owner $2`

479 # if isgrp $owner; then

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

481 # elif isuser $owner; then

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

483 # fi

484 # esac

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

486 sql="with getblog as (\

487 select key,val from blog_s where id=(\

488 select blogid from article where id in\

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

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

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

492 select case\

493 when (select author from article where\

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

495 then 'author'\

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

497 then 'open'\

498 when (select val from getmode) is null \

499 then 'open'

500 when (select val from getowner) in (select gname from grp)\

501 then (select user from grp_adm where \

502 gname=(select val from getowner) and \

503 user='$1')\

504 when (select author from article where\

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

506 then 'user+author'

507 else '' end;"

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

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

510 result=`query "$sql"`

511 [ -n "$result" ] && return 0

512 return 2

513 }

514 linkhome() {

515 # $1=UserOrGroup

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

517 if isuser $1; then

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

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

520 else

521 echo -n "grp+$1"

522 fi

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

524 }

525 hreflink() {

526 # s4 specific notation:

527 # ^href=URL

528 # ^iframe=URL

529 # ^video=URL

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

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

532 # OSM umap Wikistyle Notation:

533 # [[URL]] - Simle Link

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

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

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

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

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

539 # Other Style

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

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

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

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

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

545 # SPC+SPC+$ - <br>

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

547 checkboxON="${cb} checked>"

548 checkboxOFF="${cb}>"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

573

574 }

575 minitbl() {

576 sed -n '

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

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

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

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

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

582 H; # Concat this line to HoldSpace

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

584 $ b cont

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

586 }

587 :cont

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

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

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

591 # . of ^. is workaround for FreeBSD sed

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

593 p; # Print whole "table" element

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

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

596 }

597 x; # Back to the newest line

598 p; # Print rest' | miniul

599 }

600 miniul() {

601 sed -e '

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

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

604 :top

605 s/\n//;

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

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

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

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

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

611 $ b cont

612 N; # 次の行を読む

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

614 /^ /b top

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

616 p; s/.*//;

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

618 /^\* /b top

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

620 }

621

622 :cont

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

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

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

626 H; x

627 }

628 x'

629 }

630 acclog() (

631 # $1=table, $2=rowid

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

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

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

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

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

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

638 fi

639 )

640 gecos() (

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

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

643 )

644 setpar() {

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

646 }

647 replpar() {

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

649 }

650 getpar() {

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

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

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

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

655 fi

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

657 case "$var" in

658 owner)

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

660 echo $user; return

661 elif ismember $user $val; then

662 echo $val; return

663 fi ;;

664 esac

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

666 echo "$val"

667 }

668

669 getpartype() {

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

671 }

672 getparcount() {

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

674 }

675 getparfilename() {

676 # null if type of $1 is not file

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

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

679 }

680 sqlquote() {

681 (v="$1"

682 case "$v" in

683 "") return ;; # null

684 "X'"*) # quoted hex string

685 echo $1 ;;

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

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

688 echo "\"$v\""

689 return ;;

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

691 echo "\"$v\""

692 return ;;

693 *)

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

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

696 else

697 echo "\"$v\""

698 fi ;;

699 esac)

700 }

701 sqlquotestr() (

702 case "$1" in

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

704 echo "'$v'" ;;

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

706 esac

707 )

708 mktempd() {

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

710 }

711 getcachedir() { # $1=maintable

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

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

714 else

715 echo $tmpd/$thumbxy

716 fi

717 }

718 getval() {

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

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

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

722 echo "$user" ;;

723 stamp|datetime)

724 date "+%F %T" ;;

725 serial)

726 (s=`getpar $2`

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

728 *)

729 getpar "$2" "$3";;

730 esac

731 }

732

733 getvalquote() {

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

735 (v=`getval "$@"`

736 case "$v" in

737 "") echo NULL ;;

738 *) sqlquote "$v" ;;

739 esac)

740 }

741 getparquote() {

742 sqlquote "`getpar $1`"

743 }

744 getbinbyid() {

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

746

747 }

748 getvalbyid() {

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

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

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

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

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

754

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

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

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

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

759 return

760 fi

761 done

762 rowid=$3

763 pk=`gettblpkey $1`

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

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

766 td=${4:-$tmpd}

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

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

769 for kt in s m; do

770 t=${1}_$kt

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

772 vcount=1 # count(val)

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

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

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

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

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

778 if [ $kt = m ]; then

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

780 # Reset val to store filenames if type is string

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

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

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

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

785 i=0

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

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

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

789 i=$((i+1))

790 fn=$c.$i

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

792 case $type in

793 file:*)

794 #file=$td/$val

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

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

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

798 # FOR SPEED: Skip file generation if imgcache exists

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

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

801 && continue

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

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

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

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

806 select rowid from $cond $slice;

807 .output '$td/$fn'

808 select val from $cond $slice;

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

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

811 .output stdout

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

813 EOF

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

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

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

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

818 ls -lh "$file" |

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

820 case "$type" in

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

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

823 *|*:[Aa]pplication*)

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

825 png:- > "$file"

826 ;;

827 esac

828 ;;

829 *)

830 sq $db<<EOF

831 .output $td/$fn.rowid

832 select rowid from $cond $slice;

833 .output $td/$fn

834 select val from $cond $slice;

835 EOF

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

837 ;;

838 esac

839 done

840 else

841 rm -f $td/$c.count

842 case $type in

843 file:*)

844 echo "$val" \

845 | while read fn; do

846 file=$td/$fn

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

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

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

850 | unhexize > "$file"

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

852 case $type in

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

854 *:[Aa]pplication*)

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

856 png:- > $file ;;

857 esac

858 fi

859 done

860 ;;

861 esac

862 fi

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

864 return

865 fi

866 done

867 done)

868 }

869 getvalbypkey() (

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

871 pk=`gettblpkey $1`

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

873 getvalbyid "$1" "$2" $rowid $4

874 )

875 getvalbycond() {

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

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

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

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

880 getvalbyid "$1" "$2" $rowid "$4"

881 fi

882 }

883 getpwfield() {

884 # getpwfield user column

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

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

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

888 echo "$val"

889 return 0

890 else

891 return 1

892 fi

893 }

894 numericalize() {

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

896 }

897 encode() {

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

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

900 sha1=sha1

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

902 sha1=sha1sum

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

904 sha1=gsha1sum

905 fi

906 fi

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

908 }

909 enjpeg() {

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

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

912 cjpeg="cjpeg"

913 else

914 cjpeg="convert - jpeg:-"

915 fi

916 fi

917 $cjpeg "$@"

918 }

919 mycrypt() (

920 key=$1 salt=$2

921 # err \$2=$2

922 case $2 in

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

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

925 esac

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

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

928 )

929 hexize() {

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

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

932 hexize="xxd -p"

933 else

934 hexize_hd() {

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

936 }

937 hexize="hexize_hd"

938 fi

939 fi

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

941 }

942 unhexize() {

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

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

945 unhex="xxd -p -r"

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

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

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

949 EOF

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

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

952 # world writable...:)

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

954 fi

955 fi

956 cat "$@" | $unhex

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

958 }

959 percenthex() {

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

961 }

962 htmlescape() {

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

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

965 }

966 enascii() {

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

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

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

970 else

971 enascii_now=`date +%FT%T`

972 enascii_sed() {

973 nkf -Z0Z1Z2 \

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

975 }

976 enascii="enascii_sed"

977 fi

978 fi

979 cat "$@" | $enascii

980 }

981 size_h() {

982 i="$1" oi=$1

983 set -- B B KB MB GB TB

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

985 oi=$i

986 i=$((i/1024))

987 shift

988 done

989 echo ${oi}$1

990 }

991 gettblconf() {

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

993 ## tconfs=`sq $db \

994 tconfs=`query \

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

996 fi

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

998 }

999 gettblkeys() {

1000 # $1=tbl

1001 gettblconf

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

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

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

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

1006 case $type in

1007 *p*)

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

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

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

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

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

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

1014 esac

1015 case $type in

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

1017 esac

1018 done

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

1020 }

1021 gettblpkey() {

1022 # $1=tbl

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

1024 }

1025 gettblfkey() {

1026 (x=`gettblkeys $1`

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

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

1029 }

1030 gettblcols() {

1031 (x=`gettblkeys $1`

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

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

1034 }

1035 gettbl_s_cols() {

1036 (x=`gettblkeys $1`

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

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

1039 }

1040 gettbl_m_cols() {

1041 (x=`gettblkeys $1`

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

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

1044 }

1045 gettbl_h_cols() {

1046 (x=`gettblkeys $1`

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

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

1049 }

1050 gettbl_coltype() (

1051 gettblconf

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

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

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

1055 )

1056 is_hidden() {

1057 # $1=Tbl $2=col

1058 gettblconf

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

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

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

1062 case $x in

1063 *h*) return 0 ;;

1064 *) return 1 ;;

1065 esac

1066 }

1067

1068 dbsetbyid() {

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

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

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

1072 quotedp=$(sqlquotestr "$p")

1073 unset primary update

1074 gettblconf

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

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

1077 #err conf=$conf

1078 case ${conf#*=} in

1079 p*) primary=1 ;;

1080 f*) update=1 ;;

1081 u*) ;;

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

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

1084 esac

1085 #err t=$t

1086 type=string fn=""

1087 case $conf in

1088 */password)

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

1090 ;;

1091 */image*|*/document*)

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

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

1094 ;;

1095 esac

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

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

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

1099 if [ "$primary" ]; then

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

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

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

1103 elif [ "$update" ]; then

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

1105 else

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

1107 fi

1108 )

1109 }

1110 expire() (

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

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

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

1114 )

1115 addsession() {

1116 # expireをセット

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

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

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

1120 # Remove old session parameters

1121 now=`expire now`

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

1123 }

1124 gencookie() (

1125 for kv; do

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

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

1128 done

1129 )

1130 contenttype() {

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

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

1133 }

1134 putheader() {

1135

1136 }

1137 putfooter() {

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

1139 }

1140 getcookie() {

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

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

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

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

1145 done

1146 }

1147 genrandom() {

1148 # $1=columns (default: 10)

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

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

1151 }

1152 genserial() {

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

1154 }

1155 smail() {

1156 # smail rcpts subj (file)

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

1158 # $MAIL_FROM <- From: header value

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

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

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

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

1163 sender=${SENDER:-$admin}

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

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

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

1167 }

1168 smail_queue_flush() {

1169 # $1=timelimit

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

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

1172 SELECT rowid FROM smailq

1173 GROUP BY rcpts, subj

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

1175 EOF

1176 )

1177 for rid in $rowids; do

1178 sq $workdb \

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

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

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

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

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

1184

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

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

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

1188 .separator "\n" "------------------\n\n"

1189 SELECT time, text FROM smailq

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

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

1192 ORDER by time;

1193 EOF

1194 then

1195 echo rowid=$rid

1196 cat <<-EOF | sq $workdb

1197 DELETE FROM smailq

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

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

1200 EOF

1201 fi

1202 fi

1203 done

1204 }

1205 smail_queue() {

1206 # $1=Rcpts, $@=subj

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

1208 if [ $? -eq 0 ]; then

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

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

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

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

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

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

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

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

1217 EOF

1218 )

1219 ## XXX: Adhoc load mitigation

1220 case "$now" in

1221 *[01])

1222 err flush_queue=$mintime

1223 smail_queue_flush ;;

1224 esac

1225 fi

1226 }

1227 setviastring() {

1228 table=par

1229 oifs="$IFS"

1230 IFS="&"

1231 for us in $1; do

1232 k=${us%%=*}

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

1234 setpar "$k" "string" "$v"

1235 done

1236 IFS="$oifs"

1237 }

1238 checkdomain() (

1239 # Check the validity of domain by referring DNS

1240 item=$1

1241 err checkdomain $1

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

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

1244 )

1245 pwcheck() {

1246 # $1=passwd

1247 dbpswd=`getpwfield $user pswd`

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

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

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

1251 }

1252 mypwhash() {

1253 mycrypt "`cat`" `genrandom 5`

1254 }

1255 wasureta() {

1256 user=$1

1257 if ! checkdomain $user; then

1258 contenttype; echo

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

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

1261 putfooter

1262 exit 0

1263 fi

1264 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1266 encpswd=`echo $newpswd|mypwhash`

1267 dbsetbyid user $user pswd "$encpswd"

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

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

1270 $msgdir/mail-newaccount.m4 \

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

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

1273 }

1274 checkauth() {

1275 user=`getpar user`

1276 skc=`getpar skey` # from cookie

1277 [ -z "$user" ] && return 3

1278 skey="`getpwfield $user skey`"

1279 if [ -n "$skey" ]; then

1280 if [ x"$skey" = x"$skc" ]; then

1281 return 0

1282 fi

1283 fi

1284 pswd=`getpar pswd`

1285 quser=`sqlquotestr "$user"`

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

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

1288 return 4 # 4=server too heavy

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

1290 return 2 # 2=login fail

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

1292 wasureta "$user"

1293 return 1 # wasureta error

1294 fi

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

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

1297 if pwcheck "$pswd"; then

1298 newsession=`genrandom 50`

1299 if dbsetbyid user "$user" skey "$newsession" &&

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

1301 gencookie "user=$user" "skey=$newsession"

1302 return 0

1303 else

1304 return 4 # Heavy load??

1305 fi

1306 fi

1307 return 2 # Password mismatch

1308 }

1309 showlogin() {

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

1311 s4name=${S4NAME:-s4}

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

1313 cat $layout/login.m4.html

1314 echo '</body></html>'

1315 ) | _m4 \

1316 -D_BODYCLASS_="login" \

1317 -D_TITLE_="$s4name" \

1318 -D_SYSNAME_="Welcome to $s4name" \

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

1320 exit 0

1321 }

1322 dologin() {

1323 checkauth

1324 st=$?

1325 if [ $st != 0 ]; then

1326 contenttype; echo

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

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

1329 showlogin # and EXIT

1330 fi

1331 }

1332

1333 # Do instant jobs here

1334 dbsetup

1335 trap cleanup INT HUP EXIT TERM PIPE

1336 # trap cleanup INT HUP

1337

1338 err() {

1339 echo "$@" 1>&3

1340 }

1341

1342 cgiinit() {

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

1344 tmpf=tmp/stream

1345 tmpd=`tmpd=$tmpdir mktempd`

1346 tmpfiles=$tmpfiles" $tmpd"

1347 addsession $session

1348 getcookie

1349 case "$REQUEST_METHOD" in

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

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

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

1353 # cat > $tmpf # too much?

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

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

1356 s="`cat tmp/stream`"

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

1358 ;;

1359 esac

1360 case "$CONTENT_TYPE" in

1361 *boundary*)

1362 bndry=${CONTENT_TYPE#*boundary=}

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

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

1365 do

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

1367 #echo u=$us

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

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

1370 # err k=$k v=$v

1371 case "$k" in

1372 *:filename)

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

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

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

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

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

1378 :

1379 fi

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

1381 case "$mimetype" in

1382 [Ii]mage/x-xcf)

1383 bzip2 "$tmpd/$v"

1384 v=${v}.bz2

1385 ;;

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

1387 [Ii]mage/*)

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

1389 ;;

1390 esac

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

1392 then

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

1394 continue

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

1396 replpar text string \

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

1398 continue

1399 fi

1400 ;;

1401 *)

1402 type='string'

1403 ;;

1404 esac

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

1406 setpar "$k" "$type" "$v"

1407 done

1408 ;;

1409 *)

1410 setviastring "$s"

1411 ;;

1412 esac

1413 }

1414 email4group() {

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

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

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

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

1419 sql="WITH

1420 grpemails AS (

1421 SELECT gname, user, val email

1422 FROM grp_mem NATURAL JOIN grp_mem_s

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

1424 useremails AS (

1425 SELECT user.name, val email

1426 FROM user

1427 LEFT JOIN user_m

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

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

1430 FROM useremails u LEFT JOIN grpemails g

1431 ON u.name=g.user

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

1433 query "$sql"

1434 }

1435 email4groupbyuid() {

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

1437 g=$1; shift

1438 uids=`echo "$@"`

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

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

1441 email4group "$g" `query "$sql"`

1442 }

1443 myemail4group() {

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

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

1446 }

1447 collectmembersbyid() {

1448 # Collect user names of group specified by grid

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

1450 query "SELECT user FROM grp_mem \

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

1452 }

1453 collectmembersbyid() {

1454 # Collect user names of group name

1455 qgrp=`sqlquote "$1"`

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

1457 }

1458 collectgecosesbyid() {

1459 # Collect user gecoses of group

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

1461 query<<-EOF

1462 SELECT gecos

1463 FROM gecoses

1464 WHERE name IN (SELECT user FROM grp_mem

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

1466 EOF

1467 }

1468 collectemail() (

1469 # Collect email addresses for group $1

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

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

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

1473 for e; do

1474 if isuser "$e"; then

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

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

1477 else

1478 qgrp=`sqlquote "$e"`

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

1480 gmem="grp_mem"

1481 else

1482 tm=`sqlquote "$TEAM"`

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

1484 fi

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

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

1487 $gmem g left join grp_mem_s s

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

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

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

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

1492 query "$sql"

1493 fi

1494 done

1495 )

1496 sendinvitation() (

1497 # $1=email

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

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

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

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

1502 gecos=`gecos`

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

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

1505 _m4 -D_URL_="$urlbase" \

1506 -D_USER_="$name" \

1507 -D_EMAIL_="$1" \

1508 -D_REGIST_="$regist" \

1509 -D_ADMIN_="$admin" \

1510 $msgdir/mail-invite.m4 \

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

1512 return 0

1513 )

1514 emaildomaincheck() {

1515 case "$1" in

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

1517 *@*)

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

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

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

1521 return 2

1522 fi

1523 return 0

1524 ;;

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

1526 esac

1527 }

1528 invite() {

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

1530 case "$email" in

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

1532 *@*)

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

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

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

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

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

1538 elif sendinvitation $email; then

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

1540 else # Cannot be reached here

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

1542 fi ;;

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

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

1545 esac

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

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

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

1549 fi

1550 if [ -f $invite_policy ]; then

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

1552 else

1553 pol="$invite_policy"

1554 fi

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

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

1557 -D_INVITE_POLICY_="$pol" \

1558 $layout/html.m4.html $layout/invite.m4.html

1559 }

1560 regist() {

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

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

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

1564 echo "bye bye" | html p

1565 reutrn

1566 fi

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

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

1569 cat<<EOF

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

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

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

1573 EOF

1574 return

1575 fi

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

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

1578 # Fake login password to wasureta

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

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

1581 wasureta $email

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

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

1584 addsession $1 # for removal after 1 minute

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

1586 $layout/login.m4.html

1587 return

1588 }

1589 group_safename() {

1590 # Convert $1 to safe group name

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

1592 }

1593 groupupdate() {

1594 gname=`getpar gname`

1595 qgname=`sqlquote "$gname"`

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

1597 # See ALSO same job in showgroup()

1598 newgname=`group_safename "$gname"`

1599 err newgname=$newgname

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

1601 err NewGNAME: gname=$newgname

1602 gname=$newgname

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

1604 replpar gname string "$gname"

1605 fi

1606 # Name confliction check

1607 parow=`getpar rowid`

1608 ## err parow=$parow

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

1610 query "BEGIN EXCLUSIVE;"

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

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

1613 if [ $count -gt 0 ]; then

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

1615 query "END;"

1616 return

1617 fi

1618 par2table $formdir/grp.def

1619 query "END TRANSACTION;"

1620 # Remove orphan

1621 : <<EOF

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

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

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

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

1626 EOF

1627 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1631 sql="delete from blog where id in

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

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

1634 query "$sql";

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

1636 return # and return

1637 fi

1638 fi

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

1640 fi

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

1642 grid=$(query $sql)

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

1644 grp $grid

1645 }

1646 groupclone() {

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

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

1649 case "$1" in

1650 */noteam)

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

1652 esac

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

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

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

1656 return

1657 fi

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

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

1660 return

1661 fi

1662 i=0

1663 while true; do

1664 copy="-copy$i"

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

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

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

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

1669 i=$((i++))

1670 continue

1671 fi

1672 break

1673 done

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

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

1676 query<<-EOF

1677 BEGIN;

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

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

1680 SELECT $newqname, key, val

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

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

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

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

1685 -- Copy members and their configuration --

1686 REPLACE INTO grp_mem SELECT $newqname, user

1687 FROM grp_mem WHERE gname=$qgrp;

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

1689 FROM grp_mem_s WHERE gname=$qgrp;

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

1691 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1692 -- Copy administrators --

1693 REPLACE INTO grp_adm SELECT $newqname, user

1694 FROM grp_adm WHERE gname=$qgrp;

1695 COMMIT;

1696 EOF

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

1698 STOPCLONEMSG=1 groupconf "$newrowid"

1699 }

1700 groupman() {

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

1702

1703 GF_STAGE="grpconf"

1704 GF_STAGE=groupupdate

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

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

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

1708 -D_DUMPTABLE_="syscmd(cat)" \

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

1710 }

1711 userconf() {

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

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

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

1715 }

1716 groupconf() {

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

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

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

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

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

1722

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

1724 grp=`getgroupbyid $rowid`

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

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

1727 return

1728 fi

1729

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

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

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

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

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

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

1736 <div>

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

1738 <table>

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

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

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

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

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

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

1745 </table>

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

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

1748 </div>

1749 EOF

1750 fi

1751 }

1752 mems() {

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

1754 kwd=`getpar kwd`

1755 listmember $kwd

1756 }

1757 grps() {

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

1759 kwd=`getpar kwd`

1760 listgroup $kwd \

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

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

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

1764 $layout/form+dump.m4.html

1765 }

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

1767 gpg=`getpar grp`

1768 grid=${1:-$gpg}

1769 grp=`getgroupbyid "$grid"`

1770 ## . ./s4-blog.sh

1771 jg=`getpar joingrp`

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

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

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

1775 fi

1776 htmlheader=$layout/html.m4.html

1777 showgroup "$grid"

1778 }

1779 sql4interestblogs() {

1780 cat<<EOF

1781 CREATE TEMPORARY VIEW interestblogs AS

1782 SELECT blog.rowid rid, id, author

1783 FROM blog

1784 NATURAL JOIN

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

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

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

1788 WHEN (SELECT user FROM grp_mem

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

1790 THEN 0

1791 ELSE 1

1792 END;

1793 EOF

1794 }

1795 listnewblogsql() { # $1=user

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

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

1798 newdays=${newdays:-14}

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

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

1801 WHERE user='$user'

1802 AND tblrowid IN

1803 ($blogreadflagrowid,

1804 $blogcutoffflagrowid)),

1805 $basetime -- "0"

1806 );"`

1807 cat<<EOF

1808 `sql4interestblogs`

1809 WITH article_ctime as (

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

1811 FROM article join article_s s using(id)

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

1813 GROUP BY id

1814 ), blog_title_owner as (

1815 SELECT blg.rid brid, id,

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

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

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

1819 ), blogall as (

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

1821 ), news as (

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

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

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

1825 FROM blogall bl

1826 LEFT JOIN

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

1828 ON bl.brid=al.tblrowid

1829 WHERE atime < bl.ctime

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

1831 LIMIT 10

1832 ) SELECT brid LINK, "新着",

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

1834 ctime, title,

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

1836 FROM news;

1837 EOF

1838 }

1839

1840 search_form() {

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

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

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

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

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

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

1847 @week → 最近一週間

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

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

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

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

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

1853 auth=""

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

1855 case "$1" in

1856 author=*)

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

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

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

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

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

1862 ;;

1863 grp=*)

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

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

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

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

1868 ;;

1869 esac

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

1871 cat<<-EOF

1872 <div class="right">

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

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

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

1876

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

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

1879

1880 </form>

1881 </div>

1882 EOF

1883 }

1884

1885 imgsrc_cache() (

1886 # $1 = directory for cache'ing

1887 # $2 = table (user_m or grp_m)

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

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

1890 dir="$1" tbl="$2"

1891 keycond="$3"

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

1893 ORDER BY rowid DESC LIMIT 1"

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

1895 tmpf=$tmpd/imgsrc_cache.$$

1896 case "$4" in

1897 [Ss]) size=S ;;

1898 [Oo]) size=O ;;

1899 *) size=M ;;

1900 esac

1901 # ImageCache filename storing schema:

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

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

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

1905 valtype=`query "$sql0"`

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

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

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

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

1910 case "$filetype" in

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

1912 esac

1913 fi

1914 cacheimg_S=$dir/S_$filename

1915 cacheimg_M=$dir/M_$filename

1916 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1924 else

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

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

1927 fifo2=$fifo.2

1928 mkfifo $fifo $fifo2

1929 fmt=${filename##*.}

1930 ## [[ NOTE ]]

1931 ## a. convert oldimage newimage

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

1933 ## b is much smaller than a

1934 cat $tmpf | unhexize \

1935 | tee $fifo \

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

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

1938 | tee $fifo2 \

1939 | convert - "$cacheimg_M" &

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

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

1942 | convert - "$cacheimg_S" &

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

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

1945 echo '">'

1946 echo "$sum" > $sumfile

1947 fi

1948 ## Now preparing cache image, done.

1949 ## Store this information to DB

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

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

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

1953 query <<-EOF

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

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

1956 EOF

1957 )

1958

1959 showhome() {

1960 # $1=userRowIdToShow

1961 err showhome \$1=$1

1962 case "$1" in

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

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

1965 esac

1966 ## err ShowHome: uname=$uname

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

1968 gecos=`gecos "$uname"`

1969 ## err SH:gecos=$gecos

1970 GF_VIEWONLY=1

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

1972 search_form_args=""

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

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

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

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

1977 # Display folders

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

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

1980 and type like 'file:%';"

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

1982 nfile=`query "$sql"`

1983 # err nfile=$nfile

1984 if [ $nfile -gt 0 ]; then

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

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

1987 fi

1988 else

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

1990 GROUP BY user;"`

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

1992 search_form_args="author=$uname"

1993 fi

1994 . ./s4-blog.sh

1995

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

1997 search_form "$search_form_args" > $sf

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

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

2000 viewtable $formdir/user.def user $1

2001 echo "</div>"

2002 } > $pf

2003

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

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

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

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

2008 if true; then

2009 tbl=user_m

2010 enticond="name='$uname'"

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

2012 else

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

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

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

2016 echo '">'

2017 }

2018 fi > $imf

2019 fi

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

2021 val='$uname';"`

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

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

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

2025 listblog $uname > $bf

2026 ## unset ddd

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

2028

2029 hometail=$tmpd/tail.$$

2030 mkfifo $hometail

2031

2032 #Calling listgroupbytable, originally here

2033

2034 (

2035 # Display Most Recent Entry

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

2037 shortval=${shortval:-val}

2038

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

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

2041 ctime,

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

2043 (SELECT gecos FROM gecoses

2044 WHERE name=(SELECT val FROM blog_s

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

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

2047 FROM blog b

2048 JOIN

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

2050 FROM article a, article_s s

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

2052 GROUP BY blogid ORDER BY val DESC LIMIT 50

2053 ) m

2054 ON b.id=m.blogid;"

2055 # This should be as follows

2056 : <<EOF

2057 WITH arts AS(

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

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

2060 FROM article a NATURAL JOIN article_s s

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

2062 GROUP by s.id

2063 )

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

2065 FROM arts a0

2066 JOIN

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

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

2069 ORDER BY ctime DESC LIMIT 50;

2070 EOF

2071

2072 cat<<-EOF

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

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

2075 <div class="lcto">

2076 `DT_VIEW=replyblog dumptable html blog`

2077 </div>

2078 EOF

2079 unset DT_SQL

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

2081 # Display NEWS

2082 # 2016-06-26

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

2084 acclog blog $blogreadflagrowid

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

2086 fi

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

2088 sql=`listnewblogsql "$user"`

2089 # echo "$sql" > tmp/listnew

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

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

2092 cont=$((cont-1))

2093 err newcount=$cont

2094 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2101 `cgi_submit '確定'`

2102 `cgi_hidden read yes`

2103 EOF

2104 echo "$new10 "

2105 echo "</div>"

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

2107 acclog blog $blogcutoffflagrowid # for speed

2108 fi

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

2110 : # DT_SQL=

2111 fi

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

2113 #

2114 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2120 -D_NBLOG_="$nblog" \

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

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

2123 $layout/html.m4.html $layout/home.m4.html

2124

2125 # Record access log

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

2127 }

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

2129 contenttype; echo

2130 ## err commission: "$@"

2131 gname=`getgroupbyid $1`

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

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

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

2135 grp_reg_adm "$@"

2136 else

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

2138 fi

2139 }

2140 listgroupbytable() {

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

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

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

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

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

2146 NGsql="select distinct tag from\

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

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

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

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

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

2152 for tag in `query "$sql"`

2153 do

2154 ## err ListGrp: tag=$tag

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

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

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

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

2159 as rowid,

2160 gname,

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

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

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

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

2165 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2170 echo '</table>'

2171 done

2172 echo '</div>'

2173 }

2174 iconhref() (

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

2176 data=`percenthex "$1"`

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

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

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

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

2181 )

2182 iconhref2() (

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

2184 src=$1

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

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

2187 )

2188 listentry() (

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

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

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

2192 cond='' hiddens=''

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

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

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

2196 offset=$((offset-1))

2197 fi

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

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

2200 limit=$listentlimit

2201 dir=`getcachedir "$1"`

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

2203 hrb="$myname?home"

2204 deficon=person-default.png

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

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

2207 gcs=gecos

2208 else # if group

2209 hrb="$myname?grp"

2210 deficon=group-default.png

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

2212 gcs=name

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

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

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

2216 ## err tagconv=$tagconv

2217 fi

2218 fi

2219 if [ ! -d $dir ]; then

2220 mkdir -p $dir

2221 fi

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

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

2224 fi

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

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

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

2228 fi

2229 tag=`getpar tag` tag2=`getpar tag2`

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

2231 tag="" tag2=""

2232 fi

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

2234 tag=${tag:-$tag2}

2235 qtag=`sqlquote "$tag"`

2236 cond2="tag=$qtag"

2237 fi

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

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

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

2241 fi

2242

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

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

2245 ##qgrp=`sqlquote $grp`

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

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

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

2249 quote(a.$nm) as qname,

2250 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

2257 end

2258 as name,

2259 b.tag,

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

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

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

2263 then 'ADMIN'

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

2265 then 'Member'

2266 when '$iamowner' = '' then ''

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

2268 CASE '$entity'

2269 WHEN 'グループ'

2270 THEN coalesce(

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

2272 'open')

2273 ||

2274 CASE WHEN '$user'

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

2276 THEN ' ismember'

2277 ELSE ''

2278 END

2279 ELSE 'user'

2280 END regmode

2281 from $tbl a left join

2282 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

2293 # Note that mtime is stored only in grp_s.

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

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

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

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

2298 # Show owner/member filter button

2299 METHOD=GET

2300 hiddens="$hiddens

2301 `cgi_hidden kwd \"$kwd\"`

2302 `cgi_hidden stage \"$stage\"`"

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

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

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

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

2307 for l in $args; do

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

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

2310 s=" selected"

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

2312 else

2313 s=""

2314 fi

2315 form=$nl$form"<option value=\"$val\"$s>$tname</option>"

2316 done

2317 form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"

2318 cat<<-EOF

2319 <form action="$myname" method="$METHOD">

2320 </form>

2321 以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label

2322 for="ismembtn">参加中以外隠す</label>

2323 `cgi_checkbox onlyadm no 'id="isadmbtn"'`<label

2324 for="isadmbtn">管理者参加以外隠す</label>

2325 EOF

2326 # limit=3

2327 hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`

2328 fi

2329 if [ $total -gt $limit ]; then

2330 echo '<div>'

2331 METHOD=GET cgi_form $stage <<EOF

2332 $form

2333 <label>次の語を含む${entity}で検索:

2334 `cgi_text kwd "$kwd"`</label>

2335 $hiddens

2336 EOF

2337 echo '</div>'

2338 else

2339 echo $selectedtags | html p

2340 fi

2341 cat<<EOF

2342 <form action="$myname" method="$METHOD">

2343 <p>${total}件中の<input class="hidesub" type="text" name="start"

2344 value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens

2345 <input type="submit" value="確定"></p>

2346 </form>

2347 EOF

2348 if [ $((offset+limit)) -lt $total ]; then

2349 nextbtn=$(

2350 cat<<EOF

2351 <div class="right clear"><form action="$myname" method="$METHOD">

2352 `cgi_submit 次の${limit}件`

2353 $hiddens

2354 `cgi_hidden offset $((offset + limit))`</form></div>

2355 EOF

2356 )

2357 fi

2358 if [ $offset -gt 0 ]; then

2359 prevbtn=$(

2360 cat<<EOF

2361 <form action="$myname" method="$METHOD">

2362 `cgi_submit 前の${limit}件`

2363 $hiddens

2364 `cgi_hidden offset $((offset - limit))`</form>

2365 EOF

2366 )

2367 fi

2368 pnbtn="$nextbtn$prevbtn"

2369 echo $pnbtn

2370

2371 ## err ListEntry: `echo "$sql"\;`

2372 # sq $db here??? 2016-11-28

2373 query "$sql limit $limit ${offset:+offset $offset};" \

2374 | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do

2375 # err name=$name owner=$ownerp lnk=$lnk

2376 # err newlnk=$lnk regmode=$regmode

2377 icondir=$dir/$id

2378 # Pick up only last icon

2379 echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">

2380 <p class=\"tag _$tag\">$tag</p>" \

2381 | _m4 $tagconv

2382 if [ -n "$NOSPEEDUP" ]; then

2383 files=`getvalbyid $tbl profimg $id $icondir`

2384 if [ -n "$files" ]; then

2385 icon=`echo "$files"|tail -1`

2386 iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"

2387 else

2388 iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"

2389 fi

2390 elif [ -n "$icon" -a -s "$icon" ]; then

2391 iconhref2 "$icon" "$hrb+$lnk" "$gecos"

2392 else

2393 cond="$nm=$qname"

2394 # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S

2395 # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"

2396 img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`

2397 # err "img=[$img]"

2398 if [ -n "$img" ]; then

2399 echo "<a href=\"$hrb+$lnk\">"

2400 imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S

2401 echo "</a>"

2402 else

2403 iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"

2404 fi

2405 fi

2406 echo "<br>$name${ownerp:+<br>($ownerp)}"

2407 echo "</div>"

2408 done

2409 echo "</div>" # End of List-entry div

2410 echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}

2411 )

2412 listmember() {

2413 listentry user "$@"

2414 }

2415 listgroup() {

2416 listentry group "$@"

2417 }

2418 hexteams() { # $1=gname, $2(optional)=user

2419 cond=${2:+" AND user='$2'"}

2420 query "SELECT DISTINCT hex(val) FROM grp_mem_m

2421 WHERE gname='$1' AND key='team'$cond ORDER by val;"

2422 }

2423 showgroup() { # $1=group-rowid

2424 if [ -z "$1" ]; then

2425 grid=`getpar grid`

2426 grid=${grid%%[!0-9]*}

2427 [ -n "$grid" ] && grp=`getgroupbyid $grid`

2428 else

2429 grid=$1

2430 fi

2431 grp=`getgroupbyid $grid`

2432 qgrp=`sqlquote "$grp"`

2433 htmlgrp=`echo "$grp"|htmlescape`

2434 ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"

2435 if isgroup "$grp"; then

2436 tf=$tmpd/title.$$

2437 sf=$tmpd/search.$$

2438 bodyclass=`query "SELECT val FROM grp_s

2439 WHERE gname=$qgrp AND key='regmode';"`

2440 if ismember "$user" "$grp"; then

2441 ismember="ismember"

2442 bodyclass="$bodyclass${bodyclass:+ }ismember"

2443 else

2444 ismember="" # bodyclass="group"

2445 fi

2446 bodyclass="$bodyclass grouphome"

2447 echo "<div class=\"search\">`search_form grp=\"$htmlgrp\"`</div>"> $sf

2448 echo "グループ $htmlgrp" > $tf

2449

2450 showgroupsub $formdir/grp.def "$grid" | \

2451 _m4 -D_TITLE_="syscmd(\`cat $tf')" \

2452 -D_FORM_="syscmd(\`cat')" \

2453 -D_BODYCLASS_="$bodyclass" \

2454 -D_DUMPTABLE_="" \

2455 $htmlheader $sf $layout/form+dump.m4.html

2456 # $htmlheader $layout/form+dump.m4.html

2457 # $htmlheader is defined in grp()

2458 else # if $grp is removed at par2table

2459 listgroup

2460 fi

2461 }

2462 showgroupsub() {

2463 # $1=def-file $2=group-rowid

2464 # Using $ismember

2465 rowid=$2

2466 grp=`getgroupbyid $2`

2467 qgrp=`sqlquote "$grp"`

2468 td=`getcachedir grp/"$2"`

2469 #rowid=`sq $db "select rowid from grp where gname=$qgrp"`

2470 if [ -z "$rowid" ]; then

2471 #rowid=`sq $db "select rowid from grp where rowid=$grp"`

2472 #grp=`sq $db "select gname from grp where rowid=$grp"`

2473 echo "showgroupsub: invalid argument($1 $2)" | html p

2474 return

2475 fi

2476 val=`getvalbyid grp profimg $rowid $tmpd`

2477 enticond="gname=$qgrp"

2478 img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`

2479 if [ -n "$img" ]; then

2480 cat<<-EOF

2481 <p class="groupimg">

2482 `imgsrc_cache $td/main grp_m "$enticond" M`</p>

2483 EOF

2484 fi

2485 echo "<div class=\"noprofimg\">"

2486 viewtable $1 grp $rowid

2487 echo "</div>"

2488 if isgrpowner "$user" "$grp"; then

2489 echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"

2490 iamowner=$rowid

2491 colmd=" mode"

2492 fi

2493 if [ -n "$ismember" ]; then

2494 echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"

2495 echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"

2496 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2497 cat<<EOF

2498 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2499 <div class="fold clear">

2500 `cgi_checkbox send yes id="send"`<label

2501 for="send">グループ全員にメッセージ送信</label>

2502 <div>

2503 `cgi_textarea message "" "cols=60"`

2504 `cgi_submit 送信`

2505 `cgi_reset リセット`

2506 </div>

2507 `cgi_hidden grp $rowid`

2508 </div></form>

2509 EOF

2510 fi

2511 # 加入ボタン + 加入者リスト

2512 if [ -n "$ismember" ]; then

2513 ismem='checked' state="(参加中)"

2514 else

2515 nomem='checked' state="(現在非加入)"

2516 fi

2517 # このグループでの加入アドレス

2518 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2519 and key='email';"`

2520 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2521 ## and key='email';"

2522 ##err email=$eml

2523 cat <<EOF

2524 <div class="fold clear">

2525 `cgi_checkbox reg yes id="reg"`<label

2526 for="reg">自身の加入状態を操作する</label>$state

2527 <div>

2528 EOF

2529 cgi_form grp <<EOF

2530 <p>このグループに</p>

2531 <table class="b">

2532 <tr><th>メンバーとして</th><td>

2533 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2534 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2535 <tr><th>参加する場合のメイルアドレス<br>

2536 <small>(メインのアドレスとは違うものにする場合に記入<br>

2537 同じでよい場合は空欄に)</small></th>

2538 <td>`cgi_text email $eml`</td></tr>

2539 </table>

2540 `cgi_hidden grp $rowid`

2541 EOF

2542 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2543 echo "moderated (承認加入の)グループなので実際に参加できるのは

2544 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2545 fi

2546 echo '</div></div>'

2547 echo '<h2>話題一覧</h2>'

2548 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2549 cat<<-EOF

2550 <form class="summary" action="$myname" title="$thelp">

2551 `cgi_hidden owner "$grp"`

2552 `cgi_hidden kwd "@week"`

2553 `cgi_hidden stage searchart`

2554 `cgi_submit "一週間のまとめ"`

2555 </form>

2556 EOF

2557 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2558 colstate="state:稼動状態:frozen=rowclass=凍結"

2559 DT_CHLD=article:blogid \

2560 DT_QOWNER="$qgrp" \

2561 DT_VIEW=replyblog dumptable html blog \

2562 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2563

2564 getgname="(select gname from grp where rowid=$rowid)"

2565 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2566 cm="?commission+$rowid"

2567 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2568 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2569 # team list

2570 hexteams=`hexteams "$grp"`

2571 if [ -n "$hexteams" ]; then

2572 echo "チーム一覧" | html h2

2573 echo '<div class="dumptable"><table class="b">'

2574 sq $db -html -header<<-EOF

2575 SELECT val TEAM,

2576 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2577 MEMBERS

2578 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2579 EOF

2580 echo '</table></div>'

2581 fi

2582 }

2583 grp_getbodyclass() {

2584 # Get css class name for document.

2585 # `moderated' for moderated groups

2586 # `ismember' for groups where user belongs

2587 # $1=GroupName (w/o quote)

2588 # $user=userNameCurrentlyLogin

2589 ## err grp_getbodyclass: 1="$1"

2590 qgrp=`sqlquote "$1"`

2591 query<<-EOF

2592 SELECT coalesce(

2593 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2594 'open')

2595 ||

2596 CASE WHEN '$user'

2597 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2598 THEN ' ismember'

2599 ELSE ''

2600 END;

2601 EOF

2602 }

2603 grpaction() { # $1=group-rowid

2604 err GRP_ACTION:IN

2605 grid=${1:-`getpar grp`}

2606 grp=`getgroupbyid "$grid"`

2607 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

2608 if [ -z "$grp" ]; then

2609 echo "無効な指定です。" | html p; return

2610 fi

2611 if ! ismember $user "$grp"; then

2612 echo "加入者のみに許可された操作です。" | html p; return

2613 fi

2614 echo "グループ $grp 個別選択操作" \

2615 | _m4 -D_TITLE_="syscmd(\`cat')" \

2616 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2617 $layout/html.m4.html

2618

2619 isowner=""

2620 isgrpowner "$user" "$grp" && isowner="yes"

2621 usel=`getpar usel`

2622 if [ -n "$usel" ]; then

2623 uids=$(echo `echo $usel`|tr ' ' ',')

2624 ## err grpaction-1: grp=$grp, `echo $sql`

2625 text=`getpar text|tr -d '\r'`

2626

2627 rm=`getpar rm` cfm=`getpar confirm`

2628 ## err rm=$rm cfm=$cfm

2629 if [ x"$rm" = x"yes" ]; then

2630 if [ "$isowner" ]; then

2631 if [ x"$rm$cfm" = x"yesyes" ]; then

2632 # Eliminate

2633 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2634 for tbl in grp_mem grp_mem_s grp_mem_m; do

2635 sql="delete from $tbl $cond;"

2636 # echo "sql=$sql"

2637 query "$sql"

2638 err rmGRPuser "$sql"

2639 done

2640 num=`query "select count(*) from user where rowid in ($uids);"`

2641 #err num=$num

2642 if [ 0$num -gt 0 ]; then

2643 sql="select coalesce(b.val,a.name) from user a left join \

2644 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2645 # err `echo "$sql"`

2646 html pre<<EOF

2647 以下の${num}名のグループ $grp 登録を解除しました。

2648 `query "$sql"`

2649 EOF

2650 fi

2651 else

2652 echo "確認のチェックがないのでやめておきます。" | html p

2653 return

2654 fi

2655 else # not Group Owner

2656 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2657 return

2658 fi

2659 cat<<EOF

2660

2661 EOF

2662 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2663 if [ -z "$text" ]; then # if msg is empty

2664 echo "なにかメッセージを..." | html p

2665 return 0

2666 fi

2667 gecos=`gecos $user`

2668 safegc=`echo "$gecos" | tr -d '<>@'`

2669 #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

2670 fromad=`myemail4group "$grp"`

2671 ###mail_from="$safegc <$fromad>"

2672 mail_from="$safegc <$user>" # TEST: 2020/5/13

2673 test -n `getpar sender` &&

2674 export SENDER=$user # TEST: 2020/5/15

2675 replyto=$fromad

2676

2677 ## Start parse of attachment files

2678 if [ -n "`getpar email`" ]; then

2679 ar=`getpar supprcpt`

2680 if [ -n "$ar" ]; then

2681 for a in $ar; do

2682 checkdomain "$a" && supprcpt="$supprcpt $a"

2683 done

2684 fi

2685 subj=`getpar subject`

2686 afiles=""

2687 for fn in `query "SELECT DISTINCT val FROM par WHERE var='files';"`

2688 do

2689 f=$tmpd/$fn

2690 if [ -s $f ]; then

2691 afiles=$afiles"${afiles:+ }$f"

2692 fi

2693 done

2694 else

2695 preface=$(cat <<-EOF

2696 $url

2697 のグループ「$grp」のメンバーである $gecos さんから、

2698 あなた宛のメッセージです。

2699 ----------------------------------------------------------

2700 EOF

2701 )

2702 fi

2703 rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"

2704 rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq`

2705 subj="${subj:-$gecos さんからのメッセージ}"

2706 REPLYTO=$replyto

2707 MAIL_FROM=$mail_from

2708 export REPLYTO SMAIL_TO MAIL_FROM

2709 err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"

2710 for r in $rcpts; do

2711 SMAIL_TO="$r $user $fromad"

2712 if [ -n "$afiles" ];then

2713 ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles

2714 else

2715 smail "$r" "$subj"

2716 fi <<EOF

2717 ${preface:+$preface$nl}$text

2718 EOF

2719 done

2720 if [ $? = 0 ]; then

2721 echo "Note: 以下のメンバーにメッセージを送信しました。" | html p

2722 sql="select coalesce(b.val, a.name) from

2723 (select name from user where rowid in ($uids)) a

2724 left join user_s b on a.name=b.name and b.key='gecos';"

2725 html pre<<EOF

2726 `query "$sql"`

2727 ${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)

2728 EOF

2729 err SendDone: `echo $sql`

2730 fi

2731 elif [ x"$rm" = x"commission" ]; then

2732 grp_reg_adm $grid $usel

2733 elif [ x"$rm" = x"addteam" ]; then

2734 team=`getpar team|sed "s/'/''/g"` # for single quotation

2735 newteam=`echo $team|tr -d ,` # ..and strip spaces of both ends

2736 if [ x"$team" != x"$newteam" ]; then

2737 echo "チーム名に使えない文字を除去しました" | html p

2738 team=newteam

2739 fi

2740 if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; then

2741 cat<<-EOF | html p

2742 有効なチーム名を入力してください。

2743 カンマだけ、「なし」という名前は使えません。

2744 EOF

2745 echo "有効なチーム名を入力してください。" | html p

2746 else

2747 grp_add_team $grid "$team" $usel

2748 fi

2749 elif [ x"$rm" = x"rmteam" ]; then

2750 if [ x"yes" = x"`getpar teamconfirm`" ]; then

2751 rmteam=`getpar rmteam|sed "s/'/''/g"`

2752 if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\

2753 gname='$grp' AND user='$user' AND key='team'\

2754 AND val='$rmteam';\"`" ]; then

2755 grp_rm_team $grid "$rmteam" $usel

2756 else

2757 echo "所属していないチームの除去操作はできません。"|html p

2758 fi

2759 else

2760 echo "確認チェックなしなのでチーム除去しませんでした。"|html p

2761 fi

2762 fi

2763 fi

2764 # POST count summary

2765 from=`getpar from`; to=`getpar to`

2766 from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"

2767 to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"

2768 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2769 # New entry

2770 sql="WITH mems AS (

2771 SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g

2772 ON gm.user=g.name

2773 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2774 ), target_article AS (

2775 SELECT id FROM article_s

2776 WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'

2777 ), posts AS (

2778 SELECT author, count(author) post

2779 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2780 WHERE blogid IN (SELECT id FROM blog_s

2781 WHERE key='owner'

2782 AND val=(SELECT gname FROM grp WHERE rowid=$grid))

2783 AND key='text'

2784 GROUP BY author

2785 ), teams AS (

2786 SELECT user, group_concat(val, ', ') team

2787 FROM grp_mem_m

2788 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2789 AND key='team'

2790 GROUP BY user

2791 ), user_post AS (

2792 SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST

2793 FROM mems m LEFT JOIN posts

2794 ON m.name=posts.author

2795 GROUP by m.rowid

2796 )

2797 SELECT

2798 CASE

2799 WHEN (SELECT user FROM grp_adm

2800 WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)

2801 AND user=up.name) IS NOT NULL

2802 then 'k'

2803 ELSE ''

2804 END || rowid || ',' || gecos NAME,

2805 post POST, team _TEAM_

2806 FROM user_post up LEFT JOIN teams t

2807 ON up.name=t.user

2808 ORDER BY gecos;"

2809 ## err grpaction: "`echo \"$sql\"`"

2810 tf=$tmpd/title.$$

2811 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf

2812 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

2813 for=\"cmadmin\">管理者委任</label>

2814 <div><p>このグループでの全権を付与します。信頼できる人に託してください。

2815 </p></div>"

2816 excmsg="`cgi_radio rm yes id=\"conf\"`<label

2817 for=\"conf\">GRP登録解除</label>

2818 <div>本当に消します! `cgi_checkbox confirm yes` 確認

2819 <p>この操作による通知は本人に行きません。

2820 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2821 </div>"

2822 # Get team list to which current user belongs into $hexteams

2823 allhexteams=$(hexteams "$grp")

2824 if [ -n "$isowner" ]; then

2825 myhexteams="$allhexteams" # admin can remove all teams' attr

2826 else

2827 myhexteams=$(hexteams "$grp" "$user")

2828 fi

2829 if [ -n "$myhexteams" ]; then

2830 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

2831 for=\"cmrmteam\">チーム属性除去</label>

2832 <div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`

2833 を除去します: `cgi_checkbox teamconfirm yes` 確認

2834 <p>この操作による通知は本人に行きません。

2835 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>

2836 </div>

2837 "

2838 fi

2839 stf=$tmpd/selteam.$$

2840 cgi_select_h selteam "5445414d" $allhexteams > $stf

2841 b1='<label> <input type="checkbox" name="usel" value="'

2842 ba='<label class="admin"><input type="checkbox" name="usel" value="'

2843 br='<span id="reverse" title="Reverse Selection"></span>'

2844 #b2='"> <span>' b3='</span></label>'

2845 # | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$b2\3$b3|" \

2846 # -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$b2\3$b3|" \

2847 lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'

2848 cgi_form grpaction<<EOF \

2849 | sed -e "s|^$<TR><TD>$k$[0-9]*$,$[^<]*$|\1$ba\2$lnk|" \

2850 -e "s|^$<TR><TD>$$[0-9]*$,$[^<]*$|\1$b1\2$lnk|" \

2851 -e "s|^$<TR><TH>$$NAME$|\1$br \2|" \

2852 | _m4 -D_TITLE_="spaste(\`$tf')" \

2853 -D_SUBTITLE_="チェック後操作ボタン" \

2854 -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \

2855 $layout/form+dump.m4.html \

2856 | _m4 -D_TEAM_="spaste(\`$stf')"

2857 <p>下でチェックした人を対象として:</p>

2858 <div class="foldtabs">

2859 `cgi_radio rm addteam 'id="cmteam"'`<label

2860 for="cmteam">同じチーム属性を付与</label>

2861 <div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`

2862 `cgi_datalist_h teams $allhexteams`

2863 </div>

2864 ${rmteammsg}

2865 `cgi_radio rm send id="sendmsg"`<label

2866 for="sendmsg" title="Direct Message">DM送信</label>

2867 <div>

2868 `cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"

2869 >email書式を使う</label>

2870 <div class="folded">

2871 <table>

2872 <tr><td>From: </td><td>$user</td></tr>

2873 <tr><td>このFrom:で送る</td>

2874 <td>`cgi_checkbox sender yes 'checked'`<small></small>

2875 </td></tr>

2876 <tr><td>Subject: </td><td>`cgi_text subject`</td></tr>

2877 <tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>

2878 <tr><td>ファイル添付: </td>

2879 <td>`cgi_file files "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>

2880 </table>

2881 <p>(下記一覧から1人以上選択していない場合は送れません`cgi_submit 確認後送信`)</p>

2882 </div>

2883 <div>本文:`cgi_textarea text "" cols=72`

2884 </div>

2885 </div>

2886 ${isowner:+$cmmsg$excmsg}

2887 `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>

2888 </div>

2889 <h4>$grp 参加者一覧</h4>$fromtonote

2890 <table class="td2r thl">

2891 `sq $db -header -html "$sql"`

2892 </table>

2893 `cgi_hidden grp $grid`

2894 `cgi_hidden myuid $myuid id="myuid"`

2895 EOF

2896 }

2897 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL

2898 # Create TEMPORARY VIEW

2899 query<<EOF

2900 CREATE TEMPORARY VIEW writeusers AS

2901 SELECT DISTINCT author FROM article

2902 WHERE id in (

2903 select id from article where blogid=(select id from blog where rowid=$1)

2904 );

2905 CREATE TEMPORARY VIEW movablegroups AS

2906 SELECT g.rowid growid , g.gname

2907 FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm

2908 ON grp.gname=gm.gname -- そのユーザが属している

2909 AND user='$user') g -- グループに絞る

2910 WHERE (SELECT author FROM writeusers

2911 EXCEPT

2912 SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)

2913 IS NULL;

2914 $2

2915 EOF

2916 }

2917 sql4readableblogs() {

2918 # Create view of blogs that can be readable to $user

2919 # Blog is readable when:

2920 # 1: blog owner is an user

2921 # 2: else, 2.1: owner-group where the $user belongs

2922 # 2.2: else, owner-group is not moderated

2923 # blog(id, author), blog_s(id, key='owner', val= ->owner)

2924 cat<<EOF ## | tee tmp/sql.out

2925 CREATE TEMPORARY VIEW readableblogs AS

2926 SELECT blog.rowid rid, id, author

2927 FROM blog

2928 NATURAL JOIN

2929 (SELECT id,

2930 max(CASE key WHEN 'owner' THEN val END) owner,

2931 max(CASE key WHEN 'mode' THEN val END) mode

2932 FROM blog_s GROUP by id) bs

2933 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL

2934 THEN 1 -- blog owner is an user, READABLE

2935 WHEN (SELECT val FROM grp_s

2936 WHERE gname=bs.owner AND key='regmode') = 'moderated'

2937 AND

2938 (SELECT user FROM grp_mem

2939 WHERE gname=bs.owner AND user='$user') IS NULL

2940 THEN 0

2941 WHEN mode IN ('quiz', 'enquete')

2942 THEN 0 -- "quiz" mode blog cannot be searched

2943 ELSE 1

2944 END;

2945 EOF

2946 }

2947 editheading() { # $1=rowid-of-heading

2948 rowid=${1%%[!A-Z0-9a-z_]*}

2949 if [ -z "$rowid" ]; then

2950 echo "話題番号が未指定です。" | html p

2951 return

2952 fi

2953 owner=`getvalbyid blog owner $rowid`

2954 title=`getvalbyid blog title $rowid`

2955 GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \

2956 | _m4 -D_TITLE_="修正" \

2957 -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \

2958 -D_BLOGS_="" -D_DUMPTABLE_="" \

2959 -D_FORM_="syscmd(\`cat')" \

2960 $layout/html.m4.html $layout/form+dump.m4.html

2961 # Move to group

2962 if isuser "$owner"; then

2963 crview4article $rowid

2964 n=`query "SELECT count(*) FROM writeusers;"`

2965 ## err N=$n

2966 if [ $((n)) -gt 0 ]; then

2967 ## err ROWID=$rowid

2968 sql="SELECT growid || ':' || gname FROM movablegroups;"

2969 cat<<-EOF

2970 <div class="fold">

2971 `cgi_checkbox mv send id="mv"`<label

2972 for="mv">この話題をグループ所有に移動する</label>

2973 <div>

2974 <form action="?mvart" method="POST" enctype="multipart/form-data">

2975 移動先グループ:

2976 <select name="mv2grp">

2977 EOF

2978 query ".mode html"

2979 query<<-EOF |

2980 $sql

2981 .mode list

2982 EOF

2983 sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \

2984 -e 's,n>$[0-9]*$:$.*$<,n value="\1">\2<,'

2985 cat<<-EOF

2986 </select>

2987 <p>(移動できるグループは、この「話題」に書き込んでいる人全てが

2988 そのグループに加入しているものに限られます)</p>

2989 <p>`cgi_checkbox cfm yes`<label>確認

2990 (この操作は元に戻すことができません)</label></p>

2991 `cgi_hidden blogrowid $rowid`

2992 `cgi_submit 移動`

2993 `cgi_reset Reset`

2994 </form>

2995 </div>

2996 </div>

2997 EOF

2998 fi

2999 # end of isuser "$owner"

3000 elif { hexteams=$(hexteams "$owner" ) # blog is of GROUP

3001 [ -n "$hexteams" ];}; then

3002 none="`echo なし|hexize`"

3003 cat<<-EOF

3004 <div class="fold">

3005 `cgi_checkbox mv2team send id="mv2team"`<label

3006 for="mv2team">この話題を以下のチームのものにする</label>

3007 <div><p>現在の所属チーム設定:

3008 `query "SELECT

3009 coalesce((SELECT val FROM blog_s

3010 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)

3011 AND key='team'),

3012 ':なし');"`</p>

3013 <form action="?mvart" method="POST" enctype="multipart/form-data">

3014 移動先チーム: `cgi_select_h mv2team $none $hexteams`

3015 <p>`cgi_checkbox cfm yes`<label>確認</label></p>

3016 `cgi_hidden blogrowid $rowid`<br>

3017 `cgi_submit 移動`

3018 `cgi_reset Reset`

3019 </form></div></div>

3020 EOF

3021 fi

3022 }

3023 mvart() { # move diary to some group or team

3024 # or move blog of group to team which belong to the group

3025 blogrowid=`getpar blogrowid`

3026 cfm=`getpar cfm`

3027 ##### echo move blog:$blogrowid to $mv2grp | html p

3028 blogrowid=${blogrowid%%[!A-Z0-9a-z_]*} # Purify

3029 . ./s4-blog.sh

3030 if [ -z "$blogrowid" ]; then

3031 echo "無効な指定です(mvart)。" | html p

3032 return

3033 elif [ x"$cfm" != x"yes" ]; then

3034 echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p

3035 elif { mv2grp=`getpar mv2grp`

3036 mv2grp=${mv2grp%%[!A-Z0-9a-z_]*} # Purify

3037 [ -n "$mv2grp" ]; }; then

3038 crview4article $blogrowid

3039 ########## TRANSACTION BEGIN

3040 query "BEGIN;"

3041 n=`query "SELECT count(*) FROM writeusers;"`

3042 ## err Nwriteuser=$n

3043 if [ $((n)) -gt 0 ]; then

3044 query<<-EOF

3045 UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)

3046 WHERE key='owner'

3047 AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3048 AND $mv2grp IN (SELECT growid FROM movablegroups);

3049 EOF

3050 fi

3051 query "END;"

3052 ########## TRANSACTION END

3053 elif { mv2team=`getpar mv2team|sed "s/'/''/g"`

3054 [ -n "$mv2team" ];}; then

3055 # blog owner can move it to ANY team

3056 case "$mv2team" in

3057 'なし')

3058 cat<<-EOF

3059 DELETE FROM blog_s

3060 WHERE id=(SELECT id FROM blog WHERE rowid=$blogrowid)

3061 AND key='team';

3062 EOF

3063 ;;

3064 "") ;;

3065 *)cat<<-EOF

3066 BEGIN;

3067 REPLACE INTO blog_s(id, key, val)

3068 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3069 'team', '$mv2team');

3070 REPLACE INTO blog_s(id, key, val)

3071 VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),

3072 'notify', 'all'); -- Change notify to all

3073 END;

3074 EOF

3075 esac | query

3076 fi

3077 blog_reply $blogrowid

3078 echo yes | html p

3079 }

3080 editart() { # $1=article-rowid $2=blogrowid

3081 rowid=${1%%[!A-Z0-9a-z_]*}

3082 blogrowid=${2%%[!A-Z0-9a-z_]*}

3083 if [ -z "$rowid" -o -z "$blogrowid" ]; then

3084 echo "表示する記事番号が未指定です。" | html p

3085 return

3086 fi

3087 owner=`getvalbyid blog owner $blogrowid`

3088 title=`getvalbyid blog title $blogrowid`

3089 author=`getvalbyid article author $rowid`

3090 ## err EDITart: owner=$owner, author=$author

3091 if isgrpowner "$user" "$owner"; then

3092 : EDIT OK

3093 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3094 echo "本人か所有者しか編集できません." | html p

3095 return

3096 fi

3097 aid=`query "select id from article where rowid=$rowid;"`

3098 tmpout=$tmpd/editart.$$.out

3099 GF_ACTION="?replyblog+$blogrowid#$aid" \

3100 edittable $formdir/article.def article $rowid \

3101 > $tmpout

3102 rm -f /tmp/editart.out

3103 # Cannot use pipelining to m4 with genform() because of stdin stack

3104 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3105 -D_FORM_="syscmd(cat $tmpout)" \

3106 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3107 -D_BLOGS_= -D_DUMPTABLE_= \

3108 $layout/html.m4.html $layout/form+dump.m4.html

3109 }

3110 send2mem() {

3111 rowid=`getpar grp`

3112 rowid=${rowid%%[!0-9]*} # Cleaning

3113 if [ -z "$rowid" ]; then

3114 echo "グループが未指定です。" | html p

3115 return

3116 fi

3117 message=`getpar message`

3118 if [ -z "$message" ]; then

3119 echo "文章を入れてください。" | html p

3120 return

3121 fi

3122 grp=`getgroupbyid $rowid`

3123 members=`collectemail $grp`

3124 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3125 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3126 mailfrom="`gecos "$user"` <$mailfrom>"

3127 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3128 msg=$(cat<<-EOF

3129 $urlbase?grp+$rowid

3130 グループ $grp に所属する

3131 `gecos $user` さんよりメッセージ:

3132

3133 $message

3134 EOF

3135 )

3136 # smail rcpt subj (file)

3137 for m in $members; do

3138 echo "$msg" |

3139 MAIL_FROM=$mailfrom \

3140 SENDER=$noreply \

3141 REPLYTO=$mailfrom \

3142 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3143 smail "$m" "$sj"

3144 done

3145 cat<<EOF

3146 <p>以下のユーザに送信しました。</p>

3147 <pre>

3148 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3149 </pre>

3150 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3151 EOF

3152 }

3153 joingrpadmit() {

3154 # $1=yes/no $2=session-key

3155 if [ -z "$2" ]; then

3156 echo "bye bye" | html p; return

3157 fi

3158 t_usr=`session=$2 getpar user`

3159 t_grp=`session=$2 getpar group`

3160 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3161 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3162 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3163 echo "無効な加入依頼です。" | html p

3164 echo "有効期限が切れたか、

3165 他の管理者がいる場合は処理済みの可能性があります。" | html p

3166 return

3167 fi

3168 if ! isgrpowner "$user" "$t_grp"; then

3169 echo "グループ管理者のみの機能です。" | html p; return

3170 fi

3171 case $1 in

3172 yes) joingrp "$t_grp" "$t_usr" yes ;;

3173 no) joingrp "$t_grp" "$t_usr" no ;;

3174 *)

3175 echo "無効な指定です($1)。" | html p

3176 return ;;

3177 esac

3178 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3179 rcpts="`getgroupadminmails $t_grp` $user"

3180 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3181 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3182 に

3183 $t_usr

3184 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3185 しました。"

3186 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3187 query "delete from session where id='$2';"

3188 echo "$body" | html p

3189 }

3190

3191 joingrprequest() {

3192 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3193 jss="joingrp-`date +%s`-`genrandom 12`"

3194 addsession $jss +${memoplimitdays}days

3195 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3196 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3197 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3198 $url

3199 $user さんから

3200 グループ $1

3201 に加入依頼がありました。

3202

3203 承認する:

3204 $urlbase?joingrpadmit+yes+$jss

3205

3206 白紙に戻す:

3207 $urlbase?joingrpadmit+no+$jss

3208 EOF

3209 echo "管理者に加入依頼を出しました。

3210 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3211 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3212 }

3213 joingrp() {

3214 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3215 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3216 if isgrpowner "$user" "$1"; then

3217 isowner="yes"

3218 elif [ -n "$5" ]; then

3219 isowner="yes"

3220 else

3221 isowner=""

3222 fi

3223 ## err jg:isgrpowner: isowner="$isowner"

3224 if [ -n "$isowner" ]; then

3225 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3226 elif [ x"$2" != x"$user" ]; then # if user is not login user

3227 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3228 return

3229 elif [ x"$3" = x"no" ]; then

3230 : # Do not pursue those who leave

3231 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3232 : # Member can change own email address for the joining moderated group

3233 else # adding user is $user itself

3234 case `getgroupattr $1 regmode` in

3235 moderated)

3236 joingrprequest "$@" # Request only

3237 return

3238 ;;

3239 *)

3240 ;;

3241 esac

3242 fi

3243 qgname=`sqlquote "$1"`

3244 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3245 cond="where gname=$qgname and user='$2'"

3246 if [ x"$3" = x"yes" ]; then

3247 query "replace into grp_mem values($qgname, '$2');"

3248 # Notify joingrp to admin

3249 action="に加入しました。"

3250 if [ -n "$4" ]; then

3251 if msg=`emaildomaincheck "$4"`; then

3252 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3253 'string', '$4', NULL);"

3254 else

3255 echo $msg

3256 fi

3257 else

3258 query "delete from grp_mem_s $cond and key='email';"

3259 fi

3260 if [ -n "$5" ]; then # as ADMIN

3261 # Coming here means newly created group

3262 sql="select case\

3263 when (select count(*) from grp_mem where gname=$qgname)=1\

3264 then (select user from grp_mem\

3265 where gname=$qgname and user='$user')\

3266 else '' end; "

3267 err NewGrpChk: $sql

3268 if [ -n "`query \"$sql\"`" ]; then

3269 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3270 query "replace into grp_adm values($qgname, '$user');"

3271 fi

3272 fi

3273 else

3274 query "begin;

3275 delete from grp_mem $cond;

3276 delete from grp_mem_s $cond;

3277 delete from grp_mem_m $cond;

3278 delete from grp_adm $cond;

3279 delete from grp_adm_s $cond;

3280 delete from grp_adm_m $cond;

3281 end;"

3282 action="から脱退しました。"

3283 fi

3284 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3285 $url?grp+$grid

3286 $user (`gecos $user`)さんが

3287 グループ $1

3288 $action

3289 EOF

3290 }

3291 grp_add_team() (

3292 # $1=grp-rowid $2=team $3...=user-rowid(s)

3293 grp=`getgroupbyid $1`

3294 team=$2; shift; shift

3295 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3296 { echo "BEGIN;"

3297 for user; do

3298 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3299 '$grp',\

3300 (SELECT name FROM user WHERE rowid=$user),\

3301 'team', 'string', '$team');"

3302 done

3303 echo "END;"

3304 } | query

3305 )

3306 grp_rm_team() (

3307 # $1=grp-rowid $2=team $3...=user-rowid(s)

3308 grid=$1

3309 qgrp=$(sqlquote "`getgroupbyid $grid`")

3310 team=$2; shift; shift

3311 [ -z "$grid" -o -z "$team" ] && return

3312 { echo "BEGIN;"

3313 for user; do

3314 echo "DELETE FROM grp_mem_m\

3315 WHERE gname=$qgrp \

3316 AND user=(SELECT name FROM user WHERE rowid=$user)\

3317 AND key='team' AND val='$team';"

3318 done

3319 cat<<-EOF

3320 DELETE FROM blog_s

3321 WHERE rowid=(

3322 SELECT rowid

3323 FROM blog_s a

3324 WHERE key='team'

3325 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3326 AND NOT EXISTS (SELECT * FROM grp_mem_m

3327 WHERE key='team' AND val=a.val -- a.val=team

3328 AND gname = (SELECT val FROM blog_s b

3329 WHERE a.id=b.id AND key='owner')

3330 ));

3331 EOF

3332

3333 echo "END;"

3334 } | query

3335 )

3336 grp_reg_adm() {

3337 # $1=grp-rowid $2...=user-rowid

3338 grid=$1

3339 grp=`getgroupbyid "$1"`

3340 if [ -z "$grp" ]; then

3341 echo "無効なグループIDです" | html p; return

3342 fi

3343 if ! isgrpowner "$user" "$grp"; then

3344 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3345 fi

3346 shift

3347 for urid; do

3348 newadm=`query "select name from user where rowid=$urid;"`

3349 if [ -z "$newadm" ]; then

3350 echo "指定ユーザIDがおかしいようです。" | html p; return

3351 fi

3352 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3353 err ismember $newadm $grp

3354 if ismember $newadm "$grp"; then

3355 # OK, go ahead

3356 getgname="(select gname from grp where rowid=$grid)"

3357 query "replace into grp_adm values($getgname, '$newadm');"

3358 # confirm insertion

3359 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3360 if [ -n "`query \"$sql;\"`" ]; then

3361 echo "追加完了: $newadm" | html p

3362 else

3363 echo "追加失敗($1 $urid)" | html p

3364 fi

3365 fi

3366 showgroup $grid

3367 done

3368 }

3369 dt_rowhack() {

3370 # From: <TR>

3371 # ....

3372 # <TD>rowclass=foo</TD>

3373 # </TR>

3374 # To: <TR class="foo">....<TD>foo</TD></TR>

3375 sed -e '

3376 /^<TR>/ {

3377 :loop

3378 s/\n//

3379 N

3380 /<\/TR>/ {

3381 s/\n//

3382 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3383 n

3384 }

3385 $q

3386 b loop

3387 }'

3388 }

3389 dumptable() {

3390 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3391 # textのフィールドだけ全てダンプにしたほうがいいか

3392 # $DT_VIEW sets link

3393 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3394 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3395 VIEW=${DT_VIEW-replyblog}

3396 if [ -n "$VIEW" ]; then

3397 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3398 fi

3399 sqlfile=$tmpd/dump.sql

3400 : > $sqlfile # ensure to be empty

3401 printf '.mode html\n.header 1\n' > $sqlfile

3402 # $DT_CHLD=ChildTable:BindColumn

3403 if [ -n "$DT_CHLD" ]; then

3404 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3405 cat<<-EOF >> $sqlfile

3406 -- presql

3407 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3408 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3409 EOF

3410 # Speed up counting of new articles

3411 cat<<-EOF >> $sqlfile

3412 -- presql2

3413 DROP TABLE IF EXISTS _counts;

3414 CREATE TEMPORARY TABLE _counts AS

3415 SELECT $_i, count($_i) cnt

3416 FROM $_t GROUP BY $_i;

3417 /* Prepare NEW count table */

3418 CREATE TEMPORARY TABLE _target AS

3419 SELECT b.rowid trowid, b.id

3420 FROM "$2" b JOIN "$2_s" s

3421 ON b.id=s.id AND s.key='owner'

3422 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3423

3424 DROP TABLE IF EXISTS _children;

3425 CREATE TEMPORARY TABLE _children AS

3426 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3427 FROM (SELECT t.trowid, t.id $_i, a.id

3428 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3429 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3430

3431 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3432 DROP TABLE IF EXISTS _news;

3433 DROP VIEW IF EXISTS _news;

3434

3435 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3436 -- INSERT INTO _news

3437 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3438 CREATE TEMPORARY VIEW _news AS

3439 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3440 FROM (SELECT DISTINCT id FROM _target)

3441 a LEFT JOIN

3442 (SELECT $_i, count(ctime) newcnt

3443 FROM _children x

3444 WHERE ctime > coalesce((SELECT time from myacclog

3445 WHERE tblrowid=x.trowid),

3446 '1970-01-01')

3447 GROUP BY $_i) b

3448 ON a.id=b.$_i;

3449 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3450 EOF

3451 # REMOVE next line until 2019/5/1

3452 cntall="(select count($_i) from $_t where $_i=a.id)"

3453 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3454 # REMOVE next assignment until 2019/5/1

3455 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3456 and id in (select id from $_t where $_i=a.id) \

3457 and val > coalesce((select time from myacclog where \

3458 tblrowid=a.rowid),\

3459 '1970-01-01'))"

3460 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3461 cnt="$cntnew as '新着', $cntall as '総数',"

3462 dt_class=" td2r td3r dumpblogs"

3463 fi

3464 # Construct join expression

3465 eav="" scols=""

3466 pk=`gettblpkey $2`

3467 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3468 substr=${substr:-%s}

3469 for col in ${3:-`gettbl_s_cols $2`}; do

3470 valvar=val

3471 case $col in

3472 gecos) scols="$scols${scols:+, }${col#}"

3473 continue ;; # built-in column name

3474 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3475 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3476 case "$as" in

3477 *:*=*) cnd=${as#*:}

3478 h=${cnd%%=*} v=${cnd#*=}

3479 h=`sqlquotestr "$h"`

3480 v=`sqlquotestr "$v"`

3481 valvar="CASE val WHEN $h THEN $v END"

3482 as=${as%%:*} ;;

3483 esac

3484 ;;

3485 *) as=${col} ;;

3486 esac

3487 ss=`printf "$substr" "$valvar"`

3488 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3489 scols="$scols${scols:+, }b.$as"

3490 done

3491 #case author when '$user' then a.rowid else '---' end as ID,

3492 if [ -n "$DT_SQL" ]; then

3493 echo "$DT_SQL"

3494 else

3495 cat<<-EOF

3496 SELECT a.rowid as LINK, $cnt $scols

3497 FROM $2 a LEFT JOIN

3498 (SELECT $pk,$eav,

3499 max(CASE key

3500 WHEN 'owner'

3501 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3502 as gecos

3503 FROM ${2}_s c GROUP BY $pk)

3504 b ON a.$pk=b.$pk $4;

3505 EOF

3506 fi >> $sqlfile

3507 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3508 sqlog<<-EOF

3509 *** SQL-file: $sqlfile ***

3510 `cat $sqlfile`

3511 EOF

3512 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3513 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3514 cat $sqlfile >> tmp/sql

3515 # query ".read $sqlfile" > $tmpd/foo

3516 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3517 cp $tmpd/foo tmp/

3518 err "----- `gdate +%FT%T.%3N` ------------555555"

3519 ## $ddd LINE exists at the end of this function

3520 fi

3521 printf '.mode list\n.header 0\n' >> $sqlfile

3522 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3523 <div>

3524 <div class="dumptable">

3525 <table class="b$dt_class">

3526 `query ".read $sqlfile"`

3527 </table>

3528 </div>

3529 </div>

3530 EOF

3531 ### `query ".read $sqlfile"`

3532 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3533

3534 ## REMOVE THIS!

3535 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3536 }

3537

3538 par2table() (

3539 # copy current parameters of par into destination table

3540 # $1=definition-file

3541 # Using $user and $session

3542 # Return value:

3543 # 0: Stored successfully

3544 # 1: Insufficient fillings

3545 # 2: No permission to modify the record

3546 # 3: Invalid rowid

3547 # 4: SUCCESS to delete

3548 # 5: Stop deletion for lack of confirm check

3549 # 6: Password length too short

3550 # 7: Password mismatch

3551 # 8: Old password incorrect

3552 rowid=`getpar rowid`

3553 if [ ! -e $1 ]; then

3554 echo "テーブル定義ファイルが見付かりません" | html p

3555 exit 1

3556 fi

3557 tbl=${1%.def}

3558 tbl=${tbl##*/}

3559 if [ -n "$rowid" ]; then # Modify existing entry

3560 if [ x"$tbl" = x"user" ]; then

3561 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3562 elif [ x"$tbl" = x"grp" ]; then

3563 sql="select gname from $tbl where rowid=$rowid;"

3564 ##err p2t:grp:q $sql

3565 isgrpowner "$user" "`query $sql`" && rowowner=$user

3566 elif [ x"$tbl" = x"blog" ]; then

3567 # Check if owner in blog_s

3568 blogowner=`getvalbyid blog owner "$rowid"`

3569 if isgrpowner "$user" "$blogowner"; then

3570 rowowner=$user

3571 else

3572 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3573 fi

3574 else

3575 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3576 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3577 fi

3578 ### err rowowner=$rowowner

3579 if [ x"$user" != x"$rowowner" ]; then

3580 echo "他人のレコードはいじれないの" | html p

3581 return 2

3582 elif [ -z "$rowowner" ]; then

3583 echo "指定したレコードはないみたい" | html p

3584 return 3

3585 fi

3586 rm=`getpar rm` cfm=`getpar confirm`

3587 # Editing existent entry

3588 if [ x"$rm" = x"yes" ]; then

3589 if [ x"$rm$cfm" = x"yesyes" ]; then

3590 query "delete from $tbl where rowid=$rowid;"

3591 return 4

3592 else

3593 echo "消去確認のチェックがないので消さなかったの..." | html p

3594 return 5

3595 fi

3596 fi

3597 fi

3598

3599 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3600 if [ -n "$rowid" ]; then

3601 # Update of existing record

3602 for col in `gettblcols $tbl`; do

3603 val=`getparquote $col`

3604 [ -z "$val" ] && continue

3605 ## err query "update $tbl set $col=$val where rowid=$rowid"

3606 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3607 sql="update $tbl set $col=$val where rowid=$rowid;"

3608 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3609 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3610 ## User name cannot be changed with interface provided with this

3611 ## script. But we offer the trigger to change owner user

3612 ## of blog_s table.

3613 #err "select quote($col) from $tbl where rowid=$rowid;"

3614 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3615 cat<<-EOF | query

3616 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3617 -- should use EXCLUSIVE transaction outside of this.

3618 SAVEPOINT par2table;

3619 $sql

3620 update blog_s set val=$val

3621 where key='owner' and val=$old;

3622 RELEASE SAVEPOINT par2table;

3623 EOF

3624 ## XX: DIRTY Hack Ends here

3625 ## We should keep blog's owner as a single column which has

3626 ## foreign key constraint with primary key of grp/user.

3627 else

3628 query "$sql"

3629 fi

3630 done

3631 # Then, set up $pval for further insertion of tbl_s and tbl_m

3632 for col in `gettblpkey $tbl`; do

3633 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3634 pval="$pval${pval:+, }\"$val\""

3635 done

3636 else

3637 # New entry

3638 # Generate values() for primary keys

3639 for col in `gettblpkey $tbl`; do

3640 # Genuine primary keys for _m and _s

3641 val=`getvalquote $tbl $col`

3642 [ -z "$val" ] && continue

3643 pval="$pval${pval:+, }$val"

3644 done

3645 ##err pval=$pval

3646 for col in `gettblfkey $tbl`; do

3647 # args for values() to insertion into master table

3648 val=`getvalquote $tbl $col`

3649 [ -z "$val" ] && continue

3650 formaster=$formaster"${formaster:+, }$val"

3651 done

3652 formaster="$pval${formaster:+, }$formaster"

3653 ## err formaster=$formaster

3654 if [ -z "$formaster" ]; then

3655 echo "項目を全て埋めてください" | html pre

3656 return 1

3657 fi

3658 ## err "replace into $tbl values($formaster);"

3659 query "replace into $tbl values($formaster);"

3660 ## Insertion to master table, done

3661 fi

3662

3663 transaction=$tmpd/sqlfile.sql; touch $transaction

3664 for kt in s m; do

3665 tb2=${tbl}_$kt

3666 for col in `gettbl_${kt}_cols $tbl`; do

3667 ptype=`getpartype $col "limit 1"`

3668

3669 # First, check update of existing entries in _m

3670 if [ $kt = m ]; then

3671 # sessID|address.1.22|string|Somewhere-x.y.z

3672 sql=""

3673 ##err dots from query "select var from par where var like '$col.%';"

3674 for v in `query "select var from par where var like '$col.%';"`; do

3675 # v=address.1.22

3676 st_rowid=${v##*.}

3677 origcol=${v%%.*} # original column derived from

3678 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3679 ##case `getpartype $v` in

3680 ## err CASE `gettbl_coltype $tbl/$origcol` in

3681 ## err edit flag = `getpar action.$v`

3682 case `getpar action.$v` in

3683 rm)

3684 if [ x`getpar confirm.$v` = x"yes" ]; then

3685 newsql="delete from $tb2"

3686 else

3687 echo "削除確認未チェック" | html p

3688 fi ;;

3689 edit)

3690 case `gettbl_coltype $tbl/$origcol` in

3691 image|document|binary)

3692 file=$tmpd/`getparfilename $v`

3693 ## err type=file=$file

3694 [ -z "$file" ] && continue

3695 bn=`sqlquotestr "${file##*/}"`

3696 bin="X'"$(hexize "$file")"'"

3697 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3698 type=\"file:$ct\"

3699 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3700 cachedir=`getcachedir "$tbl/$rowid"`

3701 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3702 rm -rf $cachedir

3703 ;;

3704 *)

3705 newsql="update $tb2 set val=(select val from par where var \

3706 like '$col.%.$st_rowid')"

3707 ;;

3708 esac

3709 ;;

3710 *) # maybe "keep", do not modify value

3711 continue

3712 ;;

3713 esac

3714 # err newsql=$newsql

3715 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3716 done

3717

3718 if [ x"$bin" = x"NULL" ]; then

3719 ## err repl:normal sql=`echo $sql`

3720 if [ -n "$transaction" ]; then

3721 cat<<-EOF >> $transaction

3722 $sql

3723 DELETE FROM $tb2 WHERE type='string' AND val='';

3724 EOF

3725 else

3726 query "$sql

3727 delete from $tb2 where type='string' and val='';"

3728 ## err repl:normal done

3729 fi

3730 else

3731 # Binary update line is TOO LONG to pipelining

3732 sqlfile="$tmpd/sqlf.$$"

3733 if [ -n "$transaction" ]; then

3734 echo "$sql" >> $transaction

3735 else

3736 echo "$sql" > $sqlfile

3737 query ".read $sqlfile"

3738 fi

3739 fi

3740 # Rest of kt==m: set multiple mode

3741 nr=`getparcount $col`

3742 else

3743 nr=1 # for kt==s, number of records is 1

3744 fi

3745

3746 i=0

3747 while [ $i -lt $nr ]; do

3748 limit="limit 1 offset $i"

3749 i=$((i+1)) # increase beforehand against continue

3750 val=`getvalquote $tbl $col "$limit"`

3751 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3752 ## err $col=$val

3753 bin=NULL

3754 ## err partype$col=`getpartype $col "$limit"`

3755 case $ptype in

3756 file) file=$tmpd/`getparfilename $col "$limit"`

3757 ## err parfile-$col=$file

3758 [ -z "$file" ] && continue

3759 bin="X'"$(hexize "$file")"'"

3760 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3761 type=\"file:$ct\" ;;

3762 "*"*) continue ;; # foreign table

3763 *) type=\"string\" ;;

3764 esac

3765 case `gettbl_coltype $tbl/$col` in

3766 password) # special care for password

3767 # name={password,pswd1,pswd2}

3768 p1=`getpar pswd1 "$limit"`

3769 if [ -z "$p1" ]; then

3770 continue # SKIP password setting, if p1 is empty

3771 else

3772 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3773 ## err pswd=$pswd

3774 if pwcheck "$pswd"; then

3775 if [ x"$p1" = x"$p2" ]; then

3776 case "$p1" in

3777 ??????????*) ;;

3778 *) echo "パスワードは10字以上にしてください。" | html p

3779 return 6;;

3780 esac

3781 val="\"`echo $p1|mypwhash`\""

3782 else

3783 echo "2つの新パスワード不一致" | html p

3784 return 7

3785 fi

3786 else

3787 echo "旧パスワード違います" | html p

3788 return 8

3789 fi

3790 fi

3791 ;;

3792 esac

3793 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3794 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3795 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3796 if [ x"$bin" = x"NULL" ]; then

3797 ## err Normal-query: `echo $sql`

3798 if [ -n "$transaction" ]; then

3799 echo "$sql" >> $transaction

3800 else

3801 query "$sql"

3802 fi

3803 else

3804 sqlfile="$tmpd/query.$$"

3805 ## err sqlfile=`ls -lF $sqlfile`

3806 if [ -n "$transaction" ]; then

3807 echo "$sql" >> $transaction

3808 else

3809 echo "$sql" > $sqlfile

3810 query ".read $sqlfile"

3811 fi

3812 fi

3813 ## err p2t done

3814 done

3815 done

3816 done

3817 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3818 -- We cannot use transaction here, because groupupdate may use it.

3819 SAVEPOINT pa2table_insert;

3820 .read $transaction

3821 RELEASE SAVEPOINT pa2table_insert;

3822 EOF

3823 return $?

3824 ##err donee

3825 )

3826 genform() {

3827 # $1 = form definition file

3828 # $2, $3 (optional)= table name and ROWID

3829 # If $GF_VIEWONLY set and nonNull, output values without form

3830 # If $GF_ARGS set, use it as content-strings in the form

3831 # If $GF_OWNER set, use it as value of name="owner"

3832 # If $GF_STAGE set, use it as value of name="stage"

3833 forms="" hiddens="" rowid=$3

3834 if [ ! -e "$1" ]; then

3835 echo "そのようなデータベースはないようです($2)。" | html p

3836 return

3837 elif [ -n "$2" ]; then

3838 rec=`query "select * from $2 where rowid='$rowid';"`

3839 if [ -z "$rec" ]; then

3840 pk=`gettblpkey $2`

3841 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3842 rec=`query "select rowid from $2 where $pk='$rowid';"`

3843 rowid=$rec

3844 rec=$3

3845 fi

3846 if [ -z "$rec" ]; then

3847 echo "そんなレコードはないみたいね..." | html p

3848 return

3849 fi

3850 fi

3851 if [ -z "$GF_VIEWONLY" ]; then

3852 rm='<input id="rm" name="rm" type="checkbox"

3853 value="yes"><label for="rm">このエントリの削除</label>

3854 <span>ほんとうに消しますよ(確認)!

3855 <input name="confirm" type=checkbox value="yes">はい</span>'

3856 fi

3857 # Image Cache dir

3858 ## err genform: getcache=$2/$rowid

3859 td=`getcachedir "$2/$rowid"`

3860 while IFS=: read prompt name keytype type args; do

3861 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3862 sp="${args:+ }"

3863 form="" val=""

3864 if [ -n "$rowid" ]; then

3865 # err genform2a: Seeking for "$2.$name, type=$type"

3866 rawval=`getvalbyid $2 $name $rowid $td`

3867 val=`echo "$rawval"|htmlescape`

3868 ## err genform3a: getvalbyid $2 $name $rowid $td

3869 ## err genform3b: val="[$val]" type="$type"

3870 fi

3871 if [ -n "$GF_VIEWONLY" ]; then

3872 is_hidden "$2" "$name" && continue

3873 fi

3874 case "$type" in

3875 text*)

3876 cgiform=cgi_multi_$type

3877 if [ -s $td/$name.count -a -n "$val" ]; then

3878 form=`$cgiform $name $td`

3879 val=$(echo "$val"|

3880 while read fn; do

3881 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3882 </td></tr>$nl"

3883 done)

3884 val="<table>$nl$val$nl</table>"

3885 else

3886 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3887 form=`cgi_$type $name "$rawval" "$args"`

3888 fi

3889 ;;

3890 [Rr][Aa][Dd][Ii][Oo])

3891 fh="<label><input type=\"radio\" name=\"$name\""

3892 form="`echo $args|sed -e \

3893 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3894 ;;

3895 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3896 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3897 ;;

3898 [Ss][Ee][Ll][Ee][Cc][Tt])

3899 fh="<select name=\"$name\">$nl"

3900 form=$(for l in $args; do

3901 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3902 done)

3903 if [ -n "$val" ]; then

3904 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3905 fi

3906 form="$fh$form</select>"

3907 ;;

3908 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3909 if [ -s $td/$name.count ]; then

3910 form=`cgi_multi_file $name $td "$args"`

3911 if [ -n "$val" ]; then

3912 hrfb="$myname?showattc+$2_m"

3913 val=$(echo "$rawval" \

3914 | while read fn; do

3915 data=`percenthex "$td/$fn"`

3916 #ct=`cat $td/$fn.content-type`

3917 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3918 ri=`cat "$td/$fn.rowid"`

3919 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3920 #imgsrc="<img src=\"data:$ct,$data\">"

3921 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3922 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3923 done)

3924 fi

3925 else

3926 form="<input type=\"file\" name=\"$name\" $args>"

3927 if [ -n "$val" ]; then

3928 imgs=$(echo "$rawval"\

3929 |while read fn;do

3930 data=`percenthex "$td/$fn"`

3931 echo "<img src=\"data:image/png,$data\">$fn<br>"

3932 done)

3933 form=$form"<br>$imgs"

3934 val=$imgs # 2015-06-15

3935 else

3936 form="<input type=\"file\" name=\"$name\" $args>"

3937 fi

3938 fi

3939 ;;

3940 [Hh][Ii][Dd][Dd][Ee][Nn])

3941 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3942 args="value=\"$GF_STAGE\""

3943 fi

3944 form="<input type=\"hidden\" name=\"$name\" $args>"

3945 prompt='' # Remove prompt

3946 ;;

3947 [Aa][Uu][Tt][Hh][Oo][Rr])

3948 [ -n "$GF_VIEWONLY" ] && continue

3949 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3950 prompt="" ;;

3951 [Oo][Ww][Nn][Ee][Rr])

3952 [ -n "$GF_VIEWONLY" ] && continue

3953 val=${GF_OWNER:-$val}

3954 val=${val:-$user}

3955 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3956 prompt="" ;;

3957 [Uu][Ss][Ee][Rr])

3958 # XXX: is null $user ok?

3959 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3960 [ -n "$GF_VIEWONLY" ] && continue

3961 form="$user"

3962 ;;

3963 [Pp]assword)

3964 [ -n "$GF_VIEWONLY" ] && continue

3965 form="`cgi_passwd`"

3966 val=""

3967 ;;

3968 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3969 [ -n "$GF_VIEWONLY" ] && continue

3970 if [ -z "$rowid" ]; then

3971 val=`genserial`

3972 fi

3973 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3974 prompt="" ;;

3975 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3976 prompt=""

3977 ;;

3978 parent|path|blog*)

3979 prompt=""

3980 ;;

3981 "*"*)

3982 tail=$tail"``"

3983 continue ;;

3984 esac

3985 if [ -n "$prompt" ]; then

3986 if [ -n "${GF_VIEWONLY}" ]; then

3987 form=$val

3988 else

3989 :

3990 fi

3991 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3992 else

3993 hiddens=$hiddens$nl"$form"

3994 fi

3995 done < $1

3996 # enctype="multipart/form-data"

3997 cat<<EOF

3998 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3999 ${rowid:+$rm}

4000 <table class="b $2">

4001 $forms

4002 </table>$hiddens

4003 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4004 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4005 EOF

4006 if [ -z $GF_VIEWONLY ]; then

4007 cat<<EOF

4008 <input type="submit" name="sub" value="OK">

4009 <input type="reset" name="res" value="Reset">

4010 EOF

4011 fi

4012 cat<<EOF

4013 $GF_ARGS</form>

4014 $tail

4015 EOF

4016 }

4017 edittable() {

4018 # $1=form-def $2=table $3 rowid

4019 genform "$@"

4020 }

4021 viewtable() {

4022 GF_VIEWONLY=1 genform "$@"

4023 }

4024 showattc() {

4025 # $1=table_m $2=rowid &optional $3=RawFlag

4026 ## err \$1=$1 \$2=$2 \$3=$3

4027 if ! isfilereadable $user $1 $2; then

4028 contenttype; echo

4029 echo "このファイルは管理者のみしか見られません" | html p

4030 putfooter; exit

4031 fi

4032 idir=`umask 002; mktempd` || exit 1

4033 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4034 bin=$idir/$myname-$$.bin

4035 sql="select quote(bin) from $1 where rowid='$2';"

4036 ## err showattc: sql: $sql

4037 sq $db "$sql" | unhexize > $bin

4038 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4039 type=${tv%//*} fn=${tv#*//}

4040 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4041 ct=${type#file:}

4042 case $ct in # all text/* changed to text/plain

4043 text/*)

4044 charset=`nkf -g $bin|cut -d' ' -f1`

4045 case $charset in

4046 ASCII*) charset="" ;;

4047 esac

4048 if [ -z "$3" ]; then

4049 ct="text/html${charset:+; charset=$charset}"

4050 link="?showattc+$1+$2+raw"

4051 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4052 | sed 's,^,<span></span>,' \

4053 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4054 -D_LINK_="$link" \

4055 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4056 exit $?

4057 fi

4058 ct="text/plain${charset:+; charset=$charset}"

4059 ;;

4060 esac

4061 contenttype "$ct"

4062 echo "Content-Disposition: filename=\"$fn\""

4063 echo "Content-Length: " `cat $bin | wc -c`; echo

4064 #echo "Content-Type: " ${type#file:}; echo

4065 cat $bin

4066 }

4067 #

4068 # Some default stupid handler on CGI values

4069 #

4070 default_storedb() {

4071 # ARG: $1=table-def-file

4072 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4073 tbl=`basename $1`

4074 tbl=${tbl%.def}

4075 cols="`grep :text $1|cut -d: -f2`"

4076 col=`echo "$cols"|head -1`

4077 vcol=`getpar $col`

4078 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4079 if [ -n "$vcol" ]; then

4080 par2table $1

4081 else

4082 return 2 # No insertion occurred

4083 fi

4084 }

4085

4086 default_view() { # $1=def-file

4087 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4088 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4089 default_storedb "$@"

4090 query "select rowid from $tbl order by rowid desc;" \

4091 | while read rowid; do

4092 viewtable $1 $tbl $rowid

4093 done | _m4 -D_TITLE_="$tbl" \

4094 -D_FORM_="`genform $1`" \

4095 -D_DUMPTABLE_="syscmd(cat)" \

4096 $layout/html.m4.html $layout/form+dump.m4.html

4097 }

4098 default_viewtext() { # $1=def-file

4099 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4100 default_storedb "$@"

4101 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4102 | _m4 -D_TITLE_="$tbl" \

4103 -D_FORM_="`genform $1`" \

4104 -D_DUMPTABLE_="syscmd(cat)" \

4105 $layout/html.m4.html $layout/form+dump.m4.html

4106 }

4107 default_smail() {

4108 default_storedb "$@"

4109 if [ $? -eq 2 ]; then

4110 _m4 -D_TITLE_="入力" \

4111 -D_FORM_="`genform $1`" \

4112 -D_DUMPTABLE_="" \

4113 $layout/html.m4.html $layout/form+dump.m4.html

4114 return

4115 fi

4116 cond=""

4117 for pk in `gettblpkey $tbl`; do

4118 pv=$(sqlquote "$(getpar $pk)")

4119 cond="$cond${cond:+ and }$pk=$pv"

4120 done

4121 sql="select rowid from $tbl where $cond;"

4122 rowid=`query "$sql"`

4123 ## err smail1 - "$sql" "-> rowid=$rowid"

4124

4125 while IFS=: read prompt name keytype type args; do # Read from $1

4126 val=`getpar $name`

4127 if [ -n "$val" ]; then

4128 text="$text

4129 $prompt

4130 $name=$val

4131 ---------------------------------------------------------"

4132 fi

4133 case "$type" in

4134 image|document|file)

4135 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4136 fns=$(echo "$fn"|while read fn; do

4137 err mv $tmpd/$fn.orig $tmpd/$fn

4138 mv $tmpd/$fn.orig $tmpd/$fn

4139 rm $tmpd/$fn.rowid # Remove cache flag

4140 ## err "`ls $tmpd/$fn`"

4141 echo $fn

4142 done)

4143 files="$files $fns"

4144 ;;

4145 esac

4146 done < $1

4147 ## err FILES=$files "`ls -lF $tmpd`"

4148 subj="from ${REMOTE_ADDR}"

4149 (echo "$url"

4150 echo "への書き込みがありました。"

4151 echo "------"

4152 echo "$text"

4153 ) | (cd $tmpd &&

4154 err LS="`ls -lF`" &&

4155 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4156 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4157 echo "以下の内容で送信しました。" | html p

4158 viewtable $1 $tbl \

4159 `query "select rowid from $tbl order by rowid desc limit 1;"`

4160 echo "戻る" | html a "href=\"?\""

4161 }