tmrgo1/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 '集团端版本号';

ALTER TABLE `ftdetail`
    ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否',
    ADD COLUMN `is_show` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否显示 0 无效 1 显示 2 隐藏';
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;
update feedtemplet set group_version = version;


## SELECT * FROM `apisql` where sqlname = 'getFTdetailList'
SELECT
    TRIM(ftid)ftid,TRIM(fid)fid,
    IFNULL((SELECT fname  FROM feed WHERE pastureid =ftdetail.pastureid AND id = ftdetail.fid),ftdetail. fname) fname,
    fweight,islockcount,sort,feedgroup,TRIM(preftid)preftid,autosecond,TRIM(splitftpreid)splitftpreid,
    TRIM(ftdetail.id) id,
    TRIM(ftdetail.pastureid)pastureid,IFNULL(deviation,0) AS deviation
FROM
    ftdetail
WHERE ftdetail.pastureid = ?   AND ( ftid=?) AND is_show = 1
  AND ( fweight =? OR ? = '')
  AND ( autosecondname =?  OR ? = '')
  AND (islockcount =? OR ? = '')
  AND (sort =? OR ? = '')
  AND ( fname LIKE CONCAT('%',?,'%')  OR ? = '')
  AND ( feedgroup LIKE CONCAT('%',?,'%')  OR ? = '')
ORDER BY ftdetail.sort,ftdetail.id;

## 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.id DESC;

##  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` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增id',
   `pasture_id` bigint(20) unsigned NOT NULL 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='配方版本更新记录表';

CREATE TABLE `feed_detail_version_log` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键自增id',
   `pasture_id` bigint(20) unsigned NOT NULL COMMENT '牧场id',
   `version_id` int(11) unsigned NOT NULL COMMENT 'feed_detail_version表id',
   `feed_template_id` bigint(20) unsigned NOT NULL COMMENT '配方id',
   `forage_id` int(11) unsigned NOT NULL COMMENT '饲料id',
   `forage_name` varchar(255) NOT NULL DEFAULT '' COMMENT '饲料名称',
   `forage_group_name` varchar(155) NOT NULL DEFAULT '' COMMENT '饲料分组名称',
   `weight` int(11) unsigned NOT NULL COMMENT '重量',
   `stir_delay` int(11) unsigned NOT NULL COMMENT '搅拌延迟',
   `allow_error` int(11) unsigned NOT NULL COMMENT '允许误差',
   `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否',
   `is_lock_cow_count_ratio` tinyint(1) unsigned NOT NULL DEFAULT '2' COMMENT '是否锁定牛头数比例 1 是 2 否',
   `sort` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
   `is_show` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否删除 0 无效 1 未删除 2 已删除',
   `created` datetime NOT NULL COMMENT '创建时间',
   `updated` datetime NOT NULL COMMENT '更新时间',
   PRIMARY KEY (`id`),
   KEY `idx_feed_template_id` (`feed_template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='集团端最新配方详情表';

ALTER TABLE ftdetail DROP INDEX ftdetail_ftid,
ADD UNIQUE KEY ftdetail_ftid (`ftid`,`fid`,`pastureid`,`is_show`);