jixin_new/shiku_im/model/JXLabelObject.m

//
//  JXLabelObject.m
//  shiku_im
//
//  Created by p on 2018/6/21.
//  Copyright © 2018年 Reese. All rights reserved.
//

#import "JXLabelObject.h"
#import "FMDatabase.h"
#import "FMResultSet.h"

@implementation JXLabelObject


static JXLabelObject *sharedLabel;

+(JXLabelObject*)sharedInstance{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        sharedLabel=[[JXLabelObject alloc]init];
    });
    return sharedLabel;
}

-(id)init{
    self = [super init];
    if(self){
        _tableName = @"label";
    }
    return self;
}

// 获取所有标签
-(NSMutableArray*)fetchAllLabelsFromLocal {
    NSString* sql = @"select * from label";
    
    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
    
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
        JXLabelObject *label=[[JXLabelObject alloc] init];
        [self labelFromDataset:label rs:rs];
        [resultArr addObject:label];
    }
    [rs close];

    return resultArr;
}

- (NSMutableArray *)fetchAllRecordsFromLocal {
    NSString* sql = @"select * from groupHelperRecord order by id desc";
    
    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
    
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
        JXLabelObject *label=[[JXLabelObject alloc] init];
        [self recordsFromDataset:label rs:rs];
        [resultArr addObject:label];
    }
    [rs close];
    
    return resultArr;
}


- (BOOL)checkGroupHelperRecordTableCreatedInDb:(FMDatabase *)db {
    
    NSString *createStr=[NSString stringWithFormat:@"CREATE  TABLE  IF NOT EXISTS 'groupHelperRecord' ('id' INTEGER PRIMARY KEY, 'userId' VARCHAR, 'userIds' VARCHAR, 'text1' VARCHAR,'text2' VARCHAR,'userNames' VARCHAR, 'userNamesWithGroup' VARCHAR, 'message' VARCHAR, 'sendTime' varchar, 'userNmaesWithFriend' varchar)"];
    
    BOOL worked = [db executeUpdate:createStr];
    return worked;
    
}

-(BOOL)insertRecord {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGroupHelperRecordTableCreatedInDb:db];
    
     NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO 'groupHelperRecord' ('userId','userIds','text1','text2','userNames','userNamesWithGroup','message','sendTime','userNmaesWithFriend') VALUES (?,?,?,?,?,?,?,?,?)"];
    BOOL worked = [db executeUpdate:insertStr,self.userId,self.userIds,self.text1,self.text2,self.userNames,self.userNamesWithGroup,self.message,self.sendTime,self.userNmaesWithFriend];
    
    return worked;
}


- (NSString *)fetchAllGroupAnnouncementFromLocal {
   // NSString* sql = [NSString stringWithFormat:@"select content from groupAnnouncement where roomJid = '%@'", self.roomJid];
    NSString* sql = [NSString stringWithFormat:@"select content from groupAnnouncement"];
//    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
    
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    NSString *content;
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
//        JXLabelObject *label=[[JXLabelObject alloc] init];
//        [self groupAnnouncementFromDataset:label rs:rs];
//        [resultArr addObject:label];
        content = [rs stringForColumn:@"content"];
        return content;
    }
    [rs close];
    
    return nil;
}

- (NSString *)fetchGroupAnnouncementSelectItemFromLocal {
   // NSString* sql = [NSString stringWithFormat:@"select selectItem from groupAnnouncement where roomJid = '%@'", self.roomJid];
    NSString* sql = [NSString stringWithFormat:@"select selectItem from groupAnnouncement"];
//    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
    
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    NSString *selectItem;
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
//        JXLabelObject *label=[[JXLabelObject alloc] init];
//        [self groupAnnouncementFromDataset:label rs:rs];
//        [resultArr addObject:label];
        selectItem = [rs stringForColumn:@"selectItem"];
        return selectItem;
    }
    [rs close];
    
    return nil;
}

- (BOOL)checkGroupAnnouncementRoomJid {
     NSString* sql = [NSString stringWithFormat:@"SELECT * FROM groupAnnouncement WHERE roomJid = '%@'", self.roomJid];
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
       return YES; // 那就直接返回，即不执行插入操作
    }
    [rs close];
    
    return NO;
}


// 创建表头公告表
- (BOOL)checkGroupAnnouncementTableCreatedInDb:(FMDatabase *)db {
    
    NSString *createStr=[NSString stringWithFormat:@"CREATE  TABLE  IF NOT EXISTS 'groupAnnouncement' ('id' INTEGER PRIMARY KEY, 'userId' VARCHAR, 'roomJid' VARCHAR, 'content' VARCHAR,'creatTime' VARCHAR,'type' VARCHAR, 'roomName' VARCHAR, 'sendUserId' VARCHAR, 'typeName' varchar, 'selectItem' varchar)"];
    
    BOOL worked = [db executeUpdate:createStr];
    return worked;
    
}

- (BOOL)updateRecordGroupAnnouncement {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGroupAnnouncementTableCreatedInDb:db];
    NSString* sql = [NSString stringWithFormat:@"update groupAnnouncement set userId=?,roomJid=?,content=?,creatTime=?,type=?,roomName=?,sendUserId=?,typeName=? where roomJid=?"];
    BOOL worked = [db executeUpdate:sql,self.userId,self.roomJid,self.content,self.creatTime,self.type,self.roomName,self.sendUserId,self.typeName, self.roomJid];
    return worked;
}

- (BOOL)updateRecordGroupAnnouncementSelectItem {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGroupAnnouncementTableCreatedInDb:db];
    NSString* sql = [NSString stringWithFormat:@"update groupAnnouncement set selectItem=? where roomJid=?"];
    BOOL worked = [db executeUpdate:sql,self.selectItem, self.roomJid];
    return worked;
}


- (BOOL)insertRecordGroupAnnouncement {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGroupAnnouncementTableCreatedInDb:db];
    
     NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO 'groupAnnouncement' ('userId','roomJid','content','creatTime','type','roomName','sendUserId','typeName') VALUES (?,?,?,?,?,?,?,?)"];
    BOOL worked = [db executeUpdate:insertStr,self.userId,self.roomJid,self.content,self.creatTime,self.type,self.roomName,self.sendUserId,self.typeName];
    
    return worked;
}

- (NSDate *)fetchGradeFromLocal {
    NSString* sql = [NSString stringWithFormat:@"select data from grade where gradeIndex = '%d'", self.gradeIndex];
        
    //    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
        
        NSString* myUserId = MY_USER_ID;
        FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
        [self checkTableCreatedInDb:db];
        FMResultSet *rs=[db executeQuery:sql];
        while ([rs next]) {
            id data = [rs objectForColumnName:@"data"];
            
            return data;
        }
        [rs close];
        
        return nil;
}

// 创建所有VIP表
- (BOOL)checkGradeTableCreatedInDb:(FMDatabase *)db {
    
    NSString *createStr=[NSString stringWithFormat:@"CREATE  TABLE  IF NOT EXISTS 'grade' ('gradeIndex' integer PRIMARY KEY  NOT NULL  UNIQUE, data blob)"];
    BOOL worked = [db executeUpdate:createStr];
    return worked;
}

// 更新VIP表
- (BOOL)updateGradeTable {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGradeTableCreatedInDb:db];
    NSString* sql = [NSString stringWithFormat:@"update grade set data=? where gradeIndex=?"];
    BOOL worked = [db executeUpdate:sql,self.data,self.gradeIndex];
    return worked;
}

- (BOOL)insertGradeTable {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkGradeTableCreatedInDb:db];
    
     NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO 'grade' ('gradeIndex','data') VALUES (?,?)"];
     BOOL worked = [db executeUpdate:insertStr,self.gradeIndex,self.data];
    
    if (!worked) {
        [self updateGradeTable];
    }
    
    return worked;
}

-(BOOL)checkTableCreatedInDb:(FMDatabase *)db
{
    NSString *createStr=[NSString stringWithFormat:@"CREATE  TABLE  IF NOT EXISTS '%@' ('groupId' VARCHAR PRIMARY KEY  NOT NULL  UNIQUE , 'userId' VARCHAR, 'groupName' VARCHAR, 'userIdList' VARCHAR)",_tableName];
    
    BOOL worked = [db executeUpdate:createStr];
    return worked;
}

-(void)labelFromDataset:(JXLabelObject*)obj rs:(FMResultSet*)rs{
    obj.userId=[rs stringForColumn:@"userId"];
    obj.groupId=[rs stringForColumn:@"groupId"];
    obj.groupName=[rs stringForColumn:@"groupName"];
    obj.userIdList=[rs stringForColumn:@"userIdList"];
    
}


-(void)recordsFromDataset:(JXLabelObject*)obj rs:(FMResultSet*)rs{
    obj.userId=[rs stringForColumn:@"userId"];
    obj.userIds=[rs stringForColumn:@"userIds"];
    obj.text1=[rs stringForColumn:@"text1"];
    obj.text2=[rs stringForColumn:@"text2"];
    
    obj.userNames=[rs stringForColumn:@"userNames"];
    obj.userNamesWithGroup=[rs stringForColumn:@"userNamesWithGroup"];
    obj.message=[rs stringForColumn:@"message"];
    obj.sendTime=[rs stringForColumn:@"sendTime"];
    obj.userNmaesWithFriend=[rs stringForColumn:@"userNmaesWithFriend"];
}

-(void)groupAnnouncementFromDataset:(JXLabelObject*)obj rs:(FMResultSet*)rs{
    // self.userId,self.roomJid,self.content,self.creatTime,self.type,self.roomName,self.sendUserId,self.typeName
    obj.userId=[rs stringForColumn:@"userId"];
    obj.roomJid=[rs stringForColumn:@"roomJid"];
    obj.content=[rs stringForColumn:@"content"];
    obj.creatTime=[rs stringForColumn:@"creatTime"];
    
    obj.type=[rs stringForColumn:@"type"];
    obj.roomName=[rs stringForColumn:@"roomName"];
    obj.sendUserId=[rs stringForColumn:@"sendUserId"];
    obj.typeName=[rs stringForColumn:@"typeName"];
}

//数据库增删改查
-(BOOL)insert {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];

    NSString *insertStr=[NSString stringWithFormat:@"INSERT INTO '%@' ('groupId','userId','groupName','userIdList') VALUES (?,?,?,?)",_tableName];
    
    BOOL worked = [db executeUpdate:insertStr,self.groupId,self.userId,self.groupName,self.userIdList];
    
    if(!worked)
        worked = [self update];
    return worked;
}

-(BOOL)delete {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    NSString *sql = [NSString stringWithFormat:@"delete from %@ where groupId=?",_tableName];
    BOOL worked=[db executeUpdate:sql,self.groupId];
    return worked;
}

-(BOOL)update {
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    
    NSString* sql = [NSString stringWithFormat:@"update %@ set userId=?,groupName=?,userIdList=? where groupId=?",_tableName];
    BOOL worked = [db executeUpdate:sql,self.userId,self.groupName,self.userIdList,self.groupId];
    return worked;
}

// 获取用户的所有标签
- (NSMutableArray *)fetchLabelsWithUserId:(NSString *)userId {
    NSString* sql = [NSString stringWithFormat:@"select * from label where userIdList like '%%%@%%'", userId];
    
    NSMutableArray *resultArr=[[NSMutableArray alloc]init];
    
    NSString* myUserId = MY_USER_ID;
    FMDatabase* db = [[JXXMPP sharedInstance] openUserDb:myUserId];
    [self checkTableCreatedInDb:db];
    
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
        JXLabelObject *label=[[JXLabelObject alloc] init];
        [self labelFromDataset:label rs:rs];
        [resultArr addObject:label];
    }
    [rs close];

    return resultArr;
}
    
@end