def get_se_estimator_by_run_id(run_id, estimator_id, context):
estimator = Connections.exec_sql('QUANT',
''' SELECT pd.parameter_name, pv.value
FROM %s..param_desc pd, %s..param_value pv
WHERE pv.parameter_id = pd.parameter_id
AND pd.estimator_id = %d
AND pv.run_id = %d
''' % (bq, bq, estimator_id, run_id)
)
return pyData('init', date=[x[0] for x in estimator], value={context:[x[1] for x in estimator]})
def get_perimeter_sec_ids(perimeter_id):
'''
Returns the list of security for a perimeter
'''
ids = Connections.exec_sql("QUANT",
''' SELECT security_id FROM %s..perimeter_domain pd
JOIN %s..domain_security ds on ds.domain_id = pd.domain_id
WHERE pd.perimeter_id = %d
''' % (bq, bq, perimeter_id))
return [id[0] for id in ids] if ids is not None else None
def get_se_estimator(estimator, security_id, trading_destinations, day, context):
estimator_id = get_estimator_id(estimator)
job_id = get_job_id(estimator_id, context, security_id, trading_destinations)
if job_id == None:
print "no data for (security_id: %d, trading_destination: %s)" % (security_id, trading_destinations)
job_id = get_job_id(estimator_id, context, security_id, 'NULL')
if job_id == None:
print "no data for (security_id: %d, trading_destination: NULL)" % (security_id)
job_id = get_job_id(estimator_id, context, 'NULL', trading_destinations)
if job_id != None:
date_se = Connections.exec_sql('QUANT',
''' SELECT convert(varchar(8),max(er.stamp_date),112)
FROM %s..estimator_runs er WHERE
er.job_id = %d AND er.stamp_date <= '%s'
AND estimator_id = %d
''' % (bq, job_id, day, estimator_id)
)
if date_se != None and date_se[0][0] is not None:
data_se = Connections.exec_sql('QUANT',
''' SELECT pd.parameter_name, pv.value FROM %s..estimator_runs er,
%s..param_desc pd, %s..param_value pv
WHERE er.estimator_id = %d
AND pv.parameter_id = pd.parameter_id
AND pd.estimator_id = er.estimator_id
AND pv.run_id = er.run_id
AND er.run_id = (SELECT max(run_id) FROM %s..estimator_runs WHERE stamp_date='%s' AND job_id=%d and estimator_id=%d)
''' % (bq, bq, bq, estimator_id, bq, date_se[0][0], job_id, estimator_id)
)
data_se.append(('run_date', date_se[0][0]))
return data_se
else:
raise ValueError("No SE entries for this date.")
else:
raise ValueError ("No job_id for estimator %s" % estimator)
def get_runs_by_perimeters_for_date(perimeter_ids, estimator_id, context_id, date):
ids = Connections.exec_sql("QUANT",
''' SELECT ds.security_id, er.stamp_date, er.run_id, run_quality FROM %s..perimeter_domain pd
JOIN %s..domain_security ds on ds.domain_id=pd.domain_id
JOIN %s..association a on a.security_id=ds.security_id AND a.estimator_id= %d
AND a.context_id= %d
AND isnull(ds.trading_destination_id,0) = isnull(a.trading_destination_id,0)
JOIN %s..estimator_runs er on er.job_id=a.job_id and er.context_id = a.context_id and er.estimator_id = a.estimator_id
WHERE pd.perimeter_id in (%s) and stamp_date < '%s'
GROUP by ds.security_id having er.run_id = max(er.run_id)
''' % (bq, bq, bq, estimator_id, context_id, bq, ','.join(map(str,perimeter_ids)), date)
)
return ids
def get_context_runs_by_perimeter(perimeter_ids, estimator_id, context_ids):
ids = Connections.exec_sql('QUANT',
''' select ds.security_id, er.stamp_date, lr.last_run_id, lr.context_id
from quant..perimeter_domain pd
join quant..domain_security ds on ds.domain_id = pd.domain_id
join quant..job j on ds.domain_id = j.domain_id and j.estimator_id = %d and ds.domain_id = j.domain_id
join quant..perimeter_job pj on pj.job_id = j.job_id and pj.is_active = 1
join quant..last_run lr on lr.job_id = j.job_id and lr.context_id in (%s)
join quant..estimator_runs er on er.run_id = lr.last_run_id
where ds.trading_destination_id is null and pd.perimeter_id in (%s)
''' % (estimator_id, ','.join(map(str,context_ids)), ','.join(map(str,perimeter_ids)))
)
return ids
def get_job_id(estimator, context, s_security_id, s_trading_destination_id):
if isinstance(estimator, int):
estimator_id = estimator
else:
estimator_id = get_estimator_id(estimator)
job_id = Connections.exec_sql("QUANT",
"select job_id from %s..association WHERE \
estimator_id=%d \
AND context_id=(SELECT context_id FROM %s..context WHERE context_name = \'%s\' AND estimator_id=%d)\
AND security_id=%s\
AND trading_destination_id=%s" %
(bq, estimator_id, bq, context, estimator_id, s_security_id, s_trading_destination_id)
)
return job_id[0][0] if len(job_id) > 0 else None
def get_last_run_id(job_id, estimator_id):
run_id = Connections.exec_sql('QUANT',
''' SELECT last_run_id, convert(varchar(8), last_run_date, 104) last_run_date
FROM %s..last_run WHERE AND job_id=%d
''' % (bq, estimator_id, job_id))
return (int(run_id[0][0]), run_id[0][1]) if run_id is not None else None
def get_estimator_id(estimator):
estimator_id = Connections.exec_sql("QUANT",
"SELECT estimator_id FROM %s..estimator WHERE estimator_name = \'%s\'"
% (bq, estimator))
return int(estimator_id[0][0]) if estimator_id is not None else None
