Subversion Repositories SmartDukaan

Rev

Rev 15136 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
15114 manish.sha 1 
import os
2 
import re
3 
import smtplib
4 
import csv
5 
import MySQLdb
15134 manish.sha 6 
from dtr.storage import DataService
7 
from dtr.storage.DataService import Users
8 
from elixir import *
9 
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
10 
from sqlalchemy.sql import func
11 
from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, \
15221 manish.sha 12 
    between
15114 manish.sha 13 
 
15134 manish.sha 14 
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
15 
cursor = db.cursor()
15114 manish.sha 16 
 
15134 manish.sha 17 
DataService.initialize(db_hostname="localhost")
15114 manish.sha 18 
 
15134 manish.sha 19 
def main():
15118 manish.sha 20 
 
15221 manish.sha 21 
    sql = "SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers, 'Imeis' Group_Basis FROM  users u LEFT OUTER JOIN devices d ON u.id = d.user_id where trim(coalesce(d.imeinumber, '')) <>'' GROUP BY d.imeinumber UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers, 'Mobile_Nos' Group_Basis FROM users u LEFT OUTER JOIN devices d ON u.id = d.user_id where trim(coalesce(u.mobile_number, '')) <>'' GROUP BY u.mobile_number"
22 
 
15114 manish.sha 23 
    cursor.execute(sql)
24 
    result_data = cursor.fetchall()
25 
    if result_data:
26 
        for record_data in result_data:
15221 manish.sha 27 
            print "User Ids",record_data[0]
28 
            userIds = []
29 
            imeis = []
30 
            mobileNos = []
31 
            referrers = []
32 
            groupbasis = []
33 
            for x in record_data[0].split(','):
34 
                userIds.append(int(x.strip()))
35 
            if record_data[1]:
36 
                for x in record_data[1].split(','):
37 
                    mobileNos.append(str(x.strip()))
38 
            if record_data[2]:
39 
                for x in record_data[2].split(','):
40 
                    imeis.append(str(x.strip()))
41 
            if record_data[3]:
42 
                for x in record_data[3].split(','):
43 
                    referrers.append(str(x.strip()))
44 
            for x in record_data[4].split(','):
45 
                groupbasis.append(str(x.strip()))
46 
 
47 
            users = Users.query.filter(Users.id.in_(tuple(userIds)))
48 
            existingGroupIds = []
15134 manish.sha 49 
            for user in users:
15221 manish.sha 50 
                print user.usergroup_id
51 
                if user.usergroup_id and user.usergroup_id >0:
52 
                    existingGroupIds.append(user.usergroup_id)
53 
 
54 
            existingGroupIds = set(existingGroupIds)
55 
            existingGroupIds = list(existingGroupIds)
56 
 
57 
            print existingGroupIds
58 
            if len(existingGroupIds) >0 and len(existingGroupIds)==1:
59 
                existingGroupId = existingGroupIds[0]
60 
                checkForExistingUserGroup = "select * from usergroups where id=%d"%(existingGroupId)
15134 manish.sha 61 
                cursor.execute(checkForExistingUserGroup)
62 
                existingUserGroup = cursor.fetchone()
15221 manish.sha 63 
 
64 
                newUserIds = existingUserGroup[1]
65 
                newMobileNos = existingUserGroup[2]
66 
                newImeis = existingUserGroup[3]
67 
                newReferrers = existingUserGroup[4]
68 
                newGroupBasis = existingUserGroup[5]
69 
 
70 
                fguserIds = []
71 
                for x in record_data[0].split(','):
72 
                    fguserIds.append(int(x.strip()))
73 
 
74 
                for userId in userIds:
75 
                    if not str(userId) in newUserIds:
76 
                        newUserIds = newUserIds +','+str(userId)
77 
 
78 
                for mobileNo in mobileNos :
79 
                    if not mobileNo in newMobileNos:
80 
                        newMobileNos = newMobileNos +','+mobileNo
81 
 
82 
                for imei in imeis:
83 
                    if not imei in newImeis:
84 
                        newImeis = newImeis+','+imei
85 
 
86 
                for referrer in referrers:
87 
                    if not referrer in newReferrers:
88 
                        newReferrers = newReferrers +','+referrer
89 
 
90 
                if len(newGroupBasis)> 0:
91 
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
92 
                        newGroupBasis = str(record_data[4])
93 
                else:
94 
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
95 
                        newGroupBasis = str(record_data[4])
96 
                    else:
97 
                        for groupbase in groupbasis:
98 
                            if not groupbase in newGroupBasis:
99 
                                newGroupBasis = newGroupBasis +','+groupbase
100 
 
101 
                newGroupBasis = newGroupBasis.strip(',')
102 
                updateUserGroup = "update usergroups set userids ='%s', mobilenos ='%s', imeis ='%s', referrers ='%s', groupbasis ='%s' where id = %d"%(newUserIds, newMobileNos, newImeis, newReferrers, newGroupBasis, existingGroupId)
103 
                print updateUserGroup
15134 manish.sha 104 
                try:
105 
                    cursor.execute(updateUserGroup)
106 
                    db.commit()
107 
                except:
108 
                    # Rollback in case there is any error
109 
                    db.rollback()
110 
 
111 
                for user in users:
15221 manish.sha 112 
                    user.usergroup_id = existingGroupId
15134 manish.sha 113 
                    session.commit()
15221 manish.sha 114 
            elif len(existingGroupIds)>1:
115 
                existingGroupIds.sort()
116 
                stringGroupIds =[]
117 
                for groupId in (existingGroupIds):
118 
                    stringGroupIds.append(str(groupId))
119 
                allUserGroupsSql = "select * from usergroups where id in %s"%(str(tuple(stringGroupIds)))
120 
                print allUserGroupsSql
121 
                cursor.execute(allUserGroupsSql)
122 
                allUserGroups = cursor.fetchall()
123 
                allUserGroups = list(allUserGroups)
124 
                firstUserGroup = allUserGroups[0]
15134 manish.sha 125 
 
15221 manish.sha 126 
                newUserIds = firstUserGroup[1]
127 
                newMobileNos = firstUserGroup[2]
128 
                newImeis = firstUserGroup[3]
129 
                newReferrers = firstUserGroup[4]
130 
                newGroupBasis = firstUserGroup[5]
131 
                moreUsersToConsider = userIds
132 
 
133 
                fguserIds = []
134 
                for x in record_data[0].split(','):
135 
                    fguserIds.append(int(x.strip()))
136 
 
137 
                for usergroup in allUserGroups:
138 
                    print 'UserGroupId',usergroup[0]
139 
 
140 
                    ug_userIds = []
141 
                    ug_imeis = []
142 
                    ug_mobileNos = []
143 
                    ug_referrers = []
144 
                    ug_groupbasis = []
145 
 
146 
                    if usergroup[1]:
147 
                        for x in usergroup[1].split(','):
148 
                            ug_userIds.append(str(x.strip()))
149 
                    if usergroup[2]:
150 
                        for x in usergroup[2].split(','):
151 
                            ug_mobileNos.append(str(x.strip()))
152 
                    if usergroup[3]:
153 
                        for x in usergroup[3].split(','):
154 
                            ug_imeis.append(str(x.strip()))
155 
                    if usergroup[4]:
156 
                        for x in usergroup[4].split(','):
157 
                            ug_referrers.append(str(x.strip()))
158 
                    for x in usergroup[5].split(','):
159 
                        ug_groupbasis.append(str(x.strip()))
15134 manish.sha 160 
 
15221 manish.sha 161 
                    ug_userIds = set(ug_userIds)
162 
                    ug_userIds = list(ug_userIds)
163 
                    ug_imeis = set(ug_imeis)
164 
                    ug_imeis = list(ug_imeis)
165 
                    ug_mobileNos = set(ug_mobileNos)
166 
                    ug_mobileNos = list(ug_mobileNos)
167 
                    ug_referrers = set(ug_referrers)
168 
                    ug_referrers = list(ug_referrers)
169 
                    ug_groupbasis = set(ug_groupbasis)
170 
                    ug_groupbasis = list(ug_groupbasis)
171 
 
172 
                    for userId in ug_userIds:
173 
                        if not userId in newUserIds:
174 
                            newUserIds = newUserIds +','+str(userId)
175 
                            moreUsersToConsider.append(userId)
15134 manish.sha 176 
 
15221 manish.sha 177 
                    for mobileNo in ug_mobileNos :
178 
                        if not mobileNo in newMobileNos:
179 
                            newMobileNos = newMobileNos +','+mobileNo
180 
 
181 
                    for imei in ug_imeis:
182 
                        if not imei in newImeis:
183 
                            newImeis = newImeis+','+imei
184 
 
185 
                    for referrer in ug_referrers:
186 
                        if not referrer in newReferrers:
187 
                            newReferrers = newReferrers +','+referrer
15134 manish.sha 188 
 
15221 manish.sha 189 
                    if len(newGroupBasis) == 0:
190 
                        if len(ug_userIds) > 1:
191 
                            for groupbase in ug_groupbasis:
192 
                                if not groupbase in newGroupBasis:
193 
                                    newGroupBasis = newGroupBasis +','+groupbase
194 
                    else:
195 
                        for groupbase in ug_groupbasis:
196 
                            if not groupbase in newGroupBasis:
197 
                                newGroupBasis = newGroupBasis +','+groupbase
198 
 
199 
                newGroupBasis = newGroupBasis.strip(',')
200 
 
201 
                for userId in userIds:
202 
                    if not str(userId) in newUserIds:
203 
                        newUserIds = newUserIds +','+str(userId)
204 
 
205 
                for mobileNo in mobileNos :
206 
                    if not mobileNo in newMobileNos:
207 
                        newMobileNos = newMobileNos +','+mobileNo
15134 manish.sha 208 
 
15221 manish.sha 209 
                for imei in imeis:
210 
                    if not imei in newImeis:
211 
                        newImeis = newImeis+','+imei
212 
 
213 
                for referrer in referrers:
214 
                    if not referrer in newReferrers:
215 
                        newReferrers = newReferrers +','+referrer
15134 manish.sha 216 
 
15221 manish.sha 217 
                if len(newGroupBasis)> 0:
218 
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
219 
                        newGroupBasis = str(record_data[4])
220 
                else:
221 
                    if len(userIds) > 1 and len(fguserIds) < len(userIds):
222 
                        newGroupBasis = str(record_data[4])
223 
                    else:
224 
                        for groupbase in groupbasis:
225 
                            if not groupbase in newGroupBasis:
226 
                                newGroupBasis = newGroupBasis +','+groupbase
227 
 
228 
                newGroupBasis = newGroupBasis.strip(',')
229 
 
230 
                updateUserGroup = "update usergroups set userids ='%s', mobilenos ='%s', imeis ='%s', referrers ='%s', groupbasis ='%s' where id = %d"%(newUserIds, newMobileNos, newImeis, newReferrers, newGroupBasis, firstUserGroup[0])
231 
                print updateUserGroup
232 
                try:
233 
                    cursor.execute(updateUserGroup)
234 
                    db.commit()
235 
                except:
236 
                    # Rollback in case there is any error
237 
                    db.rollback()
238 
 
239 
                usersToConsider = Users.query.filter(Users.id.in_(tuple(moreUsersToConsider)))
240 
 
241 
                for user in usersToConsider:
242 
                    user.usergroup_id = firstUserGroup[0]
243 
                    session.commit()
244 
            elif len(existingGroupIds)==0:
245 
 
246 
                sql = "insert into usergroups (userids, mobilenos, imeis, referrers, groupbasis) values('%s', '%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3], record_data[4])
247 
 
248 
                print sql
249 
                try:
250 
                    cursor.execute(sql)
251 
                    db.commit()
252 
                except:
253 
                    # Rollback in case there is any error
254 
                    db.rollback()
255 
 
256 
                userGroupRecordSql = "select * from usergroups where mobilenos like '%s' and imeis like '%s' and referrers like '%s' and groupbasis like '%s'"%(record_data[1], record_data[2], record_data[3], record_data[4])
257 
                print userGroupRecordSql
258 
                cursor.execute(userGroupRecordSql)
259 
 
260 
                userGroupRecord = cursor.fetchone()
261 
 
262 
                for user in users:
263 
                    if not user.usergroup_id or user.usergroup_id==0:
264 
                        user.usergroup_id = userGroupRecord[0]
265 
                    session.commit()
266 
 
267 
    userGroupDetails = session.query(Users.usergroup_id, func.count(Users.id)).group_by(Users.usergroup_id).all()
268 
 
269 
    groupBasisUpdate = []
270 
    for usergroupDetail in userGroupDetails:
271 
        if usergroupDetail[1] ==1:
272 
            groupBasisUpdate.append(str(usergroupDetail[0]))
273 
 
274 
    updateUserGroupBasis ="update usergroups set groupbasis='Not_Defined' where id in %s"%(str(tuple(groupBasisUpdate)))
275 
 
276 
    print updateUserGroupBasis
277 
 
278 
    try:
279 
        cursor.execute(updateUserGroupBasis)
280 
        db.commit()
281 
    except:
282 
        # Rollback in case there is any error
283 
        db.rollback()
284 
 
15134 manish.sha 285 
    db.close()
286 
    if session.is_active:
287 
        print "session is active. closing it."
288 
        session.close()
289 
 
290 
if __name__=='__main__':
291 
    main()