s4: c45ab714d68e s4-funcs.sh

s4

view s4-funcs.sh @ 694:c45ab714d68e

To: header for email-sender contains all recipients

author	HIROSE Yuuji <yuuji@gentei.org>
date	Tue, 19 May 2020 11:49:11 +0900
parents	5f3e94a9b7b3
children	5cf0ba49aeab

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 -D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \

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

1330 showlogin # and EXIT

1331 fi

1332 }

1333

1334 # Do instant jobs here

1335 dbsetup

1336 trap cleanup INT HUP EXIT TERM PIPE

1337 # trap cleanup INT HUP

1338

1339 err() {

1340 echo "$@" 1>&3

1341 }

1342

1343 cgiinit() {

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

1345 tmpf=tmp/stream

1346 tmpd=`tmpd=$tmpdir mktempd`

1347 tmpfiles=$tmpfiles" $tmpd"

1348 addsession $session

1349 getcookie

1350 case "$REQUEST_METHOD" in

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

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

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

1354 # cat > $tmpf # too much?

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

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

1357 s="`cat tmp/stream`"

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

1359 ;;

1360 esac

1361 case "$CONTENT_TYPE" in

1362 *boundary*)

1363 bndry=${CONTENT_TYPE#*boundary=}

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

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

1366 do

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

1368 #echo u=$us

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

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

1371 # err k=$k v=$v

1372 case "$k" in

1373 *:filename)

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

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

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

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

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

1379 :

1380 fi

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

1382 case "$mimetype" in

1383 [Ii]mage/x-xcf)

1384 bzip2 "$tmpd/$v"

1385 v=${v}.bz2

1386 ;;

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

1388 [Ii]mage/*)

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

1390 ;;

1391 esac

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

1393 then

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

1395 continue

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

1397 replpar text string \

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

1399 continue

1400 fi

1401 ;;

1402 *)

1403 type='string'

1404 ;;

1405 esac

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

1407 setpar "$k" "$type" "$v"

1408 done

1409 ;;

1410 *)

1411 setviastring "$s"

1412 ;;

1413 esac

1414 }

1415 email4group() {

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

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

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

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

1420 sql="WITH

1421 grpemails AS (

1422 SELECT gname, user, val email

1423 FROM grp_mem NATURAL JOIN grp_mem_s

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

1425 useremails AS (

1426 SELECT user.name, val email

1427 FROM user

1428 LEFT JOIN user_m

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

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

1431 FROM useremails u LEFT JOIN grpemails g

1432 ON u.name=g.user

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

1434 query "$sql"

1435 }

1436 email4groupbyuid() {

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

1438 g=$1; shift

1439 uids=`echo "$@"`

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

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

1442 email4group "$g" `query "$sql"`

1443 }

1444 myemail4group() {

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

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

1447 }

1448 collectmembersbyid() {

1449 # Collect user names of group specified by grid

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

1451 query "SELECT user FROM grp_mem \

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

1453 }

1454 collectmembersbyid() {

1455 # Collect user names of group name

1456 qgrp=`sqlquote "$1"`

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

1458 }

1459 collectgecosesbyid() {

1460 # Collect user gecoses of group

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

1462 query<<-EOF

1463 SELECT gecos

1464 FROM gecoses

1465 WHERE name IN (SELECT user FROM grp_mem

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

1467 EOF

1468 }

1469 collectemail() (

1470 # Collect email addresses for group $1

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

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

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

1474 for e; do

1475 if isuser "$e"; then

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

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

1478 else

1479 qgrp=`sqlquote "$e"`

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

1481 gmem="grp_mem"

1482 else

1483 tm=`sqlquote "$TEAM"`

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

1485 fi

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

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

1488 $gmem g left join grp_mem_s s

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

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

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

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

1493 query "$sql"

1494 fi

1495 done

1496 )

1497 sendinvitation() (

1498 # $1=email

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

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

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

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

1503 gecos=`gecos`

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

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

1506 _m4 -D_URL_="$urlbase" \

1507 -D_USER_="$name" \

1508 -D_EMAIL_="$1" \

1509 -D_REGIST_="$regist" \

1510 -D_ADMIN_="$admin" \

1511 $msgdir/mail-invite.m4 \

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

1513 return 0

1514 )

1515 emaildomaincheck() {

1516 case "$1" in

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

1518 *@*)

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

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

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

1522 return 2

1523 fi

1524 return 0

1525 ;;

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

1527 esac

1528 }

1529 invite() {

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

1531 case "$email" in

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

1533 *@*)

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

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

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

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

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

1539 elif sendinvitation $email; then

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

1541 else # Cannot be reached here

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

1543 fi ;;

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

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

1546 esac

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

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

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

1550 fi

1551 if [ -f $invite_policy ]; then

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

1553 else

1554 pol="$invite_policy"

1555 fi

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

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

1558 -D_INVITE_POLICY_="$pol" \

1559 $layout/html.m4.html $layout/invite.m4.html

1560 }

1561 regist() {

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

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

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

1565 echo "bye bye" | html p

1566 reutrn

1567 fi

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

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

1570 cat<<EOF

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

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

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

1574 EOF

1575 return

1576 fi

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

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

1579 # Fake login password to wasureta

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

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

1582 wasureta $email

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

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

1585 addsession $1 # for removal after 1 minute

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

1587 $layout/login.m4.html

1588 return

1589 }

1590 group_safename() {

1591 # Convert $1 to safe group name

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

1593 }

1594 groupupdate() {

1595 gname=`getpar gname`

1596 qgname=`sqlquote "$gname"`

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

1598 # See ALSO same job in showgroup()

1599 newgname=`group_safename "$gname"`

1600 err newgname=$newgname

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

1602 err NewGNAME: gname=$newgname

1603 gname=$newgname

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

1605 replpar gname string "$gname"

1606 fi

1607 # Name confliction check

1608 parow=`getpar rowid`

1609 ## err parow=$parow

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

1611 query "BEGIN EXCLUSIVE;"

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

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

1614 if [ $count -gt 0 ]; then

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

1616 query "END;"

1617 return

1618 fi

1619 par2table $formdir/grp.def

1620 query "END TRANSACTION;"

1621 # Remove orphan

1622 : <<EOF

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

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

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

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

1627 EOF

1628 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1632 sql="delete from blog where id in

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

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

1635 query "$sql";

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

1637 return # and return

1638 fi

1639 fi

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

1641 fi

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

1643 grid=$(query $sql)

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

1645 grp $grid

1646 }

1647 groupclone() {

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

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

1650 case "$1" in

1651 */noteam)

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

1653 esac

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

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

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

1657 return

1658 fi

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

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

1661 return

1662 fi

1663 i=0

1664 while true; do

1665 copy="-copy$i"

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

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

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

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

1670 i=$((i++))

1671 continue

1672 fi

1673 break

1674 done

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

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

1677 query<<-EOF

1678 BEGIN;

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

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

1681 SELECT $newqname, key, val

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

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

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

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

1686 -- Copy members and their configuration --

1687 REPLACE INTO grp_mem SELECT $newqname, user

1688 FROM grp_mem WHERE gname=$qgrp;

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

1690 FROM grp_mem_s WHERE gname=$qgrp;

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

1692 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1693 -- Copy administrators --

1694 REPLACE INTO grp_adm SELECT $newqname, user

1695 FROM grp_adm WHERE gname=$qgrp;

1696 COMMIT;

1697 EOF

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

1699 STOPCLONEMSG=1 groupconf "$newrowid"

1700 }

1701 groupman() {

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

1703

1704 GF_STAGE="grpconf"

1705 GF_STAGE=groupupdate

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

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

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

1709 -D_DUMPTABLE_="syscmd(cat)" \

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

1711 }

1712 userconf() {

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

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

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

1716 }

1717 groupconf() {

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

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

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

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

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

1723

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

1725 grp=`getgroupbyid $rowid`

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

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

1728 return

1729 fi

1730

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

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

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

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

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

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

1737 <div>

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

1739 <table>

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

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

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

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

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

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

1746 </table>

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

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

1749 </div>

1750 EOF

1751 fi

1752 }

1753 mems() {

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

1755 kwd=`getpar kwd`

1756 listmember $kwd

1757 }

1758 grps() {

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

1760 kwd=`getpar kwd`

1761 listgroup $kwd \

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

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

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

1765 $layout/form+dump.m4.html

1766 }

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

1768 gpg=`getpar grp`

1769 grid=${1:-$gpg}

1770 grp=`getgroupbyid "$grid"`

1771 ## . ./s4-blog.sh

1772 jg=`getpar joingrp`

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

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

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

1776 fi

1777 htmlheader=$layout/html.m4.html

1778 showgroup "$grid"

1779 }

1780 sql4interestblogs() {

1781 cat<<EOF

1782 CREATE TEMPORARY VIEW interestblogs AS

1783 SELECT blog.rowid rid, id, author

1784 FROM blog

1785 NATURAL JOIN

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

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

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

1789 WHEN (SELECT user FROM grp_mem

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

1791 THEN 0

1792 ELSE 1

1793 END;

1794 EOF

1795 }

1796 listnewblogsql() { # $1=user

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

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

1799 newdays=${newdays:-14}

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

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

1802 WHERE user='$user'

1803 AND tblrowid IN

1804 ($blogreadflagrowid,

1805 $blogcutoffflagrowid)),

1806 $basetime -- "0"

1807 );"`

1808 cat<<EOF

1809 `sql4interestblogs`

1810 WITH article_ctime as (

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

1812 FROM article join article_s s using(id)

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

1814 GROUP BY id

1815 ), blog_title_owner as (

1816 SELECT blg.rid brid, id,

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

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

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

1820 ), blogall as (

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

1822 ), news as (

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

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

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

1826 FROM blogall bl

1827 LEFT JOIN

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

1829 ON bl.brid=al.tblrowid

1830 WHERE atime < bl.ctime

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

1832 LIMIT 10

1833 ) SELECT brid LINK, "新着",

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

1835 ctime, title,

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

1837 FROM news;

1838 EOF

1839 }

1840

1841 search_form() {

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

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

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

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

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

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

1848 @week → 最近一週間

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

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

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

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

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

1854 auth=""

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

1856 case "$1" in

1857 author=*)

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

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

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

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

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

1863 ;;

1864 grp=*)

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

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

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

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

1869 ;;

1870 esac

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

1872 cat<<-EOF

1873 <div class="right">

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

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

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

1877

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

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

1880

1881 </form>

1882 </div>

1883 EOF

1884 }

1885

1886 imgsrc_cache() (

1887 # $1 = directory for cache'ing

1888 # $2 = table (user_m or grp_m)

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

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

1891 dir="$1" tbl="$2"

1892 keycond="$3"

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

1894 ORDER BY rowid DESC LIMIT 1"

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

1896 tmpf=$tmpd/imgsrc_cache.$$

1897 case "$4" in

1898 [Ss]) size=S ;;

1899 [Oo]) size=O ;;

1900 *) size=M ;;

1901 esac

1902 # ImageCache filename storing schema:

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

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

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

1906 valtype=`query "$sql0"`

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

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

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

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

1911 case "$filetype" in

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

1913 esac

1914 fi

1915 cacheimg_S=$dir/S_$filename

1916 cacheimg_M=$dir/M_$filename

1917 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1925 else

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

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

1928 fifo2=$fifo.2

1929 mkfifo $fifo $fifo2

1930 fmt=${filename##*.}

1931 ## [[ NOTE ]]

1932 ## a. convert oldimage newimage

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

1934 ## b is much smaller than a

1935 cat $tmpf | unhexize \

1936 | tee $fifo \

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

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

1939 | tee $fifo2 \

1940 | convert - "$cacheimg_M" &

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

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

1943 | convert - "$cacheimg_S" &

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

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

1946 echo '">'

1947 echo "$sum" > $sumfile

1948 fi

1949 ## Now preparing cache image, done.

1950 ## Store this information to DB

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

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

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

1954 query <<-EOF

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

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

1957 EOF

1958 )

1959

1960 showhome() {

1961 # $1=userRowIdToShow

1962 err showhome \$1=$1

1963 case "$1" in

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

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

1966 esac

1967 ## err ShowHome: uname=$uname

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

1969 gecos=`gecos "$uname"`

1970 ## err SH:gecos=$gecos

1971 GF_VIEWONLY=1

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

1973 search_form_args=""

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

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

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

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

1978 # Display folders

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

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

1981 and type like 'file:%';"

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

1983 nfile=`query "$sql"`

1984 # err nfile=$nfile

1985 if [ $nfile -gt 0 ]; then

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

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

1988 fi

1989 else

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

1991 GROUP BY user;"`

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

1993 search_form_args="author=$uname"

1994 fi

1995 . ./s4-blog.sh

1996

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

1998 search_form "$search_form_args" > $sf

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

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

2001 viewtable $formdir/user.def user $1

2002 echo "</div>"

2003 } > $pf

2004

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

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

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

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

2009 if true; then

2010 tbl=user_m

2011 enticond="name='$uname'"

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

2013 else

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

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

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

2017 echo '">'

2018 }

2019 fi > $imf

2020 fi

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

2022 val='$uname';"`

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

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

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

2026 listblog $uname > $bf

2027 ## unset ddd

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

2029

2030 hometail=$tmpd/tail.$$

2031 mkfifo $hometail

2032

2033 #Calling listgroupbytable, originally here

2034

2035 (

2036 # Display Most Recent Entry

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

2038 shortval=${shortval:-val}

2039

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

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

2042 ctime,

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

2044 (SELECT gecos FROM gecoses

2045 WHERE name=(SELECT val FROM blog_s

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

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

2048 FROM blog b

2049 JOIN

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

2051 FROM article a, article_s s

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

2053 GROUP BY blogid ORDER BY val DESC LIMIT 50

2054 ) m

2055 ON b.id=m.blogid;"

2056 # This should be as follows

2057 : <<EOF

2058 WITH arts AS(

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

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

2061 FROM article a NATURAL JOIN article_s s

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

2063 GROUP by s.id

2064 )

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

2066 FROM arts a0

2067 JOIN

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

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

2070 ORDER BY ctime DESC LIMIT 50;

2071 EOF

2072

2073 cat<<-EOF

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

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

2076 <div class="lcto">

2077 `DT_VIEW=replyblog dumptable html blog`

2078 </div>

2079 EOF

2080 unset DT_SQL

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

2082 # Display NEWS

2083 # 2016-06-26

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

2085 acclog blog $blogreadflagrowid

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

2087 fi

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

2089 sql=`listnewblogsql "$user"`

2090 # echo "$sql" > tmp/listnew

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

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

2093 cont=$((cont-1))

2094 err newcount=$cont

2095 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2102 `cgi_submit '確定'`

2103 `cgi_hidden read yes`

2104 EOF

2105 echo "$new10 "

2106 echo "</div>"

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

2108 acclog blog $blogcutoffflagrowid # for speed

2109 fi

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

2111 : # DT_SQL=

2112 fi

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

2114 #

2115 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2121 -D_NBLOG_="$nblog" \

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

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

2124 $layout/html.m4.html $layout/home.m4.html

2125

2126 # Record access log

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

2128 }

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

2130 contenttype; echo

2131 ## err commission: "$@"

2132 gname=`getgroupbyid $1`

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

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

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

2136 grp_reg_adm "$@"

2137 else

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

2139 fi

2140 }

2141 listgroupbytable() {

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

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

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

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

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

2147 NGsql="select distinct tag from\

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

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

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

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

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

2153 for tag in `query "$sql"`

2154 do

2155 ## err ListGrp: tag=$tag

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

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

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

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

2160 as rowid,

2161 gname,

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

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

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

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

2166 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2171 echo '</table>'

2172 done

2173 echo '</div>'

2174 }

2175 iconhref() (

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

2177 data=`percenthex "$1"`

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

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

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

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

2182 )

2183 iconhref2() (

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

2185 src=$1

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

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

2188 )

2189 listentry() (

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

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

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

2193 cond='' hiddens=''

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

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

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

2197 offset=$((offset-1))

2198 fi

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

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

2201 limit=$listentlimit

2202 dir=`getcachedir "$1"`

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

2204 hrb="$myname?home"

2205 deficon=person-default.png

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

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

2208 gcs=gecos

2209 else # if group

2210 hrb="$myname?grp"

2211 deficon=group-default.png

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

2213 gcs=name

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

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

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

2217 ## err tagconv=$tagconv

2218 fi

2219 fi

2220 if [ ! -d $dir ]; then

2221 mkdir -p $dir

2222 fi

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

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

2225 fi

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

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

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

2229 fi

2230 tag=`getpar tag` tag2=`getpar tag2`

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

2232 tag="" tag2=""

2233 fi

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

2235 tag=${tag:-$tag2}

2236 qtag=`sqlquote "$tag"`

2237 cond2="tag=$qtag"

2238 fi

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

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

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

2242 fi

2243

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

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

2246 ##qgrp=`sqlquote $grp`

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

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

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

2250 quote(a.$nm) as qname,

2251 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

2258 end

2259 as name,

2260 b.tag,

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

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

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

2264 then 'ADMIN'

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

2266 then 'Member'

2267 when '$iamowner' = '' then ''

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

2269 CASE '$entity'

2270 WHEN 'グループ'

2271 THEN coalesce(

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

2273 'open')

2274 ||

2275 CASE WHEN '$user'

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

2277 THEN ' ismember'

2278 ELSE ''

2279 END

2280 ELSE 'user'

2281 END regmode

2282 from $tbl a left join

2283 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

2294 # Note that mtime is stored only in grp_s.

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

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

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

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

2299 # Show owner/member filter button

2300 METHOD=GET

2301 hiddens="$hiddens

2302 `cgi_hidden kwd \"$kwd\"`

2303 `cgi_hidden stage \"$stage\"`"

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

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

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

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

2308 for l in $args; do

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

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

2311 s=" selected"

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

2313 else

2314 s=""

2315 fi

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

2317 done

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

2319 cat<<-EOF

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

2321 </form>

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

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

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

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

2326 EOF

2327 # limit=3

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

2329 fi

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

2331 echo '<div>'

2332 METHOD=GET cgi_form $stage <<EOF

2333 $form

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

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

2336 $hiddens

2337 EOF

2338 echo '</div>'

2339 else

2340 echo $selectedtags | html p

2341 fi

2342 cat<<EOF

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

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

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

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

2347 </form>

2348 EOF

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

2350 nextbtn=$(

2351 cat<<EOF

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

2353 `cgi_submit 次の${limit}件`

2354 $hiddens

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

2356 EOF

2357 )

2358 fi

2359 if [ $offset -gt 0 ]; then

2360 prevbtn=$(

2361 cat<<EOF

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

2363 `cgi_submit 前の${limit}件`

2364 $hiddens

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

2366 EOF

2367 )

2368 fi

2369 pnbtn="$nextbtn$prevbtn"

2370 echo $pnbtn

2371

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

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

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

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

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

2377 # err newlnk=$lnk regmode=$regmode

2378 icondir=$dir/$id

2379 # Pick up only last icon

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

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

2382 | _m4 $tagconv

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

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

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

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

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

2388 else

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

2390 fi

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

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

2393 else

2394 cond="$nm=$qname"

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

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

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

2398 # err "img=[$img]"

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

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

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

2402 echo "</a>"

2403 else

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

2405 fi

2406 fi

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

2408 echo "</div>"

2409 done

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

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

2412 )

2413 listmember() {

2414 listentry user "$@"

2415 }

2416 listgroup() {

2417 listentry group "$@"

2418 }

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

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

2421 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

2423 }

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

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

2426 grid=`getpar grid`

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

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

2429 else

2430 grid=$1

2431 fi

2432 grp=`getgroupbyid $grid`

2433 qgrp=`sqlquote "$grp"`

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

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

2436 if isgroup "$grp"; then

2437 tf=$tmpd/title.$$

2438 sf=$tmpd/search.$$

2439 bodyclass=`query "SELECT val FROM grp_s

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

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

2442 ismember="ismember"

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

2444 else

2445 ismember="" # bodyclass="group"

2446 fi

2447 bodyclass="$bodyclass grouphome"

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

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

2450

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

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

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

2454 -D_BODYCLASS_="$bodyclass" \

2455 -D_DUMPTABLE_="" \

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

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

2458 # $htmlheader is defined in grp()

2459 else # if $grp is removed at par2table

2460 listgroup

2461 fi

2462 }

2463 showgroupsub() {

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

2465 # Using $ismember

2466 rowid=$2

2467 grp=`getgroupbyid $2`

2468 qgrp=`sqlquote "$grp"`

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

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

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

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

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

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

2475 return

2476 fi

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

2478 enticond="gname=$qgrp"

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

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

2481 cat<<-EOF

2482 <p class="groupimg">

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

2484 EOF

2485 fi

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

2487 viewtable $1 grp $rowid

2488 echo "</div>"

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

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

2491 iamowner=$rowid

2492 colmd=" mode"

2493 fi

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

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

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

2497 # div.fold input[type="checkbox"]:checked ~ div {display: block;}

2498 cat<<EOF

2499 <form action="?send2mem" method="POST" enctype="multipart/form-data">

2500 <div class="fold clear">

2501 `cgi_checkbox send yes id="send"`<label

2502 for="send">グループ全員にメッセージ送信</label>

2503 <div>

2504 `cgi_textarea message "" "cols=60"`

2505 `cgi_submit 送信`

2506 `cgi_reset リセット`

2507 </div>

2508 `cgi_hidden grp $rowid`

2509 </div></form>

2510 EOF

2511 fi

2512 # 加入ボタン + 加入者リスト

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

2514 ismem='checked' state="(参加中)"

2515 else

2516 nomem='checked' state="(現在非加入)"

2517 fi

2518 # このグループでの加入アドレス

2519 eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \

2520 and key='email';"`

2521 ##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \

2522 ## and key='email';"

2523 ##err email=$eml

2524 cat <<EOF

2525 <div class="fold clear">

2526 `cgi_checkbox reg yes id="reg"`<label

2527 for="reg">自身の加入状態を操作する</label>$state

2528 <div>

2529 EOF

2530 cgi_form grp <<EOF

2531 <p>このグループに</p>

2532 <table class="b">

2533 <tr><th>メンバーとして</th><td>

2534 <label>`cgi_radio joingrp "yes" $ismem`参加</label> /

2535 <label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>

2536 <tr><th>参加する場合のメイルアドレス<br>

2537 <small>(メインのアドレスとは違うものにする場合に記入<br>

2538 同じでよい場合は空欄に)</small></th>

2539 <td>`cgi_text email $eml`</td></tr>

2540 </table>

2541 `cgi_hidden grp $rowid`

2542 EOF

2543 if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then

2544 echo "moderated (承認加入の)グループなので実際に参加できるのは

2545 グループ管理者が承認操作をした後になります。" | html p 'class="warn"'

2546 fi

2547 echo '</div></div>'

2548 echo '<h2>話題一覧</h2>'

2549 thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"

2550 cat<<-EOF

2551 <form class="summary" action="$myname" title="$thelp">

2552 `cgi_hidden owner "$grp"`

2553 `cgi_hidden kwd "@week"`

2554 `cgi_hidden stage searchart`

2555 `cgi_submit "一週間のまとめ"`

2556 </form>

2557 EOF

2558 cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"

2559 colstate="state:稼動状態:frozen=rowclass=凍結"

2560 DT_CHLD=article:blogid \

2561 DT_QOWNER="$qgrp" \

2562 DT_VIEW=replyblog dumptable html blog \

2563 "ctime title heading team notify:通知$colmd $colstate" "$cond"

2564

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

2566 c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"

2567 cm="?commission+$rowid"

2568 thumbxy=50x50 listmember "`getpar kwd`" "$c" "$rowid" \

2569 |sed -e "s|$<br>$(,not=$.*$)|\1|" # 間違って押しやすい

2570 # team list

2571 hexteams=`hexteams "$grp"`

2572 if [ -n "$hexteams" ]; then

2573 echo "チーム一覧" | html h2

2574 echo '<div class="dumptable"><table class="b">'

2575 sq $db -html -header<<-EOF

2576 SELECT val TEAM,

2577 group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')

2578 MEMBERS

2579 FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;

2580 EOF

2581 echo '</table></div>'

2582 fi

2583 }

2584 grp_getbodyclass() {

2585 # Get css class name for document.

2586 # `moderated' for moderated groups

2587 # `ismember' for groups where user belongs

2588 # $1=GroupName (w/o quote)

2589 # $user=userNameCurrentlyLogin

2590 ## err grp_getbodyclass: 1="$1"

2591 qgrp=`sqlquote "$1"`

2592 query<<-EOF

2593 SELECT coalesce(

2594 (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),

2595 'open')

2596 ||

2597 CASE WHEN '$user'

2598 IN (SELECT user FROM grp_mem WHERE gname=$qgrp)

2599 THEN ' ismember'

2600 ELSE ''

2601 END;

2602 EOF

2603 }

2604 grpaction() { # $1=group-rowid

2605 err GRP_ACTION:IN

2606 grid=${1:-`getpar grp`}

2607 grp=`getgroupbyid "$grid"`

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

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

2610 echo "無効な指定です。" | html p; return

2611 fi

2612 if ! ismember $user "$grp"; then

2613 echo "加入者のみに許可された操作です。" | html p; return

2614 fi

2615 echo "グループ $grp 個別選択操作" \

2616 | _m4 -D_TITLE_="syscmd(\`cat')" \

2617 -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \

2618 $layout/html.m4.html

2619

2620 isowner=""

2621 isgrpowner "$user" "$grp" && isowner="yes"

2622 usel=`getpar usel`

2623 if [ -n "$usel" ]; then

2624 uids=$(echo `echo $usel`|tr ' ' ',')

2625 ## err grpaction-1: grp=$grp, `echo $sql`

2626 text=`getpar text|tr -d '\r'`

2627

2628 rm=`getpar rm` cfm=`getpar confirm`

2629 ## err rm=$rm cfm=$cfm

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

2631 if [ "$isowner" ]; then

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

2633 # Eliminate

2634 cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"

2635 for tbl in grp_mem grp_mem_s grp_mem_m; do

2636 sql="delete from $tbl $cond;"

2637 # echo "sql=$sql"

2638 query "$sql"

2639 err rmGRPuser "$sql"

2640 done

2641 num=`query "select count(*) from user where rowid in ($uids);"`

2642 #err num=$num

2643 if [ 0$num -gt 0 ]; then

2644 sql="select coalesce(b.val,a.name) from user a left join \

2645 user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"

2646 # err `echo "$sql"`

2647 html pre<<EOF

2648 以下の${num}名のグループ $grp 登録を解除しました。

2649 `query "$sql"`

2650 EOF

2651 fi

2652 else

2653 echo "確認のチェックがないのでやめておきます。" | html p

2654 return

2655 fi

2656 else # not Group Owner

2657 echo "グループ管理者でないのでメンバー操作はできません。" | html p

2658 return

2659 fi

2660 cat<<EOF

2661

2662 EOF

2663 elif [ x"$rm" = x"send" ]; then # if sendmsg mode

2664 if [ -z "$text" ]; then # if msg is empty

2665 echo "なにかメッセージを..." | html p

2666 return 0

2667 fi

2668 gecos=`gecos $user`

2669 safegc=`echo "$gecos" | tr -d '<>@'`

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

2671 fromad=`myemail4group "$grp"`

2672 ###mail_from="$safegc <$fromad>"

2673 mail_from="$safegc <$user>" # TEST: 2020/5/13

2674 test -n `getpar sender` &&

2675 export SENDER=$user # TEST: 2020/5/15

2676 replyto=$fromad

2677

2678 ## Start parse of attachment files

2679 if [ -n "`getpar email`" ]; then

2680 ar=`getpar supprcpt`

2681 if [ -n "$ar" ]; then

2682 for a in $ar; do

2683 checkdomain "$a" && supprcpt="$supprcpt $a"

2684 done

2685 fi

2686 subj=`getpar subject`

2687 afiles=""

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

2689 do

2690 f=$tmpd/$fn

2691 if [ -s $f ]; then

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

2693 fi

2694 done

2695 else

2696 preface=$(cat <<-EOF

2697 $url

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

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

2700 ----------------------------------------------------------

2701 EOF

2702 )

2703 fi

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

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

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

2707 REPLYTO=$replyto

2708 MAIL_FROM=$mail_from

2709 export REPLYTO SMAIL_TO MAIL_FROM

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

2711 for r in $rcpts; do

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

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

2714 else

2715 # Show sender and rcpts address for guest

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

2717 fi

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

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

2720 else

2721 smail "$r" "$subj"

2722 fi <<EOF

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

2724 EOF

2725 done

2726 if [ $? = 0 ]; then

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

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

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

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

2731 html pre<<EOF

2732 `query "$sql"`

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

2734 EOF

2735 err SendDone: `echo $sql`

2736 fi

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

2738 grp_reg_adm $grid $usel

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

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

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

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

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

2744 team=newteam

2745 fi

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

2747 cat<<-EOF | html p

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

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

2750 EOF

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

2752 else

2753 grp_add_team $grid "$team" $usel

2754 fi

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

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

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

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

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

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

2761 grp_rm_team $grid "$rmteam" $usel

2762 else

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

2764 fi

2765 else

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

2767 fi

2768 fi

2769 fi

2770 # POST count summary

2771 from=`getpar from`; to=`getpar to`

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

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

2774 fromtonote="<p>POST集計: $from_input - $to_input</p>"

2775 # New entry

2776 sql="WITH mems AS (

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

2778 ON gm.user=g.name

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

2780 ), target_article AS (

2781 SELECT id FROM article_s

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

2783 ), posts AS (

2784 SELECT author, count(author) post

2785 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2786 WHERE blogid IN (SELECT id FROM blog_s

2787 WHERE key='owner'

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

2789 AND key='text'

2790 GROUP BY author

2791 ), teams AS (

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

2793 FROM grp_mem_m

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

2795 AND key='team'

2796 GROUP BY user

2797 ), user_post AS (

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

2799 FROM mems m LEFT JOIN posts

2800 ON m.name=posts.author

2801 GROUP by m.rowid

2802 )

2803 SELECT

2804 CASE

2805 WHEN (SELECT user FROM grp_adm

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

2807 AND user=up.name) IS NOT NULL

2808 then 'k'

2809 ELSE ''

2810 END || rowid || ',' || gecos NAME,

2811 post POST, team _TEAM_

2812 FROM user_post up LEFT JOIN teams t

2813 ON up.name=t.user

2814 ORDER BY gecos;"

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

2816 tf=$tmpd/title.$$

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

2818 cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label

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

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

2821 </p></div>"

2822 excmsg="`cgi_radio rm yes id=\"conf\"`<label

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

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

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

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

2827 </div>"

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

2829 allhexteams=$(hexteams "$grp")

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

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

2832 else

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

2834 fi

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

2836 rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label

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

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

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

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

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

2842 </div>

2843 "

2844 fi

2845 stf=$tmpd/selteam.$$

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

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

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

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

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

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

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

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

2854 cgi_form grpaction<<EOF \

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

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

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

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

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

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

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

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

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

2864 <div class="foldtabs">

2865 `cgi_radio rm addteam 'id="cmteam"'`<label

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

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

2868 `cgi_datalist_h teams $allhexteams`

2869 </div>

2870 ${rmteammsg}

2871 `cgi_radio rm send id="sendmsg"`<label

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

2873 <div>

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

2875 >email書式を使う</label>

2876 <div class="folded">

2877 <table>

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

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

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

2881 </td></tr>

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

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

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

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

2886 </table>

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

2888 </div>

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

2890 </div>

2891 </div>

2892 ${isowner:+$cmmsg$excmsg}

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

2894 </div>

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

2896 <table class="td2r thl">

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

2898 </table>

2899 `cgi_hidden grp $grid`

2900 `cgi_hidden myuid $myuid id="myuid"`

2901 EOF

2902 }

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

2904 # Create TEMPORARY VIEW

2905 query<<EOF

2906 CREATE TEMPORARY VIEW writeusers AS

2907 SELECT DISTINCT author FROM article

2908 WHERE id in (

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

2910 );

2911 CREATE TEMPORARY VIEW movablegroups AS

2912 SELECT g.rowid growid , g.gname

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

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

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

2916 WHERE (SELECT author FROM writeusers

2917 EXCEPT

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

2919 IS NULL;

2920 $2

2921 EOF

2922 }

2923 sql4readableblogs() {

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

2925 # Blog is readable when:

2926 # 1: blog owner is an user

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

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

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

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

2931 CREATE TEMPORARY VIEW readableblogs AS

2932 SELECT blog.rowid rid, id, author

2933 FROM blog

2934 NATURAL JOIN

2935 (SELECT id,

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

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

2938 FROM blog_s GROUP by id) bs

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

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

2941 WHEN (SELECT val FROM grp_s

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

2943 AND

2944 (SELECT user FROM grp_mem

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

2946 THEN 0

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

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

2949 ELSE 1

2950 END;

2951 EOF

2952 }

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

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

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

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

2957 return

2958 fi

2959 owner=`getvalbyid blog owner $rowid`

2960 title=`getvalbyid blog title $rowid`

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

2962 | _m4 -D_TITLE_="修正" \

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

2964 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2967 # Move to group

2968 if isuser "$owner"; then

2969 crview4article $rowid

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

2971 ## err N=$n

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

2973 ## err ROWID=$rowid

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

2975 cat<<-EOF

2976 <div class="fold">

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

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

2979 <div>

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

2981 移動先グループ:

2982 <select name="mv2grp">

2983 EOF

2984 query ".mode html"

2985 query<<-EOF |

2986 $sql

2987 .mode list

2988 EOF

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

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

2991 cat<<-EOF

2992 </select>

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

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

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

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

2997 `cgi_hidden blogrowid $rowid`

2998 `cgi_submit 移動`

2999 `cgi_reset Reset`

3000 </form>

3001 </div>

3002 </div>

3003 EOF

3004 fi

3005 # end of isuser "$owner"

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

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

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

3009 cat<<-EOF

3010 <div class="fold">

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

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

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

3014 `query "SELECT

3015 coalesce((SELECT val FROM blog_s

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

3017 AND key='team'),

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

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

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

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

3022 `cgi_hidden blogrowid $rowid`<br>

3023 `cgi_submit 移動`

3024 `cgi_reset Reset`

3025 </form></div></div>

3026 EOF

3027 fi

3028 }

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

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

3031 blogrowid=`getpar blogrowid`

3032 cfm=`getpar cfm`

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

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

3035 . ./s4-blog.sh

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

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

3038 return

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

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

3041 elif { mv2grp=`getpar mv2grp`

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

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

3044 crview4article $blogrowid

3045 ########## TRANSACTION BEGIN

3046 query "BEGIN;"

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

3048 ## err Nwriteuser=$n

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

3050 query<<-EOF

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

3052 WHERE key='owner'

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

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

3055 EOF

3056 fi

3057 query "END;"

3058 ########## TRANSACTION END

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

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

3061 # blog owner can move it to ANY team

3062 case "$mv2team" in

3063 'なし')

3064 cat<<-EOF

3065 DELETE FROM blog_s

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

3067 AND key='team';

3068 EOF

3069 ;;

3070 "") ;;

3071 *)cat<<-EOF

3072 BEGIN;

3073 REPLACE INTO blog_s(id, key, val)

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

3075 'team', '$mv2team');

3076 REPLACE INTO blog_s(id, key, val)

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

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

3079 END;

3080 EOF

3081 esac | query

3082 fi

3083 blog_reply $blogrowid

3084 echo yes | html p

3085 }

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

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

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

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

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

3091 return

3092 fi

3093 owner=`getvalbyid blog owner $blogrowid`

3094 title=`getvalbyid blog title $blogrowid`

3095 author=`getvalbyid article author $rowid`

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

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

3098 : EDIT OK

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

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

3101 return

3102 fi

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

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

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

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

3107 > $tmpout

3108 rm -f /tmp/editart.out

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

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

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

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

3113 -D_BLOGS_= -D_DUMPTABLE_= \

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

3115 }

3116 send2mem() {

3117 rowid=`getpar grp`

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

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

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

3121 return

3122 fi

3123 message=`getpar message`

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

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

3126 return

3127 fi

3128 grp=`getgroupbyid $rowid`

3129 members=`collectemail $grp`

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

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

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

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

3134 msg=$(cat<<-EOF

3135 $urlbase?grp+$rowid

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

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

3138

3139 $message

3140 EOF

3141 )

3142 # smail rcpt subj (file)

3143 for m in $members; do

3144 echo "$msg" |

3145 MAIL_FROM=$mailfrom \

3146 SENDER=$noreply \

3147 REPLYTO=$mailfrom \

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

3149 smail "$m" "$sj"

3150 done

3151 cat<<EOF

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

3153 <pre>

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

3155 </pre>

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

3157 EOF

3158 }

3159 joingrpadmit() {

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

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

3162 echo "bye bye" | html p; return

3163 fi

3164 t_usr=`session=$2 getpar user`

3165 t_grp=`session=$2 getpar group`

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

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

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

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

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

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

3172 return

3173 fi

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

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

3176 fi

3177 case $1 in

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

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

3180 *)

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

3182 return ;;

3183 esac

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

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

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

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

3188 に

3189 $t_usr

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

3191 しました。"

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

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

3194 echo "$body" | html p

3195 }

3196

3197 joingrprequest() {

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

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

3200 addsession $jss +${memoplimitdays}days

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

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

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

3204 $url

3205 $user さんから

3206 グループ $1

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

3208

3209 承認する:

3210 $urlbase?joingrpadmit+yes+$jss

3211

3212 白紙に戻す:

3213 $urlbase?joingrpadmit+no+$jss

3214 EOF

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

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

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

3218 }

3219 joingrp() {

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

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

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

3223 isowner="yes"

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

3225 isowner="yes"

3226 else

3227 isowner=""

3228 fi

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

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

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

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

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

3234 return

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

3236 : # Do not pursue those who leave

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

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

3239 else # adding user is $user itself

3240 case `getgroupattr $1 regmode` in

3241 moderated)

3242 joingrprequest "$@" # Request only

3243 return

3244 ;;

3245 *)

3246 ;;

3247 esac

3248 fi

3249 qgname=`sqlquote "$1"`

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

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

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

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

3254 # Notify joingrp to admin

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

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

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

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

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

3260 else

3261 echo $msg

3262 fi

3263 else

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

3265 fi

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

3267 # Coming here means newly created group

3268 sql="select case\

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

3270 then (select user from grp_mem\

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

3272 else '' end; "

3273 err NewGrpChk: $sql

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

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

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

3277 fi

3278 fi

3279 else

3280 query "begin;

3281 delete from grp_mem $cond;

3282 delete from grp_mem_s $cond;

3283 delete from grp_mem_m $cond;

3284 delete from grp_adm $cond;

3285 delete from grp_adm_s $cond;

3286 delete from grp_adm_m $cond;

3287 end;"

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

3289 fi

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

3291 $url?grp+$grid

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

3293 グループ $1

3294 $action

3295 EOF

3296 }

3297 grp_add_team() (

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

3299 grp=`getgroupbyid $1`

3300 team=$2; shift; shift

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

3302 { echo "BEGIN;"

3303 for user; do

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

3305 '$grp',\

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

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

3308 done

3309 echo "END;"

3310 } | query

3311 )

3312 grp_rm_team() (

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

3314 grid=$1

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

3316 team=$2; shift; shift

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

3318 { echo "BEGIN;"

3319 for user; do

3320 echo "DELETE FROM grp_mem_m\

3321 WHERE gname=$qgrp \

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

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

3324 done

3325 cat<<-EOF

3326 DELETE FROM blog_s

3327 WHERE rowid=(

3328 SELECT rowid

3329 FROM blog_s a

3330 WHERE key='team'

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

3332 AND NOT EXISTS (SELECT * FROM grp_mem_m

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

3334 AND gname = (SELECT val FROM blog_s b

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

3336 ));

3337 EOF

3338

3339 echo "END;"

3340 } | query

3341 )

3342 grp_reg_adm() {

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

3344 grid=$1

3345 grp=`getgroupbyid "$1"`

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

3347 echo "無効なグループIDです" | html p; return

3348 fi

3349 if ! isgrpowner "$user" "$grp"; then

3350 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3351 fi

3352 shift

3353 for urid; do

3354 newadm=`query "select name from user where rowid=$urid;"`

3355 if [ -z "$newadm" ]; then

3356 echo "指定ユーザIDがおかしいようです。" | html p; return

3357 fi

3358 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3359 err ismember $newadm $grp

3360 if ismember $newadm "$grp"; then

3361 # OK, go ahead

3362 getgname="(select gname from grp where rowid=$grid)"

3363 query "replace into grp_adm values($getgname, '$newadm');"

3364 # confirm insertion

3365 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3366 if [ -n "`query \"$sql;\"`" ]; then

3367 echo "追加完了: $newadm" | html p

3368 else

3369 echo "追加失敗($1 $urid)" | html p

3370 fi

3371 fi

3372 showgroup $grid

3373 done

3374 }

3375 dt_rowhack() {

3376 # From: <TR>

3377 # ....

3378 # <TD>rowclass=foo</TD>

3379 # </TR>

3380 # To: <TR class="foo">....<TD>foo</TD></TR>

3381 sed -e '

3382 /^<TR>/ {

3383 :loop

3384 s/\n//

3385 N

3386 /<\/TR>/ {

3387 s/\n//

3388 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3389 n

3390 }

3391 $q

3392 b loop

3393 }'

3394 }

3395 dumptable() {

3396 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3397 # textのフィールドだけ全てダンプにしたほうがいいか

3398 # $DT_VIEW sets link

3399 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3400 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3401 VIEW=${DT_VIEW-replyblog}

3402 if [ -n "$VIEW" ]; then

3403 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3404 fi

3405 sqlfile=$tmpd/dump.sql

3406 : > $sqlfile # ensure to be empty

3407 printf '.mode html\n.header 1\n' > $sqlfile

3408 # $DT_CHLD=ChildTable:BindColumn

3409 if [ -n "$DT_CHLD" ]; then

3410 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3411 cat<<-EOF >> $sqlfile

3412 -- presql

3413 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3414 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3415 EOF

3416 # Speed up counting of new articles

3417 cat<<-EOF >> $sqlfile

3418 -- presql2

3419 DROP TABLE IF EXISTS _counts;

3420 CREATE TEMPORARY TABLE _counts AS

3421 SELECT $_i, count($_i) cnt

3422 FROM $_t GROUP BY $_i;

3423 /* Prepare NEW count table */

3424 CREATE TEMPORARY TABLE _target AS

3425 SELECT b.rowid trowid, b.id

3426 FROM "$2" b JOIN "$2_s" s

3427 ON b.id=s.id AND s.key='owner'

3428 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3429

3430 DROP TABLE IF EXISTS _children;

3431 CREATE TEMPORARY TABLE _children AS

3432 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3433 FROM (SELECT t.trowid, t.id $_i, a.id

3434 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3435 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3436

3437 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3438 DROP TABLE IF EXISTS _news;

3439 DROP VIEW IF EXISTS _news;

3440

3441 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3442 -- INSERT INTO _news

3443 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3444 CREATE TEMPORARY VIEW _news AS

3445 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3446 FROM (SELECT DISTINCT id FROM _target)

3447 a LEFT JOIN

3448 (SELECT $_i, count(ctime) newcnt

3449 FROM _children x

3450 WHERE ctime > coalesce((SELECT time from myacclog

3451 WHERE tblrowid=x.trowid),

3452 '1970-01-01')

3453 GROUP BY $_i) b

3454 ON a.id=b.$_i;

3455 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3456 EOF

3457 # REMOVE next line until 2019/5/1

3458 cntall="(select count($_i) from $_t where $_i=a.id)"

3459 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3460 # REMOVE next assignment until 2019/5/1

3461 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3462 and id in (select id from $_t where $_i=a.id) \

3463 and val > coalesce((select time from myacclog where \

3464 tblrowid=a.rowid),\

3465 '1970-01-01'))"

3466 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3467 cnt="$cntnew as '新着', $cntall as '総数',"

3468 dt_class=" td2r td3r dumpblogs"

3469 fi

3470 # Construct join expression

3471 eav="" scols=""

3472 pk=`gettblpkey $2`

3473 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3474 substr=${substr:-%s}

3475 for col in ${3:-`gettbl_s_cols $2`}; do

3476 valvar=val

3477 case $col in

3478 gecos) scols="$scols${scols:+, }${col#}"

3479 continue ;; # built-in column name

3480 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3481 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3482 case "$as" in

3483 *:*=*) cnd=${as#*:}

3484 h=${cnd%%=*} v=${cnd#*=}

3485 h=`sqlquotestr "$h"`

3486 v=`sqlquotestr "$v"`

3487 valvar="CASE val WHEN $h THEN $v END"

3488 as=${as%%:*} ;;

3489 esac

3490 ;;

3491 *) as=${col} ;;

3492 esac

3493 ss=`printf "$substr" "$valvar"`

3494 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3495 scols="$scols${scols:+, }b.$as"

3496 done

3497 #case author when '$user' then a.rowid else '---' end as ID,

3498 if [ -n "$DT_SQL" ]; then

3499 echo "$DT_SQL"

3500 else

3501 cat<<-EOF

3502 SELECT a.rowid as LINK, $cnt $scols

3503 FROM $2 a LEFT JOIN

3504 (SELECT $pk,$eav,

3505 max(CASE key

3506 WHEN 'owner'

3507 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3508 as gecos

3509 FROM ${2}_s c GROUP BY $pk)

3510 b ON a.$pk=b.$pk $4;

3511 EOF

3512 fi >> $sqlfile

3513 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3514 sqlog<<-EOF

3515 *** SQL-file: $sqlfile ***

3516 `cat $sqlfile`

3517 EOF

3518 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3519 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3520 cat $sqlfile >> tmp/sql

3521 # query ".read $sqlfile" > $tmpd/foo

3522 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3523 cp $tmpd/foo tmp/

3524 err "----- `gdate +%FT%T.%3N` ------------555555"

3525 ## $ddd LINE exists at the end of this function

3526 fi

3527 printf '.mode list\n.header 0\n' >> $sqlfile

3528 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3529 <div>

3530 <div class="dumptable">

3531 <table class="b$dt_class">

3532 `query ".read $sqlfile"`

3533 </table>

3534 </div>

3535 </div>

3536 EOF

3537 ### `query ".read $sqlfile"`

3538 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3539

3540 ## REMOVE THIS!

3541 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3542 }

3543

3544 par2table() (

3545 # copy current parameters of par into destination table

3546 # $1=definition-file

3547 # Using $user and $session

3548 # Return value:

3549 # 0: Stored successfully

3550 # 1: Insufficient fillings

3551 # 2: No permission to modify the record

3552 # 3: Invalid rowid

3553 # 4: SUCCESS to delete

3554 # 5: Stop deletion for lack of confirm check

3555 # 6: Password length too short

3556 # 7: Password mismatch

3557 # 8: Old password incorrect

3558 rowid=`getpar rowid`

3559 if [ ! -e $1 ]; then

3560 echo "テーブル定義ファイルが見付かりません" | html p

3561 exit 1

3562 fi

3563 tbl=${1%.def}

3564 tbl=${tbl##*/}

3565 if [ -n "$rowid" ]; then # Modify existing entry

3566 if [ x"$tbl" = x"user" ]; then

3567 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3568 elif [ x"$tbl" = x"grp" ]; then

3569 sql="select gname from $tbl where rowid=$rowid;"

3570 ##err p2t:grp:q $sql

3571 isgrpowner "$user" "`query $sql`" && rowowner=$user

3572 elif [ x"$tbl" = x"blog" ]; then

3573 # Check if owner in blog_s

3574 blogowner=`getvalbyid blog owner "$rowid"`

3575 if isgrpowner "$user" "$blogowner"; then

3576 rowowner=$user

3577 else

3578 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3579 fi

3580 else

3581 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3582 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3583 fi

3584 ### err rowowner=$rowowner

3585 if [ x"$user" != x"$rowowner" ]; then

3586 echo "他人のレコードはいじれないの" | html p

3587 return 2

3588 elif [ -z "$rowowner" ]; then

3589 echo "指定したレコードはないみたい" | html p

3590 return 3

3591 fi

3592 rm=`getpar rm` cfm=`getpar confirm`

3593 # Editing existent entry

3594 if [ x"$rm" = x"yes" ]; then

3595 if [ x"$rm$cfm" = x"yesyes" ]; then

3596 query "delete from $tbl where rowid=$rowid;"

3597 return 4

3598 else

3599 echo "消去確認のチェックがないので消さなかったの..." | html p

3600 return 5

3601 fi

3602 fi

3603 fi

3604

3605 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3606 if [ -n "$rowid" ]; then

3607 # Update of existing record

3608 for col in `gettblcols $tbl`; do

3609 val=`getparquote $col`

3610 [ -z "$val" ] && continue

3611 ## err query "update $tbl set $col=$val where rowid=$rowid"

3612 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3613 sql="update $tbl set $col=$val where rowid=$rowid;"

3614 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3615 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3616 ## User name cannot be changed with interface provided with this

3617 ## script. But we offer the trigger to change owner user

3618 ## of blog_s table.

3619 #err "select quote($col) from $tbl where rowid=$rowid;"

3620 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3621 cat<<-EOF | query

3622 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3623 -- should use EXCLUSIVE transaction outside of this.

3624 SAVEPOINT par2table;

3625 $sql

3626 update blog_s set val=$val

3627 where key='owner' and val=$old;

3628 RELEASE SAVEPOINT par2table;

3629 EOF

3630 ## XX: DIRTY Hack Ends here

3631 ## We should keep blog's owner as a single column which has

3632 ## foreign key constraint with primary key of grp/user.

3633 else

3634 query "$sql"

3635 fi

3636 done

3637 # Then, set up $pval for further insertion of tbl_s and tbl_m

3638 for col in `gettblpkey $tbl`; do

3639 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3640 pval="$pval${pval:+, }\"$val\""

3641 done

3642 else

3643 # New entry

3644 # Generate values() for primary keys

3645 for col in `gettblpkey $tbl`; do

3646 # Genuine primary keys for _m and _s

3647 val=`getvalquote $tbl $col`

3648 [ -z "$val" ] && continue

3649 pval="$pval${pval:+, }$val"

3650 done

3651 ##err pval=$pval

3652 for col in `gettblfkey $tbl`; do

3653 # args for values() to insertion into master table

3654 val=`getvalquote $tbl $col`

3655 [ -z "$val" ] && continue

3656 formaster=$formaster"${formaster:+, }$val"

3657 done

3658 formaster="$pval${formaster:+, }$formaster"

3659 ## err formaster=$formaster

3660 if [ -z "$formaster" ]; then

3661 echo "項目を全て埋めてください" | html pre

3662 return 1

3663 fi

3664 ## err "replace into $tbl values($formaster);"

3665 query "replace into $tbl values($formaster);"

3666 ## Insertion to master table, done

3667 fi

3668

3669 transaction=$tmpd/sqlfile.sql; touch $transaction

3670 for kt in s m; do

3671 tb2=${tbl}_$kt

3672 for col in `gettbl_${kt}_cols $tbl`; do

3673 ptype=`getpartype $col "limit 1"`

3674

3675 # First, check update of existing entries in _m

3676 if [ $kt = m ]; then

3677 # sessID|address.1.22|string|Somewhere-x.y.z

3678 sql=""

3679 ##err dots from query "select var from par where var like '$col.%';"

3680 for v in `query "select var from par where var like '$col.%';"`; do

3681 # v=address.1.22

3682 st_rowid=${v##*.}

3683 origcol=${v%%.*} # original column derived from

3684 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3685 ##case `getpartype $v` in

3686 ## err CASE `gettbl_coltype $tbl/$origcol` in

3687 ## err edit flag = `getpar action.$v`

3688 case `getpar action.$v` in

3689 rm)

3690 if [ x`getpar confirm.$v` = x"yes" ]; then

3691 newsql="delete from $tb2"

3692 else

3693 echo "削除確認未チェック" | html p

3694 fi ;;

3695 edit)

3696 case `gettbl_coltype $tbl/$origcol` in

3697 image|document|binary)

3698 file=$tmpd/`getparfilename $v`

3699 ## err type=file=$file

3700 [ -z "$file" ] && continue

3701 bn=`sqlquotestr "${file##*/}"`

3702 bin="X'"$(hexize "$file")"'"

3703 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3704 type=\"file:$ct\"

3705 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3706 cachedir=`getcachedir "$tbl/$rowid"`

3707 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3708 rm -rf $cachedir

3709 ;;

3710 *)

3711 newsql="update $tb2 set val=(select val from par where var \

3712 like '$col.%.$st_rowid')"

3713 ;;

3714 esac

3715 ;;

3716 *) # maybe "keep", do not modify value

3717 continue

3718 ;;

3719 esac

3720 # err newsql=$newsql

3721 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3722 done

3723

3724 if [ x"$bin" = x"NULL" ]; then

3725 ## err repl:normal sql=`echo $sql`

3726 if [ -n "$transaction" ]; then

3727 cat<<-EOF >> $transaction

3728 $sql

3729 DELETE FROM $tb2 WHERE type='string' AND val='';

3730 EOF

3731 else

3732 query "$sql

3733 delete from $tb2 where type='string' and val='';"

3734 ## err repl:normal done

3735 fi

3736 else

3737 # Binary update line is TOO LONG to pipelining

3738 sqlfile="$tmpd/sqlf.$$"

3739 if [ -n "$transaction" ]; then

3740 echo "$sql" >> $transaction

3741 else

3742 echo "$sql" > $sqlfile

3743 query ".read $sqlfile"

3744 fi

3745 fi

3746 # Rest of kt==m: set multiple mode

3747 nr=`getparcount $col`

3748 else

3749 nr=1 # for kt==s, number of records is 1

3750 fi

3751

3752 i=0

3753 while [ $i -lt $nr ]; do

3754 limit="limit 1 offset $i"

3755 i=$((i+1)) # increase beforehand against continue

3756 val=`getvalquote $tbl $col "$limit"`

3757 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3758 ## err $col=$val

3759 bin=NULL

3760 ## err partype$col=`getpartype $col "$limit"`

3761 case $ptype in

3762 file) file=$tmpd/`getparfilename $col "$limit"`

3763 ## err parfile-$col=$file

3764 [ -z "$file" ] && continue

3765 bin="X'"$(hexize "$file")"'"

3766 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3767 type=\"file:$ct\" ;;

3768 "*"*) continue ;; # foreign table

3769 *) type=\"string\" ;;

3770 esac

3771 case `gettbl_coltype $tbl/$col` in

3772 password) # special care for password

3773 # name={password,pswd1,pswd2}

3774 p1=`getpar pswd1 "$limit"`

3775 if [ -z "$p1" ]; then

3776 continue # SKIP password setting, if p1 is empty

3777 else

3778 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3779 ## err pswd=$pswd

3780 if pwcheck "$pswd"; then

3781 if [ x"$p1" = x"$p2" ]; then

3782 case "$p1" in

3783 ??????????*) ;;

3784 *) echo "パスワードは10字以上にしてください。" | html p

3785 return 6;;

3786 esac

3787 val="\"`echo $p1|mypwhash`\""

3788 else

3789 echo "2つの新パスワード不一致" | html p

3790 return 7

3791 fi

3792 else

3793 echo "旧パスワード違います" | html p

3794 return 8

3795 fi

3796 fi

3797 ;;

3798 esac

3799 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3800 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3801 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3802 if [ x"$bin" = x"NULL" ]; then

3803 ## err Normal-query: `echo $sql`

3804 if [ -n "$transaction" ]; then

3805 echo "$sql" >> $transaction

3806 else

3807 query "$sql"

3808 fi

3809 else

3810 sqlfile="$tmpd/query.$$"

3811 ## err sqlfile=`ls -lF $sqlfile`

3812 if [ -n "$transaction" ]; then

3813 echo "$sql" >> $transaction

3814 else

3815 echo "$sql" > $sqlfile

3816 query ".read $sqlfile"

3817 fi

3818 fi

3819 ## err p2t done

3820 done

3821 done

3822 done

3823 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3824 -- We cannot use transaction here, because groupupdate may use it.

3825 SAVEPOINT pa2table_insert;

3826 .read $transaction

3827 RELEASE SAVEPOINT pa2table_insert;

3828 EOF

3829 return $?

3830 ##err donee

3831 )

3832 genform() {

3833 # $1 = form definition file

3834 # $2, $3 (optional)= table name and ROWID

3835 # If $GF_VIEWONLY set and nonNull, output values without form

3836 # If $GF_ARGS set, use it as content-strings in the form

3837 # If $GF_OWNER set, use it as value of name="owner"

3838 # If $GF_STAGE set, use it as value of name="stage"

3839 forms="" hiddens="" rowid=$3

3840 if [ ! -e "$1" ]; then

3841 echo "そのようなデータベースはないようです($2)。" | html p

3842 return

3843 elif [ -n "$2" ]; then

3844 rec=`query "select * from $2 where rowid='$rowid';"`

3845 if [ -z "$rec" ]; then

3846 pk=`gettblpkey $2`

3847 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3848 rec=`query "select rowid from $2 where $pk='$rowid';"`

3849 rowid=$rec

3850 rec=$3

3851 fi

3852 if [ -z "$rec" ]; then

3853 echo "そんなレコードはないみたいね..." | html p

3854 return

3855 fi

3856 fi

3857 if [ -z "$GF_VIEWONLY" ]; then

3858 rm='<input id="rm" name="rm" type="checkbox"

3859 value="yes"><label for="rm">このエントリの削除</label>

3860 <span>ほんとうに消しますよ(確認)!

3861 <input name="confirm" type=checkbox value="yes">はい</span>'

3862 fi

3863 # Image Cache dir

3864 ## err genform: getcache=$2/$rowid

3865 td=`getcachedir "$2/$rowid"`

3866 while IFS=: read prompt name keytype type args; do

3867 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3868 sp="${args:+ }"

3869 form="" val=""

3870 if [ -n "$rowid" ]; then

3871 # err genform2a: Seeking for "$2.$name, type=$type"

3872 rawval=`getvalbyid $2 $name $rowid $td`

3873 val=`echo "$rawval"|htmlescape`

3874 ## err genform3a: getvalbyid $2 $name $rowid $td

3875 ## err genform3b: val="[$val]" type="$type"

3876 fi

3877 if [ -n "$GF_VIEWONLY" ]; then

3878 is_hidden "$2" "$name" && continue

3879 fi

3880 case "$type" in

3881 text*)

3882 cgiform=cgi_multi_$type

3883 if [ -s $td/$name.count -a -n "$val" ]; then

3884 form=`$cgiform $name $td`

3885 val=$(echo "$val"|

3886 while read fn; do

3887 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3888 </td></tr>$nl"

3889 done)

3890 val="<table>$nl$val$nl</table>"

3891 else

3892 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3893 form=`cgi_$type $name "$rawval" "$args"`

3894 fi

3895 ;;

3896 [Rr][Aa][Dd][Ii][Oo])

3897 fh="<label><input type=\"radio\" name=\"$name\""

3898 form="`echo $args|sed -e \

3899 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3900 ;;

3901 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3902 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3903 ;;

3904 [Ss][Ee][Ll][Ee][Cc][Tt])

3905 fh="<select name=\"$name\">$nl"

3906 form=$(for l in $args; do

3907 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3908 done)

3909 if [ -n "$val" ]; then

3910 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3911 fi

3912 form="$fh$form</select>"

3913 ;;

3914 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3915 if [ -s $td/$name.count ]; then

3916 form=`cgi_multi_file $name $td "$args"`

3917 if [ -n "$val" ]; then

3918 hrfb="$myname?showattc+$2_m"

3919 val=$(echo "$rawval" \

3920 | while read fn; do

3921 data=`percenthex "$td/$fn"`

3922 #ct=`cat $td/$fn.content-type`

3923 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3924 ri=`cat "$td/$fn.rowid"`

3925 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3926 #imgsrc="<img src=\"data:$ct,$data\">"

3927 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3928 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3929 done)

3930 fi

3931 else

3932 form="<input type=\"file\" name=\"$name\" $args>"

3933 if [ -n "$val" ]; then

3934 imgs=$(echo "$rawval"\

3935 |while read fn;do

3936 data=`percenthex "$td/$fn"`

3937 echo "<img src=\"data:image/png,$data\">$fn<br>"

3938 done)

3939 form=$form"<br>$imgs"

3940 val=$imgs # 2015-06-15

3941 else

3942 form="<input type=\"file\" name=\"$name\" $args>"

3943 fi

3944 fi

3945 ;;

3946 [Hh][Ii][Dd][Dd][Ee][Nn])

3947 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3948 args="value=\"$GF_STAGE\""

3949 fi

3950 form="<input type=\"hidden\" name=\"$name\" $args>"

3951 prompt='' # Remove prompt

3952 ;;

3953 [Aa][Uu][Tt][Hh][Oo][Rr])

3954 [ -n "$GF_VIEWONLY" ] && continue

3955 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3956 prompt="" ;;

3957 [Oo][Ww][Nn][Ee][Rr])

3958 [ -n "$GF_VIEWONLY" ] && continue

3959 val=${GF_OWNER:-$val}

3960 val=${val:-$user}

3961 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3962 prompt="" ;;

3963 [Uu][Ss][Ee][Rr])

3964 # XXX: is null $user ok?

3965 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3966 [ -n "$GF_VIEWONLY" ] && continue

3967 form="$user"

3968 ;;

3969 [Pp]assword)

3970 [ -n "$GF_VIEWONLY" ] && continue

3971 form="`cgi_passwd`"

3972 val=""

3973 ;;

3974 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3975 [ -n "$GF_VIEWONLY" ] && continue

3976 if [ -z "$rowid" ]; then

3977 val=`genserial`

3978 fi

3979 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3980 prompt="" ;;

3981 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3982 prompt=""

3983 ;;

3984 parent|path|blog*)

3985 prompt=""

3986 ;;

3987 "*"*)

3988 tail=$tail"``"

3989 continue ;;

3990 esac

3991 if [ -n "$prompt" ]; then

3992 if [ -n "${GF_VIEWONLY}" ]; then

3993 form=$val

3994 else

3995 :

3996 fi

3997 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3998 else

3999 hiddens=$hiddens$nl"$form"

4000 fi

4001 done < $1

4002 # enctype="multipart/form-data"

4003 cat<<EOF

4004 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

4005 ${rowid:+$rm}

4006 <table class="b $2">

4007 $forms

4008 </table>$hiddens

4009 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4010 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4011 EOF

4012 if [ -z $GF_VIEWONLY ]; then

4013 cat<<EOF

4014 <input type="submit" name="sub" value="OK">

4015 <input type="reset" name="res" value="Reset">

4016 EOF

4017 fi

4018 cat<<EOF

4019 $GF_ARGS</form>

4020 $tail

4021 EOF

4022 }

4023 edittable() {

4024 # $1=form-def $2=table $3 rowid

4025 genform "$@"

4026 }

4027 viewtable() {

4028 GF_VIEWONLY=1 genform "$@"

4029 }

4030 showattc() {

4031 # $1=table_m $2=rowid &optional $3=RawFlag

4032 ## err \$1=$1 \$2=$2 \$3=$3

4033 if ! isfilereadable $user $1 $2; then

4034 contenttype; echo

4035 echo "このファイルは管理者のみしか見られません" | html p

4036 putfooter; exit

4037 fi

4038 idir=`umask 002; mktempd` || exit 1

4039 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4040 bin=$idir/$myname-$$.bin

4041 sql="select quote(bin) from $1 where rowid='$2';"

4042 ## err showattc: sql: $sql

4043 sq $db "$sql" | unhexize > $bin

4044 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4045 type=${tv%//*} fn=${tv#*//}

4046 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4047 ct=${type#file:}

4048 case $ct in # all text/* changed to text/plain

4049 text/*)

4050 charset=`nkf -g $bin|cut -d' ' -f1`

4051 case $charset in

4052 ASCII*) charset="" ;;

4053 esac

4054 if [ -z "$3" ]; then

4055 ct="text/html${charset:+; charset=$charset}"

4056 link="?showattc+$1+$2+raw"

4057 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4058 | sed 's,^,<span></span>,' \

4059 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4060 -D_LINK_="$link" \

4061 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4062 exit $?

4063 fi

4064 ct="text/plain${charset:+; charset=$charset}"

4065 ;;

4066 esac

4067 contenttype "$ct"

4068 echo "Content-Disposition: filename=\"$fn\""

4069 echo "Content-Length: " `cat $bin | wc -c`; echo

4070 #echo "Content-Type: " ${type#file:}; echo

4071 cat $bin

4072 }

4073 #

4074 # Some default stupid handler on CGI values

4075 #

4076 default_storedb() {

4077 # ARG: $1=table-def-file

4078 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4079 tbl=`basename $1`

4080 tbl=${tbl%.def}

4081 cols="`grep :text $1|cut -d: -f2`"

4082 col=`echo "$cols"|head -1`

4083 vcol=`getpar $col`

4084 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4085 if [ -n "$vcol" ]; then

4086 par2table $1

4087 else

4088 return 2 # No insertion occurred

4089 fi

4090 }

4091

4092 default_view() { # $1=def-file

4093 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4094 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4095 default_storedb "$@"

4096 query "select rowid from $tbl order by rowid desc;" \

4097 | while read rowid; do

4098 viewtable $1 $tbl $rowid

4099 done | _m4 -D_TITLE_="$tbl" \

4100 -D_FORM_="`genform $1`" \

4101 -D_DUMPTABLE_="syscmd(cat)" \

4102 $layout/html.m4.html $layout/form+dump.m4.html

4103 }

4104 default_viewtext() { # $1=def-file

4105 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4106 default_storedb "$@"

4107 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4108 | _m4 -D_TITLE_="$tbl" \

4109 -D_FORM_="`genform $1`" \

4110 -D_DUMPTABLE_="syscmd(cat)" \

4111 $layout/html.m4.html $layout/form+dump.m4.html

4112 }

4113 default_smail() {

4114 default_storedb "$@"

4115 if [ $? -eq 2 ]; then

4116 _m4 -D_TITLE_="入力" \

4117 -D_FORM_="`genform $1`" \

4118 -D_DUMPTABLE_="" \

4119 $layout/html.m4.html $layout/form+dump.m4.html

4120 return

4121 fi

4122 cond=""

4123 for pk in `gettblpkey $tbl`; do

4124 pv=$(sqlquote "$(getpar $pk)")

4125 cond="$cond${cond:+ and }$pk=$pv"

4126 done

4127 sql="select rowid from $tbl where $cond;"

4128 rowid=`query "$sql"`

4129 ## err smail1 - "$sql" "-> rowid=$rowid"

4130

4131 while IFS=: read prompt name keytype type args; do # Read from $1

4132 val=`getpar $name`

4133 if [ -n "$val" ]; then

4134 text="$text

4135 $prompt

4136 $name=$val

4137 ---------------------------------------------------------"

4138 fi

4139 case "$type" in

4140 image|document|file)

4141 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4142 fns=$(echo "$fn"|while read fn; do

4143 err mv $tmpd/$fn.orig $tmpd/$fn

4144 mv $tmpd/$fn.orig $tmpd/$fn

4145 rm $tmpd/$fn.rowid # Remove cache flag

4146 ## err "`ls $tmpd/$fn`"

4147 echo $fn

4148 done)

4149 files="$files $fns"

4150 ;;

4151 esac

4152 done < $1

4153 ## err FILES=$files "`ls -lF $tmpd`"

4154 subj="from ${REMOTE_ADDR}"

4155 (echo "$url"

4156 echo "への書き込みがありました。"

4157 echo "------"

4158 echo "$text"

4159 ) | (cd $tmpd &&

4160 err LS="`ls -lF`" &&

4161 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4162 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4163 echo "以下の内容で送信しました。" | html p

4164 viewtable $1 $tbl \

4165 `query "select rowid from $tbl order by rowid desc limit 1;"`

4166 echo "戻る" | html a "href=\"?\""

4167 }