TaskData.go 45 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552
  1. package Task
  2. import (
  3. "ColdVerify_local/conf"
  4. "ColdVerify_local/lib"
  5. "ColdVerify_local/logs"
  6. "encoding/json"
  7. "fmt"
  8. "github.com/astaxie/beego/cache"
  9. _ "github.com/astaxie/beego/cache/redis"
  10. "github.com/beego/beego/v2/adapter/orm"
  11. orm2 "github.com/beego/beego/v2/client/orm"
  12. "github.com/go-sql-driver/mysql"
  13. _ "github.com/go-sql-driver/mysql"
  14. "math"
  15. "strconv"
  16. "strings"
  17. "time"
  18. )
  19. const (
  20. Temperature = "Temperature"
  21. Humidity = "Humidity"
  22. )
  23. // 模板 func TestBeego(t *testing.T) {
  24. type TaskData struct {
  25. Id int `orm:"column(ID);size(11);auto;pk"`
  26. T_id string `orm:"size(256);null"` // 标题
  27. T_t float32 `orm:"size(10);null"` // 温度
  28. T_rh float32 `orm:"size(10);null"` // 湿度
  29. T_time time.Time `orm:"type(timestamp);null;"` // 采集时间
  30. CreateTime time.Time `orm:"column(create_time);type(timestamp);null;auto_now_add"` //auto_now_add 第一次保存时才设置时间 UpdateTime time.Time `orm:"column(update_time);type(timestamp);null;auto_now"` //auto_now 每次 model 保存时都会对时间自动更新
  31. }
  32. type TaskData_Time_Min_Max_GROUP struct {
  33. T_max float32 // 温度
  34. T_max_id string // 温度
  35. T_min float32 // 湿度
  36. T_min_id string // 湿度
  37. T_times string // 采集时间
  38. }
  39. func (t *TaskData) TableName() string {
  40. return "task_data" // 数据库名称 // ************** 替换 FormulaList **************
  41. }
  42. var redisCache_TaskData cache.Cache
  43. func init() {
  44. //orm2.Debug = true
  45. config := fmt.Sprintf(`{"key":"%s","conn":"%s","dbNum":"%s","password":"%s"}`,
  46. "redis_"+"TaskData", conf.Redis_address, conf.Redis_dbNum, conf.Redis_password)
  47. logs.Println(config)
  48. var err error
  49. redisCache_TaskData, err = cache.NewCache("redis", config)
  50. if err != nil || redisCache_TaskData == nil {
  51. errMsg := "failed to init redis"
  52. logs.Println(errMsg, err)
  53. }
  54. }
  55. type TaskDataJPG struct {
  56. State int `json:"state"` //1:生成中 2:已完成 3:失败
  57. Msg string `json:"msg"`
  58. Url string `json:"url"` //url
  59. }
  60. // ---------------- Redis -------------------
  61. // Redis_Set(m.T_sn,m) // Redis 更新缓存
  62. func Redis_TaskDataJPG_Set(key string, r TaskDataJPG) (err error) {
  63. //json序列化
  64. str, err := json.Marshal(r)
  65. if err != nil {
  66. logs.Error(lib.FuncName(), err)
  67. return
  68. }
  69. err = redisCache_TaskData.Put(key, str, 5*time.Minute)
  70. if err != nil {
  71. logs.Println("set key:", key, ",value:", str, err)
  72. }
  73. return
  74. }
  75. // if r,is :=Redis_Get(T_sn);is{
  76. // return r,nil
  77. // }
  78. func Redis_TaskDataJPG_Get(key string) (r TaskDataJPG, is bool) {
  79. if redisCache_TaskData.IsExist(key) {
  80. logs.Println("找到key:", key)
  81. v := redisCache_TaskData.Get(key)
  82. json.Unmarshal(v.([]byte), &r)
  83. return r, true
  84. }
  85. logs.Println("没有 找到key:", key)
  86. return TaskDataJPG{}, false
  87. }
  88. func Redis_TaskDataJPG_Del(key string) (err error) {
  89. err = redisCache_TaskData.Delete(key)
  90. return
  91. }
  92. // 创建数据库 Device.CREATE_TaskData("")
  93. func CREATE_TaskData(alias_name, T_task_id string) bool {
  94. o := orm2.NewOrmUsingDB(alias_name)
  95. sql := "DROP TABLE IF EXISTS `z_task_data_" + T_task_id + "`"
  96. o.Raw(sql).Exec()
  97. sql = "CREATE TABLE IF NOT EXISTS `z_task_data_" + T_task_id + "` ( " +
  98. " `ID` int(11) NOT NULL AUTO_INCREMENT," +
  99. " `t_sn` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL," +
  100. " `t_id` varchar(256) DEFAULT NULL," +
  101. " `t_t` float(6, 1) NULL DEFAULT NULL," +
  102. " `t_rh` float(6, 1) NULL DEFAULT NULL," +
  103. " `t_time` datetime(0) NULL DEFAULT NULL," +
  104. " PRIMARY KEY (`ID`) USING BTREE," +
  105. " KEY `t_sn` (`t_sn`)," +
  106. " KEY `t_id` (`t_id`)," +
  107. " KEY `t_time` (`t_time`)" +
  108. ") ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;"
  109. _, err := o.Raw(sql).Exec()
  110. if err != nil {
  111. return false
  112. }
  113. //sqlIndex := "ALTER TABLE `z_task_data_" + T_task_id + "` add unique index(`t_sn`,`t_id`,`t_time`);"
  114. //logs.Debug(sqlIndex)
  115. //o.Raw(sqlIndex).Exec()
  116. return true
  117. }
  118. // ---------------- 特殊方法 -------------------
  119. // 清空
  120. func Truncate_TaskData(alias_name, T_task_id string) bool {
  121. o := orm2.NewOrmUsingDB(alias_name)
  122. sql := "truncate table z_task_data_" + T_task_id
  123. logs.Println(sql)
  124. _, err := o.Raw(sql).Exec()
  125. if err != nil {
  126. // 数据库表不存在,则创建数据库
  127. if err.(*mysql.MySQLError).Number == 1146 {
  128. CREATE_TaskData(alias_name, T_task_id)
  129. return true
  130. }
  131. logs.Error(lib.FuncName(), err)
  132. return false
  133. }
  134. return true
  135. }
  136. type TaskData_ struct {
  137. ID int `orm:"column(ID);size(100);null"` // ID
  138. T_sn string `orm:"column(t_sn);size(256);null"` // sn
  139. T_id string `orm:"column(t_id);size(256);null"` // 标题
  140. T_t float32 `orm:"column(t_t);size(10);null"` // 温度
  141. T_rh float32 `orm:"column(t_rh);size(10);null"` // 湿度
  142. T_time string `orm:"column(t_times);null;"` // 采集时间
  143. T_Certificate_sn string `orm:"size(256);null"` // 证书编号
  144. }
  145. type TaskDataClass_ struct {
  146. T_sn string `orm:"column(t_sn);size(256);null"` // 标题
  147. T_id string `orm:"column(t_id);size(256);null"` // 名称
  148. }
  149. type TaskData_AVG struct {
  150. T_t float32 `orm:"column(t_t);size(10);null"` // 温度
  151. T_time string `orm:"column(t_times);null;"` // 采集时间
  152. }
  153. func Read_TaskData_ById_List(T_task_id string, SN string, T_id string, Time_start_ string, Time_end_ string, page int, page_z int) ([]TaskData_, int64) {
  154. o := orm.NewOrm()
  155. var maps []TaskData_
  156. var maps_z []orm2.ParamsList
  157. pagez := page_z
  158. var offset int
  159. if page <= 1 {
  160. offset = 0
  161. } else {
  162. page -= 1
  163. offset = page * pagez
  164. }
  165. sql_condition := ""
  166. if len(Time_start_) > 1 {
  167. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  168. }
  169. if len(Time_end_) > 1 {
  170. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  171. }
  172. if len(T_id) > 0 {
  173. sql_condition += " AND t_id = '" + T_id + "'"
  174. }
  175. if len(SN) > 0 {
  176. sql_condition += " AND t_sn = '" + SN + "'"
  177. }
  178. if len(sql_condition) > 0 {
  179. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  180. }
  181. sql := "SELECT COUNT(ID) FROM z_task_data_" + T_task_id + sql_condition
  182. fmt.Println(sql)
  183. _, err := o.Raw(sql).ValuesList(&maps_z)
  184. if err != nil {
  185. return maps, 0
  186. }
  187. if len(maps_z) == 0 {
  188. return maps, 0
  189. }
  190. //fmt.Println("maps_z;",maps_z[0][0])
  191. sql = "SELECT ID,t_sn,t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times,t_time FROM z_task_data_" + T_task_id + sql_condition + " ORDER BY t_time DESC"
  192. if page_z != 9999 {
  193. sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
  194. }
  195. fmt.Println(sql)
  196. _, err = o.Raw(sql).QueryRows(&maps)
  197. if err != nil {
  198. logs.Error(lib.FuncName(), err)
  199. }
  200. //value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
  201. key, _ := strconv.Atoi(maps_z[0][0].(string))
  202. return maps, int64(key)
  203. }
  204. func Read_TaskData_ById_List_AES(T_task_id string, SN string, T_id string, Time_start_ string, Time_end_ string, page int, page_z int) ([]TaskData_, int64) {
  205. o := orm.NewOrm()
  206. var maps []TaskData_
  207. var maps_z []orm2.ParamsList
  208. pagez := page_z
  209. var offset int
  210. if page <= 1 {
  211. offset = 0
  212. } else {
  213. page -= 1
  214. offset = page * pagez
  215. }
  216. sql_condition := ""
  217. if len(Time_start_) > 1 {
  218. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  219. }
  220. if len(Time_end_) > 1 {
  221. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  222. }
  223. if len(T_id) > 0 {
  224. sql_condition += " AND t_id = '" + T_id + "'"
  225. }
  226. if len(SN) > 0 {
  227. if strings.Contains(SN, "|") {
  228. // 将字符串按 | 分割
  229. ids := strings.Split(strings.TrimSuffix(SN, "|"), "|")
  230. // 构建 SQL 查询
  231. query := " AND t_sn IN ("
  232. // 动态添加每个 id
  233. for i, id := range ids {
  234. query += "'" + id + "'"
  235. if i < len(ids)-1 {
  236. query += ", " // 添加逗号分隔
  237. }
  238. }
  239. query += ") " // 结束 SQL 查询
  240. } else {
  241. sql_condition += " AND t_sn = '" + SN + "'"
  242. }
  243. }
  244. if len(sql_condition) > 0 {
  245. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  246. }
  247. sql := "SELECT COUNT(ID) FROM z_task_data_" + T_task_id + sql_condition
  248. fmt.Println(sql)
  249. _, err := o.Raw(sql).ValuesList(&maps_z)
  250. if err != nil {
  251. return maps, 0
  252. }
  253. if len(maps_z) == 0 {
  254. return maps, 0
  255. }
  256. //fmt.Println("maps_z;",maps_z[0][0])
  257. sql = "SELECT ID,t_sn,t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times,t_time FROM z_task_data_" + T_task_id + sql_condition + " ORDER BY t_time"
  258. if page_z != 9999 {
  259. sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
  260. }
  261. fmt.Println(sql)
  262. _, err = o.Raw(sql).QueryRows(&maps)
  263. if err != nil {
  264. logs.Error(lib.FuncName(), err)
  265. }
  266. //value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
  267. key, _ := strconv.Atoi(maps_z[0][0].(string))
  268. return maps, int64(key)
  269. }
  270. func Read_TaskData_ById_List_SQL(T_task_id string, SQL, SN string, T_id string, Time_start_ string, Time_end_ string, page int, page_z int) ([]TaskData_, int64) {
  271. o := orm.NewOrm()
  272. var maps []TaskData_
  273. var maps_z []orm2.ParamsList
  274. pagez := page_z
  275. var offset int
  276. if page <= 1 {
  277. offset = 0
  278. } else {
  279. page -= 1
  280. offset = page * pagez
  281. }
  282. sql_condition := ""
  283. if len(Time_start_) > 1 {
  284. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  285. }
  286. if len(Time_end_) > 1 {
  287. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  288. }
  289. if len(T_id) > 0 {
  290. sql_condition += " AND t_id = '" + T_id + "'"
  291. }
  292. if len(SQL) > 0 {
  293. sql_condition += SQL
  294. }
  295. if len(SN) > 0 {
  296. if strings.Contains(SN, "|") {
  297. // 将字符串按 | 分割
  298. ids := strings.Split(strings.TrimSuffix(SN, "|"), "|")
  299. // 构建 SQL 查询
  300. query := " AND t_sn IN ("
  301. // 动态添加每个 id
  302. for i, id := range ids {
  303. query += "'" + id + "'"
  304. if i < len(ids)-1 {
  305. query += ", " // 添加逗号分隔
  306. }
  307. }
  308. query += ") " // 结束 SQL 查询
  309. } else {
  310. sql_condition += " AND t_sn = '" + SN + "'"
  311. }
  312. }
  313. if len(sql_condition) > 0 {
  314. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  315. }
  316. sql := "SELECT COUNT(ID) FROM z_task_data_" + T_task_id + sql_condition
  317. fmt.Println(sql)
  318. _, err := o.Raw(sql).ValuesList(&maps_z)
  319. if err != nil {
  320. return maps, 0
  321. }
  322. if len(maps_z) == 0 {
  323. return maps, 0
  324. }
  325. //fmt.Println("maps_z;",maps_z[0][0])
  326. sql = "SELECT ID,t_sn,t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times,t_time FROM z_task_data_" + T_task_id + sql_condition + " ORDER BY t_time"
  327. if page_z != 9999 {
  328. sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
  329. }
  330. fmt.Println(sql)
  331. _, err = o.Raw(sql).QueryRows(&maps)
  332. if err != nil {
  333. logs.Error(lib.FuncName(), err)
  334. }
  335. //value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
  336. key, _ := strconv.Atoi(maps_z[0][0].(string))
  337. return maps, int64(key)
  338. }
  339. func Read_TaskData_ById_List_(T_task_id string, SN string) []TaskData_ {
  340. o := orm.NewOrm()
  341. var maps []TaskData_
  342. sql_condition := ""
  343. sql_condition += " t_sn ='" + SN + "'"
  344. //fmt.Println("maps_z;",maps_z[0][0])
  345. sql := "SELECT ID,t_sn,t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i') AS t_times,t_time FROM z_task_data_" + T_task_id + " WHERE " + sql_condition + " ORDER BY t_time "
  346. fmt.Println(sql)
  347. _, err := o.Raw(sql).QueryRows(&maps)
  348. if err != nil {
  349. logs.Error(lib.FuncName(), err)
  350. return maps
  351. }
  352. return maps
  353. }
  354. func Read_TaskData_ById_AVG(T_task_id string, SN string, Time_start_ string, Time_end_ string) []TaskData_AVG {
  355. o := orm.NewOrm()
  356. var maps []TaskData_AVG
  357. sql_condition := ""
  358. if len(Time_start_) > 1 {
  359. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  360. }
  361. if len(Time_end_) > 1 {
  362. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  363. }
  364. if len(SN) > 0 {
  365. if strings.Contains(SN, "|") {
  366. // 将字符串按 | 分割
  367. ids := strings.Split(strings.TrimSuffix(SN, "|"), "|")
  368. // 构建 SQL 查询
  369. query := " AND t_sn IN ("
  370. // 动态添加每个 id
  371. for i, id := range ids {
  372. query += "'" + id + "'"
  373. if i < len(ids)-1 {
  374. query += ", " // 添加逗号分隔
  375. }
  376. }
  377. query += ") " // 结束 SQL 查询
  378. } else {
  379. sql_condition += " AND t_sn = '" + SN + "'"
  380. }
  381. }
  382. if len(sql_condition) > 0 {
  383. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  384. }
  385. sql := "SELECT DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times, AVG(t_t) AS t_t FROM z_task_data_" + T_task_id + sql_condition + " GROUP BY t_time ORDER BY t_time"
  386. fmt.Println(sql)
  387. o.Raw(sql).QueryRows(&maps)
  388. return maps
  389. }
  390. func Read_TaskData_ById_ClassList(T_task_id string) []TaskDataClass_ {
  391. o := orm.NewOrm()
  392. var maps []TaskDataClass_
  393. //fmt.Println("maps_z;",maps_z[0][0])
  394. //sql := "SELECT DISTINCT t_sn,t_id FROM z_task_data_" + T_task_id + " ORDER BY t_id "
  395. //sql := "SELECT DISTINCT t_sn FROM z_task_data_" + T_task_id + " ORDER BY t_id "
  396. sql := "SELECT t_sn,t_id FROM z_task_data_" + T_task_id + " GROUP BY t_sn,t_id ORDER BY t_id+0 "
  397. fmt.Println(sql)
  398. _, err := o.Raw(sql).QueryRows(&maps)
  399. if err != nil {
  400. // 数据库表不存在,则创建数据库
  401. if err.(*mysql.MySQLError).Number == 1146 {
  402. CREATE_TaskData(conf.Local_AliasName, T_task_id)
  403. }
  404. logs.Error(lib.FuncName(), err)
  405. return maps
  406. }
  407. return maps
  408. }
  409. func Read_TaskData_sn(T_task_id string) []TaskDataClass_ {
  410. o := orm.NewOrm()
  411. var maps []TaskDataClass_
  412. sql := "SELECT DISTINCT t_sn FROM z_task_data_" + T_task_id + " ORDER BY t_sn "
  413. fmt.Println(sql)
  414. _, err := o.Raw(sql).QueryRows(&maps)
  415. if err != nil {
  416. logs.Error(lib.FuncName(), err)
  417. return maps
  418. }
  419. return maps
  420. }
  421. func Update_TaskData_ByT_sn(T_task_id, T_sn, T_id string) error {
  422. o := orm.NewOrm()
  423. // 修改id
  424. sql := "UPDATE z_task_data_" + T_task_id + " SET `t_id` = '" + T_id + "' WHERE `t_sn` = '" + T_sn + "'"
  425. logs.Println(sql)
  426. _, err := o.Raw(sql).Exec()
  427. if err != nil {
  428. logs.Error(lib.FuncName(), err)
  429. return err
  430. }
  431. return nil
  432. }
  433. func Delete_TaskData_ByT_sn(T_task_id, T_sn string) error {
  434. o := orm.NewOrm()
  435. // 修改id
  436. sql := "DELETE FROM z_task_data_" + T_task_id + " WHERE `t_sn` = '" + T_sn + "'"
  437. logs.Println(sql)
  438. _, err := o.Raw(sql).Exec()
  439. if err != nil {
  440. logs.Error(lib.FuncName(), err)
  441. return err
  442. }
  443. return nil
  444. }
  445. // 添加
  446. func Add_TaskData(T_task_id string, T_sn string, T_id string, T_t string, T_rh string, T_time string) bool {
  447. o := orm2.NewOrm()
  448. tx, err := o.Begin()
  449. if err != nil {
  450. logs.Error("start the transaction failed")
  451. return false
  452. }
  453. // 开始插入数据
  454. //
  455. //sql := "INSERT INTO z_task_data_" + T_task_id + " (`t_sn`, `t_id`, `t_time`) " +
  456. // "VALUES ('" + T_sn + "', " + T_id + ", '" + T_time + "') " +
  457. // "ON DUPLICATE KEY UPDATE t_t=" + T_t + ", t_rh="+ T_rh +";"
  458. // 去重复数据
  459. sql := "DELETE FROM z_task_data_" + T_task_id + " WHERE " + " t_id = '" + T_id + "' AND " + " t_sn = '" + T_sn + "' " + "AND t_time = '" + T_time + "' "
  460. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  461. //fmt.Println(sql)
  462. res, err := tx.Raw(sql).Exec()
  463. if err != nil {
  464. tx.Rollback()
  465. logs.Error(lib.FuncName(), err)
  466. return false
  467. }
  468. res.RowsAffected()
  469. sql = "INSERT INTO z_task_data_" + T_task_id + " (`t_sn`, `t_id`, `t_t`, `t_rh`, `t_time`) " +
  470. "VALUES ('" + T_sn + "', '" + T_id + "', " + T_t + "," + T_rh + ", '" + T_time + "')"
  471. // 更新数据
  472. //sql := "INSERT INTO z_task_data_" + T_task_id + " (`t_sn`, `t_id`, `t_t`, `t_rh`, `t_time`) " +
  473. // "VALUES ('" + T_sn + "', " + T_id + ", " + T_t + "," + T_rh + ", '" + T_time + "')" +
  474. // "on duplicate key update `t_t`=" + T_t + ",`t_rh`=" + T_rh
  475. //fmt.Println(sql)
  476. res, err = tx.Raw(sql).Exec()
  477. if err != nil {
  478. tx.Rollback()
  479. logs.Error(lib.FuncName(), err)
  480. return false
  481. }
  482. tx.Commit()
  483. //fmt.Println("mysql row affected nums: ", num)
  484. return true
  485. }
  486. // 创建唯一索引
  487. func Create_Unique_Index(T_task_id string) error {
  488. // 创建索引
  489. o := orm.NewOrm()
  490. createIndexSql := "ALTER TABLE z_task_data_" + T_task_id + " ADD CONSTRAINT unique_index_t_sn_t_id_t_time UNIQUE (t_sn, t_id, t_time);"
  491. //fmt.Println(sql)
  492. _, err := o.Raw(createIndexSql).Exec()
  493. if err != nil && !strings.Contains(err.Error(), "Duplicate key name 'unique_index_t_sn_t_id_t_time'") {
  494. logs.Error(lib.FuncName(), err)
  495. return err
  496. }
  497. return nil
  498. }
  499. // 删除唯一索引
  500. func Delete_Unique_Index(T_task_id string) error {
  501. // 创建索引
  502. o := orm.NewOrm()
  503. // 删除索引
  504. deleteIndexSql := "ALTER TABLE z_task_data_" + T_task_id + " DROP INDEX unique_index_t_sn_t_id_t_time;"
  505. //fmt.Println(sql)
  506. _, err := o.Raw(deleteIndexSql).Exec()
  507. if err != nil {
  508. logs.Error(lib.FuncName(), err)
  509. return err
  510. }
  511. return nil
  512. }
  513. func Adds_TaskData(T_task_id string, valueStrings []string) error {
  514. o := orm.NewOrm()
  515. // 插入数据
  516. sql := "INSERT INTO z_task_data_" + T_task_id + " (`t_sn`, `t_id`, `t_t`, `t_rh`, `t_time`) VALUES"
  517. sql += strings.Join(valueStrings, ",")
  518. sql += " ON DUPLICATE KEY UPDATE t_t = VALUES(t_t), t_rh = VALUES(t_rh)"
  519. //fmt.Println(sql)
  520. _, err := o.Raw(sql).Exec()
  521. if err != nil {
  522. logs.Error(lib.FuncName(), err)
  523. return err
  524. }
  525. //fmt.Println("mysql row affected nums: ", num)
  526. return nil
  527. }
  528. // 修改
  529. func Up_TaskData(T_task_id string, Id string, T_t string, T_rh string, T_time string) bool {
  530. o := orm.NewOrm()
  531. SET_str := " SET "
  532. if len(T_t) > 0 {
  533. SET_str = SET_str + " t_t = " + T_t + " ,"
  534. }
  535. if len(T_rh) > 0 {
  536. SET_str = SET_str + " t_rh = " + T_rh + " ,"
  537. }
  538. if len(T_time) > 0 {
  539. SET_str = SET_str + " t_time = '" + T_time + "' ,"
  540. }
  541. if len(SET_str) > 7 {
  542. SET_str = SET_str[:(len(SET_str) - 1)]
  543. }
  544. // 开始插入数据 UPDATE `cold_verify`.`Z_TaskData_d8qMyeXLzIxn` SET `t_t` = 20.2 WHERE `ID` = 69
  545. sql := "UPDATE z_task_data_" + T_task_id + " " + SET_str + " WHERE ID = " + Id
  546. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  547. //fmt.Println(sql)
  548. res, err := o.Raw(sql).Exec()
  549. if err != nil {
  550. logs.Error(lib.FuncName(), err)
  551. return false
  552. }
  553. res.RowsAffected()
  554. //fmt.Println("mysql row affected nums: ", num)
  555. return true
  556. }
  557. // 删除
  558. func Del_TaskData(T_task_id string, Id string) bool {
  559. o := orm.NewOrm()
  560. // 开始插入数据 UPDATE `cold_verify`.`Z_TaskData_d8qMyeXLzIxn` SET `t_t` = 20.2 WHERE `ID` = 69
  561. sql := "DELETE FROM z_task_data_" + T_task_id + " WHERE ID = " + Id
  562. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  563. logs.Println(sql)
  564. res, err := o.Raw(sql).Exec()
  565. if err != nil {
  566. logs.Error(lib.FuncName(), err)
  567. return false
  568. }
  569. res.RowsAffected()
  570. //fmt.Println("mysql row affected nums: ", num)
  571. return true
  572. }
  573. func Read_TaskData_ByT_id(T_task_id string, Id string) (t_sn string, err error) {
  574. o := orm.NewOrm()
  575. // 开始插入数据 UPDATE `cold_verify`.`Z_TaskData_d8qMyeXLzIxn` SET `t_t` = 20.2 WHERE `ID` = 69
  576. sql := "SELECT t_sn FROM z_task_data_" + T_task_id + " WHERE t_id = '" + Id + "'"
  577. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  578. logs.Println(sql)
  579. err = o.Raw(sql).QueryRow(&t_sn)
  580. if err != nil {
  581. logs.Error(lib.FuncName(), err)
  582. return t_sn, err
  583. }
  584. //fmt.Println("mysql row affected nums: ", num)
  585. return t_sn, err
  586. }
  587. func Read_TaskData_ByT_sn(T_task_id string, sn string) (t_id string, err error) {
  588. o := orm.NewOrm()
  589. // 开始插入数据 UPDATE `cold_verify`.`Z_TaskData_d8qMyeXLzIxn` SET `t_t` = 20.2 WHERE `ID` = 69
  590. sql := "SELECT t_id FROM z_task_data_" + T_task_id + " WHERE t_sn = '" + sn + "'"
  591. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  592. logs.Println(sql)
  593. err = o.Raw(sql).QueryRow(&t_id)
  594. if err != nil {
  595. logs.Error(lib.FuncName(), err)
  596. return t_id, err
  597. }
  598. //fmt.Println("mysql row affected nums: ", num)
  599. return t_id, err
  600. }
  601. // 添加
  602. func Del_TaskData_t_id(T_task_id string, Id string) bool {
  603. o := orm.NewOrm()
  604. // 开始插入数据 UPDATE `cold_verify`.`Z_TaskData_d8qMyeXLzIxn` SET `t_t` = 20.2 WHERE `ID` = 69
  605. sql := "DELETE FROM z_task_data_" + T_task_id + " WHERE t_id = '" + Id + "'"
  606. // 这里有时间优化 用于一次 prepare 多次 exec,以提高批量执行的速度
  607. logs.Println(sql)
  608. res, err := o.Raw(sql).Exec()
  609. if err != nil {
  610. logs.Error(lib.FuncName(), err)
  611. return false
  612. }
  613. res.RowsAffected()
  614. //fmt.Println("mysql row affected nums: ", num)
  615. return true
  616. }
  617. // 检查导出表总数
  618. func Check_TaskData_Num(T_task_id string) bool {
  619. o1 := orm2.NewOrmUsingDB(conf.Local_AliasName)
  620. o2 := orm2.NewOrmUsingDB(conf.Server_AliasName)
  621. var maps_z1, maps_z2 []orm2.ParamsList
  622. sql := "SELECT COUNT(ID) FROM z_task_data_" + T_task_id
  623. fmt.Println(sql)
  624. _, err := o1.Raw(sql).ValuesList(&maps_z1)
  625. if err != nil {
  626. return false
  627. }
  628. _, err = o2.Raw(sql).ValuesList(&maps_z2)
  629. if err != nil {
  630. return false
  631. }
  632. if len(maps_z1) != len(maps_z2) {
  633. return false
  634. }
  635. return true
  636. }
  637. // 检查导出表总数
  638. func Check_TaskData_Num_Verify1(T_task_id string) bool {
  639. o1 := orm2.NewOrmUsingDB(conf.Local_AliasName)
  640. o2 := orm2.NewOrmUsingDB(conf.Verify1_AliasName)
  641. var maps_z1, maps_z2 []orm2.ParamsList
  642. sql := "SELECT COUNT(ID) FROM z_task_data_" + strings.ToLower(T_task_id)
  643. fmt.Println(sql)
  644. _, err := o1.Raw(sql).ValuesList(&maps_z1)
  645. if err != nil {
  646. return false
  647. }
  648. sql2 := "SELECT COUNT(ID) FROM Z_TaskData_" + T_task_id
  649. _, err = o2.Raw(sql2).ValuesList(&maps_z2)
  650. if err != nil {
  651. return false
  652. }
  653. if len(maps_z1) != len(maps_z2) {
  654. return false
  655. }
  656. return true
  657. }
  658. func Read_TaskData_Num(T_task_id string) int {
  659. o1 := orm2.NewOrmUsingDB(conf.Local_AliasName)
  660. var maps_z []orm2.ParamsList
  661. sql := "SELECT COUNT(ID) FROM z_task_data_" + T_task_id
  662. fmt.Println(sql)
  663. _, err := o1.Raw(sql).ValuesList(&maps_z)
  664. if err != nil {
  665. logs.Error(lib.FuncName(), err)
  666. return 0
  667. }
  668. key, _ := strconv.Atoi(maps_z[0][0].(string))
  669. return key
  670. }
  671. func Dump_TaskData(T_task_id, root, password, url_port, database, sql_file string) (string, error) {
  672. // url_port 127.0.0.1:3306
  673. // mysql8.0 以上加 --column-statistics=0
  674. // mysqldump --column-statistics=0 -uroot -proot -h127.0.0.1 -P3306 cold_verify Z_TaskData_ixEfo5zk2Oeb > /Data/Z_TaskData_ixEfo5zk2Oeb.sql
  675. v := Read_Local_Mysql_Version()
  676. host_port := strings.Split(url_port, ":")
  677. table_name := "z_task_data_" + T_task_id
  678. org := "mysqldump "
  679. if v >= 8 {
  680. org += "--column-statistics=0 "
  681. }
  682. //--no-create-info 只导出数据,而不添加 CREATE TABLE 语句。
  683. //--single-transaction 在备份库的时候并不锁定数据表
  684. //--add-locks:在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项)
  685. //--compact:压缩模式,产生更少的输出;
  686. org = org + fmt.Sprintf("-u%s -p%s -h%s -P%s --no-create-info --set-gtid-purged=OFF --skip-add-locks --compact --quick %s %s > %s ",
  687. root, password, host_port[0], host_port[1], database, table_name, sql_file)
  688. logs.Println(org)
  689. _, err := lib.Command(org)
  690. if err != nil {
  691. logs.Error(lib.FuncName(), err)
  692. }
  693. return org, err
  694. }
  695. func Dump_TaskData_Verify(T_task_id, root, password, url_port, database, sql_file string) (string, error) {
  696. // url_port 127.0.0.1:3306
  697. // mysql8.0 以上加 --column-statistics=0
  698. // mysqldump --column-statistics=0 -uroot -proot -h127.0.0.1 -P3306 cold_verify Z_TaskData_ixEfo5zk2Oeb > /Data/Z_TaskData_ixEfo5zk2Oeb.sql
  699. v := Read_Local_Mysql_Version()
  700. host_port := strings.Split(url_port, ":")
  701. table_name := "Z_TaskData_" + T_task_id
  702. org := "mysqldump "
  703. if v >= 8 {
  704. org += "--column-statistics=0 "
  705. }
  706. //--no-create-info 只导出数据,而不添加 CREATE TABLE 语句。
  707. //--single-transaction 在备份库的时候并不锁定数据表
  708. //--add-locks:在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项)
  709. //--compact:压缩模式,产生更少的输出;
  710. org = org + fmt.Sprintf("-u%s -p%s -h%s -P%s --no-create-info --set-gtid-purged=OFF --skip-add-locks --compact --quick %s %s > %s ",
  711. root, password, host_port[0], host_port[1], database, table_name, sql_file)
  712. logs.Println(org)
  713. _, err := lib.Command(org)
  714. if err != nil {
  715. logs.Error(lib.FuncName(), err)
  716. }
  717. return org, err
  718. }
  719. func Insert_TaskData(root, password, url_port, database, sql_file string) (string, error) {
  720. // url_port 127.0.0.1:3306
  721. // mysql -u root -p root -h127.0.0.1 -P3306 cold_verify_local < /data/Z_TaskData_ixEfo5zk2Oeb.sql
  722. host_port := strings.Split(url_port, ":")
  723. org := fmt.Sprintf("mysql -u%s -p%s -h%s -P%s %s < %s",
  724. root, password, host_port[0], host_port[1], database, sql_file)
  725. logs.Println(org)
  726. _, err := lib.Command(org)
  727. if err != nil {
  728. logs.Error(lib.FuncName(), err)
  729. }
  730. return org, err
  731. }
  732. func Read_Local_Mysql_Version() int {
  733. o := orm2.NewOrmUsingDB(conf.Local_AliasName)
  734. var params []orm2.Params
  735. o.Raw("select version();").Values(&params)
  736. version, _ := strconv.Atoi(params[0]["version()"].(string)[0:1])
  737. return version
  738. }
  739. func Import_TaskData(T_task_id string, offset, pagez int) bool {
  740. var maps []TaskData_
  741. serverOrm := orm2.NewOrmUsingDB(conf.Server_AliasName)
  742. localOrm := orm2.NewOrmUsingDB(conf.Local_AliasName)
  743. tb_name := "z_task_data_" + T_task_id
  744. // 开从先上版查询数据
  745. sql := "select `ID`, `t_sn`, `t_id`, `t_t`, `t_rh`, DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times from " + tb_name + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
  746. logs.Println(sql)
  747. _, err := serverOrm.Raw(sql).QueryRows(&maps)
  748. if err != nil {
  749. logs.Error(lib.FuncName(), err)
  750. return false
  751. }
  752. values := []string{}
  753. for _, row := range maps {
  754. // 处理数据
  755. values = append(values, fmt.Sprintf("('%s',%d,%v,%v,'%s')", row.T_sn, row.T_id, row.T_t, row.T_rh, row.T_time))
  756. }
  757. // 向本地版插入数据
  758. sql = "INSERT IGNORE INTO " + tb_name + "( `t_sn`, `t_id`, `t_t`, `t_rh`, `t_time`) VALUES " + strings.Join(values, ",")
  759. //logs.Println(sql)
  760. _, err = localOrm.Raw(sql).Exec()
  761. if err != nil {
  762. logs.Error(lib.FuncName(), err)
  763. return false
  764. }
  765. return true
  766. }
  767. // InsertTaskData 添加设备数据
  768. func InsertTaskData(T_task_id string, d TaskData_) {
  769. o := orm.NewOrm()
  770. var maps TaskData_
  771. sql := fmt.Sprintf("SELECT * FROM z_task_data_%s where t_time='%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_time, d.T_id, d.T_sn)
  772. err := o.Raw(sql).QueryRow(&maps)
  773. if err != nil && err.Error() != orm.ErrNoRows.Error() {
  774. logs.Error(lib.FuncName(), err)
  775. }
  776. // 添加
  777. if maps.ID == 0 {
  778. sqlStatement := fmt.Sprintf("insert into z_task_data_%s(t_sn, t_id, t_t, t_rh, t_time) value('%s','%s',%f,%f,'%s')", T_task_id, d.T_sn, d.T_id, d.T_t, d.T_rh, d.T_time)
  779. _, err = o.Raw(sqlStatement).Exec()
  780. if err != nil {
  781. logs.Error(lib.FuncName(), err)
  782. }
  783. return
  784. }
  785. //已经存在该条目,更新
  786. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = %f,t_rh = %f where t_time= '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_t, d.T_rh, d.T_time, d.T_id, d.T_sn)
  787. _, err = o.Raw(sqlStatement).Exec()
  788. if err != nil {
  789. logs.Error(lib.FuncName(), err)
  790. }
  791. }
  792. // InsertTaskData 添加设备数据
  793. func InsertTaskData_TH(T_task_id string, T_switch_t, T_switch_h bool, d TaskData_) {
  794. o := orm.NewOrm()
  795. var maps TaskData_
  796. sql := fmt.Sprintf("SELECT * FROM z_task_data_%s where t_time='%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_time, d.T_id, d.T_sn)
  797. err := o.Raw(sql).QueryRow(&maps)
  798. if err != nil && err.Error() != orm.ErrNoRows.Error() {
  799. logs.Error(lib.FuncName(), err)
  800. }
  801. // 添加
  802. if maps.ID == 0 {
  803. sqlStatement := fmt.Sprintf("insert into z_task_data_%s(t_sn, t_id, t_t, t_rh, t_time) value('%s','%s',%f,%f,'%s')", T_task_id, d.T_sn, d.T_id, d.T_t, d.T_rh, d.T_time)
  804. _, err = o.Raw(sqlStatement).Exec()
  805. if err != nil {
  806. logs.Error(lib.FuncName(), err)
  807. }
  808. return
  809. }
  810. //已经存在该条目,更新
  811. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = %f,t_rh = %f where t_time= '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_t, d.T_rh, d.T_time, d.T_id, d.T_sn)
  812. if T_switch_t && !T_switch_h {
  813. sqlStatement = fmt.Sprintf("update z_task_data_%s set t_t = %f where t_time= '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_t, d.T_time, d.T_id, d.T_sn)
  814. }
  815. if !T_switch_t && T_switch_h {
  816. sqlStatement = fmt.Sprintf("update z_task_data_%s set t_rh = %f where t_time= '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, d.T_rh, d.T_time, d.T_id, d.T_sn)
  817. }
  818. _, err = o.Raw(sqlStatement).Exec()
  819. if err != nil {
  820. logs.Error(lib.FuncName(), err)
  821. }
  822. }
  823. // DeleteTaskDataByTimeRange 删除时间范围内的数据
  824. func DeleteTaskDataByTimeRange(T_task_id, sn string, id string, start, end string) {
  825. sqlStatement := fmt.Sprintf("delete from z_task_data_%s where t_time between '%s' and '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, start, end, id, sn)
  826. o := orm.NewOrm()
  827. exec, err := o.Raw(sqlStatement).Exec()
  828. if err != nil {
  829. fmt.Println(err.Error())
  830. }
  831. affected, _ := exec.RowsAffected()
  832. fmt.Printf("从%s~%s时间段删除了%d条数据", start, end, affected)
  833. }
  834. // UpdateTaskDataTemperatureAndHumidityRandom 随机更新
  835. func UpdateTaskDataTemperatureAndHumidityRandom(T_task_id, sn, id, startTime, endTime string, ttMax, ttMin, trhMax, trhMin int) {
  836. //sql语句
  837. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = t_t + FLOOR(%d + RAND() * (%d - %d + 1)) /100.0, t_rh = t_rh + FLOOR(%d + RAND() * (%d - %d + 1)) /100.0 where t_time BETWEEN '%s' AND '%s' and t_id = '%s' and t_sn = '%s'", T_task_id, ttMin, ttMax, ttMin, trhMin, trhMax, trhMin, startTime, endTime, id, sn)
  838. o := orm.NewOrm()
  839. exec, err := o.Raw(sqlStatement).Exec()
  840. if err != nil {
  841. fmt.Println("执行错误:", sqlStatement)
  842. }
  843. affected, _ := exec.RowsAffected()
  844. fmt.Println("执行更新行数:", affected)
  845. }
  846. // UpdateTaskDataTemperatureAndHumidity 更新设备探头数据温湿度
  847. func UpdateTaskDataTemperatureAndHumidity(T_task_id, sn, id, startTime, endTime string, temperature, humidity float64) {
  848. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = t_t + %f , t_rh = t_rh + %f where t_id = '%s' and t_sn = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, temperature, humidity, id, sn, startTime, endTime)
  849. o := orm.NewOrm()
  850. exec, err := o.Raw(sqlStatement).Exec()
  851. if err != nil {
  852. fmt.Println(err.Error())
  853. }
  854. affected, err := exec.RowsAffected()
  855. if err != nil {
  856. fmt.Println(err.Error())
  857. }
  858. fmt.Printf("影响了%d行\n", affected)
  859. }
  860. // UpdateTaskData 更新设备探头数据
  861. func UpdateTaskData(T_task_id, sn, id string, old, newO TaskData_) {
  862. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = %f , t_rh = %f where t_sn = '%s' and t_id = '%s' and t_t = %f and t_rh = %f and t_time= '%s'", T_task_id, newO.T_t, newO.T_rh, sn, id, old.T_t, old.T_rh, old.T_time)
  863. fmt.Println("执行SQL:", sqlStatement)
  864. o := orm.NewOrm()
  865. exec, err := o.Raw(sqlStatement).Exec()
  866. if err != nil {
  867. fmt.Println("执行错误:", sqlStatement, err.Error())
  868. }
  869. affected, _ := exec.RowsAffected()
  870. fmt.Println("执行更新行数:", affected)
  871. }
  872. // 查询温度最小值 最大值
  873. func Read_TaskData_T_Min_Max(T_task_id string, SN string, T_id string, Time_start_ string, Time_end_ string) (minT, maxT float64) {
  874. o := orm.NewOrm()
  875. sql_condition := ""
  876. if len(Time_start_) > 1 {
  877. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  878. }
  879. if len(Time_end_) > 1 {
  880. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  881. }
  882. if len(T_id) > 0 {
  883. sql_condition += " AND t_id = '" + T_id + "'"
  884. }
  885. if len(SN) > 0 {
  886. sql_condition += " AND t_sn = '" + SN + "'"
  887. }
  888. if len(sql_condition) > 0 {
  889. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  890. }
  891. //fmt.Println("maps_z;",maps_z[0][0])
  892. sql := "SELECT MIN(t_t) AS min_t, MAX(t_t) AS max_t FROM z_task_data_" + T_task_id + sql_condition
  893. fmt.Println(sql)
  894. err := o.Raw(sql).QueryRow(&minT, &maxT)
  895. if err != nil {
  896. logs.Error(lib.FuncName(), err)
  897. }
  898. return
  899. }
  900. // UpdateTaskDataTemperatureAndHumidityByGeometric 更新设备探头数据温湿度 等比缩放
  901. func UpdateTaskDataTemperatureAndHumidityByGeometric(T_task_id, sn, id, startTime, endTime string, temperature, humidity float64) {
  902. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = t_t * %f , t_rh = t_rh * %f where t_id = '%s' and t_sn = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, temperature, humidity, id, sn, startTime, endTime)
  903. o := orm.NewOrm()
  904. exec, err := o.Raw(sqlStatement).Exec()
  905. if err != nil {
  906. fmt.Println(err.Error())
  907. }
  908. affected, err := exec.RowsAffected()
  909. if err != nil {
  910. fmt.Println(err.Error())
  911. }
  912. fmt.Printf("影响了%d行\n", affected)
  913. }
  914. func UpdateTaskDataTemperatureAndHumidityByGeometric_id(T_task_id, id, startTime, endTime string, temperature float64) {
  915. sqlStatement := fmt.Sprintf("update z_task_data_%s set t_t = t_t * %f where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, temperature, id, startTime, endTime)
  916. o := orm.NewOrm()
  917. exec, err := o.Raw(sqlStatement).Exec()
  918. if err != nil {
  919. fmt.Println(err.Error())
  920. }
  921. affected, err := exec.RowsAffected()
  922. if err != nil {
  923. fmt.Println(err.Error())
  924. }
  925. fmt.Printf("影响了%d行\n", affected)
  926. }
  927. // UpdateTaskDataTemperatureAndHumidityByGeometric 更新设备探头数据温湿度 等比缩放
  928. func UpdateTaskDataTemperatureAndHumidityByGeometricAVG(T_task_id, id, startTime, endTime string, temperature float64) {
  929. sqlStatement := ""
  930. if temperature > 0 {
  931. sqlStatement = fmt.Sprintf("update z_task_data_%s set t_t = t_t + %f where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, temperature, id, startTime, endTime)
  932. } else {
  933. sqlStatement = fmt.Sprintf("update z_task_data_%s set t_t = t_t %f where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, temperature, id, startTime, endTime)
  934. }
  935. o := orm.NewOrm()
  936. exec, err := o.Raw(sqlStatement).Exec()
  937. if err != nil {
  938. fmt.Println(err.Error())
  939. }
  940. affected, err := exec.RowsAffected()
  941. if err != nil {
  942. fmt.Println(err.Error())
  943. }
  944. fmt.Printf("影响了%d行\n", affected)
  945. }
  946. func Read_TaskData_T_Min_Max_Time_Min_Max(T_task_id string, SN []string, T_id []string, Time_start_ string, Time_end_ string) (minT, maxT float64, minTime, maxTime time.Time) {
  947. o := orm.NewOrm()
  948. sql_condition := ""
  949. if len(Time_start_) > 1 {
  950. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  951. }
  952. if len(Time_end_) > 1 {
  953. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  954. }
  955. if len(T_id) > 0 || len(SN) > 0 {
  956. sql_condition += " AND t_id in (" + strings.Join(T_id, ",") + ") OR t_sn in (" + strings.Join(SN, ",") + ")"
  957. }
  958. if len(sql_condition) > 0 {
  959. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  960. }
  961. //fmt.Println("maps_z;",maps_z[0][0])
  962. sql := "SELECT MIN(t_t) AS min_t, MAX(t_t) AS max_t,MIN(t_time) AS min_time, MAX(t_time) AS max_time FROM z_task_data_" + T_task_id + sql_condition
  963. fmt.Println(sql)
  964. err := o.Raw(sql).QueryRow(&minT, &maxT, &minTime, &maxTime)
  965. if err != nil {
  966. logs.Error(lib.FuncName(), err)
  967. }
  968. return
  969. }
  970. // 获取线上设备数据
  971. func Read_DeviceData_T_Min_Max_Time_Min_Max(T_sn, T_id string, Time_start_ string, Time_end_ string) (minT, maxT float64, minTime, maxTime time.Time) {
  972. o := orm2.NewOrmUsingDB(conf.Server_AliasName)
  973. sql_condition := ""
  974. if len(Time_start_) > 1 {
  975. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  976. }
  977. if len(Time_end_) > 1 {
  978. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  979. }
  980. sql_condition += " AND t_id = '" + T_id + "'"
  981. if len(sql_condition) > 0 {
  982. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  983. }
  984. //fmt.Println("maps_z;",maps_z[0][0])
  985. sql := "SELECT MIN(t_t) AS min_t, MAX(t_t) AS max_t,MIN(t_time) AS min_time, MAX(t_time) AS max_time FROM z_devicedata_" + T_sn + sql_condition
  986. fmt.Println(sql)
  987. err := o.Raw(sql).QueryRow(&minT, &maxT, &minTime, &maxTime)
  988. if err != nil {
  989. logs.Error(lib.FuncName(), err)
  990. }
  991. return
  992. }
  993. func Read_TaskData_ByIds_List(T_task_id string, SN []string, T_id []string, Time_start_ string, Time_end_ string) []TaskData {
  994. o := orm.NewOrm()
  995. var maps []TaskData
  996. sql_condition := ""
  997. if len(Time_start_) > 1 {
  998. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  999. }
  1000. if len(Time_end_) > 1 {
  1001. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  1002. }
  1003. if len(T_id) > 0 || len(SN) > 0 {
  1004. sql_condition += " AND (t_id in (" + strings.Join(T_id, ",") + ") OR t_sn in (" + strings.Join(SN, ",") + "))"
  1005. }
  1006. if len(sql_condition) > 0 {
  1007. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  1008. }
  1009. //fmt.Println("maps_z;",maps_z[0][0])
  1010. sql := "SELECT ID,t_sn,t_id,t_t,t_rh,t_time FROM z_task_data_" + T_task_id + sql_condition + " ORDER BY t_time"
  1011. fmt.Println(sql)
  1012. _, err := o.Raw(sql).QueryRows(&maps)
  1013. if err != nil {
  1014. logs.Error(lib.FuncName(), err)
  1015. }
  1016. return maps
  1017. }
  1018. func Read_TaskData_T_Min_Max_Time_Min_Max_ListGROUP(T_task_id string, SN string, Time_start_ string, Time_end_ string) []TaskData_Time_Min_Max_GROUP {
  1019. o := orm.NewOrm()
  1020. var maps_Time_Min_Max_GROUP []TaskData_Time_Min_Max_GROUP
  1021. 时间_a, _ := time.Parse("2006-01-02 15:04:05", Time_start_)
  1022. 时间_b, _ := time.Parse("2006-01-02 15:04:05", Time_end_)
  1023. for 时间_a.Unix() <= 时间_b.Unix() {
  1024. var maps []TaskData_
  1025. maps_Time_Min_Max_GROUP_ := TaskData_Time_Min_Max_GROUP{
  1026. T_max: -100,
  1027. T_min: 100,
  1028. T_times: 时间_a.Format("2006-01-02 15:04:05"),
  1029. }
  1030. sql_condition := ""
  1031. Time_start_ = 时间_a.Format("2006-01-02 15:04:05")
  1032. Time_end_ = 时间_b.Format("2006-01-02 15:04:05")
  1033. if len(Time_start_) > 1 {
  1034. sql_condition += " AND t_time >= '" + Time_start_ + "'"
  1035. }
  1036. if len(Time_end_) > 1 {
  1037. sql_condition += " AND t_time <= '" + Time_end_ + "'"
  1038. }
  1039. if len(SN) > 0 {
  1040. if strings.Contains(SN, "|") {
  1041. // 将字符串按 | 分割
  1042. ids := strings.Split(strings.TrimSuffix(SN, "|"), "|")
  1043. // 构建 SQL 查询
  1044. query := " AND t_sn IN ("
  1045. // 动态添加每个 id
  1046. for i, id := range ids {
  1047. query += "'" + id + "'"
  1048. if i < len(ids)-1 {
  1049. query += ", " // 添加逗号分隔
  1050. }
  1051. }
  1052. query += ") " // 结束 SQL 查询
  1053. } else {
  1054. sql_condition += " AND t_sn = '" + SN + "'"
  1055. }
  1056. }
  1057. if len(sql_condition) > 0 {
  1058. sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
  1059. }
  1060. //fmt.Println("maps_z;",maps_z[0][0])
  1061. sql := "SELECT ID,t_sn,t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_times,t_time FROM z_task_data_" + T_task_id + sql_condition + " ORDER BY t_time DESC"
  1062. fmt.Println(sql)
  1063. _, err := o.Raw(sql).QueryRows(&maps)
  1064. if err != nil {
  1065. logs.Error(lib.FuncName(), err)
  1066. }
  1067. for _, data := range maps {
  1068. if data.T_t > maps_Time_Min_Max_GROUP_.T_max {
  1069. maps_Time_Min_Max_GROUP_.T_max = data.T_t
  1070. maps_Time_Min_Max_GROUP_.T_max_id = data.T_id
  1071. }
  1072. if data.T_t < maps_Time_Min_Max_GROUP_.T_max {
  1073. maps_Time_Min_Max_GROUP_.T_min = data.T_t
  1074. maps_Time_Min_Max_GROUP_.T_min_id = data.T_id
  1075. }
  1076. }
  1077. maps_Time_Min_Max_GROUP = append(maps_Time_Min_Max_GROUP, maps_Time_Min_Max_GROUP_)
  1078. 时间_a = 时间_a.Add(time.Minute * 10)
  1079. }
  1080. return maps_Time_Min_Max_GROUP
  1081. }
  1082. // 按照 t_id 分组数据
  1083. func GroupDataByTID(data []TaskData) map[string][]TaskData {
  1084. groupedData := make(map[string][]TaskData)
  1085. for _, d := range data {
  1086. groupedData[d.T_id] = append(groupedData[d.T_id], d)
  1087. }
  1088. return groupedData
  1089. }
  1090. // 删除的重复数据
  1091. func DeleteDeduplicate(T_task_id string) (int64, error) {
  1092. localOrm := orm2.NewOrmUsingDB(conf.Local_AliasName)
  1093. tb_name := "z_task_data_" + T_task_id
  1094. var cnt int64
  1095. // 创建临时表
  1096. sqlCreate := "CREATE TABLE `tmp_table` AS (SELECT MIN(`ID`) AS `min_id` FROM " + tb_name + " GROUP BY `t_sn`,`t_id`,`t_time`);"
  1097. _, err := localOrm.Raw(sqlCreate).Exec()
  1098. if err != nil {
  1099. logs.Error(lib.FuncName(), err)
  1100. return cnt, err
  1101. }
  1102. sqlDelete := "DELETE FROM " + tb_name + " WHERE `ID` NOT IN (SELECT `min_id` FROM `tmp_table`);"
  1103. res, err := localOrm.Raw(sqlDelete).Exec()
  1104. if err != nil {
  1105. logs.Error(lib.FuncName(), err)
  1106. return cnt, err
  1107. }
  1108. cnt, _ = res.RowsAffected()
  1109. sqlDrop := "DROP TABLE `tmp_table`;"
  1110. _, err = localOrm.Raw(sqlDrop).Exec()
  1111. if err != nil {
  1112. logs.Error(lib.FuncName(), err)
  1113. return cnt, err
  1114. }
  1115. return cnt, nil
  1116. }
  1117. func CheckTableExist(alias_name, T_task_id string) bool {
  1118. o := orm2.NewOrmUsingDB(alias_name)
  1119. sql := "select count(ID) from z_task_data_" + T_task_id
  1120. _, err := o.Raw(sql).Exec()
  1121. if err != nil {
  1122. // 数据库表不存在,则创建数据库
  1123. logs.Error(lib.FuncName(), err)
  1124. if err.(*mysql.MySQLError).Number == 1146 {
  1125. return false
  1126. }
  1127. }
  1128. return true
  1129. }
  1130. // 修改auto_increment的初始值
  1131. func UpdateAUTO_INCREMENT(alias_name, T_task_id string) error {
  1132. o := orm2.NewOrmUsingDB(alias_name)
  1133. var max_id int
  1134. sql := "select max(ID) from z_task_data_" + T_task_id
  1135. err := o.Raw(sql).QueryRow(&max_id)
  1136. if err != nil {
  1137. logs.Error(lib.FuncName(), err)
  1138. return err
  1139. }
  1140. // ALTER TABLE z_task_data_lg9v2zpfem7b AUTO_INCREMENT = 39905;
  1141. sql = "ALTER TABLE z_task_data_" + T_task_id + " AUTO_INCREMENT = " + strconv.Itoa(max_id+1)
  1142. _, err = o.Raw(sql).Exec()
  1143. if err != nil {
  1144. logs.Error(lib.FuncName(), err)
  1145. return err
  1146. }
  1147. return nil
  1148. }
  1149. // 修改auto_increment的初始值
  1150. func Read_TaskData_AVG(T_task_id, T_sn, T_id, startTime, endTime string) float64 {
  1151. o := orm.NewOrm()
  1152. var v float64
  1153. sql := fmt.Sprintf("select AVG(t_t) FROM z_task_data_%s where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, T_id, startTime, endTime)
  1154. err := o.Raw(sql).QueryRow(&v)
  1155. if err != nil {
  1156. logs.Error(lib.FuncName(), err)
  1157. return 0
  1158. }
  1159. return v
  1160. }
  1161. // 修改auto_increment的初始值
  1162. func Read_TaskData_max(T_task_id, T_sn, T_id, startTime, endTime string) float64 {
  1163. o := orm.NewOrm()
  1164. var v float64
  1165. sql := fmt.Sprintf("select MAX(t_t) FROM z_task_data_%s where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, T_id, startTime, endTime)
  1166. err := o.Raw(sql).QueryRow(&v)
  1167. if err != nil {
  1168. logs.Error(lib.FuncName(), err)
  1169. return 0
  1170. }
  1171. return v
  1172. }
  1173. // 修改auto_increment的初始值
  1174. func Read_TaskData_min(T_task_id, T_sn, T_id, startTime, endTime string) float64 {
  1175. o := orm.NewOrm()
  1176. var v float64
  1177. sql := fmt.Sprintf("select MIN(t_t) FROM z_task_data_%s where t_id = '%s' and t_time BETWEEN '%s' and '%s'", T_task_id, T_id, startTime, endTime)
  1178. err := o.Raw(sql).QueryRow(&v)
  1179. if err != nil {
  1180. logs.Error(lib.FuncName(), err)
  1181. return 0
  1182. }
  1183. return v
  1184. }
  1185. // 重置 SN数据
  1186. func Import_TaskData_Back(Sn string, T_id string, T_task_id string, Time_start string, Time_end string) error {
  1187. o := orm.NewOrm()
  1188. // 开始插入数据
  1189. sql := "insert into z_task_data_" + T_task_id + "(t_sn,t_id,t_t,t_rh,t_time) select '" + Sn + "','" + T_id + "',t_t,t_rh,t_time from z_devicedata_" + Sn
  1190. if len(Time_start) > 0 && len(Time_end) > 0 {
  1191. sql = sql + " WHERE t_time >= '" + Time_start + "' AND t_time <= '" + Time_end + "'"
  1192. }
  1193. logs.Println(sql)
  1194. _, err := o.Raw(sql).Exec()
  1195. if err != nil {
  1196. logs.Error(lib.FuncName(), err)
  1197. return err
  1198. }
  1199. return nil
  1200. }
  1201. func Import_Task_Back(Sn string, T_id string, T_task_id, To_T_task_id string, Time_start string, Time_end string) error {
  1202. o := orm.NewOrm()
  1203. // 开始插入数据
  1204. sql := "insert into z_task_data_" + To_T_task_id + "(t_sn,t_id,t_t,t_rh,t_time) select t_sn,t_id,t_t,t_rh,t_time from z_task_data_" + T_task_id
  1205. if len(Time_start) > 0 && len(Time_end) > 0 {
  1206. sql = sql + " WHERE t_sn = '" + Sn + "' AND t_id = '" + T_id + "' AND t_time >= '" + Time_start + "' AND t_time <= '" + Time_end + "'"
  1207. }
  1208. logs.Println(sql)
  1209. _, err := o.Raw(sql).Exec()
  1210. if err != nil {
  1211. logs.Error(lib.FuncName(), err)
  1212. return err
  1213. }
  1214. return nil
  1215. }
  1216. /* ==============================================================================================================
  1217. V型数据,下降持续5分钟,持续上升 5 分钟,取上升点的第一条数据
  1218. ==============================================================================================================
  1219. */
  1220. // isDescending checks if the given data points are strictly descending.
  1221. func isDescending(data []TaskData_) bool {
  1222. if len(data) < 2 {
  1223. return false // Not enough data to determine a trend.
  1224. }
  1225. for i := 1; i < len(data); i++ {
  1226. if data[i].T_t >= data[i-1].T_t {
  1227. return false // Not strictly descending.
  1228. }
  1229. }
  1230. return true
  1231. }
  1232. // isAscending checks if the given data points are strictly ascending.
  1233. func isAscending(data []TaskData_) bool {
  1234. if len(data) < 2 {
  1235. return false // Not enough data to determine a trend.
  1236. }
  1237. for i := 1; i < len(data); i++ {
  1238. if data[i].T_t <= data[i-1].T_t {
  1239. return false // Not strictly ascending.
  1240. }
  1241. }
  1242. return true
  1243. }
  1244. // findVTrend identifies a V-shaped trend in the data and returns the time of the lowest point.
  1245. // It looks for a sequence of at least 5 descending points followed by at least 5 ascending points.
  1246. func FindVTrend(data []TaskData_) (int, bool) {
  1247. if len(data) < 10 {
  1248. return -1, false // Not enough data to find a V-trend.
  1249. }
  1250. for i := 5; i < len(data)-5; i++ { // Adjust loop for index out of bounds error
  1251. descendingData := data[i-5 : i] // Get 5 descending point
  1252. ascendingData := data[i : i+5] // get 5 ascending point
  1253. if isDescending(descendingData) && isAscending(ascendingData) {
  1254. // Found a V-trend. Return the time of the lowest point (data[i]).
  1255. return i, true
  1256. }
  1257. }
  1258. return -1, false // No V-trend found.
  1259. }
  1260. type CalculateHumps_R struct {
  1261. Start TaskData_AVG
  1262. Peak TaskData_AVG
  1263. End TaskData_AVG
  1264. }
  1265. // 计算驼峰趋势
  1266. func CalculateHumps(data []TaskData_AVG) []CalculateHumps_R {
  1267. var humps []CalculateHumps_R
  1268. n := len(data)
  1269. if n < 3 {
  1270. return humps
  1271. }
  1272. i := 0
  1273. for i < n-5 {
  1274. // 寻找开始的最低点
  1275. for !(data[i].T_t <= data[i+1].T_t && data[i+1].T_t <= data[i+2].T_t) {
  1276. i++
  1277. if i > n-5 {
  1278. break
  1279. }
  1280. }
  1281. start := i
  1282. // 寻找最高点
  1283. for !(data[i].T_t >= data[i+1].T_t && data[i+1].T_t > data[i+2].T_t && data[i+2].T_t > data[i+3].T_t) {
  1284. i++
  1285. if i > n-5 {
  1286. break
  1287. }
  1288. }
  1289. peak := i
  1290. Abs_peak := math.Abs(float64(data[start].T_t - data[peak].T_t))
  1291. if Abs_peak < 0.2 {
  1292. continue
  1293. }
  1294. // 寻找结束的最低点
  1295. for !(data[i].T_t <= data[i+1].T_t && data[i+1].T_t < data[i+2].T_t && data[i+2].T_t < data[i+3].T_t) {
  1296. i++
  1297. if i > n-5 {
  1298. break
  1299. }
  1300. }
  1301. end := i
  1302. Abs_end := math.Abs(float64(data[end].T_t - data[peak].T_t))
  1303. if Abs_end < 0.3 {
  1304. continue
  1305. }
  1306. // 如果找到了一个完整的驼峰
  1307. if start < peak && peak < end {
  1308. CalculateHumps_ := CalculateHumps_R{
  1309. Start: data[start],
  1310. Peak: data[peak],
  1311. End: data[end],
  1312. }
  1313. humps = append(humps, CalculateHumps_)
  1314. }
  1315. }
  1316. return humps
  1317. }