def query_obj_by_date2(userid, searchdate, limit):
sql = (
"select date, category, branding, notes, amount, memo from expenses"
+ " where userid='"
+ userid
+ "' and left(date, 10) like'%"
+ searchdate
+ "%' order by date desc limit "
+ str(limit)
)
# print sql
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
cursor.close()
objs = []
for o in out:
expense = Expense()
expense.init_with_mysql(userid, amount=o[4], category=o[1], branding=o[2], date=o[0], notes=o[3], memo=o[5])
objs.append(expense)
return objs
def get_expense_msg(userid, category):
sql = "select count(1), avg(amount) from expenses " + \
"where date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) " +\
"and userid='" + userid + "' and category='" + category + "'"
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
user_count = out[0][0]
user_amount = out[0][1]
sql = "select count(1)/count(distinct userid), avg(amount) from expenses " + \
"where userid not in ('obF30jhXJFKf9ak6_KlhF84WhmKc', 'obF30jlR2x9xmh6a5pMF-ZIYtzQY') and " + \
"date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) and category='" + category + "'"
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
avg_count = out[0][0]
avg_amount = out[0][1]
cursor.close()
return (user_count, user_amount, avg_count, avg_amount)
def get_total_amount(db_cursor, userid, from_date_str, end_date_str):
sql = "select sum(amount) from expenses where userid=\"%s\" and date>='%s' and date<='%s'" \
% (userid, from_date_str, end_date_str)
log.info("SQL: get_total_amount: " + sql)
print sql
db_cursor.execute(sql)
out = db_cursor.fetchall()
if out[0][0] is None:
return config.AMOUNT_NONE
else:
return float(out[0][0])
def get_total_amount(db_cursor, userid, from_date_str, end_date_str):
sql = "select sum(amount) from expenses where userid=\"%s\" and date>='%s' and date<='%s'" \
% (userid, from_date_str, end_date_str)
log.info("SQL: get_total_amount: " + sql)
print sql
db_cursor.execute(sql)
out = db_cursor.fetchall()
if out[0][0] is None:
return config.AMOUNT_NONE
else:
return float(out[0][0])
def query_obj_by_date(userid, fromdate, enddate):
sql = "select expenseid, category, date, amount, notes, memo from expenses" + \
" where userid='" + userid +"' and date >='" +\
fromdate + "' and date<='" + enddate + "' order by date desc"
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
cursor.close()
return out
def _response_single_picture_msg(msg, title, description, picurl, url):
"""
Sample:
---
<xml>
<ToUserName><![CDATA[toUser]]></ToUserName>
<FromUserName><![CDATA[fromUser]]></FromUserName>
<CreateTime>12345678</CreateTime>
<MsgType><![CDATA[news]]></MsgType>
<ArticleCount>1</ArticleCount>
<Articles>
<item>
<Title><![CDATA[title1]]></Title>
<Description><![CDATA[description1]]></Description>
<PicUrl><![CDATA[picurl]]></PicUrl>
<Url><![CDATA[url]]></Url>
</item>
</Articles>
<FuncFlag>1</FuncFlag>
</xml>
"""
msg["MsgType"] = "news"
FromUserName = msg["ToUserName"]
msg["ToUserName"] = msg["FromUserName"]
msg["FromUserName"] = FromUserName
xml = '<xml>'
for k in ['ToUserName', 'FromUserName', 'CreateTime', 'MsgType']:
xml += '<%s>%s</%s>' % (k, _cdata(msg[k]), k)
# add article
s = u"""%s
<ArticleCount>1</ArticleCount>
<Articles>
<item>
<Title>%s</Title>
<Description>%s</Description>
<PicUrl>%s</PicUrl>
<Url>%s</Url>
</item>
</Articles>
</xml>
""" % (xml, title, description, picurl, url)
log.info(s.encode('utf-8', 'ignore'))
return s
def _response_single_picture_msg(msg, title, description, picurl, url):
"""
Sample:
---
<xml>
<ToUserName><![CDATA[toUser]]></ToUserName>
<FromUserName><![CDATA[fromUser]]></FromUserName>
<CreateTime>12345678</CreateTime>
<MsgType><![CDATA[news]]></MsgType>
<ArticleCount>1</ArticleCount>
<Articles>
<item>
<Title><![CDATA[title1]]></Title>
<Description><![CDATA[description1]]></Description>
<PicUrl><![CDATA[picurl]]></PicUrl>
<Url><![CDATA[url]]></Url>
</item>
</Articles>
<FuncFlag>1</FuncFlag>
</xml>
"""
msg["MsgType"] = "news"
FromUserName = msg["ToUserName"]
msg["ToUserName"] = msg["FromUserName"]
msg["FromUserName"] = FromUserName
xml = '<xml>'
for k in ['ToUserName', 'FromUserName', 'CreateTime', 'MsgType']:
xml += '<%s>%s</%s>' % (k, _cdata(msg[k]), k)
# add article
s = u"""%s
<ArticleCount>1</ArticleCount>
<Articles>
<item>
<Title>%s</Title>
<Description>%s</Description>
<PicUrl>%s</PicUrl>
<Url>%s</Url>
</item>
</Articles>
</xml>
""" % (xml, title, description, picurl, url)
log.info(s.encode('utf-8', 'ignore'))
return s
def insert_expense(expense):
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
sql = Expense.generate_insert_sql(expense)
log.info("SQL:" + sql)
#print sql
try:
cursor.execute(sql)
conn.commit()
except MySQLdb.Error:
log.error("SQL fail: %s" % sql)
conn.rollback()
cursor.close()
# TODO: defensive check
return True
def insert_expense(expense):
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
sql = Expense.generate_insert_sql(expense)
log.info("SQL:" + sql)
#print sql
try:
cursor.execute(sql)
conn.commit()
except MySQLdb.Error:
log.error("SQL fail: %s" % sql)
conn.rollback()
cursor.close()
# TODO: defensive check
return True
def query_obj_by_date2(userid, searchdate, limit):
sql = "select date, category, branding, notes, amount, memo from expenses" + \
" where userid='" + userid +"' and left(date, 10) like'%" +\
searchdate + "%' order by date desc limit " + str(limit)
#print sql
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
cursor.close()
objs = []
for o in out:
expense = Expense()
expense.init_with_mysql(userid, amount=o[4], category=o[1], \
branding = o[2], date=o[0], notes=o[3], memo=o[5])
objs.append(expense)
return objs
def get_expense_msg(userid, category):
sql = (
"select count(1), avg(amount) from expenses "
+ "where date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) "
+ "and userid='"
+ userid
+ "' and category='"
+ category
+ "'"
)
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
user_count = out[0][0]
user_amount = out[0][1]
sql = (
"select count(1)/count(distinct userid), avg(amount) from expenses "
+ "where userid not in ('obF30jhXJFKf9ak6_KlhF84WhmKc', 'obF30jlR2x9xmh6a5pMF-ZIYtzQY') and "
+ "date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) and category='"
+ category
+ "'"
)
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
avg_count = out[0][0]
avg_amount = out[0][1]
cursor.close()
return (user_count, user_amount, avg_count, avg_amount)
def query_obj_by_date(userid, fromdate, enddate):
sql = (
"select expenseid, category, date, amount, notes, memo from expenses"
+ " where userid='"
+ userid
+ "' and date >='"
+ fromdate
+ "' and date<='"
+ enddate
+ "' order by date desc"
)
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
cursor = conn.cursor()
log.info(sql)
cursor.execute(sql)
out = cursor.fetchall()
cursor.close()
return out
def delete_obj_by_id(userid, expenseid):
if expenseid != "":
sql = "delete from expenses where userid='" + userid + "' and expenseid=" + expenseid
else:
sql = "delete from expenses where userid='" + userid + "'"
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \
config.DB_NAME, charset='utf8mb4', port=config.DB_PORT)
log.info(sql)
cursor = conn.cursor()
# TODO: report error
try:
cursor.execute(sql)
conn.commit()
except MySQLdb.Error:
log.error("ERROR delete mysql fail")
conn.rollback()
cursor.close()
return True
def delete_obj_by_id(userid, expenseid):
if expenseid != "":
sql = "delete from expenses where userid='" + userid + "' and expenseid=" + expenseid
else:
sql = "delete from expenses where userid='" + userid + "'"
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
log.info(sql)
cursor = conn.cursor()
# TODO: report error
try:
cursor.execute(sql)
conn.commit()
except MySQLdb.Error:
log.error("ERROR delete mysql fail")
conn.rollback()
cursor.close()
return True
def word_split(sentence, shall_print=0):
# python word split is incorrect for float, for example,"16.5 中午吃饭",
# will be splited as "16 5 中午吃饭", instead of "16.5 中午 吃饭"
SPECIAL_CHARACTER_FOR_FLOAT='a'
sentence=sentence.replace('。', SPECIAL_CHARACTER_FOR_FLOAT)
sentence=sentence.replace('.', SPECIAL_CHARACTER_FOR_FLOAT)
algor = mmseg.Algorithm(sentence)
token_list = []
for tok in algor:
if tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '0').isdigit():
token_list.append((tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '.'), \
tok.start, tok.end))
else:
token_list.append((tok.text, tok.start, tok.end))
# temporarily print
for text, start, end in token_list:
if shall_print == 1:
log.info("%s, %d, %d" % (text, start, end))
else:
if shall_print == 2:
print "%s, %d, %d" % (text, start, end)
return token_list
def word_split(sentence, shall_print=0):
# python word split is incorrect for float, for example,"16.5 中午吃饭",
# will be splited as "16 5 中午吃饭", instead of "16.5 中午 吃饭"
SPECIAL_CHARACTER_FOR_FLOAT = 'a'
sentence = sentence.replace('。', SPECIAL_CHARACTER_FOR_FLOAT)
sentence = sentence.replace('.', SPECIAL_CHARACTER_FOR_FLOAT)
algor = mmseg.Algorithm(sentence)
token_list = []
for tok in algor:
if tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '0').isdigit():
token_list.append((tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '.'), \
tok.start, tok.end))
else:
token_list.append((tok.text, tok.start, tok.end))
# temporarily print
for text, start, end in token_list:
if shall_print == 1:
log.info("%s, %d, %d" % (text, start, end))
else:
if shall_print == 2:
print "%s, %d, %d" % (text, start, end)
return token_list
