erpsuiteios/Apex Mobile/Apex Mobile/ApexMobileDB.m

//
//  ApexMobileDB.m
//  Apex Mobile
//
//  Created by Ray on 14-2-28.
//  Copyright (c) 2014年 United Software Applications, Inc. All rights reserved.
//

#import "ApexMobileDB.h"
#import "config.h"
@implementation ApexMobileDB
+ (int) get_recordid:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause
{
    //    [ApexMobileDB execSql: [NSString stringWithFormat: @"update fields_info set abandon = 1 where user ='%@'",user] db:db];
    return [self get_recordid:db table:tablename where:whereclause order:@"_id"];
}

+ (int) get_recordid:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause order:(NSString*) orderby
{
    int ret = -1;
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id from %@ where %@ order by %@",tablename,whereclause,orderby];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            ret = sqlite3_column_int(statement, 0);
            
            //            char *address = (char*)sqlite3_column_text(statement, 3);
            //            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
            
            
        }
        sqlite3_finalize(statement);
    }
    return ret;
    
}


+ (sqlite3*) get_db
{
    sqlite3* db = nil;
    
//    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documents = [paths objectAtIndex:0];
    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    DebugLog(@"home path %@",documents);
    if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_open failed. msg:%s",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    
//#ifdef DEBUG_DB
//    NSFileManager *manager = [NSFileManager defaultManager];
//
//    NSString *copyPath =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
//    copyPath = [copyPath stringByAppendingPathComponent:DBNAME];
//    [manager removeItemAtPath:copyPath error:nil];
//    BOOL isCopy = [manager copyItemAtPath:database_path toPath:copyPath error:nil];
//    if (isCopy) {
//        NSLog(@"拷贝成功");
//    } else {
//        NSLog(@"拷贝失败");
//    }
//#endif
    return db;
    
}

+(void)execSql:(NSString *)sql db:(sqlite3 *)db
{
    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_exec failed msg:%s",sqlite3_errmsg(db));
        DebugLog(@"sqlite3_exec failed sql:%@",sql);
        sqlite3_close(db);
        
    }
}
+(BOOL)checkForField:(NSString *)table field:(NSString *)field db:(sqlite3 *)db
{
    
    sqlite3_stmt *stmt;
    NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)",table];
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        while(sqlite3_step(stmt) == SQLITE_ROW)
        {
            
            NSString *fieldName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
            if([field isEqualToString:fieldName])
            {
                sqlite3_finalize(stmt);
                return YES;
            }
        }
    }
    

    
    return NO;
}
+(NSArray*) get_Location
{
    sqlite3* db = [self get_db ];
    NSMutableArray* ret = [[NSMutableArray alloc] init];
    NSString *sqlQuery = @"select area,company,city,longitude,latitude,address,telephone,fax,contact,email from locations";
    sqlite3_stmt * statement;
    NSMutableDictionary* map = [[NSMutableDictionary alloc] init];
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString* area =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
            NSString* company =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
            NSString* city =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
            NSString* longitude =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];

            NSString* latitude =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];

            NSString* address =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
            NSString* telephone =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
            NSString* fax =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
            NSString* contact =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
            NSString* email =[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];

            
            [map setValue:area forKey:@"area"];
            [map setValue:company forKey:@"company"];
            [map setValue:city forKey:@"city"];
            [map setValue:longitude forKey:@"longitude"];
            [map setValue:latitude forKey:@"latitude"];
            [map setValue:address forKey:@"address"];
            [map setValue:telephone forKey:@"telephone"];
            [map setValue:fax forKey:@"fax"];
            [map setValue:contact forKey:@"contact"];
            [map setValue:email forKey:@"email"];


            [ret addObject:map.copy];

            
            
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    return ret;
}
+(NSArray*) get_searchhistory:(NSString*) field
{
    sqlite3* db = [self get_db ];
    NSMutableArray* ret = [[NSMutableArray alloc] init];
    AppDelegate *appDelegate = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    NSString* user = appDelegate.user;
    NSString *sqlQuery = [NSString stringWithFormat:@"select h_val from search_history where h_field='%@' and user='%@' order by h_time desc",field,user];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            NSString *val = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
            [ret addObject:val];
            //            char *address = (char*)sqlite3_column_text(statement, 3);
            //            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
            
            
        }
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    
    return ret;
}
+(void) savehistory:(NSString*) field value:(NSString*) value
{
    sqlite3* db = [self get_db ];
    AppDelegate *appDelegate = (AppDelegate *)[[UIApplication sharedApplication] delegate];
    NSString* user = appDelegate.user;
    int _id = [self get_recordid:db table:@"search_history" where:[NSString stringWithFormat:@"user='%@' and h_field='%@' and h_val ='%@'",user,field,value]];
    if(_id>=0)
    {
        NSString* sql = [NSString stringWithFormat:@"update search_history set h_time = datetime('now', 'localtime') where _id =%d",_id];
        [self execSql:sql db:db];
    }
    else
    {
        NSString* sql = [NSString stringWithFormat:@"insert into search_history(user,h_field,h_val) values('%@','%@','%@')",user,field,value];
        [self execSql:sql db:db];
    }
    
    
    sqlite3_close(db);
}
+ (BOOL) initializeDb {
    DebugLog (@"initializeDB");
    
//    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
//    NSString *documents = [paths objectAtIndex:0];
//    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
//
//
//    // move db file from document to cache ;
//    NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//    NSString *documentFolderPath = [searchPaths objectAtIndex: 0];
//    NSString* dbFilePath = [documentFolderPath stringByAppendingPathComponent:DBNAME];
//    if ([[NSFileManager defaultManager] fileExistsAtPath: dbFilePath])
//    {
//        [[NSFileManager defaultManager] moveItemAtPath:dbFilePath toPath:database_path error:nil];
//
//    }
    // end move;
    
    // 2019.1.11 move db from cache to document
    NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject];
    NSString *docDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    
    NSString *db_cache = [cacheDir stringByAppendingPathComponent:DBNAME];
    NSString *db_doc = [docDir stringByAppendingPathComponent:DBNAME];
    if ([[NSFileManager defaultManager] fileExistsAtPath:db_cache]) {
        NSError *err;
        [[NSFileManager defaultManager] moveItemAtPath:db_cache toPath:db_doc error:&err];
        if (err) {
            NSLog(@"move database error: %@",err);
        }
    }
    
    
    
    if (true)//! [[NSFileManager defaultManager] fileExistsAtPath: database_path])
    {
        sqlite3 *db = [self get_db];
//        int ret = sqlite3_close(db);
//db = [self get_db];
//        
//        ret = sqlite3_close(db);
        NSString *create_actions_info = @"CREATE TABLE IF NOT EXISTS actions_info (_id integer PRIMARY KEY,name varchar(20),function_name varchar(20),priority integer,abandon boolean,user varchar(20));";
        
        NSString *create_fields_info = @"CREATE TABLE IF NOT EXISTS fields_info (_id integer PRIMARY KEY,name varchar(20),aname varchar(20),field_type integer,function_name varchar(20),behavior integer,priority integer,show boolean,abandon boolean,user varchar(20));";
        
        NSString *create_search_history = @"CREATE TABLE IF NOT EXISTS search_history (_id INTEGER PRIMARY KEY,h_val VARCHAR(20),h_field VARCHAR(20),level INTEGER,h_time TIMESTAMP default (datetime('now', 'localtime')),user VARCHAR(20));";
        
        NSString* create_push_message =@"CREATE TABLE IF NOT EXISTS push_message (_id INTEGER PRIMARY KEY, s_id VARCHAR(20), e_id VARCHAR(20), msgcount INTEGER, message VARCHAR(20), h_time timestamp default (datetime('now', 'localtime')), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20), read BOOLEAN);";
        
		NSString* create_favorites =@"CREATE TABLE IF NOT EXISTS favorites (_id INTEGER PRIMARY KEY, name VARCHAR(20), params VARCHAR(20), action VARCHAR(20), module_name VARCHAR(20), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20));";
		NSString* create_history=@"CREATE TABLE IF NOT EXISTS history ( _id INTEGER PRIMARY KEY, name VARCHAR(20), params VARCHAR(20), action VARCHAR(20), module_name VARCHAR(20), create_time timestamp default (datetime('now', 'localtime')), user VARCHAR(20));";
        
        
        NSString* create_location=@"CREATE TABLE IF NOT EXISTS locations ( _id INTEGER PRIMARY KEY, area VARCHAR(20), company VARCHAR(20), city VARCHAR(20), longitude VARCHAR(20), latitude VARCHAR(20) ,address VARCHAR(20),  telephone VARCHAR(20) , fax VARCHAR(20), contact VARCHAR(20), email VARCHAR(20));";
        
        
        NSString *create_auth_ver = @"create table if not exists auth_ver (_id integer primary key autoincrement, ver integer);";
        
        
        //        CREATE TABLE users (_id integer PRIMARY KEY,name varchar(20),pass varchar(20));
        [self execSql:create_actions_info db:db];
        [self execSql:create_fields_info db:db];
        [self execSql:create_search_history db:db];
        [self execSql:create_push_message db:db];
        [self execSql:create_favorites db:db];
        [self execSql:create_history db:db];
        [self execSql:create_location db:db];
        [self execSql:create_auth_ver db:db];
        
        
        if( ![self checkForField:@"search_history" field:@"level" db:db])
        {
            NSString* alter_search_history = @"ALTER TABLE search_history ADD level INTEGER";
            [self execSql:alter_search_history db:db];
        }
//        NSTimeInterval time=[[NSDate date] timeIntervalSince1970];
//        double t = time-2592000;      //NSTimeInterval返回的是double类型
//        NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
//        [formatter setDateFormat:@"yyyy-MM-dd"];
//
//        NSString*timestr=[formatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:t]];
//
    //    [self execSql:[NSString stringWithFormat:@"delete from search_history where h_time<%@",timestr ] db:db];
        [self execSql:@"delete from search_history where julianday('now', 'localtime')-julianday(h_time, 'localtime')>30" db:db];
        
        sqlite3_close(db);
//        int aaa = 0;
    }
    
    [self deleteResultFields];
    [self updateResultDisplayFields];
    
    return YES;
    
    DebugLog (@"bottom of initializeDb");
}

+ (void)jk_query:(NSString *)sql completion:(void (^)(sqlite3_stmt *, long *))query failure:(void (^)(NSString *))failure {
    
    if (sql.length == 0) {
        return;
    }
    
    dispatch_async(dispatch_get_main_queue(), ^{
        
        NSString *sqlQuery = sql;
        sqlite3_stmt * statement;
        sqlite3 *db = [self get_db];
        
        if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
            
            if (query) {
                
                long count = 0;
                while (sqlite3_step(statement) == SQLITE_ROW) {
                    query(statement,&count);
                    count++;
                }
            }
            
            sqlite3_finalize(statement);
        } else {
            DebugLog(@"excute sql:%@ error: %s",sql,sqlite3_errmsg(db));
            if (failure) {
                failure(@"error");
            }
            
        }
        
        sqlite3_close(db);
        
    });
}

+ (void)jk_excute:(NSString *)sql completion:(void (^)(BOOL))completion {
    if (sql.length == 0) {
        return;
    }
    
    dispatch_async(dispatch_get_main_queue(), ^{
        
        sqlite3 *db = [self get_db];
        char *err;
        if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) == SQLITE_OK) {
            if (completion) {
                completion(YES);
            }
        } else {
            DebugLog(@"excute sql:%@ error: %s",sql,sqlite3_errmsg(db));
            if (completion) {
                completion(NO);
            }
        }
        sqlite3_close(db);
    });
}

+ (void)jk_sync_query:(NSString *)sql completion:(void (^)(sqlite3_stmt *, long *))query failure:(void (^)(NSString *))failure {
    
    if (!sql) {
        if (failure) {
            failure(@"sql is nil");
        }
        return;
    }
    
    NSString *sqlQuery = sql;
    sqlite3_stmt * statement;
    sqlite3 *db = [self get_db];
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
        if (query) {
            
            long count = 0;
            while (sqlite3_step(statement) == SQLITE_ROW) {
                query(statement,&count);
                count++;
            }
        }
        
        sqlite3_finalize(statement);
    } else {
        DebugLog(@"excute sql:%@ error: %s",sql,sqlite3_errmsg(db));
        if (failure) {
            failure(@"error");
        }
        
    }
    
    sqlite3_close(db);
    
}

+ (void)jk_sync_excute:(NSString *)sql completion:(void (^)(BOOL))completion {
    
    if (sql.length == 0) {
        if (completion) {
            completion(NO);
        }
        return;
    }
    
    sqlite3 *db = [self get_db];
    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) == SQLITE_OK) {
        if (completion) {
            completion(YES);
        }
    } else {
        DebugLog(@"excute sql:%@ error: %s",sql,sqlite3_errmsg(db));
        if (completion) {
            completion(NO);
        }
    }
    sqlite3_close(db);
    
}

+ (NSString *)prepareUploadSQLOfUser:(NSString *)user withDiviceID:(NSString *)deviceID {
    
    NSMutableString *sqlStr = [NSMutableString string];
    sqlite3 *db = [self get_db];

    // favorites
    NSString *favorites_sql = [NSString stringWithFormat:@"select ifnull(name,''), ifnull(params, ''), ifnull(action, ''), ifnull(module_name, ''), ifnull(user, ''), ifnull(create_time, '')  from favorites where user = '%@';", user];
    sqlite3_stmt *favorites_statment;
    if (sqlite3_prepare_v2(db, favorites_sql.UTF8String, -1, &favorites_statment, NULL) == SQLITE_OK) {
        while (sqlite3_step(favorites_statment) == SQLITE_ROW) {
            
            char *ch_name = (char *)sqlite3_column_text(favorites_statment, 0);
            char *ch_params = (char *)sqlite3_column_text(favorites_statment, 1);
            char *ch_action = (char *)sqlite3_column_text(favorites_statment, 2);
            char *ch_module_name = (char *)sqlite3_column_text(favorites_statment, 3);
            char *ch_user = (char *)sqlite3_column_text(favorites_statment, 4);
            char *ch_create_time = (char *)sqlite3_column_text(favorites_statment, 5);
            
            NSString *str_name = [NSString stringWithUTF8String:ch_name];
            NSString *str_params = [NSString stringWithUTF8String:ch_params];
            NSString *str_action = [NSString stringWithUTF8String:ch_action];
            NSString *str_module_name = [NSString stringWithUTF8String:ch_module_name];
            NSString *str_user = [NSString stringWithUTF8String:ch_user];
            NSString *str_create_time = [NSString stringWithUTF8String:ch_create_time];
            
            NSString *insert_sql = [NSString stringWithFormat:@"insert into favorites (name, params, action, module_name, user_name, device_id, create_time) values ('%@', '%@', '%@', '%@', '%@', '%@', '%@');", str_name, str_params, str_action, str_module_name, str_user, deviceID, str_create_time];
            [sqlStr appendString:insert_sql];
            [sqlStr appendString:@"\r\n"];
        }
        sqlite3_finalize(favorites_statment);
    }
    
    // fields_info
    NSString *fieldsSql = [NSString stringWithFormat:@"select ifnull(name, ''), ifnull(aname, ''), ifnull(field_type, 0), ifnull(function_name, ''), ifnull(behavior, 0), ifnull(priority, 0), ifnull(show, 0), ifnull(user, '') from fields_info where user = '%@';",user];
    sqlite3_stmt *fields_statment;
    if (sqlite3_prepare_v2(db, fieldsSql.UTF8String, -1, &fields_statment, NULL) == SQLITE_OK) {
        while (sqlite3_step(fields_statment) == SQLITE_ROW) {
            
            char *ch_name = (char *)sqlite3_column_text(fields_statment, 0);
            char *ch_aname = (char *)sqlite3_column_text(fields_statment, 1);
            int field_type = sqlite3_column_int(fields_statment, 2);
            char *ch_function_name = (char *)sqlite3_column_text(fields_statment, 3);
            int behavior = sqlite3_column_int(fields_statment, 4);
            int priority = sqlite3_column_int(fields_statment, 5);
            int show = sqlite3_column_int(fields_statment, 6);
            char *ch_user = (char *)sqlite3_column_text(fields_statment, 7);
            
            NSString *str_name = [NSString stringWithUTF8String:ch_name];
            NSString *str_aname = [NSString stringWithUTF8String:ch_aname];
            NSString *str_function_name =  [NSString stringWithUTF8String:ch_function_name];
            NSString *str_user = [NSString stringWithUTF8String:ch_user];
            
            NSString *insert_sql = [NSString stringWithFormat:@"insert into fields_info (name, aname, field_type, function_name, behavior, priority, show, user_name, device_id) values ('%@', '%@', %d, '%@', %d, %d, %d, '%@', '%@');", str_name, str_aname, field_type, str_function_name, behavior, priority, show, str_user, deviceID];
            [sqlStr appendString:insert_sql];
            [sqlStr appendString:@"\r\n"];
        }
        sqlite3_finalize(fields_statment);
    }
    
    // history
    NSString *historySql = [NSString stringWithFormat:@"select ifnull(name, ''), ifnull(params, ''), ifnull(action, ''), ifnull(module_name, ''), ifnull(user, ''), ifnull(create_time, '') from history where user = '%@';",user];
    sqlite3_stmt *history_statment;
    if (sqlite3_prepare_v2(db, historySql.UTF8String, -1, &history_statment, NULL) == SQLITE_OK) {
        while (sqlite3_step(history_statment) == SQLITE_ROW) {
            char *ch_name = (char *)sqlite3_column_text(history_statment, 0);
            char *ch_params = (char *)sqlite3_column_text(history_statment, 1);
            char *ch_action = (char *)sqlite3_column_text(history_statment, 2);
            char *ch_module_name = (char *)sqlite3_column_text(history_statment, 3);
            char *ch_user = (char *)sqlite3_column_text(history_statment, 4);
            char *ch_create_time = (char *)sqlite3_column_text(history_statment, 5);
            
            NSString *str_name = [NSString stringWithUTF8String:ch_name];
            NSString *str_params = [NSString stringWithUTF8String:ch_params];
            NSString *str_action = [NSString stringWithUTF8String:ch_action];
            NSString *str_module_name = [NSString stringWithUTF8String:ch_module_name];
            NSString *str_user = [NSString stringWithUTF8String:ch_user];
            NSString *str_create_time = [NSString stringWithUTF8String:ch_create_time];
            
            NSString *insert_sql = [NSString stringWithFormat:@"insert into history (name, params, action, module_name, user_name, device_id, create_time) values ('%@', '%@', '%@', '%@', '%@', '%@', '%@');", str_name, str_params, str_action, str_module_name, str_user, deviceID, str_create_time];
            [sqlStr appendString:insert_sql];
            [sqlStr appendString:@"\r\n"];
        }
        
        sqlite3_finalize(history_statment);
    }
    
    // search history
    NSString *searchHistorySql = [NSString stringWithFormat:@"select ifnull(h_val, ''), ifnull(h_field, ''), ifnull(level, 0), ifnull(user, ''), ifnull(h_time, '') from search_history where user = '%@';",user];
    sqlite3_stmt *search_history_statment;
    if (sqlite3_prepare_v2(db, searchHistorySql.UTF8String, -1, &search_history_statment, NULL) == SQLITE_OK) {
        while (sqlite3_step(search_history_statment) == SQLITE_ROW) {
            char *ch_val = (char *)sqlite3_column_text(search_history_statment, 0);
            char *ch_field = (char *)sqlite3_column_text(search_history_statment, 1);
            int level = sqlite3_column_int(search_history_statment, 2);
            char *ch_user = (char *)sqlite3_column_text(search_history_statment, 3);
            char *ch_time = (char *)sqlite3_column_text(search_history_statment, 4);
            
            NSString *str_val = [NSString stringWithUTF8String:ch_val];
            NSString *str_field = [NSString stringWithUTF8String:ch_field];
            NSString *str_user = [NSString stringWithUTF8String:ch_user];
            NSString *str_time = [NSString stringWithUTF8String:ch_time];
            
            NSString *insert_sql = [NSString stringWithFormat:@"insert into search_history (h_val, h_field, level, user_name, device_id, h_time) values ('%@', '%@', %d, '%@', '%@', '%@');",str_val, str_field, level, str_user, deviceID, str_time];
            [sqlStr appendString:insert_sql];
            [sqlStr appendString:@"\r\n"];
        }
        
        sqlite3_finalize(search_history_statment);
    }
    
    sqlite3_close(db);
    
    return sqlStr.copy;
}

+ (BOOL)hasDatabaseFile {
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *documents = [paths objectAtIndex:0];
    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    
    if ([[NSFileManager defaultManager] fileExistsAtPath:database_path]) {
        return YES;
    }
    
    return NO;
}

+ (BOOL)isCachedDataForUser:(NSString *)user {
    
    BOOL hasDatabase = [self hasDatabaseFile];
    if (!hasDatabase) {
        return NO;
    }
    
    __block BOOL result = NO;
    
    NSString *has_sql = [NSString stringWithFormat:@"select count(0) from (select user from favorites union all select user from fields_info  union all select user from history union all select user from search_history) where user = '%@';", user];
    
    sqlite3_stmt * statement;
    sqlite3 *db = [self get_db];
    
    if (sqlite3_prepare_v2(db, [has_sql UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
        while (sqlite3_step(statement) == SQLITE_ROW) {
            int c = sqlite3_column_int(statement, 0);
            result = c > 0;
        }
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    
    return result;
}

+ (void)cleanCacheDataForUser:(NSString *)user {
    NSString *sql = [NSString stringWithFormat:@"delete from favorites where user = '%@';delete from fields_info where user = '%@';delete from history where user = '%@';delete from search_history where user = '%@';",user,user,user,user];
    [self jk_excute:sql completion:^(BOOL success) {
        
    }];
}

#pragma mark - Actions

+ (NSArray *)getActionsForFunction:(NSString *)function withUser:(NSString *)user {
    if (!function || !user) {
        return @[];
    }
    NSString *sql = [NSString stringWithFormat:@"select name from actions_info where function_name='%@' and user='%@'order by priority",function, user];
    
    __block NSMutableArray *actionArr = [NSMutableArray array];
    [self jk_sync_query:sql completion:^(sqlite3_stmt *stmt, long *count) {
        
        char *ch_name = (char *)sqlite3_column_text(stmt, 0);
        if (ch_name == NULL) {
            ch_name = "";
        }
        NSString *str_name = [NSString stringWithUTF8String:ch_name];
        [actionArr addObject:str_name];
        
    } failure:^(NSString *err_msg) {
        
    }];
    return [actionArr copy];
}

+ (NSString *)getDisplayFieldsForFunction:(NSString *)function withUser:(NSString *)user {
    
    if (!function || !user) {
        return @"";
    }
    
    NSString *sql = [NSString stringWithFormat:@"select name from fields_info where function_name='%@' and user='%@' and behavior=%d and show=1 order by priority,aname",function, user,BEHAVIOR_RESULT];
    
    __block NSString* fields = @"";
    [self jk_sync_query:sql completion:^(sqlite3_stmt *stmt, long *count) {
        
        NSString *name = [[NSString alloc] initWithUTF8String:(char *)sqlite3_column_text(stmt, 0)];
        fields = [fields stringByAppendingFormat:@"%@,",name];
        
        
    } failure:^(NSString *err_msg) {
        
    }];
    
    if (fields.length > 0) {
        fields=[fields substringToIndex: fields.length-1];
    }
    return fields;
}

+ (NSString *)getDisplayFieldsForFunction:(NSString *)function withUser:(NSString *)user limit:(NSUInteger)limit {
    
    if (!function || !user) {
        return @"";
    }
    
    NSString *sql = [NSString stringWithFormat:@"select name from fields_info where function_name='%@' and user='%@' and behavior=%d and show=1 order by priority,aname limit %lu",function, user,BEHAVIOR_RESULT, limit];
    
    __block NSString* fields = @"";
    [self jk_sync_query:sql completion:^(sqlite3_stmt *stmt, long *count) {
        
        NSString *name = [[NSString alloc] initWithUTF8String:(char *)sqlite3_column_text(stmt, 0)];
        fields = [fields stringByAppendingFormat:@"%@,",name];
        
        
    } failure:^(NSString *err_msg) {
        
    }];
    
    if (fields.length > 0) {
        fields=[fields substringToIndex: fields.length-1];
    }
    return fields;
}

// 数据库更新之后 和 数据库初始化完成 删除
+ (void)deleteResultFields {
    
    NSString *booking_sql = @"delete from fields_info where behavior = 1 and function_name = 'Ocean Booking' and name in ('booking_no','shipper','consignee','po_no','f_etd','m_eta','place_of_receipt_uncode','place_of_delivery_uncode');";

    NSString *bl_sql = @"delete from fields_info where behavior = 1 and function_name = 'Ocean B/L info.' and name in ('last_status_315_code','shipper','consignee','h_bol','etd','eta','po_no','place_of_receipt_un','place_of_delivery_un');";

    NSString *cn_sql = @"delete from fields_info where behavior = 1 and function_name = 'Container detail' and name in ('shipper','consignee','ctnr','file_no','po_no','etd','eta','fport_of_loading_un','mport_of_discharge_un');";

    sqlite3 *db = [self get_db];

    [self execSql:booking_sql db:db];
    [self execSql:bl_sql db:db];
    [self execSql:cn_sql db:db];

    sqlite3_close(db);
}

+ (void)updateResultDisplayFields {
    
    __block NSMutableArray *users = [NSMutableArray array];

    NSString *user_sql = @"select distinct user from fields_info;";
    [self jk_sync_query:user_sql completion:^(sqlite3_stmt *stmt, long *count) {

        char *user_ch = (char *)sqlite3_column_text(stmt, 0);
        NSString *user = [NSString stringWithUTF8String:user_ch];

        [users addObject:user];

    } failure:^(NSString *error) {

    }];

    NSArray *functions = @[@"Ocean Booking", @"Ocean B/L info.", @"Container detail"];
    NSUInteger maxDisplay = 3;

    sqlite3 *db = [self get_db];

    for (NSString *user in users) {

        for (NSString *function in functions) {

            NSString *sql = [NSString stringWithFormat:@"update fields_info set show = 0 where behavior = 1 and show = 1 and user = '%@' and function_name = '%@' and name in (select name from fields_info where function_name='%@' and user = '%@' and behavior=1 and show=1 order by priority,aname limit %d offset %lu);", user, function, function, user, INT_MAX, maxDisplay];

            [self execSql:sql db:db];
        }
    }
    sqlite3_close(db);
}

@end