#import <Foundation/Foundation.h>
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "DeviceInfo.h"
#import "DeviceGroupInfo.h"
#import "TimeingInfo.h"
#import "UserInfo.h"
#define DeviceTable_SQL @"DeviceInfoTable"
#define SQL_ContentKey @"content"
@interface SQLdataManger : NSObject
+ (SQLdataManger*)instance;
+ (void)clearInstance;
//按照给定的语句查询
- (NSArray *)queryOtherDataWithSql:(NSString *)sql;
//读取所有的设备
- (NSArray *)queryDeviceInfoTable;
- (DeviceInfo *)queryDataWithMAC:(NSString *)MAC;
//插入单条设备指令
-(BOOL)insertOrReqlaceSignalDevice:(DeviceInfo *)devInfo;
- (BOOL)deleteTableDataWithMAC:(NSString *)MAC;
//用户表
- (NSDictionary *)queryUserData:(NSString *)userName;
-(BOOL)deleteUserWithName:(NSString *)userName;
-(BOOL)insertOrReqlaceUserDataWithName:(UserInfo *)userInfo;
-(NSMutableArray *)allGroupNames;//获取所有的控制组合
-(BOOL)deleteGroupWith:(NSString *)groupName;//删除组合
-(BOOL)insertGroupWith:(DeviceGroupInfo *)groupName;//添加组合
//通过sql查询数据
-(NSMutableArray *)executeQueryData:(NSString *)sql;
//执行SQL指令
-(BOOL)executeSQL:(NSString *)sql;
-(BOOL)insertOrReqlaceTimeDataWithInfo:(TimeingInfo *)info;
@end
//
// SQLdataManger.m
// SSProduct
//
// Created by Showsoft_002 on 13-8-20.
// Copyright (c) 2013年 Showsoft_002. All rights reserved.
//
#import "SQLdataManger.h"
static SQLdataManger * instance=nil;
#define SQLfileName @"__MACOSX"
//#define SQLfileName @"ShowsoftSQL.sql"
#define SQLAESEncry
#define AESEncry_KEY @"showsoftAESEncryKEYSSProduct0123"
@interfaceSQLdataManger(){
NSString *sqlDataPath;
}
@end
@implementation SQLdataManger
+(SQLdataManger*)instance
{
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;
}
}
- (void)createTable {
NSFileManager * fileManager = [NSFileManagerdefaultManager];
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *paths = [[path objectAtIndex:0] stringByAppendingPathComponent:SQLfileName];
// [fileManager removeItemAtPath:paths error:nil];
sqlDataPath = paths;
if ([fileManager fileExistsAtPath:paths] == NO) {
// create it
FMDatabase * db = [FMDatabase databaseWithPath:paths];
if ([db open]) {
//建立一张表,用于存设备信息
NSString * sql = @"CREATE TABLE ‘DeviceInfoTable‘ (MAC text identity primary key , ‘ip‘ VARCHAR, ‘deviceName‘ VARCHAR,‘icoType‘ VARCHAR,‘addTime‘ VARCHAR)";
BOOL res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error when creating db table");
} else {
NSLog(@"succ to creating db table");
}
//创建组合表，存储用户组合控制信息
sql = @"CREATE TABLE ‘DeviceGroupTable‘ (groupName text identity primary key,‘devices‘ VARCHAR,‘addTime‘ VARCHAR ,‘openState‘ VARCHAR)";
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error when creating db table");
} else {
NSLog(@"succ to creating db table");
}
//创建定时管理表
sql = @"CREATE TABLE ‘DeviceTimeTable‘ (oneKey text dentity primary key,‘mac‘ VARCHAR, ‘toTime‘ VARCHAR,‘repeatWeek‘ VARCHAR ,‘action‘ VARCHAR,‘timeType‘ VARCHAR ,‘timeIndex‘ VARCHAR,‘addTime‘ VARCHAR)";
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error when creating db table");
} else {
NSLog(@"succ to creating db table");
}
//创建用户管理表
sql = @"CREATE TABLE ‘UserDataTable‘ (userName text dentity primary key,‘displayName‘ VARCHAR, ‘passWord‘ VARCHAR,‘clientMAC‘ VARCHAR ,‘clientID‘ VARCHAR,‘clientName‘ VARCHAR ,‘addTime‘ 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 ;
}
- (NSArray *)queryDeviceInfoTable{
////@"CREATE TABLE ‘UnitsRoomInfo‘ (onlyOne text identity primary key , ‘BuildingName‘ VARCHAR, ‘DanYuanName‘ VARCHAR,‘UnitsName‘ VARCHAR,‘FloorNumName‘ VARCHAR,‘AreaBuild‘ VARCHAR,‘AreaTaoNei‘ VARCHAR,‘TotalPrice‘ VARCHAR,‘SaleState‘ VARCHAR )";
NSMutableArray *mutableArray = [[NSMutableArrayalloc]init];
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"select * from DeviceInfoTable" ];
NSLog(@"sql is %@" , sql);
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
[mutableArray addObject:[self deviceInfoForm:dic]];
}
[db close];
}
return mutableArray ;
}
- (NSArray *)queryOtherDataWithSql:(NSString *)sql{
NSMutableArray *mutableArray = [[NSMutableArray alloc]init] ;
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
// rtDic = [NSDictionary dictionaryWithDictionary:dic];
[mutableArray addObject:[self deviceInfoForm:dic]];
}
[db close];
}
NSArray *sortedArray = [mutableArray sortedArrayUsingComparator: ^(DeviceInfo *obj1, DeviceInfo *obj2) {
NSString *r1String = obj1.addTime;
NSString *r2String = obj2.addTime;
r1String = [r1String stringByReplacingOccurrencesOfString:@"-"withString:@""];
r1String = [r1String stringByReplacingOccurrencesOfString:@" "withString:@""];
r2String = [r2String stringByReplacingOccurrencesOfString:@"-"withString:@""];
r2String = [r2String stringByReplacingOccurrencesOfString:@" "withString:@""];
int value1 = [r1String integerValue];
int value2 = [r2String integerValue];
if (value1 > value2) {
return (NSComparisonResult)NSOrderedDescending;
}
if (value1 < value2) {
return (NSComparisonResult)NSOrderedAscending;
}
return (NSComparisonResult)NSOrderedSame;
}];
return sortedArray ;
}
-(BOOL)insertOrReqlaceSignalDevice:(DeviceInfo *)devInfo{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open] ) {
NSString * sql = @"insert or replace into DeviceInfoTable (MAC,ip,deviceName,icoType,addTime) values(?,?,?,?,?)" ;
NSString *MAC = [self checkNULLstring:devInfo.mac];
NSString *ip = [self checkNULLstring:devInfo.ip];
NSString *deviceName = [self checkNULLstring:devInfo.deviceName];
NSString *icoType =[NSString stringWithFormat:@"%d",devInfo.icoType];
// NSString *groupName=[self checkNULLstring:devInfo.parenGroupName];
NSString *addTime = [NSStringstringWithFormat:@"%d" , (int)[[NSDatedate] timeIntervalSince1970] ];
NSLog(@"addTimeString is %@" , addTime );
res = [db executeUpdate:sql,MAC,ip,deviceName,icoType,addTime];
[db close];
}
return res;
}
- (BOOL)deleteTableDataWithMAC:(NSString *)MAC{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"delete from DeviceInfoTable where MAC =‘%@‘" , MAC];
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error to insert data");
} else {
NSLog(@"succ to insert data");
}
[db close];
}
return res ;
}
- (DeviceInfo *)queryDataWithMAC:(NSString *)MAC{
NSMutableArray *mutableArray = [[NSMutableArrayalloc]init];
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"select * from DeviceInfoTable where MAC = ‘%@‘" , MAC];
NSLog(@"sql is %@" , sql);
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
[mutableArray addObject:dic];
}
[db close];
}
DeviceInfo *info=[self deviceInfoForm:[mutableArray objectAtIndex:0]];
return info ;
}
-(NSMutableArray *)allGroupNames
{
NSMutableArray *mutableArray = [[NSMutableArrayalloc]init];
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"select * from DeviceGroupTable" ];
NSLog(@"sql is %@" , sql);
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
DeviceGroupInfo *groupInfo=[[DeviceGroupInfo alloc]init];
groupInfo.groupName=[dic objectForKey:@"groupName"];
groupInfo.devMACList=[[NSMutableArrayalloc]initWithArray:[[dic objectForKey:@"devices"] componentsSeparatedByString:@"|||"]];
groupInfo.addTime=[dic objectForKey:@"addTime"];
groupInfo.openState=[dic objectForKey:@"openState"];
[mutableArray addObject:groupInfo];
}
[db close];
}
return mutableArray ;
}
-(BOOL)deleteGroupWith:(NSString *)groupName//删除组合
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"delete from DeviceGroupTable where groupName =‘%@‘" , groupName];
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error to insert data");
} else {
NSLog(@"succ to insert data");
}
[db close];
}
return res ;
}
-(BOOL)insertGroupWith:(DeviceGroupInfo *)groupName//添加组合
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open] ) {
NSString * sql = @"insert or replace into DeviceGroupTable (groupName,devices,addTime,openState) values(?,?,?,?)" ;
NSString *groupNameT = [self checkNULLstring:groupName.groupName];
NSString *addTime = [NSStringstringWithFormat:@"%d" , (int)[[NSDatedate] timeIntervalSince1970] ];
NSMutableString *devs=[[NSMutableStringalloc]init];
for (int i=0; i<groupName.devMACList.count; i++) {
[devs appendFormat:@"%@|||",[groupName.devMACList objectAtIndex:i]];
}
// NSLog(@"addTimeString is %@" , addTime );
if(devs.length<1)
[devs appendString:@""];
NSString *openState=[self checkNULLstring:groupName.openState];
res = [db executeUpdate:sql,groupNameT,devs,addTime,openState];
[db close];
}
return res;
}
//用户表
- (NSDictionary *)queryUserData:(NSString *)userName
{
// NSMutableArray *mutableArray = [[NSMutableArray alloc]init];
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"select * from UserDataTable where userName=‘%@‘",userName];
NSLog(@"sql is %@" , sql);
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
return dic;
// [mutableArray addObject:[self deviceInfoForm:dic]];
}
[db close];
}
returnnil ;
}
-(BOOL)deleteUserWithName:(NSString *)userName
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open]) {
NSString * sql = [NSStringstringWithFormat:@"delete from UserDataTable where userName =‘%@‘" , userName];
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error to insert data");
} else {
NSLog(@"succ to insert data");
}
[db close];
}
return res ;
}
-(BOOL)insertOrReqlaceUserDataWithName:(UserInfo *)userInfo
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open] ) {
NSString * sql = @"insert or replace into UserDataTable (userName,displayName,passWord,clientMAC,clientID,clientName,addTime) values(?,?,?,?,?,?,?)" ;
NSString *userName = [self checkNULLstring:userInfo.userName];
NSString *displayName = [self checkNULLstring:userInfo.displayName];
NSString *passWord = [self checkNULLstring:userInfo.passWord];
NSString *clientMAC = [self checkNULLstring:userInfo.clientMAC];
NSString *clientID = [self checkNULLstring:userInfo.clientID];
NSString *clientName = [self checkNULLstring:userInfo.clientName];
NSString *addTime = [NSStringstringWithFormat:@"%d" , (int)[[NSDatedate] timeIntervalSince1970] ];
res = [db executeUpdate:sql,userName,displayName,passWord,clientMAC,clientID,clientName,addTime];
[db close];
}
return res;
}
//通过sql查询数据
-(NSMutableArray *)executeQueryData:(NSString *)sql
{
NSMutableArray *mutableArray = [[NSMutableArray alloc]init] ;
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
if ([db open]) {
FMResultSet * rs = [db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary] ;
[mutableArray addObject:dic];
}
[db close];
}
return mutableArray;
}
//执行SQL指令
-(BOOL)executeSQL:(NSString *)sql
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
if ([db open]) {
res = [db executeUpdate:sql];
if (!res) {
NSLog(@"error to insert data");
} else {
NSLog(@"succ to insert data");
}
[db close];
}
return res ;
}
-(BOOL)insertOrReqlaceTimeDataWithInfo:(TimeingInfo *)info
{
FMDatabase * db = [FMDatabasedatabaseWithPath:sqlDataPath];
BOOL res = NO ;
NSString * sql = @"insert or replace into DeviceTimeTable (oneKey,mac,toTime,repeatWeek,action,timeType,timeIndex,addTime) values(?,?,?,?,?,?,?,?)" ;
NSString *addTime = [NSStringstringWithFormat:@"%d" , (int)[[NSDatedate] timeIntervalSince1970] ];
info.addTime=addTime;
if ([db open]) {
res = [db executeUpdate:sql,[NSString stringWithFormat:@"%@_%d",info.mac,info.timeIndex],info.mac,info.toTime,info.repeatWeek,[NSString stringWithFormat:@"%d",info.action],[NSString stringWithFormat:@"%d",info.timeType],[NSString stringWithFormat:@"%d",info.timeIndex],info.addTime];
if (!res) {
NSLog(@"error to insert data");
} else {
NSLog(@"succ to insert data");
}
[db close];
}
return res ;
}
-(DeviceInfo *)deviceInfoForm:(NSDictionary *)dic
{
DeviceInfo *info=[[DeviceInfoalloc]init];
info.mac=[dic objectForKey:@"MAC"];
info.ip=[dic objectForKey:@"ip"];
info.deviceName=[dic objectForKey:@"deviceName"];
info.icoType=[[dic objectForKey:@"icoType"]integerValue];
info.addTime=[dic objectForKey:@"addTime"];
// info.parenGroupName=[dic objectForKey:@"groupName"];
return info;
}
@end