tmrgo/migration/v0001_feedtemplet.sql

ALTER TABLE `feedtemplet`
    ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否',
    ADD COLUMN `group_data_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '集体端数据ID',
    ADD COLUMN `group_version` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集体端版本号';

update feedtemplet set group_version = version;

ALTER TABLE `ftdetail`
    ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否';

## SELECT * FROM apisql WHERE sqlname = 'getFTList' AND ENABLE >0
## 需要更新的语句如下
SELECT * FROM ( SELECT
    tname,tcolor,ccid,ccname,fttype, remark,`enable`,source,fttypeid,tcode,is_modify,version,
    TRIM(id) id,
    TRIM(pastureid)pastureid,
    isissue,
    (SELECT GROUP_CONCAT( DISTINCT fp.`pastureName`) FROM feedtemplet_pasture fp
     WHERE fp.ftid=ft.`id` AND fp.pastureid =ft.pastureid ) grouppastures,
    (SELECT COUNT(1) FROM feedp WHERE pastureid = ft.pastureid AND (ptsfid = ft.id OR ftid = ft.id) LIMIT 1  ) AS state,
    IF((SELECT COUNT(1) FROM ftdetail_issue fi
        WHERE fi.pastureid = ft.pastureid AND fi.ftid = ft.id)+
       (SELECT COUNT(1)
        FROM  `ftdetail` ftd
        WHERE ftd.pastureid = ft.pastureid AND ftd.ftid = ft.id)>0,1,0)  AS isadjust,ft.sort
FROM
    feedtemplet ft
WHERE pastureid = ?
  AND (ccname LIKE CONCAT("%",?,"%") OR ?='')
  AND (tname LIKE CONCAT("%",?,"%") OR ?='')
  AND (fttype LIKE CONCAT("%",?,"%") OR ?='')
) f
WHERE (f.enable = ? OR ? = '' ) AND (isadjust = ? OR ? = '') AND (source = IF( ? = 0 ,"自定义",IF (? = 1,"集团下发未调整",IF(? = 2,"集团下发有调整",IF(? = 3 ,"导入","")))) OR ? = '')
ORDER BY  f.sort ASC;


ALTER TABLE cowclass ADD COLUMN group_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端id';
ALTER TABLE feedclass ADD COLUMN group_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端id';
ALTER TABLE USER ADD COLUMN `imei` VARCHAR(255) DEFAULT NULL;

##  SELECT * FROM apisql WHERE sqlname  ='updatesysopt'
## 需要更新的语句如下sqlstr

UPDATE `sysopt`
SET inforvalue = CASE inforname
WHEN 'isGetLastPlan' THEN ?
WHEN 'isGetNextPlan' THEN ?
WHEN 'isEnableSupplyFeed' THEN ?
WHEN 'isEnableRemainFeed' THEN  ?
WHEN 'reportDigit' THEN  ?
WHEN 'isLockCount' THEN ?
WHEN 'remainOpt' THEN ?
WHEN 'waterOpt' THEN ?
WHEN 'overweightWarnRate' THEN  ?
WHEN 'overweightBanRate' THEN  ?
WHEN 'repertoryWarn' THEN ?
WHEN 'isfeedstorage' THEN ?
WHEN 'times' THEN ?
WHEN 'isEnableContract' THEN  ?
WHEN 'remainOptDis' THEN  ?
WHEN 'remainOptRate' THEN round(?/100,2)
WHEN 'isSmallMaterial' THEN ?
WHEN 'decimalPlaces' THEN ?
WHEN 'isDataSync' THEN ?
WHEN 'anyCar' THEN ?
WHEN 'decimalRate' THEN ?
WHEN 'accuracy' THEN ?
WHEN 'sprinkleFeedTimeAllow' THEN ?
WHEN 'domain' THEN ?
END
WHERE pastureid= ?;

## 需要更新的语句如下params
# isGetLastPlan,isGetNextPlan,isEnableSupplyFeed,isEnableRemainFeed,reportDigit,isLockCount,remainOpt,waterOpt,overweightWarnRate,overweightBanRate,repertoryWarn,isfeedstorage,times,isEnableContract,remainOptDis,remainOptRate,isSmallMaterial,decimalPlaces,isDataSync,anyCar,decimalRate,accuracy,sprinkleFeedTimeAllow,domain,pastureid


##  setp 1 需要增加的语句
INSERT INTO sysopt (pastureid,inforname,inforvalue )VALUES (1653271339,"sprinkleFeedTimeAllow","10"),(1653271339,"domain","http://192.168.1.70:8081");

##  setp 2  SELECT * FROM apisql WHERE sqlname  ='getysoptList'
SELECT TRIM(pastureid) pastureid,

       MAX(CASE
               WHEN inforname = 'isGetLastPlan' THEN
                   inforvalue
           END
           ) AS isGetLastPlan,
       MAX(CASE
               WHEN inforname = 'isGetNextPlan' THEN
                   inforvalue
           END
           ) AS isGetNextPlan,
       MAX(CASE
               WHEN inforname = 'isEnableSupplyFeed' THEN
                   inforvalue
           END
           ) AS isEnableSupplyFeed,
       MAX(CASE
               WHEN inforname = 'isEnableRemainFeed' THEN
                   inforvalue
           END
           ) AS isEnableRemainFeed,
       MAX(CASE
               WHEN inforname = 'reportDigit' THEN
                   inforvalue
           END
           ) AS reportDigit,
       MAX(CASE
               WHEN inforname = 'isLockCount' THEN
                   inforvalue
           END
           ) AS isLockCount,
       MAX(CASE
               WHEN inforname = 'remainOpt' THEN
                   inforvalue
           END
           ) AS remainOpt,
       MAX(CASE
               WHEN inforname = 'waterOpt' THEN
                   inforvalue
           END
           ) AS waterOpt,
       MAX(CASE
               WHEN inforname = 'overweightWarnRate' THEN
                   inforvalue
           END
           ) AS overweightWarnRate,
       MAX(CASE
               WHEN inforname = 'overweightBanRate' THEN
                   inforvalue
           END
           ) AS overweightBanRate,
       MAX(CASE
               WHEN inforname = 'repertoryWarn' THEN
                   inforvalue
           END
           ) AS repertoryWarn,
       MAX(CASE
               WHEN inforname = 'isfeedstorage' THEN
                   inforvalue
           END
           ) AS  isfeedstorage,
       MAX(CASE
               WHEN inforname = 'times' THEN
                   inforvalue
           END
           ) AS times,
       MAX(CASE
               WHEN inforname = 'isEnableContract' THEN
                   inforvalue
           END
           ) AS isEnableContract,
       MAX(CASE
               WHEN inforname = 'remainOptDis' THEN
                   inforvalue
           END
           ) AS remainOptDis,
       MAX(CASE
               WHEN inforname = 'remainOptRate' THEN
                       inforvalue*100
           END
           ) AS remainOptRate,
       MAX(CASE
               WHEN inforname = 'isSmallMaterial' THEN
                   inforvalue
           END
           ) AS isSmallMaterial,
       MAX(CASE
               WHEN inforname = 'decimalPlaces' THEN
                   inforvalue
           END
           ) AS decimalPlaces,
       MAX(CASE
               WHEN inforname = 'isDataSync' THEN
                   inforvalue
           END
           ) AS isDataSync,
       MAX(CASE
               WHEN inforname = 'anyCar' THEN
                   inforvalue
           END
           ) as anyCar,
       MAX(CASE
               WHEN inforname = 'decimalRate' THEN
                   inforvalue
           END
           ) as decimalRate,
       MAX(CASE
               WHEN inforname = 'accuracy' THEN
                   inforvalue
           END
           ) as accuracy
        ,
       MAX(CASE
               WHEN inforname = 'wgSap' THEN
                   inforvalue
           END
           ) as wgSap,
       MAX(CASE
              WHEN inforname = 'sprinkleFeedTimeAllow' THEN
                   inforvalue
              END
            ) as sprinkleFeedTimeAllow,
       MAX(CASE
               WHEN inforname = 'domain' THEN
                   inforvalue
           END
           ) as domain

FROM `sysopt`
WHERE pastureid = ?;

## 配方版本更新记录表
CREATE TABLE `feed_detail_version_log` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增id',
   `feed_template_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '配方id',
   `group_version` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '版本号',
   `belong` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '配方归属 0 无效 1 归属集团端 2 归属牧场端',
   `created_at` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
   `updated_at` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
   PRIMARY KEY (`id`),
   KEY `idx_feed_template_id` (`feed_template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配方版本更新记录表'