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"


@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];
    [iSalesDB close_db:db];
    if(count==0)
    {
        return false;
    }
    return true;
    //
    //[iSalesDB close_db: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 db:db];
    int count=[iSalesDB get_recordcount:db table:@"wishlist" where:@"1=1"];
    [iSalesDB close_db: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 db:db];
        }
    }
    int count=[iSalesDB get_recordcount:db table:@"wishlist" where:@"1=1"];
    [iSalesDB close_db: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 db:db];
            
            //            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);
        
        
        
        
    }
    
    [iSalesDB close_db: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"];
    sqlite3* db= [iSalesDB get_db];
    NSMutableDictionary* detail1_section = [[self model_NIYMAL:category db:db] mutableCopy];
    
    [iSalesDB close_db:db];
    
    [ret setObject:detail1_section forKey:@"detail_1"];
    return ret;
}

+(NSMutableDictionary*) get_model_all_price:(NSString*) contact_id product_id:(int) product_id db:(sqlite3*)db
{
    
    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 db:db];
    
    //    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);
        
        
        
        
    }
    
   // [iSalesDB close_db:db];
    
    return ret;
}

+(NSNumber*) get_model_default_price:(NSString*) contact_id product_id:(int) product_id db:(sqlite3*)db
{
    NSArray* arr1 = [self get_user_all_price_type:db];
    NSArray* arr2 = [self get_contact_default_price_type:contact_id db:db];
    
    //    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);
        
        
        
        
    }
    
  //  [iSalesDB close_db:db];
    
    if(dprice==DBL_MAX)
        ret= nil;
    else
        ret= [NSNumber numberWithDouble:dprice];
    return ret;
}

+(NSArray*) get_user_all_price_type:(sqlite3*)db
{
    
    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);
        
        
        
        
    }
    
 //   [iSalesDB close_db:db];
    
    return ret;
    
    
    
}
+(NSArray*) get_contact_default_price_type:(NSString*) contact_id db:(sqlite3*)db
{
    
 //   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)
        {
    //        [iSalesDB close_db: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);
    }
    
   // [iSalesDB close_db:db];
    
    return ret;
    
    
}
+(NSString*) model_category_img :(NSString *) product_id model_name:(NSString *) model_name db:(sqlite3*)db
{
    
    
    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"];
    }
    
    
    
  //  [iSalesDB close_db: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"];
    }
    
    
    
    [iSalesDB close_db: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 db:db];
            
            [img_section setValue:model_s_img forKey:@"model_s_img"];
            [img_section setObject:[self model_img:product_id db:db] forKey:@"images"];
            [img_section setObject:[self model_property:product_id field:nsproperty_field db:db] forKey:@"property"];
            [img_section setObject:[self model_selector:nsproduct_group field:nsselector_field db:db] 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 db:db];
                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 db:db];
            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 db:db] 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);
    
    
    [iSalesDB close_db: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  db:db];
            
            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);
    
    
    [iSalesDB close_db: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);
    //    }
    //
    //
    //
    //    [iSalesDB close_db:db];
    //
    //
    //
    //
    //    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+(NSData*) offline_createorder :(NSMutableDictionary *) params
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    NSString* customer_cid = [self translateSingleQuote:params[@"customer_cid"]];
//    NSString* customer_address1 = [self translateSingleQuote:params[@"customer_address1"]];
//    NSString* customer_address2 = [self translateSingleQuote:params[@"customer_address2"]];
//    NSString* customer_address3 = [self translateSingleQuote:params[@"customer_address3"]];
//    NSString* customer_address4 = [self translateSingleQuote:params[@"customer_address4"]];
//    NSString* customer_city = [self translateSingleQuote:params[@"customer_city"]];
    NSString* customer_contact = [self translateSingleQuote:params[@"customer_contact"]];
//    NSString* customer_contact_ext = [self translateSingleQuote:params[@"customer_contact_ext"]];
//    NSString* customer_contact_notes = [self translateSingleQuote:params[@"customer_contact_notes"]];
//    NSString* customer_country = [self translateSingleQuote:params[@"customer_country"]];
    NSString* customer_email = [self translateSingleQuote:params[@"customer_email"]];
    NSString* customer_fax = [self translateSingleQuote:params[@"customer_fax"]];
    NSString* customer_first_name = [self translateSingleQuote:params[@"customer_first_name"]];
    NSString* customer_last_name = [self translateSingleQuote:params[@"customer_last_name"]];
//    NSString* customer_name = [self translateSingleQuote:params[@"customer_name"]];
    NSString* customer_phone = [self translateSingleQuote:params[@"customer_phone"]];
 //   NSString* customer_price_type = [self translateSingleQuote:params[@"customer_price_type"]];
    NSString* customer_sales_rep = [self translateSingleQuote:params[@"customer_sales_rep"]];
  //  NSString* customer_state = [self translateSingleQuote:params[@"customer_state"]];
 //   NSString* customer_zipcode = [self translateSingleQuote:params[@"customer_zipcode"]];
    NSString* create_by = [self translateSingleQuote:params[@"user"]];
    
    NSMutableArray* arr_name= [[NSMutableArray alloc] init];
    
    [arr_name addObject:customer_first_name];
    [arr_name addObject:customer_last_name];
    
    NSString *contact_name=[RAUtils arr2string:arr_name separator:@" " trim:true];
    
    
    NSString* sql_neworder=[NSString stringWithFormat:@"insert into offline_order(so_id,status,customer_cid,customer_contact,customer_email,customer_fax,customer_phone,sales_rep,create_by,customer_contact) values('OFFLINE'||hex(randomblob(16)),0,'%@','%@','%@','%@','%@','%@','%@','%@')",customer_cid,customer_contact,customer_email,customer_fax,customer_phone,customer_sales_rep,create_by ,contact_name];
    
    int result =[iSalesDB execSql:sql_neworder ];
    [ret setValue:[NSNumber numberWithInt:result] forKey:@"result"];
    
    //insert into offline_order(so_id,status,contact_id,sales_rep,create_by,customer_contact) select 'OFFLINE'||hex(randomblob(16)),-1,contact_id,sales_rep,company_name from offline_contact where contact_id='99FURO0002'
    
    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.length>0)
        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,decrypt(c.company_name),o.create_time,o.total_price from offline_order o left join offline_contact c on o.customer_cid=c.contact_id where %@ order by o.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);
        
        
        
        
    }
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
}
+(NSData*) offline_orderdetail :(NSMutableDictionary *) params
{
    
    int orderId = [params[@"orderId"] intValue];
        NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
//    [NSData dataWithContentsOfFile:[[NSBundle mainBundle]  pathForResource:@"debug_category_filter" ofType:@"json" ]];
    
//    NSString* content = [NSString stringWithContentsOfFile:thepath encoding:NSUTF8StringEncoding error:nil];
    
    NSString* sql=[NSString stringWithFormat:@"select o._id,o.so_id,o.poNumber,o.create_time,o.status,o.TotalCuft,o.TotalWeight,o.TotalCarton,decrypt(c.company_name),o.customer_contact,decrypt(c.addr_1),c.addr_2,c.addr_3,c.addr_4,o.logist,o.shipping,o.lift_gate_value,o.general_notes,o.internal_notes,o.paymentType,o.lift_gate from (select _id,so_id,poNumber,create_time,status,TotalCuft,TotalWeight,TotalCarton,customer_cid,customer_contact,logist,shipping,lift_gate_value,general_notes,internal_notes,paymentType,lift_gate from offline_order where _id=%d) o left join offline_contact c on o.customer_cid=c.contact_id",orderId ];
    sqlite3 *db = [iSalesDB get_db];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            
//            NSMutableDictionary* item=[[NSMutableDictionary alloc]init];
            
            int order_id = sqlite3_column_int(statement, 0);
            
            
            char *soid = (char*)sqlite3_column_text(statement, 1);
            if(soid==nil)
                soid= "";
            NSString *nssoid= [[NSString alloc]initWithUTF8String:soid];
            
            
            char *poNumber = (char*)sqlite3_column_text(statement, 2);
            if(poNumber==nil)
                poNumber= "";
            NSString *nspoNumber= [[NSString alloc]initWithUTF8String:poNumber];
            

            
            char *create_time = (char*)sqlite3_column_text(statement, 3);
            if(create_time==nil)
                create_time= "";
            NSString *nscreate_time= [[NSString alloc]initWithUTF8String:create_time];

            int status = sqlite3_column_int(statement, 4);
            NSString* nsstatus=@"";
            switch (status) {
                case 0:
                {
                    nsstatus=@"Temp Order";
                     break;
                }
                case 1:
                {
                    nsstatus=@"Saved Order";
                    break;
                }
                case 10:
                {
                    nsstatus=@"Quote Submitted";
                    break;
                }
                case 11:
                {
                    nsstatus=@"Sales Order Submitted";
                    break;
                }
                case 12:
                {
                    nsstatus=@"Processing";
                    break;
                }
                case 13:
                {
                    nsstatus=@"Shipped";
                    break;
                }
                case 14:
                {
                    nsstatus=@"Closed";
                    break;
                }
                case 15:
                {
                    nsstatus=@"Cancelled";
                    break;
                }
                    
                    
                default:
                    break;
            }
            
            double TotalCuft = sqlite3_column_double(statement, 5);
            double TotalWeight = sqlite3_column_double(statement, 6);
            double TotalCarton = sqlite3_column_double(statement, 7);
            
            
            char *company_name = (char*)sqlite3_column_text(statement, 8);
            if(company_name==nil)
                company_name= "";
            NSString *nscompany_name= [[NSString alloc]initWithUTF8String:company_name];
            
            
            char *customer_contact = (char*)sqlite3_column_text(statement, 9);
            if(customer_contact==nil)
                customer_contact= "";
            NSString *nscustomer_contact= [[NSString alloc]initWithUTF8String:customer_contact];
            
            
            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];

            NSMutableArray* arr_addr = [[NSMutableArray alloc] init];
            [arr_addr addObject:nsaddr_1];
            [arr_addr addObject:nsaddr_2];
            [arr_addr addObject:nsaddr_3];
            [arr_addr addObject:nsaddr_4];
            NSString * customer_address = [RAUtils arr2string:arr_addr separator:@"<br>" trim:true];
            

            char *logist = (char*)sqlite3_column_text(statement, 14);
            if(logist==nil)
                logist= "";
            NSString *nslogist=[[NSString alloc]initWithUTF8String:logist];
            
            double shipping = sqlite3_column_double(statement, 15);
            
            double lift_gate = sqlite3_column_double(statement, 16);
            
            char *general_notes = (char*)sqlite3_column_text(statement, 17);
            if(general_notes==nil)
                general_notes= "";
            NSString *nsgeneral_notes=[[NSString alloc]initWithUTF8String:general_notes];

            char *internal_notes = (char*)sqlite3_column_text(statement, 18);
            if(internal_notes==nil)
                internal_notes= "";
            NSString *nsinternal_notes=[[NSString alloc]initWithUTF8String:internal_notes];

            char *payment_type = (char*)sqlite3_column_text(statement, 19);
            if(payment_type==nil)
                payment_type= "";
            NSString *nspayment_type=[[NSString alloc]initWithUTF8String:payment_type];
            
            int have_lift_gate = sqlite3_column_int(statement, 20);
            
            NSError *error = nil;
            NSString* orderinfo = [NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:@"orderinfo" ofType:@"template"] encoding:NSUTF8StringEncoding error:&error];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"SO_PLACEHOLDER" withString:nssoid];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"PO_PLACEHOLDER" withString:nspoNumber];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"ORDERDATE_PLACEHOLDER" withString:nscreate_time];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"ORDERSTATUS_PLACEHOLDER" withString:nsstatus];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"CUFT_PLACEHOLDER" withString:[NSString stringWithFormat:@"%.2f",TotalCuft]];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"WEIGHT_PLACEHOLDER" withString:[NSString stringWithFormat:@"%.2f",TotalWeight]];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"CARTON_PLACEHOLDER" withString:[NSString stringWithFormat:@"%.2f",TotalCarton]];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"COMPANY_PLACEHOLDER" withString:nscompany_name];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"CONTACT_PLACEHOLDER" withString:nscustomer_contact];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"ADDRESS_PLACEHOLDER" withString:customer_address];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"SHIPPINGMETHOD_PLACEHOLDER" withString:nslogist];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"SHIPPING_PLACEHOLDER" withString:[NSString stringWithFormat:@"%.2f",shipping]];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"LIFTGATE_PLACEHOLDER" withString:[NSString stringWithFormat:@"%.2f",lift_gate]];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"GENERALNOTES_PLACEHOLDER" withString:nsgeneral_notes];
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"INTERNALNOTES_PLACEHOLDER" withString:nsinternal_notes];
            
            NSString *payment = nil;
            if([nspayment_type isEqualToString:@"Credit Card"])
            {
                payment=[NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:@"creditcardpayment" ofType:@"template"] encoding:NSUTF8StringEncoding error:&error];
                //payment=[payment stringByReplacingOccurrencesOfString:@"PAYMENTTYPE_PLACEHOLDER" withString:nspayment_type];
            }
            else
            {
                payment=[NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:@"normalpayment" ofType:@"template"] encoding:NSUTF8StringEncoding error:&error];
                payment=[payment stringByReplacingOccurrencesOfString:@"PAYMENTTYPE_PLACEHOLDER" withString:nspayment_type];
            }
            orderinfo=[orderinfo stringByReplacingOccurrencesOfString:@"PAYMENT_PLACEHOLDER" withString:payment];
            ret[@"order_info"]= orderinfo;
            ret[@"result"]= [NSNumber numberWithInt:2];
            
            ret[@"result"]= [NSNumber numberWithInt:2];
        }
        
 
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    [iSalesDB close_db: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,can_update_contact_info 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];
            
            int can_update_contact_info = sqlite3_column_int(statement, 13);
            
            
            [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:[NSNumber numberWithBool:can_update_contact_info]   forKey:@"can_update_contact_info"];
            
            [header setValue:nsusername   forKey:@"username"];
            
            
            [ret setObject:header forKey:@"header"];
            [ret setValue:nsmode   forKey:@"mode"];
            
            
        }
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    [iSalesDB close_db: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];
            
            [self copy_bcardImg:nsimg_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];
            [self copy_bcardImg:nsimg_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];
             [self copy_bcardImg:nsimg_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);
    }
    
    
    
    [iSalesDB close_db:db];
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}
+ (bool) copy_bcardImg:(NSString*) filename
{
    if(filename.length==0)
        return false;
   // NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    bool ret=false;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *cachefolder = [paths objectAtIndex:0];
    
//    NSString* from_subpath= [fromPath stringByReplacingOccurrencesOfString:cachefolder withString:@""];
    NSString *offline_createimg = [cachefolder stringByAppendingPathComponent:@"offline_createimg"];
    NSString *img_cache = [cachefolder stringByAppendingPathComponent:@"img_cache"];
    
    NSString * fromPath= [offline_createimg stringByAppendingPathComponent:filename];
    NSString * toPath= [img_cache stringByAppendingPathComponent:filename];
    
//    NSString *savedImagePath=[offline_createimg stringByAppendingPathComponent:filename];
//    
//    NSString *cacheImagePath=[img_cache stringByAppendingPathComponent:filename];
    
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL bdir=NO;
    if(!  [fileManager fileExistsAtPath:toPath isDirectory:&bdir])
    {
        
        NSError *error = nil;
        if(![fileManager copyItemAtPath:fromPath toPath:toPath error:&error])
        {
            ret=false;
        }
        else
        {
            ret=true;
        }
        
//        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];
        //            [iSalesDB close_db:db];
        //        }
    }
    return ret;
    

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

+(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];
        //            [iSalesDB close_db: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"]=filename;
        }
    }
    else
    {
        [ret setValue:[NSNumber numberWithInt:RESULT_ERROR] forKey:@"result"];
    }
    
    
    return [RAUtils dict2data:ret];
}

+(NSDictionary*) model_NIYMAL:(NSString*) category db:(sqlite3 *)db
{
    // 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);
    
    
  //  [iSalesDB close_db: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 db:(sqlite3 *)db
{
    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);
    }
    
    
    
 //   [iSalesDB close_db:db];
    
    return ret;
}
+(NSDictionary*) model_selector :(NSString*)product_group field:(NSString*) field db:(sqlite3*)db
{
    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 db:db];//[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);
    }
    
    
    
  //  [iSalesDB close_db:db];
    
    return ret;
}
+(NSMutableDictionary*) category_img :(int)product_id db:(sqlite3 *)db
{
    // 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);
    }
    
    
    
   // [iSalesDB close_db:db];
    
    return ret;
}
+(NSDictionary*) model_img :(int)product_id db:(sqlite3*)db
{
    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);
    }
    
    
    
 //   [iSalesDB close_db:db];
    
    return ret;
}

#pragma mark - Jack

+ (NSDictionary *)offline_getAllCountryDefault:(NSString *)countryCode {
    //    "val_227" : {
    //        "check" : 1,
    //        "value" : "US United States",
    //        "value_id" : "228"
    //    },
    
    if (!countryCode) {
        countryCode = @"US";
    }
    
    NSMutableDictionary *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];
    }] mutableCopy];
    
    [ret setValue:[NSNumber numberWithInt:ret.allKeys.count] forKey:@"count"];
    return ret;
}

+ (NSDictionary *)offline_getStateByCountryCode:(NSString *)countryCode checkedState:(NSString *)state_code{
    countryCode = [self translateSingleQuote:countryCode];
    NSString *sqlQuery = [NSString stringWithFormat:@"select _id,name,code,country_code from offline_state where country_code = '%@';",countryCode];
    
    NSMutableDictionary *ret = [[iSalesDB jk_query:sqlQuery completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        char *name = (char *) sqlite3_column_text(stmt, 1); // value
        char *code = (char *) sqlite3_column_text(stmt, 2); // value_id
        
        if (name == NULL) {
            name = "";
        }
        if (code == NULL) {
            code = "";
        }
        
        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_%ld",(*count)++];
        [container setValue:stateDic forKey:key];
        
    }] mutableCopy];
    
    [ret setValue:[NSNumber numberWithInt:ret.allKeys.count] forKey:@"count"];
    
    return ret;
}

+ (NSDictionary *)offline_getStateByCountryCodeId:(NSString *)codeId checkedState:(NSString *)stateCode {
    
    codeId = [self translateSingleQuote:codeId];
    NSString *sql = [NSString stringWithFormat:@"select name,code from offline_state where country_code = (select code as country_code FROM offline_country where countrycode_id = %@);",codeId];
    
    NSMutableDictionary *ret = [[iSalesDB jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        char *name = (char *) sqlite3_column_text(stmt, 0); // value
        char *code = (char *) sqlite3_column_text(stmt, 1); // value_id
        
        if (name == NULL) {
            name = "";
        }
        if (code == NULL) {
            code = "";
        }

        
        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_%ld",(*count)++];
        [container setValue:stateDic forKey:key];
    }] mutableCopy];
    
    [ret setValue:[NSNumber numberWithInt:ret.allKeys.count] forKey:@"count"];
    
    return ret;
}

+ (NSDictionary *)offline_getPrice {
    
    NSString *sql = [NSString stringWithFormat:@"select _id,name,type,order_by from price;"];
    
    NSMutableDictionary *ret = [[iSalesDB jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        char *name = (char *) sqlite3_column_text(stmt, 1);
        int type = sqlite3_column_int(stmt, 2);
        int orderBy = sqlite3_column_int(stmt, 3);
        
        if (name == NULL) {
            name = "";
        }
    
        
        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"];
        }
        
        [container setValue:priceDic forKey:key];
        
    }] mutableCopy];
    
    [ret setValue:[NSNumber numberWithInt:ret.allKeys.count] forKey:@"count"];
    
    return ret;
}

+ (NSDictionary *)offline_getSalesRep {
    
    // 首先从offline_login表中取出sales_code
    AppDelegate *app = (AppDelegate *)[UIApplication sharedApplication].delegate;
    NSString *user = app.user;
    user = [self translateSingleQuote:user];
    NSString *loginSql = [NSString stringWithFormat:@"select sales_code from offline_login where username = '%@'",user];
    __block NSString *user_code = @"";
    [iSalesDB jk_query:loginSql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        char *code = (char *)sqlite3_column_text(stmt, 0);
        if (code == NULL) {
            code = "";
        }
        
        user_code = [NSString stringWithUTF8String:code];
        
    }];
    
    // 再取所有salesRep
    NSString *sql = [NSString stringWithFormat:@"select _id,name,code,salesrep_id from offline_salesrep;"];
    NSMutableDictionary *ret = [[iSalesDB jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        // 1 name 2 code 3 salesrep_id
        char *name = (char *)sqlite3_column_text(stmt, 1);
        char *code = (char *)sqlite3_column_text(stmt, 2);
        int salesrep_id = sqlite3_column_int(stmt, 3);
        
        if (name == NULL) {
            name = "";
        }
        if (code == NULL) {
            code = "";
        }
        
        
        NSMutableDictionary *repDic = [NSMutableDictionary dictionary];
        [repDic setValue:[NSString stringWithFormat:@"%s - %s",code,name] forKey:@"value"];
        [repDic setValue:[NSNumber numberWithInt:salesrep_id] forKey:@"value_id"];
        // 比较code 相等则check
        if ([[NSString stringWithUTF8String:code] isEqualToString:user_code]) {
            [repDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
        }
        
        [container setValue:repDic forKey:[NSString stringWithFormat:@"val_%ld",(*count)++]];
        
    }] mutableCopy];
    
    [ret setValue:[NSNumber numberWithInt:ret.allKeys.count] forKey:@"count"];
    
    return ret;
}

+ (NSDictionary *)offline_dealZipCode:(NSString *)zipcode {
    
    zipcode = [self translateSingleQuote:zipcode];
    NSString *sql = [NSString stringWithFormat:@"select country,state,city,country_code,state_code from offline_zipcode where zipcode = '%@';",zipcode];
    
    NSMutableDictionary *ret = [[iSalesDB jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        char *country = (char *)sqlite3_column_text(stmt, 0);
        char *state = (char *)sqlite3_column_text(stmt, 1);
        char *city = (char *)sqlite3_column_text(stmt, 2);
        char *country_code = (char *)sqlite3_column_text(stmt, 3);
        char *state_code = (char *)sqlite3_column_text(stmt, 4);
        
        if (country == NULL) {
            country = "";
        }
        if (state == NULL) {
            state = "";
        }
        if (city == NULL) {
            city = "";
        }
        if (country_code == NULL) {
            country_code = "";
        }
        if (state_code == NULL) {
            state_code = "";
        }


        
        [container setValue:[NSString stringWithUTF8String:country] forKey:@"country"];
        [container setValue:[NSString stringWithUTF8String:state] forKey:@"state"];
        [container setValue:[NSString stringWithUTF8String:city] forKey:@"city"];
        [container setValue:[NSString stringWithUTF8String:country_code] forKey:@"country_code"];
        [container setValue:[NSString stringWithUTF8String:state_code] forKey:@"state_code"];
        
    }] mutableCopy];
    
    return ret;
}


+ (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;
    code_id = [self translateSingleQuote:code_id];
    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);
            if (code == NULL) {
                code = "";
            }
            ret = [NSString stringWithUTF8String:code];
        }
        
        sqlite3_finalize(statement);
    }
    [iSalesDB close_db:db];
    
    return ret;
    
}

+ (NSString *)countryCodeIdByCode:(NSString *)code {
    NSString *ret = nil;
    code = [self translateSingleQuote:code];
    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);
            if (_id == NULL) {
                _id = "";
            }
            ret = [NSString stringWithFormat:@"%s",_id];
        }
        
        sqlite3_finalize(statement);
    }
    [iSalesDB close_db:db];
    
    return ret;
}

+ (NSString *)countryNameByCountryCodeId:(NSString *)codeId {
    
    NSString *name = nil;
    codeId = [self translateSingleQuote:codeId];
    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);
            if (value == NULL) {
                value = "";
            }
            name = [NSString stringWithUTF8String:value];
        }
        
        sqlite3_finalize(statement);
    }
    [iSalesDB close_db:db];
    
    return name;
}

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

+ (NSString *)salesRepCodeById:(NSString *)_id {
    
    NSString *ret = nil;
    _id = [self translateSingleQuote:_id];
    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);
            if (rep == NULL) {
                rep = "";
            }
            ret = [NSString stringWithUTF8String:rep];
        }
        
        sqlite3_finalize(statement);
    }
    [iSalesDB close_db:db];
    
    
    return ret;
}

+ (NSString *)textAtColumn:(int)col statement:(sqlite3_stmt *)stmt{
    char *tx = (char *)sqlite3_column_text(stmt, col);
    if (tx == NULL) {
        tx = "";
    }
    NSString *text = [NSString stringWithFormat:@"%s",tx];
    if (!text) {
        text = @"";
    }
    return text;
}

+ (NSMutableDictionary *)dictionaryFileName:(NSString *)name {
    NSString *path = [[NSBundle mainBundle] pathForResource:name ofType:nil];
    NSData *data = [NSData dataWithContentsOfFile:path];
    NSMutableDictionary *ret = [[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingMutableContainers error:nil] mutableCopy];
    
    return ret;
}

+ (NSMutableDictionary *)mutableDictionary:(NSString *)key inDictionary:(NSDictionary *)dic {
    return [[dic objectForKey:key] mutableCopy];
}

+ (NSString *)translateSingleQuote:(NSString *)string {
    return [string stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
}

+ (NSString *)valueInParams:(NSDictionary *)params key:(NSString *)key {
    return [params valueForKey:key] ? [params valueForKey:key] : @"";
}

#pragma mark contact list

+(NSDictionary*) offline_contactlist :(NSMutableDictionary *) params
{
    
    //    contactType = "Sales_Order_Customer";
    //    limit = 25;
    //    offset = 0;
    //    password = 123456;
    //    "price_name" = 16;
    //    user = EvanK;
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSString* contactType = [params valueForKey:@"contactType"]; //  contactType = "Sales_Order_Customer";
    if (contactType) {
        contactType = [NSString stringWithFormat:@"%@ = 1",contactType];
    } else {
        contactType = @"1 = 1";
    }
    
    NSString* keyword = [params valueForKey:@"keyword"]; // search 时才有
    DebugLog(@"offline contact list keyword: %@",keyword);
    
    // advanced search
    
    NSString* contact_name = [params valueForKey:@"contact_name"]; // "contact_name"
    if (contact_name) {
        contact_name = [contact_name stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        contact_name = [NSString stringWithFormat:@"and lower(contact_name) like '%%%@%%'",contact_name.lowercaseString];
    } else {
        contact_name = @"";
    }
    NSString* customer_phone = [params valueForKey:@"customer_phone"]; // "customer_phone"
    if (customer_phone) {
        customer_phone = [customer_phone stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_phone = [NSString stringWithFormat:@"and lower(decrypt(phone)) like '%%%@%%'",customer_phone.lowercaseString];
    } else {
        customer_phone = @"";
    }
    NSString* customer_fax = [params valueForKey:@"customer_fax"]; // "customer_fax"
    if (customer_fax) {
        customer_fax = [customer_fax stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_fax = [NSString stringWithFormat:@"and lower(fax) like '%%%@%%'",customer_fax.lowercaseString];
    } else {
        customer_fax = @"";
    }

    NSString* customer_zipcode = [params valueForKey:@"customer_zipcode"]; // "customer_zipcode"
    if (customer_zipcode) {
        customer_zipcode = [customer_zipcode stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_zipcode = [NSString stringWithFormat:@"and lower(zipcode) like '%%%@%%'",customer_zipcode.lowercaseString];
    } else {
        customer_zipcode = @"";
    }
    NSString* customer_sales_rep = [params valueForKey:@"customer_sales_rep"]; // "customer_sales_rep"
    if (customer_sales_rep) {
        customer_zipcode = [customer_zipcode stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_sales_rep = [NSString stringWithFormat:@"and lower(sales_rep) like '%%%@%%'",customer_sales_rep.lowercaseString];
    } else {
        customer_sales_rep = @"";
    }
    NSString* customer_state = [params valueForKey:@"customer_state"]; // "customer_state"
    if (customer_state) {
        customer_state = [customer_state stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_state = [NSString stringWithFormat:@"and lower(state) like '%%%@%%'",customer_state.lowercaseString];
    } else {
        customer_state = @"";
    }
    NSString* customer_name = [params valueForKey:@"customer_name"]; // "customer_name" 也就是company name
    if (customer_name) {
        customer_name = [customer_name stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_name = [NSString stringWithFormat:@"and lower(decrypt(company_name)) like '%%%@%%'",customer_name.lowercaseString];
    } else {
        customer_name = @"";
    }
    NSString* customer_country = [params valueForKey:@"customer_country"]; // "customer_country"
    if (customer_country) {
        customer_country = [customer_country stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_country = [NSString stringWithFormat:@"and lower(country) like '%%%@%%'",customer_country.lowercaseString];
    } else {
        customer_country = @"";
    }
    NSString* customer_cid = [params valueForKey:@"customer_cid"]; // "customer_cid"
    if (customer_cid) {
        customer_cid = [customer_cid stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_cid = [NSString stringWithFormat:@"and lower(contact_id) like '%%%@%%'",customer_cid.lowercaseString];
    } else {
        customer_cid = @"";
    }
    NSString* customer_city = [params valueForKey:@"customer_city"]; // "customer_city"
    if (customer_city) {
        customer_city = [customer_city stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_city = [NSString stringWithFormat:@"and lower(city) like '%%%@%%'",customer_city.lowercaseString];
    } else {
        customer_city = @"";
    }
    NSString* customer_address = [params valueForKey:@"customer_address"]; // "customer_address"
    if (customer_address) {
        customer_address = [customer_address stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_address = [NSString stringWithFormat:@"and lower(decrypt(addr)) like '%%%@%%'",customer_address.lowercaseString];
    } else {
        customer_address = @"";
    }
    NSString* customer_email = [params valueForKey:@"customer_email"]; // "customer_email"
    if (customer_email) {
        customer_email = [customer_email stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        customer_email = [NSString stringWithFormat:@"and lower(email) like '%%%@%%'",customer_email.lowercaseString];
    } else {
        customer_email = @"";
    }
    
    NSString *price_name = [params valueForKey:@"price_name"];
    if (price_name) {
        if ([price_name containsString:@","]) {
            
            // 首先从 price表中查处name
            NSArray *pArray = [price_name componentsSeparatedByString:@","];
            
            NSMutableString *mutablePStr = [NSMutableString stringWithFormat:@"SELECT name FROM price where type = %@ ",pArray[0]];
            
            for (int i = 1;i < pArray.count;i++) {
                NSString *p = pArray[i];
                [mutablePStr appendFormat:@" or type = %@ ",p];
            }
            [mutablePStr appendString:@";"];
            
            __block NSMutableArray *price_name_array = [NSMutableArray array];
            [iSalesDB jk_query:mutablePStr db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
                char *name = (char *)sqlite3_column_text(stmt, 0);
                if (!name)
                    name = "";
                [price_name_array addObject:[self translateSingleQuote:[NSString stringWithUTF8String:name]]];
                
            }];
            
            // 再根据name 拼sql
            NSMutableString *mutable_price_name = [NSMutableString string];
            [mutable_price_name appendFormat:@" and( price_type like '%%%@%%' ",price_name_array[0]];
            for (int i = 1; i < price_name_array.count; i++) {
                [mutable_price_name appendFormat:@" or price_type like '%%%@%%' ",price_name_array[i]];
            }
            [mutable_price_name appendString:@")"];
            
            price_name = mutable_price_name;
            
        } else {
            price_name = [price_name stringByReplacingOccurrencesOfString:@" " withString:@""];
            if ([price_name isEqualToString:@""]) {
                price_name = @"";
            } else {
                
                __block NSString *price;
                price_name = [self translateSingleQuote:price_name];
                [iSalesDB jk_query:[NSString stringWithFormat:@"SELECT name FROM price where type = %@ ;",price_name] db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
                    char *p = (char *)sqlite3_column_text(stmt, 0);
                    if (p == NULL) {
                        p = "";
                    }
                    price = [NSString stringWithUTF8String:p];
                }];
                
                if ([price isEqualToString:@""]) {
                    price_name = @"";
                } else {
                    price = [self translateSingleQuote:price];
                    price_name = [NSString stringWithFormat:@"and price_type like '%%%@%%'",price];
                }
                
            }
            
        }
        
    } else {
        price_name = @"";
    }
    
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    NSString *where = [NSString stringWithFormat:@" %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ ",contactType,contact_name,customer_phone,customer_fax,customer_zipcode,customer_sales_rep,customer_state,customer_name,customer_country,customer_cid,customer_city,customer_address,customer_email,price_name];


     NSString *sql = [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 %@ order by decrypt(company_name) limit %d offset %d",where,limit,offset];
    
    
    

   // 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 decrypt(company_name) limit %d offset %d",contactType , limit, offset];
//        count=[iSalesDB get_recordcount:db table:@"offline_contact" where:[NSString stringWithFormat:@"%@='1",contactType]];
        
        sqlQuery = sql;
        
        count = [iSalesDB get_recordcount:db table:@"offline_contact" where:where];
    }
    else
    {
        // NSString* encrypt_keyword= [AESCrypt fastencrypt:keyword];
        
        keyword = keyword.lowercaseString;
        keyword = [keyword stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
        
        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 %@ and (lower(contact_id ) like'%%%@%%' or lower(decrypt(company_name)) like '%%%@%%' or lower(decrypt(phone)) like '%%%@%%' or lower(decrypt(addr)) like '%%%@%%') order by decrypt(company_name) limit %d offset %d",contactType , keyword,keyword,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 alloc]initWithUTF8String:fax];
                if(nsfax.length>0)
                    nsfax= [NSString stringWithFormat:@"FAX:%@",nsfax];
            }
            
            char *email = (char*)sqlite3_column_text(statement, 17);
            NSString *nsemail = nil;
            if(email==nil)
                nsemail=@"";
            else
            {
                nsemail= [[NSString alloc]initWithUTF8String:email];
                if(nsemail.length>0)
                    nsemail= [NSString stringWithFormat:@"EMAIL:%@",nsemail];

//                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"];
            [item setValue:editable==1?@"true":@"false" forKey:@"can_update"];
            [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);
    }
    
    
    
    [iSalesDB close_db:db];
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}


#pragma mark contact Advanced search

+(NSData *) offline_contactAdvancedSearch:(NSMutableDictionary *) params
{
    NSMutableDictionary *contactAdvanceDic = [self dictionaryFileName:@"contactAdvanceSearch.json"];
    return [RAUtils dict2data:contactAdvanceDic];
}

#pragma mark create new contact

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

+ (NSData *)offline_saveContact:(NSDictionary *)params update:(BOOL)update {
    
    NSString *addr = nil;
    NSString *contact_name = nil;
    
    NSString *companyName = [params objectForKey:@"company"];
    if (companyName) {
        companyName = [AESCrypt fastencrypt:companyName];
    } else {
        companyName = @"";
    }
    companyName = [self translateSingleQuote:companyName];
    
    NSString *addr1 = [params objectForKey:@"address"];
    
    
    NSString *addr2 = [params objectForKey:@"address2"];
    
    
    NSString *addr3 = [params objectForKey:@"address_3"];
    
    
    NSString *addr4 = [params objectForKey:@"address_4"];
    
    if (!addr2) {
        addr2 = @"";
    }
    if (!addr3) {
        addr3 = @"";
    }
    if (!addr4) {
        addr4 = @"";
    }
    if (!addr1) {
        addr1 = @"";
    }
    
    addr = [RAUtils arr2string:@[addr1,addr2,addr3,addr4] separator:@" " trim:true];
    addr = [AESCrypt fastencrypt:addr];
    addr = [self translateSingleQuote:addr];
    
    if (addr1 && ![addr1 isEqualToString:@""]) {
        addr1 = [AESCrypt fastencrypt:addr1];
    }
    addr1 = [self translateSingleQuote:addr1];
    addr2 = [self translateSingleQuote:addr2];
    addr3 = [self translateSingleQuote:addr3];
    addr4 = [self translateSingleQuote:addr4];
    
    
    NSString *country = [params objectForKey:@"country"];
    if (country) {
        country = [self countryNameByCountryCodeId:country];
    } else {
        country = @"";
    }
    country = [self translateSingleQuote:country];
    
    NSString *state = [params objectForKey:@"state"];
    if (!state) {
        state = @"";
    }
    state = [self translateSingleQuote:state];
    
    NSString *city = [params objectForKey:@"city"];
    if (!city) {
        city = @"";
    }
    city = [self translateSingleQuote:city];
    
    NSString *zipcode = [params objectForKey:@"zipcode"];
    if (!zipcode) {
        zipcode = @"";
    }
    zipcode = [self translateSingleQuote:zipcode];
    
    NSString *fistName = [params objectForKey:@"firstname"];
    if (!fistName) {
        fistName = @"";
    }

    
    NSString *lastName = [params objectForKey:@"lastname"];
    if (!lastName) {
        lastName = @"";
    }
    
    contact_name = [RAUtils arr2string:@[fistName,lastName] separator:@" " trim:true];
    contact_name = [self translateSingleQuote:contact_name];
    
    fistName = [self translateSingleQuote:fistName];
    
    
    lastName = [self translateSingleQuote:lastName];
    
    NSString *phone = [params objectForKey:@"phone"];
    if (phone) {
        phone = [AESCrypt fastencrypt:phone];
    } else {
        phone = @"";
    }
    phone = [self translateSingleQuote:phone];
    
    NSString *fax = [params objectForKey:@"fax"];
    if (!fax) {
        fax = @"";
    }
    fax = [self translateSingleQuote:fax];
    
    NSString *email = [params objectForKey:@"email"];
    if (!email) {
        email = @"";
    }
    email = [self translateSingleQuote:email];
    
    NSString *notes = [params objectForKey:@"contact_notes"];
    if (!notes) {
        notes = @"";
    }
    notes = [self translateSingleQuote:notes];
    
    NSString *price = [params objectForKey:@"price_name"];
    if (price) {
        price = [self priceNameByPriceId:price];
    } else {
        price = @"";
    }
    price = [self translateSingleQuote:price];
    
    NSString *salesRep = [params objectForKey:@"sales_rep"];
    if (salesRep) {
        salesRep = [self salesRepCodeById:salesRep];
    } else {
        salesRep = @"";
    }
    salesRep = [self translateSingleQuote:salesRep];
    
    NSString *img = [params objectForKey:@"business_card"];
    NSArray *array = [img componentsSeparatedByString:@","];
    NSString *img_0 = array[0];
    if (!img_0) {
        img_0 = @"";
    }
    img_0 = [self translateSingleQuote:img_0];
    
    NSString *img_1 = array[1];
    if (!img_1) {
        img_1 = @"";
    }
    img_1 = [self translateSingleQuote:img_1];
    
    NSString *img_2 = array[2];
    if (!img_2) {
        img_2 = @"";
    }
    img_2 = [self translateSingleQuote:img_2];
    
    NSString *contact_id = [NSUUID UUID].UUIDString;
    
    NSMutableDictionary *sync_dic = [params mutableCopy];
    
    
    NSString *sync_data = nil;
    
    NSString *sql = nil;
    if (update){
        contact_id = [params objectForKey:@"contact_id"];
        if (!contact_id) {
            contact_id = @"";
        }
        
        
        [sync_dic setValue:contact_id forKey:@"contact_id"];
        sync_data = [RAUtils dict2string:sync_dic];
        sync_data = [self translateSingleQuote:sync_data];
        
        sql = [NSString stringWithFormat:@"update offline_contact set 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 = '%@',sync_data = '%@',contact_name = '%@',addr = '%@' where contact_id = '%@';",companyName,addr1,addr2,addr3,addr4,country,state,city,zipcode,fistName,lastName,phone,fax,email,notes,price,salesRep,img_0,img_1,img_2,sync_data,contact_name,addr,contact_id];
    } else {
        
        contact_id = [self translateSingleQuote:contact_id];
        
        [sync_dic setValue:contact_id forKey:@"contact_id"];
        sync_data = [RAUtils dict2string:sync_dic];
        sync_data = [self translateSingleQuote:sync_data];
        
        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,Sales_Order_Customer,Sales_Order_Freight_Bill_To,Sales_Order_Ship_From,Sales_Order_Merchandise_Bill_To,Contact_Return_To,Sales_Order_Ship_To,sync_data,contact_name,addr) values ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@',1,'%@',1,1,0,1,0,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,sync_data,contact_name,addr];
    }
    
    int result = [iSalesDB execSql:sql];
    
    NSString *retStr = [NSString stringWithFormat:@"{\"result\":%d,\"min_ver\":\"160409\",\"mode\":\"Regular Mode\"}",result];
    
    return [retStr dataUsingEncoding:NSUTF8StringEncoding];
}


#pragma mark save new contact

+(NSData *) offline_saveNewContact:(NSMutableDictionary *) params
{
    return [self offline_saveContact:params update:NO];
}

#pragma mark edit contact

+(NSData *) offline_editContact:(NSMutableDictionary *) params
{
//    {
//        "contact_id" = "CE0D2445-3C1F-40EC-B94C-A39A6900FBDA";
//        password = 123456;
//        user = EvanK;
//    }
    
    NSString *path = [[NSBundle mainBundle] pathForResource:@"editContact.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;
    
    /*－－－－－－contact infor－－－－－－*/
    __block NSString *country = nil;
    __block NSString *company_name = nil;
    __block NSString *contact_id = params[@"contact_id"];
    
    __block NSString *addr_1,*addr_2,*addr_3,*addr_4;
    __block NSString *zipcode = nil;
    __block NSString *state = nil; // state_code
    __block NSString *city = nil; //
    __block NSString *firt_name,*last_name;
    __block NSString *phone,*fax,*email;
    __block NSString *notes,*price_type,*sales_rep;
    __block NSString *img_0,*img_1,*img_2;
    
    contact_id = [self translateSingleQuote:contact_id];
    NSString *sql = [NSString stringWithFormat:@"select country,company_name,addr_1,addr_2,addr_3,addr_4,zipcode,state,city,first_name,last_name,phone,fax,email,notes,price_type,sales_rep,img_0,img_1,img_2 from offline_contact where contact_id = '%@';",contact_id];
    
    [iSalesDB jk_query:sql completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        country = [self textAtColumn:0 statement:stmt]; // country name
        company_name = [self textAtColumn:1 statement:stmt];
        addr_1 = [self textAtColumn:2 statement:stmt];
        addr_2 = [self textAtColumn:3 statement:stmt];
        addr_3 = [self textAtColumn:4 statement:stmt];
        addr_4 = [self textAtColumn:5 statement:stmt];
        zipcode = [self textAtColumn:6 statement:stmt];
        state = [self textAtColumn:7 statement:stmt]; // state code
        city = [self textAtColumn:8 statement:stmt];
        firt_name = [self textAtColumn:9 statement:stmt];
        last_name = [self textAtColumn:10 statement:stmt];
        phone = [self textAtColumn:11 statement:stmt];
        fax = [self textAtColumn:12 statement:stmt];
        email = [self textAtColumn:13 statement:stmt];
        notes = [self textAtColumn:14 statement:stmt];
        price_type = [self textAtColumn:15 statement:stmt]; // name
        sales_rep = [self textAtColumn:16 statement:stmt]; // code
        img_0 = [self textAtColumn:17 statement:stmt];
        img_1 = [self textAtColumn:18 statement:stmt];
        img_2 = [self textAtColumn:19 statement:stmt];
        
    }];
    
    // decrypt
    if (company_name) {
        company_name = [AESCrypt fastdecrypt:company_name];
    }
    if (addr_1) {
        addr_1 = [AESCrypt fastdecrypt:addr_1];
    }
    if (phone) {
        phone = [AESCrypt fastdecrypt:phone];
    }
    
    NSString *countrySql = [NSString stringWithFormat:@"select code from offline_country where name = '%@';",[self translateSingleQuote:country]];
    countryCode = [iSalesDB jk_queryText:countrySql];
    
    stateCode = state;
    
    if ([params.allKeys containsObject:@"refresh_trigger"]) {
        
        if ([params[@"refresh_trigger"] isEqualToString:@"country"]) {
            
            NSString *code_id = params[@"country"];
            countryCode_id = code_id;
            countryCode = [self countryCodeByid:code_id];
            
        } else if ([params[@"refresh_trigger"] isEqualToString:@"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_8"] mutableCopy];
            [zipDic setValue:zipcode forKey:@"value"];
            [section_0 setValue:zipDic forKey:@"item_8"];
            
        }
        
    }
    
//    0		Country
//    1		Company Name
//    2		Contact ID
//    3		Picture
//    4		Address 1
//    5		Address 2
//    6		Address 3
//    7		Address 4
//    8		Zip Code
//    9		State/Province
//    10	City
//    11	Contact First Name
//    12	Contact Last Name
//    13	Phone
//    14	Fax
//    15	Email
//    16	Contact Notes
//    17	Price Type
//    18	Sales Rep

    // country
    NSDictionary *allCountry = [self offline_getAllCountryDefault:countryCode];
    [self setValue:allCountry forItemKey:@"item_0" valueKey:@"cadedate" inDictionary:section_0];
    
    // company
    [self setValue:company_name forItemKey:@"item_1" valueKey:@"value" inDictionary:section_0];
    
    // contact_id
    [self setValue:contact_id forItemKey:@"item_2" valueKey:@"value" inDictionary:section_0];
    
    // picture
    NSString *img_avalue = [NSString stringWithFormat:@"%@,%@,%@",img_0,img_1,img_2];
    [self setValue:img_avalue forItemKey:@"item_3" valueKey:@"avalue" inDictionary:section_0];
    [self setValue:img_0 forItemKey:@"item_3" valueKey:@"img_url_aname_0" inDictionary:section_0];
    [self setValue:img_1 forItemKey:@"item_3" valueKey:@"img_url_aname_1" inDictionary:section_0];
    [self setValue:img_2 forItemKey:@"item_3" valueKey:@"img_url_aname_2" inDictionary:section_0];
    [self setValue:img_0 forItemKey:@"item_3" valueKey:@"img_url_0" inDictionary:section_0];
    [self setValue:img_1 forItemKey:@"item_3" valueKey:@"img_url_1" inDictionary:section_0];
    [self setValue:img_2 forItemKey:@"item_3" valueKey:@"img_url_2" inDictionary:section_0];
    
    // addr 1 2 3 4
    [self setValue:addr_1 forItemKey:@"item_4" valueKey:@"value" inDictionary:section_0];
    [self setValue:addr_2 forItemKey:@"item_5" valueKey:@"value" inDictionary:section_0];
    [self setValue:addr_3 forItemKey:@"item_6" valueKey:@"value" inDictionary:section_0];
    [self setValue:addr_4 forItemKey:@"item_7" valueKey:@"value" inDictionary:section_0];
    
    // zip code
    [self setValue:zipcode forItemKey:@"item_8" valueKey:@"value" inDictionary:section_0];
    
    // state
    NSDictionary *allState = [self offline_getStateByCountryCode:countryCode checkedState:stateCode];
    [self setValue:allState forItemKey:@"item_9" valueKey:@"cadedate" inDictionary:section_0];
    
    // city
    [self setValue:city forItemKey:@"item_10" valueKey:@"value" inDictionary:section_0];
    
    // first last
    [self setValue:firt_name forItemKey:@"item_11" valueKey:@"value" inDictionary:section_0];
    [self setValue:last_name forItemKey:@"item_12" valueKey:@"value" inDictionary:section_0];
    
    // phone fax email
    [self setValue:phone forItemKey:@"item_13" valueKey:@"value" inDictionary:section_0];
    [self setValue:fax forItemKey:@"item_14" valueKey:@"value" inDictionary:section_0];
    [self setValue:email forItemKey:@"item_15" valueKey:@"value" inDictionary:section_0];
    
    // notes
    [self setValue:notes forItemKey:@"item_16" valueKey:@"value" inDictionary:section_0];
    
    // price
    NSMutableDictionary *priceDic = [[self offline_getPrice] mutableCopy];
    for (NSString *key in priceDic.allKeys) {
        
        if ([key containsString:@"val_"]) {
            NSMutableDictionary *dic = [priceDic[key] mutableCopy];
            if ([dic[@"value"] isEqualToString:price_type]) {
                [dic setValue:[NSNumber numberWithInteger:1] forKey:@"check"];
                [priceDic setValue:dic forKey:key];
            }
        }
        
    }
    [self setValue:priceDic forItemKey:@"item_17" valueKey:@"cadedate" inDictionary:section_0];
    
    // Sales Rep
    NSMutableDictionary *repDic = [[self offline_getSalesRep] mutableCopy];
    for (NSString *key in repDic.allKeys) {
        if ([key containsString:@"val_"]) {
            NSMutableDictionary *dic = [repDic[key] mutableCopy];
            NSString *value = dic[@"value"];
            NSString *code = [[[value componentsSeparatedByString:@"-"] firstObject] stringByReplacingOccurrencesOfString:@" " withString:@""];
            if (code && [code isEqualToString:sales_rep]) {
                [dic setValue:[NSNumber numberWithInteger:1] forKey:@"check"];
                [repDic setValue:dic forKey:key];
            }
        }
    }
    [self setValue:repDic forItemKey:@"item_18" valueKey:@"cadedate" inDictionary:section_0];
    
    [ret setValue:section_0 forKey:@"section_0"];
    
    return [RAUtils dict2data:ret];
}

#pragma mark save contact

+(NSData *) offline_saveContact:(NSMutableDictionary *) params
{
    return [self offline_saveContact:params update:YES];
}

#pragma mark category

+ (void)check:(NSString *)ck valueKey:(NSString *)valueKey inDictionary:(NSMutableDictionary *)dic{
    if (ck) {
        NSMutableDictionary *res = [self mutableDictionary:valueKey inDictionary:dic];
        for (NSString *key in res.allKeys) {
            if (![key isEqualToString:@"count"]) {
                NSMutableDictionary *val = [self mutableDictionary:key inDictionary:res];
                [val setValue:[NSNumber numberWithInteger:0] forKey:@"check"];
                if ([val[@"value"] isEqualToString:ck]) {
                    [val setValue:[NSNumber numberWithInteger:1] forKey:@"check"];
                }
                [res setValue:val forKey:key];
            }
        }
        [dic setValue:res forKey:valueKey];
    }
}

+(NSDictionary*) offline_category :(NSMutableDictionary *) params
{
//    NSString* orderCode = [params valueForKey:@"orderCode"];
    
    AppDelegate *appDelegate = (AppDelegate *)[UIApplication sharedApplication].delegate;
    
    NSString* category = [params valueForKey:@"category"];
    if (!category || [category isEqualToString:@""]) {
        category = @"%";
    }
    category = [self translateSingleQuote:category];
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
//    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    NSMutableDictionary *ret = [self dictionaryFileName:@"category.json"];
    NSMutableDictionary *filter = [[ret objectForKey:@"filter"] mutableCopy];
    
    
    sqlite3 *db = [iSalesDB get_db];
   // [iSalesDB AddExFunction:db];
    
    int count;
    NSString *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];
    
//    {
//        alert = SP;
//        available = "Available Now";
//        bestseller = No;
//        category = 001;
//        "custom_id" = 0;
//        limit = 20;
//        offset = 0;
//        password = 123456;
//        price = "Display All";
//        "price_template" = 0;
//        "sold_by_qty" = "Display All";
//        "sort_by" = 0;
//        user = EvanK;
//    }
    double price_min = 0;
    double price_max = 0;
    if ([params.allKeys containsObject:@"alert"]) {
        // alert
        [self check:params[@"alert"] valueKey:@"alert" inDictionary:filter];
        NSString *alert = params[@"alert"];
        if ([alert isEqualToString:@"Display All"]) {
            alert = [NSString stringWithFormat:@""];
        } else {
            alert = [self translateSingleQuote:alert];
            alert = [NSString stringWithFormat:@"and alert = '%@'",alert];
        }
        
        // available
        [self check:params[@"available"] valueKey:@"availability" inDictionary:filter];
        NSString *available = params[@"available"];
        NSString *available_condition;
        if ([available isEqualToString:@"Display All"]) {
            available_condition = @"";
        } else if ([available isEqualToString:@"Available Now"]) {
            available_condition = @"and availability > 0";
        } else {
            available_condition = @"and availability == 0";
        }
        
        // best seller
        [self check:params[@"bestseller"] valueKey:@"best_seller" inDictionary:filter];
        NSString *best_seller = @"";
        NSString *order_best_seller = @"m.name asc";
        if ([params[@"bestseller"] isEqualToString:@"Yes"]) {
            best_seller = @"and best_seller > 0";
            order_best_seller = @"m.best_seller desc";
        }
        
        // price
        [self check:params[@"price"] valueKey:@"price" inDictionary:filter];
        NSString *price = params[@"price"];
        price_min = 0;
        price_max = MAXFLOAT;
        if (appDelegate.user) {
            NSArray *priceTypeArray = [self get_contact_default_price_type:nil db:db];
            NSMutableString *priceName = [NSMutableString string];
            for (int i = 0; i < priceTypeArray.count; i++) {
                NSString *pricetype = priceTypeArray[i];
                pricetype = [self translateSingleQuote:pricetype];
                if (i == 0) {
                    [priceName appendFormat:@"'%@'",pricetype];
                } else {
                    [priceName appendFormat:@",'%@'",pricetype];
                }
            }
            
            if ([price isEqualToString:@"Display All"]) {
                price = [NSString stringWithFormat:@""];
            } else if([price containsString:@"+"]){
                price = [price stringByReplacingOccurrencesOfString:@"+" withString:@""];
                price_min = [price doubleValue];
                price = [NSString stringWithFormat:@"and product_id in (select DISTINCT product_id from model_price where price_name in (%@) and cast(decrypt(price) as double) >= %.2lf and cast(decrypt(price) as double) <= %.2lf)",priceName,price_min,price_max];
            } else {
                NSArray *priceArray = [price componentsSeparatedByString:@"-"];
                price_min = [[priceArray objectAtIndex:0] doubleValue];
                price_max = [[priceArray objectAtIndex:1] doubleValue];
                price = [NSString stringWithFormat:@"and product_id in (select DISTINCT product_id from model_price where price_name in (%@) and cast(decrypt(price) as double) >= %.2lf and cast(decrypt(price) as double) <= %.2lf)",priceName,price_min,price_max];
            }
        } else {
            price = @"";
        }
        
        // sold_by_qty : Sold in quantities of %@
        [self check:params[@"sold_by_qty"] valueKey:@"qty" inDictionary:filter];
        NSString *qty = params[@"sold_by_qty"];
        if ([qty isEqualToString:@"Display All"]) {
            qty = @"";
        } else {
            qty = [self translateSingleQuote:qty];
            qty = [NSString stringWithFormat:@"and model_set = 'Sold in quantities of %@'",qty];
        }
        
        // cate
        category = [self translateSingleQuote:category];
        NSString *cateWhere = [NSString stringWithFormat:@"category like'%%#%@#%%'",category];
        
        // where bestseller > 0 order by bestseller desc
        // sql query: alert     availability(int)   best_seller(int)    price    qty
       sqlQuery = [NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from (select name,description,product_id,closeout,best_seller from model where %@ %@ %@ %@ %@ %@) m left join wishlist w on m.product_id=w.product_id order by %@ limit %d offset %d;",cateWhere,best_seller,alert,available_condition,qty,price,order_best_seller,limit,offset];
        
        where =[NSString stringWithFormat:@"%@ %@ %@ %@ %@ %@",cateWhere,best_seller,alert,available_condition,qty,price];
        
    }
    
   count = [iSalesDB get_recordcount:db table:@"model" where:where];
    
    if (!appDelegate.user) {
        [filter setValue:@{@"count" : @(0)} forKey:@"price"];
    }
    
    [ret setValue:filter forKey:@"filter"];
    
    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, 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);
            
            int wid = sqlite3_column_int(statement, 3);
            int closeout = sqlite3_column_int(statement, 4);
            
            NSMutableDictionary* imgjson=[self category_img:product_id  db:db];
            
            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);
    } else {
        DebugLog(@"nothing...");
    }
    DebugLog(@"count:%d",count);
    
    
    [iSalesDB close_db:db];
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}

# pragma mark item search
+(NSDictionary*)  offline_itemsearch:(NSMutableDictionary *) params
{
    AppDelegate *appDelegate = (AppDelegate *)[UIApplication sharedApplication].delegate;
    
    BOOL filterSearch = ![params.allKeys containsObject:@"covertype"];
    
    NSMutableDictionary *ret = [self dictionaryFileName:@"category.json"];
    NSMutableDictionary *filter = [[ret objectForKey:@"filter"] mutableCopy];
    
    // category
    NSDictionary *category_menu = [self offline_category_menu];
    [filter setValue:category_menu forKey:@"category"];
    
    NSString* where= nil;
    NSString* orderby= @"m.name";
    if (!filterSearch) {
        int covertype = [[params valueForKey:@"covertype"] intValue];

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

    }
    
    
    int limit = [[params valueForKey:@"limit"] intValue];
    int offset = [[params valueForKey:@"offset"] intValue];
    
   
    
    sqlite3 *db = [iSalesDB get_db];
  //  [iSalesDB AddExFunction:db];
    
    int count;
    
    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];
    
    double price_min = 0;
    double price_max = 0;
    if (filterSearch) {
        // alert
        [self check:params[@"alert"] valueKey:@"alert" inDictionary:filter];
        NSString *alert = params[@"alert"];
        if ([alert isEqualToString:@"Display All"]) {
            alert = [NSString stringWithFormat:@""];
        } else {
            alert = [self translateSingleQuote:alert];
            alert = [NSString stringWithFormat:@"and alert = '%@'",alert];
        }
        
        // available
        [self check:params[@"available"] valueKey:@"availability" inDictionary:filter];
        NSString *available = params[@"available"];
        NSString *available_condition;
        if ([available isEqualToString:@"Display All"]) {
            available_condition = @"";
        } else if ([available isEqualToString:@"Available Now"]) {
            available_condition = @"and availability > 0";
        } else {
            available_condition = @"and availability == 0";
        }
        
        // best seller
        [self check:params[@"bestseller"] valueKey:@"best_seller" inDictionary:filter];
        NSString *best_seller = @"";
        NSString *order_best_seller = @"m.name asc";

        if ([params[@"bestseller"] isEqualToString:@"Yes"]) {
            best_seller = @"and best_seller > 0";
            order_best_seller = @"m.best_seller desc";
        }
        
        // price
        [self check:params[@"price"] valueKey:@"price" inDictionary:filter];
        NSString *price = params[@"price"];
        price_min = 0;
        price_max = MAXFLOAT;
        if (appDelegate.user) {
            NSArray *priceTypeArray = [self get_contact_default_price_type:nil db:db];
            NSMutableString *priceName = [NSMutableString string];
            for (int i = 0; i < priceTypeArray.count; i++) {
                NSString *pricetype = priceTypeArray[i];
                pricetype = [self translateSingleQuote:pricetype];
                if (i == 0) {
                    [priceName appendFormat:@"'%@'",pricetype];
                } else {
                    [priceName appendFormat:@",'%@'",pricetype];
                }
            }
            
            if ([price isEqualToString:@"Display All"]) {
                price = [NSString stringWithFormat:@""];
            } else if([price containsString:@"+"]){
                price = [price stringByReplacingOccurrencesOfString:@"+" withString:@""];
                price_min = [price doubleValue];
                price = [NSString stringWithFormat:@"and product_id in (select DISTINCT product_id from model_price where price_name in (%@) and cast(decrypt(price) as double) >= %.2lf and cast(decrypt(price) as double) <= %.2lf)",priceName,price_min,price_max];
            } else {
                NSArray *priceArray = [price componentsSeparatedByString:@"-"];
                price_min = [[priceArray objectAtIndex:0] doubleValue];
                price_max = [[priceArray objectAtIndex:1] doubleValue];
                price = [NSString stringWithFormat:@"and product_id in (select DISTINCT product_id from model_price where price_name in (%@) and cast(decrypt(price) as double) >= %.2lf and cast(decrypt(price) as double) <= %.2lf)",priceName,price_min,price_max];
            }
        } else {
             price = @"";
        }
        
        // sold_by_qty : Sold in quantities of %@
        [self check:params[@"sold_by_qty"] valueKey:@"qty" inDictionary:filter];
        NSString *qty = params[@"sold_by_qty"];
        if ([qty isEqualToString:@"Display All"]) {
            qty = @"";
        } else {
            qty = [self translateSingleQuote:qty];
            qty = [NSString stringWithFormat:@"and model_set = 'Sold in quantities of %@'",qty];
        }
        
        // category
        NSString *category_id = params[@"ctgId"];
        NSMutableArray *cate_id_array = nil;
        NSMutableString *cateWhere = [NSMutableString string];
        
        if ([category_id isEqualToString:@""] || !category_id) {
            [cateWhere appendString:@"1 = 1"];
        } else {
            
            if ([category_id containsString:@","]) {
                cate_id_array = [[category_id componentsSeparatedByString:@","] mutableCopy];
            } else {
                cate_id_array = [@[category_id] mutableCopy];
            }
            /*-----------*/
            NSMutableDictionary *cateDic = [[filter objectForKey:@"category"] mutableCopy];
            
            
            for (int i = 0; i < cate_id_array.count; i++) {
                
                for (NSString *key0 in cateDic.allKeys) {
                    
                    if ([key0 containsString:@"category_"]) {
                        
                        NSMutableDictionary *category0 = [[cateDic objectForKey:key0] mutableCopy];
                        for (NSString *key1 in category0.allKeys) {
                            
                            if ([key1 containsString:@"category_"]) {
                                
                                NSMutableDictionary *category1 = [[category0 objectForKey:key1] mutableCopy];
                                [category1 setValue:[NSNumber numberWithInteger:0] forKey:@"check"];
                                if ([[category1 objectForKey:@"cid"] isEqualToString:cate_id_array[i]]) {
                                    
                                    cate_id_array[i] = [category1 objectForKey:@"id"];
                                    if (i == 0) {
                                        [cateWhere appendFormat:@"category like'%%#%@#%%'",cate_id_array[i]];
                                    } else {
                                        [cateWhere appendFormat:@"or category like'%%#%@#%%'",cate_id_array[i]];
                                    }
                                    [category1 setValue:[NSNumber numberWithInteger:1] forKey:@"check"];
                                    [category0 setValue:category1 forKey:key1];
                                    [cateDic setValue:category0 forKey:key0];
                                    
                                }
                                
                            }
                            
                        }
                        
                    }
                    
                }
                
            }
            [filter setValue:cateDic forKey:@"category"];
        }

        // where bestseller > 0 order by bestseller desc
        // sql query: alert     availability(int)   best_seller(int)    price    qty
        sqlQuery = [NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from (select name,description,product_id,closeout,best_seller from model where %@ %@ %@ %@ %@ %@) m left join wishlist w on m.product_id=w.product_id order by %@ limit %d offset %d;",cateWhere,best_seller,alert,available_condition,qty,price,order_best_seller,limit,offset];
        
        // count
        where =[NSString stringWithFormat:@"%@ %@ %@ %@ %@ %@",cateWhere,best_seller,alert,available_condition,qty,price];
        
    }
    where = [where stringByReplacingOccurrencesOfString:@"m." withString:@""];
    
    count = [iSalesDB get_recordcount:db table:@"model" where:where];
    

    
    
    if (!appDelegate.user) {
        [filter setValue:@{@"count" : @(0)} forKey:@"price"];
    }
    
    [ret setValue:filter forKey:@"filter"];

    
    
    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, 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);

            
            int wid = sqlite3_column_int(statement, 3);
            int closeout = sqlite3_column_int(statement, 4);
            
            NSMutableDictionary* imgjson=[self category_img:product_id db:db];
            
            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);
    }
    
    [iSalesDB close_db:db];
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return ret;
}



@end