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
