clinical-逻辑核查数据的操作
1. 前端页面样式
2. 前端代码
添加：
展示：
修改
删除
3. 后台代码
封装的DAO类数据
1 # coding: utf-8 2 3 from pdform.services.db.dbCore import GCP_LogicalVerify, GCP_LogicalVerifyRecord, GCP_ProjectSubject 4 from pdform import db, app 5 from pdform.services.util import Util 6 from pdform.services.sysParams import SYSParams 7 from pdform.services.appStaticVal import PDFormApp 8 from gcp.appStaticVal import GCPApp 9 from dataservices.config import * 10 from sqlalchemy import and_, or_, func, desc 11 from gcp.dao.project_subject_dao import GCPProjectSubjectDao 12 from gcp.dao.project_global_var_dao import ProjectGlobalVarDao 13 14 import traceback 15 from xlrd import * 16 import xlrd 17 import js2py 18 import re 19 20 class GCPLogicalVerifyDao: 21 """ 22 逻辑核查 23 """ 24 def __init__(self): 25 self.projectGlobalVarDao = ProjectGlobalVarDao() 26 27 def get_status_message(self, status): 28 """ 29 根据核查状态返回信息 30 :param status: 31 :return: 32 """ 33 if status == GCPApp.TYPE_LV_STATUS_PASSED: 34 return u"通过" 35 elif status == GCPApp.TYPE_LV_STATUS_FAILED: 36 return u"失败" 37 return u"未知" 38 39 def get_logical_verify(self, lv_id): 40 """ 41 获取单条核查项 42 :param lv_id: 43 :return: 44 """ 45 logical_verify = db.query(GCP_LogicalVerify).filter(GCP_LogicalVerify.lv_id == lv_id).first() 46 print(11,logical_verify) 47 if not logical_verify: 48 return None 49 logical_verify_dict = Util.row2dict(logical_verify) 50 print(222,logical_verify) 51 return logical_verify_dict 52 53 def get_logical_verifies(self, project_id, params): 54 """ 55 返回核查项列表 56 :param project_id: 57 :param params: 58 :return: 59 """ 60 is_slice, is_slice_success, query_start, query_end = SYSParams.check_fetch_query_start_and_end(params) 61 if is_slice and not is_slice_success: 62 return [], 0 63 64 # 初始化 65 query = db.query(GCP_LogicalVerify) 66 print(1111,query) 67 68 # 项目 69 if project_id is not None: 70 query = query.filter(GCP_LogicalVerify.project_id == project_id) 71 print(222,query) 72 73 # 模糊匹配 74 has_search_text, search_text = SYSParams.check_fetch_query_search_text(params) 75 if has_search_text: 76 search_text = "%" + search_text + "%" 77 query = query.filter(GCP_LogicalVerify.name.like(search_text)) 78 print(3333,query) 79 80 # 滤重和排序 81 query = query.distinct(GCP_LogicalVerify.lv_id).order_by(GCP_LogicalVerify.create_date) 82 print(444,query) 83 84 # 查询结果 85 all_logical_verify_array = query.all() 86 print(555,all_logical_verify_array) 87 total = len(all_logical_verify_array) 88 89 if is_slice: 90 logical_verify_array = all_logical_verify_array[query_start:query_end] 91 else: 92 logical_verify_array = all_logical_verify_array 93 return Util.rows2dict(logical_verify_array), total 94 95 def check_exist_logical_verify(self, project_id, name, type, lv_id): 96 """ 97 判断核查项是否重名 98 :param project_id: 99 :param name: 100 :return: is_exist 101 """ 102 logical_verify_array = db.query(GCP_LogicalVerify)\ 103 .filter(GCP_LogicalVerify.project_id == project_id)\ 104 .filter(GCP_LogicalVerify.type == type)\ 105 .filter(GCP_LogicalVerify.name == name)\ 106 .all() 107 lv_count = len(logical_verify_array) 108 if lv_count <= 0: 109 return False 110 if not lv_id or lv_count > 1: 111 return True 112 logical_verify = logical_verify_array[0] 113 if logical_verify.lv_id == lv_id: 114 return False 115 return True 116 117 def add_logical_verify(self, project_id, logical_verify_dict): 118 """ 119 添加一条核查项 120 :param project_id: 121 :param logical_verify_dict: 122 :return: is_success, lv_id, error_code 123 """ 124 try: 125 is_created = False 126 logical_verify = db.query(GCP_LogicalVerify)\ 127 .filter(GCP_LogicalVerify.project_id == project_id)\ 128 .filter(GCP_LogicalVerify.name == logical_verify_dict["name"])\ 129 .first() 130 if not logical_verify: 131 is_created = True 132 logical_verify = GCP_LogicalVerify() 133 logical_verify.lv_id = Util.getUUID() 134 logical_verify.project_id = project_id 135 logical_verify.create_date = Util.getDateTime() 136 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "name"): 137 logical_verify.name = logical_verify_dict["name"] 138 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "script"): 139 logical_verify.script = logical_verify_dict["script"] 140 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "type"): 141 logical_verify.type = logical_verify_dict["type"] 142 if is_created: 143 db.add(logical_verify) 144 db.commit() 145 return True, logical_verify.lv_id 146 except Exception, ex: 147 db.rollback() 148 app.logger.error(traceback.format_exc()) 149 return False, None 150 151 def update_logical_verify(self, lv_id, logical_verify_dict): 152 """ 153 修改一条核查项 154 :param lv_id: 155 :param logical_verify_dict: 156 :return: is_success 157 """ 158 try: 159 logical_verify = db.query(GCP_LogicalVerify).filter(GCP_LogicalVerify.lv_id == lv_id).first() 160 if not logical_verify: 161 return False 162 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "name"): 163 logical_verify.name = logical_verify_dict["name"] 164 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "script"): 165 logical_verify.script = logical_verify_dict["script"] 166 if SYSParams.check_key_exist_and_not_none(logical_verify_dict, "type"): 167 logical_verify.type = logical_verify_dict["type"] 168 db.commit() 169 return True 170 except Exception, ex: 171 db.rollback() 172 app.logger.error(traceback.format_exc()) 173 return False 174 175 def delete_logical_verify(self, lv_id): 176 """ 177 删除一条核查项 178 :param lv_id: 179 :return: 180 """ 181 try: 182 logical_verify = db.query(GCP_LogicalVerify).filter(GCP_LogicalVerify.lv_id == lv_id).first() 183 if not logical_verify: 184 return False 185 db.delete(logical_verify) 186 db.commit() 187 return True 188 except Exception, ex: 189 db.rollback() 190 app.logger.error(traceback.format_exc()) 191 return False 192 193 def check_fetch_worksheet(self, xls_files): 194 """ 195 获取XLS的worksheet 196 :param xls_files: 197 :return: 198 """ 199 if not xls_files or len(xls_files) <= 0: 200 return None 201 xls_data = xls_files[0].stream.read() 202 xls_rd = xlrd.open_workbook(None, sys.stdout, 0, USE_MMAP, xls_data) 203 if xls_rd.nsheets <= 0: 204 return None 205 ws = xls_rd.sheet_by_index(0) 206 return ws 207 208 def get_cell_value(self, ws, i_row, i_col): 209 """ 210 获取指定字段的值 211 :param ws 212 :param i_row: 213 :param i_col 214 :return: 215 """ 216 try: 217 cell_value = ws.cell_value(i_row, i_col) 218 cell_value = cell_value if cell_value else "" 219 return cell_value 220 except Exception, ex: 221 app.logger.error(traceback.format_exc()) 222 return "" 223 224 def import_xls_files(self, project_id, xls_files): 225 """ 226 逻辑核查导入 227 :param project_id: 228 :param xls_files: 229 :return is_success, success_lines_count, failed_lines_array: 230 """ 231 232 # 校验XLS数据，如果不合法，则不执行操作 233 ws = self.check_fetch_worksheet(xls_files) 234 if not ws: 235 return False, 0, [] 236 237 failed_lines_array = [] 238 success_lines_count = 0 239 last_table_name = "" 240 for i_row in range(1, ws.nrows): 241 # 名称，脚本 242 if ws.ncols < 2: 243 failed_lines_array.append(i_row+1) 244 continue 245 246 # 名称不能为空 247 name = self.get_cell_value(ws, i_row, 0) 248 if not name: 249 failed_lines_array.append(i_row+1) 250 continue 251 logical_type = self.get_cell_value(ws, i_row, 2) 252 if not logical_type: 253 logical_type = "0" 254 logical_verify_dict = { 255 "name": name, 256 "type": logical_type, 257 "script": self.get_cell_value(ws, i_row, 1) 258 } 259 is_success, lv_id = self.add_logical_verify(project_id, logical_verify_dict) 260 if not is_success: 261 failed_lines_array.append(i_row+1) 262 continue 263 success_lines_count += 1 264 265 return True, success_lines_count, failed_lines_array 266 267 def get_logical_verify_records(self, project_id, params): 268 """ 269 返回核查记录列表 270 :param project_id: 271 :param params: 272 :return: 273 """ 274 is_slice, is_slice_success, query_start, query_end = SYSParams.check_fetch_query_start_and_end(params) 275 if is_slice and not is_slice_success: 276 return [], 0 277 278 # 初始化 279 query = db.query(GCP_LogicalVerifyRecord, GCP_ProjectSubject)\ 280 .join(GCP_ProjectSubject, and_(GCP_ProjectSubject.project_id == GCP_LogicalVerifyRecord.project_id, 281 GCP_ProjectSubject.dm_id == GCP_LogicalVerifyRecord.dm_id)) 282 283 # 项目 284 if project_id is not None: 285 query = query.filter(GCP_LogicalVerifyRecord.project_id == project_id) 286 287 # 受试者 288 if SYSParams.check_key_exist_and_not_none(params, "dm_id"): 289 query = query.filter(GCP_LogicalVerifyRecord.dm_id == params["dm_id"]) 290 291 # 模糊匹配 292 has_search_text, search_text = SYSParams.check_fetch_query_search_text(params) 293 if has_search_text: 294 search_text = "%" + search_text + "%" 295 query = query.filter(GCP_LogicalVerifyRecord.name.like(search_text)) 296 297 # 滤重和排序 298 query = query.distinct(GCP_LogicalVerifyRecord.lvr_id).order_by(GCP_LogicalVerifyRecord.verify_date) 299 300 # 查询结果 301 all_lvr_row_array = query.all() 302 total = len(all_lvr_row_array) 303 304 if is_slice: 305 lvr_row_array = all_lvr_row_array[query_start:query_end] 306 else: 307 lvr_row_array = all_lvr_row_array 308 lvr_dict_array = [] 309 for lvr_row in lvr_row_array: 310 logical_verify_record = lvr_row[0] 311 project_subject = lvr_row[1] 312 lvr_dict = Util.row2dict(logical_verify_record) 313 lvr_dict.update(Util.row2dict(project_subject)) 314 lvr_dict["verify_status_message"] = self.get_status_message(lvr_dict["verify_status"]) 315 lvr_dict_array.append(lvr_dict) 316 317 return lvr_dict_array, total 318 319 def clear_all_logical_verify_records(self, project_id): 320 """ 321 清除项目所有核查记录 322 :param project_id: 323 :return: 324 """ 325 try: 326 db.query(GCP_LogicalVerifyRecord).filter(GCP_LogicalVerifyRecord.project_id == project_id).delete() 327 db.commit() 328 return True 329 except Exception, ex: 330 db.rollback() 331 app.logger.error(traceback.format_exc()) 332 return False 333 334 def add_logical_verify_record(self, project_id, project_subject_dict, logical_verify_dict, logical_script_name, script_ret): 335 """ 336 添加核查记录 337 :param project_id: 338 :param project_subject_dict: 339 :param logical_verify_dict: 340 :param script_ret: 341 :return: 342 """ 343 try: 344 logical_verify_record = GCP_LogicalVerifyRecord() 345 logical_verify_record.lvr_id = Util.getUUID() 346 logical_verify_record.lv_id = logical_verify_dict["lv_id"] 347 logical_verify_record.project_id = project_id 348 logical_verify_record.dm_id = project_subject_dict["dm_id"] 349 logical_verify_record.name = logical_script_name 350 logical_verify_record.script = logical_verify_dict["script"] 351 logical_verify_record.verify_date = Util.getDateTime() 352 logical_verify_record.verify_status = \ 353 GCPApp.TYPE_LV_STATUS_PASSED if script_ret else GCPApp.TYPE_LV_STATUS_FAILED 354 db.add(logical_verify_record) 355 db.commit() 356 return True 357 except Exception, ex: 358 db.rollback() 359 app.logger.error(traceback.format_exc()) 360 return False 361 362 def translate_script(self, script, global_var_dict): 363 """ 364 翻译脚本 365 :param script: 366 :param global_var_dict: 367 :return: is_success, new_script 368 """ 369 variant_pattern = "\$[A-Za-z0-9_/]+\$" 370 variant_array = re.findall(variant_pattern, script, re.I) 371 if not variant_array: 372 return True, script 373 374 new_script = script 375 regex = re.compile(variant_pattern) 376 for variant in variant_array: 377 variant_name = variant[1:-1] 378 if variant_name not in global_var_dict: 379 return False, new_script 380 variant_value = global_var_dict[variant_name] 381 variant_value = '"%s"' % variant_value if variant_value else '' 382 new_script = regex.sub(variant_value, new_script, 1) 383 return True, new_script 384 385 def verify_subject(self, project_id, project_subject_dict, logical_verify_dict_array): 386 """ 387 核查受试者 388 :param project_id: 389 :param project_subject_dict: 390 :param logical_verify_dict_array: 391 :return: is_success, error_code, success_count, failed_count 392 """ 393 # Global variants from system 394 global_var_name_array, global_var_dict = \ 395 self.projectGlobalVarDao.get_global_vars(project_id, project_subject_dict["dm_id"], None) 396 397 success_count = 0 398 failed_count = 0 399 for logical_verify_dict in logical_verify_dict_array: 400 logical_verify_name = logical_verify_dict["name"] 401 is_success, logical_verify_name = self.translate_script(logical_verify_name, global_var_dict) 402 if not is_success: 403 logical_verify_name = logical_verify_dict["name"] 404 script = logical_verify_dict["script"] 405 if not script: 406 # Skip if script is empty 407 self.add_logical_verify_record( 408 project_id, project_subject_dict, logical_verify_dict, logical_verify_name, True) 409 success_count += 1 410 continue 411 412 # Translate script 413 is_success, new_script = self.translate_script(script, global_var_dict) 414 if is_success and new_script: 415 try: 416 # Evaluate script 417 script_ret = js2py.eval_js(new_script) 418 if script_ret: 419 # Script result is true 420 self.add_logical_verify_record( 421 project_id, project_subject_dict, logical_verify_dict, logical_verify_name, True) 422 success_count += 1 423 continue 424 except Exception, ex: 425 app.logger.error(traceback.format_exc()) 426 427 self.add_logical_verify_record( 428 project_id, project_subject_dict, logical_verify_dict, logical_verify_name, False) 429 failed_count += 1 430 431 return True, 0, success_count, failed_count 432 433 def verify_all(self, project_id, project_subject_dict_array): 434 """ 435 以受试者为核查对象遍历所有核查项 436 :param project_id: 437 :param project_subject_dict_array: 438 :return: is_success, error_code, success_count, failed_count 439 """ 440 if not project_subject_dict_array: 441 return True, 0, 0, 0 442 logical_verify_dict_array, total = self.get_logical_verifies(project_id, {}) 443 if not logical_verify_dict_array: 444 return True, 0, 0, 0 445 446 success_count = 0 447 failed_count = 0 448 for project_subject_dict in project_subject_dict_array: 449 is_success, error_code, subject_success_count, subject_failed_count = \ 450 self.verify_subject(project_id, project_subject_dict, logical_verify_dict_array) 451 if not is_success: 452 return False, error_code, 0, 0 453 success_count += subject_success_count 454 failed_count += subject_failed_count 455 456 return True, 0, success_count, failed_count 457 458 def get_screening_period_logical_verifies(self, project_id): 459 """ 460 返回核查项列表 461 :param project_id: 462 :param params: 463 :return: 464 """ 465 logical_verify_list = db.query(GCP_LogicalVerify)\ 466 .filter(GCP_LogicalVerify.project_id == project_id)\ 467 .filter(GCP_LogicalVerify.type == 0)\ 468 .all() 469 if not logical_verify_list: 470 return [] 471 return Util.rows2dict(logical_verify_list) 472 473 def screening_period_verify_subject(self, project_id, dm_id): 474 """ 475 核查受试者 476 :param project_id: 477 :param project_subject_dict: 478 :param logical_verify_dict_array: 479 :return: is_success, error_code, success_count, failed_count 480 """ 481 # Global variants from system 482 483 logical_verify_dict_array = self.get_screening_period_logical_verifies(project_id) 484 485 global_var_name_array, global_var_dict = \ 486 self.projectGlobalVarDao.get_global_vars(project_id, dm_id, None) 487 logical_verify_result = [] 488 for logical_verify_dict in logical_verify_dict_array: 489 script_name = logical_verify_dict["name"] 490 is_success, script_name = self.translate_script(script_name, global_var_dict) 491 if not is_success: 492 script_name = logical_verify_dict["name"] 493 script = logical_verify_dict["script"] 494 if not script: 495 logical_verify_result.append(script_name) 496 continue 497 # Translate script 498 is_success, new_script = self.translate_script(script, global_var_dict) 499 if is_success and new_script: 500 try: 501 # Evaluate script 502 script_ret = js2py.eval_js(new_script) 503 if script_ret: 504 logical_verify_result.append(script_name) 505 continue 506 except Exception, ex: 507 app.logger.error(traceback.format_exc()) 508 509 return logical_verify_result 510 511 #逻辑核查记录DAO 512 513 class GCPLogicalVerifyRecordDao: 514 515 """ 516 逻辑核查记录 517 """ 518 def __init__(self): 519 self.projectGlobalVarDao = ProjectGlobalVarDao() 520 521 def get_logical_verifies(self, project_id, params): 522 """ 523 返回核查项列表 524 :param project_id: 525 :param params: 526 :return: 527 """ 528 is_slice, is_slice_success, query_start, query_end = SYSParams.check_fetch_query_start_and_end(params) 529 if is_slice and not is_slice_success: 530 return [], 0 531 532 # 初始化 533 query = db.query(GCP_LogicalVerifyRecord) 534 535 # 项目 536 if project_id is not None: 537 query = query.filter(GCP_LogicalVerifyRecord.project_id == project_id) 538 539 # 模糊匹配 540 has_search_text, search_text = SYSParams.check_fetch_query_search_text(params) 541 if has_search_text: 542 search_text = "%" + search_text + "%" 543 query = query.filter(GCP_LogicalVerifyRecord.name.like(search_text)) 544 545 # 查询结果 546 all_logical_verify_array = query.all() 547 total = len(all_logical_verify_array) 548 549 if is_slice: 550 logical_verify_array = all_logical_verify_array[query_start:query_end] 551 else: 552 logical_verify_array = all_logical_verify_array 553 return Util.rows2dict(logical_verify_array), total
获取单条数据：
# coding: utf-8 from pdform.api_request import PDRequest from pdform.services.appStaticVal import PDFormApp from gcp.dao.logical_verify_dao import GCPLogicalVerifyDao from gcp.dao.project_subject_dao import GCPProjectSubjectDao class PDLogicalVerify(PDRequest): """ 逻辑核查 URI映射为： /api/v1/logical_verify """ API_NAME = "logical_verify" def __init__(self): """ 初始化 """ super(PDLogicalVerify, self).__init__("lv_id") self.logicalVerifyDao = GCPLogicalVerifyDao() #继承父类的对象 self.parser.add_argument("name") self.parser.add_argument("type") self.parser.add_argument("script") self.parser.add_argument("page_index", type=int) self.parser.add_argument("page_size", type=int) self.parser.add_argument("search_text") self.parser.add_argument("command") self.parser.add_argument("dm_id") def get_request(self, args, lv_id): """ 获取单条核查信息 :param lv_id: :return: """ logical_verify_dict = self.logicalVerifyDao.get_logical_verify(lv_id) print("222",logical_verify_dict["lv_id"]) # print(logical_verify_dict["project_id"]) print(logical_verify_dict["name"]) print(logical_verify_dict["type"]) print(logical_verify_dict["script"]) print(logical_verify_dict["create_date"]) if not logical_verify_dict: return PDFormApp.API_Error(PDFormApp.INFO_ERROR_DB_NONE), 200 return {"status": PDFormApp.STATUE_SUCCESS, "message": PDFormApp.STATUE_SUCCESS, "logical_verify": logical_verify_dict}, 200
打印结果：
('222', u'7e356c00822211e98840f4b7e2e95ca4')
11d262c0806a11e984fff4b7e2e95ca4
胜多负少
0
大幅度
2019-05-29 23:00:26
查看所有：
1 def list_request(self, args): 2 """ 3 获取所有核查信息 4 :return: 5 """ 6 project_id = args["project_id"] if args["project_id"] else "" 7 logical_verify_dict_array, total = self.logicalVerifyDao.get_logical_verifies(project_id, args) 8 9 for i in logical_verify_dict_array: 10 print("lv_id",i["lv_id"]) 11 print("projectid",i["project_id"]) 12 print("name",i["name"]) 13 print('type',i["type"]) 14 print("========") 15 return {"status": PDFormApp.STATUE_SUCCESS, "message": PDFormApp.STATUE_SUCCESS, 16 "logical_verify_list": logical_verify_dict_array, "total": total}, 200
打印结果：
('lv_id', u'6451ae1e807e11e98f55f4b7e2e95ca4')
('projectid', u'11d262c0806a11e984fff4b7e2e95ca4')
('name', u'1221')
('type', 0)
========
('lv_id', u'e54f6d4081f111e9b978f4b7e2e95ca4')
('projectid', u'11d262c0806a11e984fff4b7e2e95ca4')
('name', u'333')
('type', 1)
========
('lv_id', u'ec6f9d7081f111e980c8f4b7e2e95ca4')
('projectid', u'11d262c0806a11e984fff4b7e2e95ca4')
('name', u'3332')
('type', 0)
========
添加：
def add_request(self, args): """ 添加一条核查 :return: """ project_id = args["project_id"] if args["project_id"] else "" if args["command"] == "verify": dm_id = args["dm_id"] if dm_id: project_subject_dict = GCPProjectSubjectDao.get_subject(project_id, dm_id) if not project_subject_dict: return PDFormApp.API_Error(PDFormApp.INFO_ERROR_DB_NONE), 200 project_subject_dict_array = [project_subject_dict] else: project_subject_dict_array, total = GCPProjectSubjectDao().getProjectSubjects(args) if not project_subject_dict_array: return PDFormApp.API_Error(PDFormApp.INFO_ERROR_DB_NONE), 200 is_success, error_code, success_count, failed_count = \ self.logicalVerifyDao.verify_all(project_id, project_subject_dict_array) if not is_success: return PDFormApp.API_Error(error_code), 200 return {"status": PDFormApp.STATUE_SUCCESS, "message": PDFormApp.INFO_RUN_OK, "success_lvr_count": success_count, "failed_lvr_count": failed_count}, 200
if not args["name"]:
return PDFormApp.API_Error(PDFormApp.INFO_WARNING_LESS_PARAMETERS), 200
if self.logicalVerifyDao.check_exist_logical_verify(project_id, args["name"], args["type"], None):
return PDFormApp.API_Error(PDFormApp.INFO_WARNING_LOGICAL_VERIFY_ALREADY_EXIST), 200
is_success, lv_id = self.logicalVerifyDao.add_logical_verify(project_id, args)
if not is_success:
return PDFormApp.API_Error(PDFormApp.INFO_ERROR_DB_WRITE), 200
return {"status": PDFormApp.STATUE_SUCCESS, "message": PDFormApp.INFO_RUN_OK, "lv_id": lv_id}, 200
