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 product 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 item_id:(int) item_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.item_id=%d  order by a.order_by;",item_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.item_id=%d and b.price_name in(%@) order by a.order_by;",item_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;
//}

+(NSNumber*) get_model_default_price:(NSString*) contact_id product_id:(id) product_id item_id:(id)item_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];
    
    
    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 *productIdCondition = @"1 = 1";
    if (product_id) {
        productIdCondition = [NSString stringWithFormat:@"product_id = %@",product_id];
    }
    NSString *itemIdCondition = @"";
    if (item_id) {
        itemIdCondition = [NSString stringWithFormat:@"and item_id = %@",item_id];
    }
    
    
    NSString* sqlQuery = [NSString stringWithFormat:@"select price from model_price where %@ %@ and price_name in(%@);",productIdCondition,itemIdCondition,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 && model_name)
        sqlQuery = [NSString stringWithFormat:@"select i.url from product 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];// 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;
    else if (product_id)
        sqlQuery = [NSString stringWithFormat:@"select i.url from product 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];// select i.url from model m
    
    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 product where name='%@';",model_name];// select default_category from model where name='%@';
    else
        sqlQuery = [NSString stringWithFormat:@"select default_category from product where product_id=%@;",product_id]; // select default_category from model where 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:@"product" 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 product where name='%@';",model_name]; // 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='%@';
    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,item_id from product where product_id=%@;",product_id]; // 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=%@;

    
    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];
            
            int item_id = sqlite3_column_int(statement, 25);
            
            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];
                NSNumber* price = [self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_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];
            NSDictionary* pricejson = [self get_model_all_price:appDelegate.contact_id item_id:item_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:@"product" where:[NSString stringWithFormat:@"(lower(name) like'%%%@%%' or lower(description) like'%%%@%%') and is_active = 1",keyword,keyword]];
    
    
    NSString *sqlQuery = nil;
    if(exactMatch )
        sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from product m left join wishlist w on m.product_id=w.product_id where lower(name) like'%@%%' and m.is_active = 1 order by m.name limit %d offset %d ;",keyword,limit, offset]; // 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 ;
    else

        sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from product m left join wishlist w on m.product_id=w.product_id where (lower(name) like'%%%@%%' or lower(description) like'%%%@%%') and m.is_active = 1 order by m.name limit %d offset %d ;",keyword,keyword,limit, offset];// 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
    
    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];
    
    sqlite3 *db = [iSalesDB get_db];
    
    int result =[iSalesDB execSql:sql_neworder db:db];
    [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'
    //soId
    int _id=[iSalesDB get_insertid:@"offline_order" db:db];
    
    
    
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select so_id from offline_order where _id=%d ",_id];
    sqlite3_stmt * statement;
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            //            char *name = (char*)sqlite3_column_text(statement, 1);
            //            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];
            
            //ret = sqlite3_column_int(statement, 0);
            
            char *soId = (char*)sqlite3_column_text(statement, 0);
            if(soId==nil)
                soId="";
            NSString *nssoId = [[NSString alloc]initWithUTF8String:soId];
            [ret setValue:nssoId forKey:@"soId"];
            [ret setValue:nssoId forKey:@"orderCode"];
            
        }
        sqlite3_finalize(statement);
    }
    
    [ret setValue:[NSNumber numberWithInt:0] forKey:@"orderStatus"];
    [ret setValue:@"Regular Mode" forKey:@"mode"];
    
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
    
}

+(NSData*) offline_editorder :(NSMutableDictionary *) params
{
    
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    
    
    NSString* orderCode = [params valueForKey:@"orderCode"];
    
    
    
    sqlite3 *db = [iSalesDB get_db];
    
    int cart_count=[iSalesDB get_recordcount:db table:@"offline_cart" where:[NSString stringWithFormat:@"so_no='%@'",orderCode]];
    
    int wish_count=[iSalesDB get_recordcount:db table:@"offline_wishlist" where:@"1=1"];
    ret[@"wish_count"]=[NSNumber numberWithInt:wish_count ];
    ret[@"cart_count"]=[NSNumber numberWithInt:cart_count ];
    ret[@"result"]=[NSNumber numberWithInt:RESULT_TRUE ];
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
    
    
}

+(NSData*) offline_add2cart :(NSMutableDictionary *) params;
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    sqlite3 *db = [iSalesDB get_db];
    
    int count =0;
    if(params[@"count"]!=nil)
    {
        count = [params[@"count"] intValue];
    }
    
    
    NSString* product_id=params[@"product_id"];
    NSString* orderCode=params[@"orderCode"];
    
    
    NSArray* arr_id=[RAUtils string2arr:product_id separator:@","];
    
    for(int i=0;i<arr_id.count;i++)
    {
        
        int _id=[iSalesDB get_recordid:db table:@"offline_cart" where:[NSString stringWithFormat:@"so_no='%@' and product_id=%@",orderCode,arr_id[i]]];
        int item_qty= count;
        if(item_qty==0)
            item_qty=[self model_stockUom:[arr_id[i] intValue] db:db];
        
        NSString* sql=nil;
        
        if(_id<0)
        {
            //             NSString* bundle_serialno= [self model_bundle:[arr_id[i] intValue] db:db];
            int item_id=[self product_itemid:[arr_id[i] intValue] db:db];
            
            sql=[NSString stringWithFormat:@"insert into offline_cart(product_id,so_no,item_count,item_id) values(%@,'%@',%d,%d)",arr_id[i],orderCode,item_qty,item_id];
            
        }
        else
        {
            sql=[NSString stringWithFormat:@"update offline_cart set item_count=item_count+%d where _id=%d",item_qty,_id];
            
        }
        [iSalesDB execSql:sql db:db];
        
    }
    
    
    
    
    int cart_count=[iSalesDB get_recordcount:db table:@"offline_cart" where:[NSString stringWithFormat:@"so_no='%@'",orderCode]];
    
    int wish_count=[iSalesDB get_recordcount:db table:@"offline_wishlist" where:@"1=1"];
    ret[@"wish_count"]=[NSNumber numberWithInt:wish_count ];
    ret[@"cart_count"]=[NSNumber numberWithInt:cart_count ];
    ret[@"result"]=[NSNumber numberWithInt:RESULT_TRUE ];
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
}
+(NSDictionary*) model_bundle:(int) item_id db:(sqlite3*)db compute_part:(bool)compute
{
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    NSMutableDictionary* ret = [[NSMutableDictionary alloc]init];
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select b.item_id,b.qty,c.name,c.description,b.unit_price,b.use_unitprice from (select item_id,qty,decrypt(unit_price) as unit_price,use_unitprice from bundle where product_item_id=%d) b left join catalog c on b.item_id=c.item_id;",item_id];
    sqlite3_stmt * statement;
    
    int count=0;
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            NSMutableDictionary* itemjson = [[NSMutableDictionary alloc]init];
            int bitem_id = sqlite3_column_int(statement, 0);
            int bitem_qty = sqlite3_column_int(statement, 1);
            
            char *name = (char*)sqlite3_column_text(statement, 2);
            if(name==nil)
                name="";
            NSString* nsname=[[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 3);
            if(description==nil)
                description="";
            NSString* nsdescription=[[NSString alloc]initWithUTF8String:description];
            
            double unit_price = sqlite3_column_double(statement, 4);
             int use_unitprice = sqlite3_column_int(statement, 5);
            if(use_unitprice!=1)
            {
                unit_price=[self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_id) db:db].doubleValue;
            }
            
            itemjson[@"model"]=nsname;
            itemjson[@"description"]=nsdescription;
            itemjson[@"unit_price"]=[NSString stringWithFormat:@"%.2f",unit_price];
            itemjson[@"modulus"]=[NSNumber numberWithInt:bitem_qty];
            
            if(compute)
            {
                itemjson[@"combine"]=[self model_bundle:bitem_id db:db compute_part:compute];
            }
            ret[[NSString stringWithFormat:@"item_%d",count]]=itemjson;
            count++;
            
        }
        sqlite3_finalize(statement);
    }
    ret[@"count"]=@(count);
    if(count==0)
        return nil;
    else
        return ret;
}
+(NSDictionary*) model_subtotal:(int) item_id count:(int)count db:(sqlite3*)db compute_part:(bool)compute
{
    //compute: add part to subtotal;
    NSMutableDictionary* ret = [[NSMutableDictionary alloc]init];
    
    double cuft=0;
    double weight=0;
    int carton=0;
    
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select ulength ,uwidth ,uheight ,uweight  ,mlength ,mwidth ,mheight ,mweigth  ,ilength ,iwidth ,iheight , iweight ,pcs ,mpack ,ipack,ucbf,icbf,mcbf from catalog where item_id=%d;",item_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            double ulength = sqlite3_column_double(statement, 0);
            double uwidth = sqlite3_column_double(statement, 1);
            double uheight = sqlite3_column_double(statement, 2);
            double uweight = sqlite3_column_double(statement, 3);
            double mlength = sqlite3_column_double(statement, 4);
            double mwidth = sqlite3_column_double(statement, 5);
            double mheight = sqlite3_column_double(statement, 6);
            double mweight = sqlite3_column_double(statement, 7);
            double ilength = sqlite3_column_double(statement, 8);
            double iwidth = sqlite3_column_double(statement, 9);
            double iheight = sqlite3_column_double(statement, 10);
            double iweight = sqlite3_column_double(statement, 11);
            int pcs = sqlite3_column_int(statement,12);
            int mpack = sqlite3_column_int(statement, 13);
            int ipack = sqlite3_column_int(statement, 14);
            double ucbf = sqlite3_column_double(statement, 15);
            double icbf = sqlite3_column_double(statement, 16);
            double mcbf = sqlite3_column_double(statement, 17);
            
            if(ipack==0)
            {
                carton= count/mpack ;
                weight = mweight*carton;
                cuft= carton*(mlength*mwidth*mheight);
                int remain=count%mpack;
                if(remain==0)
                {
                    //do nothing;
                }
                else
                {
                    carton++;
                    weight += uweight*remain;
                    cuft += (ulength*uwidth*uheight)*remain;
                }
                
            }
            else
            {
                carton = count/(mpack*ipack);
                weight = mweight*carton;
                cuft= carton*(mlength*mwidth*mheight);
                int remain=count%(mpack*ipack);
                if(remain==0)
                {
                    // do nothing;
                }
                else
                {
                    carton++;
                    int icarton =remain/ipack;
                    int iremain=remain%ipack;
                    
                    weight += iweight*icarton;
                    cuft += (ilength*iwidth*iheight)*icarton;
                    if(iremain==0)
                    {
                        //do nothing;
                    }
                    else
                    {
                        
                        weight += uweight*iremain;
                        cuft += (ulength*uwidth*uheight)*iremain;
                    }
                    
                    
                }
                
            }
#ifdef BUILD_NPD
            cuft=ucbf*count;
            weight= uweight*count;
#endif
            
        }
        
        
        sqlite3_finalize(statement);
    }
    

    
    
    if(compute)
    {
        NSArray * arr_count=nil;
        NSArray * arr_bundle=[self model_bundle:item_id db:db count:&arr_count];
        for(int i=0;i<arr_bundle.count;i++)
        {
            [arr_bundle[i] intValue];
            NSDictionary* bundlejson=[self model_subtotal:[arr_bundle[i] intValue] count:[arr_count[i] intValue]*count db:db compute_part:compute];
            cuft+=[bundlejson[@"cuft"] doubleValue];
            weight+=[bundlejson[@"weight"] doubleValue];
            carton+=[bundlejson[@"carton"] intValue];
        }
    }
    ret[@"cuft"]= [NSNumber numberWithDouble:cuft];
    ret[@"weight"]= [NSNumber numberWithDouble:weight];
    ret[@"carton"]= [NSNumber numberWithInteger:carton];
    
    return ret;
}
+(NSData*) offline_requestcart :(NSMutableDictionary *) params
{
    NSMutableDictionary* ret = [[NSMutableDictionary alloc] init];
    sqlite3 *db = [iSalesDB get_db];
    UIApplication * app = [UIApplication sharedApplication];
    AppDelegate *appDelegate = (AppDelegate *)[app delegate];
    
    NSString* orderCode=params[@"orderCode"];
    
    
    
    
    
    NSString *sqlQuery =     [ NSString stringWithFormat:@"select c.product_id,c.price,c.discount,c.item_count,c.line_note,m.name,m.description,c.item_id,m.stockUom,c._id from (select _id,product_id,decrypt(str_price) as price,discount,item_count,line_note,item_id from offline_cart where so_no='%@') c left join product m on c.product_id=m.product_id",orderCode ];

    
//        NSString *sqlQuery =     [ NSString stringWithFormat:@"select c.product_id,decrypt(c.str_price),c.discount,c.item_count,c.line_note,m.name,m.description,c.item_id,m.stockUom,c._id from offline_cart c  left join product m on c.product_id=m.product_id where c.so_no='%@'",orderCode ];
    
    
//   NSString *sqlQuery = @"select * from (select decrypt(str_price) from offline_cart) c";
    
    DebugLog(@"offline_login sql:%@",sqlQuery);
    sqlite3_stmt * statement;
    
    
    [ret setValue:[NSNumber numberWithInt:AP_USER_NOT_AUTH ] forKey:@"result"];
    
    int count=0;
    int dbresult=sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil);
    if ( dbresult== SQLITE_OK)
    {
        
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            
            NSMutableDictionary* itemjson = [[NSMutableDictionary alloc] init];
            
            
            
            
            int product_id = sqlite3_column_int(statement, 0);
            
            char *str_price = (char*)sqlite3_column_text(statement, 1);
            
            int item_id = sqlite3_column_int(statement, 7);
            
            NSString* Price=nil;
            if(str_price==nil)
            {
//                NSNumber* price= [self get_model_default_price:appDelegate.contact_id product_id:product_id db:db];
                NSNumber* price = [self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_id) db:db];
                if(price==nil)
                    Price=@"No Price.";
                else
                    Price=[NSString stringWithFormat:@"%.2f",price.floatValue];
            }
            else
            {
                
                Price=[NSString stringWithFormat:@"%.2f",[[[NSString alloc]initWithUTF8String:str_price] floatValue]];
            }
            
            
            double discount = sqlite3_column_double(statement, 2);
            int item_count = sqlite3_column_int(statement, 3);
            
            char *line_note = (char*)sqlite3_column_text(statement, 4);
            NSString *nsline_note=nil;
            if(line_note!=nil)
                nsline_note= [[NSString alloc]initWithUTF8String:line_note];
            
            
            char *name = (char*)sqlite3_column_text(statement, 5);
            NSString *nsname=nil;
            if(name!=nil)
                nsname= [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(statement, 6);
            NSString *nsdescription=nil;
            if(description!=nil)
                nsdescription= [[NSString alloc]initWithUTF8String:description];
            

            
            int stockUom = sqlite3_column_int(statement, 8);
            int _id = sqlite3_column_int(statement, 9);

            
            NSDictionary* bsubtotaljson=[self model_subtotal:item_id count:item_count db:db compute_part:true];
            double cuft=[bsubtotaljson[@"cuft"] doubleValue];
            double weight=[bsubtotaljson[@"weight"] doubleValue];
            int carton=[bsubtotaljson[@"carton"] intValue];
            
//            itemjson[@"img_url"]= [self model_category_img:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:item_id]] model_name:nil db:db];
            itemjson[@"img_url"] = [self item_image:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:item_id]] db:db];
            itemjson[@"model"]=nsname;
            itemjson[@"cuft"]=[NSNumber numberWithDouble:cuft];
            itemjson[@"weight"]=[NSNumber numberWithDouble:weight];
            itemjson[@"carton"]=[NSNumber numberWithInt:carton];
            itemjson[@"cart_item_id"]=[NSNumber numberWithInt:_id].stringValue;
            itemjson[@"check"]=@"true";
            itemjson[@"product_id"]=[NSNumber numberWithInt:product_id].stringValue;
            itemjson[@"count"]=[NSNumber numberWithInt:item_count];
            itemjson[@"unit_price"]=Price;
            itemjson[@"discount"]=[NSNumber numberWithInt:discount];
            itemjson[@"description"]=[NSString stringWithFormat:@"%@\n%@",nsname,nsdescription ];
            itemjson[@"stockUom"]=[NSNumber numberWithInt:stockUom];
            itemjson[@"note"]=nsline_note;
            itemjson[@"combine"]=[self model_bundle:item_id db:db compute_part:false];
            
            ret[[NSString stringWithFormat:@"item_%d",count]]=itemjson;
            count++;
        }
        
        
            ret[@"result"]=[NSNumber numberWithInt:RESULT_TRUE ];
        sqlite3_finalize(statement);
    }
    
    
    
    
    
    
    int cart_count=[iSalesDB get_recordcount:db table:@"offline_cart" where:[NSString stringWithFormat:@"so_no='%@'",orderCode]];
    
    int wish_count=[iSalesDB get_recordcount:db table:@"offline_wishlist" where:@"1=1"];
    ret[@"wish_count"]=[NSNumber numberWithInt:wish_count ];
    ret[@"cart_count"]=[NSNumber numberWithInt:cart_count ];
    ret[@"count"]=[NSNumber numberWithInt:cart_count ];

    ret[@"mode"]=@"Regular Mode";
    
    [iSalesDB close_db:db];
    
    NSString *general_note = [iSalesDB jk_queryText:[NSString stringWithFormat:@"select general_notes from offline_order where so_id = '%@';",orderCode]];
    
    DebugLog(@"general notes :%@",general_note);
    
    ret[@"general_note"]= general_note;
    
    return [RAUtils dict2data:ret];
}
+(NSData*) 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"];
            
            NSError* error=nil;
            
            NSString* statusFilter =[NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:@"offline_status_filter_cadedate" ofType:@"json"] encoding:NSUTF8StringEncoding error:&error];
            [header setValue:statusFilter   forKey:@"statusFilter"];
            
            
            
            
            
            [ret setObject:header forKey:@"header"];
            [ret setValue:nsmode   forKey:@"mode"];
            
            
            
        }
        
        
        
        sqlite3_finalize(statement);
    }
    
    
    
    [iSalesDB close_db:db];
    
    
    
    
    DebugLog(@"data string: %@",[RAUtils dict2string:ret] );
    
    return [RAUtils dict2data: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 product 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]; // 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 ;

    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 product where product_id=%d ;",field,product_id]; // select %@,property_display from model where product_id=%d ;

    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 product where product_group='%@'  order by name;",field,product_group]; // select %@,selector_display,product_id,category from model where product_group='%@'  order by name;

    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;
}
+(int) product_itemid:(int)product_id db:(sqlite3*)db
{
    int item_id=-1;
    NSString *sqlQuery = [NSString stringWithFormat:@"select item_id from product where product_id=%d;",product_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            item_id = sqlite3_column_int(statement, 0);
        }
        
        
        sqlite3_finalize(statement);
    }
    
    return item_id;
}
+(NSArray*) model_bundle:(int)item_id db:(sqlite3*)db count:(NSArray**)count //DEPRECATED(1_5)
{
    //  NSString* ret = @"";
    NSMutableArray* arr_bundle = [[NSMutableArray alloc]init];
    NSMutableArray* arr_count = [[NSMutableArray alloc]init];
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select item_id,qty from bundle where product_item_id=%d;",item_id];
    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            int bitem_id = sqlite3_column_int(statement, 0);
            int bitem_qty = sqlite3_column_int(statement, 1);
            
            
            [arr_bundle addObject:[NSNumber numberWithInt:bitem_id] ];
            [arr_count addObject:[NSNumber numberWithInt:bitem_qty] ];
        }
        
        //        ret=[RAUtils arr2string:arr_bundle separator:@";" trim:true];
        
        sqlite3_finalize(statement);
    }
    //    if(ret==nil)
    //        ret=@"";
    *count=arr_count;
    return arr_bundle;
}
+(int) model_stockUom :(int)product_id db:(sqlite3*)db
{
    // get default sold qty, return -1 if model not found;
    int ret = -1;

    NSString *sqlQuery = [NSString stringWithFormat:@"select stockUom from product where product_id=%d;",product_id];// select stockUom from model where product_id=%d;

    sqlite3_stmt * statement;
    
    
    if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            ret = sqlite3_column_int(statement, 0);
        }
        
        
        sqlite3_finalize(statement);
    }
    
    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
#warning 做SQL操作时转义！！

+ (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];
        
    } failure:^(NSMutableDictionary *container,NSString *err_msg){
        
        DebugLog(@"query all state error: %@",err_msg);
        
//        NSMutableDictionary *stateDic = [NSMutableDictionary dictionaryWithCapacity:2];
//        [stateDic setValue:@"Other" forKey:@"value"];
//        [stateDic setValue:@"" forKey:@"value_id"];
//        [stateDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
//        
//        if (state_code && [@"" isEqualToString:state_code]) {
//            [stateDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
//        }
//
//        NSString *key = [NSString stringWithFormat:@"val_0"];
//        [container setValue:stateDic forKey:key];
        
    }] mutableCopy];
    
    // failure 可以不用了,一样的
    if (ret.allKeys.count == 0) {
        NSMutableDictionary *stateDic = [NSMutableDictionary dictionaryWithCapacity:2];
        [stateDic setValue:@"Other" forKey:@"value"];
        [stateDic setValue:@"" forKey:@"value_id"];
        [stateDic setValue:[NSNumber numberWithInt:0] forKey:@"check"];
        
        if (state_code && [@"" isEqualToString:state_code]) {
            [stateDic setValue:[NSNumber numberWithInt:1] forKey:@"check"];
        }
        
        NSString *key = [NSString stringWithFormat:@"val_0"];
        [ret setValue:stateDic forKey:key];
    }
    
    [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;
    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 = @"";
    }
    
    // 将字符全部为' '的字符串干掉
    int spaceCount = 0;
    for (int i = 0; i < text.length; i++) {
        if ([text characterAtIndex:i] == ' ') {
            spaceCount++;
        }
    }
    if (spaceCount == text.length) {
        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;
}

+ (NSString *)textFileName:(NSString *)name {
    NSString *path = [[NSBundle mainBundle] pathForResource:name ofType:nil];
    NSString *text = [NSString stringWithContentsOfFile:path encoding:NSUTF8StringEncoding error:nil];
    if (!text) {
        text = @"";
    }
    
    return text;
}

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

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

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

+ (NSString *)item_image:(NSString *)item_id db:(sqlite3 *)db {
    
    NSString* ret= nil;
    
    NSString *sqlQuery = nil;

    sqlQuery = [NSString stringWithFormat:@"select i.url from product m LEFT join model_image i on m.product_id = i.product_id  where m.item_id=%@ order by i.default_img desc, i._id asc limit 1;",item_id];// select i.url from model m
    
    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;

}


#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:@" %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ %@ and is_active=1 ",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 '%%%@%%') and is_active=1 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'%@') and is_active=1",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"];
            if (!countryCode) {
                countryCode = @"US";
            }
            
            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 = @"";
    }
    DebugLog(@"company");
    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 = @"";
    }
    DebugLog(@"addr");
    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 = @"";
    }
    DebugLog(@"country");
    country = [self translateSingleQuote:country];
    
    NSString *state = [params objectForKey:@"state"];
    if (!state) {
        state = @"";
    }
    DebugLog(@"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 = @"";
    }
    DebugLog(@"zip");
    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];
    DebugLog(@"contact_name");
    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 = @"";
    }
    DebugLog(@"PHONE");
    phone = [self translateSingleQuote:phone];
    
    NSString *fax = [params objectForKey:@"fax"];
    if (!fax) {
        fax = @"";
    }
    DebugLog(@"FAX");
    fax = [self translateSingleQuote:fax];
    
    NSString *email = [params objectForKey:@"email"];
    if (!email) {
        email = @"";
    }
    DebugLog(@"EMAIL:%@",email);
    email = [self translateSingleQuote:email];
    
    NSString *notes = [params objectForKey:@"contact_notes"];
    if (!notes) {
        notes = @"";
    }
    DebugLog(@"NOTE:%@",notes);
    notes = [self translateSingleQuote:notes];
    
    NSString *price = [params objectForKey:@"price_name"];
    if (price) {
        price = [self priceNameByPriceId:price];
    } else {
        price = @"";
    }
    DebugLog(@"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 (is_active,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,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'%%#%@#%%' and is_active = 1",category];
    

    NSString *sqlQuery = [NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from product m left join wishlist w on m.product_id=w.product_id where m.category like'%%#%@#%%' and m.is_active = 1 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,m.name asc";
        }
        
        // 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 item_id in (select DISTINCT item_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 item_id in (select DISTINCT item_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 product where %@ %@ %@ %@ %@ %@ and is_active = 1) 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:@"%@ %@ %@ %@ %@ %@ and is_active = 1",cateWhere,best_seller,alert,available_condition,qty,price];
        
    }
    
    count = [iSalesDB get_recordcount:db table:@"product" 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,m.name asc";
                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;
    where = [where stringByAppendingString:@" and m.is_active = 1"];
    sqlQuery=[NSString stringWithFormat:@"select m.name,m.description,m.product_id,w._id,m.closeout from product 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,m.name asc";
        }
        
        // 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 item_id in (select DISTINCT item_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 item_id in (select DISTINCT item_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 product where %@ %@ %@ %@ %@ %@ and is_active = 1) 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:@"%@ %@ %@ %@ %@ %@ and m.is_active = 1",cateWhere,best_seller,alert,available_condition,qty,price];
        
    }
    where = [where stringByReplacingOccurrencesOfString:@"m." withString:@""];
    
    count = [iSalesDB get_recordcount:db table:@"product" 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;
}

#pragma mark order detail

+ (NSString *)replaceHtml:(NSString *)html String:(NSString *)str0 withString:(NSString *)str1 {
    if (str1.length == 0) {
        str1 = @"&nbsp";
    }
    NSString *str = [html stringByReplacingOccurrencesOfString:str0 withString:str1];
    return str;
}
+(NSString*) timeFormat:(NSString*)datetime from:(NSString*) from to:(NSString*) to
{
    NSDateFormatter *fromformatter = [[NSDateFormatter alloc] init];
    [fromformatter setDateFormat:from];
    NSDate *date = [fromformatter dateFromString:datetime];
    
    NSDateFormatter * toformatter = [[NSDateFormatter alloc] init ];
    [toformatter setDateFormat:to];
    NSString * ret = [toformatter stringFromDate:date];
    
    return ret;
    
}

+ (NSString *)changeDateTime:(NSString *)dateTime Formate:(NSString *)formate withFormate:(NSString *)newFormate {
    
    // 把毫秒去掉
    if ([dateTime containsString:@"."]) {
        dateTime = [[dateTime componentsSeparatedByString:@"."] firstObject];
    }
    
    NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
    formatter.dateFormat = formate;
    
    //    [formatter setTimeZone:[NSTimeZone timeZoneForSecondsFromGMT:0]];;
    
    NSDate *date = [formatter dateFromString:dateTime];
    
    formatter.dateFormat = newFormate;
    return [formatter stringFromDate:date];
    
}

+ (NSString *)changeDateTimeFormate:(NSString *)dateTime {
    return [self changeDateTime:dateTime Formate:@"yyyy-MM-dd HH:mm:ss" withFormate:@"MM/dd/yyyy HH:mm:ss"];
    //    return [self timeFormat:dateTime from:@"YYYY-MM-DD HH:mm:ss" to:@"MM/DD/YYYY HH:mm:ss"];
}

+(NSData*) offline_orderdetail :(NSMutableDictionary *) params
{
    
    DebugLog(@"offline oderdetail params: %@",params);
    
    AppDelegate *appDelegate = (AppDelegate *)[UIApplication sharedApplication].delegate;
    
    int orderId = [params[@"orderId"] intValue];
    NSMutableDictionary* ret = [self dictionaryFileName:@"orderDetail.json"];
    
    // decrypt card number and card security code
//    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,o.receive_name,o.receive_contact,o.receive_ext,o.sender_name,o.sender_contact,o.sender_ext,o.shipping_billto_name,o.shipping_billto_contact,o.shipping_billto_ext,o.billing_name,o.billing_contact,o.billing_ext,o.returnto_name,o.returnto_contact,o.returnto_ext,o.handling_fee_value,o.paymentsAndCredits,o.total_price,o.customer_cid,o.credit_card_first_name,o.credit_card_last_name,o.credit_card_address1,o.credit_card_address2,o.credit_card_zipcode,o.credit_card_type,decrypt(o.credit_card_number),decrypt(o.credit_card_security_code),o.credit_card_expiration,o.credit_card_city,o.credit_card_state,o.shipNull,o.erpOrderStatus 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,receive_name,receive_contact,receive_ext,sender_name,sender_contact,sender_ext,shipping_billto_name,shipping_billto_contact,shipping_billto_ext,billing_name,billing_contact,billing_ext,returnto_name,returnto_contact,returnto_ext,handling_fee_value,paymentsAndCredits,total_price,customer_cid,credit_card_first_name,credit_card_last_name,credit_card_address1,credit_card_address2,credit_card_zipcode,credit_card_type,credit_card_number,credit_card_security_code,credit_card_expiration,credit_card_city,credit_card_state,ifnull(shipping,'ship_null') as shipNull,erpOrderStatus from offline_order where _id=%d) o left join offline_contact c on o.customer_cid=c.contact_id",orderId ];
    
    NSString *sql = [NSString stringWithFormat:@"select o._id,o.so_id,o.poNumber,o.create_time,o.status,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,o.receive_name,o.receive_contact,o.receive_ext,o.sender_name,o.sender_contact,o.sender_ext,o.shipping_billto_name,o.shipping_billto_contact,o.shipping_billto_ext,o.billing_name,o.billing_contact,o.billing_ext,o.returnto_name,o.returnto_contact,o.returnto_ext,o.handling_fee_value,o.paymentsAndCredits,o.total_price,o.customer_cid,o.credit_card_first_name,o.credit_card_last_name,o.credit_card_address1,o.credit_card_address2,o.credit_card_zipcode,o.credit_card_type,decrypt(o.credit_card_number),decrypt(o.credit_card_security_code),decrypt(o.credit_card_expiration_month),o.credit_card_city,o.credit_card_state,o.shipNull,o.erpOrderStatus,decrypt(o.credit_card_expiration_year),o.sign_picpath from (select _id,so_id,poNumber,create_time,status,customer_cid,customer_contact,logist,shipping,lift_gate_value,general_notes,internal_notes,paymentType,lift_gate,receive_name,receive_contact,receive_ext,sender_name,sender_contact,sender_ext,shipping_billto_name,shipping_billto_contact,shipping_billto_ext,billing_name,billing_contact,billing_ext,returnto_name,returnto_contact,returnto_ext,handling_fee_value,paymentsAndCredits,total_price,customer_cid,credit_card_first_name,credit_card_last_name,credit_card_address1,credit_card_address2,credit_card_zipcode,credit_card_type,credit_card_number,credit_card_security_code,credit_card_expiration_month,credit_card_city,credit_card_state,ifnull(shipping,'ship_null') as shipNull,erpOrderStatus,credit_card_expiration_year,sign_picpath 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;
    
    
    
    NSString *customerID = nil; // 在查询结果中还需要调用offline_contactinfo，需要使用，其中要打开db 就会lock，故而将其置于外面，在close db后在调用
    NSString *nssoid = nil;
    NSString* orderinfo = nil;
    NSString *moreInfo = nil;
    double handlingFee = 0;
    double payments_and_credist = 0;
    double totalPrice = 0;
    double shippingFee = 0;
    double lift_gate = 0;
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK)
    {
        
        
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            
            int order_id = sqlite3_column_int(statement, 0);
            
            NSString *sign_url = [self textAtColumn:51 statement:statement];
            ret[@"sign_url"] = sign_url;
            
            char *soid = (char*)sqlite3_column_text(statement, 1);
            if(soid==nil)
                soid= "";
            nssoid= [[NSString alloc]initWithUTF8String:soid];
            // so#
            ret[@"so#"] = nssoid;
            
            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];
            nscreate_time = [[self changeDateTimeFormate:nscreate_time] stringByAppendingString:@" PST"];// 与在线统一
            
            int status = sqlite3_column_int(statement, 4);
            int erpStatus = sqlite3_column_int(statement, 49);
            NSString* nsstatus = [self orderStatus:status erpStatus:erpStatus];
            
            // status
            if (status > 1 && status != 3) {
                status = erpStatus;
            } else if (status == 3) {
                status = 15;
            }
            ret[@"orderStatus"] = [NSNumber numberWithInteger:status];
            ret[@"order_status"] = nsstatus;
            
            
            char *company_name = (char*)sqlite3_column_text(statement, 5);
            if(company_name==nil)
                company_name= "";
            NSString *nscompany_name= [[NSString alloc]initWithUTF8String:company_name];
            // company name
            ret[@"company_name"] = nscompany_name;
            
            char *customer_contact = (char*)sqlite3_column_text(statement, 6);
            if(customer_contact==nil)
                customer_contact= "";
            NSString *nscustomer_contact= [[NSString alloc]initWithUTF8String:customer_contact];
            
            
            char *addr_1 = (char*)sqlite3_column_text(statement, 7);
            if(addr_1==nil)
                addr_1="";
            NSString *nsaddr_1= [[NSString alloc]initWithUTF8String:addr_1];
            
            char *addr_2 = (char*)sqlite3_column_text(statement, 8);
            if(addr_2==nil)
                addr_2="";
            NSString *nsaddr_2= [[NSString alloc]initWithUTF8String:addr_2];
            
            
            char *addr_3 = (char*)sqlite3_column_text(statement, 9);
            if(addr_3==nil)
                addr_3="";
            NSString *nsaddr_3= [[NSString alloc]initWithUTF8String:addr_3];
            
            
            char *addr_4 = (char*)sqlite3_column_text(statement, 10);
            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, 11);
            if(logist==nil)
                logist= "";
            NSString *nslogist=[[NSString alloc]initWithUTF8String:logist];
            
            NSString *shipping = [NSString stringWithFormat:@"$%.2f",sqlite3_column_double(statement, 12)];
            if ([[self textAtColumn:48 statement:statement] isEqualToString:@"ship_null"]) {
                shipping = @"Shipping To Be Quoted";
            } else {
                shippingFee = sqlite3_column_double(statement, 12);
            }
            
            // Shipping
            ret[@"Shipping"] = shipping;
            
            int have_lift_gate = sqlite3_column_int(statement, 17);
            lift_gate = sqlite3_column_double(statement, 13);
            // Liftgate Fee(No loading dock)
            if (!have_lift_gate) {
                lift_gate = 0;
            }
            ret[@"Liftgate Fee(No loading dock)"] = [NSString stringWithFormat:@"$%.2f",lift_gate];
            
            char *general_notes = (char*)sqlite3_column_text(statement, 14);
            if(general_notes==nil)
                general_notes= "";
            NSString *nsgeneral_notes=[[NSString alloc]initWithUTF8String:general_notes];
            
            char *internal_notes = (char*)sqlite3_column_text(statement, 15);
            if(internal_notes==nil)
                internal_notes= "";
            NSString *nsinternal_notes=[[NSString alloc]initWithUTF8String:internal_notes];
            
            char *payment_type = (char*)sqlite3_column_text(statement, 16);
            if(payment_type==nil)
                payment_type= "";
            NSString *nspayment_type=[[NSString alloc]initWithUTF8String:payment_type];
            
            
            
            // order info
            orderinfo = [self textFileName:@"order_info.html"];
            
            
            orderinfo = [self replaceHtml:orderinfo String:@"SO_or_nbsp" withString:nssoid];
            orderinfo = [self replaceHtml:orderinfo String:@"PO_or_nbsp" withString:nspoNumber];
            orderinfo = [self replaceHtml:orderinfo String:@"OrderDate_or_nbsp" withString:nscreate_time];
            orderinfo = [self replaceHtml:orderinfo String:@"OrderStatus_or_nbsp" withString:nsstatus];
            
            orderinfo = [self replaceHtml:orderinfo String:@"CustomerInfo_or_nbsp" withString:nscompany_name];
            orderinfo = [self replaceHtml:orderinfo String:@"CustomerName_or_nbsp" withString:nscustomer_contact];
            orderinfo = [self replaceHtml:orderinfo String:@"CustomerAddress_or_nbsp" withString:customer_address];
            orderinfo = [self replaceHtml:orderinfo String:@"ShippingMethod_or_nbsp" withString:nslogist];
            orderinfo = [self replaceHtml:orderinfo String:@"Shipping_or_nbsp" withString:shipping];
            orderinfo = [self replaceHtml:orderinfo String:@"LiftgateFee_or_nbsp" withString:[NSString stringWithFormat:@"%.2f",lift_gate]];
            orderinfo = [self replaceHtml:orderinfo String:@"GeneralNotes_or_nbsp" withString:nsgeneral_notes];
            orderinfo = [self replaceHtml:orderinfo String:@"InternalNotes_or_nbsp" withString:nsinternal_notes];
            
            NSString *payment = nil;
            if([nspayment_type isEqualToString:@"Credit Card"])
            {
                payment = [self textFileName:@"creditcardpayment.html"];
                
                NSString *card_first_name = [self textAtColumn:37 statement:statement];
                NSString *card_last_name = [self textAtColumn:38 statement:statement];
                NSString *card_addr1 = [self textAtColumn:39 statement:statement];
                NSString *card_addr2 = [self textAtColumn:40 statement:statement];
                NSString *card_zipcode = [self textAtColumn:41 statement:statement];
                NSString *card_type = [self textAtColumn:42 statement:statement];
                if (card_type.length > 0) { // 显示星号
                    card_type = @"****";
                }
                NSString *card_number = [self textAtColumn:43 statement:statement]; // 只显示最后四位
                if (card_number.length > 0 && card_number.length > 4) {
                    for (int i = 0; i < card_number.length - 4; i++) {
                        card_number = [card_number stringByReplacingCharactersInRange:NSMakeRange(i, 1) withString:@"*"];
                    }
                } else {
                    card_number = @"";
                }
                NSString *card_security_code = [self textAtColumn:44 statement:statement];
                NSString *card_expiration_month = [self textAtColumn:45 statement:statement];
                NSString *card_expiration_year = [self textAtColumn:50 statement:statement];
                NSString *card_expiration = [NSString stringWithFormat:@"%@/%@",card_expiration_month,card_expiration_year];
                if (card_expiration_month.length && card_expiration_year.length) { // 显示星号
                    card_expiration = @"****";
                }
                
                
                NSString *card_city = [self textAtColumn:46 statement:statement];
                NSString *card_state = [self textAtColumn:47 statement:statement];
                
                payment = [self replaceHtml:payment String:@"CardNumber_or_nbsp" withString:card_number];
                payment = [self replaceHtml:payment String:@"CardType_or_nbsp" withString:card_type];
                payment = [self replaceHtml:payment String:@"CardExpireDate_or_nbsp" withString:card_expiration];
                payment = [self replaceHtml:payment String:@"BillingFirstName_or_nbsp" withString:card_first_name];
                payment = [self replaceHtml:payment String:@"BillingLastName_or_nbsp" withString:card_last_name];
                payment = [self replaceHtml:payment String:@"BillingAddr1_or_nbsp" withString:card_addr1];
                payment = [self replaceHtml:payment String:@"BillingAddr2_or_nbsp" withString:card_addr2];
                payment = [self replaceHtml:payment String:@"BillingCity_or_nbsp" withString:card_city];
                payment = [self replaceHtml:payment String:@"BillingState_or_nbsp" withString:card_state];
                payment = [self replaceHtml:payment String:@"BillingZipCode_or_nbsp" withString:card_zipcode];
                
                
            }
            else
            {
                payment=[self textFileName:@"normalpayment.html"];
                payment = [self replaceHtml:payment String:@"PAYMENTTYPE_PLACEHOLDER" withString:nspayment_type];
            }
            orderinfo=[self replaceHtml:orderinfo String:@"Payment_info_or_nbsp" withString:payment];
            
            ret[@"result"]= [NSNumber numberWithInt:2];
            
            // more info
           moreInfo = [self textFileName:@"more_info.html"];
            
            /*****ship to******/
            // ShipToCompany_or_&nbsp
            NSString *shipToCompany = [self textAtColumn:18 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipToCompany_or_" withString:shipToCompany];
            NSString *shipToName = [self textAtColumn:19 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipToName_or_" withString:shipToName];
            NSString *shipToAddr = [self textAtColumn:20 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipToAddress_or_" withString:shipToAddr];
            
            /*****ship from******/
            // ShipFromCompany_or_&nbsp
            NSString *shipFromCompany = [self textAtColumn:21 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipFromCompany_or_" withString:shipFromCompany];
            NSString *shipFromName = [self textAtColumn:22 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipFromName_or_" withString:shipFromName];
            NSString *shipFromAddr = [self textAtColumn:23 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ShipFromAddress_or_" withString:shipFromAddr];
            
            /*****freight to******/
            // FreightBillToCompany_or_&nbsp
            NSString *freightBillToCompany = [self textAtColumn:24 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"FreightBillToCompany_or_" withString:freightBillToCompany];
            NSString *freightBillToName = [self textAtColumn:25 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"FreightBillToName_or_" withString:freightBillToName];
            NSString *freightBillToAddr = [self textAtColumn:26 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"FreightBillToAddress_or_" withString:freightBillToAddr];
            
            /*****merchandise to******/
            // MerchandiseBillToCompany_or_&nbsp
            NSString *merchandiseBillToCompany = [self textAtColumn:27 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"MerchandiseBillToCompany_or_" withString:merchandiseBillToCompany];
            NSString *merchandiseBillToName = [self textAtColumn:28 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"MerchandiseBillToName_or_" withString:merchandiseBillToName];
            NSString *merchandiseBillToAddr = [self textAtColumn:29 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"MerchandiseBillToAddress_or_" withString:merchandiseBillToAddr];
            
            /*****return to******/
            // ReturnToCompany_or_&nbsp
            NSString *returnToCompany = [self textAtColumn:30 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ReturnToCompany_or_" withString:returnToCompany];
            NSString *returnToName = [self textAtColumn:31 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ReturnToName_or_" withString:returnToName];
            NSString *returnToAddr = [self textAtColumn:32 statement:statement];
            moreInfo = [self replaceHtml:moreInfo String:@"ReturnToAddress_or_" withString:returnToAddr];
            //
            
            DebugLog(@"more info : %@",moreInfo);
            
            // handling fee
            handlingFee = sqlite3_column_double(statement, 33);
            ret[@"Handling Fee"] = [NSString stringWithFormat:@"$%.2f",handlingFee];
            
            // customer info
            customerID = [self textAtColumn:36 statement:statement];
            
            // mode
            ret[@"mode"] = appDelegate.mode;
            
            // model_count
            ret[@"model_count"] = @(0);
            
        }
        
        
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    [iSalesDB close_db:db];
    
    NSDictionary *contactInfo = [self offline_contactinfo:[@{@"contactId":customerID} mutableCopy]];
    ret[@"customerInfo"] = contactInfo;
    
    // models
    if (nssoid) {
        
        __block double TotalCuft = 0;
        __block double TotalWeight = 0;
        __block int TotalCarton = 0;

        __block double allItemPrice = 0;
        
         NSString *modelSql = [NSString stringWithFormat:@"select c.product_id,c.price,c.discount,c.item_count,c.line_note,m.name,m.description,c.item_id,m.stockUom,c._id from (select _id,product_id,decrypt(str_price) as price,discount,item_count,line_note,item_id from offline_cart where so_no='%@') c left join product m on c.product_id=m.product_id",nssoid];
        sqlite3 *db1 = [iSalesDB get_db];
        [iSalesDB jk_query:modelSql db:db1 close:YES completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
            
            NSMutableDictionary* itemjson = [[NSMutableDictionary alloc] init];
            
            
            
            
            int product_id = sqlite3_column_int(stmt, 0);
            
            char *str_price = (char*)sqlite3_column_text(stmt, 1);
            
            int item_id = sqlite3_column_int(stmt, 7);
            
            NSString* Price=nil;
            if(str_price==nil)
            {
                NSNumber* price = [self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_id) db:db1];
                if(price==nil)
                    Price=@"No Price.";
                else
                    Price=[NSString stringWithFormat:@"%.2f",price.floatValue];
            }
            else
            {
                
                Price=[NSString stringWithFormat:@"%.2f",[[[NSString alloc]initWithUTF8String:str_price] floatValue]];
            }
            
            
            double discount = sqlite3_column_double(stmt, 2);
            int item_count = sqlite3_column_int(stmt, 3);
            
            char *line_note = (char*)sqlite3_column_text(stmt, 4);
            NSString *nsline_note=nil;
            if(line_note!=nil)
                nsline_note= [[NSString alloc]initWithUTF8String:line_note];
            
            
            char *name = (char*)sqlite3_column_text(stmt, 5);
            NSString *nsname=nil;
            if(name!=nil)
                nsname= [[NSString alloc]initWithUTF8String:name];
            
            char *description = (char*)sqlite3_column_text(stmt, 6);
            NSString *nsdescription=nil;
            if(description!=nil)
                nsdescription= [[NSString alloc]initWithUTF8String:description];
            
            
            
//            int stockUom = sqlite3_column_int(stmt, 8);
//            int _id = sqlite3_column_int(stmt, 9);
            
            
            NSDictionary* bsubtotaljson=[self model_subtotal:item_id count:item_count db:db1 compute_part:true];
            double cuft=[bsubtotaljson[@"cuft"] doubleValue];
            double weight=[bsubtotaljson[@"weight"] doubleValue];
            int carton=[bsubtotaljson[@"carton"] intValue];
            
            TotalCuft += cuft;
            TotalWeight += weight;
            TotalCarton += carton;
            
            itemjson[@"combine"]=[self model_bundle:item_id db:db1 compute_part:false];
            
//            itemjson[@"img"]= [self model_category_img:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:item_id]] model_name:nil db:db1];
            itemjson[@"img"] = [self item_image:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:item_id]] db:db1];
            
            itemjson[@"Item Number"]=[NSString stringWithFormat:@"%@\n%@",nsname,nsdescription ];
            itemjson[@"note"]=nsline_note;
            itemjson[@"origin_price"] = Price;
//            Price = [NSString stringWithFormat:@"%f",[Price doubleValue] * (1.0 - discount / 100.0)];
            itemjson[@"The unit price"] = [NSString stringWithFormat:@"%f",[Price doubleValue] * (1.0 - discount / 100.0)];
            
            itemjson[@"order_item_status"] = @""; // 暂时不处理
            itemjson[@"product_id"] = [NSString stringWithFormat:@"%d",product_id];
            itemjson[@"QTY"] = [NSString stringWithFormat:@"%d",item_count];
            itemjson[@"discount"] = [NSNumber numberWithDouble:discount];
            
            
            double subTotal = item_count * [Price doubleValue] * (1.0 - discount / 100.0);
            if(itemjson[@"combine"] != nil)
            {
                //  int citem=0;
                
                int bcount=[[itemjson[@"combine"] valueForKey:@"count"] intValue];
                for(int bc=0;bc<bcount;bc++)
                {
                    NSDictionary * bitem = [itemjson[@"combine"] objectForKey:[NSString stringWithFormat:@"item_%d",bc]];
                    int modulus= [[bitem valueForKey:@"modulus"] intValue];
                    double uprice= [[bitem valueForKey:@"unit_price"]doubleValue];
                    subTotal += uprice * modulus * item_count;
                }
            }

            
            itemjson[@"Subtotal"] = [NSString stringWithFormat:@"%.2f",subTotal];
            NSLog(@"item unite price: %@ \n discount: %f item count: %d subTotal: %f",Price,discount,item_count,subTotal);
           
            
            ret[[NSString stringWithFormat:@"item_%ld",(*count)++]]=itemjson;
            ret[@"model_count"] = [NSNumber numberWithInteger:*count];
            
            allItemPrice += subTotal;
            
        }];
        
        orderinfo = [self replaceHtml:orderinfo String:@"TotalCuft_or_nbsp" withString:[NSString stringWithFormat:@"%.2f ft³",TotalCuft]];
        orderinfo = [self replaceHtml:orderinfo String:@"TotalWeight_or_nbsp" withString:[NSString stringWithFormat:@"%.2f lbs",TotalWeight]];
        orderinfo = [self replaceHtml:orderinfo String:@"TotalCarton_or_nbsp" withString:[NSString stringWithFormat:@"%d",TotalCarton]];
        
        // payments/Credits
//        payments_and_credist = sqlite3_column_double(statement, 34);
        payments_and_credist = allItemPrice;
        ret[@"Payments/Credits"] = [NSString stringWithFormat:@"$%.2f",payments_and_credist];
        
//        // total
//        totalPrice = sqlite3_column_double(statement, 35);
//        ret[@"Total"] = [NSString stringWithFormat:@"$%.2f",totalPrice];

        
        
    } else {
        orderinfo = [self replaceHtml:orderinfo String:@"TotalCuft_or_nbsp" withString:@""];
        orderinfo = [self replaceHtml:orderinfo String:@"TotalWeight_or_nbsp" withString:@""];
        orderinfo = [self replaceHtml:orderinfo String:@"TotalCarton_or_nbsp" withString:@""];
        
        // payments/Credits
        payments_and_credist = 0;
        ret[@"Payments/Credits"] = [NSString stringWithFormat:@"$%.2f",payments_and_credist];


    }
    // total
    totalPrice = payments_and_credist + lift_gate + handlingFee + shippingFee;
    ret[@"Total"] = [NSString stringWithFormat:@"$%.2f",totalPrice];
    
    ret[@"order_info"]= orderinfo;
    ret[@"more_order_info"] = moreInfo;
    
    
    return [RAUtils dict2data:ret];
    
}

#pragma mark order list

+ (NSString *)orderStatus:(int)status erpStatus:(int)erpStatus{
    NSString *nsstatus = @"";
    switch (status) {
        case 0:
        {
            nsstatus=@"Temp Order";
            break;
        }
        case 1:
        {
            nsstatus=@"Saved Order";
            break;
        }
        case 2: {
            nsstatus = [self orderStatus:erpStatus erpStatus:-1];
            break;
        }
        case 3:
        case 15:
        {
            nsstatus=@"Cancelled";
            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;
        }
            
            
            
        default:
            break;
    }
    
    return nsstatus;
    
}

+ (double) orderTotalPrice:(NSString *)so_id db:(sqlite3 *)db1 close:(BOOL)close{
    
    double total = 0;
    
    __block double payments_and_credist = 0;
    __block double allItemPrice = 0;
    
//    sqlite3 *db1 = [iSalesDB get_db];
    
    if (so_id) {
        NSString *modelSql = [NSString stringWithFormat:@"select c.product_id,c.price,c.discount,c.item_count,c.line_note,m.name,m.description,c.item_id,m.stockUom,c._id from (select _id,product_id,decrypt(str_price) as price,discount,item_count,line_note,item_id from offline_cart where so_no='%@') c left join product m on c.product_id=m.product_id",so_id];
        
        
        AppDelegate *appDelegate = (AppDelegate *)[UIApplication sharedApplication].delegate;
        
        [iSalesDB jk_query:modelSql db:db1 close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
            
            NSMutableDictionary* itemjson = [[NSMutableDictionary alloc] init];
            
            int product_id = sqlite3_column_int(stmt, 0);
            
            char *str_price = (char*)sqlite3_column_text(stmt, 1);
            
            int item_id = sqlite3_column_int(stmt, 7);
            
            NSString* Price=nil;
            if(str_price==nil)
            {
                NSNumber* price = [self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_id) db:db1];
                if(price==nil)
                    Price=@"No Price.";
                else
                    Price=[NSString stringWithFormat:@"%.2f",price.floatValue];
            }
            else
            {
                
                Price=[NSString stringWithFormat:@"%.2f",[[[NSString alloc]initWithUTF8String:str_price] floatValue]];
            }
            
            
            int discount = sqlite3_column_int(stmt, 2);
            int item_count = sqlite3_column_int(stmt, 3);
            
            
            itemjson[@"combine"]=[self model_bundle:item_id db:db1 compute_part:false];
            
            itemjson[@"The unit price"]=Price;
            
            itemjson[@"product_id"] = [NSString stringWithFormat:@"%d",product_id];
            itemjson[@"QTY"] = [NSString stringWithFormat:@"%d",item_count];
            
            double subTotal = item_count * [Price doubleValue] * (1.0 - discount / 100.0);
            if(itemjson[@"combine"] != nil)
            {
                //  int citem=0;
                
                int bcount=[[itemjson[@"combine"] valueForKey:@"count"] intValue];
                for(int bc=0;bc<bcount;bc++)
                {
                    NSDictionary * bitem = [itemjson[@"combine"] objectForKey:[NSString stringWithFormat:@"item_%d",bc]];
                    int modulus= [[bitem valueForKey:@"modulus"] intValue];
                    double uprice= [[bitem valueForKey:@"unit_price"]doubleValue];
                    subTotal += uprice * modulus * item_count;
                }
            }
            
            
            itemjson[@"Subtotal"] = [NSString stringWithFormat:@"%.2f",subTotal];
            
            
            allItemPrice += subTotal;
            
        }];
        
        
        payments_and_credist = allItemPrice;

    } else {
   
        
        // payments/Credits
        payments_and_credist = 0;
        
        
    }
    
    // lift_gate handlingFee shippingFee
    __block double lift_gate = 0;
    __block double handlingFee = 0;
    __block double shippingFee = 0;
    NSString *orderSql = [NSString stringWithFormat:@"select lift_gate,lift_gate_value,handling_fee_value,shipping from offline_order where so_id = '%@';",so_id];
    
    [iSalesDB jk_query:orderSql db:db1 close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        int have_lift_gate = sqlite3_column_int(stmt, 0);
        if (have_lift_gate) {
            lift_gate = sqlite3_column_double(stmt, 1);
        }
        
        handlingFee = sqlite3_column_double(stmt, 2);
        shippingFee = sqlite3_column_double(stmt, 3);
        
    }];
    
    // total
    total = payments_and_credist + lift_gate + handlingFee + shippingFee;


    if (close) {
        [iSalesDB close_db:db1];
    }
    return total;
}

+(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"];
    keyword = [self translateSingleQuote:keyword]; // 转义单引号
    NSString *orderStatus = [params valueForKey:@"orderStatus"];
    
    
    NSString* where=@"1 = 1";
    if(keyword.length>0)
        where=[where stringByAppendingString:[NSString stringWithFormat:@" and (lower(o.so_id) like '%%%@%%' or lower(o.sales_rep) like '%%%@%%' or lower(decrypt(c.company_name)) like '%%%@%%' or lower(o.create_by) like '%%%@%%')",keyword.lowercaseString,keyword.lowercaseString,keyword.lowercaseString,keyword.lowercaseString]];
    
    if (orderStatus.length > 0) {
        NSArray *order_status_array = [orderStatus componentsSeparatedByString:@","];
        if (order_status_array.count == 1) {
            int status_value = [[order_status_array firstObject] integerValue];
            if (status_value <= 1 || status_value == 3) {
                where = [where stringByAppendingString:[NSString stringWithFormat:@" and o.status = %d",status_value]];
            } else {
                where = [where stringByAppendingString:[NSString stringWithFormat:@" and o.erpOrderStatus = %d",status_value]];
            }
            
        } else if (order_status_array.count > 1) {
            
            for (int i = 0; i < order_status_array.count;i++) {
                NSString *status = order_status_array[i];
                NSString *condition = @" or";
                if (i == 0) {
                    condition = @" and (";
                }
                //                where = [where stringByAppendingString:[NSString stringWithFormat:@"%@ o.status = %@",condition,status]];
                
                int status_value = [status integerValue];
                if (status_value <= 1 || status_value == 3) {
                    where = [where stringByAppendingString:[NSString stringWithFormat:@"%@ o.status = %d",condition,status_value]];
                } else {
                    where = [where stringByAppendingString:[NSString stringWithFormat:@" %@ o.erpOrderStatus = %d",condition,status_value]];
                }
                
                
            }
            
            where = [where stringByAppendingString:@" )"];
        }
    }
    
    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,o.customer_contact,o.erpOrderStatus from offline_order o left join offline_contact c on o.customer_cid=c.contact_id where %@ order by o.create_time desc limit %d offset %d" ,where, limit, offset];
    
    DebugLog(@"order list sql: %@",sqlQuery);
    
    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);
            int erpStatus = sqlite3_column_double(statement, 9);
            
            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];
            DebugLog(@"time: %@",nscreate_time);
            nscreate_time = [self changeDateTimeFormate:nscreate_time]; // 与在线统一
//            double total_price = sqlite3_column_double(statement, 7);
            double total_price = [self orderTotalPrice:[NSString stringWithUTF8String:soid]db:db close:NO];
            
            NSString *customer_contact = [self textAtColumn:8 statement:statement];
            
            NSString* nsstatus = [self orderStatus:status erpStatus:erpStatus];
            
            //            ": "JH",
            //            "": "$8307.00",
            //            "": "MOB1608050001",
            //            "": "ArpithaT",
            //            "": "1st Stage Property Transformations",
            //            "": "JANICE SUTTON",
            //            "": 2255,
            //            "": "08/02/2016 09:49:18",
            //            "": 1,
            //            "": "Saved Order"
            //            "": "1470384050483",
            //            "model_count": "6 / 28"
            
            item[@"sales_rep"]= nssales_rep;
            item[@"price"]= [NSString stringWithFormat:@"$%.2f",total_price];
            item[@"so#"]= nssoid;
            item[@"create_by"]= nscreate_by;
            item[@"customer_name"]= nscompany_name;
            item[@"customer_contact"] = customer_contact;
            item[@"order_id"]= [NSString stringWithFormat:@"%d",order_id];
            item[@"purchase_time"]= nscreate_time;
            item[@"orderStatus"]= [NSString stringWithFormat:@"%d",status];
            item[@"order_status"]= nsstatus;
            item[@"order_code"]= nssoid;//[NSString stringWithFormat:@"%d",order_id];
            // item[@"model_count"]
            
            
            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];
        ret[@"can_see_price"] = [NSNumber numberWithBool:YES];
        ret[@"time_zone"] = @"PST";
        
        
        sqlite3_finalize(statement);
        
    }
    
    int total_count = [iSalesDB get_recordcount:db table:@"offline_order o left join offline_contact c on o.customer_cid=c.contact_id" where:where];
    
    ret[@"total_count"] = [NSNumber numberWithInteger:total_count];
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
}

#pragma mark  update gnotes
+(NSData*) offline_updategnotes :(NSMutableDictionary *) params
{
    DebugLog(@"params: %@",params);
//    comments = Meyoyoyoyoyoyoy;
//    orderCode = MOB1608110001;
//    password = 123456;
//    user = EvanK;
    
    NSString *sql = [NSString stringWithFormat:@"update offline_order set general_notes = '%@' where so_id = '%@';",[self valueInParams:params key:@"comments"],[self valueInParams:params key:@"orderCode"]];
    int ret = [iSalesDB execSql:sql];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];
}

#pragma mark move to wishlist

+(NSData*) offline_movecart2wish :(NSMutableDictionary *) params
{

    
    NSString *_id = [self valueInParams:params key:@"cartItemId"];
   
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];

    NSString *sql = [NSString stringWithFormat:@"select product_id,item_count,item_id from offline_cart where so_no = '%@' and _id = %@;",orderCode,_id];
    sqlite3 *db = [iSalesDB get_db];
    
    __block NSString *product_id = nil;
    __block int item_count = 0;
    __block NSString *item_id = nil;
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        product_id = [self textAtColumn:0 statement:stmt];
        item_count = sqlite3_column_int(stmt, 1);
        item_id = [self textAtColumn:2 statement:stmt];
    }];
    
    //
//    NSString *contactIdSql = [NSString stringWithFormat:@"select customer_cid from offline_order where so_id = '%@';",orderCode];
//    __block NSString *contact_id = nil;
//    [iSalesDB jk_query:contactIdSql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
//        contact_id = [self textAtColumn:0 statement:stmt];
//    }];
    
    NSString *insertIntoWishlistSql = [NSString stringWithFormat:@"insert into wishlist (product_id,item_id,qty) values (%@,%@,%d);",product_id,item_id,item_count];
    int ret = [iSalesDB execSql:insertIntoWishlistSql db:db];
    
    int wish_count = [iSalesDB get_recordcount:db table:@"wishlist" where:nil];
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    [dic setValue:[NSNumber numberWithInteger:wish_count] forKey:@"wish_count"];
    
    // 删除
    NSString *deleteSql = [NSString stringWithFormat:@"delete from offline_cart where _id = %@;",_id];
    [iSalesDB execSql:deleteSql db:db];
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:dic];
}

#pragma mark cart delete

+(NSData*) offline_cartremove :(NSMutableDictionary *) params
{
//    cartItemId = 548;
//    orderCode = MOB1608110001;
    
    NSString *_id = [self valueInParams:params key:@"cartItemId"];
    NSString *orderCode = [self valueInParams:params key:@"MOB1608110001"];
    
    NSString *sql = [NSString stringWithFormat:@"delete from offline_cart where _id = %@ and so_no = '%@';",_id,orderCode];
    int ret = [iSalesDB execSql:sql];
    
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];
}

#pragma mark set price

+(NSData*) offline_cartsetprice :(NSMutableDictionary *) params
{
    DebugLog(@"cart set price params: %@",params);
//    "cartitem_id" = 1;
//    discount = "0.000000";
//    "item_note" = "";
//     price = "269.000000";
    
    NSString *_id = [self valueInParams:params key:@"cartitem_id"];
    NSString *notes = [self valueInParams:params key:@"item_note"];
    NSString *discount = [self valueInParams:params key:@"discount"];
    NSString *price = [self valueInParams:params key:@"price"];
    price = [self translateSingleQuote:[AESCrypt fastencrypt:price]];
    
    NSString *sql = [NSString stringWithFormat:@"update offline_cart set str_price = '%@',discount = %f where _id = %@",price,discount.doubleValue,_id];
    
    int ret = [iSalesDB execSql:sql];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];
    
}

#pragma mark set line notes

+(NSData*) offline_cartsetlnotes :(NSMutableDictionary *) params
{
    NSString *_id = [self valueInParams:params key:@"cartitem_id"];
    NSString *notes = [self valueInParams:params key:@"notes"];
    notes = [self translateSingleQuote:notes];
    
    NSString *sql = [NSString stringWithFormat:@"update offline_cart set line_note = '%@' where _id = %@",notes,_id];
    
    int ret = [iSalesDB execSql:sql];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];
}

#pragma mark set qty

+(NSData*) offline_cartsetqty :(NSMutableDictionary *) params
{
    NSString *_id = [self valueInParams:params key:@"cartitem_id"];
    int item_count = [params[@"inputInt"] integerValue];
    
    NSString *sql = [NSString stringWithFormat:@"update offline_cart set item_count = %d where _id = %@;",item_count,_id];
    
    int ret = [iSalesDB execSql:sql];
    
    __block int item_id = 0;
    
    [iSalesDB jk_query:[NSString stringWithFormat:@"select item_id from offline_cart where _id = %@;",_id] completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        item_id = sqlite3_column_int(stmt, 0);
    }];
    
    sqlite3 *db = [iSalesDB get_db];
    
    NSDictionary* bsubtotaljson=[self model_subtotal:item_id count:item_count db:db compute_part:true];

    [iSalesDB close_db:db];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    [dic setValue:bsubtotaljson[@"cuft"] forKey:@"cuft"];
    [dic setValue:bsubtotaljson[@"weight"] forKey:@"weight"];
    [dic setValue:bsubtotaljson[@"carton"] forKey:@"carton"];
    
    return [RAUtils dict2data:dic];

}

#pragma mark place order

+ (NSDictionary *)infoName:(NSString *)name aname:(NSString *)aname control:(NSString *)control keyboard:(NSString *)keyboard value:(id)value {
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    if (aname) {
        [dic setValue:aname forKey:@"aname"];
    }
    if (control) {
        [dic setValue:control forKey:@"control"];
    }
    if (keyboard) {
        [dic setValue:keyboard forKey:@"keyboard"];
    }
    if (name) {
        [dic setValue:name forKey:@"name"];
    }
    if (value) {
        [dic setValue:value forKey:@"value"];
    }
    
    return dic;
}

+ (NSDictionary *)customerDic:(NSDictionary *) params db:(sqlite3 *)db {
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    __block NSMutableDictionary *customer_dic = [NSMutableDictionary dictionary];
    
    NSString *customerSql = [NSString stringWithFormat:@"select c.contact_id,c.img_0,c.img_1,c.img_2,decrypt(c.company_name),decrypt(c.addr_1),c.addr_2,c.addr_3,c.addr_4,c.first_name,c.last_name,c.email,decrypt(c.phone),c.fax,c.zipcode,c.city,c.state,c.country from offline_contact as c join offline_order as o on c.contact_id = o.customer_cid where o.so_id = '%@';",orderCode];
    [iSalesDB jk_query:customerSql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *contact_id = [self textAtColumn:0 statement:stmt];
        NSString *img0 = [self textAtColumn:1 statement:stmt];
        NSString *img1 = [self textAtColumn:2 statement:stmt];
        NSString *img2 = [self textAtColumn:3 statement:stmt];
        NSString *company_name = [self textAtColumn:4 statement:stmt];
        NSString *addr1 = [self textAtColumn:5 statement:stmt];
        NSString *addr2 = [self textAtColumn:6 statement:stmt];
        NSString *addr3 = [self textAtColumn:7 statement:stmt];
        NSString *addr4 = [self textAtColumn:8 statement:stmt];
        NSString *first_name = [self textAtColumn:9 statement:stmt];
        NSString *last_name = [self textAtColumn:10 statement:stmt];
        NSString *email = [self textAtColumn:11 statement:stmt];
        NSString *phone = [self textAtColumn:12 statement:stmt];
        NSString *fax = [self textAtColumn:13 statement:stmt];
        NSString *zipcode = [self textAtColumn:14 statement:stmt];
        NSString *city = [self textAtColumn:15 statement:stmt];
        NSString *state = [self textAtColumn:16 statement:stmt];
        NSString *country = [self textAtColumn:17 statement:stmt];
        
        NSString *name = [first_name stringByAppendingFormat:@" %@",last_name];
        
        // contact id
        NSMutableDictionary *contact_id_dic = [NSMutableDictionary dictionary];
        [contact_id_dic setValue:@"Contact ID" forKey:@"aname"];
        [contact_id_dic setValue:@"text" forKey:@"control"];
        [contact_id_dic setValue:@"text" forKey:@"keyboard"];
        [contact_id_dic setValue:@"customer_cid" forKey:@"name"];
        [contact_id_dic setValue:@"true" forKey:@"required"];
        [contact_id_dic setValue:contact_id forKey:@"value"];
        
        [customer_dic setValue:contact_id_dic forKey:@"item_0"];
        
        // business card
        NSMutableDictionary *business_card_dic = [NSMutableDictionary dictionary];
        [business_card_dic setValue:@"Business Card" forKey:@"aname"];
        [business_card_dic setValue:@"img" forKey:@"control"];
        [business_card_dic setValue:@"1" forKey:@"disable"];
        [business_card_dic setValue:img0 forKey:@"img_url_0"];
        [business_card_dic setValue:img1 forKey:@"img_url_1"];
        [business_card_dic setValue:img2 forKey:@"img_url_2"];
        [business_card_dic setValue:img0 forKey:@"img_url_aname_0"];
        [business_card_dic setValue:img1 forKey:@"img_url_aname_1"];
        [business_card_dic setValue:img2 forKey:@"img_url_aname_2"];
        [business_card_dic setValue:@"business_card" forKey:@"name"];
        
        [customer_dic setValue:business_card_dic forKey:@"item_1"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"customer_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        
        [customer_dic setValue:fax_dic forKey:@"item_10"];
        
        // zipcode
        NSDictionary *zipcode_dic = [self infoName:@"customer_zipcode" aname:@"Zipcode" control:@"text" keyboard:nil value:zipcode];
        
        [customer_dic setValue:zipcode_dic forKey:@"item_11"];
        
        // city
        NSDictionary *city_dic = [self infoName:@"customer_city" aname:@"City" control:@"text" keyboard:nil value:city];
        
        [customer_dic setValue:city_dic forKey:@"item_12"];
        
        // state
        NSDictionary *state_dic = [self infoName:@"customer_state" aname:@"State" control:@"text" keyboard:nil value:state];
        
        [customer_dic setValue:state_dic forKey:@"item_13"];
        
        // country
        NSDictionary *country_dic = [self infoName:@"customer_country" aname:@"Country" control:@"text" keyboard:nil value:country];
        
        [customer_dic setValue:country_dic forKey:@"item_14"];
        
        // company name
        NSDictionary *company_dic = [self infoName:@"customer_name" aname:@"Company Name" control:@"text" keyboard:nil value:company_name];
        
        [customer_dic setValue:company_dic forKey:@"item_2"];
        
        // addr_1
        NSDictionary *addr1_dic = [self infoName:@"customer_address1" aname:@"Address_1" control:@"text" keyboard:nil value:addr1];
        
        [customer_dic setValue:addr1_dic forKey:@"item_3"];
        
        // addr_2
        NSDictionary *addr2_dic = [self infoName:@"customer_address2" aname:@"Address_2" control:@"text" keyboard:nil value:addr2];
        
        [customer_dic setValue:addr2_dic forKey:@"item_4"];
        
        // addr_3
        NSDictionary *addr3_dic = [self infoName:@"customer_address3" aname:@"Address_3" control:@"text" keyboard:nil value:addr3];
        
        [customer_dic setValue:addr3_dic forKey:@"item_5"];
        
        // addr_4
        NSDictionary *addr4_dic = [self infoName:@"customer_address4" aname:@"Address_4" control:@"text" keyboard:nil value:addr4];
        
        [customer_dic setValue:addr4_dic forKey:@"item_6"];
        
        // Contact
        NSDictionary *contact_dic = [self infoName:@"customer_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:name];
        
        [customer_dic setValue:contact_dic forKey:@"item_7"];
        
        // email
        NSDictionary *email_dic = [self infoName:@"customer_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [customer_dic setValue:email_dic forKey:@"item_8"];
        
        // phone
        NSDictionary *phone_dic = [self infoName:@"customer_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [customer_dic setValue:phone_dic forKey:@"item_9"];
        
        // title
        [customer_dic setValue:@"Customer" forKey:@"title"];
        
        // count
        [customer_dic setValue:[NSNumber numberWithInteger:15] forKey:@"count"];
        
    }];

    return customer_dic;
}

+ (NSDictionary *)shipToDic:(NSDictionary *)params db:(sqlite3 *)db {
    
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
     
    
    NSString *sql = [NSString stringWithFormat:@"select receive_cid,receive_name,receive_ext,receive_contact,receive_email,receive_fax,receive_phone from offline_order where so_id = '%@';",orderCode];
    
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *cid = [self textAtColumn:0 statement:stmt];
        NSString *name = [self textAtColumn:1 statement:stmt];
        NSString *ext = [self textAtColumn:2 statement:stmt];
        NSString *contact = [self textAtColumn:3 statement:stmt];
        NSString *email = [self textAtColumn:4 statement:stmt];
        NSString *fax = [self textAtColumn:5 statement:stmt];
        NSString *phone = [self textAtColumn:6 statement:stmt];
        
        // count
        [dic setValue:[NSNumber numberWithInteger:8] forKey:@"count"];
        
        // title
        [dic setValue:@"Ship To" forKey:@"title"];
        
        // choose
        NSMutableDictionary *choose_dic = [NSMutableDictionary dictionary];
        [choose_dic setValue:@"choose" forKey:@"aname"];
        [choose_dic setValue:@"multi_action" forKey:@"control"];
        [choose_dic setValue:[NSNumber numberWithInteger:3] forKey:@"count"];
        NSDictionary *same_as_customer_dic = @{@"aname" : @"Same as customer",
                                               @"key_map" : @{@"receive_cid" : @"customer_cid",
                                                              @"receive_contact" : @"customer_contact",
                                                              @"receive_email" : @"customer_email",
                                                              @"receive_ext" : @"customer_contact_ext",
                                                              @"receive_fax" : @"customer_fax",
                                                              @"receive_name" : @"customer_name",
                                                              @"receive_phone" : @"customer_phone"},
                                               @"refresh" : [NSNumber numberWithInteger:1],
                                               @"type" : @"pull"};
        [choose_dic setValue:same_as_customer_dic forKey:@"item_0"];
        NSDictionary *create_new_ship_to_dic = @{@"aname" : @"Create new ship to",
                                             @"name" : @"Add new address",
                                             @"refresh" : [NSNumber numberWithInteger:1],
                                             @"value" : @"new_addr"
                                             };
        [choose_dic setValue:create_new_ship_to_dic forKey:@"item_1"];
        NSDictionary *select_ship_to_dic = @{@"aname" : @"Select ship to",
                                             @"key_map" : @{@"receive_cid" : @"customer_cid",
                                                              @"receive_contact" : @"customer_contact",
                                                              @"receive_email" : @"customer_email",
                                                              @"receive_ext" : @"customer_contact_ext",
                                                              @"receive_fax" : @"customer_fax",
                                                              @"receive_name" : @"customer_name",
                                                              @"receive_phone" : @"customer_phone"},
                                             @"name" : @"select_ship_to",
                                             @"refresh" : [NSNumber numberWithInteger:1],
                                             @"value" : @"Sales_Order_Ship_To"};
        [choose_dic setValue:select_ship_to_dic forKey:@"item_2"];
        
        [dic setValue:choose_dic forKey:@"item_0"];
        
        // contact id
        NSDictionary *contact_id_dic = [[self infoName:@"receive_cid" aname:@"Contact ID" control:@"edit" keyboard:@"text" value:cid] mutableCopy];
        [contact_id_dic setValue:@"1" forKey:@"disable"];
        [contact_id_dic setValue:@"true" forKey:@"required"];

        [dic setValue:contact_id_dic forKey:@"item_1"];
        
        // company name
        NSDictionary *company_name_dic = [self infoName:@"receive_name" aname:@"Company Name" control:@"edit" keyboard:@"text" value:name];
        
        [dic setValue:company_name_dic forKey:@"item_2"];
        
        // address
        NSDictionary *ext_dic = [self infoName:@"receive_ext" aname:@"Address" control:@"text_view" keyboard:@"text" value:ext];
        
        [dic setValue:ext_dic forKey:@"item_3"];
        
        // contact
        NSDictionary *contact_dic = [self infoName:@"receive_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:contact];
        
        [dic setValue:contact_dic forKey:@"item_4"];

        // phone
        NSDictionary *phone_dic = [self infoName:@"receive_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [dic setValue:phone_dic forKey:@"item_5"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"receive_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        [dic setValue:fax_dic forKey:@"item_6"];

        // email
        NSDictionary *email_dic = [self infoName:@"receive_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [dic setValue:email_dic forKey:@"item_7"];
        
    }];
    
    return dic;
}

+ (NSDictionary *)shipFromDic:(NSDictionary *)params db:(sqlite3 *)db {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    
    NSString *sql = [NSString stringWithFormat:@"select sender_cid,sender_name,sender_ext,sender_contact,sender_email,sender_fax,sender_phone from offline_order where so_id = '%@';",orderCode];
    
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *cid = [self textAtColumn:0 statement:stmt];
        NSString *name = [self textAtColumn:1 statement:stmt];
        NSString *ext = [self textAtColumn:2 statement:stmt];
        NSString *contact = [self textAtColumn:3 statement:stmt];
        NSString *email = [self textAtColumn:4 statement:stmt];
        NSString *fax = [self textAtColumn:5 statement:stmt];
        NSString *phone = [self textAtColumn:6 statement:stmt];
        
        // count
        [dic setValue:[NSNumber numberWithInteger:8] forKey:@"count"];
        
        // title
        [dic setValue:@"Ship From" forKey:@"title"];
        
        // hide
        [dic setValue:[NSNumber numberWithInteger:1] forKey:@"hide"];
        
        // choose
        NSMutableDictionary *choose_dic = [NSMutableDictionary dictionary];
        [choose_dic setValue:@"choose" forKey:@"aname"];
        [choose_dic setValue:@"multi_action" forKey:@"control"];
        [choose_dic setValue:[NSNumber numberWithInteger:1] forKey:@"count"];

        NSDictionary *select_ship_from_dic = @{@"aname" : @"Select ship from",
                                             @"key_map" : @{@"sender_cid" : @"customer_cid",
                                                            @"sender_contact" : @"customer_contact",
                                                            @"sender_email" : @"customer_email",
                                                            @"sender_ext" : @"customer_contact_ext",
                                                            @"sender_fax" : @"customer_fax",
                                                            @"sender_name" : @"customer_name",
                                                            @"sender_phone" : @"customer_phone"},
                                             @"name" : @"select_cid",
                                             @"refresh" : [NSNumber numberWithInteger:0],
                                             @"value" : @"Sales_Order_Ship_From"};
        [choose_dic setValue:select_ship_from_dic forKey:@"item_0"];
        
        [dic setValue:choose_dic forKey:@"item_0"];
        
        // contact id
        NSDictionary *contact_id_dic = [[self infoName:@"sender_cid" aname:@"Contact ID" control:@"edit" keyboard:@"text" value:cid] mutableCopy];
        [contact_id_dic setValue:@"1" forKey:@"disable"];
        [contact_id_dic setValue:@"true" forKey:@"required"];
        
        [dic setValue:contact_id_dic forKey:@"item_1"];
        
        // company name
        NSDictionary *company_name_dic = [self infoName:@"sender_name" aname:@"Company Name" control:@"edit" keyboard:@"text" value:name];
        
        [dic setValue:company_name_dic forKey:@"item_2"];
        
        // address
        NSDictionary *ext_dic = [self infoName:@"sender_ext" aname:@"Address" control:@"text_view" keyboard:@"text" value:ext];
        
        [dic setValue:ext_dic forKey:@"item_3"];
        
        // contact
        NSDictionary *contact_dic = [self infoName:@"sender_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:contact];
        
        [dic setValue:contact_dic forKey:@"item_4"];
        
        // phone
        NSDictionary *phone_dic = [self infoName:@"sender_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [dic setValue:phone_dic forKey:@"item_5"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"sender_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        [dic setValue:fax_dic forKey:@"item_6"];
        
        // email
        NSDictionary *email_dic = [self infoName:@"sender_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [dic setValue:email_dic forKey:@"item_7"];
        
    }];
    
    return dic;
}

+ (NSDictionary *)freightBillToDic:(NSDictionary *) params db:(sqlite3 *)db {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    
    NSString *sql = [NSString stringWithFormat:@"select shipping_billto_cid,shipping_billto_name,shipping_billto_ext,shipping_billto_contact,shipping_billto_email,shipping_billto_fax,shipping_billto_phone from offline_order where so_id = '%@';",orderCode];
    
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *cid = [self textAtColumn:0 statement:stmt];
        NSString *name = [self textAtColumn:1 statement:stmt];
        NSString *ext = [self textAtColumn:2 statement:stmt];
        NSString *contact = [self textAtColumn:3 statement:stmt];
        NSString *email = [self textAtColumn:4 statement:stmt];
        NSString *fax = [self textAtColumn:5 statement:stmt];
        NSString *phone = [self textAtColumn:6 statement:stmt];
        
        // count
        [dic setValue:[NSNumber numberWithInteger:8] forKey:@"count"];
        
        // title
        [dic setValue:@"Freight Bill To" forKey:@"title"];
        
        // hide
        [dic setValue:[NSNumber numberWithInteger:1] forKey:@"hide"];
        
        // choose
        NSMutableDictionary *choose_dic = [NSMutableDictionary dictionary];
        [choose_dic setValue:@"choose" forKey:@"aname"];
        [choose_dic setValue:@"multi_action" forKey:@"control"];
        [choose_dic setValue:[NSNumber numberWithInteger:4] forKey:@"count"];
        NSDictionary *same_as_ship_to_dic = @{@"aname" : @"Same as ship to",
                                               @"key_map" : @{@"shipping_billto_cid" : @"receive_cid",
                                                              @"shipping_billto_contact" : @"receive_contact",
                                                              @"shipping_billto_email" : @"receive_email",
                                                              @"shipping_billto_ext" : @"receive_ext",
                                                              @"shipping_billto_fax" : @"receive_fax",
                                                              @"shipping_billto_name" : @"receive_name",
                                                              @"shipping_billto_phone" : @"receive_phone"},
                                               @"type" : @"pull"};
        [choose_dic setValue:same_as_ship_to_dic forKey:@"item_0"];
        
        NSDictionary *same_as_customer_dic = @{@"aname" : @"Same as customer",
                                                 @"type" : @"pull",
                                                 @"key_map" : @{@"shipping_billto_cid" : @"customer_cid",
                                                                @"shipping_billto_contact" : @"customer_contact",
                                                                @"shipping_billto_email" : @"customer_email",
                                                                @"shipping_billto_ext" : @"customer_contact_ext",
                                                                @"shipping_billto_fax" : @"customer_fax",
                                                                @"shipping_billto_name" : @"customer_name",
                                                                @"shipping_billto_phone" : @"customer_phone"}
                                                 };
        [choose_dic setValue:same_as_customer_dic forKey:@"item_1"];
        
        NSDictionary *same_as_ship_from_dic = @{@"aname" : @"Same as ship from",
                                                @"key_map" : @{@"shipping_billto_cid" : @"sender_cid",
                                                               @"shipping_billto_contact" : @"sender_contact",
                                                               @"shipping_billto_email" : @"sender_email",
                                                               @"shipping_billto_ext" : @"sender_ext",
                                                               @"shipping_billto_fax" : @"sender_fax",
                                                               @"shipping_billto_name" : @"sender_name",
                                                               @"shipping_billto_phone" : @"sender_phone"},
                                                @"type" : @"pull"
                                             };
        [choose_dic setValue:same_as_ship_from_dic forKey:@"item_2"];
        
        NSDictionary *select_freight_bill_to_dic = @{
                                                     @"aname" : @"Select freight bill to",
                                                     @"name" : @"select_cid",
                                                     @"refresh" : [NSNumber numberWithInteger:0],
                                                     @"value" : @"Sales_Order_Freight_Bill_To",
                                                     @"key_map" : @{
                                                             @"shipping_billto_cid" : @"customer_cid",
                                                             @"shipping_billto_contact" : @"customer_contact",
                                                             @"shipping_billto_email" : @"customer_email",
                                                             @"shipping_billto_ext" : @"customer_contact_ext",
                                                             @"shipping_billto_fax" : @"customer_fax",
                                                             @"shipping_billto_name" : @"customer_name",
                                                             @"shipping_billto_phone" : @"customer_phone"
                                                             }
                                                     };
        [choose_dic setValue:select_freight_bill_to_dic forKey:@"item_3"];
        
        [dic setValue:choose_dic forKey:@"item_0"];
        
        // contact id
        NSDictionary *contact_id_dic = [[self infoName:@"shipping_billto_cid" aname:@"Contact ID" control:@"edit" keyboard:@"text" value:cid] mutableCopy];
        [contact_id_dic setValue:@"1" forKey:@"disable"];
        [contact_id_dic setValue:@"true" forKey:@"required"];
        
        [dic setValue:contact_id_dic forKey:@"item_1"];
        
        // company name
        NSDictionary *company_name_dic = [self infoName:@"shipping_billto_name" aname:@"Company Name" control:@"edit" keyboard:@"text" value:name];
        
        [dic setValue:company_name_dic forKey:@"item_2"];
        
        // address
        NSDictionary *ext_dic = [self infoName:@"shipping_billto_ext" aname:@"Address" control:@"text_view" keyboard:@"text" value:ext];
        
        [dic setValue:ext_dic forKey:@"item_3"];
        
        // contact
        NSDictionary *contact_dic = [self infoName:@"shipping_billto_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:contact];
        
        [dic setValue:contact_dic forKey:@"item_4"];
        
        // phone
        NSDictionary *phone_dic = [self infoName:@"shipping_billto_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [dic setValue:phone_dic forKey:@"item_5"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"shipping_billto_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        [dic setValue:fax_dic forKey:@"item_6"];
        
        // email
        NSDictionary *email_dic = [self infoName:@"shipping_billto_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [dic setValue:email_dic forKey:@"item_7"];
        
    }];
    
    return dic;
}

+ (NSDictionary *)merchandiseBillToDic:(NSDictionary *)params db:(sqlite3 *)db {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    
    NSString *sql = [NSString stringWithFormat:@"select billing_cid,billing_name,billing_ext,billing_contact,billing_email,billing_fax,billing_phone from offline_order where so_id = '%@';",orderCode];
    
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *cid = [self textAtColumn:0 statement:stmt];
        NSString *name = [self textAtColumn:1 statement:stmt];
        NSString *ext = [self textAtColumn:2 statement:stmt];
        NSString *contact = [self textAtColumn:3 statement:stmt];
        NSString *email = [self textAtColumn:4 statement:stmt];
        NSString *fax = [self textAtColumn:5 statement:stmt];
        NSString *phone = [self textAtColumn:6 statement:stmt];
        
        // count
        [dic setValue:[NSNumber numberWithInteger:8] forKey:@"count"];
        
        // title
        [dic setValue:@"Merchandise Bill To" forKey:@"title"];
        
        // hide
        [dic setValue:[NSNumber numberWithInteger:1] forKey:@"hide"];
        
        // choose
        NSMutableDictionary *choose_dic = [NSMutableDictionary dictionary];
        [choose_dic setValue:@"choose" forKey:@"aname"];
        [choose_dic setValue:@"multi_action" forKey:@"control"];
        [choose_dic setValue:[NSNumber numberWithInteger:3] forKey:@"count"];
        NSDictionary *same_as_ship_to_dic = @{@"aname" : @"Same as ship to",
                                              @"key_map" : @{@"billing_cid" : @"receive_cid",
                                                             @"billing_contact" : @"receive_contact",
                                                             @"billing_email" : @"receive_email",
                                                             @"billing_ext" : @"receive_ext",
                                                             @"billing_fax" : @"receive_fax",
                                                             @"billing_name" : @"receive_name",
                                                             @"billing_phone" : @"receive_phone"},
                                              @"type" : @"pull"};
        [choose_dic setValue:same_as_ship_to_dic forKey:@"item_0"];
        
        NSDictionary *same_as_customer_dic = @{@"aname" : @"Same as customer",
                                               @"type" : @"pull",
                                               @"key_map" : @{@"billing_cid" : @"customer_cid",
                                                              @"billing_contact" : @"customer_contact",
                                                              @"billing_email" : @"customer_email",
                                                              @"billing_ext" : @"customer_contact_ext",
                                                              @"billing_fax" : @"customer_fax",
                                                              @"billing_name" : @"customer_name",
                                                              @"billing_phone" : @"customer_phone"}
                                               };
        [choose_dic setValue:same_as_customer_dic forKey:@"item_1"];
        
        
        NSDictionary *select_bill_to_dic = @{
                                             @"aname" : @"Select bill to",
                                             @"name" : @"select_cid",
                                             @"refresh" : [NSNumber numberWithInteger:0],
                                             @"value" : @"Sales_Order_Merchandise_Bill_To",
                                             @"key_map" : @{
                                                     @"billing_cid" : @"customer_cid",
                                                     @"billing_contact" : @"customer_contact",
                                                     @"billing_email" : @"customer_email",
                                                     @"billing_ext" : @"customer_contact_ext",
                                                     @"billing_fax" : @"customer_fax",
                                                     @"billing_name" : @"customer_name",
                                                     @"billing_phone" : @"customer_phone"
                                                     }
                                             };
        [choose_dic setValue:select_bill_to_dic forKey:@"item_2"];
        
        [dic setValue:choose_dic forKey:@"item_0"];
        
        // contact id
        NSDictionary *contact_id_dic = [[self infoName:@"billing_cid" aname:@"Contact ID" control:@"edit" keyboard:@"text" value:cid] mutableCopy];
        [contact_id_dic setValue:@"1" forKey:@"disable"];
        [contact_id_dic setValue:@"true" forKey:@"required"];
        
        [dic setValue:contact_id_dic forKey:@"item_1"];
        
        // company name
        NSDictionary *company_name_dic = [self infoName:@"billing_name" aname:@"Company Name" control:@"edit" keyboard:@"text" value:name];
        
        [dic setValue:company_name_dic forKey:@"item_2"];
        
        // address
        NSDictionary *ext_dic = [self infoName:@"billing_ext" aname:@"Address" control:@"text_view" keyboard:@"text" value:ext];
        
        [dic setValue:ext_dic forKey:@"item_3"];
        
        // contact
        NSDictionary *contact_dic = [self infoName:@"billing_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:contact];
        
        [dic setValue:contact_dic forKey:@"item_4"];
        
        // phone
        NSDictionary *phone_dic = [self infoName:@"billing_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [dic setValue:phone_dic forKey:@"item_5"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"billing_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        [dic setValue:fax_dic forKey:@"item_6"];
        
        // email
        NSDictionary *email_dic = [self infoName:@"billing_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [dic setValue:email_dic forKey:@"item_7"];
        
    }];
    
    return dic;
}

+ (NSDictionary *)returnToDic:(NSDictionary *)params db:(sqlite3 *)db {
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    
    NSString *sql = [NSString stringWithFormat:@"select returnto_cid,returnto_name,returnto_ext,returnto_contact,returnto_email,returnto_fax,returnto_phone from offline_order where so_id = '%@';",orderCode];
    
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *cid = [self textAtColumn:0 statement:stmt];
        NSString *name = [self textAtColumn:1 statement:stmt];
        NSString *ext = [self textAtColumn:2 statement:stmt];
        NSString *contact = [self textAtColumn:3 statement:stmt];
        NSString *email = [self textAtColumn:4 statement:stmt];
        NSString *fax = [self textAtColumn:5 statement:stmt];
        NSString *phone = [self textAtColumn:6 statement:stmt];
        
        // count
        [dic setValue:[NSNumber numberWithInteger:8] forKey:@"count"];
        
        // title
        [dic setValue:@"Return To" forKey:@"title"];
        
        // hide
        [dic setValue:[NSNumber numberWithInteger:1] forKey:@"hide"];
        
        // choose
        NSMutableDictionary *choose_dic = [NSMutableDictionary dictionary];
        [choose_dic setValue:@"choose" forKey:@"aname"];
        [choose_dic setValue:@"multi_action" forKey:@"control"];
        [choose_dic setValue:[NSNumber numberWithInteger:2] forKey:@"count"];
        NSDictionary *same_as_ship_from_dic = @{@"aname" : @"Same as ship from",
                                              @"key_map" : @{@"returnto_cid" : @"sender_cid",
                                                             @"returnto_contact" : @"sender_contact",
                                                             @"returnto_email" : @"sender_email",
                                                             @"returnto_ext" : @"sender_ext",
                                                             @"returnto_fax" : @"sender_fax",
                                                             @"returnto_name" : @"sender_name",
                                                             @"returnto_phone" : @"sender_phone"},
                                              @"type" : @"pull"};
        [choose_dic setValue:same_as_ship_from_dic forKey:@"item_0"];
        
        
        NSDictionary *select_return_to_dic = @{
                                             @"aname" : @"Select return to",
                                             @"name" : @"select_cid",
                                             @"refresh" : [NSNumber numberWithInteger:0],
                                             @"value" : @"Contact_Return_To",
                                             @"key_map" : @{
                                                     @"returnto_cid" : @"customer_cid",
                                                     @"returnto_contact" : @"customer_contact",
                                                     @"returnto_email" : @"customer_email",
                                                     @"returnto_ext" : @"customer_contact_ext",
                                                     @"returnto_fax" : @"customer_fax",
                                                     @"returnto_name" : @"customer_name",
                                                     @"returnto_phone" : @"customer_phone"
                                                     }
                                             };
        [choose_dic setValue:select_return_to_dic forKey:@"item_1"];
        
        [dic setValue:choose_dic forKey:@"item_0"];
        
        // contact id
        NSDictionary *contact_id_dic = [[self infoName:@"returnto_cid" aname:@"Contact ID" control:@"edit" keyboard:@"text" value:cid] mutableCopy];
        [contact_id_dic setValue:@"1" forKey:@"disable"];
        [contact_id_dic setValue:@"true" forKey:@"required"];
        
        [dic setValue:contact_id_dic forKey:@"item_1"];
        
        // company name
        NSDictionary *company_name_dic = [self infoName:@"returnto_name" aname:@"Company Name" control:@"edit" keyboard:@"text" value:name];
        
        [dic setValue:company_name_dic forKey:@"item_2"];
        
        // address
        NSDictionary *ext_dic = [self infoName:@"returnto_ext" aname:@"Address" control:@"text_view" keyboard:@"text" value:ext];
        
        [dic setValue:ext_dic forKey:@"item_3"];
        
        // contact
        NSDictionary *contact_dic = [self infoName:@"returnto_contact" aname:@"Contact" control:@"edit" keyboard:@"text" value:contact];
        
        [dic setValue:contact_dic forKey:@"item_4"];
        
        // phone
        NSDictionary *phone_dic = [self infoName:@"returnto_phone" aname:@"Phone" control:@"edit" keyboard:@"text" value:phone];
        
        [dic setValue:phone_dic forKey:@"item_5"];
        
        // fax
        NSDictionary *fax_dic = [self infoName:@"returnto_fax" aname:@"Fax" control:@"edit" keyboard:@"text" value:fax];
        [dic setValue:fax_dic forKey:@"item_6"];
        
        // email
        NSDictionary *email_dic = [self infoName:@"returnto_email" aname:@"Email" control:@"edit" keyboard:@"text" value:email];
        
        [dic setValue:email_dic forKey:@"item_7"];
        
    }];
    
    return dic;
}

+ (NSDictionary *)modelInfoDic:(NSDictionary *)params db:(sqlite3 *)db {
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    __block NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    __block double TotalCuft = 0;
    __block double TotalWeight = 0;
    __block int TotalCarton = 0;
    __block double payments = 0;
    
    NSString *sql = [NSString stringWithFormat:@"select c.item_id,c.item_count,m.stockUom,c.price,c.discount,m.name,m.description from (select _id,product_id,decrypt(str_price) as price,discount,item_count,line_note,item_id from offline_cart where so_no='%@') c left join product m on c.product_id=m.product_id;",orderCode];
    
    AppDelegate *appDelegate = (AppDelegate *)[UIApplication sharedApplication].delegate;
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSMutableDictionary *model_dic = [NSMutableDictionary dictionary];
        
        // item id
        int item_id = sqlite3_column_int(stmt, 0);
        
        // count
        int item_count = sqlite3_column_int(stmt, 1);
        
        // stockUom
        int stockUom = sqlite3_column_int(stmt, 2);
        
        // unit price
        NSString *str_price = [self textAtColumn:3 statement:stmt];
        NSString* Price=nil;
        if(str_price==nil)
        {
            NSNumber* price = [self get_model_default_price:appDelegate.contact_id product_id:nil item_id:@(item_id) db:db];
            if(price==nil)
                Price=@"No Price.";
            else
                Price=[NSString stringWithFormat:@"%.2f",price.floatValue];
        }
        else
        {
            Price=[NSString stringWithFormat:@"%.2f",[str_price floatValue]];
        }
        
        // discount
        double discount = sqlite3_column_double(stmt, 4);
        
        // name
        NSString *name = [self textAtColumn:5 statement:stmt];
        
        // description
        NSString *description = [self textAtColumn:6 statement:stmt];
        description = [NSString stringWithFormat:@"%@\n%@",name,description ];

        // img
        NSString *img = [self item_image:[NSString stringWithFormat:@"%@",[NSNumber numberWithInt:item_id]] db:db];
        
        
        NSDictionary *combine = [self model_bundle:item_id db:db compute_part:false];
        
        double subTotal = item_count * [Price doubleValue] * (1.0 - discount / 100.0);
        if(combine != nil)
        {
            //  int citem=0;
            
            int bcount=[[combine valueForKey:@"count"] intValue];
            for(int bc=0;bc<bcount;bc++)
            {
                NSDictionary * bitem = [combine objectForKey:[NSString stringWithFormat:@"item_%d",bc]];
                int modulus= [[bitem valueForKey:@"modulus"] intValue];
                double uprice= [[bitem valueForKey:@"unit_price"]doubleValue];
                subTotal += uprice * modulus * item_count;
            }
        }

        [model_dic setValue:[NSString stringWithFormat:@"%d",item_id] forKey:@"cart_item_id"];
        [model_dic setValue:@"model" forKey:@"control"];
        [model_dic setValue:[NSString stringWithFormat:@"%d",item_count] forKey:@"count"];
        [model_dic setValue:description forKey:@"description"];
        [model_dic setValue:img forKey:@"img_url"];
        [model_dic setValue:[NSNumber numberWithInteger:stockUom] forKey:@"stockUom"];
        [model_dic setValue:[NSString stringWithFormat:@"%.2f",subTotal] forKey:@"total_price"];
        [model_dic setValue:Price forKey:@"unit_price"];
        [model_dic setValue:[NSString stringWithFormat:@"%.2f",discount] forKey:@"discount"];
        if (combine) {
            [model_dic setValue:combine forKey:@"combine"];
        }
        
        // well,what under the row is the info for total
        NSDictionary* bsubtotaljson=[self model_subtotal:item_id count:item_count db:db compute_part:true];
        double cuft=[bsubtotaljson[@"cuft"] doubleValue];
        double weight=[bsubtotaljson[@"weight"] doubleValue];
        int carton=[bsubtotaljson[@"carton"] intValue];
        
        TotalCuft += cuft;
        TotalWeight += weight;
        TotalCarton += carton;
        
        payments += subTotal;
        
        //---------------------------
        [dic setValue:model_dic forKey:[NSString stringWithFormat:@"item_%ld",(*count)++]];
        [dic setValue:[NSNumber numberWithInteger:(*count)] forKey:@"count"];
    }];
    
    [dic setValue:@"Model Information" forKey:@"title"];
    
    [dic setValue:@{@"payments" : [NSNumber numberWithDouble:payments],@"totalCuft" : [NSNumber numberWithDouble:TotalCuft],@"totalWeight" : [NSNumber numberWithDouble:TotalWeight],@"totalCarton" : [NSNumber numberWithInteger:TotalCarton]} forKey:@"toOrderTotal"];
    
    return dic;
}

+ (NSDictionary *)remarksContentDic:(NSDictionary *)params db:(sqlite3 *)db {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    [dic setValue:@"Remarks Content" forKey:@"title"];
    [dic setValue:[NSNumber numberWithInteger:4] forKey:@"count"];
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    NSString *sql = [NSString stringWithFormat:@"select poNumber,must_call,internal_notes,general_notes from offline_order where so_id = '%@';",orderCode];
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        NSString *poNumber = [self textAtColumn:0 statement:stmt];
        int mustCall = sqlite3_column_int(stmt, 1);
        NSString *internalNotes = [self textAtColumn:2 statement:stmt];
        NSString *generalNotes = [self textAtColumn:3 statement:stmt];
        
        NSDictionary *po_dic = @{
                                 @"aname" : @"PO#",
                                 @"control" : @"edit",
                                 @"keyboard" : @"text",
                                 @"name" : @"poNumber",
                                 @"value" : poNumber
                                 };
        NSDictionary *must_call_dic = @{
                                        @"aname" : @"MUST CALL BEFORE DELIVERY",
                                        @"control" : @"switch",
                                        @"name" : @"must_call",
                                        @"value" : mustCall ? @"true" : @"false"
                                        };
                                 
        NSDictionary *general_notes_dic = @{
                                            @"aname" : @"General notes",
                                            @"control" : @"text_view",
                                            @"keyboard" : @"text",
                                            @"name" : @"comments",
                                            @"value" : generalNotes
                                            };
        
        NSDictionary *internal_notes_dic = @{
                                             @"aname" : @"Internal notes",
                                             @"control" : @"text_view",
                                             @"keyboard" : @"text",
                                             @"name" : @"internal_notes",
                                             @"value" : internalNotes
                                             };
        
        [dic setValue:po_dic forKey:@"item_0"];
        [dic setValue:must_call_dic forKey:@"item_1"];
        [dic setValue:general_notes_dic forKey:@"item_2"];
        [dic setValue:internal_notes_dic forKey:@"item_3"];
    }];
    
    
    return dic;
}

+ (NSDictionary *)orderTotalDic:(NSDictionary *)params otherTotal:(NSDictionary *)total db:(sqlite3 *)db {
    // params
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];

    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    NSString *sql = [NSString stringWithFormat:@"select lift_gate,lift_gate_value,shipping,handling_fee_value from offline_order where so_id = '%@';",orderCode];
    
    __block double lift_gate_value = 0;
    __block double handling_fee = 0;
    __block double shipping = 0;
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        int lift_gate = sqlite3_column_int(stmt, 0);
        lift_gate_value = sqlite3_column_double(stmt, 1);
        shipping = sqlite3_column_double(stmt, 2);
        handling_fee = sqlite3_column_double(stmt, 3);
        
        if (!lift_gate) {
            lift_gate_value = 0;
        }
        
    }];
    
    double totalCuft = [[total valueForKey:@"totalCuft"] doubleValue];
    double totalWeight = [[total valueForKey:@"totalWeight"] doubleValue];
    int totalCarton = [[total valueForKey:@"totalCarton"] doubleValue];
    double payments = [[total valueForKey:@"payments"] doubleValue];
//    double totalPrice = payments + lift_gate_value + shipping + handling_fee;
    double totalPrice = payments;
    
    [dic setValue:@"Order Total" forKey:@"title"];
    
    NSDictionary *payments_dic = @{
                               @"align" : @"right",
                               @"aname" : @"Payments/Credits",
                               @"control" : @"text",
                               @"name" : @"paymentsAndCredits",
                               @"type" : @"price",
                               @"value" : [NSString stringWithFormat:@"%.2f",payments]
                               };
    [dic setValue:payments_dic forKey:@"item_0"];
    
    NSDictionary *handling_fee_dic = @{
                                       @"align" : @"right",
                                       @"aname" : @"Handling Fee",
                                       @"control" : @"text",
                                       @"name" : @"handling_fee_value",
                                       @"required" : @"true",
                                       @"type" : @"price",
                                       @"value" : @"Shipping To Be Quoted"/*[NSString stringWithFormat:@"%.2f",handling_fee]*/
                                       };
   
    
    NSDictionary *shipping_dic = @{
                                   @"align" : @"right",
                                   @"aname" : @"Shipping*",
                                   @"control" : @"text",
                                   @"name" : @"shipping",
                                   @"required" : @"true",
                                   @"type" : @"price",
                                   @"value" : @"Shipping To Be Quoted"/*[NSString stringWithFormat:@"%.2f",shipping]*/
                                   };
   
    
    NSDictionary *lift_gate_dic = @{
                                   @"align" : @"right",
                                   @"aname" : @"Liftgate Fee(No Loading Dock)",
                                   @"control" : @"text",
                                   @"name" : @"lift_gate_value",
                                   @"required" : @"true",
                                   @"type" : @"price",
                                   @"value" : @"Shipping To Be Quoted"/*[NSString stringWithFormat:@"%.2f",lift_gate_value]*/
                                   };
    
    int item_count = 1;
    
    NSString *params_lift_gate = [self valueInParams:params key:@"lift_gate"];
    if (params_lift_gate && [params_lift_gate isEqualToString:@"false"]) {
        
        [dic setValue:shipping_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
        [dic setValue:lift_gate_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
        
    } else if (params_lift_gate && [params_lift_gate isEqualToString:@"true"]) {
        
        [dic setValue:shipping_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
        
    } else {
        
    }

    
    [dic setValue:handling_fee_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
    
    
    
    NSDictionary *total_price_dic = @{
                                      @"align" : @"right",
                                      @"aname" : @"Total",
                                      @"control" : @"text",
                                      @"name" : @"totalPrice",
                                      @"type" : @"price",
                                      @"value" : [NSString stringWithFormat:@"%.2f",totalPrice]
                                      };
    [dic setValue:total_price_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
    
    NSDictionary *total_cuft_dic = @{
                                     @"align" : @"right",
                                     @"aname" : @"Total Cuft",
                                     @"control" : @"text",
                                     @"name" : @"",
                                     @"value" : [NSString stringWithFormat:@"%.2f",totalCuft]
                                     };
    [dic setValue:total_cuft_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
    
    NSDictionary *total_weight_dic = @{
                                       @"align" : @"right",
                                       @"aname" : @"Total Weight",
                                       @"control" : @"text",
                                       @"name" : @"",
                                       @"value" : [NSString stringWithFormat:@"%.2f",totalWeight]
                                       };
    [dic setValue:total_weight_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
    
    NSDictionary *total_carton_dic = @{
                                       @"align" : @"right",
                                       @"aname" : @"Total Carton",
                                       @"control" : @"text",
                                       @"name" : @"",
                                       @"value" : [NSString stringWithFormat:@"%d",totalCarton]
                                       };
    [dic setValue:total_carton_dic forKey:[NSString stringWithFormat:@"item_%d",item_count++]];
    
    [dic setValue:[NSNumber numberWithInteger:item_count] forKey:@"count"];
    
    return dic;
}

+ (NSDictionary *)signatureDic:(NSDictionary *)params db:(sqlite3 *)db {
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
    [dic setValue:@"Signature" forKey:@"title"];
    
    __block NSString *pic_path = @"";
    
    NSString *sql = [NSString stringWithFormat:@"select sign_picpath from offline_order where so_id = '%@';",orderCode];
    
    [iSalesDB jk_query:sql db:db close:NO completion:^(sqlite3_stmt *stmt, NSMutableDictionary *container, long *count) {
        
        pic_path = [self textAtColumn:0 statement:stmt];
        
    }];
    
    NSDictionary *pic_dic = @{
                              @"aname" : @"Signature",
                              @"avalue" :pic_path,
                              @"control" : @"signature",
                              @"name" : @"sign_picpath",
                              @"value" : pic_path
                              };
    [dic setValue:pic_dic forKey:@"item_0"];
    [dic setValue:[NSNumber numberWithInteger:1] forKey:@"count"];
    
    return dic;
}

+ (NSDictionary *)shippingMethodDic:(NSDictionary *)params db:(sqlite3 *)db {
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    
//    refresh_trigger	logist
    NSString *refresh_trigger = [self valueInParams:params key:@"refresh_trigger"];
    
    NSString *logist = nil;
    if ([refresh_trigger isEqualToString:@"logist"]) {
        logist = [self valueInParams:params key:@"logist"];
    }
    
    [dic setValue:[NSNumber numberWithInteger:1] forKey:@"count"];
    [dic setValue:@"Shipping Method" forKey:@"title"];
    
    // val_0
    if (logist && [logist isEqualToString:@"WILL CALL"]) {
        
    }
    
    NSMutableDictionary *val_0 = [NSMutableDictionary dictionary];
    [val_0 setValue:[NSNumber numberWithInteger:0] forKey:@"check"];
    [val_0 setValue:@"WILL CALL(requires 24 hours advance notice)" forKey:@"value"];
    [val_0 setValue:@"WILL CALL" forKey:@"value_id"];
    
    NSMutableDictionary *val0_subItem = [NSMutableDictionary dictionary];
    [val0_subItem setValue:[NSNumber numberWithInteger:1] forKey:@"count"];
    NSDictionary *val0_subItem_item0 = @{
                                         @"aname" : @"Option",
                                         @"cadedate" : @{
                                             @"count" : [NSNumber numberWithInteger:2],
                                             @"val_0" : @{
                                                 @"check" : [NSNumber numberWithInteger:0],
                                                 @"refresh" : [NSNumber numberWithInteger:0],
                                                 @"value" : @"PERSONAL PICK UP",
                                                 @"value_id" : @"PERSONAL PICK UP"
                                             },
                                             @"val_1" : @{
                                                 @"check" : [NSNumber numberWithInteger:0],
                                                 @"refresh" : [NSNumber numberWithInteger:0],
                                                 @"sub_item" : @{
                                                     @"count" : [NSNumber numberWithInteger:1],
                                                     @"item_0" : @{
                                                         @"aname" : @"BOL",
                                                         @"control" : @"edit",
                                                         @"keyboard" : @"text",
                                                         @"name" : @"logist_note_text",
                                                         @"refresh" : [NSNumber numberWithInteger:0],
                                                         @"required" : @"false",
                                                         @"value" : @""
                                                     }
                                                 },
                                                 @"value" : @"USE MY CARRIER",
                                                 @"value_id" : @"USE MY CARRIER"
                                             }
                                         },
                                         @"control" : @"enum",
                                         @"name" : @"logistic_note",
                                         @"required" : @"true",
                                         @"single_select" : @"true"
                                         };

    [val0_subItem setValue:val0_subItem_item0 forKey:@"item_0"];
    
    [val_0 setValue:val0_subItem forKey:@"sub_item"];
    
    // val_1
    NSString *lift_gate = [self valueInParams:params key:@"lift_gate"];
    if (lift_gate && [lift_gate isEqualToString:@"true"]) {
        
    } else {
        lift_gate = @"false";
    }
    NSDictionary *val_1 = @{
                            @"check" : [NSNumber numberWithInteger:0],
                            @"sub_item" : @{
                                @"count" : [NSNumber numberWithInteger:1],
                                @"item_0" : @{
                                    @"aname" : @"Do you have Loading Dock Available ? Slide to green for YES",
                                    @"control" : @"switch",
                                    @"name" : @"lift_gate",
                                    @"refresh" : [NSNumber numberWithInteger:1],
                                    @"required" : @"true",
                                    @"value" : lift_gate
                                }
                            },
                            @"value" : @"COMMON CARRIER",
                            @"value_id" : @"COMMON CARRIER"
                            };
    
    // cadedate
    NSDictionary *cadedate = @{
                               @"count" : [NSNumber numberWithInteger:2],
                               @"val_0" : val_0,
                               @"val_1" : val_1
                               };
    
    
    // item_0
    NSDictionary *item_0 = @{
                             @"aname" : @"Shipping",
                             @"cadedate" : cadedate,
                             @"control" : @"enum",
                             @"name" : @"logist",
                             @"refresh" : [NSNumber numberWithInteger:1],
                             @"single_select" : @"true",
                             };
    
    [dic setValue:item_0 forKey:@"item_0"];
    
    return dic;
}

+(NSData*) offline_placeorder :(NSMutableDictionary *) params
{
    
    sqlite3 *db = [iSalesDB get_db];
    
    // params
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    
    //  0 Order Type    1 Shipping Method       2 Payment Information
    __block NSMutableDictionary *ret = [self dictionaryFileName:@"placeOrderTemplate.json"];
    
    //    1 Shipping Method
    NSDictionary *shipping_method_dic = [self shippingMethodDic:params db:db];
    [ret setValue:shipping_method_dic forKey:@"section_1"];
    
    //    3 Customer
    NSDictionary *customer_dic = [self customerDic:params db:db];
    [ret setValue:customer_dic forKey:@"section_3"];

    //    4 Ship To
    NSDictionary *ship_to_dic = [self shipToDic:params db:db];
    [ret setValue:ship_to_dic forKey:@"section_4"];
    
    //    5 Ship From
    NSDictionary *ship_from_dic = [self shipFromDic:params db:db];
    [ret setValue:ship_from_dic forKey:@"section_5"];
    
    //    6 Freight Bill To
    NSDictionary *freight_bill_to = [self freightBillToDic:params db:db];
    [ret setValue:freight_bill_to forKey:@"section_6"];
    
    //    7 Merchandise Bill To
    NSDictionary *merchandise_bill_to_dic = [self merchandiseBillToDic:params db:db];
    [ret setValue:merchandise_bill_to_dic forKey:@"section_7"];
    
    //    8 Return To
    NSDictionary *return_to_dic = [self returnToDic:params db:db];
    [ret setValue:return_to_dic forKey:@"section_8"];
    
    //    9 Model Information
    NSMutableDictionary *model_info_dic = [[self modelInfoDic:params db:db] mutableCopy];
    
    NSDictionary *toOrderTotal = [model_info_dic valueForKey:@"toOrderTotal"];
    [model_info_dic removeObjectForKey:@"toOrderTotal"];
    
    [ret setValue:model_info_dic forKey:@"section_9"];
    
    //    10 Remarks Content
    NSDictionary *remarks_content_dic = [self remarksContentDic:params db:db];
    
    [ret setValue:remarks_content_dic forKey:@"section_10"];
    
    //    11 Order Total
    NSDictionary *order_total_dic = [self orderTotalDic:params otherTotal:toOrderTotal db:db];
    
    [ret setValue:order_total_dic forKey:@"section_11"];
    
    //    12 Signature
    NSDictionary *sign_dic = [self signatureDic:params db:db];
    
    [ret setValue:sign_dic forKey:@"section_12"];
    
    [iSalesDB close_db:db];
    
    return [RAUtils dict2data:ret];
//    return nil;
}

#pragma mark addr editor

+ (NSMutableDictionary *)anameDealWithStarAtItem:(NSDictionary *)item add:(BOOL)add {
    NSMutableDictionary *new_item = [item mutableCopy];
    
    [new_item setValue:add ? @"true" : @"false" forKey:@"required"];
    
    return new_item;
}

+(NSData*) offline_addreditor :(NSMutableDictionary *) params
{
    //    "is_subaction" = true;
    //    orderCode = MOB1608240002;
    //    password = 123456;
    //    "subaction_tag" = 1;
    //    user = EvanK;
    
    //    {
    //        "is_subaction" = true;
    //        orderCode = MOB1608240002;
    //        password = 123456;
    //        "refresh_trigger" = zipcode;
    //        "subaction_tag" = 1;
    //        user = EvanK;
    //    }
    
    NSMutableDictionary * ret = [self dictionaryFileName:@"createContact.json"];
    [ret removeObjectForKey:@"up_params"];
    [ret setObject:@"New Address" forKey:@"title"];
    
    NSDictionary * section_0 = [ret objectForKey:@"section_0"];
    NSMutableDictionary *new_section_0 = [NSMutableDictionary dictionary];
    
    [new_section_0 setObject:[NSNumber numberWithInteger:14] forKey:@"count"];
    
    NSMutableDictionary *country_dic = [section_0[@"item_5"] mutableCopy];
    [country_dic removeObjectForKey:@"refresh"];
    [country_dic removeObjectForKey:@"restore"];
    [new_section_0 setObject:country_dic forKey:@"item_0"]; // Country
    
    
    [new_section_0 setObject:section_0[@"item_0"] forKey:@"item_1"]; // company name*
    [new_section_0 setObject:[self anameDealWithStarAtItem:section_0[@"item_6"] add:YES] forKey:@"item_2"]; // Address 1*
    [new_section_0 setObject:section_0[@"item_7"] forKey:@"item_3"]; // Address 2
    [new_section_0 setObject:section_0[@"item_8"] forKey:@"item_4"]; // Address 3
    [new_section_0 setObject:section_0[@"item_9"] forKey:@"item_5"]; // Address 4
    
    NSMutableDictionary *zip_code_dic = [self anameDealWithStarAtItem:section_0[@"item_10"] add:YES];
    [zip_code_dic removeObjectForKey:@"refresh"];
    [new_section_0 setObject:zip_code_dic forKey:@"item_6"]; // Zip Code*
    
    [new_section_0 setObject:[self anameDealWithStarAtItem:section_0[@"item_11"] add:YES] forKey:@"item_7"]; // State/Province*
    [new_section_0 setObject:[self anameDealWithStarAtItem:section_0[@"item_12"] add:YES] forKey:@"item_8"]; // City*
    [new_section_0 setObject:section_0[@"item_13"] forKey:@"item_9"]; // Contact First Name
    [new_section_0 setObject:section_0[@"item_14"] forKey:@"item_10"]; // Contact Last Name
    [new_section_0 setObject:section_0[@"item_4"] forKey:@"item_11"]; // Phone*
    [new_section_0 setObject:section_0[@"item_15"] forKey:@"item_12"]; // Fax
    [new_section_0 setObject:[self anameDealWithStarAtItem:section_0[@"item_3"] add:NO] forKey:@"item_13"]; // Email
    [new_section_0 setObject:section_0[@"title"] forKey:@"title"];
    
    
    // country
    NSString *countryCode = @"US";
    NSDictionary *allCountry = [self offline_getAllCountryDefault:countryCode];
    [self setValue:allCountry forItemKey:@"item_0" valueKey:@"cadedate" inDictionary:new_section_0];
    
    
    // state
    NSMutableDictionary *allState = [[self offline_getStateByCountryCode:countryCode checkedState:nil] mutableCopy];
    
    NSDictionary *tmpDic = @{
                             @"value" : @"Other",
                             @"value_id" : @"",
                             @"check" : [NSNumber numberWithInteger:0]
                             };
    
    for (int i = 0; i < allState.allKeys.count; i++) {
        NSString *key = [NSString stringWithFormat:@"val_%d",i];
        
        NSDictionary *tmp = allState[key];
        [allState setValue:tmpDic forKey:key];
        tmpDic = tmp;
    }
    [allState setValue:[NSNumber numberWithInteger:allState.allKeys.count - 1] forKey:@"count"];
    
    [self setValue:allState forItemKey:@"item_7" valueKey:@"cadedate" inDictionary:new_section_0];
    
    [ret setValue:new_section_0 forKey:@"section_0"];
    
    return [RAUtils dict2data:ret];
}


#pragma mark cancel order


+(NSData*) offline_cancelorder :(NSMutableDictionary *) params
{
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    
    NSString *sql = [NSString stringWithFormat:@"update offline_order set status = 2,erpOrderStatus = 15 where so_id = '%@';",orderCode];
    
    int ret = [iSalesDB execSql:sql];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];
    
}

#pragma mark sign order

+(NSData*) offline_signorder :(NSMutableDictionary *) params
{
    //参考 offline_saveBusinesscard
    NSLog(@"sign order params: %@",params);
    //    orderCode = MOB1608240002;
    //    picpath = "16360511-BA55-4D66-9112-EF9DEA4A14F0.png";
    
    NSString *orderCode = [self valueInParams:params key:@"orderCode"];
    orderCode = [self translateSingleQuote:orderCode];
    NSString *picPath = [self valueInParams:params key:@"picpath"];
    picPath = [self translateSingleQuote:picPath];
    
    NSString *sql = [NSString stringWithFormat:@"update offline_order set sign_picpath = '%@' where so_id = '%@';",picPath,orderCode];
    
    
    int ret = [iSalesDB execSql:sql];
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    [dic setValue:[NSNumber numberWithInteger:ret] forKey:@"result"];
    [dic setValue:@"Regular Mode" forKey:@"mode"];
    [dic setValue:@"160409" forKey:@"min_ver"];
    
    return [RAUtils dict2data:dic];

}

@end