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