Subversion Repositories SmartDukaan

import os
import re
import smtplib
import csv
import MySQLdb
from dtr.storage import DataService, Mongo
from dtr.storage.DataService import MerchantSubOrders
from elixir import *
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
    between
import datetime
from pymongo.mongo_client import MongoClient

import optparse
    
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )

DataService.initialize(db_hostname="localhost")
client = MongoClient('mongodb://localhost:27017/') 

def to_py_date(java_timestamp):
    date = datetime.datetime.fromtimestamp(java_timestamp)
    return date

class MerchantSubOrder():
    orderId = None
    merchantOrderId = None
    merchantSubOrderId = None
    storeId = None
    userId = None
    productCode = None
    brand = None
    productName = None
    category_id=None
    amountPaid = None
    quantity = None
    unitPrice = None
    status = None
    createdTime = None
    
    def __init__(self,orderId, merchantOrderId, merchantSubOrderId, storeId, userId, productCode, productName,category_id, amountPaid, quantity,unitPrice, status, createdTime):
        self.orderId = orderId
        self.merchantOrderId = merchantOrderId
        self.merchantSubOrderId = merchantSubOrderId
        self.storeId = storeId
        self.userId = userId 
        self.productCode = productCode
        self.productName= productName
        self.category_id= category_id
        self.amountPaid = amountPaid
        self.quantity = quantity
        self.unitPrice = unitPrice
        self.status = status
        if createdTime is not None:
            self.createdTime = to_py_date(createdTime)
        else:
            self.createdTime = None
        self.userId = userId
        

def getAndStoreMerchantSubOrders():
    existingMaxOrderId = session.query(func.max(MerchantSubOrders.orderId)).one()
    db = client.Dtr
    collection = db.merchantOrder
    orders = collection.find({'orderId':{'$gt':int(existingMaxOrderId[0])}})
    catalogdb = client.Catalog
    masterDataCollection = catalogdb.MasterData
    for order in orders:
        if order.has_key('subOrders'):
            orderId = order['orderId']
            merchantOrderId = order['merchantOrderId']
            storeId = order['storeId']
            userId = order['userId']
            createdOnInt = None
            if order.has_key('createdOnInt'):
                createdOnInt = order['createdOnInt']
            subOrders = order['subOrders']
            for subOrder in subOrders:
                unitPrice = 0
                if subOrder.has_key('unitPrice'):
                    unitPrice = subOrder['unitPrice']
                productCode = "Undefined"
                if subOrder.has_key('productCode'):
                    productCode = subOrder['productCode']
                    skuData=Mongo.getItemByMerchantIdentifier(productCode,storeId)
                    if(skuData.has_key('category_id')):
                        category_id=skuData['category_id']
                    else:
                        category_id=None      
                
                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, productCode, subOrder['productTitle'], category_id,subOrder['amountPaid'], subOrder['quantity'], unitPrice, subOrder['status'], createdOnInt)
                product = None
                if storeId in (1,2,4,5):
                    product = list(masterDataCollection.find({'identifier':productCode.strip(), 'source_id':storeId}))
                elif storeId == 3:
                    product = list(masterDataCollection.find({'secondaryIdentifier':productCode.strip(), 'source_id':storeId}))
                if product is not None and len(product)>0:
                    merchantSubOrder.brand = product[0]["brand"]
                else:
                    merchantSubOrder.brand = 'NA'
                    
                existingMerchantSubOrder = MerchantSubOrders.query.filter(MerchantSubOrders.orderId == orderId).filter(MerchantSubOrders.merchantOrderId == merchantOrderId).filter(MerchantSubOrders.merchantSubOrderId == merchantSubOrder.merchantSubOrderId).first()
                if existingMerchantSubOrder is None:
                    merchantSubOrderDetail = MerchantSubOrders()
                    merchantSubOrderDetail.orderId = merchantSubOrder.orderId
                    merchantSubOrderDetail.merchantOrderId = merchantSubOrder.merchantOrderId
                    merchantSubOrderDetail.merchantSubOrderId = merchantSubOrder.merchantSubOrderId
                    merchantSubOrderDetail.storeId = merchantSubOrder.storeId
                    merchantSubOrderDetail.userId = merchantSubOrder.userId
                    merchantSubOrderDetail.productCode = merchantSubOrder.productCode
                    merchantSubOrderDetail.brand = merchantSubOrder.brand
                    merchantSubOrderDetail.productName = merchantSubOrder.productName
                    merchantSubOrderDetail.categoryId = merchantSubOrder.category_id
                    merchantSubOrderDetail.amountPaid = merchantSubOrder.amountPaid
                    merchantSubOrderDetail.quantity = merchantSubOrder.quantity
                    merchantSubOrderDetail.unitPrice = merchantSubOrder.unitPrice
                    merchantSubOrderDetail.status = merchantSubOrder.status
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
                    session.commit()          
            

def initiateUserGroupSegmentation():
    userGroupSegmentationQuery ="select x.brand, x.userid, x.min_price, x.max_price, x.pricerange, x.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price >0 and p.max_price >0 group by b.brand, pricerange, p.category_id, b.user_id) as x join brands br on (x.brand=br.name and x.category_id=br.category_id) group by x.brand, x.userid, x.pricerange, x.category_id UNION select y.brand, y.userid, y.min_price, y.max_price, y.pricerange, y.category_id from (select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from brand_preferences b left outer join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from clicks b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and p.min_price is null and p.max_price is null group by b.brand, b.user_id UNION select b.brand, s.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from search_terms s join (select distinct brand from brand_preferences where trim(coalesce(brand, '')) <>'') b on lower(s.search_term) like concat('%', lower(b.brand), '%') left outer join price_preferences p on s.user_id= p.user_id where p.min_price is null and p.max_price is null group by b.brand, s.user_id UNION select b.brand, b.userId userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from merchantsuborders b left outer join price_preferences p on b.userId= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.userId UNION select b.brand, b.user_id userid, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange from flipkartorders b left outer join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' and b.brand!='NA' and p.min_price is null and p.max_price is null group by b.brand, b.user_id) as y join brands br on (y.brand=br.name and y.category_id=br.category_id) group by y.brand, y.userid, y.category_id"
    cursor = db.cursor()
    
    deleteAllUserGroups = "delete from pushnotificationusergroups"
    try:
        cursor.execute(deleteAllUserGroups)
        db.commit()
    except:
        db.rollback()
        
    cursor.execute(userGroupSegmentationQuery)
    allUserGroups = cursor.fetchall()
    for userGroup in allUserGroups:
        min_price = userGroup[2]
        if min_price is None:
            min_price = 0.0
        max_price = userGroup[3]
        if max_price is None:
            max_price = 0.0
        category_id = userGroup[5]
        if category_id is None:
            category_id= 0
            
        sql = "insert into pushnotificationusergroups (brand, userids, min_price, max_price, pricerange, category_id) values('%s', %d, %f, %f, '%s', %d)"%(userGroup[0], userGroup[1], min_price, max_price, userGroup[4], category_id)
        print sql
        try:
            dtrdb = MySQLdb.connect('localhost',"root","shop2020","dtr" )
            cursor = dtrdb.cursor()
            cursor.execute(sql)
            dtrdb.commit()
            dtrdb.close()
        except:
            dtrdb.rollback()
            dtrdb.close()
        
def main():
    parser = optparse.OptionParser()
    parser.add_option("-T", "--segmenttype", dest="segmenttype",
                      default="group",
                      type="str", help="To avoid not needed order backups",
                      metavar="SEGMENTTYPE")
    (options, args) = parser.parse_args()
    if options.segmenttype == 'full':
        getAndStoreMerchantSubOrders()
        initiateUserGroupSegmentation()
    if options.segmenttype == 'group':
        initiateUserGroupSegmentation()
    if options.segmenttype == 'order':
        getAndStoreMerchantSubOrders()
    db.close()
    if session.is_active:
        print "session is active. closing it."
        session.close()
    client.close()
    
if __name__=='__main__':
    main()