Subversion Repositories SmartDukaan

#brandAndPriceSql = "select b.brand, GROUP_CONCAT(distinct b.user_id), p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange, 'BPP' as SubCategory, 'CB' as Main_Category from brand_preferences b join price_preferences p on b.user_id= p.user_id where b.status='show' and trim(coalesce(b.brand, '')) <>'' group by b.brand, pricerange, p.category_id"

#clickAndPriceSql = "select b.brand, GROUP_CONCAT(distinct b.user_id), p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange, 'CPP' as SubCategory, 'CB' as Main_Category from clicks b join price_preferences p on b.user_id= p.user_id where trim(coalesce(b.brand, '')) <>'' group by b.brand, pricerange, p.category_id"

import os

import re

import smtplib

import csv

import MySQLdb

from dtr.storage import DataService

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

    amountPaid = None

    quantity = None

    unitPrice = None

    status = None

    createdTime = None

    def __init__(self,orderId, merchantOrderId, merchantSubOrderId, storeId, userId, productCode, productName, 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.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']

                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, productCode, subOrder['productTitle'], 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.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, GROUP_CONCAT(distinct x.userid) userids, 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) as x group by x.brand, x.pricerange, x.category_id UNION select y.brand, GROUP_CONCAT(distinct y.userid) userids, 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) as y group by y.brand, 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:

        sql = "insert into pushnotificationusergroups (brand, userids, min_price, max_price, pricerange, category_id) values('%s', '%s', %f, %f, '%s', %d)"%(userGroup[0], userGroup[1], userGroup[2], userGroup[3], userGroup[4], userGroup[5])

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