Subversion Repositories SmartDukaan

'''

Created on 29-Mar-2010

@author: ashish

'''

from MySQLdb.constants.FLAG import AUTO_INCREMENT

from elixir import metadata, setup_all

from elixir.entity import Entity

from elixir.fields import Field

from elixir.options import using_options, using_table_options

from elixir.relationships import ManyToOne, OneToMany

from sqlalchemy.engine import create_engine

from sqlalchemy.types import Integer, String, Float, DateTime, Boolean, Enum, \

    Numeric, BigInteger, LargeBinary,Date

#===============================================================================

# Different entities in the model

#===============================================================================

def initialize(dbname='transaction', db_hostname="localhost", echoOn=True):

    #metadata.bind = "sqlite:///Transactionsnew.sqlite"

    #metadata.bind = 'mysql://root:shop2020@localhost/transaction'

    engine = create_engine('mysql://root:shop2020@' + db_hostname + '/' + dbname, pool_recycle=7200)

    metadata.bind = engine

    metadata.bind.echo = echoOn

    setup_all(True)

if __name__=="__main__":

    initialize()

class InvoiceIDGenerator(Entity):

    id = Field(Integer)

    orderType = Field(Integer, primary_key = True)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")   

class InvoiceCounterGenerator(Entity):

    id = Field(Integer)

    orderType = Field(Integer, primary_key = True)

    stateId = Field(Integer, primary_key = True)

    prefix = Field(String(20))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class RechargeVoucherTracker(Entity):

    order = ManyToOne("Order")

    voucherType = Field(Integer)

    amount = Field(Integer)

    voucherIssued = Field(Boolean)

    voucherCode = Field(String(30))

    issuedOn = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class LineItem(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    item_id = Field(Integer)

    productGroup = Field(String(100))

    brand = Field(String(100))

    model_number = Field(String(50))

    model_name = Field(String(50))

    color = Field(String(20))

    extra_info = Field(String(100))

    quantity = Field(Float)

    mrp = Field(Float)

    unit_price = Field(Float)

    unit_weight = Field(Float)

    total_price = Field(Float)

    transfer_price = Field(Float)

    nlc = Field(Float)

    total_weight = Field(Float)

    item_number = Field(String(50))

    serial_number = Field(String(8192)) 

    dealText = Field(String(100))

    warranty_expiry_timestamp = Field(DateTime)

    order = ManyToOne("Order")

    vatRate = Field(Float)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

    def __repr__(self):

        return "{0} {1} {2} {3}".format(self.brand or "", self.model_name or "", self.model_number or "", self.color or "")

class Order(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    warehouse_id = Field(Integer)

    logistics_provider_id = Field(Integer)

    airwaybill_no = Field(String(50))

    tracking_id = Field(String(50))

    expected_delivery_time = Field(DateTime)

    promised_delivery_time = Field(DateTime)

    expected_shipping_time = Field(DateTime)

    promised_shipping_time = Field(DateTime)

    courier_delivery_time = Field(DateTime)

    customer_id = Field(Integer)

    customer_name = Field(String(50))

    customer_mobilenumber = Field(String(20))

    customer_pincode = Field(String(10))

    customer_address1 = Field(String(100))

    customer_address2 = Field(String(100))

    customer_city = Field(String(100))

    customer_state = Field(String(100))

    customer_email = Field(String(50))

    status = Field(Integer)

    statusDescription = Field(String(50))

    total_amount = Field(Float)

    gvAmount = Field(Float)

    total_weight = Field(Float)

    invoice_number = Field(String(30))

    billed_by = Field(String(30))

    created_timestamp = Field(DateTime)

    accepted_timestamp = Field(DateTime)

    billing_timestamp = Field(DateTime)

    shipping_timestamp = Field(DateTime)

    pickup_timestamp = Field(DateTime)

    delivery_timestamp = Field(DateTime)

    outofstock_timestamp = Field(DateTime)

    lineitems = OneToMany("LineItem")

    transaction = ManyToOne("Transaction")

    insuranceDetails = OneToMany("InsuranceDetailForOrder")

    dataInsuranceDetails = OneToMany("DataInsuranceDetailForOrder")

    jacket_number = Field(Integer)

    receiver = Field(String(50))

    batchNo = Field(Integer)

    serialNo = Field(Integer)

    doaFlag = Field(Boolean)

    pickupRequestNo = Field(String(20))

    doa_auth_timestamp = Field(DateTime)

    doa_pickup_timestamp = Field(DateTime)

    received_return_timestamp = Field(DateTime)

    reship_timestamp = Field(DateTime)

    refund_timestamp = Field(DateTime)

    new_order_id = Field(Integer)

    purchase_order_id = Field(Integer)

    cod = Field(Boolean)

    refunded_by = Field(String(30))

    refund_reason = Field(String(256))

    verification_timestamp = Field(DateTime)

    delay_reason = Field(Enum('INVENTORY_LOW_PHASED_OUT', 'INVENTORY_LOW_COLOR_NOT_AVAILABLE',\

                              'INVENTORY_LOW_REVERSAL_NOT_ON_TIME', 'INVENTORY_LOW_PRODUCT_NOT_SEALED',\

                              'COURIER_DELAY_NOT_DELIVERED_TO_COURIER_ON_TIME', 'COURIER_DELAY_DID_NOT_CONNECT',\

                              'COURIER_DELAY_CUSTOMER_NOT_AVAILABLE', 'COURIER_DELAY_INCORRECT_ADDRESS',\

                              'COURIER_DELAY_OCTROI_DELAY', 'COURIER_DELAY_FORCES_OF_NATURE',\

                              'COD_VERIFICATION_DELAY', 'PAYMENT_FLAGGED', 'OTHERS'))

    cod_reconciliation_timestamp = Field(DateTime)

    previousStatus = Field(Integer)

    vendorId = Field(Integer)

    delayReasonText= Field(String(250))

    doa_logistics_provider_id = Field(Integer)

    local_connected_timestamp = Field(DateTime)

    reached_destination_timestamp = Field(DateTime)

    first_dlvyatmp_timestamp = Field(DateTime)

    orderInventory = OneToMany("OrderInventory")

    vendor_paid = Field(Boolean, default=0, server_default="0")

    originalOrderId = Field(Integer)

    fulfilmentWarehouseId = Field(Integer)

    orderType = Field(Integer)

    pickupStoreId = Field(Integer, default=0, server_default="0")

    otg = Field(Boolean, default=0, server_default="0")

    insurer = Field(Integer, default=0, server_default="0")

    insuranceAmount = Field(Float, default=0, server_default="0")

    freebieItemId = Field(Integer, default=0, server_default="0")

    source = Field(Integer, default=1, server_default="1")

    storeId = Field(Integer, default=0, server_default="0")

    advanceAmount = Field(Float, default=0, server_default="0")

    productCondition = Field(Integer, default=0, server_default="0")

    dataProtectionInsurer = Field(Integer, default=0, server_default="0")

    dataProtectionAmount = Field(Float, default=0, server_default="0")

    taxType = Field(Integer, default=0, server_default="0")

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class CodVerificationAgent(Entity):

    orderId = Field(Integer, primary_key=True, autoincrement=True)

    verificationAgent = Field(String(200))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class Transaction(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    createdOn = Field(DateTime)

    status = Field(Integer)

    status_message = Field(String(100))

    customer_id = Field(Integer)

    shopping_cart_id = Field(Integer)

    session_source = Field(String(100))

    session_start_time = Field(DateTime)

    first_source = Field(String(100))

    first_source_start_time = Field(DateTime)

    orders = OneToMany("Order")

    coupon_code = Field(String(20))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class Alert(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    type = Field(Integer)

    status = Field(Integer)

    timestamp = Field(DateTime)

    description = Field(String(100))

    warehouseId = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class BatchNoGenerator(Entity):

    id=Field(Integer, primary_key=True)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class TransactionRequiringExtraProcessing(Entity):

    transaction_id = Field(Integer, primary_key=True, autoincrement=False)

    category = Field(Enum('COD_VERIFICATION', 'DELAYED_DELIVERY', 'PAYMENT_FLAGGED', 'RECHARGE_UNKNOWN'), primary_key=True, autoincrement=False)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class OrderInventory(Entity):

    order = ManyToOne("Order", primary_key=True)

    itemId = Field(Integer)

    timestamp = Field(DateTime)

    hotspotAction = Field(Integer)

    estimate = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class EBSSettlementSummary(Entity):

    settlementId = Field(Integer, primary_key = True)

    settlementDate = Field(DateTime)

    transactionDateFrom = Field(DateTime)

    transactionDateTo = Field(DateTime)

    amount = Field(Numeric(precision=11, scale=3, asdecimal=False))

    detailsUploaded = Field(Boolean)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class PaymentSettlement(Entity):

    referenceId = Field(Integer)    #PaymentID in case of prepaid & Order Id in case of COD

    originalOrderId = Field(Integer)    #originalOrderId in case of prepaid is NULL & Order Id of original Order or Order Id in case of COD

    paymentGatewayId = Field(Integer)

    settlementDate = Field(DateTime)

    serviceTax = Field(Numeric(precision=11, scale=3, asdecimal=False))

    otherCharges = Field(Numeric(precision=11, scale=3, asdecimal=False))

    netCollection = Field(Numeric(precision=11, scale=3, asdecimal=False))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class Attribute(Entity):

    orderId = Field(Integer)

    name = Field(String(100))

    value = Field(String(100))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class EmiScheme(Entity):

    id  = Field(Integer, primary_key=True)

    gatewayId  = Field(Integer)

    bankId = Field(Integer)

    tenure = Field(Integer)

    bankName = Field(String(256))

    tenureDescription = Field(String(256))

    minAmount = Field(Integer)

    chargeType = Field(Integer)

    chargeValue = Field(Float)

    interestRate = Field(Float)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class MiscCharges(Entity):

    transaction = ManyToOne("Transaction", primary_key=True)

    chargeType = Field(Integer, primary_key=True, autoincrement=False)

    chargeAmount = Field(Float)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class BlockedIpRange(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    start = Field(BigInteger)

    end = Field(BigInteger)

    expiredOn = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class DeniedIpAddress(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    ip = Field(String(256))

    deniedOn = Field(DateTime)

    rechargeType = Field(Integer)

    deviceNumber = Field(String(128))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class InsuranceDetailForOrder(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    dob = Field(String(64))

    guardianName = Field(String(255))

    startDate = Field(DateTime)

    expiryDate = Field(DateTime)

    isDeclared = Field(Boolean, default=0)

    order = ManyToOne("Order")

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

    def __init__(self):

        self.isDeclared = 0

class DataInsuranceDetailForOrder(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    startDate = Field(DateTime)

    expiryDate = Field(DateTime)

    isDeclared = Field(Boolean, default=0, server_default="0")

    order = ManyToOne("Order")

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class DocumentStore(Entity):

    docType = Field(Integer, primary_key=True, autoincrement=False)

    docSource = Field(Integer, primary_key=True, autoincrement=False)

    document  = Field(LargeBinary)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class Company(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    name = Field(String(255))

    Address = Field(String(255))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class WalletForCompany(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    companyId = Field(Integer)

    amount = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class WalletHistoryForCompany(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    walletId = Field(Integer)

    amount = Field(Integer)

    transactionTime = Field(DateTime)

    openingBal = Field(Integer)

    closingBal = Field(Integer)

    referenceNumber = Field(Integer)

    description = Field(String(255))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class RechargeTransaction(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    storeId = Field(Integer)

    amount = Field(Integer)

    transactionTime = Field(DateTime)

    responseTime = Field(DateTime)

    description = Field(String(255))

    spiceTID = Field(String(255))

    aggTID = Field(String(255))

    providerTID = Field(String(255))

    plan = Field(String(255))

    deviceNum = Field(String(64))

    deviceType = Field(Integer)

    isFrc = Field(Boolean)

    email = Field(String(255))

    discount = Field(Integer)

    paymentAmount = Field(Integer)

    payMethod = Field(Integer, default=0, server_default="0")

    status = Field(Integer)

    invoiceNumber = Field(Integer)

    circleId = Field(Integer)

    operatorId = Field(Integer)

    name = Field(String(255))

    simNum = Field(String(255))

    cafNum = Field(String(255))

    ipAddress = Field(String(255))

    alternateNumber = Field(String(255))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class OperatorSeries(Entity):

    series = Field(Integer, primary_key=True, autoincrement=False)

    operatorId = Field(Integer)

    circleId = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class FRC(Entity):

    id = Field(Integer, primary_key=True)

    operatorId = Field(Integer)

    circleId = Field(Integer)

    denomination = Field(Integer)

    maxDiscount = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class HotspotStore(Entity):

    id = Field(Integer, primary_key=True)

    hotspotId = Field(String(3))

    companyId = Field(Integer)

    name = Field(String(100))

    city = Field(String(100))

    collectedAmount = Field(Integer)

    availableLimit = Field(Integer)

    creditLimit = Field(Integer)

    salt = Field(String(100))

    password = Field(String(100))

    isActive = Field(Boolean)

    circleId = Field(Integer)

    email = Field(String(100))

    line1 = Field(String(100))

    line2 = Field(String(100))

    state = Field(String(100))

    pin = Field(String(10))

    phone = Field(String(20))

    tin = Field(String(100))

    approvalEmail = Field(String(255))

    clusterEmail = Field(String(100))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class RechargeCollection(Entity):

    hotspotId = Field(String(3), primary_key=True, autoincrement=False)

    reconDate = Field(Integer, primary_key=True, autoincrement=False)

    cash = Field(Integer)

    hdfc = Field(Integer)

    grossAmount = Field(Integer)

    discount = Field(Integer)

    netCollection = Field(Integer)

    addedAt = Field(DateTime)

    pushedAt = Field(DateTime)

    pushedToOcr = Field(Boolean)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class StoreOrderCollection(Entity):

    hotspotId = Field(String(3), primary_key=True, autoincrement=False)

    orderId = Field(Integer, primary_key=True, autoincrement=False)

    collectionType = Field(String(255), primary_key=True, autoincrement=False)

    productName = Field(String(255))

    advanceAmount = Field(Integer)

    cash = Field(Integer)

    card = Field(Integer)

    addedAt = Field(DateTime)

    pushedAt = Field(DateTime)

    pushedToOcr = Field(Boolean)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class SourceDetail(Entity):

    id = Field(Integer, primary_key=True, autoincrement=False)

    name = Field(String(255))

    email = Field(String(255))

    tinNumber = Field(String(255))

    lastUpdatedOn = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class AmazonOrder(Entity):

    orderId = Field(Integer, primary_key=True, autoincrement=False)

    amazonOrderCode = Field(String(255))

    amazonOrderItemCode = Field(String(255))

    transactionId = Field(Integer)

    item_id = Field(Integer)

    status = Field(String(50))

    purchaseDateOnAmazon = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class StoreOrderDetail(Entity):

    orderId = Field(Integer, primary_key=True, autoincrement=False)

    storeId = Field(Integer, primary_key=True, autoincrement=False)

    advanceAmount = Field(Float)

    cashAmount = Field(Float)

    cardAmount = Field(Float)

    payStatus = Field(Integer)

    edcBank = Field(String(100))

    cashRefundAmount = Field(Float)

    cardRefundAmount = Field(Float)

    approvalCode = Field(String(100))

    cardType = Field(String(100))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class EdcBank(Entity):

    id = Field(Integer, primary_key=True, autoincrement=True)

    name = Field(String(255))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class HotspotServiceMatrix(Entity): 

    storeId = Field(Integer, primary_key=True)

    hotspotId = Field(String(3))

    rechargeService = Field(Boolean, default=0, server_default="0")

    storeWebsiteService = Field(Boolean, default=0, server_default="0")

    pickupFromStoreService = Field(Boolean, default=0, server_default="0")

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class  EbayOrder(Entity):

    orderId = Field(Integer, primary_key=True, autoincrement=False)

    salesRecordNumber = Field(Integer)

    paisaPayId = Field(String(32))

    ebayListingId = Field(String(32))

    subsidyAmount = Field(Float)

    ebayTxnDate = Field(DateTime)

    transactionId = Field(String(16))

    listingName = Field(String(128))

    listingPrice = Field(Float)

    bluedartPaisaPayRef = Field(String(16))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class  SnapdealOrder(Entity):

    orderId = Field(Integer)

    subOrderId = Field(String(32), primary_key=True, autoincrement=False)

    referenceCode = Field(String(32),primary_key=True, autoincrement=False)

    snapdealTxnDate = Field(DateTime)

    productName = Field(String(128))

    listingPrice = Field(Float)

    maxNlc = Field(Float)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class AmazonFbaSalesSnapshot(Entity):

    dateOfSale = Field(Date, primary_key=True)

    item_id = Field(Integer, primary_key=True,autoincrement=False)

    totalOrderCount = Field(Integer)

    amazonFbaInventory = Field(Integer)

    isOutOfStock = Field(Boolean)

    salePrice = Field(Float)

    ourPrice = Field(Float)

    minFbaPrice = Field(Float)

    minMfnPrice = Field(Float)

    totalSale = Field(Numeric(precision=11, scale=3, asdecimal=False))

    promotionSale =  Field(Numeric(precision=11, scale=3, asdecimal=False))

    promotionOrderCount = Field(Integer)

    ourPriceSnapshotDate  =  Field(DateTime)

    salePriceSnapshotDate  = Field(DateTime)

    minMfnPriceSnapshotDate = Field(DateTime)

    minFbaPriceSnapshotDate = Field(DateTime)

    fcLocation = Field(Integer, primary_key=True,autoincrement=False)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class SpiceTransactionHistory(Entity):

    orderId = Field(Integer, primary_key=True, autoincrement=False)

    spiceTID = Field(String(32))

    reponseCode = Field(String(32))

    rechargeTime = Field(DateTime)

    reconciliationTime = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class FlipkartOrder(Entity):   

    orderId = Field(Integer)

    flipkartSubOrderId = Field(String(30),primary_key=True, autoincrement=False)

    flipkartOrderId = Field(String(30),primary_key=True, autoincrement=False)

    flipkartTxnDate = Field(DateTime)

    shippingPrice = Field(Float)

    octroiFee = Field(Float)

    emiFee = Field(Float)

    maxNlc = Field(Float)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class AmazonFbaOrderReturns(Entity):

    amazonOrderId = Field(String(30),primary_key=True, autoincrement=False)

    insertionTimestamp = Field(DateTime, primary_key=True, autoincrement=False)

    sku = Field(Integer, primary_key=True, autoincrement=False)

    creationTimestamp = Field(DateTime)    

    shippedQuantity = Field(Integer)

    sellableReturnQuantity = Field(Integer)

    nonSellableReturnQuantity = Field(Integer)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class MobileActivationRecord(Entity):

    orderId = Field(Integer)

    deviceNumber = Field(String(64))

    circle = Field(String(100))

    operator = Field(String(100))

    activationTime = Field(DateTime)

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")

class FlipkartAdvantageOrder(Entity):

    fkOrderId = Field(String(30),primary_key=True, autoincrement=False)

    fkOrderItemId = Field(String(30),primary_key=True, autoincrement=False)

    sku = Field(Integer)

    creationTimestamp = Field(DateTime)

    customerName = Field(String(50))

    customerAddress = Field(String(500))

    pincode = Field(String(10))

    customerCity = Field(String(30))

    customerState = Field(String(30))

    customerPhone = Field(String(30))

    status = Field(String(20))

    quantity = Field(Integer)

    totalPrice = Field(Float)

    listPrice = Field(Float)

    modifiedDate = Field(DateTime)

    listingId = Field(String(50))

    cancelReason = Field(String(100))

    returnReason = Field(String(100))

    freebieItemId = Field(String(20))

    productTitle = Field(String(200))

    using_options(shortnames=True)

    using_table_options(mysql_engine="InnoDB")