routeGroup

-(void)getDataFromfilter : (NSString *)strTypeCode{

    FMResultSet *results;

    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];

    if(![defaults objectForKey:@"Category"]){
        [defaults setObject:@"Natuurwandelingen" forKey:@"Category"];
        [defaults synchronize];
    }

    NSString *isdog = @"0";

    if([defaults boolForKey:@"DogAllowed"]){
        if([defaults boolForKey:@"DogAllowed"]){
            isdog = @"1";
        }
    }

    BOOL isareafilter = NO;

    //Check for Area Filter
    if (![[defaults objectForKey:@"DistanceTo"] isEqualToString:@"Select"] && ![[defaults objectForKey:@"DistanceFrom"] isEqualToString:@"Select"]) {
        isareafilter = YES;
    }

    NSString *disTo = @"0";
    NSString *disFrom = @"0";

    if ([[defaults objectForKey:@"DistanceTo"] intValue] > [[defaults objectForKey:@"DistanceFrom"] intValue]) {
        disTo = [NSString stringWithFormat:@"%@",[defaults objectForKey:@"DistanceTo"]];
        disFrom = [NSString stringWithFormat:@"%@",[defaults objectForKey:@"DistanceFrom"]];
    }else{
        disTo = [NSString stringWithFormat:@"%@",[defaults objectForKey:@"DistanceFrom"]];
        disFrom = [NSString stringWithFormat:@"%@",[defaults objectForKey:@"DistanceTo"]];
    }

    NSMutableArray *arrAllRouteGroups = [[NSMutableArray alloc]init];

    BOOL isdatefilter = NO;

    //Check For Date Filter
    if([[defaults objectForKey:@"Category"] isEqualToString:@"KNBLO"] || [[defaults objectForKey:@"Category"] isEqualToString:@"IVN"]){

        if (![defaults objectForKey:@"Date"] && [[defaults objectForKey:@"Date"] isEqualToString:@"Select Date"]) {
            isdatefilter = YES;
        }
    }

    if (isdatefilter) {
        if ([[defaults objectForKey:@"Date"] isEqualToString:@"Coming Week"]) {
            NSString *startday = [self ListDates2:1];
            NSString *endday = [self ListDates2:7];

            if (isareafilter) {
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND (rg.eventStartDate >= %@ ) AND (rg.eventStartDate <= %@ )  AND (rg.eventFinishDate >= %@) AND (rg.eventFinishDate <= %@) AND r.dogsAllowed = ? AND r.lengthInKM >= ? AND r.lengthInKM <= ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,startday,endday,startday,endday,isdog,disFrom,disTo];
            }else{
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND (rg.eventStartDate >= %@ ) AND (rg.eventStartDate <= %@ )  AND (rg.eventFinishDate >= %@) AND (rg.eventFinishDate <= %@) AND r.dogsAllowed = ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,startday,endday,startday,endday,isdog];
            }

        }else if([[defaults objectForKey:@"Date"] isEqualToString:@"Coming Weekend"]){

            NSCalendar *gregorian = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar] ;
            NSDateComponents *comps = [gregorian components:NSWeekdayCalendarUnit fromDate:[NSDate date]];
            int weekday = (int)[comps weekday];
            int daysTosaturday = (14 - weekday) % 7;
            int daysTosunday = (8 - weekday) % 7;

            NSString *satur = [self ListDates2:daysTosaturday];
            NSString *sunday = [self ListDates2:daysTosunday];

            if (isareafilter) {
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND (rg.eventStartDate >= %@ ) AND (rg.eventStartDate <= %@ )  AND (rg.eventFinishDate >= %@) AND (rg.eventFinishDate <= %@) AND r.dogsAllowed = ? AND r.lengthInKM >= ? AND r.lengthInKM <= ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,satur,sunday,satur,sunday,isdog,disFrom,disTo];
            }else{
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND (rg.eventStartDate >= %@ ) AND (rg.eventStartDate <= %@ )  AND (rg.eventFinishDate >= %@) AND (rg.eventFinishDate <= %@) AND r.dogsAllowed = ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,satur,sunday,satur,sunday,isdog];
            }

        }else{
            if (isareafilter) {
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND rg.eventStartDate < %@ AND rg.eventFinishDate > %@ AND r.dogsAllowed = ? AND r.lengthInKM >= ? AND r.lengthInKM <= ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,[defaults objectForKey:@"Date"],[defaults objectForKey:@"Date"],isdog,disFrom,disTo];
            }else{
                results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND rg.eventStartDate < %@ AND rg.eventFinishDate > %@ AND r.dogsAllowed = ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,[defaults objectForKey:@"Date"],[defaults objectForKey:@"Date"],isdog];
            }

        }
    }else{

        if (isareafilter) {
            results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND r.dogsAllowed = ? AND r.lengthInKM >= ? AND r.lengthInKM <= ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,isdog,disFrom,disTo];
        }else{
            results = [database executeQuery:@"SELECT rg.*, COUNT(r.routeCode) AS totalNumberOfRoutes FROM routeGroups AS rg INNER JOIN routes AS r ON rg.routeGroupCode = r.routeGroup WHERE rg.type = ? AND r.dogsAllowed = ? GROUP BY rg.routeGroupCode ORDER BY rg.name ASC",strTypeCode,isdog];
        }

    }

    NSString *strAreaOrProvince;
    NSString *strAreaSizeInHA;
    NSString *strDescr;
    NSString *strEventFinishDate;
    NSString *strEventStartDate;
    NSString *strLatitude;
    NSString *strLongitude;
    NSString *strName;
    NSString *strRouteGroupCode;
    NSString *strType;
    NSString *strgpsStatus;
    int totalroutes = 0;

    while([results next]){

        strAreaOrProvince = [results stringForColumn:@"areaOrProvince"];
        strAreaSizeInHA = [results stringForColumn:@"areaSizeInHA"];
        strDescr = [results stringForColumn:@"descr"];
        strEventFinishDate = [results stringForColumn:@"eventFinishDate"];
        strEventStartDate = [results stringForColumn:@"eventStartDate"];
        strLatitude = [results stringForColumn:@"latitude"];
        strLongitude = [results stringForColumn:@"longitude"];
        strName = [results stringForColumn:@"name"];
        strRouteGroupCode = [results stringForColumn:@"routeGroupCode"];
        strType = [results stringForColumn:@"type"];
        strgpsStatus = [results stringForColumn:@"gpsstatus"];

        totalroutes = [results intForColumn:@"totalNumberOfRoutes"];

        NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];

        [dict setObject:strAreaOrProvince forKey:@"areaOrProvince"];
        [dict setObject:strAreaSizeInHA forKey:@"areaSizeInHA"];
        [dict setObject:strDescr forKey:@"descr"];
        [dict setObject:strEventFinishDate forKey:@"eventFinishDate"];
        [dict setObject:strEventStartDate forKey:@"eventStartDate"];
        [dict setObject:strLatitude forKey:@"latitude"];
        [dict setObject:strLongitude forKey:@"longitude"];
        [dict setObject:strName forKey:@"name"];
        [dict setObject:strRouteGroupCode forKey:@"routeGroupCode"];
        [dict setObject:strType forKey:@"type"];
        [dict setObject:strgpsStatus forKey:@"gpsStatus"];

        [arrAllRouteGroups addObject:dict];
    }

    if (arrRoutesList != nil) {
        arrRoutesList = nil;
    }

    arrRoutesList = [[NSMutableArray alloc] initWithArray:arrAllRouteGroups];
    [self addAnnotation:arrRoutesList];
}