Subversion Repositories SmartDukaan

# -*- coding: utf-8 -*-

'''

Created on 29-Mar-2010

@author: Chandranshu

'''

from datetime import date, timedelta

from decimal import Decimal

from elixir import *

from math import ceil

from random import randrange

from reportlab.lib import colors

from reportlab.lib.enums import TA_CENTER, TA_JUSTIFY

from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet

from reportlab.lib.units import inch, mm

from reportlab.pdfgen.canvas import Canvas

from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image, \

    BaseDocTemplate, Frame, PageTemplate, Table, TableStyle

from shop2020.clients.AlertClient import AlertClient

from shop2020.clients.CRMClient import CRMClient

from shop2020.clients.CatalogClient import CatalogClient

from shop2020.clients.HelperClient import HelperClient

from shop2020.clients.InventoryClient import InventoryClient

from shop2020.clients.LogisticsClient import LogisticsClient

from shop2020.clients.PaymentClient import PaymentClient

from shop2020.clients.PromotionClient import PromotionClient

from shop2020.clients.UserClient import UserClient

from shop2020.clients.WarehouseClient import WarehouseClient

from shop2020.config.client.ConfigClient import ConfigClient

from shop2020.model.v1 import order

from shop2020.model.v1.order.impl import DataService, RechargeService

from shop2020.model.v1.order.impl.Convertors import to_t_emi_scheme, to_t_order, \

    cutoff_date, to_t_warehouse_address

from shop2020.model.v1.order.impl.DataAccessors import get_order, \

    get_transaction, get_orders_by_mobile_number, __clone_order, delhi_pincodes, \

    __create_recharge_voucher_tracker, __push_store_collection_to_hotspot, \

    get_hotspot_store, get_source_detail

from shop2020.model.v1.order.impl.DataService import Transaction, LineItem, \

    Order, BatchNoGenerator, InvoiceIDGenerator, TransactionRequiringExtraProcessing, \

    OrderInventory, Alert, PaymentSettlement, EBSSettlementSummary, \

    CodVerificationAgent, Attribute, RechargeVoucherTracker, EmiScheme, MiscCharges, \

    BlockedIpRange, DeniedIpAddress, InsuranceDetailForOrder, DocumentStore, \

    RechargeTransaction, HotspotStore, WalletForCompany, WalletHistoryForCompany, \

    FRC, OperatorSeries, SourceDetail, Company, EbayOrder, AmazonFbaSalesSnapshot, \

    AmazonOrder, StoreOrderDetail, EdcBank, StoreOrderCollection, \

    HotspotServiceMatrix, SnapdealOrder, FlipkartOrder, DataInsuranceDetailForOrder, \

    AmazonFbaOrderReturns, FlipkartAdvantageOrder, InvoiceCounterGenerator, \

    TransactionShipmentSequence, HsOrder, Creditor, UserSanction, CreditHistory, \

    LoanHistory, ShipmentLogisticsCostDetail, ReturnOrderInfo, ReturnTransaction, \

    ReturnPickupRequest, SellerWarehouse, Seller, Organisation, \

    WarehouseAddressMapping, WarehouseAddressMaster

from shop2020.model.v1.order.impl.model.BaseOrder import BaseOrder

from shop2020.model.v1.order.impl.model.DTHRechargeOrder import DTHRechargeOrder

from shop2020.model.v1.order.impl.model.MobileRechargeOrder import \

    MobileRechargeOrder

from shop2020.model.v1.order.impl.model.RechargeDenomination import \

    RechargeDenomination

from shop2020.model.v1.order.impl.model.RechargeOrder import RechargeOrder

from shop2020.model.v1.order.impl.model.RechargePlan import RechargePlan

from shop2020.model.v1.order.impl.model.ReturnOrder import ReturnOrder

from shop2020.model.v1.order.impl.model.ServiceAvailability import \

    ServiceAvailability

from shop2020.model.v1.order.impl.model.ServiceProvider import ServiceProvider

from shop2020.model.v1.order.impl.model.TelecomCircle import TelecomCircle

from shop2020.model.v1.order.impl.model.UserWallet import UserWallet

from shop2020.model.v1.order.impl.model.UserWalletHistory import \

    UserWalletHistory

from shop2020.model.v1.user.impl.Converters import to_t_address

from shop2020.thriftpy.alert.ttypes import MonitoredEntity, EntityType

from shop2020.thriftpy.crm.ttypes import *

from shop2020.thriftpy.logistics.ttypes import DeliveryType, PickUpType

from shop2020.thriftpy.model.v1.catalog.ttypes import ItemType, ItemCondition

from shop2020.thriftpy.model.v1.inventory.ttypes import BillingType, \

    InventoryServiceException, WarehouseType, InventoryType, VatType, ItemInventory

from shop2020.thriftpy.model.v1.order.ttypes import TransactionServiceException, \

    TransactionStatus, OrderStatus, DelayReason, ExtraTransactionProcessingType, \

    HotspotAction, TimeoutSummary, OrderStatusGroups, OrderType, RechargeOrderStatus, \

    RechargeType, RechargeStatistics, DeviceNumberInfo, EmiChargeType, PayMethod, \

    OrderSource, StorePaymentStatus, ProductCondition, TaxType, \

    AmazonFCWarehouseLocation, RechargeMode, CreditTxnType, \

    CreditHistory as TCreditHistory, LoanHistory as TLoanHistory, \

    ShipmentLogisticsCostDetail as TShipmentLogisticsCostDetail, \

    ReturnTransactionStatus, ReturnAction, ReturnTxnResolutionStatus, \

    ReplacementShippingType, ReturnTxnPickupStatus, ReceivedReturnType, \

    ReturnPickupType, RefundType, SellerInfo, BuyerInfo

from shop2020.thriftpy.model.v1.user.ttypes import VoucherType, CouponCategory, \

    User, Sex

from shop2020.thriftpy.payments.ttypes import PaymentException

from shop2020.thriftpy.utils.ttypes import UserSmsInfo, SmsType

from shop2020.thriftpy.warehouse.ttypes import ScanType, \

    WarehouseServiceException

from shop2020.utils.EmailAttachmentSender import mail, get_attachment_part, \

    mail_html

from shop2020.utils.Utils import to_py_date, to_java_date, \

    getSyncOperatorsForRecharge

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound

from sqlalchemy.sql import func

from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \

    between

from string import Template

from suds.client import Client

from textwrap import dedent

from xml.dom.minidom import parseString

import MySQLdb

import base64

import datetime

import httplib

import logging

import math

import os

import re

import sys

import time

import traceback

import urllib

#Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013

#End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013

#Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013

#End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013

logging.basicConfig(level=logging.DEBUG)

sourceId = int(ConfigClient().get_property("sourceid"))

capitalFloatPayMethod = 456789 

SaholicHelpEmailId = "Saholic <help@saholic.com>"

mail_user = 'cnc.center@shop2020.in'

mail_password = '5h0p2o2o'

help_user = 'help@shop2020.in'

help_password = '5h0p2o2o'

source_url = 'www.saholic.com'

source_name = 'Saholic'

hotspot_store_url ='http://125.19.98.100/loaddetect/service.asmx?WSDL'

aclient = None

PREPAID_SHIPPING_CUTOFF_TIME = 15

COD_SHIPPING_CUTOFF_TIME = 12

billedOrdersColorMap = {}

ORDER_STATUS_TO_USER_TRUST_LEVEL_DELTA_DICT = {

        OrderStatus.RTO_RESHIPPED    :  3,

        OrderStatus.RTO_IN_TRANSIT   : -5,

        OrderStatus.DELIVERY_SUCCESS :  1,

        OrderStatus.DOA_CERT_INVALID : -5

}

creditTxnMultiplierMap = {

    CreditTxnType.BLOCKED : 1,

    CreditTxnType.BLOCKED_REVERSED : -1, 

    CreditTxnType.LOAN : -1,

    CreditTxnType.CORRECTION : 1                  

}

loanTxnMultplierMap = {

    CreditTxnType.LOAN : 1, 

    CreditTxnType.LOAN_CANCELLED : -1, 

    CreditTxnType.PAID : -1,

    CreditTxnType.CORRECTION : -1

}

refund_status_transition = { OrderStatus.LOST_IN_TRANSIT : OrderStatus.LOST_IN_TRANSIT_REFUNDED,

                     OrderStatus.RTO_RECEIVED_PRESTINE : OrderStatus.RTO_REFUNDED,

                     OrderStatus.RTO_RECEIVED_DAMAGED : OrderStatus.RTO_DAMAGED_REFUNDED,

                     OrderStatus.RTO_LOST_IN_TRANSIT : OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED,

                     OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,

                     OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,

                     OrderStatus.DOA_RECEIVED_DAMAGED : OrderStatus.DOA_REFUNDED_RCVD_DAMAGED,

                     OrderStatus.DOA_LOST_IN_TRANSIT : OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT,

                     OrderStatus.RET_PRODUCT_UNUSABLE : OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED,

                     OrderStatus.RET_PRODUCT_USABLE : OrderStatus.RET_PRODUCT_USABLE_REFUNDED,

                     OrderStatus.RET_RECEIVED_DAMAGED : OrderStatus.RET_REFUNDED_RCVD_DAMAGED,

                     OrderStatus.RET_LOST_IN_TRANSIT : OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT,

                     OrderStatus.COD_VERIFICATION_PENDING : OrderStatus.COD_VERIFICATION_FAILED,

                     OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.INVENTORY_LOW : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.ACCEPTED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,

                     OrderStatus.BILLED : OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST,

                     OrderStatus.CANCEL_REQUEST_CONFIRMED : OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST

                     }

creditorTicketSizeMap = {}

creditorDueDateMap = {}

stateIdMap = {}

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