tmrgo/migration/v0002_yq_bar.sql

# 新增圈舍名称字段pen和长度length字段
UPDATE apisql SET sqlstr = 'SELECT
bcode,bname,allowratio,autozone,autosecond,isstart,
begintag,endtag,sort,enable,autosecondname,length,pen,
TRIM(id) id,
TRIM(pastureid)pastureid,
udcode,udname
FROM
  bar
WHERE pastureid = ?
 AND ( bname LIKE CONCAT(''%'',?,''%'')  OR ? = '''')
 AND ( bcode LIKE CONCAT(''%'',?,''%'')  OR ? = '''')
 AND ( pen LIKE CONCAT(''%'',?,''%'')  OR ? = '''')
 AND (allowratio = ? OR ? = '''')
 AND (enable =? OR ? = '''')
 AND (autozone= ? OR ? = '''')
 AND (autosecond = ? OR ? = '''')
ORDER BY sort ',
params = 'pastureid,bname,bname,bcode ,bcode,pen,pen ,allowratio ,allowratio ,enable ,enable,autozone,autozone,autosecond,autosecond'
where sqlname = 'getBarList' and enable = 1;


# 新增圈舍名称字段pen和长度length字段
UPDATE apisql SET sqlstr = '
update bar set bcode=? ,bname=?,allowratio=? ,autozone=?,autosecond=?,autosecondname=?,isstart=? ,enable=?,AUFNR = ?,pen = ?,length = ?
where pastureid=? and id=?',
params = 'bcode,bname,allowratio,autozone,autosecond,autosecondname,isstart,enable,AUFNR,pen,length,pastureid,id'
where sqlname = 'updateBar' and enable = 1;

# 新增speend 车速字段
UPDATE apisql SET sqlstr ="SELECT 0 islock,
eqcode,tname,datacaptureno,trim(tclassid) tclassid,
(SELECT dictlist.label FROM dictlist JOIN dict ON dict.id=dictlist.pid WHERE dict.name='TMR设备类型' AND dict.ENABLE=1  and dictlist .enable=1  and value = tclassid) as tclassname,
maxstirfeed,remark,enable,TRIM(id) id,TRIM(pastureid)pastureid,
tcolor,imei,ifnull(datainterface,1),autozone,autosecond,if(autosecondname= '' or autosecondname is null,'禁用',autosecondname ) autosecondname,(select pasture_name from pasture where pastureid = tmr.pastureid) as pasturename,
ifnull(gps,'')  gps ,volume,speed,
TRIM(TRAILING ''.'' FROM TRIM(TRAILING ''0'' FROM FORMAT(volume_rate, 20))) AS volume_rate
FROM
  tmr
WHERE pastureid = ?  and  if(?=0,tclassid in (0,6,7),tclassid in (1,2,3,4))
  AND (tmr.eqcode LIKE CONCAT('%',?,'%')  OR ? = '')
 AND (tmr.tclassname =? OR ? = '')
AND (tmr.tname =? OR ? = '')
  AND (tmr.enable =? OR ? = '')  AND (tmr.imei =? OR ? = '')
ORDER BY tmr.id DESC",params = 'pastureid,eqtype,eqcode,eqcode,classname,classname,tname,tname,enable,enable,imei,imei'
where sqlname = 'getTmrList' and enable = 1;

# 新增speend 车速字段
update  apisql SET sqlstr ="update tmr join dictlist  JOIN `dict` ON `dict`.`id`=dictlist.`pid` set eqcode=? ,tname=? ,datacaptureno=?,tclassid=?,tclassname=? ,maxstirfeed=?,tmr.enable=?,remark=? ,tcolor = ?,imei=?,datainterface=?,autozone=if(?='',null,?),autosecond=if(?='',null,?),  autosecondname=dictlist.label,gps = ?,volume = ?,speed = ifnull(?,0), volume_rate = ? where tmr.pastureid=? and tmr.id= ? and `dict`.`name`='跳转延时' AND `dict`.ENABLE=1  and dictlist.enable=1 and  dictlist.`value` =? ",
params = 'eqcode,tname,datacaptureno,tclassid,tclassname,maxstirfeed,enable,remark,tcolor,imei,datainterface,autozone,autozone,autosecond,autosecond,gps,volume,speed,volume_rate,pastureid,id,autosecond'
where sqlname = 'updateTMR' and enable = 1;

# 新增TMR设备撒料档位操作
insert into apisql(sqlname,sqlstr,params) values ('getTmrGearList','select * from tmr_gear where tmr_id = ? order by rev', 'tmrId');

# 新增圈舍下拉框列表数据
insert into apisql(sqlname,sqlstr,params) values ('getPenList','select id,pen from bar where pastureid = ? and enable = 1 and isdelete = 0 and pen is not null group by pen order by sort', 'pastureid');

ALTER TABLE `tmr`  ADD COLUMN `volume_rate` decimal(12,5) NOT NULL COMMENT '撒料机每转体积';

# 新增圈舍
update apisql SET sqlstr = "INSERT INTO bar
(pastureid,bcode,bname,allowratio,autozone,autosecond,autosecondname,isstart,enable,sort,AUFNR,pen,length)
VALUES (?,?,?,?,?,?,?,?,?,( select max(b.sort) + 1 from bar b where b.pastureid = ? ),?,?,?)",
params = 'pastureid,bcode,bname,allowratio,autozone,autosecond,autosecondname,isstart,enable,pastureid,AUFNR,pen,length'
where sqlname = 'insertBar' and enable = 1;

# 修改栏舍索引
delete table from bar;

# 新建栏舍表
CREATE TABLE `bar` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '栏舍id',
    `pastureid` BIGINT(20) UNSIGNED NOT NULL COMMENT '牧场id',
    `bcode` VARCHAR(50) DEFAULT NULL COMMENT '栏舍编号',
    `bname` VARCHAR(200) NOT NULL COMMENT '栏舍名称',
    `allowratio` INT(11) DEFAULT 0 COMMENT '允许误差数',
    `autozone` INT(11) DEFAULT 0 COMMENT '跳转重量域（kg）',
    `autosecond` INT(4) DEFAULT 0 COMMENT '跳转延时值',
    `autosecondname` VARCHAR(50) DEFAULT NULL COMMENT '跳转延时名称',
    `isstart` TINYINT(1) DEFAULT 1 COMMENT '确认开始',
    `begintag` INT(11) DEFAULT NULL,
    `endtag` INT(11) DEFAULT NULL,
    `sort` INT(11) DEFAULT 0 COMMENT '排序',
    `enable` TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    `isdelete` TINYINT(1) DEFAULT 0 COMMENT '是否删除（0为未删除，1为伪删除）',
    `backup1` VARCHAR(255) DEFAULT NULL COMMENT '备用1',
    `backup2` VARCHAR(255) DEFAULT NULL COMMENT '备用2',
    `AUFNR` VARCHAR(50) DEFAULT NULL COMMENT 'sap牛群订单行号',
    `class` VARCHAR(50) DEFAULT NULL COMMENT '牛舍类型',
    `classcode` VARCHAR(50) DEFAULT NULL COMMENT '牛舍类型编码',
    `cattle` VARCHAR(50) DEFAULT NULL COMMENT '牛群',
    `cattlecode` VARCHAR(50) DEFAULT NULL COMMENT '牛群编码',
    `sapCode` VARCHAR(50) DEFAULT NULL,
    `udcode` VARCHAR(50) DEFAULT NULL,
    `udname` VARCHAR(50) DEFAULT NULL,
    `length` DECIMAL(12,0) NOT NULL DEFAULT 0 COMMENT '栏舍长度',
    `pen` VARCHAR(30) DEFAULT NULL COMMENT '圈舍',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `ind_pastureId_bname` (`pastureid`,`bname`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='栏舍';

# 删除tmr表
delete table from tmr;

CREATE TABLE `tmr` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'tmr',
    `pastureid` BIGINT(20) UNSIGNED NOT NULL,
    `eqcode` CHAR(20) DEFAULT NULL COMMENT 'tmr设备编号',
    `tname` VARCHAR(20) NOT NULL COMMENT 'tmr车名称',
    `datacaptureno` INT(11) NOT NULL DEFAULT 0 COMMENT '数据采集卡编号',
    `tclassid` BIGINT(20) NOT NULL COMMENT 'tmr设备类型',
    `tclassname` VARCHAR(50) DEFAULT NULL,
    `ttype` TINYINT(4) DEFAULT NULL COMMENT '设备类型 0铲车，1tmr,3撒料设备，4小料',
    `maxstirfeed` INT(11) DEFAULT 0 COMMENT '最大搅料量（kg）（默认0）',
    `sort` INT(11) NOT NULL DEFAULT 0 COMMENT '顺序',
    `enable` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '启用',
    `inorout` SMALLINT(1) NOT NULL DEFAULT 0 COMMENT '当前是否撒料(默认2车不在使用，1撒料，0是做料)',
    `projid` BIGINT(20) DEFAULT 0 COMMENT '当前执行计划',
    `lastsort` VARCHAR(20) DEFAULT NULL COMMENT '上一个料',
    `lastweight` DECIMAL(20,3) DEFAULT NULL COMMENT '上次目标重量',
    `lastbuttontime` DATETIME DEFAULT NULL COMMENT '上次执行时间',
    `nextsort` BIGINT(20) DEFAULT 0 COMMENT '下一个料',
    `nextweight` DECIMAL(20,3) DEFAULT NULL COMMENT '下次目标重量',
    `issounding` INT(11) DEFAULT 0,
    `tcolor` VARCHAR(50) DEFAULT '#ccc',
    `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
    `imei` VARCHAR(255) DEFAULT NULL COMMENT 'imei唯一标识',
    `datainterface` VARCHAR(255) DEFAULT NULL COMMENT '数据接口',
    `autozone` INT(11) DEFAULT NULL COMMENT '跳转重量（kg',
    `autosecond` INT(11) DEFAULT NULL COMMENT '跳转延时值',
    `autosecondname` VARCHAR(50) DEFAULT NULL COMMENT '跳转延时名称',
    `lastinorout` INT(1) DEFAULT 0,
    `gps` VARCHAR(50) DEFAULT NULL,
    `volume` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '容积',
    `speed` DECIMAL(12,0) NOT NULL DEFAULT 0 COMMENT '速度',
    `volume_rate` DECIMAL(12,5) NOT NULL COMMENT '撒料机每转体积',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `tmr_datacaptureno` (`datacaptureno`,`pastureid`) USING BTREE,
    UNIQUE KEY `tmr_tname` (`pastureid`,`tname`,`ttype`) USING BTREE,
    UNIQUE KEY `tmr_eqcode` (`pastureid`,`eqcode`,`ttype`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='TMR设备表';

# 新增TMR 栏舍表
update apisql SET sqlstr = "INSERT INTO tmr(pastureid,eqcode,tname,datacaptureno,tclassid,tclassname,maxstirfeed,
                `enable`,remark,ttype,tcolor,imei,datainterface,autozone,autosecond,autosecondname,gps,volume,speed,volume_rate)
VALUES (?,?,?,?,?,?,?,?,?,if(?<>0,1,0),?,if(?='''',null,?),?,if(?='''',null,?),if(?='''',null,?),?,?,?,ifnull(?,0),?)",
params = 'pastureid,eqcode,tname,datacaptureno,tclassid,tclassname,maxstirfeed,enable,remark,tclassid,tcolor,imei,imei,datainterface,autozone,autozone,autosecond,autosecond,autosecondname,gps,volume,speed,volume_rate'
where sqlname = 'insertTmr' and enable = 1;

update apisql SET sqlstr = "SELECT bname,TRIM(id) id FROM bar WHERE pastureid = ? and pen = ? and enable=1 ORDER BY id DESC",
                  params = "pastureid,pen"
where sqlname = 'getBarListEnable' and enable = 1;