JSJP_Student/jiaPei/Utils/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"

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