Example #1
0
    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
Example #2
0
    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 
Example #3
0
    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      
Example #4
0
    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
Example #5
0
    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
Example #6
0
    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                          
Example #7
0
    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
Example #8
0
    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
Example #9
0
    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
Example #10
0
    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
Example #11
0
    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