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

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

            
            char *price = (char*)sqlite3_column_text(statement, 1);
            if(price!=nil)
            {
                NSString* nsprice = [[NSString alloc]initWithUTF8String:price];
                nsprice=[AESCrypt fastdecrypt:nsprice];
                if(nsprice.length>0)
                {
                    double dp= [nsprice doubleValue];
                    item[nsname]= [NSString stringWithFormat:@"%.2f",dp];
                }
            }
            else
            {
                item[nsname]= @"No Price";
            }
            
            
            // item[nsname]= nsprice;
            
            ret[[NSString stringWithFormat:@"item_%d",count]]= item;
            count++;
            
        }
        
        ret[@"count"]= [NSNumber numberWithInt:count];
        
        
        
        sqlite3_finalize(statement);
        
        
        
        
    }
    
    sqlite3_close(db);
    
    return ret;
}

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

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

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

@end