data.py 4.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137
  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3. """
  4. demeter database
  5. name:data.py
  6. author:rabin
  7. """
  8. from __load__ import *
  9. import datetime
  10. import calendar
  11. #DROP MEASUREMENT "data_4_10021011"
  12. #DROP MEASUREMENT "data_2_10022011"
  13. class Data(Model):
  14. __table__ = 'data'
  15. def setTable(self, data):
  16. self.table = self.__table__ + '_' + str(data['farm']) + '_' + str(data['device']) + '1'
  17. def insert(self, data):
  18. self.setTable(data)
  19. json = self.insert_json(data)
  20. self.db.write_points(json)
  21. def insert_json(self, data):
  22. json = [
  23. {
  24. "measurement": self.table,
  25. "tags": {
  26. "hard": data['hard'],
  27. "type":data['type'],
  28. "gateway": data['gateway']
  29. },
  30. #"time": data['time'],
  31. "fields": {
  32. 'source' : float(data['source']),
  33. 'value' : float(data['value'])
  34. }
  35. }]
  36. return json
  37. def get(self, farm, device, col='value', search='', exp=''):
  38. data = {}
  39. data['farm'] = farm
  40. data['device'] = device
  41. self.setTable(data)
  42. if search:
  43. search = ' where ' + search
  44. sql = 'select ' + col + ' from ' + self.table + search
  45. data = self.db.query(sql)
  46. value = []
  47. time = []
  48. rows = []
  49. utc_format = '%Y-%m-%dT%H:%M:%S'
  50. local_format = "%Y-%m-%d %H:%M:%S"
  51. if 'series' in data.raw and data.raw['series'][0]['values']:
  52. for values in data.raw['series'][0]['values']:
  53. if values[1]:
  54. v = float(round(values[1], 2))
  55. v = Demeter.exp(exp, v)
  56. temp = values[0].split('.')
  57. uformat = utc_format
  58. if len(temp) <= 1:
  59. uformat = utc_format + 'Z'
  60. utc = Demeter.mktime(temp[0], uformat) + 3600*8
  61. date = Demeter.date(utc, local_format)
  62. value.append(v)
  63. time.append(date)
  64. rows.append([date, v])
  65. return {'time':time, 'value':value, 'rows':rows}
  66. def getData(self, method, data, config):
  67. search = ''
  68. col = 'value'
  69. utc_format = '%Y-%m-%dT%H:%M:%SZ'
  70. if ('start' in data and data['start']) or ('end' in data and data['end']):
  71. if 'start' in data and data['start']:
  72. start = Demeter.mktime(data['start'])
  73. startDate = Demeter.date(start - 3600*8, utc_format)
  74. search = 'time > \'' + str(startDate) + '\''
  75. if 'end' in data and data['end']:
  76. end = Demeter.mktime(data['end'])
  77. endDate = Demeter.date(end - 3600*8, utc_format)
  78. search = search + ' and time < \'' + str(endDate) + '\''
  79. search = search + self.group(self.day(start, end))
  80. else:
  81. now = Demeter.time()
  82. search = search + self.group(self.day(start, now))
  83. elif 'group' in data and data['group']:
  84. value = data['group']
  85. time = 'time > now()'
  86. search = time + ' - ' + value + 'd' + self.group(int(value))
  87. result = []
  88. if method == 'avg':
  89. col = 'mean(value) as value'
  90. result.append(self.load(config, search, col, '平均'))
  91. elif method == 'maxmin':
  92. col = 'max(value) as value'
  93. result.append(self.load(config, search, col, '最高'))
  94. col = 'min(value) as value'
  95. result.append(self.load(config, search, col, '最低'))
  96. else:
  97. col = 'value'
  98. if 'group' in search:
  99. temp = search.split('group')
  100. search = temp[0]
  101. result.append(self.load(config, search, col, '明细'))
  102. return result
  103. def load(self, config, search, col, name):
  104. result = {}
  105. result['data'] = self.get(config['farm_id'], config['hardware_id'], search=search, col=col)
  106. result['name'] = name
  107. if result['data']:
  108. result['data']['time'] = json.dumps(result['data']['time'])
  109. result['data']['value'] = json.dumps(result['data']['value'])
  110. return result
  111. def group(self, day):
  112. if day >= 180:
  113. return ' group by time(10d)'
  114. elif day >= 30:
  115. return ' group by time(1d)'
  116. elif day >= 7:
  117. return ' group by time(60m)'
  118. elif day >= 1:
  119. return ' group by time(10m)'
  120. else:
  121. return ' group by time(1m)'
  122. def day(self, start, end):
  123. return math.ceil(round(float((end - start) / (3600*24))))
  124. def dateConfig(self):
  125. return [{'id':'1', 'name':'一天'},{'id':'7', 'name':'一周'},{'id':'30', 'name':'一个月'},{'id':'180', 'name':'半年'}]