محاسبه پایلوت‌پلوشن در شبکه 3G با استفاده از ابزار SWISSQUAL

				
					def PilotPollution():
    querypath = os.getcwd() + '\PilotPollution'
    server_ip = input("please input server IP: ")
    dbname = input("please input Database name: ")

    conn_01 = connecttodb(server_ip, dbname)
    _query_3G_Voice = readtextfile(querypath + '\pp_query.sql')

    df_query_3G_Voice = pd.read_sql(_query_3G_Voice, conn_01)
    df_3G_Voice_Activesets_merged = pd.DataFrame(
        columns=['posid', 'latitude', 'longitude', 'UARFCN', 'Band', 'LAC', 'CId', 'Served_RSCP',
                 'Served_EcNo', 'RSCP_A1', 'RSCP_A2', 'RSCP_A3', 'EcNo_A1', 'EcNo_A2', 'EcNo_A3'
            , 'PSC_A1', 'PSC_A2', 'PSC_A3', 'RSCP_N1', 'RSCP_N2', 'RSCP_N3', 'RSCP_N4', 'RSCP_N5', 'RSCP_N6',
                 'EcNo_N1', 'EcNo_N2', 'EcNo_N3'
            , 'EcNo_N4', 'EcNo_N5', 'EcNo_N6', 'PSC_N1', 'PSC_N2', 'PSC_N3', 'PSC_N4', 'PSC_N5', 'PSC_N6',
                 'Diff_RSCP', 'Status'])

    _RSCP_avg = None
    _EcNo_avg = None
    _RSCP_A1 = None
    _RSCP_A2 = None
    _RSCP_A3 = None
    _EcNo_A1 = None
    _EcNo_A2 = None
    _EcNo_A3 = None
    _PSC_A1 = None
    _PSC_A2 = None
    _PSC_A3 = None
    _RSCP_N1 = None
    _RSCP_N2 = None
    _RSCP_N3 = None
    _RSCP_N4 = None
    _RSCP_N5 = None
    _RSCP_N6 = None
    _EcNo_N1 = None
    _EcNo_N2 = None
    _EcNo_N3 = None
    _EcNo_N4 = None
    _EcNo_N5 = None
    _EcNo_N6 = None
    _PSC_N1 = None
    _PSC_N2 = None
    _PSC_N3 = None
    _PSC_N4 = None
    _PSC_N5 = None
    _PSC_N6 = None

    _PosId = None
    _Latitude = None
    _Longitude = None
    _UARFCN = None
    _Band = None
    _LAC = None
    _CID = None
    status_pp = "Normal"
    Diff_RSCP_best_neighbor_first_activeset = None
    min_active_rscp = None

    WCDMA_posid = df_query_3G_Voice.groupby("PosId")
    for key, item in WCDMA_posid:
        RSCP_posid_RSCP = item.groupby("AvgEcIo")
        activesetnum = 0
        for key_rscp, item_rscp in RSCP_posid_RSCP:
            Activesets_RSCP = item_rscp.groupby("AggrEcIo_PSC")
            neighborstnum = 0
            for active_key_rscp, active_item_rscp in Activesets_RSCP:
                activesetnum += 1
                if activesetnum == 1:
                    _RSCP_avg = active_item_rscp.iloc[0]['AvgRSCP']
                    _EcNo_avg = active_item_rscp.iloc[0]['AvgEcIo']
                    _PosId = active_item_rscp.iloc[0]['PosId']
                    _Latitude = active_item_rscp.iloc[0]['Latitude']
                    _Longitude = active_item_rscp.iloc[0]['Longitude']
                    _UARFCN = active_item_rscp.iloc[0]['FreqDL']
                    _Band = active_item_rscp.iloc[0]['Technology']
                    _LAC = active_item_rscp.iloc[0]['LAC']
                    _CID = active_item_rscp.iloc[0]['CID']
                    _RSCP_A1 = active_item_rscp.iloc[0]['RSCP_PSC']
                    _EcNo_A1 = active_item_rscp.iloc[0]['AggrEcIo_PSC']
                    _PSC_A1 = active_item_rscp.iloc[0]['PSCCode']
                elif activesetnum == 2:
                    _RSCP_A2 = active_item_rscp.iloc[0]['RSCP_PSC']
                    _EcNo_A2 = active_item_rscp.iloc[0]['AggrEcIo_PSC']
                    _PSC_A2 = active_item_rscp.iloc[0]['PSCCode']
                elif activesetnum == 3:
                    _RSCP_A3 = active_item_rscp.iloc[0]['RSCP_PSC']
                    _EcNo_A3 = active_item_rscp.iloc[0]['AggrEcIo_PSC']
                    _PSC_A3 = active_item_rscp.iloc[0]['PSCCode']
                Neighbor_RSCP = active_item_rscp.groupby('neighbor_SC')
                for Neighbor_key_rscp, Neighbor_item_rscp in Neighbor_RSCP:
                    neighborstnum += 1
                    if neighborstnum == 1:
                        _RSCP_N1 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N1 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N1 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
                    elif neighborstnum == 2:
                        _RSCP_N2 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N2 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N2 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
                    elif neighborstnum == 3:
                        _RSCP_N3 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N3 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N3 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
                    elif neighborstnum == 4:
                        _RSCP_N4 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N4 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N4 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
                    elif neighborstnum == 5:
                        _RSCP_N5 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N5 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N5 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
                    elif neighborstnum == 6:
                        _RSCP_N6 = Neighbor_item_rscp['neighbor_RSCP'].mean()
                        _EcNo_N6 = Neighbor_item_rscp['neighbor_EcNo'].mean()
                        _PSC_N6 = Neighbor_item_rscp.iloc[0]['neighbor_SC']
        if _RSCP_A1 != None and _RSCP_A2 != None and _RSCP_A3 != None:
            min_active_rscp = min(_RSCP_A1, _RSCP_A2, _RSCP_A3)
        elif _RSCP_A1 != None and _RSCP_A2 != None:
            min_active_rscp = min(_RSCP_A1, _RSCP_A2)
        elif _RSCP_A1 != None:
            min_active_rscp = _RSCP_A1

        if _RSCP_N1 != None and _RSCP_N2 != None and _RSCP_N3 != None and _RSCP_N4 != None and _RSCP_N5 != None and _RSCP_N6 != None:
            Diff_RSCP_best_neighbor_first_activeset = max(_RSCP_N1, _RSCP_N2, _RSCP_N3, _RSCP_N4, _RSCP_N5,
                                                          _RSCP_N6) - min_active_rscp
            if Diff_RSCP_best_neighbor_first_activeset >= 5:
                status_pp = "Pilot Pollution"
        elif _RSCP_N1 != None and _RSCP_N2 != None and _RSCP_N3 != None and _RSCP_N4 != None and _RSCP_N5 != None:
            Diff_RSCP_best_neighbor_first_activeset = max(_RSCP_N1, _RSCP_N2, _RSCP_N3, _RSCP_N4,
                                                          _RSCP_N5) - min_active_rscp
            if Diff_RSCP_best_neighbor_first_activeset >= 5:
                status_pp = "Pilot Pollution"
        elif _RSCP_N1 != None and _RSCP_N2 != None and _RSCP_N3 != None and _RSCP_N4 != None:
            Diff_RSCP_best_neighbor_first_activeset = max(_RSCP_N1, _RSCP_N2, _RSCP_N3, _RSCP_N4) - min_active_rscp
            if Diff_RSCP_best_neighbor_first_activeset >= 5:
                status_pp = "Pilot Pollution"
        elif _RSCP_N1 != None and _RSCP_N2 != None and _RSCP_N3 != None:
            Diff_RSCP_best_neighbor_first_activeset = max(_RSCP_N1, _RSCP_N2, _RSCP_N3) - min_active_rscp
            if Diff_RSCP_best_neighbor_first_activeset >= 5:
                status_pp = "Pilot Pollution"
        elif _RSCP_N1 != None:
            Diff_RSCP_best_neighbor_first_activeset = _RSCP_N1 - min_active_rscp
            if Diff_RSCP_best_neighbor_first_activeset >= 5:
                status_pp = "Pilot Pollution"

        df_new_row = {'posid': _PosId,
                      'latitude': _Latitude,
                      'longitude': _Longitude,
                      'UARFCN': _UARFCN,
                      'Band': _Band, 'LAC': _LAC,
                      'CId': _CID,
                      'Served_RSCP': _RSCP_avg,
                      'Served_EcNo': _EcNo_avg,
                      'RSCP_A1': _RSCP_A1,
                      'RSCP_A2': _RSCP_A2,
                      'RSCP_A3': _RSCP_A3,
                      'EcNo_A1': _EcNo_A1,
                      'EcNo_A2': _EcNo_A2,
                      'EcNo_A3': _EcNo_A3,
                      'PSC_A1': _PSC_A1,
                      'PSC_A2': _PSC_A2,
                      'PSC_A3': _PSC_A3,
                      'RSCP_N1': _RSCP_N1,
                      'RSCP_N2': _RSCP_N2,
                      'RSCP_N3': _RSCP_N3,
                      'RSCP_N4': _RSCP_N4,
                      'RSCP_N5': _RSCP_N5,
                      'RSCP_N6': _RSCP_N6,
                      'EcNo_N1': _EcNo_N1,
                      'EcNo_N2': _EcNo_N2,
                      'EcNo_N3': _EcNo_N3,
                      'EcNo_N4': _EcNo_N4,
                      'EcNo_N5': _EcNo_N5,
                      'EcNo_N6': _EcNo_N6,
                      'PSC_N1': _PSC_N1,
                      'PSC_N2': _PSC_N2,
                      'PSC_N3': _PSC_N3,
                      'PSC_N4': _PSC_N1,
                      'PSC_N5': _PSC_N2,
                      'PSC_N6': _PSC_N3,
                      'Diff_RSCP_best_neighbor_first_activeset': Diff_RSCP_best_neighbor_first_activeset,
                      'Status': status_pp}
        df_3G_Voice_Activesets_merged = df_3G_Voice_Activesets_merged.append(df_new_row, ignore_index=True)
        _RSCP_avg = None
        _EcNo_avg = None
        _RSCP_A1 = None
        _RSCP_A2 = None
        _RSCP_A3 = None
        _EcNo_A1 = None
        _EcNo_A2 = None
        _EcNo_A3 = None
        _PSC_A1 = None
        _PSC_A2 = None
        _PSC_A3 = None
        _RSCP_N1 = None
        _RSCP_N2 = None
        _RSCP_N3 = None
        _RSCP_N4 = None
        _RSCP_N5 = None
        _RSCP_N6 = None
        _EcNo_N1 = None
        _EcNo_N2 = None
        _EcNo_N3 = None
        _EcNo_N4 = None
        _EcNo_N5 = None
        _EcNo_N6 = None
        _PSC_N1 = None
        _PSC_N2 = None
        _PSC_N3 = None
        _PSC_N4 = None
        _PSC_N5 = None
        _PSC_N6 = None
        _PosId = None
        _Latitude = None
        _Longitude = None
        _UARFCN = None
        _Band = None
        _LAC = None
        _CID = None
        status_pp = "Normal"
        min_active_rscp = None
        Diff_RSCP_best_neighbor_first_activeset = None
    df_3G_Voice_Activesets_merged.to_csv(dbname + '_df_3G_PilotPollution_merged.csv', index=False)