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 "AppDelegate.h"
@implementation iSalesDB


+(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), meterial 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));";
    
    
    
    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=sqlite3_close(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,meterial,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];
    sqlite3_close(db);
    
    CFAbsoluteTime end = CFAbsoluteTimeGetCurrent();
    
    //  ((double)(begintime-endtime))/(1000*1000);
    NSLog(@"time cost: %0.3f", end - start);
    
    
    
    
    
    
}
+ (sqlite3*) get_db
{
    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);
    if (sqlite3_open([database_path UTF8String], &db) != SQLITE_OK) {
        
        DebugLog(@"sqlite3_open failed. msg:%s",sqlite3_errmsg(db));
        sqlite3_close(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 = [[NSError alloc] init];
        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];
        //            sqlite3_close(db);
        //        }
    }
    NSString *save_path = [pdf_cache stringByAppendingPathComponent:filename];
    
    NSError *error = [[NSError alloc] init];
    
    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 :(NSString*) name
{
    
    
    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:@"img_cache"];
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL bdir=YES;
    if(!  [fileManager fileExistsAtPath:img_cache isDirectory:&bdir])
    {
        
        NSError *error = [[NSError alloc] init];
        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];
        //            sqlite3_close(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
{
    
    
    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:filename];
    
    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);
    
    
    // sqlite3_close(db);
    return data;
    
}
+ (BOOL) initializeDb {
    DebugLog (@"initializeDB");
    
    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 (true)//! [[NSFileManager defaultManager] fileExistsAtPath: database_path])
    {
        sqlite3 *db = [self get_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 ,ETA DATE, demension VARCHAR(20), seat_height VARCHAR(20), meterial 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));";
        
        
        
        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, price_name varchar(30));";
        NSString* create_category=@"CREATE TABLE IF NOT EXISTS category ( _id INTEGER PRIMARY KEY, code VARCHAR(20),name VARCHAR(20));";
        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, mode 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));";
        //        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_model db:db];
        
        [self execSql:create_image db:db];
        
        [self execSql:create_model_price db:db];
        [self execSql:create_category 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];
        
        //        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=sqlite3_close(db);
        return ret;
        //        int aaa = 0;
    }
    return YES;
    
    
    DebugLog (@"bottom of initializeDb");
}
+(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);
        
    }
}
+ (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"];
    }
    
    sqlite3_close(db);
    return json;
}
+(void)execSql:(NSString *)sql
{
    
    sqlite3 *db = [self get_db];
    [self execSql:sql db:db];
    
    sqlite3_close(db);
}
+ (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);
    }
    //   sqlite3_close(db);
    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 %@ orderby %@",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);
    }
    sqlite3_close(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 %@ orderby %@",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;
    
}


@end