erpsuiteios/RedAnt ERP Mobile/common/Functions/offline/OLDataProvider.m

//
//  OLDataProvider.m
//  iSales-NPD
//
//  Created by Ray on 2/2/16.
//  Copyright © 2016 United Software Applications, Inc. All rights reserved.
//

#import "OLDataProvider.h"
#import "iSalesDB.h"
#import "RAUtils.h"
#import "AESCrypt.h"
#import "AppDelegate.h"
#import "iSalesDB+JKDB.h"


@interface OLDataProvider ()

@end

@implementation OLDataProvider
+(bool) check_offlinedata
{
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    sqlite3 *db = [iSalesDB get_db];
    NSString * where=@"1=1";
    //    if(appDelegate.user!=nil)
    //        where=[NSString stringWithFormat:@"lower(username)='%@'",appDelegate.user.lowercaseString];
    int count = [iSalesDB get_recordcount:db table:@"offline_login" where:where];
    if(count==0)
    {
        return false;
    }
    return true;
    //
    sqlite3_close(db);
}
+(NSDictionary*) offline_deletewishlist :(NSMutableDictionary *) params
{
    
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    NSMutableDictionary* ret=[[NSMutableDictionary alloc]init];
    sqlite3 *db = [iSalesDB get_db];
    NSString* collectId=params[@"collectId"];
    
    
    
    
    NSString* sqlQuery = [NSString stringWithFormat:@"delete from wishlist where _id in (%@);",collectId];
    [iSalesDB execSql:sqlQuery];
    int count=[iSalesDB get_recordcount:db table:@"wishlist" where:@"1=1"];
    sqlite3_close(db);
    
    appDelegate.wish_count =count;
    
    [appDelegate update_count_mark];
    ret[@"result"]= [NSNumber numberWithInt:2];
    return ret;
}
+(NSDictionary*) offline_add2wishlist :(NSMutableDictionary *) params
{
    
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    NSMutableDictionary* ret=[[NSMutableDictionary alloc]init];
    sqlite3 *db = [iSalesDB get_db];
    NSString* product_id=params[@"product_id"];
    
    
    NSArray* arr=[RAUtils string2arr:product_id separator:@","];
    
    
    for(int i=0;i<arr.count;i++)
    {
        NSString* where=[NSString stringWithFormat:@"product_id=%@",arr[i]];
        int count=[iSalesDB get_recordcount:db table:@"wishlist" where:where];
        if(count==0)
        {
            NSString* sqlQuery = [NSString stringWithFormat:@"insert into wishlist(product_id) values('%@');",arr[i]];
            [iSalesDB execSql:sqlQuery];
        }
    }
    int count=[iSalesDB get_recordcount:db table:@"wishlist" where:@"1=1"];
    sqlite3_close(db);
    
    appDelegate.wish_count =count;
    
    [appDelegate update_count_mark];
    ret[@"result"]= [NSNumber numberWithInt:2];
    return ret;
    //
    //return ret;
}
+(NSDictionary*) offline_wishlist :(NSMutableDictionary *) params
{
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    
    
    NSString* user = appDelegate.user;
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString* sqlQuery = @"select w.product_id,m.name||'\n'||m.description,w._id from wishlist as w  left join model as m on w.product_id=m.product_id order by w.create_time;";
    sqlite3_stmt * statement;
    
    
    NSMutableDictionary* ret=[[NSMutableDictionary alloc]init];
    int count=0;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            
            NSMutableDictionary* item=[[NSMutableDictionary alloc]init];
            
            int product_id = sqlite3_column_double(statement, 0);
            
            
            
            
            
            char *description = (char*)sqlite3_column_text(statement, 1);
            if(description==nil)
                description= "";
            NSString *nsdescription= [[NSString alloc]initWithUTF8String:description];
            
            int item_id = sqlite3_column_double(statement, 2);
            
            
            NSString *nsurl=[self model_category_img:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:product_id]] model_name:nil];
            
            //            char *url = (char*)sqlite3_column_text(statement, 3);
            //            if(url==nil)
            //                url="";
            //            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            
            
            item[@"product_id"]= [NSString stringWithFormat:@"%d",product_id];
            item[@"item_id"]= [NSString stringWithFormat:@"%d",item_id];
            item[@"description"]= nsdescription;
            item[@"img"]= nsurl;
            
            
            
            ret[[NSString stringWithFormat:@"item_%d",count]]= item;
            count++;
            
        }
        
        ret[@"count"]= [NSNumber numberWithInt:count];
        ret[@"total_count"]= [NSNumber numberWithInt:count];
        // ret[@"wish_count"]= [NSNumber numberWithInt:count];
        ret[@"result"]= [NSNumber numberWithInt:2];
        
        
        appDelegate.wish_count =count;
        
        [appDelegate update_count_mark];
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    return ret;
}
+(NSDictionary*) offline_notimpl
{
    
    NSMutableDictionary* ret=[[NSMutableDictionary alloc]init];
    ret[@"result"]=@"8";
    ret[@"err_msg"]=@"offline mode does not support this function.";
    return ret;
}
+(NSDictionary*) offline_home
{
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:@"offline_data/home.json"];
    
    
    NSData* json =nil;
    json=[NSData dataWithContentsOfFile:img_cache];
    NSError *error=nil;
    NSMutableDictionary* menu = [[NSJSONSerialization JSONObjectWithData:json options:NSJSONReadingMutableLeaves error:&error] mutableCopy];
    
    return menu;
}
+(NSDictionary*) offline_category_menu
{
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:@"offline_data/category_menu.json"];
    
    
    NSData* json =nil;
    json=[NSData dataWithContentsOfFile:img_cache];
    NSError *error=nil;
    NSMutableDictionary* menu = [[NSJSONSerialization JSONObjectWithData:json options:NSJSONReadingMutableLeaves error:&error] mutableCopy];
    
    return menu;
}
+(NSDictionary*) offline_commoneditor_partialrefresh :(NSMutableDictionary *) params
{
    NSString* offline_command=params[@"offline_Command"];
    NSDictionary* ret=nil;
    if([offline_command isEqualToString:@"model_NIYMAL"])
    {
        NSString* category = params[@"category"];
        ret = [self refresh_model_NIYMAL:category];
    }
    return ret;
}
+(NSDictionary*) refresh_model_NIYMAL :(NSString *) category
{
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:@"Regular Mode" forKey:@"mode"];
    NSMutableDictionary* detail1_section = [[self model_NIYMAL:category] mutableCopy];
    
    [ret setObject:detail1_section forKey:@"detail_1"];
    return ret;
}

+(NSMutableDictionary*) get_model_all_price:(NSString*) contact_id product_id:(int) product_id
{
    
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    //    NSArray* arr1 = [self get_user_all_price_type];
    NSArray* arr2 = [self get_contact_default_price_type:contact_id];
    
    //    NSSet *set1 = [NSSet setWithArray:arr1];
    //    NSMutableSet *set2 = [[NSSet setWithArray:arr2] mutableCopy];
    
    //    if(appDelegate.contact_id==nil)
    //        set2=[set1 mutableCopy];
    //    else
    //        [set2 intersectsSet:set1];
    
    
    //    NSArray *retarr = [set2  allObjects];
    
    NSString* whereprice=[RAUtils arr2string:arr2 separator:@"," trim:true brackets:@"'"];
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString* sqlQuery = nil;
    if(appDelegate.contact_id==nil)
        sqlQuery=[NSString stringWithFormat:@"select a.name,b.price from price as a left join model_price as b on a.name=b.price_name and b.product_id=%d  order by a.order_by;",product_id];
    else
        sqlQuery=[NSString stringWithFormat:@"select a.name,b.price from price as a left join model_price as b on a.name=b.price_name and b.product_id=%d and b.price_name in(%@) order by a.order_by;",product_id,whereprice];
    sqlite3_stmt * statement;
    
    
    NSMutableDictionary* ret=[[NSMutableDictionary alloc]init];
    int count=0;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            
            NSMutableDictionary* item=[[NSMutableDictionary alloc]init];
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            if(name==nil)
                name="";
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            // double price = sqlite3_column_double(statement, 1);
            
            //            char *isnull = (char*)sqlite3_column_text(statement, 1);
            //            if(isnull==nil)
            //                item[nsname]= @"No Price";
            //            else
            //                item[nsname]= [NSString stringWithFormat:@"%.2f",price];
            
            
            char *price = (char*)sqlite3_column_text(statement, 1);
            if(price!=nil)
            {
                NSString* nsprice = [[NSString alloc]initWithUTF8String:price];
                nsprice=[AESCrypt fastdecrypt:nsprice];
                if(nsprice.length>0)
                {
                    double dp= [nsprice doubleValue];
                    item[nsname]= [NSString stringWithFormat:@"%.2f",dp];
                }
            }
            else
            {
                item[nsname]= @"No Price";
            }
            
            
            // item[nsname]= nsprice;
            
            ret[[NSString stringWithFormat:@"item_%d",count]]= item;
            count++;
            
        }
        
        ret[@"count"]= [NSNumber numberWithInt:count];
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    return ret;
}

+(NSNumber*) get_model_default_price:(NSString*) contact_id product_id:(int) product_id
{
    NSArray* arr1 = [self get_user_all_price_type];
    NSArray* arr2 = [self get_contact_default_price_type:contact_id];
    
    //    NSSet *set1 = [NSSet setWithArray:arr1];
    //    NSMutableSet *set2 = [[NSSet setWithArray:arr2] mutableCopy];
    //    [set2 intersectsSet:set1];
    //
    //
    //    NSArray *retarr = [set2  allObjects];
    
    NSString* whereprice=nil;
    if(contact_id==nil)
        whereprice=[RAUtils arr2string:arr1 separator:@"," trim:true brackets:@"'"];
    else
        whereprice=[RAUtils arr2string:arr2 separator:@"," trim:true brackets:@"'"];
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString* sqlQuery = [NSString stringWithFormat:@"select price from model_price where product_id='%d' and price_name in(%@);",product_id,whereprice];
    sqlite3_stmt * statement;
    
    
    NSNumber* ret = nil;
    double dprice=DBL_MAX;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            // double val = sqlite3_column_double(statement, 0);
            char *price = (char*)sqlite3_column_text(statement, 0);
            if(price!=nil)
            {
                NSString* nsprice = [[NSString alloc]initWithUTF8String:price];
                nsprice=[AESCrypt fastdecrypt:nsprice];
                if(nsprice.length>0)
                {
                    double dp= [nsprice doubleValue];
                    if(dp<dprice)
                        dprice=dp;
                }
            }
        }
        
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    if(dprice==DBL_MAX)
        ret= nil;
    else
        ret= [NSNumber numberWithDouble:dprice];
    return ret;
}

+(NSArray*) get_user_all_price_type
{
    
    NSArray* ret=nil;
    
    sqlite3 *db = [iSalesDB get_db];
    // no customer assigned , use login user contact_id
    
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    NSString* sqlQuery = [NSString stringWithFormat:@"select price from offline_login where username='%@';",appDelegate.user];
    sqlite3_stmt * statement;
    
    
    
    
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            char *val = (char*)sqlite3_column_text(statement, 0);
            if(val==nil)
                val="";
            NSString* price = [[NSString alloc]initWithUTF8String:val];
            
            ret=[RAUtils string2arr:price separator:OFFLINE_ARRAY_SEPARATOR];
            
        }
        
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    return ret;
    
    
    
}
+(NSArray*) get_contact_default_price_type:(NSString*) contact_id
{
    
    sqlite3 *db = [iSalesDB get_db];
    if(contact_id==nil)
    {
        // no customer assigned , use login user contact_id
        
        UIApplication * app = [UIApplication sharedApplication];
        AppDelegate *appDelegate = (AppDelegate *)[app delegate];
        NSString* sqlQuery = [NSString stringWithFormat:@"select contact_id from offline_login where username='%@';",appDelegate.user];
        sqlite3_stmt * statement;
        
        
        
        
        //    int count=0;
        if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
        {
            
            
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                
                char *val = (char*)sqlite3_column_text(statement, 0);
                if(val==nil)
                    val="";
                contact_id = [[NSString alloc]initWithUTF8String:val];
                
                
                
            }
            
            
            
            
            sqlite3_finalize(statement);
            
            
            
            
        }
        
        if(contact_id.length<=0)
        {
            sqlite3_close(db);
            return nil;
        }
        
    }
    
    NSString* sqlQuery = [NSString stringWithFormat:@"select price_type from offline_contact where contact_id='%@';",contact_id];
    sqlite3_stmt * statement;
    
    
    
    NSArray* ret=nil;
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            char *price_type = (char*)sqlite3_column_text(statement, 0);
            if(price_type==nil)
                price_type="";
            NSString *nsprice_type = [[NSString alloc]initWithUTF8String:price_type];
            if(nsprice_type.length>0)
                ret=[RAUtils string2arr:nsprice_type separator:OFFLINE_ARRAY_SEPARATOR];
            else
                ret=nil;
            
            
        }
        
        
        
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    
    return ret;
    
    
}
+(NSString*) model_category_img :(NSString *) product_id model_name:(NSString *) model_name
{
    
    
    NSString* ret= nil;
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    NSString *sqlQuery = nil;
    //select url,type from model_image where product_id=%d and type=1 order by default_img desc , _id asc limit 1;",product_id
    
    if(product_id==nil)
        sqlQuery = [NSString stringWithFormat:@"select i.url from model m LEFT join model_image i on m.product_id = i.product_id  where m.name=%@ order by i.default_img desc, i._id asc limit 1;",model_name];
    else
        sqlQuery = [NSString stringWithFormat:@"select i.url from model m LEFT join model_image i on m.product_id = i.product_id  where m.product_id=%@ order by i.default_img desc, i._id asc limit 1;",product_id];
    
    sqlite3_stmt * statement;
    
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            char *imgurl = (char*)sqlite3_column_text(statement, 0);
            if(imgurl==nil)
                imgurl="";
            NSString *nsimgurl = [[NSString alloc]initWithUTF8String:imgurl];
            
            
            ret=nsimgurl;
            
            
        }
        
        sqlite3_finalize(statement);
    }
    else
    {
        [ret setValue:@"8" forKey:@"result"];
    }
    
    
    
    sqlite3_close(db);
    DebugLog(@"data string: %@",ret );
    
    
    return ret;
}
+(NSString*) model_default_category :(NSString *) product_id model_name:(NSString *) model_name
{
    
    
    NSString* ret= nil;
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    NSString *sqlQuery = nil;
    
    if(product_id==nil)
        sqlQuery = [NSString stringWithFormat:@"select default_category from model where name='%@';",model_name];
    else
        sqlQuery = [NSString stringWithFormat:@"select default_category from model where product_id=%@;",product_id];
    
    sqlite3_stmt * statement;
    
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            char *default_category = (char*)sqlite3_column_text(statement, 0);
            if(default_category==nil)
                default_category="";
            NSString *nsdefault_category = [[NSString alloc]initWithUTF8String:default_category];
            nsdefault_category=[nsdefault_category stringByReplacingOccurrencesOfString:@"%#" withString:@""];
            nsdefault_category=[nsdefault_category stringByReplacingOccurrencesOfString:@"#%" withString:@""];
            
            ret=nsdefault_category;
            
            
        }
        
        sqlite3_finalize(statement);
    }
    else
    {
        [ret setValue:@"8" forKey:@"result"];
    }
    
    
    
    sqlite3_close(db);
    DebugLog(@"data string: %@",ret );
    
    
    return ret;
}
+(NSDictionary*) offline_model :(NSMutableDictionary *) params
{
    
    
    
    
    
    
    NSString* model_name = [params valueForKey:@"product_name"];
    
    NSString* product_id = [params valueForKey:@"product_id"];
    
    NSString* category = [params valueForKey:@"category"];
    
    
    if(category==nil)
        category = [self model_default_category:product_id model_name:model_name];
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int count = [iSalesDB get_recordcount:db table:@"model" where:[NSString stringWithFormat:@"category like'#%%%@%%#'",category]];
    
    
    NSString *sqlQuery = nil;
    
    if(product_id==nil)
        sqlQuery = [NSString stringWithFormat:@"select name,description,product_id,color,legcolor,availability,incoming_stock,demension,seat_height,material,box_dim,volume,weight,model_set,load_ability,default_category,fabric_content,assembling,made_in,special_remarks,stockUom,product_group,selector_field,property_field,packaging from model where name='%@';",model_name];
    else
        sqlQuery = [NSString stringWithFormat:@"select name,description,product_id,color,legcolor,availability,incoming_stock,demension,seat_height,material,box_dim,volume,weight,model_set,load_ability,default_category,fabric_content,assembling,made_in,special_remarks,stockUom,product_group,selector_field,property_field,packaging from model where product_id=%@;",product_id];
    
    sqlite3_stmt * statement;
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:@"3" forKey:@"detail_section_count"];
    
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            //  NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            if(name==nil)
                name="";
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 1);
            if(description==nil)
                description="";
            NSString *nsdescription = [[NSString alloc]initWithUTF8String:description];
            
            
            int product_id = sqlite3_column_int(statement, 2);
            
            
            char *color = (char*)sqlite3_column_text(statement, 3);
            if(color==nil)
                color="";
            //            NSString *nscolor = [[NSString alloc]initWithUTF8String:color];
            //
            //            char *legcolor = (char*)sqlite3_column_text(statement, 4);
            //            if(legcolor==nil)
            //                legcolor="";
            //            NSString *nslegcolor = [[NSString alloc]initWithUTF8String:legcolor];
            //
            //
            int availability = sqlite3_column_int(statement, 5);
            //
            //            int incoming_stock = sqlite3_column_int(statement, 6);
            
            
            char *demension = (char*)sqlite3_column_text(statement, 7);
            if(demension==nil)
                demension="";
            NSString *nsdemension = [[NSString alloc]initWithUTF8String:demension];
            
            
            
            //          ,,,,,,,,,
            
            
            char *seat_height = (char*)sqlite3_column_text(statement, 8);
            if(seat_height==nil)
                seat_height="";
            NSString *nsseat_height = [[NSString alloc]initWithUTF8String:seat_height];
            
            
            char *material = (char*)sqlite3_column_text(statement, 9);
            if(material==nil)
                material="";
            NSString *nsmaterial = [[NSString alloc]initWithUTF8String:material];
            
            char *box_dim = (char*)sqlite3_column_text(statement, 10);
            if(box_dim==nil)
                box_dim="";
            NSString *nsbox_dim = [[NSString alloc]initWithUTF8String:box_dim];
            
            char *volume = (char*)sqlite3_column_text(statement, 11);
            if(volume==nil)
                volume="";
            NSString *nsvolume = [[NSString alloc]initWithUTF8String:volume];
            
            double weight = sqlite3_column_double(statement, 12);
            
            char *model_set = (char*)sqlite3_column_text(statement, 13);
            if(model_set==nil)
                model_set="";
            NSString *nsmodel_set = [[NSString alloc]initWithUTF8String:model_set];
            
            char *load_ability = (char*)sqlite3_column_text(statement, 14);
            if(load_ability==nil)
                load_ability="";
            NSString *nsload_ability = [[NSString alloc]initWithUTF8String:load_ability];
            
            char *default_category = (char*)sqlite3_column_text(statement, 15);
            if(default_category==nil)
                default_category="";
            NSString *nsdefault_category = [[NSString alloc]initWithUTF8String:default_category];
            
            
            char *fabric_content = (char*)sqlite3_column_text(statement, 16);
            if(fabric_content==nil)
                fabric_content="";
            NSString *nsfabric_content = [[NSString alloc]initWithUTF8String:fabric_content];
            
            char *assembling = (char*)sqlite3_column_text(statement, 17);
            if(assembling==nil)
                assembling="";
            NSString *nsassembling = [[NSString alloc]initWithUTF8String:assembling];
            
            char *made_in = (char*)sqlite3_column_text(statement, 18);
            if(made_in==nil)
                made_in="";
            NSString *nsmade_in = [[NSString alloc]initWithUTF8String:made_in];
            
            
            char *special_remarks = (char*)sqlite3_column_text(statement, 19);
            if(special_remarks==nil)
                special_remarks="";
            NSString *nsspecial_remarks = [[NSString alloc]initWithUTF8String:special_remarks];
            
            
            int stockUcom = sqlite3_column_double(statement, 20);
            
            char *product_group = (char*)sqlite3_column_text(statement, 21);
            if(product_group==nil)
                product_group="";
            NSString *nsproduct_group = [[NSString alloc]initWithUTF8String:product_group];
            
            //            char *fashion_selector = (char*)sqlite3_column_text(statement, 22);
            //            if(fashion_selector==nil)
            //                fashion_selector="";
            //            NSString *nsfashion_selector = [[NSString alloc]initWithUTF8String:fashion_selector];
            
            char *selector_field = (char*)sqlite3_column_text(statement, 22);
            if(selector_field==nil)
                selector_field="";
            NSString *nsselector_field = [[NSString alloc]initWithUTF8String:selector_field];
            
            char *property_field = (char*)sqlite3_column_text(statement, 23);
            if(property_field==nil)
                property_field="";
            NSString *nsproperty_field = [[NSString alloc]initWithUTF8String:property_field];
            
            
            
            char *packaging = (char*)sqlite3_column_text(statement, 24);
            if(packaging==nil)
                packaging="";
            NSString *nspackaging = [[NSString alloc]initWithUTF8String:packaging];
            
            [ret setValue:[NSString stringWithFormat:@"%d",product_id] forKey:@"product_id"];
            
            NSMutableDictionary* img_section = [[NSMutableDictionary alloc] init];
            
            NSString* model_s_img = [self model_category_img:[NSString stringWithFormat:@"%d",product_id] model_name:model_name];
            
            [img_section setValue:model_s_img forKey:@"model_s_img"];
            [img_section setObject:[self model_img:product_id] forKey:@"images"];
            [img_section setObject:[self model_property:product_id field:nsproperty_field] forKey:@"property"];
            [img_section setObject:[self model_selector:nsproduct_group field:nsselector_field] forKey:@"selector"];
            
            
            NSString* Availability=nil;
            if(availability>0)
                Availability=[NSString stringWithFormat:@"%d",availability];
            else
                Availability = @"In Production";
            
            [img_section setValue:Availability forKey:@"Availability"];
            [img_section setValue:[NSString stringWithFormat:@"%d",stockUcom] forKey:@"stockUom"];
            
            AppDelegate *appDelegate = (AppDelegate *)[[UIApplication sharedApplication] delegate];
            
            
            NSString* Price=nil;
            if(appDelegate.bLogin==false)
                Price=@"Must Sign in.";
            else
            {
                NSNumber* price= [self get_model_default_price:appDelegate.contact_id product_id:product_id];
                if(price==nil)
                    Price=@"No Price.";
                else
                    Price=[NSString stringWithFormat:@"%.2f",price.floatValue];
            }
            
            [img_section setValue:Price forKey:@"price"];
            [img_section setValue:nsname forKey:@"model_name"];
            [img_section setValue:nsdescription forKey:@"model_descrition"];
            
            [ret setObject:img_section forKey:@"img_section"];
            
            NSMutableDictionary* detail0_section = [[NSMutableDictionary alloc] init];
            int detail0_item_count=0;
            
            [detail0_section setObject:[self model_kvItem:@"Dimension" value:nsdemension] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Seat Height" value:nsseat_height] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Material" value:nsmaterial] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Box dimension" value:nsbox_dim] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Volume" value:nsvolume] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Weight" value:[NSString stringWithFormat:@"%0.2f",weight]] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Set" value:nsmodel_set] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Load ability" value:nsload_ability] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Fabric Content" value:nsfabric_content] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Assembling" value:nsassembling] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Made in" value:nsmade_in] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Special Remarks" value:nsspecial_remarks] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            [detail0_section setObject:[self model_kvItem:@"Packaging" value:nspackaging] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            
            
            NSDictionary* pricejson=[self get_model_all_price:appDelegate.contact_id product_id:product_id];
            for(int l=0;l<[pricejson[@"count"] intValue];l++)
            {
                NSDictionary* price_item = pricejson[[NSString stringWithFormat:@"item_%d",l]];
                [detail0_section setObject:[self model_kvItem:price_item.allKeys[0] value:price_item.allValues[0]] forKey:[NSString stringWithFormat:@"item_%d",detail0_item_count++]];
            }
            
            
            
            
            [detail0_section setValue:[NSString stringWithFormat:@"%d",detail0_item_count] forKey:@"count"];
            [detail0_section setValue:@"kv" forKey:@"type"];
            [detail0_section setValue:@"Product Information" forKey:@"title"];
            
            [ret setObject:detail0_section forKey:@"detail_0"];
            
            
            NSMutableDictionary* detail1_section = [[self model_NIYMAL:category] mutableCopy];
            //            [detail1_section setValue:@"detail" forKey:@"target"];
            //            [detail1_section setValue:@"popup" forKey:@"action"];
            //            [detail1_section setValue:@"content" forKey:@"type"];
            //            [detail1_section setValue:@"New Items You May Also Like" forKey:@"title"];
            //            [detail1_section setValue:@"model_NIYMAL" forKey:@"data_interface"];
            //            [detail1_section setValue:@"true" forKey:@"single_row"];
            //            [detail1_section setValue:@"true" forKey:@"partial_refresh"];
            [ret setObject:detail1_section forKey:@"detail_1"];
            
            
            
            
            NSMutableDictionary* detail2_section = [[NSMutableDictionary alloc]init];
            [detail2_section setValue:@"detail" forKey:@"target"];
            [detail2_section setValue:@"popup" forKey:@"action"];
            [detail2_section setValue:@"content" forKey:@"type"];
            [detail2_section setValue:@"Recently Viewed" forKey:@"title"];
            [detail2_section setValue:@"true" forKey:@"single_row"];
            [detail2_section setValue:@"local" forKey:@"data"];
            [ret setObject:detail2_section forKey:@"detail_2"];
        }
        
        
        
        
        sqlite3_finalize(statement);
    }
    else
    {
        [ret setValue:@"8" forKey:@"result"];
    }
    NSLog(@"count:%d",count);
    
    
    sqlite3_close(db);
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*) offline_category :(NSMutableDictionary *) params
{
    NSString* orderCode = [params valueForKey:@"orderCode"];
    
    NSString* category = [params valueForKey:@"category"];
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int count = [iSalesDB get_recordcount:db table:@"model" where:[NSString stringWithFormat:@"category like'%%#%@#%%'",category]];
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from model m left join wishlist w on m.product_id=w.product_id where m.category like'%%#%@#%%' order by m.name limit %d offset %d ;",category,limit, offset];
    
    DebugLog(@"offline_category sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:[NSString stringWithFormat:@"%d",count] forKey:@"item_total_count"];
    NSMutableDictionary* items = [[NSMutableDictionary alloc] init];
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            if(name==nil)
                name="";
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 1);
            if(description==nil)
                description="";
            NSString *nsdescription = [[NSString alloc]initWithUTF8String:description];
            
            
            
            int product_id = sqlite3_column_int(statement, 2);
            
            
            //            char *url = (char*)sqlite3_column_text(statement, 3);
            //            if(url==nil)
            //                url="";
            //            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            int wid = sqlite3_column_int(statement, 3);
            int closeout = sqlite3_column_int(statement, 4);
            
            NSMutableDictionary* imgjson=[self category_img:product_id];
            
            if(wid==0)
                [item setValue:@"false" forKey:@"wish_exists"];
            else
                [item setValue:@"true" forKey:@"wish_exists"];
            
            if(closeout==0)
                [item setValue:@"false" forKey:@"is_closeout"];
            else
                [item setValue:@"true" forKey:@"is_closeout"];
            
            [item addEntriesFromDictionary:imgjson];
            
            
            // [item setValue:nsurl forKey:@"img"];
            [item setValue:nsname forKey:@"name"];
            [item setValue:nsdescription forKey:@"description"];
            [item setValue:[NSString stringWithFormat:@"%d",product_id] forKey:@"product_id"];
            [items setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
        }
        [items setValue:[NSString stringWithFormat:@"%d",i] forKey:@"count"];
        [ret setObject:items forKey:@"items"];
        sqlite3_finalize(statement);
    }
    NSLog(@"count:%d",count);
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*)  offline_search:(NSMutableDictionary *) params
{
    
    
    
    
    
    
    NSString* orderCode = [params valueForKey:@"orderCode"];
    
    NSString* keyword = [params valueForKey:@"keyword"];
    keyword=keyword.lowercaseString;
    
    bool exactMatch = [[params valueForKey:@"exactMatch"] boolValue];
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int count = [iSalesDB get_recordcount:db table:@"model" where:[NSString stringWithFormat:@"lower(name) like'%%%@%%' or lower(description) like'%%%@%%'",keyword,keyword]];
    
    
    NSString *sqlQuery = nil;
    if(exactMatch )
        sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from model m left join wishlist w on m.product_id=w.product_id where lower(name) like'%@%%' order by m.name limit %d offset %d ;",keyword,limit, offset];
    else
        sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from model m left join wishlist w on m.product_id=w.product_id where lower(name) like'%%%@%%' or lower(description) like'%%%@%%' order by m.name limit %d offset %d ;",keyword,keyword,limit, offset];
    
    DebugLog(@"offline_search sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:[NSString stringWithFormat:@"%d",count] forKey:@"item_total_count"];
    NSMutableDictionary* items = [[NSMutableDictionary alloc] init];
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 1);
            NSString *nsdescription = [[NSString alloc]initWithUTF8String:description];
            
            
            
            int product_id = sqlite3_column_int(statement, 2);
            
            
            //            char *url = (char*)sqlite3_column_text(statement, 3);
            //            if(url==nil)
            //                url="";
            //            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            int wid = sqlite3_column_int(statement, 3);
            int closeout = sqlite3_column_int(statement, 4);
            
            NSMutableDictionary* imgjson=[self category_img:product_id];
            
            if(wid==0)
                [item setValue:@"false" forKey:@"wish_exists"];
            else
                [item setValue:@"true" forKey:@"wish_exists"];
            
            if(closeout==0)
                [item setValue:@"false" forKey:@"is_closeout"];
            else
                [item setValue:@"true" forKey:@"is_closeout"];
            
            [item addEntriesFromDictionary:imgjson];
            
            
            // [item setValue:nsurl forKey:@"img"];
            [item setValue:nsname forKey:@"fash_name"];
            [item setValue:nsdescription forKey:@"description"];
            [item setValue:[NSString stringWithFormat:@"%d",product_id] forKey:@"product_id"];
            [items setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
        }
        [items setValue:[NSString stringWithFormat:@"%d",i] forKey:@"count"];
        [ret setObject:items forKey:@"items"];
        sqlite3_finalize(statement);
    }
    NSLog(@"count:%d",count);
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*)  offline_itemsearch:(NSMutableDictionary *) params
{
    
    
    
    //    [params setValue:covertype forKey:@"covertype"];
    //    [params setValue:ctgid forKey:@"ctgId"];
    //    [params setValue:modelname forKey:@"modelName"];
    //    [params setValue:modeldescrip forKey:@"modelDescription"];
    //    [params setValue:alert forKey:@"alert"];
    //    [params setValue:qty forKey:@"sold_by_qty"];
    //    [params setValue:available forKey:@"available"];
    //    [params setValue:price forKey:@"price"];
    //    [params setValue:bestseller forKey:@"bestseller"];
    
    NSString* orderCode = [params valueForKey:@"orderCode"];
    int covertype = [[params valueForKey:@"covertype"] intValue];
    
    NSString* where= nil;
    NSString* orderby= @"m.name";
    switch (covertype) {
        case 0:
        {
            where=@"m.category like'%%#005#%%'";
            break;
        }
        case 1:
        {
            where=@"m.alert like '%QS%'";
            break;
        }
        case 2:
        {
            where=@"m.availability>0";
            break;
        }
        case 3:
        {
            where=@"m.best_seller>0";
            orderby=@"m.best_seller desc";
            break;
        }
        default:
            where=@"1=1";
            break;
    }
    //   NSString* keyword = [params valueForKey:@"keyword"];
    
    //    bool exactMatch = [[params valueForKey:@"exactMatch"] boolValue];
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int count = [iSalesDB get_recordcount:db table:@"model" where:where];
    
    
    NSString *sqlQuery = nil;
    sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from model m left join wishlist w on m.product_id=w.product_id where %@ order by %@ limit %d offset %d ;",where,orderby,limit, offset];
    
    DebugLog(@"offline_itemsearch sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:[NSString stringWithFormat:@"%d",count] forKey:@"item_total_count"];
    NSMutableDictionary* items = [[NSMutableDictionary alloc] init];
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 1);
            NSString *nsdescription = [[NSString alloc]initWithUTF8String:description];
            
            
            
            int product_id = sqlite3_column_int(statement, 2);
            
            
            //            char *url = (char*)sqlite3_column_text(statement, 3);
            //            if(url==nil)
            //                url="";
            //            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            int wid = sqlite3_column_int(statement, 3);
            int closeout = sqlite3_column_int(statement, 4);
            
            NSMutableDictionary* imgjson=[self category_img:product_id];
            
            if(wid==0)
                [item setValue:@"false" forKey:@"wish_exists"];
            else
                [item setValue:@"true" forKey:@"wish_exists"];
            
            if(closeout==0)
                [item setValue:@"false" forKey:@"is_closeout"];
            else
                [item setValue:@"true" forKey:@"is_closeout"];
            
            [item addEntriesFromDictionary:imgjson];
            
            
            // [item setValue:nsurl forKey:@"img"];
            [item setValue:nsname forKey:@"fash_name"];
            [item setValue:nsdescription forKey:@"description"];
            [item setValue:[NSString stringWithFormat:@"%d",product_id] forKey:@"product_id"];
            [items setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
        }
        [items setValue:[NSString stringWithFormat:@"%d",i] forKey:@"count"];
        [ret setObject:items forKey:@"items"];
        sqlite3_finalize(statement);
    }
    NSLog(@"count:%d",count);
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*) offline_logout :(NSMutableDictionary *) params
{
    
    
    //
    //    NSString* user = [params valueForKey:@"user"];
    //
    //    NSString* password = [params valueForKey:@"password"];
    
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    ret[@"result"]=[NSNumber numberWithInt:2 ];
    
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    appDelegate.cart_count = 0;//[[jsobj valueForKey:@"cart_count"] intValue];
    appDelegate.wish_count =0;//[[jsobj valueForKey:@"wish_count"] intValue];
    appDelegate.port_count =0;//[[jsobj valueForKey:@"portfolio_count"] intValue];
    
    [appDelegate update_count_mark];
    
    
    appDelegate.can_show_price =false;
    appDelegate.can_see_price =false;
    appDelegate.can_create_portfolio =false;
    appDelegate.can_create_order =false;
    
    
    appDelegate.can_cancel_order =false;
    appDelegate.can_set_cart_price =false;
    appDelegate.can_delete_order =false;
    appDelegate.can_submit_order =false;
    appDelegate.can_set_tearsheet_price =false;
    appDelegate.can_update_contact_info = false;
    
    appDelegate.save_order_logout = false;
    appDelegate.submit_order_logout = false;
    appDelegate.alert_sold_in_quantities = false;
    
    appDelegate.ipad_perm =nil ;
    appDelegate.user_type = USER_ROLE_UNKNOWN;
    appDelegate.OrderFilter= nil;
    [appDelegate SetSo:nil];
    [appDelegate set_main_button_panel];
    
    
    //    sqlite3 *db = [iSalesDB get_db];
    //
    //
    //
    //
    //
    //    NSString *sqlQuery = [NSString stringWithFormat:@"select 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 from offline_login where lower(username)='%@' and password='%@'",user.lowercaseString ,[AESCrypt encrypt:password password:@"usai"]];
    //
    //
    //
    //
    //    DebugLog(@"offline_login sql:%@",sqlQuery);
    //    sqlite3_stmt * statement;
    //
    //
    //    [ret setValue:[NSNumber numberWithInt:AP_USER_NOT_AUTH ] forKey:@"result"];
    //
    //
    //    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    //    {
    //
    //
    //        if (sqlite3_step(statement) == SQLITE_ROW)
    //        {
    //
    //            [ret setValue:[NSNumber numberWithInt:AP_USER_AUTH ] forKey:@"result"];
    //            NSMutableDictionary* header = [[NSMutableDictionary alloc] init];
    //
    //
    //
    //            int can_show_price = sqlite3_column_int(statement, 0);
    //            int can_see_price = sqlite3_column_int(statement, 1);
    //
    //            char *contact_id = (char*)sqlite3_column_text(statement, 2);
    //            if(contact_id==nil)
    //                contact_id="";
    //            NSString *nscontact_id= [[NSString alloc]initWithUTF8String:contact_id];
    //
    //            int user_type = sqlite3_column_int(statement, 3);
    //
    //            int can_cancel_order = sqlite3_column_int(statement, 4);
    //            int can_set_cart_price = sqlite3_column_int(statement, 5);
    //            int can_create_portfolio = sqlite3_column_int(statement, 6);
    //            int can_delete_order = sqlite3_column_int(statement, 7);
    //            int can_submit_order = sqlite3_column_int(statement, 8);
    //            int can_set_tearsheet_price = sqlite3_column_int(statement, 9);
    //            int can_create_order = sqlite3_column_int(statement, 10);
    //
    //
    //            char *mode = (char*)sqlite3_column_text(statement, 11);
    //            if(mode==nil)
    //                mode="";
    //            NSString *nsmode= [[NSString alloc]initWithUTF8String:mode];
    //
    //
    //            char *username = (char*)sqlite3_column_text(statement, 12);
    //            if(username==nil)
    //                username="";
    //            NSString *nsusername= [[NSString alloc]initWithUTF8String:username];
    //
    //
    //            [header setValue:[NSNumber numberWithBool:can_show_price]   forKey:@"can_show_price"];
    //            [header setValue:[NSNumber numberWithBool:can_see_price]   forKey:@"can_see_price"];
    //            [header setValue:nscontact_id   forKey:@"contact_id"];
    //            [header setValue:[NSNumber numberWithBool:user_type]   forKey:@"user_type"];
    //            [header setValue:[NSNumber numberWithBool:can_cancel_order]   forKey:@"can_cancel_order"];
    //            [header setValue:[NSNumber numberWithBool:can_set_cart_price]   forKey:@"can_set_cart_price"];
    //            [header setValue:[NSNumber numberWithBool:can_create_portfolio]   forKey:@"can_create_portfolio"];
    //            [header setValue:[NSNumber numberWithBool:can_delete_order]   forKey:@"can_delete_order"];
    //            [header setValue:[NSNumber numberWithBool:can_submit_order]   forKey:@"can_submit_order"];
    //            [header setValue:[NSNumber numberWithBool:can_set_tearsheet_price]   forKey:@"can_set_tearsheet_price"];
    //            [header setValue:[NSNumber numberWithBool:can_create_order]   forKey:@"can_create_order"];
    //
    //            [header setValue:nsusername   forKey:@"username"];
    //
    //
    //            [ret setObject:header forKey:@"header"];
    //            [ret setValue:nsmode   forKey:@"mode"];
    //
    //
    //        }
    //
    //
    //
    //        sqlite3_finalize(statement);
    //    }
    //
    //
    //
    //    sqlite3_close(db);
    //
    //
    //
    //
    //    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSData*) offline_createorder :(NSMutableDictionary *) params
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    return [RAUtils dict2data:ret];
    
}
+(NSData*) offline_orderlist :(NSMutableDictionary *) params
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    NSString* keyword = [params valueForKey:@"keyWord"];
    
    NSString* where=@"1=1";
    if(keyword!=nil)
        where=[NSString stringWithFormat:@"lower(o.order_id) like '%%%@%%' or lower(c.company_name) like '%%%@%%' or lower(c.company_name) like '%%%@%%' or lower(o.create_by) like '%%%@%%'",keyword.lowercaseString,keyword.lowercaseString,keyword.lowercaseString,keyword.lowercaseString];
    NSString*     sqlQuery=[NSString stringWithFormat:@"SELECT o._id,o.so_id,o.status,o.sales_rep,o.create_by,c.company_name,o.create_time,o.total_price from offline_order o left join offline_contact c on o.contact_id=c.contact_idr where %@ order by create_time limit %d offset %d" ,where, limit, offset];
    
    sqlite3 *db = [iSalesDB get_db];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int count=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            
            NSMutableDictionary* item=[[NSMutableDictionary alloc]init];
            
            int order_id = sqlite3_column_double(statement, 0);
            
            
            
            
            
            char *soid = (char*)sqlite3_column_text(statement, 1);
            if(soid==nil)
                soid= "";
            NSString *nssoid= [[NSString alloc]initWithUTF8String:soid];
            
            
            int status = sqlite3_column_double(statement, 2);
            
            
            char *sales_rep = (char*)sqlite3_column_text(statement, 3);
            if(sales_rep==nil)
                sales_rep= "";
            NSString *nssales_rep= [[NSString alloc]initWithUTF8String:sales_rep];
            
            char *create_by = (char*)sqlite3_column_text(statement, 4);
            if(create_by==nil)
                create_by= "";
            NSString *nscreate_by= [[NSString alloc]initWithUTF8String:create_by];
            
            char *company_name = (char*)sqlite3_column_text(statement, 5);
            if(company_name==nil)
                company_name= "";
            NSString *nscompany_name= [[NSString alloc]initWithUTF8String:company_name];
            
            char *create_time = (char*)sqlite3_column_text(statement, 6);
            if(create_time==nil)
                create_time= "";
            NSString *nscreate_time= [[NSString alloc]initWithUTF8String:create_time];
            
            double total_price = sqlite3_column_double(statement, 7);
            
            
            
            
            item[@"sales_rep"]= nssales_rep;
            item[@"orderStatus"]= [NSString stringWithFormat:@"%d",status];
            item[@"so#"]= nssoid;
            item[@"price"]= [NSString stringWithFormat:@"$%.2f",total_price];;
            item[@"create_by"]= nscreate_by;
            item[@"customer_name"]= nscompany_name;
            item[@"order_id"]= [NSString stringWithFormat:@"%d",order_id];;
            item[@"purchase_time"]= nscreate_time;
            
            
            ret[[NSString stringWithFormat:@"item_%d",count]]= item;
            count++;
            
        }
        
        ret[@"count"]= [NSNumber numberWithInt:count];
        //ret[@"total_count"]= [NSNumber numberWithInt:count];
        // ret[@"wish_count"]= [NSNumber numberWithInt:count];
        ret[@"result"]= [NSNumber numberWithInt:2];
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    return [RAUtils dict2data:ret];
}


+(NSDictionary*) offline_login :(NSMutableDictionary *) params
{
    
    
    
    NSString* user = [params valueForKey:@"user"];
    
    NSString* password = [params valueForKey:@"password"];
    
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select 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 from offline_login where lower(username)='%@' and password='%@'",user.lowercaseString ,[AESCrypt encrypt:password password:@"usai"]];
    
    
    
    
    DebugLog(@"offline_login sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    
    
    [ret setValue:[NSNumber numberWithInt:AP_USER_NOT_AUTH ] forKey:@"result"];
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            [ret setValue:[NSNumber numberWithInt:AP_USER_AUTH ] forKey:@"result"];
            NSMutableDictionary* header = [[NSMutableDictionary alloc] init];
            
            
            
            int can_show_price = sqlite3_column_int(statement, 0);
            int can_see_price = sqlite3_column_int(statement, 1);
            
            char *contact_id = (char*)sqlite3_column_text(statement, 2);
            if(contact_id==nil)
                contact_id="";
            NSString *nscontact_id= [[NSString alloc]initWithUTF8String:contact_id];
            
            int user_type = sqlite3_column_int(statement, 3);
            
            int can_cancel_order = sqlite3_column_int(statement, 4);
            int can_set_cart_price = sqlite3_column_int(statement, 5);
            int can_create_portfolio = sqlite3_column_int(statement, 6);
            int can_delete_order = sqlite3_column_int(statement, 7);
            int can_submit_order = sqlite3_column_int(statement, 8);
            int can_set_tearsheet_price = sqlite3_column_int(statement, 9);
            int can_create_order = sqlite3_column_int(statement, 10);
            
            
            char *mode = (char*)sqlite3_column_text(statement, 11);
            if(mode==nil)
                mode="";
            NSString *nsmode= [[NSString alloc]initWithUTF8String:mode];
            
            
            char *username = (char*)sqlite3_column_text(statement, 12);
            if(username==nil)
                username="";
            NSString *nsusername= [[NSString alloc]initWithUTF8String:username];
            
            
            [header setValue:[NSNumber numberWithBool:can_show_price]   forKey:@"can_show_price"];
            [header setValue:[NSNumber numberWithBool:can_see_price]   forKey:@"can_see_price"];
            [header setValue:nscontact_id   forKey:@"contact_id"];
            [header setValue:[NSNumber numberWithBool:user_type]   forKey:@"user_type"];
            [header setValue:[NSNumber numberWithBool:can_cancel_order]   forKey:@"can_cancel_order"];
            [header setValue:[NSNumber numberWithBool:can_set_cart_price]   forKey:@"can_set_cart_price"];
            [header setValue:[NSNumber numberWithBool:can_create_portfolio]   forKey:@"can_create_portfolio"];
            [header setValue:[NSNumber numberWithBool:can_delete_order]   forKey:@"can_delete_order"];
            [header setValue:[NSNumber numberWithBool:can_submit_order]   forKey:@"can_submit_order"];
            [header setValue:[NSNumber numberWithBool:can_set_tearsheet_price]   forKey:@"can_set_tearsheet_price"];
            [header setValue:[NSNumber numberWithBool:can_create_order]   forKey:@"can_create_order"];
            
            [header setValue:nsusername   forKey:@"username"];
            
            
            [ret setObject:header forKey:@"header"];
            [ret setValue:nsmode   forKey:@"mode"];
            
            
        }
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*) offline_contactinfo :(NSMutableDictionary *) params
{
    NSString* contactId = [params valueForKey:@"contactId"];
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    
    
    
    NSString *sqlQuery = nil;
    
    
    {
        sqlQuery=[NSString stringWithFormat:@"select editable,company_name,country,addr,zipcode,state,city,contact_name,phone,contact_id,addr_1,addr_2,addr_3,addr_4,first_name,last_name,fax,email,img_0,img_1,img_2,price_type,notes,sales_rep from offline_contact where contact_id='%@'",contactId];
        
    }
    
    
    DebugLog(@"offline_contactlist sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    
    
    [ret setValue:@"2" forKey:@"result"];
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        //int i = 0;
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            [ret setValue:[NSNumber numberWithInt:AP_USER_AUTH ] forKey:@"result"];
            
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            
            //            int editable  = sqlite3_column_int(statement, 0);
            
            
            char *company_name = (char*)sqlite3_column_text(statement, 1);
            NSString *nscompany_name =nil;
            if(company_name==nil)
                nscompany_name=@"";
            else
                nscompany_name=[[NSString alloc]initWithUTF8String:company_name] ;
            
            
            char *country = (char*)sqlite3_column_text(statement, 2);
            if(country==nil)
                country="";
            NSString *nscountry= [[NSString alloc]initWithUTF8String:country];
            
            
            //            char *addr = (char*)sqlite3_column_text(statement, 3);
            //            if(addr==nil)
            //                addr="";
            //            NSString *nsaddr= [[NSString alloc]initWithUTF8String:addr];
            
            
            char *zipcode = (char*)sqlite3_column_text(statement, 4);
            if(zipcode==nil)
                zipcode="";
            NSString *nszipcode= [[NSString alloc]initWithUTF8String:zipcode];
            
            
            char *state = (char*)sqlite3_column_text(statement, 5);
            if(state==nil)
                state="";
            NSString *nsstate= [[NSString alloc]initWithUTF8String:state];
            
            char *city = (char*)sqlite3_column_text(statement, 6);
            if(city==nil)
                city="";
            NSString *nscity= [[NSString alloc]initWithUTF8String:city];
            
            //            char *contact_name = (char*)sqlite3_column_text(statement, 7);
            //            NSString *nscontact_name = nil;
            //            if(contact_name==nil)
            //                nscontact_name=@"";
            //            else
            //                nscontact_name= [[NSString alloc]initWithUTF8String:contact_name];
            
            char *phone = (char*)sqlite3_column_text(statement, 8);
            NSString *nsphone = nil;
            if(phone==nil)
                nsphone=@"";
            else
                nsphone= [[NSString alloc]initWithUTF8String:phone];
            
            
            char *contact_id = (char*)sqlite3_column_text(statement, 9);
            if(contact_id==nil)
                contact_id="";
            NSString *nscontact_id= [[NSString alloc]initWithUTF8String:contact_id];
            
            char *addr_1 = (char*)sqlite3_column_text(statement, 10);
            if(addr_1==nil)
                addr_1="";
            NSString *nsaddr_1= [[NSString alloc]initWithUTF8String:addr_1];
            
            char *addr_2 = (char*)sqlite3_column_text(statement, 11);
            if(addr_2==nil)
                addr_2="";
            NSString *nsaddr_2= [[NSString alloc]initWithUTF8String:addr_2];
            
            
            char *addr_3 = (char*)sqlite3_column_text(statement, 12);
            if(addr_3==nil)
                addr_3="";
            NSString *nsaddr_3= [[NSString alloc]initWithUTF8String:addr_3];
            
            
            char *addr_4 = (char*)sqlite3_column_text(statement, 13);
            if(addr_4==nil)
                addr_4="";
            NSString *nsaddr_4= [[NSString alloc]initWithUTF8String:addr_4];
            
            
            char *first_name = (char*)sqlite3_column_text(statement, 14);
            if(first_name==nil)
                first_name="";
            NSString *nsfirst_name= [[NSString alloc]initWithUTF8String:first_name];
            
            
            char *last_name = (char*)sqlite3_column_text(statement, 15);
            if(last_name==nil)
                last_name="";
            NSString *nslast_name= [[NSString alloc]initWithUTF8String:last_name];
            
            char *fax = (char*)sqlite3_column_text(statement, 16);
            NSString *nsfax = nil;
            if(fax==nil)
                nsfax=@"";
            else
                nsfax= [[NSString alloc]initWithUTF8String:fax];
            
            char *email = (char*)sqlite3_column_text(statement, 17);
            NSString *nsemail = nil;
            if(email==nil)
                nsemail=@"";
            else
                nsemail= [[NSString alloc]initWithUTF8String:email];
            
            char *img_0 = (char*)sqlite3_column_text(statement, 18);
            NSString *nsimg_0 = nil;
            if(img_0==nil)
                nsimg_0=@"";
            else
                nsimg_0= [[NSString alloc]initWithUTF8String:img_0];
            
            char *img_1 = (char*)sqlite3_column_text(statement, 19);
            NSString *nsimg_1 = nil;
            if(img_1==nil)
                nsimg_1=@"";
            else
                nsimg_1= [[NSString alloc]initWithUTF8String:img_1];
            
            char *img_2 = (char*)sqlite3_column_text(statement, 20);
            NSString *nsimg_2 = nil;
            if(img_2==nil)
                nsimg_2=@"";
            else
                nsimg_2= [[NSString alloc]initWithUTF8String:img_2];
            
            char *price_type = (char*)sqlite3_column_text(statement, 21);
            NSString *nsprice_type = nil;
            if(price_type==nil)
                nsprice_type=@"";
            else
                nsprice_type= [[NSString alloc]initWithUTF8String:price_type];
            
            
            char *notes = (char*)sqlite3_column_text(statement, 22);
            NSString *nsnotes = nil;
            if(notes==nil)
                nsnotes=@"";
            else
                nsnotes= [[NSString alloc]initWithUTF8String:notes];
            
            
            char *salesrep = (char*)sqlite3_column_text(statement, 23);
            NSString *nssalesrep = nil;
            if(salesrep==nil)
                nssalesrep=@"";
            else
                nssalesrep= [[NSString alloc]initWithUTF8String:salesrep];
            
            
            {
                // decrypt
                
                nscompany_name=[AESCrypt fastdecrypt:nscompany_name];
                
                //                nscompany_name=[NSString stringWithFormat:@"<b>%@</b>",nscompany_name];
                
                nsphone=[AESCrypt fastdecrypt:nsphone];
                //                nsphone=[NSString stringWithFormat:@"TEL:%@",nsphone];
                
                nsaddr_1=[AESCrypt fastdecrypt:nsaddr_1];
                //                nsaddr_1=[NSString stringWithFormat:@"TEL:%@",nsphone];
                
            }
            
            NSMutableArray* arr_name= [[NSMutableArray alloc] init];
            
            [arr_name addObject:nsfirst_name];
            [arr_name addObject:nslast_name];
            
            NSString *nscontact_name=[RAUtils arr2string:arr_name separator:@" " trim:true];
            
            
            NSMutableArray* arr_ext= [[NSMutableArray alloc] init];
            
            [arr_ext addObject:nsaddr_1];
            [arr_ext addObject:nsaddr_2];
            [arr_ext addObject:nsaddr_3];
            [arr_ext addObject:nsaddr_4];
            [arr_ext addObject:@"\r\n"];
            
            [arr_ext addObject:nscity];
            [arr_ext addObject:nsstate];
            [arr_ext addObject:nszipcode];
            [arr_ext addObject:nscountry];
            
            NSString *nsext=[RAUtils arr2string:arr_ext separator:@", " trim:true];
            
            
            nsext=[nsext stringByReplacingOccurrencesOfString:@", \r\n," withString:@"\r\n"];
            
            
            [item setValue:nsimg_2   forKey:@"business_card_2"];
            [item setValue:nsimg_0   forKey:@"business_card_0"];
            [item setValue:nscountry   forKey:@"customer_country"];
            [item setValue:nsphone   forKey:@"customer_phone"];
            [item setValue:nsimg_1   forKey:@"business_card_1"];
            [item setValue:nscompany_name   forKey:@"customer_name"];
            [item setValue:nsprice_type   forKey:@"customer_price_type"];
            [item setValue:nsfirst_name   forKey:@"customer_first_name"];
            [item setValue:nsext   forKey:@"customer_contact_ext"];
            [item setValue:nszipcode   forKey:@"customer_zipcode"];
            [item setValue:nsaddr_1   forKey:@"customer_address1"];
            [item setValue:nsaddr_2   forKey:@"customer_address2"];
            [item setValue:nsaddr_3   forKey:@"customer_address3"];
            [item setValue:nsaddr_4   forKey:@"customer_address4"];
            [item setValue:nsnotes   forKey:@"customer_contact_notes"];
            [item setValue:nslast_name   forKey:@"customer_last_name"];
            [item setValue:nscity   forKey:@"customer_city"];
            [item setValue:nsstate   forKey:@"customer_state"];
            [item setValue:nssalesrep   forKey:@"customer_sales_rep"];
            [item setValue:contactId   forKey:@"customer_cid"];
            [item setValue:nscontact_name   forKey:@"customer_contact"];
            [item setValue:nsfax   forKey:@"customer_fax"];
            [item setValue:nsemail   forKey:@"customer_email"];
            
            
            
            [ret setObject:item forKey:@"customerInfo"];
            // i++;
            
            
            
        }
        UIApplication * app = [UIApplication sharedApplication];
        AppDelegate *appDelegate = (AppDelegate *)[app delegate];
        [ret setValue:appDelegate.mode   forKey:@"mode"];
        [ret setValue:[NSNumber numberWithInt:1 ]    forKey:@"count"];
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSData *) offline_saveBusinesscard:(NSData *) image
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    NSString *offline_createimg = [cachefolder stringByAppendingPathComponent:@"offline_createimg"];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:@"img_cache"];
    
    
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL bdir=YES;
    if(!  [fileManager fileExistsAtPath:offline_createimg isDirectory:&bdir])
    {
        
        NSError *error = nil;
        bool bsuccess=[fileManager createDirectoryAtPath:offline_createimg withIntermediateDirectories:YES attributes:nil error:&error];
        
        if(!bsuccess)
        {
            DebugLog(@"Create offline_createimg folder failed");
            [ret setValue:[NSNumber numberWithInt:RESULT_ERROR] forKey:@"result"];
            return [RAUtils dict2data:ret];
        }
        
        //        if(bsuccess)
        //        {
        //            sqlite3 *db = [self get_db];
        //
        //            [self execSql:[NSString stringWithFormat:@"insert into img_cache(name) values('%@')",name] db:db];
        //            sqlite3_close(db);
        //        }
    }
    
    
    //    NSData *imagedata=UIImagePNGRepresentation(self.imgview.image);
    
    //JEPG格式
    
    //NSData *imagedata=UIImageJEPGRepresentation(m_imgFore,1.0);
    
    NSString* filename =[NSString stringWithFormat:@"%@.png",[[NSUUID UUID] UUIDString]];
    NSString *savedImagePath=[offline_createimg stringByAppendingPathComponent:filename];
    
    NSString *cacheImagePath=[img_cache stringByAppendingPathComponent:filename];
    
    bool bsuccess=[image writeToFile:savedImagePath atomically:YES];
    if(bsuccess)
    {
        NSError *error = nil;
        if(![fileManager copyItemAtPath:savedImagePath toPath:cacheImagePath error:&error])
        {
            [ret setValue:[NSNumber numberWithInt:RESULT_ERROR] forKey:@"result"];
        }
        else
        {
            [ret setValue:[NSNumber numberWithInt:RESULT_TRUE] forKey:@"result"];
            
            ret[@"img_url_aname"]=filename;
            ret[@"img_url"]=savedImagePath;
        }
    }
    else
    {
        [ret setValue:[NSNumber numberWithInt:RESULT_ERROR] forKey:@"result"];
    }
    
    
    return [RAUtils dict2data:ret];
}

+(NSDictionary*) offline_contactlist :(NSMutableDictionary *) params
{
    
    
    
    
    
    NSString* contactType = [params valueForKey:@"contactType"];
    
    NSString* keyword = [params valueForKey:@"keyword"];
    
    
    // advanced search
    
    //    NSString* contact_name = [params valueForKey:@"contact_name"];
    //    NSString* customer_phone = [params valueForKey:@"customer_phone"];
    //    NSString* customer_fax = [params valueForKey:@"customer_fax"];
    //    NSString* customer_zipcode = [params valueForKey:@"customer_zipcode"];
    //    NSString* sales_rep = [params valueForKey:@"sales_rep"];
    //    NSString* customer_state = [params valueForKey:@"customer_state"];
    //    NSString* customer_name = [params valueForKey:@"customer_name"];
    //    NSString* customer_country = [params valueForKey:@"customer_country"];
    //    NSString* customer_cid = [params valueForKey:@"customer_cid"];
    //    NSString* customer_city = [params valueForKey:@"customer_city"];
    //    NSString* customer_address = [params valueForKey:@"customer_address"];
    //    NSString* customer_email = [params valueForKey:@"customer_email"];
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int result= [iSalesDB AddExFunction:db];
    
    int count =0;
    
    
    
    NSString *sqlQuery = nil;
    
    if(keyword.length==0)
    {
        sqlQuery=[NSString stringWithFormat:@"select editable,company_name,country,addr,zipcode,state,city,contact_name,phone,contact_id,addr_1,addr_2,addr_3,addr_4,first_name,last_name,fax,email from offline_contact where %@=1 order by create_time limit %d offset %d",contactType , limit, offset];
        count=[iSalesDB get_recordcount:db table:@"offline_contact" where:[NSString stringWithFormat:@"%@='1",contactType]];
    }
    else
    {
        // NSString* encrypt_keyword= [AESCrypt fastencrypt:keyword];
        
        keyword = keyword.lowercaseString;
        
        
        sqlQuery=[NSString stringWithFormat:@"select editable,company_name,country,addr,zipcode,state,city,contact_name,phone,contact_id,addr_1,addr_2,addr_3,addr_4,first_name,last_name,fax,email from offline_contact where %@=1 and (lower(contact_id ) like'%%%@%%' or lower(decrypt(company_name)) like '%%%@%%') order by create_time limit %d offset %d",contactType , keyword,keyword,limit, offset];
        
        count=[iSalesDB get_recordcount:db table:@"offline_contact" where:[NSString stringWithFormat:@"%@=1 and (lower(contact_id ) like'%@' or lower(company_name) like'%@')",contactType,keyword,keyword]];
    }
    
    
    DebugLog(@"offline_contactlist sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    
    
    [ret setValue:@"2" forKey:@"result"];
    [ret setValue:[NSNumber numberWithInt:count ]  forKey:@"total"];
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i = 0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            [ret setValue:[NSNumber numberWithInt:AP_USER_AUTH ] forKey:@"result"];
            
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            
            //            int editable  = sqlite3_column_int(statement, 0);
            
            
            char *company_name = (char*)sqlite3_column_text(statement, 1);
            NSString *nscompany_name =nil;
            if(company_name==nil)
                nscompany_name=@"";
            else
                nscompany_name= [[NSString alloc]initWithUTF8String:company_name] ;
            
            
            char *country = (char*)sqlite3_column_text(statement, 2);
            if(country==nil)
                country="";
            NSString *nscountry= [[NSString alloc]initWithUTF8String:country];
            
            
            //            char *addr = (char*)sqlite3_column_text(statement, 3);
            //            if(addr==nil)
            //                addr="";
            //            NSString *nsaddr= [[NSString alloc]initWithUTF8String:addr];
            
            
            char *zipcode = (char*)sqlite3_column_text(statement, 4);
            if(zipcode==nil)
                zipcode="";
            NSString *nszipcode= [[NSString alloc]initWithUTF8String:zipcode];
            
            
            char *state = (char*)sqlite3_column_text(statement, 5);
            if(state==nil)
                state="";
            NSString *nsstate= [[NSString alloc]initWithUTF8String:state];
            
            char *city = (char*)sqlite3_column_text(statement, 6);
            if(city==nil)
                city="";
            NSString *nscity= [[NSString alloc]initWithUTF8String:city];
            
            //            char *contact_name = (char*)sqlite3_column_text(statement, 7);
            //            NSString *nscontact_name = nil;
            //            if(contact_name==nil)
            //                nscontact_name=@"";
            //            else
            //                nscontact_name= [[NSString alloc]initWithUTF8String:contact_name];
            
            char *phone = (char*)sqlite3_column_text(statement, 8);
            NSString *nsphone = nil;
            if(phone==nil)
                nsphone=@"";
            else
                nsphone= [[NSString alloc]initWithUTF8String:phone];
            
            
            char *contact_id = (char*)sqlite3_column_text(statement, 9);
            if(contact_id==nil)
                contact_id="";
            NSString *nscontact_id= [[NSString alloc]initWithUTF8String:contact_id];
            
            char *addr_1 = (char*)sqlite3_column_text(statement, 10);
            if(addr_1==nil)
                addr_1="";
            NSString *nsaddr_1= [[NSString alloc]initWithUTF8String:addr_1];
            
            char *addr_2 = (char*)sqlite3_column_text(statement, 11);
            if(addr_2==nil)
                addr_2="";
            NSString *nsaddr_2= [[NSString alloc]initWithUTF8String:addr_2];
            
            
            char *addr_3 = (char*)sqlite3_column_text(statement, 12);
            if(addr_3==nil)
                addr_3="";
            NSString *nsaddr_3= [[NSString alloc]initWithUTF8String:addr_3];
            
            
            char *addr_4 = (char*)sqlite3_column_text(statement, 13);
            if(addr_4==nil)
                addr_4="";
            NSString *nsaddr_4= [[NSString alloc]initWithUTF8String:addr_4];
            
            
            char *first_name = (char*)sqlite3_column_text(statement, 14);
            if(first_name==nil)
                first_name="";
            NSString *nsfirst_name= [[NSString alloc]initWithUTF8String:first_name];
            
            
            char *last_name = (char*)sqlite3_column_text(statement, 15);
            if(last_name==nil)
                last_name="";
            NSString *nslast_name= [[NSString alloc]initWithUTF8String:last_name];
            
            char *fax = (char*)sqlite3_column_text(statement, 16);
            NSString *nsfax = nil;
            if(fax==nil)
                nsfax=@"";
            else
                nsfax= [NSString stringWithFormat:@"FAX:%@",[[NSString alloc]initWithUTF8String:fax]];
            
            char *email = (char*)sqlite3_column_text(statement, 17);
            NSString *nsemail = nil;
            if(email==nil)
                nsemail=@"";
            else
                nsemail= [NSString stringWithFormat:@"EMAIL:%@",[[NSString alloc]initWithUTF8String:email]];
            
            
            
            NSMutableArray* arr_name= [[NSMutableArray alloc] init];
            
            [arr_name addObject:nsfirst_name];
            [arr_name addObject:nslast_name];
            
            NSString *nscontact_name=[RAUtils arr2string:arr_name separator:@" " trim:true];
            
            NSMutableArray* arr_addr = [[NSMutableArray alloc] init];
            
            {
                // decrypt
                
                nscompany_name=[AESCrypt fastdecrypt:nscompany_name];
                
                nscompany_name=[NSString stringWithFormat:@"<b>%@</b>",nscompany_name];
                
                nsphone=[AESCrypt fastdecrypt:nsphone];
                nsphone=[NSString stringWithFormat:@"TEL:%@",nsphone];
                
                nsaddr_1=[AESCrypt fastdecrypt:nsaddr_1];
                //                nsaddr_1=[NSString stringWithFormat:@"TEL:%@",nsphone];
                
            }
            
            
            [arr_addr addObject:nscompany_name];
            [arr_addr addObject:nscontact_name];
            [arr_addr addObject:@"<br>"];
            
            [arr_addr addObject:nsaddr_1];
            [arr_addr addObject:nsaddr_2];
            [arr_addr addObject:nsaddr_3];
            [arr_addr addObject:nsaddr_4];
            //[arr_addr addObject:nsaddr];
            [arr_addr addObject:nszipcode];
            [arr_addr addObject:nscity];
            [arr_addr addObject:nsstate];
            [arr_addr addObject:nscountry];
            [arr_addr addObject:@"<br>"];
            [arr_addr addObject:nsphone];
            [arr_addr addObject:nsfax];
            [arr_addr addObject:nsemail];
            
            NSString * name = [RAUtils arr2string:arr_addr separator:@", " trim:true];
            
            name=[name stringByReplacingOccurrencesOfString:@", <br>," withString:@"<br>"];
            
            
            [item setValue:name   forKey:@"name"];
            [item setValue:nscontact_id   forKey:@"contact_id"];
            
            [ret setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
            
            
        }
        UIApplication * app = [UIApplication sharedApplication];
        AppDelegate *appDelegate = (AppDelegate *)[app delegate];
        [ret setValue:appDelegate.mode   forKey:@"mode"];
        [ret setValue:[NSNumber numberWithInt:i ]    forKey:@"count"];
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSDictionary*) model_NIYMAL:(NSString*) category
{
    // MODEL DETAIL NEW ITEM YOU MAY ALSO LIKE
    
    
    category = [category substringToIndex:3];
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    NSMutableDictionary* params = [[NSMutableDictionary alloc] init];
    
    params[@"category"]= category;
    
    ret[@"params"]= params;
    [ret setValue:@"detail" forKey:@"target"];
    [ret setValue:@"popup" forKey:@"action"];
    [ret setValue:@"content" forKey:@"type"];
    [ret setValue:@"New Items You May Also Like" forKey:@"title"];
    [ret setValue:@"model_NIYMAL" forKey:@"data_interface"];
    [ret setValue:@"true" forKey:@"single_row"];
    [ret setValue:@"true" forKey:@"partial_refresh"];
    
    sqlite3 *db = [iSalesDB get_db];
    
    //  int count = [iSalesDB get_recordcount:db table:@"model" where:[NSString stringWithFormat:@"category like'#%%%@%%#'",category]];
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select m.name,m.product_id,i.url,random() as aa from model m LEFT join model_image i on m.product_id = i.product_id where m.category like'%%#%@#%%' and m.category like'%%#005#%%' and i.default_img = %d order by aa limit 10 ;",category,OFFLINE_IMG_TYPE_CATEGORY];
    sqlite3_stmt * statement;
    int count = 0;
    [ret setValue:[NSString stringWithFormat:@"%d",count] forKey:@"count"];
    
    //    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *name = (char*)sqlite3_column_text(statement, 0);
            NSString *nsname = [[NSString alloc]initWithUTF8String:name];
            
            
            
            
            int product_id = sqlite3_column_int(statement, 1);
            
            
            char *url = (char*)sqlite3_column_text(statement, 2);
            if(url==nil)
                url="";
            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            [item setValue:nsurl forKey:@"picture_path"];
            [item setValue:nsname forKey:@"fash_name"];
            [item setValue:[NSString stringWithFormat:@"%d",product_id] forKey:@"product_id"];
            [item setValue:category forKey:@"category"];
            [ret setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
        }
        [ret setValue:[NSString stringWithFormat:@"%d",i] forKey:@"count"];
        
        
        sqlite3_finalize(statement);
    }
    NSLog(@"count:%d",count);
    
    
    sqlite3_close(db);
    
    
    return ret;
    
    
}
+(NSDictionary*) model_kvItem:(NSString*) key value:(NSString*)value
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    [ret setValue:key forKey:@"key"];
    [ret setValue:value forKey:@"val"];
    return ret;
}
+(NSDictionary*) model_property :(int)product_id field:(NSString*) field
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    [ret setValue:@"0" forKey:@"img_count"];
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select %@,property_display from model where product_id=%d ;",field,product_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *value = (char*)sqlite3_column_text(statement, 0);
            if(value==nil)
                value="";
            NSString *nsvalue = [[NSString alloc]initWithUTF8String:value];
            
            char *key = (char*)sqlite3_column_text(statement, 1);
            if(key==nil)
                key="";
            NSString *nskey = [[NSString alloc]initWithUTF8String:key];
            
            [item setValue:nsvalue forKey:@"val"];
            [item setValue:nskey forKey:@"key"];
            
            
            [ret setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            [ret setValue:@"1" forKey:@"count"];
            i++;
            
        }
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    return ret;
}
+(NSDictionary*) model_selector :(NSString*)product_group field:(NSString*) field
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    [ret setValue:@"0" forKey:@"count"];
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select %@,selector_display,product_id,category from model where product_group='%@'  order by name;",field,product_group];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *value = (char*)sqlite3_column_text(statement, 0);
            if(value==nil)
                value="";
            NSString *nsvalue = [[NSString alloc]initWithUTF8String:value];
            
            char *selector_display = (char*)sqlite3_column_text(statement, 1);
            if(selector_display==nil)
                selector_display="";
            NSString *nsselector_display = [[NSString alloc]initWithUTF8String:selector_display];
            
            
            int product_id = sqlite3_column_int(statement, 2);
            
            
            
            
            char *category = (char*)sqlite3_column_text(statement, 3);
            if(category==nil)
                category="";
            NSString *nscategory = [[NSString alloc]initWithUTF8String:category];
            
            NSString* url = [self model_category_img:[NSNumber numberWithInt:product_id].stringValue model_name:nil];//[self category_img:product_id];
            
            [item setValue:nsvalue forKey:@"title"];
            [item setValue:url forKey:@"pic_url"];
            
            NSMutableDictionary* params=[[NSMutableDictionary alloc] init];
            [params setValue:@"2" forKey:@"count"];
            NSMutableDictionary* param0=[[NSMutableDictionary alloc] init];
            [param0 setValue:[NSNumber numberWithInt:product_id ] forKey:@"val"];
            [param0 setValue:@"product_id" forKey:@"name"];
            
            
            NSMutableDictionary* param1=[[NSMutableDictionary alloc] init];
            [param1 setValue:nscategory forKey:@"val"];
            [param1 setValue:@"category" forKey:@"name"];
            
            [params setObject:param0 forKey:@"param_0"];
            [params setObject:param1 forKey:@"param_1"];
            
            
            [item setObject:params forKey:@"params"];
            
            
            
            [ret setValue:nsselector_display forKey:@"name"];
            
            [ret setObject:item forKey:[NSString stringWithFormat:@"item_%d",i]];
            i++;
            
        }
        [ret setValue:[NSString stringWithFormat:@"%d",i] forKey:@"count"];
        [ret setValue:@"switch" forKey:@"action"];
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    return ret;
}
+(NSMutableDictionary*) category_img :(int)product_id
{
    // model 在 category search 显示的图片。
    NSMutableDictionary* ret =[[NSMutableDictionary alloc]init];
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select url,type from model_image where product_id=%d and type<2 order by default_img desc , _id asc limit 2;",product_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            char *url = (char*)sqlite3_column_text(statement, 0);
            if(url==nil)
                url="";
            int type = sqlite3_column_int(statement, 1);
            if(type==0)
                [ret setValue:[[NSString alloc]initWithUTF8String:url] forKey:@"img"];
            else
                [ret setValue:[[NSString alloc]initWithUTF8String:url] forKey:@"img_big"];
            
        }
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    return ret;
}
+(NSDictionary*) model_img :(int)product_id
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    [ret setValue:@"0" forKey:@"img_count"];
    
    sqlite3 *db = [iSalesDB get_db];
    
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select url from model_image where product_id=%d and type=1;",product_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        int i=0;
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSMutableDictionary* item = [[NSMutableDictionary alloc] init];
            
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            char *url = (char*)sqlite3_column_text(statement, 0);
            if(url==nil)
                url="";
            NSString *nsurl = [[NSString alloc]initWithUTF8String:url];
            
            
            
            [item setValue:nsurl forKey:@"s"];
            [item setValue:nsurl forKey:@"l"];
            
            
            [ret setObject:item forKey:[NSString stringWithFormat:@"img_%d",i]];
            i++;
            
        }
        [ret setValue:[NSString stringWithFormat:@"%d",i] forKey:@"img_count"];
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    sqlite3_close(db);
    
    return ret;
}

#pragma mark - Jack

#pragma mark create new contact
+ (NSDictionary *)offline_getAllCountryDefault:(NSString *)countryCode {
    //    "val_227" : {
    //        "check" : 1,
    //        "value" : "US United States",
    //        "value_id" : "228"
    //    },
    
    if (!countryCode) {
        countryCode = @"US";
    }
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    sqlite3 *db = [iSalesDB get_db];
    
    const char *sqlQuery = "select _id,name,code,countrycode_id from offline_country;";
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, sqlQuery, -1, &statement, nil) == SQLITE_OK) {
        
        long count = 0;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char *) sqlite3_column_text(statement, 1); // 全称
            char *code = (char *) sqlite3_column_text(statement, 2); // 缩写
            int code_id = sqlite3_column_int(statement, 3); // id
            
            NSMutableDictionary *countryDic = [NSMutableDictionary dictionaryWithCapacity:2];
            [countryDic setValue:[NSString stringWithFormat:@"%s",name] forKey:@"value"];
            [countryDic setValue:[NSString stringWithFormat:@"%d",code_id] forKey:@"value_id"];
            [countryDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
            
            if ([countryCode isEqualToString:[NSString stringWithUTF8String:code]]) {
                [countryDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
            }
            
            NSString *key = [NSString stringWithFormat:@"val_%ld",count++];
            [dic setValue:countryDic forKey:key];
        }
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    [dic setValue:[NSNumber numberWithInt:dic.allKeys.count] forKey:@"count"];
    return dic;
    
//    NSDictionary *ret = [iSalesDB jk_query:@"select _id,name,code,countrycode_id from offline_country;" completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container,long *count) {
//        char *name = (char *) sqlite3_column_text(stmt, 1); // 全称
//        char *code = (char *) sqlite3_column_text(stmt, 2); // 缩写
//        int code_id = sqlite3_column_int(stmt, 3); // id
//
//        NSMutableDictionary *countryDic = [NSMutableDictionary dictionaryWithCapacity:2];
//        [countryDic setValue:[NSString stringWithFormat:@"%s",name] forKey:@"value"];
//        [countryDic setValue:[NSString stringWithFormat:@"%d",code_id] forKey:@"value_id"];
//        [countryDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
//
//        if ([countryCode isEqualToString:[NSString stringWithUTF8String:code]]) {
//            [countryDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
//        }
//    
//        long n = *count;
//        *count = n + 1;
//        NSString *key = [NSString stringWithFormat:@"val_%ld",n];
//        [container setValue:countryDic forKey:key];
//    }];
//    
//    return [ret copy];
}

+ (NSDictionary *)offline_getStateByCountryCode:(NSString *)countryCode checkedState:(NSString *)state_code{
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id,name,code,country_code from offline_state where country_code = '%@';",countryCode];
    
    //    NSString *sqlQuery = [NSString stringWithFormat:@"select * from offline_state"];
    DebugLog(@"query state:%@",sqlQuery);
    
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        int i = 0;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char *) sqlite3_column_text(statement, 1); // value
            char *code = (char *) sqlite3_column_text(statement, 2); // value_id
            
            NSMutableDictionary *stateDic = [NSMutableDictionary dictionaryWithCapacity:2];
            [stateDic setValue:[NSString stringWithUTF8String:name] forKey:@"value"];
            [stateDic setValue:[NSString stringWithUTF8String:code] forKey:@"value_id"];
            [stateDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
            
            if (state_code && [[NSString stringWithUTF8String:code] isEqualToString:state_code]) {
                [stateDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
            }
            
            NSString *key = [NSString stringWithFormat:@"val_%d",i++];
            [dic setValue:stateDic forKey:key];
        }
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    [dic setValue:[NSNumber numberWithInt:dic.allKeys.count] forKey:@"count"];
    return dic;
}

+ (NSDictionary *)offline_getStateByCountryCodeId:(NSString *)codeId checkedState:(NSString *)stateCode {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select name,code from offline_state where country_code = (select code as country_code FROM offline_country where countrycode_id = %@);",codeId];
    
    DebugLog(@"query state:%@",sqlQuery);
    
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        int i = 0;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *name = (char *) sqlite3_column_text(statement, 0); // value
            char *code = (char *) sqlite3_column_text(statement, 1); // value_id
            
            NSMutableDictionary *stateDic = [NSMutableDictionary dictionaryWithCapacity:2];
            [stateDic setValue:[NSString stringWithUTF8String:name] forKey:@"value"];
            [stateDic setValue:[NSString stringWithUTF8String:code] forKey:@"value_id"];
            [stateDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
            
            if (stateCode && [[NSString stringWithUTF8String:code] isEqualToString:stateCode]) {
                [stateDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
            }
            
            NSString *key = [NSString stringWithFormat:@"val_%d",i++];
            [dic setValue:stateDic forKey:key];
        }
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    [dic setValue:[NSNumber numberWithInt:dic.allKeys.count] forKey:@"count"];
    return dic;
    
}

+ (NSDictionary *)offline_getPrice {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id,name,type,order_by from price;"];
    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);
            int type = sqlite3_column_int(statement, 2);
            int orderBy = sqlite3_column_int(statement, 3);
            
            NSMutableDictionary *priceDic = [NSMutableDictionary dictionary];
            [priceDic setValue:[NSString stringWithUTF8String:name] forKey:@"value"];
            [priceDic setValue:[NSNumber numberWithInt:type] forKey:@"value_id"];
            NSString *key = [NSString stringWithFormat:@"val_%d",orderBy];
            [priceDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
            
            if (orderBy == 0) {
                [priceDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
            }
            
            [dic setValue:priceDic forKey:key];
        }
        
        sqlite3_finalize(statement);
    }
    
    sqlite3_close(db);
    
    [dic setValue:[NSNumber numberWithInt:dic.allKeys.count] forKey:@"count"];
    
    return dic;
}

+ (NSDictionary *)offline_getSalesRep {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id,name,code,salesrep_id from offline_salesrep;"];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        long count = 0;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            // 1 name 2 code 3 salesrep_id
            char *name = (char *)sqlite3_column_text(statement, 1);
            char *code = (char *)sqlite3_column_text(statement, 2);
            int salesrep_id = sqlite3_column_int(statement, 3);
            //            int _id = sqlite3_column_int(statement, 0);
            //
            //            "val_0" : {
            //                "value" : "BBSC - BARBARA BORROUGHS",
            //                "value_id" : 121
            //            },
            NSMutableDictionary *repDic = [NSMutableDictionary dictionary];
            [repDic setValue:[NSString stringWithFormat:@"%s - %s",code,name] forKey:@"value"];
            [repDic setValue:[NSNumber numberWithInt:salesrep_id] forKey:@"value_id"];
            [dic setValue:repDic forKey:[NSString stringWithFormat:@"val_%ld",count++]];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    [dic setValue:[NSNumber numberWithInt:dic.allKeys.count] forKey:@"count"];
    
    return dic;
}

+ (void)setValue:(id)value forItemKey:(NSString *)itemKey valueKey:(NSString *)valueKey inDictionary:(NSMutableDictionary *)dic{
    
    NSMutableDictionary *item = [[dic objectForKey:itemKey] mutableCopy];
    [item setValue:value forKey:valueKey];
    
    [dic setValue:item forKey:itemKey];
}

+ (NSString *)countryCodeByid:(NSString *)code_id {
    
    NSString *ret = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select code from offline_country where countrycode_id = %@",code_id];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *code = (char *)sqlite3_column_text(statement, 0);
            ret = [NSString stringWithUTF8String:code];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    return ret;
}

+ (NSString *)countryCodeIdByCode:(NSString *)code {
    NSString *ret = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select countrycode_id from offline_country where code = '%@';",code];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *_id = (char *)sqlite3_column_text(statement, 0);
            ret = [NSString stringWithFormat:@"%s",_id];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    return ret;
}

+ (NSDictionary *)offline_dealZipCode:(NSString *)zipcode {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select country,state,city,country_code,state_code from offline_zipcode where zipcode = '%@';",zipcode];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *country = (char *)sqlite3_column_text(statement, 0);
            char *state = (char *)sqlite3_column_text(statement, 1);
            char *city = (char *)sqlite3_column_text(statement, 2);
            char *country_code = (char *)sqlite3_column_text(statement, 3);
            char *state_code = (char *)sqlite3_column_text(statement, 4);
            
            [dic setValue:[NSString stringWithUTF8String:country] forKey:@"country"];
            [dic setValue:[NSString stringWithUTF8String:state] forKey:@"state"];
            [dic setValue:[NSString stringWithUTF8String:city] forKey:@"city"];
            [dic setValue:[NSString stringWithUTF8String:country_code] forKey:@"country_code"];
            [dic setValue:[NSString stringWithUTF8String:state_code] forKey:@"state_code"];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    
    return dic;
}
+(NSData *) offline_saveContact:(NSMutableDictionary *) params
{
    return nil;
}

+ (NSData *)offline_createContact:(NSMutableDictionary *)params {
    
    NSString *path = [[NSBundle mainBundle] pathForResource:@"createContact.json" ofType:nil];
    NSData *data = [NSData dataWithContentsOfFile:path];
    NSMutableDictionary *ret = [[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingMutableContainers error:nil] mutableCopy];
    
    NSMutableDictionary *section_0 = [[ret objectForKey:@"section_0"] mutableCopy];
    
    
    
    NSString *countryCode = nil;
    NSString *countryCode_id = nil;
    NSString *stateCode = nil;
    NSString *city = nil;
    NSString *zipCode = nil;
    
    if ([params.allKeys containsObject:@"refresh_trigger"]) {
        
        if([params[@"refresh_trigger"] isEqualToString:@"country"]) { // choose country
            
            NSString *code_id = params[@"country"];
            countryCode_id = code_id;
            countryCode = [self countryCodeByid:code_id];
            
        } else if ([params[@"refresh_trigger"] isEqualToString:@"zipcode"]) { // zipcode
            
            NSString *zip_code = params[@"zipcode"];
            zipCode = zip_code;
            
            countryCode_id = params[@"country"];
            
            NSDictionary *dic = [self offline_dealZipCode:zip_code];
            
            countryCode = [dic valueForKey:@"country_code"];
            
            stateCode = [dic valueForKey:@"state_code"];
            
            city = [dic valueForKey:@"city"];
            
            // zip code
            NSMutableDictionary *zipDic = [[section_0 valueForKey:@"item_10"] mutableCopy];
            [zipDic setValue:zipCode forKey:@"value"];
            [section_0 setValue:zipDic forKey:@"item_10"];
        }
        
    } else {
        // default: US United States
        countryCode = @"US";
        countryCode_id = @"228";
    }
    
    // country
    NSDictionary *allCountry = [self offline_getAllCountryDefault:countryCode];
    [self setValue:allCountry forItemKey:@"item_5" valueKey:@"cadedate" inDictionary:section_0];
    
    // state
    NSDictionary *allState = [self offline_getStateByCountryCode:countryCode checkedState:stateCode];
    //     NSDictionary *allState = [self offline_getStateByCountryCodeId:countryCode_id checkedState:stateCode];
    [self setValue:allState forItemKey:@"item_11" valueKey:@"cadedate" inDictionary:section_0];
    
    // city
    if (city) {
        NSMutableDictionary *cityDic = [[section_0 valueForKey:@"item_12"] mutableCopy];
        [cityDic setValue:city forKey:@"value"];
        
        [section_0 setValue:cityDic forKey:@"item_12"];
    }
    
    // price type
    NSDictionary *priceDic = [self offline_getPrice];
    [self setValue:priceDic forItemKey:@"item_2" valueKey:@"cadedate" inDictionary:section_0];
    
    // Sales Rep
    NSDictionary *repDic = [self offline_getSalesRep];
    [self setValue:repDic forItemKey:@"item_17" valueKey:@"cadedate" inDictionary:section_0];
    
    [ret setValue:section_0 forKey:@"section_0"];
    
    return [RAUtils dict2data:ret];
}

#pragma mark save new contact

+ (NSString *)countryNameByCountryCodeId:(NSString *)codeId {
    
    NSString *name = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select name from offline_country where countrycode_id = %@",codeId];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *value = (char *)sqlite3_column_text(statement, 0);
            name = [NSString stringWithUTF8String:value];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);
    
    return name;
}

+ (NSString *)priceNameByPriceId:(NSString *)priceId {
    
    NSString *ret = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select name from price where order_by = %@;",priceId];
    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, 0);
            ret = [NSString stringWithUTF8String:name];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);

    return ret;
}

+ (NSString *)salesRepCodeById:(NSString *)_id {
    
    NSString *ret = nil;
    
    sqlite3 *db = [iSalesDB get_db];
    NSString *sqlQuery = [NSString stringWithFormat:@"select code from offline_salesrep where salesrep_id = %@",_id];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *rep = (char *)sqlite3_column_text(statement, 0);
            ret = [NSString stringWithUTF8String:rep];
        }
        
        sqlite3_finalize(statement);
    }
    sqlite3_close(db);

    
    return ret;
}

+(NSData *) offline_saveNewContact:(NSMutableDictionary *) params
{
//    {
//
//        password = 123456;
//        "price_name" = 0;
//        "sales_rep" = 121;

//    }

    NSLog(@"%@",params);
    NSString *companyName = [params objectForKey:@"company"];
    if (companyName) {
        companyName = [AESCrypt fastencrypt:companyName];
    }
    
    NSString *addr1 = [params objectForKey:@"address"];
    if (addr1) {
        addr1 = [AESCrypt fastencrypt:addr1];
    }
    NSString *addr2 = [params objectForKey:@"address2"];
    if (addr2) {
        addr2 = [AESCrypt fastencrypt:addr2];
    }
    NSString *addr3 = [params objectForKey:@"address_3"];
    if (addr3) {
        addr3 = [AESCrypt fastencrypt:addr3];
    }
    NSString *addr4 = [params objectForKey:@"address_4"];
    if (addr4) {
        addr4 = [AESCrypt fastencrypt:addr4];
    }
    
    NSString *country = [params objectForKey:@"country"];
    if (country) {
        country = [self countryNameByCountryCodeId:country];
    }
    NSString *state = [params objectForKey:@"state"];
    NSString *city = [params objectForKey:@"city"];
    NSString *zipcode = [params objectForKey:@"zipcode"];
    
    NSString *fistName = [params objectForKey:@"firstname"];
    NSString *lastName = [params objectForKey:@"lastname"];
    
    NSString *phone = [params objectForKey:@"phone"];
    if (phone) {
        phone = [AESCrypt fastencrypt:phone];
    }
    
    NSString *fax = [params objectForKey:@"fax"];
    NSString *email = [params objectForKey:@"email"];
    
    NSString *notes = [params objectForKey:@"contact_notes"];
    
    NSString *price = [params objectForKey:@"price_name"];
    if (price) {
        price = [self priceNameByPriceId:price];
    }
    NSString *salesRep = [params objectForKey:@"sales_rep"];
    if (salesRep) {
        salesRep = [self salesRepCodeById:salesRep];
    }
    
    NSString *img = [params objectForKey:@"business_card"];
    NSArray *array = [img componentsSeparatedByString:@","];
    NSString *img_0 = array[0];
    NSString *img_1 = array[1];
    NSString *img_2 = array[2];
    
    NSString *contact_id = [NSUUID UUID].UUIDString;
    
    NSString *sql = [NSString stringWithFormat:@"insert into offline_contact (company_name,addr_1,addr_2,addr_3,addr_4,country,state,city,zipcode,first_name,last_name,phone,fax,email,notes,price_type,sales_rep,img_0,img_1,img_2,editable,contact_id) values ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@',1,'%@')",companyName,addr1,addr2,addr3,addr4,country,state,city,zipcode,fistName,lastName,phone,fax,email,notes,price,salesRep,img_0,img_1,img_2,contact_id];
    
    int result = [iSalesDB jk_execSql:sql withDatabase:YES];
    
    NSString *retStr = [NSString stringWithFormat:@"{\"result\":%d,\"min_ver\":\"160409\",\"mode\":\"Regular Mode\"}",result];
    
    return [retStr dataUsingEncoding:NSUTF8StringEncoding];
}


@end