GXFFunctionDB.m

//
//  GXFFunctionDB.m
//  XFFruit
//
//  Created by freecui on 15/8/27.
//  Copyright (c) 2015年 Xummer. All rights reserved.
//

#define DATABASE_PATH [[NSSearchPathForDirectoriesInDomains(NSApplicationSupportDirectory, NSUserDomainMask, YES) lastObject]stringByAppendingString:@"function.db"]
//#define DATABASE_PATH [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]stringByAppendingString:@"/weChat.db"]
//FMDB
//#define FMDBQuickCheck(SomeBool) { if (!(SomeBool)) { NSLog(@"Failure on line %d", __LINE__); abort(); } }

#import "GXFFunctionDB.h"

@interface GXFFunctionDB ()
@property (nonatomic, strong) FMDatabase *dataBase;

@end
@implementation GXFFunctionDB

+ (GXFFunctionDB *)sharedInstance {
    static GXFFunctionDB *functionDb = nil;
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        functionDb = [[GXFFunctionDB alloc]init];
    });
    return functionDb;
}
- (BOOL)openDb {
    // NSSearchPathForDirectoriesInDomains(NSApplicationSupportDirectory, NSUserDomainMask, YES);
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSApplicationSupportDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dbPath = [path stringByAppendingString:@"function.db"];
    self.dataBase = [FMDatabase databaseWithPath:dbPath];
    CLog(@"dbPath = %@",dbPath);
    BOOL isOpen = [_dataBase open];
    if (!isOpen) {
         NSLog(@"数据库打开失败");
    }
        return isOpen;
    
}
- (void)createTables {
    if ([self openDb]) {
        [self functionTableCreate];
        [self displayFunctionTableCreate];
    }
}




- (BOOL)functionTableCreate {
//    if (![_dataBase open]) {
//        [self openDb];
//    }
    
 
    //Id INTEGER NOT NULL PRIMARY KEY UNIQUE CONFLICT REPLACE
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS function (Id INTEGER PRIMARY KEY NOT NULL UNIQUE ,name VARCHAR,caption VARCHAR,hasPermission INTEGER,isSelected INTEGER) ";//CONFLICT REPLACE
    BOOL work = [_dataBase executeUpdate:createTableSql];
    
    return work;
}
- (BOOL)displayFunctionTableCreate {
    if (![_dataBase open]) {
        [self openDb];
    }
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS displayFunction (Id INTEGER PRIMARY KEY AUTOINCREMENT,functionId INTEGER,functionName VARCHAR ,functionImgName VARCHAR,functionSmallImgName VARCHAR,  isSelected INTEGER,functionItemTag INTEGER )";
    BOOL work = [_dataBase executeUpdate:createTableSql];
    
    return work;
}

//+(BOOL)checkTableCreatedInDb:(FMDatabase *)db
//{
//    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS function (Id INTEGER PRIMARY KEY NOT NULL UNIQUE ,name VARCHAR,caption VARCHAR,hasPermission INTEGER,isSelected INTEGER) ";//CONFLICT REPLACE
//    BOOL worked = [db executeUpdate:createTableSql];
//    FMDBQuickCheck(worked);
//    return worked;
//}
//插入数据：从最初的json文件读入时 没有任何权限跟所有的功能都没有选中
- (BOOL)insertFunction: (GXFFunction *)function {
//    FMDatabase *db = [FMDatabase databaseWithPath:DATABASE_PATH];
//    if (![db open]) {
//        NSLog(@"数据库打开失败");
//        return YES;
//    };
//    [GXFFunctionDB checkTableCreatedInDb:db];
    
    NSString *inserSql = @"INSERT INTO  function (Id,name,caption,hasPermission,isSelected) VALUES(?,?,?,?,?)";
    BOOL work = [_dataBase executeUpdate:inserSql,@(function.Id),function.name,function.caption,@(function.hasPermission) ,@(function.isSelected)];//@(120),@"gg",@"fff",@(1),@(1)];
    
   // [db close];
    return work;
}


//查找一条数据
- (GXFFunction *)functionSelectWithId: (NSInteger)Id{
    GXFFunction *function = [[GXFFunction alloc]init];
    FMResultSet *rs = [_dataBase executeQuery:@"SELECT * FROM  function WHERE Id = ? ",@(Id)];
    while ([rs next]) {
        function.Id = [rs intForColumnIndex:0];
        function.name = [rs stringForColumnIndex:1];
        function.caption = [rs stringForColumnIndex:2];
        function.hasPermission = [rs intForColumnIndex:3];
        function.isSelected = [rs intForColumnIndex:4];
        break;
    }
    return function;

}
//更新权限
- (BOOL)updateFunctionHasPermissionWithId: (GXFFunction *)function{
    //GXFFunction *function = [self functionSelectWithId:Id];
    BOOL work = [_dataBase executeUpdate:@"UPDATE function SET hasPermission = ? where Id = ?",@(function.hasPermission),@(function.Id)];
    return work;
}
//更新是否选中
- (BOOL)updateFunctionIsSelectedWithId: (GXFFunction *)function{
    BOOL work = [_dataBase executeUpdate:@"UPDATE function SET isSelected = ? where Id = ?",function.isSelected,function.Id];
    return work;
}
//返回全部原始数据的function
- (NSArray *)originFunctions {
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM function"];
    while ([rs next]) {
        GXFFunction *function = [[GXFFunction alloc]init];
        function.Id = [rs intForColumn:@"Id"];
        [muArr addObject:function];
    }
    
    return muArr;
}
//返回全部有权限的function
- (NSArray *)functionsHasPermission {
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM function WHERE hasPermission = ?",[NSNumber numberWithBool:1]];
    while ([rs next]) {
        GXFFunction *function = [[GXFFunction alloc]init];
        function.Id = [rs intForColumn:@"Id"];
        [muArr addObject:function];
    }
    
    return muArr;
}
- (NSArray *)p_functionsIdPermission {
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT Id FROM function WHERE hasPermission = ?",[NSNumber numberWithBool:1]];
    while ([rs next]) {
       NSInteger Id = [rs intForColumn:@"Id"];
        [muArr addObject:@(Id)];
    }
    
    return muArr;
}
//既有权限又被选择的
- (NSArray *)functionsHasPermissionAndIsSelected{
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM function WHERE hasPermission = ?,isSelected = ?",[NSNumber numberWithBool:1],[NSNumber numberWithBool:1]];
    while ([rs next]) {
        GXFFunction *function = [[GXFFunction alloc]init];
        function.Id = [rs intForColumn:@"Id"];
        [muArr addObject:function];
    }
    
    return muArr;
}
- (NSArray *)p_functionsHasPermissionAndIsSelected{
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT Id FROM function WHERE hasPermission = ?,isSelected = ?",[NSNumber numberWithBool:1],[NSNumber numberWithBool:1]];
    while ([rs next]) {
        NSInteger Id = [rs intForColumn:@"Id"];
        [muArr addObject:@(Id)];
    }
    
    return muArr;
}















#pragma displayFunction
- (BOOL)insertDisplayFunction: (GXFDisplayFunction *)displayFunction {
    
    NSString *inserSql = @"INSERT INTO  displayFunction(functionId,functionName,functionImgName,functionSmallImgName,functionItemTag) VALUES(?,?,?,?,?)";
    BOOL work = [_dataBase executeUpdate:inserSql,@(displayFunction.functionId),displayFunction.functionName,displayFunction.functionImgName,displayFunction.functionSmallImgName,@(displayFunction.functionItemTag)];
    
    return work;
}
- (GXFDisplayFunction *)selectDisplayFunctionByFunctionId: (NSInteger)functionId{
    GXFDisplayFunction *displayFunction = [[GXFDisplayFunction alloc]init];
    FMResultSet *rs = [_dataBase executeQuery:@"SELET * FROM  displayFunction WHERE functionId = ? ",@(functionId)];
    while ([rs next]) {
        displayFunction.functionId = [rs intForColumn:@"functionId"];
        displayFunction.functionName = [rs stringForColumn:@"functionName"];
        displayFunction.functionImgName = [rs stringForColumn:@"functionImgName"];
        displayFunction.functionSmallImgName = [rs stringForColumn:@"functionSmallImgName"];
        displayFunction.functionItemTag = [rs intForColumn:@"functionItemTag"];
        break;
    }
    return displayFunction;
}
//更新是否选中
- (BOOL)updateDisplayFunctionIsSelectedWithId: (GXFDisplayFunction *)displayFunction{
    BOOL work = [_dataBase executeUpdate:@"UPDATE displayFunction SET isSelected = ? where functionId = ?",@(displayFunction.isSelected),@(displayFunction.functionId)];
    return work;
}
#pragma 有权限的displayfunctions
//待改正，最好的办法是两张表关联起来查找但是我还没做到
-(NSArray *)displayFunctionsHasPermission{
    
    NSMutableArray *muArr = [NSMutableArray array];
    
    NSArray *hasPermission = [self p_functionsIdPermission];
    for (int count = 0; count < hasPermission.count; count ++) {
        FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM displayFunction WHERE functionId = ?",hasPermission[count] ];
        while ([rs next]) {
            GXFDisplayFunction *displayFunction = [self p_resultSetDisplayFunction:rs];
            [muArr addObject:displayFunction];

        }
    }
    return muArr;
}

//默认配置显示功能  初始配置为
- (NSArray *)defaultdisplayFunctions {
    NSMutableArray *arr = [NSMutableArray arrayWithArray:[self displayFunctionsHasPermission]];
    for (int count = 0; count < arr.count; count ++) {
        if ((count % 2) || (count == 0)) {
            GXFDisplayFunction *displayFunction = (GXFDisplayFunction *)arr[count];
            displayFunction.isSelected = YES;
            [self updateDisplayFunctionIsSelectedWithId:displayFunction];
            [arr replaceObjectAtIndex:count withObject:displayFunction];
        }
    }
    
    return arr;
}

//返回全部原始数据的function
- (NSArray *)originDisplayFunctions {
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM displayFunction"];
    while ([rs next]) {
        GXFDisplayFunction *displayFunction = [self p_resultSetDisplayFunction:rs];
        [muArr addObject:displayFunction];
    }
    
    return muArr;
}

- (GXFDisplayFunction *)p_resultSetDisplayFunction: (FMResultSet *)rs {
    GXFDisplayFunction *displayFunction = [[GXFDisplayFunction  alloc]init];
    displayFunction.functionId = [rs intForColumn:@"functionId"];
    displayFunction.functionName = [rs stringForColumn:@"functionName"];
    displayFunction.functionImgName = [rs stringForColumn:@"functionImgName"];
    displayFunction.functionSmallImgName = [rs stringForColumn:@"functionSmallImgName"];
    displayFunction.isSelected = [rs intForColumn:@"isSelected"];
    displayFunction.functionItemTag = [rs intForColumn:@"functionItemTag"];
    return displayFunction;
}
//返回被选中的
- (NSArray *)isSelectedDisplayFunctions {
    NSMutableArray *muArr = [NSMutableArray array];
    FMResultSet *rs = [_dataBase executeQuery: @"SELECT * FROM displayFunction WHERE isSelected = ?",@(1)];
    while ([rs next]) {
        GXFDisplayFunction *displayFunction = [self p_resultSetDisplayFunction:rs];
        [muArr addObject:displayFunction];
    }
    
    return muArr;
}
@end