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
        if createdTime is not None:
            self.createdTime = to_py_date(createdTime)
        else:
            self.createdTime = None
        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']
            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':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 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()          
            

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