def setRemWelCalToAgent(agentId, rem, allocatingStatus):
qry = 'UPDATE welcome_calls SET agent_id=' + str(
agentId) + ',status=' + str(allocatingStatus) + ' WHERE '
qry = qry + getMultipleStatusQryStr()
qry = qry + ' limit ' + str(rem)
print qry
DBUtils.executeUpdateQuery(qry)
return
def newnumber():
dic = ut.get_one_number()
return render_template("form.html",
name=dic["name"],
age=dic["age"],
gender=dic["gender"],
phone=dic["phone"])
def test():
conn = sqlite3.connect(DATABASE)
cur = conn.cursor()
cur.execute("SELECT * FROM 'EmployeeList'")
employeeList = cur.fetchall()
conn.close()
employeeList = DBUtils.convertToDictionary(cur, employeeList)
return render_template('EmployeeData2.html', employeeList=employeeList)
def insertInvertory():
env = request.args.get('env')
code = request.args.get('code')
datenow = time.strftime("%Y-%m-%d")
db = DBUtils(env)
selectsql = "SELECT a.id propertyId,c.id roomTypeId,c.code RoomType FROM info.property AS a,info.room_class AS b,info.room_type AS c WHERE a.code ='" + code + "' AND b.property_id =a.id AND c.room_class_id =b.id"
result = db.dbSelect(selectsql)
for i in range(len(result)):
property_id = str(result[i]['propertyId'])
delsql = "DELETE FROM inv.`property_inventory_detail` WHERE property_id ='" + property_id + "' AND effective_date >='" + str(
datenow) + "'"
dbs = DBUtils(env)
resutls = dbs.dbExcute(delsql)
print("the resutls is:", resutls)
print(delsql)
for i in range(len(result)):
property_id = str(result[i]['propertyId'])
relation_id = str(result[i]['roomTypeId'])
insertsql = "INSERT INTO inv.`property_inventory_detail`(property_id,relation_type,relation_id,effective_date,original,consume,STATUS,create_time,update_time,deducted,out_order,non_deducted)VALUES(" + property_id + ",4," + relation_id + ",DATE_ADD('" + str(
datenow
) + "',INTERVAL 0 DAY),100,0,1,NOW(),NOW(),0,0,0),(" + property_id + ",4," + relation_id + ",DATE_ADD('" + str(
datenow
) + "',INTERVAL 1 DAY),100,0,1,NOW(),NOW(),0,0,0),(" + property_id + ",4," + relation_id + ",DATE_ADD('" + str(
datenow) + "',INTERVAL 90 DAY),100,0,1,NOW(),NOW(),0,0,0);"
dbs = DBUtils(env)
resutls = dbs.dbExcute(insertsql)
print(resutls)
print(insertsql)
return jsonify("<p color='green'>{status:200,msg:it's success!!!}</p>")
def sendmessage():
number = request.args.get('phone')
body = request.args.get('body')
print(number)
print(body)
if (number == None):
dic = ut.get_one_number()
if (dic != False):
return render_template("form.html",
name=dic["name"],
age=dic["age"],
gender=dic["gender"],
phone=dic["phone"])
else:
return "error"
if (body == None):
dic = ut.get_one_number()
if (dic != False):
return render_template("form.html",
name=dic["name"],
age=dic["age"],
gender=dic["gender"],
phone=dic["phone"])
else:
return "error"
number = sms.translatePhone(number)
print(number)
m = Message("name", number, body)
result = sms.send_message(m)
if (result == False):
dic = ut.get_one_number()
return render_template("form.html",
name=dic["name"],
age=dic["age"],
gender=dic["gender"],
phone=dic["phone"],
status="Message could not send")
else:
dic = ut.get_one_number()
return render_template("form.html",
name=dic["name"],
age=dic["age"],
gender=dic["gender"],
phone=dic["phone"],
status="Message sent sucessfully")
def getAgents():
role = ConfigReader.getValue('db', 'role')
# bima_customer, bima_user_role_permission.
filterAgentsQuery = 'SELECT user_id, role_id FROM bima_user_role_permission WHERE user_id > 0 and role_id = ' + role
# print filterAgentsQuery
validAgents = DBUtils.executeSelectQuery(filterAgentsQuery)
return validAgents
def testSelectQuery():
role = ConfigReader.getValue('db', 'role')
filterAgentsQuery = 'SELECT user_id, role_id FROM bima_user_role_permission WHERE user_id < 0 and role_id = ' + role + ' limit 10'
res = DBUtils.executeSelectQuery(filterAgentsQuery)
if res is None:
print 'Result is None for ' + filterAgentsQuery
if (len(res) == 0):
print 'Zero results found for ' + filterAgentsQuery
print 'res: '
for row in res:
print 'user_id - ' + str(row[0]) + ', role_id - ' + str(row[1])
def insertWelcomeCalls(count):
total = 0
user_id = 3210
agent_id = 0
status = 1
partner_country_id = 111
created_by = 1010
created_date = 'NOW()'
policy_id = 3333
valStr = ''
while (total < count):
user_id = user_id + total
valStr = valStr + '(' + str(user_id) + ',' + str(agent_id) + ',' + str(
status) + ',' + str(partner_country_id) + ',' + str(
created_by) + ',' + str(created_date) + ',' + str(
policy_id) + '),'
total = total + 1
valStr = valStr[:-1]
qry = 'INSERT INTO welcome_calls (user_id,agent_id,status,partner_country_id,created_by,created_date,policy_id) values ' + valStr
print 'insertWelcomeCalls qry: ' + qry
DBUtils.executeInsertQuery(qry)
return
def initialize(cycle_id):
cycle_id = str(cycle_id)
sql_query = "select distinct o.observation_no, p.proposal_id, g.file_path, g.lta_file, g.ltb_file, g.lta_gsb_file from " \
"gmrt.proposal p inner join das.observation o on p.proposal_id = o.proj_code " \
"inner join das.scangroup g on g.observation_no = o.observation_no " \
"inner join das.scans s on s.scangroup_id = g.scangroup_id " \
"where cycle_id ='"+cycle_id+"' " \
"and s.sky_freq1=s.sky_freq2 and s.sky_freq1 < 900000000 " \
"and s.chan_width >= 62500 " \
"and o.proj_code not like '16_279' " \
"and o.proj_code not like '17_072' " \
"and o.proj_code not like '18_031' " \
"and o.proj_code not like '19_043' " \
"and o.proj_code not like '20_083' " \
"and o.proj_code not like '21_057';"
dbutils = DBUtils.DBUtils()
data = dbutils.select_query(sql_query)
gadpudata = {}
for each_row in data:
obs_no = each_row[0]
proposal_id = each_row[0]
file_path = each_row[2]
lta = each_row[3]
ltb = each_row[4]
gsb = each_row[5]
# if lta or ltb or gsb:
# files = lta, ltb, gsb
if lta and ltb and gsb:
files = gsb
elif lta and ltb and not gsb:
files = lta, ltb
elif not lta and not ltb and gsb:
files = gsb
elif lta and not ltb and not gsb:
files = lta
elif lta and not ltb and gsb:
files = lta
gadpudata[int(obs_no)] = {
"proposal_id": proposal_id,
"files": files,
"file_path": file_path
}
return gadpudata
def createConnection(self,signal=True):
if signal==True:
POOL = DBUtils.PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=6, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=5, # 链接池中最多闲置的链接，0和None不限制
maxshared=3, # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='49.232.6.143',
port=3306,
user='******',
password='******',
database='sm_enterprise',
charset='utf8'
)
return self.POOL.createConnection()
def PCardInfo(user, token):
Submit_url = "https://reportedh5.17wanxiao.com/sass/api/epmpics"
uid = uids[random.randint(0, (len(uids) - 1))]
temp = 36+random.randint(2, 8)*0.1
customerid = '1911'
AttrInfo = DBUtils.GetAttributionInfo(user['AttributionId'])
response = {}
if AttrInfo != None:
check_json = {
"businessType": "epmpics",
"method": "submitUpInfo",
"jsonData": {
"deptStr": {
"deptid": AttrInfo['classId'],
"text": AttrInfo['classDescription']
},
"areaStr": appsettings.GetSchoolLocations(),
"reportdate": round(time.time()*1000),
"customerid": customerid,
"deptid": AttrInfo['classId'],
"source": "app",
"templateid": "pneumonia",
"stuNo": user["StuId"],
"username": user["Name"],
"phonenum": "",
"userid": uid,
"updatainfo": [
{
"propertyname": "isAlreadyInSchool",
"value": "云南昆明"
},
{
"propertyname": "temperature",
"value": temp
},
{
"propertyname": "jtdz",
"value": user["TeachName"]
},
{
"propertyname": "Nativeplace",
"value": "未分配"
},
{
"propertyname": "ownPhone",
"value": user["PhoneNumber"]
},
{
"propertyname": "emergencyContact",
"value": user["EmergencyContact"]
},
{
"propertyname": "mergencyPeoplePhone",
"value": user["MergencyPeoplePhone"]
},
{
"propertyname": "symptom",
"value": "无症状"
},
{
"propertyname": "isTouch",
"value": "否"
},
{
"propertyname": "xinqing",
"value": "否"
},
{
"propertyname": "isGoWarningAdress",
"value": "否"
},
{
"propertyname": "cxjh",
"value": "否"
},
{
"propertyname": "isFFHasSymptom",
"value": "否"
},
{
"propertyname": "isleaveaddress",
"value": "否"
},
{
"propertyname": "person",
"value": ""
},
{
"propertyname": "langtineadress",
"value": user["Langtineadress"]
},
{
"propertyname": "assistRemark",
"value": "无"
},
{
"propertyname": "other2",
"value": ""
}
],
"gpsType": 1,
"token": token
}
}
res = requests.post(Submit_url, json=check_json)
response = res.json()
if response["code"] == "10000":
print(user["Name"] + "打卡成功！")
DBUtils.WriteLog('打卡成功', DBUtils.LogStable, (user['Name'] + '-' + user["StuId"]), 'System-PCardInfo',
str(response))
iEmail.PostEmail('完美校园打卡', '成功', user, '')
else:
print(user["Name"] + "打卡失败！")
print(response)
DBUtils.WriteLog('打卡失败', DBUtils.LogWarning, (user['Name'] + '-' + user["StuId"]), 'System-PCardInfo',
str(response))
iEmail.PostEmail('完美校园打卡', '失败', user, response['data'])
else:
print('归属信息错误！')
response = {'code': '404', 'data': '归属信息错误！'}
DBUtils.WriteLog('归属信息错误', DBUtils.LogWarning, (user['Name'] + '-' + user["StuId"]), 'System-PCardInfo',
str(response))
iEmail.PostEmail('完美校园打卡', '失败', user, response['data'])
return response
__author__ = 'Ciddhi'
from DBUtils import *
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
dbObject.dbQuery("CREATE TABLE testing_tradesheet_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
" entry_qty int,"
" exit_date date,"
" exit_time time,"
" exit_price float"
" )")
resultIndividuals = dbObject.dbQuery(
"SELECT count(*), individual_id FROM old_tradesheet_data_table WHERE entry_date>='2012-04-09'"
" AND entry_date<='2012-07-09' GROUP BY individual_id ORDER BY COUNT(*) DESC"
)
countNum = 1
for count, individualId in resultIndividuals:
if count >= 100:
print(
str(countNum) + " - Inserting for individual : " +
str(individualId))
dbObject.dbQuery(
else:
print('归属信息错误！')
response = {'code': '404', 'data': '归属信息错误！'}
DBUtils.WriteLog('归属信息错误', DBUtils.LogWarning, (user['Name'] + '-' + user["StuId"]), 'System-PCardInfo',
str(response))
iEmail.PostEmail('完美校园打卡', '失败', user, response['data'])
return response
if __name__ == '__main__':
token = login.start_Login(appsettings.Account, appsettings.Password,appsettings.IMEI)
UserInfo = login.GetUserInfo(token)
count = 0
count_F = 0
ErrorList = {}
Users = DBUtils.GetDBUser()
for user in Users:
status = PCardInfo(user, token)
count += 1
if status['code'] != '10000':
count_F += 1
name = user['Name']+'- [ '+user['StuId']+' ]'
ErrorList.update({name: status['data']})
if len(Users) == count:
continue
time.sleep(random.randint(125, 200))
continue
if len(Users) == count:
continue
time.sleep(random.randint(30, 45))
def post_proc(fits_file, lta, object):
print(fits_file, lta, object)
dbutils = DBUtils.DBUtils()
# fits_images_list = ['/GARUDATA/IMAGING20/IMAGES/20_086_29jun2011_lta/0054-035.SP2B.PBCOR_20_086_29jun2011_lta.FITS']
# lta = '20_086_29jun2011.lta'
# object = '0054-035'
fits_dir = os.path.dirname(fits_file)
fits_table = fits.open(fits_file)
fits_header = fits_table[0].header
data_keys = {}
summary_file = glob.glob(fits_dir + '/spam_' + object + '*.summary')
rms = "NA"
for each_summary in summary_file:
if 'DONE' in open(each_summary).read():
lines = open(each_summary).readlines()
rms = lines[-1].split(' ')[-5]
else:
if rms == "NA":
log_file = each_summary.replace('summary', 'log')
lines = open(log_file).readlines()
rms = lines[-2].split(' ')[0]
sql = "select distinct file_path, s.scangroup_id from das.scangroup sg inner join das.scans s on s.scangroup_id = sg.scangroup_id " \
"where lta_file = '" + lta + "' OR lta_gsb_file = '"+ lta +"' AND source like '" + object + "'"
file_path_data = dbutils.select_scangroup_query(sql)
if file_path_data:
observation_file = glob.glob(file_path_data[0]+'/*.obslog')[0]
observation_number = str(observation_file.split('.')[0])
scangroup_id = str(file_path_data[1])
sql_sg = "select ant_mask, band_mask, calcode, chan_width, corr_version, g.observation_no, " \
"date_obs, ddec, dec_2000, dec_date, dra, lsr_vel1, lsr_vel2, lta_time, " \
"net_sign1, net_sign2, net_sign3, net_sign4, num_chans, num_pols, onsrc_time, " \
"proj_code, qual, ra_2000, ra_date, rest_freq1, rest_freq2, sky_freq1, " \
"sky_freq2, source, sta_time from das.scangroup g inner join " \
"das.scans s on s.scangroup_id = g.scangroup_id " \
"where s.scangroup_id = " + scangroup_id + " AND source like '" + object + "'"
scangroup_data = dbutils.select_scangroup_query(sql_sg)
if scangroup_data:
data_keys = {
"ANTMASK": scangroup_data[0],
"BANDMASK": scangroup_data[1],
"CALCODE": scangroup_data[2],
"CHANWIDT": scangroup_data[3],
"CORRVERS": scangroup_data[4],
"OBSNUM": scangroup_data[5],
"DATEOBS": str(scangroup_data[6]),
"DDEC": scangroup_data[7],
"DEC2000": scangroup_data[8],
"DECDATE": scangroup_data[9],
"DRA": scangroup_data[10],
"LSRVEL1": scangroup_data[11],
"LSRVEL2": scangroup_data[12],
"LTATIME": scangroup_data[13],
"NETSIGN1": scangroup_data[14],
"NETSIGN2": scangroup_data[15],
"NETSIGN3": scangroup_data[16],
"NETSIGN4": scangroup_data[17],
"NUMCHANS": scangroup_data[18],
"NUMPOLS": scangroup_data[19],
"ONSRCTIM": scangroup_data[20],
"PROJCODE": scangroup_data[21],
"QUAL": scangroup_data[22],
"RA2000": scangroup_data[23],
"RADATE": scangroup_data[24],
"RESTFRE1": scangroup_data[25],
"RESTFRE2": scangroup_data[26],
"SKYFREQ1": scangroup_data[27],
"SKYFREQ2": scangroup_data[28],
"STATIME": scangroup_data[30],
"RMS": float(rms)
}
filename = fits_file
hdulist = fits.open(filename, mode='update')
header = hdulist[0].header
try:
histroy = str(fits_header["HISTORY"]).strip().split(' ')
nh = [x for x in histroy if x]
in_line_his = [''.join(i) for i in histroy]
in_line_his = list(filter(None, in_line_his))
data_keys["BMAJ"] = float(in_line_his[in_line_his.index('BMAJ=')+1])
data_keys["BMIN"] = float(in_line_his[in_line_his.index('BMIN=')+1])
data_keys["BPA"] = float(in_line_his[in_line_his.index('BPA=')+1].split('\n')[0])
except Exception as ex:
print(ex)
for key, value in data_keys.iteritems():
print key, value
__author__ = 'Ciddhi'
from DBUtils import *
from datetime import timedelta, datetime
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
date = datetime(2012, 4, 9).date()
periodEndDate = datetime(2012, 7, 9).date()
startTime = timedelta(hours=9, minutes=15)
endTime = timedelta(hours=10, minutes=30)
dayEndTime = timedelta(hours=15, minutes=30)
lastCheckedTime = timedelta(hours=9, minutes=15)
dbObject.resetAssetAllocation()
done = False
while (not done):
resultTradingDay = dbObject.checkTradingDay(date)
for checkTradingDay, dummy0 in resultTradingDay:
if checkTradingDay==1:
print('Getting trades from superset')
resultTrades = dbObject.getTradesOrdered(date, startTime, endTime)
print('For all trades received')
for tradeId, individualId, tradeType, entryDate, entryTime, entryPrice, entryQty, exitDate, exitTime, exitPrice in resultTrades:
print('Fetching trades that are to exit')
resultTradesExit = dbObject.getTradesExit(date, lastCheckedTime, entryTime)
for id, qty, price in resultTradesExit:
# -*- coding:utf-8 -*-
import DBUtils
import ChineseCaiPiao
import SinaAicai
conn = DBUtils.connect_mysql()
cur = conn.cursor()
SinaAicai.get_data(cur=cur,conn=conn)
# chinese caipiao net
# ChineseCaiPiao.get_chinese_caipiao(cur=cur,conn=conn)
# DBUtils.create_table(cur=cur, table_name=TABLE_NAME)
# soup = BeautifulSoupUtils.get_beautiful_soup(BASE_URL)
# selects = soup.find_all('select')
# dates = []
# for select in selects:
# datas = select.find_all('option')
# for data in datas:
# print data.getText()
# dates.append(data.getText())
#
# print dates
# for date in dates:
# details = BeautifulSoupUtils.get_beautiful_soup(DETAILS_URL + date)
__author__ = 'Ciddhi'
from DBUtils import *
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS latest_individual_table"
" ("
" individual_id int"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS training_mtm_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" date date,"
" time time,"
" mtm float"
" )")
dbObject.dbQuery(
"CREATE TABLE IF NOT EXISTS training_tradesheet_data_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
for pl, dummy in resultLongNetPL:
netPL = netPL + pl
for pl, dummy in resultShortNetPL:
netPL = netPL + pl
for trades, dummy in resultLongTrades:
totalTrades = totalTrades + trades
for trades, dummy in resultShortTrades:
totalTrades = totalTrades + trades
performace = netPL/totalTrades
print(totalTrades)
print(performace)
return performace
if __name__ == "__main__":
dbObject = DBUtils()
performanceObject = PerformanceMeasures()
dbObject=DBUtils()
dbObject.dbConnect()
mtm=[]
date = datetime(2012, 3, 1).date()
periodEndDate=datetime(2012,3,31).date()
performanceObject.CalculateTradesheetPerformanceMeasures(date, periodEndDate, dbObject)
#performanceObject.CalculateReferenceTradesheetPerformanceMeasures(date, periodEndDate, dbObject)
walkforwardStartDate = date
#date = datetime.datetime(2003,8,1,12,4,5)
for i in range(0,31):
#print(walkforwardStartDate.isoformat())
result=dbObject.dbQuery("SELECT sum(mtm), 1 from mtm_table WHERE date = '"+str(walkforwardStartDate)+"'")
for sum, dummy in result:
__author__ = 'Ciddhi'
from DBUtils import *
import GlobalVariables as gv
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS old_tradesheet_data_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
" entry_qty int,"
" exit_date date,"
" exit_time time,"
" exit_price float"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS price_series_table"
" ("
" date date,"
" time time,"
" price float"
" )")
endPrice = p
# mtm calculation
if tradeType==1:
if price and endPrice:
mtm = (endPrice-price) * entryQty
dbObject.insertTrainingMTM(individualId, tradeId, tradeType, entryDate, endTime, mtm)
else:
if price and endPrice:
mtm = (price-endPrice) * entryQty
dbObject.insertTrainingMTM(individualId, tradeId, tradeType, entryDate, endTime, mtm)
if __name__ == "__main__":
mtmObject = MTM()
aggregationUnit = 1
startDate = 20120409
startTime = timedelta(hours=10, minutes=30)
endDate = 20120409
endTime = timedelta(hours=11, minutes=30)
dbObject = DBUtils()
dbObject.dbConnect()
# Reset mtm_table
#queryReset = "DELETE FROM mtm_table"
#dbObject.dbQuery(queryReset)
#mtmObject.calculateMTM(2, aggregationUnit)
mtmObject.calculateMTM(1,aggregationUnit, startDate, startTime, endDate, endTime, dbObject)
#dbObject.insertMTM(1, 11296331, 0, startDate, startTime, 0)
dbObject.dbClose()
dbObject.insertMTM(individualId, tradeId, tradeType,
entryDate, endTime, mtm)
else:
if price and endPrice:
mtm = (price - endPrice) * entryQty
negMtm += mtm
negQty += entryQty
dbObject.insertMTM(individualId, tradeId, tradeType,
entryDate, endTime, mtm)
return (posMtm, posQty, negMtm, negQty)
if __name__ == "__main__":
mtmObject = MTM()
aggregationUnit = 1
startDate = 20120409
startTime = timedelta(hours=10, minutes=30)
endDate = 20120409
endTime = timedelta(hours=11, minutes=30)
dbObject = DBUtils()
dbObject.dbConnect()
# Reset mtm_table
#queryReset = "DELETE FROM mtm_table"
#dbObject.dbQuery(queryReset)
#mtmObject.calculateMTM(2, aggregationUnit)
mtmObject.calculateMTM(aggregationUnit, startDate, startTime, endDate,
endTime, dbObject)
#dbObject.insertMTM(1, 11296331, 0, startDate, startTime, 0)
dbObject.dbClose()
],
[
negMtm / negQty * (negQty - 1), negMtm,
negMtm / negQty * (negQty + 1)
],
[
negMtm, negMtm / negQty * (negQty + 1),
negMtm / negQty * (negQty + 2)
]])
rm = gv.alpha * posRm + (1 - gv.alpha) * negRm
return rm
if __name__ == "__main__":
dbObject = DBUtils()
rewardMatrixObject = RewardMatrix()
startDate = 20120409
startTime = timedelta(hours=9, minutes=15)
endDate = 20120409
endTime = timedelta(hours=10, minutes=30)
dbObject.dbConnect()
resultIndividuals = dbObject.getIndividuals(startDate, startTime, endDate,
endTime)
for individualId, dummy in resultIndividuals:
print(individualId)
rewardMatrixObject.computeRM(individualId, startDate, startTime,
endDate, endTime, dbObject)
def getMaxValWelCal(max):
# Get valid welcome_calls, where status=0 or status=4. [NEW, INCOMPLETE]
validWelcomeCallsQuery = makeValidWelCalQry(max)
valWelCal = DBUtils.executeSelectQuery(validWelcomeCallsQuery)
return valWelCal
def createQLearningTables(self):
global alpha
global gamma
global beta
global individualFactor
global zeroRange
global greedyLevel
dbObject = DBUtils()
dbObject.dbConnect()
variableString = "a" + str(alpha).replace('.', '_') + "g" + str(gamma).replace('.', '_') + "b" + str(beta).replace('.', '_') + "f" + str(individualFactor).replace('.', '_') + "r" + str(zeroRange).replace('.', '_') + "l" + str(greedyLevel).replace('.', '_')
latestIndividualTable = gv.latestIndividualTableBase + variableString
trainingTradesheetTable = gv.trainingTradesheetTableBase + variableString
trainingAssetTable = gv.trainingAssetTableBase + variableString
dailyAssetTable = gv.dailyAssetTableBase + variableString
newTradesheetTable = gv.newTradesheetTableBase + variableString
assetTable = gv.assetTableBase + variableString
qMatrixTable = gv.qMatrixTableBase + variableString
reallocationTable = gv.reallocationTableBase + variableString
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + latestIndividualTable +
" ("
" individual_id int"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + trainingTradesheetTable +
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
" entry_qty int,"
" exit_date date,"
" exit_time time,"
" exit_price float"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + trainingAssetTable +
" ("
" individual_id int,"
" total_asset decimal(15,4),"
" used_asset decimal(15,4),"
" free_asset decimal(15,4)"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + dailyAssetTable +
"("
"date date,"
"time time,"
"total_asset decimal(15,4)"
")")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + newTradesheetTable +
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
" entry_qty int,"
" exit_date date,"
" exit_time time,"
" exit_price float"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + assetTable +
" ("
" individual_id int,"
" total_asset decimal(15,4),"
" used_asset decimal(15,4),"
" free_asset decimal(15,4)"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + qMatrixTable +
" ("
" individual_id int,"
" row_num int,"
" column_num int,"
" q_value decimal(20,10)"
" )")
dbObject.dbQuery("CREATE TABLE IF NOT EXISTS " + reallocationTable +
" ("
" individual_id int,"
" last_reallocation_date date,"
" last_reallocation_time time,"
" last_state int"
" )")
dbObject.dbClose()
return [variableString, latestIndividualTable, trainingTradesheetTable, trainingAssetTable, qMatrixTable, reallocationTable, assetTable, dailyAssetTable, newTradesheetTable]
__author__ = 'Ciddhi'
from DBUtils import *
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
resultAssets = dbObject.dbQuery(
"SELECT SUM(entry_qty*entry_price), entry_date FROM testing_tradesheet_table GROUP BY entry_date"
)
maxSum = 0
maxDate = None
sumArr = []
for sum, date in resultAssets:
sumArr.append(sum)
if sum > maxSum:
maxSum = sum
maxDate = date
sumArr.sort()
print(maxSum)
print(maxDate)
print(sumArr)
def getRemCountForAgent(agentId, limit, pendStatus):
qry = 'SELECT COUNT(*) FROM welcome_calls WHERE agent_id=' + str(
agentId) + ' AND ' + ' status=' + str(pendStatus)
rem = DBUtils.executeSelectQuery(qry)
# print 'rem='+str(rem)
return (limit - int(rem[0][0]))
def filterFind():
# Get Filters from args
filter1 = request.args.getlist('filter1')
filter2 = request.args.getlist('filter2')
filter3 = request.args.getlist('filter3')
filter4 = request.args.getlist('filter4')
filter5 = request.args.get('filter5', '')
filter6 = request.args.get('filter6', 'and')
if (len(filter1) == 0 and len(filter2) == 0 and len(filter3) == 0
and len(filter4) == 0 and filter5 == ''):
html = render_template('EmployeeFilterResults.html',
employeeList=[],
mapList=[])
return make_response(jsonify({"html": html}))
# Build search string for each filter
comparison_within = ''
comparison_between = ''
if filter6 == "and":
comparison_within = " and "
comparison_between = " and "
else:
comparison_within = " or "
comparison_between = " and "
f1 = "(" + comparison_within.join(
[f"a.RegisteredLicenses like '%{f}%'" for f in filter1]) + ")"
f2 = "(" + comparison_within.join(
[f"a.skill like '%{f}%'" for f in filter2]) + ")"
f3 = "(" + comparison_within.join(
[f"a.skillLevel like '%{f}%'" for f in filter3]) + ")"
f4 = "(" + comparison_within.join(
[f"a.StateProvince like '%{f}%'" for f in filter4]) + ")"
# Combine filters and build sql string
f_strings = []
if len(f1) > 2: f_strings.append(f1)
if len(f2) > 2: f_strings.append(f2)
if len(f3) > 2: f_strings.append(f3)
if len(f4) > 2: f_strings.append(f4)
sql = comparison_between.join(f_strings)
if filter5 != '':
if sql != '':
sql = "(" + sql + ") and"
sql = sql + f" a.LastName like '%{filter5}%'"
#sql = "select * from EmployeeList where " + sql
sql = "select a.*, b.lat as lat2, b.lng as lng2 from EmployeeList as a left join cities as b on b.city = a.City and b.stateName = a.StateProvince where " + sql
print("SQL:", sql)
# Connect to database, query, and print results
conn = sqlite3.connect(DATABASE)
cur = conn.cursor()
cur.execute(sql)
employeeList = cur.fetchall()
employeeList = DBUtils.convertToDictionary(cur, employeeList)
# Provide a second list for just the mapping function
# params = []
# for filter in filter1 + filter2 + filter3 + filter4:
# params.append("%" + filter + "%")
sql = sql + " order by a.StateProvince, a.City"
cur.execute(sql)
employeeList2 = cur.fetchall()
employeeList2 = DBUtils.convertToDictionary(cur, employeeList2)
html = render_template('EmployeeFilterResults.html',
employeeList=employeeList,
mapList=json.dumps(employeeList2))
return make_response(jsonify({"html": html}))
def scoreboard():
scores = DB.GetHighScores()
if scores is None:
scores = []
return render_template('scoreboard.html', scores=scores)
def __init__(self, conn, module, module_name=None):
self.module = module
if module_name is None:
module_name = module.__name__
self.adaptor = Adaptor(conn, DBUtils.get_dbutils(module_name))
self.module_name = module_name
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: db_utils
# Description:
# Author Dongtian
# Date: 2020-01-05
# -------------------------------------------------------------------------------
from Auto.utils.MyConf import cf
import DBUtils
import pymysql
POOL = DBUtils.PersistentDB(
creator=pymysql, # 使用链接数据库的模块
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
closeable=False,
# 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
threadlocal=None, # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
host=str(cf.get("db", "host")),
port=3306,
user='******',
password='******',
database='pooldb',
charset='utf8',
)
def __init__(self, conn, module, module_name=None):
self.module = module
if module_name is None:
module_name = module.__name__
self.adaptor = Adaptor(conn, DBUtils.get_dbutils(module_name))
self.module_name = module_name
__author__ = 'Ciddhi'
from DBUtils import *
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
dbObject.dbQuery("CREATE TABLE asset_daily_allocation_table"
"("
"date date,"
"time time,"
"total_asset decimal(15,4)"
")")
'''
dbObject.dbQuery("CREATE TABLE tradesheet_data_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
" entry_qty int,"
" exit_date date,"
" exit_time time,"
" exit_price float"
" )")
dbObject.dbQuery("CREATE TABLE old_tradesheet_data_table"
" ("
__author__ = 'Ciddhi'
from DBUtils import *
if __name__ == "__main__":
dbObject = DBUtils()
dbObject.dbConnect()
dbObject.dbQuery("CREATE TABLE latest_individual_table"
" ("
" individual_id int"
" )")
dbObject.dbQuery("CREATE TABLE training_mtm_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" date date,"
" time time,"
" mtm float"
" )")
dbObject.dbQuery("CREATE TABLE training_tradesheet_data_table"
" ("
" trade_id int,"
" individual_id int,"
" trade_type int,"
" entry_date date,"
" entry_time time,"
" entry_price float,"
def testSetup():
updateQuery = 'UPDATE welcome_calls SET status=4,agent_id=100 where id=2'
DBUtils.executeUpdateQuery(updateQuery)
