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,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 = ? and is_delete = 1 order by gear', 'tmr_id');

# 新增圈舍下拉框列表数据
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 = 'SELECT sort,fname,weight,LENGTH,bcode,ccount,speed,gear,useinbar FROM
(SELECT
a.sort,a.fname,a.lweight weight,a.fpid,
IF(a.useinbartype=0,"转投剩料',
IF(a.useinbartype=1,'撒','继续饲喂')
) useinbar,
CONCAT(a.fbarid) fbarid,
CONCAT(a.pid) pid,
b.bcode,b.`length`,c.`ccount`,d.speed,
#(a.`lweight` /  b.length) as  l1,
#(a.`lweight` *  f.`volume`) / d.volume_rate as l3,
#(60 * 60 / 1000 / d.speed) as l4,
(((a.`lweight` /  b.length) * f.`volume`) / d.volume_rate) / (60 * 60 / 1000 / d.speed)  AS gear
FROM
  downloadplandtl2 a
  JOIN bar b ON a.`fbarid` = b.`id`
  JOIN feedp c ON c.`barid` = a.`fbarid`
  JOIN tmr d ON d.id = ?
  JOIN feedtemplet f ON c.`ftid` = f.id
WHERE a.pastureid = ?
 AND a.pid= ?
AND (IF(?=0,a.useinbartype IN(0,2),a.useinbartype = 1 ) OR ?='')
ORDER BY a.sort
) AS B',params = 'tmrId,pastureid,id,useinbartype,useinbartype'
wHERE sqlname = 'getDownloadplandt2ListV2' and enable = 1;