messageCenter/service/message_mail_box.go

package service

import (
	quitl "app.yhyue.com/moapp/jybase/common"
	util "bp.jydev.jianyu360.cn/SocialPlatform/messageCenter/entity"
	. "bp.jydev.jianyu360.cn/SocialPlatform/messageCenter/rpc/messagecenter/init"
	"bp.jydev.jianyu360.cn/SocialPlatform/messageCenter/rpc/messagecenter/messagecenter"
	"database/sql"
	"fmt"
	"log"
	"time"
)

type MessaggeService struct{}

func (b MessaggeService) Table() string {
	return "socialize_message_mailbox"
}
func (b MessaggeService) Count(newUserId, userType, entUserId int64) (count int, err error) {
	v := make([]interface{}, 0)
	sqlStr := "select count(id) from socialize_message_mailbox where own_id=? and  isread=0"
	if userType == 1 {
		sqlStr = "select  count(b.id) from   socialize_chat_session a  left  join  socialize_message_mailbox  b ON a.id=b.own_id  where  b.type=5 and   a.customer_service_id=?   and  b.isread=0  and own_type = 1 order by create_time"
		v = append(v, entUserId)
	} else {
		sqlStr = "select  count(b.id) from    socialize_message_mailbox  b  where      b.own_id=?  and  b.isread=0 and own_type = 2 order by create_time"
		v = append(v, newUserId)
	}
	fmt.Println(sqlStr, v)
	rs := Mysql.CountBySql(sqlStr, v...)
	log.Println(rs)
	count = int(rs)
	return
}
func (b MessaggeService) UserList(in *messagecenter.UserReq) (data *[]map[string]interface{}, err error) {
	v := make([]interface{}, 0)
	sqlStr := ""
	if in.UserType == 2 {
		//用户最后一次信息查询
		userSql := "SELECT  MAX( c.id )   as messageId  FROM  socialize_message_mailbox c  WHERE  c.own_id = ?   AND c.type = 2   AND c.own_type = 2  GROUP BY  (  CASE        WHEN c.send_user_id > c.receive_user_id THEN    CONCAT( c.send_user_id, c.receive_user_id )     WHEN c.send_user_id < c.receive_user_id THEN    CONCAT( c.receive_user_id, c.send_user_id )    END    )  "
		data = Mysql.SelectBySql(userSql, in.NewUserId)
		userMessageId := Inhandle(data)
		//客服最后一次信息查询
		customerSql := " SELECT   max( c.id )   as messageId  FROM   socialize_message_mailbox c   LEFT JOIN socialize_chat_session d ON  IF   ( c.send_user_type = 1, d.id = c.send_user_id, d.id = c.receive_user_id )   WHERE   c.own_id = ?    AND ( c.type = 4 OR c.type = 5  )    AND c.own_type = 2  and   d.user_id=c.own_id  GROUP BY   d.ent_id  "
		data = Mysql.SelectBySql(customerSql, in.NewUserId)
		customerMessageId := Inhandle(data)
		//用户的列表
		sqlStr = "SELECT IF ( b.nickname = '', REPLACE(b.phone,SUBSTR(b.phone,4,4),'****'), b.nickname ) AS name, b.id, e.title, b.headimg, e.type, e.link, e.content, 2 AS userType, a.create_time, a.type AS itemType, ( SELECT  count( h.id )  FROM  socialize_message_mailbox h  WHERE  h.type = 2   AND h.own_id = ?   AND h.own_type = 2   AND  h.receive_user_id = ? and   h.send_user_id=b.id   AND h.isread = 0  ) AS number  FROM socialize_message_mailbox a LEFT JOIN base_user b ON  b.id = a.receive_user_id or  b.id = a.send_user_id  LEFT JOIN socialize_message e ON e.id = a.messag_id  WHERE a.id IN ( " + userMessageId + " )   AND b.id != ? UNION ALL SELECT  f.nickname AS name,    b.ent_id AS id,  e.title,f.headimage AS headimg,  e.type,  e.link,  e.content,  1 AS userType,  a.create_time,  a.type AS itemType,  (  SELECT   count( h.id )   FROM   socialize_message_mailbox h   WHERE   ( h.type = 4 OR h.type = 5  )    AND h.own_id = ?    AND h.own_type = 2   AND  h.send_user_type = 1 and  h.receive_user_id = ?    AND h.isread = 0   ) AS number  FROM  socialize_message_mailbox a  LEFT JOIN socialize_chat_session b ON IF  ( a.send_user_type = 1, b.id = a.send_user_id, b.id = a.receive_user_id )  LEFT JOIN socialize_message e ON e.id = a.messag_id  left join socialize_tenant_robot  f on f.ent_id=b.ent_id   WHERE  a.id IN (  " + customerMessageId + " )  ORDER BY create_time DESC"
		v = append(v, in.NewUserId, in.NewUserId, in.NewUserId, in.NewUserId, in.NewUserId)
	} else {
		phoneSql := ""
		if in.Phone != "" {
			phoneSql = "and  b.phone='" + in.Phone + "'"
		}
		startTimeSql := ""
		if in.StartTime != "" {
			startTimeSql = " and  DATE_FORMAT(c.create_time,'%Y-%m-%d') >= '" + in.StartTime + "' "
		}
		endTimeSql := ""
		if in.EndTime != "" {
			endTimeSql = " and DATE_FORMAT(c.create_time,'%Y-%m-%d') <= '" + in.EndTime + "' "
		}

		//先获取每个用户最后聊天记录
		userSql := " SELECT   MAX( c.id ) as messageId   FROM   socialize_message_mailbox c   LEFT JOIN socialize_chat_session d ON  IF   ( c.send_user_type = 1, d.id = c.send_user_id, d.id = c.receive_user_id )   WHERE   c.own_type = 1    AND d.customer_service_id = ?   and ( c.type = 4 OR c.type = 5 )   AND d.customer_service_id !=0    " + startTimeSql + endTimeSql + "   GROUP BY   (   CASE          WHEN  c.send_user_type =2 THEN  CONCAT( c.send_user_id )   WHEN c.send_user_type =1 THEN  CONCAT( c.receive_user_id )     END     ) "
		v = append(v, in.EntUserId)
		log.Println(userSql, v)
		data = Mysql.SelectBySql(userSql, v...)
		if data != nil {
			customerMessageId := Inhandle(data)
			//客服的用户列表
			sqlStr = "SELECT  IF  ( b.nickname = '', REPLACE(b.phone,SUBSTR(b.phone,4,4),'****'), b.nickname ) AS name,  b.id,  e.title,  b.headimg,  e.type,  e.link,  e.content,  a.create_time,  (  SELECT   count( h.id )   FROM   socialize_message_mailbox h   WHERE   ( h.type = 4 OR h.type = 5 )    AND h.own_type = 1   AND  IF   ( h.send_user_type = 1, h.send_user_id = f.id, h.receive_user_id = f.id )    AND h.isread = 0   ) AS number   FROM  socialize_message_mailbox a  LEFT JOIN base_user b ON  IF  ( a.send_user_type = 1, b.id = a.receive_user_id, b.id = a.send_user_id )  LEFT JOIN socialize_message e ON e.id = a.messag_id  LEFT JOIN socialize_chat_session f ON  IF  ( a.send_user_type = 1, f.id = a.send_user_id, f.id = a.receive_user_id )   WHERE  a.id IN  (" + customerMessageId + ")" + phoneSql + "   ORDER BY  a.create_time DESC"
			fmt.Println(in.EntUserId)
			v = make([]interface{}, 0)
		}

	}
	if sqlStr != "" {
		fmt.Println(sqlStr, v)
		data = Mysql.SelectBySql(sqlStr, v...)
	}
	return
}
func (b MessaggeService) SaveMessage(in *messagecenter.MessageEntity) (errorCode int64) {
	//先插入信息表
	fool := Mysql.ExecTx("聊天信息保存", func(tx *sql.Tx) bool {
		//先插入信息表
		message := map[string]interface{}{
			"appid":         in.Appid,
			"title":         in.Title,
			"content":       in.Content,
			"item":          in.ItemType,
			"type":          in.Type,
			"link":          in.Link,
			"create_time":   time.Now().Local().Format(util.Date_Full_Layout),
			"create_person": in.SendId,
		}
		ok := Mysql.InsertByTx(tx, "socialize_message", message)
		if ok <= 0 {
			return false
		}
		//在插入邮箱表socialize_message_mailbox
		messageMailBox := map[string]interface{}{
			"appid":         in.Appid,
			"messag_id":     ok,
			"type":          in.ItemType,
			"create_time":   time.Now().Local().Format(util.Date_Full_Layout),
			"isread":        0,
			"send_isdel":    0,
			"receive_isdel": 0,
			"iswithdraw":    0,
		}
		//系统信息处理
		if in.ItemType == 6 {
			messageMailBox = map[string]interface{}{
				"appid":         in.Appid,
				"messag_id":     ok,
				"type":          in.ItemType,
				"create_time":   time.Now().Local().Format(util.Date_Full_Layout),
				"read_time":     time.Now().Local().Format(util.Date_Full_Layout),
				"isread":        1,
				"send_isdel":    0,
				"receive_isdel": 0,
				"iswithdraw":    0,
			}
			if in.OwnType == 1 {
				//用户接受系统信息
				messageMailBox["own_type"] = 2
				messageMailBox["send_user_type"] = 1
				messageMailBox["receive_user_type"] = 2
				messageMailBox["own_id"] = in.NewUserId
				messageMailBox["send_user_id"] = in.ReceiveId
				messageMailBox["receive_user_id"] = in.NewUserId

			} else {
				//客服接受系统信息
				messageMailBox["own_type"] = 1
				messageMailBox["send_user_type"] = 2
				messageMailBox["receive_user_type"] = 1
				messageMailBox["own_id"] = in.SendId
				messageMailBox["send_user_id"] = in.ReceiveId
				messageMailBox["receive_user_id"] = in.SendId
			}
			receiveOk := Mysql.InsertByTx(tx, "socialize_message_mailbox", messageMailBox)
			if receiveOk == 0 {
				return false
			} else {
				return true
			}
		}

		if in.ItemType == 4 || in.ItemType == 5 {
			//客服或者机器人聊天
			if in.OwnType == 1 {
				// (用户发送)客服接受
				messageMailBox["own_type"] = 1
				messageMailBox["send_user_type"] = 2
				messageMailBox["receive_user_type"] = 1
				messageMailBox["own_id"] = in.ReceiveId
				messageMailBox["send_user_id"] = in.NewUserId
				messageMailBox["receive_user_id"] = in.ReceiveId
			} else {
				//客服发送(用户接收信息)
				messageMailBox["own_type"] = 2
				messageMailBox["send_user_type"] = 1
				messageMailBox["receive_user_type"] = 2
				messageMailBox["own_id"] = in.ReceiveId
				messageMailBox["send_user_id"] = in.SendId
				messageMailBox["receive_user_id"] = in.ReceiveId
				if in.ItemType == 4 {
					messageMailBox["receive_user_id"] = in.NewUserId
					messageMailBox["own_id"] = in.NewUserId
				}
			}
		} else {
			messageMailBox["own_type"] = 2
			messageMailBox["send_user_type"] = 2
			messageMailBox["receive_user_type"] = 2
			messageMailBox["own_id"] = in.ReceiveId
			messageMailBox["send_user_id"] = in.NewUserId
			messageMailBox["receive_user_id"] = in.ReceiveId
		}
		sendOk := Mysql.InsertByTx(tx, "socialize_message_mailbox", messageMailBox)
		if sendOk == 0 {
			return false
		}
		messageMailBox = map[string]interface{}{
			"appid":         in.Appid,
			"messag_id":     ok,
			"type":          in.ItemType,
			"create_time":   time.Now().Local().Format(util.Date_Full_Layout),
			"read_time":     time.Now().Local().Format(util.Date_Full_Layout),
			"isread":        1,
			"send_isdel":    0,
			"receive_isdel": 0,
			"iswithdraw":    0,
		}
		if in.ItemType == 4 || in.ItemType == 5 {
			//客服或者机器人聊天
			if in.OwnType == 1 {
				//用户发送(用户接受)
				messageMailBox["own_type"] = 2
				messageMailBox["send_user_type"] = 2
				messageMailBox["receive_user_type"] = 1
				messageMailBox["own_id"] = in.NewUserId
				messageMailBox["send_user_id"] = in.NewUserId
				messageMailBox["receive_user_id"] = in.ReceiveId

			} else {
				//客服发送信息(用户接受)
				messageMailBox["own_type"] = 1
				messageMailBox["send_user_type"] = 1
				messageMailBox["receive_user_type"] = 2
				messageMailBox["own_id"] = in.SendId
				messageMailBox["send_user_id"] = in.SendId
				messageMailBox["receive_user_id"] = in.ReceiveId
				if in.ItemType == 4 {
					messageMailBox["receive_user_id"] = in.NewUserId
				}
			}
		} else {
			messageMailBox["own_type"] = 2
			messageMailBox["send_user_type"] = 2
			messageMailBox["receive_user_type"] = 2
			messageMailBox["own_id"] = in.NewUserId
			messageMailBox["send_user_id"] = in.NewUserId
			messageMailBox["receive_user_id"] = in.ReceiveId
		}
		receiveOk := Mysql.InsertByTx(tx, "socialize_message_mailbox", messageMailBox)
		if receiveOk == 0 {
			return false
		}
		return true
	})
	if fool {
		return 0
	} else {
		return 1
	}
}
func (b MessaggeService) FindMessage(in *messagecenter.MessageReq) (*[]map[string]interface{}, int64) {
	sqlStr := ""
	countSql := ""
	v := make([]interface{}, 0)
	countV := make([]interface{}, 0)
	lastStr := "  "
	if in.LastId > 0 {
		lastStr = " and  a.messag_id < " + quitl.InterfaceToStr(in.LastId)
	}
	switch in.MsgType {
	case 2: //点对点聊天
		sqlStr = "SELECT     a.messag_id as messageId,b.* ,if(a.own_id = a.send_user_id,1,2) as fool     FROM     socialize_message_mailbox a     LEFT JOIN socialize_message b on  a.messag_id=b.id     left  join  base_user c on  c.id=a.send_user_id      left join  base_user d on  d.id=a.receive_user_id     where  a.own_id=? and ((a.send_user_id=? and   a.receive_user_id=?) or (a.send_user_id=? and  a.receive_user_id=?)) and  a.type=2 " + lastStr + "  ORDER BY a.create_time desc ,a.id   asc limit ? , ? "
		countSql = "SELECT     count(b.id)     FROM     socialize_message_mailbox a     LEFT JOIN socialize_message b on  a.messag_id=b.id     left  join  base_user c on  c.id=a.send_user_id      left join  base_user d on  d.id=a.receive_user_id     where  a.own_id=? and ((a.send_user_id=? and   a.receive_user_id=?) or (a.send_user_id=? and  a.receive_user_id=?)) and  a.type=2 " + lastStr
		v = append(v, in.NewUserId, in.NewUserId, in.SendId, in.SendId, in.NewUserId, 0, in.PageSize)
		countV = append(countV, in.NewUserId, in.NewUserId, in.SendId, in.SendId, in.NewUserId)
		break
	case 4, 5, 6, 7: //客服聊天
		if in.UserType == 1 {
			//客服聊天记录查看
			sqlStr = "SELECT   a.messag_id as messageId,b.*,   IF   ( a.own_id = a.send_user_id, 1, 2 ) AS fool,   a.send_user_type,   a.type AS itemType,   d.nickname AS robotName,   d.headimage AS robotImg,   c.customer_service_name AS setName ,   c.user_id   FROM   socialize_message_mailbox a   LEFT JOIN socialize_message b ON a.messag_id = b.id   LEFT JOIN socialize_chat_session c ON   IF   ( a.send_user_type = 1, c.id = a.send_user_id, c.id = a.receive_user_id )   LEFT JOIN socialize_tenant_robot d ON c.ent_id = d.ent_id      WHERE   a.own_type = 1    AND c.ent_id = ?    AND c.user_id = ?    AND (a.type = 5 or a.type=4 or  a.type=6 or  a.type=7)  " + lastStr + "  ORDER BY a.create_time desc ,a.id   asc limit ? , ? "
			v = append(v, in.EntId, in.SendId, 0, in.PageSize)
			countSql = "SELECT  count(b.id)  FROM   socialize_message_mailbox a   LEFT JOIN socialize_message b ON a.messag_id = b.id   LEFT JOIN socialize_chat_session c ON   IF   ( a.send_user_type = 1, c.id = a.send_user_id, c.id = a.receive_user_id )   LEFT JOIN socialize_tenant_robot d ON c.ent_id = d.ent_id       WHERE   a.own_type = 1    AND c.ent_id = ?    AND c.user_id = ?    AND (a.type = 5 or a.type=4)  " + lastStr
			countV = append(countV, in.EntId, in.SendId)
		} else {
			//用户聊天记录查看
			sqlStr = "SELECT a.messag_id as messageId,  b.*,   IF   ( a.own_id = a.send_user_id, 1, 2 ) AS fool ,   a.send_user_type,   a.type as  itemType,   d.nickname as  robotName,   d.headimage as  robotImg,   c.customer_service_name as  setName   FROM   socialize_message_mailbox a   LEFT JOIN socialize_message b ON a.messag_id = b.id   LEFT JOIN socialize_chat_session c ON   IF   ( a.send_user_type = 1, c.id = a.send_user_id, c.id = a.receive_user_id )    left join  socialize_tenant_robot d on  c.ent_id=d.ent_id    WHERE   a.own_type = 2    AND c.ent_id =?    AND a.own_id =?    AND ( a.type = 4 OR a.type = 5 or a.type=6 or  a.type=7) " + lastStr + "    ORDER BY a.create_time desc ,a.id   asc limit ? , ? "
			v = append(v, in.SendId, in.NewUserId, 0, in.PageSize)
			countSql = "SELECT   count(a.id)  FROM   socialize_message_mailbox a   LEFT JOIN socialize_message b ON a.messag_id = b.id   LEFT JOIN socialize_chat_session c ON   IF   ( a.send_user_type = 1, c.id = a.send_user_id, c.id = a.receive_user_id )    left join  socialize_tenant_robot d on  c.ent_id=d.ent_id      WHERE   a.own_type = 2    AND c.ent_id =?    AND a.own_id =?    AND ( a.type = 4 OR a.type = 5  or a.type=6 or  a.type=7) " + lastStr
			countV = append(countV, in.SendId, in.NewUserId)
		}
		break
	}
	fmt.Println(sqlStr, v)
	fmt.Println(countSql, countV)
	data := Mysql.SelectBySql(sqlStr, v...)
	count := Mysql.CountBySql(countSql, countV...)
	//自己头像处理
	if in.UserType == 2 && count > 0 {
		userData := Mysql.FindOne("base_user", map[string]interface{}{"id": in.NewUserId}, "headimg", "")
		if userData != nil {
			for key := range *data {
				(*data)[key]["ownImg"] = (*userData)["headimg"]
			}
		}

	}
	updateMap := map[string]interface{}{}
	if len(*data) > 0 {
		//未读信息修改
		switch in.MsgType {
		case 2: //点对点聊天
			updateMap = map[string]interface{}{
				"own_type": 2,
				"own_id":   in.NewUserId,
				"type":     2,
				"isread":   0,
			}
			Mysql.Update("socialize_message_mailbox", updateMap, map[string]interface{}{"isread": 1, "read_time": time.Now().Local().Format(util.Date_Full_Layout)})
			break
		case 4, 5:
			if in.UserType == 1 {
				Mysql.UpdateOrDeleteBySql("UPDATE socialize_message_mailbox a     SET a.isread = 1,    a.read_time = now( )     WHERE    a.own_type = 1     AND a.type IN ( 4, 5,6,7)     AND a.isread = 0    AND a.own_id IN ( SELECT b.id FROM socialize_chat_session b WHERE   b.customer_service_id=? and    b.user_id=?  )", in.EntUserId, in.SendId)
			} else {
				Mysql.UpdateOrDeleteBySql("UPDATE socialize_message_mailbox a     SET a.isread = 1,    a.read_time = now( )     WHERE    a.own_type = 2     AND a.type IN ( 4, 5,6,7 )     AND a.isread = 0    AND a.own_id =?", in.NewUserId)
			}
			break
		}
	}
	return data, count
}
func (b MessaggeService) CreateChatSession(in *messagecenter.ChatSessionReq) (errorCode, sessionId int64) {
	fool := Mysql.ExecTx("会话新建", func(tx *sql.Tx) bool {
		chatMession := map[string]interface{}{
			"handle_status":         0,
			"start_time":            time.Now().Local().Format(util.Date_Full_Layout),
			"appid":                 in.AppId,
			"ent_id":                in.EntId,
			"customer_service_id":   in.CustomerServiceId,
			"user_id":               in.UserId,
			"customer_service_name": in.CustomerserviceName,
		}
		sessionId = Mysql.InsertByTx(tx, "socialize_chat_session", chatMession)
		return sessionId > 0
	})
	if fool {
		return 0, sessionId
	} else {
		return 1, sessionId
	}
}
func (b MessaggeService) CloseChatSession(in *messagecenter.CloseSessionReq) (errorCode int64) {
	fool := Mysql.ExecTx("关闭会话", func(tx *sql.Tx) bool {
		updateMap := map[string]interface{}{
			"id": in.SessionId,
		}
		fool := Mysql.Update("socialize_chat_session", updateMap, map[string]interface{}{"start_time": time.Now().Local().Format(util.Date_Full_Layout)})
		return fool
	})
	if fool {
		return 0
	} else {
		return 1
	}
}

//in数据处理
func Inhandle(data *[]map[string]interface{}) (messId string) {
	if len(*data) == 0 {
		messId = "''"
		return
	}
	for k, m := range *data {
		if k > 0 {
			messId += `,`
		}
		messId += "'" + quitl.InterfaceToStr(m["messageId"]) + "'"
	}
	return
}

func (m *MessaggeService) SaveAutoReplyMsg(userType, entId, entUserId, userId int64, content, appId, nowFormat string) bool {
	return Mysql.ExecTx("保存自动回复消息", func(tx *sql.Tx) bool {
		entUserName := ""
		if entUserId > 0 {
			list := Mysql.SelectBySql(`select customer_service_name from socialize_tenant_seat where appid=? and ent_id=? and customer_service_id=?`, appId, entId, entUserId)
			if list != nil && len(*list) > 0 {
				entUserName, _ = (*list)[0]["customer_service_name"].(string)
			}
		}
		messageId := Mysql.InsertBySqlByTx(tx, `insert into socialize_message (appid,content,item,type,create_time,create_person) values (?,?,?,?,?,?)`, appId, content, 8, 1, nowFormat, "admin")
		sessionId := Mysql.InsertBySqlByTx(tx, `insert into socialize_chat_session (appid,type,ent_id,customer_service_id,customer_service_name,user_id,start_time,end_time) values (?,?,?,?,?,?,?,?)`, appId, 1, entId, entUserId, entUserName, userId, nowFormat, nowFormat)
		ok := false
		if userType == 1 {
			if Mysql.InsertBySqlByTx(tx, `insert into socialize_message_mailbox (appid,messag_id,type,send_user_id,send_user_type,receive_user_id,receive_user_type,own_id,own_type,create_time) values (?,?,?,?,?,?,?,?,?,?)`, appId, messageId, 7, sessionId, 1, userId, 2, sessionId, 1, nowFormat) > 0 {
				ok = true
			}
		} else if userType == 2 {
			if Mysql.InsertBySqlByTx(tx, `insert into socialize_message_mailbox (appid,messag_id,type,send_user_id,send_user_type,receive_user_id,receive_user_type,own_id,own_type,create_time) values (?,?,?,?,?,?,?,?,?,?)`, appId, messageId, 7, sessionId, 1, userId, 2, userId, 2, nowFormat) > 0 {
				ok = true
			}
		}
		return messageId > 0 && sessionId > 0 && ok
	})
}