JSJP_Student/jiaPei/OLD/Util/Sqlite/DB_Helper.m

//
//  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