WSCouldStorage/WSCloudStorage/CloudStorage/DB/CloudStorageDB.m

//
//  CloudStorageDB.m
//  zhuxun
//
//  Created by winsoft on 17/6/22.
//
//

#import "CloudStorageDB.h"
#import "DBOperation.h"
#import <MagicalRecord/MagicalRecord.h>
#import <MJExtension/MJExtension.h>
#import "DBConfigureCenter.h"
#import <objc/runtime.h>
@interface CloudStorageDB()
@property (nonatomic , strong) DBOperation *dbOperation;
@end
@implementation CloudStorageDB

static CloudStorageDB *cloudStorageDB;

+ (instancetype)shareInstance
{
    static dispatch_once_t onceToken;
    
    dispatch_once(&onceToken, ^{
        cloudStorageDB = [[self alloc] init];
    });
    
    return cloudStorageDB;

}

- (instancetype)init
{
    if (self = [super init]) {
        
        
        NSString *localPath = [NSPersistentStore MR_defaultLocalStoreUrl].absoluteString.stringByDeletingLastPathComponent;
        NSString *path = [NSString stringWithFormat:@"%@/%@_cloudStorageDisk.sqlite",localPath,Login_Name];
        
        //创建表
        self.dbOperation = [DBOperation dbOperationWithPath:path];
        
        [self.dbOperation creatDBWithTableName:cacheFileList dbModelClass:[CSFileModel class] Path:path autoUpgrade:YES];
        
        [self.dbOperation creatDBWithTableName:downTransFileList dbModelClass:[CSFileDownUpModel class] Path:path autoUpgrade:YES];
        
        [self.dbOperation creatDBWithTableName:upTransFileList dbModelClass:[CSFileDownUpModel class] Path:path autoUpgrade:YES];

    }
    
    return self;
}

//建立索引
- (void)createIndexForTableName:(NSString *)name indexCloums:(NSArray<NSArray*> *)indexCloums
{
    for (NSString *cloum in indexCloums) {
        NSString *sql = [NSString stringWithFormat:@"create index %@_%@ on %@(%@)",name,cloum,name,cloum];
        [self.dbOperation addDataToDBWithSQL:sql];
    }
}

- (void)addcsFilestoDB:(NSArray *)csFilesArray
{
    for (CSFileModel *csFileModel in csFilesArray) {
        [self addcsFiletoDB:csFileModel];
    }
}
//增加一条数据到数据库中
- (void)addcsFiletoDB:(CSFileModel *)csFile
{
    if (csFile.isdir) {
        [self getFileModelWithFileID:csFile.sid resultBlock:^(CSFileModel *csFileModel) {
            
            if (!csFileModel) {
                [self addDataDBWithTableName:@"cacheFileList" model:csFile];
            }
            
        }];
    }else{
    
        //根据pid,md5,name或者sid即可.
        [self getFileModelWithFileMD5:csFile.md5 resultBlock:^(CSFileModel *csFileModel) {
            
            if (!csFileModel) {
                [self addDataDBWithTableName:@"cacheFileList" model:csFile];
            }
        }];
    }
    
    
}

- (void)addCSDownUpFileToDB:(CSFileDownUpModel *)csDownUpFile
{
    if (!csDownUpFile.pid) {
        csDownUpFile.pid = @"0";
    }
  //外部调用前请自身判断是否由相同的数据存在.
    if (csDownUpFile.downUpType == DownUpModelDown) {
        [self addDataDBWithTableName:downTransFileList model:csDownUpFile];
    }else [self addDataDBWithTableName:upTransFileList model:csDownUpFile];
   
    
    //先通过md5和updownmodel判断文件是否在数据库内,如果是则更新数据,否则add.
//    
//    if (csDownUpFile.downUpType == DownUpModelDown) {
//        [self addDataDBWithTableName:downTransFileList model:csDownUpFile];
//    }else [self addDataDBWithTableName:upTransFileList model:csDownUpFile];
}

- (void)updateCloudFileToDB:(CSFileModel *)csFile
{
    
}

#pragma mark -- 2017-07-26 文件迁移 serverFullPath未处理 --sql
- (void)updateCSFilestoDB:(NSArray *)csFileSids targetid:(NSString *)pid{
    NSMutableString * pidsstring = [NSMutableString string];
    NSUInteger idx = 0;
    NSUInteger arraycount = csFileSids.count;
    for (NSString *str in csFileSids) {
        [pidsstring appendString:[NSString stringWithFormat:@"'%@'",str]];
        idx++;
        if (idx<arraycount) {
            [pidsstring appendString:@","];
        }
    }
    //未处理 serverFullPath  
    NSString *sql = [NSString stringWithFormat:@"UPDATE cacheFileList set pid = %@ where sid in (%@)",pid,pidsstring];
    [self.dbOperation addDataToDBWithSQL:sql];
    

}
- (void)getAllCloudFilesWithPid:(NSString *)pid resultBlock:(void(^)(NSArray *result))resultBlock
{
     NSString *sql = [NSString stringWithFormat:@"select * from cacheFileList where pid = '%@'",pid];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
       NSArray *modelArray = [CSFileModel mj_objectArrayWithKeyValuesArray:array];
        
        resultBlock(modelArray);
    }];
}

//isFile
- (void)getFileModelWithFileMD5:(NSString *)md5 resultBlock:(void(^)(CSFileModel *csFileModel))resultBlock
{
    NSString *sql = [NSString stringWithFormat:@"select * from cacheFileList where md5 = '%@'",md5];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray[0]);
        }else resultBlock(nil);
        
    }];
}

//isDir
- (void)getFileModelWithFileID:(NSString *)ID resultBlock:(void(^)(CSFileModel *csFileModel))resultBlock
{
    NSString *sql = [NSString stringWithFormat:@"select * from cacheFileList where sid = '%@'",ID];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray[0]);
        }else resultBlock(nil);
        
    }];
}

- (void)getCSDownUpFileWithMD5:(NSString *)md5 downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(NSArray *csFileModels))resultBlock
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where md5 = '%@'",md5]:[NSString stringWithFormat:@"select * from upTransFileList where md5 = '%@'",md5];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
}

- (void)getCSDownUpFileWithMD5:(NSString *)md5 pid:(NSString *)pid name:(NSString *)name downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(CSFileDownUpModel *csFileModel))resultBlock
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where md5 = '%@' and pid = '%@' and name = '%@'",md5,pid,name]:[NSString stringWithFormat:@"select * from upTransFileList where md5 = '%@' and pid = '%@' and name = '%@'",md5,pid,name];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray[0]);
        }else resultBlock(nil);
        
    }];
}



- (void)getCSDownUpFileSameNameDifMd5WithMD5:(NSString *)md5 name:(NSString *)name downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(CSFileDownUpModel *csFileDownUpModel))resultBlock
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where md5 != '%@' and name = '%@'",md5,name]:[NSString stringWithFormat:@"select * from upTransFileList where md5 != '%@' and name = '%@'",md5,name];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray[0]);
        }else resultBlock(nil);
        
    }];
}

- (void)getProcessCSDownUpFileWithMD5:(NSString *)md5 name:(NSString *)name downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(NSArray *downUpModels))resultBlock
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where md5 != '%@' and name = '%@' and (downUpStatus = %d OR downUpStatus = %d)",md5,name,DownUpStatusWait,DownUpStatusDoing]:[NSString stringWithFormat:@"select * from upTransFileList where md5 != '%@' and name = '%@' and (downUpStatus = %d OR downUpStatus = %d)",md5,name,DownUpStatusWait,DownUpStatusDoing];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
}

- (void)getAllProcessOrWaitFileWithResultBlock:(void(^)(NSArray *downModels,NSArray *upModels))resultBlock;
{
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where downUpStatus <= %d and isdir = 0 order by downUpStatus desc",upTransFileList,DownUpStatusDoing];
    NSString *sql2 =  [NSString stringWithFormat:@"select * from %@ where downUpStatus <= %d  and isdir = 0 order by downUpStatus desc",downTransFileList,DownUpStatusDoing];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *upModels = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        [self.dbOperation quaryDBbySQL:sql2 result:^(NSMutableArray *array) {
            
            //dict array;
            NSArray *downModels = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
            
        
            if (resultBlock) {
                resultBlock(downModels, upModels);
            }
           
            
        }];

        
    }];
}

- (void)getAllCSDownUpFilesFoldersWithPid:(NSString *)pid  isdir:(NSInteger)isDir downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(NSArray *downUpModels))resultBlock
{
    /*
     select * from downTransFileList where pid= 'pid'
     
     union all
     
     select a.sid,a.pid,a.downUpUUID as Level from downTransFileList a
     
     inner join downTransFileList b on a.sid=b.pid
     
     inner join
     
     (
     
     select ParentID from tblCity group by ParentID
     
     ) c on a.ParentID=c.ParentID
     */
    //@"WITH RECURSIVE result(pid,sid) AS(SELECT * FROM %@  WHERE pid='%@' UNION  SELECT a.sid,a.pid FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result"//[DBConfigureCenter getKeysArrayStrWithClassName:[CSFileDownUpModel class]]
    
    NSString *tableName = (downUpModel == DownUpModelDown?downTransFileList:upTransFileList);
    
    NSString *sql = [NSString stringWithFormat:@"WITH RECURSIVE result(%@) AS(SELECT * FROM %@  WHERE pid='%@' UNION  SELECT a.* FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result",[DBConfigureCenter getKeysArrayStrWithClassName:[CSFileDownUpModel class]],tableName,pid,tableName];


    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        NSMutableArray *tempArray = [NSMutableArray array];
        
        for (CSFileDownUpModel *csFileDownUpModel in modelArray) {
            
            if (isDir == 0 && !csFileDownUpModel.isdir) {
                [tempArray addObject:csFileDownUpModel];
            }else if (isDir == 1 && csFileDownUpModel.isdir)
            {
                 [tempArray addObject:csFileDownUpModel];
            }else if(isDir == 2)
            {
                [tempArray addObject:csFileDownUpModel];
            }
        }
        
    
        
        if (tempArray.count) {
            resultBlock(tempArray);
        }else resultBlock(nil);
        
    }];

}

- (void)judgeFileFolderDownUpResultWithWithPid:(NSString *)pid downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(bool result, NSArray *totalDownUpFileModels))resultBlock
{
    NSString *tableName = (downUpModel == DownUpModelDown?downTransFileList:upTransFileList);
    
    NSString *sql = [NSString stringWithFormat:@"WITH RECURSIVE result(%@) AS(SELECT * FROM %@  WHERE pid='%@' UNION  SELECT a.* FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result",[DBConfigureCenter getKeysArrayStrWithClassName:[CSFileDownUpModel class]],tableName,pid,tableName];
  
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        BOOL result = YES;
        
        NSArray *tempArray = [modelArray filteredArrayUsingPredicate:[NSPredicate predicateWithFormat:@"isdir = 0"]];
        
        for (CSFileDownUpModel *csFileDownUpModel in modelArray) {

            if (csFileDownUpModel.downUpStatus != DownUpStatusSuccess && !csFileDownUpModel.isdir) {
                result = NO;
                break;
            }
        }
        
        resultBlock(result,tempArray);
        
        
    }];

}

- (void)getFolderFilesDownUpStatusWithPid:(NSString *)pid downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(DownUpStatus downUpStatus))resultBlock
{
    NSString *tableName = (downUpModel == DownUpModelDown?downTransFileList:upTransFileList);
    
    NSString *sql =  sql = [NSString stringWithFormat:@"WITH RECURSIVE result(%@) AS(SELECT * FROM %@  WHERE pid='%@' and isdir = 0 UNION  SELECT a.* FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result",[DBConfigureCenter getKeysArrayStrWithClassName:[CSFileDownUpModel class]],tableName,pid,tableName];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
#warning 2017-07-06
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        BOOL result = YES;
        for (CSFileDownUpModel *csFileDownUpModel in modelArray) {
            if (csFileDownUpModel.downUpStatus != DownUpStatusSuccess) {
                result  = NO;
            }
            
        }
        if (result) {
            resultBlock(DownUpStatusSuccess);
        }else resultBlock(DownUpStatusDoing);
        
    }];
}


- (void)getCSDownUpFolderWithID:(NSString *)sid isdir:(BOOL)isdir name:(NSString *)name category:(NSInteger)category  downUpModel:(DownUpModel)downUpModel  resultBlock:(void(^)(CSFileDownUpModel *csFileModel))resultBlock
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where sid = '%@' and name = '%@' and isdir = %d and file_category = %ld",sid,name,isdir,category]:[NSString stringWithFormat:@"select * from upTransFileList where sid != '%@' and name = '%@' and isdir = %d and file_category = %ld",sid,name,isdir,category];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray[0]);
        }else resultBlock(nil);
        
    }];
}

- (void)getCSDownUpFolderAndSetSameStatusWithID:(NSString *)sid downUpModel:(DownUpModel)downUpModel
{
    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where sid = '%@' and downUpStatus = %d",sid,DownUpStatusSuccess]:[NSString stringWithFormat:@"select * from upTransFileList where sid != '%@' and downUpStatus = %d",sid,DownUpStatusSuccess];
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
         
            CSFileDownUpModel *csFileDownUpModel = modelArray[0];
            
            NSString *sql = [NSString stringWithFormat:@"update %@ set downUpStatus = %d where sid = '%@'",csFileDownUpModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,csFileDownUpModel.downUpStatus,csFileDownUpModel.sid];
            [self.dbOperation addDataToDBWithSQL:sql];
        }
        
    }];
}

//+ (void)getCSDownUpFileWithMD5:(NSString *)md5 filename:(NSString *)filename downUpModel:(DownUpModel)downUpModel resultBlock:(void(^)(CSFileDownUpModel *csFileModel))resultBlock
//{
//    NSString *sql = downUpModel == DownUpModelDown?[NSString stringWithFormat:@"select * from downTransFileList where md5 = '%@' and name = '%@'",md5,filename]:[NSString stringWithFormat:@"select * from upTransFileList where md5 = '%@' and name = '%@'",md5,filename];
//    [DBOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
//        
//        //dict array;
//        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
//        
//        if (modelArray.count) {
//            resultBlock(modelArray[0]);
//        }else resultBlock(nil);
//        
//    }];
//}

- (void)getSubFileFolderDownFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{
    NSString *sql = nil;
    
    if (!pid || [pid isEqualToString:@"0"]) {
        sql = [NSString stringWithFormat:@"select * from %@ where  file_category = 0",downTransFileList];
    }else sql = [NSString stringWithFormat:@"select * from %@ where  pid = '%@' and file_category != 0",downTransFileList,pid];
#warning 是否加入file_category != 0
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
    
}

- (void)getSubFileFolderDownSuccessFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{
    //文件and 子文件都成功的文件夹
    
    //先获取所有目标的文件和文件夹,再进行文件夹的判断.
    NSString *sql = nil;
    
    if (!pid || [pid isEqualToString:@"0"]) {
        sql = [NSString stringWithFormat:@"select * from %@ where  (downUpStatus = %d and file_category = 0) or (file_category = 0 and isdir = 1)",downTransFileList,DownUpStatusSuccess];
    }else sql = [NSString stringWithFormat:@"select * from %@ where (downUpStatus = %d and pid = '%@' and file_category != 0) or (pid = '%@' and file_category != 0 and isdir = 1)",downTransFileList,DownUpStatusSuccess,pid,pid];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        NSMutableArray *tempArray = [NSMutableArray array];
        
        for (CSFileDownUpModel *csFileDownUpModel in modelArray) {
            
            if (!csFileDownUpModel.isdir) {
                [tempArray addObject:csFileDownUpModel];
            }else{
            
                [self judgeFileFolderDownUpResultWithWithPid:csFileDownUpModel.sid downUpModel:csFileDownUpModel.downUpType resultBlock:^(bool result, NSArray *totalSubFileModels) {
                    
                    
                    if (result) {
                        csFileDownUpModel.subFileDownUpModels = totalSubFileModels;
                        //success 动态调整.
                        csFileDownUpModel.downUpStatus = DownUpStatusSuccess;
                        [tempArray addObject:csFileDownUpModel];
                    }
                }];
            }
        }
        
        if (tempArray.count) {
            resultBlock(tempArray);
        }else resultBlock(nil);
        
    }];
}
- (void)getSubFileFolderDownUnFinishedFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{

    NSString *sql = nil;
    
    if (!pid || [pid isEqualToString:@"0"]) {
        sql = [NSString stringWithFormat:@"select * from %@ where  (downUpStatus != %d and file_category = 0 and isdir = 0) or (file_category = 0 and isdir = 1)",downTransFileList,DownUpStatusSuccess];
    }else sql = [NSString stringWithFormat:@"select * from %@ where (downUpStatus != %d and pid = '%@' and file_category != 0 and isdir = 0) or (pid = '%@' and file_category != 0 and isdir = 1)",downTransFileList,DownUpStatusSuccess,pid,pid];
    
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        NSMutableArray *tempArray = [NSMutableArray array];
        
        for (CSFileDownUpModel *csFileDownUpModel in modelArray) {
            
            if (!csFileDownUpModel.isdir) {
                [tempArray addObject:csFileDownUpModel];
            }else{
                
                [self judgeFileFolderDownUpResultWithWithPid:csFileDownUpModel.sid downUpModel:csFileDownUpModel.downUpType resultBlock:^(bool result,NSArray *totalSubFileModels) {
                
                    if (!result) {
                        csFileDownUpModel.subFileDownUpModels = totalSubFileModels;
                        [tempArray addObject:csFileDownUpModel];
                    }
                }];
            }
        }
        
        if (tempArray.count) {
            resultBlock(tempArray);
        }else resultBlock(nil);
        
    }];
}



- (void)getSubFileFolderUpFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{
    NSString *sql = nil;
    
    if (!pid || [pid isEqualToString:@"0"]) {
        
        sql = [NSString stringWithFormat:@"select * from %@ where file_category = 0",upTransFileList];
        
    }else sql = [NSString stringWithFormat:@"select * from %@ where pid = '%@'",upTransFileList,pid];
    
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
}

- (void)getSubFileFolderUpSuccessFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{
     NSString *sql = nil;
    if (!pid || [pid isEqualToString:@"0"]) {
        
        sql = [NSString stringWithFormat:@"select * from %@ where downUpStatus = %d and file_category = 0",upTransFileList,DownUpStatusSuccess];
        
    }else sql = [NSString stringWithFormat:@"select * from %@ where downUpStatus = %d and pid = '%@'",upTransFileList,DownUpStatusSuccess,pid];
   
  
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
    
}

- (void)getSubFileFolderUpUnFinishedFileWithPid:(NSString *)pid ResultBlock:(void(^)(NSArray *result))resultBlock
{
    NSString *sql = nil;
    if (!pid || [pid isEqualToString:@"0"]) {
        
        sql = [NSString stringWithFormat:@"select * from %@ where downUpStatus != %d and file_category = 0",upTransFileList,DownUpStatusSuccess];
        
    }else sql = [NSString stringWithFormat:@"select * from %@ where downUpStatus != %d and pid = '%@'",upTransFileList,DownUpStatusSuccess,pid];
        
    [self.dbOperation quaryDBbySQL:sql result:^(NSMutableArray *array) {
        
        // 排序doing的在前.
        //dict array;
        NSArray *modelArray = [CSFileDownUpModel mj_objectArrayWithKeyValuesArray:array];
        
        if (modelArray.count) {
            resultBlock(modelArray);
        }else resultBlock(nil);
        
    }];
}


- (void)deleteCSFileToDB:(CSFileModel *)csFile
{
    
}

- (void)deleteCSDownUpFiles:(NSArray *)csFiles ResultBlock:(void(^)(NSArray *fileResult))resultBlock;
{
    NSString *tableName = nil;
    NSMutableArray *tempAllFilesArray = [NSMutableArray array];
    
    int i = 0;
    for (CSFileDownUpModel *downUpFileModel in csFiles) {
        
        
//        NSString *sql = [NSString stringWithFormat:@"delete from %@ where downUpUUID = '%@'",downUpFileModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,downUpFileModel.downUpUUID];
//        
//        [DBOperation addDataToDBWithSQL:sql];
        
        //待优化.//@"WITH RECURSIVE result(%@) AS(SELECT * FROM %@  WHERE pid='%@' UNION  SELECT a.* FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result"
        if (downUpFileModel.isdir) {
            
            //删除本身.
            NSString *sql = [NSString stringWithFormat:@"delete from %@ where downUpUUID = '%@'",downUpFileModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,downUpFileModel.downUpUUID];
    
            [self.dbOperation addDataToDBWithSQL:sql];
            
            [self getAllCSDownUpFilesFoldersWithPid:downUpFileModel.sid isdir:2 downUpModel:downUpFileModel.downUpType resultBlock:^(NSArray *downUpModels) {
                
                int j = 0;
                for (CSFileDownUpModel *subdownUpFileModel in downUpModels) {
                    
                    //@"WITH RECURSIVE result(%@) AS(SELECT * FROM %@  WHERE pid='%@' UNION  SELECT a.* FROM %@ a,result b WHERE b.sid = a.pid)SELECT * from result"
                    if (subdownUpFileModel.isdir) {
                        NSString *subsql = [NSString stringWithFormat:@"delete from %@ where downUpUUID = '%@'",subdownUpFileModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,subdownUpFileModel.downUpUUID];
                        
                        [self.dbOperation addDataToDBWithSQL:subsql];
                    }else{
                    
                        //删除同name和md5的文件
                        NSString *subsql = [NSString stringWithFormat:@"delete from %@ where md5 = '%@' and name = '%@'",subdownUpFileModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,subdownUpFileModel.md5,subdownUpFileModel.name];
                        
                        [self.dbOperation addDataToDBWithSQL:subsql];
                        
                        [tempAllFilesArray addObject:subdownUpFileModel];
                        
                        if (i >= csFiles.count - 1  && j >= downUpModels.count - 1) {

                            if (resultBlock) {
                                resultBlock(tempAllFilesArray);
                            }
                        }
                    }
                    
                    j++;
                   
                    
                }
                
            }];
            
            
        }else{
            
            //删除同name和md5的文件
            NSString *sql = [NSString stringWithFormat:@"delete from %@ where md5 = '%@' and name = '%@'",downUpFileModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,downUpFileModel.md5,downUpFileModel.name];
            
            [self.dbOperation addDataToDBWithSQL:sql];
            
            [tempAllFilesArray addObject:downUpFileModel];
            
            if (i >= csFiles.count - 1) {
               
                if (resultBlock) {
                    resultBlock(tempAllFilesArray);
                }
            }
        }
        
        i++;
    }
    
    
}

- (void)updateCSFiletoDB:(CSFileModel *)csFile
{
    
}

- (void)updateCSDownUpFileStatusWithCSFileDownUpModel:(CSFileDownUpModel *)csFileDownUpModel
{
#warning md5 和name相同的文件,则会全部更新
    NSString *sql = nil;
    if (!csFileDownUpModel.isdir) {
        if (csFileDownUpModel.localRootPath) {
            sql = [NSString stringWithFormat:@"update %@ set downUpStatus = %d,downUpSize = %ld, localRootPath = '%@' where md5 = '%@' and name = '%@';",csFileDownUpModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,csFileDownUpModel.downUpStatus,csFileDownUpModel.downUpSize,csFileDownUpModel.localRootPath,csFileDownUpModel.md5,csFileDownUpModel.name];
        }else sql = [NSString stringWithFormat:@"update %@ set downUpStatus = %d,downUpSize = %ld where md5 = '%@' and name = '%@';",csFileDownUpModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,csFileDownUpModel.downUpStatus,csFileDownUpModel.downUpSize,csFileDownUpModel.md5,csFileDownUpModel.name];
    }else{
    
        sql = [NSString stringWithFormat:@"update %@ set downUpStatus = %d where sid = '%@'",csFileDownUpModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,csFileDownUpModel.downUpStatus,csFileDownUpModel.sid];
    }
   
    [self.dbOperation addDataToDBWithSQL:sql];
    
    //同时更新relationlist,如果downupsize不断累加=size-> 累加到父节点上.

    //获取父节点,如果父节点
    
    
    
}

- (void)updateCSDownUpFileIDWithCSFileDownUpModel:(CSFileDownUpModel *)csFileDownUpModel sid:(NSString *)sid
{
    NSString *sql = [NSString stringWithFormat:@"update %@ set sid = '%@' where downUpUUID = '%@';",csFileDownUpModel.downUpType == DownUpModelDown?downTransFileList:upTransFileList,sid,csFileDownUpModel.downUpUUID];
    
    [self.dbOperation addDataToDBWithSQL:sql];
}


#warning 待封装到自定义底层数据库.
- (void)addDataDBWithTableName:(NSString *)tableName model:(id)model
{
    Class cls = [model class];
    
    unsigned int ivarsCnt = 0;
    //　获取类成员变量列表，ivarsCnt为类成员数量
    Ivar *ivars = class_copyIvarList(cls, &ivarsCnt);
    
    //　遍历成员变量列表，其中每个变量都是Ivar类型的结构体
    int i = 0;
    NSMutableArray *keys = [NSMutableArray array];
    NSMutableArray *values = [NSMutableArray array];
//    NSMutableArray *valueSqlSymbol = [NSMutableArray array];
    for (const Ivar *p = ivars; p < ivars + ivarsCnt; ++p)
    {
        
        Ivar const ivar = *p;
        
        //　获取变量名
        NSString *key = [NSString stringWithUTF8String:ivar_getName(ivar)];
        // 若此变量未在类结构体中声明而只声明为Property，则变量名加前缀 '_'下划线
        // 比如 @property(retain) NSString *abc;则 key == _abc;
        
        //　获取变量值
        id value = [model valueForKey:key];
        //        id value = [self valueForKey:key];
        
        //　取得变量类型
        // 通过 type[0]可以判断其具体的内置类型
        //        const char *type = ivar_getTypeEncoding(ivar);
        
        if(value)
        {
            BOOL strValue = NO;
            if ([value isKindOfClass:[NSString class]]) {
                strValue = YES;
                NSString *strValue = value;
                if (!strValue.length) {
                    continue;
                }
            }
            
            [keys addObject:[key substringFromIndex:1]];
            
            //字符串需要新增一个'',如果是字典对象呢?
            if (strValue) {
                [values addObject:[NSString stringWithFormat:@"'%@'",value]];
            }else [values addObject:value];
            
//            [valueSqlSymbol addObject:@"?"];
        }
        
        i++;
    }
    
    free(ivars);
    
    NSString *fileKeyListStr = [keys componentsJoinedByString:@","];
    NSString *fileValueListStr = [values componentsJoinedByString:@","];
//    NSString *sqlValueListStr = [valueSqlSymbol componentsJoinedByString:@","];
    
#warning 2017-06-23...
    NSString *SQL = [NSString stringWithFormat:@"insert into %@ (%@) values(%@)",tableName,fileKeyListStr,fileValueListStr];
    [self.dbOperation addDataToDBWithSQL:SQL];

}

- (void)updateDataDBWithTableName:(NSString *)tableName model:(id)model whereSql:(NSString *)whereSql
{
    /*
     UPDATE table_name
     SET column1 = value1, column2 = value2...., columnN = valueN
     WHERE [condition];
     */
    
    //    sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
}

- (void)deleteDataDBWIthTableName:(NSString *)tableName whereSql:(NSString *)whereSql
{
    //if wheresql = nil, clear all table
    
    //    sqlite> DELETE FROM COMPANY WHERE SQL;
}
@end