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