gobackend-qingma/service/groupblood_service.go

package service

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"sort"
	"strings"
	"time"

	"gopkg.in/guregu/null.v3"

	"xiaoniaokuaiyan.com/xiaoniao/config"
	"xiaoniaokuaiyan.com/xiaoniao/entity"
	"xiaoniaokuaiyan.com/xiaoniao/util"
)

type GroupBloodService struct {
	*WeixinService
}

// 获取用户集采信息
func (srv *GroupBloodService) GetGroupBloodInfo(cid int) (interface{}, error) {
	strSql := `select id, name, start_time,end_time from (
		select jicai_id from t_jicai_detail where custom_id = ? ) t1 inner join t_jicai t2 on t1.jicai_id = t2.id where t2.start_time < ? and t2.end_time > ? limit 1`
	db := util.GetSqlDB()
	var baseInfo = []struct {
		GroupId   int    `db:"id" json:"jicai_id"`
		Name      string `db:"name" json:"jicai_name"`
		StartTime string `db:"start_time" json:"start_time"`
		EndTime   string `db:"end_time" json:"end_time"`
	}{}
	var curTime = time.Now().Format("2006-01-02 15:04:05")
	err := db.Select(&baseInfo, strSql, cid, curTime, curTime)
	if err != nil {
		return nil, err
	}

	if len(baseInfo) == 0 {
		return nil, errors.New("1::group-blood info not found")
	}

	strSql = `select name, gender, age, birthday, mobile, address, detail_address from t_jicai_detail t1 left join t_order t2 on t1.order_id = t2.id where t1.jicai_id = ? and t2.custom_id = ?`
	var customInfo = struct {
		Name          string `db:"name" json:"custom_name"`
		Gender        int    `db:"gender" json:"gender"`
		Age           int    `db:"age" json:"age"`
		Birthday      string `db:"birthday" json:"birthday"`
		Mobile        string `db:"mobile" json:"mobile"`
		Address       string `db:"address" json:"address"`
		DetailAddress string `db:"detail_address" json:"detail_address"`
	}{}
	err = db.Get(&customInfo, strSql, baseInfo[0].GroupId, cid)
	if err != nil {
		return nil, err
	}

	strSql = `select t1.id, t1.subject_name, t1.subject_en, queue_len, cur_queue_no, queue_ing_no, queue_no, status
	     from (select * from t_jicai_subject where jicai_id = ?) t1 left join (select * from t_jicai_subject_queue where custom_id = ? and status in(1,2)) t2 on t1.id = t2.subject_id order by status asc`

	var queueInfo = []*struct {
		SubjectId      int      `db:"id" json:"subject_id"`
		SubjectName    string   `db:"subject_name" json:"subject_name"`
		SubjectEn      string   `db:"subject_en" json:"subject_en"`
		QueueLen       int      `db:"queue_len" json:"queue_len"`
		CurrentQueueNo int      `db:"cur_queue_no" json:"cur_queue_no"`
		QueueIngNo     int      `db:"queue_ing_no" json:"queue_ing_no"`
		QueueNo        null.Int `db:"queue_no" json:"queue_no"`
		Status         null.Int `db:"status" json:"status"`
		PreNums        int      `db:"-" json:"pre_nums"`
	}{}

	err = db.Select(&queueInfo, strSql, baseInfo[0].GroupId, cid)
	if err != nil {
		return nil, err
	}
	for _, item := range queueInfo {
		if item.Status.Valid && item.Status.Int64 == 1 {
			strSql = "select count(custom_id) from t_jicai_subject_queue where subject_id = ? and queue_no < ? and status =1;"
			var pnum int
			db.Get(&pnum, strSql, item.SubjectId, item.QueueNo.Int64)
			item.PreNums = pnum
		} else {
			item.PreNums = -1
		}
	}

	return map[string]interface{}{
		"baseInfo":   baseInfo,
		"customInfo": customInfo,
		"queueInfo":  queueInfo,
	}, nil
}

func (srv *GroupBloodService) Enqueue(subjectId, cid int) (interface{}, error) {
	db := util.GetWriteSqlDB()
	strSql := `select * from (select id, queue_len, cur_queue_no from t_jicai_subject where id = ? ) t1 left join
	  (select subject_id, status, queue_no from t_jicai_subject_queue where custom_id = ? and status in (1,2) order by status asc) t2 on 1=1`
	var sQueueInfoList = []struct {
		ToSubjectId    int           `db:"id"`
		QueueLen       int           `db:"queue_len"`
		CurrentQueueNo int           `db:"cur_queue_no"`
		Status         sql.NullInt64 `db:"status"`
		FromSubjectId  sql.NullInt64 `db:"subject_id"`
		QueueNo        sql.NullInt64 `db:"queue_no"`
	}{}
	err := db.Select(&sQueueInfoList, strSql, subjectId, cid)
	if err != nil {
		return nil, errors.New("1::" + err.Error())
	}
	for _, sQueueInfo := range sQueueInfoList {
		if sQueueInfo.FromSubjectId.Valid && sQueueInfo.FromSubjectId.Int64 == int64(subjectId) {
			if sQueueInfo.Status.Valid && sQueueInfo.Status.Int64 == 2 {
				return nil, errors.New("3::already checked")
			}
			return nil, errors.New("2::already Enqueued")
		}
	}
	sQueueInfo := sQueueInfoList[0]
	tx := db.MustBegin()
	strSql = "insert into t_jicai_subject_queue(subject_id, custom_id, queue_no, status, created_at) values(?,?,?,?,?);"
	tx.MustExec(strSql, subjectId, cid, sQueueInfo.CurrentQueueNo+1, 1, time.Now().Format("2006-01-02 15:04:05"))
	if sQueueInfo.FromSubjectId.Valid && sQueueInfo.Status.Int64 == 1 {
		strSql = "update t_jicai_subject_queue set status = 3 where custom_id = ? and subject_id = ?;"
		sqlResult := tx.MustExec(strSql, cid, sQueueInfo.FromSubjectId.Int64)
		if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
			tx.Tx.Rollback()
			return nil, errors.New("4::failed to update queue info")
		}
		strSql = "update t_jicai_subject set queue_len = queue_len - 1 where id = ? and queue_len > 0"
		tx.MustExec(strSql, sQueueInfo.FromSubjectId.Int64)
	}
	strSql = "update t_jicai_subject set queue_len = queue_len + 1, cur_queue_no = ? where id = ?"
	sqlResult := tx.MustExec(strSql, sQueueInfo.CurrentQueueNo+1, subjectId)
	if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
		tx.Tx.Rollback()
		return nil, errors.New("4::failed to update queue info")
	}
	tx.Commit()
	go func() {
		client := util.GetRedis()
		client.Select(12)
		msg := map[string]interface{}{
			"user":          "system",
			"subjectId":     subjectId,
			"fromSubjectId": sQueueInfo.FromSubjectId.Int64,
			"oper":          "ENQUEUE",
			"custom_id":     cid,
		}
		buf, _ := json.Marshal(msg)
		client.Publish("groupblood-inform", string(buf))
	}()
	return true, nil
}

func (srv *GroupBloodService) Next(subjectId int, operType int) (interface{}, error) {
	var queueInfo = []struct {
		QueueIngNo     int `db:"queue_ing_no"`
		QueueLen       int `db:"queue_len"`
		CurrentQueueNo int `db:"cur_queue_no"`
	}{}
	//todo 查询当前进行中人员id
	strSql := "select queue_ing_no, queue_len, cur_queue_no from t_jicai_subject where id = ?"
	db := util.GetWriteSqlDB()
	err := db.Select(&queueInfo, strSql, subjectId)
	if err != nil {
		return nil, err
	}
	if len(queueInfo) == 0 {
		return nil, errors.New("1::can not find subject")
	}
	if queueInfo[0].QueueLen <= 0 {
		return nil, errors.New("2::empty queue")
	}
	var isReady = false
	if queueInfo[0].QueueIngNo != 0 {
		//将检查完的相应客户设置为完成状态
		var toUpStatus = 2
		if operType == 1 {
			toUpStatus = 0 //跳号
		}
		//加上status等于1的条件防止当先前队列为空再进人时queue_ing_no虽不为0但是拿这个号码的人先前状态已经被改变了，导致减员出现数据不一致的情况
		strSql = "update t_jicai_subject_queue set status = ? where subject_id = ? and queue_no = ? and status = 1"
		tx := db.MustBegin()
		sqlResult := tx.MustExec(strSql, toUpStatus, subjectId, queueInfo[0].QueueIngNo)
		if ra, _ := sqlResult.RowsAffected(); ra > 0 {
			//减员 加上queue_len大于0的条件防止更新之后queue_len出现负数
			strSql = "update t_jicai_subject set queue_len = queue_len - 1 where id = ? and queue_len > 0"
			sqlResult = tx.MustExec(strSql, subjectId)
			if ra, _ = sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
			} else {
				tx.Commit()
			}

		} else {
			tx.Tx.Rollback()
			isReady = true
		}
	}
	//查询被叫号人员id
	strSql = "select custom_id, extra, queue_no from t_jicai_subject_queue t1 left join t_custom t2 on t1.custom_id = t2.id where subject_id = ? and t1.status = 1 order by queue_no asc limit 2;"
	var cList = []struct {
		CustomId int         `db:"custom_id"`
		QueueNo  int         `db:"queue_no"`
		Extra    null.String `db:"extra"`
	}{}
	db.Select(&cList, strSql, subjectId)
	if len(cList) > 0 && cList[0].QueueNo > 0 {
		strSql = "update t_jicai_subject set queue_ing_no = ? where id = ?;"
		db.Exec(strSql, cList[0].QueueNo, subjectId)
	}
	go func() {
		client := util.GetRedis()
		client.Select(12)
		msg := map[string]interface{}{
			"user":      "system",
			"subjectId": subjectId,
			"oper":      "NEXT",
		}
		buf, _ := json.Marshal(msg)
		client.Publish("groupblood-inform", string(buf))
		//推送微信模板休息
		strSql = "select t2.name, t2.start_time, subject_name from t_jicai_subject t1 left join t_jicai t2 on t1.jicai_id = t2.id where t1.id = ? limit 1;"
		var jInfo = struct {
			JicaiName   string `db:"name"`
			StartTime   string `db:"start_time"`
			SubjectName string `db:"subject_name"`
		}{}
		err = db.Get(&jInfo, strSql, subjectId)
		if err != nil {
			log.Println(err)
			return
		}
		if len(cList) == 2 {
			if !cList[1].Extra.Valid || cList[1].Extra.String == "" {
				return
			}
			var uinfo = map[string]string{}
			err = json.Unmarshal([]byte(cList[1].Extra.String), &uinfo)
			if err != nil {
				log.Println(err)
				return
			}
			msg := map[string]interface{}{
				"touser":      uinfo["openid"],
				"template_id": "2tQiM2Y3oCleWeF7aArhGr-Lu8bCg68ylw0aUvmTkEM",
				"url":         config.IniConf.Section("weixin").Key("wx.front_host").Value() + "/index/activity/queue",
				"data": map[string]interface{}{
					"first": map[string]interface{}{
						"value": "集采准备通知",
						"color": "#173177",
					},
					"keyword1": map[string]interface{}{
						"value": jInfo.JicaiName,
						"color": "#173177",
					},
					"keyword2": map[string]interface{}{
						"value": jInfo.StartTime[0:10],
						"color": "#173177",
					},
					"remark": map[string]interface{}{
						"value": "请您做好准备尽快到" + jInfo.SubjectName + "侯检",
						"color": "#173177",
					},
				},
			}
			buf, _ := json.Marshal(msg)
			srv.WeixinService.SendTpl(string(buf), "")
			if isReady {
				uinfo = map[string]string{}
				err = json.Unmarshal([]byte(cList[0].Extra.String), &uinfo)
				if err != nil {
					log.Println(err)
					return
				}
				msg["touser"] = uinfo["openid"]
				buf, _ = json.Marshal(msg)
				srv.WeixinService.SendTpl(string(buf), "")
			}
		}
	}()
	return true, nil
}

func (srv *GroupBloodService) GetQueueInfo(subjectId int, status int) (interface{}, error) {
	var istatus string = "=1"
	if status == 2 {
		istatus = "in (2,0)"
	}
	strSql := `select t1.*, t3.name, t3.id from (select js.jicai_id, custom_id, queue_no, status from t_jicai_subject_queue jsq, t_jicai_subject js where jsq.subject_id = js.id and subject_id = ? and status ` + istatus + `) t1 left join  t_jicai_detail t2 on t1.custom_id = t2.custom_id and t2.jicai_id = t1.jicai_id left join t_order t3 on t2.order_id = t3.id  order by queue_no asc`
	db := util.GetSqlDB()
	var retList = []struct {
		QueueNo int    `db:"queue_no"`
		Status  int    `db:"status"`
		Name    string `db:"name"`
		OrderId string `db:"id"`
	}{}
	err := db.Select(&retList, strSql, subjectId)
	if err != nil {
		return nil, err
	}
	strSql = "select t1.queue_ing_no,t1.subject_name, t2.name, t2.start_time, t2.end_time from t_jicai_subject t1 left join t_jicai t2 on t1.jicai_id = t2.id where t1.id = ?;"
	var queueBaseInfo = struct {
		JicaiName   string `db:"name" json:"jicai_name"`
		SubjectName string `db:"subject_name" json:"subject_name"`
		QueueIngNo  int    `db:"queue_ing_no" json:"queue_ing_no"`
		StartTime   string `db:"start_time" json:"start_time"`
		EndTime     string `db:"end_time" json:"end_time"`
	}{}
	err = db.Get(&queueBaseInfo, strSql, subjectId)
	return map[string]interface{}{
		"queueList": retList,
		"queueInfo": queueBaseInfo,
	}, err
}

func (srv *GroupBloodService) GetOrderInfo(mobile string) (interface{}, error) {
	strSql := "select id, name,mobile, gender,age,visit_date,blood_codes,address, detail_address, group_concat(t2.product_name) as pname,source from t_order t1 left join t_order_product t2 on t1.id = t2.order_id where mobile = ? and type ='200' and status in(2,4) and admin_remark='尚医智信集采' and (source='batch' or source='shangyizhixin') group by t1.id limit 2;"
	var oinfoItem = []struct {
		Id            string      `db:"id" json:"id"`
		CustomName    string      `db:"name" json:"name"`
		Mobile        string      `db:"mobile" json:"mobile"`
		Gender        int         `db:"gender" json:"gender"`
		Age           int         `db:"age" json:"age"`
		VisitDate     string      `db:"visit_date" json:"visit_date"`
		ProductName   string      `db:"pname" json:"product_name"`
		Source        string      `db:"source" json:"source"`
		Address       null.String `db:"address" json:"address"`
		DetailAddress null.String `db:"detail_address" json:"detail_address"`
		BloodCodes    null.String `db:"blood_codes" json:"blood_codes"`
		IsAnswered    bool        `db:"-" json:"is_answered"`
		IsGivingupAdd bool        `db:"-" json:"is_givingup_add"`
	}{}
	db := util.GetSqlDB()
	err := db.Select(&oinfoItem, strSql, mobile)
	if err != nil {
		return nil, err
	}
	if len(oinfoItem) == 0 {
		return nil, errors.New("1::can not find order")
	}
	var tids []int = []int{}
	err = db.Select(&tids, "select id from t_activity_answer where mobile =? and is_delete='N' limit 1;", mobile)
	if err != nil {
		return nil, err
	}
	if len(tids) > 0 {
		oinfoItem[0].IsAnswered = true
	}

	strSql = "select id from t_jicai_keshi_log where mobile =? and detect_product_id = -3 limit 1;"
	tids = []int{}
	err = db.Select(&tids, strSql, mobile)
	if err != nil {
		return nil, err
	}
	if len(tids) > 0 {
		oinfoItem[0].IsGivingupAdd = true
	}
	return oinfoItem, nil
}

func (srv *GroupBloodService) GetCheckProgress(mobile string) (interface{}, error) {
	db := util.GetSqlDB()
	strSql := "select product_id from t_order_product where order_id in (select id from t_order where mobile = ? and status in(2,4) and type ='200' and admin_remark='尚医智信集采' and (source='batch' or source='shangyizhixin'))"
	var strPids = []string{}
	err := db.Select(&strPids, strSql, mobile)
	if err != nil {
		return nil, err
	}
	if len(strPids) == 0 {
		return nil, errors.New("1::can not find order")
	}
	strSql = `select t1.*, GROUP_CONCAT(t3.id, '-', t3.name) as dids, t4.tdids, t4.detect_product_name from (
		    select id, name, is_blood, need_emptiness from t_detect_product t1 where id in (
				        select DISTINCT detect_product_id from t_product_detect_product where product_id in (` + strings.Join(strPids, ",") +
		`) 
																				    ) and is_blood = 'N'
																				)t1 left JOIN t_detect_product_item t2 on t1.id = t2.detect_product_id left JOIN t_detect_item t3 on t2.detect_item_id = t3.id left join 
																				(select detect_product_id, detect_product_name, group_concat(detect_item_id, '-', status) tdids from t_jicai_keshi_log  where mobile = ?  group by detect_product_id) t4 on t1.id = t4.detect_product_id GROUP BY t1.id;`
	var progressInfo = []struct {
		Id      int         `db:"id" json:"keshi_id"`
		Name    string      `db:"name" json:"name"`
		IsBlood string      `db:"is_blood" json:"is_blood"`
		Notice  null.String `db:"need_emptiness" json:"notice"`
		Items   []struct {
			Id     string `json:"detect_item_id"`
			Name   string `json:"name"`
			Status string `json:"status"`
		} `db:"items" json:"items"`
		KName         null.String `db:"detect_product_name" json:"k_name"`
		ItemIds       null.String `db:"dids" json:"-"`
		TargetItemIds null.String `db:"tdids" json:"-"`
		IsComplete    string      `db:"-" json:"is_complete"`
	}{}
	err = db.Select(&progressInfo, strSql, mobile)
	if err != nil {
		return nil, err
	}
	for i, l := 0, len(progressInfo); i < l; i++ {
		progressInfo[i].IsComplete = ""
		isDrop := 0
		var logItems = map[string]string{}
		if progressInfo[i].TargetItemIds.Valid {
			isDrop = 1
			logStrItems := strings.Split(progressInfo[i].TargetItemIds.String, ",")
			for _, strItem := range logStrItems {
				tps := strings.Split(strItem, "-")
				logItems[tps[0]] = tps[1]
				if tps[1] == "N" {
					isDrop = 2
				}
			}
		}
		if !progressInfo[i].ItemIds.Valid {
			return nil, errors.New("wrong base data")
		}
		titems := strings.Split(progressInfo[i].ItemIds.String, ",")
		if len(logItems) == len(titems) {
			progressInfo[i].IsComplete = "Y"
			if isDrop == 1 {
				progressInfo[i].IsComplete = "D"
			}
		}
		for _, titem := range titems {
			var status = ""
			pieces := strings.Split(titem, "-")
			if st, ok := logItems[pieces[0]]; ok {
				status = st
			}
			progressInfo[i].Items = append(progressInfo[i].Items, struct {
				Id     string `json:"detect_item_id"`
				Name   string `json:"name"`
				Status string `json:"status"`
			}{
				Id:     pieces[0],
				Name:   pieces[1],
				Status: status,
			})
		}
	}
	strSql = "select status from t_jicai_keshi_log where mobile =? and detect_product_id = -1 limit 1"
	var hasBlood string
	err = db.Get(&hasBlood, strSql, mobile)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	var cpinfo = struct {
		Id      int         `db:"id" json:"keshi_id"`
		Name    string      `db:"name" json:"name"`
		IsBlood string      `db:"is_blood" json:"is_blood"`
		Notice  null.String `db:"need_emptiness" json:"notice"`
		Items   []struct {
			Id     string `json:"detect_item_id"`
			Name   string `json:"name"`
			Status string `json:"status"`
		} `db:"items" json:"items"`
		KName         null.String `db:"detect_product_name" json:"k_name"`
		ItemIds       null.String `db:"dids" json:"-"`
		TargetItemIds null.String `db:"tdids" json:"-"`
		IsComplete    string      `db:"-" json:"is_complete"`
	}{
		Id: -1, Name: "抽血", IsBlood: "Y", Notice: null.StringFrom("需要空腹"), IsComplete: "",
	}
	if hasBlood != "" {
		if hasBlood == "N" {
			cpinfo.IsComplete = "Y"
		} else if hasBlood == "Y" {
			cpinfo.IsComplete = "D"
		}
	}
	progressInfo = append(progressInfo, cpinfo)
	strSql = "select id from t_jicai_keshi_log where mobile =? and detect_product_id = -2 limit 1;"
	var hasCmplete int
	err = db.Get(&hasCmplete, strSql, mobile)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}

	return map[string]interface{}{
		"progressinfo": progressInfo,
		"has_complete": hasCmplete,
	}, nil
}

func (srv GroupBloodService) ConfirmChecked(param *entity.GPParam) (interface{}, error) {
	db := util.GetWriteSqlDB()
	switch param.Oper {
	case "COMPLETE":
		strSql := "insert into t_jicai_keshi_log(mobile, detect_product_id) values(?,-2);"
		db.Exec(strSql, param.Mobile)
		return true, nil
	case "DROP":
		strSql := "insert into t_jicai_keshi_log(mobile, detect_product_id, detect_item_id, status,create_time, detect_product_name) values"
		nowStr := time.Now().Format("2006-01-02 15:04:05")
		for _, gpItem := range param.DPItems {
			for _, ditem := range gpItem.DetectItemIds {
				strSql += fmt.Sprintf("('%s',%d, %d, '%s', '%s', '%s'),", param.Mobile, gpItem.DetectProductId, ditem, "Y", nowStr, gpItem.Name)
			}
		}
		strSql = strSql[0 : len(strSql)-1]
		db.Exec(strSql)
		return true, nil
	default:
		return nil, nil
	}
}

func splitAndSortString(values string) string {
	varr := strings.Split(values, ",")
	sort.Strings(varr)
	return strings.Join(varr, ",")
}