def get_account_role_list(self, condition, page, count):
'''condition中的关键字：role_ids , account'''
skip_num = (page - 1) * count
acc_df = ''
account_ids = ''
account_df = ''
def process_func(db):
nonlocal acc_df, account_ids, account_df
collection_account_role = db['PT_Account_Role']
account_cur = collection_account_role.find(
{'role_id': {
'$in': condition['role_ids']
}})
account_df = pd.DataFrame(list(
account_cur[:])).drop_duplicates('account_id')
print(account_df, 'account_df')
if len(account_df) > 0:
account_ids = account_df['account_id'].tolist()
collection_account = db['PT_Account']
if len(condition['account']) > 0:
acc_cur = collection_account.find({
'id': {
'$in': account_ids
},
'account':
re.compile(condition['account'])
})
else:
acc_cur = collection_account.find(
{'id': {
'$in': account_ids
}})
acc_df = pd.DataFrame(list(acc_cur[:]))
process_db(self.db_addr, self.db_port, self.db_name, process_func)
if len(account_df) == 0:
return '无查询结果'
else:
res_df = pd.merge(account_df,
acc_df,
left_on='account_id',
right_on='id',
how='inner')
res_df = res_df[['account_id', 'account', 'date', 'role_id']]
counter = len(res_df)
if skip_num > counter:
return ''
elif skip_num <= counter and skip_num + count < counter:
res_list = dataframe_to_list(res_df.iloc[skip_num:counter, :])
else:
res_list = dataframe_to_list(res_df.iloc[skip_num:(skip_num +
count), :])
res = {'total': counter, 'result': res_list}
return res
def add_organization(self):
'''新增组织'''
sql = '''
select name,(case type WHEN 'WelfareCentre' THEN '养老院' WHEN 'Government' THEN '民政部门' WHEN 'Happiness' THEN '社区机构' END) as organization_type,contact1Name as contacts,phone as telephone,concat(ifnull('organization-',"defaultvalue"),pkOrganization) as id,pkOrganization as code from cm_organization'''
data = self.__get_table_data(sql=sql)
map_organization = dataframe_to_list(data[['id', 'code']])
add_field = {'id_card_type': '身份证', 'personnel_type': '2'}
res = dataframe_to_list(data)
for index, val in enumerate(res):
res[index] = dict(val, **add_field)
res[index]['organizational_info'] = val
self.__inser_table_data('PT_User', res)
self.__inser_table_data('Mapping_Organization', map_organization)
def process_func(db):
nonlocal res
parameter = ('cm_organization', 'Happiness', 'WelfareCentre',
'Provider')
sql = "select name,type,latitude,longitude,pkOrganization from (%s) where type in ('%s','%s','%s')" % parameter
data = pd.read_sql(sql=sql, con=db)
statistics = data.groupby(
['type']).size().to_frame(name='sum').reset_index()
apa.data = dataframe_to_list(data)
res = {
'data': dataframe_to_list(data),
'statistics': dataframe_to_list(statistics)
}
def query_data(self, func=None, x_field=None, y_field=None, isList=True):
self.data = pd.read_sql(sql=self.sql, con=self.get_engine())
if x_field:
self.data.rename(columns={x_field: 'name'}, inplace=True)
if y_field:
self.data.rename(columns={y_field: 'value'}, inplace=True)
self.data = func() if func else self.data
self.count = len(self.data)
return dataframe_to_list(self.data) if isList else self.data
def process_func(db):
nonlocal res
parameter = ('Happiness', 'WelfareCentre')
parameter_type = ((23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34,
35, 36, 38, 39, 40, 44, 45, 46), )
sql_home = 'select pkMember from mem_member_type where pkMemberType in %s' % parameter_type
sql = "select a.name,a.type,a.latitude,a.longitude from cm_organization as a,mem_member as b where a.pkOrganization = b.pkOrganization and a.type in ('%s','%s')" % parameter
data = pd.read_sql(sql=sql, con=db)
data_home = len(pd.read_sql(sql=sql_home, con=db))
statistics = data.groupby(
['type']).size().to_frame(name='sum').reset_index()
organization = data.groupby(
['name', 'latitude',
'longitude']).size().to_frame(name='sum').reset_index()
res = {
'statistics': dataframe_to_list(statistics),
'data': dataframe_to_list(organization)
}
res['statistics'].append({'sum': data_home, 'type': 'Provider'})
def add_person(self):
'''新增长者'''
sql = '''select a.name,concat(ifnull('user-',"defaultvalue"),b.pkMember) as id,b.pkMember as code,a.idNumber as id_card, a.mobilePhone as telephone,a.birthday as birth_date,(case a.sex WHEN 'Male' THEN '男' WHEN 'Female' THEN '女' END) as sex
from bd_personalinfo a,mem_member b
where a.pkPersonalInfo = b.pkPersonalInfo'''
data = self.__get_table_data(sql=sql)
map_user = dataframe_to_list(data[['id', 'code']])
res = dataframe_to_list(data)
add_field = {
'personnel_category': '4',
'id_card_type': '身份证',
'personnel_type': '1'
}
for index, val in enumerate(res):
res[index] = dict(val, **add_field)
res[index]['personnel_info'] = val
self.__inser_table_data('PT_User', res)
# 影射表
self.__inser_table_data('Mapping_User', map_user)
def get_current_account(self):
account_id = get_current_account_id(self.session)
acc_df = ''
def process_func(db):
nonlocal acc_df
collection_account = db['PT_Account']
acc_cur = collection_account.find({'id': account_id})
acc_df = pd.DataFrame(list(acc_cur[:]))
acc_df = acc_df.drop(["_id"], axis=1)
process_db(self.db_addr, self.db_port, self.db_name, process_func)
res_list = dataframe_to_list(acc_df)
return res_list
def get_role(self, ID):
res_df = ''
def process_func(db):
nonlocal res_df
collection_role = db['PT_Role']
res_cur = collection_role.find({'id': ID})
res_df = pd.DataFrame(list(res_cur[:]))
process_db(self.db_addr, self.db_port, self.db_name, process_func)
if res_df.empty:
return '无查询结果'
else:
res_df = res_df.drop(["_id"], axis=1)
res_list = dataframe_to_list(res_df)
return res_list
def find_collection(db, condition, isList=True, pageNo=1, pageSize=10):
''' 查询数据
Args:
db 查询表对象
condition 查询条件
isList 是否转化成list-True False-DataFrame
return:
res list/DataFrame数据对象
'''
res = []
data_list = list(
db.find(condition).skip((pageNo - 1) * pageSize +
1).limit(pageSize)[:])
if len(data_list):
pd_data = pd.DataFrame(data_list).drop(['_id'], axis=1)
res = dataframe_to_list(pd_data) if isList else pd_data
return res
def get_current_role(self):
account_id = self.session[SecurityConstant.account]
res_df = ''
def process_func(db):
nonlocal res_df
collection = db['PT_Account_Role']
tep_cur = collection.find({'account_id': account_id})
tep_df = pd.DataFrame(list(tep_cur[:]))
role_ids = tep_df['role_id'].tolist()
collection_role = db['PT_Role']
per_cur = collection_role.find({'id': {'$in': role_ids}})
res_df = pd.DataFrame(list(per_cur[:])).drop(['_id'], axis=1)
print(res_df, 'res_df')
process_db(self.db_addr, self.db_port, self.db_name, process_func)
res_list = dataframe_to_list(res_df)
return res_list
def get_role_list(self, condition, page, count):
skip_num = (page - 1) * count
res_df = ''
counter = ''
def process_func(db):
nonlocal res_df, counter
collection_role = db['PT_Role']
res_cur = collection_role.find(condition).skip(skip_num).limit(
count)
counter = collection_role.find(condition).count()
res_df = pd.DataFrame(list(res_cur[:]))
process_db(self.db_addr, self.db_port, self.db_name, process_func)
if res_df.empty:
return '无查询结果'
else:
res_df = res_df.drop(["_id"], axis=1)
res_list = dataframe_to_list(res_df)
#res=res_df.to_json(orient='index', force_ascii=False)
res = {'total': counter, 'result': res_list}
return res
def add_order(self):
sql = '''select pkOrder as OrderId,pkMember as userId,pkOrganization as organizationId,pkServicePackage as servicePackageId,pkServiceType as itemId from om_order'''
data = dataframe_to_list(self.__get_table_data(sql=sql))
self.__inser_table_data('serviceOrder', data)
def add_service_item(self):
'''新增服务项目'''
sql = '''select name,price,pkOperator,pkServiceType from om_servicetype'''
data = dataframe_to_list(self.__get_table_data(sql=sql))
self.__inser_table_data('serviceItem', data)
def _statics_data(self, isList=False):
self.data = self.data.groupby(
['name']).size().to_frame(name='value').reset_index()
self.data = dataframe_to_list(self.data) if isList else self.data
def query_data_by_sum(self, key):
data = pd.read_sql(sql=self.sql, con=self.get_engine())
statistics = data.groupby(
[key]).size().to_frame(name='value').reset_index()
return dataframe_to_list(statistics)