def get_rt_dg_case_list(orgn, dstn):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute(" SELECT DISTINCT category_dim_h.case_id\
FROM category_dim_h\
LEFT OUTER JOIN case_flight_fact\
ON category_dim_h.case_id = case_flight_fact.case_id\
WHERE sub_cat_nm = 'Downgrading' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "' \
UNION \
SELECT DISTINCT case_compensation_fact.case_id\
FROM case_compensation_fact\
LEFT OUTER JOIN case_flight_fact\
ON case_compensation_fact.case_id = case_flight_fact.case_id\
WHERE reason_desc='Downgrading' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "'")
case_list = []
for case in siebel_curs.fetchall():
if case not in case_list:
# Do this check due to some problem
# with DB2 SQL UNION DISTINCT.
# It merely doesn't work
case_list.append(case[0])
return case_list
def get_flt_db_case_list(orgn, dstn, fltnum):
# FIXME: why don't use SQL INTERSECT instead of 2 SQL SELECTs?
siebel_curs = dbConnector.get_siebel_curs()
# first take all cases from with subcategory overbooked
siebel_curs.execute("SELECT DISTINCT category_dim_h.case_id\
FROM category_dim_h\
LEFT OUTER JOIN case_flight_fact\
ON category_dim_h.case_id = case_flight_fact.case_id\
WHERE sub_cat_nm='Overbooking' AND\
case_flight_fact.flight_nbr='" + fltnum[1:] + "' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "'")
case_list = [list(case) for case in siebel_curs.fetchall()]
# second take all cases from compensation with reason denied boarding
siebel_curs.execute("SELECT DISTINCT case_compensation_fact.case_id\
FROM case_compensation_fact\
LEFT OUTER JOIN case_flight_fact\
ON case_compensation_fact.case_id = case_flight_fact.case_id\
WHERE reason_desc='Denied Boarding' AND\
case_flight_fact.flight_nbr='" + fltnum[1:] + "' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "'")
for case in siebel_curs.fetchall():
if case not in case_list:
# add element, because it isn't present in old list
case_list.append(list(case))
return case_list
def get_flt_dg_case_list(orgn, dstn, fltnum):
siebel_curs = dbConnector.get_siebel_curs()
# first take all cases with subcategory 'Downgrading'
siebel_curs.execute("SELECT DISTINCT category_dim_h.case_id\
FROM category_dim_h\
LEFT OUTER JOIN case_flight_fact\
ON category_dim_h.case_id = case_flight_fact.case_id\
WHERE sub_cat_nm = 'Downgrading' AND\
case_flight_fact.flight_nbr='" + fltnum[1:] + "' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "'")
case_list = [list(case) for case in siebel_curs.fetchall()]
# second take cases with reason 'Downgrading'
siebel_curs.execute("SELECT DISTINCT case_compensation_fact.case_id\
FROM case_compensation_fact\
LEFT OUTER JOIN case_flight_fact\
ON case_compensation_fact.case_id = case_flight_fact.case_id\
WHERE reason_desc='Downgrading' AND\
case_flight_fact.flight_nbr='" + fltnum[1:] + "' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "'")
for case in siebel_curs.fetchall():
if case not in case_list:
case_list.append(case)
return case_list
def get_cmpnstn_list(self):
# FIXME consider storing this value in object
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT case_cmpnstn_id, compenation_status_description,\
cmpnstn_type_desc, paid_to, cmpnstn_amt, reason_desc\
FROM case_compensation_fact\
WHERE case_id='" + self.case_id + "'")
case_list = [list(case) for case in siebel_curs.fetchall()]
return case_list
def get_cmpn_amt(self):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT compenation_status_description, cmpnstn_amt, reason_desc\
FROM case_compensation_fact\
WHERE case_id='" + self.case_id + "'")
cmpn_list = siebel_curs.fetchall()
total_cmpn = 0
stats = []
reason_list = []
for cmpn in cmpn_list:
cmpn_status = cmpn[0]
if cmpn[1] is None:
continue
if cmpn[2] is not None and cmpn[2] not in reason_list:
reason_list.append(cmpn[2])
cmpn_amt = float(cmpn[1])
if cmpn_status == 'Cancelled':
total_cmpn = total_cmpn - cmpn_amt
elif cmpn_status == 'Completed':
total_cmpn = total_cmpn + cmpn_amt
elif cmpn_status == 'Issued':
total_cmpn = total_cmpn + cmpn_amt
elif cmpn_status == 'Pending':
# Status is unclear, don't take them.
# Hopefully there arent' many such cases.
pass
elif cmpn_status == 'Rejected':
total_cmpn = 0
stats.append(cmpn_status)
ret_val = {'cmpn': {'amt': total_cmpn, 'stats': stats}, 'reason_list': reason_list}
return ret_val
def get_fltnum_date_list(self):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT flight_nbr, calendar_date\
FROM case_flight_fact\
LEFT OUTER JOIN calendar_dim\
ON case_flight_fact.flight_date = calendar_dim.calendar_key\
WHERE case_id = '" + self.case_id + "'")
ret_val = []
for flt_date in siebel_curs.fetchall():
ret_val.append({'fltnum': flt_date[0], 'date': flt_date[1]})
return ret_val
def get_cat_list(self):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT cat_nm,\
sub_cat_nm,\
child_sub_cat_nm\
FROM category_dim_h\
WHERE case_id = '" + self.case_id + "'")
cat_list = []
for cat in siebel_curs.fetchall():
cat_el = {'cat': cat[0], 'sub_cat': cat[1], 'child_sub_cat': cat[2]}
cat_list.append(cat_el)
return cat_list
def get_route_case_list(orgn, dstn, fltnum):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT case_flight_fact.case_id,\
case_flight_fact.pax_cnt,\
case_fact.case_status_desc,\
case_fact.assgn_cat_cd\
FROM case_flight_fact\
LEFT OUTER JOIN case_fact\
ON case_flight_fact.case_id = case_fact.case_id\
WHERE case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "' AND\
case_flight_fact.airline_nm='AY'")
case_list = [list(case) for case in siebel_curs.fetchall()]
return case_list
def get_fltdep_case_list(orgn, dstn, fltnum, date):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT case_flight_fact.case_id,\
case_flight_fact.pax_cnt,\
case_fact.case_status_desc,\
case_fact.assgn_cat_cd\
FROM case_flight_fact\
LEFT OUTER JOIN calendar_dim\
ON case_flight_fact.flight_date = calendar_dim.calendar_key\
LEFT OUTER JOIN case_fact\
ON case_flight_fact.case_id = case_fact.case_id\
WHERE case_flight_fact.flight_nbr='" + fltnum[1:] + "' AND\
case_flight_fact.dep_city='" + orgn + "' AND\
case_flight_fact.arr_city='" + dstn + "' AND\
calendar_dim.calendar_date='" + date + "' AND\
case_flight_fact.airline_nm = 'AY'")
# (case_id, pax_cnt, case_status_desc, assgn_cat_cd)
case_list = [list(case) for case in siebel_curs.fetchall()]
ret_val_list = []
for case in case_list:
ret_val = []
case_obj = Case(case[0])
cmpn = case_obj.get_cmpn_amt()
ret_val.append(case[0]) # case id
ret_val.append(case_obj.get_num_pax()) # number of passengers
ret_val.append(case[2]) # case status
ret_val.append(case[3]) # category
ret_val.append(cmpn['cmpn']['amt']) # case compensation amount
ret_val.append(cmpn['reason_list']) # reason description
ret_val.append(case_obj.get_cat_list()) # list of categories / subcategories / child subcategories
ret_val_list.append(ret_val)
return ret_val_list
def get_dep_dg_case_list(orgn, dstn, fltnum, date):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute(" SELECT DISTINCT category_dim_h.case_id\
FROM category_dim_h\
LEFT OUTER JOIN case_flight_fact\
ON category_dim_h.case_id = case_flight_fact.case_id\
LEFT OUTER JOIN calendar_dim\
ON case_flight_fact.flight_date = calendar_dim.calendar_key\
WHERE sub_cat_nm = 'Downgrading' AND\
case_flight_fact.dep_city = '" + orgn + "' AND\
case_flight_fact.arr_city = '" + dstn + "' AND\
case_flight_fact.flight_nbr = '" + fltnum[1:] + "' AND\
calendar_dim.calendar_date = DATE('" + date + "')\
UNION\
SELECT DISTINCT case_compensation_fact.case_id\
FROM case_compensation_fact\
LEFT OUTER JOIN case_flight_fact\
ON case_compensation_fact.case_id = case_flight_fact.case_id\
LEFT OUTER JOIN calendar_dim\
ON case_flight_fact.flight_date = calendar_dim.calendar_key\
WHERE reason_desc = 'Downgrading' AND\
case_flight_fact.dep_city = '" + orgn + "' AND\
case_flight_fact.arr_city = '" + dstn + "' AND\
case_flight_fact.flight_nbr = '" + fltnum[1:] + "' AND\
calendar_dim.calendar_date = DATE('" + date + "')")
case_list = []
for case in siebel_curs.fetchall():
if case not in case_list:
case_list.append(case[0])
return case_list
def get_num_pax(self):
siebel_curs = dbConnector.get_siebel_curs()
siebel_curs.execute("SELECT pax_cnt FROM case_fact WHERE case_id='" + self.case_id + "'")
pax_cnt = siebel_curs.fetchone()[0]
return pax_cnt if pax_cnt is not None else 0