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