def findByPage(self, page): # pageNum 当前页 # pageCount 总页数 pageSize = page.get("pageSize", constants.pageSize) where = "1=1 " order = None cond = page.get("cond") if cond: if cond.get("userNum"): where = where + " and userNum=$userNum " if cond.get("noStatus"): where = where + " and status not in $noStatus " if cond.get("status"): where = where + " and status = $status " if cond.get("headNum"): where = where + " and headNum like '%' || $headNum || '%' " if cond.get("order"): order = cond.get("order") print "where: " + str(where) print "cond: " + str(cond) count = db.select('imgHead', what="count(*) as count", where=where, vars=cond).list()[0].count headers = db.select('imgHead', where=where, vars=cond, limit=pageSize, offset=pageSize * (page.get("pageNum", 1) - 1), order=order,).list() page["pageCount"] = (count +pageSize - 1) / pageSize page["data"] = headers return page
def findByCond(self, cond): imgHeads = db.select("imgHead", where="userNum = $userNum and belong = $belong and status in ('TODO','WAIT') and appCode=$appCode", vars=cond, order="headOrd").list() for imgHead in imgHeads: headId = imgHead.get('headId') for line in db.select("imgLine", where="headId=$headId", vars={'headId':headId}, order="imgOrd", limit=1).list(): imgHead["imgPath"] = pathJoin("static/images/scan", imgHead.get("headNum"), "s_" + line.get("imgNameP")) + "?r=" + str(random.random()) break return imgHeads;
def list_users(email, is_active, start_index, page_size, order_by): if email == '' and is_active != '2': where_dict = {'is_active': is_active} elif email != '': where_dict = {'email': email} #FIXME: o sorting não funciona quando o email é NULL e status é todos (2) elif email == '' and is_active == '2': where_dict = {'is_active': '1'} user_list = db.select('vw_users', what='id, name, email, creator, created, lastupdated, lastupdatedby, lastlogin, comments, is_active', where=web.db.sqlwhere(where_dict), order=order_by, limit=page_size, offset=start_index).list() # get total users users_count = db.select('vw_users', what='count(0) as c', where=web.db.sqlwhere(where_dict))[0].c # initiate dictionary records = {} # initiate list resposta = [] if len(user_list) > 0: # set response message to OK records['Result'] = 'OK' # set per user informations for item in range(len(user_list)): # convert 'lastlogin', 'created' and 'lastupdated' from epoch to gmtime #FIXME: mudar o modo de armazenar data no banco if user_list[item].get('created'): user_list[item].update(created=time.strftime('%d/%m/%Y %H:%M:%S', time.localtime(user_list[item].get('created')))) if user_list[item].get('lastupdated'): user_list[item].update(lastupdated=time.strftime('%d/%m/%Y %H:%M:%S', time.localtime(user_list[item].get('lastupdated')))) if user_list[item].get('lastlogin'): user_list[item].update(lastlogin=time.strftime('%d/%m/%Y %H:%M:%S', time.localtime(user_list[item].get('lastlogin')))) resposta.append(user_list[item]) records['Records'] = resposta else: # no items retrieved from table records['Result'] = 'ERROR' records['Message'] = 'Nenhum usuário encontrado' # total records from table records['TotalRecordCount'] = users_count return json.dumps(records)
def get_categories(): result = db.select('tickets_category', what='id, category').list() categories = {} for i in range(len(result)): categories[result[i].get('id')] = result[i].get('category') jr = json.dumps(categories, sort_keys=True, indent=4) return jr
def findOtherHeads(self, imgHead): cond = { "userNum":imgHead.userNum, "belong":imgHead.belong, "headId":imgHead.headId } return db.select('imgHead', where="userNum=$userNum and belong = $belong and status in ('TODO','WAIT') and headId!=$headId", vars=cond, order="headOrd").list()
def moveImgLine(self, headId, lineIds): if lineIds is None or len(lineIds) == 0: return retHeadId = None imgQty = 0 imgOrd = self.getMaxOrd(headId) imgNameList = [] ids = "" for lineId in lineIds: ids += "," + lineId ids = ids[1:] for line in db.select("imgLine", where="lineId in (" + ids + ")").list(): if retHeadId is None: retHeadId = line["headId"] imgQty += 1 imgOrd += 1 line["headId"] = headId line["imgOrd"] = imgOrd db.update('imgLine', where="lineId=$lineId", vars={'lineId':line["lineId"]}, **line) imgNameList.append(line.get("imgNameP")) imgHeaderDest = self.updateHeadSizeAndQty(headId) imgHeaderSrc = self.updateHeadSizeAndQty(retHeadId) imgPathList = [] for imgName in imgNameList: imgPathList.append(pathJoin(imgHeaderSrc.get("headNum"), imgName)) imgPathList.append(pathJoin(imgHeaderSrc.get("headNum"), "s_" + imgName)) return retHeadId, imgPathList, imgHeaderDest.get("headNum")
def get_all(self, conditions=None, order=None, limit=None, offset=None): where = dict2where(conditions) return db.select(self._tb, where=where, order=order, limit=limit, offset=offset)
def list_categories(): #TODO: criar função que converte saida do banco em json result = db.select('tickets_category', what='id, category').list() categories = {} for i in range(len(result)): categories[result[i].get('id')] = result[i].get('category') jr = json.dumps(categories, sort_keys=True, indent=4) return jr
def deleteByHeadId(self, headId): imgNames = [] for line in db.select("imgLine", where="headId = $headId", vars={"headId":headId}).list(): imgNames.append(line.imgNameP) db.delete("ocrResult", where="lineId = $lineId", vars={"lineId":line.lineId}) db.delete("imgLine", where="headId = $headId", vars={"headId":headId}) db.delete("imgHead", where="headId = $headId", vars={"headId":headId}) return imgNames
def get_component(self, tcategory, tclass, tsubclass): result = db.select('tickets_category', what='id, category').list() categories = [] temp = [] for i in range(len(result)): temp = [result[i].get('id'), result[i].get('category')] categories.append(temp) jr = json.dumps(categories, sort_keys=True, indent=4) return jr
def get_categories(self): result = db.select("tickets_category", what="id, category").list() categories = [] temp = [] for i in range(len(result)): temp = [result[i].get("id"), result[i].get("category")] categories.append(temp) jr = json.dumps(categories, sort_keys=True, indent=4) return jr
def deleteByHeadIds(self, headIds): ids = "" for headId in headIds: ids += "," + str(headId) ids = ids[1:] lineList = db.select("imgLine", where="headId in (" + ids + ")").list() for line in lineList: db.delete("ocrResult", where="lineId = $lineId", vars={"lineId":line.lineId}) db.delete("imgLine", where="lineId = $lineId", vars={"lineId":line.lineId}) db.delete("imgHead", where="headId in (" + ids + ")") return lineList
def saveOrUpdateOcrResult(self, ocrResult): results = None if ocrResult and ocrResult.get('lineId') and ocrResult.get('ocrType'): results = db.select('ocrResult', where="lineId=$lineId and ocrType=$ocrType", vars={'lineId':ocrResult.get('lineId'), 'ocrType':ocrResult.get('ocrType')}).list() else: return None if results and len(results) > 0: ocrResult['resultId'] = results[0].get('resultId') self.update(ocrResult) else: db.insert("ocrResult", **ocrResult)
def fetch_join(tb = None, condition = None, join = None, limit = 10, offset = 0): if tb is None: return None where = dict2where(condition) result = db.select(tb, where = where, limit = limit, offset = offset) if join is None: return result # 返回的结果列表 result_joined = [] for row in result: # 遍历出的每一行为一个字典,默认添加{主表:主表该行结果} row_joined = {tb : row} for join_table, join_fields in join.items(): condition = {join_fields[1] : getattr(row, join_fields[0])} where = dict2where(condition) join_table_result = db.select(join_table, where = where) # join 查到的结果字典来更新行结果字典 row_joined.update({join_table : join_table_result}) #最终将包含join行的结果字典追加到返回的结果列表中 result_joined.append(row_joined) return result_joined
def fetch_join(tb=None, condition=None, join=None, limit=10, offset=0): if tb is None: return None where = dict2where(condition) result = db.select(tb, where=where, limit=limit, offset=offset) if join is None: return result # 返回的结果列表 result_joined = [] for row in result: # 遍历出的每一行为一个字典,默认添加{主表:主表该行结果} row_joined = {tb: row} for join_table, join_fields in join.items(): condition = {join_fields[1]: getattr(row, join_fields[0])} where = dict2where(condition) join_table_result = db.select(join_table, where=where) # join 查到的结果字典来更新行结果字典 row_joined.update({join_table: join_table_result}) #最终将包含join行的结果字典追加到返回的结果列表中 result_joined.append(row_joined) return result_joined
def findByHeadId(self, headId): from imgHeadDao import ImgHeadDao imgHeadDao = ImgHeadDao() lines = db.select('imgLine', where="headId=$headId", vars={"headId":headId}, order="imgOrd").list() imgHead = imgHeadDao.getById(headId) for line in lines: line['imgPathS'] = pathJoin("static/images/scan", imgHead.get("headNum"), "s_" + line.get("imgNameP")) + "?r=" + str(random.random()) line['imgPathO'] = pathJoin("static/images/scan", imgHead.get("headNum"), line.get("imgNameP")) + "?r=" + str(random.random()) fullName = line.get("imgNameS").split(".") line['imgName'] = fullName[0] line['imgExt'] = fullName[1] otherHeads = imgHeadDao.findOtherHeads(imgHead) return imgHead, otherHeads, lines if lines else []
def editImgLine(self, lineId, imgOrd, imgNameS): dbImgLine = db.select("imgLine", where="lineId=$lineId", vars={"lineId":lineId}).list()[0] if imgOrd is not None and imgOrd != "": imgOrd = int(imgOrd) dbImgOrd = dbImgLine["imgOrd"] headId = dbImgLine.get("headId") if dbImgOrd > imgOrd: db.query("update imgLine set imgOrd = imgOrd + 1 where headId = " + str(headId) + " and imgOrd >= " + str(imgOrd) + " and imgOrd <" + str(dbImgOrd)) elif dbImgOrd < imgOrd: db.query("update imgLine set imgOrd = imgOrd - 1 where headId = " + str(headId) + " and imgOrd > " + str(dbImgOrd) + " and imgOrd <=" + str(imgOrd)) dbImgLine["imgOrd"] = imgOrd if imgNameS is not None and imgNameS != "": dbImgLine["imgNameS"] = imgNameS db.update("imgLine", where="lineId=$lineId", vars={"lineId":dbImgLine.lineId}, **dbImgLine) return dbImgLine["headId"]
def saveAll(self, headId, imgLines): lineIds = [] dbImgLines = db.select('imgLine', where="headId=$headId", vars={"headId":headId}) for imgLine in dbImgLines: lineIds.append(imgLine.get("lineId")) imgOrd = self.getMaxOrd(headId) ret = [] for imgLine in imgLines: if imgLine.get("lineId") in lineIds: db.update('imgLine', where="lineId=$lineId", vars={'lineId':imgLine.get("lineId")}, **imgLine) ret.append(self.getByLineId(imgLine.get("lineId"))) else: imgOrd += 1 imgLine['imgOrd'] = imgOrd imgLine["headId"] = headId lineId = db.insert('imgLine', **imgLine) ret.append(self.getByLineId(lineId)) return ret
def getByLineId(self, lineId): results = db.select('ocrResult', where="lineId=$lineId", vars={'lineId':lineId}).list() if not results or len(results)<2: return None programResult = {} checkResult = {} for ret in results: if ret.ocrType == ocrResultType.PROGRAM: programResult = ret else: checkResult = ret programResult['resultId'] = checkResult['resultId'] for key, val in programResult.items(): if key[-1] == 'R' and val == '0': programResult[key[:-1] + 'C'] = 'label-warning' for key, val in checkResult.items(): if key[-1] == 'R' and val == '0': programResult[key[:-1]] = checkResult[key[:-1]] programResult[key[:-1] + 'C'] = 'label-danger' return programResult
def findImgHead(self, cond): where = "1=1 " order = None if cond: if cond.get("userNum"): where = where + " and userNum=$userNum " if cond.get("noStatus"): where = where + " and status not in $noStatus " if cond.get("status"): where = where + " and status = $status " if cond.get("headNum"): where = where + " and headNum like '%' || $headNum || '%' " if cond.get("order"): order = cond.get("order") if cond.get("uploadTimeStart") and cond.get("uploadTimeEnd"): where = where + "and uploadTime >= $uploadTimeStart and uploadTime <= $uploadTimeEnd " elif cond.get("uploadTimeStart"): where = where + "and uploadTime >= $uploadTimeStart " elif cond.get("uploadTimeEnd"): where = where + "and uploadTime <= $uploadTimeEnd " if cond.get("company"): where = where + "and company like '%' || $company || '%' " imgHeadList = db.select('imgHead', where=where, vars=cond, order=order,).list() return imgHeadList
def getByPickupCode(self, pickupCode): for imgHead in db.select('box', where="pickupCode=$pickupCode", vars={"pickupCode": pickupCode}).list(): return imgHead
def get_user_by_id(id): user = db.select('users', what='id, name, email, creator, created, lastupdated, lastupdatedby, lastlogin, is_active', vars=dict(id=id), where='id=$id').list() return user
def get_one(self, conditions=None, what='*', order=None): where = dict2where(conditions) try: return db.select(self._tb, where=where, order=order, limit=1)[0] except IndexError: return None
def getById(self, resultId): for result in db.select("ocrResult", where="resultId=$resultId", vars={"resultId":resultId}): return result
def findAll(self): return db.select("box").list()
def getByCond(self, cond): for imgHead in db.select("imgHead", where="userNum = $userNum and headNum = $headNum", vars=cond, order="headOrd").list(): return imgHead return None
def getByHeadNum(self, headNum): for imgHead in db.select('imgHead', where="headNum=$headNum", vars={"headNum":headNum}).list(): return imgHead return None
def getById(self, headId): for imgHead in db.select('imgHead', where="headId=$headId", vars={"headId":headId}).list(): return imgHead return None
def get_user_by_email(email): result = db.select('users', vars=dict(email=email), where=web.db.sqlwhere({'email': email, 'is_active': '1'})).list() return web.listget(result, 0, {})
def findAllAsDict(self): configDict = {} configList = db.select("config").list() for entry in configList: configDict[entry.code] = entry.val return configDict
def findByHeadNum(self, headNum): for head in db.select("imgHead", where="headNum = $headNum", vars={"headNum":headNum}).list(): return head return None
def is_email_available(email): return not db.select('users', vars=dict(email=email), what='count(id) as c', where='email = $email')[0].c
def getValueByKey(self, key): for config in db.select("config", where="code = $code", vars={"code":key}).list(): return config.val