def get_doc_keywords(cls, params):
keywords = dict()
sql = "select rlcode, rltype, rlname, rlsymbol, rlnote from 352dt_replace_label_dict where doctype = %s order by rlsymbol;"
rllist = mysql_utils.Database().query_all(sql, (params['doctype'], ))
sql2 = "select tmcode, tmtype, tmname, tmsymbol, tmnote from 352dt_template_dict where doctype = %s order by tmsymbol;"
tmlist = mysql_utils.Database().query_all(sql2, (params['doctype'], ))
for row in tmlist:
sql4 = "select tminputcode, tminputtext from 352dt_template_recommend_content where tmcode = %s group by tminputcode order by tminputcode"
tminputtypelist = mysql_utils.Database().query_all(
sql4, (row['tmcode'], ))
for row2 in tminputtypelist:
sql5 = "select tmcontentcode, tmcontent, tmsource from 352dt_template_recommend_content where tmcode = %s and tminputcode=%s order by tmcontentcode"
tminputlist = mysql_utils.Database().query_all(
sql5, (row['tmcode'], row2['tminputcode']))
row2['tminputlist'] = tminputlist
row['tminputtypelist'] = tminputtypelist
sql3 = "select nlcode, nltype, nlname, nlcontent, nlsymbol, nlnote from 352dt_num_label_dict where doctype = %s order by nlsymbol;"
nllist = mysql_utils.Database().query_all(sql3, (params['doctype'], ))
keywords['rllist'] = rllist
keywords['nllist'] = nllist
keywords['tmlist'] = tmlist
return keywords
def get_doc_list(cls, params):
sql = "select 352dt_base_dict.dict_text, 352dt_doc_info.* from 352dt_doc_info " \
"left join 352dt_base_dict on 352dt_doc_info.doc_type=352dt_base_dict.dict_name " \
"where doc_user_id = %s and doc_state = 1 order by utime desc"
rows = mysql_utils.Database().query_all(sql, (params['uid'], ))
if len(rows) > 0:
doc_list = []
for row in rows:
docstate_sql = "select (select count(*) from 352dt_doc_content where docid = %s)" \
"/(select count(*) from 352dt_doc_base_content where doctype = %s " \
"and bcontent like %s) as result"
# result = mysql_utils.Database().query_one(docstate_sql, (row['doc_id'], row['doc_type'], '%((%'))
doc_user_id_sql = "select uname from 352dt_user_info where uid = %s "
result_uname = mysql_utils.Database().query_one(
doc_user_id_sql, (row['doc_user_id'], ))
doc = dict(
docid=row['doc_id'],
doctype=row['doc_type'],
dicttext=row['dict_text'],
docname=row['doc_name'],
docpath=row['doc_path'],
doc_user_id=row['doc_user_id'],
doc_user_name=result_uname['uname'],
u_ids=row['u_ids'],
permission=row['permission'],
doc_key=row['doc_key'],
docctime=row['ctime'].strftime("%Y-%m-%d %H:%M:%S"),
docutime=row['utime'].strftime("%Y-%m-%d %H:%M:%S"),
# docstate='%.1f%%' % (100*result['result'])
docstate='100%')
doc_list.append(doc)
return doc_list
else:
return None
def get_symbol_list(cls, params):
symbol_list = []
sql = "select rlsymbol, rlcontent from 352dt_replace_label_content " \
"where uid = %s and docid = %s"
rows = mysql_utils.Database().query_all(sql, (
params['uid'],
params['docid'],
))
rlsymbol_list = []
if len(rows) > 0:
for row in rows:
rlsymbol = dict(
symbol=row['rlsymbol'],
content=row['rlcontent'],
)
rlsymbol_list.append(rlsymbol)
symbol_list.extend(rlsymbol_list)
sql = "select nlsymbol, nlcontent from 352dt_num_label_content " \
"where uid = %s and docid = %s"
rows = mysql_utils.Database().query_all(sql, (
params['uid'],
params['docid'],
))
nlsymbol_list = []
if len(rows) > 0:
for row in rows:
nlsymbol = dict(
symbol=row['nlsymbol'],
content=row['nlcontent'],
)
nlsymbol_list.append(nlsymbol)
symbol_list.extend(nlsymbol_list)
sql = "select tmsymbol, tmcontent from 352dt_template_content " \
"where uid = %s and docid = %s"
rows = mysql_utils.Database().query_all(sql, (
params['uid'],
params['docid'],
))
tmsymbol_list = []
if len(rows) > 0:
for row in rows:
tmsymbol = dict(
symbol=row['tmsymbol'],
content=row['tmcontent'],
)
tmsymbol_list.append(tmsymbol)
symbol_list.extend(tmsymbol_list)
# symbol_list.extend([{"symbol": "((str_company_issuer))", "content": "((str_company_issuer111))"}, ])
return symbol_list
def update_security_msg(data_dict):
'''
:param data_dict:
:return:
'''
save_dict = {
'security_id': 'unknown',
'security_update_code': '0',
}
save_dict = dict_assignment(save_dict, data_dict)
return_label = False
data_conn = mysql_utils.Database()
sql1 = """
UPDATE yf_bim_security_msg SET security_state='{ss}', utime = now() where id = {si};
""".format(si=int(save_dict['security_id']),
ss=save_dict['security_update_code'])
try:
data_conn.insert_del_update(sql1)
return_label = True
except Exception as e:
logger.error(repr(e))
logger.info('update success')
return return_label
def register_name_check(cls, params):
sql = "select * from 352dt_user_info where uname = %s "
row = mysql_utils.Database().query_one(sql, (params['uname'], ))
if row is not None:
return False
else:
return True
def save_security_msg(data_dict):
'''
:param data_dict:
:return:
'''
save_dict = {
'security_time': 'unknown',
'device_id': 'unknown',
'device_name': 'unknown',
'security_msg': 'unknown',
'security_level': 'unknown',
'security_state': 'unknown'
}
save_dict = dict_assignment(save_dict, data_dict)
data_conn = mysql_utils.Database()
sql1 = """
INSERT INTO yf_bim_security_msg (security_time,device_id,device_name,security_msg,security_level,security_state,ctime, utime)
VALUES ('{st}', '{di}', '{dn}', '{sm}', '{sl}', '{ss}', now(), now());
""".format(st=save_dict['security_time'],
di=save_dict['device_id'],
dn=save_dict['device_name'],
sm=save_dict['security_msg'],
sl=save_dict['security_level'],
ss=save_dict['security_state'])
data_conn.insert_del_update(sql1)
logger.info('save data success')
def register(cls, params):
if cls.register_name_check(params):
passwd = hash.salted_password(params['passwd'])
if 'user_group' in params.keys():
user_group = params['user_group']
else:
user_group = '12345'
if 'phone' in params.keys():
phone = params['phone']
else:
phone = None
sql = "insert into 352dt_user_info (uid, uname, passwd, user_group, nickname, mail, phone, ctime, utime) " \
"values (UUID(), %s, %s, %s, %s, %s, %s, now(), now())"
rowcount = mysql_utils.Database().insert_del_update(
sql, (
params['uname'],
passwd,
user_group,
params['nickname'],
params['mail'],
phone,
))
if rowcount > 0:
return True
else:
return False
def get_security_msg():
'''
:param data_dict:
:return:
'''
return_list = []
data_conn = mysql_utils.Database()
sql1 = "select * from yf_bim_security_msg where security_state = '0' order by utime desc"
rows = data_conn.query_all(sql1)
# logger.debug('unity_id:{a}, wlw_id and name :{b}'.format(a=unity_id, b=row1))
# print(rows)
for row in rows:
logger.debug(row)
return_dict = {
'security_id': '',
'security_time': '',
'device_id': '',
'device_name': '',
'security_msg': '',
'security_level': '',
'security_state': ''
}
return_dict['security_id'] = row[0]
return_dict['security_time'] = row[1]
return_dict['device_id'] = row[2]
return_dict['device_name'] = row[3]
return_dict['security_msg'] = row[4]
return_dict['security_level'] = row[5]
return_dict['security_state'] = row[6]
return_list.append(return_dict)
return {'security_msg_list': return_list}
def get_doc_base_content(cls, params):
sql = "select * from 352dt_doc_base_content where cpcode = %s"
row = mysql_utils.Database().query_one(sql, (params['cpcode'], ))
if row is not None:
doc_base_content = dict(cpcode=row['cpcode'],
bcontent=row['bcontent'],
cpchange="0")
return doc_base_content
return row
def delete_doc(cls, params):
sql = "update 352dt_doc_info set doc_state='0' " \
"where doc_user_id=%s and doc_id = %s and doc_state = 1"
rowcount = mysql_utils.Database().insert_del_update(
sql, (
params['uid'],
params['docid'],
))
return rowcount
def get_template_recommend_content_type(cls, params):
sql = "select distinct tminputcode, tminputtext from 352dt_template_recommend_content " \
"where cpcode = %s and tmcode = %s "
rows = mysql_utils.Database().query_all(sql, (
params['cpcode'],
params['tmcode'],
))
if len(rows) > 0:
return rows
return None
def save_file(cls, url, doc_path, key):
user_doc_dir = os.path.abspath(
os.path.dirname(__file__) + '/' + '..' + '/' + '..' + '/user-doc')
doc_path = user_doc_dir + '/' + doc_path
f = urllib2.urlopen(url)
stream = f.read()
with open(doc_path, "wb") as code:
code.write(stream)
sql = "update 352dt_doc_info set utime = now() where doc_key = %s"
mysql_utils.Database().insert_del_update(sql, (key, ))
def get_doc_by_id(cls, params):
sql = "select * from 352dt_doc_info where doc_id = %s and doc_state = 1"
row = mysql_utils.Database().query_one(sql, (params['docid'], ))
if row is not None:
docstate_sql = "select (select count(*) from 352dt_doc_content where docid = %s)" \
"/(select count(*) from 352dt_doc_base_content where doctype = %s " \
"and bcontent like %s) as result"
result = mysql_utils.Database().query_one(
docstate_sql, (row['doc_id'], row['doc_type'], '%((%'))
doc = doc_model.Doc(
docid=row['doc_id'],
doctype=row['doc_type'],
docname=row['doc_name'],
docctime=row['ctime'].strftime("%Y-%m-%d %H:%M:%S"),
docutime=row['utime'].strftime("%Y-%m-%d %H:%M:%S"),
docstate='%.1f%%' % (100 * result['result']))
# 实例化一个对象，将查询结果添加给对象的属性
else:
return None
return doc.to_dict()
def login_auth(cls, params, login_time):
passwd = hash.salted_password(params['passwd'])
sql = "select * from 352dt_user_info where uname = %s and passwd = %s"
row = mysql_utils.Database().query_one(sql, (params['uname'], passwd))
if row is not None:
sql = "UPDATE 352dt_user_info set login_time = %s where uid = %s"
mysql_utils.Database().insert_del_update(sql,
(login_time, row['uid']))
user = user_model.User(uid=row['uid'],
uname=row['uname'],
usergroup=row['user_group'],
nickname=row['nickname'],
mail=row['mail'],
phone=row['phone'],
login_time=login_time)
# 实例化一个对象，将查询结果逐一添加给对象的属性
else:
return None
return user
def get_doc_type_keyword(cls, params):
contents = dict()
keyword = params['keyword']
if params.has_key('page'):
page = int(params['page'])
else:
page = 1
sql = "select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword1 = %s " \
"UNION all " \
"select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword2 = %s " \
"UNION all " \
"select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword_other like %s limit %s, %s"
sql2 = "select count(temp.tmcontent) as countNum from (select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword1 = %s " \
"UNION all " \
"select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword2 = %s " \
"UNION all " \
"select tmcontent from 352dt_template_recommend_content_kw " \
"where tmkeyword_other like %s) as temp"
countNum = mysql_utils.Database().query_one(sql2, (
keyword,
keyword,
"%" + keyword + "%",
))
contentlist = mysql_utils.Database().query_all(sql, (
keyword,
keyword,
"%" + keyword + "%",
10 * (page - 1),
10,
))
result = dict()
result['contentlist'] = contentlist
result = dict(countNum.items() + result.items())
return result
def insert_doc_and_get_doc(cls, params):
download_url = str(uuid.uuid1()) + ".docx"
sql1 = "insert into 352dt_doc_info(doc_id, doc_type, doc_name, doc_path, doc_user_id, ctime, utime, doc_state) " \
"values (uuid(), %s, %s, %s, %s, now(), now(), 1)"
sql2 = "select * from 352dt_doc_info " \
"where id = (select last_insert_id() from 352dt_doc_info limit 1)"
row = mysql_utils.Database().insert_del_update_query_one(
sql1,
sql2,
params1=(params['doctype'], params['docname'], download_url,
params['uid']))
return row
def get_replace_label_content(cls, params):
sql = "select * from 352dt_replace_label_content where docid = %s and rlsymbol = %s "
row = mysql_utils.Database().query_one(
sql, (params['docid'], params['rlsymbol']))
if row is not None:
replace_label_content = dict(rlcode=row['rlcode'],
rlname=row['rlname'],
rlcontent=row['rlcontent'],
rlsymbol=row['rlsymbol'],
rlnote=row['rlnote'],
rlchange="1")
return replace_label_content
return row
def get_doc_list(cls, params):
sql = "select * from 352dt_doc_info where doc_user_id = %s and doc_state = 1 order by utime desc"
rows = mysql_utils.Database().query_all(sql, (params['uid'], ))
if len(rows) > 0:
doc_list = []
for row in rows:
docstate_sql = "select (select count(*) from 352dt_doc_content where docid = %s)" \
"/(select count(*) from 352dt_doc_base_content where doctype = %s " \
"and bcontent like %s) as result"
result = mysql_utils.Database().query_one(
docstate_sql, (row['doc_id'], row['doc_type'], '%((%'))
doc = doc_model.Doc(
docid=row['doc_id'],
doctype=row['doc_type'],
docname=row['doc_name'],
docctime=row['ctime'].strftime("%Y-%m-%d %H:%M:%S"),
docutime=row['utime'].strftime("%Y-%m-%d %H:%M:%S"),
docstate='%.1f%%' % (100 * result['result']))
doc_list.append(doc.to_dict())
return doc_list
else:
return None
def get_replace_label_dict(cls, params):
sql = "select * from 352dt_replace_label_dict where doctype = %s and rlsymbol = %s "
params['doctype'] = params['cpcode'].split('-')[0]
row = mysql_utils.Database().query_one(
sql, (params['doctype'], params['rlsymbol']))
if row is not None:
replace_label_dict = dict(rlcode=row['rlcode'],
rlname=row['rlname'],
rlcontent=row['rlname'],
rlsymbol=row['rlsymbol'],
rlnote=row['rlnote'],
rlchange="0")
return replace_label_dict
return row
def loader_device_info():
# 数据表导入 设备信息表
df = pd.read_excel(
'/Users/pinetree_mac/ps_use/start_up_business/web_data_board_yongfeng/文档/数据显示对照表190305.xlsx',
'接口设备名称对照表',
header=None,
names=['a', 'id', 'name', 'code', 'zjcode'])
print(df.head(5))
print(len(df))
print(df.iloc[652:, :])
# exit()
print(df[pd.isnull(df['code']) == True])
df['code'] = df['code'].fillna('unknow')
print('---------------')
print(df[pd.isnull(df['code']) == True])
# exit()
data_conn = mysql_utils.Database()
dpid = ''
dtype = ''
for i in range(511, 653):
did = df['id'].values[i].split('_')
dtype = did[0]
dpid = did[1]
print(df['id'].values[i], dpid, dtype)
sql = """
INSERT INTO `yf_bim_db`.`yf_bim_device_info`
(
`device_id`,
`device_name`,
`device_code`,
`device_type`,
`device_position_id`,
`ctime`,
`utime`)
VALUES
( '{did}', '{dname}', '{dcode}', '{dtype}', '{dpid}', now(), now());
""".format(did=df['id'].values[i],
dname=df['name'].values[i],
dcode=df['code'].values[i],
dtype=dtype,
dpid=dpid)
data_conn.insert_del_update(sql)
def loader_position_info():
# 数据表导入 房间、楼层、位置信息表
df = pd.read_excel(
'/Users/pinetree_mac/ps_use/start_up_business/web_data_board_yongfeng/文档/数据显示对照表190221.xls',
'Sheet3',
header=None,
names=['a', 'id', 'name'])
print(df.head(5))
print(df['id'].values[139])
if df['id'].values[0][0:1] == 'b':
print('--------', df['id'].values[0])
# exit()
data_conn = mysql_utils.Database()
fid = ''
ptype = ''
for i in range(0, 140):
if df['id'].values[i][0:1] == 'b':
# print('--------', df['id'].values[0])
fid = 'area'
elif df['id'].values[i][0:1] == 'f':
fid = 'building_a'
elif df['id'].values[i][0:1] == 'r':
fid = 'floor_' + df['id'].values[i][5:8]
else:
print(' -------- error --------')
print(df['id'].values[i], fid)
sql = """
INSERT INTO `yf_bim_db`.`yf_bim_position_info`
(`position_id`,
`position_name`,
`position_type`,
`position_father_id`,
`ctime`,
`utime`)
VALUES
('{pid}','{pname}','{ptype}','{pfid}', now(), now());
""".format(pid=df['id'].values[i],
pname=df['name'].values[i],
ptype='position',
pfid=fid)
data_conn.insert_del_update(sql)
def doc_check_t(cls, params):
sql = "select tminputcode, tmcontentcode, tmcontent, ctime, tmsource " \
"from 352dt_template_recommend_content where cpcode = %s and tmcode = %s and tminputcode = %s"
rows = mysql_utils.Database().query_all(sql, (
params['cpcode'],
params['tmcode'],
params['tminputcode'],
))
if len(rows) > 0:
for row in rows:
row['tmctime'] = row.pop('ctime').strftime('%Y-%m-%d %H:%M:%S')
return rows
else:
return None
def check_uid(cls, params):
sql = "select * from 352dt_user_info where uid = %s"
row = mysql_utils.Database().query_one(sql, (params['uid'], ))
if row is not None:
user = user_model.User(uid=row['uid'],
uname=row['uname'],
usergroup=row['user_group'],
nickname=row['nickname'],
mail=row['mail'],
phone=row['phone'])
# 实例化一个对象，将查询结果逐一添加给对象的属性
else:
return None
return user
def get_template_content(cls, params):
sql = "select * from 352dt_template_content where docid = %s and tmsymbol = %s "
row = mysql_utils.Database().query_one(sql, (
params['docid'],
params['tmsymbol'],
))
if row is not None:
template_content = dict(tmcode=row['tmcode'],
tmtype=row['tmtype'],
tmname=row['tmname'],
tmsymbol=row['tmsymbol'],
tmnote=row['tmnote'],
tmcontent=row['tmcontent'],
tmchange="1")
return template_content
return row
def get_formula(cls, params):
# 通过params里面的'cpcode'得到所有公式formulas
sql = "select * from 352dt_num_label_dict " \
"where doctype = %s and nltype = 'output' "
params['doctype'] = params['cpcode'].split('-')[0]
rows = mysql_utils.Database().query_all(sql, (params['doctype'], ))
if len(rows) > 0:
formulas = []
for row in rows:
formula = dict(lcode=row["nlcode"],
lsymbol=row['nlsymbol'],
lcontent=row['nlcontent'])
formulas.append(formula)
return formulas
else:
return None
def get_num_label_content(cls, params):
sql = "select * from 352dt_num_label_content where docid = %s and nlsymbol = %s "
row = mysql_utils.Database().query_one(sql, (
params['docid'],
params['nlsymbol'],
))
if row is not None:
num_label_content = dict(nlcode=row['nlcode'],
nltype=row['nltype'],
nlname=row['nlname'],
nlsymbol=row['nlsymbol'],
nlcontent=row['nlcontent'],
nlnote=row['nlnote'],
nlchange="1")
return num_label_content
return row
def get_template_dict(cls, params):
sql = "select * from 352dt_template_dict where doctype = %s and tmsymbol = %s "
params['doctype'] = params['cpcode'].split('-')[0]
row = mysql_utils.Database().query_one(sql, (
params['doctype'],
params['tmsymbol'],
))
if row is not None:
template_dict = dict(tmcode=row['tmcode'],
tmtype=row['tmtype'],
tmname=row['tmname'],
tmsymbol=row['tmsymbol'],
tmnote=row['tmnote'],
tmcontent=row['tmname'],
tmchange="0")
return template_dict
return row
def doc_chapter(cls, params):
doc_dict = cls.get_doc_by_id(params)
if doc_dict is None:
return None
sql = "select * from 352dt_doc_base_content " \
"where doctype = %s"
rows = mysql_utils.Database().query_all(sql, (doc_dict['doctype'], ))
if len(rows) > 0:
doc_chapter_list = []
for row in rows:
doc_chapter = dict(cptitle=row['cptitle'],
cpcode=row['cpcode'],
level=row['cplevel'],
next=row['cpnext'])
doc_chapter_list.append(doc_chapter)
return [doc_dict, doc_chapter_list]
return [doc_dict, None]
def get_wlw_id_by_unity_id(unity_id):
'''
get name by check_id
:return: name
'''
wlw_id = ''
bim_name = ''
data_conn = mysql_utils.Database()
sql1 = "select wlw_id, name from yf_bim_unity_wlw_id_check where unity_id = '{unid}'".format(
unid=unity_id)
row1 = data_conn.query_one(sql1)
logger.debug('unity_id:{a}, wlw_id and name :{b}'.format(a=unity_id,
b=row1))
print(row1)
if row1 != None:
wlw_id = row1[0]
bim_name = row1[1]
return wlw_id, bim_name
def login_auth(cls, params):
# passwd = hash.salted_password(params['password'])
passwd = params['password']
sql = "select * from yf_bim_user_info where uname = %s and passwd = %s"
row = mysql_utils.Database().query_one(sql, (params['uname'], passwd))
print(row)
if row is not None:
# user = User(uid=row['uid'], uname=row['uname'], usergroup=row['user_group'],
# nickname=row['nickname'], mail=row['mail'], phone=row['phone'])
user = User(uid=row[1],
uname=row[2],
usergroup=row[4],
nickname=row[5],
mail=row[6],
phone=row[7])
# user = User(row)
# 实例化一个对象，将查询结果逐一添加给对象的属性
else:
return None
return user
