--- a/s4-funcs.sh	Sat Jun 18 21:02:19 2016 +0859
+++ b/s4-funcs.sh	Mon Jun 20 00:02:52 2016 +0859
@@ -1134,10 +1134,13 @@
 }
 collectemail() (
   # Collect email addresses for group $1
+  # If $TEAM is set, filter by team name
+  # If $EXCEPT is set as username(s) delimited by comma,
+  # remove $EXCEPT from list: ....NOT IN ($EXCEPT)
   for e; do
     if isuser "$e"; then
-    em=`query "select val from user_m where name='$e' and key='email';"`
-    [ -n "$em" ] && echo "$em" || echo "$e"
+      em=`query "select val from user_m where name='$e' and key='email';"`
+      [ -n "$em" ] && echo "$em" || echo "$e"
     else
  #    sql="with recursive allmem as
  #        (select gname,val from grp_m where gname='$1'
@@ -1149,11 +1152,18 @@
  # where a.val in (select name from user) limit 10000;"
       # tmp tmp tmp tmp tmp tmp tmp
       qgrp=`sqlquote "$e"`
+      if [ -z "$TEAM" ]; then
+	gmem="grp_mem"
+      else
+	tm=`sqlquote "$TEAM"`
+	gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"
+      fi
+      ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}
       sql="select coalesce(s.val,um.val,g.user) from
-	 grp_mem g left join grp_mem_s s
+	 $gmem g left join grp_mem_s s
 	 on g.gname=s.gname and g.user=s.user and s.key='email'
 	 left join user_m um on g.user=um.name and um.key='email'
-	 where g.gname=$qgrp;"
+	 where g.gname=$qgrp $ex;"
       err CollectEmail: `echo "$sql"`
       query "$sql"
     fi
@@ -1244,8 +1254,9 @@
       $layout/login.m4.html
   return
 }
-newgrpchk() {
-  # Check if $1 is existing and 
+group_safename() {
+  # Convert $1 to safe group name
+  echo "$1" | tr -d '"'"'",
 }
 groupupdate() {
   gname=`getpar gname`
@@ -1253,7 +1264,7 @@
   err Enter:groupupdate
   if [ -n "$gname" ]; then
     # See ALSO same job in showgroup()
-    newgname=`echo "$gname"|tr -d '\"'"'"`
+    newgname=`group_safename "$gname"`
     err newgname=$newgname
     if [ x"$newgname" != x"$gname" ]; then
       err NewGNAME: gname=$newgname
@@ -1698,6 +1709,11 @@
 listgroup() {
   listentry group "$@"
 }
+hexteams() {	# $1=gname, $2(optional)=user
+  cond=${2:+" AND user='$2'"}
+  query "SELECT DISTINCT hex(val) FROM grp_mem_m
+	 WHERE gname='$1' AND key='team'$cond;"
+}
 showgroup() { # $1=group-rowid
 #   gname=`getpar gname`
 #   if [ -n "$gname" ]; then
@@ -1769,7 +1785,7 @@
 err ismember $user $grp
   ismember "$user" "$grp" && ismem='checked' || nomem='checked'
   # このグループでの加入アドレス
-  eml=`query "select val from grp_mem_s where gname='$2' and user='$user' \
+  eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \
 	and key='email';"`
 err EML: "select val from grp_mem_s where gname='$2' and user='$user' \
 	and key='email';"
@@ -1805,14 +1821,26 @@
 EOF
   cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"
   DT_CHLD=article:blogid \
-	 DT_VIEW=replyblog dumptable html blog 'ctime title heading' "$cond"
+	 DT_VIEW=replyblog dumptable html blog 'ctime team title heading' "$cond"
 
   getgname="(select gname from grp where rowid=$rowid)"
   c="group by b.name having b.name in (select user from grp_mem where gname=$getgname)"
   cm="?commission+$rowid"
   thumbxy=50x50 listmember "" "$c" \
       |sed -e "s|\(<br>\),not=\(.*\)|\1|"	# 間違って押しやすい
-  ##    |sed -e "s|\(<br>\),not=\(.*\)|\1<a href=\"$cm+\2\">管理者委託</a>|"
+  # team list
+  hexteams=`hexteams "$grp"`
+  if [ -n "$hexteams" ]; then
+    echo "チーム一覧" | html h2
+    echo '<div class="dumptable"><table class="b">'
+    sq $db -html -header<<-EOF
+	SELECT val TEAM,
+       	       group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')
+		MEMBERS
+	       FROM grp_mem_m WHERE gname='foo' AND key='team' GROUP BY val;
+	EOF
+    echo '</table></div>'
+  fi
 }
 grpaction() {			# $1=group-rowid
   err GRP_ACTION:IN
@@ -1896,16 +1924,38 @@
       fi
     elif [ x"$rm" = x"commission" ]; then
       grp_reg_adm $grid $usel
+    elif [ x"$rm" = x"addteam" ]; then
+      team=`getpar team|sed "s/'/''/g"`	# for single quotation
+      newteam=`echo "$team"|tr -d ,`
+      if [ x"$team" != x"$newteam" ]; then
+	echo "チーム名に使えない文字を除去しました" | html p
+	team=newteam
+      fi
+      if [ -z "$team" -o x"$team" = x"なし" ]; then
+	cat<<-EOF | html p
+	有効なチーム名を入力してください。
+	カンマだけ、「なし」という名前は使えません。
+	EOF
+	echo "有効なチーム名を入力してください。" | html p
+      else
+	grp_add_team $grid "$team" $usel
+      fi
+    elif [ x"$rm" = x"rmteam" ]; then
+      if [ x"yes" = x"`getpar teamconfirm`" ]; then
+	rmteam=`getpar rmteam|sed "s/'/''/g"`
+	if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\
+		gname='$grp' AND user='$user' AND key='team'\
+		AND val='$rmteam';\"`" ]; then
+	  grp_rm_team $grid "$rmteam" $usel
+	else
+	  echo "所属していないチームの除去操作はできません。"|html p
+	fi
+      else
+	echo "確認チェックなしなのでチーム除去しませんでした。"|html p
+      fi
     fi
   fi
   # New entry
-  sql="select u.rowid||','||j.* from user u
-	 join (select a.user as name,coalesce(b.val,a.user)
-		 from (select gname,user from grp_mem
-		 where gname=(select gname from grp where rowid=$1))
-	 a left join (select gname,user,val from grp_mem_s where key='email')
-	 b using(gname,user))
-	j using(name) order by u.name;"
   sql="select 	/* Ahh, ugly SQL, I wanna fix... */
 	 case
 	 when (select user from grp_adm where
@@ -1914,39 +1964,68 @@
 	      then 'k'
 	 else ''
 	 end || a.rowid||
-	 ','||coalesce(val,a.name) as name from
+	 ','||coalesce(val,a.name) as name,
+	 (SELECT group_concat(val, ',')
+	  FROM	 grp_mem_m
+	  WHERE	 gname='$grp' AND user=a.name AND key='team') as TEAM
+	 from
 	 (select rowid,name from user where name in
 		 (select user from grp_mem where
 		 gname=(select gname from grp where rowid=$grid)))
 	  a left join user_s on a.name=user_s.name and key='gecos'
 	order by coalesce(val,a.name);"
   err grpaction: `echo $sql`
-  b1='<label> <input type="checkbox" name="usel" value="'
-  ba='<label class="admin"><input type="checkbox" name="usel" value="'
-  b2='"> <span>' b3='</span></label>'
   tf=$tmpd/title.$$
-  echo "グループ[$grp]参加メンバーに対する操作" > $tf
+  echo "グループ[<a href=\"?grp+$grid\">$grp<a>]参加メンバーに対する操作" > $tf
   cmmsg="<div class=\"fold\">
-`cgi_radio rm commission id=\"cmadmin\"` <label
+`cgi_radio rm commission id=\"cmadmin\"`<label
  for=\"cmadmin\">下でチェックした人にグループ管理者委任</label>
 <div><p>このグループでの全権を付与します。信頼できる人に託してください。
 </p></div></div>"
   excmsg="<div class=\"fold\">
-`cgi_radio rm yes ` 下でチェックした人のグループ登録解除
+`cgi_radio rm yes `下でチェックした人のグループ登録解除
 <div>
 本当に消します! `cgi_checkbox confirm yes` 確認
 <p>この操作による通知は本人に行きません。
 あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
 </div>
 </div>"
+  # Get team list to which current user belongs into $hexteams
+  myhexteams=$(hexteams "$grp" "$user")
+  allhexteams=$(hexteams "$grp")
+  if [ -n "$hexteams" ]; then
+    rmteam="<div class=\"fold\">
+`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label
+for=\"cmrmteam\">下でチェックした人からチーム属性を除去する</label>
+<div>チーム属性:`cgi_select_h rmteam $myhexteams`
+を除去します: `cgi_checkbox teamconfirm yes` 確認
+<p>この操作による通知は本人に行きません。
+あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
+</div>
+</div>
+"
+  fi
+  b1='<label> <input type="checkbox" name="usel" value="'
+  ba='<label class="admin"><input type="checkbox" name="usel" value="'
+  #b2='"> <span>' b3='</span></label>'
+  #    	| sed  -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$b2\3$b3|" \
+  #	       -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$b2\3$b3|" \
+  lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'
   cgi_form grpaction<<EOF \
-      	| sed  -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$b2\3$b3|" \
-	       -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$b2\3$b3|" \
+      	| sed  -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$lnk|" \
+	       -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$lnk|" \
 	| _m4 -D_TITLE_="spaste(\`$tf')" \
 	      -D_SUBTITLE_="チェック後操作ボタン" \
 	      -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \
 	      $layout/form+dump.m4.html
 <div class="fold">
+`cgi_radio rm addteam 'id="cmteam"'`<label
+for="cmteam">下でチェックした人で同じチーム属性を与える</label>
+<div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`
+`cgi_datalist_h teams $allhexteams`
+</div></div>
+${rmteam}
+<div class="fold">
 `cgi_radio rm send id="sendmsg"`<label
  for="sendmsg">下でチェックした人にメッセージを送信する</label>
 <div>
@@ -1956,12 +2035,13 @@
 ${isowner:+$cmmsg$excmsg}
 <h4>$grp 参加者一覧</h4>
 <table>
-`sq $db -html "$sql"`
+`sq $db -header -html "$sql"`
 </table>
 `cgi_hidden grp $grid`
 EOF
 }
 crview4article() { # $1=rowid of blog, $2(optional)=extra SQL
+  # Create TEMPORARY VIEW
   query<<EOF
 CREATE TEMPORARY VIEW writeusers AS
  SELECT DISTINCT author FROM article
@@ -2032,22 +2112,45 @@
 	</div>
 	EOF
     fi
+    # end of isuser "$owner"
+  elif { hexteams=$(hexteams "$owner" )	# blog is of GROUP 
+	 [ -n "$hexteams" ];}; then
+    none="`echo なし|hexize`"
+    cat<<-EOF
+	<div class="fold">
+	`cgi_checkbox mv2team send id="mv2team"`<label
+	 for="mv2team">この話題を以下のチームのものにする</label>
+	<div><p>現在の所属チーム設定:
+	`query "SELECT
+           coalesce((SELECT val FROM blog_s
+            WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
+            AND key='team'),
+	   ':なし');"`</p>
+	<form action="?mvart" method="POST" enctype="multipart/form-data">
+	移動先チーム: `cgi_select_h mv2team $none $hexteams`
+	<p>`cgi_checkbox cfm yes`<label>確認</label></p>
+	`cgi_hidden blogrowid $rowid`<br>
+	`cgi_submit 移動`
+	`cgi_reset Reset`
+	</form></div></div>
+	EOF
   fi
 }
-mvart() {
+mvart() {	# move diary to some group or team
+		# or move blog of group to team which belong to the group
   blogrowid=`getpar blogrowid`
-  mv2grp=`getpar mv2grp`
   cfm=`getpar cfm`
   ##### echo move blog:$blogrowid to $mv2grp | html p
   blogrowid=${blogrowid%%[!A-Z0-9a-z_]*}	# Purify
-  mv2grp=${mv2grp%%[!A-Z0-9a-z_]*}		# Purify
   . ./s4-blog.sh
-  if [ -z "$blogrowid" -o -z "$mv2grp" ]; then
+  if [ -z "$blogrowid" ]; then
     echo "無効な指定です(mvart)。" | html p
     return
   elif [ x"$cfm" != x"yes" ]; then
     echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p
-  else				# OK to go
+  elif { mv2grp=`getpar mv2grp`
+         mv2grp=${mv2grp%%[!A-Z0-9a-z_]*}		# Purify
+	 [ -n "$mv2grp" ]; }; then
     crview4article $blogrowid
     ########## TRANSACTION BEGIN
     query "BEGIN;"
@@ -2063,6 +2166,29 @@
     fi
     query "END;"
     ########## TRANSACTION END
+  elif { mv2team=`getpar mv2team|sed "s/'/''/g"`
+	 [ -n "$mv2team" ];}; then
+    # blog owner can move it to ANY team
+    case "$mv2team" in
+      'なし')
+	cat<<-EOF
+	  DELETE FROM blog_s
+	  WHERE  id=(SELECT id FROM blog WHERE rowid=$blogrowid)
+		 AND key='team';
+	EOF
+	;;
+      "") ;;
+      *)cat<<-EOF
+	  BEGIN;
+	  REPLACE INTO blog_s(id, key, val)
+	  VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
+		 'team', '$mv2team');
+	  REPLACE INTO blog_s(id, key, val)
+	  VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
+		 'notify', 'all');	-- Change notify to all
+	  END;
+	EOF
+    esac | query
   fi
   blog_reply $blogrowid
   echo yes | html p
@@ -2187,8 +2313,14 @@
 }
 joingrp() {
   # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)
-err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4
-  isgrpowner "$user" "$1" && isowner="yes" || isowner=""
+  err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4
+  if isgrpowner "$user" "$1"; then
+    isowner="yes"
+  elif [ -n "$5" ]; then
+    isowner="yes"
+  else
+    isowner=""
+  fi
   err jg:isgrpowner: isowner="$isowner"
   if [ -n "$isowner" ]; then
     : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT
@@ -2242,6 +2374,36 @@
 delete from grp_mem_m $cond;"
   fi
 }
+grp_add_team() (
+  # $1=grp-rowid $2=team $3...=user-rowid(s)
+  grp=`getgroupbyid $1`
+  team=$2; shift; shift
+  [ -z "$grid" -o -z "$team" -o -z "$1" ] && return
+  { echo "BEGIN;"
+    for user; do
+      echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\
+		'$grp',\
+		(SELECT name FROM user WHERE rowid=$user),\
+		'team', 'string', '$team');"
+    done
+    echo "END;"
+  } | query
+)
+grp_rm_team() (
+  # $1=grp-rowid $2=team $3...=user-rowid(s)
+  grp=`getgroupbyid $1`
+  team=$2; shift; shift
+  [ -z "$grid" -o -z "$team" -o -z "$1" ] && return
+  { echo "BEGIN;"
+    for user; do
+      echo "DELETE FROM grp_mem_m\
+	    WHERE gname='$grp'\
+	    AND user=(SELECT name FROM user WHERE rowid=$user)\
+	    AND key='team' AND val='$team';"
+    done
+    echo "END;"
+  } | query
+)
 grp_reg_adm() {
   # $1=grp-rowid $2...=user-rowid
   grid=$1