def delete(self, condition, limit=1):
where = dict2where(condition)
if where is not None:
sql_query = 'DELETE FROM ' + self._tb + ' WHERE ' + where + ' LIMIT 1'
db.query(sql_query)
else:
return None
def update(self, condition, values=None):
where = dict2where(condition)
update = dict2update(values)
if values is not None:
sql_query = 'UPDATE ' + self._tb + ' SET ' + update + ' WHERE ' + where + ' LIMIT 1'
db.query(sql_query)
return True
def delete(self, condition, limit = 1):
where = dict2where(condition)
if where is not None:
sql_query = 'DELETE FROM ' + self._tb + ' WHERE ' + where + ' LIMIT 1'
db.query(sql_query)
else:
return None
def update(self, condition, values = None):
where = dict2where(condition)
update = dict2update(values)
if values is not None:
sql_query = 'UPDATE ' + self._tb + ' SET ' + update + ' WHERE ' + where + ' LIMIT 1'
db.query(sql_query)
return True
def insert(self, values = None):
def q(x): return "(" + x + ")"
if values:
sql_query = 'INSERT INTO '+ self._tb + q(', '.join(values.keys())) + ' VALUES' + q(', '.join('\''+str(_value)+'\'' for _value in values.values()))
db.query(sql_query)
return self.last_insert_id()
else:
return None
def insert(self, values = None):
def q(x): return "(" + x + ")"
if values:
sql_query = 'INSERT INTO '+ self._tb + q(', '.join(values.keys())) + ' VALUES' + q(', '.join('\''+str(_value)+'\'' for _value in values.values()))
db.query(sql_query)
return self.last_insert_id()
else:
return None
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 find(self, page, cond=None):
results = db.query("SELECT COUNT(*) AS count FROM uploadTaskLog")
page.setTotal(results[0].count)
sql = "SELECT uploadTaskLog.*,invGroup.groupNum,invGroup.scanTime,invGroup.scanBy,invGroup.imgQty,invGroup.groupSize FROM uploadTaskLog JOIN invGroup on uploadTaskLog.groupId = invGroup.groupId where 1=1"
# if cond:
# c = copy.deepcopy(cond)
# if c.get('status'):
# sql += ' and status=' + c.get('status')
# if c.get('groupNum'):
# c['groupNum'] = "%" + c['groupNum'].upper() + "%"
# sql += ' and upper(groupNum) like ' + c['groupNum']
sql += " limit " + str(page.getOffset()) + "," + str(page.size)
sql += " order by uploadTaskLog.startTime DESC"
page.data = db.query(sql).list()
return page
def all(self):
categories = []
nodes = {}
for node in db.query("SELECT * FROM `node`"):
category_id = node["category_id"]
if not nodes.has_key(category_id):
nodes[category_id] = []
nodes[category_id].append(node)
for category in db.query("SELECT * FROM `category`"):
category["nodes"] = nodes[category["id"]]
categories.append(category)
return categories
def count_table(self, conditions=None):
sql = 'SELECT count(*) as rows FROM ' + self._tb
where = ''
if conditions is not None:
where = dict2where(conditions)
sql += ' WHERE ' + where
return db.query(sql)[0].rows
def findTodoTask(self):
statuss = [taskStatus.WAITING,taskStatus.UPLOADING]
objs = db.query("select headId from uploadTask where status in $statuss",vars={"statuss":statuss}).list()
headIds=[]
for obj in objs:
headIds.append(obj.headId)
return headIds
def recent(self):
post_query = db.query(
"""SELECT p.id, p.title, p.time, p.views, p.comments, p.last_update,
n.id as node_id, n.name as node_slug, n.display_name as node_name,
u.id as user_id, u.name as user_name, u.avatar as user_avatar,
l.id as last_user_id, l.name as last_user_name
FROM `post` as p
LEFT JOIN `node` as n ON ( p.node_id = n.id )
LEFT JOIN `user` as u ON ( p.user_id = u.id )
LEFT JOIN `user` as l on ( p.last_user_id = l.id )
ORDER BY last_update DESC
LIMIT 20"""
)
posts = []
for post in post_query:
post["node"] = Storage({"id": post["node_id"], "slug": post["node_slug"], "name": post["node_name"]})
post["user"] = Storage({"id": post["user_id"], "name": post["user_name"], "avatar": post["user_avatar"]})
post["last_user"] = Storage({"id": post["last_user_id"], "name": post["last_user_name"]})
for d in [
"node_id",
"node_slug",
"node_name",
"user_id",
"user_name",
"user_avatar",
"last_user_id",
"last_user_name",
]:
del post[d]
posts.append(post)
return posts
def count_table(self, conditions = None):
sql = 'SELECT count(*) as rows FROM ' + self._tb
where = ''
if conditions is not None:
where = dict2where(conditions)
sql += ' WHERE ' + where
return db.query(sql)[0].rows
def findByPage(self, page):
pageSize = page.get("pageSize", constants.pageSize)
sql = " FROM imgLine JOIN ocrResult on imgLine.lineId=ocrResult.lineId where 1=1"
order = None
cond = page.get("cond")
if cond:
if cond.get("userNum"):
sql = sql + " and imgLine.userNum = $userNum "
if cond.get("isChecked"):
sql = sql + " and imgLine.isChecked = $isChecked "
if cond.get("headNum"):
sql = sql + " and imgLine.headNum like '%' || $headNum || '%' "
if cond.get("order"):
order = cond.get("order")
print "where: " + str(sql)
print "cond: " + str(cond)
count = db.query('select count(*) as count ' + sql, vars=cond).list()[0].count/2
offset = pageSize * (page.get("pageNum", 1) - 1)
pRet = db.query('select * ' + sql + " and ocrType='PROGRAM'" + " limit " + str(offset) + "," + str(pageSize), vars=cond).list()
cRet = db.query('select * ' + sql + " and ocrType='CHECK'" + " limit " + str(offset) + "," + str(pageSize), vars=cond).list()
cRetDict = {}
for ocr_result in cRet:
cRetDict[ocr_result['lineId']] = ocr_result;
for ocr_result in pRet:
cR = cRetDict[ocr_result['lineId']]
for key, val in ocr_result.items():
if key[-1] == 'R' and val == '0':
ocr_result[key[:-1] + "C"] = 'label label-warning'
for key, val in cR.items():
if key[-1] == 'R' and val == '0':
ocr_result[key[:-1]] = cR[key[:-1]]
ocr_result[key[:-1] + "C"] = 'label label-danger'
page["pageCount"] = (count +pageSize - 1) / pageSize
page["data"] = pRet
return page
def uploading2FailuerByTimeout(self):
#24*60=1440
db.query("update imgHead set status='FAILURE', errorMsg='超时，任务终止' where status='UPLOADING' and ((uploadSize<1024 and (julianday('now','localtime')-julianday(uploadTime))*1440>1) or (julianday('now','localtime')-julianday(uploadTime))*1440>20)")
def addUploadSize(self, headId, uploadSize):
if headId:
db.query("update imgHead set uploadSize = uploadSize +"+str(uploadSize)+" where headId = " + str(headId))
def last_insert_id(self):
return db.query('select last_insert_id() as id')[0].id
def getHeadSize(self, headId):
for o in db.query("select sum(imgSize) as headSize from imgLine where headId=$headId", vars={"headId":headId}):
return int(o.headSize) if o.headSize else 0
return 0
def query(self, sql):
db.query(sql)
return True
def query_result(self, sql):
return db.query(sql)
def getImgQty(self, headId):
for o in db.query("select count(*) as imgQty from imgLine where headId=$headId", vars={"headId":headId}):
return int(o.imgQty) if o.imgQty else 0
return 0
def getByHeadId(self, headId):
for uploadTask in db.query("select * from uploadTask where headId=$headId", vars={"headId":headId}).list():
return uploadTask
def getMaxOrd(self, cond):
for o in db.query("select max(headOrd) as headOrd from imgHead where userNum=$userNum and belong = $belong and status in ('TODO','WAIT')", vars=cond):
return int(o.headOrd) if o.headOrd else 0
return 0
def last_insert_id(self):
return db.query('select last_insert_id() as id')[0].id
def addUploadSize(self, lineId,uploadSize):
if lineId:
db.query("update imgLine set uploadSize = uploadSize +"+str(uploadSize)+" where lineId = " + str(lineId))
db.query("update imgHead set uploadSize = (select sum(l2.uploadSize) from imgLine l1, imgLine l2 where l1.headId=l2.headId and l1.lineId="+ str(lineId)+") where headId = (select headId from imgLine where lineId=" + str(lineId)+")")
def getById(self, uploadTaskId):
for uploadTask in db.query("select * from uploadTask where uploadTaskId=$uploadTaskId", vars={"uploadTaskId":uploadTaskId}).list():
return uploadTask
def query_result(self, sql):
return db.query(sql)
def findAll(self):
return db.query("SELECT uploadTask.*,imgHead.headNum,imgHead.imgQty,imgHead.headSize,imgHead.belong FROM uploadTask JOIN imgHead on uploadTask.headId = imgHead.headId order by uploadTask.status DESC").list()
def query(self, sql):
db.query(sql)
return True
def uploadFail(self, headId, errorMsg):
if headId:
db.query("update imgHead set status='FAILURE', errorMsg='"+str(errorMsg)+"' where headId = " + str(headId))
def getMaxOrd(self, headId):
for o in db.query("select max(imgOrd) as imgOrd from imgLine where headId=$headId", vars={"headId":headId}):
return int(o.imgOrd) if o.imgOrd else 0
return 0
