Subversion Repositories SmartDukaan

from bson import json_util
from bson.json_util import dumps
from datetime import datetime, timedelta, date
from pyshorteners.shorteners  import Shortener
from dtr import main
from dtr.config import PythonPropertyReader
from dtr.storage import Mongo
from dtr.storage.DataService import Retailers, Users, CallHistory, RetryConfig, \
    RetailerLinks, Activation_Codes, Agents, Agent_Roles, AgentLoginTimings, \
    FetchDataHistory, RetailerContacts, Orders, OnboardedRetailerChecklists,\
    RetailerAddresses, Pincodeavailability, app_offers, appmasters, user_app_cashbacks, user_app_installs,\
    Postoffices, UserCrmCallingData, CallHistoryCrm, ProductPricingInputs,\
    tinxys_stats, profitmandi_sms, user_accounts
from dtr.storage.Mongo import get_mongo_connection
from dtr.storage.Mysql import fetchResult
from dtr.utils import DealSheet as X_DealSheet, \
    UserSpecificDeals, utils, ThriftUtils
from dtr.utils.utils import getLogger,encryptMessage,decryptMessage,\
    get_mongo_connection_dtr_data, to_java_date
from elixir import *
from operator import and_
from sqlalchemy.sql.expression import func, func, or_, desc, asc, case
from urllib import urlencode
import contextlib
import falcon
import json
import re
import string
import traceback
import urllib
import urllib2
import uuid
import gdshortener
from dtr.dao import AppOfferObj, UserAppBatchDrillDown, UserAppBatchDateDrillDown
import base64
from falcon.util.uri import decode
import MySQLdb
from shop2020.clients.CatalogClient import CatalogClient  
from pyquery import PyQuery as pq
import time
from dtr.main import refundToWallet
import random
from dtr.utils.AutoSuggest import getSuggestions
alphalist = list(string.uppercase)
alphalist.remove('O')
alphalist.remove('I')
alphalist.remove('L')
numList = ['2','3','4','5','6','7','8','9']
codesys = [alphalist, alphalist, numList, numList, numList]
newcodesys = alphalist + numList
CONTACT_PRIORITY = ['sms', 'called', 'ringing']
RETRY_MAP = {'fresh':'retry', 'followup':'fretry', 'onboarding':'oretry'}
ASSIGN_MAP = {'retry':'assigned', 'fretry':'fassigned', 'oretry':'oassigned'}
CRM_PROJECTS_USER_AVAILABILITY = {1:{'cooldown':30, 'isrepeat':True}, 2:{'cooldown':15,'isrepeat': False}, 3:{'cooldown':2,'isrepeat': True}, 5:{'cooldown':15,'isrepeat': True}}

sticky_agents = [17,29]

def getNextRandomCode(newcodesys,size=6):
    return ''.join(random.choice(newcodesys) for _ in range(size))
def getNextCode(codesys, code=None):
    if code is None:
        code = []
        for charcode in codesys:
            code.append(charcode[0])
        return string.join(code, '')
    carry = True
    code = list(code)
    lastindex = len(codesys) - 1
    while carry:
        listChar = codesys[lastindex]
        newIndex = (listChar.index(code[lastindex])+1)%len(listChar)
        print newIndex
        code[lastindex] = listChar[newIndex]
        if newIndex != 0:
            carry = False
        lastindex -= 1
        if lastindex ==-1:
            raise BaseException("All codes are exhausted")
        
    return string.join(code, '')


global RETAILER_DETAIL_CALL_COUNTER
RETAILER_DETAIL_CALL_COUNTER = 0
global USER_DETAIL_MAP
USER_DETAIL_MAP={} 
USER_DETAIL_MAP['accs_cart']=0
USER_DETAIL_MAP['accs_active']=0
USER_DETAIL_MAP['accs_order']=0
USER_DETAIL_MAP['accs_cashback_scheme']=0
# change 1
USER_DETAIL_MAP['inactive_users']=0
lgr = getLogger('/var/log/retailer-acquisition-api.log')
DEALER_RETRY_FACTOR = int(PythonPropertyReader.getConfig('DEALER_RETRY_FACTOR'))
DEALER_FRESH_FACTOR = int(PythonPropertyReader.getConfig('DEALER_FRESH_FACTOR'))
USER_CRM_DEFAULT_RETRY_FACTOR = int(PythonPropertyReader.getConfig('USER_CRM_DEFAULT_RETRY_FACTOR'))
USER_CRM_DEFAULT_FRESH_FACTOR = int(PythonPropertyReader.getConfig('USER_CRM_DEFAULT_FRESH_FACTOR'))
TOTAL = DEALER_RETRY_FACTOR + DEALER_FRESH_FACTOR
TOTAL_USER = USER_CRM_DEFAULT_FRESH_FACTOR + USER_CRM_DEFAULT_RETRY_FACTOR
TRANSACTIONAL_SMS_SEND_URL = "http://103.15.179.45:8085/SMSGateway/sendingSMS?"
  
class CategoryDiscountInfo(object):
    
    def on_get(self, req, resp):
        
        result = Mongo.getAllCategoryDiscount()
        json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
        resp.body = json.dumps(json_docs, encoding='utf-8')
    
    def on_post(self, req, resp):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addCategoryDiscount(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
    
    def on_put(self, req, resp, _id):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateCategoryDiscount(result_json, _id)
        resp.body = json.dumps(result, encoding='utf-8')
            
        
        
class SkuSchemeDetails(object):
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllSkuWiseSchemeDetails(offset, limit)
        json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
        resp.body = json.dumps(json_docs, encoding='utf-8')

    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addSchemeDetailsForSku(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
        
class SkuDiscountInfo():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        result = Mongo.getallSkuDiscountInfo(offset,limit)
        json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
        resp.body = json.dumps(json_docs, encoding='utf-8')

    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addSkuDiscountInfo(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
        
    def on_put(self, req, resp, _id):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateSkuDiscount(result_json, _id)
        resp.body = json.dumps(result, encoding='utf-8')

class ExceptionalNlc():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllExceptionlNlcItems(offset, limit)
        json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
        resp.body = json.dumps(json_docs, encoding='utf-8')
    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addExceptionalNlc(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
    
    def on_put(self, req, resp, _id):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateExceptionalNlc(result_json, _id)
        resp.body = json.dumps(result, encoding='utf-8')
        
class Deals():
    def on_get(self,req, resp, userId):
        categoryId = req.get_param_as_int("categoryId")
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        sort = req.get_param("sort")
        direction = req.get_param_as_int("direction")
        filterData = req.get_param('filterData')
        source = req.get_param('source')
        tag_ids = req.get_param_as_list('tag_ids')
        #print "tag_ids", tag_ids
        if tag_ids:
            tag_ids = [int(tag_id) for tag_id in tag_ids]
        if categoryId!=6:
            result = Mongo.getNewDeals(int(userId), categoryId, offset, limit, sort, direction, filterData, source, tag_ids)
        else:
            result = Mongo.getAccesoryDeals(int(userId), categoryId, offset, limit, sort, direction, filterData, source, tag_ids)
        resp.body = dumps(result) 
        
class MasterData():
    def on_get(self,req, resp, skuId):
        showDp = req.get_param_as_int("showDp")
        result = Mongo.getItem(skuId, showDp)
        try:
            json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
            resp.body = json.dumps(json_docs, encoding='utf-8')
        except:
            json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
            resp.body = json.dumps(json_docs, encoding='utf-8')
    
    def on_post(self,req, resp):
        
        addNew = req.get_param_as_int("addNew")
        update = req.get_param_as_int("update")
        addToExisting = req.get_param_as_int("addToExisting")
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        if addNew == 1:
            result = Mongo.addNewItem(result_json)
        elif update == 1:
            result = Mongo.updateMaster(result_json, multi)
        elif addToExisting == 1:
            result = Mongo.addItemToExistingBundle(result_json)
        else:
            raise
        resp.body = dumps(result)
                
class CashBack():
    def on_get(self,req, resp):
        identifier = req.get_param("identifier")
        source_id = req.get_param_as_int("source_id")
        try:
            result = Mongo.getCashBackDetails(identifier, source_id)
            json_docs = json.dumps(result, default=json_util.default)
            resp.body = json_docs
        except:
            json_docs = json.dumps({}, default=json_util.default)
            resp.body = json_docs

class ImgSrc():
    def on_get(self,req, resp):
        identifier = req.get_param("identifier")
        source_id = req.get_param_as_int("source_id")
        try:
            result = Mongo.getImgSrc(identifier, source_id)
            json_docs = json.dumps(result, default=json_util.default)
            resp.body = json_docs
        except:
            json_docs = json.dumps({}, default=json_util.default)
            resp.body = json_docs

class DealSheet():
    def on_get(self,req, resp):
        X_DealSheet.sendMail()
        json_docs = json.dumps({'True':'Sheet generated, mail sent.'}, default=json_util.default)
        resp.body = json.dumps(json_docs, encoding='utf-8')
    
class DealerPrice():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        result = Mongo.getAllDealerPrices(offset,limit)
        json_docs = [json.dumps(doc, default=json_util.default) for doc in result]
        resp.body = json.dumps(json_docs, encoding='utf-8')
    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addSkuDealerPrice(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
    
    def on_put(self, req, resp, _id):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateSkuDealerPrice(result_json, _id)
        resp.body = json.dumps(result, encoding='utf-8')

    
class ResetCache():
    
    def on_get(self,req, resp):
        cache_type = req.get_param("type")
        keys = req.get_param("keys")
        result = Mongo.resetCache(cache_type, keys)
        resp.body = json.dumps(result, encoding='utf-8')
        
class UserDeals():
    def on_get(self,req,resp,userId):
        UserSpecificDeals.generateSheet(userId)
        json_docs = json.dumps({'True':'Sheet generated, mail sent.'}, default=json_util.default)
        resp.body = json.dumps(json_docs, encoding='utf-8')

class CommonUpdate():
    
    def on_post(self,req,resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateCollection(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
        resp.content_type = "application/json; charset=utf-8"

class NegativeDeals():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllNegativeDeals(offset, limit)
        resp.body = dumps(result) 

    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addNegativeDeals(result_json, multi)
        resp.body = json.dumps(result, encoding='utf-8')

class ManualDeals():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllManualDeals(offset, limit)
        resp.body = dumps(result)

    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addManualDeal(result_json, multi)
        resp.body = json.dumps(result, encoding='utf-8')
        
class CommonDelete():
    
    def on_post(self,req,resp):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.deleteDocument(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
        resp.content_type = "application/json; charset=utf-8"

class SearchProduct():
    
    def on_get(self,req,resp):
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        search_term = req.get_param("search")
        
        result = Mongo.searchMaster(offset, limit, search_term)
        resp.body = dumps(result) 

        
class FeaturedDeals():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllFeaturedDeals(offset, limit)
        resp.body = dumps(result)

    
    def on_post(self, req, resp):
        
        multi = req.get_param_as_int("multi")
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addFeaturedDeal(result_json, multi)
        resp.body = json.dumps(result, encoding='utf-8')


class CommonSearch():
    
    def on_get(self,req,resp):
        class_name = req.get_param("class")
        sku = req.get_param_as_int("sku")
        skuBundleId = req.get_param_as_int("skuBundleId")

        result = Mongo.searchCollection(class_name, sku, skuBundleId)
        resp.body = dumps(result)

class CricScore():
    
    def on_get(self,req,resp):

        result = Mongo.getLiveCricScore()
        resp.body = dumps(result)

class Notification():
    
    def on_post(self, req, resp):
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addBundleToNotification(result_json)
        resp.body = json.dumps(result, encoding='utf-8')
    
    def on_get(self, req, resp):

        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllNotifications(offset, limit)
        resp.body = dumps(result)

class DealBrands():
    
    def on_get(self, req, resp):
        
        category_id = req.get_param_as_int("category_id")
        result = Mongo.getBrandsForFilter(category_id)
        resp.body = dumps(result)

class DealRank():
    
    def on_get(self, req, resp):
        identifier = req.get_param("identifier")
        source_id = req.get_param_as_int("source_id")
        user_id = req.get_param_as_int("user_id")
        result = Mongo.getDealRank(identifier, source_id, user_id)
        json_docs = json.dumps(result, default=json_util.default)
        resp.body = json_docs
        
    
class OrderedOffers():
    def on_get(self, req, resp, storeId, storeSku):
        storeId = int(storeId)
        result = Mongo.getBundleBySourceSku(storeId, storeSku)
        json_docs = json.dumps(result, default=json_util.default)
        resp.body = json_docs
        
class GetDtrLinks():
    
    def on_get(self, req, resp, agentId, callType, userId):
        try:
            self.agentId = int(agentId)
            self.callType = callType
            if userId is not None:
                self.userId = int(userId)
                users_referrer, = session.query(Users.referrer).filter_by(id=self.userId).first()
                retailerLink = session.query(RetailerLinks).filter_by(code=users_referrer).first()
                if retailerLink is not None:
                    code = retailerLink.code
                elif users_referrer.lower()=='fos01':
                    code = 'fos01'
                else:
                    resp.body =  json.dumps({"success":"false"}, encoding='utf-8')
                    return
                session.close()
                resp.body =  json.dumps({"result":{"code":code,"link":make_tiny(code)}}, encoding='utf-8')
                return 
        finally:
            session.close
            
    def getNewRandomCode(self,):
        newCode = None
        while True:
            newCode = getNextRandomCode(newcodesys, 6)
            print 'NewCode',newCode
            isCodePresent = session.query(Activation_Codes).filter_by(code=newCode).first()
            if isCodePresent is not None:
                continue
            else:
                break
        return newCode
           
class RetailerDetail():
    global RETAILER_DETAIL_CALL_COUNTER
    def getRetryRetailer(self,failback=True):
        status = RETRY_MAP.get(self.callType)
        retailer = session.query(Retailers).filter_by(status=status).filter(Retailers.next_call_time<=datetime.now()).filter(or_(Retailers.agent_id==self.agentId, Retailers.agent_id==None)).order_by(Retailers.agent_id.desc(),Retailers.call_priority).order_by(Retailers.next_call_time).with_lockmode("update").first()
        
        if retailer is not None:
            lgr.info( "getRetryRetailer " + str(retailer.id))
        else:
            if failback:
                retailer = self.getNewRetailer(False)
                return retailer
            else:
                #No further calls for now
                return None
        retailer.status = ASSIGN_MAP.get(status)
        retailer.next_call_time = None
        lgr.info( "getRetryRetailer " + str(retailer.id))
        return retailer
            
    def getNotActiveRetailer(self):
        try:
            user = session.query(Users).filter_by(activated=0).filter_by(status=1).filter(Users.mobile_number != None).filter(~Users.mobile_number.like("0%")).filter(Users.created>datetime(2015,06,29)).order_by(Users.created.desc()).with_lockmode("update").first()
            if user is None: 
                return None
            else:
                retailerContact = session.query(RetailerContacts).filter_by(mobile_number=user.mobile_number).first()
                if retailerContact is not None:
                    retailer = session.query(Retailers).filter_by(id=retailerContact.retailer_id).first()
                else:
                    retailer = session.query(Retailers).filter_by(contact1=user.mobile_number).first()
                    if retailer is None:
                        retailer = session.query(Retailers).filter_by(contact2=user.mobile_number).first()
                        if retailer is None:
                            retailer = Retailers()
                            retailer.contact1 = user.mobile_number
                            retailer.status = 'assigned'
                            retailer.retry_count = 0
                            retailer.invalid_retry_count = 0
                            retailer.is_elavated=1
                            retailer.isvalidated = 0
                            retailer.source = 'outbound' 
                user.status = 2
                session.commit()
                print "retailer id", retailer.id
                retailer.contact = user.mobile_number
                return retailer
        finally:
            session.close()
            
    def getNewRetailer(self,failback=True):
        if self.callType == 'fresh':
            retailer = self.getNotActiveRetailer()
            if retailer is not None:
                return retailer
            else:
                return None
            '''
            As of 26 august 2016, this is temporary change should be rolled back when required
            Hint remove above else statement
            '''
        retry = True
        retailer = None 
        try:
            while(retry):
                lgr.info( "Calltype " + self.callType)
                status=self.callType
                query = session.query(Retailers).filter(Retailers.status==status).filter(or_(Retailers.agent_id==self.agentId, Retailers.agent_id==None))
                if status=='fresh':
                    query = query.filter_by(is_or=False, is_std=False).filter(Retailers.cod_limit > 4998).order_by(Retailers.is_elavated.desc(), Retailers.agent_id.desc())
                elif status=='followup':
                    query = query.filter(Retailers.next_call_time<=datetime.now()).order_by(Retailers.agent_id.desc(),Retailers.next_call_time)
                else:
                    query = query.filter(Retailers.modified<=datetime.now()).order_by(Retailers.agent_id.desc(), Retailers.modified)
                    
                retailer = query.with_lockmode("update").first()
                if retailer is not None:
                    lgr.info( "retailer " +str(retailer.id))
                    if status=="fresh":
                        userquery = session.query(Users)
                        if retailer.contact2 is not None:
                            userquery = userquery.filter(Users.mobile_number.in_([retailer.contact1,retailer.contact2]))
                        else:
                            userquery = userquery.filter_by(mobile_number=retailer.contact1)
                        user = userquery.first()
                        if user is not None:
                            retailer.status = 'alreadyuser'
                            lgr.info( "retailer.status " + retailer.status)
                            session.commit()
                            continue
                        retailer.status = 'assigned'
                    elif status=='followup':
                        if isActivated(retailer.id):
                            print "Retailer Already %d activated and marked onboarded"%(retailer.id)
                            continue
                        retailer.status = 'fassigned'
                    else:
                        retailer.status = 'oassigned'
                    retailer.retry_count = 0
                    retailer.invalid_retry_count = 0
                    lgr.info( "Found Retailer " +  str(retailer.id) + " with status " + status + " assigned to " + str(self.agentId))
                        
                else:
                    lgr.info( "No fresh/followup retailers found")
                    if failback:
                        retailer = self.getRetryRetailer(False)
                        return retailer
                retry=False
        except:
            print traceback.print_exc()
        return retailer
    
    def on_get(self, req, resp, agentId, callType=None, retailerId=None):
        try:
            global RETAILER_DETAIL_CALL_COUNTER
            RETAILER_DETAIL_CALL_COUNTER += 1
            lgr.info( "RETAILER_DETAIL_CALL_COUNTER " +  str(RETAILER_DETAIL_CALL_COUNTER))
            self.agentId = int(agentId)
            self.callType = callType
            if retailerId is not None:
                self.retailerId = int(retailerId)
                retailerLink = session.query(RetailerLinks).filter_by(retailer_id=self.retailerId).first()
                if retailerLink is not None:
                    code = retailerLink.code
                    retailerLink.agent_id = self.agentId
                    session.commit()
                else: 
                    code = self.getNewRandomCode()
                    retailerLink = RetailerLinks()
                    retailerLink.code = code
                    retailerLink.agent_id = self.agentId
                    retailerLink.retailer_id = self.retailerId
                    activationCode=Activation_Codes()
                    activationCode.code = code
                    activationCode.status = False
                    session.commit()
                session.close()
                resp.body =  json.dumps({"result":{"code":code,"link":make_tiny(code)}}, encoding='utf-8')
                return 
            retryFlag = False 
            if RETAILER_DETAIL_CALL_COUNTER % TOTAL >= DEALER_FRESH_FACTOR:
                retryFlag=True
            try:
                if retryFlag:
                    retailer = self.getRetryRetailer()
                else:
                    retailer = self.getNewRetailer()
                if retailer is None:
                    resp.body = "{}"
                    return
                fetchInfo = FetchDataHistory()
                fetchInfo.agent_id = self.agentId
                fetchInfo.call_type = self.callType
                agent = session.query(Agents).filter_by(id=self.agentId).first()
                last_disposition = session.query(CallHistory).filter_by(agent_id=self.agentId).order_by(CallHistory.id.desc()).first()
                if last_disposition is None or last_disposition.created < agent.last_login:
                    fetchInfo.last_action = 'login'
                    fetchInfo.last_action_time = agent.last_login 
                else:
                    fetchInfo.last_action = 'disposition'
                    fetchInfo.last_action_time = last_disposition.created
                fetchInfo.retailer_id = retailer.id
                session.commit()
                
                otherContacts = [r for r, in session.query(RetailerContacts.mobile_number).filter_by(retailer_id=retailer.id).order_by(RetailerContacts.contact_type).all()]
                resp.body = json.dumps(todict(getRetailerObj(retailer, otherContacts, self.callType)), encoding='utf-8')
                
                return
                
            finally:
                session.close()
    
            if retailer is None:
                resp.body = "{}"
            else:
                print "It should never come here"
                resp.body = json.dumps(todict(getRetailerObj(retailer)), encoding='utf-8')
        finally:
            session.close()
        
    def on_post(self, req, resp, agentId, callType):
        returned = False
        self.agentId = int(agentId)
        self.callType = callType
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        lgr.info( "Request ----\n"  + str(jsonReq))
        self.jsonReq = jsonReq
        invalidNumber = self.invalidNumber
        callLater = self.callLater
        alreadyUser = self.alReadyUser
        verifiedLinkSent = self.verifiedLinkSent
        onboarded = self.onboarded
        self.address = jsonReq.get('address')
        self.retailerId = int(jsonReq.get('retailerid'))
        self.smsNumber = jsonReq.get('smsnumber')
        if self.smsNumber is not None:
            self.smsNumber = self.smsNumber.strip().lstrip("0") 
        try:
            self.retailer = session.query(Retailers).filter_by(id=self.retailerId).first()
            if self.address:
                self.retailer.address_new = self.address
            self.callDisposition = jsonReq.get('calldispositiontype')
            self.callHistory = CallHistory()
            self.callHistory.agent_id=self.agentId
            self.callHistory.call_disposition = self.callDisposition
            self.callHistory.retailer_id=self.retailerId
            self.callHistory.call_type=self.callType
            self.callHistory.duration_sec = int(jsonReq.get("callduration"))
            self.callHistory.disposition_description = jsonReq.get('calldispositiondescritption')
            self.callHistory.disposition_comments = jsonReq.get('calldispositioncomments')
            lgr.info(self.callHistory.disposition_comments)
            self.callHistory.call_time = datetime.strptime(jsonReq.get("calltime"), '%d/%m/%Y %H:%M:%S')
            self.callHistory.mobile_number = jsonReq.get('number')
            self.callHistory.sms_verified = int(jsonReq.get("verified"))
            lastFetchData = session.query(FetchDataHistory).filter_by(agent_id=self.agentId).order_by(FetchDataHistory.id.desc()).first()
            if self.callDisposition == 'onboarded':
                self.checkList = jsonReq.get('checklist')
                
            if lastFetchData is None:
                raise
            self.callHistory.last_fetch_time= lastFetchData.created  
            
            dispositionMap = {  'call_later':callLater,
                        'ringing_no_answer':callLater,
                        'not_reachable':callLater,
                        'switch_off':callLater,
                        'not_retailer':invalidNumber,
                        'invalid_no':invalidNumber,
                        'wrong_no':invalidNumber,
                        'hang_up':invalidNumber,
                        'retailer_not_interested':invalidNumber,
                        'recharge_retailer':invalidNumber,
                        'accessory_retailer':invalidNumber,
                        'service_center_retailer':invalidNumber,
                        'alreadyuser':alreadyUser,
                        'verified_link_sent':verifiedLinkSent,
                        'onboarded':onboarded
                      }
            returned = dispositionMap[jsonReq.get('calldispositiontype')]()
        finally:
            session.close()
        
        if returned:
            resp.body = "{\"result\":\"success\"}"
        else:
            resp.body = "{\"result\":\"failed\"}"

    def invalidNumber(self,):
        #self.retailer.status = 'retry' if self.callType == 'fresh' else 'fretry'
        if self.callDisposition == 'invalid_no':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Invalid Number'
        elif self.callDisposition == 'wrong_no':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Wrong Number'
        elif self.callDisposition == 'hang_up':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Hang Up'
        elif self.callDisposition == 'retailer_not_interested':
            self.retailer.status='failed'
            if self.callHistory.disposition_description is None:
                self.callHistory.disposition_description = 'NA'
            self.callHistory.disposition_description = 'Reason Retailer Not Interested ' + self.callHistory.disposition_description
        elif self.callDisposition == 'recharge_retailer':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Recharge related. Not a retailer '
        elif self.callDisposition == 'accessory_retailer':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Accessory related. Not a retailer'
        elif self.callDisposition == 'service_center_retailer':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Service Center related. Not a retailer'
        elif self.callDisposition == 'not_retailer':
            self.retailer.status='failed'
            self.callHistory.disposition_description = 'Not a retailer'    
        session.commit()
        return True   
        
    def getCode(self,):
        newCode = None
        lastLink = session.query(RetailerLinks).order_by(RetailerLinks.id.desc()).with_lockmode("update").first()
        if lastLink is not None:
            if len(lastLink.code)==len(codesys):
                newCode=lastLink.code
        return getNextCode(codesys, newCode)
    
    def getNewRandomCode(self,):
        newCode = None
        while True:
            newCode = getNextRandomCode(newcodesys, 6)
            print 'NewCode',newCode
            isCodePresent = session.query(Activation_Codes).filter_by(code=newCode).first()
            if isCodePresent is not None:
                continue
            else:
                break
        return newCode
    
    def callLater(self,):
        self.retailer.status = RETRY_MAP.get(self.callType)
        self.retailer.call_priority = None
        if self.callDisposition == 'call_later':
            if self.callHistory.disposition_description is not None:
                self.retailer.call_priority = 'user_initiated'
                self.retailer.next_call_time = datetime.strptime(self.callHistory.disposition_description, '%d/%m/%Y %H:%M:%S')
                self.callHistory.disposition_description = 'User requested to call on ' + self.callHistory.disposition_description
            else:
                self.retailer.call_priority = 'system_initiated'
                self.retailer.next_call_time = self.callHistory.call_time + timedelta(days=1)
                self.callHistory.disposition_description = 'Call scheduled on ' + datetime.strftime(self.retailer.next_call_time, '%d/%m/%Y %H:%M:%S')
        else: 
            if self.callDisposition == 'ringing_no_answer':
                if self.retailer.disposition == 'ringing_no_answer':
                    self.retailer.retry_count += 1
                else:
                    self.retailer.disposition = 'ringing_no_answer'
                    self.retailer.retry_count = 1
            else:
                if self.retailer.disposition == 'ringing_no_answer':
                    pass
                else:
                    self.retailer.disposition = 'not_reachable'
                self.retailer.retry_count += 1
                self.retailer.invalid_retry_count += 1
                    
            retryConfig = session.query(RetryConfig).filter_by(call_type=self.callType, disposition_type=self.retailer.disposition, retry_count=self.retailer.retry_count).first()
            if retryConfig is not None:
                self.retailer.next_call_time = self.callHistory.call_time + timedelta(minutes = retryConfig.minutes_ahead)
                self.callHistory.disposition_description = 'Call scheduled on ' + datetime.strftime(self.retailer.next_call_time, '%d/%m/%Y %H:%M:%S')
            else:
                self.retailer.status = 'failed'
                self.callHistory.disposition_description = 'Call failed as all attempts exhausted'
            
        session.commit()
        return True
            
            
    def alReadyUser(self,):
        self.retailer.status = self.callDisposition
        if self.callHistory.disposition_description is None:
            self.callHistory.disposition_description = 'Retailer already user' 
        session.commit()
        return True
    def verifiedLinkSent(self,):
        if self.callType == 'fresh':
            if self.retailer.isvalidated:
                self.retailer.status = 'followup'
                if self.retailer.agent_id not in sticky_agents:
                    self.retailer.agent_id = None
                self.retailer.next_call_time = self.callHistory.call_time + timedelta(days=1)
                self.callHistory.disposition_description = 'App link sent via ' + self.callHistory.disposition_description+ '. followup on' + datetime.strftime(self.retailer.next_call_time, '%d/%m/%Y %H:%M:%S') 
            else:
                self.retailer.status = 'pending_verification'
                if self.retailer.agent_id not in sticky_agents:
                    self.retailer.agent_id = None
                self.callHistory.disposition_description = "Retailer is pending for verification" 
        else:
            self.retailer.status = 'followup'
            if self.retailer.agent_id not in sticky_agents:
                self.retailer.agent_id = None
            self.retailer.next_call_time = self.callHistory.call_time + timedelta(days=7)
            self.callHistory.disposition_description = 'App link sent via' + self.callHistory.disposition_description + '. Followup again on ' + datetime.strftime(self.retailer.next_call_time, '%d/%m/%Y %H:%M:%S')
        addContactToRetailer(self.agentId, self.retailerId, self.smsNumber, self.callType, 'sms')     
        session.commit()
        return True
    def onboarded(self,):
        self.retailer.status = self.callDisposition
        checkList = OnboardedRetailerChecklists()
        checkList.contact_us = self.checkList.get('contactus')
        checkList.doa_return_policy = self.checkList.get('doareturnpolicy')
        checkList.number_verification = self.checkList.get('numberverification')
        checkList.payment_option = self.checkList.get('paymentoption')
        checkList.preferences = self.checkList.get('preferences')
        checkList.product_info = self.checkList.get('productinfo')
        checkList.redeem = self.checkList.get('redeem')
        checkList.retailer_id = self.retailerId
        session.commit()
        return True
        
        
def isActivated(retailerId):
    retailerLink = session.query(RetailerLinks).filter_by(retailer_id=retailerId).first()
    user = session.query(Users).filter(or_(func.lower(Users.referrer)==retailerLink.code.lower(), Users.utm_campaign==retailerLink.code)).first()
    if user is None:
        mobileNumbers = list(session.query(RetailerContacts.mobile_number).filter_by(retailer_id=retailerId).all())
        user = session.query(Users).filter(Users.mobile_number.in_(mobileNumbers)).first()
        if user is None:
            if retailerLink.created < datetime(2015,5,26):
                historyNumbers = [number for number, in session.query(CallHistory.mobile_number).filter_by(retailer_id = retailerId).all()]
                user = session.query(Users).filter(Users.mobile_number.in_(historyNumbers)).first()
                if user is None:
                    return False
                else:
                    mapped_with = 'contact'
            else:
                return False
        else:
            mapped_with = 'contact'
    else:
        mapped_with = 'code'
    retailerLink.mapped_with = mapped_with
    if user.activation_time is not None:
        retailerLink.activated = user.activation_time
    retailerLink.activated = user.created
    retailerLink.user_id = user.id
    retailer = session.query(Retailers).filter_by(id=retailerId).first()
    if retailer.status == 'followup' or retailer.status == 'fretry':
        retailer.status = 'onboarding'
        if retailer.agent_id not in sticky_agents:
                retailer.agent_id = None
        retailer.call_priority = None
        retailer.next_call_time = None
        retailer.retry_count = 0
        retailer.invalid_retry_count = 0
    session.commit()
    print "retailerLink.retailer_id", retailerLink.retailer_id
    print "retailer", retailer.id
    session.close()
    return True
    
class AddContactToRetailer():
    def on_post(self,req,resp, agentId):
        agentId = int(agentId)
        try:
            jsonReq = json.loads(req.stream.read(), encoding='utf-8')
            retailerId = int(jsonReq.get("retailerid"))
            mobile = jsonReq.get("mobile")
            callType = jsonReq.get("calltype")
            contactType = jsonReq.get("contacttype")
            addContactToRetailer(agentId, retailerId, mobile, callType, contactType)
            session.commit()
        finally:
            session.close()

class AddAddressToRetailer():
    def on_post(self,req,resp, agentId):
        agentId = int(agentId)
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        retailerId = int(jsonReq.get("retailerid"))
        address = str(jsonReq.get("address"))
        storeName = str(jsonReq.get("storename"))
        pin = str(jsonReq.get("pin"))
        city = str(jsonReq.get("city"))
        state = str(jsonReq.get("state"))
        updateType = str(jsonReq.get("updatetype"))
        tinnumber = str(jsonReq.get("tinnumber"))
        addAddressToRetailer(agentId, retailerId, address, storeName, pin, city,state, updateType,tinnumber)
            
def addContactToRetailer(agentId, retailerId, mobile, callType, contactType):
    retailerContact = session.query(RetailerContacts).filter_by(retailer_id=retailerId).filter_by(mobile_number=mobile).first()
    if retailerContact is None:
        retailerContact = RetailerContacts()
        retailerContact.retailer_id = retailerId
        retailerContact.agent_id = agentId
        retailerContact.call_type = callType
        retailerContact.contact_type = contactType
        retailerContact.mobile_number = mobile
    else:
        if CONTACT_PRIORITY.index(retailerContact.contact_type) > CONTACT_PRIORITY.index(contactType):
            retailerContact.contact_type = contactType

def addAddressToRetailer(agentId, retailerId, address, storeName, pin, city,state, updateType,tinnumber):
    print "I am in addAddress"
    print agentId, retailerId, address, storeName, pin, city, state, updateType
    try:
        retailer = session.query(Retailers).filter_by(id=retailerId).first()
        retailer.tinnumber = tinnumber
        if updateType=='new':
            retailer.address = address
            retailer.title = storeName
            retailer.city = city
            retailer.state = state
            retailer.pin = pin
        raddress = RetailerAddresses()
        raddress.address = address
        raddress.title = storeName
        raddress.agent_id = agentId
        raddress.city = city
        raddress.pin = pin
        raddress.retailer_id = retailerId
        raddress.state = state
        session.commit()
    finally:
        session.close() 
        
        
class Login():
    
    def on_get(self, req, resp, agentId, role):
        try:
            self.agentId = int(agentId)
            self.role = role
            print str(self.agentId) + self.role;
            agents=AgentLoginTimings()
            lastLoginTime = session.query(Agents).filter(Agents.id==self.agentId).first()
            print 'lastLogintime' + str(lastLoginTime)
            agents.loginTime=lastLoginTime.last_login
            agents.logoutTime=datetime.now()
            agents.role =self.role
            agents.agent_id = self.agentId
            session.add(agents)    
            session.commit()
            resp.body =  json.dumps({"result":{"success":"true","message":"Success"}}, encoding='utf-8')
        finally:
            session.close()        
        
    def on_post(self,req,resp):
        try:
            jsonReq = json.loads(req.stream.read(), encoding='utf-8')
            lgr.info( "Request ----\n"  + str(jsonReq))
            email=jsonReq.get('email')
            password = jsonReq.get('password')
            role=jsonReq.get('role')    
            agent = session.query(Agents).filter(and_(Agents.email==email,Agents.password==password)).first()
            if agent is None:
                resp.body =  json.dumps({"result":{"success":"false","message":"Invalid User"}}, encoding='utf-8')
            else:
                print agent.id
                checkRole = session.query(Agent_Roles.id).filter(and_(Agent_Roles.agent_id==agent.id,Agent_Roles.role==role)).first()
                if checkRole is None:
                    resp.body =  json.dumps({"result":{"success":"false","message":"Invalid Role"}}, encoding='utf-8')
                else:
                    agent.last_login = datetime.now()
                    agent.login_type = role
                    resp.body =  json.dumps({"result":{"success":"true","message":"Valid User","id":agent.id}}, encoding='utf-8')
                    session.commit()
                    #session.query(Agents).filter_by(id = checkUser[0]).    
        finally:
            session.close()    
            
    def test(self,email,password,role):
        checkUser = session.query(Agents.id).filter(and_(Agents.email==email,Agents.password==password)).first()
        if checkUser is None:
            print checkUser

        else:
            print checkUser[0]
            checkRole = session.query(Agent_Roles.id).filter(and_(Agent_Roles.agent_id==checkUser[0],Agent_Roles.role==role)).first()
            if checkRole is None:
                pass
            else:
                agents=AgentLoginTimings()
                agents.loginTime=datetime.now()
                agents.logoutTime=datetime.now()
                agents.role =role
                agents.agent_id = 2
                #session.query(AgentLoginTimings).filter_by(id = checkUser[0]).update({"last_login":datetime.now()}, synchronize_session=False)
                session.add(agents)    
                session.commit()
                session.close()
                
                #session.query(Agents).filter(Agents.id==checkUser[0]).update({"last_login":Agents.last_login})

class RetailerActivation():
    def on_get(self, req, resp, userId):
        res =  markDealerActivation(int(userId))
        if res:
            resp.body = "{\"activated\":true}"
        else:
            resp.body = "{\"activated\":false}"
            

def markDealerActivation(userId):
    try:
        retailerLink = session.query(RetailerLinks).filter_by(user_id = userId).first()
        if retailerLink is not None:
            retailer = session.query(Retailers).filter_by(id = retailerLink.retailer_id).first()
            retailerLink.mapped_with='contact'
            retailer.status = 'onboarding'
            result = True
            session.commit()
            return result
            
        user = session.query(Users).filter_by(id=userId).first()
        result = False                
        mappedWith = 'contact'
        retailer = None
        if user is not None:
            referrer = None if user.referrer is None else user.referrer.upper()
            retailerLink = session.query(RetailerLinks).filter(or_(RetailerLinks.code==referrer, RetailerLinks.code==user.utm_campaign)).first()
            if retailerLink is None:
                if user.mobile_number is not None:
                    retailerContact = session.query(RetailerContacts).filter_by(mobile_number=user.mobile_number).first()
                    if retailerContact is None:
                        retailer = session.query(Retailers).filter(Retailers.status.in_(['followup', 'fretry', 'fdone'])).filter(or_(Retailers.contact1==user.mobile_number,Retailers.contact2==user.mobile_number)).first()
                    else:
                        retailer = session.query(Retailers).filter_by(id = retailerContact.retailer_id).first()
            else:
                retailer = session.query(Retailers).filter_by(id = retailerLink.retailer_id).first()
                mappedWith='code'
            if retailer is not None:
                retailerLink = session.query(RetailerLinks).filter_by(retailer_id=retailer.id).first()
                if retailerLink is not None:
                    retailerLink.user_id = user.id
                    retailerLink.mapped_with=mappedWith
                    retailer.status = 'onboarding'
                    result = True
                    session.commit()
        return result
    except:
        traceback.print_exc()
    finally:
        session.close()
                    
            
def todict(obj, classkey=None):
    if isinstance(obj, dict):
        data = {}
        for (k, v) in obj.items():
            data[k] = todict(v, classkey)
        return data
    elif hasattr(obj, "_ast"):
        return todict(obj._ast())
    elif hasattr(obj, "__iter__"):
        return [todict(v, classkey) for v in obj]
    elif hasattr(obj, "__dict__"):
        data = dict([(key, todict(value, classkey)) 
            for key, value in obj.__dict__.iteritems() 
            if not callable(value) and not key.startswith('_')])
        if classkey is not None and hasattr(obj, "__class__"):
            data[classkey] = obj.__class__.__name__
        return data
    else:
        return obj

def getRetailerObj(retailer, otherContacts1=None, callType=None):
    print "before otherContacts1",otherContacts1
    otherContacts = [] if otherContacts1 is None else otherContacts1
    print "after otherContacts1",otherContacts
    obj = Mock()
    obj.id = retailer.id
    
        
    if retailer.contact1 is not None and retailer.contact1 not in otherContacts:
        otherContacts.append(retailer.contact1)
    if retailer.contact2 is not None and retailer.contact2 not in otherContacts:
        otherContacts.append(retailer.contact2)
    obj.contact1 = None if len(otherContacts)==0 else otherContacts[0]
    if obj.contact1 is not None:
        obj.contact2 = None if len(otherContacts)==1 else otherContacts[1]
    obj.scheduled = (retailer.call_priority is not None)
    address = None
    try:
        address = session.query(RetailerAddresses).filter_by(retailer_id=retailer.id).order_by(RetailerAddresses.created.desc()).first()
    finally:
        session.close()
    if address is not None:
        obj.address = address.address
        obj.title = address.title
        obj.city = address.city
        obj.state = address.state
        obj.pin = address.pin 
    else:
        obj.address = retailer.address_new if retailer.address_new is not None else retailer.address
        obj.title = retailer.title
        obj.city = retailer.city
        obj.state = retailer.state
        obj.pin = retailer.pin 
    obj.status = retailer.status
    obj.tinnumber = retailer.tinnumber
    obj.isvalidated = retailer.isvalidated    
    if hasattr(retailer, 'contact'):
        obj.contact = retailer.contact
    if callType == 'followup':
        obj.last_call_time = datetime.strftime(retailer.modified, "%B %Y")
    if callType == 'onboarding':
        try:
            userId, activatedTime = session.query(RetailerLinks.user_id, RetailerLinks.activated).filter(RetailerLinks.retailer_id==retailer.id).first()
            activated, = session.query(Users.activation_time).filter(Users.id==userId).first()
            if activated is not None:
                activatedTime = activated
            obj.user_id = userId
            obj.created = datetime.strftime(activatedTime, '%d/%m/%Y %H:%M:%S')
            result = fetchResult("select * from useractive where user_id=%d"%(userId))
            if result == ():
                obj.last_active = None
            else:
                obj.last_active =datetime.strftime(result[0][1], '%d/%m/%Y %H:%M:%S')
            ordersCount = session.query(Orders).filter_by(user_id = userId).filter(~Orders.status.in_(['ORDER_NOT_CREATED_KNOWN', 'ORDER_ALREADY_CREATED_IGNORED'])).count()
            obj.orders = ordersCount
        finally:
            session.close()
    return obj

def make_tiny(code):
    url = 'https://play.google.com/store/apps/details?id=com.saholic.profittill&referrer=utm_source%3D0%26utm_medium%3DCRM%26utm_term%3D001%26utm_campaign%3D' + code
    #url='https://play.google.com/store/apps/details?id=com.saholic.profittill&referrer=utm_source=0&utm_medium=CRM&utm_term=001&utm_campaign='+code
    #marketUrl='market://details?id=com.saholic.profittill&referrer=utm_source=0&utm_medium=CRM&utm_term=001&utm_campaign='+code
    #url = urllib.quote(marketUrl)
    #request_url = ('http://tinyurl.com/api-create.php?' + urlencode({'url':url}))
    #filehandle = urllib2.Request(request_url)
    #x= urllib2.urlopen(filehandle)
    try:
        shortener = Shortener('TinyurlShortener')
        returnUrl =  shortener.short(url)
    except:
        shortener = Shortener('SentalaShortener')
        returnUrl =  shortener.short(url)
    return returnUrl

class SearchUser():
            
    def on_post(self, req, resp, agentId, searchType):
        retailersJsonArray = []
        try:
            jsonReq = json.loads(req.stream.read(), encoding='utf-8')
            lgr.info( "Request in Search----\n"  + str(jsonReq))
            contact=jsonReq.get('searchTerm')
            if(searchType=="number"):
                retailer_ids = session.query(RetailerContacts.retailer_id).filter_by(mobile_number=contact).all()
                retailer_ids = [r for r, in retailer_ids]    
                anotherCondition = or_(Retailers.contact1==contact,Retailers.contact2==contact, Retailers.id.in_(retailer_ids))
            else:
                m = re.match("(.*?)(\d{6})(.*?)", contact)
                if m is not None:
                    pin = m.group(2)
                    contact = m.group(1) if m.group(1) != '' else m.group(3)
                    anotherCondition = and_(Retailers.title.ilike('%%%s%%'%(contact)), Retailers.pin==pin)
                else:
                    anotherCondition = Retailers.title.ilike('%%%s%%'%(contact))
            
            retailers = session.query(Retailers).filter(anotherCondition).filter(Retailers.isvalidated==1).limit(20).all()
            if retailers is None:
                resp.body = json.dumps("{}")
            else:
                for retailer in retailers:
                    otherContacts = [r for r, in session.query(RetailerContacts.mobile_number).filter_by(retailer_id=retailer.id).order_by(RetailerContacts.contact_type).all()]
                    retailersJsonArray.append(todict(getRetailerObj(retailer, otherContacts)))    
            resp.body = json.dumps({"Retailers":retailersJsonArray}, encoding='utf-8')
        finally:
            session.close()

    
class Mock(object):
    pass

def tagActivatedReatilers():
    retailerIds = [r for  r, in session.query(RetailerLinks.retailer_id).filter_by(user_id = None).all()]
    session.close()
    for retailerId in retailerIds:
        isActivated(retailerId)
    session.close()

class StaticDeals():
    
    def on_get(self, req, resp):

        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        categoryId = req.get_param_as_int("categoryId")
        direction = req.get_param_as_int("direction")
        
        result = Mongo.getStaticDeals(offset, limit, categoryId, direction)
        resp.body = dumps(result)

class DealNotification():
    
    def on_get(self,req,resp,skuBundleIds):
        result = Mongo.getDealsForNotification(skuBundleIds)
        resp.body = dumps(result)

class DealPoints():
    
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllBundlesWithDealPoints(offset, limit)
        resp.body = dumps(result)

    
    def on_post(self, req, resp):
        
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addDealPoints(result_json)
        resp.body = json.dumps(result, encoding='utf-8')

class AppAffiliates():
    
    def on_get(self, req, resp, retailerId, appId):
        retailerId = int(retailerId)
        appId = int(appId)
        call_back = req.get_param("callback")
        result = Mongo.generateRedirectUrl(retailerId, appId)
        resp.body = call_back+'('+str(result)+')'

class AffiliatePayout():
    def on_get(self, req, resp):
        payout = req.get_param("payout")
        transaction_id = req.get_param("transaction_id")
        result = Mongo.addPayout(payout, transaction_id)
        resp.body = str(result)
    
class AppOffers():
    def on_get(self, req, resp, retailerId):
        try:            
            result = Mongo.getAppOffers(retailerId)
            offerids = result.values()
            if offerids is None or len(offerids)==0:
                resp.body = json.dumps("{}")
            else:
                appOffers = session.query(app_offers.id,app_offers.appmaster_id, app_offers.app_name, app_offers.affiliate_offer_id, app_offers.image_url, app_offers.downloads, app_offers.link, app_offers.offer_price, app_offers.offerCategory, app_offers.package_name, app_offers.promoImage, app_offers.ratings, case([(app_offers.override_payout == True, app_offers.overriden_payout)], else_=app_offers.user_payout).label('user_payout'), case([(appmasters.shortDescription != None, appmasters.shortDescription)], else_=None).label('shortDescription'), case([(appmasters.longDescription != None, appmasters.longDescription)], else_=None).label('longDescription'), appmasters.customerOneLiner, appmasters.retailerOneLiner,appmasters.rank, app_offers.offerCondition, app_offers.location).join((appmasters,appmasters.id==app_offers.appmaster_id)).filter(app_offers.id.in_(tuple(offerids))).all()
                appOffersMap = {}
                jsonOffersArray=[]
                for offer in appOffers:
                    appOffersMap[long(offer[0])]= AppOfferObj(offer[0], offer[1], offer[2], offer[3], offer[4], offer[5], offer[6], offer[7], offer[8], offer[9], offer[10], offer[11], offer[12], offer[13], offer[14], offer[15], offer[16], offer[17], offer[18], offer[19]).__dict__
                for rank in sorted(result):
                    print 'Rank', rank, 'Data', appOffersMap[result[rank]]
                    jsonOffersArray.append(appOffersMap[result[rank]])
            
            resp.body = json.dumps({"AppOffers":jsonOffersArray}, encoding='latin1')
        finally:
            session.close()
            
        
class AppUserBatchRefund():
    def on_get(self, req, resp, batchId, userId):
        try:
            batchId = long(batchId)
            userId = long(userId)
            userBatchCashback = user_app_cashbacks.get_by(user_id=userId, batchCreditId=batchId)
            if userBatchCashback is None:
                resp.body = json.dumps("{}")
            else:
                if userBatchCashback.creditedDate is not None:
                    userBatchCashback.creditedDate = str(userBatchCashback.creditedDate)
                resp.body = json.dumps(todict(userBatchCashback), encoding='utf-8')
        finally:
            session.close()
          
class AppUserBatchDrillDown():
    def on_get(self, req, resp, fortNightOfYear, userId, yearVal):
        try:
            fortNightOfYear = long(fortNightOfYear)
            userId = long(userId)
            yearVal = long(yearVal)
            appUserBatchDrillDown = session.query(user_app_installs.transaction_date, func.sum(user_app_installs.installCount).label('downloads'), func.sum(user_app_installs.payoutAmount).label('amount')).join((user_app_cashbacks,user_app_cashbacks.user_id==user_app_installs.user_id)).filter(user_app_cashbacks.fortnightOfYear==user_app_installs.fortnightOfYear).filter(user_app_cashbacks.user_id==userId).filter(user_app_cashbacks.yearVal==yearVal).filter(user_app_cashbacks.fortnightOfYear==fortNightOfYear).group_by(user_app_installs.transaction_date).all()
            cashbackArray = []
            if appUserBatchDrillDown is None or len(appUserBatchDrillDown)==0:
                resp.body = json.dumps("{}")
            else:
                for appcashBack in appUserBatchDrillDown:
                    userAppBatchDrillDown = UserAppBatchDrillDown(str(appcashBack[0]),long(appcashBack[1]), long(appcashBack[2]))
                    cashbackArray.append(todict(userAppBatchDrillDown))
                resp.body = json.dumps({"UserAppCashBackInBatch":cashbackArray}, encoding='utf-8')
        finally:
            session.close()
            
class AppUserBatchDateDrillDown():
    def on_get(self, req, resp, userId, date):
        try:
            userId = long(userId)
            date = str(date)
            date = datetime.strptime(date, '%Y-%m-%d')
            appUserBatchDateDrillDown = session.query(user_app_installs.app_name, func.sum(user_app_installs.installCount).label('downloads'), func.sum(user_app_installs.payoutAmount).label('amount')).filter(user_app_installs.user_id==userId).filter(user_app_installs.transaction_date==date).group_by(user_app_installs.app_name).all()
            cashbackArray = []
            if appUserBatchDateDrillDown is None or len(appUserBatchDateDrillDown)==0:
                resp.body = json.dumps("{}")
            else:
                for appcashBack in appUserBatchDateDrillDown:
                    userAppBatchDateDrillDown = UserAppBatchDateDrillDown(str(appcashBack[0]),long(appcashBack[1]),long(appcashBack[2]))
                    cashbackArray.append(todict(userAppBatchDateDrillDown))
                resp.body = json.dumps({"UserAppCashBackDateWise":cashbackArray}, encoding='utf-8')
        finally:
            session.close()
            
class AppUserCashBack():
    def on_get(self, req, resp, userId, status):
        try:
            userId = long(userId)
            status = str(status)
            appUserApprovedCashBacks = user_app_cashbacks.query.filter(user_app_cashbacks.user_id==userId).filter(user_app_cashbacks.status==status).all()
            cashbackArray = []
            if appUserApprovedCashBacks is None or len(appUserApprovedCashBacks)==0:
                resp.body = json.dumps("{}")
            else:
                totalAmount = 0                
                for appUserApprovedCashBack in appUserApprovedCashBacks:
                    totalAmount = totalAmount + appUserApprovedCashBack.amount
                    if appUserApprovedCashBack.creditedDate is not None:
                        appUserApprovedCashBack.creditedDate = str(appUserApprovedCashBack.creditedDate)  
                    cashbackArray.append(todict(appUserApprovedCashBack)) 
                                    
                resp.body = json.dumps({"UserAppCashBack":cashbackArray,"TotalAmount":totalAmount}, encoding='utf-8')
        finally:
            session.close()
            
class GetSaleDetail():
    def on_get(self,req,res,date_val):
        try:
                db = get_mongo_connection()
                sum=0
                count=0
                #print date_val, type(date_val)
                #cursor = db.Dtr.merchantOrder.find({'createdOnInt':{'$lt':long(date_val)},'subOrders':{'$exists':True}})
                cursor = db.Dtr.merchantOrder.find({"$and": [ { "createdOnInt":{"$gt":long(date_val)} }, {"createdOnInt":{"$lt":long(date_val)+86401} } ],"subOrders":{"$exists":True}})
                
                for document in cursor:
                    for doc in document['subOrders']:
                        sum = sum + float(doc['amountPaid'])
                        count = count + int(doc['quantity'])

                data = {"amount":sum , "quantity":count}

                res.body= json.dumps(data,encoding='utf-8')
        finally:
                session.close()

class DummyDeals():
    def on_get(self,req, resp):
        categoryId = req.get_param_as_int("categoryId")
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        result = Mongo.getDummyDeals(categoryId, offset, limit)
        resp.body = dumps(result)

class PincodeValidation():
    def on_get(self,req,resp,pincode):
        json_data={}
        cities=[]
        print pincode
        if len(str(pincode)) ==6:
            listCities = list(session.query(Postoffices.taluk,Postoffices.state).distinct().filter(Postoffices.pincode==pincode).all())
            if len(listCities)>0:
                for j in listCities:
                    if j.taluk != 'NA':
                        cities.append(j.taluk)
                json_data['cities'] = cities
                json_data['state'] = listCities[0][1]
                resp.body =  json.dumps(json_data)
            else:
                resp.body = json.dumps("{}")       
        else:
            resp.body = json.dumps("{}")
        session.close()

class SearchDummyDeals():
    def on_get(self,req,resp):
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        searchTerm = req.get_param("searchTerm")
        result = Mongo.searchDummyDeals(searchTerm, limit, offset)
        resp.body = dumps(result)

class DummyPricing:
    def on_get(self, req, resp):
        skuBundleId = req.get_param_as_int("skuBundleId")
        result = Mongo.getDummyPricing(skuBundleId)
        resp.body = dumps(result)

class DealObject:
    def on_post(self, req, resp):
        update = req.get_param_as_int("update")
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
        if update is None:    
            result = Mongo.addDealObject(result_json)
        else:
            result = Mongo.updateDealObject(result_json)
            
        resp.body = dumps(result)
            
    
    def on_get(self, req, resp):
        edit = req.get_param_as_int("edit")
        if edit is None:
            offset = req.get_param_as_int("offset")
            limit = req.get_param_as_int("limit")
        
            result = Mongo.getAllDealObjects(offset, limit)
            resp.body = dumps(result)
        else:
            id = req.get_param_as_int("id")
            result = Mongo.getDealObjectById(id)
            resp.body = dumps(result)

class DeleteDealObject:
    def on_get(self, req, resp, id):
        result = Mongo.deleteDealObject(int(id))
        resp.body = dumps(result)

class FeaturedDealObject:
    def on_get(self, req, resp):
        
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getAllFeaturedDealsForDealObject(offset, limit)
        resp.body = dumps(result)

    
    def on_post(self, req, resp):
        
        
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.addFeaturedDealForDealObject(result_json)
        resp.body = json.dumps(result, encoding='utf-8')

class DeleteFeaturedDealObject:
    def on_get(self, req, resp, id):
        result = Mongo.deleteFeaturedDealObject(int(id))
        resp.body = dumps(result)

class SubCategoryFilter:
    def on_get(self, req, resp):
        category_id = req.get_param_as_int("category_id")
        result = Mongo.getSubCategoryForFilter(category_id)
        resp.body = dumps(result)

class DummyLogin:
    def on_post(self,req,resp):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.dummyLogin(result_json)
        resp.body = json.dumps(result, encoding='utf-8')

class DummyRegister:
    def on_post(self,req,resp):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.dummyRegister(result_json)
        resp.body = json.dumps(result, encoding='utf-8')

class TinSearch:
    def on_get(self,req,resp):
        tin = req.get_param("tin")
        result = getTinInfo(tin)
        resp.body = json.dumps(result, encoding='utf-8')
        

def getTinInfo(tin):
    try:
        curData = session.query(tinxys_stats).filter_by(req_date=date.today()).first()
        if not curData:
            curData = tinxys_stats()
            curData.total = 0
            curData.request_failed = 0
            curData.invalid_tin = 0
        curData.total += 1
        tinInfo = {"isError":False}
        try:
            params = ['tin','cst','counter_name','counter_address','state','pan','registered_on','cst_status','valid_since']
            url  = "http://www.tinxsys.com/TinxsysInternetWeb/dealerControllerServlet?tinNumber=" + tin + "&searchBy=TIN"
            req = urllib2.Request(url)
            try:
                connection = urllib2.urlopen(req, timeout=10)
            except:
                curData.request_failed += 1
                tinInfo = {"isError":True,
                        "errorMsg": "Some problem occurred. Try again after some time"
                }
                return tinInfo
            pageHtml = connection.read()
            connection.close()        
            doc = pq(pageHtml)
            index=-1
            for ele in pq(doc.find('table')[2])('tr td:nth-child(2)'):
                index += 1
                text = pq(ele).text().strip()
                if not text:
                    text = pq(ele)('div').text().strip()
                tinInfo[params[index]] = text
            print index, "index"
            if index != 8:
                raise
            if not get_mongo_connection().Dtr.tin.find({"tin":tinInfo['tin']}):
                get_mongo_connection().Dtr.tin.insert(tinInfo)
            address = tinInfo['counter_address']
            m = re.match(".*?(\d{6}).*?", address)
            if m:
                tinInfo['pin'] = m.group(1)
                tinInfo['pin_required'] = False
            else:
                tinInfo['pin_required'] = True
    
            print "TinNumber: Successfully fetched details", tin
        except:
            curData.invalid_tin = 1
            traceback.print_exc()
            tinInfo = {"isError":True,
                        "errorMsg": "Could not find tin"
            }
            print "TinNumber: Problem fetching details", tin
        session.commit()
        return tinInfo
    finally:
        session.close()
        
        
class UpdateUser:
    def on_post(self,req,resp):
        try:
            result_json = json.loads(req.stream.read(), encoding='utf-8')
        except ValueError:
            raise falcon.HTTPError(falcon.HTTP_400,
                'Malformed JSON',
                'Could not decode the request body. The '
                'JSON was incorrect.')
            
        result = Mongo.updateDummyUser(result_json)
        resp.body = json.dumps(result, encoding='utf-8')

class FetchUser:
    def on_get(self,req,resp):
        user_id = req.get_param_as_int("user_id")
        result = Mongo.getDummyUser(user_id)
        resp.body = json.dumps(result, encoding='utf-8')

class SearchSubCategory:
    def on_get(self,req, resp):
        subCategoryIds = req.get_param("subCategoryId")
        searchTerm = req.get_param("searchTerm")
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        
        result = Mongo.getDealsForSearchText(subCategoryIds, searchTerm,offset, limit)
        resp.body = json.dumps(result, encoding='utf-8')

class SearchSubCategoryCount:
    def on_get(self,req, resp):
        subCategoryIds = req.get_param("subCategoryId")
        searchTerm = req.get_param("searchTerm")
        result = Mongo.getCountForSearchText(subCategoryIds, searchTerm)
        resp.body = json.dumps(result, encoding='utf-8')

class MessageEncryption:
        
    def on_get(self,req,resp):
        message_type = req.get_param("type")
        if message_type == 'encrypt':
            encryption_data = req.get_param("data")
            encrypted_data = encryptMessage(base64.decodestring(encryption_data))
            resp.body =  json.dumps({"result":{"value":encrypted_data}}, encoding='utf-8')
            
        elif message_type == 'decrypt':
            decryption_data = req.get_param("data")
            decrypted_data = decryptMessage(decryption_data)
            resp.body =  json.dumps({"result":{"value":decrypted_data}}, encoding='utf-8')
            
        else:
            resp.body = json.dumps({}, encoding='utf-8')

class GetUserCrmApplication:
            
    def on_get(self,req,resp):
        global USER_DETAIL_MAP
        project_name = req.get_param("project_name")
        USER_DETAIL_MAP[project_name]+=1
        lgr.info( "USER_DETAIL_CALL_COUNTER " +  str(USER_DETAIL_MAP[project_name]))
        retryFlag=False
        if USER_DETAIL_MAP[project_name] % TOTAL_USER >= USER_CRM_DEFAULT_FRESH_FACTOR:
                retryFlag=True
        if project_name == 'accs_cart':
            project_id=1
        elif project_name == 'accs_active':
            project_id=2
        elif project_name == 'accs_order':
            project_id=3
        elif project_name == 'accs_cashback_scheme':
            project_id=4
        elif project_name == 'inactive_users':
            project_id=5
        if retryFlag:
            user = self.getRetryUser(project_id)
        else:
            user = self.getNewUser(project_id)
        
        if user is None:
            resp.body ={}
        else:        
            user.status =  'assigned'
            user.agent_id = req.get_param("agent_id")
            user.counter=1
            user.modified = datetime.now()
            session.commit()
            resp.body = json.dumps(todict(getUserObject(user)), encoding='utf-8')
            session.close()
                    
    def getRetryUser(self,projectId,failback=True):
        status = "retry"
        user = session.query(UserCrmCallingData).filter_by(status=status,project_id=projectId).filter(UserCrmCallingData.next_call_time<=datetime.now()).filter(~(UserCrmCallingData.contact1).like('')).order_by(UserCrmCallingData.next_call_time).with_lockmode("update").first()
        
        if user is not None:
            lgr.info( "getRetryUser " + str(user.id))
        else:
            if failback:
                user = self.getNewUser(projectId,False)
                return user
            else:
                return None
        return user
    
    
    def getNewUser(self,projectId,failback=True):
            user = None 
            try:
                user = session.query(UserCrmCallingData).filter_by(project_id=projectId).filter(UserCrmCallingData.status=='new').filter(UserCrmCallingData.pincode_servicable==True).filter(UserCrmCallingData.user_available==0).filter(UserCrmCallingData.contact1!=None).filter(~(UserCrmCallingData.contact1).like('')).order_by(UserCrmCallingData.next_call_time).order_by(UserCrmCallingData.id).with_lockmode("update").first()
                if user is None:
                    insertUserCrmData(projectId)
                    user = session.query(UserCrmCallingData).filter_by(project_id=projectId).filter(UserCrmCallingData.status=='new').filter(UserCrmCallingData.pincode_servicable==True).filter(~(UserCrmCallingData.contact1).like('')).filter(UserCrmCallingData.user_available==0).filter(UserCrmCallingData.contact1!=None).order_by(UserCrmCallingData.next_call_time).order_by(UserCrmCallingData.id).with_lockmode("update").first()
                    if user is not None:
                        lgr.info( "getNewUser " + str(user.id))
                    else:
                        if failback:
                            user = self.getRetryUser(projectId,False)
                            return user
                        else:
                            return None
            except:
                print traceback.print_exc()
            return user    
                
    def on_post(self, req, resp):
        returned = False
        self.agentId = req.get_param("agent_id")
        project_name = req.get_param("project_name")
        
        if project_name == 'accs_cart':
            project_id=1
        elif project_name == 'accs_active':
            project_id=2            
        elif project_name == 'accs_order':
            project_id=3            
        elif project_name == 'accs_cashback_scheme':
            project_id=4
        elif project_name == 'inactive_users':
            project_id=5
            
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        lgr.info( "Request ----\n"  + str(jsonReq))
        self.jsonReq = jsonReq
        self.callType="default"
        callLaterAccs = self.callLaterAccs
        invalidNumber = self.invalidNumber
        accsDisposition = self.accsDisposition
        accsOrderDisposition=self.accsOrderDisposition
        otherreason = self.otherreason
        willplaceorder = self.willplaceorder
        verifiedLinkSent = self.verifiedLinkSent
        orderdiffaccount = self.orderdiffaccount
        businessclosed = self.businessclosed
        appuninstalled = self.appuninstalled
        self.userId = int(jsonReq.get('user_id'))
        try:
            self.user = session.query(UserCrmCallingData).filter_by(user_id=self.userId).filter(UserCrmCallingData.project_id==project_id).first()
            self.callDisposition = jsonReq.get('calldispositiontype')
            self.callHistoryCrm = CallHistoryCrm()
            self.callHistoryCrm.agent_id=self.agentId
            self.callHistoryCrm.project_id = project_id
            self.callHistoryCrm.call_disposition = self.callDisposition
            self.callHistoryCrm.user_id=self.userId
            self.callHistoryCrm.duration_sec = int(jsonReq.get("callduration"))
            self.callHistoryCrm.disposition_comments = jsonReq.get('calldispositioncomments')
            self.callHistoryCrm.call_time = datetime.strptime(jsonReq.get("calltime"), '%d/%m/%Y %H:%M:%S')
            self.callHistoryCrm.mobile_number = jsonReq.get('number')
            self.inputs = jsonReq.get("inputs")
            dispositionMap = {  'call_later':callLaterAccs,
                        'ringing_no_answer':callLaterAccs,
                        'not_reachable':callLaterAccs,
                        'switch_off':callLaterAccs,
                        'technical_issue':accsDisposition,
                        'pricing_issue':accsDisposition,
                        'shipping_issue':accsDisposition,
                        'internet_issue':accsDisposition,
                        'checking_price':accsDisposition,
                        'order_process':accsDisposition,
                        'placed_order':accsDisposition,
                        'place_order':accsDisposition,
                        'product_availability':accsOrderDisposition,
                        'return_replacement':accsOrderDisposition,
                        'already_purchased':accsOrderDisposition,
                        'product_quality_issue':accsOrderDisposition,
                        'delayed_delivery':accsOrderDisposition,
                        'other_complaint':accsOrderDisposition,
                        'scheme_not_clear':accsOrderDisposition,
                        'not_dealing_accessories':accsOrderDisposition,
                        'other_reason':otherreason,
                        'app_uninstalled':appuninstalled,
                        'will_place_order':willplaceorder,
                        'order_diff_acc':orderdiffaccount,
                        'not_retailer':invalidNumber,
                        'business_closed':businessclosed,
                        'invalid_no':invalidNumber,
                        'wrong_no':invalidNumber,
                        'hang_up':invalidNumber,
                        'service_center_retailer':invalidNumber,
                        'recharge_retailer':invalidNumber,
                        'verified_link_sent':verifiedLinkSent
                        
                        
                      }
            returned = dispositionMap[jsonReq.get('calldispositiontype')]()
        finally:
            session.close()
        
        if returned:
            resp.body = "{\"result\":\"success\"}"
        else:
            resp.body = "{\"result\":\"failed\"}"
                        
    def verifiedLinkSent(self,):
        users_referrer, = session.query(Users.referrer).filter_by(id=self.userId).first()
        if users_referrer is not None:
            activationCode=session.query(Activation_Codes).filter_by(code=users_referrer).first()
            if activationCode is not None:
                activationCode.status = False
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is not None:
            self.callHistoryCrm.disposition_description = 'App link sent via ' + self.callHistoryCrm.disposition_description
        else: 
            self.callHistoryCrm.disposition_description = 'App link sent'
        session.commit()
        return True
    
    
    def accsOrderDisposition(self):
        self.user.status='done'
        self.user.user_available = 1
        self.user.disposition=self.callDisposition
        self.user.modified = datetime.now()
        a = CrmTicketDtr()
        if self.callDisposition == 'product_availability':
            self.callHistoryCrm.disposition_description='Product Not available'
        elif self.callDisposition == 'return_replacement':
            self.callHistoryCrm.disposition_description='Return or replacement pending'
            #utils.sendCrmProjectMail(self.userId,self.callHistoryCrm.disposition_description,self.callHistoryCrm.disposition_comments)
            a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)
        elif self.callDisposition == 'already_purchased':
            self.callHistoryCrm.disposition_description='Already purchased required stock'
        elif self.callDisposition == 'scheme_not_clear':
            self.callHistoryCrm.disposition_description='Scheme Not Clear to the User'
        elif self.callDisposition == 'product_quality_issue':
            self.callHistoryCrm.disposition_description='Product Quality issue'
            #utils.sendCrmProjectMail(self.userId,self.callHistoryCrm.disposition_description,self.callHistoryCrm.disposition_comments)
            a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)            
        elif self.callDisposition == 'delayed_delivery':
            self.callHistoryCrm.disposition_description='Delayed Delivery' 
            #utils.sendCrmProjectMail(self.userId,self.callHistoryCrm.disposition_description,self.callHistoryCrm.disposition_comments)
            a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)   
        elif self.callDisposition == 'other_complaint':
            self.callHistoryCrm.disposition_description='Other complaint with profitmandi'
            #utils.sendCrmProjectMail(self.userId,self.callHistoryCrm.disposition_description,self.callHistoryCrm.disposition_comments)
            a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)
        elif self.callDisposition == 'not_dealing_accessories':
            self.callHistoryCrm.disposition_description='Not Dealing in Accessories/Not Interested'        
        session.commit()
        jdata = self.inputs
        jdata = json.loads(jdata)
        if jdata:
            for d in jdata:
                for key, value in d.iteritems():
                    productpricingInputs = ProductPricingInputs()
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.agent_id = self.callHistoryCrm.agent_id
                    productpricingInputs.disposition_id = self.callHistoryCrm.id
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.call_disposition = self.callHistoryCrm.call_disposition
                    productpricingInputs.project_id = self.callHistoryCrm.project_id
                    productpricingInputs.product_input = key
                    productpricingInputs.pricing_input = value
            session.commit()        
        return True
    
    def willplaceorder(self,):
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is None:
            self.callHistoryCrm.disposition_description = 'User will place order from now' 
        session.commit()
        return True
        
    def orderdiffaccount(self,):
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is None:
            self.callHistoryCrm.disposition_description = 'Placing Order from Different Account' 
        session.commit()
        return True
        
    
    def businessclosed(self,):
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is None:
            self.callHistoryCrm.disposition_description = 'Business Closed' 
        session.commit()
        return True
        
    
    def appuninstalled(self,):
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is None:
            self.callHistoryCrm.disposition_description = 'User uninstalled application' 
        a = CrmTicketDtr()
        a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)
        session.commit()
        return True
        
        
    def invalidNumber(self,):
 
        self.user.modified = datetime.now()
        if self.callDisposition == 'invalid_no':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Invalid Number'
        elif self.callDisposition == 'wrong_no':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Wrong Number'
        elif self.callDisposition == 'hang_up':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Hang Up'
        elif self.callDisposition == 'retailer_not_interested':

            self.user.disposition=self.callDisposition
            if self.callHistoryCrm.disposition_description is None:
                self.callHistoryCrm.disposition_description = 'NA'
            self.callHistoryCrm.disposition_description = 'Reason Retailer Not Interested ' + self.callHistoryCrm.disposition_description
        elif self.callDisposition == 'recharge_retailer':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Recharge related. Not a retailer '
        elif self.callDisposition == 'service_center_retailer':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Service Center related. Not a retailer'
        elif self.callDisposition == 'not_retailer':

            self.user.disposition=self.callDisposition
            self.callHistoryCrm.disposition_description = 'Not a retailer'    
        session.commit()
        return True  
    
    def otherreason(self,):
        self.user.status='done'
        self.user.modified = datetime.now()
        self.user.disposition=self.callDisposition
        if self.callHistoryCrm.disposition_description is None:
            self.callHistoryCrm.disposition_description = 'Other Reasons' 
        session.commit()
        return True
        
    
    def accsDisposition(self):
        self.user.status='done'
        self.user.user_available = 1
        self.user.disposition=self.callDisposition
        self.user.modified = datetime.now()
        if self.callDisposition == 'technical_issue':
            self.callHistoryCrm.disposition_description='Technical issue at Profitmandi'
            #utils.sendCrmProjectMail(self.userId,self.callHistoryCrm.disposition_description,self.callHistoryCrm.disposition_comments)
            a = CrmTicketDtr()
            a.addTicket(self.userId, self.callHistoryCrm.disposition_description, self.callHistoryCrm.disposition_comments)
        elif self.callDisposition == 'pricing_issue':
            self.callHistoryCrm.disposition_description='Pricing Issue(High)'
        elif self.callDisposition == 'shipping_issue':
            self.callHistoryCrm.disposition_description='Shipping charges related issue'
        elif self.callDisposition == 'internet_issue':
            self.callHistoryCrm.disposition_description='Internet issue at user end'
        elif self.callDisposition == 'checking_price':
            self.callHistoryCrm.disposition_description='Checking price'    
        elif self.callDisposition == 'order_process':
            self.callHistoryCrm.disposition_description='Order Process inquery'    
        elif self.callDisposition == 'placed_order':
            self.callHistoryCrm.disposition_description='Placed Order from Different Account'    
        elif self.callDisposition == 'place_order':
            self.callHistoryCrm.disposition_description='Will Place Order'            
        session.commit()
        jdata = self.inputs
        jdata = json.loads(jdata)
        if jdata:
            for d in jdata:
                for key, value in d.iteritems():
                    productpricingInputs = ProductPricingInputs()
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.agent_id = self.callHistoryCrm.agent_id
                    productpricingInputs.disposition_id = self.callHistoryCrm.id
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.call_disposition = self.callHistoryCrm.call_disposition
                    productpricingInputs.project_id = self.callHistoryCrm.project_id
                    productpricingInputs.product_input = key
                    productpricingInputs.pricing_input = value
            session.commit()        
        return True
        
    def callLaterAccs(self):
        self.user.status='retry'
        self.user.modified = datetime.now()
        if self.callDisposition == 'call_later':
            if self.callHistoryCrm.disposition_comments is not None:
                self.user.next_call_time = datetime.strptime(self.callHistoryCrm.disposition_comments, '%d/%m/%Y %H:%M:%S')
                self.callHistoryCrm.disposition_description = 'User requested to call'
                self.callHistoryCrm.disposition_comments = self.callHistoryCrm.disposition_comments
            else:
                self.user.next_call_time = self.callHistoryCrm.call_time + timedelta(days=1)
                self.callHistoryCrm.disposition_description = 'Call Scheduled'
                self.callHistoryCrm.disposition_comments = datetime.strftime(self.user.next_call_time, '%d/%m/%Y %H:%M:%S')
        else: 
            self.callHistoryCrm.disposition_description = 'User was not contactable'
            if self.callDisposition == 'ringing_no_answer':
                if self.user.disposition == 'ringing_no_answer':
                    self.user.retry_count += 1
                else:
                    self.user.disposition = 'ringing_no_answer'
                    self.user.retry_count = 1
            else:
                if self.user.disposition == 'ringing_no_answer':
                    pass
                else:
                    self.user.disposition = 'not_reachable'
                self.user.retry_count += 1
                self.user.invalid_retry_count += 1
                    
            retryConfig = session.query(RetryConfig).filter_by(call_type=self.callType, disposition_type=self.user.disposition, retry_count=self.user.retry_count).first()
            if retryConfig is not None:
                self.user.next_call_time = self.callHistoryCrm.call_time + timedelta(minutes = retryConfig.minutes_ahead)
                self.callHistoryCrm.disposition_description = 'Call scheduled on ' + datetime.strftime(self.user.next_call_time, '%d/%m/%Y %H:%M:%S')
            else:
                self.user.status = 'failed'
                self.user.user_available=1
                self.callHistoryCrm.disposition_description = 'Call failed as all attempts exhausted'
        self.user.disposition=self.callDisposition        
        session.commit()
        jdata =self.inputs
        jdata = json.loads(jdata)
        if jdata: 
            for d in jdata:
                for key, value in d.iteritems():
                    productpricingInputs = ProductPricingInputs()
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.agent_id = self.callHistoryCrm.agent_id
                    productpricingInputs.disposition_id = self.callHistoryCrm.id
                    productpricingInputs.user_id = self.callHistoryCrm.user_id
                    productpricingInputs.call_disposition = self.callHistoryCrm.call_disposition
                    productpricingInputs.project_id = self.callHistoryCrm.project_id
                    productpricingInputs.product_input = key
                    productpricingInputs.pricing_input = value
            session.commit()        
        return True
    
def insertUserCrmData(project_id):
    if project_id==1:  
        getCartDetailsUser()
    if project_id==2:  
        getCartTabsUser()
    if project_id==3:
        getAccsRepeatUsers(project_id)
    if project_id==4:
        getAccsSchemeCashback(project_id)
    if project_id==5:
        getInactiveUsers(project_id)

def getInactiveUsers(project_id):
    PRIOPRITIZING_USER_QUERY = "select d.user_id, count(d.user_id),x.totalorder from daily_visitors d join users u on d.user_id=u.id left join (select user_id,count(*) as totalorder from allorder group by user_id order by totalorder desc) as x on d.user_id=x.user_id where lower(u.referrer) not like 'emp%' and d.user_id not in (select user_id from daily_visitors where visited > '2016-03-01') group by d.user_id having count(*) >=5 order by x.totalorder desc, count(d.user_id) desc;"
    userFinalList=[]
    result = fetchResult(PRIOPRITIZING_USER_QUERY)
    for r in result:
        userFinalList.append(r[0])
    for i in userFinalList:
        try:
            userId=i
            userPresent = session.query(UserCrmCallingData).filter_by(user_id=userId).order_by(desc(UserCrmCallingData.modified)).first()
            if userPresent is not None:
                if userPresent.user_available==1:
                    if userPresent.project_id==project_id:
                        continue
                    elif userPresent.modified.date()>=(datetime.now().date()-timedelta(days=30)):
                        continue
                else:
                    continue         
            userMasterData = UserCrmCallingData()
            userMasterData.user_id = userId 
            userMasterData.name =getUsername(userId) 
            userMasterData.project_id = project_id
            userMasterData.user_available=0
            userMasterData.contact1 = getUserContactDetails(userId)
            userMasterData.counter = 0
            userMasterData.retry_count=0
            userMasterData.invalid_retry_count=0
            userMasterData.created = datetime.now()
            userMasterData.modified = datetime.now()
            userMasterData.status = 'new'
            userMasterData.pincode_servicable = checkPincodeServicable(userId)
            session.commit()
        except:
            print traceback.print_exc()
        finally:
            session.close()


def getAccsSchemeCashback(project_id):
    userMasterMap={}
    skipUserList=[]
    query = "select id from users where lower(referrer) in ('emp01','emp99','emp88')"
    skipUsersresult = fetchResult(query)
    for skipUser in skipUsersresult:
        skipUserList.append(skipUser[0])
    queryFilter = {"user_id":{"$nin":skipUserList}}
    result = get_mongo_connection().Catalog.PromoOffer.find(queryFilter)
    userMasterList =[]
    for r in result:
        userMasterList.append(r.get('user_id'))
    queryfilter ={"url":{"$regex" : "http://api.profittill.com/categories/target"}}
    result = get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilter).distinct('user_id')
    userSeenList=[]
    for r in result:
        userSeenList.append(r)
    finalUserList  = list(set(userMasterList) - set(userSeenList))
    queryFilter = {"user_id":{"$in":finalUserList}}
    result = get_mongo_connection().Catalog.PromoOffer.find(queryFilter)
    for r in result:
        userMasterMap[r.get('user_id')] = r.get('target2')
    d_sorted = sorted(zip(userMasterMap.values(), userMasterMap.keys()),reverse=True)
    counter=0
    for i in d_sorted:
        try:
            userId=i[1]
            if counter==20:
                break
            
#             userPresent = isUserAvailable(CRM_PROJECTS_USER_AVAILABILITY.get(project_id))
            userPresent = session.query(UserCrmCallingData).filter_by(user_id=userId).order_by(desc(UserCrmCallingData.modified)).first()
            if userPresent is not None:
                if userPresent.user_available==1:
                    if userPresent.project_id==project_id:
                        continue                        
                    elif userPresent.modified.date()>=(datetime.now().date()-timedelta(days=30)):
                        continue
                else:
                    continue               
            counter=counter+1
            userMasterData = UserCrmCallingData()
            userMasterData.user_id = userId 
            userMasterData.name =getUsername(userId) 
            userMasterData.project_id = project_id
            userMasterData.user_available=0
            userMasterData.contact1 = getUserContactDetails(userId)
            userMasterData.counter = 0
            userMasterData.retry_count=0
            userMasterData.invalid_retry_count=0
            userMasterData.created = datetime.now()
            userMasterData.modified = datetime.now()
            userMasterData.status = 'new'
            userMasterData.pincode_servicable = checkPincodeServicable(userId)
            session.commit()
        except:
            print traceback.print_exc()
        finally:
            session.close()
        
def getAccsRepeatUsers(project_id):
    
    usersMasterList=[]
    ACCS_ALL_ORDERS = "select distinct user_id from allorder where category in ('Accs','Accessories') and store_id='spice';"
    result = fetchResult(ACCS_ALL_ORDERS)
    for r in result:
        usersMasterList.append(r[0])
    
    ACCS_LAST_FIFTEEN_DAYS = "select distinct user_id from allorder where category in ('Accs','Accessories') and store_id='spice' and (date(created_on) between date(curdate() - interval 15 day) and date(curdate())) order by user_id;"
    result = fetchResult(ACCS_LAST_FIFTEEN_DAYS)
    for r in result:
        if r[0] in usersMasterList:
            usersMasterList.remove(r[0])        
    
    PRIOPRITIZING_USER_QUERY = "select user_id from allorder where category in ('Accs','Accessories')and store_id ='spice' and user_id in (%s)" % ",".join(map(str,usersMasterList)) + "group by user_id order by sum(amount_paid) desc;"
    userFinalList=[]
    result = fetchResult(PRIOPRITIZING_USER_QUERY)
    for r in result:
        userFinalList.append(r[0])
    
    counter=0
    for i in userFinalList:
        try:
            userId=i
            if counter==20:
                break
            userPresent = session.query(UserCrmCallingData).filter_by(user_id=userId).order_by(desc(UserCrmCallingData.modified)).first()
            if userPresent is not None:
                if userPresent.user_available==1:
                    if userPresent.project_id==project_id:
                        if userPresent.modified.date()>=(datetime.now().date()-timedelta(days=30)):
                            continue
                        else:
                            pass    
                    elif userPresent.modified.date()>=(datetime.now().date()-timedelta(days=30)):
                        continue
                else:
                    continue     
            counter=counter+1
            userMasterData = UserCrmCallingData()
            userMasterData.user_id = userId 
            userMasterData.name =getUsername(userId) 
            userMasterData.project_id = project_id
            userMasterData.user_available=0
            userMasterData.contact1 = getUserContactDetails(userId)
            userMasterData.counter = 0
            userMasterData.retry_count=0
            userMasterData.invalid_retry_count=0
            userMasterData.created = datetime.now()
            userMasterData.modified = datetime.now()
            userMasterData.status = 'new'
            userMasterData.pincode_servicable = checkPincodeServicable(userId)
            session.commit()
        except:
            print traceback.print_exc()
        finally:
            session.close()

def getCartDetailsUser():
    userList=[]
    orderUserList=[]
    #userMasterMap={}
    queryfilter = {"$and":
                   [
                    {'created':{"$gte":(to_java_date(datetime.now())-3*86400000)}},
                    {'created':{"$lte":(to_java_date(datetime.now())- 43200000)}},
                    {"url":{"$regex" : "http://api.profittill.com/cartdetails"}}
                    ]
                   }
    result = get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilter).distinct('user_id')
    
    for r in result:
        userList.append(r)
    
    myquery = "select a.user_id from allorder a join users u on a.user_id=u.id where a.store_id='spice' and (a.category='Accs' or a.category='Accessories') and u.referrer not like 'emp%%' and u.mobile_number IS NOT NULL and a.user_id in (%s)" % ",".join(map(str,userList)) + " UNION select id from users where lower(referrer) like 'emp%'"
    
    result = fetchResult(myquery)
    for r in result:
        orderUserList.append(r[0])
    finalUserList  = list(set(userList) - set(orderUserList))
    
    userCartIdMap={}
    fetchCartId = "select user_id,account_key from user_accounts where account_type='cartId' and user_id in (%s)" % ",".join(map(str,finalUserList))
    result = fetchResult(fetchCartId)
    for r in result:
        userCartIdMap[long(r[1])] = long(r[0])
    client = CatalogClient("catalog_service_server_host_prod","catalog_service_server_port").get_client()
    userMapReturned = client.getCartByValue(userCartIdMap.keys())
    userFinalList=[]
    for i in userMapReturned:
        userFinalList.append(userCartIdMap.get(i))
#     queryfilternew = {"$and":
#                    [
#                     {'user_id':{"$in":finalUserList}},
#                     {'created':{"$gte":(to_java_date(datetime.now())-2*86400000)}},
#                     {'created':{"$lte":(to_java_date(datetime.now())- 43200000)}},
#                     {"url":{"$regex" : "http://api.profittill.com/cartdetails"}}
#                     ]
#                    }
#     itemIds = list(get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilternew))
#      
#     for i in itemIds:
#         if(userMasterMap.has_key(i.get('user_id'))):
#             if userMasterMap.get(i.get('user_id')) > i.get('created'):
#                 userMasterMap[i.get('user_id')]=i.get('created')
#         else:
#             userMasterMap[i.get('user_id')]=i.get('created')
#             
#     d_sorted = sorted(zip(userMasterMap.values(), userMasterMap.keys()))
    addUserToTable(userFinalList,1)

def addUserToTable(userList,projectId):
    counter=0
    for i in userList:
        try:
            userId=i
            if counter==20:
                break
            userPresent = session.query(UserCrmCallingData).filter_by(user_id=userId).order_by(desc(UserCrmCallingData.modified)).first()
            if userPresent is not None:
                if userPresent.user_available==1:
                    if userPresent.project_id==projectId:
                        continue
                    elif userPresent.modified.date()>=(datetime.now().date()-timedelta(days=30)):
                        continue
                else:
                    continue     
            counter=counter+1
            userMasterData = UserCrmCallingData()
            userMasterData.user_id = userId 
            userMasterData.name =getUsername(userId) 
            userMasterData.project_id = projectId
            userMasterData.user_available=0
            userMasterData.contact1 = getUserContactDetails(userId)
            userMasterData.counter = 0
            userMasterData.retry_count=0
            userMasterData.invalid_retry_count=0
            userMasterData.created = datetime.now()
            userMasterData.modified = datetime.now()
            userMasterData.status = 'new'
            userMasterData.pincode_servicable = checkPincodeServicable(userId)
            session.commit()
        except:
            print traceback.print_exc()
        finally:
            session.close()    
    
def getCartTabsUser():
    userMasterList=[]
    userList = []
    userMasterMap={}
    queryfilter = {"$and":
                   [
                    {'created':{"$gte":(to_java_date(datetime.now())-2*86400000)}},
                    {'created':{"$lte":(to_java_date(datetime.now())- 43200000)}},
                    {"url":{"$regex" : "http://api.profittill.com/category/6"}}
                    ]
                   }
    result = get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilter).distinct('user_id')
    
    for r in result:
        userMasterList.append(r)
    
    queryfilterVisistedCart = {"$and":
                   [
                    {'user_id':{"$in":userMasterList}},
                    {"url":{"$regex" : "http://api.profittill.com/cartdetails"}}
                    ]
                  }
    result = get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilterVisistedCart).distinct('user_id')   
    for r in result:
        userList.append(r)
    
    myquery = "select user_id from allorder where store_id='spice' and (category='Accs' or category='Accessories') and user_id in (%s)" % ",".join(map(str,userMasterList)) + " UNION select id from users where lower(referrer) like 'emp%'"

    result = fetchResult(myquery)
    for r in result:
        if r[0] in userList:
            continue
        userList.append(r[0])
        
    finalUserList  = list(set(userMasterList) - set(userList))
    
    queryfilternew = {"$and":
                   [
                    {'user_id':{"$in":finalUserList}},
                    {'created':{"$gte":(to_java_date(datetime.now())-2*86400000)}},
                    {'created':{"$lte":(to_java_date(datetime.now())- 43200000)}},
                    {"url":{"$regex" : "http://api.profittill.com/category/6"}}
                    ]
                   }
    itemIds = list(get_mongo_connection_dtr_data().User.browsinghistories.find(queryfilternew))
    
    for i in itemIds:
        if(userMasterMap.has_key(i.get('user_id'))):
            if userMasterMap.get(i.get('user_id')) > i.get('created'):
                userMasterMap[i.get('user_id')]=i.get('created')
        else:
            userMasterMap[i.get('user_id')]=i.get('created')

    d_sorted = sorted(zip(userMasterMap.values(), userMasterMap.keys()))
    userFinalList=[]
    for i in d_sorted:
        userFinalList.append(i[1])
    addUserToTable(userFinalList,2)
    
def getUserContactDetails(userId):
    r = session.query(Users.mobile_number).filter_by(id=userId).first()
    if r is None:
        return None
    else:
        return r[0]
            
def getUsername(userId):
    r = session.query(Users.first_name).filter_by(id=userId).first()
    if r is None:
        return None
    else:
        return r[0]
 
def checkPincodeServicable(userId):
    checkAddressUser = "select distinct pincode from all_user_addresses where user_id= (%s)"
    result = fetchResult(checkAddressUser,userId)
    if len(result)==0:
        return True
    else:
        myquery = "select count(*) from (select distinct pincode from all_user_addresses where user_id= (%s))a join pincodeavailability p on a.pincode=p.code"
        result = fetchResult(myquery,userId)
        if result[0][0]==0:
            return False
        else:
            return True

def getUserObject(user):
    obj = Mock()
    obj.user_id = user.user_id
    result = fetchResult("select * from useractive where user_id=%d"%(user.user_id))
    if result == ():
        obj.last_active = None
    else:
        obj.last_active =datetime.strftime(result[0][1], '%d/%m/%Y %H:%M:%S')
    obj.name = user.name    
    obj.contact = user.contact1
    obj.lastOrderTimestamp=None
    
    details = session.query(Orders).filter_by(user_id = user.user_id).filter(~Orders.status.in_(['ORDER_NOT_CREATED_KNOWN','ORDER_NOT_CREATED_UNKNOWN', 'ORDER_ALREADY_CREATED_IGNORED'])).order_by(desc(Orders.created)).first()
    if details is None:
        obj.lastOrderTimestamp =None
    else:
        obj.lastOrderTimestamp =datetime.strftime(details.created, '%d/%m/%Y %H:%M:%S')
    obj.totalOrders = session.query(Orders).filter_by(user_id = user.user_id).filter(~Orders.status.in_(['ORDER_NOT_CREATED_KNOWN','ORDER_NOT_CREATED_UNKNOWN', 'ORDER_ALREADY_CREATED_IGNORED'])).count()

    cartResult = fetchResult("select account_key from user_accounts where account_type ='cartId' and user_id=%d"%(user.user_id))
    if cartResult == ():
        obj.lastActiveCartTime = None
    else:
        conn = MySQLdb.connect("192.168.190.114", "root","shop2020", "user")
        cursor = conn.cursor()
        cursor.execute("select updated_on from cart where id=%s",(cartResult[0][0]))
        result = cursor.fetchall()
        if result ==():
            obj.lastActiveCartTime = None
        else:
            print result
            obj.lastActiveCartTime =datetime.strftime(result[0][0], '%d/%m/%Y %H:%M:%S')
        conn.close()
        
    session.close()                
    return obj

class CrmTicketDtr():
    def on_post(self,req,resp):
        try:
            customerFeedbackBackMap = {}
            customerFeedbackBackMap['user_id']=req.get_param("user_id")
            user = session.query(Users).filter_by(id=customerFeedbackBackMap.get('user_id')).first()
            if user is not None:
                customerFeedbackBackMap['email']=user.email
                customerFeedbackBackMap['mobile_number']=user.mobile_number
                customerFeedbackBackMap['customer_name']=user.first_name + ' ' + user.last_name
                customerFeedbackBackMap['subject'] = req.get_param("subject")
                customerFeedbackBackMap['message'] = req.get_param("message")
                customerFeedbackBackMap['created'] = datetime.now()
                if ThriftUtils.generateCrmTicket(customerFeedbackBackMap):
                    resp.body={"result":"success"}
                else:
                    resp.body={"result":"failure"}    
            else:
                resp.body={"result":"failure"}
        except:
            print traceback.print_exc()
        finally:
            session.close()            
    def addTicket(self,user_id,subject,message):
        try:
            customerFeedbackBackMap = {}
            customerFeedbackBackMap['user_id']=user_id
            user = session.query(Users).filter_by(id=user_id).first()
            if user is not None:
                customerFeedbackBackMap['email']=user.email
                customerFeedbackBackMap['mobile_number']=user.mobile_number
                customerFeedbackBackMap['customer_name']=user.first_name + ' ' + user.last_name
                customerFeedbackBackMap['subject'] = subject
                customerFeedbackBackMap['message'] = message
                customerFeedbackBackMap['created'] = datetime.now()
                ThriftUtils.generateCrmTicket(customerFeedbackBackMap)
            else:
                print 'User is not present'
        except:
            print traceback.print_exc()                
            
class UnitDeal():
    def on_get(self,req,resp, id):
        result = Mongo.getDealById(id)
        resp.body = dumps(result)
        
class SpecialOffer():
    def on_get(self,req,resp):
        user_id = req.get_param_as_int('user_id')
        result = Mongo.getOfferForUser(user_id)
        resp.body = dumps(result)
        
class BrandSubCatFilter():
    def on_get(self, req, resp):
        category_id = req.get_param_as_int('category_id')
        id_list = req.get_param('id_list')
        type = req.get_param('type')
        result = Mongo.getBrandSubCategoryInfo(category_id, id_list, type)
        resp.body = dumps(result)

class RefundAmountWallet():
    def on_post(self,req,resp):
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        if Mongo.refundAmountInWallet(jsonReq):
            resp.body = "{\"result\":\"success\"}"
        else:
            resp.body = "{\"result\":\"failed\"}"

    def on_get(self,req,resp):
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        status = req.get_param("status")
        if status: 
            userRefundDetails = Mongo.fetchCrmRefundUsers(status,offset,limit)
        else:
            userRefundDetails = Mongo.fetchCrmRefundUsers(None,offset,limit)
        if userRefundDetails is not None:
            resp.body = dumps(userRefundDetails)
        else:
            resp.body ="{}"

class ProfitmandiBanners():
    def on_post(self,req,resp):
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        if Mongo.addProfitMandiBanner(jsonReq):
            resp.body = "{\"result\":\"success\"}"
        else:
            resp.body = "{\"result\":\"failed\"}"

    def on_get(self,req,resp, bannerId=None):
        if bannerId is None:
            banners = Mongo.fetchProfitmandiBanners()
            if banners is not None:
                resp.body = dumps(banners)
            else:
                resp.body ="{}"
        else:
            Mongo.removeProfitmandiBanners(bannerId)
            resp.body = "{\"result\":\"success\"}"
            

class RefundWalletStatus():
    def on_post(self,req,resp):
        status = req.get_param('status')
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        user_id = int(jsonReq.get('user_id'))
        _id = jsonReq.get('_id')
        if status == utils.REFUND_ADJUSTMENT_MAP.get(1):
            value = int(jsonReq.get('amount'))
            userAmountMap = {}
            datetimeNow = datetime.now()
            batchId = int(time.mktime(datetimeNow.timetuple()))
            userAmountMap[user_id] = value
            Mongo.updateCrmWalletStatus(status, _id,user_id,None,None)
            if refundToWallet(batchId,userAmountMap):
                refundType = jsonReq.get('type')
                approved_by = jsonReq.get('approved_by')
                get_mongo_connection().Dtr.refund.insert({"userId": user_id, "batch":batchId, "userAmount":value, "timestamp":datetime.strftime(datetimeNow,"%Y-%m-%d %H:%M:%S"), "type":refundType})
                get_mongo_connection().Dtr.user.update({"userId":user_id}, {"$inc": { "credited": value, refundType:value}}, upsert=True)
                Mongo.updateCrmWalletStatus(utils.REFUND_ADJUSTMENT_MAP.get(3), _id,user_id,batchId,approved_by)
                print user_id,value                
                Mongo.sendNotification([user_id], 'Batch Credit', 'Cashback Credited for %ss'%(str(refundType)), 'Rs.%s has been added to your wallet'%(value),'url', 'http://api.profittill.com/cashbacks/mine?user_id=%s'%(user_id), '2999-01-01', True, "TRAN_SMS Dear Customer, Cashback Credited for %ss. Rs.%s has been added to your wallet"%(refundType,value))
                resp.body = "{\"result\":\"success\"}"
            else:
                resp.body = "{\"result\":\"failed\"}"
        elif status == utils.REFUND_ADJUSTMENT_MAP.get(2):
            Mongo.updateCrmWalletStatus(status, _id,user_id,None,None)
            resp.body = "{\"result\":\"success\"}"
        else:
            resp.body = "{\"result\":\"failed\"}"
            
class DetailsBatchId():
    def on_get(self,req,resp):
        batchId = req.get_param('batchId')
        userRefundDetails = Mongo.fetchCrmRefundByBatchId(batchId)        
        if userRefundDetails is not None:
            resp.body = dumps(list(userRefundDetails))
        else:
            resp.body ="{}"

class HeaderLinks():
    def on_get(self, req, resp):
        category_id = req.get_param_as_int('category_id')
        result = Mongo.getHeaderLinks(category_id)
        resp.body = dumps(result)

class SendTransactionalSms():
    def on_post(self,req,resp,sms_type,identifier,agentId):
        try:
            if sms_type =='code':
                otherMobileNumber = req.get_param('mobile_number')
                if otherMobileNumber is None or str(otherMobileNumber).strip() is '':
                    retailerContact = session.query(RetailerContacts).filter_by(retailer_id=identifier).filter(RetailerContacts.contact_type=='sms').order_by(RetailerContacts.created.desc()).first()
                    if retailerContact is not None:
                        if retailerContact.mobile_number is not None and len(retailerContact.mobile_number)==10:
                            smsStatus = generateSms(identifier,retailerContact.mobile_number,agentId)
                            if smsStatus:
                                resp.body = "{\"result\":\"success\"}"
                            else:
                                resp.body = "{\"result\":\"failed\"}"
                        else:
                            print 'Not a valid number to send the message to '+str(retailerContact.mobile_number)
                            resp.body = "{\"result\":\"failed\"}"
                    else:
                        print 'No number to send the message for retailer id '+str(identifier)
                        resp.body = "{\"result\":\"failed\"}"
                else:
                    if len(str(otherMobileNumber).strip())==10:
                        smsStatus = generateSms(identifier,str(otherMobileNumber).strip(),agentId)
                        if smsStatus:
                            retailerContact = RetailerContacts()
                            retailerContact.retailer_id = identifier
                            retailerContact.agent_id = agentId
                            retailerContact.call_type = 'inbound'
                            retailerContact.contact_type = 'sms'
                            retailerContact.mobile_number = str(otherMobileNumber).strip()
                            session.commit()
                            resp.body = "{\"result\":\"success\"}"
                            
                        else:
                            resp.body = "{\"result\":\"failed\"}"
                    else:
                        print 'Not a valid number to send the message to '+str(otherMobileNumber)
                        resp.body = "{\"result\":\"failed\"}"
        except:
            print traceback.print_exc()
        finally:
            session.close()
            
def generateSms(identifier,mobile_number,agentId):
    try:
        retailerLink = session.query(RetailerLinks).filter_by(retailer_id=identifier).first()
        retailer = session.query(Retailers).filter_by(id=identifier).first()
        if retailer is not None:
            if retailerLink is not None:
                code = retailerLink.code
                retailerLink.agent_id = agentId
            else: 
                c = RetailerDetail()
                code = c.getNewRandomCode()
                retailerLink = RetailerLinks()
                retailerLink.code = code
                retailerLink.agent_id = agentId
                retailerLink.retailer_id = identifier
                activationCode=Activation_Codes()
                activationCode.code = code
                activationCode.status = False
            profitmandiUrl = make_tiny(code)
            smsText = "Dear Customer,Download ProfitMandi app now. Get best deals on mobiles & accessories.Invite Code-" + code + " Click to download " + profitmandiUrl
            url_params = { 'ani' : '91'+mobile_number,  'uname' : 'srlsaholic', 'passwd' : 'sr18mar' , 'cli' : 'PROFTM', 'message' : smsText.strip() }
            encoded_url_params = urllib.urlencode(url_params)
            print 'Mobile Number :- '+str(mobile_number) +' Url Params:- '+str(encoded_url_params)
            url = TRANSACTIONAL_SMS_SEND_URL + encoded_url_params
            response_str = None
            try:
                req = urllib2.Request(url)
                response = urllib2.urlopen(req)
                response_str = response.read()
            except:
                print 'Error while getting response for user id:- Mobile Number:- '+str(mobile_number)
                traceback.print_exc()
                return False
            print 'Mobile Number:- '+str(mobile_number) +' Sent Response:- '+ str(response_str)
            try:
                sms = profitmandi_sms()
                sms.mobile_number = mobile_number
                sms.sms_type = 'code'
                sms.identifier = identifier 
                if "Message sent successfully to " in response_str:
                    response_str_vals = response_str.split('#')
                    sms.status = "smsprocessed"
                    sms.sms_id = response_str_vals[0]
                else:
                    sms.status = "smsrejected"
                    sms.sms_id = None
            except:
                print 'Error while getting response for user id:- Mobile Number:- '+str(mobile_number)
                traceback.print_exc()
                return False
            retailer.status='followup'
            retailer.disposition = 'auto_text_sent'
            retailer.next_call_time = datetime.now() + timedelta(days=1)
            session.commit()
            return True
        else:
            return False
    except:
        print traceback.print_exc()
        return False
    finally:
        session.close()

class FilterDealsByType():
    def on_get(self, req, resp):
        categoryId = req.get_param_as_int("categoryId")
        offset = req.get_param_as_int("offset")
        limit = req.get_param_as_int("limit")
        type = req.get_param("type")
        result = Mongo.getDealsByType(categoryId, offset, limit, type)
        resp.body = dumps(result) 
    
                

class GetItemCashback():
    def on_get(self ,req,res):
        itemcashback_id = req.get_param('id')
        offsetval = req.get_param_as_int('offset')
        limitval = req.get_param_as_int('limit')
        allitemcashback = Mongo.getAllItemCasback(itemcashback_id,offsetval,limitval)
        try: 
            if allitemcashback:
                res.body = dumps(allitemcashback)
        except:
                res.body = dumps({})
            
        
    def on_post(self,req,res):
        jsonReq = json.loads(req.stream.read(), encoding='utf-8')
        item_skuId = jsonReq.get('sku')
        cb = jsonReq.get('cash_back')
        cb_description = jsonReq.get('cash_back_description')
        cb_type = jsonReq.get('cash_back_type')
        cb_status = jsonReq.get('cash_back_status')
        response = Mongo.itemCashbackAdd(item_skuId,cb,cb_description,cb_type,cb_status)
        try:
            if response:
                res.body = dumps({"result":"success"})
        except:
            res.body = dumps({"result":"failed"})
    
class AutoComplete():
    def on_get(self, req, resp):
        search_term = req.get_param('searchString')
        try:
            resp.body = dumps(getSuggestions(search_term))
        except:
            resp.body = dumps([])
            

class UserDetails():
    def on_get(self,req,resp,saholic_user_ids):
        print saholic_user_ids
        user_ids = [(user_id).strip() for user_id in saholic_user_ids.split(',')]
        user_map = {}
        d_users = session.query(Users.first_name, Users.last_name, Users.email, Users.mobile_number, user_accounts.account_key).join((user_accounts,Users.id == user_accounts.user_id)).filter(user_accounts.account_key.in_(user_ids)).filter(user_accounts.account_type == 'saholic').all()
        for user in d_users:
            user_map[int(user[4])] = {'first_name':user[0],'last_name':user[1],'email':user[2],'mobile_number':user[3]}
        try:
            resp.body = dumps(user_map)
        except:
            resp.body = dumps({})
        finally:
            session.close()

def main():
    a = RetailerDetail()
    retailer = a.getNotActiveRetailer()
    otherContacts = [r for r, in session.query(RetailerContacts.mobile_number).filter_by(retailer_id=retailer.id).order_by(RetailerContacts.contact_type).all()]
    print json.dumps(todict(getRetailerObj(retailer, otherContacts, 'fresh')), encoding='utf-8')
    
if __name__ == '__main__':
    main()