// // 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" static FMDatabase *shareDataBase = nil; @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);"]; 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'"]]; } } [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 = defUser.userDict[@"pxjd"]; if (pxkm.length < 1) { pxkm = @"1"; } // if ([pxkm isEqualToString:@"3"]) { // pxkm = @"4"; // } if ([defUser.userDict[@"city"] isEqualToString:@"3501"] && [pxkm isEqualToString:@"3"]) { /// 福州学员在科目三阶段时可以打科目四学时 pxkm = @"4"; } else if ([defUser.userDict[@"city"] isEqualToString:@"3502"] && [pxkm isEqualToString:@"3"]) { /// 厦门学员在科目三阶段时可以打科目四学时 pxkm = @"4"; } else if ([defUser.userDict[@"city"] isEqualToString:@"3503"] && ([pxkm isEqualToString:@"2"] || [pxkm isEqualToString:@"3"])) { /// 莆田学员在科目二科目三阶段时可以打科目四学时 pxkm = @"4"; } [shareDataBase executeUpdate:[NSString stringWithFormat:@"insert into train_record (student_id,begin_time,end_time,train_time,subject)values('%@','%@','%@','%@','%@')",trainRecord.studentId,[DES3Util encrypt:trainRecord.beginTime],[DES3Util encrypt:trainRecord.endTime],[DES3Util encrypt:trainRecord.trainTime],pxkm]]; } else{ [shareDataBase executeUpdate:[NSString stringWithFormat:@"update train_record set end_time='%@',train_time='%@' where begin_time='%@' and student_id=%@",[DES3Util encrypt:trainRecord.endTime],[DES3Util encrypt:trainRecord.trainTime],[DES3Util encrypt:trainRecord.beginTime],trainRecord.studentId]]; } [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"]; [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]; } } #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')",defUser.userDict[@"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 = '%@'",defUser.userDict[@"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 = '%@'",defUser.userDict[@"id"],classId]]; [shareDataBase close]; } } +(void)deleteAllSCTrain { shareDataBase=[DB_Helper createDataBase]; if ([shareDataBase open]) { [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from scPeriod_record where userId = '%@'",defUser.userDict[@"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 = '%@'",defUser.userDict[@"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,defUser.userDict[@"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,defUser.userDict[@"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,defUser.userDict[@"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,defUser.userDict[@"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,defUser.userDict[@"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