// // 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