Subversion Repositories SmartDukaan

Rev

Rev 15134 | Go to most recent revision | 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, \
12 
    between, in_
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 
    sql = "select GROUP_CONCAT( distinct x.user_ids ) user_ids, x.mobileNos, GROUP_CONCAT( distinct x.imeis) imeis, GROUP_CONCAT(distinct x.referrers) referrers from (SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is not null and u.mobile_number is not null GROUP BY d.imeinumber) as x group by x.mobileNos 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 FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is not null GROUP BY u.mobile_number 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 FROM  `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is null and LOWER(u.referrer) not like 'emp%' and LOWER(u.referrer) not like 'crm%' and LOWER(u.referrer) not like 'fos%' GROUP BY u.referrer"
21 
 
15114 manish.sha 22 
    cursor.execute(sql)
23 
    result_data = cursor.fetchall()
24 
    if result_data:
25 
        for record_data in result_data:
15134 manish.sha 26 
            users = Users.query.filter(Users.id.in_(record_data[0]))
27 
            existingGroupId = 0
28 
            for user in users:
29 
                if user.userGroupId and user.userGroupId >0:
30 
                    existingGroupId = user.userGroupId
15136 manish.sha 31 
 
15134 manish.sha 32 
            if existingGroupId >0:
33 
                checkForExistingUserGroup = "select * from usergroup where id=%d"%(existingGroupId)
34 
                cursor.execute(checkForExistingUserGroup)
35 
                existingUserGroup = cursor.fetchone()
36 
                updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], existingUserGroup[0])
37 
                try:
38 
                    cursor.execute(updateUserGroup)
39 
                    db.commit()
40 
                except:
41 
                    # Rollback in case there is any error
42 
                    db.rollback()
43 
 
44 
                for user in users:
45 
                    if not user.userGroupId or user.userGroupId==0:
46 
                        user.userGroupId = existingGroupId
47 
                    session.commit()
48 
            else:
49 
                updateUserGroupUserIds = False
50 
                updateUserGroupMobileNos = False
51 
                updateUserGroupImeis = False
52 
                updateUserGroupReferrers = False
53 
                print record_data[0]
54 
                checkForCurrentRecord = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
55 
                print checkForCurrentRecord
56 
 
57 
                cursor.execute(checkForCurrentRecord)
58 
                currentRecord = cursor.fetchone()
59 
                if currentRecord is not None:
60 
                    if currentRecord[1] != record_data[0]:
61 
                        updateUserGroupUserIds = True
62 
                    if currentRecord[2] != record_data[1]:
63 
                        updateUserGroupMobileNos = True
64 
                    if currentRecord[3] != record_data[2]:
65 
                        updateUserGroupImeis = True
66 
                    if currentRecord[4] != record_data[3]:
67 
                        updateUserGroupReferrers = True
68 
                    updateUserGroup = None
69 
                    if updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
70 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], record_data[3], currentRecord[0])
71 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
72 
                        updateUserGroup =None
73 
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
74 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
75 
                    elif updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
76 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])
77 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
78 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], currentRecord[4], currentRecord[0])
79 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and not updateUserGroupReferrers:
80 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], currentRecord[4], currentRecord[0])
81 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
82 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], currentRecord[4], currentRecord[0])
83 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
84 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], currentRecord[3], record_data[3], currentRecord[0])
85 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
86 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
87 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
88 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
89 
                    elif not updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
90 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
91 
                    elif not updateUserGroupUserIds and updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
92 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(currentRecord[1], record_data[1], record_data[2], record_data[3], currentRecord[0])
93 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and not updateUserGroupReferrers:
94 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], currentRecord[4], currentRecord[0])
95 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and updateUserGroupImeis and updateUserGroupReferrers:
96 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], record_data[2], record_data[3], currentRecord[0])
97 
                    elif updateUserGroupUserIds and not updateUserGroupMobileNos and not updateUserGroupImeis and updateUserGroupReferrers:
98 
                        updateUserGroup = "update usergroup set user_ids ='%s', mobileNos ='%s', imeis ='%s', referrers ='%s' where id = %d"%(record_data[0], currentRecord[2], currentRecord[3], record_data[3], currentRecord[0])
99 
 
100 
                    if updateUserGroup is not None:
101 
                        try:
102 
                            cursor.execute(updateUserGroup)
103 
                            db.commit()
104 
                        except:
105 
                            # Rollback in case there is any error
106 
                            db.rollback()
107 
 
108 
                    for user in users:
109 
                        if not user.userGroupId or user.userGroupId==0:
110 
                            user.userGroupId = currentRecord[0]
111 
                        session.commit()
112 
                else:
113 
                    sql = "insert into usergroup (user_ids, mobileNos, imeis, referrers) values('%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3])
114 
                    print sql
15114 manish.sha 115 
 
15134 manish.sha 116 
                    try:
117 
                        cursor.execute(sql)
118 
                        db.commit()
119 
                    except:
120 
                        # Rollback in case there is any error
121 
                        db.rollback()
122 
 
123 
                    userGroupRecordSql = "select * from usergroup where mobileNos like '%%%s%%' or imeis like '%%%s%%' or referrers like '%%%s%%'"%(record_data[1], record_data[2], record_data[3])
124 
                    cursor.execute(userGroupRecordSql)
125 
 
126 
                    userGroupRecord = cursor.fetchone()
127 
 
128 
                    for user in users:
129 
                        if not user.userGroupId or user.userGroupId==0:
130 
                            user.userGroupId = userGroupRecord[0]
131 
                        session.commit()
132 
 
133 
    db.close()
134 
    if session.is_active:
135 
        print "session is active. closing it."
136 
        session.close()
137 
 
138 
if __name__=='__main__':
139 
    main()