erpsuiteios/RedAnt ERP Mobile/common/data_provider/iSalesDB.m

//
//  iSalesDB.m
//  RedAnt ERP Mobile
//
//  Created by Ray on 14-6-24.
//  Copyright (c) 2014年 United Software Applications, Inc. All rights reserved.
//

#import "iSalesDB.h"
#import "AESCrypt.h"
//#import "AppDelegate.h"
#import "RASingleton.h"

NSLock *dblock ;
int count_dbconn;
//int count_tmpdbconn;
@implementation iSalesDB

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

void offline_dirty(sqlite3_context *context, int argc, sqlite3_value **argv) {
//    AppDelegate *appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
    if(btrigger)
    {
    NSUserDefaults *defaults =[NSUserDefaults standardUserDefaults];
    [defaults removeObjectForKey:@"OFFLINE_DIRTY"];
    [defaults setBool:true forKey:@"OFFLINE_DIRTY"];
    [defaults synchronize];
    }
}
void encryptfield (sqlite3_context *context, int argc, sqlite3_value **argv) {
    assert(argc == 1);
    switch (sqlite3_value_type(argv[0])){
        case SQLITE_TEXT: {
            unsigned const char *string = sqlite3_value_text (argv[0]);
            
            //Get the Objective C string (much easier to manage)
            NSString *ocString = [[NSString alloc] initWithUTF8String:(char *) string] ;
            NSString* encrypt=[AESCrypt fastencrypt:ocString];
            //            //Split it on punctuation and spaces
            //            NSMutableCharacterSet *cset = [NSCharacterSet punctuationCharacterSet];
            //            [cset addCharactersInString:@" "];
            //            NSArray *tokens = [ocString componentsSeparatedByCharactersInSet:cset];
            //            //Sort it
            //            NSArray *sortedArray = [tokens sortedArrayUsingSelector:@selector(localizedCaseInsensitiveCompare:)];
            //            ocString = [sortedArray componentsJoinedByString:@" "];
            //            ocString = [ocString stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
            
            const char *rString = [encrypt UTF8String];
            
            //DebugLog (@"%@", [[[NSString alloc] initWithUTF8String:(char *) rString] autorelease]);
            
            sqlite3_result_text(context, rString, (int)strlen(rString) * sizeof(char), NULL);
            break;
        }
        default: {
            sqlite3_result_null(context);
            break;
        }
    }
}

void decryptfield (sqlite3_context *context, int argc, sqlite3_value **argv) {
    assert(argc == 1);
    switch (sqlite3_value_type(argv[0])){
        case SQLITE_TEXT: {
            unsigned const char *string = sqlite3_value_text (argv[0]);
            
            //Get the Objective C string (much easier to manage)
            NSString *ocString = [[NSString alloc] initWithUTF8String:(char *) string] ;
            NSString* decrypt=[AESCrypt fastdecrypt:ocString];
            //            //Split it on punctuation and spaces
            //            NSMutableCharacterSet *cset = [NSCharacterSet punctuationCharacterSet];
            //            [cset addCharactersInString:@" "];
            //            NSArray *tokens = [ocString componentsSeparatedByCharactersInSet:cset];
            //            //Sort it
            //            NSArray *sortedArray = [tokens sortedArrayUsingSelector:@selector(localizedCaseInsensitiveCompare:)];
            //            ocString = [sortedArray componentsJoinedByString:@" "];
            //            ocString = [ocString stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
            
            const char *rString = [decrypt UTF8String];
            
            //DebugLog (@"%@", [[[NSString alloc] initWithUTF8String:(char *) rString] autorelease]);
            
            sqlite3_result_text(context, rString, (int)(strlen(rString) * sizeof(char)), NULL);
            break;
        }
        default: {
            sqlite3_result_null(context);
            break;
        }
    }
}

//+(void) icuRegexpFunc:(sqlite3_context *)p nArg:(int) nArg apArg: (sqlite3_value **)apArg
//{
//}
+(int) AddExFunction:(sqlite3 *) db
{
    
    int nResult	= 0;
    nResult=sqlite3_create_function(db, "decrypt", -1, SQLITE_ANY, 0, decryptfield, 0, 0);
    nResult=sqlite3_create_function(db, "encrypt", -1, SQLITE_ANY, 0, encryptfield, 0, 0);
    nResult=sqlite3_create_function(db, "offline_dirty", -1, SQLITE_ANY, 0, offline_dirty, 0, 0);
    //  sqlite3_create_function(database, "yourAwesome", 4, SQLITE_UTF8, NULL, &yourAwesomeFunc, NULL, NULL);
    //    nResult	= sqlite3_create_function(db, "containi", -1, SQLITE_ANY, 0, icuContainiFunc, 0, 0);
    return nResult;
}
+(NSArray*) get_saveduser
{
    {
        sqlite3* db = [self get_db ];
        NSMutableArray* ret = [[NSMutableArray alloc] init];
        //        ApexMobileAppDelegate *appDelegate = [[UIApplication sharedApplication] delegate];
        //        NSString* user = appDelegate.user;
        NSString *sqlQuery = @"select name,pwd from login_info ";
        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 *name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
                NSString *pwd = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                
                
                
                
                name = [AESCrypt decrypt:name password:@"usai"];
                pwd = [AESCrypt decrypt:pwd password:@"usai"];
                
                
                NSMutableDictionary* up = [[NSMutableDictionary alloc]init];
                up[@"name"]=name;
                up[@"pwd"]=pwd;
                [ret addObject:up];
                //            char *address = (char*)sqlite3_column_text(statement, 3);
                //            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];
                
                
            }
            sqlite3_finalize(statement);
        }
        
        [iSalesDB close_db:db];
        
        return ret;
    }
}
//+(void)testdata
//{
//
//    //---------------- init db --------------------
//    //    NSString* date;
//    //    NSDateFormatter* formatter = [[NSDateFormatter alloc]init];
//    //    [formatter setDateFormat:@"YYYY-MM-dd-hh-mm-ss"];
//    //    date = [formatter stringFromDate:[NSDate date]];
//    //
//    //    NSString* dbname=[NSString stringWithFormat:@"%@.db",date];
//    //
//    //    DebugLog (@"initializeDB");
//    //
//    //    //   NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
//    //    NSString *documents = [NSHomeDirectory() stringByAppendingPathComponent:@"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 = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] ;//[searchPaths objectAtIndex: 0];
//    //    NSString* dbFilePath = [documentFolderPath stringByAppendingPathComponent:dbname];
//    //    if ([[NSFileManager defaultManager] fileExistsAtPath: dbFilePath])
//    //    {
//    //        [[NSFileManager defaultManager] moveItemAtPath:dbFilePath toPath:database_path error:nil];
//    //
//    //    }
//    //    // end move;
//    //
//
//
//
//
//    sqlite3 *db = [iSalesDB get_db];
//
//    NSString* drop_model =@"DROP TABLE IF EXISTS MODEL;";
//    NSString* drop_IMAGE =@"DROP TABLE IF EXISTS model_image;";
//    NSString* drop_image_price =@"DROP TABLE IF EXISTS model_price;";
//    NSString* drop_category =@"DROP TABLE IF EXISTS category;";
//    NSString* drop_login =@"DROP TABLE IF EXISTS offline_login;";
//    NSString* drop_cart =@"DROP TABLE IF EXISTS offline_cart;";
//    NSString* drop_wish =@"DROP TABLE IF EXISTS offline_wishlist;";
//    NSString* drop_contact =@"DROP TABLE IF EXISTS offline_contact;";
//    NSString* drop_contact_image =@"DROP TABLE IF EXISTS contact_image;";
//
//
//    [iSalesDB execSql:drop_model db:db];
//    [iSalesDB execSql:drop_IMAGE db:db];
//    [iSalesDB execSql:drop_image_price db:db];
//    [iSalesDB execSql:drop_category db:db];
//    [iSalesDB execSql:drop_login db:db];
//    [iSalesDB execSql:drop_cart db:db];
//    [iSalesDB execSql:drop_wish db:db];
//    [iSalesDB execSql:drop_contact db:db];
//    [iSalesDB execSql:drop_contact_image db:db];
//
//    NSString* create_model=@"CREATE TABLE IF NOT EXISTS model ( _id INTEGER PRIMARY KEY, name VARCHAR(20), description VARCHAR(20), product_id INTEGER, color VARCHAR(20), legcolor VARCHAR(20) ,availability INTEGER,  incoming_stock INTEGER , demension VARCHAR(20), seat_height VARCHAR(20), material VARCHAR(20), box_dim VARCHAR(20), volume VARCHAR(20), weight VARCHAR(20), model_set VARCHAR(20), load_ability VARCHAR(20),default_category VARCHAR(20), category VARCHAR(100),fabric_content VARCHAR(20), assembling VARCHAR(20), made_in VARCHAR(20), special_remarks VARCHAR(20),stockUom integer,fashion VARCHAR(20), isnew integer,property_field VARCHAR(20),property_display VARCHAR(20),selector_field VARCHAR(20),selector_display VARCHAR(20),ETA DATE);";
//
//
//
//    NSString* create_image=@"CREATE TABLE IF NOT EXISTS model_image ( _id INTEGER PRIMARY KEY, name VARCHAR(20), url VARCHAR(256), type integer, product_id INTEGER);";
//
//    NSString* create_model_price=@"CREATE TABLE IF NOT EXISTS model_price ( _id INTEGER PRIMARY KEY, product_id INTEGER,price float , type integer);";
//    NSString* create_category=@"CREATE TABLE IF NOT EXISTS category ( _id INTEGER PRIMARY KEY, code VARCHAR(20),name VARCHAR(20));";
//
//    NSString* create_offline_login=@"CREATE TABLE IF NOT EXISTS offline_login ( _id INTEGER PRIMARY KEY, username VARCHAR(40),password VARCHAR(40), can_show_price integer ,can_see_price integer,contact_id VARCHAR(20),user_type integer,can_cancel_order integer,can_set_cart_price integer,can_create_portfolio integer, can_delete_order integer,can_submit_order integer,can_set_tearsheet_price integer,can_create_order integer, mode VARCHAR(20), sales_code VARCHAR(20));";
//
//
//
//    NSString* create_offline_cart=@"CREATE TABLE IF NOT EXISTS offline_cart ( _id INTEGER PRIMARY KEY, product_id INTEGER,price float , discount float , so_no VARCHAR(40));";
//    NSString* create_offline_wish=@"CREATE TABLE IF NOT EXISTS offline_wishlist ( _id INTEGER PRIMARY KEY, product_id INTEGER);";
//
//    NSString* create_offline_contact=@"CREATE TABLE IF NOT EXISTS offline_contact ( _id INTEGER PRIMARY KEY, country VARCHAR(40),company_name VARCHAR(40),contact_id VARCHAR(20),addr_1 text, addr_2 text , addr_3 text, addr_4 text, zipcode varchar(20),state  VARCHAR(40), city VARCHAR(40), first_name VARCHAR(40) ,last_name  VARCHAR(40),phone VARCHAR(40),fax VARCHAR(40),email VARCHAR(40),notes text, price_type  VARCHAR(40), sales_rep VARCHAR(40), type VARCHAR(40),create_time timestamp,editable integer,contact_name VARCHAR(40) , addr text);";
//
//    NSString* create_contact_image=@"CREATE TABLE IF NOT EXISTS contact_image ( _id INTEGER PRIMARY KEY, name VARCHAR(20), url VARCHAR(256),  contact_id VARCHAR(20));";
//
//
//    //   NSString* create_model_category=@"CREATE TABLE IF NOT EXISTS model_category ( _id INTEGER PRIMARY KEY, product_id INTEGER,code VARCHAR(20));";
//
//    //        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];
//    [iSalesDB execSql:create_model db:db];
//
//    [iSalesDB execSql:create_image db:db];
//
//    [iSalesDB execSql:create_model_price db:db];
//    [iSalesDB execSql:create_category db:db];
//
//    [iSalesDB execSql:create_offline_login db:db];
//
//    [iSalesDB execSql:create_offline_cart db:db];
//    [iSalesDB execSql:create_offline_wish db:db];
//
//
//    [iSalesDB execSql:create_offline_contact db:db];
//
//    [iSalesDB execSql:create_contact_image db:db];
//
//    NSString * insert_user_queeniey=@"insert into offline_login(can_show_price,can_see_price,contact_id,user_type,can_cancel_order,can_set_cart_price,can_create_portfolio,can_delete_order,can_submit_order,can_set_tearsheet_price,can_create_order,mode,username,password) values(1,1,'NPD',1,1,1,1,1,1,1,1,'Regular Mode','QueenieY','lj0EPk2Th9zZCVwrcskZOA==')";
//    [iSalesDB execSql:insert_user_queeniey db:db];
//    //   [self execSql:create_model_category 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];
//    //  int ret=[iSalesDB close_db:db];
//
//
//    DebugLog (@"bottom of initializeDb");
//
//
//    NSString* IMS_S=@"http://113.28.30.235:80/site//u/NPD/20150715/3857_1455_s.jpg";
//    //NSString* IMS_M=@"http://113.28.30.235:80/site//u/NPD/20150715/3857_1455_m.jpg";
//    NSString* IMS_L=@"http://113.28.30.235:80/site//u/NPD/20150715/3857_1455_l.jpg";
//
//    //----------------- fill data -------------------
//    NSString* img_url1 = IMS_S;
//    //   NSString* img_url2 = IMS_M;
//    NSString* img_url3 = IMS_L;
//
//    CFAbsoluteTime start = CFAbsoluteTimeGetCurrent();
//    [iSalesDB execSql:@"begin" db:db];
//    //   NSString* exec = @"";
//    for(int i=0;i<1000;i++)
//    {
//
//        int category = arc4random() % 4;
//        NSString* sql = [NSString stringWithFormat:@"insert into model(ETA,property_display,property_field,selector_display,isnew,selector_field,fashion,stockUom,name,description,product_id,color,legcolor,availability,incoming_stock,demension,seat_height,material,box_dim,volume,weight,model_set,load_ability,default_category,category,fabric_content,assembling,made_in,special_remarks) values('07/13/2016','Leg Color','legcolor','Color',1,'color','108526-48-BS%d',1,'108526-48-BS%d','Charlotte Fabric Counter Stool Brushed Smoke Legs, Putty%d',%d,'red color%d','white color%d',15%d,25%d,'20.00\"w  22.00\"d  39.00\"h%d','26.0\"h','Solid Birch Wood%d','20.50\"w  23.00\"d  40.00\"h',10.91%d,23.00%d,'Sold in quantities of 1','Load ability%d','%d','%d','77%% Polyester, 15%% Cotton, 8%% Linen', 'Fully Assembled;%d','China%d','Special Remarks%d');",i,i,i,i,i,i,i,i,i,i,i,i,i,category,category,i,i,i];
//
//
//        NSString* sql_1=[NSString stringWithFormat:@"insert into model_price (product_id,price,type) values(%d,12.%d,0);",i,i%100];
//        NSString* sql_2=[NSString stringWithFormat:@"insert into model_price (product_id,price,type) values(%d,12.%d,1);",i,i%100];
//        NSString* sql_3=[NSString stringWithFormat:@"insert into model_price (product_id,price,type) values(%d,12.%d,2);",i,i%100];
//
//
//
//        NSString* sql_4=[NSString stringWithFormat:@"insert into model_image(product_id,type,url) values(%d,0,'%@')",i,img_url1];
//        NSString* sql_5=[NSString stringWithFormat:@"insert into model_image(product_id,type,url) values(%d,1,'%@')",i,img_url3];
//
//        //        exec=[exec stringByAppendingString:sql];
//        //
//        //        exec=[exec stringByAppendingString:sql_1];
//        //        exec=[exec stringByAppendingString:sql_2];
//        //        exec=[exec stringByAppendingString:sql_3];
//
//
//        [iSalesDB execSql:sql db:db];
//
//        [iSalesDB execSql:sql_1 db:db];
//        [iSalesDB execSql:sql_2 db:db];
//        [iSalesDB execSql:sql_3 db:db];
//        [iSalesDB execSql:sql_4 db:db];
//        [iSalesDB execSql:sql_5 db:db];
//
//    }
//    //    [iSalesDB execSql:exec db:db];
//
//    [iSalesDB execSql:@"update model set category='#001003#;' where category='0'" db:db];
//    [iSalesDB execSql:@"update model set category='#001001#;' where category='1'" db:db];
//    [iSalesDB execSql:@"update model set category='#001002#;' where category='2'" db:db];
//    [iSalesDB execSql:@"update model set category='#001003#;' where category='3'" db:db];
//    [iSalesDB execSql:@"commit" db:db];
//    [iSalesDB close_db:db];
//
//    CFAbsoluteTime end = CFAbsoluteTimeGetCurrent();
//
//    //  ((double)(begintime-endtime))/(1000*1000);
//    DebugLog(@"time cost: %0.3f", end - start);
//
//
//
//
//
//
//}

+ (sqlite3*) get_db
{
    
//    if(![dblock tryLock])
//        return nil;
    
    [dblock lock];
    
    sqlite3* db = nil;
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *documents = /*[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];*/[paths objectAtIndex:0];
    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    
    DebugLog(@"DB path:%@",database_path);
    count_dbconn++;
    DebugLog(@"OPEN DB current dbconn: %d",count_dbconn);
    if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_open failed. msg:%s",sqlite3_errmsg(db));
        [iSalesDB close_db:db];
    }
    
    [self AddExFunction:db];
    [RASingleton sharedInstance].currentDB = db;
    return db;
    
    
    
}
+ (void) close_db:(sqlite3 *)db
{
    
    
    sqlite3_close(db);
    count_dbconn--;
    DebugLog(@"CLOSE DB current dbconn: %d",count_dbconn);
    [RASingleton sharedInstance].currentDB = nil;
    [dblock unlock];
    
    
    
}

+ (sqlite3*) get_db_at:(NSString* )path
{
    sqlite3* db = nil;
    
    //    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    //    NSString *documents = /*[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];*/[paths objectAtIndex:0];
    //    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
    
    //    DebugLog(@"DB path:%@",database_path);
    //count_dbconn++;
    
    DebugLog(@"current dbconn: %d",count_dbconn);
    if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_open failed. msg:%s",sqlite3_errmsg(db));
        sqlite3_close(db);
        //[iSalesDB close_db:db];
    }
    
    
    [self AddExFunction:db];
    return db;
    
}
+ (NSString* )   sqliteEscape :(NSString*) keyWord
{
    
    
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"/" withString:@"//"];
    
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"[" withString:@"/["];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"]" withString:@"/]"];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"%" withString:@"/%"];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"&" withString:@"/&"];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"_" withString:@"/_"];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@"(" withString:@"/("];
    keyWord = [keyWord stringByReplacingOccurrencesOfString:@")" withString:@"/)"];
    
    
    return keyWord;
}
+ (void) save_pdf: (NSString*) temp_path filename:(NSString*) filename
{
//    AppDelegate *appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
//
//    if(appDelegate.bEnable_Cache==false)
//        return ;
    if(temp_path==nil)
        return;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *pdf_cache = [cachefolder stringByAppendingPathComponent:@"pdf_cache"];
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL bdir=YES;
    if(!  [fileManager fileExistsAtPath:pdf_cache isDirectory:&bdir])
    {
        
        NSError *error = nil;
        bool bsuccess=[fileManager createDirectoryAtPath:pdf_cache withIntermediateDirectories:YES attributes:nil error:&error];
        
        if(!bsuccess)
            DebugLog(@"Create cache folder failed");
        
        //        if(bsuccess)
        //        {
        //            sqlite3 *db = [self get_db];
        //
        //            [self execSql:[NSString stringWithFormat:@"insert into img_cache(name) values('%@')",name] db:db];
        //            [iSalesDB close_db:db];
        //        }
    }
    NSString *save_path = [pdf_cache stringByAppendingPathComponent:filename];
    
    NSError *error = nil;
    
    bool bsuccess=[fileManager copyItemAtPath:temp_path toPath:save_path error:&error];
    
    
    
    if(bsuccess)
        DebugLog(@"PDF SAVE SUCCESS,%@",filename);
    else
        DebugLog(@"PDF SAVE FAILED,%@",filename);
    
}
+ (void) cache_img: (NSData*) imgData filename:(NSString*) name saveTo:(NSString*) path
{
    
    path=[path stringByReplacingOccurrencesOfString:@"https://" withString:@""];
    path=[path stringByReplacingOccurrencesOfString:@"http://" withString:@""];
    path=[path stringByReplacingOccurrencesOfString:name withString:@""];
    
    //    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    //    NSString *cachefolder = [paths objectAtIndex:0];
    //    NSString *img_cache = [cachefolder stringByAppendingPathComponent:[NSString stringWithFormat:@"img_cache/%@",@"www.newpacificdirect.com/u/NPD/20160615/mytest/"]];
    //    if ([[NSFileManager defaultManager] fileExistsAtPath:img_cache]) {
    //
    //        DebugLog(@"目录已经存在了");
    //
    //    }
    //    else
    //    {
    //        NSError *error = nil;
    //        bool bsuccess=[[NSFileManager defaultManager] createDirectoryAtPath:img_cache withIntermediateDirectories:YES attributes:nil error:&error];
    //
    //        if(!bsuccess)
    //            DebugLog(@"Create temp folder failed");
    //    }
    
    
    if(path.length==0)
        path=@"";
    
//    AppDelegate *appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
//
//    if(appDelegate.bEnable_Cache==false)
//        return ;
    if(imgData==nil)
        return;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:[NSString stringWithFormat:@"img_cache/%@",path]];
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL bdir=YES;
    if(!  [fileManager fileExistsAtPath:img_cache isDirectory:&bdir])
    {
        
        NSError *error = nil;
        bool bsuccess=[fileManager createDirectoryAtPath:img_cache withIntermediateDirectories:YES attributes:nil error:&error];
        
        if(!bsuccess)
            DebugLog(@"Create cache folder failed");
        
        //        if(bsuccess)
        //        {
        //            sqlite3 *db = [self get_db];
        //
        //            [self execSql:[NSString stringWithFormat:@"insert into img_cache(name) values('%@')",name] db:db];
        //            [iSalesDB close_db:db];
        //        }
    }
    NSString *filePath = [img_cache stringByAppendingPathComponent:name];
    bool bsuccess=[imgData writeToFile:filePath atomically:YES];
    
    if(bsuccess)
        DebugLog(@"IMG CACHE SUCCESS,%@",name);
    else
        DebugLog(@"IMG CACHE FAILED,%@",name);
    
}
+ (NSData*) load_cached_img:(NSString*) filename loadFrom:(NSString*) path
{
    if(path.length==0)
        return nil;
//    bool b=        [Singleton sharedInstance].homeItemClick ;
    path=[path stringByReplacingOccurrencesOfString:@"https://" withString:@""];
    path=[path stringByReplacingOccurrencesOfString:@"http://" withString:@""];
    //    path=[path stringByReplacingOccurrencesOfString:filename withString:@""];
    
//    AppDelegate *appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
//    
//    if(appDelegate.bEnable_Cache==false)
//        return nil;
    
    
    NSData* data = nil;
    
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:@"img_cache"];
    NSString *filePath = [img_cache stringByAppendingPathComponent:path];
    
    NSFileManager* fileManager = [NSFileManager defaultManager];
    if(  [fileManager fileExistsAtPath:filePath ])
    {
        data = [NSData dataWithContentsOfFile: filePath];
    }
    
    //    NSString* sqliteQuery = [NSString stringWithFormat:@"SELECT img FROM img_cache WHERE name = '%@'", filename];
    //    sqlite3_stmt* statement;
    
    //    sqlite3 *db = [self get_db];
    //
    ////    if( sqlite3_prepare_v2(db, [sqliteQuery UTF8String], -1, &statement, NULL) == SQLITE_OK )
    ////    {
    ////        if( sqlite3_step(statement) == SQLITE_ROW )
    ////        {
    ////            int length = sqlite3_column_bytes(statement, 0);
    ////            data       = [NSData dataWithBytes:sqlite3_column_blob(statement, 0) length:length];
    ////        }
    ////    }
    //
    //    // Finalize and close database.
    //    sqlite3_finalize(statement);
    
    
    // [iSalesDB close_db:db];
    return data;
    
}
+ (int) initializeDb {
    DebugLog (@"initializeDB");
    count_dbconn=0;
    
    
    
    
    
    //count_tmpdbconn=0;
    //    return false;
    //
    //       NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    //    NSString *documents = /*[NSHomeDirectory() stringByAppendingPathComponent:@"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 = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] ;//[searchPaths objectAtIndex: 0];
    //    NSString* dbFilePath = [documentFolderPath stringByAppendingPathComponent:DBNAME];
    //    if ([[NSFileManager defaultManager] fileExistsAtPath: dbFilePath])
    //    {
    //        [[NSFileManager defaultManager] moveItemAtPath:dbFilePath toPath:database_path error:nil];
    //
    //    }
    // end move;
    
    
    if(dblock==nil)
        dblock=  [[NSLock alloc] init];
    
//    /* test dblock in thread*/
//    for(int i=0;i<50;i++)
//
//    {
//        dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
//            
//            DebugLog(@"%d is running",i);
//            sqlite3* testdb = [self get_db];
//            
//            int sec=arc4random() % 10;
//            DebugLog(@"%d is processing, will take %d sec",i,sec);
//            sleep(sec);
//            [self close_db:testdb];
//            
//            DebugLog(@"%d is finish",i);
//            
//            
//        });
//    }
//    
    
    
    
    //    [iSalesDB execSql:@"PRAGMA journal_mode = DELETE;"];
    
    sqlite3 *db = [self get_db];
    
    
    //    int result= [iSalesDB AddExFunction:db];
    
    //    [iSalesDB execSql:@"update model_price set price=decrypt(price);" db:db];
    
    
    // [self execSql:@"PRAGMA journal_mode = WAL;" db:db];
    
    NSString* create_product=@"CREATE TABLE IF NOT EXISTS product ( _id INTEGER PRIMARY KEY, name VARCHAR(20,0), upc_code VARCHAR(42,0), description VARCHAR(20,0), product_id INTEGER, color VARCHAR(20,0), legcolor VARCHAR(20,0), availability INTEGER, incoming_stock INTEGER, ETA DATE, demension VARCHAR(20,0), seat_height VARCHAR(20,0), material VARCHAR(20,0), box_dim VARCHAR(20,0), volume VARCHAR(20,0), weight VARCHAR(20,0), model_set VARCHAR(20,0), load_ability VARCHAR(20,0), default_category VARCHAR(20,0), category VARCHAR(100,0), fabric_content VARCHAR(20,0), assembling VARCHAR(20,0), made_in VARCHAR(20,0), special_remarks VARCHAR(20,0), stockUom integer, fashion VARCHAR(20,0), isnew integer, property_field VARCHAR(20,0), property_display VARCHAR(20,0), selector_field VARCHAR(20,0), selector_display VARCHAR(20,0), product_group VARCHAR(20,0), packaging VARCHAR(20,0), closeout integer ,best_seller integer,alert VARCHAR(20,0),has_bundle integer,is_active integer,item_id integer,more_color integer,seat_dimension text,assembly_instruction text,product_content_writing text);";
    
    NSString* create_catalog=@"CREATE TABLE IF NOT EXISTS catalog ( _id INTEGER PRIMARY KEY, name VARCHAR(20,0), description VARCHAR(20,0), upc_code VARCHAR(42,0), item_id INTEGER,is_active integer,ulength float,uwidth float,uheight float,uweight float,pcs integer,mpack integer,mlength float,mwidth float,mheight float,mweigth float,ipack integer,ilength float,iwidth float,iheight float, iweight float,ucbf float,icbf float, mcbf float,is_single_model integer, is_master_model integer,is_rate integer,is_part integer);";
    
    NSString* create_bundle=@"CREATE TABLE IF NOT EXISTS bundle ( _id INTEGER PRIMARY KEY, product_id integer,product_item_id integer,item_id integer,unit_price double,use_unitprice integer,qty integer);";
    
    NSString* create_price=@"CREATE TABLE IF NOT EXISTS price ( _id INTEGER PRIMARY KEY, name text,  type integer,  order_by integer,is_show integer,create_time TIMESTAMP DEFAULT(datetime('now','localtime')),modify_time TIMESTAMP DEFAULT(datetime('now','localtime')));";
    NSString* create_errlog=@"CREATE TABLE IF NOT EXISTS errlog ( _id INTEGER PRIMARY KEY, errmsg text,module text,params text ,code integer,  create_time TIMESTAMP default (datetime('now', 'localtime')));";
    
    
    
    NSString* create_image=@"CREATE TABLE IF NOT EXISTS model_image ( _id INTEGER PRIMARY KEY, name VARCHAR(20), url text, type integer, product_id INTEGER,item_id integer, default_img integer,picture_id integer);";
    
    NSString* create_model_price=@"CREATE TABLE IF NOT EXISTS model_price ( _id INTEGER PRIMARY KEY, item_id integer, price text , type integer, price_name text, expire_datetime datetime);";
    
    //NSString* create_model_ext=@"CREATE TABLE IF NOT EXISTS model_ext ( _id INTEGER PRIMARY KEY, product_id INTEGER,carton integer, cuft double, weight double;";
    
    NSString* create_category=@"CREATE TABLE IF NOT EXISTS category ( _id INTEGER PRIMARY KEY, code VARCHAR(20),name VARCHAR(20));";
    NSString* create_carrier=@"CREATE TABLE IF NOT EXISTS carrier ( _id INTEGER PRIMARY KEY, code_id integer,name text);";
    NSString* create_offline_setting=@"CREATE TABLE IF NOT EXISTS offline_setting ( _id INTEGER PRIMARY KEY, name VARCHAR(20),value VARCHAR(40));";
    
    
    NSString* create_offline_login=@"CREATE TABLE IF NOT EXISTS offline_login ( _id INTEGER PRIMARY KEY, username VARCHAR(40),password VARCHAR(40), can_show_price integer ,can_see_price integer,contact_id VARCHAR(20),user_type integer,can_cancel_order integer,can_set_cart_price integer,can_create_portfolio integer, can_delete_order integer,can_submit_order integer,can_set_tearsheet_price integer,can_create_order integer, can_update_contact_info integer, mode VARCHAR(20), default_price text, price text, user_id integer,sales_code text,first_name text,last_name text);";
    
    NSString* create_offline_order=@"CREATE TABLE IF NOT EXISTS offline_order ( _id INTEGER PRIMARY KEY,so_id text, order_id text ,status integer, submit_as integer,general_notes TEXT ,internal_notes text,sales_rep TEXT,create_by TEXT, total_price float, erpOrderStatus integer, logist text, lift_gate integer, logistic_note text, paymentType text, credit_card_first_name text, credit_card_last_name text, credit_card_address1 text, credit_card_address2 text, credit_card_zipcode text, credit_card_type text, credit_card_number text, credit_card_security_code text, credit_card_expiration_month text , credit_card_expiration_year text,credit_card_city text, credit_card_state text,customer_cid text,customer_contact text,customer_email text,customer_phone text,customer_fax text,receive_cid text,receive_name text,receive_ext text,receive_contact text, receive_phone text,receive_fax text,receive_email text,sender_cid text,sender_name text,sender_ext text,sender_contact text, sender_phone text,sender_fax text,sender_email text,shipping_billto_cid text,shipping_billto_name text,shipping_billto_ext text,shipping_billto_contact text, shipping_billto_phone text,shipping_billto_fax text,shipping_billto_email text,billing_cid text,billing_name text,billing_ext text,billing_contact text, billing_phone text,billing_fax text,billing_email text,returnto_cid text,returnto_name text,returnto_ext text,returnto_contact text, returnto_phone text,returnto_fax text,returnto_email text, must_call int,poNumber text,comments text,comments_ext text,paymentsAndCredits float,shipping float,lift_gate_value float,handling_fee_value float,sign_picpath text,sync_data text,ship_via text,create_time TIMESTAMP default (datetime('now', 'localtime')),modify_time TIMESTAMP default (datetime('now', 'localtime')),schedule_date TIMESTAMP,vendor_no char,warehouse_name char,terms char,etd TIMESTAMP,carrier char ,sales_terms char,port_destination char, discount float);";
    
    
    
    
    NSString* create_offline_cart=@"CREATE TABLE IF NOT EXISTS offline_cart ( _id INTEGER PRIMARY KEY, orderitem_id integer,product_id INTEGER,item_id integer,str_price text , discount float , so_no VARCHAR(40), item_count integer,line_note text,bundle_item text,type integer,create_time TIMESTAMP default (datetime('now', 'localtime')),modify_time TIMESTAMP default (datetime('now', 'localtime')),client_reference  char);";
//    NSString* create_offline_wish=@"CREATE TABLE IF NOT EXISTS offline_wishlist ( _id INTEGER PRIMARY KEY, product_id INTEGER,qty integer,item_id integer, create_time TIMESTAMP default (datetime('now', 'localtime')));";
    NSString* create_wishlist=@"CREATE TABLE IF NOT EXISTS wishlist ( _id INTEGER PRIMARY KEY, user_id INTEGER,  product_id integer ,item_id integer , qty integer, is_delete integer, is_dirty integer,create_time TIMESTAMP default (datetime('now', 'localtime')),modify_time TIMESTAMP default (datetime('now', 'localtime')));";

    
    NSString* create_offline_contact=@"CREATE TABLE IF NOT EXISTS offline_contact (_id INTEGER, country VARCHAR(40,0), company_name VARCHAR(40,0), contact_id VARCHAR(20,0), addr_1 text, addr_2 text, addr_3 text, addr_4 text, zipcode varchar(20,0), state VARCHAR(40,0), city VARCHAR(40,0), first_name VARCHAR(40,0), last_name VARCHAR(40,0), phone VARCHAR(40,0), fax VARCHAR(40,0), email VARCHAR(40,0), notes text, price_type VARCHAR(40,0), sales_rep VARCHAR(40,0), type VARCHAR(40,0), create_time timestamp default (datetime('now', 'localtime')), editable integer, contact_name text, addr text, Sales_Order_Customer integer, Sales_Order_Freight_Bill_To integer, Sales_Order_Ship_From integer, Sales_Order_Merchandise_Bill_To integer, Contact_Return_To integer, Sales_Order_Ship_To integer,img_0 TEXT,img_1 TEXT,img_2 TEXT,related_cid VARCHAR(20,0),is_active integer,sync_data text,modify_time TIMESTAMP default (datetime('now', 'localtime')), carrier integer, PRIMARY KEY(_id) );";
    
//    NSString* create_contact_image=@"CREATE TABLE IF NOT EXISTS contact_image ( _id INTEGER PRIMARY KEY, name VARCHAR(20), url VARCHAR(256), contact_id VARCHAR(20));";
    
    
    NSString* create_login_info=@"CREATE TABLE IF NOT EXISTS login_info ( _id INTEGER PRIMARY KEY, name VARCHAR(256), pwd VARCHAR(256), lastlogin timestamp );";
    
    NSString* create_offline_country=@"CREATE TABLE IF NOT EXISTS offline_country ( _id INTEGER PRIMARY KEY, name text,code VARCHAR(16), countrycode_id integer);";
    
    NSString* create_offline_state=@"CREATE TABLE IF NOT EXISTS offline_state ( _id INTEGER PRIMARY KEY, name text,code VARCHAR(16), country_code VARCHAR(16));";
    
    NSString* create_offline_zipcode=@"CREATE TABLE IF NOT EXISTS offline_zipcode ( _id INTEGER PRIMARY KEY, country text, state text, city text,state_code VARCHAR(16), country_code VARCHAR(16), zipcode VARCHAR(16));";
    
    NSString* create_offline_salesrep=@"CREATE TABLE IF NOT EXISTS offline_salesrep ( _id INTEGER PRIMARY KEY, name text, code text,salesrep_id integer);";
    
    
    NSString *create_offline_portfolio = @"CREATE TABLE IF NOT EXISTS offline_portfolio (_id integer PRIMARY KEY,product_id integer,name text,description tex,item_id integer,fashion_id integer,available_qty integer,percentage integer,percent double,sheet_price double,sheet_discount double,img text,line_note text,is_delete integer,is_dirty integer,createtime timestamp DEFAULT(datetime('now','localtime')),modify_time timestamp DEFAULT(datetime('now','localtime')));";
    
    NSString *create_offline_pdf = @"create table if not exists offline_pdf (_id integer primary key,tearsheets_id integer,pdf_path text,create_user text,tear_note text,tear_name text,model_info text,configureParams text,is_delete integer,is_dirty integer,is_local integer,off_params text,createtime timestamp default(datetime('now','localtime')),modify_time timestamp DEFAULT(datetime('now','localtime')));";
    
    NSString *create_contact_type = @"create table if not exists contact_type (_id integer primary key,type_name text,is_show integer,create_time TIMESTAMP DEFAULT(datetime('now','localtime')),modify_time TIMESTAMP DEFAULT(datetime('now','localtime')));";

    NSString *create_payment_type = @"create table if not exists payment_type (_id integer primary key,type_id text,pay_type text,sort_id int);";

    
    NSString* create_order_trigger=@"CREATE TRIGGER  if not exists offline_order_insert after insert on offline_order BEGIN select offline_dirty();  UPDATE offline_order SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists offline_order_update after update on offline_order BEGIN select offline_dirty();  UPDATE offline_order SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists offline_order_delete after delete on offline_order BEGIN select offline_dirty();  END;";
 
    
    
    NSString* create_contact_trigger=@"CREATE TRIGGER  if not exists offline_contact_insert after insert on offline_contact BEGIN select offline_dirty();  UPDATE offline_contact SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists offline_contact_update after update on offline_contact BEGIN select offline_dirty(); UPDATE offline_contact SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;  END;CREATE TRIGGER  if not exists offline_contact_delete after delete on offline_contact BEGIN select offline_dirty();  END;";

    
    NSString* create_cart_trigger=@"CREATE TRIGGER  if not exists offline_cart_insert after insert on offline_cart BEGIN select offline_dirty(); UPDATE offline_cart SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;  END;CREATE TRIGGER  if not exists offline_cart_update after update on offline_cart BEGIN select offline_dirty(); UPDATE offline_cart SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;  END;CREATE TRIGGER  if not exists offline_cart_delete after delete on offline_cart BEGIN select offline_dirty();  END;";
    //drop trigger if exists wishlist_insert;drop trigger if exists wishlist_update;drop trigger if exists wishlist_delete;
    
    NSString* create_wishlist_trigger=@"CREATE TRIGGER  if not exists wishlist_insert after insert on wishlist BEGIN select offline_dirty();  UPDATE wishlist SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists wishlist_update after update on wishlist BEGIN select offline_dirty();  UPDATE wishlist SET modify_time= datetime('now', 'localtime'), is_dirty = 1 WHERE _id=new._id; END;CREATE TRIGGER if not exists wishlist_delete after DELETE ON wishlist BEGIN select offline_dirty(); END;";
    
        NSString* create_offline_pdf_trigger=@"CREATE TRIGGER  if not exists offline_pdf_insert after insert on offline_pdf BEGIN select offline_dirty();  UPDATE offline_pdf SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists offline_pdf_update after update on offline_pdf BEGIN select offline_dirty();  UPDATE offline_pdf SET modify_time= datetime('now', 'localtime'), is_dirty = 1 WHERE _id=new._id; END;CREATE TRIGGER if not exists offline_pdf_delete after DELETE ON offline_pdf BEGIN select offline_dirty(); END;";
    
        NSString* create_offline_portfolio_trigger=@"CREATE TRIGGER  if not exists offline_portfolio_insert after insert on offline_portfolio BEGIN select offline_dirty();  UPDATE offline_portfolio SET modify_time= datetime('now', 'localtime') WHERE _id=new._id;END;CREATE TRIGGER  if not exists offline_portfolio_update after update on offline_portfolio BEGIN select offline_dirty();  UPDATE offline_portfolio SET modify_time= datetime('now', 'localtime'), is_dirty = 1 WHERE _id=new._id; END;CREATE TRIGGER if not exists offline_portfolio_delete after DELETE ON offline_portfolio BEGIN select offline_dirty(); END;";
 //   AppDelegate *appDelegate = (AppDelegate*)[[UIApplication sharedApplication] delegate];
    
    //if(appDelegate.offline_mode)
    {
        /*************************ALTER TABLE From V1.4 to V1.5************************************/
        
        [self alterTable:@"model" columns:@"product" rename:YES db:db]; // 需要在创建product之间改名
        
        
        [self execSql:create_product db:db];
        [self execSql:create_wishlist db:db];
        [self execSql:create_price db:db];
        [self execSql:create_image db:db];
        
        [self execSql:create_model_price db:db];
        [self execSql:create_category db:db];
        [self execSql:create_carrier db:db];
        
        [self execSql:create_offline_setting db:db];
        
        [self execSql:create_offline_login db:db];
        
        [self execSql:create_offline_cart db:db];
        
//        [self execSql:create_offline_wish db:db];
        
        [self execSql:create_offline_contact db:db];
        
//        [self execSql:create_contact_image db:db];
        
        [self execSql:create_offline_order db:db];
        
        [self execSql:create_offline_country db:db];
        [self execSql:create_offline_state db:db];
        [self execSql:create_offline_zipcode db:db];
        [self execSql:create_offline_salesrep db:db];
        [self execSql:create_bundle db:db];
        [self execSql:create_catalog db:db];
        [self execSql:create_errlog db:db];
        
        [self execSql:create_offline_portfolio db:db];
        [self execSql:create_offline_pdf db:db];

        [self execSql:create_contact_type db:db];
        
        // v1.90
        [self execSql:create_payment_type db:db];
    }
    
    [self execSql:create_login_info db:db];
    
    /*************************ALTER TABLE From V1.4 to V1.5************************************/
   
//    [self alterTable:@"model" columns:@"product" rename:YES db:db];
    /**
     *  2017-06-01 v1.90 add more_color
     *  2017-06-30 v1.90 add seat_dimension , assembly_instruction , product_content_writing
     */
    [self alterTable:@"product" columns:@"has_bundle integer;is_active integer;item_id integer;more_color integer;seat_dimension text;assembly_instruction text;product_content_writing text" rename:NO db:db];
    
    [self alterTable:@"model_image" columns:@"item_id integer;picture_id integer" rename:NO db:db];
    
    [self alterTable:@"model_price" columns:@"item_id integer" rename:NO db:db];
    
    [self alterTable:@"offline_cart" columns:@"orderitem_id integer;item_id integer;str_price text;item_count integer;line_note text;bundle_item text;type integer;create_time timestamp" rename:NO db:db];
    [self alterTable:@"offline_cart" columns:@"modify_time TIMESTAMP" rename:NO db:db];
    
    [self alterTable:@"offline_contact" columns:@"is_active integer;sync_data text" rename:NO db:db];
    [self alterTable:@"offline_contact" columns:@"modify_time TIMESTAMP" rename:NO db:db];
    [self alterTable:@"offline_contact" columns:@"carrier integer " rename:NO db:db];
    
    [self alterTable:@"offline_login" columns:@"can_update_contact_info integer;sales_code text" rename:NO db:db];
    
    [self alterTable:@"wishlist" columns:@"qty integer;item_id integer" rename:NO db:db];
    [self alterTable:@"wishlist" columns:@"modify_time TIMESTAMP" rename:NO db:db];
    [self alterTable:@"wishlist" columns:@"is_delete integer" rename:NO db:db];
    [self alterTable:@"wishlist" columns:@"is_dirty integer" rename:NO db:db];
    
    [self alterTable:@"offline_order" columns:@"modify_time TIMESTAMP;schedule_date TIMESTAMP" rename:NO db:db];
    [self alterTable:@"offline_order" columns:@"ship_via text" rename:NO db:db];
    [self alterTable:@"offline_order" columns:@"vendor_no char;warehouse_name  char;terms char;etd TIMESTAMP;carrier char;sales_terms char;port_destination char" rename:NO db:db];
    [self alterTable:@"offline_order" columns:@"discount float" rename:NO db:db];
    
    [self alterTable:@"offline_cart" columns:@"client_reference char" rename:NO db:db];
    
    [self alterTable:@"offline_login" columns:@"first_name text;last_name text" rename:NO db:db];
    
    [self alterTable:@"offline_pdf" columns:@"off_params text" rename:NO db:db];
    
    [self alterTable:@"price" columns:@"is_show integer;create_time TIMESTAMP;modify_time TIMESTAMP" rename:NO db:db];
    [self alterTable:@"contact_type" columns:@"is_show integer;create_time TIMESTAMP;modify_time TIMESTAMP" rename:NO db:db];
    
    [self execSql:create_order_trigger db:db];
    [self execSql:create_contact_trigger db:db];
    [self execSql:create_cart_trigger db:db];
    [self execSql:create_wishlist_trigger db:db];
    [self execSql:create_offline_pdf_trigger db:db];
    [self execSql:create_offline_portfolio_trigger db:db];
    
//    NSString *rename_model_to_product = @"alter model rename to product";
//    [self execSql:rename_model_to_product db:db];
//    
//    if (![self checkForField:@"product" field:@"has_bundle" db:db]) {
////          [self];
//    }
//    
//    if( ![self checkForField:@"offline_login" field:@"sales_code" db:db])
//    {
//        NSString* alter_search_history = @"ALTER TABLE offline_login ADD sales_code text";
//        [self execSql:alter_search_history db:db];
//    }
//    if( ![self checkForField:@"offline_login" field:@"can_update_contact_info" db:db])
//    {
//        NSString* alter_search_history = @"ALTER TABLE offline_login ADD can_update_contact_info integer";
//        [self execSql:alter_search_history db:db];
//    }
//    if( ![self checkForField:@"offline_contact" field:@"sync_data" db:db])
//    {
//        NSString* alter_search_history = @"ALTER TABLE offline_contact ADD sync_data text";
//        [self execSql:alter_search_history db:db];
//    }
//    if( ![self checkForField:@"model_image" field:@"picture_id" db:db])
//    {
//        NSString* alter_search_history = @"ALTER TABLE model_image ADD picture_id 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];
    [iSalesDB close_db:db];
    return 0;
    //        int aaa = 0;
    
    
    // DebugLog (@"bottom of initializeDb");
}
+(int)execSql:(NSString *)sql db:(sqlite3 *)db
{
    
    int ret=RESULT_TRUE;
    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);
        ret= RESULT_FALSE;
        // [iSalesDB close_db:db];
        
    }
    //    else
    //    {
    //        int result=sqlite3_wal_checkpoint(db, NULL);
    //    }
    return ret;
}
+ (NSDictionary*) search_pdf:(int) offset limit:(int)limit keywords:(NSString*) keywords
{
    sqlite3 *db = [self get_db];
    NSMutableDictionary* json = [[NSMutableDictionary alloc] init];
    [json setValue:@"1" forKey:@"result"];
    NSString *sqlQuery =nil;
    if(keywords.length>0)
        sqlQuery=[NSString stringWithFormat: @"SELECT _id,name,file_name,send_to,create_params,c_id,c_name,o_id,pdf_type,create_time FROM pdf_cache where  lower(name) like '%%%@%%' order by create_time desc limit %d offset %d",keywords,limit,offset];
    
    else
        sqlQuery=[NSString stringWithFormat: @"SELECT _id,name,file_name,send_to,create_params,c_id,c_name,o_id,pdf_type,create_time FROM pdf_cache order by create_time desc limit %d offset %d",limit,offset];
    
    sqlite3_stmt * statement;
    int count = 0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            int _id = sqlite3_column_int(statement, 0);
            
            char *name = (char*)sqlite3_column_text(statement, 1);
            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *file_name = (char*)sqlite3_column_text(statement, 2);
            NSString *nsFileNameStr = [[NSString alloc]initWithUTF8String:file_name];
            
            
            char *type = (char*)sqlite3_column_text(statement, 8);
            NSString *nsTypeStr = [[NSString alloc]initWithUTF8String:type];
            
            char *create_time = (char*)sqlite3_column_text(statement, 9);
            NSString *nsTimeStr = [[NSString alloc]initWithUTF8String:create_time];
            
            
            
            
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            [item setValue:[NSString stringWithFormat:@"%d",_id] forKey:@"_id"];
            [item setValue:nsNameStr forKey:@"name"];
            [item setValue:nsTimeStr forKey:@"create_time"];
            [item setValue:nsTypeStr forKey:@"type"];
            [item setValue:nsFileNameStr forKey:@"file_name"];
            
            [json setObject:item forKey:[NSString stringWithFormat:@"item_%d",count]];
            count++;
            
        }
        [json setValue:[NSString stringWithFormat:@"%d",count] forKey:@"count"];
        [json setValue:@"2" forKey:@"result"];
    }
    
    [iSalesDB close_db:db];
    return json;
}
+(int)execSql:(NSString *)sql
{
    
    sqlite3 *db = [self get_db];
    int ret=[self execSql:sql db:db];
    
    [iSalesDB close_db:db];
    return ret;
}
+ (int) get_recordid:(NSString*) tablename where:(NSString*) whereclause
{
    
    //    [iSalesDB execSql: [NSString stringWithFormat: @"update fields_info set abandon = 1 where user ='%@'",user] db:db];
    return [self get_recordid:tablename where:whereclause order:@"_id"];
}
+ (int) get_recordcount:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause
{
    //sqlite3 *db = [self get_db];
    
    int ret = -1;
    NSString *sqlQuery = [NSString stringWithFormat:@"select count(0) from %@ where %@ ;",tablename,whereclause];
    
    
    DebugLog(@"get_recordcount sql:%@",sqlQuery);
    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);
    }
    //   [iSalesDB close_db:db];
    assert(ret>=0);
    return ret;
}
+ (int) get_recordid:(NSString*) tablename where:(NSString*) whereclause order:(NSString*) orderby
{
    sqlite3 *db = [self get_db];
    
    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);
    }
    [iSalesDB close_db:db];
    return ret;
    
}
+ (int) get_insertid:(NSString*) tablename db:(sqlite3*)db
{
    //   sqlite3 *db = [self get_db];
    
    int ret = -1;
    NSString *sqlQuery = [NSString stringWithFormat:@"select last_insert_rowid() from %@ ",tablename];
    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);
    }
    //   [iSalesDB close_db:db];
    return ret;
    
}
+ (int) get_recordid:(sqlite3*)db table:(NSString*) tablename where:(NSString*) whereclause
{
    //    [iSalesDB 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;
    
}

#pragma mark - JK

+ (void)alterTable:(NSString *)table columns:(NSString *)column_str_or_new_table_name rename:(BOOL)rename db:(sqlite3 *)db {
    
    if (rename) {
        
        NSString *check_new_table_exist = [NSString stringWithFormat:@"SELECT name FROM sqlite_master WHERE type='table' AND (name='%@' or name = '%@');",table,column_str_or_new_table_name];
        
        __block BOOL new_exist = NO;
        __block BOOL old_exist = NO;

        [self jk_query:check_new_table_exist db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
            
            const char *name = (char *)sqlite3_column_text(stmt, 0);
            if (name == NULL) {
                name = "";
            }
            NSString *nsname = [NSString stringWithUTF8String:name];
            
            if (!old_exist)
                old_exist = [nsname isEqualToString:table];
            
            if (!new_exist)
                new_exist = [nsname isEqualToString:column_str_or_new_table_name];
            
        }];
        
        
        if (!new_exist && old_exist) {
            NSString *rename_model_to_product = [NSString stringWithFormat:@"alter table %@ rename to %@;",table,column_str_or_new_table_name];
            [self execSql:rename_model_to_product db:db];
        }
        
    } else {
        
        NSArray *columns = [column_str_or_new_table_name componentsSeparatedByString:@";"];
        for (NSString *column in columns) {
            
            NSString *name = [[column componentsSeparatedByString:@" "] firstObject];
//            NSString *type = [[column componentsSeparatedByString:@" "] lastObject];
            
            if( ![self checkForField:table field:name db:db])
            {
                NSString* alter_sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN %@;",table,column];
                [self execSql:alter_sql db:db];
            }

        }
    }
}

+ (NSDictionary *)jk_query:(NSString *)sql completion:(queryBlock)block {
    
    return [self jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        if (block) {
            block(stmt,container,count);
        }
    } failure:nil];
}

+ (NSDictionary *)jk_query:(NSString *)sql completion:(queryBlock)block failure:(failureBlock)failure {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = sql;
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        long count = 0;
        if (block) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                block(statement,dic,&count);
            }

        }
        sqlite3_finalize(statement);
    } else {
        if (failure) {
            const char *err = sqlite3_errmsg(db);
            if (err == NULL) {
                err = "";
            }
             failure(dic,[NSString stringWithUTF8String:err]);
        }
    }
    [iSalesDB close_db:db];
    
    
    return [dic copy];
}

+ (NSDictionary *)jk_query:(NSString *)sql db:(sqlite3 *)db close:(BOOL)close completion:(queryBlock)block {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *sqlQuery = sql;
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        long count = 0;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            block(statement,dic,&count);
        }
        [dic setObject:[NSNumber numberWithInteger:RESULT_TRUE] forKey:@"result"];
        sqlite3_finalize(statement);
    } else {
        [dic setObject:[NSNumber numberWithInteger:RESULT_FALSE] forKey:@"result"];
    }

    
    if (close) {
        [iSalesDB close_db:db];
    }
    
    
    return [dic copy];
}

+ (NSString *)jk_queryText:(NSString *)sql{
    
    DebugLog(@"%s sql: %@",__func__,sql);
    
    __block NSString *ret = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = sql;
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *text = (char *)sqlite3_column_text(statement, 0);
            if (text == NULL) {
                text = "";
            }
            ret = [NSString stringWithFormat:@"%s",text];
        }
        
        sqlite3_finalize(statement);
    }
    [iSalesDB close_db:db];
    
    return ret;
}


#pragma mark trigger control
+(void) disable_trigger
{
    btrigger = false;
}
+(void) enable_trigger
{
    btrigger = true;
}
@end