s4: 1aa16edc28e5 s4-funcs.sh

s4

view s4-funcs.sh @ 688:1aa16edc28e5

Partial Committing Starts: For session-DB separation, prepare session db file.

author	HIROSE Yuuji <yuuji@gentei.org>
date	Mon, 18 May 2020 19:07:52 +0900
parents	19d28bbdbee8
children	13fb1392d2f2

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=$1

1229 oifs="$IFS"

1230 IFS="&"

1231 for us in $2; do

1232 k=${us%%=*}

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

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

1235 #echo $k=$v

1236 done

1237 IFS="$oifs"

1238 }

1239 checkdomain() (

1240 # Check the validity of domain by referring DNS

1241 item=$1

1242 err checkdomain $1

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

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

1245 )

1246 pwcheck() {

1247 # $1=passwd

1248 dbpswd=`getpwfield $user pswd`

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

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

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

1252 }

1253 mypwhash() {

1254 mycrypt "`cat`" `genrandom 5`

1255 }

1256 wasureta() {

1257 user=$1

1258 if ! checkdomain $user; then

1259 contenttype; echo

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

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

1262 putfooter

1263 exit 0

1264 fi

1265 newpswd=`genrandom` # newsalt=`genrandom 5`

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

1267 encpswd=`echo $newpswd|mypwhash`

1268 dbsetbyid user $user pswd "$encpswd"

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

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

1271 $msgdir/mail-newaccount.m4 \

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

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

1274 }

1275 checkauth() {

1276 user=`getpar user`

1277 skc=`getpar skey` # from cookie

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

1279 skey="`getpwfield $user skey`"

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

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

1282 return 0

1283 fi

1284 fi

1285 pswd=`getpar pswd`

1286 quser=`sqlquotestr "$user"`

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

1288 if [ -z "$dbuser" ]; then

1289 return 1

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

1291 wasureta "$user"

1292 return 1 # wasureta error

1293 fi

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

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

1296 if pwcheck "$pswd"; then

1297 newsession=`genrandom 50`

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

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

1300 gencookie "user=$user" "skey=$newsession"

1301 return 0

1302 else

1303 return 4 # Heavy load??

1304 fi

1305 fi

1306 return 2 # Password mismatch

1307 }

1308 showlogin() {

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

1310 s4name=${S4NAME:-s4}

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

1312 cat $layout/login.m4.html

1313 echo '</body></html>'

1314 ) | _m4 \

1315 -D_BODYCLASS_="login" \

1316 -D_TITLE_="$s4name" \

1317 -D_SYSNAME_="Welcome to $s4name" \

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

1319 exit 0

1320 }

1321 dologin() {

1322 checkauth

1323 st=$?

1324 if [ $st != 0 ]; then

1325 contenttype; echo

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

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

1328 showlogin # and EXIT

1329 fi

1330 }

1331

1332 # Do instant jobs here

1333 dbsetup

1334 trap cleanup INT HUP EXIT TERM PIPE

1335 # trap cleanup INT HUP

1336

1337 err() {

1338 echo "$@" 1>&3

1339 }

1340

1341 cgiinit() {

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

1343 tmpf=tmp/stream

1344 tmpd=`tmpd=$tmpdir mktempd`

1345 tmpfiles=$tmpfiles" $tmpd"

1346 addsession $session

1347 getcookie

1348 case "$REQUEST_METHOD" in

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

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

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

1352 # cat > $tmpf # too much?

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

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

1355 s="`cat tmp/stream`"

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

1357 ;;

1358 esac

1359 case "$CONTENT_TYPE" in

1360 *boundary*)

1361 bndry=${CONTENT_TYPE#*boundary=}

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

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

1364 do

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

1366 #echo u=$us

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

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

1369 # err k=$k v=$v

1370 case "$k" in

1371 *:filename)

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

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

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

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

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

1377 :

1378 fi

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

1380 case "$mimetype" in

1381 [Ii]mage/x-xcf)

1382 bzip2 "$tmpd/$v"

1383 v=${v}.bz2

1384 ;;

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

1386 [Ii]mage/*)

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

1388 ;;

1389 esac

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

1391 then

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

1393 continue

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

1395 replpar text string \

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

1397 continue

1398 fi

1399 ;;

1400 *)

1401 type='string'

1402 ;;

1403 esac

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

1405 setpar "$k" "$type" "$v"

1406 done

1407 ;;

1408 *)

1409 setviastring par "$s"

1410 ;;

1411 esac

1412 }

1413 email4group() {

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

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

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

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

1418 sql="WITH

1419 grpemails AS (

1420 SELECT gname, user, val email

1421 FROM grp_mem NATURAL JOIN grp_mem_s

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

1423 useremails AS (

1424 SELECT user.name, val email

1425 FROM user

1426 LEFT JOIN user_m

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

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

1429 FROM useremails u LEFT JOIN grpemails g

1430 ON u.name=g.user

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

1432 query "$sql"

1433 }

1434 email4groupbyuid() {

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

1436 g=$1; shift

1437 uids=`echo "$@"`

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

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

1440 email4group "$g" `query "$sql"`

1441 }

1442 myemail4group() {

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

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

1445 }

1446 collectmembersbyid() {

1447 # Collect user names of group specified by grid

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

1449 query "SELECT user FROM grp_mem \

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

1451 }

1452 collectmembersbyid() {

1453 # Collect user names of group name

1454 qgrp=`sqlquote "$1"`

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

1456 }

1457 collectgecosesbyid() {

1458 # Collect user gecoses of group

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

1460 query<<-EOF

1461 SELECT gecos

1462 FROM gecoses

1463 WHERE name IN (SELECT user FROM grp_mem

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

1465 EOF

1466 }

1467 collectemail() (

1468 # Collect email addresses for group $1

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

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

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

1472 for e; do

1473 if isuser "$e"; then

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

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

1476 else

1477 qgrp=`sqlquote "$e"`

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

1479 gmem="grp_mem"

1480 else

1481 tm=`sqlquote "$TEAM"`

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

1483 fi

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

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

1486 $gmem g left join grp_mem_s s

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

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

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

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

1491 query "$sql"

1492 fi

1493 done

1494 )

1495 sendinvitation() (

1496 # $1=email

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

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

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

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

1501 gecos=`gecos`

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

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

1504 _m4 -D_URL_="$urlbase" \

1505 -D_USER_="$name" \

1506 -D_EMAIL_="$1" \

1507 -D_REGIST_="$regist" \

1508 -D_ADMIN_="$admin" \

1509 $msgdir/mail-invite.m4 \

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

1511 return 0

1512 )

1513 emaildomaincheck() {

1514 case "$1" in

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

1516 *@*)

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

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

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

1520 return 2

1521 fi

1522 return 0

1523 ;;

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

1525 esac

1526 }

1527 invite() {

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

1529 case "$email" in

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

1531 *@*)

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

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

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

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

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

1537 elif sendinvitation $email; then

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

1539 else # Cannot be reached here

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

1541 fi ;;

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

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

1544 esac

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

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

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

1548 fi

1549 if [ -f $invite_policy ]; then

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

1551 else

1552 pol="$invite_policy"

1553 fi

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

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

1556 -D_INVITE_POLICY_="$pol" \

1557 $layout/html.m4.html $layout/invite.m4.html

1558 }

1559 regist() {

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

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

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

1563 echo "bye bye" | html p

1564 reutrn

1565 fi

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

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

1568 cat<<EOF

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

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

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

1572 EOF

1573 return

1574 fi

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

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

1577 # Fake login password to wasureta

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

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

1580 wasureta $email

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

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

1583 addsession $1 # for removal after 1 minute

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

1585 $layout/login.m4.html

1586 return

1587 }

1588 group_safename() {

1589 # Convert $1 to safe group name

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

1591 }

1592 groupupdate() {

1593 gname=`getpar gname`

1594 qgname=`sqlquote "$gname"`

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

1596 # See ALSO same job in showgroup()

1597 newgname=`group_safename "$gname"`

1598 err newgname=$newgname

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

1600 err NewGNAME: gname=$newgname

1601 gname=$newgname

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

1603 replpar gname string "$gname"

1604 fi

1605 # Name confliction check

1606 parow=`getpar rowid`

1607 ## err parow=$parow

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

1609 query "BEGIN EXCLUSIVE;"

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

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

1612 if [ $count -gt 0 ]; then

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

1614 query "END;"

1615 return

1616 fi

1617 par2table $formdir/grp.def

1618 query "END TRANSACTION;"

1619 # Remove orphan

1620 : <<EOF

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

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

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

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

1625 EOF

1626 rm=`getpar rm` cfm=`getpar confirm`

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

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

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

1630 sql="delete from blog where id in

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

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

1633 query "$sql";

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

1635 return # and return

1636 fi

1637 fi

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

1639 fi

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

1641 grid=$(query $sql)

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

1643 grp $grid

1644 }

1645 groupclone() {

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

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

1648 case "$1" in

1649 */noteam)

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

1651 esac

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

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

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

1655 return

1656 fi

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

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

1659 return

1660 fi

1661 i=0

1662 while true; do

1663 copy="-copy$i"

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

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

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

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

1668 i=$((i++))

1669 continue

1670 fi

1671 break

1672 done

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

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

1675 query<<-EOF

1676 BEGIN;

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

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

1679 SELECT $newqname, key, val

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

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

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

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

1684 -- Copy members and their configuration --

1685 REPLACE INTO grp_mem SELECT $newqname, user

1686 FROM grp_mem WHERE gname=$qgrp;

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

1688 FROM grp_mem_s WHERE gname=$qgrp;

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

1690 FROM grp_mem_m WHERE gname=$qgrp $noteam;

1691 -- Copy administrators --

1692 REPLACE INTO grp_adm SELECT $newqname, user

1693 FROM grp_adm WHERE gname=$qgrp;

1694 COMMIT;

1695 EOF

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

1697 STOPCLONEMSG=1 groupconf "$newrowid"

1698 }

1699 groupman() {

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

1701

1702 GF_STAGE="grpconf"

1703 GF_STAGE=groupupdate

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

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

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

1707 -D_DUMPTABLE_="syscmd(cat)" \

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

1709 }

1710 userconf() {

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

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

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

1714 }

1715 groupconf() {

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

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

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

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

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

1721

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

1723 grp=`getgroupbyid $rowid`

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

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

1726 return

1727 fi

1728

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

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

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

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

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

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

1735 <div>

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

1737 <table>

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

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

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

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

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

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

1744 </table>

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

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

1747 </div>

1748 EOF

1749 fi

1750 }

1751 mems() {

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

1753 kwd=`getpar kwd`

1754 listmember $kwd

1755 }

1756 grps() {

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

1758 kwd=`getpar kwd`

1759 listgroup $kwd \

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

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

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

1763 $layout/form+dump.m4.html

1764 }

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

1766 gpg=`getpar grp`

1767 grid=${1:-$gpg}

1768 grp=`getgroupbyid "$grid"`

1769 ## . ./s4-blog.sh

1770 jg=`getpar joingrp`

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

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

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

1774 fi

1775 htmlheader=$layout/html.m4.html

1776 showgroup "$grid"

1777 }

1778 sql4interestblogs() {

1779 cat<<EOF

1780 CREATE TEMPORARY VIEW interestblogs AS

1781 SELECT blog.rowid rid, id, author

1782 FROM blog

1783 NATURAL JOIN

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

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

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

1787 WHEN (SELECT user FROM grp_mem

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

1789 THEN 0

1790 ELSE 1

1791 END;

1792 EOF

1793 }

1794 listnewblogsql() { # $1=user

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

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

1797 newdays=${newdays:-14}

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

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

1800 WHERE user='$user'

1801 AND tblrowid IN

1802 ($blogreadflagrowid,

1803 $blogcutoffflagrowid)),

1804 $basetime -- "0"

1805 );"`

1806 cat<<EOF

1807 `sql4interestblogs`

1808 WITH article_ctime as (

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

1810 FROM article join article_s s using(id)

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

1812 GROUP BY id

1813 ), blog_title_owner as (

1814 SELECT blg.rid brid, id,

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

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

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

1818 ), blogall as (

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

1820 ), news as (

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

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

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

1824 FROM blogall bl

1825 LEFT JOIN

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

1827 ON bl.brid=al.tblrowid

1828 WHERE atime < bl.ctime

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

1830 LIMIT 10

1831 ) SELECT brid LINK, "新着",

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

1833 ctime, title,

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

1835 FROM news;

1836 EOF

1837 }

1838

1839 search_form() {

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

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

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

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

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

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

1846 @week → 最近一週間

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

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

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

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

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

1852 auth=""

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

1854 case "$1" in

1855 author=*)

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

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

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

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

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

1861 ;;

1862 grp=*)

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

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

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

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

1867 ;;

1868 esac

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

1870 cat<<-EOF

1871 <div class="right">

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

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

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

1875

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

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

1878

1879 </form>

1880 </div>

1881 EOF

1882 }

1883

1884 imgsrc_cache() (

1885 # $1 = directory for cache'ing

1886 # $2 = table (user_m or grp_m)

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

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

1889 dir="$1" tbl="$2"

1890 keycond="$3"

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

1892 ORDER BY rowid DESC LIMIT 1"

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

1894 tmpf=$tmpd/imgsrc_cache.$$

1895 case "$4" in

1896 [Ss]) size=S ;;

1897 [Oo]) size=O ;;

1898 *) size=M ;;

1899 esac

1900 # ImageCache filename storing schema:

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

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

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

1904 valtype=`query "$sql0"`

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

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

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

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

1909 case "$filetype" in

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

1911 esac

1912 fi

1913 cacheimg_S=$dir/S_$filename

1914 cacheimg_M=$dir/M_$filename

1915 cacheimg_O=$dir/$filename

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

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

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

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

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

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

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

1923 else

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

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

1926 fifo2=$fifo.2

1927 mkfifo $fifo $fifo2

1928 fmt=${filename##*.}

1929 ## [[ NOTE ]]

1930 ## a. convert oldimage newimage

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

1932 ## b is much smaller than a

1933 cat $tmpf | unhexize \

1934 | tee $fifo \

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

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

1937 | tee $fifo2 \

1938 | convert - "$cacheimg_M" &

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

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

1941 | convert - "$cacheimg_S" &

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

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

1944 echo '">'

1945 echo "$sum" > $sumfile

1946 fi

1947 ## Now preparing cache image, done.

1948 ## Store this information to DB

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

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

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

1952 query <<-EOF

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

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

1955 EOF

1956 )

1957

1958 showhome() {

1959 # $1=userRowIdToShow

1960 err showhome \$1=$1

1961 case "$1" in

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

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

1964 esac

1965 ## err ShowHome: uname=$uname

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

1967 gecos=`gecos "$uname"`

1968 ## err SH:gecos=$gecos

1969 GF_VIEWONLY=1

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

1971 search_form_args=""

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

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

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

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

1976 # Display folders

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

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

1979 and type like 'file:%';"

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

1981 nfile=`query "$sql"`

1982 # err nfile=$nfile

1983 if [ $nfile -gt 0 ]; then

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

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

1986 fi

1987 else

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

1989 GROUP BY user;"`

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

1991 search_form_args="author=$uname"

1992 fi

1993 . ./s4-blog.sh

1994

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

1996 search_form "$search_form_args" > $sf

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

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

1999 viewtable $formdir/user.def user $1

2000 echo "</div>"

2001 } > $pf

2002

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

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

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

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

2007 if true; then

2008 tbl=user_m

2009 enticond="name='$uname'"

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

2011 else

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

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

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

2015 echo '">'

2016 }

2017 fi > $imf

2018 fi

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

2020 val='$uname';"`

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

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

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

2024 listblog $uname > $bf

2025 ## unset ddd

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

2027

2028 hometail=$tmpd/tail.$$

2029 mkfifo $hometail

2030

2031 #Calling listgroupbytable, originally here

2032

2033 (

2034 # Display Most Recent Entry

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

2036 shortval=${shortval:-val}

2037

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

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

2040 ctime,

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

2042 (SELECT gecos FROM gecoses

2043 WHERE name=(SELECT val FROM blog_s

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

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

2046 FROM blog b

2047 JOIN

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

2049 FROM article a, article_s s

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

2051 GROUP BY blogid ORDER BY val DESC LIMIT 50

2052 ) m

2053 ON b.id=m.blogid;"

2054 # This should be as follows

2055 : <<EOF

2056 WITH arts AS(

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

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

2059 FROM article a NATURAL JOIN article_s s

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

2061 GROUP by s.id

2062 )

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

2064 FROM arts a0

2065 JOIN

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

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

2068 ORDER BY ctime DESC LIMIT 50;

2069 EOF

2070

2071 cat<<-EOF

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

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

2074 <div class="lcto">

2075 `DT_VIEW=replyblog dumptable html blog`

2076 </div>

2077 EOF

2078 unset DT_SQL

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

2080 # Display NEWS

2081 # 2016-06-26

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

2083 acclog blog $blogreadflagrowid

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

2085 fi

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

2087 sql=`listnewblogsql "$user"`

2088 # echo "$sql" > tmp/listnew

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

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

2091 cont=$((cont-1))

2092 err newcount=$cont

2093 if [ $cont -gt 0 ]; then

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

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

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

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

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

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

2100 `cgi_submit '確定'`

2101 `cgi_hidden read yes`

2102 EOF

2103 echo "$new10 "

2104 echo "</div>"

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

2106 acclog blog $blogcutoffflagrowid # for speed

2107 fi

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

2109 : # DT_SQL=

2110 fi

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

2112 #

2113 listgroupbytable $formdir/grp.def $cond |

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

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

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

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

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

2119 -D_NBLOG_="$nblog" \

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

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

2122 $layout/html.m4.html $layout/home.m4.html

2123

2124 # Record access log

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

2126 }

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

2128 contenttype; echo

2129 ## err commission: "$@"

2130 gname=`getgroupbyid $1`

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

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

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

2134 grp_reg_adm "$@"

2135 else

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

2137 fi

2138 }

2139 listgroupbytable() {

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

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

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

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

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

2145 NGsql="select distinct tag from\

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

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

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

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

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

2151 for tag in `query "$sql"`

2152 do

2153 ## err ListGrp: tag=$tag

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

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

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

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

2158 as rowid,

2159 gname,

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

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

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

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

2164 ## err PersonalGroupList= `echo $sql`

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

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

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

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

2169 echo '</table>'

2170 done

2171 echo '</div>'

2172 }

2173 iconhref() (

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

2175 data=`percenthex "$1"`

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

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

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

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

2180 )

2181 iconhref2() (

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

2183 src=$1

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

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

2186 )

2187 listentry() (

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

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

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

2191 cond='' hiddens=''

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

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

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

2195 offset=$((offset-1))

2196 fi

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

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

2199 limit=$listentlimit

2200 dir=`getcachedir "$1"`

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

2202 hrb="$myname?home"

2203 deficon=person-default.png

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

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

2206 gcs=gecos

2207 else # if group

2208 hrb="$myname?grp"

2209 deficon=group-default.png

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

2211 gcs=name

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

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

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

2215 ## err tagconv=$tagconv

2216 fi

2217 fi

2218 if [ ! -d $dir ]; then

2219 mkdir -p $dir

2220 fi

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

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

2223 fi

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

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

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

2227 fi

2228 tag=`getpar tag` tag2=`getpar tag2`

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

2230 tag="" tag2=""

2231 fi

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

2233 tag=${tag:-$tag2}

2234 qtag=`sqlquote "$tag"`

2235 cond2="tag=$qtag"

2236 fi

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

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

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

2240 fi

2241

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

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

2244 ##qgrp=`sqlquote $grp`

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

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

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

2248 quote(a.$nm) as qname,

2249 (SELECT val FROM ${tbl}_s

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

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

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

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

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

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

2256 end

2257 as name,

2258 b.tag,

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

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

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

2262 then 'ADMIN'

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

2264 then 'Member'

2265 when '$iamowner' = '' then ''

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

2267 CASE '$entity'

2268 WHEN 'グループ'

2269 THEN coalesce(

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

2271 'open')

2272 ||

2273 CASE WHEN '$user'

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

2275 THEN ' ismember'

2276 ELSE ''

2277 END

2278 ELSE 'user'

2279 END regmode

2280 from $tbl a left join

2281 (select $nm as name,

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

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

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

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

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

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

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

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

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

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

2292 # Note that mtime is stored only in grp_s.

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

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

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

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

2297 # Show owner/member filter button

2298 METHOD=GET

2299 hiddens="$hiddens

2300 `cgi_hidden kwd \"$kwd\"`

2301 `cgi_hidden stage \"$stage\"`"

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

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

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

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

2306 for l in $args; do

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

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

2309 s=" selected"

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

2311 else

2312 s=""

2313 fi

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

2315 done

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

2317 cat<<-EOF

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

2319 </form>

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

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

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

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

2324 EOF

2325 # limit=3

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

2327 fi

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

2329 echo '<div>'

2330 METHOD=GET cgi_form $stage <<EOF

2331 $form

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

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

2334 $hiddens

2335 EOF

2336 echo '</div>'

2337 else

2338 echo $selectedtags | html p

2339 fi

2340 cat<<EOF

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

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

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

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

2345 </form>

2346 EOF

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

2348 nextbtn=$(

2349 cat<<EOF

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

2351 `cgi_submit 次の${limit}件`

2352 $hiddens

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

2354 EOF

2355 )

2356 fi

2357 if [ $offset -gt 0 ]; then

2358 prevbtn=$(

2359 cat<<EOF

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

2361 `cgi_submit 前の${limit}件`

2362 $hiddens

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

2364 EOF

2365 )

2366 fi

2367 pnbtn="$nextbtn$prevbtn"

2368 echo $pnbtn

2369

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

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

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

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

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

2375 # err newlnk=$lnk regmode=$regmode

2376 icondir=$dir/$id

2377 # Pick up only last icon

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

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

2380 | _m4 $tagconv

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

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

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

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

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

2386 else

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

2388 fi

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

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

2391 else

2392 cond="$nm=$qname"

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

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

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

2396 # err "img=[$img]"

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

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

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

2400 echo "</a>"

2401 else

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

2403 fi

2404 fi

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

2406 echo "</div>"

2407 done

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

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

2410 )

2411 listmember() {

2412 listentry user "$@"

2413 }

2414 listgroup() {

2415 listentry group "$@"

2416 }

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

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

2419 query "SELECT DISTINCT hex(val) FROM grp_mem_m

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

2421 }

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

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

2424 grid=`getpar grid`

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

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

2427 else

2428 grid=$1

2429 fi

2430 grp=`getgroupbyid $grid`

2431 qgrp=`sqlquote "$grp"`

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

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

2434 if isgroup "$grp"; then

2435 tf=$tmpd/title.$$

2436 sf=$tmpd/search.$$

2437 bodyclass=`query "SELECT val FROM grp_s

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

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

2440 ismember="ismember"

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

2442 else

2443 ismember="" # bodyclass="group"

2444 fi

2445 bodyclass="$bodyclass grouphome"

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

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

2448

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

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

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

2452 -D_BODYCLASS_="$bodyclass" \

2453 -D_DUMPTABLE_="" \

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

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

2456 # $htmlheader is defined in grp()

2457 else # if $grp is removed at par2table

2458 listgroup

2459 fi

2460 }

2461 showgroupsub() {

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

2463 # Using $ismember

2464 rowid=$2

2465 grp=`getgroupbyid $2`

2466 qgrp=`sqlquote "$grp"`

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

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

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

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

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

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

2473 return

2474 fi

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

2476 enticond="gname=$qgrp"

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

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

2479 cat<<-EOF

2480 <p class="groupimg">

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

2482 EOF

2483 fi

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

2485 viewtable $1 grp $rowid

2486 echo "</div>"

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

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

2489 iamowner=$rowid

2490 colmd=" mode"

2491 fi

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

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

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

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

2496 cat<<EOF

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

2498 <div class="fold clear">

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

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

2501 <div>

2502 `cgi_textarea message "" "cols=60"`

2503 `cgi_submit 送信`

2504 `cgi_reset リセット`

2505 </div>

2506 `cgi_hidden grp $rowid`

2507 </div></form>

2508 EOF

2509 fi

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

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

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

2513 else

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

2515 fi

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

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

2518 and key='email';"`

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

2520 ## and key='email';"

2521 ##err email=$eml

2522 cat <<EOF

2523 <div class="fold clear">

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

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

2526 <div>

2527 EOF

2528 cgi_form grp <<EOF

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

2530 <table class="b">

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

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

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

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

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

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

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

2538 </table>

2539 `cgi_hidden grp $rowid`

2540 EOF

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

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

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

2544 fi

2545 echo '</div></div>'

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

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

2548 cat<<-EOF

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

2550 `cgi_hidden owner "$grp"`

2551 `cgi_hidden kwd "@week"`

2552 `cgi_hidden stage searchart`

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

2554 </form>

2555 EOF

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

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

2558 DT_CHLD=article:blogid \

2559 DT_QOWNER="$qgrp" \

2560 DT_VIEW=replyblog dumptable html blog \

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

2562

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

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

2565 cm="?commission+$rowid"

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

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

2568 # team list

2569 hexteams=`hexteams "$grp"`

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

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

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

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

2574 SELECT val TEAM,

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

2576 MEMBERS

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

2578 EOF

2579 echo '</table></div>'

2580 fi

2581 }

2582 grp_getbodyclass() {

2583 # Get css class name for document.

2584 # `moderated' for moderated groups

2585 # `ismember' for groups where user belongs

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

2587 # $user=userNameCurrentlyLogin

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

2589 qgrp=`sqlquote "$1"`

2590 query<<-EOF

2591 SELECT coalesce(

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

2593 'open')

2594 ||

2595 CASE WHEN '$user'

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

2597 THEN ' ismember'

2598 ELSE ''

2599 END;

2600 EOF

2601 }

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

2603 err GRP_ACTION:IN

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

2605 grp=`getgroupbyid "$grid"`

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

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

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

2609 fi

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

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

2612 fi

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

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

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

2616 $layout/html.m4.html

2617

2618 isowner=""

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

2620 usel=`getpar usel`

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

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

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

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

2625

2626 rm=`getpar rm` cfm=`getpar confirm`

2627 ## err rm=$rm cfm=$cfm

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

2629 if [ "$isowner" ]; then

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

2631 # Eliminate

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

2633 for tbl in grp_mem grp_mem_s grp_mem_m; do

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

2635 # echo "sql=$sql"

2636 query "$sql"

2637 err rmGRPuser "$sql"

2638 done

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

2640 #err num=$num

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

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

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

2644 # err `echo "$sql"`

2645 html pre<<EOF

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

2647 `query "$sql"`

2648 EOF

2649 fi

2650 else

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

2652 return

2653 fi

2654 else # not Group Owner

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

2656 return

2657 fi

2658 cat<<EOF

2659

2660 EOF

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

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

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

2664 return 0

2665 fi

2666 gecos=`gecos $user`

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

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

2669 fromad=`myemail4group "$grp"`

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

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

2672 test -n `getpar sender` &&

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

2674 replyto=$fromad

2675

2676 ## Start parse of attachment files

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

2678 ar=`getpar supprcpt`

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

2680 for a in $ar; do

2681 checkdomain "$a" && supprcpt="$supprcpt $a"

2682 done

2683 fi

2684 subj=`getpar subject`

2685 afiles=""

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

2687 do

2688 f=$tmpd/$fn

2689 if [ -s $f ]; then

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

2691 fi

2692 done

2693 else

2694 preface=$(cat <<-EOF

2695 $url

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

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

2698 ----------------------------------------------------------

2699 EOF

2700 )

2701 fi

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

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

2704 REPLYTO=$replyto

2705 SMAIL_TO="$user $fromad$supprcpt"

2706 MAIL_FROM=$mail_from

2707 export REPLYTO SMAIL_TO MAIL_FROM

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

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

2710 ./sendmultipart.sh -t "$rcpts" -s "$subj" -f "$mail_from" $afiles

2711 else

2712 smail "$rcpts" "$subj"

2713 fi <<EOF

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

2715 EOF

2716 if [ $? = 0 ]; then

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

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

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

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

2721 html pre<<EOF

2722 `query "$sql"`

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

2724 EOF

2725 err SendDone: `echo $sql`

2726 fi

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

2728 grp_reg_adm $grid $usel

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

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

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

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

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

2734 team=newteam

2735 fi

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

2737 cat<<-EOF | html p

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

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

2740 EOF

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

2742 else

2743 grp_add_team $grid "$team" $usel

2744 fi

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

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

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

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

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

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

2751 grp_rm_team $grid "$rmteam" $usel

2752 else

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

2754 fi

2755 else

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

2757 fi

2758 fi

2759 fi

2760 # POST count summary

2761 from=`getpar from`; to=`getpar to`

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

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

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

2765 # New entry

2766 sql="WITH mems AS (

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

2768 ON gm.user=g.name

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

2770 ), target_article AS (

2771 SELECT id FROM article_s

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

2773 ), posts AS (

2774 SELECT author, count(author) post

2775 FROM article NATURAL JOIN article_s NATURAL JOIN target_article

2776 WHERE blogid IN (SELECT id FROM blog_s

2777 WHERE key='owner'

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

2779 AND key='text'

2780 GROUP BY author

2781 ), teams AS (

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

2783 FROM grp_mem_m

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

2785 AND key='team'

2786 GROUP BY user

2787 ), user_post AS (

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

2789 FROM mems m LEFT JOIN posts

2790 ON m.name=posts.author

2791 GROUP by m.rowid

2792 )

2793 SELECT

2794 CASE

2795 WHEN (SELECT user FROM grp_adm

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

2797 AND user=up.name) IS NOT NULL

2798 then 'k'

2799 ELSE ''

2800 END || rowid || ',' || gecos NAME,

2801 post POST, team _TEAM_

2802 FROM user_post up LEFT JOIN teams t

2803 ON up.name=t.user

2804 ORDER BY gecos;"

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

2806 tf=$tmpd/title.$$

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

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

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

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

2811 </p></div>"

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

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

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

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

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

2817 </div>"

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

2819 allhexteams=$(hexteams "$grp")

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

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

2822 else

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

2824 fi

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

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

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

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

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

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

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

2832 </div>

2833 "

2834 fi

2835 stf=$tmpd/selteam.$$

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

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

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

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

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

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

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

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

2844 cgi_form grpaction<<EOF \

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

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

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

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

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

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

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

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

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

2854 <div class="foldtabs">

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

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

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

2858 `cgi_datalist_h teams $allhexteams`

2859 </div>

2860 ${rmteammsg}

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

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

2863 <div>

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

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

2866 <div class="folded">

2867 <table>

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

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

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

2871 </td></tr>

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

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

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

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

2876 </table>

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

2878 </div>

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

2880 </div>

2881 </div>

2882 ${isowner:+$cmmsg$excmsg}

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

2884 </div>

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

2886 <table class="td2r thl">

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

2888 </table>

2889 `cgi_hidden grp $grid`

2890 `cgi_hidden myuid $myuid id="myuid"`

2891 EOF

2892 }

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

2894 # Create TEMPORARY VIEW

2895 query<<EOF

2896 CREATE TEMPORARY VIEW writeusers AS

2897 SELECT DISTINCT author FROM article

2898 WHERE id in (

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

2900 );

2901 CREATE TEMPORARY VIEW movablegroups AS

2902 SELECT g.rowid growid , g.gname

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

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

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

2906 WHERE (SELECT author FROM writeusers

2907 EXCEPT

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

2909 IS NULL;

2910 $2

2911 EOF

2912 }

2913 sql4readableblogs() {

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

2915 # Blog is readable when:

2916 # 1: blog owner is an user

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

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

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

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

2921 CREATE TEMPORARY VIEW readableblogs AS

2922 SELECT blog.rowid rid, id, author

2923 FROM blog

2924 NATURAL JOIN

2925 (SELECT id,

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

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

2928 FROM blog_s GROUP by id) bs

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

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

2931 WHEN (SELECT val FROM grp_s

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

2933 AND

2934 (SELECT user FROM grp_mem

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

2936 THEN 0

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

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

2939 ELSE 1

2940 END;

2941 EOF

2942 }

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

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

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

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

2947 return

2948 fi

2949 owner=`getvalbyid blog owner $rowid`

2950 title=`getvalbyid blog title $rowid`

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

2952 | _m4 -D_TITLE_="修正" \

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

2954 -D_BLOGS_="" -D_DUMPTABLE_="" \

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

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

2957 # Move to group

2958 if isuser "$owner"; then

2959 crview4article $rowid

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

2961 ## err N=$n

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

2963 ## err ROWID=$rowid

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

2965 cat<<-EOF

2966 <div class="fold">

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

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

2969 <div>

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

2971 移動先グループ:

2972 <select name="mv2grp">

2973 EOF

2974 query ".mode html"

2975 query<<-EOF |

2976 $sql

2977 .mode list

2978 EOF

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

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

2981 cat<<-EOF

2982 </select>

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

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

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

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

2987 `cgi_hidden blogrowid $rowid`

2988 `cgi_submit 移動`

2989 `cgi_reset Reset`

2990 </form>

2991 </div>

2992 </div>

2993 EOF

2994 fi

2995 # end of isuser "$owner"

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

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

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

2999 cat<<-EOF

3000 <div class="fold">

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

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

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

3004 `query "SELECT

3005 coalesce((SELECT val FROM blog_s

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

3007 AND key='team'),

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

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

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

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

3012 `cgi_hidden blogrowid $rowid`<br>

3013 `cgi_submit 移動`

3014 `cgi_reset Reset`

3015 </form></div></div>

3016 EOF

3017 fi

3018 }

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

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

3021 blogrowid=`getpar blogrowid`

3022 cfm=`getpar cfm`

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

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

3025 . ./s4-blog.sh

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

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

3028 return

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

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

3031 elif { mv2grp=`getpar mv2grp`

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

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

3034 crview4article $blogrowid

3035 ########## TRANSACTION BEGIN

3036 query "BEGIN;"

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

3038 ## err Nwriteuser=$n

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

3040 query<<-EOF

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

3042 WHERE key='owner'

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

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

3045 EOF

3046 fi

3047 query "END;"

3048 ########## TRANSACTION END

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

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

3051 # blog owner can move it to ANY team

3052 case "$mv2team" in

3053 'なし')

3054 cat<<-EOF

3055 DELETE FROM blog_s

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

3057 AND key='team';

3058 EOF

3059 ;;

3060 "") ;;

3061 *)cat<<-EOF

3062 BEGIN;

3063 REPLACE INTO blog_s(id, key, val)

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

3065 'team', '$mv2team');

3066 REPLACE INTO blog_s(id, key, val)

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

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

3069 END;

3070 EOF

3071 esac | query

3072 fi

3073 blog_reply $blogrowid

3074 echo yes | html p

3075 }

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

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

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

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

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

3081 return

3082 fi

3083 owner=`getvalbyid blog owner $blogrowid`

3084 title=`getvalbyid blog title $blogrowid`

3085 author=`getvalbyid article author $rowid`

3086 ## err EDITart: owner=$owner, author=$author

3087 if isgrpowner "$user" "$owner"; then

3088 : EDIT OK

3089 elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then

3090 echo "本人か所有者しか編集できません." | html p

3091 return

3092 fi

3093 aid=`query "select id from article where rowid=$rowid;"`

3094 tmpout=$tmpd/editart.$$.out

3095 GF_ACTION="?replyblog+$blogrowid#$aid" \

3096 edittable $formdir/article.def article $rowid \

3097 > $tmpout

3098 rm -f /tmp/editart.out

3099 # Cannot use pipelining to m4 with genform() because of stdin stack

3100 _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \

3101 -D_FORM_="syscmd(cat $tmpout)" \

3102 -D_SUBTITLE_="`gecos $owner`の「$title」" \

3103 -D_BLOGS_= -D_DUMPTABLE_= \

3104 $layout/html.m4.html $layout/form+dump.m4.html

3105 }

3106 send2mem() {

3107 rowid=`getpar grp`

3108 rowid=${rowid%%[!0-9]*} # Cleaning

3109 if [ -z "$rowid" ]; then

3110 echo "グループが未指定です。" | html p

3111 return

3112 fi

3113 message=`getpar message`

3114 if [ -z "$message" ]; then

3115 echo "文章を入れてください。" | html p

3116 return

3117 fi

3118 grp=`getgroupbyid $rowid`

3119 members=`collectemail $grp`

3120 myuid=`query "SELECT rowid FROM user WHERE name='$user';"`

3121 mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`

3122 mailfrom="`gecos "$user"` <$mailfrom>"

3123 sj="グループ $grp 宛メッセージ(from `gecos $user`)"

3124 msg=$(cat<<-EOF

3125 $urlbase?grp+$rowid

3126 グループ $grp に所属する

3127 `gecos $user` さんよりメッセージ:

3128

3129 $message

3130 EOF

3131 )

3132 # smail rcpt subj (file)

3133 for m in $members; do

3134 echo "$msg" |

3135 MAIL_FROM=$mailfrom \

3136 SENDER=$noreply \

3137 REPLYTO=$mailfrom \

3138 SMAIL_TO="`echo "$grp" | nkf -jM | tr -d '\n'` readers <$m>" \

3139 smail "$m" "$sj"

3140 done

3141 cat<<EOF

3142 <p>以下のユーザに送信しました。</p>

3143 <pre>

3144 `collectgecosesbyid "$rowid" | sed 's/$/ さん/'`

3145 </pre>

3146 <p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>

3147 EOF

3148 }

3149 joingrpadmit() {

3150 # $1=yes/no $2=session-key

3151 if [ -z "$2" ]; then

3152 echo "bye bye" | html p; return

3153 fi

3154 t_usr=`session=$2 getpar user`

3155 t_grp=`session=$2 getpar group`

3156 ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp

3157 _m4 -D_TITLE_="joingrp" $layout/html.m4.html

3158 if [ -z "$t_usr" -o -z "$t_grp" ]; then

3159 echo "無効な加入依頼です。" | html p

3160 echo "有効期限が切れたか、

3161 他の管理者がいる場合は処理済みの可能性があります。" | html p

3162 return

3163 fi

3164 if ! isgrpowner "$user" "$t_grp"; then

3165 echo "グループ管理者のみの機能です。" | html p; return

3166 fi

3167 case $1 in

3168 yes) joingrp "$t_grp" "$t_usr" yes ;;

3169 no) joingrp "$t_grp" "$t_usr" no ;;

3170 *)

3171 echo "無効な指定です($1)。" | html p

3172 return ;;

3173 esac

3174 gid=$(query "select rowid from grp where gname=`sqlquote \"$t_grp\"`;")

3175 rcpts="`getgroupadminmails $t_grp` $user"

3176 ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"

3177 body="グループ <a href=\"?grp+$gid\">$t_grp</a>

3178 に

3179 $t_usr

3180 `[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`

3181 しました。"

3182 (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"

3183 query "delete from session where id='$2';"

3184 echo "$body" | html p

3185 }

3186

3187 joingrprequest() {

3188 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3189 jss="joingrp-`date +%s`-`genrandom 12`"

3190 addsession $jss +${memoplimitdays}days

3191 query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),

3192 ('$jss', 'user', 'string', `sqlquote \"$user\"`);"

3193 smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF

3194 $url

3195 $user さんから

3196 グループ $1

3197 に加入依頼がありました。

3198

3199 承認する:

3200 $urlbase?joingrpadmit+yes+$jss

3201

3202 白紙に戻す:

3203 $urlbase?joingrpadmit+no+$jss

3204 EOF

3205 echo "管理者に加入依頼を出しました。

3206 ${memoplimitdays}日以内に加入承認操作がされれば加入できますが、

3207 グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p

3208 }

3209 joingrp() {

3210 # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)

3211 ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4

3212 if isgrpowner "$user" "$1"; then

3213 isowner="yes"

3214 elif [ -n "$5" ]; then

3215 isowner="yes"

3216 else

3217 isowner=""

3218 fi

3219 ## err jg:isgrpowner: isowner="$isowner"

3220 if [ -n "$isowner" ]; then

3221 : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT

3222 elif [ x"$2" != x"$user" ]; then # if user is not login user

3223 echo "本人か、グループ管理者しか加入操作はできません。" | html p

3224 return

3225 elif [ x"$3" = x"no" ]; then

3226 : # Do not pursue those who leave

3227 elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then

3228 : # Member can change own email address for the joining moderated group

3229 else # adding user is $user itself

3230 case `getgroupattr $1 regmode` in

3231 moderated)

3232 joingrprequest "$@" # Request only

3233 return

3234 ;;

3235 *)

3236 ;;

3237 esac

3238 fi

3239 qgname=`sqlquote "$1"`

3240 grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`

3241 cond="where gname=$qgname and user='$2'"

3242 if [ x"$3" = x"yes" ]; then

3243 query "replace into grp_mem values($qgname, '$2');"

3244 # Notify joingrp to admin

3245 action="に加入しました。"

3246 if [ -n "$4" ]; then

3247 if msg=`emaildomaincheck "$4"`; then

3248 query "replace into grp_mem_s values($qgname, '$user', 'email', \

3249 'string', '$4', NULL);"

3250 else

3251 echo $msg

3252 fi

3253 else

3254 query "delete from grp_mem_s $cond and key='email';"

3255 fi

3256 if [ -n "$5" ]; then # as ADMIN

3257 # Coming here means newly created group

3258 sql="select case\

3259 when (select count(*) from grp_mem where gname=$qgname)=1\

3260 then (select user from grp_mem\

3261 where gname=$qgname and user='$user')\

3262 else '' end; "

3263 err NewGrpChk: $sql

3264 if [ -n "`query \"$sql\"`" ]; then

3265 ## err ADMIN: "replace into grp_adm values($qgname, '$user');"

3266 query "replace into grp_adm values($qgname, '$user');"

3267 fi

3268 fi

3269 else

3270 query "begin;

3271 delete from grp_mem $cond;

3272 delete from grp_mem_s $cond;

3273 delete from grp_mem_m $cond;

3274 delete from grp_adm $cond;

3275 delete from grp_adm_s $cond;

3276 delete from grp_adm_m $cond;

3277 end;"

3278 action="から脱退しました。"

3279 fi

3280 smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF

3281 $url?grp+$grid

3282 $user (`gecos $user`)さんが

3283 グループ $1

3284 $action

3285 EOF

3286 }

3287 grp_add_team() (

3288 # $1=grp-rowid $2=team $3...=user-rowid(s)

3289 grp=`getgroupbyid $1`

3290 team=$2; shift; shift

3291 [ -z "$grid" -o -z "$team" -o -z "$1" ] && return

3292 { echo "BEGIN;"

3293 for user; do

3294 echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\

3295 '$grp',\

3296 (SELECT name FROM user WHERE rowid=$user),\

3297 'team', 'string', '$team');"

3298 done

3299 echo "END;"

3300 } | query

3301 )

3302 grp_rm_team() (

3303 # $1=grp-rowid $2=team $3...=user-rowid(s)

3304 grid=$1

3305 qgrp=$(sqlquote "`getgroupbyid $grid`")

3306 team=$2; shift; shift

3307 [ -z "$grid" -o -z "$team" ] && return

3308 { echo "BEGIN;"

3309 for user; do

3310 echo "DELETE FROM grp_mem_m\

3311 WHERE gname=$qgrp \

3312 AND user=(SELECT name FROM user WHERE rowid=$user)\

3313 AND key='team' AND val='$team';"

3314 done

3315 cat<<-EOF

3316 DELETE FROM blog_s

3317 WHERE rowid=(

3318 SELECT rowid

3319 FROM blog_s a

3320 WHERE key='team'

3321 AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)

3322 AND NOT EXISTS (SELECT * FROM grp_mem_m

3323 WHERE key='team' AND val=a.val -- a.val=team

3324 AND gname = (SELECT val FROM blog_s b

3325 WHERE a.id=b.id AND key='owner')

3326 ));

3327 EOF

3328

3329 echo "END;"

3330 } | query

3331 )

3332 grp_reg_adm() {

3333 # $1=grp-rowid $2...=user-rowid

3334 grid=$1

3335 grp=`getgroupbyid "$1"`

3336 if [ -z "$grp" ]; then

3337 echo "無効なグループIDです" | html p; return

3338 fi

3339 if ! isgrpowner "$user" "$grp"; then

3340 echo "$grp グループの管理者しかこの操作はできません。" | html p; return

3341 fi

3342 shift

3343 for urid; do

3344 newadm=`query "select name from user where rowid=$urid;"`

3345 if [ -z "$newadm" ]; then

3346 echo "指定ユーザIDがおかしいようです。" | html p; return

3347 fi

3348 err GRP_reg_adm: "replace into grp_adm values(`sqlquote \"$grp\"`, '$newadm');"

3349 err ismember $newadm $grp

3350 if ismember $newadm "$grp"; then

3351 # OK, go ahead

3352 getgname="(select gname from grp where rowid=$grid)"

3353 query "replace into grp_adm values($getgname, '$newadm');"

3354 # confirm insertion

3355 sql="select * from grp_adm where gname=$getgname and user='$newadm'"

3356 if [ -n "`query \"$sql;\"`" ]; then

3357 echo "追加完了: $newadm" | html p

3358 else

3359 echo "追加失敗($1 $urid)" | html p

3360 fi

3361 fi

3362 showgroup $grid

3363 done

3364 }

3365 dt_rowhack() {

3366 # From: <TR>

3367 # ....

3368 # <TD>rowclass=foo</TD>

3369 # </TR>

3370 # To: <TR class="foo">....<TD>foo</TD></TR>

3371 sed -e '

3372 /^<TR>/ {

3373 :loop

3374 s/\n//

3375 N

3376 /<\/TR>/ {

3377 s/\n//

3378 s,^<TR>$.*$<TD>rowclass=$.*$$</TD></TR>$,<TR class="\2">\1<TD>\2\3,

3379 n

3380 }

3381 $q

3382 b loop

3383 }'

3384 }

3385 dumptable() {

3386 # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)

3387 # textのフィールドだけ全てダンプにしたほうがいいか

3388 # $DT_VIEW sets link

3389 # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?

3390 ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"

3391 VIEW=${DT_VIEW-replyblog}

3392 if [ -n "$VIEW" ]; then

3393 dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"

3394 fi

3395 sqlfile=$tmpd/dump.sql

3396 : > $sqlfile # ensure to be empty

3397 printf '.mode html\n.header 1\n' > $sqlfile

3398 # $DT_CHLD=ChildTable:BindColumn

3399 if [ -n "$DT_CHLD" ]; then

3400 _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}

3401 cat<<-EOF >> $sqlfile

3402 -- presql

3403 CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS

3404 SELECT * FROM acclog WHERE user='$user' and tbl='$2';

3405 EOF

3406 # Speed up counting of new articles

3407 cat<<-EOF >> $sqlfile

3408 -- presql2

3409 DROP TABLE IF EXISTS _counts;

3410 CREATE TEMPORARY TABLE _counts AS

3411 SELECT $_i, count($_i) cnt

3412 FROM $_t GROUP BY $_i;

3413 /* Prepare NEW count table */

3414 CREATE TEMPORARY TABLE _target AS

3415 SELECT b.rowid trowid, b.id

3416 FROM "$2" b JOIN "$2_s" s

3417 ON b.id=s.id AND s.key='owner'

3418 ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

3419

3420 DROP TABLE IF EXISTS _children;

3421 CREATE TEMPORARY TABLE _children AS

3422 SELECT a.trowid trowid, $_i, a.id, s.val ctime

3423 FROM (SELECT t.trowid, t.id $_i, a.id

3424 FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a

3425 LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

3426

3427 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3428 DROP TABLE IF EXISTS _news;

3429 DROP VIEW IF EXISTS _news;

3430

3431 -- CREATE TEMPORARY TABLE _news($_i, newcnt);

3432 -- INSERT INTO _news

3433 /* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */

3434 CREATE TEMPORARY VIEW _news AS

3435 SELECT a.id $_i, coalesce(newcnt, 0) newcnt

3436 FROM (SELECT DISTINCT id FROM _target)

3437 a LEFT JOIN

3438 (SELECT $_i, count(ctime) newcnt

3439 FROM _children x

3440 WHERE ctime > coalesce((SELECT time from myacclog

3441 WHERE tblrowid=x.trowid),

3442 '1970-01-01')

3443 GROUP BY $_i) b

3444 ON a.id=b.$_i;

3445 -- ${ddd:+.system gdate +%T.%3N >> tmp/d1}

3446 EOF

3447 # REMOVE next line until 2019/5/1

3448 cntall="(select count($_i) from $_t where $_i=a.id)"

3449 cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"

3450 # REMOVE next assignment until 2019/5/1

3451 cntnew="(select count(val) from ${_t}_s where key='ctime' \

3452 and id in (select id from $_t where $_i=a.id) \

3453 and val > coalesce((select time from myacclog where \

3454 tblrowid=a.rowid),\

3455 '1970-01-01'))"

3456 cntnew="(SELECT newcnt FROM _news where $_i=a.id)"

3457 cnt="$cntnew as '新着', $cntall as '総数',"

3458 dt_class=" td2r td3r dumpblogs"

3459 fi

3460 # Construct join expression

3461 eav="" scols=""

3462 pk=`gettblpkey $2`

3463 substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}

3464 substr=${substr:-%s}

3465 for col in ${3:-`gettbl_s_cols $2`}; do

3466 valvar=val

3467 case $col in

3468 gecos) scols="$scols${scols:+, }${col#}"

3469 continue ;; # built-in column name

3470 *:*) as=${col#*:} # as can be 稼動状態:frozen=凍結中

3471 col=${col%%:*} # stage:稼動状態:frozen=凍結中 -> stage

3472 case "$as" in

3473 *:*=*) cnd=${as#*:}

3474 h=${cnd%%=*} v=${cnd#*=}

3475 h=`sqlquotestr "$h"`

3476 v=`sqlquotestr "$v"`

3477 valvar="CASE val WHEN $h THEN $v END"

3478 as=${as%%:*} ;;

3479 esac

3480 ;;

3481 *) as=${col} ;;

3482 esac

3483 ss=`printf "$substr" "$valvar"`

3484 eav=$eav${eav:+,}" max(case key when '$col' then $ss end) as $as"

3485 scols="$scols${scols:+, }b.$as"

3486 done

3487 #case author when '$user' then a.rowid else '---' end as ID,

3488 if [ -n "$DT_SQL" ]; then

3489 echo "$DT_SQL"

3490 else

3491 cat<<-EOF

3492 SELECT a.rowid as LINK, $cnt $scols

3493 FROM $2 a LEFT JOIN

3494 (SELECT $pk,$eav,

3495 max(CASE key

3496 WHEN 'owner'

3497 THEN (SELECT gecos FROM gecoses WHERE name=val) END)

3498 as gecos

3499 FROM ${2}_s c GROUP BY $pk)

3500 b ON a.$pk=b.$pk $4;

3501 EOF

3502 fi >> $sqlfile

3503 ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"

3504 sqlog<<-EOF

3505 *** SQL-file: $sqlfile ***

3506 `cat $sqlfile`

3507 EOF

3508 if [ "$ddd" ]; then # REMOVE this block until 2019/7/1

3509 err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"

3510 cat $sqlfile >> tmp/sql

3511 # query ".read $sqlfile" > $tmpd/foo

3512 sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo

3513 cp $tmpd/foo tmp/

3514 err "----- `gdate +%FT%T.%3N` ------------555555"

3515 ## $ddd LINE exists at the end of this function

3516 fi

3517 printf '.mode list\n.header 0\n' >> $sqlfile

3518 cat<<EOF | sed "s,$<TR><TD>$$[1-9][0-9]*$$#[0-9a-fxs]*$*</TD>,\1$elink$dvlink</TD>," | dt_rowhack

3519 <div>

3520 <div class="dumptable">

3521 <table class="b$dt_class">

3522 `query ".read $sqlfile"`

3523 </table>

3524 </div>

3525 </div>

3526 EOF

3527 ### `query ".read $sqlfile"`

3528 ### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`

3529

3530 ## REMOVE THIS!

3531 [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"

3532 }

3533

3534 par2table() (

3535 # copy current parameters of par into destination table

3536 # $1=definition-file

3537 # Using $user and $session

3538 # Return value:

3539 # 0: Stored successfully

3540 # 1: Insufficient fillings

3541 # 2: No permission to modify the record

3542 # 3: Invalid rowid

3543 # 4: SUCCESS to delete

3544 # 5: Stop deletion for lack of confirm check

3545 # 6: Password length too short

3546 # 7: Password mismatch

3547 # 8: Old password incorrect

3548 rowid=`getpar rowid`

3549 if [ ! -e $1 ]; then

3550 echo "テーブル定義ファイルが見付かりません" | html p

3551 exit 1

3552 fi

3553 tbl=${1%.def}

3554 tbl=${tbl##*/}

3555 if [ -n "$rowid" ]; then # Modify existing entry

3556 if [ x"$tbl" = x"user" ]; then

3557 rowowner=`query "select name from $tbl where rowid=$rowid;"`

3558 elif [ x"$tbl" = x"grp" ]; then

3559 sql="select gname from $tbl where rowid=$rowid;"

3560 ##err p2t:grp:q $sql

3561 isgrpowner "$user" "`query $sql`" && rowowner=$user

3562 elif [ x"$tbl" = x"blog" ]; then

3563 # Check if owner in blog_s

3564 blogowner=`getvalbyid blog owner "$rowid"`

3565 if isgrpowner "$user" "$blogowner"; then

3566 rowowner=$user

3567 else

3568 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3569 fi

3570 else

3571 # 2016-12-05 There's no owner column in $tbl (need confirmation)

3572 rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`

3573 fi

3574 ### err rowowner=$rowowner

3575 if [ x"$user" != x"$rowowner" ]; then

3576 echo "他人のレコードはいじれないの" | html p

3577 return 2

3578 elif [ -z "$rowowner" ]; then

3579 echo "指定したレコードはないみたい" | html p

3580 return 3

3581 fi

3582 rm=`getpar rm` cfm=`getpar confirm`

3583 # Editing existent entry

3584 if [ x"$rm" = x"yes" ]; then

3585 if [ x"$rm$cfm" = x"yesyes" ]; then

3586 query "delete from $tbl where rowid=$rowid;"

3587 return 4

3588 else

3589 echo "消去確認のチェックがないので消さなかったの..." | html p

3590 return 5

3591 fi

3592 fi

3593 fi

3594

3595 ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""

3596 if [ -n "$rowid" ]; then

3597 # Update of existing record

3598 for col in `gettblcols $tbl`; do

3599 val=`getparquote $col`

3600 [ -z "$val" ] && continue

3601 ## err query "update $tbl set $col=$val where rowid=$rowid"

3602 ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s

3603 sql="update $tbl set $col=$val where rowid=$rowid;"

3604 if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \

3605 -o x"tbl" = x"user" -a x"$col" = x"name" ]; then

3606 ## User name cannot be changed with interface provided with this

3607 ## script. But we offer the trigger to change owner user

3608 ## of blog_s table.

3609 #err "select quote($col) from $tbl where rowid=$rowid;"

3610 old=`query "select quote($col) from $tbl where rowid=$rowid;"`

3611 cat<<-EOF | query

3612 -- Here we cannot use BEGIN-COMMIT because groupupdate()

3613 -- should use EXCLUSIVE transaction outside of this.

3614 SAVEPOINT par2table;

3615 $sql

3616 update blog_s set val=$val

3617 where key='owner' and val=$old;

3618 RELEASE SAVEPOINT par2table;

3619 EOF

3620 ## XX: DIRTY Hack Ends here

3621 ## We should keep blog's owner as a single column which has

3622 ## foreign key constraint with primary key of grp/user.

3623 else

3624 query "$sql"

3625 fi

3626 done

3627 # Then, set up $pval for further insertion of tbl_s and tbl_m

3628 for col in `gettblpkey $tbl`; do

3629 val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`

3630 pval="$pval${pval:+, }\"$val\""

3631 done

3632 else

3633 # New entry

3634 # Generate values() for primary keys

3635 for col in `gettblpkey $tbl`; do

3636 # Genuine primary keys for _m and _s

3637 val=`getvalquote $tbl $col`

3638 [ -z "$val" ] && continue

3639 pval="$pval${pval:+, }$val"

3640 done

3641 ##err pval=$pval

3642 for col in `gettblfkey $tbl`; do

3643 # args for values() to insertion into master table

3644 val=`getvalquote $tbl $col`

3645 [ -z "$val" ] && continue

3646 formaster=$formaster"${formaster:+, }$val"

3647 done

3648 formaster="$pval${formaster:+, }$formaster"

3649 ## err formaster=$formaster

3650 if [ -z "$formaster" ]; then

3651 echo "項目を全て埋めてください" | html pre

3652 return 1

3653 fi

3654 ## err "replace into $tbl values($formaster);"

3655 query "replace into $tbl values($formaster);"

3656 ## Insertion to master table, done

3657 fi

3658

3659 transaction=$tmpd/sqlfile.sql; touch $transaction

3660 for kt in s m; do

3661 tb2=${tbl}_$kt

3662 for col in `gettbl_${kt}_cols $tbl`; do

3663 ptype=`getpartype $col "limit 1"`

3664

3665 # First, check update of existing entries in _m

3666 if [ $kt = m ]; then

3667 # sessID|address.1.22|string|Somewhere-x.y.z

3668 sql=""

3669 ##err dots from query "select var from par where var like '$col.%';"

3670 for v in `query "select var from par where var like '$col.%';"`; do

3671 # v=address.1.22

3672 st_rowid=${v##*.}

3673 origcol=${v%%.*} # original column derived from

3674 ##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`

3675 ##case `getpartype $v` in

3676 ## err CASE `gettbl_coltype $tbl/$origcol` in

3677 ## err edit flag = `getpar action.$v`

3678 case `getpar action.$v` in

3679 rm)

3680 if [ x`getpar confirm.$v` = x"yes" ]; then

3681 newsql="delete from $tb2"

3682 else

3683 echo "削除確認未チェック" | html p

3684 fi ;;

3685 edit)

3686 case `gettbl_coltype $tbl/$origcol` in

3687 image|document|binary)

3688 file=$tmpd/`getparfilename $v`

3689 ## err type=file=$file

3690 [ -z "$file" ] && continue

3691 bn=`sqlquotestr "${file##*/}"`

3692 bin="X'"$(hexize "$file")"'"

3693 ct=`file --mime-type - < "$file" |cut -d' ' -f2`

3694 type=\"file:$ct\"

3695 newsql="update $tb2 set val=$bn, type=$type, bin=$bin"

3696 cachedir=`getcachedir "$tbl/$rowid"`

3697 err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir

3698 rm -rf $cachedir

3699 ;;

3700 *)

3701 newsql="update $tb2 set val=(select val from par where var \

3702 like '$col.%.$st_rowid')"

3703 ;;

3704 esac

3705 ;;

3706 *) # maybe "keep", do not modify value

3707 continue

3708 ;;

3709 esac

3710 # err newsql=$newsql

3711 sql=$sql$nl"$newsql where rowid=$st_rowid;"

3712 done

3713

3714 if [ x"$bin" = x"NULL" ]; then

3715 ## err repl:normal sql=`echo $sql`

3716 if [ -n "$transaction" ]; then

3717 cat<<-EOF >> $transaction

3718 $sql

3719 DELETE FROM $tb2 WHERE type='string' AND val='';

3720 EOF

3721 else

3722 query "$sql

3723 delete from $tb2 where type='string' and val='';"

3724 ## err repl:normal done

3725 fi

3726 else

3727 # Binary update line is TOO LONG to pipelining

3728 sqlfile="$tmpd/sqlf.$$"

3729 if [ -n "$transaction" ]; then

3730 echo "$sql" >> $transaction

3731 else

3732 echo "$sql" > $sqlfile

3733 query ".read $sqlfile"

3734 fi

3735 fi

3736 # Rest of kt==m: set multiple mode

3737 nr=`getparcount $col`

3738 else

3739 nr=1 # for kt==s, number of records is 1

3740 fi

3741

3742 i=0

3743 while [ $i -lt $nr ]; do

3744 limit="limit 1 offset $i"

3745 i=$((i+1)) # increase beforehand against continue

3746 val=`getvalquote $tbl $col "$limit"`

3747 [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue

3748 ## err $col=$val

3749 bin=NULL

3750 ## err partype$col=`getpartype $col "$limit"`

3751 case $ptype in

3752 file) file=$tmpd/`getparfilename $col "$limit"`

3753 ## err parfile-$col=$file

3754 [ -z "$file" ] && continue

3755 bin="X'"$(hexize "$file")"'"

3756 ct=`file --mime-type - < "$file"|cut -d' ' -f2`

3757 type=\"file:$ct\" ;;

3758 "*"*) continue ;; # foreign table

3759 *) type=\"string\" ;;

3760 esac

3761 case `gettbl_coltype $tbl/$col` in

3762 password) # special care for password

3763 # name={password,pswd1,pswd2}

3764 p1=`getpar pswd1 "$limit"`

3765 if [ -z "$p1" ]; then

3766 continue # SKIP password setting, if p1 is empty

3767 else

3768 pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`

3769 ## err pswd=$pswd

3770 if pwcheck "$pswd"; then

3771 if [ x"$p1" = x"$p2" ]; then

3772 case "$p1" in

3773 ??????????*) ;;

3774 *) echo "パスワードは10字以上にしてください。" | html p

3775 return 6;;

3776 esac

3777 val="\"`echo $p1|mypwhash`\""

3778 else

3779 echo "2つの新パスワード不一致" | html p

3780 return 7

3781 fi

3782 else

3783 echo "旧パスワード違います" | html p

3784 return 8

3785 fi

3786 fi

3787 ;;

3788 esac

3789 ## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"

3790 #query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3791 sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"

3792 if [ x"$bin" = x"NULL" ]; then

3793 ## err Normal-query: `echo $sql`

3794 if [ -n "$transaction" ]; then

3795 echo "$sql" >> $transaction

3796 else

3797 query "$sql"

3798 fi

3799 else

3800 sqlfile="$tmpd/query.$$"

3801 ## err sqlfile=`ls -lF $sqlfile`

3802 if [ -n "$transaction" ]; then

3803 echo "$sql" >> $transaction

3804 else

3805 echo "$sql" > $sqlfile

3806 query ".read $sqlfile"

3807 fi

3808 fi

3809 ## err p2t done

3810 done

3811 done

3812 done

3813 [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query

3814 -- We cannot use transaction here, because groupupdate may use it.

3815 SAVEPOINT pa2table_insert;

3816 .read $transaction

3817 RELEASE SAVEPOINT pa2table_insert;

3818 EOF

3819 return $?

3820 ##err donee

3821 )

3822 genform() {

3823 # $1 = form definition file

3824 # $2, $3 (optional)= table name and ROWID

3825 # If $GF_VIEWONLY set and nonNull, output values without form

3826 # If $GF_ARGS set, use it as content-strings in the form

3827 # If $GF_OWNER set, use it as value of name="owner"

3828 # If $GF_STAGE set, use it as value of name="stage"

3829 forms="" hiddens="" rowid=$3

3830 if [ ! -e "$1" ]; then

3831 echo "そのようなデータベースはないようです($2)。" | html p

3832 return

3833 elif [ -n "$2" ]; then

3834 rec=`query "select * from $2 where rowid='$rowid';"`

3835 if [ -z "$rec" ]; then

3836 pk=`gettblpkey $2`

3837 ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`

3838 rec=`query "select rowid from $2 where $pk='$rowid';"`

3839 rowid=$rec

3840 rec=$3

3841 fi

3842 if [ -z "$rec" ]; then

3843 echo "そんなレコードはないみたいね..." | html p

3844 return

3845 fi

3846 fi

3847 if [ -z "$GF_VIEWONLY" ]; then

3848 rm='<input id="rm" name="rm" type="checkbox"

3849 value="yes"><label for="rm">このエントリの削除</label>

3850 <span>ほんとうに消しますよ(確認)!

3851 <input name="confirm" type=checkbox value="yes">はい</span>'

3852 fi

3853 # Image Cache dir

3854 ## err genform: getcache=$2/$rowid

3855 td=`getcachedir "$2/$rowid"`

3856 while IFS=: read prompt name keytype type args; do

3857 [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)

3858 sp="${args:+ }"

3859 form="" val=""

3860 if [ -n "$rowid" ]; then

3861 # err genform2a: Seeking for "$2.$name, type=$type"

3862 rawval=`getvalbyid $2 $name $rowid $td`

3863 val=`echo "$rawval"|htmlescape`

3864 ## err genform3a: getvalbyid $2 $name $rowid $td

3865 ## err genform3b: val="[$val]" type="$type"

3866 fi

3867 if [ -n "$GF_VIEWONLY" ]; then

3868 is_hidden "$2" "$name" && continue

3869 fi

3870 case "$type" in

3871 text*)

3872 cgiform=cgi_multi_$type

3873 if [ -s $td/$name.count -a -n "$val" ]; then

3874 form=`$cgiform $name $td`

3875 val=$(echo "$val"|

3876 while read fn; do

3877 echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`

3878 </td></tr>$nl"

3879 done)

3880 val="<table>$nl$val$nl</table>"

3881 else

3882 #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"

3883 form=`cgi_$type $name "$rawval" "$args"`

3884 fi

3885 ;;

3886 [Rr][Aa][Dd][Ii][Oo])

3887 fh="<label><input type=\"radio\" name=\"$name\""

3888 form="`echo $args|sed -e \

3889 \"s,$[^ =][^=]*$=$[^= ][^= ]*$,$fh value=\\"\2\\">\1</label>,g\"`"

3890 ;;

3891 [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])

3892 form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"

3893 ;;

3894 [Ss][Ee][Ll][Ee][Cc][Tt])

3895 fh="<select name=\"$name\">$nl"

3896 form=$(for l in $args; do

3897 echo "<option value=\"${l#*=}\">${l%=*}</option>"

3898 done)

3899 if [ -n "$val" ]; then

3900 form=`echo $form|sed -e "s,$value=.$val.$,\\1 selected,"`

3901 fi

3902 form="$fh$form</select>"

3903 ;;

3904 [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)

3905 if [ -s $td/$name.count ]; then

3906 form=`cgi_multi_file $name $td "$args"`

3907 if [ -n "$val" ]; then

3908 hrfb="$myname?showattc+$2_m"

3909 val=$(echo "$rawval" \

3910 | while read fn; do

3911 data=`percenthex "$td/$fn"`

3912 #ct=`cat $td/$fn.content-type`

3913 ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`

3914 ri=`cat "$td/$fn.rowid"`

3915 ## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3

3916 #imgsrc="<img src=\"data:$ct,$data\">"

3917 #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"

3918 iconhref2 "$td/$fn" "$hrfb+$ri" ""

3919 done)

3920 fi

3921 else

3922 form="<input type=\"file\" name=\"$name\" $args>"

3923 if [ -n "$val" ]; then

3924 imgs=$(echo "$rawval"\

3925 |while read fn;do

3926 data=`percenthex "$td/$fn"`

3927 echo "<img src=\"data:image/png,$data\">$fn<br>"

3928 done)

3929 form=$form"<br>$imgs"

3930 val=$imgs # 2015-06-15

3931 else

3932 form="<input type=\"file\" name=\"$name\" $args>"

3933 fi

3934 fi

3935 ;;

3936 [Hh][Ii][Dd][Dd][Ee][Nn])

3937 if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then

3938 args="value=\"$GF_STAGE\""

3939 fi

3940 form="<input type=\"hidden\" name=\"$name\" $args>"

3941 prompt='' # Remove prompt

3942 ;;

3943 [Aa][Uu][Tt][Hh][Oo][Rr])

3944 [ -n "$GF_VIEWONLY" ] && continue

3945 form="<input type=\"hidden\" name=\"author\" value=\"$user\">"

3946 prompt="" ;;

3947 [Oo][Ww][Nn][Ee][Rr])

3948 [ -n "$GF_VIEWONLY" ] && continue

3949 val=${GF_OWNER:-$val}

3950 val=${val:-$user}

3951 form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"

3952 prompt="" ;;

3953 [Uu][Ss][Ee][Rr])

3954 # XXX: is null $user ok?

3955 #form="<input type=\"hidden\" name=\"user\" value=\"$user\">"

3956 [ -n "$GF_VIEWONLY" ] && continue

3957 form="$user"

3958 ;;

3959 [Pp]assword)

3960 [ -n "$GF_VIEWONLY" ] && continue

3961 form="`cgi_passwd`"

3962 val=""

3963 ;;

3964 [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])

3965 [ -n "$GF_VIEWONLY" ] && continue

3966 if [ -z "$rowid" ]; then

3967 val=`genserial`

3968 fi

3969 form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"

3970 prompt="" ;;

3971 [Ss][Ee][Ss][Ss][Ii][Oo][Nn])

3972 prompt=""

3973 ;;

3974 parent|path|blog*)

3975 prompt=""

3976 ;;

3977 "*"*)

3978 tail=$tail"``"

3979 continue ;;

3980 esac

3981 if [ -n "$prompt" ]; then

3982 if [ -n "${GF_VIEWONLY}" ]; then

3983 form=$val

3984 else

3985 :

3986 fi

3987 forms=$forms" <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"

3988 else

3989 hiddens=$hiddens$nl"$form"

3990 fi

3991 done < $1

3992 # enctype="multipart/form-data"

3993 cat<<EOF

3994 <form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">

3995 ${rowid:+$rm}

3996 <table class="b $2">

3997 $forms

3998 </table>$hiddens

3999 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}

4000 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}

4001 EOF

4002 if [ -z $GF_VIEWONLY ]; then

4003 cat<<EOF

4004 <input type="submit" name="sub" value="OK">

4005 <input type="reset" name="res" value="Reset">

4006 EOF

4007 fi

4008 cat<<EOF

4009 $GF_ARGS</form>

4010 $tail

4011 EOF

4012 }

4013 edittable() {

4014 # $1=form-def $2=table $3 rowid

4015 genform "$@"

4016 }

4017 viewtable() {

4018 GF_VIEWONLY=1 genform "$@"

4019 }

4020 showattc() {

4021 # $1=table_m $2=rowid &optional $3=RawFlag

4022 ## err \$1=$1 \$2=$2 \$3=$3

4023 if ! isfilereadable $user $1 $2; then

4024 contenttype; echo

4025 echo "このファイルは管理者のみしか見られません" | html p

4026 putfooter; exit

4027 fi

4028 idir=`umask 002; mktempd` || exit 1

4029 # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"

4030 bin=$idir/$myname-$$.bin

4031 sql="select quote(bin) from $1 where rowid='$2';"

4032 ## err showattc: sql: $sql

4033 sq $db "$sql" | unhexize > $bin

4034 tv=`query "select type||'//'||val from $1 where rowid='$2';"`

4035 type=${tv%//*} fn=${tv#*//}

4036 ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}

4037 ct=${type#file:}

4038 case $ct in # all text/* changed to text/plain

4039 text/*)

4040 charset=`nkf -g $bin|cut -d' ' -f1`

4041 case $charset in

4042 ASCII*) charset="" ;;

4043 esac

4044 if [ -z "$3" ]; then

4045 ct="text/html${charset:+; charset=$charset}"

4046 link="?showattc+$1+$2+raw"

4047 nkf -e $bin | htmlescape | nkf --oc="$charset" \

4048 | sed 's,^,<span></span>,' \

4049 | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \

4050 -D_LINK_="$link" \

4051 -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt

4052 exit $?

4053 fi

4054 ct="text/plain${charset:+; charset=$charset}"

4055 ;;

4056 esac

4057 contenttype "$ct"

4058 echo "Content-Disposition: filename=\"$fn\""

4059 echo "Content-Length: " `cat $bin | wc -c`; echo

4060 #echo "Content-Type: " ${type#file:}; echo

4061 cat $bin

4062 }

4063 #

4064 # Some default stupid handler on CGI values

4065 #

4066 default_storedb() {

4067 # ARG: $1=table-def-file

4068 # RET: $tbl=table-name, $col=mail-column, $cols=columns

4069 tbl=`basename $1`

4070 tbl=${tbl%.def}

4071 cols="`grep :text $1|cut -d: -f2`"

4072 col=`echo "$cols"|head -1`

4073 vcol=`getpar $col`

4074 err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol

4075 if [ -n "$vcol" ]; then

4076 par2table $1

4077 else

4078 return 2 # No insertion occurred

4079 fi

4080 }

4081

4082 default_view() { # $1=def-file

4083 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4084 ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \

4085 default_storedb "$@"

4086 query "select rowid from $tbl order by rowid desc;" \

4087 | while read rowid; do

4088 viewtable $1 $tbl $rowid

4089 done | _m4 -D_TITLE_="$tbl" \

4090 -D_FORM_="`genform $1`" \

4091 -D_DUMPTABLE_="syscmd(cat)" \

4092 $layout/html.m4.html $layout/form+dump.m4.html

4093 }

4094 default_viewtext() { # $1=def-file

4095 ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \

4096 default_storedb "$@"

4097 DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \

4098 | _m4 -D_TITLE_="$tbl" \

4099 -D_FORM_="`genform $1`" \

4100 -D_DUMPTABLE_="syscmd(cat)" \

4101 $layout/html.m4.html $layout/form+dump.m4.html

4102 }

4103 default_smail() {

4104 default_storedb "$@"

4105 if [ $? -eq 2 ]; then

4106 _m4 -D_TITLE_="入力" \

4107 -D_FORM_="`genform $1`" \

4108 -D_DUMPTABLE_="" \

4109 $layout/html.m4.html $layout/form+dump.m4.html

4110 return

4111 fi

4112 cond=""

4113 for pk in `gettblpkey $tbl`; do

4114 pv=$(sqlquote "$(getpar $pk)")

4115 cond="$cond${cond:+ and }$pk=$pv"

4116 done

4117 sql="select rowid from $tbl where $cond;"

4118 rowid=`query "$sql"`

4119 ## err smail1 - "$sql" "-> rowid=$rowid"

4120

4121 while IFS=: read prompt name keytype type args; do # Read from $1

4122 val=`getpar $name`

4123 if [ -n "$val" ]; then

4124 text="$text

4125 $prompt

4126 $name=$val

4127 ---------------------------------------------------------"

4128 fi

4129 case "$type" in

4130 image|document|file)

4131 fn="`getvalbyid $tbl $name $rowid $tmpd`"

4132 fns=$(echo "$fn"|while read fn; do

4133 err mv $tmpd/$fn.orig $tmpd/$fn

4134 mv $tmpd/$fn.orig $tmpd/$fn

4135 rm $tmpd/$fn.rowid # Remove cache flag

4136 ## err "`ls $tmpd/$fn`"

4137 echo $fn

4138 done)

4139 files="$files $fns"

4140 ;;

4141 esac

4142 done < $1

4143 ## err FILES=$files "`ls -lF $tmpd`"

4144 subj="from ${REMOTE_ADDR}"

4145 (echo "$url"

4146 echo "への書き込みがありました。"

4147 echo "------"

4148 echo "$text"

4149 ) | (cd $tmpd &&

4150 err LS="`ls -lF`" &&

4151 $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)

4152 _m4 -D_TITLE_="入力完了" $layout/html.m4.html

4153 echo "以下の内容で送信しました。" | html p

4154 viewtable $1 $tbl \

4155 `query "select rowid from $tbl order by rowid desc limit 1;"`

4156 echo "戻る" | html a "href=\"?\""

4157 }