// // DB_Helper.m // Texts // // Created by tongjun on 13-10-12. // Copyright (c) 2013年 tongjun. All rights reserved. // #import "DB_Helper.h" #import "Question.h" #import "DES3Util.h" #import "Migration.h" static FMDatabase *shareDataBase = nil; @interface DB_Helper () @end @implementation DB_Helper /** 创建数据库类的单例对象 */ + (FMDatabase *)createDataBase { if ([[NSFileManager defaultManager] fileExistsAtPath:dataBasePath]) { }else{ [DB_Helper copyFileDatabase]; } //transrules.db 这个库 static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ shareDataBase = [FMDatabase databaseWithPath:dataBasePath]; if ([shareDataBase open]) { //本地成绩表 [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS grades (km text, score text, startDate text, endDate text, state text, isUpload text, subject text);"]; //实操学时表 [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS scPeriod_record (userId text, classId text, classCode text, trainNum text, maxSpeed text, mileage text, gps text, obdRotationSpeed text, obdMileage text);"]; //判断字段是否存在 老学员的表中没有这个字段 这里判断添加 并赋初始值 if (![shareDataBase columnExists:@"obdRotationSpeed" inTableWithName:@"scPeriod_record"]){ //这个方法第一次用必进的 [shareDataBase executeUpdate:[NSString stringWithFormat:@"ALTER TABLE scPeriod_record ADD obdRotationSpeed text"]]; [shareDataBase executeUpdate:[NSString stringWithFormat:@"update scPeriod_record set obdRotationSpeed = '0'"]]; } if (![shareDataBase columnExists:@"obdMileage" inTableWithName:@"scPeriod_record"]){ //这个方法第一次用必进的 [shareDataBase executeUpdate:[NSString stringWithFormat:@"ALTER TABLE scPeriod_record ADD obdMileage text"]]; [shareDataBase executeUpdate:[NSString stringWithFormat:@"update scPeriod_record set obdMileage = '0'"]]; } //这个是不用写的 理论学时表 数据库有 [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS train_record (student_id text, begin_time text, end_time text, train_time text, state text, subject text, classid text);"]; if (![shareDataBase columnExists:@"subject" inTableWithName:@"train_record"]){ //这个方法第一次用必进的 [shareDataBase executeUpdate:[NSString stringWithFormat:@"ALTER TABLE train_record ADD subject text"]]; [shareDataBase executeUpdate:[NSString stringWithFormat:@"update train_record set subject = '1'"]]; } if (![shareDataBase columnExists:@"classid" inTableWithName:@"train_record"]){ //这个方法第一次用必进的 [shareDataBase executeUpdate:[NSString stringWithFormat:@"ALTER TABLE train_record ADD classid text"]]; } // // FMDBMigrationManager 创建 // FMDBMigrationManager * manager = [FMDBMigrationManager managerWithDatabaseAtPath:dataBasePath migrationsBundle:[NSBundle mainBundle]]; // // sql语句添加到数组的形式，就是可以写多条。 // // 版本一 //// Migration * migration_1 = [[Migration alloc]initWithName:@"新增USer表" andVersion:1 andExecuteUpdateArray:@[@"create table User(name text,age integer,sex text,phoneNum text)"]]; //// [manager addMigration:migration_1]; // // 版本二 // Migration * migration_2 = [[Migration alloc]initWithName:@"train_record表新增字段classid" andVersion:2 andExecuteUpdateArray:@[@"alter table train_record add classid text"]]; // [manager addMigration:migration_2]; // // // 创建版本号表 // // 执行完该语句，再去我们的数据库中查看，会发现多了一个表 schema_migrations // BOOL resultState = NO; // NSError *error = nil; // if (!manager.hasMigrationsTable) { // resultState = [manager createMigrationsTable:&error]; // } // // UINT64_MAX 表示升级到最高版本 // resultState = [manager migrateDatabaseToVersion:UINT64_MAX progress:nil error:&error]; } [shareDataBase close]; }); return shareDataBase; } /** 关闭数据库 **/ + (void)closeDataBase { if(![shareDataBase close]) { NSLog(@"数据库关闭异常，请检查"); return; } } //拷贝数据库 +(void)copyFileDatabase { NSArray *paths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory,NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *documentLibraryFolderPath = [documentsDirectory stringByAppendingPathComponent:dataBaseName]; if ([[NSFileManager defaultManager] fileExistsAtPath:documentLibraryFolderPath]) { // NSLog(@"文件已经存在了"); }else { NSString *resourceSampleImagesFolderPath =[[NSBundle mainBundle]pathForResource:@"transrules" ofType:@"db"]; NSData *mainBundleFile = [NSData dataWithContentsOfFile:resourceSampleImagesFolderPath]; [[NSFileManager defaultManager] createFileAtPath:documentLibraryFolderPath contents:mainBundleFile attributes:nil]; } } #pragma mark 理论题目练习成绩表 /**创建grade表。 (sfzmhm text, km text , score text,startDate text,endDate text,subject text) */ +(void)saveGrade:(Grade*)aGrade { if (!aGrade) { aGrade = [Grade new]; } // NSLog(@"graScore ->%@",aGrade.graScore); if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into grades (km,score,startDate,endDate,state,isUpload)values('%@','%@','%@','%@','0','%@')",myDelegate.subject,aGrade.graScore,aGrade.startDate,aGrade.endDate,aGrade.isUpdate]]; } // NSLog(@"savePath->%@",dataBasePath); [shareDataBase close]; } +(NSArray *)quearyGrade { NSMutableArray *trains=[[NSMutableArray alloc]init]; shareDataBase=[DB_Helper createDataBase]; if([shareDataBase open]){ FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from grades where state = '0' and isUpload = '1'"]]; while([s next]){ Grade *train=[[Grade alloc]init]; train.subject=[s stringForColumn:@"km"]; train.startDate=[s stringForColumn:@"startDate"]; train.endDate=[s stringForColumn:@"endDate"]; train.graScore=[s stringForColumn:@"score"]; [trains addObject:train]; } } return trains; } +(void)updateGrade:(Grade *)aGrade{ shareDataBase=[DB_Helper createDataBase]; if([shareDataBase open]){ [shareDataBase executeUpdate:[NSString stringWithFormat:@"update grades set state='1' where startDate='%@'",aGrade.startDate]]; [shareDataBase close]; } } #pragma mark 理论学时表 /** 貌似不需要sfzmhm。如果用户还未登录是没有这个sfzmhm的。 在上传时，只需要搜索state为0的记录即可 全部上传。 */ +(void)saveTrain:(TrainRecord *)trainRecord { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { //此查询有开始时间 不必用科目再查 FMResultSet *s = [shareDataBase executeQuery:[NSString stringWithFormat:@"SELECT count(*) FROM train_record WHERE begin_time='%@' and student_id=%@",[DES3Util encrypt:trainRecord.beginTime],trainRecord.studentId]]; int count=0; if ([s next]) { count=[[s stringForColumnIndex:0]intValue]; } if (count==0) { NSString *pxkm = RQ_USER_MANAGER.currentUser.pxjd; if (pxkm.length < 1) { pxkm = @"1"; } // if ([pxkm isEqualToString:@"3"]) { // pxkm = @"4"; // } /// 理论计时类型一地市编号:3501,3503 (福州，莆田；在科目二，科目三可以进行科目四的理论计时) if ([RQ_SHARE_FUNCTION.theoryOfTimingTypeOneCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && ([pxkm isEqualToString:@"2"] || [pxkm isEqualToString:@"3"])) { pxkm = @"4"; } /// 理论计时类型二地市编号:null (暂无地市；在科目二可以进行科目四的理论计时) else if ([RQ_SHARE_FUNCTION.theoryOfTimingTypeTwoCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && [pxkm isEqualToString:@"2"]) { pxkm = @"4"; } /// 理论计时类型三地市编号:3502 (厦门；在科目三可以进行科目四的理论计时) else if ([RQ_SHARE_FUNCTION.theoryOfTimingTypeThreeCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && [pxkm isEqualToString:@"3"]) { pxkm = @"4"; } /// 新增地市 (新增地市；默认在科目二，科目三可以进行科目四的理论计时) else if (![RQ_SHARE_FUNCTION.theoryOfTimingTypeOneCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && ![RQ_SHARE_FUNCTION.theoryOfTimingTypeTwoCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && ![RQ_SHARE_FUNCTION.theoryOfTimingTypeThreeCityCodeArr containsObject:RQ_USER_MANAGER.currentUser.city] && ([pxkm isEqualToString:@"2"] || [pxkm isEqualToString:@"3"])) { pxkm = @"4"; } if (RQ_USER_MANAGER.isCykh && RQStringIsNotEmpty(RQ_USER_MANAGER.cykhPxkmStr)) { pxkm = RQ_USER_MANAGER.cykhPxkmStr; } [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into train_record (student_id,begin_time,end_time,train_time,subject,classid)values('%@','%@','%@','%@','%@', '%@')",trainRecord.studentId,[DES3Util encrypt:trainRecord.beginTime],[DES3Util encrypt:trainRecord.endTime],[DES3Util encrypt:trainRecord.trainTime],pxkm,trainRecord.classid]]; } else{ [shareDataBase executeUpdate:[NSString stringWithFormat:@"update train_record set end_time='%@',train_time='%@' where begin_time='%@' and student_id=%@ and classid=%@",[DES3Util encrypt:trainRecord.endTime],[DES3Util encrypt:trainRecord.trainTime],[DES3Util encrypt:trainRecord.beginTime],trainRecord.studentId, trainRecord.classid]]; } [shareDataBase close]; } } +(NSArray *)quearyTrain:(NSString *)stuId Subject:(NSString *)sub{ //NSLog(@"quearyTrain--sub----><>%@",sub); NSMutableArray *trains=[[NSMutableArray alloc]init]; shareDataBase=[DB_Helper createDataBase]; if([shareDataBase open]){ FMResultSet *s=[shareDataBase executeQuery:[NSString stringWithFormat:@"select * from train_record where state=0 and student_id=%@ and subject = '%@'",stuId,sub]]; while([s next]){ TrainRecord *train = [[TrainRecord alloc]init]; train.studentId = [s stringForColumn:@"student_id"]; train.beginTime = [DES3Util decrypt:[s stringForColumn:@"begin_time"]]; train.endTime = [DES3Util decrypt:[s stringForColumn:@"end_time"]]; train.state = [s stringForColumn:@"state"]; train.trainTime = [DES3Util decrypt:[s stringForColumn:@"train_time"]]; train.subject = [s stringForColumn:@"subject"]; train.classid = [s stringForColumn:@"classid"]; [trains addObject:train]; } [shareDataBase close]; } return trains; } +(void)updateTrainState:(TrainRecord *)trainRecord{ shareDataBase=[DB_Helper createDataBase]; if([shareDataBase open]){ [shareDataBase executeUpdate:[NSString stringWithFormat:@"update train_record set state=1 where begin_time='%@' and student_id=%@",[DES3Util encrypt:trainRecord.beginTime],trainRecord.studentId]]; [shareDataBase close]; } } + (void)deleteTrainRecord:(TrainRecord *)trainRecord { shareDataBase = [DB_Helper createDataBase]; if([shareDataBase open]){ [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from train_record where end_time='%@'and train_time='%@' and begin_time='%@' and student_id=%@ and classid=%@",[DES3Util encrypt:trainRecord.endTime],[DES3Util encrypt:trainRecord.trainTime],[DES3Util encrypt:trainRecord.beginTime],trainRecord.studentId, trainRecord.classid]]; [shareDataBase close]; } } #pragma mark 实操计时表 //scPeriod_record (userId text, classId text, classCode text, trainNum text, maxSpeed text, mileage text, gps text, obdRotationSpeed text, obdMileage text) + (void)saveSCTrainWithDic:(NSDictionary *)dic { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into scPeriod_record (userId,classId,classCode,trainNum,maxSpeed,mileage,gps,obdRotationSpeed,obdMileage)values(%@,'%@','%@','%@','%@','%@','%@','0','0')",RQ_USER_MANAGER.currentUser._id,dic[@"classId"],[DES3Util encrypt:dic[@"classCode"]],[DES3Util encrypt:dic[@"trainNum"]],dic[@"maxSpeed"],dic[@"mileage"],dic[@"gps"]]]; [shareDataBase close]; } } + (void)deleteSCTrainWithTrainNum:(NSString *)trainNum { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from scPeriod_record where userId = '%@' and trainNum = '%@'",RQ_USER_MANAGER.currentUser._id,[DES3Util encrypt:trainNum]]]; [shareDataBase close]; } } + (void)deleteSCTrainWithClassId:(NSString *)classId { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from scPeriod_record where userId = '%@' and classId = '%@'",RQ_USER_MANAGER.currentUser._id,classId]]; [shareDataBase close]; } } +(void)deleteAllSCTrain { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from scPeriod_record where userId = '%@'",RQ_USER_MANAGER.currentUser._id]]; [shareDataBase close]; } } + (NSArray *)quearySCTrainWithClassId:(NSString *)classId { NSMutableArray *scTrains = [[NSMutableArray alloc]init]; shareDataBase = [DB_Helper createDataBase]; if([shareDataBase open]){ FMResultSet *s = [shareDataBase executeQuery:[NSString stringWithFormat:@"select * from scPeriod_record where userId = '%@' and classId = '%@'",RQ_USER_MANAGER.currentUser._id,classId]]; while([s next]){ //classId , classCode , trainNum , maxSpeed , mileage , gps NSMutableDictionary *dic = [NSMutableDictionary dictionary]; [dic setValue:[s stringForColumn:@"classId"] forKey:@"classId"]; [dic setValue:[s stringForColumn:@"maxSpeed"] forKey:@"maxSpeed"]; [dic setValue:[s stringForColumn:@"mileage"] forKey:@"mileage"]; [dic setValue:[s stringForColumn:@"gps"] forKey:@"gps"]; [dic setValue:[s stringForColumn:@"obdRotationSpeed"] forKey:@"obdRotationSpeed"]; [dic setValue:[s stringForColumn:@"obdMileage"] forKey:@"obdMileage"]; [dic setValue:[DES3Util decrypt:[s stringForColumn:@"classCode"]] forKey:@"classCode"]; [dic setValue:[DES3Util decrypt:[s stringForColumn:@"trainNum"]] forKey:@"trainNum"]; [scTrains addObject:dic]; } [shareDataBase close]; } return scTrains; } + (void)changeSCTrainWithTrainNum:(NSString *)trainNum RotationSpeed:(NSString *)rotationSpeed Speed:(NSString *)speed Mileage:(NSString *)mileage{ shareDataBase=[DB_Helper createDataBase]; if([shareDataBase open]){ [shareDataBase executeUpdate:[NSString stringWithFormat:@"update scPeriod_record set obdRotationSpeed='%@' and maxSpeed='%@' and obdMileage='%@' where trainNum='%@'",rotationSpeed,speed,mileage,[DES3Util encrypt:trainNum]]]; [shareDataBase close]; } } #pragma mark 实操/模拟/大车计时表 //scPeriod_record (userId text, classId text, classCode text, trainNum text, maxSpeed text, mileage text, gps text) + (void)saveTrainWithDic:(NSDictionary *)dic type:(NSString *)type { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { NSString *sql= [NSString stringWithFormat:@"insert into %@_record (userId,classId,classCode,trainNum,maxSpeed,mileage,gps)values(%@,'%@','%@','%@','%@','%@','%@')",type,RQ_USER_MANAGER.currentUser._id,dic[@"classId"],[DES3Util encrypt:dic[@"classCode"]],[DES3Util encrypt:dic[@"trainNum"]],dic[@"maxSpeed"],dic[@"mileage"],dic[@"gps"]]; BOOL zz = [shareDataBase executeUpdate:sql]; if (zz) { NSLog(@"写入成功"); }else{ NSLog(@"写入失败"); } [shareDataBase close]; } } + (void)deleteTrainWithTrainNum:(NSString *)trainNum type:(NSString *)type { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from %@_record where userId = '%@' and trainNum = '%@'",type,RQ_USER_MANAGER.currentUser._id,[DES3Util encrypt:trainNum]]]; [shareDataBase close]; } } + (void)deleteTrainWithClassId:(NSString *)classId type:(NSString *)type { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from %@_record where userId = '%@' and classId = '%@'",type,RQ_USER_MANAGER.currentUser._id,classId]]; [shareDataBase close]; } } +(void)deleteAllTrainWithType:(NSString *)type { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from %@_record where userId = '%@'",type,RQ_USER_MANAGER.currentUser._id]]; [shareDataBase close]; } } + (NSArray *)quearyTrainWithClassId:(NSString *)classId type:(NSString *)type { NSMutableArray *scTrains = [[NSMutableArray alloc]init]; shareDataBase = [DB_Helper createDataBase]; if([shareDataBase open]){ FMResultSet *s = [shareDataBase executeQuery:[NSString stringWithFormat:@"select * from %@_record where userId = '%@' and classId = '%@'",type,RQ_USER_MANAGER.currentUser._id,classId]]; NSLog(@"%@ \n FMResultSet_colounm: %d",s.query,s.columnCount); while([s next]){ //classId , classCode , trainNum , maxSpeed , mileage , gps NSMutableDictionary *dic = [NSMutableDictionary dictionary]; [dic setValue:[s stringForColumn:@"classId"] forKey:@"classId"]; [dic setValue:[s stringForColumn:@"maxSpeed"] forKey:@"maxSpeed"]; [dic setValue:[s stringForColumn:@"mileage"] forKey:@"mileage"]; [dic setValue:[s stringForColumn:@"gps"] forKey:@"gps"]; [dic setValue:[DES3Util decrypt:[s stringForColumn:@"classCode"]] forKey:@"classCode"]; [dic setValue:[DES3Util decrypt:[s stringForColumn:@"trainNum"]] forKey:@"trainNum"]; [scTrains addObject:dic]; } [shareDataBase close]; } return scTrains; } @end