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];
    ICRUserUtil *user = [ICRUserUtil sharedInstance];
    NSString *dbPath ;//= [path stringByAppendingString:@"function.db"];
    if (user.userCode) {
        dbPath = [NSString stringWithFormat:@"%@%@function.db",path,user.userCode];
    } else {
        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];
    }
}


- (void)deleteTables {
    if ([self openDb]) {
        [self functionTableDelete];
        [self displayFunctionTableDelete];
    }

}
- (BOOL)functionTableDelete {
    NSString *delSql = @"delete from function";
    BOOL work = [_dataBase executeQuery:delSql];
    return work;
}
- (BOOL)displayFunctionTableDelete {
    NSString *delSql = @"delete from displayFunction";
    BOOL work = [_dataBase executeQuery:delSql];
    return work;
}
- (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,hasPermission 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,hasPermission,functionItemTag) VALUES(?,?,?,?,?,?)";
    BOOL work = [_dataBase executeUpdate:inserSql,@(displayFunction.functionId),displayFunction.functionName,displayFunction.functionImgName,displayFunction.functionSmallImgName,@(0),@(displayFunction.functionItemTag)];
    
    return work;
}
//- (GXFDisplayFunction *)upDatePermissionDisplayFunctionByFunctionId: (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)updateDisplayFunctionPermissionWithId: (GXFDisplayFunction *)displayFunction{
    BOOL work = [_dataBase executeUpdate:@"UPDATE displayFunction SET hasPermission = ? where functionId = ?",@(displayFunction.hasPermission),@(displayFunction.functionId)];
    return work;
}


//更新是否选中
- (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];
    
    BOOL hasSeeOption1 = NO;
    BOOL hasSeeOption3 = NO;
    BOOL hasSeeOption4 = NO;
    BOOL hasSeeOption5 = NO;
    BOOL hasSeeOption6 = NO;
    BOOL hasSeeOption7 = NO;
    BOOL hasSeeOption8 = NO;
    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];
            displayFunction.hasPermission = YES;
            [self updateDisplayFunctionPermissionWithId:displayFunction];
            [muArr addObject:displayFunction];
            if (displayFunction.functionId == 500102 && hasSeeOption1 == NO) {
                hasSeeOption1 = YES;
            }
            if (displayFunction.functionId == 500103 && hasSeeOption1 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption1 = NO;
            }
            //"采购通知"
            if (displayFunction.functionId == 500302 && hasSeeOption3 == NO) {
                hasSeeOption3 = YES;
            }
            if (displayFunction.functionId == 500303 && hasSeeOption3 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption3 = NO;
            }
            
            //采购单???? 有三个："全部查看权"、"本人查看权"、 "供应商查看权"
            if (displayFunction.functionId == 500402 && hasSeeOption4 == NO) {
                hasSeeOption4 = YES;
            }
            if (displayFunction.functionId == 500403 && hasSeeOption4 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption4 = YES;
            }
            
            //"发运单"
            if (displayFunction.functionId == 500502 && hasSeeOption5 == NO) {
                hasSeeOption5 = YES;
            }
            if (displayFunction.functionId == 500503 && hasSeeOption5 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption5 = NO;
            }
            
            //"转运单"
            if (displayFunction.functionId == 500602 && hasSeeOption6 == NO) {
                hasSeeOption6 = YES;
            }
            if (displayFunction.functionId == 500603 && hasSeeOption6 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption6 = NO;
            }
            //"加工单"
            if (displayFunction.functionId == 500702 && hasSeeOption7 == NO) {
                hasSeeOption7 = YES;
            }
            if (displayFunction.functionId == 500703 && hasSeeOption7 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption7 = NO;
            }

            //"收货单"
            if (displayFunction.functionId == 500802 && hasSeeOption8 == NO) {
                hasSeeOption8 = YES;
            }
            if (displayFunction.functionId == 500803 && hasSeeOption8 == YES) {
                [muArr removeObject:displayFunction];
                hasSeeOption8 = NO;
            }
            

            continue;
        }                    
    }
        
    return muArr;
}




////把显示重复功能的去掉
//- (NSArray *)siftDuplicationDisplayFunctionsButton {
//    NSMutableArray  *allDisplayFunctionsArr = [NSMutableArray arrayWithArray:[self displayFunctionsHasPermission]];
//    NSMutableArray *muArr = [NSMutableArray array];
//    
//    NSArray *hasPermission = [self p_functionsIdPermission];
//    BOOL hasSeeOption1 = NO;
//    
//     BOOL hasSeeOption3 = NO;
//     BOOL hasSeeOption4 = NO;
//     BOOL hasSeeOption5 = NO;
//     BOOL hasSeeOption6 = NO;
//     BOOL hasSeeOption7 = NO;
//    
//    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];
//            //"行情反馈",
//            if (displayFunction.functionId == 500102 && hasSeeOption1 == NO) {
//                hasSeeOption1 = YES;
//            }
//            if (displayFunction.functionId == 500103 && hasSeeOption1 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption1 = NO;
//            }
//            
//            //"采购通知"
//            if (displayFunction.functionId == 500302 && hasSeeOption3 == NO) {
//                hasSeeOption3 = YES;
//            }
//            if (displayFunction.functionId == 500303 && hasSeeOption3 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption3 = NO;
//            }
//            
//            //采购单???? 有三个："全部查看权"、"本人查看权"、 "供应商查看权"
//            if (displayFunction.functionId == 500402 && hasSeeOption4 == NO) {
//                hasSeeOption4 = YES;
//            }
//            if (displayFunction.functionId == 500403 && hasSeeOption4 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption4 = YES;
//            }
////            if (displayFunction.functionId == 500403 && hasSeeOption4 == NO) {
////                //[muArr removeObject:displayFunction];
////                hasSeeOption4 = YES;
////            }
////            if (displayFunction.functionId == 500403 && hasSeeOption4 == YES) {
////                [muArr removeObject:displayFunction];
////                hasSeeOption4 = YES;
////            }
////            if (displayFunction.functionId == 500404 && hasSeeOption4 == YES) {
////                [muArr removeObject:displayFunction];
////                hasSeeOption4 = YES;
////            }
//            
//            //"发运单"
//            if (displayFunction.functionId == 500502 && hasSeeOption5 == NO) {
//                hasSeeOption5 = YES;
//            }
//            if (displayFunction.functionId == 500503 && hasSeeOption5 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption5 = NO;
//            }
//            
//            //"转运单"
//            if (displayFunction.functionId == 500602 && hasSeeOption6 == NO) {
//                hasSeeOption6 = YES;
//            }
//            if (displayFunction.functionId == 500603 && hasSeeOption6 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption6 = NO;
//            }
//            //"加工单"
//            if (displayFunction.functionId == 500702 && hasSeeOption7 == NO) {
//                hasSeeOption7 = YES;
//            }
//            if (displayFunction.functionId == 500703 && hasSeeOption7 == YES) {
//                [muArr removeObject:displayFunction];
//                hasSeeOption7 = NO;
//            }
//
//        }
//    }
//    
//    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"];
    NSMutableArray *muArr = [NSMutableArray arrayWithArray:[self displayFunctionsHasPermission]];
    BOOL hasSeeOption1 = NO;
   
    BOOL hasSeeOption3 = NO;
    BOOL hasSeeOption4 = NO;
    BOOL hasSeeOption5 = NO;
    BOOL hasSeeOption6 = NO;
    BOOL hasSeeOption7 = NO;
    BOOL hasSeeOption8 = NO;
    for(GXFDisplayFunction *displayFunction in muArr){
//    while ([rs next]) {
//        GXFDisplayFunction *displayFunction = [self p_resultSetDisplayFunction:rs];
//        [muArr addObject:displayFunction];
    
        
        //"行情反馈",
        if (displayFunction.functionId == 500102 && hasSeeOption1 == NO) {
            hasSeeOption1 = YES;
        }
        if (displayFunction.functionId == 500103 && hasSeeOption1 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption1 = NO;
        }
        
        //"采购通知"
        if (displayFunction.functionId == 500302 && hasSeeOption3 == NO) {
            hasSeeOption3 = YES;
        }
        if (displayFunction.functionId == 500303 && hasSeeOption3 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption3 = NO;
        }
        
        //采购单???? 有三个："全部查看权"、"本人查看权"、 "供应商查看权"
        if (displayFunction.functionId == 500402 && hasSeeOption4 == NO) {
            hasSeeOption4 = YES;
        }
        if (displayFunction.functionId == 500403 && hasSeeOption4 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption4 = YES;
        }
        
        //"发运单"
        if (displayFunction.functionId == 500502 && hasSeeOption5 == NO) {
            hasSeeOption5 = YES;
        }
        if (displayFunction.functionId == 500503 && hasSeeOption5 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption5 = NO;
        }

        //"转运单"
        if (displayFunction.functionId == 500602 && hasSeeOption6 == NO) {
            hasSeeOption6 = YES;
        }
        if (displayFunction.functionId == 500603 && hasSeeOption6 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption6 = NO;
        }
        //"加工单"
        if (displayFunction.functionId == 500702 && hasSeeOption7 == NO) {
            hasSeeOption7 = YES;
        }
        if (displayFunction.functionId == 500703 && hasSeeOption7 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption7 = NO;
        }
        
        //"收货单"
        if (displayFunction.functionId == 500802 && hasSeeOption8 == NO) {
            hasSeeOption8 = YES;
        }
        if (displayFunction.functionId == 500803 && hasSeeOption8 == YES) {
            [muArr removeObject:displayFunction];
            hasSeeOption8 = NO;
        }


    }
    
    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.hasPermission = [rs intForColumn:@"hasPermission"];
    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