gobackend-qingma/dal/deliver_userdal.go

package dal

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/leeqvip/gophp/serialize"
	"gopkg.in/mgo.v2/bson"
	"sort"
	"strconv"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/leeqvip/gophp"
	"gopkg.in/guregu/null.v3"
	"xiaoniaokuaiyan.com/xiaoniao/entity"
	"xiaoniaokuaiyan.com/xiaoniao/util"
)

type DeliverUserDal struct {
}

type DUOrderItem struct {
	OrderId         string      `db:"id" json:"order_id"`
	CustomName      string      `db:"name" json:"name"`
	Gender          string      `db:"gender" json:"gender"`
	Age             uint8       `db:"age" json:"age"`
	Birthday        string      `db:"birthday" json:"birthday"`
	CustomMobile    string      `db:"mobile" json:"mobile"`
	Address         string      `db:"address" json:"address"`
	BookTime        null.String `db:"book_time" json:"book_time"`
	OrderStatus     int         `db:"status" json:"order_status"`
	Products        []DUProduct `db:"-" json:"products"`
	NeedEmptiness   null.Int    `db:"need_emptiness" json:"need_emptiness"`
	ReportPeriod    null.String `db:"report_period" json:"report_period"`
	PressurePipe    null.String `db:"pressure_pipe" json:"pressure_pipe"`
	BloodAddress    null.String `db:"blood_address" json:"blood_address"`
	Notice          null.String `db:"notice" json:"notice"`
	Remark1         null.String `db:"remark1" json:"remark1"`
	GetType         null.String `db:"get_type" json:"get_type"`
	DeliverUsers    []DU        `db:"-" json:"deliver_users"`
	EVisitDate      null.String `db:"e_visit_date" json:"EVisitDate"`
	EVisitTimeRange null.String `db:"e_visit_time_range" json:"EVisitTimeRange"`
	IdNum           null.String `db:"id_num" json:"IdNum"`
	NucleinType     uint8       `db:"nuclein_type" json:"nuclein_type"`
}

type DUOrderItemV2 struct {
	DUOrderItem
	IsDone bool `json:"is_done"`
}

type DU struct {
	Name   string `db:"name" json:"name"`
	Tel    string `db:"tel" json:"tel"`
	Career string `db:"career" json:"career"`
}
type DUProduct struct {
	Name      string `db:"name" json:"name"`
	ItemNames string `db:"item_names" json:"item_names"`
	Searchs   string `db:"searchs" json:"searchs"`
}

func (dud *DeliverUserDal) GetOrderList(openid string, status, pageIndex, pageSize int) (interface{}, error) {
	//20230403 只查100 去掉110
	strStatus := " status in (3,4, 6, 11) and t3.retype in ('100') "
	whereStr := " where t1.openid = ? and" + strStatus
	params := []interface{}{
		openid,
	}
	strSql := `select t3.id, t3.name, t3.gender, t3.birthday, t3.age, t3.mobile,t3.status, concat(t3.address, t3.detail_address) as address, concat(t3.visit_date, ' ', t3.visit_time_range) as book_time, t4.need_emptiness, t4.report_period, pressure_pipe,
	blood_address, notice, t4.remark1,t4.e_visit_date,t4.e_visit_time_range,t2.get_type,t3.id_num,t3.nuclein_type
	from t_deliver_user t1 left join t_order_deliver_user t2 on t1.id = t2.deliver_user_id
	right join t_order t3 on t2.order_id = t3.id
	left join t_order_extra t4 on t3.id = t4.order_id `
	dataList := &[]*DUOrderItem{}
	result, err := List(dataList, "("+strSql+whereStr+") t5", strSql+whereStr, " order by t3.visit_date desc , t3.nuclein_type ,t3.visit_time_range", "", params, Pager{PageIndex: pageIndex, PageSize: pageSize})
	if err != nil {
		return nil, err
	}

	strSql = `select t3.*, group_concat(dp.name) as item_names from t_product_detect_product pdp left join t_detect_product dp on pdp.detect_product_id = dp.id right join 
		(select t2.id, t2.name,t2.searchs from t_order_product t1 left join t_product t2 on t1.product_id = t2.id where t1.order_id = ?) t3 on pdp.product_id = t3.id`
	strSqlDu := "select t1.name, t1.tel, t2.career from t_deliver_user t1 left join t_order_deliver_user t2 on t1.id = t2.deliver_user_id right join t_order t3 on t2.order_id = t3.id where t3.id = ?"
	db := util.GetSqlDB()
	for _, item := range *dataList {
		pitems := []DUProduct{}
		err = db.Select(&pitems, strSql, item.OrderId)
		if err != nil {
			return nil, err
		}
		item.Products = pitems

		//fetch deliver user info
		duList := []DU{}
		err = db.Select(&duList, strSqlDu, item.OrderId)
		if err != nil {
			return nil, err
		}
		item.DeliverUsers = duList
	}
	return result, nil
}

// 20220225
func (dud *DeliverUserDal) GetOrderListV2(openid string, pageIndex, pageSize int) (interface{}, error) {
	//20230403 只查100 去掉110
	strStatus := " status = 3 and t3.retype in ('100') "
	whereStr := " where t1.openid = ? and" + strStatus

	db := util.GetSqlDB()
	strSql := `select t3.id, t3.name, t3.gender, t3.birthday, t3.age, t3.mobile,t3.status, concat(t3.address, t3.detail_address) as address, concat(t3.visit_date, ' ', t3.visit_time_range) as book_time, t4.need_emptiness, t4.report_period, pressure_pipe,
	blood_address, notice, t4.remark1,t4.e_visit_date,t4.e_visit_time_range,t2.get_type,t3.id_num,t3.nuclein_type
	from t_deliver_user t1 left join t_order_deliver_user t2 on t1.id = t2.deliver_user_id
	right join t_order t3 on t2.order_id = t3.id
	left join t_order_extra t4 on t3.id = t4.order_id `
	dataList := []*DUOrderItemV2{}
	err := db.Select(&dataList, strSql+whereStr+" order by t3.visit_date desc , t3.nuclein_type ,t3.visit_time_range limit 100", openid)
	if err != nil {
		return nil, err
	}

	strSql = `select t3.*, group_concat(dp.name) as item_names from t_product_detect_product pdp left join t_detect_product dp on pdp.detect_product_id = dp.id right join 
		(select t2.id, t2.name,t2.searchs from t_order_product t1 left join t_product t2 on t1.product_id = t2.id where t1.order_id = ?) t3 on pdp.product_id = t3.id`
	strSqlDu := "select t1.name, t1.tel, t2.career from t_deliver_user t1 left join t_order_deliver_user t2 on t1.id = t2.deliver_user_id right join t_order t3 on t2.order_id = t3.id where t3.id = ?"

	strSqlSign := "select count(1) from t_order_sign where order_id = ? and flag='200' and status = '300'"
	done := []*DUOrderItemV2{}
	notDone := []*DUOrderItemV2{}
	for _, item := range dataList {
		pitems := []DUProduct{}
		err = db.Select(&pitems, strSql, item.OrderId)
		if err != nil {
			return nil, err
		}
		item.Products = pitems

		//fetch deliver user info
		duList := []DU{}
		err = db.Select(&duList, strSqlDu, item.OrderId)
		if err != nil {
			return nil, err
		}
		item.DeliverUsers = duList
		count := 0
		db.Get(&count, strSqlSign, item.OrderId)
		item.IsDone = count > 0
		if count > 0 {
			done = append(done, item)
		} else {
			notDone = append(notDone, item)
		}
	}
	return map[string]interface{}{
		"not_done": notDone,
		"done":     done,
	}, nil
}

func (dud *DeliverUserDal) GetOrderSign(orderId string) (interface{}, error) {
	db := util.GetSqlDB()
	strSql := "select t2.openid,t1.career from t_order_deliver_user t1 inner join t_deliver_user t2 on t1.deliver_user_id = t2.id where t1.order_id = ?"
	deliverUserlist := []struct {
		Openid string `json:"openid"`
		Career string `json:"career"`
	}{}
	err := db.Select(&deliverUserlist, strSql, orderId)
	if err != nil {
		return nil, err
	}
	if len(deliverUserlist) == 0 {
		return map[string]interface{}{
			"deliver_user_list": deliverUserlist,
			"sign_list":         nil,
		}, nil
	}
	strSql = "select * from t_order_sign where order_id = ? and flag='200' order by id desc"
	signList := &[]entity.OrderSign{}
	err = db.Select(signList, strSql, orderId)
	if err != nil {
		return nil, err
	}
	orderInfo, _ := DefaultOrderDal.Get(orderId)
	return map[string]interface{}{
		"deliver_user_list": deliverUserlist,
		"sign_list":         signList,
		"order_info":        orderInfo,
	}, nil
}

func (dud *DeliverUserDal) SaveOrderSign(signItem *entity.OrderSign) (interface{}, error) {
	var (
		strSql string
		kv     map[string]interface{}
	)
	db := util.GetWriteSqlDB()
	tx := db.MustBegin()
	strSql, kv = util.GenerateInsertSqlFromStruct("t_order_sign", signItem)
	sqlResult, err := tx.NamedExec(strSql, kv)
	if err != nil {
		tx.Rollback()
		return false, err
	}
	if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
		tx.Rollback()
		return false, errors.New("failed to sign")
	}
	if signItem.Status == "400" {
		var mj = struct {
			Code      string `json:"code"`
			IsXueYang string `json:"isxieyang"`
		}{}
		err := json.Unmarshal([]byte(signItem.Remark), &mj)
		if err != nil {
			tx.Rollback()
			return nil, err
		}
		//20210927 如果血样是0 则是核酸类，血检条码必须唯一
		if mj.IsXueYang == "0" {
			var count int
			db.Get(&count, "select count(1) from t_order where blood_codes=? and id != ? ", mj.Code, signItem.OrderId)
			if count > 0 {
				tx.Rollback()
				return nil, fmt.Errorf("2::已存在的blood_codes %s", mj.Code)
			}
		}
		tx.Exec("update t_order set blood_codes = ? where id = ?", mj.Code, signItem.OrderId)
		go util.InsertMongo(signItem.OrderId, "护士系统400", 1, map[string]interface{}{"blood_codes": mj.Code, "url": "/duser/saveordersign/400"})
	} else if signItem.Status == "700" {
		//20210218 增加 700 护士修改 订单name gender birthday idnum age 字段 只有在状态3 待上门才可以修改
		//20210223 增加 mobile
		var item = struct {
			Name   string `json:"name"`
			Gender string `json:"gender"`
			Birth  string `json:"birth"`
			IdNum  string `json:"idnum"`
			Mobile string `json:"mobile"`
		}{}

		err := json.Unmarshal([]byte(signItem.Remark), &item)
		if err != nil {
			tx.Rollback()
			return nil, err
		}
		//if len(item.Birth) >= 7 {
		//	item.Birth = item.Birth[0:7]
		//}
		birth, err := time.Parse("2006-01-02", item.Birth)
		if err != nil {
			tx.Rollback()
			return nil, err
		}
		age := time.Now().Year() - birth.Year()
		sqlResult, err := tx.Exec("update t_order set name = ?,age = ?,birthday = ?,gender = ?,id_num = ?,mobile = ?,updated_at = ? where id = ? and status = 3", item.Name, age, item.Birth, item.Gender, item.IdNum, item.Mobile, time.Now().Format("2006-01-02 15:04:05"), signItem.OrderId)
		if err != nil {
			tx.Rollback()
			return nil, err
		}
		if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
			tx.Rollback()
			return nil, fmt.Errorf("7::name:%s,age:%d,birth:%s,gender:%s,idnum:%s,orderid:%s", item.Name, age, item.Birth, item.Gender, item.IdNum, signItem.OrderId)
		}
		go util.InsertMongo(signItem.OrderId, "护士系统700", 1, map[string]interface{}{"name": item.Name, "age": age, "birthday": item.Birth, "gender": item.Gender, "id_num": item.IdNum, "mobile": item.Mobile, "url": "/duser/saveordersign/700"})
	}
	tx.Commit()
	return true, nil
}

var DefaultDeliverUserDal = &DeliverUserDal{}

func (dud *DeliverUserDal) Get(openid string) (*entity.DeliverUser, error) {
	db := util.GetSqlDB()
	strSql := "select * from t_deliver_user where openid = ? and is_delete = 1;"
	var du = entity.DeliverUser{}
	err := db.Get(&du, strSql, openid)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, errors.New("1::not exists")
		}
		return nil, err
	}

	du.Qualifiction, _ = getDeliverUserPic(du.Id, "qualifiction")
	du.License, _ = getDeliverUserPic(du.Id, "license")
	du.Photo, _ = getDeliverUserPic(du.Id, "photo")
	return &du, nil
}

func getDeliverUserPic(id uint64, pType string) ([]string, error) {
	db := util.GetSqlDB()
	strSql := "select pic from t_deliver_user_pic where delever_uer_id = ? and type = ? and is_delete = 'N' "
	var list = []string{}
	err := db.Select(&list, strSql, id, pType)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, errors.New("1::not exists")
		}
		return nil, err
	}
	return list, nil
}

func (dud *DeliverUserDal) SaveDU(duItem *entity.DeliverUser) (bool, error) {
	db := util.GetWriteSqlDB()
	if len(duItem.Openid) > 0 {
		strSql := "select tel from t_deliver_user where openid =? and is_delete <>2 limit 1;"
		var tel null.String
		err := db.Get(&tel, strSql, duItem.Openid)
		if err == nil {
			if !tel.Valid || (tel.Valid && len(tel.String) == 0) {
				//todo 更新 tel
				db.Exec("update t_deliver_user set tel = ? where openid = ?", duItem.Tel, duItem.Openid)
				return true, nil
			} else if tel.String != duItem.Tel {
				return false, errors.New("2:: already bind another tel")
			}
		}
	}

	strSql := "select id from t_deliver_user where tel = ? and is_delete <> 2;"
	var eid int
	db.Get(&eid, strSql, duItem.Tel)
	if eid > 0 {
		return false, errors.New("1::already regist")
	}
	strSql, kvs := util.GenerateInsertSqlFromStruct("t_deliver_user", duItem)
	sqlResult, err := db.NamedExec(strSql, kvs)
	if err != nil {
		return false, err
	}
	if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
		return false, errors.New("failed to regist")
	}
	did, _ := sqlResult.LastInsertId()
	//todo 20210312 保存到mongo t_log
	jsonbyte, err := serialize.Marshal(_map2map(kvs))
	if err != nil {
		//return false ,err
		fmt.Println(err.Error())
	} else {
		_insertMongo(strconv.FormatInt(did, 10), string(jsonbyte), 15)
	}

	strSql = "insert into t_deliver_user_pic(delever_uer_id, type,pic,create_time) values(?,?,?,?);"
	var ctime = time.Now().Format("2006-01-02 15:05:05")
	for _, ql := range duItem.Qualifiction {
		db.Exec(strSql, did, "qualifiction", ql, ctime)
	}
	for _, lc := range duItem.License {
		db.Exec(strSql, did, "license", lc, ctime)
	}
	for _, pt := range duItem.Photo {
		db.Exec(strSql, did, "photo", pt, ctime)
	}
	db.Exec("insert into t_deliver_account(deliver_user_id, created_at) values(?,?);", did, ctime)
	return true, nil
}

func (dud *DeliverUserDal) UpdateDU(duItem *entity.DeliverUser) (interface{}, error) {
	db := util.GetWriteSqlDB()
	tx := db.MustBegin()
	strSql, kvs := util.GenerateUpdateSqlFromStruct("t_deliver_user", duItem, " where id=:id")
	sqlResult, err := tx.NamedExec(strSql, kvs)
	if err != nil {
		tx.Tx.Rollback()
		return false, err
	}
	if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
		tx.Tx.Rollback()
		return false, errors.New("1::failed to update")
	}

	//todo 20210312 保存到mongo t_log
	jsonbyte, err := serialize.Marshal(_map2map(kvs))
	if err != nil {
		//tx.Tx.Rollback()
		//return false ,err
		fmt.Println(err.Error())
	} else {
		_insertMongo(strconv.FormatUint(duItem.Id, 10), string(jsonbyte), 17)
	}

	if err = _updatePic(tx, duItem.Id, "qualifiction", duItem.Qualifiction); err != nil {
		return nil, err
	}
	if err = _updatePic(tx, duItem.Id, "license", duItem.License); err != nil {
		return nil, err
	}
	if err = _updatePic(tx, duItem.Id, "photo", duItem.Photo); err != nil {
		return nil, err
	}
	tx.Commit()
	return true, nil
}

func _insertMongo(name, data string, dbtype int) {
	db := util.GetMgoDB()
	if db == nil {
		return
	}
	defer db.Session.Close()
	err := db.C("t_log").Insert(bson.M{
		"name":     name,
		"type":     dbtype,
		"data":     data,
		"createby": "go",
		"create":   time.Now().Format("2006-01-02 15:04:05"),
	})
	if err != nil {
		fmt.Println(err)
		return
	}
}

// null 转换成 普通类型，否则php 序列化过不去
func _map2map(kvs map[string]interface{}) map[string]interface{} {
	resultMap := map[string]interface{}{}
	for k, v := range kvs {
		//t := reflect.TypeOf(v)
		//switch t.Kind() {
		//case reflect.TypeOf(null.String{}).Kind():
		//	if v.(null.String).Valid{
		//		resultMap[k]=v.(null.String).String
		//	}
		//case reflect.TypeOf(null.Int{}).Kind():
		//	if v.(null.Int).Valid{
		//		resultMap[k]=v.(null.Int).Int64
		//	}
		//default:
		//			resultMap[k]=v
		//		}
		if nullString, ok := v.(null.String); ok {
			resultMap[k] = nullString.String
		} else if nullInt, ok := v.(null.Int); ok {
			resultMap[k] = nullInt.Int64
		} else {
			resultMap[k] = v
		}

	}
	return resultMap
}

func _updatePic(tx *sqlx.Tx, duId uint64, typ string, pics []string) error {
	if len(pics) > 0 {
		strSql := "delete from t_deliver_user_pic where delever_uer_id = ? and type = ?;"
		tx.MustExec(strSql, duId, typ)
		strSql = ""
		var ctime = time.Now().Format("2006-01-02 15:05:05")
		for i, ql := range pics {
			strSql += fmt.Sprintf("(%d,'%s','%s','%s')", duId, typ, ql, ctime)
			if i < len(pics)-1 {
				strSql += ","
			}
		}
		strSql = "insert into t_deliver_user_pic(delever_uer_id, type,pic,create_time) values" + strSql
		sqlResult := tx.MustExec(strSql)
		if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
			tx.Tx.Rollback()
			return errors.New("2::failed to update " + typ)
		}
	}
	return nil
}

func (dud *DeliverUserDal) GetBills(duId int, pageIndex, pageSize int) (interface{}, error) {
	per := Pager{PageIndex: pageIndex, PageSize: pageSize}
	var bills = []entity.DeliverBill{}
	result, err := List(&bills, "t_deliver_bill", "select * from t_deliver_bill", " order by created_at desc", "where deliver_user_id = ? and status = '300' ", []interface{}{duId}, per)
	return result, err
}

func (dud *DeliverUserDal) GetMsg(duId int, pageIndex, pageSize int, cityId int) (interface{}, error) {
	per := Pager{PageIndex: pageIndex, PageSize: pageSize}
	var msgList = []entity.DeliverMsg{}
	result, err := List(&msgList, "t_deliver_msg", "select * from t_deliver_msg", " order by created_at desc", "where deliver_user_id = ? ", []interface{}{duId}, per)

	var pubMsglist = []entity.DeliverMsg{}
	strSql := fmt.Sprintf("SELECT t1.*,t2.is_read from t_deliver_pubmsg t1 left join t_deliver_msg t2 on t1.id = t2.ref_id  where city_ids REGEXP ',?%d,?' order by created_at desc limit 10", cityId)
	db := util.GetWriteSqlDB()
	err = db.Select(&pubMsglist, strSql)
	if err != nil {
		return nil, err
	}
	strSql = "insert into t_deliver_msg(deliver_user_id, title, content,created_at, ref_id) values"
	for _, msg := range pubMsglist {
		if !msg.IsRead.Valid {
			strSql += fmt.Sprintf("(%d,'%s','%s', '%s', %d),", duId, msg.Title, msg.Content, msg.CreatedAt, msg.Id)
			msgList = append(msgList, msg)
		}
	}
	strSql = strSql[0 : len(strSql)-1]
	db.Exec(strSql)
	sort.Slice(msgList, func(i, j int) bool {
		return msgList[i].CreatedAt > msgList[j].CreatedAt
	})
	return result, err

}

func (dud *DeliverUserDal) GetScoreBill(duId int, pageIndex, pageSize int, typ string) (interface{}, error) {
	per := Pager{PageIndex: pageIndex, PageSize: pageSize}
	var bills = []entity.DeliverScoreBill{}
	params := []interface{}{duId}
	var whereStr = "where deliver_user_id = ? "
	if typ == "score" {
		whereStr += "and type in('SIGNIN', 'ORDER', 'INVITE_F', 'STUDY_DATA','AUTOTRANS', 'INVITE_F_EXAM', 'DAILY_QUESTION','CHECK','INVITE_EXAM','CHECK_F') "
	} else if typ == "cash" {
		whereStr += "and type in('WITHDRAW','SCORECHSH') "
	}
	result, err := List(&bills, "t_deliver_score_bill", "select * from t_deliver_score_bill", " order by created_at desc", whereStr, params, per)
	return result, err
}