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

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

cursor = db.cursor()

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

        self.createdTime = to_py_date(createdTime)

        self.userId = userId

def getAndStoreMerchantSubOrders():

    db = client.Dtr

    collection = db.merchantOrder

    orders = collection.find()

    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']

            subOrders = order['subOrders']

            for subOrder in subOrders:

                merchantSubOrder = MerchantSubOrder(orderId, merchantOrderId, subOrder['merchantSubOrderId'], storeId, userId, subOrder['productCode'], subOrder['productTitle'], subOrder['amountPaid'], subOrder['quantity'], subOrder['status'], subOrder['createdTime'])

                product = None

                if storeId in (1,2,4,5):

                    product = list(masterDataCollection.find({'identifier':subOrder['productCode'].strip(), 'source_id':storeId}))

                elif storeId == 3:

                    product = list(masterDataCollection.find({'secondaryIdentifier':subOrder['productCode'].strip(), 'source_id':storeId}))

                if product is not None:

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

#userGroupSegmentationQuery ="select x.brand, GROUP_CONCAT(distinct x.userids) userids, x.min_price, x.max_price, x.pricerange, x.category_id from (select b.brand, GROUP_CONCAT(distinct b.user_id) userids, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange 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 UNION select b.brand, GROUP_CONCAT(distinct b.user_id) userids, p.min_price, p.max_price, p.category_id, concat(p.min_price, '-', p.max_price) pricerange 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 UNION select b.brand, GROUP_CONCAT(distinct s.user_id) userids, 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), '%') join price_preferences p on s.user_id= p.user_id group by b.brand, pricerange, p.category_id) as x group by x.brand, x.pricerange, x.category_id"

def main():

    getAndStoreMerchantSubOrders()

    db.close()

    if session.is_active:

        print "session is active. closing it."

        session.close()

    client.close()

if __name__=='__main__':

    main()