genepoint_service/src/dashoo.cn/genepoint_srv/business/arrangeService/arrangeService.go

/**
 * @Author $
 * @Date $ $
 * @Note
 **/
package arrangeService

import (
	"encoding/json"
	"fmt"
	"strconv"
	"strings"

	"github.com/gogf/gf/os/glog"
	"xorm.io/xorm"

	"dashoo.cn/base_common/utils"
	. "dashoo.cn/base_common/utils/db"
	"dashoo.cn/genepoint_srv/business/common"
)

type ArrangeService struct {
	ServiceBase
}

const TABLE_ARRANGE_MAIN = "bank_arrange_main"
const TABLE_ARRANGE_DETAIL = "bank_arrange_detail"
const TABLE_TAST = "sample_storage_task"
const TABLE_BOX = "bank_box"

func GetArrangeService(xormEngine *xorm.Engine) *ArrangeService {
	s := new(ArrangeService)
	s.DBE = xormEngine
	return s
}

/**
 * @Author 徐春林
 * @Description // 查询库内整理申请人
 * @Date 15:41 2021/3/30
 * @Param
 * @return
 **/
// 获取申请单创建人
func (this *ArrangeService) GetOperUser(EntryNo string) (user string) {

	// 获取当前任务的操作人
	sql := " select  a.UserName from base_user a left join bank_arrange_main b  on a.id = b.CreateUserId  where b.EntryNo ='" + EntryNo + "' "
	this.DBE.SQL(sql).Get(&user)
	return user
}

/**
 * @Author 徐春林
 * @Description // 修改 整理单主表信息
 * @Date 15:45 2021/3/30
 * @Param
 * @return
 **/
// 修改出入库单状态
func (this *ArrangeService) ArrangeMainStatusModify(statusCode int, entryNo,msg string) error {
	set := ""
	if statusCode == 1 || statusCode == 2 || statusCode == 3 || statusCode == 5   {
		set = "  ,exception='' "
	}else{
		if msg!=""{
			set = "  ,exception='"+msg+"' "
		}else{
			set = "  ,exception='' "
		}
	}

	sql := "UPDATE " + TABLE_ARRANGE_MAIN + " SET taskstatus = '" + strconv.Itoa(statusCode) + "' " + set + "  WHERE entryno = '" + entryNo + "'"
	_, err := this.DBE.Exec(sql)
	return err
}

/**
 * @Author EDZ
 * @Description // 修改整理单详情表信息
 * @Date 15:45 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) ArrangeDetailStatusModify(statusCode int, entryNo string) error {
	//获取出入库单Id
	getId := "SELECT id FROM " + TABLE_ARRANGE_MAIN+ " WHERE entryno = '" + entryNo + "'"
	var idList []Id
	err := this.DBE.SQL(getId).Find(&idList)
	//批量更新全部成功或失败的任务样本状态
	sql := "UPDATE " + TABLE_ARRANGE_DETAIL + " SET taskstatus = '" + strconv.Itoa(statusCode) + "' WHERE parentid = '" + strconv.Itoa(idList[0].Id) + "' and taskstatus !=5"
	_, err = this.DBE.Exec(sql)

	return err
}

/**
 * @Author 徐春林
 * @Description // 保存整理单异常信息
 * @Date 17:08 2021/3/30
 * @Param
 * @return
 **/
//保存异常信息
func (this *ArrangeService) SaveArrangeExcepMsg(msg, entryNo string) error {
	sql := "UPDATE " + TABLE_ARRANGE_MAIN + " SET exception = '" + msg + "' WHERE entryno = '" + entryNo + "'"
	_, err := this.DBE.Exec(sql)
	return err
}

/**
 * @Author 徐春林
 * @Description // 根据库内整理任务结果, 成功或部分成功更新数据
 * @Date 17:23 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) UpdateArrangeRes (entryNo string, resp string, message []byte) error {
	var res ResponseEntity
	err := json.Unmarshal(message, &res)
	if err != nil {
		fmt.Println(err)
	}
	taskId := strings.Split(res.Data.Task_id, "@")[0]
	isEnd := res.Data.Is_end

	// 判断是否全部成功, 更新失败的任务状态
	this.GetAbnormalDataAndUpdate(taskId,res)
	// 先更新盒子的原位置
	err = this.UpdateBoxInfoCK(res)
	if err != nil {
		return err
	}

	// 获取返回数据中的 盒子信息
	err = this.UpdateBoxInfo(res)
	if err != nil {
		return err
	}

	// 获取盒子下的 样本信息
	for _, value := range res.Data.Actual_data {
		//获取报文中的 盒子信息
		var Rack_id string = value.Target.Rack_id
		fmt.Println("冻存盒盒子编号：", Rack_id)
		// 获取报文中的 位置信息和 样本编码
		var tubes = value.Tubes
		fmt.Println("样本位置信息：", tubes)
		//新增方法 更新位置信息
		this.UpdatePosition(taskId,Rack_id, tubes, res)
	}
	if isEnd {
		// 根据盒子中样本数量更新样本信息
		this.GetAllBox(taskId)
		err = this.StatusModifyWithDetail(taskId)
	}
	return err
}


/**
 * @Author 徐春林
 * @Description // 获取整理成功的 盒子信息并保存
 * @Date 18:36 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) UpdateBoxInfo(entity ResponseEntity) (err error) {
	//1.  获取当前返回的  冻存架信息，
	var shelf Shelf
	var equipment Bank_Equipment
	glog.Info("entity.Data.Actual_data::", entity.Data.Actual_data)

	// 循环处理返回的数据，如果出现多盒存入的分批处理

	for i := range entity.Data.Actual_data {

		glog.Info("操作地  ", i, "  条数据")

		boxBarcode := entity.Data.Actual_data[i].RackId // 冻存盒编号
		Cu := entity.Data.Actual_data[i].Target.Cu       // 设备编号
		Ltu := entity.Data.Actual_data[i].Target.Ltu     //
		Unit := entity.Data.Actual_data[i].Target.Unit
		Pos := entity.Data.Actual_data[i].Target.Pos
		Group := entity.Data.Actual_data[i].Target.Group

		fmt.Println("保存返回的冻存盒位置信息:cu：", Cu, ",Ltu", Ltu, ",Unit", Unit, ",Group", Group, ",Pos", Pos)

		// 判断是否是冷库，如果是冷库的话，查询容器架子信息时不需要组装 unit
		lengSql := " select  * from bank_equipment    where  cu = '" + utils.ToStr(Cu) + "'  and    Ltu = '" + utils.ToStr(Ltu) + "'  "
		this.DBE.SQL(lengSql).Get(&equipment)


		sql := "select b.id EquipmentId,a.id,b.code,b.name,a.XStation,a.YStation from bank_shelf a left join bank_equipment b  on a.EquipmentId = b.id  where  b.cu = '" + utils.ToStr(Cu) + "' " +
			"and  a.Ltu = '" + utils.ToStr(Ltu) + "'   and a.Group ='" + utils.ToStr(Group) + "'"

		// 拼接冻存盒位置信息
		position := equipment.Code + "-" + common.Boxlinename(shelf.XStation) + utils.ToStr(shelf.YStation) + "-" +
			"" + common.Boxlinename( Pos )

		// 冷库设备
		YStation:="  "
		if equipment.Ltu  == 0    {
			sql += "  and a.Unit ='" + utils.ToStr(Unit) + "'   "
			YStation  = " , YStation =1  "
			position += utils.ToStr(1)
			fmt.Println("保存返回的冻存盒位置信息:YStation1：1 ",  YStation)
		}else{
			YStation  = " ,   YStation =    "+ utils.ToStr(Unit)
			position += utils.ToStr(utils.ToStr(Unit))
			fmt.Println("保存返回的冻存盒位置信息:cYStation1：2 ",  YStation)
		}

		this.DBE.SQL(sql).Get(&shelf)
		fmt.Println("冻存架信息:", shelf.Id, shelf.XStation, shelf.YStation)
		position = shelf.Code + position

		//  更新盒所在的冻存架的信息  计算位置信息，并更新
		// 临时处理逻辑，默认 pos 为 盒子的  XStation
		_, err = this.DBE.Exec("update bank_box set XStation ='" + utils.ToStr(Pos) + "'  "+YStation+" ,EquipmentId ='" + utils.ToStr(shelf.EquipmentId) + "'," +
			" shelfId =" + utils.ToStr(shelf.Id) + ",IsLocked = 0 , position = '"+position+"'  where Barcode ='" + boxBarcode + "' ")
		// 更新 当前冰箱冻存盒容量 信息
		_, err = this.DBE.Exec(" update bank_currboxcapacity  set  `A" + utils.ToStr(Pos) + "`= -1 where shelfId =" + utils.ToStr(shelf.Id) + "")

	}
	return err

}

/**
 * @Author 徐春林
 * @Description // 更新盒子的 位置为空, 架子的容量
 * @Date 18:58 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) UpdateBoxInfoCK(entity ResponseEntity) (err error) {
	//1.  获取当前返回的  冻存架信息，
	var shelf Shelf

	// 循环处理返回的数据，如果出现多盒存入的分批处理
	for i := range entity.Data.Actual_data {
		boxBarcode := entity.Data.Actual_data[i].RackId // 冻存盒编号
		Pos := entity.Data.Actual_data[i].Target.Pos

		sql := " select shelfId , EquipmentId from bank_box where code = '" + boxBarcode + "'"

		this.DBE.SQL(sql).Get(&shelf)
		fmt.Println("冻存架信息:", shelf.Id, shelf.XStation, shelf.YStation)

		//  更新盒所在的冻存架的信息  计算位置信息，并更新
		// 临时处理逻辑，默认 pos 为 盒子的  XStation
		_, err = this.DBE.Exec("update bank_box set EquipmentId=null,shelfId=null,XStation =null,YStation =null,IsLocked = 0  where Barcode ='" + boxBarcode + "' ")
		// 更新 当前冰箱冻存盒容量 信息
		_, err = this.DBE.Exec(" update bank_currboxcapacity  set  `A" + utils.ToStr(Pos) + "`= -2 where shelfId =" + utils.ToStr(shelf.Id) + "")
	}
	return err

}

/**
 * @Author 徐春林
 * @Description // 获取返回结果中的样本信息
 * @Date 19:10 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) UpdatePosition(taskId,rack_id string, tubes []Tube, entity ResponseEntity) {
	// 根据返回的报文中的盒子标识，获取盒子类型
	sql := "select Id,RowNum,ColumnNum,EquipmentId,ShelfId,XStation,YStation  from bank_box where Barcode ='" + rack_id + "'"
	var box Box
	this.DBE.SQL(sql).Get(&box)

	RowNum := box.RowNum
	ColumnNum := box.ColumnNum

	//  获取容器信息
	equipmentsql := " select Id,Code,Name from bank_equipment where id = '"+   box.EquipmentId+"'   "
	var equipmentInfo EquipmentInfo
	this.DBE.SQL(equipmentsql).Get(&equipmentInfo)
	//  获取架子信息
	shelfsql := " select Id,XStation,YStation from bank_shelf where id = '"+  box.ShelfId+"'   "
	var shelf Shelf
	this.DBE.SQL(shelfsql).Get(&shelf)

	glog.Info("冻存盒的行数:", RowNum, ";冻存盒的列数：", ColumnNum) //
	glog.Info("冻存盒内管数量len(tubes) : " ,len(tubes) ) //
	if len(tubes) >0{
		for i := 0; i < len(tubes); i++ {
			no := tubes[i].No
			id := tubes[i].Id
			glog.Info("样本在盒子中的位置：", no, "；样本条码: ", id)
			//计算管子在盒子中的坐标
			var box_x, box_y int

			if no%ColumnNum == 0 { // 如果 取余数为0 则 证明该数是能被  10 整除的数  为该行 最后一个孔位
				box_y = ColumnNum
				box_x = no / ColumnNum
			} else {
				box_y = no % ColumnNum
				box_x = (no / ColumnNum) + 1
			}

			glog.Info("管子在盒子中的位置坐标为：", box_y, ";", box_x)

			position := equipmentInfo.Code + "-" + common.Boxlinename(shelf.YStation) + utils.ToStr(shelf.XStation) + "-" +
				"" + common.Boxlinename(box.YStation) + utils.ToStr(box.XStation)+"-"+common.Boxlinename(box_x) + utils.ToStr(box_y)

			sql = "update bank_sample set BoxId = '" + utils.ToStr(box.Id) + "',Position = '" + utils.ToStr(box_y) + ";" + utils.ToStr(box_x) + "' , " +
				"PositionInfo  =  '" + position + "'   where barcode ='" + id + "' "
			//执行sql 更新位置和坐标信息
			this.DBE.Exec(sql)
			this.UpdateDetail(id, entity, rack_id,taskId)
		}
	}else{
		this.UpdateDetail("", entity, rack_id,taskId)
	}

}

/**
 * @Author 徐春林
 * @Description // 更新样本的容器, 架信息
 * @Date 19:32 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) UpdateDetail(BarCode string, entity ResponseEntity, rack_id,taskId string) {

	glog.Info("修改detail：", entity)
	arrangeMain := this.GetArrangeMain(taskId)
	var shelf Shelf
	var equipment Bank_Equipment
	var equipmentid string
	var ShelfId string
	for i := range entity.Data.Actual_data {
		Cu := entity.Data.Actual_data[i].Target.Cu   // 设备编号
		Ltu := entity.Data.Actual_data[i].Target.Ltu //
		Unit := entity.Data.Actual_data[i].Target.Unit
		Group := entity.Data.Actual_data[i].Target.Group


		// 判断是否是冷库，如果是冷库的话，查询容器架子信息时不需要组装 unit
		lengSql := " select  * from bank_equipment    where  cu = '" + utils.ToStr(Cu) + "'  and    Ltu = '" + utils.ToStr(Ltu) + "'  "
		this.DBE.SQL(lengSql).Get(&equipment)

		if  entity.Response == "moving"{
			// 更内库内整理 冻存盒信息 整理结果
			updatesql := "UPDATE bank_arrange_detail SET IsLocked = '0', taskstatus = '" + strconv.Itoa(SUCCESS) + "' WHERE barcode = '" + entity.Data.Actual_data[i].Target.Rack_id + "'" + " AND parentid = '" + strconv.Itoa(arrangeMain.Id) + "'"
			this.DBE.Exec(updatesql)
			// 更内库内整理 冻存盒信息 整理结果
			updTaskSql := "UPDATE " + TABLE_TAST + " SET statuscode = '" + strconv.Itoa(SUCCESS) + "' WHERE  boxbarcode = '" +  entity.Data.Actual_data[i].Target.Rack_id + "' and task_id = '"+taskId+"'"
			this.DBE.Exec(updTaskSql)
		}

		//当  检测到数据 与当前操作盒子的数据一直时，更新  detail   和sample 信息
		if entity.Data.Actual_data[i].Target.Rack_id == rack_id {
			sql := "select b.id EquipmentId,a.id,a.XStation,a.YStation from bank_shelf a left join bank_equipment b  on a.EquipmentId = b.id  where  b.cu = '" + utils.ToStr(Cu) + "' " +
				"and  a.Ltu = '" + utils.ToStr(Ltu) + "'  and a.Group ='" + utils.ToStr(Group) + "'"

			if equipment.Ltu  == 0    {
				sql += "  and a.Unit ='" + utils.ToStr(Unit) + "'   "
			}

			this.DBE.SQL(sql).Get(&shelf)
			equipmentid = strconv.Itoa(shelf.EquipmentId)
			ShelfId = strconv.Itoa(shelf.Id)

			//pos := equipment.Code + "-" + utils.NumberToLetter(shelf.YStation) + utils.ToStr(shelf.XStation) + "-" +
			//	"" + utils.NumberToLetter(shelf.YStation) + utils.ToStr(shelf.XStation)

			//sql = "update bank_sample set EquipmentId=" + equipmentid + ",ShelfId=" + ShelfId + " , PositionInfo = CONCAT('"+pos+"-', PositionInfo)  where barcode ='" + BarCode + "' "
			sql = "update bank_sample set EquipmentId=" + equipmentid + ",ShelfId=" + ShelfId + "    where barcode ='" + BarCode + "' "
			this.DBE.Exec(sql)

			if  entity.Response == "moving"{
				// 更内库内整理 冻存盒信息 整理结果
				updatesql := "UPDATE bank_arrange_detail SET IsLocked = '0', taskstatus = '" + strconv.Itoa(SUCCESS) + "' WHERE barcode = '" + entity.Data.Actual_data[i].Target.Rack_id + "'" + " AND parentid = '" + strconv.Itoa(arrangeMain.Id) + "'"
				this.DBE.Exec(updatesql)
				// 更内库内整理 冻存盒信息 整理结果
				updTaskSql := "UPDATE " + TABLE_TAST + " SET statuscode = '" + strconv.Itoa(SUCCESS) + "' WHERE  boxbarcode = '" +  entity.Data.Actual_data[i].Target.Rack_id + "' and task_id = '"+taskId+"'"
				this.DBE.Exec(updTaskSql)
			}else{
				// 更新库内整理 样本整理结果
				updatesql := "UPDATE bank_arrange_detail SET IsLocked = '0', taskstatus = '" + strconv.Itoa(SUCCESS) + "' WHERE barcode = '" + BarCode + "'" + " AND parentid = '" + strconv.Itoa(arrangeMain.Id) + "'"
				this.DBE.Exec(updatesql)

				updTaskSql := "UPDATE " + TABLE_TAST + " SET statuscode = '" + strconv.Itoa(SUCCESS) + "' WHERE  barcode = '" + BarCode + "' and task_id = '"+taskId+"'"
				this.DBE.Exec(updTaskSql)
			}
		}


	}
}

/**
 * @Author 徐春林
 * @Description // end 时更新
 * @Date 19:36 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) StatusModifyWithDetail(task_id string) error {
	status := SUCCESS
	// 关联查询申请详情表，申请主表中指定申请单下执行状态不为success的申请详情数量
	sql := "SELECT count(*) FROM `" + TABLE_ARRANGE_DETAIL + "` a  JOIN `" + TABLE_ARRANGE_MAIN + "` b ON a.parentid = b.Id WHERE (b.entryno = '" + task_id + "' and a.taskstatus <> " + strconv.Itoa(SUCCESS) + ")"

	var total int
	//_, err := this.DBE.Exec(sql)
	this.DBE.SQL(sql).Get(&total)

	//total ,_ := result.RowsAffected()
	fmt.Println("数量：", total)

	// 存在未执行成功的子任务
	if total > 0 {
		status = FAILED
	}

	// 更新申请主表状态
	updApplySql := "UPDATE " + TABLE_ARRANGE_MAIN + " SET taskstatus = '" + strconv.Itoa(status) + "' WHERE entryno = '" + task_id + "'"
	_, err := this.DBE.Exec(updApplySql)
	return err
}

/**
 * @Author 徐春林
 * @Description // 获取异常信息, 更新任务状态
 * @Date 20:08 2021/3/30
 * @Param
 * @return
 **/
func (this *ArrangeService) GetAbnormalDataAndUpdate(taskId string, entity ResponseEntity) {
	//获取反馈报文中的所有异常样本Id列表
	racks := entity.Data.Abnormal_data.Racks
	arrangeMain := this.GetArrangeMain(taskId)
	var shelf Shelf
	if len(racks) > 0  {
		for _, v := range racks {
			// 根据盒编码,任务编码查询 任务信息,任务存在更新状态
			sql := "SELECT count(*) FROM `" + TABLE_TAST + " WHERE task_id = '" + taskId + "' and BoxBarCode = '" + v.RackId + "'"

			var total int
			this.DBE.SQL(sql).Get(&total)
			fmt.Println("盒任务数量：", total)
			// 存在未执行成功的子任务
			if total > 0 {
				// 更新任务明细信息
				updatesql := "UPDATE " + TABLE_ARRANGE_DETAIL + " SET taskstatus = '" + strconv.Itoa(FAILED) + "' WHERE BoxBarCode = '" +  v.RackId + "' AND parentid = '" + strconv.Itoa(arrangeMain.Id) + "'"
				this.DBE.Exec(updatesql)
				updTaskSql := "UPDATE " + TABLE_TAST + " SET statuscode = '" + strconv.Itoa(FAILED) + "' WHERE  BoxBarCode = '" +  v.RackId + "' and task_id = '" + taskId + "'"
				this.DBE.Exec(updTaskSql)
				continue
			}
			// 不存在根据盒编码查询盒当前的架编码
			shelfIdsql := "SELECT ShelfId as Id FROM `" + TABLE_BOX + " WHERE Barcode = '" + v.RackId + "'"

			this.DBE.SQL(shelfIdsql).Get(&shelf)
			fmt.Println("盒的冻存架Id：", shelf.Id)

			// 根据架编码,任务Id 查询 任务信息, 任务存在更新状态
			// 更新任务明细信息
			updatesql := "UPDATE " + TABLE_ARRANGE_DETAIL + " SET taskstatus = '" + strconv.Itoa(FAILED) + "' WHERE ShelfId = " +strconv.Itoa( shelf.Id)+ " AND parentid = '" + strconv.Itoa(arrangeMain.Id) + "'"
			this.DBE.Exec(updatesql)

			// 更新任务状态
			updTaskSql := "UPDATE " + TABLE_TAST + " SET statuscode = '" + strconv.Itoa(FAILED) + "' WHERE  ShelfId = " +strconv.Itoa( shelf.Id)+ "and task_id = '" + taskId + "'"
			this.DBE.Exec(updTaskSql)
		}
	}
}

/**
 * @Author 徐春林
 * @Description // 获取整理主单信息
 * @Date 9:42 2021/3/31
 * @Param
 * @return
 **/
func (this *ArrangeService) GetArrangeMain(entryNo string) Bank_Arrange_Main {
	where := "entryNo = '" + entryNo + "'"
	var entity Bank_Arrange_Main
	sql := "SELECT * FROM bank_arrange_main WHERE " + where
	//results, err := this.DBE.Query(sql)
	//fmt.Println(results)
	_,err := this.DBE.SQL(sql).Get(&entity)
	fmt.Println("执行错误：", err)
	return entity
}

/**
 * @Author 徐春林
 * @Description // 获取整理范围的所有盒子, 根据盒子中样本数 更新空盒状态
 * @Date 9:42 2021/3/31
 * @Param
 * @return
 **/
func (this *ArrangeService) GetAllBox(taskId string)  {
	arrangeMain := this.GetArrangeMain(taskId)
	sql := "update bank_box a set a.IsEmpty = (case when (select count(b.Id) from bank_sample b where a.Id = b.BoxId) > 0 then 0 else 1 end) where a.EquipmentId = '"+utils.ToStr(arrangeMain.EquipmentId)+"'"
	this.DBE.Exec(sql)
}