// // 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), 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_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')),vendor_no char,warehouse_name char,terms char,etd TIMESTAMP,carrier char ,sales_terms char,port_destination char);"; 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')), 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_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_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" 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_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