Subversion Repositories SmartDukaan

'''
Created on 22-Mar-2010

@author: ashish
'''
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 OneToMany, ManyToOne
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, String, DateTime, Float, Boolean, Enum, Date, Text
import datetime

class Vendor(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    name = Field(String(50))
    warehouses = OneToMany('Warehouse')
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class IgnoredInventoryUpdateItems(Entity):
    warehouse_id = Field(Integer,primary_key=True,autoincrement=False)
    item_id = Field(Integer,primary_key=True,autoincrement=False)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class StateMaster(Entity):
    id = Field(Integer, primary_key=True, autoincrement=False)
    name= Field(String(100), unique=True, index=True)
    stateCode = Field(String(4), unique=True)
    shortName = Field(String(4), unique=True, index=True)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class Warehouse(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    displayName = Field(String(50))
    location = Field(String(200))
    status = Field(Integer)
    addedOn = Field(DateTime)
    lastCheckedOn = Field(DateTime)
    tinNumber = Field(String(50))
    gstin = Field(String(20))
    pincode = Field(String(10))
    vendorString = Field(String(50))
    badInventory = OneToMany('BadInventorySnapshot')
    inventory = OneToMany('CurrentInventorySnapshot')
    inventoryHistory = OneToMany('ItemInventoryHistory')
    state = ManyToOne('StateMaster') 
    logisticsLocation = Field(Integer)
    vendor = ManyToOne('Vendor')
    billingType = Field(Integer)
    inventoryType = Field(Enum('GOOD', 'BAD', 'VIRTUAL'))
    warehouseType = Field(Enum('OURS', 'THIRD_PARTY'))
    shippingWarehouseId = Field(Integer)
    billingWarehouseId = Field(Integer)
    transferDelayInHours = Field(Integer)
    isAvailabilityMonitored = Field(Boolean)
    source = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
    def __repr__(self):
        return "<warehouse>%s</warehouse>" %(self.location)

class VendorItemMapping(Entity):
    vendor = ManyToOne('Vendor', primary_key=True)
    item_key = Field(String(200), primary_key=True)
    item_id = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class VendorItemPricing(Entity):
    vendor = ManyToOne('Vendor', primary_key=True)
    item_id = Field(Integer, primary_key=True)
    mop = Field(Float)
    dealerPrice = Field(Float)
    transfer_price = Field(Float)
    nlc = Field(Float)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class VendorHolidays(Entity):
    vendor = ManyToOne('Vendor', primary_key=True)
    date = Field(Date, primary_key=True)
    occasion = Field(String(100))
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
        
class CurrentInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True)
    warehouse = ManyToOne("Warehouse", primary_key=True)
    availability = Field(Integer)
    reserved = Field(Integer)
    held = Field(Integer, default=0, server_default="0")
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class HoldInventoryDetail(Entity):
    item_id = Field(Integer, primary_key=True)
    warehouse = ManyToOne("Warehouse", primary_key=True)
    held = Field(Integer)
    source = Field(Integer, primary_key=True)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class CurrentReservationSnapshot(Entity):
    item_id = Field(Integer, primary_key=True)
    warehouse_id = Field(Integer, primary_key=True)
    source_id = Field(Integer, primary_key=True)
    order_id = Field(Integer, primary_key=True)
    created_timestamp = Field(DateTime)
    promised_shipping_timestamp = Field(DateTime)
    reserved = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
        
class BadInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True)
    warehouse = ManyToOne("Warehouse", primary_key=True)
    availability = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class SupplierInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True)
    warehouse = ManyToOne("Warehouse", primary_key=True)
    availability = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class ItemInventoryHistory(Entity):
    id = Field(Integer, primary_key=True, autoincrement = True)    
    timestamp = Field(DateTime, default=datetime.datetime.now())
    availability = Field(Integer)
    item_id = Field(Integer)
    warehouse = ManyToOne("Warehouse")
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
    def __repr__(self):
        pass

class MissedInventoryUpdate(Entity):
    id = Field(Integer, primary_key=True, autoincrement=True)
    itemKey = Field(String(200))
    quantity = Field(Integer)
    warehouseId = Field(Integer)
    isIgnored = Field(Boolean)
    timestamp = Field(DateTime)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class ItemAvailabilityCache(Entity):
    itemId = Field(Integer, primary_key=True, autoincrement=False)
    sourceId = Field(Integer, primary_key=True, autoincrement=False)
    warehouseId = Field(Integer)
    expectedDelay = Field(Integer)
    billingWarehouseId = Field(Integer)
    sellingPrice = Field(Float) 
    totalAvailability = Field(Integer)
    weight = Field(Integer, default=300, server_default="300")
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class ItemLocationAvailabilityCache(Entity):
    item_id = Field(Integer, primary_key=True, autoincrement=False)
    location_id = Field(Integer, primary_key=True, autoincrement=False)
    physical_availability = Field(Integer)
    virtual_availability = Field(Integer)
    min_transfer_delay = Field(Integer)
    max_transfer_delay = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class ItemStockPurchaseParams(Entity):
    item_id = Field(Integer, primary_key=True, autoincrement=False)
    numOfDaysStock = Field(Integer)
    minStockLevel = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class OOSStatus(Entity):
    item_id = Field(Integer, primary_key=True, autoincrement=False)
    sourceId = Field(Integer, primary_key=True, autoincrement=False)
    date = Field(DateTime, primary_key=True, autoincrement=False)
    is_oos = Field(Boolean)
    num_orders = Field(Integer)
    rto_orders = Field(Integer)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class AmazonInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    reserved = Field(Integer)
    is_oos = Field(Boolean)
    lastUpdatedOnAmazon =  Field(DateTime)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class AmazonFbaInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    inbound = Field(Integer)
    reserved = Field(Integer)
    unfulfillable = Field(Integer)
    location = Field(Integer,primary_key=True,autoincrement=False)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class SnapdealInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    lastUpdatedOnSnapdeal = Field(DateTime)
    pendingOrders = Field(Integer)
    is_oos = Field(Boolean)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")

class FlipkartInventorySnapshot(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    createdOrders = Field(Integer)
    heldOrders = Field(Integer)
    is_oos = Field(Boolean)
    lastUpdatedOnFlipkart =  Field(DateTime)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class SnapdealStockAtEOD(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    date =  Field(DateTime, primary_key=True,autoincrement=False)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class FlipkartStockAtEOD(Entity):
    item_id = Field(Integer, primary_key=True,autoincrement=False)
    availability = Field(Integer)
    date =  Field(DateTime, primary_key=True,autoincrement=False)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")
    
class StockWeightedNlcInfo(Entity):
    itemId = Field(Integer, primary_key=True,autoincrement=False)
    source = Field(Integer, primary_key=True,autoincrement=False)
    updatedTimestamp = Field(DateTime, primary_key=True,autoincrement=False)
    grnDetail = Field(Text)
    stockQuantity = Field(Integer)
    avgWeightedNlc = Field(Float)
    using_options(shortnames=True)
    using_table_options(mysql_engine="InnoDB")    
def initialize(dbname='inventory', db_hostname="localhost", setup=True):
    #metadata.bind = "sqlite:///inventory-new.sqlite" #need to read it from configserver.
    #metadata.bind = 'mysql://root:shop2020@localhost/inventory'
    iengine = create_engine('mysql://root:shop2020@' + db_hostname + '/' + dbname,
                            pool_recycle=3600, pool_timeout=30)
    metadata.bind = iengine
    metadata.bind.echo = False
    setup_all(setup)

if __name__=="__main__":
    initialize()