Subversion Repositories SmartDukaan

Rev

Rev 15812 | Rev 16201 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 15812 Rev 16083
Line 1... Line -...
1 
#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"
 - 
 
2 
#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"
 - 
 
3 
import os
 1 
import os
4 
import re
 2 
import re
5 
import smtplib
 3 
import smtplib
6 
import csv
 4 
import csv
7 
import MySQLdb
 5 
import MySQLdb
Line 111... Line 109...
111 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
 109 
                    merchantSubOrderDetail.createdTime = merchantSubOrder.createdTime
112 
                    session.commit()
110 
                    session.commit()
113
111
114 
 
 112 
 
115 
def initiateUserGroupSegmentation():
 113 
def initiateUserGroupSegmentation():
116 
    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) as x 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) as y group by y.brand, y.userid, y.category_id"
 114 
    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) 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) 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"
117 
    cursor = db.cursor()
 115 
    cursor = db.cursor()
118
116
119 
    deleteAllUserGroups = "delete from pushnotificationusergroups"
 117 
    deleteAllUserGroups = "delete from pushnotificationusergroups"
120 
    try:
 118 
    try:
121 
        cursor.execute(deleteAllUserGroups)
 119 
        cursor.execute(deleteAllUserGroups)