kpt_xdmy/migration/v0002_alter_measure.sql

## set 1
ALTER TABLE `measure`
    ADD COLUMN `iotNumber` varchar(255) NOT NULL DEFAULT '' COMMENT 'IOT 编号' AFTER `pId`;


## set 2 apisql表查询语句 SELECT sqlstr,params FROM `apisql` WHERE sqlname = 'insertMeasure' AND ENABLE>0;
## sqlstr 需要更改的内容如下
insert into measure (departmentId,departmentName, location,  startAmount,lastAmount,endAmount, meterName, meterType,useType, price, employeId, pastureId,meterNumber ,lastDate,endDate,Multiple,iotNumber) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
## set 3 params 更新内容
## departmentId,departmentName,location,startAmount,startAmount,startAmount,formName,formType,useType,price,employeId, pastureId,formNumber,inputDatetime,inputDatetime,Multiple,iotNumber

## set 4 sqlstr 更新的语句 SELECT sqlstr,params FROM `apisql` WHERE sqlname = 'getMeasureList' AND ENABLE>0;
SELECT
(@i := @i + 1) i,
  a.id,
  a.meterName AS formName,
  a.meterType AS formType,
  a.useType,
  a.location,
  a.price,
  a.meterNumber AS formNumber,
  p.name pastureName,
  d.name departName,
  e.empname employeName,
  a.isInfo,
  a.`Multiple`,
  a.`departmentId`,
  a.`pastureId`,
  a.`employeId`,
  a.endAmount,
  a.iotNumber,
DATE_FORMAT(a.endDate, '%Y-%m-%d') endDate
FROM
  (SELECT
    @i := 0) AS i,
  measure a
  LEFT JOIN department p
    ON a.pastureId = p.id
  LEFT JOIN department d
    ON a.departmentId = d.id
  LEFT JOIN emp e
    ON a.employeId = e.id
WHERE (
    d.name LIKE CONCAT("%", ?, "%")
    OR ? = ''
  )
  AND (
    a.meterNumber LIKE CONCAT("%", ?, "%")
    OR ? = ''
  )
  AND (
    p.name LIKE CONCAT("%", ?, "%")
    OR ? = '现代牧业'
  )
 AND (
    a.meterType =  ?
    OR ? = ''
  )
 AND (
    a.useType=  ?
    OR ? = ''
  )
AND (
    a.meterName LIKE CONCAT("%", ?, "%")
    OR ? = ''
  )
  AND a.isDel = '0'
ORDER BY p.sort asc,a.meterType desc,a.meterNumber


## set 5  sqlstr 更新的语句 SELECT sqlstr,params FROM `apisql` WHERE sqlname = 'updateMeasure' AND ENABLE>0;
update measure set meterCode = ?,meterNumber=?,departmentId = ?, location = ?, startAmount = ?, meterName = ?,meterType =?, price = ?,employeId = ?,pastureId =?,isInfo= ?,Multiple=?,iotNumber=? where id = ?
## set 6 params
## formNumber,formNumber,departmentId, location,  startAmount, formName, formType, price, employeId, pastureId,isInfo,Multiple,iotNumber,id

## set 6 sqlstr SELECT sqlstr,params FROM `apisql` WHERE sqlname = 'importMeasureNew' AND ENABLE>0;
## call insert_measurenew(?,?,?,?,?,?,?,?,?,?,?,?,?)
## 牧场,表名称,表编号,表结构,表类型,部门,位置,最后一次抄表值,单价,管理人,倍率,iot编号,jwt_username