#import "DB_Que_Helper.h" #import "Question.h" #import "markGroup.h" #import "markInfo.h" #import "Grade.h" #import static FMDatabase *shareDataBase = nil; @interface DB_Que_Helper () @property (copy, readwrite, nonatomic) NSDictionary *updateDataDic; @property (copy, readwrite, nonatomic) NSArray *updateIdArr; @end @implementation DB_Que_Helper /** 创建数据库类的单例对象 **/ + (FMDatabase *)createDataBase { if (![[NSFileManager defaultManager] fileExistsAtPath:dataBasePath2]) { [DB_Que_Helper copyFileDatabase]; } /**线程安全的。 其实就是一个单例的方法 好处: 1 线程安全 2 很好满足静态分析器要求 3 和自动引用计数（ARC）兼容 4 仅需要少量代码 缺点: 仍可以创建非共享的实例 */ static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ shareDataBase = [FMDatabase databaseWithPath:dataBasePath2]; }); //如果数据库搞定 判断表是否存在 //添加成绩表 [DB_Que_Helper createNewTable]; //添加错题、收藏、排错、已做题表 [DB_Que_Helper createNewQuestionTable]; //添加表后 如果有旧数据 就导入 if ([myDelegate.gradeTableData isKindOfClass:[NSMutableArray class]]) { if (myDelegate.gradeTableData.count > 0) { if ([shareDataBase open]) { for (NSDictionary *dic in myDelegate.gradeTableData) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"INSERT INTO grade (date,score,time,honor,subject,car_type,sfzhm) VALUES ('%@','%@','%@','%@','%@','%@','%@');", dic[@"data"],dic[@"score"],dic[@"time"],dic[@"honor"],dic[@"subject"],dic[@"car_type"],dic[@"sfzhm"]]]; } [shareDataBase close]; } } [myDelegate.gradeTableData removeAllObjects]; myDelegate.gradeTableData = nil; } if ([myDelegate.markQuestionTableData isKindOfClass:[NSMutableArray class]]) { if (myDelegate.markQuestionTableData.count > 0) { if ([shareDataBase open]) { for (NSDictionary *dic in myDelegate.markQuestionTableData) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"INSERT INTO markQuestion (_id,type,isUpdata,subject,car_type,sfzhm)values(%d,'%@','%@','%@','%@','%@')", [dic[@"_id"] intValue],dic[@"type"],dic[@"isUpdata"],dic[@"subject"],dic[@"car_type"],dic[@"sfzhm"]]]; } [shareDataBase close]; } } [myDelegate.markQuestionTableData removeAllObjects]; myDelegate.markQuestionTableData = nil; } if ([shareDataBase open]) { int num = -1; FMResultSet *s = [shareDataBase executeQuery:@"SELECT * FROM db_versions WHERE id=1"]; if ([s next]) { num=[[s stringForColumn:@"version"] intValue]; if (num == 0) { for (NSString *idStr in self.updateIdArr) { [self updateQuestionWithId:idStr]; } NSString *sqlStr = [NSString stringWithFormat:@"UPDATE db_versions SET version = 1 WHERE id=1"]; BOOL isSuccess = [shareDataBase executeUpdate:sqlStr]; NSLog(@"%@",isSuccess? @"Success!" : @"Failed!"); } } [shareDataBase close]; } return shareDataBase; } /**创建grade表。保存我的成绩 */ +(void)createNewTable { if ([shareDataBase open]) { //创建表 如果表不存在 [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS grade (id integer PRIMARY KEY AUTOINCREMENT, date text, score text, time text , honor text,subject text ,car_type text ,sfzhm text);"]; //判断字段是否存在 老学员的表中没有这个字段 这里判断添加 并赋初始值 NSString *sfzhm = @"sfzhm"; if (![shareDataBase columnExists:sfzhm inTableWithName:@"grade"]) { NSString *sql; sql = [NSString stringWithFormat:@"ALTER TABLE grade ADD sfzhm text"]; [shareDataBase executeUpdate:sql]; [DB_Que_Helper setGradeDefaultValue]; }else{ //NSLog(@"sfzhm已存在"); } [shareDataBase close]; } } +(void)createNewQuestionTable { if ([shareDataBase open]) { //创建错题类表 type:1、错题 2、收藏 3、排错 4、已做未做 isUpdata:是否上传 [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS markQuestion (id integer PRIMARY KEY AUTOINCREMENT, _id integer, type text, isUpdata text, subject text ,car_type text ,sfzhm text);"]; [shareDataBase close]; } } +(void)insertGrade:(Grade*)gra { if ([shareDataBase open]) { //将考试成绩存到数据库 NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } [shareDataBase executeUpdate:@"INSERT INTO grade (date,score,time,honor,subject,car_type,sfzhm) VALUES (?,?,?,?,?,?,?);", gra.graDate,gra.graScore,gra.graTime,gra.graHonor,myDelegate.subject,defUser.car_type,sfzhm]; [shareDataBase close]; } } /**注意：表也是有题库名和科目的 按照题库和科目 找到对应的成绩 */ +(NSArray *)queryAllGrades { NSMutableArray *arr=[[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from grade where car_type = '%@' and subject = '%@' and sfzhm = '%@'",defUser.car_type,myDelegate.subject,sfzhm]]; while ([s next]) { Grade *gra=[[Grade alloc]init]; gra.graDate=[s stringForColumn:@"date"]; gra.graHonor=[s stringForColumn:@"honor"]; gra.graScore=[s stringForColumn:@"score"]; gra.graTime=[s stringForColumn:@"time"]; [arr addObject:gra]; //NSLog(@"邪门---><>%@--->%@----.%@----->%@",gra.graDate,gra.graHonor,gra.graScore,gra.graTime); } }else{ // NSLog(@"成绩数据库未打开----db unOpen"); [LoadingView showMsg:@"读取成绩失败！"]; } return arr; } ///**添加done和favorite字段 // 现在用的 iscollect 和 isdone 字段 这个方法不用了吧 不过如果需要添加字段 可以使用这个方法 // 可以删除 --danson // */ //+(void)addDone{ // //判断字段是否存在 // NSString* done = @"done"; // [shareDataBase open]; // if (![shareDataBase columnExists:done inTableWithName:@"question"]) // { // NSString *sql; // sql = [NSString stringWithFormat:@"ALTER TABLE question ADD done text"]; // [shareDataBase executeUpdate:sql]; // [DB_Que_Helper setDefaultValue]; // }else{ // //NSLog(@"done已存在"); // } // // [shareDataBase close]; //} +(void)addDoneAndFavorite{ //判断字段是否存在 NSString* done = @"done"; [shareDataBase open]; if (![shareDataBase columnExists:done inTableWithName:@"question"]) { NSString *sql; sql = [NSString stringWithFormat:@"ALTER TABLE question ADD done text"]; [shareDataBase executeUpdate:sql]; sql = [NSString stringWithFormat:@"ALTER TABLE question ADD favorite text"]; [shareDataBase executeUpdate:sql]; [DB_Que_Helper setDefaultValue]; }else{ // NSLog(@"done&fav exist"); } [shareDataBase close]; } +(BOOL)setStateWithArray:(NSArray *)questions { BOOL isOk = NO; BOOL allIsOK = YES; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { for (NSDictionary* obj in questions) { NSInteger qId = [obj[@"QID"] integerValue]; NSString *kType = obj[@"TYPE"]; NSString *sql; if ([kType isEqualToString:@"1"]) { //收藏 sql = [NSString stringWithFormat:@"update question set iscollect = %@ where _id=%ld",@"1",(long)qId]; } else if ([kType isEqualToString:@"2"]) { //错题 sql = [NSString stringWithFormat:@"update question set state = %@ where _id=%ld",@"1",(long)qId]; } else { //排除 sql = [NSString stringWithFormat:@"update question set isdone = %@ where _id=%ld",@"1",(long)qId]; } isOk=[shareDataBase executeUpdate:sql]; if (isOk == NO) { allIsOK = NO; } } //同步数据后 查询错题数量 NSString *sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = '1' and subject = '1'",defUser.car_type]; int cnt1 = [shareDataBase intForQuery:sql]; sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = '1' and subject = '4'",defUser.car_type]; int cnt2 = [shareDataBase intForQuery:sql]; sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = '1'",defUser.car_type]; int cnt3 = [shareDataBase intForQuery:sql]; myDelegate.faultArray = [NSMutableArray arrayWithObjects:[NSString stringWithFormat:@"%d",cnt1],[NSString stringWithFormat:@"%d",cnt2],[NSString stringWithFormat:@"%d",cnt3], nil]; [shareDataBase close]; } //NSLog(@"数据库数据更新%@",allIsOK == 1 ? @"成功" : @"失败"); //NSLog(@"错题书------>%@",myDelegate.faultArray); return allIsOK; } //如果同步失败 或无数据做的操作 +(void)getFaultIfNotNet { shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = 1 and subject = 1",defUser.car_type]; int cnt1 = [shareDataBase intForQuery:sql]; sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = 1 and subject = 4",defUser.car_type]; int cnt2 = [shareDataBase intForQuery:sql]; sql=[NSString stringWithFormat:@"select count(*) from question where car_type = '%@' and state = 1",defUser.car_type]; int cnt3 = [shareDataBase intForQuery:sql]; myDelegate.faultArray = [NSMutableArray arrayWithObjects:[NSString stringWithFormat:@"%d",cnt1],[NSString stringWithFormat:@"%d",cnt2],[NSString stringWithFormat:@"%d",cnt3], nil]; [shareDataBase close]; } } +(BOOL)setState:(NSString*)state withId:(NSInteger)qid withType:(NSString *)type { NSString *sql; if ([type isEqualToString:@"1"]) { //收藏 sql = [NSString stringWithFormat:@"update question set iscollect = %@ where _id=%ld",state,(long)qid]; } else if ([type isEqualToString:@"2"]) { //错题 sql = [NSString stringWithFormat:@"update question set state = %@ where _id=%ld",state,(long)qid]; } else { //排除 // NSLog(@"数据库更改排错状态"); sql = [NSString stringWithFormat:@"update question set isdone = %@ where _id=%ld",state,(long)qid]; } BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { isOk=[shareDataBase executeUpdate:sql]; [shareDataBase close]; } //NSLog(@"数据库数据更新%@",isOk == 1 ? @"成功" : @"失败"); return isOk; } //如果新加了字段 要有这个操作置零 --danson +(void)setDefaultValue { /**fmdb一定要重新开。因为刚才没有2个字段。现在有了2个字段。 */ static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ shareDataBase = [FMDatabase databaseWithPath:dataBasePath2]; }); if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"update question set done = 0"]]; } [shareDataBase close]; } +(void)setGradeDefaultValue { /**fmdb一定要重新开。因为刚才没有2个字段。现在有了2个字段。 */ //这样会进死循环 //shareDataBase=[DB_Que_Helper createDataBase]; //这个会导致打开多个数据库 //shareDataBase = [FMDatabase databaseWithPath:dataBasePath2]; //dansonmark static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ shareDataBase = [FMDatabase databaseWithPath:dataBasePath2]; }); if ([shareDataBase open]) { //ALTER TABLE grade ADD sfzhm text NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } [shareDataBase executeUpdate:[NSString stringWithFormat:@"update grade set sfzhm = '%@'",sfzhm]]; //NSLog(@"写入----><>%d",isok); } [shareDataBase close]; } /**不要判断是否存在，直接写入覆盖。 */ +(void)removeDB { NSString *documentsDirectory = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory,NSUserDomainMask, YES) firstObject]; // NSLog(@"移除旧题库----><>%@",documentsDirectory); NSString *documentLibraryFolderPath = [documentsDirectory stringByAppendingPathComponent:dataBaseName2]; [[NSFileManager defaultManager] removeItemAtPath:documentLibraryFolderPath error:nil]; //重新copy题库 shareDataBase=[DB_Que_Helper createDataBase]; } + (void)getDataFormLastDB { if (![[NSFileManager defaultManager] fileExistsAtPath:dataBasePath2]) { //说明是第一次安装 没有题库 不需要做转移数据的操作 return; } // NSLog(@"获取旧题库数据"); shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { //提取markQuestion数据 FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from markQuestion"]]; myDelegate.markQuestionTableData = [NSMutableArray array]; while ([s next]) { //id integer PRIMARY KEY AUTOINCREMENT, _id integer, type text, isUpdata text, subject text ,car_type text ,sfzhm text NSMutableDictionary *dic = [NSMutableDictionary dictionary]; [dic setValue:[NSString stringWithFormat:@"%d",[s intForColumn:@"_id"]] forKey:@"_id"]; [dic setValue:[s stringForColumn:@"type"] forKey:@"type"]; [dic setValue:[s stringForColumn:@"isUpdata"] forKey:@"isUpdata"]; [dic setValue:[s stringForColumn:@"subject"] forKey:@"subject"]; [dic setValue:[s stringForColumn:@"car_type"] forKey:@"car_type"]; [dic setValue:[s stringForColumn:@"sfzhm"] forKey:@"sfzhm"]; [myDelegate.markQuestionTableData addObject:dic]; } //提取grade数据 s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from grade"]]; myDelegate.gradeTableData = [NSMutableArray array]; while ([s next]) { //date text, score text, time text , honor text,subject text ,car_type text ,sfzhm text NSMutableDictionary *dic = [NSMutableDictionary dictionary]; [dic setValue:[s stringForColumn:@"text"] forKey:@"text"]; [dic setValue:[s stringForColumn:@"score"] forKey:@"score"]; [dic setValue:[s stringForColumn:@"time"] forKey:@"time"]; [dic setValue:[s stringForColumn:@"honor"] forKey:@"honor"]; [dic setValue:[s stringForColumn:@"subject"] forKey:@"subject"]; [dic setValue:[s stringForColumn:@"car_type"] forKey:@"car_type"]; [dic setValue:[s stringForColumn:@"sfzhm"] forKey:@"sfzhm"]; [myDelegate.gradeTableData addObject:dic]; } // NSLog(@"getDataFormLastDB---->%@-----%@",myDelegate.markQuestionTableData,myDelegate.gradeTableData); [shareDataBase close]; }else{ NSLog(@"题库获取失败，数据库未打开_getDataFormLastDB"); } //提取完数据 删除旧库 [DB_Que_Helper removeDB]; } /** **关闭数据库 **/ + (void)closeDataBase { if(![shareDataBase close]) { NSLog(@"数据库关闭异常，请检查"); return; } } //拷贝数据库 +(void)copyFileDatabase { // NSLog(@"拷贝数据库"); NSArray *paths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory,NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *documentLibraryFolderPath = [documentsDirectory stringByAppendingPathComponent:dataBaseName2]; if ([[NSFileManager defaultManager] fileExistsAtPath:documentLibraryFolderPath]) { // NSLog(@"222文件已经存在了"); }else { NSString *dbName = @"miaxis.db"; NSString *resourceSampleImagesFolderPath =[[NSBundle mainBundle] pathForResource:dbName ofType:nil]; NSData *mainBundleFile = [NSData dataWithContentsOfFile:resourceSampleImagesFolderPath]; [[NSFileManager defaultManager] createFileAtPath:documentLibraryFolderPath contents:mainBundleFile attributes:nil]; } } +(NSArray *)queryAllQuestion { NSMutableArray *questions=[[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from question where car_type = '%@' ",defUser.car_type]]; while ([s next]) { Question *question=[[Question alloc]init]; question._id=[s stringForColumn:@"_id"]; question.option_type=[s stringForColumn:@"option_type"]; question.media_type=[s stringForColumn:@"media_type"]; question.chapter=[s stringForColumn:@"chapter"]; question.questionLabel=[s stringForColumn:@"question_label"]; question.question=[s stringForColumn:@"question"]; question.media=[s stringForColumn:@"media"]; question.answer=[s stringForColumn:@"answer"]; question.optionA=[s stringForColumn:@"option_a"]; question.optionB=[s stringForColumn:@"option_b"]; question.optionC=[s stringForColumn:@"option_c"]; question.optionD=[s stringForColumn:@"option_d"]; question.explain=[s stringForColumn:@"explain"]; question.state=[s stringForColumn:@"state"]; question.subject=[s stringForColumn:@"subject"]; [questions addObject:question]; } [shareDataBase close]; }else{ NSLog(@"题库获取失败，数据库未打开"); } return questions; } +(NSArray*)queryArea { /**从数据库找地址信息 */ NSMutableArray *questions=[[NSMutableArray alloc] init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from area"]]; while ([s next]) { NSMutableDictionary* question = [NSMutableDictionary dictionary]; question[@"name"] = [s stringForColumn:@"name"]; question[@"code"] = [s stringForColumn:@"code"]; question[@"parent_code"] = [s stringForColumn:@"parent_code"]; [questions addObject:question]; } [shareDataBase close]; }else{ NSLog(@"ERROR: db unOpen"); } return questions; } +(NSArray *)queryQuestion:(NSString *)where { NSMutableArray *questions=[[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sql; //章节考试用到 if ([where rangeOfString:@"subject = '1'"].location != NSNotFound) { sql=[NSString stringWithFormat:@"select * from question where car_type = '%@' and %@",defUser.car_type,where]; } if ([where rangeOfString:@"subject = '4'"].location != NSNotFound) { sql=[NSString stringWithFormat:@"select * from question where car_type = 'C1' and %@",where ]; if ([defUser.car_type isEqualToString:@"D"]) { sql=[NSString stringWithFormat:@"select * from question where car_type = 'D' and %@",where ]; } } //资格证 非强化 非全部题 if ([defUser.car_type isEqualToString:@"1"] || [defUser.car_type isEqualToString:@"2"] || [defUser.car_type isEqualToString:@"3"] || [defUser.car_type isEqualToString:@"4"] || [defUser.car_type isEqualToString:@"5"]) { if (![where isEqualToString:@"strong"]) { if (where.length > 12) { where = [where substringFromIndex:13]; } sql=[NSString stringWithFormat:@"select * from question where car_type = '%@'%@",defUser.car_type,where]; } } if ([where isEqualToString:@"strong"])//代表强化练习 { sql=[NSString stringWithFormat:@"select * from question where %@",myDelegate.where]; } FMResultSet *s=[shareDataBase executeQuery:sql]; questions = [Question questionsWithFMResultSet:s]; //NSLog(@"这里找出要显示的题目sql-->%@-------where-->%@------count-->%d",sql,where,(int)questions.count); [shareDataBase close]; } return questions; } //查找章节列表 +(NSArray *)queryList:(NSString *)where { NSMutableArray *lists=[[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:where]; while ([s next]) { NSDictionary *dic = [[NSDictionary alloc] initWithObjectsAndKeys:[s stringForColumn:@"title"],@"title",[s stringForColumn:@"ID"],@"chapter", nil]; [lists addObject:dic]; } [shareDataBase close]; } return lists; } //查找标识 +(NSArray *)queryMark:(NSString *)where { NSMutableArray *markArray = [NSMutableArray array]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from APP_MARK_GROUP where MG_PARENT = '%@'",where]]; markArray = [markGroup markGroupWithFMResultSet:s]; [shareDataBase close]; } return markArray; } +(NSArray *)queryDetailMark:(NSString *)where { NSMutableArray *markArray = [NSMutableArray array]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from APP_MARK_INFO where %@",where]]; markArray = [markInfo markInfoWithFMResultSet:s]; [shareDataBase close]; } return markArray; } +(BOOL)updateDB:(NSString *)sql { shareDataBase=[DB_Que_Helper createDataBase]; BOOL isOk = NO; if ([shareDataBase open]) { isOk=[shareDataBase executeUpdate:sql]; [shareDataBase close]; } return isOk; } //添加已做的题 +(void)addToDoneQue:(NSInteger)ID { shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"update question set done = 1 where _id=%d",(int)ID]]; [shareDataBase close]; } } //这里也错了 和数据库字段不符 delete --数据库问题--danson +(BOOL)addToStrengthenQue:(NSInteger)ID { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { isOk=[shareDataBase executeUpdate:[NSString stringWithFormat:@"update question set state = 1 where _id=%d",(int)ID]]; [shareDataBase close]; } return isOk; } +(BOOL)removeToStrengthenQue:(NSInteger)ID { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { isOk=[shareDataBase executeUpdate:[NSString stringWithFormat:@"update question set state = 0 where _id=%d",(int)ID]]; [shareDataBase close]; } return isOk; } /** 抽考试题 */ +(NSArray *)queryExamQuestion:(NSString *)where { NSMutableArray *questions=[[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sql; //模拟考试中.... //从数据库中随机抽取相应数目的题目 应该不用考虑题目存放数组位置问题吧 因为不是耗时操作 按代码先后顺序来排序 //资格证考试和科一科四不同 40单选 40判断 10多选 //科目一40题判断题 科目四20题判断题 if ([where isEqualToString:@"subject = '1'"]) { sql=[NSString stringWithFormat:@"select * from question where car_type = '%@' and subject = 1 and option_type = 0 order by random() limit 40",defUser.car_type]; } else if ([where isEqualToString:@"subject = '4'"]) { sql=[NSString stringWithFormat:@"select * from question where car_type = 'C1' and subject = 4 and option_type = 0 order by random() limit 20"]; } else { sql=[NSString stringWithFormat:@"select * from question where car_type = '%@' and option_type = 1 order by random() limit 40",defUser.car_type]; } FMResultSet *s=[shareDataBase executeQuery:sql]; [questions addObjectsFromArray:[Question questionsWithFMResultSet:s]]; //科目一60题单选题 科目四20题单选题 if ([where isEqualToString:@"subject = '1'"]) { sql=[NSString stringWithFormat:@"select * from question where car_type = '%@' and subject = 1 and option_type = 1 order by random() limit 60",defUser.car_type]; } else if ([where isEqualToString:@"subject = '4'"]) { sql=[NSString stringWithFormat:@"select * from question where car_type = 'C1' and subject = 4 and option_type = 1 order by random() limit 20"]; } else { sql=[NSString stringWithFormat:@"select * from question where car_type = '%@' and option_type = 0 order by random() limit 40",defUser.car_type]; } FMResultSet *s2=[shareDataBase executeQuery:sql]; [questions addObjectsFromArray:[Question questionsWithFMResultSet:s2]]; // 科目四 10题多选题(科目一无多选题) if ([where isEqualToString:@"subject = '4'"]) { FMResultSet *s3=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from question where car_type = 'C1' and subject = 4 and option_type = 2 order by random() limit 10"]]; [questions addObjectsFromArray:[Question questionsWithFMResultSet:s3]]; } if ([where isEqualToString:@"subject = nil"]) { //NSLog(@"资格证多选题"); FMResultSet *s3=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from question where car_type = '%@' and option_type = 2 order by random() limit 10",defUser.car_type]]; [questions addObjectsFromArray:[Question questionsWithFMResultSet:s3]]; } [shareDataBase close]; } return questions; } //清空错题、收藏、排错 +(void)clearFavInfo { //每次进程序都要有这样的操作 shareDataBase=[DB_Que_Helper createDataBase]; if([shareDataBase open]) { [shareDataBase executeUpdate:@"update question set state = 0 where state = 1"]; [shareDataBase executeUpdate:@"update question set state = 0 where iscollect = 1"]; [shareDataBase executeUpdate:@"update question set state = 0 where isdone = 1"]; [shareDataBase close]; return; }else{ NSLog(@"db error"); return; } } +(BOOL)executeSql:(NSArray *)sqlList { shareDataBase=[DB_Que_Helper createDataBase]; if([shareDataBase open]){ NSInteger count=sqlList.count-1; for(int i=0;i%@-------where-->%@------count-->%d",sql,where,(int)cnt); return cnt; } return 0; } +(int)getProgress { int num=0; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s = [shareDataBase executeQuery:@"SELECT * FROM db_versions WHERE id=1"]; if ([s next]) { num=[[s stringForColumn:@"version"] intValue]; } [shareDataBase close]; } return num; } +(NSArray *)queryColumnNameWithTable:(NSString *)tableName { sqlite3 *database; sqlite3_open([dataBasePath2 UTF8String], &database); sqlite3_stmt *statement; //获取数据库的表的名字 // const char *getTableInfo = "select * from sqlite_master where type='table' order by name"; // sqlite3_prepare_v2(database, getTableInfo, -1, &statement, nil); // while (sqlite3_step(statement) == SQLITE_ROW) { // char *nameData = (char *)sqlite3_column_text(statement, 1); // NSString *tableName = [[NSString alloc] initWithUTF8String:nameData]; // NSLog(@"name:%@",tableName); // } NSMutableArray *array = [NSMutableArray array]; NSString *columnName = @""; //获取某表中字段的名字 const char *getColumn = "PRAGMA table_info(question)"; sqlite3_prepare_v2(database, getColumn, -1, &statement, nil); while (sqlite3_step(statement) == SQLITE_ROW) { char *nameData = (char *)sqlite3_column_text(statement, 1); columnName = [[NSString alloc] initWithUTF8String:nameData]; //NSLog(@"columnName:%@",columnName); [array addObject:columnName]; } sqlite3_finalize(statement); return array; } #pragma mark markQuestion //(_id integer PRIMARY KEY, type text, isUpdata text, subject text ,car_type text ,sfzhm text) +(BOOL)setMarkQuestionWithQuestion:(NSInteger)qId Type:(NSString *)markType State:(NSInteger)state { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { if (state == 1) { //添加 NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } //在调这个方法前已经判断过 是否已经存在 isOk = [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into markQuestion (_id,type,isUpdata,subject,car_type,sfzhm)values(%ld,'%@','0','%@','%@','%@')",(long)qId,markType,myDelegate.subject,defUser.car_type,sfzhm]]; } if (state == 0) { //删除 NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } isOk = [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from markQuestion where _id=%ld and type='%@' and sfzhm='%@'",(long)qId,markType,sfzhm]]; } [shareDataBase close]; } return isOk; } +(BOOL)queryIsHaveWithQuestion:(NSInteger)qId Type:(NSString *)markType { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSInteger cnt = [shareDataBase intForQuery:[NSString stringWithFormat:@"select count(*) from markQuestion where _id=%ld and type='%@' and subject = '%@' and car_type = '%@' and sfzhm = '%@'",(long)qId,markType,myDelegate.subject,defUser.car_type,sfzhm]]; if (cnt > 0) { isOk = YES; } [shareDataBase close]; } return isOk; } +(NSArray *)queryMarkQuestionWithType:(NSString *)markType { NSMutableArray *questions = [[NSMutableArray alloc]init]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSString *sql;//1、错题 2、收藏 3、排错 4、已做未做 5、除去排除的题 if ([markType isEqualToString:@"4"]){ sql=[NSString stringWithFormat:@"select * from question where subject = '%@' and car_type = '%@' and _id not in (select _id from markQuestion where type = '4' and subject = '%@' and car_type = '%@' and sfzhm = '%@')",myDelegate.subject,defUser.car_type,myDelegate.subject,defUser.car_type,sfzhm]; }else if ([markType isEqualToString:@"5"]){ //除去排错剩下的所有题 sql=[NSString stringWithFormat:@"select * from question where subject = '%@' and car_type = '%@' and _id not in (select _id from markQuestion where type = '3' and subject = '%@' and car_type = '%@' and sfzhm = '%@')",myDelegate.subject,defUser.car_type,myDelegate.subject,defUser.car_type,sfzhm]; if ([myDelegate.subject isEqualToString:@"4"]) { sql=[NSString stringWithFormat:@"select * from question where subject = '%@' and car_type = 'C1' and _id not in (select _id from markQuestion where type = '3' and subject = '%@' and car_type = '%@' and sfzhm = '%@')",myDelegate.subject,myDelegate.subject,defUser.car_type,sfzhm]; if ([defUser.car_type isEqualToString:@"D"]) { sql=[NSString stringWithFormat:@"select * from question where subject = '%@' and car_type = 'D' and _id not in (select _id from markQuestion where type = '3' and subject = '%@' and car_type = '%@' and sfzhm = '%@')",myDelegate.subject,myDelegate.subject,defUser.car_type,sfzhm]; } } }else{ sql=[NSString stringWithFormat:@"select * from question where _id in (select _id from markQuestion where type = '%@' and subject = '%@' and car_type = '%@' and sfzhm = '%@')",markType,myDelegate.subject,defUser.car_type,sfzhm]; } //如果是资格证 就将科目的判断去掉 if ([defUser.car_type isEqualToString:@"1"] || [defUser.car_type isEqualToString:@"2"] || [defUser.car_type isEqualToString:@"3"] || [defUser.car_type isEqualToString:@"4"] || [defUser.car_type isEqualToString:@"5"]) { if ([sql containsString:[NSString stringWithFormat:@"subject = '%@' and",myDelegate.subject]]) { sql = [sql stringByReplacingOccurrencesOfString:[NSString stringWithFormat:@"subject = '%@' and",myDelegate.subject] withString:@""]; } } FMResultSet *s=[shareDataBase executeQuery:sql]; questions = [Question questionsWithFMResultSet:s]; // NSLog(@"这里找出要显示的题目sql-->%@------------count-->%d",sql,(int)questions.count); [shareDataBase close]; } return questions; } +(NSArray *)queryMarkQuestionWithUpData:(NSInteger)isUpdata MarkType:(NSString *)markType Subject:(NSString *)sub { NSMutableArray *array = [NSMutableArray array]; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSString *sql; sql=[NSString stringWithFormat:@"select * from markQuestion where subject = '%@' and car_type = '%@' and type = '%@' and isUpdata = '0' and sfzhm = '%@'",sub,defUser.car_type,markType,sfzhm]; if (isUpdata == 1) { sql = [sql stringByReplacingOccurrencesOfString:@" and isUpdata = '0'" withString:@""]; } FMResultSet *s=[shareDataBase executeQuery:sql]; while ([s next]) { NSString *questId = [s stringForColumn:@"_id"]; [array addObject:questId]; } [shareDataBase close]; } //NSLog(@"题目---><>%@",array); return array; } + (NSInteger)queryMarkQuestionWithMarkType:(NSString *)markType { NSInteger cnt = 0; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSString *queryStr = [NSString stringWithFormat:@"select count(*) from markQuestion where subject = '%@' and car_type = '%@' and sfzhm = '%@' and type = '%@'",myDelegate.subject,defUser.car_type,sfzhm,markType]; cnt = [shareDataBase intForQuery:queryStr]; [shareDataBase close]; } return cnt; } +(BOOL)setIsUpdateWithType:(NSString *)markType { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSString *sql; if ([markType isEqualToString:@"0"]) { sql = [NSString stringWithFormat:@"update markQuestion set isUpdata = '1' where subject = '%@' and car_type = '%@' and sfzhm = '%@'",myDelegate.subject,defUser.car_type,sfzhm]; }else{ sql = [NSString stringWithFormat:@"update markQuestion set isUpdata = '1' where type = '%@' and subject = '%@' and car_type = '%@' and sfzhm = '%@'",markType,myDelegate.subject,defUser.car_type,sfzhm]; } isOk = [shareDataBase executeUpdate:sql]; [shareDataBase close]; } return isOk; } +(BOOL)setIsUpdateWithArray:(NSArray *)array { BOOL isOk = NO; shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } NSString *sql; NSString *sub1 = @""; NSString *sub4 = @""; for (NSString *tagStr in array) { NSInteger tag = [tagStr integerValue]; NSString *typeStr = @"1"; if (tag == 3 || tag == 6) { typeStr = @"2"; } if (tag == 4 || tag == 7) { typeStr = @"3"; } if (tag < 5) {//科一 sub1 = [sub1 stringByAppendingString:[NSString stringWithFormat:@"%@,",typeStr]]; }else{//科四 sub4 = [sub4 stringByAppendingString:[NSString stringWithFormat:@"%@,",typeStr]]; } } if (sub1.length > 0) { sub1 = [sub1 substringToIndex:sub1.length - 1]; sql = [NSString stringWithFormat:@"update markQuestion set isUpdata = '1' where subject = '1' and car_type = '%@' and sfzhm = '%@' and type in (%@)",defUser.car_type,sfzhm,sub1]; isOk = [shareDataBase executeUpdate:sql]; } if (sub4.length > 0) { sub4 = [sub4 substringToIndex:sub4.length - 1]; sql = [NSString stringWithFormat:@"update markQuestion set isUpdata = '1' where subject = '4' and car_type = '%@' and sfzhm = '%@' and type in (%@)",defUser.car_type,sfzhm,sub4]; isOk = [shareDataBase executeUpdate:sql]; } [shareDataBase close]; } return isOk; } +(BOOL)setMarkQuestionWithArray:(NSArray *)questions { BOOL isOk = NO; BOOL allIsOK = YES; NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } //NSLog(@"-----><>%@",questions); shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { for (NSDictionary *obj in questions) { NSInteger qId = [obj[@"QID"] integerValue]; NSString *kType = obj[@"TYPE"]; NSString *markType; if ([kType isEqualToString:@"1"]){ //收藏 markType = @"2"; }else if ([kType isEqualToString:@"2"]){ //错题 markType = @"1"; }else{ //排除 markType = @"3"; } NSString *cartype = defUser.car_type; if ([obj[@"CARTYPE"] length] > 0) { cartype = obj[@"CARTYPE"]; } NSString *sub = myDelegate.subject; if ([obj[@"SUBJECT"] length] > 0) { sub = obj[@"SUBJECT"]; } if ([markType isEqualToString:@"1"]) { NSInteger cnt = [shareDataBase intForQuery:[NSString stringWithFormat:@"select count(*) from markQuestion where _id=%ld and type='4' and subject = '%@' and car_type = '%@' and sfzhm = '%@'",(long)qId,sub,cartype,sfzhm]]; if (cnt == 0) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into markQuestion (_id,type,isUpdata,subject,car_type,sfzhm)values(%ld,'4','1','%@','%@','%@')",(long)qId,sub,cartype,sfzhm]]; } } //dansonmark这里的科目 车型应该来自服务器 如果服务器不返回 就要从本地题目库查 isOk = [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into markQuestion (_id,type,isUpdata,subject,car_type,sfzhm)values(%ld,'%@','1','%@','%@','%@')",(long)qId,markType,sub,cartype,sfzhm]]; if (isOk == NO) { allIsOK = NO; } } [shareDataBase close]; } return allIsOK; } +(BOOL)deleteAllMarkQuestion { BOOL isOk = NO; NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { isOk = [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from markQuestion where type != '4' and sfzhm='%@'",sfzhm]]; [shareDataBase close]; } return isOk; } +(void)moveIsDoneToMarkQuestion { //等以后就版本没有了 就不用管这个了 NSArray *columnArray = [DB_Que_Helper queryColumnNameWithTable:@"question"]; if (![columnArray containsObject:@"done"]) { //说明是新版 没有了done字段。不需要转移已做题 return; } NSString *sfzhm = @"no"; if (myDelegate.isLogin) { sfzhm = defUser.userDict[@"id"]; } shareDataBase=[DB_Que_Helper createDataBase]; if ([shareDataBase open]) { FMResultSet *s=[shareDataBase executeQuery:@"select * from question where done = 1"]; NSMutableArray *questions = [Question questionsWithFMResultSet:s]; for (Question *question in questions) { //NSLog(@"插入已做题"); [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into markQuestion (_id,type,isUpdata,subject,car_type,sfzhm)values(%d,'4','0','%@','%@','%@')",[question._id intValue],myDelegate.subject,defUser.car_type,sfzhm]]; } [shareDataBase close]; } } + (void)updateQuestionWithId:(NSString *)idStr { NSString *sqlStr0 = [NSString stringWithFormat:@"UPDATE question SET question = '%@' WHERE _id = %ld",self.updateDataDic[idStr][@"question"],(long)idStr.integerValue]; NSString *sqlStr1 = [NSString stringWithFormat:@"UPDATE question SET explain = '%@' WHERE _id = %ld",self.updateDataDic[idStr][@"explain"],(long)idStr.integerValue]; BOOL isSuccess0 = [shareDataBase executeUpdate:sqlStr0]; BOOL isSuccess1 = [shareDataBase executeUpdate:sqlStr1]; NSLog(@"%@ %@",isSuccess0? @"Success!" : @"Failed!",isSuccess1? @"Success!" : @"Failed!"); } + (NSDictionary *)updateDataDic { return @{ @"332" : @{ @"question" : @"年龄在70周岁以上的驾驶人多长时间提交一次身体条件证明？", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"466" : @{ @"question" : @"年龄在70周岁以上，在一个记分周期结束后一年内未提交身体条件证明的，其机动车驾驶证将会被车辆管理所注销。", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"521" : @{ @"question" : @"年龄在70周岁以上的机动车驾驶人，应当每年进行一次身体检查的目的是什么？", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"3960" : @{ @"question" : @"申请小型汽车准驾车型驾驶证的人年龄条件是多少？", @"explain" : @"《公安部令第123号》第十一条：申请小型汽车、小型自动挡汽车、残疾人专用小型自动挡载客汽车、轻便摩托车准驾车型的，在18周岁以上、70周岁以下。", }, @"3962" : @{ @"question" : @"申请小型汽车汽车驾驶证的，年龄应在18周岁以上70周岁以下。", @"explain" : @"《公安部令第123号》第十一条：申请小型汽车、小型自动挡汽车、残疾人专用小型自动挡载客汽车、轻便摩托车准驾车型的，在18周岁以上、70周岁以下。", }, @"3976" : @{ @"question" : @"年龄在70周岁以上的驾驶人多长时间提交一次身体条件证明？", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"2738" : @{ @"question" : @"申请小型汽车准驾车型驾驶证的人年龄条件是多少？", @"explain" : @"《公安部令第123号》第十一条：申请小型汽车、小型自动挡汽车、残疾人专用小型自动挡载客汽车、轻便摩托车准驾车型的，在18周岁以上、70周岁以下。", }, @"2740" : @{ @"question" : @"申请小型汽车汽车驾驶证的，年龄应在18周岁以上70周岁以下。", @"explain" : @"《公安部令第123号》第十一条：申请小型汽车、小型自动挡汽车、残疾人专用小型自动挡载客汽车、轻便摩托车准驾车型的，在18周岁以上、70周岁以下。", }, @"2754" : @{ @"question" : @"年龄在70周岁以上的驾驶人多长时间提交一次身体条件证明？", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"3627" : @{ @"question" : @"年龄在70周岁以上，在1个记分周期结束后，（ ）内未提交身体检查结果的，车辆管理所注销其驾驶证。", @"explain" : @"新修改的《机动车驾驶证申领和使用规定》还放宽了未体检注销驾驶证后恢复驾驶资格的年龄，规定：年龄在70周岁以上的驾驶人，在一个记分周期结束后一年内未提交身体条件证明的，车辆管理所将注销其机动车驾驶证。", }, @"3631" : @{ @"question" : @"年龄在60岁以上，超过机动车驾驶证有效期一年以上未换证的，车辆管理所将注销其驾驶证。", @"explain" : @"《公安部令第123号》第六十七条：机动车驾驶人具有下列情形之一的，车辆管理所应当注销其机动车驾驶证：（七）超过机动车驾驶证有效期一年以上未换证的。", }, @"3595" : @{ @"question" : @"年龄在60岁以上持有准驾车型为大型客车的驾驶人，应当到驾驶证核发地车辆管理所换领准驾车型为小型汽车或小型自动挡汽车的驾驶证。", @"explain" : @"《公安部令第123号》第五十条：年龄在60周岁以上的，不得驾驶大型客车、牵引车、城市公交车、中型客车、大型货车、无轨电车和有轨电车；持有大型客车、牵引车、城市公交车、中型客车、大型货车驾驶证的，应当到机动车驾驶证核发地车辆管理所换领准驾车型为小型汽车或者小型自动挡汽车的机动车驾驶证。", }, }; } + (NSArray *)updateIdArr { return @[@"332", @"466", @"521", @"3960", @"3962", @"3976", @"2738", @"2740", @"2754", @"3627", @"3631", @"3595"]; } @end