#import "SQLdataManger.h"

#import "FMDatabaseAdditions.h"

 

static SQLdataManger * instance=nil;

 

#define SQLfileName @"__MACOSX"

 

@interfaceSQLdataManger(){

    NSString *sqlDataPath;

}

@end

 

@implementation SQLdataManger

 

+(SQLdataManger*)getInstance

{

    if( instance == nil ){

        //        static dispatch_once_t onceToken;

        @synchronized(self) {

            instance =  [[SQLdataManger alloc] init];

            [instance createTable];

            

            NSLog(@"-------------------------startInit");

        }

        //        });

    }

    

    returninstance;

}

+ (void)clearInstance{

    if (instance) {

        instance = nil;

        

    }

}

//将对象转换成json字符串

- (NSString *)changeObjectToJsonString : (id)objec{

    

    NSData *tempData = [NSJSONSerialization dataWithJSONObject:objec options:0 error:nil];

    NSString *jsonString = [[NSStringalloc]initWithData:tempData encoding:NSUTF8StringEncoding];

    //    NSLog(@"jsonString is %@" ,jsonString);

    return jsonString ;

}

 

- (void)createTable {

    NSFileManager * fileManager = [NSFileManagerdefaultManager];

    

    NSArray *path = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);

    NSString *paths = [[path objectAtIndex:0] stringByAppendingPathComponent:SQLfileName];

    

    sqlDataPath = paths;

    

    if ([fileManager fileExistsAtPath:paths] == NO) {

        // create it

        FMDatabase * db = [FMDatabase databaseWithPath:paths];

        if ([db open]) {

            

            //建立第一张表:用于获取新闻是否读取状态

            NSString * sql = @"CREATE TABLE 'ReadStateTable' (id text identity primary key , 'isRead' VARCHAR)";

            BOOL res = [db executeUpdate:sql];

            if (!res) {

                NSLog(@"error when creating db table");

            } else {

                NSLog(@"succ to creating db table");

            }

            

            //建立第二张表,用于存储视频是否读取状态

            sql = @"CREATE TABLE 'VideoReadStateTable' (id text identity primary key , 'isRead' VARCHAR)";

            res = [db executeUpdate:sql];

            if (!res) {

                NSLog(@"error when creating db table");

            } else {

                NSLog(@"succ to creating db table");

            }

            

            //建立第三张表,用于存储联系人

            sql = @"CREATE TABLE 'PersonListTable' (id text identity primary key , 'email' VARCHAR , 'headPic' VARCHAR ,'job' VARCHAR ,'name' VARCHAR ,'phone' VARCHAR ,'remark' VARCHAR )";

            res = [db executeUpdate:sql];

            if (!res) {

                NSLog(@"error when creating db table");

            } else {

                NSLog(@"succ to creating db table");

            }

            

            [db close];

        } else {

            NSLog(@"error when open db");

        }

    }

}

 

- (NSString *)checkNULLstring:(NSString *)oriString{

    NSString *string = @"";

    if( !oriString ) return string;

    if( [oriString isKindOfClass:[NSNull class] ] ) return string ;

    string =[NSString stringWithFormat:@"%@" , oriString ];

    

    return string ;

}

 

#pragma mark PersonList

- (BOOL)insertPersonListDataWithData:(NSArray *)transArray{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL res = NO ;

    int nums = [transArray count];

    if ([db open]) {

        

        [db beginTransaction];

        BOOL isRollBack = NO;

        @try {

            NSString * sql = @"insert into PersonListTable (id,email,headPic,job,name,phone,remark) values(?,?,?,?,?,?,?)" ;

            for( int i =0 ;i<nums ;i++ ){

                NSDictionary *dic = [transArray objectAtIndex:i];

                NSString *idString = [self checkNULLstring:[dic objectForKey:@"id"]];

                NSString *email = [self checkNULLstring:[dic objectForKey:@"email"]];

                NSString *headPic = [self checkNULLstring:[dic objectForKey:@"headPic"]];

                NSString *job = [self checkNULLstring:[dic objectForKey:@"job"]];

                NSString *name = [self checkNULLstring:[dic objectForKey:@"name"]];

                NSString *phone = [self checkNULLstring:[dic objectForKey:@"phone"]];

                NSString *remark = [self checkNULLstring:[dic objectForKey:@"remark"]];

                

                res = [db executeUpdate:sql,idString,email,headPic,job,name,phone,remark];

            }

        }

        @catch (NSException *exception) {

            [db rollback];

        }

        @finally {

            if (!isRollBack) {

                res = YES ;

                [db commit];

            }

        }

        

        [db close];

    }

    returnYES;

}

 

- (NSArray *)queryPersonListWithSql:(NSString *)querySQL{

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

    

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    NSString *sql = [NSStringstringWithFormat:@"select * from PersonListTable"];

    if( querySQL && ![querySQL isEqualToString:@""] ){

        sql = [NSStringstringWithFormat:@"%@ where name like '%%%@%%' or job like '%%%@%%'" ,sql , querySQL , querySQL ];

    }

    

    if ([db open]) {

        FMResultSet * rs = [db executeQuery:sql];

        

        while ([rs next]) {

            NSDictionary *dic = [rs resultDictionary] ;

            [mutableArray addObject:dic];

        }

        [db close];

    }

    

    return mutableArray ;

}

 

#pragma mark NewsTable

- (BOOL)queryIsReadOfReadStateTable:(NSString *)idString{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL isExit = NO ;

    

    if ([db open]) {

        NSString * sql = [NSStringstringWithFormat:@"select * from ReadStateTable where id = '%@' " , idString ];

//        NSLog(@"sql is %@" , sql);

        

        FMResultSet * rs = [db executeQuery:sql];

        

        while ([rs next]) {

            NSDictionary *dic = [rs resultDictionary] ;

            isExit = [[dic objectForKey:@"isRead"] boolValue] ;

            break;

        }

        [db close];

    }

    

    return isExit ;

}

 

- (BOOL)insertReadStateTableData:(NSArray *)transArray{

//    return NO;

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL res = NO ;

    int nums = [transArray count];

    if ([db open]) {

        NSString * sql = @"insert into ReadStateTable (id,isRead) (SELECT '%@','%@' FROM ReadStateTable u2  JOIN( SELECT COUNT(*) num FROM ReadStateTable u1 WHERE u1.id = '%@') u3 ON u3.num < 1  LIMIT 1)" ;

        for( int i =0 ;i<nums ;i++ ){

            NSDictionary *dic = [transArray objectAtIndex:i];

            NSString *idString = [self checkNULLstring:[dic objectForKey:@"id"]];

            

            NSString *isRead = [NSString stringWithFormat:@"%@",[NSNumber numberWithBool:NO]];

            sql = [NSString stringWithFormat:sql,idString,isRead,idString];

            res = [db executeUpdate:sql];

        }

        

//        [db beginTransaction];

//        BOOL isRollBack = NO;

//        @try {

//            //CREATE TABLE 'ReadStateTable' (id text identity primary key , 'isRead' VARCHAR)

//            NSString * sql = @"insert into ReadStateTable (id,isRead) values(?,?)" ;

//            for( int i =0 ;i<nums ;i++ ){

//                NSDictionary *dic = [transArray objectAtIndex:i];

//                NSString *idString = [self checkNULLstring:[dic objectForKey:@"id"]];

//                NSString *isRead = [NSString stringWithFormat:@"%@",[NSNumber numberWithBool:NO]];

//                

//                res = [db executeUpdate:sql,idString,isRead];

//            }

//        }

//        @catch (NSException *exception) {

//            [db rollback];

//        }

//        @finally {

//            if (!isRollBack) {

//                res = YES ;

//                [db commit];

//            }

//        }

        

        [db close];

    }

    returnYES;

}

 

- (BOOL)inserAlreadyReadStateWithIdString:(NSString *)idString{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL res = NO ;

    if ([db open]) {

        NSString * sql = @"insert or replace into ReadStateTable (id,isRead) values(?,?)" ;

        NSString *isRead = [NSStringstringWithFormat:@"%@",[NSNumbernumberWithBool:YES]];

        

        res = [db executeUpdate:sql,idString,isRead];

        

        [db close];

    }

    

    

    return res ;

}

 

#pragma mark Video Read

- (BOOL)videoQueryIsReadOfReadStateTable:(NSString *)idString{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL isExit = NO ;

    

    if ([db open]) {

        NSString * sql = [NSStringstringWithFormat:@"select * from VideoReadStateTable where id = '%@' " , idString ];

//        NSLog(@"sql is %@" , sql);

        

        FMResultSet * rs = [db executeQuery:sql];

        

        while ([rs next]) {

            NSDictionary *dic = [rs resultDictionary] ;

            isExit = [[dic objectForKey:@"isRead"] boolValue] ;

            break;

        }

        [db close];

    }

    

    return isExit ;

}

 

- (BOOL)videoInsertReadStateTableData:(NSArray *)transArray{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL res = NO ;

    int nums = [transArray count];

    if ([db open]) {

 

        NSString * sql = @"insert into VideoReadStateTable (id,isRead) (SELECT '%@','%@' FROM VideoReadStateTable u2  JOIN( SELECT COUNT(*) num FROM VideoReadStateTable u1 WHERE u1.id = '%@') u3 ON u3.num < 1  LIMIT 1)" ;

        for( int i =0 ;i<nums ;i++ ){

            NSDictionary *dic = [transArray objectAtIndex:i];

            NSString *idString = [self checkNULLstring:[dic objectForKey:@"id"]];

            

            NSString *isRead = [NSString stringWithFormat:@"%@",[NSNumber numberWithBool:NO]];

            sql = [NSString stringWithFormat:sql,idString,isRead,idString];

            res = [db executeUpdate:sql];

        }

//        [db beginTransaction];

//        BOOL isRollBack = NO;

//        @try {

//            //CREATE TABLE 'ReadStateTable' (id text identity primary key , 'isRead' VARCHAR)

//            NSString * sql = @"insert into VideoReadStateTable (id,isRead) values(?,?)" ;

//            for( int i =0 ;i<nums ;i++ ){

//                NSDictionary *dic = [transArray objectAtIndex:i];

//                NSString *idString = [self checkNULLstring:[dic objectForKey:@"id"]];

//                NSString *isRead = [NSString stringWithFormat:@"%@",[NSNumber numberWithBool:NO]];

//                

//                res = [db executeUpdate:sql,idString,isRead];

//            }

//        }

//        @catch (NSException *exception) {

//            [db rollback];

//        }

//        @finally {

//            if (!isRollBack) {

//                res = YES ;

//                [db commit];

//            }

//        }

        

        [db close];

    }

    returnYES;

}

 

- (BOOL)videoInserAlreadyReadStateWithIdString:(NSString *)idString{

    FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];

    BOOL res = NO ;

    if ([db open]) {

        NSString * sql = @"insert or replace into VideoReadStateTable (id,isRead) values(?,?)" ;

        NSString *isRead = [NSStringstringWithFormat:@"%@",[NSNumbernumberWithBool:YES]];

        

        res = [db executeUpdate:sql,idString,isRead];

        

        [db close];

    }

    

    

    return res ;

}

posted on 2014-02-11 17:15  阿里海瑟要  阅读(185)  评论(0编辑  收藏  举报