YYXC_Student/jiaPei/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 {
    NSLog(@"%@",dataBasePath);
    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);"];
            
            //模拟学时表
            [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS mnPeriod_record (userId text, classId text, classCode text, trainNum text, maxSpeed text, mileage text, gps text);"];
            
            //大车学时表
            [shareDataBase executeUpdate:@"CREATE TABLE IF NOT EXISTS dcPeriod_record (userId text, classId text, classCode text, trainNum text, maxSpeed text, mileage text, gps text);"];
            
            //这个是不用写的 理论学时表 数据库有
            [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"]];
            }
        }
        [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";
//            }
            
            [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],trainRecord.subject,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)deleteTrain:(TrainRecord *)trainRecord {
    shareDataBase = [DB_Helper createDataBase];
    if([shareDataBase open]){
        [shareDataBase executeUpdate:[NSString stringWithFormat:@"delete from train_record where student_id = '%@' and begin_time = '%@' and classid = '%@'",trainRecord.studentId,[DES3Util encrypt:trainRecord.beginTime],trainRecord.classid]];
        [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;
}

+ (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];
    }
}


@end