v0001_feedtemplet.sql 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207
  1. ALTER TABLE `feedtemplet`
  2. ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否';
  3. ALTER TABLE `ftdetail`
  4. ADD COLUMN `is_modify` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改 0 无效 1 是 2 否';
  5. ## SELECT * FROM apisql WHERE sqlname = 'getFTList' AND ENABLE >0
  6. ## 需要更新的语句如下
  7. SELECT * FROM ( SELECT
  8. tname,tcolor,ccid,ccname,fttype, remark,`enable`,source,fttypeid,tcode,is_modify,
  9. TRIM(id) id,
  10. TRIM(pastureid)pastureid,
  11. isissue,
  12. (SELECT GROUP_CONCAT( DISTINCT fp.`pastureName`) FROM feedtemplet_pasture fp
  13. WHERE fp.ftid=ft.`id` AND fp.pastureid =ft.pastureid ) grouppastures,
  14. (SELECT COUNT(1) FROM feedp WHERE pastureid = ft.pastureid AND (ptsfid = ft.id OR ftid = ft.id) LIMIT 1 ) AS state,
  15. IF((SELECT COUNT(1) FROM ftdetail_issue fi
  16. WHERE fi.pastureid = ft.pastureid AND fi.ftid = ft.id)+
  17. (SELECT COUNT(1)
  18. FROM `ftdetail` ftd
  19. WHERE ftd.pastureid = ft.pastureid AND ftd.ftid = ft.id)>0,1,0) AS isadjust,ft.sort
  20. FROM
  21. feedtemplet ft
  22. WHERE pastureid = ?
  23. AND (ccname LIKE CONCAT("%",?,"%") OR ?='')
  24. AND (tname LIKE CONCAT("%",?,"%") OR ?='')
  25. AND (fttype LIKE CONCAT("%",?,"%") OR ?='')
  26. ) f
  27. WHERE (f.enable = ? OR ? = '' ) AND (isadjust = ? OR ? = '') AND (source = IF( ? = 0 ,"自定义",IF (? = 1,"集团下发未调整",IF(? = 2,"集团下发有调整",IF(? = 3 ,"导入","")))) OR ? = '')
  28. ORDER BY f.sort ASC;
  29. ALTER TABLE cowclass ADD COLUMN group_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端id';
  30. ALTER TABLE feedclass ADD COLUMN group_id int(11) unsigned NOT NULL DEFAULT '0' COMMENT '集团端id';
  31. ALTER TABLE USER ADD COLUMN `imei` VARCHAR(255) DEFAULT NULL;
  32. ## SELECT * FROM apisql WHERE sqlname ='updatesysopt'
  33. ## 需要更新的语句如下sqlstr
  34. UPDATE `sysopt`
  35. SET inforvalue = CASE inforname
  36. WHEN 'isGetLastPlan' THEN ?
  37. WHEN 'isGetNextPlan' THEN ?
  38. WHEN 'isEnableSupplyFeed' THEN ?
  39. WHEN 'isEnableRemainFeed' THEN ?
  40. WHEN 'reportDigit' THEN ?
  41. WHEN 'isLockCount' THEN ?
  42. WHEN 'remainOpt' THEN ?
  43. WHEN 'waterOpt' THEN ?
  44. WHEN 'overweightWarnRate' THEN ?
  45. WHEN 'overweightBanRate' THEN ?
  46. WHEN 'repertoryWarn' THEN ?
  47. WHEN 'isfeedstorage' THEN ?
  48. WHEN 'times' THEN ?
  49. WHEN 'isEnableContract' THEN ?
  50. WHEN 'remainOptDis' THEN ?
  51. WHEN 'remainOptRate' THEN round(?/100,2)
  52. WHEN 'isSmallMaterial' THEN ?
  53. WHEN 'decimalPlaces' THEN ?
  54. WHEN 'isDataSync' THEN ?
  55. WHEN 'anyCar' THEN ?
  56. WHEN 'decimalRate' THEN ?
  57. WHEN 'accuracy' THEN ?
  58. WHEN 'sprinkleFeedTimeAllow' THEN ?
  59. WHEN 'domain' THEN ?
  60. END
  61. WHERE pastureid= ?;
  62. ## 需要更新的语句如下params
  63. # 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
  64. ## setp 1 需要增加的语句
  65. INSERT INTO sysopt (pastureid,inforname,inforvalue )VALUES (1653271339,"sprinkleFeedTimeAllow","10"),(1653271339,"domain","http://192.168.1.70:8081");
  66. ## setp 2 SELECT * FROM apisql WHERE sqlname ='getysoptList'
  67. SELECT TRIM(pastureid) pastureid,
  68. MAX(CASE
  69. WHEN inforname = 'isGetLastPlan' THEN
  70. inforvalue
  71. END
  72. ) AS isGetLastPlan,
  73. MAX(CASE
  74. WHEN inforname = 'isGetNextPlan' THEN
  75. inforvalue
  76. END
  77. ) AS isGetNextPlan,
  78. MAX(CASE
  79. WHEN inforname = 'isEnableSupplyFeed' THEN
  80. inforvalue
  81. END
  82. ) AS isEnableSupplyFeed,
  83. MAX(CASE
  84. WHEN inforname = 'isEnableRemainFeed' THEN
  85. inforvalue
  86. END
  87. ) AS isEnableRemainFeed,
  88. MAX(CASE
  89. WHEN inforname = 'reportDigit' THEN
  90. inforvalue
  91. END
  92. ) AS reportDigit,
  93. MAX(CASE
  94. WHEN inforname = 'isLockCount' THEN
  95. inforvalue
  96. END
  97. ) AS isLockCount,
  98. MAX(CASE
  99. WHEN inforname = 'remainOpt' THEN
  100. inforvalue
  101. END
  102. ) AS remainOpt,
  103. MAX(CASE
  104. WHEN inforname = 'waterOpt' THEN
  105. inforvalue
  106. END
  107. ) AS waterOpt,
  108. MAX(CASE
  109. WHEN inforname = 'overweightWarnRate' THEN
  110. inforvalue
  111. END
  112. ) AS overweightWarnRate,
  113. MAX(CASE
  114. WHEN inforname = 'overweightBanRate' THEN
  115. inforvalue
  116. END
  117. ) AS overweightBanRate,
  118. MAX(CASE
  119. WHEN inforname = 'repertoryWarn' THEN
  120. inforvalue
  121. END
  122. ) AS repertoryWarn,
  123. MAX(CASE
  124. WHEN inforname = 'isfeedstorage' THEN
  125. inforvalue
  126. END
  127. ) AS isfeedstorage,
  128. MAX(CASE
  129. WHEN inforname = 'times' THEN
  130. inforvalue
  131. END
  132. ) AS times,
  133. MAX(CASE
  134. WHEN inforname = 'isEnableContract' THEN
  135. inforvalue
  136. END
  137. ) AS isEnableContract,
  138. MAX(CASE
  139. WHEN inforname = 'remainOptDis' THEN
  140. inforvalue
  141. END
  142. ) AS remainOptDis,
  143. MAX(CASE
  144. WHEN inforname = 'remainOptRate' THEN
  145. inforvalue*100
  146. END
  147. ) AS remainOptRate,
  148. MAX(CASE
  149. WHEN inforname = 'isSmallMaterial' THEN
  150. inforvalue
  151. END
  152. ) AS isSmallMaterial,
  153. MAX(CASE
  154. WHEN inforname = 'decimalPlaces' THEN
  155. inforvalue
  156. END
  157. ) AS decimalPlaces,
  158. MAX(CASE
  159. WHEN inforname = 'isDataSync' THEN
  160. inforvalue
  161. END
  162. ) AS isDataSync,
  163. MAX(CASE
  164. WHEN inforname = 'anyCar' THEN
  165. inforvalue
  166. END
  167. ) as anyCar,
  168. MAX(CASE
  169. WHEN inforname = 'decimalRate' THEN
  170. inforvalue
  171. END
  172. ) as decimalRate,
  173. MAX(CASE
  174. WHEN inforname = 'accuracy' THEN
  175. inforvalue
  176. END
  177. ) as accuracy
  178. ,
  179. MAX(CASE
  180. WHEN inforname = 'wgSap' THEN
  181. inforvalue
  182. END
  183. ) as wgSap,
  184. MAX(CASE
  185. WHEN inforname = 'sprinkleFeedTimeAllow' THEN
  186. inforvalue
  187. END
  188. ) as sprinkleFeedTimeAllow,
  189. MAX(CASE
  190. WHEN inforname = 'domain' THEN
  191. inforvalue
  192. END
  193. ) as domain
  194. FROM `sysopt`
  195. WHERE pastureid = ?