Subversion Repositories SmartDukaan

def update_timestamp_for_amazon_order(orderDeliveryMap):

    for orderId,dateMap in orderDeliveryMap.iteritems():

        order = get_order(orderId)

        order.expected_delivery_time = datetime.datetime.strptime(dateMap.values()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S') 

        order.promised_delivery_time = datetime.datetime.strptime(dateMap.values()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S')

        promisedShipping = datetime.datetime.strptime(dateMap.keys()[0]+' 15:00:00', '%d-%m-%Y %H:%M:%S')

        order.expected_shipping_time = promisedShipping

        order.promised_shipping_time = promisedShipping

        session.commit()

    return True

def update_source_detail_timestamp(id,lastUpdatedOn):

    detail = get_source_detail(id)

    detail.lastUpdatedOn = to_py_date(lastUpdatedOn)

    session.commit()

    return True

def create_ebay_order(ebay_order):

    ebayOrder = EbayOrder()

    ebayOrder.orderId =  ebay_order.orderId

    ebayOrder.paisaPayId =  ebay_order.paisaPayId

    ebayOrder.salesRecordNumber = ebay_order.salesRecordNumber

    ebayOrder.ebayListingId =  ebay_order.ebayListingId

    ebayOrder.subsidyAmount =  ebay_order.subsidyAmount

    ebayOrder.ebayTxnDate =  to_py_date(ebay_order.ebayTxnDate)

    ebayOrder.transactionId = ebay_order.transactionId

    ebayOrder.listingName = ebay_order.listingName

    ebayOrder.listingPrice = ebay_order.listingPrice

    session.commit()

def get_ebay_order_by_sales_rec_number(sales_rec_number):

    return EbayOrder.query.filter(EbayOrder.salesRecordNumber == sales_rec_number).one()

def get_ebay_order(sales_rec_number, listingId, paisa_pay_id):

    query = EbayOrder.query

    if sales_rec_number:

        query = query.filter(EbayOrder.salesRecordNumber == sales_rec_number)

    if listingId:

        query = query.filter(EbayOrder.ebayListingId == listingId)

    if paisa_pay_id:

        query = query.filter(EbayOrder.paisaPayId == paisa_pay_id)

    return query.all()

    #return EbayOrder.query.filter(EbayOrder.salesRecordNumber == sales_rec_number).filter(EbayOrder.ebayListingId == listingId).one()

def get_ebay_order_by_orderId(order_id):

    return EbayOrder.query.filter(EbayOrder.orderId == order_id).one()

def update_ebay_order(ebay_order):

    ebayOrder = get_ebay_order_by_orderId(ebay_order.orderId)

    ebayOrder.subsidyAmount = ebay_order.subsidyAmount

    ebayOrder.transactionId = ebay_order.transactionId

    ebayOrder.ebayTxnDate = to_py_date(ebay_order.ebayTxnDate)

    ebayOrder.bluedartPaisaPayRef = ebay_order.bluedartPaisaPayRef

    ebayOrder.listingPrice = ebay_order.listingPrice

    session.commit()

def ebay_order_exists(sales_rec_number, ebay_listing_id):

    try:

        ebayOrder = get_ebay_order_by_sales_rec_number(sales_rec_number)

        if ebayOrder.ebayListingId == ebay_listing_id:

            return True

        else:

            return False

    except Exception as e:

        return False

def update_order_for_ebay(t_order):

    order = get_order(t_order.id)

    order.customer_id = t_order.customer_id

    order.customer_name = t_order.customer_name

    order.customer_city = t_order.customer_city

    order.customer_state = t_order.customer_state

    order.customer_mobilenumber = t_order.customer_mobilenumber

    order.customer_pincode = t_order.customer_pincode

    order.customer_address1 = t_order.customer_address1

    order.customer_address2 = t_order.customer_address2

    order.customer_email = t_order.customer_email

    order.logistics_provider_id = t_order.logistics_provider_id

    order.airwaybill_no = t_order.airwaybill_no

    order.tracking_id = t_order.tracking_id

    order.status = t_order.status  

    order.statusDescription = t_order.statusDescription

    order.cod = 0

    order.otg = False

    order.freebieItemId = 0

    order.orderType = OrderType.B2C

    order.source = 6

    transaction = get_transaction(order.transaction_id)

    transaction.status = TransactionStatus.AUTHORIZED

    transaction.customer_id = t_order.customer_id

    session.commit()

def split_ebay_order(order_id, split_order_qty, split_order_item_id, use_power_ship):

    #In case of split now order is always assigned based on the website logic...Also itemId won't work for now

    order = get_order(order_id)

    if split_order_qty >=order.lineitems[0].quantity:

        raise TransactionServiceException(108, "Split Quantity is greater than or equal to order quantity")

    if order.status not in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.CAPTURE_IN_PROCESS, OrderStatus.REJECTED, OrderStatus.ACCEPTED]:

        raise TransactionServiceException(108, "Order not allowed to be split")

    new_order = __clone_order(order, False, False)

    new_order.expected_delivery_time = order.expected_delivery_time

    new_order.promised_delivery_time = order.promised_delivery_time

    new_order.expected_shipping_time = order.expected_shipping_time

    new_order.promised_shipping_time = order.promised_shipping_time

    if use_power_ship:

        new_order.logistics_provider_id = order.logistics_provider_id

        new_order.airwaybill_no = order.airwaybill_no

        new_order.tracking_id = order.tracking_id

    new_order.lineitems[0].quantity = split_order_qty

    new_order.lineitems[0].total_price = new_order.lineitems[0].unit_price*split_order_qty

    new_order.total_amount = new_order.lineitems[0].total_price

    order.lineitems[0].quantity = order.lineitems[0].quantity - split_order_qty

    order.lineitems[0].total_price = order.lineitems[0].unit_price * order.lineitems[0].quantity

    order.total_amount = order.lineitems[0].total_price

    session.commit()

    return new_order

def add_or_update_amazon_fba_sales_snapshot(amazonfbasalessnapshot):

    salesnapshotfordate = AmazonFbaSalesSnapshot.get_by(item_id = amazonfbasalessnapshot.item_id,dateOfSale=to_py_date(amazonfbasalessnapshot.dateOfSale).date(),fcLocation=amazonfbasalessnapshot.fcLocation)

    if salesnapshotfordate is None:

        amazon_fba_sales_snapshot = AmazonFbaSalesSnapshot()

        amazon_fba_sales_snapshot.dateOfSale = to_py_date(amazonfbasalessnapshot.dateOfSale).date()

        amazon_fba_sales_snapshot.item_id = amazonfbasalessnapshot.item_id

        amazon_fba_sales_snapshot.totalOrderCount = amazonfbasalessnapshot.totalOrderCount

        amazon_fba_sales_snapshot.amazonFbaInventory = amazonfbasalessnapshot.amazonFbaInventory

        amazon_fba_sales_snapshot.isOutOfStock = amazonfbasalessnapshot.isOutOfStock

        amazon_fba_sales_snapshot.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount

        amazon_fba_sales_snapshot.totalSale = amazonfbasalessnapshot.totalSale

        amazon_fba_sales_snapshot.promotionSale = amazonfbasalessnapshot.promotionSale

        #if amazonfbasalessnapshot.ourPrice is not None:

        amazon_fba_sales_snapshot.ourPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.ourPriceSnapshotDate)

        amazon_fba_sales_snapshot.ourPrice = amazonfbasalessnapshot.ourPrice

        #if amazonfbasalessnapshot.salePrice is not None:

        amazon_fba_sales_snapshot.salePriceSnapshotDate = to_py_date(amazonfbasalessnapshot.salePriceSnapshotDate)

        amazon_fba_sales_snapshot.salePrice = amazonfbasalessnapshot.salePrice

        #if amazonfbasalessnapshot.minFbaPrice is not None:

        amazon_fba_sales_snapshot.minFbaPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minFbaPriceSnapshotDate)

        amazon_fba_sales_snapshot.minFbaPrice = amazonfbasalessnapshot.minFbaPrice

        #if amazonfbasalessnapshot.minMfnPrice is not None:

        amazon_fba_sales_snapshot.minMfnPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minMfnPriceSnapshotDate)

        amazon_fba_sales_snapshot.minMfnPrice = amazonfbasalessnapshot.minMfnPrice

        amazon_fba_sales_snapshot.fcLocation = amazonfbasalessnapshot.fcLocation 

    else:

        if(amazonfbasalessnapshot.totalOrderCount > 0):

            salesnapshotfordate.isOutOfStock = 0

        salesnapshotfordate.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount

        salesnapshotfordate.totalSale = amazonfbasalessnapshot.totalSale

        salesnapshotfordate.promotionSale = amazonfbasalessnapshot.promotionSale

        salesnapshotfordate.totalOrderCount = amazonfbasalessnapshot.totalOrderCount

        salesnapshotfordate.fcLocation = amazonfbasalessnapshot.fcLocation

    session.commit()    

def get_amazon_fba_sales_snapshot_for_days(interval):

    query = session.query(Order.status, func.count(Order.id)).group_by(Order.status)

    date = datetime.datetime.now()

    toDate = date-timedelta(days = 1)

    fromDate = toDate-timedelta(days = interval)

    fbasalessnapshot = AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.dateOfSale.between (fromDate.date(),toDate.date())).order_by(AmazonFbaSalesSnapshot.dateOfSale).all()

    return fbasalessnapshot        

def create_snapdeal_order(t_snapdeal_order):

    snapdealOrder = SnapdealOrder()

    snapdealOrder.orderId = t_snapdeal_order.orderId

    snapdealOrder.subOrderId = t_snapdeal_order.subOrderId

    snapdealOrder.referenceCode = t_snapdeal_order.referenceCode

    snapdealOrder.productName = t_snapdeal_order.productName

    snapdealOrder.listingPrice = t_snapdeal_order.listingPrice

    snapdealOrder.snapdealTxnDate = to_py_date(t_snapdeal_order.snapdealTxnDate)

    snapdealOrder.maxNlc = t_snapdeal_order.maxNlc

    order = Order.get_by(id=t_snapdeal_order.orderId)

    order.status = OrderStatus.ACCEPTED

    user = None

    try:

        user_client = UserClient().get_client()

        user_to_add = User()

        user_to_add.email = 'SD.'+ str(t_snapdeal_order.orderId) + '@mailinator.com'

        user_to_add.password = 'hSp5gvCsrrhZcceE6mzzOQ'

        user_to_add.communicationEmail = user_to_add.email

        user_to_add.sourceStartTime = int(round(time.time() * 1000))

        user_to_add.sourceId = OrderSource.SNAPDEAL

        user_to_add.sex = Sex.WONT_SAY

        user_to_add = user_client.createUser(user_to_add)

        user = user_to_add

    except:

        pass

    if user:

        order.customer_id = user.userId

        order.customer_email = user.email

    session.commit()

def get_snapdeal_order(order_id, reference_code, subOrder_id):

    query = SnapdealOrder.query

    if order_id:

        query = query.filter(SnapdealOrder.orderId == order_id)

    elif reference_code==subOrder_id:

        if reference_code is None:

            reference_code=''

        query = query.filter(or_(SnapdealOrder.referenceCode == reference_code, SnapdealOrder.subOrderId== subOrder_id))

    else:

        query = query.filter(and_(SnapdealOrder.referenceCode == reference_code, SnapdealOrder.subOrderId== subOrder_id))

        return query.one()

    return query.all()

def snapdeal_order_exists(suborder_id, reference_code):

    exists = SnapdealOrder.query.filter_by(subOrderId=suborder_id,referenceCode=reference_code).first()

    if exists is not None:

        return True

    else:

        return False

def get_amazon_fba_sales_latest_snapshot_for_item(item_id):

    return AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.item_id==item_id).filter(AmazonFbaSalesSnapshot.dateOfSale!=datetime.datetime.now().date()).order_by(desc(AmazonFbaSalesSnapshot.dateOfSale)).first()

def update_latest_fba_prices_for_item(fbaitemprices):

    fbasalessnapshot = AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.item_id==fbaitemprices.item_id).order_by(desc(AmazonFbaSalesSnapshot.dateOfSale)).first()

    if(fbasalessnapshot.ourPriceSnapshotDate < to_java_date(fbaitemprices.ourPriceSnapshotDate) and fbaitemprices.ourPrice is not None):

        fbasalessnapshot.ourPriceSnapshotDate = to_py_date(fbaitemprices.ourPriceSnapshotDate)

        fbasalessnapshot.ourPrice = fbaitemprices.ourPrice

    if(fbasalessnapshot.salePriceSnapshotDate < to_java_date(fbaitemprices.salePriceSnapshotDate) and fbaitemprices.salePrice is not None):

            fbasalessnapshot.salePriceSnapshotDate = to_py_date(fbaitemprices.salePriceSnapshotDate)

            fbasalessnapshot.salePrice = fbaitemprices.salePrice

    if(fbasalessnapshot.minFbaPriceSnapshotDate < to_java_date(fbaitemprices.minFbaPriceSnapshotDate) and fbaitemprices.minFbaPrice is not None):

            fbasalessnapshot.minFbaPriceSnapshotDate = to_py_date(fbaitemprices.minFbaPriceSnapshotDate)

            fbasalessnapshot.minFbaPrice = fbaitemprices.minFbaPrice

    if(fbasalessnapshot.minMfnPriceSnapshotDate < to_java_date(fbaitemprices.minMfnPriceSnapshotDate) and fbaitemprices.minMfnPrice is not None):

        fbasalessnapshot.minMfnPriceSnapshotDate = to_py_date(fbaitemprices.minMfnPriceSnapshotDate)

        fbasalessnapshot.minMfnPrice = fbaitemprices.minMfnPrice

    session.commit()

def flipkart_order_exists(orderid,suborderid):

    exists = FlipkartOrder.query.filter_by(flipkartOrderId=orderid,flipkartSubOrderId=suborderid).first()

    if exists is not None:

        return True

    else:

        return False

def create_flipkart_order(flipkartorder):

    order = FlipkartOrder()

    order.orderId = flipkartorder.orderId

    order.flipkartSubOrderId = flipkartorder.flipkartSubOrderId

    order.flipkartOrderId = flipkartorder.flipkartOrderId

    order.flipkartTxnDate = to_py_date(flipkartorder.flipkartTxnDate)

    order.shippingPrice = flipkartorder.shippingPrice 

    order.octroiFee = flipkartorder.octroiFee

    order.emiFee = flipkartorder.emiFee

    order.maxNlc = flipkartorder.maxNlc

    s_order = Order.get_by(id=flipkartorder.orderId)

    if s_order.cod:

        s_order.status = OrderStatus.COD_VERIFICATION_PENDING

    else:

        s_order.status = OrderStatus.ACCEPTED

        s_order.accepted_timetsmap = datetime.datetime.now()

    user = None

    try:

        user_client = UserClient().get_client()

        user_to_add = User()

        user_to_add.email = 'FK.'+ str(flipkartorder.orderId) + '@mailinator.com'

        user_to_add.password = 'gR9zF-Ish2im6tbYFNivgA'

        user_to_add.communicationEmail = user_to_add.email

        user_to_add.sourceStartTime = int(round(time.time() * 1000))

        user_to_add.sourceId = OrderSource.FLIPKART

        user_to_add.sex = Sex.WONT_SAY

        user_to_add = user_client.createUser(user_to_add)

        user = user_to_add

    except:

        pass

    if user:

        s_order.customer_id = user.userId

        s_order.customer_email = user.email

    session.commit()

def get_flipkart_order(orderid):

    flipkartorder = FlipkartOrder.query.filter_by(orderId=orderid).first()

    if not flipkartorder:

        print "No found order for orderid " + str(orderid)

        raise TransactionServiceException(108, "no such order")

    return flipkartorder    

def get_flipkart_order_by_subOrderId(flipkartOrderItemId):

    flipkartorder = FlipkartOrder.query.filter_by(flipkartSubOrderId=flipkartOrderItemId).first()

    if not flipkartorder:

        raise TransactionServiceException(108, "no such flipkart order " + str(flipkartOrderItemId))

    return flipkartorder

def update_flipkart_order_dates_and_awb(orderid,suborderid,date,awb):

    flipkartorder = FlipkartOrder.query.filter_by(flipkartOrderId=orderid,flipkartSubOrderId=suborderid).first()

    print flipkartorder 

    order = get_order(flipkartorder.orderId)

    if not order:

        print "No found order for orderid " + str(orderid)

        raise TransactionServiceException(108, "no such order")

    order.tracking_id = awb

    order.expected_delivery_time = to_py_date(date + 4*24*60*60*1000)

    order.promised_delivery_time = to_py_date(date + 4*24*60*60*1000)

    order.expected_shipping_time = to_py_date(date)

    order.promised_shipping_time = to_py_date(date)

    session.commit()

def update_snapdeal_orders_status(orders):

    for statusString, orderList in orders.iteritems():

        if statusString == "Cancelled":

            try:

                for cancel_order in orderList:

                    ref_code = cancel_order[0]

                    sub_order_code = cancel_order[1]

                    snapdeal_order = None 

                    try:

                        snapdeal_order = get_snapdeal_order(None, ref_code, sub_order_code)

                    except Exception as e:

                        print 'Caught in Updation of Cancelled Orders Getting Snapdeal Order'

                        pass

                    if snapdeal_order:

                        order = get_order(snapdeal_order.orderId)

                        if order.status == OrderStatus.SHIPPED_FROM_WH:

                            order.status = OrderStatus.SHIPPED_TO_LOGST

                            session.commit()

            except Exception as e:

                print 'Caught in Updation of Cancelled Orders'

                print e

                print sys.exc_info()

        if statusString == "Delivered":

            try:

                for del_order in orderList:

                    ref_code = del_order[0]

                    sub_order_code = del_order[1]

                    dlvry_timestamp = del_order[2]

                    snapdeal_order = None                    

                    try:

                        snapdeal_order = get_snapdeal_order(None, ref_code, sub_order_code)

                    except Exception as e:

                        print 'Caught in Updation of Delivered Orders Getting Snapdeal Order'

                        pass

                    if snapdeal_order:

                        order = get_order(snapdeal_order.orderId)

                        if order.status == OrderStatus.SHIPPED_FROM_WH:

                            order.delivery_timestamp = datetime.datetime.strptime(dlvry_timestamp, "%Y-%m-%d %H:%M:%S")

                            order.receiver = order.customer_name

                            order.status = OrderStatus.DELIVERY_SUCCESS

                            order.statusDescription = "Order delivered"

                            session.commit()

            except Exception as e:

                print 'Caught in Updation of Delivered Orders'

                print e

                print sys.exc_info()

    '''

    try:

        for del_order in delivered_orders:

            ref_code = del_order[0]

            sub_order_code = del_order[1]

            dlvry_timestamp = del_order[2]

            if snapdeal_order_exists(sub_order_code, ref_code):

                snapdeal_order = get_snapdeal_order(None, None, sub_order_code)

                if snapdeal_order:

                    order = get_order(snapdeal_order.orderId)

                    if order.status == OrderStatus.SHIPPED_FROM_WH:

                        order.delivery_timestamp = datetime.datetime.strptime(dlvry_timestamp, "%Y-%m-%d %H:%M:%S")

                        order.receiver = order.customer_name

                        order.status = OrderStatus.DELIVERY_SUCCESS

                        order.statusDescription = "Order delivered"

                        session.commit()

    except Exception as e:

        print e

        print sys.exc_info()

    '''

def update_flipkart_orders_status(delivered_orders):

    try:

        for del_order in delivered_orders:

            flipkart_order_id = del_order[0]

            sub_order_code = del_order[1]

            dlvry_timestamp = del_order[2]  

            if flipkart_order_exists(flipkart_order_id,sub_order_code):

                flipkart_order = get_flipkart_order_by_subOrderId(sub_order_code)

                if flipkart_order:

                    order = get_order(flipkart_order.orderId)

                    if order.status == OrderStatus.SHIPPED_FROM_WH:

                        order.delivery_timestamp = datetime.datetime.strptime(dlvry_timestamp, "%Y-%m-%d %H:%M:%S")

                        order.receiver = order.customer_name

                        order.status = OrderStatus.DELIVERY_SUCCESS

                        order.statusDescription = "Order delivered"

                        session.commit()

    except Exception as e:

        print e

        print sys.exc_info()

    '''    

    order_ids = delivered_orders.keys()

    for orderId in order_ids:

        order = Order.query.filter(Order.id == orderId).first()

        order.status = OrderStatus.DELIVERY_SUCCESS

        order.statusDescription = "Order delivered"

        order.delivery_timestamp = datetime.datetime.strptime(delivered_orders.get(orderId), "%Y-%m-%d %H:%M:%S")

        session.commit()

    '''

def bulk_add_or_update_amazon_fba_sales_snapshot(amazonfbasalessnapshotlist):

    for amazonfbasalessnapshot in amazonfbasalessnapshotlist:

        salesnapshotfordate = AmazonFbaSalesSnapshot.get_by(item_id = amazonfbasalessnapshot.item_id,dateOfSale=to_py_date(amazonfbasalessnapshot.dateOfSale).date(),fcLocation=amazonfbasalessnapshot.fcLocation)

        if salesnapshotfordate is None:

            amazon_fba_sales_snapshot = AmazonFbaSalesSnapshot()

            amazon_fba_sales_snapshot.dateOfSale = to_py_date(amazonfbasalessnapshot.dateOfSale).date()

            amazon_fba_sales_snapshot.item_id = amazonfbasalessnapshot.item_id

            amazon_fba_sales_snapshot.totalOrderCount = amazonfbasalessnapshot.totalOrderCount

            amazon_fba_sales_snapshot.amazonFbaInventory = amazonfbasalessnapshot.amazonFbaInventory

            amazon_fba_sales_snapshot.isOutOfStock = amazonfbasalessnapshot.isOutOfStock

            amazon_fba_sales_snapshot.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount

            amazon_fba_sales_snapshot.totalSale = amazonfbasalessnapshot.totalSale

            amazon_fba_sales_snapshot.promotionSale = amazonfbasalessnapshot.promotionSale

            #if amazonfbasalessnapshot.ourPrice is not None:

            amazon_fba_sales_snapshot.ourPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.ourPriceSnapshotDate)

            amazon_fba_sales_snapshot.ourPrice = amazonfbasalessnapshot.ourPrice

            #if amazonfbasalessnapshot.salePrice is not None:

            amazon_fba_sales_snapshot.salePriceSnapshotDate = to_py_date(amazonfbasalessnapshot.salePriceSnapshotDate)

            amazon_fba_sales_snapshot.salePrice = amazonfbasalessnapshot.salePrice

            #if amazonfbasalessnapshot.minFbaPrice is not None:

            amazon_fba_sales_snapshot.minFbaPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minFbaPriceSnapshotDate)

            amazon_fba_sales_snapshot.minFbaPrice = amazonfbasalessnapshot.minFbaPrice

            #if amazonfbasalessnapshot.minMfnPrice is not None:

            amazon_fba_sales_snapshot.minMfnPriceSnapshotDate = to_py_date(amazonfbasalessnapshot.minMfnPriceSnapshotDate)

            amazon_fba_sales_snapshot.minMfnPrice = amazonfbasalessnapshot.minMfnPrice

            amazon_fba_sales_snapshot.fcLocation = amazonfbasalessnapshot.fcLocation

        else:

            if(amazonfbasalessnapshot.totalOrderCount > 0):

                salesnapshotfordate.isOutOfStock = 0

            salesnapshotfordate.promotionOrderCount = amazonfbasalessnapshot.promotionOrderCount

            salesnapshotfordate.totalSale = amazonfbasalessnapshot.totalSale

            salesnapshotfordate.promotionSale = amazonfbasalessnapshot.promotionSale

            salesnapshotfordate.totalOrderCount = amazonfbasalessnapshot.totalOrderCount

            salesnapshotfordate.fcLocation = amazonfbasalessnapshot.fcLocation

    session.commit()

def get_created_orders_for_flipkart(flipkartorderids):

    item_ordercount = dict()

    orders = []

    flipkart_orders = session.query(FlipkartOrder).filter(FlipkartOrder.flipkartOrderId.in_(tuple(flipkartorderids))).all()

    print flipkart_orders 

    if len(flipkart_orders)!=0:

        for order in flipkart_orders:

            print order.orderId 

            orders.append(order.orderId)

        items =session.query(LineItem.item_id,func.count(LineItem.quantity)).join((Order,LineItem.order_id==Order.id)).filter(Order.id.in_(tuple(orders))).filter(Order.status >=3).filter(Order.shipping_timestamp==None).group_by(LineItem.item_id).all()    

    else:

        return item_ordercount

    print "items map after query"     

    print items

    for item in items:

        print "item id"

        print item[0]

        print "order count"

        print item[1]

        item_ordercount[item[0]] = item[1]

    print item_ordercount    

    return item_ordercount    

def change_easyship_mfn_order_txn_status(transaction_id, new_status, description, pickUp, orderType, source, shipTimestamp, deliveryTimeStamp):

    transaction = get_transaction(transaction_id)

    transaction.status = new_status

    transaction.status_message = description

    ## Assign runner in case of delhi pincodes

    if transaction.orders[0].pickupStoreId:

        logistics_client = LogisticsClient().get_client() 

        store = logistics_client.getPickupStore(transaction.orders[0].pickupStoreId)

        if delhi_pincodes.__contains__(store.pin):

            pickUp = PickUpType.RUNNER  

    if new_status == TransactionStatus.FAILED:

        for order in transaction.orders:

            order.status = OrderStatus.PAYMENT_FAILED

            order.statusDescription = "Payment Failed"

    elif new_status == TransactionStatus.AUTHORIZED or new_status == TransactionStatus.FLAGGED:

        for order in transaction.orders:

            if new_status == TransactionStatus.AUTHORIZED:

                order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

                order.statusDescription = "Submitted to warehouse"

            elif new_status == TransactionStatus.FLAGGED:

                order.status = OrderStatus.PAYMENT_FLAGGED

                order.statusDescription = "Payment flagged by gateway"

            order.cod = False

            order.orderType = orderType

            #After we got payment success, we will set logistics info also 

            logistics_client = LogisticsClient().get_client()

            #FIXME line item is only one now. If multiple will come, need to fix.

            item_id = order.lineitems[0].item_id

            logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)

            #current_time = datetime.datetime.now()

            #logistics_info.deliveryTime = adjust_delivery_time(to_py_date(deliveryTimeStamp), 0)

            #logistics_info.shippingTime = adjust_delivery_time(to_py_date(shipTimestamp), 0)

            #logistics_info.deliveryDelay = 0

            order.otg = logistics_info.otgAvailable

            order.warehouse_id = logistics_info.warehouseId

            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId

            order.logistics_provider_id = 45

            #Start:- Added by Manish Sharma for FedEx Integration - Shipment Creation on 31-Jul-2013

            #order.airwaybill_no = logistics_info.airway_billno

            #order.tracking_id = order.airwaybill_no

            #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

            order.courier_delivery_time = to_py_date(deliveryTimeStamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.expected_shipping_time = to_py_date(shipTimestamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_shipping_time = order.expected_shipping_time

            order.expected_delivery_time = to_py_date(deliveryTimeStamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_delivery_time = order.expected_delivery_time 

            if order.pickupStoreId:

                order.otg = False

            inventory_client = InventoryClient().get_client()

            if order.productCondition != ProductCondition.BAD:

                inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:

                item_pricing = inventory_client.getItemPricing(item_id, -1)

                order.lineitems[0].transfer_price = item_pricing.transferPrice

                order.lineitems[0].nlc = item_pricing.nlc

            except:

                print "Not able to get transfer price. Skipping"

            catalog_client = CatalogClient().get_client() 

            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

            #Note 2 Buy back Offer

            if item_id in (7974, 7558) and transaction.coupon_code is not None and transaction.coupon_code.lower() == "note2buyback":    

                order.status = OrderStatus.COD_VERIFICATION_PENDING

                order.statusDescription = "Note 2 Buy Back Offer - Approval Pending from CRM Team"

                order.otg = False

    elif new_status == TransactionStatus.COD_IN_PROCESS:

        for order in transaction.orders:

            order.status = OrderStatus.COD_VERIFICATION_PENDING

            order.statusDescription = "Verification Pending"

            order.cod = True

            order.orderType = orderType

            #After we got payment success, we will set logistics info also 

            logistics_client = LogisticsClient().get_client()

            #FIXME line item is only one now. If multiple will come, need to fix.

            item_id = order.lineitems[0].item_id

            if order.pickupStoreId:

                # Need to send prepaid awb number for store pick up orders

                logistics_info = logistics_client.getLogisticsEstimation(item_id, order.customer_pincode, DeliveryType.COD)

                #//Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

                #prepaid_logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.PREPAID, pickUp)

                #logistics_info.airway_billno = prepaid_logistics_info.airway_billno

                #logistics_info.providerId = prepaid_logistics_info.providerId

                #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

                # Will not provide OTG in pickup store

                order.otg = False

            else:

                logistics_info = logistics_client.getLogisticsInfo(order.customer_pincode, item_id, DeliveryType.COD, pickUp)

                order.otg = logistics_info.otgAvailable

            #current_time = datetime.datetime.now()

            #logistics_info.deliveryTime = adjust_delivery_time(current_time, logistics_info.deliveryTime)

            #logistics_info.shippingTime = adjust_delivery_time(current_time, logistics_info.shippingTime)

            #logistics_info.deliveryDelay = adjust_delivery_time(current_time, (logistics_info.shippingTime+logistics_info.deliveryDelay))

            order.warehouse_id = logistics_info.warehouseId

            order.fulfilmentWarehouseId = logistics_info.fulfilmentWarehouseId

            order.logistics_provider_id = 45

            #Start:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

            #order.airwaybill_no = logistics_info.airway_billno

            #order.tracking_id = order.airwaybill_no

            #End:- Added by Manish Sharma for FedEx Integration- Shipment Creation on 31-Jul-2013

            order.courier_delivery_time = to_py_date(deliveryTimeStamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.expected_shipping_time = to_py_date(shipTimestamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_shipping_time = order.expected_shipping_time

            order.expected_delivery_time = to_py_date(deliveryTimeStamp).replace(hour=PREPAID_SHIPPING_CUTOFF_TIME, minute=0, second=0)

            order.promised_delivery_time = order.expected_delivery_time

            inventory_client = InventoryClient().get_client()

            if order.productCondition != ProductCondition.BAD:

                inventory_client.reserveItemInWarehouse(item_id, logistics_info.fulfilmentWarehouseId, sourceId, order.id, to_java_date(order.created_timestamp), to_java_date(order.promised_shipping_time), order.lineitems[0].quantity)

            try:

                item_pricing = inventory_client.getItemPricing(item_id, -1)

                order.lineitems[0].transfer_price = item_pricing.transferPrice

                order.lineitems[0].nlc = item_pricing.nlc        

            except:

                print "Not able to get transfer price. Skipping"

            catalog_client = CatalogClient().get_client()

            voucherAmount = catalog_client.getVoucherAmount(item_id, VoucherType.SPICEDECK_MOBILE)

            if voucherAmount:

                __create_recharge_voucher_tracker(order, voucherAmount, VoucherType.SPICEDECK_MOBILE)

    session.commit()

    if new_status in (TransactionStatus.AUTHORIZED, TransactionStatus.FLAGGED, TransactionStatus.COD_IN_PROCESS):

        try:

            if order.source == OrderSource.WEBSITE:    

                if new_status == TransactionStatus.COD_IN_PROCESS:

                    transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

                    transaction_requiring_extra_processing.category = 'COD_VERIFICATION'

                    transaction_requiring_extra_processing.transaction_id = transaction_id

                    session.commit()

                elif new_status == TransactionStatus.FLAGGED:

                    transaction_requiring_extra_processing = TransactionRequiringExtraProcessing()

                    transaction_requiring_extra_processing.category = 'PAYMENT_FLAGGED'

                    transaction_requiring_extra_processing.transaction_id = transaction_id

                    session.commit()

            elif order.source == OrderSource.STORE:

                order.otg = False

                session.commit()

                catalog_client = CatalogClient().get_client() 

                sourcePricing = catalog_client.getStorePricing(order.lineitems[0].item_id)

                if order.total_amount >= sourcePricing.minPrice:

                    order.status = OrderStatus.SUBMITTED_FOR_PROCESSING

                    order.statusDescription = "Order Accepted"

                    session.commit()

                    sod = StoreOrderDetail.get_by(orderId = order.id)

                    __push_store_collection_to_hotspot(order, "advance", sod.cashAmount, sod.cardAmount)

                else: 

                    order.status = OrderStatus.COD_VERIFICATION_PENDING

                    order.statusDescription = "Approval Pending"

                    content = "<html><body>"

                    content += "<h4>Order Id:  " + str(order.id) + "</h4>"

                    content += "<h4>Product:  " + str(order.lineitems[0]) + "</h4>"

                    content += "<h4>Min Price: " +  str(sourcePricing.minPrice) + "</h4>"

                    content += "<h4>Recommended Price: " +  str(sourcePricing.recommendedPrice) + "</h4>"

                    content += "<h2>Approval Requested for Price: " +  str(order.total_amount) + "</h2>"

                    content += "<h2>Note 1: If this discount price is approved then reply 'Approved' otherwise 'Not Approved' without changing anythig in the Mail.</h2>"  

                    content += "<h2>Note 2: For Quick approval using browser <a href='http://support.shop2020.in:8080/Support/price-approval?xszbg=" + base64.b64encode(str(order.id))  + "'>Click Here</a></h2>"

                    content += "<br><br></body></html>"

                    store = get_hotspot_store(order.storeId, "")

                    helper_client = HelperClient().get_client()

                    helper_client.saveUserEmailForSending(store.approvalEmail.split(';'), SaholicHelpEmailId, "Price approval requested for " + str(order.lineitems[0]) + " order Id " + str(order.id) + " in store " + store.hotspotId , content, str(transaction_id), "PriceApproval", [], [], order.source)

                    session.commit()

            else:

                order.otg = False

                session.commit()

        except Exception as e:

            print "Error inserting transaction Id: " + str(transaction_id) + " due to " + str(e)

    return True

def update_amazon_fba_order_returns(fbaOrderReturns):

    for order_return in fbaOrderReturns:

        orderReturn = AmazonFbaOrderReturns.query.filter(AmazonFbaOrderReturns.amazonOrderId==order_return.amazonOrderId).filter(AmazonFbaOrderReturns.insertionTimestamp == to_py_date(order_return.insertionTimestamp)).filter(AmazonFbaOrderReturns.sku==order_return.sku).first()

        if orderReturn:

            orderReturn.sellableReturnQuantity = order_return.sellableReturnQuantity

            orderReturn.nonSellableReturnQuantity = order_return.nonSellableReturnQuantity

            session.commit()

        else:

            orderReturn = AmazonFbaOrderReturns()

            orderReturn.amazonOrderId = order_return.amazonOrderId

            orderReturn.insertionTimestamp = to_py_date(order_return.insertionTimestamp)

            orderReturn.sku = order_return.sku

            orderReturn.creationTimestamp = to_py_date(order_return.creationTimestamp)

            orderReturn.shippedQuantity = order_return.shippedQuantity

            orderReturn.sellableReturnQuantity = order_return.sellableReturnQuantity

            orderReturn.nonSellableReturnQuantity = order_return.nonSellableReturnQuantity

            session.commit()

def get_all_amazon_fba_order_returns(insertionTimestamp):

    returns = AmazonFbaOrderReturns.query.filter(AmazonFbaOrderReturns.insertionTimestamp==to_py_date(insertionTimestamp)).all()

    if not returns:

        returns = []

    return returns  

def get_total_sale_returns_fba_skus_curent_time(insertionTimestamp):  

    allFbaSkuDetails = session.query(AmazonFbaOrderReturns.sku, func.sum(AmazonFbaOrderReturns.shippedQuantity), func.sum(AmazonFbaOrderReturns.sellableReturnQuantity), func.sum(AmazonFbaOrderReturns.nonSellableReturnQuantity)).group_by(AmazonFbaOrderReturns.sku).filter(AmazonFbaOrderReturns.insertionTimestamp == to_py_date(insertionTimestamp)).all()

    returnAllFbaSkuDetails = {}

    for fbaSkuDetail in allFbaSkuDetails:

        saleReturnDetail = {}

        saleReturnDetail['Sale'] = fbaSkuDetail[1]

        saleReturnDetail['SaleableReturn'] = fbaSkuDetail[2]

        saleReturnDetail['NonSaleableReturn'] = fbaSkuDetail[3]

        returnAllFbaSkuDetails[fbaSkuDetail[0]]= saleReturnDetail

    return returnAllFbaSkuDetails

def get_amazon_fba_sales_latest_snapshot_for_item_location_wise(item_id,location):

    return AmazonFbaSalesSnapshot.query.filter(AmazonFbaSalesSnapshot.item_id==item_id).filter(AmazonFbaSalesSnapshot.fcLocation==location).filter(AmazonFbaSalesSnapshot.dateOfSale!=datetime.datetime.now().date()).order_by(desc(AmazonFbaSalesSnapshot.dateOfSale)).first()

def get_verification_pending_orders_fk():

    retOrders = Order.query.filter(Order.source==OrderSource.FLIPKART).filter(Order.status==OrderStatus.COD_VERIFICATION_PENDING).all()

    retFkOrders = []

    order_ids = []

    if not retOrders:

        return retFkOrders

    else:

        for order in retOrders:

            order_ids.append(order.id)

        retFkOrders = FlipkartOrder.query.filter(FlipkartOrder.orderId.in_(tuple(order_ids)))

        if not retFkOrders:

            retFkOrders = []

    return retFkOrders

def get_fa_order_by_fk_order_id(fk_OrderId,fk_OrderItemId):

    faOrder = FlipkartAdvantageOrder.query.filter_by(fkOrderId=fk_OrderId,fkOrderItemId=fk_OrderItemId).first()

    if not faOrder:

        print "Not found order for orderId " + str(fk_OrderId)

        raise TransactionServiceException(108, "no such order")

    return faOrder

def flipkart_fa_order_exists(fk_OrderId,fk_OrderItemId):

    faOrder = FlipkartAdvantageOrder.query.filter_by(fkOrderId=fk_OrderId,fkOrderItemId=fk_OrderItemId).first()

    if not faOrder:

        return False

    else:

        return True

def get_all_fa_orders_list(status):

    faOrders = None

    if status == 'all':

        faOrders = FlipkartAdvantageOrder.query.all()

    else:

        faOrders = FlipkartAdvantageOrder.query.filter(FlipkartAdvantageOrder.status==status).all()

    if not faOrders:

        faOrders = []   

    return faOrders

def add_update_fa_orders_bulk(faOrdersList):

    for faOrder in faOrdersList:

        if faOrder.status == 'approved':

            fa_Order = FlipkartAdvantageOrder()

            fa_Order.fkOrderId = faOrder.fkOrderId

            fa_Order.fkOrderItemId = faOrder.fkOrderItemId

            fa_Order.sku = faOrder.sku

            fa_Order.creationTimestamp = to_py_date(faOrder.creationTimestamp)

            fa_Order.customerName = faOrder.customerName

            fa_Order.customerAddress = faOrder.customerAddress

            fa_Order.pincode = faOrder.pincode

            fa_Order.customerCity = faOrder.customerCity

            fa_Order.customerState = faOrder.customerState

            fa_Order.customerPhone = faOrder.customerPhone

            fa_Order.status = faOrder.status

            fa_Order.quantity = faOrder.quantity

            fa_Order.totalPrice = faOrder.totalPrice

            fa_Order.listPrice = faOrder.listPrice

            fa_Order.modifiedDate = to_py_date(faOrder.modifiedDate)

            fa_Order.listingId = faOrder.listingId

            fa_Order.cancelReason = faOrder.cancelReason

            fa_Order.returnReason = faOrder.returnReason

            fa_Order.freebieItemId = faOrder.freebieItemId

            fa_Order.productTitle = faOrder.productTitle

        else:

            fa_Order = FlipkartAdvantageOrder.query.filter_by(fkOrderId=faOrder.fkOrderId,fkOrderItemId=faOrder.fkOrderItemId).first()

            if fa_Order:

                if fa_Order.status !=faOrder.status:

                    fa_Order.status = faOrder.status

                    fa_Order.cancelReason = faOrder.cancelReason

                    fa_Order.returnReason = faOrder.returnReason

                    fa_Order.modifiedDate = to_py_date(faOrder.modifiedDate)

            else:

                fa_Order = FlipkartAdvantageOrder()

                fa_Order.fkOrderId = faOrder.fkOrderId

                fa_Order.fkOrderItemId = faOrder.fkOrderItemId

                fa_Order.sku = faOrder.sku

                fa_Order.creationTimestamp = to_py_date(faOrder.creationTimestamp)

                fa_Order.customerName = faOrder.customerName

                fa_Order.customerAddress = faOrder.customerAddress

                fa_Order.pincode = faOrder.pincode

                fa_Order.customerCity = faOrder.customerCity

                fa_Order.customerState = faOrder.customerState

                fa_Order.customerPhone = faOrder.customerPhone

                fa_Order.status = faOrder.status

                fa_Order.quantity = faOrder.quantity

                fa_Order.totalPrice = faOrder.totalPrice

                fa_Order.listPrice = faOrder.listPrice

                fa_Order.modifiedDate = to_py_date(faOrder.modifiedDate)

                fa_Order.listingId = faOrder.listingId

                fa_Order.cancelReason = faOrder.cancelReason

                fa_Order.returnReason = faOrder.returnReason

                fa_Order.freebieItemId = faOrder.freebieItemId

                fa_Order.productTitle = faOrder.productTitle

    session.commit()

def create_homeshop_order(hsOrder):

    hs_Order = HsOrder()

    hs_Order.orderId = hsOrder.orderId

    hs_Order.catalogueName = hsOrder.catalogueName

    hs_Order.courierName = hsOrder.courierName

    hs_Order.hsItemId = hsOrder.hsItemId

    hs_Order.hsOrderDate = to_py_date(hsOrder.hsOrderDate)

    hs_Order.hsOrderNo = hsOrder.hsOrderNo

    hs_Order.hsProductId = hsOrder.hsProductId

    hs_Order.hsSubOrderNo = hsOrder.hsSubOrderNo

    hs_Order.paymentMode = hsOrder.paymentMode

    hs_Order.sellerSku = hsOrder.sellerSku

    hs_Order.slaDays = hsOrder.slaDays

    hs_Order.shippingName = hsOrder.shippingName

    order = Order.get_by(id=hsOrder.orderId)

    order.status = OrderStatus.ACCEPTED

    order.accepted_timestamp = datetime.datetime.now()

    user = None

    try:

        user_client = UserClient().get_client()

        user_to_add = User()

        user_to_add.email = 'HS.'+ str(hsOrder.orderId) + '@mailinator.com'

        user_to_add.password = 'ouBPONyDYWfeAFtXeaYRkQ'

        user_to_add.communicationEmail = user_to_add.email

        user_to_add.sourceStartTime = int(round(time.time() * 1000))

        user_to_add.sourceId = OrderSource.HOMESHOP18

        user_to_add.sex = Sex.WONT_SAY

        user_to_add = user_client.createUser(user_to_add)

        user = user_to_add

    except:

        pass

    if user:

        order.customer_id = user.userId

        order.customer_email = user.email

    session.commit()

def get_homeshop_order(order_id, hsOrderNo, hsSubOrderNo):

    query = HsOrder.query

    if order_id:

        query = query.filter(HsOrder.orderId == order_id)

    if hsOrderNo:

        query = query.filter(HsOrder.hsOrderNo == hsOrderNo)

    if hsSubOrderNo:

        query = query.filter(HsOrder.hsSubOrderNo == hsSubOrderNo)

    return query.all()

def homeshop_order_exists(hs_OrderNo, hs_SubOrderNo):

    exists = HsOrder.query.filter_by(hsOrderNo=hs_OrderNo,hsSubOrderNo=hs_SubOrderNo).first()

    if exists is not None:

        return True

    else:

        return False

def refund_return_order(returnOrderInfo, attrList):

    returnOrder = ReturnOrderInfo.get_by(id=returnOrderInfo.id)

    if returnOrder.returnStatus not in (OrderStatus._VALUES_TO_NAMES[OrderStatus.DOA_CERT_VALID], OrderStatus._VALUES_TO_NAMES[OrderStatus.DOA_CERT_INVALID], OrderStatus._VALUES_TO_NAMES[OrderStatus.DOA_LOST_IN_TRANSIT],OrderStatus._VALUES_TO_NAMES[OrderStatus.DOA_RECEIVED_DAMAGED] ,OrderStatus._VALUES_TO_NAMES[OrderStatus.RET_LOST_IN_TRANSIT], OrderStatus._VALUES_TO_NAMES[OrderStatus.RET_PRODUCT_UNUSABLE], OrderStatus._VALUES_TO_NAMES[OrderStatus.RET_PRODUCT_USABLE],OrderStatus._VALUES_TO_NAMES[OrderStatus.RET_RECEIVED_DAMAGED]):

        print "************returnig false staus check"

        return False

    if returnOrder.returnAction == ReturnAction.REFUND:

        returnOrder.returnTxnResolutionStatus = ReturnTxnResolutionStatus._VALUES_TO_NAMES[ReturnTxnResolutionStatus.REFUND_IN_PROCESS]

    else:

        returnOrder.returnTxnResolutionStatus = ReturnTxnResolutionStatus._VALUES_TO_NAMES[ReturnTxnResolutionStatus.REPLACEMENT_ORDER_CREATED]

    detailsMap = {}

    if returnOrder.refundedAt or returnOrder.replacementOrderId:

        raise TransactionServiceException(145, "Refund/Replacemet already in Process for this return order")

    if returnOrder.receivedQuantity != returnOrder.returnQuantity:

        raise TransactionServiceException(145, "Quantity mismatch Return vs Received")

    detailsMap['RefundReason'] = returnOrderInfo.refundReason

    detailsMap['RefundType'] = returnOrderInfo.refundType

    detailsMap['RefundedBy'] = returnOrderInfo.refundedBy

    detailsMap['RefundAmount'] = returnOrderInfo.refundAmount

    detailsMap['RefundDescription'] = returnOrderInfo.refundDescription

    print detailsMap

    print "*************************8"

    if(not __process_return_order(returnOrder, detailsMap)):

        raise TransactionServiceException(145, "Issue in __process_return_order")

    returnOrder.processedAt = datetime.datetime.now()

    __markReturnTransactionAsProcessed(returnOrder.returnTransaction_id)

    set_order_attributes(returnOrder.orderId, attrList)

    return True

def __markReturnTransactionAsProcessed(returnTransactionId):