//
// 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