def post(self):
args = self.request.arguments
if 'givemejson' in args.keys():
self._go_back_format = True
pb2 = self.get_argument('pb2')
rqmsg = msgws.rqUserAdd()
msg = msgws.UserAdd()
msg.head.ver = 160328
msg.head.if_dt = int(time.time())
try:
rqmsg.ParseFromString(base64.b64decode(pb2))
msg.head.idx = rqmsg.head.idx
msg.head.if_st = 1
except:
msg.head.if_st = 46
try:
# 检查用户名密码是否存在
strsql = 'insert into {0}.user_info (user_name,user_password,user_real_name,date_create,date_update,date_access,user_remark) \
values ("{1}","{2}","{3}","{4}","{5}","{6}","{7}")'.format(
self._db_name, rqmsg.user, rqmsg.pwd, rqmsg.fullname,
mx.switchStamp(time.time()), mx.switchStamp(time.time()),
mx.switchStamp(time.time()),
'add user from {0}'.format(self.request.remote_ip))
cur = yield self.mydata_collector(strsql, need_fetch=0)
affected_rows = cur[0][0]
msg.user_id = cur[0][1]
contents = ''
if affected_rows > 0:
contents = 'add user {0} from {1} success'.format(
rqmsg.user, self.request.remote_ip)
yield self.write_event(
154, 'add user {0} from {1} success'.format(
rqmsg.user,
self.request.remote_ip,
app_unique=rqmsg.head.unique))
else:
contents = 'add user {0} from {1} failed'.format(
rqmsg.user, self.request.remote_ip)
msg.head.if_st = 0
msg.head.if_msg = 'User already exists'
except Exception as ex:
msg.head.if_st = 0
msg.head.if_msg = str(ex.message)
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
yield self.write_event(154,
contents,
2,
user_name=rqmsg.user,
app_unique=rqmsg.head.unique)
del msg, rqmsg
def post(self):
legal, rqmsg, msg = yield self.check_arguments(msgws.UpdateSmsAlarm(),
msgws.CommAns(),
use_scode=1)
if legal:
# 删除原始记录
# for a in rqmsg.record_id:
# strsql = '''delete from {0}_data.record_msg_new where record_id={1}'''.format(
# self._db_name, a)
strsql = '''delete from {0}.record_msg_new where record_id in ({1})'''.format(
self._db_name_data,
','.join([str(a) for a in rqmsg.record_id]))
cur = yield self.mydata_collector(strsql, need_fetch=0)
if cur is None:
msg.head.if_st = 45
# 写入发送记录
strsql = '''INSERT INTO {0}.record_msg_log(`send_date`, `send_number`,`send_msg`) VALUES ({1},{2},"{3}")'''.format(
self._db_name_data, mx.switchStamp(time.time()),
rqmsg.user_tel, rqmsg.fault_msg)
cur = yield self.mydata_collector(strsql, need_fetch=0)
if cur is None:
msg.head.if_st = 45
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqUserAdd(), None)
env = False
contents = ''
if user_uuid in libiisi.cache_buildin_users:
msg.head.if_st = 0
msg.head.if_msg = 'build-in user are not allowed to add new user.'
else:
if user_data is not None:
if user_data['user_auth'] < 15:
msg.head.if_st = 11
else:
# 判断用户是否存在
strsql = 'select * from {0}.user_list where user_name="{1}" and user_password="******"'.format(
self._db_name, rqmsg.user.replace('"', ''),
rqmsg.pwd.replace('"', ''))
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql, need_fetch=1)
if record_total > 0:
msg.head.if_st = 45
msg.head.if_msg = 'User already exists'
else:
strsql = 'insert into {0}.user_list (user_name, user_real_name, user_password, user_phonenumber, user_operator_code, date_create, date_update, date_access) \
values ("{1}","{2}","{3}","{4}","{5}",{6},{7},{8})'.format(
self._db_name, rqmsg.user, rqmsg.fullname,
rqmsg.pwd, rqmsg.mobile, rqmsg.code,
mx.switchStamp(int(time.time())),
mx.switchStamp(int(time.time())),
mx.switchStamp(int(time.time())))
yield self.mydata_collector(strsql, need_fetch=0)
env = True
contents = 'add user {0}'.format(rqmsg.user)
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
if env:
self.write_event(154,
contents,
2,
user_name=user_data['user_name'],
app_unique=rqmsg.head.unique)
del msg, rqmsg, user_data
def process_input_date(self, dt_start, dt_end, to_chsarp=1):
'''处理输入的时间格式'''
if dt_start == 0 and dt_end == 0:
return 0, 0
if dt_end < dt_start:
dt_end = dt_start
if len(self._fetch_limited) == 0:
if dt_end - dt_start > 864000:
dt_end = dt_start + 864000
if to_chsarp:
sdt = mx.switchStamp(dt_start)
edt = mx.switchStamp(dt_end)
else:
sdt = dt_start
edt = dt_end
return sdt, edt
def post(self):
legal, rqmsg, msg = yield self.check_arguments(msgws.rqQuerySmsAlarm(),
msgws.QuerySmsAlarm(),
use_scode=1)
if legal:
if rqmsg.data_mark == 2: # 市政短信
strsql = '''select is_alarm,record_id,rtu_name,user_phone_number
from {0}.record_msg_new where is_alarm=2
order by user_phone_number limit 100'''.format(
self._db_name_data)
else:
strsql = '''select is_alarm,record_id,rtu_name,user_phone_number,rtu_id,loop_id,loop_name,fault_name,
date_create from {0}.record_msg_new
where is_alarm in (0,1)
order by user_phone_number,rtu_id,loop_id,is_alarm,date_create desc limit 100'''.format(
self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
need_paging=0,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
smsr = msgws.QuerySmsAlarm.SmsAlarm()
smsr.data_mark = d[0]
smsr.record_id = d[1]
smsr.tml_name = d[2]
smsr.user_tel = d[3]
if d[0] in (0, 1):
smsr.tml_id = d[4]
smsr.loop_id = d[5]
smsr.loop_name = d[6]
smsr.fault_name = d[7]
smsr.dt_create = mx.switchStamp(int(d[8]))
msg.sms_alarm.extend([smsr])
del smsr
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqMruInfoNN(), msgws.MruInfoNN())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
if msg.head.if_st == 1:
if rqmsg.company_id == -1:
str_companyid = '1=1'
else:
str_companyid = ' a.company_id = {0} '.format(
rqmsg.company_id)
strsql = ''' select a.rtu_name,a.meter_address,a.mru_baudrate,a.mru_type,a.company_id,a.date_create,a.init_value,
a.company_name,a.meter_scale ,a.sim_number from {0}.para_meter as a
where {1} {2}'''.format(
self._db_name_data, str_companyid, self._fetch_limited)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
mm = msgws.MruInfoNN.MruInfoNN()
mm.mru_name = d[0]
mm.mru_id = int(d[1])
mm.mru_baud_rate = d[2]
mm.mru_type = d[3]
mm.company_id = d[4]
mm.setup_date = mx.switchStamp(int(d[5]))
mm.setup_value = float(
d[6]) if d[6] is not 0 else 0.0
mm.company_name = d[7]
mm.transform = d[8] if d[8] is not 0 else 0
mm.sim = d[9]
msg.mru_infonn.extend([mm])
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQuerySmsRecord(), msgws.QuerySmsRecord())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
yield self.update_cache("r", user_uuid)
if len(rqmsg.tels) > 0:
str_tels = ' and send_number in ({0})'.format(','.join(
[str(t) for t in rqmsg.tels]))
else:
str_tels = ''
strsql = 'select send_date,send_number,send_msg from {0}.record_msg_log \
where send_date>={1} and send_date<={2} and send_msg like "%{3}%" \
{4} {5}'.format(self._db_name_data, sdt, edt,
rqmsg.msg, str_tels,
self._fetch_limited)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
smsr = msgws.QuerySmsRecord.SmsRecord()
smsr.dt_send = mx.switchStamp(int(d[0]))
smsr.tel = int(d[1])
smsr.msg = d[2]
msg.sms_record.extend([smsr])
del smsr
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
legal, rqmsg, msg = yield self.check_arguments(msgws.rqSubmitSms(),
msgws.CommAns(),
use_scode=1)
if legal:
strsql = ''
t = mx.switchStamp(int(time.time()))
for tel in rqmsg.tels:
if isinstance(tel, types.LongType):
strsql += 'insert into {0}.record_msg_new (date_create,rtu_name,user_phone_number,is_alarm) values ({1},"{2}",{3},2);'.format(
self._db_name_data, t,
u'{0}'.format(str(rqmsg.msg).strip()), tel)
yield self.mydata_collector(strsql, need_fetch=0)
# else:
# msg.head.if_st = 0
# msg.head.if_msg = 'Security code error'
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg
def post(self):
legal, rqmsg, msg = yield self.check_arguments(msgws.CommAns(),
msgws.CommAns(),
use_scode=1)
if legal:
strsql = '''select count(1) from {0}.record_msg_log'''.format(
self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql, need_fetch=1)
if cur is not None:
if cur[0][0] > 1000:
strsql = '''delete from {0}.record_msg_log where send_date<{1}'''.format(
self._db_name_data,
mx.switchStamp(int(time.time() - 31622400)))
cur = yield self.mydata_collector(strsql, need_fetch=0)
if cur is None:
msg.head.if_st = 45
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg
def write_event(self, event_id, contents, is_client_snd, **kwords):
'''写事件记录'''
user_name = kwords['user_name'] if 'user_name' in kwords.keys() else ''
device_ids = kwords['device_ids'] if 'device_ids' in kwords.keys(
) else '0'
app_unique = kwords['app_unique'] if 'app_unique' in kwords.keys(
) else ''
remark = kwords['remark'] if 'remark' in kwords.keys(
) else '' + " " + app_unique
# strsql = "insert into record_operator (date_create, user_name, operator_id, is_client_snd, device_ids, contents, remark) values ({0},'{1}',{2},{3},'{4}','{5}','{6}')".format(
# int(time.time()), user_name, event_id, is_client_snd, device_ids, contents, remark)
# libiisi.SQL_DATA.execute(strsql)
strsql = ''
for rtu_id in device_ids.split(','):
strsql += 'insert into {0}.record_operator (date_create,user_name, operator_id, is_client_snd, rtu_id, contents, remark) \
values ({1},"{2}",{3},{4},{5},"{6}","{7}");'.format(
self._db_name_data, mx.switchStamp(time.time()), user_name,
event_id, is_client_snd, int(rtu_id), contents, remark)
cur = self.mydata_collector(strsql, need_fetch=0)
del cur, strsql
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataRtuElec(), msgws.QueryDataRtuElec())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid,
list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(','.join(
[str(a) for a in tml_ids]))
if rqmsg.data_mark == 0:
strsql = 'select a.date_create,a.rtu_id,a.loop_id,a.minutes_open,a.power \
from {5}.info_rtu_elec as a \
where a.date_create>={1} and a.date_create<={2} {3} \
order by a.date_create desc,a.rtu_id,a.loop_id {4}'.format(
self._db_name, sdt, edt, str_tmls,
self._fetch_limited, self._db_name_data)
else:
strsql = 'select a.rtu_id,a.rtu_id,a.loop_id,sum(a.minutes_open) as m,sum(a.power) as p \
from {5}.info_rtu_elec as a \
where a.date_create>={1} and a.date_create<={2} {3} \
group by a.rtu_id,a.loop_id \
order by a.rtu_id,a.loop_id {4}'.format(
self._db_name, sdt, edt, str_tmls,
self._fetch_limited, self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num,
multi_record=[])
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
dv = msgws.QueryDataRtuElec.DataRtuElecView()
if rqmsg.data_mark == 0:
dv.dt_count = mx.switchStamp(int(d[0]))
dv.tml_id = int(d[1])
dv.loop_id = int(d[2])
dv.data_lenght = int(d[3])
dv.estimate_value = float(d[4])
msg.data_rtu_elec_view.extend([dv])
del dv
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataRtu(), msgws.QueryDataRtu())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
msg.type = rqmsg.type
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid,
list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(','.join(
[str(a) for a in tml_ids]))
if rqmsg.type == 0: # 最新数据
strsql = 'select a.TABLE_NAME from information_schema.VIEWS as a where a.TABLE_NAME="data_rtu_view_new" and a.TABLE_SCHEMA="{0}"'.format(
self._db_name_data)
cur = libiisi.m_sql.run_fetch(strsql)
has_view = False
if cur is not None:
if len(cur) > 0:
has_view = True
del cur
# try:
# d = cur.next()
# has_view = True
# except:
# pass
if has_view:
strsql = '''select a.date_create, a.rtu_id,a.rtu_voltage_a,a.rtu_voltage_b,a.rtu_voltage_c,
a.rtu_current_sum_a,a.rtu_current_sum_b,a.rtu_current_sum_c,
a.rtu_alarm,a.switch_out_attraction,a.loop_id,a.v,a.a,a.power,
a.power_factor,a.bright_rate,a.switch_in_state,a.a_over_range,a.v_over_range,a.temperature,
c.loop_name,b.rtu_phy_id,b.rtu_name,c.switch_output_id
from {2}.data_rtu_view_new as a
right join {0}.para_rtu_loop_info as c on a.rtu_id=c.rtu_id and a.loop_id=c.loop_id
right join {0}.para_base_equipment as b on a.rtu_id=b.rtu_id
where a.temperature>-50 {1}
order by a.rtu_id,a.loop_id'''.format(
self._db_name, str_tmls, self._db_name_data)
else:
strsql = '''select x.*,a.rtu_voltage_a,a.rtu_voltage_b,a.rtu_voltage_c,
a.rtu_current_sum_a,a.rtu_current_sum_b, a.rtu_current_sum_c,a.rtu_alarm,a.switch_out_attraction,
d.loop_id,d.v,d.a,d.power,d.power_factor,d.bright_rate,d.switch_in_state,d.a_over_range,d.v_over_range,a.temperature,
c.loop_name,b.rtu_phy_id,b.rtu_name,c.switch_output_id
from
(select max(a.date_create) as date_create,a.rtu_id
from {2}.data_rtu_record as a where a.temperature>-50 {1} group by a.rtu_id) as x
left join {2}.data_rtu_record as a on x.rtu_id=a.rtu_id and x.date_create=a.date_create
left join {2}.data_rtu_loop_record as d on x.rtu_id=d.rtu_id and x.date_create=d.date_create
right join {0}.para_base_equipment as b on x.rtu_id=b.rtu_id
right join {0}.para_rtu_loop_info as c on d.rtu_id=c.rtu_id and d.loop_id=c.loop_id'''.format(
self._db_name, str_tmls, self._db_name_data)
# strsql = 'select a.date_create,a.rtu_id,a.rtu_voltage_a,a.rtu_voltage_b,a.rtu_voltage_c, \
# a.rtu_current_sum_a,a.rtu_current_sum_b, a.rtu_current_sum_c,a.rtu_alarm,a.switch_out_attraction, \
# a.loop_id,a.v,a.a,a.power,a.power_factor,a.bright_rate,a.switch_in_state,a.a_over_range,a.v_over_range,c.loop_name \
# from {0}_data.data_rtu_view as a left join {0}.para_rtu_loop_info as c on a.rtu_id=c.rtu_id and a.loop_id=c.loop_id \
# where EXISTS \
# (select rtu_id,date_create from \
# (select rtu_id,max(date_create) as date_create from {0}_data.data_rtu_view group by rtu_id) as t \
# where t.rtu_id=a.rtu_id and t.date_create=a.date_create) {1} order by a.date_create desc,a.rtu_id,a.loop_id;'.format(
# self._db_name, str_tmls)
else:
if len(tml_ids) != 1:
strsql = ""
else:
# 徐坤说把right join 改成join, 后面加where
strsql = '''select x.*,c.loop_name,b.rtu_phy_id,b.rtu_name,c.switch_output_id from
(select a.date_create, a.rtu_id,a.rtu_voltage_a,a.rtu_voltage_b,a.rtu_voltage_c,
a.rtu_current_sum_a,a.rtu_current_sum_b,a.rtu_current_sum_c,
a.rtu_alarm,a.switch_out_attraction,a.loop_id,a.v,a.a,a.power,
a.power_factor,a.bright_rate,a.switch_in_state,a.a_over_range,a.v_over_range,a.temperature
from {5}.data_rtu_view as a
where a.temperature>-50 and a.date_create>={1} and a.date_create<={2} {3}) as x
join {0}.para_base_equipment as b on x.rtu_id=b.rtu_id
join {0}.para_rtu_loop_info as c on x.rtu_id=c.rtu_id and x.loop_id=c.loop_id
where 1=1 {6}
ORDER BY x.rtu_id ,x.date_create'''.format(
self._db_name, sdt, edt, str_tmls,
self._fetch_limited, self._db_name_data,
str_tmls.replace("a.", "c."))
# strsql = '''select a.date_create, a.rtu_id,a.rtu_voltage_a,a.rtu_voltage_b,a.rtu_voltage_c,
# a.rtu_current_sum_a,a.rtu_current_sum_b,a.rtu_current_sum_c,
# a.rtu_alarm,a.switch_out_attraction,a.loop_id,a.v,a.a,a.power,
# a.power_factor,a.bright_rate,a.switch_in_state,a.a_over_range,a.v_over_range,
# c.loop_name from {0}_data.data_rtu_view as a
# left join {0}.para_rtu_loop_info as c on a.rtu_id=c.rtu_id and a.loop_id=c.loop_id
# where a.date_create>={1} and a.date_create<={2} {3}
# order by a.rtu_id,a.date_create desc,a.loop_id'''.format(self._db_name, sdt,
# edt, str_tmls)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num,
multi_record=[0, 1],
key_column=20)
if record_total is None:
msg.head.if_st = 45
else:
drv = msgws.QueryDataRtu.DataRtuView()
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
if d[0] is None:
continue
if drv.tml_id != int(
d[1]) or drv.dt_receive != mx.switchStamp(
int(d[0])):
if drv.tml_id > 0:
msg.data_rtu_view.extend([drv])
drv = msgws.QueryDataRtu.DataRtuView()
drv.tml_id = int(d[1])
drv.dt_receive = mx.switchStamp(int(d[0]))
drv.voltage_a = float(d[2])
drv.voltage_b = float(d[3])
drv.voltage_c = float(d[4])
drv.current_sum_a = float(d[5])
drv.current_sum_b = float(d[6])
drv.current_sum_c = float(d[7])
# drv.alarm_st.extend([int(a) for a in '{0:08b}'.format(int(d[
# 8]))[::-1]])
s = [0, 0, 0, 0, 0, 0, 0, 0]
for r in list(str(d[8])):
s[int(r) - 1] = 1
drv.alarm_st.extend(s)
x = d[9][:len(d[9]) - 1].split(';')
drv.switch_out_st.extend(
[1 if a == 'True' else 0 for a in x])
drv.phy_id = int(
d[21]) if d[21] is not None else -1
drv.tml_name = d[22] if d[
22] is not None else ''
drv.temperature = d[19]
# if d[19] is not None:
if d[10] is not None:
drlv = msgws.QueryDataRtu.LoopView()
drlv.loop_id = int(d[10])
drlv.voltage = float(d[11] / 100.0)
drlv.current = float(d[12] / 100.0)
drlv.power = float(d[13] / 100.0)
drlv.factor = float(d[14])
drlv.rate = float(d[15])
drlv.switch_in_st = int(d[16])
drlv.current_over_range = int(d[17])
drlv.voltage_over_range = int(d[18])
drlv.loop_name = d[20] if d[
20] is not None else ''
drlv.loop_switchout_id = d[23] if d[
23] is not None else 0
drv.loop_view.extend([drlv])
del drlv
if drv.tml_id > 0:
msg.data_rtu_view.extend([drv])
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataAls(), msgws.QueryDataAls())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid,
list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(','.join(
[str(a) for a in tml_ids]))
strsql = '''select a.rtu_id,a.date_create,a.lux_data,b.rtu_name
from {5}.data_lux_record as a
left join {0}.para_base_equipment as b on a.rtu_id=b.rtu_id
where a.date_create>={1} and a.date_create<={2} {3} {4}'''.format(
self._db_name, sdt, edt, str_tmls, self._fetch_limited,
self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num,
multi_record=[0, 1])
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
drv = msgws.QueryDataAls.DataAlsView()
drv.tml_id = int(d[0])
drv.dt_receive = mx.switchStamp(int(d[1]))
drv.lux_value = float(d[2])
drv.lux_name = d[3]
msg.data_als_view.extend([drv])
del drv
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataErr(), msgws.QueryDataErr())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(
rqmsg.dt_start, rqmsg.dt_end, to_chsarp=1)
msg.type = rqmsg.type
yield self.update_cache("r", user_uuid)
if len(rqmsg.err_id) == 0:
str_errs = ''
else:
str_errs = ' and a.fault_id in ({0}) '.format(
','.join([str(a) for a in rqmsg.err_id]))
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid, list(
rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(
','.join([str(a) for a in tml_ids]))
if rqmsg.confirm_type == 0:
str_confirm = ""
else:
str_confirm = " and a.confirm = {0}".format(
rqmsg.confirm_type)
if rqmsg.type == 0: # 现存故障
strsql = 'select a.fault_id,b.fault_name,a.rtu_id,a.date_create,a.date_create, \
c.rtu_phy_id,c.rtu_name,a.loop_id,a.lamp_id,a.remark,a.error_count,a.v,a.a,b.fault_name_define,d.loop_name, \
a.confirm,a.svr_ip,a.svr_name,a.app_name \
from {2}.info_fault_exist as a left join {0}.fault_types as b \
on a.fault_id=b.fault_id right join {0}.para_base_equipment as c on a.rtu_id=c.rtu_id \
left join {0}.para_rtu_loop_info as d on a.rtu_id=d.rtu_id and a.loop_id=d.loop_id \
where a.date_create>={1} {3}'.format(
self._db_name, sdt, self._db_name_data,
str_confirm)
if edt > 0:
strsql += ' and a.date_create<={0}'.format(edt)
if len(str_tmls) > 0:
strsql += ' {0}'.format(str_tmls)
if len(str_errs) > 0:
strsql += ' {0}'.format(str_errs)
strsql += ' order by a.date_create desc {0}'.format(
self._fetch_limited)
elif rqmsg.type == 1: # 历史故障
strsql = 'select a.fault_id,b.fault_name,a.rtu_id,a.date_create,a.date_remove, \
c.rtu_phy_id,c.rtu_name,a.loop_id,a.lamp_id,a.remark,a.lamp_id,a.v,a.a,b.fault_name_define,d.loop_name, \
a.confirm,a.svr_ip,a.svr_name,a.app_name \
from {3}.info_fault_history as a left join {0}.fault_types as b \
on a.fault_id=b.fault_id right join {0}.para_base_equipment as c on a.rtu_id=c.rtu_id \
left join {0}.para_rtu_loop_info as d on a.rtu_id=d.rtu_id and a.loop_id=d.loop_id \
where a.date_create <={1} and a.date_create >={2} {4}'.format(
self._db_name, edt, sdt, self._db_name_data,
str_confirm)
if len(str_tmls) > 0:
strsql += ' {0}'.format(str_tmls)
if len(str_errs) > 0:
strsql += ' {0}'.format(str_errs)
strsql += ' order by a.date_create desc {0}'.format(
self._fetch_limited)
elif rqmsg.type == 2: # 现存仅返回数量
strsql = '''select count(a.rtu_id) from {3}.info_fault_exist as a
where a.date_create >=0'''.format(
self._db_name, edt, sdt, self._db_name_data)
if len(str_tmls) > 0:
strsql += ' {0}'.format(str_tmls)
if len(str_errs) > 0:
strsql += ' {0}'.format(str_errs)
elif rqmsg.type == 3: # 历史仅返回数量
strsql = '''select count(a.rtu_id) from {3}.info_fault_history as a
where a.date_create <={1} and a.date_create >={2} {4}'''.format(
self._db_name, edt, sdt, self._db_name_data,
str_confirm)
if len(str_tmls) > 0:
strsql += ' {0}'.format(str_tmls)
if len(str_errs) > 0:
strsql += ' {0}'.format(str_errs)
if rqmsg.type in (0, 1):
np = 1
else:
np = 0
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
need_paging=np,
buffer_tag=rqmsg.head.paging_buffer_tag,
paging_idx=rqmsg.head.paging_idx,
paging_num=rqmsg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
if rqmsg.type in (0, 1):
for d in cur:
errview = msgws.QueryDataErr.ErrView()
errview.err_id = int(d[0])
errview.err_name = d[
13] if d[13] is not None else ''
errview.tml_id = int(d[2])
errview.dt_create = mx.switchStamp(int(d[3]))
errview.dt_remove = mx.switchStamp(int(d[4]))
errview.phy_id = int(
d[5]) if d[5] is not None else 0
errview.tml_name = d[
6] if d[6] is not None else ''
errview.tml_sub_id1 = int(d[7])
errview.tml_sub_id2 = int(d[8])
errview.remark = d[9]
errview.err_count = int(d[10])
errview.voltage = float(d[11])
errview.current = float(d[12])
# errview.err_name_custome = d[13]
errview.tml_loop_name = d[
14] if d[14] is not None else ""
# 武汉特殊，融断器开路，火零不平衡报警取消回路名称结尾的’火线’二字
if errview.err_id in (25, 26):
if errview.tml_loop_name.endswith(u'火线'):
errview.tml_loop_name = errview.tml_loop_name.replace(
u'火线', u'')
# 广州新增
errview.confirm_type = int(d[15])
errview.server_ip = d[16]
errview.server_name = d[17]
errview.app_name = d[18]
msg.err_view.extend([errview])
del errview
elif rqmsg.type in (2, 3):
msg.head.paging_record_total = cur[0][0]
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataMru(), msgws.QueryDataMru())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
yield self.update_cache("r", user_uuid)
sdt, edt = self.process_input_date(
rqmsg.dt_start, rqmsg.dt_end, to_chsarp=1)
if sdt + edt > 0:
strdt = ' and a.date_create>={0} and a.date_create<={1}'.format(
sdt, edt)
else:
strdt = ''
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid, list(
rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.meter_address in ({0}) '.format(
','.join([str(a) for a in tml_ids]))
if rqmsg.company_id == -1:
str_companyid = ''
else:
str_companyid = ' and a.company_id = {0} '.format(
rqmsg.company_id)
strsql = '''select a.date_create,a.meter_address,a.data_value,b.rtu_name,a.company_id
from {0}.meter_day as a
left join {0}.para_meter as b on a.meter_address=b.meter_address
where 1=1 {1} {2} {3} {4}'''.format(
self._db_name_data, strdt, str_tmls, str_companyid,
self._fetch_limited)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
dv = msgws.QueryDataMru.DataMruView()
dv.dt_create = mx.switchStamp(int(d[0]))
dv.tml_id = int(d[1])
dv.dt_mark = 0
dv.data_mark = 4
dv.mru_value = float(
d[2]) if d[2] is not 0 else 0.0
dv.mru_name = d[3] if d[3] is not None else ''
dv.company_id = d[4]
msg.data_mru_view.extend([dv])
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataMru(), msgws.QueryDataMru())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
yield self.update_cache("r", user_uuid)
sdt, edt = self.process_input_date(
rqmsg.dt_start, rqmsg.dt_end, to_chsarp=1)
if sdt + edt > 0:
strdt = ' and a.date_create>={0} and a.date_create<={1}'.format(
sdt, edt)
else:
strdt = ''
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid, list(
rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(
','.join([str(a) for a in tml_ids]))
if sdt == 0 and edt == 0: # 最新数据
strsql = '''select a.rtu_id,a.date_create,a.date_type_code,a.mru_type_code,a.mru_data,
(b.mru_ratio * a.mru_data) / 5 as elec,c.rtu_name
from {3}.data_mru_record as a
left join {0}.para_mru as b on a.rtu_id=b.rtu_id
left join {0}.para_base_equipment as c on c.rtu_id = a.rtu_id
where EXISTS
(select rtu_id,date_create from
(select rtu_id,max(date_create) as date_create from {3}.data_mru_record group by rtu_id) as t
where a.rtu_id=t.rtu_id and a.date_create=t.date_create) {1} {2} order by a.rtu_id,a.date_create desc'''.format(
self._db_name, strdt, str_tmls, self._db_name_data)
else:
strsql = '''select a.rtu_id,a.date_create,a.date_type_code,a.mru_type_code,a.mru_data,
(b.mru_ratio * a.mru_data) / 5 as elec,c.rtu_name
from {5}.data_mru_record as a
left join {0}.para_mru as b on a.rtu_id=b.rtu_id
left join {0}.para_base_equipment as c on c.rtu_id = a.rtu_id
where a.date_create>={1} and a.date_create<={2} {3} {4}'''.format(
self._db_name, sdt, edt, str_tmls,
self._fetch_limited, self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
dv = msgws.QueryDataMru.DataMruView()
dv.dt_create = mx.switchStamp(int(d[1]))
dv.tml_id = int(d[0])
dv.dt_mark = int(d[2])
dv.data_mark = int(d[3])
dv.mru_value = float(d[4])
dv.mru_elec = float(
d[5]) if d[5] is not None else 0
dv.mru_name = d[6] if d[5] is not None else ''
msg.data_mru_view.extend([dv])
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data
data = {
'uuid': user_id,
'pb2': base64.b64encode(rqmsg.SerializeToString())
}
# data = {'uuid': user_id, 'pb2': 'OABKA LGW1AH'}
r = pm.request('POST', url, fields=data, timeout=300.0, retries=False)
msg = msgif.SluElecDataGet()
msg.ParseFromString(base64.b64decode(r.data))
print(msg)
print('post finish')
time.sleep(0)
if __name__ == '__main__':
print time.localtime(mx.switchStamp(int(636142596164153726)))
# test_ws()
# url = baseurl + '/UpdatePassword'
# data = {'user_now':78, 'old_pwd':'123', 'new_pwd':'123'}
# r = pm.request('GET', url, fields=data, timeout = 20.0, retries=False)
# print(r.data)
# exit()
# test_submitsms()
# test_submittcs()
# a = time.time()
# print('sms finish ', time.time() - a)
# test_test()
# exit()
# for i in range(1):
test_userlogin()
# test_useredit()
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataEvents(), msgws.QueryDataEvents())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
yield self.update_cache("r", user_uuid)
if len(rqmsg.events_id) == 0:
str_events = ''
else:
str_events = ' a.operator_id in ({0}) '.format(','.join(
[str(a) for a in rqmsg.events_id]))
if len(rqmsg.tml_id) == 0:
str_tmls = ''
else:
str_tmls = ' a.rtu_id in ({0}) '.format(','.join(
[str(a) for a in rqmsg.tml_id]))
# 额外判断是否管理员,非管理员只能查询自己以及系统事件
if user_data['user_auth'] in libiisi.can_admin or user_data[
'is_buildin'] == 1:
if len(rqmsg.user_id) == 0:
str_users = ''
else:
str_users = ' instr("{0}",a.user_name) '.format(
','.join([str(b) for b in rqmsg.user_id]))
else:
str_users = ' a.user_name in ("{0}", "应答", "上次未发送成功...", "时间表:新建时间表", "补开时间表:新建时间表") '.format(
user_data['user_name'])
strsql = 'select a.date_create,a.user_name,a.operator_id,a.is_client_snd,a.rtu_id,a.contents,a.remark,b.name \
from {3}.record_operator as a \
left join {3}.operator_id_assign as b on a.operator_id=b.id \
where a.date_create<={1} and a.date_create>={2}'.format(
self._db_name, edt, sdt, self._db_name_data)
if len(str_events) > 0:
strsql += ' and {0}'.format(str_events)
if len(str_tmls) > 0:
strsql += ' and {0}'.format(str_tmls)
if len(str_users) > 0:
strsql += ' and {0}'.format(str_users)
strsql += ' ORDER BY a.date_create desc ' + self._fetch_limited
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
env = msgws.QueryDataEvents.DataEventsView()
env.events_id = int(d[2])
env.user_name = d[1]
env.tml_id = int(d[4]) if d[4] is not None else 0
env.events_msg = '{0} {1}'.format(d[5], d[6])
env.dt_happen = mx.switchStamp(int(d[0]))
env.events_name = d[7] if d[7] is not None else 'unknow'
msg.data_events_view.extend([env])
del env
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryEventsTimetableDo(), msgws.QueryEventsTimetableDo())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid,
list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(','.join(
[str(a) for a in tml_ids]))
strsql = 'select a.rtu_id,a.loop_id,a.is_open,a.rtu_reply_type,a.date_create \
from {0}.record_rtu_open_close_light_record as a \
where a.date_create<={1} and a.date_create>={2} {3}'.format(
self._db_name_data, edt, sdt, str_tmls)
if rqmsg.data_mark in (0, 1):
strsql += ' and is_open={0}'.format(rqmsg.data_mark)
if rqmsg.data_type in (1, 3):
strsql += ' and rtu_reply_type={0}'.format(
rqmsg.data_type)
strsql += self._fetch_limited
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
env = msgws.QueryEventsTimetableDo.TimetableDoView(
)
env.tml_id = d[0]
env.tml_loop_id = d[1]
env.data_mark = d[2]
env.data_type = d[3]
env.dt_send = mx.switchStamp(int(d[4]))
# env.dt_reply = mx.switchStamp(
# int(d[5])) if d[5] is not None else 0
msg.timetable_do_view.extend([env])
del env
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(
msgws.rqQueryDataElu(), msgws.QueryDataElu())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start,
rqmsg.dt_end,
to_chsarp=1)
msg.data_mark = rqmsg.data_mark
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid,
list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.leak_id in ({0}) '.format(','.join(
[str(a) for a in tml_ids]))
if rqmsg.data_mark == 0: # 最新数据
strsql = '''select b.leak_line_name,x.leak_id,x.date_create,x.leak_line_id,
a.auto_break_auto_alarm,a.state_alarm,a.state_on_off,a.upper_alarm_break_for_leak_temperature,
a.time_delay_break,a.alarm_value_leak_temperature,a.current_leak_temperature,a.leak_mode, c.rtu_name
from (select leak_id,max(date_create) as date_create,leak_line_id
from {2}.data_leak_line_record as a {1} group by leak_id,leak_line_id) as x
left join {2}.data_leak_line_record as a
on x.date_create=a.date_create and x.leak_id=a.leak_id and x.leak_line_id=a.leak_line_id
left join {0}.para_leak_line as b on a.leak_id=b.leak_id and a.leak_line_id=b.leak_line_id
left join {0}.para_base_equipment as c on a.leak_id=c.rtu_id'''.format(
self._db_name, str_tmls.replace('and', 'where'),
self._db_name_data)
elif rqmsg.data_mark == 1: #历史数据
strsql = '''select b.leak_line_name,a.leak_id,a.date_create,a.leak_line_id,
a.auto_break_auto_alarm,a.state_alarm,a.state_on_off,a.upper_alarm_break_for_leak_temperature,
a.time_delay_break,a.alarm_value_leak_temperature,a.current_leak_temperature,a.leak_mode,c.rtu_name
from {5}.data_leak_line_record as a
left join {0}.para_leak_line as b on a.leak_id=b.leak_id and a.leak_line_id=b.leak_line_id
left join {0}.para_base_equipment as c on a.leak_id=c.rtu_id
where a.date_create>={1} and a.date_create<={2} {3} {4}'''.format(
self._db_name, sdt, edt, str_tmls, self._fetch_limited,
self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num,
multi_record=[],
key_column=0)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
dv = msgws.QueryDataElu.DataEluView()
dv.tml_id = d[1]
dv.dt_receive = mx.switchStamp(d[2])
dv.loop_id = d[3]
dv.loop_name = d[0]
dv.alarm_set = d[4]
dv.alarm_status = d[5]
dv.door_status = d[6]
dv.up_limit = d[7]
dv.opt_delay = d[8]
dv.alarm_value = d[9]
dv.el_value = d[10]
dv.data_mode = d[11]
dv.elu_name = d[12] if d[12] is not None else ''
msg.data_elu_view.extend([dv])
del dv
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
def post(self):
user_data, rqmsg, msg, user_uuid = yield self.check_arguments(msgws.rqQueryDataLdu(),
msgws.QueryDataLdu())
if user_data is not None:
if user_data['user_auth'] in libiisi.can_read:
sdt, edt = self.process_input_date(rqmsg.dt_start, rqmsg.dt_end, to_chsarp=1)
msg.data_mark = rqmsg.data_mark
yield self.update_cache("r", user_uuid)
# 验证用户可操作的设备id
if 0 in user_data['area_r'] or user_data['is_buildin'] == 1:
if len(rqmsg.tml_id) > 0:
tml_ids = list(rqmsg.tml_id)
else:
tml_ids = []
else:
if len(rqmsg.tml_id) > 0:
tml_ids = self.check_tml_r(user_uuid, list(rqmsg.tml_id))
else:
tml_ids = libiisi.cache_tml_r[user_uuid]
if len(tml_ids) == 0:
msg.head.if_st = 11
if msg.head.if_st == 1:
if len(tml_ids) == 0:
str_tmls = ''
else:
str_tmls = ' and a.rtu_id in ({0}) '.format(','.join([str(a) for a in
tml_ids]))
if rqmsg.data_mark == 0: # 最新数据
strsql = '''select b.ldu_line_name,x.rtu_id,x.date_create,x.loop_id,a.ldu_voltage,a.ldu_current,
a.ldu_active_power,a.ldu_reactive_power,a.ldu_fault_param,a.ldu_fault_data,
a.ldu_pf_compensate,a.ldu_bright_rate,a.ldu_puse,
a.ldu_impedance,a.ldu_impedance_count,a.ldu_hop_count,a.remark from
(select rtu_id,max(date_create) as date_create,loop_id
from {2}.data_ldu_loop_record {1} group by rtu_id,loop_id) as x
left join {2}.data_ldu_loop_record as a on x.date_create=a.date_create
and x.rtu_id=a.rtu_id and x.loop_id=a.loop_id
left join {0}.para_ldu_line as b on a.rtu_id=b.ldu_fid and a.loop_id=b.ldu_line_id'''.format(
self._db_name, str_tmls.replace('and', 'where'), self._db_name_data)
elif rqmsg.data_mark == 1: #历史数据
strsql = '''select b.ldu_line_name,a.rtu_id,a.date_create,a.loop_id,a.ldu_voltage,a.ldu_current,
a.ldu_active_power,a.ldu_reactive_power,a.ldu_fault_param,a.ldu_fault_data,
a.ldu_pf_compensate,a.ldu_bright_rate,a.ldu_puse,
a.ldu_impedance,a.ldu_impedance_count,a.ldu_hop_count,a.remark
from {5}.data_ldu_loop_record as a
left join {0}.para_ldu_line as b on a.rtu_id=b.ldu_fid and a.loop_id=b.ldu_line_id
where a.date_create>={1} and a.date_create<={2} {3} {4}'''.format(
self._db_name, sdt, edt, str_tmls, self._fetch_limited, self._db_name_data)
record_total, buffer_tag, paging_idx, paging_total, cur = yield self.mydata_collector(
strsql,
need_fetch=1,
buffer_tag=msg.head.paging_buffer_tag,
paging_idx=msg.head.paging_idx,
paging_num=msg.head.paging_num,
multi_record=[],
key_column=0)
if record_total is None:
msg.head.if_st = 45
else:
msg.head.paging_record_total = record_total
msg.head.paging_buffer_tag = buffer_tag
msg.head.paging_idx = paging_idx
msg.head.paging_total = paging_total
for d in cur:
dv = msgws.QueryDataLdu.DataLduView()
dv.tml_id = d[1]
dv.dt_receive = mx.switchStamp(d[2])
dv.loop_id = d[3]
dv.loop_name = d[0]
dv.voltage = d[4]
dv.current = d[5]
dv.active_power = d[6]
dv.reactive_power = d[7]
dv.alarm_set = d[8]
dv.alarm_status = d[9]
dv.loop_status = 0
alarm_s = '{0:08b}'.format(d[8])
alarm_d = '{0:08b}'.format(d[9])
if alarm_d[4] == '1': # 无电告警
if alarm_s[1] == alarm_d[1] == '1':
dv.loop_status = 2
if alarm_s[2] == alarm_d[2] == '1':
dv.loop_status = 1
# C#版逻辑
# if alarm_d[1] == alarm_s[1]:
# dv.loop_status = 2
# elif alarm_s[2] == '1' and alarm_s[3] == '1':
# if alarm_d[2] == '1' and alarm_d[3] == '1':
# dv.loop_status = 1
# else:
# if alarm_d[2] == alarm_s[2] == '1' or alarm[3] == alarm_s[3] == '1':
# dv.loop_status = 1
else: # 有电告警
if alarm_s[7] == alarm_d[7] == '1':
dv.loop_status = 1
# 暂不用
dv.power_factor=d[10]
# dv.lighting_rate=d[11]
# dv.signal_strength=d[12]
# dv.impedance=d[13]
# dv.useful_signal=d[14]
# dv.all_signal=d[15]
msg.data_ldu_view.extend([dv])
del dv
del cur, strsql
self.write(mx.code_pb2(msg, self._go_back_format))
self.finish()
del msg, rqmsg, user_data, user_uuid
